<?php
class SQL {
  function Cargar($fichero){
    if (file_exists($fichero)) {
      $xml_indice = simplexml_load_file($fichero);
      foreach ($xml_indice->tabla as $SQL) {     
        $id   = (string) $SQL['id'];
        $this->Tabla[$id]['nombre']      = (string)$SQL['nombre'];
        $this->Tabla[$id]['onduplicado'] = (string)$SQL['onduplicado'];
        foreach ($SQL->columna as $COLUMNA) { 
          $col = (string) $COLUMNA['id'];
          $this->Columna[$id][$col]['tipo'] = (string)$COLUMNA['tipo'];
          $this->Columna[$id][$col]['key']  = (string)$COLUMNA['key'];
          $this->Columna[$id][$col]['act']  = (string)$COLUMNA['act'];
        }
      }
    }   
    else {die ("(OBJ_Formularios) Error de carga del fichero de tablas SQL");}
    $this->Valores = array();
  }
  function Crear($tabla, $SQL_Tabla, $SQL_Columna, $SQL_Valores, $Id_Conexion){
    $com    = "'";
    $coma   = "";
    $values = "";
    $w_ai   = "";
    $sw_ai  = "";
    $error  = "";
    $w_sql  = 'INSERT INTO '.$tabla.' (';
    foreach ($SQL_Columna[$tabla] as $columna => $v) {
      if ($SQL_Columna[$tabla][$columna]['tipo'] != "AI"){
        if ($SQL_Columna[$tabla][$columna]['act'] != "no"){
          $w_sql  .= $coma.$columna;
          $values .= $coma.$com.$SQL_Valores[$tabla][$columna].$com;
          $coma    = ", ";
        }
      }
      else {
        $w_ai    = $columna;
        $sw_ai   = "S";
      }
    }
    $w_sql .= ') VALUE ('.$values.')';
    if ($SQL_Tabla[$tabla]['onduplicado'] == "si"){
      $coma    = "";
      $w_sql .= ' ON DUPLICATE KEY UPDATE ';  
      foreach ($SQL_Columna[$tabla] as $columna => $v) {
        if ($SQL_Columna[$tabla][$columna]['act'] != "no"){
          $w_sql  .= $coma.$columna." = '".$SQL_Valores[$tabla][$columna]."'";
          $coma    = ", ";
        }
      }
    }
    
    $this->traza = $w_sql; // Para efectos de debbug

    mysqli_query($Id_Conexion, $w_sql) or $error = mysqli_errno();   
    switch ($error){
      case ""  :
      case 0   : $this->error = ""; break;  
      case 1062: $this->error = mysqli_error($Id_Conexion); break;
      default:   die(mysqli_error($Id_Conexion)); 
    }
    if ($sw_ai == "S"){
      $this->Valores[$tabla][$w_ai] = mysqli_insert_id($_SESSION["s_idconexion"]); // Devuelve el código insertado
      $this->error = "";
    }
  }
  function Modificar($tabla, $SQL_Tabla, $SQL_Columna, $SQL_Valores, $Id_Conexion, $where = null){
    $com    = "'";
    $coma   = "";
    $and    = "";
    $values = "";
    $w_key  = "";
    $error  = "";
    $w_sql  = 'UPDATE '.$tabla." SET ";
    foreach ($SQL_Columna[$tabla] as $columna => $v) {
      if ($SQL_Columna[$tabla][$columna]['key'] != "si"){
        if ($SQL_Columna[$tabla][$columna]['act'] != "no"){
          $w_sql  .= $coma.$columna." = '".$SQL_Valores[$tabla][$columna]."'";
          $coma    = ", ";
        }
      }
      else {
        $w_key  .= $and.$columna." = '".$SQL_Valores[$tabla][$columna]."'";
        $and     = " AND ";
      }
    }
    $w_sql .= ' WHERE '.$w_key;
    
    $this->traza = $w_sql; // Para efectos de debbug

    mysqli_query($Id_Conexion, $w_sql) or $error = mysqli_errno();   
    switch ($error){
      case ""  : $this->error = ""; break;  
      case 1062: $this->error = "Usuario/Email ya existe"; break;
      default:   die(mysqli_error($Id_Conexion)); 
    }
  }
  function Borrar($tabla, $SQL_Tabla, $SQL_Columna, $SQL_Valores, $Id_Conexion, $where = null){
    $and    = "";
    $values = "";
    $w_key  = "";
    $error  = "";
    $w_sql  = 'DELETE FROM '.$tabla." WHERE ";
    foreach ($SQL_Columna[$tabla] as $columna => $v) {
      if ($SQL_Columna[$tabla][$columna]['key'] == "si"){
        $w_sql .= $and.$columna." = '".$SQL_Valores[$tabla][$columna]."'";
        $and     = " AND ";
      }
    }
    
    $this->traza = $w_sql; // Para efectos de debbug

    mysqli_query($Id_Conexion, $w_sql) or $error = mysqli_errno();   
    switch ($error){
      case ""  : $this->error = ""; break;  
      case 1062: $this->error = "Usuario/Email ya existe"; break;
      default  :   die(mysqli_error($Id_Conexion)); 
    }
  }
  function Datos($tabla, $SQL_Tabla, $SQL_Columna, $SQL_Valores, $Id_Conexion){
    $com    = "'";
    $coma   = "";
    $and    = "";
    $w_key  = "";
    $error = "";
    $w_sql  = 'SELECT ';
    foreach ($SQL_Columna[$tabla] as $columna => $v) {
      if ($SQL_Columna[$tabla][$columna]['key'] != "si"){
        $w_sql .= $coma.$columna;
        $coma   = ", ";
      }
      else {
        $w_key  .= $and.$columna." = '".$SQL_Valores[$tabla][$columna]."'";
        $and     = " AND ";
      }
    }
    $w_sql .= ' FROM '.$tabla.' WHERE '.$w_key;
    
    $this->traza = $w_sql; // Para efectos de debbug

    $query = mysqli_query($Id_Conexion, $w_sql) or $error = mysqli_errno();   
    switch ($error){
      case ""  :
      case 0   : $this->error = ""; break;  
      default:   die(mysqli_error($Id_Conexion)); 
    }
    if (mysqli_num_rows($query) == 0){$this->error = "NO EXISTE";}
    else {
      $row = mysqli_fetch_array($query);
      foreach ($SQL_Columna[$tabla] as $columna => $v) {
        if ($SQL_Columna[$tabla][$columna]['key'] != "si"){
          $col = $columna;
          $this->Valores[$tabla][$col] = $row[$col];
        }
      }
    }
  }
  function Lista($tabla, $SQL_Tabla, $SQL_Columna, $SQL_Valores, $Id_Conexion, $order = null, $where = null, $limit = null, $group = null){
    $com    = "'";
    $coma   = "";
    $and    = "";
    $w_key  = "";
    $error = "";
    $w_sql  = "SELECT ";
    foreach ($SQL_Columna[$tabla] as $columna => $v) {
      $w_sql .= $coma.$columna;
      $coma   = ", ";
    }
    $w_sql .= " FROM ".$tabla;
    if ($where != ""){$w_sql .= " WHERE ".$where;}
    if ($group != ""){$w_sql .= " GROUP BY ".$group;}
    if ($order != ""){$w_sql .= " ORDER BY ".$order;}
    if ($limit != ""){$w_sql .= " LIMIT ".$limit;}
    
    $this->traza = $w_sql; // Para efectos de debbug

    $query = mysqli_query($Id_Conexion, $w_sql) or $error = mysqli_errno();   
    switch ($error){
      case ""  :
      case 0   : $this->error = ""; break;  
      default:   die(mysqli_error($Id_Conexion)); 
    }
    unset($this->Valores[$tabla]);
    $this->Valores[$tabla]['nreg'] = mysqli_num_rows($query);
    for ($i=0; $i < mysqli_num_rows($query); $i++){
      $row = mysqli_fetch_array($query);
      foreach ($SQL_Columna[$tabla] as $columna => $v) {
        $col = $columna;
        $this->Valores[$tabla][$col][$i] = $row[$col];
      }
    }
  }
  function Contar($tabla, $SQL_Tabla, $SQL_Columna, $SQL_Valores, $Id_Conexion, $where = null){
    $com    = "'";
    $coma   = "";
    $and    = "";
    $w_key  = "";
    $error = "";
    $w_sql  = "SELECT COUNT(*)";
    $w_sql .= " FROM ".$tabla;
    if ($where != ""){ $w_sql .= " WHERE ".$where;}
    
    $this->traza = $w_sql; // Para efectos de debbug

    $query = mysqli_query($Id_Conexion, $w_sql) or $error = mysqli_errno();   
    switch ($error){
      case ""  :
      case 0   : $this->error = ""; break;  
      default:   die(mysqli_error($Id_Conexion)); 
    }
    unset($this->Valores[$tabla]);
    $row = mysqli_fetch_array($query);
    $this->Valores[$tabla]['cuenta'] = $row[0];
  }
}