/*
 * GeneralReports.java
 *
 * Created on 7 de agosto de 2005, 06:21 PM
 */

package com.fenalco.entities;

import java.sql.Date;
import java.sql.Time;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Vector;

import com.fenalco.utilities.database.QueryException;
import com.fenalco.utilities.database.SQLUtilities;

/**
 * 
 * @author Juan Ernesto Peña Kaltekis
 */
public class GeneralReports{

    
    private Vector reportList;
    
    /** Creates a new instance of GeneralReports */
    public GeneralReports(){
    }

    public Vector reportBuilder(int companyType, int companyId, int storeId, Date startDate, Date endDate, 
            int countryId, int provinceId, int cityId, int zoneId, int neighbourhoodId, int superOffer, int category, int shoppingMethod){

        Vector result = new Vector();
        Hashtable aux = new Hashtable();
        Vector reportList = new Vector();
        Vector row = new Vector();

        String select = "SELECT DISTINCT(pedido.pedi_id), pedi_fecha, pedi_hora ";
        String from = "FROM pedido, establecimiento, minorista, proveedor, pais, departamento, ciudad, zona, barrio  ";
        String where = "WHERE pedido.esta_id=establecimiento.esta_id " +
                        "AND establecimiento.empr_id=minorista.empr_id " +
                        "AND pedido.empr_id=proveedor.empr_id " +
                        "AND establecimiento.barr_id=barrio.barr_id " +
                        "AND barrio.zona_id=zona.zona_id " +
                        "AND zona.ciud_id=ciudad.ciud_id " +
                        "AND ciudad.depa_id=departamento.depa_id " +
                        "AND departamento.pais_id=pais.pais_id ";
        String group = "";
        String orderBy = "ORDER BY pedi_fecha DESC, pedi_hora DESC ";
        String sql = "";

        
        if(companyType==1 && companyId!=-1){
            from = from.concat(", empresa ");
            where = where.concat("AND minorista.empr_id=empresa.empr_id AND empresa.empr_id="+companyId+" ");
            
            if(storeId!=-1) where = where.concat("AND establecimiento.esta_id="+storeId+" ");
            
        }

        if(companyType==2 && companyId!=-1){
            from = from.concat(", empresa ");
            where = where.concat("AND proveedor.empr_id=empresa.empr_id AND empresa.empr_id="+companyId+" ");
        }
       
        
        
        if( startDate.getTime() > 0 && endDate.getTime() > 0){
            where = where.concat("AND (pedido.pedi_fecha BETWEEN \'"+startDate+"\' AND \'"+endDate+"\') ");
        }else if( startDate.getTime() > 0 ){
            where = where.concat("AND pedido.pedi_fecha >= \'"+startDate+"\' ");
        }else if( endDate.getTime() > 0 ){
            where = where.concat("AND pedido.pedi_fecha <= \'"+endDate+"\' ");
        }

        if(countryId!=-1) where = where.concat("AND pais.pais_id="+countryId+" ");

        if(provinceId!=-1) where = where.concat("AND departamento.depa_id="+provinceId+" ");
        
        if(cityId!=-1) where = where.concat("AND ciudad.ciud_id="+cityId+" ");
        
        if(zoneId!=-1) where = where.concat("AND zona.zona_id="+zoneId+" ");
        
        if(neighbourhoodId!=-1) where = where.concat("AND barrio.barr_id="+neighbourhoodId+" ");

        if(superOffer==1) where = where.concat("AND pedido.pedi_superoferta<>0 ");

         //Categoría Producto
         if( category != -1  ){
             select = select.concat(",categoria_producto.cate_prod_nombre ");
             from = from.concat(", producto_pedido, producto_proveedor, categoria_producto ");
             where = where.concat("AND producto_pedido.pedi_id = pedido.pedi_id " +
                      "AND producto_pedido.prod_prov_id = producto_proveedor.prod_prov_id " +
                      "AND producto_proveedor.cate_prod_codigo = categoria_producto.cate_prod_codigo " +
                      "AND categoria_producto.cate_prod_codigo="+ category +" ");
         }
        //Forma de compra
        
        if(shoppingMethod != -1){
            where = where.concat("AND pedido.orig_pedi_id="+shoppingMethod+" ");
            
        }
        
        sql = sql.concat(select + from + where +group + orderBy + ";");


        try{
            System.out.println( "SQL de GeneralReports--> " + sql );

            result = SQLUtilities.query(sql);
            System.out.println("Size->"+result.size());
            
            
            if( result!= null && result.size() > 0 ){
                
                for( int i=0; i<result.size(); i++){
                    aux = ( Hashtable )result.elementAt( i );
                    int orderId = new Integer( aux.get( "pedi_id" ).toString() ).intValue();
                    Order order = new Order( orderId );
                    reportList.addElement( order );
                }
                
            }
            

        }catch(QueryException qe){
            System.out.println("Query Error "+qe);
        }

        this.reportList = reportList;
        return reportList;

    }
    
    public Vector getStats( int categoryId ){
    
        Vector stats = new Vector();
        Enumeration en; 
        Order order = new Order();
        
        int numOrders = 0;
        double total = 0;
        double totalDiscount = 0;
        
        double avgTotal = 0;
        double avgTotalDiscount = 0;
        
        double avgDiscount = 0;
        
        en = this.reportList.elements();
        while( en.hasMoreElements() ){
            order = (Order)en.nextElement();
            numOrders++;
            if( categoryId != -1 ){
                total += order.getTotalByCategory( categoryId ); 
            } else{
                total += order.getValue();
                totalDiscount += (order.getValue() - order.getDiscount() );
            }
        }
        
        avgTotal = total / numOrders;
        avgTotalDiscount = totalDiscount / numOrders;
        
        if( total > 0 ){
            avgDiscount = (( total - totalDiscount )/total)*100;
        }
        
        float avgTotalDiscountF = (float)avgTotalDiscount;
        
        stats.add( new Integer( numOrders ) );
        stats.add( new Double( total ) );
        stats.add( new Double( avgTotal ) );
        stats.add( new Double( totalDiscount ) );
        stats.add( new Double( avgTotalDiscount ) );
        stats.add( new Double( avgDiscount ) );
        
        return stats;
        
    }

    
    
    /**
     * Getter for property reportList.
     * @return Value of property reportList.
     */
    public java.util.Vector getReportList() {
        return reportList;
    }
    
    /**
     * Setter for property reportList.
     * @param reportList New value of property reportList.
     */
    public void setReportList(java.util.Vector reportList) {
        this.reportList = reportList;
    }
    
}