From f55fa6a1f3c427ff035970635aedaec16c0de4f1 Mon Sep 17 00:00:00 2001 From: Vasyl Razinkov Date: Mon, 20 May 2019 16:19:57 +0100 Subject: Fixed Sonar "Blocker Bugs" Fixed potential "thread-Leak" Change-Id: I7efcbd4d7b6b69ca1f234bed8d28cbb8e51e3e25 Signed-off-by: Vasyl Razinkov Issue-ID: PORTAL-584 --- .../portalsdk/analytics/model/ReportHandler.java | 1408 +++++++++----------- .../analytics/model/pdf/PdfReportHandler.java | 766 +++++------ .../analytics/scheduler/SchedulerUtil.java | 527 +++----- .../analytics/scheduler/SendNotifications.java | 263 ++-- .../onap/portalsdk/analytics/system/DbUtils.java | 193 ++- 5 files changed, 1322 insertions(+), 1835 deletions(-) (limited to 'ecomp-sdk/epsdk-analytics/src') diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportHandler.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportHandler.java index 56f28b54..90f3e2e1 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportHandler.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportHandler.java @@ -58,6 +58,11 @@ */ package org.onap.portalsdk.analytics.model; +import com.lowagie.text.Document; +import com.lowagie.text.Paragraph; +import com.lowagie.text.html.simpleparser.HTMLWorker; +import com.lowagie.text.html.simpleparser.StyleSheet; +import com.lowagie.text.pdf.PdfPTable; import java.io.BufferedInputStream; import java.io.BufferedWriter; import java.io.File; @@ -102,12 +107,10 @@ import java.util.concurrent.TimeoutException; import java.util.zip.ZipEntry; import java.util.zip.ZipFile; import java.util.zip.ZipOutputStream; - import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; - import org.apache.commons.io.FilenameUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; @@ -168,13 +171,6 @@ import org.onap.portalsdk.analytics.xmlobj.SemaphoreType; import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate; import org.owasp.esapi.ESAPI; -import com.lowagie.text.Document; -import com.lowagie.text.Paragraph; -import com.lowagie.text.html.simpleparser.HTMLWorker; -import com.lowagie.text.html.simpleparser.StyleSheet; -import com.lowagie.text.pdf.PdfPTable; -//import javax.servlet.RequestDispatcher; - public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(ReportHandler.class); @@ -332,7 +328,9 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { return hashMapStyles; } - private void paintExcelParams(HSSFWorkbook wb,int rowNum,int col,ArrayList paramsList, String customizedParamInfo, HSSFSheet sheet, String reportTitle, String reportDescr) throws IOException { + private void paintExcelParams(final HSSFWorkbook wb, int rowNum, final int col, final List paramsList, + final String customizedParamInfo, final HSSFSheet sheet, final String reportTitle, final String reportDescr) + throws IOException { //HSSFSheet sheet = wb.getSheet(getSheetName()); int cellNum = 0; HSSFRow row = null; @@ -569,10 +567,9 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } - - - private int paintExcelData(HSSFWorkbook wb, int rowNum, int col, ReportData rd, - HashMap styles, ReportRuntime rr, HSSFSheet sheet, String sql_whole, OutputStream sos, HttpServletRequest request) throws RaptorException { + private int paintExcelData(final HSSFWorkbook wb, int rowNum, final ReportData rd, final Map styles, + final ReportRuntime rr, final HSSFSheet sheet, final String sql_whole, + final HttpServletRequest request) throws RaptorException { int mb = 1024*1024; Runtime runtime = Runtime.getRuntime(); int returnValue = 0; @@ -2804,7 +2801,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } // if rowNum = sheet.getLastRowNum(); //System.out.println(" rowNum after Params " + rowNum); - paintExcelData(wb, rowNum, col, rd, styles,rr, sheet, "", xlsOut, request); + paintExcelData(wb, rowNum, rd, styles,rr, sheet, "", request); if (Globals.getPrintFooterInDownload() ) { rowNum = sheet.getLastRowNum(); rowNum += 2; @@ -2825,330 +2822,269 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } } // saveAsExcelFile - public void createExcelFileContent(Writer out, ReportData rd, ReportRuntime rr, HttpServletRequest request, - HttpServletResponse response, String user_id, int type) throws IOException, RaptorException { - // Adding utility for downloading Dashboard reports. - - HashMap styles = new HashMap(); - HttpSession session = request.getSession(); - ServletOutputStream sos = null; - BufferedInputStream buf = null; - HSSFWorkbook wb = null; -// if(session.getAttribute(AppConstants.SI_DASHBOARD_REP_ID)!=null) -// ReportRuntime rrDashboard = (ReportRuntime) request.getSession().getAttribute(AppConstants.SI_REPORT_RUNTIME); - String formattedDate = ""; - String xlsFName = ""; - int returnValue = 0; - boolean isDashboard = false; - if ((session.getAttribute(AppConstants.SI_DASHBOARD_REP_ID)!=null) && ( ((String) session.getAttribute(AppConstants.SI_DASHBOARD_REP_ID)).equals(rr.getReportID())) ) { - isDashboard = true; - } - if(isDashboard) { - try { - formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date()); - xlsFName = "dashboard"+formattedDate+user_id+".xls"; - - FileInputStream xlsIn = null; - POIFSFileSystem fileSystem = null; - buf = null; - FileOutputStream xlsOut = null; + public void createExcelFileContent(final Writer out, ReportData rd, final ReportRuntime rr, + final HttpServletRequest request, final HttpServletResponse response, final String user_id, final int type) + throws IOException, RaptorException { + // Adding utility for downloading Dashboard reports. + + Map styles = new HashMap(); + final HttpSession session = request.getSession(); + + HSSFWorkbook wb = null; + + int returnValue = 0; + final boolean isDashboard = (session.getAttribute(AppConstants.SI_DASHBOARD_REP_ID) != null) && + (((String) session.getAttribute(AppConstants.SI_DASHBOARD_REP_ID)).equals(rr.getReportID())); + + if (isDashboard) { + final String formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date()); + final String xlsFName = "dashboard" + formattedDate + user_id + ".xls"; + try { + FileInputStream xlsIn = null; + + final Map reportRuntimeMap = (TreeMap) request.getSession() + .getAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME_MAP); + final Map reportDataMap = (TreeMap) request.getSession() + .getAttribute(AppConstants.SI_DASHBOARD_REPORTDATA_MAP); + if (reportRuntimeMap != null) { + final Set setReportRuntime = reportRuntimeMap.entrySet(); + final Set setReportDataMap = reportDataMap.entrySet(); + final Iterator iter2 = setReportDataMap.iterator(); + int count = 0; + + for (Iterator iter = setReportRuntime.iterator(); iter.hasNext(); ) { + count++; + try { + xlsIn = + new FileInputStream(FilenameUtils.normalize(AppUtils.getTempFolderPath() + xlsFName)); + } catch (final FileNotFoundException e) { + System.out.println("File not found in the specified path."); + } + if (xlsIn != null) { + final POIFSFileSystem fileSystem = new POIFSFileSystem(xlsIn); + wb = new HSSFWorkbook(fileSystem); + } else { + wb = new HSSFWorkbook(); + } + + final Map.Entry entryData = (Entry) iter2.next(); + final Map.Entry entry = (Entry) iter.next(); + final ReportRuntime rrDashRep = (ReportRuntime) entry.getValue(); + final ReportData rdDashRep = (ReportData) entryData.getValue(); + int col = 0; + final String reportTitle = (nvl(rrDashRep.getReportTitle()).length() > 0 ? rrDashRep + .getReportTitle() + : rrDashRep.getReportName()); + final String reportDescr = rrDashRep.getReportDescr(); + if (!rdDashRep.reportRowHeaderCols.hasNext()) { + col = rdDashRep.getTotalColumnCount(); + } else { + col = rdDashRep.getTotalColumnCount(); + } + if (col == 0) { + col = 10; + } + int rowNum = 0; + final String formattedReportName = + new HtmlStripper().stripSpecialCharacters(rrDashRep.getReportName()); + + HSSFSheet sheet = null; + try { + sheet = wb.createSheet(formattedReportName); + sheet.getPrintSetup().setLandscape(true); + styles = loadStyles(rrDashRep, wb); + } catch (final IllegalArgumentException ex) { + try (final FileOutputStream xlsOut = new FileOutputStream( + FilenameUtils.normalize(AppUtils.getTempFolderPath() + xlsFName))) { + wb.write(xlsOut); + xlsOut.flush(); + } catch (final IOException e) { + logger.warn("Failed to open FileOutputStream", e); + } + continue; + } + + if (Globals.getPrintTitleInDownload() && reportTitle != null) { + paintExcelHeader(wb, rowNum, col, reportTitle, reportDescr, sheet); + rowNum = sheet.getLastRowNum(); + } else { + rowNum = 0; + } + //getting ReportRuntime object from session + if (Globals.getPrintParamsInDownload() + && rrDashRep.getParamNameValuePairsforPDFExcel(request, 1) != null) { + if (count > 1 && Globals.showParamsInAllDashboardReports()) { + paintExcelParams(wb, rowNum, col, + rrDashRep.getParamNameValuePairsforPDFExcel(request, 1), + rrDashRep.getFormFieldComments(request), sheet, reportTitle, reportDescr); + } else if (count == 1) { + paintExcelParams(wb, rowNum, col, + rrDashRep.getParamNameValuePairsforPDFExcel(request, 1), + rrDashRep.getFormFieldComments(request), sheet, reportTitle, reportDescr); + } + } // if + rowNum = sheet.getLastRowNum(); + final String sql_whole = rrDashRep.getWholeSQL(); + returnValue = paintExcelData(wb, rowNum, rdDashRep, styles, rrDashRep, sheet, sql_whole, + request); + if (returnValue == 0) { + if (Globals.getPrintFooterInDownload()) { + rowNum = sheet.getLastRowNum(); + rowNum += 2; + paintExcelFooter(wb, rowNum, col, sheet); + } + try (final FileOutputStream xlsOut = new FileOutputStream( + FilenameUtils.normalize(AppUtils.getTempFolderPath() + xlsFName))) { + wb.write(xlsOut); + //TODO Remove comment + xlsOut.flush(); + } catch (final IOException e) { + logger.warn("Failed to open FileOutputStream", e); + } + wb = null; + } + } + + response.reset(); + response.setContentType("application/vnd.ms-excel"); + response.setHeader("Content-disposition", + "attachment;filename=" + "dashboard" + formattedDate + user_id + ".xls"); + + xlsIn = new FileInputStream(AppUtils.getTempFolderPath() + xlsFName); + + int readBytes = 0; + final byte[] bOut = new byte[4096]; + try (final BufferedInputStream buf = new BufferedInputStream(xlsIn); + final ServletOutputStream sos = response.getOutputStream();) { + //read from the file; write to the ServletOutputStream + while ((readBytes = buf.read(bOut, 0, 4096)) > 0) { + buf.available(); + sos.write(bOut, 0, readBytes); + } + } catch (final IOException e) { + logger.warn("Failed to open BufferedInputStream", e); + } + } + } catch (final IOException ex) { + logger.warn("Failed to open Stream", ex); + throw ex; + } - -/* try { - xlsIn = new FileInputStream (AppUtils.getTempFolderPath() - + xlsFName); - } - catch (FileNotFoundException e) { - System.out.println ("File not found in the specified path."); - e.printStackTrace (); - } - if(xlsIn != null) { - fileSystem = new POIFSFileSystem (xlsIn); - wb = new HSSFWorkbook(fileSystem); - } else { - xlsOut = new FileOutputStream(AppUtils.getTempFolderPath() - + xlsFName); - wb = new HSSFWorkbook(); + final File f = new File(AppUtils.getTempFolderPath() + xlsFName); + if (f.exists()) { + f.delete(); } -*/ - - Map reportRuntimeMap = null; - Map reportDataMap = null; - //Map reportDisplayTypeMap = null; - reportRuntimeMap = (TreeMap) request.getSession().getAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME_MAP); - reportDataMap = (TreeMap) request.getSession().getAttribute(AppConstants.SI_DASHBOARD_REPORTDATA_MAP); - //reportDisplayTypeMap = (TreeMap) request.getSession().getAttribute(AppConstants.SI); - HSSFSheet sheet = null; - if(reportRuntimeMap!=null) { - //ServletOutputStream sos = response.getOutputStream(); - Set setReportRuntime = reportRuntimeMap.entrySet(); - Set setReportDataMap = reportDataMap.entrySet(); - Iterator iter2 = setReportDataMap.iterator(); - int count = 0; - for(Iterator iter = setReportRuntime.iterator(); iter.hasNext(); ) { - count++; - try { - xlsIn = new FileInputStream (FilenameUtils.normalize(AppUtils.getTempFolderPath() - + xlsFName)); - } - catch (FileNotFoundException e) { - System.out.println ("File not found in the specified path."); - //e.printStackTrace (); - } - if(xlsIn != null) { - fileSystem = new POIFSFileSystem (xlsIn); - wb = new HSSFWorkbook(fileSystem); - xlsOut = new FileOutputStream(FilenameUtils.normalize(AppUtils.getTempFolderPath() - + xlsFName)); - } else { - xlsOut = new FileOutputStream(FilenameUtils.normalize(AppUtils.getTempFolderPath() - + xlsFName)); - wb = new HSSFWorkbook(); - } - - Map.Entry entryData = (Entry) iter2.next(); - Map.Entry entry = (Entry) iter.next(); - //String rep_id = (String) entry.getKey(); - ReportRuntime rrDashRep = (ReportRuntime) entry.getValue(); - ReportData rdDashRep = (ReportData) entryData.getValue(); - //styles = loadStyles(rrDashRep, wb); - int col = 0; - String reportTitle = (nvl(rrDashRep.getReportTitle()).length()>0?rrDashRep.getReportTitle():rrDashRep.getReportName()); - String reportDescr = rrDashRep.getReportDescr(); - if (!rdDashRep.reportRowHeaderCols.hasNext()) - col = rdDashRep.getTotalColumnCount(); - else - col = rdDashRep.getTotalColumnCount(); - if(col==0) col=10; - int rowNum = 0; - String formattedReportName = new HtmlStripper().stripSpecialCharacters(rrDashRep.getReportName()); - - try { - sheet = wb.createSheet(formattedReportName); - sheet.getPrintSetup().setLandscape(true); - styles = loadStyles(rrDashRep, wb); - } catch (IllegalArgumentException ex) { wb.write(xlsOut);xlsOut.flush();xlsOut.close();continue;} - - if (Globals.getPrintTitleInDownload()&& reportTitle != null ) { - paintExcelHeader(wb, rowNum, col, reportTitle, reportDescr, sheet); - rowNum = sheet.getLastRowNum(); - } else - rowNum = 0; - //getting ReportRuntime object from session - if (Globals.getPrintParamsInDownload() && rrDashRep.getParamNameValuePairsforPDFExcel(request, 1) != null) { - if(count > 1 && Globals.showParamsInAllDashboardReports()) - paintExcelParams(wb,rowNum,col,rrDashRep.getParamNameValuePairsforPDFExcel(request, 1), rrDashRep.getFormFieldComments(request), sheet, reportTitle, reportDescr); - else if (count == 1) - paintExcelParams(wb,rowNum,col,rrDashRep.getParamNameValuePairsforPDFExcel(request, 1), rrDashRep.getFormFieldComments(request), sheet, reportTitle, reportDescr); - } // if - rowNum = sheet.getLastRowNum(); - String sql_whole = rrDashRep.getWholeSQL(); - returnValue = paintExcelData(wb, rowNum, col, rdDashRep, styles,rrDashRep, sheet, sql_whole, xlsOut, request); - if( returnValue == 0 ) { - if (Globals.getPrintFooterInDownload()) { - rowNum = sheet.getLastRowNum(); - rowNum += 2; - paintExcelFooter(wb, rowNum, col, sheet); - } - //wb.write(sos); - wb.write(xlsOut); - //TODO Remove comment - xlsOut.flush(); - xlsOut.close(); - wb = null; - } else { - //xlsOut.flush(); - //xlsOut.close(); - //response.reset(); - //response.setContentType("application/vnd.ms-excel"); -// RequestDispatcher dispatcher = request.getRequestDispatcher("raptor.htm?r_action=report.message"); -// request.setAttribute("message", Globals.getUserDefinedMessageForMemoryLimitReached()); -// try { -// dispatcher.forward(request, response); -// } catch (ServletException ex) {} - } - } - - response.reset(); - response.setContentType("application/vnd.ms-excel"); - response.setHeader("Content-disposition", "attachment;filename="+"dashboard"+formattedDate+user_id+".xls"); - sos = response.getOutputStream(); - xlsIn = new FileInputStream (AppUtils.getTempFolderPath() - + xlsFName); - buf = new BufferedInputStream(xlsIn); - int readBytes = 0; - byte [] bOut = new byte [4096]; - //read from the file; write to the ServletOutputStream - //while ((readBytes = buf.read()) != -1) - while ((readBytes = buf.read (bOut, 0, 4096))> 0) { - buf.available(); - sos.write (bOut, 0, readBytes); - } - - //sos.write(readBytes); - } - } catch (IOException ex) { ex.printStackTrace(); throw ex;} - - finally { - if (sos != null) - sos.close(); - if (buf != null) - buf.close(); - } - - File f = new File (AppUtils.getTempFolderPath() - + xlsFName); - if(f.exists()) f.delete(); - - } else { - wb = new HSSFWorkbook(); - // PrintWriter xlsOut = new PrintWriter(out).; - setSheetName(Globals.getSheetName()); - //ServletOutputStream sos = response.getOutputStream(); - //PrintWriter outWriter = response.getWriter(); - if (rr != null) - styles = loadStyles(rr, wb); - /* int col = 0; - if (!rd.reportRowHeaderCols.hasNext()) - col = rd.getTotalColumnCount(); - else - col = rd.getTotalColumnCount() + 1; - int rowNum = 0; - String reportTitle = rr.getReportName(); - String reportDescr = rr.getReportDescr(); - // if (Globals.getPrintTitleInDownload() && reportTitle != null) { - HSSFSheet sheet = wb.createSheet(getSheetName()); - System.out.println(" Title " + Globals.getPrintTitleInDownload()); - - if (Globals.getPrintTitleInDownload()&& reportTitle != null ) { - paintExcelHeader(wb, rowNum, col, reportTitle, reportDescr); - rowNum = wb.getSheetAt(0).getLastRowNum(); - } else - rowNum = 0; - System.out.println(" Params " + Globals.getPrintParamsInDownload()); - if (Globals.getPrintParamsInDownload() && rr.getParamNameValuePairs() != null) { - paintExcelParams(wb,rowNum,col,rr.getParamNameValuePairs()); - } // if - paintExcelData(wb, rowNum, col, rd, styles); - rowNum = wb.getSheetAt(0).getLastRowNum(); - */ - int col = 0; - //System.out.println("Row Header Count " + rd.reportRowHeaderCols.getRowCount()); - //System.out.println("Total Count " + rd.getTotalColumnCount()); - String reportTitle = (nvl(rr.getReportTitle()).length()>0?rr.getReportTitle():rr.getReportName()); - String reportDescr = rr.getReportDescr(); - - col = getColumnCountForDownloadFile(rr,rd); - /*if (!rd.reportRowHeaderCols.hasNext()) - col = rd.getTotalColumnCount(); - else - col = rd.getTotalColumnCount(); - */ - int rowNum = 0; - HSSFSheet sheet = wb.createSheet(getSheetName()); - sheet.getPrintSetup().setLandscape(true); - - if (Globals.getPrintTitleInDownload()&& reportTitle != null ) { - paintExcelHeader(wb, rowNum, col, reportTitle, reportDescr, sheet); - rowNum = sheet.getLastRowNum(); - } else - rowNum = 0; - if (Globals.getPrintParamsInDownload() && rr.getParamNameValuePairsforPDFExcel(request, 1) != null) { - ArrayList paramsList = rr.getParamNameValuePairsforPDFExcel(request, 1); - if(paramsList.size()<=0) { - paramsList = (ArrayList) request.getSession().getAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO); - } - - paintExcelParams(wb,rowNum,col,paramsList, rr.getFormFieldComments(request), sheet, reportTitle, reportDescr); - } // if - rowNum = sheet.getLastRowNum(); + } else { + wb = new HSSFWorkbook(); + setSheetName(Globals.getSheetName()); + if (rr != null) { + styles = loadStyles(rr, wb); + } - String formattedReportName = new HtmlStripper().stripSpecialCharacters(rr.getReportName()); - formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date()); - response.reset(); - response.setContentType("application/vnd.ms-excel"); - response.setHeader("Content-disposition", "attachment;filename="+formattedReportName+formattedDate+user_id+".xls"); - sos = response.getOutputStream(); - - if(type == 3 && rr.getSemaphoreList()==null && !(rr.getReportType().equals(AppConstants.RT_CROSSTAB)) ) { //type = 3 is whole - //String sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE); - //String sql_whole = rr.getWholeSQL(); - String sql_whole = ""; - sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE); - - if(sql_whole==null) { - if (!rr.getReportType().equals(AppConstants.RT_HIVE)) - sql_whole = rr.getWholeSQL(); - else - sql_whole = rr.getReportSQL(); - } - - returnValue = paintExcelData(wb, rowNum, col, rd, styles,rr, sheet, sql_whole, sos, request); - } else if(type == 2) { - returnValue = paintExcelData(wb, rowNum, col, rd, styles,rr, sheet, "", sos, request); - } else { - //String sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE); - int downloadLimit = (rr.getMaxRowsInExcelDownload()>0)?rr.getMaxRowsInExcelDownload():Globals.getDownloadLimit(); - String action = request.getParameter(AppConstants.RI_ACTION); - if(!(rr.getReportType().equals(AppConstants.RT_CROSSTAB)) && !action.endsWith("session")) { - rd = rr.loadReportData(-1, AppUtils.getUserID(request), downloadLimit,request, false /*download*/); - } - if(rr.getSemaphoreList()!=null) { - if(rr.getReportType().equals(AppConstants.RT_CROSSTAB)) { - returnValue = paintExcelData(wb, rowNum, col, rd, styles,rr, sheet, "", sos, request); - } else { - rd = rr.loadReportData(-1, AppUtils.getUserID(request), downloadLimit,request, true); - returnValue = paintExcelData(wb, rowNum, col, rd, styles,rr, sheet, "", sos, request); - } - } else { - String sql_whole = ""; - sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE); - - if(sql_whole==null) { - if (!rr.getReportType().equals(AppConstants.RT_HIVE)) - sql_whole = rr.getWholeSQL(); - else - sql_whole = rr.getReportSQL(); - } + final String reportTitle = (nvl(rr.getReportTitle()).length() > 0 ? rr.getReportTitle() + : rr.getReportName()); + final String reportDescr = rr.getReportDescr(); - returnValue = paintExcelData(wb, rowNum, col, rd, styles,rr, sheet, sql_whole, sos, request); - } - } - if( returnValue == 0 ) { - if (Globals.getPrintFooterInDownload()) { - rowNum = sheet.getLastRowNum(); - rowNum += 2; - paintExcelFooter(wb, rowNum, col, sheet); - } - //Alternatively: - wb.setPrintArea( - 0, //sheet index - 0, //start column - col, //end column - 0, //start row - rowNum //end row - ); - //TODO Remove comment - wb.write(sos); - sos.flush(); - sos.close(); - wb = null; - } else { - //sos.flush(); - //sos.close(); -/* response.reset(); - - RequestDispatcher dispatcher = request.getRequestDispatcher("/raptor.htm?action=raptor&r_action=report.message"); - request.setAttribute("message", Globals.getUserDefinedMessageForMemoryLimitReached()); - try { - dispatcher.forward(request, response); - } catch (ServletException ex) {} -*/ - } - } - } + final int col = getColumnCountForDownloadFile(rr, rd); + int rowNum = 0; + final HSSFSheet sheet = wb.createSheet(getSheetName()); + sheet.getPrintSetup().setLandscape(true); + + if (Globals.getPrintTitleInDownload() && reportTitle != null) { + paintExcelHeader(wb, rowNum, col, reportTitle, reportDescr, sheet); + rowNum = sheet.getLastRowNum(); + } else { + rowNum = 0; + } + if (Globals.getPrintParamsInDownload() && rr.getParamNameValuePairsforPDFExcel(request, 1) != null) { + List paramsList = rr.getParamNameValuePairsforPDFExcel(request, 1); + if (paramsList.size() <= 0) { + paramsList = (ArrayList) request.getSession().getAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO); + } + + paintExcelParams(wb, rowNum, col, paramsList, rr.getFormFieldComments(request), sheet, reportTitle, + reportDescr); + } // if + rowNum = sheet.getLastRowNum(); + final String formattedReportName = new HtmlStripper().stripSpecialCharacters(rr.getReportName()); + final String formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date()); + response.reset(); + response.setContentType("application/vnd.ms-excel"); + response.setHeader("Content-disposition", + "attachment;filename=" + formattedReportName + formattedDate + user_id + ".xls"); + + if (type == 3 && rr.getSemaphoreList() == null && !(rr.getReportType() + .equals(AppConstants.RT_CROSSTAB))) { //type = 3 is whole + String sql_whole = ""; + sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE); + + if (sql_whole == null) { + if (!rr.getReportType().equals(AppConstants.RT_HIVE)) { + sql_whole = rr.getWholeSQL(); + } else { + sql_whole = rr.getReportSQL(); + } + } + + returnValue = paintExcelData(wb, rowNum, rd, styles, rr, sheet, sql_whole, request); + } else if (type == 2) { + returnValue = paintExcelData(wb, rowNum, rd, styles, rr, sheet, "", request); + } else { + int downloadLimit = + (rr.getMaxRowsInExcelDownload() > 0) ? rr.getMaxRowsInExcelDownload() : Globals.getDownloadLimit(); + String action = request.getParameter(AppConstants.RI_ACTION); + if (!(rr.getReportType().equals(AppConstants.RT_CROSSTAB)) && !action.endsWith("session")) { + rd = rr.loadReportData(-1, AppUtils.getUserID(request), downloadLimit, request, false /*download*/); + } + if (rr.getSemaphoreList() != null) { + if (rr.getReportType().equals(AppConstants.RT_CROSSTAB)) { + returnValue = paintExcelData(wb, rowNum, rd, styles, rr, sheet, "", request); + } else { + rd = rr.loadReportData(-1, AppUtils.getUserID(request), downloadLimit, request, true); + returnValue = paintExcelData(wb, rowNum, rd, styles, rr, sheet, "", request); + } + } else { + String sql_whole = ""; + sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE); + + if (sql_whole == null) { + if (!rr.getReportType().equals(AppConstants.RT_HIVE)) { + sql_whole = rr.getWholeSQL(); + } else { + sql_whole = rr.getReportSQL(); + } + } + + returnValue = paintExcelData(wb, rowNum, rd, styles, rr, sheet, sql_whole, request); + } + } + if (returnValue == 0) { + if (Globals.getPrintFooterInDownload()) { + rowNum = sheet.getLastRowNum(); + rowNum += 2; + paintExcelFooter(wb, rowNum, col, sheet); + } + //Alternatively: + wb.setPrintArea( + 0, //sheet index + 0, //start column + col, //end column + 0, //start row + rowNum //end row + ); + //TODO Remove comment + try (final ServletOutputStream sos = response.getOutputStream();) { + wb.write(sos); + sos.flush(); + } catch (final IOException e) { + logger.warn("Failed to get OutputStream", e); + } + wb = null; + } + } + } public void createFlatFileContent(Writer out, ReportData rd, ReportRuntime rr, HttpServletRequest request, HttpServletResponse response, String user_id) @@ -3606,474 +3542,355 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } } - - public void createCSVFileContent(Writer out, ReportData rd, - ReportRuntime rr, HttpServletRequest request, HttpServletResponse response) - throws RaptorException { - //ArrayList reportParamNameValues = rr.getParamNameValuePairs(); - //String reportTitle = rr.getReportName(); - //String reportDescr = rr.getReportDescr(); - PrintWriter csvOut = new PrintWriter(out); - String fileName = ""; - String formattedReportName = new HtmlStripper().stripSpecialCharacters(rr.getReportName()); - String formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date()); - String fName = formattedReportName+formattedDate+AppUtils.getUserID(request); - boolean raw = AppUtils.getRequestFlag(request, "raw"); - String sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE); - - String csvFName = fName+".csv"; - String zipFName = fName+".zip"; - if(true) { - try { - fileName = AppUtils.getTempFolderPath()+""+csvFName; - csvOut = new PrintWriter(new BufferedWriter( - new OutputStreamWriter( - new FileOutputStream(fileName), "UTF-8")), false); - } catch (FileNotFoundException fex) { - fex.printStackTrace(); - } - catch (UnsupportedEncodingException fex1) { - fex1.printStackTrace(); - } - } - HtmlStripper strip = new HtmlStripper(); - //OracleConnection conn = null; - //OracleStatement st = null; - //Connection conO = null; - //Statement stO = null; - ResultSetMetaData rsmd = null; - ColumnHeaderRow chr = null; - int mb = 1024*1024; - Runtime runtime = Runtime.getRuntime(); - String valueName = ""; - if(!raw) { - String reportTitle = (nvl(rr.getReportTitle()).length()>0?rr.getReportTitle():rr.getReportName()); - csvOut.println(); - csvOut.print("\"" + reportTitle + "\","); - csvOut.println(); - - if(Globals.disclaimerPositionedTopInCSVExcel()) { - if(Globals.getShowDisclaimer()) { - csvOut.println(); - csvOut.print("\"" + Globals.getFooterFirstLine() + "\","); - csvOut.println(); - csvOut.print("\"" + Globals.getFooterSecondLine() + "\","); - csvOut.println(); - csvOut.println(); - } - } - } - if (Globals.getPrintParamsInCSVDownload() && !raw) { - ArrayList paramsList = rr.getParamNameValuePairsforPDFExcel(request, 1); - if(paramsList.size()<=0) { - paramsList = (ArrayList) request.getSession().getAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO); + public void createCSVFileContent(final Writer out, final ReportData rd, final ReportRuntime rr, + final HttpServletRequest request, final HttpServletResponse response) throws RaptorException { + + final String formattedReportName = new HtmlStripper().stripSpecialCharacters(rr.getReportName()); + final String formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date()); + final String fName = formattedReportName + formattedDate + AppUtils.getUserID(request); + final boolean raw = AppUtils.getRequestFlag(request, "raw"); + String sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE); + + final String csvFName = fName + ".csv"; + final String zipFName = fName + ".zip"; + + final String fileName = AppUtils.getTempFolderPath() + "" + csvFName; + final Runtime runtime = Runtime.getRuntime(); + final int mb = 1024 * 1024; + + try (final PrintWriter csvOut = + new PrintWriter(new BufferedWriter(new OutputStreamWriter(new FileOutputStream(fileName), "UTF-8")), + false);) { + final HtmlStripper strip = new HtmlStripper(); + ColumnHeaderRow chr = null; + if (!raw) { + final String reportTitle = (nvl(rr.getReportTitle()).length() > 0 ? rr.getReportTitle() + : rr.getReportName()); + csvOut.println(); + csvOut.print("\"" + reportTitle + "\","); + csvOut.println(); + + if (Globals.disclaimerPositionedTopInCSVExcel()) { + if (Globals.getShowDisclaimer()) { + csvOut.println(); + csvOut.print("\"" + Globals.getFooterFirstLine() + "\","); + csvOut.println(); + csvOut.print("\"" + Globals.getFooterSecondLine() + "\","); + csvOut.println(); + csvOut.println(); + } + } + } + if (Globals.getPrintParamsInCSVDownload() && !raw) { + List paramsList = rr.getParamNameValuePairsforPDFExcel(request, 1); + if (paramsList.size() <= 0) { + paramsList = (ArrayList) request.getSession().getAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO); + } + int paramSeq = 0; + for (final Iterator iter = paramsList.iterator(); iter.hasNext(); ) { + final IdNameValue value = (IdNameValue) iter.next(); + if (nvl(value.getId()).trim().length() > 0 && (!"BLANK".equals(nvl(value.getId()).trim()))) { + paramSeq += 1; + if (paramSeq <= 1) { + csvOut.print("\"" + "Run-time Parameters" + "\""); + csvOut.println(); + } + csvOut.print("\"" + value.getId() + ":" + "\","); + final String valueName = ESAPI.encoder().canonicalize(nvl(value.getName())); + if (valueName.indexOf("~") != -1 && valueName.startsWith("(")) { + csvOut.print("\"'" + valueName.replaceAll("~", ",") + "'\","); + } else { + if (valueName.startsWith("(") && valueName.endsWith(")")) { + csvOut.print( + "\"" + valueName.replaceAll("~", ",").substring(1, valueName.length() - 1) + "\","); + } else { + csvOut.print("\"" + valueName.replaceAll("~", ",") + "\","); + } + } + csvOut.println(); + } + } //for + csvOut.println(); + csvOut.println(); } - int paramSeq = 0; - for (Iterator iter = paramsList.iterator(); iter.hasNext();) { - IdNameValue value = (IdNameValue) iter.next(); - //System.out.println("\"" + value.getId() + " = " + value.getName() + "\""); - if(nvl(value.getId()).trim().length()>0 && (!nvl(value.getId()).trim().equals("BLANK"))) { - paramSeq += 1; - if(paramSeq <= 1) { - csvOut.print("\"" + "Run-time Parameters" + "\""); - csvOut.println(); - //strBuf.append("Run-time Parameters\n"); - } - csvOut.print("\"" + value.getId() +":" + "\","); - valueName = ESAPI.encoder().canonicalize(nvl(value.getName())); - if(valueName.indexOf("~")!= -1 && valueName.startsWith("(")) { - csvOut.print("\"'" + valueName.replaceAll("~",",")+ "'\","); - } else { - if(valueName.startsWith("(") && valueName.endsWith(")")) { - csvOut.print("\"" + valueName.replaceAll("~",",").substring(1, valueName.length()-1)+ "\","); - } else - csvOut.print("\"" + valueName.replaceAll("~",",")+ "\","); - } - csvOut.println(); - - //strBuf.append(value.getId()+": "+ value.getName()+"\n"); - } - } //for - csvOut.println(); - csvOut.println(); - } - - System.out.println("##### Heap utilization statistics [MB] #####"); - System.out.println("Used Memory:" - + (runtime.maxMemory() - runtime.freeMemory()) / mb); - System.out.println("Free Memory:" - + runtime.freeMemory() / mb); - System.out.println("Total Memory:" + runtime.totalMemory() / mb); - System.out.println("Max Memory:" + runtime.maxMemory() / mb); - - sql_whole = ""; - sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE); - - if(sql_whole==null) { - if (!rr.getReportType().equals(AppConstants.RT_HIVE)) - sql_whole = rr.getWholeSQL(); - else - sql_whole = rr.getReportSQL(); - } - - - if(nvl(sql_whole).length()>0) { - try(Connection conn = ConnectionUtils.getConnection(rr.getDbInfo()); - Statement st = conn.createStatement(); - ResultSet rs = st.executeQuery(sql_whole)) { - - //conn.setDefaultRowPrefetch(1000); - //st.setFetchDirection(ResultSet.TYPE_FORWARD_ONLY); - //st.setFetchSize(1000); - System.out.println("************* Map Whole SQL *************"); - System.out.println(sql_whole); - System.out.println("*****************************************"); - //st.setFetchSize(1000); - rsmd = rs.getMetaData(); - int numberOfColumns = rsmd.getColumnCount(); - HashMap colHash = new HashMap(); - String title = ""; - - if(rd!=null) { - - /*if(rd.reportTotalRowHeaderCols!=null) { - csvOut.print("\"" + "#" + "\","); - }*/ - - for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext();) { - chr = rd.reportColumnHeaderRows.getNext(); - for (chr.resetNext(); chr.hasNext();) { - ColumnHeader ch = chr.getNext(); - title = ch.getColumnTitle(); - title = Utils.replaceInString(title,"_nl_", " \n"); - if(ch.isVisible() && nvl(title).length()>0) { - csvOut.print("\"" + title + "\","); - for (int i = 1; i < ch.getColSpan(); i++) - csvOut.print(","); - } - } // for - - csvOut.println(); - } // for - int rowCount = 0; - while(rs.next()) { -/* if(runtime.freeMemory()/mb <= ((runtime.maxMemory()/mb)*Globals.getMemoryThreshold()/100) ) { - csvOut.print(Globals.getUserDefinedMessageForMemoryLimitReached() + " " + rowCount +"records out of " + rr.getReportDataSize() + " were downloaded to CSV."); - break; - } -*/ rowCount++; - //if(!raw) { - colHash = new HashMap(); - for (int i = 1; i <= numberOfColumns; i++) { - colHash.put(rsmd.getColumnLabel(i).toUpperCase(), rs.getString(i)); - } - /*if(rd.reportDataTotalRow!=null) { - csvOut.print("\"" + rowCount + "\","); - }*/ - for (chr.resetNext(); chr.hasNext();) { - ColumnHeader ch = chr.getNext(); - title = ch.getColumnTitle(); - title = Utils.replaceInString(title,"_nl_", " \n"); - - if(ch.isVisible() && nvl(title).length()>0) { - csvOut.print("\"" + strip.stripCSVHtml(nvl((String)colHash.get(ch.getColId().toUpperCase()))) + "\","); - } - - } - csvOut.println(); - /*} else { - for (int i = 1; i <= numberOfColumns; i++) { - csvOut.print("\"" + strip.stripCSVHtml( rs.getString(i)) + "\","); - } - csvOut.println(); - }*/ - - } - - if(rd.reportDataTotalRow!=null) { - for (rd.reportDataTotalRow.resetNext(); rd.reportDataTotalRow.hasNext();) { - DataRow dr = rd.reportDataTotalRow.getNext(); - csvOut.print("\"" + "Total" + "\","); - dr.resetNext();dr.getNext(); - for (; dr.hasNext();) { - DataValue dv = dr.getNext(); - if(dv.isVisible()) { - csvOut.print("\"" + strip.stripCSVHtml(dv.getDisplayValue()) + "\","); - } - } // for - - csvOut.println(); - } - } - - if(rowCount == 0) { - csvOut.print("\"No Data Found \""); - } - } else { - csvOut.print("\"No Data Found \""); - } - - } catch (SQLException ex) { - throw new RaptorException(ex); - } catch (ReportSQLException ex) { - throw new RaptorException(ex); - } catch (Exception ex) { - throw new RaptorException (ex); - } - - if(!raw) { - if(!Globals.disclaimerPositionedTopInCSVExcel()) { - if(Globals.getShowDisclaimer()) { - csvOut.print("\"" + Globals.getFooterFirstLine() + "\","); - csvOut.println(); - csvOut.print("\"" + Globals.getFooterSecondLine() + "\","); - csvOut.println(); - } - } - } - - // csvOut.flush(); - } else { - boolean firstPass = true; - if(rd!=null) { - if(rd.reportTotalRowHeaderCols!=null) { - csvOut.print("\"" + "#" + "\","); - } - for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext();) { - chr = rd.reportColumnHeaderRows.getNext(); - for (rd.reportRowHeaderCols.resetNext(1); rd.reportRowHeaderCols.hasNext();) { - RowHeaderCol rhc = rd.reportRowHeaderCols.getNext(); + System.out.println("##### Heap utilization statistics [MB] #####"); + System.out.println("Used Memory:" + (runtime.maxMemory() - runtime.freeMemory()) / mb); + System.out.println("Free Memory:" + runtime.freeMemory() / mb); + System.out.println("Total Memory:" + runtime.totalMemory() / mb); + System.out.println("Max Memory:" + runtime.maxMemory() / mb); - if (firstPass) - csvOut.print("\"" + rhc.getColumnTitle() + "\""); - csvOut.print(","); - } // for - firstPass = false; + sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE); - for (chr.resetNext(); chr.hasNext();) { - ColumnHeader ch = chr.getNext(); - if(ch.isVisible()) { - csvOut.print("\"" + ch.getColumnTitle() + "\","); - for (int i = 1; i < ch.getColSpan(); i++) - csvOut.print(","); - } - } // for + if (sql_whole == null) { + if (!AppConstants.RT_HIVE.equals(rr.getReportType())) { + sql_whole = rr.getWholeSQL(); + } else { + sql_whole = rr.getReportSQL(); + } + } - csvOut.println(); - } // for + if (nvl(sql_whole).length() > 0) { + try (final Connection conn = ConnectionUtils.getConnection(rr.getDbInfo()); + final Statement st = conn.createStatement(); + final ResultSet rs = st.executeQuery(sql_whole)) { + + System.out.println("************* Map Whole SQL *************"); + System.out.println(sql_whole); + System.out.println("*****************************************"); + final ResultSetMetaData rsmd = rs.getMetaData(); + int numberOfColumns = rsmd.getColumnCount(); + String title = ""; + + if (rd != null) { + for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext(); ) { + chr = rd.reportColumnHeaderRows.getNext(); + for (chr.resetNext(); chr.hasNext(); ) { + ColumnHeader ch = chr.getNext(); + title = ch.getColumnTitle(); + title = Utils.replaceInString(title, "_nl_", " \n"); + if (ch.isVisible() && nvl(title).length() > 0) { + csvOut.print("\"" + title + "\","); + for (int i = 1; i < ch.getColSpan(); i++) { + csvOut.print(","); + } + } + } // for + + csvOut.println(); + } // for + int rowCount = 0; + while (rs.next()) { + rowCount++; + final Map colHash = new HashMap(); + for (int i = 1; i <= numberOfColumns; i++) { + colHash.put(rsmd.getColumnLabel(i).toUpperCase(), rs.getString(i)); + } + for (chr.resetNext(); chr.hasNext(); ) { + final ColumnHeader ch = chr.getNext(); + title = ch.getColumnTitle(); + title = Utils.replaceInString(title, "_nl_", " \n"); + + if (ch.isVisible() && nvl(title).length() > 0) { + csvOut.print( + "\"" + strip + .stripCSVHtml(nvl((String) colHash.get(ch.getColId().toUpperCase()))) + + "\","); + } + + } + csvOut.println(); + } + + if (rd.reportDataTotalRow != null) { + for (rd.reportDataTotalRow.resetNext(); rd.reportDataTotalRow.hasNext(); ) { + final DataRow dr = rd.reportDataTotalRow.getNext(); + csvOut.print("\"" + "Total" + "\","); + dr.resetNext(); + dr.getNext(); + for (; dr.hasNext(); ) { + final DataValue dv = dr.getNext(); + if (dv.isVisible()) { + csvOut.print("\"" + strip.stripCSVHtml(dv.getDisplayValue()) + "\","); + } + } // for + + csvOut.println(); + } + } + + if (rowCount == 0) { + csvOut.print("\"No Data Found \""); + } + } else { + csvOut.print("\"No Data Found \""); + } + + } catch (final SQLException ex) { + throw new RaptorException(ex); + } catch (final ReportSQLException ex) { + throw new RaptorException(ex); + } catch (final Exception ex) { + throw new RaptorException(ex); + } + + if (!raw) { + if (!Globals.disclaimerPositionedTopInCSVExcel()) { + if (Globals.getShowDisclaimer()) { + csvOut.print("\"" + Globals.getFooterFirstLine() + "\","); + csvOut.println(); + csvOut.print("\"" + Globals.getFooterSecondLine() + "\","); + csvOut.println(); + } + } + } + } else { + boolean firstPass = true; + if (rd != null) { + if (rd.reportTotalRowHeaderCols != null) { + csvOut.print("\"" + "#" + "\","); + } + + for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext(); ) { + chr = rd.reportColumnHeaderRows.getNext(); + for (rd.reportRowHeaderCols.resetNext(1); rd.reportRowHeaderCols.hasNext(); ) { + RowHeaderCol rhc = rd.reportRowHeaderCols.getNext(); + + if (firstPass) { + csvOut.print("\"" + rhc.getColumnTitle() + "\""); + } + csvOut.print(","); + } // for + firstPass = false; + + for (chr.resetNext(); chr.hasNext(); ) { + ColumnHeader ch = chr.getNext(); + if (ch.isVisible()) { + csvOut.print("\"" + ch.getColumnTitle() + "\","); + for (int i = 1; i < ch.getColSpan(); i++) { + csvOut.print(","); + } + } + } // for + + csvOut.println(); + } // for + + firstPass = true; + int rowCount = 0; + for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext(); ) { + if (rd.reportDataTotalRow != null) { + rowCount++; + csvOut.print("\"" + rowCount + "\","); + } + + final DataRow dr = rd.reportDataRows.getNext(); + + for (rd.reportRowHeaderCols.resetNext(1); rd.reportRowHeaderCols.hasNext(); ) { + final RowHeaderCol rhc = rd.reportRowHeaderCols.getNext(); + if (firstPass) { + rhc.resetNext(); + } + final RowHeader rh = rhc.getNext(); + + csvOut.print("\"" + strip.stripCSVHtml(rh.getRowTitle()) + "\","); + } // for + firstPass = false; + + for (dr.resetNext(); dr.hasNext(); ) { + DataValue dv = dr.getNext(); + if (dv.isVisible()) { + csvOut.print("\"" + strip.stripCSVHtml(dv.getDisplayValue()) + "\","); + } + } // for + + csvOut.println(); + } // for + if (rd.reportDataTotalRow != null) { + for (rd.reportDataTotalRow.resetNext(); rd.reportDataTotalRow.hasNext(); ) { + DataRow dr = rd.reportDataTotalRow.getNext(); + csvOut.print("\"" + "Total" + "\","); + firstPass = false; + + for (dr.resetNext(); dr.hasNext(); ) { + DataValue dv = dr.getNext(); + if (dv.isVisible()) { + csvOut.print("\"" + strip.stripCSVHtml(dv.getDisplayValue()) + "\","); + } + } // for + + csvOut.println(); + } + } + + if (!raw) { + if (!Globals.disclaimerPositionedTopInCSVExcel()) { + if (Globals.getShowDisclaimer()) { + csvOut.print("\"" + Globals.getFooterFirstLine() + "\","); + csvOut.println(); + csvOut.print("\"" + Globals.getFooterSecondLine() + "\","); + csvOut.println(); + } + } + } + } else { + csvOut.print("\"No Data Found \""); + } + } + csvOut.flush(); + } catch (final FileNotFoundException fex) { + logger.warn("Failed to open/read/write file", fex); + } catch (final UnsupportedEncodingException fex1) { + logger.warn("Failed to open/read/write file", fex1); + } - firstPass = true; - int rowCount = 0; - for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext();) { - if(rd.reportDataTotalRow!=null) { - rowCount++; - csvOut.print("\"" + rowCount + "\","); - } - - DataRow dr = rd.reportDataRows.getNext(); + if (!raw) { + try (final ZipOutputStream zos = + new ZipOutputStream(new FileOutputStream(AppUtils.getTempFolderPath() + "" + zipFName)); + final FileInputStream fis = new FileInputStream(fileName)) { - for (rd.reportRowHeaderCols.resetNext(1); rd.reportRowHeaderCols.hasNext();) { - RowHeaderCol rhc = rd.reportRowHeaderCols.getNext(); - if (firstPass) - rhc.resetNext(); - RowHeader rh = rhc.getNext(); + int size = 0; + final byte[] buffer = new byte[1024]; - csvOut.print("\"" + strip.stripCSVHtml(rh.getRowTitle()) + "\","); - } // for - firstPass = false; + final ZipEntry entry = new ZipEntry(csvFName); + zos.putNextEntry(entry); - for (dr.resetNext(); dr.hasNext();) { - DataValue dv = dr.getNext(); - if(dv.isVisible()) - csvOut.print("\"" + strip.stripCSVHtml(dv.getDisplayValue()) + "\","); - } // for + // read data to the end of the source file and write it to the zip + // output stream. + while ((size = fis.read(buffer, 0, buffer.length)) > 0) { + zos.write(buffer, 0, size); + } + zos.closeEntry(); - csvOut.println(); - } // for - if(rd.reportDataTotalRow!=null) { - for (rd.reportDataTotalRow.resetNext(); rd.reportDataTotalRow.hasNext();) { - DataRow dr = rd.reportDataTotalRow.getNext(); - csvOut.print("\"" + "Total" + "\","); - firstPass = false; - - for (dr.resetNext(); dr.hasNext();) { - DataValue dv = dr.getNext(); - if(dv.isVisible()) - csvOut.print("\"" + strip.stripCSVHtml(dv.getDisplayValue()) + "\","); - } // for - - csvOut.println(); - } - } - - if(!raw) { - if(!Globals.disclaimerPositionedTopInCSVExcel()) { - if(Globals.getShowDisclaimer()) { - csvOut.print("\"" + Globals.getFooterFirstLine() + "\","); - csvOut.println(); - csvOut.print("\"" + Globals.getFooterSecondLine() + "\","); - csvOut.println(); - } - } - } + } catch (final Exception e) { + logger.warn("Failed to open/read/write file", e); + } + } - //csvOut.flush(); + response.reset(); + final File file; + + if (!raw) { + response.setContentType("application/octet-stream"); + response.setHeader("Content-disposition", "attachment;filename=" + fName + ".zip"); + file = new File(AppUtils.getTempFolderPath() + "" + fName + ".zip"); } else { - csvOut.print("\"No Data Found \""); + response.setContentType("application/csv"); + response.setHeader("Content-disposition", "attachment;filename=" + fName + ".csv"); + file = new File(AppUtils.getTempFolderPath() + "" + fName + ".csv"); } - } - csvOut.flush(); - csvOut.close(); - -/* - if (Globals.getPrintTitleInDownload() && reportTitle != null) { - csvOut.println(); - csvOut.println("\"" + reportTitle + "\""); - csvOut.println(); - if (Globals.getShowDescrAtRuntime() && nvl(reportDescr).length() > 0) { - csvOut.println("\"" + reportDescr + "\""); - csvOut.println(); - } - } // if - if (Globals.getPrintParamsInDownload() && reportParamNameValues != null) { - csvOut.println(); - for (Iterator iter = reportParamNameValues.iterator(); iter.hasNext();) { - IdNameValue value = (IdNameValue) iter.next(); - csvOut.println("\"" + value.getId() + " = " + value.getName() + "\""); - } // for - csvOut.println(); - } // if -*/ - if(!raw) { - try(ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(AppUtils.getTempFolderPath()+""+zipFName)); - FileInputStream fis = new FileInputStream(fileName)) { - - //final int BUFFER = 2048; - - //fis.read(buf,0,buf.length); - int size = 0; - byte[] buffer = new byte[1024]; - - //CRC32 crc = new CRC32(); - //PrintStream fos = new PrintStream(new WriterOutputStream(out)); - //BufferedOutputStream dest = new BufferedOutputStream(fos, BUFFER); - //ZipOutputStream s = new ZipOutputStream(dest); - - //s.setLevel(6); - - ZipEntry entry = new ZipEntry(csvFName); - //crc.reset(); - zos.putNextEntry(entry); - - // read data to the end of the source file and write it to the zip - // output stream. - while ((size = fis.read(buffer, 0, buffer.length)) > 0) { - zos.write(buffer, 0, size); - } - - zos.closeEntry(); - fis.close(); + try (final ServletOutputStream sos = response.getOutputStream(); + final FileInputStream fileIn = new FileInputStream(file); + final BufferedInputStream buf = new BufferedInputStream(fileIn);) { - // Finish zip process - zos.close(); + final byte[] bOut = new byte[4096]; + //read from the file; write to the ServletOutputStream + int readBytes = 0; + while ((readBytes = buf.read(bOut, 0, 4096)) > 0) { + buf.available(); + sos.write(bOut, 0, readBytes); + } - } catch(Exception e) { - e.printStackTrace(); - } + } catch (final IOException ex) { + logger.warn("Failed to open/read/write file", ex); + } catch (final Exception e) { + logger.warn("Failed to open/read/write file", e); } - response.reset(); - java.io.File file = null; - - if(!raw) { - response.setContentType("application/octet-stream"); - response.setHeader("Content-disposition","attachment;filename="+fName+".zip"); - file = new java.io.File(AppUtils.getTempFolderPath()+""+fName+".zip"); - } else { - response.setContentType("application/csv"); - response.setHeader("Content-disposition","attachment;filename="+fName+".csv"); - file = new java.io.File(AppUtils.getTempFolderPath()+""+fName+".csv"); - } - - - int c; - try(ServletOutputStream sos = response.getOutputStream(); - FileInputStream fileIn = new FileInputStream(file); - BufferedInputStream buf = new BufferedInputStream(fileIn);) { - - byte [] bOut = new byte [4096]; - //read from the file; write to the ServletOutputStream - //while ((readBytes = buf.read()) != -1) - int readBytes = 0; - while ((readBytes = buf.read (bOut, 0, 4096))> 0) { - buf.available(); - sos.write (bOut, 0, readBytes); - } + final File f = new File(AppUtils.getTempFolderPath() + fName); + if (f.exists()) { + f.delete(); + } - } catch (IOException ex) { - ex.printStackTrace(); - } catch (Exception e) { - e.printStackTrace(); - } - - File f = new File (AppUtils.getTempFolderPath() - + fName); - if(f.exists()) f.delete(); - System.out.println("##### Heap utilization statistics [MB] #####"); - System.out.println("Used Memory:" - + (runtime.maxMemory() - runtime.freeMemory()) / mb); - logger.debug(EELFLoggerDelegate.debugLogger, ("Free Memory:" - + runtime.freeMemory() / mb)); + System.out.println("##### Heap utilization statistics [MB] #####"); + System.out.println("Used Memory:" + (runtime.maxMemory() - runtime.freeMemory()) / mb); + logger.debug(EELFLoggerDelegate.debugLogger, ("Free Memory:" + runtime.freeMemory() / mb)); logger.debug(EELFLoggerDelegate.debugLogger, ("Total Memory:" + runtime.totalMemory() / mb)); logger.debug(EELFLoggerDelegate.debugLogger, ("Max Memory:" + runtime.maxMemory() / mb)); - - } // createCSVFileContent -/* public String saveCSVPageFile(HttpServletRequest request, ReportData rd, - ArrayList reportParamNameValues, String reportTitle, String reportDescr) { - try { - String formattedReportName = new HtmlStripper().stripSpecialCharacters(reportTitle); - String formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date()); - - String csvFName = formattedReportName+formattedDate+AppUtils.getUserID(request)+".csv"; - //String csvFName = AppUtils.generateFileName(request, AppConstants.FT_CSV); - - BufferedWriter csvOut = new BufferedWriter(new FileWriter(AppUtils - .getTempFolderPath() - + csvFName)); - createCSVFileContent(csvOut, rd, reportParamNameValues, reportTitle, reportDescr); - csvOut.close(); - - return csvFName; - } catch (Exception e) { - (new ErrorHandler()).processError(request, "Exception saving data to CSV file: " - + e.getMessage()); - return null; - } - } // saveCSVPageFile -*/ - -// public String saveAsFlatFile(HttpServletRequest request, ReportData rd, -// ReportRuntime rr, String reportTitle, String reportDescr) { -// try { -// String csvFName = AppUtils.generateFileName(request, AppConstants.FT_TXT); -// -// BufferedWriter txtOut = new BufferedWriter(new FileWriter(AppUtils -// .getTempFolderPath() -// + csvFName)); -// createFlatFileContent(txtOut, rd, rr, reportTitle, reportDescr); -// txtOut.close(); -// -// return csvFName; -// } catch (Exception e) { -// (new ErrorHandler()).processError(request, "Exception saving data to CSV file: " -// + e.getMessage()); -// return null; -// } -// } // saveCSVPageFile + } // createCSVFileContent public String saveXMLFile(HttpServletRequest request, String reportName, String reportXML) { try { @@ -4384,7 +4201,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } return columnCount; } - private Map loadXSSFStyles(ReportRuntime rr, XSSFWorkbook wb, Map loadedStyles) { XSSFCellStyle styleDefault = wb.createCellStyle(); @@ -6563,37 +6379,3 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } } // ReportHandler - - -/** - * Adapter for a Writer to behave like an OutputStream. - * - * Bytes are converted to chars using the platform default encoding. - * If this encoding is not a single-byte encoding, some data may be lost. - */ - class WriterOutputStream extends OutputStream { - - private final Writer writer; - - public WriterOutputStream(Writer writer) { - this.writer = writer; - } - - public void write(int b) throws IOException { - // It's tempting to use writer.write((char) b), but that may get the encoding wrong - // This is inefficient, but it works - write(new byte[] {(byte) b}, 0, 1); - } - - public void write(byte b[], int off, int len) throws IOException { - writer.write(new String(b, off, len)); - } - - public void flush() throws IOException { - writer.flush(); - } - - public void close() throws IOException { - writer.close(); - } -} diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/pdf/PdfReportHandler.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/pdf/PdfReportHandler.java index 612ed960..3a50a99c 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/pdf/PdfReportHandler.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/pdf/PdfReportHandler.java @@ -55,6 +55,24 @@ */ package org.onap.portalsdk.analytics.model.pdf; +import com.lowagie.text.BadElementException; +import com.lowagie.text.Chunk; +import com.lowagie.text.Document; +import com.lowagie.text.DocumentException; +import com.lowagie.text.Element; +import com.lowagie.text.ElementTags; +import com.lowagie.text.Font; +import com.lowagie.text.FontFactory; +import com.lowagie.text.Image; +import com.lowagie.text.PageSize; +import com.lowagie.text.Paragraph; +import com.lowagie.text.Phrase; +import com.lowagie.text.Rectangle; +import com.lowagie.text.html.simpleparser.HTMLWorker; +import com.lowagie.text.html.simpleparser.StyleSheet; +import com.lowagie.text.pdf.PdfPCell; +import com.lowagie.text.pdf.PdfPTable; +import com.lowagie.text.pdf.PdfWriter; import java.awt.Color; import java.io.File; import java.io.FileNotFoundException; @@ -80,11 +98,9 @@ import java.util.Set; import java.util.TimeZone; import java.util.TreeMap; import java.util.Vector; - import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; - import org.onap.portalsdk.analytics.error.RaptorException; import org.onap.portalsdk.analytics.error.ReportSQLException; import org.onap.portalsdk.analytics.model.ReportHandler; @@ -110,25 +126,6 @@ import org.onap.portalsdk.analytics.view.RowHeaderCol; import org.onap.portalsdk.analytics.xmlobj.DataColumnType; import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate; -import com.lowagie.text.BadElementException; -import com.lowagie.text.Chunk; -import com.lowagie.text.Document; -import com.lowagie.text.DocumentException; -import com.lowagie.text.Element; -import com.lowagie.text.ElementTags; -import com.lowagie.text.Font; -import com.lowagie.text.FontFactory; -import com.lowagie.text.Image; -import com.lowagie.text.PageSize; -import com.lowagie.text.Paragraph; -import com.lowagie.text.Phrase; -import com.lowagie.text.Rectangle; -import com.lowagie.text.html.simpleparser.HTMLWorker; -import com.lowagie.text.html.simpleparser.StyleSheet; -import com.lowagie.text.pdf.PdfPCell; -import com.lowagie.text.pdf.PdfPTable; -import com.lowagie.text.pdf.PdfWriter; - /** * @author mwliu and sundar * @@ -942,448 +939,297 @@ public class PdfReportHandler extends org.onap.portalsdk.analytics.RaptorObject{ */ private final int DEFAULT_PDF_DISPLAY_WIDTH = 10; - private int paintPdfData(HttpServletRequest request, Document document, ReportData rd, ReportRuntime rr, String sql_whole) throws DocumentException, RaptorException, IOException { - - int mb = 1024*1024; - Runtime runtime = Runtime.getRuntime(); - int returnValue = 0; - //sql_whole = rr.getWholeSQL(); - //if(rd.getDataRowCount() >= rr.getReportDataSize()) { - //sql_whole=""; - //} - float f[] = getRelativeWidths(rd, rr.getReportType().equals(AppConstants.RT_CROSSTAB)); - PdfPTable table = new PdfPTable(f); - table.setWidthPercentage(100f); - table.getDefaultCell().setHorizontalAlignment(Rectangle.ALIGN_CENTER); - table.getDefaultCell().setVerticalAlignment(Rectangle.ALIGN_BOTTOM); - - ReportDefinition rdef = (new ReportHandler()).loadReportDefinition(request, rr.getReportID()); - - List allColumns = rdef.getAllColumns(); - - float[] repotWidths = new float[rdef.getVisibleColumnCount()]; - int columnIdx = 0; - float pdfDisplayWidth = 0; - for(Iterator iter = allColumns.iterator(); iter.hasNext();){ - DataColumnType dct = (DataColumnType) iter.next(); - if(dct.isVisible()) { - - if(dct.getPdfDisplayWidthInPxls() == null || dct.getPdfDisplayWidthInPxls().equals("") || dct.getPdfDisplayWidthInPxls().startsWith("null")) - pdfDisplayWidth = DEFAULT_PDF_DISPLAY_WIDTH; - else - pdfDisplayWidth = Float.parseFloat(dct.getPdfDisplayWidthInPxls()); - - repotWidths [columnIdx++] = pdfDisplayWidth; - } - } - - table.setWidths(repotWidths); - - //table.setH - - //TODO: check title and subtitle - HttpSession session = request.getSession(); - String drilldown_index = (String) session.getAttribute("drilldown_index"); - int index = 0; - try { - index = Integer.parseInt(drilldown_index); - } catch (NumberFormatException ex) { - index = 0; - } - String titleRep = (String) session.getAttribute("TITLE_"+index); - String subtitle = (String) session.getAttribute("SUBTITLE_"+index); - - if(nvl(titleRep).length()>0 && nvl(subtitle).length()>0) - table.setHeaderRows(3); - else if (nvl(titleRep).length()>0) - table.setHeaderRows(2); - else - table.setHeaderRows(1); - table = paintPdfReportHeader(request, document, table, rr, f); - paintPdfTableHeader(document, rd, table); - - int idx = 0; - int fragmentsize = 30; //for memory management - - ResultSet rs = null; - Connection conn = null; - Statement st = null; - ResultSetMetaData rsmd = null; + + private int paintPdfData(final HttpServletRequest request, final Document document, final ReportData rd, + final ReportRuntime rr, final String sql_whole) throws DocumentException, RaptorException, IOException { + + final int mb = 1024 * 1024; + final Runtime runtime = Runtime.getRuntime(); + int returnValue = 0; + + final float f[] = getRelativeWidths(rd, AppConstants.RT_CROSSTAB.equals(rr.getReportType())); + PdfPTable table = new PdfPTable(f); + table.setWidthPercentage(100f); + table.getDefaultCell().setHorizontalAlignment(Rectangle.ALIGN_CENTER); + table.getDefaultCell().setVerticalAlignment(Rectangle.ALIGN_BOTTOM); + + final ReportDefinition rdef = (new ReportHandler()).loadReportDefinition(request, rr.getReportID()); + + final List allColumns = rdef.getAllColumns(); + + final float[] repotWidths = new float[rdef.getVisibleColumnCount()]; + int columnIdx = 0; + float pdfDisplayWidth = 0; + for (final Iterator iter = allColumns.iterator(); iter.hasNext(); ) { + final DataColumnType dct = (DataColumnType) iter.next(); + if (dct.isVisible()) { + + if (dct.getPdfDisplayWidthInPxls() == null || dct.getPdfDisplayWidthInPxls().isEmpty() || dct + .getPdfDisplayWidthInPxls().startsWith("null")) { + pdfDisplayWidth = DEFAULT_PDF_DISPLAY_WIDTH; + } else { + pdfDisplayWidth = Float.parseFloat(dct.getPdfDisplayWidthInPxls()); + } + + repotWidths[columnIdx++] = pdfDisplayWidth; + } + } + + table.setWidths(repotWidths); + + //TODO: check title and subtitle + final HttpSession session = request.getSession(); + final String drilldown_index = (String) session.getAttribute("drilldown_index"); + int index = 0; + try { + index = Integer.parseInt(drilldown_index); + } catch (NumberFormatException ex) { + index = 0; + } + final String titleRep = (String) session.getAttribute("TITLE_" + index); + final String subtitle = (String) session.getAttribute("SUBTITLE_" + index); + + if (nvl(titleRep).length() > 0 && nvl(subtitle).length() > 0) { + table.setHeaderRows(3); + } else if (nvl(titleRep).length() > 0) { + table.setHeaderRows(2); + } else { + table.setHeaderRows(1); + } + table = paintPdfReportHeader(request, document, table, rr, f); + paintPdfTableHeader(document, rd, table); + + int idx = 0; + final int fragmentsize = 30; //for memory management + rd.reportDataRows.resetNext(); DataRow dr = rd.reportDataRows.getNext(); - - //addRowHeader(table,dr,idx,rd); - - //addRowColumns(table,dr,idx); - if(nvl(sql_whole).length() >0 && rr.getReportType().equals(AppConstants.RT_LINEAR)) { - try { - conn = ConnectionUtils.getConnection(rr.getDbInfo()); - st = conn.createStatement(); - logger.debug(EELFLoggerDelegate.debugLogger, ("************* Map Whole SQL *************")); - logger.debug(EELFLoggerDelegate.debugLogger, (sql_whole)); - logger.debug(EELFLoggerDelegate.debugLogger, ("*****************************************")); - rs = st.executeQuery(sql_whole); - rsmd = rs.getMetaData(); - int numberOfColumns = rsmd.getColumnCount(); - HashMap colHash = new HashMap(); - dr = null; - int j = 0; - int rowCount = 0; - String title = ""; - while(rs.next()) { - -/* if(runtime.freeMemory()/mb <= ((runtime.maxMemory()/mb)*Globals.getMemoryThreshold()/100) ) { - returnValue = 1; - String cellValue = Globals.getUserDefinedMessageForMemoryLimitReached() + " "+ rowCount +" records out of " + rr.getReportDataSize() + " were downloaded to PDF."; - Font cellFont = FontFactory.getFont(Globals.getDataFontFamily(), - Globals.getDataFontSize(), - Font.NORMAL, Color.BLACK); - PdfPCell cell = new PdfPCell(new Paragraph(cellValue,cellFont)); - table.addCell(cell); - document.add(table); - return returnValue; - } -*/ rowCount++; - colHash = new HashMap(); - for (int i = 1; i <= numberOfColumns; i++) { - colHash.put(rsmd.getColumnLabel(i).toUpperCase(), rs.getString(i)); - } - rd.reportDataRows.resetNext(); - - dr = rd.reportDataRows.getNext(); - - j = 0; - /*if(rd.reportTotalRowHeaderCols!=null) { - - HtmlFormatter rfmt = dr.getRowFormatter(); - - Font cellFont = FontFactory.getFont(Globals.getDataFontFamily(), - Globals.getDataFontSize(), - Font.NORMAL, Color.BLACK); - if(rfmt != null) { - cellFormatterFont(rfmt,cellFont); - } - - String cellValue = new Integer(rowCount).toString(); - PdfPCell cell = new PdfPCell(new Paragraph(cellValue,cellFont)); - - //row background color can be overwritten by cell background color - cell.setBackgroundColor(getRowBackgroundColor(dr, idx)); - - cell.setHorizontalAlignment(Rectangle.ALIGN_CENTER); - - if(rfmt != null) { - formatterCell(rfmt,cell); - } - table.addCell(cell); - }*/ - - for (dr.resetNext(); dr.hasNext();j++) { - DataValue dv = dr.getNext(); - /*if(j == 0) { - HtmlFormatter cfmt = dv.getCellFormatter(); - HtmlFormatter rfmt = dv.getRowFormatter(); - - Font cellFont = FontFactory.getFont(Globals.getDataFontFamily(), - Globals.getDataFontSize(), - Font.NORMAL, Color.BLACK); - if(cfmt!= null) { - cellFormatterFont(cfmt,cellFont); - } - else if(rfmt != null) { - cellFormatterFont(rfmt,cellFont); - } - else { - if(dv.isBold()) { - cellFont.setStyle(Font.BOLD); - } - } - - //String cellValue = strip.stripHtml(value.trim()); - PdfPCell cell = new PdfPCell(new Paragraph(rowCount+"",cellFont)); - - //row background color can be overwritten by cell background color - cell.setBackgroundColor(getRowBackgroundColor(dr, idx)); - - if(nvl(dv.getAlignment()).trim().length()>0) - cell.setHorizontalAlignment(ElementTags.alignmentValue(dv.getAlignment())); - else - cell.setHorizontalAlignment(Rectangle.ALIGN_CENTER); - - if(cfmt!= null) { - formatterCell(cfmt,cell); - } - else if(rfmt != null) { - formatterCell(rfmt,cell); - } - table.addCell(cell); - }*/ - - //for (chr.resetNext(); chr.hasNext();) { - //ColumnHeader ch = chr.getNext(); - String value = nvl((String)colHash.get(dv.getColId().toUpperCase())); - if(dv.isVisible()) { - - HtmlFormatter cfmt = dv.getCellFormatter(); - HtmlFormatter rfmt = dv.getRowFormatter(); - - Font cellFont = FontFactory.getFont(FONT_FAMILY, - FONT_SIZE, - Font.NORMAL, Color.BLACK); - if(cfmt!= null) { - cellFormatterFont(cfmt,cellFont); - } - else if(rfmt != null) { - cellFormatterFont(rfmt,cellFont); - } - else { - if(dv.isBold()) { - cellFont.setStyle(Font.BOLD); - } - } - - String cellValue = strip.stripHtml(value.trim()); - PdfPCell cell = new PdfPCell(new Paragraph(cellValue,cellFont)); - - //row background color can be overwritten by cell background color - cell.setBackgroundColor(getRowBackgroundColor(dr, idx)); - - if(nvl(dv.getAlignment()).trim().length()>0) - cell.setHorizontalAlignment(ElementTags.alignmentValue(dv.getAlignment())); - else - cell.setHorizontalAlignment(Rectangle.ALIGN_CENTER); - - if(cfmt!= null) { - formatterCell(cfmt,cell); - } - else if(rfmt != null) { - formatterCell(rfmt,cell); - } - - - - table.addCell(cell); - - }//if isVisible() - - - } - - } - if(rd.reportDataTotalRow!=null) { - for (rd.reportDataTotalRow.resetNext(); rd.reportDataTotalRow.hasNext();idx++) { - dr = rd.reportDataTotalRow.getNext(); - table.getDefaultCell().setHorizontalAlignment(Rectangle.ALIGN_CENTER); - Font rowHeaderFont = FontFactory.getFont(FONT_FAMILY, - FONT_SIZE, - Font.NORMAL, Color.BLACK); - rowHeaderFont.setStyle(Font.BOLD); - rowHeaderFont.setSize(FONT_SIZE+1f); - table.getDefaultCell().setBackgroundColor(getRowBackgroundColor(dr, idx)); - table.addCell(new Paragraph("Total",rowHeaderFont)); - - - addTotalRowColumns(table,dr,idx); - if (idx % fragmentsize == fragmentsize - 1) { - document.add(table); - table.deleteBodyRows(); - table.setSkipFirstHeader(true); - } - - } - } - } catch (SQLException ex) { - throw new RaptorException(ex); - } catch (ReportSQLException ex) { - throw new RaptorException(ex); - } catch (Exception ex) { - if(!(ex.getCause() instanceof java.net.SocketException) ) - throw new RaptorException (ex); - } finally { - try { - if(conn!=null) - conn.close(); - if(st!=null) - st.close(); - if(rs!=null) - rs.close(); - } catch (SQLException ex) { - throw new RaptorException(ex); - } - } - - -// if (idx % fragmentsize == fragmentsize - 1) { -// document.add(table); -// table.deleteBodyRows(); -// table.setSkipFirstHeader(true); -// } - - //document.add(table); - } else { - if(rr.getReportType().equals(AppConstants.RT_LINEAR)) { - int rowCount = 0; - for(rd.reportDataRows.resetNext();rd.reportDataRows.hasNext();idx++) - { - rowCount++; - - /*if(rd.reportTotalRowHeaderCols!=null) { - HtmlFormatter rfmt = dr.getRowFormatter(); - - Font cellFont = FontFactory.getFont(Globals.getDataFontFamily(), - Globals.getDataFontSize(), - Font.NORMAL, Color.BLACK); - if(rfmt != null) { - cellFormatterFont(rfmt,cellFont); - } - - //String cellValue = new Integer(rowCount).toString(); - //PdfPCell cell = new PdfPCell(new Paragraph(cellValue,cellFont)); - - //row background color can be overwritten by cell background color - //cell.setBackgroundColor(getRowBackgroundColor(dr, idx)); - - //cell.setHorizontalAlignment(Rectangle.ALIGN_CENTER); - - //if(rfmt != null) { - //formatterCell(rfmt,cell); - //} - //table.addCell(cell); - }*/ - - - - if(runtime.freeMemory()/mb <= ((runtime.maxMemory()/mb)*Globals.getMemoryThreshold()/100) ) { - returnValue = 1; - } - - dr = rd.reportDataRows.getNext(); - - addRowHeader(table,dr,idx,rd); - - addRowColumns(table,dr,idx); - - if (idx % fragmentsize == fragmentsize - 1) { - document.add(table); - table.deleteBodyRows(); - table.setSkipFirstHeader(true); - } - } - - if(rd.reportDataTotalRow!=null) { - for (rd.reportDataTotalRow.resetNext(); rd.reportDataTotalRow.hasNext();idx++) { - dr = rd.reportDataTotalRow.getNext(); - table.getDefaultCell().setHorizontalAlignment(Rectangle.ALIGN_CENTER); - Font rowHeaderFont = FontFactory.getFont(FONT_FAMILY, - FONT_SIZE, - Font.NORMAL, Color.BLACK); - rowHeaderFont.setStyle(Font.BOLD); - rowHeaderFont.setSize(FONT_SIZE+1f); - table.getDefaultCell().setBackgroundColor(getRowBackgroundColor(dr, idx)); - table.addCell(new Paragraph("Total",rowHeaderFont)); - - addTotalRowColumns(table,dr,idx); - if (idx % fragmentsize == fragmentsize - 1) { - document.add(table); - table.deleteBodyRows(); - table.setSkipFirstHeader(true); - } - - } - } - - } else if (rr.getReportType().equals(AppConstants.RT_CROSSTAB)) { - int rowCount = 0; - List l = rd.getReportDataList(); - boolean first = true; - for (int dataRow = 0; dataRow < l.size(); dataRow++) { - first = true; - rowCount++; - dr = (DataRow) l.get(dataRow); - Vector rowNames = dr.getRowValues(); - for(dr.resetNext(); dr.hasNext(); ) { - - if(first) { - HtmlFormatter rfmt = dr.getRowFormatter(); - - Font cellFont = FontFactory.getFont(FONT_FAMILY, - FONT_SIZE, - Font.NORMAL, Color.BLACK); - if(rfmt != null) { - cellFormatterFont(rfmt,cellFont); - } - String cellValue = ""; - PdfPCell cell = null; - //String cellValue = new Integer(rowCount).toString(); - //PdfPCell cell = new PdfPCell(new Paragraph(cellValue,cellFont)); - //row background color can be overwritten by cell background color - //cell.setBackgroundColor(getRowBackgroundColor(dr, idx)); - - //cell.setHorizontalAlignment(Rectangle.ALIGN_CENTER); - - //if(rfmt != null) { - //formatterCell(rfmt,cell); - // } - //table.addCell(cell); - if(rowNames!=null) { - for(int i=0; i 0 && AppConstants.RT_LINEAR.equals(rr.getReportType())) { + try (final Connection conn = ConnectionUtils.getConnection(rr.getDbInfo()); + final Statement st = conn.createStatement(); + final ResultSet rs = st.executeQuery(sql_whole);) { + + logger.debug(EELFLoggerDelegate.debugLogger, ("************* Map Whole SQL *************")); + logger.debug(EELFLoggerDelegate.debugLogger, (sql_whole)); + logger.debug(EELFLoggerDelegate.debugLogger, ("*****************************************")); + + final ResultSetMetaData rsmd = rs.getMetaData(); + final int numberOfColumns = rsmd.getColumnCount(); + dr = null; + int rowCount = 0; + while (rs.next()) { + + rowCount++; + final Map colHash = new HashMap(); + for (int i = 1; i <= numberOfColumns; i++) { + colHash.put(rsmd.getColumnLabel(i).toUpperCase(), rs.getString(i)); + } + rd.reportDataRows.resetNext(); + + dr = rd.reportDataRows.getNext(); + + for (dr.resetNext(); dr.hasNext(); ) { + final DataValue dv = dr.getNext(); + + final String value = nvl((String) colHash.get(dv.getColId().toUpperCase())); + if (dv.isVisible()) { + + final HtmlFormatter cfmt = dv.getCellFormatter(); + final HtmlFormatter rfmt = dv.getRowFormatter(); + + final Font cellFont = FontFactory.getFont(FONT_FAMILY, + FONT_SIZE, + Font.NORMAL, Color.BLACK); + if (cfmt != null) { + cellFormatterFont(cfmt, cellFont); + } else if (rfmt != null) { + cellFormatterFont(rfmt, cellFont); + } else { + if (dv.isBold()) { + cellFont.setStyle(Font.BOLD); + } + } + + final String cellValue = strip.stripHtml(value.trim()); + final PdfPCell cell = new PdfPCell(new Paragraph(cellValue, cellFont)); + + //row background color can be overwritten by cell background color + cell.setBackgroundColor(getRowBackgroundColor(dr, idx)); + + if (nvl(dv.getAlignment()).trim().length() > 0) { + cell.setHorizontalAlignment(ElementTags.alignmentValue(dv.getAlignment())); + } else { + cell.setHorizontalAlignment(Rectangle.ALIGN_CENTER); + } + + if (cfmt != null) { + formatterCell(cfmt, cell); + } else if (rfmt != null) { + formatterCell(rfmt, cell); + } + + table.addCell(cell); + + }//if isVisible() + + } + + } + if (rd.reportDataTotalRow != null) { + for (rd.reportDataTotalRow.resetNext(); rd.reportDataTotalRow.hasNext(); idx++) { + dr = rd.reportDataTotalRow.getNext(); + table.getDefaultCell().setHorizontalAlignment(Rectangle.ALIGN_CENTER); + final Font rowHeaderFont = FontFactory.getFont(FONT_FAMILY, + FONT_SIZE, + Font.NORMAL, Color.BLACK); + rowHeaderFont.setStyle(Font.BOLD); + rowHeaderFont.setSize(FONT_SIZE + 1f); + table.getDefaultCell().setBackgroundColor(getRowBackgroundColor(dr, idx)); + table.addCell(new Paragraph("Total", rowHeaderFont)); + + addTotalRowColumns(table, dr, idx); + if (idx % fragmentsize == fragmentsize - 1) { + document.add(table); + table.deleteBodyRows(); + table.setSkipFirstHeader(true); + } + + } + } + } catch (final SQLException | ReportSQLException ex) { + throw new RaptorException(ex); + } catch (final Exception ex) { + if (!(ex.getCause() instanceof java.net.SocketException)) { + throw new RaptorException(ex); + } + } + //document.add(table); + } else { + if (rr.getReportType().equals(AppConstants.RT_LINEAR)) { + for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext(); idx++) { + + if (runtime.freeMemory() / mb <= ((runtime.maxMemory() / mb) * Globals.getMemoryThreshold() + / 100)) { + returnValue = 1; + } + + dr = rd.reportDataRows.getNext(); + + addRowHeader(table, dr, idx, rd); + + addRowColumns(table, dr, idx); + + if (idx % fragmentsize == fragmentsize - 1) { + document.add(table); + table.deleteBodyRows(); + table.setSkipFirstHeader(true); + } + } + + if (rd.reportDataTotalRow != null) { + for (rd.reportDataTotalRow.resetNext(); rd.reportDataTotalRow.hasNext(); idx++) { + dr = rd.reportDataTotalRow.getNext(); + table.getDefaultCell().setHorizontalAlignment(Rectangle.ALIGN_CENTER); + Font rowHeaderFont = FontFactory.getFont(FONT_FAMILY, + FONT_SIZE, + Font.NORMAL, Color.BLACK); + rowHeaderFont.setStyle(Font.BOLD); + rowHeaderFont.setSize(FONT_SIZE + 1f); + table.getDefaultCell().setBackgroundColor(getRowBackgroundColor(dr, idx)); + table.addCell(new Paragraph("Total", rowHeaderFont)); + + addTotalRowColumns(table, dr, idx); + if (idx % fragmentsize == fragmentsize - 1) { + document.add(table); + table.deleteBodyRows(); + table.setSkipFirstHeader(true); + } + + } + } + + } else if (AppConstants.RT_CROSSTAB.equals(rr.getReportType())) { + int rowCount = 0; + final List l = rd.getReportDataList(); + boolean first = true; + for (int dataRow = 0; dataRow < l.size(); dataRow++) { + first = true; + rowCount++; + dr = (DataRow) l.get(dataRow); + final Vector rowNames = dr.getRowValues(); + for (dr.resetNext(); dr.hasNext(); ) { + + if (first) { + HtmlFormatter rfmt = dr.getRowFormatter(); + + Font cellFont = FontFactory.getFont(FONT_FAMILY, + FONT_SIZE, + Font.NORMAL, Color.BLACK); + if (rfmt != null) { + cellFormatterFont(rfmt, cellFont); + } + + if (rowNames != null) { + for (int i = 0; i < rowNames.size(); i++) { + final DataValue dv = rowNames.get(i); + rfmt = dr.getRowFormatter(); + + cellFont = FontFactory.getFont(FONT_FAMILY, + FONT_SIZE, + Font.NORMAL, Color.BLACK); + if (rfmt != null) { + cellFormatterFont(rfmt, cellFont); + } + String cellValue = dv.getDisplayValue(); + if (cellValue.indexOf("|#") != -1) { + cellValue = cellValue.substring(0, cellValue.indexOf("|")); + } + + final PdfPCell cell = new PdfPCell(new Paragraph(cellValue, cellFont)); + //row background color can be overwritten by cell background color + cell.setBackgroundColor(getRowBackgroundColor(dr, idx)); + + cell.setHorizontalAlignment(Rectangle.ALIGN_CENTER); + + if (rfmt != null) { + formatterCell(rfmt, cell); + } + table.addCell(cell); + } + } + } + first = false; + + if (runtime.freeMemory() / mb <= ((runtime.maxMemory() / mb) * Globals.getMemoryThreshold() + / 100)) { + returnValue = 1; + } + + //addRowHeader(table,dr,idx,rd); + + addRowColumns(table, dr, idx); + + if (idx % fragmentsize == fragmentsize - 1) { + document.add(table); + table.deleteBodyRows(); + table.setSkipFirstHeader(true); + } + } + + } + } + + //document.add(table); + + } + + document.add(table); + paintPdfReportFooter(request, document, rr, f); + + return returnValue; + } + private void addRowHeader(PdfPTable table, DataRow dr, int idx, ReportData rd) { table.getDefaultCell().setHorizontalAlignment(Rectangle.ALIGN_CENTER); diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SchedulerUtil.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SchedulerUtil.java index 24af7e25..313f04ee 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SchedulerUtil.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SchedulerUtil.java @@ -33,11 +33,10 @@ * * ============LICENSE_END============================================ * - * + * */ package org.onap.portalsdk.analytics.scheduler; -import java.io.IOException; import java.io.InputStream; import java.math.BigDecimal; import java.sql.Connection; @@ -51,338 +50,208 @@ import java.util.Calendar; import java.util.Date; import java.util.List; import java.util.Locale; - import org.onap.portalsdk.analytics.error.ReportSQLException; import org.onap.portalsdk.analytics.system.DbUtils; +import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate; -//import oracle.jdbc.*; -//import oracle.sql.BLOB; +public class SchedulerUtil { + private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(SchedulerUtil.class); -public class SchedulerUtil { - - private Connection conn = null; - - protected Connection getConnection() { - return conn; - } - - protected void setConnection(Connection _conn) { - conn = _conn; - } - - protected Connection init() throws SQLException, ReportSQLException{ - if(conn != null) - return conn; - conn = DbUtils.getConnection(); - return conn; - } - - protected void closeConnection() throws SQLException { - if(conn != null) conn.close(); - } - - public void insertOrUpdate(String sql) throws SQLException, ReportSQLException { - - Statement stat = null; - try{ - //conn = getConnection(); - stat = conn.createStatement(); - stat.executeUpdate(sql); - - } finally{ - if(stat!=null) - stat.close(); - //conn.close(); - } - } - - public void updateBinaryStream(String sql, BigDecimal id, InputStream is, int size) throws SQLException, ReportSQLException, IOException { - - // cludge hack for oracle databases - if(conn.getMetaData().getDatabaseProductName().toLowerCase().contains("oracle")) { -/* updateBlob(sql,id,is,size); -*/ throw new ReportSQLException("only maria db support for this "); - - } - - PreparedStatement stat = null; - try { - stat = conn.prepareStatement(sql); - stat.setBigDecimal(2, id); - stat.setBinaryStream(1, is, size); - stat.executeUpdate(); - - } finally{ - if(stat!=null) - stat.close(); - } - + private Connection conn = null; + + public static Date trunc_hour(final Date v_date) { + + Calendar calendar = Calendar.getInstance(); + calendar.setTime(v_date); + calendar.set(Calendar.MILLISECOND, 0); + calendar.set(Calendar.SECOND, 0); + calendar.set(Calendar.MINUTE, 0); + return calendar.getTime(); + } + + public static Date add_hours(final Date v_date, final int i) { + + Calendar cal = Calendar.getInstance(); + cal.setTime(v_date); + cal.add(Calendar.HOUR, i); + return cal.getTime(); + } + + public static Date add_months(final Date v_date, final int i) { + + Calendar cal = Calendar.getInstance(); + cal.setTime(v_date); + cal.add(Calendar.MONTH, i); + return cal.getTime(); + } + + public static Date add_days(final Date v_date, final int i) { + + Calendar cal = Calendar.getInstance(); + cal.setTime(v_date); + cal.add(Calendar.DATE, i); + return cal.getTime(); + } + + public static Date to_date(final String input, final String format) { + + Date date = null; + try { + date = new SimpleDateFormat(format, Locale.ENGLISH).parse(input); + } catch (final Exception e) { + logger.warn("Failed to Format Date", e); + } + return date; + } + + public static String to_date_str(final Date input, final String format) { + + String date = null; + try { + date = new SimpleDateFormat(format, Locale.ENGLISH).format(input); + } catch (final Exception e) { + logger.warn("Failed to Format Date", e); + } + return date; } - - /*public void updateBlob(String sql, BigDecimal id, InputStream is, int size) throws SQLException, ReportSQLException, IOException { - PreparedStatement stat = null; - OutputStream out = null; - BLOB blob = null; - try { - stat = conn.prepareStatement(sql); - blob = BLOB.createTemporary(conn,false, BLOB.DURATION_SESSION); - out = blob.getBinaryOutputStream(); - - int read; - while((read = is.read()) != -1) { - out.write(read); - } - out.flush(); - - stat.setBigDecimal(2, id); - stat.setBlob(1, blob); - stat.executeUpdate(); - - } - catch (SQLException sqL) { - sqL.printStackTrace(); - } - finally{ - out.close(); - stat.close(); - } - - }*/ - - - public void insertOrUpdateWithPrepared(String sql, List params, List types) throws SQLException, ReportSQLException { - - PreparedStatement stat = null; - try{ - //conn = getConnection(); - stat = conn.prepareStatement(sql); - conn.getMetaData(); - int i2; - int paramLength = params.size(); - for(int i = 0 ; i< paramLength ; i++) { - i2 = i+1; - Object param = params.get(i); - int type = types.get(i); - - if(param.equals("NULL")) { - stat.setNull(i2, type); - } - else if(type == Types.VARCHAR) { - stat.setString(i2, (String)param); - } - else if(type == Types.INTEGER) { - stat.setInt(i2, (Integer)param); - } - else if(type == Types.NUMERIC) { - stat.setLong(i2, (Long)param); - } - else if(type == Types.DOUBLE) { - stat.setDouble(i2, (Double)param); - } - else if(type == Types.DATE) { - stat.setDate(i2, (java.sql.Date)param); - } - else if(type == Types.TIMESTAMP) { - stat.setTimestamp(i2, (java.sql.Timestamp)param); - } - else if(type == Types.BIGINT) { - stat.setBigDecimal(i2, (BigDecimal)param); - } - else - throw new SQLException("Unidentified Object; Please contact admin and have this method updated with the current object type"); - - } - - stat.executeUpdate(); - - } finally{ - if(stat!=null) - stat.close(); - //conn.close(); - } - } - - - public Object getSingleResult(String sql, String fieldname) throws SQLException, ReportSQLException{ - - Statement stat = null; - ResultSet rs = null; - Object o=null; - try{ - //conn = getConnection(); - stat = conn.createStatement(); - rs = stat.executeQuery(sql); - - while (rs.next()) { - o = rs.getObject(fieldname); - } - } - catch(SQLException sqlE){ - sqlE.printStackTrace(); - } - - finally{ - if(rs!=null) - rs.close(); - if(stat!=null) - stat.close(); - //conn.close(); - } - return o; + + public static String[] cr_dissecturl(final String formfields, final String delimiter) { + if (formfields == null || formfields.isEmpty()) { + return new String[]{}; + } + return formfields.split("&"); + } + + protected Connection getConnection() { + return conn; + } + + protected void setConnection(final Connection _conn) { + conn = _conn; + } + + protected Connection init() throws ReportSQLException { + if (conn != null) { + return conn; + } + conn = DbUtils.getConnection(); + return conn; + } + + protected void closeConnection() throws SQLException { + if (conn != null) { + conn.close(); + } + } + + public void insertOrUpdate(final String sql) throws SQLException, ReportSQLException { + + try (final Statement stat = conn.createStatement()) { + stat.executeUpdate(sql); + } + } + + public void updateBinaryStream(final String sql, final BigDecimal id, final InputStream is, final int size) + throws SQLException, ReportSQLException { + + // cludge hack for oracle databases + if (conn.getMetaData().getDatabaseProductName().toLowerCase().contains("oracle")) { + throw new ReportSQLException("only maria db support for this "); + } + + try (final PreparedStatement stat = conn.prepareStatement(sql)) { + stat.setBigDecimal(2, id); + stat.setBinaryStream(1, is, size); + stat.executeUpdate(); + } + } + + public void insertOrUpdateWithPrepared(final String sql, final List params, final List types) + throws SQLException { + + try (final PreparedStatement stat = conn.prepareStatement(sql)) { + conn.getMetaData(); + int i2; + final int paramLength = params.size(); + for (int i = 0; i < paramLength; i++) { + i2 = i + 1; + final Object param = params.get(i); + final int type = types.get(i); + + if ("NULL".equals(param)) { + stat.setNull(i2, type); + } else if (type == Types.VARCHAR) { + stat.setString(i2, (String) param); + } else if (type == Types.INTEGER) { + stat.setInt(i2, (Integer) param); + } else if (type == Types.NUMERIC) { + stat.setLong(i2, (Long) param); + } else if (type == Types.DOUBLE) { + stat.setDouble(i2, (Double) param); + } else if (type == Types.DATE) { + stat.setDate(i2, (java.sql.Date) param); + } else if (type == Types.TIMESTAMP) { + stat.setTimestamp(i2, (java.sql.Timestamp) param); + } else if (type == Types.BIGINT) { + stat.setBigDecimal(i2, (BigDecimal) param); + } else { + throw new SQLException( + "Unidentified Object; Please contact admin and have this method updated with the current object type"); + } + + } + + stat.executeUpdate(); + } + } + + public Object getSingleResult(final String sql, final String fieldName) { + Object o = null; + try (final Statement stat = conn.createStatement(); + final ResultSet rs = stat.executeQuery(sql);) { + while (rs.next()) { + o = rs.getObject(fieldName); + } + } catch (final SQLException sqlE) { + logger.warn("Failed to get single result", sqlE); + } + return o; + } + + public InputStream getDBStream(final String sql, final String fieldname) throws SQLException, ReportSQLException { + + // cludge hack for oracle databases + if (conn.getMetaData().getDatabaseProductName().toLowerCase().contains("oracle")) { + throw new ReportSQLException("only maria db support for this "); + } + + InputStream o = null; + try (final Statement stat = conn.createStatement(); + final ResultSet rs = stat.executeQuery(sql);) { + + while (rs.next()) { + o = rs.getBinaryStream(fieldname); + } + } catch (SQLException sqlE) { + logger.warn("Failed to get DB Stream", sqlE); + } + return o; + } + + public void getAndExecute(final String sql, final Executor executor) throws SQLException { + try (final Statement stat = conn.createStatement(); + final ResultSet rs = stat.executeQuery(sql);) { + while (rs.next()) { + executor.execute(rs); + } + } catch (SQLException sqlE) { + logger.warn("Failed to get DB Stream", sqlE); + } + } + + interface Executor { + + public void execute(ResultSet rs) throws SQLException; } - - public InputStream getDBStream(String sql, String fieldname) throws SQLException, ReportSQLException, IOException{ - - // cludge hack for oracle databases - if(conn.getMetaData().getDatabaseProductName().toLowerCase().contains("oracle")) { - /*return getDBBlob(sql,fieldname);*/ - throw new ReportSQLException("only maria db support for this "); - - } - - - Statement stat = null; - ResultSet rs = null; - InputStream o=null; - try{ - //conn = getConnection(); - stat = conn.createStatement(); - rs = stat.executeQuery(sql); - - while (rs.next()) { - o = rs.getBinaryStream(fieldname); - } - } - catch(SQLException sqlE){ - sqlE.printStackTrace(); - } - - finally{ - if(rs!=null) - rs.close(); - if(stat!=null) - stat.close(); - //conn.close(); - } - return o; - } - - /*public InputStream getDBBlob(String sql, String fieldname) throws SQLException, ReportSQLException, IOException{ - - - Statement stat = null; - ResultSet rs = null; - BLOB blob=null; - ByteArrayInputStream in = null; - try{ - stat = conn.createStatement(); - rs = stat.executeQuery(sql); - - if (rs.next()) { - blob = ((OracleResultSet) rs).getBLOB(fieldname); - in = new ByteArrayInputStream(blob.getBytes(1,(int)blob.length())); - - } - } - catch(SQLException sqlE){ - sqlE.printStackTrace(); - } - - finally{ - if(rs!=null) - rs.close(); - if(stat!=null) - stat.close(); - //conn.close(); - } - return in; - } - */ - - public void getAndExecute(String sql, Executor executor) throws SQLException, ReportSQLException{ - //Connection conn = getConnection(); - Statement stat = conn.createStatement(); - ResultSet rs = stat.executeQuery(sql); - - - while (rs.next()) { - executor.execute(rs); - } - - if(rs!=null) - rs.close(); - if(stat!=null) - stat.close(); - //conn.close(); - } - - interface Executor{ - public void execute(ResultSet rs) throws SQLException; - } - - - - - - - public static Date trunc_hour(Date v_date) { - - Calendar calendar = Calendar.getInstance(); - calendar.setTime(v_date); - calendar.set(Calendar.MILLISECOND, 0); - calendar.set(Calendar.SECOND, 0); - calendar.set(Calendar.MINUTE, 0); - return calendar.getTime(); - } - - public static Date add_hours(Date v_date, int i) { - - Calendar cal = Calendar.getInstance(); - cal.setTime(v_date); - cal.add(Calendar.HOUR, i); - return cal.getTime(); - } - - public static Date add_months(Date v_date, int i) { - - Calendar cal = Calendar.getInstance(); - cal.setTime(v_date); - cal.add(Calendar.MONTH, i); - return cal.getTime(); - } - - public static Date add_days(Date v_date, int i) { - - Calendar cal = Calendar.getInstance(); - cal.setTime(v_date); - cal.add(Calendar.DATE, i); - return cal.getTime(); - } - - public static Date to_date(String input, String format) { - - Date date = null; - try { - date = new SimpleDateFormat(format, Locale.ENGLISH).parse(input); - } catch (Exception e) { - } - return date; - } - - public static String to_date_str(Date input, String format) { - - String date = null; - try { - date = new SimpleDateFormat(format, Locale.ENGLISH).format(input); - } catch (Exception e) { - } - return date; - } - - public static String[] cr_dissecturl(String formfields, String delimiter){ - if(formfields == null || formfields.isEmpty()) - return new String[]{}; - return formfields.split("&"); - } } diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java index 8b9aabc2..3c871d87 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java @@ -38,21 +38,20 @@ package org.onap.portalsdk.analytics.scheduler; import java.math.BigDecimal; -import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; -import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.Date; import java.util.UUID; - import org.onap.portalsdk.analytics.error.RaptorException; import org.onap.portalsdk.analytics.error.ReportSQLException; import org.onap.portalsdk.analytics.scheduler.SchedulerUtil.Executor; import org.onap.portalsdk.analytics.system.Globals; +import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate; public class SendNotifications { - + + private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(SendNotifications.class); SchedulerUtil schedulerUtil; public SendEmail sendEmail; @@ -63,7 +62,6 @@ public class SendNotifications { init(); } - public void deInit() throws SQLException { schedulerUtil.closeConnection(); } @@ -71,133 +69,136 @@ public class SendNotifications { public void init() throws SQLException, ReportSQLException { schedulerUtil.init(); } - - public void send_notification(String p_mail_server, String p_sender, String p_system_name, String p_system_url, int connectionTimeout) throws RaptorException, Exception { - - System.out.println(p_mail_server + " " + p_sender + " " + p_system_name + " " + p_system_url); - int p_time_interval = Globals.getSchedulerInterval(); - int v_num_recs = 0; - String v_gen_key; - BigDecimal v_id = null; - String v_url; - String v_r_action = "report.download.pdf"; - String v_email_msg; - String v_formfields; - // String error_m; - // int transfer_timeout_limit = 1800; - // boolean v_attach_email_yn = true; - int v_schedule_id; - Date v_touch_date; - // Exception for_rec; - - Connection conn = schedulerUtil.getConnection(); - Statement stat = conn.createStatement(); - - String CNotificationsql = - /* - "SELECT x.rep_id, x.schedule_id, x.conditional_yn, x.condition_large_sql, x.notify_type, x.max_row, x.initial_formfields, x.processed_formfields, r.title, x.user_id " - + "FROM (" - + "SELECT rs.rep_id, rs.schedule_id, rs.sched_user_id user_id, rs.conditional_yn, rs.condition_large_sql, " - + "rs.notify_type, rs.max_row, rs.initial_formfields, rs.processed_formfields " - + "FROM cr_report_schedule rs " - + "WHERE rs.enabled_yn='Y' " - + "AND rs.start_" - + "date <= sysdate " - + "AND (rs.end_date >= sysdate or rs.end_date is null ) " - + "AND rs.run_date IS NOT NULL " - + ") x, cr_report r " - + "WHERE x.rep_id = r.rep_id "; - */ - - Globals.getAvailableSchedules().replace("[currentDate]", Globals.getCurrentDateString()); - - - ResultSet rs = stat.executeQuery(CNotificationsql); - - while (rs.next()) { - - v_schedule_id = rs.getInt("schedule_id"); - int offset = get_report_sched_offset(rs.getInt("rep_id"), v_schedule_id); - - if(offset >= p_time_interval) continue; - - - v_touch_date = (Date) schedulerUtil.getSingleResult("select touch_date from cr_report_email_sent_log where schedule_id = " + v_schedule_id + " and log_id = (select max(log_id) from cr_report_email_sent_log where schedule_id = " + v_schedule_id + ")", "touch_date"); - if (v_touch_date != null) { - if (Math.abs(System.currentTimeMillis() - v_touch_date.getTime()) /1000 < (p_time_interval - 1)) { - return; - } - } - - if ("Y".equals(rs.getString("conditional_yn"))) { - v_num_recs = (Integer) schedulerUtil.getSingleResult("select count(*) count from (" + rs.getString("condition_large_sql") + " )", "count"); - } - - if (v_num_recs > 0 || "N".equals(rs.getString("conditional_yn"))) { - - v_gen_key = ("Z" + UUID.randomUUID()).toString().substring(0,24); // 25 character string - Object sequenceId = schedulerUtil.getSingleResult(Globals.getSequenceNextVal().replace("[sequenceName]", "seq_email_sent_log_id"), "id"); - - if(sequenceId instanceof Long) - v_id = new BigDecimal((Long)sequenceId); - else if(sequenceId instanceof BigDecimal) - v_id = (BigDecimal)sequenceId; - - schedulerUtil.insertOrUpdate("insert into cr_report_email_sent_log (log_id, gen_key, schedule_id, rep_id, user_id, touch_date) values (" + v_id + ",'" + v_gen_key + "'," + rs.getInt("schedule_id") + "," + rs.getInt("rep_id") + "," + rs.getInt("user_id") + ", " + Globals.getCurrentDateString() + " )"); - - int notify_type = rs.getInt("notify_type"); - if (notify_type == 4) - v_r_action = "report.download"; - else if (notify_type == 2) - v_r_action = "report.download.pdf"; - else if (notify_type == 3) - v_r_action = "report.csv.download"; - else if (notify_type == 5) - v_r_action = "report.download.excel2007"; - else if (notify_type == 6) - v_r_action = "download.all"; - - if (rs.getObject("processed_formfields") != null) - v_formfields = modify_formfields(v_schedule_id, rs.getString("processed_formFields")); - else - v_formfields = strip_formfields(v_schedule_id, rs.getString("initial_formfields")); - - v_url = p_system_url + "&r_action=" + v_r_action + "&log_id=" + v_id + "&user_id=" + rs.getString("user_id") + "&pdfAttachmentKey=" + v_gen_key + "&download_limit=" + rs.getInt("max_row") + v_formfields; - - boolean v_attach_email_yn = shouldSendAttachmentInEmail(v_schedule_id); - - v_email_msg = "

" + p_system_name + " System Notification

" + "

Report " + rs.getString("title") + " is available for viewing.

You can view the report if it is attached.
" - + "If it is not attached, or you have problem to open it, you can log into Business Direct and run the report.

" + ""; - - if (rs.getInt("notify_type") != 6) { - - sendEmail.sendEmail(p_mail_server, p_sender, p_system_name + " System Notification: Report " + rs.getString("title") + " generated", v_email_msg, v_url, rs.getInt("notify_type"), v_schedule_id, p_time_interval, v_attach_email_yn,connectionTimeout); - - } else { - - // may not necessary - schedulerUtil.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values (" + v_schedule_id + ",'" + v_url + "'," + "'Success: http request began.', " + Globals.getCurrentDateString() + " )"); - schedulerUtil.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values (" + v_schedule_id + ",'" + v_url + "'," + "'Success: http response recieved. Code resp.status_code '' desc '' resp.reason_phrase', " + Globals.getCurrentDateString() + " )"); - - } - - schedulerUtil.insertOrUpdate("update cr_report_schedule set run_date = " + Globals.getCurrentDateString() +" where schedule_id=" + v_schedule_id); - - schedulerUtil.insertOrUpdate("update cr_report_email_sent_log set sent_date= " + Globals.getCurrentDateString() +" , access_flag='N' where log_id=" + v_id); - schedulerUtil.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values (" + v_schedule_id + ",'" + v_url + "','Success: Email Sent', " + Globals.getCurrentDateString() + " )"); - - } - - } - - if(rs!=null) - rs.close(); - if(stat!=null) - stat.close(); - //conn.close(); - - } + public void send_notification(final String p_mail_server, final String p_sender, final String p_system_name, + final String p_system_url, final int connectionTimeout) throws RaptorException, Exception { + + logger.info(p_mail_server + " " + p_sender + " " + p_system_name + " " + p_system_url); + final int p_time_interval = Globals.getSchedulerInterval(); + int v_num_recs = 0; + BigDecimal v_id = null; + String v_r_action = "report.download.pdf"; + + final String CNotificationsql = Globals.getAvailableSchedules() + .replace("[currentDate]", Globals.getCurrentDateString()); + + try (final ResultSet rs = schedulerUtil.getConnection().createStatement().executeQuery(CNotificationsql);) { + while (rs.next()) { + final int v_schedule_id = rs.getInt("schedule_id"); + final int offset = get_report_sched_offset(rs.getInt("rep_id"), v_schedule_id); + + if (offset >= p_time_interval) { + continue; + } + + final Date v_touch_date = (Date) schedulerUtil.getSingleResult( + "select touch_date from cr_report_email_sent_log where schedule_id = " + v_schedule_id + + " and log_id = (select max(log_id) from cr_report_email_sent_log where schedule_id = " + + v_schedule_id + ")", "touch_date"); + if (v_touch_date != null) { + if (Math.abs(System.currentTimeMillis() - v_touch_date.getTime()) / 1000 < (p_time_interval - 1)) { + return; + } + } + + if ("Y".equals(rs.getString("conditional_yn"))) { + v_num_recs = (Integer) schedulerUtil + .getSingleResult("select count(*) count from (" + rs.getString("condition_large_sql") + " )", + "count"); + } + + if (v_num_recs > 0 || "N".equals(rs.getString("conditional_yn"))) { + final String v_gen_key = ("Z" + UUID.randomUUID()).toString() + .substring(0, 24); // 25 character string + final Object sequenceId = + schedulerUtil.getSingleResult( + Globals.getSequenceNextVal().replace("[sequenceName]", "seq_email_sent_log_id"), "id"); + + if (sequenceId instanceof Long) { + v_id = new BigDecimal((Long) sequenceId); + } else if (sequenceId instanceof BigDecimal) { + v_id = (BigDecimal) sequenceId; + } + + schedulerUtil.insertOrUpdate( + "insert into cr_report_email_sent_log (log_id, gen_key, schedule_id, rep_id, user_id, touch_date) values (" + + v_id + ",'" + v_gen_key + "'," + rs.getInt("schedule_id") + "," + rs.getInt("rep_id") + + "," + + rs.getInt("user_id") + ", " + Globals.getCurrentDateString() + " )"); + + final int notify_type = rs.getInt("notify_type"); + if (notify_type == 4) { + v_r_action = "report.download"; + } else if (notify_type == 2) { + v_r_action = "report.download.pdf"; + } else if (notify_type == 3) { + v_r_action = "report.csv.download"; + } else if (notify_type == 5) { + v_r_action = "report.download.excel2007"; + } else if (notify_type == 6) { + v_r_action = "download.all"; + } + + final String v_formfields; + if (rs.getObject("processed_formfields") != null) { + v_formfields = modify_formfields(v_schedule_id, rs.getString("processed_formFields")); + } else { + v_formfields = strip_formfields(v_schedule_id, rs.getString("initial_formfields")); + } + + final String v_url = + p_system_url + "&r_action=" + v_r_action + "&log_id=" + v_id + "&user_id=" + rs + .getString("user_id") + + "&pdfAttachmentKey=" + v_gen_key + "&download_limit=" + rs.getInt("max_row") + + v_formfields; + + final boolean v_attach_email_yn = shouldSendAttachmentInEmail(v_schedule_id); + + final String v_email_msg = + "

