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

    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.kcim.dao.model.CostCostingGroup" id="costCostingGroupMap">
        <result property="id" column="id"/>
        <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="otherResponsibilityCode" column="other_responsibility_code"/>
        <result property="otherResponsibilityName" column="other_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="amount" column="amount"/>
        <result property="hospId" column="hosp_id"/>
        <result property="fileId" column="file_id"/>
        <result property="afterCostGroup" column="after_cost_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>
    <update id="reducingHistory">
        update cost_costing_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="queryStartAllocation" resultType="com.kcim.vo.CostingGroupStartVO">
        select
        ccg.id as id,
        ccg.date_year as year,
        ccg.date_month as month,
        ccg.amount as amount,
        ccg.department_code as departCode,
        ccg.department_name as departName,
        ccg.responsibility_code as responsibilityCode,
        cr.responsibility_name as responsibilityName,
        ccg.account_code as accountCode,
        ccg.account_name as accountName,
        ccg.product_code as productCode,
        cp.product_name as productName,
        ccg.file_id as fileId
        from cost_costing_group ccg
        left join cost_responsibility cr
        on ccg.responsibility_code = cr.responsibility_code and ccg.hosp_id = cr.hosp_id
        left join cost_accounting ca on ccg.account_code = ca.accounting_code and ccg.hosp_id = ca.hosp_id
        left join cost_product cp
        on cp.delete_time = 0 and ccg.product_code = cp.product_code and ccg.hosp_id = cp.hosp_id
        where ccg.delete_time = 0
        and ccg.date_year = #{year}
        and ccg.date_month = #{month}
        and ccg.hosp_id = #{hospId}
        <if test="responsibilityCode != null and responsibilityCode != ''">
            and ccg.responsibility_code = #{responsibilityCode}
        </if>
        <if test="accountCode != null and accountCode != ''">
            and ccg.account_code = #{accountCode}
        </if>
        <if test="departmentCode != null and departmentCode != ''">
            and ccg.department_code = #{departmentCode}
        </if>
        <if test="filter != null and filter != ''">
            and (ccg.product_code like concat('%',#{filter,jdbcType=VARCHAR},'%') or ccg.product_name like concat('%',#{filter,jdbcType=VARCHAR},'%') )
        </if>


        and cr.delete_time = 0
        and ca.delete_time = 0
        limit #{startIndex},#{pageSize}
    </select>
    <select id="queryStartAllocationCount" resultType="java.lang.Integer">
        select
        count(*)
        from cost_costing_group ccg
        left join cost_responsibility cr
        on ccg.responsibility_code = cr.responsibility_code and ccg.hosp_id = cr.hosp_id
        left join cost_accounting ca on ccg.account_code = ca.accounting_code and ccg.hosp_id = ca.hosp_id
        left join cost_product cp
        on cp.delete_time = 0 and ccg.product_code = cp.product_code and ccg.hosp_id = cp.hosp_id
        where ccg.delete_time = 0
        and ccg.date_year = #{year}
        and ccg.date_month = #{month}
        and ccg.hosp_id = #{hospId}
        <if test="responsibilityCode != null and responsibilityCode != ''">
            and ccg.responsibility_code = #{responsibilityCode}
        </if>
        <if test="accountCode != null and accountCode != ''">
            and ccg.account_code = #{accountCode}
        </if>
        <if test="departmentCode != null and departmentCode != ''">
            and ccg.department_code = #{departmentCode}
        </if>
        <if test="filter != null and filter != ''">
            and (ccg.product_code like concat('%',#{filter,jdbcType=VARCHAR},'%') or ccg.product_code like concat('%',#{filter,jdbcType=VARCHAR},'%') )
        </if>
        and cr.delete_time = 0
        and ca.delete_time = 0
    </select>
    <select id="queryStartAllocationTotalAmount" resultType="java.math.BigDecimal">
        select
        sum(ccg.amount)
        from cost_costing_group ccg
        left join cost_responsibility cr
        on ccg.responsibility_code = cr.responsibility_code and ccg.hosp_id = cr.hosp_id
        left join cost_accounting ca on ccg.account_code = ca.accounting_code and ccg.hosp_id = ca.hosp_id
        left join cost_product cp
        on cp.delete_time = 0 and ccg.product_code = cp.product_code and ccg.hosp_id = cp.hosp_id
        where ccg.delete_time = 0
        and ccg.date_year = #{year}
        and ccg.date_month = #{month}
        and ccg.hosp_id = #{hospId}

        and cr.delete_time = 0
        and ca.delete_time = 0
    </select>
    <select id="queryAllocation" resultType="com.kcim.vo.AllocationVO">
        select date_year as `year`,date_month as `month`,IFNULL(sum(amount),0) as amount,false as isAllocation
        from cost_costing_group
        where delete_time = 0
        <if test="year != null">
            and date_year = #{year}
            and date_month = #{month}
        </if>
        and hosp_id = #{hospId} group by date_year,date_month
        limit #{startIndex},#{pageSize}
    </select>
    <select id="queryAllocationCount" resultType="java.lang.Integer">
        select count(*) from (select count(*)
        from cost_costing_group
        where delete_time = 0
        <if test="year != null">
            and date_year = #{year}
            and date_month = #{month}
        </if>
        and hosp_id = #{hospId} group by date_year,date_month) t
    </select>

    <select id="getRealData" resultType="com.kcim.dao.model.CostCostingGroup">
        select ccg.*
        from cost_costing_group ccg
        left join cost_responsibility cr on ccg.responsibility_code = cr.responsibility_code
        where ccg.date_year = #{year}
        and ccg.date_month = #{month}
        and ccg.delete_time = 0
        and ccg.hosp_id = #{hospId}
        and cr.hosp_id = #{hospId}
        and cr.delete_time = 0
        and share_id in
        <foreach collection="maxId" item="item" index="index" separator="," open="(" close=")">
            #{item}
        </foreach>
    </select>
    <select id="getAllRealData" resultType="com.kcim.dao.model.CostCostingGroup">
        select ccg.*,cr.share_id
        from cost_costing_group ccg
        left join cost_responsibility cr on ccg.responsibility_code = cr.responsibility_code
        where ccg.date_year = #{year}
        and ccg.date_month = #{month}
        and ccg.delete_time = 0
        and ccg.hosp_id = #{hospId}
        and cr.hosp_id = #{hospId}
        and cr.delete_time = 0
    </select>
    <select id="getDepartment" resultType="com.kcim.vo.CommonDepartVo">
        SELECT DISTINCT
        department_code as departmentCode,
        department_name as departmentName
        FROM
        cost_costing_group
        WHERE
        hosp_id = #{hospId}
        and date_year = #{year}
        and date_month = #{month}
        AND delete_time = 0
        <if test="departmentName != null and departmentName != ''">
            and department_name like concat('%',#{departmentName,jdbcType=VARCHAR},'%')
        </if>
    </select>
    <select id="selectHistoryList" resultType="com.kcim.dao.model.CostCostingGroup">
        select  *
        from cost_costing_group
        where  hosp_id = #{hospId}
          and delete_time != 0
          and file_id = #{id}
    </select>
    <select id="queryStartAllocationDepartmentAmount" resultType="java.math.BigDecimal">
        select
        sum(ccg.amount)
        from cost_costing_group ccg
        left join cost_responsibility cr
        on ccg.responsibility_code = cr.responsibility_code and ccg.hosp_id = cr.hosp_id
        left join cost_accounting ca on ccg.account_code = ca.accounting_code and ccg.hosp_id = ca.hosp_id
        left join cost_product cp
        on cp.delete_time = 0 and ccg.product_code = cp.product_code and ccg.hosp_id = cp.hosp_id
        where ccg.delete_time = 0
        and ccg.date_year = #{year}
        and ccg.date_month = #{month}
        and ccg.hosp_id = #{hospId}
        <if test="responsibilityCode != null and responsibilityCode != ''">
            and ccg.responsibility_code = #{responsibilityCode}
        </if>
        <if test="accountCode != null and accountCode != ''">
            and ccg.account_code = #{accountCode}
        </if>
        <if test="departmentCode != null and departmentCode != ''">
            and ccg.department_code = #{departmentCode}
        </if>
        <if test="filter != null and filter != ''">
            and (ccg.product_code like concat('%',#{filter,jdbcType=VARCHAR},'%') or ccg.product_name like concat('%',#{filter,jdbcType=VARCHAR},'%') )
        </if>
        and cr.delete_time = 0
        and ca.delete_time = 0

    </select>


</mapper>