<?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.jeeplus.assess.invoice.mapper.FinanceInvoiceMapper">

    <resultMap id="BaseResultMap" type="com.jeeplus.assess.invoice.service.dto.FinanceInvoiceDTO">
            <id property="id" column="id" jdbcType="VARCHAR"/>
            <result property="createBy.id" column="create_by_id" jdbcType="VARCHAR"/>
            <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
            <result property="updateBy.id" column="update_by_id" jdbcType="VARCHAR"/>
            <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
            <result property="delFlag" column="del_flag" jdbcType="INTEGER"/>
            <result property="remarks" column="remarks" jdbcType="VARCHAR"/>
            <result property="type" column="type" jdbcType="VARCHAR"/>
            <result property="no" column="no" jdbcType="VARCHAR"/>
            <result property="billingType" column="billing_type" jdbcType="VARCHAR"/>
            <result property="billingWorkplaceReal" column="billing_workplace_real" jdbcType="VARCHAR"/>
            <result property="taxpayerIdentificationNo" column="taxpayer_identification_no" jdbcType="VARCHAR"/>
            <result property="address" column="address" jdbcType="VARCHAR"/>
            <result property="telPhone" column="tel_phone" jdbcType="VARCHAR"/>
            <result property="openBank" column="open_bank" jdbcType="VARCHAR"/>
            <result property="bankAccount" column="bank_account" jdbcType="VARCHAR"/>
            <result property="name" column="name" jdbcType="VARCHAR"/>
            <result property="receivablesType" column="receivables_type" jdbcType="VARCHAR"/>
            <result property="billingContent" column="billing_content" jdbcType="VARCHAR"/>
            <result property="account" column="account" jdbcType="VARCHAR"/>
            <result property="billingContentTerms" column="billing_content_terms" jdbcType="VARCHAR"/>
            <result property="billingPeople" column="billing_people" jdbcType="VARCHAR"/>
            <result property="billingDate" column="billing_date" jdbcType="TIMESTAMP"/>
            <result property="collectDate" column="collect_date" jdbcType="TIMESTAMP"/>
            <result property="billingPeopleReal" column="billing_people_real" jdbcType="VARCHAR"/>
            <result property="reconciliationPeople" column="reconciliation_people" jdbcType="VARCHAR"/>
            <result property="reconciliationArea" column="reconciliation_area" jdbcType="VARCHAR"/>
            <result property="billingWorkplaceRealId" column="billing_workplace_real_id" jdbcType="VARCHAR"/>
            <result property="billingPeopleId" column="billing_people_id" jdbcType="VARCHAR"/>
            <result property="status" column="status" jdbcType="VARCHAR"/>
            <result property="procInsId" column="proc_ins_id" jdbcType="VARCHAR"/>
            <result property="processDefinitionId" column="process_definition_id" jdbcType="VARCHAR"/>
            <result property="receivablesStatus" column="receivables_status" jdbcType="VARCHAR"/>
            <result property="invalidStatus" column="invalid_status" jdbcType="VARCHAR"/>
            <result property="receivablesDate" column="receivables_date" jdbcType="TIMESTAMP"/>
            <result property="operator" column="operator" jdbcType="VARCHAR"/>
            <result property="operatorOffice" column="operator_office" jdbcType="VARCHAR"/>
            <result property="taskId" column="task_id" jdbcType="VARCHAR"/>
            <result property="taskIdInvalid" column="task_id_invalid" jdbcType="VARCHAR"/>
            <result property="isMultiple" column="is_multiple" jdbcType="VARCHAR"/>
            <result property="redInvoiceFlag" column="red_invoice_flag" jdbcType="VARCHAR"/>
            <result property="redInvoiceRelevancyId" column="red_invoice_relevancy_id" jdbcType="VARCHAR"/>
            <result property="redInvoiceRelevancyNumber" column="red_invoice_relevancy_number" jdbcType="VARCHAR"/>

        <result property="actualDrawerEmailAddress" column="actual_drawer_email_address" jdbcType="VARCHAR"/>
            <association property="financeInvoiceInvalidDTO" column="id" select="getFii" javaType="com.jeeplus.assess.invoice.service.dto.FinanceInvoiceInvalidDTO"></association>
            <collection property="financeInvoiceReceivablesDTOList" column="id" select="getFirList" ofType="com.jeeplus.assess.invoice.service.dto.FinanceInvoiceReceivablesDTO"></collection>
            <collection property="financeInvoiceBaseDTOList" column="id" select="getBaseList" ofType="com.jeeplus.assess.invoice.service.dto.FinanceInvoiceBaseDTO"></collection>
            <collection property="financeInvoiceDetailDTOList" column="id" select="getFidList" ofType="com.jeeplus.assess.invoice.service.dto.FinanceInvoiceDetailDTO"></collection>
    </resultMap>

    <sql id="FIR_Column_List">
        fir.id,
        fir.create_by_id,
        fir.create_time,
        fir.update_by_id,
        fir.update_time,
        fir.del_flag,
        fir.remarks,
        fir.invoice_id,
        fir.remittance_unit,
        fir.remittance_amount,
        fir.remittance_date,
        fir.status
    </sql>

    <sql id="FII_Column_List">
        fii.id,
        fii.create_by_id,
        fii.create_time,
        fii.update_by_id,
        fii.update_time,
        fii.del_flag,
        fii.remarks,
        fii.invoice_id,
        fii.invalid_status,
        fii.proc_ins_id,
        fii.process_definition_id
    </sql>

    <sql id="FID_Column_List">
        fid.id,
        fid.create_by_id,
        fid.create_time,
        fid.update_by_id,
        fid.update_time,
        fid.del_flag,
        fid.remarks,
        fid.invoice_id,
        fid.code,
        fid.number,
        fid.account,
        fid.rate,
        fid.amount,
        fid.tax,
        fid.all_amount
    </sql>

    <sql id="FIB_Column_List">
        fib.id,
        fib.create_by_id,
        fib.create_time,
        fib.update_by_id,
        fib.update_time,
        fib.del_flag,
        fib.invoice_id,
        fib.program_name,
        fib.program_id,
        fib.contract_name,
        fib.location,
        fib.program_no,
        fib.client_name,
        fib.client_id,
        fib.report_no
    </sql>

    <sql id="Base_Column_List">
        fi.id,
        fi.create_by_id,
        fi.create_time,
        fi.update_by_id,
        fi.update_time,
        fi.del_flag,
        fi.remarks,
        fi.type,
        fi.no,
        fi.billing_type,
        fi.billing_workplace_real,
        fi.taxpayer_identification_no,
        fi.address,
        fi.tel_phone,
        fi.open_bank,
        fi.bank_account,
        fi.name,
        fi.receivables_type,
        fi.billing_content,
        fi.account,
        fi.billing_content_terms,
        fi.billing_people,
        fi.billing_date,
        fi.collect_date,
        fi.billing_people_real,
        fi.reconciliation_people,
        fi.reconciliation_area,
        fi.billing_workplace_real_id,
        fi.billing_people_id,
        fi.status,
        fi.proc_ins_id,
        fi.process_definition_id,
        fi.receivables_status,
        fi.invalid_status,
        fi.receivables_date,
        fi.is_multiple,
        fi.actual_drawer_email_address,
        fi.red_invoice_flag,
        fi.red_invoice_relevancy_id,
        fi.red_invoice_relevancy_number
    </sql>

    <select id="getFii" resultType="com.jeeplus.assess.invoice.service.dto.FinanceInvoiceInvalidDTO">
        select
        <include refid="FII_Column_List"></include>
        from finance_invoice_invalid fii
        where fii.del_flag = '0' and fii.invoice_id = ${id}
    </select>

    <select id="getFirList" resultType="com.jeeplus.assess.invoice.service.dto.FinanceInvoiceReceivablesDTO">
        select
        <include refid="FIR_Column_List"></include>
        from finance_invoice_receivables fir
        where fir.del_flag = '0' and fir.invoice_id = ${id}
    </select>

    <select id="getFidList" resultType="com.jeeplus.assess.invoice.service.dto.FinanceInvoiceDetailDTO">
        select
        <include refid="FID_Column_List"></include>
        from finance_invoice_detail fid
        where fid.del_flag = '0' and fid.invoice_id = ${id}
    </select>

    <select id="getBaseList" resultType="com.jeeplus.assess.invoice.service.dto.FinanceInvoiceBaseDTO">
        select
        <include refid="FIB_Column_List"></include>,
        pa1.id as auditId1,
        pa2.id as auditId2,
        pa3.id as auditId3,
        pa.id as archive_id
        from finance_invoice_base fib
        LEFT JOIN program_audit pa1 ON pa1.program_id = fib.program_id and pa1.audit_level = '1' and pa1.del_flag = '0'
        LEFT JOIN program_audit pa2 ON pa2.program_id = fib.program_id and pa2.audit_level = '2' and pa2.del_flag = '0'
        LEFT JOIN program_audit pa3 ON pa3.program_id = fib.program_id and pa3.audit_level = '3' and pa3.del_flag = '0'
        LEFT JOIN program_archive pa ON pa.program_id = fib.program_id and pa.del_flag = '0'
        where fib.del_flag = '0' and fib.invoice_id = ${id}
    </select>

    <select id="findList" resultMap="BaseResultMap">
        select
        DISTINCT
        <include refid="Base_Column_List"></include>,
        su.name as operator,
        so.name as operator_office,
        fibs.report_no,
        fibs.program_no,
        (select
        CASE
        WHEN RIGHT( GROUP_CONCAT(case when program_id is null then 2 else 1 end),1) =2 THEN GROUP_CONCAT(concat('其他：',wipr1.program_name))
        WHEN RIGHT( GROUP_CONCAT(case when program_id is null then 2 else 1 end),1) =1 THEN GROUP_CONCAT(rpr1.name)
        ELSE '' END
        FROM finance_invoice_base wipr1
        LEFT JOIN program_project_list_info rpr1 ON wipr1.program_id = rpr1.id
        WHERE wipr1.invoice_id = fi.id) as programName,
        cfid.number as "number",
        cfid.account as "accountDetail",
        art.ID_ as task_id,
        art_invalid.ID_ as task_id_invalid,
        su1.name as reconciliationPeopleName
        from finance_invoice fi
        left join finance_invoice_base fibs on fibs.invoice_id = fi.id and fibs.del_flag = '0'
        left join finance_invoice_detail cfid on cfid.invoice_id = fi.id and cfid.del_flag = '0'
        left join sys_user su on fi.create_by_id = su.id and su.del_flag = '0'
