ibatis sql

最新推荐文章于 2024-04-23 15:19:28 发布
oyprunner
最新推荐文章于 2024-04-23 15:19:28 发布
阅读量120
点赞数
分类专栏： Java-Ibatis 文章标签： SQL iBATIS C C++ C#
本文链接：https://blog.csdn.net/oyprunner/article/details/83531052
版权
Java-Ibatis 专栏收录该内容
1 篇文章 0 订阅
订阅专栏



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>