package com.reallinkbilling.download;

import static java.awt.Color.WHITE;
import static org.apache.pdfbox.pdmodel.font.PDType1Font.HELVETICA;
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.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

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.pdfbox.pdmodel.font.PDType1Font;
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.db.GetDBConnection;

public class DownloadSalesmanCommissionReport extends ActionSupport implements SessionAware {

	private static final long serialVersionUID = 778899001122334455L;

	private HttpSession session;

	public HttpServletRequest request;
	public HttpServletResponse response;

	private SessionMap<String, Object> sessionMap;

	private String fromDate;
	private String toDate;
	private String salesmanName;

	private static final float PADDING5 = 5f;
	private static final float PADDING20 = 20f;
	private static final float PADDING30 = 30f;

	public void download() {

		this.setSession(ServletActionContext.getRequest().getSession(false));

		if (this.getSession() == null
				|| this.getSession().getAttribute("login") == null) {

			addActionMessage("Please Login First !!!");
			return;
		}

		response = ServletActionContext.getResponse();
		request = ServletActionContext.getRequest();

		String shopId = (String) this.getSessionMap().get("shopId");
		String shopCode = (String) this.getSessionMap().get("shopCode");
		String db = (String) this.getSessionMap().get("db");

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {

			connection = GetDBConnection.getDBConnection(db);

			/*
			 * ============================================================
			 * SALESman COMMISSION QUERY
			 * ============================================================
			 */

			String sql =
			        "SELECT "
			        + "s.salesman_id, "
			        + "s.salesman_name, "
			        + "IFNULL(SUM(c.commission_amount),0) AS total_commission "
			        + "FROM shop_salesman_master s "
			        + "LEFT JOIN shop_salesman_commission c "
			        + "ON c.employee_id = s.salesman_id "
			        + "AND c.shop_id = s.shop_id "
			        + "AND c.shop_code = s.shop_code "
			        + "AND c.bill_date BETWEEN "
			        + "STR_TO_DATE(?,'%d-%m-%Y') "
			        + "AND STR_TO_DATE(?,'%d-%m-%Y') "
			        + "WHERE s.shop_id=? "
			        + "AND s.shop_code=? "
			        + "AND s.active='Y' ";

			if (salesmanName != null
			        && !salesmanName.trim().isEmpty()) {

			    sql += "AND s.salesman_name LIKE ? ";
			}

			sql +=
			        "GROUP BY s.salesman_id, s.salesman_name "
			        + "ORDER BY s.salesman_name";

			preparedStatement = connection.prepareStatement(sql);

			int parameterIndex = 1;

			preparedStatement.setString(parameterIndex++, this.getFromDate());
			preparedStatement.setString(parameterIndex++, this.getToDate());
			preparedStatement.setString(parameterIndex++, shopId);
			preparedStatement.setString(parameterIndex++, shopCode);

			if (salesmanName != null
			        && !salesmanName.trim().isEmpty()) {

			    preparedStatement.setString(
			            parameterIndex++,
			            "%" + salesmanName.trim() + "%");
			}

			resultSet = preparedStatement.executeQuery();

			/*
			 * ============================================================
			 * TOTAL COMMISSION
			 * ============================================================
			 */

			double totalCommission = 0.0;

			/*
			 * ============================================================
			 * PDF PATH
			 * ============================================================
			 */

			String path = request.getSession()
					.getServletContext()
					.getRealPath("/images/");

			/*
			 * ============================================================
			 * SHOP NAME
			 * ============================================================
			 */

			String shopName =
					(String) this.getSessionMap().get("shopnameenglish");

			if (shopName == null || shopName.trim().isEmpty()) {
				shopName = "";
			}

			/*
			 * ============================================================
			 * PDF DOCUMENT
			 * ============================================================
			 */

			PDDocument document = new PDDocument();

			PDPage page = new PDPage(PDRectangle.A4);

			document.addPage(page);

			PDPageContentStream contentStream =
					new PDPageContentStream(document, page);

			int pageCount = 1;

			/*
			 * ============================================================
			 * HEADER TABLE
			 * ============================================================
			 */

			TableBuilder tableHeaderBuilder = Table.builder()
					.addColumnsOfWidth(585)
					.font(HELVETICA)
					.borderColor(Color.BLACK)
					.horizontalAlignment(CENTER);

			Row headerName = Row.builder()
					.add(TextCell.builder()
							.text(shopName)
							.horizontalAlignment(CENTER)
							.borderWidth(0)
							.lineSpacing(10F)
							.build())
					.backgroundColor(WHITE)
					.textColor(Color.BLACK)
					.font(PDType1Font.HELVETICA_BOLD)
					.fontSize(18)
					.horizontalAlignment(CENTER)
					.build();

			Row info1 = Row.builder()
					.add(TextCell.builder()
							.text("Salesman Commission Report"
									+ "       Page No . : "
									+ pageCount)
							.borderWidthBottom(0.0F)
							.horizontalAlignment(CENTER)
							.build())
					.backgroundColor(WHITE)
					.textColor(Color.BLACK)
					.font(PDType1Font.HELVETICA_BOLD)
					.fontSize(10)
					.horizontalAlignment(CENTER)
					.build();

			Row info2 = Row.builder()
					.add(TextCell.builder()
							.text("From Date : "
									+ this.getFromDate()
									+ " and To Date : "
									+ this.getToDate())
							.borderWidthBottom(0.0F)
							.horizontalAlignment(CENTER)
							.build())
					.backgroundColor(WHITE)
					.textColor(Color.BLACK)
					.font(PDType1Font.HELVETICA_BOLD)
					.fontSize(8)
					.horizontalAlignment(CENTER)
					.build();

			tableHeaderBuilder.addRow(headerName);
			tableHeaderBuilder.addRow(info1);
			tableHeaderBuilder.addRow(info2);

			/*
			 * Add salesman filter information when search is used.
			 */
			if (salesmanName != null
					&& !salesmanName.trim().isEmpty()) {

				Row info3 = Row.builder()
						.add(TextCell.builder()
								.text("Salesman : "
										+ salesmanName.trim())
								.borderWidthBottom(0.0F)
								.horizontalAlignment(CENTER)
								.build())
						.backgroundColor(WHITE)
						.textColor(Color.BLACK)
						.font(PDType1Font.HELVETICA_BOLD)
						.fontSize(8)
						.horizontalAlignment(CENTER)
						.build();

				tableHeaderBuilder.addRow(info3);
			}

			Table header = tableHeaderBuilder.build();

			/*
			 * ============================================================
			 * COMMISSION TABLE
			 * ============================================================
			 */

			TableBuilder tableCommissionBuilder = Table.builder()
					.addColumnsOfWidth(430, 155)
					.font(HELVETICA)
					.borderWidth(0.5F)
					.borderColor(Color.BLACK)
					.horizontalAlignment(CENTER);

			Row tableHeader = Row.builder()

//					.add(TextCell.builder()
//							.text("ID")
//							.horizontalAlignment(LEFT)
//							.borderWidth(0.5F)
//							.lineSpacing(1F)
//							.build())

					.add(TextCell.builder()
							.text("Salesman Name")
							.horizontalAlignment(LEFT)
							.borderWidth(0.5F)
							.lineSpacing(1F)
							.build())

					.add(TextCell.builder()
							.text("Total Commission")
							.horizontalAlignment(RIGHT)
							.borderWidth(0.5F)
							.lineSpacing(1F)
							.build())

					.backgroundColor(Color.LIGHT_GRAY)
					.textColor(Color.BLACK)
					.font(PDType1Font.HELVETICA_BOLD)
					.fontSize(10)
					.horizontalAlignment(CENTER)
					.build();

			tableCommissionBuilder.addRow(tableHeader);

			/*
			 * ============================================================
			 * DRAW HEADER
			 * ============================================================
			 */

			float startY =
					page.getMediaBox().getHeight() - PADDING20;

			float currentY = startY;

			TableDrawer.builder()
					.contentStream(contentStream)
					.table(header)
					.startX(PADDING5)
					.startY(startY)
					.build()
					.draw();

			/*
			 * Header height approximately 70.
			 * Start salesman rows below header.
			 */
			currentY = startY - 80;

			/*
			 * ============================================================
			 * DATA ROWS
			 * ============================================================
			 */

			while (resultSet.next()) {

				int salesmanId =
						resultSet.getInt("salesman_id");

				String salesman =
						resultSet.getString("salesman_name");

				double commission =
						resultSet.getDouble("total_commission");

				totalCommission =
						totalCommission + commission;

				/*
				 * Create row.
				 */
				Row salesmanInfo = Row.builder()

//						.add(TextCell.builder()
//								.text(String.valueOf(salesmanId))
//								.horizontalAlignment(LEFT)
//								.borderWidth(0.5F)
//								.lineSpacing(1F)
//								.build())

						.add(TextCell.builder()
								.text(salesman == null ? "" : salesman)
								.horizontalAlignment(LEFT)
								.borderWidth(0.5F)
								.lineSpacing(1F)
								.build())

						.add(TextCell.builder()
								.text(String.format("%.2f", commission))
								.horizontalAlignment(RIGHT)
								.borderWidth(0.5F)
								.lineSpacing(1F)
								.build())

						.backgroundColor(Color.WHITE)
						.textColor(Color.BLACK)
						.font(PDType1Font.HELVETICA)
						.fontSize(10)
						.horizontalAlignment(CENTER)
						.height(25F)
						.build();

				tableCommissionBuilder.addRow(salesmanInfo);

				currentY = currentY - 25;

				/*
				 * ========================================================
				 * NEW PAGE
				 * ========================================================
				 */

				if (currentY < 97) {

					Table commissionDetails =
							tableCommissionBuilder.build();

					TableDrawer.builder()
							.contentStream(contentStream)
							.table(commissionDetails)
							.startX(PADDING5)
							.startY(startY - 75)
							.build()
							.draw();

					contentStream.close();

					pageCount++;

					/*
					 * New page.
					 */
					PDPage newPage =
							new PDPage(PDRectangle.A4);

					document.addPage(newPage);

					contentStream =
							new PDPageContentStream(
									document,
									newPage);

					page = newPage;

					currentY =
							page.getMediaBox().getHeight()
							- PADDING20;

					startY = currentY;

					/*
					 * ====================================================
					 * NEW PAGE HEADER
					 * ====================================================
					 */

					tableHeaderBuilder = Table.builder()
							.addColumnsOfWidth(585)
							.font(HELVETICA)
							.borderColor(Color.BLACK)
							.horizontalAlignment(CENTER);

					headerName = Row.builder()
							.add(TextCell.builder()
									.text(shopName)
									.horizontalAlignment(CENTER)
									.borderWidth(0)
									.lineSpacing(10F)
									.build())
							.backgroundColor(WHITE)
							.textColor(Color.BLACK)
							.font(PDType1Font.HELVETICA_BOLD)
							.fontSize(18)
							.horizontalAlignment(CENTER)
							.build();

					info1 = Row.builder()
							.add(TextCell.builder()
									.text("Salesman Commission Report"
											+ "       Page No . : "
											+ pageCount)
									.borderWidthBottom(0.0F)
									.horizontalAlignment(CENTER)
									.build())
							.backgroundColor(WHITE)
							.textColor(Color.BLACK)
							.font(PDType1Font.HELVETICA_BOLD)
							.fontSize(10)
							.horizontalAlignment(CENTER)
							.build();

					info2 = Row.builder()
							.add(TextCell.builder()
									.text("From Date : "
											+ this.getFromDate()
											+ " and To Date : "
											+ this.getToDate())
									.borderWidthBottom(0.0F)
									.horizontalAlignment(CENTER)
									.build())
							.backgroundColor(WHITE)
							.textColor(Color.BLACK)
							.font(PDType1Font.HELVETICA_BOLD)
							.fontSize(8)
							.horizontalAlignment(CENTER)
							.build();

					tableHeaderBuilder.addRow(headerName);
					tableHeaderBuilder.addRow(info1);
					tableHeaderBuilder.addRow(info2);

					if (salesmanName != null
							&& !salesmanName.trim().isEmpty()) {

						Row info3 = Row.builder()
								.add(TextCell.builder()
										.text("Salesman : "
												+ salesmanName.trim())
										.borderWidthBottom(0.0F)
										.horizontalAlignment(CENTER)
										.build())
								.backgroundColor(WHITE)
								.textColor(Color.BLACK)
								.font(PDType1Font.HELVETICA_BOLD)
								.fontSize(8)
								.horizontalAlignment(CENTER)
								.build();

						tableHeaderBuilder.addRow(info3);
					}

					header =
							tableHeaderBuilder.build();

					TableDrawer.builder()
							.contentStream(contentStream)
							.table(header)
							.startX(PADDING5)
							.startY(startY)
							.build()
							.draw();

					/*
					 * New commission table for new page.
					 */
					tableCommissionBuilder = Table.builder()
							.addColumnsOfWidth(430, 155)
							.font(HELVETICA)
							.borderWidth(0.5F)
							.borderColor(Color.BLACK)
							.horizontalAlignment(CENTER);

					tableHeader = Row.builder()

//							.add(TextCell.builder()
//									.text("ID")
//									.horizontalAlignment(LEFT)
//									.borderWidth(0.5F)
//									.lineSpacing(1F)
//									.build())

							.add(TextCell.builder()
									.text("Salesman Name")
									.horizontalAlignment(LEFT)
									.borderWidth(0.5F)
									.lineSpacing(1F)
									.build())

							.add(TextCell.builder()
									.text("Total Commission")
									.horizontalAlignment(RIGHT)
									.borderWidth(0.5F)
									.lineSpacing(1F)
									.build())

							.backgroundColor(Color.LIGHT_GRAY)
							.textColor(Color.BLACK)
							.font(PDType1Font.HELVETICA_BOLD)
							.fontSize(10)
							.horizontalAlignment(CENTER)
							.build();

					tableCommissionBuilder.addRow(tableHeader);

					currentY = startY - 80;
				}
			}

			/*
			 * ============================================================
			 * TOTAL ROW
			 * ============================================================
			 */

			Row totalRow = Row.builder()

					.add(TextCell.builder()
							.text("Total")
							.horizontalAlignment(RIGHT)
							.borderWidth(0.5F)
							.lineSpacing(1F)
							.build())

//					.add(TextCell.builder()
//							.text("")
//							.horizontalAlignment(RIGHT)
//							.borderWidth(0.5F)
//							.lineSpacing(1F)
//							.build())

					.add(TextCell.builder()
							.text(String.format("%.2f", totalCommission))
							.horizontalAlignment(RIGHT)
							.borderWidth(0.5F)
							.lineSpacing(1F)
							.build())

					.backgroundColor(Color.WHITE)
					.textColor(Color.BLACK)
					.font(PDType1Font.HELVETICA_BOLD)
					.fontSize(10)
					.horizontalAlignment(CENTER)
					.height(25F)
					.build();

			tableCommissionBuilder.addRow(totalRow);

			/*
			 * ============================================================
			 * DRAW FINAL TABLE
			 * ============================================================
			 */

			Table commissionDetails =
					tableCommissionBuilder.build();

			TableDrawer.builder()
					.contentStream(contentStream)
					.table(commissionDetails)
					.startX(PADDING5)
					.startY(startY - 75)
					.build()
					.draw();

			contentStream.close();

			/*
			 * ============================================================
			 * FILE NAME
			 * ============================================================
			 */

			String fileName =
					"salesman-commission-fromDate-"
					+ this.getFromDate()
					+ "-ToDate-"
					+ this.getToDate();

			if (salesmanName != null
					&& !salesmanName.trim().isEmpty()) {

				String cleanSalesmanName =
						salesmanName.trim()
								.replaceAll("[^a-zA-Z0-9_-]", "_");

				fileName =
						fileName
						+ "-Salesman-"
						+ cleanSalesmanName;
			}

			fileName = fileName + "-report.pdf";

			String filePath = path + "/" + fileName;

			/*
			 * ============================================================
			 * SAVE PDF
			 * ============================================================
			 */

			document.save(filePath);

			document.close();

			/*
			 * ============================================================
			 * SEND PDF TO BROWSER
			 * ============================================================
			 */

			File pdfFile = new File(filePath);

			response.setHeader(
					"Content-disposition",
					"inline; filename=" + fileName);

			response.setContentType("application/pdf");

			response.setContentLengthLong(pdfFile.length());

			FileInputStream fileInputStream =
					new FileInputStream(pdfFile);

			OutputStream outputStream =
					response.getOutputStream();

			byte[] buffer = new byte[8192];

			int bytesRead;

			while ((bytesRead =
					fileInputStream.read(buffer)) != -1) {

				outputStream.write(
						buffer,
						0,
						bytesRead);
			}

			outputStream.flush();

			fileInputStream.close();

			outputStream.close();

		} catch (SQLException e) {

			addActionError(
					e.getErrorCode()
					+ " || "
					+ e.getMessage()
					+ " || Something Went Wrong!");

		} catch (IOException e) {

			addActionError(
					e.getMessage()
					+ " || "
					+ e.getCause()
					+ " || Something Went Wrong!");

		} finally {

			if (resultSet != null) {

				try {
					resultSet.close();
				} catch (SQLException e) {
				}
			}

			if (preparedStatement != null) {

				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}

			if (connection != null) {

				try {
					connection.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	/*
	 * ================================================================
	 * SESSION
	 * ================================================================
	 */

	public HttpSession getSession() {
		return session;
	}

	public void setSession(HttpSession session) {
		this.session = session;
	}

	public SessionMap<String, Object> getSessionMap() {
		return sessionMap;
	}

	public void setSessionMap(
			SessionMap<String, Object> sessionMap) {

		this.sessionMap = sessionMap;
	}

	@Override
	public void setSession(Map<String, Object> map) {

		sessionMap =
				(SessionMap<String, Object>) map;
	}

	/*
	 * ================================================================
	 * 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;
	}

	/*
	 * ================================================================
	 * FROM DATE
	 * ================================================================
	 */

	public String getFromDate() {
		return fromDate;
	}

	public void setFromDate(String fromDate) {
		this.fromDate = fromDate;
	}

	/*
	 * ================================================================
	 * TO DATE
	 * ================================================================
	 */

	public String getToDate() {
		return toDate;
	}

	public void setToDate(String toDate) {
		this.toDate = toDate;
	}

	/*
	 * ================================================================
	 * SALESMAN NAME
	 * ================================================================
	 */

	public String getSalesmanName() {
		return salesmanName;
	}

	public void setSalesmanName(String salesmanName) {
		this.salesmanName = salesmanName;
	}
}

