package com.reallinkbilling.view;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
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.SupplierAccountDetailsDAO;
import com.reallinkbilling.cipher.EncryptionDecryption;
import com.reallinkbilling.db.GetDBConnection;


import java.text.ParseException;

public class ViewSuppAccountDetails extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = 3694238914060913156L;

	
	private HttpSession session;
	private String key;
	private String supplierName;
	private String supplierMobile;
	private String supplierAddress;
	private Map<String,String> customerSearchOptionList = new HashMap<String,String>();
	private String customerSearchOption;
	private String defaultValue;
	private String fromDate;
	private String toDate;
	private SessionMap<String,Object> sessionMap;
	
	private Float totalPurchaseAmountBeforeDate;
	private Float totalAmountPaidBeforeDate;
	private Float totalDebtAmountBeforeDate;
	
	private String totalPurchaseAmountAllTheTime;
	private String totalAmountPaidAllTheTime;
	private String totalDebtAmountAllTheTime;
	
	private List<SupplierAccountDetailsDAO> supplierAccountDetailsList = new ArrayList<SupplierAccountDetailsDAO>();
	private List<SupplierAccountDetailsDAO> supplierAccountDetailsSortedList = new ArrayList<SupplierAccountDetailsDAO>();
	
	private Float supplierOpeningBalnce = 0.0F;
	private String opeingBalancUpdatedBy;
	private String opeingBalanceUpdateDate;
	
	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{
	        	
	           	String shopId = (String) this.getSessionMap().get("shopId");
	    		String shopCode = (String) this.getSessionMap().get("shopCode");
	        	
	        	customerSearchOptionList.put("BYNAME", "BY NAME");
	        	customerSearchOptionList.put("BYMOBILE", "BY MOBILE");
	        	customerSearchOptionList.put("BYADDRESS","BY ADDRESS");
				setCustomerSearchOption("BYNAME");
				setDefaultValue("BYNAME");
	        
	        	String supplierId = EncryptionDecryption.decrypt(getKey());
	        
	        	setKey(EncryptionDecryption.encrypt(supplierId));
	        	
	        	DecimalFormat df  = new DecimalFormat("##");

	        	Connection connection = null;
	       		PreparedStatement preparedStatement= null;
	       		ResultSet resultSet = null;
	       		
	       		
	       		String sql = "";
	       		String db = (String) this.getSessionMap().get("db");
	       		try {
	       			
	       			
	       			
					connection = GetDBConnection.getDBConnection(db);
	       			
	       			
	       			
	       			PreparedStatement getOpeningBalPSMT = null;
       				ResultSet getOpeningBalRs = null;
       				String getOpeningBalSQL = "SELECT "
       						+ "opening_balance_amount,"
       						+ "DATE_FORMAT(opening_balance_date,'%d/%m/%Y'),"
       						+ "entry_updated_by "
       						+ "FROM "
       						+ "shop_supplier_open_close_balance_master "
       						+ "WHERE "
       						+ "shop_id = ? "
       						+ "AND "
       						+ "shop_code = ? "
       						+ "AND "
       						+ "supplier_id = ? ";
       				getOpeningBalPSMT = connection.prepareStatement(getOpeningBalSQL);
       				
       				getOpeningBalPSMT.setString(1, shopId);
       				getOpeningBalPSMT.setString(2, shopCode);
       				getOpeningBalPSMT.setString(3, supplierId);
       				
       				getOpeningBalRs = getOpeningBalPSMT.executeQuery();
       				
       				if(getOpeningBalRs.next())
       				{
       					this.setSupplierOpeningBalnce(getOpeningBalRs.getFloat(1));
       					this.setOpeingBalanceUpdateDate(getOpeningBalRs.getString(2));
       					this.setOpeingBalancUpdatedBy(getOpeningBalRs.getString(3));
       				}
       				else
       				{
       					this.setSupplierOpeningBalnce(0.0F);
       				}
       				if(getOpeningBalRs != null)
       				{
       					getOpeningBalRs.close();
       					getOpeningBalRs = null;
       				}
       				if(getOpeningBalPSMT != null)
       				{
       					getOpeningBalPSMT.close();
       					getOpeningBalPSMT = null;
       				}
       				
	       			
	       			
	       			sql = "SELECT "
	       					+ "SUM(payment_amount) "
	       					+ "FROM "
	       					+ "shop_supplier_payment_details "
	       					+ "WHERE "
	       					+ "shop_id = ? "
	       					+ "AND "
	       					+ "shop_code = ? "
	       					+ "AND "
	       					+ "supplier_id = ? "
	       					+ "AND "
	       					+ "payment_date < STR_TO_DATE(?, '%d-%m-%Y') ";
	       			
	       			preparedStatement = connection.prepareStatement(sql);
	       			preparedStatement.setString(1, shopId);
	       			preparedStatement.setString(2, shopCode);
	       			preparedStatement.setString(3, supplierId);
	       			preparedStatement.setString(4, this.getFromDate());
	       			
	       			resultSet = preparedStatement.executeQuery();
	       			
	       			if(resultSet.next())
	       			{
	       				if(resultSet.getString(1) != null)
	       				{
	       					this.setTotalAmountPaidBeforeDate(resultSet.getFloat(1));
	       				}
	       				else
	       				{
	       					this.setTotalAmountPaidBeforeDate(0.0F);
	       				}
	       			}
	       			else
	       			{
	       				this.setTotalAmountPaidBeforeDate(0.0F);
	       			}
	       			
	       			if(resultSet != null)
	       			{
	       				resultSet.close();
	       				resultSet = null;
	       			}
	       			if(preparedStatement != null)
	       			{
	       				preparedStatement.close();
	       				preparedStatement = null;
	       			}
	       			
	       			
	       			float floatPurchaseAmt = 0.0F;
       				float floatAmtPaid = 0.0F;
       				float floatBalanceAmt = 0.0F;
       				float totalBalanceAmt = 0.0F;
       				float totalAmountPaid = this.getTotalAmountPaidBeforeDate();
       				float totalPurchaseAmountBeforeDate = 0.0F;
       				
       				
       				sql = "SELECT "
       						+ "total_purchase_amount,"
       						+ "purchase_amount_paid "
       						+ "FROM "
       						+ "shop_purchase_info_master "
       						+ "WHERE "
       						+ "shop_id = ? "
       						+ "AND "
       						+ "shop_code = ? "
       						+ "AND "
	       					+ "supplier_id = ? "
	       					+ "AND "
	       					+ "purchase_date < STR_TO_DATE(?, '%d-%m-%Y')";
	       			
	       			preparedStatement = connection.prepareStatement(sql);
	       			preparedStatement.setString(1, shopId);
	       			preparedStatement.setString(2, shopCode);
	       			preparedStatement.setString(3, supplierId);
	       			preparedStatement.setString(4, this.getFromDate());
	       			
	       			resultSet = preparedStatement.executeQuery();
	       			
	       			while(resultSet.next())
	       			{
	       				
	       				try
	       				{
	       					floatPurchaseAmt = Math.round(resultSet.getFloat(1));
	       					floatAmtPaid =  resultSet.getFloat(2);
	       				
	       					totalPurchaseAmountBeforeDate = totalPurchaseAmountBeforeDate + floatPurchaseAmt;
	       					
	       					floatBalanceAmt = resultSet.getFloat(1) - resultSet.getFloat(2);
	       					
	       					if(floatBalanceAmt > 0 )
	       					{
	       						totalBalanceAmt = totalBalanceAmt + floatBalanceAmt;
	       						totalAmountPaid = totalAmountPaid + floatAmtPaid;
	       					}
	       					else
	       					{
	       						totalAmountPaid = totalAmountPaid + floatAmtPaid;
	       					}
	       					
	       				}
	       				catch(Exception e)
	       				{
	       					
	       					addActionError("One Of the purchase invoice could not be processed!");
	       				}
	       				
	       			}
	       			if(!hasActionErrors())
	       			{
	       				this.setTotalPurchaseAmountBeforeDate(totalPurchaseAmountBeforeDate);
	       				this.setTotalAmountPaidBeforeDate(totalAmountPaid);
	       				this.setTotalDebtAmountBeforeDate((totalPurchaseAmountBeforeDate-totalAmountPaid));
	       				
       				
	       				if(resultSet != null)
	       				{
	       					resultSet.close();
	       					resultSet = null;
	       				}
	       				
	       				if(preparedStatement != null)
	       				{
	       					preparedStatement.close();
	       					preparedStatement = null;
	       				}
	       				
	       				
	       				sql = "SELECT "
	       						+ "entry_id,"
	       						+ "supplier_id,"
	       						+ "DATE_FORMAT(payment_date, '%d/%m/%Y'),"
	       						+ "payment_amount,"
	       						+ "payement_details,"
	       						+ "entry_by "
	       						+ "FROM "
	       						+ "shop_supplier_payment_details "
	       						+ "WHERE "
	       						+ "shop_id = ? "
	       						+ "AND "
	       						+ "shop_code = ? "
	       						+ "AND "
	       						+ "supplier_id = ?"
			       				+ "AND "
		       					+ "(payment_date BETWEEN "
			   					+ "STR_TO_DATE(?, '%d-%m-%Y') "
			   					+ "AND "
			   					+ "STR_TO_DATE(?, '%d-%m-%Y'))";
	       				
	       				preparedStatement = connection.prepareStatement(sql);
	       				
	       				preparedStatement.setString(1, shopId);
	       				preparedStatement.setString(2, shopCode);
	       				preparedStatement.setString(3, supplierId);
	       				preparedStatement.setString(4, this.getFromDate());
	       				preparedStatement.setString(5, this.getToDate());
	       			
	       				resultSet = preparedStatement.executeQuery();
	       				
	       				while(resultSet.next())
	       				{
	       					Date date1=new SimpleDateFormat("dd/MM/yyyy").parse(resultSet.getString(3)); 
	    	       			
	       				
	       					SupplierAccountDetailsDAO dao = new SupplierAccountDetailsDAO();
	       					dao.setPaymentPurchaseId(EncryptionDecryption.encrypt(resultSet.getString(1)));
	       					dao.setDate(date1);
	       					dao.setInvoiceNumber("PAYMENT ENTRY");
	       					dao.setAmountPaid(resultSet.getFloat(4));
	       					dao.setComments(resultSet.getString(5));
	       					dao.setEntryBy(resultSet.getString(6));
	       					dao.setBalanceAmount(0.0F);
	       					dao.setPurchaseAmount("0.0");       					
	       					dao.setEntryType("PAYMENT");
	       					this.getSupplierAccountDetailsList().add(dao);
	       				}
	       				
	       				if(resultSet != null)
	       				{
	       					resultSet.close();
	       					resultSet = null;
	       				}
	       				
	       				if(preparedStatement != null)
	       				{
	       					preparedStatement.close();
	       					preparedStatement = null;
	       				}
	       				
	       				sql = "SELECT "
	       						+ "purchase_number,"
	       						+ "purchase_invoice_number,"
	       						+ "DATE_FORMAT(purchase_date,'%d/%m/%Y'),"
	       						+ "transportation_charges,"
	       						+ "other_expenses,"
	       						+ "total_purchase_amount,"
	       						+ "purchase_amount_paid,"
	       						+ "comments,"
	       						+ "purchased_by,"
	       						+ "purchased_by "
	       						+ "FROM "
	       						+ "shop_purchase_info_master "
	       						+ "WHERE "
	       						+ "shop_id = ? "
	       						+ "ANd "
	       						+ "shop_code = ? "
	       						+ "AND "
	       						+ "supplier_id = ? "
	       						+ "AND "
		       					+ "(purchase_date BETWEEN "
			   					+ "STR_TO_DATE(?, '%d-%m-%Y') "
			   					+ "AND "
			   					+ "STR_TO_DATE(?, '%d-%m-%Y'))";
	       				
	       				preparedStatement = connection.prepareStatement(sql);
	       				
	       				preparedStatement.setString(1, shopId);
	       				preparedStatement.setString(2, shopCode);
	       				preparedStatement.setString(3, supplierId);
	       				preparedStatement.setString(4, this.getFromDate());
	       				preparedStatement.setString(5, this.getToDate());
	       				
	       				resultSet = preparedStatement.executeQuery();
	       				while(resultSet.next())
	       				{
	       					SupplierAccountDetailsDAO dao = new SupplierAccountDetailsDAO();
	       					Date date1=new SimpleDateFormat("dd/MM/yyyy").parse(resultSet.getString(3)); 
    	       				
	       					dao.setEntryType("PURCHASE");
	       					dao.setPaymentPurchaseId(resultSet.getString(1));
	       					dao.setInvoiceNumber(resultSet.getString(2));
	       					dao.setDate(date1);
	       					dao.setPurchaseAmount(df.format(resultSet.getFloat(6)));
	       					dao.setAmountPaid(resultSet.getFloat(7));
	       					dao.setBalanceAmount(Math.round(resultSet.getFloat(6)) - resultSet.getFloat(7));
	       					dao.setComments(resultSet.getString(8));
	       					dao.setPurchaseBy(resultSet.getString(9));
	       					dao.setEntryBy(resultSet.getString(10));
	       					dao.setDebtAmount(df.format(0.0F));
	       					this.getSupplierAccountDetailsList().add(dao);
	       					
	       				}
	       				
	       				float totalAmountPaidBetweenDates = 0.0F;
	       				float totalPurchaseBetweenDates = 0.0F;
	       				float totalDebtBetwwenDates = 0.0F;
	       				
	       				for(SupplierAccountDetailsDAO dao : this.getSupplierAccountDetailsList())
	       				{
	       				 DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yy");  
						 Date d = dao.getDate();
						 dao.setDateAsString(dateFormat.format(d));
	       					
	       					try
	       					{
		       					totalAmountPaidBetweenDates = totalAmountPaidBetweenDates + dao.getAmountPaid();
		       					totalPurchaseBetweenDates = totalPurchaseBetweenDates + Float.parseFloat(dao.getPurchaseAmount());
		       					
	       					}
	       					catch(NumberFormatException nfe)
	       					{
	       						addActionError("Error while nuber formation!");
	       						
	       					}
	       				}
	       				totalDebtBetwwenDates = totalPurchaseBetweenDates - totalAmountPaidBetweenDates;
	       				
	       				this.setTotalAmountPaidAllTheTime(df.format(this.getTotalAmountPaidBeforeDate() + totalAmountPaidBetweenDates));
	       				this.setTotalPurchaseAmountAllTheTime(df.format((Float.parseFloat(df.format(this.getTotalPurchaseAmountBeforeDate()))+ totalPurchaseBetweenDates)));
	       				
	       				float totalPurAmt =  Float.valueOf(this.getTotalPurchaseAmountAllTheTime());
	       				float totalPaidAmt = this.getTotalAmountPaidBeforeDate() + totalAmountPaidBetweenDates;
	       				this.setTotalDebtAmountAllTheTime(df.format(totalPurAmt - totalPaidAmt));
		       		
	       				if(!hasActionErrors())
	       				{
	       					

   						 
   						 Collections.sort(this.getSupplierAccountDetailsList(), new Comparator<SupplierAccountDetailsDAO>() {
   							  public int compare(SupplierAccountDetailsDAO o1, SupplierAccountDetailsDAO o2) {
   							      if (o1.getDate() == null || o2.getDate() == null)
   							        return 0;
   							      return o1.getDate().compareTo(o2.getDate());
   							  }
   							});
	       					
   						 
   						 float currentDebtAmount = this.getSupplierOpeningBalnce() + this.getTotalDebtAmountBeforeDate();
   						 
   						for(SupplierAccountDetailsDAO dao : this.getSupplierAccountDetailsList())
	       				{
   							if(dao.getEntryType().equals("PURCHASE"))
   							{
   								dao.setDebtAmount(df.format(currentDebtAmount + dao.getBalanceAmount()));
   								
   								currentDebtAmount = currentDebtAmount + dao.getBalanceAmount();
   							}
   							else if(dao.getEntryType().equals("PAYMENT"))
   							{
   								dao.setDebtAmount(df.format(currentDebtAmount - dao.getAmountPaid()));
   								currentDebtAmount = currentDebtAmount -  dao.getAmountPaid();
   							}
	       				}
	       					
	       					result = "success";
	       				}
	       				else
	       				{
	       					result = "error";
	       				}
       				
	       			}
	       			else
	       			{
	       				result = "error";
	       			}
       				
       				
       				
	       			
	       		}catch(ParseException pe)
	       		{
	       			result = "error";
	    			addActionError(pe.getMessage() +" || DETAILS NOT FOUND !");
	       		}
	       		catch (SQLException e) {
	    			result = "error";
	    			addActionError(e.getErrorCode()+" || "+e.getMessage() +" || DETAILS NOT FOUND !");
	    		} catch (IOException e) {
	    			
	    			result = "error";
	    			addActionError(e.getMessage()+" || "+e.getCause()+" || DETAILS NOT FOUND !");
	    		}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) {}
	    			}
	    			
	    		}
	       		
	        }
	        
	        return result;
}



