<?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.dxhy.core.dao.CorePoolPaperDao">
    <sql id="sql_fields_added">
        t.invoice_code invoiceCode,
		t.invoice_no invoiceNo,
		t.ele_invoice_no eleInvoiceNo,
		t.invoice_status invoiceStatus,
		t.invoice_type invoiceType,
		DATE_FORMAT(t.invoice_date,'%Y-%m-%d') invoiceDate,
		t.xf_tax_no xfsh,
		t.xf_name xfmc,
		t.invoice_amount invoiceAmount,
		t.tax_amount taxAmount,
		t.total_amount totalAmount,
		t.gf_tax_no gfsh,
		t.gf_name gfmc,
		t.dk_tax_amount yxse,
		t.rzh_yesorno rzzt,
		t.rzlx rzlx,
		DATE_FORMAT(t.rzh_date,'%Y-%m-%d') rzrq,
		t.rzh_type rzfs,
		t.gx_user_name rzr,
		DATE_FORMAT(t.create_date,'%Y-%m-%d') createDate,
		t.collect_status collectStatus,
		t.qs_type qsfs,
		t.qs_status qszt,
		DATE_FORMAT(t.qs_date,'%Y-%m-%d') qsrq,
		t.qs_name qsr,
		DATE_FORMAT(t.in_account_date,'%Y-%m') inAccountMonth,
		t.in_account_status inAccountStatus,
		t.voucher_number voucherNo,
		t.amortize_status amortizeStatus,
		t.amortize_amount amortizeAmount,
		t.remain_amortize_amount remainAmortizeAmount,
		t.amortize_document_number amortizeDocumentNumber,
		t.source_system sourceSystem,
		t.list_status listStatus,
		t.remark remark,
		t.document_number documentNumber,
		DATE_FORMAT(t.collect_date,'%Y-%m-%d') collectDate,
		t.collect_from collectFrom,
        t.xml_flag xmlFlag
    </sql>

    <sql id="sql_fields_added_oracle">
        t.invoice_code invoiceCode,
		t.invoice_no invoiceNo,
		t.ele_invoice_no eleInvoiceNo,
		t.invoice_status invoiceStatus,
		t.invoice_type invoiceType,
		to_char(t.invoice_date,'YYYY-MM-DD') invoiceDate,
		t.xf_tax_no xfsh,
		t.xf_name xfmc,
		t.invoice_amount invoiceAmount,
		t.tax_amount taxAmount,
		t.total_amount totalAmount,
		t.gf_tax_no gfsh,
		t.gf_name gfmc,
		t.dk_tax_amount yxse,
		t.rzh_yesorno rzzt,
		t.rzlx rzlx,
		to_char(t.rzh_date,'YYYY-MM-DD') rzrq,
		t.rzh_type rzfs,
		t.gx_user_name rzr,
		to_char(t.create_date,'YYYY-MM-DD') createDate,
		t.collect_status collectStatus,
		t.qs_type qsfs,
		t.qs_status qszt,
		to_char(t.qs_date,'YYYY-MM-DD') qsrq,
		t.qs_name qsr,
		to_char(t.in_account_date,'YYYY-MM') inAccountMonth,
		t.in_account_status inAccountStatus,
		t.voucher_number voucherNo,
		t.amortize_status amortizeStatus,
		t.amortize_amount amortizeAmount,
		t.remain_amortize_amount remainAmortizeAmount,
		t.amortize_document_number amortizeDocumentNumber,
		t.source_system sourceSystem,
		t.list_status listStatus,
		t.remark remark,
		t.document_number documentNumber,
		to_char(t.collect_date,'YYYY-MM-DD') collectDate,
		t.collect_from collectFrom,
        t.xml_flag xmlFlag
    </sql>
    <select id="listPaperInvoice" parameterType="java.util.Map"
            resultType="com.dxhy.api.model.invoice.InvoicePaperEntity" databaseId="mysql">
        SELECT
        invoice_type invoiceType,
        invoice_code invoiceCode,
        invoice_no invoiceNo,
        ele_invoice_no eleInvoiceNo,
        DATE_FORMAT(invoice_date,'%Y-%m-%d') invoiceDate,
        gf_name buyerName,
        xf_name saleName,
        invoice_amount invoiceAmount,
        tax_amount taxAmount,
        total_amount totalAmount,
        DATE_FORMAT(collect_date,'%Y-%m-%d') collectDate,
        source_system sourceSystem,
        in_pool_reason inPoolReason,
        collect_from collectFrom
        FROM
        <if test="dataType == 1">
            t_dx_record_invoice t
            WHERE t.detail_yesorno = '1' and collect_status='1'
            AND t.gf_tax_no IN
            <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="dataType == 2">
            t_dx_sale_record_invoice t
            WHERE t.detail_yesorno = '1' and collect_status='1'
            AND t.xf_tax_no IN
            <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="cjksrq != null and cjksrq!= '' and cjjsrq != null and cjjsrq != ''">
            AND t.collect_date BETWEEN #{cjksrq} and #{cjjsrq}
        </if>
        ORDER BY t.collect_date DESC
    </select>

    <select id="listPaperInvoice" parameterType="java.util.Map"
            resultType="com.dxhy.api.model.invoice.InvoicePaperEntity" databaseId="oracle">
        SELECT
        invoice_type invoiceType,
        invoice_code invoiceCode,
        invoice_no invoiceNo,
        ele_invoice_no eleInvoiceNo,
        to_char(invoice_date,'YYYY-MM-DD') invoiceDate,
        gf_name buyerName,
        xf_name saleName,
        invoice_amount invoiceAmount,
        tax_amount taxAmount,
        total_amount totalAmount,
        to_char(collect_date,'YYYY-MM-DD') collectDate,
        source_system sourceSystem,
        in_pool_reason inPoolReason,
        collect_from collectFrom
        FROM
        <if test="dataType == 1">
            t_dx_record_invoice t
            WHERE t.detail_yesorno = '1' and collect_status='1'
            AND t.gf_tax_no IN
            <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="dataType == 2">
            t_dx_sale_record_invoice t
            WHERE t.detail_yesorno = '1' and collect_status='1'
            AND t.xf_tax_no IN
            <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="cjksrq != null and cjksrq!= '' and cjjsrq != null and cjjsrq != ''">
            AND t.collect_date BETWEEN to_date(#{cjksrq},'yyyy-mm-dd hh24:mi:ss') and to_date(#{cjjsrq},'yyyy-mm-dd hh24:mi:ss')
        </if>
        ORDER BY t.collect_date DESC
    </select>

    <!--其他发票-->
    <select id="listOtherInvoice" parameterType="java.util.Map"
            resultType="com.dxhy.api.model.invoice.ExtOtherInvoice" databaseId="mysql">
        SELECT
        invoice_type invoiceType,
        invoice_code invoiceCode,
        invoice_no invoiceNo,
        DATE_FORMAT(invoice_date,'%Y-%m-%d') invoiceDate,
        DATE_FORMAT(create_date,'%Y-%m-%d') createDate,
        total_amount invoiceAmount,
        qs_status qszt,
        DATE_FORMAT(qs_date,'%Y-%m-%d') qsrq,
        qs_type qsfs,
        qs_name qsr,
        source_system sourceSystem,
        uuid,
        select_id selectId,
        business_document_number businessDocumentNumber
        FROM
        t_dx_invoice_reimburse
        where invoice_type in
        ('91','94','95','00','97','98','101','102','103','99')
        <if test="cjksyf != null and cjksyf!= '' and cjjsyf != null and cjjsyf != ''">
            AND create_date between #{cjksyf} and #{cjjsyf}
        </if>
        <if test="kpksrq != null and kpksrq!= '' and kpjsrq != null and kpjsrq != ''">
            AND invoice_date between #{kpksrq} and #{kpjsrq}
        </if>
        <if test="keyWord != null and keyWord != '' and keyWord != 'null' ">
            AND CONCAT(IFNULL(invoice_code,''),IFNULL(invoice_no,'')) like CONCAT('%',#{keyWord},'%')
        </if>
        and gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '999' ">
            and invoice_type = #{invoiceType}
        </if>
        <if test="qszt != null and qszt != '' and qszt != 'null' and qszt != '99'">
            and qs_status = #{qszt}
        </if>
        <if test="qsyf != null and qsyf != null and qsyf != '' and qsyf != ''">
            and DATE_FORMAT(qs_date,'%Y-%m') = #{qsyf}
        </if>
        <if test="qsr != null and qsr != '' and qsr != 'null' ">
            and qs_name like concat('%',#{qsr},'%')
        </if>
        <if test="qsfs != null and qsfs != '' and qsfs != 'null' and qsfs != '99'">
            and qs_type = #{qsfs}
        </if>
        GROUP BY uuid having min(create_date)
        ORDER BY create_date DESC
    </select>

    <!--其他发票-->
    <select id="listOtherInvoice" parameterType="java.util.Map"
            resultType="com.dxhy.api.model.invoice.ExtOtherInvoice" databaseId="oracle">
        SELECT
        b.invoice_type invoiceType,
        b.invoice_code invoiceCode,
        b.invoice_no invoiceNo,
        to_char(b.invoice_date,'YYYY-MM-DD') invoiceDate,
        to_char(b.create_date,'YYYY-MM-DD') createDate,
        b.total_amount invoiceAmount,
        b.qs_status qszt,
        to_char(b.qs_date,'YYYY-MM-DD') qsrq,
        b.qs_type qsfs,
        b.qs_name qsr,
        b.source_system sourceSystem,
        b.uuid,
        b.select_id selectId,
        b.business_document_number businessDocumentNumber
        FROM t_dx_invoice_reimburse b left join (
            select uuid,min(create_date) mindate from t_dx_invoice_reimburse t
                where t.invoice_type in
                    ('91','94','95','00','97','98','101','102','103','99')
                    <if test="cjksyf != null and cjksyf!= '' and cjjsyf != null and cjjsyf != ''">
                        AND t.create_date between to_date(#{cjksyf},'yyyy-mm-dd hh24:mi:ss') and to_date(#{cjjsyf},'yyyy-mm-dd hh24:mi:ss')
                    </if>
                    <if test="kpksrq != null and kpksrq!= '' and kpjsrq != null and kpjsrq != ''">
                        AND t.invoice_date between to_date(#{kpksrq},'yyyy-mm-dd hh24:mi:ss') and to_date(#{kpjsrq},'yyyy-mm-dd hh24:mi:ss')
                    </if>
                    <if test="keyWord != null and keyWord != '' and keyWord != 'null' ">
                        AND instr(nvl(t.invoice_code,'')||nvl(t.invoice_no,''),#{keyWord}) > 0
                    </if>
                    and t.gf_tax_no in
                    <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                    <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '999' ">
                        and t.invoice_type = #{invoiceType}
                    </if>
                    <if test="qszt != null and qszt != '' and qszt != 'null' and qszt != '99'">
                        and t.qs_status = #{qszt}
                    </if>
                    <if test="qsyf != null and qsyf != null and qsyf != '' and qsyf != ''">
                        and to_char(t.qs_date,'yyyy-mm') = #{qsyf}
                    </if>
                    <if test="qsr != null and qsr != '' and qsr != 'null' ">
                        and instr(t.qs_name,#{qsr}) > 0
                    </if>
                    <if test="qsfs != null and qsfs != '' and qsfs != 'null' and qsfs != '99'">
                        and t.qs_type = #{qsfs}
                    </if>
                    GROUP BY t.uuid
        ) a on b.uuid = a.uuid
            where b.create_date = a.mindate
        ORDER BY b.create_date DESC
    </select>
    <!--增值税票-->
    <select id="listAddTaxInvoice" parameterType="java.util.Map"
            resultType="com.dxhy.api.model.invoice.ExtAddTaxInvoice" databaseId="mysql">
        select
        t.invoice_code invoiceCode,
        t.invoice_no invoiceNo,
        t.ele_invoice_no eleInvoiceNo,
        t.invoice_status invoiceStatus,
        t.invoice_type invoiceType,
        DATE_FORMAT(t.invoice_date,'%Y-%m-%d') invoiceDate,
        t.xf_tax_no xfsh,
        t.xf_name xfmc,
        t.invoice_amount invoiceAmount,
        t.tax_amount taxAmount,
        t.total_amount totalAmount,
        t.gf_tax_no gfsh,
        t.gf_name gfmc,
        t.dk_tax_amount yxse,
        t.rzh_yesorno rzzt,
        t.rzlx rzlx,
        DATE_FORMAT(t.rzh_date,'%Y-%m-%d') rzrq,
        t.rzh_type rzfs,
        t.gx_user_name rzr,
        DATE_FORMAT(t.create_date,'%Y-%m-%d') createDate,
        t.collect_status collectStatus,
        t.qs_type qsfs,
        t.qs_status qszt,
        DATE_FORMAT(t.qs_date,'%Y-%m-%d') qsrq,
        t.qs_name qsr,
        DATE_FORMAT(t.in_account_date,'%Y-%m') inAccountMonth,
        t.in_account_status inAccountStatus,
        t.voucher_number voucherNo,
        t.amortize_status amortizeStatus,
        t.amortize_amount amortizeAmount,
        t.remain_amortize_amount remainAmortizeAmount,
        t.amortize_document_number amortizeDocumentNumber,
        t.source_system sourceSystem,
        t.is_sale_list listStatus,
        t.remark remark,
        t.document_number documentNumber,
        DATE_FORMAT(t.collect_date,'%Y-%m-%d') collectDate,
        t.collect_from collectFrom,
        t.xml_flag xmlFlag,
        t.lq_tdyslxDm lqTdyslxDm,
        t.ywlx,
        t.lq_rzzt lqRzzt,
        rz.fprzsq,
        t.rzh_back_msg rzhBackMsg
        from t_dx_record_invoice t left join t_dx_lq_rz_invoice rz
        on t.uuid = rz.uuid
        <where>
            <if test='downloadType!=null and downloadType!="" and downloadType!="null" and downloadType != "0"'>
                and t.xml_flag like CONCAT(CONCAT('%', #{downloadType,jdbcType=VARCHAR}), '%')
            </if>


            <!--入账条件三个字段-->
            <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm != 'null' and tdyslxDm != '99' and tdyslxDm != 'ftdyw'">
                and t.lq_tdyslxDm = #{tdyslxDm}
            </if>
            <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm == 'ftdyw'">
                and t.lq_tdyslxDm = ''
            </if>
            <if test="fprzsq != null and fprzsq != '' and fprzsq != 'null' and fprzsq != '99'">
                and rz.fprzsq = #{fprzsq}
            </if>
            <!--已入账  02 05    未入账  01 03 04  -->
            <if test='fprzAuthStatus!=null and fprzAuthStatus!="" and fprzAuthStatus!="null" and fprzAuthStatus=="1"'>
                and t.lq_rzzt in ('02','05')
            </if>
            <if test='fprzAuthStatus!=null and fprzAuthStatus!="" and fprzAuthStatus!="null" and fprzAuthStatus=="0"'>
                and t.lq_rzzt in ('01','03','04')
            </if>
            <!--入账三个字段-->

            <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '1'.toString() ">
                and ( t.xml_flag like '%1%' or t.xml_flag like '%4%' )
            </if>
            <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '0'.toString() ">
                and ( t.xml_flag not like '%1%' and t.xml_flag not like '%4%')
            </if>

            <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '1'.toString() ">
                and ( t.xml_flag like '%2%' or t.xml_flag like '%5%' )
            </if>
            <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '0'.toString() ">
                and ( t.xml_flag not like '%2%' and t.xml_flag not like '%5%')
            </if>

            <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '1'.toString() ">
                and ( t.xml_flag like '%3%' or t.xml_flag like '%6%' )
            </if>
            <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '0'.toString() ">
                and ( t.xml_flag not like '%3%' and t.xml_flag not like '%6%')
            </if>

            <if test="cjksyf != null and cjksyf!= '' and cjjsyf != null and cjjsyf != ''">
                AND t.create_date BETWEEN #{cjksyf} and #{cjjsyf}
            </if>
            <if test="kpksrq != null and kpksrq!= '' and kpjsrq != null and kpjsrq != ''">
                AND t.invoice_date between #{kpksrq} and #{kpjsrq}
            </if>
            <if test="keyWord != null and keyWord != '' and keyWord != 'null' ">
                and CONCAT(IFNULL(t.invoice_code,''),IFNULL(t.invoice_no,''),IFNULL(t.ele_invoice_no,''),IFNULL(t.xf_name,''),IFNULL(t.xf_tax_no,''))
                like
                CONCAT('%',#{keyWord},'%')
            </if>
            and t.gf_tax_no in
            <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '99' ">
                and t.invoice_type = #{invoiceType}
            </if>
            <if test="fpzt != null and fpzt != '' and fpzt != 'null' and fpzt != '99'">
                and t.invoice_status = #{fpzt}
            </if>
            <if test="qszt != null and qszt != '' and qszt != 'null' and qszt != '99'">
                and t.qs_status = #{qszt}
            </if>
            <if test="rzzt != null and rzzt != '' and rzzt != 'null' and rzzt != '99'">
                and t.rzh_yesorno = #{rzzt}
            </if>
            <if test='rzzt == "99"'>
                and t.rzh_yesorno in ('1','2','0')
            </if>
            <if test="rzlx != null and rzlx != '' and rzlx != 'null' and rzlx != '99'">
                and t.rzlx = #{rzlx}
            </if>
            <if test="skssq != null and skssq != '' and skssq != 'null' ">
                and t.rzh_belong_date = #{skssq}
            </if>
            <if test="qsyf != null and qsyf != null and qsyf != '' and qsyf != ''">
                and DATE_FORMAT(t.qs_date,'%Y-%m') = #{qsyf}
            </if>
            <if test="qsr != null and qsr != '' and qsr != 'null' ">
                and t.qs_name like concat('%',#{qsr},'%')
            </if>
            <if test="rzr != null and rzr != '' and rzr != 'null' ">
                and t.gx_user_name like CONCAT(#{rzr},'%')
            </if>
            <if test="qsfs != null and qsfs != '' and qsfs != 'null' and qsfs != '99'">
                and t.qs_type = #{qsfs}
            </if>
            <if test="inAccountStatus != null and inAccountStatus != '' and inAccountStatus != 'null' and inAccountStatus != '99'">
                and t.in_account_status = #{inAccountStatus}
            </if>
            <if test="inAccountMonth != null and inAccountMonth != null and inAccountMonth != '' and inAccountMonth != ''">
                and DATE_FORMAT(t.in_account_date,'%Y-%m') = #{inAccountMonth}
            </if>
            <if test="invoiceSource!=null and invoiceSource!='' and invoiceSource!='99'">
                and t.invoice_source = #{invoiceSource}
            </if>
        </where>

    </select>


    <!--增值税票-->
    <select id="listAddTaxInvoice" parameterType="java.util.Map"
            resultType="com.dxhy.api.model.invoice.ExtAddTaxInvoice" databaseId="oracle">
        select
        t.invoice_code invoiceCode,
        t.invoice_no invoiceNo,
        t.ele_invoice_no eleInvoiceNo,
        t.invoice_status invoiceStatus,
        t.invoice_type invoiceType,
        to_char(t.invoice_date,'YYYY-MM-DD') invoiceDate,
        t.xf_tax_no xfsh,
        t.xf_name xfmc,
        t.invoice_amount invoiceAmount,
        t.tax_amount taxAmount,
        t.total_amount totalAmount,
        t.gf_tax_no gfsh,
        t.gf_name gfmc,
        t.dk_tax_amount yxse,
        t.rzh_yesorno rzzt,
        t.rzlx rzlx,
        to_char(t.rzh_date,'YYYY-MM-DD') rzrq,
        t.rzh_type rzfs,
        t.gx_user_name rzr,
        to_char(t.create_date,'YYYY-MM-DD') createDate,
        t.collect_status collectStatus,
        t.qs_type qsfs,
        t.qs_status qszt,
        to_char(t.qs_date,'YYYY-MM-DD') qsrq,
        t.qs_name qsr,
        to_char(t.in_account_date,'YYYY-MM') inAccountMonth,
        t.in_account_status inAccountStatus,
        t.voucher_number voucherNo,
        t.amortize_status amortizeStatus,
        t.amortize_amount amortizeAmount,
        t.remain_amortize_amount remainAmortizeAmount,
        t.amortize_document_number amortizeDocumentNumber,
        t.source_system sourceSystem,
        t.is_sale_list listStatus,
        t.remark remark,
        t.document_number documentNumber,
        to_char(t.collect_date,'YYYY-MM-DD') collectDate,
        t.collect_from collectFrom,
        t.xml_flag xmlFlag,
        t.lq_tdyslxDm lqTdyslxDm,
        t.ywlx,
        t.lq_rzzt lqRzzt,
        rz.fprzsq,
        t.rzh_back_msg rzhBackMsg
        from t_dx_record_invoice t left join t_dx_lq_rz_invoice rz
        on t.uuid = rz.uuid
        <where>
            <if test='downloadType!=null and downloadType!="" and downloadType!="null" and downloadType != "0"'>
                and t.xml_flag like CONCAT(CONCAT('%', #{downloadType,jdbcType=VARCHAR}), '%')
            </if>

            <!--入账条件三个字段-->
            <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm != 'null' and tdyslxDm != '99' and tdyslxDm != 'ftdyw'">
                and t.lq_tdyslxDm = #{tdyslxDm}
            </if>
            <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm == 'ftdyw'">
                and t.lq_tdyslxDm = ''
            </if>
            <if test="fprzsq != null and fprzsq != '' and fprzsq != 'null' and fprzsq != '99'">
                and rz.fprzsq = #{fprzsq}
            </if>
            <!--已入账  02 05    未入账  01 03 04  -->
            <if test='fprzAuthStatus!=null and fprzAuthStatus!="" and fprzAuthStatus!="null" and fprzAuthStatus=="1"'>
                and t.lq_rzzt in ('02','05')
            </if>
            <if test='fprzAuthStatus!=null and fprzAuthStatus!="" and fprzAuthStatus!="null" and fprzAuthStatus=="0"'>
                and t.lq_rzzt in ('01','03','04')
            </if>
            <!--入账三个字段-->

            <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '1'.toString() ">
                and ( t.xml_flag like '%1%' or t.xml_flag like '%4%' )
            </if>
            <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '0'.toString() ">
                and ( t.xml_flag not like '%1%' and t.xml_flag not like '%4%')
            </if>

            <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '1'.toString() ">
                and ( t.xml_flag like '%2%' or t.xml_flag like '%5%' )
            </if>
            <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '0'.toString() ">
                and ( t.xml_flag not like '%2%' and t.xml_flag not like '%5%')
            </if>

            <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '1'.toString() ">
                and ( t.xml_flag like '%3%' or t.xml_flag like '%6%' )
            </if>
            <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '0'.toString() ">
                and ( t.xml_flag not like '%3%' and t.xml_flag not like '%6%')
            </if>

            <if test="cjksyf != null and cjksyf!= '' and cjjsyf != null and cjjsyf != ''">
            AND t.create_date BETWEEN to_date(#{cjksyf},'yyyy-mm-dd hh24:mi:ss') and to_date(#{cjjsyf},'yyyy-mm-dd hh24:mi:ss')
            </if>
            <if test="kpksrq != null and kpksrq!= '' and kpjsrq != null and kpjsrq != ''">
                AND t.invoice_date between to_date(#{kpksrq},'yyyy-mm-dd hh24:mi:ss') and to_date(#{kpjsrq},'yyyy-mm-dd hh24:mi:ss')
            </if>
            <if test="keyWord != null and keyWord != '' and keyWord != 'null' ">
                AND instr(nvl(t.invoice_code,'')||nvl(t.invoice_no,'')||nvl(t.ele_invoice_no,'')||nvl(t.xf_name,'')||nvl(t.xf_tax_no,''),#{keyWord}) > 0
            </if>
            and t.gf_tax_no in
            <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '99' ">
                and t.invoice_type = #{invoiceType}
            </if>
            <if test="fpzt != null and fpzt != '' and fpzt != 'null' and fpzt != '99'">
                and t.invoice_status = #{fpzt}
            </if>
            <if test="qszt != null and qszt != '' and qszt != 'null' and qszt != '99'">
                and t.qs_status = #{qszt}
            </if>
            <if test="rzzt != null and rzzt != '' and rzzt != 'null' and rzzt != '99'">
                and t.rzh_yesorno = #{rzzt}
            </if>
            <if test='rzzt == "99"'>
                and t.rzh_yesorno in ('1','2','0')
            </if>
            <if test="rzlx != null and rzlx != '' and rzlx != 'null' and rzlx != '99'">
                and t.rzlx = #{rzlx}
            </if>
            <if test="skssq != null and skssq != '' and skssq != 'null' ">
                and t.rzh_belong_date = #{skssq}
            </if>
            <if test="qsyf != null and qsyf != null and qsyf != '' and qsyf != ''">
                and to_char(t.qs_date,'YYYY-MM') = #{qsyf}
            </if>
            <if test="qsr != null and qsr != '' and qsr != 'null' ">
                and instr(t.qs_name,#{qsr}) > 0
            </if>
            <if test="rzr != null and rzr != '' and rzr != 'null' ">
                and instr(t.gx_user_name,#{rzr}) > 0
            </if>
            <if test="qsfs != null and qsfs != '' and qsfs != 'null' and qsfs != '99'">
                and t.qs_type = #{qsfs}
            </if>
            <if test="inAccountStatus != null and inAccountStatus != '' and inAccountStatus != 'null' and inAccountStatus != '99'">
                and t.in_account_status = #{inAccountStatus}
            </if>
            <if test="inAccountMonth != null and inAccountMonth != null and inAccountMonth != '' and inAccountMonth != ''">
                and to_char(t.in_account_date,'YYYY-MM') = #{inAccountMonth}
            </if>
            <if test="invoiceSource!=null and invoiceSource!='' and invoiceSource!='99'">
                and t.invoice_source = #{invoiceSource}
            </if></where>
    </select>


    <!--销项票池查询-->
    <select id="listSaleInvoice" parameterType="java.util.Map"
            resultType="com.dxhy.api.model.invoice.ExtAddTaxInvoice" databaseId="mysql">
        select
        <include refid="sql_fields_added"/>
        ,t.incentive_flag incentiveFlag
        ,t.system_name systemName
        ,t.lq_tdyslxDm lqTdyslxDm
        from t_dx_sale_record_invoice t
        <where>
            <if test='downloadType!=null and downloadType!="" and downloadType!="null" and downloadType != "0"'>
                and t.xml_flag like CONCAT(CONCAT('%', #{downloadType,jdbcType=VARCHAR}), '%')
            </if>

            <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm != 'null' and tdyslxDm != '99' and tdyslxDm != 'ftdyw'">
                and t.lq_tdyslxDm = #{tdyslxDm}
            </if>
            <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm == 'ftdyw'">
                and t.lq_tdyslxDm = ''
            </if>

            <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '1'.toString() ">
                and ( t.xml_flag like '%1%' or t.xml_flag like '%4%' )
            </if>
            <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '0'.toString() ">
                and ( t.xml_flag not like '%1%' and t.xml_flag not like '%4%')
            </if>

            <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '1'.toString() ">
                and ( t.xml_flag like '%2%' or t.xml_flag like '%5%' )
            </if>
            <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '0'.toString() ">
                and ( t.xml_flag not like '%2%' and t.xml_flag not like '%5%')
            </if>

            <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '1'.toString() ">
                and ( t.xml_flag like '%3%' or t.xml_flag like '%6%' )
            </if>
            <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '0'.toString() ">
                and ( t.xml_flag not like '%3%' and t.xml_flag not like '%6%')
            </if>

            <if test="cjksyf != null and cjksyf!= '' and cjjsyf != null and cjjsyf != ''">
                AND t.create_date between #{cjksyf} and #{cjjsyf}
            </if>
            <if test="kpksrq != null and kpksrq!= '' and kpjsrq != null and kpjsrq != ''">
                AND t.invoice_date between #{kpksrq} and #{kpjsrq}
            </if>
            <if test="keyWord != null and keyWord != '' and keyWord != 'null' ">
                and CONCAT(IFNULL(t.invoice_code,''),IFNULL(t.invoice_no,''),IFNULL(t.ele_invoice_no,''),IFNULL(t.gf_name,''),IFNULL(t.gf_tax_no,''))
                like
                CONCAT('%',#{keyWord},'%')
            </if>
            and t.xf_tax_no in
            <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '99' ">
                and t.invoice_type = #{invoiceType}
            </if>
            <if test="invoiceStatus != null and invoiceStatus != '' and invoiceStatus != 'null' and invoiceStatus != '99'">
                and t.invoice_status = #{invoiceStatus}
            </if>
            <if test="qszt != null and qszt != '' and qszt != 'null' and qszt != '99'">
                and t.qs_status = #{qszt}
            </if>
            <if test="qsyf != null and qsyf != null and qsyf != '' and qsyf != ''">
                and DATE_FORMAT(t.qs_date,'%Y-%m') = #{qsyf}
            </if>
            <if test="qsr != null and qsr != '' and qsr != 'null' ">
                and t.qs_name like concat('%',#{qsr},'%')
            </if>
            <if test="qsfs != null and qsfs != '' and qsfs != 'null' and qsfs != '99'">
                and t.qs_type = #{qsfs}
            </if>
            <if test="remark != null and remark != '' and remark != 'null' ">
                and t.remark like CONCAT(#{remark},'%')
            </if>
            <if test="listMark != null and listMark != '' and listMark != 'null' and listMark != '99'">
                and t.list_status = #{listMark}
            </if>
            <if test="inAccountStatus != null and inAccountStatus != '' and inAccountStatus != 'null' and inAccountStatus != '99'">
                and t.in_account_status = #{inAccountStatus}
            </if>
            <if test="inAccountMonth != null and inAccountMonth != null and inAccountMonth != '' and inAccountMonth != ''">
                and DATE_FORMAT(t.in_account_date,'%Y-%m') = #{inAccountMonth}
            </if>
            <if test="incentiveFlag !=null and incentiveFlag !='' and incentiveFlag!= '99'">
                and t.incentive_flag like CONCAT('%',#{incentiveFlag},'%')
            </if>
            <if test="invoiceSource!=null and invoiceSource!='' and invoiceSource!='99'">
                and t.invoice_source = #{invoiceSource}
            </if>
            GROUP BY t.uuid
            ORDER BY t.create_date DESC
        </where>
    </select>


    <!--销项票池查询-->
    <select id="listSaleInvoice" parameterType="java.util.Map"
            resultType="com.dxhy.api.model.invoice.ExtAddTaxInvoice" databaseId="oracle">
        select
        <include refid="sql_fields_added_oracle"/>
        ,t.incentive_flag incentiveFlag
        ,t.system_name systemName
        ,t.lq_tdyslxDm lqTdyslxDm
        from t_dx_sale_record_invoice t
        left join (select uuid from t_dx_sale_record_invoice a
            <where>
                <if test='downloadType!=null and downloadType!="" and downloadType!="null" and downloadType != "0"'>
                    and t.xml_flag like CONCAT(CONCAT('%', #{downloadType,jdbcType=VARCHAR}), '%')
                </if>

                <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm != 'null' and tdyslxDm != '99' and tdyslxDm != 'ftdyw'">
                    and t.lq_tdyslxDm = #{tdyslxDm}
                </if>
                <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm == 'ftdyw'">
                    and t.lq_tdyslxDm = ''
                </if>

                <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '1'.toString() ">
                    and ( t.xml_flag like '%1%' or t.xml_flag like '%4%' )
                </if>
                <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '0'.toString() ">
                    and ( t.xml_flag not like '%1%' and t.xml_flag not like '%4%')
                </if>

                <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '1'.toString() ">
                    and ( t.xml_flag like '%2%' or t.xml_flag like '%5%' )
                </if>
                <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '0'.toString() ">
                    and ( t.xml_flag not like '%2%' and t.xml_flag not like '%5%')
                </if>

                <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '1'.toString() ">
                    and ( t.xml_flag like '%3%' or t.xml_flag like '%6%' )
                </if>
                <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '0'.toString() ">
                    and ( t.xml_flag not like '%3%' and t.xml_flag not like '%6%')
                </if>

                <if test="cjksyf != null and cjksyf!= '' and cjjsyf != null and cjjsyf != ''">
                    AND a.create_date BETWEEN to_date(#{cjksyf},'yyyy-mm-dd hh24:mi:ss') and to_date(#{cjjsyf},'yyyy-mm-dd hh24:mi:ss')
                </if>
                <if test="kpksrq != null and kpksrq!= '' and kpjsrq != null and kpjsrq != ''">
                    AND a.invoice_date between to_date(#{kpksrq},'yyyy-mm-dd hh24:mi:ss') and to_date(#{kpjsrq},'yyyy-mm-dd hh24:mi:ss')
                </if>
                <if test="keyWord != null and keyWord != '' and keyWord != 'null' ">
                    AND instr(nvl(a.invoice_code,'')||nvl(a.invoice_no,'')||nvl(a.ele_invoice_no,'')||nvl(a.gf_name,'')||nvl(a.gf_tax_no,''),#{keyWord}) > 0
                </if>
                and a.xf_tax_no in
                <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
                <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '99' ">
                    and a.invoice_type = #{invoiceType}
                </if>
                <if test="invoiceStatus != null and invoiceStatus != '' and invoiceStatus != 'null' and invoiceStatus != '99'">
                    and a.invoice_status = #{invoiceStatus}
                </if>
                <if test="qszt != null and qszt != '' and qszt != 'null' and qszt != '99'">
                    and a.qs_status = #{qszt}
                </if>
                <if test="qsyf != null and qsyf != null and qsyf != '' and qsyf != ''">
                    and to_char(a.qs_date,'YYYY-MM') = #{qsyf}
                </if>
                <if test="qsr != null and qsr != '' and qsr != 'null' ">
                    and instr(a.qs_name,#{qsr}) > 0
                </if>
                <if test="qsfs != null and qsfs != '' and qsfs != 'null' and qsfs != '99'">
                    and a.qs_type = #{qsfs}
                </if>
                <if test="remark != null and remark != '' and remark != 'null' ">
                    and instr(a.remark,#{remark}) > 0
                </if>
                <if test="listMark != null and listMark != '' and listMark != 'null' and listMark != '99'">
                    and a.list_status = #{listMark}
                </if>
                <if test="inAccountStatus != null and inAccountStatus != '' and inAccountStatus != 'null' and inAccountStatus != '99'">
                    and a.in_account_status = #{inAccountStatus}
                </if>
                <if test="inAccountMonth != null and inAccountMonth != null and inAccountMonth != '' and inAccountMonth != ''">
                    and to_char(a.in_account_date,'YYYY-MM') = #{inAccountMonth}
                </if>
                <if test="incentiveFlag !=null and incentiveFlag !='' and incentiveFlag!= '99'">
                    and instr(a.incentive_flag,#{incentiveFlag}) > 0
                </if>
                <if test="invoiceSource!=null and invoiceSource!='' and invoiceSource!='99'">
                    and a.invoice_source = #{invoiceSource}
                </if>
                GROUP BY a.uuid
        ) b on t.uuid = b.uuid
            ORDER BY t.create_date DESC
        </where>
    </select>

    <select id="getGfName" resultType="java.lang.String">
        SELECT
            nsrmc taxname
        FROM
            sys_enterprise_config
        WHERE
            nsrsbh = #{gfsh}
    </select>
<!--
    <select id="getGfName" resultType="java.lang.String">
        select nsrmc taxname from sys_enterprise_config where nsrsbh = ''
        select taxname
        from sys_dept
        where taxno = #{gfsh}
          and org_type = '5'
    </select>
-->

    <select id="getBlocName" resultType="java.lang.String" databaseId="mysql">
        SELECT config.company_name taxname
            FROM
        sys_group_config config
        JOIN sys_enterprise_config qy ON config.company = qy.company
        where qy.nsrsbh = #{gfsh}

    </select>

    <select id="getBlocName" resultType="java.lang.String" databaseId="oracle">
        SELECT config.company_name taxname
            FROM
        sys_group_config config
        JOIN sys_enterprise_config qy ON config.company = qy.company
        where qy.nsrsbh = #{gfsh}
    </select>

    <!--<select id="getBlocName" resultType="java.lang.String" databaseId="mysql">
        select taxname
        from sys_dept
        where company = (select company from sys_dept where taxno = #{gfsh} and org_type = '5')
          and org_type = '1' limit 1
    </select>

    <select id="getBlocName" resultType="java.lang.String" databaseId="oracle">
        select taxname
        from sys_dept
        where company = (select company from sys_dept where taxno = #{gfsh} and org_type = '5')
          and org_type = '1'
          and rownum = 1
    </select>-->


    <resultMap id="BaseResultMap"
               type="com.dxhy.core.model.CompanyPoolJxVO">
        <result column="invoice_code" property="invoiceCode"/>
        <result column="invoice_no" property="invoiceNo"/>
        <result column="kprq" property="invoiceDate"/>
        <result column="invoice_type" property="invoiceType"/>
        <result column="gfsh" property="gfsh"/>
        <result column="gfmc" property="gfmc"/>
        <result column="gfdzdh" property="gfdzdh"/>
        <result column="gfkhhzh" property="gfkhhzh"/>
        <result column="xfsh" property="xfsh"/>
        <result column="xfmc" property="xfmc"/>
        <result column="xfdzdh" property="xfdzdh"/>
        <result column="xfkhhzh" property="xfkhhzh"/>
        <result column="invoice_amount" property="invoiceAmount"/>
        <result column="tax_amount" property="taxAmount"/>
        <result column="jshj" property="jshj"/>
        <result column="hlmc" property="hlmc"/>
        <result column="ggxh" property="ggxh"/>
        <result column="dw" property="dw"/>
        <result column="num" property="num"/>
        <result column="dj" property="dj"/>
        <result column="je" property="je"/>
        <result column="se" property="se"/>
        <result column="sl" property="sl"/>
        <result column="bz" property="bz"/>
        <result column="uuid" property="uuid"/>
        <result column="cph" property="cph"/>
        <result column="lx" property="lx"/>
        <result column="txrqq" property="txrqq"/>
        <result column="txrqz" property="txrqz"/>
        <result column="in_account_status" property="inAccountStatus"/>
        <result column="lqTdyslxDm" property="lqTdyslxDm"/>
    </resultMap>

    <select id="listAddTaxInvoiceDetail" parameterType="java.util.Map"
            resultMap="BaseResultMap" databaseId="mysql">
        select
        t.invoice_code,t.invoice_no,DATE_FORMAT(t.invoice_date,'%Y-%m-%d')
        kprq,t.gf_tax_no gfsh,t.gf_name gfmc,t.gf_address_and_phone gfdzdh,t.gf_bank_and_no gfkhhzh,
        t.invoice_type,
        t.xf_tax_no xfsh,t.xf_name xfmc,t.xf_address_and_phone xfdzdh,t.xf_bank_and_no
        xfkhhzh,t.tax_amount,t.invoice_amount,t.rzh_belong_date skssq,
        t.total_amount jshj,t.remark bz,t.uuid,b.goods_name hlmc,b.model ggxh,b.unit dw,b.num num,b.unit_price
        dj,b.detail_amount je,b.tax_amount se,b.tax_rate sl,b.cph cph, b.lx lx,b.txrqq txrqq,b.txrqz txrqz,
        t.in_account_status
        from
         t_dx_record_invoice t left join t_dx_lq_rz_invoice rz
        on t.uuid = rz.uuid
        left JOIN t_dx_record_invoice_detail b ON t.uuid = b.uuid
        where t.detail_yesorno = '1'
        <if test="cjksyf != null and cjksyf!= '' and cjjsyf != null and cjjsyf != ''">
            AND t.create_date BETWEEN #{cjksyf} and #{cjjsyf}
        </if>

        <!--入账条件三个字段-->
        <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm != 'null' and tdyslxDm != '99' and tdyslxDm != 'ftdyw'">
            and t.lq_tdyslxDm = #{tdyslxDm}
        </if>
        <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm == 'ftdyw'">
            and t.lq_tdyslxDm = ''
        </if>
        <if test="fprzsq != null and fprzsq != '' and fprzsq != 'null' and fprzsq != '99'">
            and rz.fprzsq = #{fprzsq}
        </if>
        <!--已入账  02 05    未入账  01 03 04  -->
        <if test='fprzAuthStatus!=null and fprzAuthStatus!="" and fprzAuthStatus!="null" and fprzAuthStatus=="1"'>
            and t.lq_rzzt in ('02','05')
        </if>
        <if test='fprzAuthStatus!=null and fprzAuthStatus!="" and fprzAuthStatus!="null" and fprzAuthStatus=="0"'>
            and t.lq_rzzt in ('01','03','04')
        </if>
        <!--入账三个字段-->

        <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '1'.toString() ">
            and ( t.xml_flag like '%1%' or t.xml_flag like '%4%' )
        </if>
        <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '0'.toString() ">
            and ( t.xml_flag not like '%1%' and t.xml_flag not like '%4%')
        </if>

        <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '1'.toString() ">
            and ( t.xml_flag like '%2%' or t.xml_flag like '%5%' )
        </if>
        <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '0'.toString() ">
            and ( t.xml_flag not like '%2%' and t.xml_flag not like '%5%')
        </if>

        <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '1'.toString() ">
            and ( t.xml_flag like '%3%' or t.xml_flag like '%6%' )
        </if>
        <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '0'.toString() ">
            and ( t.xml_flag not like '%3%' and t.xml_flag not like '%6%')
        </if>

        <if test="kpksrq != null and kpksrq!= '' and kpjsrq != null and kpjsrq != ''">
            AND t.invoice_date between #{kpksrq} and #{kpjsrq}
        </if>
        <if test="keyWord != null and keyWord != '' and keyWord != 'null' ">
            and CONCAT(IFNULL(t.invoice_code,''),IFNULL(t.invoice_no,''),IFNULL(t.xf_name,'')) like
            CONCAT('%',#{keyWord},'%')
        </if>
        and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '99' ">
            and t.invoice_type = #{invoiceType}
        </if>
        <if test="fpzt != null and fpzt != '' and fpzt != 'null' and fpzt != '99'">
            and t.invoice_status = #{fpzt}
        </if>
        <if test="qszt != null and qszt != '' and qszt != 'null' and qszt != '99'">
            and t.qs_status = #{qszt}
        </if>
        <if test="rzzt != null and rzzt != '' and rzzt != 'null' and rzzt != '99'">
            and t.rzh_yesorno = #{rzzt}
        </if>
        <if test='rzzt == "99"'>
            and t.rzh_yesorno in ('1','2','0')
        </if>
        <if test="rzlx != null and rzlx != '' and rzlx != 'null' and rzlx != '99'">
            and t.rzlx = #{rzlx}
        </if>
        <if test="skssq != null and skssq != '' and skssq != 'null' ">
            and t.rzh_belong_date = #{skssq}
        </if>
        <if test="qsyf != null and qsyf != null and qsyf != '' and qsyf != ''">
            and DATE_FORMAT(t.qs_date,'%Y-%m') = #{qsyf}
        </if>
        <if test="qsr != null and qsr != '' and qsr != 'null' ">
            and t.qs_name like concat('%',#{qsr},'%')
        </if>
        <if test="rzr != null and rzr != '' and rzr != 'null' ">
            and t.confirm_user like CONCAT(#{rzr},'%')
        </if>
        <if test="qsfs != null and qsfs != '' and qsfs != 'null' and qsfs != '99'">
            and t.qs_type = #{qsfs}
        </if>
        <if test="inAccountStatus != null and inAccountStatus != '' and inAccountStatus != 'null' and inAccountStatus != '99'">
            and t.in_account_status = #{inAccountStatus}
        </if>
        <if test="inAccountMonth != null and inAccountMonth != null and inAccountMonth != '' and inAccountMonth != ''">
            and DATE_FORMAT(t.in_account_date,'%Y-%m') = #{inAccountMonth}
        </if>
        <if test="invoiceSource!=null and invoiceSource!='' and invoiceSource!='99'">
            and t.invoice_source = #{invoiceSource}
        </if>
    </select>

    <select id="listAddTaxInvoiceDetail" parameterType="java.util.Map"
            resultMap="BaseResultMap" databaseId="oracle">
        select
        t.invoice_code,t.invoice_no,to_char(t.invoice_date,'YYYY-MM-DD')
        kprq,t.gf_tax_no gfsh,t.gf_name gfmc,t.gf_address_and_phone gfdzdh,t.gf_bank_and_no gfkhhzh,
        t.invoice_type,
        t.xf_tax_no xfsh,t.xf_name xfmc,t.xf_address_and_phone xfdzdh,t.xf_bank_and_no
        xfkhhzh,t.tax_amount,t.invoice_amount,t.rzh_belong_date skssq,
        t.total_amount jshj,t.remark bz,t.uuid,b.goods_name hlmc,b.model ggxh,b.unit dw,b.num num,b.unit_price
        dj,b.detail_amount je,b.tax_amount se,b.tax_rate sl,b.cph cph, b.lx lx,b.txrqq txrqq,b.txrqz txrqz,
        t.in_account_status
        from
        t_dx_record_invoice t left join t_dx_lq_rz_invoice rz
        on t.uuid = rz.uuid
        left JOIN t_dx_record_invoice_detail b ON t.uuid = b.uuid
        where t.detail_yesorno = '1'
        <if test="cjksyf != null and cjksyf!= '' and cjjsyf != null and cjjsyf != ''">
            AND t.create_date BETWEEN to_date(#{cjksyf},'yyyy-mm-dd hh24:mi:ss') and to_date(#{cjjsyf},'yyyy-mm-dd hh24:mi:ss')
        </if>

        <!--入账条件三个字段-->
        <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm != 'null' and tdyslxDm != '99' and tdyslxDm != 'ftdyw'">
            and t.lq_tdyslxDm = #{tdyslxDm}
        </if>
        <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm == 'ftdyw'">
            and t.lq_tdyslxDm = ''
        </if>
        <if test="fprzsq != null and fprzsq != '' and fprzsq != 'null' and fprzsq != '99'">
            and rz.fprzsq = #{fprzsq}
        </if>
        <!--已入账  02 05    未入账  01 03 04  -->
        <if test='fprzAuthStatus!=null and fprzAuthStatus!="" and fprzAuthStatus!="null" and fprzAuthStatus=="1"'>
            and t.lq_rzzt in ('02','05')
        </if>
        <if test='fprzAuthStatus!=null and fprzAuthStatus!="" and fprzAuthStatus!="null" and fprzAuthStatus=="0"'>
            and t.lq_rzzt in ('01','03','04')
        </if>
        <!--入账三个字段-->

        <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '1'.toString() ">
            and ( t.xml_flag like '%1%' or t.xml_flag like '%4%' )
        </if>
        <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '0'.toString() ">
            and ( t.xml_flag not like '%1%' and t.xml_flag not like '%4%')
        </if>

        <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '1'.toString() ">
            and ( t.xml_flag like '%2%' or t.xml_flag like '%5%' )
        </if>
        <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '0'.toString() ">
            and ( t.xml_flag not like '%2%' and t.xml_flag not like '%5%')
        </if>

        <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '1'.toString() ">
            and ( t.xml_flag like '%3%' or t.xml_flag like '%6%' )
        </if>
        <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '0'.toString() ">
            and ( t.xml_flag not like '%3%' and t.xml_flag not like '%6%')
        </if>

        <if test="kpksrq != null and kpksrq!= '' and kpjsrq != null and kpjsrq != ''">
            AND t.invoice_date between to_date(#{kpksrq},'yyyy-mm-dd hh24:mi:ss') and to_date(#{kpjsrq},'yyyy-mm-dd hh24:mi:ss')
        </if>
        <if test="keyWord != null and keyWord != '' and keyWord != 'null' ">
            instr(nvl(t.invoice_code,'')||nvl(t.invoice_no,'')||nvl(t.xf_name,''),#{keyWord}) > 0
        </if>
        and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '99' ">
            and t.invoice_type = #{invoiceType}
        </if>
        <if test="fpzt != null and fpzt != '' and fpzt != 'null' and fpzt != '99'">
            and t.invoice_status = #{fpzt}
        </if>
        <if test="qszt != null and qszt != '' and qszt != 'null' and qszt != '99'">
            and t.qs_status = #{qszt}
        </if>
        <if test="rzzt != null and rzzt != '' and rzzt != 'null' and rzzt != '99'">
            and t.rzh_yesorno = #{rzzt}
        </if>
        <if test='rzzt == "99"'>
            and t.rzh_yesorno in ('1','2','0')
        </if>
        <if test="rzlx != null and rzlx != '' and rzlx != 'null' and rzlx != '99'">
            and t.rzlx = #{rzlx}
        </if>
        <if test="skssq != null and skssq != '' and skssq != 'null' ">
            and t.rzh_belong_date = #{skssq}
        </if>
        <if test="qsyf != null and qsyf != null and qsyf != '' and qsyf != ''">
            and to_char(t.qs_date,'YYYY-MM') = #{qsyf}
        </if>
        <if test="qsr != null and qsr != '' and qsr != 'null' ">
            and instr(t.qs_name,#{qsr}) > 0
        </if>
        <if test="rzr != null and rzr != '' and rzr != 'null' ">
            and instr(t.confirm_user,#{rzr}) > 0
        </if>
        <if test="qsfs != null and qsfs != '' and qsfs != 'null' and qsfs != '99'">
            and t.qs_type = #{qsfs}
        </if>
        <if test="inAccountStatus != null and inAccountStatus != '' and inAccountStatus != 'null' and inAccountStatus != '99'">
            and t.in_account_status = #{inAccountStatus}
        </if>
        <if test="inAccountMonth != null and inAccountMonth != null and inAccountMonth != '' and inAccountMonth != ''">
            and to_char(t.in_account_date,'YYYY-MM') = #{inAccountMonth}
        </if>
        <if test="invoiceSource!=null and invoiceSource!='' and invoiceSource!='99'">
            and t.invoice_source = #{invoiceSource}
        </if>
    </select>


    <select id="listSaleInvoiceDetail" parameterType="java.util.Map"
            resultMap="BaseResultMap" databaseId="mysql">
        select
        t.invoice_code,t.invoice_no,DATE_FORMAT(t.invoice_date,'%Y-%m-%d')
        kprq,t.gf_tax_no gfsh,t.gf_name gfmc,t.gf_address_and_phone gfdzdh,t.gf_bank_and_no gfkhhzh,
        t.invoice_type,
        t.xf_tax_no xfsh,t.xf_name xfmc,t.xf_address_and_phone xfdzdh,t.xf_bank_and_no
        xfkhhzh,t.tax_amount,t.invoice_amount,t.rzh_belong_date skssq,
        t.total_amount jshj,t.remark bz,t.uuid,b.goods_name hlmc,b.model ggxh,b.unit dw,b.num num,b.unit_price
        dj,b.detail_amount je,b.tax_amount se,b.tax_rate sl,b.cph cph, b.lx lx,b.txrqq txrqq,b.txrqz txrqz,
        t.in_account_status,b.yhzcbs incentiveFlag,t.lq_tdyslxDm lqTdyslxDm
        from
         t_dx_sale_record_invoice t
        left JOIN t_dx_record_invoice_detail_sales b ON t.uuid = b.uuid
        where t.detail_yesorno = '1'
        <if test="cjksyf != null and cjksyf!= '' and cjjsyf != null and cjjsyf != ''">
            AND t.create_date BETWEEN #{cjksyf} and #{cjjsyf}
        </if>

        <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm != 'null' and tdyslxDm != '99' and tdyslxDm != 'ftdyw'">
            and t.lq_tdyslxDm = #{tdyslxDm}
        </if>
        <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm == 'ftdyw'">
            and t.lq_tdyslxDm = ''
        </if>

        <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '1'.toString() ">
            and ( t.xml_flag like '%1%' or t.xml_flag like '%4%' )
        </if>
        <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '0'.toString() ">
            and ( t.xml_flag not like '%1%' and t.xml_flag not like '%4%')
        </if>

        <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '1'.toString() ">
            and ( t.xml_flag like '%2%' or t.xml_flag like '%5%' )
        </if>
        <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '0'.toString() ">
            and ( t.xml_flag not like '%2%' and t.xml_flag not like '%5%')
        </if>

        <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '1'.toString() ">
            and ( t.xml_flag like '%3%' or t.xml_flag like '%6%' )
        </if>
        <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '0'.toString() ">
            and ( t.xml_flag not like '%3%' and t.xml_flag not like '%6%')
        </if>

        <if test="kpksrq != null and kpksrq!= '' and kpjsrq != null and kpjsrq != ''">
            AND t.invoice_date between #{kpksrq} and #{kpjsrq}
        </if>
        <if test="keyWord != null and keyWord != '' and keyWord != 'null' ">
            and CONCAT(IFNULL(t.invoice_code,''),IFNULL(t.invoice_no,''),IFNULL(t.xf_name,'')) like
            CONCAT('%',#{keyWord},'%')
        </if>
        and t.xf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '99' ">
            and t.invoice_type = #{invoiceType}
        </if>
        <if test="fpzt != null and fpzt != '' and fpzt != 'null' and fpzt != '99'">
            and t.invoice_status = #{fpzt}
        </if>
        <if test="qszt != null and qszt != '' and qszt != 'null' and qszt != '99'">
            and t.qs_status = #{qszt}
        </if>
        <if test="rzzt != null and rzzt != '' and rzzt != 'null' and rzzt != '99'">
            and t.rzh_yesorno = #{rzzt}
        </if>
        <if test='rzzt == "99"'>
            and t.rzh_yesorno in ('1','2','0')
        </if>
        <if test="rzlx != null and rzlx != '' and rzlx != 'null' and rzlx != '99'">
            and t.rzlx = #{rzlx}
        </if>
        <if test="skssq != null and skssq != '' and skssq != 'null' ">
            and t.rzh_belong_date = #{skssq}
        </if>
        <if test="qsyf != null and qsyf != null and qsyf != '' and qsyf != ''">
            and DATE_FORMAT(t.qs_date,'%Y-%m') = #{qsyf}
        </if>
        <if test="qsr != null and qsr != '' and qsr != 'null' ">
            and t.qs_name like concat('%',#{qsr},'%')
        </if>
        <if test="rzr != null and rzr != '' and rzr != 'null' ">
            and t.confirm_user like CONCAT(#{rzr},'%')
        </if>
        <if test="qsfs != null and qsfs != '' and qsfs != 'null' and qsfs != '99'">
            and t.qs_type = #{qsfs}
        </if>
        <if test="inAccountStatus != null and inAccountStatus != '' and inAccountStatus != 'null' and inAccountStatus != '99'">
            and t.in_account_status = #{inAccountStatus}
        </if>
        <if test="inAccountMonth != null and inAccountMonth != null and inAccountMonth != '' and inAccountMonth != ''">
            and DATE_FORMAT(t.in_account_date,'%Y-%m') = #{inAccountMonth}
        </if>
        <if test="incentiveFlag !=null and incentiveFlag !='' and incentiveFlag!= '99'">
            and t.incentive_flag like CONCAT('%',#{incentiveFlag},'%')
        </if>
        <if test="listMark != null and listMark != '' and listMark != 'null' and listMark != '99'">
            and t.list_status = #{listMark}
        </if>
        <if test="remark != null and remark != '' and remark != 'null' ">
            and t.remark like CONCAT(#{remark},'%')
        </if>
        <if test="invoiceSource!=null and invoiceSource!='' and invoiceSource!='99'">
            and t.invoice_source = #{invoiceSource}
        </if>
    </select>

    <select id="listSaleInvoiceDetail" parameterType="java.util.Map"
            resultMap="BaseResultMap" databaseId="oracle">
        select
        t.invoice_code,t.invoice_no,to_char(t.invoice_date,'YYYY-MM-DD')
        kprq,t.gf_tax_no gfsh,t.gf_name gfmc,t.gf_address_and_phone gfdzdh,t.gf_bank_and_no gfkhhzh,
        t.invoice_type,
        t.xf_tax_no xfsh,t.xf_name xfmc,t.xf_address_and_phone xfdzdh,t.xf_bank_and_no
        xfkhhzh,t.tax_amount,t.invoice_amount,t.rzh_belong_date skssq,
        t.total_amount jshj,t.remark bz,t.uuid,b.goods_name hlmc,b.model ggxh,b.unit dw,b.num num,b.unit_price
        dj,b.detail_amount je,b.tax_amount se,b.tax_rate sl,b.cph cph, b.lx lx,b.txrqq txrqq,b.txrqz txrqz,
        t.in_account_status,b.yhzcbs incentiveFlag,t.lq_tdyslxDm lqTdyslxDm
        from
        t_dx_sale_record_invoice t
        left JOIN t_dx_record_invoice_detail_sales b ON t.uuid = b.uuid
        where t.detail_yesorno = '1'
        <if test="cjksyf != null and cjksyf!= '' and cjjsyf != null and cjjsyf != ''">
            AND t.create_date BETWEEN to_date(#{cjksyf},'yyyy-mm-dd hh24:mi:ss') and to_date(#{cjjsyf},'yyyy-mm-dd hh24:mi:ss')
        </if>

        <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm != 'null' and tdyslxDm != '99' and tdyslxDm != 'ftdyw'">
            and t.lq_tdyslxDm = #{tdyslxDm}
        </if>
        <if test="tdyslxDm != null and tdyslxDm != '' and tdyslxDm == 'ftdyw'">
            and t.lq_tdyslxDm = ''
        </if>

        <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '1'.toString() ">
            and ( t.xml_flag like '%1%' or t.xml_flag like '%4%' )
        </if>
        <if test="sourceXml != null and sourceXml != '' and sourceXml != 'null' and sourceXml == '0'.toString() ">
            and ( t.xml_flag not like '%1%' and t.xml_flag not like '%4%')
        </if>

        <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '1'.toString() ">
            and ( t.xml_flag like '%2%' or t.xml_flag like '%5%' )
        </if>
        <if test="sourcePdf != null and sourcePdf != '' and sourcePdf != 'null' and sourcePdf == '0'.toString() ">
            and ( t.xml_flag not like '%2%' and t.xml_flag not like '%5%')
        </if>

        <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '1'.toString() ">
            and ( t.xml_flag like '%3%' or t.xml_flag like '%6%' )
        </if>
        <if test="sourceOfd != null and sourceOfd != '' and sourceOfd != 'null' and sourceOfd == '0'.toString() ">
            and ( t.xml_flag not like '%3%' and t.xml_flag not like '%6%')
        </if>

        <if test="kpksrq != null and kpksrq!= '' and kpjsrq != null and kpjsrq != ''">
            AND t.invoice_date between to_date(#{kpksrq},'yyyy-mm-dd hh24:mi:ss') and to_date(#{kpjsrq},'yyyy-mm-dd hh24:mi:ss')
        </if>
        <if test="keyWord != null and keyWord != '' and keyWord != 'null' ">
            instr(nvl(t.invoice_code,'')||nvl(t.invoice_no,'')||nvl(t.xf_name,''),#{keyWord}) > 0
        </if>
        and t.xf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '99' ">
            and t.invoice_type = #{invoiceType}
        </if>
        <if test="fpzt != null and fpzt != '' and fpzt != 'null' and fpzt != '99'">
            and t.invoice_status = #{fpzt}
        </if>
        <if test="qszt != null and qszt != '' and qszt != 'null' and qszt != '99'">
            and t.qs_status = #{qszt}
        </if>
        <if test="rzzt != null and rzzt != '' and rzzt != 'null' and rzzt != '99'">
            and t.rzh_yesorno = #{rzzt}
        </if>
        <if test='rzzt == "99"'>
            and t.rzh_yesorno in ('1','2','0')
        </if>
        <if test="rzlx != null and rzlx != '' and rzlx != 'null' and rzlx != '99'">
            and t.rzlx = #{rzlx}
        </if>
        <if test="skssq != null and skssq != '' and skssq != 'null' ">
            and t.rzh_belong_date = #{skssq}
        </if>
        <if test="qsyf != null and qsyf != null and qsyf != '' and qsyf != ''">
            and to_char(t.qs_date,'YYYY-MM') = #{qsyf}
        </if>
        <if test="qsr != null and qsr != '' and qsr != 'null' ">
            and instr(t.qs_name,#{qsr}) > 0
        </if>
        <if test="rzr != null and rzr != '' and rzr != 'null' ">
            and instr(t.confirm_user,#{rzr}) > 0
        </if>
        <if test="qsfs != null and qsfs != '' and qsfs != 'null' and qsfs != '99'">
            and t.qs_type = #{qsfs}
        </if>
        <if test="inAccountStatus != null and inAccountStatus != '' and inAccountStatus != 'null' and inAccountStatus != '99'">
            and t.in_account_status = #{inAccountStatus}
        </if>
        <if test="inAccountMonth != null and inAccountMonth != null and inAccountMonth != '' and inAccountMonth != ''">
            and to_char(t.in_account_date,'YYYY-MM') = #{inAccountMonth}
        </if>
        <if test="incentiveFlag !=null and incentiveFlag !='' and incentiveFlag!= '99'">
            and instr(t.incentive_flag,#{incentiveFlag}) > 0
        </if>
        <if test="listMark != null and listMark != '' and listMark != 'null' and listMark != '99'">
            and t.list_status = #{listMark}
        </if>
        <if test="remark != null and remark != '' and remark != 'null' ">
            and instr(t.remark,#{remark}) > 0
        </if>
        <if test="invoiceSource!=null and invoiceSource!='' and invoiceSource!='99'">
            and t.invoice_source = #{invoiceSource}
        </if>
    </select>
</mapper>
