<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cftech.member.dao.MemberMapper">

    <resultMap id="resultMap" type="com.cftech.member.model.Member">
        <id column="id" property="id"/>
        <result column="number" property="number"/>
        <result column="name" property="name"/>
        <result column="phone" property="phone"/>
        <result column="imageurl" property="imageurl"/>
        <result column="cardid" property="cardid"/>
        <result column="email" property="email"/>
        <result column="member_id" property="memberId"/>
        <result column="accounts_id" property="accountsId"/>
        <result column="del_flag" property="delFlag"/>
        <result column="status" property="status"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="description" property="description"/>
        <result column="create_by" property="createBy"/>
        <result column="update_by" property="updateBy"/>
        <result column="open_id" property="openId"/>
        <result column="storeid" property="storeid"/>
        <result column="fanss_store_id" property="fanssStoreId"/>
        <result column="sex" property="sex"/>
        <result column="birthday" property="birthday"/>
        <result column="area" property="area"/>
        <result column="service_id" property="serviceId"/>
        <result column="doctor_id" property="doctorId"/>
        <result column="invitcode" property="invitcode"/>
        <result column="compan" property="compan"/>
        <result column="source" property="source"/>
    </resultMap>

    <resultMap id="resultMapDto" type="com.cftech.member.model.MemberDto">
        <id column="id" property="id"/>
        <result column="imageurl" property="imageurl"/>
        <result column="sex" property="sex"/>
        <result column="surname" property="surname"/>
        <result column="name" property="name"/>
        <result column="phone" property="phone"/>
        <result column="compan" property="compan"/>
        <result column="invitcode" property="invitcode"/>
        <result column="peopletype" property="peopletype"/>
        <result column="open_id" property="open_id"/>
        <result column="storeid" property="storeid"/>
        <result column="status" property="status"/>
        <result column="create_time" property="createTime"/>
        <result column="source" property="source"/>
    </resultMap>

    <sql id="sqlWhere">
        <if test="conds!=null">
            <trim prefix="WHERE" prefixOverrides="AND|OR">
                <foreach collection="conds.conds" index="index" item="cond">
                    ${cond.linkType}
                    <if test="cond.condType == 'EQUAL'">${cond.param} = #{cond.value}</if>
                    <if test="cond.condType == 'NOTEQUAL'">${cond.param} &lt;&gt; #{cond.value}</if>
                    <if test="cond.condType == 'GREATEQUAL'">${cond.param} &gt;= #{cond.value}</if>
                    <if test="cond.condType == 'GREATTHAN'">${cond.param} &gt; #{cond.value}</if>
                    <if test="cond.condType == 'LESSEQUAL'">${cond.param} &lt;= #{cond.value}</if>
                    <if test="cond.condType == 'LESSTHAN'">${cond.param} &lt; #{cond.value}</if>
                    <if test="cond.condType == 'BETWEEN'">${cond.param} BETWEEN #{cond.startValue} AND
                        #{cond.endValue}
                    </if>
                    <if test="cond.condType == 'ISNULL'">${cond.param} IS NULL</if>
                    <if test="cond.condType == 'NOTNULL'">${cond.param} IS NOT NULL</if>
                    <if test="cond.condType == 'LIKE'">${cond.param} LIKE #{cond.value}</if>
                    <if test="cond.condType == 'IN'">${cond.param} IN
                        <foreach item="item" index="index" collection="cond.value" open="(" separator="," close=")">
                            #{item}
                        </foreach>
                    </if>
                </foreach>
            </trim>
        </if>
    </sql>

    <sql id="sqlColumnslist">
        id,
        number,
        CONVERT(AES_DECRYPT(name,'aideakey') USING UTF8) name ,
        CONVERT(AES_DECRYPT(phone,'aideakey') USING UTF8) phone,
        imageurl,
        cardid,
        CONVERT(AES_DECRYPT(email,'aideakey') USING UTF8) email,
        member_id,
        accounts_id,
        del_flag,
        status,
        create_time,
        update_time,
        description,
        create_by,
        update_by,
        open_id,
        storeid,
        fanss_store_id,
        sex,
        CONVERT( AES_DECRYPT(birthday,'aideakey') USING UTF8) birthday,
        area,
        service_id,
        doctor_id,
        invitcode,
        compan,
        source
    </sql>

    <sql id="sqlColumns">
        id,number,name,phone,imageurl,cardid,email,member_id,accounts_id,del_flag,status,create_time,update_time,description,create_by,update_by,
        open_id,storeid,fanss_store_id,sex,birthday,area,service_id,doctor_id,invitcode,compan,source
    </sql>

    <insert id="save" parameterType="com.cftech.member.model.Member" useGeneratedKeys="true"
            keyProperty="id">
        insert into wx_mp_member
        (
            <include refid="sqlColumns"/>
        )
        values
        (
            #{id, jdbcType=BIGINT},
            #{number, jdbcType=VARCHAR},
            AES_ENCRYPT(#{name, jdbcType=VARBINARY},'aideakey'),
            AES_ENCRYPT(#{phone, jdbcType=VARBINARY},'aideakey'),
            #{imageurl, jdbcType=VARCHAR},
            #{cardid, jdbcType=VARCHAR},
            AES_ENCRYPT(#{email, jdbcType=VARBINARY},'aideakey'),
            #{memberId, jdbcType=VARCHAR},
            #{accountsId, jdbcType=BIGINT},
            #{delFlag, jdbcType=TINYINT},
            #{status, jdbcType=VARCHAR},
            now(),
            now(),
            #{description, jdbcType=VARCHAR},
            #{createBy, jdbcType=BIGINT},
            #{updateBy, jdbcType=BIGINT},
            #{openId, jdbcType=VARCHAR},
            #{storeid,jdbcType=VARCHAR},
            #{fanssStoreId, jdbcType=BIGINT},
            #{sex, jdbcType=BIGINT},AES_ENCRYPT(#{birthday,jdbcType=VARBINARY},'aideakey'),#{area,jdbcType=VARCHAR},
            #{serviceId, jdbcType=VARCHAR},
            #{doctorId,jdbcType=VARCHAR},
            #{invitcode,jdbcType=VARCHAR},
            #{compan,jdbcType=VARCHAR},
            #{source,jdbcType=BIGINT}
        )
    </insert>

    <select id="fetchById" parameterType="java.lang.Long" resultMap="resultMap">
        SELECT
            t.id,
            t.number,
            CONVERT( AES_DECRYPT(t.name,'aideakey') USING UTF8) t.name ,
            CONVERT( AES_DECRYPT( t.phone,'aideakey') USING UTF8) t.phone,
            t.imageurl,
            t.cardid,
            CONVERT( AES_DECRYPT(t.email,'aideakey') USING UTF8) t.email,
            t.member_id,
            t.accounts_id,
            t.del_flag,
            t.status,
            t.create_time,
            t.update_time,
            t.description,
            t.create_by,
            t.update_by,
            t.open_id,
            t.storeid,
            CONVERT(  AES_DECRYPT(offline.birthday,'aideakey') USING UTF8) offline.birthday,
            offline.sex,
            t.area,
            t.fanss_store_id,t.doctor_id,
            t.invitcode,t.compan,
            offline.name memberName,
            offline.phone memberPhone,
            offline.storeid memberStoreId
        FROM wx_mp_member t
        left join wx_mp_member_offline offline on offline.member_id = t.member_id and t.del_flag=0
        WHERE t.id=#{id}
    </select>

    <select id="count" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM wx_mp_member
        <include refid="sqlWhere"/>

    </select>

    <select id="fetchSearchByPage" parameterType="java.util.Map" resultMap="resultMap">
        SELECT
        <include refid="sqlColumnslist"/>
        FROM wx_mp_member
        <include refid="sqlWhere"/>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>


    <select id="countServer" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM wx_mp_member member
        LEFT JOIN wx_mp_member_offline offline on member.cardid = offline.cardid
        <include refid="sqlWhere"/>
    </select>

    <select id="fetchSearchByPageServer" parameterType="java.util.Map" resultMap="resultMap">
        SELECT
            member.id,
            member.number,
            CONVERT(AES_DECRYPT(member.name,'aideakey') USING UTF8) member.name,
            member.surname,
            CONVERT(AES_DECRYPT(member.phone,'aideakey') USING UTF8) member.phone,
            member.imageurl,
            member.member_id,
            member.accounts_id,
            member.del_flag,
            member. STATUS,
            member.create_time,
            member.update_time,
            member.description,
            member.create_by,
            member.update_by,
            member.open_id,
            member.storeid,
            member.invitcode,
            member.compan,
            member.sex,
            member.service_id
        FROM
        wx_mp_member member
        <include refid="sqlWhere"/>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>

    <update id="update" parameterType="com.cftech.member.model.Member">
        update wx_mp_member
        <set>
            <if test="id != null">
                id = #{id, jdbcType=BIGINT},
            </if>
            <if test="number != null">
                number = #{number, jdbcType=VARCHAR},
            </if>
            <if test="name != null">
                name = AES_ENCRYPT(#{name, jdbcType=VARBINARY},'aideakey'),
            </if>
            <if test="surname!=null">
                surname = #{surname},
            </if>
            <if test="phone != null">
                phone = AES_ENCRYPT(#{phone, jdbcType=VARBINARY},'aideakey'),
            </if>
            <if test="imageurl != null">
                imageurl = #{imageurl, jdbcType=VARCHAR},
            </if>
            <if test="cardid != null">
                cardid = #{cardid, jdbcType=VARCHAR},
            </if>
            <if test="email != null">
                email = AES_ENCRYPT(#{email, jdbcType=VARBINARY},'aideakey'),
            </if>
            <if test="memberId != null">
                member_id = #{memberId, jdbcType=VARCHAR},
            </if>
            <if test="accountsId != null">
                accounts_id = #{accountsId, jdbcType=BIGINT},
            </if>
            <if test="delFlag != null">
                del_flag = #{delFlag, jdbcType=TINYINT},
            </if>
            <if test="status != null">
                status = #{status, jdbcType=VARCHAR},
            </if>
            <if test="createTime != null">
                create_time = #{createTime, jdbcType=TIMESTAMP},
            </if>
            <if test="description != null">
                description = #{description, jdbcType=VARCHAR},
            </if>
            <if test="createBy != null">
                create_by = #{createBy, jdbcType=BIGINT},
            </if>
            <if test="updateBy != null">
                update_by = #{updateBy, jdbcType=BIGINT},
            </if>
            <if test="openId != null">
                open_id = #{openId, jdbcType=BIGINT},
            </if>
            <if test="storeid != null">
                storeid = #{storeid, jdbcType=VARCHAR},
            </if>
            <if test="sex != null">
                sex = #{sex, jdbcType=BIGINT},
            </if>
            <if test="birthday != null">
                birthday = AES_ENCRYPT(#{birthday, jdbcType=VARBINARY},'aideakey'),
            </if>
            <if test="area != null">
                area = #{area, jdbcType=VARCHAR},
            </if>
            <if test="fanssStoreId != null">
                fanss_store_id = #{fanssStoreId, jdbcType=VARCHAR},
            </if>
            <if test="doctorId != null">
                doctor_id = #{doctorId, jdbcType=VARCHAR}
            </if>
            <if test="invitcode != null">
                invitcode = #{invitcode, jdbcType=VARCHAR}
            </if>
            <if test="compan != null">
                compan = #{compan, jdbcType=VARCHAR}
            </if>
            <if test="serviceId != null">
                service_id = #{serviceId, jdbcType=VARCHAR}
            </if>
        </set>
        where id=#{id,jdbcType=BIGINT}
    </update>

    <update id="delete" parameterType="java.lang.Long">
        update wx_mp_member set del_flag=1 where id=#{id,jdbcType=BIGINT}
    </update>

    <select id="selectmemberDto" parameterType="java.util.Map" resultType="com.cftech.member.model.MemberDto">
        SELECT
        m.id,
        m.imageurl,
        m.sex,
        CONVERT(AES_DECRYPT(m.name,'aideakey') USING UTF8) as name,
        CONVERT(AES_DECRYPT(m.phone,'aideakey') USING UTF8) as phone,
        m.open_id AS openId,
        m.create_time as createTime,
        m.source
        FROM
        wx_mp_member m
        <include refid="sqlWhere"/>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>
    <select id="selectmemberDtoCount" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT
        COUNT(1)
        FROM
        wx_mp_member m
        <include refid="sqlWhere"/>
    </select>
    <!--个人信息回填
   -->
    <select id="memberFormation" parameterType="java.lang.String" resultType="com.cftech.member.model.MemberVO">
       SELECT id,CONVERT(AES_DECRYPT(name,'aideakey') USING UTF8) AS memberName, sex, CONVERT(AES_DECRYPT(birthday,'aideakey')USING UTF8) birthday, imageurl,
          CONVERT(AES_DECRYPT(email,'aideakey')USING UTF8) AS mailbox, CONVERT(AES_DECRYPT(phone,'aideakey')USING UTF8) as phone, cardid
       FROM  wx_mp_member WHERE open_id = #{openid} and del_flag=0
    </select>

    <select id="fanssChat" resultType="com.cftech.member.model.FanssVO">
        SELECT openid,CONVERT(AES_DECRYPT(nickname,'aideakey') USING UTF8) nickname,headimgurl
        FROM  wx_mp_fanss WHERE openid = #{openid} and delflag=0
    </select>
    <!--个人信息修改
    -->
    <update id="updateMember" parameterType="com.cftech.member.model.MemberVO">
        update wx_mp_member
        <set>
            <if test="memberName !=null">
                `name` = AES_ENCRYPT(#{memberName},'aideakey'),
            </if>
            <if test="sex !=null">
                sex = #{sex},
            </if>
            <if test="birthday !=null">
                birthday = AES_ENCRYPT(#{birthday},'aideakey'),
            </if>
            <if test="mailbox !=null">
                email = AES_ENCRYPT(#{mailbox},'aideakey'),
            </if>
            <if test="phone !=null">
                phone = AES_ENCRYPT(#{phone},'aideakey'),
            </if>
            <if test="imageUrl !=null">
                imageurl = #{imageUrl},
            </if>
            <if test="cardid != null ">
                cardid = #{cardid}
            </if>
            <if test="compan != null ">
                compan = #{compan}
            </if>
        </set>
        where id =#{id}
    </update>
</mapper>