" ?>
-mapper.dtd" >
<mapper namespace="com.j1.soa.resource.order.oracle.dao.TpInfoMapper">
<resultMap id="TpInfoResultMap" type="TpInfo">
<result column="ID" jdbcType="DECIMAL" property="id" />
<result column="AVAILABLE_BALANCE" jdbcType="DECIMAL" property="availableBalance" />
</resultMap>
<resultMap id="TpPolicyResultMap" type="TpInfo">
<result column="END_DATE" jdbcType="VARCHAR" property="dueDate" />
<result column="BALANCE" jdbcType="VARCHAR" property="dueAmount" />
</resultMap>
<resultMap id="TpConsumeRecordMap" type="TpConsumeRecord">
<result column="POLICY_NO" jdbcType="VARCHAR" property="policyNo" />
<result column="BUSINESS_ID" jdbcType="VARCHAR" property="businessId" />
<result column="AMOUNT" jdbcType="VARCHAR" property="amount" />
<result column="ADD_TIME" jdbcType="VARCHAR" property="amountTime" />
<result column="OPERATION" jdbcType="VARCHAR" property="amountStatus" />
</resultMap>
<resultMap id="AmountSumMap" type="TpConsumeRecord">
<result column="AMOUNT" jdbcType="VARCHAR" property="amount" />
<result column="OPERATION" jdbcType="VARCHAR" property="amountStatus" />
</resultMap>
<sql id="be_fy">
select *
from (select row_.*, rownum rownum_
from (
</sql>
<!-- 用于分页的尾部 -->
<sql id="ed_fy">
) row_
<![CDATA[
and rownum <= #{offset}+#{pageSize}
]]>
)
<![CDATA[
and rownum_ > #{offset}
]]>
</sql>
<select id="checkIsTp" parameterType="java.lang.Long" resultType="java.lang.Integer">
select count(*)
from fec.tp_account tpa
where tpa.member_id = #{memberId,jdbcType=DECIMAL}
and tpa.is_delete = 'N'
</select>
<select id="getTpInfo" parameterType="java.lang.Long" resultMap="TpInfoResultMap">
select tpp.tp_account_id as id, sum(tpp.balance) as available_balance
from fec.tp_account tpa, fec.tp_policy tpp
where tpa.member_id = #{memberId,jdbcType=DECIMAL}
and tpa.id = tpp.tp_account_id
and tpa.is_delete = 'N'
group by tpp.tp_account_id
</select>
<select id="getLimitPolicy" parameterType="java.lang.Long" resultMap="TpPolicyResultMap">
select tpp.balance,tpp.end_date
from fec.tp_policy tpp
where tpp.tp_account_id = #{tpAccountId,jdbcType=DECIMAL}
<![CDATA[ and TO_DATE(tpp.end_date, 'yyyy-mm-dd') <=
TO_DATE(TO_CHAR(sysdate + interval '2' month, 'yyyy-mm-dd'),
'yyyy-mm-dd')
and TO_DATE(tpp.start_date, 'yyyy-mm-dd') <= TO_DATE(TO_CHAR(sysdate,'yyyy-mm-dd'), 'yyyy-mm-dd')
and TO_DATE(tpp.end_date, 'yyyy-mm-dd') >=TO_DATE(TO_CHAR(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd')
and tpp.is_delete = 'N'
]]>
order by tpp.end_date
</select>
<select id="getTpConsumeRecordList" parameterType="Pageable" resultMap="TpConsumeRecordMap">
<include refid="be_fy"/>
select tpo.policy_no,
tpo.business_id,
sum(tpo.amount) || '' as amount,
tpo.add_time,
tpo.OPERATION
from fec.tp_policy_operation_log tpo,fec.tp_account tpa,fec.tp_policy tpp
where tpa.id = tpp.tp_account_id
and tpa.is_delete = 'N'
and tpp.policy_no = tpo.policy_no
and tpp.is_delete = 'N'
and tpa.member_id = #{params.memberId}
<if test="params.beginDate != null">
<![CDATA[
and to_date(tpo.add_time,'yyyy-mm-dd hh24:mi:ss') >= TO_DATE(#{params.beginDate},'yyyy-mm-dd hh24:mi:ss')
]]>
</if>
<if test="params.endDate != null">
<![CDATA[
and to_date(tpo.add_time,'yyyy-mm-dd hh24:mi:ss') <= TO_DATE(#{params.endDate},'yyyy-mm-dd hh24:mi:ss')
]]>
</if>
group by tpo.policy_no, tpo.business_id, tpo.add_time, tpo.OPERATION
order by tpo.add_time desc
<include refid="ed_fy"/>
</select>
<select id="getTpConsumeRecordListCount" parameterType="TpConsumeRecord" resultType="Integer">
)
from
(select tpo.policy_no,
tpo.business_id,
sum(tpo.amount) || '' as amount,
tpo.add_time,
tpo.OPERATION
from fec.tp_policy_operation_log tpo,fec.tp_account tpa,fec.tp_policy tpp
where tpa.id = tpp.tp_account_id
and tpa.is_delete = 'N'
and tpp.policy_no = tpo.policy_no
and tpp.is_delete = 'N'
and tpa.member_id = #{memberId}
<if test="beginDate != null">
<![CDATA[
and to_date(tpo.add_time,'yyyy-mm-dd hh24:mi:ss') >= TO_DATE(#{beginDate},'yyyy-mm-dd hh24:mi:ss')
]]>
</if>
<if test="endDate != null">
<![CDATA[
and to_date(tpo.add_time,'yyyy-mm-dd hh24:mi:ss') <= TO_DATE(#{endDate},'yyyy-mm-dd hh24:mi:ss')
]]>
</if>
group by tpo.policy_no, tpo.business_id, tpo.add_time, tpo.OPERATION
order by tpo.add_time desc)
</select>
<select id="getAmountSumByOperation" parameterType="TpConsumeRecord" resultMap="AmountSumMap">
select sum(tpo.amount) || '' as amount, tpo.OPERATION
from fec.tp_policy_operation_log tpo,
fec.tp_account tpa,
fec.tp_policy tpp
where tpa.id = tpp.tp_account_id
and tpa.is_delete = 'N'
and tpp.policy_no = tpo.policy_no
and tpp.is_delete = 'N'
and tpa.member_id = #{memberId}
<if test="beginDate != null">
<![CDATA[
and to_date(tpo.add_time,'yyyy-mm-dd hh24:mi:ss') >= TO_DATE(#{beginDate},'yyyy-mm-dd hh24:mi:ss')
]]>
</if>
<if test="endDate != null">
<![CDATA[
and to_date(tpo.add_time,'yyyy-mm-dd hh24:mi:ss') <= TO_DATE(#{endDate},'yyyy-mm-dd hh24:mi:ss')
]]>
</if>
group by tpo.OPERATION
</select>
<select id="getTpExceptionInfo" parameterType="Pageable" resultMap="TpConsumeRecordMap">
<include refid="be_fy"/>
'">
select tpo.policy_no,
tpo.business_id,
sum(tpo.amount) || '' as amount,
tpo.add_time,
tpo.OPERATION
from fec.tp_policy_operation_log tpo,
fec.tp_account tpa,
fec.tp_policy tpp
where tpa.id = tpp.tp_account_id
and tpa.is_delete = 'N'
and tpp.policy_no = tpo.policy_no
and tpp.is_delete = 'N'
and tpa.member_id = #{params.memberId}
and tpo.operation in (${params.amountStatus})
group by tpo.policy_no, tpo.business_id, tpo.add_time, tpo.OPERATION
</if>
<if test="params.amountStatus == 'all'">
select tpo.policy_no,
tpo.business_id,
sum(tpo.amount) || '' as amount,
tpo.add_time,
tpo.OPERATION
from fec.tp_policy_operation_log tpo,
fec.tp_account tpa,
fec.tp_policy tpp
where tpa.id = tpp.tp_account_id
and tpa.is_delete = 'N'
and tpp.policy_no = tpo.policy_no
and tpp.is_delete = 'N'
and tpa.member_id = #{params.memberId}
')
group by tpo.policy_no, tpo.business_id, tpo.add_time, tpo.OPERATION
union all
select tpp.policy_no,
'invalid' as business_id,
tpp.balance || '',
tpp.end_date as add_time,
' as OPERATION
from fec.tp_account tpa, fec.tp_policy tpp
where tpa.id = tpp.tp_account_id
and tpa.is_delete = 'N'
and tpp.is_delete = 'N'
and tpa.member_id = #{params.memberId}
<![CDATA[
and TO_DATE(tpp.end_date || '23:59:59', 'yyyy-mm-dd hh24:mi:ss') <
to_date(to_char(sysdate, 'yyyy-mm-dd') || ' 23:59:59',
'yyyy-mm-dd hh24:mi:ss')
]]>
</if>
'">
select tpo.policy_no,
tpo.business_id,
sum(tpo.amount) || '' as amount,
tpo.add_time,
tpo.OPERATION
from fec.tp_policy_operation_log tpo,
fec.tp_account tpa,
fec.tp_policy tpp
where tpa.id = tpp.tp_account_id
and tpa.is_delete = 'N'
and tpp.policy_no = tpo.policy_no
and tpp.is_delete = 'N'
and tpa.member_id = #{params.memberId}
')
group by tpo.policy_no, tpo.business_id, tpo.add_time, tpo.OPERATION
UNION ALL
select tpp.policy_no,
'invalid' as business_id,
tpp.balance || '',
tpp.end_date as add_time,
' as OPERATION
from fec.tp_account tpa, fec.tp_policy tpp
where tpa.id = tpp.tp_account_id
and tpa.is_delete = 'N'
and tpp.is_delete = 'N'
and tpa.member_id = #{params.memberId}
<![CDATA[
and TO_DATE(tpp.end_date || '23:59:59', 'yyyy-mm-dd hh24:mi:ss') <
to_date(to_char(sysdate, 'yyyy-mm-dd') || ' 23:59:59',
'yyyy-mm-dd hh24:mi:ss')
]]>
</if>
order by add_time desc
<include refid="ed_fy"/>
</select>
<select id="getTpExceptionInfoCount" parameterType="TpConsumeRecord" resultType="Integer">
)
from
(
'">
select tpo.policy_no,
tpo.business_id,
sum(tpo.amount) || '' as amount,
tpo.add_time,
tpo.OPERATION
from fec.tp_policy_operation_log tpo,
fec.tp_account tpa,
fec.tp_policy tpp
where tpa.id = tpp.tp_account_id
and tpa.is_delete = 'N'
and tpp.policy_no = tpo.policy_no
and tpp.is_delete = 'N'
and tpa.member_id = #{memberId}
and tpo.operation in (${amountStatus})
group by tpo.policy_no, tpo.business_id, tpo.add_time, tpo.OPERATION
</if>
<if test="amountStatus == 'all'">
select tpo.policy_no,
tpo.business_id,
sum(tpo.amount) || '' as amount,
tpo.add_time,
tpo.OPERATION
from fec.tp_policy_operation_log tpo,
fec.tp_account tpa,
fec.tp_policy tpp
where tpa.id = tpp.tp_account_id
and tpa.is_delete = 'N'
and tpp.policy_no = tpo.policy_no
and tpp.is_delete = 'N'
and tpa.member_id = #{memberId}
')
group by tpo.policy_no, tpo.business_id, tpo.add_time, tpo.OPERATION
union all
select tpp.policy_no,
'invalid' as business_id,
tpp.balance || '',
tpp.end_date as add_time,
' as OPERATION
from fec.tp_account tpa, fec.tp_policy tpp
where tpa.id = tpp.tp_account_id
and tpa.is_delete = 'N'
and tpp.is_delete = 'N'
and tpa.member_id = #{memberId}
<![CDATA[
and TO_DATE(tpp.end_date || '23:59:59', 'yyyy-mm-dd hh24:mi:ss') <
to_date(to_char(sysdate, 'yyyy-mm-dd') || ' 23:59:59',
'yyyy-mm-dd hh24:mi:ss')
]]>
</if>
'">
select tpo.policy_no,
tpo.business_id,
sum(tpo.amount) || '' as amount,
tpo.add_time,
tpo.OPERATION
from fec.tp_policy_operation_log tpo,
fec.tp_account tpa,
fec.tp_policy tpp
where tpa.id = tpp.tp_account_id
and tpa.is_delete = 'N'
and tpp.policy_no = tpo.policy_no
and tpp.is_delete = 'N'
and tpa.member_id = #{memberId}
')
group by tpo.policy_no, tpo.business_id, tpo.add_time, tpo.OPERATION
UNION ALL
select tpp.policy_no,
'invalid' as business_id,
tpp.balance || '',
tpp.end_date as add_time,
' as OPERATION
from fec.tp_account tpa, fec.tp_policy tpp
where tpa.id = tpp.tp_account_id
and tpa.is_delete = 'N'
and tpp.is_delete = 'N'
and tpa.member_id = #{memberId}
<![CDATA[
and TO_DATE(tpp.end_date || '23:59:59', 'yyyy-mm-dd hh24:mi:ss') <
to_date(to_char(sysdate, 'yyyy-mm-dd') || ' 23:59:59',
'yyyy-mm-dd hh24:mi:ss')
]]>
</if>
order by add_time desc)
</select>
</mapper>
