Context Navigation

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

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

Note: See TracBrowser for help on using the repository browser.

Download in other formats: