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

Introduction

Here is the source code for com.webbfontaine.valuewebb.report.AirCargoReporter.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.PrintUtils;
import com.webbfontaine.valuewebb.model.util.Utils;
import com.webbfontaine.valuewebb.report.invoice.InvoiceReporter;
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.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.webbfontaine.valuewebb.model.constants.Constants.*;

public class AirCargoReporter extends InvoiceReporter {
    private static final Logger LOGGER = LoggerFactory.getLogger(AirCargoReporter.class);

    private static final String QUERY = "SELECT  tt_id," + "trunc(tt_dat) tt_dat," + "status,"
            + "(SELECT dsc FROM RIMM_MOT WHERE cod = trs_ship_mod) trs_ship_mod," + "imp_nam," + "app_nam,"
            + "fcvr_num," + "trs_bl_num," + "trs_gds_dsc, " + "cty_supp " + "FROM TT_GEN "
            + "WHERE tt_dat >= to_date(?, 'YYYY-MM-DD') "
            + "  AND tt_dat <= to_date(?,'YYYY-MM-DD') + (1-1/24/60/60) ";
    private static final String STATUS_TAIL = "  AND status = ? ";
    private static final String SHIP_MOD_TAIL = "  AND trs_ship_mod = ? ";
    private static final int DEFAULT_STARTING_ROW_NUMBER = 1;
    private AirCargoReportBean airCargoReportBean;

    public AirCargoReporter(AirCargoReportBean airCargoReportBean) {
        super(airCargoReportBean.getDateFrom(), airCargoReportBean.getDateTo());
        this.airCargoReportBean = airCargoReportBean;
    }

    @Override
    public int makeReport() {
        try {
            byte[] report = getXLS();
            if (report != null) {
                Utils.sendBytes(report, getReportName(), contentType);
                return SUCCEEDED;
            } else {
                return IS_EMPTY;
            }
        } catch (SQLException | IOException | RuntimeException e) {
            LOGGER.error("Error occurred during query processing", e);
            return HAS_ERROR;
        }
    }

    private byte[] getXLS() throws SQLException, IOException {

        try (Connection connection = getConnection();
                PreparedStatement ps = getPreparedStatement(connection);
                ResultSet rs = ps.executeQuery();
                InputStream is = getTemplateFileStream(getReportFilePath())) {

            rs.setFetchSize(1000);

            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 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.getDate("tt_dat"), cellStyleMap.get("GENERAL_DATE_STYLE"));
            createCell(sheetRow, 'C', rs.getString("status"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE"));
            createCell(sheetRow, 'D', rs.getString("trs_ship_mod"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE"));
            createCell(sheetRow, 'E', rs.getString("imp_nam"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE"));
            createCell(sheetRow, 'F', rs.getString("app_nam"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE"));
            createCell(sheetRow, 'G', rs.getString("fcvr_num"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE"));
            createCell(sheetRow, 'H', rs.getString("trs_bl_num"), cellStyleMap.get("GENERAL_CENTER_TEXT_STYLE"));
            createCell(sheetRow, 'I', rs.getString("trs_gds_dsc"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE"));
            createCell(sheetRow, 'J', rs.getString("cty_supp"), cellStyleMap.get("GENERAL_LEFT_TEXT_STYLE"));
            rowNumber++;
        }
        return rowNumber;
    }

    private PreparedStatement getPreparedStatement(Connection connection) throws SQLException {
        Map queryParameters = constructQueryParameters();
        String query = (String) queryParameters.get("query");
        List params = (List) queryParameters.get("parameters");
        PreparedStatement ps = connection.prepareStatement(query);

        for (int i = 1; i <= params.size(); i++) {
            ps.setString(i, (String) params.get(i - 1));
        }

        return ps;
    }

    private Map constructQueryParameters() {
        Map<String, Object> queryParameters = new HashMap<>();
        String queryTail = "";
        List<String> params = new ArrayList<>();
        params.add(dateFrom);
        params.add(dateTo);

        String status = airCargoReportBean.getStatus();
        if (status != null) {
            params.add(status);
            queryTail += STATUS_TAIL;
        }

        String shipMod = airCargoReportBean.getModeOfTransport();
        if (shipMod != null) {
            params.add(shipMod);
            queryTail += SHIP_MOD_TAIL;
        }

        queryParameters.put("query", QUERY + queryTail);
        queryParameters.put("parameters", params);
        return queryParameters;
    }

    @Override
    protected String getReportFilePath() {
        return PrintUtils.getAirCargoTemplateFilePath();
    }

    @Override
    protected String getReportName() {
        return "Air-Cargo Report(" + dateFrom + "-" + dateTo + ").xls";
    }

    @Override
    protected Connection getConnection() {
        return Utils.getSQLConnection();
    }
}