com.webbfontaine.valuewebb.report.ScanSelectivityReporter.java Source code

Java tutorial

Introduction

Here is the source code for com.webbfontaine.valuewebb.report.ScanSelectivityReporter.java

Source

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();
    }
}