<?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.docus.server.zz.infrastructure.mapper.ZzDeptMapper">

    <!--根据更新时间分页查询-->
    <select id="page" resultType="java.util.Map">
        SELECT DISTINCT
        '6' AS SYS_CODE,
        ZYH AS ZYH,
        JZH AS FVISIT_ID,
        FILE_NAME AS REPORT_NAME,
        '' AS BGLX,
        '1503577916225949698' AS ASSORTID,
        EXAM_NO AS RP_EXAM_NO,
        1 AS PATH_TYPE,
        FILE_PATHFILE_PATH AS FILE_PATH,
        0 AS ISDELETE,
        0 AS state,
        '' AS jch,
        '' AS checktime,
        date_format(AFFIRM_TIME, '%Y-%m-%d %H:%i:%s') AS MODIFYTIME
        FROM
        V_JSWZH_LISRECORD
        WHERE
        1 = 1
        AND AFFIRM_TIME <![CDATA[ > ]]> #{startDate}
        AND AFFIRM_TIME <![CDATA[ <= ]]> #{endDate}
        ORDER BY
        date_format(AFFIRM_TIME, '%Y-%m-%d %H:%i:%s') DESC
        LIMIT #{offset}, #{pageSize}
    </select>

    <!--根据jzh取得所需采集的批量数据-->
    <select id="BatchForJZH" resultType="java.util.Map">
        SELECT
        '6' AS SYS_CODE,
        ZYH AS ZYH,
        JZH AS FVISIT_ID,
        FILE_NAME AS REPORT_NAME,
        '' AS BGLX,
        '1503577916225949698' AS ASSORTID,
        EXAM_NO AS RP_EXAM_NO,
        1 AS PATH_TYPE,
        FILE_PATHFILE_PATH AS FILE_PATH,
        0 AS ISDELETE,
        0 AS state,
        '' AS jch,
        '' AS checktime,
        date_format(AFFIRM_TIME, '%Y-%m-%d %H:%i:%s') AS MODIFYTIME
        FROM
        V_JSWZH_LISRECORD
        WHERE
        1 = 1
        AND JZH IN
        <foreach item="item" index="index" collection="jzhs" open="(" separator="," close=")">
            #{item}
        </foreach>

    </select>
    <!--记账号+住院次数-->
    <select id="BatchForJZHAndAdmissTimes" resultType="java.util.Map">
        SELECT
        '6' AS SYS_CODE,
        ZYH AS ZYH,
        JZH AS FVISIT_ID,
        FILE_NAME AS REPORT_NAME,
        '' AS BGLX,
        '1503577916225949698' AS ASSORTID,
        EXAM_NO AS RP_EXAM_NO,
        1 AS PATH_TYPE,
        FILE_PATHFILE_PATH AS FILE_PATH,
        0 AS ISDELETE,
        0 AS state,
        '' AS jch,
        '' AS checktime,
        date_format(AFFIRM_TIME, '%Y-%m-%d %H:%i:%s') AS MODIFYTIME
        FROM
        V_JSWZH_LISRECORD
        WHERE
        1 = 1
        and (
        <foreach item="item" index="index" collection="datas" open="(" separator="," close=")">
            (JZH =#{item.FVISIT_ID} and ADMISS_TIMES=#{item.ADMISS_TIMES}) or
        </foreach>
        )
    </select>
    <select id="BatchForZYHAndAdmissTimes" resultType="java.util.Map">
        SELECT
        '6' AS SYS_CODE,
        ZYH AS ZYH,
        JZH AS FVISIT_ID,
        FILE_NAME AS REPORT_NAME,
        '' AS BGLX,
        '1503577916225949698' AS ASSORTID,
        EXAM_NO AS RP_EXAM_NO,
        1 AS PATH_TYPE,
        FILE_PATHFILE_PATH AS FILE_PATH,
        0 AS ISDELETE,
        0 AS state,
        '' AS jch,
        '' AS checktime,
        date_format(AFFIRM_TIME, '%Y-%m-%d %H:%i:%s') AS MODIFYTIME
        FROM
        V_JSWZH_LISRECORD
        WHERE
        1 = 1
        and (
        <foreach item="item" index="index" collection="datas" open="(" separator="," close=")">
            (ZYH =#{item.ZYH} and ADMISS_TIMES=#{item.ADMISS_TIMES}) or
        </foreach>
        )
    </select>
    <select id="BatchForEXAMTIME" resultType="java.util.Map">
        SELECT
        '6' AS SYS_CODE,
        ZYH AS ZYH,
        JZH AS FVISIT_ID,
        FILE_NAME AS REPORT_NAME,
        '' AS BGLX,
        '1503577916225949698' AS ASSORTID,
        EXAM_NO AS RP_EXAM_NO,
        1 AS PATH_TYPE,
        FILE_PATHFILE_PATH AS FILE_PATH,
        0 AS ISDELETE,
        0 AS state,
        '' AS jch,
        '' AS checktime,
        date_format(AFFIRM_TIME, '%Y-%m-%d %H:%i:%s') AS MODIFYTIME
        FROM
        V_JSWZH_LISRECORD
        WHERE
        1 = 1
        and (
        <foreach item="item" index="index" collection="datas" open="(" separator="," close=")">
            (ZYH =#{item.ZYH} and EXAM_TIME <![CDATA[ >= ]]>  STR_TO_DATE(#{item.admiss_date})
            <if test="item.dis_date != null">
                and EXAM_TIME <![CDATA[ <= ]]> STR_TO_DATE(#{item.dis_date})
            </if>
        </foreach>
        )
    </select>

    <!--  mysql
    <select id="getDeptListView" resultType="com.docus.server.sys.common.pojo.dto.DeptDTO">
        SELECT `update_time` as update_time ,`dept_code` as dept_code,`dept_name` as dept_name FROM `his`.`his_dept`
        WHERE  update_time between #{startDate}  and  #{endDate}
        order by update_time asc
            LIMIT (#{pageNum}  - 1) *  #{pageSize} , #{pageSize}
    </select>
    -->


    <!--  mssqsql
    <select id="getDeptListView" resultType="com.docus.server.sys.common.pojo.dto.DeptDTO">
            select top pageSize `update_time` as update_time ,`dept_code` as dept_code,`dept_name` as dept_name
            from (select row_number()
            over(order by sno asc) as rownumber,*
            from student) temp_row
            where rownumber>((pageNum-1)*pageSize);
    </select>
    -->

    <!--  oracle
    <select id="getDeptListView" resultType="com.docus.server.sys.common.pojo.dto.DeptDTO">
            SELECT * FROM
            (SELECT tt.*,ROWNUM AS RN FROM
            (SELECT t.* FROM ${tableName} t
            where 1=1
            ORDER BY t.createTime DESC,t.id ) tt
            WHERE tt.ROWNUM <= #{pageNum}*#{pageSize}
            ) rs
            WHERE rs.RN > #{pageNum-1}*#{pageSize}
    </select>
    -->


</mapper>
