<?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.jxpt.baseService.cpyAuthInvoice.dao.CpyRecordCustomDao" >

	<resultMap id="BaseResultMap"
			   type="com.dxhy.api.model.cpy.cpyCustom.TDxRecordCustomCpyVo">
		<result column="customs_code" property="customsCode"/>
		<result column="tfrq" property="tfrq"/>
		<result column="tax_amount" property="taxAmount"/>
		<result column="lq_sfchsd" property="lqSfchsd"/>
		<result column="gf_tax_no" property="gfTaxNo"/>
		<result column="gf_name" property="gfName"/>
		<result column="lq_xfsyt" property="lqXfsyt"/>

		<result column="gxclzt" property="gxclzt"/>
		<result column="gxsbyy" property="gxsbyy"/>
		<result column="czsj" property="czsj"/>
		<result column="czr" property="czr"/>
		<result column="czr_name" property="czrName"/>

	</resultMap>


    <resultMap id="BbfpzhcxhjResultMap"
               type="com.dxhy.api.model.cpy.cpyCustom.TDxRecordCustomCpyVo">
        <result column="hjsl" property="hjsl"/>
        <result column="hjse" property="hjse"/>
    </resultMap>

	<!--成品油勾选页面合计-->
    <select id="selectAuthPageListHj" databaseId="mysql" resultType="com.dxhy.api.model.cpy.cpyCustom.TDxRecordCustomCpyVo" resultMap="BbfpzhcxhjResultMap" >
		select
        count(1) hjsl,
		ifnull(sum(t.tax_amount),0) hjse
        from t_dx_customs_cpy_gx cpygx left join t_dx_customs_record t on t.customs_code = cpygx.customs_code

		<include refid="page_where_mysql"/>


	</select>

	<!--勾选列表-->
	<select id="selectAuthPageList" databaseId="mysql"  parameterType="java.util.Map"
			resultMap="BaseResultMap">
		select
		t.customs_code,DATE_FORMAT(t.fill_in_date,'%Y-%m-%d') tfrq,t.tax_amount,t.lq_sfchsd,t.gf_tax_no,t.gf_name,t.lq_xfsyt,
		cpygx.gxclzt,
		cpygx.gxsbyy,
		DATE_FORMAT(cpygx.czsj,'%Y-%m-%d') czsj,
		cpygx.czr,
		cpygx.czr_name
		from t_dx_customs_cpy_gx cpygx left join t_dx_customs_record t on t.customs_code = cpygx.customs_code

		<include refid="page_where_mysql"/>

		order by cpygx.czsj desc
	</select>
	<select id="selectCountStatusPage" resultType="com.dxhy.api.model.db.TdxCustomsCpyGxEntity">
		select
		cpygx.gxclzt
		from t_dx_customs_cpy_gx cpygx left join t_dx_customs_record t on t.customs_code = cpygx.customs_code
		where
		t.company = #{company}
		<if test="gfsh!=null and gfsh!='' ">
			and t.gf_tax_no in
			<foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
		and cpygx.gxclzt in ('1','2','3')

	</select>

	<sql  id="page_where_mysql">
		<where>
			t.company = #{company}
			and t.gf_tax_no in
			<foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>

			<!--tabNum   未勾选0  勾选中1  已勾选2  勾选失败3 全部4-->
			<if test='tabNum != null and tabNum != "" and tabNum=="0" '>
				and cpygx.gxclzt = '0' and (t.lq_xfsyt is null or t.lq_xfsyt = '00')
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="1" '>
				and cpygx.gxclzt in ('1','2')
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="2" '>
				and cpygx.gxclzt = '4'
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="3" '>
				and cpygx.gxclzt = '3'
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="4" '>
				and cpygx.gxclzt in ('0','1','2','3','4')
			</if>

			<if test="customsCode != null and customsCode != '' and customsCode != 'null'">
				and t.customs_code = #{customsCode}
			</if>

			<if test="fillInStartDate!=null and fillInStartDate!='' ">
				and t.fill_in_date &gt;= #{fillInStartDate}
			</if>
			<if test="fillInEndDate!=null and fillInEndDate!='' ">
				and t.fill_in_date &lt;= #{fillInEndDate}
			</if>

			<if test="sfchsd != null and sfchsd != '' and sfchsd != 'null' ">
				and t.lq_sfchsd = #{sfchsd}
			</if>

		</where>
	</sql>


	<!--oracle 方面-->


	<!--成品油勾选页面合计-->
	<select id="selectAuthPageListHj" databaseId="oracle" resultType="com.dxhy.api.model.cpy.cpyCustom.TDxRecordCustomCpyVo" resultMap="BbfpzhcxhjResultMap" >
		select
		count(1) hjsl,
		ifnull(sum(t.tax_amount),0) hjse
		from t_dx_customs_cpy_gx cpygx left join t_dx_customs_record t on t.customs_code = cpygx.customs_code

		<include refid="page_where_oracle"/>
	</select>

	<!--勾选列表-->
	<select id="selectAuthPageList" databaseId="oracle"  parameterType="java.util.Map"
			resultMap="BaseResultMap">
		select
		t.customs_code,DATE_FORMAT(t.fill_in_date,'%Y-%m-%d') tfrq,t.tax_amount,t.lq_sfchsd,t.gf_tax_no,t.gf_name,t.lq_xfsyt,
		cpygx.gxclzt,
		cpygx.gxsbyy,
		DATE_FORMAT(cpygx.czsj,'%Y-%m-%d') czsj,
		cpygx.czr,
		cpygx.czr_name
		from t_dx_customs_cpy_gx cpygx left join t_dx_customs_record t on t.customs_code = cpygx.customs_code

		<include refid="page_where_oracle"/>

		order by cpygx.czsj desc
	</select>

	<sql  id="page_where_oracle">
		<where>
			t.company = #{company}
			and t.gf_tax_no in
			<foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>

			<!--tabNum   未勾选0  勾选中1  已勾选2  勾选失败3 全部4-->
			<if test='tabNum != null and tabNum != "" and tabNum=="0" '>
				and cpygx.gxclzt = '0' and (t.lq_xfsyt is null or t.lq_xfsyt = '00')
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="1" '>
				and cpygx.gxclzt in ('1','2')
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="2" '>
				and cpygx.gxclzt = '4'
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="3" '>
				and cpygx.gxclzt = '3'
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="4" '>
				and cpygx.gxclzt in ('0','1','2','3','4')
			</if>

			<if test="customsCode != null and customsCode != '' and customsCode != 'null'">
				and t.customs_code = #{customsCode}
			</if>

			<if test="fillInStartDate!=null and fillInStartDate!='' and fillInStartDate!=null and fillInStartDate!=''">
				and t.fill_in_date &gt;= #{fillInStartDate}
			</if>
			<if test="fillInEndDate!=null and fillInEndDate!='' and fillInEndDate!=null and fillInEndDate!=''">
				and t.fill_in_date &lt;= #{fillInEndDate}
			</if>

			<if test="sfchsd != null and sfchsd != '' and sfchsd != 'null' ">
				and t.lq_sfchsd = #{sfchsd}
			</if>

		</where>
	</sql>

</mapper>