<?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.kcim.dao.mapper.ImportPatientItemMapper">

    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.kcim.dao.model.ImportPatientItem" id="importPatientItemMap">
        <result property="id" column="id"/>
        <result property="hospId" column="hosp_id"/>
        <result property="computeDate" column="compute_date"/>
        <result property="visitNo" column="visit_no"/>
        <result property="patientNo" column="patient_no"/>
        <result property="name" column="name"/>
        <result property="itemCode" column="item_code"/>
        <result property="itemName" column="item_name"/>
        <result property="itemTypeCode" column="item_type_code"/>
        <result property="itemType" column="item_type"/>
        <result property="orderDepartmentCode" column="order_department_code"/>
        <result property="orderDepartmentName" column="order_department_name"/>
        <result property="orderUserCode" column="order_user_code"/>
        <result property="orderUserName" column="order_user_name"/>
        <result property="executeDepartmentCode" column="execute_department_code"/>
        <result property="executeDepartmentName" column="execute_department_name"/>
        <result property="executeUserCode" column="execute_user_code"/>
        <result property="executeUserName" column="execute_user_name"/>
        <result property="num" column="num"/>
        <result property="price" column="price"/>
        <result property="amount" column="amount"/>
        <result property="executeTime" column="execute_time"/>
        <result property="createUser" column="create_user"/>
        <result property="createTime" column="create_time"/>
        <result property="updateUser" column="update_user"/>
        <result property="updateTime" column="update_time"/>
        <result property="deleteUser" column="delete_user"/>
        <result property="deleteTime" column="delete_time"/>
        <result property="delFlag" column="del_flag"/>
        <result property="calculateFlag" column="calculate_flag"/>
    </resultMap>
    <update id="reducingPatientItemByFileId">
        update import_patient_item
        set del_flag    = 0,
            delete_time = null,
            delete_user = null,
            update_time = #{date},
            update_user = #{userId}
        where del_flag = 1
          and hosp_id = #{hospId}
          and file_id = #{fileId}
          and compute_date = #{computeDate}
    </update>
    <select id="getAllGroupByCompute" resultType="com.kcim.dao.model.dto.PatientItemVo">
        SELECT visit_no,
               item_code,
               item_name,
               item_type_code,
               item_type,
               order_code,
               order_name,
               execute_department_code,
               execute_user_code,
               sum(num)    as num,
               sum(amount) as amount
        FROM import_patient_item
        WHERE del_flag = 0
          AND compute_date = #{computeDate}
          and hosp_id = #{hospId}
          and calculate_flag = 0
        GROUP BY visit_no, execute_department_code, item_code, order_code limit 500;
    </select>
    <select id="getAllGroupByOrder" resultType="com.kcim.dao.model.dto.PatientItemVo">
        SELECT visit_no,
               item_code,
               item_name,
               item_type_code,
               item_type,
               execute_department_code,
               execute_user_code,
               sum(num)    as num,
               sum(amount) as amount
        FROM import_patient_item
        WHERE del_flag = 0
          AND compute_date = #{computeDate}
          and hosp_id = #{hospId}
        GROUP BY visit_no, execute_department_code, item_code;


    </select>
    <select id="getPatientItemPatientInfo" resultType="com.kcim.vo.PatientInfoVo">
        select DISTINCT patient_no, `name`
        from import_patient_item
        where compute_date = #{computeDate}
        and hosp_id = #{hospId}
        and del_flag = 0
        <if test="filter != null and filter != ''">
            and (patient_no like concat('%',#{filter,jdbcType=VARCHAR},'%') or `name` like
            concat('%',#{filter,jdbcType=VARCHAR},'%') )
        </if>

    </select>
    <select id="getByDepartGroupComputeDateItem"
            resultType="com.kcim.dao.model.dto.PatientItemDepartmentGroupVo">

        select id, item_code, item_name, execute_department_code, sum(num) as num, sum(amount) as amount
        from import_patient_item
        where hosp_id = #{hospId}
          and del_flag = 0
          and compute_date = #{computeDate}
          and item_type_code = 3
        GROUP BY `item_code`, execute_department_code


    </select>
    <select id="getItemGroupCount" resultType="java.lang.Long">

        select count(countSum)
        from (select count(1) as countSum
              from import_patient_item
              where hosp_id = #{hospId}
                and del_flag = 0
                and compute_date = #{computeDate}
                and item_type_code = 3
              GROUP BY `item_code`, execute_department_code) a

    </select>
    <select id="getPatientItemGroupList" resultType="com.kcim.dao.model.dto.PatientItemDepartmentGroupVo">
        select id,
               visit_no,
               name,
               patient_no,
               item_code,
               item_name,
               execute_department_code,
               execute_department_name,
               sum(num)    as num,
               sum(amount) as amount
        from import_patient_item
        where hosp_id = #{hospId}
          and del_flag = 0
          and compute_date = #{computeDate}
          and item_type_code = 3
--           and calculate_flag= 0
        GROUP BY `item_code`, execute_department_code, visit_no
    </select>
    <select id="getPatientItemGroupCount" resultType="java.lang.Long">

        select count(a.countSum)
        from (select count(1) as countSum
              from import_patient_item b

              where b.hosp_id = #{hospId}
                and b.del_flag = 0
                and b.compute_date = #{computeDate}
                and b.item_type_code = 3
              GROUP BY b.item_code, b.execute_department_code, b.visit_no) a
    </select>
    <select id="getDepartAmountGroup" resultType="com.kcim.dao.model.dto.PatientItemDepartmentGroupVo">
        select sum(amount) amount, execute_department_code
        from import_patient_item
        WHERE hosp_id = #{hospId}
          and del_flag = 0
          and compute_date = #{computeDate}
          and item_type_code = 3
        GROUP BY execute_department_code
    </select>
    <select id="getDrugCount" resultType="java.lang.Long">
        select count(a.countSum)
        from (SELECT count(1) as countSum
              FROM `import_patient_item`
              where hosp_id = #{hospId}
                and compute_date = #{computeDate}
                and item_type_code in ('1', '2')
                and del_flag = 0
              GROUP BY visit_no, item_code, item_type_code, execute_department_code) a
    </select>
    <select id="getByNoCalculateComputeDateDrugMaterial"
            resultType="com.kcim.dao.model.dto.PatientItemDepartmentGroupVo">

        SELECT visit_no, item_code, item_type,item_type_code, item_name, execute_department_code, sum(amount) as amount, sum(num) as num
        FROM `import_patient_item`
        where hosp_id = #{hospId}
          and compute_date = #{computeDate}
          and item_type_code in ('1', '2')
          and del_flag = 0
        GROUP BY visit_no, item_code, item_type_code, execute_department_code
    </select>


</mapper>