/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名：eo顧客基幹システム * モジュール名：JSYejbEKK0351B503TPDA * ソースファイル名：JSYejbEKK0351B503TPDA.java * 作成者：EK908996 * 日付：2013年03月29日 *＜機能概要＞ * 現オプションサービス契約情報一覧照会(ネット)テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン修正日修正者修正内容 * ｖ1.00.00 * v5.00 2013/04/02 FJ) 瀧澤【IKK-2013-0000775】最終更新年月日時分秒の取得を追加 * ********************************************************************************/ package eo.ejb.cbs.sqlf; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashMap; 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.ejb.cbm.entity.KK0351ETMsg; import eo.ejb.cbs.cbsmsg.EKK0351B503CBSMsg; import eo.ejb.cbs.cbsmsg.EKK0351B503CBSMsg1List; import eo.ejb.common.JCCModelCommon; import eo.ejb.common.db.JKKejbExclusiveProcKK0351; /** * * テンプレートDBアクセス部品　EKK0351B503_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEKK0351B503TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbEKK0351B503TPDA() { } /** * EKK0351B503_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(KK0351ETMsg.getTableName()); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT ") .append(" KK0351.OP_SVC_KEI_NO ") .append(" ,KK0351.GENE_ADD_DTM ") .append(" ,KK0351.OP_SVC_KEI_STAT ") .append(" ,KK0351.OP_SVC_CD ") .append(" ,KK0351.PCRS_CD ") .append(" ,KK0351.PPLAN_CD ") .append(" ,KK0351.OYA_KEI_SKBT_CD ") .append(" ,KK0351.SVC_KEI_NO ") .append(" ,KK0351.SVC_KEI_UCWK_NO ") .append(" ,KK0351.SYSID ") .append(" ,KK0351.MSKM_DTL_NO ") .append(" ,KK0351.FTRIAL_KANYU_YMD ") .append(" ,KK0351.FTRIAL_PRD_ENDYMD ") .append(" ,KK0351.HONKANYU_YMD ") .append(" ,KK0351.HONKANYU_IKO_KIGEN_YMD ") .append(" ,KK0351.SVC_USE_STA_KIBO_YMD ") .append(" ,KK0351.RSV_TSTA_KIBO_YMD ") .append(" ,KK0351.OP_SVC_KEI_KZKWRK_REQYMD ") .append(" ,KK0351.SHOSA_YMD ") .append(" ,KK0351.SHOSA_CL_YMD ") .append(" ,KK0351.SKEKKA_SEND_CD ") .append(" ,KK0351.KEI_CNC_YMD ") .append(" ,KK0351.RSV_APLY_YMD ") .append(" ,KK0351.RSV_CL_YMD ") .append(" ,KK0351.RSV_APLY_CD ") .append(" ,KK0351.PLAN_STAYMD ") .append(" ,KK0351.PLAN_ENDYMD ") .append(" ,KK0351.PLAN_CHRG_STAYMD ") .append(" ,KK0351.PLAN_CHRG_ENDYMD ") .append(" ,KK0351.PLAN_END_SBT_CD ") .append(" ,KK0351.SVC_CANCEL_YMD ") .append(" ,KK0351.SVC_CANCEL_RSN_CD ") .append(" ,KK0351.SVC_STAYMD ") .append(" ,KK0351.DSP_SVCTK_STAYMD ") .append(" ,KK0351.SVC_CHRG_STAYMD ") .append(" ,KK0351.SVC_STP_YMD ") .append(" ,KK0351.SVC_STP_RSN_CD ") .append(" ,KK0351.SVC_STP_RLS_YMD ") .append(" ,KK0351.SVC_STP_RLS_RSN_CD ") .append(" ,KK0351.PAUSE_STP_CD ") .append(" ,KK0351.SVC_PAUSE_YMD ") .append(" ,KK0351.SVC_PAUSE_RSN_CD ") .append(" ,KK0351.SVC_PAUSE_RSN_MEMO ") .append(" ,KK0351.SVC_PAUSE_RLS_YMD ") .append(" ,KK0351.SVC_PAUSE_RLS_RSN_CD ") .append(" ,KK0351.SVC_PAUSE_RLS_RSN_MEMO ") .append(" ,KK0351.SVC_ENDYMD ") .append(" ,KK0351.SVC_CHRG_ENDYMD ") .append(" ,KK0351.SVC_DSL_YMD ") .append(" ,KK0351.SVC_DLRE_CD ") .append(" ,KK0351.SVC_DLRE_MEMO ") .append(" ,KK0351.DSL_TNT_USER_ID ") .append(" ,KK0351.SVC_DSL_TTDKI_FIN_FLG ") .append(" ,KK0351.KAIHK_YMD ") .append(" ,KK0351.SVC_CANCEL_CL_YMD ") .append(" ,KK0351.SVC_DSL_CL_YMD ") .append(" ,KK0351.CHRG_STA_YMD_HOSEI_UM ") .append(" ,KK0351.SVC_PAUSE_CHRG_STA_YMD ") .append(" ,KK0351.OP_SVC_KEI_HKHASYMD ") .append(" ,KK0351.PNLTY_HASSEI_CD ") .append(" ,KK0351.IDO_DIV ") .append(" ,KK0351.SHOSA_DSL_FIN_CD ") .append(" ,KK0351.ADD_DTM ") .append(" ,KK0351.UPD_DTM ") .append(" ,NULL AS LAST_UPD_DTM ") .append(" ,KK0351.DEL_DTM ") .append(" ,KK0361.OP_SVC_KEI_NO AS OP_SVC_KEI_NO_NET ") .append(" ,KK0361.GENE_ADD_DTM AS GENE_ADD_DTM_NET ") .append(" ,KK0361.KIBO_ML_ACCOUNT_1 ") .append(" ,KK0361.KIBO_ML_ACCOUNT_2 ") .append(" ,KK0361.KIBO_ML_ACCOUNT_3 ") .append(" ,KK0361.MLTISE_SESSION_CNT ") .append(" ,KK0361.MLTISE_SHK_NINSHO_ID_PWD ") .append(" ,KK0361.MLTISE_NINSHO_ID ") .append(" ,KK0361.MLTISE_NINSHO_ID_PWD ") .append(" ,KK0361.KOTEI_IP_AD ") .append(" ,KK0361.NETMASK ") .append(" ,KK0361.SHK_POP_ID_PWD ") .append(" ,KK0361.POP_ID ") .append(" ,KK0361.MLAD ") .append(" ,KK0361.MLAD_CHGE_YMD ") .append(" ,KK0361.CAPA ") .append(" ,KK0361.SHK_WEB_ID_PWD ") .append(" ,KK0361.WEB_ID ") .append(" ,KK0361.URL_DOMAIN ") .append(" ,KK0361.URL_ACCOUNT ") .append(" ,KK0361.MLLIST_NM ") .append(" ,KK0361.MLLIST_KOSHO ") .append(" ,KK0361.MLLIST_KOKAI_YH ") .append(" ,KK0361.MLLIST_MAX_USER_CNT ") .append(" ,KK0361.MLLIST_KRISHA_MLAD ") .append(" ,KK0361.DOKUJI_DOMAIN_NM ") .append(" ,KK0361.HOSML_ACCOUNT_CNT ") .append(" ,KK0361.HOSML_BOX_CAPA ") .append(" ,KK0361.HOSML_KRISHA_ID ") .append(" ,KK0361.HOSTING_SHKM_KRISHA_ID_PWD ") .append(" ,KK0361.HOSML_KRISHA_POP_ID ") .append(" ,KK0361.HOS_SHKM_KRISHA_POP_ID_PWD ") .append(" ,KK0361.HOSML_KRISHA_MLAD ") .append(" ,KK0361.HOSML_KRISHA_MAIL_BOX_CAPA ") .append(" ,KK0361.DPAD_ID ") .append(" ,KK0361.DPAD_ISP_ID ") .append(" ,KK0361.DPAD_MAPID ") .append(" ,KK0361.DPAD_PIN ") .append(" ,KK0361.DPAD_MLAD ") .append(" ,KK0361.DPAD_ID_KOKAI_YH ") .append(" ,KK0361.DUPCON_NINSHO_ID ") .append(" ,KK0361.VOIP_USER_ID ") .append(" ,KK0361.VOIP_TELNO ") .append(" ,KK0361.DNS_SETTE_CD ") .append(" ,KK0361.MAIL_GW_KRISHA_ID ") .append(" ,KK0361.MAIL_GW_KRISHA_ID_PWD ") .append(" ,KK0361.MAIL_GW_KRISHA_MLAD ") .append(" ,KK0361.MAIL_GW_RCV_MSERVER_IP_AD ") .append(" ,KK0361.MAIL_GW_SEND_MSERVER_IP_AD ") .append(" ,KK0361.ML_GW_ACCOUNT_CNT ") .append(" ,KK0361.KOSHUMO_TELNO ") .append(" ,KK0361.SPOT_YGSITEBLCK_TRGT_SYSID ") .append(" ,KK0361.SIP_ADD_STAT_CD ") .append(" ,KK0361.SVC_KBT_SET_RRKS_MLAD ") .append(" ,KK0361.TUWA_DTL_KOKAI_YH ") .append(" ,KK0361.RSV_APLY_CD AS RSV_APLY_CD_NET ") .append(" ,KK0361.TSYRN_SVKEI_CD_SEQ ") .append(" ,KK0361.DOMAIN_DAIKO_SHINSEI_UM ") .append(" ,KK0361.EOHNT_DAIKO_SHINSEI_UM ") .append(" ,KK0361.WISH_OWNR_ID ") .append(" ,KK0361.ADD_DTM AS ADD_DTM_NET ") .append(" ,KK0361.UPD_DTM AS UPD_DTM_NET ") .append(" ,NULL AS LAST_UPD_DTM_NET ") .append(" ,KK0361.DEL_DTM AS DEL_DTM_NET ") .append(" FROM ") .append(" KK_T_OP_SVC_KEI KK0351 ") .append(" INNER JOIN KK_T_OPSVKEI_ISP KK0361 ") .append(" ON KK0361.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO ") .append(" AND KK0361.GENE_ADD_DTM = KK0351.GENE_ADD_DTM ") .append(" AND KK0361.MK_FLG = '0' ") .append(" AND KK0361.RSV_APLY_CD = '2' "); if (!"4".equals( inMsg.getObject(EKK0351B503CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" AND KK0351.SYSID = ? "); } sql_Buff.append(" AND (KK0351.OP_SVC_KEI_NO, KK0351.RSV_APLY_YMD || KK0351.GENE_ADD_DTM) = ( ") .append(" SELECT ") .append(" KK0351_GENE.OP_SVC_KEI_NO, MAX(KK0351_GENE.RSV_APLY_YMD || KK0351_GENE.GENE_ADD_DTM) AS MAX_RSV_GENE ") .append(" FROM KK_T_OP_SVC_KEI KK0351_GENE ") .append(" WHERE KK0351_GENE.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO ") .append(" AND KK0351_GENE.MK_FLG = '0' ") .append(" AND KK0351_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0351_GENE.RSV_APLY_YMD <= ? ") .append(" GROUP BY KK0351_GENE.OP_SVC_KEI_NO ") .append(" ) "); // SQL文_個別部1 if ( "1".equals( inMsg.getObject(EKK0351B503CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" WHERE ") .append(" KK0351.SVC_KEI_NO = ? ") .append(" OR ") .append(" EXISTS( ") .append(" SELECT 1 ") .append(" FROM ") .append(" KK_T_SVC_KEI_UCWK KK0161 ") .append(" WHERE ") .append(" KK0351.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ") .append(" AND KK0161.SVC_KEI_NO = ? ") .append(" AND (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ( ") .append(" SELECT ") .append(" KK0161_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS MAX_RSV_GENE ") .append(" FROM ") .append(" KK_T_SVC_KEI_UCWK KK0161_GENE ") .append(" WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ") .append(" AND KK0161_GENE.MK_FLG = '0' ") .append(" AND KK0161_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0161_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0161_GENE.KEIZK_AF_KEI_CHGECHU_FLG = '0' ") .append(" GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO ") .append(" ) ") .append(" ) ") .append(" UNION ALL ") .append(" SELECT ") .append(" KK0351_UNI.OP_SVC_KEI_NO ") .append(" ,KK0351_UNI.GENE_ADD_DTM ") .append(" ,KK0351_UNI.OP_SVC_KEI_STAT ") .append(" ,KK0351_UNI.OP_SVC_CD ") .append(" ,KK0351_UNI.PCRS_CD ") .append(" ,KK0351_UNI.PPLAN_CD ") .append(" ,KK0351_UNI.OYA_KEI_SKBT_CD ") .append(" ,KK0351_UNI.SVC_KEI_NO ") .append(" ,KK0351_UNI.SVC_KEI_UCWK_NO ") .append(" ,KK0351_UNI.SYSID ") .append(" ,KK0351_UNI.MSKM_DTL_NO ") .append(" ,KK0351_UNI.FTRIAL_KANYU_YMD ") .append(" ,KK0351_UNI.FTRIAL_PRD_ENDYMD ") .append(" ,KK0351_UNI.HONKANYU_YMD ") .append(" ,KK0351_UNI.HONKANYU_IKO_KIGEN_YMD ") .append(" ,KK0351_UNI.SVC_USE_STA_KIBO_YMD ") .append(" ,KK0351_UNI.RSV_TSTA_KIBO_YMD ") .append(" ,KK0351_UNI.OP_SVC_KEI_KZKWRK_REQYMD ") .append(" ,KK0351_UNI.SHOSA_YMD ") .append(" ,KK0351_UNI.SHOSA_CL_YMD ") .append(" ,KK0351_UNI.SKEKKA_SEND_CD ") .append(" ,KK0351_UNI.KEI_CNC_YMD ") .append(" ,KK0351_UNI.RSV_APLY_YMD ") .append(" ,KK0351_UNI.RSV_CL_YMD ") .append(" ,KK0351_UNI.RSV_APLY_CD ") .append(" ,KK0351_UNI.PLAN_STAYMD ") .append(" ,KK0351_UNI.PLAN_ENDYMD ") .append(" ,KK0351_UNI.PLAN_CHRG_STAYMD ") .append(" ,KK0351_UNI.PLAN_CHRG_ENDYMD ") .append(" ,KK0351_UNI.PLAN_END_SBT_CD ") .append(" ,KK0351_UNI.SVC_CANCEL_YMD ") .append(" ,KK0351_UNI.SVC_CANCEL_RSN_CD ") .append(" ,KK0351_UNI.SVC_STAYMD ") .append(" ,KK0351_UNI.DSP_SVCTK_STAYMD ") .append(" ,KK0351_UNI.SVC_CHRG_STAYMD ") .append(" ,KK0351_UNI.SVC_STP_YMD ") .append(" ,KK0351_UNI.SVC_STP_RSN_CD ") .append(" ,KK0351_UNI.SVC_STP_RLS_YMD ") .append(" ,KK0351_UNI.SVC_STP_RLS_RSN_CD ") .append(" ,KK0351_UNI.PAUSE_STP_CD ") .append(" ,KK0351_UNI.SVC_PAUSE_YMD ") .append(" ,KK0351_UNI.SVC_PAUSE_RSN_CD ") .append(" ,KK0351_UNI.SVC_PAUSE_RSN_MEMO ") .append(" ,KK0351_UNI.SVC_PAUSE_RLS_YMD ") .append(" ,KK0351_UNI.SVC_PAUSE_RLS_RSN_CD ") .append(" ,KK0351_UNI.SVC_PAUSE_RLS_RSN_MEMO ") .append(" ,KK0351_UNI.SVC_ENDYMD ") .append(" ,KK0351_UNI.SVC_CHRG_ENDYMD ") .append(" ,KK0351_UNI.SVC_DSL_YMD ") .append(" ,KK0351_UNI.SVC_DLRE_CD ") .append(" ,KK0351_UNI.SVC_DLRE_MEMO ") .append(" ,KK0351_UNI.DSL_TNT_USER_ID ") .append(" ,KK0351_UNI.SVC_DSL_TTDKI_FIN_FLG ") .append(" ,KK0351_UNI.KAIHK_YMD ") .append(" ,KK0351_UNI.SVC_CANCEL_CL_YMD ") .append(" ,KK0351_UNI.SVC_DSL_CL_YMD ") .append(" ,KK0351_UNI.CHRG_STA_YMD_HOSEI_UM ") .append(" ,KK0351_UNI.SVC_PAUSE_CHRG_STA_YMD ") .append(" ,KK0351_UNI.OP_SVC_KEI_HKHASYMD ") .append(" ,KK0351_UNI.PNLTY_HASSEI_CD ") .append(" ,KK0351_UNI.IDO_DIV ") .append(" ,KK0351_UNI.SHOSA_DSL_FIN_CD ") .append(" ,KK0351_UNI.ADD_DTM ") .append(" ,KK0351_UNI.UPD_DTM ") .append(" ,NULL AS LAST_UPD_DTM ") .append(" ,KK0351_UNI.DEL_DTM ") .append(" ,KK0391.OP_SVC_KEI_NO AS OP_SVC_KEI_NO_NET ") .append(" ,KK0391.GENE_ADD_DTM AS GENE_ADD_DTM_NET ") .append(" ,NULL AS KIBO_ML_ACCOUNT_1 ") .append(" ,NULL AS KIBO_ML_ACCOUNT_2 ") .append(" ,NULL AS KIBO_ML_ACCOUNT_3 ") .append(" ,NULL AS MLTISE_SESSION_CNT ") .append(" ,NULL AS MLTISE_SHK_NINSHO_ID_PWD ") .append(" ,NULL AS MLTISE_NINSHO_ID ") .append(" ,NULL AS MLTISE_NINSHO_ID_PWD ") .append(" ,NULL AS KOTEI_IP_AD ") .append(" ,NULL AS NETMASK ") .append(" ,NULL AS SHK_POP_ID_PWD ") .append(" ,NULL AS POP_ID ") .append(" ,NULL AS MLAD ") .append(" ,NULL AS MLAD_CHGE_YMD ") .append(" ,NULL AS CAPA ") .append(" ,NULL AS SHK_WEB_ID_PWD ") .append(" ,NULL AS WEB_ID ") .append(" ,NULL AS URL_DOMAIN ") .append(" ,NULL AS URL_ACCOUNT ") .append(" ,NULL AS MLLIST_NM ") .append(" ,NULL AS MLLIST_KOSHO ") .append(" ,NULL AS MLLIST_KOKAI_YH ") .append(" ,NULL AS MLLIST_MAX_USER_CNT ") .append(" ,NULL AS MLLIST_KRISHA_MLAD ") .append(" ,NULL AS DOKUJI_DOMAIN_NM ") .append(" ,NULL AS HOSML_ACCOUNT_CNT ") .append(" ,NULL AS HOSML_BOX_CAPA ") .append(" ,NULL AS HOSML_KRISHA_ID ") .append(" ,NULL AS HOSTING_SHKM_KRISHA_ID_PWD ") .append(" ,NULL AS HOSML_KRISHA_POP_ID ") .append(" ,NULL AS HOS_SHKM_KRISHA_POP_ID_PWD ") .append(" ,NULL AS HOSML_KRISHA_MLAD ") .append(" ,NULL AS HOSML_KRISHA_MAIL_BOX_CAPA ") .append(" ,NULL AS DPAD_ID ") .append(" ,NULL AS DPAD_ISP_ID ") .append(" ,NULL AS DPAD_MAPID ") .append(" ,NULL AS DPAD_PIN ") .append(" ,NULL AS DPAD_MLAD ") .append(" ,NULL AS DPAD_ID_KOKAI_YH ") .append(" ,NULL AS DUPCON_NINSHO_ID ") .append(" ,NULL AS VOIP_USER_ID ") .append(" ,NULL AS VOIP_TELNO ") .append(" ,NULL AS DNS_SETTE_CD ") .append(" ,NULL AS MAIL_GW_KRISHA_ID ") .append(" ,NULL AS MAIL_GW_KRISHA_ID_PWD ") .append(" ,NULL AS MAIL_GW_KRISHA_MLAD ") .append(" ,NULL AS MAIL_GW_RCV_MSERVER_IP_AD ") .append(" ,NULL AS MAIL_GW_SEND_MSERVER_IP_AD ") .append(" ,NULL AS ML_GW_ACCOUNT_CNT ") .append(" ,NULL AS KOSHUMO_TELNO ") .append(" ,NULL AS SPOT_YGSITEBLCK_TRGT_SYSID ") .append(" ,NULL AS SIP_ADD_STAT_CD ") .append(" ,NULL AS SVC_KBT_SET_RRKS_MLAD ") .append(" ,NULL AS TUWA_DTL_KOKAI_YH ") .append(" ,KK0391.RSV_APLY_CD AS RSV_APLY_CD_NET ") .append(" ,NULL AS TSYRN_SVKEI_CD_SEQ ") .append(" ,NULL AS DOMAIN_DAIKO_SHINSEI_UM ") .append(" ,NULL AS EOHNT_DAIKO_SHINSEI_UM ") .append(" ,NULL AS WISH_OWNR_ID ") .append(" ,KK0391.ADD_DTM AS ADD_DTM_NET ") .append(" ,KK0391.UPD_DTM AS UPD_DTM_NET ") .append(" ,NULL AS LAST_UPD_DTM_NET ") .append(" ,KK0391.DEL_DTM AS DEL_DTM_NET ") .append(" FROM ") .append(" KK_T_OP_SVC_KEI KK0351_UNI ") .append(" INNER JOIN KK_T_OPSVKEI_SUPPORT KK0391 ") .append(" ON KK0391.OP_SVC_KEI_NO = KK0351_UNI.OP_SVC_KEI_NO ") .append(" AND KK0391.GENE_ADD_DTM = KK0351_UNI.GENE_ADD_DTM ") .append(" AND KK0391.MK_FLG = '0' ") .append(" AND KK0391.RSV_APLY_CD = '2' ") .append(" AND KK0351_UNI.SYSID = ? ") .append(" AND (KK0351_UNI.OP_SVC_KEI_NO, KK0351_UNI.RSV_APLY_YMD || KK0351_UNI.GENE_ADD_DTM) = ( ") .append(" SELECT ") .append(" KK0351_GENE_UNI.OP_SVC_KEI_NO, MAX(KK0351_GENE_UNI.RSV_APLY_YMD || KK0351_GENE_UNI.GENE_ADD_DTM) AS MAX_RSV_GENE ") .append(" FROM KK_T_OP_SVC_KEI KK0351_GENE_UNI ") .append(" WHERE KK0351_GENE_UNI.OP_SVC_KEI_NO = KK0351_UNI.OP_SVC_KEI_NO ") .append(" AND KK0351_GENE_UNI.MK_FLG = '0' ") .append(" AND KK0351_GENE_UNI.RSV_APLY_CD = '2' ") .append(" AND KK0351_GENE_UNI.RSV_APLY_YMD <= ? ") .append(" GROUP BY KK0351_GENE_UNI.OP_SVC_KEI_NO ") .append(" ) ") .append(" WHERE ") .append(" KK0351_UNI.SVC_KEI_NO = ? ") .append(" OR ") .append(" EXISTS( ") .append(" SELECT 1 ") .append(" FROM ") .append(" KK_T_SVC_KEI_UCWK KK0161_UNI ") .append(" WHERE ") .append(" KK0351_UNI.SVC_KEI_UCWK_NO = KK0161_UNI.SVC_KEI_UCWK_NO ") .append(" AND KK0161_UNI.SVC_KEI_NO = ? ") .append(" AND (KK0161_UNI.SVC_KEI_UCWK_NO, KK0161_UNI.RSV_APLY_YMD || KK0161_UNI.GENE_ADD_DTM) = ( ") .append(" SELECT ") .append(" KK0161_GENE_UNI.SVC_KEI_UCWK_NO, MAX(KK0161_GENE_UNI.RSV_APLY_YMD || KK0161_GENE_UNI.GENE_ADD_DTM) AS MAX_RSV_GENE ") .append(" FROM ") .append(" KK_T_SVC_KEI_UCWK KK0161_GENE_UNI ") .append(" WHERE KK0161_GENE_UNI.SVC_KEI_UCWK_NO = KK0161_UNI.SVC_KEI_UCWK_NO ") .append(" AND KK0161_GENE_UNI.MK_FLG = '0' ") .append(" AND KK0161_GENE_UNI.RSV_APLY_CD = '2' ") .append(" AND KK0161_GENE_UNI.RSV_APLY_YMD <= ? ") .append(" AND KK0161_GENE_UNI.KEIZK_AF_KEI_CHGECHU_FLG = '0' ") .append(" GROUP BY KK0161_GENE_UNI.SVC_KEI_UCWK_NO ") .append(" ) ") .append(" ) ") .append(" ORDER BY ") .append(" OP_SVC_KEI_NO "); } if ( "2".equals( inMsg.getObject(EKK0351B503CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" WHERE ") .append(" KK0361.WEB_ID = ? ") .append(" AND KK0351.SVC_KEI_NO = ? ") .append(" ORDER BY ") .append(" KK0351.OP_SVC_KEI_NO "); } if ( "3".equals( inMsg.getObject(EKK0351B503CBSMsg.FUNC_CODE).toString() ) || "4".equals( inMsg.getObject(EKK0351B503CBSMsg.FUNC_CODE).toString() )) { sql_Buff.append(" WHERE ") .append(" KK0361.POP_ID = ? ") .append(" AND KK0351.SVC_KEI_NO = ? ") .append(" ORDER BY ") .append(" KK0351.OP_SVC_KEI_NO "); } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに指定された値を設定 if ( "1".equals( inMsg.getString(EKK0351B503CBSMsg.FUNC_CODE) ) ) { // 手修正 START // パラメータの設定(ＫＥＹ＿ＳＹＳＩＤを指定) CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(EKK0351B503CBSMsg.KEY_SYSID)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 2, JCCModelCommon.getOpeDate(inMsg, null)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 3, inMsg.getObject(EKK0351B503CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 4, inMsg.getObject(EKK0351B503CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 5, JCCModelCommon.getOpeDate(inMsg, null)); // パラメータの設定(ＫＥＹ＿ＳＹＳＩＤを指定) CAANJDBCUtil.setParam(pstmt, 6, inMsg.getObject(EKK0351B503CBSMsg.KEY_SYSID)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 7, JCCModelCommon.getOpeDate(inMsg, null)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 8, inMsg.getObject(EKK0351B503CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 9, inMsg.getObject(EKK0351B503CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 10, JCCModelCommon.getOpeDate(inMsg, null)); // 手修正 END } if ( "2".equals( inMsg.getString(EKK0351B503CBSMsg.FUNC_CODE) ) ) { // 手修正 START // パラメータの設定(ＫＥＹ＿ＳＹＳＩＤを指定) CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(EKK0351B503CBSMsg.KEY_SYSID)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 2, JCCModelCommon.getOpeDate(inMsg, null)); // パラメータの設定(ＫＥＹ＿ＷｅｂＩＤを指定) CAANJDBCUtil.setParam(pstmt, 3, inMsg.getObject(EKK0351B503CBSMsg.KEY_WEB_ID)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 4, inMsg.getObject(EKK0351B503CBSMsg.KEY_SVC_KEI_NO)); // 手修正 END } if ( "3".equals( inMsg.getString(EKK0351B503CBSMsg.FUNC_CODE) )) { // 手修正 START // パラメータの設定(ＫＥＹ＿ＳＹＳＩＤを指定) CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(EKK0351B503CBSMsg.KEY_SYSID)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 2, JCCModelCommon.getOpeDate(inMsg, null)); // パラメータの設定(ＫＥＹ＿ＰＯＰＩＤを指定) CAANJDBCUtil.setParam(pstmt, 3, inMsg.getObject(EKK0351B503CBSMsg.KEY_POP_ID)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 4, inMsg.getObject(EKK0351B503CBSMsg.KEY_SVC_KEI_NO)); // 手修正 END } if ( "4".equals( inMsg.getObject(EKK0351B503CBSMsg.FUNC_CODE).toString() )) { // 手修正 START // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 1, JCCModelCommon.getOpeDate(inMsg, null)); // パラメータの設定(ＫＥＹ＿ＰＯＰＩＤを指定) CAANJDBCUtil.setParam(pstmt, 2, inMsg.getObject(EKK0351B503CBSMsg.KEY_POP_ID)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 3, inMsg.getObject(EKK0351B503CBSMsg.KEY_SVC_KEI_NO)); // 手修正 END } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EKK0351B503CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EKK0351B503CBSMsg1List.OP_SVC_KEI_NO , EKK0351B503CBSMsg1List.GENE_ADD_DTM , EKK0351B503CBSMsg1List.OP_SVC_KEI_STAT , EKK0351B503CBSMsg1List.OP_SVC_CD , EKK0351B503CBSMsg1List.PCRS_CD , EKK0351B503CBSMsg1List.PPLAN_CD , EKK0351B503CBSMsg1List.OYA_KEI_SKBT_CD , EKK0351B503CBSMsg1List.SVC_KEI_NO , EKK0351B503CBSMsg1List.SVC_KEI_UCWK_NO , EKK0351B503CBSMsg1List.SYSID , EKK0351B503CBSMsg1List.MSKM_DTL_NO , EKK0351B503CBSMsg1List.FTRIAL_KANYU_YMD , EKK0351B503CBSMsg1List.FTRIAL_PRD_ENDYMD , EKK0351B503CBSMsg1List.HONKANYU_YMD , EKK0351B503CBSMsg1List.HONKANYU_IKO_KIGEN_YMD , EKK0351B503CBSMsg1List.SVC_USE_STA_KIBO_YMD , EKK0351B503CBSMsg1List.RSV_TSTA_KIBO_YMD , EKK0351B503CBSMsg1List.OP_SVC_KEI_KZKWRK_REQYMD , EKK0351B503CBSMsg1List.SHOSA_YMD , EKK0351B503CBSMsg1List.SHOSA_CL_YMD , EKK0351B503CBSMsg1List.SKEKKA_SEND_CD , EKK0351B503CBSMsg1List.KEI_CNC_YMD , EKK0351B503CBSMsg1List.RSV_APLY_YMD , EKK0351B503CBSMsg1List.RSV_CL_YMD , EKK0351B503CBSMsg1List.RSV_APLY_CD , EKK0351B503CBSMsg1List.PLAN_STAYMD , EKK0351B503CBSMsg1List.PLAN_ENDYMD , EKK0351B503CBSMsg1List.PLAN_CHRG_STAYMD , EKK0351B503CBSMsg1List.PLAN_CHRG_ENDYMD , EKK0351B503CBSMsg1List.PLAN_END_SBT_CD , EKK0351B503CBSMsg1List.SVC_CANCEL_YMD , EKK0351B503CBSMsg1List.SVC_CANCEL_RSN_CD , EKK0351B503CBSMsg1List.SVC_STAYMD , EKK0351B503CBSMsg1List.DSP_SVCTK_STAYMD , EKK0351B503CBSMsg1List.SVC_CHRG_STAYMD , EKK0351B503CBSMsg1List.SVC_STP_YMD , EKK0351B503CBSMsg1List.SVC_STP_RSN_CD , EKK0351B503CBSMsg1List.SVC_STP_RLS_YMD , EKK0351B503CBSMsg1List.SVC_STP_RLS_RSN_CD , EKK0351B503CBSMsg1List.PAUSE_STP_CD , EKK0351B503CBSMsg1List.SVC_PAUSE_YMD , EKK0351B503CBSMsg1List.SVC_PAUSE_RSN_CD , EKK0351B503CBSMsg1List.SVC_PAUSE_RSN_MEMO , EKK0351B503CBSMsg1List.SVC_PAUSE_RLS_YMD , EKK0351B503CBSMsg1List.SVC_PAUSE_RLS_RSN_CD , EKK0351B503CBSMsg1List.SVC_PAUSE_RLS_RSN_MEMO , EKK0351B503CBSMsg1List.SVC_ENDYMD , EKK0351B503CBSMsg1List.SVC_CHRG_ENDYMD , EKK0351B503CBSMsg1List.SVC_DSL_YMD , EKK0351B503CBSMsg1List.SVC_DLRE_CD , EKK0351B503CBSMsg1List.SVC_DLRE_MEMO , EKK0351B503CBSMsg1List.DSL_TNT_USER_ID , EKK0351B503CBSMsg1List.SVC_DSL_TTDKI_FIN_FLG , EKK0351B503CBSMsg1List.KAIHK_YMD , EKK0351B503CBSMsg1List.SVC_CANCEL_CL_YMD , EKK0351B503CBSMsg1List.SVC_DSL_CL_YMD , EKK0351B503CBSMsg1List.CHRG_STA_YMD_HOSEI_UM , EKK0351B503CBSMsg1List.SVC_PAUSE_CHRG_STA_YMD , EKK0351B503CBSMsg1List.OP_SVC_KEI_HKHASYMD , EKK0351B503CBSMsg1List.PNLTY_HASSEI_CD , EKK0351B503CBSMsg1List.IDO_DIV , EKK0351B503CBSMsg1List.SHOSA_DSL_FIN_CD , EKK0351B503CBSMsg1List.ADD_DTM , EKK0351B503CBSMsg1List.UPD_DTM , EKK0351B503CBSMsg1List.LAST_UPD_DTM , EKK0351B503CBSMsg1List.DEL_DTM , EKK0351B503CBSMsg1List.OP_SVC_KEI_NO_ISP , EKK0351B503CBSMsg1List.GENE_ADD_DTM_ISP , EKK0351B503CBSMsg1List.KIBO_ML_ACCOUNT_1 , EKK0351B503CBSMsg1List.KIBO_ML_ACCOUNT_2 , EKK0351B503CBSMsg1List.KIBO_ML_ACCOUNT_3 , EKK0351B503CBSMsg1List.MLTISE_SESSION_CNT , EKK0351B503CBSMsg1List.MLTISE_SHK_NINSHO_ID_PWD , EKK0351B503CBSMsg1List.MLTISE_NINSHO_ID , EKK0351B503CBSMsg1List.MLTISE_NINSHO_ID_PWD , EKK0351B503CBSMsg1List.KOTEI_IP_AD , EKK0351B503CBSMsg1List.NETMASK , EKK0351B503CBSMsg1List.SHK_POP_ID_PWD , EKK0351B503CBSMsg1List.POP_ID , EKK0351B503CBSMsg1List.MLAD , EKK0351B503CBSMsg1List.MLAD_CHGE_YMD , EKK0351B503CBSMsg1List.CAPA , EKK0351B503CBSMsg1List.SHK_WEB_ID_PWD , EKK0351B503CBSMsg1List.WEB_ID , EKK0351B503CBSMsg1List.URL_DOMAIN , EKK0351B503CBSMsg1List.URL_ACCOUNT , EKK0351B503CBSMsg1List.MLLIST_NM , EKK0351B503CBSMsg1List.MLLIST_KOSHO , EKK0351B503CBSMsg1List.MLLIST_KOKAI_YH , EKK0351B503CBSMsg1List.MLLIST_MAX_USER_CNT , EKK0351B503CBSMsg1List.MLLIST_KRISHA_MLAD , EKK0351B503CBSMsg1List.DOKUJI_DOMAIN_NM , EKK0351B503CBSMsg1List.HOSML_ACCOUNT_CNT , EKK0351B503CBSMsg1List.HOSML_BOX_CAPA , EKK0351B503CBSMsg1List.HOSML_KRISHA_ID , EKK0351B503CBSMsg1List.HOSTING_SHKM_KRISHA_ID_PWD , EKK0351B503CBSMsg1List.HOSML_KRISHA_POP_ID , EKK0351B503CBSMsg1List.HOS_SHKM_KRISHA_POP_ID_PWD , EKK0351B503CBSMsg1List.HOSML_KRISHA_MLAD , EKK0351B503CBSMsg1List.HOSML_KRISHA_MAIL_BOX_CAPA , EKK0351B503CBSMsg1List.DPAD_ID , EKK0351B503CBSMsg1List.DPAD_ISP_ID , EKK0351B503CBSMsg1List.DPAD_MAPID , EKK0351B503CBSMsg1List.DPAD_PIN , EKK0351B503CBSMsg1List.DPAD_MLAD , EKK0351B503CBSMsg1List.DPAD_ID_KOKAI_YH , EKK0351B503CBSMsg1List.DUPCON_NINSHO_ID , EKK0351B503CBSMsg1List.VOIP_USER_ID , EKK0351B503CBSMsg1List.VOIP_TELNO , EKK0351B503CBSMsg1List.DNS_SETTE_CD , EKK0351B503CBSMsg1List.MAIL_GW_KRISHA_ID , EKK0351B503CBSMsg1List.MAIL_GW_KRISHA_ID_PWD , EKK0351B503CBSMsg1List.MAIL_GW_KRISHA_MLAD , EKK0351B503CBSMsg1List.MAIL_GW_RCV_MSERVER_IP_AD , EKK0351B503CBSMsg1List.MAIL_GW_SEND_MSERVER_IP_AD , EKK0351B503CBSMsg1List.ML_GW_ACCOUNT_CNT , EKK0351B503CBSMsg1List.KOSHUMO_TELNO , EKK0351B503CBSMsg1List.SPOT_YGSITEBLCK_TRGT_SYSID , EKK0351B503CBSMsg1List.SIP_ADD_STAT_CD , EKK0351B503CBSMsg1List.SVC_KBT_SET_RRKS_MLAD , EKK0351B503CBSMsg1List.TUWA_DTL_KOKAI_YH , EKK0351B503CBSMsg1List.RSV_APLY_CD_ISP , EKK0351B503CBSMsg1List.TSYRN_SVKEI_CD_SEQ , EKK0351B503CBSMsg1List.DOMAIN_DAIKO_SHINSEI_UM , EKK0351B503CBSMsg1List.EOHNT_DAIKO_SHINSEI_UM , EKK0351B503CBSMsg1List.WISH_OWNR_ID , EKK0351B503CBSMsg1List.ADD_DTM_ISP , EKK0351B503CBSMsg1List.UPD_DTM_ISP , EKK0351B503CBSMsg1List.LAST_UPD_DTM_ISP , EKK0351B503CBSMsg1List.DEL_DTM_ISP }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0351B503CBSMsg1List",0); // 手修正 START HashMap lastUpdDtmMap = new HashMap(); JKKejbExclusiveProcKK0351 exclusiveProc = new JKKejbExclusiveProcKK0351(); for (int i = 0; i < outMsg1.length; i++) { String lastUpdDtm = ""; String key = outMsg1[i].getString(EKK0351B503CBSMsg1List.OP_SVC_KEI_NO); if (!lastUpdDtmMap.containsKey(key)) { // 新しいオプションサービス契約番号の場合、排他制御部品から最終更新年月日時分秒を取得 lastUpdDtm = exclusiveProc.getLastUpdDtmOpSvcKeiNo(inMsg, key); lastUpdDtmMap.put(key, lastUpdDtm); } else { // すでに取得した最終更新年月日時分秒を設定 lastUpdDtm = lastUpdDtmMap.get(key); } // 取得した最大最終更新年月日時分秒を設定 outMsg1[i].set(EKK0351B503CBSMsg1List.LAST_UPD_DTM, lastUpdDtm); } // 手修正 END // inMsg(CBSMsg)に明細をセット inMsg.set("EKK0351B503CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(EKK0351B503CBSMsg.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(EKK0351B503CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }