package com.reallinkbilling.view;

import java.io.IOException;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
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.CustomerAccountDetailsDAO;
import com.reallinkbilling.cipher.EncryptionDecryption;
import com.reallinkbilling.db.GetDBConnection;

public class ViewCustAccountDetails extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = 2792982346324255485L;
	
	private HttpSession session;
	private String key;
	private String custName;
	private String custMobile;
	private String fromDate;
	private String toDate;
	private String totalBillAmountBeforeDate;
	private String totalAmountPaidBeforeDate;
	private String totalOutStandingAmountBeforeDate;
	
	private String totalBillAmountAllTheTime;
	private String totalAmountPaidAllTheTime;
	private String totalOutStandingAmountAllTheTime;
	private String smsData;
	private List<CustomerAccountDetailsDAO> customerAccountDetailsList = new ArrayList<CustomerAccountDetailsDAO>();
	private List<CustomerAccountDetailsDAO> customerAccountDetailsSoretdList = new ArrayList<CustomerAccountDetailsDAO>();
	private Float custOpeningBalnce = 0.0F;
	private String opeingBalancUpdatedBy;
	private String opeingBalanceUpdateDate;
	
	private Map<String,String> customerSearchOptionList = new HashMap<String,String>();
	private String customerSearchOption;
	private String defaultValue;
	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{
	        
	        	
	        	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 custId = EncryptionDecryption.decrypt(getKey());	
	        	
	        	setKey(EncryptionDecryption.encrypt(custId));
	    
	        	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,"
       						+ "DATE_FORMAT(opening_bal_update_date,'%d/%m/%Y'),"
       						+ "updated_by "
       						+ "FROM "
       						+ "shop_cust_close_open_bal_master "
       						+ "WHERE "
       						+ "customer_id = ? "
       						+ "AND "
       						+ "shop_id = ? "
       						+ "AND "
       						+ "shop_code = ?";
       				getOpeningBalPSMT = connection.prepareStatement(getOpeningBalSQL);
       				
       				getOpeningBalPSMT.setString(1, custId);
       				getOpeningBalPSMT.setString(2, shopId);
       				getOpeningBalPSMT.setString(3, shopCode);
       			
       				getOpeningBalRs = getOpeningBalPSMT.executeQuery();
       				
       				if(getOpeningBalRs.next())
       				{
       					this.setCustOpeningBalnce(getOpeningBalRs.getFloat(1));
       					this.setOpeingBalanceUpdateDate(getOpeningBalRs.getString(2));
       					this.setOpeingBalancUpdatedBy(getOpeningBalRs.getString(3));
       				}
       				else
       				{
       					this.setCustOpeningBalnce(0.0F);
       				}
       				if(getOpeningBalRs != null)
       				{
       					getOpeningBalRs.close();
       					getOpeningBalRs = null;
       				}
       				if(getOpeningBalPSMT != null)
       				{
       					getOpeningBalPSMT.close();
       					getOpeningBalPSMT = null;
       				}
       				
	       			
	       			sql = "SELECT "
	       					+ "SUM(payment_amount) "
	       					+ "FROM "
	       					+ "shop_customer_payment_master "
	       					+ "where "
	       					+ "shop_id = ? "
	       					+ "AND "
	       					+ "shop_code = ? "
	       					+ "AND "
	       					+ "customer_id = ? "
	       					+ "AND "
	       					+ "entry_date < STR_TO_DATE(?, '%d-%m-%Y')";
	       			
	       			preparedStatement = connection.prepareStatement(sql);
	       			preparedStatement.setString(1, shopId);
	       			preparedStatement.setString(2, shopCode	);
	       			preparedStatement.setString(3, custId);
	       			preparedStatement.setString(4, this.getFromDate());
	       			resultSet = preparedStatement.executeQuery();
	       			
	       			if(resultSet.next())
	       			{
	       				if(resultSet.getString(1) != null)
	       				{
	       				setTotalAmountPaidBeforeDate(resultSet.getString(1));
	       				}
	       				else
	       				{
	       					setTotalAmountPaidBeforeDate("0.0");
	       				}
	       			}
	       			else
	       			{
	       				setTotalAmountPaidBeforeDate("0.0");
	       			}
	       			
	       			//System.out.println(" Amount Paid :"+getTotalAmountPaidBeforeDate());
	       			
	       			if(preparedStatement != null)
       				{
       					preparedStatement.close();
       					preparedStatement = null;
       				}
	       			
	       			float floatTotalAmt = 0.0F;
       				float floatAmtReceived = 0.0F;
       				float floatBalanceAmt = 0.0F;
       				float totalBalanceAmt = 0.0F;
       				float totalAmountReceived = Float.parseFloat(this.getTotalAmountPaidBeforeDate());
       				float totalBillAmountBeforeDate = 0.0F;
       				
       				
       				
       				sql = "SELECT "	           					
           					+ "bill_total_amount,"
           					+ "bill_amount_received,"
           					+ "bill_amount_retrurn "
           					+ "FROM "
           					+ "shop_bill_info_master "
           					+ "WHERE "		           					
           					+ "customer_id = ? "
           					+ "AND "
           					+ "bill_date < STR_TO_DATE(?, '%d-%m-%Y') "
           					+ "AND "
           					+ "shop_id = ? "
           					+ "AND "
           					+ "shop_code = ? "
           					+ "AND "
           					+ "(bill_source IN ('ORIGINAL','BARCODEBILL'))";
       				preparedStatement = connection.prepareStatement(sql);
       				preparedStatement.setString(1, custId);
	       			preparedStatement.setString(2, this.getFromDate());
	       			preparedStatement.setString(3, shopId);
	       			preparedStatement.setString(4, shopCode);
	       			
	       			
       				ResultSet resultSetBillInfo = preparedStatement.executeQuery();
       				
       				while(resultSetBillInfo.next())
       				{
       					try
       					{
	       					floatTotalAmt = Float.parseFloat(resultSetBillInfo.getString(1));
	           				floatAmtReceived = Float.parseFloat(resultSetBillInfo.getString(2));
	           				
	           				totalBillAmountBeforeDate = totalBillAmountBeforeDate + floatTotalAmt;
	           				floatBalanceAmt = floatTotalAmt - floatAmtReceived;
	           			
	           				
	           				if(floatBalanceAmt > 0)
	           				{
	           					totalAmountReceived = totalAmountReceived + floatAmtReceived;
	           					totalBalanceAmt = totalBalanceAmt+floatBalanceAmt; 	           					
	           				}
	           				else if(floatBalanceAmt == 0)
	           				{
	           					totalAmountReceived = totalAmountReceived + floatTotalAmt;
	           				}
	           				
       					}catch(NumberFormatException nfe)
       					{
       						result = "error";
       						
       					}
       				}
       				
       				
       				
       				float floatTotalAmtPaidBeforeDate = 0.0F;
       				if(getTotalAmountPaidBeforeDate() != null)
       				{
       					try
       					{
       					floatTotalAmtPaidBeforeDate = totalAmountReceived;
       					
       					}
       					catch(NumberFormatException nfe)
       					{
       						result = "error";
       						addActionError("Error while parsing in before date calculation !");
       					}
       				}
       				setTotalOutStandingAmountBeforeDate(""+(totalBillAmountBeforeDate-floatTotalAmtPaidBeforeDate));
       				setTotalAmountPaidBeforeDate(""+floatTotalAmtPaidBeforeDate);
	       			setTotalBillAmountBeforeDate(""+totalBillAmountBeforeDate);
	       			
	       			if(resultSetBillInfo != null)
	       			{
	       				resultSetBillInfo.close();
	       			}
	       			if(resultSet != null)
	       			{
	       				resultSet.close();
	       			}
	       			if(preparedStatement != null)
       				{
       					preparedStatement.close();
       				}
	       			
	       			
	       			sql = "SELECT "
	       					+ "DATE_FORMAT(entry_date, '%d/%m/%Y'),"
	       					+ "payment_amount,"
	       					+ "payment_details,"
	       					+ "payment_received_by,"
	       					+ "entry_id,"
	       					+ "DATE_FORMAT(entry_date,'%d-%m-%Y') "
	       					+ "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'))";
	       			
	       			preparedStatement = connection.prepareStatement(sql);
	       			preparedStatement.setString(1, shopId);
	       			preparedStatement.setString(2, shopCode);
	       			preparedStatement.setString(3, custId);
	       			preparedStatement.setString(4, this.getFromDate());
	       			preparedStatement.setString(5, this.getToDate());
	       			
	       			resultSet = preparedStatement.executeQuery();
	       		
	       			
	       			while(resultSet.next())	       				
	       			{
	       				
	       				CustomerAccountDetailsDAO accountDetailsDAO = new CustomerAccountDetailsDAO();
	       				Date date1=new SimpleDateFormat("dd/MM/yyyy").parse(resultSet.getString(1)); 
	       				
	       				accountDetailsDAO.setDate(date1);
	       				accountDetailsDAO.setAmountPaid(resultSet.getString(2));
	       				accountDetailsDAO.setComments(resultSet.getString(3));
	       				accountDetailsDAO.setBilledBy(resultSet.getString(4));
	       				accountDetailsDAO.setBalanceAmount("0.0");
	       				accountDetailsDAO.setBillNumber("RECEIVED");
	       				//custid + entryid + date + amount
	       				accountDetailsDAO.setCustPaymentId(URLEncoder.encode(EncryptionDecryption.encrypt(custId+"#"+resultSet.getString(5)+"#"+resultSet.getString(6)+"#"+resultSet.getString(2)),"UTF-8"));
	       				accountDetailsDAO.setBillAmount("0.0");
	       				
	       				this.getCustomerAccountDetailsList().add(accountDetailsDAO);
	       				
	       			}
	       			
	       			if(preparedStatement != null)
       				{
       					preparedStatement.close();
       					preparedStatement = null;
       				}
	       			
	       			sql = "SELECT "
	       					+ "bill_number,"
	       					+ "DATE_FORMAT(bill_date, '%d/%m/%Y'),"
	       					+ "comments,"	           					
           					+ "bill_total_amount,"
           					+ "bill_amount_received,"
           					+ "bill_amount_retrurn,"
           					+ "billed_by,"
           					+ "bill_type,"
           					+ "DATE_FORMAT(bill_date, '%d-%m-%Y') "
           					+ "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'))";
	       			
       						preparedStatement = connection.prepareStatement(sql);
       						preparedStatement.setString(1, shopId);
       						preparedStatement.setString(2, shopCode);
       						preparedStatement.setString(3, custId);
       		       			preparedStatement.setString(4, this.getFromDate());
       		       			preparedStatement.setString(5, this.getToDate());

       		       			
       		       			resultSet = preparedStatement.executeQuery();
       						
       						
       						while(resultSet.next())
       						{
       							//System.out.println(resultSet.getString(1)+"-"+resultSet.getString(8));

       		       				CustomerAccountDetailsDAO accountDetailsDAO = new CustomerAccountDetailsDAO();
       		       				
       		       			Date date1=new SimpleDateFormat("dd/MM/yyyy").parse(resultSet.getString(2)); 
    	       				
    	       			
       		       				
       		       				accountDetailsDAO.setBillNumber(resultSet.getString(1));
       		       				accountDetailsDAO.setDate(date1);
       		       				accountDetailsDAO.setBillAmount(resultSet.getString(4));
       		       				accountDetailsDAO.setComments(resultSet.getString(3));
       		       				accountDetailsDAO.setBilledBy(resultSet.getString(7));
       		       				accountDetailsDAO.setBillType(resultSet.getString(8));
       		       				accountDetailsDAO.setDateAsStringType2(resultSet.getString(9));
       		       			
       		       				
       		       			try
	       					{
		       					floatTotalAmt = Float.parseFloat(resultSet.getString(4));
		           				floatAmtReceived = Float.parseFloat(resultSet.getString(5));
		           				floatBalanceAmt = floatTotalAmt - floatAmtReceived;
		           				
		           				if(floatBalanceAmt > 0)
		           				{
		           					accountDetailsDAO.setBalanceAmount(""+floatBalanceAmt); 
		           					accountDetailsDAO.setAmountPaid(resultSet.getString(5));
		           				}
		           				else
		           				{
		           					accountDetailsDAO.setAmountPaid(resultSet.getString(4));
		           					accountDetailsDAO.setBalanceAmount("0.0"); 
		           				}
		           				
	       					}catch(NumberFormatException nfe)
	       					{
	       						result = "error";
	       						addActionError("Error While Formatting while calculating balance amount!"+nfe.getMessage());
	       					}
       		       				       		       				
       		       			this.getCustomerAccountDetailsList().add(accountDetailsDAO);
       						}
       						
       						
       						// CHALLAN ACCOUNT DETAILS

       						if(resultSet != null)
       						{
       							resultSet.close();
       							resultSet = null;
       						}
       						
       						if(preparedStatement != null)
       	       				{
       	       					preparedStatement.close();
       	       					preparedStatement = null;
       	       				}
       		       			
       		       			sql = "SELECT "
       		       					+ "challan_number,"
       		       					+ "DATE_FORMAT(challan_date, '%d/%m/%Y'),"
       		       					+ "comments,"	           					
       	           					+ "challan_total_amount,"
       	           					+ "challan_amount_received,"
       	           					+ "challan_amount_retrurn,"
       	           					+ "challan_by,"
       	           					+ "DATE_FORMAT(challan_date, '%d-%m-%Y') "
       	           					+ "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'))";
       		       			
       	       						preparedStatement = connection.prepareStatement(sql);
       	       						preparedStatement.setString(1, shopId);
       	       						preparedStatement.setString(2, shopCode);
       	       						preparedStatement.setString(3, custId);
       	       		       			preparedStatement.setString(4, this.getFromDate());
       	       		       			preparedStatement.setString(5, this.getToDate());

       	       		       			resultSet = preparedStatement.executeQuery();
       	       						
       	       		       			//System.out.println(preparedStatement.toString());
       	       						
       	       						while(resultSet.next())
       	       						{

       	       		       				CustomerAccountDetailsDAO accountDetailsDAO = new CustomerAccountDetailsDAO();
       	       		       				
       	       		       			Date date1=new SimpleDateFormat("dd/MM/yyyy").parse(resultSet.getString(2)); 
       	    	       				
       	    	       			
       	       		       				
       	       		       				accountDetailsDAO.setBillNumber(resultSet.getString(1));
       	       		       				accountDetailsDAO.setDate(date1);
       	       		       				accountDetailsDAO.setBillAmount(resultSet.getString(4));
       	       		       				accountDetailsDAO.setComments(resultSet.getString(3));
       	       		       				accountDetailsDAO.setBilledBy(resultSet.getString(7));
       	       		       				accountDetailsDAO.setBillType("CHALLAN");
       	       		       				accountDetailsDAO.setDateAsStringType2(resultSet.getString(8));
       	       		       			
       	       		       				
       	       		       			try
       		       					{
       			       					floatTotalAmt = Float.parseFloat(resultSet.getString(4));
       			           				floatAmtReceived = Float.parseFloat(resultSet.getString(5));
       			           				floatBalanceAmt = floatTotalAmt - floatAmtReceived;
       			           				
       			           				if(floatBalanceAmt > 0)
       			           				{
       			           					accountDetailsDAO.setBalanceAmount(""+floatBalanceAmt); 
       			           					accountDetailsDAO.setAmountPaid(resultSet.getString(5));
       			           				}
       			           				else
       			           				{
       			           					accountDetailsDAO.setAmountPaid(resultSet.getString(4));
       			           					accountDetailsDAO.setBalanceAmount("0.0"); 
       			           				}
       			           				
       		       					}catch(NumberFormatException nfe)
       		       					{
       		       						result = "error";
       		       						addActionError("Error While Formatting while calculating balance amount!"+nfe.getMessage());
       		       					}
       	       		       				       		       				
       	       		       			this.getCustomerAccountDetailsList().add(accountDetailsDAO);
       	       						}
       	       						
       						float totalBillAmount = 0.0F;
       						float totalPaymentReceived = 0.0F;
       						float totalOutStandingAmount = 0.0F;
       						
       						float totalOutSAmtBeforeDate = Float.parseFloat(this.getTotalOutStandingAmountBeforeDate());
						
       						float dueAmt = this.getCustOpeningBalnce()+totalOutSAmtBeforeDate;
       						
       					 for(CustomerAccountDetailsDAO dao : customerAccountDetailsList)
       					 {
       						 
       						 
       						
       						
       							
	       						 try
	       						 {
	       							 
	       							 float billAmount = Float.parseFloat(dao.getBillAmount());
	       							 float paymentReceived = Float.parseFloat(dao.getAmountPaid());
	       							 
	       							totalBillAmount = totalBillAmount+billAmount;
	       							totalPaymentReceived = totalPaymentReceived + paymentReceived;
	       							
	       						 }
	       						 catch(NumberFormatException nfe)
	       						 {
	       							 nfe.printStackTrace();
	       							 result = "error";
	       							 addActionError("Error while parsing 3 !");
	       						 }
       						 
       						 
       					 }
       					totalOutStandingAmount = (totalBillAmountBeforeDate+totalBillAmount) - (floatTotalAmtPaidBeforeDate+totalPaymentReceived);
       					 
       					totalOutStandingAmount = totalOutStandingAmount + +this.getCustOpeningBalnce();
       					 this.setTotalBillAmountAllTheTime(""+(totalBillAmountBeforeDate+totalBillAmount));
       					 this.setTotalAmountPaidAllTheTime(""+(floatTotalAmtPaidBeforeDate+totalPaymentReceived));
       					 this.setTotalOutStandingAmountAllTheTime(""+totalOutStandingAmount);
       					 
       					 
       					 
       					
    					 
    					 	//System.out.println(dateList);
    					 	
    						// System.out.println(sortedDateList);
    					 
    						 
    						 Collections.sort(customerAccountDetailsList, new Comparator<CustomerAccountDetailsDAO>() {
    							  public int compare(CustomerAccountDetailsDAO o1, CustomerAccountDetailsDAO o2) {
    							      if (o1.getDate() == null || o2.getDate() == null)
    							        return 0;
    							      return o1.getDate().compareTo(o2.getDate());
    							  }
    							});
    					
    					
    						  
    						
       					
       					 for(CustomerAccountDetailsDAO dao : customerAccountDetailsList)
       					 {
       						 DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yy");  
							 Date d = dao.getDate();
							 dao.setDateAsString(dateFormat.format(d));
							 
							  if(dao.getBillNumber().equals("RECEIVED"))
							  {
								 
								  dueAmt = dueAmt-Float.parseFloat(dao.getAmountPaid());
								
								  dao.setDueAmount(""+dueAmt);
								 
							  }
							  else
							  {								
								
								  dueAmt = dueAmt+Float.parseFloat(dao.getBalanceAmount());
								
								  dao.setDueAmount(""+dueAmt);
							  }
       					 }
       					 
       					 if(!hasActionErrors())
       					 {
    					 	
       						result = "success";
       						this.setSmsData(EncryptionDecryption.encrypt(getCustMobile()+"-"+this.getTotalOutStandingAmountAllTheTime()));
             			
       					 }
       					 else
       						 
       					 {
       						 result = "error";
       					 }
       				
	       			
	       		}catch (SQLException e) {
	       			//e.printStackTrace();
	    			result = "error";
	    			addActionError(e.getErrorCode()+" || "+e.getMessage() +" || DETAILS NOT FOUND !");
	    		} catch (IOException e) {
	    			//e.printStackTrace();
	    			result = "error";
	    			addActionError(e.getMessage()+" || "+e.getCause()+" || DETAILS NOT FOUND !");
	    		} catch (ParseException e) {
					// TODO Auto-generated catch blockaddActionError(e.getMessage()+" || "+e.getCause()+" || DETAILS NOT FOUND !");
					//e.printStackTrace();
				}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;
}
	@Override
	public void validate() {
		if (getFromDate() != null && getToDate() != null
		        && (getFromDate().trim().isEmpty() || getToDate().trim().isEmpty())) {
		    addActionError("Please select dates !");
		}
		
		
		String custId = EncryptionDecryption.decrypt(this.getKey());
			
			String shopId = (String) this.getSessionMap().get("shopId");
    		String shopCode = (String) this.getSessionMap().get("shopCode");
         
        	Connection connection = null;
       		PreparedStatement preparedStatement= null;
       		ResultSet resultSet = null;
       		
       		
       		String sql = "";
       		String db = (String) this.getSessionMap().get("db");
			try
			{
				connection = GetDBConnection.getDBConnection(db);
       			
       			
       			sql = "SELECT "
       					+ "customer_name,"
       					+ "mobile_number "
       					+ "FROM "
       					+ "shop_customer_master "
       					+ "where "
       					+ "shop_id = ? "
       					+ "AND "
       					+ "shop_code = ? "
       					+ "AND "
       					+ "customer_id = ?";
       			
       			
       			preparedStatement = connection.prepareStatement(sql);
       			preparedStatement.setString(1, shopId);
       			preparedStatement.setString(2, shopCode);
       			preparedStatement.setString(3, custId);
       			
       			resultSet = preparedStatement.executeQuery();
       			
       			resultSet.last();
       			int rowCount = resultSet.getRow();
       			resultSet.beforeFirst();
       			
       			
       			if(rowCount>0)
       			{
       				while(resultSet.next())
	       			{
	       				
	       				this.setCustName(resultSet.getString(1));
	       				this.setCustMobile(resultSet.getString(2));					
	       				
	       				
	       			}
       			}
       			
			}catch (Exception e) {
				// TODO: handle exception
			}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) {}
    			}
    			
    		}
			
			
		
	}
	
	
	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 getCustName() {
		return custName;
	}
	public void setCustName(String custName) {
		this.custName = custName;
	}
	public String getCustMobile() {
		return custMobile;
	}
	public void setCustMobile(String custMobile) {
		this.custMobile = custMobile;
	}
	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 String getTotalBillAmountBeforeDate() {
		return totalBillAmountBeforeDate;
	}
	public void setTotalBillAmountBeforeDate(String totalBillAmountBeforeDate) {
		this.totalBillAmountBeforeDate = totalBillAmountBeforeDate;
	}
	public String getTotalAmountPaidBeforeDate() {
		return totalAmountPaidBeforeDate;
	}
	public void setTotalAmountPaidBeforeDate(String totalAmountPaidBeforeDate) {
		this.totalAmountPaidBeforeDate = totalAmountPaidBeforeDate;
	}
	public String getTotalOutStandingAmountBeforeDate() {
		return totalOutStandingAmountBeforeDate;
	}
	public void setTotalOutStandingAmountBeforeDate(String totalOutStandingAmountBeforeDate) {
		this.totalOutStandingAmountBeforeDate = totalOutStandingAmountBeforeDate;
	}
	public List<CustomerAccountDetailsDAO> getCustomerAccountDetailsList() {
		return customerAccountDetailsList;
	}
	public void setCustomerAccountDetailsList(List<CustomerAccountDetailsDAO> customerAccountDetailsList) {
		this.customerAccountDetailsList = customerAccountDetailsList;
	}
	public List<CustomerAccountDetailsDAO> getCustomerAccountDetailsSoretdList() {
		return customerAccountDetailsSoretdList;
	}
	public void setCustomerAccountDetailsSoretdList(List<CustomerAccountDetailsDAO> customerAccountDetailsSoretdList) {
		this.customerAccountDetailsSoretdList = customerAccountDetailsSoretdList;
	}
	public String getTotalBillAmountAllTheTime() {
		return totalBillAmountAllTheTime;
	}
	public void setTotalBillAmountAllTheTime(String totalBillAmountAllTheTime) {
		this.totalBillAmountAllTheTime = totalBillAmountAllTheTime;
	}
	public String getTotalAmountPaidAllTheTime() {
		return totalAmountPaidAllTheTime;
	}
	public void setTotalAmountPaidAllTheTime(String totalAmountPaidAllTheTime) {
		this.totalAmountPaidAllTheTime = totalAmountPaidAllTheTime;
	}
	public String getTotalOutStandingAmountAllTheTime() {
		return totalOutStandingAmountAllTheTime;
	}
	public void setTotalOutStandingAmountAllTheTime(String totalOutStandingAmountAllTheTime) {
		this.totalOutStandingAmountAllTheTime = totalOutStandingAmountAllTheTime;
	}
	public String getSmsData() {
		return smsData;
	}
	public void setSmsData(String smsData) {
		this.smsData = smsData;
	}
	public Float getCustOpeningBalnce() {
		return custOpeningBalnce;
	}
	public void setCustOpeningBalnce(Float custOpeningBalnce) {
		this.custOpeningBalnce = custOpeningBalnce;
	}
	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 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 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;  
	}
}
