/*
 * NoOrderShopKeeper.java
 *
 * Created on 9 de febrero de 2004, 02:43 AM
 */

package com.fenalco.servlets.sysmanager.reports;


import java.io.*;
import java.net.*;
import java.sql.Date;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Enumeration;
import java.util.Vector;

import javax.servlet.*;
import javax.servlet.http.*;

import com.fenalco.entities.Neighbourhood;
import com.fenalco.entities.Zone;
import com.fenalco.entities.City;
import com.fenalco.entities.Province;
import com.fenalco.entities.DataAccess;

import com.fenalco.entities.SupplierList;
import com.fenalco.entities.CommunityList;
import com.fenalco.entities.Supplier;
import com.fenalco.entities.Community;

import com.fenalco.entities.ShopKeeperList;
import com.fenalco.entities.StoreList;
import com.fenalco.entities.ShopKeeper;
import com.fenalco.entities.Store;

import com.fenalco.entities.CategoryList;

import com.fenalco.entities.User;
import com.fenalco.entities.OrderList;
import com.fenalco.entities.GeneralReports;
import com.fenalco.utilities.lists.SupplierListViewSysManager;
import com.fenalco.utilities.lists.CommunityListView;
import com.fenalco.utilities.lists.ShopKeeperListViewSysManager;
import com.fenalco.utilities.lists.StoreListViewSupplier;
import com.fenalco.utilities.lists.ReportOrderListViewSysManager;

import com.fenalco.utilities.MessageBuilder;
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  Mario Linares
 * @version
 */
public class NoOrderShopKeeper extends HttpServlet {
    
    /** Initializes the servlet.
     */
    public void init(ServletConfig config) throws ServletException {
        super.init(config);
        
    }
    
    /** Destroys the servlet.
     */
    public void destroy() {
        
    }
    
    /** Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        String url = "";
        String message = "";
        HttpSession session = request.getSession(false);
        //try{
            if( session == null || ( User )session.getAttribute( "user" ) == null || ( ( User )session.getAttribute( "user" ) ).getRoleId() != 5 ){
                url = "/index.jsp";
                message = MessageBuilder.buildMessage(15);
            }else{

               
                Vector list = new Vector();
                ////Combos País - Departamento - Ciudad - Zona - Barrio/////////
                
                Vector countriesList = DataAccess.getCountries();
                Vector provincesList = new Vector();
                Vector citiesList = new Vector();
                Vector zonesList = new Vector();
                Vector neighbourhoodsList = new Vector();
                
                int countryId = 1;
                int provinceId = -1;
                int cityId = -1;
                int zoneId = -1;
                int neighborhoodId = -1;
                String combo = "country";
                
                if( request.getParameter("combo") != null ){
                    combo = request.getParameter( "combo" );
                }
                
                System.out.println( "combo: " + combo );
                if( combo.equals("country") ){
                    
                    if( request.getParameter( "country" ) != null ){
                        countryId = Integer.parseInt( request.getParameter( "country" ) );
                    } 
                    
                    provincesList = DataAccess.getProvinces( countryId );
                    /*
                    if( provincesList.size() > 0 ){
                        provinceId = ((Province)provincesList.elementAt(0)).getId();
                    }
                     */
                    
                    citiesList = DataAccess.getCities( provinceId );
                    /*
                    if( citiesList.size() > 0 ){
                        cityId = ((City)citiesList.elementAt(0)).getId();
                    }
                     */
                                        
                    zonesList = DataAccess.getZones( cityId );
                    /*
                    if( zonesList.size() > 0 ){
                        zoneId = ((Zone)zonesList.elementAt(0)).getId();
                    }
                     */
                    
