1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
//------------------------------------------------------------------------------ |
4
|
|
|
// |
5
|
|
|
// eTraxis - Records tracking web-based system |
6
|
|
|
// Copyright (C) 2005-2012 Artem Rodygin |
7
|
|
|
// |
8
|
|
|
// This program is free software: you can redistribute it and/or modify |
9
|
|
|
// it under the terms of the GNU General Public License as published by |
10
|
|
|
// the Free Software Foundation, either version 3 of the License, or |
11
|
|
|
// (at your option) any later version. |
12
|
|
|
// |
13
|
|
|
// This program is distributed in the hope that it will be useful, |
14
|
|
|
// but WITHOUT ANY WARRANTY; without even the implied warranty of |
15
|
|
|
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
16
|
|
|
// GNU General Public License for more details. |
17
|
|
|
// |
18
|
|
|
// You should have received a copy of the GNU General Public License |
19
|
|
|
// along with this program. If not, see <http://www.gnu.org/licenses/>. |
20
|
|
|
// |
21
|
|
|
//------------------------------------------------------------------------------ |
22
|
|
|
|
23
|
|
|
/** |
24
|
|
|
* Records |
25
|
|
|
* |
26
|
|
|
* This module provides API to work with records. |
27
|
|
|
* See also {@link https://github.com/etraxis/etraxis-obsolete/wiki/tbl_records tbl_records} database table. |
28
|
|
|
* |
29
|
|
|
* @package DBO |
30
|
|
|
* @subpackage Records |
31
|
|
|
*/ |
32
|
|
|
|
33
|
|
|
/**#@+ |
34
|
|
|
* Dependency. |
35
|
|
|
*/ |
36
|
|
|
require_once('../engine/engine.php'); |
37
|
|
|
require_once('../dbo/accounts.php'); |
38
|
|
|
require_once('../dbo/filters.php'); |
39
|
|
|
require_once('../dbo/fields.php'); |
40
|
|
|
require_once('../dbo/values.php'); |
41
|
|
|
require_once('../dbo/events.php'); |
42
|
|
|
/**#@-*/ |
43
|
|
|
|
44
|
|
|
//------------------------------------------------------------------------------ |
45
|
|
|
// Definitions. |
46
|
|
|
//------------------------------------------------------------------------------ |
47
|
|
|
|
48
|
|
|
/**#@+ |
49
|
|
|
* Data restrictions. |
50
|
|
|
*/ |
51
|
|
|
define('MAX_RECORD_SUBJECT', 250); |
52
|
|
|
define('MAX_COMMENT_BODY', 4000); |
53
|
|
|
define('MAX_ATTACHMENT_NAME', 100); |
54
|
|
|
define('MAX_SEARCH_TEXT', 100); |
55
|
|
|
define('MAX_SEARCH_WORDS', 5); |
56
|
|
|
/**#@-*/ |
57
|
|
|
|
58
|
|
|
/**#@+ |
59
|
|
|
* Record operations. |
60
|
|
|
*/ |
61
|
|
|
define('OPERATION_CREATE_RECORD', 1); |
62
|
|
|
define('OPERATION_MODIFY_RECORD', 2); |
63
|
|
|
define('OPERATION_CHANGE_STATE', 3); |
64
|
|
|
/**#@-*/ |
65
|
|
|
|
66
|
|
|
/**#@+ |
67
|
|
|
* Tabs on record view page. |
68
|
|
|
*/ |
69
|
|
|
define('RECORD_TAB_MAIN', 1); |
70
|
|
|
define('RECORD_TAB_HISTORY', 2); |
71
|
|
|
define('RECORD_TAB_CHANGES', 3); |
72
|
|
|
define('RECORD_TAB_FIELDS', 4); |
73
|
|
|
define('RECORD_TAB_COMMENTS', 5); |
74
|
|
|
define('RECORD_TAB_ATTACHMENTS', 6); |
75
|
|
|
define('RECORD_TAB_PARENTS', 7); |
76
|
|
|
define('RECORD_TAB_SUBRECORDS', 8); |
77
|
|
|
/**#@-*/ |
78
|
|
|
|
79
|
|
|
//------------------------------------------------------------------------------ |
80
|
|
|
// Functions. |
81
|
|
|
//------------------------------------------------------------------------------ |
82
|
|
|
|
83
|
|
|
/** |
84
|
|
|
* Formats specified record ID, adding template prefix if specified and leading zeroes if required. |
85
|
|
|
* |
86
|
|
|
* @param int $id Record ID. |
87
|
|
|
* @param string $prefix Template prefix. |
88
|
|
|
* @return string Formatted record ID. |
89
|
|
|
*/ |
90
|
|
|
function record_id ($id, $prefix = NULL) |
91
|
|
|
{ |
92
|
|
|
debug_write_log(DEBUG_TRACE, '[record_id]'); |
93
|
|
|
debug_write_log(DEBUG_DUMP, '[record_id] $id = ' . $id); |
94
|
|
|
debug_write_log(DEBUG_DUMP, '[record_id] $prefix = ' . $prefix); |
95
|
|
|
|
96
|
|
|
return ustr2html((is_null($prefix) ? NULL : $prefix . '-') . str_pad($id, 3, '0', STR_PAD_LEFT)); |
97
|
|
|
} |
98
|
|
|
|
99
|
|
|
/** |
100
|
|
|
* Finds in database and returns the information about specified record. |
101
|
|
|
* |
102
|
|
|
* @param int $id Record ID. |
103
|
|
|
* @return array Array with data if record is found in database, FALSE otherwise. |
104
|
|
|
*/ |
105
|
|
|
function record_find ($id) |
106
|
|
|
{ |
107
|
|
|
debug_write_log(DEBUG_TRACE, '[record_find]'); |
108
|
|
|
debug_write_log(DEBUG_DUMP, '[record_find] $id = ' . $id); |
109
|
|
|
|
110
|
|
|
$rs = dal_query('records/fndid.sql', $id, time()); |
111
|
|
|
|
112
|
|
|
return ($rs->rows == 0 ? FALSE : $rs->fetch()); |
|
|
|
|
113
|
|
|
} |
114
|
|
|
|
115
|
|
|
/** |
116
|
|
|
* Returns {@link CRecordset DAL recordset} which contains all records, allowed to be displayed |
117
|
|
|
* in accordance to current set of filters, search mode, user permissions, etc. |
118
|
|
|
* Recordset is sorted in accordance with current sort mode. |
119
|
|
|
* |
120
|
|
|
* @param array $columns List of columns (see {@link column_list}). |
121
|
|
|
* @param int &$sort Sort mode (used as output only). The function retrieves current sort mode from |
122
|
|
|
* client cookie ({@link COOKIE_RECORDS_SORT}) and updates it, if it's out of valid range. |
123
|
|
|
* @param int &$page Number of current page tab (used as output only). The function retrieves current |
124
|
|
|
* page from client cookie ({@link COOKIE_RECORDS_PAGE}) and updates it, if it's out of valid range. |
125
|
|
|
* @param bool $search_mode Whether the search mode is on. |
126
|
|
|
* @param string $search_text Text to be searched (ignored when search mode is off). |
127
|
|
|
* @return CRecordset Recordset with list of records. |
128
|
|
|
*/ |
129
|
|
|
function records_list ($columns, &$sort, &$page, $search_mode = FALSE, $search_text = NULL) |
130
|
|
|
{ |
131
|
|
|
debug_write_log(DEBUG_TRACE, '[records_list]'); |
132
|
|
|
|
133
|
|
|
$sort = try_request('sort', try_cookie(COOKIE_RECORDS_SORT . $_SESSION[VAR_VIEW])); |
134
|
|
|
$sort = ustr2int($sort, -count($columns), count($columns)); |
135
|
|
|
|
136
|
|
|
$page = try_request('page', try_cookie(COOKIE_RECORDS_PAGE . $_SESSION[VAR_VIEW])); |
137
|
|
|
$page = ustr2int($page, 1, MAXINT); |
138
|
|
|
|
139
|
|
|
$date = getdate(); |
140
|
|
|
$time = mktime(23, 59, 59, $date['mon'], $date['mday'], $date['year']); |
141
|
|
|
|
142
|
|
|
$clause_select = array('r.record_id', |
143
|
|
|
'r.subject', |
144
|
|
|
'r.responsible_id', |
145
|
|
|
'r.creator_id', |
146
|
|
|
'r.creation_time', |
147
|
|
|
'r.change_time', |
148
|
|
|
'r.closure_time', |
149
|
|
|
'r.postpone_time', |
150
|
|
|
'p.project_name', |
151
|
|
|
't.template_name', |
152
|
|
|
't.template_prefix', |
153
|
|
|
't.critical_age', |
154
|
|
|
's.state_name', |
155
|
|
|
's.state_abbr'); |
156
|
|
|
|
157
|
|
|
$clause_from = array('tbl_projects p', |
158
|
|
|
'tbl_states s'); |
159
|
|
|
|
160
|
|
|
$clause_join = array('tbl_records r'); |
161
|
|
|
|
162
|
|
|
$clause_where = array('p.project_id = t.project_id', |
163
|
|
|
't.template_id = s.template_id', |
164
|
|
|
's.state_id = r.state_id'); |
165
|
|
|
|
166
|
|
|
save_cookie(COOKIE_RECORDS_SORT . $_SESSION[VAR_VIEW], $sort); |
167
|
|
|
save_cookie(COOKIE_RECORDS_PAGE . $_SESSION[VAR_VIEW], $page); |
168
|
|
|
|
169
|
|
|
// Add default access conditions for guests and registered users. |
170
|
|
|
|
171
|
|
|
if (get_user_level() == USER_LEVEL_GUEST) |
172
|
|
|
{ |
173
|
|
|
array_push($clause_select, '0 as read_time'); |
174
|
|
|
array_push($clause_from, 'tbl_templates t'); |
175
|
|
|
array_push($clause_where, 't.guest_access = 1'); |
176
|
|
|
array_push($clause_where, 'r.closure_time is null'); |
177
|
|
|
} |
178
|
|
|
else |
179
|
|
|
{ |
180
|
|
|
$perms = 'select gp.template_id, count(gp.group_id) as gnum ' |
181
|
|
|
. 'from tbl_membership ms, tbl_group_perms gp ' |
182
|
|
|
. 'where ms.account_id = %1 and ms.group_id = gp.group_id and ' |
183
|
|
|
. (DATABASE_DRIVER == DRIVER_ORACLE9 ? 'mod(floor(gp.perms / %2), 2) = 1 ' : '(gp.perms & %2) <> 0 ') |
184
|
|
|
. 'group by template_id'; |
185
|
|
|
|
186
|
|
|
$perms = ustrprocess($perms, $_SESSION[VAR_USERID], PERMIT_VIEW_RECORD); |
187
|
|
|
|
188
|
|
|
array_push($clause_from, "tbl_templates t left outer join ({$perms}) perms on t.template_id = perms.template_id"); |
189
|
|
|
array_push($clause_where, ustrprocess('(perms.gnum is not null' . |
190
|
|
|
' or r.creator_id = %1' . |
191
|
|
|
' or r.responsible_id = %1' . |
192
|
|
|
' or ' . (DATABASE_DRIVER == DRIVER_ORACLE9 ? 'mod(floor(t.registered_perm / %2), 2) = 1' : '(t.registered_perm & %2) <> 0') . |
193
|
|
|
' or t.guest_access = 1)', |
194
|
|
|
$_SESSION[VAR_USERID], PERMIT_VIEW_RECORD)); |
195
|
|
|
|
196
|
|
|
array_push($clause_select, 'rd.read_time'); |
197
|
|
|
array_push($clause_join, 'left outer join tbl_reads rd on rd.record_id = r.record_id and rd.account_id = ' . $_SESSION[VAR_USERID]); |
198
|
|
|
} |
199
|
|
|
|
200
|
|
|
// Add search conditions if search is activated. |
201
|
|
|
|
202
|
|
|
if ($search_mode) |
203
|
|
|
{ |
204
|
|
|
debug_write_log(DEBUG_NOTICE, '[records_list] Search mode is turned on.'); |
205
|
|
|
debug_write_log(DEBUG_DUMP, '[records_list] $search_text = ' . $search_text); |
206
|
|
|
|
207
|
|
|
$search = array(); |
208
|
|
|
|
209
|
|
|
if (is_intvalue($search_text)) |
210
|
|
|
{ |
211
|
|
|
array_push($search, |
212
|
|
|
'select e.record_id ' . |
213
|
|
|
'from tbl_events e, tbl_field_values fv ' . |
214
|
|
|
'where e.event_id = fv.event_id and fv.field_type = ' . FIELD_TYPE_NUMBER . ' and fv.is_latest = 1 and fv.value_id = ' . $search_text); |
215
|
|
|
} |
216
|
|
|
|
217
|
|
|
$search_text = "'%" . ustr2sql(ustrtolower($search_text)) . "%'"; |
218
|
|
|
|
219
|
|
|
$search_in_subject = (in_array(DATABASE_DRIVER, array(DRIVER_ORACLE9, DRIVER_PGSQL80)) ? 'lower' : NULL) . '(r.subject) like ' . $search_text; |
220
|
|
|
$search_in_svalues = (in_array(DATABASE_DRIVER, array(DRIVER_ORACLE9, DRIVER_PGSQL80)) ? 'lower' : NULL) . '(sv.string_value) like ' . $search_text; |
221
|
|
|
$search_in_tvalues = (in_array(DATABASE_DRIVER, array(DRIVER_ORACLE9, DRIVER_PGSQL80)) ? 'lower' : NULL) . '(tv.text_value) like ' . $search_text; |
222
|
|
|
$search_in_comments = (in_array(DATABASE_DRIVER, array(DRIVER_ORACLE9, DRIVER_PGSQL80)) ? 'lower' : NULL) . '(c.comment_body) like ' . $search_text; |
223
|
|
|
|
224
|
|
|
array_push($search, |
225
|
|
|
'select r.record_id ' . |
226
|
|
|
'from tbl_records r ' . |
227
|
|
|
'where ' . $search_in_subject); |
228
|
|
|
|
229
|
|
|
array_push($search, |
230
|
|
|
'select e.record_id ' . |
231
|
|
|
'from tbl_events e, tbl_field_values fv, tbl_string_values sv ' . |
232
|
|
|
'where e.event_id = fv.event_id and fv.field_type = ' . FIELD_TYPE_STRING . ' and fv.value_id = sv.value_id and fv.is_latest = 1 and ' . |
233
|
|
|
$search_in_svalues); |
234
|
|
|
|
235
|
|
|
array_push($search, |
236
|
|
|
'select e.record_id ' . |
237
|
|
|
'from tbl_events e, tbl_field_values fv, tbl_text_values tv ' . |
238
|
|
|
'where e.event_id = fv.event_id and fv.field_type = ' . FIELD_TYPE_MULTILINED . ' and fv.value_id = tv.value_id and fv.is_latest = 1 and ' . |
239
|
|
|
$search_in_tvalues); |
240
|
|
|
|
241
|
|
|
array_push($search, |
242
|
|
|
'select e.record_id ' . |
243
|
|
|
'from tbl_events e, tbl_comments c ' . |
244
|
|
|
'where e.event_id = c.event_id and ' . |
245
|
|
|
$search_in_comments); |
246
|
|
|
|
247
|
|
|
$sql = 'select record_id from (' . implode(' union ', $search) . ') data'; |
248
|
|
|
|
249
|
|
|
$ids = array(); |
250
|
|
|
$rsx = new CRecordset($sql); |
251
|
|
|
|
252
|
|
|
while (($row = $rsx->fetch())) |
253
|
|
|
{ |
254
|
|
|
array_push($ids, $row['record_id']); |
255
|
|
|
} |
256
|
|
|
|
257
|
|
|
$search_ids = (count($ids) == 0) |
258
|
|
|
? 'NULL' |
259
|
|
|
: implode(', ', $ids); |
260
|
|
|
|
261
|
|
|
debug_write_log(DEBUG_NOTICE, '[records_list] $search_ids = [' . $search_ids . ']' ); |
262
|
|
|
|
263
|
|
|
array_push($clause_where, 'r.record_id in (' . $search_ids . ')'); |
264
|
|
|
} |
265
|
|
|
|
266
|
|
|
// Add filters, if it's not a search or if it's a filtered search. |
267
|
|
|
|
268
|
|
|
if (!$search_mode || $_SESSION[VAR_USE_FILTERS]) |
269
|
|
|
{ |
270
|
|
|
debug_write_log(DEBUG_NOTICE, '[records_list] Filters are in use.'); |
271
|
|
|
|
272
|
|
|
$filters = array(); |
273
|
|
|
|
274
|
|
|
$fsort = $fpage = NULL; |
275
|
|
|
$rs = filters_list($_SESSION[VAR_USERID], TRUE, $fsort, $fpage); |
276
|
|
|
|
277
|
|
|
while (($filter = $rs->fetch())) |
278
|
|
|
{ |
279
|
|
|
if ($filter['filter_flags'] == 0 && |
280
|
|
|
$filter['filter_type'] == FILTER_TYPE_ALL_PROJECTS) |
281
|
|
|
{ |
282
|
|
|
continue; |
283
|
|
|
} |
284
|
|
|
|
285
|
|
|
$clause_filter = array(); |
286
|
|
|
|
287
|
|
|
switch ($filter['filter_type']) |
288
|
|
|
{ |
289
|
|
|
case FILTER_TYPE_ALL_PROJECTS: |
290
|
|
|
break; |
291
|
|
|
|
292
|
|
|
case FILTER_TYPE_ALL_TEMPLATES: |
293
|
|
|
array_push($clause_select, 'p.project_id'); |
294
|
|
|
array_push($clause_filter, 'p.project_id = ' . $filter['filter_param']); |
295
|
|
|
break; |
296
|
|
|
|
297
|
|
|
case FILTER_TYPE_ALL_STATES: |
298
|
|
|
array_push($clause_select, 't.template_id'); |
299
|
|
|
array_push($clause_filter, 't.template_id = ' . $filter['filter_param']); |
300
|
|
|
break; |
301
|
|
|
|
302
|
|
|
case FILTER_TYPE_SEL_STATES: |
303
|
|
|
$states = filter_states_get($filter['filter_id'], $filter['filter_param']); |
304
|
|
|
array_push($clause_select, 's.state_id'); |
305
|
|
|
array_push($clause_filter, 's.state_id in (' . implode(',', array_unique($states)) . ')'); |
306
|
|
|
break; |
307
|
|
|
|
308
|
|
|
default: |
309
|
|
|
debug_write_log(DEBUG_WARNING, '[records_list] Unknown filter type = ' . $filter['filter_type']); |
310
|
|
|
} |
311
|
|
|
|
312
|
|
View Code Duplication |
if ($filter['filter_flags'] & FILTER_FLAG_CREATED_BY) |
313
|
|
|
{ |
314
|
|
|
array_push($clause_select, 'r.creator_id'); |
315
|
|
|
array_push($clause_filter, |
316
|
|
|
'r.creator_id in ' . |
317
|
|
|
'(select account_id ' . |
318
|
|
|
'from tbl_filter_accounts ' . |
319
|
|
|
'where filter_id = ' . $filter['filter_id'] . ' and filter_flag = ' . FILTER_FLAG_CREATED_BY . ')'); |
320
|
|
|
} |
321
|
|
|
|
322
|
|
View Code Duplication |
if ($filter['filter_flags'] & FILTER_FLAG_ASSIGNED_TO) |
323
|
|
|
{ |
324
|
|
|
array_push($clause_select, 'r.responsible_id'); |
325
|
|
|
array_push($clause_filter, |
326
|
|
|
'r.responsible_id in ' . |
327
|
|
|
'(select account_id ' . |
328
|
|
|
'from tbl_filter_accounts ' . |
329
|
|
|
'where filter_id = ' . $filter['filter_id'] . ' and filter_flag = ' . FILTER_FLAG_ASSIGNED_TO . ')'); |
330
|
|
|
} |
331
|
|
|
|
332
|
|
|
if ($filter['filter_flags'] & FILTER_FLAG_UNASSIGNED) |
333
|
|
|
{ |
334
|
|
|
array_push($clause_select, 'r.responsible_id'); |
335
|
|
|
array_push($clause_filter, 'r.responsible_id is null'); |
336
|
|
|
} |
337
|
|
|
|
338
|
|
|
if (($filter['filter_type'] != FILTER_TYPE_SEL_STATES) && |
339
|
|
|
($filter['filter_flags'] & FILTER_FLAG_UNCLOSED)) |
340
|
|
|
{ |
341
|
|
|
array_push($clause_filter, 'r.closure_time is null'); |
342
|
|
|
} |
343
|
|
|
|
344
|
|
|
if ($filter['filter_flags'] & FILTER_FLAG_POSTPONED) |
345
|
|
|
{ |
346
|
|
|
array_push($clause_filter, 'r.postpone_time > ' . $time); |
347
|
|
|
} |
348
|
|
|
|
349
|
|
|
if ($filter['filter_flags'] & FILTER_FLAG_ACTIVE) |
350
|
|
|
{ |
351
|
|
|
array_push($clause_filter, 'r.postpone_time <=' . $time); |
352
|
|
|
} |
353
|
|
|
|
354
|
|
|
if ($filter['filter_type'] == FILTER_TYPE_ALL_STATES || |
355
|
|
|
$filter['filter_type'] == FILTER_TYPE_SEL_STATES) |
356
|
|
|
{ |
357
|
|
|
$rs2 = dal_query('filters/ftlist.sql', $filter['filter_id']); |
358
|
|
|
|
359
|
|
|
while (($row = $rs2->fetch())) |
360
|
|
|
{ |
361
|
|
|
array_push($clause_filter, |
362
|
|
|
'r.record_id in ' . |
363
|
|
|
'(select record_id ' . |
364
|
|
|
'from tbl_events ' . |
365
|
|
|
'where (event_type = ' . EVENT_RECORD_CREATED . ' or event_type = ' . EVENT_RECORD_STATE_CHANGED . ') and ' . |
366
|
|
|
'event_time >= ' . $row['date1'] . ' and ' . |
367
|
|
|
'event_time <= ' . $row['date2'] . ' and ' . |
368
|
|
|
'event_param = ' . $row['state_id'] . ')'); |
369
|
|
|
} |
370
|
|
|
|
371
|
|
|
$rs2 = dal_query('filters/fflist.sql', $filter['filter_id']); |
372
|
|
|
|
373
|
|
|
while (($row = $rs2->fetch())) |
374
|
|
|
{ |
375
|
|
|
switch ($row['field_type']) |
376
|
|
|
{ |
377
|
|
|
case FIELD_TYPE_CHECKBOX: |
378
|
|
|
case FIELD_TYPE_LIST: |
379
|
|
|
case FIELD_TYPE_RECORD: |
380
|
|
|
|
381
|
|
|
$value = (is_null($row['param1']) |
382
|
|
|
? 'fv.value_id is null' |
383
|
|
|
: 'fv.value_id = ' . $row['param1']); |
384
|
|
|
|
385
|
|
|
array_push($clause_filter, |
386
|
|
|
'r.record_id in ' . |
387
|
|
|
'(select e.record_id ' . |
388
|
|
|
'from tbl_events e, tbl_field_values fv ' . |
389
|
|
|
'where fv.event_id = e.event_id and ' . |
390
|
|
|
'fv.field_id = ' . $row['field_id'] . ' and ' . |
391
|
|
|
$value . ' and ' . |
392
|
|
|
'fv.is_latest = 1)'); |
393
|
|
|
|
394
|
|
|
break; |
395
|
|
|
|
396
|
|
|
case FIELD_TYPE_NUMBER: |
397
|
|
|
case FIELD_TYPE_DATE: |
398
|
|
|
case FIELD_TYPE_DURATION: |
399
|
|
|
|
400
|
|
|
$range = (is_null($row['param1']) && is_null($row['param2']) ? 'fv.value_id is null and ' : NULL); |
401
|
|
|
|
402
|
|
|
if (!is_null($row['param1'])) |
403
|
|
|
{ |
404
|
|
|
$range .= 'fv.value_id >= ' . $row['param1'] . ' and '; |
405
|
|
|
} |
406
|
|
|
|
407
|
|
|
if (!is_null($row['param2'])) |
408
|
|
|
{ |
409
|
|
|
$range .= 'fv.value_id <= ' . $row['param2'] . ' and '; |
410
|
|
|
} |
411
|
|
|
|
412
|
|
|
array_push($clause_filter, |
413
|
|
|
'r.record_id in ' . |
414
|
|
|
'(select e.record_id ' . |
415
|
|
|
'from tbl_events e, tbl_field_values fv ' . |
416
|
|
|
'where fv.event_id = e.event_id and ' . |
417
|
|
|
'fv.field_id = ' . $row['field_id'] . ' and ' . |
418
|
|
|
$range . |
419
|
|
|
'fv.is_latest = 1)'); |
420
|
|
|
|
421
|
|
|
break; |
422
|
|
|
|
423
|
|
|
case FIELD_TYPE_FLOAT: |
424
|
|
|
|
425
|
|
|
$range = (is_null($row['param1']) && is_null($row['param2']) ? 'fv.value_id is null and ' : NULL); |
426
|
|
|
|
427
|
|
|
if (!is_null($row['param1'])) |
428
|
|
|
{ |
429
|
|
|
$range .= 'fl1.float_value >= fl2.float_value and '; |
430
|
|
|
} |
431
|
|
|
|
432
|
|
|
if (!is_null($row['param2'])) |
433
|
|
|
{ |
434
|
|
|
$range .= 'fl1.float_value <= fl3.float_value and '; |
435
|
|
|
} |
436
|
|
|
|
437
|
|
|
array_push($clause_filter, |
438
|
|
|
'r.record_id in ' . |
439
|
|
|
'(select e.record_id ' . |
440
|
|
|
'from tbl_events e, tbl_field_values fv, tbl_float_values fl1, tbl_float_values fl2, tbl_float_values fl3 ' . |
441
|
|
|
'where fv.event_id = e.event_id and ' . |
442
|
|
|
'fv.field_id = ' . $row['field_id'] . ' and ' . |
443
|
|
|
'fv.value_id = fl1.value_id and ' . |
444
|
|
|
$range . |
445
|
|
|
'fl2.value_id = ' . $row['param1'] . ' and ' . |
446
|
|
|
'fl3.value_id = ' . $row['param2'] . ' and ' . |
447
|
|
|
'fv.is_latest = 1)'); |
448
|
|
|
|
449
|
|
|
break; |
450
|
|
|
|
451
|
|
View Code Duplication |
case FIELD_TYPE_STRING: |
452
|
|
|
|
453
|
|
|
if (is_null($row['param1'])) |
454
|
|
|
{ |
455
|
|
|
array_push($clause_filter, |
456
|
|
|
'r.record_id in ' . |
457
|
|
|
'(select e.record_id ' . |
458
|
|
|
'from tbl_events e, tbl_field_values fv ' . |
459
|
|
|
'where fv.event_id = e.event_id and ' . |
460
|
|
|
'fv.field_id = ' . $row['field_id'] . ' and ' . |
461
|
|
|
'fv.value_id is null and ' . |
462
|
|
|
'fv.is_latest = 1)'); |
463
|
|
|
} |
464
|
|
|
else |
465
|
|
|
{ |
466
|
|
|
switch (DATABASE_DRIVER) |
467
|
|
|
{ |
468
|
|
|
case DRIVER_MYSQL50: |
469
|
|
|
$concat = "concat('%', sf.string_value, '%')"; |
470
|
|
|
break; |
471
|
|
|
case DRIVER_ORACLE9: |
472
|
|
|
$concat = "concat(concat('%', sf.string_value), '%')"; |
473
|
|
|
break; |
474
|
|
|
case DRIVER_MSSQL2K: |
475
|
|
|
$concat = "'%' + sf.string_value + '%'"; |
476
|
|
|
break; |
477
|
|
|
case DRIVER_PGSQL80: |
478
|
|
|
$concat = "'%' || sf.string_value || '%'"; |
479
|
|
|
break; |
480
|
|
|
default: ; // nop |
481
|
|
|
} |
482
|
|
|
|
483
|
|
|
array_push($clause_filter, |
484
|
|
|
'r.record_id in ' . |
485
|
|
|
'(select e.record_id ' . |
486
|
|
|
'from tbl_events e, tbl_field_values fv, tbl_string_values sv, tbl_string_values sf ' . |
487
|
|
|
'where fv.event_id = e.event_id and ' . |
488
|
|
|
'fv.field_id = ' . $row['field_id'] . ' and ' . |
489
|
|
|
'fv.value_id = sv.value_id and ' . |
490
|
|
|
'sf.value_id = ' . $row['param1'] . ' and ' . |
491
|
|
|
'sv.string_value like ' . $concat . ' and ' . |
492
|
|
|
'fv.is_latest = 1)'); |
493
|
|
|
} |
494
|
|
|
|
495
|
|
|
break; |
496
|
|
|
|
497
|
|
View Code Duplication |
case FIELD_TYPE_MULTILINED: |
498
|
|
|
|
499
|
|
|
if (is_null($row['param1'])) |
500
|
|
|
{ |
501
|
|
|
array_push($clause_filter, |
502
|
|
|
'r.record_id in ' . |
503
|
|
|
'(select e.record_id ' . |
504
|
|
|
'from tbl_events e, tbl_field_values fv ' . |
505
|
|
|
'where fv.event_id = e.event_id and ' . |
506
|
|
|
'fv.field_id = ' . $row['field_id'] . ' and ' . |
507
|
|
|
'fv.value_id is null and ' . |
508
|
|
|
'fv.is_latest = 1)'); |
509
|
|
|
} |
510
|
|
|
else |
511
|
|
|
{ |
512
|
|
|
switch (DATABASE_DRIVER) |
513
|
|
|
{ |
514
|
|
|
case DRIVER_MYSQL50: |
515
|
|
|
$concat = "concat('%', sf.string_value, '%')"; |
516
|
|
|
break; |
517
|
|
|
case DRIVER_ORACLE9: |
518
|
|
|
$concat = "concat(concat('%', sf.string_value), '%')"; |
519
|
|
|
break; |
520
|
|
|
case DRIVER_MSSQL2K: |
521
|
|
|
$concat = "'%' + sf.string_value + '%'"; |
522
|
|
|
break; |
523
|
|
|
case DRIVER_PGSQL80: |
524
|
|
|
$concat = "'%' || sf.string_value || '%'"; |
525
|
|
|
break; |
526
|
|
|
default: ; // nop |
527
|
|
|
} |
528
|
|
|
|
529
|
|
|
array_push($clause_filter, |
530
|
|
|
'r.record_id in ' . |
531
|
|
|
'(select e.record_id ' . |
532
|
|
|
'from tbl_events e, tbl_field_values fv, tbl_text_values tv, tbl_string_values sf ' . |
533
|
|
|
'where fv.event_id = e.event_id and ' . |
534
|
|
|
'fv.field_id = ' . $row['field_id'] . ' and ' . |
535
|
|
|
'fv.value_id = tv.value_id and ' . |
536
|
|
|
'sf.value_id = ' . $row['param1'] . ' and ' . |
537
|
|
|
'tv.text_value like ' . $concat . ' and ' . |
538
|
|
|
'fv.is_latest = 1)'); |
539
|
|
|
} |
540
|
|
|
|
541
|
|
|
break; |
542
|
|
|
} |
543
|
|
|
} |
544
|
|
|
} |
545
|
|
|
|
546
|
|
|
array_push($filters, implode(' and ', array_unique($clause_filter))); |
547
|
|
|
} |
548
|
|
|
|
549
|
|
|
if (count($filters) != 0) |
550
|
|
|
{ |
551
|
|
|
array_push($clause_where, '(' . implode(' or ', array_unique($filters)) . ')'); |
552
|
|
|
} |
553
|
|
|
} |
554
|
|
|
|
555
|
|
|
// Create basic query. |
556
|
|
|
|
557
|
|
|
$sql = 'select ' . implode(', ', array_unique($clause_select)) . |
558
|
|
|
' from ' . implode(', ', array_unique($clause_from)) . |
559
|
|
|
', ' . implode(' ', array_unique($clause_join)) . |
560
|
|
|
' where ' . implode(' and ', array_unique($clause_where)); |
561
|
|
|
|
562
|
|
|
$clause_select = array('r.*'); |
563
|
|
|
$clause_select = array('r.record_id', |
564
|
|
|
'r.subject', |
565
|
|
|
'r.responsible_id', |
566
|
|
|
'r.creator_id', |
567
|
|
|
'r.creation_time', |
568
|
|
|
'r.change_time', |
569
|
|
|
'r.closure_time', |
570
|
|
|
'r.postpone_time', |
571
|
|
|
'r.project_name', |
572
|
|
|
'r.template_name', |
573
|
|
|
'r.template_prefix', |
574
|
|
|
'r.critical_age', |
575
|
|
|
'r.state_name', |
576
|
|
|
'r.state_abbr', |
577
|
|
|
'r.read_time'); |
578
|
|
|
$clause_from = array(); |
579
|
|
|
$clause_join = array(); |
580
|
|
|
$clause_where = array(); |
581
|
|
|
$clause_order = array(); |
582
|
|
|
|
583
|
|
|
// SQL condition to check that current user is allowed to read the field. |
584
|
|
|
|
585
|
|
|
$sql_field_perms = <<<SQL |
586
|
|
|
|
587
|
|
|
f.field_id in (select f.field_id |
588
|
|
|
from |
589
|
|
|
tbl_group_perms gp, |
590
|
|
|
tbl_membership ms, |
591
|
|
|
tbl_field_perms fp, |
592
|
|
|
tbl_fields f |
593
|
|
|
where |
594
|
|
|
fp.field_id = f.field_id and |
595
|
|
|
fp.group_id = gp.group_id and |
596
|
|
|
ms.group_id = gp.group_id and |
597
|
|
|
ms.account_id = {$_SESSION[VAR_USERID]} and |
598
|
|
|
fp.perms = 1) |
599
|
|
|
|
600
|
|
|
or r.creator_id = {$_SESSION[VAR_USERID]} and |
601
|
|
|
f.field_id in (select f.field_id |
602
|
|
|
from tbl_fields f |
603
|
|
|
where f.author_perm >= 1) |
604
|
|
|
|
605
|
|
|
or r.responsible_id = {$_SESSION[VAR_USERID]} and |
606
|
|
|
f.field_id in (select f.field_id |
607
|
|
|
from tbl_fields f |
608
|
|
|
where f.responsible_perm >= 1) |
609
|
|
|
|
610
|
|
|
or f.field_id in (select f.field_id |
611
|
|
|
from tbl_fields f |
612
|
|
|
where f.registered_perm >= 1 or f.guest_access = 1) |
613
|
|
|
SQL; |
614
|
|
|
|
615
|
|
|
// Generate columns of the current view. |
616
|
|
|
|
617
|
|
|
foreach ($columns as $i => $column) |
618
|
|
|
{ |
619
|
|
|
$i += 1; |
620
|
|
|
|
621
|
|
|
switch ($column['column_type']) |
622
|
|
|
{ |
623
|
|
View Code Duplication |
case COLUMN_TYPE_ID: |
624
|
|
|
|
625
|
|
|
array_push($clause_select, 'r.template_prefix'); |
626
|
|
|
|
627
|
|
|
if ($i == $sort) |
628
|
|
|
{ |
629
|
|
|
array_push($clause_order, 'record_id asc'); |
630
|
|
|
} |
631
|
|
|
elseif (-$i == $sort) |
632
|
|
|
{ |
633
|
|
|
array_push($clause_order, 'record_id desc'); |
634
|
|
|
} |
635
|
|
|
|
636
|
|
|
break; |
637
|
|
|
|
638
|
|
View Code Duplication |
case COLUMN_TYPE_STATE_ABBR: |
639
|
|
|
|
640
|
|
|
array_push($clause_select, 'r.state_abbr'); |
641
|
|
|
|
642
|
|
|
if ($i == $sort) |
643
|
|
|
{ |
644
|
|
|
array_push($clause_order, 'state_abbr asc'); |
645
|
|
|
} |
646
|
|
|
elseif (-$i == $sort) |
647
|
|
|
{ |
648
|
|
|
array_push($clause_order, 'state_abbr desc'); |
649
|
|
|
} |
650
|
|
|
|
651
|
|
|
break; |
652
|
|
|
|
653
|
|
View Code Duplication |
case COLUMN_TYPE_PROJECT: |
654
|
|
|
|
655
|
|
|
array_push($clause_select, 'r.project_name'); |
656
|
|
|
|
657
|
|
|
if ($i == $sort) |
658
|
|
|
{ |
659
|
|
|
array_push($clause_order, 'project_name asc'); |
660
|
|
|
} |
661
|
|
|
elseif (-$i == $sort) |
662
|
|
|
{ |
663
|
|
|
array_push($clause_order, 'project_name desc'); |
664
|
|
|
} |
665
|
|
|
|
666
|
|
|
break; |
667
|
|
|
|
668
|
|
View Code Duplication |
case COLUMN_TYPE_SUBJECT: |
669
|
|
|
|
670
|
|
|
array_push($clause_select, 'r.subject'); |
671
|
|
|
|
672
|
|
|
if ($i == $sort) |
673
|
|
|
{ |
674
|
|
|
array_push($clause_order, 'subject asc'); |
675
|
|
|
} |
676
|
|
|
elseif (-$i == $sort) |
677
|
|
|
{ |
678
|
|
|
array_push($clause_order, 'subject desc'); |
679
|
|
|
} |
680
|
|
|
|
681
|
|
|
break; |
682
|
|
|
|
683
|
|
View Code Duplication |
case COLUMN_TYPE_AUTHOR: |
684
|
|
|
|
685
|
|
|
array_push($clause_select, 'ac.fullname as author_fullname'); |
686
|
|
|
array_push($clause_join, 'left outer join tbl_accounts ac on ac.account_id = r.creator_id'); |
687
|
|
|
|
688
|
|
|
if ($i == $sort) |
689
|
|
|
{ |
690
|
|
|
array_push($clause_order, 'author_fullname asc'); |
691
|
|
|
} |
692
|
|
|
elseif (-$i == $sort) |
693
|
|
|
{ |
694
|
|
|
array_push($clause_order, 'author_fullname desc'); |
695
|
|
|
} |
696
|
|
|
|
697
|
|
|
break; |
698
|
|
|
|
699
|
|
View Code Duplication |
case COLUMN_TYPE_RESPONSIBLE: |
700
|
|
|
|
701
|
|
|
array_push($clause_select, 'ar.fullname as responsible_fullname'); |
702
|
|
|
array_push($clause_join, 'left outer join tbl_accounts ar on ar.account_id = r.responsible_id'); |
703
|
|
|
|
704
|
|
|
if ($i == $sort) |
705
|
|
|
{ |
706
|
|
|
array_push($clause_order, 'responsible_fullname asc'); |
707
|
|
|
} |
708
|
|
|
elseif (-$i == $sort) |
709
|
|
|
{ |
710
|
|
|
array_push($clause_order, 'responsible_fullname desc'); |
711
|
|
|
} |
712
|
|
|
|
713
|
|
|
break; |
714
|
|
|
|
715
|
|
View Code Duplication |
case COLUMN_TYPE_LAST_EVENT: |
716
|
|
|
|
717
|
|
|
if ($i == $sort) |
718
|
|
|
{ |
719
|
|
|
array_push($clause_order, 'change_time asc'); |
720
|
|
|
} |
721
|
|
|
elseif (-$i == $sort) |
722
|
|
|
{ |
723
|
|
|
array_push($clause_order, 'change_time desc'); |
724
|
|
|
} |
725
|
|
|
|
726
|
|
|
break; |
727
|
|
|
|
728
|
|
View Code Duplication |
case COLUMN_TYPE_AGE: |
729
|
|
|
|
730
|
|
|
array_push($clause_select, '(' . $time . ' - r.creation_time) as opened_age'); |
731
|
|
|
array_push($clause_select, '(r.closure_time - r.creation_time) as closed_age'); |
732
|
|
|
|
733
|
|
|
if ($i == $sort) |
734
|
|
|
{ |
735
|
|
|
array_push($clause_order, 'closed_age asc'); |
736
|
|
|
array_push($clause_order, 'opened_age asc'); |
737
|
|
|
} |
738
|
|
|
elseif (-$i == $sort) |
739
|
|
|
{ |
740
|
|
|
array_push($clause_order, 'closed_age desc'); |
741
|
|
|
array_push($clause_order, 'opened_age desc'); |
742
|
|
|
} |
743
|
|
|
|
744
|
|
|
break; |
745
|
|
|
|
746
|
|
View Code Duplication |
case COLUMN_TYPE_CREATION_DATE: |
747
|
|
|
|
748
|
|
|
array_push($clause_select, 'r.creation_time'); |
749
|
|
|
|
750
|
|
|
if ($i == $sort) |
751
|
|
|
{ |
752
|
|
|
array_push($clause_order, 'creation_time asc'); |
753
|
|
|
} |
754
|
|
|
elseif (-$i == $sort) |
755
|
|
|
{ |
756
|
|
|
array_push($clause_order, 'creation_time desc'); |
757
|
|
|
} |
758
|
|
|
|
759
|
|
|
break; |
760
|
|
|
|
761
|
|
View Code Duplication |
case COLUMN_TYPE_TEMPLATE: |
762
|
|
|
|
763
|
|
|
array_push($clause_select, 'r.template_name'); |
764
|
|
|
|
765
|
|
|
if ($i == $sort) |
766
|
|
|
{ |
767
|
|
|
array_push($clause_order, 'template_name asc'); |
768
|
|
|
} |
769
|
|
|
elseif (-$i == $sort) |
770
|
|
|
{ |
771
|
|
|
array_push($clause_order, 'template_name desc'); |
772
|
|
|
} |
773
|
|
|
|
774
|
|
|
break; |
775
|
|
|
|
776
|
|
View Code Duplication |
case COLUMN_TYPE_STATE_NAME: |
777
|
|
|
|
778
|
|
|
array_push($clause_select, 'r.state_name'); |
779
|
|
|
|
780
|
|
|
if ($i == $sort) |
781
|
|
|
{ |
782
|
|
|
array_push($clause_order, 'state_name asc'); |
783
|
|
|
} |
784
|
|
|
elseif (-$i == $sort) |
785
|
|
|
{ |
786
|
|
|
array_push($clause_order, 'state_name desc'); |
787
|
|
|
} |
788
|
|
|
|
789
|
|
|
break; |
790
|
|
|
|
791
|
|
View Code Duplication |
case COLUMN_TYPE_LAST_STATE: |
792
|
|
|
|
793
|
|
|
array_push($clause_select, 'st.state_time'); |
794
|
|
|
array_push($clause_from, '(select record_id, max(event_time) as state_time' . |
795
|
|
|
' from tbl_events' . |
796
|
|
|
' where event_type = 1 or event_type = 4' . |
797
|
|
|
' group by record_id) st'); |
798
|
|
|
array_push($clause_where, 'r.record_id = st.record_id'); |
799
|
|
|
|
800
|
|
|
if ($i == $sort) |
801
|
|
|
{ |
802
|
|
|
array_push($clause_order, 'state_time asc'); |
803
|
|
|
} |
804
|
|
|
elseif (-$i == $sort) |
805
|
|
|
{ |
806
|
|
|
array_push($clause_order, 'state_time desc'); |
807
|
|
|
} |
808
|
|
|
|
809
|
|
|
break; |
810
|
|
|
|
811
|
|
View Code Duplication |
case COLUMN_TYPE_FLOAT: |
812
|
|
|
|
813
|
|
|
array_push($clause_select, "v{$column['column_id']}.value{$column['column_id']}"); |
814
|
|
|
|
815
|
|
|
array_push($clause_join, |
816
|
|
|
"left outer join " . |
817
|
|
|
"(select r.record_id, flv.float_value as value{$column['column_id']} " . |
818
|
|
|
"from tbl_records r, tbl_states s, tbl_fields f, tbl_events e, tbl_field_values fv " . |
819
|
|
|
"left outer join tbl_float_values flv on fv.value_id = flv.value_id " . |
820
|
|
|
"where r.record_id = e.record_id and s.state_id = f.state_id and s.state_name = '{$column['state_name']}' and f.field_id = fv.field_id and f.field_name = '{$column['field_name']}' and f.field_type = " . FIELD_TYPE_FLOAT . " and e.event_id = fv.event_id and fv.is_latest = 1 and ({$sql_field_perms})) v{$column['column_id']} " . |
821
|
|
|
"on r.record_id = v{$column['column_id']}.record_id"); |
822
|
|
|
|
823
|
|
|
if ($i == $sort) |
824
|
|
|
{ |
825
|
|
|
array_push($clause_order, "value{$column['column_id']} asc"); |
826
|
|
|
} |
827
|
|
|
elseif (-$i == $sort) |
828
|
|
|
{ |
829
|
|
|
array_push($clause_order, "value{$column['column_id']} desc"); |
830
|
|
|
} |
831
|
|
|
|
832
|
|
|
break; |
833
|
|
|
|
834
|
|
View Code Duplication |
case COLUMN_TYPE_STRING: |
835
|
|
|
|
836
|
|
|
array_push($clause_select, "v{$column['column_id']}.value{$column['column_id']}"); |
837
|
|
|
|
838
|
|
|
array_push($clause_join, |
839
|
|
|
"left outer join " . |
840
|
|
|
"(select r.record_id, sv.string_value as value{$column['column_id']} " . |
841
|
|
|
"from tbl_records r, tbl_states s, tbl_fields f, tbl_events e, tbl_field_values fv " . |
842
|
|
|
"left outer join tbl_string_values sv on fv.value_id = sv.value_id " . |
843
|
|
|
"where r.record_id = e.record_id and s.state_id = f.state_id and s.state_name = '{$column['state_name']}' and f.field_id = fv.field_id and f.field_name = '{$column['field_name']}' and f.field_type = " . FIELD_TYPE_STRING . " and e.event_id = fv.event_id and fv.is_latest = 1 and ({$sql_field_perms})) v{$column['column_id']} " . |
844
|
|
|
"on r.record_id = v{$column['column_id']}.record_id"); |
845
|
|
|
|
846
|
|
|
if ($i == $sort) |
847
|
|
|
{ |
848
|
|
|
array_push($clause_order, "value{$column['column_id']} asc"); |
849
|
|
|
} |
850
|
|
|
elseif (-$i == $sort) |
851
|
|
|
{ |
852
|
|
|
array_push($clause_order, "value{$column['column_id']} desc"); |
853
|
|
|
} |
854
|
|
|
|
855
|
|
|
break; |
856
|
|
|
|
857
|
|
|
case COLUMN_TYPE_MULTILINED: |
858
|
|
|
|
859
|
|
|
$txtval = (DATABASE_DRIVER == DRIVER_MSSQL2K ? 'substring(tv.text_value, 1, 4000)' : 'substr(tv.text_value, 1, 4000)'); |
860
|
|
|
|
861
|
|
|
if (DATABASE_DRIVER == DRIVER_ORACLE9) |
862
|
|
|
{ |
863
|
|
|
array_push($clause_select, "to_char(v{$column['column_id']}.value{$column['column_id']}) as \"value{$column['column_id']}\""); |
864
|
|
|
} |
865
|
|
|
else |
866
|
|
|
{ |
867
|
|
|
array_push($clause_select, "v{$column['column_id']}.value{$column['column_id']}"); |
868
|
|
|
} |
869
|
|
|
|
870
|
|
|
array_push($clause_join, |
871
|
|
|
"left outer join " . |
872
|
|
|
"(select r.record_id, {$txtval} as value{$column['column_id']} " . |
873
|
|
|
"from tbl_records r, tbl_states s, tbl_fields f, tbl_events e, tbl_field_values fv " . |
874
|
|
|
"left outer join tbl_text_values tv on fv.value_id = tv.value_id " . |
875
|
|
|
"where r.record_id = e.record_id and s.state_id = f.state_id and s.state_name = '{$column['state_name']}' and f.field_id = fv.field_id and f.field_name = '{$column['field_name']}' and f.field_type = " . FIELD_TYPE_MULTILINED . " and e.event_id = fv.event_id and fv.is_latest = 1 and ({$sql_field_perms})) v{$column['column_id']} " . |
876
|
|
|
"on r.record_id = v{$column['column_id']}.record_id"); |
877
|
|
|
|
878
|
|
|
if ($i == $sort) |
879
|
|
|
{ |
880
|
|
|
array_push($clause_order, DATABASE_DRIVER == DRIVER_ORACLE9 |
881
|
|
|
? "to_char(value{$column['column_id']}) asc" |
882
|
|
|
: "value{$column['column_id']} asc"); |
883
|
|
|
} |
884
|
|
|
elseif (-$i == $sort) |
885
|
|
|
{ |
886
|
|
|
array_push($clause_order, DATABASE_DRIVER == DRIVER_ORACLE9 |
887
|
|
|
? "to_char(value{$column['column_id']}) desc" |
888
|
|
|
: "value{$column['column_id']} desc"); |
889
|
|
|
} |
890
|
|
|
|
891
|
|
|
break; |
892
|
|
|
|
893
|
|
View Code Duplication |
case COLUMN_TYPE_LIST_STRING: |
894
|
|
|
|
895
|
|
|
array_push($clause_select, "v{$column['column_id']}.value{$column['column_id']}"); |
896
|
|
|
|
897
|
|
|
array_push($clause_join, |
898
|
|
|
"left outer join " . |
899
|
|
|
"(select r.record_id, lv.str_value as value{$column['column_id']} " . |
900
|
|
|
"from tbl_records r, tbl_states s, tbl_fields f, tbl_events e, tbl_field_values fv " . |
901
|
|
|
"left outer join tbl_list_values lv on fv.field_id = lv.field_id and fv.value_id = lv.int_value " . |
902
|
|
|
"where r.record_id = e.record_id and s.state_id = f.state_id and s.state_name = '{$column['state_name']}' and f.field_id = fv.field_id and f.field_name = '{$column['field_name']}' and f.field_type = " . FIELD_TYPE_LIST . " and e.event_id = fv.event_id and fv.is_latest = 1 and ({$sql_field_perms})) v{$column['column_id']} " . |
903
|
|
|
"on r.record_id = v{$column['column_id']}.record_id"); |
904
|
|
|
|
905
|
|
|
if ($i == $sort) |
906
|
|
|
{ |
907
|
|
|
array_push($clause_order, "value{$column['column_id']} asc"); |
908
|
|
|
} |
909
|
|
|
elseif (-$i == $sort) |
910
|
|
|
{ |
911
|
|
|
array_push($clause_order, "value{$column['column_id']} desc"); |
912
|
|
|
} |
913
|
|
|
|
914
|
|
|
break; |
915
|
|
|
|
916
|
|
|
case COLUMN_TYPE_NUMBER: |
917
|
|
|
case COLUMN_TYPE_CHECKBOX: |
918
|
|
|
case COLUMN_TYPE_LIST_NUMBER: |
919
|
|
|
case COLUMN_TYPE_RECORD: |
920
|
|
|
case COLUMN_TYPE_DATE: |
921
|
|
|
case COLUMN_TYPE_DURATION: |
922
|
|
|
|
923
|
|
|
$types = array |
924
|
|
|
( |
925
|
|
|
COLUMN_TYPE_NUMBER => FIELD_TYPE_NUMBER, |
926
|
|
|
COLUMN_TYPE_CHECKBOX => FIELD_TYPE_CHECKBOX, |
927
|
|
|
COLUMN_TYPE_LIST_NUMBER => FIELD_TYPE_LIST, |
928
|
|
|
COLUMN_TYPE_RECORD => FIELD_TYPE_RECORD, |
929
|
|
|
COLUMN_TYPE_DATE => FIELD_TYPE_DATE, |
930
|
|
|
COLUMN_TYPE_DURATION => FIELD_TYPE_DURATION, |
931
|
|
|
); |
932
|
|
|
|
933
|
|
|
array_push($clause_select, "v{$column['column_id']}.value{$column['column_id']}"); |
934
|
|
|
|
935
|
|
|
array_push($clause_join, |
936
|
|
|
"left outer join " . |
937
|
|
|
"(select r.record_id, fv.value_id as value{$column['column_id']} " . |
938
|
|
|
"from tbl_records r, tbl_states s, tbl_fields f, tbl_events e, tbl_field_values fv " . |
939
|
|
|
"where r.record_id = e.record_id and s.state_id = f.state_id and s.state_name = '{$column['state_name']}' and f.field_id = fv.field_id and f.field_name = '{$column['field_name']}' and f.field_type = {$types[$column['column_type']]} and e.event_id = fv.event_id and fv.is_latest = 1 and ({$sql_field_perms})) v{$column['column_id']} " . |
940
|
|
|
"on r.record_id = v{$column['column_id']}.record_id"); |
941
|
|
|
|
942
|
|
|
if ($i == $sort) |
943
|
|
|
{ |
944
|
|
|
array_push($clause_order, "value{$column['column_id']} asc"); |
945
|
|
|
} |
946
|
|
|
elseif (-$i == $sort) |
947
|
|
|
{ |
948
|
|
|
array_push($clause_order, "value{$column['column_id']} desc"); |
949
|
|
|
} |
950
|
|
|
|
951
|
|
|
break; |
952
|
|
|
|
953
|
|
|
default: |
954
|
|
|
debug_write_log(DEBUG_WARNING, '[records_list] Unknown column type = ' . $column['column_type']); |
955
|
|
|
} |
956
|
|
|
} |
957
|
|
|
|
958
|
|
|
// Add default sorting. |
959
|
|
|
|
960
|
|
|
if ($sort < 0) |
961
|
|
|
{ |
962
|
|
|
array_push($clause_order, 'record_id desc'); |
963
|
|
|
} |
964
|
|
|
else |
965
|
|
|
{ |
966
|
|
|
array_push($clause_order, 'record_id asc'); |
967
|
|
|
} |
968
|
|
|
|
969
|
|
|
// Bring it all together. |
970
|
|
|
|
971
|
|
|
array_push($clause_from, "({$sql}) r"); |
972
|
|
|
|
973
|
|
|
$sql = 'select ' . implode(', ', array_unique($clause_select)) . |
974
|
|
|
' from ' . implode(', ', array_unique($clause_from)) . |
975
|
|
|
' ' . implode(' ', array_unique($clause_join)); |
976
|
|
|
|
977
|
|
|
if (count($clause_where) != 0) |
978
|
|
|
{ |
979
|
|
|
$sql .= ' where ' . implode(' and ', array_unique($clause_where)); |
980
|
|
|
} |
981
|
|
|
|
982
|
|
|
$sql .= ' order by ' . implode(', ', array_unique($clause_order)); |
983
|
|
|
|
984
|
|
|
return new CRecordset($sql); |
985
|
|
|
} |
986
|
|
|
|
987
|
|
|
/** |
988
|
|
|
* Returns {@link CRecordset DAL recordset} which contains number of created records per week for specified project. |
989
|
|
|
* Each row of returned recordset contains two fields: |
990
|
|
|
* <ul> |
991
|
|
|
* <li><b>week</b> - number of week after {@link http://en.wikipedia.org/wiki/Unix_time Unix Epoch}.</li> |
992
|
|
|
* <li><b>amount</b> - number of records, created during this week</li> |
993
|
|
|
* </ul> |
994
|
|
|
* |
995
|
|
|
* @param int $id ID of project which records should be counted. |
996
|
|
|
* @return CRecordset Recordset with list of counts. |
997
|
|
|
*/ |
998
|
|
View Code Duplication |
function record_opened ($id) |
999
|
|
|
{ |
1000
|
|
|
debug_write_log(DEBUG_TRACE, '[record_opened]'); |
1001
|
|
|
debug_write_log(DEBUG_DUMP, '[record_opened] $id = ' . $id); |
1002
|
|
|
|
1003
|
|
|
return dal_query('records/opened.sql', $id, date('Z'), (DATABASE_DRIVER == DRIVER_ORACLE9 ? 'ceil' : 'ceiling')); |
1004
|
|
|
} |
1005
|
|
|
|
1006
|
|
|
/** |
1007
|
|
|
* Returns {@link CRecordset DAL recordset} which contains number of closed records per week for specified project. |
1008
|
|
|
* Each row of returned recordset contains two fields: |
1009
|
|
|
* <ul> |
1010
|
|
|
* <li><b>week</b> - number of week after {@link http://en.wikipedia.org/wiki/Unix_time Unix Epoch}.</li> |
1011
|
|
|
* <li><b>amount</b> - number of records, closed during this week</li> |
1012
|
|
|
* </ul> |
1013
|
|
|
* |
1014
|
|
|
* @param int $id ID of project which records should be counted. |
1015
|
|
|
* @return CRecordset Recordset with list of counts. |
1016
|
|
|
*/ |
1017
|
|
View Code Duplication |
function record_closed ($id) |
1018
|
|
|
{ |
1019
|
|
|
debug_write_log(DEBUG_TRACE, '[record_closed]'); |
1020
|
|
|
debug_write_log(DEBUG_DUMP, '[record_closed] $id = ' . $id); |
1021
|
|
|
|
1022
|
|
|
return dal_query('records/closed.sql', $id, date('Z'), (DATABASE_DRIVER == DRIVER_ORACLE9 ? 'ceil' : 'ceiling')); |
1023
|
|
|
} |
1024
|
|
|
|
1025
|
|
|
/** |
1026
|
|
|
* Validates record information (including all custom fields) before creation, modification, or changing state. |
1027
|
|
|
* |
1028
|
|
|
* @param int $operation Code of operation: |
1029
|
|
|
* <ul> |
1030
|
|
|
* <li>{@link OPERATION_CREATE_RECORD} - new record is going to be created</li> |
1031
|
|
|
* <li>{@link OPERATION_MODIFY_RECORD} - record is going to be modified</li> |
1032
|
|
|
* <li>{@link OPERATION_CHANGE_STATE} - state of record is going to be changed</li> |
1033
|
|
|
* </ul> |
1034
|
|
|
* @param string $subject Subject of the record (ignored on state change). |
1035
|
|
|
* @param int $record_id Record ID (should be NULL on creation). |
1036
|
|
|
* @param int $state_id ID of new state (current on modification). |
1037
|
|
|
* @param int $creator_id Author of record (used only on modification, otherwise ignored). |
1038
|
|
|
* @param int $responsible_id Responsible of record (used only on modification, otherwise ignored). |
1039
|
|
|
* @return int Error code: |
1040
|
|
|
* <ul> |
1041
|
|
|
* <li>{@link NO_ERROR} - data are valid</li> |
1042
|
|
|
* <li>{@link ERROR_INCOMPLETE_FORM} - at least one of required field is empty</li> |
1043
|
|
|
* <li>{@link ERROR_INVALID_INTEGER_VALUE} - value of some custom field of {@link FIELD_TYPE_NUMBER}, {@link FIELD_TYPE_LIST}, or {@link FIELD_TYPE_RECORD} type is not an integer</li> |
1044
|
|
|
* <li>{@link ERROR_INTEGER_VALUE_OUT_OF_RANGE} - value of some custom field of {@link FIELD_TYPE_NUMBER} or {@link FIELD_TYPE_LIST} type is out of valid range</li> |
1045
|
|
|
* <li>{@link ERROR_VALUE_FAILS_REGEX_CHECK} - value of some custom field of {@link FIELD_TYPE_STRING} or {@link FIELD_TYPE_MULTILINED} type fails the custom PCRE check</li> |
1046
|
|
|
* <li>{@link ERROR_RECORD_NOT_FOUND} - value of some custom field of {@link FIELD_TYPE_RECORD} type is not an ID of existing record</li> |
1047
|
|
|
* <li>{@link ERROR_INVALID_DATE_VALUE} - value of some custom field of {@link FIELD_TYPE_DATE} type is not a valid date value</li> |
1048
|
|
|
* <li>{@link ERROR_DATE_VALUE_OUT_OF_RANGE} - value of some custom field of {@link FIELD_TYPE_DATE} type is out of valid range</li> |
1049
|
|
|
* <li>{@link ERROR_INVALID_TIME_VALUE} - value of some custom field of {@link FIELD_TYPE_DURATION} type is not a valid duration value</li> |
1050
|
|
|
* <li>{@link ERROR_TIME_VALUE_OUT_OF_RANGE} - value of some custom field of {@link FIELD_TYPE_DURATION} type is out of valid range</li> |
1051
|
|
|
* </ul> |
1052
|
|
|
*/ |
1053
|
|
|
function record_validate ($operation, $subject, $record_id, $state_id, $creator_id = 0, $responsible_id = 0) |
1054
|
|
|
{ |
1055
|
|
|
debug_write_log(DEBUG_TRACE, '[record_validate]'); |
1056
|
|
|
debug_write_log(DEBUG_DUMP, '[record_validate] $operation = ' . $operation); |
1057
|
|
|
debug_write_log(DEBUG_DUMP, '[record_validate] $subject = ' . $subject); |
1058
|
|
|
debug_write_log(DEBUG_DUMP, '[record_validate] $record_id = ' . $record_id); |
1059
|
|
|
debug_write_log(DEBUG_DUMP, '[record_validate] $state_id = ' . $state_id); |
1060
|
|
|
debug_write_log(DEBUG_DUMP, '[record_validate] $creator_id = ' . $creator_id); |
1061
|
|
|
debug_write_log(DEBUG_DUMP, '[record_validate] $responsible_id = ' . $responsible_id); |
1062
|
|
|
|
1063
|
|
|
// Check the subject. |
1064
|
|
|
if ($operation != OPERATION_CHANGE_STATE && |
1065
|
|
|
ustrlen($subject) == 0) |
1066
|
|
|
{ |
1067
|
|
|
debug_write_log(DEBUG_NOTICE, '[record_validate] At least one required field is empty.'); |
1068
|
|
|
return ERROR_INCOMPLETE_FORM; |
1069
|
|
|
} |
1070
|
|
|
|
1071
|
|
|
// Get the list of custom fields. |
1072
|
|
|
if ($operation != OPERATION_MODIFY_RECORD) |
1073
|
|
|
{ |
1074
|
|
|
$rs = dal_query('fields/list.sql', $state_id, 'field_order'); |
1075
|
|
|
} |
1076
|
|
|
else |
1077
|
|
|
{ |
1078
|
|
|
$rs = dal_query('records/flist.sql', |
1079
|
|
|
$record_id, |
1080
|
|
|
$state_id, |
1081
|
|
|
$creator_id, |
1082
|
|
|
is_null($responsible_id) ? 0 : $responsible_id, |
1083
|
|
|
$_SESSION[VAR_USERID], |
1084
|
|
|
FIELD_ALLOW_TO_WRITE); |
1085
|
|
|
} |
1086
|
|
|
|
1087
|
|
|
// Check value candidates of all fields. |
1088
|
|
|
// Values of all custom fields are passed to the function via $_REQUEST. |
1089
|
|
|
while (($row = $rs->fetch())) |
1090
|
|
|
{ |
1091
|
|
|
$name = 'field' . $row['field_id']; |
1092
|
|
|
$value = ($row['field_type'] == FIELD_TYPE_CHECKBOX ? isset($_REQUEST[$name]) : trim(try_request($name))); |
1093
|
|
|
|
1094
|
|
|
// Required custom fields must be filled in. |
1095
|
|
|
if ($row['is_required'] && |
1096
|
|
|
$row['field_type'] != FIELD_TYPE_CHECKBOX && |
1097
|
|
|
ustrlen($value) == 0) |
|
Since your code implements the magic setter
_set
, this function will be called for any write access on an undefined variable. You can add the@property
annotation to your class or interface to document the existence of this variable.Since the property has write access only, you can use the @property-write annotation instead.
Of course, you may also just have mistyped another name, in which case you should fix the error.
See also the PhpDoc documentation for @property.