/*
 * ComparativeReports.java
 *
 */

package com.fenalco.entities;

import java.util.Vector;
import java.util.Hashtable;
import java.sql.Date;
import java.util.GregorianCalendar;
import java.util.Calendar;

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

/**
 *
 * @author  Néstor Romero León
 */
public class ComparativeReports {
    
    public static final int DAY_PERIOD = 1;
    
    public static final int MONTH_PERIOD = 2;
    
    public static final int YEAR_PERIOD = 3;
    
    private Vector report;
    
    private int quantity;
    
    private int total;
    
    /** Creates a new instance of ComparativeReports */
    public ComparativeReports() {
        
        report = new Vector();
        
    }
    
    public void comparativeReportsBySupplier( int countryId, int provinceId, int cityId, Date startDate, Date endDate ){
        
        Vector result = new Vector();
        Hashtable aux = new Hashtable();
        
        
        String select = "SELECT empresa.empr_nombre_comercial, count( * ) as cantidad, sum( pedido.pedi_total ) as total \n";
        
        String from = "FROM empresa, proveedor, pedido, pais, departamento, ciudad, zona, barrio, establecimiento \n";
        
        String where = "WHERE empresa.empr_id = proveedor.empr_id AND \n " +
                        "proveedor.empr_id = pedido.empr_id AND \n " +
                        "proveedor.empr_id IN ( select empr_id from proveedor ) AND \n" +
                        "pedido.esta_id = establecimiento.esta_id AND \n" +
                        "establecimiento.barr_id = barrio.barr_id AND \n" +
                        "barrio.zona_id = zona.zona_id AND \n" +
                        "zona.ciud_id = ciudad.ciud_id AND \n" +
                        "ciudad.depa_id = departamento.depa_id AND \n" +
                        "departamento.pais_id = pais.pais_id ";
        
        String groupBy = "GROUP BY  empresa.empr_nombre_comercial \n";
        
        String orderBy = "ORDER BY total DESC;";
        
        if( startDate.getTime() > 0 && endDate.getTime() > 0 ){
            
            where += "AND pedido.pedi_fecha BETWEEN \'" + startDate.toString() + "\' AND \'" + endDate.toString() + "\' \n";
            
        }
        
        if( countryId != -1 ){
            where += "AND pais.pais_id = "+ countryId+" \n";
        }
        
        if( provinceId != -1 ){
            where += "AND departamento.depa_id = "+provinceId+" \n";
        }
        
        if( cityId != -1 ){
            where += "AND ciudad.ciud_id = "+ cityId+ " \n";
        }
        
        String sql = select + from + where + groupBy + orderBy;
        
        try{
        
            result = SQLUtilities.query( sql );
            if( result.size() > 0 ){
                
                
                for( int i=0;i<result.size();i++ ){
                    Vector row = new Vector();
                    
                    aux = (Hashtable)result.elementAt( i );
                    row.addElement( aux.get( "empr_nombre_comercial" ) );
                    
                    int auxq = ((Integer)aux.get( "cantidad" )).intValue();
                    double auxt = ((Double)aux.get( "total2" )).intValue();
                    
                    this.quantity += auxq;
                    this.total += auxt;
                    
                    row.addElement( new Integer( auxq ) );
                    row.addElement( new Double( auxt ) );
                    
                    this.report.addElement( row );
                }
                
            }
            
        }catch( QueryException qe ){
        
            qe.printStackTrace();
        }
        
        System.out.println( this.quantity + " :: " +this.total );
        Vector auxReport = new Vector();
        for( int i=0; i<this.report.size();i++ ){
            
            Vector row = new Vector();
            row = (Vector)this.report.elementAt( i );
            
            int q = ((Integer)row.elementAt(1)).intValue();
            double t = ((Double)row.elementAt(2)).doubleValue();
            
            row.addElement( new Double( ((double)q / (double)this.quantity)*100 ) );
            row.addElement( new Double( ( t/this.total)*100 ) );
            
            auxReport.addElement( row );
        }
        
        this.report = auxReport;
        
    }
    
