<?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.sunxung.factoring.mapper.ledger.LedgerManagementMapper">

	<select id="findAccountsReceivableIdsBySupplierId" parameterType="java.lang.Long" resultType="java.lang.Long">
		SELECT DISTINCT
			t7.sop_accounts_receivable_id
		FROM
			c_invoice_basic_info t1,
			c_invoice_goods_info t2,
			c_settlement_basic_info t4,
			c_settlement_goods_info t5,
			c_acceptance_basic_info t6,
			c_financing_basic_info t7,
			c_financing_goods_info t8,
			c_supplier_info t9,
			c_enterprise t10
		WHERE
			t1.id = t2.c_invoice_basic_info_id
		  AND t2.c_settlement_basic_info_id = t4.id
		  AND t4.id = t5.c_settlement_basic_info_id
		  AND t5.c_acceptance_basic_info_id = t6.id
		  AND t7.id = t8.c_financing_basic_info_id
		  AND t8.acceptance_basic_info_id = t6.id
		  AND t6.c_supplier_info_id = t9.id
		  AND t9.c_enterprise_id = t10.id
		  AND t10.id = #{enterpriseId}
		  AND t1.invoice_status = ( SELECT t3.id FROM t_dictionary t3 WHERE t3.CODE = 'invoice_pass' )
		  AND t6.is_settlement = 1
		  AND t6.is_receivables = 1
		  AND EXISTS (
				SELECT 1 FROM c_financing_basic_info t11 WHERE t1.business_number = t11.business_number
														   AND t11.supplier_id = t6.c_supplier_info_id AND t11.financing_status IN ('financing_executing','financing_completed','financing_overdue')
			)
	</select>

    <select id="findAccountsReceivableIdsByEnterpriseId" parameterType="java.lang.Long" resultType="java.lang.Long">
		SELECT DISTINCT
			t7.sop_accounts_receivable_id
		FROM
			c_invoice_basic_info t1,
			c_invoice_goods_info t2,
			c_settlement_basic_info t4,
			c_settlement_goods_info t5,
			c_acceptance_basic_info t6,
			c_financing_basic_info t7,
			c_financing_goods_info t8,
			c_project_core_enterprise t9,
			c_enterprise t10
		WHERE
			t1.id = t2.c_invoice_basic_info_id
		  AND t2.c_settlement_basic_info_id = t4.id
		  AND t4.id = t5.c_settlement_basic_info_id
		  AND t5.c_acceptance_basic_info_id = t6.id
		  AND t7.id = t8.c_financing_basic_info_id
		  AND t8.acceptance_basic_info_id = t6.id
		  AND t6.c_project_core_enterprise_id = t9.id
		  AND t9.c_enterprise_id = t10.id
		  AND t10.id = #{enterpriseId}
		  AND t1.invoice_status = ( SELECT t3.id FROM t_dictionary t3 WHERE t3.CODE = 'invoice_pass' )
		  AND t6.is_settlement = 1
		  AND t6.is_receivables = 1
    </select>

    <select id="findAcceptanceBySupplierId" parameterType="java.lang.Long" resultType="com.sunxung.factoring.entity.ledger.LedgerFundGoodsAssign">
		SELECT DISTINCT
			t4.id,
		    t1.business_number,
		    t4.settlement_number,
		    t7.acceptance_goods_number,
		    t4.settlement_date loan_date,
		    t6.c_supplier_info_id supplier_id,
		    t9.corporate_name supplier_name,
		    t6.acceptance_status,
		    t4.settlement_status,
		    t1.invoice_status
		FROM
			c_invoice_basic_info t1,
			c_invoice_goods_info t2,
			c_settlement_basic_info t4,
			c_settlement_goods_info t5,
			c_acceptance_basic_info t6,
		  	c_acceptance_goods_info t7,
			c_supplier_info t8,
			c_enterprise t9
		WHERE
			t1.id = t2.c_invoice_basic_info_id
		  AND t2.c_settlement_basic_info_id = t4.id
		  AND t4.id = t5.c_settlement_basic_info_id
		  AND t5.c_acceptance_goods_info_id = t7.id
		  AND t6.id = t7.c_acceptance_basic_info_id
		  AND t8.id = t6.c_supplier_info_id
		  AND t9.id = t8.c_enterprise_id
		  AND t9.id = #{enterpriseId}
		  AND t1.invoice_status = ( SELECT t3.id FROM t_dictionary t3 WHERE t3.CODE = 'invoice_pass' )
		  AND t6.is_settlement = 1
		  AND t6.is_receivables = 0
		UNION ALL
		SELECT DISTINCT
			t4.id,
			t4.settlement_number,
			t7.acceptance_goods_number,
			t4.settlement_date loan_date,
			t6.c_supplier_info_id supplier_id,
			t9.corporate_name supplier_name,
			t6.acceptance_status,
			t4.settlement_status,
			t1.invoice_status
		FROM
			c_invoice_basic_info t1,
			c_invoice_goods_info t2,
			c_settlement_basic_info t4,
			c_settlement_goods_info t5,
			c_acceptance_basic_info t6,
			c_acceptance_goods_info t7,
			c_supplier_info t8,
			c_enterprise t9
		WHERE
			t1.id = t2.c_invoice_basic_info_id
		  AND t2.c_settlement_basic_info_id = t4.id
		  AND t4.id = t5.c_settlement_basic_info_id
		  AND t5.c_acceptance_goods_info_id = t7.id
		  AND t6.id = t7.c_acceptance_basic_info_id
		  AND t8.id = t6.c_supplier_info_id
		  AND t9.id = t8.c_enterprise_id
		  AND t9.id = #{enterpriseId}
		  AND t1.invoice_status = ( SELECT t3.id FROM t_dictionary t3 WHERE t3.CODE = 'invoice_pass' )
		  AND t6.is_settlement = 1
		  AND t6.is_receivables = 1
		  AND NOT EXISTS (
		      SELECT 1 FROM c_financing_basic_info t10 WHERE t1.business_number = t10.business_number
		        AND t10.supplier_id = t6.c_supplier_info_id AND t10.financing_status IN ('financing_executing','financing_completed','financing_overdue')
			)
	</select>

	<select id="findAcceptanceByEnterpriseId" parameterType="java.lang.Long" resultType="com.sunxung.factoring.entity.ledger.LedgerFundGoodsAssign">
		SELECT DISTINCT
			t4.id,
			t6.business_number,
			t4.settlement_number,
			t7.acceptance_goods_number acceptance_number,
			t4.settlement_date loan_date,
			t6.c_supplier_info_id supplier_id,
			t9.corporate_name supplier_name,
			t6.acceptance_status,
			t4.settlement_status,
			t1.invoice_status
		FROM
			c_invoice_basic_info t1,
			c_invoice_goods_info t2,
			c_settlement_basic_info t4,
			c_settlement_goods_info t5,
			c_acceptance_basic_info t6,
			c_acceptance_goods_info t7,
			c_project_core_enterprise t8,
			c_enterprise t9
		WHERE
			t1.id = t2.c_invoice_basic_info_id
		  AND t2.c_settlement_basic_info_id = t4.id
		  AND t4.id = t5.c_settlement_basic_info_id
		  AND t5.c_acceptance_goods_info_id = t7.id
		  AND t6.id = t7.c_acceptance_basic_info_id
		  AND t8.id = t6.c_project_core_enterprise_id
		  AND t9.id = t8.c_enterprise_id
		  AND t9.id = #{enterpriseId}
		  AND t1.invoice_status = ( SELECT t3.id FROM t_dictionary t3 WHERE t3.CODE = 'invoice_pass' )
		  AND t6.is_settlement = 1
		  AND t6.is_receivables = 0
		UNION ALL
		SELECT DISTINCT
			t4.id,
			t6.business_number,
			t4.settlement_number,
			t7.acceptance_goods_number acceptance_number,
			t4.settlement_date loan_date,
			t6.c_supplier_info_id supplier_id,
			t9.corporate_name supplier_name,
			t6.acceptance_status,
			t4.settlement_status,
			t1.invoice_status
		FROM
			c_invoice_basic_info t1,
			c_invoice_goods_info t2,
			c_settlement_basic_info t4,
			c_settlement_goods_info t5,
			c_acceptance_basic_info t6,
			c_acceptance_goods_info t7,
			c_supplier_info t8,
			c_enterprise t9
		WHERE
			t1.id = t2.c_invoice_basic_info_id
		  AND t2.c_settlement_basic_info_id = t4.id
		  AND t4.id = t5.c_settlement_basic_info_id
		  AND t5.c_acceptance_goods_info_id = t7.id
		  AND t6.id = t7.c_acceptance_basic_info_id
		  AND t8.id = t6.c_project_core_enterprise_id
		  AND t9.id = t8.c_enterprise_id
		  AND t9.id = #{enterpriseId}
		  AND t1.invoice_status = ( SELECT t3.id FROM t_dictionary t3 WHERE t3.CODE = 'invoice_pass' )
		  AND t6.is_settlement = 1
		  AND t6.is_receivables = 1
		  AND NOT EXISTS (
				SELECT 1 FROM c_financing_basic_info t10 WHERE t1.business_number = t10.business_number
														   AND t10.supplier_id = t6.c_supplier_info_id AND t10.financing_status IN ('financing_executing','financing_completed','financing_overdue')
			)
	</select>
</mapper>
