<?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.purchasecontract.mapper.PurchaseContractMapper">
    <resultMap type="com.trade.service.trade.purchasecontract.dto.PurchaseContractDto" id="PurchaseContractMap">
        <result property="id" column="id"/>
        <result property="customerUserId" column="customer_user_id"/>
        <result property="customerIdentifierNo" column="identifier_no"/>
        <result property="customerEnterpriseName" column="enterprise_name"/>
        <result property="customerEnterpriseCertNo" column="enterprise_cert_no"/>
        <result property="contractNo" column="contract_no"/>
        <result property="contractName" column="contract_name"/>
        <result property="salesContractId" column="sales_contract_id"/>
        <result property="salesContractNo" column="sales_contract_no"/>
        <result property="salesContractName" column="sales_contract_name"/>
        <result property="vendorEntityNo" column="vendor_entity_no"/>
        <result property="vendorEntityName" column="vendor_entity_name"/>
        <result property="agencyCertNo" column="agency_cert_no"/>
        <result property="agencyEnterpriseName" column="agency_enterprise_name"/>
        <result property="templateType" column="template_type"/>
        <result property="signDate" column="sign_date"/>
        <result property="status" column="status"/>
        <result property="draft" column="draft"/>
        <result property="contractAmount" column="contract_amount"/>
        <result property="contractCurrency" column="contract_currency"/>
        <result property="settlementCycle" column="settlement_cycle"/>
        <result property="createBy" column="create_by"/>
        <result property="updateBy" column="update_by"/>
        <result property="gmtCreate" column="gmt_create"/>
        <result property="gmtModified" column="gmt_modified"/>
        <result property="gmtSubmit" column="gmt_submit"/>
    </resultMap>

    <sql id="getPurchaseContractDtoBaseSql">
        select t.`id`,
               t.`customer_user_id`,
               e.`customer_identifier_no` as `identifier_no`,
               e.`name`                   as `enterprise_name`,
               e.`cert_no`                as `enterprise_cert_no`,
               t.`contract_no`,
               t.`contract_name`,
               t.`sales_contract_no`,
               s.`id`                     as `sales_contract_id`,
               s.`contract_name`          as `sales_contract_name`,
               t.`vendor_entity_no`,
               v.`entity_name`            as `vendor_entity_name`,
               s.`agency_cert_no`,
               p.`platform_name`          as `agency_enterprise_name`,
               t.`template_type`,
               t.`sign_date`,
               t.`draft`,
               t.`status`,
               t.`contract_amount`,
               t.`contract_currency`,
               c.`settlement_cycle`,
               t.`gmt_create`,
               t.`create_by`,
               t.`gmt_modified`,
               t.`update_by`,
               t.`gmt_submit`
        from purchase_contract t
                 left join contract_terms c on c.contract_id = t.id and c.contract_type = 'PC'
                 left join sales_contract s on (s.contract_no = t.sales_contract_no and s.customer_user_id = t.customer_user_id)
                 left join enterprise e on e.customer_id = t.customer_user_id
                 left join trading_platform p on p.cert_no = s.agency_cert_no
                 left join vendor_entity v on v.entity_no = t.vendor_entity_no
    </sql>

    <select id="getPurchaseContractBySearch"
            parameterType="com.trade.service.trade.purchasecontract.dto.PurchaseContractSearchBaseDto"
            resultMap="PurchaseContractMap">
        <include refid="getPurchaseContractDtoBaseSql"/>
        <where>
            t.deleted = 0
            <if test="customerUserId != null">
                AND t.customer_user_id = #{customerUserId}
            </if>
            <if test="contractInfo != null and contractInfo != ''">
                AND (t.contract_name LIKE CONCAT ('%',#{contractInfo},'%') OR t.contract_no = #{contractInfo})
            </if>
            <if test="salesContractNo != null and salesContractNo != ''">
                AND t.sales_contract_no = #{salesContractNo}
            </if>
            <if test="vendorEntityNo != null and vendorEntityNo != ''">
                AND t.vendor_entity_no = #{vendorEntityNo}
            </if>
            <if test="agencyCertNo != null and agencyCertNo != ''">
                AND s.agency_cert_no = #{agencyCertNo}
            </if>
            <if test="point == 0 ">
                AND t.status = '4'
                AND s.agency_cert_no IS NOT NULL AND s.agency_cert_no != ''
            </if>
            <if test="contractStatus != null and contractStatus != ''">
                AND t.status = #{contractStatus}
            </if>
            <if test="point ==1 and timeStart != null">
                AND t.gmt_create <![CDATA[ >= ]]> #{timeStart}
            </if>
            <if test="point ==0 and timeStart != null">
                AND t.gmt_submit <![CDATA[ >= ]]> #{timeStart}
            </if>
            <if test="point ==1 and timeEnd != null">
                AND t.gmt_create <![CDATA[ <= ]]> #{timeEnd}
            </if>
            <if test="point ==0 and timeEnd != null">
                AND t.gmt_submit <![CDATA[ <= ]]> #{timeEnd}
            </if>
            <if test="null != customerUserIdList and customerUserIdList.size()>0">
                AND t.customer_user_id IN
                <foreach collection="customerUserIdList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        <choose>
            <when test="customerUserId != null">
                ORDER BY t.gmt_create DESC
            </when>
            <otherwise>
                ORDER BY t.gmt_submit DESC
            </otherwise>
        </choose>
    </select>

    <select id="getPurchaseContractById"
            parameterType="java.lang.Long" resultMap="PurchaseContractMap">
        <include refid="getPurchaseContractDtoBaseSql"/>
        <where>
            t.id = #{id} AND t.deleted = 0
        </where>
    </select>
    <select id="findBySaleContractNo" resultMap="PurchaseContractMap"
            parameterType="java.lang.String">
        <include refid="getPurchaseContractDtoBaseSql"/>
        where t.sales_contract_no = #{saleContractNo} and t.deleted = 0
        and t.customer_user_id = #{customerUserId}
        <if test="vendorEntityNo != null and vendorEntityNo != ''">
            AND t.vendor_entity_no = #{vendorEntityNo}
        </if>
        order by t.id desc
        limit 1
    </select>

    <select id="findByCustomerUserIdAndPurchaseContractNo" resultMap="PurchaseContractMap"
            parameterType="java.lang.String">
        <include refid="getPurchaseContractDtoBaseSql"/>
        where t.contract_no = #{purchaseContractNo} and t.deleted = 0
        and t.customer_user_id = #{customerUserId}
    </select>

    <select id="queryList4FilingDocBySearch"
            resultType="com.trade.service.trade.filingdoc.vo.PurchaseContract4FilingDocVO">
        select a.id,
               a.contract_no,
               a.sales_contract_no,
               b.agency_enterprise_name,
               c.name enterise_name,
               a.contract_amount,
               a.contract_currency,
               a.sign_date
        from purchase_contract a
                 left join sales_contract b on a.sales_contract_no = b.contract_no
                 left join enterprise c on a.customer_user_id = c.customer_id
                 left join sales_order d on b.id = d.sale_contract_id
                 left join tax_rebate_export_details e on d.order_no = e.sales_order_no
        where a.deleted = 0
        <if test="declareDate != null and declareDate != ''">
            AND e.declaration_month_year = #{declareDate}
        </if>
        <if test="declareBatch != null and declareBatch != ''">
            AND e.declaration_batch = #{declareBatch}
        </if>
        <if test="customerUserId != null">
            AND a.customer_user_id = #{customerUserId}
        </if>
        <if test="contractNo != null and contractNo != ''">
            AND a.contract_no = #{contractNo}
        </if>
        <if test="saleContractNo != null and saleContractNo != ''">
            AND a.sales_contract_no = #{saleContractNo}
        </if>
        <if test="agencyCertNo != null and agencyCertNo != ''">
            AND b.agency_cert_no = #{agencyCertNo}
        </if>
        <if test="importerCertNo != null and importerCertNo != ''">
            AND b.importer_cert_no >= #{importerCertNo}
        </if>
        <if test="signDateStart != null">
            AND a.sign_date &gt;= #{signDateStart}
        </if>
        <if test="signDateEnd != null">
            AND a.sign_date &lt; DATE_FORMAT(DATE_ADD(#{signDateEnd},INTERVAL 1 DAY),'%Y-%m-%d')
        </if>
    </select>

</mapper>

