<?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.job.dao.TaxRefundDao">

    <!--获取待退税确认申请的数据-->
    <select databaseId="mysql" id="queryRefundList" resultType="java.util.Map">
        select d.gf_tax_no as gfTaxNo,
        d.rzh_belong_date as rzhBelongDate,
        c.taxname as taxname,
        sum(d.tax_amount) as taxAmount from (
        SELECT
        p.gf_tax_no,
        p.rzh_belong_date,
        sum(p.tax_amount) AS tax_amount
        FROM
        t_dx_customs_record p
        WHERE p.rzh_yesorno = '1'
        and p.rzlx in ('2')
        and p.data_type = '17'
        <if test="nsrsbh !=null and nsrsbh!='' and nsrsbh != 'null' ">
            and p.gf_tax_no = #{nsrsbh}
        </if>
        GROUP BY
        p.rzh_belong_date, p.gf_tax_no
        union
        SELECT
        p.gf_tax_no,
        p.rzh_belong_date,
        sum(p.tax_amount) AS tax_amount
        FROM
        t_dx_record_invoice p
        WHERE p.rzh_yesorno = '1'
        and p.rzlx in ('2')
        and p.invoice_type in('01','08','31','185')
        <if test="nsrsbh !=null and nsrsbh!='' and nsrsbh != 'null' ">
            and p.gf_tax_no = #{nsrsbh}
        </if>
        GROUP BY
        p.rzh_belong_date, p.gf_tax_no) d left join
        t_dx_tax_current c on d.gf_tax_no = c.taxno

        GROUP BY d.gf_tax_no, d.rzh_belong_date
    </select>


    <select databaseId="oracle" id="queryRefundList" resultType="java.util.Map">
        select d.gf_tax_no as "gfTaxNo",
        d.rzh_belong_date as "rzhBelongDate",
        c.taxname as "taxname",
        sum(d.tax_amount) as "taxAmount" from (
        SELECT
        p.gf_tax_no,
        p.rzh_belong_date,
        sum(p.tax_amount) AS tax_amount
        FROM
        t_dx_customs_record p
        WHERE p.rzh_yesorno = '1'
        and p.rzlx in ('2')
        and p.data_type = '17'
        <if test="nsrsbh !=null and nsrsbh!='' and nsrsbh != 'null' ">
            and p.gf_tax_no = #{nsrsbh}
        </if>
        GROUP BY
        p.rzh_belong_date, p.gf_tax_no
        union
        SELECT
        p.gf_tax_no,
        p.rzh_belong_date,
        sum(p.tax_amount) AS tax_amount
        FROM
        t_dx_record_invoice p
        WHERE p.rzh_yesorno = '1'
        and p.rzlx in ('2')
        and p.invoice_type in('01','08','31','185')
        <if test="nsrsbh !=null and nsrsbh!='' and nsrsbh != 'null' ">
            and p.gf_tax_no = #{nsrsbh}
        </if>
        GROUP BY
        p.rzh_belong_date, p.gf_tax_no) d left join
        t_dx_tax_current c on d.gf_tax_no = c.taxno

        GROUP BY d.gf_tax_no, d.rzh_belong_date,c.taxname
    </select>

    <!--获取要退税确认结果的数据-->
    <select databaseId="mysql" id="queryRefundGetResult" resultType="com.dxhy.api.model.db.TDxLqApplyStatRecordEntity">
        select id,nsrsbh,skssq,pclsh,sqlx,result_status,create_date
        from t_dx_lq_apply_stat_record
        where result_status = 'N'
        and nsrsbh = #{nsrsbh}
        and sqlx = 5
    </select>
    <select databaseId="oracle" id="queryRefundGetResult" resultType="com.dxhy.api.model.db.TDxLqApplyStatRecordEntity">
        select id,nsrsbh,skssq,pclsh,sqlx,result_status,create_date
        from t_dx_lq_apply_stat_record
        where result_status = 'N'
        and nsrsbh = #{nsrsbh}
        and sqlx = 5
    </select>

    <!--获取待退税确认申请的数据-->
    <select  databaseId="mysql" id="queryRefundDeatilList" resultType="java.util.Map">
        SELECT
        p.customs_code uuid, p.data_type invoiceType
        FROM
        t_dx_customs_record p
        WHERE p.rzh_yesorno = '1'
        and p.rzlx in ('2')
        and p.data_type = '17'
        <if test="nsrsbh !=null and nsrsbh!='' and nsrsbh != 'null' ">
            and p.gf_tax_no = #{nsrsbh}
        </if>

        union

        SELECT
        p.uuid,p.invoice_type invoiceType
        FROM
        t_dx_record_invoice p
        WHERE p.rzh_yesorno = '1'
        and p.rzlx in ('2')
        and p.invoice_type in('01','08','31','185')
        <if test="nsrsbh !=null and nsrsbh!='' and nsrsbh != 'null' ">
            and p.gf_tax_no = #{nsrsbh}
        </if>
    </select>

    <select  databaseId="oracle" id="queryRefundDeatilList" resultType="java.util.Map">
        SELECT
        p.customs_code "uuid", p.data_type "invoiceType"
        FROM
        t_dx_customs_record p
        WHERE p.rzh_yesorno = '1'
        and p.rzlx in ('2')
        and p.data_type = '17'
        <if test="nsrsbh !=null and nsrsbh!='' and nsrsbh != 'null' ">
            and p.gf_tax_no = #{nsrsbh}
        </if>

        union

        SELECT
        p.uuid,p.invoice_type invoiceType
        FROM
        t_dx_record_invoice p
        WHERE p.rzh_yesorno = '1'
        and p.rzlx in ('2')
        and p.invoice_type in('01','08','31','185')
        <if test="nsrsbh !=null and nsrsbh!='' and nsrsbh != 'null' ">
            and p.gf_tax_no = #{nsrsbh}
        </if>
    </select>

</mapper>
