<?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.salaryacct.ExcelAcctResultMapper">

    <select id="listEmployeeIdUnionSalaryAcctResult" resultType="long">
        SELECT DISTINCT employee_id
        FROM hrsa_salary_acct_result
        WHERE  salary_acct_record_id = #{salaryAcctRecordId}
        <if test="employeeIds != null and employeeIds.size() > 0">
            AND employee_id IN
            <foreach collection="employeeIds" open="(" item="employeeId" separator="," close=")">
                #{employeeId}
            </foreach>
        </if>
        UNION
        SELECT DISTINCT employee_id
        FROM hrsa_excel_acct_result
        WHERE  salary_acct_record_id = #{salaryAcctRecordId}
        <if test="employeeIds != null and employeeIds.size() > 0">
            AND employee_id IN
            <foreach collection="employeeIds" open="(" item="employeeId" separator="," close=")">
                #{employeeId}
            </foreach>
        </if>
    </select>

    <insert id="batchInsert">
        INSERT INTO hrsa_excel_acct_result(salary_sob_id, salary_acct_emp_id, salary_acct_record_id, employee_id,
        salary_item_id, result_value, creator, create_time, update_time,
        delete_type, tenant_key,tax_agent_id)
        VALUES
        <foreach collection="collection" item="item" separator=",">
            (
            #{item.salarySobId},
            #{item.salaryAcctEmpId},
            #{item.salaryAcctRecordId},
            #{item.employeeId},
            #{item.salaryItemId},
            #{item.resultValue},
            #{item.creator},
            #{item.createTime},
            #{item.updateTime},
            #{item.deleteType},
            #{item.tenantKey},
            #{item.taxAgentId}
            )
        </foreach>
    </insert>
     <insert id="batchInsert" databaseId="oracle"> 
         INSERT INTO hrsa_excel_acct_result(salary_sob_id, salary_acct_emp_id, salary_acct_record_id, employee_id,
         salary_item_id, result_value, creator, create_time, update_time, 
         delete_type, tenant_key,tax_agent_id)
         
         <foreach collection="collection" item="item" separator="union all">
             select
             #{item.salarySobId,jdbcType=DOUBLE},
             #{item.salaryAcctEmpId,jdbcType=DOUBLE},
             #{item.salaryAcctRecordId,jdbcType=DOUBLE},
             #{item.employeeId,jdbcType=DOUBLE},
             #{item.salaryItemId,jdbcType=DOUBLE},
             #{item.resultValue,jdbcType=VARCHAR},
             #{item.creator,jdbcType=DOUBLE},
             #{item.createTime,jdbcType=DATE},
             #{item.updateTime,jdbcType=DATE},
             #{item.deleteType,jdbcType=INTEGER},
             #{item.tenantKey,jdbcType=VARCHAR},
             #{item.taxAgentId,jdbcType=DOUBLE}
              from dual  
         </foreach> 
     </insert> 
     <insert id="batchInsert" databaseId="sqlserver"> 
         <foreach collection="collection" item="item" separator=";">
         INSERT INTO hrsa_excel_acct_result( salary_sob_id, salary_acct_emp_id, salary_acct_record_id, employee_id,
         salary_item_id, result_value, creator, create_time, update_time,
         delete_type, tenant_key, tax_agent_id)
         VALUES
             (
             #{item.salarySobId}, 
             #{item.salaryAcctEmpId}, 
             #{item.salaryAcctRecordId}, 
             #{item.employeeId}, 
             #{item.salaryItemId}, 
             #{item.resultValue}, 
             #{item.creator}, 
             #{item.createTime}, 
             #{item.updateTime}, 
             #{item.deleteType}, 
             #{item.tenantKey},
             #{item.taxAgentId}
             ) 
         </foreach> 
     </insert> 

    <delete id="deleteBySalaryAcctEmployeeIds">
        DELETE FROM hrsa_excel_acct_result
        WHERE salary_acct_emp_id IN
        <foreach collection="salaryAcctEmployeeIds" open="(" item="salaryAcctEmployeeId" separator="," close=")">
            #{salaryAcctEmployeeId}
        </foreach>
    </delete>

    <delete id="deleteBySalaryAcctRecordIds">
        DELETE FROM hrsa_excel_acct_result
        WHERE salary_acct_record_id IN
        <foreach collection="salaryAcctRecordIds" open="(" item="salaryAcctRecordId" separator="," close=")">
            #{salaryAcctRecordId}
        </foreach>
    </delete>


    <resultMap id="BaseResultMap" type="com.engine.salary.entity.salaryacct.po.ExcelAcctResultPO">
        <result column="id" property="id" />
        <result column="salary_sob_id" property="salarySobId" />
        <result column="salary_acct_emp_id" property="salaryAcctEmpId" />
        <result column="salary_acct_record_id" property="salaryAcctRecordId" />
        <result column="employee_id" property="employeeId" />
        <result column="salary_item_id" property="salaryItemId" />
        <result column="result_value" property="resultValue" />
        <result column="creator" property="creator" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
        <result column="delete_type" property="deleteType" />
        <result column="tenant_key" property="tenantKey" />
        <result column="tax_agent_id" property="taxAgentId" />
    </resultMap>

    <!-- 表字段 -->
    <sql id="baseColumns">
        t.id
        , t.salary_sob_id
        , t.salary_acct_emp_id
        , t.salary_acct_record_id
        , t.employee_id
        , t.salary_item_id
        , t.result_value
        , t.creator
        , t.create_time
        , t.update_time
        , t.delete_type
        , t.tenant_key
        , t.tax_agent_id
    </sql>

    <!-- 条件查询 -->
    <select id="listSome" resultMap="BaseResultMap" parameterType="com.engine.salary.entity.salaryacct.po.ExcelAcctResultPO">
        SELECT
        <include refid="baseColumns" />
        FROM hrsa_excel_acct_result t
        WHERE delete_type = 0
        <if test="id != null">
            AND id = #{id}
        </if>
        <if test="salarySobId != null">
            AND salary_sob_id = #{salarySobId}
        </if>
        <if test="salaryAcctEmpId != null">
            AND salary_acct_emp_id = #{salaryAcctEmpId}
        </if>
        <if test="salaryAcctRecordId != null">
            AND salary_acct_record_id = #{salaryAcctRecordId}
        </if>
        <if test="employeeId != null">
            AND employee_id = #{employeeId}
        </if>
        <if test="salaryItemId != null">
            AND salary_item_id = #{salaryItemId}
        </if>
        <if test="resultValue != null">
            AND result_value = #{resultValue}
        </if>
        <if test="creator != null">
            AND creator = #{creator}
        </if>
        <if test="createTime != null">
            AND create_time = #{createTime}
        </if>
        <if test="updateTime != null">
            AND update_time = #{updateTime}
        </if>
        <if test="deleteType != null">
            AND delete_type = #{deleteType}
        </if>
        <if test="tenantKey != null">
            AND tenant_key = #{tenantKey}
        </if>
        <if test="taxAgentId != null">
            AND tax_agent_id = #{taxAgentId}
        </if>
        <if test="salaryAcctEmpIds != null and salaryAcctEmpIds.size()>0">
            AND salary_acct_emp_id IN
            <foreach collection="salaryAcctEmpIds" open="(" item="salaryAcctEmpId" separator="," close=")">
                #{salaryAcctEmpId}
            </foreach>
        </if>
        ORDER BY id DESC
    </select>

    <update id="batchUpdate" parameterType="java.util.List">
        update hrsa_excel_acct_result
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="result_value =case" suffix="end,">
                <foreach collection="collection" item="item" index="index">
                    <if test="item.resultValue!=null">
                        when id=#{item.id} then #{item.resultValue}
                    </if>
                </foreach>
            </trim>
            <trim prefix="delete_type =case" suffix="end,">
                <foreach collection="collection" item="item" index="index">
                    <if test="item.deleteType!=null">
                        when id=#{item.id} then #{item.deleteType}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        id in
        <foreach collection="collection" item="item" index="index" separator="," open="(" close=")">
            #{item.id}
        </foreach>
    </update>
</mapper>
