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       function Combobox_usuarios($nombre = "codigo", $default = null)
 308       {
 309           $q = "SELECT codigo, nombre FROM " . TBL_USERS . " WHERE userlevel <> 9;";
 310           $result = mysql_query($q, $this->connection);
 311           /* Error occurred, return given name by default */
 312           $num_rows = mysql_numrows($result);
 313           $s = '';
 314           if (!$result || ($num_rows < 0)) {
 315               $s .= "Error mostrando la información";
 316               return $s;
 317           }
 318           if ($num_rows == 0) {
 319               /*Esto nunca deberia de pasar realmente...*/
 320               $s .= "¡No hay clientes/usuarios ingresados!";
 321               return $s;
 322           }
 323           $s = '<select name="' . $nombre . '">';
 324           for ($i = 0; $i < $num_rows; $i++) {
 325               $uname = mysql_result($result, $i, "codigo");
 326               $nombre = mysql_result($result, $i, "nombre");
 327               if ($uname == $default) {
 328                   $selected = ' selected="selected"';
 329               } else {
 330                   $selected = "";
 331               }
 332               $s .= '<option value="' . $uname . '"' . $selected . '>' . $nombre . '</option>';
 333           }
 334           $s .= '</select>';
 335           return $s;
 336       }
 337
 338       function Combobox_pedido($nombre = "codigo_pedido", $default = null, $desde = null, $hasta = null)
 339       {
 340           $intervalo = '';
 341           if ($desde && $hasta) {
 342               $intervalo = " WHERE catorcena_inicio<=$desde AND catorcena_fin>=$hasta";
 343           }
 344           //if ($desde && $hasta) { $intervalo .= " AND catorcena_fin<='$hasta'"; }
 345           $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;";
 346           $result = mysql_query($q, $this->connection);
 347           /* Error occurred, return given name by default */
 348           $num_rows = mysql_numrows($result);
 349           $s = '';
 350           if (!$result || ($num_rows < 0)) {
 351               $s .= "Error mostrando la información";
 352               return $s;
 353           }
 354           if ($num_rows == 0) {
 355               /*Esto nunca deberia de pasar realmente...*/
 356               $s .= "¡No hay pedidos ingresados!";
 357               return $s;
 358           }
 359           $s = '<select name="' . $nombre . '" id="' . $nombre . '">';
 360           for ($i = 0; $i < $num_rows; $i++) {
 361               $codigo_pedido = mysql_result($result, $i, "codigo_pedido");
 362               $nombre = mysql_result($result, $i, "nombre");
 363               if ($codigo_pedido == $default) {
 364                   $selected = ' selected="selected"';
 365               } else {
 366                   $selected = "";
 367               }
 368               $s .= '<option value="' . $codigo_pedido . '"' . $selected . '>' . $nombre . '</option>';
 369           }
 370           $s .= '</select>';
 371           return $s;
 372       }
 373
 374       function Combobox_mupi($nombre = "codigo_mupi", $default = null)
 375       {
 376           //id_mupi, codigo_calle.codigo_mupi , calle, ubicacion.
 377           $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;";
 378           //echo $q;
 379           $result = mysql_query($q, $this->connection);
 380           /* Error occurred, return given name by default */
 381           $num_rows = mysql_numrows($result);
 382           $s = '';
 383           if (!$result || ($num_rows < 0)) {
 384               $s .= "Error mostrando la información";
 385               return $s;
 386           }
 387           if ($num_rows == 0) {
 388               /*Esto nunca deberia de pasar realmente...*/
 389               $s .= "¡No hay " . _NOMBRE_ . " ingresados!";
 390               return $s;
 391           }
 392           $s = '<select name="' . $nombre . '">';
 393           for ($i = 0; $i < $num_rows; $i++) {
 394               $id_mupi = mysql_result($result, $i, "id_mupi");
 395               $nombre = mysql_result($result, $i, "nombre");
 396               if ($id_mupi == $default) {
 397                   $selected = ' selected="selected"';
 398               } else {
 399                   $selected = "";
 400               }
 401               $s .= '<option value="' . $id_mupi . '"' . $selected . '>' . $nombre . '</option>';
 402           }
 403           $s .= '</select>';
 404           return $s;
 405       }
 406
 407       function Combobox_calle($nombre = "codigo_calle", $default = null, $calle = null)
 408       {
 409           if ($calle) {
 410               $wCalle = " AND codigo_calle='$calle'";
 411           } else {
 412               $wCalle = "";
 413           }
 414           $q = "SELECT codigo_calle, CONCAT(ubicacion, ' [Cod. ', codigo_calle , ']') as nombre FROM " . TBL_STREETS . $wCalle . " ORDER BY ubicacion;";
 415           $result = mysql_query($q, $this->connection);
 416           /* Error occurred, return given name by default */
 417           $num_rows = mysql_numrows($result);
 418           $s = '';
 419           if (!$result || ($num_rows < 0)) {
 420               $s .= "Error mostrando la información";
 421               return $s;
 422           }
 423           if ($num_rows == 0) {
 424               /*Esto nunca deberia de pasar realmente...*/
 425               $s .= "¡No hay calles " . _NOMBRE_ . " ingresadas!";
 426               return $s;
 427           }
 428           $s = '<select name="' . $nombre . '" id="' . $nombre . '">';
 429           for ($i = 0; $i < $num_rows; $i++) {
 430               $codigo_calle = mysql_result($result, $i, "codigo_calle");
 431               $nombre = mysql_result($result, $i, "nombre");
 432               if ($codigo_calle == $default) {
 433                   $selected = ' selected="selected"';
 434               } else {
 435                   $selected = "";
 436               }
 437               $s .= '<option value="' . $codigo_calle . '"' . $selected . '>' . $nombre . '</option>';
 438           }
 439           $s .= '</select>';
 440           return $s;
 441       }
 442
 443       function Combobox_CatorcenasConPresencia($nombre = "catorcena_presencia", $codigo = null, $OnChange = null)
 444       {
 445           global $session;
 446           $WHERE_USER = '';
 447           $NivelesPermitidos = array(ADMIN_LEVEL, SALESMAN_LEVEL);
 448           if (!in_array($session->userlevel, $NivelesPermitidos) || $codigo) {
 449               $WHERE_USER = "WHERE codigo='" . $codigo . "'";
 450           }
 451           $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;";
 452           $result = mysql_query($q, $this->connection);
 453           //echo $q.'<br />';
 454           /* Error occurred, return given name by default */
 455           $num_rows = mysql_numrows($result);
 456           $s = '';
 457           if (!$result || ($num_rows < 0)) {
 458               $s .= "Error mostrando la información";
 459               return $s;
 460           }
 461           if ($num_rows == 0) {
 462               $s .= "¡No tiene ninguna pantalla alquilada en ninguna catorcena!";
 463               return $s;
 464           }
 465           $catorcena_actual = Obtener_catorcena_cercana();
 466           $s = '<select id="' . $nombre . '" name="' . $nombre . '" onkeyup="' . $OnChange . '" onclick="' . $OnChange . '">';
 467           for ($i = 0; $i < $num_rows; $i++) {
 468               $catorcena_inicio = mysql_result($result, $i, "catorcena");
 469               $catorcena_fin = Fin_de_catorcena($catorcena_inicio);
 470               if ($catorcena_inicio == $catorcena_actual) {
 471                   $selected = ' selected="selected"';
 472               } else {
 473                   $selected = "";
 474               }
 475               $s .= '<option value="' . $catorcena_inicio . '"' . $selected . '>' . "Del " . date('d-m-Y', $catorcena_inicio) . ' al ' . date('d-m-Y', $catorcena_fin) . '</option>';
 476           }
 477           $s .= '</select>';
 478           return $s;
 479       }
 480
 481       function Combobox_CallesConPresencia($nombre, $codigo, $catorcena)
 482       {
 483           // Calles donde el usuario $codigo tiene caras alquiladas en la catorcena $catorcena.
 484           global $session;
 485           $WHERE_USER = '';
 486           $NivelesPermitidos = array(ADMIN_LEVEL, SALESMAN_LEVEL);
 487           if (!in_array($session->userlevel, $NivelesPermitidos) || $codigo) {
 488               $WHERE_USER = " AND codigo_pedido IN (SELECT codigo_pedido FROM emupi_mupis_pedidos WHERE codigo='" . $codigo . "')";
 489           }
 490           // Filtro de cadena:
 491           // 1. Filtrar todas caras que esten en la catorcena requerida
 492           // 2. De esas filtrar las que tengan pedidos de $codigo
 493           // 3. De ahi tenemos codigo_mupi, del cual sacamos codigo_calle
 494           // 4. Posteriormente la ubicacion de esa calle.
 495           // 5. Mostramos nada mas las distintas calles.
 496           // - Combobox espera calle y ubicación.
 497           $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 . " ORDER BY ubicacion;";
 498           //echo $q.'<br />';
 499           $result = mysql_query($q, $this->connection);
 500           $num_rows = mysql_numrows($result);
 501           $s = '';
 502           if (!$result || ($num_rows < 0)) {
 503               $s .= "Error mostrando la información";
 504               return $s;
 505           }
 506           if ($num_rows == 0) {
 507               $s .= "¡No tiene presencia en ninguna calle para esta catorcena!";
 508               return $s;
 509           }
 510           $s = '<select id="' . $nombre . '" name="' . $nombre . '">';
 511           for ($i = 0; $i < $num_rows; $i++) {
 512               $s .= '<option value="' . mysql_result($result, $i, "calle") . '">' . mysql_result($result, $i, "ubicacion") . '</option>';
 513           }
 514           $s .= '</select>';
 515           return $s;
 516       }
 517
 518       /**
 519        * query - Performs the given query on the database and
 520        * returns the result, which may be false, true or a
 521        * resource identifier.
 522        */
 523       function query($query)
 524       {
 525           $resultado = @mysql_query($query, $this->connection);
 526           if (mysql_error($this->connection)) {
 527               echo '<pre>MySQL:' . mysql_error() . '</pre>';
 528           }
 529           return $resultado;
 530       }
 531   }
 532
 533   /* Create database connection */
 534   $database = new MySQLDB;
 535 ?>