/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名 ：eo顧客基幹システム * モジュール名 ：JSYejbECH0551B010TPDA * ソースファイル名：JSYejbECH0551B010TPDA.java * 作成者 ：EKek0086 * 日付 ：2011年10月16日 *＜機能概要＞ * SQLFacility * テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン 修正日 修正者 修正内容 * ｖ1.00.00 * ｖ5.00.00 2013/07/26 FJ)河井 【TG1-2013-0000662】マスタ参照基準日対応 * ｖ32.00.00 2017/05/26 FJ)河邊 【OM-2017-0000488】オープンカーソルエラー * ********************************************************************************/ 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.JPCModelConstant; import eo.ejb.cbm.entity.CH0501ETMsg; import eo.ejb.cbm.entity.CH0551ETMsg; import eo.ejb.cbs.cbsmsg.ECH0551B010CBSMsg; import eo.ejb.cbs.cbsmsg.ECH0551B010CBSMsg1List; import eo.ejb.common.JPCModelCommon; /** * * テンプレートDBアクセス部品　ECH0551B010_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbECH0551B010TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbECH0551B010TPDA() { } /** * ECH0551B010_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void */ public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){ // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt1 = null; PreparedStatement pstmt2 = null; // リザルトセット ResultSet rsltQuery = null; // ページング用 int iTotalSearchNum = 0; // 総検索数 int iTotalPageNum = 0; // 総ページ数 int iDisplayNum = 0; // 表示件数 int iMaxSearchNum = 0; // 最大検索件数 int iDisplayPageNum = 0; // 表示ページ番号 // int iArrayCopyStart = 0; // 取得範囲先頭 // int iArrayCopyCount = 0; // 取得件数 // パラメータ設定リスト List replaceListCnt = new ArrayList(); /******************** * SQL文の作成 ********************/ try{ // 運用日付 String opeDate = (String)JPCModelCommon.getOpeDate(inMsg, inContext, null); // 請求年月の月末日取得 String endYmd = getUseEnd(inMsg, inContext, opeDate); //コネクション取得 con1 = JSYejbConnection.getConnection(CH0551ETMsg.getTableName()); // -------------------------------------------------- // // カウント取得 // -------------------------------------------------- // StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT COUNT(CH0551.PRC_TAIO_KIROK_NO) AS CNT "); // 条件1 追加 StringBuilder condition1 = getCondition1(inMsg, replaceListCnt); sql_Buff.append(condition1); //prepareStatementにSQL文をセット pstmt1 = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに 指定された値を設定 for (int idx = 0; idx < replaceListCnt.size(); idx++ ) { CAANJDBCUtil.setParam(pstmt1, idx+1, replaceListCnt.get(idx)); } // ResultSetの取得 rsltQuery = pstmt1.executeQuery(); // ECH0551B010CBSMsgの明細にセットする項目 String[] msgKeyList1 = { ECH0551B010CBSMsg.TOTAL_SEARCH_NUM }; rsltQuery.next(); mapMessage(rsltQuery, msgKeyList1, inMsg); // パラメータの設定 // パラメータの最大検索件数がnullでない場合、最大検索件数の設定 if (!inMsg.isNull(ECH0551B010CBSMsg.MAX_SEARCH_NUM)) { iMaxSearchNum = Integer.parseInt(inMsg.getString(ECH0551B010CBSMsg.MAX_SEARCH_NUM)); } // パラメータの表示件数がnullでない場合、表示件数の設定 if (!inMsg.isNull(ECH0551B010CBSMsg.DISPLAY_NUM)) { iDisplayNum = Integer.parseInt(inMsg.getString(ECH0551B010CBSMsg.DISPLAY_NUM)); } // パラメータの表示ページ番号がnullでない場合、表示ページ番号の設定 if (!inMsg.isNull(ECH0551B010CBSMsg.DISPLAY_PAGE_NUM)) { iDisplayPageNum = Integer.parseInt(inMsg.getString(ECH0551B010CBSMsg.DISPLAY_PAGE_NUM)); } // 総検索件数の設定 iTotalSearchNum = Integer.parseInt(inMsg.getString(ECH0551B010CBSMsg.TOTAL_SEARCH_NUM)); // 総ページ数の設定 // 表示件数が0の場合 if (0 == iDisplayNum) { iTotalPageNum = 1; } // 検索件数/表示件数の余りがない場合 else if (0 == iTotalSearchNum % iDisplayNum) { iTotalPageNum = iTotalSearchNum / iDisplayNum; } else { iTotalPageNum = iTotalSearchNum / iDisplayNum + 1; } // 総検索数と総ページ数の設定 inMsg.set(ECH0551B010CBSMsg.TOTAL_SEARCH_NUM, String.valueOf(iTotalSearchNum)); inMsg.set(ECH0551B010CBSMsg.TOTAL_PAGE_NUM, String.valueOf(iTotalPageNum)); // 検索エラーの設定 // 総検索件数が0の場合 if(0 == iTotalSearchNum) { inMsg.set(ECH0551B010CBSMsg.SEARCH_ERR_FLG , "1"); return; } // 総検索件数が最大検索件数より大きい場合(最大検索件数超え) else if(0 != iMaxSearchNum && iTotalSearchNum > iMaxSearchNum) { inMsg.set(ECH0551B010CBSMsg.SEARCH_ERR_FLG , "2"); return; } // 表示ページに該当するデータがない場合 else if(iTotalPageNum < iDisplayPageNum) { inMsg.set(ECH0551B010CBSMsg.SEARCH_ERR_FLG , "3"); return; } // 取得開始・終了行算出 int iStartNum = iDisplayNum * (iDisplayPageNum - 1) + 1; int iEndNum = iDisplayNum * iDisplayPageNum; // -------------------------------------------------- // // データ取得 // -------------------------------------------------- // sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT CH0551.PRC_TAIO_KIROK_NO ") .append(" ,CH0551.PRC_TAIO_USER_ID ") .append(" ,CH0551.PRC_TAIO_USER_ID_NM ") .append(" ,CH0551.SEIKY_KEI_NO ") .append(" ,CH0551.SVC_KEI_NO ") .append(" ,CH0551.PRC_TAIO_KIROK_DTM ") .append(" ,CH0551.PRC_TAIO_KIROK_SBT_CD ") .append(" ,CH0551.PRC_TAIO_KIROK_SBT_CD_NM ") .append(" ,CH0551.PRC_TAIO_KIROK_SBT_DTL_CD ") .append(" ,CH0551.PRC_TAIO_KIROK_SBT_DTL_CD_NM ") .append(" ,CH0551.TITLE ") .append(" ,CH0551.KIJI ") .append(" ,CH0551.CHG_BF_CD_SBT_CD ") .append(" ,CH0551.CHG_BF_CD_DIV ") .append(" ,CH0551.CHG_BF_CD_DIV_NM ") .append(" ,CH0551.CHG_AF_CD_SBT_CD ") .append(" ,CH0551.CHG_AF_CD_DIV ") .append(" ,CH0551.CHG_AF_CD_DIV_NM ") .append(" ,CH0551.CHG_BF_PRC_GRP_CD ") .append(" ,CH0551.CHG_BF_PRC_GRP_CD_NM ") .append(" ,CH0551.CHG_BF_PCRS_CD ") .append(" ,CH0551.CHG_BF_PCRS_CD_NM ") .append(" ,CH0551.CHG_BF_PRC_SVC_CD ") .append(" ,CH0551.CHG_BF_PRC_SVC_CD_NM ") .append(" ,CH0551.CHG_BF_PRC_KMK_CD ") .append(" ,CH0551.CHG_BF_PRC_KMK_CD_NM ") .append(" ,CH0551.CHG_AF_PRC_GRP_CD ") .append(" ,CH0551.CHG_AF_PRC_GRP_CD_NM ") .append(" ,CH0551.CHG_AF_PCRS_CD ") .append(" ,CH0551.CHG_AF_PCRS_CD_NM ") .append(" ,CH0551.CHG_AF_PRC_SVC_CD ") .append(" ,CH0551.CHG_AF_PRC_SVC_CD_NM ") .append(" ,CH0551.CHG_AF_PRC_KMK_CD ") .append(" ,CH0551.CHG_AF_PRC_KMK_CD_NM ") .append(" ,CH0551.CHG_BF_YMD ") .append(" ,CH0551.CHG_AF_YMD ") .append(" ,CH0551.CHG_BF_AMNT ") .append(" ,CH0551.CHG_AF_AMNT ") .append(" ,CH0551.KIROK_TG_YM ") .append(" ,CH0551.TOKUSOKU_NO ") .append(" ,CH0551.TOKUSOKU_IDO_NO ") .append(" ,CH0551.SEIKY_NO ") .append(" ,CH0551.TAIORRKI_RENDO_TG_FLG ") .append(" ,CH0551.TAIORRKI_RENDO_TG_FLG_NM ") .append(" ,CH0551.TAIO_KIROK_NO ") .append(" FROM ( "); sql_Buff.append(" SELECT CH0551.PRC_TAIO_KIROK_NO ") .append(" ,CH0551.PRC_TAIO_USER_ID ") .append(" ,(SELECT ZM0051.USER_NM ") .append(" FROM ZM_M_USER ZM0051 ") .append(" WHERE ZM0051.USER_ID = CH0551.PRC_TAIO_USER_ID ") .append(" AND ZM0051.MK_FLG = '0' ") .append(" ) AS PRC_TAIO_USER_ID_NM ") .append(" ,CH0551.SEIKY_KEI_NO ") .append(" ,CH0551.SVC_KEI_NO ") .append(" ,CH0551.TAIO_DTM PRC_TAIO_KIROK_DTM ") .append(" ,CH0551.PRC_TAIO_KIROK_SBT_CD ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00916' ") .append(" AND ZM0171.CD_DIV = CH0551.PRC_TAIO_KIROK_SBT_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS PRC_TAIO_KIROK_SBT_CD_NM ") .append(" ,CH0551.PRC_TAIO_KIROK_SBT_DTL_CD ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00917' ") .append(" AND ZM0171.CD_DIV = CH0551.PRC_TAIO_KIROK_SBT_DTL_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS PRC_TAIO_KIROK_SBT_DTL_CD_NM ") .append(" ,CH0551.TITLE ") .append(" ,CH0551.KIJI ") .append(" ,CH0551.CHG_BF_CD_SBT_CD ") .append(" ,CH0551.CHG_BF_CD_DIV ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = CH0551.CHG_BF_CD_SBT_CD ") .append(" AND ZM0171.CD_DIV = CH0551.CHG_BF_CD_DIV ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD AND ZM0171.MK_FLG = '0' ") .append(" ) AS CHG_BF_CD_DIV_NM ") .append(" ,CH0551.CHG_AF_CD_SBT_CD ") .append(" ,CH0551.CHG_AF_CD_DIV ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = CH0551.CHG_AF_CD_SBT_CD ") .append(" AND ZM0171.CD_DIV = CH0551.CHG_AF_CD_DIV ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS CHG_AF_CD_DIV_NM ") .append(" ,CH0551.CHG_BF_PRC_GRP_CD ") .append(" ,( SELECT ") .append(" KK0781.PRC_GRP_NM ") .append(" FROM ") .append(" KK_M_PRC_GRP KK0781 ") .append(" WHERE ") .append(" KK0781.PRC_GRP_CD = CH0551.CHG_BF_PRC_GRP_CD ") .append(" AND KK0781.PRC_GRP_TSTAYMD || KK0781.GENE_ADD_DTM = ") .append(" ( SELECT ") .append(" MAX(KK0781_GENE.PRC_GRP_TSTAYMD || KK0781_GENE.GENE_ADD_DTM) ") .append(" FROM ") .append(" KK_M_PRC_GRP KK0781_GENE ") .append(" WHERE ") .append(" KK0781_GENE.PRC_GRP_CD = KK0781.PRC_GRP_CD ") .append(" AND KK0781_GENE.PRC_GRP_TSTAYMD <= ? ") .append(" AND KK0781_GENE.MK_FLG = '0' ) ) AS CHG_BF_PRC_GRP_CD_NM ") .append(" ,CH0551.CHG_BF_PCRS_CD ") .append(" ,(SELECT KK0791.PCRS_NM ") .append(" FROM KK_M_PCRS KK0791 ") .append(" WHERE KK0791.PCRS_CD = CH0551.CHG_BF_PCRS_CD ") .append(" AND KK0791.PCRS_TSTAYMD || KK0791.GENE_ADD_DTM = ") .append(" ( SELECT ") .append(" MAX(KK0791_GENE.PCRS_TSTAYMD || KK0791_GENE.GENE_ADD_DTM) AS KK0791_MAX ") .append(" FROM ") .append(" KK_M_PCRS KK0791_GENE ") .append(" WHERE ") .append(" KK0791_GENE.PCRS_CD = KK0791.PCRS_CD ") .append(" AND KK0791_GENE.PCRS_TSTAYMD <= ? ") .append(" AND KK0791_GENE.MK_FLG = '0' ) ) AS CHG_BF_PCRS_CD_NM ") .append(" ,CH0551.CHG_BF_PRC_SVC_CD ") .append(" ,(SELECT AC0181.SCREEN_DSP_NM ") .append(" FROM AC_M_PRC_SVC_KANRI AC0181 ") .append(" WHERE AC0181.SYS_CD = 'AC' ") .append(" AND AC0181.PCRS_CD = CH0551.CHG_BF_PCRS_CD ") .append(" AND AC0181.PRC_SVC_CD = CH0551.CHG_BF_PRC_SVC_CD ") .append(" AND AC0181.PRC_SVC_KANRI_TSTAYMD || AC0181.GENE_ADD_DTM =") .append(" ( SELECT ") .append(" MAX(AC0181_GENE.PRC_SVC_KANRI_TSTAYMD || AC0181_GENE.GENE_ADD_DTM) ") .append(" FROM ") .append(" AC_M_PRC_SVC_KANRI AC0181_GENE ") .append(" WHERE ") .append(" AC0181_GENE.SYS_CD = AC0181.SYS_CD ") .append(" AND AC0181_GENE.PCRS_CD = AC0181.PCRS_CD ") .append(" AND AC0181_GENE.PRC_SVC_CD = AC0181.PRC_SVC_CD ") .append(" AND AC0181_GENE.PRC_SVC_KANRI_TSTAYMD <= ? ") .append(" AND AC0181_GENE.MK_FLG = '0' ) ") .append(" AND ROWNUM = 1 ") .append(" ) AS CHG_BF_PRC_SVC_CD_NM ") .append(" ,CH0551.CHG_BF_PRC_KMK_CD ") .append(" ,(SELECT CH0391.PRC_KMK_NM ") .append(" FROM CH_M_PRC_KMK_OPUT_NM CH0391 ") .append(" WHERE CH0391.PRC_KMK_NM_SBT_CD = '1' ") .append(" AND CH0391.PRC_GRP_CD = CH0551.CHG_BF_PRC_GRP_CD ") .append(" AND CH0391.PCRS_CD = CH0551.CHG_BF_PCRS_CD ") .append(" AND CH0391.PRC_SVC_CD = CH0551.CHG_BF_PRC_SVC_CD ") .append(" AND CH0391.PRC_KMK_CD = CH0551.CHG_BF_PRC_KMK_CD ") .append(" AND CH0391.PRC_KMK_OPUT_NM_TSTAYMD || CH0391.GENE_ADD_DTM = ") .append(" ( SELECT ") .append(" MAX(CH0391_GENE.PRC_KMK_OPUT_NM_TSTAYMD || CH0391_GENE.GENE_ADD_DTM) ") .append(" FROM ") .append(" CH_M_PRC_KMK_OPUT_NM CH0391_GENE ") .append(" WHERE ") .append(" CH0391_GENE.PRC_KMK_NM_SBT_CD = CH0391.PRC_KMK_NM_SBT_CD ") .append(" AND CH0391_GENE.PRC_GRP_CD = CH0391.PRC_GRP_CD ") .append(" AND CH0391_GENE.PCRS_CD = CH0391.PCRS_CD ") .append(" AND CH0391_GENE.PRC_SVC_CD = CH0391.PRC_SVC_CD ") .append(" AND CH0391_GENE.PRC_KMK_CD = CH0391.PRC_KMK_CD ") .append(" AND CH0391_GENE.PRC_KMK_OPUT_NM_TSTAYMD <= ? ") .append(" AND CH0391_GENE.MK_FLG = '0' ) ") .append(" AND ROWNUM = 1 ") .append(" ) AS CHG_BF_PRC_KMK_CD_NM ") .append(" ,CH0551.CHG_AF_PRC_GRP_CD ") .append(" ,( SELECT ") .append(" KK0781.PRC_GRP_NM ") .append(" FROM ") .append(" KK_M_PRC_GRP KK0781 ") .append(" WHERE ") .append(" KK0781.PRC_GRP_CD = CH0551.CHG_AF_PRC_GRP_CD ") .append(" AND KK0781.PRC_GRP_TSTAYMD || KK0781.GENE_ADD_DTM = ") .append(" ( SELECT ") .append(" MAX(KK0781_GENE.PRC_GRP_TSTAYMD || KK0781_GENE.GENE_ADD_DTM) ") .append(" FROM ") .append(" KK_M_PRC_GRP KK0781_GENE ") .append(" WHERE ") .append(" KK0781_GENE.PRC_GRP_CD = KK0781.PRC_GRP_CD ") .append(" AND KK0781_GENE.PRC_GRP_TSTAYMD <= ? ") .append(" AND KK0781_GENE.MK_FLG = '0' ) ) AS CHG_AF_PRC_GRP_CD_NM ") .append(" ,CH0551.CHG_AF_PCRS_CD ") .append(" ,(SELECT KK0791.PCRS_NM ") .append(" FROM KK_M_PCRS KK0791 ") .append(" WHERE KK0791.PCRS_CD = CH0551.CHG_AF_PCRS_CD ") .append(" AND KK0791.PCRS_TSTAYMD || KK0791.GENE_ADD_DTM = ") .append(" ( SELECT ") .append(" MAX(KK0791_GENE.PCRS_TSTAYMD || KK0791_GENE.GENE_ADD_DTM) AS KK0791_MAX ") .append(" FROM ") .append(" KK_M_PCRS KK0791_GENE ") .append(" WHERE ") .append(" KK0791_GENE.PCRS_CD = KK0791.PCRS_CD ") .append(" AND KK0791_GENE.PCRS_TSTAYMD <= ? ") .append(" AND KK0791_GENE.MK_FLG = '0' ) ) AS CHG_AF_PCRS_CD_NM ") .append(" ,CH0551.CHG_AF_PRC_SVC_CD ") .append(" ,(SELECT AC0181.SCREEN_DSP_NM ") .append(" FROM AC_M_PRC_SVC_KANRI AC0181 ") .append(" WHERE AC0181.SYS_CD = 'AC' ") .append(" AND AC0181.PCRS_CD = CH0551.CHG_AF_PCRS_CD ") .append(" AND AC0181.PRC_SVC_CD = CH0551.CHG_AF_PRC_SVC_CD ") .append(" AND AC0181.PRC_SVC_KANRI_TSTAYMD || AC0181.GENE_ADD_DTM = ") .append(" ( SELECT ") .append(" MAX(AC0181_GENE.PRC_SVC_KANRI_TSTAYMD || AC0181_GENE.GENE_ADD_DTM) AS AC0181_MAX ") .append(" FROM ") .append(" AC_M_PRC_SVC_KANRI AC0181_GENE ") .append(" WHERE ") .append(" AC0181_GENE.SYS_CD = AC0181.SYS_CD ") .append(" AND AC0181_GENE.PCRS_CD = AC0181.PCRS_CD ") .append(" AND AC0181_GENE.PRC_SVC_CD = AC0181.PRC_SVC_CD ") .append(" AND AC0181_GENE.PRC_SVC_KANRI_TSTAYMD <= ? ") .append(" AND AC0181_GENE.MK_FLG = '0' ) ) AS CHG_AF_PRC_SVC_CD_NM ") .append(" ,CH0551.CHG_AF_PRC_KMK_CD ") .append(" ,(SELECT CH0391.PRC_KMK_NM ") .append(" FROM CH_M_PRC_KMK_OPUT_NM CH0391 ") .append(" WHERE CH0391.PRC_KMK_NM_SBT_CD = '1' ") .append(" AND CH0391.PRC_GRP_CD = CH0551.CHG_AF_PRC_GRP_CD ") .append(" AND CH0391.PCRS_CD = CH0551.CHG_AF_PCRS_CD ") .append(" AND CH0391.PRC_SVC_CD = CH0551.CHG_AF_PRC_SVC_CD ") .append(" AND CH0391.PRC_KMK_CD = CH0551.CHG_AF_PRC_KMK_CD ") .append(" AND CH0391.PRC_KMK_OPUT_NM_TSTAYMD || CH0391.GENE_ADD_DTM = ") .append(" ( SELECT ") .append(" MAX(CH0391_GENE.PRC_KMK_OPUT_NM_TSTAYMD || CH0391_GENE.GENE_ADD_DTM) AS CH0391_MAX ") .append(" FROM ") .append(" CH_M_PRC_KMK_OPUT_NM CH0391_GENE ") .append(" WHERE ") .append(" CH0391_GENE.PRC_KMK_NM_SBT_CD = CH0391.PRC_KMK_NM_SBT_CD ") .append(" AND CH0391_GENE.PRC_GRP_CD = CH0391.PRC_GRP_CD ") .append(" AND CH0391_GENE.PCRS_CD = CH0391.PCRS_CD ") .append(" AND CH0391_GENE.PRC_SVC_CD = CH0391.PRC_SVC_CD ") .append(" AND CH0391_GENE.PRC_KMK_CD = CH0391.PRC_KMK_CD ") .append(" AND CH0391_GENE.PRC_KMK_OPUT_NM_TSTAYMD <= ? ") .append(" AND CH0391_GENE.MK_FLG = '0' ) ") .append(" AND ROWNUM = 1 ") .append(" ) AS CHG_AF_PRC_KMK_CD_NM ") .append(" ,CH0551.CHG_BF_YMD ") .append(" ,CH0551.CHG_AF_YMD ") .append(" ,CH0551.CHG_BF_AMNT ") .append(" ,CH0551.CHG_AF_AMNT ") .append(" ,CH0551.KIROK_TG_YM ") .append(" ,CH0551.TOKUSOKU_NO ") .append(" ,CH0551.TOKUSOKU_IDO_NO ") .append(" ,CH0551.SEIKY_NO ") .append(" ,CH0551.TAIORRKI_RENDO_TG_FLG ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00984' ") .append(" AND ZM0171.CD_DIV = CH0551.TAIORRKI_RENDO_TG_FLG ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS TAIORRKI_RENDO_TG_FLG_NM ") .append(" ,CH0551.TAIO_KIROK_NO ") .append(" ,ROW_NUMBER() OVER(ORDER BY CH0551.TAIO_DTM DESC,CH0551.PRC_TAIO_KIROK_SBT_CD ASC,CH0551.PRC_TAIO_KIROK_SBT_DTL_CD ASC) AS ROW_NO "); // from句、where句のパラメータはreplaceListCntにセット済であるため、リストに挿入する int insIndex = 0; replaceListCnt.add(insIndex++, opeDate); replaceListCnt.add(insIndex++, opeDate); replaceListCnt.add(insIndex++, opeDate); replaceListCnt.add(insIndex++, opeDate); if("1".equals( inMsg.getString(ECH0551B010CBSMsg.FUNC_CODE))) { replaceListCnt.add(insIndex++, endYmd); replaceListCnt.add(insIndex++, endYmd); replaceListCnt.add(insIndex++, endYmd); replaceListCnt.add(insIndex++, endYmd); replaceListCnt.add(insIndex++, endYmd); replaceListCnt.add(insIndex++, endYmd); replaceListCnt.add(insIndex++, endYmd); replaceListCnt.add(insIndex++, endYmd); } else if("2".equals( inMsg.getString(ECH0551B010CBSMsg.FUNC_CODE))) { replaceListCnt.add(insIndex++, opeDate); replaceListCnt.add(insIndex++, opeDate); replaceListCnt.add(insIndex++, opeDate); replaceListCnt.add(insIndex++, opeDate); replaceListCnt.add(insIndex++, opeDate); replaceListCnt.add(insIndex++, opeDate); replaceListCnt.add(insIndex++, opeDate); replaceListCnt.add(insIndex++, opeDate); } replaceListCnt.add(insIndex++, opeDate); // 条件1 追加 sql_Buff.append(condition1); // ソート条件 追加 sql_Buff.append(getSortCondition(inMsg)); // sql_Buff.append(" ) CH0551 "); sql_Buff.append(" WHERE CH0551.ROW_NO BETWEEN ? AND ? "); replaceListCnt.add(iStartNum); replaceListCnt.add(iEndNum); // //prepareStatementにSQL文をセット pstmt2 = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに 指定された値を設定 for (int idx = 0; idx < replaceListCnt.size(); idx++ ) { CAANJDBCUtil.setParam(pstmt2, idx+1, replaceListCnt.get(idx)); } // OM-2017-0000488 2017/05/26 ADD START if(rsltQuery != null){ rsltQuery.close(); } // OM-2017-0000488 2017/05/26 ADD END // ResultSetの取得 rsltQuery = pstmt2.executeQuery(); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // OM-2017-0000488 2017/05/26 ADD START if(rsltQuery != null){ rsltQuery.close(); } // OM-2017-0000488 2017/05/26 ADD END // ResultSetの取得 rsltQuery = pstmt2.executeQuery(); // ECH0551B010CBSMsgの明細にセットする項目 String[] msgKeyList2 = { ECH0551B010CBSMsg1List.PRC_TAIO_KIROK_NO , ECH0551B010CBSMsg1List.PRC_TAIO_USER_ID , ECH0551B010CBSMsg1List.PRC_TAIO_USER_ID_NM , ECH0551B010CBSMsg1List.SEIKY_KEI_NO , ECH0551B010CBSMsg1List.SVC_KEI_NO , ECH0551B010CBSMsg1List.PRC_TAIO_KIROK_DTM , ECH0551B010CBSMsg1List.PRC_TAIO_KIROK_SBT_CD , ECH0551B010CBSMsg1List.PRC_TAIO_KIROK_SBT_CD_NM , ECH0551B010CBSMsg1List.PRC_TAIO_KIROK_SBT_DTL_CD , ECH0551B010CBSMsg1List.PRC_TAIO_KIROK_SBT_DTL_CD_NM , ECH0551B010CBSMsg1List.TITLE , ECH0551B010CBSMsg1List.KIJI , ECH0551B010CBSMsg1List.CHG_BF_CD_SBT_CD , ECH0551B010CBSMsg1List.CHG_BF_CD_DIV , ECH0551B010CBSMsg1List.CHG_BF_CD_DIV_NM , ECH0551B010CBSMsg1List.CHG_AF_CD_SBT_CD , ECH0551B010CBSMsg1List.CHG_AF_CD_DIV , ECH0551B010CBSMsg1List.CHG_AF_CD_DIV_NM , ECH0551B010CBSMsg1List.CHG_BF_PRC_GRP_CD , ECH0551B010CBSMsg1List.CHG_BF_PRC_GRP_CD_NM , ECH0551B010CBSMsg1List.CHG_BF_PCRS_CD , ECH0551B010CBSMsg1List.CHG_BF_PCRS_CD_NM , ECH0551B010CBSMsg1List.CHG_BF_PRC_SVC_CD , ECH0551B010CBSMsg1List.CHG_BF_PRC_SVC_CD_NM , ECH0551B010CBSMsg1List.CHG_BF_PRC_KMK_CD , ECH0551B010CBSMsg1List.CHG_BF_PRC_KMK_CD_NM , ECH0551B010CBSMsg1List.CHG_AF_PRC_GRP_CD , ECH0551B010CBSMsg1List.CHG_AF_PRC_GRP_CD_NM , ECH0551B010CBSMsg1List.CHG_AF_PCRS_CD , ECH0551B010CBSMsg1List.CHG_AF_PCRS_CD_NM , ECH0551B010CBSMsg1List.CHG_AF_PRC_SVC_CD , ECH0551B010CBSMsg1List.CHG_AF_PRC_SVC_CD_NM , ECH0551B010CBSMsg1List.CHG_AF_PRC_KMK_CD , ECH0551B010CBSMsg1List.CHG_AF_PRC_KMK_CD_NM , ECH0551B010CBSMsg1List.CHG_BF_YMD , ECH0551B010CBSMsg1List.CHG_AF_YMD , ECH0551B010CBSMsg1List.CHG_BF_AMNT , ECH0551B010CBSMsg1List.CHG_AF_AMNT , ECH0551B010CBSMsg1List.KIROK_TG_YM , ECH0551B010CBSMsg1List.TOKUSK_NO , ECH0551B010CBSMsg1List.TOKUSK_IDO_NO , ECH0551B010CBSMsg1List.SEIKY_NO , ECH0551B010CBSMsg1List.TAIORRKI_RENDO_TG_FLG , ECH0551B010CBSMsg1List.TAIORRKI_RENDO_TG_FLG_NM , ECH0551B010CBSMsg1List.TAIO_KIROK_NO }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList2,"eo.ejb.cbs.cbsmsg.ECH0551B010CBSMsg1List",0); // inMsg(CBSMsg)に取得した明細をセット inMsg.set("ECH0551B010CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(ECH0551B010CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } finally { // 資源の解放 try{ if(rsltQuery != null){ rsltQuery.close(); } if(pstmt1 != null){ pstmt1.close(); } if(pstmt2 != null){ pstmt2.close(); } if(con1 != null){ closeConnection(con1); } }catch(SQLException e){ inMsg.set(ECH0551B010CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } /** * ECH0551B010_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @param replaceListCnt パラメータ設定リスト
* @return StringBuilder */ private StringBuilder getCondition1(CAANMsg inMsg, List replaceListCnt){ String funcCode = inMsg.getString(ECH0551B010CBSMsg.FUNC_CODE); String seikyKeiNo = inMsg.getString(ECH0551B010CBSMsg.KEY_SEIKY_KEI_NO); StringBuilder sql_Buff = new StringBuilder(); if (JPCModelConstant.FUNC_CD_1.equals(funcCode) || JPCModelConstant.FUNC_CD_2.equals(funcCode)) { sql_Buff.append(" FROM CH_T_PRC_TAIOKRK CH0551 ") .append(" WHERE CH0551.SEIKY_KEI_NO = ? ") .append(" AND CH0551.MK_FLG = '0' "); replaceListCnt.add(seikyKeiNo); } return sql_Buff; } /** * ECH0551B010_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return StringBuilder */ private StringBuilder getSortCondition(CAANMsg inMsg){ String funcCode = inMsg.getString(ECH0551B010CBSMsg.FUNC_CODE); StringBuilder sql_Buff = new StringBuilder(); if (JPCModelConstant.FUNC_CD_1.equals(funcCode) || JPCModelConstant.FUNC_CD_2.equals(funcCode)) { sql_Buff.append(" ORDER BY ") .append(" CH0551.TAIO_DTM DESC, ") .append(" CH0551.PRC_TAIO_KIROK_SBT_CD ASC, ") .append(" CH0551.PRC_TAIO_KIROK_SBT_DTL_CD ASC "); } return sql_Buff; } /** * イベント年月日より請求年月の月末日を取得します。
* @param msg CBSメッセージ * @param context エージェントディスパッチコンテキスト * @param eventYmd イベント年月日 * @return String YYYYMMDD形式の月末日 */ private String getUseEnd(CAANMsg inCBSMsg, AgentDispatchContext inContext, String eventYmd) { String endYmd = ""; if (eventYmd == null) { return ""; } // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt = null; // リザルトセット ResultSet rsltQuery = null; try { //コネクション取得 con1 = JSYejbConnection.getConnection(CH0501ETMsg.getTableName()); StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT ") .append(" CH0501.EVENT_YMD ") .append(" FROM ") .append(" CH_M_PRC_SCHDL_TEIGI CH0501 ") .append(" WHERE ") .append(" CH0501.EVENT_CD = '02' ") .append(" AND CH0501.SEIKY_YM = (SELECT ") .append(" MIN(CH0501_SUB.SEIKY_YM) AS SEIKY_YM ") .append(" FROM ") .append(" CH_M_PRC_SCHDL_TEIGI CH0501_SUB ") .append(" WHERE ") .append(" CH0501_SUB.EVENT_CD = '03' ") .append(" AND CH0501_SUB.EVENT_YMD >= ? ") .append(" AND CH0501_SUB.MK_FLG = '0') "); //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // パラメータの設定 CAANJDBCUtil.setParam(pstmt, 1, eventYmd); // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // 戻り値の判定 if (rsltQuery.next()) { endYmd = rsltQuery.getString(CH0501ETMsg.EVENT_YMD); } } catch(SQLException e) { inCBSMsg.set(ECH0551B010CBSMsg.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) { inCBSMsg.set(ECH0551B010CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } return endYmd; } }