<?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.yifu.cloud.plus.v1.yifu.salary.mapper.TSalaryAccountItemMapper">

    <resultMap id="tSalaryAccountItemMap" type="com.yifu.cloud.plus.v1.yifu.salary.entity.TSalaryAccountItem">
        <id property="id" column="ID"/>
        <result property="salaryAccountId" column="SALARY_ACCOUNT_ID"/>
        <result property="cnName" column="CN_NAME"/>
        <result property="javaFiedName" column="JAVA_FIED_NAME"/>
        <result property="salaryMoney" column="SALARY_MONEY"/>
        <result property="textValue" column="TEXT_VALUE"/>
        <result property="isTax" column="IS_TAX"/>
    </resultMap>

    <!-- 工资导入优化使用的vo -->
    <resultMap id="itemVoMap" type="com.yifu.cloud.plus.v1.yifu.salary.entity.TSalaryAccountItem">
        <id property="id" column="ID"/>
        <result property="salaryAccountId" column="SALARY_ACCOUNT_ID"/>
        <result property="cnName" column="CN_NAME"/>
        <result property="javaFiedName" column="JAVA_FIED_NAME"/>
        <result property="salaryMoney" column="SALARY_MONEY"/>
        <result property="textValue" column="TEXT_VALUE"/>
        <result property="isTax" column="IS_TAX"/>
        <result property="empIdcard" column="EMP_IDCARD"/>
        <result property="settlementMonth" column="SETTLEMENT_MONTH"/>
        <result property="formType" column="FORM_TYPE"/>
        <result property="annualBonusType" column="ANNUAL_BONUS_TYPE"/>
    </resultMap>

    <!--tSalaryAccountItem简单分页查询-->
    <select id="getTSalaryAccountItemPage" resultMap="tSalaryAccountItemMap">
		SELECT
		ID,
		SALARY_ACCOUNT_ID,
		CN_NAME,
		JAVA_FIED_NAME,
		SUM(SALARY_MONEY)SALARY_MONEY,
		TEXT_VALUE,
		IS_TAX
		FROM
			<if test="tSalaryAccountItem.salaryMonth == null or tSalaryAccountItem.salaryMonth.trim() >= '202201'">
				t_salary_account_item a

			</if>
			<if test="tSalaryAccountItem.salaryMonth != null and tSalaryAccountItem.salaryMonth.trim() &lt; '202201' ">
				t_salary_account_item_hro_2021 a
			</if>
        <where>
            <if test="tSalaryAccountItem.id != null and tSalaryAccountItem.id.trim() != ''">
                AND ID = #{tSalaryAccountItem.id}
            </if>
            <if test="tSalaryAccountItem.salaryAccountId != null and tSalaryAccountItem.salaryAccountId.trim() != ''">
                AND SALARY_ACCOUNT_ID = #{tSalaryAccountItem.salaryAccountId}
            </if>
            <if test="tSalaryAccountItem.cnName != null and tSalaryAccountItem.cnName.trim() != ''">
                AND CN_NAME = #{tSalaryAccountItem.cnName}
            </if>
            <if test="tSalaryAccountItem.javaFiedName != null and tSalaryAccountItem.javaFiedName.trim() != ''">
                AND JAVA_FIED_NAME = #{tSalaryAccountItem.javaFiedName}
            </if>
            <if test="tSalaryAccountItem.salaryMoney != null and tSalaryAccountItem.salaryMoney.trim() != ''">
                AND SALARY_MONEY = #{tSalaryAccountItem.salaryMoney}
            </if>
        </where>
		GROUP BY CN_NAME
    </select>

    <select id="getAllTSalaryAccountItem" resultMap="tSalaryAccountItemMap">
        SELECT
        i.ID,
        i.SALARY_ACCOUNT_ID,
        i.CN_NAME,
        i.JAVA_FIED_NAME,
        i.SALARY_MONEY,
        i.TEXT_VALUE,
        i.IS_TAX
        FROM
        t_salary_account_item i left join t_salary_account a on i.SALARY_ACCOUNT_ID = a.ID
        <where>
            a.DELETE_FLAG = 0
            <if test="deptId != null and deptId.trim() != ''">
                AND a.DEPT_ID = #{deptId}
            </if>
            <if test="settleMonth != null and settleMonth.trim() != ''">
                AND a.SETTLEMENT_MONTH = #{settleMonth}
            </if>
            <if test="javaFiedName != null and javaFiedName.trim() != ''">
                AND i.JAVA_FIED_NAME = #{javaFiedName}
            </if>
            <if test="finallyYear != null and finallyYear.trim() != ''">
                AND a.SETTLEMENT_MONTH like concat(#{finallyYear}, '%')
            </if>
            <if test="empIdcard != null and empIdcard.trim() != ''">
                AND a.EMP_IDCARD = #{empIdcard}
            </if>
            <if test="accountId != null and accountId.trim() != ''">
                AND a.id != #{accountId}
            </if>
        </where>
    </select>

    <select id="getEmpAllSalaryAccountItem" resultMap="tSalaryAccountItemMap">
        SELECT
        i.ID,
        i.SALARY_ACCOUNT_ID,
        i.CN_NAME,
        i.JAVA_FIED_NAME,
        i.SALARY_MONEY,
        i.TEXT_VALUE,
        i.IS_TAX
        FROM
        t_salary_account_item i left join t_salary_account a on i.SALARY_ACCOUNT_ID = a.ID
        <where>
            a.DELETE_FLAG = 0 and a.FORM_TYPE != 7
            <if test="idCard != null and idCard.trim() != ''">
                AND a.EMP_IDCARD = #{idCard}
            </if>
            <if test="maxYearMonth != null and maxYearMonth.trim() != ''">
                AND a.SETTLEMENT_MONTH >= #{maxYearMonth}
            </if>
            <if test="invoiceTitle != null and invoiceTitle.trim() != ''">
                AND a.INVOICE_TITLE = #{invoiceTitle}
            </if>
        </where>
    </select>

    <select id="getAllMoney" resultType="java.math.BigDecimal">
        SELECT
        sum(i.SALARY_MONEY)
        FROM
        t_salary_account_item i left join t_salary_account a on i.SALARY_ACCOUNT_ID = a.ID
        <where>
            a.DELETE_FLAG = 0
            <if test="deptId != null and deptId.trim() != ''">
                AND a.DEPT_ID = #{deptId}
            </if>
            <if test="javaFiedName != null and javaFiedName.trim() != ''">
                AND i.JAVA_FIED_NAME = #{javaFiedName}
            </if>
            <if test="salaryMonth != null and salaryMonth.trim() != ''">
                AND a.SALARY_MONTH > #{salaryMonth}
            </if>
            <if test="empIdcard != null and empIdcard.trim() != ''">
                AND a.EMP_IDCARD = #{empIdcard}
            </if>
        </where>
    </select>
    <select id="getAllMoneyByAccountIdAndFiedName" resultType="java.math.BigDecimal">
        SELECT
        sum(i.SALARY_MONEY)
        FROM
        t_salary_account_item i
         where
            i.SALARY_ACCOUNT_ID in (${accountId})
            AND i.JAVA_FIED_NAME = '${javaFiedName}'
    </select>

    <select id="getAverageSalaryItemMonthCount" resultType="Integer">
        SELECT
        count(DISTINCT a.SALARY_MONTH)
        FROM
        t_salary_account_item i left join t_salary_account a on i.SALARY_ACCOUNT_ID = a.ID
        <where>
            a.DELETE_FLAG = 0
            <if test="deptId != null and deptId.trim() != ''">
                AND a.DEPT_ID = #{deptId}
            </if>
            <if test="javaFiedName != null and javaFiedName.trim() != ''">
                AND i.JAVA_FIED_NAME = #{javaFiedName}
            </if>
            <if test="salaryMonth != null and salaryMonth.trim() != ''">
                AND a.SALARY_MONTH > #{salaryMonth}
            </if>
            <if test="empIdcard != null and empIdcard.trim() != ''">
                AND a.EMP_IDCARD = #{empIdcard}
            </if>
        </where>

    </select>

    <select id="getEngineerAllMoney" resultType="java.math.BigDecimal">
        SELECT
        sum(i.SALARY_MONEY)
        FROM
        t_engineer_account_item i left join t_engineer_account a on i.SALARY_ACCOUNT_ID = a.ID
        <where>
            a.DELETE_FLAG = 0
            <if test="deptId != null and deptId.trim() != ''">
                AND a.DEPT_ID = #{deptId}
            </if>
            <if test="javaFiedName != null and javaFiedName.trim() != ''">
                AND i.JAVA_FIED_NAME = #{javaFiedName}
            </if>
            <if test="salaryMonth != null and salaryMonth.trim() != ''">
                AND a.SALARY_MONTH > #{salaryMonth}
            </if>
            <if test="empIdcard != null and empIdcard.trim() != ''">
                AND a.EMP_IDCARD = #{empIdcard}
            </if>
        </where>
    </select>

    <select id="getEngineerAverageSalaryItemMonthCount" resultType="Integer">
        SELECT
        count(DISTINCT a.SALARY_MONTH)
        FROM
        t_engineer_account_item i left join t_engineer_account a on i.SALARY_ACCOUNT_ID = a.ID
        <where>
            a.DELETE_FLAG = 0
            <if test="deptId != null and deptId.trim() != ''">
                AND a.DEPT_ID = #{deptId}
            </if>
            <if test="javaFiedName != null and javaFiedName.trim() != ''">
                AND i.JAVA_FIED_NAME = #{javaFiedName}
            </if>
            <if test="salaryMonth != null and salaryMonth.trim() != ''">
                AND a.SALARY_MONTH > #{salaryMonth}
            </if>
            <if test="empIdcard != null and empIdcard.trim() != ''">
                AND a.EMP_IDCARD = #{empIdcard}
            </if>
        </where>
    </select>

    <!--获取报账表金额-->
    <select id="getMoneyByAccountIdAndJavaFiedName" resultType="java.math.BigDecimal">
        SELECT
        i.SALARY_MONEY
        FROM
        t_salary_account_item i
         where
         i.SALARY_ACCOUNT_ID = #{accountId}
        AND i.JAVA_FIED_NAME = '${javaFiedName}'
    </select>

    <!--报账明细-->
    <select id="getAccountItemList" resultMap="tSalaryAccountItemMap">
        SELECT
        i.ID,
        i.SALARY_ACCOUNT_ID,
        i.CN_NAME,
        i.JAVA_FIED_NAME,
        i.SALARY_MONEY,
        i.TEXT_VALUE,
        i.IS_TAX
        FROM t_salary_account_item i
        left join t_salary_account a on a.id=i.SALARY_ACCOUNT_ID
        left join t_salary_standard s on s.id = a.SALARY_FORM_ID
        <if test="unitName != null">
            left join view_dept_unit d on d.id = s.DEPART_ID
        </if>
        <if test="userDeptId != null">
            left join view_sys_user u on u.user_id = s.CREATE_USER
        </if>
        <where>
            a.DELETE_FLAG = 0
            <if test="idStr != null and idStr.trim() != ''">
                AND a.id in (${idStr})
            </if>
            <if test="idStr == null or idStr.trim() == ''">
                <if test="tSalaryAccount.invoiceTitle != null and tSalaryAccount.invoiceTitle.trim() != ''">
                    AND a.INVOICE_TITLE = #{tSalaryAccount.invoiceTitle}
                </if>
                <if test="tSalaryAccount.province != null">
                    AND a.PROVINCE = #{tEngineerAccount.province}
                </if>
                <if test="tSalaryAccount.city != null">
                    AND a.CITY = #{tEngineerAccount.city}
                </if>
                <if test="tSalaryAccount.town != null">
                    AND a.TOWN = #{tEngineerAccount.town}
                </if>
                <if test="tSalaryAccount.taxMonth != null and tSalaryAccount.taxMonth.trim() != ''">
                    AND a.TAX_MONTH = #{tSalaryAccount.taxMonth}
                </if>
                <if test="tSalaryAccount.id != null and tSalaryAccount.id.trim() != ''">
                    AND a.ID = #{tSalaryAccount.id}
                </if>
                <if test="tSalaryAccount.salaryFormId != null and tSalaryAccount.salaryFormId.trim() != ''">
                    AND a.SALARY_FORM_ID = #{tSalaryAccount.salaryFormId}
                </if>
                <if test="tSalaryAccount.salaryMonth != null and tSalaryAccount.salaryMonth.trim() != ''">
                    AND a.SALARY_MONTH = #{tSalaryAccount.salaryMonth}
                </if>
                <if test="tSalaryAccount.empId != null and tSalaryAccount.empId.trim() != ''">
                    AND a.EMP_ID = #{tSalaryAccount.empId}
                </if>
                <if test="tSalaryAccount.empName != null and tSalaryAccount.empName.trim() != ''">
                    AND a.EMP_NAME like '%${tSalaryAccount.empName}%'
                </if>
                <if test="tSalaryAccount.empNo != null and tSalaryAccount.empNo.trim() != ''">
                    AND a.EMP_NO = #{tSalaryAccount.empNo}
                </if>
                <if test="tSalaryAccount.deptId != null and tSalaryAccount.deptId.trim() != ''">
                    AND a.BELONG_DEPART = #{tSalaryAccount.deptId}
                </if>
                <if test="tSalaryAccount.deptNo != null and tSalaryAccount.deptNo.trim() != ''">
                    AND a.DEPT_NO like '%${tSalaryAccount.deptNo}%'
                </if>
                <if test="tSalaryAccount.deptName != null and tSalaryAccount.deptName.trim() != ''">
                    AND a.DEPT_NAME like '%${tSalaryAccount.deptName}%'
                </if>
                <if test="tSalaryAccount.empIdcard != null and tSalaryAccount.empIdcard.trim() != ''">
                    AND a.EMP_IDCARD like '%${tSalaryAccount.empIdcard}%'
                </if>
                <if test="tSalaryAccount.bankNo != null and tSalaryAccount.bankNo.trim() != ''">
                    AND a.BANK_NO like '%${tSalaryAccount.bankNo}%'
                </if>
                <if test="tSalaryAccount.bankName != null and tSalaryAccount.bankName.trim() != ''">
                    AND a.BANK_NAME = #{tSalaryAccount.bankName}
                </if>
                <if test="tSalaryAccount.salaryCount != null and tSalaryAccount.salaryCount.trim() != ''">
                    AND a.SALARY_COUNT = #{tSalaryAccount.salaryCount}
                </if>
                <if test="tSalaryAccount.salaryStyle != null and tSalaryAccount.salaryStyle.trim() != ''">
                    AND a.SALARY_STYLE = #{tSalaryAccount.salaryStyle}
                </if>
                <if test="tSalaryAccount.salaryGiveTime != null and tSalaryAccount.salaryGiveTime.trim() != ''">
                    AND a.SALARY_GIVE_TIME = #{tSalaryAccount.salaryGiveTime}
                </if>
                <if test="tSalaryAccount.distributionFlag != null and tSalaryAccount.distributionFlag.trim() != ''">
                    AND a.DISTRIBUTION_FLAG = #{tSalaryAccount.distributionFlag}
                </if>
                <if test="tSalaryAccount.deduSocialMonth != null and tSalaryAccount.deduSocialMonth.trim() != ''">
                    AND a.DEDU_SOCIAL_MONTH = #{tSalaryAccount.deduSocialMonth}
                </if>
                <if test="tSalaryAccount.deduProvidentMonth != null and tSalaryAccount.deduProvidentMonth.trim() != ''">
                    AND a.DEDU_PROVIDENT_MONTH = #{tSalaryAccount.deduProvidentMonth}
                </if>
                <if test="tSalaryAccount.formType != null and tSalaryAccount.formType.trim() != ''">
                    AND a.FORM_TYPE = #{tSalaryAccount.formType}
                </if>
                <if test="tSalaryAccount.salaryTaxFlag != null and tSalaryAccount.salaryTaxFlag.trim() != ''">
                    AND a.SALARY_TAX_FLAG = #{tSalaryAccount.salaryTaxFlag}
                </if>
                <if test="tSalaryAccount.settlementMonth != null and tSalaryAccount.settlementMonth.trim() != ''">
                    AND a.SETTLEMENT_MONTH = #{tSalaryAccount.settlementMonth}
                </if>
                <if test="tSalaryAccount.socialPriority != null and tSalaryAccount.socialPriority.trim() != ''">
                    AND a.SOCIAL_PRIORITY = #{tSalaryAccount.socialPriority}
                </if>
                <if test="tSalaryAccount.fundPriority != null and tSalaryAccount.fundPriority.trim() != ''">
                    AND a.FUND_PRIORITY = #{tSalaryAccount.fundPriority}
                </if>
                <if test="tSalaryAccount.annualBonusType != null and tSalaryAccount.annualBonusType.trim() != ''">
                    AND a.ANNUAL_BONUS_TYPE = #{tSalaryAccount.annualBonusType}
                </if>
                <if test="createUserId != null">
                    AND s.CREATE_USER = #{createUserId}
                </if>
                <if test="userDeptId != null">
                    AND u.dept_id = #{userDeptId}
                </if>
                <if test="unitName != null">
                    AND d.CUSTOMER_NAME = '${unitName}'
                </if>
                <if test="settleMonthStart != null">
                    AND a.SETTLEMENT_MONTH >= '${settleMonthStart}'
                </if>
                <if test="settleMonthEnd != null">
                    AND a.SETTLEMENT_MONTH <![CDATA[<=]]> '${settleMonthEnd}'
                </if>
            </if>
        </where>
        order by a.SETTLEMENT_MONTH,a.id desc
    </select>

    <sql id="Base_Column_List">
                a.ID,
                a.SALARY_ACCOUNT_ID,
                a.CN_NAME,
                a.JAVA_FIED_NAME,
                a.SALARY_MONEY,
                a.IS_TAX,
                a.TEXT_VALUE
    </sql>
    <sql id="tSalaryAccountItem_where">
        <if test="tSalaryAccountItem != null">
                    <if test="tSalaryAccountItem.id != null and tSalaryAccountItem.id.trim() != ''">
                        AND a.ID = #{tSalaryAccountItem.id}
                    </if>
                    <if test="tSalaryAccountItem.salaryAccountId != null and tSalaryAccountItem.salaryAccountId.trim() != ''">
                        AND a.SALARY_ACCOUNT_ID = #{tSalaryAccountItem.salaryAccountId}
                    </if>
                    <if test="tSalaryAccountItem.cnName != null and tSalaryAccountItem.cnName.trim() != ''">
                        AND a.CN_NAME = #{tSalaryAccountItem.cnName}
                    </if>
                    <if test="tSalaryAccountItem.javaFiedName != null and tSalaryAccountItem.javaFiedName.trim() != ''">
                        AND a.JAVA_FIED_NAME = #{tSalaryAccountItem.javaFiedName}
                    </if>
                    <if test="tSalaryAccountItem.salaryMoney != null">
                        AND a.SALARY_MONEY = #{tSalaryAccountItem.salaryMoney}
                    </if>
                    <if test="tSalaryAccountItem.isTax != null">
                        AND a.IS_TAX = #{tSalaryAccountItem.isTax}
                    </if>
                    <if test="tSalaryAccountItem.textValue != null and tSalaryAccountItem.textValue.trim() != ''">
                        AND a.TEXT_VALUE = #{tSalaryAccountItem.textValue}
                    </if>
        </if>
    </sql>

    <!-- 获取工资明细 -->
    <select id="getItemSet" resultType="java.lang.String">
        select
            i.cn_name a
        from t_salary_account_item i
        LEFT JOIN t_salary_account a on a.id=i.SALARY_ACCOUNT_ID
        where
            a.SALARY_FORM_ID = #{salaryId}
        GROUP BY i.CN_NAME
        order by i.JAVA_FIED_NAME desc,i.id
    </select>

	<!-- 获取所有数据，组装map，工资导入使用 -->
	<select id="getAllItemVoList" resultMap="itemVoMap">
		SELECT
		i.ID,
		i.SALARY_ACCOUNT_ID,
		i.CN_NAME,
		i.JAVA_FIED_NAME,
		i.SALARY_MONEY,
		i.TEXT_VALUE,
		i.IS_TAX,
		a.EMP_IDCARD,
		a.SETTLEMENT_MONTH,
		a.FORM_TYPE,
		a.ANNUAL_BONUS_TYPE
		FROM
		t_salary_account_item i left join t_salary_account a on i.SALARY_ACCOUNT_ID = a.ID
		<where>
			a.SETTLEMENT_MONTH like DATE_FORMAT(now(),"%Y%")
			<if test="idCardList != null">
				AND a.EMP_IDCARD in
				<foreach item="item" index="index" collection="idCardList" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
			<if test="invoiceTitle != null and invoiceTitle.trim() != ''">
				AND a.INVOICE_TITLE = #{invoiceTitle}
			</if>
			AND a.DELETE_FLAG = 0 and a.FORM_TYPE != 7
		</where>
	</select>

	<!-- 获取所有数据，组装map，工资导入使用 -->
	<select id="getSalaryItemVoList" resultMap="itemVoMap">
		SELECT
		i.ID,
		i.SALARY_ACCOUNT_ID,
		i.CN_NAME,
		i.JAVA_FIED_NAME,
		i.SALARY_MONEY,
		i.TEXT_VALUE,
		i.IS_TAX,
		a.EMP_IDCARD,
		a.SETTLEMENT_MONTH,
		a.FORM_TYPE,
		a.ANNUAL_BONUS_TYPE
		FROM
		t_salary_account_item i left join t_salary_account a on i.SALARY_ACCOUNT_ID = a.ID
		<where>
			a.SETTLEMENT_MONTH like DATE_FORMAT(now(),"%Y%")
			<if test="idCardList != null">
				AND a.EMP_IDCARD in
				<foreach item="item" index="index" collection="idCardList" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
			<if test="invoiceTitle != null and invoiceTitle.trim() != ''">
				AND a.INVOICE_TITLE = #{invoiceTitle}
			</if>
			AND a.DELETE_FLAG = 0 and a.FORM_TYPE in ('0','1','2')
		</where>
	</select>

	<!-- 获取所有数据，组装map，HCM使用 -->
	<select id="getSalaryItemVoListByHcm" resultMap="tSalaryAccountItemMap">
		SELECT
			<include refid="Base_Column_List"/>
		FROM t_salary_account_item a
		where a.SALARY_ACCOUNT_ID in
		<foreach item="item" index="index" collection="accIdList" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

	<select id="getSumByAccountId" resultType="java.util.Map">
		SELECT
		a.SALARY_ACCOUNT_ID as 'key',
		a.SALARY_MONEY  as 'value'
		FROM
		t_salary_account_item a
		WHERE
		a.SALARY_ACCOUNT_ID IN
		<foreach item="item" index="index" collection="account" open="(" separator="," close=")">
			#{item.id}
		</foreach>
		and
		a.JAVA_FIED_NAME='actualSalarySum'
	</select>


	<select id="getListByAccIdList" resultMap="tSalaryAccountItemMap">
		SELECT
		ID,
		SALARY_ACCOUNT_ID,
		CN_NAME,
		JAVA_FIED_NAME,
		SALARY_MONEY,
		TEXT_VALUE,
		IS_TAX
		FROM t_salary_account_item a
		where a.SALARY_ACCOUNT_ID in
		<foreach collection="accIdList" item="ids" open="(" separator="," close=")">
			#{ids}
		</foreach>
	</select>

</mapper>
