gerrit.onap Code Review - portal/sdk.git/blob

   1 /*
   2  * ============LICENSE_START==========================================
   3  * ONAP Portal SDK
   4  * ===================================================================
   5  * Copyright © 2017 AT&T Intellectual Property. All rights reserved.
   6  * ===================================================================
   7  *
   8  * Unless otherwise specified, all software contained herein is licensed
   9  * under the Apache License, Version 2.0 (the “License”);
  10  * you may not use this software except in compliance with the License.
  11  * You may obtain a copy of the License at
  12  *
  13  *             http://www.apache.org/licenses/LICENSE-2.0
  14  *
  15  * Unless required by applicable law or agreed to in writing, software
  16  * distributed under the License is distributed on an "AS IS" BASIS,
  17  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  18  * See the License for the specific language governing permissions and
  19  * limitations under the License.
  20  *
  21  * Unless otherwise specified, all documentation contained herein is licensed
  22  * under the Creative Commons License, Attribution 4.0 Intl. (the “License”);
  23  * you may not use this documentation except in compliance with the License.
  24  * You may obtain a copy of the License at
  25  *
  26  *             https://creativecommons.org/licenses/by/4.0/
  27  *
  28  * Unless required by applicable law or agreed to in writing, documentation
  29  * distributed under the License is distributed on an "AS IS" BASIS,
  30  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  31  * See the License for the specific language governing permissions and
  32  * limitations under the License.
  33  *
  34  * ============LICENSE_END============================================
  35  *
  36  * ECOMP is a trademark and service mark of AT&T Intellectual Property.
  37  */
  38 package org.onap.portalsdk.analytics.model.definition;
  39
  40 import java.io.Serializable;
  41 import java.sql.Connection;
  42 import java.util.Calendar;
  43 import java.util.Collections;
  44 import java.util.Iterator;
  45 import java.util.List;
  46 import java.util.StringTokenizer;
  47 import java.util.Vector;
  48 import java.util.regex.Matcher;
  49 import java.util.regex.Pattern;
  50
  51 import javax.servlet.http.HttpServletRequest;
  52 import javax.xml.bind.JAXBException;
  53 import javax.xml.datatype.DatatypeConfigurationException;
  54 import javax.xml.datatype.DatatypeFactory;
  55 //import javax.xml.transform.stream.*;
  56
  57 import org.onap.portalsdk.analytics.controller.WizardSequence;
  58 import org.onap.portalsdk.analytics.controller.WizardSequenceCrossTab;
  59 import org.onap.portalsdk.analytics.controller.WizardSequenceDashboard;
  60 import org.onap.portalsdk.analytics.controller.WizardSequenceLinear;
  61 import org.onap.portalsdk.analytics.controller.WizardSequenceSQLBasedCrossTab;
  62 import org.onap.portalsdk.analytics.controller.WizardSequenceSQLBasedHive;
  63 import org.onap.portalsdk.analytics.controller.WizardSequenceSQLBasedLinear;
  64 import org.onap.portalsdk.analytics.controller.WizardSequenceSQLBasedLinearDatamining;
  65 import org.onap.portalsdk.analytics.error.RaptorException;
  66 import org.onap.portalsdk.analytics.model.DataCache;
  67 import org.onap.portalsdk.analytics.model.ReportLoader;
  68 import org.onap.portalsdk.analytics.model.base.OrderBySeqComparator;
  69 import org.onap.portalsdk.analytics.model.base.OrderSeqComparator;
  70 import org.onap.portalsdk.analytics.model.base.ReportWrapper;
  71 import org.onap.portalsdk.analytics.model.runtime.FormField;
  72 import org.onap.portalsdk.analytics.system.AppUtils;
  73 import org.onap.portalsdk.analytics.system.DbUtils;
  74 import org.onap.portalsdk.analytics.system.Globals;
  75 import org.onap.portalsdk.analytics.util.AppConstants;
  76 import org.onap.portalsdk.analytics.util.DataSet;
  77 import org.onap.portalsdk.analytics.util.Utils;
  78 import org.onap.portalsdk.analytics.xmlobj.ChartAdditionalOptions;
  79 import org.onap.portalsdk.analytics.xmlobj.ChartDrillOptions;
  80 import org.onap.portalsdk.analytics.xmlobj.ColFilterType;
  81 import org.onap.portalsdk.analytics.xmlobj.CustomReportType;
  82 import org.onap.portalsdk.analytics.xmlobj.DataColumnList;
  83 import org.onap.portalsdk.analytics.xmlobj.DataColumnType;
  84 import org.onap.portalsdk.analytics.xmlobj.DataSourceType;
  85 import org.onap.portalsdk.analytics.xmlobj.DataminingOptions;
  86 import org.onap.portalsdk.analytics.xmlobj.FormFieldList;
  87 import org.onap.portalsdk.analytics.xmlobj.FormFieldType;
  88 import org.onap.portalsdk.analytics.xmlobj.FormatList;
  89 import org.onap.portalsdk.analytics.xmlobj.FormatType;
  90 import org.onap.portalsdk.analytics.xmlobj.JavascriptItemType;
  91 import org.onap.portalsdk.analytics.xmlobj.ObjectFactory;
  92 import org.onap.portalsdk.analytics.xmlobj.PredefinedValueList;
  93 import org.onap.portalsdk.analytics.xmlobj.SemaphoreType;
  94 import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate;
  95
  96 /**<HR/>
  97  * This class is part of <B><I>RAPTOR (Rapid Application Programming Tool for OLAP Reporting)</I></B><BR/>
  98  * <HR/>
  99  *
 100  * --------------------------------------------------------------------------------------------------<BR/>
 101  * <B>ReportDefinition.java</B> - This involves in creating and modifying RAPTOR reports.
 102  * --------------------------------------------------------------------------------------------------<BR/>
 103  *
 104  *
 105  * <U>Change Log</U><BR/><BR/>
 106  *
 107  * 18-Aug-2009 : Version 8.5.1 (Sundar);<UL><LI> request Object is passed to prevent caching user/roles - Datamining/Hosting. </LI></UL>
 108  * 27-Jul-2009 : Version 8.4 (Sundar); <UL><LI>userIsAuthorizedToSeeLog is checked for Admin User instead of Super User.</LI></UL>
 109  * 22-Jun-2009 : Version 8.4 (Sundar); <UL><LI>A new type ChartAdditionalOptions is introduced in RAPTOR XSD.
 110  * For this type a create procedure is added to this class.</LI></UL>
 111  *
 112  */
 113
 114 public class ReportDefinition extends ReportWrapper implements Serializable {
 115
 116         private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(ReportDefinition.class);
 117
 118         private ReportSchedule reportSchedule = null;
 119
 120         private WizardSequence wizardSequence = null;
 121
 122
 123         private boolean reportUpdateLogged = false; // Used to avoid multiple
 124                                                                                                 // entries in the report log
 125                                                                                                 // when persisting report on
 126                                                                                                 // each step
 127
 128         private ReportDefinition(CustomReportType crType, String reportID, String ownerID,
 129                         String createID, String createDate, String updateID, String updateDate,
 130                         String menuID, boolean menuApproved, HttpServletRequest request) throws RaptorException {
 131                 super(crType, reportID, ownerID, createID, createDate, updateID, updateDate, menuID,
 132                                 menuApproved);
 133         if(reportID.equals("-1"))
 134                 reportSchedule = new ReportSchedule(getReportID(), getOwnerID(), false, request);
 135         else
 136                 reportSchedule = new ReportSchedule(getReportID(), getOwnerID(), true, request);
 137                 generateWizardSequence(null);
 138         } // ReportDefinition
 139
 140         public ReportDefinition(ReportWrapper rw, HttpServletRequest request)throws RaptorException {
 141                 super(rw);
 142
 143                 reportSchedule = new ReportSchedule(reportID, rw.getOwnerID(),false, request);
 144                 generateWizardSequence(null);
 145         } // ReportDefinition
 146
 147         private void setReportID(String reportID) {
 148                 this.reportID = reportID;
 149                 reportSchedule.setReportID(reportID);
 150                 reportSchedule.setScheduleUserID(getOwnerID());
 151         } // setReportID
 152
 153         public ReportSchedule getReportSchedule() {
 154                 return reportSchedule;
 155         }
 156
 157         public static ReportDefinition unmarshal(String reportXML, String reportID, HttpServletRequest request)
 158                         throws RaptorException {
 159                 ReportDefinition rn = null;
 160                 CustomReportType crType = ReportWrapper.unmarshalCR(reportXML);
 161                         //Log.write("Report [" + reportID + "]: XML unmarshalled", 4);
 162                         logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] Report [" + reportID + "]: XML unmarshalled"));
 163
 164                         rn = new ReportDefinition(crType, reportID, null, null, null, null, null, null, false, request);
 165                         return rn;
 166         } // unmarshal
 167
 168         public static ReportDefinition createBlank(HttpServletRequest request) throws RaptorException {
 169                 String curTime = Utils.getCurrentDateTime();
 170                 String userID = AppUtils.getUserID(request);
 171                 ReportDefinition rd = new ReportDefinition(ReportWrapper.createBlankCR(userID), "-1",
 172                                 userID, userID, curTime, userID, curTime, "", false, request);
 173
 174                 // Necessary initialization
 175
 176                 return rd;
 177         } // ReportDefinition
 178
 179         public void setAsCopy(HttpServletRequest request) throws RaptorException  {
 180                 verifySQLBasedReportAccess(request);
 181
 182                 replaceCustomReportWithClone();
 183
 184                 setReportID("-1");
 185                 setReportName("Copy: " + getReportName());
 186         } // setAsCopy
 187
 188         public WizardSequence getWizardSequence() {
 189                 return wizardSequence;
 190         } // getWizardSequence
 191
 192         public void generateWizardSequence(HttpServletRequest request) throws RaptorException {
 193                 boolean userIsAuthorizedToSeeLog = false;
 194                 String userId = null;
 195                 if(request!=null) {
 196                         userId = AppUtils.getUserID(request);
 197                 if (userId != null)
 198                         userIsAuthorizedToSeeLog = AppUtils.isAdminUser(request)
 199                                         || AppUtils.isAdminUser(request);
 200                 //System.out.println("******** Report Type  "+getReportType() + " userIsAuthorizedToSeeLog " + userIsAuthorizedToSeeLog);
 201                 }
 202                 if (getReportType().equals(AppConstants.RT_LINEAR)){
 203                         if (getReportDefType().equals(AppConstants.RD_SQL_BASED))
 204                                 wizardSequence = new WizardSequenceSQLBasedLinear(userIsAuthorizedToSeeLog);
 205                         else if (getReportDefType().equals(AppConstants.RD_SQL_BASED_DATAMIN))
 206                                 wizardSequence = new WizardSequenceSQLBasedLinearDatamining(userIsAuthorizedToSeeLog);
 207                         else
 208                                 wizardSequence = new WizardSequenceLinear(userIsAuthorizedToSeeLog);
 209                 } else if (getReportType().equals(AppConstants.RT_CROSSTAB)) {
 210                         if (getReportDefType().equals(AppConstants.RD_SQL_BASED))
 211                                 wizardSequence = new WizardSequenceSQLBasedCrossTab(userIsAuthorizedToSeeLog);
 212                         else
 213                                 wizardSequence = new WizardSequenceCrossTab(userIsAuthorizedToSeeLog);
 214                 } else if (getReportType().equals(AppConstants.RT_DASHBOARD)) {
 215                         wizardSequence = new WizardSequenceDashboard(userIsAuthorizedToSeeLog);
 216                 } else if (getReportType().equals(AppConstants.RT_HIVE)) {
 217                         wizardSequence = new WizardSequenceSQLBasedHive(userIsAuthorizedToSeeLog);
 218                 } else
 219                         wizardSequence = new WizardSequence();
 220         } // generateWizardSequence
 221
 222         private boolean canPersistDashboard() {
 223            //System.out.println(" getDashBoardReports().getReportsList().size() " + getDashBoardReports().getReportsList().size());
 224                 /* Commented for New DashBoard
 225            if (getDashBoardReports()!=null && getDashBoardReports().getReportsList()!=null && getDashBoardReports().getReportsList().size() > 0) {
 226                    for (Iterator iter = getDashBoardReports().getReportsList().iterator(); iter.hasNext();) {
 227                            Reports report = (Reports)iter.next();
 228                            try {
 229                            if(Integer.parseInt(report.getReportId())>0) return true;
 230                            } catch (NumberFormatException ex) {}
 231                    } // for
 232            } //if
 233            */
 234
 235                 //if( )
 236            return nvl(getDashboardLayoutHTML()).length() > 0;
 237         } //canPersistDashboard
 238
 239         private boolean canPersistLinearReport() {
 240                 System.out.println("&&&&&&&&&&&&&&&&&&&&&& canPersistLinearReport");
 241                 boolean visibleColExist = false;
 242
 243                 if (getDataSourceList().getDataSource().size() > 0) {
 244                         for (Iterator iter = getAllColumns().iterator(); iter.hasNext();) {
 245                                 DataColumnType dct = (DataColumnType) iter.next();
 246
 247                                 if (dct.isVisible()) {
 248                                         visibleColExist = true;
 249                                         break;
 250                                 }
 251                         } // for
 252                 } // if
 253
 254                 System.out.println("&&&&&&&&&&&&&&&&&&&&&& visibleColExist " + visibleColExist);
 255                 return visibleColExist;
 256         } // canPersistLinearReport
 257
 258         private boolean canPersistCrossTabReport() {
 259                 boolean rowColExist = false;
 260                 boolean colColExist = false;
 261                 boolean valColExist = false;
 262
 263                 if (getDataSourceList().getDataSource().size() > 0) {
 264                         for (Iterator iter = getAllColumns().iterator(); iter.hasNext();) {
 265                                 DataColumnType dct = (DataColumnType) iter.next();
 266
 267                                 if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_ROW))
 268                                         rowColExist = true;
 269                                 if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_COLUMN))
 270                                         colColExist = true;
 271                                 if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_VALUE))
 272                                         valColExist = true;
 273                         } // for
 274                 } // if
 275
 276                 return rowColExist && colColExist && valColExist;
 277         } // canPersistCrossTabReport
 278
 279         private boolean canPersistReport() {
 280                 return getReportType().equals(AppConstants.RT_CROSSTAB) ? canPersistCrossTabReport()
 281                                 : (getReportType().equals(AppConstants.RT_LINEAR)? canPersistLinearReport():((getReportType().equals(AppConstants.RT_HIVE)? canPersistLinearReport():canPersistDashboard())));
 282         } // canPersistReport
 283
 284         public void persistReport(HttpServletRequest request) throws RaptorException {
 285                 if (!canPersistReport()) {
 286                         System.out.println("&&&&&&&&&&&&&&&&&&&&&& In !canPersistReport ReportType: " + getReportType());
 287                         return;
 288                 } else {
 289                         System.out.println("&&&&&&&&&&&&&&&&&&&&&&  Not In !canPersistReport");
 290                 }
 291
 292                 Connection connection = null;
 293                 try {
 294                         String userID = AppUtils.getUserID(request);
 295                         String reportXML = marshal();
 296                         logger.debug(EELFLoggerDelegate.debugLogger, ("Ocurring during Schedule "));
 297                         System.out.println("&&&&&&&&&&&&&&&&&&&&&&  Report ID " + reportID);
 298                         if (nvl(reportID, "-1").equals("-1")) {
 299                 connection = DbUtils.startTransaction();
 300                                 // Add report
 301                 String sql = "";
 302                 if (nvl(Globals.getAdhocReportSequence()).length()> 0 && nvl(Globals.getAdhocUserRoldId()).length() > 0 && AppUtils.isUserInRole(request, Globals.getAdhocUserRoldId()) && !AppUtils.isAdminUser(request)) {
 303                         //sql = "SELECT "+ Globals.getAdhocReportSequence() + ".nextval FROM dual";
 304                         sql = Globals.getPersistReportAdhoc();
 305                         sql = sql.replace("[Globals.getAdhocReportSequence()]", Globals.getAdhocReportSequence());
 306
 307                 } else{
 308                         //sql = "SELECT seq_cr_report.nextval FROM dual";
 309                         sql = Globals.getNewReportData();
 310                 }
 311                 DataSet ds = DbUtils.executeQuery(connection,sql);
 312                                 setReportID(ds.getString(0, 0));
 313
 314                                 reportSecurity.reportCreate(reportID, userID, isPublic());
 315                                 ReportLoader.createCustomReportRec(connection, this, reportXML);
 316                                 ReportLoader.createReportLogEntry(connection, reportID, userID,
 317                                                 AppConstants.RLA_CREATE, "", "");
 318                                 reportUpdateLogged = true;
 319                                 logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] DB insert report " + reportID + " succesfull"));
 320                         } else {
 321                                 // Update report
 322                                 verifySQLBasedReportAccess(request);
 323                                 reportSecurity.reportUpdate(request);
 324                 connection = DbUtils.startTransaction();
 325                                 ReportLoader.updateCustomReportRec(connection, this, reportXML);
 326                                 if (!reportUpdateLogged) {
 327                                         ReportLoader.createReportLogEntry(connection, reportID, userID,
 328                                                         AppConstants.RLA_UPDATE,"","");
 329                                         reportUpdateLogged = true;
 330                                 } // if
 331                                 logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] DB update report " + reportID + " succesfull"));
 332                         }
 333
 334                         getReportSchedule().persistScheduleData(connection, request);
 335
 336                         DbUtils.commitTransaction(connection);
 337                 } catch (RaptorException e) {
 338             e.printStackTrace();
 339                         DbUtils.rollbackTransaction(connection);
 340                         throw e;
 341                 } finally {
 342                 DbUtils.clearConnection(connection);
 343         }
 344         } // persistReport
 345
 346         public String getCrossTabDisplayValue(String crossTabValue) {
 347                 return nvl(crossTabValue).equals(AppConstants.CV_ROW) ? "Row headings" : (nvl(
 348                                 crossTabValue).equals(AppConstants.CV_COLUMN) ? "Column headings" : (nvl(
 349                                 crossTabValue).equals(AppConstants.CV_VALUE) ? "Report values" : "Invisible/Filter"));
 350         } // getCrossTabDisplayValue
 351
 352         public String getCrossTabDisplayValue(DataColumnType dct) {
 353                 return getCrossTabDisplayValue(dct.getCrossTabValue());
 354         } // getCrossTabDisplayValue
 355
 356         public String getColumnLabel(DataColumnType dct) throws Exception {
 357                 String tableName = getTableById(dct.getTableId()).getTableName();
 358                 Vector dbColumns = null;
 359                 dbColumns = DataCache.getReportTableDbColumns(tableName, cr.getDbInfo());
 360                 if (dbColumns != null)
 361                         for (int i = 0; i < dbColumns.size(); i++) {
 362                                 DBColumnInfo dbCol = (DBColumnInfo) dbColumns.get(i);
 363                                 if (dct.getDbColName().equals(dbCol.getColName()))
 364                                         return dbCol.getLabel();
 365                         } // for
 366
 367                 return "";
 368         } // getCrossTabDisplayValue
 369
 370         public String getFilterLabel(ColFilterType cft) {
 371                 StringBuffer fLabel = new StringBuffer();
 372
 373                 fLabel.append(cft.getExpression());
 374                 fLabel.append(" ");
 375                 if (cft.getArgType() != null)
 376                         if (cft.getArgType().equals(AppConstants.AT_FORMULA)) {
 377                                 fLabel.append("[" + cft.getArgValue() + "]");
 378                         } else if (cft.getArgType().equals(AppConstants.AT_VALUE)) {
 379                                 fLabel.append(cft.getArgValue());
 380                         } else if (cft.getArgType().equals(AppConstants.AT_LIST)) {
 381                                 fLabel.append("(" + cft.getArgValue() + ")");
 382                         } else if (cft.getArgType().equals(AppConstants.AT_COLUMN)) {
 383                                 DataColumnType dctFilter = getColumnById(cft.getArgValue());
 384                                 fLabel.append("[" + dctFilter.getDisplayName() + "]");
 385                         } else if (cft.getArgType().equals(AppConstants.AT_FORM)) {
 386                                 fLabel.append("[Form Field]");
 387                         }
 388
 389                 return fLabel.toString();
 390         } // getFilterLabel
 391
 392         public Vector getReportUsers(HttpServletRequest request) throws RaptorException {
 393                 return reportSecurity.getReportUsers(request);
 394         } // getReportUsers
 395
 396         public Vector getReportRoles(HttpServletRequest request) throws RaptorException {
 397                 return reportSecurity.getReportRoles(request);
 398         } // getReportRoles
 399
 400         /** ************************************************************************************************* */
 401
 402         public void clearAllDrillDowns() {
 403                 List reportCols = getAllColumns();
 404                 for (int i = 0; i < reportCols.size(); i++) {
 405                         DataColumnType dct = (DataColumnType) reportCols.get(i);
 406                         dct.setDrillDownURL(null);
 407                         dct.setDrillDownParams(null);
 408                         dct.setDrillDownType(null);
 409                 } // for
 410         } // clearAllDrillDowns
 411
 412         public void setOuterJoin(DataSourceType curTable, String joinType) {
 413                 String refDefinition = nvl(curTable.getRefDefinition());
 414                 int outerJoinIdx = refDefinition.indexOf(" (+)");
 415                 if (outerJoinIdx >= 0)
 416                         // Clear existing outer join
 417                         if (outerJoinIdx == (refDefinition.length() - 4))
 418                                 refDefinition = refDefinition.substring(0, outerJoinIdx);
 419                         else
 420                                 refDefinition = refDefinition.substring(0, outerJoinIdx)
 421                                                 + refDefinition.substring(outerJoinIdx + 4);
 422
 423                 int equalSignIdx = refDefinition.indexOf("=");
 424                 if (equalSignIdx < 0)
 425                         // Ref. definition not present
 426                         return;
 427
 428                 if (refDefinition.indexOf(curTable.getTableId()) < equalSignIdx) {
 429                         // Cur. table is on the left side
 430                         if (nvl(joinType).equals(AppConstants.OJ_CURRENT))
 431                                 refDefinition = refDefinition.substring(0, equalSignIdx) + " (+)"
 432                                                 + refDefinition.substring(equalSignIdx);
 433                         else if (nvl(joinType).equals(AppConstants.OJ_JOINED))
 434                                 refDefinition = refDefinition + " (+)";
 435                 } else {
 436                         // Joined table is on the left side
 437                         if (nvl(joinType).equals(AppConstants.OJ_CURRENT))
 438                                 refDefinition = refDefinition + " (+)";
 439                         else if (nvl(joinType).equals(AppConstants.OJ_JOINED))
 440                                 refDefinition = refDefinition.substring(0, equalSignIdx) + " (+)"
 441                                                 + refDefinition.substring(equalSignIdx);
 442                 }
 443
 444                 curTable.setRefDefinition(refDefinition);
 445         } // setOuterJoin
 446
 447         public void addDataSourceType(ObjectFactory objFactory, String tableId, String tableName,
 448                         String tablePK, String displayName, String refTableId, String refDefinition,
 449                         String comment) throws RaptorException {
 450                 DataSourceType dst = objFactory.createDataSourceType();
 451
 452                 dst.setTableId(tableId);
 453                 dst.setTableName(tableName);
 454                 dst.setTablePK(tablePK);
 455                 dst.setDisplayName(displayName);
 456                 if (nvl(refTableId).length() > 0)
 457                         dst.setRefTableId(refTableId);
 458                 if (nvl(refDefinition).length() > 0)
 459                         dst.setRefDefinition(refDefinition);
 460                 if (nvl(comment).length() > 0)
 461                         dst.setComment(comment);
 462
 463                 DataColumnList dataColumnList = objFactory.createDataColumnList();
 464                 dst.setDataColumnList(dataColumnList);
 465
 466                 getDataSourceList().getDataSource().add(dst);
 467
 468                 resetCache(true);
 469         } // addDataSourceType
 470
 471         public void deleteDataSourceType(String tableId) {
 472                 super.deleteDataSourceType(tableId);
 473         } // deleteDataSourceType
 474
 475         public String getUniqueColumnId(String colName) {
 476                 String colId = "";
 477
 478                 int colIdN = getAllColumns().size() + 1;
 479                 do {
 480                         colId = colName.substring(0, 2).toLowerCase() + (colIdN++);
 481                 } while (getColumnById(colId) != null);
 482
 483                 return colId;
 484         } // getUniqueColumnId
 485
 486         public DataColumnType addDataColumnType(ObjectFactory objFactory, String colId,
 487                         String tableId, // Table to which the new column belongs
 488                         String dbColName, String crossTabValue, String colName, String displayName,
 489                         int displayWidth, String displayAlignment, int orderSeq, boolean visible,
 490                         boolean calculated, String colType, String colFormat, boolean groupBreak,
 491                         int orderBySeq, String orderByAscDesc, String displayTotal, String colOnChart,
 492                         int chartSeq, String drillDownType, String drillDownURL, String drillDownParams,
 493                         String semaphoreId, String comment) throws RaptorException {
 494                 DataColumnType dct = null;
 495                         dct = objFactory.createDataColumnType();
 496
 497                         dct.setColId(colId);
 498                         dct.setTableId(tableId);
 499                         dct.setDbColName(dbColName);
 500                         if (nvl(crossTabValue).length() > 0)
 501                                 dct.setCrossTabValue(crossTabValue);
 502                         dct.setColName(colName);
 503                         dct.setDisplayName(displayName);
 504                         if (displayWidth > 0)
 505                                 dct.setDisplayWidth(displayWidth);
 506                         if (nvl(displayAlignment).length() > 0)
 507                                 dct.setDisplayAlignment(displayAlignment);
 508                         if (orderSeq > 0)
 509                                 dct.setOrderSeq(orderSeq);
 510                         else
 511                                 dct.setOrderSeq(getAllColumns().size() + 1);
 512                         dct.setVisible(visible);
 513                         dct.setCalculated(calculated);
 514                         // dct.setColType(colType);
 515                         if (nvl(colFormat).length() > 0)
 516                                 dct.setColFormat(colFormat);
 517                         dct.setGroupBreak(groupBreak);
 518                         if (orderBySeq > 0)
 519                                 dct.setOrderBySeq(orderBySeq);
 520                         if (nvl(orderByAscDesc).length() > 0)
 521                                 dct.setOrderByAscDesc(orderByAscDesc);
 522                         if (nvl(displayTotal).length() > 0)
 523                                 dct.setDisplayTotal(displayTotal);
 524                         if (nvl(colOnChart).length() > 0)
 525                                 dct.setColOnChart(colOnChart);
 526                         if (chartSeq > 0)
 527                                 dct.setChartSeq(chartSeq);
 528                         if (nvl(drillDownType).length() > 0)
 529                                 dct.setDrillDownType(drillDownType);
 530                         if (nvl(drillDownURL).length() > 0)
 531                                 dct.setDrillDownURL(drillDownURL);
 532                         if (nvl(drillDownParams).length() > 0)
 533                                 dct.setDrillDownParams(drillDownParams);
 534                         if (nvl(semaphoreId).length() > 0)
 535                                 dct.setSemaphoreId(semaphoreId);
 536                         if (nvl(comment).length() > 0)
 537                                 dct.setComment(comment);
 538
 539                         dct.setDbColType(colType);
 540                         adjustColumnType(dct);
 541
 542                         // ColFilterList colFilterList = objFactory.createColFilterList();
 543                         // dct.setColFilterList(colFilterList);
 544
 545                         getTableById(tableId).getDataColumnList().getDataColumn().add(dct);
 546
 547                         resetCache(false);
 548
 549                 return dct;
 550         } // addDataColumnType
 551
 552         public void deleteDataColumnType(String colId) {
 553                 int colOrder = getColumnById(colId).getOrderSeq();
 554
 555                 List dcList = getColumnTableById(colId).getDataColumnList().getDataColumn();
 556                 for (Iterator iterC = dcList.iterator(); iterC.hasNext();) {
 557                         DataColumnType dct = (DataColumnType) iterC.next();
 558
 559                         if (dct.getColId().equals(colId) && dct.getOrderSeq() == colOrder)
 560                                 iterC.remove();
 561                         else if (dct.getOrderSeq() > colOrder)
 562                                 dct.setOrderSeq(dct.getOrderSeq() - 1);
 563                 } // for
 564
 565                 if (getFormFieldList() != null)
 566                         for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
 567                                 FormFieldType fft = (FormFieldType) iter.next();
 568                                 if (nvl(fft.getColId()).equals(colId)) {
 569                                         fft.setColId("");
 570                                         fft.setFieldType(FormField.FFT_TEXT);
 571                                         if (nvl(fft.getDefaultValue()).equals(AppConstants.FILTER_MAX_VALUE)
 572                                                         || nvl(fft.getDefaultValue())
 573                                                                         .equals(AppConstants.FILTER_MIN_VALUE))
 574                                                 fft.setDefaultValue("");
 575                                 } // if
 576                         } // for
 577
 578                 resetCache(false);
 579                 resetColumnOrderValues();
 580         } // deleteDataColumnType
 581
 582         public void shiftColumnOrderUp(String colId) {
 583                 List reportCols = getAllColumns();
 584                 for (int i = 0; i < reportCols.size(); i++) {
 585                         DataColumnType dct = (DataColumnType) reportCols.get(i);
 586
 587                         if (dct.getColId().equals(colId) && (i > 0)) {
 588                                 DataColumnType dctUp = (DataColumnType) reportCols.get(i - 1);
 589                                 dctUp.setOrderSeq(dctUp.getOrderSeq() + 1);
 590                                 dct.setOrderSeq(dct.getOrderSeq() - 1);
 591                                 break;
 592                         } // if
 593                 } // for
 594
 595                 Collections.sort(reportCols, new OrderSeqComparator());
 596                 resetCache(true);
 597                 resetColumnOrderValues();
 598         } // shiftColumnOrderUp
 599
 600         public void shiftColumnOrderDown(String colId) {
 601                 List reportCols = getAllColumns();
 602                 for (int i = 0; i < reportCols.size(); i++) {
 603                         DataColumnType dct = (DataColumnType) reportCols.get(i);
 604
 605                         if (dct.getColId().equals(colId) && (i < reportCols.size() - 1)) {
 606                                 DataColumnType dctDown = (DataColumnType) reportCols.get(i + 1);
 607                                 dctDown.setOrderSeq(dctDown.getOrderSeq() - 1);
 608                                 dct.setOrderSeq(dct.getOrderSeq() + 1);
 609                                 break;
 610                         } // if
 611                 } // for
 612
 613                 Collections.sort(reportCols, new OrderSeqComparator());
 614                 resetCache(true);
 615                 resetColumnOrderValues();
 616         } // shiftColumnOrderDown
 617
 618         public void resetColumnOrderValues() {
 619                 List reportCols = getAllColumns();
 620
 621                 int colOrder = 0;
 622                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 623                         DataColumnType dct = (DataColumnType) iter.next();
 624                         dct.setOrderSeq(++colOrder);
 625                 } // for
 626
 627                 Collections.sort(reportCols, new OrderSeqComparator());
 628         } // resetColumnOrderValues
 629
 630         public void addColFilterType(ObjectFactory objFactory, String colId, // Column
 631                                                                                                                                                         // to
 632                                                                                                                                                         // which
 633                                                                                                                                                         // the
 634                                                                                                                                                         // new
 635                                                                                                                                                         // filter
 636                                                                                                                                                         // belongs
 637                         String joinCondition, String openBrackets, String expression, String argType,
 638                         String argValue, String closeBrackets, String comment) throws RaptorException {
 639                         ColFilterType cft = objFactory.createColFilterType();
 640
 641                         cft.setColId(colId);
 642                         cft.setJoinCondition(nvl(joinCondition, "AND"));
 643                         if (nvl(openBrackets).length() > 0)
 644                                 cft.setOpenBrackets(openBrackets);
 645                         cft.setExpression(expression);
 646                         if (nvl(argType).length() > 0)
 647                                 cft.setArgType(argType);
 648                         if (nvl(argValue).length() > 0)
 649                                 cft.setArgValue(argValue);
 650                         if (nvl(closeBrackets).length() > 0)
 651                                 cft.setCloseBrackets(closeBrackets);
 652                         if (nvl(comment).length() > 0)
 653                                 cft.setComment(comment);
 654
 655                         DataColumnType dct = getColumnById(colId);
 656                         if (dct != null) {
 657                                 if (dct.getColFilterList() == null)
 658                                         dct.setColFilterList(objFactory.createColFilterList());
 659
 660                                 cft.setFilterSeq(dct.getColFilterList().getColFilter().size());
 661                                 dct.getColFilterList().getColFilter().add(cft);
 662                         } // if
 663
 664                         resetCache(true);
 665         } // addColFilterType
 666
 667         public void removeColumnFilter(String colId, int filterPos) {
 668                 DataColumnType dct = getColumnById(colId);
 669
 670                 if (dct.getColFilterList() != null)
 671                         try {
 672                                 dct.getColFilterList().getColFilter().remove(filterPos);
 673                         } catch (IndexOutOfBoundsException e) {
 674                         }
 675
 676                 resetCache(true);
 677         } // removeColumnFilter
 678
 679         public void addColumnSort(String colId, String ascDesc) {
 680                 addColumnSort(colId, ascDesc, -1);
 681         } // addColumnSort
 682
 683         public void addColumnSort(String colId, String ascDesc, int sortOrder) {
 684                 if (sortOrder <= 0) {
 685                         sortOrder = 1;
 686                         List reportCols = getAllColumns();
 687                         for (Iterator iter = reportCols.iterator(); iter.hasNext();)
 688                                 if (((DataColumnType) iter.next()).getOrderBySeq() > 0)
 689                                         sortOrder++;
 690                 } // if
 691
 692                 DataColumnType dct = getColumnById(colId);
 693                 dct.setOrderBySeq(sortOrder);
 694                 dct.setOrderByAscDesc(ascDesc);
 695
 696                 resetCache(true);
 697         } // addColumnSort
 698
 699         public void removeColumnSort(String colId) {
 700                 DataColumnType dct = getColumnById(colId);
 701                 int sortOrder = dct.getOrderBySeq();
 702
 703                 dct.setOrderBySeq(0);
 704                 dct.setOrderByAscDesc(null);
 705
 706                 if (sortOrder > 0) {
 707                         List reportCols = getAllColumns();
 708                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 709                                 DataColumnType dct2 = (DataColumnType) iter.next();
 710
 711                                 if (dct2.getOrderBySeq() > sortOrder)
 712                                         dct2.setOrderBySeq(dct2.getOrderBySeq() - 1);
 713                         } // for
 714                 } // if
 715
 716                 resetCache(true);
 717         } // removeColumnSort
 718
 719         public void shiftColumnSortUp(String colId) {
 720                 List reportCols = getAllColumns();
 721                 Collections.sort(reportCols, new OrderBySeqComparator());
 722
 723                 for (int i = 0; i < reportCols.size(); i++) {
 724                         DataColumnType dct = (DataColumnType) reportCols.get(i);
 725
 726                         if (dct.getColId().equals(colId) && (dct.getOrderBySeq() > 0)) {
 727                                 DataColumnType dctUp = (DataColumnType) reportCols.get(i - 1);
 728                                 if (dctUp.getOrderBySeq() > 0)
 729                                         dctUp.setOrderBySeq(dctUp.getOrderBySeq() + 1);
 730                                 dct.setOrderBySeq(dct.getOrderBySeq() - 1);
 731                                 break;
 732                         } // if
 733                 } // for
 734
 735                 Collections.sort(reportCols, new OrderSeqComparator());
 736                 resetCache(true);
 737         } // shiftColumnSortUp
 738
 739         public void shiftColumnSortDown(String colId) {
 740                 List reportCols = getAllColumns();
 741                 Collections.sort(reportCols, new OrderBySeqComparator());
 742
 743                 for (int i = 0; i < reportCols.size(); i++) {
 744                         DataColumnType dct = (DataColumnType) reportCols.get(i);
 745
 746                         if (dct.getColId().equals(colId) && (dct.getOrderBySeq() > 0)) {
 747                                 DataColumnType dctDown = (DataColumnType) reportCols.get(i + 1);
 748                                 if (dctDown.getOrderBySeq() > 0)
 749                                         dctDown.setOrderBySeq(dctDown.getOrderBySeq() - 1);
 750                                 dct.setOrderBySeq(dct.getOrderBySeq() + 1);
 751                                 break;
 752                         } // if
 753                 } // for
 754
 755                 Collections.sort(reportCols, new OrderSeqComparator());
 756                 resetCache(true);
 757         } // shiftColumnSortDown
 758
 759         /** ************************************************************************************************* */
 760
 761         public String generateNewSemaphoreId() {
 762                 if (getSemaphoreList() == null)
 763                         return "sem1";
 764
 765                 String semaphoreId = null;
 766                 boolean idExists = true;
 767                 for (int i = 1; idExists; i++) {
 768                         semaphoreId = "sem" + i;
 769                         idExists = false;
 770                         for (Iterator iter = getSemaphoreList().getSemaphore().iterator(); iter.hasNext();)
 771                                 if (semaphoreId.equals(((SemaphoreType) iter.next()).getSemaphoreId())) {
 772                                         idExists = true;
 773                                         break;
 774                                 }
 775                 } // for
 776
 777                 return semaphoreId;
 778         } // generateNewSemaphoreId
 779
 780         public SemaphoreType addSemaphore(ObjectFactory objFactory, SemaphoreType semaphoreType)
 781                         throws RaptorException {
 782                 SemaphoreType sem =  null;
 783                 try {
 784                 if (getSemaphoreList() == null)
 785                         setSemaphoreList(objFactory.createSemaphoreList());
 786
 787                 String semaphoreName = null;
 788                 boolean nameExists = true;
 789                 for (int i = 1; nameExists; i++) {
 790                         semaphoreName = semaphoreType.getSemaphoreName() + ((i > 1) ? (" v" + i) : "");
 791                         nameExists = false;
 792                         for (Iterator iter2 = getSemaphoreList().getSemaphore().iterator(); iter2
 793                                         .hasNext();)
 794                                 if (semaphoreName.equals(((SemaphoreType) iter2.next()).getSemaphoreName())) {
 795                                         nameExists = true;
 796                                         break;
 797                                 }
 798                 } // for
 799
 800                 sem = cloneSemaphoreType(objFactory, semaphoreType);
 801                 getSemaphoreList().getSemaphore().add(sem);
 802
 803                 sem.setSemaphoreId(generateNewSemaphoreId());
 804                 sem.setSemaphoreName(semaphoreName);
 805                 } catch (JAXBException ex) {
 806                         throw new RaptorException(ex.getMessage(), ex.getCause());
 807                 }
 808
 809                 return sem;
 810         } // addSemaphore
 811
 812         public SemaphoreType addSemaphoreType(ObjectFactory objFactory, String semaphoreName,
 813                         String semaphoreType, String comment) throws RaptorException {
 814                 SemaphoreType sem =  null;
 815                         if (getSemaphoreList() == null)
 816                                 setSemaphoreList(objFactory.createSemaphoreList());
 817
 818                         sem = objFactory.createSemaphoreType();
 819                         getSemaphoreList().getSemaphore().add(sem);
 820
 821                         sem.setSemaphoreId(generateNewSemaphoreId());
 822                         sem.setSemaphoreName(semaphoreName);
 823                         sem.setSemaphoreType(nvl(semaphoreType));
 824                         if (nvl(comment).length() > 0)
 825                                 sem.setComment(comment);
 826
 827                         FormatList formatList = objFactory.createFormatList();
 828                         sem.setFormatList(formatList);
 829                 return sem;
 830         } // addSemaphoreType
 831
 832
 833         public String getNextIdForJavaScriptElement (ObjectFactory objFactory, String fieldId) throws RaptorException {
 834                 String id = "";
 835                 JavascriptItemType jit = null;
 836                 int incr = 0;
 837                         if (getJavascriptList() == null) {
 838                                 setJavascriptList(objFactory.createJavascriptList());
 839                                 return fieldId + "|1";
 840                         } else {
 841                                 if(getJavascriptList().getJavascriptItem().iterator().hasNext()) {
 842                                 for (Iterator iter = getJavascriptList().getJavascriptItem().iterator(); iter.hasNext();) {
 843                                         jit = (JavascriptItemType) iter.next();
 844                                         logger.debug(EELFLoggerDelegate.debugLogger, ("^^^^^JAVASCRIPTITEMTYPE " + jit.getFieldId() + " " + fieldId + " " + id));
 845                                         if(nvl(jit.getFieldId()).length()>0 && jit.getFieldId().equals(fieldId)) {
 846                                                 ++incr;
 847                                         }
 848                                 } // for
 849                                 return fieldId + "|"+incr;
 850                                 } else {
 851                                         return fieldId + "|1";
 852                                 }
 853
 854                         }
 855                 //return null;
 856         }
 857
 858         public JavascriptItemType addJavascriptType(ObjectFactory objFactory, String id) throws RaptorException {
 859                 JavascriptItemType javascriptItemType =  null;
 860                 int flag = 0; // checking whether id existing in the list
 861                         if (getJavascriptList() == null) {
 862                                 setJavascriptList(objFactory.createJavascriptList());
 863                                 javascriptItemType = objFactory.createJavascriptItemType();
 864                                 getJavascriptList().getJavascriptItem().add(javascriptItemType);
 865                                 return javascriptItemType;
 866                         } else {
 867
 868                                 for (Iterator iter = getJavascriptList().getJavascriptItem().iterator(); iter.hasNext();) {
 869                                         javascriptItemType  = (JavascriptItemType)iter.next();
 870                                         if(javascriptItemType.getId().equals(id) && !id.startsWith("-1")) {
 871                                                 flag = 1;
 872                                                 break;
 873                                         }
 874                                 }
 875                                 if(flag == 1) return javascriptItemType;
 876                                 else {
 877                                         javascriptItemType = objFactory.createJavascriptItemType();
 878                                         getJavascriptList().getJavascriptItem().add(javascriptItemType);
 879                                         return javascriptItemType;
 880                                 }
 881                         }
 882
 883         } // addSemaphoreType
 884
 885         public boolean deleteJavascriptType(String id) throws RaptorException {
 886                 JavascriptItemType javascriptType =  null;
 887                 if (getJavascriptList() == null)
 888                         return true;
 889                 for (Iterator iter = getJavascriptList().getJavascriptItem().iterator(); iter.hasNext();) {
 890                         javascriptType  = (JavascriptItemType)iter.next();
 891                         if(javascriptType.getId().equals(id)) {
 892                                 iter.remove();
 893                                 return true;
 894                         }
 895                 }
 896                 return false;
 897         } // addSemaphoreType
 898
 899         public static FormatType addEmptyFormatType(ObjectFactory objFactory,
 900                         SemaphoreType semaphore) throws RaptorException {
 901                 FormatType fmt = null;
 902                         fmt = objFactory.createFormatType();
 903                         semaphore.getFormatList().getFormat().add(fmt);
 904
 905                         String formatId = null;
 906                         boolean idExists = true;
 907                         for (int i = 1; idExists; i++) {
 908                                 formatId = semaphore.getSemaphoreId() + "_fmt" + i;
 909                                 idExists = false;
 910                                 for (Iterator iter = semaphore.getFormatList().getFormat().iterator(); iter
 911                                                 .hasNext();)
 912                                         if (formatId.equals(((FormatType) iter.next()).getFormatId())) {
 913                                                 idExists = true;
 914                                                 break;
 915                                         }
 916                         } // for
 917                         fmt.setFormatId(formatId);
 918                 return fmt;
 919         } // addEmptyFormatType
 920
 921         public static void deleteFormatType(SemaphoreType semaphore, String formatId) {
 922                 for (Iterator iter = semaphore.getFormatList().getFormat().iterator(); iter.hasNext();)
 923                         if (formatId.equals(((FormatType) iter.next()).getFormatId())) {
 924                                 iter.remove();
 925                                 break;
 926                         } // if
 927         } // deleteFormatType
 928
 929         public FormFieldType addFormFieldType(ObjectFactory objFactory, String fieldName,
 930                         String colId, String fieldType, String validationType, String mandatory,
 931                         String defaultValue, String fieldSQL, String comment, Calendar rangeStartDate, Calendar rangeEndDate,
 932                         String rangeStartDateSQL, String rangeEndDateSQL) throws RaptorException {
 933                 FormFieldType fft = null;
 934                         fft = objFactory.createFormFieldType();
 935
 936                         fft.setFieldName(fieldName);
 937                         fft.setColId(colId);
 938                         fft.setFieldType(fieldType);
 939                         fft.setValidationType(validationType);
 940                         fft.setMandatory(nvl(mandatory, "N"));
 941                         fft.setDefaultValue(nvl(defaultValue));
 942                         fft.setOrderBySeq((getFormFieldList() == null) ? 1 : getFormFieldList().getFormField()
 943                                         .size() + 1);
 944                         fft.setFieldSQL(fieldSQL);
 945                         //fft.setRangeStartDate(rangeStartDate);
 946                         //fft.setRangeEndDate(rangeEndDate);
 947
 948             try {
 949                 fft.setRangeStartDate(DatatypeFactory.newInstance()
 950                         .newXMLGregorianCalendar(rangeStartDate.YEAR, rangeStartDate.MONTH, rangeStartDate.DAY_OF_WEEK, rangeStartDate.HOUR, rangeStartDate.MINUTE, rangeStartDate.SECOND, rangeStartDate.MILLISECOND, rangeStartDate.ZONE_OFFSET));
 951                 fft.setRangeStartDate(DatatypeFactory.newInstance()
 952                         .newXMLGregorianCalendar(rangeEndDate.YEAR, rangeEndDate.MONTH, rangeEndDate.DAY_OF_WEEK, rangeEndDate.HOUR, rangeEndDate.MINUTE, rangeEndDate.SECOND, rangeEndDate.MILLISECOND, rangeEndDate.ZONE_OFFSET));
 953                 /*currField.setRangeEndDate(DatatypeFactory.newInstance()
 954                         .newXMLGregorianCalendar(end));*/
 955             } catch (DatatypeConfigurationException ex) {
 956
 957             }
 958
 959                         fft.setRangeStartDateSQL(rangeStartDateSQL);
 960                         fft.setRangeEndDateSQL(rangeEndDateSQL);
 961                         if (nvl(comment).length() > 0)
 962                                 fft.setComment(comment);
 963
 964                         String fieldId = null;
 965                         boolean idExists = true;
 966                         for (int i = 1; idExists; i++) {
 967                                 fieldId = "ff" + i;
 968                                 idExists = false;
 969                                 if (getFormFieldList() != null)
 970                                         for (Iterator iter = getFormFieldList().getFormField().iterator(); iter
 971                                                         .hasNext();)
 972                                                 if (fieldId.equals(((FormFieldType) iter.next()).getFieldId())) {
 973                                                         idExists = true;
 974                                                         break;
 975                                                 }
 976                         } // for
 977                         fft.setFieldId(fieldId);
 978
 979                         if (getFormFieldList() == null) {
 980                                 FormFieldList formFieldList = objFactory.createFormFieldList();
 981                                 setFormFieldList(formFieldList);
 982                         }
 983
 984                         getFormFieldList().getFormField().add(fft);
 985                 return fft;
 986         } // addFormFieldType
 987
 988         //addCustomizedTextForParameters
 989         public void addCustomizedTextForParameters(String comment) throws RaptorException {
 990                         getFormFieldList().setComment(comment);
 991         }
 992
 993         public FormFieldType addFormFieldBlank(ObjectFactory objFactory) throws RaptorException {
 994                 FormFieldType fft = null;
 995                         fft = objFactory.createFormFieldType();
 996
 997                         fft.setFieldName("BLANK");
 998                 fft.setColId("bk");
 999                 fft.setFieldType(FormField.FFT_BLANK);
1000                 fft.setOrderBySeq((getFormFieldList() == null) ? 1 : getFormFieldList().getFormField()
1001                                         .size() + 1);
1002                         String fieldId = null;
1003                         boolean idExists = true;
1004                         for (int i = 1; idExists; i++) {
1005                                 fieldId = "ff" + i;
1006                                 idExists = false;
1007                                 if (getFormFieldList() != null)
1008                                         for (Iterator iter = getFormFieldList().getFormField().iterator(); iter
1009                                                         .hasNext();)
1010                                                 if (fieldId.equals(((FormFieldType) iter.next()).getFieldId())) {
1011                                                         idExists = true;
1012                                                         break;
1013                                                 }
1014                         } // for
1015                         fft.setFieldId(fieldId);
1016
1017                         if (getFormFieldList() == null) {
1018                                 FormFieldList formFieldList = objFactory.createFormFieldList();
1019                                 setFormFieldList(formFieldList);
1020                         }
1021
1022                         getFormFieldList().getFormField().add(fft);
1023                 return fft;
1024         } // addFormFieldBlank
1025
1026         public void replaceFormFieldReferences(String fieldName, String replaceWith) {
1027                 if (fieldName.equals(replaceWith))
1028                         return;
1029
1030                 for (Iterator iter = getAllColumns().iterator(); iter.hasNext();) {
1031                         DataColumnType dct = (DataColumnType) iter.next();
1032
1033                         if (dct.isCalculated() && dct.getColName().indexOf(fieldName) >= 0)
1034                                 dct.setColName(Utils.replaceInString(dct.getColName(), fieldName, nvl(
1035                                                 replaceWith, "NULL")));
1036
1037                         if (dct.getColFilterList() != null)
1038                                 for (Iterator iter2 = dct.getColFilterList().getColFilter().iterator(); iter2
1039                                                 .hasNext();) {
1040                                         ColFilterType cft = (ColFilterType) iter2.next();
1041
1042                                         if (nvl(cft.getArgType()).equals(AppConstants.AT_FORM)
1043                                                         && nvl(cft.getArgValue()).equals(fieldName))
1044                                                 cft.setArgValue(replaceWith);
1045                                 } // for
1046                 } // for
1047         } // replaceFormFieldReferences
1048
1049         public void deleteFormField(String fieldId) {
1050                 String fieldDisplayName = null;
1051
1052                 int orderBySeq = Integer.MAX_VALUE;
1053                 if (getFormFieldList() != null)
1054                         for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
1055                                 FormFieldType fft = (FormFieldType) iter.next();
1056
1057                                 if (fieldId.equals(fft.getFieldId())) {
1058                                         //orderBySeq = fft.getOrderBySeq();
1059                                         fieldDisplayName = getFormFieldDisplayName(fft);
1060                                         iter.remove();
1061                                 } else if (fft.getOrderBySeq()!=null && (fft.getOrderBySeq().intValue() > orderBySeq))
1062                                         fft.setOrderBySeq(fft.getOrderBySeq() - 1);
1063                         } // for
1064
1065                 if (fieldDisplayName != null)
1066                         replaceFormFieldReferences(fieldDisplayName, "");
1067         } // deleteFormField
1068
1069         public void shiftFormFieldUp(String fieldId) {
1070                 if (getFormFieldList() == null)
1071                         return;
1072
1073                 for (int i = 0; i < getFormFieldList().getFormField().size(); i++) {
1074                         FormFieldType fft = (FormFieldType) getFormFieldList().getFormField().get(i);
1075
1076                         if (fft.getFieldId().equals(fieldId) && (i > 0)) {
1077                                 FormFieldType prevFft = (FormFieldType) getFormFieldList().getFormField().get(
1078                                                 i - 1);
1079                                 prevFft.setOrderBySeq(prevFft.getOrderBySeq() + 1);
1080                                 fft.setOrderBySeq((fft.getOrderBySeq() == null)?0:fft.getOrderBySeq() - 1);
1081
1082                                 getFormFieldList().getFormField().remove(i);
1083                                 getFormFieldList().getFormField().add(i - 1, fft);
1084                                 return;
1085                         } // if
1086                 } // for
1087         } // shiftFormFieldUp
1088
1089         public void shiftFormFieldDown(String fieldId) {
1090                 if (getFormFieldList() == null)
1091                         return;
1092
1093                 for (int i = 0; i < getFormFieldList().getFormField().size(); i++) {
1094                         FormFieldType fft = (FormFieldType) getFormFieldList().getFormField().get(i);
1095
1096                         if (fft.getFieldId().equals(fieldId)
1097                                         && (i < getFormFieldList().getFormField().size() - 1)) {
1098                                 FormFieldType nextFft = (FormFieldType) getFormFieldList().getFormField().get(
1099                                                 i + 1);
1100                                 nextFft.setOrderBySeq((nextFft.getOrderBySeq() == null)?0:nextFft.getOrderBySeq() - 1);
1101                                 fft.setOrderBySeq((fft.getOrderBySeq() == null)?0:fft.getOrderBySeq() + 1);
1102
1103                                 getFormFieldList().getFormField().remove(i + 1);
1104                                 getFormFieldList().getFormField().add(i, nextFft);
1105                                 return;
1106                         } // if
1107                 } // for
1108         } // shiftFormFieldDown
1109
1110         public static void addFormFieldPredefinedValue(ObjectFactory objFactory,
1111                         FormFieldType formField, String predefinedValue) throws RaptorException {
1112                         if (formField.getPredefinedValueList() == null) {
1113                                 PredefinedValueList predefinedValueList = objFactory.createPredefinedValueList();
1114                                 formField.setPredefinedValueList(predefinedValueList);
1115                         } // if
1116
1117                         if (predefinedValue.length() > 0) {
1118                                 formField.getPredefinedValueList().getPredefinedValue().add(predefinedValue);
1119                                 Collections.sort(formField.getPredefinedValueList().getPredefinedValue());
1120                         } // if
1121         } // addFormFieldPredefinedValue
1122
1123         public static void deleteFormFieldPredefinedValue(FormFieldType formField,
1124                         String predefinedValue) {
1125                 if (formField != null && formField.getPredefinedValueList() != null
1126                                 && predefinedValue.length() > 0)
1127                         for (Iterator iter = formField.getPredefinedValueList().getPredefinedValue()
1128                                         .iterator(); iter.hasNext();)
1129                                 if (predefinedValue.equals((String) iter.next())) {
1130                                         iter.remove();
1131                                         break;
1132                                 } // if
1133         } // deleteFormFieldPredefinedValue
1134
1135         /** ************************************************************************************************* */
1136
1137         private int curSQLParsePos = 0;
1138
1139         private String getNextSQLParseToken(String sql, boolean updateParsePos) {
1140                 int braketCount = 0;
1141                 boolean isInsideQuote = false;
1142                 StringBuffer nextToken = new StringBuffer();
1143                 for (int idxNext = curSQLParsePos; idxNext < sql.length(); idxNext++) {
1144                         char ch = sql.charAt(idxNext);
1145
1146                         if (Character.isWhitespace(ch) || ch == ',') {
1147                                 if (ch == ',')
1148                                         nextToken.append(ch);
1149
1150                                 if (nextToken.length() == 0)
1151                                         continue;
1152                                 else if (braketCount == 0 && (!isInsideQuote)) {
1153                                         if (updateParsePos)
1154                                                 curSQLParsePos = idxNext + ((ch == ',') ? 1 : 0);
1155                                         break;
1156                                 } else if (ch != ',' && nextToken.charAt(nextToken.length() - 1) != ' ')
1157                                         nextToken.append(' ');
1158                         } else {
1159                                 nextToken.append(ch);
1160
1161                                 if (ch == '(' || ch == '[')
1162                                         braketCount++;
1163                                 else if (ch == ')' || ch == ']')
1164                                         braketCount--;
1165                                 else if (ch == '\''/* ||ch=='\"' */)
1166                                         isInsideQuote = (!isInsideQuote);
1167                         } // else
1168                 } // for
1169
1170                 return nextToken.toString();
1171         } // getNextSQLParseToken
1172
1173         private boolean isParseSQLColID(String token) {
1174                 if (nvl(token).length() == 0)
1175                         return false;
1176
1177                 for (int i = 0; i < token.length(); i++) {
1178                         char ch = token.charAt(i);
1179
1180                         if (i == 0 && ch == '_')
1181                                 return false;
1182
1183                         if (!(Character.isLetterOrDigit(ch) || ch == '_'))
1184                                 return false;
1185                 } // for
1186
1187                 return true;
1188         } // isParseSQLColID
1189
1190         private DataColumnType getParseSQLDataColumn(String sqlExpression, String colId,
1191                         StringBuffer parsedSQL, Vector updatedReportCols, boolean isCYMBALScript) throws RaptorException {
1192                 DataColumnType dct = null;
1193
1194                 if (colId != null) {
1195                         if (!isParseSQLColID(colId))
1196                                 throw new org.onap.portalsdk.analytics.error.ValidationException(
1197                                                 "["
1198                                                                 + colId
1199                                                                 + "] must either be a valid column id consisting only of letters, numbers, and underscores, or there must be a comma in front of it.");
1200
1201                         dct = getColumnById(colId);
1202                 } else {
1203                         // Getting unique column id
1204                         colId = "";
1205                         int colIdN = 0;
1206                         for (int i = 0; (i < sqlExpression.length()) && (colIdN < 2); i++)
1207                                 if (Character.isLetter(sqlExpression.charAt(i))) {
1208                                         colId += sqlExpression.toLowerCase().charAt(i);
1209                                         colIdN++;
1210                                 } // if
1211
1212                         colIdN = getAllColumns().size() + updatedReportCols.size();
1213                         for (boolean idAlreadyUsed = true; idAlreadyUsed; colIdN++) {
1214                                 String newColId = colId + colIdN;
1215                                 idAlreadyUsed = false;
1216
1217                                 for (Iterator iter = getAllColumns().iterator(); iter.hasNext();)
1218                                         if (newColId.equals(((DataColumnType) iter.next()).getColId())) {
1219                                                 idAlreadyUsed = true;
1220                                                 break;
1221                                         }
1222
1223                                 if (!idAlreadyUsed)
1224                                         for (Iterator iter = updatedReportCols.iterator(); iter.hasNext();)
1225                                                 if (newColId.equals(((DataColumnType) iter.next()).getColId())) {
1226                                                         idAlreadyUsed = true;
1227                                                         break;
1228                                                 }
1229                         } // for
1230
1231                         colId += (colIdN - 1);
1232                 } // else
1233
1234                 if (dct == null) {
1235                         dct = (new ObjectFactory()).createDataColumnType();
1236                         dct.setColId(colId);
1237                         dct.setDisplayWidth(10);
1238                         dct.setDisplayAlignment("Left");
1239                         dct.setVisible(true);
1240                         dct.setGroupBreak(false); // ???
1241                         if(!isCYMBALScript) {
1242                                 boolean isValidIdentifier = Character.isLetterOrDigit(sqlExpression.charAt(0));
1243                                 for (int i = 0; i < sqlExpression.length(); i++)
1244                                         if (!(Character.isLetterOrDigit(sqlExpression.charAt(i))
1245                                                         || (sqlExpression.charAt(i) == '_') || (sqlExpression.charAt(i) == '$'))) {
1246                                                 isValidIdentifier = false;
1247                                                 break;
1248                                         } // if
1249
1250                                 if (isValidIdentifier) {
1251                                         dct.setDisplayName(sqlExpression);
1252                                 } else {
1253                                         dct.setDisplayName(colId);
1254                                 } // else
1255                         } else dct.setDisplayName(colId);
1256                 } // if
1257                  if(!isCYMBALScript)
1258                 sqlExpression = sqlExpression.replaceAll(", '", ",'");
1259                 dct.setDbColName(sqlExpression);
1260                 dct.setColName(sqlExpression);
1261                 dct.setCalculated(true);
1262                 dct.setColType(AppConstants.CT_CHAR);
1263                 dct.setDbColType(AppConstants.CT_CHAR);
1264                 adjustColumnType(dct); // ???
1265                 if(!isCYMBALScript) {
1266                         if (parsedSQL.toString().equals("SELECT ")
1267                                         || parsedSQL.toString().equals("SELECT DISTINCT "))
1268                                 parsedSQL.append("\n\t");
1269                         else
1270                                 parsedSQL.append(", \n\t");
1271                         parsedSQL.append(sqlExpression);
1272                         parsedSQL.append(" ");
1273                         parsedSQL.append(colId);
1274                 }
1275
1276                 return dct;
1277         } // getParseSQLDataColumn
1278
1279         public void parseReportSQL(String sql) throws RaptorException {
1280                 StringBuffer parsedSQL = new StringBuffer();
1281
1282                 Vector updatedReportCols = new Vector();
1283
1284                 curSQLParsePos = 0;
1285                 int lastParsePos = curSQLParsePos;
1286                 String lastToken = null;
1287                 String nextToken = getNextSQLParseToken(sql, true);
1288
1289         String  dbInfo          = getDBInfo();
1290         boolean isCYMBALScript = false;
1291                 if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
1292                         try {
1293                          org.onap.portalsdk.analytics.util.RemDbInfo remDbInfo = new org.onap.portalsdk.analytics.util.RemDbInfo();
1294                          String dbType = remDbInfo.getDBType(dbInfo);
1295                          if (dbType.equals("DAYTONA") && !(nextToken.toUpperCase().equals("SELECT"))) {
1296                                  isCYMBALScript = true;
1297                          }
1298                         } catch (Exception ex) {
1299                    throw new RaptorException(ex);
1300                         }
1301                 }
1302                 if ( isCYMBALScript == false ) {
1303                         while (nextToken.length() > 0) {
1304                                 if (parsedSQL.length() == 0) {
1305                                         if (nextToken.toUpperCase().equals("SELECT"))
1306                                                 parsedSQL.append("SELECT ");
1307                                         else
1308                                                 throw new org.onap.portalsdk.analytics.error.ValidationException(
1309                                                                 "The SQL must start with the SELECT keyword.");
1310                                 } else if (nextToken.toUpperCase().equals("DISTINCT")
1311                                                 && parsedSQL.toString().equals("SELECT ")) {
1312                                         parsedSQL.append("DISTINCT ");
1313                                 } else if (nextToken.equals("*")
1314                                                 && (parsedSQL.toString().equals("SELECT ") || parsedSQL.toString().equals(
1315                                                                 "SELECT DISTINCT "))) {
1316                                         throw new org.onap.portalsdk.analytics.error.ValidationException(
1317                                                         "You cannot use \"SELECT *\". Please specify select columns/expressions.");
1318                                 } else if (nextToken.toUpperCase().equals("FROM")) {
1319                                         if (lastToken != null) {
1320                                                 updatedReportCols.add(getParseSQLDataColumn(lastToken, null, parsedSQL,
1321                                                                 updatedReportCols, false));
1322                                                 lastToken = null;
1323                                         }
1324
1325                                         parsedSQL.append(" \n");
1326                                         while (lastParsePos < sql.length()
1327                                                         && Character.isWhitespace(sql.charAt(lastParsePos)))
1328                                                 lastParsePos++;
1329                                         parsedSQL.append(sql.substring(lastParsePos));
1330                                         break;
1331                                 } else {
1332                                         if (nextToken.charAt(nextToken.length() - 1) == ',') {
1333                                                 // The token ends with ,
1334                                                 nextToken = nextToken.substring(0, nextToken.length() - 1);
1335
1336                                                 if (nextToken.length() == 0) {
1337                                                         if (lastToken != null) {
1338                                                                 updatedReportCols.add(getParseSQLDataColumn(lastToken, null,
1339                                                                                 parsedSQL, updatedReportCols, false));
1340                                                                 lastToken = null;
1341                                                         } // else just comma => ignore it
1342                                                 } else {
1343                                                         if (lastToken != null) {
1344                                                                 updatedReportCols.add(getParseSQLDataColumn(lastToken, nextToken,
1345                                                                                 parsedSQL, updatedReportCols, false));
1346                                                                 lastToken = null;
1347                                                         } else
1348                                                                 updatedReportCols.add(getParseSQLDataColumn(nextToken, null,
1349                                                                                 parsedSQL, updatedReportCols, false));
1350                                                 }
1351                                         } else {
1352                                                 // The token doesn't end with ,
1353                                                 if (lastToken == null)
1354                                                         lastToken = nextToken;
1355                                                 else {
1356                                                         String token = getNextSQLParseToken(sql, false);
1357                                                         if (!token.toUpperCase().equals("FROM"))
1358                                                                 throw new org.onap.portalsdk.analytics.error.ValidationException(
1359                                                                                 "|FROM keyword or a comma expected after [" + nextToken
1360                                                                                                 + "].");
1361
1362                                                         updatedReportCols.add(getParseSQLDataColumn(lastToken, nextToken,
1363                                                                         parsedSQL, updatedReportCols, false));
1364                                                         lastToken = null;
1365                                                 } // else
1366                                         } // else
1367                                 } // else
1368
1369                                 lastParsePos = curSQLParsePos;
1370                                 nextToken = getNextSQLParseToken(sql, true);
1371                         } // while
1372                 } else { // if CYMBAL Script
1373                         curSQLParsePos = 0;
1374                         Pattern re                      = null;
1375                         Matcher matcher         = null;
1376                         String extracted        = null;
1377                         nextToken = getNextCYMBALSQLParseToken(sql,true);
1378                         while (nextToken.length() > 0) {
1379                                 if (lastToken == null) lastToken = nextToken;
1380
1381                                 if( lastToken.toUpperCase().startsWith("DO DISPLAY")) {
1382                                         re              = Pattern.compile("each(.*)\\[.(.*?)\\]");   //\\[(.*?)\\]
1383                                         matcher = re.matcher(nextToken);
1384                                         if (matcher.find()) {
1385                                                 extracted = matcher.group();
1386                                                 re              = Pattern.compile("\\[(.*?)\\]");
1387                                         matcher = re.matcher(nextToken);
1388                                         if(matcher.find()) {
1389                                                 extracted = matcher.group();
1390                                                 extracted = extracted.substring(1,extracted.length()-1);
1391                                                 StringTokenizer sToken = new StringTokenizer(extracted, ",");
1392                                                 while(sToken.hasMoreTokens()) {
1393                                                 String str1 = sToken.nextToken().trim().substring(1);
1394                                             updatedReportCols.add(getParseSQLDataColumn("", str1,
1395                                                         new StringBuffer(""), updatedReportCols, true));
1396                                                 }
1397                                         }
1398
1399                                         }
1400
1401                                 }
1402                                 lastToken = nextToken;
1403                                 nextToken = getNextCYMBALSQLParseToken(sql, true);
1404                         }
1405
1406                 }
1407                 if (updatedReportCols.size() == 0)
1408                         throw new org.onap.portalsdk.analytics.error.ValidationException(
1409                                         "The SQL statement must have at least one column in the SELECT clause.");
1410                 if (getDataSourceList().getDataSource().size() == 0)
1411                         addDataSourceType(new ObjectFactory(), "du0", "DUAL", "", "DUAL", null, null, null);
1412                 DataSourceType dst = (DataSourceType) getDataSourceList().getDataSource().get(0);
1413                 dst.getDataColumnList().getDataColumn().clear();
1414
1415                 for (int i = 0; i < updatedReportCols.size(); i++) {
1416                         DataColumnType dct = (DataColumnType) updatedReportCols.get(i);
1417                         dct.setTableId(dst.getTableId());
1418                         dct.setOrderSeq(i + 1);
1419                         dst.getDataColumnList().getDataColumn().add(dct);
1420                 } // for
1421                 setReportSQL(parsedSQL.toString());
1422                 resetCache(false);
1423         } // parseReportSQL
1424
1425         private String getNextCYMBALSQLParseToken(String sql, boolean updateParsePos) {
1426                 int braketCount = 0;
1427                 boolean isInsideQuote = false;
1428                 StringBuffer nextToken = new StringBuffer();
1429                 for (int idxNext = curSQLParsePos; idxNext < sql.length(); idxNext++) {
1430                         char ch = sql.charAt(idxNext);
1431
1432                         if (ch!='\n') {
1433                                         nextToken.append(ch);
1434                                         if (updateParsePos)
1435                                                 curSQLParsePos = idxNext;
1436                         }
1437                         else {
1438                                 curSQLParsePos = idxNext+1;
1439                                 break;
1440                         }
1441                 } // for
1442
1443                 return nextToken.toString();
1444         } // getNextSQLParseToken
1445
1446         public void addChartAdditionalOptions(ObjectFactory objFactory) throws RaptorException {
1447                         ChartAdditionalOptions chartOptions = objFactory.createChartAdditionalOptions();
1448                         cr.setChartAdditionalOptions(chartOptions);
1449         }
1450
1451         public void addChartDrillOptions(ObjectFactory objFactory) throws RaptorException {
1452                 ChartDrillOptions chartOptions = objFactory.createChartDrillOptions();
1453                 cr.setChartDrillOptions(chartOptions);
1454 }
1455
1456         public void addDataminingOptions(ObjectFactory objFactory) throws RaptorException {
1457                         DataminingOptions dataminingOptions = objFactory.createDataminingOptions();
1458                         cr.setDataminingOptions(dataminingOptions);
1459         }
1460         /*public void addChartAdditionalOptions(ObjectFactory objFactory, String chartType, String chartMultiplePieOrder, String chartMultiplePieLabelDisplay,
1461                         String chartOrientation, String secondaryChartRenderer, String chartDisplay, String legendPosition,
1462                         String labelAngle) throws RaptorException {
1463                 try {
1464                         ChartAdditionalOptions chartOptions = objFactory.createChartAdditionalOptions();
1465
1466                         if (nvl(chartMultiplePieOrder).length() > 0)
1467                                 chartOptions.setChartMultiplePieOrder(chartMultiplePieOrder);
1468                         if (nvl(chartMultiplePieLabelDisplay).length() > 0)
1469                                 chartOptions.setChartMultiplePieLabelDisplay(chartMultiplePieLabelDisplay);
1470                         if (nvl(chartOrientation).length() > 0)
1471                                 chartOptions.setChartOrientation(chartOrientation);
1472                         if (nvl(secondaryChartRenderer).length() > 0)
1473                                 chartOptions.setSecondaryChartRenderer(secondaryChartRenderer);
1474                         if (nvl(chartDisplay).length() > 0)
1475                                 chartOptions.setChartDisplay(chartDisplay);
1476                         if (nvl(legendPosition).length() > 0)
1477                                 chartOptions.setLegendPosition(legendPosition);
1478                         if (nvl(labelAngle).length() > 0)
1479                                 chartOptions.setLabelAngle(labelAngle);
1480
1481                 cr.setChartAdditionalOptions(chartOptions);
1482                 } catch (JAXBException ex) {
1483                         throw new RaptorException(ex.getMessage(), ex.getCause());
1484                 }
1485         } // addChartAdditionalOptions*/
1486
1487
1488 } // ReportDefinition