/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名：eo顧客基幹システム * モジュール名：JSYejbEKK0451B011TPDA * ソースファイル名：JSYejbEKK0451B011TPDA.java * 作成者：EK911025 * 日付：2013年07月06日 *＜機能概要＞ * 割引サービス契約／データ抽出項目一覧照会（SYSID）テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン修正日修正者修正内容 * ｖ1.00.00 * v5.00.00 2013/07/11 FJ)寺園 IT1-2013-0001353 * v9.00.00 2014/04/17 FJ)小島 ANK-2056-00-00 * v11.00.00 2014/10/10 FJ)寺園 ANK-2305-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.EKK0451B011CBSMsg; import eo.ejb.cbs.cbsmsg.EKK0451B011CBSMsg1List; import eo.ejb.cbm.entity.KK0451ETMsg; import eo.ejb.common.JKKModelCommon; /** * * テンプレートDBアクセス部品　EKK0451B011_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEKK0451B011TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbEKK0451B011TPDA() { } /** * EKK0451B011_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(KK0451ETMsg.getTableName()); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT ") .append(" WRIB_DCHS_HAMBET_CD ") .append(" , WRIBSVK_DCHSKMST_NO ") .append(" , WRIBSVK_DCHSKMST_STAT ") .append(" , WRIBSVK_DCHSKMST_STAT_NM ") .append(" , WRIB_DCHS_TYPE_CD ") .append(" , WRIB_DCHS_TYPE_CD_NM ") .append(" , DSP_CAMPAIGN_DCHSKM_CD ") .append(" , WRISVC_DCHSKM_CD ") .append(" , WRISVC_DCHSKM_NM ") .append(" , MSKM_YMD ") .append(" , SVC_CHRG_DCHSKMST_STAYMD ") .append(" , SVC_CHRG_DCHSKMST_ENDYMD ") .append(" , APLY_JUN_NM ") .append(" FROM ( ") .append(" SELECT '1' AS WRIB_DCHS_HAMBET_CD ") .append(" , KK0451.WRIB_SVC_KEI_NO AS WRIBSVK_DCHSKMST_NO ") .append(" , KK0451.WRIB_SVC_KEI_STAT AS WRIBSVK_DCHSKMST_STAT ") .append(" ,(SELECT ZM0171_01.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_01 ") .append(" WHERE ZM0171_01.CD_SBT_CD = 'CD00055' ") .append(" AND ZM0171_01.CD_DIV = KK0451.WRIB_SVC_KEI_STAT ") .append(" AND ZM0171_01.MK_FLG = '0' ") .append(" ) AS WRIBSVK_DCHSKMST_STAT_NM ") .append(" , KK0841.WRIB_TYPE_CD AS WRIB_DCHS_TYPE_CD ") .append(" ,(SELECT ZM0171_02.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_02 ") .append(" WHERE ZM0171_02.CD_SBT_CD = 'CD00284' ") .append(" AND ZM0171_02.CD_DIV = KK0841.WRIB_TYPE_CD ") .append(" AND ZM0171_02.MK_FLG = '0' ") .append(" ) AS WRIB_DCHS_TYPE_CD_NM ") .append(" , KK0841.DSP_CAMPAIGN_CD AS DSP_CAMPAIGN_DCHSKM_CD ") .append(" , KK0451.WRIB_SVC_CD AS WRISVC_DCHSKM_CD ") .append(" , KK0841.WRIB_SVC_NM AS WRISVC_DCHSKM_NM ") .append(" , DECODE(KK0841.WRIB_TYPE_CD, '10', NULL, KK0021.MSKM_YMD) AS MSKM_YMD ") .append(" , KK0451.SVC_CHRG_STAYMD AS SVC_CHRG_DCHSKMST_STAYMD ") .append(" , KK0451.SVC_CHRG_ENDYMD AS SVC_CHRG_DCHSKMST_ENDYMD ") .append(" ,(SELECT ZM0171_03.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_03 ") .append(" WHERE ZM0171_03.CD_SBT_CD = 'CD00403' ") .append(" AND ZM0171_03.CD_DIV = KK0451.APLY_JUN ") .append(" AND ZM0171_03.MK_FLG = '0' ") .append(" ) AS APLY_JUN_NM ") .append(" , KK0451.WRIB_ADD_OPTY_CD AS WRIB_DCHSKMST_ADD_OPTY_CD ") .append(" , KK0841.DSP_JUN AS DSP_JUN ") .append(" , KK0841.WRIB_SCREEN_CTRL_CD AS WRIB_SCREEN_CTRL_CD ") .append(" FROM KK_T_WRIB_SVC_KEI KK0451 ") .append(" INNER JOIN KK_T_WRIB_SVC_KEI KK0451_01 ") .append(" ON KK0451_01.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO ") .append(" INNER JOIN KK_T_MSKM_DTL KK0021 ") .append(" ON KK0021.MSKM_DTL_NO = KK0451_01.MSKM_DTL_NO ") .append(" INNER JOIN KK_M_WRIB_SVC KK0841 ") .append(" ON KK0841.WRIB_SVC_CD = KK0451.WRIB_SVC_CD ") .append(" WHERE KK0451.SYSID = ? ") .append(" AND (KK0451.WRIB_SVC_KEI_NO, KK0451.RSV_APLY_YMD || KK0451.GENE_ADD_DTM) = ") .append(" (SELECT KK0451_GENE.WRIB_SVC_KEI_NO, MAX(KK0451_GENE.RSV_APLY_YMD || KK0451_GENE.GENE_ADD_DTM) AS KK0451_MAX ") .append(" FROM KK_T_WRIB_SVC_KEI KK0451_GENE ") .append(" WHERE KK0451_GENE.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO ") .append(" AND KK0451_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0451_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0451_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0451_GENE.WRIB_SVC_KEI_NO) ") .append(" AND (KK0841.WRIB_SVC_CD, KK0841.RSV_APLY_YMD || KK0841.GENE_ADD_DTM) = ") .append(" (SELECT KK0841_GENE.WRIB_SVC_CD, MAX(KK0841_GENE.RSV_APLY_YMD || KK0841_GENE.GENE_ADD_DTM) AS KK0841_MAX ") .append(" FROM KK_M_WRIB_SVC KK0841_GENE ") .append(" WHERE KK0841_GENE.WRIB_SVC_CD = KK0841.WRIB_SVC_CD ") .append(" AND KK0841_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0841_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0841_GENE.WRIB_SVC_CD) ") .append(" AND (KK0451_01.WRIB_SVC_KEI_NO, KK0451_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0451_01_GENE.WRIB_SVC_KEI_NO, MIN(KK0451_01_GENE.GENE_ADD_DTM) AS KK0451_01_MIN ") .append(" FROM KK_T_WRIB_SVC_KEI KK0451_01_GENE ") .append(" WHERE KK0451_01_GENE.WRIB_SVC_KEI_NO = KK0451_01.WRIB_SVC_KEI_NO ") .append(" AND KK0451_01_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0451_01_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0451_01_GENE.WRIB_SVC_KEI_NO) ") .append(" AND (KK0021.MSKM_DTL_NO, KK0021.GENE_ADD_DTM) = ") .append(" (SELECT KK0021_GENE.MSKM_DTL_NO, MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX ") .append(" FROM KK_T_MSKM_DTL KK0021_GENE ") .append(" WHERE KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO ") .append(" AND KK0021_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0021_GENE.MSKM_DTL_NO) ") .append(" UNION ALL ") .append(" SELECT '2' AS WRIB_DCHS_HAMBET_CD ") .append(" , KK1391.DCHSKMST_NO AS WRIBSVK_DCHSKMST_NO ") .append(" , KK1391.DCHSKMST_STAT AS WRIBSVK_DCHSKMST_STAT ") .append(" ,(SELECT ZM0171_11.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_11 ") .append(" WHERE ZM0171_11.CD_SBT_CD = 'CD00808' ") .append(" AND ZM0171_11.CD_DIV = KK1391.DCHSKMST_STAT ") .append(" AND ZM0171_11.MK_FLG = '0' ") .append(" ) AS WRIBSVK_DCHSKMST_STAT_NM ") .append(" , KK1351.DCHS_TYPE_CD AS WRIB_DCHS_TYPE_CD ") .append(" ,(SELECT ZM0171_12.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_12 ") .append(" WHERE ZM0171_12.CD_SBT_CD = 'CD00284' ") .append(" AND ZM0171_12.CD_DIV = KK1351.DCHS_TYPE_CD ") .append(" AND ZM0171_12.MK_FLG = '0' ") .append(" ) AS WRIB_DCHS_TYPE_CD_NM ") .append(" , KK1351.DSP_DCHSKM_CD AS DSP_CAMPAIGN_DCHSKM_CD ") .append(" , KK1391.DCHSKM_CD AS WRISVC_DCHSKM_CD ") .append(" , KK1351.DCHSKM_NM AS WRISVC_DCHSKM_NM ") .append(" , DECODE(KK1351.DCHS_TYPE_CD, '10', NULL, KK0021.MSKM_YMD) AS MSKM_YMD ") // データ抽出項目コードが"DT0000002"(ネット電話ＴＶ三点申込)の場合は、データ抽出項目設定開始年月日を表示しない。 .append(" , DECODE(KK1391.DCHSKM_CD, 'DT0000002', NULL, KK1391.DCHSKMST_STA_YMD) AS SVC_CHRG_DCHSKMST_STAYMD ") .append(" , KK1391.DCHSKMST_END_YMD AS SVC_CHRG_DCHSKMST_ENDYMD ") .append(" ,(SELECT ZM0171_13.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_13 ") .append(" WHERE ZM0171_13.CD_SBT_CD = 'CD00403' ") .append(" AND ZM0171_13.CD_DIV = KK1391.APLY_JUN ") .append(" AND ZM0171_13.MK_FLG = '0' ") .append(" ) AS APLY_JUN_NM ") .append(" , KK1391.DCHSKMST_ADD_OPTY_CD AS WRIB_DCHSKMST_ADD_OPTY_CD ") .append(" , KK1351.DSP_JUN AS DSP_JUN ") .append(" , '1' AS WRIB_SCREEN_CTRL_CD ") .append(" FROM KK_T_DCHSKMST KK1391 ") .append(" INNER JOIN KK_M_DCHSKM KK1351 ") .append(" ON KK1351.DCHSKM_CD = KK1391.DCHSKM_CD ") .append(" INNER JOIN KK_T_DCHSKMST KK1391_01 ") .append(" ON KK1391_01.DCHSKMST_NO = KK1391.DCHSKMST_NO ") .append(" INNER JOIN KK_T_MSKM_DTL KK0021 ") .append(" ON KK0021.MSKM_DTL_NO = KK1391_01.MSKM_DTL_NO ") .append(" WHERE KK1391.SYSID = ? ") .append(" AND (KK1391.DCHSKMST_NO, KK1391.GENE_ADD_DTM) = ") .append(" (SELECT KK1391_GENE.DCHSKMST_NO, MAX(KK1391_GENE.GENE_ADD_DTM) AS KK1391_MAX ") .append(" FROM KK_T_DCHSKMST KK1391_GENE ") .append(" WHERE KK1391_GENE.DCHSKMST_NO = KK1391.DCHSKMST_NO ") .append(" AND KK1391_GENE.MK_FLG = '0' ") .append(" GROUP BY KK1391_GENE.DCHSKMST_NO) ") .append(" AND (KK1351.DCHSKM_CD, KK1351.RSV_APLY_YMD || KK1351.GENE_ADD_DTM) = ") .append(" (SELECT KK1351_GENE.DCHSKM_CD, MAX(KK1351_GENE.RSV_APLY_YMD || KK1351_GENE.GENE_ADD_DTM) AS KK1351_MAX ") .append(" FROM KK_M_DCHSKM KK1351_GENE ") .append(" WHERE KK1351_GENE.DCHSKM_CD = KK1351.DCHSKM_CD ") .append(" AND KK1351_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK1351_GENE.MK_FLG = '0' ") .append(" GROUP BY KK1351_GENE.DCHSKM_CD) ") .append(" AND (KK1391_01.DCHSKMST_NO, KK1391_01.GENE_ADD_DTM) = ") .append(" (SELECT KK1391_01_GENE.DCHSKMST_NO, MIN(KK1391_01_GENE.GENE_ADD_DTM) AS KK1391_01_MIN ") .append(" FROM KK_T_DCHSKMST KK1391_01_GENE ") .append(" WHERE KK1391_01_GENE.DCHSKMST_NO = KK1391_01.DCHSKMST_NO ") .append(" AND KK1391_01_GENE.MK_FLG = '0' ") .append(" GROUP BY KK1391_01_GENE.DCHSKMST_NO) ") .append(" AND (KK0021.MSKM_DTL_NO, KK0021.GENE_ADD_DTM) = ") .append(" (SELECT KK0021_GENE.MSKM_DTL_NO, MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX ") .append(" FROM KK_T_MSKM_DTL KK0021_GENE ") .append(" WHERE KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO ") .append(" AND KK0021_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0021_GENE.MSKM_DTL_NO) ") .append(" ) "); // SQL文_個別部1 if ( "1".equals( inMsg.getObject(EKK0451B011CBSMsg.FUNC_CODE).toString() ) || "2".equals( inMsg.getObject(EKK0451B011CBSMsg.FUNC_CODE).toString() )) { if ("2".equals( inMsg.getObject(EKK0451B011CBSMsg.FUNC_CODE).toString() )) { /* ++++++++++ v11.00.00 【ANK-2305-00-00 】mod start ++++++++++ */ // sql_Buff.append(" WHERE WRIB_SCREEN_CTRL_CD = '1' "); sql_Buff.append(" WHERE WRIB_SCREEN_CTRL_CD IN('1', '2') "); /* ++++++++++ v11.00.00 【ANK-2305-00-00 】mod end ++++++++++ */ } sql_Buff.append(" ORDER BY WRIBSVK_DCHSKMST_STAT ASC ") .append(" ,WRIB_DCHS_HAMBET_CD ASC ") .append(" ,SVC_CHRG_DCHSKMST_STAYMD DESC ") .append(" ,DSP_JUN ASC ") .append(" ,WRISVC_DCHSKM_CD ASC "); } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに指定された値を設定 if ( "1".equals( inMsg.getString(EKK0451B011CBSMsg.FUNC_CODE) ) || "2".equals( inMsg.getString(EKK0451B011CBSMsg.FUNC_CODE) )) { // パラメータの設定(ＫＥＹ＿SYSIDを指定) CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(EKK0451B011CBSMsg.KEY_SYSID)); // パラメータの設定(運用日付) CAANJDBCUtil.setParam(pstmt, 2, JKKModelCommon.getOpeDate(inMsg)); // パラメータの設定(運用日付) CAANJDBCUtil.setParam(pstmt, 3, JKKModelCommon.getOpeDate(inMsg)); // パラメータの設定(ＫＥＹ＿SYSIDを指定) CAANJDBCUtil.setParam(pstmt, 4, inMsg.getObject(EKK0451B011CBSMsg.KEY_SYSID)); // パラメータの設定(運用日付) CAANJDBCUtil.setParam(pstmt, 5, JKKModelCommon.getOpeDate(inMsg)); } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EKK0451B011CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EKK0451B011CBSMsg1List.WRIB_DCHS_HAMBET_CD , EKK0451B011CBSMsg1List.WRIBSVK_DCHSKMST_NO , EKK0451B011CBSMsg1List.WRIBSVK_DCHSKMST_STAT , EKK0451B011CBSMsg1List.WRIBSVK_DCHSKMST_STAT_NM , EKK0451B011CBSMsg1List.WRIB_DCHS_TYPE_CD , EKK0451B011CBSMsg1List.WRIB_DCHS_TYPE_CD_NM , EKK0451B011CBSMsg1List.DSP_CAMPAIGN_DCHSKM_CD , EKK0451B011CBSMsg1List.WRISVC_DCHSKM_CD , EKK0451B011CBSMsg1List.WRISVC_DCHSKM_NM , EKK0451B011CBSMsg1List.MSKM_YMD , EKK0451B011CBSMsg1List.SVC_CHRG_DCHSKMST_STAYMD , EKK0451B011CBSMsg1List.SVC_CHRG_DCHSKMST_ENDYMD , EKK0451B011CBSMsg1List.APLY_JUN_NM }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0451B011CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("EKK0451B011CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(EKK0451B011CBSMsg.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(EKK0451B011CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }