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
10 * Copyright 2021, Franck Villaume - TrivialDev
12 * This file is part of FusionForge. FusionForge is free software;
13 * you can redistribute it and/or modify it under the terms of the
14 * GNU General Public License as published by the Free Software
15 * Foundation; either version 2 of the Licence, or (at your option)
18 * FusionForge is distributed in the hope that it will be useful,
19 * but WITHOUT ANY WARRANTY; without even the implied warranty of
20 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
21 * GNU General Public License for more details.
23 * You should have received a copy of the GNU General Public License along
24 * with FusionForge; if not, write to the Free Software Foundation, Inc.,
25 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
29 * Standard Alcatel-Lucent disclaimer for contributing to open source
31 * "The Artifact ("Contribution") has not been tested and/or
32 * validated for release as or in products, combinations with products or
33 * other commercial use. Any use of the Contribution is entirely made at
34 * the user's own responsibility and the user can not rely on any features,
35 * functionalities or performances Alcatel-Lucent has attributed to the
38 * THE CONTRIBUTION BY ALCATEL-LUCENT IS PROVIDED AS IS, WITHOUT WARRANTY
39 * OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
40 * WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, COMPLIANCE,
41 * NON-INTERFERENCE AND/OR INTERWORKING WITH THE SOFTWARE TO WHICH THE
42 * CONTRIBUTION HAS BEEN MADE, TITLE AND NON-INFRINGEMENT. IN NO EVENT SHALL
43 * ALCATEL-LUCENT BE LIABLE FOR ANY DAMAGES OR OTHER LIABLITY, WHETHER IN
44 * CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
45 * CONTRIBUTION OR THE USE OR OTHER DEALINGS IN THE CONTRIBUTION, WHETHER
46 * TOGETHER WITH THE SOFTWARE TO WHICH THE CONTRIBUTION RELATES OR ON A STAND
50 require_once $gfcommon.'include/FFError.class.php';
52 define('ARTIFACT_QUERY_ASSIGNEE',1);
53 define('ARTIFACT_QUERY_STATE',2);
54 define('ARTIFACT_QUERY_MODDATE',3);
55 define('ARTIFACT_QUERY_EXTRAFIELD',4);
56 define('ARTIFACT_QUERY_SORTCOL',5);
57 define('ARTIFACT_QUERY_SORTORD',6);
58 define('ARTIFACT_QUERY_OPENDATE',7);
59 define('ARTIFACT_QUERY_CLOSEDATE',8);
60 define('ARTIFACT_QUERY_SUMMARY',9);
61 define('ARTIFACT_QUERY_DESCRIPTION',10);
62 define('ARTIFACT_QUERY_FOLLOWUPS',11);
63 define('ARTIFACT_QUERY_SUBMITTER',12);
64 define('ARTIFACT_QUERY_LAST_MODIFIER',13);
66 require_once $gfcommon.'tracker/ArtifactType.class.php';
68 class ArtifactQuery extends FFError {
70 * The artifact type object.
72 * @var object $ArtifactType.
74 var $ArtifactType; //object
77 * Array of artifact data.
79 * @var array $data_array.
84 * Array of query conditions
86 * @var array $element_array.
91 * @param $ArtifactType $ArtifactType c object.
92 * @param array|bool $data
94 function __construct(&$ArtifactType, $data = false) {
95 parent::__construct();
97 // Was ArtifactType legit?
98 if (!$ArtifactType || !is_object($ArtifactType)) {
99 $this->setError('ArtifactQuery: No Valid ArtifactType');
102 // Did ArtifactType have an error?
103 if ($ArtifactType->isError()) {
104 $this->setError('ArtifactQuery: '.$ArtifactType->getErrorMessage());
107 $this->ArtifactType =& $ArtifactType;
110 if (is_array($data)) {
111 $this->data_array =& $data;
113 $this->fetchData($data);
119 * create - create a row in the table that stores a saved query for
122 * @param string $name
125 * @param $moddaterange
128 * @param $extra_fields
129 * @param int $opendaterange
130 * @param int $closedaterange
131 * @param string $summary
132 * @param string $description
133 * @param string $followups
134 * @param int $query_type
135 * @param array $query_options
136 * @param string $submitter Name of the saved query.
137 * @param string $last_modifier
138 * @return bool true on success / false on failure.
140 function create($name,$status,$assignee,$moddaterange,$sort_col,$sort_ord,$extra_fields,$opendaterange=0,$closedaterange=0,
141 $summary='',$description='',$followups='',$query_type=0,$query_options=array(),$submitter='',$last_modifier='') {
146 $this->setMissingParamsError();
149 if (!session_loggedin()) {
150 $this->setError(_('Must Be Logged In'));
154 if ($this->Exist(htmlspecialchars($name))) {
155 $this->setError(_('Query already exists'));
159 if ($query_type>0 && !forge_check_perm ('tracker', $this->ArtifactType->getID(), 'manager')) {
160 $this->setError( _('You must have tracker admin rights to set or update a project level query.'));
164 // Reset the project default query.
165 if ($query_type==2) {
166 $res = db_query_params ('UPDATE artifact_query SET query_type=1 WHERE query_type=2 AND group_artifact_id=$1',
167 array($this->ArtifactType->getID()));
169 $this->setError('Error Updating: '.db_error());
175 $result = db_query_params ('INSERT INTO artifact_query (group_artifact_id,query_name,user_id,query_type) VALUES ($1,$2,$3,$4)',
176 array ($this->ArtifactType->getID(),
177 htmlspecialchars($name),
180 if ($result && db_affected_rows($result) > 0) {
182 $id=db_insertid($result,'artifact_query','artifact_query_id');
184 $this->setError('Error getting id '.db_error());
188 if (!$this->insertElements($id,$status,$submitter,$assignee,$moddaterange,$sort_col,$sort_ord,$extra_fields,$opendaterange,$closedaterange,$summary,$description,$followups,$last_modifier)) {
194 $this->setError(db_error());
199 // Now set up our internal data structures
201 if ($this->fetchData($id)) {
211 * fetchData - re-fetch the data for this ArtifactQuery from the database.
213 * @param int $id ID of saved query.
214 * @return bool success.
216 function fetchData($id) {
217 $res = db_query_params ('SELECT * FROM artifact_query WHERE artifact_query_id=$1',
220 if (!$res || db_numrows($res) < 1) {
221 $this->setError('ArtifactQuery: Invalid ArtifactQuery ID'.db_error());
224 $this->data_array = db_fetch_array($res);
225 db_free_result($res);
226 $res = db_query_params ('SELECT * FROM artifact_query_fields WHERE artifact_query_id=$1',
228 unset($this->element_array);
229 while ($arr = db_fetch_array($res)) {
231 // Some things may have been saved as comma-separated items
233 if (strstr($arr['query_field_values'],',')) {
234 $arr['query_field_values']=explode(',',$arr['query_field_values']);
236 $this->element_array[$arr['query_field_type']][$arr['query_field_id']]=$arr['query_field_values'];
242 * getArtifactType - get the ArtifactType Object this ArtifactExtraField is associated with.
244 * @return object ArtifactType.
246 function &getArtifactType() {
247 return $this->ArtifactType;
251 * insertElements - ???
257 * @param $moddaterange
260 * @param $extra_fields
261 * @param $opendaterange
262 * @param $closedaterange
263 * @param string $summary
264 * @param string $description
265 * @param $last_modifier
267 * @return bool True/false on success or not.
269 function insertElements($id,$status,$submitter,$assignee,$moddaterange,$sort_col,$sort_ord,$extra_fields,$opendaterange,$closedaterange,$summary,$description,$followups,$last_modifier) {
270 $res = db_query_params ('DELETE FROM artifact_query_fields WHERE artifact_query_id=$1',
273 $this->setError('Deleting Old Elements: '.db_error());
277 $status = intval($status);
278 $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)',
280 ARTIFACT_QUERY_STATE,
283 $this->setError('Setting Status: '.db_error());
287 if (is_array($submitter)) {
288 for($e=0; $e<count($submitter); $e++) {
289 $submitter[$e]=intval($submitter[$e]);
291 $submitter=implode(',',$submitter);
293 $submitter = intval($submitter);
296 if (is_array($assignee)) {
297 for($e=0; $e<count($assignee); $e++) {
298 $assignee[$e]=intval($assignee[$e]);
300 $assignee=implode(',',$assignee);
302 $assignee = intval($assignee);
305 if (is_array($last_modifier)) {
306 for($e=0; $e<count($last_modifier); $e++) {
307 $last_modifier[$e]=intval($last_modifier[$e]);
309 $last_modifier=implode(',',$last_modifier);
311 $last_modifier = intval($last_modifier);
314 if (preg_match("/[^[:alnum:]_]/", $sort_col)) {
315 $this->setError('ArtifactQuery: not valid sort_col');
319 if (preg_match("/[^[:alnum:]_]/", $sort_ord)) {
320 $this->setError('ArtifactQuery: not valid sort_ord');
324 //CSV LIST OF SUBMITTERS
326 $res = db_query_params ('INSERT INTO artifact_query_fields
327 (artifact_query_id,query_field_type,query_field_id,query_field_values)
328 VALUES ($1,$2,0,$3)',
330 ARTIFACT_QUERY_SUBMITTER,
333 $this->setError('Setting Submitter: '.db_error());
338 //CSV LIST OF SUBMITTERS
339 if ($last_modifier) {
340 $res = db_query_params ('INSERT INTO artifact_query_fields
341 (artifact_query_id,query_field_type,query_field_id,query_field_values)
342 VALUES ($1,$2,0,$3)',
344 ARTIFACT_QUERY_LAST_MODIFIER,
347 $this->setError('Setting Last Modifier: '.db_error());
352 //CSV LIST OF ASSIGNEES
354 $res = db_query_params ('INSERT INTO artifact_query_fields
355 (artifact_query_id,query_field_type,query_field_id,query_field_values)
356 VALUES ($1,$2,0,$3)',
358 ARTIFACT_QUERY_ASSIGNEE,
361 $this->setError('Setting Assignee: '.db_error());
366 //MOD DATE RANGE YYYY-MM-DD YYYY-MM-DD format
367 if ($moddaterange && !$this->validateDateRange($moddaterange)) {
368 $this->setError(_('Invalid Last Modified Date Range'));
371 $res = db_query_params ('INSERT INTO artifact_query_fields
372 (artifact_query_id,query_field_type,query_field_id,query_field_values)
373 VALUES ($1,$2,0,$3)',
375 ARTIFACT_QUERY_MODDATE,
378 $this->setError('Setting Last Modified Date Range: '.db_error());
382 //OPEN DATE RANGE YYYY-MM-DD YYYY-MM-DD format
383 if ($opendaterange && !$this->validateDateRange($opendaterange)) {
384 $this->setError(_('Invalid Open Date Range'));
387 $res = db_query_params ('INSERT INTO artifact_query_fields
388 (artifact_query_id,query_field_type,query_field_id,query_field_values)
389 VALUES ($1,$2,0,$3)',
391 ARTIFACT_QUERY_OPENDATE,
394 $this->setError('Setting Open Date Range: '.db_error());
398 //CLOSE DATE RANGE YYYY-MM-DD YYYY-MM-DD format
399 if ($closedaterange && !$this->validateDateRange($closedaterange)) {
400 $this->setError(_('Invalid Close Date Range'));
403 $res = db_query_params ('INSERT INTO artifact_query_fields
404 (artifact_query_id,query_field_type,query_field_id,query_field_values)
405 VALUES ($1,$2,0,$3)',
407 ARTIFACT_QUERY_CLOSEDATE,
410 $this->setError('Setting Close Date Range: '.db_error());
415 $res = db_query_params ('INSERT INTO artifact_query_fields
416 (artifact_query_id,query_field_type,query_field_id,query_field_values)
417 VALUES ($1,$2,0,$3)',
419 ARTIFACT_QUERY_SORTCOL,
422 $this->setError('Setting Sort Col: '.db_error());
425 $res = db_query_params ('INSERT INTO artifact_query_fields
426 (artifact_query_id,query_field_type,query_field_id,query_field_values)
427 VALUES ($1,$2,0,$3)',
429 ARTIFACT_QUERY_SORTORD,
432 $this->setError('Setting Sort Order: '.db_error());
436 // Saving the summary value.
437 $res=db_query_params ('INSERT INTO artifact_query_fields
438 (artifact_query_id,query_field_type,query_field_id,query_field_values)
439 VALUES ($1,$2,$3,$4)',
441 ARTIFACT_QUERY_SUMMARY,
445 $this->setError('Setting Summary: '.db_error());
449 // Saving the description value.
450 $res=db_query_params ('INSERT INTO artifact_query_fields
451 (artifact_query_id,query_field_type,query_field_id,query_field_values)
452 VALUES ($1,$2,$3,$4)',
454 ARTIFACT_QUERY_DESCRIPTION,
458 $this->setError('Setting Description: '.db_error());
462 // Saving the followups value.
463 $res=db_query_params ('INSERT INTO artifact_query_fields
464 (artifact_query_id,query_field_type,query_field_id,query_field_values)
465 VALUES ($1,$2,$3,$4)',
467 ARTIFACT_QUERY_FOLLOWUPS,
471 $this->setError('Setting Followups: '.db_error());
475 if (!$extra_fields) {
476 $extra_fields=array();
479 $keys=array_keys($extra_fields);
480 $vals=array_values($extra_fields);
481 for ($i=0; $i<count($keys); $i++) {
486 // Checkboxes and multi-select may be arrays so store it comma-separated
488 if (is_array($vals[$i])) {
489 for($e=0; $e<count($vals[$i]); $e++) {
490 $vals[$i][$e]=intval($vals[$i][$e]);
492 $vals[$i]=implode(',',$vals[$i]);
495 $aef = new ArtifactExtraField($this->ArtifactType, $keys[$i]);
496 $type = $aef->getType();
497 if ($type == ARTIFACT_EXTRAFIELDTYPE_INTEGER) {
498 if (!preg_match('/^[><= \-\+0-9%]+$/', $vals[$i])) {
499 $this->setError('Invalid Value for Integer type: '. $vals[$i]);
504 $res = db_query_params ('INSERT INTO artifact_query_fields
505 (artifact_query_id,query_field_type,query_field_id,query_field_values)
506 VALUES ($1,$2,$3,$4)',
508 ARTIFACT_QUERY_EXTRAFIELD,
512 $this->setError('Setting values: '.db_error());
520 * getID - get this ArtifactQuery ID.
522 * @return int The id #.
525 if (isset($this->data_array['artifact_query_id'])) {
526 return $this->data_array['artifact_query_id'];
532 * getName - get the name.
534 * @return string The name.
537 if (isset($this->data_array['query_name'])) {
538 return $this->data_array['query_name'];
544 * getUserId - get the user_id.
546 * @return string The user_id.
548 function getUserId() {
549 return $this->data_array['user_id'];
553 * getQueryType - get the type of the query
555 * @return string type of query (0: private, 1: project, 2: project&default)
557 function getQueryType() {
558 if (isset($this->data_array['query_type'])) {
559 return $this->data_array['query_type'];
565 * getQueryOptions - get the options of the query
567 * @return array array of all activated options
569 function getQueryOptions() {
570 if (isset($this->data_array['query_options'])) {
571 return explode('|', $this->data_array['query_options']);
578 * getSortCol - the column that you're sorting on
580 * @return string The column name.
582 function getSortCol() {
583 if (!isset($this->element_array)) {
586 return $this->element_array[ARTIFACT_QUERY_SORTCOL][0];
590 * getSortOrd - ASC or DESC
592 * @return string ASC or DESC
594 function getSortOrd() {
595 if (!isset($this->element_array)) {
598 return $this->element_array[ARTIFACT_QUERY_SORTORD][0];
602 * getModDateRange - get the range of dates to include in a query
604 * @return string mod date range.
606 function getModDateRange() {
607 if (!isset($this->element_array)) {
610 if ($this->element_array[ARTIFACT_QUERY_MODDATE][0]) {
611 return $this->element_array[ARTIFACT_QUERY_MODDATE][0];
618 * getOpenDateRange - get the range of dates to include in a query
620 * @return string Open date range.
622 function getOpenDateRange() {
623 if (!isset($this->element_array)) {
626 if (isset($this->element_array[ARTIFACT_QUERY_OPENDATE][0])) {
627 return $this->element_array[ARTIFACT_QUERY_OPENDATE][0];
634 * getCloseDateRange - get the range of dates to include in a query
636 * @return string Close date range.
638 function getCloseDateRange() {
639 if (!isset($this->element_array)) {
642 if (isset($this->element_array[ARTIFACT_QUERY_CLOSEDATE][0])) {
643 return $this->element_array[ARTIFACT_QUERY_CLOSEDATE][0];
650 * getSummary - get the summary string to include in a query
652 * @return string Summary string.
654 function getSummary() {
655 if (!isset($this->element_array[ARTIFACT_QUERY_SUMMARY][0])) {
658 return $this->element_array[ARTIFACT_QUERY_SUMMARY][0];
662 * getDescription - get the description string to include in a query
664 * @return string Description string.
666 function getDescription() {
667 if (!isset($this->element_array[ARTIFACT_QUERY_DESCRIPTION][0])) {
670 return $this->element_array[ARTIFACT_QUERY_DESCRIPTION][0];
674 * getFollowups - get the followups string to include in a query
676 * @return string Folowups string.
678 function getFollowups() {
679 if (!isset($this->element_array[ARTIFACT_QUERY_FOLLOWUPS][0])) {
682 return $this->element_array[ARTIFACT_QUERY_FOLLOWUPS][0];
688 * @return string Assignee ID
690 function getAssignee() {
691 if (!isset($this->element_array[ARTIFACT_QUERY_ASSIGNEE])) {
694 return $this->element_array[ARTIFACT_QUERY_ASSIGNEE][0];
700 * @return string Submitter ID
702 function getSubmitter() {
703 if (!isset($this->element_array[ARTIFACT_QUERY_SUBMITTER])) {
706 return $this->element_array[ARTIFACT_QUERY_SUBMITTER][0];
712 * @return string Last Modifier ID
714 function getLastModifier() {
715 if (!isset($this->element_array[ARTIFACT_QUERY_LAST_MODIFIER])) {
718 return $this->element_array[ARTIFACT_QUERY_LAST_MODIFIER][0];
724 * @return string Status ID
726 function getStatus() {
727 if (!isset($this->element_array)) {
730 return $this->element_array[ARTIFACT_QUERY_STATE][0];
734 * getExtraFields - complex multi-dimensional array of extra field IDs/Vals
736 * @return array Complex Array
738 function getExtraFields() {
739 if (!isset($this->element_array)) {
742 if (! isset ($this->element_array[ARTIFACT_QUERY_EXTRAFIELD])) {
743 $this->element_array[ARTIFACT_QUERY_EXTRAFIELD] = array () ;
745 return $this->element_array[ARTIFACT_QUERY_EXTRAFIELD];
749 * validateDateRange - validate a date range in this format '1999-05-01 1999-06-01'.
751 * @param string $daterange A range of two dates (1999-05-01 1999-06-01)
752 * @return bool true/false.
754 function validateDateRange(&$daterange) {
755 if(! preg_match('/([0-9]{4})-[0-9]{2}-[0-9]{2} ([0-9]{4})-[0-9]{2}-[0-9]{2}/', $daterange, $matches)) {
758 # Hack to avoid exceeding the maximum value for an integer in the database
759 if ($matches[1] > 2037) {
760 $daterange = preg_replace('/[\d]{4}(-[\d]{2}-[\d]{2} [\d]{4}-[\d]{2}-[\d]{2})/', '2037$1', $daterange);
762 if ($matches[2] > 2037) {
763 $daterange = preg_replace('/([\d]{4}-[\d]{2}-[\d]{2} )[\d]{4}(-[\d]{2}-[\d]{2})/', '${1}2037$2', $daterange);
770 * update - update a row in the table used to query names
776 * @param $moddaterange
779 * @param $extra_fields
780 * @param string $opendaterange
781 * @param string $closedaterange
783 * @param $description
785 * @param int $query_type Id of the saved query
786 * @param array $query_options
787 * @param string $submitter
788 * @param string $last_modifier
789 * @return bool success.
791 function update($name, $status, $assignee, $moddaterange, $sort_col, $sort_ord, $extra_fields, $opendaterange = '', $closedaterange = '',
792 $summary = '', $description = '', $followups = '', $query_type = 0, $query_options = array(), $submitter = '', $last_modifier = '') {
794 $this->setMissingParamsError();
797 if (!session_loggedin()) {
798 $this->setError(_('Must Be Logged In'));
801 if ($query_type>0 && !forge_check_perm ('tracker', $this->ArtifactType->getID(), 'manager')) {
802 $this->setError(_('You must have tracker admin rights to set or update a project level query.'));
806 // Reset the project default query.
807 if ($query_type==2) {
808 $res = db_query_params ('UPDATE artifact_query SET query_type=1 WHERE query_type=2 AND group_artifact_id=$1',
809 array($this->ArtifactType->getID()));
811 $this->setError('Error Updating: '.db_error());
816 $result = db_query_params ('UPDATE artifact_query
820 WHERE artifact_query_id=$4',
821 array (htmlspecialchars($name),
823 join('|', $query_options),
825 if ($result && db_affected_rows($result) > 0) {
826 if (!$this->insertElements($this->getID(),$status,$submitter,$assignee,$moddaterange,$sort_col,$sort_ord,$extra_fields,$opendaterange,$closedaterange,$summary,$description,$followups,$last_modifier)) {
831 $this->fetchData($this->getID());
835 $this->setError('Error Updating: '.db_error());
842 * makeDefault - set this as the default query
844 * @return bool success.
846 function makeDefault() {
847 if (!session_loggedin()) {
848 $this->setError(_('Must Be Logged In'));
851 $usr =& session_get_user();
852 return $usr->setPreference('art_query'.$this->ArtifactType->getID(),$this->getID());
856 * delete - delete query
858 * @return bool success.
861 if (forge_check_perm ('tracker', $this->ArtifactType->getID(), 'manager')) {
862 $res = db_query_params ('DELETE FROM artifact_query WHERE artifact_query_id=$1 AND (user_id=$2 OR query_type>0)',
863 array ($this->getID(),
869 $res = db_query_params ('DELETE FROM artifact_query WHERE artifact_query_id=$1 AND user_id=$2',
870 array ($this->getID(),
876 db_query_params ('DELETE FROM user_preferences WHERE preference_value=$1 AND preference_name =$2',
877 array ($this->getID(),
878 'art_query'.$this->ArtifactType->getID())) ;
879 unset($this->data_array);
880 unset($this->element_array);
885 * Exist - check if already exist a query with the same name , user_id and artifact_id
887 * @param string $name Name of query
888 * @return bool true if query already exists
890 function Exist($name) {
891 $user_id = user_getid();
892 $art_id = $this->ArtifactType->getID();
893 $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)',
897 if (db_numrows($res)>0) {
907 // c-file-style: "bsd"