开发中的sql（3）

最新推荐文章于 2023-06-19 16:45:08 发布
wanghang88
最新推荐文章于 2023-06-19 16:45:08 发布
阅读量539
点赞数
本文链接：https://blog.csdn.net/wanghang88/article/details/52239003
版权
开发中的sql参考，mapper.xml模板t_cbs_recon_billorder_cps表
像条件查询，条件修改以及对于date类型参数的处理。以及对于传入的参数是HashMap的条件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<mapper	namespace="com.bill99.cbs.recon.trans.base.technical.dao.IReconBillOrderCpsDao">

	<resultMap id="reconBillOrderCpsEo" type="com.bill99.cbs.recon.trans.base.technical.model.ReconBillOrderCpsEo">
		<result property="billorderId" column="billorder_id" />
		<result property="channelNo" column="channel_no" />
		<result property="bankNo" column="bank_no" />
		<result property="processId" column="process_id" />
		<result property="sysTransType" column="sys_trans_type" javaType="java.lang.Long" />
		<result property="orderAmount" column="order_amount"  javaType="java.lang.Long" />
		<result property="discRate" column="disc_rate" />
		<result property="feeAmount" column="fee_amount" javaType="java.lang.Long"/>
		<result property="settleAmount" column="settle_amount"  javaType="java.lang.Long" />
		<result property="payerAcct" column="payer_acct" />
		<result property="payeeAcct" column="payee_acct" />
		<result property="payerName" column="payer_name" />
		<result property="payeeName" column="payee_name" />
		<result property="orderDate" column="order_date" />
		<result property="orderTime" column="ORDER_TIME" />
		<result property="merchantCode" column="merchant_code" />
		<result property="reconDate" column="recon_date" />
		<result property="settleDate" column="settle_date" />
		<result property="cavDate" column="cav_date" />
		<result property="matchGroup" column="match_group" />
		<result property="authNetId" column="auth_net_id" />
		<result property="issueBankId" column="issue_bank_id" />
		<result property="idTxn" column="id_txn" />
		<result property="idTxnCtl" column="id_txn_ctl" />
		<result property="authRef" column="auth_ref" />
		<result property="authTraceNo" column="auth_trace_no" />
		<result property="authCode" column="auth_code" />
		<result property="authInvoiceNo" column="auth_invoice_no" />
		<result property="resultId" column="result_id" />
		<result property="cavId" column="cav_id" />
		<result property="timeStamp" column="time_stamp" />
		<result property="bankorderId" column="bankorder_id" />
		<result property="reconStatus" column="recon_status" />
		<result property="handleStatus" column="handle_status" />
		<result property="crtTime" column="crt_time" />
		<result property="updTime" column="upd_time" />
		<result property="crtUserId" column="crt_user_id" />
		<result property="updUserId" column="upd_user_id" />
		<result property="uploadId" column="upload_id" />
		<result property="reconId" column="RECON_ID" />
		<result property="fundReconStatus" column="FUND_RECON_STATUS" />
		<result property="peTid" column="PE_TID" />
		<result property="oraTid" column="ORA_TID" />
		<result property="costVariance" column="COST_VARIANCE" />
		<result property="discAmt" column="disc_amt" />
		<result property="feeRate" column="fee_rate" />
	</resultMap>

	<!-- 全部列名 -->
	<sql id="allColumns">
		billorder_id,
		channel_no,
		bank_no,
		process_id,
		sys_trans_type,
		order_amount,
		disc_rate,
		fee_amount,
		settle_amount,
		payer_acct,
		payee_acct,
		payer_name,
		payee_name,
		order_date,
		order_time,
		merchant_code,
		recon_date,
		settle_date,
		cav_date,
		match_group,
		auth_net_id,
		issue_bank_id,
		id_txn,
		id_txn_ctl,
		auth_ref,
		auth_trace_no,
		auth_code,
		auth_invoice_no,
		result_id,
		cav_id,
		time_stamp,
		bankorder_id,
		recon_status,
		handle_status,
		crt_time,
		upd_time,
		crt_user_id,
		upd_user_id,
		upload_id,
		RECON_ID,
		FUND_RECON_STATUS,
		PE_TID,
		ORA_TID,
		COST_VARIANCE,
		disc_amt,
		fee_rate
	</sql>

	
	<!-- 保存多条记录 -->
	<insert id="save" parameterType="com.bill99.cbs.recon.trans.base.technical.model.ReconBillOrderCpsEo">
	<selectKey resultType="long" order="BEFORE" keyProperty="billorderId">
	       SELECT RECONCAV.SEQ_T_CBS_RECON_BILLORDER_CPS.NEXTVAL as billorderId  from DUAL
	    </selectKey>
			INSERT INTO RECONCAV.T_CBS_RECON_BILLORDER_CPS (
			<include refid="allColumns" />
			) VALUES (
			#{billorderId},
			#{channelNo},
			#{bankNo},
			#{processId},
			#{sysTransType},
			#{orderAmount},
			#{discRate},
			#{feeAmount},
			#{settleAmount},
			#{payerAcct},
			#{payeeAcct},
			#{payerName},
			#{payeeName},
			#{orderDate},
			#{orderTime},
			#{merchantCode},
			#{reconDate},
			#{settleDate},
			#{cavDate},
			#{matchGroup},
			#{authNetId},
			#{issueBankId},
			#{idTxn},
			#{idTxnCtl},
			#{authRef},
			#{authTraceNo},
			#{authCode},
			#{authInvoiceNo},
			#{resultId},
			#{cavId},
			#{timeStamp},
			#{bankorderId},
			#{reconStatus},
			#{handleStatus},
			sysdate,
			sysdate,
			#{crtUserId},
			#{updUserId},
			#{uploadId},
			#{reconId},
			#{fundReconStatus},
			#{peTid},
			#{oraTid},
			#{costVariance},
			#{discAmt},
			#{feeRate}
			)
	</insert>
	
	<!-- 与数据拉取对应 -->
	<delete id="deleteByReconDate">
		delete from RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		<where>
			<if test="channelNo != null">channel_no = #{channelNo} </if>
			<if test="reconDate != null">and RECON_DATE =to_date(#{reconDate},'yyyy-mm-dd')</if>
			<if test="transType != null">and SYS_TRANS_TYPE = #{transType} </if>
		</where>	
	</delete>
	
	<!-- 与数据拉取对应 ,reconDate可能不一致，依据orderDate删除-->
	<delete id="deleteByOrderDate">
		delete from RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		<where>
			<if test="channelNo != null">channel_no = #{channelNo} </if>
			<if test="orderDate != null">and ORDER_DATE =to_date(#{orderDate},'yyyy-mm-dd')</if>
			<if test="transType != null">and SYS_TRANS_TYPE = #{transType} </if>
		</where>	
	</delete>	
	<select id="queryDistinctSettleDate" parameterType="com.bill99.cbs.recon.trans.base.domain.query.ReconBillOrderCpsForQuery" resultType="java.sql.Date">
		select distinct(SETTLE_DATE) from RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		<where>
			<if test="channelNo != null">and CHANNEL_NO = #{channelNo} </if>
			<if test="reconDate != null">and RECON_DATE =to_date(#{reconDate},'yyyy-mm-dd')</if>
			<if test="sysTransType != null">and SYS_TRANS_TYPE = #{sysTransType} </if>
		</where>
	</select>
	<select id="hasCount" resultType="java.lang.Integer">
		select count(1) from RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		<where>
			<if test="channelNo != null">and CHANNEL_NO = #{channelNo} </if>
			<if test="reconDate != null">and RECON_DATE =to_date(#{reconDate},'yyyy-mm-dd')</if>
		</where>
	</select>
	
	<select id="query" parameterType="com.bill99.cbs.recon.trans.base.domain.query.ReconBillOrderCpsForQuery" resultMap="reconBillOrderCpsEo">
		select <include refid="allColumns"/> from RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		<where>
			<if test="minAmount != null">and ORDER_AMOUNT >= #{minAmount} </if>
			<if test="maxAmount != null">and ORDER_AMOUNT < #{maxAmount} </if>
		    <if test="billorderId != null">and billorder_id = #{billorderId} </if>
			<if test="channelNo != null">and CHANNEL_NO = #{channelNo} </if>
			<if test="bankNo != null"> and BANK_NO = #{bankNo} </if>
			<if test="uploadId != null"> and UPLOAD_ID = #{uploadId} </if>
			<if test="reconStatus != null">and RECON_STATUS = #{reconStatus}</if>
			<if test="sysTransType != null"> and SYS_TRANS_TYPE = #{sysTransType} </if>
			<if test="reconDate != null"> and RECON_DATE = to_date(#{reconDate},'yyyy-mm-dd')</if>
			<if test="beginReconDate != null"> and RECON_DATE >= to_date(#{beginReconDate},'yyyy-mm-dd')</if>
			<if test="endReconDate != null"> and RECON_DATE <=to_date(#{endReconDate},'yyyy-mm-dd')</if>
			<if test="resultId != null">and RESULT_ID = #{resultId} </if>
			<if test="uploadId != null">and UPLOAD_ID = #{uploadId} </if>
			<if test="fundReconStatus != null">and FUND_RECON_STATUS = #{fundReconStatus} </if>
			<if test="peTid != null">and PE_TID = #{peTid} </if>
			<if test="oraTid != null">and ORA_TID = #{oraTid}</if>
			<if test="channelNos !=null and channelNos.size() !=0">
				and CHANNEL_NO in 
				<foreach collection="channelNos" item="itemChannel" separator="," open="(" close=")">
					#{itemChannel}
				</foreach> 
			</if>
			<if test="reconStatusList !=null and reconStatusList.size() !=0">
				and RECON_STATUS in 
				<foreach collection="reconStatusList" item="itemStatus" separator="," open="(" close=")">
					#{itemStatus}
				</foreach> 
			</if>
			<if test="sysTransTypeList !=null and sysTransTypeList.size() !=0">
				and SYS_TRANS_TYPE in 
				<foreach collection="sysTransTypeList" item="item" separator="," open="(" close=")">
					#{item}
				</foreach> 
			</if>
			<if test="idTxn != null"> and ID_TXN = #{idTxn}</if>
			<if test="beginCrtTime != null"> AND CRT_TIME >= #{beginCrtTime}</if>
			<if test="endCrtTime != null"> AND CRT_TIME <= #{endCrtTime}</if>
		</where>
		<if test="page!=null and page.orderField != null">ORDER BY ${page.orderField} </if>
		<if test="page!=null and page.orderDirection != null">${page.orderDirection}</if>
	</select>
	
	<select id="queryForDashBoard" parameterType="java.util.HashMap" resultMap="reconBillOrderCpsEo">
		select 
		SYS_TRANS_TYPE as SYS_TRANS_TYPE,
		sum(ORDER_AMOUNT) as ORDER_AMOUNT,
		sum(FEE_AMOUNT) as FEE_AMOUNT,
		sum(SETTLE_AMOUNT) as SETTLE_AMOUNT  
		from RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		<where>
			<if test="channelNo !=null">
				and CHANNEL_NO = #{channelNo}
			</if>
			<if test="reconDate !=null">
				and RECON_DATE = #{reconDate}
			</if>
		</where>
		group by SYS_TRANS_TYPE
	</select>
	
	<select id="getTotalOrderAmountForDashBoardByParam" parameterType="java.util.HashMap" resultType="java.lang.Long">
		SELECT
		SUM(ORDER_AMOUNT)
		FROM RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		WHERE
		RECON_DATE = TO_DATE(#{reconDate},'yyyy-mm-dd')
		AND SYS_TRANS_TYPE = #{sysTransType}
		AND CHANNEL_NO = #{channelNo}
	</select>
	
	<select id="getTotalFeeAmountForDashBoardByParam" parameterType="java.util.HashMap" resultType="java.lang.Long">
		SELECT
		SUM(FEE_AMOUNT)
		FROM RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		WHERE
		RECON_DATE = TO_DATE(#{reconDate},'yyyy-mm-dd')
		AND SYS_TRANS_TYPE = #{sysTransType}
		AND CHANNEL_NO = #{channelNo}
	</select>
	
	<select id="getTotalSettleAmountForDashBoardByParam" parameterType="java.util.HashMap" resultType="java.lang.Long">
		SELECT
		SUM(SETTLE_AMOUNT)
		FROM RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		WHERE
		RECON_DATE = TO_DATE(#{reconDate},'yyyy-mm-dd')
		AND SYS_TRANS_TYPE = #{sysTransType}
		AND CHANNEL_NO = #{channelNo}
	</select>
	
	<select id="queryRefundList" parameterType="com.bill99.cbs.recon.trans.base.domain.query.ReconBillOrderCpsForQuery" resultMap="reconBillOrderCpsEo">
		select <include refid="allColumns"/> from RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		<where>
			<if test="minAmount != null">and ORDER_AMOUNT >= #{minAmount} </if>
			<if test="maxAmount != null">and ORDER_AMOUNT < #{maxAmount} </if>
		    <if test="billorderId != null">and billorder_id = #{billorderId} </if>
			<if test="channelNo != null">and CHANNEL_NO = #{channelNo} </if>
			<if test="bankNo != null"> and BANK_NO = #{bankNo} </if>
			<if test="sysTransType != null"> and SYS_TRANS_TYPE = #{sysTransType} </if>
			<if test="reconDate != null"> RECON_DATE <=to_date(#{reconDate},'yyyy-mm-dd')</if>
			<if test="beginReconDate != null"> and RECON_DATE >= to_date(#{beginReconDate},'yyyy-mm-dd')</if>
			<if test="endReconDate != null"> and RECON_DATE <=to_date(#{endReconDate},'yyyy-mm-dd')</if>
			<if test="resultId != null">and RESULT_ID = #{resultId} </if>
			<if test="channelNos !=null and channelNos.size() !=0">
				and CHANNEL_NO in 
				<foreach collection="channelNos" item="itemChannel" separator="," open="(" close=")">
					#{itemChannel}
				</foreach> 
			</if>
			<if test="reconStatusList !=null and reconStatusList.size() !=0">
				and RECON_STATUS in 
				<foreach collection="reconStatusList" item="itemStatus" separator="," open="(" close=")">
					#{itemStatus}
				</foreach> 
			</if>
			<if test="idTxn != null"> and ID_TXN = #{idTxn}</if>
		</where>
		<if test="page!=null and page.orderField != null">ORDER BY ${page.orderField} </if>
		<if test="page!=null and page.orderDirection != null">${page.orderDirection}</if>
	</select>	
	
	<update id="update" parameterType="com.bill99.cbs.recon.trans.base.technical.model.ReconBillOrderCpsEo">
		UPDATE RECONCAV.T_CBS_RECON_BILLORDER_CPS
		<set>
			<if test="processId != null">process_id = #{processId},</if>
			<if test="sysTransType != null">sys_Trans_Type = #{sysTransType},</if>
			<if test="resultId != null">result_Id = #{resultId},</if>
			<if test="bankorderId != null">bankorder_Id = #{bankorderId},</if>
			<if test="reconStatus != null">recon_Status = #{reconStatus},</if>
			<if test="handleStatus != null">handle_Status = #{handleStatus},</if>
			<if test="updUserId != null">upd_user_id = #{updUserId},</if>
			<if test="uploadId != null">upload_id = #{uploadId},</if>	
			<if test="settleDate != null">SETTLE_DATE = #{settleDate},</if>
			<if test="reconId != null">RECON_ID = #{reconId},</if>
			<if test="fundReconStatus != null">FUND_RECON_STATUS = #{fundReconStatus},</if>
			recon_Date = RECON_DATE,
			upd_Time = sysdate
		</set>
		WHERE billorder_id = #{billorderId}
	</update>
	
	<select id="count" parameterType="com.bill99.cbs.recon.trans.base.domain.query.ReconBillOrderCpsForQuery" resultType="java.lang.Integer">
		select count(1) from RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		<where>
		<if test="minAmount != null">and ORDER_AMOUNT >= #{minAmount} </if>
			<if test="maxAmount != null">and ORDER_AMOUNT < #{maxAmount} </if>
		    <if test="billorderId != null">and billorder_id = #{billorderId} </if>
			<if test="channelNo != null">and CHANNEL_NO = #{channelNo} </if>
			<if test="bankNo != null"> and BANK_NO = #{bankNo} </if>
			<if test="reconStatus != null">and RECON_STATUS = #{reconStatus}</if>
			<if test="sysTransType != null"> and SYS_TRANS_TYPE = #{sysTransType} </if>
			<if test="reconDate != null"> and RECON_DATE = to_date(#{reconDate},'yyyy-mm-dd')</if>
			<if test="beginReconDate != null"> and RECON_DATE >= to_date(#{beginReconDate},'yyyy-mm-dd')</if>
			<if test="endReconDate != null"> and RECON_DATE <=to_date(#{endReconDate},'yyyy-mm-dd')</if>
			<if test="resultId != null">and RESULT_ID = #{resultId} </if>
			<if test="fundReconStatus != null">and FUND_RECON_STATUS = #{fundReconStatus}</if>
			<if test="peTid != null">and PE_TID = #{peTid} </if>
			<if test="oraTid != null">and ORA_TID = #{oraTid}</if>
			<if test="uploadId != null">and UPLOAD_ID = #{uploadId} </if>
			<if test="channelNos !=null and channelNos.size() !=0">
				and CHANNEL_NO in 
				<foreach collection="channelNos" item="itemChannel" separator="," open="(" close=")">
					#{itemChannel}
				</foreach> 
			</if>
			<if test="reconStatusList !=null and reconStatusList.size() !=0">
				and RECON_STATUS in 
				<foreach collection="reconStatusList" item="itemStatus" separator="," open="(" close=")">
					#{itemStatus}
				</foreach> 
			</if>
			<if test="sysTransTypeList !=null and sysTransTypeList.size() !=0">
				and SYS_TRANS_TYPE in 
				<foreach collection="sysTransTypeList" item="item" separator="," open="(" close=")">
					#{item}
				</foreach> 
			</if>
			<if test="idTxn != null"> and ID_TXN = #{idTxn}</if>
		</where>
	</select>
	
	<select id="countNum" parameterType="com.bill99.cbs.recon.trans.base.domain.query.ReconBillOrderCpsForQuery" resultType="java.lang.Long">
		SELECT COUNT(1) FROM RECONCAV.T_CBS_RECON_BILLORDER_CPS
		<where>
	 	    <if test="reconStatus != null">and RECON_STATUS = #{reconStatus}</if>
		 	<if test="resultId != null">and RESULT_ID = #{resultId} </if>
		    <if test="reconStatusList !=null and reconStatusList.size() !=0">
				and RECON_STATUS in 
				<foreach collection="reconStatusList" item="itemstatus" separator="," open="(" close=")">
					#{itemstatus}
				</foreach> 
			</if>
		</where>
	</select>
	
	<select id="countAmt" parameterType="com.bill99.cbs.recon.trans.base.domain.query.ReconBillOrderCpsForQuery" resultType="java.lang.Long">
		SELECT sum(ORDER_AMOUNT) FROM RECONCAV.T_CBS_RECON_BILLORDER_CPS
		<where>
	 	    <if test="reconStatus != null">and RECON_STATUS = #{reconStatus}</if>
		 	<if test="resultId != null">and RESULT_ID = #{resultId} </if>
		    <if test="reconStatusList !=null and reconStatusList.size() !=0">
				and RECON_STATUS in 
				<foreach collection="reconStatusList" item="itemstatus" separator="," open="(" close=")">
					#{itemstatus}
				</foreach> 
			</if>
		</where>
	</select>		
	
	<select id="statisticsQueryAll" parameterType="com.bill99.cbs.recon.trans.base.domain.query.ReconBillOrderCpsForQuery"
		resultType="com.bill99.cbs.recon.trans.base.technical.model.ReconBillOrderCpsEo">
		select sum(sumAmount) as sumAmount,sum(sumNum) as sumNum from(
	   select sum(order_amount) as sumAmount, count(1) as sumNum
	     	from RECONCAV.T_CBS_RECON_BILLORDER_CPS
	   <where> 
	   <if test="sysTransType != null"> and SYS_TRANS_TYPE = #{sysTransType} </if>
		<if test="resultId != null"> and RESULT_ID = #{resultId} </if>
		</where>	
	union all 
	   select sum(order_amount) as sumAmount, count(1) as sumNum
	     	from RECONCAV.T_CBS_RECON_BILLERROR_CPS
	   <where> 
	   <if test="sysTransType != null"> and SYS_TRANS_TYPE = #{sysTransType} </if>
		<if test="resultId != null"> and RESULT_ID = #{resultId} </if>
		</where>		
		)
	</select>
	
	<select id="billOrderTotalUnMatch"
		parameterType="com.bill99.cbs.recon.trans.base.domain.query.ReconBillOrderCpsForQuery"
		resultType="com.bill99.cbs.recon.trans.base.technical.model.ReconBillOrderCpsEo">
		select sum(order_amount) as sumNum, count(1) as sumAmount from(select
		billorder_id,order_amount
		from RECONCAV.T_CBS_RECON_BILLORDER_CPS
		<where>
		    SETTLE_DATE > RECON_DATE
			<if test="channelNo !=null">and CHANNEL_NO = #{channelNo}</if>
			<if test="reconDate !=null">and RECON_DATE <= to_date(#{reconDate},'yyyy-MM-dd')</if>
			<if test="sysTransType != null">and SYS_TRANS_TYPE=#{sysTransType}</if>
		</where>
		union
		select billorder_id,order_amount
		from RECONCAV.T_CBS_RECON_BILLORDER_CPS
		<where>
			<if test="channelNo !=null">CHANNEL_NO = #{channelNo}</if>
			<if test="reconDate !=null">and RECON_DATE <= to_date(#{reconDate},'yyyy-MM-dd')</if>
			<if test="sysTransType != null">and SYS_TRANS_TYPE=#{sysTransType}</if>
			and recon_status=#{reconStatus}
		</where>
		)
	</select>
	<select id="billOrderUnMatchQueryAll" parameterType="com.bill99.cbs.recon.trans.base.domain.query.ReconBillOrderCpsForQuery" resultMap="reconBillOrderCpsEo">
		select
		t.RECON_DATE,  
		t.BANK_NO,
		t.CHANNEL_NO,
		t.ORDER_AMOUNT,
		t.SYS_TRANS_TYPE,
		t.PAYER_ACCT,
		t.PAYEE_ACCT,
		t.MERCHANT_CODE,
		t.MATCH_GROUP,
		t.RECON_STATUS
		from RECONCAV.T_CBS_RECON_BILLORDER_CPS t
		<where>
		    t.SETTLE_DATE > t.RECON_DATE
			<if test="channelNo !=null">and t.CHANNEL_NO = #{channelNo}</if>
			<if test="reconDate !=null">and t.RECON_DATE <= to_date(#{reconDate},'yyyy-MM-dd')</if>
			<if test="sysTransType != null">and t.SYS_TRANS_TYPE = #{sysTransType}</if>
		</where>
		union
		select
		t.RECON_DATE,  
		t.BANK_NO,
		t.CHANNEL_NO,
		t.ORDER_AMOUNT,
		t.SYS_TRANS_TYPE,
		t.PAYER_ACCT,
		t.PAYEE_ACCT,
		t.MERCHANT_CODE,
		t.MATCH_GROUP,
		t.RECON_STATUS
		from RECONCAV.T_CBS_RECON_BILLORDER_CPS t
		<where>
			<if test="channelNo !=null"> t.CHANNEL_NO = #{channelNo}</if>
			<if test="reconDate !=null">and t.RECON_DATE <= TO_DATE(#{reconDate} , 'yyyy-MM-dd')</if>
			<if test="sysTransType != null">and t.SYS_TRANS_TYPE= #{sysTransType}</if>
			and t.recon_status=#{reconStatus}
		</where>
	</select>
	
	<select id="statisticsQuery" parameterType="com.bill99.cbs.recon.trans.base.domain.query.ReconBillOrderCpsForQuery"
		resultType="com.bill99.cbs.recon.trans.base.technical.model.ReconBillOrderCpsEo">
	   select sum(order_amount) as "sumAmount", count(1) as "sumNum"
	     	from RECONCAV.T_CBS_RECON_BILLORDER_CPS
	   <where> 
	   <if test="reconStatus != null">and RECON_STATUS = #{reconStatus} </if>
	   <if test="sysTransType != null"> and SYS_TRANS_TYPE = #{sysTransType} </if>
		<if test="resultId != null"> and RESULT_ID = #{resultId} </if>
		</where>	
	</select>

	<delete id="deleteByBillOrderIds" >
		delete from RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		<where>
			<if test="billOrderIds !=null and billOrderIds.size() !=0">
				BILLORDER_ID in 
				<foreach collection="billOrderIds" item="item" separator="," open="(" close=")">
					#{item}
				</foreach> 
			</if>		
		</where>
	</delete>			

	<delete id="deleteById" parameterType="java.lang.Long">
		delete from RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		where billorder_id = #{id}
	</delete>	

	<delete id="deleteByResultId" parameterType="java.lang.Long">
		delete from RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		where RESULT_ID = #{resultId} 
	</delete>		
	

	<delete id="batchDeleteByObject"  parameterType="com.bill99.cbs.recon.trans.base.technical.model.ReconBillOrderCpsEo">
		delete from RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		<where>
			<if test="channelNo != null">and CHANNEL_NO = #{channelNo}</if>
			<if test="bankNo != null">and BANK_NO = #{bankNo}</if>
			<if test="processId != null">and BILLORDER_ID = #{processId}</if>
			<if test="sysTransType != null">and SYS_TRANS_TYPE = #{sysTransType}</if>
			<if test="orderAmount != null">and ORDER_AMOUNT = #{orderAmount}</if>
			<if test="discRate != null">and DISC_RATE = #{discRate}</if>
			<if test="feeAmount != null">and FEE_AMOUNT = #{feeAmount}</if>
			<if test="settleAmount != null">and SETTLE_AMOUNT = #{settleAmount}</if>
			<if test="payerAcct != null">and PAYER_ACCT = #{payerAcct}</if>
			<if test="payeeAcct != null">and PAYEE_ACCT = #{payeeAcct}</if>
			<if test="payerName != null">and PAYER_NAME = #{payerName}</if>
			<if test="payeeName != null">and PAYEE_NAME = #{payeeName}</if>
			<if test="merchantCode != null">and MERCHANT_CODE = #{merchantCode}</if>
			<if test="matchGroup != null">and MATCH_GROUP = #{matchGroup}</if>
			<if test="authNetId != null">and AUTH_NET_ID = #{authNetId}</if>
			<if test="idTxn != null">and ID_TXN = #{idTxn}</if>
			<if test="idTxnCtl != null">and ID_TXN_CTL = #{idTxnCtl}</if>
			<if test="authRef != null">and AUTH_REF = #{authRef}</if>
			<if test="authTraceNo != null">and AUTH_TRACE_NO = #{authTraceNo}</if>
			<if test="authCode != null">and AUTH_CODE = #{authCode}</if>		
			<if test="authInvoiceNo != null">and AUTH_INVOICE_NO = #{authInvoiceNo}</if>		
		</where>
	</delete>	
	
	
	<update id="updateToOrigin" parameterType="java.lang.Long">
		UPDATE RECONCAV.T_CBS_RECON_BILLORDER_CPS set bankorder_id='',recon_status=0
		    WHERE result_id = #{resultId}
	</update>
	
	<update id="updateToOriginByResultAndUploadId"  parameterType="com.bill99.cbs.recon.trans.base.domain.query.ReconBillOrderCpsForQuery">
		UPDATE RECONCAV.T_CBS_RECON_BILLORDER_CPS set bankorder_id='',upload_id='',recon_status=0
		    <where>
			<if test="resultId != null">and result_id = #{resultId}</if>
			<if test="uploadId != null">and upload_id = #{uploadId}</if>		
		</where>
	</update>
	
	
		
	<!-- 保存 -->
	<insert id="saveWithId" parameterType="com.bill99.cbs.recon.trans.base.technical.model.ReconBillOrderCpsEo">
			INSERT INTO RECONCAV.T_CBS_RECON_BILLORDER_CPS (
			<include refid="allColumns" />
			) VALUES (
			#{billorderId},
			#{channelNo},
			#{bankNo},
			#{processId},
			#{sysTransType},
			#{orderAmount},
			#{discRate},
			#{feeAmount},
			#{settleAmount},
			#{payerAcct},
			#{payeeAcct},
			#{payerName},
			#{payeeName},
			#{orderDate},
			#{orderTime},
			#{merchantCode},
			#{reconDate},
			#{settleDate},
			#{cavDate},
			#{matchGroup},
			#{authNetId},
			#{issueBankId},
			#{idTxn},
			#{idTxnCtl},
			#{authRef},
			#{authTraceNo},
			#{authCode},
			#{authInvoiceNo},
			#{resultId},
			#{cavId},
			#{timeStamp},
			#{bankorderId},
			#{reconStatus},
			#{handleStatus},
			sysdate,
			sysdate,
			#{crtUserId},
			#{updUserId},
			#{uploadId},
			#{reconId},
			#{fundReconStatus},
			#{discAmt},
			#{feeRate}
			)
	</insert>
	
	
	
	
	<!-- 修改资金对账状态，对账编号，分区表，加上RECON_DATE的条件  -->
	<update id="updateByParam" parameterType="com.bill99.cbs.recon.trans.base.domain.query.ReconBillOrderCpsForQuery" >
		UPDATE RECONCAV.T_CBS_RECON_BILLORDER_CPS
		<set>
			<if test="reconId != null"> RECON_ID = #{reconId},</if>
			<if test="fundReconStatus != null"> FUND_RECON_STATUS = #{fundReconStatus},</if>
			UPD_TIME = SYSDATE
		</set>
	<where>
		<if test="channelNo != null">and CHANNEL_NO = #{channelNo} </if>
	 	<if test="settleDate != null"> and SETTLE_DATE = #{settleDate}     </if>
	 	<if test="beginSettleDate != null"> and SETTLE_DATE >= #{beginSettleDate}  </if>
	    <if test="endSettleDate != null"> and SETTLE_DATE <= #{endSettleDate}  </if>
	    <if test="beginReconDate2 != null">  and  RECON_DATE >= #{beginReconDate2}</if>
	    <if test="endReconDate2 != null">   and RECON_DATE <= #{endReconDate2} </if>
	   	<if test="sysTransTypeList !=null and sysTransTypeList.size() !=0">
				 and SYS_TRANS_TYPE in 
				<foreach collection="sysTransTypeList" item="item" separator="," open="(" close=")">
					#{item}
				</foreach> 
			</if>	
			<if test="reconStatus != null">and RECON_STATUS = #{reconStatus}</if>
			<if test="reconId != null and  fundReconStatus != null">and   (RECON_ID is null  or  FUND_RECON_STATUS is null or FUND_RECON_STATUS  <>  #{fundReconStatus})</if>
	 </where>
	</update>
	
	<select id="getTotalAmountByParam" parameterType="java.util.HashMap" resultType="java.lang.Long">
		SELECT
		SUM(ORDER_AMOUNT)
		FROM
		RECONCAV.T_CBS_RECON_BILLORDER_CPS
		WHERE
		RECON_DATE = to_date(#{reconDate},'yyyy-mm-dd')
		AND CHANNEL_NO = #{channelNo}
		AND SYS_TRANS_TYPE = #{sysTransType}
	</select>
	
	<select id="getTotalFeeByParam" parameterType="java.util.HashMap" resultType="java.lang.Long">
		SELECT
		SUM(DISC_AMT)
		FROM
		RECONCAV.T_CBS_RECON_BILLORDER_CPS
		WHERE
		RECON_DATE = to_date(#{reconDate},'yyyy-mm-dd')
		AND CHANNEL_NO = #{channelNo}
		AND SYS_TRANS_TYPE = #{sysTransType}
	</select> 
	
	<select id="queryFirstData" parameterType="java.util.HashMap" resultMap="reconBillOrderCpsEo">
		select <include refid="allColumns"/> from RECONCAV.T_CBS_RECON_BILLORDER_CPS 
		WHERE
		RECON_DATE = to_date(#{reconDate},'yyyy-mm-dd')
		AND CHANNEL_NO = #{channelNo}
		AND rownum = 1
	</select> 
	
	<select id="getTotalAmountAndFeeByParam" parameterType="java.util.HashMap" resultMap="reconBillOrderCpsEo">
		SELECT 
			RECON_ID,
			SUM(ORDER_AMOUNT) AS ORDER_AMOUNT,
			SUM(FEE_AMOUNT) AS FEE_AMOUNT
		FROM
			T_CBS_RECON_BILLORDER_CPS
		WHERE
			recon_date between to_date(#{reconDate}, 'yyyy-mm-dd') -2 
							and to_date(#{reconDate}, 'yyyy-mm-dd') +2
       		and SETTLE_DATE=to_date(#{reconDate},'yyyy-mm-dd')
			AND CHANNEL_NO=#{channelNo}
			AND SYS_TRANS_TYPE=#{sysTransType}
			AND RECON_STATUS=#{reconStatus}
			AND FUND_RECON_STATUS=#{fundReconStatus}
			AND PE_TID IS NULL
		GROUP BY recon_id
   	</select>
   	
   	<update id="updatePeTid" parameterType="java.util.HashMap">
		UPDATE RECONCAV.T_CBS_RECON_BILLORDER_CPS
		SET PE_TID=#{peTid} , UPD_TIME = sysdate
		WHERE
		recon_date between to_date(#{reconDate}, 'yyyy-mm-dd') -2 
							and to_date(#{reconDate}, 'yyyy-mm-dd') +2
		and SETTLE_DATE=to_date(#{reconDate},'yyyy-mm-dd')
		AND PE_TID IS NULL
		AND CHANNEL_NO=#{channelNo}
		AND SYS_TRANS_TYPE=#{sysTransType}
		AND RECON_STATUS=#{reconStatus}
		AND FUND_RECON_STATUS=#{fundReconStatus}
	</update>
	
	<select id="getTotalAmountAndFeeLessReconDate" parameterType="java.util.HashMap" resultMap="reconBillOrderCpsEo">
		SELECT
		SUM(ORDER_AMOUNT) AS ORDER_AMOUNT,
		SUM(DISC_AMT) AS DISC_AMT
		FROM
		RECONCAV.T_CBS_RECON_BILLORDER_CPS
		WHERE
		RECON_DATE < to_date(#{reconDate},'yyyy-mm-dd')
		AND CHANNEL_NO=#{channelNo}
		AND SYS_TRANS_TYPE=#{sysTransType}
		AND RECON_STATUS=#{reconStatus}
   	</select>
   	
   	<select id="getTotalAmountAndFeeLessEqualReconDate" parameterType="java.util.HashMap" resultMap="reconBillOrderCpsEo">
		SELECT
		SUM(ORDER_AMOUNT) AS ORDER_AMOUNT,
		SUM(DISC_AMT) AS DISC_AMT
		FROM
		RECONCAV.T_CBS_RECON_BILLORDER_CPS
		WHERE
		RECON_DATE <= to_date(#{reconDate},'yyyy-mm-dd')
		AND CHANNEL_NO=#{channelNo}
		AND SYS_TRANS_TYPE=#{sysTransType}
		AND RECON_STATUS=#{reconStatus}
   	</select>
   	
   	<select id="getTotalAmountAndFeeMoreSettleDate" parameterType="java.util.HashMap" resultMap="reconBillOrderCpsEo">
		SELECT
		SUM(ORDER_AMOUNT) AS ORDER_AMOUNT,
		SUM(DISC_AMT) AS DISC_AMT
		FROM
		RECONCAV.T_CBS_RECON_BILLORDER_CPS
		WHERE
		SETTLE_DATE > to_date(#{settleDate},'yyyy-mm-dd')
		AND CHANNEL_NO=#{channelNo}
		AND SYS_TRANS_TYPE=#{sysTransType}
		AND RECON_STATUS=#{reconStatus}
   	</select>
   	
   	<select id="getTotalAmountAndFeeMoreEqualSettleDate" parameterType="java.util.HashMap" resultMap="reconBillOrderCpsEo">
		SELECT
		SUM(ORDER_AMOUNT) AS ORDER_AMOUNT,
		SUM(DISC_AMT) AS DISC_AMT
		FROM
		RECONCAV.T_CBS_RECON_BILLORDER_CPS
		WHERE
		SETTLE_DATE >= to_date(#{settleDate},'yyyy-mm-dd')
		AND CHANNEL_NO=#{channelNo}
		AND SYS_TRANS_TYPE=#{sysTransType}
		AND RECON_STATUS=#{reconStatus}
   	</select>
</mapper>