<?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.CostIncomeGroupMapper">

    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.kcim.dao.model.CostIncomeGroup" id="costIncomeGroupMap">
        <result property="id" jdbcType="BIGINT" column="id"/>
        <result property="openDepartmentCode" column="open_department_code"/>
        <result property="openDepartmentName" column="open_department_name"/>
        <result property="openResponsibilityCode" column="open_responsibility_code"/>
        <result property="openResponsibilityName" column="open_responsibility_name"/>
        <result property="startDepartmentCode" column="start_department_code"/>
        <result property="startDepartmentName" column="start_department_name"/>
        <result property="startResponsibilityCode" column="start_responsibility_code"/>
        <result property="startResponsibilityName" column="start_responsibility_name"/>
        <result property="productCode" column="product_code"/>
        <result property="productName" column="product_name"/>
        <result property="accountCode" column="account_code"/>
        <result property="accountName" column="account_name"/>
        <result property="openDepartmentAmount" column="open_department_amount"/>
        <result property="startDepartmentAmount" column="start_department_amount"/>
        <result property="amount" column="amount"/>
        <result property="hospId" column="hosp_id"/>
        <result property="fileId" column="file_id"/>
        <result property="doctorNumber" column="doctor_number"/>
        <result property="doctorName" column="doctor_name"/>
        <result property="patientId" column="patient_id"/>
        <result property="outpatientId" column="outpatient_id"/>
        <result property="patientName" column="patient_name"/>
        <result property="patientFee" column="patient_fee"/>
        <result property="receiptFee" column="receipt_fee"/>
        <result property="totalNumber" column="total_number"/>
        <result property="unit" column="unit"/>
        <result property="feeDatetime" column="fee_datetime"/>
        <result property="afterIncomeGroup" column="after_income_group"/>
        <result property="dateYear" column="date_year"/>
        <result property="dateMonth" column="date_month"/>
        <result property="createTime" column="create_time"/>
        <result property="deleteTime" column="delete_time"/>
    </resultMap>

    <sql id="Base_Column_List">
        id
        , open_department_code, open_department_name, open_responsibility_code, open_responsibility_name, start_department_code, start_department_name, start_responsibility_code,
    start_responsibility_name, product_code , product_name, account_code, account_name, open_department_amount, start_department_amount,amount,hosp_id,file_id,
    doctor_number ,doctor_name,patient_id,outpatient_id,patient_name, patient_fee ,receipt_fee ,total_number ,unit ,fee_datetime,date_year,date_month,create_time,delete_time
    </sql>
    <update id="reducingHistory">
        update cost_income_group set delete_time = 0
        where file_id = #{id}
        and hosp_id = #{hospId}
        and id in
        <foreach close=")" collection="list" item="item" open="(" separator=",">
            #{item}
        </foreach>

    </update>

    <select id="countMoney" resultType="com.kcim.vo.CostIncomeGroupAllAmountVO">
        select
        <include refid="Base_Column_List"/>
        ,group_concat(amount) as allMoney
        from cost_income_group
        where id IN
        <foreach close=")" collection="idList" item="item" open="(" separator=",">
            #{item}
        </foreach>
        group by open_department_code , start_department_code , product_code
    </select>

    <select id="getCollections" resultType="com.kcim.vo.CollectionVO">
        select file_id, date_year as `year`, date_month as `month`, sum(amount) as amount
        from cost_income_group
        where hosp_id = #{hospId} and delete_time = 0
        <if test="date != null and date != ''">
            and date_year = YEAR(concat(#{date},'01')) and date_month = MONTH(concat(#{date},'01'))
        </if>
        group by date_month, date_year
        limit #{startIndex},#{pageSize}
    </select>

    <select id="getCollectionCount" resultType="java.lang.Integer">

        select count(*) from (
        select id
        from cost_income_group
        where hosp_id = #{hospId} and delete_time = 0
        <if test="date != null and date != ''">
            and date_year = YEAR(concat(#{date},'01')) and date_month = MONTH(concat(#{date},'01'))
        </if>
        group by date_month, date_year
        ) t
    </select>
    <select id="selectListAndMoney" resultType="com.kcim.vo.CostIncomeGroupAllAmountBO">
        select *   , group_concat(amount) as allMoney
        from cost_income_group
        where date_year = #{year}
          and date_month = #{month}
          and hosp_id = #{hospId}
        and delete_time = 0


    </select>
    <select id="getOpenDepartment" resultType="com.kcim.vo.CommonDepartVo">
        SELECT DISTINCT
            open_department_code as departmentCode,
            open_department_name as departmentName
        FROM
            cost_income_group
        WHERE
            hosp_id = #{hospId}
          and date_year = #{year}
          and date_month = #{month}
          AND delete_time = 0
        <if test="departmentName != null and departmentName != ''">
            and open_department_name  like concat('%',#{departmentName,jdbcType=VARCHAR},'%')
        </if>
    </select>
    <select id="getStartDepartment" resultType="com.kcim.vo.CommonDepartVo">
        SELECT DISTINCT
            start_department_code as departmentCode,
            start_department_name as departmentName
        FROM
            cost_income_group
        WHERE
            hosp_id = #{hospId}
          and date_year = #{year}
          and date_month = #{month}
          AND delete_time = 0
        <if test="departmentName != null and departmentName != ''">
            and start_department_name  like concat('%',#{departmentName,jdbcType=VARCHAR},'%')
        </if>
    </select>
    <select id="selectHistoryList" resultType="com.kcim.dao.model.CostIncomeGroup">
        select  *
        from cost_income_group
        where  hosp_id = #{hospId}
        and delete_time != 0
        and file_id = #{id}
    </select>


</mapper>