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

    <resultMap id="FprzhrResultMap" type="com.dxhy.extend.model.ExtFprzhrRespVO">
        <result column="rzhr" property="rzhr"/>
        <result column="fpsl" property="fpsl"/>
        <result column="hjje" property="hjje"/>
        <result column="hjse" property="hjse"/>
        <result column="hjdk" property="hjdk"/>
        <result column="rzDate" property="rzDate"/>

    </resultMap>
    <select id="invoiceStatByRzhr" parameterType="java.util.Map"
            resultMap="FprzhrResultMap" databaseId="mysql">
        select
        t.confirm_user rzhr,
        COUNT(t.id ) as fpsl,
        ifnull(sum(invoice_amount),0) as hjje,
        ifnull(sum(tax_amount),0) as hjse ,
        ifnull(sum(dk_tax_amount),0) as hjdk
        from t_dx_record_invoice t
        where
        t.auth_status = '4' and rzh_yesorno='1'
        and (t.invoice_type in (${krzpz}) or (t.invoice_type = '32' and t.lq_tdyslxDm = '08'))
        and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="kpksrq!=null and kpksrq!='' and kpjsrq!=null and kpjsrq!=''">
            and date_format(t.invoice_date,'%Y-%m-%d') between #{kpksrq} and #{kpjsrq}
        </if>
        <if test="rzksrq!=null and rzksrq!='' and rzjsrq!=null and rzjsrq!=''">
            and date_format(t.rzh_date,'%Y-%m-%d') between #{rzksrq} and #{rzjsrq}
        </if>
        <if test="skssq!=null and skssq!=''">
            and t.rzh_belong_date = #{skssq}
        </if>
        <if test="rzhr!=null and rzhr!=''">
            and t.confirm_user like concat('%',#{rzhr},'%')
        </if>
        GROUP BY t.confirm_user ORDER by t.confirm_user
    </select>
    <select id="invoiceStatByRzhr" parameterType="java.util.Map"
            resultMap="FprzhrResultMap" databaseId="oracle">
        select
        t.confirm_user rzhr,
        COUNT(t.id ) as fpsl,
        COALESCE(sum(invoice_amount),0) as hjje,
        COALESCE(sum(tax_amount),0) as hjse ,
        COALESCE(sum(dk_tax_amount),0) as hjdk
        from t_dx_record_invoice t
        where
        t.auth_status = '4' and rzh_yesorno='1'
        and (t.invoice_type in (${krzpz}) or (t.invoice_type = '32' and t.lq_tdyslxDm = '08'))
        and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="kpksrq!=null and kpksrq!='' and kpjsrq!=null and kpjsrq!=''">
            and to_char(t.invoice_date,'yyyy-mm-dd') between #{kpksrq} and #{kpjsrq}
        </if>
        <if test="rzksrq!=null and rzksrq!='' and rzjsrq!=null and rzjsrq!=''">
            and to_char(t.rzh_date,'yyyy-mm-dd') between #{rzksrq} and #{rzjsrq}
        </if>
        <if test="skssq!=null and skssq!=''">
            and t.rzh_belong_date = #{skssq}
        </if>
        <if test="rzhr!=null and rzhr!=''">
            and t.confirm_user like concat(concat('%',#{rzhr}),'%')
        </if>
        GROUP BY t.confirm_user ORDER by t.confirm_user
    </select>

    <select id="selectByGfshAndRznf" parameterType="java.util.Map"
            resultMap="FprzhrResultMap" databaseId="oracle">
        select
        t.confirm_user rzhr,
        COUNT(t.id ) as fpsl,
        sum(invoice_amount) as hjje,
        sum(tax_amount) as hjse ,
        sum(dk_tax_amount) as hjdk
        from t_dx_record_invoice t
        where
        t.auth_status = '4' and rzh_yesorno='1'
        and t.invoice_type in ('01','03','14')
        and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="kpksrq!=null and kpksrq!='' and kpjsrq!=null and kpjsrq!=''">
            and to_char(t.invoice_date,'yyyy-MM-dd') between #{kpksrq} and #{kpjsrq}
        </if>
        <if test="rzksrq!=null and rzksrq!='' and rzjsrq!=null and rzjsrq!=''">
            and to_char(t.rzh_date,'yyyy-MM-dd') between #{rzksrq} and #{rzjsrq}
        </if>
        <if test="skssq!=null and skssq!=''">
            and t.rzh_belong_date = #{skssq}
        </if>
        GROUP BY t.confirm_user ORDER by t.confirm_user
    </select>


</mapper>
