/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名：eo顧客基幹システム * モジュール名：JSYejbEKK0081B004TPDA * ソースファイル名：JSYejbEKK0081B004TPDA.java * 作成者：EK912022 * 日付：2013年02月06日 *＜機能概要＞ * サービス契約一覧照会（SYSID）2テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン修正日修正者修正内容 * ｖ1.00.00 * v4.00.00 2013/01/21 FJ)沖田 ST1-2012-0000846 * ｖ4.00.01 2013/02/01 FJ）沖田 IT1-2013-0000186 * ｖ4.00.02 2013/02/06 FJ)坂本 ST1-2012-0000846 * ｖ4.00.03 2013/02/06 FJ)坂本 IT2-2013-0000090 * ｖ5.00.00 2013/03/22 FJ)寺園 OT-2013-0000311 * ｖ5.00.01 2013/07/07 FJ)竹内 IT2-2013-0000575 * ｖ5.00.02 2013/08/10 FJ)寺本 IK1-2013-0000152 * ｖ5.00.03 2013/10/29 FJ)寺園 OM-2013-0003575 * ｖ5.00.04 2013/11/08 FJ)寺園 OM-2013-0003984 * ｖ6.00.00 2013/12/28 FJ)寺園 OM-2013-0005112 * v10.00.00 2014/10/10 FJ)伊藤 ANK-2304-00-00 * v11.00.00 2014/12/02 FJ)谷口 ANK-2248-01-00 * v12.00.00 2015/02/20 FJ)藤本 ANK-2302-00-00 * v29.00.00 2017/03/03 FJ)柳【ANK-2975-00-00】(客B)検索キーに該当するサービスのソート * v41.00.00 2019/02/04 FJ)舘山【ANK-3387-00-00】マンションタイプ光コンセントサービス導入 * v53.00.00 2021/04/12 FJ)宮下【ANK-3989-00-00】マンション光コンセント方式への5G・10Gコース追加 * v53.00.01 2021/04/30 GDC)ジョバニー【ANK-4009-00-00】CX戦略WG方針対応（異動シェア分・API） * v53.00.02 2021/06/07 FJ)星野【ANK-4009-16-00】【CX戦略WG方針対応】「開通前後判定フラグ」の判定条件変更 * v53.00.03 2021/06/17 FJ)星野 ST-2021-0000012 * v60.00.00 2022/06/09 FJ)原【ANK-4244-00-00】【eo定期】MT既設賃貸対応 * v61.00.00 2022/12/09 FJ)宇野 ANK-4315-00-00_【eo定期】 eoホームゲートウェイ導入対応 * v64.00.00 2023/01/06 FJ)張本【ANK-4316-00-00】ｅｏアプリ対応 * v71.00.00 2024/03/04 GDC)v.auditor ANK-4543-00-00 NTT卸対応：Step0 * v71.00.00 2023/12/12 GDC)v.auditor ANK-4468-00-00 _eo光ネット「シンプルプラン」追加対応 * v73.00.00 2024/09/17 GDC)j.tagongtong ANK-4427-00-00 NTT卸対応 * v73.00.00 2024/09/17 FJ)菅原 ST-2025-0000002 NTT卸対応 ********************************************************************************/ package eo.ejb.cbs.sqlf; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; 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.JPCModelConstant; import eo.common.util.JKKStringUtil; import eo.ejb.cbm.entity.KK0081ETMsg; import eo.ejb.cbs.cbsmsg.EKK0081B004CBSMsg; import eo.ejb.cbs.cbsmsg.EKK0081B004CBSMsg1List; import eo.ejb.common.JKKModelCommon; import eo.ejb.common.JKKModelConst; import eo.ejb.common.JKKejbPagingUtil; import eo.ejb.common.edit.JKKejbEditCommon; import eo.common.constant.JKKStrConst; /** * * テンプレートDBアクセス部品　EKK0081B004_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEKK0081B004TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * ソート項目用（料金グループコード) */ private static final String SORT_PRC_GRP_CD = "1"; /** * ソート項目用（料金コースコード) */ private static final String SORT_PRCS_CD= "2"; /** * ソート項目用（住所関連) */ private static final String SORT_ADD_CONNECT = "3"; /** * ソート項目用（サービス契約ステータス) */ private static final String SORT_SVC_KEI_STAT = "4"; /** * ソート項目用（サービス契約番号) */ private static final String SORT_SVC_KEI_NO = "5"; /** * ソート項目用（電話番号1) */ private static final String SORT_TEL1 = "6"; /** * ソート項目用（電話番号2) */ private static final String SORT_TEL2 = "7"; /** * ソート項目用（申込年月日) */ private static final String SORT_MSKM_YMD = "8"; /** * ソート項目用（申込書番号) */ private static final String SORT_MSKMSHO_NO = "9"; /** * ソート項目用（申込明細番号) */ private static final String SORT_MSKM_DTL_NO = "10"; /** * ソート項目用（請求契約番号) */ private static final String SORT_SEIKYU_KEI_NO = "11"; /** * ソート項目用（工事案件番号) */ private static final String SORT_KOJI_ANK_NO = "12"; /** * ソート項目用（同番移転可否) */ private static final String SORT_DOBAN_ITEN_KAHI = "13"; /** * ソート項目用（同番移転ステータス) */ private static final String SORT_DOBAN_ITEN_STAT = "14"; /** * ソート項目用（同番移転ステータス) */ private static final String SORT_SEIRI_NO = "15"; /** * ソート項目用（同番移転可否２) */ private static final String SORT_DOBAN_ITEN_KAHI_2 = "16"; /** * ソート項目用（申込書明細番号) */ private static final String SORT_MSKMSHO_DTL_NO = "17"; /** * ソート区分用（昇順) */ private static final String SORT_ASC = "1"; /** * ソート区分用（降順) */ private static final String SORT_DESC = "2"; /** * コンストラクタ */ public JSYejbEKK0081B004TPDA() { } /** * EKK0081B004_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void */ public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){ // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt_Count1 = null; PreparedStatement pstmt_Page1 = null; PreparedStatement pstmt_Count2 = null; PreparedStatement pstmt_Page2 = null; // リザルトセット ResultSet rsltQuery_Count1 = null; ResultSet rsltQuery_Page1 = null; ResultSet rsltQuery_Count2 = null; ResultSet rsltQuery_Page2 = null; // パラメータのカウント int iPCnt = 0; // 検索条件設定フラグ boolean keyFlg = false; // 検索条件(特定ID)が指定されているか判定 if(!inMsg.isNull(EKK0081B004CBSMsg.KEY_MSKMSHO_NO) || !inMsg.isNull(EKK0081B004CBSMsg.KEY_KIKI_SEIZO_NO) || !inMsg.isNull(EKK0081B004CBSMsg.KEY_KOJIAK_NO) || !inMsg.isNull(EKK0081B004CBSMsg.KEY_SVC_KEI_NO) || // ANK-2975-00-00 2017/03/03 ADD START !inMsg.isNull(EKK0081B004CBSMsg.KEY_SEIKY_KEI_NO) || !inMsg.isNull(EKK0081B004CBSMsg.KEY_MSKMSHO_DTL_NO) || !inMsg.isNull(EKK0081B004CBSMsg.KEY_SEIRI_NO) || // ANK-2975-00-00 2017/03/03 ADD END !inMsg.isNull(EKK0081B004CBSMsg.KEY_MSKM_DTL_NO)){ // 検索条件(特定ID)が指定されている。 keyFlg = true; } /******************** * SQL文の作成 ********************/ try{ //コネクション取得 con1 = JSYejbConnection.getConnection(KK0081ETMsg.getTableName()); // SQL文の作成(ページングヘッダ) // (1) StringBuffer sql_Buff1 = new StringBuffer(); sql_Buff1.append("SELECT * ") .append("FROM ( ") .append(" SELECT ALL_SELECT.*, ROWNUM AS LINE ") .append(" FROM ( "); // SQL文の作成(件数取得用) // (2) StringBuffer sql_Buff2 = new StringBuffer(); sql_Buff2.append(" SELECT ") .append(" COUNT(*) AS CNT "); // SQL文の作成(項目取得) // (3) StringBuffer sql_Buff3 = new StringBuffer(); sql_Buff3.append(" SELECT ") .append(" KK0251.KAISEN_PLACE_STATE_NM ") .append(" , KK0251.KAISEN_PLACE_CITY_NM ") .append(" , KK0251.KAISEN_PLACE_OAZTSU_NM ") .append(" , KK0251.KAISEN_PLACE_AZCHO_NM ") .append(" , KK0251.KAISEN_PLACE_BNCHIGO ") .append(" , KK0251.KAISEN_PLACE_ADRTTM ") .append(" , KK0251.KAISEN_PLACE_ADRRM ") .append(" , KK0081.SVC_KEI_STAT ") .append(" , (SELECT ZM0171_01.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_01 ") .append(" WHERE ZM0171_01.CD_SBT_CD = 'CD01586' ") .append(" AND ZM0171_01.CD_DIV = ( ") .append(" SELECT ") .append(" CASE WHEN KK0081_02.SVC_KEI_STAT = '210' ") .append(" AND KK0081_02.PAUSE_STP_CD = '02' ") .append(" THEN '211' ") .append(" WHEN KK0081_02.SVC_KEI_STAT = '910' ") .append(" AND KK0081_02.SVC_DLRE_CD = '02' ") .append(" THEN '911' ") .append(" ELSE KK0081_02.SVC_KEI_STAT ") .append(" END DISP_SVC_KEI_STAT ") .append(" FROM KK_T_SVC_KEI KK0081_02 ") .append(" WHERE KK0081_02.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" AND KK0081_02.GENE_ADD_DTM = KK0081.GENE_ADD_DTM ") .append(" ) ") .append(" AND ZM0171_01.MK_FLG = '0') AS SVC_KEI_STAT_NM ") .append(" , KK0081.SVC_KEI_NO ") // ANK-4427-00-00 MOD START // .append(" , KK0161_01.TELNO AS TELNO1 ") .append(" , CASE WHEN ") .append(" ( SELECT KK0791.NTTORS_DIV ") .append(" FROM KK_M_PCRS KK0791 ") .append(" WHERE KK0791.PCRS_CD = KK0081.PCRS_CD ") .append(" AND KK0791.PCRS_TSTAYMD <= ? AND KK0791.PCRS_TENDYMD >= ? ") .append(" AND (KK0791.PCRS_CD, KK0791.RSV_APLY_YMD || KK0791.GENE_ADD_DTM) = ") .append(" ( SELECT KK0791_GENE.PCRS_CD, MAX(KK0791_GENE.RSV_APLY_YMD || KK0791_GENE.GENE_ADD_DTM) AS KK0791_MAX ") .append(" FROM KK_M_PCRS KK0791_GENE ") .append(" WHERE KK0791_GENE.PCRS_CD = KK0791.PCRS_CD ") .append(" AND KK0791_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0791_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0791_GENE.PCRS_CD ) ") .append(" ) IS NOT NULL THEN ") // ST-2025-0000002 MOD START // .append(" (CASE WHEN KK0351_01.TYPEN_TELNO IS NOT NULL THEN KK0351_01.TYPEN_TELNO WHEN KK0351_02.TYPEN_TELNO IS NOT NULL THEN KK0351_02.TYPEN_TELNO END) ") .append(" NVL( ") .append(" ( ") .append(" SELECT ") .append(" CASE WHEN (KK0361_01.TYPEN_TELNO IS NULL ") .append(" OR KK0361_01.TYPEN_TELNO = '') THEN '(' || ZM0171_01.CD_DIV_NM || ')' ") .append(" ELSE KK0361_01.TYPEN_TELNO || ' (' || ZM0171_01.CD_DIV_NM || ')' END AS TYPEN_TELNO ") .append(" FROM KK_T_OP_SVC_KEI KK0351_TMP_01 ") .append(" INNER JOIN KK_T_OPSVKEI_ISP KK0361_01 ") .append(" ON KK0351_TMP_01.OP_SVC_KEI_NO = KK0361_01.OP_SVC_KEI_NO ") .append(" AND KK0351_TMP_01.GENE_ADD_DTM = KK0361_01.GENE_ADD_DTM ") .append(" INNER JOIN ZM_M_CD_NM_KANRI ZM0171_01 ") .append(" ON KK0351_TMP_01.OP_SVC_KEI_STAT = ZM0171_01.CD_DIV ") .append(" AND ZM0171_01.CD_SBT_CD = 'CD00037' ") .append(" WHERE ") .append(" KK0351_TMP_01.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" AND (KK0351_TMP_01.RSV_APLY_YMD || KK0351_TMP_01.GENE_ADD_DTM) = ( ") .append(" SELECT MAX(KK0351_GENE_01.RSV_APLY_YMD || KK0351_GENE_01.GENE_ADD_DTM) AS KK0351_GENE_01_MAX ") .append(" FROM KK_T_OP_SVC_KEI KK0351_GENE_01 ") .append(" WHERE KK0351_GENE_01.OP_SVC_KEI_NO = KK0351_TMP_01.OP_SVC_KEI_NO ") .append(" AND KK0351_GENE_01.SVC_KEI_NO = KK0351_TMP_01.SVC_KEI_NO ") .append(" AND KK0351_GENE_01.RSV_APLY_CD = '2' ") .append(" AND KK0351_GENE_01.RSV_APLY_YMD <= ? ") .append(" AND KK0351_GENE_01.MK_FLG = '0') ") .append(" AND KK0351_TMP_01.OP_SVC_CD = 'B138' ") .append(" AND KK0351_TMP_01.OP_SVC_KEI_STAT NOT IN ('910', '920') ") .append(" AND ROWNUM <= 1 ") .append(" ), ") .append(" ( ") .append(" SELECT ") .append(" CASE WHEN (KK0361_02.TYPEN_TELNO IS NULL ") .append(" OR KK0361_02.TYPEN_TELNO = '') THEN '(' || ZM0171_02.CD_DIV_NM || ')' ") .append(" ELSE KK0361_02.TYPEN_TELNO || ' (' || ZM0171_02.CD_DIV_NM || ')' END AS TYPEN_TELNO ") .append(" FROM KK_T_OP_SVC_KEI KK0351_TMP_02 ") .append(" INNER JOIN (SELECT KK0351_TMP_03.SVC_KEI_NO , ") .append(" MAX(KK0351_TMP_03.RSV_APLY_YMD || KK0351_TMP_03.GENE_ADD_DTM) AS KK0351_TMP_03_MAX ") .append(" FROM KK_T_OP_SVC_KEI KK0351_TMP_03 ") .append(" WHERE (KK0351_TMP_03.RSV_APLY_YMD || KK0351_TMP_03.GENE_ADD_DTM) = ( ") .append(" SELECT MAX(KK0351_GENE_02.RSV_APLY_YMD || KK0351_GENE_02.GENE_ADD_DTM) AS KK0351_GENE_02_MAX ") .append(" FROM KK_T_OP_SVC_KEI KK0351_GENE_02 ") .append(" WHERE KK0351_GENE_02.OP_SVC_KEI_NO = KK0351_TMP_03.OP_SVC_KEI_NO ") .append(" AND KK0351_GENE_02.SVC_KEI_NO = KK0351_TMP_03.SVC_KEI_NO ") .append(" AND KK0351_GENE_02.RSV_APLY_CD = '2' ") .append(" AND KK0351_GENE_02.RSV_APLY_YMD <= ? ") .append(" AND KK0351_GENE_02.MK_FLG = '0') ") .append(" AND KK0351_TMP_03.OP_SVC_CD = 'B138' ") .append(" AND KK0351_TMP_03.OP_SVC_KEI_STAT = '910' ") .append(" GROUP BY KK0351_TMP_03.SVC_KEI_NO) KK0351_TMP_04 ") .append(" ON KK0351_TMP_02.SVC_KEI_NO = KK0351_TMP_04.SVC_KEI_NO ") .append(" AND KK0351_TMP_02.RSV_APLY_YMD || KK0351_TMP_02.GENE_ADD_DTM = KK0351_TMP_04.KK0351_TMP_03_MAX ") .append(" INNER JOIN KK_T_OPSVKEI_ISP KK0361_02 ") .append(" ON KK0351_TMP_02.OP_SVC_KEI_NO = KK0361_02.OP_SVC_KEI_NO ") .append(" AND KK0351_TMP_02.GENE_ADD_DTM = KK0361_02.GENE_ADD_DTM ") .append(" INNER JOIN ZM_M_CD_NM_KANRI ZM0171_02 ") .append(" ON KK0351_TMP_02.OP_SVC_KEI_STAT = ZM0171_02.CD_DIV ") .append(" AND ZM0171_02.CD_SBT_CD = 'CD00037' ") .append(" WHERE ") .append(" KK0351_TMP_02.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" AND ROWNUM <= 1 ") .append(" ) ") .append(" ) ") // ST-2025-0000002 MOD END .append(" ELSE KK0161_01.TELNO") .append(" END AS TELNO1") // ANK-4427-00-00 MOD END .append(" , KK0161_02.TELNO AS TELNO2 ") .append(" , KK0011.MSKM_YMD ") .append(" , KK0011.MSKM_NO ") .append(" , KK0321.SEIKY_KEI_NO ") // ANK-2248-01-00 2014/12/02 ADD START .append(" , CASE WHEN KK0441.SEIOPSVC_KEI_STAT = '100' ") .append(" AND KK0781.NHK_DNTIKTKEI_TG_FLG = '1' ") .append(" THEN '有' ") .append(" WHEN KK0781.NHK_DNTIKTKEI_TG_FLG = '0' ") .append(" THEN '' ") .append(" ELSE '無' ") .append(" END DISP_NHKDNTIKT_UMU ") // ANK-2248-01-00 2014/12/02 ADD END .append(" , (SELECT KU0081.KOJIAK_NO ") .append(" FROM KU_T_SVKEI_KOJIAK KU0081 ") .append(" WHERE KU0081.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" AND KU0081.SVKEI_KOJIAK_TSTAYMD || KU0081.KOJIAK_NO = ( ") .append(" SELECT MAX(KU0081_01.SVKEI_KOJIAK_TSTAYMD || KU0081_01.KOJIAK_NO) ") .append(" FROM KU_T_SVKEI_KOJIAK KU0081_01 ") .append(" WHERE KU0081_01.SVC_KEI_NO = KU0081.SVC_KEI_NO ") .append(" AND KU0081_01.SVKEI_KOJIAK_TSTAYMD <= ? ") .append(" AND KU0081_01.SVKEI_KOJIAK_TENDYMD >= ? ") .append(" AND KU0081_01.MK_FLG = '0' ") .append(" AND EXISTS (SELECT * FROM KU_T_KOJIAK KU0011 ") .append(" WHERE KU0011.KOJIAK_NO = KU0081_01.KOJIAK_NO ") // ANK-4315-00-00 MOD STR // .append(" AND KU0011.KOJIAK_SBT_CD IN ('001','003','008','009','011','012') ") .append(" AND KU0011.KOJIAK_SBT_CD IN ('001','003','008','009','011','012','015') ") // ANK-4315-00-00 MOD END .append(" AND KU0011.KOJIAK_STAT <> '900' ") .append(" AND KU0011.MK_FLG = '0'))) AS KOJIAK_NO ") .append(" , TU0021.DOBANITEN_KH ") .append(" , (SELECT ZM0171_02.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_02 ") .append(" WHERE ZM0171_02.CD_SBT_CD = 'CD00658' ") .append(" AND ZM0171_02.CD_DIV = TU0021.DOBANITEN_KH ") .append(" AND ZM0171_02.MK_FLG = '0') AS DOBANITEN_KH_NM ") .append(" , TU0021.DOBANITEN_STAT ") .append(" , KK0161_01.DOBANITEN_REQ_CTRL_CD ") .append(" , TU0021_2.DOBANITEN_KH AS DOBANITEN_KH_2 ") .append(" , (SELECT ZM0171_03.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_03 ") .append(" WHERE ZM0171_03.CD_SBT_CD = 'CD00658' ") .append(" AND ZM0171_03.CD_DIV = TU0021_2.DOBANITEN_KH ") .append(" AND ZM0171_03.MK_FLG = '0') AS DOBANITEN_KH_NM_2 ") .append(" , TU0021_2.DOBANITEN_STAT AS DOBANITEN_STAT_2 ") .append(" , KK0161_02.DOBANITEN_REQ_CTRL_CD AS DOBANITEN_REQ_CTRL_CD_2 ") .append(" , KK0081.PRC_GRP_CD ") .append(" , KK0021_01.MSKMSHO_NO ") .append(" , KK0081.SHOSA_YMD ") // ANK-4543-00-00 MOD START // .append(" , KK0781.PRC_GRP_NM ") .append(" ,CASE ") .append(" WHEN (KK0081.SVC_KEI_NO IN (SELECT KK3511.SVC_KEI_NO FROM KK_T_NTTORS_KEI KK3511 WHERE KK3511.MK_FLG = '0') ) ") .append(" THEN (SELECT KK3521.PRC_GRP_NM FROM KK_M_NTTORS_PCRS KK3521 INNER JOIN KK_T_NTTORS_KEI KK3511 ") .append(" ON KK3511.NTTORS_SVC_SBT = KK3521.NTTORS_SVC_SBT WHERE KK3511.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" AND KK3511.GENE_ADD_DTM =(SELECT MAX(GENE_ADD_DTM) ") .append(" FROM KK_T_NTTORS_KEI KK3511_GENE ") .append(" WHERE KK3511.SVC_KEI_NO = KK3511_GENE.SVC_KEI_NO ") .append(" AND KK3511.MK_FLG = 0 ") .append(" GROUP BY SVC_KEI_NO )) ") // .append(" ) ELSE ( KK0781.PRC_GRP_NM ") // ANK-4468-00-00 ADD START .append(" WHEN ( KK0081.PRC_GRP_CD = '02' ") .append(" AND KK0081.PCRS_CD IN ('A97', 'A99') ") .append(" ) THEN ( SELECT ZM0321.WORK_PARAM_SETTE_VALUE ") .append(" FROM ZM_M_WORK_PARAM_KNRI ZM0321 ") .append(" WHERE ZM0321.WORK_PARAM_ID = 'KK_SIMPL_C_GRPNM_01' ") .append(" ) ELSE ( KK0781.PRC_GRP_NM ") // ANK-4468-00-00 ADD END .append(" ) END AS PRC_GRP_NM ") // ANK-4543-00-00 MOD END .append(" , KK0781.ID_TCHISHO_PRT_YO_PRGP_NM ") .append(" , KK0791.PCRS_NM ") .append(" , KK0791.ID_TCHISHO_PRT_YO_PCRS_NM ") // 20141010 ANK-2304-00-00 Update Start // .append(" , KK0591.PPLAN_NM ") .append(" , CASE WHEN KK0591.PCRS_CD = 'A37' AND ") .append(" KK0481.SVC_KEI_NO IS NOT NULL THEN ") .append(" '").append(JKKModelConst.EO_TV_COURSE_BS_STAR_NM).append("' ") // 地デジ・ＢＳスタチャンセットコース // ▽▽▽ ANK-3387-00-00 Modify START // .append(" ELSE KK0591.PPLAN_NM ") .append(" WHEN KK0891.TK_HOSHIKI_CD = '008' AND KK0891.TK_HOSHIKI_PATTERN_CD = '51' ") // ▽▽▽ ANK-3989-00-00 MOD START // .append(" THEN REPLACE(KK0591.PPLAN_NM, '") // .append(JKKStrConst.HIKARI_CONCNT_CKAN_BF_WORD).append("', '") // .append(JKKStrConst.HIKARI_CONCNT_CKAN_AF_WORD).append("') ") .append(" THEN CASE WHEN KK0891.TONAI_SPEED_CD = '008' AND KK0081.SVC_CD = '01' ") .append(" THEN '") .append(JKKStrConst.HIKARI_CONCNT_CKAN_AF_WORD_5G).append("' ") .append(" WHEN KK0891.TONAI_SPEED_CD = '009' AND KK0081.SVC_CD = '01' ") .append(" THEN '") .append(JKKStrConst.HIKARI_CONCNT_CKAN_AF_WORD_10G).append("' ") .append(" ELSE REPLACE(KK0591.PPLAN_NM, '") .append(JKKStrConst.HIKARI_CONCNT_CKAN_BF_WORD).append("', '") .append(JKKStrConst.HIKARI_CONCNT_CKAN_AF_WORD).append("') ") .append(" END ") // ▲▲▲ ANK-3989-00-00 MOD END // ANK-4244-00-00 ADD START .append(" WHEN KK0891.TK_HOSHIKI_CD = '008' AND KK0891.TK_HOSHIKI_PATTERN_CD = '50' AND KK0881.HHS_1GONLY_FLG = '1'") .append(" THEN CASE WHEN KK0891.TONAI_SPEED_CD = '008' AND KK0081.SVC_CD = '01' ") .append(" THEN '") .append(JKKStrConst.HIKARI_CKAN_AF_WORD_5G).append("' ") .append(" WHEN KK0891.TONAI_SPEED_CD = '009' AND KK0081.SVC_CD = '01' ") .append(" THEN '") .append(JKKStrConst.HIKARI_CKAN_AF_WORD_10G).append("' ") .append(" ELSE KK0591.PPLAN_NM ") .append(" END ") // ANK-4244-00-00 ADD END // ANK-4543-00-00 ADD START .append(" WHEN (KK0081.SVC_KEI_NO IN (SELECT KK3511.SVC_KEI_NO FROM KK_T_NTTORS_KEI KK3511 WHERE KK3511.MK_FLG = '0') ) ") .append(" THEN (SELECT KK3521.PPLAN_NM FROM KK_M_NTTORS_PCRS KK3521 INNER JOIN KK_T_NTTORS_KEI KK3511 ") .append(" ON KK3511.NTTORS_SVC_SBT = KK3521.NTTORS_SVC_SBT WHERE KK3511.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" AND KK3511.GENE_ADD_DTM =(SELECT MAX(GENE_ADD_DTM) ") .append(" FROM KK_T_NTTORS_KEI KK3511_GENE ") .append(" WHERE KK3511.SVC_KEI_NO = KK3511_GENE.SVC_KEI_NO ") .append(" AND KK3511.MK_FLG = 0 ") .append(" GROUP BY SVC_KEI_NO) )") // ANK-4543-00-00 ADD END // ANK-4468-00-00 ADD START .append(" WHEN KK0081.PRC_GRP_CD = '02' AND KK0081.PCRS_CD IN ('A97', 'A99') ") .append(" THEN CASE WHEN KK0081.PCRS_CD = 'A97' ") .append(" THEN ( SELECT ZM0321.WORK_PARAM_SETTE_VALUE ") .append(" FROM ZM_M_WORK_PARAM_KNRI ZM0321 ") .append(" WHERE ZM0321.WORK_PARAM_ID = 'KK_SIMPL_C_PCRSNM_02' )") .append(" WHEN KK0081.PCRS_CD = 'A99' ") .append(" THEN ( SELECT ZM0321.WORK_PARAM_SETTE_VALUE ") .append(" FROM ZM_M_WORK_PARAM_KNRI ZM0321 ") .append(" WHERE ZM0321.WORK_PARAM_ID = 'KK_SIMPL_C_PCRSNM_03' )") .append(" ELSE KK0591.PPLAN_NM ") .append(" END ") // ANK-4468-00-00 ADD END .append(" ELSE KK0591.PPLAN_NM ") // ▲▲▲ ANK-3387-00-00 Modify END .append(" END AS PPLAN_NM ") // 20141010 ANK-2304-00-00 Update End .append(" , KK0591.ID_TCHISHO_PRT_YO_PPLAN_NM ") .append(" , KK0081.SVC_CD ") .append(" , KK0021_01.MSKMSHO_DTL_NO ") .append(" , KK0081.SEIRI_NO ") .append(" , KK0011.MSKM_STAT ") .append(" , KK0081.SHOSA_DSL_FIN_CD ") .append(" , KK0081.IDO_DIV ") // ANK-4009-00-00 ADD START .append(" , KK0081.SVC_USE_STA_KIBO_YMD ") .append(" , KK0081.SVC_STA_YMD ") .append(" , KK0891.TK_HOSHIKI_CD ") .append(" , KK0891.TK_HOSHIKI_PATTERN_CD ") // ANK-4009-00-00 ADD END // ANK-4009-16-00 ADD START .append(" , KK0081.PCRS_CD ") // ANK-4009-16-00 ADD END // ANK-4316-00-00 ADD START .append(" , KK0021_01.MSKM_DTL_NO ") .append(" , KK0251.SVC_KEI_KAISEN_UCWK_NO ") // ANK-4316-00-00 ADD END ; // SQL文の作成(FROM句/WHERE句)(基本部条件) // (4) StringBuffer sql_Buff4 = new StringBuffer(); StringBuffer sql_Buff5 = new StringBuffer(); StringBuffer sql_Buff6 = new StringBuffer(); if ( "1".equals( inMsg.getObject(EKK0081B004CBSMsg.FUNC_CODE).toString() ) // ST-2021-0000012対応 20210617 星野 ADD START || "2".equals( inMsg.getObject(EKK0081B004CBSMsg.FUNC_CODE).toString() ) // ST-2021-0000012対応 20210617 星野 ADD END ) { // FROM句 sql_Buff4.append(" FROM ") .append(" KK_T_SVC_KEI KK0081 ") .append(" INNER JOIN ") .append(" (SELECT KK0021.MSKM_NO ") .append(" ,KK0021.MSKM_DTL_NO ") .append(" ,KK0021.MSKMSHO_NO ") .append(" ,KK0021.MSKMSHO_DTL_NO ") .append(" ,KK0081_01.SVC_KEI_NO ") .append(" FROM KK_T_MSKM_DTL KK0021 ") .append(" INNER JOIN KK_T_SVC_KEI KK0081_01 ") .append(" ON KK0081_01.MSKM_DTL_NO = KK0021.MSKM_DTL_NO ") .append(" WHERE (KK0021.MSKM_DTL_NO, KK0021.GENE_ADD_DTM) = ") .append(" (SELECT KK0021_GENE.MSKM_DTL_NO, MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX ") .append(" FROM KK_T_MSKM_DTL KK0021_GENE ") .append(" WHERE KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO ") .append(" AND KK0021_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0021_GENE.MSKM_DTL_NO) ") .append(" AND (KK0021.MSKM_DTL_CANCEL_RSN_CD IS NULL ") .append(" OR (KK0021.MSKM_DTL_CANCEL_RSN_CD <> '10' ") .append(" AND KK0021.MSKM_DTL_CANCEL_RSN_CD <> '20')) ") .append(" AND KK0081_01.GENE_ADD_DTM = ") .append(" (SELECT MIN(KK0081_01_GENE.GENE_ADD_DTM) AS KK0081_01_MIN ") .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.MK_FLG = '0') ") .append(" ) KK0021_01 ") .append(" ON KK0081.SVC_KEI_NO = KK0021_01.SVC_KEI_NO ") .append(" INNER JOIN KK_T_MSKM KK0011 ") .append(" ON KK0021_01.MSKM_NO = KK0011.MSKM_NO ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0241_TMP.SVC_KEI_NO , ") .append(" KK0241_TMP.SVC_KEI_KAISEN_UCWK_NO ") .append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_TMP ") .append(" WHERE KK0241_TMP.KAISEN_UCWK_USE_STAYMD <= ? ") .append(" AND (KK0241_TMP.SVC_KEI_NO, KK0241_TMP.KAISEN_UCWK_USE_ENDYMD || KK0241_TMP.SVC_KEI_KAISEN_UCWK_NO) = ") .append(" (SELECT KK0241_01.SVC_KEI_NO, MAX(KK0241_01.KAISEN_UCWK_USE_ENDYMD || KK0241_01.SVC_KEI_KAISEN_UCWK_NO) ") .append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_01 ") .append(" WHERE KK0241_01.SVC_KEI_NO = KK0241_TMP.SVC_KEI_NO ") .append(" AND KK0241_01.KAISEN_UCWK_USE_STAYMD <= ? ") .append(" AND KK0241_01.MK_FLG = '0' ") .append(" GROUP BY KK0241_01.SVC_KEI_NO) ") .append(" AND KK0241_TMP.MK_FLG = '0' ") .append(" ) KK0241 ") .append(" ON KK0081.SVC_KEI_NO = KK0241.SVC_KEI_NO ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0251_TMP.SVC_KEI_KAISEN_UCWK_NO , ") .append(" KK0251_TMP.KAISEN_PLACE_STATE_NM , ") .append(" KK0251_TMP.KAISEN_PLACE_CITY_NM , ") .append(" KK0251_TMP.KAISEN_PLACE_OAZTSU_NM , ") .append(" KK0251_TMP.KAISEN_PLACE_AZCHO_NM , ") .append(" KK0251_TMP.KAISEN_PLACE_BNCHIGO , ") .append(" KK0251_TMP.KAISEN_PLACE_ADRTTM , ") .append(" KK0251_TMP.KAISEN_PLACE_ADRRM ") .append(" FROM KK_T_SVKEI_KAISEN_UW KK0251_TMP ") .append(" WHERE (KK0251_TMP.SVC_KEI_KAISEN_UCWK_NO, KK0251_TMP.GENE_ADD_DTM) = ") .append(" (SELECT KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO, MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX ") .append(" FROM KK_T_SVKEI_KAISEN_UW KK0251_GENE ") .append(" WHERE KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251_TMP.SVC_KEI_KAISEN_UCWK_NO ") .append(" AND KK0251_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO) ") .append(" ) KK0251 ") .append(" ON KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0161_TMP_01.SVC_KEI_NO ") .append(" , KK0161_TMP_01.SVC_KEI_UCWK_NO ") .append(" , KK0191_TMP_01.TELNO ") .append(" , KK0191_TMP_01.DOBANITEN_REQ_CTRL_CD ") .append(" FROM KK_T_SVC_KEI_UCWK KK0161_TMP_01 ") .append(" INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191_TMP_01 ") .append(" ON KK0161_TMP_01.SVC_KEI_UCWK_NO = KK0191_TMP_01.SVC_KEI_UCWK_NO ") .append(" AND KK0161_TMP_01.GENE_ADD_DTM = KK0191_TMP_01.GENE_ADD_DTM ") .append(" AND KK0191_TMP_01.TELNO_JUN = 1 ") .append(" WHERE (KK0161_TMP_01.SVC_KEI_UCWK_NO, KK0161_TMP_01.RSV_APLY_YMD || KK0161_TMP_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0161_GENE_01.SVC_KEI_UCWK_NO, MAX(KK0161_GENE_01.RSV_APLY_YMD || KK0161_GENE_01.GENE_ADD_DTM) AS KK0161_MAX ") .append(" FROM KK_T_SVC_KEI_UCWK KK0161_GENE_01 ") .append(" WHERE KK0161_GENE_01.SVC_KEI_UCWK_NO = KK0161_TMP_01.SVC_KEI_UCWK_NO ") .append(" AND KK0161_GENE_01.RSV_APLY_YMD <= ? ") .append(" AND KK0161_GENE_01.RSV_APLY_CD = '2' ") .append(" AND KK0161_GENE_01.MK_FLG = '0' ") .append(" GROUP BY KK0161_GENE_01.SVC_KEI_UCWK_NO) ") .append(" AND KK0161_TMP_01.SVC_KEI_UCWK_STAT IN ('010', '020', '030', '100', '210', '220') ") .append(" AND KK0161_TMP_01.KEIZK_AF_KEI_CHGECHU_FLG = '0' ") .append(" AND KK0191_TMP_01.MK_FLG = '0' ") .append(" ) KK0161_01 ") .append(" ON KK0081.SVC_KEI_NO = KK0161_01.SVC_KEI_NO ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0161_TMP_02.SVC_KEI_NO ") .append(" , KK0161_TMP_02.SVC_KEI_UCWK_NO ") .append(" , KK0191_TMP_02.TELNO ") .append(" , KK0191_TMP_02.DOBANITEN_REQ_CTRL_CD ") .append(" FROM KK_T_SVC_KEI_UCWK KK0161_TMP_02 ") .append(" INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191_TMP_02 ") .append(" ON KK0161_TMP_02.SVC_KEI_UCWK_NO = KK0191_TMP_02.SVC_KEI_UCWK_NO ") .append(" AND KK0161_TMP_02.GENE_ADD_DTM = KK0191_TMP_02.GENE_ADD_DTM ") .append(" AND KK0191_TMP_02.TELNO_JUN = 2 ") .append(" WHERE (KK0161_TMP_02.SVC_KEI_UCWK_NO, KK0161_TMP_02.RSV_APLY_YMD || KK0161_TMP_02.GENE_ADD_DTM) = ") .append(" (SELECT KK0161_GENE_02.SVC_KEI_UCWK_NO, MAX(KK0161_GENE_02.RSV_APLY_YMD || KK0161_GENE_02.GENE_ADD_DTM) AS KK0161_MAX ") .append(" FROM KK_T_SVC_KEI_UCWK KK0161_GENE_02 ") .append(" WHERE KK0161_GENE_02.SVC_KEI_UCWK_NO = KK0161_TMP_02.SVC_KEI_UCWK_NO ") .append(" AND KK0161_GENE_02.RSV_APLY_YMD <= ? ") .append(" AND KK0161_GENE_02.RSV_APLY_CD = '2' ") .append(" AND KK0161_GENE_02.MK_FLG = '0' ") .append(" GROUP BY KK0161_GENE_02.SVC_KEI_UCWK_NO) ") .append(" AND KK0161_TMP_02.SVC_KEI_UCWK_STAT IN ('010', '020', '030', '100', '210', '220') ") .append(" AND KK0161_TMP_02.KEIZK_AF_KEI_CHGECHU_FLG = '0' ") .append(" AND KK0191_TMP_02.MK_FLG = '0' ") .append(" ) KK0161_02 ") .append(" ON KK0081.SVC_KEI_NO = KK0161_02.SVC_KEI_NO ") .append(" LEFT OUTER JOIN ") .append(" (SELECT TU0021_TMP1.* ") .append(" ,ROW_NUMBER() OVER (PARTITION BY TU0021_TMP1.KEY_SVC_KEI_UCWK_NO ORDER BY TU0021_TMP1.DOBANITEN_NO DESC) AS LINE ") .append(" FROM ( ") .append(" SELECT TU0021_02.* ") .append(" ,KK0161_02.SVC_KEI_UCWK_NO AS KEY_SVC_KEI_UCWK_NO ") .append(" FROM TU_T_DOBANITEN TU0021_02 ") .append(" INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ") .append(" ON KK0191.TELNO = TU0021_02.NTT_TELNO ") .append(" INNER JOIN KK_T_SVC_KEI_UCWK KK0161_02 ") .append(" ON KK0161_02.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO ") .append(" AND KK0161_02.GENE_ADD_DTM = KK0191.GENE_ADD_DTM ") .append(" INNER JOIN KK_T_SVC_KEI KK0081 ") .append(" ON KK0081.SVC_KEI_NO = KK0161_02.SVC_KEI_NO ") .append(" AND KK0081.SYSID = TU0021_02.SYSID ") .append(" WHERE ") .append(" (TU0021_02.DOBANITEN_NO, TU0021_02.GENE_ADD_DTM) = ") .append(" (SELECT TU0021_02_GENE.DOBANITEN_NO, MAX(TU0021_02_GENE.GENE_ADD_DTM) AS TU0021_MAX ") .append(" FROM TU_T_DOBANITEN TU0021_02_GENE ") .append(" WHERE TU0021_02_GENE.DOBANITEN_NO = TU0021_02.DOBANITEN_NO ") .append(" AND TU0021_02_GENE.MK_FLG = '0' ") .append(" GROUP BY TU0021_02_GENE.DOBANITEN_NO) ") .append(" AND (TU0021_02.DOBANITEN_NO) = ") .append(" (SELECT MAX(TU0021_02_GENE2.DOBANITEN_NO) AS TU0021_MAX_2 ") .append(" FROM TU_T_DOBANITEN TU0021_02_GENE2 ") .append(" WHERE TU0021_02_GENE2.NTT_TELNO = TU0021_02.NTT_TELNO ") .append(" AND TU0021_02_GENE2.SYSID = TU0021_02.SYSID ") .append(" AND TU0021_02_GENE2.MK_FLG = '0' ") .append(" GROUP BY TU0021_02_GENE2.NTT_TELNO, TU0021_02_GENE2.SYSID) ") .append(" AND (KK0161_02.SVC_KEI_UCWK_NO, KK0161_02.RSV_APLY_YMD || KK0161_02.GENE_ADD_DTM) = ") .append(" (SELECT KK0161_02_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_02_GENE.RSV_APLY_YMD || KK0161_02_GENE.GENE_ADD_DTM) AS KK0161_MAX ") .append(" FROM KK_T_SVC_KEI_UCWK KK0161_02_GENE ") .append(" WHERE KK0161_02_GENE.SVC_KEI_UCWK_NO = KK0161_02.SVC_KEI_UCWK_NO ") .append(" AND KK0161_02_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0161_02_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0161_02_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0161_02_GENE.SVC_KEI_UCWK_NO) ") .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(" ) TU0021_TMP1 ") .append(" ) TU0021 ") .append(" ON KK0161_01.SVC_KEI_UCWK_NO = TU0021.KEY_SVC_KEI_UCWK_NO ") .append(" AND TU0021.LINE = 1 ") .append(" LEFT OUTER JOIN ") .append(" (SELECT TU0021_TMP2.* ") .append(" ,ROW_NUMBER() OVER (PARTITION BY TU0021_TMP2.KEY_SVC_KEI_UCWK_NO ORDER BY TU0021_TMP2.DOBANITEN_NO DESC) AS LINE ") .append(" FROM ( ") .append(" SELECT TU0021_02.* ") .append(" ,KK0161_02.SVC_KEI_UCWK_NO AS KEY_SVC_KEI_UCWK_NO ") .append(" FROM TU_T_DOBANITEN TU0021_02 ") .append(" INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ") .append(" ON KK0191.TELNO = TU0021_02.NTT_TELNO ") .append(" INNER JOIN KK_T_SVC_KEI_UCWK KK0161_02 ") .append(" ON KK0161_02.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO ") .append(" AND KK0161_02.GENE_ADD_DTM = KK0191.GENE_ADD_DTM ") .append(" INNER JOIN KK_T_SVC_KEI KK0081 ") .append(" ON KK0081.SVC_KEI_NO = KK0161_02.SVC_KEI_NO ") .append(" AND KK0081.SYSID = TU0021_02.SYSID ") .append(" WHERE ") .append(" (TU0021_02.DOBANITEN_NO, TU0021_02.GENE_ADD_DTM) = ") .append(" (SELECT TU0021_02_GENE.DOBANITEN_NO, MAX(TU0021_02_GENE.GENE_ADD_DTM) AS TU0021_MAX ") .append(" FROM TU_T_DOBANITEN TU0021_02_GENE ") .append(" WHERE TU0021_02_GENE.DOBANITEN_NO = TU0021_02.DOBANITEN_NO ") .append(" AND TU0021_02_GENE.MK_FLG = '0' ") .append(" GROUP BY TU0021_02_GENE.DOBANITEN_NO) ") .append(" AND (TU0021_02.DOBANITEN_NO) = ") .append(" (SELECT MAX(TU0021_02_GENE2.DOBANITEN_NO) AS TU0021_MAX_2 ") .append(" FROM TU_T_DOBANITEN TU0021_02_GENE2 ") .append(" WHERE TU0021_02_GENE2.NTT_TELNO = TU0021_02.NTT_TELNO ") .append(" AND TU0021_02_GENE2.SYSID = TU0021_02.SYSID ") .append(" AND TU0021_02_GENE2.MK_FLG = '0' ") .append(" GROUP BY TU0021_02_GENE2.NTT_TELNO, TU0021_02_GENE2.SYSID) ") .append(" AND (KK0161_02.SVC_KEI_UCWK_NO, KK0161_02.RSV_APLY_YMD || KK0161_02.GENE_ADD_DTM) = ") .append(" (SELECT KK0161_02_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_02_GENE.RSV_APLY_YMD || KK0161_02_GENE.GENE_ADD_DTM) AS KK0161_MAX ") .append(" FROM KK_T_SVC_KEI_UCWK KK0161_02_GENE ") .append(" WHERE KK0161_02_GENE.SVC_KEI_UCWK_NO = KK0161_02.SVC_KEI_UCWK_NO ") .append(" AND KK0161_02_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0161_02_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0161_02_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0161_02_GENE.SVC_KEI_UCWK_NO) ") .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(" ) TU0021_TMP2 ") .append(" ) TU0021_2 ") .append(" ON KK0161_02.SVC_KEI_UCWK_NO = TU0021_2.KEY_SVC_KEI_UCWK_NO ") .append(" AND TU0021_2.LINE = 1 ") .append(" LEFT OUTER JOIN ") .append(" ( ") .append(" SELECT ") .append(" KK0781_TMP.PRC_GRP_CD ") .append(" , KK0781_TMP.PRC_GRP_NM ") .append(" , KK0781_TMP.ID_TCHISHO_PRT_YO_PRGP_NM ") // ANK-2248-01-00 2014/12/02 ADD START .append(" , KK0781_TMP.NHK_DNTIKTKEI_TG_FLG") // ANK-2248-01-00 2014/12/02 ADD END .append(" FROM ") .append(" KK_M_PRC_GRP KK0781_TMP ") .append(" WHERE ") .append(" (KK0781_TMP.PRC_GRP_CD, KK0781_TMP.RSV_APLY_YMD || KK0781_TMP.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0781_GENE.PRC_GRP_CD, MAX(KK0781_GENE.RSV_APLY_YMD || KK0781_GENE.GENE_ADD_DTM) AS KK0781_MAX ") .append(" FROM ") .append(" KK_M_PRC_GRP KK0781_GENE ") .append(" WHERE ") .append(" KK0781_GENE.PRC_GRP_CD = KK0781_TMP.PRC_GRP_CD ") .append(" AND KK0781_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0781_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0781_GENE.PRC_GRP_CD ") .append(" ) ") .append(" ) KK0781 ") .append(" ON KK0081.PRC_GRP_CD = KK0781.PRC_GRP_CD ") .append(" LEFT OUTER JOIN ") .append(" ( ") .append(" SELECT ") .append(" KK0791_TMP.PCRS_CD ") .append(" , KK0791_TMP.PCRS_NM ") .append(" , KK0791_TMP.ID_TCHISHO_PRT_YO_PCRS_NM ") .append(" FROM ") .append(" KK_M_PCRS KK0791_TMP ") .append(" WHERE ") .append(" (KK0791_TMP.PCRS_CD, KK0791_TMP.RSV_APLY_YMD || KK0791_TMP.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0791_GENE.PCRS_CD, MAX(KK0791_GENE.RSV_APLY_YMD || KK0791_GENE.GENE_ADD_DTM) AS KK0791_MAX ") .append(" FROM ") .append(" KK_M_PCRS KK0791_GENE ") .append(" WHERE ") .append(" KK0791_GENE.PCRS_CD = KK0791_TMP.PCRS_CD ") .append(" AND KK0791_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0791_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0791_GENE.PCRS_CD ") .append(" ) ") .append(" ) KK0791 ") .append(" ON KK0081.PCRS_CD = KK0791.PCRS_CD ") .append(" LEFT OUTER JOIN KK_M_PPLAN KK0591 ") .append(" ON KK0081.PCRS_CD = KK0591.PCRS_CD ") .append(" AND KK0081.PPLAN_CD = KK0591.PPLAN_CD ") .append(" AND KK0591.MK_FLG = '0' ") .append(" LEFT OUTER JOIN ") .append(" (SELECT ") .append(" KK0321_TMP.SVC_KEI_NO ") .append(" , KK0321_TMP.SEIKY_KEI_NO ") .append(" FROM ") .append(" KK_T_KAKINS KK0321_TMP ") .append(" WHERE ") .append(" KK0321_TMP.KAKINS_TSTAYMD <= ? ") .append(" AND KK0321_TMP.KAKINS_TENDYMD >= ? ") .append(" AND KK0321_TMP.MK_FLG = '0' ") .append(" ) KK0321 ") .append(" ON KK0081.SVC_KEI_NO = KK0321.SVC_KEI_NO ") // 20141010 ANK-2304-00-00 Add Start .append(" LEFT OUTER JOIN ( ") .append(" SELECT DISTINCT ") .append(" KK0481.SVC_KEI_NO ") .append(" FROM KK_T_WRISVC_TG_KEI KK0481 ") // 割引サービス対象契約 .append(" INNER JOIN ( ") .append(" SELECT KK0451.WRIB_SVC_KEI_NO ") .append(" FROM KK_T_WRIB_SVC_KEI KK0451 ") // 割引サービス契約 .append(" WHERE (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(" AND KK0451.WRIB_SVC_CD = 'W00000009' ") // 20150220 ANK-2302-00-00 【スタchセット割】1年後セット割引きの自動解約処理 MOD START // .append(" AND KK0451.PCRS_CD = 'F05' ") // .append(" AND KK0451.PPLAN_CD = 'PF0501' ") .append(" AND KK0451.PCRS_CD IN ('F05', 'F06') ") .append(" AND KK0451.PPLAN_CD IN ('PF0501', 'PF0601') ") // 20150220 ANK-2302-00-00 【スタchセット割】1年後セット割引きの自動解約処理 MOD END .append(" AND KK0451.WRIB_SVC_KEI_STAT NOT IN ('300', '910', '920') ") .append(" ) KK0451 ") .append(" ON KK0451.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO ") .append(" WHERE (KK0481.WRIB_SVC_KEI_NO, KK0481.WRIB_SVC_TRGT_KEI_NO, KK0481.GENE_ADD_DTM ) = ") .append(" ( SELECT ") .append(" 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 ") .append(" KK_T_WRISVC_TG_KEI KK0481_GENE1 ") .append(" WHERE ") .append(" 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(" ) KK0481 ") .append(" ON KK0481.SVC_KEI_NO = KK0081.SVC_KEI_NO ") // 20141010 ANK-2304-00-00 Add End // ANK-2248-01-00 2014/12/02 ADD START .append(" LEFT OUTER JOIN ( ") .append(" SELECT KK0441.SEIOPSVC_KEI_NO, ") .append(" KK0441.SEIKY_KEI_NO, ") .append(" KK0441.SEIOPSVC_KEI_STAT ") .append(" FROM KK_T_SEIOPSVC_KEI KK0441 ") .append(" WHERE (KK0441.SEIOPSVC_KEI_NO, KK0441.RSV_APLY_YMD || KK0441.GENE_ADD_DTM) = ") .append(" (SELECT KK0441_GENE.SEIOPSVC_KEI_NO, MAX(KK0441_GENE.RSV_APLY_YMD || KK0441_GENE.GENE_ADD_DTM) AS KK0441_MAX ") .append(" FROM KK_T_SEIOPSVC_KEI KK0441_GENE ") .append(" WHERE KK0441_GENE.SEIOPSVC_KEI_NO = KK0441.SEIOPSVC_KEI_NO ") .append(" AND KK0441_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0441_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0441_GENE.MK_FLG = '0' ") .append(" AND KK0441_GENE.SEIOPSVC_KEI_NO = ") .append(" (SELECT MAX(NHKOP.SEIOPSVC_KEI_NO) AS NHK_SEIOP_NO ") .append(" FROM KK_T_SEIOPSVC_KEI NHKOP ") .append(" WHERE NHKOP.SEIOPSVC_CD = 'E002' ") .append(" AND NHKOP.SEIOPSVC_KEI_STAT = '100' ") .append(" AND NHKOP.SEIKY_KEI_NO = KK0441.SEIKY_KEI_NO) ") .append(" GROUP BY KK0441_GENE.SEIOPSVC_KEI_NO) ") .append(" ) KK0441 ") .append(" ON KK0441.SEIKY_KEI_NO = KK0321.SEIKY_KEI_NO ") // ANK-2248-01-00 2014/12/02 ADD END // ▽▽▽ ANK-3387-00-00 ADD START .append(" LEFT OUTER JOIN KK_T_TK_HOSHIKI_KEI KK0891 ") .append(" ON KK0891.TK_HOSHIKI_KEI_NO = KK0081.TK_HOSHIKI_KEI_NO ") .append(" AND KK0891.MK_FLG = '0' ") // ▲▲▲ ANK-3387-00-00 ADD END // ANK-4244-00-00 ADD START .append(" LEFT OUTER JOIN KK_T_KAISEN_USE_KEI KK0881 ") .append(" ON KK0881.KAISEN_USE_KEI_NO = KK0891.KAISEN_USE_KEI_NO AND KK0881.MK_FLG = '0' ") // ANK-4244-00-00 ADD END // WHERE句 .append(" WHERE ") .append(" KK0081.SYSID = ? ") .append(" AND KK0081.PRC_GRP_CD <> '99' ") .append(" AND KK0081.SVC_CD <> '04' ") // ST-2021-0000012対応 20210617 星野 ADD START ; // 機能コード：２の場合は申込ステータスではなく、仮申込フラグを条件とする if( "2".equals( inMsg.getObject(EKK0081B004CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff4 .append(" AND (KK0011.KARI_MSKM_FLG IS NULL OR KK0011.KARI_MSKM_FLG <> '1') "); } else { sql_Buff4 // ST-2021-0000012対応 20210617 星野 ADD END .append(" AND KK0011.MSKM_STAT <> '100' ") // ST-2021-0000012対応 20210617 星野 ADD START ; } sql_Buff4 // ST-2021-0000012対応 20210617 星野 ADD END .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(" AND (KK0011.MSKM_NO, KK0011.GENE_ADD_DTM) = ") .append(" (SELECT KK0011_GENE.MSKM_NO, MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_MAX ") .append(" FROM KK_T_MSKM KK0011_GENE ") .append(" WHERE KK0011_GENE.MSKM_NO = KK0011.MSKM_NO ") .append(" AND KK0011_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0011_GENE.MSKM_NO) "); // ＫＥＹ＿料金グループコードの設定判定 if (!inMsg.isNull(EKK0081B004CBSMsg.KEY_PRC_GRP_CD)) { JKKejbEditCommon instance = new JKKejbEditCommon(); sql_Buff4.append(" AND KK0081.PRC_GRP_CD IN( ") .append(instance.setSqlIn(inMsg.getString(EKK0081B004CBSMsg.KEY_PRC_GRP_CD))) .append(" ) "); } // ANK-4316-00-00 ADD START // ＫＥＹ＿サービス契約回線内訳番号の設定判定 if (!inMsg.isNull(EKK0081B004CBSMsg.KEY_SVC_KEI_KAISEN_UCWK_NO)) { sql_Buff4.append(" AND KK0251.SVC_KEI_KAISEN_UCWK_NO = ? "); } //ANK-4316-00-00 ADD END // SQL文の作成(WHERE句)(個別条件部1（特定IDと一致する条件）) // (5) // 検索条件(特定ID)が指定されている場合 if(keyFlg){ sql_Buff5.append(" AND ( "); // 条件判定フラグ boolean jokenFlg = false; // ＫＥＹ＿申込書番号の設定判定 if (!inMsg.isNull(EKK0081B004CBSMsg.KEY_MSKMSHO_NO)) { sql_Buff5.append(" KK0021_01.MSKMSHO_NO = ? "); jokenFlg = true; } // ＫＥＹ＿機器製造番号の設定判定 if (!inMsg.isNull(EKK0081B004CBSMsg.KEY_KIKI_SEIZO_NO)) { if(jokenFlg){ sql_Buff5.append(" OR "); }else{ jokenFlg = true; } sql_Buff5.append(" EXISTS ") .append(" (SELECT * FROM KK_T_KKTK_SVC_KEI KK0341 ") .append(" WHERE KK0341.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" AND KK0341.KIKI_SEIZO_NO = ? ") .append(" AND (KK0341.KKTK_SVC_KEI_NO, KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) ") .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_YMD <= ? ") .append(" AND KK0341_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0341_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0341_GENE.KKTK_SVC_KEI_NO) ") .append(" ) "); } // ＫＥＹ＿工事案件番号の設定判定 if (!inMsg.isNull(EKK0081B004CBSMsg.KEY_KOJIAK_NO)) { if(jokenFlg){ sql_Buff5.append(" OR "); }else{ jokenFlg = true; } sql_Buff5.append(" EXISTS ") .append(" (SELECT * FROM KU_T_SVKEI_KOJIAK KU0081_02 ") .append(" WHERE KU0081_02.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" AND KU0081_02.KOJIAK_NO = ? ") .append(" AND KU0081_02.MK_FLG = '0') "); } // ＫＥＹ＿サービス契約番号の設定判定 if (!inMsg.isNull(EKK0081B004CBSMsg.KEY_SVC_KEI_NO)) { if(jokenFlg){ sql_Buff5.append(" OR "); }else{ jokenFlg = true; } sql_Buff5.append(" KK0081.SVC_KEI_NO = ? "); } // ＫＥＹ＿申込明細番号の設定判定 if (!inMsg.isNull(EKK0081B004CBSMsg.KEY_MSKM_DTL_NO)) { if(jokenFlg){ sql_Buff5.append(" OR "); } sql_Buff5.append(" KK0081.MSKM_DTL_NO = ? "); } // ANK-2975-00-00 2017/03/03 ADD START // ＫＥＹ＿請求契約番号の設定判定 if (!inMsg.isNull(EKK0081B004CBSMsg.KEY_SEIKY_KEI_NO)) { if (jokenFlg) { sql_Buff5.append(" OR "); } else { jokenFlg = true; } sql_Buff5.append(" KK0321.SEIKY_KEI_NO = ? "); } // ＫＥＹ＿申込書明細番号の設定判定 if (!inMsg.isNull(EKK0081B004CBSMsg.KEY_MSKMSHO_DTL_NO)) { if (jokenFlg) { sql_Buff5.append(" OR "); } else { jokenFlg = true; } sql_Buff5.append(" KK0021_01.MSKMSHO_DTL_NO = ? "); } // ＫＥＹ＿整理番号の設定判定 if (!inMsg.isNull(EKK0081B004CBSMsg.KEY_SEIRI_NO)) { if (jokenFlg) { sql_Buff5.append(" OR "); } else { jokenFlg = true; } sql_Buff5.append(" KK0081.SEIRI_NO = ? "); } // ANK-2975-00-00 2017/03/03 ADD END sql_Buff5.append(" ) "); } // ANK-2975-00-00 2017/03/03 DEL START // // SQL文の作成(WHERE句)(個別条件部2（特定IDと一致しない条件）) // // (6) // sql_Buff6.append(" AND KK0081.SVC_KEI_NO <> ? "); // ANK-2975-00-00 2017/03/03 DEL END } // ソート区分判定 String strSortKbn = ""; // ソート区分が設定されていた場合 if (!inMsg.isNull(EKK0081B004CBSMsg.KEY_SORT_KBN)) { if (SORT_ASC.equals(inMsg.getObject(EKK0081B004CBSMsg.KEY_SORT_KBN))) { strSortKbn = " ASC "; } else if (SORT_DESC.equals(inMsg.getObject(EKK0081B004CBSMsg.KEY_SORT_KBN))) { strSortKbn = " DESC "; } } // SQL文の作成(ORDER BY句) // (7) StringBuffer sql_Buff7 = new StringBuffer(); sql_Buff7.append(" ORDER BY "); // ＫＥＹ＿ソート項目Ｎｏが未設定の場合 if (inMsg.isNull(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO)) { sql_Buff7.append(" KK0251.KAISEN_PLACE_STATE_NM ASC ") .append(" , KK0251.KAISEN_PLACE_CITY_NM ASC ") .append(" , KK0251.KAISEN_PLACE_OAZTSU_NM ASC ") .append(" , KK0251.KAISEN_PLACE_AZCHO_NM ASC ") .append(" , KK0251.KAISEN_PLACE_BNCHIGO ASC ") .append(" , KK0251.KAISEN_PLACE_ADRTTM ASC ") .append(" , KK0251.KAISEN_PLACE_ADRRM ASC ") .append(" , KK0081.PRC_GRP_CD ASC "); } // ＫＥＹ＿ソート項目Ｎｏが「1」の場合 else if (SORT_PRC_GRP_CD.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" KK0081.PRC_GRP_CD " + strSortKbn); } // ＫＥＹ＿ソート項目Ｎｏが「2」の場合 else if (SORT_PRCS_CD.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" KK0081.PCRS_CD " + strSortKbn); } // ＫＥＹ＿ソート項目Ｎｏが「3」の場合 else if (SORT_ADD_CONNECT.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" KK0251.KAISEN_PLACE_STATE_NM || ") .append(" KK0251.KAISEN_PLACE_CITY_NM || ") .append(" KK0251.KAISEN_PLACE_OAZTSU_NM || ") .append(" KK0251.KAISEN_PLACE_AZCHO_NM || ") .append(" KK0251.KAISEN_PLACE_BNCHIGO || ") .append(" KK0251.KAISEN_PLACE_ADRTTM || ") .append(" KK0251.KAISEN_PLACE_ADRRM " + strSortKbn); } // ＫＥＹ＿ソート項目Ｎｏが「4」の場合 else if (SORT_SVC_KEI_STAT.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" KK0081.SVC_KEI_STAT " + strSortKbn); } // ＫＥＹ＿ソート項目Ｎｏが「5」の場合 else if (SORT_SVC_KEI_NO.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" KK0081.SVC_KEI_NO " + strSortKbn ); } // ＫＥＹ＿ソート項目Ｎｏが「6」の場合 else if (SORT_TEL1.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" TELNO1 " + strSortKbn ); } // ＫＥＹ＿ソート項目Ｎｏが「7」の場合 else if (SORT_TEL2.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" TELNO2 " + strSortKbn ); } // ＫＥＹ＿ソート項目Ｎｏが「8」の場合 else if (SORT_MSKM_YMD.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" KK0011.MSKM_YMD " + strSortKbn ); } // ＫＥＹ＿ソート項目Ｎｏが「9」の場合 else if (SORT_MSKMSHO_NO.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" KK0021_01.MSKMSHO_NO " + strSortKbn ); } // ＫＥＹ＿ソート項目Ｎｏが「10」の場合 else if (SORT_MSKM_DTL_NO.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" KK0021_01.MSKM_DTL_NO " + strSortKbn ); } // ＫＥＹ＿ソート項目Ｎｏが「11」の場合 else if (SORT_SEIKYU_KEI_NO.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" KK0321.SEIKY_KEI_NO " + strSortKbn ); } // ＫＥＹ＿ソート項目Ｎｏが「12」の場合 else if (SORT_KOJI_ANK_NO.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" KOJIAK_NO " + strSortKbn ); } // ＫＥＹ＿ソート項目Ｎｏが「13」の場合 else if (SORT_DOBAN_ITEN_KAHI.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" TU0021.DOBANITEN_KH " + strSortKbn ); } // ＫＥＹ＿ソート項目Ｎｏが「14」の場合 else if (SORT_DOBAN_ITEN_STAT.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" TU0021.DOBANITEN_STAT " + strSortKbn ); } // ＫＥＹ＿ソート項目Ｎｏが「15」の場合 else if (SORT_SEIRI_NO.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" KK0081.SEIRI_NO" + strSortKbn ); } // ＫＥＹ＿ソート項目Ｎｏが「16」の場合 else if (SORT_DOBAN_ITEN_KAHI_2.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" TU0021_2.DOBANITEN_KH " + strSortKbn ); } // ＫＥＹ＿ソート項目Ｎｏが「17」の場合 else if (SORT_MSKMSHO_DTL_NO.equals(inMsg.getString(EKK0081B004CBSMsg.KEY_SORT_ITEM_NO))) { sql_Buff7.append(" KK0021_01.MSKMSHO_DTL_NO " + strSortKbn ); } // SQL文の作成(ページングフッタ) // (8) StringBuffer sql_Buff8 = new StringBuffer(); sql_Buff8.append(" ) ALL_SELECT ") .append(" ) PAGE_SELECT ") .append(" WHERE PAGE_SELECT.LINE BETWEEN ? AND ? ") .append(" ORDER BY PAGE_SELECT.LINE ASC "); // EKK0081B004CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EKK0081B004CBSMsg1List.KAISEN_PLACE_STATE_NM , EKK0081B004CBSMsg1List.KAISEN_PLACE_CITY_NM , EKK0081B004CBSMsg1List.KAISEN_PLACE_OAZTSU_NM , EKK0081B004CBSMsg1List.KAISEN_PLACE_AZCHO_NM , EKK0081B004CBSMsg1List.KAISEN_PLACE_BNCHIGO , EKK0081B004CBSMsg1List.KAISEN_PLACE_ADRTTM , EKK0081B004CBSMsg1List.KAISEN_PLACE_ADRRM , EKK0081B004CBSMsg1List.SVC_KEI_STAT , EKK0081B004CBSMsg1List.SVC_KEI_STAT_NM , EKK0081B004CBSMsg1List.SVC_KEI_NO , EKK0081B004CBSMsg1List.TELNO1 , EKK0081B004CBSMsg1List.TELNO2 , EKK0081B004CBSMsg1List.MSKM_YMD , EKK0081B004CBSMsg1List.MSKM_NO , EKK0081B004CBSMsg1List.SEIKY_KEI_NO // ANK-2248-01-00 2014/12/02 ADD START , EKK0081B004CBSMsg1List.NHKDNTIKT_UMU // ANK-2248-01-00 2014/12/02 ADD END , EKK0081B004CBSMsg1List.KOJIAK_NO , EKK0081B004CBSMsg1List.DOBANITEN_KH , EKK0081B004CBSMsg1List.DOBANITEN_KH_NM , EKK0081B004CBSMsg1List.DOBANITEN_STAT , EKK0081B004CBSMsg1List.DOBANITEN_REQ_CTRL_CD , EKK0081B004CBSMsg1List.DOBANITEN_KH_2 , EKK0081B004CBSMsg1List.DOBANITEN_KH_NM_2 , EKK0081B004CBSMsg1List.DOBANITEN_STAT_2 , EKK0081B004CBSMsg1List.DOBANITEN_REQ_CTRL_CD_2 , EKK0081B004CBSMsg1List.PRC_GRP_CD , EKK0081B004CBSMsg1List.MSKMSHO_NO , EKK0081B004CBSMsg1List.SHOSA_YMD , EKK0081B004CBSMsg1List.PRC_GRP_NM , EKK0081B004CBSMsg1List.ID_TCHISHO_PRT_YO_PRGP_NM , EKK0081B004CBSMsg1List.PCRS_NM , EKK0081B004CBSMsg1List.ID_TCHISHO_PRT_YO_PCRS_NM , EKK0081B004CBSMsg1List.PPLAN_NM , EKK0081B004CBSMsg1List.ID_TCHISHO_PRT_YO_PPLAN_NM , EKK0081B004CBSMsg1List.SVC_CD , EKK0081B004CBSMsg1List.MSKMSHO_DTL_NO , EKK0081B004CBSMsg1List.SEIRI_NO , EKK0081B004CBSMsg1List.MSKM_STAT , EKK0081B004CBSMsg1List.SHOSA_DSL_FIN_CD , EKK0081B004CBSMsg1List.IDO_DIV // ANK-4009-00-00 ADD START , EKK0081B004CBSMsg1List.SVC_USE_STA_KIBO_YMD , EKK0081B004CBSMsg1List.SVC_STA_YMD , EKK0081B004CBSMsg1List.TK_HOSHIKI_CD , EKK0081B004CBSMsg1List.TK_HOSHIKI_PATTERN_CD // ANK-4009-00-00 ADD END // ANK-4009-16-00 ADD START , EKK0081B004CBSMsg1List.PCRS_CD // ANK-4009-16-00 ADD END // ANK-4316-00-00 ADD START , EKK0081B004CBSMsg1List.MSKM_DTL_NO , EKK0081B004CBSMsg1List.SVC_KEI_KAISEN_UCWK_NO // ANK-4316-00-00 ADD END }; // 表示件数 long iDispNum = inMsg.getStringAsLong(EKK0081B004CBSMsg.DISPLAY_NUM); // 表示ページ数 long iDispPage = inMsg.getStringAsLong(EKK0081B004CBSMsg.DISPLAY_PAGE_NUM); // 最大取得件数 long lMaxNum = inMsg.getStringAsLong(EKK0081B004CBSMsg.MAX_SEARCH_NUM); // 検索条件(特定ID)の設定により処理を振り分ける if(!keyFlg){ // 検索条件(特定ID)が指定されていない場合 StringBuffer sql_Count1 = new StringBuffer(); sql_Count1.append(sql_Buff2) .append(sql_Buff4); // 件数判定処理 if(!judgeCnt(inContext, con1, inMsg, sql_Count1, iDispNum, lMaxNum)){ return; } // SQL文の組立て(項目取得・ページング用)(1) + (3) + (4) + (7) + (8) StringBuffer sql_Page = new StringBuffer(); sql_Page.append(sql_Buff1) .append(sql_Buff3) .append(sql_Buff4) .append(sql_Buff7) .append(sql_Buff8); // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = executeQuery(inContext, con1, inMsg, sql_Page, iDispPage, iDispNum, msgKeyList1); // 取得結果が0件の場合は表示ページ該当なしエラー if (0L == outMsg1.length) { inMsg.set(EKK0081B004CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_NODATA); return; } // inMsg(CBSMsg)に明細をセット inMsg.set("EKK0081B004CBSMsg1List", outMsg1); }else{ // 検索条件(特定ID)が指定されている場合 // 個別条件部1（特定IDと一致する条件）で組立て // SQL文の組立て(件数取得用) (2) + (4) + (5) StringBuffer sql_Count = new StringBuffer(); sql_Count.append(sql_Buff2) .append(sql_Buff4) .append(sql_Buff5); //prepareStatementにSQL文をセット pstmt_Count1 = con1.prepareStatement(sql_Count.toString()); //ログ出力(SQL文の出力_件数取得用) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Count); // パラメータカウントの初期化 iPCnt = 0; // PreparedStatementの指定されたパラメータカラムに指定された値を設定 if ( "1".equals( inMsg.getString(EKK0081B004CBSMsg.FUNC_CODE)) // ST-2021-0000012対応 20210617 星野 ADD START || "2".equals( inMsg.getObject(EKK0081B004CBSMsg.FUNC_CODE).toString() ) // ST-2021-0000012対応 20210617 星野 ADD END ) { // パラメータの設定 iPCnt = setPram(inMsg, pstmt_Count1, iPCnt, "0", "1"); } // ResultSetの取得 rsltQuery_Count1 = pstmt_Count1.executeQuery(); // 総検索件数を取得する（個別条件部1（特定IDと一致する条件）） long iDataCnt1 = 0L; if (rsltQuery_Count1.next()) { iDataCnt1 = rsltQuery_Count1.getLong("CNT"); } // データ取得件数が0件ではない場合（個別条件部1（特定IDと一致する条件）） if (0L != iDataCnt1) { // 個別条件部1（特定IDと一致する条件）で組立て // ANK-2975-00-00 2017/03/03 MOD START // // SQL文の組立て(3) + (4) + (5) // SQL文の組立て(3) + (4) + (5) + (7) // ANK-2975-00-00 2017/03/03 MOD END StringBuffer sql_Page = new StringBuffer(); sql_Page.append(sql_Buff3) .append(sql_Buff4) // ANK-2975-00-00 2017/03/03 MOD START // .append(sql_Buff5); .append(sql_Buff5) .append(sql_Buff7); // ANK-2975-00-00 2017/03/03 MOD END // prepareStatementにSQL文をセット pstmt_Page1 = con1.prepareStatement(sql_Page.toString()); // ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Page); // パラメータカウントの初期化 iPCnt = 0; // PreparedStatementの指定されたパラメータカラムに指定された値を設定 if ( "1".equals( inMsg.getString(EKK0081B004CBSMsg.FUNC_CODE)) // ST-2021-0000012対応 20210617 星野 ADD START || "2".equals( inMsg.getObject(EKK0081B004CBSMsg.FUNC_CODE).toString() ) // ST-2021-0000012対応 20210617 星野 ADD END ) { // パラメータの設定 iPCnt = setPram(inMsg, pstmt_Page1, iPCnt, "1", "1"); } // ResultSetの取得 rsltQuery_Page1 = pstmt_Page1.executeQuery(); // 取得したサービス契約番号 String svcKeiNo = null; // ANK-2975-00-00 2017/03/03 ADD START // サービス契約番号用バッファ StringBuilder buf = new StringBuilder(); // サービス契約番号用カウンタ long counter = 0L; // ANK-2975-00-00 2017/03/03 ADD END // 明細に処理結果(ResultSet)をマッピング（個別条件部1（特定IDと一致する条件）） ArrayList rsltMsgList = new ArrayList(); while (rsltQuery_Page1.next()) { // 取得したサービス契約番号を保持する svcKeiNo = rsltQuery_Page1.getString("SVC_KEI_NO"); // ANK-2975-00-00 2017/03/03 ADD START buf.append("'") .append(svcKeiNo) .append("'"); // 最終行でなければカンマを結合 if ((iDataCnt1 - 1) != counter) { buf.append(","); } // 最終行であればサービス契約番号をカンマ区切りで結合したものをサービス契約番号とする else { svcKeiNo = buf.toString(); } counter++; // ANK-2975-00-00 2017/03/03 ADD END // 表示ページが1の場合のみ if(iDispPage == 1){ // 検索結果の転記 CAANMsg rsltMsg = new CAANMsg("eo.ejb.cbs.cbsmsg.EKK0081B004CBSMsg1List"); mapMessage(rsltQuery_Page1, msgKeyList1, rsltMsg); // 検索結果をリストにセット rsltMsgList.add(rsltMsg); } } // 検索対象を算出する long[] iPages = JKKejbPagingUtil.calcSearchRows(iDispPage, iDispNum); long iRowSta = iPages[0]; long iRowEnd = iPages[1]; // 表示ページが1の場合 if(iDispPage == 1){ iRowEnd = iPages[1] - 1; }else{ iRowSta = iPages[0] - 1; iRowEnd = iPages[1] - 1; } // ANK-2975-00-00 2017/03/03 ADD START // SQL文の作成(WHERE句)(個別条件部2（特定IDと一致しない条件）) // (6) JKKejbEditCommon instance = new JKKejbEditCommon(); if (!JKKStringUtil.isNullBlank(svcKeiNo)) { sql_Buff6.append(" AND KK0081.SVC_KEI_NO NOT IN ( ") .append(instance.setSqlIn(svcKeiNo)) .append(" ) "); } // ANK-2975-00-00 2017/03/03 ADD END // 個別条件部2（特定IDと一致しない条件）で組立て // SQL文の組立て(件数取得用) (2) + (4) + (6) StringBuffer sql_Count2 = new StringBuffer(); sql_Count2.append(sql_Buff2) .append(sql_Buff4) .append(sql_Buff6); //prepareStatementにSQL文をセット pstmt_Count2 = con1.prepareStatement(sql_Count2.toString()); //ログ出力(SQL文の出力_件数取得用) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Count2); // パラメータカウントの初期化 iPCnt = 0; // PreparedStatementの指定されたパラメータカラムに指定された値を設定 if ( "1".equals( inMsg.getString(EKK0081B004CBSMsg.FUNC_CODE)) // ST-2021-0000012対応 20210617 星野 ADD START || "2".equals( inMsg.getObject(EKK0081B004CBSMsg.FUNC_CODE).toString() ) // ST-2021-0000012対応 20210617 星野 ADD END ) { // パラメータの設定 iPCnt = setPram(inMsg, pstmt_Count2, iPCnt, "0", "0"); // パラメータの設定(取得したサービス契約番号を指定) // ANK-2975-00-00 2017/03/03 MOD START // CAANJDBCUtil.setParam(pstmt_Count2, ++iPCnt, svcKeiNo); if (!JKKStringUtil.isNullBlank(svcKeiNo)) { ArrayList