    public void comparativeReportsByCity( int countryId, int provinceId, Date startDate, Date endDate ){
    
        Vector result = new Vector();
        Hashtable aux = new Hashtable();
        
        
        String select = "SELECT ciudad.ciud_nombre, count(*) as cantidad , sum(pedido.pedi_total) as total \n";
        
        String from = "FROM pedido, establecimiento, barrio, zona, ciudad \n";
        
        String where = "WHERE pedido.esta_id = establecimiento.esta_id AND \n " +
                        "establecimiento.barr_id = barrio.barr_id AND \n " + 
                        "barrio.zona_id = zona.zona_id AND \n " + 
                        "zona.ciud_id = ciudad.ciud_id \n ";
        
        String innerQuery = "AND ciudad.ciud_id IN (SELECT ciudad.ciud_id " +
                            "FROM ciudad, departamento, pais " +
                            "WHERE ciudad.depa_id = departamento.depa_id AND \n" +
                            "departamento.pais_id = pais.pais_id ";
        
        if( countryId != -1 ){
            innerQuery += " AND pais.pais_id = " + countryId + " ";  
        }
        
        if( provinceId != -1 ){
            innerQuery += " AND departamento.depa_id = " + provinceId +" ";
        }
        
        
        String groupBy = "GROUP BY  ciudad.ciud_nombre \n";
        
        String orderBy = "ORDER BY total DESC;";
        
        if( startDate.getTime() > 0 && endDate.getTime() > 0 ){
            
            where += "AND pedido.pedi_fecha BETWEEN \'" + startDate.toString() + "\' AND \'" + endDate.toString() + "\' \n";
            
        }
        
        
        String sql = select + from + where + innerQuery + ")\n "  + groupBy + orderBy;
        
        System.out.println( sql );
        
        try{
        
            result = SQLUtilities.query( sql );
            if( result.size() > 0 ){
                
                
                for( int i=0;i<result.size();i++ ){
                    Vector row = new Vector();
                    
                    aux = (Hashtable)result.elementAt( i );
                    row.addElement( aux.get( "ciud_nombre" ) );
                    
                    int auxq = ((Integer)aux.get( "cantidad" )).intValue();
                    double auxt = ((Double)aux.get( "total2" )).intValue();
                    
                    this.quantity += auxq;
                    this.total += auxt;
                    
                    row.addElement( new Integer( auxq ) );
                    row.addElement( new Double( auxt ) );
                    
                    this.report.addElement( row );
                }
                
            }
            
        }catch( QueryException qe ){
        
            qe.printStackTrace();
        }
        
        System.out.println( this.quantity + " :: " +this.total );
        Vector auxReport = new Vector();
        for( int i=0; i<this.report.size();i++ ){
            
            Vector row = new Vector();
            row = (Vector)this.report.elementAt( i );
            
            int q = ((Integer)row.elementAt(1)).intValue();
            double t = ((Double)row.elementAt(2)).doubleValue();
            
            row.addElement( new Double( ((double)q / (double)this.quantity)*100 ) );
            row.addElement( new Double( ( t/this.total)*100 ) );
            
            auxReport.addElement( row );
        }
        
        this.report = auxReport;
        
    }
    
