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.catalina.Session;
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.QuotationInformationDAO;
import com.reallinkbilling.db.GetDBConnection;

public class QuotationInfoJSON extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = -3828289564696834998L;
	
	private String key;
	private String searchOption;
	
	private HttpSession session;	
	private SessionMap<String,Object> sessionMap;
	
	
	
	
	private List<QuotationInformationDAO> quotationInfoList = new ArrayList<QuotationInformationDAO>();
	
	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  "
       				+ "customer_id "
       				+ "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 "
           				+ "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 "
           				+ "FROM "
           				+ "shop_customer_master "
           				+ "WHERE "
           				+ "billing_address LIKE '%"+actualKey+"%' "
           				+ "AND "
    					+ "shop_id = ? "
    					+ "AND "
    					+ "shop_code = ?";
       		}
       		else
       		{
       		 sql = "SELECT  "
	       				+ "customer_id "
	       				+ "FROM "
        				+ "shop_customer_master "
        				+ "WHERE "
        				+ "customer_name LIKE '%"+actualKey+"%' "
        				+ "AND "
    					+ "shop_id = ? "
    					+ "AND "
    					+ "shop_code = ?";
       				
       			
       		}
       		
       		String db = (String) session.getAttribute("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)
				{
       			 	
					if (rowCount > 0) {
					    while (resultSet.next()) {
					        String customerId = resultSet.getString(1); 

					       
					        String quotationQuery = "SELECT "
					                + "quotationnumber, "
					                + "quotationdate, "
					                + "customername, "
					                + "quotationtotalamount, "
					                + "quotation_by "
					                + "FROM quotation_info "
					                + "WHERE custid = ? "
					                + "AND shop_id = ? "
					                + "AND shop_code = ?";

					        try (PreparedStatement quotationStatement = connection.prepareStatement(quotationQuery)) {
					            quotationStatement.setString(1, customerId);
					            quotationStatement.setString(2, shopId);
					            quotationStatement.setString(3, shopCode);

					            try (ResultSet quotationResultSet = quotationStatement.executeQuery()) {
					                while (quotationResultSet.next()) {
					                    QuotationInformationDAO quotationInfoDAO = new QuotationInformationDAO();

					                    quotationInfoDAO.setQuotationNumber(quotationResultSet.getString("quotationnumber"));
					                    quotationInfoDAO.setQuotationDate(quotationResultSet.getString("quotationdate"));
					                    quotationInfoDAO.setCustomerName(quotationResultSet.getString("customername"));
					                    quotationInfoDAO.setQuotationAmount(quotationResultSet.getString("quotationtotalamount"));
					                    quotationInfoDAO.setQuotationBy(quotationResultSet.getString("quotation_by"));

					                    quotationInfoList.add(quotationInfoDAO);
					                }
					            }
					        }
					    }
					}

				}	
       		}	 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();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<QuotationInformationDAO> getQuotationInfoList() {
		return quotationInfoList;
	}

	public void setQuotationInfoList(List<QuotationInformationDAO> quotationInfoList) {
		this.quotationInfoList = quotationInfoList;
	}

	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;
	}
	
	@Override  
	public void setSession(Map<String, Object> map) {  
	    sessionMap=(SessionMap<String, Object>)map;  
	}


	
	
}
