/*******************************************************************************
* All Rights reserved,Copyright (c) K-Opticom
********************************************************************************
*＜プログラム内容＞
* システム名 ：eo顧客基幹システム
* モジュール名 ：JSYejbEKK0081B509TPDA
* ソースファイル名：JSYejbEKK0081B509TPDA.java
* 作成者 ：EK909278
* 日付 ：2012年05月31日
*＜機能概要＞
* 問合せ対応お客様サービス情報一覧照会テンプレートDBアクセス部品です。
*＜修正履歴＞
* バージョン 修正日 修正者 修正内容
* ｖ1.00.00
* ｖ4.00.00 2013/03/04 FJ)伊藤 OT-2013-0000322
* ｖ5.00.00 2013/08/09 FJ)伊藤 IT1-2013-0001570 マンションＩＤの設定値をマンション物件.マンションオーナーIDから回線使用契約マンションIDに変更。
* ｖ6.00.00 2013/09/14 FJ)伊藤 OT-2013-0002064 回線対象サービス契約の取得条件を変更。
* ｖ6.00.01 2013/10/30 FJ)江森 OM-2013-0003843 認証ID取得方法の変更
* ｖ6.00.02 2013/12/07 FJ）伊藤 OM-2013-0004056 受付番号の検索条件を変更
* ｖ9.00.00 2014/08/15 FJ）伊藤 OM-2014-0002569 契約者タイプコードによって連絡先スキーマの取得条件を変更。
* ｖ29.00.00 2017/02/10 FJ) 笠川 ANK-2928-00-00 お客さま申告アドレスの複数登録
*
********************************************************************************/
package eo.ejb.cbs.sqlf;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.fujitsu.futurity.model.base.CAANJDBCUtil;
import com.fujitsu.futurity.model.base.CAANMsg;
import com.fujitsu.futurity.model.base.CAANRuntimeException;
import com.fujitsu.futurity.model.base.CAANSQLFacility;
import com.fujitsu.futurity.model.ejb.common.JSYejbConnection;
import com.fujitsu.futurity.model.ejb.common.JSYejbLog;
import com.fujitsu.futurity.model.ejb.common.StatusCodes;
import com.fujitsu.futurity.model.ejb.common.fw.AgentDispatchContext;
import com.fujitsu.futurity.model.ejb.common.fw.TemplateDBAccessHandler;
import eo.common.constant.JCRStrConst;
import eo.common.constant.JPCModelConstant;
import eo.ejb.cbm.entity.CK0011ETMsg;
import eo.ejb.cbm.entity.KK0081ETMsg;
import eo.ejb.cbs.cbsmsg.EKK0081B509CBSMsg;
import eo.ejb.cbs.cbsmsg.EKK0081B509CBSMsg1List;
import eo.ejb.common.JCCModelCommon;
/**
*
* テンプレートDBアクセス部品 EKK0081B509_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/
public class JSYejbEKK0081B509TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
/**
* コンストラクタ
*/
public JSYejbEKK0081B509TPDA() {
}
/**
* EKK0081B509_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(KK0081ETMsg.getTableName());
//運用日付
String opeDate = JCCModelCommon.getOpeDate(inMsg, null);
// v9.00.00 OM-2014-0002569 ADD START
// 契約者タイプコードの取得
String keishaTypeCd = getKeishaTypeCd(inMsg, inContext, opeDate);
// v9.00.00 OM-2014-0002569 ADD END
// SQL文_基本部1
StringBuffer sql_Buff = new StringBuffer();
sql_Buff.append(" SELECT ")
.append(" SV01.SVC_KEI_NO, ")
.append(" SV01.SYSID, ")
.append(" RK02.TELNO, ")
.append(" RK04.MLAD, ")
.append(" RK06.RRKS_NO, ")
.append(" RK06.MLAD, ")
.append(" RK06.UPD_DTM, ")
//ANK-2928-00-00 ADD START
.append(" RK08.RRKS_NO, ")
.append(" RK08.MLAD, ")
.append(" RK08.UPD_DTM, ")
.append(" RK10.RRKS_NO, ")
.append(" RK10.MLAD, ")
.append(" RK10.UPD_DTM, ")
//ANK-2928-00-00 ADD END
.append(" SU02.ISP_NINSHO_ID, ")
.append(" SU02.UQ_NINSHO_ID, ")
.append(" SU02.ADSL_NINSHO_ID, ")
.append(" M002.MSKMSHO_NO, ")
.append(" SV01.PRC_GRP_CD, ")
.append(" ( ")
.append(" SELECT ")
.append(" G001.PRC_GRP_NM ")
.append(" FROM ")
.append(" KK_M_PRC_GRP G001 ")
.append(" WHERE ")
.append(" G001.PRC_GRP_CD = SV01.PRC_GRP_CD AND ")
.append(" (G001.PRC_GRP_CD,G001.RSV_APLY_YMD || G001.GENE_ADD_DTM) = ")
.append(" ( ")
.append(" SELECT ")
.append(" G001_GENE.PRC_GRP_CD, ")
.append(" MAX(G001_GENE.RSV_APLY_YMD || G001_GENE.GENE_ADD_DTM) AS G001_MAX ")
.append(" FROM ")
.append(" KK_M_PRC_GRP G001_GENE ")
.append(" WHERE ")
.append(" G001_GENE.PRC_GRP_CD = G001.PRC_GRP_CD AND ")
.append(" G001_GENE.RSV_APLY_YMD <= ? AND ")
.append(" G001_GENE.MK_FLG = '0' ")
.append(" GROUP BY G001_GENE.PRC_GRP_CD ")
.append(" ) AND ")
.append(" G001.MK_FLG = '0' ")
.append(" ) PRC_GRP_NM, ")
.append(" ( ")
.append(" SELECT ")
.append(" P001.PCRS_NM ")
.append(" FROM ")
.append(" KK_M_PCRS P001 ")
.append(" WHERE ")
.append(" P001.PCRS_CD = SV01.PCRS_CD AND ")
.append(" (P001.PCRS_CD,P001.RSV_APLY_YMD || P001.GENE_ADD_DTM) = ")
.append(" ( ")
.append(" SELECT ")
.append(" P001_GENE.PCRS_CD, ")
.append(" MAX(P001_GENE.RSV_APLY_YMD || P001_GENE.GENE_ADD_DTM) AS P001_MAX ")
.append(" FROM ")
.append(" KK_M_PCRS P001_GENE ")
.append(" WHERE ")
.append(" P001_GENE.PCRS_CD = P001.PCRS_CD AND ")
.append(" P001_GENE.RSV_APLY_YMD <= ? AND ")
.append(" P001_GENE.MK_FLG = '0' ")
.append(" GROUP BY P001_GENE.PCRS_CD ")
.append(" ) AND ")
.append(" P001.MK_FLG = '0' ")
.append(" ) PCRS_NM, ")
.append(" SV01.SVC_STA_YMD, ")
.append(" TKHS.MANSION_ID, ")
.append(" KU02.MANSION_NM, ")
.append(" KU02.KAISEN_PLACE_PCD, ")
.append(" KU02.KAISEN_PLACE_STATE_NM, ")
.append(" KU02.KAISEN_PLACE_CITY_NM, ")
.append(" KU02.KAISEN_PLACE_OAZTSU_NM, ")
.append(" KU02.KAISEN_PLACE_AZCHO_NM, ")
.append(" KU02.KAISEN_PLACE_BNCHIGO, ")
.append(" KU02.KAISEN_PLACE_ADRTTM, ")
.append(" KU02.KAISEN_PLACE_ADRRM, ")
.append(" KU02.KAISEN_PLACE_TELNO, ")
.append(" SV01.SVC_KEI_STAT, ")
.append(" SU02.SVC_KEI_UCWK_STAT ")
.append(" FROM ")
.append(" KK_T_SVC_KEI SV01 ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT ")
.append(" M001.MSKM_DTL_NO, ")
.append(" M001.MSKMSHO_NO, ")
.append(" SVC1.SVC_KEI_NO ")
.append(" FROM ")
.append(" KK_T_MSKM_DTL M001 ")
.append(" INNER JOIN KK_T_SVC_KEI SVC1 ")
.append(" ON (SVC1.MSKM_DTL_NO = M001.MSKM_DTL_NO) ")
.append(" WHERE ")
.append(" (M001.MSKM_DTL_NO,M001.GENE_ADD_DTM) = ")
.append(" ( ")
.append(" SELECT ")
.append(" M001_GENE.MSKM_DTL_NO, ")
.append(" MAX(M001_GENE.GENE_ADD_DTM) AS M001_MAX ")
.append(" FROM ")
.append(" KK_T_MSKM_DTL M001_GENE ")
.append(" WHERE ")
.append(" M001_GENE.MSKM_DTL_NO = M001.MSKM_DTL_NO AND ")
.append(" M001_GENE.MK_FLG = '0' ")
.append(" GROUP BY M001_GENE.MSKM_DTL_NO ")
.append(" ) AND ")
.append(" SVC1.GENE_ADD_DTM = ( ")
.append(" SELECT ")
.append(" MIN(SVC1_GENE.GENE_ADD_DTM) AS SVC1_MIN ")
.append(" FROM ")
.append(" KK_T_SVC_KEI SVC1_GENE ")
.append(" WHERE ")
.append(" SVC1_GENE.SVC_KEI_NO = SVC1.SVC_KEI_NO AND ")
.append(" SVC1_GENE.MK_FLG = '0' ")
.append(" ) ")
.append(" ) M002 ON ( ")
.append(" M002.SVC_KEI_NO = SV01.SVC_KEI_NO ")
.append(" ) ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT ")
.append(" SU01.SVC_KEI_NO, ")
.append(" SU01.SVC_KEI_UCWK_NO, ")
.append(" EO01.ISP_NINSHO_ID, ")
.append(" EO02.ADSL_NINSHO_ID, ")
.append(" EO03.UQ_NINSHO_ID, ")
.append(" SU01.SVC_KEI_UCWK_STAT ")
.append(" FROM ")
.append(" KK_T_SVC_KEI_UCWK SU01 ")
.append(" LEFT OUTER JOIN KK_T_SVKEIUW_EOH_NET EO01 ON ( ")
.append(" EO01.SVC_KEI_UCWK_NO = SU01.SVC_KEI_UCWK_NO AND ")
.append(" EO01.GENE_ADD_DTM = SU01.GENE_ADD_DTM AND ")
.append(" EO01.RSV_APLY_CD = '2' AND ")
.append(" EO01.MK_FLG = '0' ")
.append(" ) ")
.append(" LEFT OUTER JOIN KK_T_SVKEIUW_EOADSL EO02 ON ( ")
.append(" EO02.SVC_KEI_UCWK_NO = SU01.SVC_KEI_UCWK_NO AND ")
.append(" EO02.GENE_ADD_DTM = SU01.GENE_ADD_DTM AND ")
.append(" EO02.RSV_APLY_CD = '2' AND ")
.append(" EO02.MK_FLG = '0' ")
.append(" ) ")
.append(" LEFT OUTER JOIN KK_T_SVKEIUW_EOMOBA EO03 ON ( ")
.append(" EO03.SVC_KEI_UCWK_NO = SU01.SVC_KEI_UCWK_NO AND ")
.append(" EO03.GENE_ADD_DTM = SU01.GENE_ADD_DTM AND ")
.append(" EO03.RSV_APLY_CD = '2' AND ")
.append(" EO03.MK_FLG = '0' ")
.append(" ) ")
.append(" WHERE ")
.append(" (SU01.SVC_KEI_UCWK_NO,SU01.RSV_APLY_YMD || SU01.GENE_ADD_DTM) = ")
.append(" ( ")
.append(" SELECT ")
.append(" SU01_GENE.SVC_KEI_UCWK_NO, ")
.append(" MAX(SU01_GENE.RSV_APLY_YMD || SU01_GENE.GENE_ADD_DTM) AS SU01_MAX ")
.append(" FROM ")
.append(" KK_T_SVC_KEI_UCWK SU01_GENE ")
.append(" WHERE ")
.append(" SU01_GENE.SVC_KEI_UCWK_NO = SU01.SVC_KEI_UCWK_NO AND ")
.append(" SU01_GENE.RSV_APLY_YMD <= ? AND ")
.append(" SU01_GENE.RSV_APLY_CD = '2' AND ")
.append(" SU01_GENE.MK_FLG = '0' ")
.append(" GROUP BY SU01_GENE.SVC_KEI_UCWK_NO ")
.append(" ) AND ")
.append(" SU01.MK_FLG = '0' ")
.append(" ) SU02 ON ( ")
.append(" SU02.SVC_KEI_NO = SV01.SVC_KEI_NO ");
//-------------------------------------------------------------------------------- 修正 Start --------//
if (!(inMsg.isNull(EKK0081B509CBSMsg.KEY_SVC_KEI_UCWK_NO)))
{
sql_Buff.append(" AND SU02.SVC_KEI_UCWK_NO = ? ");
}
//-------------------------------------------------------------------------------- 修正 End ----------//
sql_Buff.append(" ) ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT ")
.append(" KTSV1.SVC_KEI_NO, ")
.append(" KTSV1.SVC_KEI_KAISEN_UCWK_NO, ")
.append(" SKUW1.KAISEN_PLACE_PCD, ")
.append(" SKUW1.KAISEN_PLACE_STATE_NM, ")
.append(" SKUW1.KAISEN_PLACE_CITY_NM, ")
.append(" SKUW1.KAISEN_PLACE_OAZTSU_NM, ")
.append(" SKUW1.KAISEN_PLACE_AZCHO_NM, ")
.append(" SKUW1.KAISEN_PLACE_BNCHIGO, ")
.append(" SKUW1.KAISEN_PLACE_ADRTTM, ")
.append(" SKUW1.KAISEN_PLACE_ADRRM, ")
.append(" SKUW1.KAISEN_PLACE_TELNO, ")
.append(" MB01.MANSION_OWNR_ID, ")
.append(" MB01.MANSION_NM ")
.append(" FROM ")
.append(" KK_T_KAISEN_TG_SVKEI KTSV1, ")
.append(" KK_T_SVKEI_KAISEN_UW SKUW1 ")
.append(" LEFT OUTER JOIN KK_T_MANSION_BUKKEN MB01 ON ( ")
.append(" SKUW1.MANSION_BUKKEN_NO = MB01.MANSION_BUKKEN_NO ")
.append(" ) ")
.append(" WHERE ")
.append(" KTSV1.SVC_KEI_KAISEN_UCWK_NO = SKUW1.SVC_KEI_KAISEN_UCWK_NO AND ")
.append(" KTSV1.MK_FLG = '0' AND ")
.append(" KTSV1.KAISEN_UCWK_USE_STAYMD <= ? AND ")
.append(" (KTSV1.SVC_KEI_NO, KTSV1.KAISEN_UCWK_USE_ENDYMD) = ")
.append(" ( ")
.append(" SELECT KTSV1_SUB.SVC_KEI_NO, MAX(KTSV1_SUB.KAISEN_UCWK_USE_ENDYMD) ")
.append(" FROM KK_T_KAISEN_TG_SVKEI KTSV1_SUB ")
.append(" WHERE KTSV1_SUB.SVC_KEI_NO = KTSV1.SVC_KEI_NO ")
.append(" AND KTSV1_SUB.KAISEN_UCWK_USE_STAYMD <= ? ")
.append(" AND KTSV1_SUB.MK_FLG = '0' ")
.append(" GROUP BY KTSV1_SUB.SVC_KEI_NO ")
.append(" ) AND ")
.append(" (SKUW1.SVC_KEI_KAISEN_UCWK_NO,SKUW1.GENE_ADD_DTM) = ")
.append(" ( ")
.append(" SELECT ")
.append(" SKUW_GENE.SVC_KEI_KAISEN_UCWK_NO, ")
.append(" MAX(SKUW_GENE.GENE_ADD_DTM) AS SKUW_MAX ")
.append(" FROM ")
.append(" KK_T_SVKEI_KAISEN_UW SKUW_GENE ")
.append(" WHERE ")
.append(" SKUW_GENE.SVC_KEI_KAISEN_UCWK_NO = SKUW1.SVC_KEI_KAISEN_UCWK_NO AND ")
.append(" SKUW_GENE.MK_FLG = '0' ")
.append(" GROUP BY SKUW_GENE.SVC_KEI_KAISEN_UCWK_NO ")
.append(" ) AND ")
.append(" SKUW1.MK_FLG = '0' ")
.append(" ) KU02 ON ( ")
.append(" KU02.SVC_KEI_NO = SV01.SVC_KEI_NO ");
//-------------------------------------------------------------------------------- 修正 Start --------//
if (!(inMsg.isNull(EKK0081B509CBSMsg.KEY_SVC_KEI_KAISEN_UCWK_NO)))
{
sql_Buff.append(" AND KU02.SVC_KEI_KAISEN_UCWK_NO = ? ");
}
//-------------------------------------------------------------------------------- 修正 End ----------//
sql_Buff.append(" ) ")
.append(" LEFT OUTER JOIN( ")
.append(" SELECT ")
.append(" TKHS_SUB.TK_HOSHIKI_KEI_NO, ")
.append(" KSUK_SUB.MANSION_ID ")
.append(" FROM ")
.append(" KK_T_TK_HOSHIKI_KEI TKHS_SUB, ")
.append(" KK_T_KAISEN_USE_KEI KSUK_SUB ")
.append(" WHERE ")
.append(" TKHS_SUB.KAISEN_USE_KEI_NO = KSUK_SUB.KAISEN_USE_KEI_NO AND ")
.append(" KSUK_SUB.MK_FLG = '0' AND ")
.append(" TKHS_SUB.MK_FLG = '0' ")
.append(" ) TKHS ON ( ")
.append(" TKHS.TK_HOSHIKI_KEI_NO = SV01.TK_HOSHIKI_KEI_NO ")
.append(" ), ")
.append(" CK_T_CUST CUST ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT ")
.append(" RK01.SYSID, ")
.append(" RK01.TELNO ")
.append(" FROM ")
.append(" CK_T_RRKS RK01 ")
.append(" WHERE ")
//ANK-2928-00-00 MOD START
// .append(" (RK01.RRKS_NO,RK01.RSV_APLY_YMD || RK01.GENE_ADD_DTM) = ")
.append(" (RK01.RSV_APLY_YMD || RK01.GENE_ADD_DTM) = ")
//ANK-2928-00-00 MOD END
.append(" ( ")
.append(" SELECT ")
//ANK-2928-00-00 DEL START
// .append(" RK01_GENE.RRKS_NO, ")
//ANK-2928-00-00 DEL END
.append(" MAX(RK01_GENE.RSV_APLY_YMD || RK01_GENE.GENE_ADD_DTM) AS RK01_MAX ")
.append(" FROM ")
.append(" CK_T_RRKS RK01_GENE ")
.append(" WHERE ")
//ANK-2928-00-00 MOD START
// .append(" RK01_GENE.RRKS_NO = RK01.RRKS_NO AND ")
.append(" RK01_GENE.SYSID = RK01.SYSID AND ")
//ANK-2928-00-00 MOD END
.append(" RK01_GENE.RSV_APLY_YMD <= ? AND ")
.append(" RK01_GENE.RSV_APLY_CD = '2' AND ")
.append(" RK01_GENE.RRK_WAY_CD = '003' AND ")
.append(" RK01_GENE.RRKS_SBT_CD = '2' AND ")
.append(" RK01_GENE.MK_FLG = '0' ");
// v9.00.00 OM-2014-0002569 ADD START
// 契約者タイプコードが法人の場合
if (keishaTypeCd.equals(JCRStrConst.KEISHA_TYPE_CD_HOJIN))
{
sql_Buff.append(" AND RK01_GENE.RRKS_CD = '01' ");
}
// v9.00.00 OM-2014-0002569 ADD END
//ANK-2928-00-00 MOD START
// sql_Buff.append(" GROUP BY RK01_GENE.RRKS_NO ")
// .append(" ) ")
sql_Buff.append(" ) ")
//ANK-2928-00-00 MOD END
.append(" AND RK01.RRK_WAY_CD = '003' ")
.append(" AND RK01.RRKS_SBT_CD = '2' ")
.append(" AND RK01.MK_FLG = '0' ")
.append(" ) RK02 ON ( ")
.append(" RK02.SYSID = CUST.SYSID ")
.append(" ) ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT ")
.append(" RK03.SYSID, ")
.append(" RK03.MLAD ")
.append(" FROM ")
.append(" CK_T_RRKS RK03 ")
.append(" WHERE ")
//ANK-2928-00-00 MOD START
// .append(" (RK03.RRKS_NO,RK03.RSV_APLY_YMD || RK03.GENE_ADD_DTM) = ")
.append(" (RK03.RSV_APLY_YMD || RK03.GENE_ADD_DTM) = ")
//ANK-2928-00-00 MOD END
.append(" ( ")
.append(" SELECT ")
//ANK-2928-00-00 DEL START
// .append(" RK03_GENE.RRKS_NO, ")
//ANK-2928-00-00 DEL END
.append(" MAX(RK03_GENE.RSV_APLY_YMD || RK03_GENE.GENE_ADD_DTM) AS RK03_MAX ")
.append(" FROM ")
.append(" CK_T_RRKS RK03_GENE ")
.append(" WHERE ")
//ANK-2928-00-00 MOD START
// .append(" RK03_GENE.RRKS_NO = RK03.RRKS_NO AND ")
.append(" RK03_GENE.SYSID = RK03.SYSID AND ")
//ANK-2928-00-00 MOD END
.append(" RK03_GENE.RSV_APLY_YMD <= ? AND ")
.append(" RK03_GENE.RSV_APLY_CD = '2' AND ")
.append(" RK03_GENE.RRK_WAY_CD = '001' AND ")
.append(" RK03_GENE.MK_FLG = '0' ");
// v9.00.00 OM-2014-0002569 ADD START
// 契約者タイプコードが法人の場合
if (keishaTypeCd.equals(JCRStrConst.KEISHA_TYPE_CD_HOJIN))
{
sql_Buff.append(" AND RK03_GENE.RRKS_CD = '01' ");
}
// v9.00.00 OM-2014-0002569 ADD END
//ANK-2928-00-00 MOD START
// sql_Buff.append(" GROUP BY RK03_GENE.RRKS_NO ")
// .append(" ) ")
sql_Buff.append(" ) ")
//ANK-2928-00-00 MOD END
.append(" AND RK03.RRK_WAY_CD = '001' ")
.append(" AND RK03.MK_FLG = '0' ")
.append(" ) RK04 ON ( ")
.append(" RK04.SYSID = CUST.SYSID ")
.append(" ) ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT ")
.append(" RK05.RRKS_NO, ")
.append(" RK05.SYSID, ")
.append(" RK05.MLAD, ")
.append(" RK05.UPD_DTM ")
.append(" FROM ")
.append(" CK_T_RRKS RK05 ")
.append(" WHERE ")
//ANK-2928-00-00 MOD START
// .append(" (RK05.RRKS_NO,RK05.RSV_APLY_YMD || RK05.GENE_ADD_DTM) = ")
.append(" (RK05.RSV_APLY_YMD || RK05.GENE_ADD_DTM) = ")
//ANK-2928-00-00 MOD END
.append(" ( ")
.append(" SELECT ")
//ANK-2928-00-00 DEL START
// .append(" RK05_GENE.RRKS_NO, ")
//ANK-2928-00-00 DEL END
.append(" MAX(RK05_GENE.RSV_APLY_YMD || RK05_GENE.GENE_ADD_DTM) AS RK05_MAX ")
.append(" FROM ")
.append(" CK_T_RRKS RK05_GENE ")
.append(" WHERE ")
//ANK-2928-00-00 MOD START
// .append(" RK05_GENE.RRKS_NO = RK05.RRKS_NO AND ")
.append(" RK05_GENE.SYSID = RK05.SYSID AND ")
//ANK-2928-00-00 MOD END
.append(" RK05_GENE.RSV_APLY_YMD <= ? AND ")
.append(" RK05_GENE.RSV_APLY_CD = '2' AND ")
.append(" RK05_GENE.RRK_WAY_CD = '002' AND ")
.append(" RK05_GENE.MK_FLG = '0' ");
// v9.00.00 OM-2014-0002569 ADD START
// 契約者タイプコードが法人の場合
if (keishaTypeCd.equals(JCRStrConst.KEISHA_TYPE_CD_HOJIN))
{
sql_Buff.append(" AND RK05_GENE.RRKS_CD = '01' ");
}
// v9.00.00 OM-2014-0002569 ADD END
//ANK-2928-00-00 MOD START
// sql_Buff.append(" GROUP BY RK05_GENE.RRKS_NO ")
// .append(" ) ")
sql_Buff.append(" ) ")
//ANK-2928-00-00 MOD END
.append(" AND RK05.RRK_WAY_CD = '002' ")
.append(" AND RK05.MK_FLG = '0' ")
.append(" ) RK06 ON ( ")
.append(" RK06.SYSID = CUST.SYSID ")
.append(" ) ")
//ANK-2928-00-00 ADD START
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT ")
.append(" RK07.RRKS_NO, ")
.append(" RK07.SYSID, ")
.append(" RK07.MLAD, ")
.append(" RK07.UPD_DTM ")
.append(" FROM ")
.append(" CK_T_RRKS RK07 ")
.append(" WHERE ")
.append(" (RK07.RSV_APLY_YMD || RK07.GENE_ADD_DTM) = ")
.append(" ( ")
.append(" SELECT ")
.append(" MAX(RK07_GENE.RSV_APLY_YMD || RK07_GENE.GENE_ADD_DTM) AS RK07_MAX ")
.append(" FROM ")
.append(" CK_T_RRKS RK07_GENE ")
.append(" WHERE ")
.append(" RK07_GENE.SYSID = RK07.SYSID AND ")
.append(" RK07_GENE.RSV_APLY_YMD <= ? AND ")
.append(" RK07_GENE.RSV_APLY_CD = '2' AND ")
.append(" RK07_GENE.RRK_WAY_CD = '006' AND ")
.append(" RK07_GENE.MK_FLG = '0' ");
// 契約者タイプコードが法人の場合
if (keishaTypeCd.equals(JCRStrConst.KEISHA_TYPE_CD_HOJIN))
{
sql_Buff.append(" AND RK07_GENE.RRKS_CD = '01' ");
}
sql_Buff.append(" ) ")
.append(" AND RK07.RRK_WAY_CD = '006' ")
.append(" AND RK07.MK_FLG = '0' ")
.append(" ) RK08 ON ( ")
.append(" RK08.SYSID = CUST.SYSID ")
.append(" ) ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT ")
.append(" RK09.RRKS_NO, ")
.append(" RK09.SYSID, ")
.append(" RK09.MLAD, ")
.append(" RK09.UPD_DTM ")
.append(" FROM ")
.append(" CK_T_RRKS RK09 ")
.append(" WHERE ")
.append(" (RK09.RSV_APLY_YMD || RK09.GENE_ADD_DTM) = ")
.append(" ( ")
.append(" SELECT ")
.append(" MAX(RK09_GENE.RSV_APLY_YMD || RK09_GENE.GENE_ADD_DTM) AS RK09_MAX ")
.append(" FROM ")
.append(" CK_T_RRKS RK09_GENE ")
.append(" WHERE ")
.append(" RK09_GENE.SYSID = RK09.SYSID AND ")
.append(" RK09_GENE.RSV_APLY_YMD <= ? AND ")
.append(" RK09_GENE.RSV_APLY_CD = '2' AND ")
.append(" RK09_GENE.RRK_WAY_CD = '007' AND ")
.append(" RK09_GENE.MK_FLG = '0' ");
// 契約者タイプコードが法人の場合
if (keishaTypeCd.equals(JCRStrConst.KEISHA_TYPE_CD_HOJIN))
{
sql_Buff.append(" AND RK09_GENE.RRKS_CD = '01' ");
}
sql_Buff.append(" ) ")
.append(" AND RK09.RRK_WAY_CD = '007' ")
.append(" AND RK09.MK_FLG = '0' ")
.append(" ) RK10 ON ( ")
.append(" RK10.SYSID = CUST.SYSID ")
.append(" ) ")
//ANK-2928-00-00 ADD END
.append(" WHERE ")
.append(" SV01.SYSID = CUST.SYSID AND ")
.append(" SV01.SVC_KEI_NO = ? AND ")
.append(" (SV01.SVC_KEI_NO,SV01.RSV_APLY_YMD || SV01.GENE_ADD_DTM) = ")
.append(" ( ")
.append(" SELECT ")
.append(" S001_GENE.SVC_KEI_NO, ")
.append(" MAX(S001_GENE.RSV_APLY_YMD || S001_GENE.GENE_ADD_DTM) AS S001_MAX ")
.append(" FROM ")
.append(" KK_T_SVC_KEI S001_GENE ")
.append(" WHERE ")
.append(" S001_GENE.SVC_KEI_NO = SV01.SVC_KEI_NO AND ")
.append(" S001_GENE.RSV_APLY_YMD <= ? AND ")
.append(" S001_GENE.RSV_APLY_CD = '2' AND ")
.append(" S001_GENE.MK_FLG = '0' ")
.append(" GROUP BY S001_GENE.SVC_KEI_NO ")
.append(" ) AND ")
.append(" SV01.PRC_GRP_CD <> '99' AND ")
.append(" SV01.MK_FLG = '0' AND ")
.append(" (CUST.SYSID,CUST.RSV_APLY_YMD || CUST.GENE_ADD_DTM) = ")
.append(" ( ")
.append(" SELECT ")
.append(" CUST_GENE.SYSID, ")
.append(" MAX(CUST_GENE.RSV_APLY_YMD || CUST_GENE.GENE_ADD_DTM) AS CUST_MAX ")
.append(" FROM ")
.append(" CK_T_CUST CUST_GENE ")
.append(" WHERE ")
.append(" CUST_GENE.SYSID = CUST.SYSID AND ")
.append(" CUST_GENE.RSV_APLY_YMD <= ? AND ")
.append(" CUST_GENE.RSV_APLY_CD = '2' AND ")
.append(" CUST_GENE.MK_FLG = '0' ")
.append(" GROUP BY CUST_GENE.SYSID ")
.append(" ) AND ")
.append(" CUST.KEISHA_TYPE_CD <> '5' AND ")
.append(" CUST.MK_FLG = '0' ")
.append(" ORDER BY ")
.append(" SU02.SVC_KEI_UCWK_NO ASC ");
//prepareStatementにSQL文をセット
pstmt = con1.prepareStatement(sql_Buff.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff);
// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
if (JPCModelConstant.FUNC_CD_1.equals( inMsg.getString(EKK0081B509CBSMsg.FUNC_CODE) ) ) {
//-------------------------------------------------------------------------------- 修正 Start --------//
int idx = 1;
// パラメータの設定(運用日を指定)
CAANJDBCUtil.setParam(pstmt, idx++, opeDate);
CAANJDBCUtil.setParam(pstmt, idx++, opeDate);
CAANJDBCUtil.setParam(pstmt, idx++, opeDate);
// パラメータの設定(ＫＥＹ＿サービス契約内訳番号を指定)
if (!(inMsg.isNull(EKK0081B509CBSMsg.KEY_SVC_KEI_UCWK_NO)))
{
CAANJDBCUtil.setParam(pstmt, idx++, inMsg.getObject(EKK0081B509CBSMsg.KEY_SVC_KEI_UCWK_NO));
}
// パラメータの設定(ＫＥＹ＿基準日を指定)
CAANJDBCUtil.setParam(pstmt, idx++, inMsg.getObject(EKK0081B509CBSMsg.KEY_STDARDYMD));
CAANJDBCUtil.setParam(pstmt, idx++, inMsg.getObject(EKK0081B509CBSMsg.KEY_STDARDYMD));
// パラメータの設定(ＫＥＹ＿サービス契約回線内訳番号を指定)
if (!(inMsg.isNull(EKK0081B509CBSMsg.KEY_SVC_KEI_KAISEN_UCWK_NO)))
{
CAANJDBCUtil.setParam(pstmt, idx++, inMsg.getObject(EKK0081B509CBSMsg.KEY_SVC_KEI_KAISEN_UCWK_NO));
}
// パラメータの設定(運用日を指定)
CAANJDBCUtil.setParam(pstmt, idx++, opeDate);
CAANJDBCUtil.setParam(pstmt, idx++, opeDate);
CAANJDBCUtil.setParam(pstmt, idx++, opeDate);
//ANK-2928-00-00 ADD START
CAANJDBCUtil.setParam(pstmt, idx++, opeDate);
CAANJDBCUtil.setParam(pstmt, idx++, opeDate);
//ANK-2928-00-00 ADD END
// パラメータの設定(ＫＥＹ＿サービス契約番号を指定)
CAANJDBCUtil.setParam(pstmt, idx++, inMsg.getObject(EKK0081B509CBSMsg.KEY_SVC_KEI_NO));
// パラメータの設定(運用日を指定)
CAANJDBCUtil.setParam(pstmt, idx++, opeDate);
CAANJDBCUtil.setParam(pstmt, idx++, opeDate);
//-------------------------------------------------------------------------------- 修正 End ----------//
}
// ResultSetの取得
rsltQuery = pstmt.executeQuery();
// EKK0081B509CBSMsgの明細にセットする項目
String[] msgKeyList1 = {
EKK0081B509CBSMsg1List.SVC_KEI_NO
, EKK0081B509CBSMsg1List.SYSID
, EKK0081B509CBSMsg1List.HANDY_TELNO
, EKK0081B509CBSMsg1List.CUST_MLAD
, EKK0081B509CBSMsg1List.RRKS_NO
, EKK0081B509CBSMsg1List.SKK_MLAD
, EKK0081B509CBSMsg1List.RRKS_UPD_DTM_BF
//ANK-2928-00-00 ADD START
, EKK0081B509CBSMsg1List.RRKS_NO2
, EKK0081B509CBSMsg1List.SKK_MLAD2
, EKK0081B509CBSMsg1List.RRKS_UPD_DTM_BF2
, EKK0081B509CBSMsg1List.RRKS_NO3
, EKK0081B509CBSMsg1List.SKK_MLAD3
, EKK0081B509CBSMsg1List.RRKS_UPD_DTM_BF3
//ANK-2928-00-00 ADD END
, EKK0081B509CBSMsg1List.ISP_NINSHO_ID
, EKK0081B509CBSMsg1List.UQ_NINSHO_ID
, EKK0081B509CBSMsg1List.ADSL_NINSHO_ID
, EKK0081B509CBSMsg1List.MSKMSHO_NO
, EKK0081B509CBSMsg1List.PRC_GRP_CD
, EKK0081B509CBSMsg1List.PRC_GRP_NM
, EKK0081B509CBSMsg1List.PCRS_NM
, EKK0081B509CBSMsg1List.SVC_STA_YMD
, EKK0081B509CBSMsg1List.MANSION_OWNR_ID
, EKK0081B509CBSMsg1List.MANSION_OWNR_NM
, EKK0081B509CBSMsg1List.KAISEN_PLACE_PCD
, EKK0081B509CBSMsg1List.KAISEN_PLACE_STATE_NM
, EKK0081B509CBSMsg1List.KAISEN_PLACE_CITY_NM
, EKK0081B509CBSMsg1List.KAISEN_PLACE_OAZTSU_NM
, EKK0081B509CBSMsg1List.KAISEN_PLACE_AZCHO_NM
, EKK0081B509CBSMsg1List.KAISEN_PLACE_BNCHIGO
, EKK0081B509CBSMsg1List.KAISEN_PLACE_ADRTTM
, EKK0081B509CBSMsg1List.KAISEN_PLACE_ADRRM
, EKK0081B509CBSMsg1List.KAISEN_PLACE_TELNO
, EKK0081B509CBSMsg1List.SVC_KEI_STAT
, EKK0081B509CBSMsg1List.SVC_KEI_UCWK_STAT
};
// 明細に処理結果(ResultSet)をマッピング
CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0081B509CBSMsg1List",0);
// inMsg(CBSMsg)に明細をセット
inMsg.set("EKK0081B509CBSMsg1List", outMsg1);
//-------------------------------------------------------------------------------- 修正 Start --------//
// 検索エラーフラグの設定
if (outMsg1.length == 0)
{
inMsg.set(EKK0081B509CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_ZERO);
}
//-------------------------------------------------------------------------------- 修正 End ----------//
} catch(SQLException e) {
inMsg.set(EKK0081B509CBSMsg.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(EKK0081B509CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
}
}
}
// v9.00.00 OM-2014-0002569 ADD START
/**
* 契約者タイプコードを取得します。
* @param inCBSMsg CBSメッセージ
* @param inContext エージェントディスパッチコンテキスト
* @param opeDate 運用日付
* @return 契約者タイプコード
*/
private String getKeishaTypeCd(CAANMsg inCBSMsg, AgentDispatchContext inContext, String opeDate)
{
String keishaTypeCd = "";
// コネクション
Connection con1 = null;
// プリペアステートメント
PreparedStatement pstmt = null;
// リザルトセット
ResultSet rsltQuery = null;
try
{
//コネクション取得
con1 = JSYejbConnection.getConnection(CK0011ETMsg.getTableName());
StringBuffer sql_Buff = new StringBuffer();
sql_Buff.append(" SELECT CUST.KEISHA_TYPE_CD ")
.append(" FROM KK_T_SVC_KEI SVCK, ")
.append(" CK_T_CUST CUST ")
.append(" WHERE SVCK.SYSID = CUST.SYSID ")
.append(" AND SVCK.SVC_KEI_NO = ? ")
.append(" AND (SVCK.SVC_KEI_NO,SVCK.RSV_APLY_YMD || SVCK.GENE_ADD_DTM) = ")
.append(" (SELECT SVCK_GENE.SVC_KEI_NO, ")
.append(" MAX(SVCK_GENE.RSV_APLY_YMD || SVCK_GENE.GENE_ADD_DTM) AS SVCK_MAX ")
.append(" FROM KK_T_SVC_KEI SVCK_GENE ")
.append(" WHERE SVCK_GENE.SVC_KEI_NO = SVCK.SVC_KEI_NO ")
.append(" AND SVCK_GENE.RSV_APLY_YMD <= ? ")
.append(" AND SVCK_GENE.RSV_APLY_CD = '2' ")
.append(" AND SVCK_GENE.MK_FLG = '0' ")
.append(" GROUP BY SVCK_GENE.SVC_KEI_NO ")
.append(" ) ")
.append(" AND SVCK.PRC_GRP_CD <> '99' ")
.append(" AND (CUST.SYSID,CUST.RSV_APLY_YMD || CUST.GENE_ADD_DTM) = ")
.append(" (SELECT CUST_GENE.SYSID, ")
.append(" MAX(CUST_GENE.RSV_APLY_YMD || CUST_GENE.GENE_ADD_DTM) AS CUST_MAX ")
.append(" FROM CK_T_CUST CUST_GENE ")
.append(" WHERE CUST_GENE.SYSID = CUST.SYSID ")
.append(" AND CUST_GENE.RSV_APLY_YMD <= ? ")
.append(" AND CUST_GENE.RSV_APLY_CD = '2' ")
.append(" AND CUST_GENE.MK_FLG = '0' ")
.append(" GROUP BY CUST_GENE.SYSID ")
.append(" ) ")
.append(" AND CUST.KEISHA_TYPE_CD <> '5' ");
//prepareStatementにSQL文をセット
pstmt = con1.prepareStatement(sql_Buff.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff);
// パラメータの設定
CAANJDBCUtil.setParam(pstmt, 1, inCBSMsg.getObject(EKK0081B509CBSMsg.KEY_SVC_KEI_NO));
CAANJDBCUtil.setParam(pstmt, 2, opeDate);
CAANJDBCUtil.setParam(pstmt, 3, opeDate);
// ResultSetの取得
rsltQuery = pstmt.executeQuery();
// 戻り値の判定
if (rsltQuery.next())
{
keishaTypeCd = rsltQuery.getString(CK0011ETMsg.KEISHA_TYPE_CD);
}
}
catch(SQLException e)
{
inCBSMsg.set(EKK0081B509CBSMsg.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)
{
inCBSMsg.set(EKK0081B509CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
}
}
return keishaTypeCd;
}
// v9.00.00 OM-2014-0002569 ADD END
}