/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名 ：eo顧客基幹システム * モジュール名 ：JSYejbECR0011B030TPDA * ソースファイル名：JSYejbECR0011B030TPDA.java * 作成者 ：EK909582 * 日付 ：2013年01月11日 *＜機能概要＞ * 対応記録一覧照会(簡易検索)テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン 修正日 修正者 修正内容 * ｖ1.00.00 * ｖ2.00.00 2013/02/12 富士通 IT1-2013-0000265 品質強化対策（性能改善） * ｖ4.00.00 2013/03/25 FJ）伊藤 OT-2013-0000311 * ｖ6.00.00 2013/08/27 FJ）伊藤 OM-2013-0000447 性能改善 * ｖ6.00.01 2013/12/07 FJ）伊藤 OM-2013-0004056 受付番号の検索条件を変更 * ｖ8.00.00 2014/06/11 FJ）伊藤 OM-2014-0002131 OUT項目「対応開始年月日時分秒」の追加 * v65.00.00 2023/02/27 FJ）中原 【ANK-4346-00-00】【eo定期】CCMG再構築対応 * ********************************************************************************/ package eo.ejb.cbs.sqlf; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.fujitsu.futurity.model.base.CAANJDBCUtil; import com.fujitsu.futurity.model.base.CAANMsg; import com.fujitsu.futurity.model.base.CAANRuntimeException; import com.fujitsu.futurity.model.base.CAANSQLFacility; import com.fujitsu.futurity.model.ejb.common.JSYejbConnection; import com.fujitsu.futurity.model.ejb.common.JSYejbLog; import com.fujitsu.futurity.model.ejb.common.StatusCodes; import com.fujitsu.futurity.model.ejb.common.fw.AgentDispatchContext; import com.fujitsu.futurity.model.ejb.common.fw.TemplateDBAccessHandler; import eo.common.constant.JCRStrConst; import eo.common.constant.JPCModelConstant; import eo.common.util.JCRUtilCommon; import eo.ejb.cbm.entity.CR0011ETMsg; import eo.ejb.cbs.cbsmsg.ECR0011B030CBSMsg; import eo.ejb.cbs.cbsmsg.ECR0011B030CBSMsg1List; import eo.ejb.common.JCCModelCommon; /** * * テンプレートDBアクセス部品　ECR0011B030_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbECR0011B030TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbECR0011B030TPDA() { } /** * ECR0011B030_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void */ public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){ // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt = null; // リザルトセット ResultSet rsltQuery = null; /******************** * SQL文の作成 ********************/ try{ //*************************** 自動生成ソース修正 START ***************************// //パラメータ設定リスト List replaceList = new ArrayList(); // ＫＥＹ＿対応記録番号 String taioKirokNo = (String)inMsg.getObject(ECR0011B030CBSMsg.KEY_TAIO_KIROK_NO); // ＫＥＹ＿申込書番号 String mskmshoNo = (String)inMsg.getObject(ECR0011B030CBSMsg.KEY_MSKMSHO_NO); // ＫＥＹ＿サービス契約番号 String svcKeiNo = (String)inMsg.getObject(ECR0011B030CBSMsg.KEY_SVC_KEI_NO); // ＫＥＹ＿問合せ客番号 String proscstNo = (String)inMsg.getObject(ECR0011B030CBSMsg.KEY_PROSCST_NO); // (ＫＥＹ＿ｅｏＩＤ String eoid = String.valueOf(inMsg.getObject(ECR0011B030CBSMsg.KEY_EOID)); String[] eoidList = eoid.split(JCRStrConst.FUZZY_SEARCH_KUGIRI); StringBuilder sqlEoid = new StringBuilder(); if (eoid != null && !"".equals(eoid) && eoidList.length > 0) { for(int i = 0; i < eoidList.length ; i++) { if (i != 0) { sqlEoid.append(" OR "); } sqlEoid.append(" CUS1.EOID = ? "); } } // ＫＥＹ＿電話番号 String telNo = (String)inMsg.getObject(ECR0011B030CBSMsg.KEY_KEISHA_TELNO); // ＫＥＹ＿整理番号 String seiriNo = (String)inMsg.getObject(ECR0011B030CBSMsg.KEY_SEIRI_NO); // ＳＯＲＴ指定項目 String sortItem = (String)inMsg.getObject(ECR0011B030CBSMsg.SORT_SHITEI_KMK_NM); //運用日付 String opeDate = JCCModelCommon.getOpeDate(inMsg, null); boolean custFlg = false; boolean proscstFlg = false; //*************************** 自動生成ソース修正 END ***************************// //コネクション取得 con1 = JSYejbConnection.getConnection(CR0011ETMsg.getTableName()); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); //*************************** 自動生成ソース修正 START ***************************// // 問合せ客 入力値が設定されていない場合 if (JCRUtilCommon.isNull(proscstNo)) { custFlg = true; sql_Buff.append(" SELECT ") .append(" TKR1.TAIO_KIROK_NO, ") .append(" TKR1.TITLE, ") .append(" TKR1.SVC_KEI_NO, ") .append(" DECODE(CUS1.CUST_NM, NULL, ") .append(" DECODE(CUS1.CONT_CUST_NM, NULL, NULL, ") .append(" CASE WHEN ") .append(" CUS1.MEMBER_SBT_CD IN ('11', '21', '30') ") .append(" THEN ") .append(" CUS1.CONT_CUST_NM ") .append(" ELSE ") .append(" CASE WHEN ") .append(" (CUS1.MMB_SBT_TOKUT_FLG = '0' AND CUS1.EONETID_MI_IKO_FLG = '1') ") .append(" THEN ") .append(" CUS1.CONT_CUST_NM ") .append(" ELSE ") .append(" CUS1.CUST_NM ") .append(" END ") .append(" END ") .append(" ), ") .append(" CASE WHEN ") .append(" CUS1.MEMBER_SBT_CD IN ('11', '21', '30') ") .append(" THEN ") .append(" CUS1.CONT_CUST_NM ") .append(" ELSE ") .append(" CASE WHEN ") .append(" (CUS1.MMB_SBT_TOKUT_FLG = '0' AND CUS1.EONETID_MI_IKO_FLG = '1') ") .append(" THEN ") .append(" CUS1.CONT_CUST_NM ") .append(" ELSE ") .append(" CUS1.CUST_NM ") .append(" END ") .append(" END ") .append(" ) AS CUST_NM, ") .append(" CUS1.EOID, ") .append(" TKR1.UK_DTM, ") .append(" TKR1.TOIAWASE_STAT_CD, ") .append(" (SELECT ") .append(" CD01.CD_DIV_NM ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI CD01 ") .append(" WHERE ") .append(" CD01.CD_SBT_CD = 'CD00229' AND ") .append(" CD01.CD_DIV = TKR1.TOIAWASE_STAT_CD AND ") .append(" CD01.CD_TSTAYMD <= SUBSTR(TKR1.UPD_DTM, 1, 8) AND ") .append(" CD01.CD_TENDYMD >= SUBSTR(TKR1.UPD_DTM, 1, 8) AND ") .append(" CD01.MK_FLG = '0' ") .append(" ) AS TOIAWASE_STAT_NM, ") .append(" TKR1.TAIO_KIROK_STAT, ") .append(" (SELECT ") .append(" CD02.CD_DIV_NM ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI CD02 ") .append(" WHERE ") .append(" CD02.CD_SBT_CD = 'CD00230' AND ") .append(" CD02.CD_DIV = TKR1.TAIO_KIROK_STAT AND ") .append(" CD02.CD_TSTAYMD <= SUBSTR(TKR1.UPD_DTM, 1, 8) AND ") .append(" CD02.CD_TENDYMD >= SUBSTR(TKR1.UPD_DTM, 1, 8) AND ") .append(" CD02.MK_FLG = '0' ") .append(" ) AS TAIO_KIROK_STAT_NM, ") .append(" TKR1.CALLBK_KIBO_YMD, ") .append(" TKR1.CALLBK_KIBO_TIME, ") .append(" TKR1.CALLBK_SHITEI_TYPE_CD, ") .append(" (SELECT ") .append(" CD03.CD_DIV_NM ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI CD03 ") .append(" WHERE ") .append(" CD03.CD_SBT_CD = 'CD00457' AND ") .append(" CD03.CD_DIV = TKR1.CALLBK_SHITEI_TYPE_CD AND ") .append(" CD03.CD_TSTAYMD <= SUBSTR(TKR1.UPD_DTM, 1, 8) AND ") .append(" CD03.CD_TENDYMD >= SUBSTR(TKR1.UPD_DTM, 1, 8) AND ") .append(" CD03.MK_FLG = '0' ") .append(" ) AS CALLBK_SHITEI_TYPE_CD_NM, ") .append(" (SELECT ") .append(" CD04.CD_DIV_NM ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI CD04 ") .append(" INNER JOIN ZM_T_AGING AGIN ON ( ") .append(" CD04.CD_DIV = AGIN.AGING_STAT AND ") .append(" AGIN.AGING_SBT_CD = '004' AND ") .append(" AGIN.MK_FLG = '0' ") .append(" ) ") .append(" WHERE ") .append(" CD04.CD_SBT_CD = 'CD00063' AND ") .append(" CD04.CD_DIV = AGIN.AGING_STAT AND ") .append(" CD04.CD_TSTAYMD <= SUBSTR(TKR1.UPD_DTM, 1, 8) AND ") .append(" CD04.CD_TENDYMD >= SUBSTR(TKR1.UPD_DTM, 1, 8) AND ") .append(" CD04.MK_FLG = '0' AND ") .append(" AGIN.AGING_TG_VALUE = CUS1.EOID ") .append(" ) AS EOID_STATUS, ") .append(" TKR1.TAIO_STA_DTM ") .append(" FROM ") .append(" CR_T_TAIO_KIROK TKR1 "); // 電話番号 入力値が設定されている場合 if (!JCRUtilCommon.isNull(telNo)) { sql_Buff.append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" TEL_SVC_SUB.SVC_KEI_NO ") .append(" FROM ") .append(" ( ") .append(" ( ") .append(" SELECT ") .append(" SV01_TEL1.SVC_KEI_NO ") .append(" FROM ") .append(" KK_T_SVC_KEI SV01_TEL1, ") .append(" KK_T_SVC_KEI_UCWK SU01_TEL1, ") .append(" KK_T_SVKEIUW_EOH_TEL UT01_TEL1 ") .append(" WHERE ") .append(" SU01_TEL1.SVC_KEI_UCWK_NO = UT01_TEL1.SVC_KEI_UCWK_NO AND ") .append(" SU01_TEL1.GENE_ADD_DTM = UT01_TEL1.GENE_ADD_DTM AND ") .append(" (UT01_TEL1.TELNO = ? OR UT01_TEL1.DAIHYO_TELNO = ?) AND ") .append(" SU01_TEL1.MK_FLG = '0' AND ") .append(" UT01_TEL1.MK_FLG = '0' AND ") .append(" SU01_TEL1.KEIZK_AF_KEI_CHGECHU_FLG = '0' AND ") .append(" SV01_TEL1.SVC_KEI_NO = SU01_TEL1.SVC_KEI_NO AND ") .append(" SV01_TEL1.RSV_APLY_YMD <= ? AND ") .append(" SV01_TEL1.RSV_APLY_CD = '2' AND ") .append(" SV01_TEL1.MK_FLG = '0' ") .append(" ) ") .append(" UNION ALL ") .append(" ( ") .append(" SELECT ") .append(" SV01_TEL2.SVC_KEI_NO ") .append(" FROM ") .append(" KK_T_SVC_KEI SV01_TEL2, ") .append(" CK_T_CUST C002_TEL2 ") .append(" WHERE ") .append(" SV01_TEL2.SYSID = C002_TEL2.SYSID AND ") .append(" C002_TEL2.KEISHA_TELNO = ? AND ") .append(" C002_TEL2.RSV_APLY_YMD <= ? AND ") .append(" C002_TEL2.RSV_APLY_CD = '2' AND ") .append(" C002_TEL2.MK_FLG = '0' AND ") .append(" SV01_TEL2.RSV_APLY_YMD <= ? AND ") .append(" SV01_TEL2.RSV_APLY_CD = '2' AND ") .append(" SV01_TEL2.MK_FLG = '0' ") .append(" ) ") .append(" UNION ALL ") .append(" ( ") .append(" SELECT ") .append(" SV01_TEL3.SVC_KEI_NO ") .append(" FROM ") .append(" KK_T_SVC_KEI SV01_TEL3, ") .append(" CK_T_CUST CO01_TEL3, ") .append(" CK_T_CUST_KOJIN CK01_TEL3 ") .append(" WHERE ") .append(" CO01_TEL3.SYSID = CK01_TEL3.SYSID AND ") .append(" CO01_TEL3.GENE_ADD_DTM = CK01_TEL3.GENE_ADD_DTM AND ") .append(" CK01_TEL3.OFFC_TELNO = ? AND ") .append(" CO01_TEL3.RSV_APLY_YMD <= ? AND ") .append(" CO01_TEL3.RSV_APLY_CD = '2' AND ") .append(" CO01_TEL3.MK_FLG = '0' AND ") .append(" CK01_TEL3.MK_FLG = '0' AND ") .append(" SV01_TEL3.SYSID = CO01_TEL3.SYSID AND ") .append(" SV01_TEL3.RSV_APLY_YMD <= ? AND ") .append(" SV01_TEL3.RSV_APLY_CD = '2' AND ") .append(" SV01_TEL3.MK_FLG = '0' ") .append(" ) ") .append(" UNION ALL ") .append(" ( ") .append(" SELECT ") .append(" SV01_TEL4.SVC_KEI_NO ") .append(" FROM ") .append(" KK_T_SVC_KEI SV01_TEL4, ") .append(" CK_T_CUST CO01_TEL4, ") .append(" CK_T_CUST_KOJIN CK01_TEL4 ") .append(" WHERE ") .append(" CO01_TEL4.SYSID = CK01_TEL4.SYSID AND ") .append(" CO01_TEL4.GENE_ADD_DTM = CK01_TEL4.GENE_ADD_DTM AND ") .append(" CK01_TEL4.SKSHA_TELNO = ? AND ") .append(" CK01_TEL4.MK_FLG = '0' AND ") .append(" CO01_TEL4.RSV_APLY_YMD <= ? AND ") .append(" CO01_TEL4.RSV_APLY_CD = '2' AND ") .append(" CO01_TEL4.MK_FLG = '0' AND ") .append(" SV01_TEL4.SYSID = CO01_TEL4.SYSID AND ") .append(" SV01_TEL4.RSV_APLY_YMD <= ? AND ") .append(" SV01_TEL4.RSV_APLY_CD = '2' AND ") .append(" SV01_TEL4.MK_FLG = '0' ") .append(" GROUP BY ") .append(" SV01_TEL4.SVC_KEI_NO ") .append(" ) ") .append(" UNION ALL ") .append(" ( ") .append(" SELECT ") .append(" SV01_TEL5.SVC_KEI_NO ") .append(" FROM ") .append(" KK_T_SVC_KEI SV01_TEL5, ") .append(" CK_T_RRKS RKS1_TEL5 ") .append(" WHERE ") .append(" SV01_TEL5.SYSID = RKS1_TEL5.SYSID AND ") .append(" RKS1_TEL5.TELNO = ? AND ") .append(" RKS1_TEL5.RSV_APLY_YMD <= ? AND ") .append(" RKS1_TEL5.RSV_APLY_CD = '2' AND ") .append(" RKS1_TEL5.MK_FLG = '0' AND ") .append(" SV01_TEL5.RSV_APLY_YMD <= ? AND ") .append(" SV01_TEL5.RSV_APLY_CD = '2' AND ") .append(" SV01_TEL5.MK_FLG = '0' ") .append(" ) ") .append(" UNION ALL ") .append(" ( ") .append(" SELECT ") .append(" SV01_TEL6.SVC_KEI_NO ") .append(" FROM ") .append(" KK_T_SVC_KEI SV01_TEL6, ") .append(" KK_T_KAKINS KAKN_TEL6, ") .append(" KK_T_SEIKY_KEI SK01_TEL6 ") .append(" WHERE ") .append(" KAKN_TEL6.SEIKY_KEI_NO = SK01_TEL6.SEIKY_KEI_NO AND ") .append(" SK01_TEL6.SOHUS_TELNO = ? AND ") .append(" SK01_TEL6.RSV_APLY_YMD <= ? AND ") .append(" SK01_TEL6.RSV_APLY_CD = '2' AND ") .append(" SK01_TEL6.MK_FLG = '0' AND ") .append(" KAKN_TEL6.KAKINS_TSTAYMD <= ? AND ") .append(" KAKN_TEL6.KAKINS_TENDYMD >= ? AND ") .append(" KAKN_TEL6.MK_FLG = '0' AND ") .append(" SV01_TEL6.SVC_KEI_NO = KAKN_TEL6.SVC_KEI_NO AND ") .append(" SV01_TEL6.RSV_APLY_YMD <= ? AND ") .append(" SV01_TEL6.RSV_APLY_CD = '2' AND ") .append(" SV01_TEL6.MK_FLG = '0' ") .append(" ) ") .append(" UNION ALL ") .append(" ( ") .append(" SELECT ") .append(" SV01_TEL7.SVC_KEI_NO ") .append(" FROM ") .append(" KK_T_SVC_KEI SV01_TEL7, ") // ANK-4346-00-00 DEL START //.append(" CN_T_CONT_KEI CNK1_TEL7, ") // ANK-4346-00-00 DEL END .append(" CN_T_CONT_KEI_UCWK CNU1_TEL7 ") .append(" WHERE ") // ANK-4346-00-00 DEL START //.append(" CNK1_TEL7.CONT_KEI_NO = CNU1_TEL7.CONT_KEI_NO AND ") // ANK-4346-00-00 DEL END .append(" CNU1_TEL7.N_050_TELNO = ? AND ") .append(" CNU1_TEL7.MK_FLG = '0' AND ") // ANK-4346-00-00 MOD START //.append(" CNK1_TEL7.MK_FLG = '0' AND ") //.append(" SV01_TEL7.SYSID = CNK1_TEL7.SYSID AND ") .append(" SV01_TEL7.SYSID = CNU1_TEL7.SYSID AND ") // ANK-4346-00-00 MOD END .append(" SV01_TEL7.RSV_APLY_YMD <= ? AND ") .append(" SV01_TEL7.RSV_APLY_CD = '2' AND ") .append(" SV01_TEL7.MK_FLG = '0' ") .append(" ) ") .append(" ) TEL_SVC_SUB ") .append(" GROUP BY TEL_SVC_SUB.SVC_KEI_NO ") .append(" ) TEL_SVC ON ( ") .append(" TEL_SVC.SVC_KEI_NO = TKR1.SVC_KEI_NO ") .append(" ) "); replaceList.add(telNo); replaceList.add(telNo); replaceList.add(opeDate); replaceList.add(telNo); replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(telNo); replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(telNo); replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(telNo); replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(telNo); replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(telNo); replaceList.add(opeDate); } sql_Buff.append(" ,KK_T_SVC_KEI SVC1, ") .append(" CK_T_CUST CUS1 ") .append(" WHERE ") .append(" TKR1.SVC_KEI_NO = SVC1.SVC_KEI_NO AND ") .append(" TKR1.MK_FLG = '0' ") .append(" AND CUS1.SYSID = SVC1.SYSID ") .append(" AND SVC1.PRC_GRP_CD <> '99' ") .append(" AND (SVC1.SVC_KEI_NO, SVC1.RSV_APLY_YMD || SVC1.GENE_ADD_DTM) = ( ") .append(" SELECT ") .append(" SVCK_RCNT.SVC_KEI_NO, ") .append(" MAX(SVCK_RCNT.RSV_APLY_YMD || SVCK_RCNT.GENE_ADD_DTM) AS SVCK_RCNT_MAX ") .append(" FROM ") .append(" KK_T_SVC_KEI SVCK_RCNT ") .append(" WHERE ") .append(" SVCK_RCNT.SVC_KEI_NO = SVC1.SVC_KEI_NO AND ") .append(" SVCK_RCNT.RSV_APLY_YMD <= ? AND ") .append(" SVCK_RCNT.RSV_APLY_CD = '2' AND ") .append(" SVCK_RCNT.MK_FLG = '0' ") .append(" GROUP BY ") .append(" SVCK_RCNT.SVC_KEI_NO ") .append(" ) ") .append(" AND CUS1.KEISHA_TYPE_CD <> '5' ") .append(" AND (CUS1.SYSID, CUS1.RSV_APLY_YMD || CUS1.GENE_ADD_DTM) = ( ") .append(" SELECT ") .append(" CUST_RCNT.SYSID, ") .append(" MAX(CUST_RCNT.RSV_APLY_YMD || CUST_RCNT.GENE_ADD_DTM) AS CUST_RCNT_MAX ") .append(" FROM ") .append(" CK_T_CUST CUST_RCNT ") .append(" WHERE ") .append(" CUST_RCNT.SYSID = CUS1.SYSID AND ") .append(" CUST_RCNT.RSV_APLY_YMD <= ? AND ") .append(" CUST_RCNT.RSV_APLY_CD = '2' AND ") .append(" CUST_RCNT.MK_FLG = '0' ") .append(" GROUP BY ") .append(" CUST_RCNT.SYSID ") .append(" ) "); replaceList.add(opeDate); replaceList.add(opeDate); // 対応記録番号 入力値が設定されている場合 if (!JCRUtilCommon.isNull(taioKirokNo)) { sql_Buff.append(" AND TKR1.TAIO_KIROK_NO = ? "); replaceList.add(taioKirokNo); } // 申込書番号 入力値が設定されている場合 if (!JCRUtilCommon.isNull(mskmshoNo)) { sql_Buff.append(" AND (EXISTS(SELECT 'X' FROM KK_T_SVC_KEI SVC2 ") .append(" INNER JOIN KK_T_MSKM_DTL MSDT_TEL1 ") .append(" ON (SVC2.MSKM_DTL_NO = MSDT_TEL1.MSKM_DTL_NO) ") .append(" WHERE SVC2.SVC_KEI_NO = SVC1.SVC_KEI_NO ") .append(" AND SVC2.GENE_ADD_DTM = ( ") .append(" SELECT ") .append(" MIN(SVC2_GENE.GENE_ADD_DTM) AS SVC2_MIN ") .append(" FROM ") .append(" KK_T_SVC_KEI SVC2_GENE ") .append(" WHERE ") .append(" SVC2_GENE.SVC_KEI_NO = SVC2.SVC_KEI_NO AND ") .append(" SVC2_GENE.MK_FLG = '0') ") .append(" AND MSDT_TEL1.MSKMSHO_NO = ? ") .append(" AND (MSDT_TEL1.MSKM_DTL_NO, MSDT_TEL1.GENE_ADD_DTM) = ( ") .append(" SELECT ") .append(" MSDT_TEL2.MSKM_DTL_NO, ") .append(" MAX(MSDT_TEL2.GENE_ADD_DTM) AS MSDT_TEL2_MAX ") .append(" FROM ") .append(" KK_T_MSKM_DTL MSDT_TEL2 ") .append(" WHERE ") .append(" MSDT_TEL2.MSKM_DTL_NO = MSDT_TEL1.MSKM_DTL_NO AND ") .append(" MSDT_TEL2.MK_FLG = '0' ") .append(" GROUP BY ") .append(" MSDT_TEL2.MSKM_DTL_NO) ") .append(" ) ") .append(" ) "); replaceList.add(mskmshoNo); } // サービス契約番号 入力値が設定されている場合 if (!JCRUtilCommon.isNull(svcKeiNo)) { sql_Buff.append(" AND TKR1.SVC_KEI_NO = ? "); replaceList.add(svcKeiNo); } // 整理番号 入力値が設定されている場合 if (!JCRUtilCommon.isNull(seiriNo)) { sql_Buff.append(" AND SVC1.SEIRI_NO = ? "); replaceList.add(seiriNo); } // 電話番号 入力値が設定されている場合 if (!JCRUtilCommon.isNull(telNo)) { sql_Buff.append(" AND (EXISTS(SELECT 'X' FROM KK_T_KAKINS KAKN_TEL ") .append(" WHERE KAKN_TEL.SVC_KEI_NO = SVC1.SVC_KEI_NO ") .append(" AND KAKN_TEL.KAKINS_TSTAYMD <= ? ") .append(" AND KAKN_TEL.KAKINS_TENDYMD >= ? ") .append(" AND KAKN_TEL.MK_FLG = '0' ") .append(" AND EXISTS(SELECT 'X' FROM KK_T_SEIKY_KEI SK01 ") .append(" WHERE SK01.SOHUS_TELNO = ? ") .append(" AND SK01.SEIKY_KEI_NO =KAKN_TEL.SEIKY_KEI_NO ") .append(" AND (SK01.SEIKY_KEI_NO,SK01.RSV_APLY_YMD || SK01.GENE_ADD_DTM) = ( ") .append(" SELECT ") .append(" SK01_RCNT.SEIKY_KEI_NO, ") .append(" MAX(SK01_RCNT.RSV_APLY_YMD || SK01_RCNT.GENE_ADD_DTM) AS SK01_RCNT_MAX ") .append(" FROM ") .append(" KK_T_SEIKY_KEI SK01_RCNT ") .append(" WHERE ") .append(" SK01_RCNT.SEIKY_KEI_NO = SK01.SEIKY_KEI_NO AND ") .append(" SK01_RCNT.RSV_APLY_YMD <= ? AND ") .append(" SK01_RCNT.RSV_APLY_CD = '2' AND ") .append(" SK01_RCNT.MK_FLG = '0' ") .append(" GROUP BY ") .append(" SK01_RCNT.SEIKY_KEI_NO ") .append(" ) ") .append(" ) ") .append(" ) ") .append(" OR EXISTS(SELECT 'X' FROM CK_T_CUST CUST_TEL ") .append(" WHERE CUST_TEL.KEISHA_TELNO = ? ") .append(" AND CUST_TEL.SYSID = CUS1.SYSID ") .append(" AND CUST_TEL.GENE_ADD_DTM = CUS1.GENE_ADD_DTM ") .append(" AND CUST_TEL.RSV_APLY_YMD <= ? ") .append(" AND CUST_TEL.RSV_APLY_CD = '2' ") .append(" AND CUST_TEL.MK_FLG = '0') ") .append(" OR EXISTS(SELECT 'X' FROM KK_T_SVC_KEI_UCWK SU01_TEL1 ") .append(" WHERE SU01_TEL1.SVC_KEI_NO = SVC1.SVC_KEI_NO ") .append(" AND SU01_TEL1.KEIZK_AF_KEI_CHGECHU_FLG = '0' ") .append(" AND (SU01_TEL1.SVC_KEI_UCWK_NO,SU01_TEL1.RSV_APLY_YMD || SU01_TEL1.GENE_ADD_DTM) = ( ") .append(" SELECT ") .append(" SU01_RCNT.SVC_KEI_UCWK_NO, ") .append(" MAX(SU01_RCNT.RSV_APLY_YMD || SU01_RCNT.GENE_ADD_DTM) AS SU01_RCNT_MAX ") .append(" FROM ") .append(" KK_T_SVC_KEI_UCWK SU01_RCNT ") .append(" WHERE ") .append(" SU01_RCNT.SVC_KEI_UCWK_NO = SU01_TEL1.SVC_KEI_UCWK_NO AND ") .append(" SU01_RCNT.RSV_APLY_YMD <= ? AND ") .append(" SU01_RCNT.RSV_APLY_CD = '2' AND ") .append(" SU01_RCNT.MK_FLG = '0' ") .append(" GROUP BY ") .append(" SU01_RCNT.SVC_KEI_UCWK_NO) ") .append(" AND EXISTS(SELECT 'X' FROM KK_T_SVKEIUW_EOH_TEL EHTU_TEL1 ") .append(" WHERE EHTU_TEL1.TELNO = ? ") .append(" AND EHTU_TEL1.SVC_KEI_UCWK_NO = SU01_TEL1.SVC_KEI_UCWK_NO ") .append(" AND EHTU_TEL1.GENE_ADD_DTM = SU01_TEL1.GENE_ADD_DTM ") .append(" AND EHTU_TEL1.MK_FLG = '0') ") .append(" ) ") .append(" OR EXISTS(SELECT 'X' FROM KK_T_SVC_KEI_UCWK SU01_TEL2 ") .append(" WHERE SU01_TEL2.SVC_KEI_NO = SVC1.SVC_KEI_NO ") .append(" AND SU01_TEL2.KEIZK_AF_KEI_CHGECHU_FLG = '0' ") .append(" AND (SU01_TEL2.SVC_KEI_UCWK_NO,SU01_TEL2.RSV_APLY_YMD || SU01_TEL2.GENE_ADD_DTM) = ( ") .append(" SELECT ") .append(" SU01_RCNT.SVC_KEI_UCWK_NO, ") .append(" MAX(SU01_RCNT.RSV_APLY_YMD || SU01_RCNT.GENE_ADD_DTM) AS SU01_RCNT_MAX ") .append(" FROM ") .append(" KK_T_SVC_KEI_UCWK SU01_RCNT ") .append(" WHERE ") .append(" SU01_RCNT.SVC_KEI_UCWK_NO = SU01_TEL2.SVC_KEI_UCWK_NO AND ") .append(" SU01_RCNT.RSV_APLY_YMD <= ? AND ") .append(" SU01_RCNT.RSV_APLY_CD = '2' AND ") .append(" SU01_RCNT.MK_FLG = '0' ") .append(" GROUP BY ") .append(" SU01_RCNT.SVC_KEI_UCWK_NO ") .append(" ) ") .append(" AND EXISTS(SELECT 'X' FROM KK_T_SVKEIUW_EOH_TEL EHTU_TEL2 ") .append(" WHERE EHTU_TEL2.DAIHYO_TELNO = ? ") .append(" AND EHTU_TEL2.SVC_KEI_UCWK_NO = SU01_TEL2.SVC_KEI_UCWK_NO ") .append(" AND EHTU_TEL2.GENE_ADD_DTM = SU01_TEL2.GENE_ADD_DTM ") .append(" AND EHTU_TEL2.MK_FLG = '0') ") .append(" ) ") .append(" OR EXISTS(SELECT 'X' FROM CK_T_CUST_KOJIN CK01_TEL1 ") .append(" WHERE CK01_TEL1.OFFC_TELNO = ? ") .append(" AND CK01_TEL1.SYSID = CUS1.SYSID ") .append(" AND CK01_TEL1.GENE_ADD_DTM = CUS1.GENE_ADD_DTM ") .append(" AND CK01_TEL1.MK_FLG = '0') ") .append(" OR EXISTS(SELECT 'X' FROM CK_T_CUST_KOJIN CK01_TEL2 ") .append(" WHERE CK01_TEL2.SKSHA_TELNO = ? ") .append(" AND CK01_TEL2.SYSID = CUS1.SYSID ") .append(" AND CK01_TEL2.GENE_ADD_DTM = CUS1.GENE_ADD_DTM ") .append(" AND CK01_TEL2.MK_FLG = '0') ") .append(" OR EXISTS(SELECT 'X' FROM CK_T_RRKS RK01_TEL1 ") .append(" WHERE RK01_TEL1.TELNO = ? ") .append(" AND RK01_TEL1.SYSID = CUS1.SYSID ") .append(" AND RK01_TEL1.RRK_WAY_CD = '003' ") .append(" AND RK01_TEL1.RRKS_SBT_CD = '2' ") .append(" AND (RK01_TEL1.RRKS_NO,RK01_TEL1.RSV_APLY_YMD || RK01_TEL1.GENE_ADD_DTM) = (SELECT ") .append(" RK01_TEL2.RRKS_NO, ") .append(" MAX(RK01_TEL2.RSV_APLY_YMD || RK01_TEL2.GENE_ADD_DTM) AS RK01_TEL2_MAX ") .append(" FROM ") .append(" CK_T_RRKS RK01_TEL2 ") .append(" WHERE ") .append(" RK01_TEL2.RRKS_NO = RK01_TEL1.RRKS_NO AND ") .append(" RK01_TEL2.RSV_APLY_YMD <= ? AND ") .append(" RK01_TEL2.RSV_APLY_CD = '2' AND ") .append(" RK01_TEL2.RRK_WAY_CD = '003' AND ") .append(" RK01_TEL2.RRKS_SBT_CD = '2' AND ") .append(" RK01_TEL2.MK_FLG = '0' ") .append(" GROUP BY ") .append(" RK01_TEL2.RRKS_NO) ") .append(" ) ") .append(" ) "); replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(telNo); replaceList.add(opeDate); replaceList.add(telNo); replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(telNo); replaceList.add(opeDate); replaceList.add(telNo); replaceList.add(telNo); replaceList.add(telNo); replaceList.add(telNo); replaceList.add(opeDate); } // eoID 入力値が設定されている場合 if (!JCRUtilCommon.isNull(sqlEoid)) { sql_Buff.append(" AND ( ") .append(sqlEoid) .append(" ) "); for(int i = 0; i < eoidList.length ; i++) { replaceList.add(eoidList[i]); } } } // サービス契約番号、申込書番号、eoID、整理番号 入力値が設定されていない場合 if (JCRUtilCommon.isNull(svcKeiNo) && JCRUtilCommon.isNull(mskmshoNo) && JCRUtilCommon.isNull(sqlEoid) && JCRUtilCommon.isNull(seiriNo)) { proscstFlg = true; // お客様も対象の場合 if (custFlg) { sql_Buff.append(" UNION ALL "); } sql_Buff.append(" SELECT ") .append(" TKR2.TAIO_KIROK_NO, ") .append(" TKR2.TITLE, ") .append(" TKR2.SVC_KEI_NO, ") .append(" PCST.PROSCST_NM AS CUST_NM, ") .append(" NULL AS EOID, ") .append(" TKR2.UK_DTM, ") .append(" TKR2.TOIAWASE_STAT_CD, ") .append(" (SELECT ") .append(" CD01.CD_DIV_NM ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI CD01 ") .append(" WHERE ") .append(" CD01.CD_SBT_CD = 'CD00229' AND ") .append(" CD01.CD_DIV = TKR2.TOIAWASE_STAT_CD AND ") .append(" CD01.CD_TSTAYMD <= SUBSTR(TKR2.UPD_DTM, 1, 8) AND ") .append(" CD01.CD_TENDYMD >= SUBSTR(TKR2.UPD_DTM, 1, 8) AND ") .append(" CD01.MK_FLG = '0' ") .append(" ) ASTOIAWASE_STAT_NM, ") .append(" TKR2.TAIO_KIROK_STAT, ") .append(" (SELECT ") .append(" CD02.CD_DIV_NM ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI CD02 ") .append(" WHERE ") .append(" CD02.CD_SBT_CD = 'CD00230' AND ") .append(" CD02.CD_DIV = TKR2.TAIO_KIROK_STAT AND ") .append(" CD02.CD_TSTAYMD <= SUBSTR(TKR2.UPD_DTM, 1, 8) AND ") .append(" CD02.CD_TENDYMD >= SUBSTR(TKR2.UPD_DTM, 1,8) AND ") .append(" CD02.MK_FLG = '0' ") .append(" ) AS TAIO_KIROK_STAT_NM, ") .append(" TKR2.CALLBK_KIBO_YMD, ") .append(" TKR2.CALLBK_KIBO_TIME, ") .append(" TKR2.CALLBK_SHITEI_TYPE_CD, ") .append(" (SELECT ") .append(" CD03.CD_DIV_NM ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI CD03 ") .append(" WHERE ") .append(" CD03.CD_SBT_CD = 'CD00457' AND ") .append(" CD03.CD_DIV = TKR2.CALLBK_SHITEI_TYPE_CD AND ") .append(" CD03.CD_TSTAYMD <= SUBSTR(TKR2.UPD_DTM, 1, 8) AND ") .append(" CD03.CD_TENDYMD >= SUBSTR(TKR2.UPD_DTM, 1, 8) AND ") .append(" CD03.MK_FLG = '0' ") .append(" ) AS CALLBK_SHITEI_TYPE_CD_NM, ") .append(" NULL AS EOID_STATUS, ") .append(" TKR2.TAIO_STA_DTM ") .append(" FROM ") .append(" CR_T_TAIO_KIROK TKR2 ") .append(" INNER JOIN CK_T_PROSCST PCST ON ( ") .append(" PCST.PROSCST_NO = TKR2.PROSCST_NO AND ") .append(" PCST.MK_FLG = '0' ") .append(" ) ") .append(" WHERE ") .append(" TKR2.MK_FLG = '0' "); // 対応記録番号 入力値が設定されている場合 if (!JCRUtilCommon.isNull(taioKirokNo)) { sql_Buff.append(" AND TKR2.TAIO_KIROK_NO = ? "); replaceList.add(taioKirokNo); } // 問合せ客番号 入力値が設定されている場合 if (!JCRUtilCommon.isNull(proscstNo)) { sql_Buff.append(" AND TKR2.PROSCST_NO = ? "); replaceList.add(proscstNo); } // 電話番号 入力値が設定されている場合 if (!JCRUtilCommon.isNull(telNo)) { sql_Buff.append(" AND (PCST.PROSCST_TELNO = ? OR PCST.PROSCST_KTAI_TELNO = ? ) "); replaceList.add(telNo); replaceList.add(telNo); } } // SQL文_個別部1 if (JPCModelConstant.FUNC_CD_1.equals(inMsg.getString(ECR0011B030CBSMsg.FUNC_CODE))) { if (JCRStrConst.TOIAWASE_STAT_CD.equals(sortItem)) { // 問合せ状態コード 昇順 sql_Buff.append(" ORDER BY ") .append(" TOIAWASE_STAT_CD ") .append(" ,TAIO_KIROK_NO "); } if (JCRStrConst.KIROK_NO.equals(sortItem)) { // 対応記録番号 昇順 sql_Buff.append(" ORDER BY ") .append(" TAIO_KIROK_NO "); } if (JCRStrConst.SVC_NO.equals(sortItem)) { // サービス契約番号 昇順 sql_Buff.append(" ORDER BY ") .append(" SVC_KEI_NO ") .append(" ,TOIAWASE_STAT_CD ") .append(" ,TAIO_KIROK_NO "); } if (JCRStrConst.EOID.equals(sortItem)) { // eoid 昇順 sql_Buff.append(" ORDER BY ") .append(" eoID ") .append(" ,TOIAWASE_STAT_CD ") .append(" ,TAIO_KIROK_NO "); } } else if (JPCModelConstant.FUNC_CD_2.equals(inMsg.getString(ECR0011B030CBSMsg.FUNC_CODE))) { if (JCRStrConst.TOIAWASE_STAT_CD.equals(sortItem)) { // 問合せ状態コード 降順 sql_Buff.append(" ORDER BY ") .append(" TOIAWASE_STAT_CD DESC ") .append(" ,TAIO_KIROK_NO "); } if (JCRStrConst.KIROK_NO.equals(sortItem)) { // 対応記録番号 降順 sql_Buff.append(" ORDER BY ") .append(" TAIO_KIROK_NO DESC "); } if (JCRStrConst.SVC_NO.equals(sortItem)) { // サービス契約番号 降順 sql_Buff.append(" ORDER BY ") .append(" SVC_KEI_NO DESC ") .append(" ,TOIAWASE_STAT_CD ") .append(" ,TAIO_KIROK_NO "); } if (JCRStrConst.EOID.equals(sortItem)) { // eoid 降順 sql_Buff.append(" ORDER BY ") .append(" eoID DESC ") .append(" ,TOIAWASE_STAT_CD ") .append(" ,TAIO_KIROK_NO "); } } // お客様と問合せ客の条件が入っていた場合、検索を行わない if (!custFlg && !proscstFlg) { // 総検索件数の設定 inMsg.set(ECR0011B030CBSMsg.TOTAL_SEARCH_NUM, "0"); return; } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに 指定された値を設定 for (int idx = 0; idx < replaceList.size(); idx++ ) { CAANJDBCUtil.setParam(pstmt, idx+1, replaceList.get(idx)); } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // ECR0011B030CBSMsgの明細にセットする項目 String[] msgKeyList1 = { ECR0011B030CBSMsg1List.TAIO_KIROK_NO , ECR0011B030CBSMsg1List.TITLE , ECR0011B030CBSMsg1List.SVC_KEI_NO , ECR0011B030CBSMsg1List.CUST_NM , ECR0011B030CBSMsg1List.EOID , ECR0011B030CBSMsg1List.UK_DTM , ECR0011B030CBSMsg1List.TOIAWASE_STAT_CD , ECR0011B030CBSMsg1List.TOIAWASE_STAT_CD_NM , ECR0011B030CBSMsg1List.TAIO_KIROK_STAT , ECR0011B030CBSMsg1List.TAIO_KIROK_STAT_NM , ECR0011B030CBSMsg1List.CALLBK_KIBO_YMD , ECR0011B030CBSMsg1List.CALLBK_KIBO_TIME , ECR0011B030CBSMsg1List.CALLBK_SHITEI_TYPE_CD , ECR0011B030CBSMsg1List.CALLBK_SHITEI_TYPE_CD_NM , ECR0011B030CBSMsg1List.EOID_STATUS , ECR0011B030CBSMsg1List.TAIO_STA_DTM }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery, msgKeyList1, "eo.ejb.cbs.cbsmsg.ECR0011B030CBSMsg1List", 0); // inMsg(CBSMsg)に明細をセット inMsg.set("ECR0011B030CBSMsg1List", outMsg1); // 総検索件数の設定 inMsg.set(ECR0011B030CBSMsg.TOTAL_SEARCH_NUM, String.valueOf(outMsg1.length)); //*************************** 自動生成ソース修正 END ***************************// } catch(SQLException e) { inMsg.set(ECR0011B030CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } finally { // 資源の解放 try{ if(rsltQuery != null){ rsltQuery.close(); } if(pstmt != null){ pstmt.close(); } if(con1 != null){ closeConnection(con1); } }catch(SQLException e){ inMsg.set(ECR0011B030CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }