From 1faf201e8608dfa4d7af3460fd3d1fc7ebec398b Mon Sep 17 00:00:00 2001 From: talasila Date: Tue, 7 Feb 2017 11:47:55 -0500 Subject: Initial OpenECOMP Portal SDK commit Change-Id: I66a3491600a4b9ea241128dc29267eed6a78ed76 Signed-off-by: talasila --- .../portalsdk/analytics/model/SearchHandler.java | 490 +++++++++++++++++++++ 1 file changed, 490 insertions(+) create mode 100644 ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/SearchHandler.java (limited to 'ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/SearchHandler.java') diff --git a/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/SearchHandler.java b/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/SearchHandler.java new file mode 100644 index 00000000..428bc90a --- /dev/null +++ b/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/SearchHandler.java @@ -0,0 +1,490 @@ +/*- + * ================================================================================ + * eCOMP Portal SDK + * ================================================================================ + * Copyright (C) 2017 AT&T Intellectual Property + * ================================================================================ + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * ================================================================================ + */ +/* =========================================================================================== + * This class is part of RAPTOR (Rapid Application Programming Tool for OLAP Reporting) + * Raptor : This tool is used to generate different kinds of reports with lot of utilities + * =========================================================================================== + * + * ------------------------------------------------------------------------------------------- + * SearchHandler.java - This class is used to search reports and sort them in different order + * based on preference. It can also download the list in CSV format. + * ------------------------------------------------------------------------------------------- + * + * + * + * Changes + * ------- + * 18-Aug-2009 : Version 8.5.1 (Sundar); + * 13-Aug-2009 : Version 8.5 (Sundar); + * 27-Jul-2009 : Version 8.4 (Sundar); + * + */ +package org.openecomp.portalsdk.analytics.model; + +import java.io.BufferedWriter; +import java.io.FileWriter; +import java.io.IOException; +import java.io.PrintWriter; +import java.io.Writer; +import java.util.Iterator; + +import javax.servlet.http.HttpServletRequest; + +import org.openecomp.portalsdk.analytics.controller.ErrorHandler; +import org.openecomp.portalsdk.analytics.error.RaptorException; +import org.openecomp.portalsdk.analytics.model.search.ReportSearchResult; +import org.openecomp.portalsdk.analytics.model.search.ReportSearchResultJSON; +import org.openecomp.portalsdk.analytics.model.search.SearchResultColumn; +import org.openecomp.portalsdk.analytics.model.search.SearchResultField; +import org.openecomp.portalsdk.analytics.model.search.SearchResultRow; +import org.openecomp.portalsdk.analytics.system.AppUtils; +import org.openecomp.portalsdk.analytics.system.DbUtils; +import org.openecomp.portalsdk.analytics.system.Globals; +import org.openecomp.portalsdk.analytics.util.AppConstants; +import org.openecomp.portalsdk.analytics.util.DataSet; +import org.openecomp.portalsdk.analytics.util.HtmlStripper; + +public class SearchHandler extends org.openecomp.portalsdk.analytics.RaptorObject { + private static final String HTML_FORM = "forma"; + private final static String PRIVATE_ICON = "Private "; + + public SearchHandler() { + } + + public void createCSVFileContent(Writer out, ReportSearchResult sr) throws IOException { + PrintWriter csvOut = new PrintWriter(out); + HtmlStripper strip = new HtmlStripper(); + + for (int c = 1; c < sr.getNumColumns(); c++) { + SearchResultColumn column = sr.getColumn(c); + if (column.getLinkURL() == null) + csvOut.print("\"" + column.getColumnTitle() + "\","); + } // for + csvOut.println(); + + for (int r = 0; r < sr.getNumRows(); r++) { + SearchResultRow row = sr.getRow(r); + + int c = 1; + for (row.resetNext(1); row.hasNext();) { + SearchResultField field = row.getNext(); + if (sr.getColumn(c++).getLinkURL() == null) + if (field.getDisplayValue().startsWith(PRIVATE_ICON)) + csvOut.print("\"" + + strip.stripHtml(field.getDisplayValue().substring(PRIVATE_ICON.length())) + + "\","); + else + csvOut.print("\"" + strip.stripHtml(field.getDisplayValue()) + "\","); + } // for + + csvOut.println(); + } // for + } // createCSVFileContent + + public String saveCSVPageFile(HttpServletRequest request, ReportSearchResult sr) { + try { + String csvFName = AppUtils.generateFileName(request, + (sr.getPageNo() < 0) ? AppConstants.FT_CSV_ALL : AppConstants.FT_CSV); + + BufferedWriter csvOut = new BufferedWriter(new FileWriter(AppUtils + .getTempFolderPath() + + csvFName)); + createCSVFileContent(csvOut, sr); + csvOut.close(); + + if (sr.getPageNo() < 0) + sr.setCsvAllRowsFileName(csvFName); + else + sr.setCsvPageFileName(csvFName); + + return csvFName; + } catch (Exception e) { + (new ErrorHandler()).processError(request, "Exception saving data to CSV file: " + + e.getMessage()); + return null; + } + } // saveCSVPageFile + + public ReportSearchResultJSON loadReportSearchResult(HttpServletRequest request) + throws RaptorException { + String userID = AppUtils.getUserID(request); + String fReportID = nvl(AppUtils.getRequestValue(request, AppConstants.RI_F_REPORT_ID)); + String fReportName = nvl(AppUtils.getRequestValue(request, + AppConstants.RI_F_REPORT_NAME)); + String sortOrder = nvl(AppUtils.getRequestValue(request, AppConstants.RI_SORT_ORDER), + AppConstants.RI_F_REPORT_NAME); + + String menuId = nvl(AppUtils.getRequestValue(request, AppConstants.RI_LIST_CATEGORY)); + + boolean userOnly = AppUtils.getRequestFlag(request, AppConstants.RI_USER_REPORTS); + boolean publicOnly = AppUtils.getRequestFlag(request, AppConstants.RI_PUBLIC_REPORTS); + boolean favoriteOnly = AppUtils.getRequestFlag(request, AppConstants.RI_FAVORITE_REPORTS); + + int pageNo = 0; + try { + pageNo = Integer.parseInt(request.getParameter(AppConstants.RI_NEXT_PAGE)); + } catch (Exception e) { + } + + StringBuffer roleList = new StringBuffer(); + roleList.append("-1"); + String rep_title_sql = "''"; + for (Iterator iter = AppUtils.getUserRoles(request).iterator(); iter.hasNext();) + roleList.append("," + ((String) iter.next())); + // + /*String sql = "SELECT cr.rep_id, " + + "cr.rep_id report_id, " + + rep_title_sql+ + "||DECODE(cr.public_yn, 'Y', '', '" + + PRIVATE_ICON + + "')||cr.title||'' title, " + + "cr.descr, " + + "au.first_name||' '||au.last_name owner_name, " + + "TO_CHAR(cr.create_date, 'MM/DD/YYYY') create_date, " + + "DECODE(NVL(cr.owner_id, cr.create_id), " + + userID + + ", 'N', NVL(ra.read_only_yn, 'Y')) read_only_yn, " + + "DECODE(NVL(cr.owner_id, cr.create_id), " + + userID + + ", 'Y', 'N') user_is_owner_yn, " + + "case when report_xml like '%N%' " + + "then 'N' " + + "when report_xml like '%Y%' " + + "or 1 = (select distinct 1 from cr_report_schedule where rep_id = cr.rep_id) " + + "then 'Y' " + + "else 'N' end " + + "FROM cr_report cr, " + + "app_user au, " + + "(SELECT rep_id, " + + "MIN(read_only_yn) read_only_yn " + + "FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = " + + userID + + ") " + + "UNION ALL " + + "(SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN (" + + roleList.toString() + "))" + ") report_access " + "GROUP BY rep_id) ra " + + "WHERE TO_CHAR(cr.rep_id) = nvl('" + fReportID + + "', TO_CHAR(cr.rep_id)) AND " + "UPPER(cr.title) LIKE UPPER('%" + + fReportName + "%') AND " + "nvl(cr.owner_id, cr.create_id) = au.user_id " + + "AND cr.rep_id = ra.rep_id (+) ";*/ + + String sql = Globals.getLoadReportSearchResult(); + String rep_id = ""; + String rep_id_options = ""; + String rep_id_sql = " AND FORMAT(cr.rep_id, 0) like coalesce('%%', FORMAT(cr.rep_id, 0)) "; + if(request.getParameter("rep_id")!=null) { + rep_id = request.getParameter("rep_id"); + } + if(request.getParameter("rep_id_options")!=null) { + rep_id_options = request.getParameter("rep_id_options"); + } + + /*Default: AND FORMAT(cr.rep_id, 0) like coalesce('%%', FORMAT(cr.rep_id, 0)) */ + + /*Equal to AND cr.rep_id = 1000 0 */ + /*Less than : AND cr.rep_id < 1000 1 */ + /*Greater than AND cr.rep_id > 1000 2 */ + + + if(AppUtils.nvl(rep_id).length()>0 ) { + if(AppUtils.nvl(rep_id_options).length()>0 ) { + switch (rep_id_options) { + case "0": + rep_id_sql = " AND cr.rep_id = "+ rep_id+" "; + break; + case "1": + rep_id_sql = " AND cr.rep_id < "+ rep_id+" "; + break; + case "2": + rep_id_sql = " AND cr.rep_id > "+ rep_id+" "; + break; + default: + rep_id_sql = " AND FORMAT(cr.rep_id, 0) like coalesce('%%', FORMAT(cr.rep_id, 0)) "; + break; + } + } else { + rep_id_sql = " AND cr.rep_id = "+ rep_id+" "; + } + } else { + rep_id_sql = " AND FORMAT(cr.rep_id, 0) like coalesce('%%', FORMAT(cr.rep_id, 0)) "; //equal is default + } + + sql = sql.replace("[fReportID]", rep_id_sql); + + String rep_name = ""; + String rep_name_options = ""; + String rep_name_sql = " AND UPPER(cr.title) LIKE UPPER('%%') "; + if(request.getParameter("rep_name")!=null) { + rep_name = request.getParameter("rep_name"); + } + if(request.getParameter("rep_name_options")!=null) { + rep_name_options = request.getParameter("rep_name_options"); + } + + /* Report name AND UPPER(cr.title) LIKE UPPER('Dash%') 0 */ + + /* Report name AND UPPER(cr.title) LIKE UPPER('%1') 1 */ + /* Report name AND UPPER(cr.title) LIKE UPPER('%1%') 2 */ + + if(AppUtils.nvl(rep_name).length()>0 ) { + if(AppUtils.nvl(rep_name_options).length()>0 ) { + switch (rep_name_options) { + case "0": + rep_name_sql = " AND UPPER(cr.title) LIKE UPPER('"+rep_name+"%') "; + break; + case "1": + rep_name_sql = " AND UPPER(cr.title) LIKE UPPER('%"+rep_name+"') "; + break; + case "2": + rep_name_sql = " AND UPPER(cr.title) LIKE UPPER('%"+rep_name+"%') "; + break; + default: + rep_name_sql = " AND UPPER(cr.title) LIKE UPPER('%%') "; + break; + } + } else { + rep_name_sql = " AND UPPER(cr.title) LIKE UPPER('%"+rep_name+"%') "; //contains is default + } + } else { + rep_name_sql = " AND UPPER(cr.title) LIKE UPPER('%%') "; + } + sql = sql.replace("[fReportName]", rep_name_sql); + + if (menuId.length() > 0){ + /*sql += "AND INSTR('|'||cr.menu_id||'|', '|'||'" + menuId + "'||'|') > 0 " + * +"AND + * cr.menu_approved_yn = + * 'Y' " + ;*/ + String sql_add = Globals.getLoadReportSearchInstr(); + sql+= sql_add; + } + + //String user_sql = " AND nvl(cr.owner_id, cr.create_id) = " + userID; + String user_sql = Globals.getLoadReportSearchResultUser(); + + //String public_sql = " AND (nvl(cr.owner_id, cr.create_id) = " + userID + // + " OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL)"; + String public_sql = Globals.getLoadReportSearchResultPublic(); + + //String fav_sql = " AND cr.rep_id in (select rep_id from cr_favorite_reports where user_id = " + userID +" ) "; + String fav_sql = Globals.getLoadReportSearchResultFav(); + + if (userOnly) + // My reports - user is owner + sql += " " + user_sql; + else if (publicOnly) { + // Public reports - user has read or write access to the report + // (user is owner or report is public or user has explicit user or + // role access) + if (!AppUtils.isSuperUser(request)) + sql += " " + public_sql; + } else if (favoriteOnly) { + sql += " " + public_sql; + sql += " " + fav_sql; + } else if (!AppUtils.isSuperUser(request)) { + // All reports + // If user is super user - gets unrestricted access to all reports + // (read_only gets overriden later) + // else - not super user - doesn't get access to private reports of + // other users (= Public reports); Admin users get edit right + // override later + //sql += public_sql; + sql += " " + public_sql; + } + + + + if (sortOrder.equals(AppConstants.RI_F_OWNER_ID)){ + //sql += " ORDER BY DECODE(nvl(cr.owner_id, cr.create_id), " + userID + //+ ", ' ', upper(au.first_name||' '||au.last_name)), upper(cr.title)"; + String sql_sort = Globals.getLoadReportSearchResultSort(); + sql+=" " + sql_sort; + } + else if (sortOrder.equals(AppConstants.RI_F_REPORT_ID)) + sql += " ORDER BY cr.rep_id"; + else if(sortOrder.equals(AppConstants.RI_F_REPORT_CREATE_DATE)) + sql += " ORDER BY cr.create_date"; + else if(sortOrder.equals(AppConstants.RI_F_PUBLIC)) + sql += " ORDER BY cr.public_yn desc"; + + else + // if(sortOrder.equals(AppConstants.RI_F_REPORT_NAME)) + sql += " ORDER BY upper(cr.title)"; + + sql = sql.replace("[rep_title_sql]", "cr.title"); + sql = sql.replace("[PRIVATE_ICON]", PRIVATE_ICON); + sql = sql.replace("[userID]", userID); + sql = sql.replace("[roleList.toString()]", roleList.toString()); + + //System.out.println("query is for search list is : " + sql); + DataSet ds = DbUtils.executeQuery(sql); + + ReportSearchResultJSON rsr = new ReportSearchResultJSON(0, 6, 7); + rsr.parseData(ds, request, 0, 20, 6, 7); + //saveCSVPageFile(request, rsr); + //rsr.truncateToPage(pageNo); + //saveCSVPageFile(request, rsr); + + return rsr; + } // loadReportSearchResult + + public ReportSearchResult loadFolderReportResult(HttpServletRequest request) + throws Exception { + String userID = AppUtils.getUserID(request); + String fReportID = nvl(AppUtils.getRequestValue(request, AppConstants.RI_F_REPORT_ID)); + String fReportName = nvl(AppUtils.getRequestValue(request, + AppConstants.RI_F_REPORT_NAME)); + String sortOrder = nvl(AppUtils.getRequestValue(request, AppConstants.RI_SORT_ORDER), + AppConstants.RI_F_REPORT_NAME); + + String menuId = nvl(AppUtils.getRequestValue(request, AppConstants.RI_LIST_CATEGORY)); + + boolean userOnly = AppUtils.getRequestFlag(request, AppConstants.RI_USER_REPORTS); + boolean publicOnly = AppUtils.getRequestFlag(request, AppConstants.RI_PUBLIC_REPORTS); + + int pageNo = 0; + try { + pageNo = Integer.parseInt(request.getParameter(AppConstants.RI_NEXT_PAGE)); + } catch (Exception e) { + } + + StringBuffer roleList = new StringBuffer(); + roleList.append("-1"); + String rep_title_sql = "''"; + for (Iterator iter = AppUtils.getUserRoles(request).iterator(); iter.hasNext();) + roleList.append("," + ((String) iter.next())); + // + /*String sql = "SELECT cr.rep_id, " + + "cr.rep_id report_id, " + + rep_title_sql+ + "||DECODE(cr.public_yn, 'Y', '', '" + + PRIVATE_ICON + + "')||cr.title||'' title, " + + "cr.descr, " + + "au.first_name||' '||au.last_name owner_name, " + + "TO_CHAR(cr.create_date, 'MM/DD/YYYY') create_date, " + + "DECODE(NVL(cr.owner_id, cr.create_id), " + + userID + + ", 'N', NVL(ra.read_only_yn, 'Y')) read_only_yn, " + + "DECODE(NVL(cr.owner_id, cr.create_id), " + + userID + + ", 'Y', 'N') user_is_owner_yn " + + "FROM cr_report cr, " + + "app_user au, " + + "(SELECT rep_id, " + + "MIN(read_only_yn) read_only_yn " + + "FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = " + + userID + + ") " + + "UNION ALL " + + "(SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN (" + + roleList.toString() + "))" + ") report_access " + "GROUP BY rep_id) ra " + + "WHERE TO_CHAR(cr.rep_id) = nvl('" + fReportID + + "', TO_CHAR(cr.rep_id)) AND " + "UPPER(cr.title) LIKE UPPER('%" + + fReportName + "%') AND " + "nvl(cr.owner_id, cr.create_id) = au.user_id " + + "AND cr.rep_id = ra.rep_id (+) ";*/ + + String sql = Globals.getLoadFolderReportResult(); + sql = sql.replace("[rep_title_sql]", rep_title_sql); + sql = sql.replace("[PRIVATE_ICON]", PRIVATE_ICON); + sql = sql.replace("[userID]", userID); + sql = sql.replace("[roleList.toString()]", roleList.toString()); + sql = sql.replace("[fReportID]", fReportID); + sql = sql.replace("[fReportName]", fReportName); + + if (menuId.length() > 0){ + /*sql += "AND INSTR('|'||cr.menu_id||'|', '|'||'" + menuId + "'||'|') > 0 " + * +"AND + * cr.menu_approved_yn = + * 'Y' " + ;*/ + String sql_add = Globals.getLoadReportSearchInstr(); + sql+= sql_add; + } + + //String user_sql = " AND nvl(cr.owner_id, cr.create_id) = " + userID; + String user_sql = Globals.getLoadReportSearchResultUser(); + + //String public_sql = " AND (nvl(cr.owner_id, cr.create_id) = " + userID + // + " OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL)"; + String public_sql = Globals.getLoadReportSearchResultPublic(); + + if (userOnly) + // My reports - user is owner + sql += user_sql; + else if (publicOnly) + // Public reports - user has read or write access to the report + // (user is owner or report is public or user has explicit user or + // role access) + if (!AppUtils.isSuperUser(request)) + sql += public_sql; + else if (!AppUtils.isSuperUser(request)) { + // All reports + // If user is super user - gets unrestricted access to all reports + // (read_only gets overriden later) + // else - not super user - doesn't get access to private reports of + // other users (= Public reports); Admin users get edit right + // override later + sql += public_sql; + } + + if (sortOrder.equals(AppConstants.RI_F_OWNER_ID)){ + + + //sql += " ORDER BY DECODE(nvl(cr.owner_id, cr.create_id), " + userID + // + ", ' ', au.first_name||' '||au.last_name), cr.title"; + + String sql_sort = Globals.getLoadFolderReportResultSort(); + sql+=sql_sort; + } + else if (sortOrder.equals(AppConstants.RI_F_REPORT_ID)) + sql += " ORDER BY cr.rep_id"; + else if(sortOrder.equals(AppConstants.RI_F_REPORT_CREATE_DATE)) + sql += " ORDER BY cr.create_date"; + else if(sortOrder.equals(AppConstants.RI_F_PUBLIC)) + sql += " ORDER BY cr.public_yn desc"; + else + // if(sortOrder.equals(AppConstants.RI_F_REPORT_NAME)) + sql += " ORDER BY cr.title"; + + //System.out.println("query is for search list is : " + sql); + DataSet ds = DbUtils.executeQuery(sql); + + ReportSearchResult rsr = new ReportSearchResult(-1, 6, 7); + rsr.parseData(ds, request); + saveCSVPageFile(request, rsr); + rsr.truncateToPage(pageNo); + saveCSVPageFile(request, rsr); + + return rsr; + } // loadFolderReportResult + + +} // SearchHandler -- cgit 1.2.3-korg