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.Comparator;
import java.util.HashMap;
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;

public class GetGSTR_1Report extends ActionSupport implements SessionAware{

	
	/**
	 * 
	 */
	private static final long serialVersionUID = 3889256261560470795L;
	private HttpSession session;
	private String fromDate;
	private String toDate;
	private String customerName;
	private String Sheet;
	
	
	private List<GSTSaleBillInfoDAO> fromDateToGSTReportDAOList = new ArrayList<GSTSaleBillInfoDAO>();
	
	private Map<String,String> SheetList = new HashMap<String,String>();
	
	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);
						  
					
				}
				
				//System.out.println(preparedStatement.toString());
				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)
			  {
				  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");
	
	
	
	
	SheetList.put("b2b", "b2b");
	SheetList.put("b2cl", "b2cl");
	SheetList.put("b2cs","b2cs");
	SheetList.put("hsn","hsn");
	/* SheetList.put("Docs_issued","Docs_issued"); */
	
	
	
	
	if(this.getFromDate() == null || this.getFromDate().equals(""))
	{
		addActionError("Please Select From Date !");
	}
	
	if(this.getToDate() == null || this.getFromDate().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 Map<String, String> getSheetList() {
		return SheetList;
	}


	public void setSheetList(Map<String, String> sheetList) {
		SheetList = sheetList;
	}


	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 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;
	}
		}
	
	