<?php
  ////////////////////////////////////////////////////////////////////////////////////////////////////////
  //       Class : class_funciones_db
  // Description : Clase que posee funciones de manejo de configuracion interna de base de datos
  ////////////////////////////////////////////////////////////////////////////////////////////////////////
class covensol_apr_c_funciones_db
{
    var $is_msg_error;
    var $io_database;
    function covensol_apr_c_funciones_db($param=array())//Constructor de la clase.
	{
	    $this->ls_database_source = $_SESSION["ls_database"];
		$this->ls_database_target = $_SESSION["ls_data_des"];
		$this->io_mensajes        = new class_mensajes();		
		$this->io_funciones       = new class_funciones();
		$this->io_validacion      = new class_validacion();
		$this->io_fecha           = new class_fecha();
		$io_conect	              = new sigesp_include_covensol();
		$io_conexion_origen       = $io_conect->uf_conectar();
		$io_conexion_destino      = $io_conect->uf_conectar_otra_bd ($_SESSION["ls_hostname_destino"],$_SESSION["ls_login_destino"],$_SESSION["ls_password_destino"],$this->ls_database_target,$_SESSION["ls_gestor_destino"],$_SESSION["ls_port_destino"]); 
		$this->io_sql_origen      = new class_sql_covensol($io_conexion_origen);
		$this->io_sql_destino 	  = new class_sql_covensol($io_conexion_destino);	   
	} // end contructor


	function SelectColumna($param=array())
	{
	   /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	   //	     Function: SelectColumna
	   //		   Access: public 
	   //		Argumento: $param
	   //	  Description: deternima si existe una columna en una tabla
	   //	   Creado Por: Lic. Edgar A. Quintero
	   //  Fecha Creación: 12/02/2012 								
	   //  Modificado Por: 
	   //    Fecha Modif.: 
	   /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	    $this->io_database  = $_SESSION["ls_database"];
		$this->ls_gestor    = $_SESSION["ls_gestor"];
	    if($this->BdObjetivo=='destino'){
			$this->io_database  = $this->ls_database_target;
			$this->ls_gestor    = $_SESSION["ls_gestor_destino"];
		}
	   
	   switch ($this->ls_gestor)
	   {
	   		case "MYSQLT":
			  $ls_sql = " SELECT COLUMN_NAME 
						 FROM INFORMATION_SCHEMA.COLUMNS 
						 WHERE TABLE_SCHEMA='".$this->io_database."' AND UPPER(TABLE_NAME)=UPPER('".$this->tabla."') AND UPPER(COLUMN_NAME)=UPPER('".$this->columna."')";
			break;
	   		case "POSTGRES":
			  $ls_sql = " SELECT COLUMN_NAME 
						  FROM INFORMATION_SCHEMA.COLUMNS 
						  WHERE table_catalog='".$this->io_database."' AND UPPER(table_name)=UPPER('".$this->tabla."') AND UPPER(column_name)=UPPER('".$this->columna."')";
			break;

	   }
	  
	  if($this->BdObjetivo=='destino'){$rs_data=$this->io_sql_destino->select($ls_sql); $this->sql_msg = $this->io_sql_destino->message;}
	  else{$rs_data=$this->io_sql_origen->select($ls_sql); $this->sql_msg = $this->io_sql_origen->message;}
	  
	  if($rs_data===false){   
         $this->io_msg->message("CLASE->".get_class($this)." MÉTODO->SelectColumna ERROR->".$this->io_funcion->uf_convertirmsg($this->sql_msg));			
		 return false;
	  }
	  
	  $existe = ($rs_data->RecordCount())?true:false;	 
	  return $existe;
	} // end function uf_select_column


