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.SupplierInfoDAO;
import com.reallinkbilling.cipher.EncryptionDecryption;
import com.reallinkbilling.db.GetDBConnection;

public class SearchSupplierInfo extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = 6756358979030980171L;
	private String key;
	private String searchOption;
	private SessionMap<String,Object> sessionMap;  

	private HttpSession session;	
	
	private List<SupplierInfoDAO> supplierInfoList = new ArrayList<SupplierInfoDAO>();
	
	
	public String execute() throws Exception {

	 	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");
    		
    		
        	Connection connection = null;
       		PreparedStatement preparedStatement= null;
       		ResultSet resultSet = null;
       		
       	
       		String actualKey = this.getKey().trim();
       		
       		String sql = "";
	       		if(this.getSearchOption().equals("BYNAME"))
	       		{	
	       			sql = "SELECT "
	       					+ "supplier_id,"
	       					+ "suplier_name,"
	       					+ "contact_number,"
	       					+ "supplier_address "
	       					+ "FROM "
	       					+ "shop_supplier_master "
	       					+ "WHERE "
	       					+ "suplier_name LIKE '%"+actualKey+"%' "
	       					+ "AND "
	       					+ "shop_id = ? "
	       					+ "AND "
	       					+ "shop_code = ? ";
	       		}
	       		else if(this.getSearchOption().equals("BYMOBILE"))
	       		{
	       			sql = "SELECT "
	       					+ "supplier_id,"
	       					+ "suplier_name,"
	       					+ "contact_number,"
	       					+ "supplier_address "
	       					+ "FROM "
	       					+ "shop_supplier_master "
	       					+ "WHERE "
	       					+ "contact_number LIKE '%"+actualKey+"%' "
	       					+ "AND "
	       					+ "shop_id = ? "
	       					+ "AND "
	       					+ "shop_code = ? ";
	       		}
	       		else if(this.getSearchOption().equals("BYADDRESS"))
	       		{
	       			sql = "SELECT "
	       					+ "supplier_id,"
	       					+ "suplier_name,"
	       					+ "contact_number,"
	       					+ "supplier_address "
	       					+ "FROM "
	       					+ "shop_supplier_master "
	       					+ "WHERE "
	       					+ "supplier_address LIKE '%"+actualKey+"%' "
	       					+ "AND "
	       					+ "shop_id = ? "
	       					+ "AND "
	       					+ "shop_code = ? ";
	       		}
	       		
	       		
	       		
	       		String db = (String) this.getSessionMap().get("db");
			       	try{
			       			
			       			
							connection = GetDBConnection.getDBConnection(db);
			       		
			       			preparedStatement = connection.prepareStatement(sql);
			       			preparedStatement.setString(1, shopId);
			       			preparedStatement.setString(2, shopCode);
			       			
			       		//	System.out.println(preparedStatement.toString());
			       			resultSet = preparedStatement.executeQuery();      			
			       			resultSet.last();
							int rowCount = resultSet.getRow();
							resultSet.beforeFirst();
							
							if(rowCount>0)
							{
								while(resultSet.next())
								{
									
									SupplierInfoDAO infoDAO = new SupplierInfoDAO();
									
									PreparedStatement getOpeningBalPSMT = null;
				       				ResultSet getOpeningBalRs = null;
				       				String getOpeningBalSQL = "SELECT "
				       						+ "opening_balance_amount "
				       						+ "FROM "
				       						+ "shop_supplier_open_close_balance_master "
				       						+ "WHERE "
				       						+ "supplier_id = ? "
				       						+ "AND "
				       						+ "shop_id = ? "
				       						+ "AND "
				       						+ "shop_code = ?";
				       				getOpeningBalPSMT = connection.prepareStatement(getOpeningBalSQL);
				       				
				       				getOpeningBalPSMT.setString(1, resultSet.getString(1));
				       				getOpeningBalPSMT.setString(2, shopId);
				       				getOpeningBalPSMT.setString(3, shopCode);
				       				
				       				getOpeningBalRs = getOpeningBalPSMT.executeQuery();
				       				
				       				if(getOpeningBalRs.next())
				       				{
				       					infoDAO.setSupplierOpeningBal(getOpeningBalRs.getFloat(1));
				       				}
				       				else
				       				{
				       					infoDAO.setSupplierOpeningBal(0.0F);
				       				}
				       				
				       				if(getOpeningBalRs != null)
				       				{
				       					getOpeningBalRs.close();
				       					getOpeningBalRs = null;
				       				}
				       				if(getOpeningBalPSMT != null)
				       				{
				       					getOpeningBalPSMT.close();
				       					getOpeningBalPSMT = null;
				       				}

									
									infoDAO.setSupplierId(resultSet.getString(1));
									infoDAO.setSupplierName(resultSet.getString(2));
									infoDAO.setSupplierMobile(resultSet.getString(3));
									infoDAO.setSupplierAddress(resultSet.getString(4));
									infoDAO.setKey(EncryptionDecryption.encrypt(resultSet.getString(1)));
									
									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 "
				       						+ "supplier_id = ? "
				       						+ "AND "
				       						+ "shop_id = ? "
				       						+ "AND "
				       						+ "shop_code = ?";
				       				
				       				getTotalPurchasePSMT = connection.prepareStatement(getTotalPurchaseSQL);
				       				
				       				getTotalPurchasePSMT.setString(1, resultSet.getString(1));
				       				getTotalPurchasePSMT.setString(2, shopId);
				       				getTotalPurchasePSMT.setString(3, shopCode);
				       				
				       				
				       				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 "
				       						+ "supplier_id = ? "
				       						+ "AND "
				       						+ "shop_id = ? "
				       						+ "AND "
				       						+ "shop_code = ?";
				       				
				       				getSuppAmtPaidPSMT = connection.prepareStatement(getSuppAmtPaidSQL);
				       				
				       				
				       				
				       				getSuppAmtPaidPSMT.setString(1, resultSet.getString(1));
				       				getSuppAmtPaidPSMT.setString(2, shopId);
				       				getSuppAmtPaidPSMT.setString(3, shopCode);
				       				
				       				
				       				getSuppAmtPaidRS = getSuppAmtPaidPSMT.executeQuery();
				       				
				       				
				       				
				       				if(getSuppAmtPaidRS.next())
				       				{
				       					floatTotalAmountPaid = floatTotalAmountPaid + getSuppAmtPaidRS.getFloat(1);
				       				}
				       				
				       				floatTotalBalanceAmt = floatTotalPurchaseAmt - floatTotalAmountPaid;
				       				
				       				infoDAO.setTotalPurchase(""+floatTotalPurchaseAmt);
				       				infoDAO.setDebtAmount(floatTotalBalanceAmt+infoDAO.getSupplierOpeningBal());
				       				
				       				
				       				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;
				       				}
				       				
									
									this.getSupplierInfoList().add(infoDAO);
								}
								
							}
			       		}
	       				catch (SQLException e) {
	       					//e.printStackTrace();
	       				addActionMessage(e.getErrorCode()+"||"+e.getMessage()+"|| SOME ERROR");
			
						} catch (IOException e) {
							//e.printStackTrace();
							addActionMessage(e.getMessage()+"|| SOME ERROR");
						}	
						finally
						{
							if(resultSet != null)
							{
								try{resultSet.close();}catch(SQLException sqe){}   					
							}
							if(preparedStatement != null)
							{
								try {preparedStatement.close();} catch (SQLException e) {}
							}
							if(connection != null)
							{
								try {connection.close();} catch (SQLException e) {}
							}
							
						}
	}
			
       result = "success";
		
		return result;
	}
		
		



	public String getKey() {
		return key;
	}


	public void setKey(String key) {
		this.key = key;
	}


	public String getSearchOption() {
		return searchOption;
	}


	public void setSearchOption(String searchOption) {
		this.searchOption = searchOption;
	}


	


	public List<SupplierInfoDAO> getSupplierInfoList() {
		return supplierInfoList;
	}


	public void setSupplierInfoList(List<SupplierInfoDAO> supplierInfoList) {
		this.supplierInfoList = supplierInfoList;
	}


	public static long getSerialversionuid() {
		return serialVersionUID;
	}
	
	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;  
	}
}
