/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名 ：eo顧客基幹システム * モジュール名 ：JSYejbECH0011B016TPDA * ソースファイル名：JSYejbECH0011B016TPDA.java * 作成者 ：EK946241 * 日付 ：2018年03月26日 *＜機能概要＞ * 債権一覧照会(対応履歴)テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン 修正日 修正者 修正内容 * ｖ1.00.00 * ********************************************************************************/ 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.JACStrConst; import eo.ejb.cbm.entity.CH0011ETMsg; import eo.ejb.cbm.entity.ZM0321ETMsg; import eo.ejb.cbm.entity.ZM0321LE; import eo.ejb.cbs.cbsmsg.ECH0011B016CBSMsg; import eo.ejb.cbs.cbsmsg.ECH0011B016CBSMsg1List; import eo.ejb.common.JPCModelCommon; /** * * テンプレートDBアクセス部品　ECH0011B016_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbECH0011B016TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbECH0011B016TPDA() { } /** * ECH0011B016_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void */ public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){ // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt = null; // リザルトセット ResultSet rsltQuery = null; // パラメータ設定リスト List replaceListCnt = new ArrayList(); /******************** * SQL文の作成 ********************/ try{ String funcCode = inMsg.getString(ECH0011B016CBSMsg.FUNC_CODE); String seikyKeiNo = inMsg.getString(ECH0011B016CBSMsg.KEY_SEIKY_KEI_NO); // 業務機能識別コード取得 String workParam[] = getWorkParam(); // 運用日付 String opeDate = (String)JPCModelCommon.getOpeDate(inMsg, inContext, null); // コネクション取得 con1 = JSYejbConnection.getConnection(CH0011ETMsg.getTableName()); // -------------------------------------------------- // // データ取得 // -------------------------------------------------- // // SQL文_基本部1 StringBuilder sql_Buff = new StringBuilder(); sql_Buff.append("SELECT ") .append(" CH0011.SAIKEN_YM AS SAIKEN_FIX_YM , ") .append(" (SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00158' ") .append(" AND ZM0171.CD_DIV = CH0011.SAIKEN_STAT ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD AND ZM0171.MK_FLG = '0') ") // ?=運用日 .append(" AS SAIKEN_STAT_NM , ") .append(" (SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00166' ") .append(" AND ZM0171.CD_DIV = CH0011.SEIKY_WAY_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD AND ZM0171.MK_FLG = '0') ")// ?=運用日 .append(" AS SEIKY_WAY_CD_NM , ") .append(" (SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00164' ") .append(" AND ZM0171.CD_DIV = CH0011.SEIKY_SBT_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD AND ZM0171.MK_FLG = '0') ") // ?=運用日 .append(" AS SEIKY_SBT_CD_NM , ") .append(" CH0011.JUTO_FIN_YMD , ") .append(" (SELECT CH0111.RECEIPT_YMD ") .append(" FROM (SELECT CH0201_1.SAIKEN_NO , ") .append(" MAX(CH0111.RECEIPT_YMD) AS RECEIPT_YMD ") .append(" FROM CH_T_JUTO CH0201_1 ") .append(" INNER JOIN CH_T_NYUKIN CH0111 ") .append(" ON CH0201_1.NYUKIN_NO = CH0111.NYUKIN_NO ") .append(" AND CH0111.MK_FLG = '0' ") .append(" WHERE CH0201_1.JUTO_NO IN (SELECT CH0201_2.JUTO_NO ") .append(" FROM CH_T_JUTO CH0201_2 ") .append(" INNER JOIN CH_T_SAIKEN CH0011 ") .append(" ON CH0011.SAIKEN_NO = CH0201_2.SAIKEN_NO ") .append(" AND CH0011.SEIKY_KEI_NO = ? ") // ?=請求契約番号 .append(" WHERE CH0201_2.MK_FLG = '0' ") .append(" AND CH0201_2.CL_YMD IS NULL) ") .append(" GROUP BY CH0201_1.SAIKEN_NO) CH0111 ") .append(" WHERE CH0011.SAIKEN_NO = CH0111.SAIKEN_NO) RECEIPT_YMD , ") .append(" (SELECT CH0021.AMNT ") .append(" FROM CH_T_SAIKEN_UCWK CH0021 ") .append(" INNER JOIN (SELECT CH0401.PRC_GRP_CD, ") .append(" CH0401.PCRS_CD, ") .append(" CH0401.PRC_SVC_CD, ") .append(" CH0401.PRC_KMK_CD ") .append(" FROM CH_M_PRC_KMK_CS_CHGE CH0401 ") .append(" WHERE CH0401.SYS_CD = 'CH' ") .append(" AND CH0401.WORK_KINO_SKBT_CD = ? ") // ?=債権額: SAIK_AMNT(00900910601) .append(" AND CH0401.CHSHT_CHG_CD = '1' ") .append(" AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD ")// ?=運用日 .append(" AND CH0401.MK_FLG = '0') CH0401 ") .append(" ON CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD ") .append(" AND CH0401.PCRS_CD = CH0021.PCRS_CD ") .append(" AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD ") .append(" AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD ") .append(" WHERE CH0021.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM ") .append(" AND CH0021.MK_FLG = '0' ") .append(" AND ROWNUM = 1) SAIKEN_AMNT , ") .append(" (SELECT CH0021.AMNT ") .append(" FROM CH_T_SAIKEN_UCWK CH0021 ") .append(" INNER JOIN (SELECT CH0401.PRC_GRP_CD, ") .append(" CH0401.PCRS_CD, ") .append(" CH0401.PRC_SVC_CD, ") .append(" CH0401.PRC_KMK_CD ") .append(" FROM CH_M_PRC_KMK_CS_CHGE CH0401 ") .append(" WHERE CH0401.SYS_CD = 'CH' ") .append(" AND CH0401.WORK_KINO_SKBT_CD = ? ") // ?=充当額＿合計: JUTO_GAKU(00900960601) .append(" AND CH0401.CHSHT_CHG_CD = '1' ") .append(" AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD ") // ?=運用日 .append(" AND CH0401.MK_FLG = '0') CH0401 ") .append(" ON CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD ") .append(" AND CH0401.PCRS_CD = CH0021.PCRS_CD ") .append(" AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD ") .append(" AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD ") .append(" WHERE CH0021.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM ") .append(" AND CH0021.MK_FLG = '0' ") .append(" AND ROWNUM = 1) JYTO_AMNT , ") .append(" CH0041.SEIKY_NO ") .append("FROM CH_T_SAIKEN CH0011 ") .append("INNER JOIN (SELECT CH0041.SEIKY_NO , ") .append(" CH0041.SAIKEN_NO , ") .append(" CH0051.KHRI_RSLT_CD , ") .append(" CH0051.SEIKY_YM ") .append(" FROM CH_T_SEIKY_SKN_KNRN CH0041 ") .append(" INNER JOIN CH_T_SEIKY CH0051 ") .append(" ON CH0051.SEIKY_NO = CH0041.SEIKY_NO ") .append(" WHERE 1 = 1 ") .append(" AND CH0051.SEIKY_KEI_NO = ? ") // ?=請求契約番号 .append(" AND CH0051.MK_FLG = '0' ") .append(" AND CH0041.KKSHI_CNT = 0 ") .append(" AND CH0041.MK_FLG = '0' ") .append(" AND CH0051.SEIKY_SBT_CD <> '3') CH0041 ") .append("ON CH0041.SAIKEN_NO = CH0011.SAIKEN_NO ") .append("AND CH0041.SEIKY_YM = CH0011.SAIKEN_YM ") .append("WHERE CH0011.SEIKY_KEI_NO = ? ") // ?=請求契約番号 .append("AND CH0011.SAIKEN_STAT IN ('020', '030', '040', '050', '060', '080', '090') ") .append("AND CH0011.MK_FLG = '0' ") .append("ORDER BY SAIKEN_FIX_YM DESC , CH0011.SEIKY_SBT_CD ASC "); replaceListCnt.add(opeDate); replaceListCnt.add(opeDate); replaceListCnt.add(opeDate); replaceListCnt.add(seikyKeiNo); replaceListCnt.add(workParam[0]); // 債権額 : SAIK_AMNT(00900910601) replaceListCnt.add(opeDate); replaceListCnt.add(workParam[2]); // 充当額＿合計 : JUTO_GAKU(00900960601) replaceListCnt.add(opeDate); replaceListCnt.add(seikyKeiNo); replaceListCnt.add(seikyKeiNo); // prepareStatementにSQL文をセット pstmt = 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(pstmt, idx+1, replaceListCnt.get(idx)); } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // ECH0011B016CBSMsgの明細にセットする項目 String[] msgKeyList1 = { ECH0011B016CBSMsg1List.SAIKEN_FIX_YM , ECH0011B016CBSMsg1List.SAIKEN_STAT_NM , ECH0011B016CBSMsg1List.SEIKY_WAY_CD_NM , ECH0011B016CBSMsg1List.SEIKY_SBT_CD_NM , ECH0011B016CBSMsg1List.JUTO_FIN_YMD , ECH0011B016CBSMsg1List.RECEIPT_YMD , ECH0011B016CBSMsg1List.SAIKEN_AMNT , ECH0011B016CBSMsg1List.JYTO_AMNT , ECH0011B016CBSMsg1List.SEIKY_NO }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.ECH0011B016CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("ECH0011B016CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(ECH0011B016CBSMsg.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(ECH0011B016CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } /** * ECH0011B016_SQLF
* @return String[] */ private String[] getWorkParam(){ String[] workParam = new String[4]; // 業務パラメータ管理より業務機能識別コード：料金項目コード（債権額）を取得する CAANMsg msgZM0321 = new CAANMsg(ZM0321ETMsg.class.getName()); ZM0321LE le = new ZM0321LE(); msgZM0321.set(ZM0321ETMsg.WORK_PARAM_ID, JACStrConst.WKPRA_CH_WKS_SAIKEN_AMNT); // SAIK_AMNT(00900910601) msgZM0321.set(ZM0321ETMsg.MK_FLG, JACStrConst.MK_FLG_YK); CAANMsg retZM0321 = le.findByPrimaryKey(msgZM0321); workParam[0] = retZM0321.getString(ZM0321ETMsg.WORK_PARAM_SETTE_VALUE); // 業務パラメータ管理より業務機能識別コード：料金項目コード（未収金額））取得する msgZM0321.set(ZM0321ETMsg.WORK_PARAM_ID, JACStrConst.WKPARA_CH_WKS_MISYU_KINGAKU); // MISH_AMNT(00900950601) retZM0321 = le.findByPrimaryKey(msgZM0321); workParam[1] = retZM0321.getString(ZM0321ETMsg.WORK_PARAM_SETTE_VALUE); // 業務パラメータ管理より業務機能識別コード：料金項目コード（充当額＿合計）を取得する msgZM0321.set(ZM0321ETMsg.WORK_PARAM_ID, JACStrConst.WKPRA_CH_WKS_JUTO_GOKEI); // JUTO_GAKU(00900960601) retZM0321 = le.findByPrimaryKey(msgZM0321); workParam[2] = retZM0321.getString(ZM0321ETMsg.WORK_PARAM_SETTE_VALUE); // 業務パラメータ管理より業務機能識別コード：料金項目コード（変更前債権額）を取得する msgZM0321.set(ZM0321ETMsg.WORK_PARAM_ID, JACStrConst.WKPRA_CH_WKS_CHGE_SAIKEN); // CHGE_SAIK(00900910630) retZM0321 = le.findByPrimaryKey(msgZM0321); workParam[3] = retZM0321.getString(ZM0321ETMsg.WORK_PARAM_SETTE_VALUE); return workParam; } }