<?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.ncpInvoice.dao.NcpJjkcCustomsDao">

	<resultMap id="BaseResultMap"
			   type="com.dxhy.api.model.rz.rzCustom.JkszhcxRespVO">
		<result column="customs_code" property="customsCode"/>
		<result column="fill_in_date" property="fillInDate"/>
		<result column="gf_tax_no" property="gfTaxNo"/>
		<result column="gf_name" property="gfName"/>
		<result column="tax_amount" property="taxAmount"/>
		<result column="yx_tax_amount" property="yxTaxAmount"/>
		<result column="audit_status" property="auditStatus"/>
		<result column="apply_check" property="applyCheck"/>
		<result column="check_status" property="checkStatus"/>
		<result column="qs_status" property="qsStatus"/>
		<result column="qs_date" property="qsDate"/>
		<result column="qs_type" property="qsType"/>
		<result column="qs_user" property="qsUser"/>
		<result column="rzh_date" property="rzhDate"/>
		<result column="rzh_yesorno" property="rzhYesorno"/>
		<result column="auth_status" property="authStatus"/>
		<result column="bdk_status" property="bdkStatus"/>
		<result column="confirm_user" property="confirmUser"/>
		<result column="rzh_belong_date" property="rzhBelongDate"/>
		<result column="glzt" property="glzt"/>
		<result column="customs_scan_id" property="customsScanId"/>
		<result column="businessName" property="businessName"/>

		<result column="ncpbfje" property="ncpbfje"/>
		<result column="kjjkcse" property="kjjkcse"/>
		<result column="sykjjkcse" property="sykjjkcse"/>


	</resultMap>
	<resultMap id="HjResultMap" type="java.util.Map">
		<result column="hjsl" property="hjsl" javaType="Long"/>
		<result column="hjse" property="hjse"/>
		<result column="sykjjkcse" property="sykjjkcse"/>
	</resultMap>

	<!--加计扣除结果页面对应-->
	<resultMap id="BaseJjkcResultMap"
			   type="com.dxhy.api.model.rz.rzCustom.JkszhcxRespVO">
		<result column="customs_code" property="customsCode"/>
		<result column="fill_in_date" property="fillInDate"/>
		<result column="gf_tax_no" property="gfTaxNo"/>
		<result column="gf_name" property="gfName"/>
		<result column="tax_amount" property="taxAmount"/>
		<result column="yx_tax_amount" property="yxTaxAmount"/>
		<result column="audit_status" property="auditStatus"/>
		<result column="apply_check" property="applyCheck"/>
		<result column="check_status" property="checkStatus"/>
		<result column="qs_status" property="qsStatus"/>
		<result column="qs_date" property="qsDate"/>
		<result column="qs_type" property="qsType"/>
		<result column="qs_user" property="qsUser"/>
		<result column="rzh_date" property="rzhDate"/>
		<result column="rzh_yesorno" property="rzhYesorno"/>
		<result column="auth_status" property="authStatus"/>
		<result column="bdk_status" property="bdkStatus"/>
		<result column="confirm_user" property="confirmUser"/>
		<result column="rzh_belong_date" property="rzhBelongDate"/>
		<result column="glzt" property="glzt"/>
		<result column="customs_scan_id" property="customsScanId"/>
		<result column="businessName" property="businessName"/>

		<result column="ncpbfje" property="ncpbfje"/>
		<result column="kjjkcse" property="kjjkcse"/>
		<result column="sykjjkcse" property="sykjjkcse"/>

		<result column="bcjjkcse" property="bcjjkcse"/>
		<result column="clzt" property="clzt"/>
		<result column="sqlx" property="sqlx"/>
		<result column="jjssq" property="jjssq"/>
		<result column="sbyy" property="sbyy"/>
		<result column="czsj" property="czsj"/>
		<result column="gxsj" property="gxsj"/>
		<result column="czr" property="czr"/>


	</resultMap>
	<resultMap id="HjJjkcResultMap" type="java.util.Map">
		<result column="hjsl" property="hjsl" javaType="Long"/>
		<result column="hjse" property="hjse"/>
		<result column="sykjjkcse" property="sykjjkcse"/>
		<result column="bcjjkcse" property="bcjjkcse"/>
	</resultMap>



	<select databaseId="mysql" id="selectByPramsMap"
			parameterType="java.util.Map" resultMap="BaseResultMap">
		select
		t.id,t.customs_code,DATE_FORMAT(t.fill_in_date,'%Y-%m-%d')
		fill_in_date,t.gf_tax_no,t.gf_name,t.tax_amount,t.yx_tax_amount,t.audit_status,t.apply_check,t.check_status,t.qs_status,
		DATE_FORMAT(t.qs_date,'%Y-%m-%d') qs_date,t.qs_type,t.qs_user,DATE_FORMAT(t.rzh_date,'%Y-%m-%d')
		rzh_date, t.rzh_yesorno,t.auth_status,t.bdk_status,t.confirm_user,t.rzh_belong_date,t.glzt,
		t.customs_scan_id,t.dept_name businessName,
		t.ncpbfje,t.kjjkcse,t.sykjjkcse

		from t_dx_customs_record t
		where export_mark = '0' and ywlx = '2' and sykjjkcse >0 and sfqsqbbjj = '0' and glzt = '0' and auth_status = 4
		and t.gf_tax_no in
		<foreach close=")" collection="gfsh" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>

		<if test="notInCustomsCodeList != null and notInCustomsCodeList.size() > 0">
			and t.customs_code not in
			<foreach collection="notInCustomsCodeList" index="index" item="item"
					 open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>

		<if test="customsCode != null and customsCode != '' and customsCode != 'null'">
			and t.customs_code = #{customsCode}
		</if>
		<if
				test="fillInStartDate != null and fillInEndDate != null and fillInStartDate != '' and fillInEndDate != ''">
			and t.fill_in_date between #{fillInStartDate} and #{fillInEndDate}
		</if>
		order by t.fill_in_date desc
	</select>
	<select databaseId="oracle" id="selectByPramsMap"
			parameterType="java.util.Map" resultMap="BaseResultMap">
		select
		t.id,t.customs_code,to_char(t.fill_in_date,'yyyy-mm-dd')
		fill_in_date,t.gf_tax_no,t.gf_name,t.tax_amount,t.yx_tax_amount,t.audit_status,t.apply_check,t.check_status,t.qs_status,
		to_char(t.qs_date,'yyyy-mm-dd') qs_date,t.qs_type,t.qs_user,to_char(t.rzh_date,'yyyy-mm-dd')
		rzh_date,
		t.rzh_yesorno,t.auth_status,t.bdk_status,t.confirm_user,t.rzh_belong_date,t.glzt,t.customs_scan_id,t.dept_name
		businessName,
		t.ncpbfje,t.kjjkcse,t.sykjjkcse
		from t_dx_customs_record t
		where export_mark = '0' and ywlx = '2' and sykjjkcse >0 and sfqsqbbjj = '0' and glzt = '0' and auth_status = 4
		 and t.gf_tax_no in
		<foreach close=")" collection="gfsh" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>

		<if test="notInCustomsCodeList != null and notInCustomsCodeList.size() > 0">
			and t.customs_code not in
			<foreach collection="notInCustomsCodeList" index="index" item="item"
					 open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>

		<if test="customsCode != null and customsCode != '' and customsCode != 'null'">
			and t.customs_code = #{customsCode}
		</if>
		<if
				test="fillInStartDate != null and fillInEndDate != null and fillInStartDate != '' and fillInEndDate != ''">
			and t.fill_in_date between to_date(#{fillInStartDate},'yyyy-mm-dd hh24:mi:ss') and
			to_date(#{fillInEndDate},'yyyy-mm-dd hh24:mi:ss')
		</if>
		order by t.fill_in_date desc
	</select>
	<select databaseId="mysql" id="selectHjxx"
			parameterType="java.util.Map" resultMap="HjResultMap">
		select
		count(1) hjsl,
		sum(t.tax_amount) hjse,
		sum(t.sykjjkcse) sykjjkcse
		from t_dx_customs_record t
		where export_mark = '0' and ywlx = '2' and sykjjkcse >0 and sfqsqbbjj = '0' and glzt = '0' and auth_status = 4
        and t.gf_tax_no in
		<foreach close=")" collection="gfsh" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>

		<if test="notInCustomsCodeList != null and notInCustomsCodeList.size() > 0">
			and t.customs_code not in
			<foreach collection="notInCustomsCodeList" index="index" item="item"
					 open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>

		<if test="customsCode != null and customsCode != '' and customsCode != 'null'">
			and t.customs_code = #{customsCode}
		</if>
		<if
				test="fillInStartDate != null and fillInEndDate != null and fillInStartDate != '' and fillInEndDate != ''">
			and t.fill_in_date between #{fillInStartDate} and #{fillInEndDate}
		</if>
	</select>
	<select databaseId="oracle" id="selectHjxx"
			parameterType="java.util.Map" resultMap="HjResultMap">
		select
		count(1) hjsl,
		sum(t.tax_amount) hjse,
		sum(t.sykjjkcse) sykjjkcse
		from t_dx_customs_record t
		where export_mark = '0' and ywlx = '2' and sykjjkcse >0 and sfqsqbbjj = '0'  and glzt = '0' and auth_status = 4
		 and t.gf_tax_no in
		<foreach close=")" collection="gfsh" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>
		<if test="customsCode != null and customsCode != '' and invoiceStatus != 'null'">
			and t.customs_code = #{customsCode}
		</if>

		<if test="notInCustomsCodeList != null and notInCustomsCodeList.size() > 0">
			and t.customs_code not in
			<foreach collection="notInCustomsCodeList" index="index" item="item"
					 open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>

		<if
				test="fillInStartDate != null and fillInEndDate != null and fillInStartDate != '' and fillInEndDate != ''">
			and t.fill_in_date between to_date(#{fillInStartDate},'yyyy-mm-dd hh24:mi:ss') and
			to_date(#{fillInEndDate},'yyyy-mm-dd hh24:mi:ss')
		</if>
	</select>


	<!--加计扣除结果页面合计和结果-->
	<select databaseId="mysql" id="selectResultHjxx" parameterType="java.util.Map" resultMap="HjJjkcResultMap">
		select
		count(1) hjsl,
		COALESCE(sum(t.tax_amount), 0) hjse,
		COALESCE(sum(t.sykjjkcse), 0) sykjjkcse,
		COALESCE(sum(jjkc.bcjjkcse), 0) bcjjkcse
		from t_dx_customs_ncp_jjkc jjkc left  join t_dx_customs_record t on jjkc.customs_code=t.customs_code
		where t.gf_tax_no in
		<foreach close=")" collection="gfsh" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>

		<if test="notInCustomsCodeList != null and notInCustomsCodeList.size() > 0">
			and t.customs_code not in
			<foreach collection="notInCustomsCodeList" index="index" item="item"
					 open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>

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

		<if test="clzt != null and clzt != '' and clzt != 'null'">
			and jjkc.clzt = #{clzt}
		</if>
		<if test="sqlx != null and sqlx != '' and sqlx != 'null'">
			and jjkc.sqlx = #{sqlx}
		</if>
		<if test="jjssq != null and jjssq != '' and jjssq != 'null'">
			and jjkc.jjssq = #{jjssq}
		</if>

		<if
				test="fillInStartDate != null and fillInEndDate != null and fillInStartDate != '' and fillInEndDate != ''">
			and t.fill_in_date between #{fillInStartDate} and #{fillInEndDate}
		</if>
	</select>


	<select databaseId="oracle" id="selectResultHjxx" parameterType="java.util.Map" resultMap="HjJjkcResultMap">
		select
		count(1) hjsl,
		COALESCE(sum(t.tax_amount), 0) hjse,
		COALESCE(sum(t.sykjjkcse), 0) sykjjkcse,
		COALESCE(sum(jjkc.bcjjkcse), 0) bcjjkcse
		from t_dx_customs_ncp_jjkc jjkc left  join t_dx_customs_record t on jjkc.customs_code=t.customs_code
		where t.gf_tax_no in
		<foreach close=")" collection="gfsh" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>

		<if test="notInCustomsCodeList != null and notInCustomsCodeList.size() > 0">
			and t.customs_code not in
			<foreach collection="notInCustomsCodeList" index="index" item="item"
					 open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>

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

		<if test="clzt != null and clzt != '' and clzt != 'null'">
			and jjkc.clzt = #{clzt}
		</if>
		<if test="sqlx != null and sqlx != '' and sqlx != 'null'">
			and jjkc.sqlx = #{sqlx}
		</if>
		<if test="jjssq != null and jjssq != '' and jjssq != 'null'">
			and jjkc.jjssq = #{jjssq}
		</if>

		<if
				test="fillInStartDate != null and fillInEndDate != null and fillInStartDate != '' and fillInEndDate != ''">
			and t.fill_in_date between to_date(#{fillInStartDate},'yyyy-mm-dd hh24:mi:ss') and to_date(#{fillInEndDate},'yyyy-mm-dd hh24:mi:ss')
		</if>
	</select>


	<select databaseId="mysql" id="selectResultByPramsMap" parameterType="java.util.Map" resultMap="BaseJjkcResultMap">
		select
		t.id,t.customs_code,DATE_FORMAT(t.fill_in_date,'%Y-%m-%d')
		fill_in_date,t.gf_tax_no,t.gf_name,t.tax_amount,t.yx_tax_amount,t.audit_status,t.apply_check,t.check_status,t.qs_status,
		DATE_FORMAT(t.qs_date,'%Y-%m-%d') qs_date,t.qs_type,t.qs_user,DATE_FORMAT(t.rzh_date,'%Y-%m-%d')
		rzh_date, t.rzh_yesorno,t.auth_status,t.bdk_status,t.confirm_user,t.rzh_belong_date,t.glzt,
		t.customs_scan_id,t.dept_name businessName,
		t.ncpbfje,t.kjjkcse,t.sykjjkcse,
		jjkc.bcjjkcse,jjkc.clzt,jjkc.sqlx,jjkc.jjssq,jjkc.sbyy,DATE_FORMAT(jjkc.czsj,'%Y-%m-%d') czsj,DATE_FORMAT(jjkc.gxsj,'%Y-%m-%d') gxsj,czr
		from t_dx_customs_ncp_jjkc jjkc left  join t_dx_customs_record t on jjkc.customs_code=t.customs_code
		where
		    t.gf_tax_no in
		<foreach close=")" collection="gfsh" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>

		<if test="notInCustomsCodeList != null and notInCustomsCodeList.size() > 0">
			and t.customs_code not in
			<foreach collection="notInCustomsCodeList" index="index" item="item"
					 open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>

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

		<if test="clzt != null and clzt != '' and clzt != 'null'">
			and jjkc.clzt = #{clzt}
		</if>
		<if test="sqlx != null and sqlx != '' and sqlx != 'null'">
			and jjkc.sqlx = #{sqlx}
		</if>
		<if test="jjssq != null and jjssq != '' and jjssq != 'null'">
			and jjkc.jjssq = #{jjssq}
		</if>

		<if
				test="fillInStartDate != null and fillInEndDate != null and fillInStartDate != '' and fillInEndDate != ''">
			and t.fill_in_date between #{fillInStartDate} and #{fillInEndDate}
		</if>

		<!--税款所属期-->
		<if test="skssq != null and skssq != '' and skssq != 'null' ">
			and t.rzh_belong_date = #{skssq}
		</if>
		<!--认证类型-->
		<if test="rzlx != null and rzlx != '' and rzlx != 'null' and rzlx != '99'">
			and t.rzlx = #{rzlx}
		</if>
		<!--认证状态-->
		<if test="authStatus != null and authStatus != '' and authStatus != 'null'  and authStatus != '99'">
			and t.auth_status = #{authStatus}
		</if>

		order by t.fill_in_date desc
	</select>


	<select databaseId="oracle" id="selectResultByPramsMap" parameterType="java.util.Map" resultMap="BaseJjkcResultMap">
		select
		t.id,t.customs_code,TO_CHAR(t.fill_in_date, 'yyyy-MM-dd')
		fill_in_date,t.gf_tax_no,t.gf_name,t.tax_amount,t.yx_tax_amount,t.audit_status,t.apply_check,t.check_status,t.qs_status,
		TO_CHAR(t.qs_date, 'yyyy-MM-dd') qs_date,t.qs_type,t.qs_user,TO_CHAR(t.rzh_date, 'yyyy-MM-dd')
		rzh_date, t.rzh_yesorno,t.auth_status,t.bdk_status,t.confirm_user,t.rzh_belong_date,t.glzt,
		t.customs_scan_id,t.dept_name businessName,
		t.ncpbfje,t.kjjkcse,t.sykjjkcse,
		jjkc.bcjjkcse,jjkc.clzt,jjkc.sqlx,jjkc.jjssq,jjkc.sbyy,TO_CHAR(jjkc.czsj, 'yyyy-MM-dd') czsj,TO_CHAR(jjkc.gxsj, 'yyyy-MM-dd') gxsj,czr
		from t_dx_customs_ncp_jjkc jjkc left  join t_dx_customs_record t on jjkc.customs_code=t.customs_code
		where
		t.gf_tax_no in
		<foreach close=")" collection="gfsh" index="index" item="item" open="(" separator=",">
			#{item}
		</foreach>

		<if test="notInCustomsCodeList != null and notInCustomsCodeList.size() > 0">
			and t.customs_code not in
			<foreach collection="notInCustomsCodeList" index="index" item="item"
					 open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>

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

		<if test="clzt != null and clzt != '' and clzt != 'null'">
			and jjkc.clzt = #{clzt}
		</if>
		<if test="sqlx != null and sqlx != '' and sqlx != 'null'">
			and jjkc.sqlx = #{sqlx}
		</if>
		<if test="jjssq != null and jjssq != '' and jjssq != 'null'">
			and jjkc.jjssq = #{jjssq}
		</if>

		<if
				test="fillInStartDate != null and fillInEndDate != null and fillInStartDate != '' and fillInEndDate != ''">
			and t.fill_in_date between to_date(#{fillInStartDate},'yyyy-mm-dd hh24:mi:ss') and to_date(#{fillInEndDate},'yyyy-mm-dd hh24:mi:ss')
		</if>

		<!--税款所属期-->
		<if test="skssq != null and skssq != '' and skssq != 'null' ">
			and t.rzh_belong_date = #{skssq}
		</if>
		<!--认证类型-->
		<if test="rzlx != null and rzlx != '' and rzlx != 'null' and rzlx != '99'">
			and t.rzlx = #{rzlx}
		</if>
		<!--认证状态-->
		<if test="authStatus != null and authStatus != '' and authStatus != 'null'  and authStatus != '99'">
			and t.auth_status = #{authStatus}
		</if>
		order by t.fill_in_date desc
	</select>
</mapper>
