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

    <select id="selectByDkCount" parameterType="java.util.Map"
            resultType="com.dxhy.base.model.BaseTDxDkCountRespVO" databaseId="mysql">
        select t.id,t.taxno,t.taxname,t.skssq,date_format(t.create_date ,'%Y-%m-%d') createDate,t.tj_status
        tjStatus,t.qs_status qsStatus,t.sfdqskssq,
        t.cxtj_status cxtjStatus,t.tj_date tjDate,t.cxqs_status cxqsStatus,t.apply_msg,
        t.tj_user,t.qr_user,date_format(t.qr_date,'%Y-%m-%d %H:%i:%s')qrDate
        from t_dx_dk_count t
        where
        t.taxno in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="skssq != null and skssq != '' and skssq != 'null' ">
            and t.skssq = #{skssq}
        </if>
        <if test='tjzt != null and tjzt != "" and tjzt != "null" and tjzt != "6"'>
            and t.tj_status = #{tjzt}
        </if>
        <if test='tjzt=="0"'>
            and sfdqskssq = '1'
            <![CDATA[ and date_add(str_to_date(concat(t.skssq,01),'%Y%m%d'), interval 1 month)< now() ]]>
        </if>
        <if test='tjzt == "6" or qrtjzt == "6"'>
            and sfdqskssq = '1'
            <![CDATA[ and date_add(str_to_date(concat(t.skssq,01),'%Y%m%d'), interval 1 month)> str_to_date(date_format(now(),'%Y%m%d'),'%Y%m%d') ]]>
        </if>
        <if test='qrtjzt != null and qrtjzt != "" and qrtjzt != "null" and qrtjzt !="6"'>
            and t.qs_status = #{qrtjzt}
        </if>

        order by t.skssq desc,t.taxno
    </select>

    <select id="selectByDkCount" parameterType="java.util.Map"
            resultType="com.dxhy.base.model.BaseTDxDkCountRespVO" databaseId="oracle">
        select t.id,t.taxno,t.taxname,t.skssq,t.create_date createDate,t.tj_status tjStatus,t.qs_status
        qsStatus,t.sfdqskssq,
        t.cxtj_status cxtjStatus,t.tj_date tjDate,t.cxqs_status cxqsStatus,t.apply_msg,
        t.tj_user,t.qr_user,to_char(t.qr_date,'yyyy-mm-dd hh24:mi:ss') qrDate
        from t_dx_dk_count t
        where
        t.taxno in
        <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="skssq != null and skssq != '' and skssq != 'null' ">
            and t.skssq = #{skssq}
        </if>
        <if test='tjzt != null and tjzt != "" and tjzt != "null" and tjzt != "6"'>
            and t.tj_status = #{tjzt}
        </if>
        <if test='tjzt == "0"'>
            and sfdqskssq = '1'
            <![CDATA[ and add_months(to_date(concat(t.skssq,'01'),'yyyyMMdd'),1) < sysdate ]]>
        </if>
        <if test='tjzt == "6" or qrtjzt == "6"'>
            and sfdqskssq = '1'
            <![CDATA[ and add_months(to_date(concat(t.skssq,'01'),'yyyyMMdd'),1) > to_date(to_char(sysdate,'yyyyMMdd'),'yyyyMMdd') ]]>
        </if>
        <if test='qrtjzt != null and qrtjzt != "" and qrtjzt != "null" and qrtjzt !="6"'>
            and t.qs_status = #{qrtjzt}
        </if>
        order by t.skssq desc,t.taxno
    </select>

    <select id="getPassword" resultType="string">
      select password from sys_enterprise_config where nsrsbh = #{taxno}
    </select>
    <select id="selectSumCountDetail" resultType="com.dxhy.base.model.BaseTDxDkCountDetailSumVO" databaseId="mysql">
      select dk_invoice_count invoiceNum,
        dk_tax_amount_count dksehj
        from t_dx_dk_count_detail
        where taxno = #{taxno}
        and tj_month = #{skssq}
        and (invoice_type='99' or invoice_type is null) and (tjjglx = '1' or tjjglx is null)
    </select>

    <select id="selectSumCountDetail" resultType="com.dxhy.base.model.BaseTDxDkCountDetailSumVO" databaseId="oracle">
      select dk_invoice_count invoiceNum,
        dk_tax_amount_count dksehj
        from t_dx_dk_count_detail
        where taxno = #{taxno}
        and tj_month = #{skssq}
        and (invoice_type='99' or invoice_type is null) and (tjjglx = '1' or tjjglx is null)
    </select>
</mapper>
