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.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.DAO.ProductBarcodeDetailsDAO;
import com.reallinkbilling.db.GetDBConnection;

public class GetGSTSalesReport extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = 3502790352342908340L;
	private List<GSTSaleBillInfoDAO> fromDateToGSTReportDAOList = new ArrayList<GSTSaleBillInfoDAO>();
	
	private  HttpSession session;

	private String fromDate;
	private String toDate;
	private String reportFormat;

	private ArrayList<String> reportFormatList = new ArrayList<String>();
	
	
	private SessionMap<String,Object> sessionMap;  


	public String execute() {
		
		String result = "login";
		  session=ServletActionContext.getRequest().getSession(false);  
	        if(session==null || session.getAttribute("login")==null){ 
	        	addActionMessage("Please Login First !!!");
	        	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");
			
			
			  
			try {
				
				DecimalFormat df = 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 = "";
	        	 }
	        	 
				 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);
				   
				  //System.out.println(preparedStatement.toString());
				  
				  resultSet = preparedStatement.executeQuery();
				  
				  while(resultSet.next())
				  {
					  try
					  {
						  GSTSaleBillInfoDAO dao = new GSTSaleBillInfoDAO();
					
						  dao.setBillNumber(billNoPreFix+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(billNoPreFix+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);
						  
						  //System.out.println(billNoPreFix+resultSet.getString(1)+","+resultSet.getString(3));
					  }
				
					   
					
				  }
				  
				  
				  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;
					  }
					  
					  
					  
				  }
				  
			
					  
					
		    			
					  result = SUCCESS;
				 
			}
			catch (SQLException e) {
				e.printStackTrace();
				result = "error";
				addActionError("Records not found for specified dates!");
				
				
			} catch (IOException e) {
			
				result="error";
				//e.printStackTrace();
				addActionError("Records not found for specified dates!");
			}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;
}
	
	@Override
	public void validate() {
		if(this.getFromDate().equals("") && this.getToDate().equals(""))
		{
			addActionError("Please select dates!");
		}
		
		
		
    	this.getReportFormatList().add("EXCEL");
    	this.getReportFormatList().add("PDF");
    	
	}
	
	
	public void setSession(Map<String, Object> map) {
		// TODO Auto-generated method stub
		 sessionMap=(SessionMap<String, Object>)map;  
		
	}

	

	public SessionMap<String, Object> getSessionMap() {
		return sessionMap;
	}


	public void setSessionMap(SessionMap<String, Object> sessionMap) {
		this.sessionMap = sessionMap;
	}


	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 ArrayList<String> getReportFormatList() {
		return reportFormatList;
	}

	public void setReportFormatList(ArrayList<String> reportFormatList) {
		this.reportFormatList = reportFormatList;
	}

	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 static long getSerialversionuid() {
		return serialVersionUID;
	}

	
	
}

