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

    <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
        ${ex.query}
        and ia.examine_status = 'OK'
        <if test="customerId != null and customerId !=''">
            and 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="listInvoiceHuiZong" resultType="com.zfire.mall.manager.bean.invoice.InvoiceNewListBean">
        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
        ${ex.query}
        and ia.examine_status = 'OK'
        <if test="customerId != null and customerId !=''">
            and 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>
        ${ex.orderBy}  order by ia.create_time desc
    </select>
</mapper>