/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名 ：eo顧客基幹システム * モジュール名 ：JSYejbEDK0291B010TPDA * ソースファイル名：JSYejbEDK0291B010TPDA.java * 作成者 ：EK909582 * 日付 ：2014年09月17日 *＜機能概要＞ * 型確定条件（型確定パターン）一覧照会テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン 修正日 修正者 修正内容 * ｖ10.00.00 2014/09/17 FJ)伊藤 新規作成 * ｖ39.00.01 2018/11/16 FJ)黒田 【IT1-2018-0000201】障害対応 * ********************************************************************************/ 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 java.util.List; 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.common.constant.JDKStrConst; import eo.common.constant.JPCModelConstant; import eo.ejb.cbm.entity.DK0291ETMsg; import eo.ejb.cbs.cbsmsg.EDK0291B010CBSMsg; import eo.ejb.cbs.cbsmsg.EDK0291B010CBSMsg1List; import eo.ejb.common.JPCModelCommon; /** * * テンプレートDBアクセス部品　EDK0291B010_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEDK0291B010TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbEDK0291B010TPDA() { } /** * EDK0291B010_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(DK0291ETMsg.getTableName()); //*************************** 自動生成ソース修正 START ***************************// // ＫＥＹ＿型確定パターン取得 String keyMdlFixPtn = inMsg.getObject(EDK0291B010CBSMsg.KEY_MDL_FIX_PTN).toString(); // 運用日取得 String opeDate = (String)JPCModelCommon.getOpeDate(inMsg, null); List replaceList = new ArrayList(); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT ") .append(" DK0281.YMD, ") .append(" DK0281.DOW_CD, ") .append(" ( ") .append(" SELECT ") .append(" ZM0171.CD_DIV_ALI ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00023' ") .append(" AND ZM0171.CD_DIV = DK0281.DOW_CD ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) DOW, "); replaceList.add(opeDate); replaceList.add(opeDate); // ＫＥＹ＿型確定パターンがONUメーカー指定の場合 if (JDKStrConst.MDL_FIX_PTN_ONU_MAKER_SHITEI.equals(keyMdlFixPtn)) { // IT1-2018-0000201 MOD START // for (int i = 0; i < 10; i++) for (int i = 0; i < 20; i++) // IT1-2018-0000201 MOD END { sql_Buff.append(" ( ") .append(" SELECT ") .append(" DK0291.TAKNKIKI_MODEL_CD ") .append(" FROM DK_M_TKKIKI_MDL_FIX DK0291 ") .append(" WHERE TO_CHAR(TO_DATE(DK0281.YMD) - 1, 'yyyymmdd') BETWEEN DK0291.TSTAYMD AND DK0291.TENDYMD ") .append(" AND DK0291.TAKNKIKI_MDL_CD_FIX_GRP_CD = 'ONU1' ") .append(" AND DK0291.MAKER_CD = ( ") .append(" SELECT ") .append(" ZM0171.CD_DIV ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD01661' ") .append(" AND ZM0171.DSP_JUN = '").append(i + 1).append("' ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) ") .append(" AND DK0291.MK_FLG = '0' ") .append(" ) TAKNKIKI_MODEL_").append(String.format("%1$02d", i + 1)).append(", ") .append(" 0 ZAIKO_RT_").append(String.format("%1$02d", i + 1)); // IT1-2018-0000201 MOD START // if (i != 9) if (i != 19) // IT1-2018-0000201 MOD END { sql_Buff.append(", "); } replaceList.add(opeDate); replaceList.add(opeDate); } } // ＫＥＹ＿型確定パターンが770Mhz V-ONUメーカー指定の場合 else if (JDKStrConst.MDL_FIX_PTN_770M_VONU_MAKER_SHITEI.equals(keyMdlFixPtn)) { // IT1-2018-0000201 MOD START // for (int i = 0; i < 10; i++) for (int i = 0; i < 20; i++) // IT1-2018-0000201 MOD END { sql_Buff.append(" ( ") .append(" SELECT ") .append(" DK0291.TAKNKIKI_MODEL_CD ") .append(" FROM DK_M_TKKIKI_MDL_FIX DK0291 ") .append(" WHERE TO_CHAR(TO_DATE(DK0281.YMD) - 1, 'yyyymmdd') BETWEEN DK0291.TSTAYMD AND DK0291.TENDYMD ") .append(" AND DK0291.TAKNKIKI_MDL_CD_FIX_GRP_CD = 'VON1' ") .append(" AND DK0291.MAKER_CD = ( ") .append(" SELECT ") .append(" ZM0171.CD_DIV ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD01662' ") .append(" AND ZM0171.DSP_JUN = '").append(i + 1).append("' ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) ") .append(" AND DK0291.TV_COURSE_GRP_CD = '0' ") .append(" AND DK0291.MK_FLG = '0' ") .append(" ) TAKNKIKI_MODEL_").append(String.format("%1$02d", i + 1)).append(", ") .append(" 0 ZAIKO_RT_").append(String.format("%1$02d", i + 1)); // IT1-2018-0000201 MOD START // if (i != 9) if (i != 19) // IT1-2018-0000201 MOD END { sql_Buff.append(", "); } replaceList.add(opeDate); replaceList.add(opeDate); } } // ＫＥＹ＿型確定パターンが2.6Ghz V-ONUメーカー指定の場合 else if (JDKStrConst.MDL_FIX_PTN_2_6G_VONU_MAKER_SHITEI.equals(keyMdlFixPtn)) { // IT1-2018-0000201 MOD START // for (int i = 0; i < 10; i++) for (int i = 0; i < 20; i++) // IT1-2018-0000201 MOD END { sql_Buff.append(" ( ") .append(" SELECT ") .append(" DK0291.TAKNKIKI_MODEL_CD ") .append(" FROM DK_M_TKKIKI_MDL_FIX DK0291 ") .append(" WHERE TO_CHAR(TO_DATE(DK0281.YMD) - 1, 'yyyymmdd') BETWEEN DK0291.TSTAYMD AND DK0291.TENDYMD ") .append(" AND DK0291.TAKNKIKI_MDL_CD_FIX_GRP_CD = 'VON1' ") .append(" AND DK0291.MAKER_CD = ( ") .append(" SELECT ") .append(" ZM0171.CD_DIV ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD01662' ") .append(" AND ZM0171.DSP_JUN = '").append(i + 1).append("' ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) ") .append(" AND DK0291.TV_COURSE_GRP_CD = '1' ") .append(" AND DK0291.MK_FLG = '0' ") .append(" ) TAKNKIKI_MODEL_").append(String.format("%1$02d", i + 1)).append(", ") .append(" 0 ZAIKO_RT_").append(String.format("%1$02d", i + 1)); // IT1-2018-0000201 MOD START // if (i != 9) if (i != 19) // IT1-2018-0000201 MOD END { sql_Buff.append(", "); } replaceList.add(opeDate); replaceList.add(opeDate); } } // ＫＥＹ＿型確定パターンが770Mhz V-ONU在庫比率指定の場合 else if (JDKStrConst.MDL_FIX_PTN_770M_VONU_ZIK_HRT_SHITEI.equals(keyMdlFixPtn)) { // IT1-2018-0000201 MOD START // for (int i = 0; i < 10; i++) for (int i = 0; i < 20; i++) // IT1-2018-0000201 MOD END { sql_Buff.append(" ( ") .append(" SELECT ") .append(" DK0291.TAKNKIKI_MODEL_CD ") .append(" FROM DK_M_TKKIKI_MDL_FIX DK0291 ") .append(" INNER JOIN ZM_M_TAKNKIKI_MODEL ZM0411 ") .append(" ON DK0291.TAKNKIKI_MODEL_CD = ZM0411.TAKNKIKI_MODEL_CD ") .append(" AND ZM0411.MAKER_CD = ( ") .append(" SELECT ") .append(" ZM0171.CD_DIV ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD01663' ") .append(" AND ZM0171.DSP_JUN = '").append(i + 1).append("' ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) ") .append(" AND ZM0411.MK_FLG = '0' ") .append(" WHERE TO_CHAR(TO_DATE(DK0281.YMD) - 1, 'yyyymmdd') BETWEEN DK0291.TSTAYMD AND DK0291.TENDYMD ") .append(" AND DK0291.TAKNKIKI_MDL_CD_FIX_GRP_CD = 'VON1' ") .append(" AND DK0291.MAKER_CD IS NULL ") .append(" AND DK0291.ZAIKO_RT > 0 ") .append(" AND DK0291.TV_COURSE_GRP_CD = '0' ") .append(" AND DK0291.MK_FLG = '0' ") .append(" ) TAKNKIKI_MODEL_").append(String.format("%1$02d", i + 1)).append(", ") .append(" ( ") .append(" SELECT ") .append(" DK0291.ZAIKO_RT ") .append(" FROM DK_M_TKKIKI_MDL_FIX DK0291 ") .append(" INNER JOIN ZM_M_TAKNKIKI_MODEL ZM0411 ") .append(" ON DK0291.TAKNKIKI_MODEL_CD = ZM0411.TAKNKIKI_MODEL_CD ") .append(" AND ZM0411.MAKER_CD = ( ") .append(" SELECT ") .append(" ZM0171.CD_DIV ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD01663' ") .append(" AND ZM0171.DSP_JUN = '").append(i + 1).append("' ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) ") .append(" AND ZM0411.MK_FLG = '0' ") .append(" WHERE TO_CHAR(TO_DATE(DK0281.YMD) - 1, 'yyyymmdd') BETWEEN DK0291.TSTAYMD AND DK0291.TENDYMD ") .append(" AND DK0291.TAKNKIKI_MDL_CD_FIX_GRP_CD = 'VON1' ") .append(" AND DK0291.MAKER_CD IS NULL ") .append(" AND DK0291.ZAIKO_RT > 0 ") .append(" AND DK0291.TV_COURSE_GRP_CD = '0' ") .append(" AND DK0291.MK_FLG = '0' ") .append(" ) ZAIKO_RT_").append(String.format("%1$02d", i + 1)); // IT1-2018-0000201 MOD START // if (i != 9) if (i != 19) // IT1-2018-0000201 MOD END { sql_Buff.append(", "); } replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(opeDate); } } // ＫＥＹ＿型確定パターンが2.6Ghz V-ONU在庫比率指定の場合 else if (JDKStrConst.MDL_FIX_PTN_2_6G_VONU_ZIK_HRT_SHITEII.equals(keyMdlFixPtn)) { // IT1-2018-0000201 MOD START // for (int i = 0; i < 10; i++) for (int i = 0; i < 20; i++) // IT1-2018-0000201 MOD END { sql_Buff.append(" ( ") .append(" SELECT ") .append(" DK0291.TAKNKIKI_MODEL_CD ") .append(" FROM DK_M_TKKIKI_MDL_FIX DK0291 ") .append(" INNER JOIN ZM_M_TAKNKIKI_MODEL ZM0411 ") .append(" ON DK0291.TAKNKIKI_MODEL_CD = ZM0411.TAKNKIKI_MODEL_CD ") .append(" AND ZM0411.MAKER_CD = ( ") .append(" SELECT ") .append(" ZM0171.CD_DIV ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD01663' ") .append(" AND ZM0171.DSP_JUN = '").append(i + 1).append("' ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) ") .append(" AND ZM0411.MK_FLG = '0' ") .append(" WHERE TO_CHAR(TO_DATE(DK0281.YMD) - 1, 'yyyymmdd') BETWEEN DK0291.TSTAYMD AND DK0291.TENDYMD ") .append(" AND DK0291.TAKNKIKI_MDL_CD_FIX_GRP_CD = 'VON1' ") .append(" AND DK0291.MAKER_CD IS NULL ") .append(" AND DK0291.ZAIKO_RT > 0 ") .append(" AND DK0291.TV_COURSE_GRP_CD = '1' ") .append(" AND DK0291.MK_FLG = '0' ") .append(" ) TAKNKIKI_MODEL_").append(String.format("%1$02d", i + 1)).append(", ") .append(" ( ") .append(" SELECT ") .append(" DK0291.ZAIKO_RT ") .append(" FROM DK_M_TKKIKI_MDL_FIX DK0291 ") .append(" INNER JOIN ZM_M_TAKNKIKI_MODEL ZM0411 ") .append(" ON DK0291.TAKNKIKI_MODEL_CD = ZM0411.TAKNKIKI_MODEL_CD ") .append(" AND ZM0411.MAKER_CD = ( ") .append(" SELECT ") .append(" ZM0171.CD_DIV ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD01663' ") .append(" AND ZM0171.DSP_JUN = '").append(i + 1).append("' ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) ") .append(" AND ZM0411.MK_FLG = '0' ") .append(" WHERE TO_CHAR(TO_DATE(DK0281.YMD) - 1, 'yyyymmdd') BETWEEN DK0291.TSTAYMD AND DK0291.TENDYMD ") .append(" AND DK0291.TAKNKIKI_MDL_CD_FIX_GRP_CD = 'VON1' ") .append(" AND DK0291.MAKER_CD IS NULL ") .append(" AND DK0291.ZAIKO_RT > 0 ") .append(" AND DK0291.TV_COURSE_GRP_CD = '1' ") .append(" AND DK0291.MK_FLG = '0' ") .append(" ) ZAIKO_RT_").append(String.format("%1$02d", i + 1)); // IT1-2018-0000201 MOD START // if (i != 9) if (i != 19) // IT1-2018-0000201 MOD END { sql_Buff.append(", "); } replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(opeDate); } } // ＫＥＹ＿型確定パターンが多機能ルータ在庫比率指定の場合 else if (JDKStrConst.MDL_FIX_PTN_TKN_RT_ZIK_HRT_SHITEII.equals(keyMdlFixPtn)) { // IT1-2018-0000201 MOD START // for (int i = 0; i < 10; i++) for (int i = 0; i < 20; i++) // IT1-2018-0000201 MOD END { sql_Buff.append(" ( ") .append(" SELECT ") .append(" DK0291.TAKNKIKI_MODEL_CD ") .append(" FROM DK_M_TKKIKI_MDL_FIX DK0291 ") .append(" INNER JOIN ZM_M_TAKNKIKI_MODEL ZM0411 ") .append(" ON DK0291.TAKNKIKI_MODEL_CD = ZM0411.TAKNKIKI_MODEL_CD ") .append(" AND ZM0411.MAKER_CD = ( ") .append(" SELECT ") .append(" ZM0171.CD_DIV ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD01664' ") .append(" AND ZM0171.DSP_JUN = '").append(i + 1).append("' ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) ") .append(" AND ZM0411.MK_FLG = '0' ") .append(" WHERE TO_CHAR(TO_DATE(DK0281.YMD) - 1, 'yyyymmdd') BETWEEN DK0291.TSTAYMD AND DK0291.TENDYMD ") .append(" AND DK0291.TAKNKIKI_MDL_CD_FIX_GRP_CD = 'MTR1' ") .append(" AND DK0291.MAKER_CD IS NULL ") .append(" AND DK0291.ZAIKO_RT > 0 ") .append(" AND DK0291.MK_FLG = '0' ") .append(" ) TAKNKIKI_MODEL_").append(String.format("%1$02d", i + 1)).append(", ") .append(" ( ") .append(" SELECT ") .append(" DK0291.ZAIKO_RT ") .append(" FROM DK_M_TKKIKI_MDL_FIX DK0291 ") .append(" INNER JOIN ZM_M_TAKNKIKI_MODEL ZM0411 ") .append(" ON DK0291.TAKNKIKI_MODEL_CD = ZM0411.TAKNKIKI_MODEL_CD ") .append(" AND ZM0411.MAKER_CD = ( ") .append(" SELECT ") .append(" ZM0171.CD_DIV ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD01664' ") .append(" AND ZM0171.DSP_JUN = '").append(i + 1).append("' ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) ") .append(" AND ZM0411.MK_FLG = '0' ") .append(" WHERE TO_CHAR(TO_DATE(DK0281.YMD) - 1, 'yyyymmdd') BETWEEN DK0291.TSTAYMD AND DK0291.TENDYMD ") .append(" AND DK0291.TAKNKIKI_MDL_CD_FIX_GRP_CD = 'MTR1' ") .append(" AND DK0291.MAKER_CD IS NULL ") .append(" AND DK0291.ZAIKO_RT > 0 ") .append(" AND DK0291.MK_FLG = '0' ") .append(" ) ZAIKO_RT_").append(String.format("%1$02d", i + 1)); // IT1-2018-0000201 MOD START // if (i != 9) if (i != 19) // IT1-2018-0000201 MOD END { sql_Buff.append(", "); } replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(opeDate); replaceList.add(opeDate); } } // ＫＥＹ＿型確定パターンがSTB HDD容量の場合 else if (JDKStrConst.MDL_FIX_PTN_STB_HDD_YORYO.equals(keyMdlFixPtn)) { // IT1-2018-0000201 MOD START // for (int i = 0; i < 10; i++) for (int i = 0; i < 20; i++) // IT1-2018-0000201 MOD END { sql_Buff.append(" ( ") .append(" SELECT ") .append(" DK0291.TAKNKIKI_MODEL_CD ") .append(" FROM DK_M_TKKIKI_MDL_FIX DK0291 ") .append(" WHERE TO_CHAR(TO_DATE(DK0281.YMD) - 1, 'yyyymmdd') BETWEEN DK0291.TSTAYMD AND DK0291.TENDYMD ") .append(" AND DK0291.TAKNKIKI_MDL_CD_FIX_GRP_CD = 'STB3' ") .append(" AND DK0291.HDD_CAPA_CD = ( ") .append(" SELECT ") .append(" ZM0171.CD_DIV ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00557' ") .append(" AND ZM0171.DSP_JUN = '").append(i + 1).append("' ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) ") .append(" AND DK0291.MK_FLG = '0' ") .append(" ) TAKNKIKI_MODEL_").append(String.format("%1$02d", i + 1)).append(", ") .append(" 0 ZAIKO_RT_").append(String.format("%1$02d", i + 1)); // IT1-2018-0000201 MOD START // if (i != 9) if (i != 19) // IT1-2018-0000201 MOD END { sql_Buff.append(", "); } replaceList.add(opeDate); replaceList.add(opeDate); } } // ＫＥＹ＿型確定パターンがSTB HDD容量＋販売種別指定の場合 else if (JDKStrConst.MDL_FIX_PTN_STB_HDD_YORYO_HNBISBT_SHITEI.equals(keyMdlFixPtn)) { // IT1-2018-0000201 MOD START // for (int i = 0; i < 10; i++) for (int i = 0; i < 20; i++) // IT1-2018-0000201 MOD END { sql_Buff.append(" ( ") .append(" SELECT ") .append(" DK0291.TAKNKIKI_MODEL_CD ") .append(" FROM DK_M_TKKIKI_MDL_FIX DK0291 ") .append(" WHERE TO_CHAR(TO_DATE(DK0281.YMD) - 1, 'yyyymmdd') BETWEEN DK0291.TSTAYMD AND DK0291.TENDYMD ") .append(" AND DK0291.TAKNKIKI_MDL_CD_FIX_GRP_CD = 'STB2' ") .append(" AND DK0291.HAMBAI_SBT_CD = ? ") .append(" AND DK0291.HDD_CAPA_CD = ( ") .append(" SELECT ") .append(" ZM0171.CD_DIV ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00557' ") .append(" AND ZM0171.DSP_JUN = '").append(i + 1).append("' ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) ") .append(" AND DK0291.MK_FLG = '0' ") .append(" ) TAKNKIKI_MODEL_").append(String.format("%1$02d", i + 1)).append(", ") .append(" 0 ZAIKO_RT_").append(String.format("%1$02d", i + 1)); // IT1-2018-0000201 MOD START // if (i != 9) if (i != 19) // IT1-2018-0000201 MOD END { sql_Buff.append(", "); } replaceList.add(inMsg.getObject(EDK0291B010CBSMsg.KEY_HAMBAI_SBT_CD).toString()); replaceList.add(opeDate); replaceList.add(opeDate); } } // ＫＥＹ＿型確定パターンがSTB メーカー指定の場合 else if (JDKStrConst.MDL_FIX_PTN_STB_MAKER_SHITEI.equals(keyMdlFixPtn)) { // IT1-2018-0000201 MOD START // for (int i = 0; i < 10; i++) for (int i = 0; i < 20; i++) // IT1-2018-0000201 MOD END { sql_Buff.append(" ( ") .append(" SELECT ") .append(" DK0291.TAKNKIKI_MODEL_CD ") .append(" FROM DK_M_TKKIKI_MDL_FIX DK0291 ") .append(" WHERE TO_CHAR(TO_DATE(DK0281.YMD) - 1, 'yyyymmdd') BETWEEN DK0291.TSTAYMD AND DK0291.TENDYMD ") .append(" AND DK0291.TAKNKIKI_MDL_CD_FIX_GRP_CD = 'STB4' ") .append(" AND DK0291.MAKER_CD = ( ") .append(" SELECT ") .append(" ZM0171.CD_DIV ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD01665' ") .append(" AND ZM0171.DSP_JUN = '").append(i + 1).append("' ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) ") .append(" AND DK0291.MK_FLG = '0' ") .append(" ) TAKNKIKI_MODEL_").append(String.format("%1$02d", i + 1)).append(", ") .append(" 0 ZAIKO_RT_").append(String.format("%1$02d", i + 1)); // IT1-2018-0000201 MOD START // if (i != 9) if (i != 19) // IT1-2018-0000201 MOD END { sql_Buff.append(", "); } replaceList.add(opeDate); replaceList.add(opeDate); } } sql_Buff.append(" FROM DK_M_BTRY_SOKO_CAL DK0281 "); // SQL文_個別部1 if ( JPCModelConstant.FUNC_CD_1.equals( inMsg.getObject(EDK0291B010CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" WHERE DK0281.SOKO_CD = '000001' ") .append(" AND DK0281.YMD BETWEEN ? AND ? ") .append(" AND DK0281.MK_FLG = '0' ") .append(" ORDER BY DK0281.YMD "); replaceList.add(inMsg.getObject(EDK0291B010CBSMsg.KEY_TSTAYMD).toString()); replaceList.add(inMsg.getObject(EDK0291B010CBSMsg.KEY_TENDYMD).toString()); } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに 指定された値を設定 for (int idx = 0; idx < replaceList.size(); idx++ ) { CAANJDBCUtil.setParam(pstmt, idx + 1, replaceList.get(idx)); } //*************************** 自動生成ソース修正 END ***************************// // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EDK0291B010CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EDK0291B010CBSMsg1List.YMD , EDK0291B010CBSMsg1List.DOW_CD , EDK0291B010CBSMsg1List.DOW , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_01 , EDK0291B010CBSMsg1List.ZAIKO_RT_01 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_02 , EDK0291B010CBSMsg1List.ZAIKO_RT_02 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_03 , EDK0291B010CBSMsg1List.ZAIKO_RT_03 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_04 , EDK0291B010CBSMsg1List.ZAIKO_RT_04 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_05 , EDK0291B010CBSMsg1List.ZAIKO_RT_05 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_06 , EDK0291B010CBSMsg1List.ZAIKO_RT_06 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_07 , EDK0291B010CBSMsg1List.ZAIKO_RT_07 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_08 , EDK0291B010CBSMsg1List.ZAIKO_RT_08 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_09 , EDK0291B010CBSMsg1List.ZAIKO_RT_09 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_10 , EDK0291B010CBSMsg1List.ZAIKO_RT_10 // IT1-2018-0000201 ADD START , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_11 , EDK0291B010CBSMsg1List.ZAIKO_RT_11 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_12 , EDK0291B010CBSMsg1List.ZAIKO_RT_12 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_13 , EDK0291B010CBSMsg1List.ZAIKO_RT_13 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_14 , EDK0291B010CBSMsg1List.ZAIKO_RT_14 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_15 , EDK0291B010CBSMsg1List.ZAIKO_RT_15 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_16 , EDK0291B010CBSMsg1List.ZAIKO_RT_16 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_17 , EDK0291B010CBSMsg1List.ZAIKO_RT_17 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_18 , EDK0291B010CBSMsg1List.ZAIKO_RT_18 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_19 , EDK0291B010CBSMsg1List.ZAIKO_RT_19 , EDK0291B010CBSMsg1List.TAKNKIKI_MODEL_20 , EDK0291B010CBSMsg1List.ZAIKO_RT_20 // IT1-2018-0000201 ADD END }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EDK0291B010CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("EDK0291B010CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(EDK0291B010CBSMsg.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(EDK0291B010CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }