<commit_before>package com.cebedo.pmsys.service.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.validation.BindingResult;
import org.springframework.web.multipart.MultipartFile;

import com.cebedo.pmsys.bean.EstimateComputationBean;
import com.cebedo.pmsys.bean.EstimateComputationInputBean;
import com.cebedo.pmsys.bean.EstimateComputationOutputRowJSON;
import com.cebedo.pmsys.bean.EstimateComputationShape;
import com.cebedo.pmsys.bean.EstimateResultConcrete;
import com.cebedo.pmsys.bean.EstimateResultMRCHB;
import com.cebedo.pmsys.bean.EstimateResultMRIndependentFooting;
import com.cebedo.pmsys.bean.EstimateResultMasonryCHB;
import com.cebedo.pmsys.bean.EstimateResultMasonryCHBFooting;
import com.cebedo.pmsys.bean.EstimateResultMasonryCHBLaying;
import com.cebedo.pmsys.bean.EstimateResultMasonryPlastering;
import com.cebedo.pmsys.constants.ConstantsEstimation;
import com.cebedo.pmsys.constants.ConstantsRedis;
import com.cebedo.pmsys.constants.RegistryResponseMessage;
import com.cebedo.pmsys.domain.EstimationOutput;
import com.cebedo.pmsys.enums.AuditAction;
import com.cebedo.pmsys.enums.EstimateType;
import com.cebedo.pmsys.enums.TableDimensionCHB;
import com.cebedo.pmsys.enums.TableDimensionCHBFooting;
import com.cebedo.pmsys.enums.TableMRCHBHorizontal;
import com.cebedo.pmsys.enums.TableMRCHBTieWire;
import com.cebedo.pmsys.enums.TableMRCHBVertical;
import com.cebedo.pmsys.enums.TableMixtureCHBFooting;
import com.cebedo.pmsys.enums.TableMixtureCHBLaying;
import com.cebedo.pmsys.enums.TableMixturePlaster;
import com.cebedo.pmsys.enums.TableProportionConcrete;
import com.cebedo.pmsys.helper.AuthHelper;
import com.cebedo.pmsys.helper.ExcelHelper;
import com.cebedo.pmsys.helper.MessageHelper;
import com.cebedo.pmsys.helper.ValidationHelper;
import com.cebedo.pmsys.model.Project;
import com.cebedo.pmsys.repository.EstimationOutputValueRepo;
import com.cebedo.pmsys.service.EstimateService;
import com.cebedo.pmsys.ui.AlertBoxGenerator;
import com.cebedo.pmsys.utils.EstimateUtils;
import com.cebedo.pmsys.validator.EstimateInputValidator;
import com.google.gson.Gson;

@Service
public class EstimateServiceImpl implements EstimateService {

    // Details.
    private static final int EXCEL_DETAILS_QUANTITY = 1;
    private static final int EXCEL_DETAILS_NAME = 2;
    private static final int EXCEL_DETAILS_AREA = 3;
    private static final int EXCEL_DETAILS_VOLUME = 4;

    // Estimate and Remarks.
    private static final int EXCEL_ESTIMATE_MASONRY_CONCRETE = 5;
    private static final int EXCEL_ESTIMATE_MASONRY_CHB = 6;
    private static final int EXCEL_ESTIMATE_MASONRY_CHB_LAYING = 7;
    private static final int EXCEL_ESTIMATE_MASONRY_PLASTERING = 8;
    private static final int EXCEL_ESTIMATE_MASONRY_FOUNDATION_AREA = 9;
    private static final int EXCEL_ESTIMATE_MASONRY_CHB_FOOTING = 10;
    private static final int EXCEL_ESTIMATE_MASONRY_FOOTING_LENGTH = 11;
    private static final int EXCEL_ESTIMATE_MASONRY_FOOTING_WIDTH = 12;
    private static final int EXCEL_ESTIMATE_MASONRY_FOOTING_HEIGHT = 13;
    private static final int EXCEL_ESTIMATE_MR_CHB = 14;
    private static final int EXCEL_ESTIMATE_MR_INDEPENDENT_FOOTING = 15;
    private static final int EXCEL_ESTIMATE_MR_FOOTING_BAR_LENGTH = 16;
    private static final int EXCEL_ESTIMATE_MR_FOOTING_BAR_PER_FOOTING = 17;
    private static final int EXCEL_DETAILS_REMARKS = 18;

