/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名 ：eo顧客基幹システム * モジュール名 ：JSYejbEDK0171B040TPDA * ソースファイル名：JSYejbEDK0171B040TPDA.java * 作成者 ：EK909134 * 日付 ：2012年01月19日 *＜機能概要＞ * 依頼者引渡一覧照会テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン 修正日 修正者 修正内容 * ｖ1.00.00 * ｖ7.00.00 2013/11/19 FJ）塚田 ANK-1578-00-00(多機能ルータ対応) * ｖ7.00.00 2014/01/24 FJ）鄭 ST-2014-0000030対応 * ｖ9.00.00 2014/07/30 FJ）窪田 OM-2014-0002584対応 * ｖ28.00.00 2017/02/27 FJ）中山 【OM-2017-0000152】修理受付一覧画面の依頼者引渡ボタンの性能障害対応 ********************************************************************************/ 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.common.constant.JPCModelConstant; import eo.ejb.cbs.cbsmsg.EDK0171B040CBSMsg; import eo.ejb.cbs.cbsmsg.EDK0171B040List; import eo.ejb.cbm.entity.DK0171ETMsg; import eo.ejb.common.JDKModelCommon.COMMA; import static eo.ejb.common.JDKModelCommon.*; /** * * テンプレートDBアクセス部品　EDK0171B040_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEDK0171B040TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbEDK0171B040TPDA() { } /** * EDK0171B040_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(DK0171ETMsg.getTableName()); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); //OM-2017-0000152 MOD START // sql_Buff.append(" SELECT ") sql_Buff.append(" SELECT /*+ INDEX(KK0241 KK_T_KAISEN_TG_SVKEI_IDX_03) INDEX(KK0251 KK_T_SVKEI_KAISEN_UW_PK_01 ) */ ") //OM-2017-0000152 MOD END .append(" DK0171_OUTER.SHURI_KIKI_SOHU_YMD ") .append(" , DK0171_OUTER.UPD_DTM ") .append(" , DK0171_OUTER.BTRYHOZN_TAIO_CD ") .append(" , DK0171_OUTER.BTRYHOZN_BIKO ") .append(" , DK0171_OUTER.TAIOKRK_UPD_DTM ") .append(" , DK0171_OUTER.SOKO_CD ") .append(" , DK0171_OUTER.SOKO_NM ") .append(" , DK0171_OUTER.KKTK_SVC_KEI_NO ") .append(" , DK0171_OUTER.KIKI_CHG_NO ") .append(" , DK0171_OUTER.TAKNKIKI_SBT_CD ") .append(" , DK0171_OUTER.HUZOKUHIN_SBT_CD ") .append(" , DK0171_OUTER.BTRYHOZN_UK_DTM ") .append(" , DK0171_OUTER.RENEWAL_AF_TAKNKIKI_MDL_CD ") .append(" , DK0171_OUTER.RENEWAL_AF_HZKHIN_MDL_CD ") .append(" , DK0171_OUTER.RENEWAL_AF_KIKI_SEIZO_NO ") .append(" , DK0171_OUTER.PLACE_NO ") .append(" , DK0171_OUTER.KKTK_SVKEI_IDO_CD ") .append(" , DK0171_OUTER.KKTK_SVKEI_IDO_DTM ") .append(" , DK0171_OUTER.SVC_KEI_NO ") .append(" , DK0171_OUTER.KKTK_SBT_CD ") .append(" , DK0171_OUTER.IDOMOTO_KRIPLACE_SKCD ") .append(" , DK0171_OUTER.KIKI_STC_SAKI_PLACE_NO ") .append(" , DK0171_OUTER.MSKM_DTL_NO ") .append(" , KK0251.KAISEN_PLACE_AD_CD") .append(" , KK0251.KAISEN_PLACE_PCD") .append(" , KK0251.KAISEN_PLACE_STATE_NM") .append(" , KK0251.KAISEN_PLACE_CITY_NM") .append(" , KK0251.KAISEN_PLACE_OAZTSU_NM") .append(" , KK0251.KAISEN_PLACE_AZCHO_NM") .append(" , KK0251.KAISEN_PLACE_BNCHIGO") .append(" , KK0251.KAISEN_PLACE_ADRTTM") .append(" , KK0251.KAISEN_PLACE_ADRRM") .append(" , KK0251.KAISEN_PLACE_TELNO") .append(" FROM ( ") .append(" SELECT ") .append(" DK0171.SHURI_KIKI_SOHU_YMD AS SHURI_KIKI_SOHU_YMD, ") .append(" DK0171.UPD_DTM AS UPD_DTM, ") .append(" DK0351.BTRYHOZN_TAIO_CD AS BTRYHOZN_TAIO_CD, ") .append(" DK0351.BTRYHOZN_BIKO AS BTRYHOZN_BIKO, ") .append(" DK0351.UPD_DTM AS TAIOKRK_UPD_DTM, ") .append(" CASE DK0011.IDOMOTO_KRIPLACE_SKCD ") .append(" WHEN '1' THEN DK0011.HAISO_MOTO_SOKO_CD ") .append(" WHEN '2' THEN DK0011.IDOMOTO_KOCOMP_CD ") .append(" WHEN '3' THEN DK0011.IDOMOTO_YTKSKOF_CD ") .append(" END AS SOKO_CD, ") .append(" CASE DK0011.IDOMOTO_KRIPLACE_SKCD ") .append(" WHEN '1' THEN DK0091.SOKO_NM ") .append(" WHEN '2' THEN ZM0381.KOJI_COMP_NM ") .append(" WHEN '3' THEN ZM0391.YOTAKU_SAKI_OFFC_NM ") .append(" END AS SOKO_NM, ") .append(" DK0171.KKTK_SVC_KEI_NO AS KKTK_SVC_KEI_NO, ") .append(" DK0171.KIKI_CHG_NO AS KIKI_CHG_NO, ") .append(" KK0341.TAKNKIKI_SBT_CD AS TAKNKIKI_SBT_CD, ") .append(" '' AS HUZOKUHIN_SBT_CD, ") .append(" DK0171.BTRYHOZN_UK_DTM AS BTRYHOZN_UK_DTM, ") .append(" DK0141.RENEWAL_AF_TAKNKIKI_MDL_CD AS RENEWAL_AF_TAKNKIKI_MDL_CD, ") .append(" DK0141.RENEWAL_AF_HZKHIN_MDL_CD AS RENEWAL_AF_HZKHIN_MDL_CD, ") .append(" DK0141.RENEWAL_AF_KIKI_SEIZO_NO AS RENEWAL_AF_KIKI_SEIZO_NO, ") .append(" KK0341.KIKI_STC_SAKI_PLACE_NO AS PLACE_NO, ") .append(" DK0171.KKTK_SVKEI_IDO_CD AS KKTK_SVKEI_IDO_CD, ") .append(" DECODE(DK0171.KKTK_SVKEI_IDO_DTM, null, DK0171.KKTK_SVKEI_IDO_DTM, DK0171.KKTK_SVKEI_IDO_DTM || '000') AS KKTK_SVKEI_IDO_DTM, ") .append( svcKeiNoFor2("KK0341", "SVC_KEI_NO", "SUBSTR(DK0171.BTRYHOZN_UK_DTM,1,8)", COMMA.RIGHT, "SUBSTR(DK0171.BTRYHOZN_UK_DTM,1,8)")) .append(" KK0341.KKTK_SBT_CD, ") .append(" DK0011.IDOMOTO_KRIPLACE_SKCD, ") .append(" KK0341.KIKI_STC_SAKI_PLACE_NO, ") .append(" KK0341.MSKM_DTL_NO") .append(" FROM ") .append(" DK_T_BTRYHOZN DK0171 ") .append(" LEFT OUTER JOIN DK_T_BTRYHOZN_TIOKRK DK0351 ") .append(" ON DK0351.BTRYHOZN_NO = DK0171.BTRYHOZN_NO ") .append(" AND DK0351.BTRYHOZN_TAIO_CD = '08' ") .append(" AND DK0351.MK_FLG = '0' ") .append(" LEFT OUTER JOIN DK_T_MKHMPIN DK0131 ") .append(" ON DK0131.BTRYHOZN_NO = DK0171.BTRYHOZN_NO ") .append(" AND DK0131.MK_FLG = '0' ") .append(" LEFT OUTER JOIN DK_T_MKHMPIN_TG_KIKI DK0141 ") .append(" ON DK0141.MKRHMPIN_NO = DK0131.MKRHMPIN_NO ") .append(" AND DK0141.MKRHMPIN_TAIKIKI_STAT = '002' ") .append(" LEFT OUTER JOIN DK_T_HAISO DK0011 ") .append(" ON DK0011.BTRYHOZN_NO = DK0171.BTRYHOZN_NO ") .append(" AND DK0011.MK_FLG = '0' ") .append(" LEFT OUTER JOIN DK_M_SOKO DK0091 ") .append(" ON DK0091.SOKO_CD = DK0011.HAISO_MOTO_SOKO_CD ") .append(" AND DK0091.MK_FLG = '0' ") .append(" LEFT OUTER JOIN ZM_M_KOJI_COMP ZM0381 ") .append(" ON ZM0381.KOJI_COMP_CD = DK0011.IDOMOTO_KOCOMP_CD ") .append(" AND ZM0381.MK_FLG = '0' ") .append(" LEFT OUTER JOIN ZM_M_YTK_SK_OFFC ZM0391 ") .append(" ON ZM0391.YOTAKU_SAKI_OFFC_CD = DK0011.IDOMOTO_YTKSKOF_CD ") .append(" AND ZM0391.MK_FLG = '0' ") .append(" INNER JOIN KK_T_KKTK_SVC_KEI KK0341 ") .append(" ON KK0341.KKTK_SVC_KEI_NO = DK0171.KKTK_SVC_KEI_NO ") .append(" AND KK0341.KIKI_CHG_NO = DK0171.KIKI_CHG_NO ") .append(" AND KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM = ") .append(" (SELECT MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) AS KK0341_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_GENE ") .append(" WHERE KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO ") .append(" AND KK0341_GENE.KIKI_CHG_NO = KK0341.KIKI_CHG_NO ") // OM-2014-0002584 DEL START //.append(" AND KK0341_GENE.RSV_APLY_YMD <= SUBSTR(DK0171.BTRYHOZN_UK_DTM, 0, 8) ") // OM-2014-0002584 DEL END .append(" AND KK0341_GENE.MK_FLG = '0') "); // SQL文_個別部1 if ( JPCModelConstant.FUNC_CD_1.equals( inMsg.getObject(EDK0171B040CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" WHERE DK0171.BTRYHOZN_NO = ? ") .append(" AND DK0171.MK_FLG = '0' ") .append(" ) DK0171_OUTER ") .append(" LEFT OUTER JOIN KK_T_KAISEN_TG_SVKEI KK0241 ") .append(" ON KK0241.SVC_KEI_NO = DK0171_OUTER.SVC_KEI_NO ") .append(" AND ((KK0241.KAISEN_UCWK_USE_STAYMD <> KK0241.KAISEN_UCWK_USE_ENDYMD") .append(" AND ? BETWEEN KK0241.KAISEN_UCWK_USE_STAYMD AND KK0241.KAISEN_UCWK_USE_ENDYMD)") .append(" OR (KK0241.KAISEN_UCWK_USE_STAYMD = '20991231' AND KK0241.KAISEN_UCWK_USE_ENDYMD = '20991231'))") .append(" AND KK0241.MK_FLG = '0' ") .append(" LEFT OUTER JOIN KK_T_SVKEI_KAISEN_UW KK0251 ") .append(" ON KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO ") .append(" AND KK0241.MK_FLG = '0' ") .append(" WHERE KK0251.GENE_ADD_DTM = (SELECT MAX(GENE_ADD_DTM) ") .append(" FROM KK_T_SVKEI_KAISEN_UW ") .append(" WHERE SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO ") .append(" AND KK0241.MK_FLG = '0' ) "); } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに 指定された値を設定 if ( JPCModelConstant.FUNC_CD_1.equals( inMsg.getString(EDK0171B040CBSMsg.FUNC_CODE) ) ) { // パラメータの設定(ＫＥＹ＿物流保全番号を指定) CAANJDBCUtil.setParam(pstmt,1, inMsg.getObject(EDK0171B040CBSMsg.KEY_BTRYHOZN_NO)); CAANJDBCUtil.setParam(pstmt,2, inMsg.getObject(EDK0171B040CBSMsg.KEY_UNYO_YMD)); } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EDK0171B040CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EDK0171B040List.SHURI_KIKI_SOHU_YMD , EDK0171B040List.UPD_DTM , EDK0171B040List.BTRYHOZN_TAIO_CD , EDK0171B040List.BTRYHOZN_BIKO , EDK0171B040List.TAIOKRK_UPD_DTM , EDK0171B040List.SOKO_CD , EDK0171B040List.SOKO_NM , EDK0171B040List.KKTK_SVC_KEI_NO , EDK0171B040List.KIKI_CHG_NO , EDK0171B040List.TAKNKIKI_SBT_CD , EDK0171B040List.HUZOKUHIN_SBT_CD , EDK0171B040List.BTRYHOZN_UK_DTM , EDK0171B040List.RENEWAL_AF_TAKNKIKI_MDL_CD , EDK0171B040List.RENEWAL_AF_HZKHIN_MDL_CD , EDK0171B040List.RENEWAL_AF_KIKI_SEIZO_NO , EDK0171B040List.PLACE_NO , EDK0171B040List.KKTK_SVKEI_IDO_CD , EDK0171B040List.KKTK_SVKEI_IDO_DTM , EDK0171B040List.SVC_KEI_NO , EDK0171B040List.KKTK_SBT_CD , EDK0171B040List.IDOMOTO_KRIPLACE_SKCD , EDK0171B040List.KIKI_STC_SAKI_PLACE_NO , EDK0171B040List.MSKM_DTL_NO , EDK0171B040List.HISOS_AD_CD , EDK0171B040List.HISOS_PCD , EDK0171B040List.HISOS_STATE_NM , EDK0171B040List.HISOS_CITY_NM , EDK0171B040List.HISOS_OAZTSU_NM , EDK0171B040List.HISOS_AZCHO_NM , EDK0171B040List.HISOS_BNCHIGO , EDK0171B040List.HISOS_ADRTTM , EDK0171B040List.HISOS_ADRRM , EDK0171B040List.HISOS_TELNO }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EDK0171B040List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("EDK0171B040List", outMsg1); } catch(SQLException e) { inMsg.set(EDK0171B040CBSMsg.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(EDK0171B040CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }