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

    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.kcim.dao.model.IncomeCollection" id="incomeCollectionMap">
        <result property="id" column="id"/>
        <result property="year" column="year"/>
        <result property="month" column="month"/>
        <result property="departmentCode" column="department_code"/>
        <result property="departmentName" column="department_name"/>
        <result property="responsibilityCode" column="responsibility_code"/>
        <result property="responsibilityName" column="responsibility_name"/>
        <result property="accountingCode" column="accounting_code"/>
        <result property="accountingName" column="accounting_name"/>
        <result property="productCode" column="product_code"/>
        <result property="productName" column="product_name"/>
        <result property="isDirectIncome" column="is_direct_income"/>
        <result property="amount" column="amount"/>
        <result property="fileId" column="file_id"/>
        <result property="hospId" column="hosp_id"/>
        <result property="createTime" column="create_time"/>
        <result property="deleteTime" column="delete_time"/>
    </resultMap>

    <select id="getCollectList" resultType="com.kcim.vo.CollectedVO">
        select * from cost_income_collection
        where delete_time = 0 and hosp_id = #{collectDTO.hospId}
        <if test="collectDTO.date != null and collectDTO.date != ''">
            and `year` = YEAR(concat(#{collectDTO.date},'01')) and `month` = MONTH(concat(#{collectDTO.date},'01'))
        </if>
        <if test="collectDTO.responsibilityCode != null and collectDTO.responsibilityCode != ''">
            and responsibility_code = #{collectDTO.responsibilityCode}
        </if>
        <if test="collectDTO.productCode != null and collectDTO.productCode != ''">
            and product_code = #{collectDTO.productCode}
        </if>
        <if test="collectDTO.accountFilter != null and collectDTO.accountFilter != ''">
            and (accounting_code like concat('%',#{collectDTO.accountFilter,jdbcType=VARCHAR},'%') or accounting_name like concat('%',#{collectDTO.accountFilter,jdbcType=VARCHAR},'%'))
        </if>
        limit #{collectDTO.current},#{collectDTO.pageSize};
    </select>

    <select id="getCollectListCount" resultType="java.lang.Integer">
        select count(*) from cost_income_collection
        where delete_time = 0 and hosp_id = #{collectDTO.hospId}
        <if test="collectDTO.date != null and collectDTO.date != ''">
            and `year` = YEAR(concat(#{collectDTO.date},'01')) and `month` = MONTH(concat(#{collectDTO.date},'01'))
        </if>
        <if test="collectDTO.responsibilityCode != null and collectDTO.responsibilityCode != ''">
            and responsibility_code = #{collectDTO.responsibilityCode}
        </if>
        <if test="collectDTO.productCode != null and collectDTO.productCode != ''">
            and product_code = #{collectDTO.productCode}
        </if>
    </select>

    <select id="getTotalAmount" resultType="java.math.BigDecimal">
        select IFNULL(sum(amount),0) as totalAmount from cost_income_collection
        where delete_time = 0 and hosp_id = #{collectDTO.hospId}
        <if test="collectDTO.date != null and collectDTO.date != ''">
            and `year` = YEAR(concat(#{collectDTO.date},'01')) and `month` = MONTH(concat(#{collectDTO.date},'01'))
        </if>

    </select>
    <select id="getResponsibilityTotalAmount" resultType="java.math.BigDecimal">
        select IFNULL(sum(amount),0) as totalAmount from cost_income_collection
        where delete_time = 0 and hosp_id = #{collectDTO.hospId}
        <if test="collectDTO.date != null and collectDTO.date != ''">
            and `year` = YEAR(concat(#{collectDTO.date},'01')) and `month` = MONTH(concat(#{collectDTO.date},'01'))
        </if>
        <if test="collectDTO.responsibilityCode != null and collectDTO.responsibilityCode != ''">
            and responsibility_code = #{collectDTO.responsibilityCode}
        </if>
        <if test="collectDTO.productCode != null and collectDTO.productCode != ''">
            and product_code = #{collectDTO.productCode}
        </if>
    </select>

    <select id="getCountByResponseAndAccounts" resultType="java.math.BigDecimal">
        select IFNULL(sum(amount), 0) as totalAmount
        from cost_income_collection
        where delete_time = 0 and hosp_id = #{hospId} and responsibility_code = #{responsibilityCode}
        <if test="date != null and date != ''">
            and `year` = YEAR(concat(#{date},'01')) and `month` = MONTH(concat(#{date},'01'))
        </if>
        and accounting_code in
        <foreach collection="accountingCodes" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>
    <select id="getResponsibility" resultType="com.kcim.vo.CodeAndNameVO">
        select responsibility_code as code ,responsibility_name as name from cost_income_collection
        where delete_time = 0 and hosp_id = #{hospId}
        <if test="responsibilityCode != null and responsibilityCode != ''">
            and responsibility_code = #{responsibilityCode}
        </if>
        <if test="date != null and date != ''">
            and `year` = YEAR(concat(#{date},'01')) and `month` = MONTH(concat(#{date},'01'))
        </if>
    </select>
    <select id="getAccount" resultType="com.kcim.vo.CodeAndNameVO">
        select accounting_code as code ,accounting_name as name from cost_income_collection
        where delete_time = 0 and hosp_id = #{hospId}

        <if test="date != null and date != ''">
            and `year` = YEAR(concat(#{date},'01')) and `month` = MONTH(concat(#{date},'01'))
        </if>
    </select>
    <select id="getCountAccountAndResponsibilities" resultType="java.math.BigDecimal">
        select IFNULL(sum(amount), 0) as totalAmount
        from cost_income_collection
        where delete_time = 0 and hosp_id = #{hospId} and accounting_code = #{code}
        <if test="date != null and date != ''">
            and `year` = YEAR(concat(#{date},'01')) and `month` = MONTH(concat(#{date},'01'))
        </if>
        and responsibility_code in
        <foreach collection="responsibilityCodes" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>
    <select id="getCountByResponsibilitiesAndAccounts" resultType="java.math.BigDecimal">
        select IFNULL(sum(amount), 0) as totalAmount
        from cost_income_collection
        where delete_time = 0 and hosp_id = #{hospId} and responsibility_code in
        <foreach  collection="responsibilityCodes" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="date != null and date != ''">
            and `year` = YEAR(concat(#{date},'01')) and `month` = MONTH(concat(#{date},'01'))
        </if>
        and accounting_code in
        <foreach collection="accountingCodes" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="getResponsibilitiesAccounts" resultType="com.kcim.dao.model.IncomeCollection">
        select responsibility_code,IFNULL(sum(amount), 0) as amount
        from cost_income_collection
        where delete_time = 0
            and hosp_id = #{hospId}
            and `year` =#{year}
            and `month` = #{month}
        GROUP BY
        responsibility_code
    </select>


</mapper>