"Fossies" - the Fresh Open Source Software Archive

Member "ampache-5.0.0/src/Repository/Model/Search.php" (31 Aug 2021, 125635 Bytes) of package /linux/www/ampache-5.0.0.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) PHP source code syntax highlighting (style: standard) with prefixed line numbers and code folding option. Alternatively you can here view or download the uninterpreted source code file. For more information about "Search.php" see the Fossies "Dox" file reference documentation.

    1 <?php
    2 /*
    3  * vim:set softtabstop=4 shiftwidth=4 expandtab:
    4  *
    5  * LICENSE: GNU Affero General Public License, version 3 (AGPL-3.0-or-later)
    6  * Copyright 2001 - 2020 Ampache.org
    7  *
    8  * This program is free software: you can redistribute it and/or modify
    9  * it under the terms of the GNU Affero General Public License as published by
   10  * the Free Software Foundation, either version 3 of the License, or
   11  * (at your option) any later version.
   12  *
   13  * This program is distributed in the hope that it will be useful,
   14  * but WITHOUT ANY WARRANTY; without even the implied warranty of
   15  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   16  * GNU Affero General Public License for more details.
   17  *
   18  * You should have received a copy of the GNU Affero General Public License
   19  * along with this program.  If not, see <https://www.gnu.org/licenses/>.
   20  */
   21 
   22 declare(strict_types=0);
   23 
   24 namespace Ampache\Repository\Model;
   25 
   26 use Ampache\Module\Authorization\Access;
   27 use Ampache\Repository\Model\Metadata\Repository\MetadataField;
   28 use Ampache\Module\System\Dba;
   29 use Ampache\Config\AmpConfig;
   30 use Ampache\Module\System\Core;
   31 use Ampache\Repository\LicenseRepositoryInterface;
   32 use Ampache\Repository\UserRepositoryInterface;
   33 
   34 /**
   35  * Search-related voodoo.  Beware tentacles.
   36  */
   37 class Search extends playlist_object
   38 {
   39     protected const DB_TABLENAME = 'search';
   40 
   41     public $searchtype;
   42     public $rules          = array();
   43     public $logic_operator = 'AND';
   44     public $type           = 'public';
   45     public $random         = 0;
   46     public $limit          = 0;
   47     public $last_count     = 0;
   48     public $last_duration  = 0;
   49     public $date           = 0;
   50 
   51     public $basetypes;
   52     public $types;
   53 
   54     public $link;
   55     public $f_link;
   56 
   57     public $search_user;
   58 
   59     private $stars;
   60     private $order_by;
   61 
   62     /**
   63      * constructor
   64      * @param integer $search_id
   65      * @param string $searchtype
   66      * @param User|null $user
   67      */
   68     public function __construct($search_id = 0, $searchtype = 'song', ?User $user = null)
   69     {
   70         if ($user->id) {
   71             $this->search_user = $user;
   72         } else {
   73             $this->search_user = Core::get_global('user');
   74         }
   75         $this->searchtype = $searchtype;
   76         if ($search_id > 0) {
   77             $info = $this->get_info($search_id);
   78             foreach ($info as $key => $value) {
   79                 $this->$key = $value;
   80             }
   81             $this->rules = json_decode((string)$this->rules, true);
   82         }
   83         $this->date = time();
   84 
   85         $this->stars = array(
   86             T_('0 Stars'),
   87             T_('1 Star'),
   88             T_('2 Stars'),
   89             T_('3 Stars'),
   90             T_('4 Stars'),
   91             T_('5 Stars')
   92         );
   93 
   94         // Define our basetypes
   95         $this->set_basetypes();
   96 
   97         $this->types = array();
   98         switch ($searchtype) {
   99             case 'song':
  100                 $this->song_types();
  101                 $this->order_by = '`song`.`file`';
  102                 break;
  103             case 'album':
  104                 $this->album_types();
  105                 $this->order_by = (AmpConfig::get('album_group')) ? '`album`.`name`' : '`album`.`name`, `album`.`disk`';
  106                 break;
  107             case 'video':
  108                 $this->video_types();
  109                 $this->order_by = '`video`.`file`';
  110                 break;
  111             case 'artist':
  112                 $this->artist_types();
  113                 $this->order_by = '`artist`.`name`';
  114                 break;
  115             case 'playlist':
  116                 $this->playlist_types();
  117                 $this->order_by = '`playlist`.`name`';
  118                 break;
  119             case 'label':
  120                 $this->label_types();
  121                 $this->order_by = '`label`.`name`';
  122                 break;
  123             case 'user':
  124                 $this->user_types();
  125                 $this->order_by = '`user`.`username`';
  126                 break;
  127             case 'tag':
  128             case 'genre':
  129                 $this->tag_types();
  130                 $this->order_by = '`tag`.`name`';
  131                 break;
  132         } // end switch on searchtype
  133     } // end constructor
  134 
  135     /**
  136      * set_basetypes
  137      *
  138      * Function called during construction to set the different types and rules for search
  139      */
  140     private function set_basetypes()
  141     {
  142         $this->basetypes['numeric'][] = array(
  143             'name' => 'gte',
  144             'description' => T_('is greater than or equal to'),
  145             'sql' => '>='
  146         );
  147 
  148         $this->basetypes['numeric'][] = array(
  149             'name' => 'lte',
  150             'description' => T_('is less than or equal to'),
  151             'sql' => '<='
  152         );
  153 
  154         $this->basetypes['numeric'][] = array(
  155             'name' => 'equal',
  156             'description' => T_('equals'),
  157             'sql' => '<=>'
  158         );
  159 
  160         $this->basetypes['numeric'][] = array(
  161             'name' => 'ne',
  162             'description' => T_('does not equal'),
  163             'sql' => '<>'
  164         );
  165 
  166         $this->basetypes['numeric'][] = array(
  167             'name' => 'gt',
  168             'description' => T_('is greater than'),
  169             'sql' => '>'
  170         );
  171 
  172         $this->basetypes['numeric'][] = array(
  173             'name' => 'lt',
  174             'description' => T_('is less than'),
  175             'sql' => '<'
  176         );
  177 
  178         $this->basetypes['is_true'][] = array(
  179             'name' => 'true',
  180             'description' => T_('is true'),
  181             'sql' => '1'
  182         );
  183 
  184         $this->basetypes['boolean'][] = array(
  185             'name' => 'true',
  186             'description' => T_('is true'),
  187             'sql' => '1'
  188         );
  189 
  190         $this->basetypes['boolean'][] = array(
  191             'name' => 'false',
  192             'description' => T_('is false'),
  193             'sql' => '0'
  194         );
  195 
  196         $this->basetypes['text'][] = array(
  197             'name' => 'contain',
  198             'description' => T_('contains'),
  199             'sql' => 'LIKE',
  200             'preg_match' => array('/^/', '/$/'),
  201             'preg_replace' => array('%', '%')
  202         );
  203 
  204         $this->basetypes['text'][] = array(
  205             'name' => 'notcontain',
  206             'description' => T_('does not contain'),
  207             'sql' => 'NOT LIKE',
  208             'preg_match' => array('/^/', '/$/'),
  209             'preg_replace' => array('%', '%')
  210         );
  211 
  212         $this->basetypes['text'][] = array(
  213             'name' => 'start',
  214             'description' => T_('starts with'),
  215             'sql' => 'LIKE',
  216             'preg_match' => '/$/',
  217             'preg_replace' => '%'
  218         );
  219 
  220         $this->basetypes['text'][] = array(
  221             'name' => 'end',
  222             'description' => T_('ends with'),
  223             'sql' => 'LIKE',
  224             'preg_match' => '/^/',
  225             'preg_replace' => '%'
  226         );
  227 
  228         $this->basetypes['text'][] = array(
  229             'name' => 'equal',
  230             'description' => T_('is'),
  231             'sql' => '='
  232         );
  233 
  234         $this->basetypes['text'][] = array(
  235             'name' => 'not equal',
  236             'description' => T_('is not'),
  237             'sql' => '!='
  238         );
  239 
  240         $this->basetypes['text'][] = array(
  241             'name' => 'sounds',
  242             'description' => T_('sounds like'),
  243             'sql' => 'SOUNDS LIKE'
  244         );
  245 
  246         $this->basetypes['text'][] = array(
  247             'name' => 'notsounds',
  248             'description' => T_('does not sound like'),
  249             'sql' => 'NOT SOUNDS LIKE'
  250         );
  251 
  252         $this->basetypes['text'][] = array(
  253             'name' => 'regexp',
  254             'description' => T_('matches regular expression'),
  255             'sql' => 'REGEXP'
  256         );
  257 
  258         $this->basetypes['text'][] = array(
  259             'name' => 'notregexp',
  260             'description' => T_('does not match regular expression'),
  261             'sql' => 'NOT REGEXP'
  262         );
  263 
  264         $this->basetypes['tags'][] = array(
  265             'name' => 'contain',
  266             'description' => T_('contains'),
  267             'sql' => 'LIKE',
  268             'preg_match' => array('/^/', '/$/'),
  269             'preg_replace' => array('%', '%')
  270         );
  271 
  272         $this->basetypes['tags'][] = array(
  273             'name' => 'notcontain',
  274             'description' => T_('does not contain'),
  275             'sql' => 'NOT LIKE',
  276             'preg_match' => array('/^/', '/$/'),
  277             'preg_replace' => array('%', '%')
  278         );
  279 
  280         $this->basetypes['tags'][] = array(
  281             'name' => 'start',
  282             'description' => T_('starts with'),
  283             'sql' => 'LIKE',
  284             'preg_match' => '/$/',
  285             'preg_replace' => '%'
  286         );
  287 
  288         $this->basetypes['tags'][] = array(
  289             'name' => 'end',
  290             'description' => T_('ends with'),
  291             'sql' => 'LIKE',
  292             'preg_match' => '/^/',
  293             'preg_replace' => '%'
  294         );
  295 
  296         $this->basetypes['tags'][] = array(
  297             'name' => 'equal',
  298             'description' => T_('is'),
  299             'sql' => '>'
  300         );
  301 
  302         $this->basetypes['tags'][] = array(
  303             'name' => 'not equal',
  304             'description' => T_('is not'),
  305             'sql' => '='
  306         );
  307 
  308         $this->basetypes['boolean_numeric'][] = array(
  309             'name' => 'equal',
  310             'description' => T_('is'),
  311             'sql' => '<=>'
  312         );
  313 
  314         $this->basetypes['boolean_numeric'][] = array(
  315             'name' => 'ne',
  316             'description' => T_('is not'),
  317             'sql' => '<>'
  318         );
  319 
  320         $this->basetypes['boolean_subsearch'][] = array(
  321             'name' => 'equal',
  322             'description' => T_('is'),
  323             'sql' => ''
  324         );
  325 
  326         $this->basetypes['boolean_subsearch'][] = array(
  327             'name' => 'ne',
  328             'description' => T_('is not'),
  329             'sql' => 'NOT'
  330         );
  331 
  332         $this->basetypes['date'][] = array(
  333             'name' => 'lt',
  334             'description' => T_('before'),
  335             'sql' => '<'
  336         );
  337 
  338         $this->basetypes['date'][] = array(
  339             'name' => 'gt',
  340             'description' => T_('after'),
  341             'sql' => '>'
  342         );
  343 
  344         $this->basetypes['days'][] = array(
  345             'name' => 'lt',
  346             'description' => T_('before (x) days ago'),
  347             'sql' => '<'
  348         );
  349 
  350         $this->basetypes['days'][] = array(
  351             'name' => 'gt',
  352             'description' => T_('after (x) days ago'),
  353             'sql' => '>'
  354         );
  355 
  356         $this->basetypes['recent_played'][] = array(
  357             'name' => 'ply',
  358             'description' => T_('Limit'),
  359             'sql' => '`date`'
  360         );
  361         $this->basetypes['recent_added'][] = array(
  362             'name' => 'add',
  363             'description' => T_('Limit'),
  364             'sql' => '`addition_time`'
  365         );
  366 
  367         $this->basetypes['recent_updated'][] = array(
  368             'name' => 'upd',
  369             'description' => T_('Limit'),
  370             'sql' => '`update_time`'
  371         );
  372 
  373         $this->basetypes['user_numeric'][] = array(
  374             'name' => 'love',
  375             'description' => T_('has loved'),
  376             'sql' => 'userflag'
  377         );
  378 
  379         $this->basetypes['user_numeric'][] = array(
  380             'name' => '5star',
  381             'description' => T_('has rated 5 stars'),
  382             'sql' => '`rating` = 5'
  383         );
  384 
  385         $this->basetypes['user_numeric'][] = array(
  386             'name' => '4star',
  387             'description' => T_('has rated 4 stars'),
  388             'sql' => '`rating` = 4'
  389         );
  390 
  391         $this->basetypes['user_numeric'][] = array(
  392             'name' => '3star',
  393             'description' => T_('has rated 3 stars'),
  394             'sql' => '`rating` = 3'
  395         );
  396 
  397         $this->basetypes['user_numeric'][] = array(
  398             'name' => '2star',
  399             'description' => T_('has rated 2 stars'),
  400             'sql' => '`rating` = 2'
  401         );
  402 
  403         $this->basetypes['user_numeric'][] = array(
  404             'name' => '1star',
  405             'description' => T_('has rated 1 star'),
  406             'sql' => '`rating` = 1'
  407         );
  408 
  409         $this->basetypes['user_numeric'][] = array(
  410             'name' => 'unrated',
  411             'description' => T_('has not rated'),
  412             'sql' => 'unrated'
  413         );
  414         $this->basetypes['multiple'] = array_merge($this->basetypes['text'], $this->basetypes['numeric']);
  415     }
  416 
  417     /**
  418      * type_numeric
  419      *
  420      * Generic integer searches rules
  421      * @param string $name
  422      * @param string $label
  423      * @param string $type
  424      */
  425     private function type_numeric($name, $label, $type = 'numeric')
  426     {
  427         $this->types[] = array(
  428             'name' => $name,
  429             'label' => $label,
  430             'type' => $type,
  431             'widget' => array('input', 'number')
  432         );
  433     }
  434 
  435     /**
  436      * type_date
  437      *
  438      * Generic integer searches rules
  439      * @param string $name
  440      * @param string $label
  441      */
  442     private function type_date($name, $label)
  443     {
  444         $this->types[] = array(
  445             'name' => $name,
  446             'label' => $label,
  447             'type' => 'date',
  448             'widget' => array('input', 'datetime-local')
  449         );
  450     }
  451 
  452     /**
  453      * type_text
  454      *
  455      * Generic text rules
  456      * @param string $name
  457      * @param string $label
  458      */
  459     private function type_text($name, $label)
  460     {
  461         $this->types[] = array(
  462             'name' => $name,
  463             'label' => $label,
  464             'type' => 'text',
  465             'widget' => array('input', 'text')
  466         );
  467     }
  468 
  469     /**
  470      * type_select
  471      *
  472      * Generic rule to select from a list
  473      * @param string $name
  474      * @param string $label
  475      * @param string $type
  476      * @param array $array
  477      */
  478     private function type_select($name, $label, $type, $array)
  479     {
  480         $this->types[] = array(
  481             'name' => $name,
  482             'label' => $label,
  483             'type' => $type,
  484             'widget' => array('select', $array)
  485         );
  486     }
  487 
  488     /**
  489      * type_boolean
  490      *
  491      * True or false generic searches
  492      * @param string $name
  493      * @param string $label
  494      * @param string $type
  495      */
  496     private function type_boolean($name, $label, $type = 'boolean')
  497     {
  498         $this->types[] = array(
  499             'name' => $name,
  500             'label' => $label,
  501             'type' => $type,
  502             'widget' => array('input', 'hidden')
  503         );
  504     }
  505 
  506     /**
  507      * songtypes
  508      *
  509      * this is where all the searchtypes for songs are defined
  510      */
  511     private function song_types()
  512     {
  513         $this->type_text('anywhere', T_('Any searchable text'));
  514         $this->type_text('title', T_('Title'));
  515         $this->type_text('album', T_('Album'));
  516         $this->type_text('artist', T_('Song Artist'));
  517         $this->type_text('album_artist', T_('Album Artist'));
  518         $this->type_text('composer', T_('Composer'));
  519 
  520         $this->type_numeric('year', T_('Year'));
  521 
  522         if (AmpConfig::get('ratings')) {
  523             $this->type_select('myrating', T_('My Rating'), 'numeric', $this->stars);
  524             $this->type_select('rating', T_('Rating (Average)'), 'numeric', $this->stars);
  525             $this->type_select('albumrating', T_('My Rating (Album)'), 'numeric', $this->stars);
  526             $this->type_select('artistrating', T_('My Rating (Artist)'), 'numeric', $this->stars);
  527         }
  528         if (AmpConfig::get('userflags')) {
  529             $this->type_text('favorite', T_('Favorites'));
  530             $this->type_text('favorite_album', T_('Favorites (Album)'));
  531             $this->type_text('favorite_artist', T_('Favorites (Artist)'));
  532         }
  533 
  534         /* HINT: Number of times object has been played */
  535         $this->type_numeric('played_times', T_('# Played'));
  536         /* HINT: Number of times object has been skipped */
  537         $this->type_numeric('skipped_times', T_('# Skipped'));
  538         /* HINT: Number of times object has been played OR skipped */
  539         $this->type_numeric('played_or_skipped_times', T_('# Played or Skipped'));
  540         /* HINT: Percentage of (Times Played / Times skipped) * 100 */
  541         $this->type_numeric('play_skip_ratio', T_('Played/Skipped ratio'));
  542         $this->type_numeric('last_play', T_('My Last Play'), 'days');
  543         $this->type_numeric('last_skip', T_('My Last Skip'), 'days');
  544         $this->type_numeric('last_play_or_skip', T_('My Last Play or Skip'), 'days');
  545         $this->type_boolean('played', T_('Played'));
  546         $this->type_boolean('myplayed', T_('Played by Me'));
  547         $this->type_boolean('myplayedalbum', T_('Played by Me (Album)'));
  548         $this->type_boolean('myplayedartist', T_('Played by Me (Artist)'));
  549         $this->type_numeric('time', T_('Length (in minutes)'));
  550 
  551         $this->type_text('tag', T_('Genre'));
  552         $this->type_text('album_tag', T_('Album Genre'));
  553         $this->type_text('artist_tag', T_('Artist Genre'));
  554 
  555         $users = $this->getUserRepository()->getValidArray();
  556         $this->type_select('other_user', T_('Another User'), 'user_numeric', $users);
  557         $this->type_select('other_user_album', T_('Another User (Album)'), 'user_numeric', $users);
  558         $this->type_select('other_user_artist', T_('Another User (Artist)'), 'user_numeric', $users);
  559 
  560         $this->type_text('label', T_('Label'));
  561         if (AmpConfig::get('licensing')) {
  562             $licenses = array();
  563             foreach ($this->getLicenseRepository()->getAll() as $license_id) {
  564                 $license               = new License($license_id);
  565                 $licenses[$license_id] = $license->name;
  566             }
  567             $this->type_select('license', T_('Music License'), 'boolean_numeric', $licenses);
  568         }
  569 
  570         $playlists = Playlist::get_playlist_array($this->search_user->id);
  571         if (!empty($playlists)) {
  572             $this->type_select('playlist', T_('Playlist'), 'boolean_numeric', $playlists);
  573         }
  574         $playlists = self::get_search_array($this->search_user->id);
  575         if (!empty($playlists)) {
  576             $this->type_select('smartplaylist', T_('Smart Playlist'), 'boolean_subsearch', $playlists);
  577         }
  578 
  579         $this->type_text('playlist_name', T_('Playlist Name'));
  580 
  581         $this->type_text('comment', T_('Comment'));
  582         $this->type_text('lyrics', T_('Lyrics'));
  583         $this->type_text('file', T_('Filename'));
  584         $bitrate_array = array(
  585             '32',
  586             '40',
  587             '48',
  588             '56',
  589             '64',
  590             '80',
  591             '96',
  592             '112',
  593             '128',
  594             '160',
  595             '192',
  596             '224',
  597             '256',
  598             '320',
  599             '640',
  600             '1280'
  601         );
  602         $this->type_select('bitrate', T_('Bitrate'), 'numeric', $bitrate_array);
  603         $this->type_date('added', T_('Added'));
  604         $this->type_date('updated', T_('Updated'));
  605 
  606         $this->type_numeric('recent_played', T_('Recently played'), 'recent_played');
  607         $this->type_numeric('recent_added', T_('Recently added'), 'recent_added');
  608         $this->type_numeric('recent_updated', T_('Recently updated'), 'recent_updated');
  609 
  610         $catalogs = array();
  611         foreach (Catalog::get_catalogs('music', $this->search_user->id) as $catid) {
  612             $catalog = Catalog::create_from_id($catid);
  613             $catalog->format();
  614             $catalogs[$catid] = $catalog->f_name;
  615         }
  616         if (!empty($catalogs)) {
  617             $this->type_select('catalog', T_('Catalog'), 'boolean_numeric', $catalogs);
  618         }
  619 
  620         $this->type_text('mbid', T_('MusicBrainz ID'));
  621         $this->type_text('mbid_album', T_('MusicBrainz ID (Album)'));
  622         $this->type_text('mbid_artist', T_('MusicBrainz ID (Artist)'));
  623         $this->type_boolean('possible_duplicate', T_('Possible Duplicate'), 'is_true');
  624 
  625         if (AmpConfig::get('enable_custom_metadata')) {
  626             $metadataFields          = array();
  627             $metadataFieldRepository = new MetadataField();
  628             foreach ($metadataFieldRepository->findAll() as $metadata) {
  629                 $metadataFields[$metadata->getId()] = $metadata->getName();
  630             }
  631             $this->types[] = array(
  632                 'name' => 'metadata',
  633                 'label' => T_('Metadata'),
  634                 'type' => 'multiple',
  635                 'subtypes' => $metadataFields,
  636                 'widget' => array('subtypes', array('input', 'text'))
  637             );
  638         }
  639     }
  640 
  641     /**
  642      * artisttypes
  643      *
  644      * this is where all the searchtypes for artists are defined
  645      */
  646     private function artist_types()
  647     {
  648         $this->type_text('title', T_('Name'));
  649 
  650         $this->type_numeric('yearformed', T_('Year'));
  651         $this->type_text('placeformed', T_('Place'));
  652 
  653         if (AmpConfig::get('ratings')) {
  654             $this->type_select('myrating', T_('My Rating'), 'numeric', $this->stars);
  655             $this->type_select('rating', T_('Rating (Average)'), 'numeric', $this->stars);
  656         }
  657 
  658         if (AmpConfig::get('userflags')) {
  659             $this->type_text('favorite', T_('Favorites'));
  660         }
  661 
  662         /* HINT: Number of times object has been played */
  663         $this->type_numeric('played_times', T_('# Played'));
  664         $this->type_numeric('last_play', T_('My Last Play'), 'days');
  665         $this->type_numeric('last_skip', T_('My Last Skip'), 'days');
  666         $this->type_numeric('last_play_or_skip', T_('My Last Play or Skip'), 'days');
  667         $this->type_boolean('played', T_('Played'));
  668         $this->type_boolean('myplayed', T_('Played by Me'));
  669         $this->type_numeric('time', T_('Length (in minutes)'));
  670 
  671         $this->type_text('tag', T_('Genre'));
  672 
  673         $users = $this->getUserRepository()->getValidArray();
  674         $this->type_select('other_user', T_('Another User'), 'user_numeric', $users);
  675 
  676         $this->type_numeric('recent_played', T_('Recently played'), 'recent_played');
  677 
  678         $catalogs = array();
  679         foreach (Catalog::get_catalogs('music', $this->search_user->id) as $catid) {
  680             $catalog = Catalog::create_from_id($catid);
  681             $catalog->format();
  682             $catalogs[$catid] = $catalog->f_name;
  683         }
  684         if (!empty($catalogs)) {
  685             $this->type_select('catalog', T_('Catalog'), 'boolean_numeric', $catalogs);
  686         }
  687 
  688         $this->type_text('mbid', T_('MusicBrainz ID'));
  689 
  690         $this->type_boolean('has_image', T_('Local Image'));
  691         $this->type_numeric('image_width', T_('Image Width'));
  692         $this->type_numeric('image_height', T_('Image Height'));
  693         $this->type_boolean('possible_duplicate', T_('Possible Duplicate'), 'is_true');
  694         $this->type_boolean('possible_duplicate_album', T_('Possible Duplicate Albums'), 'is_true');
  695     } // artisttypes
  696 
  697     /**
  698      * albumtypes
  699      *
  700      * this is where all the searchtypes for albums are defined
  701      */
  702     private function album_types()
  703     {
  704         $this->type_text('title', T_('Title'));
  705         $this->type_text('artist', T_('Album Artist'));
  706 
  707         $this->type_numeric('year', T_('Year'));
  708         $this->type_numeric('original_year', T_('Original Year'));
  709         $this->type_text('release_type', T_('Release Type'));
  710         $this->type_text('release_status', T_('Release Status'));
  711 
  712         if (AmpConfig::get('ratings')) {
  713             $this->type_select('myrating', T_('My Rating'), 'numeric', $this->stars);
  714             $this->type_select('rating', T_('Rating (Average)'), 'numeric', $this->stars);
  715             $this->type_select('artistrating', T_('My Rating (Artist)'), 'numeric', $this->stars);
  716         }
  717         if (AmpConfig::get('userflags')) {
  718             $this->type_text('favorite', T_('Favorites'));
  719         }
  720 
  721         /* HINT: Number of times object has been played */
  722         $this->type_numeric('played_times', T_('# Played'));
  723         $this->type_numeric('last_play', T_('My Last Play'), 'days');
  724         $this->type_numeric('last_skip', T_('My Last Skip'), 'days');
  725         $this->type_numeric('last_play_or_skip', T_('My Last Play or Skip'), 'days');
  726         $this->type_boolean('played', T_('Played'));
  727         $this->type_boolean('myplayed', T_('Played by Me'));
  728         $this->type_numeric('time', T_('Length (in minutes)'));
  729 
  730         $this->type_text('tag', T_('Genre'));
  731 
  732         $users = $this->getUserRepository()->getValidArray();
  733         $this->type_select('other_user', T_('Another User'), 'user_numeric', $users);
  734 
  735         $this->type_numeric('recent_played', T_('Recently played'), 'recent_played');
  736 
  737         $catalogs = array();
  738         foreach (Catalog::get_catalogs('music', $this->search_user->id) as $catid) {
  739             $catalog = Catalog::create_from_id($catid);
  740             $catalog->format();
  741             $catalogs[$catid] = $catalog->f_name;
  742         }
  743         if (!empty($catalogs)) {
  744             $this->type_select('catalog', T_('Catalog'), 'boolean_numeric', $catalogs);
  745         }
  746 
  747         $this->type_text('mbid', T_('MusicBrainz ID'));
  748 
  749         $this->type_boolean('has_image', T_('Local Image'));
  750         $this->type_numeric('image_width', T_('Image Width'));
  751         $this->type_numeric('image_height', T_('Image Height'));
  752         $this->type_boolean('possible_duplicate', T_('Possible Duplicate'), 'is_true');
  753     } // albumtypes
  754 
  755     /**
  756      * videotypes
  757      *
  758      * this is where all the searchtypes for videos are defined
  759      */
  760     private function video_types()
  761     {
  762         $this->type_text('file', T_('Filename'));
  763     }
  764 
  765     /**
  766      * playlisttypes
  767      *
  768      * this is where all the searchtypes for playlists are defined
  769      */
  770     private function playlist_types()
  771     {
  772         $this->type_text('title', T_('Name'));
  773     }
  774 
  775     /**
  776      * labeltypes
  777      *
  778      * this is where all the searchtypes for labels are defined
  779      */
  780     private function label_types()
  781     {
  782         $this->type_text('title', T_('Name'));
  783         $this->type_text('category', T_('Category'));
  784     }
  785 
  786     /**
  787      * usertypes
  788      *
  789      * this is where all the searchtypes for users are defined
  790      */
  791     private function user_types()
  792     {
  793         $this->type_text('username', T_('Username'));
  794     }
  795 
  796     /**
  797      * tagtypes
  798      *
  799      * this is where all the searchtypes for Genres are defined
  800      */
  801     private function tag_types()
  802     {
  803         $this->type_text('name', T_('Genre'));
  804     }
  805 
  806     /**
  807      * clean_request
  808      *
  809      * Sanitizes raw search data
  810      * @param array $data
  811      * @return array
  812      */
  813     public static function clean_request($data)
  814     {
  815         $request = array();
  816         foreach ($data as $key => $value) {
  817             $prefix = substr($key, 0, 4);
  818             $value  = trim((string)$value);
  819 
  820             if ($prefix == 'rule' && strlen((string)$value)) {
  821                 $request[$key] = Dba::escape(filter_var($value, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES));
  822             }
  823         }
  824 
  825         // Figure out if they want an AND based search or an OR based search
  826         switch ($data['operator']) {
  827             case 'or':
  828                 $request['operator'] = 'OR';
  829                 break;
  830             default:
  831                 $request['operator'] = 'AND';
  832                 break;
  833         }
  834 
  835         // Verify the type
  836         switch ($data['type']) {
  837             case 'album':
  838             case 'artist':
  839             case 'video':
  840             case 'song':
  841             case 'tag':  // for Genres
  842             case 'playlist':
  843             case 'label':
  844             case 'user':
  845                 $request['type'] = $data['type'];
  846                 break;
  847             default:
  848                 $request['type'] = 'song';
  849                 break;
  850         }
  851 
  852         return $request;
  853     } // end clean_request
  854 
  855     /**
  856      * get_name_byid
  857      *
  858      * Returns the name of the saved search corresponding to the given ID
  859      * @param string $search_id
  860      * @return string
  861      */
  862     public static function get_name_byid($search_id)
  863     {
  864         $sql        = "SELECT `name` FROM `search` WHERE `id` = '$search_id'";
  865         $db_results = Dba::read($sql);
  866         $row        = Dba::fetch_assoc($db_results);
  867 
  868         return $row['name'];
  869     }
  870 
  871     /**
  872      * get_searches
  873      *
  874      * Return the IDs of all saved searches accessible by the current user.
  875      * @param integer $user_id
  876      * @return array
  877      */
  878     public static function get_searches($user_id = null)
  879     {
  880         $user_id = Core::get_global('user')->id;
  881         $key     = 'searches';
  882         if (parent::is_cached($key, $user_id)) {
  883             return parent::get_from_cache($key, $user_id);
  884         }
  885         $is_admin = (Access::check('interface', 100, $user_id) || $user_id == -1);
  886         $sql      = "SELECT `id` FROM `search` ";
  887         $params   = array();
  888 
  889         if (!$is_admin) {
  890             $sql .= "WHERE (`user` = ? OR `type` = 'public') ";
  891             $params[] = $user_id;
  892         }
  893         $sql .= "ORDER BY `name`";
  894 
  895         $db_results = Dba::read($sql, $params);
  896         $results    = array();
  897         while ($row = Dba::fetch_assoc($db_results)) {
  898             $results[$row['id']] = $row['name'];
  899         }
  900 
  901         parent::add_to_cache($key, $user_id, $results);
  902 
  903         return $results;
  904     }
  905 
  906     /**
  907      * get_search_array
  908      * Returns a list of searches accessible by the user with formatted name.
  909      * @param integer $user_id
  910      * @return array
  911      */
  912     public static function get_search_array($user_id = null)
  913     {
  914         if (!$user_id) {
  915             $user_id = Core::get_global('user')->id;
  916         }
  917         $key = 'searcharray';
  918         if (parent::is_cached($key, $user_id)) {
  919             return parent::get_from_cache($key, $user_id);
  920         }
  921         $is_admin = (Access::check('interface', 100, $user_id) || $user_id == -1);
  922         $sql      = "SELECT `id`, IF(`user` = ?, `name`, CONCAT(`name`, ' (', `username`, ')')) AS `name` FROM `search` ";
  923         $params   = array($user_id);
  924 
  925         if (!$is_admin) {
  926             $sql .= "WHERE (`user` = ? OR `type` = 'public') ";
  927             $params[] = $user_id;
  928         }
  929         $sql .= "ORDER BY `name`";
  930         //debug_event(self::class, 'get_searches query: ' . $sql, 5);
  931 
  932         $db_results = Dba::read($sql, $params);
  933         $results    = array();
  934         while ($row = Dba::fetch_assoc($db_results)) {
  935             $results[$row['id']] = $row['name'];
  936         }
  937 
  938         parent::add_to_cache($key, $user_id, $results);
  939 
  940         return $results;
  941     } // get_smartlist_array
  942 
  943     /**
  944      * run
  945      *
  946      * This function actually runs the search and returns an array of the
  947      * results.
  948      * @param array $data
  949      * @param User $user
  950      * @return integer[]
  951      */
  952     public static function run($data, $user = null)
  953     {
  954         $limit  = (int)($data['limit']);
  955         $offset = (int)($data['offset']);
  956         $random = ((int)$data['random'] > 0) ? 1 : 0;
  957         $data   = self::clean_request($data);
  958         $search = new Search(null, $data['type'], $user);
  959         $search->parse_rules($data);
  960 
  961         // Generate BASE SQL
  962         $limit_sql = "";
  963         if ($limit > 0) {
  964             $limit_sql = ' LIMIT ';
  965             if ($offset) {
  966                 $limit_sql .= $offset . ",";
  967             }
  968             $limit_sql .= $limit;
  969         }
  970 
  971         $search_info = $search->to_sql();
  972         $sql         = $search_info['base'] . ' ' . $search_info['table_sql'];
  973         if (!empty($search_info['where_sql'])) {
  974             $sql .= ' WHERE ' . $search_info['where_sql'];
  975         }
  976         if (!empty($search_info['group_sql'])) {
  977             $sql .= ' GROUP BY ' . $search_info['group_sql'];
  978             if (!empty($search_info['having_sql'])) {
  979                 $sql .= ' HAVING ' . $search_info['having_sql'];
  980             }
  981         }
  982         $sql .= ($random > 0) ? " ORDER BY RAND()" : " ORDER BY " . $search->order_by;
  983         $sql .= ' ' . $limit_sql;
  984         $sql = trim((string)$sql);
  985 
  986         //debug_event(self::class, 'SQL get_items: ' . $sql, 5);
  987         $db_results = Dba::read($sql);
  988         $results    = array();
  989         while ($row = Dba::fetch_assoc($db_results)) {
  990             $results[] = (int)$row['id'];
  991         }
  992 
  993         return $results;
  994     }
  995 
  996     /**
  997      * delete
  998      *
  999      * Does what it says on the tin.
 1000      * @return boolean
 1001      */
 1002     public function delete()
 1003     {
 1004         $search_id = Dba::escape($this->id);
 1005         $sql       = "DELETE FROM `search` WHERE `id` = ?";
 1006         Dba::write($sql, array($search_id));
 1007 
 1008         return true;
 1009     }
 1010 
 1011     /**
 1012      * format
 1013      * Gussy up the data
 1014      * @param boolean $details
 1015      */
 1016     public function format($details = true)
 1017     {
 1018         parent::format();
 1019 
 1020         $this->link   = AmpConfig::get('web_path') . '/smartplaylist.php?action=show_playlist&playlist_id=' . $this->id;
 1021         $this->f_link = '<a href="' . $this->link . '">' . scrub_out($this->f_name) . '</a>';
 1022     }
 1023 
 1024     /**
 1025      * get_items
 1026      *
 1027      * Return an array of the items output by our search (part of the
 1028      * playlist interface).
 1029      * @return array
 1030      */
 1031     public function get_items()
 1032     {
 1033         $results = array();
 1034 
 1035         $sqltbl = $this->to_sql();
 1036         $sql    = $sqltbl['base'] . ' ' . $sqltbl['table_sql'];
 1037         if (!empty($sqltbl['where_sql'])) {
 1038             $sql .= ' WHERE ' . $sqltbl['where_sql'];
 1039         }
 1040         if (!empty($sqltbl['group_sql'])) {
 1041             $sql .= ' GROUP BY ' . $sqltbl['group_sql'];
 1042         }
 1043         if (!empty($sqltbl['having_sql'])) {
 1044             $sql .= ' HAVING ' . $sqltbl['having_sql'];
 1045         }
 1046 
 1047         $sql .= ($this->random > 0) ? " ORDER BY RAND()" : " ORDER BY " . $this->order_by;
 1048         if ($this->limit > 0) {
 1049             $sql .= " LIMIT " . (string)($this->limit);
 1050         }
 1051         //debug_event(self::class, 'SQL get_items: ' . $sql, 5);
 1052 
 1053         $db_results = Dba::read($sql);
 1054         while ($row = Dba::fetch_assoc($db_results)) {
 1055             $results[] = array(
 1056                 'object_id' => $row['id'],
 1057                 'object_type' => $this->searchtype
 1058             );
 1059         }
 1060         $this->date = time();
 1061         $this->set_last(count($results), 'last_count');
 1062         $this->set_last(self::get_total_duration($results), 'last_duration');
 1063 
 1064         return $results;
 1065     }
 1066 
 1067     /**
 1068      * set_last
 1069      *
 1070      * @param integer $count
 1071      * @param string $column
 1072      */
 1073     private function set_last($count, $column)
 1074     {
 1075         if (in_array($column, array('last_count', 'last_duration'))) {
 1076             $search_id = Dba::escape($this->id);
 1077             $sql       = "UPDATE `search` SET `" . Dba::escape($column) . "` = " . $count . " WHERE `id` = ?";
 1078             Dba::write($sql, array($search_id));
 1079         }
 1080     }
 1081 
 1082     /**
 1083      * get_random_items
 1084      *
 1085      * Returns a randomly sorted array (with an optional limit) of the items
 1086      * output by our search (part of the playlist interface)
 1087      * @param integer $limit
 1088      * @return array
 1089      */
 1090     public function get_random_items($limit = null)
 1091     {
 1092         $results = array();
 1093 
 1094         $sqltbl = $this->to_sql();
 1095         $sql    = $sqltbl['base'] . ' ' . $sqltbl['table_sql'];
 1096         if (!empty($sqltbl['where_sql'])) {
 1097             $sql .= ' WHERE ' . $sqltbl['where_sql'];
 1098         }
 1099         $rating_filter = AmpConfig::get_rating_filter();
 1100         if ($rating_filter > 0 && $rating_filter <= 5 && Core::get_global('user')) {
 1101             $user_id = Core::get_global('user')->id;
 1102             if (empty($sqltbl['where_sql'])) {
 1103                 $sql .= " WHERE ";
 1104             } else {
 1105                 $sql .= " AND ";
 1106             }
 1107             $sql .= "`" . $this->searchtype . "`.`id` NOT IN (SELECT `object_id` FROM `rating` WHERE `rating`.`object_type` = '" . $this->searchtype . "' AND `rating`.`rating` <=$rating_filter AND `rating`.`user` = $user_id)";
 1108         }
 1109         if (!empty($sqltbl['group_sql'])) {
 1110             $sql .= ' GROUP BY ' . $sqltbl['group_sql'];
 1111         }
 1112         if (!empty($sqltbl['having_sql'])) {
 1113             $sql .= ' HAVING ' . $sqltbl['having_sql'];
 1114         }
 1115 
 1116         $sql .= " ORDER BY RAND()";
 1117         $sql .= ($limit)
 1118             ? " LIMIT " . (string) ($limit)
 1119             : "";
 1120         //debug_event(self::class, 'SQL get_random_items: ' . $sql, 5);
 1121 
 1122         $db_results = Dba::read($sql);
 1123 
 1124         while ($row = Dba::fetch_assoc($db_results)) {
 1125             $results[] = array(
 1126                 'object_id' => $row['id'],
 1127                 'object_type' => $this->searchtype
 1128             );
 1129         }
 1130 
 1131         return $results;
 1132     }
 1133 
 1134     /**
 1135      * get_total_duration
 1136      * Get the total duration of all songs.
 1137      * @param array $songs
 1138      * @return integer
 1139      */
 1140     public static function get_total_duration($songs)
 1141     {
 1142         $song_ids = array();
 1143         foreach ($songs as $objects) {
 1144             $song_ids[] = (string)$objects['object_id'];
 1145         }
 1146         $idlist = '(' . implode(',', $song_ids) . ')';
 1147         if ($idlist == '()') {
 1148             return 0;
 1149         }
 1150         $sql = "SELECT SUM(`time`) FROM `song` WHERE `id` IN $idlist";
 1151 
 1152         $db_results = Dba::read($sql);
 1153         $results    = Dba::fetch_row($db_results);
 1154 
 1155         return (int)$results['0'];
 1156     } // get_total_duration
 1157 
 1158     /**
 1159      * name_to_basetype
 1160      *
 1161      * Iterates over our array of types to find out the basetype for
 1162      * the passed string.
 1163      * @param string $name
 1164      * @return string|false
 1165      */
 1166     public function name_to_basetype($name)
 1167     {
 1168         foreach ($this->types as $type) {
 1169             if ($type['name'] == $name) {
 1170                 return $type['type'];
 1171             }
 1172         }
 1173 
 1174         return false;
 1175     }
 1176 
 1177     /**
 1178      * parse_rules
 1179      *
 1180      * Takes an array of sanitized search data from the form and generates our real array from it.
 1181      * @param array $data
 1182      */
 1183     public function parse_rules($data)
 1184     {
 1185         // check that a limit or random flag have been sent
 1186         $this->random = (isset($data['random'])) ? (int) $data['random'] : $this->random;
 1187         $this->limit  = (isset($data['limit'])) ? (int) $data['limit'] : $this->limit;
 1188         // parse the remaining rule* keys
 1189         $this->rules  = array();
 1190         foreach ($data as $rule => $value) {
 1191             if ((($this->searchtype == 'artist' && $value == 'artist') || $value == 'name') && preg_match('/^rule_[0123456789]*$/', $rule)) {
 1192                 $value = 'title';
 1193             }
 1194             if (preg_match('/^rule_(\d+)$/', $rule, $ruleID)) {
 1195                 $ruleID     = (string)$ruleID[1];
 1196                 $input_rule = (string)$data['rule_' . $ruleID . '_input'];
 1197                 $operator   = $this->basetypes[$this->name_to_basetype($value)][$data['rule_' . $ruleID . '_operator']]['name'];
 1198                 //keep vertical bar in regular expression
 1199                 if (in_array($operator, ['regexp', 'notregexp'])) {
 1200                     $input_rule = str_replace("|", "\0", $input_rule);
 1201                 }
 1202                 foreach (explode('|', $input_rule) as $input) {
 1203                     $this->rules[] = array(
 1204                         $value,
 1205                         $operator,
 1206                         in_array($operator, ['regexp', 'notregexp']) ? str_replace("\0", "|", $input) : $input,
 1207                         $data['rule_' . $ruleID . '_subtype']
 1208                     );
 1209                 }
 1210             }
 1211         }
 1212         $this->logic_operator = $data['operator'];
 1213     }
 1214 
 1215     /**
 1216      * save
 1217      *
 1218      * Save this search to the database for use as a smart playlist
 1219      * @return string
 1220      */
 1221     public function save()
 1222     {
 1223         $user = Core::get_global('user');
 1224         // Make sure we have a unique name
 1225         if (!$this->name) {
 1226             $this->name = $user->username . ' - ' . get_datetime(time());
 1227         }
 1228         $sql        = "SELECT `id` FROM `search`  WHERE `name` = ? AND `user` = ? AND `type` = ?;";
 1229         $db_results = Dba::read($sql, array($this->name, $user->id, $this->type));
 1230         if (Dba::num_rows($db_results)) {
 1231             $this->name .= uniqid('', true);
 1232         }
 1233 
 1234         $sql = "INSERT INTO `search` (`name`, `type`, `user`, `username`, `rules`, `logic_operator`, `random`, `limit`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
 1235         Dba::write($sql, array(
 1236             $this->name,
 1237             $this->type,
 1238             $user->id,
 1239             $user->username,
 1240             json_encode($this->rules),
 1241             $this->logic_operator,
 1242             ($this->random > 0) ? 1 : 0,
 1243             $this->limit
 1244         ));
 1245         $insert_id = Dba::insert_id();
 1246         $this->id  = (int)$insert_id;
 1247 
 1248         return $insert_id;
 1249     }
 1250 
 1251     /**
 1252      * to_js
 1253      *
 1254      * Outputs the javascript necessary to re-show the current set of rules.
 1255      * @return string
 1256      */
 1257     public function to_js()
 1258     {
 1259         $javascript = "";
 1260         foreach ($this->rules as $rule) {
 1261             $javascript .= '<script>' . 'SearchRow.add("' . $rule[0] . '","' . $rule[1] . '","' . $rule[2] . '", "' . $rule[3] . '"); </script>';
 1262         }
 1263 
 1264         return $javascript;
 1265     }
 1266 
 1267     /**
 1268      * to_sql
 1269      *
 1270      * Call the appropriate real function.
 1271      * @return array
 1272      */
 1273     public function to_sql()
 1274     {
 1275         return call_user_func(array($this, $this->searchtype . "_to_sql"));
 1276     }
 1277 
 1278     /**
 1279      * update
 1280      *
 1281      * This function updates the saved version with the current settings.
 1282      * @param array|null $data
 1283      * @return integer
 1284      */
 1285     public function update(array $data = null)
 1286     {
 1287         if ($data && is_array($data)) {
 1288             $this->name   = $data['name'];
 1289             $this->type   = $data['pl_type'];
 1290             $this->user   = $data['pl_user'];
 1291             $this->random = ((int)$data['random'] > 0 || $this->random) ? 1 : 0;
 1292             $this->limit  = $data['limit'];
 1293         }
 1294         $this->username = User::get_username($this->user);
 1295 
 1296         if (!$this->id) {
 1297             return 0;
 1298         }
 1299 
 1300         $sql = "UPDATE `search` SET `name` = ?, `type` = ?, `username` = ?, `rules` = ?, `logic_operator` = ?, `random` = ?, `limit` = ? WHERE `id` = ?";
 1301         Dba::write($sql, array(
 1302             $this->name,
 1303             $this->type,
 1304             $this->username,
 1305             json_encode($this->rules),
 1306             $this->logic_operator,
 1307             $this->random,
 1308             $this->limit,
 1309             $this->id
 1310         ));
 1311         // reformat after an update
 1312         $this->format();
 1313 
 1314         return $this->id;
 1315     }
 1316 
 1317     /**
 1318      * _mangle_data
 1319      *
 1320      * Private convenience function.  Mangles the input according to a set
 1321      * of predefined rules so that we don't have to include this logic in
 1322      * foo_to_sql.
 1323      * @param array|string $data
 1324      * @param string|false $type
 1325      * @param array $operator
 1326      * @return array|boolean|integer|string|string[]|null
 1327      */
 1328     private function _mangle_data($data, $type, $operator)
 1329     {
 1330         if ($operator['preg_match']) {
 1331             $data = preg_replace($operator['preg_match'], $operator['preg_replace'], $data);
 1332         }
 1333 
 1334         if ($type == 'numeric' || $type == 'days') {
 1335             return (int)($data);
 1336         }
 1337 
 1338         if ($type == 'boolean') {
 1339             return make_bool($data);
 1340         }
 1341 
 1342         return $data;
 1343     }
 1344 
 1345     /**
 1346      * album_to_sql
 1347      *
 1348      * Handles the generation of the SQL for album searches.
 1349      * @return array
 1350      */
 1351     private function album_to_sql()
 1352     {
 1353         $sql_logic_operator = $this->logic_operator;
 1354         $user_id            = $this->search_user->id;
 1355         $catalog_disable    = AmpConfig::get('catalog_disable');
 1356         $catalog_filter     = AmpConfig::get('catalog_filter');
 1357 
 1358         $where       = array();
 1359         $table       = array();
 1360         $join        = array();
 1361         $group       = array();
 1362         $having      = array();
 1363         $join['tag'] = array();
 1364         $groupdisks  = AmpConfig::get('album_group');
 1365 
 1366         foreach ($this->rules as $rule) {
 1367             $type     = $this->name_to_basetype($rule[0]);
 1368             $operator = array();
 1369             if (!$type) {
 1370                 return array();
 1371             }
 1372             foreach ($this->basetypes[$type] as $op) {
 1373                 if ($op['name'] == $rule[1]) {
 1374                     $operator = $op;
 1375                     break;
 1376                 }
 1377             }
 1378             $raw_input          = $this->_mangle_data($rule[2], $type, $operator);
 1379             $input              = filter_var($raw_input, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
 1380             $sql_match_operator = $operator['sql'];
 1381             if ($groupdisks) {
 1382                 /* 'album_group' DEFAULT:
 1383                  * `album`.`prefix`, `album`.`name`, `album`.`album_artist`, `album`.`release_type`, `album`.`release_status`, `album`.`mbid`, `album`.`year`, `album`.`original_year`
 1384                  */
 1385                 $group[] = "`album`.`prefix`";
 1386                 $group[] = "`album`.`name`";
 1387                 $group[] = "`album`.`album_artist`";
 1388                 $group[] = "`album`.`release_type`";
 1389                 $group[] = "`album`.`release_status`";
 1390                 $group[] = "`album`.`mbid`";
 1391                 $group[] = "`album`.`year`";
 1392                 $group[] = "`album`.`original_year`";
 1393             } else {
 1394                 $group[] = "`album`.`id`";
 1395                 $group[] = "`album`.`disk`";
 1396             }
 1397 
 1398             switch ($rule[0]) {
 1399                 case 'title':
 1400                     $where[] = "(`album`.`name` $sql_match_operator '$input' OR LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)) $sql_match_operator '$input')";
 1401                     break;
 1402                 case 'year':
 1403                     $where[] = "`album`.`" . $rule[0] . "` $sql_match_operator '$input'";
 1404                     break;
 1405                 case 'original_year':
 1406                     $where[] = "(`album`.`original_year` $sql_match_operator '$input' OR " .
 1407                         "(`album`.`original_year` IS NULL AND `album`.`year` $sql_match_operator '$input'))";
 1408                     break;
 1409                 case 'time':
 1410                     $input   = $input * 60;
 1411                     $where[] = "`album`.`time` $sql_match_operator '$input'";
 1412                     break;
 1413                 case 'rating':
 1414                     // average ratings only
 1415                     $where[]          = "`average_rating`.`avg` $sql_match_operator '$input'";
 1416                     $table['average'] = "LEFT JOIN (SELECT `object_id`, ROUND(AVG(IFNULL(`rating`.`rating`,0))) AS `avg` FROM `rating` WHERE `rating`.`object_type`='album' GROUP BY `object_id`) AS `average_rating` on `average_rating`.`object_id` = `album`.`id` ";
 1417                     break;
 1418                 case 'favorite':
 1419                     $where[] = "(`album`.`name` $sql_match_operator '$input' OR LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)) $sql_match_operator '$input') AND `favorite_album_$user_id`.`user` = $user_id AND `favorite_album_$user_id`.`object_type` = 'album'";
 1420                     // flag once per user
 1421                     $table['favorite'] .= (!strpos((string) $table['favorite'], "favorite_album_$user_id"))
 1422                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user` FROM `user_flag` WHERE `user` = $user_id) AS `favorite_album_$user_id` ON `album`.`id`=`favorite_album_$user_id`.`object_id` AND `favorite_album_$user_id`.`object_type` = 'album'"
 1423                         : "";
 1424                     break;
 1425                 case 'myrating':
 1426                 case 'artistrating':
 1427                     // combine these as they all do the same thing just different tables
 1428                     $looking = str_replace('rating', '', $rule[0]);
 1429                     $column  = ($looking == 'my') ? 'id' : 'album_artist';
 1430                     $my_type = ($looking == 'my') ? 'album' : $looking;
 1431                     if ($input == 0 && $sql_match_operator == '>=') {
 1432                         break;
 1433                     }
 1434 
 1435                     if ($input == 0 && $sql_match_operator == '<') {
 1436                         $input              = -1;
 1437                         $sql_match_operator = '<=>';
 1438                     }
 1439                     if ($input == 0 && $sql_match_operator == '<>') {
 1440                         $input              = 1;
 1441                         $sql_match_operator = '>=';
 1442                     }
 1443                     if (($input == 0 && $sql_match_operator != '>') || ($input == 1 && $sql_match_operator == '<')) {
 1444                         $where[] = "`rating_" . $my_type . "_" . $user_id . "`.`rating` IS NULL";
 1445                     } elseif ($sql_match_operator == '<>' || $sql_match_operator == '<' || $sql_match_operator == '<=' || $sql_match_operator == '!=') {
 1446                         $where[] = "(`rating_" . $my_type . "_" . $user_id . "`.`rating` $sql_match_operator $input OR `rating_" . $my_type . "_" . $user_id . "`.`rating` IS NULL)";
 1447                     } else {
 1448                         $where[] = "`rating_" . $my_type . "_" . $user_id . "`.`rating` $sql_match_operator $input";
 1449                     }
 1450                     // rating once per user
 1451                     $table['rating'] .= (!strpos((string) $table['rating'], "rating_" . $my_type . "_" . $user_id))
 1452                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `rating` FROM `rating` WHERE `user` = $user_id AND `object_type`='$my_type') AS `rating_" . $my_type . "_" . $user_id . "` ON `rating_" . $my_type . "_" . $user_id . "`.`object_id`=`album`.`$column`"
 1453                         : "";
 1454                     break;
 1455                 case 'myplayed':
 1456                     $column       = 'id';
 1457                     $my_type      = 'album';
 1458                     $operator_sql = ((int)$sql_match_operator == 0) ? 'IS NULL' : 'IS NOT NULL';
 1459                     // played once per user
 1460                     $table['myplayed'] .= (!strpos((string) $table['myplayed'], "myplayed_" . $my_type . "_" . $user_id))
 1461                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user` FROM `object_count` WHERE `object_count`.`object_type` = '$my_type' AND `object_count`.`count_type` = 'stream' AND `object_count`.`user`=$user_id GROUP BY `object_id`, `object_type`, `user`) AS `myplayed_" . $my_type . "_" . $user_id . "` ON `album`.`$column`=`myplayed_" . $my_type . "_" . $user_id . "`.`object_id` AND `myplayed_" . $my_type . "_" . $user_id . "`.`object_type` = '$my_type'"
 1462                         : "";
 1463                     $where[] = "`myplayed_" . $my_type . "_" . $user_id . "`.`object_id` $operator_sql";
 1464                     break;
 1465                 case 'last_play':
 1466                     $my_type = 'album';
 1467                     $table['last_play'] .= (!strpos((string) $table['last_play'], "last_play_" . $my_type . "_" . $user_id))
 1468                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user`, MAX(`date`) AS `date` FROM `object_count` WHERE `object_count`.`object_type` = '$my_type' AND `object_count`.`count_type` = 'stream' AND `object_count`.`user`=$user_id GROUP BY `object_id`, `object_type`, `user`) AS `last_play_" . $my_type . "_" . $user_id . "` ON `album`.`id`=`last_play_" . $my_type . "_" . $user_id . "`.`object_id` AND `last_play_" . $my_type . "_" . $user_id . "`.`object_type` = '$my_type'"
 1469                         : "";
 1470                     $where[] = "`last_play_" . $my_type . "_" . $user_id . "`.`date` $sql_match_operator (UNIX_TIMESTAMP() - ($input * 86400))";
 1471                     break;
 1472                 case 'last_skip':
 1473                     $my_type = 'album';
 1474                     $table['last_skip'] .= (!strpos((string) $table['last_skip'], "last_skip_" . $my_type . "_" . $user_id))
 1475                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user`, MAX(`date`) AS `date` FROM `object_count` WHERE `object_count`.`object_type` = 'song' AND `object_count`.`count_type` = 'skip' AND `object_count`.`user`=$user_id GROUP BY `object_id`, `object_type`, `user`) AS `last_skip_" . $my_type . "_" . $user_id . "` ON `song`.`id`=`last_skip_" . $my_type . "_" . $user_id . "`.`object_id` AND `last_skip_" . $my_type . "_" . $user_id . "`.`object_type` = 'song'"
 1476                         : "";
 1477                     $where[]      = "`last_skip_" . $my_type . "_" . $user_id . "`.`date` $sql_match_operator (UNIX_TIMESTAMP() - ($input * 86400))";
 1478                     $join['song'] = true;
 1479                     break;
 1480                 case 'last_play_or_skip':
 1481                     $my_type = 'album';
 1482                     $table['last_play_or_skip'] .= (!strpos((string) $table['last_play_or_skip'], "last_play_or_skip_" . $my_type . "_" . $user_id))
 1483                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user`, MAX(`date`) AS `date` FROM `object_count` WHERE `object_count`.`object_type` = 'song' AND `object_count`.`count_type` IN ('stream', 'skip') AND `object_count`.`user`=$user_id GROUP BY `object_id`, `object_type`, `user`) AS `last_play_or_skip_" . $my_type . "_" . $user_id . "` ON `song`.`id`=`last_play_or_skip_" . $my_type . "_" . $user_id . "`.`object_id` AND `last_play_or_skip_" . $my_type . "_" . $user_id . "`.`object_type` = 'song'"
 1484                         : "";
 1485                     $where[]      = "`last_play_or_skip_" . $my_type . "_" . $user_id . "`.`date` $sql_match_operator (UNIX_TIMESTAMP() - ($input * 86400))";
 1486                     $join['song'] = true;
 1487                     break;
 1488                 case 'played_times':
 1489                     $where[] = "`album`.`id` IN (SELECT `object_count`.`object_id` FROM `object_count` WHERE `object_count`.`object_type` = 'album' AND `object_count`.`count_type` = 'stream' GROUP BY `object_count`.`object_id` HAVING COUNT(*) $sql_match_operator '$input')";
 1490                     break;
 1491                 case 'release_type':
 1492                     $where[] = "`album`.`release_type` $sql_match_operator '$input'";
 1493                     break;
 1494                 case 'release_status':
 1495                     $where[] = "`album`.`release_status` $sql_match_operator '$input'";
 1496                     break;
 1497                 case 'other_user':
 1498                     $other_userid = $input;
 1499                     if ($sql_match_operator == 'userflag') {
 1500                         $where[] = "`favorite_album_$other_userid`.`user` = $other_userid AND `favorite_album_$other_userid`.`object_type` = 'album'";
 1501                         // flag once per user
 1502                         $table['favorite'] .= (!strpos((string) $table['favorite'], "favorite_album_$other_userid"))
 1503                             ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user` FROM `user_flag` WHERE `user` = $other_userid) AS `favorite_album_$other_userid` ON `song`.`album`=`favorite_album_$other_userid`.`object_id` AND `favorite_album_$other_userid`.`object_type` = 'album'"
 1504                             : "";
 1505                     } else {
 1506                         $column  = 'id';
 1507                         $my_type = 'album';
 1508                         $where[] = "`rating_album_" . $other_userid . '`.' . $sql_match_operator . " AND `rating_album_$other_userid`.`user` = $other_userid AND `rating_album_$other_userid`.`object_type` = 'album'";
 1509                         // rating once per user
 1510                         $table['rating'] .= (!strpos((string) $table['rating'], "rating_" . $my_type . "_" . $user_id))
 1511                             ? "LEFT JOIN `rating` AS `rating_" . $my_type . "_" . $user_id . "` ON `rating_" . $my_type . "_" . $user_id . "`.`object_type`='$my_type' AND `rating_" . $my_type . "_" . $user_id . "`.`object_id`=`$my_type`.`$column` AND `rating_" . $my_type . "_" . $user_id . "`.`user` = $user_id "
 1512                             : "";
 1513                     }
 1514                     break;
 1515                 case 'recent_played':
 1516                     $key                     = md5($input . $sql_match_operator);
 1517                     $where[]                 = "`played_$key`.`object_id` IS NOT NULL";
 1518                     $table['played_' . $key] = "LEFT JOIN (SELECT `object_id` FROM `object_count` WHERE `object_type` = 'album' ORDER BY $sql_match_operator DESC LIMIT $input) as `played_$key` ON `album`.`id` = `played_$key`.`object_id`";
 1519                     break;
 1520                 case 'catalog':
 1521                     $where[] = "`album`.`catalog` $sql_match_operator '$input'";
 1522                     break;
 1523                 case 'tag':
 1524                     $where[] = "`album`.`id` IN (SELECT `tag_map`.`object_id` FROM `tag_map` WHERE `tag_map`.`object_type`='album' AND `tag_map`.`tag_id` IN (SELECT `tag`.`id` FROM `tag` WHERE `tag`.`is_hidden` = 0 AND `tag`.`name` $sql_match_operator '$input'))";
 1525                     break;
 1526                 case 'has_image':
 1527                     $where[]            = ($sql_match_operator == '1') ? "`has_image`.`object_id` IS NOT NULL" : "`has_image`.`object_id` IS NULL";
 1528                     $table['has_image'] = "LEFT JOIN (SELECT `object_id` FROM `image` WHERE `object_type` = 'album') as `has_image` ON `album`.`id` = `has_image`.`object_id`";
 1529                     break;
 1530                 case 'image_height':
 1531                 case 'image_width':
 1532                     $looking       = strpos($rule[0], "image_") ? str_replace('image_', '', $rule[0]) : str_replace('image ', '', $rule[0]);
 1533                     $where[]       = "`image`.`$looking` $sql_match_operator '$input'";
 1534                     $join['image'] = true;
 1535                     break;
 1536                 case 'artist':
 1537                     $where[]         = "(`artist`.`name` $sql_match_operator '$input' OR LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`)) $sql_match_operator '$input')";
 1538                     $table['artist'] = "LEFT JOIN `artist` ON `album`.`album_artist`=`artist`.`id`";
 1539                     break;
 1540                 case 'mbid':
 1541                     $where[] = "`album`.`mbid` $sql_match_operator '$input'";
 1542                     break;
 1543                 case 'possible_duplicate':
 1544                     $where[]               = "(`dupe_search1`.`dupe_id1` IS NOT NULL OR `dupe_search2`.`dupe_id2` IS NOT NULL)";
 1545                     $table['dupe_search1'] = "LEFT JOIN (SELECT MIN(`id`) AS `dupe_id1`, LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)) AS `fullname`, COUNT(LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`))) AS `Counting` FROM `album` GROUP BY `album_artist`, LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)), `disk`, `year`, `release_type`, `release_status` HAVING `Counting` > 1) AS `dupe_search1` ON `album`.`id` = `dupe_search1`.`dupe_id1`";
 1546                     $table['dupe_search2'] = "LEFT JOIN (SELECT MAX(`id`) AS `dupe_id2`, LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)) AS `fullname`, COUNT(LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`))) AS `Counting` FROM `album` GROUP BY `album_artist`, LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)), `disk`, `year`, `release_type`, `release_status` HAVING `Counting` > 1) AS `dupe_search2` ON `album`.`id` = `dupe_search2`.`dupe_id2`";
 1547                     break;
 1548                 default:
 1549                     break;
 1550             } // switch on ruletype album
 1551         } // foreach rule
 1552 
 1553         $join['song']        = $join['song'] || $catalog_disable || $catalog_filter;
 1554         $join['catalog']     = $catalog_disable || $catalog_filter;
 1555         $join['catalog_map'] = $catalog_filter;
 1556 
 1557         $where_sql = implode(" $sql_logic_operator ", $where);
 1558 
 1559         if ($join['song']) {
 1560             $table['0_song'] = "LEFT JOIN `song` ON `song`.`album`=`album`.`id`";
 1561         }
 1562         if ($join['catalog']) {
 1563             $table['1_catalog'] = "LEFT JOIN `catalog` AS `catalog_se` ON `catalog_se`.`id`=`song`.`catalog`";
 1564             if (!empty($where_sql)) {
 1565                 $where_sql .= " AND `catalog_se`.`enabled` = '1' AND `song`.`enabled` = 1";
 1566             } else {
 1567                 $where_sql .= " `catalog_se`.`enabled` = '1' AND `song`.`enabled` = 1";
 1568             }
 1569         }
 1570         if ($join['catalog_map']) {
 1571             $table['2_catalog_map'] = "LEFT JOIN `catalog_map` AS `catalog_map_album` ON `catalog_map_album`.`object_id`=`album`.`id` AND `catalog_map_album`.`object_type` = 'album' AND `catalog_map_album`.`catalog_id` = `catalog_se`.`id`";
 1572             if (!empty($where_sql)) {
 1573                 $where_sql .= " AND `catalog_se`.`filter_user` IN (0, $user_id)";
 1574             } else {
 1575                 $where_sql .= " `catalog_se`.`filter_user` IN (0, $user_id)";
 1576             }
 1577         }
 1578         if ($join['count']) {
 1579             $table['object_count'] = "LEFT JOIN (SELECT `object_count`.`object_id`, MAX(`object_count`.`date`) AS `date` FROM `object_count` WHERE `object_count`.`object_type` = 'album' AND `object_count`.`user`='" . $user_id . "' AND `object_count`.`count_type` = 'stream' GROUP BY `object_count`.`object_id`) AS `object_count` ON `object_count`.`object_id`=`album`.`id`";
 1580         }
 1581         if ($join['image']) {
 1582             $table['0_song'] = "LEFT JOIN `song` ON `song`.`album`=`album`.`id` LEFT JOIN `image` ON `image`.`object_id`=`album`.`id`";
 1583             $where_sql .= " AND `image`.`object_type`='album'";
 1584             $where_sql .= " AND `image`.`size`='original'";
 1585         }
 1586         ksort($table);
 1587         $table_sql  = implode(' ', $table);
 1588         $group_sql  = implode(',', $group);
 1589         $having_sql = implode(" $sql_logic_operator ", $having);
 1590 
 1591         return array(
 1592             'base' => ($groupdisks) ? 'SELECT MIN(`album`.`id`) AS `id` FROM `album`' : 'SELECT MIN(`album`.`id`) AS `id`, MAX(`album`.`disk`) AS `disk` FROM `album`',
 1593             'join' => $join,
 1594             'where' => $where,
 1595             'where_sql' => $where_sql,
 1596             'table' => $table,
 1597             'table_sql' => $table_sql,
 1598             'group_sql' => $group_sql,
 1599             'having_sql' => $having_sql
 1600         );
 1601     }
 1602 
 1603     /**
 1604      * artist_to_sql
 1605      *
 1606      * Handles the generation of the SQL for artist searches.
 1607      * @return array
 1608      */
 1609     private function artist_to_sql()
 1610     {
 1611         $sql_logic_operator = $this->logic_operator;
 1612         $user_id            = $this->search_user->id;
 1613         $catalog_disable    = AmpConfig::get('catalog_disable');
 1614         $catalog_filter     = AmpConfig::get('catalog_filter');
 1615 
 1616         $where       = array();
 1617         $table       = array();
 1618         $join        = array();
 1619         $group       = array();
 1620         $having      = array();
 1621 
 1622         foreach ($this->rules as $rule) {
 1623             $type     = $this->name_to_basetype($rule[0]);
 1624             $operator = array();
 1625             if (!$type) {
 1626                 return array();
 1627             }
 1628             foreach ($this->basetypes[$type] as $op) {
 1629                 if ($op['name'] == $rule[1]) {
 1630                     $operator = $op;
 1631                     break;
 1632                 }
 1633             }
 1634             $raw_input          = $this->_mangle_data($rule[2], $type, $operator);
 1635             $input              = filter_var($raw_input, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
 1636             $sql_match_operator = $operator['sql'];
 1637 
 1638             switch ($rule[0]) {
 1639                 case 'title':
 1640                 case 'name':
 1641                     $where[] = "(`artist`.`name` $sql_match_operator '$input' OR LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`)) $sql_match_operator '$input')";
 1642                     break;
 1643                 case 'yearformed':
 1644                     $where[] = "`artist`.`yearformed` $sql_match_operator '$input'";
 1645                     break;
 1646                 case 'placeformed':
 1647                     $where[] = "`artist`.`placeformed` $sql_match_operator '$input'";
 1648                     break;
 1649                 case 'time':
 1650                     $input   = $input * 60;
 1651                     $where[] = "`artist`.`time` $sql_match_operator '$input'";
 1652                     break;
 1653                 case 'tag':
 1654                     $where[] = "`artist`.`id` IN (SELECT `tag_map`.`object_id` FROM `tag_map` WHERE `tag_map`.`object_type`='artist' AND `tag_map`.`tag_id` IN (SELECT `tag`.`id` FROM `tag` WHERE `tag`.`is_hidden` = 0 AND `tag`.`name` $sql_match_operator '$input'))";
 1655                     break;
 1656                 case 'rating':
 1657                     // average ratings only
 1658                     $where[]          = "`average_rating`.`avg` $sql_match_operator '$input'";
 1659                     $table['average'] = "LEFT JOIN (SELECT `object_id`, ROUND(AVG(IFNULL(`rating`.`rating`,0))) AS `avg` FROM `rating` WHERE `rating`.`object_type`='artist' GROUP BY `object_id`) AS `average_rating` on `average_rating`.`object_id` = `artist`.`id` ";
 1660                     break;
 1661                 case 'favorite':
 1662                     $where[] = "(`artist`.`name` $sql_match_operator '$input' OR LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`)) $sql_match_operator '$input') AND `favorite_artist_$user_id`.`user` = $user_id AND `favorite_artist_$user_id`.`object_type` = 'artist'";
 1663                     // flag once per user
 1664                     $table['favorite'] .= (!strpos((string) $table['favorite'], "favorite_artist_$user_id"))
 1665                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user` FROM `user_flag` WHERE `user` = $user_id) AS `favorite_artist_$user_id` ON `artist`.`id`=`favorite_artist_$user_id`.`object_id` AND `favorite_artist_$user_id`.`object_type` = 'artist'"
 1666                         : "";
 1667                     break;
 1668                 case 'has_image':
 1669                     $where[]            = ($sql_match_operator == '1') ? "`has_image`.`object_id` IS NOT NULL" : "`has_image`.`object_id` IS NULL";
 1670                     $table['has_image'] = "LEFT JOIN (SELECT `object_id` FROM `image` WHERE `object_type` = 'artist') as `has_image` ON `artist`.`id` = `has_image`.`object_id`";
 1671                     break;
 1672                 case 'image_height':
 1673                 case 'image_width':
 1674                     $looking       = strpos($rule[0], "image_") ? str_replace('image_', '', $rule[0]) : str_replace('image ', '', $rule[0]);
 1675                     $where[]       = "`image`.`$looking` $sql_match_operator '$input'";
 1676                     $join['image'] = true;
 1677                     break;
 1678                 case 'myrating':
 1679                     // combine these as they all do the same thing just different tables
 1680                     $column  = 'id';
 1681                     $my_type = 'artist';
 1682                     if ($input == 0 && $sql_match_operator == '>=') {
 1683                         break;
 1684                     }
 1685                     if ($input == 0 && $sql_match_operator == '<') {
 1686                         $input              = -1;
 1687                         $sql_match_operator = '=';
 1688                     }
 1689                     if ($input == 0 && $sql_match_operator == '<>') {
 1690                         $input              = 1;
 1691                         $sql_match_operator = '>=';
 1692                     }
 1693                     if (($input == 0 && $sql_match_operator != '>') || ($input == 1 && $sql_match_operator == '<')) {
 1694                         $where[] = "`rating_" . $my_type . "_" . $user_id . "`.`rating` IS NULL";
 1695                     } elseif ($sql_match_operator == '<>' || $sql_match_operator == '<' || $sql_match_operator == '<=' || $sql_match_operator == '!=') {
 1696                         $where[] = "(`rating_" . $my_type . "_" . $user_id . "`.`rating` $sql_match_operator $input OR `rating_" . $my_type . "_" . $user_id . "`.`rating` IS NULL)";
 1697                     } else {
 1698                         $where[] = "`rating_" . $my_type . "_" . $user_id . "`.`rating` $sql_match_operator $input";
 1699                     }
 1700                     // rating once per user
 1701                     $table['rating'] .= (!strpos((string) $table['rating'], "rating_" . $my_type . "_" . $user_id))
 1702                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `rating` FROM `rating` WHERE `user` = $user_id AND `object_type`='$my_type') AS `rating_" . $my_type . "_" . $user_id . "` ON `rating_" . $my_type . "_" . $user_id . "`.`object_id`=`artist`.`$column`"
 1703                         : "";
 1704                     break;
 1705                 case 'myplayed':
 1706                     $column       = 'id';
 1707                     $my_type      = 'artist';
 1708                     $operator_sql = ((int)$sql_match_operator == 0) ? 'IS NULL' : 'IS NOT NULL';
 1709                     // played once per user
 1710                     $table['myplayed'] .= (!strpos((string) $table['myplayed'], "myplayed_" . $my_type . "_" . $user_id))
 1711                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user` FROM `object_count` WHERE `object_count`.`object_type` = '$my_type' AND `object_count`.`count_type` = 'stream' AND `object_count`.`user`=$user_id GROUP BY `object_id`, `object_type`, `user`) AS `myplayed_" . $my_type . "_" . $user_id . "` ON `artist`.`$column`=`myplayed_" . $my_type . "_" . $user_id . "`.`object_id` AND `myplayed_" . $my_type . "_" . $user_id . "`.`object_type` = '$my_type'"
 1712                         : "";
 1713                     $where[] = "`myplayed_" . $my_type . "_" . $user_id . "`.`object_id` $operator_sql";
 1714                     break;
 1715                 case 'last_play':
 1716                     $my_type = 'artist';
 1717                     $table['last_play'] .= (!strpos((string) $table['last_play'], "last_play_" . $my_type . "_" . $user_id))
 1718                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user`, MAX(`date`) AS `date` FROM `object_count` WHERE `object_count`.`object_type` = '$my_type' AND `object_count`.`count_type` = 'stream' AND `object_count`.`user`=$user_id GROUP BY `object_id`, `object_type`, `user`) AS `last_play_" . $my_type . "_" . $user_id . "` ON `artist`.`id`=`last_play_" . $my_type . "_" . $user_id . "`.`object_id` AND `last_play_" . $my_type . "_" . $user_id . "`.`object_type` = '$my_type'"
 1719                         : "";
 1720                     $where[] = "`last_play_" . $my_type . "_" . $user_id . "`.`date` $sql_match_operator (UNIX_TIMESTAMP() - ($input * 86400))";
 1721                     break;
 1722                 case 'last_skip':
 1723                     $my_type = 'artist';
 1724                     $table['last_skip'] .= (!strpos((string) $table['last_skip'], "last_skip_" . $my_type . "_" . $user_id))
 1725                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user`, MAX(`date`) AS `date` FROM `object_count` WHERE `object_count`.`object_type` = 'song' AND `object_count`.`count_type` = 'skip' AND `object_count`.`user`=$user_id GROUP BY `object_id`, `object_type`, `user`) AS `last_skip_" . $my_type . "_" . $user_id . "` ON `song`.`id`=`last_skip_" . $my_type . "_" . $user_id . "`.`object_id` AND `last_skip_" . $my_type . "_" . $user_id . "`.`object_type` = 'song'"
 1726                         : "";
 1727                     $where[]      = "`last_skip_" . $my_type . "_" . $user_id . "`.`date` $sql_match_operator (UNIX_TIMESTAMP() - ($input * 86400))";
 1728                     $join['song'] = true;
 1729                     break;
 1730                 case 'last_play_or_skip':
 1731                     $my_type = 'artist';
 1732                     $table['last_play_or_skip'] .= (!strpos((string) $table['last_play_or_skip'], "last_play_or_skip_" . $my_type . "_" . $user_id))
 1733                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user`, MAX(`date`) AS `date` FROM `object_count` WHERE `object_count`.`object_type` = 'song' AND `object_count`.`count_type` IN ('stream', 'skip') AND `object_count`.`user`=$user_id GROUP BY `object_id`, `object_type`, `user`) AS `last_play_or_skip_" . $my_type . "_" . $user_id . "` ON `song`.`id`=`last_play_or_skip_" . $my_type . "_" . $user_id . "`.`object_id` AND `last_play_or_skip_" . $my_type . "_" . $user_id . "`.`object_type` = 'song'"
 1734                         : "";
 1735                     $where[]      = "`last_play_or_skip_" . $my_type . "_" . $user_id . "`.`date` $sql_match_operator (UNIX_TIMESTAMP() - ($input * 86400))";
 1736                     $join['song'] = true;
 1737                     break;
 1738                 case 'played_times':
 1739                     $where[] = "`artist`.`id` IN (SELECT `object_count`.`object_id` FROM `object_count` WHERE `object_count`.`object_type` = 'artist' AND `object_count`.`count_type` = 'stream' GROUP BY `object_count`.`object_id` HAVING COUNT(*) $sql_match_operator '$input')";
 1740                     break;
 1741                 case 'other_user':
 1742                     $other_userid = $input;
 1743                     if ($sql_match_operator == 'userflag') {
 1744                         $where[] = "`favorite_artist_$other_userid`.`user` = $other_userid AND `favorite_artist_$other_userid`.`object_type` = 'artist'";
 1745                         // flag once per user
 1746                         $table['favorite'] .= (!strpos((string) $table['favorite'], "favorite_artist_$other_userid"))
 1747                             ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user` FROM `user_flag` WHERE `user` = $other_userid) AS `favorite_artist_$other_userid` ON `song`.`artist`=`favorite_artist_$other_userid`.`object_id` AND `favorite_artist_$other_userid`.`object_type` = 'artist'"
 1748                             : "";
 1749                     } else {
 1750                         $column  = 'id';
 1751                         $my_type = 'artist';
 1752                         $where[] = "`rating_artist_" . $other_userid . '`.' . $sql_match_operator . " AND `rating_artist_$other_userid`.`user` = $other_userid AND `rating_artist_$other_userid`.`object_type` = 'artist'";
 1753                         // rating once per user
 1754                         $table['rating'] .= (!strpos((string) $table['rating'], "rating_" . $my_type . "_" . $user_id))
 1755                             ? "LEFT JOIN `rating` AS `rating_" . $my_type . "_" . $user_id . "` ON `rating_" . $my_type . "_" . $user_id . "`.`object_type`='$my_type' AND `rating_" . $my_type . "_" . $user_id . "`.`object_id`=`$my_type`.`$column` AND `rating_" . $my_type . "_" . $user_id . "`.`user` = $user_id "
 1756                             : "";
 1757                     }
 1758                     break;
 1759                 case 'recent_played':
 1760                     $key                     = md5($input . $sql_match_operator);
 1761                     $where[]                 = "`played_$key`.`object_id` IS NOT NULL";
 1762                     $table['played_' . $key] = "LEFT JOIN (SELECT `object_id` FROM `object_count` WHERE `object_type` = 'artist' ORDER BY $sql_match_operator DESC LIMIT $input) as `played_$key` ON `artist`.`id` = `played_$key`.`object_id`";
 1763                     break;
 1764                 case 'catalog':
 1765                     $where[]                = "`artist_catalog`.`catalog_id` $sql_match_operator '$input'";
 1766                     $join['artist_catalog'] = true;
 1767                     break;
 1768                 case 'mbid':
 1769                     $where[] = "`artist`.`mbid` $sql_match_operator '$input'";
 1770                     break;
 1771                 case 'possible_duplicate':
 1772                     $where[]               = "(`dupe_search1`.`dupe_id1` IS NOT NULL OR `dupe_search2`.`dupe_id2` IS NOT NULL)";
 1773                     $table['dupe_search1'] = "LEFT JOIN (SELECT MIN(`id`) AS `dupe_id1`, LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`)) AS `fullname`, COUNT(LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`))) AS `Counting` FROM `artist` GROUP BY `fullname` HAVING `Counting` > 1) AS `dupe_search1` ON `artist`.`id` = `dupe_search1`.`dupe_id1`";
 1774                     $table['dupe_search2'] = "LEFT JOIN (SELECT MAX(`id`) AS `dupe_id2`, LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`)) AS `fullname`, COUNT(LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`))) AS `Counting` FROM `artist` GROUP BY `fullname` HAVING `Counting` > 1) AS `dupe_search2` ON `artist`.`id` = `dupe_search2`.`dupe_id2`";
 1775                     break;
 1776                 case 'possible_duplicate_album':
 1777                     $where[]                     = "(`dupe_album_search1`.`dupe_album_id1` IS NOT NULL OR `dupe_album_search2`.`dupe_album_id2` IS NOT NULL)";
 1778                     $table['dupe_album_search1'] = "LEFT JOIN (SELECT album_artist, MIN(`id`) AS `dupe_album_id1`, LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)) AS `fullname`, COUNT(LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`))) AS `Counting` FROM `album` GROUP BY `album_artist`, LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)), `disk`, `year`, `release_type`, `release_status` HAVING `Counting` > 1) AS `dupe_album_search1` ON `artist`.`id` = `dupe_album_search1`.`album_artist`";
 1779                     $table['dupe_album_search2'] = "LEFT JOIN (SELECT album_artist, MAX(`id`) AS `dupe_album_id2`, LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)) AS `fullname`, COUNT(LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`))) AS `Counting` FROM `album` GROUP BY `album_artist`, LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)), `disk`, `year`, `release_type`, `release_status` HAVING `Counting` > 1) AS `dupe_album_search2` ON `artist`.`id` = `dupe_album_search2`.`album_artist`";
 1780                     break;
 1781                 default:
 1782                     break;
 1783             } // switch on ruletype artist
 1784         } // foreach rule
 1785 
 1786         $join['song']        = $join['song'] || $catalog_disable || $catalog_filter;
 1787         $join['catalog']     = $catalog_disable || $catalog_filter;
 1788         $join['catalog_map'] = $catalog_filter;
 1789 
 1790         $where_sql = implode(" $sql_logic_operator ", $where);
 1791 
 1792         if ($join['song']) {
 1793             $table['0_song'] = "LEFT JOIN `song` ON `song`.`artist`=`artist`.`id`";
 1794         }
 1795         if ($join['catalog']) {
 1796             $table['1_catalog'] = "LEFT JOIN `catalog` AS `catalog_se` ON `catalog_se`.`id`=`song`.`catalog`";
 1797             if (!empty($where_sql)) {
 1798                 $where_sql .= " AND `catalog_se`.`enabled` = '1' AND `song`.`enabled` = 1";
 1799             } else {
 1800                 $where_sql .= " `catalog_se`.`enabled` = '1' AND `song`.`enabled` = 1";
 1801             }
 1802         }
 1803         if ($join['catalog_map']) {
 1804             $table['2_catalog_map'] = "LEFT JOIN `catalog_map` AS `catalog_map_artist` ON `catalog_map_artist`.`object_id`=`artist`.`id` AND `catalog_map_artist`.`object_type` = 'artist' AND `catalog_map_artist`.`catalog_id` = `catalog_se`.`id`";
 1805             if (!empty($where_sql)) {
 1806                 $where_sql .= " AND `catalog_se`.`filter_user` IN (0, $user_id)";
 1807             } else {
 1808                 $where_sql .= " `catalog_se`.`filter_user` IN (0, $user_id)";
 1809             }
 1810         }
 1811         if ($join['count']) {
 1812             $table['object_count'] = "LEFT JOIN (SELECT `object_count`.`object_id`, MAX(`object_count`.`date`) AS `date` FROM `object_count` WHERE `object_count`.`object_type` = 'artist' AND `object_count`.`user`='" . $user_id . "' AND `object_count`.`count_type` = 'stream' GROUP BY `object_count`.`object_id`) AS `object_count` ON `object_count`.`object_id`=`artist`.`id`";
 1813         }
 1814         if ($join['image']) {
 1815             $table['0_song'] = "LEFT JOIN `song` ON `song`.`artist`=`artist`.`id` LEFT JOIN `image` ON `image`.`object_id`=`artist`.`id`";
 1816             $where_sql .= " AND `image`.`object_type`='artist'";
 1817             $where_sql .= " AND `image`.`size`='original'";
 1818         }
 1819         if ($join['artist_catalog']) {
 1820             $table['catalog_map'] = "LEFT JOIN `catalog_map` AS `artist_catalog` ON `artist_catalog`.`object_type` = 'artist' AND `artist_catalog`.`object_id`=`artist`.`id`";
 1821         }
 1822         ksort($table);
 1823         $table_sql  = implode(' ', $table);
 1824         $group_sql  = implode(',', $group);
 1825         $having_sql = implode(" $sql_logic_operator ", $having);
 1826 
 1827         return array(
 1828             'base' => "SELECT DISTINCT(`artist`.`id`), `artist`.`name` FROM `artist`",
 1829             'join' => $join,
 1830             'where' => $where,
 1831             'where_sql' => $where_sql,
 1832             'table' => $table,
 1833             'table_sql' => $table_sql,
 1834             'group_sql' => $group_sql,
 1835             'having_sql' => $having_sql
 1836         );
 1837     }
 1838 
 1839     /**
 1840      * song_to_sql
 1841      * Handles the generation of the SQL for song searches.
 1842      * @return array
 1843      */
 1844     private function song_to_sql()
 1845     {
 1846         $sql_logic_operator = $this->logic_operator;
 1847         $user_id            = $this->search_user->id;
 1848         $catalog_disable    = AmpConfig::get('catalog_disable');
 1849         $catalog_filter     = AmpConfig::get('catalog_filter');
 1850 
 1851         $where       = array();
 1852         $table       = array();
 1853         $join        = array();
 1854         $group       = array();
 1855         $having      = array();
 1856         $metadata    = array();
 1857 
 1858         foreach ($this->rules as $rule) {
 1859             $type     = $this->name_to_basetype($rule[0]);
 1860             $operator = array();
 1861             if (!$type) {
 1862                 return array();
 1863             }
 1864             foreach ($this->basetypes[$type] as $op) {
 1865                 if ($op['name'] == $rule[1]) {
 1866                     $operator = $op;
 1867                     break;
 1868                 }
 1869             }
 1870             $raw_input          = $this->_mangle_data($rule[2], $type, $operator);
 1871             $input              = filter_var($raw_input, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
 1872             $sql_match_operator = $operator['sql'];
 1873 
 1874             switch ($rule[0]) {
 1875                 case 'anywhere':
 1876                     $tag_string = "`song`.`id` IN (SELECT `tag_map`.`object_id` FROM `tag_map` WHERE `tag_map`.`object_type`='song' AND `tag_map`.`tag_id` IN (SELECT `tag`.`id` FROM `tag` WHERE `tag`.`is_hidden` = 0 AND `tag`.`name` $sql_match_operator '$input'))";
 1877                     // we want AND NOT and like for this query to really exclude them
 1878                     if ($sql_match_operator == 'NOT LIKE' || $sql_match_operator == 'NOT' || $sql_match_operator == '!=') {
 1879                         $where[] = "NOT ((`artist`.`name` LIKE '$input' OR LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`)) LIKE '$input') OR (`album`.`name` LIKE '$input' OR LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)) LIKE '$input') OR `song_data`.`comment` LIKE '$input' OR `song_data`.`label` LIKE '$input' OR `song`.`file` LIKE '$input' OR `song`.`title` LIKE '$input' OR NOT " . $tag_string . ')';
 1880                     } else {
 1881                         $where[] = "((`artist`.`name` $sql_match_operator '$input' OR LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`)) $sql_match_operator '$input') OR (`album`.`name` $sql_match_operator '$input' OR LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)) $sql_match_operator '$input') OR `song_data`.`comment` $sql_match_operator '$input' OR `song_data`.`label` $sql_match_operator '$input' OR `song`.`file` $sql_match_operator '$input' OR `song`.`title` $sql_match_operator '$input' OR " . $tag_string . ')';
 1882                     }
 1883                     // join it all up
 1884                     $table['album']    = "LEFT JOIN `album` ON `song`.`album`=`album`.`id`";
 1885                     $table['artist']   = "LEFT JOIN `artist` ON `song`.`artist`=`artist`.`id`";
 1886                     $join['song_data'] = true;
 1887                     break;
 1888                 case 'tag':
 1889                     $where[] = "`song`.`id` IN (SELECT `tag_map`.`object_id` FROM `tag_map` WHERE `tag_map`.`object_type`='song' AND `tag_map`.`tag_id` IN (SELECT `tag`.`id` FROM `tag` WHERE `tag`.`is_hidden` = 0 AND `tag`.`name` $sql_match_operator '$input'))";
 1890                     break;
 1891                 case 'album_tag':
 1892                     $table['album'] = "LEFT JOIN `album` ON `song`.`album`=`album`.`id`";
 1893                     $where[]        = "`album`.`id` IN (SELECT `tag_map`.`object_id` FROM `tag_map` WHERE `tag_map`.`object_type`='album' AND `tag_map`.`tag_id` IN (SELECT `tag`.`id` FROM `tag` WHERE `tag`.`is_hidden` = 0 AND `tag`.`name` $sql_match_operator '$input'))";
 1894                     break;
 1895                 case 'artist_tag':
 1896                     $table['artist'] = "LEFT JOIN `artist` ON `song`.`artist`=`artist`.`id`";
 1897                     $where[]         = "`artist`.`id` IN (SELECT `tag_map`.`object_id` FROM `tag_map` WHERE `tag_map`.`object_type`='artist' AND `tag_map`.`tag_id` IN (SELECT `tag`.`id` FROM `tag` WHERE `tag`.`is_hidden` = 0 AND `tag`.`name` $sql_match_operator '$input'))";
 1898                     break;
 1899                 case 'title':
 1900                     $where[] = "`song`.`title` $sql_match_operator '$input'";
 1901                     break;
 1902                 case 'album':
 1903                     $where[]        = "(`album`.`name` $sql_match_operator '$input' OR LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)) $sql_match_operator '$input')";
 1904                     $table['album'] = "LEFT JOIN `album` ON `song`.`album`=`album`.`id`";
 1905                     break;
 1906                 case 'artist':
 1907                     $where[]         = "(`artist`.`name` $sql_match_operator '$input' OR LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`)) $sql_match_operator '$input')";
 1908                     $table['artist'] = "LEFT JOIN `artist` ON `song`.`artist`=`artist`.`id`";
 1909                     break;
 1910                 case 'album_artist':
 1911                     $where[]         = "(`album_artist`.`name` $sql_match_operator '$input' " .
 1912                         " OR LTRIM(CONCAT(COALESCE(`album_artist`.`prefix`, ''), " .
 1913                         "' ', `album_artist`.`name`)) $sql_match_operator '$input')";
 1914                     $table['album']        = "LEFT JOIN `album` ON `song`.`album`=`album`.`id`";
 1915                     $table['album_artist'] = "LEFT JOIN `artist` AS `album_artist` ON `album`.`album_artist`=`album_artist`.`id`";
 1916                     break;
 1917                 case 'composer':
 1918                     $where[] = "`song`.`composer` $sql_match_operator '$input'";
 1919                     break;
 1920                 case 'time':
 1921                     $input   = $input * 60;
 1922                     $where[] = "`song`.`time` $sql_match_operator '$input'";
 1923                     break;
 1924                 case 'file':
 1925                     $where[] = "`song`.`file` $sql_match_operator '$input'";
 1926                     break;
 1927                 case 'year':
 1928                     $where[] = "`song`.`year` $sql_match_operator '$input'";
 1929                     break;
 1930                 case 'comment':
 1931                     $where[]           = "`song_data`.`comment` $sql_match_operator '$input'";
 1932                     $join['song_data'] = true;
 1933                     break;
 1934                 case 'label':
 1935                     $where[]           = "`song_data`.`label` $sql_match_operator '$input'";
 1936                     $join['song_data'] = true;
 1937                     break;
 1938                 case 'lyrics':
 1939                     $where[]           = "`song_data`.`lyrics` $sql_match_operator '$input'";
 1940                     $join['song_data'] = true;
 1941                     break;
 1942                 case 'played':
 1943                     $where[] = "`song`.`played` = '$sql_match_operator'";
 1944                     break;
 1945                 case 'last_play':
 1946                     $my_type = 'song';
 1947                     $table['last_play'] .= (!strpos((string) $table['last_play'], "last_play_" . $my_type . "_" . $user_id))
 1948                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user`, MAX(`date`) AS `date` FROM `object_count` WHERE `object_count`.`object_type` = '$my_type' AND `object_count`.`count_type` = 'stream' AND `object_count`.`user`=$user_id GROUP BY `object_id`, `object_type`, `user`) AS `last_play_" . $my_type . "_" . $user_id . "` ON `song`.`id`=`last_play_" . $my_type . "_" . $user_id . "`.`object_id` AND `last_play_" . $my_type . "_" . $user_id . "`.`object_type` = '$my_type'"
 1949                         : "";
 1950                     $where[] = "`last_play_" . $my_type . "_" . $user_id . "`.`date` $sql_match_operator (UNIX_TIMESTAMP() - ($input * 86400))";
 1951                     break;
 1952                 case 'last_skip':
 1953                     $my_type = 'song';
 1954                     $table['last_skip'] .= (!strpos((string) $table['last_skip'], "last_skip_" . $my_type . "_" . $user_id))
 1955                         ?
 1956                         "LEFT JOIN (SELECT `object_id`, `object_type`, `user`, MAX(`date`) AS `date` FROM `object_count` " .
 1957                         "WHERE `object_count`.`object_type` = '$my_type' AND `object_count`.`count_type` = 'skip' " .
 1958                         "AND `object_count`.`user`=$user_id GROUP BY `object_id`, `object_type`, `user`) AS `last_skip_" . $my_type . "_" . $user_id . "` " .
 1959                         "ON `song`.`id`=`last_skip_" . $my_type . "_" . $user_id . "`.`object_id` " .
 1960                         "AND `last_skip_" . $my_type . "_" . $user_id . "`.`object_type` = '$my_type' " : "";
 1961                     $where[] = "`last_skip_" . $my_type . "_" . $user_id . "`.`date` $sql_match_operator (UNIX_TIMESTAMP() - ($input * 86400))";
 1962                     break;
 1963                 case 'last_play_or_skip':
 1964                     $my_type = 'song';
 1965                     $table['last_play_or_skip'] .= (!strpos((string) $table['play_or_skip'], "play_or_skip_" . $my_type . "_" . $user_id))
 1966                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user`, MAX(`date`) AS `date` FROM `object_count` WHERE `object_count`.`object_type` = '$my_type' AND `object_count`.`count_type` IN ('stream', 'skip') AND `object_count`.`user`=$user_id GROUP BY `object_id`, `object_type`, `user`) AS `play_or_skip_" . $my_type . "_" . $user_id . "` ON `song`.`id`=`play_or_skip_" . $my_type . "_" . $user_id . "`.`object_id` AND `play_or_skip_" . $my_type . "_" . $user_id . "`.`object_type` = '$my_type'"
 1967                         : "";
 1968                     $where[] = "`play_or_skip_" . $my_type . "_" . $user_id . "`.`date` $sql_match_operator (UNIX_TIMESTAMP() - ($input * 86400))";
 1969                     break;
 1970                 case 'played_times':
 1971                     $where[] = "`song`.`id` IN (SELECT `object_count`.`object_id` FROM `object_count` WHERE `object_count`.`object_type` = 'song' AND `object_count`.`count_type` = 'stream' GROUP BY `object_count`.`object_id` HAVING COUNT(*) $sql_match_operator '$input')";
 1972                     break;
 1973                 case 'skipped_times':
 1974                     $where[] = "`song`.`id` IN (SELECT `object_count`.`object_id` FROM `object_count` WHERE `object_count`.`object_type` = 'song' AND `object_count`.`count_type` = 'skip' GROUP BY `object_count`.`object_id` HAVING COUNT(*) $sql_match_operator '$input')";
 1975                     break;
 1976                 case 'played_or_skipped_times':
 1977                     $where[] = "`song`.`id` IN (SELECT `object_count`.`object_id` FROM `object_count` " .
 1978                         "WHERE `object_count`.`object_type` = 'song' AND `object_count`.`count_type` IN ('stream', 'skip') " .
 1979                         "GROUP BY `object_count`.`object_id` HAVING COUNT(*) $sql_match_operator '$input')";
 1980                     break;
 1981                 case 'play_skip_ratio':
 1982                     $where[] = "`song`.`id` IN (SELECT `song`.`id` FROM `song` LEFT JOIN (SELECT COUNT(`object_id`) AS `counting`, `object_id`, `count_type` FROM `object_count` WHERE `object_type` = 'song' AND `count_type` = 'stream' GROUP BY `object_id`, `count_type`) AS `stream_count` on `song`.`id` = `stream_count`.`object_id` LEFT JOIN (SELECT COUNT(`object_id`) AS `counting`, `object_id`, `count_type` FROM `object_count` WHERE `object_type` = 'song' AND `count_type` = 'skip' GROUP BY `object_id`, `count_type`) AS `skip_count` on `song`.`id` = `skip_count`.`object_id` WHERE ((IFNULL(`stream_count`.`counting`, 0)/IFNULL(`skip_count`.`counting`, 0)) * 100) $sql_match_operator '$input' GROUP BY `song`.`id`)";
 1983                     break;
 1984                 case 'myplayed':
 1985                 case 'myplayedalbum':
 1986                 case 'myplayedartist':
 1987                 // combine these as they all do the same thing just different tables
 1988                 $looking      = str_replace('myplayed', '', $rule[0]);
 1989                 $column       = ($looking == '') ? 'id' : $looking;
 1990                 $my_type      = ($looking == '') ? 'song' : $looking;
 1991                 $operator_sql = ((int) $sql_match_operator == 0) ? 'IS NULL' : 'IS NOT NULL';
 1992                 // played once per user
 1993                 $table['myplayed'] .= (!strpos((string) $table['myplayed'], "myplayed_" . $my_type . "_" . $user_id))
 1994                     ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user` FROM `object_count` WHERE `object_count`.`object_type` = '$my_type' AND `object_count`.`count_type` = 'stream' AND `object_count`.`user`=$user_id GROUP BY `object_id`, `object_type`, `user`) AS `myplayed_" . $my_type . "_" . $user_id . "` ON `song`.`$column`=`myplayed_" . $my_type . "_" . $user_id . "`.`object_id` AND `myplayed_" . $my_type . "_" . $user_id . "`.`object_type` = '$my_type'"
 1995                     : "";
 1996                 $where[] = "`myplayed_" . $my_type . "_" . $user_id . "`.`object_id` $operator_sql";
 1997                     break;
 1998                 case 'bitrate':
 1999                     $input   = $input * 1000;
 2000                     $where[] = "`song`.`bitrate` $sql_match_operator '$input'";
 2001                     break;
 2002                 case 'rating':
 2003                     // average ratings only
 2004                     $where[]          = "`average_rating`.`avg` $sql_match_operator '$input'";
 2005                     $table['average'] = "LEFT JOIN (SELECT `object_id`, ROUND(AVG(IFNULL(`rating`.`rating`,0))) AS `avg` FROM `rating` WHERE `rating`.`object_type`='song' GROUP BY `object_id`) AS `average_rating` on `average_rating`.`object_id` = `song`.`id` ";
 2006                     break;
 2007                 case 'favorite':
 2008                     $where[] = "`song`.`title` $sql_match_operator '$input' AND `favorite_song_$user_id`.`user` = $user_id AND `favorite_song_$user_id`.`object_type` = 'song'";
 2009                     // flag once per user
 2010                     $table['favorite'] .= (!strpos((string) $table['favorite'], "favorite_song_$user_id"))
 2011                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user` FROM `user_flag` WHERE `user` = $user_id) AS `favorite_song_$user_id` ON `song`.`id`=`favorite_song_$user_id`.`object_id` AND `favorite_song_$user_id`.`object_type` = 'song'"
 2012                         : "";
 2013                     break;
 2014                 case 'favorite_album':
 2015                     $where[] = "(`album`.`name` $sql_match_operator '$input' OR LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)) $sql_match_operator '$input') AND `favorite_album_$user_id`.`user` = $user_id AND `favorite_album_$user_id`.`object_type` = 'album'";
 2016                     // flag once per user
 2017                     $table['favorite'] .= (!strpos((string) $table['favorite'], "favorite_album_$user_id"))
 2018                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user` FROM `user_flag` WHERE `user` = $user_id) AS `favorite_album_$user_id` ON `album`.`id`=`favorite_album_$user_id`.`object_id` AND `favorite_album_$user_id`.`object_type` = 'album'"
 2019                         : "";
 2020                     $join['album'] = true;
 2021                     break;
 2022                 case 'favorite_artist':
 2023                     $where[] = "(`artist`.`name` $sql_match_operator '$input' OR LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`)) $sql_match_operator '$input') AND `favorite_artist_$user_id`.`user` = $user_id AND `favorite_artist_$user_id`.`object_type` = 'artist'";
 2024                     // flag once per user
 2025                     $table['favorite'] .= (!strpos((string) $table['favorite'], "favorite_artist_$user_id"))
 2026                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user` FROM `user_flag` WHERE `user` = $user_id) AS `favorite_artist_$user_id` ON `artist`.`id`=`favorite_artist_$user_id`.`object_id` AND `favorite_artist_$user_id`.`object_type` = 'artist'"
 2027                         : "";
 2028                     $join['artist'] = true;
 2029                     break;
 2030                 case 'myrating':
 2031                 case 'albumrating':
 2032                 case 'artistrating':
 2033                     // combine these as they all do the same thing just different tables
 2034                     $looking = str_replace('rating', '', $rule[0]);
 2035                     $column  = ($looking == 'my') ? 'id' : $looking;
 2036                     $my_type = ($looking == 'my') ? 'song' : $looking;
 2037                     if ($input == 0 && $sql_match_operator == '>=') {
 2038                         break;
 2039                     }
 2040                     if ($input == 0 && $sql_match_operator == '<') {
 2041                         $input              = -1;
 2042                         $sql_match_operator = '=';
 2043                     }
 2044                     if ($input == 0 && $sql_match_operator == '<>') {
 2045                         $input              = 1;
 2046                         $sql_match_operator = '>=';
 2047                     }
 2048                     if (($input == 0 && $sql_match_operator != '>') || ($input == 1 && $sql_match_operator == '<')) {
 2049                         $where[] = "`rating_" . $my_type . "_" . $user_id . "`.`rating` IS NULL";
 2050                     } elseif ($sql_match_operator == '<>' || $sql_match_operator == '<' || $sql_match_operator == '<=' || $sql_match_operator == '!=') {
 2051                         $where[] = "(`rating_" . $my_type . "_" . $user_id . "`.`rating` $sql_match_operator $input OR `rating_" . $my_type . "_" . $user_id . "`.`rating` IS NULL)";
 2052                     } else {
 2053                         $where[] = "`rating_" . $my_type . "_" . $user_id . "`.`rating` $sql_match_operator $input";
 2054                     }
 2055                     // rating once per user
 2056                     $table['rating'] .= (!strpos((string) $table['rating'], "rating_" . $my_type . "_" . $user_id))
 2057                         ? "LEFT JOIN (SELECT `object_id`, `object_type`, `rating` FROM `rating` WHERE `user` = $user_id AND `object_type`='$my_type') AS `rating_" . $my_type . "_" . $user_id . "` ON `rating_" . $my_type . "_" . $user_id . "`.`object_id`=`song`.`$column`"
 2058                         : "";
 2059                     break;
 2060                 case 'catalog':
 2061                     $where[] = "`song`.`catalog` $sql_match_operator '$input'";
 2062                     break;
 2063                 case 'other_user':
 2064                 case 'other_user_album':
 2065                 case 'other_user_artist':
 2066                     // combine these as they all do the same thing just different tables
 2067                     $looking      = str_replace('other_user_', '', $rule[0]);
 2068                     $column       = ($looking == 'other_user') ? 'id' : $looking;
 2069                     $my_type      = ($looking == 'other_user') ? 'song' : $looking;
 2070                     $other_userid = $input;
 2071                     if ($sql_match_operator == 'userflag') {
 2072                         $where[] = "`favorite_" . $my_type . "_" . $other_userid . "`.`user` = $other_userid AND `favorite_" . $my_type . "_" . $other_userid . "`.`object_type` = '$my_type'";
 2073                         // flag once per user
 2074                         $table['favorite'] .= (!strpos((string) $table['favorite'], "favorite_" . $my_type . "_" . $other_userid . ""))
 2075                             ? "LEFT JOIN (SELECT `object_id`, `object_type`, `user` FROM `user_flag` WHERE `user` = $other_userid) AS `favorite_" . $my_type . "_" . $other_userid . "` ON `song`.`$column`=`favorite_" . $my_type . "_" . $other_userid . "`.`object_id` AND `favorite_" . $my_type . "_" . $other_userid . "`.`object_type` = '$my_type'"
 2076                             : "";
 2077                     } else {
 2078                         $unrated = ($sql_match_operator == 'unrated');
 2079                         $where[] = ($unrated) ? "`song`.`$column` NOT IN (SELECT `object_id` FROM `rating` WHERE `object_type` = '$my_type' AND `user` = $other_userid)" : "`rating_" . $my_type . "_" . $other_userid . "`.$sql_match_operator AND `rating_" . $my_type . "_" . $other_userid . "`.`user` = $other_userid AND `rating_" . $my_type . "_" . $other_userid . "`.`object_type` = '$my_type'";
 2080                         // rating once per user
 2081                         $table['rating'] .= (!strpos((string) $table['rating'], "rating_" . $my_type . "_" . $other_userid))
 2082                             ? "LEFT JOIN `rating` AS `rating_" . $my_type . "_" . $other_userid . "` ON `rating_" . $my_type . "_" . $other_userid . "`.`object_type`='$my_type' AND `rating_" . $my_type . "_" . $other_userid . "`.`object_id`=`song`.`$column` AND `rating_" . $my_type . "_" . $other_userid . "`.`user` = $other_userid "
 2083                             : "";
 2084                     }
 2085                     break;
 2086                 case 'playlist_name':
 2087                     $join['playlist']      = true;
 2088                     $join['playlist_data'] = true;
 2089                     $where[]               = "`playlist`.`name` $sql_match_operator '$input'";
 2090                     break;
 2091                 case 'playlist':
 2092                     $join['playlist_data'] = true;
 2093                     $where[]               = "`playlist_data`.`playlist` $sql_match_operator '$input'";
 2094                     break;
 2095                 case 'smartplaylist':
 2096                     $subsearch  = new Search($input, 'song', $this->search_user);
 2097                     $results    = $subsearch->get_items();
 2098                     $itemstring = '';
 2099                     if (count($results) > 0) {
 2100                         foreach ($results as $item) {
 2101                             $itemstring .= ' ' . $item['object_id'] . ',';
 2102                         }
 2103                         $where[]  = "`song`.`id` $sql_match_operator IN (" . substr($itemstring, 0, -1) . ")";
 2104                     }
 2105                     break;
 2106                 case 'license':
 2107                     $where[] = "`song`.`license` $sql_match_operator '$input'";
 2108                     break;
 2109                 case 'added':
 2110                     $input   = strtotime((string) $input);
 2111                     $where[] = "`song`.`addition_time` $sql_match_operator $input";
 2112                     break;
 2113                 case 'updated':
 2114                     $input   = strtotime((string) $input);
 2115                     $where[] = "`song`.`update_time` $sql_match_operator $input";
 2116                     break;
 2117                 case 'recent_played':
 2118                     $key                     = md5($input . $sql_match_operator);
 2119                     $where[]                 = "`played_$key`.`object_id` IS NOT NULL";
 2120                     $table['played_' . $key] = "LEFT JOIN (SELECT `object_id` FROM `object_count` WHERE `object_type` = 'song' ORDER BY $sql_match_operator DESC LIMIT $input) as `played_$key` ON `song`.`id` = `played_$key`.`object_id`";
 2121                     break;
 2122                 case 'recent_added':
 2123                     $key                       = md5($input . $sql_match_operator);
 2124                     $where[]                   = "`addition_time_$key`.`id` IS NOT NULL";
 2125                     $table['addition_' . $key] = "LEFT JOIN (SELECT `id` FROM `song` ORDER BY $sql_match_operator DESC LIMIT $input) as `addition_time_$key` ON `song`.`id` = `addition_time_$key`.`id`";
 2126                     break;
 2127                 case 'recent_updated':
 2128                     $key                     = md5($input . $sql_match_operator);
 2129                     $where[]                 = "`update_time_$key`.`id` IS NOT NULL";
 2130                     $table['update_' . $key] = "LEFT JOIN (SELECT `id` FROM `song` ORDER BY $sql_match_operator DESC LIMIT $input) as `update_time_$key` ON `song`.`id` = `update_time_$key`.`id`";
 2131                     break;
 2132                 case 'mbid':
 2133                     $where[] = "`song`.`mbid` $sql_match_operator '$input'";
 2134                     break;
 2135                 case 'mbid_album':
 2136                     $table['album'] = "LEFT JOIN `album` ON `song`.`album`=`album`.`id`";
 2137                     $where[]        = "`album`.`mbid` $sql_match_operator '$input'";
 2138                     break;
 2139                 case 'mbid_artist':
 2140                     $table['artist'] = "LEFT JOIN `artist` ON `song`.`artist`=`artist`.`id`";
 2141                     $where[]         = "`artist`.`mbid` $sql_match_operator '$input'";
 2142                     break;
 2143                 case 'possible_duplicate':
 2144                     $where[]               = "(`dupe_search1`.`dupe_id1` IS NOT NULL OR `dupe_search2`.`dupe_id2` IS NOT NULL)";
 2145                     $table['dupe_search1'] = "LEFT JOIN (SELECT MIN(`song`.`id`) AS `dupe_id1`, CONCAT(LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`)), LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)), `album`.`disk`, `song`.`title`) AS `fullname`, COUNT(CONCAT(LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`)), LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)), `album`.`disk`, `song`.`title`)) AS `counting` FROM `song` LEFT JOIN `album` on `song`.`album` = `album`.`id` LEFT JOIN `artist` ON `song`.`artist` = `artist`.`id` GROUP BY `fullname` HAVING `Counting` > 1) AS `dupe_search1` ON `song`.`id` = `dupe_search1`.`dupe_id1`";
 2146                     $table['dupe_search2'] = "LEFT JOIN (SELECT MAX(`song`.`id`) AS `dupe_id2`, CONCAT(LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`)), LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)), `album`.`disk`, `song`.`title`) AS `fullname`, COUNT(CONCAT(LTRIM(CONCAT(COALESCE(`artist`.`prefix`, ''), ' ', `artist`.`name`)), LTRIM(CONCAT(COALESCE(`album`.`prefix`, ''), ' ', `album`.`name`)), `album`.`disk`, `song`.`title`)) AS `counting` FROM `song` LEFT JOIN `album` on `song`.`album` = `album`.`id` LEFT JOIN `artist` ON `song`.`artist` = `artist`.`id` GROUP BY `fullname` HAVING `Counting` > 1) AS `dupe_search2` ON `song`.`id` = `dupe_search2`.`dupe_id2`";
 2147                     break;
 2148                 case 'metadata':
 2149                     $field = (int)$rule[3];
 2150                     if ($sql_match_operator === '=' && strlen($input) == 0) {
 2151                         $where[] = "NOT EXISTS (SELECT NULL FROM `metadata` WHERE `metadata`.`object_id` = `song`.`id` AND `metadata`.`field` = {$field})";
 2152                     } else {
 2153                         $parsedInput = is_numeric($input) ? $input : '"' . $input . '"';
 2154                         if (!array_key_exists($field, $metadata)) {
 2155                             $metadata[$field] = array();
 2156                         }
 2157                         $metadata[$field][] = "`metadata`.`data` $sql_match_operator $parsedInput";
 2158                     }
 2159                     break;
 2160                 default:
 2161                     break;
 2162             } // switch on ruletype song
 2163         } // foreach over rules
 2164 
 2165         // translate metadata queries into sql for each field
 2166         foreach ($metadata as $metadata_field => $metadata_queries) {
 2167             $metadata_sql = "EXISTS (SELECT NULL FROM `metadata` WHERE `metadata`.`object_id` = `song`.`id` AND `metadata`.`field` = {$metadata_field} AND (";
 2168             $metadata_sql .= implode(" $sql_logic_operator ", $metadata_queries);
 2169             $where[] = $metadata_sql . '))';
 2170         }
 2171 
 2172         $join['catalog_map'] = $catalog_filter;
 2173         $join['catalog']     = $catalog_disable || $catalog_filter;
 2174 
 2175         $where_sql = implode(" $sql_logic_operator ", $where);
 2176 
 2177         // now that we know which things we want to JOIN...
 2178         if ($join['song_data']) {
 2179             $table['song_data'] = "LEFT JOIN `song_data` ON `song`.`id`=`song_data`.`song_id`";
 2180         }
 2181         if ($join['playlist_data']) {
 2182             $table['playlist_data'] = "LEFT JOIN `playlist_data` ON `song`.`id`=`playlist_data`.`object_id` AND `playlist_data`.`object_type`='song'";
 2183             if ($join['playlist']) {
 2184                 $table['playlist'] = "LEFT JOIN `playlist` ON `playlist_data`.`playlist`=`playlist`.`id`";
 2185             }
 2186         }
 2187         if ($join['catalog']) {
 2188             $table['1_catalog'] = "LEFT JOIN `catalog` AS `catalog_se` ON `catalog_se`.`id`=`song`.`catalog`";
 2189             if (!empty($where_sql)) {
 2190                 $where_sql .= " AND `catalog_se`.`enabled` = '1' AND `song`.`enabled` = 1";
 2191             } else {
 2192                 $where_sql .= " `catalog_se`.`enabled` = '1' AND `song`.`enabled` = 1";
 2193             }
 2194         }
 2195         if ($join['catalog_map']) {
 2196             $table['2_catalog_map'] = "LEFT JOIN `catalog_map` AS `catalog_map_song` ON `catalog_map_song`.`object_id`=`song`.`id` AND `catalog_map_song`.`object_type` = 'song' AND `catalog_map_song`.`catalog_id` = `catalog_se`.`id`";
 2197             if (!empty($where_sql)) {
 2198                 $where_sql .= " AND `catalog_se`.`filter_user` IN (0, $user_id)";
 2199             } else {
 2200                 $where_sql .= " `catalog_se`.`filter_user` IN (0, $user_id)";
 2201             }
 2202         }
 2203         if ($join['album']) {
 2204             $table['album'] = "LEFT JOIN `album` ON `song`.`album`=`album`.`id`";
 2205         }
 2206         if ($join['artist']) {
 2207             $table['artist'] = "LEFT JOIN `artist` ON `song`.`artist`=`artist`.`id`";
 2208         }
 2209         ksort($table);
 2210         $table_sql  = implode(' ', $table);
 2211         $group_sql  = implode(',', $group);
 2212         $having_sql = implode(" $sql_logic_operator ", $having);
 2213 
 2214         return array(
 2215             'base' => 'SELECT DISTINCT(`song`.`id`), `song`.`file` FROM `song`',
 2216             'join' => $join,
 2217             'where' => $where,
 2218             'where_sql' => $where_sql,
 2219             'table' => $table,
 2220             'table_sql' => $table_sql,
 2221             'group_sql' => $group_sql,
 2222             'having_sql' => $having_sql
 2223         );
 2224     }
 2225 
 2226     /**
 2227      * video_to_sql
 2228      *
 2229      * Handles the generation of the SQL for video searches.
 2230      * @return array
 2231      */
 2232     private function video_to_sql()
 2233     {
 2234         $sql_logic_operator = $this->logic_operator;
 2235         $user_id            = $this->search_user->id;
 2236         $catalog_disable    = AmpConfig::get('catalog_disable');
 2237         $catalog_filter     = AmpConfig::get('catalog_filter');
 2238 
 2239         $where  = array();
 2240         $table  = array();
 2241         $join   = array();
 2242         $group  = array();
 2243         $having = array();
 2244 
 2245         foreach ($this->rules as $rule) {
 2246             $type     = $this->name_to_basetype($rule[0]);
 2247             $operator = array();
 2248             if (!$type) {
 2249                 return array();
 2250             }
 2251             foreach ($this->basetypes[$type] as $op) {
 2252                 if ($op['name'] == $rule[1]) {
 2253                     $operator = $op;
 2254                     break;
 2255                 }
 2256             }
 2257             $raw_input          = $this->_mangle_data($rule[2], $type, $operator);
 2258             $input              = filter_var($raw_input, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
 2259             $sql_match_operator = $operator['sql'];
 2260 
 2261             switch ($rule[0]) {
 2262                 case 'file':
 2263                     $where[] = "`video`.`file` $sql_match_operator '$input'";
 2264                     break;
 2265                 default:
 2266                     break;
 2267             } // switch on ruletype
 2268         } // foreach rule
 2269 
 2270         $join['catalog_map'] = $catalog_filter;
 2271         $join['catalog']     = $catalog_disable || $catalog_filter;
 2272 
 2273         $where_sql = implode(" $sql_logic_operator ", $where);
 2274 
 2275         if ($join['catalog']) {
 2276             $table['1_catalog'] = "LEFT JOIN `catalog` AS `catalog_se` ON `catalog_se`.`id`=`video`.`catalog`";
 2277             if (!empty($where_sql)) {
 2278                 $where_sql .= " AND `catalog_se`.`enabled` = '1' AND `video`.`enabled` = 1";
 2279             } else {
 2280                 $where_sql .= " `catalog_se`.`enabled` = '1' AND `video`.`enabled` = 1";
 2281             }
 2282         }
 2283         if ($join['catalog_map']) {
 2284             $table['2_catalog_map'] = "LEFT JOIN `catalog_map` AS `catalog_map_video` ON `catalog_map_video`.`object_id`=`video`.`id` AND `catalog_map_video`.`object_type` = 'video' AND `catalog_map_video`.`catalog_id` = `catalog_se`.`id`";
 2285             if (!empty($where_sql)) {
 2286                 $where_sql .= " AND `catalog_se`.`filter_user` IN (0, $user_id)";
 2287             } else {
 2288                 $where_sql .= " `catalog_se`.`filter_user` IN (0, $user_id)";
 2289             }
 2290         }
 2291         ksort($table);
 2292         $table_sql  = implode(' ', $table);
 2293         $group_sql  = implode(',', $group);
 2294         $having_sql = implode(" $sql_logic_operator ", $having);
 2295 
 2296         return array(
 2297             'base' => 'SELECT DISTINCT(`video`.`id`), `video`.`file` FROM `video`',
 2298             'join' => $join,
 2299             'where' => $where,
 2300             'where_sql' => $where_sql,
 2301             'table' => $table,
 2302             'table_sql' => $table_sql,
 2303             'group_sql' => $group_sql,
 2304             'having_sql' => $having_sql
 2305         );
 2306     }
 2307 
 2308     /**
 2309      * playlist_to_sql
 2310      *
 2311      * Handles the generation of the SQL for playlist searches.
 2312      * @return array
 2313      */
 2314     private function playlist_to_sql()
 2315     {
 2316         $sql_logic_operator = $this->logic_operator;
 2317         $user_id            = $this->search_user->id;
 2318         $catalog_disable    = AmpConfig::get('catalog_disable');
 2319         $catalog_filter     = AmpConfig::get('catalog_filter');
 2320 
 2321         $where  = array();
 2322         $table  = array();
 2323         $join   = array();
 2324         $group  = array();
 2325         $having = array();
 2326 
 2327         foreach ($this->rules as $rule) {
 2328             $type     = $this->name_to_basetype($rule[0]);
 2329             $operator = array();
 2330             if (!$type) {
 2331                 return array();
 2332             }
 2333             foreach ($this->basetypes[$type] as $op) {
 2334                 if ($op['name'] == $rule[1]) {
 2335                     $operator = $op;
 2336                     break;
 2337                 }
 2338             }
 2339             $raw_input          = $this->_mangle_data($rule[2], $type, $operator);
 2340             $input              = filter_var($raw_input, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
 2341             $sql_match_operator = $operator['sql'];
 2342 
 2343             $where[] = "(`playlist`.`type` = 'public' OR `playlist`.`user`=" . $user_id . ")";
 2344 
 2345             switch ($rule[0]) {
 2346                 case 'title':
 2347                 case 'name':
 2348                     $where[] = "`playlist`.`name` $sql_match_operator '$input'";
 2349                     break;
 2350                 default:
 2351                     break;
 2352             } // switch on ruletype
 2353         } // foreach rule
 2354 
 2355         $join['playlist_data'] = true;
 2356         $join['song']          = $join['song'] || $catalog_disable || $catalog_filter;
 2357         $join['catalog']       = $catalog_disable || $catalog_filter;
 2358         $join['catalog_map']   = $catalog_filter;
 2359 
 2360         $where_sql = implode(" $sql_logic_operator ", $where);
 2361 
 2362         if ($join['playlist_data']) {
 2363             $table['0_playlist_data'] = "LEFT JOIN `playlist_data` ON `playlist_data`.`playlist` = `playlist`.`id`";
 2364         }
 2365         if ($join['song']) {
 2366             $table['0_song'] = "LEFT JOIN `song` ON `song`.`id`=`playlist_data`.`object_id`";
 2367             $where_sql .= " AND `playlist_data`.`object_type` = 'song'";
 2368         }
 2369         if ($join['catalog']) {
 2370             $table['1_catalog'] = "LEFT JOIN `catalog` AS `catalog_se` ON `catalog_se`.`id`=`song`.`catalog`";
 2371             if ($catalog_disable) {
 2372                 if (!empty($where_sql)) {
 2373                     $where_sql .= " AND `catalog_se`.`enabled` = '1' AND `song`.`enabled` = 1";
 2374                 } else {
 2375                     $where_sql .= " `catalog_se`.`enabled` = '1' AND `song`.`enabled` = 1";
 2376                 }
 2377             }
 2378         }
 2379         if ($join['catalog_map']) {
 2380             if (!empty($where_sql)) {
 2381                 $where_sql .= " AND `catalog_se`.`filter_user` IN (0, $user_id)";
 2382             } else {
 2383                 $where_sql .= " `catalog_se`.`filter_user` IN (0, $user_id)";
 2384             }
 2385         }
 2386         ksort($table);
 2387         $table_sql  = implode(' ', $table);
 2388         $group_sql  = implode(',', $group);
 2389         $having_sql = implode(" $sql_logic_operator ", $having);
 2390 
 2391         return array(
 2392             'base' => 'SELECT DISTINCT(`playlist`.`id`), `playlist`.`name` FROM `playlist`',
 2393             'join' => $join,
 2394             'where' => $where,
 2395             'where_sql' => $where_sql,
 2396             'table' => $table,
 2397             'table_sql' => $table_sql,
 2398             'group_sql' => $group_sql,
 2399             'having_sql' => $having_sql
 2400         );
 2401     }
 2402 
 2403     /**
 2404      * label_to_sql
 2405      *
 2406      * Handles the generation of the SQL for label searches.
 2407      * @return array
 2408      */
 2409     private function label_to_sql()
 2410     {
 2411         $sql_logic_operator = $this->logic_operator;
 2412         $user_id            = $this->search_user->id;
 2413         $catalog_disable    = AmpConfig::get('catalog_disable');
 2414         $catalog_filter     = AmpConfig::get('catalog_filter');
 2415 
 2416         $where = array();
 2417         $table = array();
 2418         $join  = array();
 2419 
 2420         foreach ($this->rules as $rule) {
 2421             $type     = $this->name_to_basetype($rule[0]);
 2422             $operator = array();
 2423             if (!$type) {
 2424                 return array();
 2425             }
 2426             foreach ($this->basetypes[$type] as $op) {
 2427                 if ($op['name'] == $rule[1]) {
 2428                     $operator = $op;
 2429                     break;
 2430                 }
 2431             }
 2432             $raw_input          = $this->_mangle_data($rule[2], $type, $operator);
 2433             $input              = filter_var($raw_input, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
 2434             $sql_match_operator = $operator['sql'];
 2435 
 2436             switch ($rule[0]) {
 2437                 case 'title':
 2438                 case 'name':
 2439                     $where[] = "`label`.`name` $sql_match_operator '$input'";
 2440                     break;
 2441                 case 'category':
 2442                     $where[] = "`label`.`category` $sql_match_operator '$input'";
 2443                     break;
 2444                 default:
 2445                     break;
 2446             } // switch on ruletype
 2447         } // foreach rule
 2448 
 2449         $join['catalog_map'] = $catalog_filter;
 2450         $join['catalog']     = $catalog_disable || $catalog_filter;
 2451 
 2452         $where_sql = implode(" $sql_logic_operator ", $where);
 2453 
 2454         if ($catalog_disable || $catalog_filter) {
 2455             $table['0_label_asso']  = "LEFT JOIN `label_asso` ON `label_asso`.`label` = `label`.`id`";
 2456             $table['1_artist']      = "LEFT JOIN `artist` ON `label_asso`.`artist` = `artist`.`id`";
 2457             $table['2_catalog_map'] = "LEFT JOIN `catalog_map` AS `catalog_map_artist` ON `catalog_map_artist`.`object_id`=`artist`.`id` AND `catalog_map_artist`.`object_type` = 'artist'";
 2458         }
 2459 
 2460         if ($join['catalog_map']) {
 2461             if (!empty($where_sql)) {
 2462                 $where_sql .= " AND `catalog_map_artist`.`object_type` = 'artist' AND `catalog_se`.`filter_user` IN (0, $user_id)";
 2463             } else {
 2464                 $where_sql .= " `catalog_map_artist`.`object_type` = 'artist' AND `catalog_se`.`filter_user` IN (0, $user_id)";
 2465             }
 2466         }
 2467         if ($join['catalog']) {
 2468             $table['3_catalog'] = "LEFT JOIN `catalog`AS `catalog_se` ON `catalog_map_artist`.`catalog_id` = `catalog_se`.`id`";
 2469             if ($catalog_disable) {
 2470                 if (!empty($where_sql)) {
 2471                     $where_sql .= " AND `catalog_se`.`enabled` = '1'";
 2472                 } else {
 2473                     $where_sql .= " `catalog_se`.`enabled` = '1'";
 2474                 }
 2475             }
 2476         }
 2477         $table_sql = implode(' ', $table);
 2478 
 2479         return array(
 2480             'base' => 'SELECT DISTINCT(`label`.`id`), `label`.`name` FROM `label`',
 2481             'join' => $join,
 2482             'where' => $where,
 2483             'where_sql' => $where_sql,
 2484             'table' => $table,
 2485             'table_sql' => $table_sql,
 2486             'group_sql' => '',
 2487             'having_sql' => ''
 2488         );
 2489     }
 2490 
 2491     /**
 2492        * tag_to_sql
 2493        *
 2494        * Handles the generation of the SQL for tag (genre) searches.
 2495        * @return array
 2496        */
 2497 
 2498     private function tag_to_sql()
 2499     {
 2500         $sql_logic_operator = $this->logic_operator;
 2501         $user_id            = $this->search_user->id;
 2502         $catalog_disable    = AmpConfig::get('catalog_disable');
 2503         $catalog_filter     = AmpConfig::get('catalog_filter');
 2504 
 2505         $where = array();
 2506         $table = array();
 2507         $join  = array();
 2508 
 2509         foreach ($this->rules as $rule) {
 2510             $type     = $this->name_to_basetype($rule[0]);
 2511             $operator = array();
 2512             if (!$type) {
 2513                 return array();
 2514             }
 2515             foreach ($this->basetypes[$type] as $op) {
 2516                 if ($op['name'] == $rule[1]) {
 2517                     $operator = $op;
 2518                     break;
 2519                 }
 2520             }
 2521             $raw_input          = $this->_mangle_data($rule[2], $type, $operator);
 2522             $input              = filter_var($raw_input, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
 2523             $sql_match_operator = $operator['sql'];
 2524 
 2525             switch ($rule[0]) {
 2526                 case 'title':
 2527                 case 'name':
 2528                     $where[] = "`tag`.`name` $sql_match_operator '$input'";
 2529                     break;
 2530                 case 'category':
 2531                     $where[] = "`tag`.`category` $sql_match_operator '$input'";
 2532                     break;
 2533                 default:
 2534                     break;
 2535             } // switch on ruletype
 2536         } // foreach rule
 2537 
 2538         $join['catalog_map'] = $catalog_filter;
 2539         $join['catalog']     = $catalog_disable || $catalog_filter;
 2540 
 2541         $where_sql = implode(" $sql_logic_operator ", $where);
 2542 
 2543         if ($join['catalog']) {
 2544             $table['1_catalog'] = "LEFT JOIN `catalog` AS `catalog_se` ON `catalog_se`.`id`=`song`.`catalog`";
 2545             if (!empty($where_sql)) {
 2546                 $where_sql .= " AND `catalog_se`.`enabled` = '1' AND `song`.`enabled` = 1";
 2547             } else {
 2548                 $where_sql .= " `catalog_se`.`enabled` = '1' AND `song`.`enabled` = 1";
 2549             }
 2550         }
 2551         if ($join['catalog_map']) {
 2552             $table['2_catalog_map'] = "LEFT JOIN `catalog_map` AS `catalog_map_album` ON `catalog_map_album`.`object_id`=`album`.`id` AND `catalog_map_album`.`object_type` = 'album' AND `catalog_map_album`.`catalog_id` = `catalog_se`.`id`";
 2553             if (!empty($where_sql)) {
 2554                 $where_sql .= " AND `catalog_se`.`filter_user` IN (0, $user_id)";
 2555             } else {
 2556                 $where_sql .= " `catalog_se`.`filter_user` IN (0, $user_id)";
 2557             }
 2558         }
 2559 
 2560         return array(
 2561             'base' => 'SELECT DISTINCT(`tag`.`id`) FROM `tag`',
 2562             'join' => $join,
 2563             'where' => $where,
 2564             'where_sql' => $where_sql,
 2565             'table' => $table,
 2566             'table_sql' => '',
 2567             'group_sql' => '',
 2568             'having_sql' => ''
 2569         );
 2570     }
 2571 
 2572     /**
 2573      * user_to_sql
 2574      *
 2575      * Handles the generation of the SQL for user searches.
 2576      * @return array
 2577      */
 2578     private function user_to_sql()
 2579     {
 2580         $sql_logic_operator = $this->logic_operator;
 2581 
 2582         $where = array();
 2583         $table = array();
 2584         $join  = array();
 2585 
 2586         foreach ($this->rules as $rule) {
 2587             $type     = $this->name_to_basetype($rule[0]);
 2588             $operator = array();
 2589             if (!$type) {
 2590                 return array();
 2591             }
 2592             foreach ($this->basetypes[$type] as $op) {
 2593                 if ($op['name'] == $rule[1]) {
 2594                     $operator = $op;
 2595                     break;
 2596                 }
 2597             }
 2598             $raw_input          = $this->_mangle_data($rule[2], $type, $operator);
 2599             $input              = filter_var($raw_input, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
 2600             $sql_match_operator = $operator['sql'];
 2601 
 2602             switch ($rule[0]) {
 2603                 case 'username':
 2604                     $where[] = "`user`.`username` $sql_match_operator '$input'";
 2605                     break;
 2606                 default:
 2607                     break;
 2608             } // switch on ruletype
 2609         } // foreach rule
 2610 
 2611         $where_sql = implode(" $sql_logic_operator ", $where);
 2612         ksort($table);
 2613 
 2614         return array(
 2615             'base' => 'SELECT DISTINCT(`user`.`id`), `user`.`username` FROM `user`',
 2616             'join' => $join,
 2617             'where' => $where,
 2618             'where_sql' => $where_sql,
 2619             'table' => $table,
 2620             'table_sql' => '',
 2621             'group_sql' => '',
 2622             'having_sql' => ''
 2623         );
 2624     }
 2625 
 2626     /**
 2627      * year_search
 2628      *
 2629      * Build search rules for year -> year searching.
 2630      * @param $fromYear
 2631      * @param $toYear
 2632      * @param $size
 2633      * @param $offset
 2634      * @return array
 2635      */
 2636     public static function year_search($fromYear, $toYear, $size, $offset)
 2637     {
 2638         $search           = array();
 2639         $search['limit']  = $size;
 2640         $search['offset'] = $offset;
 2641         $search['type']   = "album";
 2642         $count            = 0;
 2643         if ($fromYear) {
 2644             $search['rule_' . $count . '_input']    = $fromYear;
 2645             $search['rule_' . $count . '_operator'] = 0;
 2646             $search['rule_' . $count . '']          = "original_year";
 2647             ++$count;
 2648         }
 2649         if ($toYear) {
 2650             $search['rule_' . $count . '_input']    = $toYear;
 2651             $search['rule_' . $count . '_operator'] = 1;
 2652             $search['rule_' . $count . '']          = "original_year";
 2653             ++$count;
 2654         }
 2655 
 2656         return $search;
 2657     }
 2658 
 2659     /**
 2660      * @deprecated
 2661      */
 2662     private function getLicenseRepository(): LicenseRepositoryInterface
 2663     {
 2664         global $dic;
 2665 
 2666         return $dic->get(LicenseRepositoryInterface::class);
 2667     }
 2668 
 2669     /**
 2670      * @deprecated inject dependency
 2671      */
 2672     private function getUserRepository(): UserRepositoryInterface
 2673     {
 2674         global $dic;
 2675 
 2676         return $dic->get(UserRepositoryInterface::class);
 2677     }
 2678 }