5 * Copyright 2005, Anthony J. Pugliese
6 * Copyright 2005, GForge, LLC
7 * Copyright 2009, Roland Mas
8 * Copyright 2009, Alcatel-Lucent
10 * This file is part of FusionForge. FusionForge is free software;
11 * you can redistribute it and/or modify it under the terms of the
12 * GNU General Public License as published by the Free Software
13 * Foundation; either version 2 of the Licence, or (at your option)
16 * FusionForge is distributed in the hope that it will be useful,
17 * but WITHOUT ANY WARRANTY; without even the implied warranty of
18 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 * GNU General Public License for more details.
21 * You should have received a copy of the GNU General Public License along
22 * with FusionForge; if not, write to the Free Software Foundation, Inc.,
23 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
27 * Standard Alcatel-Lucent disclaimer for contributing to open source
29 * "The Artifact ("Contribution") has not been tested and/or
30 * validated for release as or in products, combinations with products or
31 * other commercial use. Any use of the Contribution is entirely made at
32 * the user's own responsibility and the user can not rely on any features,
33 * functionalities or performances Alcatel-Lucent has attributed to the
36 * THE CONTRIBUTION BY ALCATEL-LUCENT IS PROVIDED AS IS, WITHOUT WARRANTY
37 * OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
38 * WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, COMPLIANCE,
39 * NON-INTERFERENCE AND/OR INTERWORKING WITH THE SOFTWARE TO WHICH THE
40 * CONTRIBUTION HAS BEEN MADE, TITLE AND NON-INFRINGEMENT. IN NO EVENT SHALL
41 * ALCATEL-LUCENT BE LIABLE FOR ANY DAMAGES OR OTHER LIABLITY, WHETHER IN
42 * CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
43 * CONTRIBUTION OR THE USE OR OTHER DEALINGS IN THE CONTRIBUTION, WHETHER
44 * TOGETHER WITH THE SOFTWARE TO WHICH THE CONTRIBUTION RELATES OR ON A STAND
48 require_once $gfcommon.'include/FFError.class.php';
50 define('ARTIFACT_QUERY_ASSIGNEE',1);
51 define('ARTIFACT_QUERY_STATE',2);
52 define('ARTIFACT_QUERY_MODDATE',3);
53 define('ARTIFACT_QUERY_EXTRAFIELD',4);
54 define('ARTIFACT_QUERY_SORTCOL',5);
55 define('ARTIFACT_QUERY_SORTORD',6);
56 define('ARTIFACT_QUERY_OPENDATE',7);
57 define('ARTIFACT_QUERY_CLOSEDATE',8);
58 define('ARTIFACT_QUERY_SUMMARY',9);
59 define('ARTIFACT_QUERY_DESCRIPTION',10);
60 define('ARTIFACT_QUERY_FOLLOWUPS',11);
61 define('ARTIFACT_QUERY_SUBMITTER',12);
63 require_once $gfcommon.'tracker/ArtifactType.class.php';
65 class ArtifactQuery extends FFError {
67 * The artifact type object.
69 * @var object $ArtifactType.
71 var $ArtifactType; //object
74 * Array of artifact data.
76 * @var array $data_array.
81 * Array of query conditions
83 * @var array $element_array.
88 * @param $ArtifactType $ArtifactType c object.
89 * @param array|bool $data
91 function __construct(&$ArtifactType, $data = false) {
92 parent::__construct();
94 // Was ArtifactType legit?
95 if (!$ArtifactType || !is_object($ArtifactType)) {
96 $this->setError('ArtifactQuery: No Valid ArtifactType');
99 // Did ArtifactType have an error?
100 if ($ArtifactType->isError()) {
101 $this->setError('ArtifactQuery: '.$ArtifactType->getErrorMessage());
104 $this->ArtifactType =& $ArtifactType;
107 if (is_array($data)) {
108 $this->data_array =& $data;
110 $this->fetchData($data);
116 * create - create a row in the table that stores a saved query for
119 * @param string $name
122 * @param $moddaterange
125 * @param $extra_fields
126 * @param int $opendaterange
127 * @param int $closedaterange
128 * @param string $summary
129 * @param string $description
131 * @param int $query_type
132 * @param array $query_options
133 * @param string $submitter Name of the saved query.
134 * @return bool true on success / false on failure.
136 function create($name,$status,$assignee,$moddaterange,$sort_col,$sort_ord,$extra_fields,$opendaterange=0,$closedaterange=0,
137 $summary,$description,$followups,$query_type=0,$query_options=array(),$submitter='') {
142 $this->setMissingParamsError();
145 if (!session_loggedin()) {
146 $this->setError(_('Must Be Logged In'));
150 if ($this->Exist(htmlspecialchars($name))) {
151 $this->setError(_('Query already exists'));
155 if ($query_type>0 && !forge_check_perm ('tracker', $this->ArtifactType->getID(), 'manager')) {
156 $this->setError( _('You must have tracker admin rights to set or update a project level query.'));
160 // Reset the project default query.
161 if ($query_type==2) {
162 $res = db_query_params ('UPDATE artifact_query SET query_type=1 WHERE query_type=2 AND group_artifact_id=$1',
163 array($this->ArtifactType->getID()));
165 $this->setError('Error Updating: '.db_error());
171 $result = db_query_params ('INSERT INTO artifact_query (group_artifact_id,query_name,user_id,query_type) VALUES ($1,$2,$3,$4)',
172 array ($this->ArtifactType->getID(),
173 htmlspecialchars($name),
176 if ($result && db_affected_rows($result) > 0) {
178 $id=db_insertid($result,'artifact_query','artifact_query_id');
180 $this->setError('Error getting id '.db_error());
184 if (!$this->insertElements($id,$status,$submitter,$assignee,$moddaterange,$sort_col,$sort_ord,$extra_fields,$opendaterange,$closedaterange,$summary,$description,$followups)) {
190 $this->setError(db_error());
195 // Now set up our internal data structures
197 if ($this->fetchData($id)) {
207 * fetchData - re-fetch the data for this ArtifactQuery from the database.
209 * @param int $id ID of saved query.
210 * @return boolean success.
212 function fetchData($id) {
213 $res = db_query_params ('SELECT * FROM artifact_query WHERE artifact_query_id=$1',
216 if (!$res || db_numrows($res) < 1) {
217 $this->setError('ArtifactQuery: Invalid ArtifactQuery ID'.db_error());
220 $this->data_array = db_fetch_array($res);
221 db_free_result($res);
222 $res = db_query_params ('SELECT * FROM artifact_query_fields WHERE artifact_query_id=$1',
224 unset($this->element_array);
225 while ($arr = db_fetch_array($res)) {
227 // Some things may have been saved as comma-separated items
229 if (strstr($arr['query_field_values'],',')) {
230 $arr['query_field_values']=explode(',',$arr['query_field_values']);
232 $this->element_array[$arr['query_field_type']][$arr['query_field_id']]=$arr['query_field_values'];
238 * getArtifactType - get the ArtifactType Object this ArtifactExtraField is associated with.
240 * @return object ArtifactType.
242 function &getArtifactType() {
243 return $this->ArtifactType;
247 * insertElements - ???
253 * @param $moddaterange
256 * @param $extra_fields
257 * @param $opendaterange
258 * @param $closedaterange
259 * @param string $summary
260 * @param string $description
262 * @return boolean True/false on success or not.
264 function insertElements($id,$status,$submitter,$assignee,$moddaterange,$sort_col,$sort_ord,$extra_fields,$opendaterange,$closedaterange,$summary,$description,$followups) {
265 $res = db_query_params ('DELETE FROM artifact_query_fields WHERE artifact_query_id=$1',
268 $this->setError('Deleting Old Elements: '.db_error());
272 $status = intval($status);
273 $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)',
275 ARTIFACT_QUERY_STATE,
278 $this->setError('Setting Status: '.db_error());
282 if (is_array($submitter)) {
283 for($e=0; $e<count($submitter); $e++) {
284 $submitter[$e]=intval($submitter[$e]);
286 $submitter=implode(',',$submitter);
288 $submitter = intval($submitter);
291 if (is_array($assignee)) {
292 for($e=0; $e<count($assignee); $e++) {
293 $assignee[$e]=intval($assignee[$e]);
295 $assignee=implode(',',$assignee);
297 $assignee = intval($assignee);
300 if (preg_match("/[^[:alnum:]_]/", $sort_col)) {
301 $this->setError('ArtifactQuery: not valid sort_col');
305 if (preg_match("/[^[:alnum:]_]/", $sort_ord)) {
306 $this->setError('ArtifactQuery: not valid sort_ord');
310 //CSV LIST OF SUBMITTERS
312 $res = db_query_params ('INSERT INTO artifact_query_fields
313 (artifact_query_id,query_field_type,query_field_id,query_field_values)
314 VALUES ($1,$2,0,$3)',
316 ARTIFACT_QUERY_SUBMITTER,
319 $this->setError('Setting Submitter: '.db_error());
324 //CSV LIST OF ASSIGNEES
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_ASSIGNEE,
333 $this->setError('Setting Assignee: '.db_error());
338 //MOD DATE RANGE YYYY-MM-DD YYYY-MM-DD format
339 if ($moddaterange && !$this->validateDateRange($moddaterange)) {
340 $this->setError(_('Invalid Last Modified Date Range'));
343 $res = db_query_params ('INSERT INTO artifact_query_fields
344 (artifact_query_id,query_field_type,query_field_id,query_field_values)
345 VALUES ($1,$2,0,$3)',
347 ARTIFACT_QUERY_MODDATE,
350 $this->setError('Setting Last Modified Date Range: '.db_error());
354 //OPEN DATE RANGE YYYY-MM-DD YYYY-MM-DD format
355 if ($opendaterange && !$this->validateDateRange($opendaterange)) {
356 $this->setError(_('Invalid Open Date Range'));
359 $res = db_query_params ('INSERT INTO artifact_query_fields
360 (artifact_query_id,query_field_type,query_field_id,query_field_values)
361 VALUES ($1,$2,0,$3)',
363 ARTIFACT_QUERY_OPENDATE,
366 $this->setError('Setting Open Date Range: '.db_error());
370 //CLOSE DATE RANGE YYYY-MM-DD YYYY-MM-DD format
371 if ($closedaterange && !$this->validateDateRange($closedaterange)) {
372 $this->setError(_('Invalid Close Date Range'));
375 $res = db_query_params ('INSERT INTO artifact_query_fields
376 (artifact_query_id,query_field_type,query_field_id,query_field_values)
377 VALUES ($1,$2,0,$3)',
379 ARTIFACT_QUERY_CLOSEDATE,
382 $this->setError('Setting Close Date Range: '.db_error());
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_SORTCOL,
394 $this->setError('Setting Sort Col: '.db_error());
397 $res = db_query_params ('INSERT INTO artifact_query_fields
398 (artifact_query_id,query_field_type,query_field_id,query_field_values)
399 VALUES ($1,$2,0,$3)',
401 ARTIFACT_QUERY_SORTORD,
404 $this->setError('Setting Sort Order: '.db_error());
408 // Saving the summary value.
409 $res=db_query_params ('INSERT INTO artifact_query_fields
410 (artifact_query_id,query_field_type,query_field_id,query_field_values)
411 VALUES ($1,$2,$3,$4)',
413 ARTIFACT_QUERY_SUMMARY,
417 $this->setError('Setting Summary: '.db_error());
421 // Saving the description value.
422 $res=db_query_params ('INSERT INTO artifact_query_fields
423 (artifact_query_id,query_field_type,query_field_id,query_field_values)
424 VALUES ($1,$2,$3,$4)',
426 ARTIFACT_QUERY_DESCRIPTION,
430 $this->setError('Setting Description: '.db_error());
434 // Saving the followups value.
435 $res=db_query_params ('INSERT INTO artifact_query_fields
436 (artifact_query_id,query_field_type,query_field_id,query_field_values)
437 VALUES ($1,$2,$3,$4)',
439 ARTIFACT_QUERY_FOLLOWUPS,
443 $this->setError('Setting Followups: '.db_error());
447 if (!$extra_fields) {
448 $extra_fields=array();
451 $keys=array_keys($extra_fields);
452 $vals=array_values($extra_fields);
453 for ($i=0; $i<count($keys); $i++) {
458 // Checkboxes and multi-select may be arrays so store it comma-separated
460 if (is_array($vals[$i])) {
461 for($e=0; $e<count($vals[$i]); $e++) {
462 $vals[$i][$e]=intval($vals[$i][$e]);
464 $vals[$i]=implode(',',$vals[$i]);
467 $aef = new ArtifactExtraField($this->ArtifactType, $keys[$i]);
468 $type = $aef->getType();
469 if ($type == ARTIFACT_EXTRAFIELDTYPE_INTEGER) {
470 if (!preg_match('/^[><= \-\+0-9%]+$/', $vals[$i])) {
471 $this->setError('Invalid Value for Integer type: '. $vals[$i]);
476 $res = db_query_params ('INSERT INTO artifact_query_fields
477 (artifact_query_id,query_field_type,query_field_id,query_field_values)
478 VALUES ($1,$2,$3,$4)',
480 ARTIFACT_QUERY_EXTRAFIELD,
484 $this->setError('Setting values: '.db_error());
492 * getID - get this ArtifactQuery ID.
494 * @return int The id #.
497 return $this->data_array['artifact_query_id'];
501 * getName - get the name.
503 * @return string The name.
506 return $this->data_array['query_name'];
510 * getUserId - get the user_id.
512 * @return string The user_id.
514 function getUserId() {
515 return $this->data_array['user_id'];
519 * getQueryType - get the type of the query
521 * @return string type of query (0: private, 1: project, 2: project&default)
523 function getQueryType() {
524 return $this->data_array['query_type'];
528 * getQueryOptions - get the options of the query
530 * @return array array of all activated options
532 function getQueryOptions() {
533 if (isset($this->data_array['query_options'])) {
534 return explode('|', $this->data_array['query_options']);
541 * getSortCol - the column that you're sorting on
543 * @return string The column name.
545 function getSortCol() {
546 if (!isset($this->element_array))
548 return $this->element_array[ARTIFACT_QUERY_SORTCOL][0];
552 * getSortOrd - ASC or DESC
554 * @return string ASC or DESC
556 function getSortOrd() {
557 if (!isset($this->element_array))
559 return $this->element_array[ARTIFACT_QUERY_SORTORD][0];
563 * getModDateRange - get the range of dates to include in a query
565 * @return string mod date range.
567 function getModDateRange() {
568 if (!isset($this->element_array))
570 if ($this->element_array[ARTIFACT_QUERY_MODDATE][0]) {
571 return $this->element_array[ARTIFACT_QUERY_MODDATE][0];
578 * getOpenDateRange - get the range of dates to include in a query
580 * @return string Open date range.
582 function getOpenDateRange() {
583 if (!isset($this->element_array))
585 if (isset($this->element_array[ARTIFACT_QUERY_OPENDATE][0])) {
586 return $this->element_array[ARTIFACT_QUERY_OPENDATE][0];
593 * getCloseDateRange - get the range of dates to include in a query
595 * @return string Close date range.
597 function getCloseDateRange() {
598 if (!isset($this->element_array))
600 if (isset($this->element_array[ARTIFACT_QUERY_CLOSEDATE][0])) {
601 return $this->element_array[ARTIFACT_QUERY_CLOSEDATE][0];
608 * getSummary - get the summary string to include in a query
610 * @return string Summary string.
612 function getSummary() {
613 if (!isset($this->element_array[ARTIFACT_QUERY_SUMMARY][0])) {
616 return $this->element_array[ARTIFACT_QUERY_SUMMARY][0];
620 * getDescription - get the description string to include in a query
622 * @return string Description string.
624 function getDescription() {
625 if (!isset($this->element_array[ARTIFACT_QUERY_DESCRIPTION][0])) {
628 return $this->element_array[ARTIFACT_QUERY_DESCRIPTION][0];
632 * getFollowups - get the followups string to include in a query
634 * @return string Folowups string.
636 function getFollowups() {
637 if (!isset($this->element_array[ARTIFACT_QUERY_FOLLOWUPS][0])) {
640 return $this->element_array[ARTIFACT_QUERY_FOLLOWUPS][0];
646 * @return string Assignee ID
648 function getAssignee() {
649 if (!isset($this->element_array[ARTIFACT_QUERY_ASSIGNEE]))
651 return $this->element_array[ARTIFACT_QUERY_ASSIGNEE][0];
657 * @return string Submitter ID
659 function getSubmitter() {
660 if (!isset($this->element_array[ARTIFACT_QUERY_SUBMITTER]))
662 return $this->element_array[ARTIFACT_QUERY_SUBMITTER][0];
668 * @return string Status ID
670 function getStatus() {
671 if (!isset($this->element_array))
673 return $this->element_array[ARTIFACT_QUERY_STATE][0];
677 * getExtraFields - complex multi-dimensional array of extra field IDs/Vals
679 * @return array Complex Array
681 function getExtraFields() {
682 if (!isset($this->element_array))
684 if (! isset ($this->element_array[ARTIFACT_QUERY_EXTRAFIELD])) {
685 $this->element_array[ARTIFACT_QUERY_EXTRAFIELD] = array () ;
687 return $this->element_array[ARTIFACT_QUERY_EXTRAFIELD];
691 * validateDateRange - validate a date range in this format '1999-05-01 1999-06-01'.
693 * @param string $daterange A range of two dates (1999-05-01 1999-06-01)
694 * @return bool true/false.
696 function validateDateRange(&$daterange) {
697 if(! preg_match('/([0-9]{4})-[0-9]{2}-[0-9]{2} ([0-9]{4})-[0-9]{2}-[0-9]{2}/', $daterange, $matches)) {
701 # Hack to avoid exceeding the maximum value for an integer in the database
702 if ($matches[1] > 2037) {
703 $daterange = preg_replace('/[\d]{4}(-[\d]{2}-[\d]{2} [\d]{4}-[\d]{2}-[\d]{2})/', '2037$1', $daterange);
705 if ($matches[2] > 2037) {
706 $daterange = preg_replace('/([\d]{4}-[\d]{2}-[\d]{2} )[\d]{4}(-[\d]{2}-[\d]{2})/', '${1}2037$2', $daterange);
713 * update - update a row in the table used to query names
719 * @param $moddaterange
722 * @param $extra_fields
723 * @param string $opendaterange
724 * @param string $closedaterange
726 * @param $description
728 * @param int $query_type Id of the saved query
729 * @param array $query_options
730 * @param string $submitter
731 * @return bool success.
733 function update($name,$status,$assignee,$moddaterange,$sort_col,$sort_ord,$extra_fields,$opendaterange='',$closedaterange='',
734 $summary,$description,$followups,$query_type=0,$query_options=array(),$submitter='') {
736 $this->setMissingParamsError();
739 if (!session_loggedin()) {
740 $this->setError(_('Must Be Logged In'));
743 if ($query_type>0 && !forge_check_perm ('tracker', $this->ArtifactType->getID(), 'manager')) {
744 $this->setError(_('You must have tracker admin rights to set or update a project level query.'));
748 // Reset the project default query.
749 if ($query_type==2) {
750 $res = db_query_params ('UPDATE artifact_query SET query_type=1 WHERE query_type=2 AND group_artifact_id=$1',
751 array($this->ArtifactType->getID()));
753 $this->setError('Error Updating: '.db_error());
758 $result = db_query_params ('UPDATE artifact_query
762 WHERE artifact_query_id=$4',
763 array (htmlspecialchars($name),
765 join('|', $query_options),
767 if ($result && db_affected_rows($result) > 0) {
768 if (!$this->insertElements($this->getID(),$status,$submitter,$assignee,$moddaterange,$sort_col,$sort_ord,$extra_fields,$opendaterange,$closedaterange,$summary,$description,$followups)) {
773 $this->fetchData($this->getID());
777 $this->setError('Error Updating: '.db_error());
784 * makeDefault - set this as the default query
786 * @return boolean success.
788 function makeDefault() {
789 if (!session_loggedin()) {
790 $this->setError(_('Must Be Logged In'));
793 $usr =& session_get_user();
794 return $usr->setPreference('art_query'.$this->ArtifactType->getID(),$this->getID());
798 * delete - delete query
800 * @return boolean success.
803 if (forge_check_perm ('tracker', $this->ArtifactType->getID(), 'manager')) {
804 $res = db_query_params ('DELETE FROM artifact_query WHERE artifact_query_id=$1 AND (user_id=$2 OR query_type>0)',
805 array ($this->getID(),
811 $res = db_query_params ('DELETE FROM artifact_query WHERE artifact_query_id=$1 AND user_id=$2',
812 array ($this->getID(),
818 db_query_params ('DELETE FROM user_preferences WHERE preference_value=$1 AND preference_name =$2',
819 array ($this->getID(),
820 'art_query'.$this->ArtifactType->getID())) ;
821 unset($this->data_array);
822 unset($this->element_array);
827 * Exist - check if already exist a query with the same name , user_id and artifact_id
829 * @param string $name Name of query
830 * @return bool true if query already exists
832 function Exist($name) {
833 $user_id = user_getid();
834 $art_id = $this->ArtifactType->getID();
835 $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)',
839 if (db_numrows($res)>0) {
849 // c-file-style: "bsd"