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.ParseException;
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.EmployeeAccountDetailsDAO;
import com.reallinkbilling.cipher.EncryptionDecryption;
import com.reallinkbilling.db.GetDBConnection;
import com.reallinkbilling.utils.GetEmpoyeeDetailsById;

public class ViewEmployeeAccountDetails extends ActionSupport implements SessionAware{

	
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -7753667789346409584L;
	private SessionMap<String,Object> sessionMap;
	
	private HttpSession session;
	private String key;
	private String employeeName;
	private String employeeMobile;
	private String defaultValue;
	private String fromDate;
	private String toDate;
	
	
	private Float totalSalaryAmountBeforeDate;
	private Float totalSalaryPaidBeforeDate;
	private Float totalUnpaidSalaryAmountBeforeDate;
	private Float totalSalaryAmountAllTheTime;
	private Float totalSalaryAmountPaidAllTheTime;
	private Float totalUnpaidAmountAllTheTime;
	
	private List<EmployeeAccountDetailsDAO> employeeAccountDetailsList = new ArrayList<EmployeeAccountDetailsDAO>();
	private List<EmployeeAccountDetailsDAO> employeeAccountDetailsSortedList = new ArrayList<EmployeeAccountDetailsDAO>();
	
	private Float employeeBalancePending = 0.0F;
	private Float employeeBalanceAdvance = 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");
		    
	        	String employeeId = EncryptionDecryption.decrypt(getKey());
	        
	        	

