package com.reallinkbilling.report;

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.PurchaseDateWiseReportDAO;
import com.reallinkbilling.db.GetDBConnection;

public class GetPurchaseReport extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = -8510696889700245213L;

	private String fromDate;
	private String toDate;
	private HttpSession session;
	private String supplierName;
	
	private double totalBillingAmount;
	private double totalPaidAmount;
	private double totalCreditAmount;
	
	private SessionMap<String,Object> sessionMap;  
	
	private List<PurchaseDateWiseReportDAO> purchaseList = new ArrayList<PurchaseDateWiseReportDAO>();
	
	public String execute(){
		String result = "login";
		session=ServletActionContext.getRequest().getSession(false); 
		 
	        if(session==null || session.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);
	    			
	    			
	    			if(this.getSupplierName().equals(""))
	    			{
	    			
	    			sql = "SELECT "
	    					+ "purchase_number,"
	    					+ "purchase_invoice_number,"
	    					+ "DATE_FORMAT(purchase_date,'%d-%m-%Y'),"
	    					+ "purchase_time,"
	    					+ "payment_type,"
	    					+ "supplier_id,"
	    					+ "supplier_name,"
	    					+ "transportation_charges,"
	    					+ "other_expenses,"
	    					+ "comments,"
	    					+ "total_purchase_amount,"
	    					+ "purchase_amount_paid,"
	    					+ "purchase_cgst_amount,"
	    					+ "purchase_scgst_amount,"
	    					+ "purchase_igst_amount,"
	    					+ "purchase_net_amount,"
	    					+ "entry_by,"
	    					+ "invoice_pic,"
	    					+ "purchased_by "
	    					+ "FROM "
	    					+ "shop_purchase_info_master "
	    					+ "WHERE "
	    					+ "shop_id = ? "
	    					+ "AND "
	    					+ "shop_code = ? "
	    					+ "AND "
	    					+ "(purchase_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());
	    			
	    			}else
	    			{
	    				sql = "SELECT "
		    					+ "purchase_number,"
		    					+ "purchase_invoice_number,"
		    					+ "DATE_FORMAT(purchase_date,'%d-%m-%Y'),"
		    					+ "purchase_time,"
		    					+ "payment_type,"
		    					+ "supplier_id,"
		    					+ "supplier_name,"
		    					+ "transportation_charges,"
		    					+ "other_expenses,"
		    					+ "comments,"
		    					+ "total_purchase_amount,"
		    					+ "purchase_amount_paid,"
		    					+ "purchase_cgst_amount,"
		    					+ "purchase_scgst_amount,"
		    					+ "purchase_igst_amount,"
		    					+ "purchase_net_amount,"
		    					+ "entry_by,"
		    					+ "invoice_pic,"
		    					+ "purchased_by "
		    					+ "FROM "
		    					+ "shop_purchase_info_master "
		    					+ "WHERE "
		    					+ "supplier_name LIKE '%"+this.getSupplierName().trim()+"%' "
		    					+ "AND "
		    					+ "shop_id = ? "
		    					+ "AND "
		    					+ "shop_code = ? "
		    					+ "AND "
		    					+ "(purchase_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());
		    			
		    			System.out.print(preparedStatement.toString());
		    			
	    			}
	    			
	    			
	    		
	    			
	    			resultSet = preparedStatement.executeQuery();
	    			
	    			int rowCount = 0;
	    			
	    			resultSet.last();
	    			rowCount = resultSet.getRow();
	    			resultSet.beforeFirst();
	    			
	    			if(rowCount > 0 )
	    			{
	    				
	    				totalBillingAmount = 0.0;
	    				totalPaidAmount = 0.0;
	    				totalCreditAmount = 0.0;
	    				
	    				while(resultSet.next())
	    				{
	    					PurchaseDateWiseReportDAO dao = new PurchaseDateWiseReportDAO();
	    					dao.setPurchaseNumber(resultSet.getString(1));
	    					dao.setInvoiceNumber(resultSet.getString(2));
	    					dao.setPurchaseDate(resultSet.getString(3));
	    					dao.setPaymentType(resultSet.getString(5));
	    					dao.setSupplierId(resultSet.getString(6));
	    					dao.setSupplierName(resultSet.getString(7));
	    					dao.setTransportChages(resultSet.getString(8));
	    					dao.setOtherExpenses(resultSet.getString(9));
	    					dao.setComments(resultSet.getString(10));
	    					
	    					
	    					dao.setTotalPurchaseAmount(resultSet.getString(11));
	    					dao.setTotalPurchaseAmountPaid(resultSet.getString(12));

	    					double totalPurchaseAmount = 0.0;
	    					double totalPurchaseAmountPaid = 0.0;
	    					double creditAmount = 0.0;

	    					try {
	    					    totalPurchaseAmount = Double.parseDouble(resultSet.getString(11));
	    					} catch (Exception e) {
	    					    totalPurchaseAmount = 0.0;
	    					}

	    					try {
	    					    totalPurchaseAmountPaid = Double.parseDouble(resultSet.getString(12));
	    					} catch (Exception e) {
	    					    totalPurchaseAmountPaid = 0.0;
	    					}

	    					creditAmount = totalPurchaseAmount - totalPurchaseAmountPaid;

	    					if (creditAmount < 0) {
	    					    creditAmount = 0.0;
	    					}

	    					dao.setCreditAmount(String.format("%.1f", creditAmount));
	    					
	    					totalBillingAmount += totalPurchaseAmount;
	    					totalPaidAmount += totalPurchaseAmountPaid;
	    					totalCreditAmount += creditAmount;

	    					dao.setTotalCGST(resultSet.getString(13));
	    					
	    					
	    					dao.setTotalSGST(resultSet.getString(14));
	    					dao.setTotalIGST(resultSet.getString(15));
	    					dao.setTotalNettAmount(resultSet.getString(16));
	    					dao.setEntryBy(resultSet.getString(17));
	    					dao.setPurchaseBy(resultSet.getString(19));
	    					
	    					if(resultSet.getBlob(18) == null)
	    					{
	    					    dao.setInvoiceImageAvailable(false);
	    					}
	    					else
	    					{
	    					    dao.setInvoiceImageAvailable(true);
	    					}
	    					this.getPurchaseList().add(dao);
	    					
	    					
	    					
	    				}
	    				result = "success";
	    			}
	    			else
	    			{
	    				result = "error";
	    				addActionError("Details not found for information provided!");
	    			}
	    			
	    			
	    		} catch (SQLException e) {
	    			//e.printStackTrace();
	    			result = "error";
	    			
	    			addActionError(e.getErrorCode()+" || "+e.getMessage() +" || Something Went Wrong!");
	    		} catch (Exception e) {
	    			result = "error";
	    			//e.printStackTrace();
	    			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;
	}


	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 HttpSession getSession() {
		return session;
	}


	public void setSession(HttpSession session) {
		this.session = session;
	}


	public static long getSerialversionuid() {
		return serialVersionUID;
	}


	public String getSupplierName() {
		return supplierName;
	}


	public void setSupplierName(String supplierName) {
		this.supplierName = supplierName;
	}


	public List<PurchaseDateWiseReportDAO> getPurchaseList() {
		return purchaseList;
	}


	public void setPurchaseList(List<PurchaseDateWiseReportDAO> purchaseList) {
		this.purchaseList = purchaseList;
	}
	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 double getTotalBillingAmount() {
		return totalBillingAmount;
	}


	public void setTotalBillingAmount(double totalBillingAmount) {
		this.totalBillingAmount = totalBillingAmount;
	}


	public double getTotalPaidAmount() {
		return totalPaidAmount;
	}


	public void setTotalPaidAmount(double totalPaidAmount) {
		this.totalPaidAmount = totalPaidAmount;
	}


	public double getTotalCreditAmount() {
		return totalCreditAmount;
	}


	public void setTotalCreditAmount(double totalCreditAmount) {
		this.totalCreditAmount = totalCreditAmount;
	}
	
	
	        
}