                    neighbourhoodsList = DataAccess.getNeighbourhoodsZone( zoneId );
                    /*
                    if( neighbourhoodsList.size() > 0 ){
                        neighbourhoodId = ((Neighbourhood)neighbourhoodsList.elementAt(0)).getId();
                    }
                     */
                    
                }else if( combo.equals("province") ){
                
                    countryId = Integer.parseInt( request.getParameter( "country" ) );
                    provinceId = Integer.parseInt( request.getParameter( "province" ) );
                    
                    provincesList = DataAccess.getProvinces( countryId );
                    citiesList = DataAccess.getCities( provinceId );
                    /*
                    if( citiesList.size() > 0 ){
                        cityId = ((City)citiesList.elementAt(0)).getId();
                    }
                     */
                                        
                    zonesList = DataAccess.getZones( cityId );
                    /*
                    if( zonesList.size() > 0 ){
                        zoneId = ((Zone)zonesList.elementAt(0)).getId();
                    }
                     */
                    
                    neighbourhoodsList = DataAccess.getNeighbourhoodsZone( zoneId );
                    /*
                    if( neighbourhoodsList.size() > 0 ){
                        neighbourhoodId = ((Neighbourhood)neighbourhoodsList.elementAt(0)).getId();
                    }
                     */
                    
                }else if( combo.equals("city") ){
                
                    countryId = Integer.parseInt( request.getParameter( "country" ) );
                    provinceId = Integer.parseInt( request.getParameter( "province" ) );
                    cityId = Integer.parseInt( request.getParameter( "city" ) );
                    
                    provincesList = DataAccess.getProvinces( countryId );
                    citiesList = DataAccess.getCities( provinceId );
                    zonesList = DataAccess.getZones( cityId );
                    /*
                    if( zonesList.size() > 0 ){
                        zoneId = ((Zone)zonesList.elementAt(0)).getId();
                    }
                     */
                    
                    neighbourhoodsList = DataAccess.getNeighbourhoodsZone( zoneId );
                    /*
                    if( neighbourhoodsList.size() > 0 ){
                        neighbourhoodId = ((Neighbourhood)neighbourhoodsList.elementAt(0)).getId();
                    }
                     */
                    
                    
                }else if( combo.equals("zone") ){
                
                    countryId = Integer.parseInt( request.getParameter( "country" ) );
                    provinceId = Integer.parseInt( request.getParameter( "province" ) );
                    cityId = Integer.parseInt( request.getParameter( "city" ) );
                    zoneId = Integer.parseInt( request.getParameter( "zone" ) );
                    
                    provincesList = DataAccess.getProvinces( countryId );
                    citiesList = DataAccess.getCities( provinceId );
                    zonesList = DataAccess.getZones( cityId );
                    neighbourhoodsList = DataAccess.getNeighbourhoodsZone( zoneId );
                    /*
                    if( neighbourhoodsList.size() > 0 ){
                        neighbourhoodId = ((Neighbourhood)neighbourhoodsList.elementAt(0)).getId();
                    }
                     */
                    
                }
                try{
                    neighborhoodId = Integer.parseInt( request.getParameter( " neighbourhood" ) );
                }
                catch(Exception e){
                    neighborhoodId = -1;
                }
                
                ///////////////////////////////Paginación///////////////////////
                //Criterios de Paginación
                int page = 0;
                int pageResults = 20;
                int pages = 0; // numero total de páginas
                int firstRow = 0; // numero del primero registro que se va a mostrar
                int lastRow = 0; // numero del último registro que se va a mostrar
                int total = 0; // numero total de resultados
                
                
                if(request.getParameter("page")!=null){
                    try{
                        page = Integer.parseInt(request.getParameter("page"));
                    }catch(NumberFormatException nfe){
                        page = 1;
                    }
                    page = (page < 1) ? 1 : page;
                    try{
                        pageResults = Integer.parseInt(request.getParameter("pageResults"));
                    }catch(NumberFormatException nfe){
                        pageResults = 20;
                    }
                    pageResults = (pageResults < 1) ? 20 : pageResults;
                }
                
                
                
                ////////////////////////////////////////////////////////////////
                
                list = this.getNoOrderList(countryId, provinceId, cityId, zoneId, neighborhoodId);
                ////////////////////////////////////////////////////////////////
               
                
                total = list.size();
                pages = (int) Math.ceil((double) total / (double) pageResults);
                if( page > pages ){
                    page = pages;
                }
                firstRow = (page - 1) * pageResults;
                lastRow = page * pageResults;
                firstRow = (firstRow < 0) ? 0 : firstRow;
                if( lastRow > total ){
                    lastRow = total;
                }

                Vector temp = new Vector();
                for( int i = 0; i < total; i++ ){
                    if( i >= firstRow && i <= lastRow - 1 ){
                        temp.addElement(list.elementAt(i));
                        
                    }
                }

                list = temp ;
                
                //Creación Vista !!!! TODO
                
                ////////////////////////////////////////////////////////////////
                

                ///////////////////Envío Parámetros/////////////////////////////
                
               
                request.setAttribute("countryId", new Integer(countryId));
                request.setAttribute("provinceId", new Integer(provinceId));
                request.setAttribute("cityId", new Integer(cityId));
                request.setAttribute("zoneId", new Integer(zoneId));
                request.setAttribute("neighbourhoodId", new Integer( neighborhoodId ) );

                request.setAttribute("countriesList", countriesList);
                request.setAttribute("provincesList", provincesList);
                request.setAttribute("citiesList", citiesList);
                request.setAttribute("zonesList", zonesList );
                request.setAttribute("neighbourhoodsList", neighbourhoodsList );
                request.setAttribute("combo", combo );
                request.setAttribute("list", temp);
                
                /// información de la paginacion
                firstRow++; // para no arrancar desde 0
                firstRow = (lastRow == 0) ? 0 : firstRow;
                request.setAttribute("firstRow", firstRow + "");
                request.setAttribute("lastRow", lastRow + "");
                request.setAttribute("total", total + "");
                request.setAttribute("page", page + "");
                request.setAttribute("pages", pages + "");
                request.setAttribute("pageResults", pageResults + "");
                
                url = "/administrador/reportes/no_compra_minorista.jsp";
                
                ////////////////////////////////////////////////////////////////
                
            }    
          
        request.setAttribute("message", message);
        RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(url);
        dispatcher.forward(request, response);
    }
    
    /** Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }
    
    /** Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }
    
    /** Returns a short description of the servlet.
     */
    public String getServletInfo() {
        return "Short description";
    }
    
    
    private Vector getNoOrderList(int countryId, int provinceId, int cityId, int zoneId, int  neighborhoodId){
      
        
        Vector result = new Vector();
        Hashtable aux = new Hashtable();
        Vector reportList = new Vector();
        Vector row = new Vector();

        String select = "select distinct empresa.empr_id,empresa.empr_nombre_comercial ";
 
        String from = "FROM empresa,pais, departamento, ciudad, zona, barrio,establecimiento  ";
        String where = "WHERE empresa.empr_id in "+ 
                       "(select distinct establecimiento.empr_id  from establecimiento "+
                       "where establecimiento.esta_id not in( "+
                       "select distinct pedido.esta_id "+
                       "from pedido) ) "+
                       "AND establecimiento.empr_id=empresa.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 orderBy = "ORDER BY empresa.empr_nombre_comercial";
        String sql = "";
        
        
        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(neighborhoodId!=-1) where = where.concat("AND barrio.barr_id="+neighborhoodId+" ");
        
        sql = sql.concat(select + from + where + orderBy + ";");

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

            result = SQLUtilities.query(sql);
            
            
            
            if( result!= null && result.size() > 0 ){
                
                for( int i=0; i<result.size(); i++){
                    aux = ( Hashtable )result.elementAt( i );
                    int emprId = new Integer( aux.get( "empr_id" ).toString() ).intValue();
                    reportList.addElement( new Integer(emprId) );
                }
                
            }
            

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