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.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import java.util.Collections;
import java.util.Comparator;

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.SupplierDebtReportDAO;
import com.reallinkbilling.db.GetDBConnection;

import com.reallinkbilling.cipher.EncryptionDecryption;

public class GetSupplierDebtReport extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = -4278860738280964117L;
	
	private HttpSession session;
	private Float minAmount;
	private Float maxAmount;
	private String startDate;
	private String endDate;
	private Float allTotalPurchaseAmount;
	private Float allTotalPaymentAmount;
	private Float allTotalDebtAmount;
	private SessionMap<String,Object> sessionMap;  
	
	
	List<SupplierDebtReportDAO> supplierDebtList = new ArrayList<SupplierDebtReportDAO>();
	
	
	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");
	        	
	        	Date today = new Date(); // Fri Jun 17 14:54:28 PDT 2016 
	        	
	        	Calendar cal = Calendar.getInstance(); cal.setTime(today); // don't forget this if date is arbitrary e.g. 01-01-2014
	        	int year = cal.get(Calendar.YEAR);
	        	SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-YYYY");
	        	
	        	setStartDate("01-04-"+(year-1));
	        	setEndDate(sdf.format(today));
	        	
	        	Connection connection = null;
	    		PreparedStatement preparedStatement= null;
	    		ResultSet resultSet = null;
	    	 
	    		String sql = "";
	    		String db = (String) this.getSessionMap().get("db");
	    		
	    		try
	    		{
	    			 Map<String,SupplierDebtReportDAO> supplierInfoMap = new LinkedHashMap<String,SupplierDebtReportDAO>();
		    			
						connection = GetDBConnection.getDBConnection(db);
	    			
		    			sql = "SELECT "
		    					+ "supplier_id,"
		    					+ "suplier_name,"
		    					+ "contact_number,"
		    					+ "supplier_address "
		    					+ "FROM "
		    					+ "shop_supplier_master "
		    					+ "WHERE "
		    					+ "shop_id = ? "
		    					+ "AND "
		    					+ "shop_code = ? "
		    					+ "ORDER BY suplier_name asc";
		    			
		    			preparedStatement = connection.prepareStatement(sql);
		    			preparedStatement.setString(1, shopId);
		    			preparedStatement.setString(2, shopCode);
		    			
		    			resultSet = preparedStatement.executeQuery();
		    			int count = 1;
		    			
		    			while(resultSet.next())
		    			{
		    				SupplierDebtReportDAO dao =  new SupplierDebtReportDAO();
		    				dao.setSrNo(count);
		    				dao.setSupplierName(resultSet.getString("suplier_name"));
		    				dao.setSupplierAddress(resultSet.getString("supplier_address"));

		    				String supplierId = resultSet.getString("supplier_id");
		    				dao.setKey(EncryptionDecryption.encrypt(supplierId));

		    				supplierInfoMap.put(supplierId, dao);
		    				count++;
		    			}
		    			
		    			if(resultSet != null)
		    			{
		    				resultSet.close();
		    				resultSet = null;
		    			}
		    			
		    			if(preparedStatement != null)
		    			{
		    				preparedStatement.close();
		    				preparedStatement = null;
		    			}
		    			
	    			for(String supplierId : supplierInfoMap.keySet())
	    			{
		    				
		    				SupplierDebtReportDAO dao = supplierInfoMap.get(supplierId);

							PreparedStatement getOpeningBalPSMT = null;
		       				ResultSet getOpeningBalRs = null;
		       				String getOpeningBalSQL = "SELECT "
		       						+ "opening_balance_amount "
		       						+ "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())
		       				{
		       					dao.setOpeningBalance(getOpeningBalRs.getFloat(1));
		       				}
		       				else
		       				{
		       					dao.setOpeningBalance(0.0F);
		       				}
		       				
		       				if(getOpeningBalRs != null)
		       				{
		       					getOpeningBalRs.close();
		       					getOpeningBalRs = null;
		       				}
		       				if(getOpeningBalPSMT != null)
		       				{
		       					getOpeningBalPSMT.close();
		       					getOpeningBalPSMT = null;
		       				}
		       				
		       				
		       				float floatTotalPurchaseAmt = 0.0F;
		       				float floatTotalAmountPaid = 0.0F;
		       				float floatTotalBalanceAmt = 0.0F;
		       				
		       				
		       				PreparedStatement getTotalPurchasePSMT = null;
		       				ResultSet getTotalPurchaseRS = null;
		       				
		       				String getTotalPurchaseSQL = "SELECT "
		       						+ "SUM(total_purchase_amount),"
		       						+ "SUM(purchase_amount_paid) "
		       						+ "FROM "
		       						+ "shop_purchase_info_master "
		       						+ "WHERE "
		       						+ "shop_id = ? "
		       						+ "AND "
		       						+ "shop_code = ? "
		       						+ "AND "
		       						+ "supplier_id = ? ";
		       				
		       				getTotalPurchasePSMT = connection.prepareStatement(getTotalPurchaseSQL);
		       				
		       				getTotalPurchasePSMT.setString(1, shopId);
		       				getTotalPurchasePSMT.setString(2, shopCode);
		       				getTotalPurchasePSMT.setString(3, supplierId);
		       				
		       				getTotalPurchaseRS = getTotalPurchasePSMT.executeQuery();
		       				
		       				if(getTotalPurchaseRS.next())
		       				{
		       					floatTotalPurchaseAmt = getTotalPurchaseRS.getFloat(1);
		       					floatTotalAmountPaid = getTotalPurchaseRS.getFloat(2);
		       					
		       				}
		       				
		       				
		       				PreparedStatement getSuppAmtPaidPSMT = null;
		       				ResultSet getSuppAmtPaidRS = null;
		       				String getSuppAmtPaidSQL = "SELECT "
		       						+ "SUM(payment_amount) "
		       						+ "FROM "
		       						+ "shop_supplier_payment_details "
		       						+ "WHERE "
		       						+ "shop_id = ? "
		       						+ "AND "
		       						+ "shop_code = ? "
		       						+ "AND "
		       						+ "supplier_id = ? ";
		       				
		       				getSuppAmtPaidPSMT = connection.prepareStatement(getSuppAmtPaidSQL);
		       				getSuppAmtPaidPSMT.setString(1, shopId);
		       				getSuppAmtPaidPSMT.setString(2, shopCode);
		       				getSuppAmtPaidPSMT.setString(3, supplierId);
		       				
		       				getSuppAmtPaidRS = getSuppAmtPaidPSMT.executeQuery();
		       				
		       				
		       				
		       				if(getSuppAmtPaidRS.next())
		       				{
		       					floatTotalAmountPaid = floatTotalAmountPaid + getSuppAmtPaidRS.getFloat(1);
		       				}
		       				
		       				floatTotalBalanceAmt = floatTotalPurchaseAmt - floatTotalAmountPaid;
		       				
		       				dao.setTotalPurchaseAmount(floatTotalPurchaseAmt);
		       				dao.setTotalDebtAmount(floatTotalBalanceAmt+dao.getOpeningBalance());
		       				dao.setTotalPaymentAmount(floatTotalAmountPaid);
		       				
		       			//	System.out.println(floatTotalBalanceAmt+dao.getOpeningBalance());
		       				if(getSuppAmtPaidRS != null)
		       				{
		       					getSuppAmtPaidRS.close();
		       					getSuppAmtPaidRS = null;
		       				}
		       				
		       				if(getSuppAmtPaidPSMT != null)
		       				{
		       					getSuppAmtPaidPSMT.close();
		       					getSuppAmtPaidPSMT = null;
		       				}
		       				
		       				
		       				
		       				if(getTotalPurchaseRS != null)
		       				{
		       					getTotalPurchaseRS.close();
		       					getTotalPurchaseRS = null;
		       				}
		       				
		       				if(getTotalPurchasePSMT != null)
		       				{
		       					getTotalPurchasePSMT.close();
		       					getTotalPurchasePSMT = null;
		       				}
		       				
		       				supplierInfoMap.put(supplierId, dao);
		       				
	    			}
	    			this.setAllTotalDebtAmount(0.0F);
	    			this.setAllTotalPaymentAmount(0.0F);
	    			this.setAllTotalPurchaseAmount(0.0F);
	    			for(String supplierId : supplierInfoMap.keySet())
	    			{
	    				SupplierDebtReportDAO dao = supplierInfoMap.get(supplierId);
	    				
	    				if(dao.getTotalDebtAmount() > 0)
	    				{
	    					this.setAllTotalDebtAmount(this.getAllTotalDebtAmount()+supplierInfoMap.get(supplierId).getTotalDebtAmount());
		    				this.setAllTotalPurchaseAmount(this.getAllTotalPurchaseAmount()+supplierInfoMap.get(supplierId).getTotalPurchaseAmount());
		    				this.setAllTotalPaymentAmount(this.getAllTotalPaymentAmount()+supplierInfoMap.get(supplierId).getTotalPaymentAmount());
		    				this.getSupplierDebtList().add(supplierInfoMap.get(supplierId));
	    				}
	    				
	    			}
	    			
	    			// Sort supplier report by debit amount in descending order
	    			Collections.sort(supplierDebtList, new Comparator<SupplierDebtReportDAO>() {
	    			    @Override
	    			    public int compare(SupplierDebtReportDAO s1, SupplierDebtReportDAO s2) {
	    			        return Float.compare(
	    			            s2.getTotalDebtAmount(),
	    			            s1.getTotalDebtAmount()
	    			        );
	    			    }
	    			});
	    			
	    			result = "success";
	    		}
	    		catch (SQLException e) 
	    		{
		    			//e.printStackTrace();
		    			result = "error";		    			
		    			addActionError(e.getErrorCode()+" || "+e.getMessage() +" || Something Went Wrong!");
		    			
		    	} catch (IOException e) {
		    		
		    			result = "error";		    			
		    			addActionError(e.getMessage()+" || "+e.getCause()+" || Something Went Wrong!");
		    	}
	    			finally {
		    			if(resultSet != null)
						{
							try {
								resultSet.close();
							} catch (SQLException e) {
								
							}
						}
		    			if(preparedStatement != null)
		    			{
		    				try {preparedStatement.close();} catch (SQLException e) {}
		    			}
		    			if(connection != null)
		    			{
		    				
		    				try {connection.close();}catch (SQLException e) {}
		    			}
		    			
		    		}
	        	
	        }
	        return result;
	}


	@Override
	public void validate() {
		
		if(this.getMinAmount() != null && !this.getMinAmount().equals(""))
		{
				float min = this.getMinAmount();
					if(min<0)
					{
						addActionError("1.Minimum amount cannot be less than 0 !");
					}
				
		}
		
		if(this.getMaxAmount() != null && !this.getMaxAmount().equals(""))
		{
			
				float max =this.getMaxAmount();
				if(max<0)
				{
					addActionError("Maximum amount cannot be less than 0 !");
				}
				
			
		}
		
		
	}
	public HttpSession getSession() {
		return session;
	}


	public void setSession(HttpSession session) {
		this.session = session;
	}


	

	public String getStartDate() {
		return startDate;
	}


	public Float getMinAmount() {
		return minAmount;
	}


	public void setMinAmount(Float minAmount) {
		this.minAmount = minAmount;
	}


	public Float getMaxAmount() {
		return maxAmount;
	}


	public void setMaxAmount(Float maxAmount) {
		this.maxAmount = maxAmount;
	}


	public Float getAllTotalPurchaseAmount() {
		return allTotalPurchaseAmount;
	}


	public void setAllTotalPurchaseAmount(Float allTotalPurchaseAmount) {
		this.allTotalPurchaseAmount = allTotalPurchaseAmount;
	}


	public Float getAllTotalPaymentAmount() {
		return allTotalPaymentAmount;
	}


	public void setAllTotalPaymentAmount(Float allTotalPaymentAmount) {
		this.allTotalPaymentAmount = allTotalPaymentAmount;
	}


	public Float getAllTotalDebtAmount() {
		return allTotalDebtAmount;
	}


	public void setAllTotalDebtAmount(Float allTotalDebtAmount) {
		this.allTotalDebtAmount = allTotalDebtAmount;
	}


	public void setStartDate(String startDate) {
		this.startDate = startDate;
	}


	public String getEndDate() {
		return endDate;
	}


	public void setEndDate(String endDate) {
		this.endDate = endDate;
	}


	


	public static long getSerialversionuid() {
		return serialVersionUID;
	}


	public List<SupplierDebtReportDAO> getSupplierDebtList() {
		return supplierDebtList;
	}


	public void setSupplierDebtList(List<SupplierDebtReportDAO> supplierDebtList) {
		this.supplierDebtList = supplierDebtList;
	}

	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;  
	}
	       
	
}