    // Cost
    private static final int EXCEL_COST_CHB = 19;
    private static final int EXCEL_COST_CEMENT_40KG = 20;
    private static final int EXCEL_COST_CEMENT_50KG = 21;
    private static final int EXCEL_COST_SAND = 22;
    private static final int EXCEL_COST_GRAVEL = 23;
    private static final int EXCEL_COST_STEEL_BAR = 24;
    private static final int EXCEL_COST_TIE_WIRE_KILOS = 25;
    private static final int EXCEL_COST_TIE_WIRE_ROLLS = 26;

    private MessageHelper messageHelper = new MessageHelper();
    private AuthHelper authHelper = new AuthHelper();
    private ExcelHelper excelHelper = new ExcelHelper();
    private ValidationHelper validationHelper = new ValidationHelper();

    private EstimationOutputValueRepo estimationOutputValueRepo;

    public void setEstimationOutputValueRepo(EstimationOutputValueRepo estimationOutputValueRepo) {
	this.estimationOutputValueRepo = estimationOutputValueRepo;
    }

    @Autowired
    EstimateInputValidator estimateInputValidator;

    @Override
    @Transactional
    public HSSFWorkbook exportXLS(String key) {

	EstimationOutput output = this.estimationOutputValueRepo.get(key);

	// Security check.
	if (!this.authHelper.isActionAuthorized(output)) {
	    this.messageHelper.unauthorized(ConstantsRedis.OBJECT_ESTIMATION_OUTPUT, output.getKey());
	    return new HSSFWorkbook();
	}
	this.messageHelper.send(AuditAction.ACTION_EXPORT, ConstantsRedis.OBJECT_ESTIMATE, key);
	HSSFWorkbook wb = new HSSFWorkbook();

	// Summary sheet.
	constructSheetSummary(wb, output);

	// Inputs.
	constructSheetInputs(wb, output);

	// Concrete.
	constructSheetConcrete(wb, output);

	// CHB Laying.
	constructSheetCHBLaying(wb, output);

	// Plastering.
	constructSheetCHBPlastering(wb, output);

	// CHB (Footing).
	constructSheetCHBFooting(wb, output);

	// Metal Reinforcement (CHB).
	constructSheetMRCHB(wb, output);

	return wb;
    }

