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

    <resultMap id="BaseResultMap"
               type="com.dxhy.core.model.BbycfpcxRespVO">
        <result column="fpdm" property="fpdm"/>
        <result column="fphm" property="fphm"/>
        <result column="eleInvoiceNo" property="eleInvoiceNo"/>
        <result column="invoiceType" property="invoiceType"/>

        <result column="kprq" property="kprq"/>
        <result column="gfsh" property="gfsh"/>
        <result column="gfmc" property="gfmc"/>
        <result column="xfsh" property="xfsh"/>
        <result column="xfmc" property="xfmc"/>
        <result column="je" property="je"/>
        <result column="se" property="se"/>
        <result column="yczt" property="yczt"/>
        <result column="ycsj" property="ycsj"/>
        <result column="qszt" property="qszt"/>
        <result column="qsrq" property="qsrq"/>
        <result column="qsr" property="qsr"/>
        <result column="dept_name" property="businessName"/>
    </resultMap>

    <resultMap id="BbycfpcxhjResultMap"
               type="com.dxhy.core.model.BbycfpcxRespVO">
        <result column="hjsl" property="id"/>
        <result column="hjje" property="je"/>
        <result column="hjse" property="se"/>
    </resultMap>

    <select id="selectByPramsMap" parameterType="java.util.Map"
            resultMap="BaseResultMap" databaseId="mysql">
        select
        t.invoice_code fpdm, t.invoice_no fphm ,t.ele_invoice_no eleInvoiceNo,t.invoice_type invoiceType,DATE_FORMAT(t.invoice_date,'%Y-%m-%d')
        kprq,t.gf_tax_no gfsh,t.gf_name gfmc,
        t.xf_tax_no xfsh,t.xf_name xfmc,t.tax_amount se,t.invoice_amount je,DATE_FORMAT(t.qs_date,'%Y-%m-%d') qsrq,
        t.qs_status qszt,t.qs_name qsr, t.invoice_status yczt,DATE_FORMAT(t.status_update_date,'%Y-%m-%d') ycsj ,
        DATE_FORMAT(t.rzh_date,'%Y-%m-%d') rzsj,t.rzh_yesorno rzzt,t.auth_status rzclzt,t.bdk_status
        rzlx,t.rzh_belong_date skssq,dept_name
        from t_dx_record_invoice t
        WHERE t.invoice_status != '0'
        and (t.invoice_type in (${krzpz}) or (t.invoice_type = '32' and t.lq_tdyslxDm = '08'))
        and t.invoice_date between #{kpksrq} and #{kpjsrq}
        and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if
                test="yczt != null and yczt != '' and yczt != 'null' and yczt != '99' ">
            and t.invoice_status = #{yczt}
        </if>
        <if test="qszt != null and qszt != '' and qszt != 'null' and  yczt != '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="xfmc != null and xfmc != '' and xfmc != 'null'  ">
            and t.xf_name like CONCAT(#{xfmc},'%')
        </if>
        <if test="invoiceCode != null and invoiceCode != '' and invoiceCode != 'null' ">
            and t.invoice_code = #{invoiceCode}
        </if>
        <if test="invoiceNo != null and invoiceNo != '' and invoiceNo != 'null' ">
            and t.invoice_no = #{invoiceNo}
        </if>
        <if test="eleInvoiceNo!=null and eleInvoiceNo!='' and eleInvoiceNo!='null'">
            and t.ele_invoice_no = #{eleInvoiceNo}
        </if>
        <if test="company != null and company !=''">
            and t.company = #{company}
        </if>
        <if test="businessCode != '99' and businessCode != null and businessCode != '' and businessCode != 'null' ">
            and t.dept_id = #{businessCode}
        </if>
        <if test="businessCode==''">
            and (t.dept_id is null or t.dept_id='' or t.dept_id='99')
        </if>
        <if test='businessCode == "99" and business != null and business.size()>0'>
            and (t.dept_id in
            <foreach collection="business" index="index" item="item" open="(" separator="," close="">
                #{item.businessCode}
            </foreach>
            ) or (t.dept_id is null or t.dept_id='' or t.dept_id='99'))
        </if>
    </select>
    <select id="selectByPramsMap" parameterType="java.util.Map"
            resultMap="BaseResultMap" databaseId="oracle">
        select
        t.invoice_code fpdm, t.invoice_no fphm ,t.ele_invoice_no eleInvoiceNo,to_char(t.invoice_date,'yyyy-mm-dd')
        kprq,t.gf_tax_no gfsh,t.gf_name gfmc,t.invoice_type invoiceType,
        t.xf_tax_no xfsh,t.xf_name xfmc,t.tax_amount se,t.invoice_amount je,to_char(t.qs_date,'yyyy-mm-dd') qsrq,
        t.qs_status qszt,t.qs_name qsr, t.invoice_status yczt,to_char(t.status_update_date,'yyyy-mm-dd') ycsj ,
        to_char(t.rzh_date,'yyyy-mm-dd') rzsj,t.rzh_yesorno rzzt,t.auth_status rzclzt,t.bdk_status
        rzlx,t.rzh_belong_date skssq,dept_name
        from t_dx_record_invoice t
        WHERE t.invoice_status != '0'
        and (t.invoice_type in (${krzpz}) or (t.invoice_type = '32' and t.lq_tdyslxDm = '08'))
        and t.invoice_date between to_date(#{kpksrq},'yyyy-mm-dd hh24:mi:ss') and to_date(#{kpjsrq},'yyyy-mm-dd hh24:mi:ss')
        and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if
                test="yczt != null and yczt != '' and yczt != 'null' and yczt != '99' ">
            and t.invoice_status = #{yczt}
        </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.auth_status = #{rzzt}
        </if>
        <if test="xfmc != null and xfmc != '' and xfmc != 'null' ">
            and t.xf_name like CONCAT(#{xfmc},'%')
        </if>
        <if test="invoiceCode != null and invoiceCode != '' and invoiceCode != 'null' ">
            and t.invoice_code = #{invoiceCode}
        </if>
        <if test="invoiceNo != null and invoiceNo != '' and invoiceNo != 'null' ">
            and t.invoice_no = #{invoiceNo}
        </if>
        <if test="eleInvoiceNo!=null and eleInvoiceNo!='' and eleInvoiceNo!='null'">
            and t.ele_invoice_no = #{eleInvoiceNo}
        </if>
        <if test="company != null and company !=''">
            and t.company = #{company}
        </if>
        <if test="businessCode != '99' and businessCode != null and businessCode != '' and businessCode != 'null' ">
            and t.dept_id = #{businessCode}
        </if>
        <if test="businessCode==''">
            and (t.dept_id is null or t.dept_id='' or t.dept_id='99')
        </if>
        <if test='businessCode == "99" and business != null and business.size()>0'>
            and (t.dept_id in
            <foreach collection="business" index="index" item="item" open="(" separator="," close="">
                #{item.businessCode}
            </foreach>
            ) or (t.dept_id is null or t.dept_id='' or t.dept_id='99'))
        </if>
    </select>

    <select id="selectBbfpzhHj" parameterType="java.util.Map"
            resultMap="BbycfpcxhjResultMap" databaseId="mysql">

        select count(1) hjsl,sum(t.invoice_amount)hjje,sum(t.tax_amount)hjse
        from t_dx_record_invoice t
        where t.invoice_status != '0'
        and t.invoice_type in ('01','03','14')
        and DATE_FORMAT(t.invoice_date,'%Y-%m-%d') between #{kpksrq} and #{kpjsrq}
        and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if
                test="yczt != null and yczt != '' and yczt != 'null' and yczt != '99' ">
            and t.invoice_status = #{yczt}
        </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="xfmc != null and xfmc != '' and xfmc != 'null' ">
            and t.xf_name like CONCAT(#{xfmc},'%')
        </if>
        <if
                test="invoiceNo != null and invoiceNo != '' and invoiceNo != 'null' ">
            and t.invoice_no = #{invoiceNo}
        </if>


    </select>
    <select id="selectBbfpzhHj" parameterType="java.util.Map"
            resultMap="BbycfpcxhjResultMap" databaseId="oracle">

        select count(1) hjsl,sum(t.invoice_amount)hjje,sum(t.tax_amount)hjse
        from t_dx_record_invoice t
        where
        t.invoice_status != '0'
        and t.invoice_type in ('01','03','14')
        and t.invoice_date between to_date(#{kpksrq},'yyyy-mm-dd hh24:mi:ss') and to_date(#{kpjsrq},'yyyy-mm-dd hh24:mi:ss')
        and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if
                test="yczt != null and yczt != '' and yczt != 'null' and yczt != '99' ">
            and t.invoice_status = #{yczt}
        </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.auth_status = #{rzzt}
        </if>
        <if test="xfmc != null and xfmc != '' and xfmc != 'null' ">
            and t.xf_name like CONCAT(#{xfmc},'%')
        </if>
        <if
                test="invoiceNo != null and invoiceNo != '' and invoiceNo != 'null' ">
            and t.invoice_no = #{invoiceNo}
        </if>


    </select>

</mapper>
