<?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.huyi.service.financeRecord.mapper.FinanceRecordMapper">

    <select id="selectFinanceRecordList" parameterType="map" resultType="map">
        SELECT
            r.zfr_id              'zfrId',
            r.zfr_number          'zfrNumber',
            r.zfr_amount          'zfrAmount',
            r.zfr_rate            'zfrRate',
            r.zfr_handler         'zfrHandler',
            DATE_FORMAT(r.zfr_repayment_date,'%Y-%m-%d') AS 'zfrRepaymentDate',
            DATE_FORMAT(r.zfr_apply_date ,'%Y-%m-%d') AS 'zfrApplyDate',
            DATE_FORMAT(r.zfr_loan_date,'%Y-%m-%d') AS 'zfrLoanDate',
            r.zfr_approve_stt     'zfrApproveStt',
            r.zfr_status          'zfrStatus',
            i.zfi_id              'zfiId',
            i.zfi_number          'zfiNumner',
            i.zfi_core_id         'zfiCoreId',
            i.zfi_supplier_id     'zfiSupplierId',
            i.zfi_amount          'zfiAmount',
            p.zfpcr_charge_rate   'zfpcrChargeRate',
            s.scy_name            'coreScyName',
            c.scy_name            'supplierScyName',
            IFNULL(CAST(ROUND(i.zfi_amount * (IFNULL(p.zfpcr_charge_rate,0.00)/100),2)AS CHAR),0.00) AS 'rzAmount'
        FROM
        zc_finance_record r
        LEFT JOIN zc_finance_inf i
        ON i.zfi_id = r.zfr_finance_id
        LEFT JOIN zc_finance_pro_com_rel p
        ON i.zfi_core_quota_id =  p.zfpcr_id
        LEFT JOIN sys_company s
        ON i.zfi_core_id = s.scy_id
        LEFT JOIN sys_company c
        ON i.zfi_supplier_id = c.scy_id
        WHERE 1 = 1
        <if test="paramMap.zfrStatus != null  and paramMap.zfrStatus != ''">
            and r.zfr_status = #{paramMap.zfrStatus}
        </if>
<!--        <if test="paramMap.pvfId != null  and paramMap.pvfId != ''">-->
<!--            and i.pvf_id = #{paramMap.pvfId}-->
<!--        </if>-->
<!--        <if test="paramMap.pvfCategory != null  and paramMap.pvfCategory != ''">-->
<!--            and i.pvf_category = #{paramMap.pvfCategory}-->
<!--        </if>-->
<!--        <if test="paramMap.cciAbbreviation != null  and paramMap.cciAbbreviation != ''">-->
<!--            and c.cci_abbreviation like concat('%', #{paramMap.cciAbbreviation}, '%')-->
<!--        </if>-->
<!--        <if test="paramMap.cifNo != null  and paramMap.cifNo != ''">-->
<!--            and a.cif_no like concat('%', #{paramMap.cifNo}, '%')-->
<!--        </if>-->
<!--        <if test="paramMap.cifName != null  and paramMap.cifName != ''">-->
<!--            and a.cif_name like concat('%', #{paramMap.cifName}, '%')-->
<!--        </if>-->
<!--        <if test="paramMap.pifName != null  and paramMap.pifName != ''">-->
<!--            and p.pif_name like concat('%', #{paramMap.pifName}, '%')-->
<!--        </if>-->
<!--        <if test="paramMap.pvfInvoiceTitle != null  and paramMap.pvfInvoiceTitle != ''">-->
<!--            and i.pvf_invoice_title like concat('%', #{paramMap.pvfInvoiceTitle}, '%')-->
<!--        </if>-->
<!--        <if test="paramMap.pvfType != null  and paramMap.pvfType != ''">-->
<!--            and i.pvf_type = #{paramMap.pvfType}-->
<!--        </if>-->
<!--        <if test="paramMap.pvfAuthStt != null  and paramMap.pvfAuthStt != ''">-->
<!--            and i.pvf_auth_stt = #{paramMap.pvfAuthStt}-->
<!--        </if>-->
<!--        <if test="paramMap.startTime != null  and paramMap.startTime != ''">-->
<!--            and i.pvf_date &gt;= #{paramMap.startTime}-->
<!--        </if>-->
<!--        <if test="paramMap.endTime != null  and paramMap.endTime != ''">-->
<!--            and i.pvf_date &lt;=  #{paramMap.endTime}-->
<!--        </if>-->
<!--        <if test="paramMap.startCreate != null  and paramMap.startCreate != ''">-->
<!--            and DATE_FORMAT(i.create_time, '%Y-%m-%d') &gt;= #{paramMap.startCreate}-->
<!--        </if>-->
<!--        <if test="paramMap.endCreate != null  and paramMap.endCreate != ''">-->
<!--            and DATE_FORMAT(i.create_time, '%Y-%m-%d') &lt;=  #{paramMap.endCreate}-->
<!--        </if>-->
        order by r.create_time desc
    </select>
    <select id="selectBillInfList" parameterType="map" resultType="map">
        SELECT
        b.zbi_id            'zbiId',
        b.zbi_number        'zbiNumber',
        b.zbi_name          'zbiName',
        DATE_FORMAT(b.zbi_pay_date, '%Y-%m-%d') AS 'zbiPayDate',
        IFNULL(CAST(ROUND(b.zbi_amount,2)AS CHAR),0.00) as 'zbiAmount',
        s.scy_name          'payer',
        c.scy_name          'payee'
        FROM
        zc_bill_inf b
        LEFT JOIN sys_company s
        ON s.scy_id = b.zbi_payer_id
        LEFT JOIN sys_company c
        ON c.scy_id = b.zbi_payee_id
        where zbi_status = '00'
        <if test="paramMap.billIdList != null  and paramMap.billIdList != ''">
            and b.zbi_id  IN (${paramMap.billIdList})
        </if>
        order by b.create_time desc
    </select>
</mapper>