	        	Connection connection = null;
	       		PreparedStatement preparedStatement= null;
	       		ResultSet resultSet = null;
	       		
	       		
	       		String sql = "";
	       		String db = (String) this.getSessionMap().get("db");
	       		try {
	       			
	       			
	       			
					connection = GetDBConnection.getDBConnection(db);
	       			
	       			
	       			
	       			PreparedStatement getPendingAdvBalPSMT = null;
       				ResultSet getPendingAdvBalRs = null;
       				
       				String getPendingAdvBalSQL = "SELECT "
       						+ "opening_bal_type,"
       						+ "opening_balance_amount "
       						+ "FROM "
       						+ "shop_employee_salary_details "
       						+ "WHERE "
       						+ "employee_id = ? "
       						+ "AND "
       						+ "shop_id = ? "
       						+ "AND "
       						+ "shop_code = ? ";
       				getPendingAdvBalPSMT = connection.prepareStatement(getPendingAdvBalSQL);
       				
       				getPendingAdvBalPSMT.setString(1, employeeId);
       				getPendingAdvBalPSMT.setString(2, shopId);
       				getPendingAdvBalPSMT.setString(3, shopCode);
       				
       				
       				getPendingAdvBalRs = getPendingAdvBalPSMT.executeQuery();
       				
       				if(getPendingAdvBalRs.next())
       				{
       					if(getPendingAdvBalRs.getString(1).equals("ADVANCE"))
       					{
       						this.setEmployeeBalanceAdvance(getPendingAdvBalRs.getFloat(2));
       						this.setEmployeeBalancePending(0.0F);
       					}
       					else
       					{
       						this.setEmployeeBalanceAdvance(0.0F);
       						this.setEmployeeBalancePending(getPendingAdvBalRs.getFloat(2));
       					}
       					
       				
       				}
       				else
       				{
       					this.setEmployeeBalanceAdvance(0.0F);
   						this.setEmployeeBalancePending(0.0F);
       				}
       				if(getPendingAdvBalRs != null)
       				{
       					getPendingAdvBalRs.close();
       					getPendingAdvBalRs = null;
       				}
       				if(getPendingAdvBalPSMT != null)
       				{
       					getPendingAdvBalPSMT.close();
       					getPendingAdvBalPSMT = null;
       				}
       				
	       			
	       			
	       			sql = "SELECT "
	       					+ "SUM(payment_amount) "
	       					+ "FROM "
	       					+ "shop_employee_payment_details "
	       					+ "WHERE "
	       					+ "employee_id = ? "
	       					+ "and "
	       					+ "payment_date < STR_TO_DATE(?, '%d-%m-%Y') "
	       					+ "AND "
	       					+ "shop_id = ? "
	       					+ "AND "
	       					+ "shop_code = ? ";
	       			
	       			preparedStatement = connection.prepareStatement(sql);
	       			preparedStatement.setString(1, employeeId);
	       			preparedStatement.setString(2, this.getFromDate());
	       			preparedStatement.setString(3, shopId);
	       			preparedStatement.setString(4, shopCode);
	       			
	       			resultSet = preparedStatement.executeQuery();
	       			
	       			if(resultSet.next())
	       			{
	       				if(resultSet.getString(1) != null)
	       				{
	       					this.setTotalSalaryPaidBeforeDate(resultSet.getFloat(1));
	       				}
	       				else
	       				{
	       					this.setTotalSalaryPaidBeforeDate(0.0F);
	       				}
	       			}
	       			else
	       			{
	       				this.setTotalSalaryPaidBeforeDate(0.0F);
	       			}
	       			
	       			if(resultSet != null)
	       			{
	       				resultSet.close();
	       				resultSet = null;
	       			}
	       			if(preparedStatement != null)
	       			{
	       				preparedStatement.close();
	       				preparedStatement = null;
	       			}
	       			
	       			
	       			float floatSalaryAmt = 0.0F;
       				
       				float totalSalaryAmountPaid = this.getTotalSalaryPaidBeforeDate();
       				float totalSalaryAmountBeforeDate = 0.0F;
       				
       				
       				sql = "SELECT "
       						+ "total_calculated_salary_amount "
       						+ "FROM "
       						+ "shop_employee_salary_calculation_details "
       						+ "WHERE "
	       					+ "employee_id = ? "
	       					+ "AND "
	       					+ "salary_to_date < STR_TO_DATE(?, '%d-%m-%Y') "
		       				+ "AND "
		   					+ "shop_id = ? "
		   					+ "AND "
		   					+ "shop_code = ? ";
	       			
	       			preparedStatement = connection.prepareStatement(sql);
	       			preparedStatement.setString(1, employeeId);
	       			preparedStatement.setString(2, this.getFromDate());
	       			preparedStatement.setString(3, shopId);
	       			preparedStatement.setString(4, shopCode);
	       			
	       			resultSet = preparedStatement.executeQuery();
	       			
	       			while(resultSet.next())
	       			{
	       				
	       				try
	       				{
	       					floatSalaryAmt = resultSet.getFloat(1);
	       					totalSalaryAmountBeforeDate = totalSalaryAmountBeforeDate + floatSalaryAmt;
	       					
	       				}
	       				catch(Exception e)
	       				{
	       					
	       					addActionError("One Of the Entry invoice could not be processed!");
	       				}
	       				
	       			}
	       			if(!hasActionErrors())
	       			{
	       				this.setTotalSalaryAmountBeforeDate(totalSalaryAmountBeforeDate);
	       			
	       				if(resultSet != null)
	       				{
	       					resultSet.close();
	       					resultSet = null;
	       				}
	       				
	       				if(preparedStatement != null)
	       				{
	       					preparedStatement.close();
	       					preparedStatement = null;
	       				}
	       				
	       				
	       				sql = "SELECT "
	       						+ "entry_id,"
	       						+ "employee_id,"
	       						+ "DATE_FORMAT(payment_date, '%d/%m/%Y'),"
	       					    + "payment_amount,"
	       			            + "payment_details "
	       						+ "FROM "
	       						+ "shop_employee_payment_details "
	       						+ "WHERE "
	       						+ "employee_id = ?"
			       				+ "AND "
		       					+ "(payment_date BETWEEN "
			   					+ "STR_TO_DATE(?, '%d-%m-%Y') "
			   					+ "AND "
			   					+ "STR_TO_DATE(?, '%d-%m-%Y')) "
			   					+ "AND "
			   					+ "shop_id = ? "
			   					+ "AND "
			   					+ "shop_code = ? ";
	       				
	       				preparedStatement = connection.prepareStatement(sql);
	       				
	       				preparedStatement.setString(1, employeeId);
	       				preparedStatement.setString(2, this.getFromDate());
	       				preparedStatement.setString(3, this.getToDate());
	       				preparedStatement.setString(4, shopId);
		       			preparedStatement.setString(5, shopCode);
	       			
	       				resultSet = preparedStatement.executeQuery();
	       				
	       				while(resultSet.next())
	       				{
	       					Date date1=new SimpleDateFormat("dd/MM/yyyy").parse(resultSet.getString(3)); 
	    	       			
	       				
	       					EmployeeAccountDetailsDAO dao = new EmployeeAccountDetailsDAO();
	       					dao.setEntryId(EncryptionDecryption.encrypt(resultSet.getString(1)));
	       					dao.setDate(resultSet.getString(3));
	       					dao.setEntryDate(date1);
	       					dao.setSalaryAmount(0.0F);
	       					dao.setAmount(resultSet.getFloat(4));	       					
	       					dao.setAmountType("PAYMENT");
	       					dao.setDueAmount(0.0F);
	       				    dao.setComments(resultSet.getString(5));
	       					dao.setSalaryFromDate("-");
	       					dao.setSalaryToDate("-");
	       					this.getEmployeeAccountDetailsList().add(dao);
	       					
	       				}
	       				
	       				if(resultSet != null)
	       				{
	       					resultSet.close();
	       					resultSet = null;
	       				}
	       				
	       				if(preparedStatement != null)
	       				{
	       					preparedStatement.close();
	       					preparedStatement = null;
	       				}
	       				
	       				sql = "SELECT "
	       						+ "entry_id,"
	       						+ "DATE_FORMAT(salary_from_date,'%d/%m/%Y'),"
	       						+ "DATE_FORMAT(salary_to_date,'%d/%m/%Y'),"
	       						+ "total_calculated_salary_amount,"
	       						+ "comments "
	       						+ "FROM "
	       						+ "shop_employee_salary_calculation_details "
	       						+ "WHERE "
	       						+ "employee_id = ? "
	       						+ "AND "
		       					+ "(salary_to_date BETWEEN "
			   					+ "STR_TO_DATE(?, '%d-%m-%Y') "
			   					+ "AND "
			   					+ "STR_TO_DATE(?, '%d-%m-%Y')) "
			   					+ "AND "
			   					+ "shop_id = ? "
			   					+ "AND "
			   					+ "shop_code = ? ";
	       				
	       				preparedStatement = connection.prepareStatement(sql);
	       				
	       				preparedStatement.setString(1, employeeId);
	       				preparedStatement.setString(2, this.getFromDate());
	       				preparedStatement.setString(3, this.getToDate());
	       				preparedStatement.setString(4, shopId);
	       				preparedStatement.setString(5, shopCode);
	       				
	       				
	       				resultSet = preparedStatement.executeQuery();
	       				while(resultSet.next())
	       				{
	       					Date date1=new SimpleDateFormat("dd/MM/yyyy").parse(resultSet.getString(3)); 
	    	       			
		       				
	       					EmployeeAccountDetailsDAO dao = new EmployeeAccountDetailsDAO();
	       					dao.setEntryId(EncryptionDecryption.encrypt(resultSet.getString(1)));
	       					dao.setDate(resultSet.getString(2) + " - TO -"+resultSet.getString(3));
	       					dao.setSalaryFromDate(resultSet.getString(2));
	       					dao.setSalaryToDate(resultSet.getString(3));
	       					dao.setEntryDate(date1);
	       					dao.setSalaryAmount(resultSet.getFloat(4));
	       					dao.setAmount(0.0F);	       					
	       					dao.setAmountType("SALARY");
	       					dao.setComments(resultSet.getString(5));
	       					dao.setDueAmount(0.0F);
	       					this.getEmployeeAccountDetailsList().add(dao);
	       					
	       				}
	       				
	       				
	       		
	       				if(!hasActionErrors())
	       				{
	       					

   						 
   						 Collections.sort(this.getEmployeeAccountDetailsList(), new Comparator<EmployeeAccountDetailsDAO>() {
   							  public int compare(EmployeeAccountDetailsDAO o1, EmployeeAccountDetailsDAO o2) {
   							      if (o1.getEntryDate() == null || o2.getEntryDate() == null)
   							        return 0;
   							      return o1.getEntryDate().compareTo(o2.getEntryDate());
   							  }
   							});
   						 
   						
   						 float dueAmountBeforeDates = this.getTotalSalaryAmountBeforeDate() - this.getTotalSalaryPaidBeforeDate();
   						 float currentSalaryAmtDue = 0.0F;
   					
   						this.setTotalUnpaidSalaryAmountBeforeDate(dueAmountBeforeDates);
						if(this.getEmployeeBalanceAdvance() > 0)
						{
							currentSalaryAmtDue = dueAmountBeforeDates - this.getEmployeeBalanceAdvance();
						} 
						else if(this.getEmployeeBalancePending() > 0 )
						{
							currentSalaryAmtDue = dueAmountBeforeDates + this.getEmployeeBalancePending(); 
						}
						 else
						 {
							currentSalaryAmtDue = dueAmountBeforeDates;
						 }
	   						      
   						for(EmployeeAccountDetailsDAO dao : this.getEmployeeAccountDetailsList())
	       				{
   							if(dao.getAmountType().equals("SALARY"))
   							{
   								dao.setDueAmount(currentSalaryAmtDue + dao.getSalaryAmount());
   								
   								currentSalaryAmtDue = currentSalaryAmtDue + dao.getSalaryAmount();
   							}
   							else if(dao.getAmountType().equals("PAYMENT"))
   							{
   								dao.setDueAmount(currentSalaryAmtDue - dao.getAmount());
   								currentSalaryAmtDue = currentSalaryAmtDue -  dao.getAmount();
   							}
	       				}
   						
   							
	       					result = SUCCESS;
	       				}
	       				else
	       				{
	       					result = ERROR;
	       				}
       				
	       			}
	       			else
	       			{
	       				result = ERROR;
	       			}
       				
       				
	       			
	       			HashMap<String, String> employeeDetails = GetEmpoyeeDetailsById.getEmployeeDetails(employeeId, this.getSessionMap(), connection);
       				
	       			
	       			this.setEmployeeName(employeeDetails.get("EMPLOYEENAMEENG"));
	       			this.setEmployeeMobile(employeeDetails.get("EMPLOYEEMOBNUM"));
	       			
	       			
	       		}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 getEmployeeName() {
	return employeeName;
}



public void setEmployeeName(String employeeName) {
	this.employeeName = employeeName;
}



public String getEmployeeMobile() {
	return employeeMobile;
}



public void setEmployeeMobile(String employeeMobile) {
	this.employeeMobile = employeeMobile;
}



public Float getTotalSalaryAmountBeforeDate() {
	return totalSalaryAmountBeforeDate;
}



public void setTotalSalaryAmountBeforeDate(Float totalSalaryAmountBeforeDate) {
	this.totalSalaryAmountBeforeDate = totalSalaryAmountBeforeDate;
}



public Float getTotalSalaryPaidBeforeDate() {
	return totalSalaryPaidBeforeDate;
}



public void setTotalSalaryPaidBeforeDate(Float totalSalaryPaidBeforeDate) {
	this.totalSalaryPaidBeforeDate = totalSalaryPaidBeforeDate;
}



public Float getTotalUnpaidSalaryAmountBeforeDate() {
	return totalUnpaidSalaryAmountBeforeDate;
}



public void setTotalUnpaidSalaryAmountBeforeDate(Float totalUnpaidSalaryAmountBeforeDate) {
	this.totalUnpaidSalaryAmountBeforeDate = totalUnpaidSalaryAmountBeforeDate;
}



public Float getTotalSalaryAmountAllTheTime() {
	return totalSalaryAmountAllTheTime;
}



public void setTotalSalaryAmountAllTheTime(Float totalSalaryAmountAllTheTime) {
	this.totalSalaryAmountAllTheTime = totalSalaryAmountAllTheTime;
}



public Float getTotalSalaryAmountPaidAllTheTime() {
	return totalSalaryAmountPaidAllTheTime;
}



public void setTotalSalaryAmountPaidAllTheTime(Float totalSalaryAmountPaidAllTheTime) {
	this.totalSalaryAmountPaidAllTheTime = totalSalaryAmountPaidAllTheTime;
}



public Float getTotalUnpaidAmountAllTheTime() {
	return totalUnpaidAmountAllTheTime;
}



public void setTotalUnpaidAmountAllTheTime(Float totalUnpaidAmountAllTheTime) {
	this.totalUnpaidAmountAllTheTime = totalUnpaidAmountAllTheTime;
}



public List<EmployeeAccountDetailsDAO> getEmployeeAccountDetailsList() {
	return employeeAccountDetailsList;
}



public void setEmployeeAccountDetailsList(List<EmployeeAccountDetailsDAO> employeeAccountDetailsList) {
	this.employeeAccountDetailsList = employeeAccountDetailsList;
}



public List<EmployeeAccountDetailsDAO> getEmployeeAccountDetailsSortedList() {
	return employeeAccountDetailsSortedList;
}



public void setEmployeeAccountDetailsSortedList(List<EmployeeAccountDetailsDAO> employeeAccountDetailsSortedList) {
	this.employeeAccountDetailsSortedList = employeeAccountDetailsSortedList;
}



public Float getEmployeeBalancePending() {
	return employeeBalancePending;
}



public void setEmployeeBalancePending(Float employeeBalancePending) {
	this.employeeBalancePending = employeeBalancePending;
}



public Float getEmployeeBalanceAdvance() {
	return employeeBalanceAdvance;
}



public void setEmployeeBalanceAdvance(Float employeeBalanceAdvance) {
	this.employeeBalanceAdvance = employeeBalanceAdvance;
}



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 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 void setSession(Map<String, Object> map) {  
    sessionMap=(SessionMap<String, Object>)map;  
}


public SessionMap<String, Object> getSessionMap() {
	return sessionMap;
}

public void setSessionMap(SessionMap<String, Object> sessionMap) {
	this.sessionMap = sessionMap;
}
	
}

