<?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.salesorder.mapper.CustomsDeclarationMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.trade.service.trade.salesorder.domain.CustomsDeclaration">
        <result column="id" property="id" />
        <result column="deleted" property="deleted" />
        <result column="sale_order_no" property="saleOrderNo" />
        <result column="related_entry_ind" property="relatedEntryInd" />
        <result column="bgsbzt_dm" property="bgsbztDm" />
        <result column="bgdhgbh" property="bgdhgbh" />
        <result column="hgqy_dm" property="hgqyDm" />
        <result column="ckrq1" property="ckrq1" />
        <result column="sbhggqka_dm" property="sbhggqkaDm" />
        <result column="sbhggqkajc" property="sbhggqkajc" />
        <result column="yfje" property="yfje" />
        <result column="yfhghbzm_dm" property="yfhghbzmDm" />
        <result column="bfje" property="bfje" />
        <result column="bfhghbzm_dm" property="bfhghbzmDm" />
        <result column="zfje1" property="zfje1" />
        <result column="zfhghbzm_dm" property="zfhghbzmDm" />
        <result column="js1" property="js1" />
        <result column="mz2" property="mz2" />
        <result column="jz" property="jz" />
        <result column="hgckhwbgdsbrq" property="hgckhwbgdsbrq" />
        <result column="sbdwmc" property="sbdwmc" />
        <result column="jzxh" property="jzxh" />
        <result column="gmt_create" property="gmtCreate" />
        <result column="gmt_modified" property="gmtModified" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id,
        deleted,
        sale_order_no, related_entry_ind, bgsbzt_dm, bgdhgbh, hgqy_dm, ckrq1, sbhggqka_dm, sbhggqkajc, yfje, yfhghbzm_dm, bfje, bfhghbzm_dm, zfje1, zfhghbzm_dm, js1, mz2, jz, hgckhwbgdsbrq, sbdwmc, jzxh, gmt_create, gmt_modified
    </sql>

    <select id="queryOrderNo4UpdateCustomsDeclaration"
            resultType="com.trade.service.trade.salesorder.vo.SalesOrder4CustomsDeclarationVO">
        SELECT
            a.sale_order_no query_no,
            IF
                ( c.agency_cert_no IS NOT NULL AND c.agency_cert_no != '', d.xt_user_id, e.xt_uid ) AS xt_user_id,
            IF
                ( c.agency_cert_no IS NOT NULL AND c.agency_cert_no != '', d.xt_eid, f.xt_eid ) AS xt_eid
        FROM
            customs_declaration a
                LEFT JOIN sales_order b ON a.sale_order_no = b.order_no
                LEFT JOIN sales_contract c ON b.sale_contract_id = c.id
                LEFT JOIN trading_platform d ON c.agency_cert_no = d.cert_no
                LEFT JOIN customer_user e ON b.customer_user_id = e.id
                LEFT JOIN enterprise f ON b.customer_user_id = f.customer_id
        WHERE
            a.bgsbzt_dm != '10'
	        AND a.deleted = 0
    </select>


    <select id="queryListBySearch" resultType="com.trade.service.trade.salesorder.vo.CustomsDeclarationVO">
        select
        a.id,
        a.bgdhgbh,
        a.sale_order_no order_no,
        a.bgsbzt_dm status,
        a.hgckhwbgdsbrq,
        a.ckrq1,
        d.platform_name agency_enterprise_name,
        e.entity_name importer_name,
        c.trade_terms,
        f.invoice_no,
        g.lading_bill_no
        From customs_declaration a
        left join sales_order b on a.sale_order_no = b.order_no
        left join sales_contract c on b.sale_contract_id = c.id
        left join trading_platform d on c.agency_cert_no = d.cert_no
        left join importer_entity e on b.importer_id = e.id
        left join sales_order_invoice f on b.id = f.order_id
        left join sales_order_logistics g on b.id = g.order_id
        WHERE a.deleted = 0
        <if test="orderNo != null and orderNo != ''">
            <!-- good design!! -->
            AND (a.sale_order_no = #{orderNo} or a.bgdhgbh = #{orderNo} or f.invoice_no = #{orderNo} or g.lading_bill_no= #{orderNo})
        </if>
        <if test="customerUserId != null">
            AND b.customer_user_id = #{customerUserId}
        </if>
        <if test="agencyCertNo != null and agencyCertNo != ''">
            AND c.agency_cert_no = #{agencyCertNo}
        </if>
        <if test="importerId != null">
            AND b.importer_id = #{importerId}
        </if>
        <if test="tradeTerms != null and tradeTerms != ''">
            AND c.trade_terms = #{tradeTerms}
        </if>
        <if test="status != null and status != ''">
            AND a.bgsbzt_dm = #{status}
        </if>
        <if test="declareTimeStart != null">
            AND a.hgckhwbgdsbrq &gt;= #{declareTimeStart}
        </if>
        <if test="declareTimeEnd != null">
            AND a.hgckhwbgdsbrq &lt; DATE_FORMAT(DATE_ADD(#{declareTimeEnd},INTERVAL 1 DAY),'%Y-%m-%d')
        </if>
        <if test="exportTimeStart != null">
            AND a.ckrq1 &gt;= #{exportTimeStart}
        </if>
        <if test="exportTimeEnd != null">
            AND a.ckrq1 &lt; DATE_FORMAT(DATE_ADD(#{exportTimeEnd},INTERVAL 1 DAY),'%Y-%m-%d')
        </if>
    </select>


    <select id="getDetailById" resultType="com.trade.service.trade.salesorder.vo.CustomsDeclarationDtlVO">
        select
            a.bgdhgbh customs_no,
            a.sbhggqka_dm,
            a.sbhggqkajc,
            a.yfje,
            a.yfhghbzm_dm,
            a.bfje,
            a.bfhghbzm_dm,
            a.zfje1,
            a.zfhghbzm_dm,
            a.js1,
            a.mz2,
            a.jz,
            a.sbdwmc,
            a.jzxh,
            a.hgqy_dm,
            a.bgdhgbh,
            a.sale_order_no order_no,
            a.bgsbzt_dm,
            a.hgckhwbgdsbrq,
            a.ckrq1,
            b.id as order_id,
            d.platform_name agency_enterprise_name,
            e.entity_name importer_name,
            c.contract_no,
            c.trade_terms,
            c.exchange_rate,
            f.invoice_no,
            g.lading_bill_no,
            g.from_port,
            g.dest_port,
            h.entrust_file_id,
            h.entrust_no,
            h.customs_file_id
        From customs_declaration a
                 left join sales_order b on a.sale_order_no = b.order_no
                 left join sales_contract c on b.sale_contract_id = c.id
                 left join trading_platform d on c.agency_cert_no = d.cert_no
                 left join importer_entity e on b.importer_id = e.id
                 left join sales_order_invoice f on b.id = f.order_id
                 left join sales_order_logistics g on b.id = g.order_id
                 left join customs_declaration_file h on a.bgdhgbh = h.customs_no
        WHERE a.id = #{id} AND a.deleted = 0
    </select>

    <select id="queryData4DownloadCustomsDeclarationFile"
            resultType="com.trade.service.trade.filingdoc.vo.SalesOrder4CustomsFileQueryVO">
        SELECT
            a.bgdhgbh query_no,
            CASE
                WHEN g.customs_file_id IS NULL
                    AND g.entrust_file_id IS NULL THEN
                    '10'
                WHEN g.customs_file_id IS NULL THEN
                    '01' ELSE '02'
                END AS type,
            IF
                ( c.agency_cert_no IS NOT NULL AND c.agency_cert_no != '', d.xt_user_id, e.xt_uid ) AS xt_user_id,
            IF
                ( c.agency_cert_no IS NOT NULL AND c.agency_cert_no != '', d.xt_eid, f.xt_eid ) AS xt_eid
        FROM
            customs_declaration a
                LEFT JOIN sales_order b ON a.sale_order_no = b.order_no
                LEFT JOIN sales_contract c ON b.sale_contract_id = c.id
                LEFT JOIN trading_platform d ON c.agency_cert_no = d.cert_no
                LEFT JOIN customer_user e ON b.customer_user_id = e.id
                LEFT JOIN enterprise f ON b.customer_user_id = f.customer_id
                LEFT JOIN customs_declaration_file g ON a.bgdhgbh = g.customs_no
        WHERE
            (g.customs_file_id IS NULL
           OR g.entrust_file_id IS NULL)
            AND a.deleted = 0
    </select>

    <select id="queryList4FilingDocBySearch"
            resultType="com.trade.service.trade.filingdoc.vo.CustomsDeclaration4FilingDocVO">
        select
            a.bgdhgbh customs_no,
            a.sbdwmc declare_company_name,
            a.sale_order_no order_no,
            a.hgckhwbgdsbrq,
            a.ckrq1 out_date,
            b.amount out_amount,
            c.trade_terms,
            d.customs_file_id,
            d.entrust_no,
            d.entrust_file_id
        From customs_declaration a
                 left join sales_order b on a.sale_order_no = b.order_no
                 left join sales_contract c on b.sale_contract_id = c.id
                 left join customs_declaration_file d on a.bgdhgbh = d.customs_no
        WHERE a.deleted = 0
        <if test="customsNoOrSaleOrderNo != null and customsNoOrSaleOrderNo != ''">
            AND (a.bgdhgbh = #{customsNoOrSaleOrderNo} or a.sale_order_no = #{customsNoOrSaleOrderNo})
        </if>
        <if test="entrustNo != null and entrustNo != ''">
            AND d.entrust_no = #{entrustNo}
        </if>
        <if test="declareCompany != null and declareCompany != ''">
            AND a.sbdwmc = #{declareCompany}
        </if>
        <if test="outDateStart != null">
            AND a.ckrq1 &gt;= #{outDateStart}
        </if>
        <if test="outDateEnd != null">
            AND a.ckrq1 &lt; DATE_FORMAT(DATE_ADD(#{outDateEnd},INTERVAL 1 DAY),'%Y-%m-%d')
        </if>
    </select>
    <select id="queryAllDeclareCompany4Dict" resultType="java.lang.String">
        SELECT DISTINCT sbdwmc FROM customs_declaration WHERE deleted = 0 ORDER BY sbdwmc
    </select>

</mapper>