	function SelectTabla($param=array())
	{
		   /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
		   //	     Function: SelectTabla
		   //		   Access: public 
		   //		Argumento: $param
		   //	  Description: deternima si existe una Tabla
		   //	   Creado Por: Lic. Edgar A. Quintero
		   //  Fecha Creación: 12/02/2012 								
		   //  Modificado Por: 
		   //    Fecha Modif.: 
		   /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
		   $this->io_database  = $_SESSION["ls_database"];
			$this->ls_gestor    = $_SESSION["ls_gestor"];
			if($this->BdObjetivo=='destino'){
				$this->io_database  = $this->ls_database_target;
				$this->ls_gestor    = $_SESSION["ls_gestor_destino"];
			}
		   switch ($this->ls_gestor)
		   {
				case "MYSQLT":
				   $ls_sql= " SELECT * FROM 
							  INFORMATION_SCHEMA.TABLES 
							  WHERE TABLE_SCHEMA='".$this->io_database."' AND (UPPER(TABLE_NAME)=UPPER('".$this->tabla."'))";				
				break;
				case "POSTGRES":
				   $ls_sql= " SELECT * FROM 
							  INFORMATION_SCHEMA.TABLES 
							  WHERE table_catalog='".$this->io_database."' AND (UPPER(table_name)=UPPER('".$this->tabla."'))";				break;
				
		   }
		   if($this->BdObjetivo=='destino'){$rs_data=$this->io_sql_destino->select($ls_sql); $this->sql_msg = $this->io_sql_destino->message;}
	  	   else{$rs_data=$this->io_sql_origen->select($ls_sql); $this->sql_msg = $this->io_sql_origen->message;}
		   
		   if($rs_data===false){   
			  $this->io_msg->message("CLASE->".get_class($this)." MÉTODO->SelectTabla ERROR->".$this->io_funcion->uf_convertirmsg($this->sql_msg));			
			  return false;
		   }
		  
		   $existe = ($rs_data->RecordCount())?true:false;
		 
		   return $existe;
	} // end function uf_select_table

