/*
 * ReportOrderList.java
 *
 * Created on 11 de octubre de 2004, 03:51 PM
 */

package com.fenalco.entities;

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

import com.fenalco.entities.OrderList;
import com.fenalco.entities.Order;

import java.sql.Date;

import java.util.Hashtable;
import java.util.Vector;
import java.util.Enumeration;

/**
 *
 * @author  admin
 */
public class ReportOrderList {
    
    private OrderList orderList;
    
    /** Creates a new instance of ReportOrderList */
    public ReportOrderList() {
        orderList = null;
    }
    
    /*
     *orderStatus 1 : pedido
     *orderStatus 4 : compra
     */
    
    public ReportOrderList(int shopKeeperId, int supplierId, int storeId, int communityId, String sortCriteriaOrder, int orderStatus, 
                           int superOffer, String groupFieldOrder, String groupOperation, Date startDate, Date endDate){
    
           this(); 
           
           String sqlOrder = new String("");
           String selectOrder = new String("SELECT pedido.pedi_id, pedido.pedi_fecha ");
           String fromOrder = new String(" FROM pedido ");
           String whereOrder = new String(" WHERE pedido.esta_pedi_id="+orderStatus+" ");
           
           String sqlHistoric = new String("");
           //String selectHistoric = new String("SELECT pedido_historico.pedi_hist_id, pedido_historico.pedi_hist_fecha ");
           String selectHistoric = new String("SELECT pedido_historico.pedi_hist_id as pedi_id, pedido_historico.pedi_hist_fecha as pedi_fecha");
           String fromHistoric = new String(" FROM pedido_historico ");
           String whereHistoric = new String(" WHERE pedido_historico.pedi_hist_estado_pedido_id="+orderStatus+" ");
           
           String and = new String(" AND ");
           
           String sortByOrder = new String(" ORDER BY pedi_fecha;");
           //String sortByHistoric = new String(" ORDER BY pedido_historico.pedi_hist_fecha");

               if( shopKeeperId > -1 ){
                    //SELECT
                    selectOrder = selectOrder.concat(", empresa.empr_id, empresa.empr_nombre_comercial");
                    selectHistoric = selectHistoric.concat(", empresa.empr_id, empresa.empr_nombre_comercial");

                    //FROM
                    fromOrder = fromOrder.concat(", establecimiento, empresa");
                    fromHistoric = fromHistoric.concat(", establecimiento, empresa");

                    //WHERE
                    whereOrder = whereOrder.concat(and+"pedido.esta_id = establecimiento.esta_id AND " +
                                                   "establecimiento.empr_id = empresa.empr_id AND " +
                                                   "empresa.empr_id = "+shopKeeperId+" ");

                    whereHistoric = whereHistoric.concat(and+"pedido_historico.pedi_hist_establecimiento_id = establecimiento.esta_id AND " +
                                                   "establecimiento.empr_id = empresa.empr_id AND " +
                                                   "empresa.empr_id = "+shopKeeperId+" ");

                    if(and.equals("")){
                        and = and.concat("AND ");
                    }

                    if( storeId > -1){

                    //SELECT
                    selectOrder = selectOrder.concat(", establecimiento.esta_id, establecimiento.esta_nombre");
                    selectHistoric = selectHistoric.concat(", establecimiento.esta_id, establecimiento.esta_nombre");

                    //FROM
                    //fromOrder = fromOrder.concat(", establecimiento");
                    //fromHistoric = fromHistoric.concat(", establecimiento");

                    //WHERE
                    //whereOrder = whereOrder.concat(and+"pedido.esta_id = establecimiento.esta_id AND establecimiento.esta_id="+storeId+" ");
                    //whereHistoric = whereHistoric.concat(and+"pedido_historico.pedi_hist_establecimiento_id = establecimiento.esta_id AND establecimiento.esta_id="+storeId+" ");
                    whereOrder = whereOrder.concat(and+"establecimiento.esta_id="+storeId+" ");
                    whereHistoric = whereHistoric.concat(and+"establecimiento.esta_id="+storeId+" ");


                    if(and.equals("")){
                        and = and.concat("AND ");
                    }
               }
           }
           
           if( supplierId > -1 ){
                
                //SELECT
                selectOrder = selectOrder.concat(", empresa.empr_id, empresa.empr_nombre_comercial");
                selectHistoric = selectHistoric.concat(", empresa.empr_id, empresa.empr_nombre_comercial");

                //FROM
                fromOrder = fromOrder.concat(", empresa");
                fromHistoric = fromHistoric.concat(", empresa");
                
                //WHERE
                whereOrder = whereOrder.concat(and+"pedido.empr_id = empresa.empr_id AND empresa.empr_id="+supplierId+" ");
                whereHistoric = whereHistoric.concat(and+"pedido_historico.pedi_hist_empresa_id = empresa.empr_id AND empresa.empr_id = "+supplierId+" ");

                if(and.equals("")){
                    and = and.concat("AND ");
                }
                
                 if( communityId > -1){

                    //SELECT
                    selectOrder = selectOrder.concat(", comunidad.comu_id, comunidad.comu_nombre");
                    selectHistoric = selectHistoric.concat(", comunidad.comu_id, comunidad.comu_nombre");

                    //FROM
                    fromOrder = fromOrder.concat(", comunidad");
                    fromHistoric = fromHistoric.concat(", comunidad");

                    //WHERE
                    whereOrder = whereOrder.concat(and+"comunidad.empr_id = empresa.empr_id AND empresa.empr_id = pedido.empr_id AND comunidad.comu_id = "+communityId+" ");
                    whereHistoric = whereHistoric.concat(and+"comunidad.empr_id = empresa.empr_id AND empresa.empr_id = pedido_historico.pedi_hist_empresa_id AND comunidad.comu_id = "+communityId+" ");

                    if(and.equals("")){
                        and = and.concat("AND ");
                    }
               }
           }
           
           
           
          
           
           /*
           if(orderStatus>-1){
                whereOrder = whereOrder.concat(and+"pedido.esta_pedi_id ="+orderStatus+" ");
                whereHistoric = whereHistoric.concat(and+"pedido_historico.pedi_hist_estado_pedido_id ="+orderStatus+" ");
           }*/
           
           if(superOffer == 1){
                whereOrder = whereOrder.concat(and+"pedido.pedi_superoferta ="+superOffer+" ");
                whereHistoric = whereHistoric.concat(and+"pedido_historico.pedi_hist_superoferta="+superOffer+" ");
           }
           
           if( startDate.getTime() > 0){
                
                //WHERE
                whereOrder = whereOrder.concat(and+"pedido.pedi_fecha BETWEEN \'"+startDate+"\' AND \'"+endDate+"\' ");
                whereHistoric = whereHistoric.concat(and+"pedido_historico.pedi_hist_fecha BETWEEN \'"+startDate+"\' AND \'"+endDate+"\' ");

                if(and.equals("")){
                    and = and.concat("AND ");
                }
           }
           
           if( !groupFieldOrder.equals("") ){
                
               //SELECT
                selectOrder = selectOrder.concat(","+groupOperation+"("+groupFieldOrder+") as operation ");
                selectHistoric = selectHistoric.concat(","+groupOperation+"("+groupFieldOrder+") as operation");

                //WHERE
                whereOrder = whereOrder.concat(" GROUP BY "+groupFieldOrder+" ");
                whereHistoric = whereHistoric.concat(" GROUP BY "+groupFieldOrder+" ");
           }
           
           sqlOrder = sqlOrder.concat(selectOrder);
           sqlOrder = sqlOrder.concat(fromOrder);
           sqlOrder = sqlOrder.concat(whereOrder);
           //sqlOrder = sqlOrder.concat(sortByOrder);
           
           sqlHistoric = sqlHistoric.concat(selectHistoric);
           sqlHistoric = sqlHistoric.concat(fromHistoric);
           sqlHistoric = sqlHistoric.concat(whereHistoric);
           //sqlHistoric = sqlHistoric.concat(sortByHistoric);
           
           String sqlTotal = new String( sqlOrder.concat(" UNION "+sqlHistoric) );
           
           System.out.println("sql->"+sqlTotal);
           
           if( !sortCriteriaOrder.equals("") ){
               sortByOrder = " ORDER BY "+sortCriteriaOrder+";";
           }
           
           /*if ( !sortCriteriaHistoric.equals("")){
               sortByHistoric = " ORDER BY "+sortCriteriaHistoric;
           }*/
           
           sqlTotal = sqlTotal.concat(sortByOrder);
           
           Vector result;
           Hashtable aux = new Hashtable();
           Vector orders = new Vector();
           try{
                result = SQLUtilities.query(sqlTotal);
                for( int i=0; i<result.size(); i++){
                    aux = ( Hashtable )result.elementAt( i );
                    orders.addElement( new Order( new Integer( aux.get( "pedi_id" ).toString() ).intValue() ) );
                }
                
                OrderList list = new OrderList(); 
                list.setOrders( orders );
                
                this.setOrderList( list );
           
           }catch(QueryException qe){
                System.out.println("Query Error "+qe);
           }
    }
    
    /**
     * Getter for property orderList.
     * @return Value of property orderList.
     */
    public OrderList getOrderList() {
        return orderList;
    }
    
    /**
     * Setter for property orderList.
     * @param orderList New value of property orderList.
     */
    public void setOrderList(OrderList orderList) {
        this.orderList = orderList;
    }
    
    public static void main(String[] args){
        ReportOrderList report = new ReportOrderList(-1,-1,-1,-1,"",1,0,"","",new Date(0),new Date(0));
        OrderList list = report.getOrderList();
        for(int i=0; i<list.getOrders().size(); i++){
            Order order = (Order)list.getOrders().elementAt(i);
            System.out.println(order.getId());
        }
        System.out.println("Ya!!");
        System.exit(0);
    }
    
}
