Context Navigation

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

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

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

Download in other formats: