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

    <select id="querySaleCountRate" resultType="com.dxhy.extend.entity.ExtSaleCountModel">
        SELECT
        tax_rate,
        sum( invoice_amount_pt ) invoice_amount_pt,
        sum( tax_amount_pt ) tax_amount_pt,
        sum( total_amount_pt ) total_amount_pt,
        sum( invoice_amount_nt ) invoice_amount_nt,
        sum( tax_amount_nt ) tax_amount_nt,
        sum( total_amount_nt ) total_amount_nt,
        sum( invoice_amount_pt_void ) invoice_amount_pt_void,
        sum( tax_amount_pt_void ) tax_amount_pt_void,
        sum( total_amount_pt_void ) total_amount_pt_void,
        sum( invoice_amount_nt_void ) invoice_amount_nt_void,
        sum( tax_amount_nt_void ) tax_amount_nt_void,
        sum( total_amount_nt_void ) total_amount_nt_void
        FROM
        t_dx_alltaxes_sale_tax_rate where billing_date=#{skssq}
        <if test="fplx != null and fplx != '' and fplx != 'null' and fplx != '99' ">
            and invoice_kind_code =#{fplx}
        </if>
        and tax_number in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="invoiceSource !=null and invoiceSource!='' and invoiceSource!='99'">
            and invoice_source = #{invoiceSource}
        </if>
        GROUP BY
        tax_rate order by tax_rate
    </select>


    <select id="querySaleItem" resultType="com.dxhy.extend.entity.ExtSaleItemCountModel">
        select item_code,item_name,tax_number,invoice_amount,tax_amount,total_amount,invoice_num from
        t_dx_alltaxes_sale_detail_final where tax_number in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '99' ">
            and invoice_type = #{invoiceType}
        </if>
        and billing_date=#{skssq}
        <if test="searchKey != null and searchKey != '' and searchKey != 'null' ">
            and item_name like
            CONCAT('%',#{searchKey},'%')
        </if>
        and invoice_num>0
    </select>

    <select id="querySaleItem" resultType="com.dxhy.extend.entity.ExtSaleItemCountModel" databaseId="oracle">
        select item_code,item_name,tax_number,invoice_amount,tax_amount,total_amount,invoice_num from
        t_dx_alltaxes_sale_detail_final where tax_number in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="invoiceType != null and invoiceType != '' and invoiceType != 'null' and invoiceType != '99' ">
            and invoice_type = #{invoiceType}
        </if>
        and billing_date=#{skssq}
        <if test="searchKey != null and searchKey != '' and searchKey != 'null' ">
            and item_name like
            CONCAT(CONCAT('%',#{searchKey}),'%')
        </if>
        and invoice_num>0
    </select>

    <select id="querySaleName" resultType="java.lang.String" databaseId="mysql">
        select xf_name
        from t_dx_sale_record_invoice
        where xf_tax_no = #{taxNumber}
        limit 1
    </select>

    <select id="querySaleName" resultType="java.lang.String" databaseId="oracle">
        select * from (
        select xf_name
        from t_dx_sale_record_invoice
        where xf_tax_no = #{taxNumber}
        ) tmp where ROWNUM &lt;= 1
    </select>


    <select id="querySaleNameCondition" resultType="com.dxhy.extend.entity.ExtSaleItemConditionModel">
        select yhzc, invoice_type, tax_rate, goods_code
        from t_dx_alltaxes_sale_detail
        where item_name = #{itemName}
    </select>


    <select id="querySaleItemInvoices" resultType="com.dxhy.extend.entity.ExtSaleItemInvoiceModel" databaseId="mysql">
        select distinct DATE_FORMAT(t.invoice_date,'%Y-%m-%d')
        invoiceDate,t.invoice_code,t.invoice_no,t.gf_name,t.gf_tax_no,t.invoice_amount,t.tax_amount,t.total_amount,t.invoice_type,t.invoice_status
        from t_dx_sale_record_invoice t left join dxhy_detail.t_dx_record_invoice_detail_sales t1 on t.uuid = t1.uuid
        where
        t1.incentive_flag like CONCAT('%',#{yhzc},'%') and t1.tax_rate in
        <foreach collection="taxRate" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="goodsCode.length > 0">
            and substr(t1.goods_num,1,1) in
            <foreach collection="goodsCode" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        and t.xf_tax_no =#{taxno} and date_format(t.invoice_date,'%Y%m') = #{skssq}
        and t.invoice_type in
        <foreach collection="invoiceType" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>


    </select>

    <select id="querySaleItemInvoices" resultType="com.dxhy.extend.entity.ExtSaleItemInvoiceModel" databaseId="oracle">
        select distinct to_char(t.invoice_date,'YYYY-MM-DD')
        invoiceDate,t.invoice_code,t.invoice_no,t.gf_name,t.gf_tax_no,t.invoice_amount,t.tax_amount,t.total_amount,t.invoice_type,t.invoice_status
        from t_dx_sale_record_invoice t left join dxhy_detail.t_dx_record_invoice_detail_sales t1 on t.uuid = t1.uuid
        where
        instr(t.incentive_flag,#{yhzc}) > 0 and t1.tax_rate in
        <foreach collection="taxRate" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="goodsCode.length > 0">
            and substr(t1.goods_num,1,1) in
            <foreach collection="goodsCode" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        and t.xf_tax_no =#{taxno} and to_char(t.invoice_date,'YYYYMM') = #{skssq}
        and t.invoice_type in
        <foreach collection="invoiceType" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

</mapper>
