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.Collections;
import java.util.Date;
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.CustomerCreditReportDAO;
import com.reallinkbilling.DAO.CustomerInfoDAO;
import com.reallinkbilling.cipher.EncryptionDecryption;
import com.reallinkbilling.db.GetDBConnection;

public class GetCustomerCreditReport extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = -3363815906834114723L;
	
	private HttpSession session;
	private String minAmount;
	private String maxAmount;
	private String startDate;
	private String endDate;
	private List<String> downloadTypeList = new ArrayList<String>();
	private String downloadType;
	
	
	private List<CustomerCreditReportDAO> custCreditList = new ArrayList<CustomerCreditReportDAO>();	
	private float totolReportBillAmount = 0.0F;
	private float totalReportPaymentAmount = 0.0F;
	private float totalReportCreditAmount = 0.0F;
	
	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{
	        	
	        	downloadTypeList.add("pdf");
	        	downloadTypeList.add("xl");
	        	
	        	
	        	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,CustomerInfoDAO> customerInfoMap = new LinkedHashMap<String,CustomerInfoDAO>();
	    			
					connection = GetDBConnection.getDBConnection(db);
	    			
	    			sql = "SELECT "
	    					+ "customer_id,"
	    					+ "customer_name,"
	    					+ "mobile_number,"
	    					+ "billing_address,"
	    					+ "credit_limit "
	    					+ "FROM "
	    					+ "shop_customer_master "
	    					+ "WHERE "
	    					+ "shop_id = ? "
	    					+ "AND "
	    					+ "shop_code = ? "
	    					+ "ORDER BY customer_name ASC";
	    			
	    			preparedStatement = connection.prepareStatement(sql);
	    			preparedStatement.setString(1, shopId);
	    			preparedStatement.setString(2, shopCode);
	    			
	    			resultSet = preparedStatement.executeQuery();
	    			int count = 1;
	    		
	    		
	    			while(resultSet.next())
	    			{
	    				CustomerInfoDAO customerInfoDAO = new CustomerInfoDAO();
	    				
	    				customerInfoDAO.setCustomerId(resultSet.getString(1));
	    				customerInfoDAO.setCustName(resultSet.getString(2));
	    				customerInfoDAO.setMobileNumber(resultSet.getString(3));
	    				customerInfoDAO.setBillingaddress(resultSet.getString(4));
	    				customerInfoDAO.setCreditlimit(resultSet.getString(5));
	    				
	    				customerInfoMap.put(resultSet.getString(1),customerInfoDAO);	    				
	    			}
	    			
	    			if(resultSet != null)
	    			{
	    				resultSet.close();
	    				resultSet = null;
	    			}
	    			
	    			if(preparedStatement != null)
	    			{
	    				preparedStatement.close();
	    				preparedStatement = null;
	    			}
	    			
	    			Map<String,CustomerCreditReportDAO> custCreditReportMap = new LinkedHashMap<String,CustomerCreditReportDAO>() ;
	    			
	    			for(String custId: customerInfoMap.keySet())
	    			{
	    				float opeingBalFloat = 0.0F;
       					PreparedStatement getOpeningBalPSMT = null;
           				ResultSet getOpeningBalRs = null;
           				String getOpeningBalSQL = "SELECT "
           						+ "opening_balance,"
           						+ "DATE_FORMAT(opening_bal_update_date,'%d/%m/%Y'),"
           						+ "updated_by "
           						+ "FROM "
           						+ "shop_cust_close_open_bal_master "
           						+ "WHERE "
           						+ "shop_id = ? "
           						+ "AND "
           						+ "shop_code = ? "
           						+ "AND "
           						+ "customer_id = ?";
           				getOpeningBalPSMT = connection.prepareStatement(getOpeningBalSQL);
           				
           				getOpeningBalPSMT.setString(1, shopId);
           				getOpeningBalPSMT.setString(2, shopCode);
           				getOpeningBalPSMT.setString(3, custId);
           				
           				getOpeningBalRs = getOpeningBalPSMT.executeQuery();
           				
           				if(getOpeningBalRs.next())
           				{
       					
           					opeingBalFloat = getOpeningBalRs.getFloat(1);
       					
           				}
           				
           		
           				if(getOpeningBalRs != null)
           				{
           					getOpeningBalRs.close();
           					getOpeningBalRs = null;
           				}
           				if(getOpeningBalPSMT != null)
           				{
           					getOpeningBalPSMT.close();
           					getOpeningBalPSMT = null;
           				}
       					
       					
           				
	    				CustomerInfoDAO customerInfoDAO = customerInfoMap.get(custId);
	    				CustomerCreditReportDAO creditReportDAO = new CustomerCreditReportDAO();
	    				creditReportDAO.setCustometName(customerInfoDAO.getCustName());
	    				
	    				creditReportDAO.setKey(EncryptionDecryption.encrypt(custId));
	    				
	    				creditReportDAO.setCustomerMobile(customerInfoDAO.getMobileNumber());
	    				creditReportDAO.setCustomerAddress(customerInfoDAO.getBillingaddress());
	    				customerInfoDAO.setOpeningBalance(opeingBalFloat);
	    				creditReportDAO.setOpeningBalance(opeingBalFloat);
	    				creditReportDAO.setSrNo(count);
	    				count ++;
	    				
	    				float custTotalPaymentAmount = 0.0F;
	    				
	    				
	    				PreparedStatement getCustPaymentDetailsPSMT = null;
	    				ResultSet getCustPaymnetDetailsRS = null;
	    				
	    				String getCustPaymentDetailsSQL = "SELECT "
		    						+ "SUM(payment_amount) "
			       					+ "FROM "
			       					+ "shop_customer_payment_master "
			       					+ "WHERE "
			       					+ "shop_id = ? "
			       					+ "AND "
			       					+ "shop_code = ? "
			       					+ "AND "
			       					+ "customer_id = ? "
			       					+ "AND "
			       					+ "(entry_date "
			       					+ "BETWEEN "
			       					+ "STR_TO_DATE(?, '%d-%m-%Y') "
			       					+ "AND "
			       					+ "STR_TO_DATE(?, '%d-%m-%Y')) ";
	    		
	    			getCustPaymentDetailsPSMT = connection.prepareStatement(getCustPaymentDetailsSQL);
	    			
	    			getCustPaymentDetailsPSMT.setString(1, shopId);
	    			getCustPaymentDetailsPSMT.setString(2, shopCode);
	    			getCustPaymentDetailsPSMT.setString(3, custId);
	    			getCustPaymentDetailsPSMT.setString(4, this.getStartDate());
	    			getCustPaymentDetailsPSMT.setString(5, this.getEndDate());
	    			
	    			
	    			getCustPaymnetDetailsRS = getCustPaymentDetailsPSMT.executeQuery();
	    			
	    			if(getCustPaymnetDetailsRS.next())
	    			{
	    				if(getCustPaymnetDetailsRS.getString(1) != null)
	    				{
	    					custTotalPaymentAmount = getCustPaymnetDetailsRS.getFloat(1);
	    				}
	    			}
	    			
	    			creditReportDAO.setTotalPaymentAmount(""+custTotalPaymentAmount);
	    			
	    			custCreditReportMap.put(custId, creditReportDAO);
	    			
	    			
			    			if(getCustPaymnetDetailsRS != null)
			    			{
			    				getCustPaymnetDetailsRS.close();
			    				getCustPaymnetDetailsRS = null;
			    			}
			    			
			    			if(getCustPaymentDetailsPSMT != null)
			    			{
			    				getCustPaymentDetailsPSMT.close();
			    				getCustPaymentDetailsPSMT = null;
			    			}
			    			
			    			//System.out.println(creditReportDAO.getCustometName()+"::"+ creditReportDAO.getTotalPaymentAmount());
	    			}
	    			
	    			for(String custId: custCreditReportMap.keySet())	    				
	    			{
	    				CustomerCreditReportDAO creditReportDAO = custCreditReportMap.get(custId);
	    				
	    				float custTotalPaymentAmount = Float.parseFloat(creditReportDAO.getTotalPaymentAmount());
				    			float custTotalCreditAmount = 0.0F;
			    				float custTotalBillAmount = 0.0F;
    				
				    			float floatBillAmt = 0.0F;
			       				float floatBillAmtReceived = 0.0F;
			       				float floatBillBalanceAmt = 0.0F;
			       				
			       				float totalAmountRecieved = 0.0F;
			       				float totalBalanceAmt = 0.0F;			       			
			       				float totalBillAmount = 0.0F;
			       				
			       				
			       				PreparedStatement getBillAmountPSMT = null;
			       				ResultSet getBillAmountRS = null;
			       				String getBillAmountSQL= "";
			       				
			       				getBillAmountSQL = "SELECT "	           					
			           					+ "bill_total_amount,"
			           					+ "bill_amount_received,"
			           					+ "bill_amount_retrurn "
			           					+ "FROM "
			           					+ "shop_bill_info_master "
			           					+ "WHERE "
			           					+ "shop_id = ? "
			           					+ "AND "
			           					+ "shop_code = ? "
			           					+ "AND "		           					
			           					+ "customer_id = ? "
			           					+ "AND "
			           					+ "(bill_date "
			           					+ "BETWEEN "
			           					+ "STR_TO_DATE(?, '%d-%m-%Y') "
				       					+ "AND "
				       					+ "STR_TO_DATE(?, '%d-%m-%Y')) "
				       					+ "AND "
				       					+ "(bill_source IN ('ORIGINAL','BARCODEBILL'))";
			       				
			       				
			       				getBillAmountPSMT = connection.prepareStatement(getBillAmountSQL);
			       				getBillAmountPSMT.setString(1, shopId);
			       				getBillAmountPSMT.setString(2, shopCode);
			       				getBillAmountPSMT.setString(3, custId);
			       				getBillAmountPSMT.setString(4, this.getStartDate());
			       				getBillAmountPSMT.setString(5, this.getEndDate());
			       				
			       				
			       				getBillAmountRS = getBillAmountPSMT.executeQuery();
			       				
			       				while(getBillAmountRS.next())
			       				{
			       					
			       					
			       					try
			       					{
			       						if(getBillAmountRS.getString(2) != null)
				       						{
					       						floatBillAmt = getBillAmountRS.getFloat(1);
					       						floatBillAmtReceived = getBillAmountRS.getFloat(2);
					       						floatBillBalanceAmt = floatBillAmt - floatBillAmtReceived;
					       						totalBillAmount = totalBillAmount + floatBillAmt;
						           				
						           				if(floatBillBalanceAmt > 0)
						           				{
						           					totalAmountRecieved = totalAmountRecieved + floatBillAmtReceived;
						           					totalBalanceAmt = totalBalanceAmt+floatBillBalanceAmt;
						           					
						           					
						           				}
						           				else if(floatBillBalanceAmt <= 0)
						           				{
						           					totalAmountRecieved = totalAmountRecieved + floatBillAmt;
						           				}
				       						}
					           				
					           				
					           				
				       					}catch(NumberFormatException nfe)
				       					{
				       						nfe.printStackTrace();
				       						result = "error";
				       						addActionError("Error while fomatting ! "
				       								+ "While calculating each bill datails");
				       						
				       					}
			       				}
			       				
			       				
			       				
			       				
			       				PreparedStatement getChallanAmountPSMT = null;
			       				ResultSet getChallanAmountRS = null;
			       				String getChallanAmountSQL= "";
			       				
			       				getChallanAmountSQL = "SELECT "	           					
			           					+ "challan_total_amount,"
			           					+ "challan_amount_received,"
			           					+ "challan_amount_retrurn "
			           					+ "FROM "
			           					+ "shop_challan_info_master "
			           					+ "WHERE "
			           					+ "shop_id = ? "
			           					+ "AND "
			           					+ "shop_code = ? "
			           					+ "AND "		           					
			           					+ "customer_id = ? "
			           					+ "AND "
			           					+ "(challan_date "
			           					+ "BETWEEN "
			           					+ "STR_TO_DATE(?, '%d-%m-%Y') "
				       					+ "AND "
				       					+ "STR_TO_DATE(?, '%d-%m-%Y')) ";
			       				
			       				
			       				getChallanAmountPSMT = connection.prepareStatement(getChallanAmountSQL);
			       				getChallanAmountPSMT.setString(1, shopId);
			       				getChallanAmountPSMT.setString(2, shopCode);
			       				getChallanAmountPSMT.setString(3, custId);
			       				getChallanAmountPSMT.setString(4, this.getStartDate());
			       				getChallanAmountPSMT.setString(5, this.getEndDate());
			       				
			       				
			       				getChallanAmountRS = getChallanAmountPSMT.executeQuery();
			       				
			       				while(getChallanAmountRS.next())
			       				{
			       					
			       					
			       					try
			       					{
			       						if(getChallanAmountRS.getString(2) != null)
				       						{
					       						floatBillAmt = getChallanAmountRS.getFloat(1);
					       						floatBillAmtReceived = getChallanAmountRS.getFloat(2);
					       						floatBillBalanceAmt = floatBillAmt - floatBillAmtReceived;
					       						totalBillAmount = totalBillAmount + floatBillAmt;
						           				
						           				if(floatBillBalanceAmt > 0)
						           				{
						           					totalAmountRecieved = totalAmountRecieved + floatBillAmtReceived;
						           					totalBalanceAmt = totalBalanceAmt+floatBillBalanceAmt;
						           					
						           					
						           				}
						           				else if(floatBillBalanceAmt <= 0)
						           				{
						           					totalAmountRecieved = totalAmountRecieved + floatBillAmt;
						           				}
				       						}
					           				
					           				
					           				
				       					}catch(NumberFormatException nfe)
				       					{
				       						nfe.printStackTrace();
				       						result = "error";
				       						addActionError("Error while fomatting ! "
				       								+ "While calculating each bill datails");
				       						
				       					}
			       				}
			       				
			       				
			       				totalBalanceAmt = totalBalanceAmt + creditReportDAO.getOpeningBalance();
			       				
			       				
			       				
			       				custTotalPaymentAmount = custTotalPaymentAmount + totalAmountRecieved;
			       				custTotalCreditAmount = (creditReportDAO.getOpeningBalance()+totalBillAmount) -  custTotalPaymentAmount;
				       			custTotalBillAmount = totalBillAmount;
			       				
			       			
			       				
			       				creditReportDAO.setTotalBillAmount(""+custTotalBillAmount);
			       				creditReportDAO.setTotalCreditAmount(custTotalCreditAmount);
			       				creditReportDAO.setTotalPaymentAmount(""+custTotalPaymentAmount);
			       				
			       				creditReportDAO.setSmsData(EncryptionDecryption.encrypt(creditReportDAO.getCustomerMobile()
			       						+"-"+creditReportDAO.getTotalCreditAmount()));
			       				
			       				
			       				if(!this.getMinAmount().equals("") && !this.getMaxAmount().equals(""))
			       				{
			       					float minAmt = Float.parseFloat(this.getMinAmount());
			       					float maxAmt = Float.parseFloat(this.getMaxAmount());
			       					
			       					if( custTotalCreditAmount > 0 && custTotalCreditAmount >=minAmt && custTotalCreditAmount <=maxAmt)
			       					{
			       						this.getCustCreditList().add(creditReportDAO);
			       					}
			       					
			       				}
			       				else if(!this.getMinAmount().equals("") && this.getMaxAmount().equals(""))
			       				{
			       					float minAmt = Float.parseFloat(this.getMinAmount());
			       				
			       					
			       					if(custTotalCreditAmount > 0 && custTotalCreditAmount >=minAmt)
			       					{
			       						this.getCustCreditList().add(creditReportDAO);
			       					}
			       				}
			       				else if(this.getMinAmount().equals("") && !this.getMaxAmount().equals(""))
			       				{
			       					float maxAmt = Float.parseFloat(this.getMaxAmount());
			       				
			       					
			       					if(custTotalCreditAmount > 0 && custTotalCreditAmount <=maxAmt)
			       					{
			       						this.getCustCreditList().add(creditReportDAO);
			       					}
			       				}
			       				else
			       				{
			       					if(custTotalCreditAmount > 0) {
			       						
			       						this.getCustCreditList().add(creditReportDAO);
			       					}
			       					
			       				}
			       				
			       				
			       				
			       				
			       			   
			       				
			       				if(getBillAmountRS != null)
			       				{
			       					getBillAmountRS.close();
			       					getBillAmountRS = null;
			       				}
			       				if(getBillAmountPSMT != null)
			       				{
			       					getBillAmountPSMT.close();
			       					getBillAmountPSMT = null;
			       				}
			       				
			       				
				    			
	    			}	
	    			
	    			// Sort by outstanding amount: highest to lowest
	    			Collections.sort(custCreditList,
	    			    new java.util.Comparator<CustomerCreditReportDAO>() {
	    			        @Override
	    			        public int compare(CustomerCreditReportDAO c1,
	    			                           CustomerCreditReportDAO c2) {
	    			            return Float.compare(
	    			                c2.getTotalCreditAmount(),
	    			                c1.getTotalCreditAmount()
	    			            );
	    			        }
	    			    }
	    			);

	    			// Calculate report totals
	    			int listSize = custCreditList.size();

	    			for (int s = 0; s < listSize; s++) {

	    			    CustomerCreditReportDAO dao = custCreditList.get(s);

	    			    totolReportBillAmount += Float.parseFloat(dao.getTotalBillAmount());

	    			    totalReportPaymentAmount += Float.parseFloat(dao.getTotalPaymentAmount());

	    			    totalReportCreditAmount += dao.getTotalCreditAmount();
	    			}
	    			
	    			this.setTotolReportBillAmount(totolReportBillAmount);
	    			this.setTotalReportPaymentAmount(totalReportPaymentAmount);
	    			this.setTotalReportCreditAmount(totalReportCreditAmount);
	    			
	    			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(""))
		{
			if(this.getMinAmount().length()<9)
			{
				try
				{
					float min = Float.parseFloat(this.getMinAmount());
					if(min<0)
					{
						addActionError("1.Minimum amount cannot be less than 0 !");
					}
					
				}catch(NumberFormatException nfe)
				{
					addActionError("2.Minimum amount is not correct!");
					
				}
			}else
			{
				addActionError("Minimumm amount is not correct!");
			}
		}
		
		if(this.getMaxAmount() != null && !this.getMaxAmount().equals(""))
		{
			if(this.getMaxAmount().length()<9)
			{
			try
			{
				
				float max = Float.parseFloat(this.getMaxAmount());
				if(max<0)
				{
					addActionError("Maximum amount cannot be less than 0 !");
				}
				
			}catch(NumberFormatException nfe)
			{
				addActionError("Maximum amount is not correct!");
				
			}
			}else
			{
				addActionError("Maximum amount is not correct!");
			}
		}
		
		
	}
	public HttpSession getSession() {
		return session;
	}


	public void setSession(HttpSession session) {
		this.session = session;
	}


	public String getMinAmount() {
		return minAmount;
	}


	public void setMinAmount(String minAmount) {
		this.minAmount = minAmount;
	}


	public String getMaxAmount() {
		return maxAmount;
	}


	public void setMaxAmount(String maxAmount) {
		this.maxAmount = maxAmount;
	}


	public List<CustomerCreditReportDAO> getCustCreditList() {
		return custCreditList;
	}


	public void setCustCreditList(List<CustomerCreditReportDAO> custCreditList) {
		this.custCreditList = custCreditList;
	}


	public String getStartDate() {
		return startDate;
	}


	public void setStartDate(String startDate) {
		this.startDate = startDate;
	}


	public String getEndDate() {
		return endDate;
	}


	public void setEndDate(String endDate) {
		this.endDate = endDate;
	}


	public float getTotolReportBillAmount() {
		return totolReportBillAmount;
	}


	public void setTotolReportBillAmount(float totolReportBillAmount) {
		this.totolReportBillAmount = totolReportBillAmount;
	}


	public float getTotalReportPaymentAmount() {
		return totalReportPaymentAmount;
	}


	public void setTotalReportPaymentAmount(float totalReportPaymentAmount) {
		this.totalReportPaymentAmount = totalReportPaymentAmount;
	}


	public float getTotalReportCreditAmount() {
		return totalReportCreditAmount;
	}


	public void setTotalReportCreditAmount(float totalReportCreditAmount) {
		this.totalReportCreditAmount = totalReportCreditAmount;
	}
	
	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;  
	}


	public List<String> getDownloadTypeList() {
		return downloadTypeList;
	}


	public void setDownloadTypeList(List<String> downloadTypeList) {
		this.downloadTypeList = downloadTypeList;
	}


	public String getDownloadType() {
		return downloadType;
	}


	public void setDownloadType(String downloadType) {
		this.downloadType = downloadType;
	}
	        
	
}
