/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名：eo顧客基幹システム * モジュール名：JSYejbECR0011B020TPDA * ソースファイル名：JSYejbECR0011B020TPDA.java * 作成者：EK908998 * 日付：2011年06月28日 *＜機能概要＞ * SQLFacility * テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン修正日修正者修正内容 * ｖ1.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 eo.ejb.cbs.cbsmsg.ECR0011B020CBSMsg; import eo.ejb.cbs.cbsmsg.ECR0011B020CBSMsg1List; import eo.ejb.cbm.entity.CR0031ETMsg; /** * * テンプレートDBアクセス部品　ECR0011B020_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbECR0011B020TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbECR0011B020TPDA() { } /** * ECR0011B020_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(CR0031ETMsg.getTableName()); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT ") .append(" TUWK.TAIO_KIROK_UCWK_NO ") .append(" , TUWK.TOIAWASE_RSP_DIV ") .append(" , (SELECT ") .append(" CD01.CD_DIV_NM ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI CD01 ") .append(" WHERE ") .append(" CD01.CD_SBT_CD = 'CD00234' ") .append(" AND CD01.CD_DIV = TUWK.TOIAWASE_RSP_DIV ") .append(" AND CD01.CD_TSTAYMD <= SUBSTR(TUWK.UPD_DTM, 1, 8) ") .append(" AND CD01.CD_TENDYMD >= SUBSTR(TUWK.UPD_DTM, 1, 8) ") .append(" AND CD01.MK_FLG = '0') AS TOIAWASE_RSP_DIV_NM ") .append(" , TUWK.DSP_JUN ") .append(" , TUWK.CUST_KOE ") .append(" , TUWK.CSR_KOE ") .append(" , CTGR01.CTGR_NO AS CTGR_NO1 ") .append(" , TUWC01.TAIOKRK_UW_CTGR_LV AS TAIOKRK_UW_CTGR_LV1 ") .append(" , TUWC01.TAIOKRK_UW_CTGR_NM AS TAIOKRK_UW_CTGR_NM1 ") .append(" , CTGR02.CTGR_NO AS CTGR_NO2 ") .append(" , TUWC02.TAIOKRK_UW_CTGR_LV AS TAIOKRK_UW_CTGR_LV2 ") .append(" , TUWC02.TAIOKRK_UW_CTGR_NM AS TAIOKRK_UW_CTGR_NM2 ") .append(" , CTGR03.CTGR_NO AS CTGR_NO3 ") .append(" , TUWC03.TAIOKRK_UW_CTGR_LV AS TAIOKRK_UW_CTGR_LV3 ") .append(" , TUWC03.TAIOKRK_UW_CTGR_NM AS TAIOKRK_UW_CTGR_NM3 ") .append(" , CTGR04.CTGR_NO AS CTGR_NO4 ") .append(" , TUWC04.TAIOKRK_UW_CTGR_LV AS TAIOKRK_UW_CTGR_LV4 ") .append(" , TUWC04.TAIOKRK_UW_CTGR_NM AS TAIOKRK_UW_CTGR_NM4 ") .append(" , CTGR05.CTGR_NO AS CTGR_NO5 ") .append(" , TUWC05.TAIOKRK_UW_CTGR_LV AS TAIOKRK_UW_CTGR_LV5 ") .append(" , TUWC05.TAIOKRK_UW_CTGR_NM AS TAIOKRK_UW_CTGR_NM5 ") .append(" , CTGR06.CTGR_NO AS CTGR_NO6 ") .append(" , TUWC06.TAIOKRK_UW_CTGR_LV AS TAIOKRK_UW_CTGR_LV6 ") .append(" , TUWC06.TAIOKRK_UW_CTGR_NM AS TAIOKRK_UW_CTGR_NM6 ") .append(" , CTGR07.CTGR_NO AS CTGR_NO7 ") .append(" , TUWC07.TAIOKRK_UW_CTGR_LV AS TAIOKRK_UW_CTGR_LV7 ") .append(" , TUWC07.TAIOKRK_UW_CTGR_NM AS TAIOKRK_UW_CTGR_NM7 ") .append(" , CTGR08.CTGR_NO AS CTGR_NO8 ") .append(" , TUWC08.TAIOKRK_UW_CTGR_LV AS TAIOKRK_UW_CTGR_LV8 ") .append(" , TUWC08.TAIOKRK_UW_CTGR_NM AS TAIOKRK_UW_CTGR_NM8 ") .append(" , CTGR09.CTGR_NO AS CTGR_NO9 ") .append(" , TUWC09.TAIOKRK_UW_CTGR_LV AS TAIOKRK_UW_CTGR_LV9 ") .append(" , TUWC09.TAIOKRK_UW_CTGR_NM AS TAIOKRK_UW_CTGR_NM9 ") .append(" , CTGR10.CTGR_NO AS CTGR_NO10 ") .append(" , TUWC10.TAIOKRK_UW_CTGR_LV AS TAIOKRK_UW_CTGR_LV10 ") .append(" , TUWC10.TAIOKRK_UW_CTGR_NM AS TAIOKRK_UW_CTGR_NM10 ") .append(" , CTGR11.CTGR_NO AS CTGR_NO11 ") .append(" , TUWC11.TAIOKRK_UW_CTGR_LV AS TAIOKRK_UW_CTGR_LV11 ") .append(" , TUWC11.TAIOKRK_UW_CTGR_NM AS TAIOKRK_UW_CTGR_NM11 ") .append(" FROM ") .append(" CR_T_TAIOKRK_UCWK TUWK ") .append(" LEFT OUTER JOIN CR_T_TAIOKRK_UW_CTGR TUWC01 ") .append(" ON ( ") .append(" TUWC01.TAIO_KIROK_NO = TUWK.TAIO_KIROK_NO ") .append(" AND TUWC01.TAIO_KIROK_UCWK_NO = TUWK.TAIO_KIROK_UCWK_NO ") .append(" AND TUWC01.TAIOKRK_UW_CTGR_LV = '001' ") .append(" AND TUWC01.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_M_CTGR CTGR01 ") .append(" ON ( ") .append(" CTGR01.CTGR_NM = TUWC01.TAIOKRK_UW_CTGR_NM ") .append(" AND CTGR01.CTGR_LV = TUWC01.TAIOKRK_UW_CTGR_LV ") .append(" AND CTGR01.WKKBT_ORG_CD = ? ") .append(" AND CTGR01.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_T_TAIOKRK_UW_CTGR TUWC02 ") .append(" ON ( ") .append(" TUWC02.TAIO_KIROK_NO = TUWK.TAIO_KIROK_NO ") .append(" AND TUWC02.TAIO_KIROK_UCWK_NO = TUWK.TAIO_KIROK_UCWK_NO ") .append(" AND TUWC02.TAIOKRK_UW_CTGR_LV = '002' ") .append(" AND TUWC02.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_M_CTGR CTGR02 ") .append(" ON ( ") .append(" CTGR02.CTGR_NM = TUWC02.TAIOKRK_UW_CTGR_NM ") .append(" AND CTGR02.CTGR_LV = TUWC02.TAIOKRK_UW_CTGR_LV ") .append(" AND CTGR02.JOI_CTGR_NO = CTGR01.CTGR_NO ") .append(" AND CTGR02.WKKBT_ORG_CD = ? ") .append(" AND CTGR02.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_T_TAIOKRK_UW_CTGR TUWC03 ") .append(" ON ( ") .append(" TUWC03.TAIO_KIROK_NO = TUWK.TAIO_KIROK_NO ") .append(" AND TUWC03.TAIO_KIROK_UCWK_NO = TUWK.TAIO_KIROK_UCWK_NO ") .append(" AND TUWC03.TAIOKRK_UW_CTGR_LV = '003' ") .append(" AND TUWC03.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_M_CTGR CTGR03 ") .append(" ON ( ") .append(" CTGR03.CTGR_NM = TUWC03.TAIOKRK_UW_CTGR_NM ") .append(" AND CTGR03.CTGR_LV = TUWC03.TAIOKRK_UW_CTGR_LV ") .append(" AND CTGR03.JOI_CTGR_NO = CTGR02.CTGR_NO ") .append(" AND CTGR03.WKKBT_ORG_CD = ? ") .append(" AND CTGR03.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_T_TAIOKRK_UW_CTGR TUWC04 ") .append(" ON ( ") .append(" TUWC04.TAIO_KIROK_NO = TUWK.TAIO_KIROK_NO ") .append(" AND TUWC04.TAIO_KIROK_UCWK_NO = TUWK.TAIO_KIROK_UCWK_NO ") .append(" AND TUWC04.TAIOKRK_UW_CTGR_LV = '004' ") .append(" AND TUWC04.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_M_CTGR CTGR04 ") .append(" ON ( ") .append(" CTGR04.CTGR_NM = TUWC04.TAIOKRK_UW_CTGR_NM ") .append(" AND CTGR04.CTGR_LV = TUWC04.TAIOKRK_UW_CTGR_LV ") .append(" AND CTGR04.JOI_CTGR_NO = CTGR03.CTGR_NO ") .append(" AND CTGR04.WKKBT_ORG_CD = ? ") .append(" AND CTGR04.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_T_TAIOKRK_UW_CTGR TUWC05 ") .append(" ON ( ") .append(" TUWC05.TAIO_KIROK_NO = TUWK.TAIO_KIROK_NO ") .append(" AND TUWC05.TAIO_KIROK_UCWK_NO = TUWK.TAIO_KIROK_UCWK_NO ") .append(" AND TUWC05.TAIOKRK_UW_CTGR_LV = '005' ") .append(" AND TUWC05.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_M_CTGR CTGR05 ") .append(" ON ( ") .append(" CTGR05.CTGR_NM = TUWC05.TAIOKRK_UW_CTGR_NM ") .append(" AND CTGR05.CTGR_LV = TUWC05.TAIOKRK_UW_CTGR_LV ") .append(" AND CTGR05.JOI_CTGR_NO = CTGR04.CTGR_NO ") .append(" AND CTGR05.WKKBT_ORG_CD = ? ") .append(" AND CTGR05.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_T_TAIOKRK_UW_CTGR TUWC06 ") .append(" ON ( ") .append(" TUWC06.TAIO_KIROK_NO = TUWK.TAIO_KIROK_NO ") .append(" AND TUWC06.TAIO_KIROK_UCWK_NO = TUWK.TAIO_KIROK_UCWK_NO ") .append(" AND TUWC06.TAIOKRK_UW_CTGR_LV = '006' ") .append(" AND TUWC06.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_M_CTGR CTGR06 ") .append(" ON ( ") .append(" CTGR06.CTGR_NM = TUWC06.TAIOKRK_UW_CTGR_NM ") .append(" AND CTGR06.CTGR_LV = TUWC06.TAIOKRK_UW_CTGR_LV ") .append(" AND CTGR06.JOI_CTGR_NO = CTGR05.CTGR_NO ") .append(" AND CTGR06.WKKBT_ORG_CD = ? ") .append(" AND CTGR06.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_T_TAIOKRK_UW_CTGR TUWC07 ") .append(" ON ( ") .append(" TUWC07.TAIO_KIROK_NO = TUWK.TAIO_KIROK_NO ") .append(" AND TUWC07.TAIO_KIROK_UCWK_NO = TUWK.TAIO_KIROK_UCWK_NO ") .append(" AND TUWC07.TAIOKRK_UW_CTGR_LV = '007' ") .append(" AND TUWC07.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_M_CTGR CTGR07 ") .append(" ON ( ") .append(" CTGR07.CTGR_NM = TUWC07.TAIOKRK_UW_CTGR_NM ") .append(" AND CTGR07.CTGR_LV = TUWC07.TAIOKRK_UW_CTGR_LV ") .append(" AND CTGR07.JOI_CTGR_NO = CTGR06.CTGR_NO ") .append(" AND CTGR07.WKKBT_ORG_CD = ? ") .append(" AND CTGR07.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_T_TAIOKRK_UW_CTGR TUWC08 ") .append(" ON ( ") .append(" TUWC08.TAIO_KIROK_NO = TUWK.TAIO_KIROK_NO ") .append(" AND TUWC08.TAIO_KIROK_UCWK_NO = TUWK.TAIO_KIROK_UCWK_NO ") .append(" AND TUWC08.TAIOKRK_UW_CTGR_LV = '008' ") .append(" AND TUWC08.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_M_CTGR CTGR08 ") .append(" ON ( ") .append(" CTGR08.CTGR_NM = TUWC08.TAIOKRK_UW_CTGR_NM ") .append(" AND CTGR08.CTGR_LV = TUWC08.TAIOKRK_UW_CTGR_LV ") .append(" AND CTGR08.JOI_CTGR_NO = CTGR07.CTGR_NO ") .append(" AND CTGR08.WKKBT_ORG_CD = ? ") .append(" AND CTGR08.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_T_TAIOKRK_UW_CTGR TUWC09 ") .append(" ON ( ") .append(" TUWC09.TAIO_KIROK_NO = TUWK.TAIO_KIROK_NO ") .append(" AND TUWC09.TAIO_KIROK_UCWK_NO = TUWK.TAIO_KIROK_UCWK_NO ") .append(" AND TUWC09.TAIOKRK_UW_CTGR_LV = '009' ") .append(" AND TUWC09.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_M_CTGR CTGR09 ") .append(" ON ( ") .append(" CTGR09.CTGR_NM = TUWC09.TAIOKRK_UW_CTGR_NM ") .append(" AND CTGR09.CTGR_LV = TUWC09.TAIOKRK_UW_CTGR_LV ") .append(" AND CTGR09.JOI_CTGR_NO = CTGR08.CTGR_NO ") .append(" AND CTGR09.WKKBT_ORG_CD = ? ") .append(" AND CTGR09.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_T_TAIOKRK_UW_CTGR TUWC10 ") .append(" ON ( ") .append(" TUWC10.TAIO_KIROK_NO = TUWK.TAIO_KIROK_NO ") .append(" AND TUWC10.TAIO_KIROK_UCWK_NO = TUWK.TAIO_KIROK_UCWK_NO ") .append(" AND TUWC10.TAIOKRK_UW_CTGR_LV = '010' ") .append(" AND TUWC10.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_M_CTGR CTGR10 ") .append(" ON ( ") .append(" CTGR10.CTGR_NM = TUWC10.TAIOKRK_UW_CTGR_NM ") .append(" AND CTGR10.CTGR_LV = TUWC10.TAIOKRK_UW_CTGR_LV ") .append(" AND CTGR10.JOI_CTGR_NO = CTGR09.CTGR_NO ") .append(" AND CTGR10.WKKBT_ORG_CD = ? ") .append(" AND CTGR10.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_T_TAIOKRK_UW_CTGR TUWC11 ") .append(" ON ( ") .append(" TUWC11.TAIO_KIROK_NO = TUWK.TAIO_KIROK_NO ") .append(" AND TUWC11.TAIO_KIROK_UCWK_NO = TUWK.TAIO_KIROK_UCWK_NO ") .append(" AND TUWC11.TAIOKRK_UW_CTGR_LV = '011' ") .append(" AND TUWC11.MK_FLG = '0' ") .append(" ) ") .append(" LEFT OUTER JOIN CR_M_CTGR CTGR11 ") .append(" ON ( ") .append(" CTGR11.CTGR_NM = TUWC11.TAIOKRK_UW_CTGR_NM ") .append(" AND CTGR11.CTGR_LV = TUWC11.TAIOKRK_UW_CTGR_LV ") .append(" AND CTGR11.JOI_CTGR_NO = CTGR10.CTGR_NO ") .append(" AND CTGR11.WKKBT_ORG_CD = ? ") .append(" AND CTGR11.MK_FLG = '0' ") .append(" ) "); // SQL文_個別部1 if ( "1".equals( inMsg.getObject(ECR0011B020CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" WHERE ") .append(" TUWK.TAIO_KIROK_NO = ? ") .append(" AND TUWK.MK_FLG = '0' ") .append(" ORDER BY ") .append(" TUWK.DSP_JUN "); } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに指定された値を設定 if ( "1".equals( inMsg.getString(ECR0011B020CBSMsg.FUNC_CODE) ) ) { // ▼▼▼▼▼ 変更開始 ▼▼▼▼▼ for (int i = 1; i < 12; i++) { // パラメータの設定(ＫＥＹ＿業務個別設定組織コードを指定) CAANJDBCUtil.setParam(pstmt, i, inMsg.getObject(ECR0011B020CBSMsg.KEY_WKKBT_ORG_CD)); } // パラメータの設定(ＫＥＹ＿対応記録番号を指定) CAANJDBCUtil.setParam(pstmt, 12, inMsg.getObject(ECR0011B020CBSMsg.KEY_TAIO_KIROK_NO)); // ▲▲▲▲▲ 変更終了 ▲▲▲▲▲ } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // ECR0011B020CBSMsgの明細にセットする項目 String[] msgKeyList1 = { ECR0011B020CBSMsg1List.TAIO_KIROK_UCWK_NO , ECR0011B020CBSMsg1List.TOIAWASE_RSP_DIV , ECR0011B020CBSMsg1List.TOIAWASE_RSP_DIV_NM , ECR0011B020CBSMsg1List.DSP_JUN , ECR0011B020CBSMsg1List.CUST_KOE , ECR0011B020CBSMsg1List.CSR_KOE , ECR0011B020CBSMsg1List.CTGR_NO1 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_LV1 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_NM1 , ECR0011B020CBSMsg1List.CTGR_NO2 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_LV2 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_NM2 , ECR0011B020CBSMsg1List.CTGR_NO3 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_LV3 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_NM3 , ECR0011B020CBSMsg1List.CTGR_NO4 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_LV4 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_NM4 , ECR0011B020CBSMsg1List.CTGR_NO5 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_LV5 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_NM5 , ECR0011B020CBSMsg1List.CTGR_NO6 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_LV6 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_NM6 , ECR0011B020CBSMsg1List.CTGR_NO7 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_LV7 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_NM7 , ECR0011B020CBSMsg1List.CTGR_NO8 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_LV8 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_NM8 , ECR0011B020CBSMsg1List.CTGR_NO9 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_LV9 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_NM9 , ECR0011B020CBSMsg1List.CTGR_NO10 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_LV10 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_NM10 , ECR0011B020CBSMsg1List.CTGR_NO11 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_LV11 , ECR0011B020CBSMsg1List.TAIOKRK_UW_CTGR_NM11 }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.ECR0011B020CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("ECR0011B020CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(ECR0011B020CBSMsg.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(ECR0011B020CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }