package com.reallinkbilling.download;

import static java.awt.Color.BLACK;
import static java.awt.Color.WHITE;
import static org.apache.pdfbox.pdmodel.font.PDType1Font.HELVETICA;
import static org.apache.pdfbox.pdmodel.font.PDType1Font.HELVETICA_BOLD;
import static org.apache.pdfbox.pdmodel.font.PDType1Font.HELVETICA_OBLIQUE;
import static org.vandeseer.easytable.settings.HorizontalAlignment.CENTER;
import static org.vandeseer.easytable.settings.HorizontalAlignment.LEFT;
import static org.vandeseer.easytable.settings.HorizontalAlignment.RIGHT;

import java.awt.Color;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.pdfbox.pdmodel.PDDocument;
import org.apache.pdfbox.pdmodel.PDPage;
import org.apache.pdfbox.pdmodel.PDPageContentStream;
import org.apache.pdfbox.pdmodel.common.PDRectangle;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;

import org.apache.struts2.ServletActionContext;
import org.apache.struts2.dispatcher.SessionMap;
import org.apache.struts2.interceptor.SessionAware;

import org.vandeseer.easytable.TableDrawer;
import org.vandeseer.easytable.structure.Row;
import org.vandeseer.easytable.structure.Table;
import org.vandeseer.easytable.structure.Table.TableBuilder;
import org.vandeseer.easytable.structure.cell.TextCell;

import com.opensymphony.xwork2.ActionSupport;
import com.reallinkbilling.DAO.stockTransferDetailsDAO;
import com.reallinkbilling.db.GetDBConnection;
import com.reallinkbilling.resource.GetListResource;
import com.reallinkbilling.utils.GetGodownDetailsById;


