<?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="cn.ezhizao.project.business.finance.IndividualIncomeTax.mapper.BizFinancialIndividualIncomeTaxMapper">

    <resultMap type="cn.ezhizao.project.business.finance.IndividualIncomeTax.domain.BizFinancialIndividualIncomeTax"
               id="BizFinancialIndividualIncomeTaxResult">
        <id column="id" property="id"/>
        <result column="account_sets_id" jdbcType="INTEGER" property="accountSetsId"/>
        <result column="tenant_id" jdbcType="BIGINT" property="tenantId"/>
        <result column="company_id" jdbcType="BIGINT" property="companyId"/>
        <association property="companyName" javaType="java.lang.String" select="getCompanyName" column="company_id"/>
        <association property="socialCreditCode" javaType="java.lang.String" select="getSocialCreditCode"
                     column="company_id"/>
        <association property="fromCompanyName" javaType="java.lang.String" select="getFromCompanyName"
                     column="tenant_id"/>
        <association property="taxType" javaType="java.lang.String" select="getTaxType" column="company_id"/>
        <association property="currentEmployeeName" javaType="java.lang.String" select="getCurrentEmployeeName"
                     column="current_employee_id"/>
        <association property="evidenceFile" javaType="java.lang.String" select="getCurrentEvidenceFile" column="id"/>
        <association property="companyId" javaType="java.lang.Long" select="getCompanyId" column="id"/>
        <association property="serviceName" select="getServiceName" column="company_id"></association>
    </resultMap>

    <resultMap type="cn.ezhizao.project.business.salary.domain.BizFinancialSalaryDetailEmployee"
               id="BizFinancialSalaryDetailEmployeeResult">
        <id column="id" property="id"/>
    </resultMap>

    <resultMap type="cn.ezhizao.project.business.salary.domain.BizFinancialSalaryDetail"
               id="BizFinancialSalaryDetailResult">
        <id column="id" property="id"/>
    </resultMap>

    <resultMap type="cn.ezhizao.project.business.salary.domain.BizFinancialSalary" id="BizFinancialSalaryResult">
        <id column="id" property="id"/>
    </resultMap>

    <resultMap
            type="cn.ezhizao.project.business.finance.IndividualIncomeTax.domain.BizFinancialIndividualIncomeTaxDetail"
            id="BizFinancialIndividualIncomeTaxDetailResult">
        <id column="id" property="id"/>
        <collection property="reportFiles"
                    ofType="cn.ezhizao.project.business.reportTaxIsZero.domain.BizFinancialReportTaxDetailReport"
                    select="getFiles" column="id"/>

    </resultMap>


    <resultMap type="cn.ezhizao.project.system.domain.SysFileStorage" id="FileStorageResult">
        <id column="id" property="id"/>
    </resultMap>

    <select id="getList" parameterType="BizFinancialIndividualIncomeTax"
            resultMap="BizFinancialIndividualIncomeTaxResult">
        SELECT *, c.customer_label_id, t4.lable as customerLabelName, t6.nick_name as verifierName,t8.account_name as toTenantName,
        t5.verifier_time FROM biz_financial_individual_income_tax f
        LEFT JOIN biz_company c ON f.company_id = c.id
        LEFT JOIN biz_tenant t ON f.tenant_id=t.id
        left join biz_lable t4 on t4.id = c.customer_label_id
        left join biz_financial_individual_income_tax_detail t5 on t5.financial_individual_income_tax_id = f.id
        and t5.deleted = 0 and t5.month = #{month} and t5.year = #{year} and t5.financial_individual_income_tax_id = f.id
        left join sys_user t6 on t6.user_id = t5.verifier_id
        left join biz_entrust t9 on t9.work_order_id = f.work_order_id and t9.deleted = 0 and  date_format(t9.current_month, '%Y%m') = concat(#{year}, #{month})
        left join biz_tenant t8 on t8.id = t9.to_tenant_id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            f.deleted = 0
            AND c.is_zero = 0 and c.deleted = 0 and f.is_stop = 0
            <if test="currentEmployeeId != null ">AND current_employee_id = #{currentEmployeeId}</if>
            <if test="principal != null and principal != ''">
                AND (c.leader_id=#{principal} OR c.adviser_id=#{principal})
            </if>
            <if test="companyName != null and companyName !=''  ">AND c.name like concat('%', #{companyName}, '%')</if>

            <if test="taxType != null and taxType != null">
                AND c.tax_type = #{taxType}
            </if>
            <if test="verifierName != null">
                AND t6.nick_name like concat('%', #{verifierName}, '%')
            </if>
            <if test="dateFilter != null and dateFilter.size()>0 ">
                AND (
                DATE_FORMAT(t5.verifier_time,'%Y-%m-%d') &gt;= #{dateFilter[0]}
                AND
                DATE_FORMAT(t5.verifier_time,'%Y-%m-%d') &lt;= #{dateFilter[1]}
                )
            </if>
            <if test="customerLabelId != null">
                AND c.customer_label_id = #{customerLabelId}
            </if>
            <if test="month != null and year != null">
                <if test="status != null and status != 0">
                    and exists (select 1 from biz_financial_individual_income_tax_detail f3 where
                    f3.financial_individual_income_tax_id = f.id
                    and f3.month = #{month}
                    and f3.year = #{year}
                    and f3.status = #{status}
                    )
                </if>
                <if test="tenantId != null">
                    AND exists (select 1 from biz_entrust eb
                    where eb.deleted = 0
                    and (eb.to_tenant_id = #{tenantId} or eb.from_tenant_id = #{tenantId})
                    and date_format(eb.current_month, '%Y%m') = concat(#{year}, #{month})
                    and eb.work_order_id = f.work_order_id
                    )
                </if>
                <if test="status != null and status == 0">
                    AND not exists (select 1 from biz_financial_individual_income_tax_detail fsd where
                    fsd.financial_individual_income_tax_id =
                    f.id and
                    fsd.month = #{month} and fsd.year = #{year})
                </if>
            </if>
            <if test="fromCompanyName != null and fromCompanyName != ''">
                AND t.account_name like concat('%', #{fromCompanyName}, '%')
            </if>
            <if test="socialCreditCode != null and socialCreditCode!=''">
                AND c.social_credit_code = #{socialCreditCode}
            </if>
        </trim>
    </select>

    <select id="getServiceName" resultType="String">
        SELECT s.nick_name FROM sys_user s
        WHERE s.user_id =(SELECT leader_id  FROM biz_company WHERE id=#{company_id})
    </select>

    <select id="getFiles" resultMap="FileStorageResult">
        select *
        from sys_file_storage
        where deleted = 0
          and master_id = #{id}
    </select>



    <select id="getTaxDetail" parameterType="BizFinancialIndividualIncomeTaxDetail"
            resultMap="BizFinancialIndividualIncomeTaxDetailResult">
        SELECT *
        FROM biz_financial_individual_income_tax_detail
        WHERE financial_individual_income_tax_id = #{id}
                  AND year = #{year}
                  AND month = #{month}
          AND deleted = 0
    </select>


    <select id="getSalary" parameterType="BizFinancialSalary" resultMap="BizFinancialSalaryResult">
        SELECT *
        FROM biz_financial_salary
        WHERE work_order_id = #{workOrderId}
          AND deleted = 0;
    </select>


    <select id="getSalaryDetail" parameterType="BizFinancialSalaryDetail" resultMap="BizFinancialSalaryDetailResult">
        SELECT *
        FROM biz_financial_salary_detail
        WHERE financial_salary_id = #{id}
                  AND year = #{year}
                  AND month = #{month}
          AND deleted = 0
    </select>

    <select id="getSalaryEmployee" parameterType="BizFinancialSalaryDetailEmployee"
            resultMap="BizFinancialSalaryDetailEmployeeResult">
        SELECT *
        FROM biz_financial_salary_detail_employee
        WHERE financial_salary_detail_id = #{id};
    </select>

    <select id="getCompanyId" resultType="java.lang.Long">
        SELECT company_id
        FROM biz_financial_individual_income_tax
        WHERE id = #{id};
    </select>

    <select id="getCurrentEvidenceFile" resultType="java.lang.String">
        SELECT evidence_file
        FROM biz_financial_individual_income_tax_detail
        WHERE financial_individual_income_tax_id = #{id}
                  AND year = #{year}
                  AND month = #{month}
    </select>

    <select id="getCurrentEmployeeName" resultType="java.lang.String">
        SELECT nick_name
        FROM sys_user
        WHERE user_id = #{current_employee_id};
    </select>

    <select id="getFromCompanyName" resultType="java.lang.String">
        SELECT account_name
        FROM biz_tenant
        WHERE id = #{tenant_id};
    </select>

    <select id="getTaxType" resultType="java.lang.String">
        SELECT tax_type
        FROM biz_company
        WHERE id = #{company_id};
    </select>

    <select id="getSocialCreditCode" resultType="java.lang.String">
        SELECT social_credit_code
        FROM biz_company
        WHERE id = #{company_id};
    </select>

    <select id="getCompanyName" resultType="java.lang.String">
        SELECT name
        FROM biz_company
        WHERE id = #{company_id};
    </select>

    <delete id="physicalDelete">
        DELETE FROM biz_financial_individual_income_tax
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            <if test="id != null">
                id = #{id} AND
            </if>
            <!-- 删除条件为其他外键可以在这里加 -->
        </trim>
    </delete>
</mapper>
