include/database.php

   1 <?php
   2   require_once("const.php");
   3   class MySQLDB
   4   {
   5       //The MySQL database connection
   6       var $connection;
   7       //Number of active users viewing site
   8       var $num_active_users;
   9       //Number of active guests viewing site
  10       var $num_active_guests;
  11       //Number of signed-up users
  12       var $num_members;
  13       /* Note: call getNumMembers() to access $num_members! */
  14
  15       /* Class constructor */
  16       function MySQLDB()
  17       {
  18           /* Make connection to database */
  19           $this->connection = @mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die("Fue imposible conectarse a la base de datos, posiblemente no ha ejecutado el instalador (instalar.php) de " . _NOMBRE_ . " correctamente.<br /><hr />Detalles del error:<pre>" . mysql_error() . "</pre>");
  20           mysql_select_db(DB_NAME, $this->connection) or die(mysql_error());
  21
  22           /**
  23            * Only query database to find out number of members
  24            * when getNumMembers() is called for the first time,
  25            * until then, default value set.
  26            */
  27           $this->num_members = -1;
  28
  29           if (TRACK_VISITORS) {
  30               /* Calculate number of users at site */
  31               $this->calcNumActiveUsers();
  32
  33               /* Calculate number of guests at site */
  34               $this->calcNumActiveGuests();
  35           }
  36       }
  37
  38       /**
  39        * confirmUserPass - Checks whether or not the given
  40        * codigo is in the database, if so it checks if the
  41        * given clave is the same clave in the database
  42        * for that user. If the user doesn't exist or if the
  43        * claves don't match up, it returns an error code
  44        * (1 or 2). On success it returns 0.
  45        */
  46       function confirmUserPass($codigo, $clave)
  47       {
  48           /* Add slashes if necessary (for query) */
  49           if (!get_magic_quotes_gpc()) {
  50               $codigo = addslashes($codigo);
  51           }
  52
  53           /* Verify that user is in database */
  54           $q = "SELECT clave FROM " . TBL_USERS . " WHERE codigo = '$codigo'";
  55           $result = mysql_query($q, $this->connection);
  56           if (!$result || (mysql_numrows($result) < 1)) {
  57               //Indicates codigo failure
  58               return 1;
  59           }
  60
  61           /* Retrieve clave from result, strip slashes */
  62           $dbarray = mysql_fetch_array($result);
  63           $dbarray['clave'] = stripslashes($dbarray['clave']);
  64           $clave = stripslashes($clave);
  65
  66           /* Validate that clave is correct */
  67           if ($clave == $dbarray['clave']) {
  68               //Success! codigo and clave confirmed
  69               return 0;
  70           } else {
  71
  72               //Indicates clave failure
  73               return 2;
  74           }
  75       }
  76
  77       /**
  78        * confirmUserID - Checks whether or not the given
  79        * codigo is in the database, if so it checks if the
  80        * given userid is the same userid in the database
  81        * for that user. If the user doesn't exist or if the
  82        * userids don't match up, it returns an error code
  83        * (1 or 2). On success it returns 0.
  84        */
  85       function confirmUserID($codigo, $userid)
  86       {
  87           /* Add slashes if necessary (for query) */
  88           if (!get_magic_quotes_gpc()) {
  89               $codigo = addslashes($codigo);
  90           }
  91
  92           /* Verify that user is in database */
  93           $q = "SELECT userid FROM " . TBL_USERS . " WHERE codigo = '$codigo'";
  94           $result = mysql_query($q, $this->connection);
  95           if (!$result || (mysql_numrows($result) < 1)) {
  96               //Indicates codigo failure
  97               return 1;
  98           }
  99
 100           /* Retrieve userid from result, strip slashes */
 101           $dbarray = mysql_fetch_array($result);
 102           $dbarray['userid'] = stripslashes($dbarray['userid']);
 103           $userid = stripslashes($userid);
 104
 105           /* Validate that userid is correct */
 106           if ($userid == $dbarray['userid']) {
 107               //Success! codigo and userid confirmed
 108               return 0;
 109           } else {
 110
 111               //Indicates userid invalid
 112               return 2;
 113           }
 114       }
 115
 116       /**
 117        * codigoTaken - Returns true if the codigo has
 118        * been taken by another user, false otherwise.
 119        */
 120       function codigoTaken($codigo)
 121       {
 122           if (!get_magic_quotes_gpc()) {
 123               $codigo = addslashes($codigo);
 124           }
 125           $q = "SELECT codigo FROM " . TBL_USERS . " WHERE codigo = '$codigo'";
 126           $result = mysql_query($q, $this->connection);
 127           return(mysql_numrows($result) > 0);
 128       }
 129
 130       /**
 131        * codigoBanned - Returns true if the codigo has
 132        * been banned by the administrator.
 133        */
 134       function codigoBanned($codigo)
 135       {
 136           if (!get_magic_quotes_gpc()) {
 137               $codigo = addslashes($codigo);
 138           }
 139           $q = "SELECT codigo FROM " . TBL_BANNED_USERS . " WHERE codigo = '$codigo'";
 140           $result = mysql_query($q, $this->connection);
 141           return(mysql_numrows($result) > 0);
 142       }
 143
 144       /**
 145        * addNewUser - Inserts the given (codigo, clave, email)
 146        * info into the database. Appropriate user level is set.
 147        * Returns true on success, false otherwise.
 148        */
 149       function addNewUser($codigo, $clave, $nombre, $razon, $email, $telefono1, $telefono2, $telefono3, $logotipo, $notas)
 150       {
 151           $time = time();
 152           DEPURAR("Nuevo usuario:");
 153           /* If admin sign up, give admin user level */
 154           if (strcasecmp($codigo, ADMIN_NAME) == 0) {
 155               $ulevel = ADMIN_LEVEL;
 156           } else {
 157
 158               $ulevel = CLIENT_LEVEL;
 159           }
 160           $q = "INSERT INTO " . TBL_USERS . " VALUES ('$codigo', '$clave', '$nombre', '$razon', '$email', '$telefono1', '$telefono2', '$telefono3', '$logotipo', '$notas', $ulevel, 0, " . time() . ")";
 161           DEPURAR($q);
 162           return mysql_query($q, $this->connection);
 163       }
 164
 165       /**
 166        * updateUserField - Updates a field, specified by the field
 167        * parameter, in the user's row of the database.
 168        */
 169       function updateUserField($codigo, $field, $value)
 170       {
 171           $q = "UPDATE " . TBL_USERS . " SET " . $field . " = '$value' WHERE codigo = '$codigo'";
 172           return mysql_query($q, $this->connection);
 173       }
 174
 175       /**
 176        * getUserInfo - Returns the result array from a mysql
 177        * query asking for all information stored regarding
 178        * the given codigo. If query fails, NULL is returned.
 179        */
 180       function getUserInfo($codigo)
 181       {
 182           $q = "SELECT * FROM " . TBL_USERS . " WHERE codigo = '$codigo'";
 183           $result = mysql_query($q, $this->connection);
 184           /* Error occurred, return given name by default */
 185           if (!$result || (mysql_numrows($result) < 1)) {
 186               return null;
 187           }
 188           /* Return result array */
 189           $dbarray = mysql_fetch_array($result);
 190           return $dbarray;
 191       }
 192
 193       /**
 194        * getNumMembers - Returns the number of signed-up users
 195        * of the website, banned members not included. The first
 196        * time the function is called on page load, the database
 197        * is queried, on subsequent calls, the stored result
 198        * is returned. This is to improve efficiency, effectively
 199        * not querying the database when no call is made.
 200        */
 201       function getNumMembers()
 202       {
 203           if ($this->num_members < 0) {
 204               $q = "SELECT * FROM " . TBL_USERS;
 205               $result = mysql_query($q, $this->connection);
 206               $this->num_members = mysql_numrows($result);
 207           }
 208           return $this->num_members;
 209       }
 210
 211       /**
 212        * calcNumActiveUsers - Finds out how many active users
 213        * are viewing site and sets class variable accordingly.
 214        */
 215       function calcNumActiveUsers()
 216       {
 217           /* Calculate number of users at site */
 218           $q = "SELECT * FROM " . TBL_ACTIVE_USERS;
 219           $result = mysql_query($q, $this->connection);
 220           $this->num_active_users = mysql_numrows($result);
 221       }
 222
 223       /**
 224        * calcNumActiveGuests - Finds out how many active guests
 225        * are viewing site and sets class variable accordingly.
 226        */
 227       function calcNumActiveGuests()
 228       {
 229           /* Calculate number of guests at site */
 230           $q = "SELECT * FROM " . TBL_ACTIVE_GUESTS;
 231           $result = mysql_query($q, $this->connection);
 232           $this->num_active_guests = mysql_numrows($result);
 233       }
 234
 235       /**
 236        * addActiveUser - Updates codigo's last active timestamp
 237        * in the database, and also adds him to the table of
 238        * active users, or updates timestamp if already there.
 239        */
 240       function addActiveUser($codigo, $time)
 241       {
 242           $q = "UPDATE " . TBL_USERS . " SET timestamp = '$time' WHERE codigo = '$codigo'";
 243           mysql_query($q, $this->connection);
 244
 245           if (!TRACK_VISITORS)
 246               return;
 247           $q = "REPLACE INTO " . TBL_ACTIVE_USERS . " VALUES ('$codigo', '$time')";
 248           mysql_query($q, $this->connection);
 249           $this->calcNumActiveUsers();
 250       }
 251
 252       /* addActiveGuest - Adds guest to active guests table */
 253       function addActiveGuest($ip, $time)
 254       {
 255           if (!TRACK_VISITORS)
 256               return;
 257           $q = "REPLACE INTO " . TBL_ACTIVE_GUESTS . " VALUES ('$ip', '$time')";
 258           mysql_query($q, $this->connection);
 259           $this->calcNumActiveGuests();
 260       }
 261
 262       /* These functions are self explanatory, no need for comments */
 263
 264       /* removeActiveUser */
 265       function removeActiveUser($codigo)
 266       {
 267           if (!TRACK_VISITORS)
 268               return;
 269           $q = "DELETE FROM " . TBL_ACTIVE_USERS . " WHERE codigo = '$codigo'";
 270           //echo $q."<br>";
 271           mysql_query($q, $this->connection);
 272           $this->calcNumActiveUsers();
 273       }
 274
 275       /* removeActiveGuest */
 276       function removeActiveGuest($ip)
 277       {
 278           if (!TRACK_VISITORS)
 279               return;
 280           $q = "DELETE FROM " . TBL_ACTIVE_GUESTS . " WHERE ip = '$ip'";
 281           mysql_query($q, $this->connection);
 282           $this->calcNumActiveGuests();
 283       }
 284
 285       /* removeInactiveUsers */
 286       function removeInactiveUsers()
 287       {
 288           if (!TRACK_VISITORS)
 289               return;
 290           $timeout = time() - USER_TIMEOUT * 60;
 291           $q = "DELETE FROM " . TBL_ACTIVE_USERS . " WHERE timestamp < $timeout";
 292           mysql_query($q, $this->connection);
 293           $this->calcNumActiveUsers();
 294       }
 295
 296       /* removeInactiveGuests */
 297       function removeInactiveGuests()
 298       {
 299           if (!TRACK_VISITORS)
 300               return;
 301           $timeout = time() - GUEST_TIMEOUT * 60;
 302           $q = "DELETE FROM " . TBL_ACTIVE_GUESTS . " WHERE timestamp < $timeout";
 303           mysql_query($q, $this->connection);
 304           $this->calcNumActiveGuests();
 305       }
 306       // ************************************************************* //
 307           // Funciones de conveniencia
 308       // ************************************************************* //
 309
 310           function REGISTRAR($clave, $valor, $detalle)
 311           {
 312           global $session;
 313           $clave = mysql_real_escape_string($clave); // Evitamos SQL Inyection.
 314           $valor = mysql_real_escape_string($valor); // Evitamos SQL Inyection.
 315           $detalle = mysql_real_escape_string($detalle); // Evitamos SQL Inyection.
 316           $autor = mysql_real_escape_string($session->codigo); // Evitamos SQL Inyection.
 317           $timestamp = time();
 318           $q = "INSERT INTO ".TBL_REGISTRY." (clave, valor, detalle, autor, timestamp) VALUES ('$clave','$valor','$detalle','$autor','$timestamp')";
 319           @mysql_query($q, $this->connection);
 320           DEPURAR ($q,0);
 321           }
 322
 323       function Combobox_usuarios($nombre = "codigo", $default = null)
 324       {
 325           $q = "SELECT codigo, nombre FROM " . TBL_USERS . " WHERE userlevel <> 9;";
 326           $result = mysql_query($q, $this->connection);
 327           /* Error occurred, return given name by default */
 328           $num_rows = mysql_numrows($result);
 329           $s = '';
 330           if (!$result || ($num_rows < 0)) {
 331               $s .= "Error mostrando la información";
 332               return $s;
 333           }
 334           if ($num_rows == 0) {
 335               /*Esto nunca deberia de pasar realmente...*/
 336               $s .= "¡No hay clientes/usuarios ingresados!";
 337               return $s;
 338           }
 339           $s = '<select name="' . $nombre . '">';
 340           for ($i = 0; $i < $num_rows; $i++) {
 341               $uname = mysql_result($result, $i, "codigo");
 342               $nombre = mysql_result($result, $i, "nombre");
 343               if ($uname == $default) {
 344                   $selected = ' selected="selected"';
 345               } else {
 346                   $selected = "";
 347               }
 348               $s .= '<option value="' . $uname . '"' . $selected . '>' . $nombre . '</option>';
 349           }
 350           $s .= '</select>';
 351           return $s;
 352       }
 353
 354       function Combobox_pedido($nombre = "codigo_pedido", $default = null, $desde = null, $hasta = null)
 355       {
 356           $intervalo = '';
 357           if ($desde && $hasta) {
 358               $intervalo = " WHERE catorcena_inicio<=$desde AND catorcena_fin>=$hasta";
 359           }
 360           //if ($desde && $hasta) { $intervalo .= " AND catorcena_fin<='$hasta'"; }
 361           $q = "SELECT codigo_pedido, CONCAT(codigo_pedido,'. ', (SELECT nombre FROM " . TBL_USERS . " AS b WHERE b.codigo = a.codigo), ', ' , descripcion) as nombre FROM " . TBL_MUPI_ORDERS . " as a$intervalo ORDER BY codigo_pedido;";
 362           $result = mysql_query($q, $this->connection);
 363           /* Error occurred, return given name by default */
 364           $num_rows = mysql_numrows($result);
 365           $s = '';
 366           if (!$result || ($num_rows < 0)) {
 367               $s .= "Error mostrando la información";
 368               return $s;
 369           }
 370           if ($num_rows == 0) {
 371               /*Esto nunca deberia de pasar realmente...*/
 372               $s .= "¡No hay pedidos ingresados!";
 373               return $s;
 374           }
 375           $s = '<select name="' . $nombre . '" id="' . $nombre . '">';
 376           for ($i = 0; $i < $num_rows; $i++) {
 377               $codigo_pedido = mysql_result($result, $i, "codigo_pedido");
 378               $nombre = mysql_result($result, $i, "nombre");
 379               if ($codigo_pedido == $default) {
 380                   $selected = ' selected="selected"';
 381               } else {
 382                   $selected = "";
 383               }
 384               $s .= '<option value="' . $codigo_pedido . '"' . $selected . '>' . $nombre . '</option>';
 385           }
 386           $s .= '</select>';
 387           return $s;
 388       }
 389
 390       function Combobox_mupi($nombre = "codigo_mupi", $default = null)
 391       {
 392           //id_mupi, codigo_calle.codigo_mupi , calle, ubicacion.
 393           $q = "SELECT id_mupi, CONCAT(codigo_calle, '.' , codigo_mupi, ' | ', (SELECT ubicacion FROM emupi_calles AS b WHERE a.codigo_calle=b.codigo_calle), ', ', direccion ) as nombre FROM emupi_mupis AS a;";
 394           //echo $q;
 395           $result = mysql_query($q, $this->connection);
 396           /* Error occurred, return given name by default */
 397           $num_rows = mysql_numrows($result);
 398           $s = '';
 399           if (!$result || ($num_rows < 0)) {
 400               $s .= "Error mostrando la información";
 401               return $s;
 402           }
 403           if ($num_rows == 0) {
 404               /*Esto nunca deberia de pasar realmente...*/
 405               $s .= "¡No hay " . _NOMBRE_ . " ingresados!";
 406               return $s;
 407           }
 408           $s = '<select name="' . $nombre . '">';
 409           for ($i = 0; $i < $num_rows; $i++) {
 410               $id_mupi = mysql_result($result, $i, "id_mupi");
 411               $nombre = mysql_result($result, $i, "nombre");
 412               if ($id_mupi == $default) {
 413                   $selected = ' selected="selected"';
 414               } else {
 415                   $selected = "";
 416               }
 417               $s .= '<option value="' . $id_mupi . '"' . $selected . '>' . $nombre . '</option>';
 418           }
 419           $s .= '</select>';
 420           return $s;
 421       }
 422
 423       function Combobox_calle($nombre = "codigo_calle", $default = null, $calle = null)
 424       {
 425           if ($calle) {
 426               $wCalle = " AND codigo_calle='$calle'";
 427           } else {
 428               $wCalle = "";
 429           }
 430           $q = "SELECT codigo_calle, CONCAT(ubicacion, ' [Cod. ', codigo_calle , ']') as nombre FROM " . TBL_STREETS . $wCalle . " ORDER BY ubicacion;";
 431           $result = mysql_query($q, $this->connection);
 432           $num_rows = mysql_numrows($result);
 433           $s = '';
 434           if (!$result || ($num_rows < 0)) {
 435               $s .= "Error mostrando la información";
 436               return $s;
 437           }
 438           if ($num_rows == 0) {
 439               /*Esto nunca deberia de pasar realmente...*/
 440               $s .= "¡No hay calles " . _NOMBRE_ . " ingresadas!";
 441               return $s;
 442           }
 443           $s = '<select name="' . $nombre . '" id="' . $nombre . '">';
 444           for ($i = 0; $i < $num_rows; $i++) {
 445               $codigo_calle = mysql_result($result, $i, "codigo_calle");
 446               $nombre = mysql_result($result, $i, "nombre");
 447               if ($codigo_calle == $default) {
 448                   $selected = ' selected="selected"';
 449               } else {
 450                   $selected = "";
 451               }
 452               $s .= '<option value="' . $codigo_calle . '"' . $selected . '>' . $nombre . '</option>';
 453           }
 454           $s .= '</select>';
 455           return $s;
 456       }
 457
 458       function Combobox_CatorcenasConPresencia($nombre = "catorcena_presencia", $codigo = null, $OnChange = null)
 459       {
 460           global $session;
 461           $WHERE_USER = '';
 462           $NivelesPermitidos = array(ADMIN_LEVEL, SALESMAN_LEVEL, DEMO_LEVEL);
 463           if (!in_array($session->userlevel, $NivelesPermitidos) || $codigo) {
 464               $WHERE_USER = "WHERE codigo='" . $codigo . "'";
 465           }
 466           $q = "SELECT DISTINCT catorcena FROM " . TBL_MUPI_FACES . " WHERE catorcena <=" . Obtener_catorcena_siguiente() . " AND codigo_pedido IN (SELECT codigo_pedido FROM " . TBL_MUPI_ORDERS . " $WHERE_USER)  ORDER BY catorcena;";
 467           $result = mysql_query($q, $this->connection);
 468           //echo $q.'<br />';
 469           $num_rows = mysql_numrows($result);
 470           $s = '';
 471           if (!$result || ($num_rows < 0)) {
 472               $s .= "Error mostrando la información";
 473               return $s;
 474           }
 475           if ($num_rows == 0) {
 476               $s .= "¡No tiene ninguna pantalla alquilada en ninguna catorcena!";
 477               return $s;
 478           }
 479           $catorcena_actual = Obtener_catorcena_cercana();
 480           $s = '<select id="' . $nombre . '" name="' . $nombre . '" onkeyup="' . $OnChange . '" onclick="' . $OnChange . '">';
 481                   for ($i = 0; $i < $num_rows; $i++) {
 482               $catorcena_inicio = mysql_result($result, $i, "catorcena");
 483               $catorcena_fin = Fin_de_catorcena($catorcena_inicio);
 484               if ($catorcena_inicio == $catorcena_actual) {
 485                   $selected = ' selected="selected"';
 486               } else {
 487                   $selected = "";
 488               }
 489               $s .= '<option value="' . $catorcena_inicio . '"' . $selected . '>' . "Del " . date('d-m-Y', $catorcena_inicio) . ' al ' . date('d-m-Y', $catorcena_fin) . '</option>';
 490           }
 491           $s .= '</select>';
 492           return $s;
 493       }
 494
 495       function Combobox_CallesConPresencia($nombre, $codigo, $catorcena)
 496       {
 497           // Calles donde el usuario $codigo tiene caras alquiladas en la catorcena $catorcena.
 498           global $session;
 499           $WHERE_USER = '';
 500           $NivelesPermitidos = array(ADMIN_LEVEL, SALESMAN_LEVEL, DEMO_LEVEL);
 501           if (!in_array($session->userlevel, $NivelesPermitidos) || $codigo) {
 502               $WHERE_USER = " AND codigo_pedido IN (SELECT codigo_pedido FROM emupi_mupis_pedidos WHERE codigo='" . $codigo . "')";
 503           }
 504           // Filtro de cadena:
 505           // 1. Filtrar todas caras que esten en la catorcena requerida
 506           // 2. De esas filtrar las que tengan pedidos de $codigo
 507           // 3. De ahi tenemos codigo_mupi, del cual sacamos codigo_calle
 508           // 4. Posteriormente la ubicacion de esa calle.
 509           // 5. Mostramos nada mas las distintas calles.
 510           // - Combobox espera calle y ubicación.
 511           $q = "SELECT DISTINCT @calle := (SELECT codigo_calle FROM emupi_mupis AS b WHERE a.codigo_mupi=b.id_mupi) AS 'calle', (SELECT ubicacion FROM emupi_calles WHERE codigo_calle=@calle) AS ubicacion FROM emupi_mupis_caras AS a WHERE catorcena=" . $catorcena . $WHERE_USER . " HAVING ubicacion IS NOT NULL ORDER BY ubicacion;";
 512
 513                   echo $q.'<br />';
 514           $result = mysql_query($q, $this->connection);
 515                   $q2 = "select distinct grupo_calle from emupi_calles where grupo_calle IS NOT NULL AND grupo_calle != '';";
 516                   DEPURAR ($q2,0);
 517                   $result2 = mysql_query($q2, $this->connection);
 518           $num_rows = mysql_numrows($result);
 519                   $num_rows2 = mysql_numrows($result2);
 520           $s = '';
 521           if (!$result || ($num_rows < 0)) {
 522               $s .= "Error mostrando la información";
 523               return $s;
 524           }
 525           if ($num_rows == 0) {
 526               $s .= "¡No tiene presencia en ninguna calle para esta catorcena!";
 527               return $s;
 528           }
 529           $s = '<select id="' . $nombre . '" name="' . $nombre . '">';
 530
 531                   //Agregamos los grupos
 532                   $s .= '<optgroup label="Grupos">';
 533
 534                   //Agregamos el grupo "Todas" -- todas las calles.
 535                   $s .= '<option value="::T::">Todas</option>';
 536                   for ($i = 0; $i < $num_rows2; $i++) {
 537               $s .= '<option value="G:' . urlencode(mysql_result($result2, $i, "grupo_calle")) . '">' . mysql_result($result2, $i, "grupo_calle") . '</option>';
 538           }
 539                   //Agregamos las secciones de calle
 540                   $s .= '<optgroup label="Secciones">';
 541           for ($i = 0; $i < $num_rows; $i++) {
 542               $s .= '<option value="' . mysql_result($result, $i, "calle") . '">' . mysql_result($result, $i, "ubicacion") . '</option>';
 543           }
 544           $s .= '</select>';
 545           return $s;
 546       }
 547
 548       /**
 549        * query - Performs the given query on the database and
 550        * returns the result, which may be false, true or a
 551        * resource identifier.
 552        */
 553       function query($query)
 554       {
 555           $resultado = @mysql_query($query, $this->connection);
 556           if (mysql_error($this->connection)) {
 557               echo '<pre>MySQL:' . mysql_error() . '</pre>';
 558           }
 559           return $resultado;
 560       }
 561   }
 562
 563   /* Create database connection */
 564   $database = new MySQLDB;
 565 ?>