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.FileInputStream;
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.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.ArrayList;
import java.util.List;
import java.text.DecimalFormat;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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.dispatcher.SessionMap;
import org.apache.struts2.interceptor.ServletRequestAware;
import org.apache.struts2.interceptor.ServletResponseAware;
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.reallinkbilling.DAO.ItemWiseSaleReportDAO;
import com.reallinkbilling.db.GetDBConnection;
import com.reallinkbilling.utils.GetProductDetailsUtil;
import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;

public class DownloadItemWiseSaleReport extends ActionSupport implements SessionAware,ServletRequestAware,ServletResponseAware{

	
	

	/**
	 * 
	 */
	private static final long serialVersionUID = 2231894051386352138L;

		private final static Color BLUE_DARK = new Color(76, 129, 190);
	    private final static Color BLUE_LIGHT_1 = new Color(186, 206, 230);
	    private final static Color BLUE_LIGHT_2 = new Color(218, 230, 242);

	    private final static Color GRAY_LIGHT_1 = new Color(245, 245, 245);
	    private final static Color GRAY_LIGHT_2 = new Color(240, 240, 240);
	    private final static Color GRAY_LIGHT_3 = new Color(216, 216, 216);
	    private static final float PADDING5 = 5f;
	    private static final float PADDING10 = 10f;
	    private static final float PADDING20 = 20f;
	    private static final float PADDING30 = 30f;
	    private static final float PADDING40 = 40f;
	    private static final float PADDING50 = 50f;

	private SessionMap<String,Object> sessionMap;
	private String fromDate;
	private String toDate;
	private HttpServletRequest request = null;
	private HttpServletResponse response = null;
	
	private List<ItemWiseSaleReportDAO> itemWiseSaleList = new  ArrayList<ItemWiseSaleReportDAO>();
	private String allBillAmount;
	private String allDiscount;
	private String allBillAmountAfterDis;
	private String allPurchaseAmount;
	
	private Float totalCashCollection;
	private Float totalCreditCollection;
	private Float totalCreditGiven;
	private Float totalBilling;
	private Float totalCollection;
	