    private void constructSheetMRCHB(HSSFWorkbook wb, EstimationOutput output) {
	// For headers.
	HSSFSheet sheet = wb.createSheet("Metal Reinforcement (CHB)");
	int rowIndex = 0;

	// Headers.
	HSSFRow row = sheet.createRow(rowIndex);
	HSSFCell cellQuantity = row.createCell(3);
	HSSFCell cellCost = row.createCell(6);
	CellUtil.setAlignment(cellQuantity, wb, CellStyle.ALIGN_CENTER);
	CellUtil.setAlignment(cellCost, wb, CellStyle.ALIGN_CENTER);
	cellQuantity.setCellValue("Quantity");<commit_msg>Independent footing result included when exported to XLS<commit_after>package com.cebedo.pmsys.service.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.validation.BindingResult;
import org.springframework.web.multipart.MultipartFile;

import com.cebedo.pmsys.bean.EstimateComputationBean;
import com.cebedo.pmsys.bean.EstimateComputationInputBean;
import com.cebedo.pmsys.bean.EstimateComputationOutputRowJSON;
import com.cebedo.pmsys.bean.EstimateComputationShape;
import com.cebedo.pmsys.bean.EstimateResultConcrete;
import com.cebedo.pmsys.bean.EstimateResultMRCHB;
import com.cebedo.pmsys.bean.EstimateResultMRIndependentFooting;
import com.cebedo.pmsys.bean.EstimateResultMasonryCHB;
import com.cebedo.pmsys.bean.EstimateResultMasonryCHBFooting;
import com.cebedo.pmsys.bean.EstimateResultMasonryCHBLaying;
import com.cebedo.pmsys.bean.EstimateResultMasonryPlastering;
import com.cebedo.pmsys.constants.ConstantsEstimation;
import com.cebedo.pmsys.constants.ConstantsRedis;
import com.cebedo.pmsys.constants.RegistryResponseMessage;
import com.cebedo.pmsys.domain.EstimationOutput;
import com.cebedo.pmsys.enums.AuditAction;
import com.cebedo.pmsys.enums.EstimateType;
import com.cebedo.pmsys.enums.TableDimensionCHB;
import com.cebedo.pmsys.enums.TableDimensionCHBFooting;
import com.cebedo.pmsys.enums.TableMRCHBHorizontal;
import com.cebedo.pmsys.enums.TableMRCHBTieWire;
import com.cebedo.pmsys.enums.TableMRCHBVertical;
import com.cebedo.pmsys.enums.TableMixtureCHBFooting;
import com.cebedo.pmsys.enums.TableMixtureCHBLaying;
import com.cebedo.pmsys.enums.TableMixturePlaster;
import com.cebedo.pmsys.enums.TableProportionConcrete;
import com.cebedo.pmsys.helper.AuthHelper;
import com.cebedo.pmsys.helper.ExcelHelper;
import com.cebedo.pmsys.helper.MessageHelper;
import com.cebedo.pmsys.helper.ValidationHelper;
import com.cebedo.pmsys.model.Project;
import com.cebedo.pmsys.repository.EstimationOutputValueRepo;
import com.cebedo.pmsys.service.EstimateService;
import com.cebedo.pmsys.ui.AlertBoxGenerator;
import com.cebedo.pmsys.utils.EstimateUtils;
import com.cebedo.pmsys.validator.EstimateInputValidator;
import com.google.gson.Gson;

@Service
public class EstimateServiceImpl implements EstimateService {

    // Details.
    private static final int EXCEL_DETAILS_QUANTITY = 1;
    private static final int EXCEL_DETAILS_NAME = 2;
    private static final int EXCEL_DETAILS_AREA = 3;
    private static final int EXCEL_DETAILS_VOLUME = 4;

    // Estimate and Remarks.
    private static final int EXCEL_ESTIMATE_MASONRY_CONCRETE = 5;
    private static final int EXCEL_ESTIMATE_MASONRY_CHB = 6;
    private static final int EXCEL_ESTIMATE_MASONRY_CHB_LAYING = 7;
    private static final int EXCEL_ESTIMATE_MASONRY_PLASTERING = 8;
    private static final int EXCEL_ESTIMATE_MASONRY_FOUNDATION_AREA = 9;
    private static final int EXCEL_ESTIMATE_MASONRY_CHB_FOOTING = 10;
    private static final int EXCEL_ESTIMATE_MASONRY_FOOTING_LENGTH = 11;
    private static final int EXCEL_ESTIMATE_MASONRY_FOOTING_WIDTH = 12;
    private static final int EXCEL_ESTIMATE_MASONRY_FOOTING_HEIGHT = 13;
    private static final int EXCEL_ESTIMATE_MR_CHB = 14;
    private static final int EXCEL_ESTIMATE_MR_INDEPENDENT_FOOTING = 15;
    private static final int EXCEL_ESTIMATE_MR_FOOTING_BAR_LENGTH = 16;
    private static final int EXCEL_ESTIMATE_MR_FOOTING_BAR_PER_FOOTING = 17;
    private static final int EXCEL_DETAILS_REMARKS = 18;

    // Cost
    private static final int EXCEL_COST_CHB = 19;
    private static final int EXCEL_COST_CEMENT_40KG = 20;
    private static final int EXCEL_COST_CEMENT_50KG = 21;
    private static final int EXCEL_COST_SAND = 22;
    private static final int EXCEL_COST_GRAVEL = 23;
    private static final int EXCEL_COST_STEEL_BAR = 24;
    private static final int EXCEL_COST_TIE_WIRE_KILOS = 25;
    private static final int EXCEL_COST_TIE_WIRE_ROLLS = 26;

    private MessageHelper messageHelper = new MessageHelper();
    private AuthHelper authHelper = new AuthHelper();
    private ExcelHelper excelHelper = new ExcelHelper();
    private ValidationHelper validationHelper = new ValidationHelper();

    private EstimationOutputValueRepo estimationOutputValueRepo;

    public void setEstimationOutputValueRepo(EstimationOutputValueRepo estimationOutputValueRepo) {
	this.estimationOutputValueRepo = estimationOutputValueRepo;
    }

    @Autowired
    EstimateInputValidator estimateInputValidator;

    @Override
    @Transactional
    public HSSFWorkbook exportXLS(String key) {

	EstimationOutput output = this.estimationOutputValueRepo.get(key);

	// Security check.
	if (!this.authHelper.isActionAuthorized(output)) {
	    this.messageHelper.unauthorized(ConstantsRedis.OBJECT_ESTIMATION_OUTPUT, output.getKey());
	    return new HSSFWorkbook();
	}
	this.messageHelper.send(AuditAction.ACTION_EXPORT, ConstantsRedis.OBJECT_ESTIMATE, key);
	HSSFWorkbook wb = new HSSFWorkbook();

	// Summary sheet.
	constructSheetSummary(wb, output);

	// Inputs.
	constructSheetInputs(wb, output);

	// Concrete.
	constructSheetConcrete(wb, output);

	// CHB Laying.
	constructSheetCHBLaying(wb, output);

	// Plastering.
	constructSheetCHBPlastering(wb, output);

	// CHB (Footing).
	constructSheetCHBFooting(wb, output);

	// Metal Reinforcement (CHB).
	constructSheetMRCHB(wb, output);

	// Metal Reinforcement (Independent Footing).
	constructSheetMRIndependentFooting(wb, output);

	return wb;
    }

    private void constructSheetMRIndependentFooting(HSSFWorkbook wb, EstimationOutput output) {
	// For headers.
	HSSFSheet sheet = wb.createSheet("Metal Rein. (Ind. Footing)");
	int rowIndex = 0;

	// Headers.
	HSSFRow row = sheet.createRow(rowIndex);
	HSSFCell cellQuantity = row.createCell(3);
	HSSFCell cellCost = row.createCell(4);
	CellUtil.setAlignment(cellQuantity, wb, CellStyle.ALIGN_CENTER);
	CellUtil.setAlignment(cellCost, wb, CellStyle.ALIGN_CENTER);
	cellQuantity.setCellValue("Quantity");
	cellCost.setCellValue("Cost");
	rowIndex++;

	// Headers.
	row = sheet.createRow(rowIndex);
	row.createCell(0).setCellValue("Quantity");
	row.createCell(1).setCellValue("Name");
	row.createCell(2).setCellValue("Steel Bar Length (Meters)");
	row.createCell(3).setCellValue("Steel Bar (Pieces)");
	row.createCell(4).setCellValue("Steel Bar (PHP/Piece)");
	rowIndex++;

	for (EstimateComputationBean computedRow : output.getEstimates()) {
	    EstimateResultMRIndependentFooting estimate = computedRow.getResultMRIndependentFooting();

	    row = sheet.createRow(rowIndex);
	    row.createCell(0).setCellValue(computedRow.getQuantity());
	    row.createCell(1).setCellValue(computedRow.getName());
	    row.createCell(2).setCellValue(estimate.getSteelBarLength());
	    row.createCell(3).setCellValue(estimate.getSteelBarsQuantity());
	    row.createCell(4).setCellValue(estimate.getCostSteelBars());
	    rowIndex++;
	}
    }

    private void constructSheetMRCHB(HSSFWorkbook wb, EstimationOutput output) {
	// For headers.
	HSSFSheet sheet = wb.createSheet("Metal Reinforcement (CHB)");
	int rowIndex = 0;

	// Headers.
	HSSFRow row = sheet.createRow(rowIndex);
	HSSFCell cellQuantity = row.createCell(3);
	HSSFCell cellCost = row.createCell(6);
	CellUtil.setAlignment(cellQuantity, wb, CellStyle.ALIGN_CENTER);
	CellUtil.setAlignment(cellCost, wb, CellStyle.ALIGN_CENTER);
	cellQuantity.setCellValue("Quantity");
{'fraction_non_alphanumeric': 0.0879651676984829, 'fraction_numerical': 0.00768759779576842, 'mean_word_length': 5.65158371040724, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '262272', 'n_tokens_mistral': 5330, 'n_tokens_neox': 5133, 'n_words': 885}