--         left join sys_user_manage_office sumo on sumo.office_id = su.office_id
        left join sys_office so on su.office_id = so.id and so.del_flag = '0'
        left join act_ru_task art on fi.proc_ins_id = art.PROC_INST_ID_
        left join finance_invoice_invalid invalid on invalid.invoice_id = fi.id and invalid.del_flag = '0'
        left join act_ru_task art_invalid on invalid.proc_ins_id = art_invalid.PROC_INST_ID_
        left join sys_user su1 on su1.id = fi.reconciliation_people
        ${ew.customSqlSegment}
    </select>

    <select id="findRedList" resultMap="BaseResultMap">
        select
        DISTINCT
        <include refid="Base_Column_List"></include>,
        su.name as operator,
        so.name as operator_office,
        (select
        CASE
        WHEN RIGHT( GROUP_CONCAT(case when program_id is null then 2 else 1 end),1) =2 THEN GROUP_CONCAT(concat('其他：',wipr1.program_name))
        WHEN RIGHT( GROUP_CONCAT(case when program_id is null then 2 else 1 end),1) =1 THEN GROUP_CONCAT(rpr1.name)
        ELSE '' END
        FROM finance_invoice_base wipr1
        LEFT JOIN program_project_list_info rpr1 ON wipr1.program_id = rpr1.id
        WHERE wipr1.invoice_id = fi.id) as programName,
        cfid.number as "number",
        cfid.account as "accountDetail",
        art.ID_ as task_id,
        art_invalid.ID_ as task_id_invalid,
        su1.name as reconciliationPeopleName
        from finance_invoice fi
        left join finance_invoice_detail cfid on cfid.invoice_id = fi.id and cfid.del_flag = '0'
        left join sys_user su on fi.create_by_id = su.id and su.del_flag = '0'
        --         left join sys_user_manage_office sumo on sumo.office_id = su.office_id
        left join sys_office so on su.office_id = so.id and so.del_flag = '0'
        left join act_ru_task art on fi.proc_ins_id = art.PROC_INST_ID_
        left join finance_invoice_invalid invalid on invalid.invoice_id = fi.id and invalid.del_flag = '0'
        left join act_ru_task art_invalid on invalid.proc_ins_id = art_invalid.PROC_INST_ID_
        left join sys_user su1 on su1.id = fi.reconciliation_people
        ${ew.customSqlSegment}
    </select>

    <select id="queryById" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"></include>,
        su.name as reconciliationPeopleName,
        su1.name as billingPeopleRealName
        from finance_invoice fi
        left join sys_user su on fi.reconciliation_people = su.id
        left join sys_user su1 on fi.billing_people_real = su1.id
        where fi.del_flag = '0' and fi.id = ${id}
    </select>

    <update id="setReceivablesDateNull" parameterType="string">
        update finance_invoice set receivables_date = null where id = #{id}
    </update>

    <select id="getExceedFinance" resultType="com.jeeplus.assess.invoice.service.dto.FinanceInvoiceDTO">
        SELECT
        DISTINCT
        <include refid="Base_Column_List"></include>,
        (select
        CASE
        WHEN RIGHT( GROUP_CONCAT(case when program_id is null then 2 else 1 end),1) =2 THEN GROUP_CONCAT(concat('其他：',wipr1.program_name))
        WHEN RIGHT( GROUP_CONCAT(case when program_id is null then 2 else 1 end),1) =1 THEN GROUP_CONCAT(rpr1.name)
        ELSE '' END
        FROM finance_invoice_base wipr1
        LEFT JOIN program_project_list_info rpr1 ON wipr1.program_id = rpr1.id
        WHERE wipr1.invoice_id = fi.id) as programName,
        fi.create_by_id as 'createBy.id'
        FROM
        finance_invoice_base a
        left join finance_invoice fi on a.invoice_id = fi.id
        LEFT JOIN program_project_list_info pr on a.program_id=pr.id
        WHERE
        ( fi.receivables_date IS NULL and fi.receivables_status = '0' )
        and fi.status != 1 and fi.status != 2 and fi.status != 3 and fi.status != 4 and fi.status != 7
        AND DATE( fi.billing_date ) &lt;= DATE(
        DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))
        ORDER BY
        fi.billing_date DESC
    </select>

    <select id="getInvoiceNumberStr" resultType="java.lang.String">
		select group_concat(number) from finance_invoice_detail where invoice_id = #{invoiceId}
	</select>
</mapper>
