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.LinkedHashMap;
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.ProductionDetailsDAO;
import com.reallinkbilling.cipher.EncryptionDecryption;
import com.reallinkbilling.db.GetDBConnection;
import com.reallinkbilling.utils.LabelCapSchema;
import com.reallinkbilling.utils.PackingMasterOptions;

public class ProductionDoneDetailsReport extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = -3660930169106106327L;
	private HttpSession session;
	private String fromDate;
	private String toDate;
	private List<ProductionDetailsDAO> productionDetailsList = new ArrayList<ProductionDetailsDAO>();
	private SessionMap<String,Object> sessionMap; 
	
	private Map<String,String> finishedProductList = new LinkedHashMap<String,String>();

	private String finishedProductId;
	
	private Float totalQuantity;
	
	
	public String execute(){
		String result = "login";
		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);
					LabelCapSchema.ensure(this.getSessionMap());
					
					if(this.getFinishedProductId().equals("ALL"))
					{
						sql = "SELECT "
								+ "spdm.production_id,"
								+ "spm.product_name_eng,"
								+ "spdm.finished_product_id,"
								+ "DATE_FORMAT(spdm.production_date,'%d-%m-%Y'),"
								+ "spdm.production_time,"
								+ "spdm.production_quantity,"
								+ "spdm.batch_id,"
								+ "spdm.shift,"
								+ "spdm.production_by,"
								+ "spdm.production_details,"
								+ "lm.label_name,"
								+ "lm.label_code,"
								+ "cm.cap_name,"
								+ "cm.cap_code,"
								+ "produced.product_name_eng "
								+ "FROM "
								+ "shop_production_details_master AS spdm "
								+ "INNER JOIN "
								+ "shop_product_master as spm "
								+ "ON "
								+ "spdm.finished_product_id = spm.product_id "
								+ "LEFT JOIN "
								+ "shop_label_master AS lm "
								+ "ON "
								+ "spdm.label_id = lm.label_id "
								+ "AND "
								+ "spdm.shop_id = lm.shop_id "
								+ "AND "
								+ "spdm.shop_code = lm.shop_code "
								+ "LEFT JOIN "
								+ "shop_cap_master AS cm "
								+ "ON "
								+ "spdm.cap_id = cm.cap_id "
								+ "AND "
								+ "spdm.shop_id = cm.shop_id "
								+ "AND "
								+ "spdm.shop_code = cm.shop_code "
								+ "LEFT JOIN "
								+ "shop_product_master AS produced "
								+ "ON "
								+ "spdm.product_id = produced.product_id "
								+ "AND "
								+ "produced.shop_id = spdm.shop_id "
								+ "AND "
								+ "produced.shop_code = spdm.shop_code "
								+ "WHERE "
								+ "spdm.shop_id = ? "
								+ "AND "
								+ "spdm.shop_code = ? "
								+ "AND "
								+ "spm.shop_id = ? "
								+ "AND "
								+ "spm.shop_code = ? "
								+ "AND "
								+ "(spdm.production_date "
								+ "BETWEEN STR_TO_DATE(?,'%d-%m-%Y') AND STR_TO_DATE(?,'%d-%m-%Y')) "
								+ "ORDER BY "
								+ "spdm.production_date";
						
						preparedStatement = connection.prepareStatement(sql);
						
						preparedStatement.setString(1, shopId);
						preparedStatement.setString(2, shopCode);
						preparedStatement.setString(3, shopId);
						preparedStatement.setString(4, shopCode);
						preparedStatement.setString(5, this.getFromDate());
						preparedStatement.setString(6, this.getToDate());
					}
					else
					{
						sql = "SELECT "
								+ "spdm.production_id,"
								+ "spm.product_name_eng,"
								+ "spdm.finished_product_id,"
								+ "DATE_FORMAT(spdm.production_date,'%d-%m-%Y'),"
								+ "spdm.production_time,"
								+ "spdm.production_quantity,"
								+ "spdm.batch_id,"
								+ "spdm.shift,"
								+ "spdm.production_by,"
								+ "spdm.production_details,"
								+ "lm.label_name,"
								+ "lm.label_code,"
								+ "cm.cap_name,"
								+ "cm.cap_code,"
								+ "produced.product_name_eng "
								+ "FROM "
								+ "shop_production_details_master AS spdm "
								+ "INNER JOIN "
								+ "shop_product_master as spm "
								+ "ON "
								+ "spdm.finished_product_id = spm.product_id "
								+ "LEFT JOIN "
								+ "shop_label_master AS lm "
								+ "ON "
								+ "spdm.label_id = lm.label_id "
								+ "AND "
								+ "spdm.shop_id = lm.shop_id "
								+ "AND "
								+ "spdm.shop_code = lm.shop_code "
								+ "LEFT JOIN "
								+ "shop_cap_master AS cm "
								+ "ON "
								+ "spdm.cap_id = cm.cap_id "
								+ "AND "
								+ "spdm.shop_id = cm.shop_id "
								+ "AND "
								+ "spdm.shop_code = cm.shop_code "
								+ "LEFT JOIN "
								+ "shop_product_master AS produced "
								+ "ON "
								+ "spdm.product_id = produced.product_id "
								+ "AND "
								+ "produced.shop_id = spdm.shop_id "
								+ "AND "
								+ "produced.shop_code = spdm.shop_code "
								+ "WHERE "
								+ "spdm.shop_id = ? "
								+ "AND "
								+ "spdm.shop_code = ? "
								+ "AND "
								+ "spm.shop_id = ? "
								+ "AND "
								+ "spm.shop_code = ? "
								+ "AND "
								+ "(spdm.production_date "
								+ "BETWEEN STR_TO_DATE(?,'%d-%m-%Y') AND STR_TO_DATE(?,'%d-%m-%Y')) "
								+ "AND "
								+ "spdm.finished_product_id = ? "
								+ "ORDER BY "
								+ "spdm.production_date";
						
						preparedStatement = connection.prepareStatement(sql);
						
						preparedStatement.setString(1, shopId);
						preparedStatement.setString(2, shopCode);
						preparedStatement.setString(3, shopId);
						preparedStatement.setString(4, shopCode);
						preparedStatement.setString(5, this.getFromDate());
						preparedStatement.setString(6, this.getToDate());
						preparedStatement.setString(7, this.getFinishedProductId());
					}
					
					//System.out.println(preparedStatement.toString());
					resultSet = preparedStatement.executeQuery();
					
					resultSet.last();
					int rowCount = resultSet.getRow();
					resultSet.beforeFirst();
					
					if(rowCount > 0)
					{
						
						this.setTotalQuantity(0.0F);
						
						while(resultSet.next())
						{
							ProductionDetailsDAO dao = new ProductionDetailsDAO();
							
							dao.setProductionId(resultSet.getString(1));
							dao.setFinishedProdcutName(resultSet.getString(2));
							dao.setFinishedProductId(resultSet.getString(3));
							dao.setProductionDate(resultSet.getString(4));
							dao.setProductionTime(resultSet.getString(5));
							dao.setProductionQuantity(resultSet.getString(6));
							dao.setBatchId(resultSet.getString(7));
							dao.setLabelName(PackingMasterOptions.displayName(resultSet.getString(11), resultSet.getString(12)));
							dao.setCapName(PackingMasterOptions.displayName(resultSet.getString(13), resultSet.getString(14)));
							dao.setProductName(resultSet.getString(15));
							dao.setKey(EncryptionDecryption.encrypt(resultSet.getString(1)));
							
							this.setTotalQuantity(this.getTotalQuantity()+resultSet.getFloat(6));
							this.getProductionDetailsList().add(dao);
							
						
						}
						result = SUCCESS;
					}else
					{
						result = ERROR;
						addActionError("Details not found for date specified!");
					}
					
	    		}catch (SQLException e) {
	    			e.printStackTrace();
	    			
	    			result = ERROR;
	    			
	    			addActionError(e.getErrorCode()+" || "+e.getMessage() +" || Something Went Wrong!");
	    		} catch (IOException e) {
	    			result = ERROR;
	    			
	    			addActionError(e.getMessage()+" || "+e.getCause()+" || Something Went Wrong!");
	    		} finally {
	    			if(resultSet != null)
					{
						try {
							resultSet.close();
						} catch (SQLException e) {
							
						}
					}
	    			if(preparedStatement != null)
	    			{
	    				try {preparedStatement.close();} catch (SQLException e) {}
	    			}
	    			if(connection != null)
	    			{
	    				
	    				try {connection.close();}catch (SQLException e) {}
	    			}
	    			
	    		}
	    		
	    		
        	
        }
        
        return result;
}
	@Override
	public void validate() {
		if(this.getFromDate() == null || this.getFromDate().equals(""))
		{
			addActionError("Please select from date!");
		}
		
		if(this.getToDate() == null || this.getToDate().equals(""))
		{
			addActionError("Please select to date!");
		}
		
		if(this.getFinishedProductId().equals(""))
		{
			addActionError("Please select fininshed product!");
		}
		
	 	Connection connection = null;
		PreparedStatement preparedStatement= null;
		ResultSet resultSet = null;
	
		String sql = "";
		String db = (String) this.getSessionMap().get("db");
		
		String shopId = (String) this.getSessionMap().get("shopId");
		String shopCode = (String) this.getSessionMap().get("shopCode");
     	
		
		
		try
		{
			connection = GetDBConnection.getDBConnection(db);
			
			sql = "SELECT "
					+ "finished_product_id,"
					+ "finished_product_name_eng "
					+ "FROM "
					+ "shop_finished_product_details_master "
					+ "WHERE "
					+ "shop_id = ? "
					+ "AND "
					+ "shop_code = ?";
			
			
			preparedStatement = connection.prepareStatement(sql);
			
			preparedStatement.setString(1, shopId);
			preparedStatement.setString(2, shopCode);
			
			resultSet = preparedStatement.executeQuery();
			
			this.getFinishedProductList().put("ALL", "ALL");
	    	
			
			while(resultSet.next())
			{
				this.getFinishedProductList().put(resultSet.getString(1), resultSet.getString(2));
			}
			
			
		}
		catch (SQLException e) {} catch (IOException e) {}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) {}
			}
			
		}
    	
		
	}
	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 List<ProductionDetailsDAO> getProductionDetailsList() {
		return productionDetailsList;
	}
	public void setProductionDetailsList(List<ProductionDetailsDAO> productionDetailsList) {
		this.productionDetailsList = productionDetailsList;
	}
	public static long getSerialversionuid() {
		return serialVersionUID;
	}

	public Map<String, String> getFinishedProductList() {
		return finishedProductList;
	}
	public void setFinishedProductList(Map<String, String> finishedProductList) {
		this.finishedProductList = finishedProductList;
	}
	
	
	public String getFinishedProductId() {
		return finishedProductId;
	}
	public void setFinishedProductId(String finishedProductId) {
		this.finishedProductId = finishedProductId;
	}
	
	
	public Float getTotalQuantity() {
		return totalQuantity;
	}
	public void setTotalQuantity(Float totalQuantity) {
		this.totalQuantity = totalQuantity;
	}
	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;  
	}

}
