/*
 * ============LICENSE_START==========================================
 * ONAP Portal SDK
 * ===================================================================
 * Copyright © 2017 AT&T Intellectual Property. All rights reserved.
 * ===================================================================
 *
 * Unless otherwise specified, all software contained herein is licensed
 * under the Apache License, Version 2.0 (the "License");
 * you may not use this software 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.
 *
 * Unless otherwise specified, all documentation contained herein is licensed
 * under the Creative Commons License, Attribution 4.0 Intl. (the "License");
 * you may not use this documentation except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *             https://creativecommons.org/licenses/by/4.0/
 *
 * Unless required by applicable law or agreed to in writing, documentation
 * 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.
 *
 * ============LICENSE_END============================================
 *
 * 
 */
/* ===========================================================================================
 * This class is part of <I>RAPTOR (Rapid Application Programming Tool for OLAP Reporting)</I> 
 * 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);<UL><LI> request Object is passed to prevent caching user/roles - Datamining/Hosting. </LI></UL>	
 * 13-Aug-2009 : Version 8.5 (Sundar);<UL><LI>Refresh is added while running report.</LI>
 * 									 </UL>	
 * 27-Jul-2009 : Version 8.4 (Sundar);<UL><LI> A new sort order PUBLIC is added.</LI>
 * 									  <LI> In Public reports option it brings all the reports 
 * 											including the one which logged in user didn't create 
 * 											and which is not public. This is available for Super users and "Admin equivalent Super Users".</LI>
 * 									 </UL>	
 *
 */
package org.onap.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.apache.commons.io.FilenameUtils;
import org.onap.portalsdk.analytics.controller.ErrorHandler;
import org.onap.portalsdk.analytics.error.RaptorException;
import org.onap.portalsdk.analytics.model.search.ReportSearchResult;
import org.onap.portalsdk.analytics.model.search.ReportSearchResultJSON;
import org.onap.portalsdk.analytics.model.search.SearchResultColumn;
import org.onap.portalsdk.analytics.model.search.SearchResultField;
import org.onap.portalsdk.analytics.model.search.SearchResultRow;
import org.onap.portalsdk.analytics.system.AppUtils;
import org.onap.portalsdk.analytics.system.DbUtils;
import org.onap.portalsdk.analytics.system.Globals;
import org.onap.portalsdk.analytics.util.AppConstants;
import org.onap.portalsdk.analytics.util.DataSet;
import org.onap.portalsdk.analytics.util.HtmlStripper;

public class SearchHandler extends org.onap.portalsdk.analytics.RaptorObject {
    private static final String HTML_FORM = "forma";
	private final static String PRIVATE_ICON = "<img border=0 src="
			+ AppUtils.getImgFolderURL()
			+ "accessicon.gif alt=Private width=16 height=12>&nbsp;";

	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(FilenameUtils.normalize(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 = "'<a class=\"hyperref1\" href=''#'' onClick=''document."+HTML_FORM+"."+AppConstants.RI_ACTION+".value=\"report.run\";"+
                               "document."+HTML_FORM+".c_master.value=\"'|| cr.rep_id||'\";document."+HTML_FORM+".refresh.value=\"Y\";document."+HTML_FORM+".submit();return false;''>'";
		for (Iterator iter = AppUtils.getUserRoles(request).iterator(); iter.hasNext();)
			roleList.append("," + ((String) iter.next()));
        //<a href="#" alt="Run report" onClick="document.forma.r_action.value='report.run'; document.forma.c_master.value='1073';">
		String sql = Globals.getLoadReportSearchResult();
		String rep_id = "";
		String rep_id_sql_value = "";
		String rep_id_options = "";
		String rep_id_sql = Globals.getLoadReportSearchRepIdSql();
		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_sql_value = rep_id;
					break;
				case "1":
					rep_id_sql = " AND cr.rep_id < ? ";
					rep_id_sql_value = rep_id;
					break;
				case "2":
					rep_id_sql = " AND cr.rep_id > ? ";
					rep_id_sql_value = rep_id;
					break;
				default: 
					rep_id_sql = Globals.getLoadReportSearchRepIdSql();
					break;
				}
			} else {
				rep_id_sql = " AND cr.rep_id = ? ";
				rep_id_sql_value = rep_id;
			}
		} else {
			rep_id_sql = Globals.getLoadReportSearchRepIdSql(); //equal is default
		}
		
		sql = sql.replace("[fReportID]", rep_id_sql);
		
		String rep_name = "";
		String rep_name_sql_value = "";
		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_sql_value = rep_name+"%";
					break;
				case "1":
					rep_name_sql = " AND UPPER(cr.title) LIKE UPPER(?) ";
					rep_name_sql_value = "%"+rep_name;
					break;
				case "2":
					rep_name_sql = " AND UPPER(cr.title) LIKE UPPER(?) ";
					rep_name_sql_value = "%"+rep_name+"%";
					break;
				default: 
					rep_name_sql = " AND UPPER(cr.title) LIKE UPPER(?) ";
					rep_name_sql_value = "%%";
					break;
				}
			} else {
				rep_name_sql = " AND UPPER(cr.title) LIKE UPPER(?) "; //contains is default
				rep_name_sql_value = "%"+rep_name+"%";
			}
		} else {
			rep_name_sql = " AND UPPER(cr.title) LIKE UPPER(?) ";
			rep_name_sql_value = "%%";
		}
		sql = sql.replace("[fReportName]",rep_name_sql);

		if (menuId.length() > 0){
			String sql_add = Globals.getLoadReportSearchInstr();
			sql+= sql_add;
		}

		String user_sql = Globals.getLoadReportSearchResultUser();
		
		String public_sql = Globals.getLoadReportSearchResultPublic();
		
		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;
		}
		
		
		
		if (sortOrder.equals(AppConstants.RI_F_OWNER_ID)){
			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
			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());

		DataSet ds = DbUtils.executeQuery(sql, rep_name_sql_value, rep_id_sql_value);

		ReportSearchResultJSON rsr = new ReportSearchResultJSON(0, 6, 7);
		rsr.parseData(ds, request, 0, 20, 6, 7);

		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 = "'<a class=\"hyperref1\" href=''#'' onClick=''document."+HTML_FORM+"."+AppConstants.RI_ACTION+".value=\"report.run\";"+
		                       "document."+HTML_FORM+".c_master.value=\"'|| cr.rep_id||'\";document."+HTML_FORM+".submit();return false;''>'";
		for (Iterator iter = AppUtils.getUserRoles(request).iterator(); iter.hasNext();)
			roleList.append("," + ((String) iter.next()));
		//<a href="#" alt="Run report" onClick="document.forma.r_action.value='report.run'; document.forma.c_master.value='1073';">
		
		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){

			String sql_add = Globals.getLoadReportSearchInstr();
			sql+= sql_add;
		}
		
			String user_sql = Globals.getLoadReportSearchResultUser();
				
			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)){
			
			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
			sql += " ORDER BY cr.title";
		
		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