/*******************************************************************************
* All Rights reserved,Copyright (c) K-Opticom
********************************************************************************
*＜プログラム内容＞
* システム名 ：eo顧客基幹システム
* モジュール名 ：JSYejbEDK0111B030TPDA
* ソースファイル名：JSYejbEDK0111B030TPDA.java
* 作成者 ：EK909135
* 日付 ：2011年08月11日
*＜機能概要＞
* 棚移動結果情報一覧照会テンプレート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.EDK0111B030CBSMsg;
import eo.ejb.cbs.cbsmsg.EDK0111B030CBSMsg1List;
import eo.ejb.cbm.entity.DK0111ETMsg;
/**
*
* テンプレートDBアクセス部品 EDK0111B030_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/
public class JSYejbEDK0111B030TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
/**
* コンストラクタ
*/
public JSYejbEDK0111B030TPDA() {
}
/**
* EDK0111B030_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(DK0111ETMsg.getTableName());
// SQL文_基本部1
StringBuffer sql_Buff = new StringBuffer();
sql_Buff.append(" SELECT ")
.append(" MOVENO, ")
.append(" SJIYMD, ")
.append(" MVMTSOKOCD, ")
.append(" MVMTKOJICD, ")
.append(" MVMTOFFICECD, ")
.append(" MVMTSHELFCD, ")
.append(" TKKIKIMDLCD, ")
.append(" TKKIKISETHINMDLCD, ")
.append(" HUZOKUHINMDLCD, ")
.append(" KIKISEIZONO, ")
.append(" TKKIKISETHINSEIZONO, ")
.append(" GDSSTATCD, ")
.append(" SJICNT, ")
.append(" MVSKSOKOCD, ")
.append(" MVSKKOJICD, ")
.append(" MVSKOFFICECD, ")
.append(" MVSKSHELFCD, ")
.append(" SHITEICD, ")
.append(" MVKBN ")
.append(" FROM ")
.append(" ( ")
.append(" SELECT DISTINCT ")
.append(" SOKODNA.SOKODNA_MOVE_NO AS MOVENO, ")
.append(" SOKODNA.SOKODNA_MOVE_ADD_YMD AS SJIYMD, ")
.append(" SOKODNA.SOKO_CD AS MVMTSOKOCD, ")
.append(" SOKODNA.KOJI_COMP_CD AS MVMTKOJICD, ")
.append(" SOKODNA.YOTAKU_SAKI_OFFC_CD AS MVMTOFFICECD, ")
.append(" SOKODNA.MOVE_MOTO_SHELF_CD AS MVMTSHELFCD, ")
.append(" SOKODNA.TAKNKIKI_MODEL_CD AS TKKIKIMDLCD, ")
.append(" SOKODNA.TAKNKIKI_SETHIN_MODEL_CD AS TKKIKISETHINMDLCD, ")
.append(" SOKODNA.HUZOKUHIN_MODEL_CD AS HUZOKUHINMDLCD, ")
.append(" TAISYOKK.KIKI_SEIZO_NO AS KIKISEIZONO, ")
.append(" TAISYOKK.TAKNKIKI_SETHIN_NO AS TKKIKISETHINSEIZONO, ")
.append(" SOKODNA.GDS_STAT_CD AS GDSSTATCD, ")
.append(" SOKODNA.SJI_CNT AS SJICNT, ")
.append(" SOKODNA.SOKO_CD AS MVSKSOKOCD, ")
.append(" SOKODNA.KOJI_COMP_CD AS MVSKKOJICD, ")
.append(" SOKODNA.YOTAKU_SAKI_OFFC_CD AS MVSKOFFICECD, ")
.append(" SOKODNA.MOVE_SAKI_SHELF_CD AS MVSKSHELFCD, ")
.append(" SOKODNA.SHITEI_WAY_CD AS SHITEICD, ")
.append(" '1' AS MVKBN, ")
.append(" TAISYOKK.SOKODNA_MOVE_TAIKIKI_NO AS KIKINO ")
.append(" FROM DK_T_SOKODNA_MOVE SOKODNA ")
.append(" LEFT OUTER JOIN DK_T_SDNAMV_TG_KIKI TAISYOKK ")
.append(" ON (TAISYOKK.SOKODNA_MOVE_NO = SOKODNA.SOKODNA_MOVE_NO ")
.append(" AND TAISYOKK.MK_FLG = '0') ")
.append(" WHERE SOKODNA.SHELF_MOVE_LOT_NO = ? ")
.append(" AND SOKODNA.MK_FLG = '0' ")
.append(" UNION ALL ")
.append(" SELECT DISTINCT ")
.append(" YBK.YBKIKI_HAISO_NO AS MOVENO, ")
.append(" YBK.YBKKHAI_UK_YMD AS SJIYMD, ")
.append(" YBK.HAISO_MOTO_SOKO_CD AS MVMTSOKOCD, ")
.append(" YBK.HISOM_KOCOMP_CD AS MVMTKOJICD, ")
.append(" YBK.HISOM_YOTAKU_SK_OFFC_CD AS MVMTOFFICECD, ")
.append(" YBK.HISOM_SHELF_CD AS MVMTSHELFCD, ")
.append(" YBK.TAKNKIKI_MODEL_CD AS TKKIKIMDLCD, ")
.append(" YBK.TAKNKIKI_SETHIN_MODEL_CD AS TKKIKISETHINMDLCD, ")
.append(" YBK.HUZOKUHIN_MODEL_CD AS HUZOKUHINMDLCD, ")
.append(" TAISYOBPN.KIKI_SEIZO_NO AS KIKISEIZONO, ")
.append(" TAISYOBPN.TAKNKIKI_SETHIN_NO AS TKKIKISETHINSEIZONO, ")
.append(" YBK.GDS_STAT_CD AS GDSSTATCD, ")
.append(" YBK.SJI_CNT AS SJICNT, ")
.append(" YBK.HISOS_SOKO_CD AS MVSKSOKOCD, ")
.append(" YBK.HISOS_KOCOMP_CD AS MVSKKOJICD, ")
.append(" YBK.HISOS_YOTAKU_SK_OFFC_CD AS MVSKOFFICECD, ")
.append(" YBK.HISOS_SHELF_CD AS MVSKSHELFCD, ")
.append(" YBK.SHITEI_WAY_CD AS SHITEICD, ")
.append(" '2' AS MVKBN, ")
.append(" TAISYOBPN.YBKIKI_HAISO_TAIBPIN_NO AS KIKINO ")
.append(" FROM DK_T_YBKIKI_HAISO YBK ")
.append(" LEFT OUTER JOIN DK_T_YBKKHAI_TG_BPIN TAISYOBPN ")
.append(" ON (TAISYOBPN.YBKIKI_HAISO_NO = YBK.YBKIKI_HAISO_NO ")
.append(" AND TAISYOBPN.MK_FLG = '0') ")
.append(" WHERE YBK.SHELF_MOVE_LOT_NO = ? ")
.append(" AND YBK.MK_FLG = '0' ")
.append(" ) ");
/* ++++++++++ ｖ1.00.00 削除開始 ++++++++++ */
// SQL文_個別部1
// if ( "1".equals( inMsg.getObject(EDK0111B030CBSMsg.FUNC_CODE).toString() ) ) {
/* ++++++++++ ｖ1.00.00 削除終了 ++++++++++ */
sql_Buff.append(" ORDER BY ")
.append(" MOVENO ASC, ")
.append(" KIKINO ASC ");
/* ++++++++++ ｖ1.00.00 削除開始 ++++++++++ */
// }
/* ++++++++++ ｖ1.00.00 削除終了 ++++++++++ */
//prepareStatementにSQL文をセット
pstmt = con1.prepareStatement(sql_Buff.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff);
// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
/* ++++++++++ ｖ1.00.00 変更開始 ++++++++++ */
// if ( "1".equals( inMsg.getString(EDK0111B030CBSMsg.FUNC_CODE) ) ) {
// パラメータの設定(ＫＥＹ＿棚移動ロット番号を指定)
CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(EDK0111B030CBSMsg.KEY_SHELF_MV_LOT_NO));
CAANJDBCUtil.setParam(pstmt, 2, inMsg.getObject(EDK0111B030CBSMsg.KEY_SHELF_MV_LOT_NO));
// }
/* ++++++++++ ｖ1.00.00 変更終了 ++++++++++ */
// ResultSetの取得
rsltQuery = pstmt.executeQuery();
// EDK0111B030CBSMsgの明細にセットする項目
String[] msgKeyList1 = {
EDK0111B030CBSMsg1List.MOVE_NO
, EDK0111B030CBSMsg1List.SJI_YMD
, EDK0111B030CBSMsg1List.MV_MT_SOKO_CD
, EDK0111B030CBSMsg1List.MV_MT_KOJI_CD
, EDK0111B030CBSMsg1List.MV_MT_OFFICE_CD
, EDK0111B030CBSMsg1List.MV_MT_SHELF_CD
, EDK0111B030CBSMsg1List.TKKIKI_MDL_CD
, EDK0111B030CBSMsg1List.TKKIKI_SETHIN_MDL_CD
, EDK0111B030CBSMsg1List.HUZOKUHIN_MDL_CD
, EDK0111B030CBSMsg1List.KIKI_SEIZO_NO
, EDK0111B030CBSMsg1List.TKKIKI_SETHIN_SEIZO_NO
, EDK0111B030CBSMsg1List.GDS_STAT_CD
, EDK0111B030CBSMsg1List.SJI_CNT
, EDK0111B030CBSMsg1List.MV_SK_SOKO_CD
, EDK0111B030CBSMsg1List.MV_SK_KOJI_CD
, EDK0111B030CBSMsg1List.MV_SK_OFFICE_CD
, EDK0111B030CBSMsg1List.MV_SK_SHELF_CD
, EDK0111B030CBSMsg1List.SHITEI_WAY_CD
, EDK0111B030CBSMsg1List.MV_SBT
};
// 明細に処理結果(ResultSet)をマッピング
CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EDK0111B030CBSMsg1List",0);
// inMsg(CBSMsg)に明細をセット
inMsg.set("EDK0111B030CBSMsg1List", outMsg1);
} catch(SQLException e) {
inMsg.set(EDK0111B030CBSMsg.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(EDK0111B030CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
}
}
}
}