<?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.engin.order.EnginOrderReserveMapper">

    <resultMap id="listResultMap" type="com.zfire.mall.manager.bean.engin.order.EnginOrderBean">
        <result column="engin_order_id" jdbcType="VARCHAR" property="enginOrderId" />
        <result column="admin_company_id" jdbcType="VARCHAR" property="adminCompanyId" />
        <result column="customer_id" jdbcType="VARCHAR" property="customerId" />
        <result column="customer_number" jdbcType="VARCHAR" property="customerNumber" />
        <result column="customer_name" jdbcType="VARCHAR" property="customerName" />
        <result column="engin_order_type" jdbcType="VARCHAR" property="enginOrderType" />
        <result column="engin_order_no" jdbcType="VARCHAR" property="enginOrderNo" />
        <result column="order_date" jdbcType="TIMESTAMP" property="orderDate" />
        <result column="engin_user_name" jdbcType="VARCHAR" property="loginUserId" />
        <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="ref_engin_record_no" jdbcType="VARCHAR" property="refEnginRecordNo" />
        <result column="ref_project_category" jdbcType="VARCHAR" property="refProjectCategory" />
        <result column="ref_project_name" jdbcType="VARCHAR" property="refProjectName" />
        <result column="ref_project_type" jdbcType="VARCHAR" property="refProjectType" />
        <result column="ref_project_note" jdbcType="VARCHAR" property="refProjectNote" />
        <result column="ref_use_unit" jdbcType="VARCHAR" property="refUseUnit" />
        <result column="ref_install_address" jdbcType="VARCHAR" property="refInstallAddress" />
        <result column="ref_project_no" jdbcType="VARCHAR" property="refProjectNo" />
        <result column="ref_factory_no" jdbcType="VARCHAR" property="refFactoryNo" />
        <result column="ref_promise_status" jdbcType="VARCHAR" property="refPromiseStatus" />
        <result column="ref_trade_category" jdbcType="VARCHAR" property="refTradeCategory" />
        <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="ref_declare_no" jdbcType="VARCHAR" property="refDeclareNo" />
        <result column="ref_power_category" jdbcType="VARCHAR" property="refPowerCategory" />
        <result column="ref_machine_type" jdbcType="VARCHAR" property="refMachineType" />
        <result column="ref_region_work" jdbcType="VARCHAR" property="refRegionWork" />
        <result column="ref_buy_unit_name" jdbcType="VARCHAR" property="refBuyUnitName" />
        <result column="ref_buy_unit_call_men" jdbcType="VARCHAR" property="refBuyUnitCallMen" />
        <result column="ref_buy_unit_tel" jdbcType="VARCHAR" property="refBuyUnitTel" />
        <result column="ref_buy_unit_phone" jdbcType="VARCHAR" property="refBuyUnitPhone" />
        <result column="ref_promise_provide" jdbcType="VARCHAR" property="refPromiseProvide" />
        <result column="ref_other_annex_result" jdbcType="VARCHAR" property="refOtherAnnexResult" />
        <result column="ref_other_annex_idea" jdbcType="VARCHAR" property="refOtherAnnexIdea" />
        <result column="ref_data_delay_result" jdbcType="VARCHAR" property="refDataDelayResult" />
        <result column="ref_data_delay_idea" jdbcType="VARCHAR" property="refDataDelayIdea" />
        <result column="ref_to_retail_result" jdbcType="VARCHAR" property="refToRetailResult" />
        <result column="ref_to_retail_idea" jdbcType="VARCHAR" property="refToRetailIdea" />
        <result column="ref_contract_result" jdbcType="VARCHAR" property="refContractResult" />
        <result column="ref_contract_idea" jdbcType="VARCHAR" property="refContractIdea" />
        <result column="ref_bills_expire_date" jdbcType="DATE" property="refBillsExpireDate" />
        <result column="ref_count" jdbcType="INTEGER" property="refCount" />
        <result column="service_id" jdbcType="VARCHAR" property="serviceId" />
        <result column="service_name" jdbcType="VARCHAR" property="serviceName" />
        <result column="file_no" jdbcType="VARCHAR" property="fileNo" />
        <result column="is_plan_order" jdbcType="BOOLEAN" property="isPlanOrder" />
        <result column="remark" jdbcType="VARCHAR" property="remark" />
        <result column="ge_li_note" jdbcType="VARCHAR" property="geLiNote" />
        <result column="ge_li_iner_note" jdbcType="VARCHAR" property="geLiInerNote" />
        <result column="take_deposit" jdbcType="BOOLEAN" property="takeDeposit" />
        <result column="examine_note" jdbcType="VARCHAR" property="examineNote" />
        <result column="examine_status" jdbcType="VARCHAR" property="examineStatus" />
        <result column="direct_transfer_status" jdbcType="BOOLEAN" property="directTransferStatus" />
        <result column="create_by" jdbcType="VARCHAR" property="createBy" />
        <result column="create_name" jdbcType="VARCHAR" property="createName" />
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
        <result column="update_by" jdbcType="VARCHAR" property="updateBy" />
        <result column="update_name" jdbcType="VARCHAR" property="updateName" />
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
        <result column="submit_by" jdbcType="VARCHAR" property="submitBy" />
        <result column="submit_name" jdbcType="VARCHAR" property="submitName" />
        <result column="submit_time" jdbcType="TIMESTAMP" property="submitTime" />
        <result column="confirm_by" jdbcType="VARCHAR" property="confirmBy" />
        <result column="confirm_name" jdbcType="VARCHAR" property="confirmName" />
        <result column="confirm_time" jdbcType="TIMESTAMP" property="confirmTime" />
        <result column="abandon_by" jdbcType="VARCHAR" property="abandonBy" />
        <result column="abandon_name" jdbcType="VARCHAR" property="abandonName" />
        <result column="abandon_time" jdbcType="TIMESTAMP" property="abandonTime" />
        <result column="close_by" jdbcType="VARCHAR" property="closeBy" />
        <result column="close_name" jdbcType="VARCHAR" property="closeName" />
        <result column="close_time" jdbcType="TIMESTAMP" property="closeTime" />
        <result column="end_direct_by" jdbcType="VARCHAR" property="endDirectBy" />
        <result column="end_direct_name" jdbcType="VARCHAR" property="endDirectName" />
        <result column="end_direct_time" jdbcType="TIMESTAMP" property="endDirectTime" />
        <result column="king_dee_sync" jdbcType="VARCHAR" property="kingDeeSync" />
        <result column="king_dee_id" jdbcType="VARCHAR" property="kingDeeId" />
        <result column="correspond_id" jdbcType="VARCHAR" property="correspondId" />
        <result column="correspond_name" jdbcType="VARCHAR" property="correspondName" />
        <collection property="items" resultMap="itemResultList" />
    </resultMap>

    <resultMap id="itemResultList" type="com.zfire.mall.manager.bean.engin.order.EnginOrderItemBean">
        <result column="id" jdbcType="VARCHAR" property="id" />
        <result column="parent_id" jdbcType="VARCHAR" property="parentId" />
        <result column="use_ref_count" jdbcType="INTEGER" property="useRefCount" />
        <result column="item_sale_type_id" jdbcType="VARCHAR" property="saleTypeId" />
        <result column="item_sale_type_code" jdbcType="VARCHAR" property="saleTypeCode" />
        <result column="item_sale_type_name" jdbcType="VARCHAR" property="saleTypeName" />
        <result column="material_id" jdbcType="VARCHAR" property="materialId" />
        <result column="material_number" jdbcType="VARCHAR" property="materialNumber" />
        <result column="material_name" jdbcType="VARCHAR" property="materialName" />
        <result column="material_old_number" jdbcType="VARCHAR" property="materialOldNumber" />
        <result column="specification" jdbcType="VARCHAR" property="specification" />
        <result column="real_material_id" jdbcType="VARCHAR" property="realMaterialId" />
        <result column="real_material_number" jdbcType="VARCHAR" property="realMaterialNumber" />
        <result column="real_material_name" jdbcType="VARCHAR" property="realMaterialName" />
        <result column="real_material_old_number" jdbcType="VARCHAR" property="realMaterialOldNumber" />
        <result column="real_specification" jdbcType="VARCHAR" property="realSpecification" />
        <result column="unit" jdbcType="VARCHAR" property="unit" />
        <result column="engin_price" jdbcType="DECIMAL" property="enginPrice" />
        <result column="engin_totality" jdbcType="DECIMAL" property="enginTotality" />
        <result column="engin_num" jdbcType="INTEGER" property="enginNum" />
        <result column="price" jdbcType="DECIMAL" property="price" />
        <result column="qty" jdbcType="INTEGER" property="qty" />
        <result column="disc_amount" jdbcType="DECIMAL" property="discAmount" />
        <result column="total_amount" jdbcType="DECIMAL" property="totalAmount" />
        <result column="rebate_rate" jdbcType="DECIMAL" property="rebateRate" />
        <result column="rebate_amount" jdbcType="DECIMAL" property="rebateAmount" />
        <result column="deposit_rate" jdbcType="DECIMAL" property="depositRate" />
        <result column="customer_wallet_id" jdbcType="VARCHAR" property="customerWalletId" />
        <result column="customer_wallet_name" jdbcType="VARCHAR" property="customerWalletName" />
        <result column="customer_wallet_id2" jdbcType="VARCHAR" property="customerWalletId2" />
        <result column="customer_wallet_name2" jdbcType="VARCHAR" property="customerWalletName2" />
        <result column="service_id" jdbcType="VARCHAR" property="serviceId" />
        <result column="service_name" jdbcType="VARCHAR" property="serviceName" />
        <result column="is_direct_transfer" jdbcType="BOOLEAN" property="isDirectTransfer" />
        <result column="direct_transfer_qty" jdbcType="INTEGER" property="directTransferQty" />
        <result column="has_send_qty" jdbcType="INTEGER" property="hasSendQty" />
        <result column="single_rebate_amount" jdbcType="DECIMAL" property="singleRebateAmount" />
        <result column="single_disc_amount" jdbcType="DECIMAL" property="singleDiscAmount" />
        <result column="single_pay_price" jdbcType="DECIMAL" property="singlePayPrice" />
        <result column="single_deposit_amount" jdbcType="DECIMAL" property="singleDepositAmount" />
        <result column="pay_amount" jdbcType="DECIMAL" property="payAmount" />
        <result column="pay_rebate_amount" jdbcType="DECIMAL" property="payRebateAmount" />
        <result column="pay_cash_amount" jdbcType="DECIMAL" property="payCashAmount" />
        <result column="pay_credit_amount" jdbcType="DECIMAL" property="payCreditAmount" />
        <result column="total_deposit_amount" jdbcType="DECIMAL" property="totalDepositAmount" />
        <result column="old_qty" jdbcType="INTEGER" property="oldQty" />
        <result column="old_pay_amount" jdbcType="DECIMAL" property="oldPayAmount" />
        <result column="old_rebate_amount" jdbcType="DECIMAL" property="oldRebateAmount" />
        <result column="tax" jdbcType="DECIMAL" property="tax" />
        <result column="item_remark" jdbcType="VARCHAR" property="remark" />
        <result column="refundable_qty" jdbcType="INTEGER" property="refundableQty" />
        <result column="retired_qty" jdbcType="INTEGER" property="retiredQty" />
        <result column="refundable_qty" jdbcType="INTEGER" property="refundableQty" />
        <result column="retired_qty" jdbcType="INTEGER" property="retiredQty" />
        <result column="refund_product_qty" jdbcType="INTEGER" property="refundProductQty" />
        <result column="refund_product_amount" jdbcType="DECIMAL" property="refundProductAmount" />
        <result column="king_dee_entry_id" jdbcType="VARCHAR" property="kingDeeEntryId" />
        <result column="sales_qty" jdbcType="INTEGER" property="salesQty" />
        <result column="sales_amount" jdbcType="DECIMAL" property="salesAmount" />
    </resultMap>

    <select id="listPage" resultType="com.zfire.mall.manager.bean.engin.order.EnginOrderListBean">
        SELECT
            a.engin_order_id,
            a.admin_company_id,
            a.customer_id,
            a.customer_number,
            a.customer_name,
            a.service_id,
            a.service_name,
            a.sale_type_id,
            a.sale_type_code,
            a.sale_type_name,
            a.engin_user_name,
            a.engin_order_type,
            a.engin_order_no,
            a.order_date,
            a.ref_engin_record_no,
            a.ref_project_category,
            a.ref_project_name,
            a.ref_project_type,
            a.ref_project_note,
            a.ref_use_unit,
            a.ref_install_address,
            a.ref_project_no,
            a.ref_factory_no,
            a.ref_promise_status,
            a.ref_trade_category,
            a.ref_linkman,
            a.ref_phone,
            a.ref_tel,
            a.ref_declare_no,
            a.ref_power_category,
            a.ref_machine_type,
            a.ref_region_work,
            a.ref_buy_unit_name,
            a.ref_promise_provide,
            a.ref_other_annex_idea,
            a.ref_count,
            a.file_no,
            a.take_deposit,
            a.direct_transfer_status,
            a.advance_status,
            a.ge_li_note,
            a.ge_li_iner_note,
            a.remark,
            a.examine_note,
            a.examine_status,
            a.create_by,
            a.create_name,
            a.create_time,
            a.update_by,
            a.update_name,
            a.update_time,
            a.submit_by,
            a.submit_name,
            a.submit_time,
            a.confirm_by,
            a.confirm_name,
            a.confirm_time,
            a.abandon_by,
            a.abandon_name,
            a.abandon_time,
            a.close_by,
            a.close_name,
            a.close_time,
            a.end_direct_by,
            a.end_direct_name,
            a.end_direct_time,
            a.king_dee_sync,
            a.king_dee_id,
            a.correspond_id,
            a.correspond_name,
            b.id,
            b.parent_id,
            b.use_ref_count,
            b.sale_type_id AS itemSaleTypeId,
            b.sale_type_code AS itemSaleTypeCode,
            b.sale_type_name AS itemSaleTypeName,
            b.material_id,
            b.material_number,
            b.material_old_number,
            b.material_name,
            b.specification,
            b.unit,
            b.engin_price,
            b.engin_num,
            b.engin_totality,
            b.qty,
            b.price,
            b.total_amount,
            b.disc_amount,
            b.rebate_rate,
            b.rebate_amount,
            b.deposit_rate,
            b.customer_wallet_id,
            b.customer_wallet_name,
            b.customer_wallet_id2,
            b.customer_wallet_name2,
            b.service_id AS itemServiceId,
            b.service_name AS itemServiceName,
            b.is_direct_transfer,
            b.direct_transfer_qty,
            b.has_send_qty,
            b.single_rebate_amount,
            b.single_disc_amount,
            b.single_pay_price,
            b.single_deposit_amount,
            b.pay_amount,
            b.pay_rebate_amount,
            b.pay_cash_amount,
            b.pay_credit_amount,
            b.total_deposit_amount,
            b.old_qty,
            b.old_pay_amount,
            b.old_rebate_amount,
            b.tax,
            b.real_material_name,
            b.real_material_number,
            b.real_specification,
            b.real_material_old_number,
            b.remark AS item_remark,
            b.refund_product_qty,
            b.refund_product_amount,
            b.k3_category_name,
        (b.refundable_qty - b.withhold ) as refundable_qty,
               <!--
                    (b.refundable_qty - (SELECT IFNULL(SUM(iao.refundable_qty),0) FROM invoice_application ia
                    LEFT JOIN invoice_application_order iao on ia.id = iao.invoice_id
                    where ia.examine_status	in ('SAVE','WAIT')
                    and iao.order_id =b.id and ia.type != 2)) as refundable_qty,
                -->
            b.retired_qty,
            kdm.material_group_name
        FROM
            engin_order a JOIN engin_order_item b ON a.engin_order_id = b.parent_id
        JOIN king_dee_material kdm on b.material_id = kdm.id
        <where>
            <if test="customerId != null and customerId != ''">
                AND a.customer_id = #{customerId}
            </if>
            <if test="customerWalletId != null and customerWalletId != ''">
                AND b.wallet_id1 = #{customerWalletId}
            </if>
            <if test="customerKeyword != null and customerKeyword != ''">
                AND (a.customer_name LIKE CONCAT('%', #{customerKeyword},'%') OR a.customer_number LIKE CONCAT('%', #{customerKeyword},'%'))
            </if>
            <if test="enginOrderType != null and enginOrderType != ''">
                AND a.engin_order_type = #{enginOrderType}
            </if>
<!--            <if test="enginOrderNo != null and enginOrderNo != ''">-->
<!--                AND a.engin_order_no = #{enginOrderNo}-->
<!--            </if>-->
            <if test="enginOrderNo != null and enginOrderNo.size == 1">
                AND a.engin_order_no LIKE CONCAT('%',#{enginOrderNo[0]},'%')
            </if>
            <if test="enginOrderNo != null and enginOrderNo.size > 1">
                AND a.engin_order_no in
                <foreach item="item" index="index" collection="enginOrderNo" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="refProjectNo != null and refProjectNo != ''">
                AND a.ref_project_no LIKE CONCAT('%', #{refProjectNo},'%')
            </if>
            <if test="refEnginRecordNo != null and refEnginRecordNo != ''">
                AND a.ref_engin_record_no LIKE CONCAT('%', #{refEnginRecordNo},'%')
            </if>

            <if test="refProjectCategory != null and refProjectCategory != ''">
                AND a.ref_project_category LIKE CONCAT('%', #{refProjectCategory},'%')
            </if>
            <if test="refProjectName != null and refProjectName != ''">
                AND a.ref_project_name LIKE CONCAT('%', #{refProjectName},'%')
            </if>
            <if test="refUseUnit != null and refUseUnit != ''">
                AND a.ref_use_unit LIKE CONCAT('%', #{refUseUnit},'%')
            </if>
            <if test="refTradeCategory != null and refTradeCategory != ''">
                AND a.ref_trade_category LIKE CONCAT('%', #{refTradeCategory},'%')
            </if>
            <if test="startCreateTime != null and startCreateTime != ''">
                AND a.create_time <![CDATA[ >= ]]> #{startCreateTime}
            </if>
            <if test="endCreateTime != null and endCreateTime != ''">
                AND a.create_time <![CDATA[ <= ]]> #{endCreateTime}
            </if>
            <if test="startOrderTime != null and startOrderTime != ''">
                AND a.order_date <![CDATA[ >= ]]> #{startOrderTime}
            </if>
            <if test="endOrderTime != null and endOrderTime != ''">
                AND a.order_date <![CDATA[ <= ]]> #{endOrderTime}
            </if>
            <if test="saleTypeId != null and saleTypeId != ''">
                AND b.sale_type_id = #{saleTypeId}
            </if>
            <if test="createName != null and createName != ''">
                AND a.create_name LIKE CONCAT('%', #{createName},'%')
            </if>
            <if test="confirmName != null and confirmName != ''">
                AND a.confirm_name LIKE CONCAT('%', #{confirmName},'%')
            </if>
            <if test="serviceId != null and serviceId != ''">
                AND a.service_id = #{serviceId}
            </if>
            <if test="specification != null and specification != ''">
                AND b.specification LIKE CONCAT('%', #{specification},'%')
            </if>
            <if test="materialNumber != null and materialNumber != ''">
                AND b.material_number LIKE CONCAT('%', #{materialNumber},'%')
            </if>
            <if test="materialName != null and materialName != ''">
                AND b.material_name LIKE CONCAT('%', #{materialName},'%')
            </if>
            <if test="materialOldNumber != null and materialOldNumber != ''">
                AND b.material_old_number LIKE CONCAT('%', #{materialOldNumber},'%')
            </if>
            <if test="refRegionWork != null and refRegionWork != ''">
                AND a.ref_region_work LIKE CONCAT('%', #{refRegionWork},'%')
            </if>
            <if test="refBuyUnitName != null and refBuyUnitName != ''">
                AND a.ref_buy_unit_name LIKE CONCAT('%', #{refBuyUnitName},'%')
            </if>
            <if test="remark != null and remark != ''">
                AND a.remark LIKE CONCAT('%', #{remark},'%')
            </if>
            <if test="geLiInerNote != null and geLiInerNote != ''">
                AND a.ge_li_iner_note LIKE CONCAT('%', #{geLiInerNote},'%')
            </if>
            <if test="refFactoryNo != null and refFactoryNo != ''">
                AND a.ref_factory_no LIKE CONCAT('%', #{refFactoryNo},'%')
            </if>
            <if test="refPromiseProvide != null and refPromiseProvide != ''">
                AND a.ref_promise_provide LIKE CONCAT('%', #{refPromiseProvide},'%')
            </if>
            <if test="isDirectTransfer != null">
                AND b.is_direct_transfer = #{isDirectTransfer}
            </if>
            <if test="examineStatus != null and examineStatus.size > 0">
                AND a.examine_status in
                <foreach item="item" index="index" collection="examineStatus" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="adminCompanyIds != null and adminCompanyIds.size > 0">
                AND a.admin_company_id in
                <foreach item="item" index="index" collection="adminCompanyIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="customerId == '' and serviceIds != null and serviceIds.size > 0">
                and b.service_id in
                <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="customerId == '' and k3CategoryIds != null and k3CategoryIds.size > 0">
                and b.k3_category_id in
                <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        <!--<if test="examineStatus != null and examineStatus.size == 1 and examineStatus.contains(&quot;WAIT&quot;)">-->
            <!--GROUP BY a.engin_order_id-->
        <!--</if>-->
        <!--<if test="examineStatus == null || examineStatus.size != 1 || !examineStatus.contains(&quot;WAIT&quot;)">-->
            <!--group by b.id-->
        <!--</if>-->
        ORDER BY a.create_time DESC, b.id DESC
    </select>
    <select id="detail" resultMap="listResultMap">
        SELECT
            a.*,
            b.id,
            b.parent_id,
            b.use_ref_count,
            b.sale_type_id AS item_sale_type_id,
            b.sale_type_code AS item_sale_type_code,
            b.sale_type_name AS item_sale_type_name,
            b.material_id,
            b.material_number,
            b.material_old_number,
            b.material_name,
            b.specification,
            b.real_material_id,
            b.real_material_number,
            b.real_material_old_number,
            b.real_material_name,
            b.real_specification,
            b.unit,
            b.engin_price,
            b.engin_num,
            b.engin_totality,
            b.qty,
            b.price,
            b.disc_amount,
            b.total_amount,
            b.rebate_rate,
            b.rebate_amount,
            b.deposit_rate,
            b.customer_wallet_id,
            b.customer_wallet_name,
            b.customer_wallet_id2,
            b.customer_wallet_name2,
            b.is_direct_transfer,
            b.direct_transfer_qty,
            b.has_send_qty,
            b.single_rebate_amount,
            b.single_disc_amount,
            b.single_pay_price,
            b.single_deposit_amount,
            b.pay_amount,
            b.pay_rebate_amount,
            b.pay_cash_amount,
            b.pay_credit_amount,
            b.total_deposit_amount,
            b.old_qty,
            b.old_pay_amount,
            b.tax,
            b.remark AS 'item_remark',
            b.refundable_qty,
            b.retired_qty,
            IFNULL(b.old_qty, 0) - ABS(IFNULL(b.refund_product_qty, 0)) - IFNULL(b.retired_qty, 0) AS sales_qty,
            (IFNULL(b.old_qty, 0) - ABS(IFNULL(b.refund_product_qty, 0)) - IFNULL(b.retired_qty, 0)) * b.price AS sales_amount
        FROM
            engin_order a JOIN engin_order_item b ON a.engin_order_id = b.parent_id
        <where>
            a.engin_order_id = #{id}
            <if test="customerId != null and customerId != ''">
                AND a.customer_id = #{customerId}
            </if>
            <if test="adminCompanyIds != null and adminCompanyIds.size > 0">
                AND a.admin_company_id in
                <foreach item="item" index="index" collection="adminCompanyIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="serviceIds != null and serviceIds.size > 0">
                and b.service_id in
                <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
    <select id="findHistoryItem" resultType="com.zfire.mall.manager.bean.engin.order.EnginInfoHistoryItemBean">
        SELECT
            b.sale_type_id,
            b.material_id,
            SUM(b.qty) AS qty
        FROM
            engin_order a,
            engin_order_item b
        WHERE
            a.engin_order_id = b.parent_id
            AND a.examine_status = 'OK'
            AND a.customer_id = #{customerId}
            AND a.ref_engin_record_no = #{recordNo}
        GROUP BY b.sale_type_id, b.material_id
    </select>
    <select id="queryPreOrder" resultType="com.zfire.mall.manager.bean.engin.order.EnginOrderListBean">
        SELECT
            a.*,
            b.id,
            b.parent_id,
            b.use_ref_count,
            b.sale_type_id,
            b.sale_type_code,
            b.sale_type_name,
            b.material_id,
            b.material_number,
            b.material_old_number,
            b.material_name,
            b.specification,
            b.unit,
            b.engin_price,
            b.engin_num,
            b.engin_totality,
            b.qty,
            b.price,
            b.total_amount,
            b.disc_amount,
            b.rebate_rate,
            b.rebate_amount,
            b.deposit_rate,
            b.customer_wallet_id,
            b.customer_wallet_name,
            b.customer_wallet_id2,
            b.customer_wallet_name2,
            b.is_direct_transfer,
            b.direct_transfer_qty,
            b.has_send_qty,
            b.single_rebate_amount,
            b.single_disc_amount,
            b.single_pay_price,
            b.single_deposit_amount,
            b.pay_amount,
            b.pay_rebate_amount,
            b.pay_cash_amount,
            b.pay_credit_amount,
            b.total_deposit_amount,
            b.tax,
            b.real_material_name,
            b.real_material_number,
            b.real_material_old_number,
            b.remark,
            b.service_id,
            b.service_name,
            b.refundable_qty,
            b.retired_qty
        FROM
            engin_order a JOIN engin_order_item b ON a.engin_order_id = b.parent_id
        WHERE
            a.ref_engin_record_no = #{ recordNo }
            AND a.examine_status = 'OK'
        ORDER BY a.confirm_time DESC
        LIMIT 1
    </select>
    <select id="queryWaitPayAmount" resultType="com.zfire.mall.manager.plus.entity.EnginOrderItem">
        SELECT
            b.customer_wallet_id,
            SUM(b.pay_amount) AS pay_amount
        FROM
            engin_order a, engin_order_item b
        WHERE
            a.engin_order_id = b.parent_id
            AND
            a.customer_id = #{customerId}
            AND
            a.examine_status = #{examineStatus}
            <if test="isPlanOrder != null">
                AND
                a.is_plan_order = #{isPlanOrder}
            </if>
            <if test="walllets != null and walllets.size > 0">
                and b.customer_wallet_id in
                <foreach item="item" index="index" collection="walllets" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            GROUP BY b.customer_wallet_id
    </select>
    <select id="countList" resultType="com.zfire.mall.manager.bean.PoySaleCountBean">
        SELECT
            SUM(b.qty) AS totalC,
            SUM(b.pay_amount) AS payAmountC,
            SUM(b.total_amount) AS amountC
        FROM
        engin_order a JOIN engin_order_item b ON a.engin_order_id = b.parent_id
        JOIN king_dee_material kdm on b.material_id = kdm.id
        LEFT JOIN invoice_application_order iao on iao.order_id = b.id and iao.refundable_qty > 0 and iao.invoice_examine_status in ('SAVE','WAIT')

        <where>
            <if test="customerId != null and customerId != ''">
                AND a.customer_id = #{customerId}
            </if>
            <if test="customerWalletId != null and customerWalletId != ''">
                AND b.wallet_id1 = #{customerWalletId}
            </if>
            <if test="customerKeyword != null and customerKeyword != ''">
                AND (a.customer_name LIKE CONCAT('%', #{customerKeyword},'%') OR a.customer_number LIKE CONCAT('%', #{customerKeyword},'%'))
            </if>
            <if test="enginOrderType != null and enginOrderType != ''">
                AND a.engin_order_type = #{enginOrderType}
            </if>
            <if test="enginOrderNo != null and enginOrderNo.size == 1">
                AND a.engin_order_no LIKE CONCAT('%',#{enginOrderNo[0]},'%')
            </if>
            <if test="enginOrderNo != null and enginOrderNo.size > 1">
                AND a.engin_order_no in
                <foreach item="item" index="index" collection="enginOrderNo" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="refProjectNo != null and refProjectNo != ''">
                AND a.ref_project_no LIKE CONCAT('%', #{refProjectNo},'%')
            </if>
            <if test="refEnginRecordNo != null and refEnginRecordNo != ''">
                AND a.ref_engin_record_no LIKE CONCAT('%', #{refEnginRecordNo},'%')
            </if>

            <if test="refProjectCategory != null and refProjectCategory != ''">
                AND a.ref_project_category LIKE CONCAT('%', #{refProjectCategory},'%')
            </if>
            <if test="refProjectName != null and refProjectName != ''">
                AND a.ref_project_name LIKE CONCAT('%', #{refProjectName},'%')
            </if>
            <if test="refUseUnit != null and refUseUnit != ''">
                AND a.ref_use_unit LIKE CONCAT('%', #{refUseUnit},'%')
            </if>
            <if test="refTradeCategory != null and refTradeCategory != ''">
                AND a.ref_trade_category LIKE CONCAT('%', #{refTradeCategory},'%')
            </if>
            <if test="startCreateTime != null and startCreateTime != ''">
                AND a.create_time <![CDATA[ >= ]]> #{startCreateTime}
            </if>
            <if test="endCreateTime != null and endCreateTime != ''">
                AND a.create_time <![CDATA[ <= ]]> #{endCreateTime}
            </if>
            <if test="startOrderTime != null and startOrderTime != ''">
                AND a.order_date <![CDATA[ >= ]]> #{startOrderTime}
            </if>
            <if test="endOrderTime != null and endOrderTime != ''">
                AND a.order_date <![CDATA[ <= ]]> #{endOrderTime}
            </if>
            <if test="saleTypeId != null and saleTypeId != ''">
                AND b.sale_type_id = #{saleTypeId}
            </if>
            <if test="createName != null and createName != ''">
                AND a.create_name LIKE CONCAT('%', #{createName},'%')
            </if>
            <if test="confirmName != null and confirmName != ''">
                AND a.confirm_name LIKE CONCAT('%', #{confirmName},'%')
            </if>
            <if test="serviceId != null and serviceId != ''">
                AND a.service_id = #{serviceId}
            </if>
            <if test="specification != null and specification != ''">
                AND b.specification LIKE CONCAT('%', #{specification},'%')
            </if>
            <if test="materialNumber != null and materialNumber != ''">
                AND b.material_number LIKE CONCAT('%', #{materialNumber},'%')
            </if>
            <if test="materialName != null and materialName != ''">
                AND b.material_name LIKE CONCAT('%', #{materialName},'%')
            </if>
            <if test="materialOldNumber != null and materialOldNumber != ''">
                AND b.material_old_number LIKE CONCAT('%', #{materialOldNumber},'%')
            </if>
            <if test="refRegionWork != null and refRegionWork != ''">
                AND a.ref_region_work LIKE CONCAT('%', #{refRegionWork},'%')
            </if>
            <if test="refBuyUnitName != null and refBuyUnitName != ''">
                AND a.ref_buy_unit_name LIKE CONCAT('%', #{refBuyUnitName},'%')
            </if>
            <if test="remark != null and remark != ''">
                AND a.remark LIKE CONCAT('%', #{remark},'%')
            </if>
            <if test="geLiInerNote != null and geLiInerNote != ''">
                AND a.ge_li_iner_note LIKE CONCAT('%', #{geLiInerNote},'%')
            </if>
            <if test="refFactoryNo != null and refFactoryNo != ''">
                AND a.ref_factory_no LIKE CONCAT('%', #{refFactoryNo},'%')
            </if>
            <if test="refPromiseProvide != null and refPromiseProvide != ''">
                AND a.ref_promise_provide LIKE CONCAT('%', #{refPromiseProvide},'%')
            </if>
            <if test="isDirectTransfer != null">
                AND b.is_direct_transfer = #{isDirectTransfer}
            </if>
            <if test="examineStatus != null and examineStatus.size > 0">
                AND a.examine_status in
                <foreach item="item" index="index" collection="examineStatus" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="adminCompanyIds != null and adminCompanyIds.size > 0">
                AND a.admin_company_id in
                <foreach item="item" index="index" collection="adminCompanyIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="serviceIds != null and serviceIds.size > 0">
                and b.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 b.k3_category_id in
                <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
    <select id="platformQueryList" resultType="com.zfire.mall.manager.bean.engin.order.EnginOrderListBean">
        SELECT
            b.id,
            a.ref_engin_record_no,
            a.engin_order_no,
            a.ref_use_unit,
            a.customer_name,
            b.specification,
            b.real_material_name,
            b.real_specification,
            b.qty,
            a.ref_install_address
        FROM
            engin_order a, engin_order_item b
        WHERE
            a.engin_order_id = b.parent_id
            <if test="adminUserId != null and adminUserId != '' and customerId == null">
                AND a.admin_user_id = #{adminUserId}
            </if>
            <if test="customerId != null and customerId != ''">
                AND a.customer_id = #{customerId}
            </if>
            <if test="recordNo != null and recordNo != ''">
                AND a.ref_engin_record_no LIKE CONCAT('%', #{recordNo},'%')
            </if>
            <if test="orderNo != null and orderNo != ''">
                AND a.engin_order_no LIKE CONCAT('%', #{orderNo},'%')
            </if>
            <if test="useUnit != null and useUnit != ''">
                AND a.ref_use_unit LIKE CONCAT('%', #{useUnit},'%')
            </if>
            <if test="specs != null and specs != ''">
                AND b.specification LIKE CONCAT('%', #{specs},'%')
            </if>
            <if test="realModel != null and realModel != ''">
                AND b.real_specification LIKE CONCAT('%', #{realModel},'%')
            </if>
            <if test="address != null and address != ''">
                AND ref_install_address LIKE CONCAT('%', #{address},'%')
            </if>
            <if test="orderType != null and orderType != ''">
                AND engin_order_type LIKE CONCAT('%', #{orderType},'%')
            </if>
            AND a.examine_status = 'OK'
            AND b.platform_ref = 0
        ORDER BY a.create_time DESC
    </select>

    <select id="v2HomeListPage" resultType="com.zfire.mall.manager.bean.engin.v2.order.HomeEnginOrderVO">
        SELECT
            a.engin_order_id,
            a.admin_company_id,
            a.customer_id,
            a.customer_number,
            a.customer_name,
            a.service_id,
            a.service_name,
            a.sale_type_id,
            a.sale_type_code,
            a.sale_type_name,
            a.engin_user_name,
            a.engin_order_type,
            a.engin_order_no,
            a.order_date,
            a.ref_engin_record_no,
            a.ref_project_category,
            a.ref_project_name,
            a.ref_project_type,
            a.ref_project_note,
            a.ref_use_unit,
            a.ref_install_address,
            a.ref_project_no,
            a.ref_factory_no,
            a.ref_promise_status,
            a.ref_trade_category,
            a.ref_linkman,
            a.ref_phone,
            a.ref_tel,
            a.ref_declare_no,
            a.ref_power_category,
            a.ref_machine_type,
            a.ref_region_work,
            a.ref_buy_unit_name,
            a.ref_buy_unit_call_men,
            a.ref_buy_unit_tel,
            a.ref_buy_unit_phone,
            a.ref_promise_provide,
            a.ref_contract_result,
            a.ref_contract_idea,
            a.ref_to_retail_result,
            a.ref_to_retail_idea,
            a.ref_data_delay_result,
            a.ref_data_delay_idea,
            a.ref_other_annex_result,
            a.ref_other_annex_idea,
            a.ref_bills_expire_date,
            a.ref_count,
            a.file_no,
            a.take_deposit,
            a.direct_transfer_status,
            a.advance_status,
            a.ge_li_note,
            a.ge_li_iner_note,
            a.remark,
            a.examine_note,
            a.examine_status,
            a.create_by,
            a.create_name,
            a.create_time,
            a.update_by,
            a.update_name,
            a.update_time,
            a.submit_by,
            a.submit_name,
            a.submit_time,
            a.confirm_by,
            a.confirm_name,
            a.confirm_time,
            a.abandon_by,
            a.abandon_name,
            a.abandon_time,
            a.close_by,
            a.close_name,
            a.close_time,
            a.end_direct_by,
            a.end_direct_name,
            a.end_direct_time,
            a.king_dee_sync,
            a.king_dee_id,
            a.correspond_id,
            a.correspond_name,
            b.id,
            b.parent_id,
            b.use_ref_count,
            b.sale_type_id AS itemSaleTypeId,
            b.sale_type_code AS itemSaleTypeCode,
            b.sale_type_name AS itemSaleTypeName,
            b.material_id,
            b.material_number,
            b.material_old_number,
            b.material_name,
            b.specification,
            b.unit,
            b.engin_price,
            b.engin_num,
            b.engin_totality,
            b.qty,
            b.price,
            b.total_amount,
            b.disc_amount,
            b.rebate_rate,
            b.rebate_amount,
            b.deposit_rate,
            b.customer_wallet_id,
            b.customer_wallet_name,
            b.customer_wallet_id2,
            b.customer_wallet_name2,
            b.service_id AS itemServiceId,
            b.service_name AS itemServiceName,
            b.is_direct_transfer,
            b.direct_transfer_qty,
            b.has_send_qty,
            b.single_rebate_amount,
            b.single_disc_amount,
            b.single_pay_price,
            b.single_deposit_amount,
            b.pay_amount,
            b.pay_rebate_amount,
            b.pay_cash_amount,
            b.pay_credit_amount,
            b.total_deposit_amount,
            b.old_qty,
            b.old_pay_amount,
            b.old_rebate_amount,
            b.tax,
            b.real_material_name,
            b.real_material_number,
            b.real_specification,
            b.real_material_old_number,
            b.remark AS item_remark,
            b.refund_product_qty,
            b.refund_product_amount,
            b.k3_category_name,
            (b.refundable_qty - b.withhold ) as refundable_qty,
            b.retired_qty,
            kdm.material_group_name,
            IFNULL(b.old_qty, 0) - ABS(IFNULL(b.refund_product_qty, 0)) - IFNULL(b.retired_qty, 0) AS itemSalesQty,
            (IFNULL(b.old_qty, 0) - ABS(IFNULL(b.refund_product_qty, 0)) - IFNULL(b.retired_qty, 0)) * b.price AS itemSalesAmount,
            b.direct_deposit_amount AS itemDirectDepositAmount
        FROM
            engin_order a JOIN engin_order_item b ON a.engin_order_id = b.parent_id
            JOIN king_dee_material kdm on b.material_id = kdm.id
        ${ex.query}
        <if test="ex.orderBy == null or ex.orderBy ==''">
            ORDER BY a.create_time DESC, b.id DESC
        </if>
        ${ex.orderBy}

    </select>
    <select id="v2TradeListPage" resultType="com.zfire.mall.manager.bean.engin.v2.order.TradeEnginOrderVO">
        SELECT
        a.engin_order_id,
        a.admin_company_id,
        a.customer_id,
        a.customer_number,
        a.customer_name,
        a.service_id,
        a.service_name,
        a.sale_type_id,
        a.sale_type_code,
        a.sale_type_name,
        a.engin_user_name,
        a.engin_order_type,
        a.engin_order_no,
        a.order_date,
        a.ref_engin_record_no,
        a.ref_project_category,
        a.ref_project_name,
        a.ref_project_type,
        a.ref_project_note,
        a.ref_use_unit,
        a.ref_install_address,
        a.ref_project_no,
        a.ref_factory_no,
        a.ref_promise_status,
        a.ref_trade_category,
        a.ref_linkman,
        a.ref_phone,
        a.ref_tel,
        a.ref_declare_no,
        a.ref_power_category,
        a.ref_machine_type,
        a.ref_region_work,
        a.ref_buy_unit_name,
        a.ref_promise_provide,
        a.ref_other_annex_idea,
        a.ref_count,
        a.file_no,
        a.take_deposit,
        a.direct_transfer_status,
        a.advance_status,
        a.ge_li_note,
        a.ge_li_iner_note,
        a.remark,
        a.examine_note,
        a.examine_status,
        a.create_by,
        a.create_name,
        a.create_time,
        a.update_by,
        a.update_name,
        a.update_time,
        a.submit_by,
        a.submit_name,
        a.submit_time,
        a.confirm_by,
        a.confirm_name,
        a.confirm_time,
        a.abandon_by,
        a.abandon_name,
        a.abandon_time,
        a.close_by,
        a.close_name,
        a.close_time,
        a.end_direct_by,
        a.end_direct_name,
        a.end_direct_time,
        a.king_dee_sync,
        a.king_dee_id,
        a.correspond_id,
        a.correspond_name,
        b.id,
        b.parent_id,
        b.use_ref_count,
        b.sale_type_id AS itemSaleTypeId,
        b.sale_type_code AS itemSaleTypeCode,
        b.sale_type_name AS itemSaleTypeName,
        b.material_id,
        b.material_number,
        b.material_old_number,
        b.material_name,
        b.specification,
        b.unit,
        b.engin_price,
        b.engin_num,
        b.engin_totality,
        b.qty,
        b.price,
        b.total_amount,
        b.disc_amount,
        b.rebate_rate,
        b.rebate_amount,
        b.deposit_rate,
        b.customer_wallet_id,
        b.customer_wallet_name,
        b.customer_wallet_id2,
        b.customer_wallet_name2,
        b.service_id AS itemServiceId,
        b.service_name AS itemServiceName,
        b.is_direct_transfer,
        b.direct_transfer_qty,
        b.has_send_qty,
        b.single_rebate_amount,
        b.single_disc_amount,
        b.single_pay_price,
        b.single_deposit_amount,
        b.pay_amount,
        b.pay_rebate_amount,
        b.pay_cash_amount,
        b.pay_credit_amount,
        b.total_deposit_amount,
        b.old_qty,
        b.old_pay_amount,
        b.old_rebate_amount,
        b.tax,
        b.real_material_name,
        b.real_material_number,
        b.real_specification,
        b.real_material_old_number,
        b.remark AS item_remark,
        b.refund_product_qty,
        b.refund_product_amount,
        b.k3_category_name,
        (b.refundable_qty - b.withhold ) as refundable_qty,
        b.retired_qty,
        kdm.material_group_name
        FROM
        engin_order a JOIN engin_order_item b ON a.engin_order_id = b.parent_id
        JOIN king_dee_material kdm on b.material_id = kdm.id
        ${ex.query}
        <if test="ex.orderBy == null or ex.orderBy ==''">
            ORDER BY a.create_time DESC, b.id DESC
        </if>
        ${ex.orderBy}

    </select>
    <select id="v2CountList" resultType="com.zfire.mall.manager.bean.PoySaleCountBean">
        SELECT
            IFNULL(SUM(b.qty), 0) AS totalC,
            IFNULL(SUM(b.pay_amount), 0) AS payAmountC,
            IFNULL(SUM(b.total_amount), 0) AS amountC,
            COUNT(DISTINCT a.ref_engin_record_no) AS totalEC
        FROM
        engin_order a JOIN engin_order_item b ON a.engin_order_id = b.parent_id
        JOIN king_dee_material kdm ON b.material_id = kdm.id
        LEFT JOIN invoice_application_order iao ON iao.order_id = b.id AND iao.refundable_qty > 0 AND iao.invoice_examine_status IN ('SAVE','WAIT')
        ${ex.query}
    </select>
    <select id="v2HomeCountList" resultType="com.zfire.mall.manager.bean.PoySaleCountBean">
        SELECT
            SUM(IFNULL(b.old_qty, 0) - ABS(IFNULL(b.refund_product_qty, 0)) - IFNULL(b.retired_qty, 0)) AS totalC,
            SUM((IFNULL(b.old_qty, 0) - ABS(IFNULL(b.refund_product_qty, 0)) - IFNULL(b.retired_qty, 0)) * b.price) AS payAmountC,
            SUM((IFNULL(b.old_qty, 0) - ABS(IFNULL(b.refund_product_qty, 0)) - IFNULL(b.retired_qty, 0)) * b.price) AS amountC,
            COUNT(DISTINCT a.ref_engin_record_no) AS totalEC
        FROM
            engin_order a JOIN engin_order_item b ON a.engin_order_id = b.parent_id
                          JOIN king_dee_material kdm ON b.material_id = kdm.id
            ${ex.query}
    </select>

</mapper>