<?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.deposit.DepositMangeReserveMapper">

    <resultMap id="listResultMap" type="com.zfire.mall.manager.bean.engin.deposit.DepositManageBean">
        <result column="deposit_manage_id" jdbcType="VARCHAR" property="depositManageId" />
        <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="primary_order_date" jdbcType="VARCHAR" property="primaryOrderDate" />
        <result column="engin_order_type" jdbcType="VARCHAR" property="enginOrderType" />
        <result column="main_id" jdbcType="VARCHAR" property="mainId" />
        <result column="main_name" jdbcType="VARCHAR" property="mainName" />
        <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_engin_record_no" jdbcType="VARCHAR" property="refEnginRecordNo" />
        <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_promise_provide" jdbcType="VARCHAR" property="refPromiseProvide" />
        <result column="ref_promise_accept_status" jdbcType="VARCHAR" property="refPromiseAcceptStatus" />
        <result column="ref_promise_price_remark" jdbcType="VARCHAR" property="refPromisePriceRemark" />
        <result column="ref_follow_accept_remark" jdbcType="VARCHAR" property="refFollowAcceptRemark" />
        <result column="ref_fj_shen_pi_remark" jdbcType="VARCHAR" property="refFjShenPiRemark" />
        <result column="ref_datum_provide" jdbcType="VARCHAR" property="refDatumProvide" />
        <result column="ref_datum_provide_date" jdbcType="VARCHAR" property="refDatumProvideDate" />
        <result column="ref_is_big_amounts" jdbcType="VARCHAR" property="refIsBigAmounts" />
        <result column="ref_contract_result" jdbcType="VARCHAR" property="refContractResult" />
        <result column="ref_contract_idea" jdbcType="VARCHAR" property="refContractIdea" />
        <result column="ref_to_retail_result" jdbcType="VARCHAR" property="refToRetailResult" />
        <result column="ref_to_retail_idea" jdbcType="VARCHAR" property="refToRetailIdea" />
        <result column="ref_data_delay_result" jdbcType="VARCHAR" property="refDataDelayResult" />
        <result column="ref_data_delay_idea" jdbcType="VARCHAR" property="refDataDelayIdea" />
        <result column="ref_other_annex_result" jdbcType="VARCHAR" property="refOtherAnnexResult" />
        <result column="ref_other_annex_idea" jdbcType="VARCHAR" property="refOtherAnnexIdea" />
        <result column="ref_datum_accept_date" jdbcType="VARCHAR" property="refDatumAcceptDate" />
        <result column="order_ge_li_inner_note" jdbcType="VARCHAR" property="orderGeLiInnerNote" />
        <result column="service_id" jdbcType="VARCHAR" property="serviceId" />
        <result column="service_name" jdbcType="VARCHAR" property="serviceName" />
        <result column="deposit_amount" jdbcType="DECIMAL" property="depositAmount" />
        <result column="deposit_diff_amount" jdbcType="DECIMAL" property="depositDiffAmount" />
        <result column="send_factory_date" jdbcType="TIMESTAMP" property="sendFactoryDate" />
        <result column="install_date" jdbcType="TIMESTAMP" property="installDate" />
        <result column="is_refund_deposit" jdbcType="BOOLEAN" property="isRefundDeposit" />
        <result column="sale_type_id" jdbcType="BOOLEAN" property="saleTypeId" />
        <result column="sale_type_name" jdbcType="BOOLEAN" property="saleTypeName" />
        <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="ge_li_inner_data_note" jdbcType="VARCHAR" property="geLiInnerDataNote" />
        <result column="real_use_unit" jdbcType="VARCHAR" property="realUseUnit" />
        <result column="real_install_address" jdbcType="VARCHAR" property="realInstallAddress" />
        <result column="apply_note" jdbcType="VARCHAR" property="applyNote" />
        <result column="examine_after_remark" jdbcType="VARCHAR" property="examineAfterRemark" />
        <result column="amend_remark" jdbcType="VARCHAR" property="amendRemark" />
        <result column="review_remark" jdbcType="VARCHAR" property="reviewRemark" />
        <result column="data_state" jdbcType="VARCHAR" property="dataState" />
        <result column="examine_status" jdbcType="VARCHAR" property="examineStatus" />
        <result column="check_by" jdbcType="VARCHAR" property="checkBy" />
        <result column="check_note" jdbcType="VARCHAR" property="checkNote" />
        <result column="check_date" jdbcType="VARCHAR" property="checkDate" />
        <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="amend_by" jdbcType="VARCHAR" property="amendBy" />
        <result column="amend_name" jdbcType="VARCHAR" property="amendName" />
        <result column="amend_time" jdbcType="TIMESTAMP" property="amendTime" />
        <result column="review_by" jdbcType="VARCHAR" property="reviewBy" />
        <result column="review_name" jdbcType="VARCHAR" property="reviewName" />
        <result column="review_time" jdbcType="TIMESTAMP" property="reviewTime" />
        <collection property="items" resultMap="itemResultList" />
    </resultMap>

    <resultMap id="itemResultList" type="com.zfire.mall.manager.bean.engin.deposit.DepositMangeItemBean">
        <result column="id" jdbcType="VARCHAR" property="id" />
        <result column="parent_id" jdbcType="VARCHAR" property="parentId" />
        <result column="item_ref_engin_record_no" jdbcType="VARCHAR" property="refEnginRecordNo" />
        <result column="special_item_no" jdbcType="VARCHAR" property="specialItemNo" />
        <result column="factory_no" jdbcType="VARCHAR" property="factoryNo" />
        <result column="special_no" jdbcType="VARCHAR" property="specialNo" />
        <result column="bill_no" jdbcType="VARCHAR" property="billNo" />
        <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="price" jdbcType="DECIMAL" property="price" />
        <result column="item_qty" jdbcType="INTEGER" property="qty" />
        <result column="item_total_amount" jdbcType="DECIMAL" property="totalAmount" />
        <result column="customer_wallet_id" jdbcType="VARCHAR" property="customerWalletId" />
        <result column="item_remark" jdbcType="VARCHAR" property="remark" />
        <result column="tax" jdbcType="DECIMAL" property="tax" />
        <result column="has_send_qty" jdbcType="INTEGER" property="hasSendQty" />
        <result column="item_deposit_amount" jdbcType="DECIMAL" property="depositAmount" />
        <result column="item_data_qty" jdbcType="INTEGER" property="dataQty" />
        <result column="item_diff_amount" jdbcType="DECIMAL" property="diffAmount" />
        <result column="diff_policy_price" jdbcType="DECIMAL" property="diffPolicyPrice" />
        <result column="diff_price" jdbcType="DECIMAL" property="diffPrice" />
        <result column="contract_qty" jdbcType="INTEGER" property="contractQty" />
        <result column="contract_price" jdbcType="DECIMAL" property="contractPrice" />
        <result column="contract_amount" jdbcType="DECIMAL" property="contractAmount" />
        <result column="mail_factory_date" jdbcType="TIMESTAMP" property="mailFactoryDate" />
        <result column="mail_factory_year" jdbcType="VARCHAR" property="mailFactoryYear" />
        <result column="confirm_date" jdbcType="TIMESTAMP" property="confirmDate" />
        <result column="settlement_no" jdbcType="VARCHAR" property="settlementNo" />
        <result column="settlement_date" jdbcType="TIMESTAMP" property="settlementDate" />
        <result column="in_factory_date" jdbcType="TIMESTAMP" property="inFactoryDate" />
        <result column="in_factory_type" jdbcType="VARCHAR" property="inFactoryType" />
        <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.deposit.DepositManageBean">
        SELECT
            a.*,
            b.id,
            b.parent_id,
            b.factory_no,
            b.special_no,
            b.material_id,
            b.material_number,
            b.material_name,
            b.material_old_number,
            b.specification,
            b.real_material_id,
            b.real_material_number,
            b.real_material_name,
            b.real_material_old_number,
            b.real_specification,
            b.unit,
            b.price AS itemPrice,
            b.qty AS itemQty,
            b.deposit_amount AS itemDepositAmount,
            b.data_qty AS itemDataQty,
            b.diff_amount AS itemDiffAmount,
            b.remark AS itemRemark,
            b.has_send_qty,
            b.contract_qty AS itemContractQty,
            b.contract_price AS itemContractPrice,
            b.contract_amount AS itemContractAmount,
            b.special_item_no,
            b.special_item_no_number,
            b.bill_no,
            b.bill_no_number,
            b.mail_factory_date,
            b.mail_factory_year,
            b.confirm_date,
            b.settlement_no,
            b.settlement_date,
            b.in_factory_date,
            b.in_factory_type,
            b.customer_wallet_id
        FROM
            deposit_manage a
            LEFT JOIN deposit_manage_item b ON a.deposit_manage_id = b.parent_id
        <where>
            <if test="customerId != null and customerId != ''">
                AND a.customer_id = #{customerId}
            </if>
            <if test="settlementStatus != null ">
                 <if test="settlementStatus ==1">
                     AND b.settlement_date IS NULL
                 </if>
                 <if test="settlementStatus ==0">
                     AND b.settlement_date IS NOT NULL
                 </if>
            </if>
            <if test="mailFactoryStatus != null ">
                <if test="mailFactoryStatus ==1">
                AND b.mail_factory_date IS NULL
                </if>
                 <if test="mailFactoryStatus ==0">
                     AND b.mail_factory_date IS NOT NULL
                 </if>
            </if>
            <if test="customerKeyword != null and customerKeyword != ''">
                AND (a.customer_name LIKE CONCAT('%', #{customerKeyword}, '%')
                OR a.customer_number LIKE CONCAT('%', #{customerKeyword}, '%')
                )
            </if>
            <if test="refEnginRecordNo != null and refEnginRecordNo != ''">
                AND a.ref_engin_record_no LIKE CONCAT('%', #{refEnginRecordNo}, '%')
            </if>
            <if test="refProjectName != null and refProjectName != ''">
                AND a.ref_project_name LIKE CONCAT('%', #{refProjectName},'%')
            </if>
            <if test="enginOrderType != null and enginOrderType != ''">
                AND a.engin_order_type = #{enginOrderType}
            </if>

            <if test="refTradeCategory != null and refTradeCategory != ''">
                AND a.ref_trade_category LIKE CONCAT('%', #{refTradeCategory},'%')
            </if>
            <if test="refUseUnit != null and refUseUnit != ''">
                AND a.ref_use_unit LIKE CONCAT('%', #{refUseUnit},'%')
            </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="isRefundDeposit != null">
                AND a.is_refund_deposit = #{isRefundDeposit}
            </if>
            <if test="startSubmitTime != null and startSubmitTime!=''">
                AND a.submit_time <![CDATA[ >= ]]> #{startSubmitTime}
            </if>
            <if test="endSubmitTime != null and endSubmitTime!=''">
                AND a.submit_time <![CDATA[ <= ]]> #{endSubmitTime}
            </if>
            <if test="startConfirmTime != null and startConfirmTime!=''">
                AND a.confirm_time <![CDATA[ >= ]]> #{startConfirmTime}
            </if>
            <if test="endConfirmTime != null and endConfirmTime!=''">
                AND a.confirm_time <![CDATA[ <= ]]> #{endConfirmTime}
            </if>
            <if test="materialNumber != null and materialNumber != ''">
                AND b.material_number LIKE CONCAT('%', #{materialNumber},'%')
            </if>
            <if test="materialOldNumber != null and materialOldNumber != ''">
                AND b.material_old_number LIKE CONCAT('%', #{materialOldNumber},'%')
            </if>
            <if test="materialName != null and materialName != ''">
                AND b.material_name LIKE CONCAT('%', #{materialName},'%')
            </if>
            <if test="specification != null and specification != '' ">
                AND b.specification LIKE CONCAT('%', #{specification},'%')
            </if>
            <if test="factoryNo != null and factoryNo != ''">
                AND b.factory_no LIKE CONCAT('%', #{factoryNo},'%')
            </if>
            <if test="specialNo != null and specialNo != ''">
                AND b.special_no LIKE CONCAT('%', #{specialNo},'%')
            </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="refRegionWork != null and refRegionWork != ''">
                AND a.ref_region_work LIKE CONCAT('%', #{refRegionWork},'%')
            </if>
            <if test="startInstallTime != null and startInstallTime!=''">
                AND a.install_date <![CDATA[ >= ]]> #{startInstallTime}
            </if>
            <if test="endInstallTime != null and endInstallTime!=''">
                AND a.install_date <![CDATA[ <= ]]> #{endInstallTime}
            </if>
            <if test="startAcceptTime != null and startAcceptTime!=''">
                AND a.ref_datum_accept_date <![CDATA[ >= ]]> #{startAcceptTime}
            </if>
            <if test="endAcceptTime != null and endAcceptTime!=''">
                AND a.ref_datum_accept_date <![CDATA[ <= ]]> #{endAcceptTime}
            </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="specialItemNo != null and specialItemNo != ''">
                AND b.special_item_no LIKE CONCAT('%', #{preSpecialItemNo},'%')
            </if>
            <if test="startFactoryNo != null and endFactoryNo != null">
                AND b.factory_no LIKE CONCAT( #{preFactoryNo},'%')
                AND b.factory_no_number BETWEEN #{startFactoryNo} AND #{endFactoryNo}
            </if>
            <if test="startSpecialNo != null and endSpecialNo != null">
                AND b.special_no LIKE CONCAT( #{preSpecialNo},'%')
                AND b.special_no_number BETWEEN #{startSpecialNo} AND #{endSpecialNo}
            </if>
            <if test="startBillNo != null and endBillNo != null">
            	AND b.bill_no LIKE CONCAT( #{preBillNo},'%')
                AND b.bill_no_number BETWEEN #{startBillNo} AND #{endBillNo}
            </if>
            <if test="startMailFactoryDate != null and startMailFactoryDate != '' and endMailFactoryDate != null and endMailFactoryDate != ''">
                AND b.mail_factory_date BETWEEN #{startMailFactoryDate} AND #{endMailFactoryDate}
            </if>
            <if test="mailFactoryYear != null and mailFactoryYear != ''">
                AND b.mail_factory_year LIKE CONCAT('%', #{mailFactoryYear},'%')
            </if>
            <if test="startConfirmDate != null and startConfirmDate != '' and endConfirmDate != null and endConfirmDate != ''">
                AND b.confirm_date BETWEEN #{startConfirmDate} AND #{endConfirmDate}
            </if>
            <if test="startSettlementNo != null and startSettlementNo != '' and endSettlementNo != null and endSettlementNo != ''">
                AND b.settlement_no BETWEEN #{startSettlementNo} AND #{endSettlementNo}
            </if>
            <if test="startSettlementDate != null and startSettlementDate != '' and endSettlementDate != null and endSettlementDate != ''">
                AND b.settlement_date BETWEEN #{startSettlementDate} AND #{endSettlementDate}
            </if>
            <if test="startInFactoryDate != null and startInFactoryDate != '' and endInFactoryDate != null and endInFactoryDate != ''">
                AND b.in_factory_date BETWEEN #{startInFactoryDate} AND #{endInFactoryDate}
            </if>
            <if test="inFactoryType != null and inFactoryType != ''">
                AND b.in_factory_type LIKE CONCAT('%', #{inFactoryType},'%')
            </if>
            <if test="startOrderDate != null and startOrderDate != '' and endOrderDate != null and endOrderDate != ''">
                AND a.primary_order_date BETWEEN #{startOrderDate} AND #{endOrderDate}
            </if>
            <if test="dataStates != null and dataStates.size > 0">
                AND a.data_state IN
                <foreach item="item" index="index" collection="dataStates" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </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 a.service_id IN
                <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        ORDER BY a.create_time DESC, a.deposit_manage_id DESC
    </select>
    <select id="detail" resultMap="listResultMap">
        SELECT
        a.*,
        b.id,
        b.parent_id,
        b.ref_engin_record_no AS item_ref_engin_record_no,
        b.factory_no,
        b.special_no,
        b.material_id,
        b.material_number,
        b.material_name,
        b.material_old_number,
        b.specification,
        b.real_material_id,
        b.real_material_number,
        b.real_material_name,
        b.real_material_old_number,
        b.real_specification,
        b.unit,
        b.price,
        b.qty AS item_qty,
        b.has_send_qty,
        b.total_amount AS item_total_amount,
        b.deposit_amount AS item_deposit_amount,
        b.data_qty AS item_data_qty,
        b.diff_amount AS item_diff_amount,
        b.remark AS item_remark,
        b.diff_policy_price,
        b.diff_price,
        b.contract_qty,
        b.contract_price,
        b.contract_amount,
        b.customer_wallet_id,
        b.sales_qty,
        b.sales_amount
        FROM
        deposit_manage a JOIN deposit_manage_item b ON a.deposit_manage_id = b.parent_id
        <where>
            a.deposit_manage_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 a.service_id in
                <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
    <select id="queryEnginOrderInfo" resultType="com.zfire.mall.manager.bean.engin.deposit.EnginOrderInfo">
        SELECT
            a.engin_order_no,
            a.order_date,
            b.material_id,
            b.material_number,
            b.material_old_number,
            b.material_name,
            b.specification,
            b.price,
            b.qty,
            b.total_deposit_amount
        FROM
            engin_order a
                LEFT JOIN engin_order_item b ON a.engin_order_id = b.parent_id
        WHERE
            a.ref_engin_record_no = #{refEnginRecordNo}
          AND a.examine_status = 'OK'
        ORDER BY a.create_time DESC, a.engin_order_id DESC
    </select>
    <select id="queryDisplaceOrderInfo"
            resultType="com.zfire.mall.manager.bean.displace.order.DisplaceOrderInfo">
        SELECT
            a.displace_order_id,
            a.order_date,
            b.material_id,
            b.material_number,
            b.material_old_number,
            b.material_name,
            b.specification,
            b.price,
            b.qty,
            b.total_deposit_amount
        FROM
            displace_order a
                LEFT JOIN displace_order_item b ON a.displace_order_id = b.parent_id
        WHERE
            a.engin_record_no = #{refEnginRecordNo}
          AND a.examine_status = 'OK'
    </select>
    <select id="queryOrderHasSendQty" resultType="com.zfire.mall.manager.plus.entity.EnginOrderItem">
        SELECT
        a.ref_engin_record_no,
        b.material_id,
        b.customer_wallet_id,
        b.price,
        SUM(b.qty) - SUM(b.refundable_qty) AS qty,
        SUM(b.has_send_qty) AS orderQty
        FROM
        engin_order a,
        engin_order_item b
        WHERE
        a.engin_order_id = b.parent_id
        AND a.ref_engin_record_no in
        <foreach item="item" index="index" collection="recordNos" open="(" separator=","
                 close=")">
            #{item}
        </foreach>
        GROUP BY
        a.ref_engin_record_no, b.material_id, b.customer_wallet_id, b.price
    </select>
    <select id="countList" resultType="com.zfire.mall.manager.bean.PoySaleCountBean">
        SELECT
        SUM(b.qty) AS totalC,
        SUM(b.total_amount) AS amountC
        FROM
        deposit_manage a
        LEFT JOIN deposit_manage_item b ON a.deposit_manage_id = b.parent_id
        <where>
            <if test="customerId != null and customerId != ''">
                AND a.customer_id = #{customerId}
            </if>
            <if test="settlementStatus != null ">
                <if test="settlementStatus ==1">
                    AND b.settlement_date IS NULL
                </if>
                <if test="settlementStatus ==0">
                    AND b.settlement_date IS NOT NULL
                </if>
            </if>
            <if test="mailFactoryStatus != null ">
                <if test="mailFactoryStatus ==1">
                    AND b.mail_factory_date IS NULL
                </if>
                <if test="mailFactoryStatus ==0">
                    AND b.mail_factory_date IS NOT NULL
                </if>
            </if>
            <if test="customerKeyword != null and customerKeyword != ''">
                AND (a.customer_name LIKE CONCAT('%', #{customerKeyword}, '%')
                OR a.customer_number LIKE CONCAT('%', #{customerKeyword}, '%')
                )
            </if>
            <if test="refEnginRecordNo != null and refEnginRecordNo != ''">
                AND a.ref_engin_record_no LIKE CONCAT('%', #{refEnginRecordNo}, '%')
            </if>
            <if test="refProjectName != null and refProjectName != ''">
                AND a.ref_project_name LIKE CONCAT('%', #{refProjectName},'%')
            </if>
            <if test="enginOrderType != null and enginOrderType != ''">
                AND a.engin_order_type = #{enginOrderType}
            </if>

            <if test="refTradeCategory != null and refTradeCategory != ''">
                AND a.ref_trade_category LIKE CONCAT('%', #{refTradeCategory},'%')
            </if>
            <if test="refUseUnit != null and refUseUnit != ''">
                AND a.ref_use_unit LIKE CONCAT('%', #{refUseUnit},'%')
            </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="isRefundDeposit != null">
                AND a.is_refund_deposit = #{isRefundDeposit}
            </if>
            <if test="startSubmitTime != null and startSubmitTime!=''">
                AND a.submit_time <![CDATA[ >= ]]> #{startSubmitTime}
            </if>
            <if test="endSubmitTime != null and endSubmitTime!=''">
                AND a.submit_time <![CDATA[ <= ]]> #{endSubmitTime}
            </if>
            <if test="startConfirmTime != null and startConfirmTime!=''">
                AND a.confirm_time <![CDATA[ >= ]]> #{startConfirmTime}
            </if>
            <if test="endConfirmTime != null and endConfirmTime!=''">
                AND a.confirm_time <![CDATA[ <= ]]> #{endConfirmTime}
            </if>
            <if test="materialNumber != null and materialNumber != ''">
                AND b.material_number LIKE CONCAT('%', #{materialNumber},'%')
            </if>
            <if test="materialOldNumber != null and materialOldNumber != ''">
                AND b.material_old_number LIKE CONCAT('%', #{materialOldNumber},'%')
            </if>
            <if test="materialName != null and materialName != ''">
                AND b.material_name LIKE CONCAT('%', #{materialName},'%')
            </if>
            <if test="specification != null and specification != '' ">
                AND b.specification LIKE CONCAT('%', #{specification},'%')
            </if>
            <if test="factoryNo != null and factoryNo != ''">
                AND b.factory_no LIKE CONCAT('%', #{factoryNo},'%')
            </if>
            <if test="specialNo != null and specialNo != ''">
                AND b.special_no LIKE CONCAT('%', #{specialNo},'%')
            </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="refRegionWork != null and refRegionWork != ''">
                AND a.ref_region_work LIKE CONCAT('%', #{refRegionWork},'%')
            </if>
            <if test="startInstallTime != null and startInstallTime!=''">
                AND a.install_date <![CDATA[ >= ]]> #{startInstallTime}
            </if>
            <if test="endInstallTime != null and endInstallTime!=''">
                AND a.install_date <![CDATA[ <= ]]> #{endInstallTime}
            </if>
            <if test="startAcceptTime != null and startAcceptTime!=''">
                AND a.ref_datum_accept_date <![CDATA[ >= ]]> #{startAcceptTime}
            </if>
            <if test="endAcceptTime != null and endAcceptTime!=''">
                AND a.ref_datum_accept_date <![CDATA[ <= ]]> #{endAcceptTime}
            </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="specialItemNo != null and specialItemNo != ''">
                AND b.special_item_no LIKE CONCAT('%', #{preSpecialItemNo},'%')
            </if>
            <if test="startFactoryNo != null and endFactoryNo != null">
                AND b.factory_no LIKE CONCAT( #{preFactoryNo},'%')
                AND b.factory_no_number BETWEEN #{startFactoryNo} AND #{endFactoryNo}
            </if>
            <if test="startSpecialNo != null and endSpecialNo != null">
                AND b.special_no LIKE CONCAT( #{preSpecialNo},'%')
                AND b.special_no_number BETWEEN #{startSpecialNo} AND #{endSpecialNo}
            </if>
            <if test="startBillNo != null and endBillNo != null">
                AND b.bill_no LIKE CONCAT( #{preBillNo},'%')
                AND b.bill_no_number BETWEEN #{startBillNo} AND #{endBillNo}
            </if>
            <if test="startMailFactoryDate != null and startMailFactoryDate != '' and endMailFactoryDate != null and endMailFactoryDate != ''">
                AND b.mail_factory_date BETWEEN #{startMailFactoryDate} AND #{endMailFactoryDate}
            </if>
            <if test="mailFactoryYear != null and mailFactoryYear != ''">
                AND b.mail_factory_year LIKE CONCAT('%', #{mailFactoryYear},'%')
            </if>
            <if test="startConfirmDate != null and startConfirmDate != '' and endConfirmDate != null and endConfirmDate != ''">
                AND b.confirm_date BETWEEN #{startConfirmDate} AND #{endConfirmDate}
            </if>
            <if test="startSettlementNo != null and startSettlementNo != '' and endSettlementNo != null and endSettlementNo != ''">
                AND b.settlement_no BETWEEN #{startSettlementNo} AND #{endSettlementNo}
            </if>
            <if test="startSettlementDate != null and startSettlementDate != '' and endSettlementDate != null and endSettlementDate != ''">
                AND b.settlement_date BETWEEN #{startSettlementDate} AND #{endSettlementDate}
            </if>
            <if test="startInFactoryDate != null and startInFactoryDate != '' and endInFactoryDate != null and endInFactoryDate != ''">
                AND b.in_factory_date BETWEEN #{startInFactoryDate} AND #{endInFactoryDate}
            </if>
            <if test="inFactoryType != null and inFactoryType != ''">
                AND b.in_factory_type LIKE CONCAT('%', #{inFactoryType},'%')
            </if>
            <if test="startOrderDate != null and startOrderDate != '' and endOrderDate != null and endOrderDate != ''">
                AND a.primary_order_date BETWEEN #{startOrderDate} AND #{endOrderDate}
            </if>
            <if test="dataStates != null and dataStates.size > 0">
                AND a.data_state IN
                <foreach item="item" index="index" collection="dataStates" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </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 a.service_id IN
                <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>

    <select id="v2HomeListPage" resultType="com.zfire.mall.manager.bean.engin.v2.deposit.DepositManageVO">
        SELECT
            a.*,
            b.id,
            b.parent_id,
            b.factory_no,
            b.special_no,
            b.material_id,
            b.material_number,
            b.material_name,
            b.material_old_number,
            b.specification,
            b.real_material_id,
            b.real_material_number,
            b.real_material_name,
            b.real_material_old_number,
            b.real_specification,
            b.unit,
            b.price AS itemPrice,
            b.qty AS itemQty,
            IFNULL(b.price, 0) * IFNULL(b.qty, 0) AS itemTotalAmount,
            b.deposit_amount AS itemDepositAmount,
            b.data_qty AS itemDataQty,
            b.diff_amount AS itemDiffAmount,
            b.remark AS itemRemark,
            b.has_send_qty,
            b.contract_qty AS itemContractQty,
            b.contract_price AS itemContractPrice,
            b.contract_amount AS itemContractAmount,
            b.special_item_no,
            b.special_item_no_number,
            b.bill_no,
            b.bill_no_number,
            b.mail_factory_date,
            b.mail_factory_year,
            b.confirm_date,
            b.settlement_no,
            b.settlement_date,
            b.in_factory_date,
            b.in_factory_type,
            b.customer_wallet_id,
            b.sales_qty AS itemSalesQty,
            b.sales_amount AS itemSalesAmount
        FROM
            deposit_manage a
                LEFT JOIN deposit_manage_item b ON a.deposit_manage_id = b.parent_id
        ${ex.query}
        <if test="ex.orderBy == null or ex.orderBy ==''">
            ORDER BY a.create_time DESC, a.deposit_manage_id DESC, b.sales_qty DESC
        </if>
        ${ex.orderBy}
    </select>
    <select id="v2CountList" resultType="com.zfire.mall.manager.bean.PoySaleCountBean">
        SELECT
            IFNULL(SUM(b.sales_qty), 0) AS totalC,
            IFNULL(SUM(b.sales_amount), 0) AS amountC,
            COUNT(DISTINCT a.ref_engin_record_no) AS totalEC
        FROM
            deposit_manage a
                LEFT JOIN deposit_manage_item b ON a.deposit_manage_id = b.parent_id
            ${ex.query}
    </select>
    <select id="queryOrderItem" resultType="com.zfire.mall.manager.bean.engin.v2.order.HomeEnginOrderVO">
        SELECT
            b.material_id,
            b.real_material_id,
            b.price,
            SUM(IFNULL(b.old_qty, 0)) - ABS(SUM(IFNULL(b.refund_product_qty, 0))) - SUM(IFNULL(b.retired_qty, 0)) AS itemSalesQty,
            (SUM(IFNULL(b.old_qty, 0)) - ABS(SUM(IFNULL(b.refund_product_qty, 0))) - SUM(IFNULL(b.retired_qty, 0))) * b.price AS itemSalesAmount
        FROM
            engin_order a, engin_order_item b
        WHERE
            a.engin_order_id = b.parent_id
            AND a.examine_status = 'OK'
            AND a.ref_engin_record_no = #{refEnginRecordNo}
        GROUP BY b.material_id, b.real_material_id, b.price
    </select>

    <update id="batchSpanUpdateInfo">
        UPDATE deposit_manage a JOIN geli.agency_bills b ON a.ref_engin_record_no = b.recordNo
        SET
            a.ref_contract_idea = IFNULL(b.contractIdea, a.ref_contract_idea),
            a.ref_to_retail_idea = IFNULL(b.toRetailIdea, a.ref_to_retail_idea),
            a.ref_data_delay_idea = IFNULL(b.dataDelayIdea, a.ref_data_delay_idea),
            a.ref_other_annex_idea = IFNULL(b.otherAnnexIdea, a.ref_other_annex_idea)
        WHERE 1 = 1
    </update>
</mapper>