/*******************************************************************************
* All Rights reserved,Copyright (c) K-Opticom
********************************************************************************
*＜プログラム内容＞
* システム名 ：eo顧客基幹システム
* モジュール名 ：JSYejbEDK0011A010TPDA
* ソースファイル名：JSYejbEDK0011A010TPDA.java
* 作成者 ：EK908580
* 日付 ：2011年05月09日
*＜機能概要＞
* 配送一意照会テンプレート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 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.DK0011ETMsg;
import eo.ejb.cbs.cbsmsg.EDK0011A010CBSMsg;
import eo.ejb.cbs.cbsmsg.EDK0011A010CBSMsg1List;
import eo.ejb.common.JDKModelCommon;
import static eo.ejb.common.JDKModelCommon.*;
import static eo.common.constant.JZM0171Constant.*;
/**
*
* テンプレートDBアクセス部品 EDK0011A010_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/
public class JSYejbEDK0011A010TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
/**
* コンストラクタ
*/
public JSYejbEDK0011A010TPDA() {
}
/**
* EDK0011A010_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void
*/
public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){
// コネクション
Connection con1 = null;
// プリペアステートメント
PreparedStatement pstmtForCount = null;
PreparedStatement pstmtForFetch = null;
// リザルトセット
ResultSet rsltQueryForCount = null;
ResultSet rsltQueryForFetch = null;
/********************
* SQL文の作成
********************/
try{
//コネクション取得
con1 = JSYejbConnection.getConnection(DK0011ETMsg.getTableName());
// SQL文_基本部1
StringBuffer sql_Buff = new StringBuffer();
sql_Buff.append(" SELECT ")
.append(" HAISO.HAISO_NO ")
.append( cd(CD00576, "HAISOBPIN.ADD_OPTNTY_KEI_IDO_DIV", "ADD_OPTNTY_KEI_IDO_DIV_NM"))
.append(" , HAISO.HAISO_STAT ")
.append( cd(CD00009, "HAISO.HAISO_STAT", "HAISO_STAT_NM"))
.append(" , HAISO.HAISO_UK_YMD ")
.append(" , HAISO.HISOS_KOCOMP_CD ")
.append(" , KOCOMP.KOJI_COMP_NM AS HISOS_KOCOMP_NM ")
.append(" , HAISO.HAISO_DIV ")
.append( cd(CD00020, "HAISO.HAISO_DIV", "HAISO_DIV_NM"))
.append(" , HAISO.HAISO_MEMO ")
.append(" , HAISO.HISOS_KSITEI_FLG ")
.append( cd(CD00010, "HAISO.HISOS_KSITEI_FLG", "HISOS_KSITEI_FLG_NM"))
.append(" , HAISO.HISOS_NM ")
.append(" , HAISO.HISOS_AD_CD ")
.append(" , HAISO.HISOS_PCD ")
.append(" , HAISO.HISOS_STATE_NM ")
.append(" , HAISO.HISOS_CITY_NM ")
.append(" , HAISO.HISOS_OAZTSU_NM ")
.append(" , HAISO.HISOS_AZCHO_NM ")
.append(" , HAISO.HISOS_BNCHIGO ")
.append(" , HAISO.HISOS_ADRTTM ")
.append(" , HAISO.HISOS_ADRRM ")
.append(" , HAISO.HISOS_TELNO ")
.append(" , HAISO.HAISO_MOTO_SOKO_CD ")
.append(" , SOKO.SOKO_NM AS HAISO_MOTO_SOKO_NM ")
.append(" , HAISO.HOJIN_SVC_KEI_UK_NO ")
.append(" , HAISO.HOJIN_KEISHA_NM ")
.append(" , HAISO.HOJIN_RRKS_KTAI_TELNO ")
.append(" , HAISO.TA_SYS_RNKI_FLG ")
.append( cd(CD00673, "HAISO.TA_SYS_RNKI_FLG", "TA_SYS_RNKI_FLG_NM"))
.append(" , HAISO.TA_SYS_RNKI_YMD ")
.append(" , HAISO.TA_SYS_RNKS_CD ")
.append( cd(CD00674, "HAISO.TA_SYS_RNKS_CD", "TA_SYS_RNKS_NM"))
.append(" , HAISO.HOJIN_PRC_GRP_CD ")
.append(" , PRC_GRP.PRC_GRP_NM AS HOJIN_PRC_GRP_NM ")
.append(" , HAISO.HOJIN_PCRS_CD ")
.append(" , PCRS.PCRS_NM AS HOJIN_PCRS_NM ")
.append(" , HAISO.HOJIN_PPLAN_CD ")
.append(" , PPLAN.PPLAN_NM AS HOJIN_PPLAN_NM ")
.append(" , HAISO.HAISO_RSV_YMD ")
.append(" , HAISO.HAITATSU_FIN_RSV_DTM ")
.append(" , HAISO.AD_MI_FIX_FLG ")
.append( cd(CD00467, "HAISO.AD_MI_FIX_FLG", "AD_MI_FIX_FLG_NM"))
.append(" , HAISO.HAISO_SJI_YMD ")
.append(" , HAISO.SHUKKA_TAIKI_FLG ")
.append( cd(CD00011, "HAISO.SHUKKA_TAIKI_FLG", "SHUKKA_TAIKI_FLG_NM"))
.append(" , HAISO.MODEL_CHGE_CD ")
.append(" , HAISO.SHUKKA_YMD ")
.append(" , HAISO.SHUKKA_LOT_NO ")
.append(" , HAISO.HAISO_FIN_YMD ")
.append(" , HAISO.HAISO_STP_ID ")
.append(" , USR.USER_NM AS HAISO_STP_USER_NM ")
.append(" , HAISO.HAISO_STP_CD ")
.append( cd(CD00022, "HAISO.HAISO_STP_CD", "HAISO_STP_CD_NM"))
.append(" , HAISO.HAISO_STP_YMD ")
.append(" , HAISO.BTRYHOZN_NO ")
.append(" , HAISO.HAISO_KIGEN_YMD ")
.append(" , HAISO.HAISO_ARIV_SHITEI_YMD ")
.append(" , HAISO.HAISO_ARIV_SHITEI_TIME_CD ")
.append( cd(CD00584, "HAISO.HAISO_ARIV_SHITEI_TIME_CD", "HAISO_ARIV_SHITEI_TIME_NM"))
.append(" , HAISO.MSKM_NO ")
.append(" , HAISO.TCHISHO_TEMP_CD ")
.append(" , HAISO.IDOMOTO_KRIPLACE_SKCD ")
.append(" , HAISO.IDOMOTO_KOCOMP_CD ")
.append(" , KOJICOMP.KOJI_COMP_NM AS IDOMOTO_KOCOMP_NM ")
.append(" , HAISO.IDOMOTO_YTKSKOF_CD ")
.append(" , YTKSKOFFC.YOTAKU_SAKI_OFFC_NM AS IDOMOTO_YTKSKOF_NM ")
.append(" , HAISO.ADD_DTM ")
.append(" , HAISO.ADD_OPEACNT ")
.append(" , HAISO.UPD_DTM ")
.append(" , HAISO.UPD_OPEACNT ")
.append(" , HAISO.DEL_DTM ")
.append(" , HAISO.DEL_OPEACNT ")
.append(" , HAISO.MK_FLG ")
.append( cd(CD00016, "HAISO.MK_FLG", "MK_FLG_NM"))
.append(" FROM ")
.append(" DK_T_HAISO HAISO ")
.append(" LEFT OUTER JOIN DK_M_SOKO SOKO ")
.append(" ON (SOKO.SOKO_CD = HAISO.HAISO_MOTO_SOKO_CD ")
.append(" AND SOKO.MK_FLG = '0') ")
.append(" LEFT OUTER JOIN DK_T_HAISO_TG_BPIN HAISOBPIN ")
.append(" ON HAISOBPIN.HAISO_NO = HAISO.HAISO_NO ")
.append(" AND HAISOBPIN.MK_FLG = '0' ")
.append(" LEFT OUTER JOIN ZM_M_KOJI_COMP KOCOMP ")
.append(" ON (KOCOMP.KOJI_COMP_CD = HAISO.HISOS_KOCOMP_CD ")
.append(" AND KOCOMP.MK_FLG = '0' ")
.append(" ) ")
.append(" LEFT OUTER JOIN ")
.append(" (SELECT PRC_GRP_CD,GENE_ADD_DTM,PRC_GRP_NM,RSV_APLY_YMD,MK_FLG FROM KK_M_PRC_GRP ")
.append(" WHERE (RSV_APLY_YMD || GENE_ADD_DTM) = ")
.append(" (SELECT MAX(RSV_APLY_YMD || GENE_ADD_DTM) FROM KK_M_PRC_GRP WHERE RSV_APLY_YMD <= ? AND MK_FLG = '0') ")
.append(" ) PRC_GRP ")
.append(" ON (PRC_GRP.PRC_GRP_CD = HAISO.HOJIN_PRC_GRP_CD ")
.append(" AND PRC_GRP.RSV_APLY_YMD <= ? ")
.append(" AND PRC_GRP.MK_FLG = '0' ")
.append(" ) ")
.append(" LEFT OUTER JOIN ")
.append(" (SELECT PCRS_CD,GENE_ADD_DTM,PCRS_NM,RSV_APLY_YMD,MK_FLG FROM KK_M_PCRS ")
.append(" WHERE (RSV_APLY_YMD || GENE_ADD_DTM) = ")
.append(" (SELECT MAX(RSV_APLY_YMD || GENE_ADD_DTM) FROM KK_M_PCRS WHERE RSV_APLY_YMD <= ? AND MK_FLG = '0') ")
.append(" ) PCRS ")
.append(" ON (PCRS.PCRS_CD = HAISO.HOJIN_PCRS_CD ")
.append(" AND PCRS.RSV_APLY_YMD <= ? ")
.append(" AND PCRS.MK_FLG = '0' ")
.append(" ) ")
.append(" LEFT OUTER JOIN KK_M_PPLAN PPLAN ")
.append(" ON (PPLAN.PCRS_CD = HAISO.HOJIN_PCRS_CD ")
.append(" AND PPLAN.PPLAN_CD = HAISO.HOJIN_PPLAN_CD ")
.append(" AND PPLAN.PPLAN_TSTAYMD <= ? ")
.append(" AND PPLAN.MK_FLG = '0' ")
.append(" ) ")
.append(" LEFT OUTER JOIN ZM_M_USER USR ")
.append(" ON (USR.USER_ID = HAISO.HAISO_STP_ID ")
.append(" AND USR.MK_FLG = '0' ")
.append(" ) ")
.append(" LEFT OUTER JOIN ZM_M_KOJI_COMP KOJICOMP ")
.append(" ON (KOJICOMP.KOJI_COMP_CD = HAISO.IDOMOTO_KOCOMP_CD ")
.append(" AND KOJICOMP.MK_FLG = '0' ")
.append(" ) ")
.append(" LEFT OUTER JOIN ZM_M_YTK_SK_OFFC YTKSKOFFC ")
.append(" ON (YTKSKOFFC.YOTAKU_SAKI_OFFC_CD = HAISO.IDOMOTO_YTKSKOF_CD ")
.append(" AND YTKSKOFFC.MK_FLG = '0' ")
.append(" ) ");
// SQL文_個別部1
if (JPCModelConstant.FUNC_CD_1.equals(inMsg.getObject(EDK0011A010CBSMsg.FUNC_CODE).toString()))
{
sql_Buff.append(" WHERE ")
.append(" HAISO.HAISO_NO = ? ")
.append(" AND HAISO.MK_FLG = '0' ")
.append(" AND ROWNUM <= 1 ");
}
//=================================================================
// 対象件数を取得するprepareStatementにSQL文をセット
StringBuffer sql_cnt = new StringBuffer();
sql_cnt.append(" SELECT COUNT(*) AS CNT ")
.append(" FROM ( ")
.append(sql_Buff)
.append(" ) A ");
pstmtForCount = con1.prepareStatement(sql_cnt.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_cnt);
int index = 0;
for (int i = 1; i < 17; i++)
{
// パラメータの設定(ＫＥＹ＿運用日付を指定)
CAANJDBCUtil.setParam(pstmtForCount, ++index, inMsg.getObject(EDK0011A010CBSMsg.KEY_UNYO_YMD));
}
// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
if (JPCModelConstant.FUNC_CD_1.equals(inMsg.getString(EDK0011A010CBSMsg.FUNC_CODE)))
{
// パラメータの設定(ＫＥＹ＿配送番号を指定)
CAANJDBCUtil.setParam(pstmtForCount, ++index, inMsg.getObject(EDK0011A010CBSMsg.KEY_HAISO_NO));
}
// ResultSetの取得
rsltQueryForCount = pstmtForCount.executeQuery();
rsltQueryForCount.next();
int totalSearchNum = rsltQueryForCount.getInt(1);
//検索結果件数が0件
if (totalSearchNum == 0)
{
inMsg.set("search_err_flg", JPCModelConstant.SEARCH_ERR_FLG_ZERO);
return;
}
//=================================================================
//データを取得するprepareStatementにSQL文をセット
pstmtForFetch = con1.prepareStatement(sql_Buff.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff);
index = 0;
for (int i = 1; i < 17; i++)
{
// パラメータの設定(ＫＥＹ＿運用日付を指定)
CAANJDBCUtil.setParam(pstmtForFetch, ++index, inMsg.getObject(EDK0011A010CBSMsg.KEY_UNYO_YMD));
}
// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
if (JPCModelConstant.FUNC_CD_1.equals(inMsg.getString(EDK0011A010CBSMsg.FUNC_CODE)))
{
// パラメータの設定(ＫＥＹ＿配送番号を指定)
CAANJDBCUtil.setParam(pstmtForFetch, ++index, inMsg.getObject(EDK0011A010CBSMsg.KEY_HAISO_NO));
}
// ResultSetの取得
rsltQueryForFetch = pstmtForFetch.executeQuery();
// EDK0011A010CBSMsgの明細にセットする項目
String[] msgKeyList1 = {
EDK0011A010CBSMsg1List.HAISO_NO
, EDK0011A010CBSMsg1List.ADD_OPTNTY_KEI_IDO_DIV_NM
, EDK0011A010CBSMsg1List.HAISO_STAT
, EDK0011A010CBSMsg1List.HAISO_STAT_NM
, EDK0011A010CBSMsg1List.HAISO_UK_YMD
, EDK0011A010CBSMsg1List.HISOS_KOCOMP_CD
, EDK0011A010CBSMsg1List.HISOS_KOCOMP_NM
, EDK0011A010CBSMsg1List.HAISO_DIV
, EDK0011A010CBSMsg1List.HAISO_DIV_NM
, EDK0011A010CBSMsg1List.HAISO_MEMO
, EDK0011A010CBSMsg1List.HISOS_KSITEI_FLG
, EDK0011A010CBSMsg1List.HISOS_KSITEI_FLG_NM
, EDK0011A010CBSMsg1List.HISOS_NM
, EDK0011A010CBSMsg1List.HISOS_AD_CD
, EDK0011A010CBSMsg1List.HISOS_PCD
, EDK0011A010CBSMsg1List.HISOS_STATE_NM
, EDK0011A010CBSMsg1List.HISOS_CITY_NM
, EDK0011A010CBSMsg1List.HISOS_OAZTSU_NM
, EDK0011A010CBSMsg1List.HISOS_AZCHO_NM
, EDK0011A010CBSMsg1List.HISOS_BNCHIGO
, EDK0011A010CBSMsg1List.HISOS_ADRTTM
, EDK0011A010CBSMsg1List.HISOS_ADRRM
, EDK0011A010CBSMsg1List.HISOS_TELNO
, EDK0011A010CBSMsg1List.HAISO_MOTO_SOKO_CD
, EDK0011A010CBSMsg1List.HAISO_MOTO_SOKO_NM
, EDK0011A010CBSMsg1List.HOJIN_SVC_KEI_UK_NO
, EDK0011A010CBSMsg1List.HOJIN_KEISHA_NM
, EDK0011A010CBSMsg1List.HOJIN_RRKS_KTAI_TELNO
, EDK0011A010CBSMsg1List.TA_SYS_RNKI_FLG
, EDK0011A010CBSMsg1List.TA_SYS_RNKI_FLG_NM
, EDK0011A010CBSMsg1List.TA_SYS_RNKI_YMD
, EDK0011A010CBSMsg1List.TA_SYS_RNKS_CD
, EDK0011A010CBSMsg1List.TA_SYS_RNKS_NM
, EDK0011A010CBSMsg1List.HOJIN_PRC_GRP_CD
, EDK0011A010CBSMsg1List.HOJIN_PRC_GRP_NM
, EDK0011A010CBSMsg1List.HOJIN_PCRS_CD
, EDK0011A010CBSMsg1List.HOJIN_PCRS_NM
, EDK0011A010CBSMsg1List.HOJIN_PPLAN_CD
, EDK0011A010CBSMsg1List.HOJIN_PPLAN_NM
, EDK0011A010CBSMsg1List.HAISO_RSV_YMD
, EDK0011A010CBSMsg1List.HAITATSU_FIN_RSV_DTM
, EDK0011A010CBSMsg1List.AD_MI_FIX_FLG
, EDK0011A010CBSMsg1List.AD_MI_FIX_FLG_NM
, EDK0011A010CBSMsg1List.HAISO_SJI_YMD
, EDK0011A010CBSMsg1List.SHUKKA_TAIKI_FLG
, EDK0011A010CBSMsg1List.SHUKKA_TAIKI_FLG_NM
, EDK0011A010CBSMsg1List.MODEL_CHGE_CD
, EDK0011A010CBSMsg1List.SHUKKA_YMD
, EDK0011A010CBSMsg1List.SHUKKA_LOT_NO
, EDK0011A010CBSMsg1List.HAISO_FIN_YMD
, EDK0011A010CBSMsg1List.HAISO_STP_ID
, EDK0011A010CBSMsg1List.HAISO_STP_USER_NM
, EDK0011A010CBSMsg1List.HAISO_STP_CD
, EDK0011A010CBSMsg1List.HAISO_STP_CD_NM
, EDK0011A010CBSMsg1List.HAISO_STP_YMD
, EDK0011A010CBSMsg1List.BTRYHOZN_NO
, EDK0011A010CBSMsg1List.HAISO_KIGEN_YMD
, EDK0011A010CBSMsg1List.HAISO_ARIV_SHITEI_YMD
, EDK0011A010CBSMsg1List.HAISO_ARIV_SHITEI_TIME_CD
, EDK0011A010CBSMsg1List.HAISO_ARIV_SHITEI_TIME_NM
, EDK0011A010CBSMsg1List.MSKM_NO
, EDK0011A010CBSMsg1List.TCHISHO_TEMP_CD
, EDK0011A010CBSMsg1List.IDOMOTO_KRIPLACE_SKCD
, EDK0011A010CBSMsg1List.IDOMOTO_KOCOMP_CD
, EDK0011A010CBSMsg1List.IDOMOTO_KOCOMP_NM
, EDK0011A010CBSMsg1List.IDOMOTO_YTKSKOF_CD
, EDK0011A010CBSMsg1List.IDOMOTO_YTKSKOF_NM
, EDK0011A010CBSMsg1List.ADD_DTM
, EDK0011A010CBSMsg1List.ADD_OPEACNT
, EDK0011A010CBSMsg1List.UPD_DTM
, EDK0011A010CBSMsg1List.UPD_OPEACNT
, EDK0011A010CBSMsg1List.DEL_DTM
, EDK0011A010CBSMsg1List.DEL_OPEACNT
, EDK0011A010CBSMsg1List.MK_FLG
, EDK0011A010CBSMsg1List.MK_FLG_NM
};
// 明細に処理結果(ResultSet)をマッピング
CAANMsg[] outMsg1 = mapMessageList(rsltQueryForFetch,msgKeyList1,"eo.ejb.cbs.cbsmsg.EDK0011A010CBSMsg1List",0);
// inMsg(CBSMsg)に明細をセット
inMsg.set("EDK0011A010CBSMsg1List", outMsg1);
} catch(SQLException e) {
inMsg.set(EDK0011A010CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
} finally {
// 資源の解放
try{
SQLException exception = JDKModelCommon.close(pstmtForCount, pstmtForFetch, rsltQueryForCount, rsltQueryForFetch);
if(con1 != null){
closeConnection(con1);
}
if (exception != null)
{
throw exception;
}
}catch(SQLException e){
inMsg.set(EDK0011A010CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
}
}
}
}