/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名 ：eo顧客基幹システム * モジュール名 ：JSYejbEKK0351B003TPDA * ソースファイル名：JSYejbEKK0351B003TPDA.java * 作成者 ：EK912022 * 日付 ：2012年12月06日 *＜機能概要＞ * オプションサービス契約履歴一覧照会テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン 修正日 修正者 修正内容 * ｖ1.00.00 * v32.02.00 2017/07/25 FJ)舘山 【OM-2017-0000593】住所変更の番号変更による非課金判定 * ********************************************************************************/ 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 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.EKK0351B003CBSMsg; import eo.ejb.cbs.cbsmsg.EKK0351B003CBSMsg1List; import eo.ejb.common.JKKModelCommon; /** * * テンプレートDBアクセス部品　EKK0351B003_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEKK0351B003TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * 区切り文字 */ private static final String Comma = " ,"; /** * バインド変数 */ private static final String bind = "?"; /** * コンストラクタ */ public JSYejbEKK0351B003TPDA() { } /** * EKK0351B003_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()); // バインド変数設定値保持用領域 StringBuffer strBufBind = new StringBuffer(); ArrayList arraySvkiuwkno = new ArrayList(); // サービスＩＦ入力値：サービス契約内訳番号１判定 createSvkiuwkno(inMsg, strBufBind, arraySvkiuwkno, EKK0351B003CBSMsg.KEY_SVC_KEI_UCWK_NO1); // サービスＩＦ入力値：サービス契約内訳番号２判定 createSvkiuwkno(inMsg, strBufBind, arraySvkiuwkno, EKK0351B003CBSMsg.KEY_SVC_KEI_UCWK_NO2); // サービスＩＦ入力値：サービス契約内訳番号３判定 createSvkiuwkno(inMsg, strBufBind, arraySvkiuwkno, EKK0351B003CBSMsg.KEY_SVC_KEI_UCWK_NO3); // サービスＩＦ入力値：サービス契約内訳番号４判定 createSvkiuwkno(inMsg, strBufBind, arraySvkiuwkno, EKK0351B003CBSMsg.KEY_SVC_KEI_UCWK_NO4); // サービスＩＦ入力値：サービス契約内訳番号５判定 createSvkiuwkno(inMsg, strBufBind, arraySvkiuwkno, EKK0351B003CBSMsg.KEY_SVC_KEI_UCWK_NO5); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT KK0351.OP_SVC_KEI_NO AS KK0351_OP_SVC_KEI_NO ") .append(" , KK0351.GENE_ADD_DTM AS KK0351_GENE_ADD_DTM ") .append(" , KK0351.OP_SVC_KEI_STAT AS OP_SVC_KEI_STAT ") .append(" , KK0351.OP_SVC_CD AS OP_SVC_CD ") .append(" , KK0351.PCRS_CD AS PCRS_CD ") .append(" , KK0351.PPLAN_CD AS PPLAN_CD ") .append(" , KK0351.OYA_KEI_SKBT_CD AS OYA_KEI_SKBT_CD ") .append(" , KK0351.SVC_KEI_NO AS SVC_KEI_NO ") .append(" , KK0351.SVC_KEI_UCWK_NO AS SVC_KEI_UCWK_NO ") .append(" , KK0351.SYSID AS SYSID ") .append(" , KK0351.MSKM_DTL_NO AS MSKM_DTL_NO ") .append(" , KK0351.FTRIAL_KANYU_YMD AS FTRIAL_KANYU_YMD ") .append(" , KK0351.FTRIAL_PRD_ENDYMD AS FTRIAL_PRD_ENDYMD ") .append(" , KK0351.HONKANYU_YMD AS HONKANYU_YMD ") .append(" , KK0351.HONKANYU_IKO_KIGEN_YMD AS HONKANYU_IKO_KIGEN_YMD ") .append(" , KK0351.SVC_USE_STA_KIBO_YMD AS SVC_USE_STA_KIBO_YMD ") .append(" , KK0351.RSV_TSTA_KIBO_YMD AS RSV_TSTA_KIBO_YMD ") .append(" , KK0351.OP_SVC_KEI_KZKWRK_REQYMD AS OP_SVC_KEI_KZKWRK_REQYMD ") .append(" , KK0351.SHOSA_YMD AS SHOSA_YMD ") .append(" , KK0351.SHOSA_CL_YMD AS SHOSA_CL_YMD ") .append(" , KK0351.SKEKKA_SEND_CD AS SKEKKA_SEND_CD ") .append(" , KK0351.KEI_CNC_YMD AS KEI_CNC_YMD ") .append(" , KK0351.RSV_APLY_YMD AS RSV_APLY_YMD ") .append(" , KK0351.RSV_CL_YMD AS RSV_CL_YMD ") .append(" , KK0351.RSV_APLY_CD AS KK0351_RSV_APLY_CD ") .append(" , KK0351.PLAN_STAYMD AS PLAN_STAYMD ") .append(" , KK0351.PLAN_ENDYMD AS PLAN_ENDYMD ") .append(" , KK0351.PLAN_CHRG_STAYMD AS PLAN_CHRG_STAYMD ") .append(" , KK0351.PLAN_CHRG_ENDYMD AS PLAN_CHRG_ENDYMD ") .append(" , KK0351.PLAN_END_SBT_CD AS PLAN_END_SBT_CD ") .append(" , KK0351.SVC_CANCEL_YMD AS SVC_CANCEL_YMD ") .append(" , KK0351.SVC_CANCEL_RSN_CD AS SVC_CANCEL_RSN_CD ") .append(" , KK0351.SVC_STAYMD AS SVC_STAYMD ") .append(" , KK0351.SVC_CHRG_STAYMD AS SVC_CHRG_STAYMD ") .append(" , KK0351.SVC_STP_YMD AS SVC_STP_YMD ") .append(" , KK0351.SVC_STP_RSN_CD AS SVC_STP_RSN_CD ") .append(" , KK0351.SVC_STP_RLS_YMD AS SVC_STP_RLS_YMD ") .append(" , KK0351.SVC_STP_RLS_RSN_CD AS SVC_STP_RLS_RSN_CD ") .append(" , KK0351.PAUSE_STP_CD AS PAUSE_STP_CD ") .append(" , KK0351.SVC_PAUSE_YMD AS SVC_PAUSE_YMD ") .append(" , KK0351.SVC_PAUSE_RSN_CD AS SVC_PAUSE_RSN_CD ") .append(" , KK0351.SVC_PAUSE_RSN_MEMO AS SVC_PAUSE_RSN_MEMO ") .append(" , KK0351.SVC_PAUSE_RLS_YMD AS SVC_PAUSE_RLS_YMD ") .append(" , KK0351.SVC_PAUSE_RLS_RSN_CD AS SVC_PAUSE_RLS_RSN_CD ") .append(" , KK0351.SVC_PAUSE_RLS_RSN_MEMO AS SVC_PAUSE_RLS_RSN_MEMO ") .append(" , KK0351.SVC_ENDYMD AS SVC_ENDYMD ") .append(" , KK0351.SVC_CHRG_ENDYMD AS SVC_CHRG_ENDYMD ") .append(" , KK0351.SVC_DSL_YMD AS SVC_DSL_YMD ") .append(" , KK0351.SVC_DLRE_CD AS SVC_DLRE_CD ") .append(" , KK0351.SVC_DLRE_MEMO AS SVC_DLRE_MEMO ") .append(" , KK0351.DSL_TNT_USER_ID AS DSL_TNT_USER_ID ") .append(" , KK0351.SVC_DSL_TTDKI_FIN_FLG AS SVC_DSL_TTDKI_FIN_FLG ") .append(" , KK0351.KAIHK_YMD AS KAIHK_YMD ") .append(" , KK0351.SVC_CANCEL_CL_YMD AS SVC_CANCEL_CL_YMD ") .append(" , KK0351.SVC_DSL_CL_YMD AS SVC_DSL_CL_YMD ") .append(" , KK0351.CHRG_STA_YMD_HOSEI_UM AS CHRG_STA_YMD_HOSEI_UM ") .append(" , KK0351.SVC_PAUSE_CHRG_STA_YMD AS SVC_PAUSE_CHRG_STA_YMD ") .append(" , KK0351.OP_SVC_KEI_HKHASYMD AS OP_SVC_KEI_HKHASYMD ") .append(" , KK0351.PNLTY_HASSEI_CD AS PNLTY_HASSEI_CD ") .append(" , KK0351.IDO_DIV AS IDO_DIV ") .append(" , KK0351.SHOSA_DSL_FIN_CD AS SHOSA_DSL_FIN_CD ") .append(" , KK0351.SVCTK_BUT_DEL_TRN_JSSI_DTM AS SVCTK_BUT_DEL_TRN_JSSI_DTM ") .append(" , KK0351.ADD_DTM AS KK0351_ADD_DTM ") .append(" , KK0351.ADD_OPEACNT AS KK0351_ADD_OPEACNT ") .append(" ,( SELECT USER_NM ") .append(" FROM ZM_M_USER ZM0051 ") .append(" WHERE ZM0051.USER_ID = KK0351.ADD_OPEACNT ") .append(" AND ZM0051.MK_FLG = '0' ") .append(" ) AS ZM0051_USER_NM ") .append(" , KK0351.UPD_DTM AS KK0351_UPD_DTM ") .append(" , KK0351.UPD_OPEACNT AS KK0351_UPD_OPEACNT ") .append(" , KK0351.DEL_DTM AS KK0351_DEL_DTM ") .append(" , KK0351.DEL_OPEACNT AS KK0351_DEL_OPEACNT ") .append(" , KK0351.MK_FLG AS KK0351_MK_FLG ") .append(" , KK0361.OP_SVC_KEI_NO AS KK0361_OP_SVC_KEI_NO ") .append(" , KK0361.GENE_ADD_DTM AS KK0361_GENE_ADD_DTM ") .append(" , KK0361.KIBO_ML_ACCOUNT_1 AS KIBO_ML_ACCOUNT_1 ") .append(" , KK0361.KIBO_ML_ACCOUNT_2 AS KIBO_ML_ACCOUNT_2 ") .append(" , KK0361.KIBO_ML_ACCOUNT_3 AS KIBO_ML_ACCOUNT_3 ") .append(" , KK0361.MLTISE_SHK_NINSHO_ID_PWD AS MLTISE_SHK_NINSHO_ID_PWD ") .append(" , KK0361.MLTISE_NINSHO_ID AS MLTISE_NINSHO_ID ") .append(" , KK0361.MLTISE_NINSHO_ID_PWD AS MLTISE_NINSHO_ID_PWD ") .append(" , KK0361.KOTEI_IP_AD AS KOTEI_IP_AD ") .append(" , KK0361.NETMASK AS NETMASK ") .append(" , KK0361.SHK_POP_ID_PWD AS SHK_POP_ID_PWD ") .append(" , KK0361.POP_ID AS POP_ID ") .append(" , KK0361.MLAD AS MLAD ") .append(" , KK0361.MLAD_CHGE_YMD AS MLAD_CHGE_YMD ") .append(" , KK0361.CAPA AS CAPA ") .append(" , KK0361.SHK_WEB_ID_PWD AS SHK_WEB_ID_PWD ") .append(" , KK0361.WEB_ID AS WEB_ID ") .append(" , KK0361.URL_DOMAIN AS URL_DOMAIN ") .append(" , KK0361.URL_ACCOUNT AS URL_ACCOUNT ") .append(" , KK0361.MLLIST_NM AS MLLIST_NM ") .append(" , KK0361.MLLIST_KOSHO AS MLLIST_KOSHO ") .append(" , KK0361.MLLIST_KOKAI_YH AS MLLIST_KOKAI_YH ") .append(" , KK0361.MLLIST_MAX_USER_CNT AS MLLIST_MAX_USER_CNT ") .append(" , KK0361.MLLIST_KRISHA_MLAD AS MLLIST_KRISHA_MLAD ") .append(" , KK0361.DOKUJI_DOMAIN_NM AS DOKUJI_DOMAIN_NM ") .append(" , KK0361.HOSML_ACCOUNT_CNT AS HOSML_ACCOUNT_CNT ") .append(" , KK0361.HOSML_BOX_CAPA AS HOSML_BOX_CAPA ") .append(" , KK0361.HOSML_KRISHA_ID AS HOSML_KRISHA_ID ") .append(" , KK0361.HOSTING_SHKM_KRISHA_ID_PWD AS HOSTING_SHKM_KRISHA_ID_PWD ") .append(" , KK0361.HOSML_KRISHA_POP_ID AS HOSML_KRISHA_POP_ID ") .append(" , KK0361.HOS_SHKM_KRISHA_POP_ID_PWD AS HOS_SHKM_KRISHA_POP_ID_PWD ") .append(" , KK0361.HOSML_KRISHA_MLAD AS HOSML_KRISHA_MLAD ") .append(" , KK0361.HOSML_KRISHA_MAIL_BOX_CAPA AS HOSML_KRISHA_MAIL_BOX_CAPA ") .append(" , KK0361.DPAD_ID AS DPAD_ID ") .append(" , KK0361.DPAD_ISP_ID AS DPAD_ISP_ID ") .append(" , KK0361.DPAD_MAPID AS DPAD_MAPID ") .append(" , KK0361.DPAD_PIN AS DPAD_PIN ") .append(" , KK0361.DPAD_MLAD AS DPAD_MLAD ") .append(" , KK0361.DPAD_ID_KOKAI_YH AS DPAD_ID_KOKAI_YH ") .append(" , KK0361.DUPCON_NINSHO_ID AS DUPCON_NINSHO_ID ") .append(" , KK0361.VOIP_USER_ID AS VOIP_USER_ID ") .append(" , KK0361.VOIP_TELNO AS VOIP_TELNO ") .append(" , KK0361.DNS_SETTE_CD AS DNS_SETTE_CD ") .append(" , KK0361.MAIL_GW_KRISHA_ID AS MAIL_GW_KRISHA_ID ") .append(" , KK0361.MAIL_GW_KRISHA_ID_PWD AS MAIL_GW_KRISHA_ID_PWD ") .append(" , KK0361.MAIL_GW_KRISHA_MLAD AS MAIL_GW_KRISHA_MLAD ") .append(" , KK0361.MAIL_GW_RCV_MSERVER_IP_AD AS MAIL_GW_RCV_MSERVER_IP_AD ") .append(" , KK0361.MAIL_GW_SEND_MSERVER_IP_AD AS MAIL_GW_SEND_MSERVER_IP_AD ") .append(" , KK0361.ML_GW_ACCOUNT_CNT AS ML_GW_ACCOUNT_CNT ") .append(" , KK0361.KOSHUMO_TELNO AS KOSHUMO_TELNO ") .append(" , KK0361.SPOT_YGSITEBLCK_TRGT_SYSID AS SPOT_YGSITEBLCK_TRGT_SYSID ") .append(" , KK0361.SIP_ADD_STAT_CD AS SIP_ADD_STAT_CD ") .append(" , KK0361.SVC_KBT_SET_RRKS_MLAD AS SVC_KBT_SET_RRKS_MLAD ") .append(" , KK0361.TUWA_DTL_KOKAI_YH AS TUWA_DTL_KOKAI_YH ") .append(" , KK0361.RSV_APLY_CD AS KK0361_RSV_APLY_CD ") .append(" , KK0361.TSYRN_SVKEI_CD_SEQ AS TSYRN_SVKEI_CD_SEQ ") .append(" , KK0361.ADD_DTM AS KK0361_ADD_DTM ") .append(" , KK0361.ADD_OPEACNT AS KK0361_ADD_OPEACNT ") .append(" , KK0361.UPD_DTM AS KK0361_UPD_DTM ") .append(" , KK0361.UPD_OPEACNT AS KK0361_UPD_OPEACNT ") .append(" , KK0361.DEL_DTM AS KK0361_DEL_DTM ") .append(" , KK0361.DEL_OPEACNT AS KK0361_DEL_OPEACNT ") .append(" , KK0361.MK_FLG AS KK0361_MK_FLG ") .append(" , KK0391.OP_SVC_KEI_NO AS KK0391_OP_SVC_KEI_NO ") .append(" , KK0391.GENE_ADD_DTM AS KK0391_GENE_ADD_DTM ") .append(" , KK0391.RSV_APLY_CD AS KK0391_RSV_APLY_CD ") .append(" , KK0391.ADD_DTM AS KK0391_ADD_DTM ") .append(" , KK0391.ADD_OPEACNT AS KK0391_ADD_OPEACNT ") .append(" , KK0391.UPD_DTM AS KK0391_UPD_DTM ") .append(" , KK0391.UPD_OPEACNT AS KK0391_UPD_OPEACNT ") .append(" , KK0391.DEL_DTM AS KK0391_DEL_DTM ") .append(" , KK0391.DEL_OPEACNT AS KK0391_DEL_OPEACNT ") .append(" , KK0391.MK_FLG AS KK0391_MK_FLG ") .append(" , KK0371.OP_SVC_KEI_NO AS KK0371_OP_SVC_KEI_NO ") .append(" , KK0371.GENE_ADD_DTM AS KK0371_GENE_ADD_DTM ") .append(" , KK0371.N_050_OP_TELNO AS N_050_OP_TELNO ") .append(" , KK0371.RSV_APLY_CD AS KK0371_RSV_APLY_CD ") .append(" , KK0371.ADD_DTM AS KK0371_ADD_DTM ") .append(" , KK0371.ADD_OPEACNT AS KK0371_ADD_OPEACNT ") .append(" , KK0371.UPD_DTM AS KK0371_UPD_DTM ") .append(" , KK0371.UPD_OPEACNT AS KK0371_UPD_OPEACNT ") .append(" , KK0371.DEL_DTM AS KK0371_DEL_DTM ") .append(" , KK0371.DEL_OPEACNT AS KK0371_DEL_OPEACNT ") .append(" , KK0371.MK_FLG AS KK0371_MK_FLG ") .append(" , KK0381.OP_SVC_KEI_NO AS KK0381_OP_SVC_KEI_NO ") .append(" , KK0381.GENE_ADD_DTM AS KK0381_GENE_ADD_DTM ") .append(" , KK0381.NHK_GRP_IKT_PAY_CYCLE_CD AS NHK_GRP_IKT_PAY_CYCLE_CD ") .append(" , KK0381.FIRST_SEIYMD AS FIRST_SEIYMD ") .append(" , KK0381.CABLE_GUIDE_MSKM_CNT AS CABLE_GUIDE_MSKM_CNT ") .append(" , KK0381.GUIDEMG_SBT_CD AS GUIDEMG_SBT_CD ") .append(" , KK0381.GUIDEMG_HASSO_WAY_CD AS GUIDEMG_HASSO_WAY_CD ") .append(" , KK0381.GUIDEMG_SOHUS_NM AS GUIDEMG_SOHUS_NM ") .append(" , KK0381.GUIDEMG_SOHUS_KANA AS GUIDEMG_SOHUS_KANA ") .append(" , KK0381.GUIDEMG_SOHUS_AD_CD AS GUIDEMG_SOHUS_AD_CD ") .append(" , KK0381.GUIDEMG_SOHUS_PCD AS GUIDEMG_SOHUS_PCD ") .append(" , KK0381.GUIDEMG_SOHUS_STATE_NM AS GUIDEMG_SOHUS_STATE_NM ") .append(" , KK0381.GUIDEMG_SOHUS_CITY_NM AS GUIDEMG_SOHUS_CITY_NM ") .append(" , KK0381.GUIDEMG_SOHUS_OAZTSU_NM AS GUIDEMG_SOHUS_OAZTSU_NM ") .append(" , KK0381.GUIDEMG_SOHUS_AZCHO_NM AS GUIDEMG_SOHUS_AZCHO_NM ") .append(" , KK0381.GUIDEMG_SOHUS_BNCHIGO AS GUIDEMG_SOHUS_BNCHIGO ") .append(" , KK0381.GUIDEMG_SOHUS_ADRTTM AS GUIDEMG_SOHUS_ADRTTM ") .append(" , KK0381.GUIDEMG_SOHUS_ADRRM AS GUIDEMG_SOHUS_ADRRM ") .append(" , KK0381.GUIDEMG_SOHUS_TELNO AS GUIDEMG_SOHUS_TELNO ") .append(" , KK0381.GUIDEMG_SHS_KSITEI_FLG AS GUIDEMG_SHS_KSITEI_FLG ") .append(" , KK0381.NHK_CUST_NO AS NHK_CUST_NO ") .append(" , KK0381.SHUK_YMD AS SHUK_YMD ") .append(" , KK0381.NHK_DNTIKTKEI_HKTDK_YMD AS NHK_DNTIKTKEI_HKTDK_YMD ") .append(" , KK0381.NHK_DNTIKTKEI_CHGRE_MEMO AS NHK_DNTIKTKEI_CHGRE_MEMO ") .append(" , KK0381.RED_BCAS_NO AS RED_BCAS_NO ") .append(" , KK0381.RSV_APLY_CD AS KK0381_RSV_APLY_CD ") .append(" , KK0381.ADD_DTM AS KK0381_ADD_DTM ") .append(" , KK0381.ADD_OPEACNT AS KK0381_ADD_OPEACNT ") .append(" , KK0381.UPD_DTM AS KK0381_UPD_DTM ") .append(" , KK0381.UPD_OPEACNT AS KK0381_UPD_OPEACNT ") .append(" , KK0381.DEL_DTM AS KK0381_DEL_DTM ") .append(" , KK0381.DEL_OPEACNT AS KK0381_DEL_OPEACNT ") .append(" , KK0381.MK_FLG AS KK0381_MK_FLG ") .append(" ,( SELECT KK0821.OP_SVC_NM ") .append(" FROM KK_M_OP_SVC KK0821 ") .append(" WHERE KK0821.OP_SVC_CD = KK0351.OP_SVC_CD ") .append(" AND (KK0821.OP_SVC_CD, KK0821.RSV_APLY_YMD || KK0821.GENE_ADD_DTM) = ") .append(" ( SELECT KK0821_GENE.OP_SVC_CD, MAX(KK0821_GENE.RSV_APLY_YMD || KK0821_GENE.GENE_ADD_DTM) ") .append(" FROM KK_M_OP_SVC KK0821_GENE ") .append(" WHERE KK0821_GENE.OP_SVC_CD = KK0821.OP_SVC_CD ") .append(" AND KK0821_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0821_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0821_GENE.OP_SVC_CD ) ") .append(" ) AS OP_SVC_NM ") .append(" , KK0361.IPV6_AD_IFID AS IPV6_AD_IFID ") .append(" , KK0371.TWRYO_STIAM_TTU_TCHI_STIAM AS TWRYO_STIAM_TTU_TCHI_STIAM ") .append(" , KK0381.NHK_DNTIKTKEI_NHK_CNC_YMD ") .append(" , KK0381.NHK_DNTIKTKEI_ADD_MEMO ") .append(" ,( SELECT USER_NM ") .append(" FROM ZM_M_USER ZM0051 ") .append(" WHERE ZM0051.USER_ID = KK0351.UPD_OPEACNT ") .append(" AND ZM0051.MK_FLG = '0' ") .append(" ) AS KK0351_UPD_OPEACNT_NM ") .append(" , KK0361.FMTCEL_JGS_CD ") .append(" , KK0361.FMTCEL_JGS_USE_CD ") .append(" , KK0361.FMTCEL_NINSHO_ID ") .append(" , KK0361.FMTCEL_NINSHO_ID_PWD ") .append(" , KK0361.MLTISE_SESSION_CNT ") .append(" , KK0361.WISH_OWNR_ID ") .append(" FROM KK_T_OP_SVC_KEI KK0351 ") .append(" LEFT JOIN KK_T_OPSVKEI_ISP KK0361 ") .append(" ON KK0351.OP_SVC_KEI_NO = KK0361.OP_SVC_KEI_NO ") .append(" AND KK0351.GENE_ADD_DTM = KK0361.GENE_ADD_DTM ") .append(" LEFT JOIN KK_T_OPSVKEI_TEL KK0371 ") .append(" ON KK0351.OP_SVC_KEI_NO = KK0371.OP_SVC_KEI_NO ") .append(" AND KK0351.GENE_ADD_DTM = KK0371.GENE_ADD_DTM ") .append(" LEFT JOIN KK_T_OPSVKEI_TV KK0381 ") .append(" ON KK0351.OP_SVC_KEI_NO = KK0381.OP_SVC_KEI_NO ") .append(" AND KK0351.GENE_ADD_DTM = KK0381.GENE_ADD_DTM ") .append(" LEFT JOIN KK_T_OPSVKEI_SUPPORT KK0391 ") .append(" ON KK0351.OP_SVC_KEI_NO = KK0391.OP_SVC_KEI_NO ") .append(" AND KK0351.GENE_ADD_DTM = KK0391.GENE_ADD_DTM "); // SQL文_個別部1 if ("1".equals(inMsg.getObject(EKK0351B003CBSMsg.FUNC_CODE).toString())) { sql_Buff.append(" WHERE (KK0351.OP_SVC_KEI_NO, KK0351.GENE_ADD_DTM) IN ( "); // ＫＥＹ＿サービス契約番号が設定されている場合 if (!inMsg.isNull(EKK0351B003CBSMsg.KEY_SVC_KEI_NO)) { sql_Buff.append(" SELECT KK0351_01.OP_SVC_KEI_NO, KK0351_01.GENE_ADD_DTM ") .append(" FROM KK_T_OP_SVC_KEI KK0351_01 ") .append(" WHERE KK0351_01.OYA_KEI_SKBT_CD = '01' ") .append(" AND KK0351_01.SVC_KEI_NO = ? ") .append(" AND KK0351_01.MK_FLG = '0' "); } // ＫＥＹ＿サービス契約番号、ＫＥＹ＿サービス契約内訳番号１〜５が設定されている場合 if (!inMsg.isNull(EKK0351B003CBSMsg.KEY_SVC_KEI_NO) && 0L != arraySvkiuwkno.size()) { sql_Buff.append(" UNION ALL "); } // ＫＥＹ＿サービス契約内訳番号１〜５が設定されている場合 if (0L != arraySvkiuwkno.size()) { sql_Buff.append(" SELECT KK0351_02.OP_SVC_KEI_NO, KK0351_02.GENE_ADD_DTM ") .append(" FROM KK_T_OP_SVC_KEI KK0351_02 ") .append(" WHERE KK0351_02.OYA_KEI_SKBT_CD = '03' ") .append(" AND KK0351_02.SVC_KEI_UCWK_NO IN ( ") .append(strBufBind.toString()) .append(" ) ") .append(" AND KK0351_02.MK_FLG = '0' "); } // ▼▼▼ OM-2017-0000593 ADD START // ＫＥＹ＿オプションサービス契約番号が設定されている場合 if (!inMsg.isNull(EKK0351B003CBSMsg.KEY_OP_SVC_KEI_NO)) { // ＫＥＹ＿サービス契約番号、ＫＥＹ＿サービス契約内訳番号１〜５のどちらかが設定されている場合 if (!inMsg.isNull(EKK0351B003CBSMsg.KEY_SVC_KEI_NO) || 0L != arraySvkiuwkno.size()) { sql_Buff.append(" UNION ALL "); } sql_Buff.append(" SELECT KK0351_03.OP_SVC_KEI_NO, KK0351_03.GENE_ADD_DTM ") .append(" FROM KK_T_OP_SVC_KEI KK0351_03 ") .append(" WHERE KK0351_03.OP_SVC_KEI_NO = ? ") .append(" AND KK0351_03.MK_FLG = '0' "); } // ▲▲▲ OM-2017-0000593 ADD END sql_Buff.append(" ) ") .append(" ORDER BY KK0351.OP_SVC_KEI_NO ASC, KK0351.GENE_ADD_DTM DESC "); } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // パラメータのカウント int iPCnt = 0; // PreparedStatementの指定されたパラメータカラムに 指定された値を設定 if ("1".equals(inMsg.getString(EKK0351B003CBSMsg.FUNC_CODE))) { // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); if (!inMsg.isNull(EKK0351B003CBSMsg.KEY_SVC_KEI_NO)) { // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0351B003CBSMsg.KEY_SVC_KEI_NO)); } // パラメータの設定(ＫＥＹ＿サービス契約内訳番号を指定) if (0L != arraySvkiuwkno.size()) { // パラメータの設定 int iCnt = 0; for(iCnt = 0; iCnt < arraySvkiuwkno.size(); iCnt++) { // バインド変数に設定 CAANJDBCUtil.setParam(pstmt, ++iPCnt, arraySvkiuwkno.get(iCnt)); } } // ▼▼▼ OM-2017-0000593 ADD START if (!inMsg.isNull(EKK0351B003CBSMsg.KEY_OP_SVC_KEI_NO)) { // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0351B003CBSMsg.KEY_OP_SVC_KEI_NO)); } // ▲▲▲ OM-2017-0000593 ADD END } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EKK0351B003CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EKK0351B003CBSMsg1List.KK0351_OP_SVC_KEI_NO , EKK0351B003CBSMsg1List.KK0351_GENE_ADD_DTM , EKK0351B003CBSMsg1List.OP_SVC_KEI_STAT , EKK0351B003CBSMsg1List.OP_SVC_CD , EKK0351B003CBSMsg1List.PCRS_CD , EKK0351B003CBSMsg1List.PPLAN_CD , EKK0351B003CBSMsg1List.OYA_KEI_SKBT_CD , EKK0351B003CBSMsg1List.SVC_KEI_NO , EKK0351B003CBSMsg1List.SVC_KEI_UCWK_NO , EKK0351B003CBSMsg1List.SYSID , EKK0351B003CBSMsg1List.MSKM_DTL_NO , EKK0351B003CBSMsg1List.FTRIAL_KANYU_YMD , EKK0351B003CBSMsg1List.FTRIAL_PRD_ENDYMD , EKK0351B003CBSMsg1List.HONKANYU_YMD , EKK0351B003CBSMsg1List.HONKANYU_IKO_KIGEN_YMD , EKK0351B003CBSMsg1List.SVC_USE_STA_KIBO_YMD , EKK0351B003CBSMsg1List.RSV_TSTA_KIBO_YMD , EKK0351B003CBSMsg1List.OP_SVC_KEI_KZKWRK_REQYMD , EKK0351B003CBSMsg1List.SHOSA_YMD , EKK0351B003CBSMsg1List.SHOSA_CL_YMD , EKK0351B003CBSMsg1List.SKEKKA_SEND_CD , EKK0351B003CBSMsg1List.KEI_CNC_YMD , EKK0351B003CBSMsg1List.RSV_APLY_YMD , EKK0351B003CBSMsg1List.RSV_CL_YMD , EKK0351B003CBSMsg1List.KK0351_RSV_APLY_CD , EKK0351B003CBSMsg1List.PLAN_STAYMD , EKK0351B003CBSMsg1List.PLAN_ENDYMD , EKK0351B003CBSMsg1List.PLAN_CHRG_STAYMD , EKK0351B003CBSMsg1List.PLAN_CHRG_ENDYMD , EKK0351B003CBSMsg1List.PLAN_END_SBT_CD , EKK0351B003CBSMsg1List.SVC_CANCEL_YMD , EKK0351B003CBSMsg1List.SVC_CANCEL_RSN_CD , EKK0351B003CBSMsg1List.SVC_STAYMD , EKK0351B003CBSMsg1List.SVC_CHRG_STAYMD , EKK0351B003CBSMsg1List.SVC_STP_YMD , EKK0351B003CBSMsg1List.SVC_STP_RSN_CD , EKK0351B003CBSMsg1List.SVC_STP_RLS_YMD , EKK0351B003CBSMsg1List.SVC_STP_RLS_RSN_CD , EKK0351B003CBSMsg1List.PAUSE_STP_CD , EKK0351B003CBSMsg1List.SVC_PAUSE_YMD , EKK0351B003CBSMsg1List.SVC_PAUSE_RSN_CD , EKK0351B003CBSMsg1List.SVC_PAUSE_RSN_MEMO , EKK0351B003CBSMsg1List.SVC_PAUSE_RLS_YMD , EKK0351B003CBSMsg1List.SVC_PAUSE_RLS_RSN_CD , EKK0351B003CBSMsg1List.SVC_PAUSE_RLS_RSN_MEMO , EKK0351B003CBSMsg1List.SVC_ENDYMD , EKK0351B003CBSMsg1List.SVC_CHRG_ENDYMD , EKK0351B003CBSMsg1List.SVC_DSL_YMD , EKK0351B003CBSMsg1List.SVC_DLRE_CD , EKK0351B003CBSMsg1List.SVC_DLRE_MEMO , EKK0351B003CBSMsg1List.DSL_TNT_USER_ID , EKK0351B003CBSMsg1List.SVC_DSL_TTDKI_FIN_FLG , EKK0351B003CBSMsg1List.KAIHK_YMD , EKK0351B003CBSMsg1List.SVC_CANCEL_CL_YMD , EKK0351B003CBSMsg1List.SVC_DSL_CL_YMD , EKK0351B003CBSMsg1List.CHRG_STA_YMD_HOSEI_UM , EKK0351B003CBSMsg1List.SVC_PAUSE_CHRG_STA_YMD , EKK0351B003CBSMsg1List.OP_SVC_KEI_HKHASYMD , EKK0351B003CBSMsg1List.PNLTY_HASSEI_CD , EKK0351B003CBSMsg1List.IDO_DIV , EKK0351B003CBSMsg1List.SHOSA_DSL_FIN_CD , EKK0351B003CBSMsg1List.SVCTK_BUT_DEL_TRN_JSSI_DTM , EKK0351B003CBSMsg1List.KK0351_ADD_DTM , EKK0351B003CBSMsg1List.KK0351_ADD_OPEACNT , EKK0351B003CBSMsg1List.KK0351_ADD_OPEACNT_NM , EKK0351B003CBSMsg1List.KK0351_UPD_DTM , EKK0351B003CBSMsg1List.KK0351_UPD_OPEACNT , EKK0351B003CBSMsg1List.KK0351_DEL_DTM , EKK0351B003CBSMsg1List.KK0351_DEL_OPEACNT , EKK0351B003CBSMsg1List.KK0351_MK_FLG , EKK0351B003CBSMsg1List.KK0361_OP_SVC_KEI_NO , EKK0351B003CBSMsg1List.KK0361_GENE_ADD_DTM , EKK0351B003CBSMsg1List.KIBO_ML_ACCOUNT_1 , EKK0351B003CBSMsg1List.KIBO_ML_ACCOUNT_2 , EKK0351B003CBSMsg1List.KIBO_ML_ACCOUNT_3 , EKK0351B003CBSMsg1List.MLTISE_SHK_NINSHO_ID_PWD , EKK0351B003CBSMsg1List.MLTISE_NINSHO_ID , EKK0351B003CBSMsg1List.MLTISE_NINSHO_ID_PWD , EKK0351B003CBSMsg1List.KOTEI_IP_AD , EKK0351B003CBSMsg1List.NETMASK , EKK0351B003CBSMsg1List.SHK_POP_ID_PWD , EKK0351B003CBSMsg1List.POP_ID , EKK0351B003CBSMsg1List.MLAD , EKK0351B003CBSMsg1List.MLAD_CHGE_YMD , EKK0351B003CBSMsg1List.CAPA , EKK0351B003CBSMsg1List.SHK_WEB_ID_PWD , EKK0351B003CBSMsg1List.WEB_ID , EKK0351B003CBSMsg1List.URL_DOMAIN , EKK0351B003CBSMsg1List.URL_ACCOUNT , EKK0351B003CBSMsg1List.MLLIST_NM , EKK0351B003CBSMsg1List.MLLIST_KOSHO , EKK0351B003CBSMsg1List.MLLIST_KOKAI_YH , EKK0351B003CBSMsg1List.MLLIST_MAX_USER_CNT , EKK0351B003CBSMsg1List.MLLIST_KRISHA_MLAD , EKK0351B003CBSMsg1List.DOKUJI_DOMAIN_NM , EKK0351B003CBSMsg1List.HOSML_ACCOUNT_CNT , EKK0351B003CBSMsg1List.HOSML_BOX_CAPA , EKK0351B003CBSMsg1List.HOSML_KRISHA_ID , EKK0351B003CBSMsg1List.HOSTING_SHKM_KRISHA_ID_PWD , EKK0351B003CBSMsg1List.HOSML_KRISHA_POP_ID , EKK0351B003CBSMsg1List.HOS_SHKM_KRISHA_POP_ID_PWD , EKK0351B003CBSMsg1List.HOSML_KRISHA_MLAD , EKK0351B003CBSMsg1List.HOSML_KRISHA_MAIL_BOX_CAPA , EKK0351B003CBSMsg1List.DPAD_ID , EKK0351B003CBSMsg1List.DPAD_ISP_ID , EKK0351B003CBSMsg1List.DPAD_MAPID , EKK0351B003CBSMsg1List.DPAD_PIN , EKK0351B003CBSMsg1List.DPAD_MLAD , EKK0351B003CBSMsg1List.DPAD_ID_KOKAI_YH , EKK0351B003CBSMsg1List.DUPCON_NINSHO_ID , EKK0351B003CBSMsg1List.VOIP_USER_ID , EKK0351B003CBSMsg1List.VOIP_TELNO , EKK0351B003CBSMsg1List.DNS_SETTE_CD , EKK0351B003CBSMsg1List.MAIL_GW_KRISHA_ID , EKK0351B003CBSMsg1List.MAIL_GW_KRISHA_ID_PWD , EKK0351B003CBSMsg1List.MAIL_GW_KRISHA_MLAD , EKK0351B003CBSMsg1List.MAIL_GW_RCV_MSERVER_IP_AD , EKK0351B003CBSMsg1List.MAIL_GW_SEND_MSERVER_IP_AD , EKK0351B003CBSMsg1List.ML_GW_ACCOUNT_CNT , EKK0351B003CBSMsg1List.KOSHUMO_TELNO , EKK0351B003CBSMsg1List.SPOT_YGSITEBLCK_TRGT_SYSID , EKK0351B003CBSMsg1List.SIP_ADD_STAT_CD , EKK0351B003CBSMsg1List.SVC_KBT_SET_RRKS_MLAD , EKK0351B003CBSMsg1List.TUWA_DTL_KOKAI_YH , EKK0351B003CBSMsg1List.KK0361_RSV_APLY_CD , EKK0351B003CBSMsg1List.TSYRN_SVKEI_CD_SEQ , EKK0351B003CBSMsg1List.KK0361_ADD_DTM , EKK0351B003CBSMsg1List.KK0361_ADD_OPEACNT , EKK0351B003CBSMsg1List.KK0361_UPD_DTM , EKK0351B003CBSMsg1List.KK0361_UPD_OPEACNT , EKK0351B003CBSMsg1List.KK0361_DEL_DTM , EKK0351B003CBSMsg1List.KK0361_DEL_OPEACNT , EKK0351B003CBSMsg1List.KK0361_MK_FLG , EKK0351B003CBSMsg1List.KK0391_OP_SVC_KEI_NO , EKK0351B003CBSMsg1List.KK0391_GENE_ADD_DTM , EKK0351B003CBSMsg1List.KK0391_RSV_APLY_CD , EKK0351B003CBSMsg1List.KK0391_ADD_DTM , EKK0351B003CBSMsg1List.KK0391_ADD_OPEACNT , EKK0351B003CBSMsg1List.KK0391_UPD_DTM , EKK0351B003CBSMsg1List.KK0391_UPD_OPEACNT , EKK0351B003CBSMsg1List.KK0391_DEL_DTM , EKK0351B003CBSMsg1List.KK0391_DEL_OPEACNT , EKK0351B003CBSMsg1List.KK0391_MK_FLG , EKK0351B003CBSMsg1List.KK0371_OP_SVC_KEI_NO , EKK0351B003CBSMsg1List.KK0371_GENE_ADD_DTM , EKK0351B003CBSMsg1List.N_050_OP_TELNO , EKK0351B003CBSMsg1List.KK0371_RSV_APLY_CD , EKK0351B003CBSMsg1List.KK0371_ADD_DTM , EKK0351B003CBSMsg1List.KK0371_ADD_OPEACNT , EKK0351B003CBSMsg1List.KK0371_UPD_DTM , EKK0351B003CBSMsg1List.KK0371_UPD_OPEACNT , EKK0351B003CBSMsg1List.KK0371_DEL_DTM , EKK0351B003CBSMsg1List.KK0371_DEL_OPEACNT , EKK0351B003CBSMsg1List.KK0371_MK_FLG , EKK0351B003CBSMsg1List.KK0381_OP_SVC_KEI_NO , EKK0351B003CBSMsg1List.KK0381_GENE_ADD_DTM , EKK0351B003CBSMsg1List.NHK_GRP_IKT_PAY_CYCLE_CD , EKK0351B003CBSMsg1List.FIRST_SEIYMD , EKK0351B003CBSMsg1List.CABLE_GUIDE_MSKM_CNT , EKK0351B003CBSMsg1List.GUIDEMG_SBT_CD , EKK0351B003CBSMsg1List.GUIDEMG_HASSO_WAY_CD , EKK0351B003CBSMsg1List.GUIDEMG_SOHUS_NM , EKK0351B003CBSMsg1List.GUIDEMG_SOHUS_KANA , EKK0351B003CBSMsg1List.GUIDEMG_SOHUS_AD_CD , EKK0351B003CBSMsg1List.GUIDEMG_SOHUS_PCD , EKK0351B003CBSMsg1List.GUIDEMG_SOHUS_STATE_NM , EKK0351B003CBSMsg1List.GUIDEMG_SOHUS_CITY_NM , EKK0351B003CBSMsg1List.GUIDEMG_SOHUS_OAZTSU_NM , EKK0351B003CBSMsg1List.GUIDEMG_SOHUS_AZCHO_NM , EKK0351B003CBSMsg1List.GUIDEMG_SOHUS_BNCHIGO , EKK0351B003CBSMsg1List.GUIDEMG_SOHUS_ADRTTM , EKK0351B003CBSMsg1List.GUIDEMG_SOHUS_ADRRM , EKK0351B003CBSMsg1List.GUIDEMG_SOHUS_TELNO , EKK0351B003CBSMsg1List.GUIDEMG_SHS_KSITEI_FLG , EKK0351B003CBSMsg1List.NHK_CUST_NO , EKK0351B003CBSMsg1List.SHUK_YMD , EKK0351B003CBSMsg1List.NHK_DNTIKTKEI_HKTDK_YMD , EKK0351B003CBSMsg1List.NHK_DNTIKTKEI_CHGRE_MEMO , EKK0351B003CBSMsg1List.RED_BCAS_NO , EKK0351B003CBSMsg1List.KK0381_RSV_APLY_CD , EKK0351B003CBSMsg1List.KK0381_ADD_DTM , EKK0351B003CBSMsg1List.KK0381_ADD_OPEACNT , EKK0351B003CBSMsg1List.KK0381_UPD_DTM , EKK0351B003CBSMsg1List.KK0381_UPD_OPEACNT , EKK0351B003CBSMsg1List.KK0381_DEL_DTM , EKK0351B003CBSMsg1List.KK0381_DEL_OPEACNT , EKK0351B003CBSMsg1List.KK0381_MK_FLG , EKK0351B003CBSMsg1List.OP_SVC_NM , EKK0351B003CBSMsg1List.IPV6_AD_IFID , EKK0351B003CBSMsg1List.TWRYO_STIAM_TTU_TCHI_STIAM , EKK0351B003CBSMsg1List.NHK_DNTIKTKEI_NHK_CNC_YMD , EKK0351B003CBSMsg1List.NHK_DNTIKTKEI_ADD_MEMO , EKK0351B003CBSMsg1List.KK0351_UPD_OPEACNT_NM , EKK0351B003CBSMsg1List.FMTCEL_JGS_CD , EKK0351B003CBSMsg1List.FMTCEL_JGS_USE_CD , EKK0351B003CBSMsg1List.FMTCEL_NINSHO_ID , EKK0351B003CBSMsg1List.FMTCEL_NINSHO_ID_PWD , EKK0351B003CBSMsg1List.MLTISE_SESSION_CNT , EKK0351B003CBSMsg1List.WISH_OWNR_ID }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0351B003CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("EKK0351B003CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(EKK0351B003CBSMsg.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(EKK0351B003CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } /** *

* サービスＩＦ入力値の判定を行ないます。 *

* @param inMsg 入力値の格納されたCBSMsg * @param strBufBind バインド文字列 * @param arraySvkiuwkno バインド変数のリスト(サービス契約内訳番号) * @param strKey 項目を取得するためのキー */ private void createSvkiuwkno(CAANMsg inMsg, StringBuffer strBufBind, ArrayList arraySvkiuwkno, String strKey) { if (!inMsg.isNull(strKey)) { if(0L != strBufBind.length()) { strBufBind.append(Comma); } strBufBind.append(bind); arraySvkiuwkno.add(inMsg.getObject(strKey)); } } }