source: extensions/GrumPluginClasses/classes/GPCRequestBuilder.class.inc.php @ 8166

Last change on this file since 8166 was 7451, checked in by grum, 14 years ago

fix bug:1960
implement feature:1971
File size: 42.5 KB
Line 
1<?php
2/* -----------------------------------------------------------------------------
3  class name: GCPRequestBuilder
4  class version  : 1.1.1
5  plugin version : 3.3.2
6  date           : 2010-09-08
7
8  ------------------------------------------------------------------------------
9  Author     : Grum
10    email    : grum@piwigo.org
11    website  : http://photos.grum.com
12    PWG user : http://forum.phpwebgallery.net/profile.php?id=3706
13
14    << May the Little SpaceFrog be with you ! >>
15  ------------------------------------------------------------------------------
16  *
17  * theses classes provides base functions to manage search pictures in the
18  * database
19  *
20  *
21  * HOW TO USE IT ?
22  * ===============
23  *
24  * when installing the plugin, you have to register the usage of the request
25  * builder
26  *
27  * 1/ Create a RBCallback class
28  *  - extends the GPCSearchCallback class ; the name of the extended class must
29  *    be "RBCallBack%" => replace the "%" by the plugin name
30  *     for example : 'ThePlugin' => 'RBCallBackThePlugin'
31  *
32  * 2/ In the plugin 'maintain.inc.php' file :
33  *  - function plugin_install, add :
34  *       GPCRequestBuilder::register('plugin name', 'path to the RBCallback classe');
35  *          for example : GPCRequestBuilder::register('ThePlugin', $piwigo_path.'plugins/ThePlugin/rbcallback_file_name.php');
36  *
37  *
38  *  - function plugin_uninstall, add :
39  *       GPCRequestBuilder::unregister('plugin name');
40  *          for example : GPCRequestBuilder::unregister('ThePlugin');
41  *
42  * 3/ In the plugin code, put somewhere
43  *     GPCRequestBuilder::loadJSandCSS();
44  *     => this will load specific JS and CSS in the page, by adding url in the
45  *        the header, so try to put this where you're used to prepare the header
46  *
47  * 4/ to display the request builder, just add the returned string in the html
48  *    page
49  *       $stringForTheTemplate=GPCRequestBuilder::displaySearchPage();
50  *
51  *
52  *
53  * HOW DOES THE REQUEST BUILDER WORKS ?
54  * ====================================
55  *
56  * the request builder works in 2 steps :
57  *  - first step  : build a cache, to associate all image id corresponding to
58  *                  the search criterion
59  *                  the cache is an association of request ID/image id
60  *  - second step : use the cache to retrieve images informations
61  *
62  ------------------------------------------------------------------------------
63  :: HISTORY
64
65| release | date       |
66| 1.0.0   | 2010/04/30 | * start coding
67|         |            |
68| 1.1.0   | 2010/09/08 | * add functionnalities to manage complex requests
69|         |            |
70| 1.1.1   | 2010/10/14 | * fix bug on the buildGroupRequest function
71|         |            |   . adding 'DISTINCT' keyword to the SQL requests
72|         |            |
73|         |            | * ajax management moved into the gpc_ajax.php file
74|         |            |
75|         |            | * fix bug on user level access to picture
76|         |            |
77|         |            |
78|         |            |
79|         |            |
80|         |            |
81
82  --------------------------------------------------------------------------- */
83
84if(!defined('GPC_DIR')) define('GPC_DIR' , baseName(dirname(dirname(__FILE__))));
85if(!defined('GPC_PATH')) define('GPC_PATH' , PHPWG_PLUGINS_PATH . GPC_DIR . '/');
86
87include_once('GPCTables.class.inc.php');
88
89/**
90 *
91 * Preparing the temporary table => doCache()
92 * ------------------------------------------
93 * To prepare the cache, the request builder use the following functions :
94 *  - getImageId
95 *  - getFrom
96 *  - getWhere
97 *  - getHaving
98 *
99 * Preparing the cache table => doCache()
100 * --------------------------------------
101 * To prepare the cache, the request builder use the following functions :
102 *  => the getFilter function is used to prepare the filter for the getPage()
103 *     function ; not used to build the cache
104 *
105 * Retrieving the results => getPage()
106 * -----------------------------------
107 * To retrieve the image informations, the request builder uses the following
108 * functions :
109 *  - getSelect
110 *  - getFrom
111 *  - getJoin
112 *  - getFilter (in fact, the result of this function is stored by the doCache()
113 *               function while the cache is builded, but it is used only when
114 *               retrieving the results for multirecord tables)
115 *  - formatData
116 *
117 *
118 * Example
119 * -------
120 * Consider the table "tableA" like this
121 *
122 *  - (*) imageId
123 *  - (*) localId
124 *  -     att1
125 *  -     att2
126 *  The primary key is the 'imageId'+'localId' attributes
127 *    => for one imageId, you can have ZERO or more than ONE record
128 *       when you register the class, you have to set the $multiRecord parameter
129 *       to 'y'
130 *
131 *  gatImageId returns      : "tableA.imageId"
132 *  getSelect returns       : "tableA.att1, tableA.att2"
133 *  getFrom returns         : "tableA"
134 *  getWhere returns        : "tableA.localId= xxxx AND tableA.att1 = zzzz"
135 *  getJoin returns         : "tableA.imageId = pit.id"
136 *  getFilter returns       : "tableA.localId= xxxx"
137 *
138 *  Examples :
139 *   - plugin AdvancedMetadata use getFilter
140 *   - plugin AdvancedSearchEngine, module ASETag use getHaving and getWhere
141 */
142class GPCSearchCallback {
143
144  /**
145   * the getImageId returns the name of the image id attribute
146   * return String
147   */
148  static public function getImageId()
149  {
150    return("");
151  }
152
153  /**
154   * the getSelect function must return an attribute list separated with a comma
155   *
156   * "att1, att2, att3, att4"
157   *
158   * you can specifie tables names and aliases
159   *
160   * "table1.att1 AS alias1, table1.att2 AS alias2, table2.att3 AS alias3"
161   */
162  static public function getSelect($param="")
163  {
164    return("");
165  }
166
167  /**
168   * the getFrom function must return a tables list separated with a comma
169   *
170   * "table1, (table2 left join table3 on table2.key = table3.key), table4"
171   */
172  static public function getFrom($param="")
173  {
174    return("");
175  }
176
177  /**
178   * the getWhere function must return a ready to use where clause
179   *
180   * "(att1 = value0 OR att2 = value1) AND att4 LIKE value2 "
181   */
182  static public function getWhere($param="")
183  {
184    return("");
185  }
186
187
188  /**
189   * the getHaving function return a ready to user HAVING clause
190   *
191   * " FIND_IN_SET(value0, GROUP_CONCAT(DISTINCT att1 SEPARATOR ',')) AND
192   *   FIND_IN_SET(value0, GROUP_CONCAT(DISTINCT att1 SEPARATOR ',')) "
193   *
194   */
195  static public function getHaving($param="")
196  {
197    return("");
198  }
199
200
201  /**
202   * the getJoin function must return a ready to use sql statement allowing to
203   * join the IMAGES table (key : pit.id) with given conditions
204   *
205   * "att3 = pit.id "
206   */
207  static public function getJoin($param="")
208  {
209    return("");
210  }
211
212
213  /**
214   * the getFilter function must return a ready to use where clause
215   * this where clause is used to filter the cache when the used tables can
216   * return more than one result
217   *
218   * the filter can be empty, can be equal to the where clause, or can be equal
219   * to a sub part of the where clause
220   *
221   * in most case, return "" is the best solution
222   *
223   */
224  static public function getFilter($param="")
225  {
226    //return(self::getWhere($param));
227    return("");
228  }
229
230
231  /**
232   * this function is called by the request builder, allowing to display plugin
233   * data with a specific format
234   *
235   * @param Array $attributes : array of ('attribute_name' => 'attribute_value')
236   * @return String : HTML formatted value
237   */
238  static public function formatData($attributes)
239  {
240    return(print_r($attributes, true));
241  }
242
243
244  /**
245   * this function is called by the request builder to make the search page, and
246   * must return the HTML & JS code of the dialogbox used to select criterion
247   *
248   * Notes :
249   *  - the dialogbox is a JS object with a public method 'show'
250   *  - when the method show is called, one parameter is given by the request
251   *    builder ; the parameter is an object defined as this :
252   *      {
253   *        cBuilder: an instance of the criteriaBuilder object used in the page,
254   *      }
255   *
256   *
257   *
258   *
259   * @param String $mode : can take 'admin' or 'public' values, allowing to
260   *                       return different interface if needed
261   * @return String : HTML formatted value
262   */
263  static public function getInterfaceContent($mode='admin')
264  {
265    return("");
266  }
267
268  /**
269   * this function returns the label displayed in the criterion menu
270   *
271   * @return String : label displayed in the criterions menu
272   */
273  static public function getInterfaceLabel()
274  {
275    return(l10n('gpc_rb_unknown_interface'));
276  }
277
278  /**
279   * this function returns the name of the dialog box class
280   *
281   * @return String : name of the dialogbox class
282   */
283  static public function getInterfaceDBClass()
284  {
285    return('');
286  }
287
288
289}
290
291
292//load_language('rbuilder.lang', GPC_PATH);
293
294
295class GPCRequestBuilder {
296
297  static public $pluginName = 'GPCRequestBuilder';
298  static public $version = '1.1.0';
299
300  static private $tables = Array();
301  static protected $tGlobalId=0;
302
303  /**
304   * register a plugin using GPCRequestBuilder
305   *
306   * @param String $pluginName : the plugin name
307   * @param String $fileName : the php filename where the callback function can
308   *                           be found
309   * @return Boolean : true if registering is Ok, otherwise false
310   */
311  static public function register($plugin, $fileName)
312  {
313    $config=Array();
314    if(GPCCore::loadConfig(self::$pluginName, $config))
315    {
316      $config['registered'][$plugin]=Array(
317        'name' => $plugin,
318        'fileName' => $fileName,
319        'date' => date("Y-m-d H:i:s"),
320        'version' => self::$version
321      );
322      return(GPCCore::saveConfig(self::$pluginName, $config));
323    }
324    return(false);
325  }
326
327  /**
328   * unregister a plugin using GPCRequestBuilder
329   *
330   * assume that if the plugin was not registerd before, unregistering returns
331   * a true value
332   *
333   * @param String $pluginName : the plugin name
334   * @return Boolean : true if registering is Ok, otherwise false
335   */
336  static public function unregister($plugin)
337  {
338    $config=Array();
339    if(GPCCore::loadConfig(self::$pluginName, $config))
340    {
341      if(array_key_exists('registered', $config))
342      {
343        if(array_key_exists($plugin, $config['registered']))
344        {
345          unset($config['registered'][$plugin]);
346          return(GPCCore::saveConfig(self::$pluginName, $config));
347        }
348      }
349    }
350    // assume if the plugin was not registered before, unregistering it is OK
351    return(true);
352  }
353
354  /**
355   * @return Array : list of registered plugins
356   */
357  static public function getRegistered()
358  {
359    $config=Array();
360    if(GPCCore::loadConfig(self::$pluginName, $config))
361    {
362      if(array_key_exists('registered', $config))
363      {
364        return($config['registered']);
365      }
366    }
367    return(Array());
368  }
369
370
371  /**
372   * initialise the class
373   *
374   * @param String $prefixeTable : the piwigo prefixe used on tables name
375   * @param String $pluginNameFile : the plugin name used for tables name
376   */
377  static public function init($prefixeTable, $pluginNameFile)
378  {
379    $list=Array('request', 'result_cache', 'temp');
380
381    for($i=0;$i<count($list);$i++)
382    {
383      self::$tables[$list[$i]]=$prefixeTable.$pluginNameFile.'_'.$list[$i];
384    }
385  }
386
387  /**
388   * create the tables needed by RequestBuilder (used during the gpc process install)
389   */
390  static public function createTables()
391  {
392    $tablesDef=array(
393"CREATE TABLE `".self::$tables['request']."` (
394  `id` int(10) unsigned NOT NULL auto_increment,
395  `user_id` int(10) unsigned NOT NULL,
396  `date` datetime NOT NULL,
397  `num_items` int(10) unsigned NOT NULL default '0',
398  `execution_time` float unsigned NOT NULL default '0',
399  `connected_plugin` char(255) NOT NULL,
400  `filter` text NOT NULL,
401  `parameters` text NOT NULL,
402  PRIMARY KEY  (`id`)
403)
404CHARACTER SET utf8 COLLATE utf8_general_ci",
405
406"CREATE TABLE `".self::$tables['result_cache']."` (
407  `id` int(10) unsigned NOT NULL,
408  `image_id` int(10) unsigned NOT NULL,
409  PRIMARY KEY  (`id`,`image_id`)
410)
411CHARACTER SET utf8 COLLATE utf8_general_ci",
412
413"CREATE TABLE `".self::$tables['temp']."` (
414  `requestId` char(30) NOT NULL,
415  `imageId` mediumint(8) unsigned NOT NULL,
416  PRIMARY KEY  (`requestId`,`imageId`)
417)
418CHARACTER SET utf8 COLLATE utf8_general_ci",
419  );
420
421    $tablef= new GPCTables(self::$tables);
422    $tablef->create($tablesDef);
423
424    return(true);
425  }
426
427  /**
428   * update the tables needed by RequestBuilder (used during the gpc process
429   * activation)
430   */
431  static public function updateTables($pluginPreviousRelease)
432  {
433    $tablef=new GPCTables(array(self::$tables['temp']));
434
435    switch($pluginPreviousRelease)
436    {
437      case '03.01.00':
438        $tablesCreate=array();
439        $tablesUpdate=array();
440
441        $tablesCreate[]=
442"CREATE TABLE `".self::$tables['temp']."` (
443  `requestId` char(30) NOT NULL,
444  `imageId` mediumint(8) unsigned NOT NULL,
445  PRIMARY KEY  (`requestId`,`imageId`)
446)
447CHARACTER SET utf8 COLLATE utf8_general_ci";
448
449        $tablesUpdate[self::$tables['request']]['filter']=
450"ADD COLUMN  `filter` text NOT NULL default '' ";
451
452
453
454        $tablef->create($tablesCreate);
455        $tablef->updateTablesFields($tablesUpdate);
456        // no break ! need to be updated like the next release
457        // break;
458      case '03.01.01':
459      case '03.02.00':
460      case '03.02.01':
461      case '03.03.00':
462      case '03.03.01':
463        $tablesUpdate=array();
464
465        $tablesUpdate[self::$tables['request']]['parameters']=
466"ADD COLUMN `parameters` TEXT NOT NULL AFTER `filter`";
467
468        $tablef->updateTablesFields($tablesUpdate);
469        // no break ! need to be updated like the next release
470        // break;
471    }
472
473    return(true);
474  }
475
476  /**
477   * delete the tables needed by RequestBuilder
478   */
479  static public function deleteTables()
480  {
481    $tablef= new GPCTables(self::$tables);
482    $tablef->drop();
483    return(true);
484  }
485
486
487  /**
488   * this function add and handler on the 'loc_end_page_header' to add request
489   * builder JS script & specific CSS on the page
490   *
491   * use it when the displayed page need an access to the criteriaBuilder GUI
492   *
493   */
494  static public function loadJSandCSS()
495  {
496    load_language('rbuilder.lang', GPC_PATH);
497    add_event_handler('loc_begin_page_header', array('GPCRequestBuilder', 'insertJSandCSSFiles'), 9);
498  }
499
500
501  /**
502   * insert JS a CSS file in header
503   *
504   * the function is declared public because it used by the 'loc_begin_page_header'
505   * event callback
506   *
507   * DO NOT USE IT DIRECTLY
508   *
509   */
510  static public function insertJSandCSSFiles()
511  {
512    global $template;
513
514
515    $baseName=basename(dirname(dirname(__FILE__))).'/css/';
516    $template->append('head_elements', '<link href="plugins/'.$baseName.'rbuilder.css" type="text/css" rel="stylesheet"/>');
517    if(defined('IN_ADMIN')) $template->append('head_elements', '<link href="plugins/'.$baseName.'rbuilder_'.$template->get_themeconf('name').'.css" type="text/css" rel="stylesheet"/>');
518
519
520    $baseName=basename(dirname(dirname(__FILE__))).'/js/';
521    GPCCore::addHeaderJS('jquery', 'themes/default/js/jquery.packed.js');
522    GPCCore::addHeaderJS('gpc.interface', 'plugins/'.$baseName.'external/interface/interface.js');
523    GPCCore::addHeaderJS('gpc.inestedsortable', 'plugins/'.$baseName.'external/inestedsortable.pack.js');
524    GPCCore::addHeaderJS('gpc.rbCriteriaBuilder', 'plugins/'.$baseName.'rbCriteriaBuilder.packed.js');
525
526    $template->append('head_elements',
527"<script type=\"text/javascript\">
528  requestBuilderOptions = {
529    textAND:\"".l10n('gpc_rb_textAND')."\",
530    textOR:\"".l10n('gpc_rb_textOR')."\",
531    textNoCriteria:\"".l10n('There is no criteria ! At least, one criteria is required to do search...')."\",
532    textSomethingWrong:\"".l10n('gpc_something_is_wrong_on_the_server_side')."\",
533    textCaddieUpdated:\"".l10n('gpc_the_caddie_is_updated')."\",
534    helpEdit:\"".l10n('gpc_help_edit_criteria')."\",
535    helpDelete:\"".l10n('gpc_help_delete_criteria')."\",
536    helpMove:\"".l10n('gpc_help_move_criteria')."\",
537    helpSwitchCondition:\"".l10n('gpc_help_switch_condition')."\",
538    ajaxUrl:'plugins/GrumPluginClasses/gpc_ajax.php',
539  }
540</script>");
541  }
542
543
544  /**
545   * execute request from the ajax call
546   *
547   * @return String : a ready to use HTML code
548   */
549  static public function executeRequest($ajaxfct)
550  {
551    $result='';
552    switch($ajaxfct)
553    {
554      case 'public.rbuilder.searchExecute':
555        $result=self::doCache();
556        break;
557      case 'public.rbuilder.searchGetPage':
558        $result=self::getPage($_REQUEST['requestNumber'], $_REQUEST['page'], $_REQUEST['numPerPage']);
559        break;
560    }
561    return($result);
562  }
563
564
565  /**
566   * clear the cache table
567   *
568   * @param Boolean $clearAll : if set to true, clear all records without
569   *                            checking timestamp
570   */
571  static public function clearCache($clearAll=false)
572  {
573    if($clearAll)
574    {
575      $sql="DELETE FROM ".self::$tables['result_cache'];
576    }
577    else
578    {
579      $sql="DELETE pgrc FROM ".self::$tables['result_cache']." pgrc
580              LEFT JOIN ".self::$tables['request']." pgr
581                ON pgrc.id = pgr.id
582              WHERE pgr.date < '".date('Y-m-d H:i:s', strtotime("-2 hour"))."'";
583    }
584    pwg_query($sql);
585  }
586
587  /**
588   * prepare the temporary table used for multirecord requests
589   *
590   * @param Integer $requestNumber : id of request
591   * @return String : name of the request key temporary table
592   */
593  static private function prepareTempTable($requestNumber)
594  {
595    //$tableName=call_user_func(Array('RBCallBack'.$plugin, 'getFrom'));
596    //$imageIdName=call_user_func(Array('RBCallBack'.$plugin, 'getImageId'));
597
598    $tempClauses=array();
599    foreach($_REQUEST['extraData'] as $key => $extraData)
600    {
601      $tempClauses[$key]=array(
602        'plugin' => $extraData['owner'],
603        'where' => call_user_func(Array('RBCallBack'.$extraData['owner'], 'getWhere'), $extraData['param']),
604        'having' => call_user_func(Array('RBCallBack'.$extraData['owner'], 'getHaving'), $extraData['param']),
605      );
606    }
607
608    $sql="INSERT INTO ".self::$tables['temp']." ".self::buildGroupRequest($_REQUEST[$_REQUEST['requestName']], $tempClauses, $_REQUEST['operator'], ' AND ', $requestNumber);
609//echo $sql;
610    $result=pwg_query($sql);
611
612    return($requestNumber);
613  }
614
615  /**
616   * clear the temporary table used for multirecord requests
617   *
618   * @param Array $requestNumber : the requestNumber to delete
619   */
620  static private function clearTempTable($requestNumber)
621  {
622    $sql="DELETE FROM ".self::$tables['temp']." WHERE requestId = '$requestNumber';";
623    pwg_query($sql);
624  }
625
626
627  /**
628   * execute a query, and place result in cache
629   *
630   *
631   * @return String : queryNumber;numberOfItems
632   */
633  static private function doCache()
634  {
635    global $user;
636
637    self::clearCache();
638
639    $registeredPlugin=self::getRegistered();
640    $requestNumber=self::getNewRequest($user['id']);
641
642    $build=Array(
643      'SELECT' => 'pit.id',
644      'FROM' => '',
645      'WHERE' => 'pit.level <= '.$user['level'],
646      'GROUPBY' => '',
647      'FILTER' => ''
648    );
649    $tmpBuild=Array(
650      'FROM' => Array(
651        '('.IMAGES_TABLE.' pit LEFT JOIN '.IMAGE_CATEGORY_TABLE.' pic ON pit.id = pic.image_id)' /*JOIN IMAGES & IMAGE_CATEGORY tables*/
652       .'   JOIN '.USER_CACHE_CATEGORIES_TABLE.' pucc ON pucc.cat_id=pic.category_id',  /* IMAGE_CATEGORY & USER_CACHE_CATEGORIES_TABLE tables*/
653
654      ),
655      'WHERE' => Array(),
656      'JOIN' => Array(999=>'pucc.user_id='.$user['id']),
657      'GROUPBY' => Array(
658        'pit.id'
659      ),
660      'FILTER' => Array(),
661    );
662
663    /* build data request for plugins
664     *
665     * Array('Plugin1' =>
666     *          Array(
667     *            criteriaNumber1 => pluginParam1,
668     *            criteriaNumber2 => pluginParam2,
669     *            criteriaNumberN => pluginParamN
670     *          ),
671     *       'Plugin2' =>
672     *          Array(
673     *            criteriaNumber1 => pluginParam1,
674     *            criteriaNumber2 => pluginParam2,
675     *            criteriaNumberN => pluginParamN
676     *          )
677     * )
678     *
679     */
680    $pluginNeeded=Array();
681    $pluginList=Array();
682    $tempName=Array();
683    foreach($_REQUEST['extraData'] as $key => $val)
684    {
685      $pluginNeeded[$val['owner']][$key]=$_REQUEST['extraData'][$key]['param'];
686      $pluginList[$val['owner']]=$val['owner'];
687    }
688
689    /* for each plugin, include the rb callback class file */
690    foreach($pluginList as $val)
691    {
692      if(file_exists($registeredPlugin[$val]['fileName']))
693      {
694        include_once($registeredPlugin[$val]['fileName']);
695      }
696    }
697
698    /* prepare the temp table for the request */
699    self::prepareTempTable($requestNumber);
700    $tmpBuild['FROM'][]=self::$tables['temp'];
701    $tmpBuild['JOIN'][]=self::$tables['temp'].".requestId = '".$requestNumber."'
702                        AND ".self::$tables['temp'].".imageId = pit.id";
703
704    /* for each needed plugin, prepare the filter */
705    foreach($pluginNeeded as $key => $val)
706    {
707      foreach($val as $itemNumber => $param)
708      {
709        $tmpFilter=call_user_func(Array('RBCallBack'.$key, 'getFilter'), $param);
710
711        if(trim($tmpFilter)!="") $tmpBuild['FILTER'][$key][]='('.$tmpFilter.')';
712      }
713    }
714
715
716    /* build FROM
717     *
718     */
719    $build['FROM']=implode(',', $tmpBuild['FROM']);
720    unset($tmpBuild['FROM']);
721
722    /* build WHERE
723     */
724    self::cleanArray($tmpBuild['WHERE']);
725    if(count($tmpBuild['WHERE'])>0)
726    {
727      $build['WHERE']=' ('.self::buildGroup($_REQUEST[$_REQUEST['requestName']], $tmpBuild['WHERE'], $_REQUEST['operator'], ' AND ').') ';
728    }
729    unset($tmpBuild['WHERE']);
730
731
732    /* build FILTER
733     */
734    self::cleanArray($tmpBuild['FILTER']);
735    if(count($tmpBuild['FILTER'])>0)
736    {
737      $tmp=array();
738      foreach($tmpBuild['FILTER'] as $key=>$val)
739      {
740        $tmp[$key]='('.implode(' OR ', $val).')';
741      }
742      $build['FILTER']=' ('.implode(' AND ', $tmp).') ';
743    }
744    unset($tmpBuild['FILTER']);
745
746
747    /* for each plugin, adds jointure with the IMAGE table
748     */
749    self::cleanArray($tmpBuild['JOIN']);
750    if(count($tmpBuild['JOIN'])>0)
751    {
752      if($build['WHERE']!='') $build['WHERE'].=' AND ';
753      $build['WHERE'].=' ('.implode(' AND ', $tmpBuild['JOIN']).') ';
754    }
755    unset($tmpBuild['JOIN']);
756
757    self::cleanArray($tmpBuild['GROUPBY']);
758    if(count($tmpBuild['GROUPBY'])>0)
759    {
760      $build['GROUPBY'].=' '.implode(', ', $tmpBuild['GROUPBY']).' ';
761    }
762    unset($tmpBuild['GROUPBY']);
763
764
765
766    $sql=' FROM '.$build['FROM'];
767    if($build['WHERE']!='')
768    {
769      $sql.=' WHERE '.$build['WHERE'];
770    }
771    if($build['GROUPBY']!='')
772    {
773      $sql.=' GROUP BY '.$build['GROUPBY'];
774    }
775
776    $sql.=" ORDER BY pit.id ";
777
778    $sql="INSERT INTO ".self::$tables['result_cache']." (SELECT DISTINCT $requestNumber, ".$build['SELECT']." $sql)";
779
780//echo $sql;
781    $returned="0;0";
782
783    $result=pwg_query($sql);
784    if($result)
785    {
786      $numberItems=pwg_db_changes($result);
787      self::updateRequest($requestNumber, $numberItems, 0, implode(',', $pluginList), $build['FILTER'], $_REQUEST['extraData']);
788
789      $returned="$requestNumber;".$numberItems;
790    }
791
792    self::clearTempTable($requestNumber);
793
794    return($returned);
795  }
796
797  /**
798   * return a page content. use the cache table to find request result
799   *
800   * @param Integer $requestNumber : the request number (from cache table)
801   * @param Integer $pageNumber : the page to be returned
802   * @param Integer $numPerPage : the number of items returned on a page
803   * @param String $mode : if mode = 'count', the function returns the number of
804   *                       rows ; otherwise, returns rows in a html string
805   * @return String : formatted HTML code
806   */
807  static private function getPage($requestNumber, $pageNumber, $numPerPage)
808  {
809    global $conf, $user;
810    $request=self::getRequest($requestNumber);
811
812    if($request===false)
813    {
814      return("KO");
815    }
816
817    $limitFrom=$numPerPage*($pageNumber-1);
818
819    $pluginNeeded=explode(',', $request['connected_plugin']);
820    $registeredPlugin=self::getRegistered();
821
822    $build=Array(
823      'SELECT' => '',
824      'FROM' => '',
825      'WHERE' => '',
826      'GROUPBY' => '',
827    );
828    $tmpBuild=Array(
829      'SELECT' => Array(
830        'RB_PIT' => "pit.id AS imageId, pit.name AS imageName, pit.path AS imagePath", // from the piwigo's image table
831        'RB_PIC' => "GROUP_CONCAT(DISTINCT pic.category_id SEPARATOR ',') AS imageCategoriesId",     // from the piwigo's image_category table
832        'RB_PCT' => "GROUP_CONCAT(DISTINCT CASE WHEN pct.name IS NULL THEN '' ELSE pct.name END SEPARATOR '#sep#') AS imageCategoriesNames,
833                     GROUP_CONCAT(DISTINCT CASE WHEN pct.permalink IS NULL THEN '' ELSE pct.permalink END SEPARATOR '#sep#') AS imageCategoriesPLink,
834                     GROUP_CONCAT(DISTINCT CASE WHEN pct.dir IS NULL THEN 'V' ELSE 'P' END) AS imageCategoriesDir",   //from the piwigo's categories table
835      ),
836      'FROM' => Array(
837        // join rb result_cache table with piwigo's images table, joined with the piwigo's image_category table, joined with the categories table
838        'RB' => "(((".self::$tables['result_cache']." pgrc
839                  RIGHT JOIN ".IMAGES_TABLE." pit
840                  ON pgrc.image_id = pit.id)
841                    RIGHT JOIN ".IMAGE_CATEGORY_TABLE." pic
842                    ON pit.id = pic.image_id)
843                       RIGHT JOIN ".CATEGORIES_TABLE." pct
844                       ON pct.id = pic.category_id)
845                          RIGHT JOIN ".USER_CACHE_CATEGORIES_TABLE." pucc
846                          ON pucc.cat_id = pic.category_id",
847      ),
848      'WHERE' => Array(
849        'RB' => "pgrc.id=".$requestNumber." AND pucc.user_id=".$user['id'],
850        ),
851      'JOIN' => Array(),
852      'GROUPBY' => Array(
853        'RB' => "pit.id"
854      )
855    );
856
857
858    $extraData=array();
859    foreach($request['parameters'] as $data)
860    {
861      $extraData[$data['owner']]=$data['param'];
862    }
863
864    /* for each needed plugin :
865     *  - include the file
866     *  - call the static public function getFrom, getJoin, getSelect
867     */
868    foreach($pluginNeeded as $key => $val)
869    {
870      if(array_key_exists($val, $registeredPlugin))
871      {
872        if(file_exists($registeredPlugin[$val]['fileName']))
873        {
874          include_once($registeredPlugin[$val]['fileName']);
875
876          $tmp=explode(',', call_user_func(Array('RBCallBack'.$val, 'getSelect'), $extraData[$val]));
877          foreach($tmp as $key2=>$val2)
878          {
879            $tmp[$key2]=self::groupConcatAlias($val2, '#sep#');
880          }
881          $tmpBuild['SELECT'][$val]=implode(',', $tmp);
882          $tmpBuild['FROM'][$val]=call_user_func(Array('RBCallBack'.$val, 'getFrom'), $extraData[$val]);
883          $tmpBuild['JOIN'][$val]=call_user_func(Array('RBCallBack'.$val, 'getJoin'), $extraData[$val]);
884        }
885      }
886    }
887
888    /* build SELECT
889     *
890     */
891    $build['SELECT']=implode(',', $tmpBuild['SELECT']);
892
893    /* build FROM
894     *
895     */
896    $build['FROM']=implode(',', $tmpBuild['FROM']);
897    unset($tmpBuild['FROM']);
898
899
900    /* build WHERE
901     */
902    if($request['filter']!='') $tmpBuild['WHERE'][]=$request['filter'];
903    $build['WHERE']=implode(' AND ', $tmpBuild['WHERE']);
904    unset($tmpBuild['WHERE']);
905
906    /* for each plugin, adds jointure with the IMAGE table
907     */
908    self::cleanArray($tmpBuild['JOIN']);
909    if(count($tmpBuild['JOIN'])>0)
910    {
911      $build['WHERE'].=' AND ('.implode(' AND ', $tmpBuild['JOIN']).') ';
912    }
913    unset($tmpBuild['JOIN']);
914
915    self::cleanArray($tmpBuild['GROUPBY']);
916    if(count($tmpBuild['GROUPBY'])>0)
917    {
918      $build['GROUPBY'].=' '.implode(', ', $tmpBuild['GROUPBY']).' ';
919    }
920    unset($tmpBuild['GROUPBY']);
921
922
923    $imagesList=Array();
924
925    $sql='SELECT DISTINCT '.$build['SELECT']
926        .' FROM '.$build['FROM']
927        .' WHERE '.$build['WHERE']
928        .' GROUP BY '.$build['GROUPBY'];
929
930    $sql.=' ORDER BY pit.id '
931         .' LIMIT '.$limitFrom.', '.$numPerPage;
932
933//echo $sql;
934    $result=pwg_query($sql);
935    if($result)
936    {
937      while($row=pwg_db_fetch_assoc($result))
938      {
939        // affect standard datas
940        $datas['imageThumbnail']=dirname($row['imagePath'])."/".$conf['dir_thumbnail']."/".$conf['prefix_thumbnail'].basename($row['imagePath']);
941        $datas['imageId']=$row['imageId'];
942        $datas['imagePath']=$row['imagePath'];
943        $datas['imageName']=$row['imageName'];
944
945        $datas['imageCategoriesId']=explode(',', $row['imageCategoriesId']);
946        $datas['imageCategoriesNames']=explode('#sep#', $row['imageCategoriesNames']);
947        $datas['imageCategoriesPLink']=explode('#sep#', $row['imageCategoriesPLink']);
948        $datas['imageCategoriesDir']=explode(',', $row['imageCategoriesDir']);
949
950        $datas['imageCategories']=Array();
951        for($i=0;$i<count($datas['imageCategoriesId']);$i++)
952        {
953          $datas['imageCategories'][]=array(
954            'id' => $datas['imageCategoriesId'][$i],
955            'name' => $datas['imageCategoriesNames'][$i],
956            'dirType' => $datas['imageCategoriesDir'][$i],
957            'pLinks' => $datas['imageCategoriesPLink'][$i],
958            'link'=> make_picture_url(
959                        array(
960                          'image_id' => $datas['imageId'],
961                          'category' => array
962                            (
963                              'id' => $datas['imageCategoriesId'][$i],
964                              'name' => $datas['imageCategoriesNames'][$i],
965                              'permalink' => $datas['imageCategoriesPLink'][$i]
966                            )
967                        )
968                      )
969          );
970        }
971
972        /* affect datas for each plugin
973         *
974         * each plugin have to format the data in an HTML code
975         *
976         * so, for each plugin :
977         *  - look the attributes given in the SELECT clause
978         *  - for each attributes, associate the returned value of the record
979         *  - affect in datas an index equals to the plugin pluginName, with returned HTML code ; HTML code is get from a formatData function
980         *
981         * Example :
982         *  plugin ColorStart provide 2 attributes 'csColors' and 'csColorsPct'
983         *
984         *  we affect to the $attributes var :
985         *  $attributes['csColors'] = $row['csColors'];
986         *  $attributes['csColorsPct'] = $row['csColorsPct'];
987         *
988         *  call the ColorStat RB callback formatData with the $attributes => the function return a HTML code ready to use in the template
989         *
990         *  affect $datas['ColorStat'] = $the_returned_html_code;
991         *
992         *
993         */
994        foreach($tmpBuild['SELECT'] as $key => $val)
995        {
996          if($key!='RB_PIT' && $key!='RB_PIC' && $key!='RB_PCT')
997          {
998            $tmp=explode(',', $val);
999
1000            $attributes=Array();
1001
1002            foreach($tmp as $key2 => $val2)
1003            {
1004              $name=self::getAttribute($val2);
1005              $attributes[$name]=$row[$name];
1006            }
1007
1008            $datas['plugin'][$key]=call_user_func(Array('RBCallBack'.$key, 'formatData'), $attributes);
1009
1010            unset($tmp);
1011            unset($attributes);
1012          }
1013        }
1014        $imagesList[]=$datas;
1015        unset($datas);
1016      }
1017    }
1018
1019    return(self::toHtml($imagesList));
1020    //return("get page : $requestNumber, $pageNumber, $numPerPage<br>$debug<br>$sql");
1021  }
1022
1023  /**
1024   * remove all empty value from an array
1025   * @param Array a$array : the array to clean
1026   */
1027  static private function cleanArray(&$array)
1028  {
1029    foreach($array as $key => $val)
1030    {
1031      if(is_array($val))
1032      {
1033        self::cleanArray($val);
1034        if(count($val)==0) unset($array[$key]);
1035      }
1036      elseif(trim($val)=='') unset($array[$key]);
1037    }
1038  }
1039
1040  /**
1041   * returns the alias for an attribute
1042   *
1043   *  item1                          => returns item1
1044   *  table1.item1                   => returns item1
1045   *  table1.item1 AS alias1         => returns alias1
1046   *  item1 AS alias1                => returns alias1
1047   *  GROUP_CONCAT( .... ) AS alias1 => returns alias1
1048   *
1049   * @param String $var : value to examine
1050   * @return String : the attribute name
1051   */
1052  static private function getAttribute($val)
1053  {
1054    preg_match('/(?:GROUP_CONCAT\(.*\)|(?:[A-Z0-9_]*)\.)?([A-Z0-9_]*)(?:\s+AS\s+([A-Z0-9_]*))?/i', trim($val), $result);
1055    if(array_key_exists(2, $result))
1056    {
1057      return($result[2]);
1058    }
1059    elseif(array_key_exists(1, $result))
1060    {
1061      return($result[1]);
1062    }
1063    else
1064    {
1065      return($val);
1066    }
1067  }
1068
1069
1070  /**
1071   * returns a a sql statement GROUP_CONCAT for an alias
1072   *
1073   *  item1                  => returns GROUP_CONCAT(item1 SEPARATOR $sep) AS item1
1074   *  table1.item1           => returns GROUP_CONCAT(table1.item1 SEPARATOR $sep) AS item1
1075   *  table1.item1 AS alias1 => returns GROUP_CONCAT(table1.item1 SEPARATOR $sep) AS alias1
1076   *  item1 AS alias1        => returns GROUP_CONCAT(item1 SEPARATOR $sep) AS alias1
1077   *
1078   * @param String $val : value to examine
1079   * @param String $sep : the separator
1080   * @return String : the attribute name
1081   */
1082  static private function groupConcatAlias($val, $sep=',')
1083  {
1084    /*
1085     * table1.item1 AS alias1
1086     *
1087     * $result[3] = alias1
1088     * $result[2] = item1
1089     * $result[1] = table1.item1
1090     */
1091    preg_match('/((?:(?:[A-Z0-9_]*)\.)?([A-Z0-9_]*))(?:\s+AS\s+([A-Z0-9_]*))?/i', trim($val), $result);
1092    if(array_key_exists(3, $result))
1093    {
1094      return("GROUP_CONCAT(DISTINCT ".$result[1]." SEPARATOR '$sep') AS ".$result[3]);
1095    }
1096    elseif(array_key_exists(2, $result))
1097    {
1098      return("GROUP_CONCAT(DISTINCT ".$result[1]." SEPARATOR '$sep') AS ".$result[2]);
1099    }
1100    else
1101    {
1102      return("GROUP_CONCAT(DISTINCT $val SEPARATOR '$sep') AS ".$val);
1103    }
1104  }
1105
1106
1107  /**
1108   * get a new request number and create it in the request table
1109   *
1110   * @param Integer $userId : id of the user
1111   * @return Integer : the new request number, -1 if something wrong appened
1112   */
1113  static private function getNewRequest($userId)
1114  {
1115    $sql="INSERT INTO ".self::$tables['request']." VALUES('', '$userId', '".date('Y-m-d H:i:s')."', 0, 0, '', '', '')";
1116    $result=pwg_query($sql);
1117    if($result)
1118    {
1119      return(pwg_db_insert_id());
1120    }
1121    return(-1);
1122  }
1123
1124  /**
1125   * update request properties
1126   *
1127   * @param Integer $request_id : the id of request to update
1128   * @param Integer $numItems : number of items found in the request
1129   * @param Float $executionTime : time in second to execute the request
1130   * @param String $pluginList : list of used plugins
1131   * @param String $parameters : parameters given for the request
1132   * @return Boolean : true if request was updated, otherwise false
1133   */
1134  static private function updateRequest($requestId, $numItems, $executionTime, $pluginList, $additionalFilter, $parameters)
1135  {
1136    $sql="UPDATE ".self::$tables['request']."
1137            SET num_items = $numItems,
1138                execution_time = $executionTime,
1139                connected_plugin = '$pluginList',
1140                filter = '".mysql_escape_string($additionalFilter)."',
1141                parameters = '".serialize($parameters)."'
1142            WHERE id = $requestId";
1143    $result=pwg_query($sql);
1144    if($result)
1145    {
1146      return(true);
1147    }
1148    return(false);
1149  }
1150
1151  /**
1152   * returns request properties
1153   *
1154   * @param Integer $request_id : the id of request to update
1155   * @return Array : properties for request, false if request doesn't exist
1156   */
1157  static private function getRequest($requestId)
1158  {
1159    $returned=false;
1160    $sql="SELECT user_id, date, num_items, execution_time, connected_plugin, filter, parameters
1161          FROM ".self::$tables['request']."
1162          WHERE id = $requestId";
1163    $result=pwg_query($sql);
1164    if($result)
1165    {
1166      while($row=pwg_db_fetch_assoc($result))
1167      {
1168        if($row['parameters']!='') $row['parameters']=unserialize($row['parameters']);
1169        $returned=$row;
1170      }
1171    }
1172    return($returned);
1173  }
1174
1175
1176  /**
1177   * internal function used by the executeRequest function for single record
1178   * requests
1179   *
1180   * this function is called recursively
1181   *
1182   * @param Array $groupContent :
1183   * @param Array $items :
1184   * @return String : a where clause
1185   */
1186  static private function buildGroup($groupContent, $items, $groups, $operator)
1187  {
1188    $returned=Array();
1189    foreach($groupContent as $key => $val)
1190    {
1191      if(strpos($val['id'], 'iCbGroup')!==false)
1192      {
1193        preg_match('/[0-9]*$/i', $val['id'], $groupNumber);
1194        $returned[]=self::buildGroup($val['children'], $items, $groups, $groups[$groupNumber[0]]);
1195      }
1196      else
1197      {
1198        preg_match('/[0-9]*$/i', $val['id'], $itemNumber);
1199        $returned[]=" (".$items[$itemNumber[0]].") ";
1200      }
1201    }
1202    return('('.implode($operator, $returned).')');
1203  }
1204
1205
1206  /**
1207   * internal function used by the executeRequest function for multi records
1208   * requests
1209   *
1210   * this function is called recursively
1211   *
1212   * @param Array $groupContent :
1213   * @param Array $clausesItems : array with 'where' and 'having' conditions (and 'plugin' for the plugin)
1214   * @param Array $groups : operators of each group
1215   * @param String $operator : 'OR' or 'AND', according with the current group operator
1216   * @param String $requestNumber : the request number
1217   * @return String : part of a SQL request
1218   */
1219  static private function buildGroupRequest($groupContent, $clausesItems, $groups, $operator, $requestNumber)
1220  {
1221    $returnedS='';
1222    $returned=Array();
1223    foreach($groupContent as $key => $val)
1224    {
1225      if(strpos($val['id'], 'iCbGroup')!==false)
1226      {
1227        preg_match('/[0-9]*$/i', $val['id'], $groupNumber);
1228
1229        $groupValue=self::buildGroupRequest($val['children'], $clausesItems, $groups, $groups[$groupNumber[0]], $requestNumber);
1230
1231        if($groupValue!='')
1232          $returned[]=array(
1233            'mode'  => 'group',
1234            'value' => $groupValue
1235          );
1236      }
1237      else
1238      {
1239        preg_match('/[0-9]*$/i', $val['id'], $itemNumber);
1240
1241        $returned[]=array(
1242          'mode'  => 'item',
1243          'plugin' => $clausesItems[$itemNumber[0]]['plugin'],
1244          'valueWhere' => ($clausesItems[$itemNumber[0]]['where']!='')?" (".$clausesItems[$itemNumber[0]]['where'].") ":'',
1245          'valueHaving' => ($clausesItems[$itemNumber[0]]['having'])?" (".$clausesItems[$itemNumber[0]]['having'].") ":'',
1246        );
1247      }
1248    }
1249
1250    if(count($returned)>0)
1251    {
1252      if(strtolower(trim($operator))=='and')
1253      {
1254        $tId=0;
1255        foreach($returned as $key=>$val)
1256        {
1257          if($tId>0) $returnedS.=" JOIN ";
1258
1259          if($val['mode']=='item')
1260          {
1261            $returnedS.="(SELECT DISTINCT ".call_user_func(Array('RBCallBack'.$val['plugin'], 'getImageId'))." AS imageId
1262                          FROM ".call_user_func(Array('RBCallBack'.$val['plugin'], 'getFrom'));
1263            if($val['valueWhere']!='') $returnedS.=" WHERE ".$val['valueWhere'];
1264            if($val['valueHaving']!='')
1265              $returnedS.=" GROUP BY imageId
1266                            HAVING ".$val['valueHaving'];
1267            $returnedS.=") t".self::$tGlobalId." ";
1268          }
1269          else
1270          {
1271            $returnedS.="(".$val['value'].") t".self::$tGlobalId." ";
1272          }
1273
1274          if($tId>0) $returnedS.=" ON t".(self::$tGlobalId-1).".imageId = t".self::$tGlobalId.".imageId ";
1275          $tId++;
1276          self::$tGlobalId++;
1277        }
1278        $returnedS="SELECT DISTINCT '$requestNumber', t".(self::$tGlobalId-$tId).".imageId FROM ".$returnedS;
1279      }
1280      else
1281      {
1282        foreach($returned as $key=>$val)
1283        {
1284          if($returnedS!='') $returnedS.=" UNION DISTINCT ";
1285
1286          if($val['mode']=='item')
1287          {
1288            $returnedS.="SELECT DISTINCT '$requestNumber', t".self::$tGlobalId.".imageId
1289                          FROM (SELECT ".call_user_func(Array('RBCallBack'.$val['plugin'], 'getImageId'))." AS imageId
1290                                FROM ".call_user_func(Array('RBCallBack'.$val['plugin'], 'getFrom'));
1291            if($val['valueWhere']!='') $returnedS.=" WHERE ".$val['valueWhere'];
1292            if($val['valueHaving']!='')
1293              $returnedS.=" GROUP BY imageId
1294                            HAVING ".$val['valueHaving'];
1295            $returnedS.=") t".self::$tGlobalId." ";
1296          }
1297          else
1298          {
1299            $returnedS.="SELECT DISTINCT '$requestNumber', t".self::$tGlobalId.".imageId FROM (".$val['value'].") t".self::$tGlobalId;
1300          }
1301
1302          self::$tGlobalId++;
1303        }
1304      }
1305    }
1306
1307    return($returnedS);
1308  }
1309
1310
1311  /**
1312   * convert a list of images to HTML
1313   *
1314   * @param Array $imagesList : list of images id & associated datas
1315   * @return String : list formatted into HTML code
1316   */
1317  static protected function toHtml($imagesList)
1318  {
1319    global $template;
1320
1321    $template->set_filename('result_items',
1322                dirname(dirname(__FILE__)).'/templates/GPCRequestBuilder_result.tpl');
1323
1324
1325
1326    $template->assign('datas', $imagesList);
1327
1328    return($template->parse('result_items', true));
1329  }
1330
1331
1332  /**
1333   * returns allowed (or not allowed) categories for a user
1334   *
1335   * used the USER_CACHE_TABLE if possible
1336   *
1337   * @param Integer $userId : a valid user Id
1338   * @return String : IN(...), NOT IN(...) or nothing if there is no restriction
1339   *                  for the user
1340   */
1341  public function getUserCategories($userId)
1342  {
1343/*
1344    $returned='';
1345    if($user['forbidden_categories']!='')
1346    {
1347      $returned=Array(
1348        'JOIN' => 'AND ('.IMAGE_CATEGORY.'.category_id NOT IN ('.$user['forbidden_categories'].') ) ',
1349        'FROM' => IMAGE_CATEGORY
1350      );
1351
1352
1353    }
1354    *
1355    *
1356    */
1357  }
1358
1359
1360  /**
1361   * display search page
1362   *
1363   * @param Array $filter : an array of string ; each item is the name of a
1364   *                        registered plugin
1365   *                        if no parameters are given, no filter is applied
1366   *                        otherwise only plugin wich name is given are
1367   *                        accessible
1368   */
1369  static public function displaySearchPage($filter=array())
1370  {
1371    global $template, $lang;
1372
1373    if(is_string($filter)) $filter=array($filter);
1374    $filter=array_flip($filter);
1375
1376    $template->set_filename('gpc_search_page',
1377                dirname(dirname(__FILE__)).'/templates/GPCRequestBuilder_search.tpl');
1378
1379    $registeredPlugin=self::getRegistered();
1380    $dialogBox=Array();
1381    foreach($registeredPlugin as $key=>$val)
1382    {
1383      if(array_key_exists($key, $registeredPlugin) and
1384         (count($filter)==0 or array_key_exists($key, $filter)))
1385      {
1386        if(file_exists($registeredPlugin[$key]['fileName']))
1387        {
1388          include_once($registeredPlugin[$key]['fileName']);
1389
1390          $dialogBox[]=Array(
1391            'handle' => $val['name'].'DB',
1392            'dialogBoxClass' => call_user_func(Array('RBCallBack'.$key, 'getInterfaceDBClass')),
1393            'label' => call_user_func(Array('RBCallBack'.$key, 'getInterfaceLabel')),
1394            'content' => call_user_func(Array('RBCallBack'.$key, 'getInterfaceContent')),
1395          );
1396        }
1397      }
1398    }
1399
1400    $datas=Array(
1401      'dialogBox' => $dialogBox,
1402      'themeName' => defined('IN_ADMIN')?$template->get_themeconf('name'):'',
1403    );
1404
1405    $template->assign('datas', $datas);
1406
1407    return($template->parse('gpc_search_page', true));
1408  } //displaySearchPage
1409
1410}
1411
1412
1413?>
Note: See TracBrowser for help on using the repository browser.