" + p_system_name + " System Notification

" + + "

Report " + rs.getString("title") + + " is available for viewing.

You can view the report if it is attached.
" + + "If it is not attached, or you have problem to open it, you can log into Business Direct and run the report.

" + + ""; + + if (rs.getInt("notify_type") != 6) { + sendEmail.sendEmail(p_mail_server, p_sender, + p_system_name + " System Notification: Report " + rs.getString("title") + " generated", + v_email_msg, v_url, rs.getInt("notify_type"), v_schedule_id, p_time_interval, + v_attach_email_yn, + connectionTimeout); + + } else { + // may not necessary + schedulerUtil.insertOrUpdate( + "insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values (" + + v_schedule_id + ",'" + v_url + "'," + "'Success: http request began.', " + Globals + .getCurrentDateString() + " )"); + schedulerUtil.insertOrUpdate( + "insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values (" + + v_schedule_id + ",'" + v_url + "'," + + "'Success: http response recieved. Code resp.status_code '' desc '' resp.reason_phrase', " + + Globals.getCurrentDateString() + " )"); + } + + schedulerUtil.insertOrUpdate( + "update cr_report_schedule set run_date = " + Globals.getCurrentDateString() + + " where schedule_id=" + + v_schedule_id); + + schedulerUtil.insertOrUpdate( + "update cr_report_email_sent_log set sent_date= " + Globals.getCurrentDateString() + + " , access_flag='N' where log_id=" + v_id); + schedulerUtil.insertOrUpdate( + "insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values (" + + v_schedule_id + + ",'" + v_url + "','Success: Email Sent', " + Globals.getCurrentDateString() + " )"); + } + } + } catch (final SQLException e) { + logger.warn("Failed to open connection", e); + } + } private boolean shouldSendAttachmentInEmail(int v_schedule_id) throws SQLException, ReportSQLException { diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/DbUtils.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/DbUtils.java index d2a97dfe..1f58ada3 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/DbUtils.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/DbUtils.java @@ -33,7 +33,7 @@ * * ============LICENSE_END============================================ * - * + * */ package org.onap.portalsdk.analytics.system; @@ -44,9 +44,7 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; - import javax.sql.DataSource; - import org.apache.commons.lang3.StringUtils; import org.onap.portalsdk.analytics.error.RaptorException; import org.onap.portalsdk.analytics.error.ReportSQLException; @@ -61,28 +59,28 @@ public class DbUtils { private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(DbUtils.class); - private static DataSource dataSource; + private DataSource dataSource; - public DbUtils() {} + public DbUtils() { + } public static Connection getConnection() throws ReportSQLException { try { return AppUtils.getDatasource().getConnection(); - } catch (SQLException ex) { - logger.error(EELFLoggerDelegate.debugLogger, ("Error " + ex)); + } catch (final SQLException ex) { + logger.warn("Failed to get Connection", ex); } return null; } // getConnection - public static void clearConnection(Connection con) throws ReportSQLException { + public static void clearConnection(final Connection con) throws ReportSQLException { try { if ((con != null) && !con.isClosed()) { Globals.getDbUtils().clearConnection(con); } - } catch (SQLException ex) { + } catch (final Exception ex) { + logger.warn("Failed to clear Connection", ex); throw new ReportSQLException(ex.getMessage(), ex.getCause()); - } catch (Exception ex2) { - throw new ReportSQLException(ex2.getMessage(), ex2.getCause()); } } // clearConnection @@ -91,52 +89,53 @@ public class DbUtils { try { con = getConnection(); con.setAutoCommit(false); - - } catch (SQLException ex) { - throw new ReportSQLException(ex.getMessage(), ex.getCause()); - } catch (Exception ex2) { + } catch (final Exception ex2) { + logger.warn("Failed to start Transaction", ex2); throw new ReportSQLException(ex2.getMessage(), ex2.getCause()); } return con; } // startTransaction - public static void commitTransaction(Connection con) throws ReportSQLException { + public static void commitTransaction(final Connection con) throws ReportSQLException { try { con.commit(); - } catch (SQLException ex) { - throw new ReportSQLException(ex.getMessage(), ex.getCause()); - } catch (Exception ex2) { + } catch (final Exception ex2) { + logger.warn("Failed to commit Transaction", ex2); throw new ReportSQLException(ex2.getMessage(), ex2.getCause()); } } // commitTransaction - public static void rollbackTransaction(Connection con) throws ReportSQLException { + public static void rollbackTransaction(final Connection con) throws ReportSQLException { try { con.rollback(); clearConnection(con); - } catch (SQLException ex) { - throw new ReportSQLException(ex.getMessage(), ex.getCause()); - } catch (Exception ex2) { + } catch (final Exception ex2) { + logger.warn("Failed to rollback Transaction", ex2); throw new ReportSQLException(ex2.getMessage(), ex2.getCause()); } } // rollbackTransaction - public static String executeCall(Connection con, String sql, boolean expectResult) throws ReportSQLException { + public static String executeCall(Connection con, final String sql, final boolean expectResult) + throws ReportSQLException { String result = null; try { - if (con.isClosed()) + if (con.isClosed()) { con = getConnection(); + } logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL Call] " + sql)); - try (CallableStatement stmt = con.prepareCall(sql)) { - if (expectResult) + try (final CallableStatement stmt = con.prepareCall(sql)) { + if (expectResult) { stmt.registerOutParameter(1, Types.CHAR); + } stmt.executeUpdate(); - if (expectResult) + if (expectResult) { result = stmt.getString(1); + } con.commit(); } - } catch (SQLException e) { + } catch (final SQLException e) { + logger.warn("Failed to execute Call", e); throw new ReportSQLException(e.getMessage(), sql); } finally { clearConnection(con); @@ -145,60 +144,66 @@ public class DbUtils { return result; } // executeCall - public static String executeCall(String sql, boolean expectResult) throws RaptorException { + public static String executeCall(final String sql, final boolean expectResult) + throws RaptorException { Connection con = null; con = getConnection(); - String result = executeCall(con, sql, expectResult); - return result; + return executeCall(con, sql, expectResult); } // executeCall - public static int executeUpdate(Connection con, String sql) throws ReportSQLException { + public static int executeUpdate(final Connection con, final String sql) throws ReportSQLException { int rcode = -1; try (Statement stmt = con.createStatement()) { logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL Update] " + sql)); rcode = stmt.executeUpdate(sql); - } catch (SQLException e) { + } catch (final SQLException e) { + logger.warn("Failed to execute Update", e); throw new ReportSQLException(e.getMessage(), sql); } return rcode; } // executeUpdate - public static int executeUpdate(String sql) throws ReportSQLException { - Connection con = null; - try { - con = getConnection(); - int rcode = executeUpdate(con, sql); - if (Globals.getDBType().equals("oracle")) + public static int executeUpdate(final String sql) throws ReportSQLException { + try (final Connection con = getConnection()) { + final int rcode = executeUpdate(con, sql); + if ("oracle".equals(Globals.getDBType())) { con.commit(); - + } return rcode; - } catch (SQLException e) { + } catch (final SQLException e) { + logger.warn("Failed to execute Update", e); throw new ReportSQLException(e.getMessage(), sql); - } finally { - clearConnection(con); } } // executeUpdate - public static DataSet executeQuery(Connection con, String sql) throws ReportSQLException { + public static DataSet executeQuery(final Connection con, final String sql) throws ReportSQLException { return executeQuery(con, sql, Integer.MAX_VALUE); } // executeQuery - public static DataSet executeQuery(Connection con, String sql, int maxRowLimit) throws ReportSQLException { + public static DataSet executeQuery(Connection con, final String sql, final int maxRowLimit) + throws ReportSQLException { try { - if (con.isClosed()) + if (con.isClosed()) { con = getConnection(); - // con. - try (Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql)) { + } + //con. + try (final Statement stmt = con.createStatement(); + final ResultSet rs = stmt.executeQuery(sql)) { logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL] " + sql)); return new DataSet(rs, maxRowLimit); } - } catch (SQLException e) { + } catch (final SQLException e) { + logger.warn("Failed to execute Query", e); throw new ReportSQLException(e.getMessage(), sql); } } // executeQuery - public static DataSet executeQuery(String sql, String reportName, String reportID) throws ReportSQLException { - try (Connection con = getConnection(); PreparedStatement preparedStatement = con.prepareStatement(sql);) { + public static DataSet executeQuery(final String sql, final String reportName, final String reportID) + throws ReportSQLException { + + try (final Connection con = getConnection(); + final PreparedStatement preparedStatement = con.prepareStatement(sql);) { + if (StringUtils.isNotBlank(reportID)) { preparedStatement.setString(1, reportID); preparedStatement.setString(2, reportName); @@ -206,95 +211,79 @@ public class DbUtils { preparedStatement.setString(1, reportName); } - try (ResultSet rs = preparedStatement.executeQuery();) { + try (final ResultSet rs = preparedStatement.executeQuery();) { logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL] " + sql)); return new DataSet(rs, Integer.MAX_VALUE); } - } catch (SQLException e) { - throw new ReportSQLException(e.getMessage(), sql); - } catch (ReportSQLException ex) { - logger.error(EELFLoggerDelegate.debugLogger, ("Error " + sql)); - throw new ReportSQLException(ex.getMessage(), ex); - } catch (Exception ex1) { - throw new ReportSQLException(ex1.getMessage(), ex1.getCause()); + } catch (final Exception e) { + logger.error(EELFLoggerDelegate.debugLogger, ("Error " + sql), e); + throw new ReportSQLException(e.getMessage(), e.getCause()); } } // executeQuery - public static DataSet executeQuery(String sql) throws ReportSQLException { + public static DataSet executeQuery(final String sql) throws ReportSQLException { return executeQuery(sql, Integer.MAX_VALUE); } // executeQuery - public static DataSet executeQuery(String sql, int maxRowLimit) throws ReportSQLException { - Connection con = null; - try { - con = getConnection(); + public static DataSet executeQuery(final String sql, final int maxRowLimit) throws ReportSQLException { + try (final Connection con = getConnection()) { return executeQuery(con, sql, maxRowLimit); - } catch (ReportSQLException ex) { + } catch (final ReportSQLException ex) { logger.error(EELFLoggerDelegate.debugLogger, ("Error " + sql)); throw new ReportSQLException(ex.getMessage(), ex); - } catch (Exception ex1) { + } catch (final Exception ex1) { + logger.warn("Failed to execute Query", ex1); throw new ReportSQLException(ex1.getMessage(), ex1.getCause()); - } finally { - clearConnection(con); } } // executeQuery - // For ZK Support + //For ZK Support - public static int executeQuery(ReportRuntime rr, int dateOption) { - Connection con = null; - int rowCount = 0; - try { - con = ConnectionUtils.getConnection(rr.getDBInfo()); - String wholeSql = rr.getWholeSQL(); + public static int executeQuery(final ReportRuntime rr, final int dateOption) { - DataColumnType dc = rr.getColumnWhichNeedEnhancedPagination(); + int rowCount = 0; + try (final Connection con = ConnectionUtils.getConnection(rr.getDBInfo());) { + final String wholeSql = rr.getWholeSQL(); + final DataColumnType dc = rr.getColumnWhichNeedEnhancedPagination(); String date_ColId = dc.getColId(); - String dataFormat = dc.getColFormat(); - if (dataFormat != null && dataFormat.length() > 0) + final String dataFormat = dc.getColFormat(); + if (dataFormat != null && dataFormat.length() > 0) { date_ColId = "to_date(" + date_ColId + ", '" + dataFormat + "')"; + } String sql = ""; - if (dateOption == 1) + if (dateOption == 1) { sql = "select count(distinct to_char(" + date_ColId + ", 'YYYY/MM')) from (" + wholeSql + ")"; - else if (dateOption == 3) + } else if (dateOption == 3) { sql = "select count(distinct to_char(" + date_ColId + ", 'YYYY/MM/DD')) from (" + wholeSql + ")"; - else if (dateOption == 2) + } else if (dateOption == 2) { sql = "select count(distinct to_char(" + date_ColId + ", 'YYYY')) from (" + wholeSql + ")"; - DataSet ds = executeQuery(con, sql.toString()); - rowCount = ds.getInt(0, 0); - } catch (ReportSQLException ex) { - logger.error(EELFLoggerDelegate.debugLogger, ("Error " + ex)); - } catch (Exception ex1) { - logger.error(EELFLoggerDelegate.debugLogger, ("Error " + ex1)); - } finally { - try { - clearConnection(con); - } catch (ReportSQLException ex2) { - logger.error(EELFLoggerDelegate.debugLogger, ("Error " + ex2)); } + final DataSet ds = executeQuery(con, sql); + rowCount = ds.getInt(0, 0); + } catch (final Exception ex1) { + logger.warn("Failed to execute Query", ex1); } return rowCount; } - public String nvl(String s) { + public static String nvls(final String s) { return (s == null) ? "" : s; } - public static String nvls(String s) { - return (s == null) ? "" : s; + public static String nvl(final String s, final String sDefault) { + return nvls(s).isEmpty() ? sDefault : s; } - public static String nvl(String s, String sDefault) { - return nvls(s).equals("") ? sDefault : s; + public String nvl(final String s) { + return (s == null) ? "" : s; } - public static DataSource getDataSource() { + public DataSource getDataSource() { return dataSource; } @Autowired - public void setDataSource(DataSource dataSource) { - dataSource = dataSource; + public void setDataSource(final DataSource dataSource) { + this.dataSource = dataSource; } - } // DbUtils -- cgit 1.2.3-korg