package com.reallinkbilling.ajax;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
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.CustomerInfoDAO;
import com.reallinkbilling.cipher.EncryptionDecryption;
import com.reallinkbilling.db.GetDBConnection;

public class SearchCustomerInfo extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = -3828289564696834998L;
	
	private String key;
	private String searchOption;
	private SessionMap<String,Object> sessionMap;  
	private HttpSession session;	
	
	private List<CustomerInfoDAO> custInfoList = new ArrayList<CustomerInfoDAO>();
	
	public String execute() throws Exception {

	 	String result = "login";
		session=ServletActionContext.getRequest().getSession(false);  
        
		if(session==null || session.getAttribute("login")==null){ 
        	addActionMessage("Please Login First !!!");
            return "login";  
        }  
        else
        {
        	
        	
        	String shopId = (String) this.getSessionMap().get("shopId");
    		String shopCode = (String) this.getSessionMap().get("shopCode");
         	
		
        	//System.out.println("hello");
        	
        	Connection connection = null;
       		PreparedStatement preparedStatement= null;
       		ResultSet resultSet = null;
       	
       		String actualKey = this.getKey().trim();
       		
       		String sql = "";
       		if(this.getSearchOption().equals("BYNAME"))
       		{	
       		 sql = "SELECT "
       				+ "customer_id,"
       				+ "add_date,"
       				+ "customer_name,"
       				+ "mobile_number,"
       				+ "gst_number,"
       				+ "pan_number,"
       				+ "tin_number,"
       				+ "customer_type,"
       				+ "billing_address,"
       				+ "birth_date,"
       				+ "is_mobile_verified,"
       				+ "credit_limit "
       				+ "FROM "
       				+ "shop_customer_master "
       				+ "WHERE "
       				+ "customer_name like '%"+actualKey+"%' "
       				+ "AND "
       				+ "shop_id = ? "
       				+ "AND "
       				+ "shop_code = ? ";
       			
       		}
       		else if(this.getSearchOption().equals("BYMOBILE"))
       		{
       			sql = "SELECT "
           				+ "customer_id,"
           				+ "add_date,"
           				+ "customer_name,"
           				+ "mobile_number,"
           				+ "gst_number,"
           				+ "pan_number,"
           				+ "tin_number,"
           				+ "customer_type,"
           				+ "billing_address,"
           				+ "birth_date,"
           				+ "is_mobile_verified,"
           				+ "credit_limit "
           				+ "FROM "
           				+ "shop_customer_master "
           				+ "WHERE "    				
           				+ "mobile_number like '%"+actualKey+"%' "
           				+ "AND "
           				+ "shop_id = ? "
           				+ "AND "
           				+ "shop_code = ? ";
           				
       		}
       		else if(this.getSearchOption().equals("BYADDRESS"))
       		{
       			sql = "SELECT "
           				+ "customer_id,"
           				+ "add_date,"
           				+ "customer_name,"
           				+ "mobile_number,"
           				+ "gst_number,"
           				+ "pan_number,"
           				+ "tin_number,"
           				+ "customer_type,"
           				+ "billing_address,"
           				+ "birth_date,"
           				+ "is_mobile_verified,"
           				+ "credit_limit "
           				+ "FROM "
           				+ "shop_customer_master "
           				+ "WHERE "    		        				
           				+ "billing_address like '%"+actualKey+"%' "
           				+ "AND "
           				+ "shop_id = ? "
           				+ "AND "
           				+ "shop_code = ? ";
       		}
       		else
       		{
       			sql = "SELECT "
           				+ "customer_id,"
           				+ "add_date,"
           				+ "customer_name,"
           				+ "mobile_number,"
           				+ "gst_number,"
           				+ "pan_number,"
           				+ "tin_number,"
           				+ "customer_type,"
           				+ "billing_address,"
           				+ "birth_date,"
           				+ "is_mobile_verified,"
           				+ "credit_limit "
           				+ "FROM "
           				+ "shop_customer_master "
           				+ "WHERE "    	
           				+ "customer_name like '%"+actualKey+"%' "
           				+ "AND "
           				+ "shop_id = ? "
           				+ "AND "
           				+ "shop_code = ? ";
       				
       			
       		}
       	//	System.out.println(sql);
       		
       		String db = (String) this.getSessionMap().get("db");
       		try {
       			
       			
				connection = GetDBConnection.getDBConnection(db);
       			
       			preparedStatement = connection.prepareStatement(sql);
       			preparedStatement.setString(1, shopId);
       			preparedStatement.setString(2, shopCode);
       			
       			
       		resultSet = preparedStatement.executeQuery();      			
       			resultSet.last();
				int rowCount = resultSet.getRow();
				resultSet.beforeFirst();
				
				if(rowCount>0)
				{
       			 	
	       			while(resultSet.next())
	       			{
	       				float openingBal = 0.0F;
	       				
	       				PreparedStatement getOpeningBalPSMT = null;
	       				ResultSet getOpeningBalRS = null;
	       				String sqlOpeningBal = "SELECT "
	       						+ "opening_balance "
	       						+ "FROM "
	       						+ "shop_cust_close_open_bal_master  "
	       						+ "WHERE "
	       						+ "customer_id = ? "
	       						+ "AND "
	       						+ "shop_id = ? "
	       						+ "AND "
	       						+ "shop_code = ? ";
	       				
	       				getOpeningBalPSMT = connection.prepareStatement(sqlOpeningBal);
	       				
	       				getOpeningBalPSMT.setString(1, resultSet.getString(1));
	       				getOpeningBalPSMT.setString(2, shopId);
	       				getOpeningBalPSMT.setString(3, shopCode);
	       				
	       				getOpeningBalRS = getOpeningBalPSMT.executeQuery();
	       				
	       				if(getOpeningBalRS.next())
	       				{
	       					openingBal = getOpeningBalRS.getFloat(1);
	       				}
	       			
	       				if(getOpeningBalRS != null)
	       				{
	       					getOpeningBalRS.close();
	       					getOpeningBalRS = null;
	       				}
	       				
	       				if(getOpeningBalPSMT != null)
	       				{
	       					getOpeningBalPSMT.close();
	       					getOpeningBalPSMT = null;
	       				}
	       				
	       				CustomerInfoDAO customerInfoDAO = new CustomerInfoDAO();
	       				
	       			//	System.out.println("Name:"+resultSet.getString(3)+",ID:"+resultSet.getString(1));
	       				customerInfoDAO.setCustomerId(resultSet.getString(1));
	       				customerInfoDAO.setCustName(resultSet.getString(3));
	       				customerInfoDAO.setMobileNumber(resultSet.getString(4));
	       				customerInfoDAO.setCustomertype(resultSet.getString(8));
	       				customerInfoDAO.setBillingaddress(resultSet.getString(9));
	       				customerInfoDAO.setCreditlimit(resultSet.getString(12));
	       				customerInfoDAO.setMobileVerified(resultSet.getString(11));       				
	       				
	       				customerInfoDAO.setKey(EncryptionDecryption.encrypt(resultSet.getString(1)));
	       				customerInfoDAO.setOpeningBalance(openingBal);
	       				 

		    			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;
	       				
	       				float totalPaymentDone = 0.0F;
	       				
	       				sql = "SELECT "	           					
	           					+ "bill_total_amount,"
	           					+ "bill_amount_received,"
	           					+ "bill_amount_retrurn "
	           					+ "FROM "
	           					+ "shop_bill_info_master "
	           					+ "where "		           					
	           					+ "customer_id = ? "
	           					+ "AND "
	           					+ "shop_id = ? "
	           					+ "AND "
	           					+ "shop_code = ? "
	           					+ "AND "
	           					+ "(bill_source IN ('ORIGINAL','BARCODEBILL'))";
	           				
	       				
	       				
	       				preparedStatement = connection.prepareStatement(sql);
	       				preparedStatement.setString(1, resultSet.getString(1));
	       				preparedStatement.setString(2, shopId);
	       				preparedStatement.setString(3, shopCode);
	       				
	       				ResultSet resultSetBillInfo = preparedStatement.executeQuery();
	       				
	       				while(resultSetBillInfo.next())
	       				{
	       					try
	       					{
	       						if(resultSetBillInfo.getString(2) != null)
	       						{
		       						floatBillAmt = resultSetBillInfo.getFloat(1);
		       						floatBillAmtReceived = resultSetBillInfo.getFloat(2);
		       						floatBillBalanceAmt = floatBillAmt - floatBillAmtReceived;
		       						
			           				
			           				if(floatBillBalanceAmt > 0)
			           				{
			           					totalAmountRecieved = totalAmountRecieved + floatBillAmtReceived;
			           					totalBalanceAmt = totalBalanceAmt+floatBillBalanceAmt;
			           					
			           					
			           				}
			           				else if(floatBillBalanceAmt <= 0)
			           				{
			           					totalAmountRecieved = totalAmountRecieved + floatBillAmt;
			           				}
	       						}
		           				
	       					}catch(NumberFormatException nfe)
	       					{
	       						result = "error";
	       						addActionError("Error while fomatting ! "
	       								+ "While calculating each bill datails for customer "+resultSet.getString(2));
	       						
	       					}
	       				}
	       				
	       				if(resultSetBillInfo != null)
	       				{
	       					resultSetBillInfo.close();
	       					resultSetBillInfo = null;
	       				}
	       				
	       				if(preparedStatement != null)
	       				{
	       					preparedStatement.close();
	       					preparedStatement = null;
	       				}
	       				
	       				sql = "SELECT "	           					
	           					+ "challan_total_amount,"
	           					+ "challan_amount_received,"
	           					+ "challan_amount_retrurn "
	           					+ "FROM "
	           					+ "shop_challan_info_master "
	           					+ "where "		           					
	           					+ "customer_id = ? "
	           					+ "AND "
	           					+ "shop_id = ? "
	           					+ "AND "
	           					+ "shop_code = ? ";
	           				
	       				
	       				
	       				preparedStatement = connection.prepareStatement(sql);
	       				preparedStatement.setString(1, resultSet.getString(1));
	       				preparedStatement.setString(2, shopId);
	       				preparedStatement.setString(3, shopCode);
	       				
	       				 resultSetBillInfo = preparedStatement.executeQuery();
	       				
	       				while(resultSetBillInfo.next())
	       				{
	       					try
	       					{
	       						if(resultSetBillInfo.getString(2) != null)
	       						{
		       						floatBillAmt = resultSetBillInfo.getFloat(1);
		       						floatBillAmtReceived = resultSetBillInfo.getFloat(2);
		       						floatBillBalanceAmt = floatBillAmt - floatBillAmtReceived;
		       						
			           				
			           				if(floatBillBalanceAmt > 0)
			           				{
			           					totalAmountRecieved = totalAmountRecieved + floatBillAmtReceived;
			           					totalBalanceAmt = totalBalanceAmt+floatBillBalanceAmt;
			           					
			           					
			           				}
			           				else if(floatBillBalanceAmt <= 0)
			           				{
			           					totalAmountRecieved = totalAmountRecieved + floatBillAmt;
			           				}
	       						}
		           				
	       					}catch(NumberFormatException nfe)
	       					{
	       						result = "error";
	       						addActionError("Error while fomatting ! "
	       								+ "While calculating each bill datails for customer "+resultSet.getString(2));
	       						
	       					}
	       				}
	       				
	       				totalBalanceAmt = totalBalanceAmt + customerInfoDAO.getOpeningBalance();
	       				if(resultSetBillInfo != null)
	       				{
	       					resultSetBillInfo.close();
	       					resultSetBillInfo = null;
	       				}
	       				
	       				if(preparedStatement != null)
	       				{
	       					preparedStatement.close();
	       					preparedStatement = null;
	       				}
	       				
	       				sql = "SELECT "
	       						+ "sum(payment_amount) "
	       						+ "FROM "
	       						+ "shop_customer_payment_master "
	       						+ "WHERE "
	       						+ "customer_id = '"+resultSet.getString(1)+"' "
	       						+ "AND "
	       						+ "shop_id = ? "
	       						+ "AND "
	       						+ "shop_code = ? ";
	       			
	       				preparedStatement = connection.prepareStatement(sql);
	       				preparedStatement.setString(1, shopId);
	       				preparedStatement.setString(2, shopCode);
	       				
	       				
	       				ResultSet resultSetTotalPayment = preparedStatement.executeQuery();
	       				
	       				resultSetTotalPayment.last();
	       				int rowCount2 = resultSetTotalPayment.getRow();
	       				resultSetTotalPayment.beforeFirst();
	       				
	       				if(rowCount2>0)
	       				{
	       				
		       				while(resultSetTotalPayment.next())
		       				{
		       					try
		       					{
		       						if(resultSetTotalPayment.getString(1) != null)
		       						{		       						
		       							totalPaymentDone = Float.parseFloat(resultSetTotalPayment.getString(1));
		       						}
		       					}
		       					catch(NumberFormatException nfe)
		       					{
		       						result = "error";
		       						addActionError("Error While Formatting while calculating total payment amount!"+nfe.getMessage());
		       					}
		       				}
		       						
		       				
		       				totalBalanceAmt = totalBalanceAmt - totalPaymentDone;
	       				}
	       				
	       				if(resultSetTotalPayment != null)
	       				{
	       					resultSetTotalPayment.close();
	       					resultSetTotalPayment = null;
	       				}
	       				customerInfoDAO.setTotalOutStandingAmount(""+totalBalanceAmt);
	       				
	       				this.getCustInfoList().add(customerInfoDAO);
	       				
	       			}
				}
   				
       		}	 catch (SQLException e) {
   				//e.printStackTrace();
   				addActionMessage(e.getErrorCode()+"||"+e.getMessage()+"|| SOME ERROR");
			
			} catch (Exception e) {
				//e.printStackTrace();
				
				addActionMessage(e.getMessage()+"|| SOME ERROR");
			}	
   			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) {}
   				}
   				
   			}
   		}
    	
		result = "success";
    	return result;
	}

	public String getKey() {
		return key;
	}

	public void setKey(String key) {
		this.key = key;
	}

	public List<CustomerInfoDAO> getCustInfoList() {
		return custInfoList;
	}

	public void setCustInfoList(List<CustomerInfoDAO> custInfoList) {
		this.custInfoList = custInfoList;
	}

	public String getSearchOption() {
		return searchOption;
	}

	public void setSearchOption(String searchOption) {
		this.searchOption = searchOption;
	}

		

	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;  
	}

	
	
}
