/*******************************************************************************
* All Rights reserved,Copyright (c) K-Opticom
********************************************************************************
*＜プログラム内容＞
* システム名 ：eo顧客基幹システム
* モジュール名 ：JSYejbEKK0241B004TPDA
* ソースファイル名：JSYejbEKK0241B004TPDA.java
* 作成者 ：EK911025
* 日付 ：2018年01月11日
*＜機能概要＞
* 工事費割賦契約回線情報一覧照会テンプレートDBアクセス部品です。
*＜修正履歴＞
* バージョン 修正日 修正者 修正内容
* v36.00.00 2018/01/10 FJ) 寺園 【ANK-3296-00-00】工事費割賦請求
* v36.00.01 2018/02/08 FJ) 寺園 【ANK-3296-00-00 IT1-2018-0000022】工事費割賦請求
* v37.00.00 2018/04/23 FJ) 澤田 【ANK-3383-00-00】工事費割賦請求STEP2
* v37.02.00 2018/05/28 FJ) 澤田 【IT1-2018-0000073】工事費割賦が有でキャンペーンが重複しないように対応
* v53.00.00 2021/04/19 FJ) 麻生 【ANK-3951-00-00】「競争ルールの検証に関するWG」に関するシステム対応
* v56.00.00 2021/08/16 FJ) 舘山 【OM-2020-0000460】相当額CPが電話-Tv用からネット用に切り替わらない
* v60.00.00 2022/08/18 FJ) 麻生 【ANK-4330-00-00】消費者保護ガイドライン改正対応（Step2）
*
********************************************************************************/
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.JKKStrConst;
import eo.ejb.cbs.cbsmsg.EKK0241B004CBSMsg;
import eo.ejb.cbs.cbsmsg.EKK0241B004CBSMsg1List;
import eo.ejb.cbm.entity.KK0241ETMsg;
import eo.ejb.common.JKKModelCommon;
/**
*
* テンプレートDBアクセス部品 EKK0241B004_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/
public class JSYejbEKK0241B004TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
/**
* コンストラクタ
*/
public JSYejbEKK0241B004TPDA() {
}
/**
* EKK0241B004_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(KK0241ETMsg.getTableName());
// SQL文_基本部1
StringBuffer sql_Buff = new StringBuffer();
sql_Buff.append(" SELECT ")
.append(" KK0241.SVC_KEI_KAISEN_UCWK_NO ")
.append(" , KK3131.KAP_KEI_STAT ")
.append(" , KK0081_NET.SVC_KEI_NO AS SVC_KEI_NO_NET ")
.append(" , KK0081_NET.SVC_KEI_STAT AS SVC_KEI_STAT_NET ")
.append(" , KK0081_NET.SEIKY_KEI_NO AS SEIKY_KEI_NO_NET ")
.append(" , KK0081_TEL.SVC_KEI_NO AS SVC_KEI_NO_TEL ")
.append(" , KK0081_TEL.SVC_KEI_STAT AS SVC_KEI_STAT_TEL ")
.append(" , KK0081_TEL.SEIKY_KEI_NO AS SEIKY_KEI_NO_TEL ")
.append(" , KK0081_TV.SVC_KEI_NO AS SVC_KEI_NO_TV ")
.append(" , KK0081_TV.SVC_KEI_STAT AS SVC_KEI_STAT_TV ")
.append(" , KK0081_TV.SEIKY_KEI_NO AS SEIKY_KEI_NO_TV ")
.append(" , KK0451_NET.WRIB_SVC_KEI_NO AS KOJIHI_KAP_CP_KEI_NO_NET ")
.append(" , KK0451_NET.WRIB_SVC_KEI_STAT AS KOJIHI_KAP_CP_STAT_NET ")
.append(" , KK0451_TEL.WRIB_SVC_KEI_NO AS KOJIHI_KAP_CP_KEI_NO_TEL ")
.append(" , KK0451_TEL.WRIB_SVC_KEI_STAT AS KOJIHI_KAP_CP_STAT_TEL ")
// v36.00.01 ADD START
.append(" , KK0451_NET.IDO_DIV AS KOJIHI_KAP_CP_IDO_DIV_NET ")
.append(" , KK0451_TEL.IDO_DIV AS KOJIHI_KAP_CP_IDO_DIV_TEL ")
// v36.00.01 ADD END
// v37.02.00 ADD START
.append(" , KK3131.MSKM_DTL_NO AS MSKM_DTL_NO ")
// v53.00.00 DEL START
// .append(" , KK3131.KOJIHI_KAP_KEI_NO AS KOJIHI_KAP_KEI_NO ")
// v53.00.00 DEL END
// v37.02.00 ADD END
// v53.00.00 ADD START
.append(" , KK3131.KAP_PLAN_CD AS KAP_PLAN_CD ")
// v60.00.00 ADD START
.append(" , KK0081_NET.SYSID AS SYSID_NET ")
.append(" , KK0081_TEL.SYSID AS SYSID_TEL ")
.append(" , KK0081_TV.SYSID AS SYSID_TV ")
// v60.00.00 ADD END
.append(" , KK3131.KOJIHI_KAP_KEI_NO AS KOJIHI_KAP_KEI_NO ")
// v53.00.00 ADD END
.append(" FROM KK_T_KAISEN_TG_SVKEI KK0241 ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT KK3131.SVC_KEI_KAISEN_UCWK_NO ")
// v37.02.00 ADD START
.append(" , KK3131.KOJIHI_KAP_KEI_NO ")
.append(" , KK3131.MSKM_DTL_NO ")
// v37.02.00 ADD END
.append(" , KK3131.KAP_KEI_STAT ")
// v53.00.00 ADD START
.append(" , KK3131.KAP_PLAN_CD ")
// v53.00.00 ADD END
.append(" FROM KK_T_KOJIHI_KAP_KEI KK3131 ")
// v37.00.00 MOD START
// .append(" WHERE (KK3131.SVC_KEI_KAISEN_UCWK_NO, KK3131.GENE_ADD_DTM) = ")
// .append(" ( SELECT KK3131_GENE.SVC_KEI_KAISEN_UCWK_NO, MAX(KK3131_GENE.GENE_ADD_DTM) AS KK3131_MAX ")
// .append(" FROM KK_T_KOJIHI_KAP_KEI KK3131_GENE ")
// .append(" WHERE KK3131_GENE.SVC_KEI_KAISEN_UCWK_NO = KK3131.SVC_KEI_KAISEN_UCWK_NO ")
// .append(" AND KK3131_GENE.MK_FLG = '0' ")
// .append(" GROUP BY KK3131_GENE.SVC_KEI_KAISEN_UCWK_NO) ")
.append(" WHERE (KK3131.KOJIHI_KAP_KEI_NO, KK3131.GENE_ADD_DTM) = ")
.append(" ( SELECT KK3131_GENE.KOJIHI_KAP_KEI_NO, MAX(KK3131_GENE.GENE_ADD_DTM) AS KK3131_MAX ")
.append(" FROM KK_T_KOJIHI_KAP_KEI KK3131_GENE ")
.append(" WHERE KK3131_GENE.KOJIHI_KAP_KEI_NO = KK3131.KOJIHI_KAP_KEI_NO ")
.append(" AND KK3131_GENE.MK_FLG = '0' ")
.append(" GROUP BY KK3131_GENE.KOJIHI_KAP_KEI_NO) ")
// v37.00.00 MOD END
.append(" ) KK3131 ")
.append(" ON KK3131.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT KK0241.SVC_KEI_KAISEN_UCWK_NO ")
.append(" , KK0081.SVC_KEI_NO ")
.append(" , KK0081.SVC_KEI_STAT ")
.append(" , KK0321.SEIKY_KEI_NO ")
// v60.00.00 ADD START
.append(" , KK0081.SYSID ")
// v60.00.00 ADD END
.append(" FROM KK_T_KAISEN_TG_SVKEI KK0241 ")
.append(" INNER JOIN KK_T_SVC_KEI KK0081 ")
.append(" ON KK0081.SVC_KEI_NO = KK0241.SVC_KEI_NO ")
.append(" INNER JOIN KK_T_KAKINS KK0321 ")
.append(" ON KK0321.SVC_KEI_NO = KK0081.SVC_KEI_NO ")
.append(" WHERE KK0081.SVC_CD = '01' ")
.append(" AND KK0081.SVC_KEI_STAT IN('010','020','030','100','210','220') ")
.append(" AND KK0321.KAKINS_TSTAYMD <= ? ")
.append(" AND KK0321.KAKINS_TENDYMD >= ? ")
.append(" AND KK0321.MK_FLG= '0' ")
.append(" AND KK0241.KAISEN_UCWK_USE_STAYMD <= ? ")
.append(" AND (KK0081.SVC_CD, KK0241.KAISEN_UCWK_USE_ENDYMD) IN ")
.append(" (SELECT KK0081_01.SVC_CD, MAX(KK0241_01.KAISEN_UCWK_USE_ENDYMD) ")
.append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_01 ")
.append(" INNER JOIN KK_T_SVC_KEI KK0081_01 ")
.append(" ON KK0081_01.SVC_KEI_NO = KK0241_01.SVC_KEI_NO ")
.append(" WHERE KK0241_01.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO ")
.append(" AND KK0241_01.KAISEN_UCWK_USE_STAYMD <= ? ")
.append(" AND KK0241_01.MK_FLG = '0' ")
.append(" AND (KK0081_01.SVC_KEI_NO, KK0081_01.RSV_APLY_YMD || KK0081_01.GENE_ADD_DTM) = ")
.append(" (SELECT KK0081_01_GENE.SVC_KEI_NO, MAX(KK0081_01_GENE.RSV_APLY_YMD || KK0081_01_GENE.GENE_ADD_DTM) AS KK0081_01_MAX ")
.append(" FROM KK_T_SVC_KEI KK0081_01_GENE ")
.append(" WHERE KK0081_01_GENE.SVC_KEI_NO = KK0081_01.SVC_KEI_NO ")
.append(" AND KK0081_01_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0081_01_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0081_01_GENE.MK_FLG = '0' ")
.append(" GROUP BY KK0081_01_GENE.SVC_KEI_NO ) ")
.append(" GROUP BY KK0081_01.SVC_CD ) ")
.append(" AND KK0241.MK_FLG = '0' ")
.append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ")
.append(" (SELECT KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_MAX ")
.append(" FROM KK_T_SVC_KEI KK0081_GENE ")
.append(" WHERE KK0081_GENE.SVC_KEI_NO = KK0081.SVC_KEI_NO ")
.append(" AND KK0081_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0081_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0081_GENE.MK_FLG = '0' ")
.append(" GROUP BY KK0081_GENE.SVC_KEI_NO) ")
.append(" ) KK0081_NET ")
.append(" ON KK0081_NET.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT KK0241.SVC_KEI_KAISEN_UCWK_NO ")
.append(" , KK0081.SVC_KEI_NO ")
.append(" , KK0081.SVC_KEI_STAT ")
.append(" , KK0321.SEIKY_KEI_NO ")
// v60.00.00 ADD START
.append(" , KK0081.SYSID ")
// v60.00.00 ADD END
.append(" FROM KK_T_KAISEN_TG_SVKEI KK0241 ")
.append(" INNER JOIN KK_T_SVC_KEI KK0081 ")
.append(" ON KK0081.SVC_KEI_NO = KK0241.SVC_KEI_NO ")
.append(" INNER JOIN KK_T_KAKINS KK0321 ")
.append(" ON KK0321.SVC_KEI_NO = KK0081.SVC_KEI_NO ")
.append(" WHERE KK0081.SVC_CD = '02' ")
.append(" AND KK0081.SVC_KEI_STAT IN('010','020','030','100','210','220') ")
.append(" AND KK0321.KAKINS_TSTAYMD <= ? ")
.append(" AND KK0321.KAKINS_TENDYMD >= ? ")
.append(" AND KK0321.MK_FLG= '0' ")
.append(" AND KK0241.KAISEN_UCWK_USE_STAYMD <= ? ")
.append(" AND (KK0081.SVC_CD, KK0241.KAISEN_UCWK_USE_ENDYMD) IN ")
.append(" (SELECT KK0081_01.SVC_CD, MAX(KK0241_01.KAISEN_UCWK_USE_ENDYMD) ")
.append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_01 ")
.append(" INNER JOIN KK_T_SVC_KEI KK0081_01 ")
.append(" ON KK0081_01.SVC_KEI_NO = KK0241_01.SVC_KEI_NO ")
.append(" WHERE KK0241_01.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO ")
.append(" AND KK0241_01.KAISEN_UCWK_USE_STAYMD <= ? ")
.append(" AND KK0241_01.MK_FLG = '0' ")
.append(" AND (KK0081_01.SVC_KEI_NO, KK0081_01.RSV_APLY_YMD || KK0081_01.GENE_ADD_DTM) = ")
.append(" (SELECT KK0081_01_GENE.SVC_KEI_NO, MAX(KK0081_01_GENE.RSV_APLY_YMD || KK0081_01_GENE.GENE_ADD_DTM) AS KK0081_01_MAX ")
.append(" FROM KK_T_SVC_KEI KK0081_01_GENE ")
.append(" WHERE KK0081_01_GENE.SVC_KEI_NO = KK0081_01.SVC_KEI_NO ")
.append(" AND KK0081_01_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0081_01_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0081_01_GENE.MK_FLG = '0' ")
.append(" GROUP BY KK0081_01_GENE.SVC_KEI_NO ) ")
.append(" GROUP BY KK0081_01.SVC_CD ) ")
.append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ")
.append(" (SELECT KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_MAX ")
.append(" FROM KK_T_SVC_KEI KK0081_GENE ")
.append(" WHERE KK0081_GENE.SVC_KEI_NO = KK0081.SVC_KEI_NO ")
.append(" AND KK0081_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0081_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0081_GENE.MK_FLG = '0' ")
.append(" GROUP BY KK0081_GENE.SVC_KEI_NO) ")
.append(" ) KK0081_TEL ")
.append(" ON KK0081_TEL.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT KK0241.SVC_KEI_KAISEN_UCWK_NO ")
.append(" , KK0081.SVC_KEI_NO ")
.append(" , KK0081.SVC_KEI_STAT ")
.append(" , KK0321.SEIKY_KEI_NO ")
// v60.00.00 ADD START
.append(" , KK0081.SYSID ")
// v60.00.00 ADD END
.append(" FROM KK_T_KAISEN_TG_SVKEI KK0241 ")
.append(" INNER JOIN KK_T_SVC_KEI KK0081 ")
.append(" ON KK0081.SVC_KEI_NO = KK0241.SVC_KEI_NO ")
.append(" INNER JOIN KK_T_KAKINS KK0321 ")
.append(" ON KK0321.SVC_KEI_NO = KK0081.SVC_KEI_NO ")
.append(" WHERE KK0081.SVC_CD = '03' ")
// v37.00.00 ADD START
.append(" AND KK0081.PCRS_CD <> 'A23' ")
// v37.00.00 ADD END
.append(" AND KK0081.SVC_KEI_STAT IN('010','020','030','100','210','220') ")
.append(" AND KK0321.KAKINS_TSTAYMD <= ? ")
.append(" AND KK0321.KAKINS_TENDYMD >= ? ")
.append(" AND KK0321.MK_FLG= '0' ")
.append(" AND KK0241.KAISEN_UCWK_USE_STAYMD <= ? ")
.append(" AND (KK0081.SVC_CD, KK0241.KAISEN_UCWK_USE_ENDYMD) IN ")
.append(" (SELECT KK0081_01.SVC_CD, MAX(KK0241_01.KAISEN_UCWK_USE_ENDYMD) ")
.append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_01 ")
.append(" INNER JOIN KK_T_SVC_KEI KK0081_01 ")
.append(" ON KK0081_01.SVC_KEI_NO = KK0241_01.SVC_KEI_NO ")
.append(" WHERE KK0241_01.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO ")
.append(" AND KK0241_01.KAISEN_UCWK_USE_STAYMD <= ? ")
.append(" AND KK0241_01.MK_FLG = '0' ")
.append(" AND (KK0081_01.SVC_KEI_NO, KK0081_01.RSV_APLY_YMD || KK0081_01.GENE_ADD_DTM) = ")
.append(" (SELECT KK0081_01_GENE.SVC_KEI_NO, MAX(KK0081_01_GENE.RSV_APLY_YMD || KK0081_01_GENE.GENE_ADD_DTM) AS KK0081_01_MAX ")
.append(" FROM KK_T_SVC_KEI KK0081_01_GENE ")
.append(" WHERE KK0081_01_GENE.SVC_KEI_NO = KK0081_01.SVC_KEI_NO ")
.append(" AND KK0081_01_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0081_01_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0081_01_GENE.MK_FLG = '0' ")
.append(" GROUP BY KK0081_01_GENE.SVC_KEI_NO ) ")
.append(" GROUP BY KK0081_01.SVC_CD ) ")
.append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ")
.append(" (SELECT KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_MAX ")
.append(" FROM KK_T_SVC_KEI KK0081_GENE ")
.append(" WHERE KK0081_GENE.SVC_KEI_NO = KK0081.SVC_KEI_NO ")
.append(" AND KK0081_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0081_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0081_GENE.MK_FLG = '0' ")
.append(" GROUP BY KK0081_GENE.SVC_KEI_NO) ")
.append(" ) KK0081_TV ")
.append(" ON KK0081_TV.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT KK0481.SVC_KEI_NO ")
.append(" , KK0451.WRIB_SVC_KEI_NO ")
.append(" , KK0451.WRIB_SVC_KEI_STAT ")
// v36.00.01 ADD START
.append(" , KK0451.IDO_DIV ")
// v36.00.01 ADD END
.append(" FROM KK_T_WRISVC_TG_KEI KK0481 ")
.append(" INNER JOIN KK_T_WRIB_SVC_KEI KK0451 ")
.append(" ON KK0451.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO ")
// v53.00.00 MOD START
//.append(" WHERE KK0451.WRIB_SVC_CD = ? ");
.append(" WHERE KK0451.WRIB_SVC_CD IN (?, ?) ");
// v53.00.00 MOD END
// v36.00.01 MOD START
// .append(" AND KK0451.WRIB_SVC_KEI_STAT IN('010','100','300','910') ")
// v60.00.00 MOD START
// if ( "1".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ) {
if ( "1".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) || "3".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ) {
// v60.00.00 MOD END
sql_Buff.append(" AND KK0451.WRIB_SVC_KEI_STAT IN('010','100','300','910') ");
}
// v60.00.00 MOD START
// else if ( "2".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ) {
else if ( "2".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) || "4".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ) {
// v60.00.00 MOD END
sql_Buff.append(" AND KK0451.WRIB_SVC_KEI_STAT IN('010','100','300','910','920') ");
}
// v36.00.01 MOD END
sql_Buff.append(" AND (KK0481.WRIB_SVC_KEI_NO, KK0481.WRIB_SVC_TRGT_KEI_NO, KK0481.GENE_ADD_DTM ) = ")
.append(" ( SELECT KK0481_GENE1.WRIB_SVC_KEI_NO, ")
.append(" KK0481_GENE1.WRIB_SVC_TRGT_KEI_NO, ")
.append(" MAX(KK0481_GENE1.GENE_ADD_DTM) AS KK0481_GENE1_MAX ")
.append(" FROM KK_T_WRISVC_TG_KEI KK0481_GENE1 ")
.append(" WHERE KK0481_GENE1.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO ")
.append(" AND KK0481_GENE1.WRIB_SVC_TRGT_KEI_NO = KK0481.WRIB_SVC_TRGT_KEI_NO ")
.append(" AND KK0481_GENE1.WRISVC_TG_KEI_TSTAYMD <= ? ")
.append(" AND KK0481_GENE1.WRISVC_TG_KEI_TENDYMD >= ? ")
.append(" AND KK0481_GENE1.MK_FLG = '0' ")
.append(" GROUP BY ")
.append(" KK0481_GENE1.WRIB_SVC_KEI_NO ")
.append(" ,KK0481_GENE1.WRIB_SVC_TRGT_KEI_NO ")
.append(" ) ")
.append(" AND (KK0451.WRIB_SVC_KEI_NO, KK0451.RSV_APLY_YMD || KK0451.GENE_ADD_DTM) = ")
.append(" (SELECT KK0451_GENE.WRIB_SVC_KEI_NO, MAX(KK0451_GENE.RSV_APLY_YMD || KK0451_GENE.GENE_ADD_DTM) ")
.append(" FROM KK_T_WRIB_SVC_KEI KK0451_GENE ")
.append(" WHERE KK0451_GENE.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO ")
.append(" AND KK0451_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0451_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0451_GENE.MK_FLG = '0' ")
.append(" GROUP BY KK0451_GENE.WRIB_SVC_KEI_NO ")
.append(" ) ")
.append(" ORDER BY KK0451.WRIB_SVC_KEI_NO DESC ")
.append(" ) KK0451_NET ")
.append(" ON KK0451_NET.SVC_KEI_NO = KK0081_NET.SVC_KEI_NO ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT KK0481.SVC_KEI_NO ")
.append(" , KK0451.WRIB_SVC_KEI_NO ")
.append(" , KK0451.WRIB_SVC_KEI_STAT ")
// v36.00.01 ADD START
.append(" , KK0451.IDO_DIV ")
// v36.00.01 ADD END
.append(" FROM KK_T_WRISVC_TG_KEI KK0481 ")
.append(" INNER JOIN KK_T_WRIB_SVC_KEI KK0451 ")
.append(" ON KK0451.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO ")
// v53.00.00 MOD START
//.append(" WHERE KK0451.WRIB_SVC_CD = ? ");
.append(" WHERE KK0451.WRIB_SVC_CD IN (?, ?, ?) ");
// v53.00.00 MOD END
// v36.00.01 MOD START
// .append(" AND KK0451.WRIB_SVC_KEI_STAT IN('010','100','300','910') ")
// v60.00.00 MOD START
// if ( "1".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ) {
if ( "1".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) || "3".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ) {
// v60.00.00 MOD END
sql_Buff.append(" AND KK0451.WRIB_SVC_KEI_STAT IN('010','100','300','910') ");
}
// v60.00.00 MOD START
// else if ( "2".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ) {
else if ( "2".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) || "4".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ) {
// v60.00.00 MOD END
sql_Buff.append(" AND KK0451.WRIB_SVC_KEI_STAT IN('010','100','300','910','920') ");
}
// v36.00.01 MOD END
sql_Buff.append(" AND (KK0481.WRIB_SVC_KEI_NO, KK0481.WRIB_SVC_TRGT_KEI_NO, KK0481.GENE_ADD_DTM ) = ")
.append(" ( SELECT KK0481_GENE1.WRIB_SVC_KEI_NO, ")
.append(" KK0481_GENE1.WRIB_SVC_TRGT_KEI_NO, ")
.append(" MAX(KK0481_GENE1.GENE_ADD_DTM) AS KK0481_GENE1_MAX ")
.append(" FROM KK_T_WRISVC_TG_KEI KK0481_GENE1 ")
.append(" WHERE KK0481_GENE1.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO ")
.append(" AND KK0481_GENE1.WRIB_SVC_TRGT_KEI_NO = KK0481.WRIB_SVC_TRGT_KEI_NO ")
.append(" AND KK0481_GENE1.WRISVC_TG_KEI_TSTAYMD <= ? ")
.append(" AND KK0481_GENE1.WRISVC_TG_KEI_TENDYMD >= ? ")
.append(" AND KK0481_GENE1.MK_FLG = '0' ")
.append(" GROUP BY ")
.append(" KK0481_GENE1.WRIB_SVC_KEI_NO ")
.append(" ,KK0481_GENE1.WRIB_SVC_TRGT_KEI_NO ")
.append(" ) ")
.append(" AND (KK0451.WRIB_SVC_KEI_NO, KK0451.RSV_APLY_YMD || KK0451.GENE_ADD_DTM) = ")
.append(" (SELECT KK0451_GENE.WRIB_SVC_KEI_NO, MAX(KK0451_GENE.RSV_APLY_YMD || KK0451_GENE.GENE_ADD_DTM) ")
.append(" FROM KK_T_WRIB_SVC_KEI KK0451_GENE ")
.append(" WHERE KK0451_GENE.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO ")
.append(" AND KK0451_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0451_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0451_GENE.MK_FLG = '0' ")
.append(" GROUP BY KK0451_GENE.WRIB_SVC_KEI_NO ")
.append(" ) ")
.append(" ORDER BY KK0451.WRIB_SVC_KEI_NO DESC ")
.append(" ) KK0451_TEL ")
.append(" ON KK0451_TEL.SVC_KEI_NO = KK0081_TEL.SVC_KEI_NO ");
// SQL文_個別部1
// v36.00.01 MOD START
// if ( "1".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ) {
// v60.00.00 MOD START
// if ( "1".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ||
// "2".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() )) {
if ( "1".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ||
"2".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ||
"3".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ||
"4".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() )) {
// v60.00.00 MOD END
// v36.00.01 MOD END
sql_Buff.append(" WHERE KK0241.SVC_KEI_NO = ? ")
.append(" AND KK0241.KAISEN_UCWK_USE_STAYMD <= ? ")
.append(" AND KK0241.KAISEN_UCWK_USE_ENDYMD >= ? ")
.append(" AND KK0241.MK_FLG = '0' ")
// OM-2020-0000460 Modify START
// v37.02.00 ADD START
// .append(" ORDER BY KK3131.KOJIHI_KAP_KEI_NO DESC ");
.append(" ORDER BY KK3131.KOJIHI_KAP_KEI_NO DESC ")
// v37.02.00 ADD END
.append(" ,KK0451_NET.WRIB_SVC_KEI_NO DESC ")
.append(" ,KK0451_TEL.WRIB_SVC_KEI_NO DESC ")
;
// OM-2020-0000460 Modify END
}
//prepareStatementにSQL文をセット
pstmt = con1.prepareStatement(sql_Buff.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff);
// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
// v36.00.01 MOD START
// if ( "1".equals( inMsg.getString(EKK0241B004CBSMsg.FUNC_CODE) ) ) {
// v60.00.00 MOD START
// if ( "1".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ||
// "2".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() )) {
if ( "1".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ||
"2".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ||
"3".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ||
"4".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() )) {
// v60.00.00 MOD END
// v36.00.01 MOD END
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 1, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 2, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 3, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 4, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 5, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 6, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 7, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 8, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 9, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 10, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 11, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 12, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 13, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 14, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 15, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 16, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 17, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 18, JKKModelCommon.getOpeDate(inMsg));
// v60.00.00 MOD START
// // パラメータの設定(標準工事費相当額割引CP（ネット用）を指定)
// CAANJDBCUtil.setParam(pstmt, 19, JKKStrConst.WRIB_SVC_KOJIHI_KAP_CP_NET);
// // v53.00.00 ADD START
// // パラメータの設定(標準工事費相当額割引CP（２４回）（ネット用）を指定)
// // v53.00.00 ADD END
// CAANJDBCUtil.setParam(pstmt, 20, JKKStrConst.WRIB_SVC_KOJIHI_KAP_CP_NET_24);
if ( "1".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ||
"2".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ))
{
// パラメータの設定(標準工事費相当額割引CP（ネット用）を指定)
CAANJDBCUtil.setParam(pstmt, 19, JKKStrConst.WRIB_SVC_KOJIHI_KAP_CP_NET);
// v53.00.00 ADD START
// パラメータの設定(標準工事費相当額割引CP（２４回）（ネット用）を指定)
CAANJDBCUtil.setParam(pstmt, 20, JKKStrConst.WRIB_SVC_KOJIHI_KAP_CP_NET_24);
// v53.00.00 ADD END
}
else if ( "3".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ||
"4".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ))
{
// パラメータの設定(標準工事費割引CP（２４回）（ネット用）を指定)
CAANJDBCUtil.setParam(pstmt, 19, JKKStrConst.WRIB_SVC_KOJIHI_WRBK_CP_NET_24);
// パラメータの設定(標準工事費割引CP（２４回）（ネット用）を指定)
CAANJDBCUtil.setParam(pstmt, 20, JKKStrConst.WRIB_SVC_KOJIHI_WRBK_CP_NET_24);
}
// v60.00.00 MOD END
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 21, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 22, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 23, JKKModelCommon.getOpeDate(inMsg));
// v60.00.00 MOD START
// // パラメータの設定(標準工事費相当額割引CP（電話用）を指定)
// CAANJDBCUtil.setParam(pstmt, 24, JKKStrConst.WRIB_SVC_KOJIHI_KAP_CP_TEL);
// // v53.00.00 ADD START
// // パラメータの設定(標準工事費相当額割引CP（２４回）（電話用）を指定)
// CAANJDBCUtil.setParam(pstmt, 25, JKKStrConst.WRIB_SVC_KOJIHI_KAP_CP_TEL_24);
// // パラメータの設定(標準工事費相当額割引CP（一括）（電話用）を指定)
// CAANJDBCUtil.setParam(pstmt, 26, JKKStrConst.WRIB_SVC_KOJIHI_KAP_CP_TEL_IKT);
// // v53.00.00 ADD END
if ( "1".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ||
"2".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ))
{
// パラメータの設定(標準工事費相当額割引CP（電話用）を指定)
CAANJDBCUtil.setParam(pstmt, 24, JKKStrConst.WRIB_SVC_KOJIHI_KAP_CP_TEL);
// v53.00.00 ADD START
// パラメータの設定(標準工事費相当額割引CP（２４回）（電話用）を指定)
CAANJDBCUtil.setParam(pstmt, 25, JKKStrConst.WRIB_SVC_KOJIHI_KAP_CP_TEL_24);
// パラメータの設定(標準工事費相当額割引CP（一括）（電話用）を指定)
CAANJDBCUtil.setParam(pstmt, 26, JKKStrConst.WRIB_SVC_KOJIHI_KAP_CP_TEL_IKT);
// v53.00.00 ADD END
}
else if ( "3".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ) ||
"4".equals( inMsg.getObject(EKK0241B004CBSMsg.FUNC_CODE).toString() ))
{
// パラメータの設定(標準工事費割引CP（２４回）（電話用）を指定)
CAANJDBCUtil.setParam(pstmt, 24, JKKStrConst.WRIB_SVC_KOJIHI_WRBK_CP_TEL_24);
// パラメータの設定(標準工事費割引CP（２４回）（電話用）を指定)
CAANJDBCUtil.setParam(pstmt, 25, JKKStrConst.WRIB_SVC_KOJIHI_WRBK_CP_TEL_24);
// パラメータの設定(標準工事費割引CP（２４回）（電話用）を指定)
CAANJDBCUtil.setParam(pstmt, 26, JKKStrConst.WRIB_SVC_KOJIHI_WRBK_CP_TEL_24);
}
// v60.00.00 MOD END
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 27, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 28, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 29, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(ＫＥＹ＿サービス契約番号を指定)
CAANJDBCUtil.setParam(pstmt, 30, inMsg.getObject(EKK0241B004CBSMsg.KEY_SVC_KEI_NO));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 31, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 32, JKKModelCommon.getOpeDate(inMsg));
}
// ResultSetの取得
rsltQuery = pstmt.executeQuery();
// EKK0241B004CBSMsgの明細にセットする項目
String[] msgKeyList1 = {
EKK0241B004CBSMsg1List.SVC_KEI_KAISEN_UCWK_NO
, EKK0241B004CBSMsg1List.KAP_KEI_STAT
, EKK0241B004CBSMsg1List.SVC_KEI_NO_NET
, EKK0241B004CBSMsg1List.SVC_KEI_STAT_NET
, EKK0241B004CBSMsg1List.SEIKY_KEI_NO_NET
, EKK0241B004CBSMsg1List.SVC_KEI_NO_TEL
, EKK0241B004CBSMsg1List.SVC_KEI_STAT_TEL
, EKK0241B004CBSMsg1List.SEIKY_KEI_NO_TEL
, EKK0241B004CBSMsg1List.SVC_KEI_NO_TV
, EKK0241B004CBSMsg1List.SVC_KEI_STAT_TV
, EKK0241B004CBSMsg1List.SEIKY_KEI_NO_TV
, EKK0241B004CBSMsg1List.KOJIHI_KAP_CP_KEI_NO_NET
, EKK0241B004CBSMsg1List.KOJIHI_KAP_CP_STAT_NET
, EKK0241B004CBSMsg1List.KOJIHI_KAP_CP_KEI_NO_TEL
, EKK0241B004CBSMsg1List.KOJIHI_KAP_CP_STAT_TEL
// v36.00.01 ADD START
, EKK0241B004CBSMsg1List.KOJIHI_KAP_CP_IDO_DIV_NET
, EKK0241B004CBSMsg1List.KOJIHI_KAP_CP_IDO_DIV_TEL
// v36.00.01 ADD END
// v37.02.00 ADD START
, EKK0241B004CBSMsg1List.MSKM_DTL_NO
// v37.02.00 ADD END
// v53.00.00 ADD START
, EKK0241B004CBSMsg1List.KAP_PLAN_CD
// v53.00.00 ADD END
// v60.00.00 ADD START
, EKK0241B004CBSMsg1List.SYSID_NET
, EKK0241B004CBSMsg1List.SYSID_TEL
, EKK0241B004CBSMsg1List.SYSID_TV
// v60.00.00 ADD END
};
// 明細に処理結果(ResultSet)をマッピング
CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0241B004CBSMsg1List",0);
// inMsg(CBSMsg)に明細をセット
inMsg.set("EKK0241B004CBSMsg1List", outMsg1);
} catch(SQLException e) {
inMsg.set(EKK0241B004CBSMsg.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(EKK0241B004CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
}
}
}
}