public HttpSession getSession() {
	return session;
}



public void setSession(HttpSession session) {
	this.session = session;
}



public String getKey() {
	return key;
}



public void setKey(String key) {
	this.key = key;
}



public String getSupplierName() {
	return supplierName;
}



public void setSupplierName(String supplierName) {
	this.supplierName = supplierName;
}



public String getSupplierMobile() {
	return supplierMobile;
}



public void setSupplierMobile(String supplierMobile) {
	this.supplierMobile = supplierMobile;
}



public String getSupplierAddress() {
	return supplierAddress;
}



public void setSupplierAddress(String supplierAddress) {
	this.supplierAddress = supplierAddress;
}



public Map<String, String> getCustomerSearchOptionList() {
	return customerSearchOptionList;
}



public void setCustomerSearchOptionList(Map<String, String> customerSearchOptionList) {
	this.customerSearchOptionList = customerSearchOptionList;
}



public String getCustomerSearchOption() {
	return customerSearchOption;
}



public void setCustomerSearchOption(String customerSearchOption) {
	this.customerSearchOption = customerSearchOption;
}



public String getDefaultValue() {
	return defaultValue;
}



public void setDefaultValue(String defaultValue) {
	this.defaultValue = defaultValue;
}



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 static long getSerialversionuid() {
	return serialVersionUID;
}



