<?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.diagbot.mapper.MedCheckInfoMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.diagbot.entity.MedCheckInfo">
        <id column="id" property="id" />
        <result column="is_deleted" property="isDeleted" />
        <result column="gmt_create" property="gmtCreate" />
        <result column="gmt_modified" property="gmtModified" />
        <result column="creator" property="creator" />
        <result column="modifier" property="modifier" />
        <result column="behospital_code" property="behospitalCode" />
        <result column="hospital_id" property="hospitalId" />
        <result column="check_id" property="checkId" />
        <result column="check_name" property="checkName" />
        <result column="check_time" property="checkTime" />
        <result column="status" property="status" />
        <result column="check_type" property="checkType" />
        <result column="job_creator" property="jobCreator" />
        <result column="job_creator_name" property="jobCreatorName" />
        <result column="job_create_time" property="jobCreateTime" />
        <result column="job_type" property="jobType" />
        <result column="job_distributor" property="jobDistributor" />
        <result column="job_distribution_name" property="jobDistributionName" />
        <result column="job_distribution_time" property="jobDistributionTime" />
    </resultMap>
    <!--获取核查人员用户科室、姓名、角色-->
    <select id="getCheckUser" resultType="com.diagbot.dto.DeptCheckUserDTO">
        SELECT DISTINCT
        a.*, r.`code` wardCode,
        r.`name` wardName,
        m.`code` medoupCode,
        m.`name` medoupName
        FROM
        (
        SELECT DISTINCT
        a.id doctorId,
        a.username,
        a.linkman doctorName,
        c.dept_id deptId,
        d.dept_name deptName
        from
        sys_user a,sys_user_role b,sys_user_dept c ,bas_dept_info d
        where a.id = b.user_id and a.is_deleted = "N" and b.is_deleted = "N" and c.is_deleted = "N" and a.id = c.user_id
        and d.dept_id = c.dept_id and c.hospital_id = d.hospital_id
        and c.hospital_id = #{hospitalId}
        <if test="roleIds !=null and roleIds.size()!=0 ">
            and b.role_id in
            <foreach collection="roleIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>

        <if test="deptIds !=null and deptIds.size()!=0">
            and c.dept_id in
            <foreach collection="deptIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="deptStations !=null and deptStations.size()!=0">
            and d.station in
            <foreach collection="deptStations" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        order by c.dept_id
        ) a
        LEFT JOIN sys_region_dept rd ON rd.dept_id = a.deptId
        AND rd.is_deleted = 'N'
        LEFT JOIN sys_region r ON r.`code` = rd.region_code
        AND r.is_deleted = 'N'
        LEFT JOIN bas_doctor_info mu ON mu.doctor_id = a.username
        AND mu.is_deleted = 'N'
        LEFT JOIN sys_medoup m ON m.`code` = mu.group_id
        AND m.is_deleted = 'N'
        WHERE
        1 = 1
        <if test="region !=null and region.size()!=0 ">
            AND r.`code` IN
            <foreach collection="region" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="medoup !=null and medoup.size()!=0 ">
            AND m.`code` IN
            <foreach collection="medoup" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>

    </select>
    <!--核查人员通过id获取核查人员的核查任务数-->
    <select id="getDeptJobNumByUserId" resultType="com.diagbot.dto.DeptCheckUserDTO">
        SELECT
        b.check_id doctorId,
        count(*) AS jobNum,
        b.`status`
        FROM
        med_behospital_info a
        LEFT JOIN bas_doctor_info d ON d.doctor_id = a.doctor_id
        AND d.hospital_id = a.hospital_id
        AND d.is_deleted = 'N'
        LEFT JOIN med_check_info b ON a.behospital_code = b.behospital_code
        AND a.hospital_id = b.hospital_id
        LEFT JOIN sys_user u ON u.id = b.check_id
        AND u.is_deleted = 'N'
        LEFT JOIN bas_doctor_info doctor ON u.username = doctor.doctor_id
        AND doctor.is_deleted = 'N'
        where b.is_deleted = 'N' and a.is_deleted = 'N'   and b.`status` is not null
        <if test="jobType != null ">
            and b.job_type = #{jobType}
        </if>

        <if test="userList !=null and userList.size!=0">
            and b.check_id in
            <foreach collection="userList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="deptIds !=null and deptIds.size()!=0 and jobType ==0">
            and a.beh_dept_id in
            <foreach collection="deptIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="deptIds !=null and deptIds.size()!=0 and jobType ==2">
            and a.beh_dept_id not in
            <foreach collection="deptIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="jobType ==0">
            GROUP BY b.check_id,b.`status`
        </if>
        <if test="jobType ==1 or jobType ==2">
            GROUP BY b.check_id,b.`status`
        </if>
    </select>


    <!--获取待分配核查任务列表-->
    <select id="getCheckList" resultType="com.diagbot.dto.CheckJobDTO">
        SELECT
        a.behospital_code behospitalCode,
        a.beh_dept_name behospitalDept,
        a.doctor_name attendingDoctor,
        a.leave_hospital_date leaveHospitalDate,
        a.`name`,
        b.`value`,
        a.id,
        a.beh_dept_id behospitalDeptId,
        a.job_create_time jobCreateTime,
        a.wardCode,
        a.wardName,
        a.medoupCode,
        a.medoupName
        from
        (
        SELECT
        a.behospital_code,
        a.`name`,
        a.beh_dept_name,
        a.doctor_name,
        a.leave_hospital_date,
        a.hospital_id,
        a.beh_dept_id,
        b.job_create_time,
        b.id,
        a.wardCode,
        a.wardName,
        a.medoupCode,
        a.medoupName
        FROM
        (SELECT
        a.behospital_code,
        a.`name`,
        a.beh_dept_name,
        a.doctor_name,
        a.leave_hospital_date,
        a.hospital_id,
        a.beh_dept_id,
        a.ward_code wardCode,
        a.ward_name wardName,
        m.`code` medoupCode,
        m.`name` medoupName
        FROM
        med_behospital_info a
        LEFT JOIN bas_doctor_info mu ON mu.doctor_id = a.doctor_id
        AND mu.is_deleted = 'N'
        LEFT JOIN sys_medoup m ON m.`code` = mu.group_id
        AND m.is_deleted = 'N'
        WHERE
        a.is_deleted = 'N'
        <if test="behosDateStart != null and behosDateStart != ''">
            <![CDATA[ AND a.leave_hospital_date >= #{behosDateStart}]]>
        </if>
        <if test="behosDateEnd != null and behosDateEnd != ''">
            <![CDATA[ AND a.leave_hospital_date <= #{behosDateEnd}]]>
        </if>
        <if test="doctorName !=null and doctorName != ''">and a.doctor_name LIKE CONCAT('%',#{doctorName},'%')
        </if>
        <if test="behospitalCode !=null and behospitalCode != ''">
            and a.behospital_code LIKE CONCAT('%',#{behospitalCode},'%')
        </if>
        <if test="fileCode !=null and fileCode != ''">
            and a.file_code LIKE CONCAT('%',#{fileCode},'%')
        </if>
        <if test="name !=null and name != ''">
            and a.`name` LIKE CONCAT('%',#{name},'%')
        </if>

        <if test="wards !=null and wards.size()!=0 ">
            AND a.ward_code in
            <foreach collection="wards" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>

        <if test="doctorIds !=null and doctorIds.size()!=0 ">
            AND (a.doctor_id in
            <foreach collection="doctorIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
            <if test="depts !=null and depts.size()!=0 and(medoups!=null and medoups.size==0)">
                or a.beh_dept_id in
                <foreach collection="depts" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            )
        </if>
        ) a
        JOIN med_check_info b ON a.behospital_code = b.behospital_code
        AND a.hospital_id = b.hospital_id
        WHERE
        b.is_deleted = 'N'
        AND b.job_distribution_name IS NULL
        AND b.`status` = 0
        <if test="jobType !=null and jobType != ''">
            and b.job_type = #{jobType}
        </if>
        <if test="startTime != null and startTime != ''">
            <![CDATA[ AND b.job_create_time >= #{startTime}]]>
        </if>
        <if test="endTime != null and endTime != ''">
            <![CDATA[ AND b.job_create_time <= #{endTime}]]>
        </if>
        ) a
        JOIN med_behospital_type b on a.behospital_code = b.behospital_code
        and a.hospital_id = b.hospital_id and b.is_deleted = 'N' and  b.`value` is not null
        <if test="values !=null and values.size()!=0 ">
            and b.`value` in
            <foreach collection="values" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    </select>


    <!--获取指定核查人员的核查任务-->
    <select id="getCheckListByUserId" resultType="com.diagbot.dto.CheckJobDTO">
        SELECT
        a.behospital_code behospitalCode,
        a.beh_dept_name behospitalDept,
        a.doctor_name attendingDoctor,
        a.leave_hospital_date leaveHospitalDate,
        a.`name`,
        b.`value`,
        a.id,
        a.job_distributor jobDistributor,
        a.job_distribution_name jobDistributorName
        from
        (select
            a.behospital_code,
            a.`name`,
            a.beh_dept_name,
            a.doctor_name,
            a.leave_hospital_date,
            a.hospital_id,
            a.beh_dept_id,
            b.id,
            b.job_distributor,
            b.job_distribution_name
            from
            med_behospital_info a ,med_check_info b
            where a.behospital_code = b.behospital_code and a.hospital_id = b.hospital_id and a.is_deleted = 'N'
            and b.is_deleted = 'N' and b.`status` = 0
            <if test="jobType !=null and jobType != ''">
                and b.job_type = #{jobType}
            </if>
            <if test="startTime != null and startTime != ''">
                <![CDATA[ AND b.job_create_time >= #{startTime}]]>
            </if>
            <if test="endTime != null and endTime != ''">
                <![CDATA[ AND b.job_create_time <= #{endTime}]]>
            </if>

            <if test="behosDateStart != null and behosDateStart != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{behosDateStart}]]>
            </if>
            <if test="behosDateEnd != null and behosDateEnd != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{behosDateEnd}]]>
            </if>
            <if test="checkId !=null and checkId != ''">
                and b.check_id = #{checkId}
            </if>
            <if test="depts !=null and depts.size()!=0 and jobType==0">
                and a.beh_dept_id in
                <foreach collection="depts" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="depts !=null and depts.size()!=0 and jobType==2">
                and a.beh_dept_id not in
                <foreach collection="depts" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
        ) a LEFT JOIN med_behospital_type b on a.behospital_code = b.behospital_code
        and a.hospital_id = b.hospital_id and b.is_deleted = 'N'
    </select>
    <select id="getCheckWorkPage" resultType="com.diagbot.dto.CheckWorkDTO">
        SELECT
        mbi.behospital_code behospitalCode,
        mbi.`name`,
        mbi.file_code fileCode,
        mbi.sex,
        TIMESTAMPDIFF(
        DAY,
        ifnull(mhp.birthday, mbi.birthday),
        mbi.behospital_date
        ) AS birthday,
        mhp.age,
        mbi.ward_code wardCode,
        mbi.ward_name wardName,
        mbi.beh_dept_id deptId,
        mbi.beh_dept_name behDeptName,
        mbi.leave_hospital_date leaveHospitalDate,
        mhp.behospital_day_num + 0 behospitalDayNum,
        mbi.doctor_id doctorId,
        mbi.doctor_name doctorName,
        mqi.score_res scoreRes,
        mqi.`level`,
        mbi.diagnose,
        CASE
        WHEN mbt.behospital_type = '出院病人' THEN
        '6'
        WHEN mbt.behospital_type = '死亡病人' THEN
        '6'
        WHEN mbt.behospital_type = '危重病人' THEN
        '4'
        WHEN mbt.behospital_type = '抢救病人' THEN
        '5'
        WHEN mbt.behospital_type = '手术病人' THEN
        '2'
        WHEN mbt.behospital_type = '疑难病人' THEN
        '3'
        WHEN mbt.behospital_type = '输血病人' THEN
        '4'
        ELSE
        '0'
        END AS score,
        CASE
        WHEN ISNULL(mci.job_create_time) THEN
        '未生成'
        ELSE
        '已生成'
        END AS creatStatus,
        mci.job_create_time jobCreateTime,
        mci.job_creator jobCreator,
        mci.job_creator_name jobCreatorName,
        mci.check_time checkTime,
        mci.job_distribution_time jobDistributionTime,
        medoup.`code` medoupCode,
        medoup.`name` medoupName
        FROM
        med_behospital_info mbi
        LEFT JOIN med_home_page mhp ON mbi.behospital_code = mhp.behospital_code
        AND mbi.hospital_id = mhp.hospital_id
        AND mhp.is_deleted = 'N'
        LEFT JOIN bas_doctor_info doctor ON doctor.doctor_id = mbi.doctor_id
        AND doctor.is_deleted = 'N'
        LEFT JOIN sys_medoup medoup ON medoup.`code` = doctor.group_id
        AND medoup.is_deleted = 'N'
        LEFT JOIN med_check_info mci ON mbi.behospital_code = mci.behospital_code
        AND mbi.hospital_id = mci.hospital_id
        AND mci.is_deleted = 'N'
        LEFT JOIN med_qcresult_info mqi ON mbi.behospital_code = mqi.behospital_code
        AND mbi.hospital_id = mqi.hospital_id
        AND mqi.is_deleted = 'N'
        JOIN med_behospital_type mbt ON mbi.behospital_code = mbt.behospital_code
        AND mbi.hospital_id = mbt.hospital_id
        AND mbt.is_deleted = 'N'
        WHERE
        mbi.hospital_id=#{hospitalId} AND mbi.is_deleted = 'N' and mqi.score_res is not null
        AND mbi.qc_type_id != 0
        AND mci.check_id is null
        <if test="startDate != null">
            <![CDATA[ AND mbi.leave_hospital_date >= #{startDate}]]>
        </if>
        <if test="endDate != null">
            <![CDATA[ AND mbi.leave_hospital_date < #{endDate}]]>
        </if>
        <if test="creatStatus != null and creatStatus == '1'.toString()">
            AND mci.job_create_time is not null
            <if test="jobType != null and jobType != ''">
                AND mci.job_type=#{jobType}
            </if>
        </if>
        <if test="creatStatus != null and creatStatus == '0'.toString()">
            AND mci.job_create_time is null
        </if>
        <if test="name != null and name != ''">
            AND mbi.`name` like CONCAT('%',#{name},'%')
        </if>
        <if test="behospitalCode != null and behospitalCode != ''">
            AND mbi.behospital_code like CONCAT('%',#{behospitalCode},'%')
        </if>
        <if test="fileCode != null and fileCode != ''">
            AND mbi.file_code like CONCAT('%',#{fileCode},'%')
        </if>
        <if test="smallDay != null">
            <![CDATA[ AND mhp.behospital_day_num >= #{smallDay}]]>
        </if>
        <if test="bigDay != null">
            <![CDATA[ AND mhp.behospital_day_num <= #{bigDay}]]>
        </if>
        <if test="doctorName != null and doctorName != ''">
            AND mbi.doctor_name LIKE CONCAT( '%', #{doctorName}, '%' )
        </if>
        <if test="diagnose != null and diagnose != ''">
            AND mbi.diagnose LIKE CONCAT( '%', #{diagnose}, '%' )
        </if>
        <if test="level != null and level != ''">
            AND mqi.`level`= #{level}
        </if>
        <if test="isPlacefile != null and isPlacefile != ''">
            AND mbi.is_placefile = #{isPlacefile}
        </if>
        <if test="values !=null and values.size > 0">
            and mbt.`value` in
            <foreach collection="values" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>

        AND( 1=1
        <if test="wards !=null and wards.size > 0 and depts.size ==0">
            AND mbi.ward_code IN
            <foreach collection="wards" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="depts !=null and depts.size > 0">
            AND mbi.beh_dept_id in
            <foreach collection="depts" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="medoups !=null and medoups.size > 0">
            AND medoup.`code` IN
            <foreach collection="medoups" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>

        <if test="doctorIds !=null and doctorIds.size > 0 and orderDepts !=null and orderDepts.size>0">
            OR (mbi.beh_dept_id in
            <foreach collection="orderDepts" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
            and mbi.doctor_id IN
            <foreach collection="doctorIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
            )
        </if>
        )
    </select>

    <!--通过住院号，取消核查任务-->
    <update id="deleteBatchCodes">
        <foreach collection="list" item="item"  separator=";">
            delete from
            med_check_info
            where behospital_code = #{item} and is_deleted = 'N'
        </foreach>
    </update>

    <select id="checkedRecordList" resultType="com.diagbot.dto.CheckedRecordListDTO">
        SELECT t1.*,SUM(if(t2.behospital_code is null,0,1)) as casesEntryNum
        from
        (select
        b.behospital_code as behospitalCode,
        a.`status` as status,
        b.ward_code wardCode,
        b.ward_name wardName,
        b.beh_dept_id AS behDeptId,
        b.beh_dept_name AS behDeptName,
        medoup.`code` medoupCode,
        medoup.`name` medoupName,
        b.`name` as name,
        b.file_code as fileCode,
        b.doctor_name as doctorName,
        b.leave_hospital_date as leaveHospitalDate,
        a.job_create_time as jobCreateTime,
        a.job_distribution_time as jobDistributionTime,
        a.check_id as checkId,
        a.check_name as checkName,
        a.check_time as checkTime,
        c.`level` as level,
        c.score_res as scoreRes,
        CASE WHEN a.job_type='0' THEN '科室任务'
        WHEN a.job_type='1' THEN '质控科任务'
        WHEN a.job_type='2' THEN '院级任务' END AS jobTypeName,
        <!-- 张三是A科室的监管员，李四是A科室和B科室的普通质控员，那么张三在核查任务列表中不能看到李四B科室的数据  -->
        case when a.job_type='0' and FIND_IN_SET(b.beh_dept_id,#{currentDeptIds})=0 then 0 else 1 end as isDel
        from med_check_info a join med_behospital_info b on a.hospital_id=b.hospital_id and
        a.behospital_code=b.behospital_code
        LEFT JOIN bas_doctor_info doctor ON doctor.doctor_id = b.doctor_id
        AND doctor.is_deleted = 'N'
        LEFT JOIN sys_medoup medoup ON medoup.`code` = doctor.group_id
        AND medoup.is_deleted = 'N'
        JOIN med_qcresult_info c on b.hospital_id=c.hospital_id and b.behospital_code=c.behospital_code and
        b.is_deleted='N' and c.is_deleted = 'N'
        where a.hospital_id=#{hospitalId} and a.is_deleted='N'
        and b.hospital_id=#{hospitalId} and b.is_deleted='N'
        and c.hospital_id=#{hospitalId}
        and a.check_id in
        <foreach collection="currentGeneralUserIds" item="currentGeneralUserId" separator="," close=")" open="(">
            #{currentGeneralUserId}
        </foreach>
        <if test="status!=null">
            and a.`status`=#{status}
        </if>
        <if test="depts !=null and depts.size()!=0 ">
            and b.beh_dept_id in
            <foreach collection="depts" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="wards !=null and wards.size()!=0 ">
            AND b.ward_code in
            <foreach collection="wards" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="medoups !=null and medoups.size()!=0 ">
            AND  medoup.`code` in
            <foreach collection="medoups" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>

        <if test="name!=null and name!=''">
            and b.`name` like concat('%',#{name},'%')
        </if>
        <if test="behospitalCode!=null and behospitalCode!=''">
            and b.behospital_code like concat('%',#{behospitalCode},'%')
        </if>
        <if test="behospitalCode!=null and behospitalCode!=''">
            and b.behospital_code like concat('%',#{behospitalCode},'%')
        </if>
        <if test="doctorName!=null and doctorName!=''">
            and b.file_code like concat('%',#{fileCode},'%')
        </if>
        <if test="jobDistributionTimeStart!=null">
            and a.job_distribution_time >= #{jobDistributionTimeStart}
        </if>
        <if test="jobDistributionTimeEnd!=null">
            and #{jobDistributionTimeEnd} >= a.job_distribution_time
        </if>
        <if test="checkName!=null and checkName!=''">
            and a.check_name like concat('%',#{checkName},'%')
        </if>
        <if test="level!=null and level!=''">
            and c.`level`=#{level}
        </if>
        <if test="checkJobTypes!=null and checkJobTypes.size>0">
            and a.job_type in
            <foreach collection="checkJobTypes" item="checkJobType" separator="," close=")" open="(">
                #{checkJobType}
            </foreach>
        </if>
        ) t1 left join med_qcresult_detail t2 on t1.behospitalCode=t2.behospital_code and t2.hospital_id=#{hospitalId} and t2.is_deleted='N'

        group by t1.behospitalCode
    </select>

</mapper>
