<?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="org.jeecg.modules.finance.mapper.FdFinancialStatementsMapper">
<!--到款-->
    <select id="queryByConId1" resultType="org.jeecg.modules.finance.entity.FdFinancialStatements">
        SELECT
        prb.id as id,
        prb.payment_date as dateOfArrivalp,
        prb.milepost as paymentTerms,
        prb.contract_amount as amount,
        pr.contractno as contractCode,
        pr.applicantname as bidPartyName,
        pr.contractpartyname as contractPartyName,
        pr.currency_type AS currency,
        pr.bd_person_id as bdId,
        pr.bd_person as bdName,
        pr.compound as compoundName,
        prb.type_name as typeName,
        pr.create_time AS createTime
        FROM
        fd_project_receivables_b prb
        LEFT JOIN fd_project_receivables pr ON pr.id = prb.pk_head_id
        WHERE
        pr.del_flag='0' AND
        prb.payment_date IS not NULL
        and pr.id = prb.pk_head_id
        <if test="fdFinancialStatements.createTime!=null and fdFinancialStatements.updateTime!=null">
            and prb.payment_date between #{fdFinancialStatements.createTime} and #{fdFinancialStatements.updateTime}
        </if>
        <if test="fdFinancialStatements.currencyMoney!=null and fdFinancialStatements.currencyMoney!=''">
            and pr.currency_type like CONCAT('%',#{fdFinancialStatements.currencyMoney},'%')
        </if>
        <if test="fdFinancialStatements.pkBidParty!=null and fdFinancialStatements.pkBidParty!=''">
            and pr.pk_applicant = #{fdFinancialStatements.pkBidParty}
        </if>
        <if test="fdFinancialStatements.pkContractParty!=null and fdFinancialStatements.pkContractParty!=''">
            and pr.pk_contractparty = #{fdFinancialStatements.pkContractParty}
        </if>
        <if test="fdFinancialStatements.bdId!=null and fdFinancialStatements.bdId!=''">
            and pr.bd_person_id = #{fdFinancialStatements.bdId}
        </if>
        <if test="fdFinancialStatements.currencyTypeId!=null and fdFinancialStatements.currencyTypeId!=''">
            and pr.currency_type_id = #{fdFinancialStatements.currencyTypeId}
        </if>
        <if test="fdFinancialStatements.bidPartyName!=null and fdFinancialStatements.bidPartyName!=''">
            and pr.applicantname like CONCAT('%',#{fdFinancialStatements.bidPartyName},'%')
        </if>

        union

        SELECT
        scrb.id as id,
        scrb.payment_notice_date AS paymentNoticeDate,
        scrb.pk_milestone AS paymentTerms,
        scrb.contract_money AS amount,
        scr.contract_code AS contractCode,
        scr.bidparty_name AS bidPartyName,
        scr.contract_party_name AS contractPartyName,
        scr.currency_name AS currency,
        scr.BD_person_in_charge AS bdId,
        scr.BD_person_in_charge_name AS bdName,
        scr.compound AS compoundName,
        scrb.type_name AS typeName,
        scr.create_time AS createTime
        FROM
        fd_supplementary_contract_receivables_b scrb
        LEFT JOIN fd_supplementary_contract_receivables scr ON scr.id = scrb.pk_supplementary_contract_receivables
        WHERE
        scr.del_flag='0' AND
        scrb.payment_date IS not NULL
        and scr.id = scrb.pk_supplementary_contract_receivables
        <if test="fdFinancialStatements.createTime!=null and fdFinancialStatements.updateTime!=null">
            and scrb.payment_date between #{fdFinancialStatements.createTime} and #{fdFinancialStatements.updateTime}
        </if>
        <if test="fdFinancialStatements.currencyMoney!=null and fdFinancialStatements.currencyMoney!=''">
            and scr.currency_name like CONCAT('%',#{fdFinancialStatements.currencyMoney},'%')
        </if>
        <if test="fdFinancialStatements.pkBidParty!=null and fdFinancialStatements.pkBidParty!=''">
            and scr.pk_bidparty = #{fdFinancialStatements.pkBidParty}
        </if>
        <if test="fdFinancialStatements.pkContractParty!=null and fdFinancialStatements.pkContractParty!=''">
            and scr.pk_contract_party = #{fdFinancialStatements.pkContractParty}
        </if>
        <if test="fdFinancialStatements.bdId!=null and fdFinancialStatements.bdId!=''">
            and scr.BD_person_in_charge = #{fdFinancialStatements.bdId}
        </if>
        <if test="fdFinancialStatements.currencyTypeId!=null and fdFinancialStatements.currencyTypeId!=''">
            and scr.currency = #{fdFinancialStatements.currencyTypeId}
        </if>
        <if test="fdFinancialStatements.bidPartyName!=null and fdFinancialStatements.bidPartyName!=''">
            and scr.bidparty_name like CONCAT('%',#{fdFinancialStatements.bidPartyName},'%')
        </if>

        ORDER BY createTime DESC
    </select>

    <!--未到款-->
    <select id="queryByConId2" resultType="org.jeecg.modules.finance.entity.FdFinancialStatements">
        SELECT
        prb.id as id,
        prb.notice_date as paymentNoticeDate,
        prb.milepost as paymentTerms,
        prb.contract_amount as amount,
        pr.contractno as contractCode,
        pr.applicantname as bidPartyName,
        pr.contractpartyname as contractPartyName,
        pr.currency_type AS currency,
        pr.bd_person_id as bdId,
        pr.bd_person as bdName,
        pr.compound as compoundName,
        prb.type_name as typeName,
        c.credit_period as creditPeriod,
        pr.create_time as createTime
        FROM
        fd_project_receivables_b prb
        LEFT JOIN fd_project_receivables pr ON pr.id = prb.pk_head_id
        left join fd_customer c on c.id = pr.pk_applicant
        WHERE
        pr.del_flag='0' AND
        prb.payment_date IS NULL
        and prb.notice_date IS NOT NULL
        and pr.id = prb.pk_head_id
        <if test="fdFinancialStatements.createTime!=null and fdFinancialStatements.updateTime!=null">
            and prb.notice_date between #{fdFinancialStatements.createTime} and #{fdFinancialStatements.updateTime}
        </if>
        <if test="fdFinancialStatements.currencyMoney!=null and fdFinancialStatements.currencyMoney!=''">
            and pr.currency_type like CONCAT('%',#{fdFinancialStatements.currencyMoney},'%')
        </if>
        <if test="fdFinancialStatements.pkBidParty!=null and fdFinancialStatements.pkBidParty!=''">
            and pr.pk_applicant = #{fdFinancialStatements.pkBidParty}
        </if>
        <if test="fdFinancialStatements.pkContractParty!=null and fdFinancialStatements.pkContractParty!=''">
            and pr.pk_contractparty = #{fdFinancialStatements.pkContractParty}
        </if>
        <if test="fdFinancialStatements.bdId!=null and fdFinancialStatements.bdId!=''">
            and pr.bd_person_id = #{fdFinancialStatements.bdId}
        </if>
        <if test="fdFinancialStatements.currencyTypeId!=null and fdFinancialStatements.currencyTypeId!=''">
            and pr.currency_type_id = #{fdFinancialStatements.currencyTypeId}
        </if>
        <if test="fdFinancialStatements.bidPartyName!=null and fdFinancialStatements.bidPartyName!=''">
            and pr.applicantname like CONCAT('%',#{fdFinancialStatements.bidPartyName},'%')
        </if>

        UNION

        SELECT
        scrb.id as id,
        scrb.payment_notice_date as paymentNoticeDate,
        scrb.pk_milestone as paymentTerms,
        scrb.contract_money as amount,
        scr.contract_code as contractCode,
        scr.bidparty_name as bidPartyName,
        scr.contract_party_name as contractPartyName,
        scr.currency_name AS currency,
        scr.BD_person_in_charge as bdId,
        scr.BD_person_in_charge_name as bdName,
        scr.compound as compoundName,
        scrb.type_name as typeName,
        c.credit_period as creditPeriod,
        scr.create_time as createTime
        FROM
        fd_supplementary_contract_receivables_b scrb
        LEFT JOIN fd_supplementary_contract_receivables scr ON scr.id = scrb.pk_supplementary_contract_receivables
        left join fd_customer c on c.id = scr.pk_bidparty
        WHERE
        scr.del_flag='0' AND
        scrb.payment_date IS NULL
        AND scrb.payment_notice_date IS NOT NULL
        and scr.id = scrb.pk_supplementary_contract_receivables
        <if test="fdFinancialStatements.createTime!=null and fdFinancialStatements.updateTime!=null">
            and scrb.payment_notice_date between #{fdFinancialStatements.createTime} and #{fdFinancialStatements.updateTime}
        </if>
        <if test="fdFinancialStatements.currencyMoney!=null and fdFinancialStatements.currencyMoney!=''">
            and scr.currency_name like CONCAT('%',#{fdFinancialStatements.currencyMoney},'%')
        </if>
        <if test="fdFinancialStatements.pkBidParty!=null and fdFinancialStatements.pkBidParty!=''">
            and scr.pk_bidparty = #{fdFinancialStatements.pkBidParty}
        </if>
        <if test="fdFinancialStatements.pkContractParty!=null and fdFinancialStatements.pkContractParty!=''">
            and scr.pk_contractparty = #{fdFinancialStatements.pkContractParty}
        </if>
        <if test="fdFinancialStatements.bdId!=null and fdFinancialStatements.bdId!=''">
            and scr.BD_person_in_charge = #{fdFinancialStatements.bdId}
        </if>
        <if test="fdFinancialStatements.currencyTypeId!=null and fdFinancialStatements.currencyTypeId!=''">
            and scr.currency = #{fdFinancialStatements.currencyTypeId}
        </if>
        <if test="fdFinancialStatements.bidPartyName!=null and fdFinancialStatements.bidPartyName!=''">
            and scr.bidparty_name like CONCAT('%',#{fdFinancialStatements.bidPartyName},'%')
        </if>

        ORDER BY createTime DESC

    </select>


    <!--SELECT-->
    <!--pf.id,-->
    <!--pf.contract_party AS pkContractParty,-->
    <!--con.id AS pkContract,-->
    <!--con.CODE AS contractCode,-->
    <!--cus.id AS pkBidParty,-->
    <!--cus.`name` AS bidPartyName,-->
    <!--prb.contract_amount AS amount,-->
    <!--pr.currency_type AS currency,-->
    <!--cc.description AS paymentTerms,-->
    <!--prb.payment_date AS dateOfArrivalp,-->
    <!--prb.notice_date AS paymentNoticeDate,-->
    <!--pr.compound AS compoundName,-->
    <!--pf.bd AS bdId,-->
    <!--fs.pk_report_name,-->
    <!--fs.report_name-->
    <!--FROM-->
    <!--fd_project_file pf-->
    <!--LEFT JOIN fd_contract con ON pf.contract_id = con.id-->
    <!--LEFT JOIN fd_project_receivables pr ON pr.project_name = pf.project_name-->
    <!--LEFT JOIN fd_customer cus ON cus.id = pf.customer_id-->
    <!--LEFT JOIN fd_project_receivables_b prb ON pr.id = prb.pk_head_id-->
    <!--LEFT JOIN fd_contract_clause cc ON cc.contract_id = con.id-->
    <!--LEFT JOIN fd_financial_statements fs on fs.pk_contract = con.id-->
    <!--WHERE-->
    <!--con.del_flag = '0'-->
    <!--and prb.payment_date is not Null-->
    <!--<if test="createTime!=null and updateTime!=null">-->
        <!--and pr.create_time between #{createTime} and #{updateTime}-->
    <!--</if>-->


    <!--SELECT-->
    <!--pf.id,-->
    <!--pf.contract_party AS pkContractParty,-->
    <!--con.id AS pkContract,-->
    <!--con.CODE AS contractCode,-->
    <!--cus.id AS pkBidParty,-->
    <!--cus.`name` AS bidPartyName,-->
    <!--prb.contract_amount AS amount,-->
    <!--pr.currency_type AS currency,-->
    <!--cc.description AS paymentTerms,-->
    <!--prb.payment_date AS dateOfArrivalp,-->
    <!--prb.notice_date AS paymentNoticeDate,-->
    <!--pr.compound AS compoundName,-->
    <!--pf.bd AS bdId,-->
    <!--fs.pk_report_name,-->
    <!--fs.report_name-->
    <!--FROM-->
    <!--fd_project_file pf-->
    <!--LEFT JOIN fd_contract con ON pf.contract_id = con.id-->
    <!--LEFT JOIN fd_project_receivables pr ON pr.project_name = pf.project_name-->
    <!--LEFT JOIN fd_customer cus ON cus.id = pf.customer_id-->
    <!--LEFT JOIN fd_project_receivables_b prb ON pr.id = prb.pk_head_id-->
    <!--LEFT JOIN fd_contract_clause cc ON cc.contract_id = con.id-->
    <!--LEFT JOIN fd_financial_statements fs on fs.pk_contract = con.id-->
    <!--WHERE-->
    <!--con.del_flag = '0'-->
    <!--and prb.payment_date is Null-->
    <!--<if test="createTime!=null and updateTime!=null">-->
        <!--and pr.create_time between #{createTime} and #{updateTime}-->
    <!--</if>-->
</mapper>