package com.reallinkbilling.print;

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.FileInputStream;
import java.io.FileOutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
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.pdfbox.pdmodel.graphics.image.PDImageXObject;
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.BillInformationDAO;
import com.reallinkbilling.DAO.BillProductInfoDAO;
import com.reallinkbilling.db.GetDBConnection;
import com.reallinkbilling.resource.GetListResource;
import com.reallinkbilling.utils.NumberToWordsConverter;

public class PrintProformaTaxInvoice extends ActionSupport implements SessionAware {

    private static final long serialVersionUID = 1L;

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

    private BillInformationDAO proformaInfoDAO = null;
    private List<BillProductInfoDAO> productInfoList = null;
    public HttpServletRequest request;
    public HttpServletResponse response;
    private String proformaNumber;
    private String size;
    private String lang;

    private String nameOfBank;
    private String bankingName;
    private String nameOfBranch;
    private String accountNumber;
    private String ifscCode;

    private static final float PADDING20 = 20f;

    public void printProformaTaxOnly() throws Exception {
        DecimalFormat df = new DecimalFormat("##.##");
        String shopId = (String) this.getSessionMap().get("shopId");
        String shopCode = (String) this.getSessionMap().get("shopCode");
        String shopnameenglish = (String) this.getSessionMap().get("shopnameenglish");
        String address = (String) this.getSessionMap().get("shoponelineaddress");
        @SuppressWarnings("unchecked")
        HashMap<String, String> bankDetails = (HashMap<String, String>) this.getSessionMap().get("bank_details");
        this.setNameOfBank(bankDetails.get("bankname"));
        this.setBankingName(bankDetails.get("bankingname"));
        this.setNameOfBranch(bankDetails.get("branchname"));
        this.setAccountNumber(bankDetails.get("accnumber"));
        this.setIfscCode(bankDetails.get("ifsccode"));
        String custcarenumber = (String) this.getSessionMap().get("shopcustcarenumber");
        String gstNumber = (String) this.getSessionMap().get("shopgstnumber");

        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            proformaInfoDAO = new BillInformationDAO();
            productInfoList = new ArrayList<BillProductInfoDAO>();
            String db = (String) ServletActionContext.getRequest().getSession(false).getAttribute("db");
            connection = GetDBConnection.getDBConnection(db);

            String sql = "SELECT DATE_FORMAT(proformadate,'%d-%M-%Y'), proformatime, custid, customername, "
                    + "transportcharges, comments, proformatotalamount, proforma_by, "
                    + "validity_days, DATE_FORMAT(validity_upto,'%d-%M-%Y'), payment_terms, delivery_terms, "
                    + "advance_percent, advance_amount "
                    + "FROM proforma_info WHERE proformanumber=? AND shop_id=? AND shop_code=?";
            ps = connection.prepareStatement(sql);
            ps.setString(1, this.getProformaNumber());
            ps.setString(2, shopId);
            ps.setString(3, shopCode);
            rs = ps.executeQuery();
            if (rs.next()) {
                proformaInfoDAO.setBillDate(rs.getString(1));
                proformaInfoDAO.setBillNumber(getProformaNumber());
                proformaInfoDAO.setCustId(rs.getString(3));
                proformaInfoDAO.setCustomerName(rs.getString(4));
                proformaInfoDAO.setTransportCharge(rs.getFloat(5));
                proformaInfoDAO.setComments(rs.getString(6));
                proformaInfoDAO.setTotalBillAmount(rs.getString(7));
                proformaInfoDAO.setBilledBy(rs.getString(8));
                proformaInfoDAO.setValidityDays(rs.getInt(9));
                proformaInfoDAO.setValidityUpto(rs.getString(10));
                proformaInfoDAO.setPaymentTerms(rs.getString(11));
                proformaInfoDAO.setDeliveryTerms(rs.getString(12));
                proformaInfoDAO.setAdvancePercent(rs.getFloat(13));
                proformaInfoDAO.setAdvanceAmount(rs.getFloat(14));
            }
            rs.close();
            ps.close();

            // Fallback: if some proforma header fields are empty in DB, try to read them from session
            try {
                Object sessionObj = this.getSessionMap().get("proformaInformation");
                if (sessionObj instanceof BillInformationDAO) {
                    BillInformationDAO sessionProforma = (BillInformationDAO) sessionObj;
                    if (proformaInfoDAO.getComments() == null || proformaInfoDAO.getComments().trim().isEmpty()) {
                        proformaInfoDAO.setComments(sessionProforma.getComments());
                    }
                    if ((proformaInfoDAO.getPaymentTerms() == null
                            || proformaInfoDAO.getPaymentTerms().trim().isEmpty())
                            && sessionProforma.getPaymentTerms() != null) {
                        proformaInfoDAO.setPaymentTerms(sessionProforma.getPaymentTerms());
                    }
                    if ((proformaInfoDAO.getDeliveryTerms() == null
                            || proformaInfoDAO.getDeliveryTerms().trim().isEmpty())
                            && sessionProforma.getDeliveryTerms() != null) {
                        proformaInfoDAO.setDeliveryTerms(sessionProforma.getDeliveryTerms());
                    }
                    if ((proformaInfoDAO.getValidityDays() == null || proformaInfoDAO.getValidityDays() == 0)
                            && sessionProforma.getValidityDays() != null) {
                        proformaInfoDAO.setValidityDays(sessionProforma.getValidityDays());
                    }
                    if ((proformaInfoDAO.getValidityUpto() == null
                            || proformaInfoDAO.getValidityUpto().trim().isEmpty())
                            && sessionProforma.getValidityUpto() != null) {
                        proformaInfoDAO.setValidityUpto(sessionProforma.getValidityUpto());
                    }
                    if ((proformaInfoDAO.getAdvancePercent() == null || proformaInfoDAO.getAdvancePercent() == 0.0F)
                            && sessionProforma.getAdvancePercent() != null) {
                        proformaInfoDAO.setAdvancePercent(sessionProforma.getAdvancePercent());
                    }
                    if ((proformaInfoDAO.getAdvanceAmount() == null || proformaInfoDAO.getAdvanceAmount() == 0.0F)
                            && sessionProforma.getAdvanceAmount() != null) {
                        proformaInfoDAO.setAdvanceAmount(sessionProforma.getAdvanceAmount());
                    }
                }
            } catch (Exception ignore) {
            }

            ps = connection.prepareStatement(
                    "SELECT billing_address FROM shop_customer_master WHERE customer_id=? AND shop_id=? AND shop_code=?");
            ps.setString(1, proformaInfoDAO.getCustId());
            ps.setString(2, shopId);
            ps.setString(3, shopCode);
            rs = ps.executeQuery();
            if (rs.next()) {
                proformaInfoDAO.setCustomerAddress(rs.getString(1));
            }
            rs.close();
            ps.close();

            sql = "SELECT productname, productnamemarathi, salesunit, taxpercentage, "
                    + "salesrate, quanity, "
                    + "discountpercentage, discountrupees, productamount, "
                    + "productamtafterdiscount, grossamount, igst, cgst, sgst "
                    + "FROM product_proforma_info "
                    + "WHERE proformanumber=? AND shop_id=? AND shop_code=?";
            ps = connection.prepareStatement(sql);
            ps.setString(1, this.getProformaNumber());
            ps.setString(2, shopId);
            ps.setString(3, shopCode);
            rs = ps.executeQuery();
            while (rs.next()) {
                BillProductInfoDAO d = new BillProductInfoDAO();
                d.setProductName(rs.getString(1));
                d.setProductNameMarathi(rs.getString(2));
                d.setSalesUnit(rs.getString(3));
                d.setTaxId(rs.getString(4));
                try {
                    Map<String, String> taxInfo = GetListResource.GetTaxNamePercentage(rs.getString(4),
                            this.getSessionMap());
                    String taxPercentage = taxInfo.get("TAX-PERCENTAGE");
                    d.setTaxPercentage(Float.parseFloat(taxPercentage));

                } catch (Exception ignore) {
                }
                d.setSalesRate(rs.getFloat(5));
                d.setQuantity(rs.getFloat(6));
                d.setDiscountPecentage(rs.getFloat(7));
                d.setDiscountRupees(rs.getFloat(8));
                d.setAmount(rs.getFloat(9));
                d.setAmountAfterDiscount(rs.getFloat(10));
                d.setGrossAmount(rs.getFloat(11));
                d.setIgst(rs.getFloat(12));
                d.setCgst(rs.getFloat(13));
                d.setSgst(rs.getFloat(14));

                String prodId = null;
                try {
                    prodId = d.getProductName().split("-")[0];
                } catch (Exception ignore) {
                }
                if (prodId != null) {
                    PreparedStatement hps = connection.prepareStatement(
                            "SELECT hsn_sac_code FROM shop_product_master WHERE product_id=? AND shop_id=? AND shop_code=?");
                    hps.setString(1, prodId);
                    hps.setString(2, shopId);
                    hps.setString(3, shopCode);
                    ResultSet hrs = hps.executeQuery();
                    if (hrs.next()) {
                        d.setHsnCode(hrs.getString(1));
                    } else {
                        d.setHsnCode("NA");
                    }
                    hrs.close();
                    hps.close();
                } else {
                    d.setHsnCode("NA");
                }
                productInfoList.add(d);
            }
            rs.close();
            ps.close();

            response = ServletActionContext.getResponse();
            request = ServletActionContext.getRequest();
            String path = request.getSession().getServletContext().getRealPath("/images/");

            PDImageXObject pdLeftLogoImage = null;
            PDImageXObject pdRightLogoImage = null;
            PDImageXObject pdOwnerSignImage = null;
            PDImageXObject pdCompanyStampImage = null;

            PDDocument document = new PDDocument();
            PDPage page = new PDPage(PDRectangle.A4);
            document.addPage(page);

            if (this.getSessionMap().get("left_logo_image_array") != null) {
                byte[] bytes = (byte[]) this.getSessionMap().get("left_logo_image_array");
                if (bytes != null) {
                    String p = path + "/left_logo_temp.jpeg";
                    try (FileOutputStream fos = new FileOutputStream(p)) {
                        fos.write(bytes);
                    }
                    pdLeftLogoImage = PDImageXObject.createFromFile(p, document);
                }
            }
            if (this.getSessionMap().get("right_logo_image_array") != null) {
                byte[] bytes = (byte[]) this.getSessionMap().get("right_logo_image_array");
                if (bytes != null) {
                    String p = path + "/right_logo_temp.jpeg";
                    try (FileOutputStream fos = new FileOutputStream(p)) {
                        fos.write(bytes);
                    }
                    pdRightLogoImage = PDImageXObject.createFromFile(p, document);
                }
            }
            if (this.getSessionMap().get("owner_sign_image_array") != null) {
                byte[] bytes = (byte[]) this.getSessionMap().get("owner_sign_image_array");
                if (bytes != null) {
                    String p = path + "/owner_sign_image.jpeg";
                    try (FileOutputStream fos = new FileOutputStream(p)) {
                        fos.write(bytes);
                    }
                    pdOwnerSignImage = PDImageXObject.createFromFile(p, document);
                }
            }
            if (this.getSessionMap().get("company_stamp_image_array") != null) {
                byte[] bytes = (byte[]) this.getSessionMap().get("company_stamp_image_array");
                if (bytes != null) {
                    String p = path + "/company_stamp_image.jpeg";
                    try (FileOutputStream fos = new FileOutputStream(p)) {
                        fos.write(bytes);
                    }
                    pdCompanyStampImage = PDImageXObject.createFromFile(p, document);
                }
            }

            PDPageContentStream contentStream = new PDPageContentStream(document, page);

            Table header;
            Table billNumberTable;
            Table customerDetails;
            Table productDetails;
            Table totalAmountDetails;
            Table bankDetailsTable;
            Table proformaExtraDetails;
            Table taxTable;
            Table conditionInfo;
            Table bottomInfo;
            Table signature;

            float contentWidth = page.getMediaBox().getWidth() - (2 * PADDING20);
            TableBuilder tableHeaderBuilder = Table.builder().addColumnsOfWidth(contentWidth).font(HELVETICA)
                    .borderColor(Color.BLACK).horizontalAlignment(CENTER);

            Row title = Row.builder()
                    .add(TextCell.builder().text("PROFORMA INVOICE").horizontalAlignment(CENTER).borderWidth(0).build())
                    .backgroundColor(WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(12)
                    .build();
            Row headerName = Row.builder()
                    .add(TextCell.builder().text(shopnameenglish).horizontalAlignment(CENTER).borderWidth(0).build())
                    .backgroundColor(WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(15)
                    .build();
            Row addressRow = Row.builder()
                    .add(TextCell.builder().text("Address : " + address).horizontalAlignment(CENTER)
                            .borderWidthBottom(0.5F).build())
                    .backgroundColor(WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(8)
                    .build();
            Row contactRow = Row.builder()
                    .add(TextCell.builder().text("" + "" + " ").horizontalAlignment(CENTER).borderWidthBottom(1F)
                            .build())
                    .backgroundColor(WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(10)
                    .build();
            Row gstRow = Row.builder()
                    .add(TextCell.builder().text("Contact No. : " + custcarenumber + "  GST NO :" + gstNumber)
                            .horizontalAlignment(CENTER).borderWidthBottom(0.5F).build())
                    .backgroundColor(WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(10)
                    .build();
            tableHeaderBuilder.addRow(title);
            tableHeaderBuilder.addRow(headerName);
            tableHeaderBuilder.addRow(addressRow);
            tableHeaderBuilder.addRow(contactRow);
            tableHeaderBuilder.addRow(gstRow);
            header = tableHeaderBuilder.build();

            TableBuilder billNumberDate = Table.builder().addColumnsOfWidth(140, 140, 135, 140).font(HELVETICA)
                    .borderWidth(1).borderColor(Color.BLACK);
            Row billNumberDateRow1 = Row.builder()
                    .add(TextCell.builder().text("Proforma Number").horizontalAlignment(LEFT).borderWidth(0.5F)
                            .fontSize(8).build())
                    .add(TextCell.builder().text(proformaInfoDAO.getBillNumber()).horizontalAlignment(LEFT)
                            .borderWidth(0.5F).fontSize(10).build())
                    .add(TextCell.builder().text("Proforma Date").horizontalAlignment(RIGHT).borderWidth(0.5F)
                            .fontSize(8).build())
                    .add(TextCell.builder().text(proformaInfoDAO.getBillDate()).horizontalAlignment(LEFT)
                            .borderWidth(0.5F).fontSize(10).build())
                    .backgroundColor(WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).build();
            billNumberDate.addRow(billNumberDateRow1);
            billNumberTable = billNumberDate.build();

            TableBuilder tableCustDetailsBuilder = Table.builder().addColumnsOfWidth(contentWidth).font(HELVETICA)
                    .borderColor(Color.BLACK).horizontalAlignment(CENTER);
            Row custHeader = Row.builder()
                    .add(TextCell.builder().text("Details Of Customer (Proforma To)").horizontalAlignment(CENTER)
                            .borderWidth(0).build())
                    .backgroundColor(WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(10)
                    .build();
            String[] custDetails = proformaInfoDAO.getCustomerName().split("-");
            Row custNameRow = Row.builder()
                    .add(TextCell
                            .builder()
                            .text("Customer Name:  "
                                    + (custDetails.length > 1 ? custDetails[1] : proformaInfoDAO.getCustomerName()))
                            .horizontalAlignment(LEFT).borderWidth(0).borderWidthBottom(0.5F).build())
                    .backgroundColor(WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(9)
                    .build();
            Row custAddrRow = Row.builder()
                    .add(TextCell
                            .builder()
                            .text("Address: "
                                    + (proformaInfoDAO.getCustomerAddress() == null ? ""
                                            : proformaInfoDAO.getCustomerAddress()))
                            .horizontalAlignment(LEFT).borderWidth(0).borderWidthBottom(0.5F).build())
                    .backgroundColor(WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(8)
                    .build();
            Row custMobileRow = Row.builder()
                    .add(TextCell
                            .builder()
                            .text("Mobile:  " + (custDetails.length > 2 ? custDetails[2] : ""))
                            .horizontalAlignment(LEFT).borderWidth(0).borderWidthBottom(0.5F).build())
                    .backgroundColor(WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(9)
                    .build();
            tableCustDetailsBuilder.addRow(custHeader);
            tableCustDetailsBuilder.addRow(custNameRow);
            tableCustDetailsBuilder.addRow(custAddrRow);
            tableCustDetailsBuilder.addRow(custMobileRow);
            customerDetails = tableCustDetailsBuilder.build();

            Color BLUE_LIGHT = new Color(186, 206, 230);
            TableBuilder tableproductDetailsBuilder = Table.builder()
                    .addColumnsOfWidth(25, 200, 45, 35, 60, 30, 45, 30, 45, 40).font(HELVETICA).borderWidth(0.5F)
                    .borderColor(Color.BLACK);
            Row productHeader1 = Row.builder()
                    .add(TextCell.builder().text("Sr.").rowSpan(2).horizontalAlignment(CENTER).borderWidth(0.5F)
                            .build())
                    .add(TextCell.builder().text("Description Of Goods | HSN Code").rowSpan(2)
                            .horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text("Unit\nPrice").rowSpan(2).horizontalAlignment(CENTER)
                            .borderWidth(0.5F).build())
                    .add(TextCell.builder().text("Qty").rowSpan(2).horizontalAlignment(CENTER).borderWidth(0.5F)
                            .build())
                    .add(TextCell.builder().text("Taxable\nAmount").rowSpan(2).horizontalAlignment(CENTER)
                            .borderWidth(0.5F).build())
                    .add(TextCell.builder().text("SGST").colSpan(2).horizontalAlignment(CENTER).borderWidth(0.5F)
                            .build())
                    .add(TextCell.builder().text("CGST").colSpan(2).horizontalAlignment(CENTER).borderWidth(0.5F)
                            .build())
                    .add(TextCell.builder().text("Total\nAmount").rowSpan(2).horizontalAlignment(CENTER)
                            .borderWidth(0.5F).build())
                    .backgroundColor(BLUE_LIGHT).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(8)
                    .horizontalAlignment(CENTER).build();
            Row productHeader2 = Row.builder()
                    .add(TextCell.builder().text("Rate %").horizontalAlignment(CENTER).borderWidth(0.5F).build())
                    .add(TextCell.builder().text("Amount").horizontalAlignment(CENTER).borderWidth(0.5F).build())
                    .add(TextCell.builder().text("Rate %").horizontalAlignment(CENTER).borderWidth(0.5F).build())
                    .add(TextCell.builder().text("Amount").horizontalAlignment(CENTER).borderWidth(0.5F).build())
                    .backgroundColor(BLUE_LIGHT).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(8)
                    .horizontalAlignment(CENTER).build();
            tableproductDetailsBuilder.addRow(productHeader1);
            tableproductDetailsBuilder.addRow(productHeader2);

            int srNo = 1;
            for (int i = 0; i < productInfoList.size(); i++) {
                BillProductInfoDAO dao = productInfoList.get(i);
                float halfRate = 0.0F;
                try {
                    halfRate = dao.getTaxPercentage() / 2.0F;
                } catch (Exception ignore) {
                }
                String desc = dao.getProductName().split("-")[1].toUpperCase() + "\n[ HSN Code - "
                        + (dao.getHsnCode() == null ? "NA" : dao.getHsnCode()) + " ]";
                Row r = Row.builder()
                        .add(TextCell.builder().text(String.valueOf(srNo)).horizontalAlignment(CENTER)
                                .borderWidth(0.5F).build())
                        .add(TextCell.builder().text(desc).horizontalAlignment(LEFT).borderWidth(0.5F).fontSize(8)
                                .build())
                        .add(TextCell.builder().text(df.format(dao.getSalesRate())).horizontalAlignment(RIGHT)
                                .borderWidth(0.5F).build())
                        .add(TextCell.builder().text(df.format(dao.getQuantity())).horizontalAlignment(CENTER)
                                .borderWidth(0.5F).build())
                        .add(TextCell.builder().text(df.format(dao.getGrossAmount())).horizontalAlignment(RIGHT)
                                .borderWidth(0.5F).build())
                        .add(TextCell.builder().text(df.format(halfRate)).horizontalAlignment(CENTER)
                                .borderWidth(0.5F).build())
                        .add(TextCell.builder().text(df.format(dao.getSgst())).horizontalAlignment(RIGHT)
                                .borderWidth(0.5F).build())
                        .add(TextCell.builder().text(df.format(halfRate)).horizontalAlignment(CENTER)
                                .borderWidth(0.5F).build())
                        .add(TextCell.builder().text(df.format(dao.getCgst())).horizontalAlignment(RIGHT)
                                .borderWidth(0.5F).build())
                        .add(TextCell.builder().text(df.format(dao.getAmountAfterDiscount())).horizontalAlignment(RIGHT)
                                .borderWidth(0.5F).build())
                        .backgroundColor(Color.WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA).fontSize(8)
                        .build();
                tableproductDetailsBuilder.addRow(r);
                srNo++;
            }
            productDetails = tableproductDetailsBuilder.build();

            float totalAmt = 0.0F, totalDiscount = 0.0F, totalAfterDisc = 0.0F, totalMaterialCost = 0.0F;
            for (BillProductInfoDAO d : productInfoList) {
                totalAmt += d.getAmount();
                totalAfterDisc += d.getAmountAfterDiscount();
                totalDiscount += (d.getAmount() - d.getAmountAfterDiscount());
                try {
                    totalMaterialCost += d.getGrossAmount();
                } catch (Exception ignore) {
                }
            }

            Map<String, float[]> taxAgg = new HashMap<String, float[]>(); // hsn -> [gstRate, taxable, sgst, cgst, igst]
            float sumTaxable = 0, sumSgst = 0, sumCgst = 0, sumIgst = 0;
            for (BillProductInfoDAO d : productInfoList) {
                String prodName = d.getProductName();
                String prodId = null;
                try {
                    prodId = prodName.split("-")[0];
                } catch (Exception ignore) {
                }
                String hsn = "NA";
                if (prodId != null) {
                    PreparedStatement hps = connection.prepareStatement(
                            "SELECT hsn_sac_code FROM shop_product_master WHERE product_id=? AND shop_id=? AND shop_code=?");
                    hps.setString(1, prodId);
                    hps.setString(2, shopId);
                    hps.setString(3, shopCode);
                    ResultSet hrs = hps.executeQuery();
                    if (hrs.next()) {
                        hsn = hrs.getString(1);
                    }
                    hrs.close();
                    hps.close();
                }
                float[] arr = taxAgg.getOrDefault(hsn,
                        new float[] { d.getTaxPercentage(), 0, 0, 0, 0 });
                arr[1] += d.getGrossAmount();
                arr[2] += d.getSgst();
                arr[3] += d.getCgst();
                arr[4] += d.getIgst();
                taxAgg.put(hsn, arr);
            }

            TableBuilder taxTableBuilder = Table.builder()
                    .addColumnsOfWidth(120, 55, 85, 45, 60, 45, 60, 35, 50).font(HELVETICA).borderColor(Color.BLACK)
                    .horizontalAlignment(CENTER);
            Row taxHeader1 = Row.builder()
                    .add(TextCell.builder().text("HSN Code").rowSpan(2).horizontalAlignment(CENTER).borderWidth(0.5F)
                            .build())
                    .add(TextCell.builder().text("GST %").rowSpan(2).horizontalAlignment(CENTER).borderWidth(0.5F)
                            .build())
                    .add(TextCell.builder().text("Taxble Value").rowSpan(2).horizontalAlignment(CENTER)
                            .borderWidth(0.5F).build())
                    .add(TextCell.builder().text("SGST").colSpan(2).horizontalAlignment(CENTER).borderWidth(0.5F)
                            .build())
                    .add(TextCell.builder().text("CGST").colSpan(2).horizontalAlignment(CENTER).borderWidth(0.5F)
                            .build())
                    .add(TextCell.builder().text("IGST").colSpan(2).horizontalAlignment(CENTER).borderWidth(0.5F)
                            .build())
                    .backgroundColor(WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA).fontSize(8).build();
            Row taxHeader2 = Row.builder()
                    .add(TextCell.builder().text("Tax %").horizontalAlignment(CENTER).borderWidth(0.5F).build())
                    .add(TextCell.builder().text("Amt").horizontalAlignment(CENTER).borderWidth(0.5F).build())
                    .add(TextCell.builder().text("Tax %").horizontalAlignment(CENTER).borderWidth(0.5F).build())
                    .add(TextCell.builder().text("Amt").horizontalAlignment(CENTER).borderWidth(0.5F).build())
                    .add(TextCell.builder().text("Tax %").horizontalAlignment(CENTER).borderWidth(0.5F).build())
                    .add(TextCell.builder().text("Amt").horizontalAlignment(CENTER).borderWidth(0.5F).build())
                    .backgroundColor(WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA).fontSize(8).build();
            taxTableBuilder.addRow(taxHeader1);
            taxTableBuilder.addRow(taxHeader2);
            for (String hsn : taxAgg.keySet()) {
                float[] a = taxAgg.get(hsn);
                sumTaxable += a[1];
                sumSgst += a[2];
                sumCgst += a[3];
                sumIgst += a[4];
                taxTableBuilder.addRow(Row.builder()
                        .add(TextCell.builder().text(hsn).horizontalAlignment(CENTER).borderWidth(0.5F).build())
                        .add(TextCell.builder().text(new DecimalFormat("##").format(a[0])).horizontalAlignment(CENTER)
                                .borderWidth(0.5F).build())
                        .add(TextCell.builder().text(df.format(a[1])).horizontalAlignment(CENTER).borderWidth(0.5F)
                                .build())
                        .add(TextCell.builder().text(df.format(a[0] / 2)).horizontalAlignment(CENTER)
                                .borderWidth(0.5F).build())
                        .add(TextCell.builder().text(df.format(a[2])).horizontalAlignment(CENTER).borderWidth(0.5F)
                                .build())
                        .add(TextCell.builder().text(df.format(a[0] / 2)).horizontalAlignment(CENTER)
                                .borderWidth(0.5F).build())
                        .add(TextCell.builder().text(df.format(a[3])).horizontalAlignment(CENTER).borderWidth(0.5F)
                                .build())
                        .add(TextCell.builder().text(df.format(a[0])).horizontalAlignment(CENTER).borderWidth(0.5F)
                                .build())
                        .add(TextCell.builder().text(df.format(a[4])).horizontalAlignment(CENTER).borderWidth(0.5F)
                                .build())
                        .backgroundColor(WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA).fontSize(8).build());
            }
            taxTableBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("Total").horizontalAlignment(RIGHT).colSpan(2).borderWidth(0.5F)
                            .build())
                    .add(TextCell.builder().text(df.format(sumTaxable)).horizontalAlignment(CENTER).borderWidth(0.5F)
                            .build())
                    .add(TextCell.builder().text("-").horizontalAlignment(CENTER).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(df.format(sumSgst)).horizontalAlignment(CENTER).borderWidth(0.5F)
                            .build())
                    .add(TextCell.builder().text("-").horizontalAlignment(CENTER).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(df.format(sumCgst)).horizontalAlignment(CENTER).borderWidth(0.5F)
                            .build())
                    .add(TextCell.builder().text("-").horizontalAlignment(CENTER).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(df.format(sumIgst)).horizontalAlignment(CENTER).borderWidth(0.5F)
                            .build())
                    .backgroundColor(WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA).fontSize(8).build());
            taxTable = taxTableBuilder.build();

            TableBuilder bankDetailsBuilder = Table.builder().addColumnsOfWidth(100, 160).font(HELVETICA)
                    .borderWidth(1).horizontalAlignment(LEFT).borderColor(Color.BLACK);
            Row bankRow = Row.builder()
                    .add(TextCell.builder().text("Bank Details").colSpan(2).horizontalAlignment(CENTER)
                            .borderWidth(0.5F).build())
                    .backgroundColor(Color.WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(8)
                    .height(15F).build();
            bankDetailsBuilder.addRow(bankRow);
            bankDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("Banking Name").horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(String.valueOf(this.getBankingName())).horizontalAlignment(LEFT)
                            .borderWidth(0.5F).build())
                    .font(PDType1Font.HELVETICA).fontSize(8).build());
            bankDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("Name Of Bank").horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(String.valueOf(this.getNameOfBank())).horizontalAlignment(LEFT)
                            .borderWidth(0.5F).build())
                    .font(PDType1Font.HELVETICA).fontSize(8).build());
            bankDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("Branch").horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(String.valueOf(this.getNameOfBranch())).horizontalAlignment(LEFT)
                            .borderWidth(0.5F).build())
                    .font(PDType1Font.HELVETICA).fontSize(8).build());
            bankDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("IFSC Code").horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(String.valueOf(this.getIfscCode())).horizontalAlignment(LEFT)
                            .borderWidth(0.5F).build())
                    .font(PDType1Font.HELVETICA).fontSize(8).build());
            bankDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("Account Number").horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(String.valueOf(this.getAccountNumber())).horizontalAlignment(LEFT)
                            .borderWidth(0.5F).build())
                    .font(PDType1Font.HELVETICA).fontSize(8).build());
            bankDetailsTable = bankDetailsBuilder.build();

            TableBuilder totalDetailsBuilder = Table.builder().addColumnsOfWidth(150, 100).font(HELVETICA)
                    .borderWidth(1).horizontalAlignment(RIGHT).borderColor(Color.BLACK);
            totalDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("Total Material Cost").horizontalAlignment(RIGHT).borderWidth(0.5F)
                            .build())
                    .add(TextCell.builder().text(df.format(totalMaterialCost)).horizontalAlignment(RIGHT)
                            .borderWidth(0.5F).build())
                    .font(PDType1Font.HELVETICA_BOLD).fontSize(9).build());
            totalDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("SGST").horizontalAlignment(RIGHT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(df.format(sumSgst)).horizontalAlignment(RIGHT).borderWidth(0.5F)
                            .build())
                    .font(PDType1Font.HELVETICA_BOLD).fontSize(9).build());
            totalDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("CGST").horizontalAlignment(RIGHT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(df.format(sumCgst)).horizontalAlignment(RIGHT).borderWidth(0.5F)
                            .build())
                    .font(PDType1Font.HELVETICA_BOLD).fontSize(9).build());
            totalDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("IGST").horizontalAlignment(RIGHT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(df.format(sumIgst)).horizontalAlignment(RIGHT).borderWidth(0.5F)
                            .build())
                    .font(PDType1Font.HELVETICA_BOLD).fontSize(9).build());
            totalDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("Transportation Charges").horizontalAlignment(RIGHT)
                            .borderWidth(0.5F).build())
                    .add(TextCell.builder().text(df.format(proformaInfoDAO.getTransportCharge())).horizontalAlignment(RIGHT)
                            .borderWidth(0.5F).build())
                    .font(PDType1Font.HELVETICA_BOLD).fontSize(9).build());
            totalDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("Total Proforma Amount").horizontalAlignment(RIGHT)
                            .borderWidth(0.5F).build())
                    .add(TextCell.builder().text(String.valueOf(proformaInfoDAO.getTotalBillAmount()))
                            .horizontalAlignment(RIGHT).borderWidth(0.5F).build())
                    .font(PDType1Font.HELVETICA_BOLD).fontSize(9).build());
            totalAmountDetails = totalDetailsBuilder.build();

            // Table for printing Comments, Validity, Payment/Delivery terms, Advance details cleanly
            TableBuilder extraDetailsBuilder = Table.builder()
                    .addColumnsOfWidth(140, contentWidth - 140)
                    .font(HELVETICA)
                    .borderWidth(0.5F)
                    .borderColor(Color.BLACK);

            String commentsExtraText = proformaInfoDAO.getComments() == null ? "" : proformaInfoDAO.getComments();
            extraDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("Comments").horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(commentsExtraText).horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .font(PDType1Font.HELVETICA).fontSize(8).build());

            String validityValue;
            if (proformaInfoDAO.getValidityDays() != null && proformaInfoDAO.getValidityDays() > 0) {
                validityValue = proformaInfoDAO.getValidityDays() + " Days";
            } else if (proformaInfoDAO.getValidityUpto() != null
                    && proformaInfoDAO.getValidityUpto().trim().length() > 0) {
                validityValue = proformaInfoDAO.getValidityUpto();
            } else {
                validityValue = "-";
            }
            extraDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("Validity").horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(validityValue).horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .font(PDType1Font.HELVETICA).fontSize(8).build());

            String paymentTermsVal = proformaInfoDAO.getPaymentTerms() == null ? "" : proformaInfoDAO.getPaymentTerms();
            extraDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("Payment Terms").horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(paymentTermsVal).horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .font(PDType1Font.HELVETICA).fontSize(8).build());

            String deliveryTermsVal = proformaInfoDAO.getDeliveryTerms() == null ? ""
                    : proformaInfoDAO.getDeliveryTerms();
            extraDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("Delivery Terms").horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(deliveryTermsVal).horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .font(PDType1Font.HELVETICA).fontSize(8).build());

            float advPercentExtra = proformaInfoDAO.getAdvancePercent() == null ? 0.0F : proformaInfoDAO.getAdvancePercent();
            float advAmountExtra = proformaInfoDAO.getAdvanceAmount() == null ? 0.0F : proformaInfoDAO.getAdvanceAmount();

            extraDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("Advance %").horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(advPercentExtra == 0.0F ? "-" : df.format(advPercentExtra))
                            .horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .font(PDType1Font.HELVETICA).fontSize(8).build());

            extraDetailsBuilder.addRow(Row.builder()
                    .add(TextCell.builder().text("Advance Amount").horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .add(TextCell.builder().text(advAmountExtra == 0.0F ? "-" : df.format(advAmountExtra))
                            .horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .font(PDType1Font.HELVETICA).fontSize(8).build());

            proformaExtraDetails = extraDetailsBuilder.build();

            TableBuilder signatureBuiler = Table.builder().addColumnsOfWidth(260, 260).font(HELVETICA)
                    .borderColor(Color.BLACK).horizontalAlignment(CENTER);
            Row signRow1 = Row.builder()
                    .add(TextCell.builder().text("Receiver Signature").horizontalAlignment(CENTER).borderWidth(0)
                            .build())
                    .add(TextCell.builder().text("For Proprietor").horizontalAlignment(CENTER).borderWidth(0).build())
                    .backgroundColor(Color.WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(9)
                    .height(20F).build();
            signatureBuiler.addRow(signRow1);
            signature = signatureBuiler.build();

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

            TableDrawer.builder().contentStream(contentStream).table(header).startX(PADDING20).startY(startY).build()
                    .draw();
            startY = startY - header.getHeight() - 10;
            TableDrawer.builder().contentStream(contentStream).table(billNumberTable).startX(PADDING20).startY(startY)
                    .build().draw();
            startY = startY - billNumberTable.getHeight() - 10;
            TableDrawer.builder().contentStream(contentStream).table(customerDetails).startX(PADDING20).startY(startY)
                    .build().draw();
            startY = startY - customerDetails.getHeight() - 10;
            TableDrawer.builder().contentStream(contentStream).table(productDetails).startX(PADDING20).startY(startY)
                    .build().draw();
            startY = startY - productDetails.getHeight() - 10;
            TableDrawer.builder().contentStream(contentStream).table(bankDetailsTable).startX(PADDING20).startY(startY)
                    .build().draw();
            float totalsStartX = PADDING20 + (contentWidth - totalAmountDetails.getWidth());
            if (totalsStartX < PADDING20) {
                totalsStartX = PADDING20;
            }
            TableDrawer.builder().contentStream(contentStream).table(totalAmountDetails).startX(totalsStartX)
                    .startY(startY).build().draw();
            startY = startY - bankDetailsTable.getHeight() - 10;

            // Draw extra Proforma details (comments, validity, terms, advance)
            TableDrawer.builder().contentStream(contentStream).table(proformaExtraDetails).startX(PADDING20)
                    .startY(startY).build().draw();
            startY = startY - proformaExtraDetails.getHeight() - 10;

            TableDrawer.builder().contentStream(contentStream).table(taxTable).startX(PADDING20).startY(startY).build()
                    .draw();
            startY = startY - taxTable.getHeight() - 5;

            TableBuilder tableConditionInfoBuilder = Table.builder().addColumnsOfWidth(contentWidth).font(HELVETICA)
                    .borderColor(Color.BLACK);
            Row tcHeader = Row.builder()
                    .add(TextCell.builder().text("Terms & Conditions :").horizontalAlignment(LEFT).borderWidth(0.5F)
                            .build())
                    .backgroundColor(Color.WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(9)
                    .build();
            String validityText;
            if (proformaInfoDAO.getValidityDays() != null && proformaInfoDAO.getValidityDays() > 0) {
                validityText = "1. This proforma is valid for " + proformaInfoDAO.getValidityDays()
                        + " days from the date of proforma.";
            } else if (proformaInfoDAO.getValidityUpto() != null
                    && proformaInfoDAO.getValidityUpto().trim().length() > 0) {
                validityText = "1. This proforma is valid up to " + proformaInfoDAO.getValidityUpto() + ".";
            } else {
                validityText = "1. This proforma is valid for 15 days from the date of proforma.";
            }
            Row tcRow1 = Row.builder()
                    .add(TextCell.builder().text(validityText).horizontalAlignment(LEFT).borderWidth(0.5F).build())
                    .backgroundColor(Color.WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD).fontSize(9)
                    .build();
            tableConditionInfoBuilder.addRow(tcHeader);
            tableConditionInfoBuilder.addRow(tcRow1);
            conditionInfo = tableConditionInfoBuilder.build();

            TableDrawer.builder().contentStream(contentStream).table(conditionInfo).startX(PADDING20).startY(startY)
                    .build().draw();
            startY = startY - conditionInfo.getHeight() - 5;

            TableBuilder tableBottomInfoBuilder = Table.builder().addColumnsOfWidth(contentWidth).font(HELVETICA)
                    .borderColor(Color.BLACK);
            try {
                Float totalAmounttoConvert = Float.parseFloat(proformaInfoDAO.getTotalBillAmount());
                DecimalFormat dfConvert = new DecimalFormat("##.##");
                String convertedAmount = "INR " + NumberToWordsConverter.getConverted(dfConvert.format(totalAmounttoConvert));
                Row amountWords = Row.builder()
                        .add(TextCell.builder().text("Amount in words :" + convertedAmount).horizontalAlignment(LEFT)
                                .borderWidth(0.5F).build())
                        .backgroundColor(Color.WHITE).textColor(Color.BLACK).font(PDType1Font.HELVETICA_BOLD)
                        .fontSize(9).build();
                tableBottomInfoBuilder.addRow(amountWords);
            } catch (Exception ignore) {
            }
            bottomInfo = tableBottomInfoBuilder.build();

            TableDrawer.builder().contentStream(contentStream).table(bottomInfo).startX(PADDING20).startY(startY)
                    .build().draw();
            startY = startY - bottomInfo.getHeight() - 120;
            TableDrawer.builder().contentStream(contentStream).table(signature).startX(PADDING20).startY(startY)
                    .build().draw();

            if (pdLeftLogoImage != null) {
                contentStream.drawImage(pdLeftLogoImage, 20, 770, 65, 65);
            }
            if (pdRightLogoImage != null) {
                contentStream.drawImage(pdRightLogoImage, 470, 770, 65, 65);
            }
            if (pdOwnerSignImage != null) {
                contentStream.drawImage(pdOwnerSignImage, 380, 40, 90, 30);
            }
            if (pdCompanyStampImage != null) {
                contentStream.drawImage(pdCompanyStampImage, 260, 40, 67, 67);
            }

            contentStream.close();

            document.save(path + "/proforma-tax.pdf");
            document.close();

            response.setHeader("Content-disposition", "inline; filename=proforma-tax.pdf");
            response.setContentType("application/pdf");
            @SuppressWarnings("resource")
            FileInputStream fileInputStream = new FileInputStream(path + "/proforma-tax.pdf");
            PrintWriter out = response.getWriter();
            int bytes;
            while ((bytes = fileInputStream.read()) != -1) {
                out.write(bytes);
            }
            fileInputStream.close();
            out.flush();
            out.close();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception ignore) {
            }
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (Exception ignore) {
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (Exception ignore) {
            }
        }
    }

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

    @SuppressWarnings("unchecked")
    @Override
    public void setSession(Map<String, Object> map) {
        sessionMap = (SessionMap<String, Object>) map;
    }

    public String getProformaNumber() {
        return proformaNumber;
    }

    public void setProformaNumber(String proformaNumber) {
        this.proformaNumber = proformaNumber;
    }

    public String getSize() {
        return size;
    }

    public void setSize(String size) {
        this.size = size;
    }

    public String getLang() {
        return lang;
    }

    public void setLang(String lang) {
        this.lang = lang;
    }

    public String getBankingName() {
        return bankingName;
    }

    public void setBankingName(String bankingName) {
        this.bankingName = bankingName;
    }

    public String getNameOfBranch() {
        return nameOfBranch;
    }

    public void setNameOfBranch(String nameOfBranch) {
        this.nameOfBranch = nameOfBranch;
    }

    public String getAccountNumber() {
        return accountNumber;
    }

    public void setAccountNumber(String accountNumber) {
        this.accountNumber = accountNumber;
    }

    public String getIfscCode() {
        return ifscCode;
    }

    public void setIfscCode(String ifscCode) {
        this.ifscCode = ifscCode;
    }

    public String getNameOfBank() {
        return nameOfBank;
    }

    public void setNameOfBank(String nameOfBank) {
        this.nameOfBank = nameOfBank;
    }
}

