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." ORDER BY userlevel DESC;";
 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) {
 300       $q = "SELECT codigo_pedido, CONCAT(codigo_pedido,'. ',codigo) as nombre FROM ".TBL_MUPI_ORDERS;
 301    $result = mysql_query($q, $this->connection);
 302    /* Error occurred, return given name by default */
 303    $num_rows = mysql_numrows($result);
 304    $s='';
 305    if(!$result || ($num_rows < 0)){
 306       $s.= "Error mostrando la información";
 307       return $s;
 308    }
 309    if($num_rows == 0){
 310       /*Esto nunca deberia de pasar realmente...*/
 311       $s.= "¡No hay pedidos ingresados!";
 312       return $s;
 313    }
 314   $s='<select name="'.$nombre.'">';
 315   for($i=0; $i<$num_rows; $i++){
 316       $codigo_pedido  = mysql_result($result,$i,"codigo_pedido");
 317       $nombre = mysql_result($result,$i,"nombre");
 318       if ( $codigo_pedido == $default ) { $selected = ' selected="selected"'; } else { $selected = ""; }
 319       $s.='<option value="'.$codigo_pedido.'"'.$selected.'>'. $nombre .'</option>';
 320    }
 321    $s.= '</select>';
 322    return $s;
 323    }
 324
 325     function Combobox_mupi ($nombre="codigo_mupi", $default=NULL) {
 326       $q = "SELECT codigo_mupi, CONCAT(codigo_mupi,'. ',direccion) as nombre FROM ".TBL_MUPI;
 327    $result = mysql_query($q, $this->connection);
 328    /* Error occurred, return given name by default */
 329    $num_rows = mysql_numrows($result);
 330    $s='';
 331    if(!$result || ($num_rows < 0)){
 332       $s.= "Error mostrando la información";
 333       return $s;
 334    }
 335    if($num_rows == 0){
 336       /*Esto nunca deberia de pasar realmente...*/
 337       $s.= "¡No hay "._NOMBRE_." ingresados!";
 338       return $s;
 339    }
 340   $s='<select name="'.$nombre.'">';
 341   for($i=0; $i<$num_rows; $i++){
 342       $codigo_mupi  = mysql_result($result,$i,"codigo_mupi");
 343       $nombre = mysql_result($result,$i,"nombre");
 344       if ( $codigo_mupi == $default ) { $selected = ' selected="selected"'; } else { $selected = ""; }
 345       $s.='<option value="'.$codigo_mupi.'"'.$selected.'>'. $nombre .'</option>';
 346    }
 347    $s.= '</select>';
 348    return $s;
 349    }
 350
 351     function Combobox_calle ($nombre="codigo_calle", $default=NULL) {
 352       $q = "SELECT codigo_calle, CONCAT(codigo_calle,'. ',ubicacion) as nombre FROM ".TBL_STREETS;
 353    $result = mysql_query($q, $this->connection);
 354    /* Error occurred, return given name by default */
 355    $num_rows = mysql_numrows($result);
 356    $s='';
 357    if(!$result || ($num_rows < 0)){
 358       $s.= "Error mostrando la información";
 359       return $s;
 360    }
 361    if($num_rows == 0){
 362       /*Esto nunca deberia de pasar realmente...*/
 363       $s.= "¡No hay calles "._NOMBRE_." ingresadas!";
 364       return $s;
 365    }
 366   $s='<select name="'.$nombre.'">';
 367   for($i=0; $i<$num_rows; $i++){
 368       $codigo_calle  = mysql_result($result,$i,"codigo_calle");
 369       $nombre = mysql_result($result,$i,"nombre");
 370       if ( $codigo_calle == $default ) { $selected = ' selected="selected"'; } else { $selected = ""; }
 371       $s.='<option value="'.$codigo_calle.'"'.$selected.'>'. $nombre .'</option>';
 372    }
 373    $s.= '</select>';
 374    return $s;
 375    }
 376
 377    /**
 378     * query - Performs the given query on the database and
 379     * returns the result, which may be false, true or a
 380     * resource identifier.
 381     */
 382    function query($query){
 383      $resultado = @mysql_query($query, $this->connection);
 384      if ( mysql_error($this->connection) ) {
 385         echo '<pre>MySQL:'. mysql_error().'</pre>';
 386       }
 387         return $resultado;
 388    }
 389 };
 390
 391 /* Create database connection */
 392 $database = new MySQLDB;
 393
 394 ?>