<?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.SalesOrderMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.trade.service.trade.salesorder.domain.SalesOrder">
        <result column="id" property="id"/>
        <result column="deleted" property="deleted"/>
        <result column="customer_user_id" property="customerUserId"/>
        <result column="order_no" property="orderNo"/>
        <result column="importer_id" property="importerId"/>
        <result column="sale_contract_id" property="saleContractId"/>
        <result column="amount" property="amount"/>
        <result column="pending_received_amount" property="pendingReceivedAmount"/>
        <result column="status" property="status"/>
        <result column="customs_declaration_status" property="customsDeclarationStatus"/>
        <result column="gmt_create" property="gmtCreate"/>
        <result column="gmt_modified" property="gmtModified"/>
        <result column="created_by" property="createBy"/>
        <result column="updated_by" property="updateBy"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id
        ,
        deleted,
        created_by, updated_by,
        customer_user_id, order_no, importer_id, sale_contract_id, amount, pending_received_amount, status, customs_declaration_status, gmt_create, gmt_modified
    </sql>

    <select id="queryListBySearch" resultType="com.trade.service.trade.salesorder.vo.SalesOrderVO">
        SELECT
        a.id,
        a.deleted, a.pending_received_amount,
        a.customer_user_id, a.order_no, a.importer_id, a.sale_contract_id, a.amount, a.status, a.gmt_create,
        a.gmt_modified,
        b.contract_no as sales_contract_no,
        b.port_departure as from_port,
        b.port_destination as dest_port,
        c.payment_way,
        c.payment_currency,
        c.settlement_cycle,
        d.entity_name AS importer_name,
        e.identifier_no AS customer_identifier_no,
        f.name AS enterprise_name,
        g.platform_name agency_enterprise_name
        FROM
        sales_order a
        LEFT JOIN sales_contract b ON a.sale_contract_id = b.id
        LEFT JOIN contract_terms c ON b.id = c.contract_id
        LEFT JOIN importer_entity d ON a.importer_id = d.id
        LEFT JOIN customer_user e ON a.customer_user_id = e.id
        LEFT JOIN enterprise f ON a.customer_user_id = f.customer_id
        LEFT JOIN trading_platform g ON b.agency_cert_no = g.cert_no
        WHERE
        a.deleted = 0
        <if test="customerUserId!= null">
            AND a.customer_user_id = #{customerUserId}
        </if>
        <if test="orderNo!= null and orderNo!= ''">
            AND a.order_no = #{orderNo}
        </if>
        <if test="paymentWay!= null and paymentWay!= ''">
            AND c.payment_way = #{paymentWay}
        </if>
        <if test="importerId!= null">
            AND a.importer_id = #{importerId}
        </if>
        <if test="contractNo!= null and contractNo!= ''">
            AND b.contract_no = #{contractNo}
        </if>
        <if test="point == 0 ">
            AND a.status != 0
        </if>
        <if test="status!= null">
            AND a.status = #{status}
        </if>
        <if test="createdTimeStart!= null">
            AND a.gmt_create &gt;= #{createdTimeStart}
        </if>
        <if test="createdTimeEnd!= null">
            AND a.gmt_create &lt; DATE_FORMAT(DATE_ADD(#{createdTimeEnd},INTERVAL 1 DAY),'%Y-%m-%d')
        </if>
        <if test="userName!= null and userName!= ''">
            AND (e.identifier_no = #{userName} or f.name like CONCAT('%',#{userName},'%'))
        </if>
        <if test="agencyCertNo!= null and agencyCertNo!= ''">
            AND b.agency_cert_no = #{agencyCertNo}
        </if>
        <if test="null != statusList and statusList.size()>0">
            AND a.status IN
            <foreach collection="statusList" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        ORDER BY a.id DESC
    </select>


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

    <update id="updateCustomsDeclarationStatusFinishedByOrderNo">
        UPDATE sales_order
        SET customs_declaration_status = 1
        WHERE order_no = #{orderNo}
          AND deleted = 0
    </update>

</mapper>
