database/php/irreco_webdb_database.php

   1 <?php
   2
   3 /*
   4  * irreco - Ir Remote Control
   5  * Copyright (C) 2007,2008  Arto Karppinen (arto.karppinen@iki.fi),
   6  * Joni Kokko (t5kojo01@students.oamk.fi),
   7  * Sami Mäki (kasmra@xob.kapsi.fi),
   8  * Harri Vattulainen (t5vaha01@students.oamk.fi)
   9  *
  10  * This program is free software; you can redistribute it and/or
  11  * modify it under the terms of the GNU General Public License
  12  * as published by the Free Software Foundation; either version 2
  13  * of the License, or (at your option) any later version.
  14  *
  15  * This program is distributed in the hope that it will be useful,
  16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  18  * GNU General Public License for more details.
  19  *
  20  * You should have received a copy of the GNU General Public License
  21  * along with this program; if not, write to the Free Software Foundation,
  22  * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
  23  */
  24
  25 require_once 'MDB2.php';
  26 require_once 'irreco_webdb_log.php';
  27
  28 class IrrecoWebdbDatabase
  29 {
  30         private $db;
  31         private $log;
  32         private $prepared_query;
  33         private $previous_result_set;
  34
  35         function __construct($dsn)
  36         {
  37                 $options = array('debug' => 2,
  38                                  'result_buffering' => false);
  39
  40                 IrrecoLog::$database->log("Connecting to server...");
  41                 $this->db = MDB2::factory($dsn, $options);
  42
  43                 $this->db->setFetchMode(MDB2_FETCHMODE_OBJECT);
  44                 if (PEAR::isError($this->db)) {
  45                         IrrecoLog::critical($this->db->getMessage());
  46                 }
  47         }
  48
  49         function __destruct() {
  50                 if (is_object($this->db)) {
  51                         $this->db->disconnect();
  52                         $this->db = NULL;
  53                 }
  54         }
  55
  56         /**
  57          * Run Query on database. Die on error.
  58          */
  59         private function query($query)
  60         {
  61                 /* Make sure that we dont collect multiple result
  62                    sets in memory. */
  63                 if (is_object($this->previous_result_set)) {
  64                         $this->previous_result_set->free();
  65                         $this->previous_result_set = NULL;
  66                 }
  67
  68                 IrrecoLog::$database->log('Query: "' . $query . '"');
  69                 $result = $this->db->query($query);
  70                 if (PEAR::isError($result)) {
  71                         IrrecoLog::critical($result->getMessage());
  72                 } else {
  73                         $this->previous_result_set = $result;
  74                         return $result;
  75                 }
  76         }
  77
  78         /**
  79          * Prepare Query on database. Die on error.
  80          */
  81         private function prepare($query, $types, $prepare)
  82         {
  83                 /* Make sure that we dont prepare several queries. */
  84                 if (is_object($this->prepared_query)) {
  85                         $this->prepared_query->free();
  86                         $this->prepared_query = NULL;
  87                 }
  88
  89                 IrrecoLog::$database->log('Prepare: "' . $query . '"');
  90                 $result = $this->db->prepare($query/*, $types, $prepare*/);
  91                 if (PEAR::isError($result)) {
  92                         IrrecoLog::critical($result->getMessage());
  93                 } else {
  94                         $this->prepared_query = $result;
  95                         return $result;
  96                 }
  97         }
  98
  99         /**
 100          * Execute Query on database. Die on error.
 101          */
 102         private function execute($args)
 103         {
 104                 /* Make sure that we dont collect multiple result
 105                    sets in memory. */
 106                 if (is_object($this->previous_result_set)) {
 107                         $this->previous_result_set->free();
 108                         $this->previous_result_set = NULL;
 109                 }
 110
 111                 IrrecoLog::$database->log('Execute: "' . serialize($args) . '"');
 112                 $result = $this->prepared_query->execute($args);
 113                 if (PEAR::isError($result)) {
 114                         IrrecoLog::critical($result->getMessage());
 115                 } else {
 116                         $this->previous_result_set = $result;
 117                         return $result;
 118                 }
 119         }
 120
 121         private function lockTable($table)
 122         {
 123                 $this->query('LOCK TABLE ' .
 124                              $this->db->quoteIdentifier($table, 'text').
 125                              ' WRITE');
 126                 /*
 127                 $this->prepare('LOCK TABLE ? WRITE',
 128                                array('text'), MDB2_PREPARE_RESULT);
 129                 $this->execute($table);
 130                 */
 131         }
 132
 133         private function unlockTables()
 134         {
 135                 $this->query('UNLOCK TABLES');
 136         }
 137
 138         /**
 139          * Add user to database
 140          */
 141         function addUser($name, $email, $passwd, $table)
 142         {
 143                 IrrecoLog::$database->log('Add user: "' . $name .
 144                                 '" to table "' . $table . '".');
 145
 146                 /* Lock table */
 147                 $this->lockTable($table);
 148
 149                 /* Check for same name already at DB */
 150                 $data = $this->getNameId($table, $name);
 151                 if ($data != "") {
 152                         IrrecoLog::$database->log('Name already at database.');
 153                         return FALSE;
 154                 }
 155
 156                 /* insert data */
 157                 $this->query("INSERT INTO " .
 158                                 $this->db->quoteIdentifier($table, 'text').
 159                                 " VALUES (NULL, ".
 160                                 $this->db->quote($name, 'text'). ",
 161                                 ". $this->db->quote($email, 'text'). ",
 162                                 ". $this->db->quote($passwd, 'text').
 163                                 ", NOW(), NOW() )" );
 164
 165                 /* Unlock table */
 166                 $this->unlockTables();
 167
 168                 IrrecoLog::$database->log('Done.');
 169
 170                 return TRUE;
 171         }
 172
 173
 174         /**
 175          * Add User agent data to database
 176          */
 177         function addUserAgentData($user_agent)
 178         {
 179                 IrrecoLog::$database->log("Client:" . $_SERVER['HTTP_USER_AGENT']);
 180                 $result = $this->query('SELECT user_agent AS '.
 181                                         'user_agent FROM user_agent WHERE date = CURDATE() '.
 182                                         'AND user_agent = '. $this->db->quote($user_agent, 'text'));
 183                 $rows = $result->fetchAll();
 184
 185                 if($user_agent != $rows[0]->user_agent) {
 186                         $this->query('INSERT INTO user_agent (user_agent, date, count) VALUES('.
 187                                         $this->db->quote($user_agent, 'text').', CURDATE(), 1)');
 188                 }
 189                 else{
 190                         $this->query('UPDATE user_agent SET count = count+1 WHERE user_agent = '.
 191                                         $this->db->quote($user_agent, 'text').' AND date= CURDATE()');
 192                 }
 193
 194         }
 195
 196
 197         /**
 198          * Add configuration to database
 199          *
 200          * @param string backend Name for Backend. e.g. "IRTrans Transceiver"
 201          * @param string category Name for Category. e.g. "Tv"
 202          * @param string manufacturer Name for Manufacturer. e.g. "Sony"
 203          * @param string model Name for Model. e.g. "Sony"
 204          * @param string user Username
 205          * @param string password sha1-hash of password
 206          * @param string file_hash sha1-hash of file_data
 207          * @param string file_name Name for File
 208          * @param string file_data Content of file
 209          * @return string
 210          */
 211         function uploadConfiguration($backend,
 212                                      $category,
 213                                      $manufacturer,
 214                                      $model,
 215                                      $user,
 216                                      $password,
 217                                      $file_hash,
 218                                      $file_name,
 219                                      $file_data)
 220         {
 221                 /* Check if username matches up to password and get id */
 222                 $user_id = $this->checkUsernameAndPassword($user, $password);
 223                 if($user_id == NULL) {
 224                         return('username and password mismatch.');
 225                 }
 226
 227                 /* Check if file hash matches with sha1 sum of file data */
 228                 if(sha1($file_data) != $file_hash) {
 229                         return('file hash and file data mismatch.');
 230                 }
 231
 232                 /* get id for backend, category and manufacturer */
 233                 /* create new if needed */
 234                 /* ?delete new id's if adding data fails? */
 235                 $backend_id = $this->createNameId('backend', $backend);
 236                 $category_id = $this->createNameId('category', $category);
 237                 $manufacturer_id = $this->createNameId('manufacturer',
 238                                                       $manufacturer);
 239
 240                 /* insert data */
 241                 $this->lockTable('file');
 242
 243                 if ($this->fileExists($file_hash, $file_name)) {
 244                         $this->unlockTables();
 245                         return('file already exist.');
 246                 }
 247
 248                 $this->query('INSERT INTO file VALUES ( '.
 249                              $this->db->quote($file_hash, 'text'). ', '.
 250                              $this->db->quote($file_name, 'text'). ', '.
 251                              $this->db->quote($file_data, 'text'). ', '.
 252                              'NOW(), NOW(), 0)');
 253
 254                 /* Unlock table */
 255                 $this->unlockTables();
 256
 257                 /* Create configuration */
 258                 $this->lockTable('configuration');
 259                 $this->query("INSERT INTO configuration VALUES (NULL, ".
 260                              $this->db->quote($user_id, 'text'). ", ".
 261                              $this->db->quote($backend_id, 'text'). ", ".
 262                              $this->db->quote($category_id, 'text'). ", ".
 263                              $this->db->quote($manufacturer_id, 'text'). ", ".
 264                              $this->db->quote($model, 'text'). ", ".
 265                              $this->db->quote($file_hash, 'text'). ", ".
 266                              $this->db->quote($file_name, 'text'). ")");
 267
 268                 /* Unlock table */
 269                 $this->unlockTables();
 270
 271                 IrrecoLog::$database->log('configuration added successfully.');
 272
 273                 return('configuration added successfully');
 274         }
 275
 276         function dumpResultToLog($result)
 277         {
 278                 IrrecoLog::$database->log('Dumping result set.');
 279
 280                 $i = 1;
 281                 while ($row = $result->fetchRow()) {
 282                         IrrecoLog::$database->log(' Row ' . $i++ . ': ' .
 283                                                   serialize($row));
 284                 }
 285
 286                 IrrecoLog::$database->log('Done.');
 287         }
 288
 289         /**
 290          * Check username and password.
 291          */
 292         function checkUsernameAndPassword($name, $password)
 293         {
 294                 $result = $this->query(
 295                         'SELECT id AS id FROM user '.
 296                         'WHERE name LIKE '. $this->db->quote($name, 'text').' '.
 297                         'AND password = '. $this->db->quote($password, 'text'));
 298                 $row = $result->fetchRow();
 299
 300                 if (is_object($row)) {
 301                         $id = $row->id;
 302                         IrrecoLog::$database->log(
 303                                         'username matches up to password');
 304                 } else {
 305                         $id = NULL;
 306                         IrrecoLog::$database->log(
 307                                         'username and password mismatch');
 308                 }
 309                 return $id;
 310         }
 311         /**
 312          * Check if file already exist
 313          *
 314          *
 315          * @return TRUE if file exist
 316          */
 317         function fileExists($file_hash, $file_name)
 318         {
 319                 $result = $this->query(
 320                 'SELECT hash AS hash, name AS name FROM file '.
 321                 'WHERE hash = '. $this->db->quote($file_hash, 'text'). ' '.
 322                 'AND name = '. $this->db->quote($file_name, 'text'));
 323                 $row = $result->fetchRow();
 324
 325                 if (is_object($row)) {
 326                         IrrecoLog::$database->log('file exist');
 327                         return TRUE;
 328                 }
 329                 IrrecoLog::$database->log('file not exist');
 330                 return FALSE;
 331         }
 332
 333         /**
 334          * Get ID of name.
 335          */
 336         function getNameId($table, $name)
 337         {
 338                 /* Attempt to fetch category. */
 339                 $result = $this->query(
 340                         'SELECT id AS id, name AS name FROM '.
 341                         $this->db->quoteIdentifier($table, 'text') . ' ' .
 342                         'WHERE name LIKE ' .
 343                         $this->db->quote($name, 'text'));
 344                 $row = $result->fetchRow();
 345
 346                 if (is_object($row)) {
 347                         $id = $row->id;
 348                         IrrecoLog::$database->log(
 349                                 'Id for name "' . $name .'" in table "' .
 350                                 $table . '" is "' . $id . '".', PEAR_LOG_DEBUG);
 351                 } else {
 352                         $id = NULL;
 353                 }
 354                 return $id;
 355         }
 356
 357         /**
 358          * Create ID for name if it does not exist, or get old ID of name.
 359          */
 360         function createNameId($table, $name)
 361         {
 362                 IrrecoLog::$database->log('Get id for name "' . $name .
 363                                           '" in table "' . $table . '".');
 364
 365                 // Lock the table
 366                 $this->lockTable($table);
 367
 368                 // Create category if it does not exists.
 369                 $id = $this->getNameId($table, $name);
 370                 if ($id != NULL) {
 371                         $this->unlockTables();
 372                         return $id;
 373                 } else {
 374                         $this->query('INSERT INTO ' .
 375                                      $this->db->quoteIdentifier($table, 'text').
 376                                      ' (name) VALUES(' .
 377                                      $this->db->quote($name, 'text') . ')');
 378                         $id = $this->db->lastInsertID();
 379                 }
 380
 381                 $this->unlockTables();
 382                 IrrecoLog::$database->log('Category ID: "' . $id . '".');
 383                 return $id;
 384         }
 385
 386         /**
 387          * Get list of categories that have configurations in them.
 388          *
 389          * @return Two dimensional array with category rows.
 390          */
 391         function getCategoryList()
 392         {
 393                 $result = $this->query(
 394                         'SELECT category.id AS id, '.
 395                         'category.name AS name FROM category '.
 396                         'RIGHT JOIN configuration '.
 397                         'ON category.id = configuration.category '.
 398                         'INNER JOIN file ON '.
 399                         'configuration.file_hash = file.hash AND '.
 400                         'configuration.file_name = file.name '.
 401                         'GROUP BY category.name');
 402                 $rows = $result->fetchAll();
 403                 return $rows;
 404         }
 405
 406         /**
 407          * Get whole list of all categories. Even those that don't
 408          * have configurations in them.
 409          *
 410          * @return Two dimensional array with all category rows.
 411          */
 412         function getWholeCategoryList()
 413         {
 414                 $result = $this->query(
 415                         'SELECT category.id AS id, '.
 416                         'category.name AS name FROM category '.
 417                         'GROUP BY category.name');
 418                 $rows = $result->fetchAll();
 419                 return $rows;
 420         }
 421
 422         /**
 423          * Get list of manufacturers that have configurations in them.
 424          *
 425          * @return Two dimensional array with manufacturer rows for category.
 426          */
 427         function getManufacturerList($category)
 428         {
 429                 IrrecoLog::$database->log('Category name: "' . $category . '".');
 430                 $result = $this->query(
 431                         'SELECT manufacturer.id AS id, '.
 432                         'manufacturer.name AS name FROM manufacturer '.
 433                         'RIGHT JOIN configuration '.
 434                         'ON manufacturer.id = configuration.manufacturer '.
 435                         'WHERE manufacturer.id IN '.
 436                         '(SELECT manufacturer FROM configuration '.
 437                         'INNER JOIN file ON '.
 438                         'configuration.file_hash = file.hash AND '.
 439                         'configuration.file_name = file.name '.
 440                         'RIGHT JOIN category '.
 441                         'ON category.id = configuration.category '.
 442                         "WHERE category.name = ".
 443                         $this->db->quote($category, 'text').") ".
 444                         'GROUP BY manufacturer.name');
 445                 $rows = $result->fetchAll();
 446                 return $rows;
 447         }
 448
 449         /**
 450          * Get list of all manufacturers. Even those that don't have
 451          * configurations in them.
 452          *
 453          * @return Two dimensional array with manufacturer rows.
 454          */
 455         function getWholeManufacturerList()
 456         {
 457                 IrrecoLog::$database->log('Get all manufacturers: "' . '".');
 458                 $result = $this->query(
 459                         'SELECT manufacturer.id AS id, '.
 460                         'manufacturer.name AS name FROM manufacturer '.
 461                         'GROUP BY manufacturer.name');
 462                 $rows = $result->fetchAll();
 463                 return $rows;
 464         }
 465
 466         /**
 467          * Get list of Models by manufacturer in selected category.
 468          *
 469          * @return Two dimensional array
 470          */
 471         function getModelList($category,$manufacturer)
 472         {
 473                 IrrecoLog::$database->log('Category name: "' .
 474                                           $category . '".');
 475                 IrrecoLog::$database->log('Manufacturer name: "' .
 476                                           $manufacturer . '".');
 477                 $result = $this->query(
 478                         'SELECT configuration.id AS id, '.
 479                         'configuration.model AS model FROM configuration '.
 480                         'RIGHT JOIN file ON '.
 481                         'configuration.file_hash = file.hash AND '.
 482                         'configuration.file_name = file.name '.
 483                         "WHERE configuration.category = ".
 484                         "(SELECT id FROM category WHERE name = ".
 485                         $this->db->quote($category, 'text').") ".
 486                         "AND configuration.manufacturer = ".
 487                         "(SELECT id FROM manufacturer WHERE name = ".
 488                         $this->db->quote($manufacturer, 'text').") ".
 489                         'GROUP BY configuration.model');
 490                 $rows = $result->fetchAll();
 491                 return $rows;
 492         }
 493
 494         /**
 495          * Get list of Configurations for Model
 496          *
 497          * @return Two dimensional array
 498          */
 499         function getConfigs($model)
 500         {
 501                 IrrecoLog::$database->log('Model name: "'.$model.'"');
 502
 503                 $result = $this->query(
 504                         'SELECT configuration.id AS id, '.
 505                         'user.name AS user FROM configuration '.
 506                         'LEFT JOIN user ON user.id = configuration.user '.
 507                         'RIGHT JOIN file ON '.
 508                         'configuration.file_hash = file.hash AND '.
 509                         'configuration.file_name = file.name '.
 510                         "WHERE configuration.model = ".
 511                         $this->db->quote($model, 'text')." ".
 512                         'ORDER BY user.name, file.uploaded DESC');
 513
 514                 $rows = $result->fetchAll();
 515
 516                 return $rows;
 517         }
 518
 519         /**
 520          * Get list of Configurations for Model and manufacturer
 521          *
 522          * @return Two dimensional array
 523          */
 524         function getConfigurations($manufacturer, $model)
 525         {
 526                 IrrecoLog::$database->log('Model name: "'.$model.'"');
 527
 528                 $result = $this->query(
 529                         'SELECT configuration.id AS id, '.
 530                         'user.name AS user FROM configuration '.
 531                         'LEFT JOIN user ON user.id = configuration.user '.
 532                         'RIGHT JOIN file ON '.
 533                         'configuration.file_hash = file.hash AND '.
 534                         'configuration.file_name = file.name '.
 535                         "WHERE configuration.model = ".
 536                         $this->db->quote($model, 'text')." ".
 537                         "AND configuration.manufacturer = ".
 538                         "(SELECT id FROM manufacturer WHERE name = ".
 539                         $this->db->quote($manufacturer, 'text').") ".
 540                         'ORDER BY user.name, file.uploaded DESC');
 541
 542                 $rows = $result->fetchAll();
 543
 544                 return $rows;
 545         }
 546
 547         /**
 548          * Get all data from category
 549          *
 550          * @return Two dimensional array
 551          */
 552         function getConfiguration($id)
 553         {
 554                 IrrecoLog::$database->log('Category id: "'.$id.'"');
 555
 556                 $result = $this->query(
 557                         'SELECT configuration.id AS id, '.
 558                         'user.name AS user, '.
 559                         'backend.name AS backend, '.
 560                         'category.name AS category, '.
 561                         'manufacturer.name AS manufacturer, '.
 562                         'configuration.model AS model, '.
 563                         'configuration.file_hash AS file_hash, '.
 564                         'configuration.file_name AS file_name, '.
 565                         'file.uploaded AS uploaded, '.
 566                         'file.download_count AS download_count '.
 567                         'FROM configuration RIGHT JOIN user '.
 568                         'ON configuration.user = user.id '.
 569                         'RIGHT JOIN backend ON '.
 570                         'configuration.backend = backend.id '.
 571                         'RIGHT JOIN category ON '.
 572                         'configuration.category = category.id '.
 573                         'RIGHT JOIN manufacturer ON '.
 574                         'configuration.manufacturer = manufacturer.id '.
 575                         'RIGHT JOIN file ON '.
 576                         'configuration.file_hash = file.hash AND '.
 577                         'configuration.file_name = file.name '.
 578                         "WHERE configuration.id = ".
 579                         $this->db->quote($id, 'text'));
 580
 581                 $rows = $result->fetchAll();
 582
 583                 return $rows;
 584         }
 585
 586         /**
 587          * Get configuration_id by name and date.
 588          *
 589          * Returns configuration_id or 0 if configuration does not exists.
 590          * @param string file_hash sha1-hash of file_data
 591          * @param string file_name Name for File
 592          * @return int
 593          */
 594         function getConfigIdByFilehashAndFilename($file_hash, $file_name)
 595         {
 596                 $result = $this->query(
 597                         'SELECT id AS id FROM configuration '.
 598                         'WHERE file_hash = '.
 599                         $this->db->quote($file_hash, 'text').' '.
 600                         'AND file_name = '.
 601                         $this->db->quote($file_name, 'text'));
 602                 $rows = $result->fetchAll();
 603                 $id = 0;
 604                 if ($rows != NULL) {
 605                         $id = intval($rows[0]->id);
 606                 }
 607                 return $id;
 608         }
 609
 610         /**
 611          * Get file data by hash and name
 612          *
 613          * @return array
 614          */
 615         function getFileData($hash,$name)
 616         {
 617                 IrrecoLog::$database->log('Hash: "' .
 618                                           $hash . '".');
 619                 IrrecoLog::$database->log('Name: "' .
 620                                           $name . '".');
 621                 $result = $this->query(
 622                         'SELECT file.data AS data '.
 623                         "FROM file WHERE file.hash = ".
 624                         $this->db->quote($hash, 'text')." ".
 625                         "AND file.name = ".
 626                         $this->db->quote($name, 'text'));
 627                 $rows = $result->fetchAll();
 628
 629                 //Update download_count. (timestamp will be updated
 630                 //automatically)
 631
 632                 $this->lockTable('file');
 633                 $this->query(
 634                         "UPDATE file SET download_count = download_count + 1 ".
 635                         "WHERE file.hash = ".
 636                         $this->db->quote($hash, 'text')." ".
 637                         "AND file.name = ".
 638                         $this->db->quote($name, 'text'));
 639                 $this->unlockTables();
 640
 641                 return $rows;
 642         }
 643
 644         /**
 645          * Create new Theme
 646          *
 647          *returns theme_id or 0
 648          * @return int
 649          */
 650         function createNewTheme($name, $comment, $preview_button,
 651                                 $folder, $user, $password)
 652         {
 653                 IrrecoLog::$database->log('Theme name: "'.$name.'" '.
 654                                           'Comment: "'.$comment.'" '.
 655                                           'Previewbutton: "'.$preview_button.'" '.
 656                                           'Folder: "'.$folder.'" '.
 657                                           'User: "'.$user.'" ');
 658
 659                 /* Check if username matches up to password and get id */
 660                 $user_id = $this->checkUsernameAndPassword($user, $password);
 661                 if($user_id == NULL) {
 662                         return('username and password mismatch.');
 663                 }
 664
 665                 $result = $this->query(
 666                         'INSERT INTO irreco.theme ( '.
 667                         'id, name, user_id, comment, preview_button, folder, '.
 668                         'uploaded, modified, downloaded, download_count) '.
 669                         'VALUES ( NULL, '.$this->db->quote($name, 'text').', '.
 670                         $this->db->quote($user_id, 'text').', '.
 671                         $this->db->quote($comment, 'text').', '.
 672                         $this->db->quote($preview_button, 'text').', '.
 673                         $this->db->quote($folder, 'text').', '.
 674                         'NOW(), NOW(), NOW(), 0)');
 675
 676                 $theme = $this->query(
 677                         'SELECT id AS id FROM irreco.theme '.
 678                         'WHERE uploaded = (SELECT MAX(uploaded) '.
 679                         'FROM irreco.theme)');
 680                 $row = $theme->fetchAll();
 681
 682                 return intval($row[0]->id);
 683         }
 684
 685         /**
 686          * Set Theme downloadable
 687          *
 688          * @param int id id-number for theme.
 689          * @param bool downloadable
 690          * @param string user Username
 691          * @param string password sha1-hash of password
 692          * @return int
 693          */
 694         function setThemeDownloadable($id, $downloadable, $user, $password)
 695         {
 696                 /* Check if username matches up to password and get id */
 697                 $user_id = $this->checkUsernameAndPassword($user, $password);
 698                 if($user_id == NULL) {
 699                         return('username and password mismatch.');
 700                 }
 701
 702                 $success = $this->query('UPDATE theme SET downloadable = '.
 703                                 $this->db->quote($downloadable, 'text').
 704                                 ' WHERE theme.id = '.
 705                                 $this->db->quote($id, 'text'));
 706                 return 1;
 707         }
 708
 709         /**
 710          * Get list of themes
 711          *
 712          * @return Two dimensional array.
 713          */
 714         function getThemeList()
 715         {
 716                 $result = $this->query(
 717                         'SELECT id AS id, name AS name FROM theme '.
 718                         'WHERE downloadable = 1 AND '.
 719                         'uploaded IN (SELECT MAX(uploaded) '.
 720                         'FROM theme WHERE downloadable = 1 '.
 721                         'GROUP BY name) ORDER BY name');
 722                 $rows = $result->fetchAll();
 723
 724                 if ($rows[0]->name == NULL) {
 725                         return('Database is empty.');
 726                 }
 727                 return $rows;
 728         }
 729
 730         /**
 731          * Get all data from theme
 732          *
 733          * Returns name, user, comment, preview_button, folder,
 734          * uploaded, modified, downloaded, download_count
 735          * @return Two dimensional array
 736          */
 737         function getTheme($id)
 738         {
 739                 IrrecoLog::$database->log('Theme id: "'.$id.'"');
 740
 741                 $result = $this->query(
 742                         'SELECT theme.id AS id, '.
 743                         'theme.name AS name, '.
 744                         'user.name AS user, '.
 745                         'theme.comment AS comment, '.
 746                         'theme.preview_button AS preview_button, '.
 747                         'theme.folder AS folder, '.
 748                         'theme.uploaded AS uploaded, '.
 749                         'theme.modified AS modified, '.
 750                         'theme.downloaded AS downloaded, '.
 751                         'theme.download_count AS download_count '.
 752                         'FROM theme RIGHT JOIN user '.
 753                         'ON theme.user_id = user.id '.
 754                         "WHERE theme.id = ".
 755                         $this->db->quote($id, 'text'));
 756
 757                 $rows = $result->fetchAll();
 758
 759                 return $rows;
 760         }
 761
 762         /**
 763          * Get Versions od Theme by name
 764          *
 765          * @return Two dimensional array.
 766          */
 767         function getThemeVersionsByName($name)
 768         {
 769                 $result = $this->query(
 770                         'SELECT id AS id, uploaded AS uploaded FROM theme '.
 771                         "WHERE name = ".$this->db->quote($name, 'text')." ".
 772                         'AND downloadable = 1 '.
 773                         'ORDER BY uploaded DESC');
 774                 $rows = $result->fetchAll();
 775                 return $rows;
 776         }
 777
 778         /**
 779          * Get theme_id by name and date.
 780          *
 781          * Returns theme_id or 0 if theme does not exists.
 782          * @param string name Name for theme.
 783          * @param string date theme creation date.
 784          * @return int
 785          */
 786         function getThemeIdByNameAndDate($name, $date)
 787         {
 788                 $result = $this->query(
 789                         'SELECT id AS id FROM theme '.
 790                         'WHERE name = '.$this->db->quote($name, 'text').' '.
 791                         'AND uploaded = '.$this->db->quote($date, 'text').' '.
 792                         'AND downloadable = 1 ');
 793                 $rows = $result->fetchAll();
 794                 $id = 0;
 795                 if ($rows != NULL) {
 796                         $id = intval($rows[0]->id);
 797                 }
 798                 return $id;
 799         }
 800
 801         /**
 802          * Add button to Theme
 803          *
 804          *returns button_id or 0
 805          * @return int
 806          */
 807         function addButtonToTheme($name, $allow_text, $text_format_up,
 808                                 $text_format_down, $text_padding,
 809                                 $text_h_align, $text_v_align,
 810                                 $image_up_hash, $image_up_name, $image_up,
 811                                 $image_down_hash, $image_down_name, $image_down,
 812                                 $folder, $theme_id, $user, $password)
 813         {
 814                 IrrecoLog::$database->log('Button name: "'.$name.'" '.
 815                                 'allow_text: "'.$allow_text.'" '.
 816                                 'text_format_up: "'.$text_format_up.'" '.
 817                                 'text_format_down: "'.$text_format_down.'" '.
 818                                 'text_padding: "'.$text_padding.'" '.
 819                                 'text_h_align: "'.$text_h_align.'" '.
 820                                 'text_v_align: "'.$text_v_align.'" '.
 821                                 'image_up_hash: "'.$image_up_hash.'" '.
 822                                 'image_up_name: "'.$image_up_name.'" '.
 823                                 'image_down_hash: "'.$image_down_hash.'" '.
 824                                 'image_down_name: "'.$image_down_name.'" '.
 825                                 'folder: "'.$folder.'" '.
 826                                 'theme_id: "'.$theme_id.'" '.
 827                                 'User: "'.$user.'" ');
 828
 829                 /* Check if username matches up to password and get id */
 830                 $user_id = $this->checkUsernameAndPassword($user, $password);
 831                 if($user_id == NULL) {
 832                         return('username and password mismatch.');
 833                 }
 834
 835                 /* Check if file hash matches with sha1 sum of file data */
 836                 if(sha1($image_up) != $image_up_hash ||
 837                    sha1($image_down) != $image_down_hash) {
 838                         return('file hash and file data mismatch.');
 839                 }
 840
 841                 /* insert data */
 842                 $this->lockTable('file');
 843
 844                 if (!$this->fileExists($image_up_hash, $image_up_name)) {
 845                         $this->query('INSERT INTO file VALUES ( '.
 846                              $this->db->quote($image_up_hash, 'text'). ', '.
 847                              $this->db->quote($image_up_name, 'text'). ', '.
 848                              $this->db->quote($image_up, 'text'). ', '.
 849                              'NOW(), NOW(), 0)');
 850                 }
 851                 if (!$this->fileExists($image_down_hash, $image_down_name)) {
 852                         $this->query('INSERT INTO file VALUES ( '.
 853                              $this->db->quote($image_down_hash, 'text'). ', '.
 854                              $this->db->quote($image_down_name, 'text'). ', '.
 855                              $this->db->quote($image_down, 'text'). ', '.
 856                              'NOW(), NOW(), 0)');
 857                 }
 858                 $allow_text_string = 'FALSE';
 859                 if ($allow_text == TRUE) {
 860                         $allow_text_string = 'TRUE';
 861                 }
 862
 863                 /* Unlock table */
 864                 $this->unlockTables();
 865
 866                 $button = $this->query(
 867                         'SELECT id AS id FROM irreco.button '.
 868                         'WHERE theme_id = '.
 869                         $this->db->quote($theme_id, 'text').' AND '.
 870                         'name = '.$this->db->quote($name, 'text'));
 871
 872                 $row = $button->fetchAll();
 873
 874                 if ($row[0]->id == NULL) {
 875                         $result = $this->query(
 876                         'INSERT INTO irreco.button ( '.
 877                         'id, name, allow_text, text_format_up, '.
 878                         'text_format_down, text_padding, '.
 879                         'text_h_align, text_v_align, image_up_hash, '.
 880                         'image_up_name, image_down_hash, image_down_name, '.
 881                         'folder, theme_id) '.
 882                         'VALUES ( NULL, '.$this->db->quote($name, 'text').', '.
 883                         $allow_text_string.', '.
 884                         $this->db->quote($text_format_up, 'text').', '.
 885                         $this->db->quote($text_format_down, 'text').', '.
 886                         $this->db->quote($text_padding, 'text').', '.
 887                         $this->db->quote($text_h_align, 'text').', '.
 888                         $this->db->quote($text_v_align, 'text').', '.
 889                         $this->db->quote($image_up_hash, 'text').', '.
 890                         $this->db->quote($image_up_name, 'text').', '.
 891                         $this->db->quote($image_down_hash, 'text').', '.
 892                         $this->db->quote($image_down_name, 'text').', '.
 893                         $this->db->quote($folder, 'text').', '.
 894                         $this->db->quote($theme_id, 'text').')');
 895
 896                         IrrecoLog::$database->log('Button added');
 897
 898                         $button = $this->query(
 899                         'SELECT id AS id FROM irreco.button '.
 900                         'WHERE theme_id = '.
 901                         $this->db->quote($theme_id, 'text').' AND '.
 902                         'name = '.$this->db->quote($name, 'text'));
 903
 904                         $row = $button->fetchAll();
 905                 } else {
 906                         $result = $this->query(
 907                         'UPDATE irreco.button SET '.
 908                         'allow_text = '.$allow_text_string.', '.
 909                         'text_format_up = '.
 910                         $this->db->quote($text_format_up, 'text').', '.
 911                         'text_format_down = '.
 912                         $this->db->quote($text_format_down, 'text').', '.
 913                         'text_padding = '.
 914                         $this->db->quote($text_padding, 'text').', '.
 915                         'text_h_align = '.
 916                         $this->db->quote($text_h_align, 'text').', '.
 917                         'text_v_align = '.
 918                         $this->db->quote($text_v_align, 'text').', '.
 919                         'image_up_hash = '.
 920                         $this->db->quote($image_up_hash, 'text').', '.
 921                         'image_up_name = '.
 922                         $this->db->quote($image_up_name, 'text').', '.
 923                         'image_down_hash = '.
 924                         $this->db->quote($image_down_hash, 'text').', '.
 925                         'image_down_name = '.
 926                         $this->db->quote($image_down_name, 'text').', '.
 927                         'folder = '.$this->db->quote($folder, 'text').' '.
 928                         'WHERE theme_id = '.
 929                         $this->db->quote($theme_id, 'text').' AND '.
 930                         'name = '.$this->db->quote($name, 'text'));
 931
 932                         IrrecoLog::$database->log('Button updated');
 933                 }
 934
 935                 return intval($row[0]->id);
 936         }
 937
 938         /**
 939          * Get list of buttons for theme
 940          *
 941          * @return Two dimensional array.
 942          */
 943         function getButtonList($theme_id)
 944         {
 945                 $result = $this->query(
 946                         'SELECT id AS id, name AS name FROM button '.
 947                         'WHERE theme_id = '.
 948                         $this->db->quote($theme_id, 'text').' '.
 949                         'ORDER BY name');
 950                 $rows = $result->fetchAll();
 951                 return $rows;
 952         }
 953
 954         /**
 955          * Get all data from Button
 956          *
 957          * Returns name, allow_text, text_format_up, text_format_down,
 958          * text_padding, image_up_hash, image_up_name, base64 encoded image_up,
 959          * image_down_hash, image_down_name, base64 encoded image_down, folder,
 960          * theme_id
 961          * @param int id id-number for button.
 962          * @return struct
 963          */
 964         function getButton($id)
 965         {
 966                 IrrecoLog::$database->log('Button id: "'.$id.'"');
 967
 968                 $result = $this->query('SELECT name AS name, '.
 969                                        'allow_text AS allow_text, '.
 970                                        'text_format_up AS text_format_up, '.
 971                                        'text_format_down AS text_format_down, '.
 972                                        'text_padding AS text_padding, '.
 973                                        'text_h_align AS text_h_align, '.
 974                                        'text_v_align AS text_v_align, '.
 975                                        'image_up_hash AS image_up_hash, '.
 976                                        'image_up_name AS image_up_name, '.
 977                                        'image_down_hash AS image_down_hash, '.
 978                                        'image_down_name AS image_down_name, '.
 979                                        'folder AS folder, '.
 980                                        'theme_id AS theme_id '.
 981                                        'FROM button WHERE id = '.
 982                                         $this->db->quote($id, 'text'));
 983                 $rows = $result->fetchAll();
 984
 985                 $image_up = $this->getFileData($rows[0]->image_up_hash,
 986                                                $rows[0]->image_up_name);
 987
 988                 $image_down = $this->getFileData($rows[0]->image_down_hash,
 989                                                  $rows[0]->image_down_name);
 990
 991                 //Set the type of a variable for text-format
 992                 settype($rows[0]->text_format_up, "string");
 993                 settype($rows[0]->text_format_down, "string");
 994
 995                 settype ( $rows[0]->allow_text , "boolean");
 996                 $array = array();
 997                 $array['name'] = $rows[0]->name;
 998                 $array['allow_text'] = $rows[0]->allow_text;
 999                 $array['text_format_up'] = $rows[0]->text_format_up;
1000                 $array['text_format_down'] = $rows[0]->text_format_down;
1001                 $array['text_padding'] = intval($rows[0]->text_padding);
1002                 $array['text_h_align'] = doubleval($rows[0]->text_h_align);
1003                 $array['text_v_align'] = doubleval($rows[0]->text_v_align);
1004                 $array['image_up_hash'] = $rows[0]->image_up_hash;
1005                 $array['image_up_name'] = $rows[0]->image_up_name;
1006                 $array['image_up'] = base64_encode($image_up[0]->data);
1007                 $array['image_down_hash'] = $rows[0]->image_down_hash;
1008                 $array['image_down_name'] = $rows[0]->image_down_name;
1009                 $array['image_down'] = base64_encode($image_down[0]->data);
1010                 $array['folder'] = $rows[0]->folder;
1011
1012                 $theme_id = $rows[0]->theme_id;
1013
1014                 $last_button = $this->query('SELECT MAX(id) AS id '.
1015                                             'FROM button WHERE theme_id = '.
1016                                             $theme_id);
1017                 $button = $last_button->fetchAll();
1018
1019                 if($button[0]->id == $id)
1020                 {
1021                         $this->query('UPDATE theme SET download_count = '.
1022                                      'download_count + 1 WHERE id = '.
1023                                      $theme_id);
1024                 }
1025
1026                 return $array;
1027         }
1028
1029         /**
1030          * Get preview button
1031          *
1032          * Returns image-data
1033          * @param int id id-number for theme.
1034          * @return string
1035          */
1036         function getPreviewButton($theme_id)
1037         {
1038                 IrrecoLog::$database->log('Theme id: "'.$theme_id.'"');
1039
1040                 $result = $this->query(
1041                         'SELECT button.image_up_hash AS hash, '.
1042                         'button.image_up_name AS name FROM button '.
1043                         'WHERE button.name = (SELECT preview_button '.
1044                         'FROM theme WHERE id = '.
1045                         $this->db->quote($theme_id, 'text').') AND '.
1046                         'button.theme_id = '.
1047                         $this->db->quote($theme_id, 'text'));
1048
1049                 $rows = $result->fetchAll();
1050
1051                 $data = $this->getFileData($rows[0]->hash, $rows[0]->name);
1052
1053                 return $data[0]->data;
1054         }
1055         /**
1056          * Add background to Theme
1057          *
1058          *returns bg_id or 0
1059          * @return int
1060          */
1061         function addBgToTheme($name, $image_hash, $image_name, $image,
1062                               $folder, $theme_id, $user, $password)
1063         {
1064                 IrrecoLog::$database->log('Bg name: "'.$name.'" '.
1065                                           'image_hash: "'.$image_up_hash.'" '.
1066                                           'image_name: "'.$image_up_name.'" '.
1067                                           'folder: "'.$folder.'" '.
1068                                           'theme_id: "'.$theme_id.'" '.
1069                                           'User: "'.$user.'" ');
1070
1071                 /* Check if username matches up to password and get id */
1072                 $user_id = $this->checkUsernameAndPassword($user, $password);
1073                 if($user_id == NULL) {
1074                         return('username and password mismatch.');
1075                 }
1076
1077                 /* Check if file hash matches with sha1 sum of file data */
1078                 if(sha1($image) != $image_hash) {
1079                         return('file hash and file data mismatch.');
1080                 }
1081
1082                 /* insert data */
1083                 $this->lockTable('file');
1084
1085                 if (!$this->fileExists($image_hash, $image_name)) {
1086                         $this->query('INSERT INTO file VALUES ( '.
1087                              $this->db->quote($image_hash, 'text'). ', '.
1088                              $this->db->quote($image_name, 'text'). ', '.
1089                              $this->db->quote($image, 'text'). ', '.
1090                              'NOW(), NOW(), 0)');
1091                 }
1092
1093                 /* Unlock table */
1094                 $this->unlockTables();
1095
1096                 $bg = $this->query(
1097                         'SELECT id AS id FROM irreco.bg '.
1098                         'WHERE theme_id = '.
1099                         $this->db->quote($theme_id, 'text').' AND '.
1100                         'name = '.$this->db->quote($name, 'text'));
1101                 $row = $bg->fetchAll();
1102
1103                 if ($row[0]->id == NULL) {
1104                         $result = $this->query(
1105                         'INSERT INTO irreco.bg ( '.
1106                         'id, name, image_hash, image_name, folder, theme_id) '.
1107                         'VALUES ( NULL, '.$this->db->quote($name, 'text').', '.
1108                         $this->db->quote($image_hash, 'text').', '.
1109                         $this->db->quote($image_name, 'text').', '.
1110                         $this->db->quote($folder, 'text').', '.
1111                         $this->db->quote($theme_id, 'text').')');
1112
1113                         if ($result == 1) {
1114                                 IrrecoLog::$database->log('BG added');
1115                         }
1116
1117                         $bg = $this->query(
1118                         'SELECT id AS id FROM irreco.bg '.
1119                         'WHERE theme_id = '.
1120                         $this->db->quote($theme_id, 'text').' AND '.
1121                         'name = '.$this->db->quote($name, 'text'));
1122
1123                         $row = $bg->fetchAll();
1124
1125                 } else {
1126                         $result = $this->query(
1127                         'UPDATE irreco.bg SET '.
1128                         'image_hash = '.
1129                         $this->db->quote($image_hash, 'text').', '.
1130                         'image_name = '.
1131                         $this->db->quote($image_name, 'text').', '.
1132                         'folder = '.$this->db->quote($folder, 'text').' '.
1133                         'WHERE theme_id = '.
1134                         $this->db->quote($theme_id, 'text').' AND '.
1135                         'name = '.$this->db->quote($name, 'text'));
1136
1137                         if ($result == 1) {
1138                                 IrrecoLog::$database->log('BG updated');
1139                         }
1140                 }
1141
1142
1143
1144                 return intval($row[0]->id);
1145         }
1146
1147         /**
1148          * Get list of backgrounds for theme
1149          *
1150          * @return Two dimensional array.
1151          */
1152         function getBgList($theme_id)
1153         {
1154                 $result = $this->query(
1155                         'SELECT id AS id, name AS name FROM bg '.
1156                         'WHERE theme_id = '.
1157                         $this->db->quote($theme_id, 'text').' '.
1158                         'ORDER BY name');
1159                 $rows = $result->fetchAll();
1160                 return $rows;
1161         }
1162
1163         /**
1164          * Get all data from Background
1165          *
1166          * Returns name, image_hash, image_name,
1167          * folder and base64 encoded image_data
1168          * @param int id id-number for background.
1169          * @return struct
1170          */
1171         function getBg($id)
1172         {
1173                 IrrecoLog::$database->log('Bg id: "'.$id.'"');
1174
1175                 $result = $this->query('SELECT name AS name, '.
1176                                        'image_hash AS hash, '.
1177                                        'image_name AS file_name, '.
1178                                        'folder AS folder '.
1179                                        'FROM bg WHERE id = '.
1180                                         $this->db->quote($id, 'text'));
1181                 $rows = $result->fetchAll();
1182
1183                 $data = $this->getFileData($rows[0]->hash, $rows[0]->file_name);
1184
1185                 $array = array();
1186                 $array['name'] = $rows[0]->name;
1187                 $array['image_hash'] = $rows[0]->hash;
1188                 $array['image_name'] = $rows[0]->file_name;
1189                 $array['image_data'] = base64_encode($data[0]->data);
1190                 $array['folder'] = $rows[0]->folder;
1191
1192                 return $array;
1193         }
1194
1195         /**
1196          * Login to database
1197          *
1198          * @param string user Username
1199          * @param string password sha1-hash of password
1200          * @return boolean
1201          */
1202         function loginToDB($user, $password)
1203         {
1204
1205                 /* Check if username matches up to password and get id */
1206                 $user_id = $this->checkUsernameAndPassword($user, $password);
1207                 if($user_id == NULL)
1208                 {
1209                         return FALSE;
1210                 }
1211
1212                 return TRUE;
1213         }
1214
1215         /**
1216          * Create new Remote
1217          *
1218          *returns remote_id or 0
1219          * @return int
1220          */
1221         function createNewRemote($comment, $category, $manufacturer,
1222                                  $model, $file_hash, $file_name, $file_data,
1223                                  $user, $password)
1224         {
1225                 IrrecoLog::$database->log('Remote model: "'.$model.'" '.
1226                                           'Comment: "'.$comment.'" '.
1227                                           'Category: "'.$category.'" '.
1228                                           'Manufacturer: "'.$manufacturer);
1229
1230                 /* Check if username matches up to password and get id */
1231                 $user_id = $this->checkUsernameAndPassword($user, $password);
1232                 if($user_id == NULL) {
1233                         return('username and password mismatch.');
1234                 }
1235
1236                 /* Check if file hash matches with sha1 sum of file data */
1237                 if(sha1($file_data) != $file_hash) {
1238                         return('file hash and file data mismatch.');
1239                 }
1240
1241                 /* get id for backend, category and manufacturer */
1242                 /* create new if needed */
1243                 $category_id = $this->createNameId('category', $category);
1244                 $manufacturer_id = $this->createNameId('manufacturer',
1245                                                       $manufacturer);
1246
1247                 if (!$this->fileExists($file_hash, $file_name)) {
1248                         $this->query('INSERT INTO file VALUES ( '.
1249                                 $this->db->quote($file_hash, 'text'). ', '.
1250                                 $this->db->quote($file_name, 'text'). ', '.
1251                                 $this->db->quote($file_data, 'text'). ', '.
1252                                 'NOW(), NOW(), 0)');
1253                 }
1254
1255                 $result = $this->query(
1256                         'INSERT INTO irreco.remote ( '.
1257                         'id, user_id, comment, category, manufacturer, '.
1258                         'model, file_hash, file_name, uploaded, modified, '.
1259                         'downloaded, download_count, downloadable) '.
1260                         'VALUES ( NULL, '.
1261                         $this->db->quote($user_id, 'text').', '.
1262                         $this->db->quote($comment, 'text').', '.
1263                         $this->db->quote($category_id, 'text').', '.
1264                         $this->db->quote($manufacturer_id, 'text').', '.
1265                         $this->db->quote($model, 'text').', '.
1266                         $this->db->quote($file_hash, 'text').', '.
1267                         $this->db->quote($file_name, 'text').', '.
1268                         'NOW(), NOW(), NOW(), 0, 0)');
1269
1270                 $theme = $this->query(
1271                         'SELECT id AS id FROM irreco.remote '.
1272                         'WHERE uploaded = (SELECT MAX(uploaded) '.
1273                         'FROM irreco.remote)');
1274                 $row = $theme->fetchAll();
1275
1276                 return intval($row[0]->id);
1277         }
1278
1279         /**
1280          * Set Remote downloadable
1281          *
1282          * @param int id id-number for remote.
1283          * @param bool downloadable
1284          * @param string user Username
1285          * @param string password sha1-hash of password
1286          * @return int
1287          */
1288         function setRemoteDownloadable($id, $downloadable, $user, $password)
1289         {
1290                 /* Check if username matches up to password and get id */
1291                 $user_id = $this->checkUsernameAndPassword($user, $password);
1292                 if($user_id == NULL) {
1293                         return('username and password mismatch.');
1294                 }
1295
1296                 $success = $this->query('UPDATE remote SET downloadable = '.
1297                                         $this->db->quote($downloadable, 'text').
1298                                         ' WHERE remote.id = '.
1299                                         $this->db->quote($id, 'text'));
1300                 return 1;
1301         }
1302
1303         function addConfigurationToRemote($remote_id, $config_id,
1304                                           $user, $password)
1305         {
1306                 /* Check if username matches up to password and get id */
1307                 $user_id = $this->checkUsernameAndPassword($user, $password);
1308                 if($user_id == NULL) {
1309                         return('username and password mismatch.');
1310                 }
1311
1312                 $result = $this->query('SELECT id AS id '.
1313                                 'FROM remote_configuration WHERE '.
1314                                 'remote_id = '.
1315                                 $this->db->quote($remote_id, 'text').' AND '.
1316                                 'configuration_id = '.
1317                                 $this->db->quote($config_id, 'text'));
1318                 $rows = $result->fetchAll();
1319
1320                 if ($rows == NULL) {
1321                         $success = $this->query(
1322                                 'INSERT INTO remote_configuration '.
1323                                 '(remote_id, configuration_id) '.
1324                                 'VALUES ( '.
1325                                 $this->db->quote($remote_id, 'text').', '.
1326                                 $this->db->quote($config_id, 'text').')');
1327                 }
1328                 return 1;
1329         }
1330
1331         function addThemeToRemote($remote_id, $theme_id, $user, $password)
1332         {
1333                 /* Check if username matches up to password and get id */
1334                 $user_id = $this->checkUsernameAndPassword($user, $password);
1335                 if($user_id == NULL) {
1336                         return('username and password mismatch.');
1337                 }
1338
1339                 $result = $this->query('SELECT id AS id '.
1340                                 'FROM remote_theme WHERE '.
1341                                 'remote_id = '.
1342                                 $this->db->quote($remote_id, 'text').' AND '.
1343                                 'theme_id = '.
1344                                 $this->db->quote($theme_id, 'text'));
1345                 $rows = $result->fetchAll();
1346
1347                 if ($rows == NULL) {
1348                         $success = $this->query(
1349                                 'INSERT INTO irreco.remote_theme '.
1350                                 '(id, remote_id, theme_id) '.
1351                                 'VALUES ( NULL, '.
1352                                 $this->db->quote($remote_id, 'text').', '.
1353                                 $this->db->quote($theme_id, 'text').')');
1354                 }
1355                 return 1;
1356         }
1357
1358         /**
1359          * Get list of categories that have remotes in them.
1360          *
1361          * @return Two dimensional array with category rows.
1362          */
1363         function getRemoteCategories()
1364         {
1365                 $result = $this->query(
1366                         'SELECT category.id AS id, '.
1367                         'category.name AS name FROM category '.
1368                         'RIGHT JOIN remote '.
1369                         'ON category.id = remote.category '.
1370                         'INNER JOIN file ON '.
1371                         'remote.file_hash = file.hash AND '.
1372                         'remote.file_name = file.name '.
1373                         'AND remote.downloadable = TRUE '.
1374                         'GROUP BY category.name');
1375                 $rows = $result->fetchAll();
1376                 return $rows;
1377         }
1378
1379         /**
1380          * Get list of manufacturers that have remotes in them.
1381          *
1382          * @return Two dimensional array with manufacturer rows for category.
1383          */
1384         function getRemoteManufacturers($category)
1385         {
1386                 IrrecoLog::$database->log('Category name: "' . $category . '".');
1387                 $result = $this->query(
1388                         'SELECT manufacturer.id AS id, '.
1389                         'manufacturer.name AS name FROM manufacturer '.
1390                         'RIGHT JOIN remote '.
1391                         'ON manufacturer.id = remote.manufacturer '.
1392                         'WHERE manufacturer.id IN '.
1393                         '(SELECT manufacturer FROM remote '.
1394                         'INNER JOIN file ON '.
1395                         'remote.file_hash = file.hash AND '.
1396                         'remote.file_name = file.name '.
1397                         'RIGHT JOIN category '.
1398                         'ON category.id = remote.category '.
1399                         'WHERE category.name = '.
1400                         $this->db->quote($category, 'text').') '.
1401                         'AND remote.downloadable = TRUE '.
1402                         'GROUP BY manufacturer.name');
1403                 $rows = $result->fetchAll();
1404                 return $rows;
1405         }
1406
1407         /**
1408          * Get list of Models by manufacturer in selected category.
1409          *
1410          * @return Two dimensional array
1411          */
1412         function getRemoteModels($category,$manufacturer)
1413         {
1414                 $result = $this->query(
1415                         'SELECT remote.id AS id, '.
1416                         'remote.model AS model FROM remote '.
1417                         'RIGHT JOIN file ON '.
1418                         'remote.file_hash = file.hash AND '.
1419                         'remote.file_name = file.name '.
1420                         'WHERE remote.category = '.
1421                         '(SELECT id FROM category WHERE name = '.
1422                         $this->db->quote($category, 'text').') '.
1423                         'AND remote.manufacturer = '.
1424                         '(SELECT id FROM manufacturer WHERE name = '.
1425                         $this->db->quote($manufacturer, 'text').') '.
1426                         'AND remote.downloadable = TRUE '.
1427                         'GROUP BY remote.model');
1428                 $rows = $result->fetchAll();
1429                 return $rows;
1430         }
1431
1432         /**
1433          * Get Creators for model.
1434          *
1435          * @param string category Name for Category. e.g. "Tv"
1436          * @param string manufacturer Name for Manufacturer. e.g. "Sony"
1437          * @param string model Name for Model. e.g. "xyz"
1438          * @return array
1439          */
1440         function getRemoteCreators($category, $manufacturer, $model)
1441         {
1442                 $result = $this->query(
1443                         'SELECT remote.id AS id, '.
1444                         'user.name AS user FROM remote '.
1445                         'LEFT JOIN user ON user.id = remote.user_id '.
1446                         'WHERE remote.model = '.
1447                         $this->db->quote($model, 'text').' '.
1448                         'AND remote.manufacturer = '.
1449                         '(SELECT id FROM manufacturer WHERE name = '.
1450                         $this->db->quote($manufacturer, 'text').') '.
1451                         'AND remote.category = '.
1452                         '(SELECT id FROM category WHERE name = '.
1453                         $this->db->quote($category, 'text').') '.
1454                         'AND remote.downloadable = TRUE '.
1455                         'GROUP BY user '.
1456                         'ORDER BY user.name');
1457                 $rows = $result->fetchAll();
1458                 return $rows;
1459         }
1460
1461         /**
1462          * Get Remotes
1463          *
1464          * Returns list of remote_id
1465          * @param string category Name for Category. e.g. "Tv"
1466          * @param string manufacturer Name for Manufacturer. e.g. "Sony"
1467          * @param string model Name for Model. e.g. "xyz"
1468          * @param string user Username of Remote creator
1469          * @return Two dimensional array
1470          */
1471         function getRemotes($category, $manufacturer, $model, $user)
1472         {
1473                 $result = $this->query(
1474                         'SELECT remote.id AS id FROM remote '.
1475                         'WHERE remote.user_id = '.
1476                         '(SELECT id FROM user WHERE name = '.
1477                         $this->db->quote($user, 'text').') '.
1478                         'AND remote.model = '.
1479                         $this->db->quote($model, 'text').' '.
1480                         'AND remote.manufacturer = '.
1481                         '(SELECT id FROM manufacturer WHERE name = '.
1482                         $this->db->quote($manufacturer, 'text').') '.
1483                         'AND remote.category = '.
1484                         '(SELECT id FROM category WHERE name = '.
1485                         $this->db->quote($category, 'text').') '.
1486                         'AND remote.downloadable = TRUE '.
1487                         'ORDER BY remote.uploaded DESC');
1488                 $rows = $result->fetchAll();
1489
1490                 return $rows;
1491         }
1492
1493         /**
1494          * Get all data from remote
1495          *
1496          * Returns name, user, comment,  category, manufacturer,
1497          * model, file_hash, file_name, uploaded, modified, downloaded,
1498          * and download_count
1499          * @param int id id-number for remote.
1500          * @return struct
1501          */
1502         function getRemoteById($id)
1503         {
1504                 $data = $this->query(
1505                         'SELECT remote.id AS id, '.
1506                         'user.name AS user, '.
1507                         'remote.comment AS comment, '.
1508                         'category.name AS category, '.
1509                         'manufacturer.name AS manufacturer, '.
1510                         'remote.model AS model, '.
1511                         'remote.file_hash AS file_hash, '.
1512                         'remote.file_name AS file_name, '.
1513                         'remote.uploaded AS uploaded, '.
1514                         'remote.modified AS modified, '.
1515                         'remote.downloaded AS downloaded, '.
1516                         'remote.download_count AS download_count '.
1517                         'FROM remote RIGHT JOIN user '.
1518                         'ON remote.user_id = user.id '.
1519                         'RIGHT JOIN category ON '.
1520                         'remote.category = category.id '.
1521                         'RIGHT JOIN manufacturer ON '.
1522                         'remote.manufacturer = manufacturer.id '.
1523                         'WHERE remote.id = '.
1524                         $this->db->quote($id, 'text'));
1525
1526                 $rows = $data->fetchAll();
1527
1528                 $array = array();
1529
1530                 $array['user'] = $rows[0]->user;
1531                 $array['comment'] = $rows[0]->comment;
1532                 $array['category'] = $rows[0]->category;
1533                 $array['manufacturer'] = $rows[0]->manufacturer;
1534                 $array['model'] = $rows[0]->model;
1535                 $array['file_hash'] = $rows[0]->file_hash;
1536                 $array['file_name'] = $rows[0]->file_name;
1537                 $array['uploaded'] = $rows[0]->uploaded;
1538                 $array['modified'] = $rows[0]->modified;
1539                 $array['downloaded'] = $rows[0]->downloaded;
1540                 $array['download_count'] = intval($rows[0]->download_count);
1541
1542                 return $array;
1543         }
1544
1545         /**
1546          * Get themes of remote
1547          *
1548          * @param int remote_id id-number for remote.
1549          * @return array
1550          */
1551         function getThemesOfRemote($remote_id)
1552         {
1553                 $result = $this->query(
1554                         'SELECT remote_theme.theme_id AS id, '.
1555                         'theme.name AS name FROM remote_theme '.
1556                         'RIGHT JOIN theme ON '.
1557                         'remote_theme.theme_id = theme.id '.
1558                         'WHERE remote_theme.remote_id = '.
1559                         $this->db->quote($remote_id, 'text').' '.
1560                         'ORDER BY theme.name');
1561                 $rows = $result->fetchAll();
1562
1563                 if ($rows == NULL) {
1564                         return("Can't find any theme of remote.");
1565                 }
1566                 return $rows;
1567         }
1568
1569         /**
1570          * Get configurations of remote
1571          *
1572          * @param int remote_id id-number for remote.
1573          * @return array
1574          */
1575         function getConfigurationsOfRemote($remote_id)
1576         {
1577                 $result = $this->query(
1578                         'SELECT remote_configuration.configuration_id AS id, '.
1579                         'configuration.model AS model '.
1580                         'FROM remote_configuration '.
1581                         'RIGHT JOIN configuration ON '.
1582                         'remote_configuration.configuration_id = '.
1583                         'configuration.id '.
1584                         'WHERE remote_configuration.remote_id = '.
1585                         $this->db->quote($remote_id, 'text').' '.
1586                         'ORDER BY configuration.model');
1587                 $rows = $result->fetchAll();
1588
1589                 if ($rows == NULL) {
1590                         return("Can't find any configuration of remote.");
1591                 }
1592                 return $rows;
1593         }
1594 }
1595
1596 ?>