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

    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.kcim.dao.model.AllocationQuery" id="allocationQueryMap">
        <result property="id" column="id"/>
        <result property="dateYear" column="date_year"/>
        <result property="dateMonth" column="date_month"/>
        <result property="hospId" column="hosp_id"/>
        <result property="responsibilityCode" column="responsibility_code"/>
        <result property="responsibilityName" column="responsibility_name"/>
        <result property="originId" column="origin_id"/>
        <result property="originType" column="origin_type"/>
        <result property="amount" column="amount"/>
        <result property="accountingCode" column="accounting_code"/>
        <result property="accountingName" column="accounting_name"/>
        <result property="targetResponsibilityCode" column="target_responsibility_code"/>
        <result property="targetResponsibilityName" column="target_responsibility_code"/>
        <result property="shareLevelId" column="share_level_id"/>
        <result property="createTime" column="create_time"/>
        <result property="deleteTime" column="delete_time"/>
    </resultMap>


    <select id="getTotalMoney" resultType="java.math.BigDecimal">
        select sum(amount)
        from cost_allocation_query
        where date_year = #{dateYear}
          and date_month = #{month}
          and hosp_id = #{hospId}
          and delete_time = 0
    </select>
    <select id="getRespCodeAndName" resultType="com.kcim.vo.CodeAndNameVO">
        select responsibility_code as code, responsibility_name as name
        from cost_allocation_query
        where date_year = #{dateYear}
          and date_month = #{month}
          and hosp_id = #{hospId}
          and delete_time = 0
    </select>
    <select id="getAccountCodeAndName" resultType="com.kcim.vo.CodeAndNameVO">
        select accounting_code as code, accounting_name as name
        from cost_allocation_query
        where date_year = #{dateYear}
          and date_month = #{month}
          and hosp_id = #{hospId}
          and delete_time = 0
    </select>
    <select id="getCountByRespAndAccounts" resultType="java.math.BigDecimal">
        select IFNULL(sum(amount), 0)
        from cost_allocation_query
        where date_year = #{dateYear}
        and date_month = #{month}
        and hosp_id = #{hospId}
        and responsibility_code = #{code}
        and accounting_code in
        <foreach collection="accountCodes" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        and delete_time = 0

    </select>
    <select id="getTotalByAccountAndResps" resultType="java.math.BigDecimal">
        select IFNULL(sum(amount), 0)
        from cost_allocation_query
        where date_year = #{dateYear}
        and date_month = #{month}
        and hosp_id = #{hospId}
        and accounting_code = #{code}
        and responsibility_code in
        <foreach collection="respCodes" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        and delete_time = 0
    </select>
    <select id="getTotalByAccountAndRespCode" resultType="java.math.BigDecimal">
        select IFNULL(sum(amount), 0)
        from cost_allocation_query
        where date_year = #{dateYear}
          and date_month = #{month}
          and hosp_id = #{hospId}
          and accounting_code = #{accountCode}
          and responsibility_code = #{responsibilityCode}
          and delete_time = 0
    </select>

    <select id="getAcountAccounts" resultType="com.kcim.dao.model.AllocationQuery">
        SELECT
            accounting_code,
            IFNULL( sum( amount ), 0 ) AS amount
        FROM
            cost_allocation_query
        WHERE
            date_year = #{dateYear}
          AND date_month = #{month}
          AND hosp_id = #{hospId}
          AND delete_time = 0
        GROUP BY
            accounting_code
    </select>
    <select id="getRespAcountAccounts" resultType="com.kcim.dao.model.AllocationQuery">
        SELECT
            responsibility_code,
            accounting_code,
            IFNULL( sum( amount ), 0 ) AS amount
        FROM
            cost_allocation_query
        WHERE
            date_year = #{dateYear}
          AND date_month = #{month}
          AND hosp_id = #{hospId}
          AND delete_time = 0
        GROUP BY
            responsibility_code,
            accounting_code
    </select>
    <select id="getRespOriginAcountAccounts" resultType="com.kcim.dao.model.AllocationQuery">
        SELECT
            responsibility_code,
            origin_type,
            accounting_code,
            IFNULL( sum( amount ), 0 ) AS amount
        FROM
            cost_allocation_query
        WHERE
            date_year = #{dateYear}
          AND date_month = #{month}
          AND hosp_id = #{hospId}
          AND delete_time = 0
        GROUP BY
            responsibility_code,
            origin_type,
            accounting_code
    </select>
</mapper>