    function SelectConstraint($param=array())
	{		   
		   /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
		   //	     Function: SelectTabla
		   //		   Access: public 
		   //		Argumento: $param
		   //	  Description: deternima si existe una Constraint
		   //	   Creado Por: Lic. Edgar A. Quintero
		   //  Fecha Creación: 12/02/2012 								
		   //  Modificado Por: 
		   //    Fecha Modif.: 
		   /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
		   $this->io_database  = $_SESSION["ls_database"];
			$this->ls_gestor    = $_SESSION["ls_gestor"];
			if($this->BdObjetivo=='destino'){
				$this->io_database  = $this->ls_database_target;
				$this->ls_gestor    = $_SESSION["ls_gestor_destino"];
			}
		   switch ($this->ls_gestor)
		   {
				case "MYSQLT":
				   $ls_sql=" SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
                              WHERE TABLE_NAME='".$this->tabla."' 
        				      AND CONSTRAINT_NAME='".$this->constrains."'
       					      AND TABLE_SCHEMA='".$this->io_database."'";				   			
				break;
				case "POSTGRES":
				   $ls_sql= " SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS  
                              WHERE TABLE_NAME='".$this->tabla."'
        					  AND CONSTRAINT_NAME='".$this->constrains."'
      						  AND TABLE_CATALOG='".$this->io_database."'";	   
				  
				break;				
		   }
		   if($this->BdObjetivo=='destino'){$rs_data=$this->io_sql_destino->select($ls_sql); $this->sql_msg = $this->io_sql_destino->message;}
	  	   else{$rs_data=$this->io_sql_origen->select($ls_sql); $this->sql_msg = $this->io_sql_origen->message;}

		   if($rs_data===false){   
			  $this->io_msg->message("CLASE->".get_class($this)." MÉTODO->SelectConstraint ERROR->".$this->io_funcion->uf_convertirmsg($this->sql_msg));			
			  return false;
		   }
		  
		   $existe = ($rs_data->RecordCount())?true:false;
		 
		   return $existe;
   }///fin  uf_select_constraint

//------------------------------------------------------------------------------------------------------------------------------------
	function SelectTypeColumn($param=array())
	{
	   /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	   //	     Function: SelectTypeColumn
	   //		   Access: public 
	   //		Argumento: $param
	   //	  Description: deternima el tipo de datos de la columna en una tabla
	   //	   Creado Por: Lic. Edgar A. Quintero
	   //  Fecha Creación: 12/02/2012 								
	   //  Modificado Por: 
	   //    Fecha Modif.: 
	   /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        $this->io_database  = $_SESSION["ls_database"];
		$this->ls_gestor    = $_SESSION["ls_gestor"];
	    if($this->BdObjetivo=='destino'){
			$this->io_database  = $this->ls_database_target;
			$this->ls_gestor    = $_SESSION["ls_gestor_destino"];
		}
	   switch ($this->ls_gestor)
	   {
	   		case "MYSQLT":
			  $ls_sql = " SELECT DATA_TYPE                                    
						   FROM INFORMATION_SCHEMA.COLUMNS                   
						  WHERE TABLE_SCHEMA='".$this->io_database."'        
						    AND UPPER(TABLE_NAME)=UPPER('".$this->tabla."')     
						    AND UPPER(COLUMN_NAME)=UPPER('".$this->columna."')  
						    AND UPPER(DATA_TYPE)=UPPER('".$this->type."')          ";
			break;
	   		case "POSTGRES":
			  $ls_sql = " SELECT DATA_TYPE                                   
						   FROM INFORMATION_SCHEMA.COLUMNS                  
						  WHERE table_catalog='".$this->io_database."'      
						    AND UPPER(table_name)=UPPER('".$this->tabla."')    
						    AND UPPER(column_name)=UPPER('".$this->columna."') 
						    AND UPPER(DATA_TYPE)=UPPER('".$this->type."')         "; 
			break;	   		
	   }	  
	   if($this->BdObjetivo=='destino'){$rs_data=$this->io_sql_destino->select($ls_sql); $this->sql_msg = $this->io_sql_destino->message;}
	   else{$rs_data=$this->io_sql_origen->select($ls_sql); $this->sql_msg = $this->io_sql_origen->message;}
	   
	   if($rs_data===false){   
		  $this->io_msg->message("CLASE->".get_class($this)." MÉTODO->SelectTypeColumn ERROR->".$this->io_funcion->uf_convertirmsg($this->sql_msg));			
		  return false;
	   }
	  
	   $existe = ($rs_data->RecordCount())?true:false;
	 
	   return $existe;
	} // uf_select_type_columna
//------------------------------------------------------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------------------------------------------------------
   function SelectVista($param=array())
	{
	    /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	   //	     Function: SelectTypeColumn
	   //		   Access: public 
	   //		Argumento: $param
	   //	  Description: deternima si existe una vista existe en la base de datos
	   //	   Creado Por: Lic. Edgar A. Quintero
	   //  Fecha Creación: 12/02/2012 								
	   //  Modificado Por: 
	   //    Fecha Modif.: 
	   /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        $this->io_database  = $_SESSION["ls_database"];
		$this->ls_gestor    = $_SESSION["ls_gestor"];
	    if($this->BdObjetivo=='destino'){
			$this->io_database  = $this->ls_database_target;
			$this->ls_gestor    = $_SESSION["ls_gestor_destino"];
		}
	   switch ($this->ls_gestor)
	   {
	   		case "MYSQLT":
			   $ls_sql= " SELECT table_name 
			               FROM INFORMATION_SCHEMA.VIEWS 						
						  WHERE TABLE_SCHEMA='".$this->io_database."'  
						    AND (UPPER(TABLE_NAME)=UPPER('".$this->vista."'))";				
			break;
	   		case "POSTGRES":
			   $ls_sql= " SELECT table_name 
			               FROM INFORMATION_SCHEMA.VIEWS 
						  WHERE table_catalog='".$this->io_database."'
						    AND table_schema='public'
						    AND (UPPER(TABLE_NAME)=UPPER('".$this->vista."'))";			            
			break;
	   		
	   }
	  
	  if($this->BdObjetivo=='destino'){$rs_data=$this->io_sql_destino->select($ls_sql); $this->sql_msg = $this->io_sql_destino->message;}
	  else{$rs_data=$this->io_sql_origen->select($ls_sql); $this->sql_msg = $this->io_sql_origen->message;}

	   if($rs_data===false){   
		  $this->io_msg->message("CLASE->".get_class($this)." MÉTODO->SelectVista ERROR->".$this->io_funcion->uf_convertirmsg($this->sql_msg));			
		  return false;
	   }
	  
	   $existe = ($rs_data->RecordCount())?true:false;
	 
	   return $existe;
	} // end function uf_select_vista  
//------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------
    function SelectTamanoTipoColumn($param=array())
	{	   
	   /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	   //	     Function: SelectTamanoTipoColumn
	   //		   Access: public 
	   //		Argumento: $param
	   //	  Description: deternima el tipo de datos de la columna en una tabla
	   //	   Creado Por: Lic. Edgar A. Quintero
	   //  Fecha Creación: 12/02/2012 								
	   //  Modificado Por: 
	   //    Fecha Modif.: 
	   /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        $this->io_database  = $_SESSION["ls_database"];
		$this->ls_gestor    = $_SESSION["ls_gestor"];
	    if($this->BdObjetivo=='destino'){
			$this->io_database  = $this->ls_database_target;
			$this->ls_gestor    = $_SESSION["ls_gestor_destino"];
		}
	   $tamano=0;
	   switch ($this->ls_gestor)
	   {
	   		case "POSTGRES":
			  $ls_sql =" SELECT a.attname as nomcolum,      
					   		t.typname as type,          
					   		CASE WHEN a.attlen='-1'     
					   			 THEN (a.atttypmod - 4) 
					   			 ELSE a.attlen          
					   		 END as tamano              
					     FROM pg_catalog.pg_attribute a    
					     LEFT JOIN pg_catalog.pg_type t ON t.oid = a.atttypid   
					     LEFT JOIN pg_catalog.pg_class c ON c.oid = a.attrelid  
					     LEFT JOIN pg_catalog.pg_constraint cc ON cc.conrelid = c.oid AND cc.conkey[1] = a.attnum 
					     LEFT JOIN pg_catalog.pg_attrdef d ON d.adrelid = c.oid AND a.attnum = d.adnum            
					     WHERE c.relname ='".$this->tabla."'                                                         
					   	AND a.attname= '".$this->columna."'                                                       
					   	AND a.attnum > 0                                                                       
					   	AND t.oid = a.atttypid ";			  
				
			   if($this->BdObjetivo=='destino'){$rs_data=$this->io_sql_destino->select($ls_sql); $this->sql_msg = $this->io_sql_destino->message;}
	  	       else{$rs_data=$this->io_sql_origen->select($ls_sql); $this->sql_msg = $this->io_sql_origen->message;}

			   if($rs_data===false){   
				  $this->io_msg->message("CLASE->".get_class($this)." MÉTODO->SelectTamanoTipoColumn ERROR->".$this->io_funcion->uf_convertirmsg($this->sql_msg));			
				  return false;
			   }
			  
			  break;     		
	   }	 
	  return $rs_data->fields;
	} // uf_select_type_columna
//---------------------------------------------------------------------------------------------------------------------------------------