public Float getTotalPurchaseAmountBeforeDate() {
	return totalPurchaseAmountBeforeDate;
}



public void setTotalPurchaseAmountBeforeDate(Float totalPurchaseAmountBeforeDate) {
	this.totalPurchaseAmountBeforeDate = totalPurchaseAmountBeforeDate;
}



public Float getTotalAmountPaidBeforeDate() {
	return totalAmountPaidBeforeDate;
}



public void setTotalAmountPaidBeforeDate(Float totalAmountPaidBeforeDate) {
	this.totalAmountPaidBeforeDate = totalAmountPaidBeforeDate;
}



public Float getTotalDebtAmountBeforeDate() {
	return totalDebtAmountBeforeDate;
}



public void setTotalDebtAmountBeforeDate(Float totalDebtAmountBeforeDate) {
	this.totalDebtAmountBeforeDate = totalDebtAmountBeforeDate;
}




public String getTotalPurchaseAmountAllTheTime() {
	return totalPurchaseAmountAllTheTime;
}



public void setTotalPurchaseAmountAllTheTime(String totalPurchaseAmountAllTheTime) {
	this.totalPurchaseAmountAllTheTime = totalPurchaseAmountAllTheTime;
}



public String getTotalAmountPaidAllTheTime() {
	return totalAmountPaidAllTheTime;
}



