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

    <select id="listInvoice" resultType="com.zfire.mall.manager.bean.invoice.InvoiceBean">
        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,
        ia.automatic_status,
        iao.material_group_name,
               iao.k3_service_id,
               iao.k3_service_name,
               iao.header_remark,
               kdm.category_name,
               kdm.pjxh1_Text,
        iao.file_no as itemFileNo
        FROM
        invoice_application ia
        left join invoice_application_order iao on ia.id = iao.invoice_id
        LEFT JOIN king_dee_material kdm ON kdm.id = iao.material_id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        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 ia.id = #{id}
        </if>
          <if test="createBy != null and createBy!= ''">
            and ia.create_by like concat('%',#{createBy},'%')
        </if>
             <if test="automaticStatus != null ">
            and ia.automatic_status = #{automaticStatus}
        </if>
        <if test="customerName != null and customerName!= ''">
            and ia.customer_name like concat('%',#{customerName},'%')
        </if>
            <if test="approvalName != null and approvalName!= ''">
            and ia.approval_name like concat('%',#{approvalName},'%')
        </if>
        <if test="mainOrderId != null and mainOrderId!= ''">
            and (iao.main_order_id like concat('%',#{mainOrderId},'%') or ia.engin_order_no  like concat('%',#{mainOrderId},'%'))
        </if>
        <if test="correspondId != null and correspondId != ''">
            and ia.correspond_id = #{correspondId}
        </if>
        <if test="type != null and type != ''">
            and ia.type = #{type}
        </if>
          <if test="correspondName != null and correspondName != ''">
            and ia.correspond_name like concat('%',#{correspondName},'%')
        </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 ia.order_time BETWEEN #{startTime} and #{endTime}
        </if>
        <if test="customerNumber != null and customerNumber!= ''">
            and ia.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="examineStatus != null and examineStatus!= ''">
            and ia.examine_status = #{examineStatus}
        </if>
        <if test="status != null and status!= ''">
            and ia.examine_status != #{status}
        </if>
        <if test="orderType != null and orderType!= ''">
            and iao.order_type = #{orderType}
        </if>
        <if test="customerId != null and customerId != ''">
            and ia.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="categoryName != null and categoryName != ''">
            and kdm.category_name = #{categoryName}
        </if>
             <if test="categoryId != null and categoryId != ''">
            and kdm.category_id = #{categoryId}
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        order by ia.create_time desc
    </select>

    <resultMap id="BaseResultMap" type="com.zfire.mall.manager.bean.invoice.InvoiceBody">
        <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_url" jdbcType="VARCHAR" property="fileUrl"/>
        <result column="examine_status" jdbcType="TINYINT" property="examineStatus"/>
        <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"/>
        <collection property="orders" 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="detail" resultMap="BaseResultMap">
        SELECT
        ia.*
        FROM
        invoice_application ia
        where 1=1
        <if test="id != null and id!= ''">
            and ia.id = #{id}
        </if>
    </select>

    <select id="order" resultType="com.zfire.mall.manager.bean.invoice.InvoiceApplicationOrderBody">
        SELECT
            iao.*,
               kdm.pjxh1_Text,
                iao.file_no as itemFileNo,
                iao.remark as invoiceRemark
        FROM
         invoice_application_order iao
        left join king_dee_material kdm on iao.material_id = kdm.id
        where iao.invoice_id = #{id}
    </select>

    <select id="selectInteger" resultType="Integer">
        SELECT
            sum(iao.invoice_num)
        FROM
            invoice_application_order iao
                LEFT JOIN retail_order_item roi ON iao.order_id = roi.id
                LEFT JOIN invoice_application ia on ia.id = iao.invoice_id
                LEFT JOIN sales_order so on so.order_no = ia.id
        where roi.material_id = #{materialId}
          and ia.correspond_id= #{correspondId}
          and so.examine_status in ('OK')
    </select>


    <select id="listProject" resultType="com.zfire.mall.manager.bean.invoice.InvoiceProjectBean">
        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,
        iao.material_group_name,
               iao.k3_service_name,
               iao.k3_service_id,
        kdm.category_name,
        kdm.pjxh1_Text,
        iao.file_no as itemFileNo
        FROM
        invoice_application ia
        LEFT JOIN invoice_application_order iao ON ia.id = iao.invoice_id
        left join king_dee_material kdm  on kdm.id = iao.material_id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        where 1=1
        and iao.order_type in ('TRADE','HOME','PERMU_HOME','PERMU_TRADE','REQUISITION_TRADE','REQUISITION_HOME')
        <if test="id != null and id!= ''">
            and ia.id like concat('%',#{id},'%')
        </if>
            <if test="categoryId != null and categoryId!= ''">
            and kdm.category_id =#{categoryId}
        </if>
        <if test="automaticStatus != null ">
            and ia.automatic_status = #{automaticStatus}
        </if>
        <if test="createBy != null and createBy!= ''">
            and ia.create_by like concat('%',#{createBy},'%')
        </if>
        <if test="refRegionWork != null and refRegionWork!= ''">
            and ia.ref_region_work like concat('%',#{categoryId},'%')
        </if>
        <if test="customerName != null and customerName!= ''">
            and ia.customer_name like concat('%',#{customerName},'%')
        </if>
          <if test="approvalName != null and approvalName!= ''">
            and ia.approval_name like concat('%',#{approvalName},'%')
        </if>
          <if test="mainOrderId != null and mainOrderId!= ''">
            and ia.engin_order_no like concat('%',#{mainOrderId},'%')
        </if>
        <if test="correspondId != null and correspondId != ''">
            and ia.correspond_id = #{correspondId}
        </if>
          <if test="type != null and type != ''">
            and ia.type = #{type}
        </if>
          <if test="correspondName != null and correspondName != ''">
            and ia.correspond_name like concat('%',#{correspondName},'%')
        </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 ia.order_time BETWEEN #{startTime} and #{endTime}
        </if>
        <if test="customerNumber != null and customerNumber!= ''">
            and ia.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="examineStatus != null and examineStatus!= ''">
            and ia.examine_status = #{examineStatus}
        </if>
          <if test="status != null and status!= ''">
            and ia.examine_status != #{status}
        </if>
        <if test="orderType != null and orderType!= ''">
            and iao.order_type = #{orderType}
        </if>
        <if test="customerId != null and customerId != ''">
            and ia.customer_id = #{customerId}
        </if>
          <if test="refUseUnit != null and refUseUnit != ''">
            and ia.ref_use_unit like concat('%',#{refUseUnit},'%')
        </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="refEnginRecordNo != null and refEnginRecordNo != ''">
            and ia.ref_engin_record_no like concat('%',#{refEnginRecordNo},'%')
        </if>
        order by ia.create_time desc
    </select>


    <resultMap id="BaseResultProjectMap" type="com.zfire.mall.manager.bean.invoice.InvoiceProjectBody">
        <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_url" jdbcType="VARCHAR" property="fileUrl"/>
        <result column="examine_status" jdbcType="TINYINT" property="examineStatus"/>
        <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"/>
        <collection property="orders" ofType="com.zfire.mall.manager.bean.invoice.InvoiceApplicationOrderBody" 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="invoiceRemark" jdbcType="VARCHAR" property="invoiceRemark"/>
            <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="engin_order_no" jdbcType="VARCHAR" property="enginOrderNo"/>
            <result column="pjxh1_text" jdbcType="VARCHAR" property="pjxh1Text"/>
        </collection>
    </resultMap>

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

    <select id="orderProject" resultType="com.zfire.mall.manager.bean.invoice.InvoiceApplicationOrderBody">
        SELECT
        iao.id,
        iao.main_order_id,
        iao.order_id,
        iao.invoice_id,
        iao.invoice_num,
        iao.approval_number,
        iao.refundable_qty,
        iao.retired_qty,
        iao.material_id,
        iao.order_type,
        iao.sale_type_id,
        iao.sale_type_code,
        iao.sale_type_name,
        iao.the_time,
        iao.material_old_number,
        iao.material_code,
        iao.material_name,
        iao.specification,
        iao.unit,
        iao.remark as invoiceRemark,
        iao.price,
        iao.pay_amount,
        iao.tax,
        iao.qty,
        iao.after_tax_price,
        iao.no_total_amount,
        iao.pay_rebate_amount,
        iao.total_disc_amount,
        iao.refund_status,
        iao.single_pay_price,
        iao.single_deposit_amount,
        iao.service_id,
        iao.service_name,
        iao.disc_amount,
        iao.king_dee_seq,
        iao.king_dee_entry_id,
        iao.king_sale_id,
        iao.king_dee_order_seq,
        iao.king_dee_order_id,
        iao.king_bill_no,
        iao.total_amount,
        iao.single_rebate_amount,
        iao.policy_id,
        iao.policy_title,
        iao.policy_code,
        iao.sales_order_item_id,
        iao.variety_qty,
        iao.requisition_id,
        iao.requisition_item_id,
        iao.header_remark,
        iao.k3_category_id,
        iao.k3_category_number,
        iao.k3_category_name,
        iao.k3_service_id,
        iao.k3_service_name,
        ia.engin_order_no,
               kdm.pjxh1_Text,
               iao.file_no as itemFileNo
        FROM
        invoice_application_order iao
        left join invoice_application ia on iao.invoice_id = ia.id
        left join king_dee_material kdm on kdm.id = iao.material_id
        WHERE
        1 =1
        <if test="id != null and id!= ''">
            and iao.invoice_id = #{id}
        </if>
    </select>

    <select id="listInvoicePick" resultType="com.zfire.mall.manager.bean.invoice.InvoicePickBean">

        SELECT
        sd.*,
        iao.id as invoiceOrderId,
        iao.main_order_id,
        iao.order_id,
        iao.approval_number,
        iao.refundable_qty+iao.retired_qty as refundable_qty,
        iao.retired_qty,
        iao.material_id,
        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.order_type,
        iao.invoice_id,
        IFNULL(sum(pms.print_num),null) as print_num
        FROM
        invoice_application sd
        JOIN invoice_application_order iao ON sd.id = iao.invoice_id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        join sales_order_item soi on soi.invoice_item_id = iao.id
        LEFT JOIN pick_manager_ship pms on iao.id = pms.invoice_order_id
        WHERE 1 = 1
        and sd.examine_status = 'OK'
        and sd.type =1
        and soi.refundable_qty > 0
        <if test="correspondId != null and correspondId != ''">
        and sd.correspond_id =#{correspondId}
        </if>
        <if test="customerId != null and customerId != ''">
            and sd.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>
        GROUP BY iao.id HAVING print_num is null
        order by sd.create_time desc
    </select>

    <select id="listOrder" resultType="com.zfire.mall.manager.bean.invoice.InvoiceOrder">
        SELECT
        a.admin_company_id,
        b.k3_category_name as category_name,
        b.remark,
        b.remark as invoiceRemark,
        a.create_time,
        b.service_id,
        a.customer_id,
        a.id as mainOrderId,
        a.the_time,
        IF(a.requisition_status = 1,IF(a.type = 1,'REQUISITION_RETAIL','REQUISITION_RETAIL_POLICY'),IF(a.type = 1,'RETAIL','RETAIL_POLICY')) as order_type,
        b.id AS order_id,
        b.sale_type_id,
        b.sale_type_name,
        b.material_id,
        b.material_code,
        b.material_name,
        b.material_old_number,
        b.specification,
        b.unit,
        b.qty,
        (b.refundable_qty-IFNULL(b.withhold,0)) as refundable_qty,
        b.retired_qty,
        a.remark as  headerRemark,
        b.k3_category_id,
        if(IFNULL(b.king_dee_seq,0) &lt; 1,1,0) as order_base
        FROM
        retail_order_item b
        JOIN  retail_order a ON a.id = b.retail_order_id
        join customer_product_type_rela cptr on b.customer_wallet_id = cptr.customer_wallet_id
        WHERE
        a.examine_status = 'OK'
        and ((b.direct_transfer_status = 1 and b.is_direct_transfer = 1) or b.is_direct_transfer = 0)
        <if test="id != null and id != ''">
            and  a.id = #{id}
        </if>
        <if test="customerId != null and customerId != ''">
            and a.customer_id = #{customerId}
        </if>
        <if test="categoryName != null and categoryName != ''">
            and b.k3_category_name = #{categoryName}
        </if>
        <if test="specification != null and specification != ''">
            and b.specification like concat('%',#{specification},'%')
        </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 cptr.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and b.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

        having refundable_qty > 0
        order by a.create_time desc
    </select>

    <select id="listProjectOrder" resultType="com.zfire.mall.manager.bean.invoice.InvoiceProjectOrder">
        SELECT
        eo.ref_engin_record_no,
        kdm.category_name,
        cptr.service_id,
        eo.customer_id,
        eo.engin_order_id as mainOrderId,
        IF(eo.requisition_status = 1,IF(eo.engin_order_type = 'HOME','REQUISITION_HOME','REQUISITION_TRADE'),eo.engin_order_type) as order_type,
        eo.engin_order_no,
        eo.order_date,
        eoi.id as orderId,
        eoi.sale_type_id,
        eoi.sale_type_name,
        eoi.material_id,
        eoi.material_number as materialCode,
        eoi.material_name,
        eoi.material_old_number,
        eoi.specification,
        eoi.unit,
        eoi.qty,
        (eoi.qty-IFNULL(sum(iao.refundable_qty),0)) as refundable_qty,
        eoi.retired_qty,
        eo.ref_project_name,
        eo.create_time,
        eoi.k3_category_id,
        eoi.remark,
        eoi.remark as invoiceRemark,
        eo.remark as headerRemark,
        eo.ref_project_category,
        eo.ref_trade_category,
        eo.ref_promise_status,
        if(IFNULL(eo.king_dee_id,0) &lt; 1,1,0) as order_base
        FROM
        engin_order eo
        JOIN engin_order_item eoi ON eo.engin_order_id = eoi.parent_id
        JOIN king_dee_material kdm on eoi.material_id = kdm.id
        join customer_product_type_rela cptr on eoi.customer_wallet_id = cptr.customer_wallet_id
        LEFT JOIN invoice_application_order iao on iao.order_id = eoi.id and iao.refundable_qty > 0
        LEFT JOIN invoice_application ia on iao.invoice_id = ia.id and ia.examine_status in ('SAVE','WAIT')
        WHERE
        eo.examine_status = 'OK'
        and eo.is_plan_order = 1
        and ((eoi.direct_transfer_status = 1 and eoi.is_direct_transfer = 1) or eoi.is_direct_transfer = 0)
        <if test="enginOrderNo != null and enginOrderNo != ''">
            and  eo.engin_order_no like concat('%',#{enginOrderNo},'%')
        </if>
        <if test="refProjectName != null and refProjectName != ''">
            and eo.ref_project_name like concat('%',#{refProjectName},'%')
        </if>
        <if test="refEnginRecordNo != null and refEnginRecordNo != ''">
            and eo.ref_engin_record_no like concat('%',#{refEnginRecordNo},'%')
        </if>
        <if test="specification != null and specification != ''">
            and eoi.specification like concat('%',#{specification},'%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and eo.order_date between #{startTime} and #{endTime}
        </if>
        <if test="customerId != null and customerId != ''">
            and eo.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="categoryName != null and categoryName != ''">
            and kdm.category_name = #{categoryName}
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and eoi.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

        GROUP BY eoi.id having refundable_qty > 0
        order by eo.create_time desc
    </select>
    <select id="summary" resultType="com.zfire.mall.manager.bean.pick.PickSummary">
        SELECT
        s.*,
        v.*
        FROM
        (
        SELECT
        COUNT( DISTINCT ia.customer_id ) AS pickOrderCustomNum,
        IF(COUNT(iao.refundable_qty) &lt; 1,0,sum(iao.refundable_qty) ) AS shipOrderNum
        FROM
        pick_manager_ship pms
        LEFT JOIN pick_manager pm ON pms.pick_id = pm.id
        LEFT JOIN invoice_application_order iao ON pms.invoice_order_id = iao.id
        LEFT JOIN invoice_application ia ON ia.id = iao.invoice_id
        WHERE
        pms.`status` = 2
            <if test="startTime != null and startTime != ''">
                and pms.pick_actual_time between #{startTime} and #{endTime}
            </if>
        ) s
        LEFT JOIN (
        SELECT
        count( DISTINCT pm.id ) AS pickOrderNum,
        COUNT( DISTINCT ia.customer_id ) AS pickCustomNum
        FROM
        pick_manager_ship pms
        LEFT JOIN pick_manager pm ON pms.pick_id = pm.id
        LEFT JOIN invoice_application_order iao ON pms.invoice_order_id = iao.id
        LEFT JOIN invoice_application ia ON ia.id = iao.invoice_id
            where 1=1
        <if test="startTime != null and startTime != ''">
            and pm.create_time between #{startTime} and #{endTime}
        </if>
        ) v on 1=1
    </select>

    <select id="summaryHome" resultType="com.zfire.mall.manager.bean.pick.PickSummary">

        SELECT
        IF(COUNT(DISTINCT pm.id) &lt; 1,0,SUM(DISTINCT pm.id)) as shipHomeConditionerNum,
        IF(COUNT(iao.refundable_qty) &lt; 1,0,SUM( iao.refundable_qty)) as shipHomeConditionerTower,
        IF(COUNT(iao.pay_amount) &lt; 1,0,SUM( iao.pay_amount)) as shipHomeConditionerPrice

        FROM
        pick_manager_ship pms
        LEFT JOIN pick_manager pm ON pms.pick_id = pm.id
        LEFT JOIN invoice_application_order iao ON pms.invoice_order_id = iao.id
        LEFT JOIN invoice_application ia ON ia.id = iao.invoice_id
        WHERE
        pms.`status` = 2
        <if test="startTime != null and startTime != ''">
            and pms.pick_actual_time between #{startTime} and #{endTime}
        </if>

    </select>
    <select id="summaryTrade" resultType="com.zfire.mall.manager.bean.pick.PickSummary">

        SELECT
        IF(COUNT(DISTINCT pm.id) &lt; 1,0,SUM(DISTINCT pm.id)) as shipTradeConditionerNum,
        IF(COUNT(iao.refundable_qty) &lt; 1,0,SUM( iao.refundable_qty)) as shipTradeConditionerTower,
        IF(COUNT(iao.pay_amount) &lt; 1,0,SUM( iao.pay_amount)) as shipTradeConditionerPrice

        FROM
        pick_manager_ship pms
        LEFT JOIN pick_manager pm ON pms.pick_id = pm.id
        LEFT JOIN invoice_application_order iao ON pms.invoice_order_id = iao.id
        LEFT JOIN invoice_application ia ON ia.id = iao.invoice_id
        WHERE
        pms.`status` = 2
        <if test="startTime != null and startTime != ''">
            and pms.pick_actual_time between #{startTime} and #{endTime}
        </if>

    </select>
    <select id="summaryLif" resultType="com.zfire.mall.manager.bean.pick.PickSummary">

        SELECT
        IF(COUNT(DISTINCT pm.id) &lt; 1,0,SUM(DISTINCT pm.id)) as pickLifeNum,
        IF(COUNT(iao.refundable_qty) &lt; 1,0,SUM( iao.refundable_qty)) as pickLifeTower,
        IF(COUNT(iao.pay_amount) &lt; 1,0,SUM( iao.pay_amount)) as pickLifePrice

        FROM
        pick_manager_ship pms
        LEFT JOIN pick_manager pm ON pms.pick_id = pm.id
        LEFT JOIN invoice_application_order iao ON pms.invoice_order_id = iao.id
        LEFT JOIN invoice_application ia ON ia.id = iao.invoice_id
        WHERE
        pms.`status` = 2
        <if test="startTime != null and startTime != ''">
            and pms.pick_actual_time between #{startTime} and #{endTime}
        </if>

    </select>
    <select id="selectInvoice" resultType="java.lang.Integer">

        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 ia.type != 2
          and iao.order_id =#{retailId}

    </select>

    <select id="selectInvoiceOk" resultType="java.lang.Integer">

        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','OK')
        and ia.type != 2
          and iao.order_id =#{retailId}

    </select>



    <select id="listInvoiceRetreat" resultType="com.zfire.mall.manager.bean.invoice.InvoiceRetreatOrder">

        SELECT
        ia.id,
        iao.total_amount,
        iao.pay_amount,
        ia.order_time,
        ia.correspond_name,
        ia.customer_name,
        iao.material_code,
        iao.material_old_number,
        iao.material_name,
        iao.specification,
        iao.no_total_amount,
        iao.after_tax_price,
        iao.refundable_qty,
        1 as inOrderType,
        ia.create_time,
        ia.correspond_id,
        iao.main_order_id
        FROM
        invoice_application ia
        LEFT JOIN invoice_application_order iao ON ia.id = iao.invoice_id
        where ia.examine_status = 'OK'
        <if test="mainOrderId != null and mainOrderId != ''">
            and  (iao.main_order_id =  #{mainOrderId} or (SELECT COUNT(1) FROM requisition_order ro where ro.order_no = #{mainOrderId} and iao.requisition_id = ro.id) > 0 > 0)
        </if>
        order by ia.create_time desc
    </select>
    <select id="listInvoiceHuiZong" resultType="com.zfire.mall.manager.bean.invoice.InvoiceBean">
        SELECT
        ia.id,
        ia.order_time,
        ia.customer_number,
        ia.customer_id,
        ia.customer_name,
        ia.remark,
        ia.create_by,
        ia.create_time,
        ia.update_by,
        ia.update_time,
        ia.file_name,
        ia.file_url,
        ia.correspond_id,
        ia.correspond_name,
        ia.approval_name,
        ia.approval_time,
        ia.approval_remark,
        ia.correspond_user_name,
        ia.engin_info_no,
        ia.examine_status,
        ia.close_name,
        ia.engin_order_no,
        ia.engin_order_type,
        ia.file_no,
        ia.type,
        ia.sales_order_id,
        ia.stock_id,
        ia.stock_number,
        ia.stock_name,
        ia.sales_id,
        ia.gejiang_sync,
        ia.gejiang_sync_remark,
        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.print_num,
        iao.header_remark,
        iao.material_group_name,

        concat(ia.admin_area,'区域') as admin_area,
        if(iao.service_name is null or iao.service_name='','',CONCAT(iao.service_name,' 区域','')) as service_area,
        month(ia.create_time)  as month,
        iao.k3_service_id,
        iao.k3_service_name,
        iao.customer_wallet_id,
        iao.customer_wallet_name,
        iao.customer_wallet_name2,
        iao.customer_wallet_id2,
        iao.file_no as itemFileNo,
        ia.sales_examine_status,
        ia.parent_id,
        eo.order_date,
        eo.examine_status as enginExamineStatus,
        eo.ref_project_category,
        eo.ref_trade_category,
        eo.ref_region_work,
        eo.ref_factory_no,
        eo.ref_install_address,
        eo.ref_linkman,
        eo.ref_tel,
        eo.ref_phone,
        eo.take_deposit,
        eo.ref_project_note,
        eo.ref_project_no,
        eo.ref_project_type,
        eo.ge_li_iner_note,
        eo.ref_declare_no,
        eo.create_time as engin_create_time,
        eo.file_no as engin_file_no,
        eo.remark as engin_remark,
        eo.create_by as engin_create_by,
        eo.confirm_by,
        eo.confirm_time,
        eo.confirm_name,
        eo.close_time,
        eo.examine_note,
        eo.ref_engin_record_no,
        eo.ref_power_category,
        eo.ref_machine_type,
        eo.ref_project_name,
        iao.print_status,
        ia.second_customer_number,
        iao.category_name,
        iao.k3_category_name,
        iao.material_group_name,
        cptr.k3_org_number
        FROM
        invoice_application_order iao
        JOIN invoice_application ia ON ia.id = iao.invoice_id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        left join engin_order eo on eo.engin_order_no = ia.engin_order_no
        where 1=1
        and ia.examine_status = 'OK'
        <if test="id != null and id!= ''">
            and ia.id = #{id}
        </if>
           <if test="discriminate != null and discriminate!= ''">
            and ia.id  like concat('%',#{discriminate},'%')
        </if>
       <if test="invoicePrintStatus != null ">
        <if test="invoicePrintStatus == 1 ">
            and iao.print_num  > 0
        </if>
           <if test="invoicePrintStatus == 0 ">
               and iao.print_num  = 0
           </if>
        </if>
              <if test="refEnginRecordNo != null and refEnginRecordNo!= ''">
            and ia.ref_engin_record_no  like concat('%',#{refEnginRecordNo},'%')
        </if>
         <if test="materialOldNumber != null and materialOldNumber!= ''">
            and iao.material_old_number = #{materialOldNumber}
        </if>
          <if test="createBy != null and createBy!= ''">
            and ia.create_by like concat('%',#{createBy},'%')
        </if>
             <if test="printStatus != null">
            and iao.print_status = #{printStatus}
        </if>
        <if test="type != null and type != ''">
            and ia.type = #{type}
        </if>
        <if test="customerName != null and customerName!= ''">
            and ia.customer_name like concat('%',#{customerName},'%')
        </if>
          <if test="mainOrderId != null and mainOrderId!= ''">
            and (iao.main_order_id like concat('%',#{mainOrderId},'%') or ia.engin_order_no like concat('%',#{mainOrderId},'%') )
        </if>
        <if test="correspondId != null and correspondId.size > 0">
            AND ia.correspond_id in
            <foreach item="item" index="index" collection="correspondId" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="correspondName != null and correspondName != ''">
            and ia.correspond_name like concat('%',#{correspondName},'%')
        </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 ia.order_time BETWEEN #{startTime} and #{endTime}
        </if>
               <if test="approvalStartTime != null and approvalStartTime!= ''">
            and ia.approval_time BETWEEN #{approvalStartTime} and #{approvalEndTime}
        </if>
        <if test="customerNumber != null and customerNumber!= ''">
            and ia.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="materialNumber != null and materialNumber!= ''">
            and iao.material_code like concat('%',#{materialNumber},'%')
        </if>
        <if test="examineStatus != null and examineStatus!= ''">
            and ia.examine_status = #{examineStatus}
        </if>
        <if test="customerId != null and customerId != ''">
            and ia.customer_id = #{customerId}
        </if>
        <if test="createStartTime != null and createStartTime!= ''">
            and ia.create_time BETWEEN #{createStartTime} and #{createEndTime}
        </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="categoryName != null and categoryName != ''">
            and iao.category_name = #{categoryName}
        </if>
            <if test="categoryId != null and categoryId != ''">
            and iao.category_id = #{categoryId}
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        order by ia.create_time desc

    </select>
    <select id="listSaleOrder" resultType="com.zfire.mall.manager.bean.sale.SaleOrderBean">
        SELECT
        so.id,
        so.order_time,
        so.customer_number,
        so.customer_id,
        so.customer_name,
        so.remark,
        so.create_by,
        so.create_time,
        so.update_by,
        so.update_time,
        so.file_name,
        so.file_url,
        so.correspond_id,
        so.correspond_name,
        so.approval_name,
        so.approval_time,
        so.approval_remark,
        so.correspond_user_name,
        so.engin_info_no,
        so.examine_status,
        so.close_name,
        so.close_time,
        so.engin_order_no,
        so.ref_project_name,
        so.ref_use_unit,
        so.engin_order_type,
        so.ref_factory_no,
        so.ref_install_address,
        so.ref_linkman,
        so.ref_phone,
        so.ref_tel,
        so.order_no,
        so.bill_receipt,
        iao.id as sales_order_item_id,
        iao.main_order_id,
        iao.order_id,
        iao.invoice_id,
        iao.invoice_num,
        iao.approval_number,
        (iao.refundable_qty   + IFNULL(sum(iaom.refundable_qty),0)) as refundable_qty,
        iao.retired_qty,
        iao.material_id,
        iao.order_type,
        iao.sale_type_id,
        iao.sale_type_code,
        iao.sale_type_name,
        iao.the_time,
        iao.material_old_number,
        iao.material_code,
        iao.material_name,
        iao.specification,
        iao.unit,
        iao.remark as invoice_remark,
        iao.price,
        iao.pay_amount,
        iao.tax,
        iao.qty,
        iao.after_tax_price,
        iao.no_total_amount,
        iao.pay_rebate_amount,
        iao.total_disc_amount,
        iao.refund_status,
        iao.single_pay_price,
        iao.single_deposit_amount,
        iao.service_id,
        iao.service_name,
        iao.disc_amount,
        iao.king_dee_seq,
        iao.king_dee_entry_id,
        iao.king_sale_id,
        iao.king_dee_order_seq,
        iao.king_dee_order_id,
        iao.king_bill_no,
        iao.total_amount,
        iao.single_rebate_amount,
        iao.policy_id,
        iao.policy_title,
        iao.policy_code,
        iao.sales_order_no,
        kdm.category_name,
        iao.header_remark,
        so.ref_engin_record_no
        FROM
        sales_order so
        JOIN sales_order_item iao ON so.id = iao.sales_order_no
        JOIN king_dee_material kdm on iao.material_id = kdm.id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        LEFT JOIN invoice_application_order iaom ON iao.id = iaom.sales_order_item_id and iaom.invoice_examine_status != 'OK'
        where 1=1
        and so.examine_status != 'OK'
        and so.type = 1


        <if test="saleType != null and saleType == 1">
            and iao.order_type in ('RETAIL','RETAIL_POLICY','PERMU_RETAIL','PERMU_RETAIL_POLICY','REQUISITION_RETAIL','REQUISITION_RETAIL_POLICY')
        </if>
        <if test="saleType != null and saleType == 2">
            and iao.order_type in ('TRADE','HOME','PERMU_HOME','PERMU_TRADE','REQUISITION_TRADE','REQUISITION_HOME')
        </if>
        <if test="mainOrderId != null and mainOrderId != ''">
            and (iao.main_order_id like concat('%',#{mainOrderId},'%') or so.engin_order_no  like concat('%',#{mainOrderId},'%'))
        </if>
        <if test="id != null and id != ''">
            and so.id like concat('%',#{id},'%')
        </if>
        <if test="orderNo != null and orderNo != ''">
            and so.order_no like concat('%',#{orderNo},'%')
        </if>
             <if test="categoryName != null and categoryName != ''">
            and kdm.category_name like concat('%',#{categoryName},'%')
        </if>
        <if test="enginOrderNo != null and enginOrderNo != ''">
            and so.engin_order_no like concat('%',#{enginOrderNo},'%')
        </if>
        <if test="refEnginRecordNo != null and refEnginRecordNo != ''">
            and so.ref_engin_record_no like concat('%',#{refEnginRecordNo},'%')
        </if>
        <if test="refProjectName != null and refProjectName != ''">
            and so.ref_project_name like concat('%',#{refProjectName},'%')
        </if>
        <if test="customerName != null and customerName != ''">
            and so.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="correspondId != null and correspondId != ''">
            and so.correspond_id = #{correspondId}
        </if>
        <if test="specification != null and specification != ''">
            and iao.specification like concat('%',#{specification},'%')
        </if>
        <if test="customerNumber != null and customerNumber != ''">
            and so.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="materialName != null and materialName != ''">
            and iao.material_name like concat('%',#{materialName},'%')
        </if>
        <if test="correspondName != null and correspondName != ''">
            and so.correspond_name like concat('%',#{correspondName},'%')
        </if>
        <if test="materialNumber != null and materialNumber != ''">
            and iao.material_code like concat('%',#{materialNumber},'%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and so.order_time between #{startTime} and #{endTime}
        </if>
        <if test="customerId != null and customerId != ''">
            and so.customer_id = #{customerId}
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and cptr.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY iao.id HAVING refundable_qty >0
        order by so.create_time desc
    </select>

    <select id="selectReserved" resultType="int">
        select IFNULL(SUM(roi.reserved_num),0) from reserved_order_item roi where #{retailId} = roi.order_id and roi.status = 1
    </select>
    <select id="listInvoiceNumber" resultType="com.zfire.mall.manager.bean.invoice.InvoiceNumber">
        SELECT
        ifnull(SUM(iao.refundable_qty),0) as invoiceNumber,
        ifnull(SUM(iao.pay_amount),0) as payAmount
        FROM
        invoice_application ia
        JOIN invoice_application_order iao ON ia.id = iao.invoice_id
        JOIN king_dee_material kdm ON kdm.id = iao.material_id
        join admin_user au on au.admin_user_id = iao.service_id
        JOIN sales_order so on so.id = ia.sales_order_id
        join king_dee_customer kdc on kdc.id = ia.customer_id
        left join engin_order eo on eo.engin_order_no = ia.engin_order_no
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id

        where 1=1
        and ia.examine_status = 'OK'
        <if test="id != null and id!= ''">
            and ia.id = #{id}
        </if>
        <if test="invoicePrintStatus != null ">
            <if test="invoicePrintStatus == 1 ">
                and iao.print_num  > 0
            </if>
            <if test="invoicePrintStatus == 0 ">
                and iao.print_num  &lt; 0
            </if>
        </if>
             <if test="discriminate != null and discriminate!= ''">
            and ia.id like concat('%',#{discriminate},'%')
        </if>
        <if test="refEnginRecordNo != null and refEnginRecordNo!= ''">
            and ia.ref_engin_record_no  like concat('%',#{refEnginRecordNo},'%')
        </if>
        <if test="materialOldNumber != null and materialOldNumber!= ''">
            and iao.material_old_number = #{materialOldNumber}
        </if>
        <if test="printStatus != null">
            and pms.status = #{printStatus}
        </if>
           <if test="createBy != null and createBy!= ''">
            and ia.create_by like concat('%',#{createBy},'%')
        </if>
        <if test="customerName != null and customerName!= ''">
            and ia.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="mainOrderId != null and mainOrderId!= ''">
            and (iao.main_order_id like concat('%',#{mainOrderId},'%') or ia.engin_order_no like concat('%',#{mainOrderId},'%') )
        </if>
        <if test="correspondId != null and correspondId.size > 0">
            AND ia.correspond_id in
            <foreach item="item" index="index" collection="correspondId" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="correspondName != null and correspondName != ''">
            and ia.correspond_name like concat('%',#{correspondName},'%')
        </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 ia.order_time BETWEEN #{startTime} and #{endTime}
        </if>
        <if test="approvalStartTime != null and approvalStartTime!= ''">
            and ia.approval_time BETWEEN #{approvalStartTime} and #{approvalEndTime}
        </if>
        <if test="customerNumber != null and customerNumber!= ''">
            and ia.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="materialNumber != null and materialNumber!= ''">
            and kdm.number like concat('%',#{materialNumber},'%')
        </if>
        <if test="examineStatus != null and examineStatus!= ''">
            and ia.examine_status = #{examineStatus}
        </if>
        <if test="customerId != null and customerId != ''">
            and ia.customer_id = #{customerId}
        </if>
        <if test="createStartTime != null and createStartTime!= ''">
            and ia.create_time BETWEEN #{createStartTime} and #{createEndTime}
        </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="categoryName != null and categoryName != ''">
            and kdm.category_name = #{categoryName}
        </if>
        <if test="categoryId != null and categoryId != ''">
            and kdm.category_id = #{categoryId}
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        order by ia.create_time desc
    </select>
    <select id="selectSalesNum" resultType="java.lang.Integer">
             SELECT
            IFNULL(SUM(iao.refundable_qty),0)
        FROM
            sales_order ia
                LEFT JOIN sales_order_item iao on ia.id = iao.invoice_id
        where ia.examine_status	in ('SAVE','WAIT')
        and ia.type != 2
          and iao.order_id =#{retailId}
    </select>
    <select id="listInvoiceHuiZongCount" resultType="java.lang.Integer">
        SELECT
        COUNT(1)
        FROM
        invoice_application_order iao
        JOIN invoice_application ia ON ia.id = iao.invoice_id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        left join engin_order eo on eo.engin_order_no = ia.engin_order_no
        where 1=1
        and ia.examine_status = 'OK'
        <if test="id != null and id!= ''">
            and ia.id = #{id}
        </if>
        <if test="discriminate != null and discriminate!= ''">
            and ia.id  like concat('%',#{discriminate},'%')
        </if>
        <if test="invoicePrintStatus != null ">
            <if test="invoicePrintStatus == 1 ">
                and iao.print_num  > 0
            </if>
            <if test="invoicePrintStatus == 0 ">
                and iao.print_num  = 0
            </if>
        </if>
        <if test="refEnginRecordNo != null and refEnginRecordNo!= ''">
            and ia.ref_engin_record_no  like concat('%',#{refEnginRecordNo},'%')
        </if>
        <if test="materialOldNumber != null and materialOldNumber!= ''">
            and iao.material_old_number = #{materialOldNumber}
        </if>
        <if test="createBy != null and createBy!= ''">
            and ia.create_by like concat('%',#{createBy},'%')
        </if>
        <if test="printStatus != null">
            and iao.print_status = #{printStatus}
        </if>
        <if test="type != null and type != ''">
            and ia.type = #{type}
        </if>
        <if test="customerName != null and customerName!= ''">
            and ia.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="mainOrderId != null and mainOrderId!= ''">
            and (iao.main_order_id like concat('%',#{mainOrderId},'%') or ia.engin_order_no like concat('%',#{mainOrderId},'%') )
        </if>
        <if test="correspondId != null and correspondId.size > 0">
            AND ia.correspond_id in
            <foreach item="item" index="index" collection="correspondId" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="correspondName != null and correspondName != ''">
            and ia.correspond_name like concat('%',#{correspondName},'%')
        </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 ia.order_time BETWEEN #{startTime} and #{endTime}
        </if>
        <if test="approvalStartTime != null and approvalStartTime!= ''">
            and ia.approval_time BETWEEN #{approvalStartTime} and #{approvalEndTime}
        </if>
        <if test="customerNumber != null and customerNumber!= ''">
            and ia.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="materialNumber != null and materialNumber!= ''">
            and iao.material_code like concat('%',#{materialNumber},'%')
        </if>
        <if test="examineStatus != null and examineStatus!= ''">
            and ia.examine_status = #{examineStatus}
        </if>
        <if test="customerId != null and customerId != ''">
            and ia.customer_id = #{customerId}
        </if>
        <if test="createStartTime != null and createStartTime!= ''">
            and ia.create_time BETWEEN #{createStartTime} and #{createEndTime}
        </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="categoryName != null and categoryName != ''">
            and iao.category_name = #{categoryName}
        </if>
        <if test="categoryId != null and categoryId != ''">
            and iao.category_id = #{categoryId}
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

    </select>

    <update id="printAdd">
        UPDATE invoice_application_order
        SET print_num = print_num + 1,
        invoice_print_status = 1
        WHERE
        id IN
        <foreach item="item" collection="list" separator="," open="(" close=")" index="">
            #{item}
        </foreach>
    </update>


    <update id="updateStatus">
      UPDATE invoice_application_order iao
        LEFT JOIN invoice_application ia ON iao.invoice_id = ia.id
        SET iao.invoice_examine_status = ia.examine_status,
			iao.ref_engin_record_no = ia.ref_engin_record_no
        where ia.id = #{id}
    </update>

    <update id="updateInvoice">
        UPDATE invoice_application ia
        JOIN king_dee_customer kdc ON ia.customer_id = kdc.id
        JOIN invoice_application_order iao ON iao.invoice_id = ia.id
        JOIN king_dee_material kdm ON iao.material_id = kdm.id
        LEFT JOIN retail_order_item roi ON roi.id = iao.order_id
        LEFT JOIN engin_order_item eoi ON eoi.id = iao.order_id
        SET ia.parent_id = kdc.parent_id,
        ia.sales_order_id = ia.sales_id,
        ia.second_customer_number = kdc.second_customer_number,
        iao.category_id = kdm.id,
        iao.category_name = kdm.category_name,
        iao.category_number = kdm.category_number,
        iao.goods_order_id = IF(roi.retail_order_id is NULL,eoi.engin_order_no,roi.retail_order_id),
        iao.material_group_name = kdm.material_group_name
        where ia.id = #{id}

    </update>
    <update id="updateWithold">
        <if test="retailUpdate != null and retailUpdate == 'RETAIL'">
            update retail_order_item roi
            join invoice_application_order iao on roi.id = iao.order_id and iao.invoice_id = #{id}
            set roi.withhold = IFNULL((select sum(iao.refundable_qty) FROM invoice_application_order iao where roi.id = iao.order_id and iao.refundable_qty > 0
            and iao.invoice_examine_status in ('SAVE','WAIT')),0)
        </if>
        <if test="retailUpdate != null and retailUpdate == 'ENGIN'">
            update engin_order_item roi
            join invoice_application_order iao on roi.id = iao.order_id and iao.invoice_id = #{id}
            set roi.withhold = IFNULL((select sum(iao.refundable_qty) FROM invoice_application_order iao where roi.id = iao.order_id and iao.refundable_qty > 0
            and iao.invoice_examine_status in ('SAVE','WAIT')),0)
        </if>

    </update>
    <update id="updateWitholdOrder">
        <if test="retailUpdate != null and retailUpdate == 'RETAIL'">
            update retail_order_item roi
            set roi.withhold = IFNULL((select sum(iao.refundable_qty) FROM invoice_application_order iao where roi.id = iao.order_id and iao.refundable_qty > 0
            and iao.invoice_examine_status in ('SAVE','WAIT')),0) where roi.id =  #{orderId}

        </if>
        <if test="retailUpdate != null and retailUpdate == 'ENGIN'">
            update engin_order_item roi
            set roi.withhold = IFNULL((select sum(iao.refundable_qty) FROM invoice_application_order iao where roi.id = iao.order_id and iao.refundable_qty > 0
            and iao.invoice_examine_status in ('SAVE','WAIT')),0) where roi.id =  #{orderId}
        </if>
    </update>
    <update id="updateSalseOrderId">
    UPDATE sales_order_item iao
            join invoice_application_order iaor on iao.invoice_item_id = iaor.id
        set iao.goods_order_id = iaor.goods_order_id  where iao.invoice_id = #{id};
    </update>
    <update id="updatePick">
         UPDATE pick_manager_ship ia


        SET
        ia.goods_order_id = IF(ia.engin_order_no is NULL,ia.main_order_id,ia.engin_order_no)

        where ia.goods_order_id is null and ia.pick_id = #{id}
    </update>
    <update id="updateTaiZhang">
        UPDATE customer_wallet_record a
JOIN invoice_application b on a.bill_no = b.id
set a.engin_record_no  = b.ref_engin_record_no where a.bill_no = #{id}
    </update>
    <update id="updateSaleTaiZhang">
       update customer_wallet_record a
JOIN sales_order b on (a.bill_no = b.id or a.bill_no = b.engin_order_no)

set a.engin_record_no  = b.ref_engin_record_no where a.bill_no = #{id} and a.engin_record_no = '';



    </update>
    <update id="updateSaleOrderTaiZhang">
        update customer_wallet_record a
JOIN sales_order b on (a.bill_no = b.id or a.bill_no = b.engin_order_no)
set a.engin_record_no  = b.ref_engin_record_no where a.bill_no = #{enginOrderNo} and a.engin_record_no = ''
    </update>


    <resultMap id="BaseResultListPushMap" type="com.zfire.mall.manager.bean.pick.PickManagerPushBean">
        <id column="saleCode" jdbcType="VARCHAR" property="saleCode"/>
        <result column="orderTime" jdbcType="VARCHAR" property="orderTime"/>
        <result column="warehouseFlag" jdbcType="VARCHAR" property="warehouseFlag"/>
        <result column="totalAmount" jdbcType="VARCHAR" property="totalAmount"/>
        <result column="deliverWarehouseId" jdbcType="VARCHAR" property="deliverWarehouseId"/>
        <result column="deliverWarehouse" jdbcType="VARCHAR" property="deliverWarehouse"/>
        <result column="merchantCode" jdbcType="VARCHAR" property="merchantCode"/>
        <result column="merchantName" jdbcType="VARCHAR" property="merchantName"/>
        <result column="goodsDeliveringTime" jdbcType="VARCHAR" property="goodsDeliveringTime"/>
        <result column="goodsPickingKey" jdbcType="VARCHAR" property="goodsPickingKey"/>
        <result column="goodsReceiptingCustomer" jdbcType="VARCHAR" property="goodsReceiptingCustomer"/>
        <result column="phone" jdbcType="VARCHAR" property="phone"/>
        <result column="goodsReceiptingAddress" jdbcType="VARCHAR" property="goodsReceiptingAddress"/>
        <result column="otherNotes" jdbcType="TIMESTAMP" property="otherNotes"/>
        <result column="orderNotes" jdbcType="VARCHAR" property="orderNotes"/>
        <result column="refProjectCategory" jdbcType="VARCHAR" property="refProjectCategory"/>
        <result column="customerOrderId" jdbcType="VARCHAR" property="customerOrderId"/>
        <collection property="goods" ofType="com.zfire.mall.manager.bean.pick.PickManagerPushGoodsBean" select="goods" column="saleCode">
            <id column="id" jdbcType="VARCHAR" property="machineSetCode"/>
            <result column="specification" jdbcType="VARCHAR" property="specification"/>
            <result column="goodsAmount" jdbcType="VARCHAR" property="goodsAmount"/>
            <result column="otherNotes" jdbcType="VARCHAR" property="otherNotes"/>
            <result column="goodsVolume" jdbcType="VARCHAR" property="goodsVolume"/>
            <result column="totalVolume" jdbcType="VARCHAR" property="totalVolume"/>
            <result column="serviceId" jdbcType="VARCHAR" property="serviceId"/>
            <result column="serviceName" jdbcType="VARCHAR" property="serviceName"/>
            <result column="saleType" jdbcType="VARCHAR" property="saleType"/>
        </collection>
    </resultMap>


    <select id="listPush"  resultMap="BaseResultListPushMap">
        SELECT
        pmp.invoice_id as  saleCode,
        unix_timestamp(ia.order_time) as orderTime,
        ia.order_time,
        pmp.warehouse_flag as warehouseFlag,
        sum(iao.refundable_qty) as totalAmount,
        ia.correspond_id as deliverWarehouseId,
        ia.correspond_name as deliverWarehouse,
        ia.customer_number as merchantCode,
        ia.customer_name as merchantName,
        ia.ref_project_category as refProjectCategory,
        unix_timestamp(pmp.pick_time) as goodsDeliveringTime,
        pmp.invoice_id as goodsPickingKey,
        pmp.receiving_name as goodsReceiptingCustomer,
        pmp.phone as phone,
        pmp.address as goodsReceiptingAddress,
        pmp.remark as otherNotes,
        pmp.status as status,
        pmp.customer_order_id as customerOrderId,
        ia.remark as orderNotes,
        pmp.is_up,
        pmp.is_discharge
        FROM
        pick_manager_push pmp
        join invoice_application ia on pmp.invoice_id = ia.id
        join invoice_application_order iao on ia.id = iao.invoice_id
        where 1=1
        and pmp.sync_status in ('WAIT','FAIL')
        <if test="syncTime != null and syncTime != ''">
            and pmp.update_time >= #{syncTime}
        </if>
        	GROUP BY pmp.invoice_id
    </select>


    <select id="goods"  resultType="com.zfire.mall.manager.bean.pick.PickManagerPushGoodsBean">
      SELECT
	iao.material_code as  machineSetCode,
	iao.material_old_number as  machineSetOldCode,
	iao.specification as  specification,
	iao.refundable_qty+iao.retired_qty as  goodsAmount,
	iao.remark as  otherNotes,
	iao.service_name as  serviceName,
	iao.service_id as  serviceId,
	      iao.sale_type_name as saleType
    FROM
	invoice_application_order iao
	where iao.invoice_id = #{id}
    </select>
    <select id="listInvoiceOrder" resultType="com.zfire.mall.manager.bean.invoice.InvoiceApplicationPick">
        SELECT
        ia.*,
        pms.receiving_name,
        pms.address,
        pms.phone
        FROM
        invoice_application_order iao
        JOIN invoice_application ia ON ia.id = iao.invoice_id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        join pick_manager_ship pms on pms.invoice_order_id = iao.id
        where 1=1
	and ia.order_track_time >= #{date}
	and ia.order_track_type = 'YES'
        <if test="customerId != null and customerId != ''">
            and ia.customer_id = #{customerId}
        </if>
        <if test="invoiceId != null and invoiceId != ''">
            and ia.id = #{invoiceId}
        </if>
        <if test="userName != null and userName != ''">
            and pms.receiving_name like concat('%',#{userName},'%')
        </if>
      <if test="remark != null and remark != ''">
            and ia.order_track_status like concat('%',#{remark},'%')
        </if>
          <if test="address != null and address != ''">
            and pms.address like concat('%',#{address},'%')
        </if>
        <if test="startTime != null and startTime != ''">
            and ia.order_track_time between  #{startTime} and #{endTime}
        </if>
      <if test="serviceIds != null and serviceIds.size > 0">
          and cptr.service_id in
          <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                   close=")">
              #{item}
          </foreach>
      </if>

      <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
          and iao.k3_category_id in
          <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                   close=")">
              #{item}
          </foreach>
      </if>
      group by ia.id order by ia.order_track_time desc

    </select>

    <select id="huiZongList" resultType="com.zfire.mall.manager.bean.invoice.InvoiceVO">
         SELECT
        ia.id,
        iao.id as itemId,
        ia.order_time,
        ia.customer_number,
        ia.customer_id,
        ia.customer_name,
        ia.remark,
        ia.create_by,
        ia.create_time,
        ia.update_by,
        ia.update_time,
        ia.file_name,
        ia.file_url,
        ia.correspond_id,
        ia.correspond_name,
        ia.approval_name,
        ia.approval_time,
        ia.approval_remark,
        ia.correspond_user_name,
        ia.engin_info_no,
        ia.examine_status,
        ia.close_name,
        ia.engin_order_no,
        ia.engin_order_type,
        ia.file_no,
        ia.type,
        ia.sales_order_id,
        ia.stock_id,
        ia.stock_number,
        ia.stock_name,
        ia.sales_id,
        ia.gejiang_sync,
        ia.gejiang_sync_remark,
        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.pay_amount as payAllAmount,
        iao.tax,
        iao.qty,
        iao.after_tax_price,
        iao.no_total_amount,
        iao.pay_rebate_amount,
        iao.total_disc_amount,
        iao.total_disc_amount + iao.pay_rebate_amount  as payDiscAmount,
        iao.refund_status,
        iao.single_pay_price,
        iao.single_deposit_amount,
        iao.service_id,
        iao.service_name,
        iao.disc_amount,
        iao.goods_order_id,
        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.print_num,
        iao.header_remark,
        iao.material_group_name,
        iao.invoice_print_status,

        concat(ia.admin_area,'区域') as admin_area,
        if(iao.service_name is null or iao.service_name='','',CONCAT(iao.service_name,' 区域','')) as service_area,
        month(ia.create_time)  as month,
        iao.k3_service_id,
        iao.k3_service_name,
        iao.customer_wallet_id,
        iao.customer_wallet_name,
        iao.customer_wallet_name2,
        iao.customer_wallet_id2,
        iao.file_no as itemFileNo,
        ia.sales_examine_status,
        ia.parent_id,
        ia.engin_order_date as order_date,
        ia.examine_status as enginExamineStatus,
        ia.ref_project_category,
        ia.ref_trade_category,
        ia.ref_region_work,
        ia.ref_factory_no,
        ia.ref_install_address,
        ia.ref_linkman,
        ia.ref_tel,
        ia.ref_phone,
        ia.automatic_status,
        ia.take_deposit,
        ia.ref_project_note,
        ia.ref_project_no,
        ia.ref_project_type,
        ia.ge_li_iner_note,
        ia.ref_declare_no,
        ia.create_time as engin_create_time,
        ia.file_no as engin_file_no,
        ia.remark as engin_remark,
        ia.create_by as engin_create_by,
        ia.confirm_by,
        ia.confirm_time,
        ia.confirm_name,
        ia.close_time,
        ia.examine_note,
        ia.ref_engin_record_no,
        ia.ref_power_category,
        ia.ref_machine_type,
        ia.ref_project_name,
        iao.print_status,
        ia.second_customer_number,
        iao.category_name,
        iao.k3_category_name,
        iao.material_group_name,
        ia.ref_use_unit,
        if(ia.sales_examine_status = 'OK',0,iao.refundable_qty) as salesQty,
        cptr.k3_org_number
        FROM
        invoice_application_order iao
        JOIN invoice_application ia ON ia.id = iao.invoice_id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        where 1=1
        <if test="collect != null and collect.size > 0">
            and iao.id in
            <foreach item="item" index="index" collection="collect" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        order by ia.create_time desc
    </select>

    <select id="listV2" resultType="com.zfire.mall.manager.bean.invoice.InvoiceVO">
        SELECT

        iao.id as itemId

        FROM
        invoice_application_order iao
        JOIN invoice_application ia ON ia.id = iao.invoice_id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        ${ex.query}
        and ia.examine_status = 'OK'

        <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="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

        <if test="ex.orderBy == null or ex.orderBy ==''">
            order by ia.create_time desc
        </if>
        ${ex.orderBy}
    </select>
    <select id="listCountV2" resultType="java.lang.Integer">
        SELECT
        COUNT(1)
        FROM
        invoice_application_order iao
        JOIN invoice_application ia ON ia.id = iao.invoice_id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        left join engin_order eo on eo.engin_order_no = ia.engin_order_no

        ${ex.query}
        and ia.examine_status = 'OK'

        <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="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

    </select>
    <select id="listRetailV2" resultType="com.zfire.mall.manager.bean.invoice.InvoiceRetailVO">
        SELECT
        iao.id as itemId
        FROM
        invoice_application_order iao
        JOIN invoice_application ia ON ia.id = iao.invoice_id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        ${ex.query}
        and iao.order_type in ('RETAIL','RETAIL_POLICY','PERMU_RETAIL','PERMU_RETAIL_POLICY','REQUISITION_RETAIL','REQUISITION_RETAIL_POLICY')
        <if test="customerId != null and customerId != ''">
            and ia.customer_id = #{customerId}
        </if>
        <if test="integer != null and integer != ''">
            and ia.examine_status = 'OK'
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and cptr.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

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

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

    <select id="retailList" resultType="com.zfire.mall.manager.bean.invoice.InvoiceRetailVO">
        SELECT
        ia.id,
        ia.order_time,
        ia.customer_number,
        ia.customer_id,
        ia.customer_name,
        ia.remark,
        ia.create_by,
        ia.create_time,
        ia.update_by,
        ia.update_time,
        ia.file_name,
        ia.file_url,
        ia.correspond_id,
        ia.correspond_name,
        ia.approval_name,
        ia.approval_time,
        ia.approval_remark,
        ia.correspond_user_name,
        ia.engin_info_no,
        ia.examine_status,
        ia.close_name,
        ia.engin_order_no,
        ia.engin_order_type,
        ia.file_no,
        ia.type,
        ia.sales_order_id,
        ia.stock_id,
        ia.stock_number,
        ia.stock_name,
        ia.sales_id,
        ia.automatic_status,
        ia.gejiang_sync,
        ia.gejiang_sync_remark,
        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.pay_amount as payAllAmount,
        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.print_num,
        iao.header_remark,
        iao.material_group_name,
        iao.goods_order_id,
        iao.total_disc_amount +iao.pay_rebate_amount as payDiscAmount ,
        concat(ia.admin_area,'区域') as admin_area,
        if(iao.service_name is null or iao.service_name='','',CONCAT(iao.service_name,' 区域','')) as service_area,
        month(ia.create_time)  as month,
        iao.k3_service_id,
        iao.k3_service_name,
        iao.customer_wallet_id,
        iao.customer_wallet_name,
        iao.customer_wallet_name2,
        iao.customer_wallet_id2,
        iao.file_no as itemFileNo,
        ia.sales_examine_status,
        ia.parent_id,
        iao.print_status,
        ia.second_customer_number,
        iao.category_name,
        iao.k3_category_name,
        iao.material_group_name,
        cptr.k3_org_number,
        iao.id as itemId
        FROM
        invoice_application_order iao
        JOIN invoice_application ia ON ia.id = iao.invoice_id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        where 1=1
        <if test="collect != null and collect.size > 0">
            and iao.id in
            <foreach item="item" index="index" collection="collect" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

        order by ia.create_time desc

    </select>

    <select id="projectList" resultType="com.zfire.mall.manager.bean.invoice.InvoiceProjectVO">
        SELECT
        ia.id,
        ia.order_time,
        ia.customer_number,
        ia.customer_id,
        ia.customer_name,
        ia.remark,
        ia.create_by,
        ia.create_time,
        ia.update_by,
        ia.update_time,
        ia.file_name,
        ia.file_url,
        ia.correspond_id,
        ia.correspond_name,
        ia.approval_name,
        ia.approval_time,
        ia.approval_remark,
        ia.correspond_user_name,
        ia.engin_info_no,
        ia.examine_status,
        ia.close_name,
        ia.engin_order_no,
        ia.engin_order_type,
        ia.file_no,
        ia.type,
        ia.sales_order_id,
        ia.stock_id,
        ia.stock_number,
        ia.stock_name,
        ia.sales_id,
        ia.automatic_status,
        ia.gejiang_sync,
        ia.gejiang_sync_remark,
        iao.main_order_id,
        iao.order_id,
        iao.id as itemId,
        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.pay_amount as payAllAmount,
        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.print_num,
        iao.header_remark,
        iao.material_group_name,
        iao.goods_order_id,
        iao.total_disc_amount +iao.pay_rebate_amount as payDiscAmount ,
        concat(ia.admin_area,'区域') as admin_area,
        if(iao.service_name is null or iao.service_name='','',CONCAT(iao.service_name,' 区域','')) as service_area,
        month(ia.create_time)  as month,
        iao.k3_service_id,
        iao.k3_service_name,
        iao.customer_wallet_id,
        iao.customer_wallet_name,
        iao.customer_wallet_name2,
        iao.customer_wallet_id2,
        iao.file_no as itemFileNo,
        ia.sales_examine_status,
        ia.parent_id,
        ia.engin_order_date as order_date,
        ia.examine_status as enginExamineStatus,
        ia.ref_project_category,
        ia.ref_trade_category,
        ia.ref_region_work,
        ia.ref_factory_no,
        ia.ref_install_address,
        ia.ref_linkman,
        ia.ref_tel,
        ia.ref_phone,
        ia.take_deposit,
        ia.ref_project_note,
        ia.ref_project_no,
        ia.ref_project_type,
        ia.ge_li_iner_note,
        ia.ref_declare_no,
        ia.create_time as engin_create_time,
        ia.engin_file_no as engin_file_no,
        ia.remark as engin_remark,
        ia.create_by as engin_create_by,
        ia.confirm_by,
        ia.confirm_time,
        ia.confirm_name,
        ia.close_time,
        ia.examine_note,
        ia.ref_engin_record_no,
        ia.ref_power_category,
        ia.ref_machine_type,
        ia.ref_project_name,
        iao.print_status,
        ia.second_customer_number,
        iao.category_name,
        iao.k3_category_name,
        iao.material_group_name,
        ia.ref_use_unit,
        cptr.k3_org_number
        FROM
        invoice_application_order iao
        JOIN invoice_application ia ON ia.id = iao.invoice_id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        where 1=1
        <if test="collect != null and collect.size > 0">
            and iao.id in
            <foreach item="item" index="index" collection="collect" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
            order by ia.create_time desc

    </select>


    <select id="listProjectV2" resultType="com.zfire.mall.manager.bean.invoice.InvoiceProjectVO">
        SELECT
        iao.id as itemId
        FROM
        invoice_application_order iao
        JOIN invoice_application ia ON ia.id = iao.invoice_id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        ${ex.query}
        and iao.order_type in ('TRADE','HOME','PERMU_HOME','PERMU_TRADE','REQUISITION_TRADE','REQUISITION_HOME')
        <if test="customerId != null and customerId != ''">
            and ia.customer_id = #{customerId}
        </if>
        <if test="orderType != null and orderType == 2 ">
            and iao.order_type in ('HOME','PERMU_HOME','REQUISITION_HOME')
            and ia.examine_status = 'OK'

        </if>
        <if test="orderType != null and orderType == 3 ">
            and iao.order_type in ('TRADE','PERMU_TRADE','REQUISITION_TRADE')
            and ia.examine_status = 'OK'
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and cptr.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

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

        <if test="ex.orderBy == null or ex.orderBy ==''">
            order by ia.create_time desc
        </if>
        ${ex.orderBy}
    </select>
    <select id="listInvoiceNumberV2" resultType="com.zfire.mall.manager.bean.invoice.InvoiceNumber">
        SELECT
        ifnull(SUM(iao.refundable_qty),0) as invoiceNumber,
        ifnull(SUM(iao.pay_amount),0) as payAmount
        FROM
        invoice_application ia
        JOIN invoice_application_order iao ON ia.id = iao.invoice_id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        ${ex.query}
        and ia.examine_status = 'OK'

        <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="k3CategoryIds != null and k3CategoryIds.size > 0">
            and iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
    </select>
    <select id="listOrderNum" resultType="java.lang.Long">
        SELECT COUNT(1) FROM (
        SELECT
        (b.refundable_qty-IFNULL(b.withhold,0)) as refundable_qty
        FROM
        retail_order_item b
        JOIN retail_order a ON a.id = b.retail_order_id
        join customer_product_type_rela cptr on b.customer_wallet_id = cptr.customer_wallet_id
        WHERE
        a.examine_status = 'OK'
        and ((b.direct_transfer_status = 1 and b.is_direct_transfer = 1) or b.is_direct_transfer = 0)
        <if test="id != null and id != ''">
            and a.id = #{id}
        </if>
        <if test="customerId != null and customerId != ''">
            and a.customer_id = #{customerId}
        </if>
        <if test="categoryName != null and categoryName != ''">
            and b.k3_category_name = #{categoryName}
        </if>
        <if test="specification != null and specification != ''">
            and b.specification like concat('%',#{specification},'%')
        </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 cptr.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and b.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        having refundable_qty > 0
        ) s

    </select>
</mapper>