/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名：eo顧客基幹システム * モジュール名：JSYejbECN0021B001TPDA * ソースファイル名：JSYejbECN0021B001TPDA.java * 作成者：EK908988 * 日付：2011年12月13日 *＜機能概要＞ * SQLFacility * テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン修正日修正者修正内容 * ｖ1.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 eo.ejb.cbs.cbsmsg.ECN0021B001CBSMsg; import eo.ejb.cbs.cbsmsg.ECN0021B001CBSMsgList; import eo.ejb.cbm.entity.CN0021ETMsg; import eo.ejb.common.JCCModelCommon; /** * * テンプレートDBアクセス部品　ECN0021B001_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbECN0021B001TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbECN0021B001TPDA() { } /** * ECN0021B001_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void */ public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){ // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt = null; // リザルトセット ResultSet rsltQuery = null; /******************** * SQL文の作成 ********************/ try{ //コネクション取得 con1 = JSYejbConnection.getConnection(CN0021ETMsg.getTableName()); /* ++++++++++ v1.00.00 追加開始 ++++++++++ */ // SYSIDを分割する String sysidArray = (String)inMsg.getObject(ECN0021B001CBSMsg.KEY_SYSID); String[] sysidStr = sysidArray.split(","); /* ++++++++++ v1.00.00 追加終了 ++++++++++ */ // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT ") .append(" CNKEI.CONT_KEI_NO AS CONT_KEI_NO ") .append(" ,CNKEI.SYSID AS SYSID ") .append(" ,CK0011.EOID AS EOID ") .append(" ,CNKEI.CONT_SVC_CD AS CONT_SVC_CD ") .append(" ,CNSVC.CONT_SVC_NM AS CONT_SVC_NM ") .append(" ,CNKEI.CONT_CD AS CONT_CD ") .append(" ,CNKEI.CONT_OUT_CD AS CONT_OUT_CD ") .append(" ,CNKEI.CONT_NM AS CONT_NM ") .append(" ,CNKEI.CONT_SBT_CD AS CONT_SBT_CD ") .append(" ,CNKEI.CONT_KEI_STA_DTM AS CONT_KEI_STA_DTM ") .append(" ,CNKEI.CONT_KEI_END_DTM AS CONT_KEI_END_DTM ") .append(" ,CNKEI.CONT_PAYWAY_CD AS CONT_PAYWAY_CD ") .append(" ,(SELECT ZM0171_01.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_01 ") .append(" WHERE ZM0171_01.CD_SBT_CD = 'CD00828' ") .append(" AND ZM0171_01.CD_DIV = CNKEI.CONT_PAYWAY_CD ") .append(" AND ZM0171_01.MK_FLG = '0' ") .append(" ) AS CONT_PAYWAY_NM ") .append(" ,CNKEI.CRECARD_ID AS CRECARD_ID ") .append(" ,CRECA03.CRECARD_NO AS CRECARD_NO ") .append(" ,CNKEI.ISP_KSSAI_SVC_KEI_NO AS ISP_KSSAI_SVC_KEI_NO ") .append(" ,CNKEI.SUPPLIER AS SUPPLIER ") .append(" ,CNKEI.CONT_KEI_STAT_CD AS CONT_KEI_STAT_CD ") .append(" ,CASE ") .append(" WHEN CONT_KEI_STAT_CD = '0' AND (CONT_KEI_END_DTM IS NULL OR CONT_KEI_END_DTM >= SUBSTR(?, 1, 14)) THEN ") .append(" '契約中' ") .append(" WHEN CONT_KEI_STAT_CD = '0' AND CONT_KEI_END_DTM < SUBSTR(?, 1, 14) THEN ") .append(" '契約終了' ") .append(" ELSE ") .append(" KANRI01.CD_DIV_NM ") .append(" END CONT_KEI_STAT_NM ") .append(" ,CNKEI.TAXAREA AS TAXAREA ") .append(" ,CNKEI.CHOICETICKT_USE_PSB_CNT AS CHOICETICKT_USE_PSB_CNT ") .append(" ,CNKEI.CONT_KEI_KEIZOKU_CNT AS CONT_KEI_KEIZOKU_CNT ") .append(" ,CNKEI.CONT_BUY_CNT AS CONT_BUY_CNT ") .append(" ,CNKEI.CONT_TANKA AS CONT_TANKA ") .append(" ,CNKEI.CONT_KEI_SBT_CD AS CONT_KEI_SBT_CD ") .append(" ,CNKEI.KEI_PSBSU AS KEI_PSBSU ") .append(" ,CNKEI.HIKEIZOKU_FLG AS HIKEIZOKU_FLG ") .append(" ,CNKEI.CONT_KEI_YRYO_MRYO_FLG AS CONT_KEI_YRYO_MRYO_FLG ") .append(" ,CNKEI.CHRG_FORM_CD AS CHRG_FORM_CD ") .append(" ,CNKEI.OLD_LIMIT AS OLD_LIMIT ") .append(" ,CNKEI.USE_PSB_USER_SBT_CD AS USE_PSB_USER_SBT_CD ") .append(" ,CNKEI.RNKSAKI_CD AS RNKSAKI_CD ") .append(" ,CNKEI.RNKSAKI_CONT_SKBT_ID AS RNKSAKI_CONT_SKBT_ID ") .append(" ,CNKEI.CONT_SONZAI_FLG AS CONT_SONZAI_FLG ") .append(" ,CNKEI.KEI_JI_SYSID AS KEI_JI_SYSID ") .append(" ,CNKEI.KEI_JI_MMB_SBT_CD AS KEI_JI_MMB_SBT_CD ") .append(" ,CNKEI.DSL_JI_MMB_SBT_CD AS DSL_JI_MMB_SBT_CD ") .append(" ,CK0011.MEMBER_SBT_CD AS MEMBER_SBT_CD ") .append(" ,(SELECT ZM0171_01.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_01 ") .append(" WHERE ZM0171_01.CD_SBT_CD = 'CD00039' ") .append(" AND ZM0171_01.CD_DIV = CK0011.MEMBER_SBT_CD ") .append(" AND ZM0171_01.MK_FLG = '0' ") .append(" ) AS MEMBER_SBT_NM ") .append(" ,CNKEI.KEI_TRAN_YMD AS KEI_TRAN_YMD ") .append(" FROM ") .append(" CN_T_CONT_KEI CNKEI ") .append(" LEFT OUTER JOIN CN_M_CONT_SVC CNSVC ON CNKEI.CONT_SVC_CD = CNSVC.CONT_SVC_CD ") .append(" LEFT OUTER JOIN ZM_M_CD_NM_KANRI KANRI01 ") .append(" ON (KANRI01.CD_DIV = CNKEI.CONT_KEI_STAT_CD AND ") .append(" KANRI01.CD_SBT_CD = 'CD00516' AND ") .append(" KANRI01.MK_FLG = '0') ") .append(" LEFT OUTER JOIN ( ") .append(" SELECT ") .append(" COMP01.CRECARD_COMP_ALI, ") .append(" COMP01.CRECARD_COMP_CD, ") .append(" CRECA04.CRECARD_NO, ") .append(" CRECA04.CRECARD_ID ") .append(" FROM ") .append(" KK_M_CRECARD_COMP COMP01, ") .append(" ( ") .append(" SELECT ") .append(" CRECA02.CRECARD_NO AS CRECARD_NO, ") .append(" CRECA02.CRECARD_ID AS CRECARD_ID, ") .append(" CRECA02.CRECARD_JIGYO_CD AS CRECARD_JIGYO_CD, ") .append(" CRECA02.CRECARD_COMP_CD AS CRECARD_COMP_CD, ") .append(" CRECA02.CHRG_TRGT_YM AS CHRG_TRGT_YM ") .append(" FROM CN_T_CONTJIGYO_CRECA CRECA02 ") .append(" INNER JOIN ( ") .append(" SELECT ") .append(" CRECA01.CRECARD_ID AS CRECARD_ID, ") .append(" MAX(CRECA01.CHRG_TRGT_YM) AS CHRG_TRGT_YM ") .append(" FROM ") .append(" CN_T_CONTJIGYO_CRECA CRECA01 ") .append(" WHERE ") .append(" CRECA01.CHRG_TRGT_YM <= SUBSTR(?, 1, 6) ") .append(" GROUP BY ") .append(" CRECARD_ID) CRECA ") .append(" ON CRECA.CRECARD_ID = CRECA02.CRECARD_ID AND CRECA02.CHRG_TRGT_YM = CRECA.CHRG_TRGT_YM ") .append(" ) CRECA04 ") .append(" WHERE ") .append(" COMP01.CRECARD_COMP_CD = CRECA04.CRECARD_COMP_CD AND ") .append(" COMP01.CRECARD_JIGYO_CD = CRECA04.CRECARD_JIGYO_CD ") .append(" ) CRECA03 ") .append(" ON CRECA03.CRECARD_ID = CNKEI.CRECARD_ID ") .append(" LEFT OUTER JOIN ( ") .append(" SELECT ") .append(" CKCUST.SYSID ") .append(" ,CKCUST.EOID ") .append(" ,CKCUST.MEMBER_SBT_CD ") .append(" FROM ") .append(" CK_T_CUST CKCUST ") .append(" WHERE ") .append(" CKCUST.SYSID IN("); for(int iCnt = 0 ; iCnt < sysidStr.length ; iCnt++) { if(iCnt == 0) { sql_Buff.append(sysidStr[iCnt]); } else { sql_Buff.append(", " + sysidStr[iCnt]); } } sql_Buff.append(") AND ") .append(" CKCUST.RSV_APLY_YMD || CKCUST.GENE_ADD_DTM = ( ") .append(" SELECT MAX(CKCUST_GENE.RSV_APLY_YMD || CKCUST_GENE.GENE_ADD_DTM) AS CKCUST_MAX ") .append(" FROM ") .append(" CK_T_CUST CKCUST_GENE ") .append(" WHERE ") .append(" CKCUST_GENE.SYSID = CKCUST.SYSID AND ") .append(" CKCUST_GENE.RSV_APLY_YMD <= SUBSTR(?, 1, 8) AND ") .append(" CKCUST_GENE.RSV_APLY_CD = '2' AND ") .append(" CKCUST_GENE.MK_FLG = '0' ) ") .append(" ) CK0011 ") .append(" ON CNKEI.SYSID = CK0011.SYSID "); // SQL文_個別部1 if ( "1".equals( inMsg.getObject(ECN0021B001CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" WHERE ") .append(" CNKEI.SYSID IN("); for(int iCnt = 0 ; iCnt < sysidStr.length ; iCnt++) { if(iCnt == 0) { sql_Buff.append(sysidStr[iCnt]); } else { sql_Buff.append(", " + sysidStr[iCnt]); } } sql_Buff.append(") AND ") .append(" CNKEI.MK_FLG='0' AND ") .append(" CNSVC.MK_FLG='0' ") .append(" ORDER BY ") .append(" CONT_SVC_CD, ") .append(" CONT_CD, ") .append(" CONT_KEI_STAT_CD "); } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに指定された値を設定 if ( "1".equals( inMsg.getString(ECN0021B001CBSMsg.FUNC_CODE) ) ) { /* ++++++++++ v1.00.00 追加開始 ++++++++++ */ // パラメータの設定(ＫＥＹ＿システム年月日時分秒を指定) CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(ECN0021B001CBSMsg.KEY_SYS_DTM)); // パラメータの設定(ＫＥＹ＿システム年月日時分秒を指定) CAANJDBCUtil.setParam(pstmt, 2, inMsg.getObject(ECN0021B001CBSMsg.KEY_SYS_DTM)); // パラメータの設定(ＫＥＹ＿システム年月日時分秒を指定) CAANJDBCUtil.setParam(pstmt, 3, inMsg.getObject(ECN0021B001CBSMsg.KEY_SYS_DTM)); // パラメータの設定(ＫＥＹ＿予約適用年月日を指定) CAANJDBCUtil.setParam(pstmt, 4, inMsg.getObject(ECN0021B001CBSMsg.KEY_RSV_APLY_YMD)); /* ++++++++++ v1.00.00 追加終了 ++++++++++ */ } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // ECN0021B001CBSMsgの明細にセットする項目 String[] msgKeyList1 = { ECN0021B001CBSMsgList.CONT_KEI_NO , ECN0021B001CBSMsgList.SYSID , ECN0021B001CBSMsgList.EOID , ECN0021B001CBSMsgList.CONT_SVC_CD , ECN0021B001CBSMsgList.CONT_SVC_NM , ECN0021B001CBSMsgList.CONT_CD , ECN0021B001CBSMsgList.CONT_OUT_CD , ECN0021B001CBSMsgList.CONT_NM , ECN0021B001CBSMsgList.CONT_SBT_CD , ECN0021B001CBSMsgList.CONT_KEI_STA_DTM , ECN0021B001CBSMsgList.CONT_KEI_END_DTM , ECN0021B001CBSMsgList.CONT_PAYWAY_CD , ECN0021B001CBSMsgList.CONT_PAYWAY_NM , ECN0021B001CBSMsgList.CRECARD_ID , ECN0021B001CBSMsgList.CRECARD_NO , ECN0021B001CBSMsgList.ISP_KSSAI_SVC_KEI_NO , ECN0021B001CBSMsgList.SUPPLIER , ECN0021B001CBSMsgList.CONT_KEI_STAT_CD , ECN0021B001CBSMsgList.CONT_KEI_STAT_NM , ECN0021B001CBSMsgList.TAXAREA , ECN0021B001CBSMsgList.CHOICETICKT_USE_PSB_CNT , ECN0021B001CBSMsgList.CONT_KEI_KEIZOKU_CNT , ECN0021B001CBSMsgList.CONT_BUY_CNT , ECN0021B001CBSMsgList.CONT_TANKA , ECN0021B001CBSMsgList.CONT_KEI_SBT_CD , ECN0021B001CBSMsgList.KEI_PSBSU , ECN0021B001CBSMsgList.HIKEIZOKU_FLG , ECN0021B001CBSMsgList.CONT_KEI_YRYO_MRYO_FLG , ECN0021B001CBSMsgList.CHRG_FORM_CD , ECN0021B001CBSMsgList.OLD_LIMIT , ECN0021B001CBSMsgList.USE_PSB_USER_SBT_CD , ECN0021B001CBSMsgList.RNKSAKI_CD , ECN0021B001CBSMsgList.RNKSAKI_CONT_SKBT_ID , ECN0021B001CBSMsgList.CONT_SONZAI_FLG , ECN0021B001CBSMsgList.KEI_JI_SYSID , ECN0021B001CBSMsgList.KEI_JI_MMB_SBT_CD , ECN0021B001CBSMsgList.DSL_JI_MMB_SBT_CD , ECN0021B001CBSMsgList.MEMBER_SBT_CD , ECN0021B001CBSMsgList.MEMBER_SBT_NM , ECN0021B001CBSMsgList.KEI_TRAN_YMD }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.ECN0021B001CBSMsgList",0); // クレジットカード番号を復号化 for (int i = 0; i < outMsg1.length; i++) { if (null != outMsg1[i].getString(ECN0021B001CBSMsgList.CRECARD_NO) && !outMsg1[i].getString(ECN0021B001CBSMsgList.CRECARD_NO).isEmpty()) { outMsg1[i].set(ECN0021B001CBSMsgList.CRECARD_NO, JCCModelCommon.getDecryptResult(inMsg, inContext, outMsg1[i].getString(ECN0021B001CBSMsgList.CRECARD_NO))); } } // inMsg(CBSMsg)に明細をセット inMsg.set("ECN0021B001CBSMsgList", outMsg1); } catch(SQLException e) { inMsg.set(ECN0021B001CBSMsg.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(ECN0021B001CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }