/*******************************************************************************
* All Rights reserved,Copyright (c) K-Opticom
********************************************************************************
*＜プログラム内容＞
* システム名 ：eo顧客基幹システム
* モジュール名 ：JSYejbEKU0141B010TPDA
* ソースファイル名：JSYejbEKU0141B010TPDA.java
* 作成者 ：富士通
* 日付 ：2011年07月31日
*＜機能概要＞
* SQLFacility
* テンプレートDBアクセス部品です。
*＜修正履歴＞
* バージョン 修正日 修正者 修正内容
* v1.00.00 2011/07/31 FJ)高橋 新規作成
* v3.00.00 2012/04/06 FJ)高橋 【ANK-0376-00-00】(OPMS連携)機器出荷情報連携における連携項目追加】
* v7.00.00 2013/11/12 FJ)中西 【ANK-1578-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.common.constant.JKUStrConst;
import eo.common.constant.JPCModelConstant;
import eo.ejb.cbs.cbsmsg.EKU0141B010CBSMsg;
import eo.ejb.cbs.cbsmsg.EKU0141B010CBSMsg1List;
import eo.ejb.cbm.entity.KU0141ETMsg;
/**
*
* テンプレートDBアクセス部品 EKU0141B010_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/
public class JSYejbEKU0141B010TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
/**
* コンストラクタ
*/
public JSYejbEKU0141B010TPDA() {
}
/**
* EKU0141B010_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void
*/
public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){
// コネクション
Connection con1 = null;
// プリペアステートメント
PreparedStatement pstmt = null;
// リザルトセット
ResultSet rsltQuery = null;
/**
* パラメータセット用
*/
int iIndex = 1;
// 検索処理パターン
String searchType = (String)inMsg.getObject(EKU0141B010CBSMsg.SEARCH_TYPE);
if(JPCModelConstant.SAERCH_TYPE_IKT.equals(searchType))
{
/**
* 最大検索件数が未設定の場合
*/
if (inMsg.isNull(EKU0141B010CBSMsg.MAX_SEARCH_NUM))
{
throw new IllegalArgumentException(JKUStrConst.ICRN_ERR_MSG_IKT);
}
}
else if(JPCModelConstant.SAERCH_TYPE_BBN.equals(searchType))
{
/**
* 最大検索件数、表示件数、表示ページ番号が未設定の場合
*/
if (inMsg.isNull(EKU0141B010CBSMsg.MAX_SEARCH_NUM)
|| inMsg.isNull(EKU0141B010CBSMsg.DISPLAY_NUM)
|| inMsg.isNull(EKU0141B010CBSMsg.DISPLAY_PAGE_NUM))
{
throw new IllegalArgumentException(JKUStrConst.ICRN_ERR_MSG_BBN);
}
}
else
{
throw new IllegalArgumentException(JKUStrConst.ICRN_ERR_MSG_FUSEI_PTN);
}
/********************
* SQL文の作成
********************/
try{
//コネクション取得
con1 = JSYejbConnection.getConnection(KU0141ETMsg.getTableName());
/***********************************************************************************************
* 総件数取得
*/
// SQL文_基本部1
StringBuffer sql_Buff = new StringBuffer();
StringBuffer sql_Select = new StringBuffer();
StringBuffer sql_From = new StringBuffer();
StringBuffer sql_Where = new StringBuffer();
StringBuffer sql_Order = new StringBuffer();
// SQL文_基本部1
sql_Select.append(" SELECT ")
.append(" ROW_NUMBER() OVER(ORDER BY KU0141.TAKNKIKI_SBT_CD, KU0141.KOJIAK_TRGT_TAKNKIKI_NO) AS ROW_NUM ")
.append(" ,KU0141.KOJIAK_NO ")
.append(" ,KU0141.KOJIAK_TRGT_TAKNKIKI_NO ")
.append(" ,KU0141.KOJIAK_TRGT_TAKNKIKI_STAT ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD00475' ")
.append(" AND ZM0171.CD_DIV = KU0141.KOJIAK_TRGT_TAKNKIKI_STAT ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )KOJIAK_TRGT_TAKNKIKI_STAT_NM ")
.append(" ,KU0141.KOJIAK_TRGT_KIKI_GRP_NO ")
.append(" ,KU0141.STC_TEKKYO_FLG ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD00772' ")
.append(" AND ZM0171.CD_DIV = KU0141.STC_TEKKYO_FLG ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )STC_TEKKYO_FLG_NM ")
.append(" ,KU0141.TAKNKIKI_SBT_CD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD01262' ")
.append(" AND ZM0171.CD_DIV = KU0141.TAKNKIKI_SBT_CD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )TAKNKIKI_SBT_NM ")
.append(" ,KU0141.TAKNKIKI_IDO_CD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD00562' ")
.append(" AND ZM0171.CD_DIV = KU0141.TAKNKIKI_IDO_CD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )TAKNKIKI_IDO_NM ")
.append(" ,KU0141.RSV_TAKNKIKI_MODEL_CD ")
.append(" ,KU0141.RSV_KIKI_SEIZO_NO ")
.append(" ,KU0141.RSV_MAKER_CD ")
.append(" ,(SELECT MAKER_NM_1 ")
.append(" FROM ZM_M_MAKER ZM0401 ")
.append(" WHERE KU0141.RSV_MAKER_CD = ZM0401.MAKER_CD ")
.append(" AND ZM0401.MK_FLG = '0' ")
.append(" )RSV_MAKER_NM ")
.append(" ,KU0141.RSV_MACAD ")
.append(" ,KU0141.RSV_STB_ID ")
.append(" ,KU0141.RSV_HDD_CAPA_CD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD00557' ")
.append(" AND ZM0171.CD_DIV = KU0141.RSV_HDD_CAPA_CD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )RSV_HDD_CAPA_NM ")
.append(" ,KU0141.RSV_VONU_BSPT_KH ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD00003' ")
.append(" AND ZM0171.CD_DIV = KU0141.RSV_VONU_BSPT_KH ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )RSV_VONU_BSPT_KH_NM ")
.append(" ,KU0141.RSV_STB_KEI_TV_COURSE_CD ")
.append(" ,CASE ")
.append(" WHEN KU0011.KOJIAK_REQYMD IS NOT NULL ")
.append(" THEN (SELECT KK0791.PCRS_NM FROM KK_M_PCRS KK0791 ")
.append(" WHERE ")
.append(" KU0141.RSV_STB_KEI_TV_COURSE_CD = KK0791.PCRS_CD ")
.append(" AND KU0011.KOJIAK_REQYMD BETWEEN KK0791.PCRS_TSTAYMD AND KK0791.PCRS_TENDYMD ")
.append(" AND KK0791.MK_FLG = '0') ")
.append(" WHEN KU0011.MSKM_UK_YMD IS NOT NULL ")
.append(" THEN (SELECT KK0791.PCRS_NM FROM KK_M_PCRS KK0791 ")
.append(" WHERE ")
.append(" KU0141.RSV_STB_KEI_TV_COURSE_CD = KK0791.PCRS_CD ")
.append(" AND KU0011.MSKM_UK_YMD BETWEEN KK0791.PCRS_TSTAYMD AND KK0791.PCRS_TENDYMD ")
.append(" AND KK0791.MK_FLG = '0') ")
.append(" ELSE (SELECT KK0791.PCRS_NM FROM KK_M_PCRS KK0791 ")
.append(" WHERE ")
.append(" KU0141.RSV_STB_KEI_TV_COURSE_CD = KK0791.PCRS_CD ")
.append(" AND KK0791.PCRS_TENDYMD = '20991231' ")
.append(" AND KK0791.MK_FLG = '0') ")
.append(" END RSV_STB_KEI_TV_COURSE_NM ")
.append(" ,KU0141.RSV_ROUTER_KIND_CD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD01152' ")
.append(" AND ZM0171.CD_DIV = KU0141.RSV_ROUTER_KIND_CD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )RSV_ROUTER_KIND_NM ")
.append(" ,KU0141.RSV_ROUTER_RENT_CD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD01143' ")
.append(" AND ZM0171.CD_DIV = KU0141.RSV_ROUTER_RENT_CD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )RSV_ROUTER_RENT_NM ")
.append(" ,KU0141.RSV_TAKINORT_KINO_CD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD01603' ")
.append(" AND ZM0171.CD_DIV = KU0141.RSV_TAKINORT_KINO_CD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )RSV_TAKINORT_KINO_NM ")
.append(" ,KU0141.RSV_TAKINORT_RTR_KINO_SKCD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD01604' ")
.append(" AND ZM0171.CD_DIV = KU0141.RSV_TAKINORT_RTR_KINO_SKCD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )RSV_TAKINORT_RTR_KINO_SKBT_NM ")
.append(" ,KU0141.CHGE_TAKNKIKI_MODEL_CD ")
.append(" ,KU0141.CHGE_KIKI_SEIZO_NO ")
.append(" ,KU0141.CHGE_MAKER_CD ")
.append(" ,(SELECT MAKER_NM_1 ")
.append(" FROM ZM_M_MAKER ZM0401 ")
.append(" WHERE KU0141.CHGE_MAKER_CD = ZM0401.MAKER_CD ")
.append(" AND ZM0401.MK_FLG = '0' ")
.append(" )CHGE_MAKER_NM ")
.append(" ,KU0141.CHGE_MACAD ")
.append(" ,KU0141.CHGE_STB_ID ")
.append(" ,KU0141.CHGE_HDD_CAPA_CD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD00557' ")
.append(" AND ZM0171.CD_DIV = KU0141.CHGE_HDD_CAPA_CD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )CHGE_HDD_CAPA_NM ")
.append(" ,KU0141.CHGE_VONU_BSPT_KH ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD00003' ")
.append(" AND ZM0171.CD_DIV = KU0141.CHGE_VONU_BSPT_KH ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )CHGE_VONU_BSPT_KH_NM ")
.append(" ,KU0141.CHGE_STB_KEI_TV_COURSE_CD ")
.append(" ,CASE ")
.append(" WHEN KU0011.KOJIAK_REQYMD IS NOT NULL ")
.append(" THEN (SELECT KK0791.PCRS_NM FROM KK_M_PCRS KK0791 ")
.append(" WHERE ")
.append(" KU0141.CHGE_STB_KEI_TV_COURSE_CD = KK0791.PCRS_CD ")
.append(" AND KU0011.KOJIAK_REQYMD BETWEEN KK0791.PCRS_TSTAYMD AND KK0791.PCRS_TENDYMD ")
.append(" AND KK0791.MK_FLG = '0') ")
.append(" WHEN KU0011.MSKM_UK_YMD IS NOT NULL ")
.append(" THEN (SELECT KK0791.PCRS_NM FROM KK_M_PCRS KK0791 ")
.append(" WHERE ")
.append(" KU0141.CHGE_STB_KEI_TV_COURSE_CD = KK0791.PCRS_CD ")
.append(" AND KU0011.MSKM_UK_YMD BETWEEN KK0791.PCRS_TSTAYMD AND KK0791.PCRS_TENDYMD ")
.append(" AND KK0791.MK_FLG = '0') ")
.append(" ELSE (SELECT KK0791.PCRS_NM FROM KK_M_PCRS KK0791 ")
.append(" WHERE ")
.append(" KU0141.CHGE_STB_KEI_TV_COURSE_CD = KK0791.PCRS_CD ")
.append(" AND KK0791.PCRS_TENDYMD = '20991231' ")
.append(" AND KK0791.MK_FLG = '0') ")
.append(" END CHGE_STB_KEI_TV_COURSE_NM ")
.append(" ,KU0141.CHGE_ROUTER_KIND_CD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD01152' ")
.append(" AND ZM0171.CD_DIV = KU0141.CHGE_ROUTER_KIND_CD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )CHGE_ROUTER_KIND_NM ")
.append(" ,KU0141.CHGE_ROUTER_RENT_CD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD01143' ")
.append(" AND ZM0171.CD_DIV = KU0141.CHGE_ROUTER_RENT_CD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )CHGE_ROUTER_RENT_NM ")
.append(" ,KU0141.CHG_TAKINORT_KINO_CD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD01603' ")
.append(" AND ZM0171.CD_DIV = KU0141.CHG_TAKINORT_KINO_CD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )CHG_TAKINORT_KINO_NM ")
.append(" ,KU0141.CHG_TAKINORT_RTR_KINO_SKCD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD01604' ")
.append(" AND ZM0171.CD_DIV = KU0141.CHG_TAKINORT_RTR_KINO_SKCD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )CHG_TAKINORT_RTR_KINO_SKBT_NM ")
.append(" ,KU0141.CHGE_SHUKKA_JOKYO_FLG ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD00776' ")
.append(" AND ZM0171.CD_DIV = KU0141.CHGE_SHUKKA_JOKYO_FLG ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )CHGE_SHUKKA_JOKYO_FLG_NM ")
.append(" ,KU0141.JSEKI_TAKNKIKI_MODEL_CD ")
.append(" ,KU0141.JSEKI_KIKI_SEIZO_NO ")
.append(" ,KU0141.JSEKI_MAKER_CD ")
.append(" ,(SELECT MAKER_NM_1 ")
.append(" FROM ZM_M_MAKER ZM0401 ")
.append(" WHERE KU0141.JSEKI_MAKER_CD = ZM0401.MAKER_CD ")
.append(" AND ZM0401.MK_FLG = '0' ")
.append(" )JSEKI_MAKER_NM ")
.append(" ,KU0141.JSEKI_MACAD ")
.append(" ,KU0141.JSEKI_STB_ID ")
.append(" ,KU0141.JSEKI_HDD_CAPA_CD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD00557' ")
.append(" AND ZM0171.CD_DIV = KU0141.JSEKI_HDD_CAPA_CD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )JSEKI_HDD_CAPA_NM ")
.append(" ,KU0141.JSEKI_VONU_BSPT_KH ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD00003' ")
.append(" AND ZM0171.CD_DIV = KU0141.JSEKI_VONU_BSPT_KH ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )JSEKI_VONU_BSPT_KH_NM ")
.append(" ,KU0141.JSEKI_STB_KEI_TV_COURSE_CD ")
.append(" ,CASE ")
.append(" WHEN KU0011.KOJIAK_REQYMD IS NOT NULL ")
.append(" THEN (SELECT KK0791.PCRS_NM FROM KK_M_PCRS KK0791 ")
.append(" WHERE ")
.append(" KU0141.JSEKI_STB_KEI_TV_COURSE_CD = KK0791.PCRS_CD ")
.append(" AND KU0011.KOJIAK_REQYMD BETWEEN KK0791.PCRS_TSTAYMD AND KK0791.PCRS_TENDYMD ")
.append(" AND KK0791.MK_FLG = '0') ")
.append(" WHEN KU0011.MSKM_UK_YMD IS NOT NULL ")
.append(" THEN (SELECT KK0791.PCRS_NM FROM KK_M_PCRS KK0791 ")
.append(" WHERE ")
.append(" KU0141.JSEKI_STB_KEI_TV_COURSE_CD = KK0791.PCRS_CD ")
.append(" AND KU0011.MSKM_UK_YMD BETWEEN KK0791.PCRS_TSTAYMD AND KK0791.PCRS_TENDYMD ")
.append(" AND KK0791.MK_FLG = '0') ")
.append(" ELSE (SELECT KK0791.PCRS_NM FROM KK_M_PCRS KK0791 ")
.append(" WHERE ")
.append(" KU0141.JSEKI_STB_KEI_TV_COURSE_CD = KK0791.PCRS_CD ")
.append(" AND KK0791.PCRS_TENDYMD = '20991231' ")
.append(" AND KK0791.MK_FLG = '0') ")
.append(" END JSEKI_STB_KEI_TV_COURSE_NM ")
.append(" ,KU0141.JSEKI_ROUTER_KIND_CD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD01152' ")
.append(" AND ZM0171.CD_DIV = KU0141.JSEKI_ROUTER_KIND_CD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )JSEKI_ROUTER_KIND_NM ")
.append(" ,KU0141.JSEKI_ROUTER_RENT_CD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD01143' ")
.append(" AND ZM0171.CD_DIV = KU0141.JSEKI_ROUTER_RENT_CD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )JSEKI_ROUTER_RENT_NM ")
.append(" ,KU0141.JSK_TAKINORT_KINO_CD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD01603' ")
.append(" AND ZM0171.CD_DIV = KU0141.JSK_TAKINORT_KINO_CD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )JSK_TAKINORT_KINO_NM ")
.append(" ,KU0141.JSK_TAKINORT_RTR_KINO_SKCD ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD01604' ")
.append(" AND ZM0171.CD_DIV = KU0141.JSK_TAKINORT_RTR_KINO_SKCD ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )JSK_TAKINORT_RTR_KINO_SKBT_NM ")
.append(" ,KU0141.KKTK_SVC_KEI_NO ")
.append(" ,KU0141.KKTK_SVC_KEI_CHGE_MEMO ")
.append(" ,KU0141.MOTO_TCASE_NO ")
.append(" ,KU0141.OPMS_TKKIKI_CHGE_RSN_CD_NM ")
.append(" ,KU0141.OPMS_TKKIKI_CHGE_RSN_MEMO ")
.append(" ,KU0141.ADD_DTM ")
.append(" ,KU0141.ADD_OPEACNT ")
.append(" ,(SELECT ZM0051.USER_NM ")
.append(" FROM ZM_M_USER ZM0051 ")
.append(" WHERE ZM0051.USER_ID = KU0141.ADD_OPEACNT ")
.append(" AND ZM0051.MK_FLG = '0' ")
.append(" )ADD_OPEACNT_NM ")
.append(" ,KU0141.UPD_DTM ")
.append(" ,KU0141.UPD_OPEACNT ")
.append(" ,(SELECT ZM0051.USER_NM ")
.append(" FROM ZM_M_USER ZM0051 ")
.append(" WHERE ZM0051.USER_ID = KU0141.UPD_OPEACNT ")
.append(" AND ZM0051.MK_FLG = '0' ")
.append(" )UPD_OPEACNT_NM ")
.append(" ,KU0141.DEL_DTM ")
.append(" ,KU0141.DEL_OPEACNT ")
.append(" ,(SELECT ZM0051.USER_NM ")
.append(" FROM ZM_M_USER ZM0051 ")
.append(" WHERE ZM0051.USER_ID = KU0141.DEL_OPEACNT ")
.append(" AND ZM0051.MK_FLG = '0' ")
.append(" )DEL_OPEACNT_NM ")
.append(" ,KU0141.MK_FLG ")
.append(" ,(SELECT ZM0171.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD00016' ")
.append(" AND ZM0171.CD_DIV = KU0141.MK_FLG ")
.append(" AND ZM0171.MK_FLG = '0' ")
.append(" )MK_FLG_NM ");
//From句
sql_From.append(" FROM ")
.append(" KU_T_KJAK_TG_TKKIKI KU0141 ")
.append(" INNER JOIN KU_T_KOJIAK KU0011 ")
.append(" ON KU0141.KOJIAK_NO = KU0011.KOJIAK_NO ");
// SQL文_個別部1
if ( "1".equals( inMsg.getObject(EKU0141B010CBSMsg.FUNC_CODE).toString() ) ) {
sql_Where.append(" WHERE ")
.append(" KU0141.KOJIAK_NO = ? ")
.append(" AND KU0141.MK_FLG = '0' ")
.append(" AND KU0011.MK_FLG = '0' ");
}
sql_Order.append(" ORDER BY ")
.append(" KU0141.TAKNKIKI_SBT_CD")
.append(" ,KU0141.KOJIAK_TRGT_TAKNKIKI_NO");
//パラメータ取得
int iDispNum = 0;
int iDispPageNum = 0;
//開始行、終了行設定
int iStartNum = 0;
int iEndNum = 0;
//件数取得SQL
StringBuffer sql_cnt = new StringBuffer();
sql_cnt.append(" SELECT ")
.append(" COUNT(*) AS CNT ")
.append(sql_From)
.append(sql_Where);
//prepareStatementにSQL文をセット
pstmt = con1.prepareStatement(sql_cnt.toString());
//パラメータ設定用Index 初期化
iIndex = 1;
// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
if ( "1".equals( inMsg.getString(EKU0141B010CBSMsg.FUNC_CODE) ) ) {
// パラメータの設定(ＫＥＹ＿工事案件番号を指定)
CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(EKU0141B010CBSMsg.KEY_KOJIAK_NO));
}
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_cnt);
/**
* SQL文実行
*/
// ResultSetの取得
rsltQuery = pstmt.executeQuery();
// EKU0141B010CBSMsgの明細にセットする項目
String[] msgKeyListCnt = {
EKU0141B010CBSMsg.TOTAL_SEARCH_NUM
};
/**
* 結果取得
*/
rsltQuery.next();
mapMessage(rsltQuery, msgKeyListCnt, inMsg);
// 総ページ数設定
int iTotal = Integer.parseInt(inMsg.getString(EKU0141B010CBSMsg.TOTAL_SEARCH_NUM));
// 部分検索の場合
if(JPCModelConstant.SAERCH_TYPE_BBN.equals(searchType))
{
//パラメータ取得
iDispNum = Integer.parseInt(inMsg.getString(EKU0141B010CBSMsg.DISPLAY_NUM));
iDispPageNum = Integer.parseInt(inMsg.getString(EKU0141B010CBSMsg.DISPLAY_PAGE_NUM));
//開始行、終了行設定
iStartNum = iDispNum * (iDispPageNum - 1) + 1;
iEndNum = iDispNum * iDispPageNum;
/**
* 総ページ数、エラーフラグ設定
*/
int iSearchDivDisp = iTotal / iDispNum;
int iRest = iTotal % iDispNum;
// 割り切れない場合は総ページ数を増やす
if (0 != iRest)
{
iSearchDivDisp++;
}
inMsg.set(EKU0141B010CBSMsg.TOTAL_PAGE_NUM, Integer.toString(iSearchDivDisp));
}
// 一括検索、部分検索の場合
// 検索件数が0件の場合、エラーフラグに"1"を設定して処理終了
if (0 == (Integer.parseInt(inMsg.getString(EKU0141B010CBSMsg.TOTAL_SEARCH_NUM))))
{
inMsg.set(EKU0141B010CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_ZERO);
return;
}
// 検索件数が最大検索件数を超えた場合、エラーフラグに"2"を設定して処理終了
if (Integer.parseInt(inMsg.getString(EKU0141B010CBSMsg.MAX_SEARCH_NUM))
< (Integer.parseInt(inMsg.getString(EKU0141B010CBSMsg.TOTAL_SEARCH_NUM))))
{
inMsg.set(EKU0141B010CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_OVERMAX);
return;
}
// 部分検索の場合
if(JPCModelConstant.SAERCH_TYPE_BBN.equals(searchType))
{
// 表示ページに該当するデータがない場合、エラーフラグに"3"を設定して処理終了
if (iTotal < iStartNum)
//if (Integer.parseInt(inMsg.getString(EKU0141B010CBSMsg.MAX_SEARCH_NUM)) < iStartNum)
{
inMsg.set(EKU0141B010CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_NODATA);
return;
}
}
if(rsltQuery != null){
rsltQuery.close();
}
if(pstmt != null){
pstmt.close();
}
/***********************************************************************************************
* データ取得
*/
sql_Buff.append(" SELECT ")
.append(" TBL.KOJIAK_NO, ")
.append(" TBL.KOJIAK_TRGT_TAKNKIKI_NO, ")
.append(" TBL.KOJIAK_TRGT_TAKNKIKI_STAT, ")
.append(" TBL.KOJIAK_TRGT_TAKNKIKI_STAT_NM, ")
.append(" TBL.KOJIAK_TRGT_KIKI_GRP_NO, ")
.append(" TBL.STC_TEKKYO_FLG, ")
.append(" TBL.STC_TEKKYO_FLG_NM, ")
.append(" TBL.TAKNKIKI_SBT_CD, ")
.append(" TBL.TAKNKIKI_SBT_NM, ")
.append(" TBL.TAKNKIKI_IDO_CD, ")
.append(" TBL.TAKNKIKI_IDO_NM, ")
.append(" TBL.RSV_TAKNKIKI_MODEL_CD, ")
.append(" TBL.RSV_KIKI_SEIZO_NO, ")
.append(" TBL.RSV_MAKER_CD, ")
.append(" TBL.RSV_MAKER_NM, ")
.append(" TBL.RSV_MACAD, ")
.append(" TBL.RSV_STB_ID, ")
.append(" TBL.RSV_HDD_CAPA_CD, ")
.append(" TBL.RSV_HDD_CAPA_NM, ")
.append(" TBL.RSV_VONU_BSPT_KH, ")
.append(" TBL.RSV_VONU_BSPT_KH_NM, ")
.append(" TBL.RSV_STB_KEI_TV_COURSE_CD, ")
.append(" TBL.RSV_STB_KEI_TV_COURSE_NM, ")
.append(" TBL.RSV_ROUTER_KIND_CD, ")
.append(" TBL.RSV_ROUTER_KIND_NM, ")
.append(" TBL.RSV_ROUTER_RENT_CD, ")
.append(" TBL.RSV_ROUTER_RENT_NM, ")
.append(" TBL.RSV_TAKINORT_KINO_CD, ")
.append(" TBL.RSV_TAKINORT_KINO_NM, ")
.append(" TBL.RSV_TAKINORT_RTR_KINO_SKCD, ")
.append(" TBL.RSV_TAKINORT_RTR_KINO_SKBT_NM, ")
.append(" TBL.CHGE_TAKNKIKI_MODEL_CD, ")
.append(" TBL.CHGE_KIKI_SEIZO_NO, ")
.append(" TBL.CHGE_MAKER_CD, ")
.append(" TBL.CHGE_MAKER_NM, ")
.append(" TBL.CHGE_MACAD, ")
.append(" TBL.CHGE_STB_ID, ")
.append(" TBL.CHGE_HDD_CAPA_CD, ")
.append(" TBL.CHGE_HDD_CAPA_NM, ")
.append(" TBL.CHGE_VONU_BSPT_KH, ")
.append(" TBL.CHGE_VONU_BSPT_KH_NM, ")
.append(" TBL.CHGE_STB_KEI_TV_COURSE_CD, ")
.append(" TBL.CHGE_STB_KEI_TV_COURSE_NM, ")
.append(" TBL.CHGE_ROUTER_KIND_CD, ")
.append(" TBL.CHGE_ROUTER_KIND_NM, ")
.append(" TBL.CHGE_ROUTER_RENT_CD, ")
.append(" TBL.CHGE_ROUTER_RENT_NM, ")
.append(" TBL.CHG_TAKINORT_KINO_CD, ")
.append(" TBL.CHG_TAKINORT_KINO_NM, ")
.append(" TBL.CHG_TAKINORT_RTR_KINO_SKCD, ")
.append(" TBL.CHG_TAKINORT_RTR_KINO_SKBT_NM, ")
.append(" TBL.CHGE_SHUKKA_JOKYO_FLG, ")
.append(" TBL.CHGE_SHUKKA_JOKYO_FLG_NM, ")
.append(" TBL.JSEKI_TAKNKIKI_MODEL_CD, ")
.append(" TBL.JSEKI_KIKI_SEIZO_NO, ")
.append(" TBL.JSEKI_MAKER_CD, ")
.append(" TBL.JSEKI_MAKER_NM, ")
.append(" TBL.JSEKI_MACAD, ")
.append(" TBL.JSEKI_STB_ID, ")
.append(" TBL.JSEKI_HDD_CAPA_CD, ")
.append(" TBL.JSEKI_HDD_CAPA_NM, ")
.append(" TBL.JSEKI_VONU_BSPT_KH, ")
.append(" TBL.JSEKI_VONU_BSPT_KH_NM, ")
.append(" TBL.JSEKI_STB_KEI_TV_COURSE_CD, ")
.append(" TBL.JSEKI_STB_KEI_TV_COURSE_NM, ")
.append(" TBL.JSEKI_ROUTER_KIND_CD, ")
.append(" TBL.JSEKI_ROUTER_KIND_NM, ")
.append(" TBL.JSEKI_ROUTER_RENT_CD, ")
.append(" TBL.JSEKI_ROUTER_RENT_NM, ")
.append(" TBL.JSK_TAKINORT_KINO_CD, ")
.append(" TBL.JSK_TAKINORT_KINO_NM, ")
.append(" TBL.JSK_TAKINORT_RTR_KINO_SKCD, ")
.append(" TBL.JSK_TAKINORT_RTR_KINO_SKBT_NM, ")
.append(" TBL.KKTK_SVC_KEI_NO, ")
.append(" TBL.KKTK_SVC_KEI_CHGE_MEMO, ")
.append(" TBL.MOTO_TCASE_NO, ")
.append(" TBL.OPMS_TKKIKI_CHGE_RSN_CD_NM, ")
.append(" TBL.OPMS_TKKIKI_CHGE_RSN_MEMO, ")
.append(" TBL.ADD_DTM, ")
.append(" TBL.ADD_OPEACNT, ")
.append(" TBL.ADD_OPEACNT_NM, ")
.append(" TBL.UPD_DTM, ")
.append(" TBL.UPD_OPEACNT, ")
.append(" TBL.UPD_OPEACNT_NM, ")
.append(" TBL.DEL_DTM, ")
.append(" TBL.DEL_OPEACNT, ")
.append(" TBL.DEL_OPEACNT_NM, ")
.append(" TBL.MK_FLG, ")
.append(" TBL.MK_FLG_NM ")
.append(" FROM ")
.append(" ( ")
.append(sql_Select)
.append(sql_From)
.append(sql_Where)
.append(sql_Order)
.append(" ) TBL ");
if(JPCModelConstant.SAERCH_TYPE_BBN.equals(searchType))
{
sql_Buff.append(" WHERE ");
sql_Buff.append(" TBL.ROW_NUM BETWEEN ? AND ? ");
}
//prepareStatementにSQL文をセット
pstmt = con1.prepareStatement(sql_Buff.toString());
//パラメータ設定用Index 初期化
iIndex = 1;
// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
if ( "1".equals( inMsg.getString(EKU0141B010CBSMsg.FUNC_CODE) ) ) {
// パラメータの設定(ＫＥＹ＿工事案件番号を指定)
CAANJDBCUtil.setParam(pstmt, iIndex++, inMsg.getObject(EKU0141B010CBSMsg.KEY_KOJIAK_NO));
}
if(JPCModelConstant.SAERCH_TYPE_BBN.equals(searchType))
{
//開始レコードの設定
CAANJDBCUtil.setParam(pstmt, iIndex++, iStartNum);
//終了レコードの設定
CAANJDBCUtil.setParam(pstmt, iIndex++, iEndNum);
}
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff);
// ResultSetの取得
rsltQuery = pstmt.executeQuery();
// EKU0141B010CBSMsgの明細にセットする項目
String[] msgKeyList1 = {
EKU0141B010CBSMsg1List.KOJIAK_NO
, EKU0141B010CBSMsg1List.KOJIAK_TRGT_TAKNKIKI_NO
, EKU0141B010CBSMsg1List.KOJIAK_TRGT_TAKNKIKI_STAT
, EKU0141B010CBSMsg1List.KOJIAK_TRGT_TAKNKIKI_STAT_NM
, EKU0141B010CBSMsg1List.KOJIAK_TRGT_KIKI_GRP_NO
, EKU0141B010CBSMsg1List.STC_TEKKYO_FLG
, EKU0141B010CBSMsg1List.STC_TEKKYO_FLG_NM
, EKU0141B010CBSMsg1List.TAKNKIKI_SBT_CD
, EKU0141B010CBSMsg1List.TAKNKIKI_SBT_NM
, EKU0141B010CBSMsg1List.TAKNKIKI_IDO_CD
, EKU0141B010CBSMsg1List.TAKNKIKI_IDO_NM
, EKU0141B010CBSMsg1List.RSV_TAKNKIKI_MODEL_CD
, EKU0141B010CBSMsg1List.RSV_KIKI_SEIZO_NO
, EKU0141B010CBSMsg1List.RSV_MAKER_CD
, EKU0141B010CBSMsg1List.RSV_MAKER_NM
, EKU0141B010CBSMsg1List.RSV_MACAD
, EKU0141B010CBSMsg1List.RSV_STB_ID
, EKU0141B010CBSMsg1List.RSV_HDD_CAPA_CD
, EKU0141B010CBSMsg1List.RSV_HDD_CAPA_NM
, EKU0141B010CBSMsg1List.RSV_VONU_BSPT_KH
, EKU0141B010CBSMsg1List.RSV_VONU_BSPT_KH_NM
, EKU0141B010CBSMsg1List.RSV_STB_KEI_TV_COURSE_CD
, EKU0141B010CBSMsg1List.RSV_STB_KEI_TV_COURSE_NM
, EKU0141B010CBSMsg1List.RSV_ROUTER_KIND_CD
, EKU0141B010CBSMsg1List.RSV_ROUTER_KIND_NM
, EKU0141B010CBSMsg1List.RSV_ROUTER_RENT_CD
, EKU0141B010CBSMsg1List.RSV_ROUTER_RENT_NM
, EKU0141B010CBSMsg1List.RSV_TAKINORT_KINO_CD
, EKU0141B010CBSMsg1List.RSV_TAKINORT_KINO_NM
, EKU0141B010CBSMsg1List.RSV_TAKINORT_RTR_KINO_SKCD
, EKU0141B010CBSMsg1List.RSV_TAKINORT_RTR_KINO_SKBT_NM
, EKU0141B010CBSMsg1List.CHGE_TAKNKIKI_MODEL_CD
, EKU0141B010CBSMsg1List.CHGE_KIKI_SEIZO_NO
, EKU0141B010CBSMsg1List.CHGE_MAKER_CD
, EKU0141B010CBSMsg1List.CHGE_MAKER_NM
, EKU0141B010CBSMsg1List.CHGE_MACAD
, EKU0141B010CBSMsg1List.CHGE_STB_ID
, EKU0141B010CBSMsg1List.CHGE_HDD_CAPA_CD
, EKU0141B010CBSMsg1List.CHGE_HDD_CAPA_NM
, EKU0141B010CBSMsg1List.CHGE_VONU_BSPT_KH
, EKU0141B010CBSMsg1List.CHGE_VONU_BSPT_KH_NM
, EKU0141B010CBSMsg1List.CHGE_STB_KEI_TV_COURSE_CD
, EKU0141B010CBSMsg1List.CHGE_STB_KEI_TV_COURSE_NM
, EKU0141B010CBSMsg1List.CHGE_ROUTER_KIND_CD
, EKU0141B010CBSMsg1List.CHGE_ROUTER_KIND_NM
, EKU0141B010CBSMsg1List.CHGE_ROUTER_RENT_CD
, EKU0141B010CBSMsg1List.CHGE_ROUTER_RENT_NM
, EKU0141B010CBSMsg1List.CHG_TAKINORT_KINO_CD
, EKU0141B010CBSMsg1List.CHG_TAKINORT_KINO_NM
, EKU0141B010CBSMsg1List.CHG_TAKINORT_RTR_KINO_SKCD
, EKU0141B010CBSMsg1List.CHG_TAKINORT_RTR_KINO_SKBT_NM
, EKU0141B010CBSMsg1List.CHGE_SHUKKA_JOKYO_FLG
, EKU0141B010CBSMsg1List.CHGE_SHUKKA_JOKYO_FLG_NM
, EKU0141B010CBSMsg1List.JSEKI_TAKNKIKI_MODEL_CD
, EKU0141B010CBSMsg1List.JSEKI_KIKI_SEIZO_NO
, EKU0141B010CBSMsg1List.JSEKI_MAKER_CD
, EKU0141B010CBSMsg1List.JSEKI_MAKER_NM
, EKU0141B010CBSMsg1List.JSEKI_MACAD
, EKU0141B010CBSMsg1List.JSEKI_STB_ID
, EKU0141B010CBSMsg1List.JSEKI_HDD_CAPA_CD
, EKU0141B010CBSMsg1List.JSEKI_HDD_CAPA_NM
, EKU0141B010CBSMsg1List.JSEKI_VONU_BSPT_KH
, EKU0141B010CBSMsg1List.JSEKI_VONU_BSPT_KH_NM
, EKU0141B010CBSMsg1List.JSEKI_STB_KEI_TV_COURSE_CD
, EKU0141B010CBSMsg1List.JSEKI_STB_KEI_TV_COURSE_NM
, EKU0141B010CBSMsg1List.JSEKI_ROUTER_KIND_CD
, EKU0141B010CBSMsg1List.JSEKI_ROUTER_KIND_NM
, EKU0141B010CBSMsg1List.JSEKI_ROUTER_RENT_CD
, EKU0141B010CBSMsg1List.JSEKI_ROUTER_RENT_NM
, EKU0141B010CBSMsg1List.JSK_TAKINORT_KINO_CD
, EKU0141B010CBSMsg1List.JSK_TAKINORT_KINO_NM
, EKU0141B010CBSMsg1List.JSK_TAKINORT_RTR_KINO_SKCD
, EKU0141B010CBSMsg1List.JSK_TAKINORT_RTR_KINO_SKBT_NM
, EKU0141B010CBSMsg1List.KKTK_SVC_KEI_NO
, EKU0141B010CBSMsg1List.KKTK_SVC_KEI_CHGE_MEMO
, EKU0141B010CBSMsg1List.MOTO_TCASE_NO
, EKU0141B010CBSMsg1List.OPMS_TKKIKI_CHGE_RSN_CD_NM
, EKU0141B010CBSMsg1List.OPMS_TKKIKI_CHGE_RSN_MEMO
, EKU0141B010CBSMsg1List.ADD_DTM
, EKU0141B010CBSMsg1List.ADD_OPEACNT
, EKU0141B010CBSMsg1List.ADD_OPEACNT_NM
, EKU0141B010CBSMsg1List.UPD_DTM
, EKU0141B010CBSMsg1List.UPD_OPEACNT
, EKU0141B010CBSMsg1List.UPD_OPEACNT_NM
, EKU0141B010CBSMsg1List.DEL_DTM
, EKU0141B010CBSMsg1List.DEL_OPEACNT
, EKU0141B010CBSMsg1List.DEL_OPEACNT_NM
, EKU0141B010CBSMsg1List.MK_FLG
, EKU0141B010CBSMsg1List.MK_FLG_NM
};
// 明細に処理結果(ResultSet)をマッピング
CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKU0141B010CBSMsg1List",0);
// 検索エラーフラグの設定
// 一括検索の場合
if (JPCModelConstant.SAERCH_TYPE_IKT.equals(searchType))
{
// 検索件数が0件の場合、エラーフラグに"1"を設定して処理終了
if (0 == outMsg1.length)
{
inMsg.set(EKU0141B010CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_ZERO);
return;
}
// 検索件数が最大検索件数を超えた場合、エラーフラグに"2"を設定して処理終了
if (Integer.parseInt(inMsg.getString(EKU0141B010CBSMsg.MAX_SEARCH_NUM))
< (outMsg1.length))
{
inMsg.set(EKU0141B010CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_OVERMAX);
return;
}
}
// 部分検索の場合
if(JPCModelConstant.SAERCH_TYPE_BBN.equals(searchType))
{
// 表示ページに該当するデータがない場合、エラーフラグに"3"を設定して処理終了
if (outMsg1.length == 0)
{
inMsg.set(EKU0141B010CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_NODATA);
return;
}
}
// 検索エラーフラグの設定
// 一括検索の場合
if (JPCModelConstant.SAERCH_TYPE_IKT.equals(searchType))
{
//総検索件数を再設定
inMsg.set(EKU0141B010CBSMsg.TOTAL_SEARCH_NUM, Integer.toString(outMsg1.length));
}
// inMsg(CBSMsg)に明細をセット
inMsg.set("EKU0141B010CBSMsg1List", outMsg1);
} catch(SQLException e) {
inMsg.set(EKU0141B010CBSMsg.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(EKU0141B010CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
}
}
}
}