/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名 ：eo顧客基幹システム * モジュール名 ：JSYejbEKK0081B014TPDA * ソースファイル名：JSYejbEKK0081B014TPDA.java * 作成者 ：EK908586 * 日付 ：2013年10月28日 *＜機能概要＞ * サービス契約一覧照会（複数指定）テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン 修正日 修正者 修正内容 * ｖ1.00.00 * ｖ4.00.00 2013/01/21 FJ)沖田 ST1-2012-0000846 * ｖ4.00.01 2013/02/01 FJ)沖田 IT1-2013-0000186 * ｖ4.00.02 2013/02/06 FJ)坂本 ST1-2012-0000846 * ｖ5.00.00 2013/03/04 FJ)寺園 ANK-1477-00-00 * ｖ5.00.01 2013/03/29 FJ)沖田 OT-2013-0000455 * ｖ5.00.02 2013/07/12 FJ)沖田 LT-2013-0000469 * ｖ6.00.00 2013/10/18 FJ)伊藤 OM-2013-0003436 * ｖ6.00.01 2013/10/28 FJ)日野 ANK-1636-00-00　客サ起票分No32住所変更受付(HT⇒HT)対応 * ｖ32.00.00 2017/05/25 FJ)麻生 ANK-3153-00-00　仮申込の契約状態表示変更対応 * v41.00.00 2019/02/04 FJ)舘山 【ANK-3387-00-00】 マンションタイプ光コンセントサービス導入 * v53.00.00 2021/04/12 FJ)宮下 【ANK-3989-00-00】 マンション光コンセント方式への5G・10Gコース追加 * v60.00.00 2022/06/09 FJ)原 【ANK-4244-00-00】【eo定期】MT既設賃貸対応 ********************************************************************************/ package eo.ejb.cbs.sqlf; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; 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.KK0081ETMsg; import eo.ejb.cbs.cbsmsg.EKK0081B014CBSMsg; import eo.ejb.cbs.cbsmsg.EKK0081B014CBSMsg1List; import eo.ejb.common.JKKModelCommon; import eo.common.constant.JKKStrConst; /** * * テンプレートDBアクセス部品　EKK0081B014_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEKK0081B014TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbEKK0081B014TPDA() { } /** * EKK0081B014_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(KK0081ETMsg.getTableName()); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT ") .append(" KK0081.SVC_KEI_NO, ") .append(" KK0081.PPLAN_CD, ") // ▽▽▽ ANK-3387-00-00 ADD START // .append(" (SELECT KK0591.PPLAN_NM ") .append(" (SELECT ") .append(" CASE WHEN KK0891.TK_HOSHIKI_CD = '008' AND KK0891.TK_HOSHIKI_PATTERN_CD = '51' ") // ▽▽▽ ANK-3989-00-00 MOD START // .append(" THEN REPLACE(KK0591.PPLAN_NM, '") // .append(JKKStrConst.HIKARI_CONCNT_CKAN_BF_WORD).append("', '") // .append(JKKStrConst.HIKARI_CONCNT_CKAN_AF_WORD).append("') ") .append(" THEN CASE WHEN KK0891.TONAI_SPEED_CD = '008' AND KK0081.SVC_CD = '01' ") .append(" THEN '") .append(JKKStrConst.HIKARI_CONCNT_CKAN_AF_WORD_5G).append("' ") .append(" WHEN KK0891.TONAI_SPEED_CD = '009' AND KK0081.SVC_CD = '01' ") .append(" THEN '") .append(JKKStrConst.HIKARI_CONCNT_CKAN_AF_WORD_10G).append("' ") .append(" ELSE REPLACE(KK0591.PPLAN_NM, '") .append(JKKStrConst.HIKARI_CONCNT_CKAN_BF_WORD).append("', '") .append(JKKStrConst.HIKARI_CONCNT_CKAN_AF_WORD).append("') ") .append(" END ") // ▲▲▲ ANK-3989-00-00 MOD END // ANK-4244-00-00 ADD START .append(" WHEN KK0891.TK_HOSHIKI_CD = '008' AND KK0891.TK_HOSHIKI_PATTERN_CD = '50' AND KK0881.HHS_1GONLY_FLG = '1' ") .append(" THEN CASE WHEN KK0891.TONAI_SPEED_CD = '008' AND KK0081.SVC_CD = '01' ") .append(" THEN '") .append(JKKStrConst.HIKARI_CKAN_AF_WORD_5G).append("' ") .append(" WHEN KK0891.TONAI_SPEED_CD = '009' AND KK0081.SVC_CD = '01' ") .append(" THEN '") .append(JKKStrConst.HIKARI_CKAN_AF_WORD_10G).append("' ") .append(" ELSE KK0591.PPLAN_NM ") .append(" END ") // ANK-4244-00-00 ADD END .append(" ELSE KK0591.PPLAN_NM ") .append(" END ") // ▲▲▲ ANK-3387-00-00 ADD END .append(" FROM KK_M_PPLAN KK0591 ") .append(" WHERE KK0591.PCRS_CD = KK0081.PCRS_CD ") .append(" AND KK0591.PPLAN_CD = KK0081.PPLAN_CD ") .append(" AND KK0591.MK_FLG = '0' ") .append(" ) AS PPLAN_CD_NM, ") .append(" KK0081.SVC_KEI_STAT, ") .append(" (SELECT ZM0171_01.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_01 ") .append(" WHERE ZM0171_01.CD_SBT_CD = 'CD00037' ") .append(" AND ZM0171_01.CD_DIV = KK0081.SVC_KEI_STAT ") .append(" AND ZM0171_01.MK_FLG = '0' ") .append(" ) AS SVC_KEI_STAT_NM, ") .append(" KK0081.IDO_NG_STAT_CD, ") .append(" (SELECT ZM0171_02.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_02 ") .append(" WHERE ZM0171_02.CD_SBT_CD = 'CD00280' ") .append(" AND ZM0171_02.CD_DIV = KK0081.IDO_NG_STAT_CD ") .append(" AND ZM0171_02.MK_FLG = '0' ") .append(" ) AS IDO_NG_STAT_CD_NM, ") .append(" CH0231.TOKUSOKU_STAT, ") .append(" (SELECT ZM0171_03.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_03 ") .append(" WHERE ZM0171_03.CD_SBT_CD = 'CD00184' ") .append(" AND ZM0171_03.CD_DIV = CH0231.TOKUSOKU_STAT ") .append(" AND ZM0171_03.MK_FLG = '0' ") .append(" ) AS TOKUSOKU_STAT_NM, ") .append(" KK0021.MSKM_FORM_CD, ") .append(" (SELECT ZM0171_04.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_04 ") .append(" WHERE ZM0171_04.CD_SBT_CD = 'CD00591' ") .append(" AND ZM0171_04.CD_DIV = KK0021.MSKM_FORM_CD ") .append(" AND ZM0171_04.MK_FLG = '0' ") .append(" ) AS MSKM_FORM_CD_NM, ") .append(" KK0081.ADD_DTM, ") .append(" KK0011.MSKM_UK_DTM, ") .append(" KK0021.MSKM_YMD, ") .append(" KK0071.AGNT_CD, ") .append(" KK0071.AGNT_CD_NM, ") .append(" KK0251.KAISEN_PLACE_PCD, ") .append(" KK0251.KAISEN_PLACE_STATE_NM, ") .append(" KK0251.KAISEN_PLACE_CITY_NM, ") .append(" KK0251.KAISEN_PLACE_OAZTSU_NM, ") .append(" KK0251.KAISEN_PLACE_AZCHO_NM, ") .append(" KK0251.KAISEN_PLACE_BNCHIGO, ") .append(" KK0251.KAISEN_PLACE_ADRTTM, ") .append(" KK0251.KAISEN_PLACE_ADRRM, ") .append(" KK0021.MSKMSHO_NO, ") .append(" KK0081.SVC_STA_YMD, ") .append(" KK0081.SVC_CHRG_STAYMD, ") .append(" KK0081.SVC_ENDYMD, ") .append(" KK0081.SVC_CHRG_ENDYMD, ") .append(" (SELECT KK0751.SVC_NM ") .append(" FROM KK_M_SVC KK0751 ") .append(" WHERE KK0751.SVC_CD = KK0081.SVC_CD ") .append(" AND (KK0751.SVC_CD, KK0751.RSV_APLY_YMD || KK0751.GENE_ADD_DTM) = ") .append(" (SELECT KK0751_GENE.SVC_CD,MAX(KK0751_GENE.RSV_APLY_YMD || KK0751_GENE.GENE_ADD_DTM) AS KK0751_MAX ") .append(" FROM KK_M_SVC KK0751_GENE ") .append(" WHERE KK0751_GENE.SVC_CD = KK0751.SVC_CD ") .append(" AND KK0751_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0751_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0751_GENE.SVC_CD) ") .append(" ) AS SVC_NM, ") .append(" KK0081.SYSID, ") .append(" (SELECT KK0781.EO_BRAND_CD ") .append(" FROM KK_M_PRC_GRP KK0781 ") .append(" WHERE KK0781.PRC_GRP_CD = KK0081.PRC_GRP_CD ") .append(" AND (KK0781.PRC_GRP_CD, KK0781.RSV_APLY_YMD || KK0781.GENE_ADD_DTM) = ") .append(" (SELECT KK0781_GENE.PRC_GRP_CD, MAX(KK0781_GENE.RSV_APLY_YMD || KK0781_GENE.GENE_ADD_DTM) AS KK0781_MAX ") .append(" FROM KK_M_PRC_GRP KK0781_GENE ") .append(" WHERE KK0781_GENE.PRC_GRP_CD = KK0781.PRC_GRP_CD ") .append(" AND KK0781_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0781_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0781_GENE.PRC_GRP_CD)) EO_BRAND_CD, "); if ("2".equals( inMsg.getObject(EKK0081B014CBSMsg.FUNC_CODE).toString())) { sql_Buff .append(" CASE WHEN NVL(KK0481_01.SOKU_WARI_UM_CNT, 0) = 0 ") .append(" THEN '無' ") .append(" ELSE '有' ") .append(" END SOKU_WARI_UM_NM, "); } else { sql_Buff .append(" NULL AS SOKU_WARI_UM_NM, "); } // JKKejbEditCommon instance = new JKKejbEditCommon(); sql_Buff.append(" (SELECT ZM0171_01.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_01 ") .append(" WHERE ZM0171_01.CD_SBT_CD = 'CD01586' ") .append(" AND ZM0171_01.CD_DIV = ( ") .append(" SELECT ") .append(" CASE WHEN KK0081_02.SVC_KEI_STAT = '210' ") .append(" AND KK0081_02.PAUSE_STP_CD = '02' ") .append(" THEN '211' ") .append(" WHEN KK0081_02.SVC_KEI_STAT = '910' ") .append(" AND KK0081_02.SVC_DLRE_CD = '02' ") .append(" THEN '911' ") .append(" ELSE KK0081_02.SVC_KEI_STAT ") .append(" END DISP_SVC_KEI_STAT ") .append(" FROM KK_T_SVC_KEI KK0081_02 ") .append(" WHERE KK0081_02.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" AND KK0081_02.GENE_ADD_DTM = KK0081.GENE_ADD_DTM ") .append(" ) ") .append(" AND ZM0171_01.MK_FLG = '0') AS KEI_STAT, ") .append(" KK0081.SVC_DSL_KISAN_YMD, ") .append(" KK0081.SKEKKA_CD, ") .append(" (SELECT ZM0171_05.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_05 ") .append(" WHERE ZM0171_05.CD_DIV = KK0081.SKEKKA_CD ") .append(" AND ZM0171_05.CD_SBT_CD = 'CD00281' ") .append(" AND ZM0171_05.MK_FLG = '0') AS SKEKKA_CD_NM, ") .append(" KK0011.MSKMSHO_ARIV_YMD, ") .append(" KK0011.MSKM_YMD, ") .append(" KK0321.SEIKY_KEI_NO, ") .append(" KK0081.SVC_CD, ") //ANK-3153-00-00 ADD START .append(" KK0011.KARI_MSKM_FLG, ") .append(" KK0011.MSKM_SBT_CD ") //ANK-3153-00-00 ADD END .append(" FROM ") .append(" (SELECT KK0081_01.* ") .append(" ,MAX(KK0081_01.RSV_APLY_YMD || KK0081_01.GENE_ADD_DTM) OVER (PARTITION BY KK0081_01.SVC_KEI_NO) MAX_GENE_ADD_DTM ") .append(" FROM KK_T_SVC_KEI KK0081_01 ") .append(" WHERE KK0081_01.SVC_KEI_NO IN ( ") // KEY_サービス契約番号を設定する // .append(instance.setSqlIn(inMsg.getString(EKK0081B014CBSMsg.KEY_SVC_KEI_NO))) .append(inMsg.getString(EKK0081B014CBSMsg.KEY_SVC_KEI_NO)) .append(" ) ") .append(" AND KK0081_01.RSV_APLY_YMD <= ? ") .append(" AND KK0081_01.RSV_APLY_CD = '2' ") .append(" AND KK0081_01.MK_FLG = '0') KK0081 ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0321_TMP.SEIKY_KEI_NO, ") .append(" KK0321_TMP.SVC_KEI_NO ") .append(" FROM KK_T_KAKINS KK0321_TMP ") .append(" WHERE KK0321_TMP.KAKINS_TSTAYMD <= ? ") .append(" AND KK0321_TMP.KAKINS_TENDYMD >= ? ") .append(" AND KK0321_TMP.MK_FLG = '0' ") .append(" ) KK0321 ") .append(" ON KK0081.SVC_KEI_NO = KK0321.SVC_KEI_NO ") .append(" LEFT OUTER JOIN ") .append(" (SELECT CH0231_TMP.TOKUSOKU_STAT, ") .append(" CH0231_TMP.SEIKY_KEI_NO ") .append(" FROM CH_T_TOKUSOKU CH0231_TMP ") .append(" WHERE CH0231_TMP.TOKUSOKU_STAYMD <= ? ") .append(" AND CH0231_TMP.TOKUSOKU_ENDYMD >= ? ") .append(" AND CH0231_TMP.MK_FLG = '0' ") .append(" ) CH0231 ") .append(" ON KK0321.SEIKY_KEI_NO = CH0231.SEIKY_KEI_NO ") .append(" LEFT OUTER JOIN ") .append(" ( ") .append(" SELECT ") .append(" KK0021_TMP.MSKM_DTL_NO ") .append(" ,KK0021_TMP.MSKM_NO ") .append(" ,KK0021_TMP.MSKM_FORM_CD ") .append(" ,KK0021_TMP.MSKM_YMD ") .append(" ,KK0021_TMP.MSKMSHO_NO ") .append(" ,KK0081_SUB.SVC_KEI_NO ") .append(" FROM ") .append(" KK_T_MSKM_DTL KK0021_TMP ") .append(" INNER JOIN KK_T_SVC_KEI KK0081_SUB ") .append(" ON (KK0081_SUB.MSKM_DTL_NO = KK0021_TMP.MSKM_DTL_NO) ") .append(" WHERE ") .append(" (KK0021_TMP.MSKM_DTL_NO, KK0021_TMP.GENE_ADD_DTM) = (SELECT KK0021_TMP_GENE.MSKM_DTL_NO, ") .append(" MAX(KK0021_TMP_GENE.GENE_ADD_DTM) AS KK0021_TMP_MAX ") .append(" FROM KK_T_MSKM_DTL KK0021_TMP_GENE ") .append(" WHERE KK0021_TMP_GENE.MSKM_DTL_NO = KK0021_TMP.MSKM_DTL_NO ") .append(" AND KK0021_TMP_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0021_TMP_GENE.MSKM_DTL_NO) ") .append(" AND KK0081_SUB.GENE_ADD_DTM = (SELECT MIN(KK0081_SUB_GENE.GENE_ADD_DTM) AS KK0081_SUB_MIN ") .append(" FROM KK_T_SVC_KEI KK0081_SUB_GENE ") .append(" WHERE KK0081_SUB_GENE.SVC_KEI_NO = KK0081_SUB.SVC_KEI_NO ") .append(" AND KK0081_SUB_GENE.MK_FLG = '0') ") .append(" ) KK0021 ") .append(" ON KK0081.SVC_KEI_NO = KK0021.SVC_KEI_NO ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0011_TMP.* ") .append(" ,MAX(KK0011_TMP.GENE_ADD_DTM) OVER (PARTITION BY KK0011_TMP.MSKM_NO) MAX_GENE_ADD_DTM ") .append(" FROM KK_T_MSKM KK0011_TMP ") .append(" WHERE KK0011_TMP.MK_FLG = '0' ") .append(" ) KK0011 ") .append(" ON KK0021.MSKM_NO = KK0011.MSKM_NO ") .append(" AND KK0011.GENE_ADD_DTM = KK0011.MAX_GENE_ADD_DTM ") .append(" LEFT OUTER JOIN ") .append(" ( ") .append(" SELECT ") .append(" KK0071_SUB.MSKM_NO, ") .append(" KK0071_SUB.AGNT_CD, ") .append(" (SELECT KK0061.AGNT_NM ") .append(" FROM KK_M_AGNT KK0061 ") .append(" WHERE KK0061.AGNT_CD = KK0071_SUB.AGNT_CD ") .append(" AND (KK0061.AGNT_CD, KK0061.RSV_APLY_YMD || KK0061.GENE_ADD_DTM) = ") .append(" (SELECT KK0061_GENE.AGNT_CD, MAX(KK0061_GENE.RSV_APLY_YMD || KK0061_GENE.GENE_ADD_DTM) AS KK0061_MAX ") .append(" FROM KK_M_AGNT KK0061_GENE ") .append(" WHERE KK0061_GENE.AGNT_CD = KK0061.AGNT_CD ") .append(" AND KK0061_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0061_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0061_GENE.AGNT_CD )) AS AGNT_CD_NM ") .append(" FROM KK_T_MSKM_AGNT KK0071_SUB ") .append(" WHERE ") .append(" KK0071_SUB.MK_FLG = '0' ") .append(" AND KK0071_SUB.MSKM_NO IN( ") .append(" SELECT DISTINCT ") .append(" KK0011_01.MSKM_NO ") .append(" FROM KK_T_SVC_KEI KK0081_01 ") .append(" INNER JOIN KK_T_MSKM_DTL KK0021_01 ") .append(" ON KK0021_01.MSKM_DTL_NO = KK0081_01.MSKM_DTL_NO ") .append(" INNER JOIN KK_T_MSKM KK0011_01 ") .append(" ON KK0011_01.MSKM_NO = KK0021_01.MSKM_NO ") .append(" WHERE ") .append(" KK0081_01.SVC_KEI_NO IN ( ") // KEY_サービス契約番号を設定する .append(inMsg.getString(EKK0081B014CBSMsg.KEY_SVC_KEI_NO)) .append(" ) ") .append(" AND KK0011_01.MSKM_SBT_CD IN ('00001','00002') ") .append(" AND KK0081_01.MK_FLG = '0' ") .append(" AND KK0021_01.MK_FLG = '0' ") .append(" AND KK0011_01.MK_FLG = '0' ") .append(" UNION ") .append(" SELECT DISTINCT ") .append(" KK0011_02.MSKM_NO ") .append(" FROM KK_T_SVC_KEI KK0081_02 ") .append(" INNER JOIN KK_T_MSKM_DTL KK0021_02 ") .append(" ON KK0021_02.MSKM_DTL_NO = KK0081_02.MSKM_DTL_NO ") .append(" INNER JOIN KK_T_MSKM KK0011_02 ") .append(" ON KK0011_02.MSKM_NO = KK0021_02.MSKM_NO ") .append(" WHERE ") .append(" KK0081_02.SVC_KEI_NO IN ( ") // KEY_サービス契約番号を設定する .append(inMsg.getString(EKK0081B014CBSMsg.KEY_SVC_KEI_NO)) .append(" ) ") .append(" AND KK0011_02.MSKM_SBT_CD = '00017' ") .append(" AND KK0081_02.MK_FLG = '0' ") .append(" AND KK0021_02.MK_FLG = '0' ") .append(" AND KK0011_02.MK_FLG = '0' ") .append(" AND NOT EXISTS ( ") .append(" SELECT 1 ") .append(" FROM KK_T_SVC_KEI KK0081_03 ") .append(" INNER JOIN KK_T_MSKM_DTL KK0021_03 ") .append(" ON KK0021_03.MSKM_DTL_NO = KK0081_03.MSKM_DTL_NO ") .append(" INNER JOIN KK_T_MSKM KK0011_03 ") .append(" ON KK0011_03.MSKM_NO = KK0021_03.MSKM_NO ") .append(" WHERE ") .append(" KK0081_03.SVC_KEI_NO = KK0081_02.SVC_KEI_NO ") .append(" AND KK0011_03.MSKM_SBT_CD = '00001' ") .append(" AND KK0081_03.MK_FLG = '0' ") .append(" AND KK0021_03.MK_FLG = '0' ") .append(" AND KK0011_03.MK_FLG = '0' ") .append(" ) ") .append(" ) ") .append(" ) KK0071 ") .append(" ON KK0011.MSKM_NO = KK0071.MSKM_NO ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0241_TMP.SVC_KEI_NO, ") .append(" KK0241_TMP.SVC_KEI_KAISEN_UCWK_NO ") .append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_TMP ") .append(" WHERE KK0241_TMP.KAISEN_UCWK_USE_STAYMD <= ? ") .append(" AND (KK0241_TMP.SVC_KEI_NO, KK0241_TMP.KAISEN_UCWK_USE_ENDYMD) = ") .append(" (SELECT KK0241_01.SVC_KEI_NO, MAX(KK0241_01.KAISEN_UCWK_USE_ENDYMD) ") .append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_01 ") .append(" WHERE KK0241_01.SVC_KEI_NO = KK0241_TMP.SVC_KEI_NO ") .append(" AND KK0241_01.KAISEN_UCWK_USE_STAYMD <= ? ") .append(" AND KK0241_01.MK_FLG = '0' ") .append(" GROUP BY KK0241_01.SVC_KEI_NO) ") .append(" AND KK0241_TMP.MK_FLG = '0' ") .append(" ) KK0241 ") .append(" ON KK0081.SVC_KEI_NO = KK0241.SVC_KEI_NO ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0251_TMP.* ") .append(" ,MAX(KK0251_TMP.GENE_ADD_DTM) OVER (PARTITION BY KK0251_TMP.SVC_KEI_KAISEN_UCWK_NO) MAX_GENE_ADD_DTM ") .append(" FROM KK_T_SVKEI_KAISEN_UW KK0251_TMP ") .append(" WHERE KK0251_TMP.MK_FLG = '0' ") .append(" ) KK0251 ") .append(" ON KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO ") .append(" AND KK0251.GENE_ADD_DTM = KK0251.MAX_GENE_ADD_DTM ") .append(" AND KK0251.USE_STAYMD <= ? ") .append(" AND KK0251.USE_ENDYMD >= ? "); // ▽▽▽ ANK-3387-00-00 ADD START sql_Buff.append(" LEFT OUTER JOIN KK_T_TK_HOSHIKI_KEI KK0891 ") .append(" ON KK0891.TK_HOSHIKI_KEI_NO = KK0081.TK_HOSHIKI_KEI_NO ") .append(" AND KK0891.MK_FLG = '0' "); // ANK-4244-00-00 ADD START sql_Buff.append(" LEFT OUTER JOIN KK_T_KAISEN_USE_KEI KK0881 ") .append(" ON KK0881.KAISEN_USE_KEI_NO = KK0891.KAISEN_USE_KEI_NO AND KK0881.MK_FLG = '0' "); // ANK-4244-00-00 ADD END // ▲▲▲ ANK-3387-00-00 ADD END if ("2".equals( inMsg.getObject(EKK0081B014CBSMsg.FUNC_CODE).toString())) { sql_Buff .append(" LEFT OUTER JOIN ") .append(" ( ") .append(" SELECT ") .append(" KK0481.SVC_KEI_NO ") .append(" ,KK0451.WRIB_SVC_CD ") .append(" ,COUNT(*) AS SOKU_WARI_UM_CNT ") .append(" FROM ") .append(" KK_T_WRISVC_TG_KEI KK0481 ") .append(" INNER JOIN KK_T_WRIB_SVC_KEI KK0451 ") .append(" ON KK0451.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0481.WRISVC_TG_KEI_TSTAYMD <= ? ") .append(" AND KK0481.WRISVC_TG_KEI_TENDYMD >= ? ") .append(" AND KK0451.WRIB_SVC_KEI_STAT IN ('010', '100') ") .append(" AND (KK0481.WRIB_SVC_KEI_NO, KK0481.WRIB_SVC_TRGT_KEI_NO, KK0481.GENE_ADD_DTM) = ") .append(" (SELECT KK0481_GENE.WRIB_SVC_KEI_NO, KK0481_GENE.WRIB_SVC_TRGT_KEI_NO, MAX(KK0481_GENE.GENE_ADD_DTM) AS KK0481_MAX ") .append(" FROM KK_T_WRISVC_TG_KEI KK0481_GENE ") .append(" WHERE KK0481_GENE.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO ") .append(" AND KK0481_GENE.WRIB_SVC_TRGT_KEI_NO = KK0481.WRIB_SVC_TRGT_KEI_NO ") .append(" AND KK0481_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0481_GENE.WRIB_SVC_KEI_NO, KK0481_GENE.WRIB_SVC_TRGT_KEI_NO ) ") .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(" GROUP BY KK0481.SVC_KEI_NO, KK0451.WRIB_SVC_CD ") .append(" ) KK0481_01 ") .append(" ON KK0481_01.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" AND KK0481_01.WRIB_SVC_CD = 'W00000021' "); } // SQL文_個別部1 if ("1".equals( inMsg.getObject(EKK0081B014CBSMsg.FUNC_CODE).toString()) || "2".equals( inMsg.getObject(EKK0081B014CBSMsg.FUNC_CODE).toString())) { sql_Buff.append(" WHERE KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = KK0081.MAX_GENE_ADD_DTM ") .append(" AND KK0081.PRC_GRP_CD <> '99' ") .append(" ORDER BY ") .append(" KK0251.KAISEN_PLACE_PCD ASC, ") .append(" KK0251.KAISEN_PLACE_STATE_NM ASC, ") .append(" KK0251.KAISEN_PLACE_CITY_NM ASC, ") .append(" KK0251.KAISEN_PLACE_OAZTSU_NM ASC, ") .append(" KK0251.KAISEN_PLACE_AZCHO_NM ASC, ") .append(" KK0251.KAISEN_PLACE_BNCHIGO ASC, ") .append(" KK0251.KAISEN_PLACE_ADRTTM ASC, ") .append(" KK0251.KAISEN_PLACE_ADRRM ASC, ") .append(" KK0081.SVC_KEI_NO ASC "); } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // 運用日付 String sOpeDate = JKKModelCommon.getOpeDate(inMsg); // SELECT句、FROM句に運用日付を設定 // ArrayList alBind = instance.setInBind(inMsg.getString(EKK0081B014CBSMsg.KEY_SVC_KEI_NO)); int iBindCounter = 1; int iDateCnt = 12; if ("2".equals( inMsg.getObject(EKK0081B014CBSMsg.FUNC_CODE).toString())) { iDateCnt = iDateCnt + 3; } for (int i = 0; i < iDateCnt; i++) { // if (iBindCounter == 3) // { // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // for(int j = 0; j < alBind.size(); j++) // { // // SELECT句に設定するバインド変数値 // CAANJDBCUtil.setParam(pstmt, iBindCounter++, alBind.get(j)); // } // } // 運用日付を設定する CAANJDBCUtil.setParam(pstmt, iBindCounter++, sOpeDate); } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EKK0081B014CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EKK0081B014CBSMsg1List.SVC_KEI_NO , EKK0081B014CBSMsg1List.PPLAN_CD , EKK0081B014CBSMsg1List.PPLAN_CD_NM , EKK0081B014CBSMsg1List.SVC_KEI_STAT , EKK0081B014CBSMsg1List.SVC_KEI_STAT_NM , EKK0081B014CBSMsg1List.IDO_NG_STAT_CD , EKK0081B014CBSMsg1List.IDO_NG_STAT_CD_NM , EKK0081B014CBSMsg1List.TOKUSOKU_STAT , EKK0081B014CBSMsg1List.TOKUSOKU_STAT_NM , EKK0081B014CBSMsg1List.MSKM_FORM_CD , EKK0081B014CBSMsg1List.MSKM_FORM_CD_NM , EKK0081B014CBSMsg1List.ADD_DTM , EKK0081B014CBSMsg1List.MSKM_UK_DTM , EKK0081B014CBSMsg1List.MSKM_YMD , EKK0081B014CBSMsg1List.AGNT_CD , EKK0081B014CBSMsg1List.AGNT_NM , EKK0081B014CBSMsg1List.KAISEN_PLACE_PCD , EKK0081B014CBSMsg1List.KAISEN_PLACE_STATE_NM , EKK0081B014CBSMsg1List.KAISEN_PLACE_CITY_NM , EKK0081B014CBSMsg1List.KAISEN_PLACE_OAZTSU_NM , EKK0081B014CBSMsg1List.KAISEN_PLACE_AZCHO_NM , EKK0081B014CBSMsg1List.KAISEN_PLACE_BNCHIGO , EKK0081B014CBSMsg1List.KAISEN_PLACE_ADRTTM , EKK0081B014CBSMsg1List.KAISEN_PLACE_ADRRM , EKK0081B014CBSMsg1List.MSKMSHO_NO , EKK0081B014CBSMsg1List.SVC_STA_YMD , EKK0081B014CBSMsg1List.SVC_CHRG_STAYMD , EKK0081B014CBSMsg1List.SVC_ENDYMD , EKK0081B014CBSMsg1List.SVC_CHRG_ENDYMD , EKK0081B014CBSMsg1List.SVC_NM , EKK0081B014CBSMsg1List.SYSID , EKK0081B014CBSMsg1List.EO_BRAND_CD , EKK0081B014CBSMsg1List.SOKU_WARI_UM_NM , EKK0081B014CBSMsg1List.KEI_STAT , EKK0081B014CBSMsg1List.SVC_DSL_KISAN_YMD , EKK0081B014CBSMsg1List.SKEKKA_CD , EKK0081B014CBSMsg1List.SKEKKA_CD_NM , EKK0081B014CBSMsg1List.MSKMSHO_ARIV_YMD , EKK0081B014CBSMsg1List.MSKM_YMD_MSKM , EKK0081B014CBSMsg1List.SEIKY_KEI_NO , EKK0081B014CBSMsg1List.SVC_CD //ANK-3153-00-00 ADD START , EKK0081B014CBSMsg1List.KARI_MSKM_FLG , EKK0081B014CBSMsg1List.MSKM_SBT_CD //ANK-3153-00-00 ADD END }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0081B014CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("EKK0081B014CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(EKK0081B014CBSMsg.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(EKK0081B014CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }