2 // +----------------------------------------------------------------------+
3 // | Anuko Time Tracker
4 // +----------------------------------------------------------------------+
5 // | Copyright (c) Anuko International Ltd. (https://www.anuko.com)
6 // +----------------------------------------------------------------------+
10 // Definitions of custom field types.
12 const TYPE_TEXT = 1; // A text field.
13 const TYPE_DROPDOWN = 2; // A dropdown field with pre-defined values.
15 var $fields = array(); // Array of custom fields for team.
16 var $options = array(); // Array of options for a dropdown custom field.
19 function CustomFields($team_id) {
20 $mdb2 = getConnection();
23 $sql = "select id, type, label, required from tt_custom_fields where team_id = $team_id and status = 1 and type > 0";
24 $res = $mdb2->query($sql);
25 if (!is_a($res, 'PEAR_Error')) {
26 while ($val = $res->fetchRow()) {
27 $this->fields[] = array('id'=>$val['id'],'type'=>$val['type'],'label'=>$val['label'],'required'=>$val['required'],'value'=>'');
31 // If we have a dropdown obtain options for it.
32 if ((count($this->fields) > 0) && ($this->fields[0]['type'] == CustomFields::TYPE_DROPDOWN)) {
34 $sql = "select id, value from tt_custom_field_options where field_id = ".$this->fields[0]['id']." order by value";
35 $res = $mdb2->query($sql);
36 if (!is_a($res, 'PEAR_Error')) {
37 while ($val = $res->fetchRow()) {
38 $this->options[$val['id']] = $val['value'];
44 function insert($log_id, $field_id, $option_id, $value) {
46 $mdb2 = getConnection();
47 $sql = "insert into tt_custom_field_log (log_id, field_id, option_id, value) values($log_id, $field_id, ".$mdb2->quote($option_id).", ".$mdb2->quote($value).")";
48 $affected = $mdb2->exec($sql);
49 return (!is_a($affected, 'PEAR_Error'));
52 function update($log_id, $field_id, $option_id, $value) {
54 return true; // Nothing to update.
56 // Remove older custom field values, if any.
57 $res = $this->delete($log_id);
61 if (!$value && !$option_id)
62 return true; // Do not insert NULL values.
64 return $this->insert($log_id, $field_id, $option_id, $value);
67 function delete($log_id) {
69 $mdb2 = getConnection();
70 $sql = "update tt_custom_field_log set status = NULL where log_id = $log_id";
71 $affected = $mdb2->exec($sql);
72 return (!is_a($affected, 'PEAR_Error'));
75 function get($log_id) {
78 $mdb2 = getConnection();
79 $sql = "select id, field_id, option_id, value from tt_custom_field_log where log_id = $log_id and status = 1";
80 $res = $mdb2->query($sql);
81 if (!is_a($res, 'PEAR_Error')) {
82 while ($val = $res->fetchRow()) {
90 // insertOption adds a new option to a custom field.
91 static function insertOption($field_id, $option_name) {
93 $mdb2 = getConnection();
95 // Check if the option exists.
97 $sql = "select id from tt_custom_field_options where field_id = $field_id and value = ".$mdb2->quote($option_name);
98 $res = $mdb2->query($sql);
99 if (is_a($res, 'PEAR_Error'))
101 if ($val = $res->fetchRow()) $id = $val['id'];
105 $sql = "insert into tt_custom_field_options (field_id, value) values($field_id, ".$mdb2->quote($option_name).")";
106 $affected = $mdb2->exec($sql);
107 if (is_a($affected, 'PEAR_Error'))
113 // updateOption updates option name.
114 static function updateOption($id, $option_name) {
116 $mdb2 = getConnection();
118 $sql = "update tt_custom_field_options set value = ".$mdb2->quote($option_name)." where id = $id";
119 $affected = $mdb2->exec($sql);
120 return (!is_a($affected, 'PEAR_Error'));
123 // delete Option deletes an option and all custom field log entries that used it.
124 static function deleteOption($id) {
126 $mdb2 = getConnection();
128 $field_id = CustomFields::getFieldIdForOption($id);
130 // First make sure that the field is ours.
131 $sql = "select team_id from tt_custom_fields where id = $field_id";
132 $res = $mdb2->query($sql);
133 if (is_a($res, 'PEAR_Error'))
135 $val = $res->fetchRow();
136 if ($user->team_id != $val['team_id'])
139 // Delete log entries with this option.
140 $sql = "update tt_custom_field_log set status = NULL where field_id = $field_id and value = ".$mdb2->quote($id);
141 $affected = $mdb2->exec($sql);
142 if (is_a($affected, 'PEAR_Error'))
145 // Delete the option.
146 $sql = "delete from tt_custom_field_options where id = $id";
147 $affected = $mdb2->exec($sql);
148 return (!is_a($affected, 'PEAR_Error'));
151 // getOptions returns an array of options for a custom field.
152 static function getOptions($field_id) {
154 $mdb2 = getConnection();
157 // First make sure that the field is ours.
158 $sql = "select team_id from tt_custom_fields where id = $field_id";
159 $res = $mdb2->query($sql);
160 if (is_a($res, 'PEAR_Error'))
162 $val = $res->fetchRow();
163 if ($user->team_id != $val['team_id'])
167 $sql = "select id, value from tt_custom_field_options where field_id = $field_id order by value";
168 $res = $mdb2->query($sql);
169 if (!is_a($res, 'PEAR_Error')) {
170 while ($val = $res->fetchRow()) {
171 $options[$val['id']] = $val['value'];
178 // getOptiondName returns an option name for a custom field.
179 static function getOptionName($id) {
181 $mdb2 = getConnection();
183 $field_id = CustomFields::getFieldIdForOption($id);
185 // First make sure that the field is ours.
186 $sql = "select team_id from tt_custom_fields where id = $field_id";
187 $res = $mdb2->query($sql);
188 if (is_a($res, 'PEAR_Error'))
190 $val = $res->fetchRow();
191 if ($user->team_id != $val['team_id'])
195 $sql = "select value from tt_custom_field_options where id = $id";
196 $res = $mdb2->query($sql);
197 if (!is_a($res, 'PEAR_Error')) {
198 $val = $res->fetchRow();
199 $name = $val['value'];
205 // getFields returns an array of custom fields for team.
206 static function getFields() {
208 $mdb2 = getConnection();
211 $sql = "select id, type, label from tt_custom_fields where team_id = $user->team_id and status = 1 and type > 0";
212 $res = $mdb2->query($sql);
213 if (!is_a($res, 'PEAR_Error')) {
214 while ($val = $res->fetchRow()) {
215 $fields[] = array('id'=>$val['id'],'type'=>$val['type'],'label'=>$val['label']);
222 // getField returns a custom field.
223 static function getField($id) {
225 $mdb2 = getConnection();
227 $sql = "select label, type, required from tt_custom_fields where id = $id and team_id = $user->team_id";
228 $res = $mdb2->query($sql);
229 if (!is_a($res, 'PEAR_Error')) {
230 $val = $res->fetchRow();
238 // getFieldIdForOption returns field id from an associated option id.
239 static function getFieldIdForOption($option_id) {
240 $mdb2 = getConnection();
242 $sql = "select field_id from tt_custom_field_options where id = $option_id";
243 $res = $mdb2->query($sql);
244 if (!is_a($res, 'PEAR_Error')) {
245 $val = $res->fetchRow();
246 $field_id = $val['field_id'];
252 // The insertField inserts a custom field for team.
253 static function insertField($field_name, $field_type, $required) {
257 $mdb2 = getConnection();
259 $sql = "insert into tt_custom_fields (team_id, type, label, required, status) values($user->team_id, $field_type, ".$mdb2->quote($field_name).", $required, 1)";
260 $affected = $mdb2->exec($sql);
261 return (!is_a($affected, 'PEAR_Error'));
264 // The updateField updates custom field for team.
265 static function updateField($id, $name, $type, $required) {
269 $mdb2 = getConnection();
271 $sql = "update tt_custom_fields set label = ".$mdb2->quote($name).", type = $type, required = $required where id = $id and team_id = $user->team_id";
272 $affected = $mdb2->exec($sql);
273 return (!is_a($affected, 'PEAR_Error'));
276 // The deleteField deletes a custom field, its options and log entries for team.
277 static function deleteField($field_id) {
279 // Our overall intention is to keep the code simple and manageable.
280 // If a users wishes to delete a field, we will delete all its options and log entries.
281 // Otherwise we have to do conditional queries depending on field status (this complicates things).
284 $mdb2 = getConnection();
286 // First make sure that the field is ours so that we can safely delete it.
287 $sql = "select team_id from tt_custom_fields where id = $field_id";
288 $res = $mdb2->query($sql);
289 if (is_a($res, 'PEAR_Error'))
291 $val = $res->fetchRow();
292 if ($user->team_id != $val['team_id'])
295 // Mark log entries as deleted.
296 $sql = "update tt_custom_field_log set status = NULL where field_id = $field_id";
297 $affected = $mdb2->exec($sql);
298 if (is_a($affected, 'PEAR_Error'))
301 // Delete field options.
302 $sql = "delete from tt_custom_field_options where field_id = $field_id";
303 $affected = $mdb2->exec($sql);
304 if (is_a($affected, 'PEAR_Error'))
308 $sql = "delete from tt_custom_fields where id = $field_id and team_id = $user->team_id";
309 $affected = $mdb2->exec($sql);
310 return (!is_a($affected, 'PEAR_Error'));