package com.reallinkbilling.report;

import java.io.IOException;
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.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;

public class GetGSTInvoiceReport extends ActionSupport implements SessionAware{

	
	/**
	 * 
	 */
	private static final long serialVersionUID = 3889256261560470796L;
	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;  
	private String addSideBarJs;

	 
	@SuppressWarnings("resource")
	public String execute() {
		
		String result = "login";
		  session=ServletActionContext.getRequest().getSession(false);  
	        if(session==null || session.getAttribute("login")==null){ 
	        	addActionMessage(this.getText("message.error.plzloginfirst"));
	        	result= "login";  
	        }  
	        else
	        {
		
			
		
		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)
			  {
				  result = SUCCESS;
			  }else {
				  result = ERROR;
				  addActionError("Records Not Found For Specific Dates !");
			  }
				  
			 
		}
		catch (SQLException e) {
			//e.printStackTrace();
			result = "error";
			addActionError("Records Not Found For Specific Dates !");
			
			
		} catch (IOException e) {
		
			result="error";
			//e.printStackTrace();
			addActionError("Records Not Found For Specific Dates !");
		}catch (Exception e) {
			// TODO: handle exception
			//e.printStackTrace();
		}
		
		
		finally
		{
			if(resultSet != null)
				{
					try {resultSet.close(); resultSet = null;}catch(SQLException sqe){}   					
				}
			if(preparedStatement != null)
			{
				try {
					preparedStatement.close(); preparedStatement = null;
				} catch (SQLException e) {
					
				}
			}
			
			if(connection != null)
			{
				try {
					connection.close(); connection = null;
				} catch (SQLException e) {
					
				}
			}
			
			
		
 				
 				
 			}
		
		
		}
	       
	       return result;
	        
	        
			
			}


public void validate() {
	
	this.setAddSideBarJs("FALSE");
	
	
	if(this.getFromDate() == null || this.getFromDate().equals(""))
	{
		addActionError("Please Select From Date !");
	}
	
	if(this.getToDate() == null || this.getToDate().equals(""))
	{
		addActionError("Please Select To Date !");
	}
	
	
	
}

			


	@Override
	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 List<GSTSaleBillInfoDAO> getFromDateToGSTReportDAOList() {
		return fromDateToGSTReportDAOList;
	}




	public void setFromDateToGSTReportDAOList(List<GSTSaleBillInfoDAO> fromDateToGSTReportDAOList) {
		this.fromDateToGSTReportDAOList = fromDateToGSTReportDAOList;
	}




	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 String getAddSideBarJs() {
		return addSideBarJs;
	}


	public void setAddSideBarJs(String addSideBarJs) {
		this.addSideBarJs = addSideBarJs;
	}


	public void setSessionMap(SessionMap<String, Object> sessionMap) {
		this.sessionMap = sessionMap;
	}
		}
	