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

    <resultMap id="QssbResultMap" type="com.dxhy.core.model.QssbRespVO">
        <result column="id" property="id"/>
        <result column="uuid" property="uuid"/>
        <result column="qsrq" property="qsrq"/>
        <result column="qszt" property="qszt"/>
        <result column="qsfs" property="qsfs"/>
        <result column="qsr" property="qsr"/>
        <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="deptName" property="businessName"/>
    </resultMap>

    <select id="selectQssbBySmb" parameterType="java.util.Map"
            resultMap="QssbResultMap" databaseId="mysql">
        select t.id id,t.uuid uuid, DATE_FORMAT(t.qs_date,'%Y-%m-%d') qsrq,t.qs_status qszt,t.qs_type qsfs,t.user_name
        qsr,
        t.invoice_code fpdm, t.invoice_type invoiceType,t.invoice_no fphm,t.ele_invoice_no eleInvoiceNo,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.invoice_amount je,t.tax_amount se,t.dept_name deptName
        from t_dx_invoice t
        where t.qs_status = '0'
        and t.qs_date between #{qsrqq} and #{qsrqz}
        and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>

        <if test="xfmc != null and xfmc != '' and xfmc != 'null' ">
            and t.xf_name like CONCAT(#{xfmc},'%')
        </if>
        <if test="qsr != null and qsr != '' and qsr != 'null' ">
            and t.user_name like concat('%',#{qsr},'%')
        </if>
        <if test="fpdm != null and fpdm != '' and fpdm != 'null' ">
            and t.invoice_code = #{fpdm}
        </if>
        <if test="fphm != null and fphm != '' and fphm != 'null' ">
            and t.invoice_no = #{fphm}
        </if>
        <if test="eleInvoiceNo!=null and eleInvoiceNo!='' and eleInvoiceNo!='null'">
            and t.ele_invoice_no = #{eleInvoiceNo}
        </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="company!=null and company !=''">
            and t.company = #{company}
        </if>
        ORDER BY t.qs_date desc
    </select>

    <select id="selectQssbBySmb" parameterType="java.util.Map"
            resultMap="QssbResultMap" databaseId="oracle">
        select t.id id,t.uuid uuid,to_char(t.qs_date,'yyyy-MM-dd') qsrq,t.qs_status qszt,t.qs_type qsfs,t.user_name qsr,
        t.invoice_code fpdm,t.invoice_no fphm,t.ele_invoice_no eleInvoiceNo,t.invoice_type invoiceType,to_char(t.invoice_date,'yyyy-MM-dd')
        kprq,t.gf_tax_no gfsh,t.gf_name gfmc,
        t.xf_tax_no xfsh,t.xf_name xfmc,t.invoice_amount je,t.tax_amount se,t.dept_name deptName
        from t_dx_invoice t
        where t.qs_status = '0'
        and t.qs_date between to_date(#{qsrqq},'yyyy-mm-dd hh24:mi:ss') and to_date(#{qsrqz},'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="xfmc != null and xfmc != '' and xfmc != 'null' ">
            and t.xf_name like CONCAT(#{xfmc},'%')
        </if>
        <if test="qsr != null and qsr != '' and qsr != 'null' ">
            and t.user_name like concat(concat('%',#{qsr}),'%')
        </if>
        <if test="fpdm != null and fpdm != '' and fpdm != 'null' ">
            and t.invoice_code = #{fpdm}
        </if>
        <if test="fphm != null and fphm != '' and fphm != 'null' ">
            and t.invoice_no = #{fphm}
        </if>
        <if test="eleInvoiceNo!=null and eleInvoiceNo!='' and eleInvoiceNo!='null'">
            and t.ele_invoice_no = #{eleInvoiceNo}
        </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="company!=null and company !=''">
            and t.company = #{company}
        </if>
        ORDER BY t.qs_date desc
    </select>


</mapper>