`<?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=”RegulationDO”>
<sql id=”queryParam”>
<where> <if test=”id != null and id != ””>
r.ID = #{id}
</if>
<if test=”isLock != null and isLock != ””>
AND r.IS_LOCK = #{isLock}
</if>
<if test=”checkPoint != null and checkPoint != ””>
AND r.CHECK_POINT = #{checkPoint}
</if>
<if test=”riskEventType != null and riskEventType != ””>
AND r.RISK_EVENT_TYPE = #{riskEventType}
</if>
<choose> <when test=”ruleTypeArray != null and ruleTypeArray.length > 0″>
AND r.RULE_TYPE in
<foreach collection="ruleTypeArray" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</when> <when test=”(ruleTypeArray == null or ruleTypeArray.length == 0) and ruleType != null and ruleType != ””>
AND r.RULE_TYPE = #{ruleType}
</when>
<when test=”ruleType == null or ruleType == ””>
AND r.RULE_TYPE != ’30’ AND r.RULE_TYPE != ’20’
</when>
</choose> <if test=”ruleRunType != null and ruleRunType != ””>
AND r.RULE_RUN_TYPE = #{ruleRunType}
</if>
<if test=”ruleName != null and ruleName != ”” >
AND r.RULE_NAME like ‘%’||#{ruleName}||’%’
</if>
<if test=”ruleDesc != null and ruleDesc != ”” >
AND r.RULE_DESC = #{ruleDesc}
</if>
<if test=”transType != null and transType != ””>
AND r.TRANS_TYPE = #{transType}
</if>
<if test=”ruleClassify != null and ruleClassify != ””>
AND r.RULE_CLASSIFY = #{ruleClassify}
</if>
<if test=”createOper != null and createOper != ”” >
AND r.CREATE_OPER = #{createOper}
</if>
<if test=”updateOper != null and updateOper != ”” >
AND r.UPDATE_OPER = #{updateOper}
</if>
<if test=”inuseBegTime != null and inuseBegTime != ”” >
AND r.INUSE_BEG_TIME = to_date(#{inuseBegTime,jdbcType=VARCHAR},’yyyy-mm-dd hh24:mi:ss’)
</if>
<if test=”inuseEndTime != null and inuseEndTime != ”” >
AND r.INUSE_END_TIME = to_date(#{inuseEndTime,jdbcType=VARCHAR},’yyyy-mm-dd hh24:mi:ss’)
</if>
<if test=”minAmount != null and minAmount != ”” >
AND r.MIN_AMOUNT = #{minAmount}
</if>
<if test=”checkCustomerId != null and checkCustomerId != ”” >
AND r.CHECK_CUSTOMER_ID = #{checkCustomerId}
</if>
<if test=”checkCardId != null and checkCardId != ”” >
AND r.CHECK_CARD_ID = #{checkCardId}
</if>
<if test=”ruleContent != null and ruleContent != ”” >
AND r.RULE_CONTENT = #{ruleContent}
</if>
<if test=”runMode != null and runMode != ”” >
AND r.RUN_MODE = #{runMode}
</if>
<if test=”createTime != null and createTime != ”” >
AND r.CREATE_TIME = to_date(#{createTime,jdbcType=VARCHAR},’yyyy-mm-dd hh24:mi:ss’)
</if>
<if test=”updateTime != null and updateTime != ”” >
AND r.UPDATE_TIME = to_date(#{updateTime,jdbcType=VARCHAR},’yyyy-mm-dd hh24:mi:ss’)
</if>
<if test=”payType != null and payType != ”” >
AND r.PAY_TYPE = #{payType}
</if>
<if test=”grade != null and grade != ”” >
AND r.GRADE = #{grade}
</if>
<if test=”riskRuleId != null and riskRuleId != ”” >
AND r.RISKRULEID = #{riskRuleId}
</if>
<if test=”isInUse != null and isInUse != ””>
AND r.IS_IN_USE = #{isInUse}
</if>
<if test=’isInUse != null and isInUse == “1”‘>
AND r.INUSE_END_TIME > sysdate
</if>
<if test=”ruleResult != null and ruleResult != ””>
AND r.RULE_RESULT = #{ruleResult}
</if>
<if test=”safetyFlag != null and safetyFlag != ””>
AND r.SAFETY_FLAG = #{safetyFlag}
</if>
<if test=”authority != null and authority != ””>
<choose> <when test=”authority == ‘ 安全付科技 '”>
AND (r.AUTHORITY = #{authority} OR r.AUTHORITY = ” OR r.AUTHORITY IS NULL)
</when>
<otherwise> AND r.AUTHORITY = #{authority}
</otherwise>
</choose> </if> </where> </sql>
<sql id=”queryInfo”>
r.ID id,r.IS_LOCK isLock,
r.CHECK_POINT checkPoint,
r.RISK_EVENT_TYPE riskEventType,
r.RULE_TYPE ruleType,
r.RULE_RUN_TYPE ruleRunType,
r.RULE_NAME ruleName,
r.RULE_DESC ruleDesc,
r.TRANS_TYPE transType,
r.RULE_CLASSIFY ruleClassify,
r.CREATE_OPER createOper,
r.CREATE_TIME createTime,
r.UPDATE_OPER updateOper,
to_char(r.UPDATE_TIME,'yyyy-mm-dd hh24:mi:ss') updateTime,
r.INUSE_BEG_TIME inuseBegTime,
r.INUSE_END_TIME inuseEndTime,
r.MIN_AMOUNT minAmount,
r.CHECK_CUSTOMER_ID checkCustomerId,
r.CHECK_CARD_ID checkCardId,
r.RULE_CONTENT ruleContent,
r.RUN_MODE runMode,
r.PAY_TYPE payType,
r.GRADE grade,
r.RISKRULEID riskRuleId,
r.RULE_RESULT ruleResult,
r.RULE_ACTION ruleAction,
r.ICE_TYPE iceType,
r.RULE_CONTENT_DESC ruleContentDesc,
r.RISK_EXTRA_MSG riskExtraMsg,
r.IS_IN_USE isInUse,
r.SELECTOR_ITEMS selectorItems,
r.final_rule_content finalRuleContent,
r.RULE_REAL_NAME ruleRealName,
r.SAFETY_FLAG safetyFlag,
r.AUTHORITY authority
</sql>
<select id=”queryTheRuleName” parameterType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO”
resultType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO”>
<![CDATA[
SELECT * FROM tc_rc_regulations WHERE rule_name=#{ruleName} AND check_point=#{checkPoint}
]]>
</select>
<select id=”queryRuleName” parameterType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO”
resultType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO”>
<![CDATA[
SELECT * FROM tc_rc_regulations WHERE rule_name=#{ruleName} AND id <>#{id} AND check_point=#{checkPoint}
]]>
</select>
<select id=”queryByRuleName” resultType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO”>
SELECT <include refid=”queryInfo”/>
FROM TC_RC_REGULATIONS r
WHERE RULE_NAME like '%'||#{ruleName}||'%'
order by inuseBegTime desc
</select>
<select id=”queryById” resultType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO”>
SELECT <include refid=”queryInfo”/>
FROM TC_RC_REGULATIONS r
WHERE Id=#{id}
order by updateTime desc
</select>
<select id=”queryByRiskRuleId” resultType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO”>
SELECT <include refid=”queryInfo”/>
FROM TC_RC_REGULATIONS r
WHERE RISKRULEID=#{riskRuleId}
</select>
<select id=”queryAll” resultType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO”>
SELECT <include refid=”queryInfo”/>
FROM TC_RC_REGULATIONS r
<include refid="queryParam" />
</select> <!– 新增 –>
<insert id=”add” parameterType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO” useGeneratedKeys=”true” keyProperty=”ID”>
<![CDATA[
INSERT INTO TC_RC_REGULATIONS
(ID, IS_LOCK, CHECK_POINT, RISK_EVENT_TYPE, RULE_TYPE, RULE_RUN_TYPE, RULE_NAME, RULE_DESC, TRANS_TYPE, RULE_CLASSIFY, CREATE_OPER, CREATE_TIME,
UPDATE_OPER, UPDATE_TIME, INUSE_BEG_TIME , INUSE_END_TIME, RULE_CONTENT, RUN_MODE, PAY_TYPE, GRADE,RISKRULEID,
RULE_RESULT,RULE_ACTION,ICE_TYPE, RULE_CONTENT_DESC,CHECK_CUSTOMER_ID,CHECK_CARD_ID,RISK_EXTRA_MSG,IS_IN_USE,SELECTOR_ITEMS,RULE_REAL_NAME,SAFETY_FLAG,AUTHORITY)
VALUES
(#{id,jdbcType=VARCHAR},'0',#{checkPoint,jdbcType=VARCHAR},#{riskEventType,jdbcType=VARCHAR},#{ruleType,jdbcType=VARCHAR},
#{ruleRunType,jdbcType=VARCHAR},#{ruleName,jdbcType=VARCHAR},#{ruleDesc,jdbcType=VARCHAR},
#{transType,jdbcType=VARCHAR},#{ruleClassify,jdbcType=VARCHAR},#{createOper,jdbcType=VARCHAR},sysdate,#{updateOper,jdbcType=VARCHAR},
sysdate,to_date(#{inuseBegTime,jdbcType=VARCHAR},'yyyy-mm-dd hh24:mi:ss'),
to_date(#{inuseEndTime,jdbcType=VARCHAR},'yyyy-mm-dd hh24:mi:ss'),#{ruleContent,jdbcType=VARCHAR},
#{runMode,jdbcType=VARCHAR},#{payType,jdbcType=VARCHAR},#{grade,jdbcType=VARCHAR},
SEQ_RISK_RULE_ID.NEXTVAL,#{ruleResult,jdbcType=VARCHAR},#{ruleAction,jdbcType=VARCHAR},
#{iceType,jdbcType=VARCHAR},#{ruleContentDesc,jdbcType=VARCHAR},#{checkCustomerId,jdbcType=VARCHAR},#{checkCardId,jdbcType=VARCHAR},#{riskExtraMsg,jdbcType=VARCHAR},#{isInUse,jdbcType=CHAR},#{selectorItems,jdbcType=CHAR},
#{ruleRealName,jdbcType=VARCHAR},#{safetyFlag,jdbcType=VARCHAR},#{authority,jdbcType=VARCHAR})
]]>
<!– <selectKey resultType=”java.lang.Integer” keyProperty=”ID”>
SELECT LAST_INSERT_ID(); </selectKey> –> </insert>
<select id=”query” resultType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO”>
SELECT <include refid=”queryInfo”/>, ra.APPROVE_STATUS approveStatus
FROM TC_RC_REGULATIONS r left join TC_RC_REGULATION_APPROVE ra
on r.id = ra.regulation_id AND ra.APPROVE_STATUS='0'
<include refid="queryParam" />
order by r.UPDATE_TIME desc
</select>
<select id=”queryOne” resultType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO”>
SELECT <include refid=”queryInfo”/>
FROM TC_RC_REGULATIONS r
<include refid="queryParam" />
</select>
<update id=”update” parameterType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO”>
update TC_RC_REGULATIONS
<set>
<if test=”isLock != null and isLock != ””>
IS_LOCK = #{isLock},
</if>
<if test=”checkPoint != null and checkPoint != ””>
CHECK_POINT = #{checkPoint},
</if>
<if test=”riskEventType != null and riskEventType != ””>
RISK_EVENT_TYPE = #{riskEventType},
</if>
<if test=”ruleType != null and ruleType != ””>
RULE_TYPE = #{ruleType},
</if>
<if test=”ruleRunType != null and ruleRunType != ””>
RULE_RUN_TYPE = #{ruleRunType},
</if>
<if test=”ruleName != null and ruleName != ”” >
RULE_NAME = #{ruleName},
</if>
<if test=”transType != null and transType != ””>
TRANS_TYPE = #{transType},
</if>
<if test=”ruleClassify != null and ruleClassify != ””>
RULE_CLASSIFY = #{ruleClassify},
</if>
<if test=”updateOper != null and updateOper != ”” >
UPDATE_OPER = #{updateOper},
</if>
UPDATE_TIME = sysdate,
<if test="inuseBegTime != null and inuseBegTime !=''" >
INUSE_BEG_TIME = to_date(#{inuseBegTime,jdbcType=VARCHAR},’yyyy-mm-dd hh24:mi:ss’),
</if>
<if test=”inuseEndTime == null or inuseEndTime == ” ” >
INUSE_END_TIME = null,
</if>
<if test=”inuseEndTime != null and inuseEndTime != ”” >
INUSE_END_TIME = to_date(#{inuseEndTime,jdbcType=VARCHAR},’yyyy-mm-dd hh24:mi:ss’),
</if>
<if test=”ruleContent != null and ruleContent != ”” >
RULE_CONTENT = #{ruleContent},
</if>
<if test=”ruleContentDesc != null and ruleContentDesc != ”” >
RULE_CONTENT_DESC = #{ruleContentDesc},
</if>
<if test=”ruleResult != null and ruleResult != ”” >
RULE_RESULT = #{ruleResult},
</if>
RULE_ACTION = #{ruleAction},
<if test="iceType != null and iceType !=''" >
ICE_TYPE = #{iceType},
</if>
<if test=”riskExtraMsg != null and riskExtraMsg !=””>
RISK_EXTRA_MSG = #{riskExtraMsg},
</if>
<if test=”isInUse != null and isInUse != ””>
IS_IN_USE = #{isInUse},
</if>
<if test=”selectorItems != null and selectorItems != ””>
SELECTOR_ITEMS = #{selectorItems},
</if>
<if test=”ruleDesc != null and ruleDesc != ””>
RULE_DESC = #{ruleDesc,jdbcType=VARCHAR},
</if>
<if test=”minAmount != null and minAmount != ””>
MIN_AMOUNT = #{minAmount,jdbcType=VARCHAR},
</if>
<if test=”checkCustomerId != null and checkCustomerId != ”” >
CHECK_CUSTOMER_ID = #{checkCustomerId,jdbcType=VARCHAR},
</if>
<if test=”checkCardId != null and checkCardId != ”” >
CHECK_CARD_ID = #{checkCardId,jdbcType=VARCHAR},
</if>
<if test=”runMode != null and runMode != ””>
RUN_MODE = #{runMode,jdbcType=VARCHAR},
</if>
<if test=”payType != null and payType != ””>
PAY_TYPE = #{payType,jdbcType=VARCHAR},
</if>
<if test=”grade != null and grade != ””>
GRADE = #{grade,jdbcType=VARCHAR},
</if>
<if test=”finalRuleContent != null and finalRuleContent != ””>
final_rule_content = #{finalRuleContent,jdbcType=VARCHAR},
</if>
<if test=”ruleRealName != null and ruleRealName != ”” >
RULE_REAL_NAME = #{ruleRealName},
</if>
<if test=”safetyFlag != null and safetyFlag != ”” >
SAFETY_FLAG = #{safetyFlag},
</if>
<if test=”authority != null and authority != ”” >
AUTHORITY = #{authority},
</if>
</set>
where ID =#{id,jdbcType=VARCHAR}
</update>
<update id=”updatePushResult” parameterType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO”>
update TC_RC_REGULATIONS T set T.push_code = #{pushCode,jdbcType=VARCHAR},T.riskRuleId = #{grade,jdbcType=VARCHAR}
where ID =#{id,jdbcType=VARCHAR}
</update>
<update id=”updateIsLock” parameterType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO”>
update TC_RC_REGULATIONS T set T.IS_LOCK = ‘1’
<if test="authority != null and authority !=''">
, AUTHORITY = #{authority,jdbcType=VARCHAR}
</if>
where ID =#{id,jdbcType=VARCHAR}
</update>
<select id=”count” resultType=”java.lang.Integer”>
SELECT COUNT(1) FROM TC_RC_REGULATIONS r
<include refid="queryParam" />
</select> <select id=”queryWithModule” parameterType=”java.util.Map” resultType=”com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO”>
select r.id, r.rule_content ruleContent, r.riskruleid, r.final_rule_content finalRuleContent, r.inuse_end_time inuseEndTime,
r.RULE_ACTION ruleAction
from tc_rc_regulations r
left join t_rule_module_relation a
on a.rule_id = r.riskruleid
and a.type = #{type,jdbcType=VARCHAR}
<where>
<if test=”moduleId != null and moduleId != ””>
a.module_id = #{moduleId,jdbcType=VARCHAR}
</if>
</where> </select> <update id=”updateQuickDisable” parameterType=”java.util.Map”>
update TC_RC_REGULATIONS T
set T.IS_IN_USE = '0',
T.IS_LOCK = '0',
T.UPDATE_OPER = #{updateOper, jdbcType=VARCHAR},
T.UPDATE_TIME = sysdate
where riskRuleId = #{riskRuleId,jdbcType=VARCHAR}
</update>
</mapper>`