<?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.baseInterface.bigBTab.dao.BigBTabDao">

    <select id="countRzSe" parameterType="com.dxhy.api.model.bigB.tabInterface.RzTjTabReq" resultType="map" databaseId="mysql">

        select FORMAT(IFNULL(sum(total), 0.0),2) total,monthStr,num
        from

        (
        <!--发票-->
        SELECT
         sum(dk_tax_amount) total,
         MONTH(rzh_date) monthStr,
         count(*) num
        FROM t_dx_record_invoice
        WHERE
        rzh_yesorno = #{rzhYesorno}
        <if test="taxplayercodeDeptList != null and taxplayercodeDeptList.size() > 0">
            and gf_tax_no in
            <foreach collection="taxplayercodeDeptList" index="index" item="item"
                     open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="startDate!=null and startDate!=''">
            AND rzh_date &gt;= #{startDate}
        </if>
        <if test="endDate!=null and endDate!=''">
            AND rzh_date &lt;= #{endDate}
        </if>
          AND (invoice_type in(${krzpz}) or (invoice_type ='32' and lq_tdyslxDm='08'))
        GROUP BY
        MONTH(rzh_date)

        union all

        <!--海关缴款书-->
        SELECT
        sum(yx_tax_amount) total,
        MONTH(rzh_date) monthStr,
        count(*) num
        FROM t_dx_customs_record
        WHERE
        rzh_yesorno = #{rzhYesorno}
        and data_type = '17'
        <if test="taxplayercodeDeptList != null and taxplayercodeDeptList.size() > 0">
            and gf_tax_no in
            <foreach collection="taxplayercodeDeptList" index="index" item="item"
                     open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="startDate!=null and startDate!=''">
            AND rzh_date &gt;= #{startDate}
        </if>
        <if test="endDate!=null and endDate!=''">
            AND rzh_date &lt;= #{endDate}
        </if>
        GROUP BY
        MONTH(rzh_date)

        union all
        <!--出口转内销发票-->
        SELECT
        sum(export_yxse) total,
        MONTH(rzh_date) monthStr,
        count(*) num
        FROM t_dx_export_invoice
        WHERE
        rzh_yesorno = #{rzhYesorno}
        <if test="taxplayercodeDeptList != null and taxplayercodeDeptList.size() > 0">
            and gf_tax_no in
            <foreach collection="taxplayercodeDeptList" index="index" item="item"
                     open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="startDate!=null and startDate!=''">
            AND rzh_date &gt;= #{startDate}
        </if>
        <if test="endDate!=null and endDate!=''">
            AND rzh_date &lt;= #{endDate}
        </if>
        AND invoice_type in(${krzpz})
        GROUP BY
        MONTH(rzh_date)

        union all

        <!--出口转内销缴款书-->
        SELECT
        sum(yx_export_tax_amount) total,
        MONTH(rzh_date) monthStr,
        count(*) num
        FROM t_dx_export_customs
        WHERE
        rzh_yesorno = #{rzhYesorno}
        and data_type = '17'
        <if test="taxplayercodeDeptList != null and taxplayercodeDeptList.size() > 0">
            and gf_tax_no in
            <foreach collection="taxplayercodeDeptList" index="index" item="item"
                     open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="startDate!=null and startDate!=''">
            AND rzh_date &gt;= #{startDate}
        </if>
        <if test="endDate!=null and endDate!=''">
            AND rzh_date &lt;= #{endDate}
        </if>
        GROUP BY
        MONTH(rzh_date)

        )
        tj
        GROUP BY monthStr


    </select>

    <select id="countRzSe" parameterType="com.dxhy.api.model.bigB.tabInterface.RzTjTabReq" resultType="map" databaseId="oracle">

        select TRUNC(nvl(sum(total), 0.0),2) "total", monthStr as "monthStr",num as "num"
        from

        (
        <!--发票-->
        SELECT
        sum(dk_tax_amount) total,
        TO_NUMBER(TO_CHAR(rzh_date, 'mm')) monthStr,
        count(*) num
        FROM t_dx_record_invoice
        WHERE
        rzh_yesorno = #{rzhYesorno}
        <if test="taxplayercodeDeptList != null and taxplayercodeDeptList.size() > 0">
            and gf_tax_no in
            <foreach collection="taxplayercodeDeptList" index="index" item="item"
              open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="startDate!=null and startDate!=''">
            AND rzh_date &gt;= to_date(#{startDate},'yyyy-mm-dd hh24:mi:ss')
        </if>
        <if test="endDate!=null and endDate!=''">
            AND rzh_date &lt;= to_date(#{endDate},'yyyy-mm-dd hh24:mi:ss')
        </if>
        AND (invoice_type in(${krzpz}) or (invoice_type ='32' and lq_tdyslxDm='08'))
        GROUP BY
        TO_NUMBER(TO_CHAR(rzh_date, 'mm'))

        union all

        <!--海关缴款书-->
        SELECT
        sum(yx_tax_amount) total,
        TO_NUMBER(TO_CHAR(rzh_date, 'mm')) monthStr,
        count(*) num
        FROM t_dx_customs_record
        WHERE
        rzh_yesorno = #{rzhYesorno}
        and data_type = '17'
        <if test="taxplayercodeDeptList != null and taxplayercodeDeptList.size() > 0">
            and gf_tax_no in
            <foreach collection="taxplayercodeDeptList" index="index" item="item"
              open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="startDate!=null and startDate!=''">
            AND rzh_date &gt;= to_date(#{startDate},'yyyy-mm-dd hh24:mi:ss')
        </if>
        <if test="endDate!=null and endDate!=''">
            AND rzh_date &lt;= to_date(#{endDate},'yyyy-mm-dd hh24:mi:ss')
        </if>
        GROUP BY
        TO_NUMBER(TO_CHAR(rzh_date, 'mm'))

        union all
        <!--出口转内销发票-->
        SELECT
        sum(export_yxse) total,
        TO_NUMBER(TO_CHAR(rzh_date, 'mm')) monthStr,
        count(*) num
        FROM t_dx_export_invoice
        WHERE
        rzh_yesorno = #{rzhYesorno}
        <if test="taxplayercodeDeptList != null and taxplayercodeDeptList.size() > 0">
            and gf_tax_no in
            <foreach collection="taxplayercodeDeptList" index="index" item="item"
              open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="startDate!=null and startDate!=''">
            AND rzh_date &gt;= to_date(#{startDate},'yyyy-mm-dd hh24:mi:ss')
        </if>
        <if test="endDate!=null and endDate!=''">
            AND rzh_date &lt;= to_date(#{endDate},'yyyy-mm-dd hh24:mi:ss')
        </if>
        AND invoice_type in(${krzpz})
        GROUP BY
        TO_NUMBER(TO_CHAR(rzh_date, 'mm'))

        union all

        <!--出口转内销缴款书-->
        SELECT
        sum(yx_export_tax_amount) total,
        TO_NUMBER(TO_CHAR(rzh_date, 'mm')) monthStr,
        count(*) num
        FROM t_dx_export_customs
        WHERE
        rzh_yesorno = #{rzhYesorno}
        and data_type = '17'
        <if test="taxplayercodeDeptList != null and taxplayercodeDeptList.size() > 0">
            and gf_tax_no in
            <foreach collection="taxplayercodeDeptList" index="index" item="item"
              open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="startDate!=null and startDate!=''">
            AND rzh_date &gt;= to_date(#{startDate},'yyyy-mm-dd hh24:mi:ss')
        </if>
        <if test="endDate!=null and endDate!=''">
            AND rzh_date &lt;= to_date(#{endDate},'yyyy-mm-dd hh24:mi:ss')
        </if>
        GROUP BY
        TO_NUMBER(TO_CHAR(rzh_date, 'mm'))

        )
        tj
        GROUP BY monthStr,num


    </select>

    <!--进项税转出查询-->
    <select id="countJxszc" resultType="map">
        SELECT
        FORMAT(IFNULL(sum(a.out_tax_amount), 0.0),2) total , <!-- byjxsezce-->
        MONTH(a.out_date) monthStr
        FROM t_dx_record_invoice_out a
        LEFT JOIN t_dx_record_invoice b ON a.uuid = b.uuid
        <where>
            1 = 1
            <if test="taxplayercodeDeptList != null and taxplayercodeDeptList.size()>0">
                and b.gf_tax_no in
                <foreach collection="taxplayercodeDeptList" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

            <if test="startDate != null">
                <![CDATA[ and a.out_date >= #{startDate} ]]>
            </if>
            <if test="endDate != null">

                <![CDATA[ and a.out_date <= #{endDate} ]]>
            </if>
        </where>
        GROUP BY
        MONTH(a.out_date)
        ORDER BY
        MONTH(a.out_date) DESC
    </select>

    <select id="countJxszc" resultType="map" databaseId="oracle">
        SELECT
        TRUNC(nvl(sum(a.out_tax_amount), 0.0),2) as "total" , <!-- byjxsezce-->
        TO_NUMBER(TO_CHAR(a.out_date, 'mm')) as "monthStr"
        FROM t_dx_record_invoice_out a
        LEFT JOIN t_dx_record_invoice b ON a.uuid = b.uuid
        <where>
            1 = 1
            <if test="taxplayercodeDeptList != null and taxplayercodeDeptList.size()>0">
                and b.gf_tax_no in
                <foreach collection="taxplayercodeDeptList" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

            <if test="startDate != null">
                <![CDATA[ and a.out_date >= to_date(#{startDate},'yyyy-mm-dd hh24:mi:ss') ]]>
            </if>
            <if test="endDate != null">

                <![CDATA[ and a.out_date <=to_date( #{endDate},'yyyy-mm-dd hh24:mi:ss') ]]>
            </if>
        </where>
        GROUP BY
        TO_NUMBER(TO_CHAR(a.out_date, 'mm'))
        ORDER BY
        TO_NUMBER(TO_CHAR(a.out_date, 'mm')) DESC
    </select>

    <select id="countYcfpsl" resultType="map">
        select
        count(*) total,
        MONTH(status_update_date) monthStr
        from t_dx_record_invoice t
        where t.invoice_status != '0'
        and rzh_yesorno = '0'
        and (t.invoice_type in (${krzpz}) or (t.invoice_type = '32' and t.lq_tdyslxDm = '08'))
        <if test="taxplayercodeDeptList != null and taxplayercodeDeptList.size()>0">
            and t.gf_tax_no in
            <foreach collection="taxplayercodeDeptList" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="startDate!=null and startDate!=''">
            AND status_update_date &gt;= #{startDate}
        </if>
        <if test="endDate!=null and endDate!=''">
            AND status_update_date &lt;= #{endDate}
        </if>

        GROUP BY
        MONTH(status_update_date)
        ORDER BY
        MONTH(status_update_date) DESC

    </select>

    <select id="countYcfpsl" resultType="map" databaseId="oracle">
        select
        count(*) as "total",
        TO_NUMBER(TO_CHAR(status_update_date, 'mm')) as "monthStr"
        from t_dx_record_invoice t
        where t.invoice_status != '0'
        and rzh_yesorno = '0'
        and (t.invoice_type in (${krzpz}) or (t.invoice_type = '32' and t.lq_tdyslxDm = '08'))
        <if test="taxplayercodeDeptList != null and taxplayercodeDeptList.size()>0">
            and t.gf_tax_no in
            <foreach collection="taxplayercodeDeptList" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="startDate!=null and startDate!=''">
            AND status_update_date &gt;= to_date(#{startDate},'yyyy-mm-dd hh24:mi:ss')
        </if>
        <if test="endDate!=null and endDate!=''">
            AND status_update_date &lt;= to_date(#{endDate},'yyyy-mm-dd hh24:mi:ss')
        </if>

        GROUP BY
        TO_NUMBER(TO_CHAR(status_update_date, 'mm'))
        ORDER BY
        TO_NUMBER(TO_CHAR(status_update_date, 'mm')) DESC

    </select>








    <select id="countYcfpinfo" resultType="com.dxhy.api.model.bigB.tabInterface.YcFptjTabRespData">
        select
        t.invoice_type fpzldm,
        count(*) fpsl,
        FORMAT(sum(t.tax_amount), 2) AS fpse
        from t_dx_record_invoice t
        where t.invoice_status != '0'
        and rzh_yesorno = '0'
        and (t.invoice_type in (${krzpz}) or (t.invoice_type = '32' and t.lq_tdyslxDm = '08'))
        <if test="taxplayercodeDeptList != null and taxplayercodeDeptList.size()>0">
            and t.gf_tax_no in
            <foreach collection="taxplayercodeDeptList" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="startDate!=null and startDate!=''">
            AND status_update_date &gt;= #{startDate}
        </if>
        <if test="endDate!=null and endDate!=''">
            AND status_update_date &lt;= #{endDate}
        </if>
        group by fpzldm
    </select>

    <select id="countYcfpinfo" resultType="com.dxhy.api.model.bigB.tabInterface.YcFptjTabRespData" databaseId="oracle">
        select
        t.invoice_type fpzldm,
        count(*) fpsl,
        TRUNC(sum(t.tax_amount), 2) AS fpse
        from t_dx_record_invoice t
        where t.invoice_status != '0'
        and rzh_yesorno = '0'
        and (t.invoice_type in (${krzpz}) or (t.invoice_type = '32' and t.lq_tdyslxDm = '08'))
        <if test="taxplayercodeDeptList != null and taxplayercodeDeptList.size()>0">
            and t.gf_tax_no in
            <foreach collection="taxplayercodeDeptList" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="startDate!=null and startDate!=''">
            AND status_update_date &gt;= to_date(#{startDate},'yyyy-mm-dd hh24:mi:ss')
        </if>
        <if test="endDate!=null and endDate!=''">
            AND status_update_date &lt;= to_date(#{endDate},'yyyy-mm-dd hh24:mi:ss')
        </if>
        group by invoice_type
    </select>
</mapper>
