5 * Copyright 2005, Anthony J. Pugliese
6 * Copyright 2005, GForge, LLC
7 * Copyright 2009, Roland Mas
8 * Copyright 2009, Alcatel-Lucent
9 * Copyright 2016, Stéphane-Eymeric Bredthauer - TrivialDev
11 * This file is part of FusionForge. FusionForge is free software;
12 * you can redistribute it and/or modify it under the terms of the
13 * GNU General Public License as published by the Free Software
14 * Foundation; either version 2 of the Licence, or (at your option)
17 * FusionForge is distributed in the hope that it will be useful,
18 * but WITHOUT ANY WARRANTY; without even the implied warranty of
19 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 * GNU General Public License for more details.
22 * You should have received a copy of the GNU General Public License along
23 * with FusionForge; if not, write to the Free Software Foundation, Inc.,
24 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
28 * Standard Alcatel-Lucent disclaimer for contributing to open source
30 * "The Artifact ("Contribution") has not been tested and/or
31 * validated for release as or in products, combinations with products or
32 * other commercial use. Any use of the Contribution is entirely made at
33 * the user's own responsibility and the user can not rely on any features,
34 * functionalities or performances Alcatel-Lucent has attributed to the
37 * THE CONTRIBUTION BY ALCATEL-LUCENT IS PROVIDED AS IS, WITHOUT WARRANTY
38 * OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
39 * WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, COMPLIANCE,
40 * NON-INTERFERENCE AND/OR INTERWORKING WITH THE SOFTWARE TO WHICH THE
41 * CONTRIBUTION HAS BEEN MADE, TITLE AND NON-INFRINGEMENT. IN NO EVENT SHALL
42 * ALCATEL-LUCENT BE LIABLE FOR ANY DAMAGES OR OTHER LIABLITY, WHETHER IN
43 * CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
44 * CONTRIBUTION OR THE USE OR OTHER DEALINGS IN THE CONTRIBUTION, WHETHER
45 * TOGETHER WITH THE SOFTWARE TO WHICH THE CONTRIBUTION RELATES OR ON A STAND
49 require_once $gfcommon.'include/FFError.class.php';
51 define('ARTIFACT_QUERY_ASSIGNEE',1);
52 define('ARTIFACT_QUERY_STATE',2);
53 define('ARTIFACT_QUERY_MODDATE',3);
54 define('ARTIFACT_QUERY_EXTRAFIELD',4);
55 define('ARTIFACT_QUERY_SORTCOL',5);
56 define('ARTIFACT_QUERY_SORTORD',6);
57 define('ARTIFACT_QUERY_OPENDATE',7);
58 define('ARTIFACT_QUERY_CLOSEDATE',8);
59 define('ARTIFACT_QUERY_SUMMARY',9);
60 define('ARTIFACT_QUERY_DESCRIPTION',10);
61 define('ARTIFACT_QUERY_FOLLOWUPS',11);
62 define('ARTIFACT_QUERY_SUBMITTER',12);
63 define('ARTIFACT_QUERY_LAST_MODIFIER',13);
65 require_once $gfcommon.'tracker/ArtifactType.class.php';
67 class ArtifactQuery extends FFError {
69 * The artifact type object.
71 * @var object $ArtifactType.
73 var $ArtifactType; //object
76 * Array of artifact data.
78 * @var array $data_array.
83 * Array of query conditions
85 * @var array $element_array.
90 * @param $ArtifactType $ArtifactType c object.
91 * @param array|bool $data
93 function __construct(&$ArtifactType, $data = false) {
94 parent::__construct();
96 // Was ArtifactType legit?
97 if (!$ArtifactType || !is_object($ArtifactType)) {
98 $this->setError('ArtifactQuery: No Valid ArtifactType');
101 // Did ArtifactType have an error?
102 if ($ArtifactType->isError()) {
103 $this->setError('ArtifactQuery: '.$ArtifactType->getErrorMessage());
106 $this->ArtifactType =& $ArtifactType;
109 if (is_array($data)) {
110 $this->data_array =& $data;
112 $this->fetchData($data);
118 * create - create a row in the table that stores a saved query for
121 * @param string $name
124 * @param $moddaterange
127 * @param $extra_fields
128 * @param int $opendaterange
129 * @param int $closedaterange
130 * @param string $summary
131 * @param string $description
132 * @param string $followups
133 * @param int $query_type
134 * @param array $query_options
135 * @param string $submitter Name of the saved query.
136 * @param string $last_modifier
137 * @return bool true on success / false on failure.
139 function create($name,$status,$assignee,$moddaterange,$sort_col,$sort_ord,$extra_fields,$opendaterange=0,$closedaterange=0,
140 $summary='',$description='',$followups='',$query_type=0,$query_options=array(),$submitter='',$last_modifier='') {
145 $this->setMissingParamsError();
148 if (!session_loggedin()) {
149 $this->setError(_('Must Be Logged In'));
153 if ($this->Exist(htmlspecialchars($name))) {
154 $this->setError(_('Query already exists'));
158 if ($query_type>0 && !forge_check_perm ('tracker', $this->ArtifactType->getID(), 'manager')) {
159 $this->setError( _('You must have tracker admin rights to set or update a project level query.'));
163 // Reset the project default query.
164 if ($query_type==2) {
165 $res = db_query_params ('UPDATE artifact_query SET query_type=1 WHERE query_type=2 AND group_artifact_id=$1',
166 array($this->ArtifactType->getID()));
168 $this->setError('Error Updating: '.db_error());
174 $result = db_query_params ('INSERT INTO artifact_query (group_artifact_id,query_name,user_id,query_type) VALUES ($1,$2,$3,$4)',
175 array ($this->ArtifactType->getID(),
176 htmlspecialchars($name),
179 if ($result && db_affected_rows($result) > 0) {
181 $id=db_insertid($result,'artifact_query','artifact_query_id');
183 $this->setError('Error getting id '.db_error());
187 if (!$this->insertElements($id,$status,$submitter,$assignee,$moddaterange,$sort_col,$sort_ord,$extra_fields,$opendaterange,$closedaterange,$summary,$description,$followups,$last_modifier)) {
193 $this->setError(db_error());
198 // Now set up our internal data structures
200 if ($this->fetchData($id)) {
210 * fetchData - re-fetch the data for this ArtifactQuery from the database.
212 * @param int $id ID of saved query.
213 * @return boolean success.
215 function fetchData($id) {
216 $res = db_query_params ('SELECT * FROM artifact_query WHERE artifact_query_id=$1',
219 if (!$res || db_numrows($res) < 1) {
220 $this->setError('ArtifactQuery: Invalid ArtifactQuery ID'.db_error());
223 $this->data_array = db_fetch_array($res);
224 db_free_result($res);
225 $res = db_query_params ('SELECT * FROM artifact_query_fields WHERE artifact_query_id=$1',
227 unset($this->element_array);
228 while ($arr = db_fetch_array($res)) {
230 // Some things may have been saved as comma-separated items
232 if (strstr($arr['query_field_values'],',')) {
233 $arr['query_field_values']=explode(',',$arr['query_field_values']);
235 $this->element_array[$arr['query_field_type']][$arr['query_field_id']]=$arr['query_field_values'];
241 * getArtifactType - get the ArtifactType Object this ArtifactExtraField is associated with.
243 * @return object ArtifactType.
245 function &getArtifactType() {
246 return $this->ArtifactType;
250 * insertElements - ???
256 * @param $moddaterange
259 * @param $extra_fields
260 * @param $opendaterange
261 * @param $closedaterange
262 * @param string $summary
263 * @param string $description
264 * @param $last_modifier
266 * @return boolean True/false on success or not.
268 function insertElements($id,$status,$submitter,$assignee,$moddaterange,$sort_col,$sort_ord,$extra_fields,$opendaterange,$closedaterange,$summary,$description,$followups,$last_modifier) {
269 $res = db_query_params ('DELETE FROM artifact_query_fields WHERE artifact_query_id=$1',
272 $this->setError('Deleting Old Elements: '.db_error());
276 $status = intval($status);
277 $res = db_query_params ('INSERT INTO artifact_query_fields (artifact_query_id,query_field_type,query_field_id,query_field_values) VALUES ($1,$2,0,$3)',
279 ARTIFACT_QUERY_STATE,
282 $this->setError('Setting Status: '.db_error());
286 if (is_array($submitter)) {
287 for($e=0; $e<count($submitter); $e++) {
288 $submitter[$e]=intval($submitter[$e]);
290 $submitter=implode(',',$submitter);
292 $submitter = intval($submitter);
295 if (is_array($assignee)) {
296 for($e=0; $e<count($assignee); $e++) {
297 $assignee[$e]=intval($assignee[$e]);
299 $assignee=implode(',',$assignee);
301 $assignee = intval($assignee);
304 if (is_array($last_modifier)) {
305 for($e=0; $e<count($last_modifier); $e++) {
306 $last_modifier[$e]=intval($last_modifier[$e]);
308 $last_modifier=implode(',',$last_modifier);
310 $last_modifier = intval($last_modifier);
313 if (preg_match("/[^[:alnum:]_]/", $sort_col)) {
314 $this->setError('ArtifactQuery: not valid sort_col');
318 if (preg_match("/[^[:alnum:]_]/", $sort_ord)) {
319 $this->setError('ArtifactQuery: not valid sort_ord');
323 //CSV LIST OF SUBMITTERS
325 $res = db_query_params ('INSERT INTO artifact_query_fields
326 (artifact_query_id,query_field_type,query_field_id,query_field_values)
327 VALUES ($1,$2,0,$3)',
329 ARTIFACT_QUERY_SUBMITTER,
332 $this->setError('Setting Submitter: '.db_error());
337 //CSV LIST OF SUBMITTERS
338 if ($last_modifier) {
339 $res = db_query_params ('INSERT INTO artifact_query_fields
340 (artifact_query_id,query_field_type,query_field_id,query_field_values)
341 VALUES ($1,$2,0,$3)',
343 ARTIFACT_QUERY_LAST_MODIFIER,
346 $this->setError('Setting Last Modifier: '.db_error());
351 //CSV LIST OF ASSIGNEES
353 $res = db_query_params ('INSERT INTO artifact_query_fields
354 (artifact_query_id,query_field_type,query_field_id,query_field_values)
355 VALUES ($1,$2,0,$3)',
357 ARTIFACT_QUERY_ASSIGNEE,
360 $this->setError('Setting Assignee: '.db_error());
365 //MOD DATE RANGE YYYY-MM-DD YYYY-MM-DD format
366 if ($moddaterange && !$this->validateDateRange($moddaterange)) {
367 $this->setError(_('Invalid Last Modified Date Range'));
370 $res = db_query_params ('INSERT INTO artifact_query_fields
371 (artifact_query_id,query_field_type,query_field_id,query_field_values)
372 VALUES ($1,$2,0,$3)',
374 ARTIFACT_QUERY_MODDATE,
377 $this->setError('Setting Last Modified Date Range: '.db_error());
381 //OPEN DATE RANGE YYYY-MM-DD YYYY-MM-DD format
382 if ($opendaterange && !$this->validateDateRange($opendaterange)) {
383 $this->setError(_('Invalid Open Date Range'));
386 $res = db_query_params ('INSERT INTO artifact_query_fields
387 (artifact_query_id,query_field_type,query_field_id,query_field_values)
388 VALUES ($1,$2,0,$3)',
390 ARTIFACT_QUERY_OPENDATE,
393 $this->setError('Setting Open Date Range: '.db_error());
397 //CLOSE DATE RANGE YYYY-MM-DD YYYY-MM-DD format
398 if ($closedaterange && !$this->validateDateRange($closedaterange)) {
399 $this->setError(_('Invalid Close Date Range'));
402 $res = db_query_params ('INSERT INTO artifact_query_fields
403 (artifact_query_id,query_field_type,query_field_id,query_field_values)
404 VALUES ($1,$2,0,$3)',
406 ARTIFACT_QUERY_CLOSEDATE,
409 $this->setError('Setting Close Date Range: '.db_error());
414 $res = db_query_params ('INSERT INTO artifact_query_fields
415 (artifact_query_id,query_field_type,query_field_id,query_field_values)
416 VALUES ($1,$2,0,$3)',
418 ARTIFACT_QUERY_SORTCOL,
421 $this->setError('Setting Sort Col: '.db_error());
424 $res = db_query_params ('INSERT INTO artifact_query_fields
425 (artifact_query_id,query_field_type,query_field_id,query_field_values)
426 VALUES ($1,$2,0,$3)',
428 ARTIFACT_QUERY_SORTORD,
431 $this->setError('Setting Sort Order: '.db_error());
435 // Saving the summary value.
436 $res=db_query_params ('INSERT INTO artifact_query_fields
437 (artifact_query_id,query_field_type,query_field_id,query_field_values)
438 VALUES ($1,$2,$3,$4)',
440 ARTIFACT_QUERY_SUMMARY,
444 $this->setError('Setting Summary: '.db_error());
448 // Saving the description value.
449 $res=db_query_params ('INSERT INTO artifact_query_fields
450 (artifact_query_id,query_field_type,query_field_id,query_field_values)
451 VALUES ($1,$2,$3,$4)',
453 ARTIFACT_QUERY_DESCRIPTION,
457 $this->setError('Setting Description: '.db_error());
461 // Saving the followups value.
462 $res=db_query_params ('INSERT INTO artifact_query_fields
463 (artifact_query_id,query_field_type,query_field_id,query_field_values)
464 VALUES ($1,$2,$3,$4)',
466 ARTIFACT_QUERY_FOLLOWUPS,
470 $this->setError('Setting Followups: '.db_error());
474 if (!$extra_fields) {
475 $extra_fields=array();
478 $keys=array_keys($extra_fields);
479 $vals=array_values($extra_fields);
480 for ($i=0; $i<count($keys); $i++) {
485 // Checkboxes and multi-select may be arrays so store it comma-separated
487 if (is_array($vals[$i])) {
488 for($e=0; $e<count($vals[$i]); $e++) {
489 $vals[$i][$e]=intval($vals[$i][$e]);
491 $vals[$i]=implode(',',$vals[$i]);
494 $aef = new ArtifactExtraField($this->ArtifactType, $keys[$i]);
495 $type = $aef->getType();
496 if ($type == ARTIFACT_EXTRAFIELDTYPE_INTEGER) {
497 if (!preg_match('/^[><= \-\+0-9%]+$/', $vals[$i])) {
498 $this->setError('Invalid Value for Integer type: '. $vals[$i]);
503 $res = db_query_params ('INSERT INTO artifact_query_fields
504 (artifact_query_id,query_field_type,query_field_id,query_field_values)
505 VALUES ($1,$2,$3,$4)',
507 ARTIFACT_QUERY_EXTRAFIELD,
511 $this->setError('Setting values: '.db_error());
519 * getID - get this ArtifactQuery ID.
521 * @return int The id #.
524 return $this->data_array['artifact_query_id'];
528 * getName - get the name.
530 * @return string The name.
533 return $this->data_array['query_name'];
537 * getUserId - get the user_id.
539 * @return string The user_id.
541 function getUserId() {
542 return $this->data_array['user_id'];
546 * getQueryType - get the type of the query
548 * @return string type of query (0: private, 1: project, 2: project&default)
550 function getQueryType() {
551 return $this->data_array['query_type'];
555 * getQueryOptions - get the options of the query
557 * @return array array of all activated options
559 function getQueryOptions() {
560 if (isset($this->data_array['query_options'])) {
561 return explode('|', $this->data_array['query_options']);
568 * getSortCol - the column that you're sorting on
570 * @return string The column name.
572 function getSortCol() {
573 if (!isset($this->element_array))
575 return $this->element_array[ARTIFACT_QUERY_SORTCOL][0];
579 * getSortOrd - ASC or DESC
581 * @return string ASC or DESC
583 function getSortOrd() {
584 if (!isset($this->element_array))
586 return $this->element_array[ARTIFACT_QUERY_SORTORD][0];
590 * getModDateRange - get the range of dates to include in a query
592 * @return string mod date range.
594 function getModDateRange() {
595 if (!isset($this->element_array))
597 if ($this->element_array[ARTIFACT_QUERY_MODDATE][0]) {
598 return $this->element_array[ARTIFACT_QUERY_MODDATE][0];
605 * getOpenDateRange - get the range of dates to include in a query
607 * @return string Open date range.
609 function getOpenDateRange() {
610 if (!isset($this->element_array))
612 if (isset($this->element_array[ARTIFACT_QUERY_OPENDATE][0])) {
613 return $this->element_array[ARTIFACT_QUERY_OPENDATE][0];
620 * getCloseDateRange - get the range of dates to include in a query
622 * @return string Close date range.
624 function getCloseDateRange() {
625 if (!isset($this->element_array))
627 if (isset($this->element_array[ARTIFACT_QUERY_CLOSEDATE][0])) {
628 return $this->element_array[ARTIFACT_QUERY_CLOSEDATE][0];
635 * getSummary - get the summary string to include in a query
637 * @return string Summary string.
639 function getSummary() {
640 if (!isset($this->element_array[ARTIFACT_QUERY_SUMMARY][0])) {
643 return $this->element_array[ARTIFACT_QUERY_SUMMARY][0];
647 * getDescription - get the description string to include in a query
649 * @return string Description string.
651 function getDescription() {
652 if (!isset($this->element_array[ARTIFACT_QUERY_DESCRIPTION][0])) {
655 return $this->element_array[ARTIFACT_QUERY_DESCRIPTION][0];
659 * getFollowups - get the followups string to include in a query
661 * @return string Folowups string.
663 function getFollowups() {
664 if (!isset($this->element_array[ARTIFACT_QUERY_FOLLOWUPS][0])) {
667 return $this->element_array[ARTIFACT_QUERY_FOLLOWUPS][0];
673 * @return string Assignee ID
675 function getAssignee() {
676 if (!isset($this->element_array[ARTIFACT_QUERY_ASSIGNEE]))
678 return $this->element_array[ARTIFACT_QUERY_ASSIGNEE][0];
684 * @return string Submitter ID
686 function getSubmitter() {
687 if (!isset($this->element_array[ARTIFACT_QUERY_SUBMITTER]))
689 return $this->element_array[ARTIFACT_QUERY_SUBMITTER][0];
695 * @return string Last Modifier ID
697 function getLastModifier() {
698 if (!isset($this->element_array[ARTIFACT_QUERY_LAST_MODIFIER]))
700 return $this->element_array[ARTIFACT_QUERY_LAST_MODIFIER][0];
706 * @return string Status ID
708 function getStatus() {
709 if (!isset($this->element_array))
711 return $this->element_array[ARTIFACT_QUERY_STATE][0];
715 * getExtraFields - complex multi-dimensional array of extra field IDs/Vals
717 * @return array Complex Array
719 function getExtraFields() {
720 if (!isset($this->element_array))
722 if (! isset ($this->element_array[ARTIFACT_QUERY_EXTRAFIELD])) {
723 $this->element_array[ARTIFACT_QUERY_EXTRAFIELD] = array () ;
725 return $this->element_array[ARTIFACT_QUERY_EXTRAFIELD];
729 * validateDateRange - validate a date range in this format '1999-05-01 1999-06-01'.
731 * @param string $daterange A range of two dates (1999-05-01 1999-06-01)
732 * @return bool true/false.
734 function validateDateRange(&$daterange) {
735 if(! preg_match('/([0-9]{4})-[0-9]{2}-[0-9]{2} ([0-9]{4})-[0-9]{2}-[0-9]{2}/', $daterange, $matches)) {
739 # Hack to avoid exceeding the maximum value for an integer in the database
740 if ($matches[1] > 2037) {
741 $daterange = preg_replace('/[\d]{4}(-[\d]{2}-[\d]{2} [\d]{4}-[\d]{2}-[\d]{2})/', '2037$1', $daterange);
743 if ($matches[2] > 2037) {
744 $daterange = preg_replace('/([\d]{4}-[\d]{2}-[\d]{2} )[\d]{4}(-[\d]{2}-[\d]{2})/', '${1}2037$2', $daterange);
751 * update - update a row in the table used to query names
757 * @param $moddaterange
760 * @param $extra_fields
761 * @param string $opendaterange
762 * @param string $closedaterange
764 * @param $description
766 * @param int $query_type Id of the saved query
767 * @param array $query_options
768 * @param string $submitter
769 * @param string $last_modifier
770 * @return bool success.
772 function update($name,$status,$assignee,$moddaterange,$sort_col,$sort_ord,$extra_fields,$opendaterange='',$closedaterange='',
773 $summary,$description,$followups,$query_type=0,$query_options=array(),$submitter='',$last_modifier='') {
775 $this->setMissingParamsError();
778 if (!session_loggedin()) {
779 $this->setError(_('Must Be Logged In'));
782 if ($query_type>0 && !forge_check_perm ('tracker', $this->ArtifactType->getID(), 'manager')) {
783 $this->setError(_('You must have tracker admin rights to set or update a project level query.'));
787 // Reset the project default query.
788 if ($query_type==2) {
789 $res = db_query_params ('UPDATE artifact_query SET query_type=1 WHERE query_type=2 AND group_artifact_id=$1',
790 array($this->ArtifactType->getID()));
792 $this->setError('Error Updating: '.db_error());
797 $result = db_query_params ('UPDATE artifact_query
801 WHERE artifact_query_id=$4',
802 array (htmlspecialchars($name),
804 join('|', $query_options),
806 if ($result && db_affected_rows($result) > 0) {
807 if (!$this->insertElements($this->getID(),$status,$submitter,$assignee,$moddaterange,$sort_col,$sort_ord,$extra_fields,$opendaterange,$closedaterange,$summary,$description,$followups,$last_modifier)) {
812 $this->fetchData($this->getID());
816 $this->setError('Error Updating: '.db_error());
823 * makeDefault - set this as the default query
825 * @return boolean success.
827 function makeDefault() {
828 if (!session_loggedin()) {
829 $this->setError(_('Must Be Logged In'));
832 $usr =& session_get_user();
833 return $usr->setPreference('art_query'.$this->ArtifactType->getID(),$this->getID());
837 * delete - delete query
839 * @return boolean success.
842 if (forge_check_perm ('tracker', $this->ArtifactType->getID(), 'manager')) {
843 $res = db_query_params ('DELETE FROM artifact_query WHERE artifact_query_id=$1 AND (user_id=$2 OR query_type>0)',
844 array ($this->getID(),
850 $res = db_query_params ('DELETE FROM artifact_query WHERE artifact_query_id=$1 AND user_id=$2',
851 array ($this->getID(),
857 db_query_params ('DELETE FROM user_preferences WHERE preference_value=$1 AND preference_name =$2',
858 array ($this->getID(),
859 'art_query'.$this->ArtifactType->getID())) ;
860 unset($this->data_array);
861 unset($this->element_array);
866 * Exist - check if already exist a query with the same name , user_id and artifact_id
868 * @param string $name Name of query
869 * @return bool true if query already exists
871 function Exist($name) {
872 $user_id = user_getid();
873 $art_id = $this->ArtifactType->getID();
874 $res = db_query_params ('SELECT * FROM artifact_query WHERE group_artifact_id = $1 AND query_name = $2 AND (user_id = $3 OR query_type>0)',
878 if (db_numrows($res)>0) {
888 // c-file-style: "bsd"