	public void downloadItemWiseSaleReport() throws Exception {
		
		//System.out.println("Bill Number : "+billNumber);
		
				Connection connection = null;
				PreparedStatement preparedStatement= null;
				ResultSet resultSet = null;
				
				String sql = "";
				
				String billDateTime = "";
				
				Map<String, Object> session = ActionContext.getContext().getSession();
				
				String db = (String) this.getSessionMap().get("db");
				try
	        	{
					
					
					String shopId = (String) this.getSessionMap().get("shopId");
		    		String shopCode = (String) this.getSessionMap().get("shopCode");

		    		
					connection = GetDBConnection.getDBConnection(db);
					

					sql = "SELECT "
//							+ "DATE_FORMAT(csd.bill_date,'%d-%m-%Y'),"
//							+ "csd.bill_number,"
							+ "spi.product_id,"
							+ "spi.product_name,"
							+ "spi.sales_unit,"
							+ "SUM(spi.product_quantity),"
							+ "SUM(spi.product_total_amount),"
							+ "SUM(spi.discount_ruppees*spi.product_quantity),"
							+ "SUM(spi.product_total_amount_aft_discount),"
							+ "SUM(spi.product_net_amount),"
							+ "SUM(spi.product_igst_amount),"
							+ "SUM(spi.product_cgst_amount),"
							+ "SUM(spi.product_sgst_amount) "
							+ "FROM "
							+ "shop_bill_info_master AS csd "
							+ "INNER JOIN "
							+ "shop_bill_product_info_master AS spi "
							+ "ON "
							+ "csd.bill_number = spi.bill_number "
							+ "WHERE "
							+ "csd.shop_id = ? "
							+ "AND "
							+ "csd.shop_code = ? "
							+ "AND "
							+ "spi.shop_id = ? "
							+ "AND "
							+ "spi.shop_code = ? "
							+ "AND "						
							+ "(csd.bill_date "
							+ "BETWEEN STR_TO_DATE(?,'%d-%m-%Y') "
							+ "AND "
							+ "STR_TO_DATE(?,'%d-%m-%Y')) "
							+ "GROUP BY "
							+ "spi.product_id,spi.product_name,spi.sales_unit";
					
					preparedStatement = connection.prepareStatement(sql);
					preparedStatement.setString(1, shopId);
					preparedStatement.setString(2, shopCode);
					preparedStatement.setString(3, shopId);
					preparedStatement.setString(4, shopCode);
					preparedStatement.setString(5, this.getFromDate());
					preparedStatement.setString(6, this.getToDate());
					
					
					resultSet = preparedStatement.executeQuery();
					
					DecimalFormat df = new DecimalFormat("##.##");
					float tBillAmt = 0.0F;
					float tDiscount = 0.0F;
					float tBillAmtAftDis = 0.0F;
					float tPurchaseAmt = 0.0F;
					while(resultSet.next())
					{
						ItemWiseSaleReportDAO saleReportDAO =  new ItemWiseSaleReportDAO();
						
						float productPurchaseRate = GetProductDetailsUtil.getProductPurchaseRateById(this.getSessionMap(), resultSet.getString(1));
						
						saleReportDAO.setProductName(resultSet.getString(2));
						saleReportDAO.setUnits(resultSet.getString(3));
						saleReportDAO.setQuantity(df.format(Float.parseFloat(resultSet.getString(4))));
						
						saleReportDAO.setTotalPurchaseAmount(df.format(productPurchaseRate*resultSet.getFloat(4)));
						
						saleReportDAO.setTotalBillAmount(resultSet.getString(5));
						saleReportDAO.setTotalDiscountAmount(resultSet.getString(6));
						saleReportDAO.setTotalBillAmountAfterDiscount(resultSet.getString(7));
						saleReportDAO.setTotalGrossAmount(resultSet.getString(8));
						saleReportDAO.setTotalIGSTAmount(resultSet.getString(9));
						saleReportDAO.setTotalCGSTAmount(resultSet.getString(10));
						saleReportDAO.setTotalSGSTAmount(resultSet.getString(11));
						
						this.getItemWiseSaleList().add(saleReportDAO);
						
						tPurchaseAmt = tPurchaseAmt + Float.parseFloat(saleReportDAO.getTotalPurchaseAmount());
						tBillAmt = tBillAmt + resultSet.getFloat(5);
						tDiscount = tDiscount + resultSet.getFloat(6);
						tBillAmtAftDis = tBillAmtAftDis + resultSet.getFloat(7);
						
					}
					
					this.setAllPurchaseAmount(df.format(tPurchaseAmt));
					this.setAllBillAmount(df.format(tBillAmt));
					this.setAllDiscount(df.format(tDiscount));
					this.setAllBillAmountAfterDis(df.format(tBillAmtAftDis));
					
					

					if(resultSet != null)
					{
						resultSet.close();
						resultSet = null;
					}
					
					if(preparedStatement != null)
					{
						preparedStatement.close();
						preparedStatement = null;
					}
					
					
					sql = "SELECT "
	    					//+ "bill_date,"
	    					+ "SUM(bill_total_amount),"
	    					+ "SUM(bill_amount_received),"
	    					+ "SUM(bill_amount_retrurn) "
	    					+ "FROM "
	    					+ "shop_bill_info_master "
	    					+ "WHERE "
	    					+ "shop_id = ? "
	    					+ "AND "
	    					+ "shop_code = ? "
	    					+ "AND "
	    					+ "(bill_date "
							+ "BETWEEN "
							+ "STR_TO_DATE(?,'%d-%m-%Y') "
							+ "AND "
							+ "STR_TO_DATE(?,'%d-%m-%Y'))";
					
					preparedStatement = connection.prepareStatement(sql);
					
					preparedStatement.setString(1, shopId);
					preparedStatement.setString(2, shopCode);
					preparedStatement.setString(3, this.getFromDate());
					preparedStatement.setString(4, this.getToDate());
					
					resultSet = preparedStatement.executeQuery();
					
				
					
					this.setTotalBilling(0.0F);
					this.setTotalCashCollection(0.0F);
					this.setTotalCollection(0.0F);
					this.setTotalCreditCollection(0.0F);
					this.setTotalCreditGiven(0.0F);
					
					
	    			while(resultSet.next())	    				
	    			{
	    				
    				if(resultSet.getString(1) != null && resultSet.getString(2) != null && resultSet.getString(3) != null)
    				{
	    				this.setTotalBilling(this.getTotalBilling() + (Float.parseFloat(resultSet.getString(1))));
	    				
	    				float amtReceived = Float.parseFloat(resultSet.getString(2));
	    				float amountReturn  = Float.parseFloat(resultSet.getString(3));
	    				
	    				if(amountReturn > 0 )
	    				{
	    					this.setTotalCashCollection(this.getTotalCashCollection() + Float.parseFloat(resultSet.getString(1)));
	    				}
	    				else
	    				{
	    					this.setTotalCashCollection(this.getTotalCashCollection() + amtReceived);
	    					this.setTotalCreditGiven(this.getTotalCreditGiven()+
	    							+ (Float.parseFloat(resultSet.getString(1)) - amtReceived));
	    				}
	    				
	    				
	    			}
	    			}
	    			
	    			sql = "SELECT SUM(payment_amount) "
	    					+ "FROM "
	    					+ "shop_customer_payment_master "
	    					+ "WHERE "
	    					+ "shop_id = ? "
	    					+ "AND "
	    					+ "shop_code = ? "
	    					+ "AND "
	    					+ "(entry_date "
	    					+ "BETWEEN "
			    			+ "STR_TO_DATE(?,'%d-%m-%Y') "
							+ "AND "
							+ "STR_TO_DATE(?,'%d-%m-%Y'))";
	    			
	    			PreparedStatement ps = connection.prepareStatement(sql);
	    			
	    			ps.setString(1, shopId);
	    			ps.setString(2, shopCode);
	    			ps.setString(3, this.getFromDate());
	    			ps.setString(4, this.getToDate());
	    			ResultSet rs = ps.executeQuery();
	    			
	    			
	    			if(rs.next())
	    			{
	    				try
	    				{
    	    				if(rs.getString(1) != null)
    	    				{
    	    				
    	    					this.setTotalCreditCollection(rs.getFloat(1));
    	    				}
    	    				else
    	    				{
    	    					this.setTotalCreditCollection(0.0F);
    	    				}
	    				}catch(NumberFormatException nfe)
	    				{}
	    			}
	    			if(rs != null)
	    			{
	    				rs.close();
	    				rs = null;
	    			}
	    			if(ps != null)
	    			{
	    				ps.close();
	    				ps = null;
	    			}
	    			
	    			this.setTotalCollection(this.getTotalCreditCollection()+this.getTotalCashCollection());
					
	    			//System.out.println("ItemWiseSaleList size = " + this.getItemWiseSaleList().size());
	    			
					if(this.getItemWiseSaleList().size()>0)
					{
						
						
						try
						{
							String name = (String) this.getSessionMap().get("shopnameenglish");
							String address = (String) this.getSessionMap().get("shopaddresseng");
							String path= this.getRequest().getSession().getServletContext().getRealPath("/images/");
							
							
							 java.util.Date date = new java.util.Date();
				        	SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
				        	String reportDate = dateFormat.format(date);
							
							
				       	     long t = date.getTime();	        	     
				       	     java.sql.Time time = new java.sql.Time(t);
				       	     
							PDDocument document = new PDDocument(); 
							PDPage page = null;
							
							 page = new PDPage(PDRectangle.A4);
							
							 
							 document.addPage(page);
							 
							 
					         PDPageContentStream contentStream = new PDPageContentStream(document, page);
							 
					         int pageCount = 1;
					         
					         Table header;
					         
					         
					         
					         
					         TableBuilder tableHeaderBuilder = Table.builder()
						                .addColumnsOfWidth(585)				                
						                .font(HELVETICA)
						                .borderColor(Color.BLACK)
						                .horizontalAlignment(CENTER);
					         
					         Row dairyNameHeader = Row.builder()
						                .add(TextCell.builder().text(name).horizontalAlignment(CENTER).borderWidth(0).lineSpacing(0.5F).build())				               
						                .backgroundColor(WHITE)
						                .textColor(Color.BLACK)
						                .font(PDType1Font.HELVETICA_BOLD).fontSize(16)				              
						                .horizontalAlignment(CENTER)
						                .build();
					         
					         Row addressRow = Row.builder()
						                .add(TextCell.builder().text(address).horizontalAlignment(CENTER).borderWidth(0).lineSpacing(0.5F).build())				               
						                .backgroundColor(WHITE)
						                .textColor(Color.BLACK)
						                .font(PDType1Font.HELVETICA_BOLD).fontSize(8)				              
						                .horizontalAlignment(CENTER)
						                .build();
					         
					         
					         Row reportInfo1 = Row.builder()
						                .add(TextCell.builder()
						                		.text("Item Wise Sale Report From : "+this.getFromDate()+" to Date:"+this.getToDate()+" "+time)
						                		.horizontalAlignment(CENTER).borderWidth(0).lineSpacing(0.5F).build())				               
						                .backgroundColor(WHITE)
						                .textColor(Color.BLACK)
						                .font(PDType1Font.HELVETICA_BOLD).fontSize(10)				              
						                .horizontalAlignment(CENTER)
						                .build();
					         
					         
					         
					         
					         tableHeaderBuilder.addRow(dairyNameHeader);
					         tableHeaderBuilder.addRow(addressRow);
					         tableHeaderBuilder.addRow(reportInfo1);
					        
					         
					         header = tableHeaderBuilder.build();
					         
					         float startY = page.getMediaBox().getHeight() - PADDING20;
					         TableDrawer.builder()
				                .contentStream(contentStream)
				                .table(header)
				                .startX(PADDING30)
				                .startY(startY)
				                .build()
				                .draw();
							
					         
					         Table stockReportHeaderInfo;
					         TableBuilder tableStockInfoBuilder = Table.builder()
						                .addColumnsOfWidth(30,180,115,60,60,60,80)				                
						                .font(HELVETICA)
						                .borderWidth(0.5F)
						                .borderColor(Color.BLACK)
						                .horizontalAlignment(CENTER);
					         
					         
					         Row stockTableHeader = Row.builder()
					        		  	.add(TextCell.builder().text("SrNo").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())					              
						                .add(TextCell.builder().text("Product Name").horizontalAlignment(CENTER).fontSize(8).borderWidth(0.5F).lineSpacing(0.5F).build())				               
						                .add(TextCell.builder().text("Quantity").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
						                .add(TextCell.builder().text("Purchase amount").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())							               
						                .add(TextCell.builder().text("Bill Amount").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
						                .add(TextCell.builder().text("Discount").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
						                .add(TextCell.builder().text("Amount After Discount").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())								           
						                .backgroundColor(Color.LIGHT_GRAY)
						                .textColor(Color.BLACK)
						                .font(PDType1Font.HELVETICA_BOLD).fontSize(8)				              
						                .horizontalAlignment(CENTER)
						                .build();
					         
					         tableStockInfoBuilder.addRow(stockTableHeader);
					         stockReportHeaderInfo = tableStockInfoBuilder.build();
					        		
					        		 TableDrawer.builder()
						                .contentStream(contentStream)
						                .table(stockReportHeaderInfo)
						                .startX(PADDING5)
						                .startY(startY-50)
						                .build()
						                .draw();
					         
					        		
					        		int srNo = 1;

					        		float productTableStartY = startY - 75F;

					        		List<Row> productRows = new ArrayList<Row>();


					        		
					        		// LOOP THROUGH ALL PRODUCTS
					        		
					        		
					        		for(ItemWiseSaleReportDAO detailsDAO : this.getItemWiseSaleList())
					        		{

					        		  
					        		    // CREATE PRODUCT ROW
					        		   

					        		    Row productRow = Row.builder()

					        		            .add(TextCell.builder()
					        		                    .text("" + srNo)
					        		                    .horizontalAlignment(CENTER)
					        		                    .borderWidth(0.5F)
					        		                    .lineSpacing(0.5F)
					        		                    .build())

					        		            .add(TextCell.builder()
					        		                    .text(detailsDAO.getProductName())
					        		                    .fontSize(9)
					        		                    .horizontalAlignment(LEFT)
					        		                    .borderWidth(0.5F)
					        		                    .lineSpacing(0.5F)
					        		                    .build())

					        		            .add(TextCell.builder()
					        		                    .text(detailsDAO.getQuantity()
					        		                            + " "
					        		                            + detailsDAO.getUnits())
					        		                    .horizontalAlignment(LEFT)
					        		                    .borderWidth(0.5F)
					        		                    .lineSpacing(0.5F)
					        		                    .build())

					        		            .add(TextCell.builder()
					        		                    .text(detailsDAO.getTotalPurchaseAmount())
					        		                    .horizontalAlignment(RIGHT)
					        		                    .borderWidth(0.5F)
					        		                    .lineSpacing(0.5F)
					        		                    .build())

					        		            .add(TextCell.builder()
					        		                    .text(df.format(Double.parseDouble(detailsDAO.getTotalBillAmount())))
					        		                    .horizontalAlignment(RIGHT)
					        		                    .borderWidth(0.5F)
					        		                    .lineSpacing(0.5F)
					        		                    .build())

					        		            .add(TextCell.builder()
					        		                    .text(df.format(Double.parseDouble(detailsDAO.getTotalDiscountAmount())))
					        		                    .horizontalAlignment(RIGHT)
					        		                    .borderWidth(0.5F)
					        		                    .lineSpacing(0.5F)
					        		                    .build())

					        		            .add(TextCell.builder()
					        		                    .text(df.format(Double.parseDouble(detailsDAO.getTotalBillAmountAfterDiscount())))
					        		                    .horizontalAlignment(RIGHT)
					        		                    .borderWidth(0.5F)
					        		                    .lineSpacing(0.5F)
					        		                    .build())

					        		            .backgroundColor(Color.WHITE)
					        		            .textColor(Color.BLACK)
					        		            .font(PDType1Font.HELVETICA)
					        		            .fontSize(12)
					        		            .horizontalAlignment(CENTER)
					        		            .build();


					        		    productRows.add(productRow);


					        		    

					        		    TableBuilder tempBuilder =
					        		            Table.builder()
					        		                    .addColumnsOfWidth(30,180,115,60,60,60,80)	
					        		                    .font(HELVETICA)
					        		                    .borderWidth(0.5F)
					        		                    .borderColor(Color.BLACK)
					        		                    .horizontalAlignment(CENTER);


					        		    for(Row row : productRows)
					        		    {
					        		        tempBuilder.addRow(row);
					        		    }


					        		    Table tempTable = tempBuilder.build();


					        		    
					        		    // CHECK AVAILABLE SPACE
					        		   

					        		    float availableHeight =
					        		            productTableStartY - 70F;


					        		   
					        		    // CURRENT PAGE FULL
					        		  

					        		    if(tempTable.getHeight() > availableHeight)
					        		    {

					        		       
					        		        // Remove current row.
					        		        // It belongs to next page.
					        		        

					        		        productRows.remove(productRows.size() - 1);


					        		      
					        		        // Build table for current page
					        		        

					        		        TableBuilder currentPageBuilder =
					        		                Table.builder()
					        		                        .addColumnsOfWidth(30,180,115,60,60,60,80)	
					        		                        .font(HELVETICA)
					        		                        .borderWidth(0.5F)
					        		                        .borderColor(Color.BLACK)
					        		                        .horizontalAlignment(CENTER);


					        		        for(Row row : productRows)
					        		        {
					        		            currentPageBuilder.addRow(row);
					        		        }


					        		        Table currentPageProductTable =
					        		                currentPageBuilder.build();


					        		        
					        		        // DRAW CURRENT PAGE PRODUCTS
					        		       

					        		        TableDrawer.builder()
					        		                .contentStream(contentStream)
					        		                .table(currentPageProductTable)
					        		                .startX(PADDING5)
					        		                .startY(productTableStartY)
					        		                .build()
					        		                .draw();


					        		       
					        		        // CLOSE CURRENT PAGE
					        		        

					        		        contentStream.close();


					        		       
					        		        // CREATE NEW PAGE
					        		       

					        		        PDPage newPage =
					        		                new PDPage(PDRectangle.A4);

					        		        document.addPage(newPage);


					        		        contentStream =
					        		                new PDPageContentStream(document, newPage);


					        		        startY =
					        		                newPage.getMediaBox().getHeight()
					        		                        - PADDING20;


					        		       
					        		        // REPORT HEADER ON NEW PAGE
					        		        

					        		        TableBuilder tableHeaderBuilderOtherPage =
					        		                Table.builder()
					        		                        .addColumnsOfWidth(585)
					        		                        .font(HELVETICA)
					        		                        .borderColor(Color.BLACK)
					        		                        .horizontalAlignment(CENTER);


					        		        Row dairyNameHeaderOtherPage =
					        		                Row.builder()

					        		                        .add(TextCell.builder()
					        		                                .text(name)
					        		                                .horizontalAlignment(CENTER)
					        		                                .borderWidth(0)
					        		                                .lineSpacing(0.5F)
					        		                                .build())

					        		                        .backgroundColor(WHITE)
					        		                        .textColor(Color.BLACK)
					        		                        .font(PDType1Font.HELVETICA_BOLD)
					        		                        .fontSize(16)
					        		                        .horizontalAlignment(CENTER)
					        		                        .build();


					        		        Row addressRowOtherPage =
					        		                Row.builder()

					        		                        .add(TextCell.builder()
					        		                                .text(address)
					        		                                .horizontalAlignment(CENTER)
					        		                                .borderWidth(0)
					        		                                .lineSpacing(0.5F)
					        		                                .build())

					        		                        .backgroundColor(WHITE)
					        		                        .textColor(Color.BLACK)
					        		                        .font(PDType1Font.HELVETICA_BOLD)
					        		                        .fontSize(8)
					        		                        .horizontalAlignment(CENTER)
					        		                        .build();


					        		        Row reportInfo1OtherPage =
					        		                Row.builder()

					        		                        .add(TextCell.builder()
					        		                                .text("Item Wise Sale Report From : "
					        		                                        + this.getFromDate()
					        		                                        + " to Date:"
					        		                                        + this.getToDate()
					        		                                        + " " + time)
					        		                                .horizontalAlignment(CENTER)
					        		                                .borderWidth(0)
					        		                                .lineSpacing(0.5F)
					        		                                .build())

					        		                        .backgroundColor(WHITE)
					        		                        .textColor(Color.BLACK)
					        		                        .font(PDType1Font.HELVETICA_BOLD)
					        		                        .fontSize(12)
					        		                        .horizontalAlignment(CENTER)
					        		                        .build();


					        		        tableHeaderBuilderOtherPage.addRow(
					        		                dairyNameHeaderOtherPage);

					        		        tableHeaderBuilderOtherPage.addRow(
					        		                addressRowOtherPage);

					        		        tableHeaderBuilderOtherPage.addRow(
					        		                reportInfo1OtherPage);


					        		        Table headerOtherPage =
					        		                tableHeaderBuilderOtherPage.build();


					        		        TableDrawer.builder()
					        		                .contentStream(contentStream)
					        		                .table(headerOtherPage)
					        		                .startX(PADDING30)
					        		                .startY(startY)
					        		                .build()
					        		                .draw();


					        		        
					        		        // PRODUCT HEADER
					        		        

					        		        TableDrawer.builder()
					        		                .contentStream(contentStream)
					        		                .table(stockReportHeaderInfo)
					        		                .startX(PADDING5)
					        		                .startY(startY - 50)
					        		                .build()
					        		                .draw();


					        		      
					        		        // RESET PRODUCT START POSITION
					        		       

					        		        productTableStartY =
					        		                startY - 75F;


					        		       
					        		        // START NEW PRODUCT LIST
					        		       

					        		        productRows = new ArrayList<Row>();


					        		      
					        		        // ADD CURRENT PRODUCT TO NEW PAGE
					        		       

					        		        productRows.add(productRow);
					        		    }


					        		    srNo++;
					        		}


					        		
					        		// CREATE TOTAL ROW
					        		

					        		Row totalTableInfoRow =
					        		        Row.builder()

					        		                .add(TextCell.builder()
					        		                        .text("Total")
					        		                        .colSpan(3)
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .lineSpacing(0.5F)
					        		                        .build())

					        		                .add(TextCell.builder()
					        		                        .text(this.getAllPurchaseAmount())
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .lineSpacing(0.5F)
					        		                        .build())

					        		                .add(TextCell.builder()
					        		                        .text(this.getAllBillAmount())
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .lineSpacing(0.5F)
					        		                        .build())

					        		                .add(TextCell.builder()
					        		                        .text(this.getAllDiscount())
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .lineSpacing(0.5F)
					        		                        .build())

					        		                .add(TextCell.builder()
					        		                        .text(this.getAllBillAmountAfterDis())
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .lineSpacing(0.5F)
					        		                        .build())

					        		                .backgroundColor(Color.WHITE)
					        		                .textColor(Color.BLACK)
					        		                .font(PDType1Font.HELVETICA_BOLD)
					        		                .fontSize(9)
					        		                .horizontalAlignment(CENTER)
					        		                .height(25F)
					        		                .build();


					        		
					        		// ADD TOTAL TO FINAL PRODUCT PAGE
					        		

					        		productRows.add(totalTableInfoRow);


					        		
					        		// BUILD FINAL PRODUCT TABLE
					        		

					        		TableBuilder finalProductBuilder =
					        		        Table.builder()
					        		                .addColumnsOfWidth(30,180,115,60,60,60,80)	
					        		                .font(HELVETICA)
					        		                .borderWidth(0.5F)
					        		                .borderColor(Color.BLACK)
					        		                .horizontalAlignment(CENTER);


					        		for(Row row : productRows)
					        		{
					        		    finalProductBuilder.addRow(row);
					        		}


					        		Table finalProductTable =
					        		        finalProductBuilder.build();


					        		float finalProductTableHeight =
					        		        finalProductTable.getHeight();


					        		
					        		// CHECK WHETHER FINAL PRODUCT TABLE FITS
					        		

					        		float finalAvailableHeight =
					        		        productTableStartY - 50F;


					        		if(finalProductTableHeight > finalAvailableHeight)
					        		{

					        		    // --------------------------------------------------------
					        		    // Remove Total from current page
					        		    // --------------------------------------------------------

					        		    productRows.remove(productRows.size() - 1);


					        		    // --------------------------------------------------------
					        		    // Build products without Total
					        		    // --------------------------------------------------------

					        		    TableBuilder lastProductBuilder =
					        		            Table.builder()
					        		                    .addColumnsOfWidth(30,180,115,60,60,60,80)	
					        		                    .font(HELVETICA)
					        		                    .borderWidth(0.5F)
					        		                    .borderColor(Color.BLACK)
					        		                    .horizontalAlignment(CENTER);


					        		    for(Row row : productRows)
					        		    {
					        		        lastProductBuilder.addRow(row);
					        		    }


					        		    Table lastProductTable =
					        		            lastProductBuilder.build();


					        		    // --------------------------------------------------------
					        		    // Draw remaining products
					        		    // --------------------------------------------------------

					        		    TableDrawer.builder()
					        		            .contentStream(contentStream)
					        		            .table(lastProductTable)
					        		            .startX(PADDING5)
					        		            .startY(productTableStartY)
					        		            .build()
					        		            .draw();


					        		    // --------------------------------------------------------
					        		    // New page for Total
					        		    // --------------------------------------------------------

					        		    contentStream.close();


					        		    PDPage newPage =
					        		            new PDPage(PDRectangle.A4);

					        		    document.addPage(newPage);


					        		    contentStream =
					        		            new PDPageContentStream(document, newPage);


					        		    startY =
					        		            newPage.getMediaBox().getHeight()
					        		                    - PADDING20;


					        		    // Header
					        		    TableDrawer.builder()
					        		            .contentStream(contentStream)
					        		            .table(header)
					        		            .startX(PADDING30)
					        		            .startY(startY)
					        		            .build()
					        		            .draw();


					        		    // Product header
					        		    TableDrawer.builder()
					        		            .contentStream(contentStream)
					        		            .table(stockReportHeaderInfo)
					        		            .startX(PADDING5)
					        		            .startY(startY - 50)
					        		            .build()
					        		            .draw();


					        		    productTableStartY =
					        		            startY - 75F;


					        		    // --------------------------------------------------------
					        		    // Create table containing ONLY Total
					        		    // --------------------------------------------------------

					        		    TableBuilder totalBuilder =
					        		            Table.builder()
					        		                    .addColumnsOfWidth(30,180,115,60,60,60,80)	
					        		                    .font(HELVETICA)
					        		                    .borderWidth(0.5F)
					        		                    .borderColor(Color.BLACK)
					        		                    .horizontalAlignment(CENTER);


					        		    totalBuilder.addRow(totalTableInfoRow);


					        		    finalProductTable =
					        		            totalBuilder.build();
					        		}


					        		
					        		// DRAW FINAL PRODUCT TABLE
					        		

					        		TableDrawer.builder()
					        		        .contentStream(contentStream)
					        		        .table(finalProductTable)
					        		        .startX(PADDING5)
					        		        .startY(productTableStartY)
					        		        .build()
					        		        .draw();


					        		
					        		// CALCULATE WHERE PRODUCT TABLE ENDS
					        		

					        		float productTableEndY =
					        		        productTableStartY
					        		                - finalProductTable.getHeight();


					        		
					        		// NOW CREATE COLLECTION TABLE
					        		

					        		TableBuilder collectionRowBuilder =
					        		        Table.builder()
					        		                .addColumnsOfWidth(200,130)
					        		                .font(HELVETICA)
					        		                .borderWidth(0.5F)
					        		                .borderColor(Color.BLACK)
					        		                .horizontalAlignment(RIGHT);


					        		// Collection Header

					        		Row collectionInfoRow =
					        		        Row.builder()
					        		                .add(TextCell.builder()
					        		                        .text("Collection Report (in Rs.)")
					        		                        .colSpan(2)
					        		                        .horizontalAlignment(CENTER)
					        		                        .borderWidth(0.5F)
					        		                        .lineSpacing(0.5F)
					        		                        .build())
					        		                .backgroundColor(Color.LIGHT_GRAY)
					        		                .textColor(Color.BLACK)
					        		                .font(PDType1Font.HELVETICA_BOLD)
					        		                .fontSize(11)
					        		                .horizontalAlignment(RIGHT)
					        		                .height(25F)
					        		                .build();


					        		// Row 1

					        		Row collectionInfoRow1 =
					        		        Row.builder()
					        		                .add(TextCell.builder()
					        		                        .text("Total Billing Amt Aft Dis")
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .build())

					        		                .add(TextCell.builder()
					        		                        .text(df.format(this.getTotalBilling()))
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .build())

					        		                .backgroundColor(Color.WHITE)
					        		                .textColor(Color.BLACK)
					        		                .font(PDType1Font.HELVETICA_BOLD)
					        		                .fontSize(11)
					        		                .height(25F)
					        		                .build();


					        		// Row 2

					        		Row collectionInfoRow2 =
					        		        Row.builder()
					        		                .add(TextCell.builder()
					        		                        .text("Total Cash Collected")
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .build())

					        		                .add(TextCell.builder()
					        		                        .text(df.format(this.getTotalCashCollection()))
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .build())

					        		                .backgroundColor(Color.WHITE)
					        		                .textColor(Color.BLACK)
					        		                .font(PDType1Font.HELVETICA_BOLD)
					        		                .fontSize(11)
					        		                .height(25F)
					        		                .build();


					        		// Row 3

					        		Row collectionInfoRow3 =
					        		        Row.builder()
					        		                .add(TextCell.builder()
					        		                        .text("Total Credit Collected")
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .build())

					        		                .add(TextCell.builder()
					        		                        .text(df.format(this.getTotalCreditCollection()))
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .build())

					        		                .backgroundColor(Color.WHITE)
					        		                .textColor(Color.BLACK)
					        		                .font(PDType1Font.HELVETICA_BOLD)
					        		                .fontSize(11)
					        		                .height(25F)
					        		                .build();


					        		// Row 4

					        		Row collectionInfoRow4 =
					        		        Row.builder()
					        		                .add(TextCell.builder()
					        		                        .text("Total Credit Given")
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .build())

					        		                .add(TextCell.builder()
					        		                        .text(df.format(this.getTotalCreditGiven()))
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .build())

					        		                .backgroundColor(Color.WHITE)
					        		                .textColor(Color.BLACK)
					        		                .font(PDType1Font.HELVETICA_BOLD)
					        		                .fontSize(11)
					        		                .height(25F)
					        		                .build();


					        		// Row 5

					        		Row collectionInfoRow5 =
					        		        Row.builder()
					        		                .add(TextCell.builder()
					        		                        .text("Total Collection")
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .build())

					        		                .add(TextCell.builder()
					        		                        .text(df.format(this.getTotalCollection()))
					        		                        .horizontalAlignment(RIGHT)
					        		                        .borderWidth(0.5F)
					        		                        .build())

					        		                .backgroundColor(Color.WHITE)
					        		                .textColor(Color.BLACK)
					        		                .font(PDType1Font.HELVETICA_BOLD)
					        		                .fontSize(9)
					        		                .height(25F)
					        		                .build();


					        		collectionRowBuilder.addRow(collectionInfoRow);
					        		collectionRowBuilder.addRow(collectionInfoRow1);
					        		collectionRowBuilder.addRow(collectionInfoRow2);
					        		collectionRowBuilder.addRow(collectionInfoRow3);
					        		collectionRowBuilder.addRow(collectionInfoRow4);
					        		collectionRowBuilder.addRow(collectionInfoRow5);


					        		Table collectionTable =
					        		        collectionRowBuilder.build();


					        		
					        		// CHECK COLLECTION TABLE SPACE
					        		

					        		float collectionTableHeight =
					        		        collectionTable.getHeight();

					        		float gap = 10F;

					        		float bottomMargin = 50F;


					        		float collectionAvailableSpace =
					        		        productTableEndY
					        		                - gap
					        		                - bottomMargin;


					        		// ============================================================
					        		// COLLECTION FITS ON CURRENT PAGE
					        		// ============================================================

					        		if(collectionTableHeight <= collectionAvailableSpace)
					        		{

					        		    float collectionStartY =
					        		            productTableEndY - gap;


					        		    TableDrawer.builder()
					        		            .contentStream(contentStream)
					        		            .table(collectionTable)
					        		            .startX(PADDING5)
					        		            .startY(collectionStartY)
					        		            .build()
					        		            .draw();
					        		}


					        		// ============================================================
					        		// COLLECTION DOES NOT FIT
					        		// ============================================================

					        		else
					        		{

					        		    contentStream.close();


					        		    // ========================================================
					        		    // NEW PAGE
					        		    // ========================================================

					        		    PDPage newPage =
					        		            new PDPage(PDRectangle.A4);

					        		    document.addPage(newPage);


					        		    contentStream =
					        		            new PDPageContentStream(document, newPage);


					        		    float newPageStartY =
					        		            newPage.getMediaBox().getHeight()
					        		                    - PADDING20;


					        		    // ========================================================
					        		    // DRAW REPORT HEADER
					        		    // ========================================================

					        		    TableDrawer.builder()
					        		            .contentStream(contentStream)
					        		            .table(header)
					        		            .startX(PADDING30)
					        		            .startY(newPageStartY)
					        		            .build()
					        		            .draw();


					        		    // ========================================================
					        		    // COLLECTION REPORT
					        		    // ========================================================

					        		    float collectionStartY =
					        		            newPageStartY
					        		                    - header.getHeight()
					        		                    - 20F;


					        		    TableDrawer.builder()
					        		            .contentStream(contentStream)
					        		            .table(collectionTable)
					        		            .startX(PADDING5)
					        		            .startY(collectionStartY)
					        		            .build()
					        		            .draw();
					        		}
					        		 
					        		 
					        		 
					        		
							         
							        contentStream.close();
									 	document.save(path+"/itemwisesale-report.pdf");
								        document.close();
								        
								        

										response.setHeader("Content-disposition", "inline; filename=itemwisesale-report.pdf");
										response.setContentType("application/pdf");
										FileInputStream fileInputStream = new FileInputStream(path+"/itemwisesale-report.pdf");
										PrintWriter out = response.getWriter();  
										int bytes;
										while ((bytes = fileInputStream.read()) != -1) {
											out.write(bytes);
										}
										fileInputStream.close();
										out.flush();
										out.close();
					
						
	        	}catch (Exception e) {
					e.printStackTrace();
				}finally {
					if(resultSet != null)
    				{
    					try {
    						resultSet.close();resultSet = null;
    					} catch (SQLException e) {
    						
    					}
    				}
					if(preparedStatement != null)
					{
						try {preparedStatement.close(); preparedStatement = null;} catch (SQLException e) {}
					}
					if(connection != null)
					{
						
						try {connection.close(); connection = null;}catch (SQLException e) {}
					}
				
				}
	
			}
					
	    		
	   }catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(resultSet != null)
			{
				try {
					resultSet.close();resultSet = null;
				} catch (SQLException e) {
					
				}
			}
			if(preparedStatement != null)
			{
				try {preparedStatement.close(); preparedStatement = null;} catch (SQLException e) {}
			}
			if(connection != null)
			{
				
				try {connection.close(); connection = null;}catch (SQLException e) {}
			}
		
		}
}
	
	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;
	}
	public HttpServletRequest getRequest() {
		return request;
	}

	public void setRequest(HttpServletRequest request) {
		this.request = request;
	}

	public HttpServletResponse getResponse() {
		return response;
	}

	public void setResponse(HttpServletResponse response) {
		this.response = response;
	}

	
	public void setServletResponse(HttpServletResponse httpServletResponse) {
		this.response = httpServletResponse;
	}

	
	public void setServletRequest(HttpServletRequest httpServletRequest) {
		this.request = httpServletRequest;
	}

	




	public static long getSerialversionuid() {
		return serialVersionUID;
	}

	public String getFromDate() {
		return fromDate;
	}

	public void setFromDate(String fromDate) {
		this.fromDate = fromDate;
	}

	public String getToDate() {
		return toDate;
	}

	public void setToDate(String toDate) {
		this.toDate = toDate;
	}
	public List<ItemWiseSaleReportDAO> getItemWiseSaleList() {
		return itemWiseSaleList;
	}
	public void setItemWiseSaleList(List<ItemWiseSaleReportDAO> itemWiseSaleList) {
		this.itemWiseSaleList = itemWiseSaleList;
	}
	public String getAllBillAmount() {
		return allBillAmount;
	}
	public void setAllBillAmount(String allBillAmount) {
		this.allBillAmount = allBillAmount;
	}
	public String getAllDiscount() {
		return allDiscount;
	}
	public void setAllDiscount(String allDiscount) {
		this.allDiscount = allDiscount;
	}
	public String getAllBillAmountAfterDis() {
		return allBillAmountAfterDis;
	}
	public void setAllBillAmountAfterDis(String allBillAmountAfterDis) {
		this.allBillAmountAfterDis = allBillAmountAfterDis;
	}
	public Float getTotalCashCollection() {
		return totalCashCollection;
	}
	public void setTotalCashCollection(Float totalCashCollection) {
		this.totalCashCollection = totalCashCollection;
	}
	public Float getTotalCreditCollection() {
		return totalCreditCollection;
	}
	public void setTotalCreditCollection(Float totalCreditCollection) {
		this.totalCreditCollection = totalCreditCollection;
	}
	
	public Float getTotalCreditGiven() {
		return totalCreditGiven;
	}
	public void setTotalCreditGiven(Float totalCreditGiven) {
		this.totalCreditGiven = totalCreditGiven;
	}
	public Float getTotalBilling() {
		return totalBilling;
	}
	public void setTotalBilling(Float totalBilling) {
		this.totalBilling = totalBilling;
	}
	public Float getTotalCollection() {
		return totalCollection;
	}
	public void setTotalCollection(Float totalCollection) {
		this.totalCollection = totalCollection;
	}
	public String getAllPurchaseAmount() {
		return allPurchaseAmount;
	}
	public void setAllPurchaseAmount(String allPurchaseAmount) {
		this.allPurchaseAmount = allPurchaseAmount;
	}
	
	
	
}

	