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.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.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.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class DownloadGSTInvoiceReport extends ActionSupport implements SessionAware{

	
	/**
	 * 
	 */
	private static final long serialVersionUID = 6805671539467196118L;
	private HttpSession session;
	private String fromDate;
	private String toDate;
	private String customerName;
	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) {}
}
			
			
		  // Map to aggregate data by invoice number and tax slab
		  Map<String, GSTSaleBillInfoDAO> aggregatedMap = new LinkedHashMap<String, GSTSaleBillInfoDAO>();
		  
		  int srNo = 1;
		  for(String billNumber:billMap.keySet())
		  {
			  
			  String getProductInfoSQL = "";
			  PreparedStatement getProductInfoPSMT = null;
			  ResultSet getProductInfoRS = null;
			  
			  GSTSaleBillInfoDAO billDAO = 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())
			  {
				  
				  String taxid = df.format(getProductInfoRS.getFloat(9));
			      Map<String, String> taxInfo = GetListResource.GetTaxNamePercentage(taxid,this.getSessionMap());
			      String taxPercentage = taxInfo.get("TAX-PERCENTAGE");
			      
			      // Create key: billNumber_taxPercentage
			      String aggregationKey = billNumber + "_" + taxPercentage;
			      
			      if(aggregatedMap.containsKey(aggregationKey))
			      {
			    	  // Aggregate existing entry
			    	  GSTSaleBillInfoDAO existingDAO = aggregatedMap.get(aggregationKey);
			    	  
			    	  float existingTaxable = Float.parseFloat(existingDAO.getTaxableAmount());
			    	  float newTaxable = getProductInfoRS.getFloat(5);
			    	  float totalTaxable = existingTaxable + newTaxable;
			    	  
			    	  float existingCgst = Float.parseFloat(existingDAO.getCgst());
			    	  float newCgst = getProductInfoRS.getFloat(8);
			    	  float totalCgst = existingCgst + newCgst;
			    	  
			    	  float existingSgst = Float.parseFloat(existingDAO.getSgst());
			    	  float newSgst = getProductInfoRS.getFloat(7);
			    	  float totalSgst = existingSgst + newSgst;
			    	  
			    	  float existingIgst = Float.parseFloat(existingDAO.getIgst());
			    	  float newIgst = getProductInfoRS.getFloat(6);
			    	  float totalIgst = existingIgst + newIgst;
			    	  
			    	  existingDAO.setTaxableAmount(df.format(totalTaxable));
			    	  existingDAO.setCgst(df.format(totalCgst));
			    	  existingDAO.setSgst(df.format(totalSgst));
			    	  existingDAO.setIgst(df.format(totalIgst));
			      }
			      else
			      {
			    	  // Create new entry
			    	  GSTSaleBillInfoDAO newDAO  = new GSTSaleBillInfoDAO();
			    	  
			    	  newDAO.setBillNumber(billDAO.getBillNumber());
			    	  newDAO.setBillDate(billDAO.getBillDate());
			    	  newDAO.setCustId(billDAO.getCustId());
			    	  newDAO.setCustName(billDAO.getCustName());
			    	  newDAO.setCustAddress(billDAO.getCustAddress());
			    	  newDAO.setCustGstNumber(billDAO.getCustGstNumber());
			    	  newDAO.setTransportCharges(billDAO.getTransportCharges());
			    	  newDAO.setBillTotalAmount(billDAO.getBillTotalAmount());
			    	  newDAO.setBilledBy(billDAO.getBilledBy());
			    	  newDAO.setComments(billDAO.getComments());
			    	  
			    	  newDAO.setSrNo(""+srNo);
			    	  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.setTaxPercentage(taxPercentage);
			    	  
			    	  aggregatedMap.put(aggregationKey, newDAO);
			    	  srNo++;
			      }
			  }
			  
			 
			  if(getProductInfoRS != null)
			  {
				  getProductInfoRS.close();
				  getProductInfoRS = null;
			  }
			  
			  if(getProductInfoPSMT != null)
			  {
				  getProductInfoPSMT.close();
				  getProductInfoPSMT = null;
			  }
			  
		  }
		  
		  // Add all aggregated entries to the list
		  for(GSTSaleBillInfoDAO dao : aggregatedMap.values())
		  {
			  this.getFromDateToGSTReportDAOList().add(dao);
		  }
	  
	  
	  
	  if(this.getFromDateToGSTReportDAOList().size()>0)
		{
			
			
				
		  
				
				response = ServletActionContext.getResponse();
				request = ServletActionContext.getRequest();
				
				
				 
				String fileName = "GST_Invoice_Report.xls";
				
				
				 HSSFWorkbook workbook = new HSSFWorkbook();
		            HSSFSheet mySheet = workbook.createSheet("GST Invoice Report From -"+this.getFromDate()+"-to-"+this.getToDate()); 
		            
			mySheet.setColumnWidth(0, 10 * 256);  // Sr No - set to 10 characters
			mySheet.setColumnWidth(1, 15 * 256);  // Invoice Date - set to 15 characters
			mySheet.setColumnWidth(2, 20 * 256);  // Invoice Number - set to 20 characters
			mySheet.setColumnWidth(3, 40 * 256);  // Customer Name - set to 40 characters
			mySheet.setColumnWidth(4, 20 * 256);  // Customer GST Number - set to 20 characters
			mySheet.setColumnWidth(5, 20 * 256);  // Taxable Value - set to 20 characters
			mySheet.setColumnWidth(6, 15 * 256);  // CGST - set to 15 characters
			mySheet.setColumnWidth(7, 15 * 256);  // SGST - set to 15 characters
			mySheet.setColumnWidth(8, 15 * 256);  // IGST - set to 15 characters
			mySheet.setColumnWidth(9, 15 * 256);  // Tax Slab (Percentage) - set to 15 characters
			mySheet.setColumnWidth(10, 20 * 256); // Invoice Value (Amount) - set to 20 characters
			
			 
			 	
			 
			 HSSFRow rowhead = mySheet.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 Number");
	            rowhead.createCell(5).setCellValue("Taxable Value");
	            rowhead.createCell(6).setCellValue("CGST");
	            rowhead.createCell(7).setCellValue("SGST");
	            rowhead.createCell(8).setCellValue("IGST");
	            rowhead.createCell(9).setCellValue("Tax Slab (Percentage)");
	            rowhead.createCell(10).setCellValue("Invoice Value (Amount)");
	            
			
			
			   
			 int rowCount = 1;
			   
				
						
		   for(GSTSaleBillInfoDAO dao:this.getFromDateToGSTReportDAOList())
	       {
			   
			   HSSFRow row = mySheet.createRow(rowCount);
	            
			   
			   row.createCell(0).setCellValue(dao.getSrNo());
	            row.createCell(1).setCellValue(dao.getBillDate());
	            row.createCell(2).setCellValue(dao.getBillNumber());
	            row.createCell(3).setCellValue(dao.getCustName());
	            row.createCell(4).setCellValue(dao.getCustGstNumber() != null ? dao.getCustGstNumber() : "");
	            row.createCell(5).setCellValue(dao.getTaxableAmount());
	            row.createCell(6).setCellValue(dao.getCgst());
	            row.createCell(7).setCellValue(dao.getSgst());
	            row.createCell(8).setCellValue(dao.getIgst());
	            row.createCell(9).setCellValue(dao.getTaxPercentage());
	            row.createCell(10).setCellValue(dao.getBillTotalAmount());
	           
				
				 
				 
				 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 getCustomerName() {
		return customerName;
	}


	public void setCustomerName(String customerName) {
		this.customerName = customerName;
	}


	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;
	}


	
	

	
	}
	