<?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.report.dao.ReportMapper">

    <resultMap id="resultMap" type="com.cftech.report.model.Report">
        <id column="id" property="id"/>
        <result column="region" property="region"/>
        <result column="province" property="province"/>
        <result column="city" property="city"/>
        <result column="sales_represent" property="salesRepresent"/>
        <result column="hospital" property="hospital"/>
        <result column="department" property="department"/>
        <result column="doctor_name" property="doctorName"/>
        <result column="doctor_title" property="doctorTitle"/>
        <result column="sex" property="sex"/>
        <result column="doctor_phone" property="doctorPhone"/>
        <result column="cf_hospital" property="cfHospital"/>
        <result column="cf_doctor" property="cfDoctor"/>
        <result column="consult_number" property="consultNumber"/>
        <result column="order_number" property="orderNumber"/>
        <result column="user_name" property="userName"/>
        <result column="phone" property="phone"/>
        <result column="product_name" property="productName"/>
        <result column="drugs_num" property="drugsNum"/>
        <result column="fk_time" property="fkTime"/>
        <result column="fh_time" property="fhTime"/>
        <result column="sj_time" property="sjTime"/>
        <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"/>
    </resultMap>

    <resultMap id="resultMapPeopleInfo" type="com.cftech.report.model.ReportPeopleInfo">
        <id column="id" property="id"/>
        <result column="productName" property="productName"/>
        <result column="drugsNum" property="drugsNum"/>
        <result column="orderNumber" property="orderNumber"/>
        <result column="consultNumber" property="consultNumber"/>
        <result column="userName" property="userName"/>
        <result column="phone" property="phone"/>
        <result column="cfDoctor" property="cfDoctor"/>
        <result column="cfHospital" property="cfHospital"/>
        <result column="fkTime" property="fkTime"/>
        <result column="fhTime" property="fhTime"/>
        <result column="sjTime" property="sjTime"/>
        <result column="openId" property="openId"/>
    </resultMap>

    <resultMap id="resultMapCodeInfo" type="com.cftech.report.model.ReportCode">
        <id column="id" property="id"/>
        <result column="openId" property="openId"/>
        <result column="type" property="type"/>
        <result column="bindId" property="bindId"/>
    </resultMap>

    <resultMap id="resultMapDoctorInfo" type="com.cftech.report.model.ReportDoctorInfo">
        <id column="id" property="id"/>
        <result column="userName" property="userName"/>
        <result column="gender" property="gender"/>
        <result column="orgId" property="orgId"/>
        <result column="orgName" property="orgName"/>
        <result column="orgParentIds" property="orgParentIds"/>
        <result column="userType" property="userType"/>
        <result column="userPhone" property="userPhone"/>
    </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">
                                    id,
                                                region,
                                                province,
                                                city,
                                                sales_represent,
                                                hospital,
                                                department,
                                                doctor_name,
                                                doctor_title,
                                                sex,
                                                doctor_phone,
                                                cf_hospital,
                                                cf_doctor,
                                                consult_number,
                                                order_number,
                                                user_name,
                                                phone,
                                                product_name,
                                                drugs_num,
                                                fk_time,
                                                fh_time,
                                                sj_time,
                                                accounts_id,
                                                del_flag,
                                                status,
                                                create_time,
                                                update_time,
                                                description,
                                                create_by,
                                                update_by
                        </sql>


    <insert id="save" parameterType="com.cftech.report.model.Report" useGeneratedKeys="true"
            keyProperty="id">
        insert into t_aidea_report
        (
        <include refid="sqlColumns"/>
        )
        values
        (
        #{id, jdbcType=BIGINT},
        #{region, jdbcType=VARCHAR},
        #{province, jdbcType=VARCHAR},
        #{city, jdbcType=VARCHAR},
        #{salesRepresent, jdbcType=VARCHAR},
        #{hospital, jdbcType=VARCHAR},
        #{department, jdbcType=VARCHAR},
        #{doctorName, jdbcType=VARCHAR},
        #{doctorTitle, jdbcType=VARCHAR},
        #{sex, jdbcType=VARCHAR},
        #{doctorPhone, jdbcType=VARCHAR},
        #{cfHospital, jdbcType=VARCHAR},
        #{cfDoctor, jdbcType=VARCHAR},
        #{consultNumber, jdbcType=VARCHAR},
        #{orderNumber, jdbcType=VARCHAR},
        #{userName, jdbcType=VARCHAR},
        #{phone, jdbcType=VARCHAR},
        #{productName, jdbcType=VARCHAR},
        #{drugsNum, jdbcType=VARCHAR},
        #{fkTime , jdbcType=TIMESTAMP},
        #{fhTime , jdbcType=TIMESTAMP},
        #{sjTime , jdbcType=TIMESTAMP},
        #{accountsId, jdbcType=BIGINT},
        #{delFlag, jdbcType=TINYINT},
        #{status, jdbcType=VARCHAR},
        now(),
        now(),
        #{description, jdbcType=VARCHAR},
        #{createBy, jdbcType=BIGINT},
        #{updateBy, jdbcType=BIGINT}
        )
    </insert>

    <select id="fetchById" parameterType="java.lang.Long" resultMap="resultMap">
        SELECT
        <include refid="sqlColumns"/>
        FROM t_aidea_report t
        WHERE t.id=#{id}
    </select>

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

    </select>

    <select id="fetchSearchByPage" parameterType="java.util.Map" resultMap="resultMap">
        SELECT
        <include refid="sqlColumns"/>
        FROM t_aidea_report
        <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.report.model.Report">
        update t_aidea_report
        <set>
            <if test="id != null">
                id = #{id, jdbcType=BIGINT},
            </if>
            <if test="region != null">
                region = #{region, jdbcType=VARCHAR},
            </if>
            <if test="province != null">
                province = #{province, jdbcType=VARCHAR},
            </if>
            <if test="city != null">
                city = #{city, jdbcType=VARCHAR},
            </if>
            <if test="salesRepresent != null">
                sales_represent = #{salesRepresent, jdbcType=VARCHAR},
            </if>
            <if test="hospital != null">
                hospital = #{hospital, jdbcType=VARCHAR},
            </if>
            <if test="department != null">
                department = #{department, jdbcType=VARCHAR},
            </if>
            <if test="doctorName != null">
                doctor_name = #{doctorName, jdbcType=VARCHAR},
            </if>
            <if test="doctorTitle != null">
                doctor_title = #{doctorTitle, jdbcType=VARCHAR},
            </if>
            <if test="sex != null">
                sex = #{sex, jdbcType=VARCHAR},
            </if>
            <if test="doctorPhone != null">
                doctor_phone = #{doctorPhone, jdbcType=VARCHAR},
            </if>
            <if test="cfHospital != null">
                cf_hospital = #{cfHospital, jdbcType=VARCHAR},
            </if>
            <if test="cfDoctor != null">
                cf_doctor = #{cfDoctor, jdbcType=VARCHAR},
            </if>
            <if test="consultNumber != null">
                consult_number = #{consultNumber, jdbcType=VARCHAR},
            </if>
            <if test="orderNumber != null">
                order_number = #{orderNumber, jdbcType=VARCHAR},
            </if>
            <if test="userName != null">
                user_name = #{userName, jdbcType=VARCHAR},
            </if>
            <if test="phone != null">
                phone = #{phone, jdbcType=VARCHAR},
            </if>
            <if test="productName != null">
                product_name = #{productName, jdbcType=VARCHAR},
            </if>
            <if test="drugsNum != null">
                drugs_num = #{drugsNum, jdbcType=VARCHAR},
            </if>
            <if test="fkTime != null">
                fk_time = #{fkTime, jdbcType=TIMESTAMP},
            </if>
            <if test="fhTime != null">
                fh_time = #{fhTime, jdbcType=TIMESTAMP},
            </if>
            <if test="sjTime != null">
                sj_time = #{sjTime, jdbcType=TIMESTAMP},
            </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>
        </set>
        where id=#{id,jdbcType=BIGINT}
    </update>

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

    <!--查询报表人员信息-->
    <select id="selectReportPeopleInfo" resultMap="resultMapPeopleInfo" parameterType="java.util.Map">
        SELECT
        product.product_name productName,
        detail.drugs_num drugsNum,
        ord.number orderNumber,
        consult.consult_id consultNumber,
        CONVERT ( AES_DECRYPT( consult.user_name, 'aideakey' ) USING UTF8 ) userName,
        CONVERT ( AES_DECRYPT( consult.phone, 'aideakey' ) USING UTF8 ) phone,
        qyuser.`name` cfDoctor,
        org1.org_name cfHospital,
        ord.create_time fkTime,
        waybill.send_express_date fhTime,
        waybill.accept_express_date sjTime,
        ord.openid openId
        FROM
        t_order_details detail
        LEFT JOIN t_aidea_product product ON product.id = detail.drugs_id
        AND product.del_flag = 0
        LEFT JOIN t_order ord ON ord.id = detail.order_id
        LEFT JOIN t_aidea_consult_sheet consult ON consult.order_id = ord.id
        LEFT JOIN t_qyuser qyuser ON qyuser.id = consult.past_records
        LEFT JOIN t_orgunit org1 ON org1.id = consult.allergy
        LEFT JOIN t_aidea_waybill waybill ON waybill.order_id = org1.id
        <include refid="sqlWhere"/>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
    </select>

    <!--查询用户对应的扫码记录-->
    <select id="selectReportCodeInfo" resultMap="resultMapCodeInfo" parameterType="java.util.Map">
        SELECT
        record.openid openId,
        record.type type,
        record.ticket ticket,
        shop.bind_id bindId
        FROM
        t_behavior_qrcode_record record
        LEFT JOIN t_shop_wxqrcode shop ON shop.ticket = record.ticket
        AND shop.del_flag = 0
        <include refid="sqlWhere"/>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
    </select>

    <!--根据医生Id查询医生信息-->
    <select id="selectReportDoctorInfo" resultMap="resultMapDoctorInfo" parameterType="java.util.Map">
        SELECT
        qyuser.id id,
        qyuser.`name` userName,
        qyuser.gender gender,
        qyuser.org_id orgId,
        qyuser.user_type userType,
        qyuser.mobile userPhone,
        org.org_name orgName,
        org.parent_ids orgParentIds
        FROM
        t_qyuser qyuser
        LEFT JOIN t_orgunit org ON org.id = qyuser.org_id
        AND org.del_flag = 0
        <include refid="sqlWhere"/>
    </select>

</mapper>