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

package com.fenalco.entities;

import java.sql.Date;
import java.util.Enumeration;
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
	 */
	public static Vector getCountries( )
	{
		/*
		 * Este Hashtable está organizado así: key: countryId, value
		 * countryName;
		 */
		Vector countries = new Vector();
		Hashtable aux = new Hashtable();
		String sql = "SELECT pais_id, pais_nombre FROM pais ORDER BY pais_nombre;";
		Vector result = new Vector();

		try
		{
			result = SQLUtilities.query(sql);
			for( int i = 0; i < result.size(); i++ )
			{
				aux = (Hashtable) result.elementAt(i);
				countries.addElement(new Country(Integer.parseInt(aux.get(
						"pais_id").toString())));
			}
		}
		catch (QueryException qe)
		{
			//System.out.println("Query Error: "+qe);
		}

		return countries;
	}

	public static Vector getValidCountries( )
	{
		Vector countries = new Vector();
		Vector temp = DataAccess.getCountries();
		Enumeration en = temp.elements();
		while( en.hasMoreElements() )
		{
			boolean flag = false;
			int key = ((Country) en.nextElement()).getId();
			Vector temp2 = DataAccess.getProvinces(key);
			Enumeration en2 = temp2.elements();

			while( en2.hasMoreElements() && !flag )
			{
				int key2 = ((Province) en2.nextElement()).getId();
				Vector temp3 = DataAccess.getCities(key2);
				if( temp3.size() > 0 )
				{
					flag = true;
				}

			}
			if( flag )
			{
				countries.addElement(new Country(key));
			}

		}
		return countries;
	}

	public static Vector getValidCountries2( )
	{
		Vector countries = new Vector();
		Vector temp = DataAccess.getCountries();
		Enumeration en = temp.elements();
		while( en.hasMoreElements() )
		{
			boolean flag = false;
			int key = ((Country) en.nextElement()).getId();
			Vector temp2 = DataAccess.getProvinces(key);
			Enumeration en2 = temp2.elements();

			while( en2.hasMoreElements() && !flag )
			{
				int key2 = ((Province) en2.nextElement()).getId();
				Vector temp3 = DataAccess.getCities(key2);
				Enumeration en3 = temp3.elements();
				while( en3.hasMoreElements() )
				{
					int key3 = ((City) en3.nextElement()).getId();
					Vector temp4 = DataAccess.getNeighbourhoods(key3);
					if( temp4.size() > 0 )
					{
						flag = true;
					}

				}

			}
			if( flag )
			{
				countries.addElement(new Country(key));
			}

		}
		return countries;
	}

	public static Vector getValidProvinces( int countryId )
	{
		Vector provinces = new Vector();
		Vector temp = DataAccess.getProvinces(countryId);
		Enumeration en = temp.elements();
		while( en.hasMoreElements() )
		{
			boolean flag = false;
			int key = ((Province) en.nextElement()).getId();
			Vector temp2 = DataAccess.getCities(key);
			if( temp2.size() > 0 )
			{
				flag = true;
			}

			if( flag )
			{
				provinces.addElement(new Province(key));
			}
		}

		return provinces;
	}

	public static Vector getValidProvinces2( int countryId )
	{
		Vector provinces = new Vector();
		Vector temp = DataAccess.getProvinces(countryId);
		Enumeration en = temp.elements();
		while( en.hasMoreElements() )
		{
			boolean flag = false;
			int key = ((Province) en.nextElement()).getId();
			Vector temp2 = DataAccess.getCities(key);
			Enumeration en2 = temp2.elements();
			while( en2.hasMoreElements() && !flag )
			{
				int key2 = ((City) en2.nextElement()).getId();
				Vector temp3 = DataAccess.getNeighbourhoods(key2);
				if( temp3.size() > 0 )
				{
					flag = true;
				}
			}

			if( flag )
			{
				provinces.addElement(new Province(key));
			}
		}

		return provinces;
	}

	public static Vector getValidCities( int provinceId )
	{
		Vector cities = new Vector();
		Vector temp = DataAccess.getCities(provinceId);
                System.out.println(temp);
		Enumeration en = temp.elements();
		while( en.hasMoreElements() )
		{
			boolean flag = false;
			int key = ((City) en.nextElement()).getId();
                        Vector temp2 = DataAccess.getNeighbourhoods(key);
			if( temp2.size() > 0 )
			{
				flag = true;
			}


			if( flag )
			{
                                cities.addElement(new City(key));
			}
		}

		return cities;
	}

	/**
	 * Obtiene los departamentos asociados al país consultando en la tabla
	 * departamento
	 * 
	 * @return Vector que contiene los departamentos
	 * @param countryId
	 *            Identificador del país del cual se quieren obtener los
	 *            departamentos
	 */
	public static Vector getProvinces( int countryId )
	{
		Vector provinces = new Vector();
		Hashtable aux = new Hashtable();
		String sql = "SELECT depa_id,depa_nombre FROM departamento WHERE pais_id="
				+ countryId + " ORDER BY depa_nombre;";
		Vector result = new Vector();

		try
		{
			result = SQLUtilities.query(sql);

			for( int i = 0; i < result.size(); i++ )
			{
				aux = (Hashtable) result.elementAt(i);
				provinces.addElement(new Province(Integer.parseInt(aux.get(
						"depa_id").toString())));
			}
		}
		catch (QueryException qe)
		{
			System.out.println("Query Error: " + qe);
		}
		return provinces;

	}

	/**
	 * Obtiene las ciudades/municipios asociados al departamento consultando en
	 * la tabla ciudad
	 * 
	 * @param provinceId
	 *            Identificador del departamento del cual se quieren obtener las
	 *            ciudades/municipios
	 * 
	 * @return Vector que contiene las ciudades/municipios
	 */
	public static Vector getCities( int provinceId )
	{
		Vector cities = new Vector();
		Hashtable aux = new Hashtable();
		String sql = "SELECT ciud_id,ciud_nombre FROM ciudad WHERE depa_id="
				+ provinceId + " ORDER BY ciud_nombre;";
		Vector result = new Vector();

		try
		{
			result = SQLUtilities.query(sql);
			for( int i = 0; i < result.size(); i++ )
			{
				aux = (Hashtable) result.elementAt(i);
				cities.addElement(new City(Integer.parseInt(aux.get("ciud_id")
						.toString())));
			}
		}
		catch (QueryException qe)
		{
			System.out.println("Query Error: " + qe.getMessage());
			qe.printStackTrace();
		}

		return cities;
	}

	/**
	 * Obtiene las zonas asociadas a la ciudad/municipio consultando en la tabla
	 * zona
	 * 
	 * @return Vector que contiene las zonas
	 * @param cityId
	 *            Identificador de la ciudad/municipio del cual se quieren
	 *            obtener las zonas
	 */
	public static Vector getZones( int cityId )
	{
		Vector zones = new Vector();
		Hashtable aux = new Hashtable();
		String sql = "SELECT zona_id, zona_nombre FROM zona WHERE ciud_id ="
				+ cityId + " ORDER BY zona_nombre;";
		Vector result = new Vector();

		try
		{
			result = SQLUtilities.query(sql);
			for( int i = 0; i < result.size(); i++ )
			{
				aux = (Hashtable) result.elementAt(i);
				zones.addElement(new Zone(Integer.parseInt(aux.get("zona_id")
						.toString())));
			}
		}
		catch (QueryException qe)
		{
			System.out.println("Query Error: " + qe);
		}
		return zones;
	}

	/**
	 * Obtiene los barrios asociados a una ciudad
	 * 
	 * @param zoneId
	 *            Identificador de la zona de la cual se quieren obtener los
	 *            barrios
	 * @param cityId
	 *            id de la ciudad respectiva
	 * @return Vector que contiene los barrios
	 */
	public static Vector getNeighbourhoods( int cityId )
	{
		Vector neighbourhoods = new Vector();
		Hashtable aux = new Hashtable();
		String sql = "SELECT DISTINCT barr_id, barr_nombre FROM barrio,zona WHERE barrio.zona_id=zona.zona_id AND ciud_id="
				+ cityId + " ORDER BY barr_nombre;";
		Vector result = new Vector();

		try
		{
			result = SQLUtilities.query(sql);
			for( int i = 0; i < result.size(); i++ )
			{
				aux = (Hashtable) result.elementAt(i);
				neighbourhoods.addElement(new Neighbourhood(Integer
						.parseInt(aux.get("barr_id").toString())));
			}
		}
		catch (QueryException qe)
		{
			System.out.println("Query Error: " + qe);
		}
		return neighbourhoods;
	}

	/**
	 * Obtiene los barrios asociados a la zona consultando en la tabla barrio
	 * 
	 * @return Hashtable que contiene los barrios
	 */
	public static Vector getNeighbourhoodsZone( int zoneId )
	{
		Vector neighbourhoods = new Vector();
		Hashtable aux = new Hashtable();
		String sql = "SELECT barr_id, barr_nombre FROM barrio WHERE zona_id ="
				+ zoneId + " ORDER BY barr_nombre;";
		Vector result = new Vector();

		try
		{
			result = SQLUtilities.query(sql);
			for( int i = 0; i < result.size(); i++ )
			{
				aux = (Hashtable) result.elementAt(i);
				neighbourhoods.addElement(new Neighbourhood(Integer
						.parseInt(aux.get("barr_id").toString())));
			}
		}
		catch (QueryException qe)
		{
			System.out.println("Query Error: " + qe);
		}
                if(neighbourhoods.size()==0){
                    neighbourhoods.addElement(new Neighbourhood(1));
                }
		return neighbourhoods;
	}


	/**
	 * 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 WHERE tipo_empr_id<>0 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_id=\'2\';");
			Vector results = SQLUtilities.query(query);
			Hashtable temp = (Hashtable) results.elementAt(0);
			if( temp != null )
			{
				email = (String) temp.get("para_sist_valor");
			}

		}
		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 + "\';";
                
		String sql3 = "SELECT empr_id" + " FROM proveedor" + " WHERE prov_gln=\'"
				+ GLN + "\';";
                
		try{

			Vector result = SQLUtilities.query(sql);
			if( result.size() > 0 ){
				flag = true;
			}else{
                            result = SQLUtilities.query(sql2);
                            if( result.size() > 0 ){
                                    flag = true;
                            }else{
                                result = SQLUtilities.query(sql3);
                                if( result.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.esta_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_parametro2").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 DESC;";
		Vector result = new Vector();

		try
		{
			result = SQLUtilities.query(sql);
			for( int i = 0; i < result.size(); i++ )
			{
                                aux = (Hashtable) result.elementAt(i);
				Vector temp = new Vector();
				temp.add(aux.get("tipo_fact_nombre"));
				temp.add((Double)(aux.get("tipo_fact_parametro2")));
				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 = "SELECT * 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( 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 )
			{
				flag = false;
			}
		}
		catch (Exception e)
		{
			flag = false;
		}
		return flag;

	}

   /*
    *Indica si un minorista es cliente exclusivo de un proveedor
    */
   public static boolean isExclusive( int shopKeeperId ){
        boolean response = false;
        String sql = "SELECT empr_id FROM minorista_proveedor WHERE mino_prov_exclusivo=1 AND empr_id="+shopKeeperId+";";
        Vector result;
        
        try{
            result = SQLUtilities.query(sql);
            
            if(result.size()>0){
                response = true;
            }
            
        }catch(QueryException qe){
            
        }
        //System.out.println(shopKeeperId+" "+response);
        return response;
     }
        
    /**
    * Indica si un minorista tiene relaciones comerciales con proveedores distintos al del
    * id que va como parámetro de la función
    * @param shopKeeperId Identificador del minorista
    * @param shopKeeperId Identificador del proveedor
    * @param suppliers Identificadores de los proveedores con los que el minorista tiene
    * relación comercial (diferentes al provedor del parámetro)
    */     
    public static boolean hasCommercialRelations(int shopKeeperId, int supplierId, Vector suppliers){
        boolean flag = true;
        String sql="SELECT mino_prov_id, pro_empr_id FROM minorista_proveedor " +
                    "WHERE empr_id="+shopKeeperId+" AND pro_empr_id<>"+supplierId+";";
 
        try{
            Vector result = SQLUtilities.query(sql);
            if(result.size()==0){
                flag=false;
            }else{
                for( int i = 0; i < result.size(); i++ ){
                    Hashtable aux = (Hashtable) result.elementAt(i);
                    suppliers.addElement( aux.get("pro_empr_id"));
                }
            }
        }catch(QueryException qe){
           qe.printStackTrace();
        }
       
        return flag;
    }
   
   
   public static String formatText( String input, int numChars ){
   
        String aux = input;
        String result = new String();
        int length = aux.length();
        int init = 0;
        int end = 1;

        while( end < length ){
            if( end % numChars == 0 ){

                result = result.concat( aux.substring( init, end ) + " " );
                init = end;

            }
            end++;
        }

        if( init < end ){
            result = result.concat( aux.substring( init,end ) );
        }   
        
        return result;
       
   } 
   
   public static Vector getCategories( )
   {
   		Vector cat = new Vector( );
   		
   		String sql = "SELECT cate_prod_nombre FROM categoria_producto ORDER BY cate_prod_nombre;";
   		Vector temp = null;
   		try
		{
			temp = SQLUtilities.query( sql );
		}
		catch( QueryException e )
		{
			e.printStackTrace();
		}
		
		for( int i = 0; i < temp.size(); i++ )
		{
			Hashtable hashtable = ( Hashtable )temp.elementAt( i );
			cat.addElement( ( String )hashtable.get( "cate_prod_nombre" ) );
		}
   		
		return cat;
   }
   
   public static String getProductCode( int id )
   {
   		String code = "";
   		
   		String sql = "SELECT DISTINCT prod_prov_codigo FROM producto_proveedor WHERE prod_prov_id=" + id + ";";
   		try
		{
			Vector res = SQLUtilities.query( sql );
			code = ( String )( ( Hashtable )res.elementAt( 0 ) ).get( "prod_prov_codigo" );
		}
		catch( QueryException e )
		{
//			e.printStackTrace();
		}
		catch( Exception e )
		{
			
		}
   		
   		
   		return code;
   }

   public static String getProductName( int id )
   {
   		String code = "";
   		
   		String sql = "SELECT DISTINCT prod_prov_descripcion_corta FROM producto_proveedor WHERE prod_prov_id=" + id + ";";
   		try
		{
			Vector res = SQLUtilities.query( sql );
			code = ( String )( ( Hashtable )res.elementAt( 0 ) ).get( "prod_prov_descripcion_corta" );
		}
		catch( QueryException e )
		{
//			e.printStackTrace();
		}
		catch( Exception e )
		{
			
		}
   		
   		
   		return code;
   }


   public static Vector getSupplierCategories( int supplierId ){
   
       Vector result = new Vector();
       Hashtable aux = new Hashtable();
       Vector categories = new Vector();
       
       String sql = "SELECT DISTINCT cate_prod_nombre " +
                    "FROM categoria_producto, producto_proveedor " +
                    "WHERE categoria_producto.cate_prod_codigo=producto_proveedor.cate_prod_codigo AND " +
                    "producto_proveedor.empr_id="+supplierId+" ORDER BY cate_prod_nombre ASC;";
       
       try{
           
           //System.out.println( sql );
           result = SQLUtilities.query( sql );
           if( result.size() > 0 ){
               for( int i =0; i<result.size(); i++ ){
                
                   aux = ( Hashtable )result.elementAt( i );
                   categories.add(  ( String )aux.get("cate_prod_nombre") );
                   
               }
               
               
           }
       
       }catch( QueryException qe ){
       
           System.out.println( "Query Error "+qe.getMessage() );
           
       }
       
       return categories;
       
   }
   
	public static void main( String[] args )
	{
            Hashtable h = getCompleteBillingTypes();
            Enumeration e = h.elements();
            while(e.hasMoreElements()){
                Vector v = (Vector)e.nextElement();
                for(int i=0; i<v.size(); i++){
                    System.out.println(v.elementAt(i));
                }
            }
	}
	
	
}