<?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.trade.service.trade.exportrebate.mapper.TaxRebateExportDetailsMapper">
    <resultMap type="com.trade.service.trade.exportrebate.dto.ExportTaxRebateStatisticDto" id="ExportTaxRebateStatisticMap">
        <result property="salesOrderNo" column="sales_order_no"/>
        <result property="declarationMonthYear" column="declaration_month_year"/>
        <result property="declarationBatch" column="declaration_batch"/>
        <result property="correlationNo" column="correlation_no"/>
        <result property="customsDeclarationNo" column="customs_declaration_no"/>
        <result property="proformaInvoiceNo" column="proforma_invoice_no"/>
        <result property="taxRefundAmount" column="tax_refund_amount"/>
        <result property="exportDate" column="export_date"/>
        <result property="exportQuantity" column="export_quantity"/>
        <result property="status" column="status"/>
    </resultMap>

    <select id="getPagesBySearch"
            parameterType="com.trade.service.trade.exportrebate.dto.search.TaxRebateSearchDto"
            resultMap="ExportTaxRebateStatisticMap">
        SELECT
        d.declaration_month_year,
        <if test="hasDeclarationBatch == true">
            d.declaration_batch,
        </if>
        d.customs_declaration_no,
        d.sales_order_no,
        d.proforma_invoice_no,
        SUM(d.tax_refund_amount) AS tax_refund_amount,
        d.export_date,
        SUM(d.export_quantity) AS export_quantity,
        d.status
        FROM tax_rebate_export_details d
        WHERE d.deleted = 0 AND (d.agency_cert_no IS NULL OR d.agency_cert_no = '')
        <if test="customerUserId!= null ">
            AND d.customer_user_id = #{customerUserId}
        </if>
        <if test="declarationMonthYearStart != null and declarationMonthYearStart != ''">
            AND d.declaration_month_year <![CDATA[ >= ]]> #{declarationMonthYearStart}
        </if>
        <if test="declarationMonthYearEnd != null and declarationMonthYearEnd != ''">
            AND d.declaration_month_year <![CDATA[ <= ]]> #{declarationMonthYearEnd}
        </if>
        <if test="declarationBatch != null and declarationBatch != ''">
            AND d.declaration_batch LIKE CONCAT ('%',#{declarationBatch},'%')
        </if>
        <if test="customsDeclarationNo != null and customsDeclarationNo != ''">
            AND d.customs_declaration_no LIKE CONCAT ('%',#{customsDeclarationNo},'%')
        </if>
        <if test="salesOrderNo != null and salesOrderNo != ''">
            AND d.sales_order_no LIKE CONCAT ('%',#{salesOrderNo},'%')
        </if>
        <if test="proformaInvoiceNo != null and proformaInvoiceNo != ''">
            AND d.proforma_invoice_no LIKE CONCAT ('%',#{proformaInvoiceNo},'%')
        </if>
        <if test="exportDateStart != null">
            AND d.export_date <![CDATA[ >= ]]> #{exportDateStart}
        </if>
        <if test="exportDateEnd != null">
            AND d.export_date <![CDATA[ <= ]]> #{exportDateEnd}
        </if>
        <if test="status != null and status != ''">
            AND d.status = #{status}
        </if>
        GROUP BY d.declaration_month_year,
        <if test="hasDeclarationBatch == true">
            d.declaration_batch,
        </if>
        d.customs_declaration_no,
        d.sales_order_no,
        d.proforma_invoice_no,
        d.export_date,
        d.status
        ORDER BY d.export_date DESC,d.declaration_batch DESC
    </select>

    <resultMap type="com.trade.service.trade.exportrebate.dto.TaxRebateTotalDataDto" id="TaxRebateTotalDataMap">
        <result property="totalCount" column="total_count"/>
        <result property="customsDeclarationCount" column="customs_declaration_count"/>
        <result property="exportTotalAmount" column="export_total_amount"/>
        <result property="purchaseInvoiceCount" column="purchase_invoice_count"/>
        <result property="taxRebateTotalAmount" column="tax_rebate_total_amount"/>
    </resultMap>

    <select id="getTaxRebateTotalDataBySearch"
            parameterType="com.trade.service.trade.exportrebate.dto.search.TaxRebateSummarySearchDto"
            resultMap="TaxRebateTotalDataMap">
        SELECT
        COUNT(DISTINCT d.id) AS total_count,
        COUNT(DISTINCT d.customs_declaration_no) AS customs_declaration_count,
        SUM(COALESCE(d.fob_price_usd,0)) / 10000 AS export_total_amount,
        SUM(COALESCE(p.purchase_invoice_count, 0)) AS purchase_invoice_count,
        SUM(COALESCE(d.tax_refund_amount,0)) / 10000 AS tax_rebate_total_amount
        FROM tax_rebate_export_details d
        LEFT JOIN (
            SELECT rebate_export_details_id, COUNT(DISTINCT purchase_voucher_no) AS purchase_invoice_count
                FROM tax_rebate_purchase_details
                WHERE deleted = 0
                GROUP BY rebate_export_details_id
        ) p ON d.id = p.rebate_export_details_id
        LEFT JOIN trading_platform p ON p.cert_no = d.agency_cert_no
        WHERE d.deleted = 0 AND d.agency_cert_no IS NOT NULL AND d.agency_cert_no != ''
        <if test="declarationMonthYearStart != null">
            AND d.declaration_month_year <![CDATA[ >= ]]> #{declarationMonthYearStart}
        </if>
        <if test="declarationMonthYearEnd != null">
            AND d.declaration_month_year <![CDATA[ <= ]]> #{declarationMonthYearEnd}
        </if>
        <if test="declarationBatch != null and declarationBatch != ''">
            AND d.declaration_batch LIKE CONCAT ('%',#{declarationBatch},'%')
        </if>
        <if test="status != null and status != ''">
            AND d.status = #{status}
        </if>
        <if test="agencyName != null and agencyName != ''">
            AND p.platform_name LIKE CONCAT ('%',#{agencyName},'%')
        </if>
        <if test="agencyCertNo != null and agencyCertNo != ''">
            AND d.agency_cert_no = #{agencyCertNo}
        </if>
    </select>

    <resultMap type="com.trade.service.trade.exportrebate.dto.TaxRebateSummaryDataDto" id="TaxRebateSummaryDataMap">
        <result property="declarationMonthYear" column="declaration_month_year"/>
        <result property="declarationBatch" column="declaration_batch"/>
        <result property="customsDeclarationCount" column="customs_declaration_count"/>
        <result property="exportTotalAmount" column="export_total_amount"/>
        <result property="purchaseInvoiceCount" column="purchase_invoice_count"/>
        <result property="taxRebateTotalAmount" column="tax_rebate_total_amount"/>
        <result property="agencyCertNo" column="agency_cert_no"/>
        <result property="agencyName" column="agency_name"/>
        <result property="status" column="status"/>
        <result property="selectable" column="selectable"/>
    </resultMap>

    <select id="getTaxRebateSummaryDataPagesBySearch"
            parameterType="com.trade.service.trade.exportrebate.dto.search.TaxRebateSummarySearchDto"
            resultMap="TaxRebateSummaryDataMap">
        SELECT
        d.declaration_month_year,
        d.declaration_batch,
        COUNT(DISTINCT d.customs_declaration_no) AS customs_declaration_count,
        SUM(COALESCE(d.fob_price_usd,0)) / 10000 AS export_total_amount,
        SUM(COALESCE(p.purchase_invoice_count, 0)) AS purchase_invoice_count,
        SUM(COALESCE(d.tax_refund_amount,0)) / 10000 AS tax_rebate_total_amount,
        d.agency_cert_no,
        p.platform_name AS agency_name,
        d.status,
        SUM(IF(d.payment_status IS NOT NULL AND d.payment_status = '0', 1, 0)) > 0 AS selectable
        FROM tax_rebate_export_details d
        LEFT JOIN (
            SELECT rebate_export_details_id, COUNT(DISTINCT purchase_voucher_no) AS purchase_invoice_count
            FROM tax_rebate_purchase_details
            WHERE deleted = 0
            GROUP BY rebate_export_details_id
        ) p ON d.id = p.rebate_export_details_id
        LEFT JOIN trading_platform p ON p.cert_no = d.agency_cert_no
        WHERE d.deleted = 0 AND d.agency_cert_no IS NOT NULL AND d.agency_cert_no != ''
        <if test="declarationMonthYearStart != null">
            AND d.declaration_month_year <![CDATA[ >= ]]> #{declarationMonthYearStart}
        </if>
        <if test="declarationMonthYearEnd != null">
            AND d.declaration_month_year <![CDATA[ <= ]]> #{declarationMonthYearEnd}
        </if>
        <if test="declarationBatch != null and declarationBatch != ''">
            AND d.declaration_batch LIKE CONCAT ('%',#{declarationBatch},'%')
        </if>
        <if test="status != null and status != ''">
            AND d.status = #{status}
        </if>
        <if test="agencyName != null and agencyName != ''">
            AND p.platform_name LIKE CONCAT ('%',#{agencyName},'%')
        </if>
        <if test="agencyCertNo != null and agencyCertNo != ''">
            AND d.agency_cert_no = #{agencyCertNo}
        </if>
        GROUP BY d.declaration_month_year, d.declaration_batch, d.status, p.platform_name, d.agency_cert_no
        ORDER BY d.declaration_month_year DESC, d.declaration_batch DESC
    </select>

    <resultMap type="com.trade.service.trade.exportrebate.dto.TaxRebateExportAbstractInfo"
               id="TaxRebateExportAbstractInfoMap">
        <result property="id" column="id"/>
        <result property="customerUserId" column="customer_user_id"/>
        <result property="agencyCertNo" column="agency_cert_no"/>
        <result property="agencyName" column="agency_name"/>
        <result property="importerCertNo" column="importer_cert_no"/>
        <result property="importerName" column="importer_name"/>
        <result property="salesOrderNo" column="sales_order_no"/>
        <result property="salesContractId" column="sales_contract_id"/>
        <result property="salesContractNo" column="sales_contract_no"/>
        <result property="declarationMonthYear" column="declaration_month_year"/>
        <result property="declarationBatch" column="declaration_batch"/>
        <result property="declarationSerialNo" column="declaration_serial_no"/>
        <result property="correlationNo" column="correlation_no"/>
        <result property="customsDeclarationNo" column="customs_declaration_no"/>
        <result property="proformaInvoiceNo" column="proforma_invoice_no"/>
        <result property="taxRefundAmount" column="tax_refund_amount"/>
        <result property="exportDate" column="export_date"/>
        <result property="hsCode" column="hs_code"/>
        <result property="exportCommodityCode" column="export_commodity_code"/>
        <result property="exportCommodityName" column="export_commodity_name"/>
        <result property="measurementUnit" column="measurement_unit"/>
        <result property="exportQuantity" column="export_quantity"/>
        <result property="fobPriceUsd" column="fob_price_usd"/>
        <result property="exportAmount" column="export_amount"/>
        <result property="status" column="status"/>
        <result property="paymentStatus" column="payment_status"/>
        <result property="paymentDate" column="payment_date"/>
    </resultMap>

    <select id="getTaxRebateExportAbstractInfoPagesBySearch"
            parameterType="com.trade.service.trade.exportrebate.dto.search.TaxRebateExportAbstractInfoSearchDto"
            resultMap="TaxRebateExportAbstractInfoMap">
        SELECT
        d.id,
        d.customer_user_id,
        d.agency_cert_no,
        p.platform_name AS agency_name,
        d.importer_cert_no,
        i.entity_name AS importer_name,
        d.sales_order_no,
        d.sales_contract_id,
        d.sales_contract_no,
        d.declaration_month_year,
        d.declaration_batch,
        d.declaration_serial_no,
        d.correlation_no,
        d.customs_declaration_no,
        d.proforma_invoice_no,
        d.tax_refund_amount,
        d.export_date,
        d.hs_code,
        d.export_commodity_code,
        d.export_commodity_name,
        d.measurement_unit,
        d.export_quantity,
        d.fob_price_usd,
        d.export_quantity,
        ifnull(d.fob_price_usd,0) AS export_amount,
        d.status,
        d.payment_status,
        d.payment_date
        FROM tax_rebate_export_details d
        LEFT JOIN trading_platform p ON p.cert_no = d.agency_cert_no
        LEFT JOIN importer_entity i ON (i.cert_no = d.importer_cert_no and i.customer_user_id = d.customer_user_id)
        WHERE d.deleted = 0 AND d.agency_cert_no = #{agencyCertNo}
            AND d.declaration_month_year = #{declarationMonthYear}
            AND d.declaration_batch =#{declarationBatch}
            AND d.status = #{status}
        <if test="salesOrderNo != null and salesOrderNo != ''">
            AND d.sales_order_no LIKE CONCAT ('%',#{salesOrderNo},'%')
        </if>
        <if test="customsDeclarationNo != null and customsDeclarationNo != ''">
            AND d.customs_declaration_no LIKE CONCAT ('%',#{customsDeclarationNo},'%')
        </if>
        <if test="proformaInvoiceNo != null and proformaInvoiceNo != ''">
            AND d.proforma_invoice_no LIKE CONCAT ('%',#{proformaInvoiceNo},'%')
        </if>
        <if test="agencyName != null and agencyName != ''">
            AND p.platform_name LIKE CONCAT ('%',#{agencyName},'%')
        </if>
        <if test="importerName != null and importerName != ''">
            AND i.entity_name LIKE CONCAT ('%',#{importerName},'%')
        </if>
        <if test="paymentStatus != null and paymentStatus != ''">
            AND d.payment_status = #{paymentStatus}
        </if>
    </select>


    <resultMap type="com.trade.service.trade.exportrebate.domain.TaxRebateExportDetails"
               id="TaxRebateExportDetailsMap">
        <result property="id" column="id"/>
        <result property="customerUserId" column="customer_user_id"/>
        <result property="agencyCertNo" column="agency_cert_no"/>
        <result property="importerCertNo" column="importer_cert_no"/>
        <result property="salesOrderNo" column="sales_order_no"/>
        <result property="salesContractId" column="sales_contract_id"/>
        <result property="salesContractNo" column="sales_contract_co"/>
        <result property="declarationMonthYear" column="declaration_month_year"/>
        <result property="declarationBatch" column="declaration_batch"/>
        <result property="declarationSerialNo" column="declaration_serial_no"/>
        <result property="correlationNo" column="correlation_no"/>
        <result property="customsDeclarationNo" column="customs_declaration_no"/>
        <result property="proformaInvoiceNo" column="proforma_invoice_no"/>
        <result property="taxRefundAmount" column="tax_refund_amount"/>
        <result property="exportDate" column="export_date"/>
        <result property="hsCode" column="hs_code"/>
        <result property="exportCommodityCode" column="export_commodity_code"/>
        <result property="exportCommodityName" column="export_commodity_name"/>
        <result property="measurementUnit" column="measurement_unit"/>
        <result property="exportQuantity" column="export_quantity"/>
        <result property="fobPriceUsd" column="fob_price_usd"/>
        <result property="status" column="status"/>
        <result property="paymentStatus" column="payment_status"/>
        <result property="paymentDate" column="payment_date"/>
        <result property="gmtCreate" column="gmt_create"/>
        <result property="gmtModified" column="gmt_modified"/>
    </resultMap>

    <select id="getPayableTaxRebateExportDetailsForSelfExportUser"
            resultType="com.trade.service.trade.exportrebate.domain.TaxRebateExportDetails"
            resultMap="TaxRebateExportDetailsMap">
        SELECT
            d.id,
            d.customer_user_id,
            d.agency_cert_no,
            d.importer_cert_no,
            d.sales_order_no,
            d.sales_contract_id,
            d.sales_contract_no,
            d.declaration_month_year,
            d.declaration_batch,
            d.declaration_serial_no,
            d.correlation_no,
            d.customs_declaration_no,
            d.proforma_invoice_no,
            d.tax_refund_amount,
            d.export_date,
            d.hs_code,
            d.export_commodity_code,
            d.export_commodity_name,
            d.measurement_unit,
            d.export_quantity,
            d.fob_price_usd,
            d.export_quantity,
            d.status,
            d.payment_status,
            d.payment_date,
            d.gmt_create,
            d.gmt_modified
        FROM tax_rebate_export_details d
        WHERE d.deleted = 0
          AND d.status = '0'
          AND d.payment_status = '0'
          AND (d.agency_cert_no = '' OR d.agency_cert_no IS NULL)
    </select>

    <select id="getDetailsByCSearch"
            parameterType="com.trade.service.trade.exportrebate.dto.search.ExportTaxRebateDetailsForCSearchDto"
            resultMap="TaxRebateExportDetailsMap">
        SELECT
            d.id,
            d.customer_user_id,
            d.importer_cert_no,
            d.sales_order_no,
            d.sales_contract_id,
            d.sales_contract_no,
            d.declaration_month_year,
            d.declaration_batch,
            d.declaration_serial_no,
            d.correlation_no,
            d.customs_declaration_no,
            d.proforma_invoice_no,
            d.tax_refund_amount,
            d.export_date,
            d.hs_code,
            d.export_commodity_code,
            d.export_commodity_name,
            d.measurement_unit,
            d.export_quantity,
            d.fob_price_usd,
            d.export_quantity,
            d.status,
            d.payment_status,
            d.payment_date,
            d.gmt_create,
            d.gmt_modified
        FROM tax_rebate_export_details d
        WHERE d.deleted = 0
          AND (d.agency_cert_no = '' OR d.agency_cert_no IS NULL)
          AND d.customer_user_id = #{customerUserId}
          AND d.declaration_month_year = #{declarationMonthYear}
          AND d.declaration_batch = #{declarationBatch}
          AND d.customs_declaration_no = #{customsDeclarationNo}
          AND d.sales_order_no = #{salesOrderNo}
          AND d.proforma_invoice_no = #{proformaInvoiceNo}
          AND d.export_date = #{exportDate}
          AND d.status = #{status}
    </select>
</mapper>