public class DownloadWarehouseStockTransferReport
		extends ActionSupport implements SessionAware {

	private static final long serialVersionUID = 9126248917004948090L;

	private SessionMap<String, Object> sessionMap;
	private HttpSession session;

	private List<stockTransferDetailsDAO> productStockTransferList =
			new ArrayList<stockTransferDetailsDAO>();

	private Map<String, String> fileTypeList =
			new HashMap<String, String>();

	private String defaultfileType;

	private Map<String, String> warehouseList =
			new HashMap<String, String>();

	private String defaultwarehouse;
	
	private String defaultstockType;

	private String transferDate;

	private HttpServletRequest request;
	private HttpServletResponse response;

	private String addSideBarJs;


	// ============================================================
	// PROFESSIONAL PDF STYLING
	// ============================================================

	private static final Color HEADER_BG =
			new Color(248, 249, 250);

	private static final Color BORDER_COLOR =
			new Color(169, 169, 169);

	private static final Color ACCENT_LINE =
			new Color(108, 117, 125);

	private static final float PADDING_5 = 5f;
	private static final float PADDING_30 = 30f;

	private static final int TITLE_FONT_SIZE = 18;
	private static final int SUBTITLE_FONT_SIZE = 14;
	private static final int HEADER_FONT_SIZE = 12;
	private static final int BODY_FONT_SIZE = 10;
	private static final int SMALL_FONT_SIZE = 8;


	// ============================================================
	// DOWNLOAD
	// ============================================================

	public void download() {

		session = ServletActionContext
				.getRequest()
				.getSession(false);

		if (session == null ||
				session.getAttribute("login") == null) {

			addActionMessage("Please Login First !!!");
			return;
		}

		response = ServletActionContext.getResponse();
		request = ServletActionContext.getRequest();

		Date d = new Date();

		SimpleDateFormat dateFormat =
				new SimpleDateFormat("dd-MM-yyyy");

		SimpleDateFormat timeFormat =
				new SimpleDateFormat("HH:mm:ss");

		this.setTransferDate(
				dateFormat.format(d));

		String currentTime =
				timeFormat.format(d);


		// ========================================================
		// REALLINK / EASYSBILL SESSION VALUES
		// ========================================================

		String shopId =
				(String) this.getSessionMap()
						.get("shopId");

		String shopCode =
				(String) this.getSessionMap()
						.get("shopCode");

		String db =
				(String) this.getSessionMap()
						.get("db");


		// ========================================================
		// FILE TYPE LIST
		// ========================================================

		fileTypeList.put("pdf", "PDF");

		
		fileTypeList.put("xls", "Excel");


		// ========================================================
		// GODOWN LIST
		// ========================================================

		warehouseList.putAll(
				GetListResource.getGodownList(
						sessionMap));

		warehouseList.put(
				"All",
				"All Warehouses");


		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		Map<String, stockTransferDetailsDAO> productMap =
				new HashMap<String, stockTransferDetailsDAO>();

		String warehouseName = "";


		try {

			connection =
					GetDBConnection.getDBConnection(db);


			// ====================================================
			// ALL WAREHOUSES
			// ====================================================

			if ("All".equals(
					this.getDefaultwarehouse())) {

				warehouseName =
						"All Warehouses";

				String sql = 
				        "SELECT product_id, product_name, " 
				        + "SUM(in_stock_quantity) AS in_stock_quantity " 
				        + "FROM warehouse_stock_master " 
				        + "WHERE shop_id = ? " 
				        + "AND shop_code = ? " 
				        + "GROUP BY product_id, product_name ";

				if ("AVAILABLE STOCK".equals(defaultstockType)) {
				    sql += "HAVING SUM(in_stock_quantity) > 0 ";
				}

				sql += "ORDER BY product_name";

				preparedStatement =
						connection.prepareStatement(sql);

				preparedStatement.setString(
						1,
						shopId);

				preparedStatement.setString(
						2,
						shopCode);
			}


			// ====================================================
			// SPECIFIC GODOWN
			// ====================================================

			else {

				HashMap<String, String> goDownDetails =
						GetGodownDetailsById.getGodownDetails(
								this.getDefaultwarehouse(),
								this.getSessionMap(),
								connection);

				if (goDownDetails != null) {

					warehouseName =
							goDownDetails.get("GODOWNNAME");
				}

				if (warehouseName == null ||
						warehouseName.trim().equals("")) {

					warehouseName =
							"Selected Warehouse";
				}


				/*
				 * defaultwarehouse normally contains:
				 *
				 * godownId-godownName
				 *
				 * Example:
				 *
				 * 1-Main Godown
				 *
				 * Therefore only the first part is used.
				 */

				String warehouseId =
						this.getDefaultwarehouse()
								.split("-")[0];


				String sql = 
				        "SELECT product_id, product_name, " 
				        + "in_stock_quantity " 
				        + "FROM warehouse_stock_master " 
				        + "WHERE shop_id = ? " 
				        + "AND shop_code = ? " 
				        + "AND warehouse_id = ? ";

				if ("AVAILABLE STOCK".equals(defaultstockType)) {
				    sql += "AND in_stock_quantity > 0 ";
				}

				sql += "ORDER BY product_name";

				preparedStatement =
						connection.prepareStatement(sql);

				preparedStatement.setString(
						1,
						shopId);

				preparedStatement.setString(
						2,
						shopCode);

				preparedStatement.setString(
						3,
						warehouseId);
			}


			// ====================================================
			// EXECUTE QUERY
			// ====================================================

			resultSet =
					preparedStatement.executeQuery();


			// ====================================================
			// BUILD PRODUCT MAP
			// ====================================================

			while (resultSet.next()) {

				String productId =
						resultSet.getString(
								"product_id");

				String productName =
						resultSet.getString(
								"product_name");

				/*
				 * DECIMAL(12,2)
				 */
				double quantity =
						resultSet.getDouble(
								"in_stock_quantity");


				stockTransferDetailsDAO dao =
						productMap.getOrDefault(
								productId,
								new stockTransferDetailsDAO());


				dao.setProductName(
						productName);


				dao.setInStockquantity(
						dao.getInStockquantity()
								+ quantity);


				productMap.put(
						productId,
						dao);
			}


			// ====================================================
			// CONVERT MAP TO LIST
			// ====================================================

			productStockTransferList.clear();

			for (stockTransferDetailsDAO dao :
					productMap.values()) {

				productStockTransferList.add(dao);
			}


			// ====================================================
			// NO STOCK
			// ====================================================

			if (productStockTransferList.isEmpty()) {

				addActionError(
						"No Stock Details Found!");

				return;
			}


			// ====================================================
			// DOWNLOAD PDF
			// ====================================================

			if ("pdf".equalsIgnoreCase(
					this.getDefaultfileType())) {

				generateProfessionalPDF(
						warehouseName,
						currentTime);
			}


			// ====================================================
			// DOWNLOAD EXCEL - XLS
			// ====================================================

			else if ("xls".equalsIgnoreCase(
					this.getDefaultfileType())) {

				generateProfessionalExcel(
						warehouseName,
						currentTime);
			}

		}

		catch (SQLException e) {

			e.printStackTrace();

			addActionError(
					"Database Error: "
					+ e.getMessage());
		}

		catch (Exception e) {

			e.printStackTrace();

			addActionError(
					"System Error: "
					+ e.getMessage());
		}

		finally {

			closeResources(
					resultSet,
					preparedStatement,
					connection);
		}
	}


	// ============================================================
	// PDF
	// ============================================================

	private void generateProfessionalPDF(
			String warehouseName,
			String currentTime) throws Exception {


		String companyName =
				(String) this.getSessionMap()
						.get("shopnameenglish");


		String companyAddress =
				(String) this.getSessionMap()
						.get("shopaddressenglish");


		if (companyName == null) {
			companyName = "";
		}

		if (companyAddress == null) {
			companyAddress = "";
		}


		String path =
				this.getRequest()
						.getSession()
						.getServletContext()
						.getRealPath("/images/");


		PDDocument document =
				new PDDocument();


		PDPage page =
				new PDPage(
						PDRectangle.A4);


		document.addPage(page);


		PDPageContentStream contentStream =
				new PDPageContentStream(
						document,
						page);


		createProfessionalHeader(
				contentStream,
				page,
				companyName,
				companyAddress,
				warehouseName,
				currentTime);


		createProfessionalDataTable(
				document,
				contentStream,
				page,
				companyName,
				companyAddress,
				warehouseName,
				currentTime);


		/*
		 * The data-table method can create additional
		 * pages and content streams.
		 *
		 * The original stream is closed here only
		 * when still active.
		 */
		try {
			contentStream.close();
		}
		catch (Exception e) {
			// Ignore
		}


		String fileName =
				"warehouse-stock-report-"
				+ warehouseName
						.replaceAll("\\s+", "-")
				+ "-"
				+ this.getTransferDate()
				+ ".pdf";


		document.save(
				path + "/" + fileName);


		document.close();


		// ========================================================
		// SEND PDF
		// ========================================================

		response.reset();


		response.setHeader(
				"Content-Disposition",
				"inline; filename=\""
				+ fileName
				+ "\"");


		response.setContentType(
				"application/pdf");


		try (
				FileInputStream fileInputStream =
						new FileInputStream(
								path + "/" + fileName);

				ServletOutputStream outputStream =
						response.getOutputStream()
		) {

			byte[] buffer =
					new byte[8192];

			int bytesRead;

			while (
					(bytesRead =
							fileInputStream.read(buffer))
							!= -1) {

				outputStream.write(
						buffer,
						0,
						bytesRead);
			}

			outputStream.flush();
		}
	}


	// ============================================================
	// PDF HEADER
	// ============================================================

	private void createProfessionalHeader(
			PDPageContentStream contentStream,
			PDPage page,
			String companyName,
			String companyAddress,
			String warehouseName,
			String currentTime) throws Exception {


		float pageWidth =
				page.getMediaBox()
						.getWidth();


		float startY =
				page.getMediaBox()
						.getHeight()
						- PADDING_30;


		if (companyName == null) {
			companyName = "";
		}


		if (companyAddress == null) {
			companyAddress = "";
		}


		Table header =
				Table.builder()

						.addColumnsOfWidth(
								pageWidth
								- 2 * PADDING_30)

						.font(HELVETICA)

						.borderWidth(0)


						// COMPANY NAME
						.addRow(
								Row.builder()

										.add(
												TextCell.builder()
														.text(
																companyName
																		.toUpperCase())
														.horizontalAlignment(
																CENTER)
														.borderWidth(0)
														.build())

										.backgroundColor(
												WHITE)

										.textColor(
												BLACK)

										.font(
												HELVETICA_BOLD)

										.fontSize(
												TITLE_FONT_SIZE)

										.build())


						// ADDRESS
						.addRow(
								Row.builder()

										.add(
												TextCell.builder()
														.text(
																companyAddress)
														.horizontalAlignment(
																CENTER)
														.borderWidth(0)
														.build())

										.backgroundColor(
												WHITE)

										.textColor(
												BLACK)

										.font(
												HELVETICA_OBLIQUE)

										.fontSize(
												BODY_FONT_SIZE)

										.build())


						// LINE
						.addRow(
								Row.builder()

										.add(
												TextCell.builder()
														.text(
																"------------------------------------------------------------")
														.horizontalAlignment(
																CENTER)
														.borderWidth(0)
														.build())

										.backgroundColor(
												WHITE)

										.textColor(
												ACCENT_LINE)

										.font(
												HELVETICA)

										.fontSize(
												SMALL_FONT_SIZE)

										.build())


						// TITLE
						.addRow(
								Row.builder()

										.add(
												TextCell.builder()
														.text(
																"WAREHOUSE STOCK REPORT")
														.horizontalAlignment(
																CENTER)
														.borderWidth(0)
														.build())

										.backgroundColor(
												WHITE)

										.textColor(
												BLACK)

										.font(
												HELVETICA_BOLD)

										.fontSize(
												SUBTITLE_FONT_SIZE)

										.build())


						// DETAILS
						.addRow(
								Row.builder()

										.add(
												TextCell.builder()
														.text(
																String.format(
																		"Warehouse: %s  |  Date: %s  |  Time: %s",
																		warehouseName,
																		this.getTransferDate(),
																		currentTime))
														.horizontalAlignment(
																CENTER)
														.borderWidth(0)
														.build())

										.backgroundColor(
												WHITE)

										.textColor(
												BLACK)

										.font(
												HELVETICA)

										.fontSize(
												BODY_FONT_SIZE)

										.build())


						// LINE
						.addRow(
								Row.builder()

										.add(
												TextCell.builder()
														.text(
																"------------------------------------------------------------")
														.horizontalAlignment(
																CENTER)
														.borderWidth(0)
														.build())

										.backgroundColor(
												WHITE)

										.textColor(
												ACCENT_LINE)

										.font(
												HELVETICA)

										.fontSize(
												SMALL_FONT_SIZE)

										.build())

						.build();


		TableDrawer.builder()

				.contentStream(
						contentStream)

				.table(
						header)

				.startX(
						PADDING_30)

				.startY(
						startY)

				.build()

				.draw();
	}


	// ============================================================
	// PDF TABLE
	// ============================================================

	private void createProfessionalDataTable(
			PDDocument document,
			PDPageContentStream contentStream,
			PDPage page,
			String companyName,
			String companyAddress,
			String warehouseName,
			String currentTime) throws Exception {


		float pageWidth =
				page.getMediaBox()
						.getWidth();


		float startY =
				page.getMediaBox()
						.getHeight()
						- PADDING_30;


		float tableStartY =
				startY - 130;


		float currentY =
				tableStartY - 20;


		TableBuilder tableBuilder =
				createStockTableBuilder(
						pageWidth);


		Row tableHeader =
				createStockTableHeader();


		tableBuilder.addRow(
				tableHeader);


		int srNo = 1;

		double totalQuantity = 0;


		for (stockTransferDetailsDAO detailsDAO :
				this.getProductStockTransferList()) {


			float rowHeight =
					estimateStockTransferRowHeight(
							detailsDAO);


			if (currentY - rowHeight < 50) {


				Table currentTable =
						tableBuilder.build();


				TableDrawer.builder()

						.contentStream(
								contentStream)

						.table(
								currentTable)

						.startX(
								PADDING_30)

						.startY(
								tableStartY)

						.build()

						.draw();


				contentStream.close();


				PDPage newPage =
						new PDPage(
								PDRectangle.A4);


				document.addPage(
						newPage);


				contentStream =
						new PDPageContentStream(
								document,
								newPage);


				createProfessionalHeader(
						contentStream,
						newPage,
						companyName,
						companyAddress,
						warehouseName,
						currentTime);


				tableBuilder =
						createStockTableBuilder(
								pageWidth);


				tableBuilder.addRow(
						tableHeader);


				startY =
						newPage.getMediaBox()
								.getHeight()
								- PADDING_30;


				tableStartY =
						startY - 130;


				currentY =
						tableStartY - 20;


				page = newPage;
			}


			Color rowBG =
					(srNo % 2 == 0)
							? new Color(
									250,
									250,
									250)
							: WHITE;


			Row dataRow =
					Row.builder()


							.add(
									TextCell.builder()
											.text(
													String.valueOf(
															srNo))
											.horizontalAlignment(
													CENTER)
											.borderWidth(
													0.5f)
											.build())


							.add(
									TextCell.builder()
											.text(
													detailsDAO
															.getProductName())
											.horizontalAlignment(
													LEFT)
											.borderWidth(
													0.5f)
											.build())


							.add(
									TextCell.builder()
											.text(
													String.format(
															"%,.2f",
															detailsDAO
																	.getInStockquantity()))
											.horizontalAlignment(
													RIGHT)
											.borderWidth(
													0.5f)
											.build())


							.add(
									TextCell.builder()
											.text("")
											.horizontalAlignment(
													CENTER)
											.borderWidth(
													0.5f)
											.build())


							.backgroundColor(
									rowBG)

							.textColor(
									BLACK)

							.font(
									HELVETICA)

							.fontSize(
									BODY_FONT_SIZE)

							.build();


			tableBuilder.addRow(
					dataRow);


			currentY -= rowHeight;


			totalQuantity +=
					detailsDAO
							.getInStockquantity();


			srNo++;
		}


		// ========================================================
		// TOTAL ROW
		// ========================================================

		Row totalRow =
				Row.builder()


						.add(
								TextCell.builder()
										.text("")
										.horizontalAlignment(
												CENTER)
										.borderWidth(
												0.5f)
										.build())


						.add(
								TextCell.builder()
										.text(
												"TOTAL")
										.horizontalAlignment(
												RIGHT)
										.borderWidth(
												0.5f)
										.build())


						.add(
								TextCell.builder()
										.text(
												String.format(
														"%,.2f",
														totalQuantity))
										.horizontalAlignment(
												RIGHT)
										.borderWidth(
												0.5f)
										.build())


						.add(
								TextCell.builder()
										.text("")
										.horizontalAlignment(
												CENTER)
										.borderWidth(
												0.5f)
										.build())


						.backgroundColor(
								HEADER_BG)

						.textColor(
								BLACK)

						.font(
								HELVETICA_BOLD)

						.fontSize(
								HEADER_FONT_SIZE)

						.build();


		tableBuilder.addRow(
				totalRow);


		Table finalTable =
				tableBuilder.build();


		TableDrawer.builder()

				.contentStream(
						contentStream)

				.table(
						finalTable)

				.startX(
						PADDING_30)

				.startY(
						tableStartY)

				.build()

				.draw();


		addProfessionalFooter(
				contentStream,
				page,
				srNo - 1,
				totalQuantity);
	}


	// ============================================================
	// STOCK TABLE BUILDER
	// ============================================================

	private TableBuilder createStockTableBuilder(
			float pageWidth) {

		return Table.builder()

				.addColumnsOfWidth(
						60,
						pageWidth
								- 2 * PADDING_30
								- 300,
						100,
						100)

				.font(
						HELVETICA)

				.borderWidth(
						0.5f)

				.borderColor(
						BORDER_COLOR)

				.horizontalAlignment(
						LEFT);
	}


	// ============================================================
	// STOCK TABLE HEADER
	// ============================================================

	private Row createStockTableHeader() {

		return Row.builder()


				.add(
						TextCell.builder()
								.text(
										"Sr. No.")
								.horizontalAlignment(
										CENTER)
								.borderWidth(
										0.5f)
								.build())


				.add(
						TextCell.builder()
								.text(
										"Product Name")
								.horizontalAlignment(
										LEFT)
								.borderWidth(
										0.5f)
								.build())


				.add(
						TextCell.builder()
								.text(
										"Stock Quantity")
								.horizontalAlignment(
										RIGHT)
								.borderWidth(
										0.5f)
								.build())


				.add(
						TextCell.builder()
								.text(
										"Signature")
								.horizontalAlignment(
										CENTER)
								.borderWidth(
										0.5f)
								.build())


				.backgroundColor(
						HEADER_BG)

				.textColor(
						BLACK)

				.font(
						HELVETICA_BOLD)

				.fontSize(
						HEADER_FONT_SIZE)

				.build();
	}


	// ============================================================
	// PDF FOOTER
	// ============================================================

	private void addProfessionalFooter(
			PDPageContentStream contentStream,
			PDPage page,
			int totalProducts,
			double totalQuantity) throws Exception {


		float pageWidth =
				page.getMediaBox()
						.getWidth();


		String summaryText =
				String.format(
						"Summary: %d Products | Total Stock: %,.2f Units | Generated: %s",
						totalProducts,
						totalQuantity,
						new SimpleDateFormat(
								"dd-MM-yyyy HH:mm")
								.format(
										new Date()));


		Row summaryRow =
				Row.builder()

						.add(
								TextCell.builder()
										.text(
												summaryText)
										.horizontalAlignment(
												CENTER)
										.borderWidth(
												0)
										.build())

						.backgroundColor(
								WHITE)

						.textColor(
								BLACK)

						.font(
								HELVETICA_OBLIQUE)

						.fontSize(
								SMALL_FONT_SIZE)

						.build();


		Table footerTable =
				Table.builder()

						.addColumnsOfWidth(
								pageWidth
								- 2 * PADDING_30)

						.font(
								HELVETICA)

						.borderWidth(
								0)

						.addRow(
								summaryRow)

						.build();


		TableDrawer.builder()

				.contentStream(
						contentStream)

				.table(
						footerTable)

				.startX(
						PADDING_30)

				.startY(
						PADDING_30)

				.build()

				.draw();
	}


	// ============================================================
	// EXCEL - HSSF XLS
	// ============================================================

	// ============================================================
	// EXCEL - XLS
	// ============================================================

	private void generateProfessionalExcel(
	        String warehouseName,
	        String currentTime) throws Exception {

	    String fileName =
	            "warehouse-stock-report-"
	            + warehouseName
	                    .replaceAll("\\s+", "-")
	            + "-"
	            + this.getTransferDate()
	            + "-"
	            + currentTime
	                    .replace(":", "-")
	            + ".xls";


	    HSSFWorkbook workbook =
	            new HSSFWorkbook();

	    try {

	        HSSFSheet sheet =
	                workbook.createSheet(
	                        "Warehouse Stock Report");


	        // ====================================================
	        // COLUMN WIDTHS
	        // ====================================================

	        sheet.setColumnWidth(
	                0,
	                15 * 256);

	        sheet.setColumnWidth(
	                1,
	                40 * 256);

	        sheet.setColumnWidth(
	                2,
	                20 * 256);


	        // ====================================================
	        // STYLES
	        // ====================================================

	        HSSFCellStyle titleStyle =
	                createExcelTitleStyle(workbook);

	        HSSFCellStyle headerStyle =
	                createExcelHeaderStyle(workbook);

	        HSSFCellStyle dataStyle =
	                createExcelDataStyle(workbook);

	        HSSFCellStyle quantityStyle =
	                createExcelQuantityStyle(workbook);

	        HSSFCellStyle totalStyle =
	                createExcelTotalStyle(workbook);


	        int rowNum = 0;


	        // ====================================================
	        // TITLE
	        // ====================================================

	        HSSFRow titleRow =
	                sheet.createRow(rowNum++);

	        titleRow.setHeightInPoints(25);

	        Cell titleCell =
	                titleRow.createCell(0);

	        titleCell.setCellValue(
	                "WAREHOUSE STOCK REPORT");

	        titleCell.setCellStyle(
	                titleStyle);


	        sheet.addMergedRegion(
	                new org.apache.poi.ss.util.CellRangeAddress(
	                        0,
	                        0,
	                        0,
	                        2));


	        // ====================================================
	        // DETAILS
	        // ====================================================

	        HSSFRow detailsRow =
	                sheet.createRow(rowNum++);

	        Cell detailsCell =
	                detailsRow.createCell(0);

	        detailsCell.setCellValue(
	                "Warehouse: "
	                + warehouseName
	                + " | Date: "
	                + this.getTransferDate()
	                + " | Time: "
	                + currentTime);

	        detailsCell.setCellStyle(
	                dataStyle);


	        sheet.addMergedRegion(
	                new org.apache.poi.ss.util.CellRangeAddress(
	                        1,
	                        1,
	                        0,
	                        2));


	        // ====================================================
	        // EMPTY ROW
	        // ====================================================

	        rowNum++;


	        // ====================================================
	        // HEADER
	        // ====================================================

	        HSSFRow headerRow =
	                sheet.createRow(rowNum++);

	        headerRow.setHeightInPoints(22);


	        Cell headerCell1 =
	                headerRow.createCell(0);

	        headerCell1.setCellValue(
	                "Sr. No.");

	        headerCell1.setCellStyle(
	                headerStyle);


	        Cell headerCell2 =
	                headerRow.createCell(1);

	        headerCell2.setCellValue(
	                "Product Name");

	        headerCell2.setCellStyle(
	                headerStyle);


	        Cell headerCell3 =
	                headerRow.createCell(2);

	        headerCell3.setCellValue(
	                "Stock Quantity");

	        headerCell3.setCellStyle(
	                headerStyle);


	        // ====================================================
	        // DATA
	        // ====================================================

	        int srNo = 1;

	        double totalQuantity = 0.0;


	        for (stockTransferDetailsDAO dao :
	                this.getProductStockTransferList()) {


	            HSSFRow dataRow =
	                    sheet.createRow(rowNum++);


	            // Sr. No.
	            Cell cell1 =
	                    dataRow.createCell(0);

	            cell1.setCellValue(srNo);

	            cell1.setCellStyle(dataStyle);


	            // Product Name
	            Cell cell2 =
	                    dataRow.createCell(1);

	            cell2.setCellValue(
	                    dao.getProductName());

	            cell2.setCellStyle(dataStyle);


	            // Stock Quantity
	            Cell cell3 =
	                    dataRow.createCell(2);

	            double quantity =
	                    dao.getInStockquantity();

	            cell3.setCellValue(quantity);

	            cell3.setCellStyle(quantityStyle);


	            totalQuantity += quantity;

	            srNo++;
	        }


	        // ====================================================
	        // TOTAL ROW
	        // ====================================================

	        HSSFRow totalRow =
	                sheet.createRow(rowNum++);

	        totalRow.setHeightInPoints(22);


	        Cell totalCell1 =
	                totalRow.createCell(0);

	        totalCell1.setCellValue("");

	        totalCell1.setCellStyle(
	                totalStyle);


	        Cell totalCell2 =
	                totalRow.createCell(1);

	        totalCell2.setCellValue(
	                "TOTAL");

	        totalCell2.setCellStyle(
	                totalStyle);


	        Cell totalCell3 =
	                totalRow.createCell(2);

	        totalCell3.setCellValue(
	                totalQuantity);

	        totalCell3.setCellStyle(
	                totalStyle);


	        // ====================================================
	        // WRITE XLS
	        // ====================================================

	        try (
	                FileOutputStream fileOut =
	                        new FileOutputStream(fileName)
	        ) {

	            workbook.write(fileOut);
	        }

	    } finally {

	        workbook.close();
	    }


	    // ========================================================
	    // DOWNLOAD XLS
	    // ========================================================

	    response.reset();

	    response.setHeader(
	            "Content-Disposition",
	            "attachment; filename=\""
	            + fileName
	            + "\"");

	    response.setContentType(
	            "application/vnd.ms-excel");


	    try (
	            FileInputStream fileInputStream =
	                    new FileInputStream(fileName);

	            ServletOutputStream outputStream =
	                    response.getOutputStream()
	    ) {

	        byte[] buffer =
	                new byte[8192];

	        int bytesRead;

	        while (
	                (bytesRead =
	                        fileInputStream.read(buffer))
	                        != -1) {

	            outputStream.write(
	                    buffer,
	                    0,
	                    bytesRead);
	        }

	        outputStream.flush();
	    }
	}


	// ============================================================
	// EXCEL TITLE STYLE
	// ============================================================

	private HSSFCellStyle createExcelTitleStyle(
			HSSFWorkbook workbook) {


		HSSFCellStyle style =
				workbook.createCellStyle();


		Font font =
				workbook.createFont();


		font.setBold(
				true);


		font.setFontHeightInPoints(
				(short) 16);


		style.setFont(
				font);


		style.setBorderBottom(
				BorderStyle.THICK);


		style.setBorderRight(
				BorderStyle.THIN);


		style.setBorderLeft(
				BorderStyle.THIN);


		style.setBorderTop(
				BorderStyle.THIN);


		style.setAlignment(
				org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER);


		style.setVerticalAlignment(
				org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);


		style.setWrapText(
				true);


		return style;
	}


	// ============================================================
	// EXCEL HEADER STYLE
	// ============================================================

	private HSSFCellStyle createExcelHeaderStyle(
			HSSFWorkbook workbook) {


		HSSFCellStyle style =
				workbook.createCellStyle();


		Font font =
				workbook.createFont();


		font.setBold(
				true);


		font.setFontHeightInPoints(
				(short) 12);


		style.setFont(
				font);


		style.setBorderBottom(
				BorderStyle.THIN);


		style.setBorderRight(
				BorderStyle.THIN);


		style.setBorderLeft(
				BorderStyle.THIN);


		style.setBorderTop(
				BorderStyle.THIN);


		style.setAlignment(
				org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER);


		style.setVerticalAlignment(
				org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);


		style.setWrapText(
				true);


		return style;
	}


	// ============================================================
	// EXCEL DATA STYLE
	// ============================================================

	private HSSFCellStyle createExcelDataStyle(
			HSSFWorkbook workbook) {


		HSSFCellStyle style =
				workbook.createCellStyle();


		Font font =
				workbook.createFont();


		font.setFontHeightInPoints(
				(short) 10);


		style.setFont(
				font);


		style.setBorderBottom(
				BorderStyle.THIN);


		style.setBorderRight(
				BorderStyle.THIN);


		style.setBorderLeft(
				BorderStyle.THIN);


		style.setBorderTop(
				BorderStyle.THIN);


		style.setVerticalAlignment(
				org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);


		style.setWrapText(
				true);


		return style;
	}


	// ============================================================
	// EXCEL QUANTITY STYLE
	// ============================================================

	private HSSFCellStyle createExcelQuantityStyle(
			HSSFWorkbook workbook) {


		HSSFCellStyle style =
				workbook.createCellStyle();


		Font font =
				workbook.createFont();


		font.setFontHeightInPoints(
				(short) 10);


		style.setFont(
				font);


		style.setBorderBottom(
				BorderStyle.THIN);


		style.setBorderRight(
				BorderStyle.THIN);


		style.setBorderLeft(
				BorderStyle.THIN);


		style.setBorderTop(
				BorderStyle.THIN);


		style.setAlignment(
				org.apache.poi.ss.usermodel.HorizontalAlignment.RIGHT);


		style.setVerticalAlignment(
				org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);


		/*
		 * Display:
		 *
		 * 10.00
		 * 125.50
		 * 1,250.75
		 */
		style.setDataFormat(
				workbook.createDataFormat()
						.getFormat("#,##0.00"));


		return style;
	}


	// ============================================================
	// EXCEL TOTAL STYLE
	// ============================================================

	private HSSFCellStyle createExcelTotalStyle(
			HSSFWorkbook workbook) {


		HSSFCellStyle style =
				workbook.createCellStyle();


		Font font =
				workbook.createFont();


		font.setBold(
				true);


		font.setFontHeightInPoints(
				(short) 11);


		style.setFont(
				font);


		style.setBorderBottom(
				BorderStyle.THICK);


		style.setBorderRight(
				BorderStyle.THIN);


		style.setBorderLeft(
				BorderStyle.THIN);


		style.setBorderTop(
				BorderStyle.THIN);


		style.setAlignment(
				org.apache.poi.ss.usermodel.HorizontalAlignment.RIGHT);


		style.setVerticalAlignment(
				org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);


		style.setDataFormat(
				workbook.createDataFormat()
						.getFormat("#,##0.00"));


		return style;
	}


	// ============================================================
	// CLOSE RESOURCES
	// ============================================================

	private void closeResources(
			ResultSet rs,
			PreparedStatement ps,
			Connection conn) {


		try {

			if (rs != null) {
				rs.close();
			}

		}
		catch (SQLException e) {
			// Ignore
		}


		try {

			if (ps != null) {
				ps.close();
			}

		}
		catch (SQLException e) {
			// Ignore
		}


		try {

			if (conn != null) {
				conn.close();
			}

		}
		catch (SQLException e) {
			// Ignore
		}
	}


	// ============================================================
	// VALIDATE
	// ============================================================

	@Override
	public void validate() {


		if (this.getDefaultwarehouse() == null
				|| this.getDefaultwarehouse()
						.trim()
						.equals("")) {


			addActionError(
					"Please Provide Warehouse Name !");
		}


		if (this.getDefaultfileType() == null
				|| this.getDefaultfileType()
						.trim()
						.equals("")) {


			addActionError(
					"Please Provide Download File Type !");
		}
	}


	// ============================================================
	// SESSION
	// ============================================================

	@Override
	@SuppressWarnings("unchecked")
	public void setSession(
			Map<String, Object> map) {


		sessionMap =
				(SessionMap<String, Object>) map;
	}


	public SessionMap<String, Object> getSessionMap() {

		return sessionMap;
	}


	public void setSessionMap(
			SessionMap<String, Object> sessionMap) {

		this.sessionMap =
				sessionMap;
	}


	// ============================================================
	// REQUEST
	// ============================================================

	public HttpServletRequest getRequest() {

		return request;
	}


	public void setRequest(
			HttpServletRequest request) {

		this.request =
				request;
	}


	// ============================================================
	// RESPONSE
	// ============================================================

	public HttpServletResponse getResponse() {

		return response;
	}


	public void setResponse(
			HttpServletResponse response) {

		this.response =
				response;
	}


	// ============================================================
	// HTTP SESSION
	// ============================================================

	public HttpSession getSession() {

		return session;
	}


	public void setSession(
			HttpSession session) {

		this.session =
				session;
	}


	// ============================================================
	// SIDEBAR
	// ============================================================

	public String getAddSideBarJs() {

		return addSideBarJs;
	}


	public void setAddSideBarJs(
			String addSideBarJs) {

		this.addSideBarJs =
				addSideBarJs;
	}


	// ============================================================
	// PRODUCT LIST
	// ============================================================

	public List<stockTransferDetailsDAO>
			getProductStockTransferList() {

		return productStockTransferList;
	}


	public void setProductStockTransferList(
			List<stockTransferDetailsDAO>
					productStockTransferList) {

		this.productStockTransferList =
				productStockTransferList;
	}


	// ============================================================
	// FILE TYPE
	// ============================================================

	public Map<String, String>
			getFileTypeList() {

		return fileTypeList;
	}


	public void setFileTypeList(
			Map<String, String> fileTypeList) {

		this.fileTypeList =
				fileTypeList;
	}


	public String getDefaultfileType() {

		return defaultfileType;
	}


	public void setDefaultfileType(
			String defaultfileType) {

		this.defaultfileType =
				defaultfileType;
	}


	// ============================================================
	// WAREHOUSE
	// ============================================================

	public Map<String, String>
			getWarehouseList() {

		return warehouseList;
	}


	public void setWarehouseList(
			Map<String, String> warehouseList) {

		this.warehouseList =
				warehouseList;
	}

	

	public String getDefaultstockType() {
		return defaultstockType;
	}


	public void setDefaultstockType(String defaultstockType) {
		this.defaultstockType = defaultstockType;
	}


	public String getDefaultwarehouse() {

		return defaultwarehouse;
	}


	public void setDefaultwarehouse(
			String defaultwarehouse) {

		this.defaultwarehouse =
				defaultwarehouse;
	}


	// ============================================================
	// DATE
	// ============================================================

	public String getTransferDate() {

		return transferDate;
	}


	public void setTransferDate(
			String transferDate) {

		this.transferDate =
				transferDate;
	}


	// ============================================================
	// ROW HEIGHT
	// ============================================================

	private float estimateStockTransferRowHeight(
			stockTransferDetailsDAO dao) {


		String productName =
				(dao == null
						|| dao.getProductName() == null)
								? ""
								: String.valueOf(
										dao.getProductName())
										.replaceAll(
												"\\s+",
												" ")
										.trim();


		int lines =
				Math.max(
						1,
						(int) Math.ceil(
								productName.length()
										/ 40.0));


		return Math.max(
				20f,
				lines * 11f);
	}

}