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.text.DecimalFormat;
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.reallinkbilling.DAO.ItemwisePurchaseReportDAO;
import com.reallinkbilling.db.GetDBConnection;
import com.opensymphony.xwork2.ActionSupport;

public class ItemWisePurchaseReport extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = -835363253445318549L;

	private SessionMap<String,Object> sessionMap; 
	private HttpSession session;
	private String fromDate;
	private String toDate;
	private List<ItemwisePurchaseReportDAO> itemWisePurchaseList = new  ArrayList<ItemwisePurchaseReportDAO>();
	private String totalPurchaseAmount;
	
	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{
	        	
	        	Connection connection = null;
				PreparedStatement preparedStatement= null;
				ResultSet  resultSet = null;
				float totalPuchaseAmt = 0.0F;
				String sql = "";
				String db = (String) this.getSessionMap().get("db");
				
				try
				{
				
					String shopId = (String) this.getSessionMap().get("shopId");
		    		String shopCode = (String) this.getSessionMap().get("shopCode");
		    	
		    		
					connection = GetDBConnection.getDBConnection(db);
					
					
					sql = "SELECT "
							//+ "spim.purchase_date,"
							+ "sppim.product_name,"
							+ "sppim.purchase_unit,"
							+ "SUM(sppim.puroduct_quantity),"
							+ "SUM(sppim.product_total_amount),"							
							+ "SUM(sppim.product_net_amount),"
							+ "SUM(sppim.product_igst_amount),"
							+ "SUM(sppim.product_cgst_amount),"
							+ "SUM(sppim.product_sgst_amount) "
							+ "FROM "
							+ "shop_purchase_info_master AS spim "
							+ "INNER JOIN "
							+ "shop_purchase_product_info_master AS sppim "
							+ "ON "
							+ "spim.purchase_number = sppim.purchase_number "
							+ "WHERE "
							+ "spim.shop_id = ? "
							+ "AND "
							+ "spim.shop_code = ? "
							+ "AND "
							+ "sppim.shop_id = ? "
							+ "AND "
							+ "sppim.shop_code = ? "
							+ "AND "						
							+ "(spim.purchase_date "
							+ "BETWEEN STR_TO_DATE(?,'%d-%m-%Y') "
							+ "AND "
							+ "STR_TO_DATE(?,'%d-%m-%Y')) "
							+ "GROUP BY "
							+ "sppim.purchase_unit,sppim.product_name";
					
					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());
					
					resultSet = preparedStatement.executeQuery();
					DecimalFormat df = new DecimalFormat();
					df.setMaximumFractionDigits(2);
					while(resultSet.next())
					{
						ItemwisePurchaseReportDAO purchaseReportDAO =  new ItemwisePurchaseReportDAO();
						purchaseReportDAO.setProductName(resultSet.getString(1));
						purchaseReportDAO.setUnits(resultSet.getString(2));
						purchaseReportDAO.setQuantity(df.format(resultSet.getFloat(3)));
						purchaseReportDAO.setTotalAmount(df.format(resultSet.getFloat(4)));
						totalPuchaseAmt = totalPuchaseAmt + resultSet.getFloat(4);
						purchaseReportDAO.setRate(resultSet.getFloat(4)/resultSet.getFloat(3));
						this.getItemWisePurchaseList().add(purchaseReportDAO);	
						
						System.out.println("product = "+resultSet.getString(1));
						System.out.println("product = "+resultSet.getString(2));
						System.out.println("product = "+resultSet.getString(3));
						System.out.println("product = "+resultSet.getString(4));
						
					}
					
					this.setTotalPurchaseAmount(df.format(totalPuchaseAmt));
					result = "success";
					
			}catch (SQLException e) 
			{		
					result = "error";
					addActionError(e.getErrorCode()+" || "+e.getMessage() +" || Purchase details could not be found !");
			
			}catch (IOException e) {
				result = "error";
				addActionError(e.getMessage() +" || Purchase details could not be found !");
		
			}
			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 provide dates!");
		}
	}

	public HttpSession getSession() {
		return session;
	}


	public void setSession(HttpSession session) {
		this.session = session;
	}


	public static long getSerialversionuid() {
		return serialVersionUID;
	}


	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 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<ItemwisePurchaseReportDAO> getItemWisePurchaseList() {
		return itemWisePurchaseList;
	}

	public void setItemWisePurchaseList(List<ItemwisePurchaseReportDAO> itemWisePurchaseList) {
		this.itemWisePurchaseList = itemWisePurchaseList;
	}

	public String getTotalPurchaseAmount() {
		return totalPurchaseAmount;
	}

	public void setTotalPurchaseAmount(String totalPurchaseAmount) {
		this.totalPurchaseAmount = totalPurchaseAmount;
	}


	
}
