Java tutorial
package com.webbfontaine.valuewebb.report; /* --------------------------------------------------------------- * Copyrights 2002-2014 Webb Fontaine * * This software is the proprietary information of Webb Fontaine. * Its use is subject to License terms. * Developer : Edgar Harutyunyan * Date: 4/8/14 * --------------------------------------------------------------- */ import com.webbfontaine.valuewebb.model.util.DBUtils; import com.webbfontaine.valuewebb.model.util.PrintUtils; import com.webbfontaine.valuewebb.model.util.Utils; import com.webbfontaine.valuewebb.report.invoice.InvoiceReporter; import net.sf.jasperreports.engine.*; import net.sf.jasperreports.engine.export.JRPdfExporter; import net.sf.jasperreports.engine.util.JRLoader; import org.apache.commons.io.IOUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Map; import static com.webbfontaine.valuewebb.model.constants.Constants.*; public class ScanSelectivityReporter extends InvoiceReporter { private static final Logger LOGGER = LoggerFactory.getLogger(ScanSelectivityReporter.class); private static final String QUERY = "WITH SEL_CRT_LIST AS (SELECT tt_id," + " LISTAGG(crt_cod, ',') WITHIN GROUP (ORDER BY NULL) AS crt_cod1, " + " (SELECT 'CIR014' FROM DUAL) AS crt_cod2 " + " FROM ( SELECT DISTINCT(h1.tt_id), " + " h2.crt_cod " + " FROM HITS h1 INNER JOIN HITS h2 ON h1.tt_id = h2.tt_id " + " AND h1.crt_cod = 'CIR014' " + " AND h2.crt_cod = 'CIR007' " + " UNION ALL " + " SELECT DISTINCT(h1.tt_id), " + " h2.crt_cod " + " FROM HITS h1 INNER JOIN HITS h2 ON h1.tt_id = h2.tt_id " + " AND h1.crt_cod = 'CIR014' " + " AND h2.crt_cod = 'CIR006' " + " ORDER BY tt_id) " + " GROUP BY tt_id " + " )" + " SELECT selCrts.tt_id, " + " selCrts.crt_cod1, " + " selCrts.crt_cod2, " + " (SELECT COUNT(*) FROM TT_GEN_CONTAINER ttCont WHERE ttCont.tt_gen_tt_id = selCrts.tt_id) AS num_of_cont, " + " ttGen.idf_num AS dai_num, " + " ttGen.trs_bl_num AS bl_num, " + " pd.pd_dpd AS prod_description, " + " pd.d_qty AS declared_quantity, " + " pd.d_unit, " + " ttGen.trs_grs_wgt, " + " ttGen.trs_net_wgt, " + " (SELECT to_char(MAX(tim),'DD-MON-YYYY HH24:MI') FROM HITS WHERE tt_id = selCrts.tt_id AND crt_cod IN ('CIR014', 'CIR007', 'CIR006')) AS hit_date " + " FROM SEL_CRT_LIST selCrts , TT_GEN ttGen, PD pd " + " WHERE selCrts.tt_id = ttGen.tt_id " + " AND ttGen.status = 'Accepted' " + " AND ttGen.is_final_docs_exist = 1 " + " AND ttGen.tt_id = pd.tt_id " + " AND ttGen.pds_nbr = 1 " + " AND ttGen.fi_dat >= to_date(?, 'YYYY-MM-DD') " + " AND ttGen.fi_dat <= to_date(?,'YYYY-MM-DD') + (1-1/24/60/60) "; private static final int DEFAULT_STARTING_ROW_NUMBER = 1; private String reportType; public ScanSelectivityReporter(ScanSelectivityReportBean bean, String type) { super(bean.getDateFrom(), bean.getDateTo()); reportType = type; } @Override public int makeReport() { LOGGER.debug("Trying to generate Scan Selectivity Report..."); try { byte[] report = generateReport(); if (report != null) { Utils.sendBytes(report, getReportName(), contentType); return SUCCEEDED; } else { return IS_EMPTY; } } catch (SQLException | IOException | JRException e) { LOGGER.error("Error occurred during Scan Selectivity Report...", e); return HAS_ERROR; } } private byte[] generateReport() throws SQLException, IOException, JRException { Connection connection = null; PreparedStatement ps = null; InputStream templateFile = null; ResultSet rs = null; try { connection = getConnection(); ps = getPreparedStatement(connection); templateFile = getTemplateFileStream(getReportFilePath()); rs = ps.executeQuery(); rs.setFetchSize(1000); if (XLS_REPORT.equals(reportType)) { return processXls(templateFile, rs); } else { return processPdf(templateFile, rs); } } finally { DBUtils.closeResource(connection); DBUtils.closeResource(ps); IOUtils.closeQuietly(templateFile); DBUtils.closeResource(rs); } } private byte[] processXls(InputStream is, ResultSet rs) throws IOException, SQLException { HSSFWorkbook workbook = new HSSFWorkbook(is); Sheet sheet = workbook.getSheetAt(0); Map<String, CellStyle> cellStyleMap = getAllCellStyles(workbook); int rowsCreated = createCells(rs, cellStyleMap, sheet); if (rowsCreated == DEFAULT_STARTING_ROW_NUMBER) { LOGGER.debug("Report is empty"); return null; } try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) { workbook.write(outputStream); return outputStream.toByteArray(); } } private byte[] processPdf(InputStream is, ResultSet rs) throws JRException, IOException { JRDataSource jrDataSource = new JRResultSetDataSource(rs); JasperReport compiledReport = (JasperReport) JRLoader.loadObject(is); JasperPrint jasperPrint = JasperFillManager.fillReport(compiledReport, parameters, jrDataSource); JRExporter exporter = new JRPdfExporter(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint); exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, outputStream); exporter.exportReport(); outputStream.close(); return outputStream.toByteArray(); } private int createCells(ResultSet rs, Map<String, CellStyle> cellStyleMap, Sheet sheet) throws SQLException { int rowNumber = DEFAULT_STARTING_ROW_NUMBER; while (rs.next()) { Row sheetRow = sheet.createRow(rowNumber); createCell(sheetRow, 'A', rs.getBigDecimal("tt_id"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE")); createCell(sheetRow, 'B', rs.getString("crt_cod1"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE")); createCell(sheetRow, 'C', rs.getString("crt_cod2"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE")); createCell(sheetRow, 'D', rs.getBigDecimal("num_of_cont"), cellStyleMap.get("CENTER_DIGIT_CELL_STYLE")); createCell(sheetRow, 'E', rs.getString("dai_num"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE")); createCell(sheetRow, 'F', rs.getString("bl_num"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE")); createCell(sheetRow, 'G', rs.getString("prod_description"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE")); createCell(sheetRow, 'H', rs.getBigDecimal("declared_quantity"), cellStyleMap.get("GENERAL_RIGHT_NUMBER_STYLE")); createCell(sheetRow, 'I', rs.getString("d_unit"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE")); createCell(sheetRow, 'J', rs.getBigDecimal("trs_grs_wgt"), cellStyleMap.get("GENERAL_CENTER_NUMBER_STYLE")); createCell(sheetRow, 'K', rs.getBigDecimal("trs_net_wgt"), cellStyleMap.get("GENERAL_CENTER_NUMBER_STYLE")); createCell(sheetRow, 'L', rs.getString("hit_date"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE")); rowNumber++; } return rowNumber; } private PreparedStatement getPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(QUERY); ps.setString(1, dateFrom); ps.setString(2, dateTo); return ps; } @Override protected String getReportFilePath() { return XLS_REPORT.equals(reportType) ? PrintUtils.getScanSelectivityTemplateFilePath() : "/reports/scanSelectivityReport.jasper"; } @Override protected String getReportName() { return "ScanSelectivityReport(" + dateFrom + "--" + dateTo + ")." + reportType; } @Override protected Connection getConnection() { return Utils.getSQLConnection(); } }