<?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.consultsheet.dao.ConsultSheetMapper">

    <resultMap id="resultMap" type="com.cftech.consultsheet.model.ConsultSheet">
        <id column="id" property="id"/>
        <result column="consult_id" property="consultId"/>
        <result column="user_name" property="userName"/>
        <result column="number_id" property="numberId"/>
        <result column="sex" property="sex"/>
        <result column="phone" property="phone"/>
        <result column="past_records" property="pastRecords"/>
        <result column="prescription" property="prescription"/>
        <result column="doctor_id" property="doctorId"/>
        <result column="service_id" property="serviceId"/>
        <result column="illness" property="illness"/>
        <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="allergy" property="allergy"/>
        <result column="symptom" property="symptom"/>
        <result column="diagnosis" property="diagnosis"/>
        <result column="open_id" property="openId"></result>
        <result column="drugs_id" property="drugsId"></result>
        <result column="member_id" property="memberId"></result>
        <result column="order_id" property="orderId"></result>
        <result column="address_id" property="addressId"></result>
        <result column="audit_time" property="auditTime"></result>
    </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="sqlColumns">
        a.id,
        a.consult_id,
        CONVERT(AES_DECRYPT(a.user_name,'aideakey') USING UTF8) userName,
        a.number_id numberId,
        a.sex,
        CONVERT(AES_DECRYPT(a.phone,'aideakey') USING UTF8) phone,
        a.past_records pastRecords,
        a.prescription,
        a.doctor_id doctorId,
        a.service_id serviceId,
        a.illness,
        a.accounts_id accountsId,
        a.del_flag delFlag,
        a.status,
        a.create_time createTime,
        a.update_time updateTime,
        a.description,
        a.create_by createBy,
        a.update_by updateBy,
        a.allergy,
        a.symptom,
        a.diagnosis,
        a.open_id openId,
        a.drugs_id drugsId,
        a.member_id memberId,
        a.order_id orderId,
        a.address_id addressId,
        a.audit_time auditTime,
        b.`name` pharmaName,
        c.`name` customerName
    </sql>

    <sql id="sqlCol">
        id,
        CONVERT(AES_DECRYPT(user_name,'aideakey') USING UTF8) userName,
        sex,
        CONVERT(AES_DECRYPT(phone,'aideakey') USING UTF8) phone,
        past_records,
        prescription,
        illness,
        accounts_id,
        STATUS,
        create_time,
        description,
        allergy,
        symptom,
        open_id,
        consult_id consultId,
        status,
        drugs_id drugsId,
        address_id addressId
    </sql>

    <insert id="save" parameterType="com.cftech.consultsheet.model.ConsultSheet" useGeneratedKeys="true"
            keyProperty="id">
        insert into t_aidea_consult_sheet
        (
            id,
            consult_id,
            user_name ,
            number_id,
            sex,
            phone,
            past_records,
            prescription,
            doctor_id,
            service_id,
            illness,
            accounts_id,
            del_flag,
            STATUS,
            create_time,
            update_time,
            description,
            create_by,
            update_by,
            allergy,
            symptom,
            diagnosis,
            open_id,
            drugs_id,
            member_id,
            order_id,
            address_id,
            audit_time
        )
        values
        (
            #{id, jdbcType=BIGINT},
            #{consultId, jdbcType=VARCHAR},
            AES_ENCRYPT(#{userName, jdbcType=VARBINARY},'aideakey'),
            #{numberId, jdbcType=VARCHAR},
            #{sex, jdbcType=BIGINT},
            AES_ENCRYPT(#{phone, jdbcType=VARBINARY},'aideakey'),
            #{pastRecords, jdbcType=VARCHAR},
            #{prescription, jdbcType=VARCHAR},
            #{doctorId, jdbcType=BIGINT},
            #{serviceId, jdbcType=BIGINT},
            #{illness, jdbcType=VARCHAR},
            #{accountsId, jdbcType=BIGINT},
            #{delFlag, jdbcType=TINYINT},
            #{status, jdbcType=VARCHAR},
            now(),
            now(),
            #{description, jdbcType=VARCHAR},
            #{createBy, jdbcType=BIGINT},
            #{updateBy, jdbcType=BIGINT},
            #{allergy, jdbcType=VARCHAR},
            #{symptom, jdbcType=VARCHAR},
            #{diagnosis, jdbcType=BIGINT},
            #{openId, jdbcType=VARCHAR},
            #{drugsId, jdbcType=BIGINT},
            #{memberId, jdbcType=BIGINT},
            #{orderId, jdbcType=BIGINT},
            #{addressId, jdbcType=VARCHAR},
            #{auditTime, jdbcType=TIMESTAMP}
        )
    </insert>

    <select id="fetchById" parameterType="java.lang.Long" resultType="com.cftech.consultsheet.model.ConsultSheet">
        SELECT
        <include refid="sqlColumns"/>, d.name pharmacist, e.org_name hospital, f.store fansAlias, CONVERT(AES_DECRYPT(f.nickname,'aideakey') USING UTF8) nickName
        FROM t_aidea_consult_sheet a
        LEFT JOIN t_qyuser b ON b.id = a.doctor_id
        LEFT JOIN t_qyuser c ON c.id = a.service_id
        LEFT JOIN t_qyuser d ON d.id = a.past_records
        LEFT JOIN t_orgunit e ON e.id = a.allergy
        LEFT JOIN wx_mp_fanss f ON f.openid = a.open_id AND f.delflag = 0
        WHERE a.id = #{id}
        GROUP BY id
    </select>

    <select id="count" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM t_aidea_consult_sheet a
        LEFT JOIN t_qyuser b ON b.id = a.doctor_id
        LEFT JOIN t_qyuser c ON c.id = a.service_id
        LEFT JOIN `user` u ON u.userid = b.id
        <include refid="sqlWhere"/>
    </select>

    <select id="fetchSearchByCount" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM t_aidea_consult_sheet a
        LEFT JOIN t_order o ON a.order_id = o.id
        LEFT JOIN t_qyuser b ON b.id = a.doctor_id AND b.del_flag = '0'
        LEFT JOIN t_qyuser c ON c.id = a.service_id AND c.del_flag = '0'
        LEFT JOIN wx_mp_member m ON m.open_id = a.open_id AND m.del_flag = '0'
        LEFT JOIN t_aidea_authentication au ON m.cardid = au.id_card AND au.del_flag = '0'
        <include refid="sqlWhere"/>
        <if test="id!=null">and (a.doctor_id = ${id} or a.service_id =${id} )</if>
    </select>

    <select id="fetchSearchByPage" parameterType="java.util.Map" resultType="com.cftech.consultsheet.model.ConsultSheet">
        SELECT
        <include refid="sqlColumns"/>, o.number orderCode, CASE WHEN au.name IS NOT NULL THEN 1 ELSE 0 END isThree
        FROM t_aidea_consult_sheet a
        LEFT JOIN t_order o ON a.order_id = o.id
        LEFT JOIN t_qyuser b ON b.id = a.doctor_id AND b.del_flag = '0'
        LEFT JOIN t_qyuser c ON c.id = a.service_id AND c.del_flag = '0'
        LEFT JOIN wx_mp_member m ON m.open_id = a.open_id AND m.del_flag = '0'
        LEFT JOIN t_aidea_authentication au ON m.cardid = au.id_card AND au.del_flag = '0'
        <include refid="sqlWhere"/>
        <if test="id!=null">AND (a.doctor_id = ${id} or a.service_id =${id})</if>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>

    <select id="consultSheetList" resultType="com.cftech.consultsheet.model.ConsultSheet">
        SELECT
          <include refid="sqlCol"/>
        from t_aidea_consult_sheet
        where del_flag = 0
            <if test="openid!=null">
                and open_id = #{openid}
            </if>
            <if test="name!=null and name !=''">
                and (CONVERT(AES_DECRYPT(user_name,'aideakey') USING UTF8) like concat('%',#{name},'%') OR consult_id like concat('%',#{name},'%'))
            </if>
            <if test="id!=null">
                and id = #{id}
            </if>
            ORDER BY create_time DESC
            <if test="page!=null and pageSize !='' and pageSize!=null and page !=''">
                limit #{page}, #{pageSize}
            </if>
    </select>

    <update id="update" parameterType="com.cftech.consultsheet.model.ConsultSheet">
        update t_aidea_consult_sheet
        <set>
            <if test="id != null">
                id = #{id, jdbcType=BIGINT},
            </if>
            <if test="consultId != null">
                consult_id = #{consultId, jdbcType=VARCHAR},
            </if>
            <if test="userName != null">
                user_name = AES_ENCRYPT(#{userName, jdbcType=VARBINARY},'aideakey'),
            </if>
            <if test="numberId != null">
                number_id = #{numberId, jdbcType=VARCHAR},
            </if>
            <if test="sex != null">
                sex = #{sex, jdbcType=BIGINT},
            </if>
            <if test="phone != null">
                phone = AES_ENCRYPT(#{phone, jdbcType=VARBINARY},'aideakey'),
            </if>
            <if test="pastRecords != null">
                past_records = #{pastRecords, jdbcType=VARCHAR},
            </if>
            <if test="prescription != null">
                prescription = #{prescription, jdbcType=VARCHAR},
            </if>
            <if test="doctorId != null">
                doctor_id = #{doctorId, jdbcType=BIGINT},
            </if>
            <if test="serviceId != null">
                service_id = #{serviceId, jdbcType=BIGINT},
            </if>
            <if test="illness != null">
                illness = #{illness, 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="allergy != null">
                allergy = #{allergy, jdbcType=VARCHAR},
            </if>
            <if test="symptom != null">
                symptom = #{symptom, jdbcType=VARCHAR},
            </if>
            <if test="diagnosis != null">
                diagnosis = #{diagnosis, jdbcType=BIGINT},
            </if>
            <if test="openId != null">
                open_id = #{openId, jdbcType=VARCHAR},
            </if>
            <if test="openId != null">
                drugs_id = #{drugsId, jdbcType=BIGINT},
            </if>
            <if test="openId != null">
                member_id = #{memberId, jdbcType=BIGINT},
            </if>
            <if test="openId != null">
                order_id = #{orderId, jdbcType=BIGINT},
            </if>
            <if test="addressId != null">
                address_id = #{addressId, jdbcType=BIGINT},
            </if>
            <if test="auditTime != null">
                audit_time = #{auditTime, jdbcType=TIMESTAMP},
            </if>
        </set>
        where id=#{id,jdbcType=BIGINT}
    </update>

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

    <update id="updateDate">
        update t_aidea_consult_sheet
        <set>
            <if test="status !=null">
                status = #{status},
            </if>
            <if test="description !=null">
                description = #{description},
            </if>
            audit_time = now()
        </set>
        where id = #{consultId}
    </update>

    <update id="updateStatus">
        update t_aidea_consult_sheet
        <set>
            <if test="status !=null">
                status = #{status},
            </if>
            <if test="des !=null and des != ''">
                symptom = #{des},
            </if>
            audit_time = now()
        </set>
        where id = #{consultId}
    </update>

</mapper>
