package com.reallinkbilling.jspRedirect;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
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.ItemWiseStockDetailsDAO;
import com.reallinkbilling.db.GetDBConnection;

public class ItemWiseStockReportJsp extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = 873615081827050453L;
	private SessionMap<String,Object> sessionMap;  
	private HttpSession session;
	private List<ItemWiseStockDetailsDAO> productStockList = new ArrayList<ItemWiseStockDetailsDAO>();
	private String totalPurchaseValue;
	private String totalCustSaleValue;
	private String totalDealerSaleValue;
	private String totalRetailSaleValue;
	private boolean addQuantity2;
	private String stockType = "ALL";
	
	public String execute() {
		 
		String result = "login";
		session=ServletActionContext.getRequest().getSession(false); 
		
	        if(session==null || session.getAttribute("login")==null){ 
	        	addActionMessage("Please Logout and Login again !");
	        	result = "error";
	        }  
	        else{
	        	

	        	HashMap<String, String> settings = (HashMap<String, String>) this.getSessionMap().get("shop_settings");
	        
	        	if(settings.get("add_quntity2").equals("TRUE"))
	        	{
	        		this.setAddQuantity2(true);
	        	}
	        	else
	        	{
	        		this.setAddQuantity2(false);
	        	}
	        	
	        	DecimalFormat df = new DecimalFormat("##.##");
	        	float totalCustSaleVal = 0.0F;
	        	float totalPurchaseVal = 0.0F;
	        	float totalDealerSaleVal = 0.0F;
	        	float totalRetailSaleVal = 0.0F;
	        	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 "
					        + "product_name_eng,"
					        + "product_name_mar,"
					        + "product_alias,"
					        + "low_stock_count,"
					        + "in_stock_quantity,"
					        + "in_stock_quantity_2,"
					        + "purchase_rate,"
					        + "customer_sales_price,"
					        + "dealer_sales_price,"
					        + "retailer_sales_price,"
					        + "stock_conversion_ratio "
					        + "FROM shop_product_master "
					        + "WHERE shop_id = ? "
					        + "AND shop_code = ? ";


if ("AVAILABLE STOCK".equalsIgnoreCase(stockType)) {
    sql += "AND in_stock_quantity > 0 ";
} else if ("ZERO STOCK".equalsIgnoreCase(stockType)) {
    sql += "AND in_stock_quantity = 0 ";
}


					sql += "ORDER BY product_name_eng";

					//System.out.println("Selected Stock Type: " + stockType);
					//System.out.println("Stock SQL: " + sql);

					preparedStatement = connection.prepareStatement(sql);
					preparedStatement.setString(1, shopId);
					preparedStatement.setString(2, shopCode);

					resultSet = preparedStatement.executeQuery();
					
					
					
					while (resultSet.next()) {

					    ItemWiseStockDetailsDAO detailsDAO =
					            new ItemWiseStockDetailsDAO();

					    float inStockQty = resultSet.getFloat(5);
					    float stockConversionRatio = resultSet.getFloat(11);
					    float purchaseRate = resultSet.getFloat(7);

					    float purchaseValue = 0.0F;

					    if (stockConversionRatio != 0) {
					        purchaseValue =
					                (inStockQty / stockConversionRatio) * purchaseRate;
					    }

					    detailsDAO.setProductNameEn(resultSet.getString(1));
					    detailsDAO.setProductNameMr(resultSet.getString(2));
					    detailsDAO.setAlias(resultSet.getString(3));
					    detailsDAO.setLowStockCount(resultSet.getString(4));
					    detailsDAO.setStock(df.format(inStockQty));
					    detailsDAO.setStock2(df.format(resultSet.getFloat(6)));
					    detailsDAO.setPurchaseValue(df.format(purchaseValue));
					    detailsDAO.setCustSaleValue(
					            df.format(inStockQty * resultSet.getFloat(8)));
					    detailsDAO.setDealerSaleValue(
					            df.format(inStockQty * resultSet.getFloat(9)));
					    detailsDAO.setRetailerSaleValue(
					            df.format(inStockQty * resultSet.getFloat(10)));

					    totalPurchaseVal += purchaseValue;
					    totalCustSaleVal += inStockQty * resultSet.getFloat(8);
					    totalDealerSaleVal += inStockQty * resultSet.getFloat(9);
					    totalRetailSaleVal += inStockQty * resultSet.getFloat(10);

					    productStockList.add(detailsDAO);
					}
					
					this.setTotalCustSaleValue(df.format(totalCustSaleVal));
					this.setTotalPurchaseValue(df.format(totalPurchaseVal));
					this.setTotalDealerSaleValue(df.format(totalDealerSaleVal));
					this.setTotalRetailSaleValue(df.format(totalRetailSaleVal));
					
					result = SUCCESS;
					
				}
				catch (SQLException e) {
					
						result = ERROR;
						addActionError(e.getErrorCode()+" || "+e.getMessage() +" || Cannot load page !");

					
				} catch (Exception e) {
					result = ERROR;
					
					addActionError(e.getMessage()+" || "+e.getCause()+" || Cannot load page !!!");
				}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;
	}
	
	
	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 HttpSession getSession() {
		return session;
	}


	public void setSession(HttpSession session) {
		this.session = session;
	}


	public List<ItemWiseStockDetailsDAO> getProductStockList() {
		return productStockList;
	}


	public void setProductStockList(List<ItemWiseStockDetailsDAO> productStockList) {
		this.productStockList = productStockList;
	}


	public static long getSerialversionuid() {
		return serialVersionUID;
	}


	public String getTotalPurchaseValue() {
		return totalPurchaseValue;
	}


	public void setTotalPurchaseValue(String totalPurchaseValue) {
		this.totalPurchaseValue = totalPurchaseValue;
	}


	public String getTotalCustSaleValue() {
		return totalCustSaleValue;
	}


	public void setTotalCustSaleValue(String totalCustSaleValue) {
		this.totalCustSaleValue = totalCustSaleValue;
	}


	public String getTotalDealerSaleValue() {
		return totalDealerSaleValue;
	}


	public void setTotalDealerSaleValue(String totalDealerSaleValue) {
		this.totalDealerSaleValue = totalDealerSaleValue;
	}


	public String getTotalRetailSaleValue() {
		return totalRetailSaleValue;
	}


	public void setTotalRetailSaleValue(String totalRetailSaleValue) {
		this.totalRetailSaleValue = totalRetailSaleValue;
	}


	public boolean isAddQuantity2() {
		return addQuantity2;
	}


	public void setAddQuantity2(boolean addQuantity2) {
		this.addQuantity2 = addQuantity2;
	}


	public String getStockType() {
		return stockType;
	}


	public void setStockType(String stockType) {
		this.stockType = stockType;
	}

	
	
	
}
