package com.reallinkbilling.report;

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.CustomerAccountDetailsDAO;
import com.reallinkbilling.db.GetDBConnection;

public class SearchSaleBill  extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = -8591113875387565921L;
	
	private HttpSession session;
	private String fromDate;
	private String toDate;
	private String customerName;
	private String billNumber;
	private SessionMap<String,Object> sessionMap; 
	private List<CustomerAccountDetailsDAO> customerAccountDetailsList = new ArrayList<CustomerAccountDetailsDAO>();
	private float totalBillAmount = 0.0F;
	private float totalAmountReceived = 0.0F;
	private float totalCreditAmount = 0.0F;
	
	
	public String execute(){
		String result = "login";
		
		totalBillAmount = 0.0F;
		totalAmountReceived = 0.0F;
		totalCreditAmount = 0.0F;
		
		this.setSession(ServletActionContext.getRequest().getSession(false)); 
		 
	        if(this.getSession()==null || this.getSession().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 sql = "";
	    		String db = (String) this.getSessionMap().get("db");
	    		
	    		try
	    		{
	    			
					connection = GetDBConnection.getDBConnection(db);
					
					
					//System.out.println(this.getCustomerName());
					if(this.getBillNumber() != null && this.getBillNumber().length() > 0)
					{
						sql = "SELECT "	           					
	           					+ "bill_number,"
	           					+ "customer_id,"
	           					+ "customer_name,"
	           					+ "transportation_charges,"
	           					+ "DATE_FORMAT(bill_date, '%d/%m/%Y'),"
	           					+ "bill_type,"
	           					+ "comments,"
	           					+ "bill_total_amount,"
	           					+ "bill_amount_received,"
	           					+ "bill_amount_retrurn,"
	           					+ "DATE_FORMAT(bill_date, '%d-%m-%Y') "
	           					+ "FROM "
	           					+ "shop_bill_info_master "
	           					+ "WHERE "
	           					+ "bill_number = ? "
	           					+ "AND "
	           					+ "shop_id = ? "
	           					+ "AND "
	           					+ "shop_code = ?"
								+ "AND "
		       					+ "(bill_date BETWEEN STR_TO_DATE(?, '%d-%m-%Y') AND STR_TO_DATE(?, '%d-%m-%Y')) ";
	       				preparedStatement = connection.prepareStatement(sql);
	       				preparedStatement.setString(1, this.getBillNumber());
		       			preparedStatement.setString(2, shopId);
		       			preparedStatement.setString(3, shopCode);
		       			preparedStatement.setString(4, this.getFromDate());
		       			preparedStatement.setString(5, this.getToDate());
		       			
					}
					else if(this.getCustomerName() != null && this.getCustomerName().length() > 0)
					{
	       				sql = "SELECT "	           					
	           					+ "bill_number,"
	           					+ "customer_id,"
	           					+ "customer_name,"
	           					+ "transportation_charges,"
	           					+ "DATE_FORMAT(bill_date, '%d/%m/%Y'),"
	           					+ "bill_type,"
	           					+ "comments,"
	           					+ "bill_total_amount,"
	           					+ "bill_amount_received,"
	           					+ "bill_amount_retrurn,"
	           					+ "DATE_FORMAT(bill_date, '%d-%m-%Y') "
	           					+ "FROM "
	           					+ "shop_bill_info_master "
	           					+ "WHERE "		           					
	           					+ "customer_name LIKE '%"+this.getCustomerName()+"%' "
	           					+ "AND "
	           					+ "(bill_date BETWEEN STR_TO_DATE(?, '%d-%m-%Y') AND STR_TO_DATE(?, '%d-%m-%Y')) "
	           					+ "AND "
	           					+ "shop_id = ? "
	           					+ "AND "
	           					+ "shop_code = ?";
	       				preparedStatement = connection.prepareStatement(sql);
		       			preparedStatement.setString(1, this.getFromDate());
		       			preparedStatement.setString(2, this.getToDate());
		       			preparedStatement.setString(3, shopId);
		       			preparedStatement.setString(4, shopCode);
					}
					else
					{
						//System.out.println("in hello");
						sql = "SELECT "	           					
	           					+ "bill_number,"
	           					+ "customer_id,"
	           					+ "customer_name,"
	           					+ "transportation_charges,"
	           					+ "DATE_FORMAT(bill_date, '%d/%m/%Y'),"
	           					+ "bill_type,"
	           					+ "comments,"
	           					+ "bill_total_amount,"
	           					+ "bill_amount_received,"
	           					+ "bill_amount_retrurn,"
	           					+ "DATE_FORMAT(bill_date, '%d-%m-%Y') "
	           					+ "FROM "
	           					+ "shop_bill_info_master "
	           					+ "WHERE "	
	           					+ "(bill_date BETWEEN STR_TO_DATE(?, '%d-%m-%Y') AND STR_TO_DATE(?, '%d-%m-%Y')) "
	           					+ "AND "
	           					+ "shop_id = ? "
	           					+ "AND "
	           					+ "shop_code = ?";
	       				preparedStatement = connection.prepareStatement(sql);
		       			preparedStatement.setString(1, this.getFromDate());
		       			preparedStatement.setString(2, this.getToDate());
		       			preparedStatement.setString(3, shopId);
		       			preparedStatement.setString(4, shopCode);
					}
	       			
	       			
       				ResultSet resultSetBillInfo = preparedStatement.executeQuery();
       				float floatTotalAmt = 0.0F;
       				float floatAmtReceived = 0.0F;
       				float floatBalanceAmt = 0.0F;
       				
       				while(resultSetBillInfo.next())
       				{
       					CustomerAccountDetailsDAO accountDetailsDAO = new CustomerAccountDetailsDAO();
		       				
   		       		
   		       				
   		       				accountDetailsDAO.setBillNumber(resultSetBillInfo.getString(1));
   		       				accountDetailsDAO.setCustId(resultSetBillInfo.getString(2));
   		       				try
   		       				{
   		       				accountDetailsDAO.setCustName(resultSetBillInfo.getString(3).split("-")[1]);
   		       				}catch(Exception e)
   		       				{
   		       				accountDetailsDAO.setCustName(resultSetBillInfo.getString(3));
   	   		       			
   		       				}
   		       				accountDetailsDAO.setDateAsString(resultSetBillInfo.getString(5));
   		       				accountDetailsDAO.setBillType(resultSetBillInfo.getString(6));
   		       				accountDetailsDAO.setComments(resultSetBillInfo.getString(7));
   		       				accountDetailsDAO.setBillAmount(resultSetBillInfo.getString(8));
   		       				accountDetailsDAO.setDateAsStringType2(resultSetBillInfo.getString(11));
   		       				
   		       				
   		       			try
   		       		{
   		       		    floatTotalAmt = Float.parseFloat(resultSetBillInfo.getString(8));
   		       		    floatAmtReceived = Float.parseFloat(resultSetBillInfo.getString(9));

   		       		    // Credit Amount = Bill Amount - Amount Received
   		       		    floatBalanceAmt = floatTotalAmt - floatAmtReceived;

   		       		    // Do not allow negative credit
   		       		    if(floatBalanceAmt < 0)
   		       		    {
   		       		        floatBalanceAmt = 0.0F;
   		       		    }

   		       		    accountDetailsDAO.setBillAmount(resultSetBillInfo.getString(8));
   		       		    accountDetailsDAO.setAmountPaid(resultSetBillInfo.getString(9));
   		       		    accountDetailsDAO.setBalanceAmount(String.valueOf(floatBalanceAmt));

   		       		    // Calculate totals
   		       		    totalBillAmount += floatTotalAmt;
   		       		    totalAmountReceived += floatAmtReceived;
   		       		    totalCreditAmount += floatBalanceAmt;

   		       		}
   		       		catch(NumberFormatException nfe)
   		       		{
   		       		    result = "error";
   		       		    addActionError(
   		       		        "Error While Formatting while calculating credit amount! "
   		       		        + nfe.getMessage()
   		       		    );
   		       		}	       		       				
   		       			this.getCustomerAccountDetailsList().add(accountDetailsDAO);
       				}
       				
       				if(this.getCustomerAccountDetailsList().size() == 0)
       				{
       					addActionError("Details Not Found!");
       				}
       				
       				result = SUCCESS;
	    			
	    		}
	    		 catch (SQLException e) {
		    			e.printStackTrace();
		    			result = "error";
		    			
		    			addActionError(e.getErrorCode()+" || "+e.getMessage() +" || Something Went Wrong!");
		    		} catch (IOException e) {
		    			result = "error";
		    			e.printStackTrace();
		    			addActionError(e.getMessage()+" || "+e.getCause()+" || Something Went Wrong!");
		    		} 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(this.getFromDate().equals("") && this.getToDate().equals(""))
		{
			addActionError("Please select dates!");
		}
	}
	public HttpSession getSession() {
		return session;
	}

	public void setSession(HttpSession session) {
		this.session = session;
	}

	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 getCustomerName() {
		return customerName;
	}

	public void setCustomerName(String customerName) {
		this.customerName = customerName;
	}

	public static long getSerialversionuid() {
		return serialVersionUID;
	}

	public List<CustomerAccountDetailsDAO> getCustomerAccountDetailsList() {
		return customerAccountDetailsList;
	}

	public void setCustomerAccountDetailsList(List<CustomerAccountDetailsDAO> customerAccountDetailsList) {
		this.customerAccountDetailsList = customerAccountDetailsList;
	}

	public String getBillNumber() {
		return billNumber;
	}

	public void setBillNumber(String billNumber) {
		this.billNumber = billNumber;
	}

	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;  
	}

	public float getTotalBillAmount() {
		return totalBillAmount;
	}

	public void setTotalBillAmount(float totalBillAmount) {
		this.totalBillAmount = totalBillAmount;
	}

	public float getTotalAmountReceived() {
		return totalAmountReceived;
	}

	public void setTotalAmountReceived(float totalAmountReceived) {
		this.totalAmountReceived = totalAmountReceived;
	}

	public float getTotalCreditAmount() {
		return totalCreditAmount;
	}

	public void setTotalCreditAmount(float totalCreditAmount) {
		this.totalCreditAmount = totalCreditAmount;
	}
	
	
	
}
