<?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.zfire.mall.manager.commonmapper.SaleOrderMapper">
    <select id="list" resultType="com.zfire.mall.manager.bean.sale.SaleOrderBean">
        SELECT
        so.id,
        so.order_time,
        so.customer_number,
        so.customer_id,
        so.customer_name,
        so.remark,
        so.create_by,
        so.create_time,
        so.update_by,
        so.update_time,
        so.file_name,
        so.file_url,
        so.correspond_id,
        so.correspond_name,
        so.approval_name,
        so.approval_time,
        so.approval_remark,
        so.correspond_user_name,
        so.engin_info_no,
        so.examine_status,
        so.close_name,
        so.close_time,
        so.engin_order_no,
        so.ref_project_name,
        so.ref_use_unit,
        so.engin_order_type,
        so.ref_factory_no,
        so.ref_install_address,
        so.ref_linkman,
        so.ref_phone,
        so.ref_tel,
        so.order_no,
        so.bill_receipt,
        so.type,
        so.invoice_type,
        so.ref_engin_record_no,
        so.counterclaim,
        so.sync_status,
        iao.main_order_id,
        iao.order_id,
        iao.invoice_id,
        iao.invoice_num,
        iao.approval_number,
        iao.refundable_qty,
        iao.retired_qty,
        iao.material_id,
        iao.order_type,
        iao.sale_type_id,
        iao.sale_type_code,
        iao.sale_type_name,
        iao.the_time,
        iao.material_old_number,
        iao.material_code,
        iao.material_name,
        iao.specification,
        iao.unit,
        iao.remark as invoiceRemark,
        iao.price,
        iao.pay_amount,
        iao.tax,
        iao.qty,
        iao.after_tax_price,
        iao.no_total_amount,
        iao.pay_rebate_amount,
        iao.total_disc_amount,
        iao.refund_status,
        iao.single_pay_price,
        iao.single_deposit_amount,
        iao.service_id,
        iao.service_name,
        iao.disc_amount,
        iao.king_dee_seq,
        iao.king_dee_entry_id,
        iao.king_sale_id,
        iao.king_dee_order_seq,
        iao.king_dee_order_id,
        iao.king_bill_no,
        iao.total_amount,
        iao.single_rebate_amount,
        iao.policy_id,
        iao.policy_title,
        iao.policy_code,
        iao.sales_order_no,
        iao.sales_order_item_id,
        iao.sales_order_id,
        iao.invoice_item_id,
        iao.requisition_id,
        iao.requisition_item_id,
        iao.header_remark,
        iao.k3_service_name,
        iao.k3_service_id,
        iao.k3_category_name,
        iao.customer_wallet_name,
        ia.order_time as invoiceOrderTime,
        pms.`status`,
        pms.print_time,
        cptr.k3_org_number
        FROM
        sales_order so
        JOIN sales_order_item iao ON so.id = iao.sales_order_no
        left JOIN invoice_application ia on so.order_no = ia.id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        left join pick_manager_ship pms on pms.invoice_order_id = iao.invoice_item_id
        where 1=1
    <!--    <if test="orderNo != null and orderNo != ''">
            and so.order_no like concat('%',#{orderNo},'%')
        </if>-->
        <if test="orderNo != null and orderNo.size > 0">
            and so.order_no in
            <foreach item="item" index="index" collection="orderNo" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
     <!--     <if test="id != null and id != ''">
            and so.id like concat('%',#{id},'%')
        </if>-->

        <if test="id != null and id.size > 0">
            and so.id in
            <foreach item="item" index="index" collection="id" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="mainOrderId != null and mainOrderId != ''">
            and (iao.main_order_id like concat('%',#{mainOrderId},'%') or so.engin_order_no  like concat('%',#{mainOrderId},'%'))
        </if>
        <if test="adminUserId != null and adminUserId.size > 0 and (customerId == null or customerId == '') ">
            and so.correspond_id in
            <foreach item="item" index="index" collection="adminUserId" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="syncStatus != null">
            and so.sync_status = #{syncStatus}
        </if>
        <if test="categoryId != null and categoryId != ''">
            and iao.k3_category_id = #{categoryId}
        </if>
        <if test="customerName != null and customerName != ''">
            and so.customer_name like concat('%',#{customerName},'%')
        </if>
           <if test="createBy != null and createBy != ''">
            and so.create_by like concat('%',#{createBy},'%')
        </if>
    <!--    <if test="correspondId != null and correspondId != ''">
            and so.correspond_id = #{correspondId}
        </if>-->
        <if test="correspondId != null and correspondId.size > 0">
            and so.correspond_id in
            <foreach item="item" index="index" collection="correspondId" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="specification != null and specification != ''">
            and iao.specification like concat('%',#{specification},'%')
        </if>
        <if test="customerNumber != null and customerNumber != ''">
            and so.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="examineStatus != null and examineStatus != ''">
            and so.examine_status = #{examineStatus}
        </if>
          <if test="materialName != null and materialName != ''">
            and iao.material_name like concat('%',#{materialName},'%')
        </if>
        <if test="correspondName != null and correspondName != ''">
            and so.correspond_name like concat('%',#{correspondName},'%')
        </if>
        <if test="materialNumber != null and materialNumber != ''">
            and iao.material_code like concat('%',#{materialNumber},'%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and so.order_time between #{startTime} and #{endTime}
        </if >
     <if test="invoiceStartTime != null and invoiceStartTime != '' and invoiceEndTime != null and invoiceEndTime != ''">
         and ia.order_time between #{invoiceStartTime} and #{invoiceEndTime}
        </if >
        <if test="approvalStartTime != null and approvalStartTime != '' and approvalEndTime != null and approvalEndTime != ''">
            and so.approval_time between #{approvalStartTime} and #{approvalEndTime}
        </if>
            <if test="printStartTime != null and printStartTime != '' and printEndTime != null and printEndTime != ''">
            and pms.print_time between #{printStartTime} and #{printEndTime}
        </if>
        <if test="customerId != null and customerId != ''">
            and so.customer_id = #{customerId}
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and cptr.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="printTimeDesc != null and printTimeDesc != ''">
            order by pms.print_time desc
        </if>
        <if test="printTimeDesc == null || printTimeDesc == ''">
            order by iao.id desc
        </if>
    </select>


    <resultMap id="mapDetail" type="com.zfire.mall.manager.bean.sale.SaleOrderBody">
        <id column="id" jdbcType="VARCHAR" property="id"/>
        <result column="order_time" jdbcType="TIMESTAMP" property="orderTime"/>
        <result column="customer_number" jdbcType="VARCHAR" property="customerNumber"/>
        <result column="customer_id" jdbcType="VARCHAR" property="customerId"/>
        <result column="customer_name" jdbcType="VARCHAR" property="customerName"/>
        <result column="remark" jdbcType="VARCHAR" property="remark"/>
        <result column="create_by" jdbcType="VARCHAR" property="createBy"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="file_name" jdbcType="VARCHAR" property="fileName"/>
        <result column="file_url" jdbcType="VARCHAR" property="fileUrl"/>
        <result column="correspond_id" jdbcType="VARCHAR" property="correspondId"/>
        <result column="correspond_name" jdbcType="VARCHAR" property="correspondName"/>
        <result column="approval_name" jdbcType="VARCHAR" property="approvalName"/>
        <result column="approval_time" jdbcType="TIMESTAMP" property="approvalTime"/>
        <result column="approval_remark" jdbcType="VARCHAR" property="approvalRemark"/>
        <result column="correspond_user_name" jdbcType="VARCHAR" property="correspondUserName"/>
        <result column="engin_info_no" jdbcType="VARCHAR" property="enginInfoNo"/>
        <result column="examine_status" jdbcType="VARCHAR" property="examineStatus"/>
        <result column="close_name" jdbcType="VARCHAR" property="closeName"/>
        <result column="close_time" jdbcType="TIMESTAMP" property="closeTime"/>
        <result column="engin_order_no" jdbcType="VARCHAR" property="enginOrderNo"/>
        <result column="ref_project_name" jdbcType="VARCHAR" property="refProjectName"/>
        <result column="ref_use_unit" jdbcType="VARCHAR" property="refUseUnit"/>
        <result column="engin_order_type" jdbcType="VARCHAR" property="enginOrderType"/>
        <result column="ref_factory_no" jdbcType="VARCHAR" property="refFactoryNo"/>
        <result column="ref_install_address" jdbcType="VARCHAR" property="refInstallAddress"/>
        <result column="ref_linkman" jdbcType="VARCHAR" property="refLinkman"/>
        <result column="ref_phone" jdbcType="VARCHAR" property="refPhone"/>
        <result column="ref_tel" jdbcType="VARCHAR" property="refTel"/>
        <result column="order_no" jdbcType="VARCHAR" property="orderNo"/>
        <result column="bill_receipt" jdbcType="TINYINT" property="billReceipt"/>
        <collection property="saleOrderData" ofType="com.zfire.mall.manager.bean.sale.SaleOrderData"
                    select="order" column="id">
            <id column="id" jdbcType="VARCHAR" property="id"/>
            <result column="main_order_id" jdbcType="VARCHAR" property="mainOrderId"/>
            <result column="order_id" jdbcType="VARCHAR" property="orderId"/>
            <result column="invoice_id" jdbcType="VARCHAR" property="invoiceId"/>
            <result column="invoice_num" jdbcType="INTEGER" property="invoiceNum"/>
            <result column="approval_number" jdbcType="INTEGER" property="approvalNumber"/>
            <result column="refundable_qty" jdbcType="INTEGER" property="refundableQty"/>
            <result column="retired_qty" jdbcType="INTEGER" property="retiredQty"/>
            <result column="material_id" jdbcType="VARCHAR" property="materialId"/>
            <result column="order_type" jdbcType="VARCHAR" property="orderType"/>
            <result column="sale_type_id" jdbcType="VARCHAR" property="saleTypeId"/>
            <result column="sale_type_code" jdbcType="VARCHAR" property="saleTypeCode"/>
            <result column="sale_type_name" jdbcType="VARCHAR" property="saleTypeName"/>
            <result column="the_time" jdbcType="TIMESTAMP" property="theTime"/>
            <result column="material_old_number" jdbcType="VARCHAR" property="materialOldNumber"/>
            <result column="material_code" jdbcType="VARCHAR" property="materialCode"/>
            <result column="material_name" jdbcType="VARCHAR" property="materialName"/>
            <result column="specification" jdbcType="VARCHAR" property="specification"/>
            <result column="unit" jdbcType="VARCHAR" property="unit"/>
            <result column="remark" jdbcType="VARCHAR" property="remark"/>
            <result column="price" jdbcType="DECIMAL" property="price"/>
            <result column="pay_amount" jdbcType="DECIMAL" property="payAmount"/>
            <result column="tax" jdbcType="DECIMAL" property="tax"/>
            <result column="qty" jdbcType="INTEGER" property="qty"/>
            <result column="after_tax_price" jdbcType="DECIMAL" property="afterTaxPrice"/>
            <result column="no_total_amount" jdbcType="DECIMAL" property="noTotalAmount"/>
            <result column="pay_rebate_amount" jdbcType="DECIMAL" property="payRebateAmount"/>
            <result column="total_disc_amount" jdbcType="DECIMAL" property="totalDiscAmount"/>
            <result column="refund_status" jdbcType="BIT" property="refundStatus"/>
            <result column="single_pay_price" jdbcType="DECIMAL" property="singlePayPrice"/>
            <result column="single_deposit_amount" jdbcType="DECIMAL" property="singleDepositAmount"/>
            <result column="service_id" jdbcType="VARCHAR" property="serviceId"/>
            <result column="service_name" jdbcType="VARCHAR" property="serviceName"/>
            <result column="disc_amount" jdbcType="DECIMAL" property="discAmount"/>
            <result column="king_dee_seq" jdbcType="VARCHAR" property="kingDeeSeq"/>
            <result column="king_dee_entry_id" jdbcType="VARCHAR" property="kingDeeEntryId"/>
            <result column="king_sale_id" jdbcType="VARCHAR" property="kingSaleId"/>
            <result column="king_dee_order_seq" jdbcType="VARCHAR" property="kingDeeOrderSeq"/>
            <result column="king_dee_order_id" jdbcType="VARCHAR" property="kingDeeOrderId"/>
            <result column="king_bill_no" jdbcType="VARCHAR" property="kingBillNo"/>
            <result column="total_amount" jdbcType="DECIMAL" property="totalAmount"/>
            <result column="single_rebate_amount" jdbcType="DECIMAL" property="singleRebateAmount"/>
            <result column="policy_id" jdbcType="VARCHAR" property="policyId"/>
            <result column="policy_title" jdbcType="VARCHAR" property="policyTitle"/>
            <result column="policy_code" jdbcType="VARCHAR" property="policyCode"/>
            <result column="sales_order_item_id" jdbcType="VARCHAR" property="salesOrderItemId"/>
            <result column="print_time" jdbcType="VARCHAR" property="printTime"/>
            <result column="print_status" jdbcType="VARCHAR" property="printStatus"/>
        </collection>
    </resultMap>


    <select id="detail" resultMap="mapDetail">
        SELECT
        *
        FROM sales_order
        WHERE id = #{id}
    </select>


    <select id="order" resultType="com.zfire.mall.manager.bean.sale.SaleOrderData">
        SELECT
        iao.*,
        ia.engin_order_no,
        iao.remark as invoiceRemark,
        pms.print_time,
        pms.status as print_status
        FROM
        sales_order_item iao
        left join  invoice_application ia on iao.invoice_id = ia.id
          left join pick_manager_ship pms on pms.invoice_order_id = iao.invoice_item_id
        WHERE
        iao.sales_order_no = #{id}
    </select>






    <select id="listSaleOrder" resultType="com.zfire.mall.manager.bean.sale.SaleOrderBean">
        SELECT
        so.id,
        so.order_time,
        so.customer_number,
        so.customer_id,
        so.customer_name,
        so.remark,
        so.create_by,
        so.create_time,
        so.update_by,
        so.update_time,
        so.file_name,
        so.file_url,
        so.correspond_id,
        so.correspond_name,
        so.approval_name,
        so.approval_time,
        so.approval_remark,
        so.correspond_user_name,
        so.engin_info_no,
        so.examine_status,
        so.close_name,
        so.close_time,
        so.engin_order_no,
        so.ref_project_name,
        so.ref_use_unit,
        so.engin_order_type,
        so.ref_factory_no,
        so.ref_install_address,
        so.ref_linkman,
        so.ref_phone,
        so.ref_tel,
        so.order_no,
        so.bill_receipt,
        iao.id as sales_order_item_id,
        iao.main_order_id,
        iao.order_id,
        iao.invoice_id,
        iao.invoice_num,
        iao.approval_number,
        (iao.refundable_qty   + IFNULL(sum(iaom.refundable_qty),0)) as refundable_qty,
        iao.retired_qty,
        iao.material_id,
        iao.order_type,
        iao.sale_type_id,
        iao.sale_type_code,
        iao.sale_type_name,
        iao.the_time,
        iao.material_old_number,
        iao.material_code,
        iao.material_name,
        iao.specification,
        iao.unit,
        iao.remark as invoice_remark,
        iao.price,
        iao.pay_amount,
        iao.tax,
        iao.qty,
        iao.after_tax_price,
        iao.no_total_amount,
        iao.pay_rebate_amount,
        iao.total_disc_amount,
        iao.refund_status,
        iao.single_pay_price,
        iao.single_deposit_amount,
        iao.service_id,
        iao.service_name,
        iao.disc_amount,
        iao.king_dee_seq,
        iao.king_dee_entry_id,
        iao.king_sale_id,
        iao.king_dee_order_seq,
        iao.king_dee_order_id,
        iao.king_bill_no,
        iao.total_amount,
        iao.single_rebate_amount,
        iao.policy_id,
        iao.policy_title,
        iao.policy_code,
        iao.sales_order_no,
        kdm.category_name,
        iao.header_remark,
        so.ref_engin_record_no
        FROM
        sales_order so
        JOIN sales_order_item iao ON so.id = iao.sales_order_no
        JOIN king_dee_material kdm on iao.material_id = kdm.id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        LEFT JOIN sales_order_item iaom ON iao.id = iaom.sales_order_item_id and iaom.refundable_qty &lt; 0
        LEFT JOIN sales_order iam on iam.id = iaom.invoice_id and iam.type = 2
        where 1=1
        and so.examine_status = 'OK'
        and so.type = 1
        and so.sync_status = 1
        <if test="orderNo != null and orderNo != ''">
              and so.order_no like concat('%',#{orderNo},'%')
        </if>
             <if test="id != null and id != ''">
            and so.id like concat('%',#{id},'%')
        </if>
           <if test="mainOrderId != null and mainOrderId != ''">
            and (iao.main_order_id like concat('%',#{mainOrderId},'%') or so.engin_order_no like concat('%',#{mainOrderId},'%'))
        </if>
        <if test="customerName != null and customerName != ''">
            and so.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="correspondId != null and correspondId != ''">
            and so.correspond_id = #{correspondId}
        </if>
        <if test="specification != null and specification != ''">
            and iao.specification like concat('%',#{specification},'%')
        </if>
        <if test="customerNumber != null and customerNumber != ''">
            and so.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="materialName != null and materialName != ''">
            and iao.material_name like concat('%',#{materialName},'%')
        </if>
        <if test="correspondName != null and correspondName != ''">
            and so.correspond_name like concat('%',#{correspondName},'%')
        </if>
        <if test="materialNumber != null and materialNumber != ''">
            and iao.material_code like concat('%',#{materialNumber},'%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and so.order_time between #{startTime} and #{endTime}
        </if>
        <if test="customerId != null and customerId != ''">
            and so.customer_id = #{customerId}
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and cptr.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY iao.id HAVING refundable_qty >0
        order by so.create_time desc
    </select>
    <select id="listStorageFee" resultType="com.zfire.mall.manager.bean.sale.StorageFeeBean">
        SELECT
        iao.order_type,
        IFNULL( ia.engin_order_no, iao.main_order_id ) AS main_order_id,
        iao.order_id AS orderId,
        IFNULL( ro.the_time, eo.order_date ) AS the_time,
        IFNULL( ro.examine_time, eo.confirm_time ) AS examine_time,
        roi.create_time AS reservedCreateTime,
        roi.reserved_id,
        ia.id AS invoiceId,
        ia.order_time,
        ia.approval_time,
        so.examine_status AS salesExamineStatus,
        so.approval_time AS salesApprovalTime,
        so.approval_name AS salesApprovalName,
        ia.correspond_name,
        ia.customer_number,
        ia.customer_name,
        iao.material_code,
        iao.specification,
        iao.qty,
        iao.pay_amount,
        iao.pay_rebate_amount,
        iao.refundable_qty,
        iao.total_amount,
        iao.customer_wallet_name,
        cptr.k3_org_number,
        cptr.service_name,
        eo.ref_use_unit,
        iao.total_disc_amount
        FROM
        invoice_application_order iao
        JOIN invoice_application ia ON ia.id = iao.invoice_id
        JOIN customer_product_type_rela cptr ON iao.customer_wallet_id = cptr.customer_wallet_id
        LEFT JOIN sales_order so ON so.order_no = ia.id
        LEFT JOIN reserved_order_item roi ON roi.order_id = iao.order_id
        LEFT JOIN retail_order ro ON iao.main_order_id = ro.id
        LEFT JOIN engin_order eo ON eo.engin_order_no = ia.engin_order_no
        WHERE
        1 = 1
        AND ia.examine_status = 'OK'
        <if test="invoiceStartTime != null and invoiceStartTime != '' and invoiceEndTime != null and invoiceEndTime != ''">
            and ia.order_time between #{invoiceStartTime} and #{invoiceEndTime}
        </if >
          <if test="approvalStartTime != null and approvalStartTime != '' and approvalEndTime != null and approvalEndTime != ''">
            and ia.approval_time between #{approvalStartTime} and #{approvalEndTime}
        </if >
             <if test="saleExamineStatus != null and saleExamineStatus != ''">
            and so.examine_status = #{saleExamineStatus}
        </if >
        <if test="saleStartTime != null and saleStartTime != '' and saleEndTime != null and saleEndTime != ''">
            and so.approval_time between #{saleStartTime} and #{saleEndTime}
        </if >
        <if test="orderNo != null and orderNo.size > 0">
            and so.order_no in
            <foreach item="item" index="index" collection="orderNo" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

        <if test="id != null and id.size > 0">
            and so.id in
            <foreach item="item" index="index" collection="id" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="mainOrderId != null and mainOrderId != ''">
            and (iao.main_order_id like concat('%',#{mainOrderId},'%') or ia.engin_order_no  like concat('%',#{mainOrderId},'%'))
        </if>

        <if test="customerName != null and customerName != ''">
            and ia.customer_name like concat('%',#{customerName},'%')
        </if>

        <if test="correspondId != null and correspondId.size > 0">
            and ia.correspond_id in
            <foreach item="item" index="index" collection="correspondId" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="materialNumber != null and materialNumber != ''">
            and iao.material_code like concat('%',#{materialNumber},'%')
        </if>
        <if test="specification != null and specification != ''">
            and iao.specification like concat('%',#{specification},'%')
        </if>
        <if test="customerNumber != null and customerNumber != ''">
            and so.customer_number like concat('%',#{customerNumber},'%')
        </if>

        <if test="materialName != null and materialName != ''">
            and iao.material_name like concat('%',#{materialName},'%')
        </if>



    <!--    <if test="customerId != null and customerId != ''">
            and so.customer_id = #{customerId}
        </if>-->
        <if test="serviceIds != null and serviceIds.size > 0">
            and cptr.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        group by iao.id
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            having the_time between #{startTime} and #{endTime}
        </if >

        order by iao.id desc
    </select>
    <select id="listCount" resultType="java.lang.Integer">
        SELECT
        count(1)
        FROM
        sales_order so
        JOIN sales_order_item iao ON so.id = iao.sales_order_no
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        left join pick_manager_ship pms on pms.invoice_order_id = iao.invoice_item_id
        where 1=1
        <!--    <if test="orderNo != null and orderNo != ''">
                and so.order_no like concat('%',#{orderNo},'%')
            </if>-->
        <if test="orderNo != null and orderNo.size > 0">
            and so.order_no in
            <foreach item="item" index="index" collection="orderNo" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <!--     <if test="id != null and id != ''">
               and so.id like concat('%',#{id},'%')
           </if>-->

        <if test="id != null and id.size > 0">
            and so.id in
            <foreach item="item" index="index" collection="id" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="mainOrderId != null and mainOrderId != ''">
            and (iao.main_order_id like concat('%',#{mainOrderId},'%') or so.engin_order_no  like concat('%',#{mainOrderId},'%'))
        </if>
        <if test="adminUserId != null and adminUserId.size > 0 and (customerId == null or customerId == '') ">
            and so.correspond_id in
            <foreach item="item" index="index" collection="adminUserId" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="syncStatus != null">
            and so.sync_status = #{syncStatus}
        </if>
        <if test="categoryId != null and categoryId != ''">
            and iao.k3_category_id = #{categoryId}
        </if>
        <if test="customerName != null and customerName != ''">
            and so.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="createBy != null and createBy != ''">
            and so.create_by like concat('%',#{createBy},'%')
        </if>
        <!--    <if test="correspondId != null and correspondId != ''">
                and so.correspond_id = #{correspondId}
            </if>-->
        <if test="correspondId != null and correspondId.size > 0">
            and so.correspond_id in
            <foreach item="item" index="index" collection="correspondId" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="specification != null and specification != ''">
            and iao.specification like concat('%',#{specification},'%')
        </if>
        <if test="customerNumber != null and customerNumber != ''">
            and so.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="examineStatus != null and examineStatus != ''">
            and so.examine_status = #{examineStatus}
        </if>
        <if test="materialName != null and materialName != ''">
            and iao.material_name like concat('%',#{materialName},'%')
        </if>
        <if test="correspondName != null and correspondName != ''">
            and so.correspond_name like concat('%',#{correspondName},'%')
        </if>
        <if test="materialNumber != null and materialNumber != ''">
            and iao.material_code like concat('%',#{materialNumber},'%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and so.order_time between #{startTime} and #{endTime}
        </if >
        <if test="invoiceStartTime != null and invoiceStartTime != '' and invoiceEndTime != null and invoiceEndTime != ''">
            and (select count(1) from invoice_application ia where ia.order_time between #{invoiceStartTime} and #{invoiceEndTime} and ia.id = so.order_no) > 0
        </if >
        <if test="approvalStartTime != null and approvalStartTime != '' and approvalEndTime != null and approvalEndTime != ''">
            and so.approval_time between #{approvalStartTime} and #{approvalEndTime}
        </if>
        <if test="printStartTime != null and printStartTime != '' and printEndTime != null and printEndTime != ''">
            and pms.print_time between #{printStartTime} and #{printEndTime}
        </if>
        <if test="customerId != null and customerId != ''">
            and so.customer_id = #{customerId}
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and cptr.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
    </select>



    <update id="updateInvoice">
             UPDATE invoice_application ia
 JOIN sales_order so ON ia.id = so.order_no
 set ia.sales_examine_status = so.examine_status
where so.id = #{id}
    </update>


    <insert id="insertCode">
       INSERT INTO code_manager (
                                id,
	                            shipment_id,
	                            line_number,
	                            order_id,
	                            owner_no,
	                            receiving_name,
	                            material_code,
	                            specification,
	                            barcode_no,
	                            scan,
	                            qty,
	                            unit,
	                            scanner_name,
	                            scan_time,
	                            scan_dot,
	                            create_by,
	                            create_time,
	                            update_by,
	                            update_time
	                            )
        SELECT
        b.id,
        b.shipment_id,
        b.line_number,
        b.order_id,
        b.owner_no,
        b.receiving_name,
        b.material_code,
        b.specification,
        b.barcode_no,
        b.scan,
        b.qty,
        b.unit,
        b.scanner_name,
        b.scan_time,
        b.scan_dot,
        b.create_by,
        b.create_time,
        b.update_by,
        b.update_time
        FROM
        	code_manager_temp b
        WHERE
        	b.barcode_no NOT IN ( SELECT a.barcode_no FROM code_manager a )
        	AND b.batch = #{batch}
    </insert>


    <select id="listCode" resultType="com.zfire.mall.manager.bean.sale.CodeManagerBean">
	    SELECT
			*
		FROM
			code_manager
        ${ex.query}
        ${ex.orderBy}
		ORDER BY create_time DESC,id DESC
    </select>
    <select id="listStorageFeeV2" resultType="com.zfire.mall.manager.bean.sale.StorageFeeBeanVO">
        SELECT
        iao.order_type,
        IFNULL( ia.engin_order_no, iao.main_order_id ) AS main_order_id,
        iao.order_id AS orderId,
        IFNULL( ro.the_time, eo.order_date ) AS the_time,
        IFNULL( ro.examine_time, eo.confirm_time ) AS examine_time,
        roi.create_time AS reservedCreateTime,
        roi.reserved_id,
        ia.id AS invoiceId,
        ia.order_time,
        ia.approval_time,
        so.examine_status AS salesExamineStatus,
        so.approval_time AS salesApprovalTime,
        so.approval_name AS salesApprovalName,
        ia.correspond_name,
        ia.customer_number,
        ia.customer_name,
        iao.material_code,
        iao.specification,
        iao.qty,
        iao.pay_amount,
        iao.pay_rebate_amount,
        iao.refundable_qty,
        iao.total_amount,
        iao.customer_wallet_name,
        cptr.k3_org_number,
        cptr.service_name,
        eo.ref_use_unit,
        iao.total_disc_amount
        FROM
        invoice_application_order iao
        JOIN invoice_application ia ON ia.id = iao.invoice_id
        JOIN customer_product_type_rela cptr ON iao.customer_wallet_id = cptr.customer_wallet_id
        LEFT JOIN sales_order so ON so.order_no = ia.id
        LEFT JOIN reserved_order_item roi ON roi.order_id = iao.order_id
        LEFT JOIN retail_order ro ON iao.main_order_id = ro.id
        LEFT JOIN engin_order eo ON eo.engin_order_no = ia.engin_order_no
        WHERE
        1 = 1
        AND ia.examine_status = 'OK'
        <if test="invoiceStartTime != null and invoiceStartTime != '' and invoiceEndTime != null and invoiceEndTime != ''">
            and ia.order_time between #{invoiceStartTime} and #{invoiceEndTime}
        </if >
        <if test="approvalStartTime != null and approvalStartTime != '' and approvalEndTime != null and approvalEndTime != ''">
            and ia.approval_time between #{approvalStartTime} and #{approvalEndTime}
        </if >
        <if test="saleExamineStatus != null and saleExamineStatus != ''">
            and so.examine_status = #{saleExamineStatus}
        </if >
        <if test="saleStartTime != null and saleStartTime != '' and saleEndTime != null and saleEndTime != ''">
            and so.approval_time between #{saleStartTime} and #{saleEndTime}
        </if >
        <if test="orderNo != null and orderNo.size > 0">
            and so.order_no in
            <foreach item="item" index="index" collection="orderNo" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

        <if test="id != null and id.size > 0">
            and so.id in
            <foreach item="item" index="index" collection="id" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="mainOrderId != null and mainOrderId != ''">
            and (iao.main_order_id like concat('%',#{mainOrderId},'%') or ia.engin_order_no  like concat('%',#{mainOrderId},'%'))
        </if>

        <if test="customerName != null and customerName != ''">
            and ia.customer_name like concat('%',#{customerName},'%')
        </if>

        <if test="correspondId != null and correspondId.size > 0">
            and ia.correspond_id in
            <foreach item="item" index="index" collection="correspondId" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="materialNumber != null and materialNumber != ''">
            and iao.material_code like concat('%',#{materialNumber},'%')
        </if>
        <if test="specification != null and specification != ''">
            and iao.specification like concat('%',#{specification},'%')
        </if>
        <if test="customerNumber != null and customerNumber != ''">
            and so.customer_number like concat('%',#{customerNumber},'%')
        </if>

        <if test="materialName != null and materialName != ''">
            and iao.material_name like concat('%',#{materialName},'%')
        </if>



        <!--    <if test="customerId != null and customerId != ''">
                and so.customer_id = #{customerId}
            </if>-->
        <if test="serviceIds != null and serviceIds.size > 0">
            and cptr.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        group by iao.id
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            having the_time between #{startTime} and #{endTime}
        </if >

        order by iao.id desc
    </select>
    <select id="listV2" resultType="com.zfire.mall.manager.bean.sale.SaleOrderVO">
        SELECT
        so.id,
        so.order_time,
        so.customer_number,
        so.customer_id,
        so.customer_name,
        so.remark,
        so.create_by,
        so.create_time,
        so.update_by,
        so.update_time,
        so.file_name,
        so.file_url,
        so.correspond_id,
        so.correspond_name,
        so.approval_name,
        so.approval_time,
        so.approval_remark,
        so.correspond_user_name,
        so.engin_info_no,
        so.examine_status,
        so.close_name,
        so.close_time,
        so.engin_order_no,
        so.ref_project_name,
        so.ref_use_unit,
        so.engin_order_type,
        so.ref_factory_no,
        so.ref_install_address,
        so.ref_linkman,
        so.ref_phone,
        so.ref_tel,
        so.order_no,
        so.bill_receipt,
        so.bill_date,
        so.type,
        so.invoice_type,
        so.ref_engin_record_no,
        so.counterclaim,
        so.sync_status,
        iao.main_order_id,
        iao.order_id,
        iao.invoice_id,
        iao.invoice_num,
        iao.approval_number,
        iao.refundable_qty,
        iao.retired_qty,
        iao.material_id,
        iao.order_type,
        iao.sale_type_id,
        iao.sale_type_code,
        iao.sale_type_name,
        iao.the_time,
        iao.material_old_number,
        iao.material_code,
        iao.material_name,
        iao.specification,
        iao.unit,
        iao.remark as invoiceRemark,
        iao.price,
        iao.pay_amount,
        iao.tax,
        iao.qty,
        iao.after_tax_price,
        iao.no_total_amount,
        iao.pay_rebate_amount,
        iao.total_disc_amount,
        iao.refund_status,
        iao.single_pay_price,
        iao.single_deposit_amount,
        iao.service_id,
        iao.service_name,
        iao.disc_amount,
        iao.king_dee_seq,
        iao.king_dee_entry_id,
        iao.king_sale_id,
        iao.king_dee_order_seq,
        iao.king_dee_order_id,
        iao.king_bill_no,
        iao.total_amount,
        iao.single_rebate_amount,
        iao.policy_id,
        iao.policy_title,
        iao.policy_code,
        iao.sales_order_no,
        iao.sales_order_item_id,
        iao.sales_order_id,
        iao.invoice_item_id,
        iao.requisition_id,
        iao.requisition_item_id,
        iao.header_remark,
        iao.k3_service_name,
        iao.k3_service_id,
        iao.k3_category_name,
        iao.customer_wallet_name,
        iao.goods_order_id,
        ia.order_time as invoiceOrderTime,
        pms.`status`,
        pms.print_time,
        cptr.k3_org_number
        FROM
        sales_order so
        JOIN sales_order_item iao ON so.id = iao.sales_order_no
        left JOIN invoice_application ia on so.order_no = ia.id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        left join pick_manager_ship pms on pms.invoice_order_id = iao.invoice_item_id

        ${ex.query}

        <if test="adminUserId != null and adminUserId.size > 0 and (customerId == null or customerId == '') ">
            and so.correspond_id in
            <foreach item="item" index="index" collection="adminUserId" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="customerId != null and customerId != ''">
            and so.customer_id = #{customerId}
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and cptr.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

        <if test="ex.orderBy == null or ex.orderBy ==''">
            order by so.create_time desc
        </if>
        ${ex.orderBy}
    </select>
    <select id="listCountV2" resultType="java.lang.Integer">
          SELECT
        count(1)
        FROM
        sales_order so
        JOIN sales_order_item iao ON so.id = iao.sales_order_no
        left JOIN invoice_application ia on so.order_no = ia.id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        left join pick_manager_ship pms on pms.invoice_order_id = iao.invoice_item_id

        ${ex.query}

        <if test="adminUserId != null and adminUserId.size > 0 and (customerId == null or customerId == '') ">
            and so.correspond_id in
            <foreach item="item" index="index" collection="adminUserId" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="customerId != null and customerId != ''">
            and so.customer_id = #{customerId}
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and cptr.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

        <if test="ex.orderBy == null or ex.orderBy ==''">
            order by so.create_time desc
        </if>
        ${ex.orderBy}
    </select>

    <select id="listV3" resultType="com.zfire.mall.manager.bean.sale.SaleOrderVOV">
        SELECT
        so.id,
        so.order_time,
        so.customer_number,
        so.customer_id,
        so.customer_name,
        so.remark,
        so.create_by,
        so.create_time,
        so.update_by,
        so.update_time,
        so.file_name,
        so.file_url,
        so.correspond_id,
        so.correspond_name,
        so.approval_name,
        so.approval_time,
        so.approval_remark,
        so.correspond_user_name,
        so.engin_info_no,
        so.examine_status,
        so.close_name,
        so.close_time,
        so.engin_order_no,
        so.ref_project_name,
        so.ref_use_unit,
        so.engin_order_type,
        so.ref_factory_no,
        so.ref_install_address,
        so.ref_linkman,
        so.ref_phone,
        so.ref_tel,
        so.order_no,
        so.bill_receipt,
        so.bill_date,
        so.type,
        so.invoice_type,
        so.ref_engin_record_no,
        so.counterclaim,
        so.sync_status,
        iao.main_order_id,
        iao.order_id,
        iao.invoice_id,
        iao.invoice_num,
        iao.approval_number,
        iao.refundable_qty,
        iao.retired_qty,
        iao.material_id,
        iao.order_type,
        iao.sale_type_id,
        iao.sale_type_code,
        iao.sale_type_name,
        iao.the_time,
        iao.material_old_number,
        iao.material_code,
        iao.material_name,
        iao.specification,
        iao.unit,
        iao.remark as invoiceRemark,
        iao.price,
        iao.pay_amount,
        iao.tax,
        iao.qty,
        iao.after_tax_price,
        iao.no_total_amount,
        iao.pay_rebate_amount,
        iao.total_disc_amount,
        iao.refund_status,
        iao.single_pay_price,
        iao.single_deposit_amount,
        iao.service_id,
        iao.service_name,
        iao.disc_amount,
        iao.king_dee_seq,
        iao.king_dee_entry_id,
        iao.king_sale_id,
        iao.king_dee_order_seq,
        iao.king_dee_order_id,
        iao.king_bill_no,
        iao.total_amount,
        iao.single_rebate_amount,
        iao.policy_id,
        iao.policy_title,
        iao.policy_code,
        iao.sales_order_no,
        iao.sales_order_item_id,
        iao.sales_order_id,
        iao.invoice_item_id,
        iao.requisition_id,
        iao.requisition_item_id,
        iao.header_remark,
        iao.k3_service_name,
        iao.k3_service_id,
        iao.k3_category_name,
        iao.customer_wallet_name,
        iao.goods_order_id,
        ia.order_time as invoiceOrderTime,
        pms.`status`,
        pms.print_time,
        cptr.k3_org_number
        FROM
        sales_order so
        JOIN sales_order_item iao ON so.id = iao.sales_order_no
        left JOIN invoice_application ia on so.order_no = ia.id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        left join pick_manager_ship pms on pms.invoice_order_id = iao.invoice_item_id

        ${ex.query}

        <if test="adminUserId != null and adminUserId.size > 0 and (customerId == null or customerId == '') ">
            and so.correspond_id in
            <foreach item="item" index="index" collection="adminUserId" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="customerId != null and customerId != ''">
            and so.customer_id = #{customerId}
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and cptr.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

        <if test="ex.orderBy == null or ex.orderBy ==''">
            order by so.create_time desc
        </if>
        ${ex.orderBy}
    </select>
</mapper>