<?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.filingdoc.mapper.FilingDocWriteTaskMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.trade.service.trade.filingdoc.domain.FilingDocWriteTask">
        <result column="id" property="id" />
        <result column="cert_no" property="certNo" />
        <result column="biz_no" property="bizNo" />
        <result column="part" property="part" />
        <result column="status" property="status" />
        <result column="agent_ind" property="agentInd" />
        <result column="remark" property="remark" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id,
        cert_no, biz_no, part, status, agent_ind, remark
    </sql>




    <select id="queryList4FilingDocBySearch" resultType="com.trade.service.trade.filingdoc.vo.FilingDocVO">
        SELECT
        d.declaration_month_year, d.declaration_batch,
        COUNT(DISTINCT d.customs_declaration_no) AS customs_declaration_count,
        SUM(COALESCE(d.export_quantity,0) * COALESCE(d.fob_price_usd,0)) / 10000 AS export_amount,
        SUM(COALESCE(p.purchase_invoice_count, 0)) AS purchase_invoice_count,
        SUM(COALESCE(d.tax_refund_amount,0)) / 10000 AS tax_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
        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>
        group by d.declaration_month_year, d.declaration_batch
        order by d.declaration_month_year desc, d.declaration_batch desc
    </select>

</mapper>
