<?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.base.dao.BaseFpcjhzLbDao">

    <resultMap id="BaseResultMap" type="com.dxhy.base.model.BaseFpcjCjhzRespVO">
        <result column="sl" property="sl"/>
        <result column="je" property="je"/>
        <result column="se" property="se"/>
        <result column="cjrq" property="cjrq"/>
        <result column="gfsh" property="gfsh"/>
    </resultMap>

    <resultMap id="HjxxMap" type="com.dxhy.base.model.BaseFpcjCjhzRespVO">
        <result column="hjsl" property="sl"/>
        <result column="hjje" property="je"/>
        <result column="hjse" property="se"/>
    </resultMap>

    <resultMap id="GfxxMap" type="com.dxhy.base.model.BaseFpcjCjhzRespVO">
        <result column="gfsh" property="gfsh"/>
        <result column="gfmc" property="gfmc"/>
    </resultMap>

    <select id="selectByGfshAndCjrq" parameterType="java.util.Map"
            resultMap="BaseResultMap" databaseId="mysql">
        select COUNT(1)sl,
        ifnull(SUM(t.invoice_amount),0) je,
        ifnull(SUM(t.tax_amount),0) se,
        DATE_FORMAT(t.create_date,'%Y-%m-%d')
        cjrq,t.gf_tax_no gfsh
        from t_dx_record_invoice t
        WHERE date_format(t.create_date,'%Y-%m-%d') between #{cjksrq} and #{cjjsrq}
        and t.company = #{company}
        and (invoice_type in(${krzpz}) or (invoice_type ='32' and lq_tdyslxDm='08'))
        and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="ycpbz != null and ycpbz != '' and ycpbz != 'null' ">
            and t.invoice_status !='0'
        </if>
        and t.source_system = '0'
        group by cjrq,gfsh
        order by cjrq desc
    </select>
    <select id="selectByGfshAndCjrq" parameterType="java.util.Map"
            resultMap="BaseResultMap" databaseId="oracle">
        select COUNT(1)sl,
        COALESCE(SUM(t.invoice_amount),0) je,
        COALESCE(SUM(t.tax_amount),0) se, to_char(t.create_date,'YYYY-MM-DD')
        cjrq,t.gf_tax_no gfsh
        from t_dx_record_invoice t
        WHERE to_char(t.create_date,'yyyy-mm-dd') between #{cjksrq} and #{cjjsrq}
        and t.company = #{company}
        and (invoice_type in(${krzpz}) or (invoice_type ='32' and lq_tdyslxDm='08'))
        and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="ycpbz != null and ycpbz != '' and ycpbz != 'null' ">
            and t.invoice_status !='0'
        </if>
        and t.source_system = '0'
        group by to_char(t.create_date,'YYYY-MM-DD'),gf_tax_no
        order by to_char(t.create_date,'YYYY-MM-DD') desc
    </select>

    <select id="selectHjxxByGfshAndCjrq" parameterType="java.util.Map"
            resultMap="HjxxMap" 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 date_format(t.create_date,'%Y-%m-%d') between #{cjksrq} and #{cjjsrq}
        and t.company = #{company}
        and (invoice_type in(${krzpz}) or (invoice_type ='32' and lq_tdyslxDm='08'))
        and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="ycpbz != null and ycpbz != '' and ycpbz != 'null' ">
            and t.invoice_status !='0'
        </if>
        and t.source_system = '0'
    </select>

    <select id="selectHjxxByGfshAndCjrq" parameterType="java.util.Map"
            resultMap="HjxxMap" 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 to_char(t.create_date,'yyyy-mm-dd') between #{cjksrq} and #{cjjsrq}
        and t.company = #{company}
        and (invoice_type in(${krzpz}) or (invoice_type ='32' and lq_tdyslxDm='08'))
        and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="ycpbz != null and ycpbz != '' and ycpbz != 'null' ">
            and t.invoice_status !='0'
        </if>
        and t.source_system = '0'
    </select>

    <select id="selectGfxxList" parameterType="java.util.Map"
            resultMap="GfxxMap">
        select distinct t.gf_tax_no gfsh,t.gf_name gfmc
        from t_dx_record_invoice t
        WHERE t.source_system = '0' and t.gf_name is not null and t.gf_tax_no in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

</mapper>
