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 var $fields = array(); // Array of custom fields for group.
40 var $options = array(); // Array of options for a dropdown custom field.
43 function __construct() {
45 $mdb2 = getConnection();
47 $group_id = $user->getGroup();
48 $org_id = $user->org_id;
51 $sql = "select id, type, label, required from tt_custom_fields".
52 " where group_id = $group_id and org_id = $org_id and status = 1 and type > 0";
53 $res = $mdb2->query($sql);
54 if (!is_a($res, 'PEAR_Error')) {
55 while ($val = $res->fetchRow()) {
56 $this->fields[] = array('id'=>$val['id'],'type'=>$val['type'],'label'=>$val['label'],'required'=>$val['required'],'value'=>'');
60 // If we have a dropdown obtain options for it.
61 if ((count($this->fields) > 0) && ($this->fields[0]['type'] == CustomFields::TYPE_DROPDOWN)) {
63 $sql = "select id, value from tt_custom_field_options".
64 " where field_id = ".$this->fields[0]['id']." and group_id = $group_id and org_id = $org_id and status = 1 order by value";
65 $res = $mdb2->query($sql);
66 if (!is_a($res, 'PEAR_Error')) {
67 while ($val = $res->fetchRow()) {
68 $this->options[$val['id']] = $val['value'];
74 function insert($log_id, $field_id, $option_id, $value) {
76 $mdb2 = getConnection();
78 $group_id = $user->getGroup();
79 $org_id = $user->org_id;
81 $sql = "insert into tt_custom_field_log (group_id, org_id, log_id, field_id, option_id, value)".
82 " values($group_id, $org_id, $log_id, $field_id, ".$mdb2->quote($option_id).", ".$mdb2->quote($value).")";
83 $affected = $mdb2->exec($sql);
84 return (!is_a($affected, 'PEAR_Error'));
87 function update($log_id, $field_id, $option_id, $value) {
89 return true; // Nothing to update.
91 // Remove older custom field values, if any.
92 $res = $this->delete($log_id);
96 if (!$value && !$option_id)
97 return true; // Do not insert NULL values.
99 return $this->insert($log_id, $field_id, $option_id, $value);
102 function delete($log_id) {
104 $mdb2 = getConnection();
106 $group_id = $user->getGroup();
107 $org_id = $user->org_id;
109 $sql = "update tt_custom_field_log set status = null".
110 " where log_id = $log_id and group_id = $group_id and org_id = $org_id";
111 $affected = $mdb2->exec($sql);
112 return (!is_a($affected, 'PEAR_Error'));
115 function get($log_id) {
117 $mdb2 = getConnection();
119 $group_id = $user->getGroup();
120 $org_id = $user->org_id;
122 $sql = "select id, field_id, option_id, value from tt_custom_field_log".
123 " where log_id = $log_id and group_id = $group_id and org_id = $org_id and status = 1";
124 $res = $mdb2->query($sql);
125 if (!is_a($res, 'PEAR_Error')) {
127 while ($val = $res->fetchRow()) {
135 // insertOption adds a new option to a custom field.
136 static function insertOption($field_id, $option_name) {
138 $mdb2 = getConnection();
140 $group_id = $user->getGroup();
141 $org_id = $user->org_id;
143 // Check if the option exists.
145 $sql = "select id from tt_custom_field_options".
146 " where field_id = $field_id and group_id = $group_id and org_id = $org_id and value = ".$mdb2->quote($option_name);
147 $res = $mdb2->query($sql);
148 if (is_a($res, 'PEAR_Error'))
150 if ($val = $res->fetchRow()) $id = $val['id'];
154 $sql = "insert into tt_custom_field_options (group_id, org_id, field_id, value)".
155 " values($group_id, $org_id, $field_id, ".$mdb2->quote($option_name).")";
156 $affected = $mdb2->exec($sql);
157 if (is_a($affected, 'PEAR_Error'))
163 // updateOption updates option name.
164 static function updateOption($id, $option_name) {
166 $mdb2 = getConnection();
168 $group_id = $user->getGroup();
169 $org_id = $user->org_id;
171 $sql = "update tt_custom_field_options set value = ".$mdb2->quote($option_name).
172 " where id = $id and group_id = $group_id and org_id = $org_id";
173 $affected = $mdb2->exec($sql);
174 return (!is_a($affected, 'PEAR_Error'));
177 // delete Option deletes an option and all custom field log entries that used it.
178 static function deleteOption($id) {
180 $mdb2 = getConnection();
182 $group_id = $user->getGroup();
183 $org_id = $user->org_id;
185 $field_id = CustomFields::getFieldIdForOption($id);
186 if (!$field_id) return false;
188 // Delete log entries with this option. TODO: why? Research impact.
189 $sql = "update tt_custom_field_log set status = null".
190 " where field_id = $field_id and group_id = $group_id and org_id = $org_id and value = ".$mdb2->quote($id);
191 $affected = $mdb2->exec($sql);
192 if (is_a($affected, 'PEAR_Error'))
195 // Delete the option.
196 $sql = "update tt_custom_field_options set status = null".
197 " where id = $id and group_id = $group_id and org_id = $org_id";
198 $affected = $mdb2->exec($sql);
199 return (!is_a($affected, 'PEAR_Error'));
202 // getOptions returns an array of options for a custom field.
203 static function getOptions($field_id) {
205 $mdb2 = getConnection();
207 $group_id = $user->getGroup();
208 $org_id = $user->org_id;
211 $sql = "select id, value from tt_custom_field_options".
212 " where field_id = $field_id and group_id = $group_id and org_id = $org_id and status = 1 order by value";
213 $res = $mdb2->query($sql);
214 if (!is_a($res, 'PEAR_Error')) {
216 while ($val = $res->fetchRow()) {
217 $options[$val['id']] = $val['value'];
224 // getOptionName returns an option name for a custom field.
225 static function getOptionName($id) {
227 $mdb2 = getConnection();
229 $group_id = $user->getGroup();
230 $org_id = $user->org_id;
232 $sql = "select value from tt_custom_field_options".
233 " where id = $id and group_id = $group_id and org_id = $org_id and status = 1";
234 $res = $mdb2->query($sql);
235 if (!is_a($res, 'PEAR_Error')) {
236 $val = $res->fetchRow();
237 $name = $val['value'];
243 // getFields returns an array of custom fields for group.
244 static function getFields() {
246 $mdb2 = getConnection();
248 $group_id = $user->getGroup();
249 $org_id = $user->org_id;
252 $sql = "select id, entity_type, type, label from tt_custom_fields".
253 " where group_id = $group_id and org_id = $org_id and status = 1 and type > 0";
254 $res = $mdb2->query($sql);
255 if (!is_a($res, 'PEAR_Error')) {
256 while ($val = $res->fetchRow()) {
264 // getField returns a custom field.
265 static function getField($id) {
267 $mdb2 = getConnection();
269 $group_id = $user->getGroup();
270 $org_id = $user->org_id;
272 $sql = "select label, type, required from tt_custom_fields".
273 " where id = $id and group_id = $group_id and org_id = $org_id";
274 $res = $mdb2->query($sql);
275 if (!is_a($res, 'PEAR_Error')) {
276 $val = $res->fetchRow();
284 // getFieldIdForOption returns field id from an associated option id.
285 static function getFieldIdForOption($option_id) {
287 $mdb2 = getConnection();
289 $group_id = $user->getGroup();
290 $org_id = $user->org_id;
292 $sql = "select field_id from tt_custom_field_options".
293 " where id = $option_id and group_id = $group_id and org_id = $org_id";
294 $res = $mdb2->query($sql);
295 if (!is_a($res, 'PEAR_Error')) {
296 $val = $res->fetchRow();
297 $field_id = $val['field_id'];
303 // The insertField inserts a custom field for group.
304 static function insertField($field_name, $entity_type, $field_type, $required) {
306 $mdb2 = getConnection();
308 $group_id = $user->getGroup();
309 $org_id = $user->org_id;
311 $sql = "insert into tt_custom_fields (group_id, org_id, entity_type, type, label, required, status)".
312 " values($group_id, $org_id, $entity_type, $field_type, ".$mdb2->quote($field_name).", $required, 1)";
313 $affected = $mdb2->exec($sql);
314 return (!is_a($affected, 'PEAR_Error'));
317 // The updateField updates custom field for group.
318 static function updateField($id, $name, $type, $required) {
320 $mdb2 = getConnection();
322 $group_id = $user->getGroup();
323 $org_id = $user->org_id;
325 $sql = "update tt_custom_fields set label = ".$mdb2->quote($name).", type = $type, required = $required".
326 " where id = $id and group_id = $group_id and org_id = $org_id";
327 $affected = $mdb2->exec($sql);
328 return (!is_a($affected, 'PEAR_Error'));
331 // The deleteField deletes a custom field, its options and log entries for group.
332 static function deleteField($field_id) {
334 $mdb2 = getConnection();
336 $group_id = $user->getGroup();
337 $org_id = $user->org_id;
339 // Mark log entries as deleted. TODO: why are we doing this? Research impact.
340 $sql = "update tt_custom_field_log set status = null".
341 " where field_id = $field_id and group_id = $group_id and org_id = $org_id";
342 $affected = $mdb2->exec($sql);
343 if (is_a($affected, 'PEAR_Error'))
346 // Mark field options as deleted.
347 $sql = "update tt_custom_field_options set status = null".
348 " where field_id = $field_id and group_id = $group_id and org_id = $org_id";
349 $affected = $mdb2->exec($sql);
350 if (is_a($affected, 'PEAR_Error'))
353 // Mark custom field as deleted.
354 $sql = "update tt_custom_fields set status = null".
355 " where id = $field_id and group_id = $group_id and org_id = $org_id";
356 $affected = $mdb2->exec($sql);
357 return (!is_a($affected, 'PEAR_Error'));