/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名 ：eo顧客基幹システム * モジュール名 ：JSYejbEAC0621B010TPDA * ソースファイル名：JSYejbEAC0621B010TPDA.java * 作成者 ：EK909278 * 日付 ：2013年03月13日 *＜機能概要＞ * コンテンツ通話明細一覧照会テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン 修正日 修正者 修正内容 * ｖ1.00.00 * ｖ6.00.00 2014/01/22 FJ)垣内 【OM-2014-0000268】機能コード"4"のとき、課金可否をWHERE句に追加 * ｖ32.00.00 2017/05/26 FJ)河邊 【OM-2017-0000488】オープンカーソルエラー * v33.00.00 2017/08/25 FJ)田中 【ANK-3228-00-00】通話明細表示条件の改修対応 * ********************************************************************************/ package eo.ejb.cbs.sqlf; import com.fujitsu.futurity.model.base.CAANMsg; import com.fujitsu.futurity.model.base.CAANSQLFacility; import com.fujitsu.futurity.model.base.CAANRuntimeException; import com.fujitsu.futurity.model.base.CAANJDBCUtil; 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.TemplateDBAccessHandler; import com.fujitsu.futurity.model.ejb.common.fw.AgentDispatchContext; 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 eo.common.constant.JPCModelConstant; import eo.ejb.cbs.cbsmsg.EAC0621B010CBSMsg; import eo.ejb.cbs.cbsmsg.EAC0621B010CBSMsg1List; import eo.ejb.cbm.entity.AC0621ETMsg; /** * * テンプレートDBアクセス部品　EAC0621B010_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEAC0621B010TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbEAC0621B010TPDA() { } /** * EAC0621B010_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; // パラメータ設定リスト List replaceListCnt = new ArrayList(); /******************** * SQL文の作成 ********************/ try{ //コネクション取得 con1 = JSYejbConnection.getConnection(AC0621ETMsg.getTableName()); /******************** * カウント取得 ********************/ StringBuilder sql_Buff = new StringBuilder(); sql_Buff.append(" SELECT ") .append(" COUNT(1) AS CNT ") .append(" FROM ") .append(" AC_T_CN050AP_TW_DTL AC0621 ") .append(" LEFT OUTER JOIN ") .append(" AC_M_TUWA_SBT AC0571 ") .append(" ON AC0571.PCALL_SBT_CD = AC0621.PCALL_SBT_CD ") .append(" AND AC0571.TUWA_SVC_SKBT_CD = AC0621.TUWA_SVC_SKBT_CD ") .append(" AND ? BETWEEN AC0571.TUWA_SBT_TSTAYMD AND AC0571.TUWA_SBT_TENDYMD ") .append(" AND AC0571.MK_FLG = '0' "); replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_UNYO_YMD)); // ＫＥＹ＿運用日付 // 条件 追加 StringBuilder condition = getCondition(inMsg, replaceListCnt); sql_Buff.append(condition); //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(); // ECH0011B010CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EAC0621B010CBSMsg.TOTAL_SEARCH_NUM }; rsltQuery.next(); mapMessage(rsltQuery, msgKeyList1, inMsg); // 総ページ数設定 int iTotal = Integer.parseInt(inMsg.getString(EAC0621B010CBSMsg.TOTAL_SEARCH_NUM)); int iDispNum = iTotal; if (!inMsg.isNull(EAC0621B010CBSMsg.DISPLAY_NUM)) { iDispNum = Integer.parseInt(inMsg.getString(EAC0621B010CBSMsg.DISPLAY_NUM)); } int iMaxSerchNum = iTotal; if (!inMsg.isNull(EAC0621B010CBSMsg.MAX_SEARCH_NUM)) { iMaxSerchNum = Integer.parseInt(inMsg.getString(EAC0621B010CBSMsg.MAX_SEARCH_NUM)); } inMsg.set(EAC0621B010CBSMsg.TOTAL_SEARCH_NUM, Integer.toString(iTotal)); // 検索件数が0件の場合、エラーフラグに"1"を設定して処理終了 if (0 == (Integer.parseInt(inMsg.getString(EAC0621B010CBSMsg.TOTAL_SEARCH_NUM)))) { inMsg.set(EAC0621B010CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_ZERO); inMsg.set(EAC0621B010CBSMsg.TOTAL_PAGE_NUM, Integer.toString(iTotal)); return; } int iSearchDivDisp = iTotal / iDispNum; int iRest = iTotal % iDispNum; // 割り切れない場合は総ページ数を増やす if (0 != iRest) { iSearchDivDisp++; } inMsg.set(EAC0621B010CBSMsg.TOTAL_PAGE_NUM, Integer.toString(iSearchDivDisp)); // 検索件数が最大検索件数を超えた場合、エラーフラグに"2"を設定して処理終了 if (iMaxSerchNum < iTotal) { inMsg.set(EAC0621B010CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_OVERMAX); return; } // 表示ページに該当するデータがない場合、エラーフラグに"3"を設定して処理終了 int iDispPageNum = 1; if (!inMsg.isNull(EAC0621B010CBSMsg.DISPLAY_PAGE_NUM)) { iDispPageNum = Integer.parseInt(inMsg.getString(EAC0621B010CBSMsg.DISPLAY_PAGE_NUM)); } if (iSearchDivDisp < iDispPageNum) { inMsg.set(EAC0621B010CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_NODATA); return; } int iStartNum = iDispNum * (iDispPageNum - 1) + 1; int iEndNum = iDispNum * iDispPageNum; /******************** * SQL文_基本部 ********************/ sql_Buff = new StringBuilder(); sql_Buff.append(" SELECT ") .append(" AC0621.PCALL_STAYMD ") .append(" ,AC0621.PCALL_STA_HMS ") .append(" ,AC0621.PCALL_ENDYMD ") .append(" ,AC0621.PCALL_END_HMS ") .append(" ,AC0621.PCALL_SEC_CNT ") .append(" ,AC0621.INCOMING_TELNO ") .append(" ,AC0621.PCALL_PRC ") .append(" ,AC0621.PCALL_SBT_CD ") .append(" ,AC0621.TUWA_SBT_ALI ") .append(" ,AC0621.CHRG_KH ") .append(" ,AC0621.CD_DIV_ALI ") .append(" ,AC0621.COUNTRY_NM ") .append(" ,AC0621.KAZEI_HIKAZEI_CD ") .append(" FROM ") .append(" ( SELECT ") .append(" AC0621.PCALL_STAYMD ") .append(" ,AC0621.PCALL_STA_HMS ") .append(" ,AC0621.PCALL_ENDYMD ") .append(" ,AC0621.PCALL_END_HMS ") .append(" ,AC0621.PCALL_SEC_CNT ") .append(" ,AC0621.INCOMING_TELNO ") .append(" ,AC0621.PCALL_PRC ") .append(" ,AC0621.PCALL_SBT_CD ") .append(" ,AC0571.TUWA_SBT_ALI ") .append(" ,AC0621.CHRG_KH ") .append(" ,( SELECT ") .append(" ZM0171.CD_DIV_NM ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ") .append(" ZM0171.CD_SBT_CD = ? ") .append(" AND ZM0171.CD_DIV = AC0621.CHRG_KH ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS CD_DIV_ALI ") .append(" ,AC0621.COUNTRY_NM ") .append(" ,AC0621.KAZEI_HIKAZEI_CD ") .append(" ,ROW_NUMBER() OVER(ORDER BY AC0621.PCALL_STAYMD, AC0621.PCALL_STA_HMS, AC0621.PCALL_SBT_CD ) AS ROW_NO ") .append(" FROM ") .append(" AC_T_CN050AP_TW_DTL AC0621 ") .append(" LEFT OUTER JOIN ") .append(" AC_M_TUWA_SBT AC0571 ") .append(" ON AC0571.PCALL_SBT_CD = AC0621.PCALL_SBT_CD ") .append(" AND AC0571.TUWA_SVC_SKBT_CD = AC0621.TUWA_SVC_SKBT_CD ") .append(" AND ? BETWEEN AC0571.TUWA_SBT_TSTAYMD AND AC0571.TUWA_SBT_TENDYMD ") .append(" AND AC0571.MK_FLG = '0' "); replaceListCnt = new ArrayList(); replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_CD_SBT_CD)); // ＫＥＹ＿コード種別コード replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_UNYO_YMD)); // ＫＥＹ＿運用日付 replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_UNYO_YMD)); // ＫＥＹ＿運用日付 // 条件 追加 condition = getCondition(inMsg, replaceListCnt); sql_Buff.append(condition); sql_Buff.append(" ORDER BY ROW_NO ") .append(" ) AC0621 ") .append(" WHERE ") .append(" AC0621.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(); // EAC0621B010CBSMsgの明細にセットする項目 String[] msgKeyList2 = { EAC0621B010CBSMsg1List.PCALL_STAYMD , EAC0621B010CBSMsg1List.PCALL_STA_HMS , EAC0621B010CBSMsg1List.PCALL_ENDYMD , EAC0621B010CBSMsg1List.PCALL_END_HMS , EAC0621B010CBSMsg1List.PCALL_SEC_CNT , EAC0621B010CBSMsg1List.INCOMING_TELNO , EAC0621B010CBSMsg1List.PCALL_PRC , EAC0621B010CBSMsg1List.PCALL_SBT , EAC0621B010CBSMsg1List.TUWA_SBT_ALI , EAC0621B010CBSMsg1List.CHRG_KH , EAC0621B010CBSMsg1List.CD_DIV_ALI , EAC0621B010CBSMsg1List.COUNTRY_NM , EAC0621B010CBSMsg1List.KAZEI_HIKAZEI_CD }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList2,"eo.ejb.cbs.cbsmsg.EAC0621B010CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("EAC0621B010CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(EAC0621B010CBSMsg.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(EAC0621B010CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } /** * EAC0621B010_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @param replaceListCnt パラメータ設定リスト
* @return StringBuilder */ private StringBuilder getCondition(CAANMsg inMsg, List replaceListCnt){ String funcCode = inMsg.getString(EAC0621B010CBSMsg.FUNC_CODE); StringBuilder sql_Buff = new StringBuilder(); sql_Buff.append(" WHERE "); if (JPCModelConstant.FUNC_CD_1.equals(funcCode)) { sql_Buff.append(" AC0621.SYSID = ? ") .append(" AND AC0621.KAKINS_TELNO = ? ") // ANK-3228-00-00 DEL START //.append(" AND AC0621.PCALL_STAYMD BETWEEN ? AND ? ") // ANK-3228-00-00 DEL END // ANK-3228-00-00 ADD START .append(" AND AC0621.PCALL_ENDYMD BETWEEN ? AND ? ") // ANK-3228-00-00 ADD END .append(" AND AC0621.CHRG_KH = ? ") .append(" AND AC0621.MK_FLG = '0' "); replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_SYSID)); // ＫＥＹ＿ＳＹＳＩＤ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_KAKINS_TELNO)); // ＫＥＹ＿課金先電話番号 replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_PCALL_STAYMD_FROM_1)); // ＫＥＹ＿通話開始年月日＿ＦＲＯＭ＿１ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_PCALL_STAYMD_TO_1)); // ＫＥＹ＿通話開始年月日＿ＴＯ＿１ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_CHRG_KH)); // ＫＥＹ＿課金可否 } else if (JPCModelConstant.FUNC_CD_2.equals(funcCode)) { sql_Buff.append(" AC0621.SYSID = ? ") .append(" AND AC0621.KAKINS_TELNO = ? ") // ANK-3228-00-00 DEL START //.append(" AND AC0621.PCALL_STAYMD BETWEEN ? AND ? ") // ANK-3228-00-00 DEL END // ANK-3228-00-00 ADD START .append(" AND AC0621.PCALL_ENDYMD BETWEEN ? AND ? ") // ANK-3228-00-00 ADD END .append(" AND AC0621.MK_FLG = '0' "); replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_SYSID)); // ＫＥＹ＿ＳＹＳＩＤ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_KAKINS_TELNO)); // ＫＥＹ＿課金先電話番号 replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_PCALL_STAYMD_FROM_1)); // ＫＥＹ＿通話開始年月日＿ＦＲＯＭ＿１ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_PCALL_STAYMD_TO_1)); // ＫＥＹ＿通話開始年月日＿ＴＯ＿１ } else if (JPCModelConstant.FUNC_CD_3.equals(funcCode)) { sql_Buff.append(" AC0621.SYSID = ? ") .append(" AND AC0621.KAKINS_TELNO = ? ") .append(" AND AC0621.CHRG_KH = ? ") .append(" AND AC0621.MK_FLG = '0' ") .append(" AND ( ") .append(" ( ") .append(" AC0571.CHRG_TG_USE_MON_SKBT_CD = ? ") // ANK-3228-00-00 DEL START //.append(" AND AC0621.PCALL_STAYMD BETWEEN ? AND ? ") // ANK-3228-00-00 DEL END // ANK-3228-00-00 ADD START .append(" AND AC0621.PCALL_ENDYMD BETWEEN ? AND ? ") // ANK-3228-00-00 ADD END .append(" ) ") .append(" OR ") .append(" ( ") .append(" ( ") .append(" AC0571.CHRG_TG_USE_MON_SKBT_CD = ? OR ") .append(" AC0571.CHRG_TG_USE_MON_SKBT_CD IS NULL ") .append(" ) ") // ANK-3228-00-00 DEL START //.append(" AND AC0621.PCALL_STAYMD BETWEEN ? AND ? ") // ANK-3228-00-00 DEL END // ANK-3228-00-00 ADD START .append(" AND AC0621.PCALL_ENDYMD BETWEEN ? AND ? ") // ANK-3228-00-00 ADD END .append(" ) ") .append(" ) "); replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_SYSID)); // ＫＥＹ＿ＳＹＳＩＤ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_KAKINS_TELNO)); // ＫＥＹ＿課金先電話番号 replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_CHRG_KH)); // ＫＥＹ＿課金可否 replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_USE_MON_SBT_CD_1)); // ＫＥＹ＿利用月種別コード＿１ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_PCALL_STAYMD_FROM_1)); // ＫＥＹ＿通話開始年月日＿ＦＲＯＭ＿１ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_PCALL_STAYMD_TO_1)); // ＫＥＹ＿通話開始年月日＿ＴＯ＿１ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_USE_MON_SBT_CD_2)); // ＫＥＹ＿利用月種別コード＿２ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_PCALL_STAYMD_FROM_2)); // ＫＥＹ＿通話開始年月日＿ＦＲＯＭ＿２ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_PCALL_STAYMD_TO_2)); // ＫＥＹ＿通話開始年月日＿ＴＯ＿２ } else if (JPCModelConstant.FUNC_CD_4.equals(funcCode)) { sql_Buff.append(" AC0621.SYSID = ? ") .append(" AND AC0621.KAKINS_TELNO = ? ") .append(" AND AC0621.CHRG_KH = ? ") .append(" AND AC0621.MK_FLG = '0' ") .append(" AND ( ") .append(" ( ") .append(" AC0571.CHRG_TG_USE_MON_SKBT_CD = ? ") .append(" AND AC0621.PCALL_ENDYMD BETWEEN ? AND ? ") .append(" ) ") .append(" OR ") .append(" ( ") .append(" ( ") .append(" AC0571.CHRG_TG_USE_MON_SKBT_CD = ? OR ") .append(" AC0571.CHRG_TG_USE_MON_SKBT_CD IS NULL ") .append(" ) ") .append(" AND AC0621.PCALL_ENDYMD BETWEEN ? AND ? ") .append(" ) ") .append(" ) "); replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_SYSID)); // ＫＥＹ＿ＳＹＳＩＤ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_KAKINS_TELNO)); // ＫＥＹ＿課金先電話番号 replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_CHRG_KH)); // ＫＥＹ＿課金可否 replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_USE_MON_SBT_CD_1)); // ＫＥＹ＿利用月種別コード＿１ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_PCALL_STAYMD_FROM_1)); // ＫＥＹ＿通話開始年月日＿ＦＲＯＭ＿１ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_PCALL_STAYMD_TO_1)); // ＫＥＹ＿通話開始年月日＿ＴＯ＿１ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_USE_MON_SBT_CD_2)); // ＫＥＹ＿利用月種別コード＿２ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_PCALL_STAYMD_FROM_2)); // ＫＥＹ＿通話開始年月日＿ＦＲＯＭ＿２ replaceListCnt.add(inMsg.getString(EAC0621B010CBSMsg.KEY_PCALL_STAYMD_TO_2)); // ＫＥＹ＿通話開始年月日＿ＴＯ＿２ } return sql_Buff; } }