/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名 ：eo顧客基幹システム * モジュール名 ：JSYejbECK0201B504TPDA * ソースファイル名：JSYejbECK0201B504TPDA.java * 作成者 ：EK909998 * 日付 ：2012年06月12日 *＜機能概要＞ * 問合せ対応メールアドレス（既加入者）一覧照会テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン 修正日 修正者 修正内容 * ｖ1.00.00 * v29.00.00 2017/03/09 FJ)笠川 【ANK-2928-00-00】お客さま申告アドレスの複数登録 * ********************************************************************************/ package eo.ejb.cbs.sqlf; import com.fujitsu.futurity.model.base.CAANMsg; import com.fujitsu.futurity.model.base.CAANSQLFacility; import com.fujitsu.futurity.model.base.CAANRuntimeException; import com.fujitsu.futurity.model.base.CAANJDBCUtil; 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.TemplateDBAccessHandler; import com.fujitsu.futurity.model.ejb.common.fw.AgentDispatchContext; 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 eo.common.constant.JPCModelConstant; import eo.ejb.cbs.cbsmsg.ECK0201B504CBSMsg; import eo.ejb.cbs.cbsmsg.ECK0201B504CBSMsg1List; import eo.ejb.cbm.entity.CK0201ETMsg; import eo.ejb.common.JCCModelCommon; /** * * テンプレートDBアクセス部品　ECK0201B504_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbECK0201B504TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbECK0201B504TPDA() { } /** * ECK0201B504_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void */ public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){ // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt = null; // リザルトセット ResultSet rsltQuery = null; //パラメータ設定リスト List replaceList = new ArrayList(); /******************** * SQL文の作成 ********************/ try{ //コネクション取得 con1 = JSYejbConnection.getConnection(CK0201ETMsg.getTableName()); //運用日付取得 String opeDate = JCCModelCommon.getOpeDate(inMsg, null); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT MLAD_TBL.MLAD AS MLAD ") .append(" FROM (SELECT TO_CHAR(1) AS SEQ_NO, ") .append(" RRKS01.MLAD AS MLAD ") .append(" FROM CK_T_RRKS RRKS01, ") .append(" KK_T_SVC_KEI SVKE01 ") .append(" WHERE SVKE01.SVC_KEI_NO = ? ") .append(" AND SVKE01.PRC_GRP_CD <> '99' ") .append(" AND (SVKE01.SVC_KEI_NO,SVKE01.RSV_APLY_YMD || SVKE01.GENE_ADD_DTM) = ") .append(" (SELECT SVKE01_SUB.SVC_KEI_NO, ") .append(" MAX(SVKE01_SUB.RSV_APLY_YMD || SVKE01_SUB.GENE_ADD_DTM) ") .append(" FROM KK_T_SVC_KEI SVKE01_SUB ") .append(" WHERE SVKE01_SUB.SVC_KEI_NO = SVKE01.SVC_KEI_NO ") .append(" AND SVKE01_SUB.RSV_APLY_YMD <= ? ") .append(" AND SVKE01_SUB.RSV_APLY_CD = '2' ") .append(" AND SVKE01_SUB.MK_FLG = '0' ") .append(" GROUP BY SVKE01_SUB.SVC_KEI_NO ") .append(" ) ") .append(" AND RRKS01.SYSID = SVKE01.SYSID ") .append(" AND RRKS01.RRK_WAY_CD = '001' ") .append(" AND (RRKS01.RRKS_NO,RRKS01.RSV_APLY_YMD || RRKS01.GENE_ADD_DTM) = ") .append(" (SELECT RRKS01_SUB.RRKS_NO, ") .append(" MAX(RRKS01_SUB.RSV_APLY_YMD || RRKS01_SUB.GENE_ADD_DTM) ") .append(" FROM CK_T_RRKS RRKS01_SUB ") .append(" WHERE RRKS01_SUB.RRKS_NO = RRKS01.RRKS_NO ") .append(" AND RRKS01_SUB.RRK_WAY_CD = '001' ") .append(" AND RRKS01_SUB.RSV_APLY_YMD <= ? ") .append(" AND RRKS01_SUB.RSV_APLY_CD = '2' ") .append(" AND RRKS01_SUB.MK_FLG = '0' ") .append(" GROUP BY RRKS01_SUB.RRKS_NO ") .append(" ) ") .append(" UNION ALL "); // パラメータ追加(ＫＥＹ＿サービス契約番号を指定) replaceList.add(inMsg.getObject(ECK0201B504CBSMsg.KEY_SVC_KEI_NO)); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); sql_Buff.append(" SELECT TO_CHAR(2) AS SEQ_NO, ") .append(" RRKS02.MLAD AS MLAD ") .append(" FROM CK_T_RRKS RRKS02, ") .append(" KK_T_SVC_KEI SVKE02 ") .append(" WHERE SVKE02.SVC_KEI_NO = ? ") .append(" AND SVKE02.PRC_GRP_CD <> '99' ") .append(" AND (SVKE02.SVC_KEI_NO,SVKE02.RSV_APLY_YMD || SVKE02.GENE_ADD_DTM) = ") .append(" (SELECT SVKE02_SUB.SVC_KEI_NO,") .append(" MAX(SVKE02_SUB.RSV_APLY_YMD || SVKE02_SUB.GENE_ADD_DTM) ") .append(" FROM KK_T_SVC_KEI SVKE02_SUB ") .append(" WHERE SVKE02_SUB.SVC_KEI_NO = SVKE02.SVC_KEI_NO ") .append(" AND SVKE02_SUB.RSV_APLY_YMD <= ? ") .append(" AND SVKE02_SUB.RSV_APLY_CD = '2' ") .append(" AND SVKE02_SUB.MK_FLG = '0' ") .append(" GROUP BY SVKE02_SUB.SVC_KEI_NO ") .append(" ) ") .append(" AND RRKS02.SYSID = SVKE02.SYSID ") .append(" AND RRKS02.RRK_WAY_CD = '002' ") .append(" AND (RRKS02.RRKS_NO,RRKS02.RSV_APLY_YMD || RRKS02.GENE_ADD_DTM) = ") .append(" (SELECT RRKS02_SUB.RRKS_NO, ") .append(" MAX(RRKS02_SUB.RSV_APLY_YMD || RRKS02_SUB.GENE_ADD_DTM) ") .append(" FROM CK_T_RRKS RRKS02_SUB ") .append(" WHERE RRKS02_SUB.RRKS_NO = RRKS02.RRKS_NO ") .append(" AND RRKS02_SUB.RRK_WAY_CD = '002' ") .append(" AND RRKS02_SUB.RSV_APLY_YMD <= ? ") .append(" AND RRKS02_SUB.RSV_APLY_CD = '2' ") .append(" AND RRKS02_SUB.MK_FLG = '0' ") .append(" GROUP BY RRKS02_SUB.RRKS_NO ") .append(" ) ") .append(" UNION ALL "); // パラメータ追加(ＫＥＹ＿サービス契約番号を指定) replaceList.add(inMsg.getObject(ECK0201B504CBSMsg.KEY_SVC_KEI_NO)); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); // 20170309 ANK-2928-00-00 ADD START sql_Buff.append(" SELECT TO_CHAR(3) AS SEQ_NO, ") .append(" RRKS04.MLAD AS MLAD ") .append(" FROM CK_T_RRKS RRKS04, ") .append(" KK_T_SVC_KEI SVKE04 ") .append(" WHERE SVKE04.SVC_KEI_NO = ? ") .append(" AND SVKE04.PRC_GRP_CD <> '99' ") .append(" AND (SVKE04.SVC_KEI_NO,SVKE04.RSV_APLY_YMD || SVKE04.GENE_ADD_DTM) = ") .append(" (SELECT SVKE04_SUB.SVC_KEI_NO,") .append(" MAX(SVKE04_SUB.RSV_APLY_YMD || SVKE04_SUB.GENE_ADD_DTM) ") .append(" FROM KK_T_SVC_KEI SVKE04_SUB ") .append(" WHERE SVKE04_SUB.SVC_KEI_NO = SVKE04.SVC_KEI_NO ") .append(" AND SVKE04_SUB.RSV_APLY_YMD <= ? ") .append(" AND SVKE04_SUB.RSV_APLY_CD = '2' ") .append(" AND SVKE04_SUB.MK_FLG = '0' ") .append(" GROUP BY SVKE04_SUB.SVC_KEI_NO ") .append(" ) ") .append(" AND RRKS04.SYSID = SVKE04.SYSID ") .append(" AND RRKS04.RRK_WAY_CD = '006' ") .append(" AND (RRKS04.RRKS_NO,RRKS04.RSV_APLY_YMD || RRKS04.GENE_ADD_DTM) = ") .append(" (SELECT RRKS04_SUB.RRKS_NO, ") .append(" MAX(RRKS04_SUB.RSV_APLY_YMD || RRKS04_SUB.GENE_ADD_DTM) ") .append(" FROM CK_T_RRKS RRKS04_SUB ") .append(" WHERE RRKS04_SUB.RRKS_NO = RRKS04.RRKS_NO ") .append(" AND RRKS04_SUB.RRK_WAY_CD = '006' ") .append(" AND RRKS04_SUB.RSV_APLY_YMD <= ? ") .append(" AND RRKS04_SUB.RSV_APLY_CD = '2' ") .append(" AND RRKS04_SUB.MK_FLG = '0' ") .append(" GROUP BY RRKS04_SUB.RRKS_NO ") .append(" ) ") .append(" UNION ALL "); // パラメータ追加(ＫＥＹ＿サービス契約番号を指定) replaceList.add(inMsg.getObject(ECK0201B504CBSMsg.KEY_SVC_KEI_NO)); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); sql_Buff.append(" SELECT TO_CHAR(4) AS SEQ_NO, ") .append(" RRKS05.MLAD AS MLAD ") .append(" FROM CK_T_RRKS RRKS05, ") .append(" KK_T_SVC_KEI SVKE05 ") .append(" WHERE SVKE05.SVC_KEI_NO = ? ") .append(" AND SVKE05.PRC_GRP_CD <> '99' ") .append(" AND (SVKE05.SVC_KEI_NO,SVKE05.RSV_APLY_YMD || SVKE05.GENE_ADD_DTM) = ") .append(" (SELECT SVKE05_SUB.SVC_KEI_NO,") .append(" MAX(SVKE05_SUB.RSV_APLY_YMD || SVKE05_SUB.GENE_ADD_DTM) ") .append(" FROM KK_T_SVC_KEI SVKE05_SUB ") .append(" WHERE SVKE05_SUB.SVC_KEI_NO = SVKE05.SVC_KEI_NO ") .append(" AND SVKE05_SUB.RSV_APLY_YMD <= ? ") .append(" AND SVKE05_SUB.RSV_APLY_CD = '2' ") .append(" AND SVKE05_SUB.MK_FLG = '0' ") .append(" GROUP BY SVKE05_SUB.SVC_KEI_NO ") .append(" ) ") .append(" AND RRKS05.SYSID = SVKE05.SYSID ") .append(" AND RRKS05.RRK_WAY_CD = '007' ") .append(" AND (RRKS05.RRKS_NO,RRKS05.RSV_APLY_YMD || RRKS05.GENE_ADD_DTM) = ") .append(" (SELECT RRKS05_SUB.RRKS_NO, ") .append(" MAX(RRKS05_SUB.RSV_APLY_YMD || RRKS05_SUB.GENE_ADD_DTM) ") .append(" FROM CK_T_RRKS RRKS05_SUB ") .append(" WHERE RRKS05_SUB.RRKS_NO = RRKS05.RRKS_NO ") .append(" AND RRKS05_SUB.RRK_WAY_CD = '007' ") .append(" AND RRKS05_SUB.RSV_APLY_YMD <= ? ") .append(" AND RRKS05_SUB.RSV_APLY_CD = '2' ") .append(" AND RRKS05_SUB.MK_FLG = '0' ") .append(" GROUP BY RRKS05_SUB.RRKS_NO ") .append(" ) ") .append(" UNION ALL "); // パラメータ追加(ＫＥＹ＿サービス契約番号を指定) replaceList.add(inMsg.getObject(ECK0201B504CBSMsg.KEY_SVC_KEI_NO)); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); // 20170309 ANK-2928-00-00 ADD END // 20170309 ANK-2928-00-00 MOD START // sql_Buff.append(" SELECT TO_CHAR(3) AS SEQ_NO, ") sql_Buff.append(" SELECT TO_CHAR(4) AS SEQ_NO, ") // 20170309 ANK-2928-00-00 MOD END .append(" OISP03.MLAD AS MLAD ") .append(" FROM KK_T_OPSVKEI_ISP OISP03, ") .append(" KK_T_OP_SVC_KEI OSVC03, ") .append(" KK_T_SVC_KEI SVKE03 ") .append(" WHERE SVKE03.SVC_KEI_NO = ? ") .append(" AND SVKE03.PRC_GRP_CD <> '99' ") .append(" AND (SVKE03.SVC_KEI_NO,SVKE03.RSV_APLY_YMD || SVKE03.GENE_ADD_DTM) = ") .append(" (SELECT SVKE03_SUB.SVC_KEI_NO, ") .append(" MAX(SVKE03_SUB.RSV_APLY_YMD || SVKE03_SUB.GENE_ADD_DTM) ") .append(" FROM KK_T_SVC_KEI SVKE03_SUB ") .append(" WHERE SVKE03_SUB.SVC_KEI_NO = SVKE03.SVC_KEI_NO ") .append(" AND SVKE03_SUB.RSV_APLY_YMD <= ? ") .append(" AND SVKE03_SUB.RSV_APLY_CD = '2' ") .append(" AND SVKE03_SUB.MK_FLG = '0' ") .append(" GROUP BY SVKE03_SUB.SVC_KEI_NO ") .append(" ) ") .append(" AND OSVC03.SYSID = SVKE03.SYSID ") .append(" AND (OSVC03.OP_SVC_KEI_NO,OSVC03.RSV_APLY_YMD || OSVC03.GENE_ADD_DTM) = ") .append(" (SELECT OSVC03_SUB.OP_SVC_KEI_NO, ") .append(" MAX(OSVC03_SUB.RSV_APLY_YMD || OSVC03_SUB.GENE_ADD_DTM) ") .append(" FROM KK_T_OP_SVC_KEI OSVC03_SUB ") .append(" WHERE OSVC03_SUB.OP_SVC_KEI_NO = OSVC03.OP_SVC_KEI_NO ") .append(" AND OSVC03_SUB.RSV_APLY_YMD <= ? ") .append(" AND OSVC03_SUB.RSV_APLY_CD = '2' ") .append(" AND OSVC03_SUB.MK_FLG = '0' ") .append(" GROUP BY OSVC03_SUB.OP_SVC_KEI_NO ") .append(" ) ") .append(" AND OISP03.OP_SVC_KEI_NO = OSVC03.OP_SVC_KEI_NO ") .append(" AND OISP03.GENE_ADD_DTM = OSVC03.GENE_ADD_DTM ") .append(" AND OISP03.MLAD IS NOT NULL ") .append(" ) MLAD_TBL "); // パラメータ追加(ＫＥＹ＿サービス契約番号を指定) replaceList.add(inMsg.getObject(ECK0201B504CBSMsg.KEY_SVC_KEI_NO)); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); // SQL文_個別部1 if ( "1".equals( inMsg.getObject(ECK0201B504CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" WHERE MLAD_TBL.MLAD IS NOT NULL ") .append(" GROUP BY MLAD ") .append(" ORDER BY MIN(SEQ_NO) "); } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); //-------------------------------------------------------------------------------- 修正 Start --------// // PreparedStatementの指定されたパラメータカラムに 指定された値を設定 for (int idx = 0 ; idx < replaceList.size() ; idx++) { CAANJDBCUtil.setParam(pstmt, idx + 1, replaceList.get(idx)); } //-------------------------------------------------------------------------------- 修正 End ----------// // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // ECK0201B504CBSMsgの明細にセットする項目 String[] msgKeyList1 = { ECK0201B504CBSMsg1List.MLAD }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.ECK0201B504CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("ECK0201B504CBSMsg1List", outMsg1); // ▼▼▼ ADD START // 検索エラーフラグの設定 if (outMsg1.length == 0) { inMsg.set(ECK0201B504CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_ZERO); } // 総検索件数の設定 inMsg.set(ECK0201B504CBSMsg.TOTAL_SEARCH_NUM, String.valueOf(outMsg1.length)); // ▲▲▲ ADD END } catch(SQLException e) { inMsg.set(ECK0201B504CBSMsg.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(ECK0201B504CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }