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

    <resultMap id="WbmxResultMap" type="com.dxhy.core.model.FpcjWbfpmxRespVO">
        <result column="invoice_code" property="fpdm"/>
        <result column="invoice_no" property="fphm"/>
        <result column="invoiceType" property="fplx"/>
        <result column="check_code" property="jym"/>

        <result column="gf_name" property="gfmc"/>
        <result column="xf_name" property="xfmc"/>
        <result column="invoice_date" property="kprq"/>
        <result column="invoice_amount" property="je"/>
        <result column="tax_amount" property="se"/>
        <result column="create_date" property="cjrq"/>
        <result column="invoice_source" property="invoiceSourcs"/>
        <result column="ele_invoice_no" property="eleInvoiceNo"/>
    </resultMap>

    <resultMap id="WbmxhjResultMap" type="com.dxhy.api.model.invoice.TDxRecordInvoice">
        <result column="hjsl" property="id"/>
        <result column="hjje" property="invoiceAmount"/>
        <result column="hjse" property="taxAmount"/>
    </resultMap>
    <!-- 自定义底账表结果集映射-->
    <resultMap id="recordInvoiceMap" type="com.dxhy.core.model.RecordInvoiceModel">
        <result property="cutApproveDate" column="lastdate"/>
        <result property="invoiceType" column="invoice_type"/>
        <result property="invoiceCode" column="invoice_code"/>
        <result property="invoiceNo" column="invoice_no"/>
        <result property="invoiceDate" column="invoice_date"/>
        <result property="gfName" column="gf_name"/>
        <result property="xfName" column="xf_name"/>
        <result property="gfTaxNo" column="gf_tax_no"/>
        <result property="taxAmount" column="taxAmount"/>
        <result property="invoiceAmount" column="invoiceAmount"/>
        <result property="dkAmount" column="dkAmount"/>
    </resultMap>

    <select id="selectWbfpmx" parameterType="java.util.Map"
            resultMap="WbmxResultMap" databaseId="mysql">
        select
        t.invoice_type,t.invoice_code,t.invoice_no,t.invoice_type
        invoiceType,date_format(invoice_date,'%Y-%m-%d')
        invoice_date,t.invoice_amount,t.tax_amount,t.gf_name,t.xf_name,t.gf_tax_no,
        t.xf_tax_no,t.check_code,date_format(create_date,'%Y-%m-%d') create_date,invoice_source,t.ele_invoice_no
        from t_dx_record_invoice t
        where t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        and (invoice_type in(${krzpz}) or (invoice_type ='32' and lq_tdyslxDm='08'))
        <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '99' ">
            and t.invoice_type = #{invoiceType}
        </if>
        <if test="invoiceNo != null and invoiceNo != '' and invoiceNo != 'null' ">
            and t.invoice_no = #{invoiceNo}
        </if>
        <if test="company != null and company !=''">
            and t.company = #{company}
        </if>
        <if test="invoiceSource!=null and invoiceSource!='' and invoiceSource!='null' and invoiceSource!='99'">
            and t.invoice_source = #{invoiceSource}
        </if>
        <if test="eleInvoiceNo!=null and eleInvoiceNo!='' and eleInvoiceNo!='null'">
            and t.ele_invoice_no = #{eleInvoiceNo}
        </if>
        and t.source_system = '0'
        and t.detail_yesorno = '0'
        and t.create_date between #{cjksrq} and #{cjjsrq}
        order by t.create_date desc
    </select>

    <select id="selectWbfpmx" parameterType="java.util.Map"
            resultMap="WbmxResultMap" databaseId="oracle">
        select
        t.invoice_type,t.invoice_code,t.invoice_no,t.invoice_type invoiceType,
        to_char(invoice_date,'yyyy-MM-dd')
        invoice_date,t.invoice_amount,t.tax_amount,t.gf_name,t.gf_tax_no,t.xf_name,t.xf_tax_no,
        t.check_code,to_char(create_date,'yyyy-MM-dd') create_date,invoice_source,t.ele_invoice_no
        from t_dx_record_invoice t
        where t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        and (invoice_type in(${krzpz}) or (invoice_type ='32' and lq_tdyslxDm='08'))
        <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '99' ">
            and t.invoice_type = #{invoiceType}
        </if>
        <if test="invoiceNo != null and invoiceNo != '' and invoiceNo != 'null' ">
            and t.invoice_no = #{invoiceNo}
        </if>
        <if test="company != null and company !=''">
            and t.company = #{company}
        </if>
        <if test="invoiceSource!=null and invoiceSource!='' and invoiceSource!='null' and invoiceSource!='99'">
            and t.invoice_source = #{invoiceSource}
        </if>
        <if test="eleInvoiceNo!=null and eleInvoiceNo!='' and eleInvoiceNo!='null'">
            and t.ele_invoice_no = #{eleInvoiceNo}
        </if>
        and t.source_system = '0'
        and t.detail_yesorno = '0'
        and t.create_date between to_date(#{cjksrq},'yyyy-mm-dd hh24:mi:ss') and to_date(#{cjjsrq},'yyyy-mm-dd hh24:mi:ss')
        order by t.create_date desc
    </select>

    <select id="selectWbfpmxHj" parameterType="java.util.Map"
            resultMap="WbmxhjResultMap" databaseId="mysql">
        select
        count(1) hjsl,
        ifnull(sum(t.invoice_amount),0) hjje,
        ifnull(sum(t.tax_amount),0) hjse
        from t_dx_record_invoice t
        where t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        and (invoice_type in(${krzpz}) or (invoice_type ='32' and lq_tdyslxDm='08'))
        <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '99' ">
            and t.invoice_type = #{invoiceType}
        </if>
        <if test="invoiceNo != null and invoiceNo != '' and invoiceNo != 'null' ">
            and t.invoice_no = #{invoiceNo}
        </if>
        <if test="company != null and company !=''">
            and t.company = #{company}
        </if>
        <if test="invoiceSource!=null and invoiceSource!='' and invoiceSource!='null' and invoiceSource!='99'">
            and t.invoice_source = #{invoiceSource}
        </if>
        <if test="eleInvoiceNo!=null and eleInvoiceNo!='' and eleInvoiceNo!='null'">
            and t.ele_invoice_no = #{eleInvoiceNo}
        </if>
        and t.source_system = '0'
        and t.detail_yesorno = '0'
        and t.create_date between #{cjksrq} and #{cjjsrq}
    </select>

    <select id="selectWbfpmxHj" parameterType="java.util.Map"
            resultMap="WbmxhjResultMap" databaseId="oracle">
        select
        count(1) hjsl,
        COALESCE(sum(t.invoice_amount),0) hjje,
        COALESCE(sum(t.tax_amount),0) hjse
        from t_dx_record_invoice t
        where t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        and (invoice_type in(${krzpz}) or (invoice_type ='32' and lq_tdyslxDm='08'))
        <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '99' ">
            and t.invoice_type = #{invoiceType}
        </if>
        <if test="invoiceNo != null and invoiceNo != '' and invoiceNo != 'null' ">
            and t.invoice_no = #{invoiceNo}
        </if>
        <if test="company != null and company !=''">
            and t.company = #{company}
        </if>
        <if test="invoiceSource!=null and invoiceSource!='' and invoiceSource!='null' and invoiceSource!='99'">
            and t.invoice_source = #{invoiceSource}
        </if>
        <if test="eleInvoiceNo!=null and eleInvoiceNo!='' and eleInvoiceNo!='null'">
            and t.ele_invoice_no = #{eleInvoiceNo}
        </if>
        and t.source_system = '0'
        and t.detail_yesorno = '0'
        and t.create_date between to_date(#{cjksrq},'yyyy-mm-dd hh24:mi:ss') and to_date(#{cjjsrq},'yyyy-mm-dd hh24:mi:ss')
    </select>


    <select id="queryInvoice" parameterType="java.util.Map"
            resultMap="recordInvoiceMap" databaseId="mysql">
        SELECT
        t.invoice_type,
        t.gf_tax_no,
        DATE_FORMAT(DATE_ADD(t.invoice_date, INTERVAL 360 DAY), '%Y%m') AS lastdate,
        t.invoice_code,
        t.invoice_no,
        DATE_FORMAT(t.invoice_date , '%Y-%m-%d') invoice_date,
        t.gf_name,
        t.xf_name,
        t.invoice_amount AS invoiceAmount,
        t.tax_amount AS taxAmount
        FROM
        t_dx_record_invoice t
        LEFT JOIN t_dx_tax_current tdtc ON t.gf_tax_no = tdtc.taxno
        WHERE
        valid = 1
        AND (t.rzh_belong_date IS NULL OR t.rzh_belong_date = '')
        AND t.invoice_amount > 0
        AND invoice_status = 0
        AND t.rzh_yesorno = 0
        AND t.invoice_type IN ("01", "03", "14")
        AND t.confirm_date IS NULL
        AND t.rzh_date IS NULL
        AND t.tax_amount >= 0
        AND t.gf_tax_no IN
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="month !=null and month !=''">
            AND period_add(tdtc.current_tax_period,#{month}) >= DATE_FORMAT(DATE_ADD(invoice_date, INTERVAL 360 DAY),
            '%Y%m')
        </if>
        AND DATE_FORMAT(invoice_date, '%Y%m%d') >= tdtc.select_start_date
        ORDER BY
        t.invoice_date ASC
    </select>
    <select id="queryInvoice" parameterType="java.util.Map"
            resultMap="recordInvoiceMap" databaseId="oracle">
        SELECT
        t.invoice_type,
        t.gf_tax_no,
        to_char(t.invoice_date + 360, 'yyyyMM') lastdate,
        t.invoice_code,
        t.invoice_no,
        to_char(t.invoice_date , 'yyyy-MM-dd') invoice_date,
        t.gf_name,
        t.xf_name,
        t.invoice_amount invoiceAmount,
        t.tax_amount taxAmount ,
        t.dk_tax_amount dxAmount
        FROM
        t_dx_record_invoice t
        LEFT JOIN t_dx_tax_current tdtc ON t.gf_tax_no = tdtc.taxno
        WHERE
        valid = 1
        AND (t.rzh_belong_date IS NULL OR t.rzh_belong_date = '')
        AND t.invoice_amount > 0
        AND invoice_status = 0
        AND t.rzh_yesorno = 0
        AND t.invoice_type IN ('01', '03', '14')
        AND t.confirm_date IS NULL
        AND t.rzh_date IS NULL
        AND t.tax_amount >= 0
        AND t.gf_tax_no IN
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="month !=null and month !=''">
            AND add_months(to_date(tdtc.current_tax_period,'yyyyMM'),#{month}) >= to_date(to_char(invoice_date + 360,
            'yyyyMM'),'yyyyMM')
        </if>
        AND to_char(invoice_date, 'yyyyMMdd') >= tdtc.select_start_date
        ORDER BY
        t.invoice_date ASC
    </select>

</mapper>
