2 // +----------------------------------------------------------------------+
3 // | Anuko Time Tracker
4 // +----------------------------------------------------------------------+
5 // | Copyright (c) Anuko International Ltd. (https://www.anuko.com)
6 // +----------------------------------------------------------------------+
7 // | LIBERAL FREEWARE LICENSE: This source code document may be used
8 // | by anyone for any purpose, and freely redistributed alone or in
9 // | combination with other software, provided that the license is obeyed.
11 // | There are only two ways to violate the license:
13 // | 1. To redistribute this code in source form, with the copyright
14 // | notice or license removed or altered. (Distributing in compiled
15 // | forms without embedded copyright notices is permitted).
17 // | 2. To redistribute modified versions of this code in *any* form
18 // | that bears insufficient indications that the modifications are
19 // | not the work of the original author(s).
21 // | This license applies to this document only, not any other software
22 // | that it may be combined with.
24 // +----------------------------------------------------------------------+
26 // | https://www.anuko.com/time_tracker/credits.htm
27 // +----------------------------------------------------------------------+
31 // Definitions of custom field types.
32 const ENTITY_TIME = 1; // Field is associated with time entries.
33 const ENTITY_USER = 2; // Field is associated with users.
34 const ENTITY_PROJECT = 3; // Field is associated with projects.
36 const TYPE_TEXT = 1; // A text field.
37 const TYPE_DROPDOWN = 2; // A dropdown field with pre-defined values.
39 // TODO: replace $fields with entity-specific arrays: timeFields, userFields, etc.
40 var $fields = array(); // Array of custom fields for group.
42 // Refactoring ongoing...
43 var $timeFields = null;
44 var $userFields = null;
45 var $projectFields = null;
48 function __construct() {
50 $mdb2 = getConnection();
52 $group_id = $user->getGroup();
53 $org_id = $user->org_id;
56 $sql = "select id, entity_type, type, label, required from tt_custom_fields".
57 " where group_id = $group_id and org_id = $org_id and status = 1 and type > 0";
58 $res = $mdb2->query($sql);
59 if (!is_a($res, 'PEAR_Error')) {
60 while ($val = $res->fetchRow()) {
61 $this->fields[] = array('id'=>$val['id'],'type'=>$val['type'],'label'=>$val['label'],'required'=>$val['required'],'value'=>'');
62 if (CustomFields::ENTITY_TIME == $val['entity_type'])
63 $this->timeFields[] = $val;
64 else if (CustomFields::ENTITY_USER == $val['entity_type'])
65 $this->userFields[] = $val;
66 else if (CustomFields::ENTITY_PROJECT == $val['entity_type'])
67 $this->projectFields[] = $val;
72 function insert($log_id, $field_id, $option_id, $value) {
74 $mdb2 = getConnection();
76 $group_id = $user->getGroup();
77 $org_id = $user->org_id;
79 $sql = "insert into tt_custom_field_log (group_id, org_id, log_id, field_id, option_id, value)".
80 " values($group_id, $org_id, $log_id, $field_id, ".$mdb2->quote($option_id).", ".$mdb2->quote($value).")";
81 $affected = $mdb2->exec($sql);
82 return (!is_a($affected, 'PEAR_Error'));
85 function update($log_id, $field_id, $option_id, $value) {
87 return true; // Nothing to update.
89 // Remove older custom field values, if any.
90 $res = $this->delete($log_id);
94 if (!$value && !$option_id)
95 return true; // Do not insert NULL values.
97 return $this->insert($log_id, $field_id, $option_id, $value);
100 function delete($log_id) {
102 $mdb2 = getConnection();
104 $group_id = $user->getGroup();
105 $org_id = $user->org_id;
107 $sql = "update tt_custom_field_log set status = null".
108 " where log_id = $log_id and group_id = $group_id and org_id = $org_id";
109 $affected = $mdb2->exec($sql);
110 return (!is_a($affected, 'PEAR_Error'));
113 function get($log_id) {
115 $mdb2 = getConnection();
117 $group_id = $user->getGroup();
118 $org_id = $user->org_id;
120 $sql = "select id, field_id, option_id, value from tt_custom_field_log".
121 " where log_id = $log_id and group_id = $group_id and org_id = $org_id and status = 1";
122 $res = $mdb2->query($sql);
123 if (!is_a($res, 'PEAR_Error')) {
125 while ($val = $res->fetchRow()) {
133 // insertOption adds a new option to a custom field.
134 static function insertOption($field_id, $option_name) {
136 $mdb2 = getConnection();
138 $group_id = $user->getGroup();
139 $org_id = $user->org_id;
141 // Check if the option exists.
143 $sql = "select id from tt_custom_field_options".
144 " where field_id = $field_id and group_id = $group_id and org_id = $org_id and value = ".$mdb2->quote($option_name);
145 $res = $mdb2->query($sql);
146 if (is_a($res, 'PEAR_Error'))
148 if ($val = $res->fetchRow()) $id = $val['id'];
152 $sql = "insert into tt_custom_field_options (group_id, org_id, field_id, value)".
153 " values($group_id, $org_id, $field_id, ".$mdb2->quote($option_name).")";
154 $affected = $mdb2->exec($sql);
155 if (is_a($affected, 'PEAR_Error'))
161 // updateOption updates option name.
162 static function updateOption($id, $option_name) {
164 $mdb2 = getConnection();
166 $group_id = $user->getGroup();
167 $org_id = $user->org_id;
169 $sql = "update tt_custom_field_options set value = ".$mdb2->quote($option_name).
170 " where id = $id and group_id = $group_id and org_id = $org_id";
171 $affected = $mdb2->exec($sql);
172 return (!is_a($affected, 'PEAR_Error'));
175 // delete Option deletes an option and all custom field log entries that used it.
176 static function deleteOption($id) {
178 $mdb2 = getConnection();
180 $group_id = $user->getGroup();
181 $org_id = $user->org_id;
183 $field_id = CustomFields::getFieldIdForOption($id);
184 if (!$field_id) return false;
186 // Delete log entries with this option. TODO: why? Research impact.
187 $sql = "update tt_custom_field_log set status = null".
188 " where field_id = $field_id and group_id = $group_id and org_id = $org_id and value = ".$mdb2->quote($id);
189 $affected = $mdb2->exec($sql);
190 if (is_a($affected, 'PEAR_Error'))
193 // Delete the option.
194 $sql = "update tt_custom_field_options set status = null".
195 " where id = $id and group_id = $group_id and org_id = $org_id";
196 $affected = $mdb2->exec($sql);
197 return (!is_a($affected, 'PEAR_Error'));
200 // getOptions returns an array of options for a custom field.
201 static function getOptions($field_id) {
203 $mdb2 = getConnection();
205 $group_id = $user->getGroup();
206 $org_id = $user->org_id;
209 $sql = "select id, value from tt_custom_field_options".
210 " where field_id = $field_id and group_id = $group_id and org_id = $org_id and status = 1 order by value";
211 $res = $mdb2->query($sql);
212 if (!is_a($res, 'PEAR_Error')) {
214 while ($val = $res->fetchRow()) {
215 $options[$val['id']] = $val['value'];
222 // getOptionName returns an option name for a custom field.
223 static function getOptionName($id) {
225 $mdb2 = getConnection();
227 $group_id = $user->getGroup();
228 $org_id = $user->org_id;
230 $sql = "select value from tt_custom_field_options".
231 " where id = $id and group_id = $group_id and org_id = $org_id and status = 1";
232 $res = $mdb2->query($sql);
233 if (!is_a($res, 'PEAR_Error')) {
234 $val = $res->fetchRow();
235 $name = $val['value'];
241 // getFields returns an array of custom fields for group.
242 static function getFields() {
244 $mdb2 = getConnection();
246 $group_id = $user->getGroup();
247 $org_id = $user->org_id;
250 $sql = "select id, entity_type, type, label from tt_custom_fields".
251 " where group_id = $group_id and org_id = $org_id and status = 1 and type > 0";
252 $res = $mdb2->query($sql);
253 if (!is_a($res, 'PEAR_Error')) {
254 while ($val = $res->fetchRow()) {
262 // getField returns a custom field.
263 static function getField($id) {
265 $mdb2 = getConnection();
267 $group_id = $user->getGroup();
268 $org_id = $user->org_id;
270 $sql = "select label, entity_type, type, required from tt_custom_fields".
271 " where id = $id and group_id = $group_id and org_id = $org_id";
272 $res = $mdb2->query($sql);
273 if (!is_a($res, 'PEAR_Error')) {
274 $val = $res->fetchRow();
282 // getFieldIdForOption returns field id from an associated option id.
283 static function getFieldIdForOption($option_id) {
285 $mdb2 = getConnection();
287 $group_id = $user->getGroup();
288 $org_id = $user->org_id;
290 $sql = "select field_id from tt_custom_field_options".
291 " where id = $option_id and group_id = $group_id and org_id = $org_id";
292 $res = $mdb2->query($sql);
293 if (!is_a($res, 'PEAR_Error')) {
294 $val = $res->fetchRow();
295 $field_id = $val['field_id'];
301 // The insertField inserts a custom field for group.
302 static function insertField($field_name, $entity_type, $field_type, $required) {
304 $mdb2 = getConnection();
306 $group_id = $user->getGroup();
307 $org_id = $user->org_id;
309 $sql = "insert into tt_custom_fields (group_id, org_id, entity_type, type, label, required, status)".
310 " values($group_id, $org_id, $entity_type, $field_type, ".$mdb2->quote($field_name).", $required, 1)";
311 $affected = $mdb2->exec($sql);
312 return (!is_a($affected, 'PEAR_Error'));
315 // The updateField updates custom field for group.
316 static function updateField($id, $name, $type, $required) {
318 $mdb2 = getConnection();
320 $group_id = $user->getGroup();
321 $org_id = $user->org_id;
323 $sql = "update tt_custom_fields set label = ".$mdb2->quote($name).", type = $type, required = $required".
324 " where id = $id and group_id = $group_id and org_id = $org_id";
325 $affected = $mdb2->exec($sql);
326 return (!is_a($affected, 'PEAR_Error'));
329 // The deleteField deletes a custom field, its options and log entries for group.
330 static function deleteField($field_id) {
332 $mdb2 = getConnection();
334 $group_id = $user->getGroup();
335 $org_id = $user->org_id;
337 // Mark log entries as deleted. TODO: why are we doing this? Research impact.
338 $sql = "update tt_custom_field_log set status = null".
339 " where field_id = $field_id and group_id = $group_id and org_id = $org_id";
340 $affected = $mdb2->exec($sql);
341 if (is_a($affected, 'PEAR_Error'))
344 // Mark field options as deleted.
345 $sql = "update tt_custom_field_options set status = null".
346 " where field_id = $field_id and group_id = $group_id and org_id = $org_id";
347 $affected = $mdb2->exec($sql);
348 if (is_a($affected, 'PEAR_Error'))
351 // Mark custom field as deleted.
352 $sql = "update tt_custom_fields set status = null".
353 " where id = $field_id and group_id = $group_id and org_id = $org_id";
354 $affected = $mdb2->exec($sql);
355 return (!is_a($affected, 'PEAR_Error'));
358 // insertEntityFields - inserts entity custom fields into tt_entity_custom_fields.
359 function insertEntityFields($entity_type, $entity_id, $entityFields) {
360 foreach ($entityFields as $entityField) {
361 if (!$this->insertEntityField($entity_type, $entity_id, $entityField))
367 // insertEntityField - inserts a single entity custom field into tt_entity_custom_fields.
368 function insertEntityField($entity_type, $entity_id, $entityField) {
370 $mdb2 = getConnection();
372 $group_id = $user->getGroup();
373 $org_id = $user->org_id;
375 $created = 'now(), '.$mdb2->quote($_SERVER['REMOTE_ADDR']).', '.$user->id;
377 $field_id = (int) $entityField['field_id'];
379 $option_id = $entityField['type'] == CustomFields::TYPE_DROPDOWN ? (int) $entityField['value'] : null;
380 $value = $entityField['type'] == CustomFields::TYPE_TEXT ? $entityField['value'] : null;
382 // TODO: add a jon to protect from bogus option_ids in post.
383 $sql = "insert into tt_entity_custom_fields".
384 " (group_id, org_id, entity_type, entity_id, field_id, option_id, value, created, created_ip, created_by)".
385 " values($group_id, $org_id, $entity_type, $entity_id, $field_id, ".$mdb2->quote($option_id).", ".$mdb2->quote($value).", $created)";
386 $affected = $mdb2->exec($sql);
387 return (!is_a($affected, 'PEAR_Error'));
390 // updateEntityFields - updates entity custom fields in tt_entity_custom_fields table
391 // by doing a delete followed up by an insert.
392 function updateEntityFields($entity_type, $entity_id, $entityFields) {
393 $result = $this->deleteEntityFields($entity_type, $entity_id);
394 if (!$result) return false;
396 return $this->insertEntityFields($entity_type, $entity_id, $entityFields);
399 // deleteEntityFields - deletes entity custom fields (permanently).
400 // Note: deleting, rather than marking fields deleted is on purpose
401 // because we want to keep the table small after multiple entity edits.
402 function deleteEntityFields($entity_type, $entity_id) {
404 $mdb2 = getConnection();
406 $group_id = $user->getGroup();
407 $org_id = $user->org_id;
409 $modified_part = ', modified = now(), modified_ip = '.$mdb2->quote($_SERVER['REMOTE_ADDR']).', modified_by = '.$user->id;
411 $sql = "delete from tt_entity_custom_fields".
412 " where entity_type = $entity_type and entity_id = $entity_id".
413 " and group_id = $group_id and org_id = $org_id";
414 $affected = $mdb2->exec($sql);
415 return (!is_a($affected, 'PEAR_Error'));
418 // getEntityFieldValue - obtains entity custom field value from the database.
419 function getEntityFieldValue($entity_type, $entity_id, $field_id, $type) {
421 $mdb2 = getConnection();
423 $group_id = $user->getGroup();
424 $org_id = $user->org_id;
426 $sql = "select option_id, value from tt_entity_custom_fields".
427 " where entity_type = $entity_type and entity_id = $entity_id".
428 " and field_id = $field_id".
429 " and group_id = $group_id and org_id = $org_id and status = 1";
430 $res = $mdb2->query($sql);
431 if (!is_a($res, 'PEAR_Error')) {
432 if ($val = $res->fetchRow()) {
433 if (CustomFields::TYPE_DROPDOWN == $type)
434 return $val['option_id'];
435 if (CustomFields::TYPE_TEXT == $type)
436 return $val['value'];