/*******************************************************************************
* All Rights reserved,Copyright (c) K-Opticom
********************************************************************************
*＜プログラム内容＞
* システム名 ：eo顧客基幹システム
* モジュール名 ：JSYejbEKK0341B015TPDA
* ソースファイル名：JSYejbEKK0341B015TPDA.java
* 作成者 ：EKek0043
* 日付 ：2011年07月28日
*＜機能概要＞
* SQLFacility
* テンプレートDBアクセス部品です。
*＜修正履歴＞
* バージョン 修正日 修正者 修正内容
* ｖ1.00.00
* ｖ4.00.00 2013/02/04 FJ）藤本 ST3-2012-0000483
* ｖ4.00.01 2013/02/12 FJ）寺園 IT1-2013-0000256
* ｖ5.00.00 2013/11/18 FJ）稲岡 OM-2013-0004260
* ｖ7.00.00 2014/01/30 FJ）稲岡 OM-2014-0000015
* ｖ7.00.01 2014/06/12 FJ）古内 OM-2013-0002365
********************************************************************************/
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.EKK0341B015CBSMsg;
import eo.ejb.cbs.cbsmsg.EKK0341B015CBSMsg1List;
import eo.ejb.cbm.entity.KK0341ETMsg;
import eo.ejb.common.JKKModelCommon;
import eo.ejb.common.JKKejbPagingUtil;
/**
*
* テンプレートDBアクセス部品 EKK0341B015_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/
public class JSYejbEKK0341B015TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
/**
* コンストラクタ
*/
public JSYejbEKK0341B015TPDA() {
}
/**
* EKK0341B015_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void
*/
public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){
// コネクション
Connection con1 = null;
// プリペアステートメント
PreparedStatement pstmt_Count = null;
PreparedStatement pstmt_Page = null;
// リザルトセット
ResultSet rsltQuery_Count = null;
ResultSet rsltQuery_Page = null;
// パラメータのカウント
int iPCnt = 0;
/********************
* SQL文の作成
********************/
try{
//コネクション取得
con1 = JSYejbConnection.getConnection(KK0341ETMsg.getTableName());
// SQL文の作成(ページングヘッダ)
// (1)
StringBuffer sql_Buff1 = new StringBuffer();
sql_Buff1
.append(" SELECT * ")
.append(" FROM ( SELECT ALL_SELECT.*, ROWNUM AS LINE ")
.append(" FROM ( ");
// SQL文の作成(COUNT用)
// (2)
StringBuffer sql_Buff2 = new StringBuffer();
sql_Buff2
.append(" SELECT COUNT(*) AS CNT ")
.append(" FROM ");
// SQL文の作成(項目取得用)
// (3)
StringBuffer sql_Buff3 = new StringBuffer();
sql_Buff3
.append(" SELECT ")
.append(" STB1.KOJIAK_NO ")
.append(" , STB1.SVC_KEI_NO AS SVC_KEI_NO_1 ")
.append(" , STB2.SVC_KEI_NO AS SVC_KEI_NO_2 ")
.append(" , STB3.SVC_KEI_NO AS SVC_KEI_NO_3 ")
.append(" , STB4.SVC_KEI_NO AS SVC_KEI_NO_4 ")
.append(" , STB5.SVC_KEI_NO AS SVC_KEI_NO_5 ")
.append(" , STB1.SVC_KEI_UCWK_NO AS SVC_KEI_UCWK_NO_1 ")
.append(" , STB2.SVC_KEI_UCWK_NO AS SVC_KEI_UCWK_NO_2 ")
.append(" , STB3.SVC_KEI_UCWK_NO AS SVC_KEI_UCWK_NO_3 ")
.append(" , STB4.SVC_KEI_UCWK_NO AS SVC_KEI_UCWK_NO_4 ")
.append(" , STB5.SVC_KEI_UCWK_NO AS SVC_KEI_UCWK_NO_5 ")
.append(" , STB1.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_1_STB ")
.append(" , STB2.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_2_STB ")
.append(" , STB3.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_3_STB ")
.append(" , STB4.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_4_STB ")
.append(" , STB5.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_5_STB ")
.append(" , BCAS1.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_1_BCAS ")
.append(" , BCAS2.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_2_BCAS ")
.append(" , BCAS3.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_3_BCAS ")
.append(" , BCAS4.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_4_BCAS ")
.append(" , BCAS5.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_5_BCAS ")
.append(" , CCAS1.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_1_CCAS ")
.append(" , CCAS2.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_2_CCAS ")
.append(" , CCAS3.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_3_CCAS ")
.append(" , CCAS4.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_4_CCAS ")
.append(" , CCAS5.TAKNKIKI_MODEL_CD AS TAKNKIKI_MODEL_CD_5_CCAS ")
.append(" , STB1.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_1_STB ")
.append(" , STB2.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_2_STB ")
.append(" , STB3.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_3_STB ")
.append(" , STB4.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_4_STB ")
.append(" , STB5.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_5_STB ")
.append(" , BCAS1.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_1_BCAS ")
.append(" , BCAS2.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_2_BCAS ")
.append(" , BCAS3.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_3_BCAS ")
.append(" , BCAS4.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_4_BCAS ")
.append(" , BCAS5.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_5_BCAS ")
.append(" , CCAS1.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_1_CCAS ")
.append(" , CCAS2.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_2_CCAS ")
.append(" , CCAS3.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_3_CCAS ")
.append(" , CCAS4.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_4_CCAS ")
.append(" , CCAS5.KIKI_SEIZO_NO AS KIKI_SEIZO_NO_5_CCAS ")
.append(" , STB1.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_1_STB ")
.append(" , STB2.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_2_STB ")
.append(" , STB3.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_3_STB ")
.append(" , STB4.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_4_STB ")
.append(" , STB5.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_5_STB ")
.append(" , BCAS1.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_1_BCAS ")
.append(" , BCAS2.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_2_BCAS ")
.append(" , BCAS3.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_3_BCAS ")
.append(" , BCAS4.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_4_BCAS ")
.append(" , BCAS5.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_5_BCAS ")
.append(" , CCAS1.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_1_CCAS ")
.append(" , CCAS2.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_2_CCAS ")
.append(" , CCAS3.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_3_CCAS ")
.append(" , CCAS4.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_4_CCAS ")
.append(" , CCAS5.EO_TV_KKST_SNN_STAT_CD AS EO_TV_KKST_SNN_STAT_CD_5_CCAS ")
.append(" , STB1.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM1_STB ")
.append(" , STB2.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM2_STB ")
.append(" , STB3.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM3_STB ")
.append(" , STB4.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM4_STB ")
.append(" , STB5.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM5_STB ")
.append(" , BCAS1.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM1_BCAS ")
.append(" , BCAS2.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM2_BCAS ")
.append(" , BCAS3.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM3_BCAS ")
.append(" , BCAS4.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM4_BCAS ")
.append(" , BCAS5.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM5_BCAS ")
.append(" , CCAS1.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM1_CCAS ")
.append(" , CCAS2.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM2_CCAS ")
.append(" , CCAS3.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM3_CCAS ")
.append(" , CCAS4.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM4_CCAS ")
.append(" , CCAS5.EO_TV_KKST_SNN_STAT_CD_NM AS EO_TV_KKST_SNN_STAT_CDNM5_CCAS ")
.append(" , STB1.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_1_STB ")
.append(" , STB2.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_2_STB ")
.append(" , STB3.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_3_STB ")
.append(" , STB4.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_4_STB ")
.append(" , STB5.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_5_STB ")
.append(" , BCAS1.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_1_BCAS ")
.append(" , BCAS2.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_2_BCAS ")
.append(" , BCAS3.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_3_BCAS ")
.append(" , BCAS4.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_4_BCAS ")
.append(" , BCAS5.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_5_BCAS ")
.append(" , CCAS1.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_1_CCAS ")
.append(" , CCAS2.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_2_CCAS ")
.append(" , CCAS3.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_3_CCAS ")
.append(" , CCAS4.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_4_CCAS ")
.append(" , CCAS5.KKST_JSEKI_UK_YMD AS KKST_JSEKI_UK_YMD_5_CCAS ")
.append(" FROM ");
// SQL文の作成(FROM句、WHERE句)
// (4)
StringBuffer sql_Buff4 = new StringBuffer();
StringBuffer sql_Buff4_1 = new StringBuffer();
StringBuffer sql_Buff4_2 = new StringBuffer();
sql_Buff4_1
.append(" ( ")
.append(" SELECT ")
.append(" KU0141.KOJIAK_NO ")
// ++++++++++++++++++++++++++ ｖ7.00.01 変更開始 ++++++++++++++++++++++++++
// サービス契約内訳でなく、機器提供サービス契約の情報を利用する。
// .append(" , KK0161.SVC_KEI_NO ")
// .append(" , KK0161.SVC_KEI_UCWK_NO ")
.append(" , KK0341.SVC_KEI_NO ")
.append(" , KK0341.SVC_KEI_UCWK_NO ")
// ++++++++++++++++++++++++++ ｖ7.00.01 変更終了 ++++++++++++++++++++++++++
.append(" , KK0341.TAKNKIKI_MODEL_CD ")
.append(" , KK0341.KIKI_SEIZO_NO ")
.append(" , KK0341.EO_TV_KKST_SNN_STAT_CD ")
.append(" , (SELECT ZM0171_01.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_01 ")
.append(" WHERE ZM0171_01.CD_SBT_CD = 'CD00644' ")
.append(" AND ZM0171_01.CD_DIV = KK0341.EO_TV_KKST_SNN_STAT_CD ")
.append(" AND ZM0171_01.MK_FLG = '0') AS EO_TV_KKST_SNN_STAT_CD_NM ")
.append(" , KK0341.KKST_JSEKI_UK_YMD ");
// 機能コード１の場合
if ("1".equals( inMsg.getString(EKK0341B015CBSMsg.FUNC_CODE)))
{
sql_Buff4_1
// ++++++++++++++++++++++++++ ｖ7.00.01 変更開始 ++++++++++++++++++++++++++
// サービス契約内訳でなく、機器提供サービス契約の情報を利用する。
// .append(" , ROW_NUMBER() OVER (PARTITION BY KU0141.KOJIAK_NO ORDER BY KU0141.KOJIAK_NO ASC, KK0161.SVC_KEI_NO ASC, ")
// .append(" KK0161.SVC_KEI_UCWK_NO ASC, KK0341.KKTK_SVC_KEI_NO ASC) AS LINE ");
.append(" , ROW_NUMBER() OVER (PARTITION BY KU0141.KOJIAK_NO ORDER BY KU0141.KOJIAK_NO ASC, KK0341.SVC_KEI_NO ASC, ")
.append(" KK0341.SVC_KEI_UCWK_NO ASC, KK0341.KKTK_SVC_KEI_NO ASC) AS LINE ");
// ++++++++++++++++++++++++++ ｖ7.00.01 変更終了 ++++++++++++++++++++++++++
}
else
{
sql_Buff4_1
.append(" , ROW_NUMBER() OVER (PARTITION BY KU0141.KOJIAK_NO ORDER BY KU0141.KOJIAK_NO ASC, KK0341.EO_TV_KKST_SNN_STAT_CD, ")
// ++++++++++++++++++++++++++ ｖ7.00.01 変更開始 ++++++++++++++++++++++++++
// サービス契約内訳でなく、機器提供サービス契約の情報を利用する。
// .append(" KK0161.SVC_KEI_UCWK_NO ASC) AS LINE ");
.append(" KK0341.SVC_KEI_UCWK_NO ASC) AS LINE ");
// ++++++++++++++++++++++++++ ｖ7.00.01 変更終了 ++++++++++++++++++++++++++
}
sql_Buff4_1
.append(" FROM ")
.append(" KK_T_KKTK_SVC_KEI KK0341 ")
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// .append(" INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ")
// .append(" ON KK0341.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ")
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
.append(" INNER JOIN KU_T_KJAK_TG_TKKIKI KU0141 ")
.append(" ON KK0341.KKTK_SVC_KEI_NO = KU0141.KKTK_SVC_KEI_NO ")
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
///* ++++++++++ v7.00.00 追加開始 ++++++++++ */
// .append(" INNER JOIN KU_T_KOJIAK KU0011 ")
// .append(" ON KU0011.KOJIAK_NO = KU0141.KOJIAK_NO ")
///* ++++++++++ v7.00.00 追加終了 ++++++++++ */
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
.append(" WHERE ")
.append(" (KK0341.KKTK_SVC_KEI_NO, NVL(KK0341.RSV_APLY_YMD, '20991231') || KK0341.GENE_ADD_DTM) = ")
.append(" (SELECT KK0341_GENE.KKTK_SVC_KEI_NO, MAX(NVL(KK0341_GENE.RSV_APLY_YMD, '20991231') || 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.RSV_APLY_CD IN ('1', '2') ")
.append(" AND KK0341_GENE.MK_FLG= '0' ")
.append(" GROUP BY KK0341_GENE.KKTK_SVC_KEI_NO) ")
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// .append(" AND (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ")
// .append(" (SELECT KK0161_GENE.SVC_KEI_UCWK_NO,MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX ")
// .append(" FROM KK_T_SVC_KEI_UCWK KK0161_GENE ")
// .append(" WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ")
// .append(" AND KK0161_GENE.RSV_APLY_YMD <= ? ")
// .append(" AND KK0161_GENE.RSV_APLY_CD = '2' ")
// .append(" AND KK0161_GENE.MK_FLG= '0' ")
// .append(" GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO) ")
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// ++++++++++++++++++++++++++ ｖ7.00.01 変更開始 ++++++++++++++++++++++++++
///* ++++++++++ v7.00.00 追加開始 ++++++++++ */
// .append(" AND KU0011.KOJIAK_STAT NOT IN ('200', '900') ")
// .append(" AND KU0011.KOJIAK_SBT_CD IN ('001', '003', '008', '012', '013') ")
// .append(" AND KU0011.MK_FLG = '0' ");
///* ++++++++++ v7.00.00 追加終了 ++++++++++ */
.append(" AND EXISTS(SELECT 1 ")
.append(" FROM KU_T_KOJIAK KU0011 ")
.append(" WHERE KU0011.KOJIAK_NO = KU0141.KOJIAK_NO ")
.append(" AND KU0011.KOJIAK_STAT BETWEEN '110' AND '190' ")
.append(" AND KU0011.KOJIAK_SBT_CD IN ('001', '003', '008', '012', '013') ")
.append(" AND KU0011.MK_FLG = '0' ) ");
// ++++++++++++++++++++++++++ ｖ7.00.01 変更終了 ++++++++++++++++++++++++++
//検索条件を追加（KEY_KKST_JSEKI_UK_YMD_FROM）
if (!inMsg.isNull(EKK0341B015CBSMsg.KEY_KKST_JSEKI_UK_YMD_FROM))
{
sql_Buff4_1.append(" AND KK0341.KKST_JSEKI_UK_YMD >= ?");
}
//検索条件を追加（KEY_KKST_JSEKI_UK_YMD_TO）
if (!inMsg.isNull(EKK0341B015CBSMsg.KEY_KKST_JSEKI_UK_YMD_TO))
{
sql_Buff4_1.append(" AND KK0341.KKST_JSEKI_UK_YMD <= ?");
}
//検索条件を追加（KEY_EO_TV_KKST_SNN_STAT_CD）
if (!inMsg.isNull(EKK0341B015CBSMsg.KEY_EO_TV_KKST_SNN_STAT_CD))
{
// ++++++++++++++++++++++++++ ｖ7.00.01 変更開始 ++++++++++++++++++++++++++
// sql_Buff4_1.append(" AND KK0341.EO_TV_KKST_SNN_STAT_CD = ?");
sql_Buff4_1.append(" AND KK0341.EO_TV_KKST_SNN_STAT_CD = '").append(inMsg.getString(EKK0341B015CBSMsg.KEY_EO_TV_KKST_SNN_STAT_CD)).append("'");
// ++++++++++++++++++++++++++ ｖ7.00.01 変更終了 ++++++++++++++++++++++++++
}
else
{
sql_Buff4_1.append(" AND KK0341.EO_TV_KKST_SNN_STAT_CD IN ('0', '1')");
}
//検索条件を追加（KEY_KOJIAK_NO）
if (!inMsg.isNull(EKK0341B015CBSMsg.KEY_KOJIAK_NO))
{
sql_Buff4_1.append(" AND KU0141.KOJIAK_NO = ?");
}
//検索条件を追加（KEY_SVC_KEI_NO）
if (!inMsg.isNull(EKK0341B015CBSMsg.KEY_SVC_KEI_NO))
{
// ++++++++++++++++++++++++++ ｖ7.00.01 変更開始 ++++++++++++++++++++++++++
// サービス契約内訳でなく、機器提供サービス契約の情報を利用する。
// sql_Buff4_1.append(" AND KK0161.SVC_KEI_NO = ?");
sql_Buff4_1.append(" AND KK0341.SVC_KEI_NO = ?");
// ++++++++++++++++++++++++++ ｖ7.00.01 変更終了 ++++++++++++++++++++++++++
}
sql_Buff4_1
.append(" AND KU0141.MK_FLG= '0' ")
.append(" AND KK0341.KKTK_SVC_CD = 'C009' ")
// ++++++++++++++++++++++++++ ｖ7.00.01 変更開始 ++++++++++++++++++++++++++
// SQL規約に従い、NOT IN を INに変更
// インデックスを効かせるために、IS NOT NULL をやめ、 >= '00000000'に変更
// .append(" AND KK0341.KKTK_SVC_KEI_STAT NOT IN ('910', '920') ")
// .append(" AND KK0341.KKST_JSEKI_UK_YMD IS NOT NULL ");
.append(" AND KK0341.KKTK_SVC_KEI_STAT IN ('010', '020', '030', '100', '110', '210', '220') ")
.append(" AND KK0341.KKST_JSEKI_UK_YMD >= '00000000' ");
// ++++++++++++++++++++++++++ ｖ7.00.01 変更終了 ++++++++++++++++++++++++++
sql_Buff4
.append(sql_Buff4_1)
.append(" ) STB1 ")
.append(" LEFT OUTER JOIN ")
.append(sql_Buff4_1)
.append(" ) STB2 ")
.append(" ON STB2.KOJIAK_NO = STB1.KOJIAK_NO ")
.append(" AND STB2.LINE = 2 ")
.append(" LEFT OUTER JOIN ")
.append(sql_Buff4_1)
.append(" ) STB3 ")
.append(" ON STB3.KOJIAK_NO = STB1.KOJIAK_NO ")
.append(" AND STB3.LINE = 3 ")
.append(" LEFT OUTER JOIN ")
.append(sql_Buff4_1)
.append(" ) STB4 ")
.append(" ON STB4.KOJIAK_NO = STB1.KOJIAK_NO ")
.append(" AND STB4.LINE = 4 ")
.append(" LEFT OUTER JOIN ")
.append(sql_Buff4_1)
.append(" ) STB5 ")
.append(" ON STB5.KOJIAK_NO = STB1.KOJIAK_NO ")
.append(" AND STB5.LINE = 5 ");
sql_Buff4_2
.append(" LEFT OUTER JOIN ")
.append(" ( ")
.append(" SELECT ")
// ++++++++++++++++++++++++++ ｖ7.00.01 変更開始 ++++++++++++++++++++++++++
// .append(" KK0161.SVC_KEI_UCWK_NO ")
.append(" KK0341.SVC_KEI_UCWK_NO ")
// ++++++++++++++++++++++++++ ｖ7.00.01 変更終了 ++++++++++++++++++++++++++
.append(" , KK0341.TAKNKIKI_MODEL_CD ")
.append(" , KK0341.KIKI_SEIZO_NO ")
.append(" , KK0341.EO_TV_KKST_SNN_STAT_CD ")
.append(" , (SELECT ZM0171_01.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_01 ")
.append(" WHERE ZM0171_01.CD_SBT_CD = 'CD00644' ")
.append(" AND ZM0171_01.CD_DIV = KK0341.EO_TV_KKST_SNN_STAT_CD ")
.append(" AND ZM0171_01.MK_FLG = '0') AS EO_TV_KKST_SNN_STAT_CD_NM ")
.append(" , KK0341.KKST_JSEKI_UK_YMD ")
.append(" FROM ")
.append(" KK_T_KKTK_SVC_KEI KK0341 ")
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// .append(" INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ")
// .append(" ON KK0341.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ")
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
.append(" WHERE ")
.append(" (KK0341.KKTK_SVC_KEI_NO, NVL(KK0341.RSV_APLY_YMD, '20991231') || KK0341.GENE_ADD_DTM) = ")
.append(" (SELECT KK0341_GENE.KKTK_SVC_KEI_NO, MAX(NVL(KK0341_GENE.RSV_APLY_YMD, '20991231') || 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.RSV_APLY_CD IN ('1', '2') ")
.append(" AND KK0341_GENE.MK_FLG= '0' ")
.append(" GROUP BY KK0341_GENE.KKTK_SVC_KEI_NO) ")
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// .append(" AND (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ")
// .append(" (SELECT KK0161_GENE.SVC_KEI_UCWK_NO,MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX ")
// .append(" FROM KK_T_SVC_KEI_UCWK KK0161_GENE ")
// .append(" WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ")
// .append(" AND KK0161_GENE.RSV_APLY_YMD <= ? ")
// .append(" AND KK0161_GENE.RSV_APLY_CD = '2' ")
// .append(" AND KK0161_GENE.MK_FLG= '0' ")
// .append(" GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO) ")
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// ++++++++++++++++++++++++++ ｖ7.00.01 変更開始 ++++++++++++++++++++++++++
// .append(" AND KK0341.KKST_JSEKI_UK_YMD IS NOT NULL ");
.append(" AND KK0341.KKST_JSEKI_UK_YMD >= '00000000' ");
// ++++++++++++++++++++++++++ ｖ7.00.01 変更終了 ++++++++++++++++++++++++++
sql_Buff4
.append(sql_Buff4_2)
.append(" AND KK0341.KKTK_SVC_CD = 'C010' ")
.append(" ) BCAS1 ")
.append(" ON BCAS1.SVC_KEI_UCWK_NO = STB1.SVC_KEI_UCWK_NO ");
sql_Buff4
.append(sql_Buff4_2)
.append(" AND KK0341.KKTK_SVC_CD = 'C010' ")
.append(" ) BCAS2 ")
.append(" ON BCAS2.SVC_KEI_UCWK_NO = STB2.SVC_KEI_UCWK_NO ");
sql_Buff4
.append(sql_Buff4_2)
.append(" AND KK0341.KKTK_SVC_CD = 'C010' ")
.append(" ) BCAS3 ")
.append(" ON BCAS3.SVC_KEI_UCWK_NO = STB3.SVC_KEI_UCWK_NO ");
sql_Buff4
.append(sql_Buff4_2)
.append(" AND KK0341.KKTK_SVC_CD = 'C010' ")
.append(" ) BCAS4 ")
.append(" ON BCAS4.SVC_KEI_UCWK_NO = STB4.SVC_KEI_UCWK_NO ");
sql_Buff4
.append(sql_Buff4_2)
.append(" AND KK0341.KKTK_SVC_CD = 'C010' ")
.append(" ) BCAS5 ")
.append(" ON BCAS5.SVC_KEI_UCWK_NO = STB5.SVC_KEI_UCWK_NO ");
sql_Buff4
.append(sql_Buff4_2)
.append(" AND KK0341.KKTK_SVC_CD = 'C011' ")
.append(" ) CCAS1 ")
.append(" ON CCAS1.SVC_KEI_UCWK_NO = STB1.SVC_KEI_UCWK_NO ");
sql_Buff4
.append(sql_Buff4_2)
.append(" AND KK0341.KKTK_SVC_CD = 'C011' ")
.append(" ) CCAS2 ")
.append(" ON CCAS2.SVC_KEI_UCWK_NO = STB2.SVC_KEI_UCWK_NO ");
sql_Buff4
.append(sql_Buff4_2)
.append(" AND KK0341.KKTK_SVC_CD = 'C011' ")
.append(" ) CCAS3 ")
.append(" ON CCAS3.SVC_KEI_UCWK_NO = STB3.SVC_KEI_UCWK_NO ");
sql_Buff4
.append(sql_Buff4_2)
.append(" AND KK0341.KKTK_SVC_CD = 'C011' ")
.append(" ) CCAS4 ")
.append(" ON CCAS4.SVC_KEI_UCWK_NO = STB4.SVC_KEI_UCWK_NO ");
sql_Buff4
.append(sql_Buff4_2)
.append(" AND KK0341.KKTK_SVC_CD = 'C011' ")
.append(" ) CCAS5 ")
.append(" ON CCAS5.SVC_KEI_UCWK_NO = STB5.SVC_KEI_UCWK_NO ")
.append(" WHERE ")
.append(" STB1.LINE = 1 ");
// SQL文の作成(ORDER BY句 機能コード１)
// (5)
StringBuffer sql_Buff5_01 = new StringBuffer();
sql_Buff5_01.append(" ORDER BY ")
.append(" STB1.KOJIAK_NO ASC");
// SQL文の作成(ORDER BY句 機能コード２)
// (5)
StringBuffer sql_Buff5_02 = new StringBuffer();
sql_Buff5_02.append(" ORDER BY ")
.append(" STB1.EO_TV_KKST_SNN_STAT_CD ASC")
.append(" , STB1.KOJIAK_NO ASC");
// SQL文の作成(ページングフッタ)
// (6)
StringBuffer sql_Buff6 = new StringBuffer();
sql_Buff6
.append(" ) ALL_SELECT ")
.append(" ) PAGE_SELECT ")
.append("WHERE PAGE_SELECT.LINE BETWEEN ? AND ? ")
.append("ORDER BY PAGE_SELECT.LINE ASC ");
// 最大件数判定処理（機能コード１のみ）
if ( "1".equals( inMsg.getString(EKK0341B015CBSMsg.FUNC_CODE) ) ) {
// SQL文の組立て(件数取得用)
// (2) + (4)
StringBuffer sql_Count = new StringBuffer();
sql_Count
.append(sql_Buff2)
.append(sql_Buff4);
//prepareStatementにSQL文をセット
pstmt_Count = con1.prepareStatement(sql_Count.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Count);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Count, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Count, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Count, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Count, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Count, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// ResultSetの取得
rsltQuery_Count = pstmt_Count.executeQuery();
// 総検索件数を取得する
long lDataCnt = 0L;
if (rsltQuery_Count.next())
{
lDataCnt = rsltQuery_Count.getLong("CNT");
}
// 表示件数
long lDispNum = inMsg.getStringAsLong(EKK0341B015CBSMsg.DISPLAY_NUM);
// 表示ページ数
long lDispPage = inMsg.getStringAsLong(EKK0341B015CBSMsg.DISPLAY_PAGE_NUM);
// 最大取得件数
long lMaxNum = inMsg.getStringAsLong(EKK0341B015CBSMsg.MAX_SEARCH_NUM);
// 総ページ数
String strMaxPage = JKKejbPagingUtil.calcTotalPage(lDataCnt, lDispNum);
// 共通部に値を設定する
inMsg.set(EKK0341B015CBSMsg.TOTAL_SEARCH_NUM, String.valueOf(lDataCnt)); // 総検索件数
inMsg.set(EKK0341B015CBSMsg.TOTAL_PAGE_NUM, strMaxPage); // 総ページ数
// データ取得件数が0件の場合エラー
if (0L == lDataCnt)
{
inMsg.set(EKK0341B015CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_ZERO);
return;
}
// 最大取得件数を超えた場合エラー
if (lDataCnt > lMaxNum)
{
inMsg.set(EKK0341B015CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_OVERMAX);
return;
}
// SQL文の組立て(項目取得・ページング用)
// (1) + (3) + (4) + (5) + (6)
StringBuffer sql_Page = new StringBuffer();
sql_Page
.append(sql_Buff1)
.append(sql_Buff3)
.append(sql_Buff4)
.append(sql_Buff5_01)
.append(sql_Buff6);
//prepareStatementにSQL文をセット
pstmt_Page = con1.prepareStatement(sql_Page.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Page);
// 検索対象行を算出する
long[] lPages = JKKejbPagingUtil.calcSearchRows(lDispPage, lDispNum);
long lRowSta = lPages[0];
long lRowEnd = lPages[1];
// パラメータのカウントを初期化
iPCnt = 0;
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Page, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Page, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Page, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Page, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Page, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定(検索開始行を指定)
CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, lRowSta);
// パラメータの設定(検索終了行を指定)
CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, lRowEnd);
}
// 照会処理（機能コード２）
if ( "2".equals( inMsg.getString(EKK0341B015CBSMsg.FUNC_CODE) ) ) {
// SQL文の組立て(項目取得)
// (3) + (4) + (5)
StringBuffer sql_Page = new StringBuffer();
sql_Page
.append(sql_Buff3)
.append(sql_Buff4)
.append(sql_Buff5_02);
//prepareStatementにSQL文をセット
pstmt_Page = con1.prepareStatement(sql_Page.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Page);
// パラメータのカウントを初期化
iPCnt = 0;
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Page, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Page, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Page, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Page, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Count, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定
iPCnt = setPram(inMsg, pstmt_Page, iPCnt);
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(運用日付を設定)
// CAANJDBCUtil.setParam(pstmt_Page, ++iPCnt, JKKModelCommon.getOpeDate(inMsg));
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
}
// ResultSetの取得
rsltQuery_Page = pstmt_Page.executeQuery();
// EKK0341B015CBSMsgの明細にセットする項目
String[] msgKeyList1 = {
EKK0341B015CBSMsg1List.KOJIAK_NO
, EKK0341B015CBSMsg1List.SVC_KEI_NO_1
, EKK0341B015CBSMsg1List.SVC_KEI_NO_2
, EKK0341B015CBSMsg1List.SVC_KEI_NO_3
, EKK0341B015CBSMsg1List.SVC_KEI_NO_4
, EKK0341B015CBSMsg1List.SVC_KEI_NO_5
, EKK0341B015CBSMsg1List.SVC_KEI_UCWK_NO_1
, EKK0341B015CBSMsg1List.SVC_KEI_UCWK_NO_2
, EKK0341B015CBSMsg1List.SVC_KEI_UCWK_NO_3
, EKK0341B015CBSMsg1List.SVC_KEI_UCWK_NO_4
, EKK0341B015CBSMsg1List.SVC_KEI_UCWK_NO_5
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_1_STB
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_2_STB
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_3_STB
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_4_STB
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_5_STB
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_1_BCAS
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_2_BCAS
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_3_BCAS
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_4_BCAS
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_5_BCAS
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_1_CCAS
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_2_CCAS
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_3_CCAS
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_4_CCAS
, EKK0341B015CBSMsg1List.TAKNKIKI_MODEL_CD_5_CCAS
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_1_STB
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_2_STB
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_3_STB
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_4_STB
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_5_STB
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_1_BCAS
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_2_BCAS
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_3_BCAS
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_4_BCAS
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_5_BCAS
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_1_CCAS
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_2_CCAS
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_3_CCAS
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_4_CCAS
, EKK0341B015CBSMsg1List.KIKI_SEIZO_NO_5_CCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_1_STB
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_2_STB
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_3_STB
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_4_STB
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_5_STB
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_1_BCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_2_BCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_3_BCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_4_BCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_5_BCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_1_CCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_2_CCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_3_CCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_4_CCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CD_5_CCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM1_STB
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM2_STB
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM3_STB
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM4_STB
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM5_STB
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM1_BCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM2_BCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM3_BCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM4_BCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM5_BCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM1_CCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM2_CCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM3_CCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM4_CCAS
, EKK0341B015CBSMsg1List.EO_TV_KKST_SNN_STAT_CDNM5_CCAS
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_1_STB
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_2_STB
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_3_STB
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_4_STB
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_5_STB
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_1_BCAS
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_2_BCAS
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_3_BCAS
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_4_BCAS
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_5_BCAS
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_1_CCAS
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_2_CCAS
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_3_CCAS
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_4_CCAS
, EKK0341B015CBSMsg1List.KKST_JSEKI_UK_YMD_5_CCAS
};
// 明細に処理結果(ResultSet)をマッピング
CAANMsg[] outMsg1 = mapMessageList(rsltQuery_Page,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0341B015CBSMsg1List",0);
// エラー処理（機能コード１のみ）
if ( "1".equals( inMsg.getString(EKK0341B015CBSMsg.FUNC_CODE) ) ) {
// 取得結果が0件の場合は表示ページ該当なしエラー
if (0 >= outMsg1.length)
{
inMsg.set(EKK0341B015CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_NODATA);
return;
}
}
// inMsg(CBSMsg)に明細をセット
inMsg.set("EKK0341B015CBSMsg1List", outMsg1);
} catch(SQLException e) {
inMsg.set(EKK0341B015CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
} finally {
// 資源の解放
try{
if(rsltQuery_Count != null){
rsltQuery_Count.close();
}
if(rsltQuery_Page != null){
rsltQuery_Page.close();
}
if(pstmt_Count != null){
pstmt_Count.close();
}
if(pstmt_Page != null){
pstmt_Page.close();
}
if(con1 != null){
closeConnection(con1);
}
}catch(SQLException e){
inMsg.set(EKK0341B015CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
}
}
}
/**
*
* パラメータの設定を行います。
*
* @param inMsg 入力値の格納されたCBSMsg
* @param pstmt プリペアステートメント
* @param iPCnt パラメータのカウント
* @exception SQLException
*/
private int setPram(CAANMsg inMsg, PreparedStatement pstmt, int iPCnt) throws SQLException
{
// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
// パラメータの設定(ＫＥＹ＿機器設置実績受付年月日(FROM)を指定)
if (!inMsg.isNull(EKK0341B015CBSMsg.KEY_KKST_JSEKI_UK_YMD_FROM))
{
CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0341B015CBSMsg.KEY_KKST_JSEKI_UK_YMD_FROM));
}
// パラメータの設定(ＫＥＹ＿機器設置実績受付年月日(TO)を指定)
if (!inMsg.isNull(EKK0341B015CBSMsg.KEY_KKST_JSEKI_UK_YMD_TO))
{
CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0341B015CBSMsg.KEY_KKST_JSEKI_UK_YMD_TO));
}
// ++++++++++++++++++++++++++ ｖ7.00.01 削除開始 ++++++++++++++++++++++++++
// // パラメータの設定(ＫＥＹ＿eoTV機器設置承認状態コードを指定)
// if (!inMsg.isNull(EKK0341B015CBSMsg.KEY_EO_TV_KKST_SNN_STAT_CD))
// {
// CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0341B015CBSMsg.KEY_EO_TV_KKST_SNN_STAT_CD));
// }
// ++++++++++++++++++++++++++ ｖ7.00.01 削除終了 ++++++++++++++++++++++++++
// パラメータの設定(ＫＥＹ＿工事案件番号を指定)
if (!inMsg.isNull(EKK0341B015CBSMsg.KEY_KOJIAK_NO))
{
CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0341B015CBSMsg.KEY_KOJIAK_NO));
}
// パラメータの設定(ＫＥＹ＿サービス契約番号を指定)
if (!inMsg.isNull(EKK0341B015CBSMsg.KEY_SVC_KEI_NO))
{
CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0341B015CBSMsg.KEY_SVC_KEI_NO));
}
return iPCnt;
}
}