include/database.php

   1 <?
   2 require_once("const.php");
   3 class MySQLDB
   4 {
   5    var $connection;         //The MySQL database connection
   6    var $num_active_users;   //Number of active users viewing site
   7    var $num_active_guests;  //Number of active guests viewing site
   8    var $num_members;        //Number of signed-up users
   9    /* Note: call getNumMembers() to access $num_members! */
  10
  11    /* Class constructor */
  12    function MySQLDB(){
  13       /* Make connection to database */
  14       $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>");
  15       mysql_select_db(DB_NAME, $this->connection) or die(mysql_error());
  16
  17       /**
  18        * Only query database to find out number of members
  19        * when getNumMembers() is called for the first time,
  20        * until then, default value set.
  21        */
  22       $this->num_members = -1;
  23
  24       if(TRACK_VISITORS){
  25          /* Calculate number of users at site */
  26          $this->calcNumActiveUsers();
  27
  28          /* Calculate number of guests at site */
  29          $this->calcNumActiveGuests();
  30       }
  31    }
  32
  33    /**
  34     * confirmUserPass - Checks whether or not the given
  35     * codigo is in the database, if so it checks if the
  36     * given clave is the same clave in the database
  37     * for that user. If the user doesn't exist or if the
  38     * claves don't match up, it returns an error code
  39     * (1 or 2). On success it returns 0.
  40     */
  41    function confirmUserPass($codigo, $clave){
  42       /* Add slashes if necessary (for query) */
  43       if(!get_magic_quotes_gpc()) {
  44               $codigo = addslashes($codigo);
  45       }
  46
  47       /* Verify that user is in database */
  48       $q = "SELECT clave FROM ".TBL_USERS." WHERE codigo = '$codigo'";
  49       $result = mysql_query($q, $this->connection);
  50       if(!$result || (mysql_numrows($result) < 1)){
  51          return 1; //Indicates codigo failure
  52       }
  53
  54       /* Retrieve clave from result, strip slashes */
  55       $dbarray = mysql_fetch_array($result);
  56       $dbarray['clave'] = stripslashes($dbarray['clave']);
  57       $clave = stripslashes($clave);
  58
  59       /* Validate that clave is correct */
  60       if($clave == $dbarray['clave']){
  61          return 0; //Success! codigo and clave confirmed
  62       }
  63       else{
  64          return 2; //Indicates clave failure
  65       }
  66    }
  67
  68    /**
  69     * confirmUserID - Checks whether or not the given
  70     * codigo is in the database, if so it checks if the
  71     * given userid is the same userid in the database
  72     * for that user. If the user doesn't exist or if the
  73     * userids don't match up, it returns an error code
  74     * (1 or 2). On success it returns 0.
  75     */
  76    function confirmUserID($codigo, $userid){
  77       /* Add slashes if necessary (for query) */
  78       if(!get_magic_quotes_gpc()) {
  79               $codigo = addslashes($codigo);
  80       }
  81
  82       /* Verify that user is in database */
  83       $q = "SELECT userid FROM ".TBL_USERS." WHERE codigo = '$codigo'";
  84       $result = mysql_query($q, $this->connection);
  85       if(!$result || (mysql_numrows($result) < 1)){
  86          return 1; //Indicates codigo failure
  87       }
  88
  89       /* Retrieve userid from result, strip slashes */
  90       $dbarray = mysql_fetch_array($result);
  91       $dbarray['userid'] = stripslashes($dbarray['userid']);
  92       $userid = stripslashes($userid);
  93
  94       /* Validate that userid is correct */
  95       if($userid == $dbarray['userid']){
  96          return 0; //Success! codigo and userid confirmed
  97       }
  98       else{
  99          return 2; //Indicates userid invalid
 100       }
 101    }
 102
 103    /**
 104     * codigoTaken - Returns true if the codigo has
 105     * been taken by another user, false otherwise.
 106     */
 107    function codigoTaken($codigo){
 108       if(!get_magic_quotes_gpc()){
 109          $codigo = addslashes($codigo);
 110       }
 111       $q = "SELECT codigo FROM ".TBL_USERS." WHERE codigo = '$codigo'";
 112       $result = mysql_query($q, $this->connection);
 113       return (mysql_numrows($result) > 0);
 114    }
 115
 116    /**
 117     * codigoBanned - Returns true if the codigo has
 118     * been banned by the administrator.
 119     */
 120    function codigoBanned($codigo){
 121       if(!get_magic_quotes_gpc()){
 122          $codigo = addslashes($codigo);
 123       }
 124       $q = "SELECT codigo FROM ".TBL_BANNED_USERS." WHERE codigo = '$codigo'";
 125       $result = mysql_query($q, $this->connection);
 126       return (mysql_numrows($result) > 0);
 127    }
 128
 129    /**
 130     * addNewUser - Inserts the given (codigo, clave, email)
 131     * info into the database. Appropriate user level is set.
 132     * Returns true on success, false otherwise.
 133     */
 134    function addNewUser( $codigo, $clave, $nombre, $razon, $email, $telefono1, $telefono2, $telefono3, $logotipo, $notas ){
 135       $time = time();
 136       DEPURAR ("Nuevo usuario");
 137       /* If admin sign up, give admin user level */
 138       if(strcasecmp($codigo, ADMIN_NAME) == 0){
 139          $ulevel = ADMIN_LEVEL;
 140       }else{
 141          $ulevel = USER_LEVEL;
 142       }
 143       $q = "INSERT INTO ".TBL_USERS." VALUES ('$codigo', '$clave', '$nombre', '$razon', '$email', '$telefono1', '$telefono2', '$telefono3', '$logotipo', '$notas', 0, 0, ".time().")";
 144       DEPURAR($q);
 145       return mysql_query($q, $this->connection);
 146    }
 147
 148    /**
 149     * updateUserField - Updates a field, specified by the field
 150     * parameter, in the user's row of the database.
 151     */
 152    function updateUserField($codigo, $field, $value){
 153       $q = "UPDATE ".TBL_USERS." SET ".$field." = '$value' WHERE codigo = '$codigo'";
 154       return mysql_query($q, $this->connection);
 155    }
 156
 157    /**
 158     * getUserInfo - Returns the result array from a mysql
 159     * query asking for all information stored regarding
 160     * the given codigo. If query fails, NULL is returned.
 161     */
 162    function getUserInfo($codigo){
 163       $q = "SELECT * FROM ".TBL_USERS." WHERE codigo = '$codigo'";
 164       $result = mysql_query($q, $this->connection);
 165       /* Error occurred, return given name by default */
 166       if(!$result || (mysql_numrows($result) < 1)){
 167          return NULL;
 168       }
 169       /* Return result array */
 170       $dbarray = mysql_fetch_array($result);
 171       return $dbarray;
 172    }
 173
 174    /**
 175     * getNumMembers - Returns the number of signed-up users
 176     * of the website, banned members not included. The first
 177     * time the function is called on page load, the database
 178     * is queried, on subsequent calls, the stored result
 179     * is returned. This is to improve efficiency, effectively
 180     * not querying the database when no call is made.
 181     */
 182    function getNumMembers(){
 183       if($this->num_members < 0){
 184          $q = "SELECT * FROM ".TBL_USERS;
 185          $result = mysql_query($q, $this->connection);
 186          $this->num_members = mysql_numrows($result);
 187       }
 188       return $this->num_members;
 189    }
 190
 191    /**
 192     * calcNumActiveUsers - Finds out how many active users
 193     * are viewing site and sets class variable accordingly.
 194     */
 195    function calcNumActiveUsers(){
 196       /* Calculate number of users at site */
 197       $q = "SELECT * FROM ".TBL_ACTIVE_USERS;
 198       $result = mysql_query($q, $this->connection);
 199       $this->num_active_users = mysql_numrows($result);
 200    }
 201
 202    /**
 203     * calcNumActiveGuests - Finds out how many active guests
 204     * are viewing site and sets class variable accordingly.
 205     */
 206    function calcNumActiveGuests(){
 207       /* Calculate number of guests at site */
 208       $q = "SELECT * FROM ".TBL_ACTIVE_GUESTS;
 209       $result = mysql_query($q, $this->connection);
 210       $this->num_active_guests = mysql_numrows($result);
 211    }
 212
 213    /**
 214     * addActiveUser - Updates codigo's last active timestamp
 215     * in the database, and also adds him to the table of
 216     * active users, or updates timestamp if already there.
 217     */
 218    function addActiveUser($codigo, $time){
 219       $q = "UPDATE ".TBL_USERS." SET timestamp = '$time' WHERE codigo = '$codigo'";
 220       mysql_query($q, $this->connection);
 221
 222       if(!TRACK_VISITORS) return;
 223       $q = "REPLACE INTO ".TBL_ACTIVE_USERS." VALUES ('$codigo', '$time')";
 224       mysql_query($q, $this->connection);
 225       $this->calcNumActiveUsers();
 226    }
 227
 228    /* addActiveGuest - Adds guest to active guests table */
 229    function addActiveGuest($ip, $time){
 230       if(!TRACK_VISITORS) return;
 231       $q = "REPLACE INTO ".TBL_ACTIVE_GUESTS." VALUES ('$ip', '$time')";
 232       mysql_query($q, $this->connection);
 233       $this->calcNumActiveGuests();
 234    }
 235
 236    /* These functions are self explanatory, no need for comments */
 237
 238    /* removeActiveUser */
 239    function removeActiveUser($codigo){
 240       if(!TRACK_VISITORS) return;
 241       $q = "DELETE FROM ".TBL_ACTIVE_USERS." WHERE codigo = '$codigo'";
 242       //echo $q."<br>";
 243       mysql_query($q, $this->connection);
 244       $this->calcNumActiveUsers();
 245    }
 246
 247    /* removeActiveGuest */
 248    function removeActiveGuest($ip){
 249       if(!TRACK_VISITORS) return;
 250       $q = "DELETE FROM ".TBL_ACTIVE_GUESTS." WHERE ip = '$ip'";
 251       mysql_query($q, $this->connection);
 252       $this->calcNumActiveGuests();
 253    }
 254
 255    /* removeInactiveUsers */
 256    function removeInactiveUsers(){
 257       if(!TRACK_VISITORS) return;
 258       $timeout = time()-USER_TIMEOUT*60;
 259       $q = "DELETE FROM ".TBL_ACTIVE_USERS." WHERE timestamp < $timeout";
 260       mysql_query($q, $this->connection);
 261       $this->calcNumActiveUsers();
 262    }
 263
 264    /* removeInactiveGuests */
 265    function removeInactiveGuests(){
 266       if(!TRACK_VISITORS) return;
 267       $timeout = time()-GUEST_TIMEOUT*60;
 268       $q = "DELETE FROM ".TBL_ACTIVE_GUESTS." WHERE timestamp < $timeout";
 269       mysql_query($q, $this->connection);
 270       $this->calcNumActiveGuests();
 271    }
 272
 273    function Combobox_usuarios ($nombre="codigo", $default=NULL) {
 274       $q = "SELECT codigo, nombre FROM ".TBL_USERS." WHERE userlevel <> 9;";
 275    $result = mysql_query($q, $this->connection);
 276    /* Error occurred, return given name by default */
 277    $num_rows = mysql_numrows($result);
 278    $s='';
 279    if(!$result || ($num_rows < 0)){
 280       $s.= "Error mostrando la información";
 281       return $s;
 282    }
 283    if($num_rows == 0){
 284       /*Esto nunca deberia de pasar realmente...*/
 285       $s.= "¡No hay clientes/usuarios ingresados!";
 286       return $s;
 287    }
 288   $s='<select name="'.$nombre.'">';
 289   for($i=0; $i<$num_rows; $i++){
 290       $uname  = mysql_result($result,$i,"codigo");
 291       $nombre = mysql_result($result,$i,"nombre");
 292       if ( $uname == $default ) { $selected = ' selected="selected"'; } else { $selected = ""; }
 293       $s.='<option value="'.$uname.'"'.$selected.'>'. $nombre .'</option>';
 294    }
 295    $s.= '</select>';
 296    return $s;
 297    }
 298
 299     function Combobox_pedido ($nombre="codigo_pedido", $default=NULL, $desde = NULL, $hasta = NULL) {
 300     $intervalo = '';
 301     if ($desde && $hasta) { $intervalo = " WHERE catorcena_inicio<=$desde AND catorcena_fin>=$hasta"; }
 302     //if ($desde && $hasta) { $intervalo .= " AND catorcena_fin<='$hasta'"; }
 303    $q = "SELECT codigo_pedido, CONCAT(codigo_pedido,'. ', (SELECT nombre FROM ".TBL_USERS." AS b WHERE b.codigo = a.codigo)) as nombre FROM ".TBL_MUPI_ORDERS . " as a$intervalo;";
 304    $result = mysql_query($q, $this->connection);
 305    /* Error occurred, return given name by default */
 306    $num_rows = mysql_numrows($result);
 307    $s='';
 308    if(!$result || ($num_rows < 0)){
 309       $s.= "Error mostrando la información";
 310       return $s;
 311    }
 312    if($num_rows == 0){
 313       /*Esto nunca deberia de pasar realmente...*/
 314       $s.= "¡No hay pedidos ingresados!";
 315       return $s;
 316    }
 317   $s='<select name="'.$nombre.'">';
 318   for($i=0; $i<$num_rows; $i++){
 319       $codigo_pedido  = mysql_result($result,$i,"codigo_pedido");
 320       $nombre = mysql_result($result,$i,"nombre");
 321       if ( $codigo_pedido == $default ) { $selected = ' selected="selected"'; } else { $selected = ""; }
 322       $s.='<option value="'.$codigo_pedido.'"'.$selected.'>'. $nombre .'</option>';
 323    }
 324    $s.= '</select>';
 325    return $s;
 326    }
 327
 328     function Combobox_mupi ($nombre="codigo_mupi", $default=NULL) {
 329       $q = "SELECT codigo_mupi, CONCAT(codigo_mupi, '. ' , (SELECT ubicacion from ".TBL_STREETS." as b WHERE b.codigo_calle = a.codigo_calle), ', ' , direccion) AS nombre from ".TBL_MUPI." as a;";
 330    $result = mysql_query($q, $this->connection);
 331    /* Error occurred, return given name by default */
 332    $num_rows = mysql_numrows($result);
 333    $s='';
 334    if(!$result || ($num_rows < 0)){
 335       $s.= "Error mostrando la información";
 336       return $s;
 337    }
 338    if($num_rows == 0){
 339       /*Esto nunca deberia de pasar realmente...*/
 340       $s.= "¡No hay "._NOMBRE_." ingresados!";
 341       return $s;
 342    }
 343   $s='<select name="'.$nombre.'">';
 344   for($i=0; $i<$num_rows; $i++){
 345       $codigo_mupi  = mysql_result($result,$i,"codigo_mupi");
 346       $nombre = mysql_result($result,$i,"nombre");
 347       if ( $codigo_mupi == $default ) { $selected = ' selected="selected"'; } else { $selected = ""; }
 348       $s.='<option value="'.$codigo_mupi.'"'.$selected.'>'. $nombre .'</option>';
 349    }
 350    $s.= '</select>';
 351    return $s;
 352    }
 353
 354     function Combobox_calle ($nombre="codigo_calle", $default=NULL) {
 355       $q = "SELECT codigo_calle, CONCAT(codigo_calle,'. ',ubicacion) as nombre FROM ".TBL_STREETS;
 356    $result = mysql_query($q, $this->connection);
 357    /* Error occurred, return given name by default */
 358    $num_rows = mysql_numrows($result);
 359    $s='';
 360    if(!$result || ($num_rows < 0)){
 361       $s.= "Error mostrando la información";
 362       return $s;
 363    }
 364    if($num_rows == 0){
 365       /*Esto nunca deberia de pasar realmente...*/
 366       $s.= "¡No hay calles "._NOMBRE_." ingresadas!";
 367       return $s;
 368    }
 369   $s='<select name="'.$nombre.'">';
 370   for($i=0; $i<$num_rows; $i++){
 371       $codigo_calle  = mysql_result($result,$i,"codigo_calle");
 372       $nombre = mysql_result($result,$i,"nombre");
 373       if ( $codigo_calle == $default ) { $selected = ' selected="selected"'; } else { $selected = ""; }
 374       $s.='<option value="'.$codigo_calle.'"'.$selected.'>'. $nombre .'</option>';
 375    }
 376    $s.= '</select>';
 377    return $s;
 378    }
 379
 380   function Combobox_CatorcenasConPresencia ($nombre="catorcena_presencia", $codigo=NULL, $OnChange=NULL) {
 381           global $session;
 382           $WHERE_USER = '';
 383           if ( !$session->isAdmin() || $codigo ) {$WHERE_USER = "WHERE codigo='".$codigo."'";}
 384    $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;";
 385    $result = mysql_query($q, $this->connection);
 386    //echo $q.'<br />';
 387    /* Error occurred, return given name by default */
 388    $num_rows = mysql_numrows($result);
 389    $s='';
 390    if(!$result || ($num_rows < 0)){
 391       $s.= "Error mostrando la información";
 392       return $s;
 393    }
 394    if($num_rows == 0){
 395       $s.= "¡No tiene ninguna pantalla alquilada en ninguna catorcena!";
 396       return $s;
 397    }
 398    $catorcena_actual = Obtener_catorcena_cercana();
 399   $s='<select id="'.$nombre.'" name="'.$nombre.'" onkeyup="'.$OnChange.'" onclick="'.$OnChange.'">';
 400   for($i=0; $i<$num_rows; $i++){
 401       $catorcena_inicio  = mysql_result($result,$i,"catorcena");
 402       $catorcena_fin = Fin_de_catorcena($catorcena_inicio);
 403       if ( $catorcena_inicio == $catorcena_actual ) { $selected = ' selected="selected"'; } else { $selected = ""; }
 404       $s.='<option value="'.$catorcena_inicio.'"'.$selected.'>'."Del " . date('d-m-Y',$catorcena_inicio) . ' al ' . date('d-m-Y',$catorcena_fin) .'</option>';
 405    }
 406    $s.= '</select>';
 407    return $s;
 408    }
 409
 410    function Combobox_CallesConPresencia($nombre, $codigo, $catorcena){
 411           global $session;
 412           $WHERE_USER = '';
 413           if ( !$session->isAdmin() || $codigo ) {$WHERE_USER = " AND codigo_pedido IN (SELECT codigo_pedido FROM emupi_mupis_pedidos WHERE codigo='".$codigo."')";}
 414    $q = "SELECT DISTINCT @calle := (SELECT codigo_calle FROM emupi_mupis AS b WHERE a.codigo_mupi=b.codigo_mupi) AS 'calle', (SELECT ubicacion FROM emupi_calles WHERE codigo_calle=@calle) AS ubicacion FROM emupi_mupis_caras AS a WHERE catorcena=".$_GET['catorcena']. $WHERE_USER .";";
 415    //echo $q.'<br />';
 416    $result = mysql_query($q, $this->connection);
 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       $s.= "¡No tiene presencia en ninguna calle para esta catorcena!";
 425       return $s;
 426    }
 427    $s='<select id="'.$nombre.'" name="'.$nombre.'">';
 428   for($i=0; $i<$num_rows; $i++){
 429       $s.='<option value="'.mysql_result($result,$i,"calle").'">'. mysql_result($result,$i,"ubicacion") .'</option>';
 430    }
 431    $s.= '</select>';
 432    return $s;
 433    }
 434
 435    /**
 436     * query - Performs the given query on the database and
 437     * returns the result, which may be false, true or a
 438     * resource identifier.
 439     */
 440    function query($query){
 441      $resultado = @mysql_query($query, $this->connection);
 442      if ( mysql_error($this->connection) ) {
 443         echo '<pre>MySQL:'. mysql_error().'</pre>';
 444       }
 445         return $resultado;
 446    }
 447 };
 448
 449 /* Create database connection */
 450 $database = new MySQLDB;
 451
 452 ?>