public void setTotalAmountPaidAllTheTime(String totalAmountPaidAllTheTime) {
	this.totalAmountPaidAllTheTime = totalAmountPaidAllTheTime;
}






public String getTotalDebtAmountAllTheTime() {
	return totalDebtAmountAllTheTime;
}



public void setTotalDebtAmountAllTheTime(String totalDebtAmountAllTheTime) {
	this.totalDebtAmountAllTheTime = totalDebtAmountAllTheTime;
}



public List<SupplierAccountDetailsDAO> getSupplierAccountDetailsList() {
	return supplierAccountDetailsList;
}



public void setSupplierAccountDetailsList(List<SupplierAccountDetailsDAO> supplierAccountDetailsList) {
	this.supplierAccountDetailsList = supplierAccountDetailsList;
}



public List<SupplierAccountDetailsDAO> getSupplierAccountDetailsSortedList() {
	return supplierAccountDetailsSortedList;
}



public void setSupplierAccountDetailsSortedList(List<SupplierAccountDetailsDAO> supplierAccountDetailsSortedList) {
	this.supplierAccountDetailsSortedList = supplierAccountDetailsSortedList;
}



public Float getSupplierOpeningBalnce() {
	return supplierOpeningBalnce;
}



public void setSupplierOpeningBalnce(Float supplierOpeningBalnce) {
	this.supplierOpeningBalnce = supplierOpeningBalnce;
}



public String getOpeingBalancUpdatedBy() {
	return opeingBalancUpdatedBy;
}



public void setOpeingBalancUpdatedBy(String opeingBalancUpdatedBy) {
	this.opeingBalancUpdatedBy = opeingBalancUpdatedBy;
}



public String getOpeingBalanceUpdateDate() {
	return opeingBalanceUpdateDate;
}



public void setOpeingBalanceUpdateDate(String opeingBalanceUpdateDate) {
	this.opeingBalanceUpdateDate = opeingBalanceUpdateDate;
}
	
public SessionMap<String, Object> getSessionMap() {
	return sessionMap;
}

public void setSessionMap(SessionMap<String, Object> sessionMap) {
	this.sessionMap = sessionMap;
}
 
public void setSession(Map<String, Object> map) {  
    sessionMap=(SessionMap<String, Object>)map;  
}
	
}
