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




    <select id="list" resultType="com.zfire.mall.manager.bean.ship.ShipDocumentBean">
        SELECT
        sd.*,
        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 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.k3_service_id,
        iao.k3_service_name,
        iao.header_remark,
        kdm.category_name,
        kdm.material_group_name,
        kdm.pjxh1_Text,
        IF(so.examine_status = 'OK',1,0) as salesStatus
        FROM ship_document sd
        JOIN invoice_application_order iao ON sd.id = iao.invoice_id
        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 so on iao.invoice_id = so.order_no
        WHERE 1 = 1
        and iao.order_type in ('RETAIL','RETAIL_POLICY','PERMU_RETAIL','PERMU_RETAIL_POLICY','REQUISITION_RETAIL','REQUISITION_RETAIL_POLICY')
        <if test="id != null and id != ''">
            and sd.id = #{id}
        </if>
        <if test="customerName != null and customerName != ''">
            and sd.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="customerNumber != null and customerNumber != ''">
            and sd.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="materialName != null and materialName != ''">
            and iao.material_name like concat('%',#{materialName},'%')
        </if>
        <if test="specification != null and specification != ''">
            and iao.specification like concat('%',#{specification},'%')
        </if>
        <if test="startTime != null and startTime != ''">
            and sd.order_time BETWEEN #{startTime} and #{endTime}
        </if>
        <if test="customerId != null and customerId != ''">
            and sd.customer_id = #{customerId}
        </if>
          <if test="serviceId != null and serviceId != ''">
            and iao.service_id = #{serviceId}
        </if>
           <if test="k3serviceId != null and k3serviceId != ''">
            and iao.k3_service_id = #{k3serviceId}
        </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="mainOrderId != null and mainOrderId != ''">
            and iao.main_order_id = #{mainOrderId}
        </if>
        <if test="categoryName != null and categoryName != ''">
            and kdm.category_name = #{categoryName}
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and kdm.category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        order by sd.create_time desc
    </select>

    <select id="listRetreat" resultType="com.zfire.mall.manager.bean.ship.RetreatDocumentBean">
        SELECT
            rd.*,
            rdo.*
        FROM
            retreat_document rd
                 JOIN retreat_document_order rdo ON rd.id = rdo.retreat_id
                join king_dee_material kdm on kdm.id=rdo.material_id
        where 1=1
        <if test="retreatOrderNo != null and retreatOrderNo != ''">
            and rd.id = #{retreatOrderNo}
        </if>
        <if test="customerName != null and customerName != ''">
            and rd.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="materialName != null and materialName != ''">
            and rdo.material_name like concat('%',#{materialName},'%')
        </if>
        <if test="specification != null and specification != ''">
            and rdo.specification like concat('%',#{specification},'%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and rd.retreat_time between #{startTime} and #{endTime}
        </if>
        <if test="customerNumber != null and customerNumber != ''">
            and rd.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="customerId != null and customerId != ''">
            and rd.customer_id = #{customerId}
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and rdo.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 kdm.category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        order by rd.create_time desc
    </select>


    <select id="listPick" resultType="com.zfire.mall.manager.bean.pick.PickManagerBean">
        SELECT
        pm.*,
        pms.pick_id,
        pms.invoice_order_id,
        pms.print_num,
        pms.status,
        pms.print_time,
        pms.pick_actual_time,
        pms.material_id,
        pms.material_old_number,
        pms.material_code,
        pms.material_name,
        pms.receiving_name,
        pms.phone,
        pms.address,
        pms.main_order_id,
        pms.order_id,
        pms.invoice_id,
        pms.approval_number,
        pms.invoice_num,
        pms.refundable_qty+iao.retired_qty as refundable_qty,
        pms.order_type,
        pms.sale_type_id,
        pms.sale_type_code,
        pms.sale_type_name,
        pms.the_time,
        pms.specification,
        pms.unit,
        pms.price,
        pms.pay_amount,
        pms.tax,
        pms.qty,
        pms.after_tax_price,
        pms.no_total_amount,
        pms.pay_rebate_amount,
        pms.total_disc_amount,
        pms.k3_category_id as category_id,
        pms.k3_category_number as category_number,
        pms.k3_category_name as  category_name,
        pms.sales_order_id,
        pms.ref_engin_record_no,
        pms.engin_order_no,
        iao.invoice_id
        FROM
        pick_manager_ship pms
        JOIN pick_manager pm ON pm.id = pms.pick_id
        join customer_product_type_rela cptr on pms.customer_wallet_id = cptr.customer_wallet_id
        join invoice_application_order iao on pms.invoice_order_id = iao.id
        where 1=1
        <if test="invoiceId != null and invoiceId != ''">
            and pms.invoice_id like concat('%',#{invoiceId},'%')
        </if>
        <if test="logisticsId != null and logisticsId != ''">
            and pm.logistics_id = #{logisticsId}
        </if>
         <if test="printStartTime != null and printStartTime != ''">
             and pms.print_time BETWEEN #{printStartTime} and #{printEndTime}
        </if>
         <if test="salesOrderId != null and salesOrderId != ''">
            and pms.sales_order_id like concat('%',#{salesOrderId},'%')
        </if>
        <if test="takerPhone != null and takerPhone != ''">
            and pm.taker_phone like concat('%',#{takerPhone},'%')
        </if>
        <if test="correspondIds != null and correspondIds.size > 0">
            and pm.correspond_id in
            <foreach item="item" index="index" collection="correspondIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="createBy != null and createBy != ''">
            and pm.create_by like concat('%',#{createBy},'%')
        </if>
        <if test="id != null and id != ''">
            and pm.id = #{id}
        </if>
        <if test="correspondId != null and correspondId != ''">
            and pm.correspond_id = #{correspondId}
        </if>
        <if test="takerId != null and takerId != ''">
            and pm.taker_id like concat('%',#{takerId},'%')
        </if>
        <if test="pickStartTime != null and pickStartTime != ''">
            and pm.pick_time BETWEEN #{pickStartTime} and #{pickEndTime}
        </if>
          <if test="startTime != null and endTime != ''">
            and pm.create_time BETWEEN #{startTime} and #{endTime}
        </if>
        <if test="customerId != null and customerId != ''">
            and pm.customer_id = #{customerId}
        </if>
        <if test="customerIds != null and customerIds != ''">
            and pm.customer_id = #{customerIds}
        </if>
        <if test="customerName != null and customerName != ''">
            and pm.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="customerNumber != null and customerNumber != ''">
            and pm.customer_number like concat('%',#{customerNumber},'%')
        </if>
          <if test="status != null">
            and pms.status = #{status}
        </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>
        order by pms.id desc
    </select>


    <select id="listPrint" resultType="com.zfire.mall.manager.bean.pick.PickManagerBean">
        SELECT
        pm.*,
        pms.invoice_order_id,
        pms.print_num,
        pms.STATUS,
        pms.print_time,
        pms.pick_actual_time,
        iao.main_order_id,
        iao.order_id,
        iao.invoice_id,
        iao.invoice_num,
        iao.approval_number,
        iao.refundable_qty+iao.retired_qty 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.price,
        iao.pay_amount,
        iao.tax,
        iao.qty,
        iao.after_tax_price,
        iao.no_total_amount,
        iao.pay_rebate_amount,
        iao.total_disc_amount,
        ia.engin_order_no,
        ia.invoice_id,
        kdm.category_id,
        kdm.category_name
        FROM
        pick_manager_ship pms
        LEFT JOIN pick_manager pm ON pm.id = pms.pick_id
        LEFT JOIN sales_order_item iao ON iao.invoice_item_id = pms.invoice_order_id
        LEFT JOIN invoice_application ia on iao.invoice_id = ia.id
        LEFT JOIN king_dee_material kdm on iao.material_id = kdm.id
        LEFT JOIN king_dee_category kdc on kdm.category_id = kdc.id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        where 1=1
        and ia.type =1
        <if test="invoiceId != null and invoiceId != ''">
            and iao.invoice_id like concat('%',#{invoiceId},'%')
        </if>
          <if test="takerPhone != null and takerPhone != ''">
            and pm.taker_phone like concat('%',#{takerPhone},'%')
        </if>
        <if test="createBy != null and createBy != ''">
            and pm.create_by like concat('%',#{createBy},'%')
        </if>
        <if test="id != null and id != ''">
            and pm.id = #{id}
        </if>
        <if test="correspondId != null and correspondId != ''">
            and pm.correspond_id = #{correspondId}
        </if>
        <if test="takerId != null and takerId != ''">
            and pm.taker_id like concat('%',#{takerId},'%')
        </if>
        <if test="pickStartTime != null and pickStartTime != ''">
            and pm.pick_time BETWEEN #{pickStartTime} and #{pickEndTime}
        </if>
        <if test="customerId != null and customerId != ''">
            and ia.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="status != null">
            and pms.status = #{status}
        </if>
        GROUP BY pms.id
        order by kdc.number,pm.create_time desc
    </select>


    <update id="printAdd">
        UPDATE pick_manager_ship
        SET print_num = print_num + 1
        ,print_time = now(),status = 1
        WHERE
        pick_id IN
        <foreach item="item" collection="ids" separator="," open="(" close=")" index="">
            #{item}
        </foreach>
    </update>

    <resultMap id="retreatMapDetail" type="com.zfire.mall.manager.bean.ship.RetreatDocumentDetail">
        <id column="id" jdbcType="VARCHAR" property="id"/>
        <result column="retreat_time" jdbcType="TIMESTAMP" property="retreatTime"/>
        <result column="customer_number" jdbcType="VARCHAR" property="customerNumber"/>
        <result column="customer_name" jdbcType="VARCHAR" property="customerName"/>
        <result column="remark" jdbcType="VARCHAR" property="remark"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="create_by" jdbcType="VARCHAR" property="createBy"/>
        <result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
        <collection property="retreatDocumentOrder" ofType="com.zfire.mall.manager.bean.ship.RetreatDocumentOrderDetail"
                    select="RetreatOrder" column="id">
            <result column="status" jdbcType="TINYINT" property="status"/>
            <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="num" jdbcType="INTEGER" property="num"/>
            <result column="correspond_id" jdbcType="VARCHAR" property="correspondId"/>
            <result column="correspond_name" jdbcType="VARCHAR" property="correspondName"/>
        </collection>
    </resultMap>


    <select id="detail" resultMap="retreatMapDetail">
        SELECT sd.*
        FROM retreat_document sd
        where sd.id = #{id}
        order by sd.create_time desc
    </select>

    <select id="RetreatOrder" resultType="com.zfire.mall.manager.bean.ship.RetreatDocumentOrderDetail">
        SELECT
            rdo.*
        FROM
            retreat_document_order rdo

        where rdo.retreat_id =#{id}
    </select>

    <resultMap id="BaseResultMapDetail" type="com.zfire.mall.manager.bean.ship.ShipDocumentDetail">
        <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="main_id" jdbcType="VARCHAR" property="mainId"/>
        <result column="main_name" jdbcType="VARCHAR" property="mainName"/>
        <result column="order_type" jdbcType="TINYINT" property="orderType"/>
        <result column="tax" jdbcType="DECIMAL" property="tax"/>
        <result column="bill_number" jdbcType="VARCHAR" property="billNumber"/>
        <result column="currency" jdbcType="VARCHAR" property="currency"/>
        <result column="printing_sheet" jdbcType="VARCHAR" property="printingSheet"/>
        <result column="file_number" jdbcType="VARCHAR" property="fileNumber"/>
        <result column="out_time" jdbcType="TIMESTAMP" property="outTime"/>
        <result column="bill_time" jdbcType="TIMESTAMP" property="billTime"/>
        <collection property="invoicePickBeans"
                    ofType="com.zfire.mall.manager.bean.invoice.InvoiceApplicationOrderBody" 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="pjxh1_Text" jdbcType="VARCHAR" property="pjxh1Text"/>
        </collection>
    </resultMap>



    <select id="detailShip" resultMap="BaseResultMapDetail">
        SELECT sd.*
        FROM ship_document sd
        WHERE 1 = 1
        <if test="id != null and id != ''">
            and sd.id = #{id}
        </if>
    </select>

    <select id="order" resultType="com.zfire.mall.manager.bean.invoice.InvoiceApplicationOrderBody">
        SELECT
        iao.*,
               ia.sales_id,
               ia.sales_id as sales_order_id,
               kdm.pjxh1_Text
        FROM
        invoice_application_order iao
        left join invoice_application ia on iao.invoice_id = ia.id
        left join king_dee_material kdm on iao.material_id = kdm.id
        WHERE
        iao.invoice_id = #{id}
    </select>


    <select id="listProject" resultType="com.zfire.mall.manager.bean.ship.ShipDocumentProjectBean">
        SELECT
        ia.*,
        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 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.header_remark,
        kdm.category_name,
        kdm.material_group_name,
        iao.k3_service_id,
        iao.k3_service_name,
        iao.real_material_id,
        iao.real_material_name,
        iao.real_material_number,
        iao.real_material_old_number,
        iao.real_specification,
        IF( so.examine_status='OK' ,1,0) as salesStatus
        FROM ship_document sd
        JOIN invoice_application_order iao ON sd.id = iao.invoice_id
        JOIN invoice_application ia on iao.invoice_id = ia.id
        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
        join sales_order so on ia.id = so.order_no
        WHERE 1 = 1

        <if test="id != null and id != ''">
            and sd.id=#{id}
        </if>

        <if test="mainOrderId != null and mainOrderId != ''">
            and ia.engin_order_no like concat('%',#{mainOrderId},'%')
        </if>
            <if test="refRegionWork != null and refRegionWork != ''">
            and ia.ref_region_work like concat('%',#{refRegionWork},'%')
        </if>
        <if test="customerName != null and customerName != ''">
            and sd.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="customerNumber != null and customerNumber != ''">
            and sd.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="materialName != null and materialName != ''">
            and iao.material_name like concat('%',#{materialName},'%')
        </if>
        <if test="specification != null and specification != ''">
            and iao.specification like concat('%',#{specification},'%')
        </if>
        <if test="orderType != null and orderType == 2">
            and iao.order_type in ('HOME','PERMU_HOME','REQUISITION_HOME')
        </if>
        <if test="orderType != null and orderType ==3">
            and iao.order_type in ('TRADE','PERMU_TRADE','REQUISITION_TRADE')
        </if>
        <if test="startTime != null and startTime != ''">
            and sd.order_time BETWEEN #{startTime} and #{endTime}
        </if>
        <if test="customerId != null and customerId != ''">
            and sd.customer_id = #{customerId}
        </if>
          <if test="serviceId != null and serviceId != ''">
            and iao.service_id = #{serviceId}
        </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="enginOrderNo != null and enginOrderNo != ''">
            and ia.engin_order_no = #{enginOrderNo}
        </if>
        <if test="refEnginRecordNo != null and refEnginRecordNo != ''">
            and ia.ref_engin_record_no = #{refEnginRecordNo}
        </if>
          <if test="categoryName != null and categoryName != ''">
            and kdm.category_name = #{categoryName}
        </if>
        order by sd.create_time desc
    </select>


    <resultMap id="BaseResultProjectMap" type="com.zfire.mall.manager.bean.ship.ShipDocumentProjectDetail">
        <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="main_id" jdbcType="VARCHAR" property="mainId"/>
        <result column="main_name" jdbcType="VARCHAR" property="mainName"/>
        <result column="tax" jdbcType="DECIMAL" property="tax"/>
        <result column="bill_number" jdbcType="VARCHAR" property="billNumber"/>
        <result column="currency" jdbcType="VARCHAR" property="currency"/>
        <result column="printing_sheet" jdbcType="VARCHAR" property="printingSheet"/>
        <result column="file_number" jdbcType="VARCHAR" property="fileNumber"/>
        <result column="out_time" jdbcType="TIMESTAMP" property="outTime"/>
        <result column="bill_time" jdbcType="TIMESTAMP" property="billTime"/>
        <collection property="invoicePickBeans"
                    ofType="com.zfire.mall.manager.bean.invoice.InvoiceApplicationOrderProjectBody"
                    select="orderProject" 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"/>
        </collection>
    </resultMap>

    <select id="detailProject" resultMap="BaseResultProjectMap">
        SELECT sd.*
        FROM ship_document sd
        WHERE 1 = 1
        <if test="id != null and id != ''">
            and sd.id = #{id}
        </if>
    </select>

    <select id="orderProject" resultType="com.zfire.mall.manager.bean.invoice.InvoiceApplicationOrderProjectBody">
        SELECT
        *
        FROM
        invoice_application_order iao
        WHERE
        iao.invoice_id = #{id}
    </select>

    <select id="listInvoice" resultType="com.zfire.mall.manager.bean.ship.RetreatInvoiceBean">
        SELECT
        sd.*,
        iao.id as invoiceOrderId,
        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,
        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,
        case when (SELECT count(1) FROM pick_manager_ship pms where iao.id = pms.invoice_order_id) > 0 then 1 ELSE 0 END as pickStatus
        FROM ship_document sd
        LEFT JOIN invoice_application_order iao ON sd.id = iao.invoice_id
        WHERE 1 = 1
        and sd.id =#{id}
        <if test="customerId != null and customerId != ''">
            and sd.customer_id =#{customerId}
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and iao.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <resultMap id="PickManagerMap" type="com.zfire.mall.manager.bean.pick.PickManagerDetailBean">
        <id column="id" jdbcType="VARCHAR" property="id"/>
        <result column="pick_time" jdbcType="VARCHAR" property="pickTime"/>
        <result column="pick_status" jdbcType="VARCHAR" property="pickStatus"/>
        <result column="pick_type" jdbcType="TINYINT" property="pickType"/>
        <result column="pick_logistics" jdbcType="VARCHAR" property="pickLogistics"/>
        <result column="logistics_id" jdbcType="VARCHAR" property="logisticsId"/>
        <result column="remark" jdbcType="VARCHAR" property="remark"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="create_by" jdbcType="VARCHAR" property="createBy"/>
        <result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
        <result column="pick_car" jdbcType="VARCHAR" property="pickCar"/>
        <result column="taker_name" jdbcType="VARCHAR" property="takerName"/>
        <result column="taker_id" jdbcType="VARCHAR" property="takerId"/>
        <result column="car_brand" jdbcType="VARCHAR" property="carBrand"/>
        <result column="taker_car_id" jdbcType="VARCHAR" property="takerCarId"/>
        <result column="information_key" jdbcType="VARCHAR" property="informationKey"/>
        <result column="correspond_id" jdbcType="VARCHAR" property="correspondId"/>
        <result column="correspond_name" jdbcType="VARCHAR" property="correspondName"/>
        <collection property="invoicePickBeans" ofType="com.zfire.mall.manager.bean.invoice.InvoicePickBean" select="orderPick" column="id">
            <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="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="invoiceOrderId" jdbcType="VARCHAR" property="invoiceOrderId"/>
            <result column="pjxh1_Text" jdbcType="VARCHAR" property="pjxh1Text"/>
            <result column="address" jdbcType="VARCHAR" property="address"/>
            <result column="receiving_name" jdbcType="VARCHAR" property="receivingName"/>
            <result column="phone" jdbcType="VARCHAR" property="phone"/>
        </collection>
    </resultMap>

    <select id="detailPick" resultMap="PickManagerMap">
        SELECT
            pm.*
        FROM
            pick_manager pm
        WHERE
            1 =1
        and pm.id = #{id}
    </select>

    <select id="orderPick" resultType="com.zfire.mall.manager.bean.invoice.InvoicePickBean">
        SELECT
        sd.*,
        pms.pick_id,
        pms.print_num,
        pms.status,
        pms.print_time,
        pms.pick_actual_time,
        pms.address,
        pms.receiving_name,
        pms.phone,
        pms.warehouse_flag,
        pms.customer_order_id,
        iao.id as invoiceOrderId,
        iao.main_order_id,
        iao.order_id,
        iao.invoice_id,
        iao.invoice_num,
        iao.approval_number,
        iao.refundable_qty+iao.retired_qty 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,
        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,
				kdm.pjxh1_Text,
						sd.sales_order_id
        FROM
        invoice_application sd
         JOIN invoice_application_order iao ON sd.id = iao.invoice_id
         JOIN pick_manager_ship pms on pms.invoice_order_id = iao.id
         JOIN king_dee_material kdm on kdm.id = iao.material_id
        WHERE
        1 = 1
        and pms.pick_id = #{id}
        GROUP BY iao.id
    </select>
    <select id="listCustomer" resultType="com.zfire.mall.manager.plus.entity.KingDeeCustomer">
        SELECT
            kdc.*
        FROM
            taker_record tr
                LEFT JOIN king_dee_customer kdc ON tr.customer_id = kdc.id
        where 1=1
         <if test="phone != null and phone != ''">
             and tr.phone =#{phone}
         </if>
          <if test="keyword != null and keyword != ''">
             and kdc.name like concat('%',#{keyword},'%')
         </if>
          <if test="region != null and region != ''">
             and kdc.number like concat('%',#{region},'%')
         </if>


    </select>
    <select id="listV2" resultType="com.zfire.mall.manager.bean.pick.PickManagerBeanVO">
        SELECT
        pm.*,
        pms.goods_order_id,
        pms.pick_id,
        pms.invoice_order_id,
        pms.print_num,
        pms.status,
        pms.print_time,
        pms.pick_actual_time,
        pms.material_id,
        pms.material_old_number,
        pms.material_code,
        pms.material_name,
        pms.receiving_name as pmReceivingName,
        pms.phone as pmPhone,
        pms.address as pmAddress,
        pms.main_order_id,
        pms.order_id,
        pms.invoice_id,
        pms.approval_number,
        pms.invoice_num,
        pms.refundable_qty+iao.retired_qty as refundable_qty,
        pms.order_type,
        pms.sale_type_id,
        pms.sale_type_code,
        pms.sale_type_name,
        pms.the_time,
        pms.specification,
        pms.unit,
        pms.price,
        pms.pay_amount,
        pms.tax,
        pms.qty,
        pms.after_tax_price,
        pms.no_total_amount,
        pms.pay_rebate_amount,
        pms.total_disc_amount,
        pms.k3_category_id as category_id,
        pms.k3_category_number as category_number,
        pms.k3_category_name as  category_name,
        pms.sales_order_id,
        pms.ref_engin_record_no,
        pms.engin_order_no,
        pms.customer_order_id,
        iao.invoice_id
        FROM
        pick_manager_ship pms
        JOIN pick_manager pm ON pm.id = pms.pick_id
        join customer_product_type_rela cptr on pms.customer_wallet_id = cptr.customer_wallet_id
        join invoice_application_order iao on pms.invoice_order_id = iao.id
        ${ex.query}

        <if test="correspondIds != null and correspondIds.size > 0">
            and pm.correspond_id in
            <foreach item="item" index="index" collection="correspondIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

        <if test="customerId != null and customerId != ''">
            and pm.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="ex.orderBy == null or ex.orderBy ==''">
            order by pms.id desc
        </if>
        ${ex.orderBy}
    </select>

    <update id="printAddInvoice">
        UPDATE invoice_application_order iao
        JOIN pick_manager_ship pms ON iao.id = pms.invoice_order_id
        set iao.print_status = pms.status
        WHERE
        pms.pick_id IN
        <foreach item="item" collection="ids" separator="," open="(" close=")" index="">
            #{item}
        </foreach>
    </update>
</mapper>