1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
<?php
/**
* GForge Search Engine
*
* Copyright 2004 (c) Dominik Haas, GForge Team
*
* http://gforge.org
*
* @version $Id: TrackersSearchQuery.class 5270 2006-02-05 17:12:19Z tperdue $
*/
require_once('common/search/SearchQuery.class');
class TrackersSearchQuery extends SearchQuery {
/**
* group id
*
* @var int $groupId
*/
var $groupId;
/**
* flag if non public items are returned
*
* @var boolean $showNonPublic
*/
var $showNonPublic;
/**
* Constructor
*
* @param string $words words we are searching for
* @param int $offset offset
* @param boolean $isExact if we want to search for all the words or if only one matching the query is sufficient
* @param int $groupId group id
* @param array $sections sections to search in
* @param boolean $showNonPublic flag if private sections are searched too
*/
function TrackersSearchQuery($words, $offset, $isExact, $groupId, $sections=SEARCH__ALL_SECTIONS, $showNonPublic=false) {
$this->groupId = $groupId;
$this->showNonPublic = $showNonPublic;
$this->SearchQuery($words, $offset, $isExact);
$this->setSections($sections);
}
/**
* getQuery - get the sql query built to get the search results
*
* @return string sql query to execute
*/
function getQuery() {
global $sys_use_fti;
if ($sys_use_fti) {
$nonPublic = 'false';
$sections = '';
if ($this->showNonPublic) {
$nonPublic = '';
$nonPublicMsg = '';
} else {
$nonPublic = 'AND artifact_group_list.is_public = 1';
$nonPublicMsg = 'AND agl.is_public = 1';
}
if ($this->sections != SEARCH__ALL_SECTIONS) {
$sections = "AND artifact_group_list.group_artifact_id IN (".$this->sections.")";
} else {
$sections = '';
}
$words = $this->getFormattedWords();
$group_id=$this->groupId;
$sql = "SELECT DISTINCT ON (artifact.artifact_id) artifact.artifact_id,
artifact.group_artifact_id, artifact.summary AS summary,
artifact.open_date, users.realname, artifact_group_list.name
FROM artifact LEFT JOIN artifact_message USING (artifact_id), users,
artifact_group_list
WHERE users.user_id = artifact.submitted_by
$nonPublic
AND artifact_group_list.group_artifact_id = artifact.group_artifact_id
AND artifact_group_list.group_id = '$group_id'
$sections
AND artifact.artifact_id IN (
SELECT artifact_id FROM artifact_idx,
to_tsquery('$words') AS q WHERE vectors @@ q ORDER BY rank(vectors, q) DESC)
OR artifact.artifact_id IN (
SELECT artifact_id FROM artifact_message_idx,
to_tsquery('$words') AS q WHERE vectors @@ q ORDER BY rank(vectors, q) DESC)";
} else {
$sql = 'SELECT DISTINCT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name '
. 'FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list '
. 'WHERE users.user_id = artifact.submitted_by '
. 'AND artifact_group_list.group_artifact_id = artifact.group_artifact_id '
. 'AND artifact_group_list.group_id = '.$this->groupId.' ';
if ($this->sections != SEARCH__ALL_SECTIONS) {
$sql .= 'AND artifact_group_list.group_artifact_id in ('.$this->sections.') ';
}
if (!$this->showNonPublic) {
$sql .= 'AND artifact_group_list.is_public = 1 ';
}
$sql .= 'AND (('.$this->getIlikeCondition('artifact.details', $this->words).') '
. 'OR ('.$this->getIlikeCondition('artifact.summary', $this->words).') '
. 'OR ('.$this->getIlikeCondition('artifact_message.body', $this->words).')) '
. 'ORDER BY artifact_group_list.name, artifact.artifact_id';
}
return $sql;
}
/**
* getSections - returns the list of available trackers
*
* @param $groupId int group id
* @param $showNonPublic boolean if we should consider non public sections
*/
function getSections($groupId, $showNonPublic=false) {
$sql = 'SELECT group_artifact_id, name FROM artifact_group_list WHERE group_id = '.$groupId.'';
if (!$showNonPublic) {
$sql .= ' AND artifact_group_list.is_public = 1';
}
$sql .= ' ORDER BY name';
$sections = array();
$res = db_query($sql);
while($data = db_fetch_array($res)) {
$sections[$data['group_artifact_id']] = $data['name'];
}
return $sections;
}
function getSearchByIdQuery() {
$sql = 'SELECT DISTINCT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name '
. 'FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list '
. 'WHERE users.user_id = artifact.submitted_by '
. 'AND artifact_group_list.group_artifact_id = artifact.group_artifact_id '
. 'AND artifact_group_list.group_id = '.$this->groupId.' ';
if ($this->sections != SEARCH__ALL_SECTIONS) {
$sql .= 'AND artifact_group_list.group_artifact_id in ('.$this->sections.') ';
}
if (!$this->showNonPublic) {
$sql .= 'AND artifact_group_list.is_public = 1 ';
}
$sql .= 'AND artifact.artifact_id=\''.$this->searchId.'\''
. 'ORDER BY artifact_group_list.name, artifact.artifact_id';
return $sql;
}
}
?>
|
