package com.reallinkbilling.download;


import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.struts2.ServletActionContext;
import org.apache.struts2.dispatcher.SessionMap;
import org.apache.struts2.interceptor.SessionAware;

import com.opensymphony.xwork2.ActionSupport;
import com.reallinkbilling.DAO.GSTSaleBillInfoDAO;
import com.reallinkbilling.db.GetDBConnection;
import com.reallinkbilling.resource.GetListResource;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
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;

public class DownloadGSTR_1Report extends ActionSupport implements SessionAware{

	
	/**
	 * 
	 */
	private static final long serialVersionUID = 6805671539467196119L;
	private HttpSession session;
	private String fromDate;
	private String toDate;
	private String Source;
	private String customerName;
	private String Sheet;
	private List<GSTSaleBillInfoDAO> fromDateToGSTReportDAOList = new ArrayList<GSTSaleBillInfoDAO>();
	
	
	public HttpServletRequest request;
	public HttpServletResponse response;
	
	private SessionMap<String,Object> sessionMap;  
	
	
	
	
	@SuppressWarnings("resource")
	public void download(){
		
		
		
			
		
		session=ServletActionContext.getRequest().getSession(false);  
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "";
		
		String db = (String) session.getAttribute("db");
		
		String shopId = (String) this.getSessionMap().get("shopId");
		String shopCode = (String) this.getSessionMap().get("shopCode");
    	
		  
    try {
			
			DecimalFormat df = new DecimalFormat("##.##");
			
			String splitCustomerName [] = new String[5];
        	
        	String customerId = "";
        	try
        	{
        		splitCustomerName = customerName.split("-");
        		customerId = splitCustomerName[0];
        		
        	}catch(Exception e)
        	{
        		customerId = customerName;
        	}
        	
        	
        	connection = GetDBConnection.getDBConnection(db);
			
			
			LinkedHashMap<String,GSTSaleBillInfoDAO> billMap = new LinkedHashMap<String,GSTSaleBillInfoDAO>();

			if(this.getCustomerName().equals("ALL") || this.getCustomerName() == "NULL" || this.getCustomerName().equals(""))
			{
		 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.transportation_charges,"
		  		+ "bi.bill_total_amount,"
		  		+ "bi.billed_by,"
		  		+ "bi.comments "
		  		+ "FROM "
		  		+ "shop_bill_info_master as bi "
		  		+ "INNER JOIN "
		  		+ "shop_customer_master as cust "
		  		+ "ON "
		  		+ "bi.customer_id = cust.customer_id "
		  		+ "WHERE "
		  		+ "(bill_date  "
		  		+ "BETWEEN "
		  		+ "STR_TO_DATE(?,'%d-%m-%Y') "
		  		+ "AND "
		  		+ "STR_TO_DATE(?,'%d-%m-%Y')) "
		  		+ "AND "
		  		+ "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);
		  
			}
			
			else{
				
				
				
				 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.transportation_charges,"
					  		+ "bi.bill_total_amount,"
					  		+ "bi.billed_by,"
					  		+ "bi.comments "
					  		+ "FROM "
					  		+ "shop_bill_info_master as bi "
					  		+ "INNER JOIN "
					  		+ "shop_customer_master as cust "
					  		+ "ON "
					  		+ "bi.customer_id = cust.customer_id "
					  		+ "WHERE "
					  		+ "(bill_date  "
					  		+ "BETWEEN "
					  		+ "STR_TO_DATE(?,'%d-%m-%Y') "
					  		+ "AND "
					  		+ "STR_TO_DATE(?,'%d-%m-%Y')) "
					  		+ "AND "
					  		+ "bill_type = 'TAX-INVOICE' "
					  		+ "AND "
				            + "bi.customer_id = ? "
				            + "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, customerId);
				      preparedStatement.setString(4, shopId);
					  preparedStatement.setString(5, shopCode);
					  preparedStatement.setString(6, shopId);
					  preparedStatement.setString(7, shopCode);
					  
				
			}
			resultSet = preparedStatement.executeQuery();
			  
			  while(resultSet.next())
			  {
				  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.setTransportCharges(resultSet.getFloat(7));
				  dao.setBillTotalAmount(resultSet.getString(8));
				  dao.setBilledBy(resultSet.getString(9));
				  dao.setComments(resultSet.getString(10));
				  
				  billMap.put(resultSet.getString(1), dao);
			  }
			  
			
		 
if(resultSet !=null)
{
	try {resultSet.close(); resultSet = null;} catch (SQLException e) {}
}
if(preparedStatement != null)
{
	try {preparedStatement.close(); preparedStatement = null;} catch (SQLException e) {}
}
			
			
		  int srNo = 1;
		  for(String billNumber:billMap.keySet())
		  {
			  
			  String getProductInfoSQL = "";
			  PreparedStatement getProductInfoPSMT = null;
			  ResultSet getProductInfoRS = null;
			  
			  GSTSaleBillInfoDAO dao = billMap.get(billNumber);
			  
			  getProductInfoSQL = "SELECT "
			  		+ "product_name,"
			  		+ "product_total_amount_aft_discount,"
			  		+ "product_quantity,"
			  		+ "sales_rate,"
			  		+ "product_net_amount,"
			  		+ "product_igst_amount,"
			  		+ "product_cgst_amount,"
			  		+ "product_sgst_amount, "
			  		+ "id_tax_table, "
			  		+ "sales_unit "
			  		+ "FROM "
			  		+ "shop_bill_product_info_master "
			  		+ "WHERE "
			  		+ "bill_number = ? "
			  		+ "AND "
			  		+ "bill_type = 'TAX-INVOICE' "
			  		+ "AND "
			  	    + "shop_id = ? " 
			  	    + "AND "
			        + "shop_code = ? "; 
			        
			  
			  getProductInfoPSMT = connection.prepareStatement(getProductInfoSQL);
			  
			  getProductInfoPSMT.setString(1, billNumber);
			  getProductInfoPSMT.setString(2, shopId);
			  getProductInfoPSMT.setString(3, shopCode);
			  
			  getProductInfoRS = getProductInfoPSMT.executeQuery();
			  
				
			  while(getProductInfoRS.next())
			  {
				
				  GSTSaleBillInfoDAO newDAO  = new GSTSaleBillInfoDAO();
				  
				 
		            
				  newDAO.setBillNumber(dao.getBillNumber());
				  newDAO.setBillDate(dao.getBillDate());
				  newDAO.setCustId(dao.getCustId());
				  newDAO.setCustName(dao.getCustName());
				  newDAO.setCustAddress(dao.getCustAddress());
				  newDAO.setCustGstNumber(dao.getCustGstNumber());
				  newDAO.setTransportCharges(dao.getTransportCharges());
				  newDAO.setBillTotalAmount(dao.getBillTotalAmount());
				  newDAO.setBilledBy(dao.getBilledBy());
				  newDAO.setComments(dao.getComments());
				  
				  newDAO.setSrNo(""+srNo);
				  newDAO.setItemName(getProductInfoRS.getString(1));
				  newDAO.setItemQuantity(getProductInfoRS.getString(3));
				  newDAO.setItemRate(getProductInfoRS.getString(4));
				  newDAO.setTaxableAmount(df.format(getProductInfoRS.getFloat(5)));
				  newDAO.setIgst(df.format(getProductInfoRS.getFloat(6)));
				  newDAO.setSgst(df.format(getProductInfoRS.getFloat(7)));
				  newDAO.setCgst(df.format(getProductInfoRS.getFloat(8)));
				  
				  newDAO.setSalesUnit(getProductInfoRS.getString(10));
					  
				  
			        String taxid = df.format(getProductInfoRS.getFloat(9));
			        Map<String, String> taxInfo = GetListResource.GetTaxNamePercentage(taxid,this.getSessionMap());
			       
			        String taxPercentage = taxInfo.get("TAX-PERCENTAGE");
			       
			       
			        newDAO.setTaxPercentage(taxPercentage);

				  
					float tcs = 0.0f;
						  newDAO.setTcs(df.format(tcs));
						  newDAO.setTotalAmount(df.format(getProductInfoRS.getFloat(5) + getProductInfoRS.getFloat(7) + getProductInfoRS.getFloat(8)));
						  newDAO.setGrandTotal(df.format( getProductInfoRS.getFloat(5) 
								  	+ getProductInfoRS.getFloat(6) 
								  	+ getProductInfoRS.getFloat(7) 
								  	+ getProductInfoRS.getFloat(8) 
								  	+ newDAO.getTransportCharges() + tcs));
					  
						  
						  
						 
				            
						  this.getFromDateToGSTReportDAOList().add(newDAO);
						 
				            
					        srNo++;
					
					  
                       
						 
					        
					        
				}
			  
				
			  
			 
			 
			 
			  if(getProductInfoRS != null)
			  {
				  getProductInfoRS.close();
				  getProductInfoRS = null;
			  }
			  
			  if(getProductInfoPSMT != null)
			  {
				  getProductInfoPSMT.close();
				  getProductInfoPSMT = null;
			  }
			  
			  
			  
		  }
		 
		  if(this.getSheet().equals("hsn"))
		  {
		  
		  for (GSTSaleBillInfoDAO dao : this.getFromDateToGSTReportDAOList()) {
			    
			    String productName = dao.getItemName();
			    
			    
			    String getHSNCodeSQL = "SELECT product_id,hsn_sac_code FROM shop_product_master WHERE product_name_eng = ? "
			    		+ "AND "
				  	    + "shop_id = ? " 
				  	    + "AND "
				        + "shop_code = ? "; 
				        
			    try (PreparedStatement getHSNCodePSMT = connection.prepareStatement(getHSNCodeSQL)) {
			        getHSNCodePSMT.setString(1, productName);
			        getHSNCodePSMT.setString(2, shopId);
			        getHSNCodePSMT.setString(3, shopCode);
					  
			        try (ResultSet getHSNCodeRS = getHSNCodePSMT.executeQuery()) {
			            if (getHSNCodeRS.next()) {
			            	 String idproduct = getHSNCodeRS.getString(1);
			                String hsnCode = getHSNCodeRS.getString(2);
			                
			                
			                dao.setHsnCode(hsnCode);
			                dao.setIdProduct(idproduct);
			            }else {
			            	dao.setHsnCode("null");
			            }
			        }
			        
			    } catch (SQLException e) {
			        e.printStackTrace();
			        
			    }
			}


		 
		  List<GSTSaleBillInfoDAO> reportList = this.getFromDateToGSTReportDAOList();

		   
		    Map<String, GSTSaleBillInfoDAO> aggregatedDataMap = new LinkedHashMap<>();

		    
		    for (GSTSaleBillInfoDAO dao : reportList) {
		        String idProduct = dao.getIdProduct();  
		        String salesUnit = dao.getSalesUnit();

		        
		        String key = idProduct + "_" + salesUnit;

		        if (aggregatedDataMap.containsKey(key)) {
		            
		            GSTSaleBillInfoDAO existingDAO = aggregatedDataMap.get(key);

		            
		            float newQuantity = Float.parseFloat(existingDAO.getItemQuantity()) + Float.parseFloat(dao.getItemQuantity());
		            float newTaxableAmount = Float.parseFloat(existingDAO.getTaxableAmount()) + Float.parseFloat(dao.getTaxableAmount());
		            float newTotalAmount = Float.parseFloat(existingDAO.getTotalAmount()) + Float.parseFloat(dao.getTotalAmount());
		            float newIgst = Float.parseFloat(existingDAO.getIgst()) + Float.parseFloat(dao.getIgst());
		            float newCgst = Float.parseFloat(existingDAO.getCgst()) + Float.parseFloat(dao.getCgst());
		            float newSgst = Float.parseFloat(existingDAO.getSgst()) + Float.parseFloat(dao.getSgst());

		            
		            float newRate = (Float.parseFloat(existingDAO.getItemRate()) + Float.parseFloat(dao.getItemRate())) / 2;

		            
		            existingDAO.setItemQuantity(String.valueOf(newQuantity));
		            existingDAO.setTaxableAmount(String.valueOf(newTaxableAmount));
		            existingDAO.setTotalAmount(String.valueOf(newTotalAmount));
		            existingDAO.setIgst(String.valueOf(newIgst));
		            existingDAO.setCgst(String.valueOf(newCgst));
		            existingDAO.setSgst(String.valueOf(newSgst));
		            existingDAO.setItemRate(String.valueOf(newRate));

		        } else {
		            
		            aggregatedDataMap.put(key, dao);
		        }
		    }

		    
		    List<GSTSaleBillInfoDAO> sortedList = aggregatedDataMap.values().stream()
		        .sorted(Comparator
		            .comparing((GSTSaleBillInfoDAO dao) -> dao.getIdProduct(), Comparator.nullsLast(Comparator.naturalOrder()))
		            .thenComparing((GSTSaleBillInfoDAO dao) -> dao.getSalesUnit(), Comparator.nullsLast(Comparator.naturalOrder())))
		        .collect(Collectors.toList());

		   
		    reportList.clear();
		    reportList.addAll(sortedList);
		    
		    this.setFromDateToGSTReportDAOList(reportList); 
		
		  }
	  
	  
	  
	  if(this.getFromDateToGSTReportDAOList().size()>0)
		{
			
			
				
		  
				
				response = ServletActionContext.getResponse();
				request = ServletActionContext.getRequest();
				
				if(this.getSheet().equals("b2b"))
				{
					
					 
					String fileName = "GSTR_1_Report_Sheet-"+this.getSheet()+".xls";
					
					
					 HSSFWorkbook workbook = new HSSFWorkbook();
			            HSSFSheet mySheet = workbook.createSheet("GSTR_1_Report-Sheet-"+ this.getSheet() +" From -"+this.getFromDate()+"-to-"+this.getToDate()); 
			            
					mySheet.setColumnWidth(0, 20 * 256);  // GSTIN/UIN - set to 20 characters
					mySheet.setColumnWidth(1, 40 * 256);  // Trade Name - set to 25 characters
					mySheet.setColumnWidth(2, 15 * 256);  // Invoice No - set to 15 characters
					mySheet.setColumnWidth(3, 15 * 256);  // Date Of Invoice - set to 15 characters
					mySheet.setColumnWidth(4, 15 * 256);  // Invoice Value - set to 15 characters
					mySheet.setColumnWidth(5, 10 * 256);  // GST% - set to 10 characters
					mySheet.setColumnWidth(6, 15 * 256);  // Taxable Value - set to 15 characters
					mySheet.setColumnWidth(7, 10 * 256);  // CESS - set to 10 characters
					mySheet.setColumnWidth(8, 20 * 256);  // Place Of Supply - set to 20 characters
					mySheet.setColumnWidth(9, 20 * 256); // Invoice Type - set to 20 characters
					
					 
					 	
					 
					 HSSFRow rowhead = mySheet.createRow((short)0);
					 
					
					 
					 rowhead.createCell(0).setCellValue("GSTIN/UIN");
			            rowhead.createCell(1).setCellValue("Trade Name");
			            rowhead.createCell(2).setCellValue("Invoice No");
			            rowhead.createCell(3).setCellValue("Date Of Invoice");
			            rowhead.createCell(4).setCellValue("Invoice Value");
			            rowhead.createCell(5).setCellValue("GST%");
			            rowhead.createCell(6).setCellValue("Taxable Value");
			            rowhead.createCell(7).setCellValue("CESS");
			            rowhead.createCell(8).setCellValue("Place Of Supply");
			            rowhead.createCell(9).setCellValue("Invoice Type");
			            
					
					
					   
					 int rowCount = 1;
					   
						
								
				   for(GSTSaleBillInfoDAO dao:this.getFromDateToGSTReportDAOList())
			       {
					   
					   HSSFRow row = mySheet.createRow(rowCount);
			            
					   
					   
					   
					   row.createCell(0).setCellValue(dao.getCustGstNumber());
			            row.createCell(1).setCellValue(dao.getCustName());
			            row.createCell(2).setCellValue(dao.getBillNumber());
			            row.createCell(3).setCellValue(dao.getBillDate());
			            row.createCell(4).setCellValue(dao.getTotalAmount());
			            row.createCell(5).setCellValue(dao.getTaxPercentage());
			            row.createCell(6).setCellValue(dao.getTaxableAmount());
			            row.createCell(7).setCellValue("0");
			            row.createCell(8).setCellValue("MAHARASHTRA");
			            row.createCell(9).setCellValue("REGULAR");
			           
						
							 
							 
							 rowCount++;
				        	 
				         }
				   
				   
				   
					
					   
					   
					   FileOutputStream fileOut = new FileOutputStream(fileName);
					   workbook.write(fileOut);
			            fileOut.close();
			            workbook.close();
			            
			            
			    		response.setHeader("Content-disposition", "inline; filename="+fileName);
						response.setContentType("application/xlsx");
						FileInputStream fileInputStream = new FileInputStream(fileName);
						PrintWriter out = response.getWriter();  
						int bytes;
						while ((bytes = fileInputStream.read()) != -1) {
							out.write(bytes);
						}
						fileInputStream.close();
						out.close();
					 
					 
					
				}
				
				if(this.getSheet().equals("b2cl"))
				{
					
				
					 
					String fileName = "GSTR_1_Report_Sheet-"+this.getSheet()+".xls";
					
					
					HSSFWorkbook workbook = new HSSFWorkbook();
		            HSSFSheet mySheet = workbook.createSheet("GSTR_1_Report-Sheet-"+ this.getSheet() +" From -"+this.getFromDate()+"-to-"+this.getToDate());
		            
		            
					
					mySheet.setColumnWidth(0, 40 * 256);  // Customer Name - set to 30 characters
					mySheet.setColumnWidth(1, 20 * 256);  // Invoice No - set to 20 characters
					mySheet.setColumnWidth(2, 15 * 256);  // Invoice Date - set to 15 characters
					mySheet.setColumnWidth(3, 20 * 256);  // Invoice Value - set to 20 characters
					mySheet.setColumnWidth(4, 10 * 256);  // GST% - set to 10 characters
					mySheet.setColumnWidth(5, 20 * 256);  // Taxable Value - set to 20 characters
					mySheet.setColumnWidth(6, 10 * 256);  // CESS - set to 10 characters
					mySheet.setColumnWidth(7, 25 * 256);  // Place Of Supply - set to 25 characters
					
					
					
					 HSSFRow rowhead = mySheet.createRow((short)0);
					 
			            rowhead.createCell(0).setCellValue("Customer Name");
			            rowhead.createCell(1).setCellValue("Invoice No");
			            rowhead.createCell(2).setCellValue("Invoice Date");
			            rowhead.createCell(3).setCellValue("Invoice Value");
			            rowhead.createCell(4).setCellValue("GST%");
			            rowhead.createCell(5).setCellValue("Taxable Value");
			            rowhead.createCell(6).setCellValue("CESS");
			            rowhead.createCell(7).setCellValue("Place Of Supply");
			          
			          
					 
				
					
					   
					 int rowCount = 1;
					   
						
								
				   for(GSTSaleBillInfoDAO dao:this.getFromDateToGSTReportDAOList())
			       {
					   
					   HSSFRow row = mySheet.createRow(rowCount);
			            
			            row.createCell(0).setCellValue(dao.getCustName());
			            row.createCell(1).setCellValue(dao.getBillNumber());
			            row.createCell(2).setCellValue(dao.getBillDate());
			            row.createCell(3).setCellValue(dao.getTotalAmount());
			            row.createCell(4).setCellValue(dao.getTaxPercentage());
			            row.createCell(5).setCellValue(dao.getTaxableAmount());
			            row.createCell(6).setCellValue("0");
			            row.createCell(7).setCellValue("MAHARASHTRA");
			            
			              
							
							 
							 
							 rowCount++;
				        	 
				         }
				   
				   
				   
					
					   
					   
					   FileOutputStream fileOut = new FileOutputStream(fileName);
					   workbook.write(fileOut);
			            fileOut.close();
			            workbook.close();
			            
			            
			    		response.setHeader("Content-disposition", "inline; filename="+fileName);
						response.setContentType("application/xlsx");
						FileInputStream fileInputStream = new FileInputStream(fileName);
						PrintWriter out = response.getWriter();  
						int bytes;
						while ((bytes = fileInputStream.read()) != -1) {
							out.write(bytes);
						}
						fileInputStream.close();
						out.close();
					 
					 
					
				}
				
				if(this.getSheet().equals("b2cs"))
				{
					
				
					 
					String fileName = "GSTR_1_Report_Sheet-"+this.getSheet()+".xls";
					
					
					 HSSFWorkbook workbook = new HSSFWorkbook();
			            HSSFSheet mySheet = workbook.createSheet("GSTR_1_Report-Sheet-"+ this.getSheet() +" From -"+this.getFromDate()+"-to-"+this.getToDate());
			            
					mySheet.setColumnWidth(0, 10 * 256);  // GST% - set to 10 characters
					mySheet.setColumnWidth(1, 20 * 256);  // Taxable Value - set to 20 characters
					mySheet.setColumnWidth(2, 10 * 256);  // CESS - set to 10 characters
					mySheet.setColumnWidth(3, 25 * 256);  // Place Of Supply - set to 25 characters
					


					
					HSSFRow rowhead = mySheet.createRow((short)0);
					
		            rowhead.createCell(0).setCellValue("GST%");
		            rowhead.createCell(1).setCellValue("Taxable Value");
		            rowhead.createCell(2).setCellValue("CESS");
		            rowhead.createCell(3).setCellValue("Place Of Supply");
		            
		             
					 
					
					 
					   
					 int rowCount = 1;
					   
						
								
				   for(GSTSaleBillInfoDAO dao:this.getFromDateToGSTReportDAOList())
			       {
					   
					   
					   HSSFRow row = mySheet.createRow(rowCount);
			            
			            row.createCell(0).setCellValue(dao.getTaxPercentage());
			            row.createCell(1).setCellValue(dao.getTaxableAmount());
			            row.createCell(2).setCellValue("0");
			            row.createCell(3).setCellValue("MAHARASHTRA");
			            
			             
							 
							 
							 rowCount++;
				        	 
				         }
				   
				   
				   
					
					   
					   
					   FileOutputStream fileOut = new FileOutputStream(fileName);
					   workbook.write(fileOut);
			            fileOut.close();
			            workbook.close();
			            
			            
			    		response.setHeader("Content-disposition", "inline; filename="+fileName);
						response.setContentType("application/xlsx");
						FileInputStream fileInputStream = new FileInputStream(fileName);
						PrintWriter out = response.getWriter();  
						int bytes;
						while ((bytes = fileInputStream.read()) != -1) {
							out.write(bytes);
						}
						fileInputStream.close();
						out.close();
					 
					 
					
				}
				
				if(this.getSheet().equals("hsn"))
				{
					
					 
					String fileName = "GSTR_1_Report_Sheet-"+this.getSheet()+".xls";
					
					
					HSSFWorkbook workbook = new HSSFWorkbook();
		            HSSFSheet mySheet = workbook.createSheet("GSTR_1_Report-Sheet-"+ this.getSheet() +" From -"+this.getFromDate()+"-to-"+this.getToDate());
		            
		            
					
					mySheet.setColumnWidth(0, 15 * 256);  // HSN - set to 15 characters
					mySheet.setColumnWidth(1, 30 * 256);  // Description - set to 30 characters
					mySheet.setColumnWidth(2, 20 * 256);  // UQC - set to 10 characters
					mySheet.setColumnWidth(3, 15 * 256);  // Total Quantity - set to 15 characters
					mySheet.setColumnWidth(4, 20 * 256);  // Total Value - set to 20 characters
					mySheet.setColumnWidth(5, 10 * 256);  // Rate - set to 10 characters
					mySheet.setColumnWidth(6, 20 * 256);  // Total Taxable Value - set to 20 characters
					mySheet.setColumnWidth(7, 15 * 256);  // IGST - set to 15 characters
					mySheet.setColumnWidth(8, 15 * 256);  // CGST - set to 15 characters
					mySheet.setColumnWidth(9, 15 * 256);  // SGST - set to 15 characters
					mySheet.setColumnWidth(10, 10 * 256); // CESS - set to 10 characters
					 
					 
					
					
					HSSFRow rowhead = mySheet.createRow((short)0);
					
		            rowhead.createCell(0).setCellValue("HSN");
		            rowhead.createCell(1).setCellValue("Description");
		            rowhead.createCell(2).setCellValue("UQC");
		            rowhead.createCell(3).setCellValue("Total Quantity");
		            rowhead.createCell(4).setCellValue("Total Value");
		            rowhead.createCell(5).setCellValue("Rate");
		            rowhead.createCell(6).setCellValue("Total Taxable Value");
		            rowhead.createCell(7).setCellValue("IGST");
		            rowhead.createCell(8).setCellValue("CGST");
		            rowhead.createCell(9).setCellValue("SGST");
		            rowhead.createCell(10).setCellValue("CESS");
		            
		          
					
					   
					 int rowCount = 1;
					   
						
								
				   for(GSTSaleBillInfoDAO dao:this.getFromDateToGSTReportDAOList())
			       {
					   
					   HSSFRow row = mySheet.createRow(rowCount);
			            
			            row.createCell(0).setCellValue(dao.getHsnCode());
			            row.createCell(1).setCellValue(dao.getItemName());
			            row.createCell(2).setCellValue(dao.getSalesUnit());
			            row.createCell(3).setCellValue(dao.getItemQuantity());
			            row.createCell(4).setCellValue(dao.getTotalAmount());
			            row.createCell(5).setCellValue(dao.getItemRate());
			            row.createCell(6).setCellValue(dao.getTaxableAmount());
			            row.createCell(7).setCellValue(dao.getIgst());
			            row.createCell(8).setCellValue(dao.getCgst());
			            row.createCell(9).setCellValue(dao.getSgst());
			            row.createCell(10).setCellValue("0");
			           
			            
							
							
							 
							 
							 rowCount++;
				        	 
				         }
				   
				   
				   
					
					   
					   
					   FileOutputStream fileOut = new FileOutputStream(fileName);
					   workbook.write(fileOut);
			            fileOut.close();
			            workbook.close();
			            
			            
			    		response.setHeader("Content-disposition", "inline; filename="+fileName);
						response.setContentType("application/xlsx");
						FileInputStream fileInputStream = new FileInputStream(fileName);
						PrintWriter out = response.getWriter();  
						int bytes;
						while ((bytes = fileInputStream.read()) != -1) {
							out.write(bytes);
						}
						fileInputStream.close();
						out.close();
					 
					 
					
				}
				
				if(this.getSheet().equals("Docs_issued"))
				{
					
					 
					String fileName = "GSTR_1_Report_Sheet-"+this.getSheet()+".xlsx";
					
					XSSFWorkbook workbook = new XSSFWorkbook();
					XSSFSheet mySheet = workbook.createSheet("GSTR_1_Report-Sheet-"+ this.getSheet() +" From -"+this.getFromDate()+"-to-"+this.getToDate());  
					mySheet.setColumnWidth(0, 25 * 256);  // Type Of Document - set to 25 characters
					mySheet.setColumnWidth(1, 15 * 256);  // Series From - set to 15 characters
					mySheet.setColumnWidth(2, 15 * 256);  // Series To - set to 15 characters
					mySheet.setColumnWidth(3, 15 * 256);  // Total Number - set to 15 characters
					mySheet.setColumnWidth(4, 15 * 256);  // Cancelled - set to 15 characters
					mySheet.setColumnWidth(5, 15 * 256);  // Net Issued - set to 15 characters

					 	Font font = workbook.createFont();
				       
				        font.setBold(true);
				       
					 final XSSFCellStyle cellStyle = workbook.createCellStyle();

					 cellStyle.setBorderBottom(BorderStyle.THIN);
					 cellStyle.setBorderRight(BorderStyle.THIN);
					 cellStyle.setBorderLeft(BorderStyle.THIN);
					 cellStyle.setBorderTop(BorderStyle.THIN);
					 cellStyle.setFont(font);
					 cellStyle.setWrapText(true);
					 
					 final XSSFCellStyle cellStyleNonBold = workbook.createCellStyle();

					 cellStyleNonBold.setBorderBottom(BorderStyle.THIN);
					 cellStyleNonBold.setBorderRight(BorderStyle.THIN);
					 cellStyleNonBold.setBorderLeft(BorderStyle.THIN);
					 cellStyleNonBold.setBorderTop(BorderStyle.THIN);
					 
					 short s = 255-0-0;
					 final XSSFCellStyle cellStyleBoldRed = workbook.createCellStyle();	 
					 
					 cellStyleBoldRed.setBorderBottom(BorderStyle.THIN);
					 cellStyleBoldRed.setBorderRight(BorderStyle.THIN);
					 cellStyleBoldRed.setBorderLeft(BorderStyle.THIN);
					 cellStyleBoldRed.setBorderTop(BorderStyle.THIN);
					 cellStyleBoldRed.setFillForegroundColor(s);
					 cellStyleBoldRed.setFont(font);								 
					 cellStyleBoldRed.setWrapText(true);
					 
					 XSSFRow rowHead = mySheet.createRow(0);							 
					 
					 Cell cellHead1 = rowHead.createCell(0);
					 cellHead1.setCellValue("Type Of Document");
					 cellHead1.setCellStyle(cellStyleBoldRed);
					 
					 Cell cellHead2 = rowHead.createCell(1);
					 cellHead2.setCellValue("Series From");
					 cellHead2.setCellStyle(cellStyleBoldRed);
					 
					 Cell cellHead3 = rowHead.createCell(2);
					 cellHead3.setCellValue("Series To");
					 cellHead3.setCellStyle(cellStyleBoldRed);
					 
					 
					 Cell cellHead4 = rowHead.createCell(3);
					 cellHead4.setCellValue("Total Number");
					 cellHead4.setCellStyle(cellStyleBoldRed);
					 
					 
					 Cell cellHead5 = rowHead.createCell(4);
					 cellHead5.setCellValue("Cancelled");
					 cellHead5.setCellStyle(cellStyleBoldRed);
					 
					 
					 Cell cellHead6 = rowHead.createCell(5);
					 cellHead6.setCellValue("Net Issued");
					 cellHead6.setCellStyle(cellStyleBoldRed);
					 
					 
					
					   
					 int rowCount = 1;
					   
						
								
				   for(GSTSaleBillInfoDAO dao:this.getFromDateToGSTReportDAOList())
			       {
					   		XSSFRow rowData = mySheet.createRow(rowCount);
							  
							 Cell cell1 = rowData.createCell(0);
							 cell1.setCellValue("");
							 cell1.setCellStyle(cellStyleNonBold);
							 
							  
							 Cell cell2 = rowData.createCell(1);
							 cell2.setCellValue("");
							 cell2.setCellStyle(cellStyleNonBold);						 
							 
							  
							 Cell cell3 = rowData.createCell(2);
							 cell3.setCellValue("");
							 cell3.setCellStyle(cellStyleNonBold);
							 
							 Cell cell4 = rowData.createCell(3);
							 cell4.setCellValue("");
							 cell4.setCellStyle(cellStyleNonBold);
							 
							 Cell cell5 = rowData.createCell(4);
							 cell5.setCellValue("");
							 cell5.setCellStyle(cellStyleNonBold);
							 
							 Cell cell6 = rowData.createCell(5);
							 cell6.setCellValue("");
							 cell6.setCellStyle(cellStyleNonBold);
							 
							
							 
							 rowCount++;
				        	 
				         }
				   
				   
				   
					
					   
					   
					   FileOutputStream fileOut = new FileOutputStream(fileName);
					   workbook.write(fileOut);
			            fileOut.close();
			            workbook.close();
			            
			            
			    		response.setHeader("Content-disposition", "inline; filename="+fileName);
						response.setContentType("application/xlsx");
						FileInputStream fileInputStream = new FileInputStream(fileName);
						PrintWriter out = response.getWriter();  
						int bytes;
						while ((bytes = fileInputStream.read()) != -1) {
							out.write(bytes);
						}
						fileInputStream.close();
						out.close();
					 
					 
					
				}
				
				
				
		}
	  
		
			}catch (SQLException e) {
				
				//e.printStackTrace();
				
				addActionError(e.getErrorCode()+" || "+e.getMessage() +" || "+"Details Not Found");

			
		}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 void setSession(Map<String, Object> arg0) {
		// TODO Auto-generated method stub
		sessionMap=(SessionMap<String, Object>)arg0;
	}


	public HttpSession getSession() {
		return session;
	}


	public void setSession(HttpSession session) {
		this.session = session;
	}


	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 getSource() {
		return Source;
	}



	public void setSource(String source) {
		Source = source;
	}



	public String getCustomerName() {
		return customerName;
	}


	public void setCustomerName(String customerName) {
		this.customerName = customerName;
	}


	public String getSheet() {
		return Sheet;
	}


	public void setSheet(String sheet) {
		Sheet = sheet;
	}


	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 SessionMap<String, Object> getSessionMap() {
		return sessionMap;
	}


	public void setSessionMap(SessionMap<String, Object> sessionMap) {
		this.sessionMap = sessionMap;
	}



	public List<GSTSaleBillInfoDAO> getFromDateToGSTReportDAOList() {
		return fromDateToGSTReportDAOList;
	}



	public void setFromDateToGSTReportDAOList(List<GSTSaleBillInfoDAO> fromDateToGSTReportDAOList) {
		this.fromDateToGSTReportDAOList = fromDateToGSTReportDAOList;
	}



	
	
	
	}
	
