Context Navigation

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

Last change on this file since 6901 was 6901, checked in by grum, 14 years ago
Version 3.2.0 Add process for update from GPC 3.1.0
File size: 39.4 KB

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

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

Download in other formats: