<?xml version="1.0" encoding="UTF-8"?>

<!--
  ~
  ~      Copyright (c) 2018-2025, lengleng All rights reserved.
  ~
  ~  Redistribution and use in source and binary forms, with or without
  ~  modification, are permitted provided that the following conditions are met:
  ~
  ~ Redistributions of source code must retain the above copyright notice,
  ~  this list of conditions and the following disclaimer.
  ~  Redistributions in binary form must reproduce the above copyright
  ~  notice, this list of conditions and the following disclaimer in the
  ~  documentation and/or other materials provided with the distribution.
  ~  Neither the name of the yifu4cloud.com developer nor the names of its
  ~  contributors may be used to endorse or promote products derived from
  ~  this software without specific prior written permission.
  ~  Author: lengleng (wangiegie@gmail.com)
  ~
  -->

<!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.TSalaryEmployeeMapper">

  <resultMap id="tSalaryEmployeeMap" type="com.yifu.cloud.plus.v1.yifu.salary.entity.TSalaryEmployee">
    <id property="id" column="ID"/>
    <result property="empName" column="EMP_NAME"/>
    <result property="empIdcard" column="EMP_IDCARD"/>
    <result property="unitId" column="UNIT_ID"/>
    <result property="unitName" column="UNIT_NAME"/>
    <result property="unitNo" column="UNIT_NO"/>
    <result property="deptId" column="DEPT_ID"/>
    <result property="deptName" column="DEPT_NAME"/>
    <result property="deptNo" column="DEPT_NO"/>
    <result property="createBy" column="CREATE_BY"/>
    <result property="createName" column="CREATE_NAME"/>
    <result property="createTime" column="CREATE_TIME"/>
    <result property="updateBy" column="UPDATE_BY"/>
    <result property="updateTime" column="UPDATE_TIME"/>
    <result property="bankName" column="BANK_NAME"/>
    <result property="bankSubName" column="BANK_SUB_NAME"/>
    <result property="bankNo" column="BANK_NO"/>
    <result property="invoiceTitle" column="INVOICE_TITLE"/>
    <result property="bankProvince" column="BANK_PROVINCE"/>
    <result property="bankCity" column="BANK_CITY"/>
    <result property="empPhone" column="EMP_PHONE"/>
    <result property="taxMonth" column="TAX_MONTH"/>
    <result property="fileStatus" column="FILE_STATUS"/>
  </resultMap>
	<resultMap id="salaryEmployeeExportMap" type="com.yifu.cloud.plus.v1.yifu.salary.vo.TSalaryEmployeeExportVo">
		<result property="empName" column="EMP_NAME"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="unitId" column="UNIT_ID"/>
		<result property="unitName" column="UNIT_NAME"/>
		<result property="unitNo" column="UNIT_NO"/>
		<result property="deptId" column="DEPT_ID"/>
		<result property="deptName" column="DEPT_NAME"/>
		<result property="deptNo" column="DEPT_NO"/>
		<result property="bankName" column="BANK_NAME"/>
		<result property="bankSubName" column="BANK_SUB_NAME"/>
		<result property="bankNo" column="BANK_NO"/>
		<result property="invoiceTitle" column="INVOICE_TITLE"/>
		<result property="bankProvince" column="BANK_PROVINCE"/>
		<result property="bankCity" column="BANK_CITY"/>
		<result property="empPhone" column="EMP_PHONE"/>
		<result property="taxMonth" column="TAX_MONTH"/>
		<result property="fileStatus" column="FILE_STATUS"/>
	</resultMap>
	<resultMap id="salaryEmpRosterMap" type="com.yifu.cloud.plus.v1.yifu.salary.vo.TSalaryEmpRosterVo">
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="bankName" column="BANK_NAME"/>
		<result property="bankSubName" column="BANK_SUB_NAME"/>
		<result property="bankNo" column="BANK_NO"/>
		<result property="bankProvince" column="BANK_PROVINCE"/>
		<result property="bankCity" column="BANK_CITY"/>
	</resultMap>
    <sql id="Base_Column_List">
                a.ID,
                a.EMP_NAME,
                a.EMP_IDCARD,
                a.UNIT_ID,
                a.UNIT_NAME,
                a.UNIT_NO,
                a.DEPT_ID,
                a.DEPT_NAME,
                a.DEPT_NO,
                a.CREATE_BY,
                a.CREATE_NAME,
                a.CREATE_TIME,
                a.UPDATE_BY,
                a.UPDATE_TIME,
                a.BANK_NAME,
                a.BANK_SUB_NAME,
                a.BANK_NO,
                a.INVOICE_TITLE,
                a.BANK_PROVINCE,
                a.BANK_CITY,
                a.EMP_PHONE,
                a.TAX_MONTH,
                a.FILE_STATUS
    </sql>
    <sql id="tSalaryEmployee_where">
        <if test="tSalaryEmployee != null">
                    <if test="tSalaryEmployee.id != null and tSalaryEmployee.id.trim() != ''">
                        AND a.ID = #{tSalaryEmployee.id}
                    </if>
                    <if test="tSalaryEmployee.empName != null and tSalaryEmployee.empName.trim() != ''">
                        AND a.EMP_NAME = #{tSalaryEmployee.empName}
                    </if>
                    <if test="tSalaryEmployee.empIdcard != null and tSalaryEmployee.empIdcard.trim() != ''">
                        AND a.EMP_IDCARD = #{tSalaryEmployee.empIdcard}
                    </if>
                    <if test="tSalaryEmployee.unitId != null and tSalaryEmployee.unitId.trim() != ''">
                        AND a.UNIT_ID = #{tSalaryEmployee.unitId}
                    </if>
                    <if test="tSalaryEmployee.unitName != null and tSalaryEmployee.unitName.trim() != ''">
                        AND a.UNIT_NAME = #{tSalaryEmployee.unitName}
                    </if>
                    <if test="tSalaryEmployee.unitNo != null and tSalaryEmployee.unitNo.trim() != ''">
                        AND a.UNIT_NO = #{tSalaryEmployee.unitNo}
                    </if>
                    <if test="tSalaryEmployee.deptId != null and tSalaryEmployee.deptId.trim() != ''">
                        AND a.DEPT_ID = #{tSalaryEmployee.deptId}
                    </if>
                    <if test="tSalaryEmployee.deptName != null and tSalaryEmployee.deptName.trim() != ''">
                        AND a.DEPT_NAME like concat('%',#{tSalaryEmployee.deptName},'%')
                    </if>
                    <if test="tSalaryEmployee.deptNo != null and tSalaryEmployee.deptNo.trim() != ''">
                        AND a.DEPT_NO = #{tSalaryEmployee.deptNo}
                    </if>
                    <if test="tSalaryEmployee.createBy != null and tSalaryEmployee.createBy.trim() != ''">
                        AND a.CREATE_BY = #{tSalaryEmployee.createBy}
                    </if>
                    <if test="tSalaryEmployee.createName != null and tSalaryEmployee.createName.trim() != ''">
                        AND a.CREATE_NAME = #{tSalaryEmployee.createName}
                    </if>
                    <if test="tSalaryEmployee.createTime != null">
                        AND a.CREATE_TIME = #{tSalaryEmployee.createTime}
                    </if>
                    <if test="tSalaryEmployee.updateBy != null and tSalaryEmployee.updateBy.trim() != ''">
                        AND a.UPDATE_BY = #{tSalaryEmployee.updateBy}
                    </if>
                    <if test="tSalaryEmployee.updateTime != null">
                        AND a.UPDATE_TIME = #{tSalaryEmployee.updateTime}
                    </if>
                    <if test="tSalaryEmployee.bankName != null and tSalaryEmployee.bankName.trim() != ''">
                        AND a.BANK_NAME = #{tSalaryEmployee.bankName}
                    </if>
                    <if test="tSalaryEmployee.bankSubName != null and tSalaryEmployee.bankSubName.trim() != ''">
                        AND a.BANK_SUB_NAME = #{tSalaryEmployee.bankSubName}
                    </if>
                    <if test="tSalaryEmployee.bankNo != null and tSalaryEmployee.bankNo.trim() != ''">
                        AND a.BANK_NO = #{tSalaryEmployee.bankNo}
                    </if>
                    <if test="tSalaryEmployee.invoiceTitle != null and tSalaryEmployee.invoiceTitle.trim() != ''">
                        AND a.INVOICE_TITLE like concat('%',#{tSalaryEmployee.invoiceTitle},'%')
                    </if>
                    <if test="tSalaryEmployee.bankProvince != null">
                        AND a.BANK_PROVINCE = #{tSalaryEmployee.bankProvince}
                    </if>
                    <if test="tSalaryEmployee.bankCity != null">
                        AND a.BANK_CITY = #{tSalaryEmployee.bankCity}
                    </if>
                    <if test="tSalaryEmployee.empPhone != null and tSalaryEmployee.empPhone.trim() != ''">
                        AND a.EMP_PHONE = #{tSalaryEmployee.empPhone}
                    </if>
                    <if test="tSalaryEmployee.taxMonth != null and tSalaryEmployee.taxMonth.trim() != ''">
                        AND a.TAX_MONTH = #{tSalaryEmployee.taxMonth}
                    </if>
                    <if test="tSalaryEmployee.fileStatus != null">
                        AND a.FILE_STATUS = #{tSalaryEmployee.fileStatus}
                    </if>
        </if>
    </sql>
    <!--tSalaryEmployee简单分页查询-->
    <select id="getTSalaryEmployeePage" resultMap="tSalaryEmployeeMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_salary_employee a
        <where>
            1=1
            <include refid="tSalaryEmployee_where"/>
			<if test="tSalaryEmployee.authSql != null and tSalaryEmployee.authSql.trim() != ''">
				${tSalaryEmployee.authSql}
			</if>
			ORDER BY a.CREATE_TIME DESC
		</where>
    </select>

	<select id="noPageDiy" resultMap="salaryEmployeeExportMap" parameterType="com.yifu.cloud.plus.v1.yifu.salary.vo.TSalaryEmployeeExportVo">
		SELECT
		a.ID,
		a.EMP_NAME,
		a.EMP_IDCARD,
		a.UNIT_ID,
		a.UNIT_NAME,
		a.UNIT_NO,
		a.DEPT_ID,
		a.DEPT_NAME,
		a.DEPT_NO,
		a.CREATE_BY,
		a.CREATE_NAME,
		a.CREATE_TIME,
		a.UPDATE_BY,
		a.UPDATE_TIME,
		a.BANK_NAME,
		a.BANK_SUB_NAME,
		a.BANK_NO,
		a.INVOICE_TITLE,
		a1.area_name as "BANK_PROVINCE",
		a2.area_name as "BANK_CITY",
		a.EMP_PHONE,
		a.TAX_MONTH,
		case when a.FILE_STATUS = 0 then "在职"
		when a.FILE_STATUS = 1 then "离职"
		else null  end  as  "FILE_STATUS"
		FROM t_salary_employee a
		left join sys_area a1 on a1.id=a.BANK_PROVINCE
		left join sys_area a2 on a2.id=a.BANK_CITY
		<where>
			1=1
			<include refid="where_export"/>
			<if test="tSalaryEmployee.authSql != null and tSalaryEmployee.authSql.trim() != ''">
				${tSalaryEmployee.authSql}
			</if>
			ORDER BY a.CREATE_TIME DESC
		</where>
		<if test="tSalaryEmployee != null">
			<if test="tSalaryEmployee.limitStart != null">
				limit #{tSalaryEmployee.limitStart},#{tSalaryEmployee.limitEnd}
			</if>
		</if>
	</select>

	<sql id="where_export">
		<include refid="tSalaryEmployee_where"/>
		<if test="idList != null and idList.size>0">
			AND a.id in
			<foreach item="idStr" index="index" collection="idList" open="(" separator="," close=")">
				#{idStr}
			</foreach>
		</if>
	</sql>

	<!-- 按身份证查询全部人员 -->
	<select id="getListByIdCard" resultMap="tSalaryEmployeeMap" >
		<if test="idCardList != null and idCardList.size>0">
			SELECT
			<include refid="Base_Column_List"/>
			FROM t_salary_employee a
			where a.EMP_IDCARD in
			<foreach item="idStr" index="index" collection="idCardList" open="(" separator="," close=")">
				#{idStr}
			</foreach>
		</if>
	</select>

	<!-- 按手机号查询全部人员 -->
	<select id="getListByPhone" resultMap="tSalaryEmployeeMap" >
		<if test="phoneList != null and phoneList.size>0">
			SELECT
			<include refid="Base_Column_List"/>
			FROM t_salary_employee a
			where a.EMP_PHONE in
			<foreach item="idStr" index="index" collection="phoneList" open="(" separator="," close=")">
				#{idStr}
			</foreach>
		</if>
	</select>

	<!-- 按身份证查询人员 -->
	<select id="getByEmpIdCard" resultMap="tSalaryEmployeeMap" >
		SELECT
			<include refid="Base_Column_List"/>
		FROM t_salary_employee a
		where a.EMP_IDCARD = #{empIdCard}
		ORDER BY a.BANK_CITY DESC LIMIT 1
	</select>

	<select id="selectByIdCard" resultType="com.yifu.cloud.plus.v1.yifu.salary.entity.TSalaryAccountItem">
		SELECT
			a.ID,
			a.SALARY_ACCOUNT_ID,
			a.CN_NAME,
			a.JAVA_FIED_NAME,
			SUM(a.SALARY_MONEY)SALARY_MONEY,
			a.IS_TAX,
			a.TEXT_VALUE,
			b.EMP_IDCARD,
			b.SALARY_MONTH,
			b.DELETE_FLAG
		FROM `t_salary_account_item`  a LEFT JOIN t_salary_account b on a.SALARY_ACCOUNT_ID =b.ID
		WHERE  a.JAVA_FIED_NAME ='phoneSubsidy'
		  and b.EMP_IDCARD= #{empIdCard}
		  and b.SETTLEMENT_MONTH=#{nowMonth}
		   and b.DELETE_FLAG='0'
	</select>
	<!--  人员花名册薪资信息  -->
	<select id="getSalaryEmpRoster" resultMap="salaryEmpRosterMap" parameterType="com.yifu.cloud.plus.v1.yifu.salary.vo.TSalaryEmpRosterVo">
		select  a.EMP_IDCARD,BANK_NO,BANK_NAME,BANK_SUB_NAME,a.BANK_PROVINCE,a.BANK_CITY from t_salary_employee  a
		LEFT JOIN sys_area a1 on a.BANK_PROVINCE=a1.id
		LEFT JOIN sys_area a2 on a.BANK_CITY= a2.id
		<where>
			1=1
			<if test="idCards != null and idCards.size>0">
				AND a.EMP_IDCARD in
				<foreach item="idCard" index="index" collection="idCards" open="(" separator="," close=")">
					#{idCard}
				</foreach>
			</if>
		</where>

	</select>
</mapper>
