/*
 * DataAccess.java
 *
 * Created on 13 de agosto de 2004, 02:27 PM
 */

package com.fenalco.entities;

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

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

/**
 * Clase que permite obtener información de tablas específicas de la
 * Base de Datos y hacer ciertas operaciones específicas
 * @author Juan Ernesto Peña Kaltekis
 */
public class DataAccess {
    
    /** Constructor por defecto de la clase */
    public DataAccess() {
    }
    
    /**
     * Obtiene los paises del sistema consultando en la tabla pais
     * @return Vector que contiene los paises
     */    
    
    
    /**
     * Obtiene los sectores en los que puede estar una empresa consultando en la tabla sector
     * @return Hashtable que contiene los sectores
     */    
    public static Hashtable getSectors(){
        Hashtable sectors=new Hashtable();
        Hashtable aux = new Hashtable();
        String sql = "SELECT sect_codigo,sect_nombre FROM sector ORDER BY sect_nombre;";
        Vector result = new Vector();
        
        try{
            result = SQLUtilities.query(sql);
            for(int i=0;i<result.size();i++){
                aux = (Hashtable)result.elementAt(i);
                sectors.put(aux.get("sect_codigo"),aux.get("sect_nombre"));
            }
        }catch(QueryException qe){
            //System.out.println("Query Error: "+qe);
        }
        return sectors;        
    }    
    
    
        
    /**
     * Obtiene los tipos de empresa que pueden estar en el sistema consultando en la tabla
     * tipo_empresa
     * @return Hashtable que contiene los tipos de empresa
     */    
    public static Hashtable getCompanyTypes(){
        Hashtable types=new Hashtable();
        Hashtable aux = new Hashtable();
        String sql = "SELECT tipo_empr_id,tipo_empr_nombre FROM tipo_empresa ORDER BY tipo_empr_nombre;";
        Vector result = new Vector();
        
        try{
            result = SQLUtilities.query(sql);
            for(int i=0;i<result.size();i++){
                aux = (Hashtable)result.elementAt(i);
                types.put(aux.get("tipo_empr_id"),aux.get("tipo_empr_nombre"));
            }
        }catch(QueryException qe){
            //System.out.println("Query Error: "+qe);
        }
        return types;        
    }
    
    /**
     * Obtiene la lista de ciiu de la tabla ciiu
     * @return Hashtable que contiene los ciiu
     */    
    public static Hashtable getCiiu(){
        Hashtable ciius=new Hashtable();
        Hashtable aux = new Hashtable();
        String sql = "SELECT ciiu_id, ciiu_ciiu FROM ciiu ORDER BY ciiu_ciiu;";
        Vector result = new Vector();
        
        try{
            result = SQLUtilities.query(sql);
            for(int i=0;i<result.size();i++){
                aux = (Hashtable)result.elementAt(i);
                ciius.put(aux.get("ciiu_id"),aux.get("ciiu_ciiu"));
            }
        }catch(QueryException qe){
            //System.out.println("Query Error: "+qe);
        }
        
        return ciius;
    }
    

    public static Hashtable getCompanyStatus(){
        Hashtable status=new Hashtable();
        Hashtable aux = new Hashtable();
        String sql = "SELECT esta_empr_id, esta_empr_nombre FROM estado_empresa ORDER BY esta_empr_id;";
        Vector result = new Vector();
        
        try{
            result = SQLUtilities.query(sql);
            for(int i=0;i<result.size();i++){
                aux = (Hashtable)result.elementAt(i);
                status.put(aux.get("esta_empr_id"),aux.get("esta_empr_nombre"));
            }
        }catch(QueryException qe){
            //System.out.println("Query Error: "+qe);
        }
        
        return status;
    }
    
    
    
    /**
     * Crea el archivo backup.sql como copia de seguridad de los datos
     * almacenados en el sistema
     * @return booleano que indica si la realización del backup fué exitosa
     */    
    public static boolean makeBackUp(){
        return false;
    }    
    
    public static Hashtable getBillStatus()
    {
        return null;
    }
    
    /**
     * Esta funciòn retorna el URL del servlet de incio de cada rol
     */
    public static String getRoleUrl(int roleId){
        String url ="";
        try{
            Vector results = SQLUtilities.query("SELECT rol_url" +
                           " FROM rol" +
                           " WHERE rol_id="+roleId+";");
            url = (String)( ((Hashtable)results.elementAt(0)).get("rol_url") );
        }
        catch(QueryException qe){
            System.out.println(qe.getMessage());
        }
                
        return url;
    }
    
    
    /**
     *Esta funciòn retorna el nombre de la ciudad especificada por cityId
     */
    
    public static String getCityName(int cityId){
        String name = null;
        try{
            
            String query = new String("SELECT ciud_nombre" +
                                      " FROM ciudad" +
                                      " WHERE ciud_id ="+cityId);
            Vector results = SQLUtilities.query(query);
            name = (String)( ( (Hashtable)results.elementAt(0) ).get("ciud_nombre") );
            
            
            
        }
        catch(QueryException qe){
            System.out.println(qe.getMessage());
        }
        return name;
    }
    
    
    
    /**
     *
     */
    public static String getCompanyStatusName(int statusId){
        String status = null;
        try{
            
            String query = new String("SELECT esta_empr_nombre" +
                                      " FROM estado_empresa" +
                                      " WHERE esta_empr_id ="+statusId+";");
            Vector results = SQLUtilities.query(query);
            status = (String)( ( (Hashtable)results.elementAt(0) ).get("esta_empr_nombre") );
            
        }
        catch(QueryException qe){
            System.out.println(qe.getMessage());
        }
        return status;
    }
    
    /**
     *
     */
    public static String getOrderStatusName(int statusId){
        String status = null;
        try{
            
            String query = new String("SELECT esta_pedi_nombre" +
                                      " FROM estado_pedido" +
                                      " WHERE esta_pedi_id ="+statusId+";");
            Vector results = SQLUtilities.query(query);
            status = (String)( ( (Hashtable)results.elementAt(0) ).get("esta_pedi_nombre") );
            
            
            
        }
        catch(QueryException qe){
            System.out.println(qe.getMessage());
        }
        return status;
    }
    
    /**
     *
     */
    public static Hashtable getIvaList(){
        Hashtable ivaList = new Hashtable();
        
        try{
            String query = "SELECT iva_id, iva_valor FROM iva ORDER BY iva_valor;";
            Vector results = SQLUtilities.query(query);
            
            for(int i=0; i<results.size() ; i++){
		Integer key = (Integer)((Hashtable)results.elementAt(i)).get("iva_id");
		String object = (String)((Hashtable)results.elementAt(i)).get("iva_valor");
		ivaList.put(key,object);
            }
            
        }catch(QueryException qe){
            
        }
        
        return ivaList;
    }
    
    
    /**
     * Esta funciòn retorna el email definido por PVL para efecto de envio de correos
     * a minoristas y proveeedores
     */
     public static String getPvlEmail(){
         String email ="pvl@pvl.com";
         
         try{
             
             String query = new String("SELECT para_sist_valor" +
                                       " FROM parametros_sistema" +
                                       " WHERE para_sist_nombre=\'Correo PVL\';");
             Vector results = SQLUtilities.query(query);
             Hashtable temp = (Hashtable)results.elementAt(0);
             if(  temp != null){
                email = (String)temp.get("");
             }

         }
         catch(QueryException qe){
             System.out.println(qe.getMessage());
             qe.printStackTrace();
         }
         
         return email;
     }
    
    
    
    /**
     *
     */ 
     public static int getStatusSupplierShopKeeper(int idSupplier, int idShopKeeper){
         int id =1;
         try{
             String query = new String("SELECT mino_prov_catalogo" +
                                       " FROM minorista_proveedor" +
                                       " WHERE empr_id="+idShopKeeper+
                                       " AND pro_empr_id="+idSupplier+";");
             Vector results = SQLUtilities.query(query);
             Hashtable temp = (Hashtable)results.elementAt(0);
             id = ( (Integer)temp.get("mino_prov_catalogo") ).intValue();
             
         }
         catch(QueryException qe){
             System.out.println(qe.getMessage());
             qe.printStackTrace();
         }
         return id;
     }

