package com.reallinkbilling.download;

import static java.awt.Color.WHITE;
import static org.apache.pdfbox.pdmodel.font.PDType1Font.HELVETICA;
import static org.vandeseer.easytable.settings.HorizontalAlignment.CENTER;
import static org.vandeseer.easytable.settings.HorizontalAlignment.LEFT;
import static org.vandeseer.easytable.settings.HorizontalAlignment.RIGHT;

import java.awt.Color;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
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.LinkedHashMap;
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.fontbox.ttf.TTFParser;
import org.apache.fontbox.ttf.TrueTypeFont;
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.PDFont;
import org.apache.pdfbox.pdmodel.font.PDType0Font;
import org.apache.pdfbox.pdmodel.font.PDType1Font;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
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.GSTPurchaseInvoiceInfoDAO;
import com.reallinkbilling.db.GetDBConnection;

public class DownloadPurchaseGstReport extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = 899359324049622561L;
	private HttpSession session;
	
	public HttpServletRequest request;
	public HttpServletResponse response;
	private SessionMap<String,Object> sessionMap;
	  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 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 List<GSTPurchaseInvoiceInfoDAO> fromDateToGSTReportDAOList = new ArrayList<GSTPurchaseInvoiceInfoDAO>();
	
		private String fromDate;
		private String toDate;
		private String reportFormat;
	
	public void download(){
	
			this.setSession(ServletActionContext.getRequest().getSession(false)); 
		
	        if(this.getSession()==null || this.getSession().getAttribute("login")==null){ 
	        	addActionMessage("Please Login First !!!");
	         }  
	        else{
	        	
	        	Connection connection = null;
				PreparedStatement preparedStatement= null;
				ResultSet  resultSet = null;
				
				String sql = "";
				String db = (String)  this.getSessionMap().get("db");
				
				try
				{
					DecimalFormat df = new DecimalFormat("##.##");
					DecimalFormat df2 = new DecimalFormat("##");
					connection = GetDBConnection.getDBConnection(db);
					
					
					LinkedHashMap<String,GSTPurchaseInvoiceInfoDAO> purchaseInvoiceMap = new LinkedHashMap<String,GSTPurchaseInvoiceInfoDAO>();
					String shopId = (String) this.getSessionMap().get("shopId");
		    		String shopCode = (String) this.getSessionMap().get("shopCode");
		    		
					 sql = "SELECT "
						  		+ "pi.purchase_invoice_number,"
						  		+ "DATE_FORMAT(pi.purchase_date,'%d-%m-%Y'),"
						  		+ "pi.supplier_id,"
						  		+ "pi.supplier_name,"
						  		+ "supplier.supplier_address,"
						  		+ "supplier.gst_number,"
						  		+ "pi.total_purchase_amount,"
						  		+ "pi.transportation_charges,"
						  		+ "pi.tcs_amount "
						  		+ "FROM "
						  		+ "shop_purchase_info_master as pi "
						  		+ "INNER JOIN "
						  		+ "shop_supplier_master as supplier "
						  		+ "ON "
						  		+ "pi.supplier_id = supplier.supplier_id "
						  		+ "WHERE "
						  		+ "(pi.purchase_date  "
						  		+ "BETWEEN "
						  		+ "STR_TO_DATE(?,'%d-%m-%Y') "
						  		+ "AND "
						  		+ "STR_TO_DATE(?,'%d-%m-%Y')) "					  		
						  		+ "AND "
						  		+ "pi.shop_id = ? "
						  		+ "AND "
						  		+ "pi.shop_code = ? "
						  		+ "AND "
						  		+ "supplier.shop_id = ? "
						  		+ "AND "
						  		+ "supplier.shop_code = ? "
						  		+ "ORDER BY pi.purchase_date";
					  
					  preparedStatement = connection.prepareStatement(sql);
					  
					  preparedStatement.setString(1, this.getFromDate());
					  preparedStatement.setString(2, this.getToDate());
					  preparedStatement.setString(3, shopId);				  
					  preparedStatement.setString(4, shopCode);
					  preparedStatement.setString(5, shopId);				  
					  preparedStatement.setString(6, shopCode);
					   
					//  System.out.println(preparedStatement.toString());
					  
					  resultSet = preparedStatement.executeQuery();
					  
					  while(resultSet.next())
					  {
						  GSTPurchaseInvoiceInfoDAO dao = new GSTPurchaseInvoiceInfoDAO();
						
						  dao.setInvoiceNumber(resultSet.getString(1));
						  dao.setInvoiceDate(resultSet.getString(2));
						  dao.setSupplierId(resultSet.getString(3));
						  dao.setSupplierName(resultSet.getString(4));
						  dao.setSupllierAddress(resultSet.getString(5));
						  dao.setSupplierGSTNumber(resultSet.getString(6));
						  dao.setTotalAmount(resultSet.getString(7));
						  dao.setFrieghtCharges(resultSet.getFloat(8));
						  dao.setTcsAmount(resultSet.getFloat(9));
						  
						  purchaseInvoiceMap.put(resultSet.getString(1), dao);
					  }
					  
					  
					  if(resultSet != null)
					  {
						  resultSet.close();
						  resultSet = null;
					  }
					  
					  if(preparedStatement != null)
					  {
						  preparedStatement.close();
						  preparedStatement = null;
					  }
					  int srNo = 1;
					  for(String purchaseInvoiceNumber:purchaseInvoiceMap.keySet())
					  {
						  
						  String getProductInfoSQL = "";
						  PreparedStatement getProductInfoPSMT = null;
						  ResultSet getProductInfoRS = null;
						  
						  GSTPurchaseInvoiceInfoDAO dao = purchaseInvoiceMap.get(purchaseInvoiceNumber);
						  
						  getProductInfoSQL = "SELECT "
						  		+ "product_id,"
						  		+ "product_name,"
						  		+ "product_total_amount,"
						  		+ "puroduct_quantity,"
						  		+ "purchase_rate,"
						  		+ "product_net_amount,"
						  		+ "product_igst_amount,"
						  		+ "product_cgst_amount,"
						  		+ "product_sgst_amount "
						  		+ "FROM "
						  		+ "shop_purchase_product_info_master "
						  		+ "WHERE "
						  		+ "purchase_invoice_number = ? "
						  		+ "AND "
						  		+ "shop_id = ? "
						  		+ "AND "
						  		+ "shop_code = ? ";
						  
						  getProductInfoPSMT = connection.prepareStatement(getProductInfoSQL);
						  
						  getProductInfoPSMT.setString(1, purchaseInvoiceNumber);
						  getProductInfoPSMT.setString(2, shopId);				  
						  getProductInfoPSMT.setString(3, shopCode);
						  getProductInfoRS = getProductInfoPSMT.executeQuery();
						  
						  
						 @SuppressWarnings("unchecked")
						HashMap<String,String> productHSNcode = (HashMap<String, String>) this.getSessionMap().get("productHSNcode");
				
						 while(getProductInfoRS.next())
						  {
							 GSTPurchaseInvoiceInfoDAO dao2 = new GSTPurchaseInvoiceInfoDAO(); 
							  dao2.setInvoiceDate(dao.getInvoiceDate());
							  dao2.setInvoiceNumber(dao.getInvoiceNumber());
							  dao2.setSupplierName(dao.getSupplierName());
							  dao2.setSupplierGSTNumber(dao.getSupplierGSTNumber());
							  dao2.setSupllierAddress(dao.getSupllierAddress());
							  dao2.setSrNo(""+srNo);
							  dao2.setProductHSCSACCode(productHSNcode.get(getProductInfoRS.getString(1)));
							  dao2.setProductName(getProductInfoRS.getString(2));
							  dao2.setQuantity(getProductInfoRS.getString(4));
							  dao2.setRate(getProductInfoRS.getString(5));
							  dao2.setTaxableAmount(df.format(getProductInfoRS.getFloat(6)));
							  dao2.setIgst(df.format(getProductInfoRS.getFloat(7)));
							  dao2.setSgst(df.format(getProductInfoRS.getFloat(8)));
							  dao2.setCgst(df.format(getProductInfoRS.getFloat(9)));
							  dao2.setTcsAmount(dao.getTcsAmount());  
							  dao2.setFrieghtCharges(dao.getFrieghtCharges());
								
									 
								dao2.setTotalAmount(df.format(getProductInfoRS.getFloat(6) + getProductInfoRS.getFloat(8) + getProductInfoRS.getFloat(9)));
									  dao2.setGrandTotal(df.format( getProductInfoRS.getFloat(6) 
											  	+ getProductInfoRS.getFloat(7) 
											  	+ getProductInfoRS.getFloat(8) 
											  	+ getProductInfoRS.getFloat(9)
											  	+ dao2.getTcsAmount()+dao2.getFrieghtCharges()));
								  
								  
								  srNo++;
								  this.getFromDateToGSTReportDAOList().add( dao2);
								
								  
							  
						  }
						  
					
						
						  
						  if(getProductInfoRS != null)
						  {
							  getProductInfoRS.close();
							  getProductInfoRS = null;
						  }
						  
						  if(getProductInfoPSMT != null)
						  {
							  getProductInfoPSMT.close();
							  getProductInfoPSMT = null;
						  }
						  
						  
						  
					  }
					  
					
					
				if(this.getReportFormat().equals("PDF"))
				{
					
					response = ServletActionContext.getResponse();
					request = ServletActionContext.getRequest();
					
					String path=	request.getSession().getServletContext().getRealPath("/images/");
					
				
					PDDocument document = new PDDocument(); 
					PDPage page = null;
					
					page = 	new PDPage(new PDRectangle(830,580));
					 
					 document.addPage(page);
					 
					 
			         PDPageContentStream contentStream = new PDPageContentStream(document, page);
			         
			         File fontFile  =  new File( request.getSession().getServletContext().getRealPath("/fonts/Nirmala.ttf"));
			 		
					 TrueTypeFont ttf1 = new TTFParser().parse(fontFile);
					 PDFont fontMarathi = PDType0Font.load(document, ttf1, true);
					 
					 
			         int pageCount = 1;
			         
			         Table header;
			         
			         
			         
			         
			         TableBuilder tableHeaderBuilder = Table.builder()
				                .addColumnsOfWidth(830)				                
				                .font(HELVETICA)
				                .borderColor(Color.BLACK)
				                .horizontalAlignment(CENTER);
			         
			         Row dairyNameHeader = Row.builder()
				                .add(TextCell.builder().text((String)this.getSessionMap().get("shopnameenglish")).horizontalAlignment(CENTER).borderWidth(0).lineSpacing(0.2F).build())				               
				                .backgroundColor(WHITE)
				                .textColor(Color.BLACK)
				                .font(PDType1Font.HELVETICA_BOLD).fontSize(16)				              
				                .horizontalAlignment(CENTER)
				                .build();
			         
			      
			         Row reportInfo1 = Row.builder()
				                .add(TextCell.builder()
		                		.text("GST Purchase Report From Date:"+this.getFromDate()+" to Date :"+this.getToDate()+" : Page No: "+pageCount)
		                		.horizontalAlignment(CENTER).borderWidth(0).lineSpacing(0.2F).build())				               
				                .backgroundColor(WHITE)
				                .textColor(Color.BLACK)
				                .font(PDType1Font.HELVETICA_BOLD).fontSize(10)				              
				                .horizontalAlignment(CENTER)
				                .build();
			         
			         
			         tableHeaderBuilder.addRow(dairyNameHeader);
			         tableHeaderBuilder.addRow(reportInfo1);
			       
			         
			         header = tableHeaderBuilder.build();
			         
			         
			         
			         float startY = page.getMediaBox().getHeight() - PADDING20;
			         TableDrawer.builder()
		                .contentStream(contentStream)
		                .table(header)
		                .startX(PADDING40)
		                .startY(startY)
		                .build()
		                .draw();
			         
			         
			         
			         Table custListHeaderInfo;
			         TableBuilder custInfoBuilder = Table.builder()
			        		  .addColumnsOfWidth(20,40,40,80,70,60,40,40,30,30,40,40,40,40,50,50,40,40)			 		                
						           .font(HELVETICA)
				                .borderWidth(0.5F)
				                .borderColor(Color.BLACK)
				                .horizontalAlignment(CENTER);
			         
			         
			         Row custInfoTableHeader = Row.builder()
			        		 	.add(TextCell.builder().text("SrNo").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
					            .add(TextCell.builder().text("Date").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
				                .add(TextCell.builder().text("Inv.No.").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
				                .add(TextCell.builder().text("Supplier Name").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
				            	.add(TextCell.builder().text("GST No.").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
					            .add(TextCell.builder().text("Desc").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
				                .add(TextCell.builder().text("Address").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
				                .add(TextCell.builder().text("Item").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
				            	.add(TextCell.builder().text("Rate").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
					            .add(TextCell.builder().text("Qty").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
				                .add(TextCell.builder().text("Taxable Amt.").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
				                .add(TextCell.builder().text("IGST").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
				            	.add(TextCell.builder().text("CGST").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
					            .add(TextCell.builder().text("SGST").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
				                .add(TextCell.builder().text("Sub Total").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
				                .add(TextCell.builder().text("Frieght Charges").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
					            .add(TextCell.builder().text("Sub Total").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
					            .add(TextCell.builder().text("Grand Total").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
				                .backgroundColor(Color.LIGHT_GRAY)
				                .textColor(Color.BLACK)
				                .height(30F)
				                .font(PDType1Font.HELVETICA_BOLD).fontSize(8)				              
				                .horizontalAlignment(CENTER)
				                .build();
			         
			         custInfoBuilder.addRow(custInfoTableHeader);
			         custListHeaderInfo = custInfoBuilder.build();
			        		
			        		 TableDrawer.builder()
				                .contentStream(contentStream)
				                .table(custListHeaderInfo)
				                .startX(PADDING40)
				                .startY(startY-50)
				                .build()
				                .draw();
			         
			        		 float currentY =  startY;
			        		
			        		 Table custDetailsTable = null;
				        	 
					         TableBuilder custDetailsTableBuilder = Table.builder()
					        		  .addColumnsOfWidth(20,40,40,80,70,60,40,40,30,30,40,40,40,40,50,50,40,40)			 		                
										    .font(HELVETICA)
						                .borderWidth(0.5F)
						                .borderColor(Color.BLACK)
						                .horizontalAlignment(CENTER);
				        	 			        		 
			        		 for(GSTPurchaseInvoiceInfoDAO dao:this.getFromDateToGSTReportDAOList())
					         {  
					        
					        	 
						         if(currentY < 250)
						         {
						        	 
						        	 custDetailsTable = custDetailsTableBuilder.build();
						        	 TableDrawer.builder()
						                .contentStream(contentStream)
						                .table(custDetailsTable)
						                .startX(PADDING40)
						                .startY(startY-65)
						                .build()
						                .draw();
						        	 
						        	 custDetailsTable = null;
						        	 custDetailsTableBuilder = null;
						        	 
						        	  custDetailsTableBuilder = Table.builder()
						        			  .addColumnsOfWidth(20,40,40,80,70,60,40,40,30,30,40,40,40,40,50,50,40,40)			 		                
												       .font(HELVETICA)
								                .borderWidth(0.5F)
								                .borderColor(Color.BLACK)
								                .horizontalAlignment(CENTER);
						        	 
						        	 pageCount++;
						        	 contentStream.close();
						        	PDPage newPage =	new PDPage(new PDRectangle(830,580));
									
						        	 contentStream = new PDPageContentStream(document,newPage);
						        	
						        	currentY = startY;
						             document.addPage( newPage );
						             
						             
						             
						             Table headerOtherPage;
							         
							         
							         
							         
							         TableBuilder tableHeaderBuilderOtherPage = Table.builder()
								                .addColumnsOfWidth(830)				                
								                .font(HELVETICA)
								                .borderColor(Color.BLACK)
								                .horizontalAlignment(CENTER);
							         
							         Row dairyNameHeaderOtherPage = Row.builder()
								                .add(TextCell.builder().text((String)this.getSessionMap().get("shopnameenglish")).horizontalAlignment(CENTER).borderWidth(0).lineSpacing(0.2F).build())				               
								                .backgroundColor(WHITE)
								                .textColor(Color.BLACK)
								                .font(PDType1Font.HELVETICA_BOLD).fontSize(16)				              
								                .horizontalAlignment(CENTER)
								                .build();
							         
							         
							         Row reportInfo1OtherPage = Row.builder()
								                .add(TextCell.builder()
								                		.text("Purchase GST Report From Date:"+this.getFromDate()+" to Date :"+this.getToDate()+" : Page No: "+pageCount)
								                		.horizontalAlignment(CENTER).borderWidth(0).lineSpacing(0.2F).build())				               
										                .backgroundColor(WHITE)
										                .textColor(Color.BLACK)
										                .font(PDType1Font.HELVETICA_BOLD).fontSize(10)				              
										                .horizontalAlignment(CENTER)
										                .build();
									         
							       
							         
							         
							         tableHeaderBuilderOtherPage.addRow(dairyNameHeaderOtherPage);
							         tableHeaderBuilderOtherPage.addRow(reportInfo1OtherPage);
							       
							         
							         headerOtherPage = tableHeaderBuilderOtherPage.build();
							         
							         TableDrawer.builder()
						                .contentStream(contentStream)
						                .table(headerOtherPage)
						                .startX(PADDING40)
						                .startY(startY)
						                .build()
						                .draw();
							         
							         
						             
						             TableDrawer.builder()
						                .contentStream(contentStream)
						                .table(custListHeaderInfo)
						                .startX(PADDING40)
						                .startY(startY-50)
						                .build()
						                .draw();
						             currentY = currentY + 55;
						         }
						        
						         
						         Row custInfoRow = Row.builder()
							                .add(TextCell.builder().text(dao.getSrNo()).fontSize(6).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.2F).build())				               
							                .add(TextCell.builder().text(dao.getInvoiceDate()).fontSize(6).horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(dao.getInvoiceNumber()).fontSize(6).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(dao.getSupplierName()).fontSize(7).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(""+dao.getSupplierGSTNumber()).fontSize(7).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.2F).build())				               
							                .add(TextCell.builder().text("").fontSize(7).horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(""+dao.getSupllierAddress()).fontSize(7).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(dao.getProductName()).fontSize(7).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.2F).build())				               
							                .add(TextCell.builder().text(dao.getRate()).fontSize(7).horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(dao.getQuantity()).fontSize(7).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(dao.getTaxableAmount()).fontSize(7).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(dao.getIgst()).fontSize(7).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(dao.getCgst()).fontSize(7).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.2F).build())				               
							                .add(TextCell.builder().text(dao.getSgst()).fontSize(7).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(""+dao.getTotalAmount()).fontSize(7).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(""+dao.getFrieghtCharges()).fontSize(7).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(""+dao.getTcsAmount()).fontSize(7).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.2F).build())
								            .add(TextCell.builder().text(dao.getGrandTotal()).fontSize(7).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .backgroundColor(Color.WHITE)
							                .textColor(Color.BLACK)
							                .font(fontMarathi)			              
							                .horizontalAlignment(CENTER)
							                .height(25F)
							                .build();
						         
						         custDetailsTableBuilder.addRow(custInfoRow);
						         
						         currentY = currentY - 20;
					        	 
					        	
					         }
			        		 
			        		 if(pageCount==1 || custDetailsTableBuilder != null)
			        		 {

				        	 custDetailsTable = custDetailsTableBuilder.build();
				        	 TableDrawer.builder()
				                .contentStream(contentStream)
				                .table(custDetailsTable)
				                .startX(PADDING40)
				                .startY(startY-65)
				                .build()
				                .draw();
			        		 }

					         	contentStream.close();
							 	document.save(path+"/purchase-gst-report.pdf");
						        document.close();
						        
						        

								response.setHeader("Content-disposition", "inline; filename=purchase-gst-report.pdf");
								response.setContentType("application/pdf");
								FileInputStream fileInputStream = new FileInputStream(path+"/purchase-gst-report.pdf");
								PrintWriter out = response.getWriter();  
								int bytes;
								while ((bytes = fileInputStream.read()) != -1) {
									out.write(bytes);
								}
								fileInputStream.close();
								out.flush();
								out.close();
								ttf1.close();
				}
				else if(this.getReportFormat().equals("EXCEL"))
				{
					response = ServletActionContext.getResponse();
					request = ServletActionContext.getRequest();
					
				
					
					 try {
						 String filename = "purchasereport-"+this.getFromDate()+"-to-"+this.getToDate()+".xls" ;
				            HSSFWorkbook workbook = new HSSFWorkbook();
				            HSSFSheet sheet = workbook.createSheet("FirstSheet"); 
				            
				            
							 final HSSFCellStyle cellStyleNonBoldRHAlign = workbook.createCellStyle();

							 cellStyleNonBoldRHAlign.setBorderBottom(BorderStyle.THIN);
							 cellStyleNonBoldRHAlign.setBorderRight(BorderStyle.THIN);
							 cellStyleNonBoldRHAlign.setBorderLeft(BorderStyle.THIN);
							 cellStyleNonBoldRHAlign.setBorderTop(BorderStyle.THIN);
							 cellStyleNonBoldRHAlign.setAlignment(HorizontalAlignment.RIGHT);
							 
							 final HSSFCellStyle cellStyleNonBold = workbook.createCellStyle();

							 cellStyleNonBold.setBorderBottom(BorderStyle.THIN);
							 cellStyleNonBold.setBorderRight(BorderStyle.THIN);
							 cellStyleNonBold.setBorderLeft(BorderStyle.THIN);
							 cellStyleNonBold.setBorderTop(BorderStyle.THIN);
							 
				            sheet.autoSizeColumn(0);
				            sheet.autoSizeColumn(1);
				            sheet.autoSizeColumn(2);
				            sheet.autoSizeColumn(3);
				            sheet.autoSizeColumn(4);
				            sheet.autoSizeColumn(5);
				            sheet.autoSizeColumn(6);
				            sheet.autoSizeColumn(7);
				            sheet.autoSizeColumn(8);
				            sheet.autoSizeColumn(9);
				            HSSFRow rowhead = sheet.createRow((short)0);
				            rowhead.createCell(0).setCellValue("Sr No.");
				            rowhead.createCell(1).setCellValue("Invoice Date");
				            rowhead.createCell(2).setCellValue("Invoice Number");
				            rowhead.createCell(3).setCellValue("Supplier Name");
				            rowhead.createCell(4).setCellValue("Supplier GST No");
				            rowhead.createCell(5).setCellValue("Discription");
				            rowhead.createCell(6).setCellValue("Address");
				            rowhead.createCell(7).setCellValue("Item Name");
				            rowhead.createCell(8).setCellValue("HSN/SAC Code");
				            rowhead.createCell(9).setCellValue("Rate");
				            rowhead.createCell(10).setCellValue("Quantity");
				            rowhead.createCell(11).setCellValue("Taxable Amount");
				            rowhead.createCell(12).setCellValue("IGST");
				            rowhead.createCell(13).setCellValue("CGST");
				            rowhead.createCell(14).setCellValue("SGST");
				            rowhead.createCell(15).setCellValue("Sub Total");
				            rowhead.createCell(16).setCellValue("Fright Charges");
				            rowhead.createCell(17).setCellValue("TSC Amount");
				            rowhead.createCell(18).setCellValue("Grand Total");

				            int k = 0;
				            
				            float totalTaxableAmount = 0.0F;
				            float totalIGSTAmount = 0.0F;
				            float totalSGSTAmount = 0.0F;
				            float totalCGSTAmount = 0.0F;
				            float totalOfSubTotal = 0.0F;
				            float totalOfFrightCharge = 0.0F;
				            float totalOfTCSAmount = 0.0F;
				            float totalOfGrandTotal = 0.0F;
				            	
				            
				       	 for(GSTPurchaseInvoiceInfoDAO dao:this.getFromDateToGSTReportDAOList())
				         {  
				       		 k = (int)Integer.parseInt(dao.getSrNo());
				            HSSFRow row = sheet.createRow((int)Integer.parseInt(dao.getSrNo()));
				    		
				          
				            row.createCell(0).setCellValue((int) Integer.parseInt(dao.getSrNo()));
				            
				            row.createCell(1).setCellValue(dao.getInvoiceDate());
				            row.createCell(2).setCellValue(""+dao.getInvoiceNumber());
				            row.createCell(3).setCellValue(dao.getSupplierName());
				            row.createCell(4).setCellValue(dao.getSupplierGSTNumber());
				            row.createCell(5).setCellValue("");
				            row.createCell(6).setCellValue(dao.getSupllierAddress());
				            row.createCell(7).setCellValue(dao.getProductName());
				            row.createCell(8).setCellValue(dao.getProductHSCSACCode());
				            row.createCell(9).setCellValue((float)Float.parseFloat(dao.getRate()));
				            row.createCell(10).setCellValue((float)Float.parseFloat(dao.getQuantity()));
				            row.createCell(11).setCellValue(df.format((float)Float.parseFloat(dao.getTaxableAmount())));
				            row.createCell(12).setCellValue(df.format((float)Float.parseFloat(dao.getIgst())));
				            row.createCell(13).setCellValue(df.format((float)Float.parseFloat(dao.getCgst())));
				            row.createCell(14).setCellValue(df.format((float)Float.parseFloat(dao.getSgst())));
				            row.createCell(15).setCellValue(df2.format((float)Float.parseFloat(dao.getTotalAmount())));
				            row.createCell(16).setCellValue(df2.format(dao.getFrieghtCharges()));
				            row.createCell(17).setCellValue(df2.format(dao.getTcsAmount()));
				            row.createCell(18).setCellValue(df2.format((float)Float.parseFloat(dao.getGrandTotal())));
				         
				            totalTaxableAmount = totalTaxableAmount + (float)Float.parseFloat(dao.getTaxableAmount());
				            
				             totalIGSTAmount = totalIGSTAmount + (float)Float.parseFloat(dao.getIgst());
				             totalSGSTAmount = totalSGSTAmount + (float)Float.parseFloat(dao.getCgst());
				             totalCGSTAmount = totalCGSTAmount + (float)Float.parseFloat(dao.getSgst());
				             totalOfSubTotal = totalOfSubTotal + (float)Float.parseFloat(dao.getTotalAmount());
				             totalOfFrightCharge = totalOfFrightCharge + dao.getFrieghtCharges();
				             totalOfTCSAmount = totalOfTCSAmount + dao.getTcsAmount();
				             totalOfGrandTotal = totalOfGrandTotal + (float)Float.parseFloat(dao.getGrandTotal());
				         }

				       	 	HSSFRow rowTotal = sheet.createRow(k+1);
				            
				            sheet.addMergedRegion(new CellRangeAddress(k+1,k+1,0,10));  
				           
				            
				             Cell cell1 = rowTotal.createCell(0);
							 cell1.setCellValue("Total");
							 cell1.setCellStyle(cellStyleNonBoldRHAlign);
							 
							 Cell cell2 = rowTotal.createCell(11);
							 cell2.setCellValue(totalTaxableAmount);
							 cell2.setCellStyle(cellStyleNonBoldRHAlign);
							 
							 
							 Cell cell3 = rowTotal.createCell(12);
							 cell3.setCellValue(totalIGSTAmount);
							 cell3.setCellStyle(cellStyleNonBoldRHAlign);
							 
							 
							 Cell cell4 = rowTotal.createCell(13);
							 cell4.setCellValue(totalSGSTAmount);
							 cell4.setCellStyle(cellStyleNonBoldRHAlign);
							 
							 
							 Cell cell5 = rowTotal.createCell(14);
							 cell5.setCellValue(totalCGSTAmount);
							 cell5.setCellStyle(cellStyleNonBoldRHAlign);
							 
							 
							 Cell cell6 = rowTotal.createCell(15);
							 cell6.setCellValue(totalOfSubTotal);
							 cell6.setCellStyle(cellStyleNonBoldRHAlign);
							 
							 Cell cell7 = rowTotal.createCell(16);
							 cell7.setCellValue(totalOfFrightCharge);
							 cell7.setCellStyle(cellStyleNonBoldRHAlign);
							 
							 
							 Cell cell8 = rowTotal.createCell(17);
							 cell8.setCellValue(totalOfTCSAmount);
							 cell8.setCellStyle(cellStyleNonBoldRHAlign);
							 
							 
							 Cell cell9 = rowTotal.createCell(18);
							 cell9.setCellValue(totalOfGrandTotal);
							 cell9.setCellStyle(cellStyleNonBoldRHAlign);
						
				            
				            FileOutputStream fileOut = new FileOutputStream(filename);
				            workbook.write(fileOut);
				            fileOut.close();
				            workbook.close();
				            
				            
				    		response.setHeader("Content-disposition", "inline; filename=purchasereport-"+this.getFromDate()+"-to-"+this.getToDate()+".xls");
							response.setContentType("application/xls");
							FileInputStream fileInputStream = new FileInputStream("purchasereport-"+this.getFromDate()+"-to-"+this.getToDate()+".xls");
							PrintWriter out = response.getWriter();  
							int bytes;
							while ((bytes = fileInputStream.read()) != -1) {
								out.write(bytes);
							}
							fileInputStream.close();
							out.close();
				         
					    } catch (FileNotFoundException e) {
					        // TODO Auto-generated catch block
					        e.printStackTrace();
					    }
					
				}
				
					
				}
				catch (SQLException e) 
				{

					e.printStackTrace();
					addActionError(e.getErrorCode()+" || "+e.getMessage() +" || Collection details could not be found !");


				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}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) {}
					}
				
			}
        }
}



	public HttpSession getSession() {
		return session;
	}



	public void setSession(HttpSession session) {
		this.session = session;
	}
      



	public List<GSTPurchaseInvoiceInfoDAO> getFromDateToGSTReportDAOList() {
		return fromDateToGSTReportDAOList;
	}



	public void setFromDateToGSTReportDAOList(List<GSTPurchaseInvoiceInfoDAO> fromDateToGSTReportDAOList) {
		this.fromDateToGSTReportDAOList = fromDateToGSTReportDAOList;
	}



	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 String getReportFormat() {
		return reportFormat;
	}



	public void setReportFormat(String reportFormat) {
		this.reportFormat = reportFormat;
	}



	public SessionMap<String, Object> getSessionMap() {
		return sessionMap;
	}

	public void setSessionMap(SessionMap<String, Object> sessionMap) {
		this.sessionMap = sessionMap;
	}

	
	public void setSession(Map<String, Object> map) {  
	    sessionMap=(SessionMap<String, Object>)map;  
	}
    
}

