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.ItemWiseSaleReportDAO;
import com.reallinkbilling.db.GetDBConnection;
import com.reallinkbilling.utils.GetProductDetailsUtil;
import com.opensymphony.xwork2.ActionSupport;

public class ItemWiseSaleReport extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = 5134866086809949089L;

	private SessionMap<String,Object> sessionMap; 
	private HttpSession session;
	private String fromDate;
	private String toDate;
	private List<ItemWiseSaleReportDAO> itemWiseSaleList = new  ArrayList<ItemWiseSaleReportDAO>();
	private String allBillAmount;
	private String allDiscount;
	private String allBillAmountAfterDis;
	private String allPurchaseAmount;
	
	private Float totalCashCollection;
	private Float totalCreditCollection;
	private Float totalCreeditGiven;
	private Float totalBilling;
	private Float totalCollection;
	private Float totalCreditGiven;
	
	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;
				
				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 "
							//+ "DATE_FORMAT(csd.bill_date,'%d-%m-%Y'),"
						//	+ "csd.bill_number,"
							+ "spi.product_id,"
							+ "spi.product_name,"
							+ "spi.sales_unit,"
							+ "SUM(spi.product_quantity),"
							+ "SUM(spi.product_total_amount),"
							+ "SUM(spi.discount_ruppees*spi.product_quantity),"
							+ "SUM(spi.product_total_amount_aft_discount),"
							+ "SUM(spi.product_net_amount),"
							+ "SUM(spi.product_igst_amount),"
							+ "SUM(spi.product_cgst_amount),"
							+ "SUM(spi.product_sgst_amount) "
							+ "FROM "
							+ "shop_bill_info_master AS csd "
							+ "INNER JOIN "
							+ "shop_bill_product_info_master AS spi "
							+ "ON "
							+ "csd.bill_number = spi.bill_number "
							+ "WHERE "
							+ "csd.shop_id = ? "
							+ "AND "
							+ "csd.shop_code = ?"
							+ "AND "
							+ "spi.shop_id = ? "
							+ "AND "
							+ "spi.shop_code = ? "
							+ "AND "						
							+ "(csd.bill_date "
							+ "BETWEEN STR_TO_DATE(?,'%d-%m-%Y') "
							+ "AND "
							+ "STR_TO_DATE(?,'%d-%m-%Y')) "
							+ "GROUP BY "
							+ "spi.product_id,spi.product_name,spi.sales_unit";
					
					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("##.##");
					float tBillAmt = 0.0F;
					float tDiscount = 0.0F;
					float tBillAmtAftDis = 0.0F;
					float tPurchaseAmt = 0.0F;
					while(resultSet.next())
					{
						ItemWiseSaleReportDAO saleReportDAO =  new ItemWiseSaleReportDAO();
						
						float productPurchaseRate = GetProductDetailsUtil.getProductPurchaseRateById(this.getSessionMap(), resultSet.getString(1));
						
						saleReportDAO.setProductName(resultSet.getString(2));
						saleReportDAO.setUnits(resultSet.getString(3));
						saleReportDAO.setQuantity(df.format(Float.parseFloat(resultSet.getString(4))));
						
						saleReportDAO.setTotalPurchaseAmount(df.format(productPurchaseRate*resultSet.getFloat(4)));
						
						System.out.println("Product = " + resultSet.getString(2));
						System.out.println("qty = " + resultSet.getString(4));
						System.out.println("column5 = " + resultSet.getString(5));
						System.out.println("column6 = " + resultSet.getString(6));
						System.out.println("column7 = " + resultSet.getString(7));
						
						saleReportDAO.setTotalBillAmount(resultSet.getString(5));
						saleReportDAO.setTotalDiscountAmount(resultSet.getString(6));
						saleReportDAO.setTotalBillAmountAfterDiscount(resultSet.getString(7));
						saleReportDAO.setTotalGrossAmount(resultSet.getString(8));
						saleReportDAO.setTotalIGSTAmount(resultSet.getString(9));
						saleReportDAO.setTotalCGSTAmount(resultSet.getString(10));
						saleReportDAO.setTotalSGSTAmount(resultSet.getString(11));
						
						this.getItemWiseSaleList().add(saleReportDAO);
						
						tPurchaseAmt = tPurchaseAmt + Float.parseFloat(saleReportDAO.getTotalPurchaseAmount());
						tBillAmt = tBillAmt + resultSet.getFloat(5);
						tDiscount = tDiscount + resultSet.getFloat(6);
						tBillAmtAftDis = tBillAmtAftDis + resultSet.getFloat(7);
						
					}
					
					this.setAllPurchaseAmount(df.format(tPurchaseAmt));
					this.setAllBillAmount(df.format(tBillAmt));
					this.setAllDiscount(df.format(tDiscount));
					this.setAllBillAmountAfterDis(df.format(tBillAmtAftDis));
					
					

					if(resultSet != null)
					{
						resultSet.close();
						resultSet = null;
					}
					
					if(preparedStatement != null)
					{
						preparedStatement.close();
						preparedStatement = null;
					}
					
					
					sql = "SELECT "
	    					//+ "bill_date,"
	    					+ "SUM(bill_total_amount),"
	    					+ "SUM(bill_amount_received),"
	    					+ "SUM(bill_amount_retrurn) "
	    					+ "FROM "
	    					+ "shop_bill_info_master "
	    					+ "WHERE "
	    					+ "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, shopId);
					preparedStatement.setString(2, shopCode);
					preparedStatement.setString(3, this.getFromDate());
					preparedStatement.setString(4, this.getToDate());
					
					resultSet = preparedStatement.executeQuery();
					
				
					
					this.setTotalBilling(0.0F);
					this.setTotalCashCollection(0.0F);
					this.setTotalCollection(0.0F);
					this.setTotalCreditCollection(0.0F);
					this.setTotalCreditGiven(0.0F);
					
					
	    			while(resultSet.next())	    				
	    			{
	    				
    				if(resultSet.getString(1) != null && resultSet.getString(2) != null && resultSet.getString(3) != null)
    				{
	    				this.setTotalBilling(this.getTotalBilling() + (Float.parseFloat(resultSet.getString(1))));
	    				
	    				float amtReceived = Float.parseFloat(resultSet.getString(2));
	    				float amountReturn  = Float.parseFloat(resultSet.getString(3));
	    				
	    				if(amountReturn > 0 )
	    				{
	    					this.setTotalCashCollection(this.getTotalCashCollection() + Float.parseFloat(resultSet.getString(1)));
	    				}
	    				else
	    				{
	    					this.setTotalCashCollection(this.getTotalCashCollection() + amtReceived);
	    					this.setTotalCreditGiven(this.getTotalCreditGiven()+
	    							+ (Float.parseFloat(resultSet.getString(1)) - amtReceived));
	    				}
	    				
	    				
	    			}
	    			}
	    			
	    			
	    			sql = "SELECT SUM(payment_amount) "
	    					+ "FROM "
	    					+ "shop_customer_payment_master "
	    					+ "WHERE "
	    					+ "shop_id = ? "
	    					+ "AND "
	    					+ "shop_code = ? "
	    					+ "AND "
	    					+ "(entry_date "
	    					+ "BETWEEN "
			    			+ "STR_TO_DATE(?,'%d-%m-%Y') "
							+ "AND "
							+ "STR_TO_DATE(?,'%d-%m-%Y'))";
	    			
	    			PreparedStatement ps = connection.prepareStatement(sql);
	    			
	    			ps.setString(1, shopId);
	    			ps.setString(2, shopCode);
	    			ps.setString(3, this.getFromDate());
	    			ps.setString(4, this.getToDate());
	    			ResultSet rs = ps.executeQuery();
	    			
	    			
	    			if(rs.next())
	    			{
	    				try
	    				{
    	    				if(rs.getString(1) != null)
    	    				{
    	    				
    	    					this.setTotalCreditCollection(rs.getFloat(1));
    	    				}
    	    				else
    	    				{
    	    					this.setTotalCreditCollection(0.0F);
    	    				}
	    				}catch(NumberFormatException nfe)
	    				{}
	    			}
	    			if(rs != null)
	    			{
	    				rs.close();
	    				rs = null;
	    			}
	    			if(ps != null)
	    			{
	    				ps.close();
	    				ps = null;
	    			}
	    			
	    			this.setTotalCollection(this.getTotalCreditCollection()+this.getTotalCashCollection());
					this.setAllBillAmount(df.format(tBillAmt));
					this.setAllDiscount(df.format(tDiscount));
					this.setAllBillAmountAfterDis(df.format(tBillAmtAftDis));
					result = "success";
					
			}catch (SQLException e) 
			{		
					result = "error";
					addActionError(e.getErrorCode()+" || "+e.getMessage() +" || Sale details could not be found !");
			
			}catch (IOException e) {
				result = "error";
				addActionError(e.getMessage() +" || Sale 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<ItemWiseSaleReportDAO> getItemWiseSaleList() {
		return itemWiseSaleList;
	}


	public void setItemWiseSaleList(List<ItemWiseSaleReportDAO> itemWiseSaleList) {
		this.itemWiseSaleList = itemWiseSaleList;
	}

	public String getAllBillAmount() {
		return allBillAmount;
	}

	public void setAllBillAmount(String allBillAmount) {
		this.allBillAmount = allBillAmount;
	}

	public String getAllDiscount() {
		return allDiscount;
	}

	public void setAllDiscount(String allDiscount) {
		this.allDiscount = allDiscount;
	}

	public String getAllBillAmountAfterDis() {
		return allBillAmountAfterDis;
	}

	public void setAllBillAmountAfterDis(String allBillAmountAfterDis) {
		this.allBillAmountAfterDis = allBillAmountAfterDis;
	}

	public Float getTotalCashCollection() {
		return totalCashCollection;
	}

	public void setTotalCashCollection(Float totalCashCollection) {
		this.totalCashCollection = totalCashCollection;
	}

	public Float getTotalCreditCollection() {
		return totalCreditCollection;
	}

	public void setTotalCreditCollection(Float totalCreditCollection) {
		this.totalCreditCollection = totalCreditCollection;
	}

	public Float getTotalCreeditGiven() {
		return totalCreeditGiven;
	}

	public void setTotalCreeditGiven(Float totalCreeditGiven) {
		this.totalCreeditGiven = totalCreeditGiven;
	}

	public Float getTotalBilling() {
		return totalBilling;
	}

	public void setTotalBilling(Float totalBilling) {
		this.totalBilling = totalBilling;
	}

	public Float getTotalCollection() {
		return totalCollection;
	}

	public void setTotalCollection(Float totalCollection) {
		this.totalCollection = totalCollection;
	}

	public Float getTotalCreditGiven() {
		return totalCreditGiven;
	}

	public void setTotalCreditGiven(Float totalCreditGiven) {
		this.totalCreditGiven = totalCreditGiven;
	}

	public String getAllPurchaseAmount() {
		return allPurchaseAmount;
	}

	public void setAllPurchaseAmount(String allPurchaseAmount) {
		this.allPurchaseAmount = allPurchaseAmount;
	}

	
	
	
	
}
