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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.GSTSaleBillInfoDAO;
import com.reallinkbilling.db.GetDBConnection;

public class DownloadGstReport 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<GSTSaleBillInfoDAO> fromDateToGSTReportDAOList = new ArrayList<GSTSaleBillInfoDAO>();
	
		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,GSTSaleBillInfoDAO> billMap = new LinkedHashMap<String,GSTSaleBillInfoDAO>();
					String shopId = (String) this.getSessionMap().get("shopId");
		    		String shopCode = (String) this.getSessionMap().get("shopCode");
		    		

		        	 String billNoPreFix = "";
		        	 
		        	 if(shopId.equals("46"))
		        	 {
		        		 billNoPreFix = "KE/2023-24/";
		        	 }
		        	 else if(shopId.equals("47"))
		        	 {
		        		 billNoPreFix = "KE/AN/2023-24/";
		        	 }
		        	 else
		        	 {
		        		 billNoPreFix = "";
		        	 }
		        	 System.out.println(billNoPreFix);
		        	 
					 sql = "SELECT "
						  		+ "bi.bill_number,"
						  		+ "DATE_FORMAT(bi.bill_date,'%d-%m-%Y'),"
						  		+ "bi.customer_id,"
						  		+ "bi.customer_name,"
						  		+ "cust.billing_address,"
						  		+ "cust.gst_number,"
						  		+ "bi.bill_total_amount "
						  		+ "FROM "
						  		+ "shop_bill_info_master as bi "
						  		+ "INNER JOIN "
						  		+ "shop_customer_master as cust "
						  		+ "ON "
						  		+ "bi.customer_id = cust.customer_id "
						  		+ "WHERE "
						  		+ "(bi.bill_date  "
						  		+ "BETWEEN "
						  		+ "STR_TO_DATE(?,'%d-%m-%Y') "
						  		+ "AND "
						  		+ "STR_TO_DATE(?,'%d-%m-%Y')) "
						  		+ "AND "
						  		+ "bi.bill_type = 'TAX-INVOICE' "
						  		+ "AND "
						  		+ "bi.shop_id = ? "
						  		+ "AND "
						  		+ "bi.shop_code = ? "
						  		+ "AND "
						  		+ "cust.shop_id = ? "
						  		+ "AND "
						  		+ "cust.shop_code = ? "
						  		+ "ORDER BY bi.bill_date,bi.bill_number";
					  
					  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);
					   
					  
					  resultSet = preparedStatement.executeQuery();
					  
					  while(resultSet.next())
					  {
						  
						  try
						  {
							  GSTSaleBillInfoDAO dao = new GSTSaleBillInfoDAO();
							
							  dao.setBillNumber(resultSet.getString(1));
							  dao.setBillDate(resultSet.getString(2));
							  dao.setCustId(resultSet.getString(3));
							  dao.setCustName(resultSet.getString(4).split("-")[1]);
							  dao.setCustAddress(resultSet.getString(5));
							  dao.setCustGstNumber(resultSet.getString(6));
							  dao.setBillTotalAmount(resultSet.getString(7));
						
							  
							  billMap.put(resultSet.getString(1), dao);
						  }
						  catch(Exception e)
						  {
							  GSTSaleBillInfoDAO dao = new GSTSaleBillInfoDAO();
								
							  dao.setBillNumber(resultSet.getString(1));
							  dao.setBillDate(resultSet.getString(2));
							  dao.setCustId("1");
							  dao.setCustName("CASH");
							  dao.setCustAddress("NA");
							  dao.setCustGstNumber("NA");
							  dao.setBillTotalAmount(resultSet.getString(7));
						
							  
							  billMap.put(resultSet.getString(1), dao);
						  }
						  
					  }
					  
					  
					  if(resultSet != null)
					  {
						  resultSet.close();
						  resultSet = null;
					  }
					  
					  if(preparedStatement != null)
					  {
						  preparedStatement.close();
						  preparedStatement = null;
					  }
					  int srNo = 1;
					  for(String billNumber:billMap.keySet())
					  {
						  
						  String getProductInfoSQL = "";
						  PreparedStatement getProductInfoPSMT = null;
						  ResultSet getProductInfoRS = null;
						  
						  GSTSaleBillInfoDAO dao = billMap.get(billNumber);
						  
						  getProductInfoSQL = "SELECT "
							  		+ "product_id,"
							  		+ "product_name,"
							  		+ "product_total_amount_aft_discount,"
							  		+ "product_quantity,"
							  		+ "sales_rate,"
							  		+ "product_net_amount,"
							  		+ "product_igst_amount,"
							  		+ "product_cgst_amount,"
							  		+ "product_sgst_amount "
							  		+ "FROM "
							  		+ "shop_bill_product_info_master "
							  		+ "WHERE "
							  		+ "bill_number = ? "
							  		+ "AND "
							  		+ "shop_id = ? "
							  		+ "AND "
							  		+ "shop_code = ? "
							  		+ "AND "					  		
							  		+ "bill_type = 'TAX-INVOICE'";
							  
							  getProductInfoPSMT = connection.prepareStatement(getProductInfoSQL);
							  
							  getProductInfoPSMT.setString(1, billNumber);
							  getProductInfoPSMT.setString(2, shopId);				  
							  getProductInfoPSMT.setString(3, shopCode);
							  getProductInfoRS = getProductInfoPSMT.executeQuery();
							  
							  
							 HashMap<String,String> productHSNcode = (HashMap<String, String>) this.getSessionMap().get("productHSNcode");
					
							 while(getProductInfoRS.next())
							  {
								  GSTSaleBillInfoDAO dao2 = new GSTSaleBillInfoDAO(); 
								  dao2.setBillDate(dao.getBillDate());
								  dao2.setBillNumber(dao.getBillNumber());
								  dao2.setCustName(dao.getCustName());
								  dao2.setCustomerGSTNumber(dao.getCustGstNumber());
								  dao2.setCustAddress(dao.getCustAddress());
								  dao2.setCustGstNumber(dao.getCustGstNumber());
								  dao2.setCustomerGSTNumber(dao.getCustomerGSTNumber());
								  dao2.setSrNo(""+srNo);
								  dao2.setProductHSCSACCode(productHSNcode.get(getProductInfoRS.getString(1)));
								  dao2.setItemName(getProductInfoRS.getString(2));
								  dao2.setItemQuantity(getProductInfoRS.getString(4));
								  dao2.setItemRate(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)));
									  
									
										  float tcs = 0.0f;
										  dao2.setTcs(df.format(tcs));
										  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)
												  	+ tcs));
									  
									  
									  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 Sales 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,40,50)			 		                
						           .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("Customer 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("TCS").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,40,50)			 		                
										    .font(HELVETICA)
						                .borderWidth(0.5F)
						                .borderColor(Color.BLACK)
						                .horizontalAlignment(CENTER);
				        	 			        		 
			        		 for(GSTSaleBillInfoDAO 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,40,50)			 		                
												       .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("GST Sales 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.getBillDate()).fontSize(6).horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(billNoPreFix+""+dao.getBillNumber()).fontSize(6).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(dao.getCustName()).fontSize(7).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(""+dao.getCustGstNumber()).fontSize(7).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.2F).build())				               
							                .add(TextCell.builder().text(""+dao.getComments()).fontSize(7).horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(""+dao.getCustAddress()).fontSize(7).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(dao.getItemName()).fontSize(7).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.2F).build())				               
							                .add(TextCell.builder().text(dao.getItemRate()).fontSize(7).horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(0.2F).build())
							                .add(TextCell.builder().text(dao.getItemQuantity()).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.getTcs()).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+"/gst-report.pdf");
						        document.close();
						        
						        

								response.setHeader("Content-disposition", "inline; filename=gst-report.pdf");
								response.setContentType("application/pdf");
								FileInputStream fileInputStream = new FileInputStream(path+"/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 = "gstsalereport-"+this.getFromDate()+"-to-"+this.getToDate()+".xls" ;
				            HSSFWorkbook workbook = new HSSFWorkbook();
				            HSSFSheet sheet = workbook.createSheet("FirstSheet");  
				            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("Customer Name");
				            rowhead.createCell(4).setCellValue("Customer 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("TCS");
				            rowhead.createCell(17).setCellValue("Grand Total");

				       	 for(GSTSaleBillInfoDAO dao:this.getFromDateToGSTReportDAOList())
				         {  
				        
				            HSSFRow row = sheet.createRow((int)Integer.parseInt(dao.getSrNo()));
				            
				            row.createCell(0).setCellValue((int) Integer.parseInt(dao.getSrNo()));
				            row.createCell(1).setCellValue(dao.getBillDate());
				            row.createCell(2).setCellValue(billNoPreFix+""+(int) Integer.parseInt(dao.getBillNumber()));
				            row.createCell(3).setCellValue(dao.getCustName());
				            row.createCell(4).setCellValue(dao.getCustGstNumber());
				            row.createCell(5).setCellValue(dao.getComments());
				            row.createCell(6).setCellValue(dao.getCustAddress());
				            row.createCell(7).setCellValue(dao.getItemName());
				            row.createCell(8).setCellValue(dao.getProductHSCSACCode());
				            row.createCell(9).setCellValue((float)Float.parseFloat(dao.getItemRate()));
				            row.createCell(10).setCellValue((float)Float.parseFloat(dao.getItemQuantity()));
				            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((float)Float.parseFloat(dao.getTcs()));
				            row.createCell(17).setCellValue(df2.format((float)Float.parseFloat(dao.getGrandTotal())));
				      
				         }

				            FileOutputStream fileOut = new FileOutputStream(filename);
				            workbook.write(fileOut);
				            fileOut.close();
				            workbook.close();
				            
				            
				    		response.setHeader("Content-disposition", "inline; filename=gstsalereport-"+this.getFromDate()+"-to-"+this.getToDate()+".xls");
							response.setContentType("application/xls");
							FileInputStream fileInputStream = new FileInputStream("gstsalereport-"+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<GSTSaleBillInfoDAO> getFromDateToGSTReportDAOList() {
		return fromDateToGSTReportDAOList;
	}



	public void setFromDateToGSTReportDAOList(List<GSTSaleBillInfoDAO> 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;  
	}
    
}

