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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.trade.service.trade.salesorder.domain.SalesOrderInvoice">
        <result column="id" property="id"/>
        <result column="deleted" property="deleted"/>
        <result column="order_id" property="orderId"/>
        <result column="invoice_no" property="invoiceNo"/>
        <result column="amount" property="amount"/>
        <result column="file_id" property="fileId"/>
        <result column="gmt_create" property="gmtCreate"/>
        <result column="gmt_modified" property="gmtModified"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id
        ,
        deleted,
        order_id, invoice_no, amount, file_id, gmt_create, gmt_modified
    </sql>


    <select id="queryListBySearch" resultType="com.trade.service.trade.salesorder.vo.SalesOrderInvoiceVO">

        SELECT
        a.invoice_no,
        a.amount,
        a.gmt_create,
        a.file_id,
        b.order_no,
        c.entity_name as importer_name,
        d.contract_no as contract_no,
        e.platform_name as agency_enterprise_name,
        f.identifier_no AS customer_identifier_no,
        g.name AS enterprise_name,
        (SELECT sum(sog.count) FROM sales_order_goods sog WHERE sog.sale_order_id = b.id) AS goods_count
        FROM
        sales_order_invoice a
        join sales_order b on a.order_id = b.id
        left join importer_entity c on b.importer_id = c.id
        left join sales_contract d on b.sale_contract_id = d.id
        left join trading_platform e on d.agency_cert_no = e.cert_no
        left join customer_user f on b.customer_user_id = f.id
        left join enterprise g on b.customer_user_id = g.customer_id
        WHERE
        a.deleted = 0
        <if test="customerUserId!= null">
            AND b.customer_user_id = #{customerUserId}
        </if>
        <if test="orderNo!= null and orderNo!= ''">
            AND b.order_no = #{orderNo}
        </if>
        <if test="invoiceNo!= null and invoiceNo!= ''">
            AND a.invoice_no LIKE CONCAT('%', #{invoiceNo}, '%')
        </if>
        <if test="importerId!= null">
            AND b.importer_id = #{importerId}
        </if>
        <if test="contractNo!= null and contractNo!= ''">
            AND d.contract_no = #{contractNo}
        </if>
        <if test="agencyCertNo!= null and agencyCertNo!= ''">
            AND e.cert_no = #{agencyCertNo}
        </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 f.identifier_no = #{userName} or g.name like CONCAT('%',#{userName},'%')
        </if>
        ORDER BY a.id DESC
    </select>
    <select id="getDetailByInvoiceNo" resultType="com.trade.service.trade.salesorder.vo.SalesOrderInvoiceDtlVO">
        SELECT
            a.order_id,
            a.invoice_no,
            a.amount,
            a.gmt_create,
            a.file_id,
            b.order_no,
            c.entity_name as importer_name,
            d.contract_no as sales_contract_no,
            d.contract_name as sales_contract_name,
            d.trade_terms,
            d.port_departure,
            d.port_destination,
            e.platform_name as agency_enterprise_name,
            f.payment_way
        FROM
            sales_order_invoice a
                join sales_order b on a.order_id = b.id
                left join importer_entity c on b.importer_id = c.id
                left join sales_contract d on b.sale_contract_id = d.id
                left join trading_platform e on d.agency_cert_no = e.cert_no
                left join contract_terms f on d.id = f.contract_id
        WHERE
            a.deleted = 0
          and a.invoice_no = #{invoiceNo}
    </select>

</mapper>
