<?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.engine.salary.mapper.siaccount.ExcelInsuranceDetailMapper">
    <resultMap id="BaseResultMap" type="com.engine.salary.entity.siaccount.po.ExcelInsuranceDetailPO">
        <result column="id" property="id"/>
        <result column="employee_id" property="employeeId"/>
        <result column="bill_month" property="billMonth"/>
        <result column="bill_status" property="billStatus"/>
        <result column="payment_status" property="paymentStatus"/>
        <result column="supplementary_month" property="supplementaryMonth"/>
        <result column="supplementary_projects" property="supplementaryProjects"/>
        <result column="resource_from" property="resourceFrom"/>
        <result column="social_pay_org" property="socialPayOrg"/>
        <result column="social_account" property="socialAccount"/>
        <result column="fund_pay_org" property="fundPayOrg"/>
        <result column="fund_account" property="fundAccount"/>
        <result column="supplement_fund_account" property="supplementFundAccount"/>
        <result column="other_pay_org" property="otherPayOrg"/>
        <result column="social_scheme_id" property="socialSchemeId"/>
        <result column="social_payment_base_string" property="socialPaymentBaseString"/>
        <result column="fund_scheme_id" property="fundSchemeId"/>
        <result column="fund_payment_base_string" property="fundPaymentBaseString"/>
        <result column="other_scheme_id" property="otherSchemeId"/>
        <result column="other_payment_base_string" property="otherPaymentBaseString"/>
        <result column="social_per_json" property="socialPerJson"/>
        <result column="social_per_sum" property="socialPerSum"/>
        <result column="fund_per_json" property="fundPerJson"/>
        <result column="fund_per_sum" property="fundPerSum"/>
        <result column="other_per_json" property="otherPerJson"/>
        <result column="other_per_sum" property="otherPerSum"/>
        <result column="per_sum" property="perSum"/>
        <result column="social_com_json" property="socialComJson"/>
        <result column="social_com_sum" property="socialComSum"/>
        <result column="fund_com_json" property="fundComJson"/>
        <result column="fund_com_sum" property="fundComSum"/>
        <result column="other_com_json" property="otherComJson"/>
        <result column="other_com_sum" property="otherComSum"/>
        <result column="com_sum" property="comSum"/>
        <result column="social_sum" property="socialSum"/>
        <result column="fund_sum" property="fundSum"/>
        <result column="other_sum" property="otherSum"/>
        <result column="total" property="total"/>
        <result column="creator" property="creator"/>
        <result column="delete_type" property="deleteType"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="tenant_key" property="tenantKey"/>
        <result column="payment_organization" property="paymentOrganization"/>
    </resultMap>

    <!-- 表字段 -->
    <sql id="baseColumns">
        t
        .
        id
        , t.employee_id
        , e.status as employee_status
        , t.bill_month
        , t.bill_status
        , t.payment_status
        , t.supplementary_month
        , t.supplementary_projects
        , t.resource_from
        , t.social_pay_org
        , t.social_account
        , t.fund_pay_org
        , t.fund_account
        , t.supplement_fund_account
        , t.other_pay_org
        , t.social_scheme_id
        , t.social_payment_base_string
        , t.social_payment_com_base_string
        , t.fund_scheme_id
        , t.fund_payment_base_string
        , t.fund_payment_com_base_string
        , t.other_scheme_id
        , t.other_payment_base_string
        , t.other_payment_com_base_string
        , t.social_per_json
        , t.social_per_sum
        , t.fund_per_json
        , t.fund_per_sum
        , t.other_per_json
        , t.other_per_sum
        , t.per_sum
        , t.social_com_json
        , t.social_com_sum
        , t.fund_com_json
        , t.fund_com_sum
        , t.other_com_json
        , t.other_com_sum
        , t.com_sum
        , t.social_sum
        , t.fund_sum
        , t.other_sum
        , t.total
        , t.creator
        , t.delete_type
        , t.create_time
        , t.update_time
        , t.tenant_key
        , t.payment_organization
    </sql>

    <sql id="paramSqlCommon">
        <if test="param.billMonth != null and param.billMonth != ''">
            AND
            t.bill_month = #{param.billMonth}
        </if>
        <if test="param.paymentStatus != null">
            AND
            t.payment_status = #{param.paymentStatus}
        </if>
        <if test="param.paymentOrganization != null and param.paymentOrganization != ''">
            AND
            t.payment_organization = #{param.paymentOrganization}
        </if>
    </sql>

    <update id="updateBatchSelective" parameterType="java.util.List">
        update hrsa_excel_bill_detail
        <trim prefix="set" suffixOverrides=",">

            <trim prefix="social_payment_com_base_string = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.socialPaymentComBaseString != null">
                        when id = #{item.id} then #{item.socialPaymentComBaseString}
                    </if>
                </foreach>
            </trim>
            <trim prefix="fund_payment_com_base_string = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.fundPaymentComBaseString != null">
                        when id = #{item.id} then #{item.fundPaymentComBaseString}
                    </if>
                </foreach>
            </trim>
            <trim prefix="other_payment_com_base_string = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.otherPaymentComBaseString != null">
                        when id = #{item.id} then #{item.otherPaymentComBaseString}
                    </if>
                </foreach>
            </trim>
            <trim prefix="social_payment_base_string = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.socialPaymentBaseString != null">
                        when id = #{item.id} then #{item.socialPaymentBaseString}
                    </if>
                </foreach>
            </trim>
            <trim prefix="fund_payment_base_string = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.fundPaymentBaseString != null">
                        when id = #{item.id} then #{item.fundPaymentBaseString}
                    </if>
                </foreach>
            </trim>
            <trim prefix="other_payment_base_string = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.otherPaymentBaseString != null">
                        when id = #{item.id} then #{item.otherPaymentBaseString}
                    </if>
                </foreach>
            </trim>
            <trim prefix="social_per_json = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.socialPerJson != null">
                        when id = #{item.id} then #{item.socialPerJson}
                    </if>
                </foreach>
            </trim>
            <trim prefix="social_per_sum = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.socialPerSum != null">
                        when id = #{item.id} then #{item.socialPerSum}
                    </if>
                </foreach>
            </trim>
            <trim prefix="fund_per_json = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.fundPerJson != null">
                        when id = #{item.id} then #{item.fundPerJson}
                    </if>
                </foreach>
            </trim>
            <trim prefix="fund_per_sum = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.fundPerSum != null">
                        when id = #{item.id} then #{item.fundPerSum}
                    </if>
                </foreach>
            </trim>
            <trim prefix="other_per_json = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.otherPerJson != null">
                        when id = #{item.id} then #{item.otherPerJson}
                    </if>
                </foreach>
            </trim>
            <trim prefix="other_per_sum = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.otherPerSum != null">
                        when id = #{item.id} then #{item.otherPerSum}
                    </if>
                </foreach>
            </trim>
            <trim prefix="per_sum = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.perSum != null">
                        when id = #{item.id} then #{item.perSum}
                    </if>
                </foreach>
            </trim>
            <trim prefix="social_com_json = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.socialComJson != null">
                        when id = #{item.id} then #{item.socialComJson}
                    </if>
                </foreach>
            </trim>
            <trim prefix="social_com_sum = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.socialComSum != null">
                        when id = #{item.id} then #{item.socialComSum}
                    </if>
                </foreach>
            </trim>
            <trim prefix="fund_com_json = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.fundComJson != null">
                        when id = #{item.id} then #{item.fundComJson}
                    </if>
                </foreach>
            </trim>
            <trim prefix="fund_com_sum = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.fundComSum != null">
                        when id = #{item.id} then #{item.fundComSum}
                    </if>
                </foreach>
            </trim>
            <trim prefix="other_com_json = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.otherComJson != null">
                        when id = #{item.id} then #{item.otherComJson}
                    </if>
                </foreach>
            </trim>
            <trim prefix="other_com_sum = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.otherComSum != null">
                        when id = #{item.id} then #{item.otherComSum}
                    </if>
                </foreach>
            </trim>
            <trim prefix="com_sum = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.comSum != null">
                        when id = #{item.id} then #{item.comSum}
                    </if>
                </foreach>
            </trim>
            <trim prefix="social_sum = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.socialSum != null">
                        when id = #{item.id} then #{item.socialSum}
                    </if>
                </foreach>
            </trim>
            <trim prefix="fund_sum = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.fundSum != null">
                        when id = #{item.id} then #{item.fundSum}
                    </if>
                </foreach>
            </trim>
            <trim prefix="other_sum = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.otherSum != null">
                        when id = #{item.id} then #{item.otherSum}
                    </if>
                </foreach>
            </trim>
            <trim prefix="total = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    <if test="item.total != null">
                        when id = #{item.id} then #{item.total}
                    </if>
                </foreach>
            </trim>
        </trim>
        where id in
        <foreach close=")" collection="list" item="item" open="(" separator=", ">
            #{item.id}
        </foreach>
    </update>

    <select id="queryCompareList" resultMap="BaseResultMap">
        SELECT
        t.id,t.employee_id,t.social_per_json,t.social_com_json,
        t.fund_per_json,t.fund_com_json,t.other_per_json,
        t.other_com_json,t.social_per_sum,t.social_com_sum,
        t.fund_per_sum,t.fund_com_sum,t.other_per_sum,
        t.other_com_sum,t.per_sum,t.com_sum
        FROM
        hrsa_excel_bill_detail t
        WHERE t.delete_type = 0
        AND t.payment_status = 0
        AND t.bill_month = #{billMonth}
        AND t.payment_organization = #{paymentOrganization}
        AND employee_id = #{employeeId}
    </select>
    <select id="listAll" resultType="com.engine.salary.entity.siaccount.po.ExcelInsuranceDetailPO">
        SELECT
            t
                .
                id
             , t.employee_id
             , t.bill_month
             , t.bill_status
             , t.payment_status
             , t.supplementary_month
             , t.supplementary_projects
             , t.resource_from
             , t.social_pay_org
             , t.social_account
             , t.fund_pay_org
             , t.fund_account
             , t.supplement_fund_account
             , t.other_pay_org
             , t.social_scheme_id
             , t.social_payment_base_string
             , t.social_payment_com_base_string
             , t.fund_scheme_id
             , t.fund_payment_base_string
             , t.fund_payment_com_base_string
             , t.other_scheme_id
             , t.other_payment_base_string
             , t.other_payment_com_base_string
             , t.social_per_json
             , t.social_per_sum
             , t.fund_per_json
             , t.fund_per_sum
             , t.other_per_json
             , t.other_per_sum
             , t.per_sum
             , t.social_com_json
             , t.social_com_sum
             , t.fund_com_json
             , t.fund_com_sum
             , t.other_com_json
             , t.other_com_sum
             , t.com_sum
             , t.social_sum
             , t.fund_sum
             , t.other_sum
             , t.total
             , t.creator
             , t.delete_type
             , t.create_time
             , t.update_time
             , t.tenant_key
             , t.payment_organization
        FROM
        hrsa_excel_bill_detail t
        WHERE t.delete_type = 0
    </select>

    <!-- 根据主键删除记录 -->
    <delete id="deleteById" parameterType="com.engine.salary.entity.siaccount.po.ExcelInsuranceDetailPO">
        UPDATE hrsa_excel_bill_detail
        SET delete_type = 1
        WHERE id = #{id}
          AND delete_type = 0
    </delete>

    <!-- 根据id批量删除 -->
    <delete id="batchDelByIds">
        UPDATE hrsa_excel_bill_detail
        SET delete_type = 1
        WHERE delete_type = 0
        <if test="ids != null and ids.size()>0">
            AND id IN
            <foreach collection="ids" open="(" item="id" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </delete>


    <insert id="batchSave">
        INSERT INTO hrsa_excel_bill_detail
        (id,employee_id,bill_month,bill_status,payment_status,supplementary_month,supplementary_projects,resource_from,social_pay_org,social_account,social_scheme_id,social_payment_base_string,
        fund_pay_org,fund_account,supplement_fund_account,fund_scheme_id,fund_payment_base_string,other_pay_org,other_scheme_id,other_payment_base_string,social_per_json,
        social_per_sum,fund_per_json,fund_per_sum,other_per_json,other_per_sum,per_sum,social_com_json,social_com_sum,fund_com_json,fund_com_sum,other_com_json,other_com_sum,
        com_sum,social_sum,fund_sum,other_sum,total,creator,create_time,update_time,delete_type,tenant_key,payment_organization,
        social_payment_com_base_string,fund_payment_com_base_string,other_payment_com_base_string)
        VALUES
        <foreach collection="accounts" item="item" separator=",">
            (
            #{item.id},
            #{item.employeeId},
            #{item.billMonth},
            #{item.billStatus},
            #{item.paymentStatus},
            #{item.supplementaryMonth},
            #{item.supplementaryProjects},
            #{item.resourceFrom},
            #{item.socialPayOrg},
            #{item.socialAccount},
            #{item.socialSchemeId},
            #{item.socialPaymentBaseString},
            #{item.fundPayOrg},
            #{item.fundAccount},
            #{item.supplementFundAccount},
            #{item.fundSchemeId},
            #{item.fundPaymentBaseString},
            #{item.otherPayOrg},
            #{item.otherSchemeId},
            #{item.otherPaymentBaseString},
            #{item.socialPerJson},
            #{item.socialPerSum},
            #{item.fundPerJson},
            #{item.fundPerSum},
            #{item.otherPerJson},
            #{item.otherPerSum},
            #{item.perSum},
            #{item.socialComJson},
            #{item.socialComSum},
            #{item.fundComJson},
            #{item.fundComSum},
            #{item.otherComJson},
            #{item.otherComSum},
            #{item.comSum},
            #{item.socialSum},
            #{item.fundSum},
            #{item.otherSum},
            #{item.total},
            #{item.creator},
            #{item.createTime},
            #{item.updateTime},
            #{item.deleteType},
            #{item.tenantKey},
            #{item.paymentOrganization},
            #{item.socialPaymentComBaseString},
            #{item.fundPaymentComBaseString},
            #{item.otherPaymentComBaseString}
            )
        </foreach>
    </insert>
    <insert id="batchSave" databaseId="oracle">
        INSERT INTO hrsa_excel_bill_detail
        (id,employee_id,bill_month,bill_status,payment_status,supplementary_month,supplementary_projects,resource_from,social_pay_org,social_account,social_scheme_id,social_payment_base_string,
        fund_pay_org,fund_account,supplement_fund_account,fund_scheme_id,fund_payment_base_string,other_pay_org,other_scheme_id,other_payment_base_string,social_per_json,
        social_per_sum,fund_per_json,fund_per_sum,other_per_json,other_per_sum,per_sum,social_com_json,social_com_sum,fund_com_json,fund_com_sum,other_com_json,other_com_sum,
        com_sum,social_sum,fund_sum,other_sum,total,creator,create_time,update_time,delete_type,tenant_key,payment_organization,
        social_payment_com_base_string,fund_payment_com_base_string,other_payment_com_base_string)
        <foreach collection="accounts" item="item" separator="union all">
            select
            #{item.id,jdbcType=DOUBLE},
            #{item.employeeId,jdbcType=DOUBLE},
            #{item.billMonth,jdbcType=VARCHAR},
            #{item.billStatus,jdbcType=INTEGER},
            #{item.paymentStatus,jdbcType=INTEGER},
            #{item.supplementaryMonth,jdbcType=VARCHAR},
            #{item.supplementaryProjects,jdbcType=VARCHAR},
            #{item.resourceFrom,jdbcType=INTEGER},
            #{item.socialPayOrg,jdbcType=DOUBLE},
            #{item.socialAccount,jdbcType=VARCHAR},
            #{item.socialSchemeId,jdbcType=DOUBLE},
            #{item.socialPaymentBaseString,jdbcType=VARCHAR},
            #{item.fundPayOrg,jdbcType=DOUBLE},
            #{item.fundAccount,jdbcType=VARCHAR},
            #{item.supplementFundAccount,jdbcType=VARCHAR},
            #{item.fundSchemeId,jdbcType=DOUBLE},
            #{item.fundPaymentBaseString,jdbcType=VARCHAR},
            #{item.otherPayOrg,jdbcType=DOUBLE},
            #{item.otherSchemeId,jdbcType=DOUBLE},
            #{item.otherPaymentBaseString,jdbcType=VARCHAR},
            #{item.socialPerJson,jdbcType=VARCHAR},
            #{item.socialPerSum,jdbcType=VARCHAR},
            #{item.fundPerJson,jdbcType=VARCHAR},
            #{item.fundPerSum,jdbcType=VARCHAR},
            #{item.otherPerJson,jdbcType=VARCHAR},
            #{item.otherPerSum,jdbcType=VARCHAR},
            #{item.perSum,jdbcType=VARCHAR},
            #{item.socialComJson,jdbcType=VARCHAR},
            #{item.socialComSum,jdbcType=VARCHAR},
            #{item.fundComJson,jdbcType=VARCHAR},
            #{item.fundComSum,jdbcType=VARCHAR},
            #{item.otherComJson,jdbcType=VARCHAR},
            #{item.otherComSum,jdbcType=VARCHAR},
            #{item.comSum,jdbcType=VARCHAR},
            #{item.socialSum,jdbcType=VARCHAR},
            #{item.fundSum,jdbcType=VARCHAR},
            #{item.otherSum,jdbcType=VARCHAR},
            #{item.total,jdbcType=VARCHAR},
            #{item.creator,jdbcType=DOUBLE},
            #{item.createTime},
            #{item.updateTime},
            #{item.deleteType},
            #{item.tenantKey,jdbcType=VARCHAR},
            #{item.paymentOrganization,jdbcType=DOUBLE},
            #{item.socialPaymentComBaseString,jdbcType=VARCHAR},
            #{item.fundPaymentComBaseString,jdbcType=VARCHAR},
            #{item.otherPaymentComBaseString,jdbcType=VARCHAR}
            from dual
        </foreach>
    </insert>
    <insert id="batchSave" databaseId="sqlserver">
        <foreach collection="accounts" item="item" separator=";">
            INSERT INTO hrsa_excel_bill_detail
            (id,employee_id,bill_month,bill_status,payment_status,supplementary_month,supplementary_projects,resource_from,social_pay_org,social_account,social_scheme_id,social_payment_base_string,
            fund_pay_org,fund_account,supplement_fund_account,fund_scheme_id,fund_payment_base_string,other_pay_org,other_scheme_id,other_payment_base_string,social_per_json,
            social_per_sum,fund_per_json,fund_per_sum,other_per_json,other_per_sum,per_sum,social_com_json,social_com_sum,fund_com_json,fund_com_sum,other_com_json,other_com_sum,
            com_sum,social_sum,fund_sum,other_sum,total,creator,create_time,update_time,delete_type,tenant_key,payment_organization,
            social_payment_com_base_string,fund_payment_com_base_string,other_payment_com_base_string)
            VALUES
            (
            #{item.id},
            #{item.employeeId},
            #{item.billMonth},
            #{item.billStatus},
            #{item.paymentStatus},
            #{item.supplementaryMonth},
            #{item.supplementaryProjects},
            #{item.resourceFrom},
            #{item.socialPayOrg},
            #{item.socialAccount},
            #{item.socialSchemeId},
            #{item.socialPaymentBaseString},
            #{item.fundPayOrg},
            #{item.fundAccount},
            #{item.supplementFundAccount},
            #{item.fundSchemeId},
            #{item.fundPaymentBaseString},
            #{item.otherPayOrg},
            #{item.otherSchemeId},
            #{item.otherPaymentBaseString},
            #{item.socialPerJson},
            #{item.socialPerSum},
            #{item.fundPerJson},
            #{item.fundPerSum},
            #{item.otherPerJson},
            #{item.otherPerSum},
            #{item.perSum},
            #{item.socialComJson},
            #{item.socialComSum},
            #{item.fundComJson},
            #{item.fundComSum},
            #{item.otherComJson},
            #{item.otherComSum},
            #{item.comSum},
            #{item.socialSum},
            #{item.fundSum},
            #{item.otherSum},
            #{item.total},
            #{item.creator},
            #{item.createTime},
            #{item.updateTime},
            #{item.deleteType},
            #{item.tenantKey},
            #{item.paymentOrganization},
            #{item.socialPaymentComBaseString},
            #{item.fundPaymentComBaseString},
            #{item.otherPaymentComBaseString}
            )
        </foreach>
    </insert>


    <sql id="likeCondition" >
        <if test="userName != null and userName != ''">
            AND e.lastname like CONCAT('%',#{userName},'%')
        </if>
    </sql>

    <sql id="likeCondition" databaseId="oracle">
        <if test="userName != null and userName != ''">
            AND e.lastname like '%'||#{userName}||'%'
        </if>
    </sql>

    <sql id="likeCondition" databaseId="sqlserver">
        <if test="userName != null and userName != ''">
            AND e.lastname like '%'+#{userName}+'%'
        </if>
    </sql>



</mapper>