ibatis sql




1----------------------------查询------------------------------------------------
<select id="getTableNme" parameterClass="com.isoftstone.telesales.sys.dto.TableCodeDTO"
resultClass="java.lang.String">
select C_NME_CN from t_prd_ins where c_insrnc_cde=#codeId# and c_nme_cn=#codeDescription#
</select>


<select id="tbCode_selectProvList" resultClass="com.isoftstone.telesales.common.dto.TbCodeDTO">
<![CDATA[
select CODE as code,
NAME as name
From t_code where kind = 'SYPVCLST' and code like '%0000' order by CODE
]]>
</select>


<select id="tbCommCode1_queryTCommCode1" parameterClass="java.util.Hashtable" resultClass="com.isoftstone.telesales.common.dto.TbCommCode1DTO">
<![CDATA[
SELECT C_CDE AS cde,
C_ENM AS enm,
C_CNM AS cnm,
C_PAR_CDE AS parCde
FROM T_COMM_CODE1
]]>
<dynamic prepend="WHERE">
<isNotEmpty property="comboxId" prepend="and">
<![CDATA[ N_COMBOX_ID = #comboxId# ]]>
</isNotEmpty>
<isNotEmpty property="parCde" prepend="and">
<![CDATA[ C_PAR_CDE = #parCde# ]]>
</isNotEmpty>
<isEqual property="prodNo" prepend="and" compareValue="0331">
<![CDATA[ C_CDE in('01','02','13','25')]]>
</isEqual>
</dynamic>
</select>


<select id="tbArea_selectAllProvice" parameterClass="java.lang.String"
resultClass="com.isoftstone.telesales.common.dto.TbAreaDTO">
<![CDATA[
SELECT AREACODE AS areaCode, AREANAME As areaName
FROM Tb_Area WHERE PARENTAREACODE='0001'
]]>
</select>


<select id="tbAgent_selectCmpnyAgtCdeList" parameterClass="java.util.Map"
resultClass="com.isoftstone.telesales.common.dto.TbAgentDTO">
<![CDATA[
SELECT C_CHA_CDE as chaCde,C_CHA_NME as chaNme FROM T_AGENT WHERE C_FRONT_BACK_MRK='150001' AND C_CHA_CLS = #cChaCls# AND C_CHA_MRK <> '1'
AND (C_DPT_CDE ='' OR C_DPT_CDE ='00' OR C_DPT_CDE = #subDptCde# OR C_DPT_CDE = #cDptCde#) ORDER BY C_CHA_CDE
]]>
</select>



<select id="tbLockInfo_queryList" parameterClass="com.isoftstone.telesales.common.dto.TbLockInfoDTO"
resultClass="com.isoftstone.telesales.common.dto.TbLockInfoDTO">
<![CDATA[
SELECT
C_LTI_ID as ltiId,
C_TAB_NME as tabNme,
C_LOCK_FIELD as lockField,
C_LOCK_CONTENT as lockContent,
C_LOCK_TYPE as lockType,
C_CRT_CDE as crtCde
FROM TB_LOCK_INFO
WHERE 1=1
]]>
<isNotEmpty property="ltiId" prepend="and">
<![CDATA[ C_LTI_ID = #ltiId# ]]>
</isNotEmpty>
<isNotEmpty property="tabNme" prepend="and">
<![CDATA[ C_TAB_NME = #tabNme# ]]>
</isNotEmpty>
<isNotEmpty property="lockField" prepend="and">
<![CDATA[ C_LOCK_FIELD = #lockField# ]]>
</isNotEmpty>
<isNotEmpty property="lockContent" prepend="and">
<![CDATA[ C_LOCK_CONTENT = #lockContent# ]]>
</isNotEmpty>
<isNotEmpty property="lockType" prepend="and">
<![CDATA[ C_LOCK_TYPE = #lockType# ]]>
</isNotEmpty>
<isNotEmpty property="crtCde" prepend="and">
<![CDATA[ C_CRT_CDE = #crtCde# ]]>
</isNotEmpty>
</select>


<select id="query_contact_list_count" parameterClass="com.isoftstone.telesales.contact.dto.ContactRecDTO" resultClass="java.lang.Long">
<![CDATA[
select count(*)
from TB_CONTACT_REC t1
left join TB_INS_INFO t4 on (t1.C_TCR_ID = t4.C_CONTACT_ID and t4.c_del = '0'),
TB_CST_INFO t2, TB_USER_INFO t5
where t1.c_del = '0'
and t2.c_del = '0'
and t5.c_del = '0'
and t1.C_TCI_ID = t2.C_TCI_ID
and t1.C_USER_NAME = t5.C_NME
]]>
<dynamic prepend="and">
<isNotEmpty property="nme" prepend="and">
<![CDATA[ t2.C_NME=#nme# ]]>
</isNotEmpty>
<isNotEmpty property="userName" prepend="and">
<![CDATA[ t5.C_TRUE_NME = #userName# ]]>
</isNotEmpty>
<isNotEmpty property="custMobile" prepend="and">
<![CDATA[ t2.C_MOBILE= #custMobile# ]]>
</isNotEmpty>
<isNotEmpty property="custProvince" prepend="and">
<![CDATA[ t2.C_PROVINCE= #custProvince# ]]>
</isNotEmpty>
<isNotEmpty property="custCity" prepend="and">
<![CDATA[ t2.C_CITY= #custCity# ]]>
</isNotEmpty>
<isNotEmpty property="vrm" prepend="and">
<![CDATA[ t4.C_VRM = #vrm# ]]>
</isNotEmpty>
<isNotEmpty property="insType" prepend="and">
<![CDATA[ t4.C_TYPE= #insType# ]]>
</isNotEmpty>
<isNotEmpty property="appNo" prepend="and">
<![CDATA[ t4.C_APP_NO= #appNo# ]]>
</isNotEmpty>
<isNotEmpty property="plyNo" prepend="and">
<![CDATA[ t4.C_PLY_NO= #plyNo# ]]>
</isNotEmpty>
<isNotEmpty property="contactBegDate" prepend="and">
<![CDATA[ t1.T_CRT_DATE >= to_date(#contactBegDate# || ' 00:00:00', 'yyyy-MM-dd HH24:mi:ss') ]]>
</isNotEmpty>
<isNotEmpty property="contactEndDate" prepend="and">
<![CDATA[ t1.T_CRT_DATE <= to_date(#contactEndDate# || ' 23:59:59', 'yyyy-MM-dd HH24:mi:ss') ]]>
</isNotEmpty>
</dynamic>
</select>


<select id="tagModel_query_forCount" parameterClass="com.isoftstone.telesales.developmodel.dto.TbInsInfoCustomDTO" resultClass="java.lang.Long">
<![CDATA[
SELECT COUNT(*) AS totalResults
FROM TB_INS_INFO
]]>
<dynamic prepend="WHERE">
<isNotEmpty property="plyNo" prepend="and">
<![CDATA[ C_PLY_NO = #plyNo# ]]>
</isNotEmpty>
<isNotEmpty property="appNo" prepend="and">
<![CDATA[ C_APP_NO = #appNo# ]]>
</isNotEmpty>
<isNotEmpty property="type" prepend="and">
<![CDATA[ C_TYPE = #type# ]]>
</isNotEmpty>
<isNotEmpty property="dptCde" prepend="and">
<![CDATA[ C_DPT_CDE = #dptCde# ]]>
</isNotEmpty>
<isNotEmpty property="stDate" prepend="and">
<![CDATA[ T_ST_DATE = #stDate# ]]>
</isNotEmpty>
<isNotEmpty property="endDate" prepend="and">
<![CDATA[ T_END_DATE = #endDate# ]]>
</isNotEmpty>
</dynamic>
</select>


2----------------------------------------------insert------------------------------------------------

<insert id="tbLockInfo_insert" parameterClass="com.isoftstone.telesales.common.dto.TbLockInfoDTO">
<![CDATA[
INSERT INTO TB_LOCK_INFO
(C_LTI_ID, C_TAB_NME, C_LOCK_FIELD, C_CRT_CDE, T_CRT_DATE, C_UPD_CDE, T_UPD_DATE, C_LOCK_TYPE, C_LOCK_CONTENT)
VALUES
(#ltiId# , #tabNme#, #lockField#, #crtCde#, sysdate, #updCde#, #updDate#, #lockType#, #lockContent#)
]]>
</insert>


<insert id="insInfo_updateStateForCust" parameterClass="java.util.Map">
<![CDATA[
INSERT INTO TB_INS_INFO
(C_TII_ID, C_CST_ID, C_CONTACT_ID,C_VRM, C_PLY_NO, C_APP_NO, C_TYPE, T_APP_DATE, T_ST_DATE, T_END_DATE, C_TOTAL_FEE,
C_USER, C_STAT, C_NOTE, C_DELIVER_ID, C_DPT_CDE, C_DELIVER_STAT, T_DELIVER_DATE, T_DISPATCH_DATE, C_TASK_ID,
C_CRT_CDE, T_CRT_DATE, C_UPD_CDE, T_UPD_DATE, C_DEL, C_IS_ORPHAN, T_PRINT_DATE, C_IS_TELESALE_PLY, C_DISPATCH_USER,
C_DIS_LEVEL,C_FIN_STAT, C_CORE_CLNTAPPSN, C_CORE_CLNTSN,C_SMS_FLAG,C_SMS_ID,C_INSENTDATE_MD,C_CORE_CUSTNAME)
SELECT
#pkId#,C_CST_ID, C_CONTACT_ID,C_VRM,
]]>
<isEmpty property="plyNo">
<![CDATA[ C_PLY_NO, ]]>
</isEmpty>
<isNotEmpty property="plyNo">
<![CDATA[ #plyNo#, ]]>
</isNotEmpty>
<![CDATA[ C_APP_NO, C_TYPE, T_APP_DATE, T_ST_DATE, ]]>
<isEmpty property="endDate">
<![CDATA[ T_END_DATE, ]]>
</isEmpty>
<isNotEmpty property="endDate">
<![CDATA[ #endDate#, ]]>
</isNotEmpty>
<![CDATA[ C_TOTAL_FEE,
C_USER, C_STAT, C_NOTE, C_DELIVER_ID, C_DPT_CDE, C_DELIVER_STAT, T_DELIVER_DATE, T_DISPATCH_DATE, C_TASK_ID,
C_CRT_CDE, T_CRT_DATE, #updCde#, SYSDATE, '0', C_IS_ORPHAN, T_PRINT_DATE, C_IS_TELESALE_PLY, C_DISPATCH_USER,
C_DIS_LEVEL,
]]>
<isEmpty property="finStat">
<![CDATA[ C_FIN_STAT, ]]>
</isEmpty>
<isNotEmpty property="finStat">
<![CDATA[ #finStat#,]]>
</isNotEmpty>
<![CDATA[
C_CORE_CLNTAPPSN,C_CORE_CLNTSN ,C_SMS_FLAG,C_SMS_ID,C_INSENTDATE_MD,C_CORE_CUSTNAME
FROM TB_INS_INFO
WHERE C_TII_ID = #tiiId#
]]>
</insert>


<insert id="tbTaskInfo_updateState" parameterClass="java.util.Map">
<![CDATA[
INSERT INTO TB_TASK_INFO
(N_TTI_ID, C_TTI_ID, C_CST_ID, C_TEAM_ID, C_NME, T_ASSIGN_DATE, C_STRATUM, C_STAT,
C_FOLLOW_RSN, C_RSN_NOTE, C_CANCEL_RSN, C_CANCEL_RSN_NOTE, C_NEXT_FOLLOW_CONTENT,
C_NEXT_FOLLOW_DATE, T_CANCEL_DATE, C_CANCEL_TAG, C_RECYCLE_DATE, C_USERID,
C_ASSIGN_TAG, C_TSK_TYPE, C_REMARK, C_CRT_CDE, T_CRT_DATE, C_UPD_CDE, T_UPD_DATE,
C_DEL, C_TSK_INS_ID, C_TSK_ACTIVATION_FLAG,C_PUR_LEVEL,C_OUT_SUCESS_REASON,C_OUT_FAILURE_REASON,C_OTHER_REASON,C_IN_BUSINESS_REASON,C_IN_INFO_CHANGE,C_IN_IS_COMPLA,C_CST_PROVINCE,C_CST_CITY,C_CST_SOURCE)
SELECT
#pkId#, C_TTI_ID, C_CST_ID, C_TEAM_ID, C_NME, T_ASSIGN_DATE, C_STRATUM, #stat#,
C_FOLLOW_RSN, C_RSN_NOTE, C_CANCEL_RSN, C_CANCEL_RSN_NOTE, C_NEXT_FOLLOW_CONTENT,
C_NEXT_FOLLOW_DATE, T_CANCEL_DATE, C_CANCEL_TAG, C_RECYCLE_DATE, C_USERID,
C_ASSIGN_TAG, C_TSK_TYPE, C_REMARK, C_CRT_CDE, SYSDATE, #updCde#, SYSDATE,
'0', C_TSK_INS_ID, C_TSK_ACTIVATION_FLAG,C_PUR_LEVEL,C_OUT_SUCESS_REASON,C_OUT_FAILURE_REASON,C_OTHER_REASON,C_IN_BUSINESS_REASON,C_IN_INFO_CHANGE,C_IN_IS_COMPLA,C_CST_PROVINCE,C_CST_CITY,C_CST_SOURCE
FROM TB_TASK_INFO
WHERE N_TTI_ID = #nttiId#
]]>
</insert>


3-----------------------------------------delete------------------------------------------------

<delete id="tbLockInfo_delete" parameterClass="com.isoftstone.telesales.common.dto.TbLockInfoDTO" >
<![CDATA[
DELETE TB_LOCK_INFO
WHERE 1=1
]]>
<isNotEmpty property="ltiId" prepend="and">
<![CDATA[ C_LTI_ID = #ltiId# ]]>
</isNotEmpty>
<isNotEmpty property="tabNme" prepend="and">
<![CDATA[ C_TAB_NME = #tabNme# ]]>
</isNotEmpty>
<isNotEmpty property="lockField" prepend="and">
<![CDATA[ C_LOCK_FIELD = #lockField# ]]>
</isNotEmpty>
<isNotEmpty property="lockContent" prepend="and">
<![CDATA[ C_LOCK_CONTENT = #lockContent# ]]>
</isNotEmpty>
<isNotEmpty property="lockType" prepend="and">
<![CDATA[ C_LOCK_TYPE = #lockType# ]]>
</isNotEmpty>
</delete>


4 ---------------------------------------update---------------------------------------------

<update id="insInfo_deleteByAppNo" parameterClass="java.lang.String">
update
TB_INS_INFO
set
C_DEL = '1'
where
TB_INS_INFO.C_APP_NO=#appNo#
</update>


<update id="insInfo_updateData" parameterClass="com.isoftstone.telesales.common.dto.TbInsInfoDTO">
update
TB_INS_INFO
set
C_PLY_NO=#plyNo#,
C_APP_NO=#appNo#,
C_TYPE=#type#,
T_ST_DATE=
to_date(substr(#stDate#,1,10),'yy-MM-dd'),
T_END_DATE= to_date(substr(#endDate#,1,10),'yy-MM-dd'),
C_TOTAL_FEE=#totalFee#,
C_CONTACT_ID=#contactId#,
C_TASK_ID=#taskId#
where C_TII_ID = #tiiId#
</update>





评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值