<?php
/**
* @author Gustavo Mena
* Manipulated records from a table using ODBC connection with PROGRESS DB
*
*/
class abm {
/**
Fields
*/
var $con_id; //Resource connection
var $tabla='';//
/*Array Fields Campos
0-label,
1-readonly,
2-get_descrip,
3-table,
4-field,
5-field_filter,
6-is_group
7-group_name
8-width
9-margins
10-validate_data
*/
var $campos=array();
var $primary_key=array();//Primary Keys
var $nro_registros=1; //Limit Rows
var $where='';//Where condition
var $titulo='';//Title
var $order_by='';//Order by
var $botones_activos=array();//Buttons Enabled
var $buscar_por=array();//Search By
var $tipo_busqueda='E';//E:Begin C:Contain T:End I:Equal
var $campos_a_validar=array();//Fields to validate
var $remitentes='hide@address.com';//E-amil SQL Error
var $ancho_frame_pr='900';//width fieldset
var $font_size='15';//Font Size
/**
* Constructor
*/
function abm(){
// Register the destructor
register_shutdown_function(array( &$this, "__destroy" ));
}
/**
* Destructor
*/
// Create Destructor
function __destroy() {
}
/**
* Method that updates the view
*/
function controlaUpdate() {
#Filter Update Query
if(isset($_GET['update'])){
//Simple Primary Key
if(count($this->getPrimaryKey())==1){
$dataType=$this->getFieldType($this->tabla,$this->primary_key[0]);
switch($dataType){
case 'CHARACTER':
$this -> setWhere($this->primary_key[0]."='".$_GET['update']."'");
break;
case 'INTEGER': case 'DECIMAL':
$this -> setWhere($this->primary_key[0].'='.$_GET['update']);
break;
}
}else{
//Primary key complex
$idx=0;
$pkey=explode(',',$_GET['update']);
$cadena_where='';
foreach($this->getPrimaryKey() as $cl){
$dataType=$this->getFieldType($this->tabla,$cl);
switch($dataType){
case 'CHARACTER':
if($pkey[$idx]=='0'){
$pkey[$idx]='';
}
if($idx==0){
$cadena_where=" $cl='$pkey[$idx]' ";
}else{
$cadena_where.=" AND $cl='$pkey[$idx]' ";
}
break;
case 'INTEGER': case 'DECIMAL':
if($idx==0){
$cadena_where=" $cl=$pkey[$idx] ";
}else{
$cadena_where.=" AND $cl=$pkey[$idx] ";
}
break;
}
$idx++;
}
//set filter update
$this -> setWhere($cadena_where);
}
}
#Filter to save Update
if(isset($_GET['save'])){
//Simple Primary Key
if(count($this->getPrimaryKey())==1){
$dataType=$this->getFieldType($this->tabla,$this->primary_key[0]);
switch($dataType){
case 'CHARACTER':
$this -> setWhere($this->primary_key[0]."='".$_GET['pkey']."'");
break;
case 'INTEGER': case 'DECIMAL':
$this -> setWhere($this->primary_key[0].'='.$_GET['pkey']);
break;
}
}else{
//Primary Key Complex
$idx=0;
$pkey=explode(',',$_GET['pkey']);
$cadena_where='';
foreach($this->getPrimaryKey() as $cl){
$dataType=$this->getFieldType($this->tabla,$cl);
switch($dataType){
case 'CHARACTER':
if($pkey[$idx]=='0'){
$pkey[$idx]='';
}
if($idx==0){
$cadena_where=" $cl='$pkey[$idx]' ";
}else{
$cadena_where.=" AND $cl='$pkey[$idx]' ";
}
break;
case 'INTEGER': case 'DECIMAL':
if($idx==0){
$cadena_where=" $cl=$pkey[$idx] ";
}else{
$cadena_where.=" AND $cl=$pkey[$idx] ";
}
break;
}
$idx++;
}
//Set filter update
$this -> setWhere($cadena_where);
}
}
#Filter Search
if(isset($_GET['buscar'])){
if(count($this->getPrimaryKey())==1){
$dataType=$this->getFieldType($this->tabla,$_GET['buscar_por']);
switch($dataType){
case 'CHARACTER':
switch($this->getTipoBusqueda()){
case 'E':
$this -> setWhere($_GET['buscar_por']." LIKE '".$_GET['valor_buscar']."%'");
break;
case 'T':
$this -> setWhere($_GET['buscar_por']." LIKE '%".$_GET['valor_buscar']."'");
break;
case 'C':
$this -> setWhere($_GET['buscar_por']." LIKE '%".$_GET['valor_buscar']."%'");
break;
case 'I':
$this -> setWhere($_GET['buscar_por']."='".$_GET['valor_buscar']."'");
break;
}
break;
case 'INTEGER': case 'DECIMAL':
$this -> setWhere($_GET['buscar_por']."=".$_GET['valor_buscar']);
break;
}
}else{
//Primary key Simple
$idx=0;
$cadena_where='';
foreach($this->buscar_por as $k_campo_buscar=>$campo_buscar){
$dataType=$this->getFieldType($this->tabla,$k_campo_buscar);
switch($dataType){
case 'CHARACTER':
if($pkey[$idx]=='0'){
$pkey[$idx]='';
}
if($idx==0){
$cadena_where.=" $k_campo_buscar='".$_GET[$k_campo_buscar]."'";
}else{
$cadena_where.=" AND $k_campo_buscar='".$_GET[$k_campo_buscar]."'";
}
break;
case 'INTEGER': case 'DECIMAL':
if($idx==0){
$cadena_where.=" $k_campo_buscar=".$_GET[$k_campo_buscar];
}else{
$cadena_where.=" AND $k_campo_buscar=".$_GET[$k_campo_buscar];
}
break;
}
$idx++;
}
//Set filter update
$this -> setWhere($cadena_where);
}
}
#Exec update
if(isset($_GET['save'])){
$columnas=array();
foreach($_GET as $clave=>$valor){
if($clave!='save' and $clave!='pkey'){
//Data Type
$dataType=$this->getFieldType($this->tabla,$clave);
switch($dataType){
case 'CHARACTER':
$cadena_update="$clave='$valor'";
break;
case 'LOGICAL':
if($valor==1){
$cadena_update="$clave=yes";
}elseif($valor==0){
$cadena_update="$clave=no";
}
break;
case 'INTEGER': case 'DECIMAL':
$cadena_update="$clave=$valor";
break;
case 'DATE':
if($valor!=''){
$valor=split('/',$valor);
$valor=$valor[2].'-'.$valor[1].'-'.$valor[0];
$cadena_update="$clave={d '$valor'}";
}else{
$cadena_update="$clave=NULL";
}
break;
}
$columnas[$clave]=$cadena_update;
}
}
$this->exec_Update($columnas);
}
#Exec Insert
if(isset($_GET['insert_save'])){
$cadenas_insert=array();
$cadena_insert='';
$columnas_insert=array();
foreach($_GET as $clave=>$valor){
if($clave!='insert_save' and $clave!='pkey'){
//Data Type
$dataType=$this->getFieldType($this->tabla,$clave);
switch($dataType){
case 'CHARACTER':
$cadena_insert="'$valor'";
break;
case 'LOGICAL':
if($valor==1){
$cadena_insert="yes";
}elseif($valor==0){
$cadena_insert="no";
}
break;
case 'INTEGER': case 'DECIMAL':
$cadena_insert="$valor";
break;
case 'DATE':
if($valor!=''){
$valor=split('/',$valor);
$valor=$valor[2].'-'.$valor[1].'-'.$valor[0];
$cadena_insert="{d '$valor'}";
}else{
$cadena_insert='NULL';
}
break;
}
$cadenas_insert[$clave]=$cadena_insert;
$columnas_insert[]=$clave;
}
}
//print_r($cadenas_insert);
//print_r($columnas_insert);
$this->exec_Insert($columnas_insert,$cadenas_insert);
}
}
/**
* Data Base Connection
*/
function conectar($strServer, $strUsername, $strPassword){
//Enviroment
putenv("DYLD_LIBRARY_PATH=/usr/local/opl/lib");
putenv("ODBCINSTINI=/etc/odbcinst.ini");
putenv("ODBCINI=/etc/odbc.ini");
$this->con_id = odbc_connect($strServer, $strUsername, $strPassword,SQL_CUR_USE_ODBC);
}
/**
* Data BaseDisconnection
*/
function desconectar(){
odbc_close($this->con_id);
}
/**
* Set Table Name
*/
function setTabla($nombre){
$this->tabla=$nombre;
}
/**
* Set Primary Key
*/
function setPrimaryKey($valor){
$this->primary_key=$valor;
}
/**
* Getter Primary Key
*/
function getPrimaryKey(){
return $this->primary_key;
}
/**
*Set Number Row (MySQL:LIMIT)
*/
function setNroRegistros($nro){
$this->nro_registros=$nro;
}
/**
*Set Title
*/
function setTitulo($valor){
$this->titulo=$valor;
}
/**
* Get Title
*/
function getTitulo(){
return $this->titulo;
}
/**
*Exec Query SQL
*/
function consulta(){
$columnas=array();
foreach($this->campos as $clave=>$valor){
$columna=$clave;
//echo "$columna = $valor<br>";
$columnas[]=$columna;
}
$campos = implode(",", $columnas);
$sql="SELECT $campos FROM $this->tabla $this->where $this->order_by";
/**
*Prepare Statement
$result=odbc_prepare($this->con_id, $sql);
odbc_setoption($result, 2, 1,$this->nro_registros);
odbc_execute($result);
*/
$result=odbc_exec($this->con_id,$sql);
if(!$result){
trigger_error('Error ABM:'.$sql);
echo"<div align='center' class='tdrojo' id=\"msg_err_bucar\">Row No Found</div>";
$sql="SELECT $campos FROM $this->tabla $this->order_by";
$result=odbc_exec($this->con_id,$sql);
return($result);
}else{
//trigger_error($sql);
$contador_select=0;
$row_select=odbc_fetch_array($result);
$contador_select=odbc_num_rows($result);
if($contador_select==0){
echo"<div align='center' class='tdrojo' id=\"msg_err_bucar\">Row No Found</div>";
$sql="SELECT $campos FROM $this->tabla $this->order_by";
$result=odbc_exec($this->con_id,$sql);
}
return($result);
}
}
/**
*Set WHERE (SQL)
*/
function setWhere($filtro){
$this->where='WHERE '.$filtro;
}
/**
* Set ORDER By
* @param string $orden
*/
function setOrderBy($orden){
$this->order_by='ORDER BY '.$orden;
}
/**
*Draw DataGrid
*/
function dibujaGrilla($result){
#Row Browser
if(!isset($_GET['r']) or $_GET['primero']==1){
$r=$_GET['r'];
$r=1;
}elseif($_GET['n']==1){
$r=$_GET['r']+1;
}elseif($_GET['n']==0){
$r=$_GET['r']-1;
}
if(isset($_GET['ultimo'])){
#Count Rows
$sql_count="select count(*) from $this->tabla";
$result_count=odbc_exec($this->con_id,$sql_count);
while($row_count=odbc_fetch_array($result_count)){
$total_reg=$row_count['COUNT'];
}
$r=$total_reg;
}
#Fetch a Row
//$r = row number
odbc_fetch_row($result,$r);
#Get number of columns in a result
$campo = odbc_num_fields($result);
$pk='';
$pk_value='';
$array_update=array();
#Form UPDATE
if(isset($_GET['update'])){
echo "<form method='get' name='update' action='' enctype=\"multipart/form-data\">";
}
#Form INSERT
if(isset($_GET['insert'])){
echo "<form method='get' name='insert' action='' enctype=\"multipart/form-data\">";
}
echo "<fieldset style=width:".$this->getAnchoFrame().";font-size:".$this->getFontSize()."><legend align=\"center\">".$this->getTitulo()."</legend>";
//Field with the focus
//Always the first
$campo_foco='';
$j=0;
$asigno_foco=0;
$pk_compuestas=array();
for($i=1;$i<=$campo;$i++){
#Parameters of Field
$valores=split(',',$this->campos[odbc_field_name($result,$i)]);
$label=$valores[0];
$is_readonly=$valores[1];
//get Column Size
$column_size=$this->getPropiedadTabla("COLUMN_SIZE",odbc_field_name($result,$i));
$size=$column_size;
$maxlength=$column_size;
$trae_descrip=$valores[2];
$table_descrip=$valores[3];
$field_descrip=$valores[4];
$field_filter=$valores[5];
$is_group=$valores[6];
$group_name=$valores[7];
$ancho_tabla=$valores[8];
$margenes=$valores[9];
$valida_dato=$valores[10];
#IF is_group is true the creates fieldset
if($is_group==1){
echo "</fieldset>";
echo "<fieldset style=\"width:$ancho_tabla;font-size:".$this->getFontSize().";$margenes\">";
echo "<legend>$group_name</legend>";
}
$array_update[odbc_field_name($result,$i)]=odbc_result($result,$i);
$valor=odbc_result($result,$i);
//get Description
$descrip='';
if($trae_descrip==1){
$descrip=$this->getDescrip($table_descrip,$field_descrip,$field_filter,$valor);
}
$tipoDato=$this->getFieldType($this->tabla,odbc_field_name($result,$i));
switch($tipoDato){
case 'DATE':
if($valor!=''){
$valor=split('-',$valor);
$valor=$valor[1].'/'.$valor[2].'/'.$valor[0];
}else{
$valor='';
}
break;
case 'LOGICAL':
if($valor==1){
$valor='YES';
}else{
$valor='NO';
}
break;
}
if(!isset($_GET['update']) and !isset($_GET['insert'])){
if($valor==''){
$valor='-';
}
echo "<b>$label</b> $valor $descrip<br>";
}elseif(isset($_GET['update'])){
#Draw UPDATE
if($is_readonly==0 and $asigno_foco==0){
$campo_foco=odbc_field_name($result,$i);
$asigno_foco=1;
}
echo "<b>$label</b>";
//Is Red Only
if($is_readonly==1){
echo"$valor<br>";
}else{
//if is_select is true then draw the combo
$is_select=0;
switch($tipoDato){
case 'LOGICAL':
$is_select=1;
break;
}
if($is_select==1){
echo"<select name='".odbc_field_name($result,$i)."'>";
if($valor=='SI'){
echo"<option value='1' selected>YES</option>";
echo"<option value='0' >NO</option>";
}else{
echo"<option value='1' >YES</option>";
echo"<option value='0' selected>NO</option>";
}
echo"</select>";
echo"<br>";
}else{
echo "<input name=\"".odbc_field_name($result,$i)."\" type=\"text\" value=\"$valor\" size=$size maxlength=$maxlength onBlur=\"this.style.backgroundColor='#05286B';this.style.color='#FFFFFF'\" onFocus=\"this.style.backgroundColor='#FFFFFF';this.style.color='#000000'\" style=\"background-color: #05286B;border-width: 1;color: #FFFFFF;font-size: 9pt;font-family: arial\" >";
echo "<br>";
}
}
}elseif(isset($_GET['insert'])){
##Draw Insert
if($is_readonly==0 and $asigno_foco==0){
$campo_foco=odbc_field_name($result,$i);
$asigno_foco=1;
}
echo "<b>$label</b>";
$is_select=0;
switch($tipoDato){
case 'LOGICAL':
$is_select=1;
break;
}
if($is_select==1){
echo"<select name='".odbc_field_name($result,$i)."'>";
echo"<option value='1' >YES</option>";
echo"<option value='0' >NO</option>";
echo"</select>";
echo"<br>";
}else{
if($is_readonly==0){
echo "<input name=\"".odbc_field_name($result,$i)."\" type=\"text\" value=\"\" size=$size maxlength=$maxlength onBlur=\"this.style.backgroundColor='#05286B';this.style.color='#FFFFFF'\" onFocus=\"this.style.backgroundColor='#FFFFFF';this.style.color='#000000'\" style=\"background-color: #05286B;border-width: 1;color: #FFFFFF;font-size: 9pt;font-family: arial\">";
}else{
echo "-";
}
echo"<br>";
}
}
#
#check type key
#
// primary key simple
if(count($this->getPrimaryKey())==1){
$pk=strtolower(odbc_field_name($result,$i));
if($pk==strtolower($this->primary_key[0])){
$pk_value=odbc_result($result,$i);
if($pk_value==''){
$pk_value=0;
}
}
}else{
//primary key complex
$pk=strtolower(odbc_field_name($result,$i));
foreach($this->getPrimaryKey() as $k_comp){
if($pk==strtolower($k_comp)){
$pk_valor=odbc_result($result,$i);
if($pk_valor==''){
$pk_valor=0;
}
$pk_compuestas[]=$pk_valor;
}
}
//string with values of primary key complex
$pk_value=implode(',',$pk_compuestas);
}
}
echo"</fieldset>";
//Show the buttons
$botones='<div id="botones" align="center">';
$botones.='<table>';
$botones.='<tr>';
if(!isset($_GET['update']) and !isset($_GET['insert'])){
$arreglo_botones=$this->botones_activos;
if($arreglo_botones['navegador']==true){
$botones.="<td align='center' class='titulo'><a href=$PHP_SELF?primero=1> |< </a></td>";
$botones.="<td align='center' class='titulo'><a href=$PHP_SELF?r=$r&n=0> << </a></td>";
$botones.="<td align='center' class='titulo'><a href=$PHP_SELF?r=$r&n=1> >> </a></td>";
$botones.="<td align='center' class='titulo'><a href=$PHP_SELF?ultimo=1> >| </a></td>";
}
if($arreglo_botones['insert']==true){
$botones.="<td align='center' class='titulo'><a href=$PHP_SELF?insert=1> Add </a></td>";
}
if($arreglo_botones['delete']==true){
$botones.="<td align='center' class='titulo'><a href=$PHP_SELF?delete=$pk_value> Delete </a></td>";
}
if($arreglo_botones['update']==true){
$botones.="<td align='center' class='titulo'><a href=$PHP_SELF?update=$pk_value> Edit </a></td>";
}
if($arreglo_botones['buscar']==true){
//Search with the pkey simple
if(count($this->getPrimaryKey())==1){
$botones.="<form name=\"buscar\" method=\"get\" enctype=\"multipart/form-data\">";
$botones.="<select name=\"buscar_por\">";
foreach($this->buscar_por as $item_buscar=>$label_buscar){
$botones.="<option value=\"$item_buscar\">$label_buscar</option>";
}
$botones.="</select>";
$botones.="<input type=\"text\" onBlur=\"this.style.backgroundColor='FFFFFF';\" onFocus=\"this.style.backgroundColor='FFFFBB';\" name=\"valor_buscar\" size=\"10\">";
$botones.="<input type='submit' value='Buscar' name='buscar'>";
$botones.="</form>";
}else{
//Search with the pkey complex
$botones.="<form name=\"buscar\" method=\"get\" enctype=\"multipart/form-data\">";
$botones.="<fieldset style=width:300;font-size:12><legend>Search</legend>";
$c_buscar=0;
foreach($this->buscar_por as $item_buscar=>$label_buscar){
$column_size=$this->getPropiedadTabla("COLUMN_SIZE",$item_buscar);
$botones.="$label_buscar:<input type=\"text\" onBlur=\"this.style.backgroundColor='FFFFFF';\" onFocus=\"this.style.backgroundColor='FFFFBB';\" name=\"$item_buscar\" maxlength=\"$column_size\" size=\"$column_size\">";
$botones.="<br>";
//Focus to search
if($c_buscar==0){
$campo_foco_buscar=$item_buscar;
}
$c_buscar++;
}
$botones.="<input type='hidden' name='valor_buscar'>";
$botones.="<input type='submit' value='Search' name='buscar'>";
$botones.="</fieldset>";
$botones.="</form>";
}
}
}elseif(isset($_GET['update'])){
## UPDATE
$botones.="<td align='center'><input type='submit' value='Cancel' name='cancel'></td>";
$botones.="<td align='center'><input type='submit' value='Save' name='save'></td>";
$botones.="<input type=\"hidden\" name=\"pkey\" value=\"$pk_value\" >";
$botones.="</form>";
}elseif(isset($_GET['insert'])){
## INSERT
$botones.="<td align='center' ><input type='submit' value='Cancel' name='cancel'></td>";
$botones.="<td align='center' ><input type='submit' value='Save' name='insert_save'></td>";
$botones.="<input type=\"hidden\" name=\"pkey\" value=\"$pk_value\" >";
$botones.="</form>";
}
$botones.='</tr>';
$botones.='</table>';
$botones.='</div>';
echo $botones;
#Deletes a record
if(isset($_GET['delete'])){
$filtro_del=$_GET['delete'];
if(count($this->getPrimaryKey())==1){
$tipoDato=$this->getFieldType($this->tabla,$this->primary_key[0]);
switch($tipoDato){
case 'DATE':
break;
case 'LOGICAL':
break;
case 'DECIMAL': case 'INTEGER':
$where_del='WHERE '.$this->primary_key[0].'='.$filtro_del;
break;
case 'CHARACTER':
$where_del='WHERE '.$this->primary_key[0]."='$filtro_del'";
break;
}
}else{
//pkey complex
$idx=0;
$pkey=explode(',',$filtro_del);
$where_del='WHERE ';
foreach($this->getPrimaryKey() as $cl){
$dataType=$this->getFieldType($this->tabla,$cl);
switch($dataType){
case 'CHARACTER':
if($pkey[$idx]=='0'){
$pkey[$idx]='';
}
if($idx==0){
$where_del.=" $cl='$pkey[$idx]' ";
}else{
$where_del.=" AND $cl='$pkey[$idx]' ";
}
break;
case 'INTEGER': case 'DECIMAL':
if($idx==0){
$where_del.=" $cl=$pkey[$idx] ";
}else{
$where_del.=" AND $cl=$pkey[$idx] ";
}
break;
}
$idx++;
}
}
$sql_delete="DELETE FROM $this->tabla $where_del";
//$result_delete=odbc_exec($this->con_id,$sql_delete);
echo $sql_delete;
if(!$result_delete){
trigger_error('Error ABM: '.$sql_delete);
$this->enviar_correo($sql_delete);
echo"<div align='center' class='tdrojo' id=\"msg_error\">Error al borrar ". $this->primary_key[0] ." ".$pkey[0]."</div>";
}else{
echo"<div align='center' class='tdverde' id=\"msg_error\">Se borro ".$this->primary_key[0]." ".$pkey[0]."</div>";
}
}
//Control de Foco en el update
if(isset($_GET['update'])){
echo "<script>";
echo "document.update.$campo_foco.focus();";
echo "document.update.$campo_foco.select();";
echo "</script>";
}elseif(isset($_GET['insert'])){
echo "<script>";
echo "document.insert.$campo_foco.focus();";
echo "</script>";
}elseif(!isset($_GET['update']) and !isset($_GET['insert'])){
echo "<script>";
if(count($this->getPrimaryKey())==1){
echo "document.buscar.valor_buscar.focus();";
}else{
echo "document.buscar.$campo_foco_buscar.focus();";
}
echo "</script>";
}
}
/**
*Buttons
*/
function dibujarBotones($navegador=false,$insert=false,$update=false,$delete=false,$ver=false,$buscar=false){
if($navegador==true){
$this->botones_activos['navegador']=true;
}
if($insert==true){
$this->botones_activos['insert']=true;
}
if($update==true){
$this->botones_activos['update']=true;
}
if($delete==true){
$this->botones_activos['delete']=true;
}
if($ver==true){
$this->botones_activos['ver']=true;
}
if($buscar==true){
$this->botones_activos['buscar']=true;
}
}
/**
*Exec UPDATE
*/
function exec_Update($columnas){
$cont=0;
# Set Fields to Valide
$this -> setCamposValidar();
foreach($columnas as $field){
$fields=array();
$fields=split('=',$field);
$clave=$fields[0];
$dato=$fields[1];
if(array_key_exists($clave, $this->campos_a_validar)) {
$tabla_v=$this->campos_a_validar[$clave]['tabla'];
$campo_v=$this->campos_a_validar[$clave]['campo'];
$filtro_v=$this->campos_a_validar[$clave]['filtro'];
$dato_v=$dato;
$bool=$this->consultaValidar($tabla_v,$campo_v,$filtro_v,$dato_v);
if($bool==false){
echo "<div id=\"valida_$clave\" class=\"tdrojo\" align=\"center\">$dato_v: ".strtoupper($filtro_v)." no valido</div>";
$cont++;
}
}
}
if($cont==0){
$campos = implode(",", $columnas);
$sql="UPDATE $this->tabla SET $campos $this->where";
echo $sql;
//$result=odbc_exec($this->con_id,$sql);
if(!$result){
trigger_error('Error ABM:'.$sql);
$this->enviar_correo($sql);
echo "<div id=\"err_update\" class=\"tdrojo\" align=\"center\">Error al actualizar</div>";
}else{
echo "<div id=\"ok_update\" class=\"tdverde\" align=\"center\">Actulizacion realizada con exito</div>";
}
}
}
/**
* Exec INSERT
*/
function exec_Insert($columnas,$columnas_insert){
$cont=0;
# Verifica campos a validar
$this -> setCamposValidar();
foreach($columnas as $field){
if(array_key_exists($field, $this->campos_a_validar)){
$tabla_v=$this->campos_a_validar[$field]['tabla'];
$campo_v=$this->campos_a_validar[$field]['campo'];
$filtro_v=$this->campos_a_validar[$field]['filtro'];
$dato_v=$columnas_insert[$field];
$bool=$this->consultaValidar($tabla_v,$campo_v,$filtro_v,$dato_v);
if($bool==false){
echo "<div id=\"$clave\" class=\"tdrojo\" align=\"center\">$dato_v: ".strtoupper($filtro_v)." no valido</div>";
$cont++;
}
}
}
if($cont==0){
$campos = implode(",", $columnas);
$values = implode(",", $columnas_insert);
$sql="INSERT INTO $this->tabla($campos) VALUES($values)";
echo "$sql<br>";
//$result=odbc_exec($this->con_id,$sql);
if(!$result){
trigger_error('Error ABM:'.$sql);
$this->enviar_correo($sql);
echo"<div align='center' class='tdrojo' id=\"msg_err_insert\">Error al agregar nuevo registro</div>";
}else{
echo"<div align='center' class='ver' id=\"msg_ok_insert\">Registro agregado con exito</div>";
}
}
}
/**
* Set Search fields
*/
function setBuscarPor($arreglo_buscar){
$this->buscar_por=$arreglo_buscar;
}
/**
* Get Data Type Column
@author Gustavo Mena
@param string $tabla
@param string $columna
@return string $tipo Return: CHARACTER,LOGICAL, INTEGER,DECIMAL,DATE
*/
function getFieldType($tabla,$columna){
$res=odbc_columns($this->con_id,"DSN_NAME","","$tabla","$columna");
while(odbc_fetch_row($res))
{
$tipoDato=odbc_result($res,"TYPE_NAME");
}
return $tipoDato;
}
/**
* get descrip
* @param string $table
* @param string $field
* @param string $fieldfiltro
* @param string $filtro
* @return string $descrip
*/
function getDescrip($table,$field,$fieldfiltro,$filtro){
if($filtro!=""){
$tDato=$this->getFieldType($table,$fieldfiltro);
switch($tDato){
case 'CHARACTER':
$where="WHERE $fieldfiltro='$filtro'";
break;
case 'INTEGER': case 'DECIMAL':
$where="WHERE $fieldfiltro=$filtro";
break;
}
$sql="SELECT $field FROM $table $where";
$res=odbc_exec($this->con_id,$sql);
if(!$res){
trigger_error('Error ABM:'.$sql);
}
$c_descrip=0;
while($row=odbc_fetch_array($res)){
$descrip=$row[$field];
$c_descrip++;
}
if($c_descrip!=0){
$descrip=' - '.$descrip;
return $descrip;
}
}
}
/**
* Setter Type of search
*/
function setTipoBusqueda($tipo){
$this->tipo_busqueda=$tipo;
}
/**
* Getter Type of search
*/
function getTipoBusqueda(){
return $this->tipo_busqueda;
}
/**
* Setter To validate fields
*/
function setCamposValidar(){
$valores=array();
foreach($this->campos as $k=>$v){
$valores=split(',',$v);
if($valores[10]==1){
$this->campos_a_validar[$k]['tabla']=$valores[3];
$this->campos_a_validar[$k]['campo']=$valores[4];
$this->campos_a_validar[$k]['filtro']=$valores[5];
}
}
}
/**
* Getter To validate fields
*/
function getCamposValidar(){
return $this->campos_a_validar;
}
/**
* Query to validate data
*/
function consultaValidar($tabla,$campo,$filtro,$dato){
$c=0;
$sql="SELECT $campo FROM $tabla WHERE $filtro=$dato";
$result=odbc_exec($this->con_id,$sql);
if(!$result){
return false;
}else{
while($row=odbc_fetch_array($result)){
$c++;
}
if($c==0){
return false;
}else{
return true;
}
}
}
/**
* Setter Getter: width main frame
*/
function setAnchoFrame($valor){
$this->ancho_frame_pr=$valor;
}
function getAnchoFrame(){
return $this->ancho_frame_pr;
}
/**
* Setter Getter: font size
*/
function setFontSize($valor){
$this->font_size=$valor;
}
function getFontSize(){
return $this->font_size;
}
/**
* get propertie table/column
Properties Table Progress
--------------------------
TABLE_CAT
TABLE_SCHEM
TABLE_NAME
COLUMN_NAME
DATA_TYPE
TYPE_NAME
COLUMN_SIZE
BUFFER_LENGTH
DECIMAL_DIGITS
NUM_PREC_RADIX
NULLABLE
REMARKS
COLUMN_DEF
SQL_DATA_TYPE
SQL_DATETIME_SUB
CHAR_OCTET_LENGTH
ORDINAL_POSITION
IS_NULLABLE
*/
function getPropiedadTabla($propiedad='DATA_TYPE',$campo){
$propiedad=strtoupper($propiedad);
$res=odbc_columns($this->con_id,"DSN_NAME","","$this->tabla","$campo");
while(odbc_fetch_row($res))
{
$valor=odbc_result($res,"$propiedad");
}
return $valor;
}
/**
* Send and receive Array
*/
function array_envia($array) {
$tmp = serialize($array);
$tmp = urlencode($tmp);
return $tmp;
}
function array_recibe($url_array) {
$tmp = stripslashes($url_array);
$tmp = urldecode($tmp);
$tmp = unserialize($tmp);
return $tmp;
}
/**
* Send e-mail
*/
function enviar_correo($mensaje)
{
$cabeceras = 'MIME-Version: 1.0' . "\r\n";
$cabeceras .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
$cabeceras .= 'From: Pulse' . "\r\n";
mail($this->remitentes, $this->getTitulo().' - '.$_SERVER['PHP_SELF'], $mensaje, $cabeceras);
}
/**
* Export Result to HTML
*/
function odbc_result_all_ex($res, $sTable, $sRow){
$cFields = odbc_num_fields($res);
$strTable = "<table $sTable>";
$strTable .= "<tr>";
for ($n=1; $n<=$cFields; $n++)
{
$strTable .= "<td $sRow><b>". str_replace("_", " ", odbc_field_name($res, $n)) . "</b></td>";
}
$strTable .= "</tr>";
while(odbc_fetch_row($res))
{
$strTable .= "<tr>";
for ($n=1; $n<=$cFields; $n++)
{
if (odbc_result($res, $n)=='')
{
$strTable .= "<td $sRow> </td>";
}
else
{
$strTable .= "<td $sRow>". odbc_result($res, $n) . "</td>";
}
}
$strTable .= "</tr>";
}
$strTable .= "</table>";
echo $strTable;
}
}
?>