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.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.BillInfoForSearchDAO;
import com.reallinkbilling.db.GetDBConnection;
import com.reallinkbilling.resource.DataBaseResourceFactory;


public class SearchBillInfo extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = 992825583872321632L;
	private String key;	
	private String billDate;
	private  HttpSession session;
	private String billAll;
	private SessionMap<String,Object> sessionMap; 
	
	private ArrayList<BillInfoForSearchDAO> billInfoForSearchList = new ArrayList<BillInfoForSearchDAO>();
	
	
	@Override
	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");
        	
        	Connection connection = null;
       		PreparedStatement preparedStatement= null;
       		ResultSet resultSet = null;
       		
       		
       		String sql = "";
       		String db = (String) session.getAttribute("db");
       		try {
       			
				connection = GetDBConnection.getDBConnection(db);
       			String keyWithOutSpace = getKey().trim();
       			if(billAll.equals("true") & !(getKey().toUpperCase().contains("CASH") 
       					|| getKey().toUpperCase().contains("CAS")))
       			
       			{
       				sql = "SELECT "
           					+ "bill_number,"
           					+ "DATE_FORMAT(bill_date, '%d-%M-%Y'),"
           					+ "payment_type,"
           					+ "bill_type,"
           					+ "customer_name,"       					
           					+ "comments,"
           					+ "bill_total_amount,"
           					+ "bill_amount_received,"
           					+ "bill_amount_retrurn "
           					+ "FROM "
           					+ "shop_bill_info_master "
           					+ "WHERE "
           					+ "shop_id = '"+shopId+"' "
           					+ "AND "
           					+ "shop_code = '"+shopCode+"' "
           					+ "AND "
           					+ "bill_time <> 'NULL' "
           					+ "AND "              					
           					+ "(customer_name like '%"+keyWithOutSpace.toUpperCase()+"%' or bill_number like '%"+keyWithOutSpace+"%')";
           			
       			
       			}
       			else if(getKey().trim().toUpperCase().contains("ALL"))
       			{
       				sql = "SELECT "
           					+ "bill_number,"
           					+ "DATE_FORMAT(bill_date, '%d-%M-%Y'),"
           					+ "payment_type,"
           					+ "bill_type,"
           					+ "customer_name,"       					
           					+ "comments,"
           					+ "bill_total_amount,"
           					+ "bill_amount_received,"
           					+ "bill_amount_retrurn "
           					+ "FROM "
           					+ "shop_bill_info_master "
           					+ "WHERE "
           					+ "shop_id = '"+shopId+"' "
           					+ "AND "
           					+ "shop_code = '"+shopCode+"' "
           					+ "AND "
           					+ "bill_date = STR_TO_DATE('"+this.getBillDate()+"', '%d-%m-%Y') "
           					+ "and "
           					+ "bill_time <> 'NULL' ";
       				
           					
       			}
       			else if(getKey().trim().toUpperCase().contains("-"))
       			{
       				sql = "SELECT "
           					+ "bill_number,"
           					+ "DATE_FORMAT(bill_date, '%d-%M-%Y'),"
           					+ "payment_type,"
           					+ "bill_type,"
           					+ "customer_name,"       					
           					+ "comments,"
           					+ "bill_total_amount,"
           					+ "bill_amount_received,"
           					+ "bill_amount_retrurn "
           					+ "FROM "
           					+ "shop_bill_info_master "
           					+ "WHERE "
           					+ "shop_id = '"+shopId+"' "
           					+ "AND "
           					+ "shop_code = '"+shopCode+"' "
           					+ "AND "
           					+ "bill_date = STR_TO_DATE('"+this.getBillDate()+"', '%d-%m-%Y') "
           					+ "and "
           					+ "bill_time <> 'NULL' "
           					+ "and "
           					+ "customer_name NOT LIKE '%CASH%'";
       				
           					
       			}
       			else
       			{
       				sql = "SELECT "
           					+ "bill_number,"
           					+ "DATE_FORMAT(bill_date, '%d-%M-%Y'),"
           					+ "payment_type,"
           					+ "bill_type,"
           					+ "customer_name,"       					
           					+ "comments,"
           					+ "bill_total_amount,"
           					+ "bill_amount_received,"
           					+ "bill_amount_retrurn "
           					+ "FROM "
           					+ "shop_bill_info_master "
           					+ "WHERE "
           					+ "shop_id = '"+shopId+"' "
           					+ "AND "
           					+ "shop_code = '"+shopCode+"' "
           					+ "AND "
           					+ "bill_date = STR_TO_DATE('"+this.getBillDate()+"', '%d-%m-%Y') "
           					+ "and "
           					+ "bill_time <> 'NULL' "
           					+ "and "
           					+ "(customer_name like '%"+keyWithOutSpace.toUpperCase()+"%' or bill_number like '%"+keyWithOutSpace+"%')";
           			
       			}

      
       			preparedStatement = connection.prepareStatement(sql);
       			
       			resultSet = preparedStatement.executeQuery();
       			
       			while(resultSet.next())
       			{
       				int totalItemCount = 0;
       			
       				//System.out.println(resultSet.getString(1));
       				
       				BillInfoForSearchDAO dao = new BillInfoForSearchDAO();
       				dao.setBillNumber(resultSet.getString(1));
       				dao.setBillDate(resultSet.getString(2));
       				dao.setPaymentType(resultSet.getString(3));
       				dao.setBillType(resultSet.getString(4));
       				dao.setCustomerName(resultSet.getString(5));
       				dao.setComments(resultSet.getString(6));
       				dao.setTotalAmount(resultSet.getString(7));
       				
       				float floatTotalAmt = 0.0F;
       				float floatAmtReceived = 0.0F;
       				float floatBalanceAmt = 0.0F;
       				
       				try
       				{
	       				if(resultSet.getString(8) == null)
	       				{
	       				 dao.setAmountReceived("0.0");
	       				}
	       				else
	       				{
	       				 dao.setAmountReceived(resultSet.getString(8));
	       				}
	       				if(resultSet.getString(9) == null || resultSet.getString(9).equals(""))
	       				{
	       				 dao.setAmtReturn("0.0");
	       				}
	       				else
	       				{
	       				 dao.setAmtReturn(resultSet.getString(9));
	       				}
       				
       				String s = "SELECT count(*) "
       						+ "FROM shop_bill_product_info_master "
       						+ "WHERE "
       						+ "shop_id = '"+shopId+"' "
           					+ "AND "
           					+ "shop_code = '"+shopCode+"' "
           					+ "AND "
       						+ "bill_number='"+resultSet.getString(1)+"' ";
       				totalItemCount = new DataBaseResourceFactory().getRowCount(s);
       				
       				
       				
       				
       				
       				floatTotalAmt = Float.parseFloat(dao.getTotalAmount());
       				floatAmtReceived = Float.parseFloat(dao.getAmountReceived());
       				floatBalanceAmt = floatTotalAmt - floatAmtReceived;
       				}catch(NumberFormatException nfe)
       				{
       					nfe.printStackTrace();
       				}
       				
       				if(floatBalanceAmt > 0)
       				{
       					
       				}
       				else
       				{
       					floatBalanceAmt = 0.0F;
       				}
       				
       				dao.setItemsCount(""+totalItemCount);
       				dao.setBalanceAmount(""+floatBalanceAmt);
       				billInfoForSearchList.add(dao);
       				
       				
       			}
       			
       			
       			result = "success";
       					
       		} 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) {}
   				}
   				
   			}
   		}
    	
    	
    	return result;
	}

	public String getKey() {
		return key;
	}

	public void setKey(String key) {
		this.key = key;
	}



	public ArrayList<BillInfoForSearchDAO> getBillInfoForSearchList() {
		return billInfoForSearchList;
	}

	public void setBillInfoForSearchList(ArrayList<BillInfoForSearchDAO> billInfoForSearchList) {
		this.billInfoForSearchList = billInfoForSearchList;
	}

	public String getBillDate() {
		return billDate;
	}

	public void setBillDate(String billDate) {
		this.billDate = billDate;
	}

	public String getBillAll() {
		return billAll;
	}

	public void setBillAll(String billAll) {
		this.billAll = billAll;
	}

	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;  
	}

	
	
	
	
	
	

}