	function SelectIndex($param=array())
	{
		   /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
		   //	     Function: SelectIndex
		   //		   Access: public 
		   //		Argumento: $param
		   //	  Description: Devuelve true si existe un indice
		   //	   Creado Por: Lic. Edgar A. Quintero
		   //  Fecha Creación: 12/02/2012 								
		   //  Modificado Por: 
		   //    Fecha Modif.: 
		   /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
		   $this->io_database  = $_SESSION["ls_database"];
			$this->ls_gestor    = $_SESSION["ls_gestor"];
			if($this->BdObjetivo=='destino'){
				$this->io_database  = $this->ls_database_target;
				$this->ls_gestor    = $_SESSION["ls_gestor_destino"];
			}
		  switch($_SESSION["ls_gestor"]){
			case "MYSQLT":
			  $ls_sql = "SELECT DISTINCT INDEX_NAME 
						   FROM information_schema.STATISTICS 
						  WHERE TABLE_SCHEMA = '".$_SESSION["ls_database"]."'
							AND INDEX_SCHEMA = '".$_SESSION["ls_database"]."'
							AND TABLE_NAME = '".$this->tabla."'
							AND INDEX_NAME = '".$this->index."';";
			break;
			case "POSTGRES":
			  $ls_sql = "SELECT relname FROM pg_catalog.pg_class WHERE relname= '".$this->index."'";		
			break;	 			  
		  }
		   
		   if($this->BdObjetivo=='destino'){$rs_data=$this->io_sql_destino->select($ls_sql); $this->sql_msg = $this->io_sql_destino->message;}
	  	   else{$rs_data=$this->io_sql_origen->select($ls_sql); $this->sql_msg = $this->io_sql_origen->message;}
		   
		   if($rs_data===false){   
			  $this->io_msg->message("CLASE->".get_class($this)." MÉTODO->SelectIndex ERROR->".$this->io_funcion->uf_convertirmsg($this->sql_msg));			
			  return false;
		   }
		  
		   $existe = ($rs_data->RecordCount())?true:false;
		 
		   return $existe;
	}
} // end class_funcrions_db 
?>