<?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.extend.dao.ExtTicketDao">
    <resultMap id="InvoicePoolHjResultMap"
               type="com.dxhy.extend.entity.ExtTicketInvoiceModel">
        <result column="hjsl" property="id"/>
        <result column="hjje" property="invoiceAmount"/>
        <result column="hjse" property="taxAmount"/>
    </resultMap>
    <select id="queryTotal" parameterType="java.util.Map"
            resultMap="InvoicePoolHjResultMap" databaseId="mysql">
        select count(1) hjsl,sum(t.invoice_amount)hjje,sum(t.tax_amount)hjse
        from t_dx_invoice_reimburse t
        <where>
            <if  test="kpksrq != null and kpksrq != '' and kpksrq != 'null' and kpjsrq != null and kpjsrq != '' and kpjsrq != 'null'">
                and t.invoice_date between #{kpksrq} and #{kpjsrq}
            </if>
            <if  test="cjksyf != null and cjksyf != '' and cjksyf != 'null' and cjjsyf != null and cjjsyf != '' and cjjsyf != 'null'">
                and t.create_date between #{cjksyf} and #{cjjsyf}
            </if>
            <if test="keyWord != null and keyWord != '' and keyWord != 'null' ">
                and CONCAT(IFNULL(t.passenger,''),IFNULL(t.passenger_s_f_z_h,'')) like
                CONCAT('%',#{keyWord},'%')
            </if>
            and t.gf_tax_no in
            <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            <if test="list != null and list != '' and list != 'null' ">
                and t.invoice_type in
                <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </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="isDeduction != null and isDeduction != ''">
                and t.is_deduction = #{isDeduction}
            </if>
            <if test="company !=null and company !=''">
                and t.company = #{company}
            </if>
        </where>
    </select>

    <select id="queryTotal" parameterType="java.util.Map"
            resultMap="InvoicePoolHjResultMap" databaseId="oracle">
        select count(1) hjsl,sum(t.invoice_amount)hjje,sum(t.tax_amount)hjse
        from t_dx_invoice_reimburse t
        <where>
            <if  test="kpksrq != null and kpksrq != '' and kpksrq != 'null' and kpjsrq != null and kpjsrq != '' and kpjsrq != 'null'">
                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="cjksyf != null and cjksyf != '' and cjksyf != 'null' and cjjsyf != null and cjjsyf != '' and cjjsyf != 'null'">
                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="keyWord != null and keyWord != '' and keyWord != 'null' ">
                and  instr(nvl(t.passenger,'')||nvl(t.passenger_s_f_z_h,''),#{keyWord}) > 0
            </if>
            and t.gf_tax_no in
            <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            <if test="list != null and list != '' and list != 'null' ">
                and t.invoice_type in
                <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </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>
            <if test="isDeduction != null and isDeduction != ''">
                and t.is_deduction = #{isDeduction}
            </if>
            <if test="company !=null and company !=''">
                and t.company = #{company}
            </if>
        </where>
    </select>

    <select id="queryTickets" parameterType="java.util.Map"
            resultType="com.dxhy.extend.entity.ExtTicketInvoiceModel" databaseId="oracle">
        select *
        from t_dx_invoice_reimburse t
        <where>

            <if  test="kpksrq != null and kpksrq != '' and kpksrq != 'null' and kpjsrq != null and kpjsrq != '' and kpjsrq != 'null'">
                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="cjksyf != null and cjksyf != '' and cjksyf != 'null' and cjjsyf != null and cjjsyf != '' and cjjsyf != 'null'">
                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="keyWord != null and keyWord != '' and keyWord != 'null' ">
                and  instr(nvl(t.passenger,'')||nvl(t.passenger_s_f_z_h,''),#{keyWord}) > 0
            </if>
            and t.gf_tax_no in
            <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            <if test="list != null and list != '' and list != 'null' ">
                and t.invoice_type in
                <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </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>
            <if test="isDeduction != null and isDeduction != ''">
                and t.is_deduction = #{isDeduction}
            </if>
            <if test="company !=null and company !=''">
                and t.company = #{company}
            </if>
        </where>
    </select>

    <select id="queryTickets" parameterType="java.util.Map"
            resultType="com.dxhy.extend.entity.ExtTicketInvoiceModel" databaseId="mysql">
        select *
        from t_dx_invoice_reimburse t
        <where>
            <if  test="kpksrq != null and kpksrq != '' and kpksrq != 'null' and kpjsrq != null and kpjsrq != '' and kpjsrq != 'null'">
                and t.invoice_date between #{kpksrq} and #{kpjsrq}
            </if>
            <if  test="cjksyf != null and cjksyf != '' and cjksyf != 'null' and cjjsyf != null and cjjsyf != '' and cjjsyf != 'null'">
                and t.create_date between #{cjksyf} and #{cjjsyf}
            </if>
            <if test="keyWord != null and keyWord != '' and keyWord != 'null' ">
                and CONCAT(IFNULL(t.passenger,''),IFNULL(t.passenger_s_f_z_h,'')) like
                CONCAT('%',#{keyWord},'%')
            </if>
            and t.gf_tax_no in
            <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            <if test="list != null and list != '' and list != 'null' ">
                and t.invoice_type in
                <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </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="isDeduction != null and isDeduction != ''">
                and t.is_deduction = #{isDeduction}
            </if>
            <if test="company !=null and company !=''">
                and t.company = #{company}
            </if>
        </where>
    </select>
</mapper>