    public void behaviorReports( int timeFormat, int supplierId, int countryId, int provinceId, int cityId, Date startDate, Date endDate ){
    
        Vector timeTable = this.timePeriodBuilder( startDate, endDate, timeFormat );
        Date start = new Date(0);
        Date end = new Date(0);
        Vector result = new Vector();
        Hashtable aux = new Hashtable();
        
        String select = "SELECT count(*) as pedidos , sum( pedido.pedi_total ) as total \n";
        
        String from = "FROM pedido, establecimiento, barrio, zona, ciudad, departamento, pais \n";
        
        String where = "WHERE pedido.esta_id = establecimiento.esta_id AND \n " +
                        "establecimiento.barr_id = barrio.barr_id AND \n " + 
                        "barrio.zona_id = zona.zona_id AND \n " + 
                        "zona.ciud_id = ciudad.ciud_id AND \n" +
                        "ciudad.depa_id = departamento.depa_id AND \n" + 
                        "departamento.pais_id = pais.pais_id \n";
        
        if( supplierId != -1 ){
            where += "AND pedido.empr_id = "+supplierId+" \n";
        }
        
        if( countryId != -1 ){
            where += "AND pais.pais_id = "+ countryId +" \n";
        }
        
        if( provinceId != -1 ){
            where += "AND departamento.depa_id = "+ provinceId +" \n";
        }
        
        if( cityId != -1 ){
            where += "AND ciudad.ciud_id = "+ cityId +" \n";
        }
        
        String sql = select + from + where;
        
        if( timeTable.size() > 1 ){
            
            for( int i=0;i<timeTable.size()-1; i++ ){
                
                Vector row = new Vector();
                int numOrders = 0;
                double total = 0;
                
                start = (Date)timeTable.elementAt( i );
                end = (Date)timeTable.elementAt( i + 1 );
                
                result = new Vector();
                aux = new Hashtable();
                String auxWhere = "AND pedi_fecha >= \'"+ start.toString() +"\' AND pedi_fecha < \'" + end.toString() + "\' ";
                
                try{
                    
                    System.out.println( sql + auxWhere );
                    
                    result = SQLUtilities.query( sql + auxWhere );
                    if( result.size() > 0 ){
                        
                        aux = (Hashtable)result.elementAt( 0 );
                        numOrders = ((Integer)aux.get( "pedidos" )).intValue();
                        
                        if( aux.get("total2") != null ){
                            total = ((Double)aux.get("total2")).doubleValue();
                        }
                        
                    }
                    
                }catch( QueryException qe ){
                    qe.printStackTrace();
                }
                
                System.out.println( start + " :: " + end + " :: " + numOrders + " :: " + total + "\n\n\n" );
                
                row.addElement( start );
                row.addElement( end );
                row.addElement( new Integer( numOrders ) );
                row.addElement( new Double( total ) );
                
                this.report.addElement( row );
            }
            
        }
        
    }
    
    
    public Vector timePeriodBuilder( Date startDate, Date endDate, int timeFormat ){
    
        //Inicialización Calendarios
        GregorianCalendar init = new GregorianCalendar();
        init.setTimeInMillis( startDate.getTime() );
        GregorianCalendar end = new GregorianCalendar();
        end.setTimeInMillis( endDate.getTime() );
        Date aux = new Date(0);
        
        //Creación tabla de fechas
        Vector timeTable = new Vector();
        
        aux = new Date( init.getTimeInMillis() );
        timeTable.add( aux );
        //System.out.println( aux );
        
        switch( timeFormat ){
        
            case DAY_PERIOD:
                
                while( init.before( end ) ){
                    
                    init.set( Calendar.DATE, init.get( Calendar.DATE ) + 1 ); 
                    aux = new Date( init.getTimeInMillis() );
                    timeTable.add( aux );
                    //System.out.println( aux );
                    
                }
                
            break;
            
            case MONTH_PERIOD:
                
                while( init.before( end ) ){
                
                    init.set( Calendar.MONTH, init.get( Calendar.MONTH ) + 1 ); 
                    aux = new Date( init.getTimeInMillis() );
                    timeTable.add( aux );
                    
                     
                    //System.out.println( aux );
                    
                }
                
            break;
            
            case YEAR_PERIOD:
                
                while( init.before( end ) ){
                
                    init.set( Calendar.YEAR, init.get( Calendar.YEAR ) + 1 );
                    aux = new Date( init.getTimeInMillis() );
                    timeTable.add( aux );
                    
                    //System.out.println( aux );
                    
                }
                
            break;
            
        }
        
        return timeTable;
        
    }
    
    
    public static void main( String[] args ){
        
        Date startDate = new Date( 0 );
        Date endDate = new Date( 0 );
        
        String start = "2005-01-01";
        String end = "2006-01-01";
        
        startDate = Date.valueOf( start );
        endDate = Date.valueOf( end );
        
        System.out.println( startDate + " :: " + endDate );
        
        ComparativeReports cr = new ComparativeReports();
        cr.behaviorReports( cr.MONTH_PERIOD, 2347640, -1, -1, -1, startDate, endDate );
        
        System.exit( -1 );
        
    }
    
    
    /**
     * Getter for property report.
     * @return Value of property report.
     */
    public java.util.Vector getReport() {
        return report;
    }    
    
    /**
     * Setter for property report.
     * @param report New value of property report.
     */
    public void setReport(java.util.Vector report) {
        this.report = report;
    }
    
    /**
     * Getter for property numOrders.
     * @return Value of property numOrders.
     */
    public int getQuantity() {
        return quantity;
    }
    
    /**
     * Setter for property numOrders.
     * @param numOrders New value of property numOrders.
     */
    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }
    
    /**
     * Getter for property total.
     * @return Value of property total.
     */
    public int getTotal() {
        return total;
    }
    
    /**
     * Setter for property total.
     * @param total New value of property total.
     */
    public void setTotal(int total) {
        this.total = total;
    }
    
}