     /*
      * Obtiene el estado exclusivo de un minorista para un proveedor
      * @return id Exclusividad del minorista para el proveedor
      */
     public static int getShopKeeperExclusiveStatus(int idSupplier, int idShopKeeper){
         int id =1;
         try{
             String query = new String("SELECT mino_prov_exclusivo" +
                                       " FROM minorista_proveedor" +
                                       " WHERE empr_id="+idShopKeeper+
                                       " AND pro_empr_id="+idSupplier+";");
             Vector results = SQLUtilities.query(query);
             Hashtable temp = (Hashtable)results.elementAt(0);
             id = ( (Integer)temp.get("mino_prov_exclusivo") ).intValue();
             
         }
         catch(QueryException qe){
             System.out.println(qe.getMessage());
             qe.printStackTrace();
         }
         return id;
     }
     
     
     public static int getCityProvince(int cityId){
        int provinceId=0;
        String sql = "SELECT depa_id FROM ciudad WHERE ciud_id="+cityId+";";
        Vector result;

        try{

            result = SQLUtilities.query(sql);
            provinceId = ((Integer)((Hashtable)result.elementAt(0)).get("depa_id")).intValue();

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

    public static int getProvinceCountry(int provinceId){
        int countryId=0;
        String sql = "SELECT pais_id FROM departamento WHERE depa_id="+provinceId+";";
        Vector result;

        try{

            result = SQLUtilities.query(sql);
            countryId = ((Integer)((Hashtable)result.elementAt(0)).get("pais_id")).intValue();

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

    public static int getNeighbourhoodCity(int neighbourhoodId){
        int countryId=0;
        String sql = "SELECT ciud_id FROM barrio,zona WHERE barr_id="+neighbourhoodId+" AND zona.zona_id=barrio.zona_id;";
        Vector result;

        try{

            result = SQLUtilities.query(sql);
            countryId = ((Integer)((Hashtable)result.elementAt(0)).get("ciud_id")).intValue();

        }catch(QueryException qe){
            //System.out.println("Query Error: "+qe);
        }
         
        return countryId;
    }
    
    public static boolean existGLN(String GLN){
        boolean flag = false;
        String sql = "SELECT esta_id" +
                     " FROM establecimiento" +
                     " WHERE esta_gln=\'"+GLN+"\';";
        
        String sql2 = "SELECT empr_id" +
                     " FROM empresa" +
                     " WHERE empr_gln=\'"+GLN+"\';";
                     
        try{

            Vector result = SQLUtilities.query(sql);
            Vector result2 = SQLUtilities.query(sql2);
            if(result.size() > 0){
                flag = true;
            }
            if(result2.size() > 0){
                flag = true;
            }

        }catch(QueryException qe){
            //System.out.println("Query Error: "+qe);
        }
         
        return flag;
    }
    
    public static Hashtable getBillingTypes(){
        Hashtable types=new Hashtable();
        Hashtable aux = new Hashtable();
        String sql = "SELECT tipo_fact_id, tipo_fact_nombre FROM tipo_facturacion ORDER BY tipo_fact_id;";
        Vector result = new Vector();
        
        try{
            result = SQLUtilities.query(sql);
            for(int i=0;i<result.size();i++){
                aux = (Hashtable)result.elementAt(i);
                types.put(aux.get("tipo_fact_id"),aux.get("tipo_fact_nombre"));
            }
        }catch(QueryException qe){
            System.out.println("Query Error: "+qe);
        }
        
        return types;
    }
    
    
    public static Hashtable getOrderStatus(int roleId){
        Hashtable status =new Hashtable();
        Hashtable aux = new Hashtable();
        String sql = "SELECT esta_pedi_id, esta_pedi_nombre FROM estado_pedido WHERE rol_id="+roleId+"ORDER BY esta_pedi_id;";
        Vector result = new Vector();
        
        try{
            result = SQLUtilities.query(sql);
            for(int i=0;i<result.size();i++){
                aux = (Hashtable)result.elementAt(i);
                status.put(aux.get("esta_pedi_id"),aux.get("esta_pedi_nombre"));
            }
        }catch(QueryException qe){
            System.out.println("Query Error: "+qe);
        }
        
        return status;
    }
    
    public static Hashtable getQuoteStatus(){
        Hashtable status =new Hashtable();
        Hashtable aux = new Hashtable();
        String sql = "SELECT esta_coti_id, esta_coti_nombre FROM estado_cotizacion ORDER BY esta_coti_id;";
        Vector result = new Vector();
        
        try{
            result = SQLUtilities.query(sql);
            for(int i=0;i<result.size();i++){
                aux = (Hashtable)result.elementAt(i);
                status.put(aux.get("esta_coti_id"),aux.get("esta_coti_nombre"));
            }
        }catch(QueryException qe){
            System.out.println("Query Error: "+qe);
        }
        
        return status;
    }
    
    
    public static Hashtable getShopKeepers(){
        Hashtable status =new Hashtable();
        Hashtable aux = new Hashtable();
        String sql = "SELECT empr_id, empr_nombre_comercial FROM empresa WHERE tipo_empr_id=1 ORDER BY empr_nombre_comercial;";
        Vector result = new Vector();
        
        try{
            result = SQLUtilities.query(sql);
            for(int i=0;i<result.size();i++){
                aux = (Hashtable)result.elementAt(i);
                status.put(aux.get("empr_id"),aux.get("empr_nombre_comercial"));
            }
        }catch(QueryException qe){
            System.out.println("Query Error: "+qe);
        }
        
        return status;
    }
    
    public static boolean hasOrders(int companyId){
        boolean hasOrders=false;
        Vector res;
        
        String sql="SELECT pedi_id" +
                    " FROM pedido" +
                    " WHERE empr_id="+companyId+";";
        try{
            res = SQLUtilities.query( sql );
            if(res.size()>0){
                hasOrders=true;
            }
        }catch(QueryException qe){
            System.out.println( "Error --> " + qe );
            hasOrders=true;
        }
        
        if(!hasOrders){
            sql="SELECT pedi_id" +
                " FROM pedido, establecimiento" +
                " WHERE establecimiento.empr_id="+companyId +
                " AND establecimiento.empr_id=pedido.esta_id;";
            try{
                res = SQLUtilities.query( sql );
                if(res.size()>0){
                    hasOrders=true;
                }
            }catch(QueryException qe){
                System.out.println( "Error --> " + qe );
                hasOrders=true;
            }
        }
        
        return hasOrders;
    }
    
    public static double getBillingParameterValue(int billingTypeId){

        Hashtable temp;
        double value=0;
        String sql="SELECT tipo_fact_parametro" +
            " FROM tipo_facturacion" +
            " WHERE tipo_fact_id="+billingTypeId+";";

        try{
            Vector result = SQLUtilities.query(sql);
            temp = (Hashtable)result.elementAt(0);
            String s=temp.get("tipo_fact_parametro").toString();
            value = Double.valueOf(s).doubleValue();
            
        }catch(QueryException qe){
            System.out.println("Error-> "+qe);
        }
        
        return value;
    }
    
    
    public static int getStoreCommunity(int storeId){
        int community=0;
        String sql="SELECT comu_id" +
                   " FROM comunidad_establecimiento" +
                   " WHERE esta_id="+storeId+";";
         System.out.println("SQL-->"+sql);
        try{
            Vector result = SQLUtilities.query(sql);
            Hashtable temp = (Hashtable)result.elementAt(0);
            community = ((Integer)temp.get("comu_id")).intValue();
            
        }catch(QueryException qe){
            System.out.println("Error-> "+qe);
        }
        
        return community;   
    }
    
    public static int getStoreCommunity(int storeId, int supplierId){
        
        int community=0;
        String sql="SELECT comunidad.comu_id" +
            " FROM comunidad_establecimiento, comunidad" +
            " WHERE comunidad_establecimiento.esta_id="+storeId+
            " AND comunidad_establecimiento.comu_id=comunidad.comu_id" +
            " AND empr_id="+supplierId+";";
        System.out.println("SQL-->"+sql);

        try{
            Vector result = SQLUtilities.query(sql);
             
            community =( (Integer) ((Hashtable)result.elementAt(0)).get("comu_id")).intValue();
            
        }catch(QueryException qe){
            System.out.println("Error-> "+qe);
        }
        
        return community;  
    }
    
    public static int getNumberActivatedStores(int supplierId, Date date){
        int stores=0;
        int month = date.getMonth();
        int year = date.getYear();
        
        String sql="SELECT rela_fact_fecha_activacion" +
                    " FROM comunidad, relacion_facturacion" +
                    " WHERE empr_id="+supplierId+" AND comu_id=rela_fact_comunidad;";
        
        try{
            Vector result = SQLUtilities.query(sql);
            for(int i=0; i<result.size(); i++){
                Hashtable temp = (Hashtable)result.elementAt(i);
                Date temp_date = Date.valueOf(temp.get("rela_fact_fecha_activacion").toString());
                if(temp_date.getMonth()==date.getMonth() && temp_date.getYear()==date.getYear()){
                    stores++;
                }
            }
        }catch(QueryException qe){
            System.out.println("Errot-> "+qe);
        }
        
        return stores;
    }

    public static Hashtable getCompleteBillingTypes(){
        Hashtable types=new Hashtable();
        Hashtable aux = new Hashtable();
        String sql = "SELECT tipo_fact_id, tipo_fact_nombre, tipo_fact_parametro FROM tipo_facturacion ORDER BY tipo_fact_id;";
        Vector result = new Vector();
        
        try{
            result = SQLUtilities.query(sql);
            for(int i=0;i<result.size();i++){
                Vector temp= new Vector();
                aux = (Hashtable)result.elementAt(i);
                temp.add(aux.get("tipo_fact_nombre"));
                temp.add(aux.get("tipo_fact_parametro3"));
                types.put(aux.get("tipo_fact_id"),temp);
            }
        }catch(QueryException qe){
            System.out.println("Query Error: "+qe);
        }
        
        return types;
    }
    
    
    public static void updateBillingParameter(int id, double value, Transaction transaction){
        
        String sql="UPDATE tipo_facturacion SET tipo_fact_parametro="+value+
                    " WHERE tipo_fact_id="+id+";";
        transaction.addSql(sql);
    }
    
    public static void newCountry(String name, Transaction transaction){
        int id=0;
        try{
            id=SQLUtilities.getNextId("pais","pais_id");
            String sql="INSERT INTO pais VALUES("+id+",\'"+name+"\');";
            transaction.addSql(sql);
        }catch(QueryException qe){}
        
    }
    
    public static void newProvince(int countryId, String name, Transaction transaction){
        int id=0;
        try{
            id=SQLUtilities.getNextId("departamento","depa_id");
            String sql="INSERT INTO departamento VALUES("+id+","+countryId+",\'"+name+"\');";
            transaction.addSql(sql);
        }catch(QueryException qe){}
    }
    
    public static void newCity(int provinceId, String name, Transaction transaction){
        int cityId=0;
        int zoneId=0;
        try{
            cityId=SQLUtilities.getNextId("ciudad","ciud_id");
            String sql="INSERT INTO ciudad VALUES("+cityId+","+provinceId+",\'"+name+"\');";
            transaction.addSql(sql);
            newZone(cityId,"Indefinida",transaction);
        }catch(QueryException qe){}            
    }
    
    public static void newZone(int cityId, String name, Transaction transaction){
        int id=0;
        try{
            id=SQLUtilities.getNextId("zona","zona_id");
            String sql="INSERT INTO zona VALUES("+id+","+cityId+",\'"+name+"\');";
            transaction.addSql(sql);
        }catch(QueryException qe){}            
    }
    
    public static void newNeighbourhood(int zoneId, String name, Transaction transaction){
        int id=0;
        try{
            id=SQLUtilities.getNextId("barrio","barr_id");
            String sql="INSERT INTO barrio VALUES("+id+","+zoneId+",\'"+name+"\');";
            transaction.addSql(sql);
        }catch(QueryException qe){}            
    }

    public static boolean deleteCountry(int id, Transaction transaction){
        boolean success=true;
        String sql="SELECT * FROM departamento WHERE pais_id="+id+";";
        Vector result = new Vector();
        
        try{
            result = SQLUtilities.query(sql);
            if(result==null || result.size()==0){
                sql="DELETE FROM pais WHERE pais_id="+id+";";
                transaction.addSql(sql);
            }else{
                success=false;
            }
        }catch(QueryException qe){}
        
        return success;
        
    }

    public static boolean deleteProvince(int id, Transaction transaction){
        boolean success=true;
        String sql="SELECT * FROM ciudad WHERE depa_id="+id+";";
        Vector result = new Vector();
        
        try{
            result = SQLUtilities.query(sql);
            if(result==null || result.size()==0){
                sql="DELETE FROM departamento WHERE depa_id="+id+";";
                transaction.addSql(sql);
            }else{
                success=false;
            }
        }catch(QueryException qe){}
        
        return success;
    }

    public static boolean deleteCity(int id, Transaction transaction){
        boolean success=true;
        String sql="SELECT zona_id, zona_nombre FROM zona WHERE ciud_id="+id+";";
        Vector result = new Vector();
        
        try{
            result = SQLUtilities.query(sql);
            if(result.size()==1){
                sql="DELETE FROM zona WHERE ciud_id="+id+";";
                transaction.addSql(sql);
                sql="DELETE FROM ciudad WHERE ciud_id="+id+";";
                transaction.addSql(sql);
            }else{
                success=false;
            }
        }catch(QueryException qe){}
        
        return success;
    }
    
    public static boolean deleteZone(int id, Transaction transaction){
        boolean success=true;
        String sql1="SELECT nombre FROM zona WHERE zona_id="+id+";";
        String sql2="SELECT * FROM barrio WHERE zona_id="+id+";";
        Vector result = new Vector();
        
        try{
            result = SQLUtilities.query(sql1);
            Hashtable temp = (Hashtable)result.elementAt(0);
            String name = (String)temp.get("zona_nombre");
            
            if(!name.equals("Indefinida")){
                result = SQLUtilities.query(sql2);
                if(result!=null && result.size()==0){
                    String sql="DELETE FROM zona WHERE zona_id="+id+";";
                    transaction.addSql(sql);
                }else{
                    success=false;
                }
            }else{
                success=false;
            }
        }catch(QueryException qe){}
        
        return success;
    }
    
    public static boolean deleteNeighbourhood(int id, Transaction transaction){
        String sql="DELETE FROM barrio WHERE barr_id="+id+";";
        transaction.addSql(sql);
        
        return true;
    }
    
    
    public static String getCurrency( double d )
    {
        String currency = "";
        long l = ( long )d;
        String price = l + "";
        int size = price.length();
        boolean cont = true;
        while( cont )
        {
            if( size > 3 )
            {
                currency = price.substring( price.length() - 3 ) + "." + currency;
                price = price.substring( 0, price.length() - 3 );
                size = price.length();
            }
            else
            {
                currency = price + "." + currency;
                cont = false;
            }
        }
        currency = "$" + currency.substring( 0, currency.length() - 1 );
        return currency;
    }
    
    public static int getStoresNumber(int shopKeeperId){
        int stores = 0;
        
        try{
            String query = "SELECT count(*) as n" +
                           " FROM establecimiento" +
                           " WHERE empr_id="+shopKeeperId+";";
            Vector results = SQLUtilities.query(query);
            Hashtable temp = (Hashtable)results.elementAt(0);
            System.out.println(temp);
            String object = temp.get("n").toString();
            stores = Integer.parseInt(object);
            
        }
        catch(QueryException qe){
            System.out.println("Error en DataAccess-->"+qe.getMessage());
            stores = -1;
        }
        
        return stores;
    }
    
    
    public static int getSupplierUsers(int supplierId){
        int userCount = -1;
        try{
            String sql = "SELECT para_sist_valor FROM parametros_sistema WHERE para_sist_id=3;";
            Vector result = SQLUtilities.query(sql);
            Hashtable aux = (Hashtable)result.elementAt(0);
            userCount = new Integer((String)aux.get("para_sist_valor")).intValue();
        }catch(QueryException qe){
            System.out.println("Query Error "+qe);
        }
        return userCount;
    }
    
    public static double getMinimalOrder(int supplierId){
         double minimal = -1;
           try{
            String sql = "SELECT prov_pedido_minimo FROM proveedor WHERE empr_id="+supplierId+";";
            Vector result = SQLUtilities.query(sql);
            Hashtable aux = (Hashtable)result.elementAt(0);
            minimal = ((Double)aux.get("prov_pedido_minimo2")).doubleValue();
           
        }catch(QueryException qe){
            System.out.println("Query Error "+qe);
        }
        return minimal;
        
    }
    
    public static int getStratum(){
        int stratum = -1;
           try{
            String sql = "SELECT para_sist_valor FROM parametros_sistema WHERE para_sist_id=4;";
            Vector result = SQLUtilities.query(sql);
            Hashtable aux = (Hashtable)result.elementAt(0);
            Object temp = aux.get("para_sist_valor");
            stratum = Integer.parseInt(temp.toString());
           
        }catch(QueryException qe){
            System.out.println("Query Error "+qe);
        }
        return stratum;
    }
    
   public static int geSuppliertIdFromGln( String gln )
   {
       int id = 0;
       String sql = "SELECT empr_id FROM proveedor WHERE prov_gln=\'" + gln + "\';";
       try
       {
           Vector res = SQLUtilities.query( sql );
           Hashtable d = ( Hashtable )res.elementAt( 0 );
           id = ( ( Integer )d.get( "empr_id" ) ).intValue();
       }
       catch( Exception e )
       {
           id = 0;
       }
       return id;
   }

   public static int getMaxStoresNumber(){
       int stores = 0;
       String sql = "SELECT para_sist_valor FROM parametros_sistema WHERE para_sist_id=1;";
       try
       {
           Vector res = SQLUtilities.query( sql );
           Hashtable aux = ( Hashtable )res.elementAt( 0 );
           Object temp = aux.get("para_sist_valor");
           stores = Integer.parseInt(temp.toString());
          
       }
       catch( Exception e )
       {
           stores = 0;
       }
       
       return stores;
   }
    
   
   public static boolean monthInvoiced(Date date){
       boolean flag = true;
       
       String sql = "SELCT * FROM factura WHERE fact_fecha=\'"+date.toString()+"\';";
       try{
           Vector result = SQLUtilities.query(sql);
           if(result.size()==0){
               return false;
           }
       }catch(QueryException qe){
            System.out.println(qe);
       }
       
       return flag;
   }
   
   
    /*:x*/
    public static Vector communityIds( int supplierId ){
        Vector ids = new Vector();
        String sql = "SELECT comu_id FROM comunidad WHERE empr_id="+supplierId+";";
        
        try{
            
            Vector result = SQLUtilities.query( sql );
            int size = result.size();
            
            if( size > 0 ){
                
                for(int i =0; i<size; i++ ){
                    
                    Hashtable aux = ( Hashtable )result.elementAt( i );
                    ids.addElement( (Integer)aux.get("comu_id"));
                    
                }
                
            }
            
        }catch( QueryException qe ){
            
            System.out.println( "Query Exception " + qe );
            
        }
        
        return ids;
    }
    
    
    /**
    * Esta función valida si un producto tiene escalas definidas para
    * uan comunidad dada
    */
   public static boolean isValidProduct(int id, int community){
       boolean flag = true;
       
       String sql = "SELECT esca_prec_prod_id " +
                    " FROM escala_precio_producto" +
                    " WHERE comu_id="+community+" AND prod_prov_id="+id+";";
       
       try
       {
           Vector results = SQLUtilities.query( sql );
           if(results == null || results.size() == 0) {
               
           }
       }
       catch( Exception e )
       {
           flag = false;
       }
       return flag;
       
   }
    
    public static void main(String[] args)
    {
        System.out.println( DataAccess.existGLN("") );
    }
}
