X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/timetracker.git/blobdiff_plain/7797eda9fb04c217a813db88e00bb9ee541eabbb..ccaee9cc6abdbf1684b25e771b0dca35659e4a55:/WEB-INF/lib/ttReportHelper.class.php diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php index 16277bd1..068ae9db 100644 --- a/WEB-INF/lib/ttReportHelper.class.php +++ b/WEB-INF/lib/ttReportHelper.class.php @@ -40,6 +40,9 @@ class ttReportHelper { static function getWhere($options) { global $user; + $group_id = $user->getGroup(); + $org_id = $user->org_id; + // Prepare dropdown parts. $dropdown_parts = ''; if ($options['client_id']) @@ -61,17 +64,18 @@ class ttReportHelper { if ($user->can('view_reports') || $user->can('view_all_reports') || $user->isClient()) $user_list_part = " and l.user_id in ($userlist)"; else - $user_list_part = " and l.user_id = ".$user->id; - $user_list_part .= " and l.group_id = ".$user->getGroup(); + $user_list_part = " and l.user_id = ".$user->getUser(); + $user_list_part .= " and l.group_id = $group_id and l.org_id = $org_id"; // Prepare sql query part for where. + $dateFormat = $user->getDateFormat(); if ($options['period']) - $period = new Period($options['period'], new DateAndTime($user->date_format)); + $period = new Period($options['period'], new DateAndTime($dateFormat)); else { $period = new Period(); $period->setPeriod( - new DateAndTime($user->date_format, $options['period_start']), - new DateAndTime($user->date_format, $options['period_end'])); + new DateAndTime($dateFormat, $options['period_start']), + new DateAndTime($dateFormat, $options['period_end'])); } $where = " where l.status = 1 and l.date >= '".$period->getStartDate(DB_DATEFORMAT)."' and l.date <= '".$period->getEndDate(DB_DATEFORMAT)."'". " $user_list_part $dropdown_parts"; @@ -82,6 +86,9 @@ class ttReportHelper { static function getExpenseWhere($options) { global $user; + $group_id = $user->getGroup(); + $org_id = $user->org_id; + // Prepare dropdown parts. $dropdown_parts = ''; if ($options['client_id']) @@ -99,17 +106,18 @@ class ttReportHelper { if ($user->can('view_reports') || $user->can('view_all_reports') || $user->isClient()) $user_list_part = " and ei.user_id in ($userlist)"; else - $user_list_part = " and ei.user_id = ".$user->id; - $user_list_part .= " and ei.group_id = ".$user->getGroup(); + $user_list_part = " and ei.user_id = ".$user->getUser(); + $user_list_part .= " and ei.group_id = $group_id and ei.org_id = $org_id"; // Prepare sql query part for where. + $dateFormat = $user->getDateFormat(); if ($options['period']) - $period = new Period($options['period'], new DateAndTime($user->date_format)); + $period = new Period($options['period'], new DateAndTime($dateFormat)); else { $period = new Period(); $period->setPeriod( - new DateAndTime($user->date_format, $options['period_start']), - new DateAndTime($user->date_format, $options['period_end'])); + new DateAndTime($dateFormat, $options['period_start']), + new DateAndTime($dateFormat, $options['period_end'])); } $where = " where ei.status = 1 and ei.date >= '".$period->getStartDate(DB_DATEFORMAT)."' and ei.date <= '".$period->getEndDate(DB_DATEFORMAT)."'". " $user_list_part $dropdown_parts"; @@ -136,7 +144,9 @@ class ttReportHelper { $grouping_by_user = ttReportHelper::groupingBy('user', $options); $grouping_by_cf_1 = ttReportHelper::groupingBy('cf_1', $options); } - $convertTo12Hour = ('%I:%M %p' == $user->time_format) && ($options['show_start'] || $options['show_end']); + $convertTo12Hour = ('%I:%M %p' == $user->getTimeFormat()) && ($options['show_start'] || $options['show_end']); + $trackingMode = $user->getTrackingMode(); + $decimalMark = $user->getDecimalMark(); // Prepare a query for time items in tt_log table. $fields = array(); // An array of fields for database query. @@ -157,7 +167,7 @@ class ttReportHelper { // Add custom field. $include_cf_1 = $options['show_custom_field_1'] || $grouping_by_cf_1; if ($include_cf_1) { - $custom_fields = new CustomFields($user->getGroup()); + $custom_fields = new CustomFields(); $cf_1_type = $custom_fields->fields[0]['type']; if ($cf_1_type == CustomFields::TYPE_TEXT) { array_push($fields, 'cfl.value as cf_1'); @@ -178,10 +188,13 @@ class ttReportHelper { array_push($fields, "TIME_FORMAT(l.duration, '%k:%i') as duration"); // Add work units. if ($options['show_work_units']) { - if ($user->unit_totals_only) + if ($user->getConfigOption('unit_totals_only')) array_push($fields, "null as units"); - else - array_push($fields, "if(l.billable = 0 or time_to_sec(l.duration)/60 < $user->first_unit_threshold, 0, ceil(time_to_sec(l.duration)/60/$user->minutes_in_unit)) as units"); + else { + $firstUnitThreshold = $user->getConfigInt('1st_unit_threshold'); + $minutesInUnit = $user->getConfigInt('minutes_in_unit'); + array_push($fields, "if(l.billable = 0 or time_to_sec(l.duration)/60 < $firstUnitThreshold, 0, ceil(time_to_sec(l.duration)/60/$minutesInUnit)) as units"); + } } // Add note. if ($options['show_note']) @@ -189,7 +202,7 @@ class ttReportHelper { // Handle cost. $includeCost = $options['show_cost']; if ($includeCost) { - if (MODE_TIME == $user->tracking_mode) + if (MODE_TIME == $trackingMode) array_push($fields, "cast(l.billable * coalesce(u.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2)) as cost"); // Use default user rate. else array_push($fields, "cast(l.billable * coalesce(upb.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2)) as cost"); // Use project rate for user. @@ -229,7 +242,7 @@ class ttReportHelper { " left join tt_custom_field_options cfo on (cfl.option_id = cfo.id)"; } } - if ($includeCost && MODE_TIME != $user->tracking_mode) + if ($includeCost && MODE_TIME != $trackingMode) $left_joins .= " left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)"; $where = ttReportHelper::getWhere($options); @@ -339,12 +352,12 @@ class ttReportHelper { $val['finish'] = ttTimeHelper::to12HourFormat($val['finish']); } if (isset($val['cost'])) { - if ('.' != $user->decimal_mark) - $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']); + if ('.' != $decimalMark) + $val['cost'] = str_replace('.', $decimalMark, $val['cost']); } if (isset($val['expense'])) { - if ('.' != $user->decimal_mark) - $val['expense'] = str_replace('.', $user->decimal_mark, $val['expense']); + if ('.' != $decimalMark) + $val['expense'] = str_replace('.', $decimalMark, $val['expense']); } if ($grouping) $val['grouped_by'] = ttReportHelper::makeGroupByKey($options, $val); @@ -394,50 +407,44 @@ class ttReportHelper { // With expenses, it becomes a select with group by from a combined set of records obtained with "union all". static function getSubtotals($options) { global $user; - $mdb2 = getConnection(); $concat_part = ttReportHelper::makeConcatPart($options); $join_part = ttReportHelper::makeJoinPart($options); + + // TODO: Consider moving this block out into a separate function. + $workUnits = $options['show_work_units']; + if ($workUnits) { + $unitTotalsOnly = $user->getConfigOption('unit_totals_only'); + $firstUnitThreshold = $user->getConfigInt('1st_unit_threshold'); + $minutesInUnit = $user->getConfigInt('minutes_in_unit', 15); + if ($unitTotalsOnly) + $work_unit_part = ", if (sum(l.billable * time_to_sec(l.duration)/60) < $firstUnitThreshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$minutesInUnit))) as units"; + else + $work_unit_part = ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $firstUnitThreshold, 0, ceil(time_to_sec(l.duration)/60/$minutesInUnit))) as units"; + } + // End of TODO. + $where = ttReportHelper::getWhere($options); $group_by_part = ttReportHelper::makeGroupByPart($options); if ($options['show_cost']) { - if (MODE_TIME == $user->tracking_mode) { + if (MODE_TIME == $user->getTrackingMode()) { if (!ttReportHelper::groupingBy('user', $options)) $left_join = 'left join tt_users u on (l.user_id = u.id)'; - $sql = "select $concat_part, sum(time_to_sec(l.duration)) as time"; - if ($options['show_work_units']) { - if ($user->unit_totals_only) - $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $user->first_unit_threshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units"; - else - $sql .= ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $user->first_unit_threshold, 0, ceil(time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units"; - } + $sql = "select $concat_part, sum(time_to_sec(l.duration)) as time".$work_unit_part; $sql .= ", sum(cast(l.billable * coalesce(u.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10, 2))) as cost, null as expenses from tt_log l $join_part $left_join $where $group_by_part"; } else { // If we are including cost and tracking projects, our query (the same as above) needs to join the tt_user_project_binds table. - $sql = "select $concat_part, sum(time_to_sec(l.duration)) as time"; - if ($options['show_work_units']) { - if ($user->unit_totals_only) - $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $user->first_unit_threshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units"; - else - $sql .= ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $user->first_unit_threshold, 0, ceil(time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units"; - } + $sql = "select $concat_part, sum(time_to_sec(l.duration)) as time".$work_unit_part; $sql .= ", sum(cast(l.billable * coalesce(upb.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost, null as expenses from tt_log l $join_part left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id) $where $group_by_part"; } } else { - // $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time"; - $sql = "select $concat_part, sum(time_to_sec(l.duration)) as time"; - if ($options['show_work_units']) { - if ($user->unit_totals_only) - $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $user->first_unit_threshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units"; - else - $sql .= ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $user->first_unit_threshold, 0, ceil(time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units"; - } + $sql = "select $concat_part, sum(time_to_sec(l.duration)) as time".$work_unit_part; $sql .= ", null as expenses from tt_log l $join_part $where $group_by_part"; } @@ -469,9 +476,10 @@ class ttReportHelper { $time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null; $rowLabel = ttReportHelper::makeGroupByLabel($val['group_field'], $options); if ($options['show_cost']) { - if ('.' != $user->decimal_mark) { - $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']); - $val['expenses'] = str_replace('.', $user->decimal_mark, $val['expenses']); + $decimalMark = $user->getDecimalMark(); + if ('.' != $decimalMark) { + $val['cost'] = str_replace('.', $decimalMark, $val['cost']); + $val['expenses'] = str_replace('.', $decimalMark, $val['expenses']); } $subtotals[$val['group_field']] = array('name'=>$rowLabel,'user'=>$val['user'],'project'=>$val['project'],'task'=>$val['task'],'client'=>$val['client'],'cf_1'=>$val['cf_1'],'time'=>$time,'units'=> $val['units'],'cost'=>$val['cost'],'expenses'=>$val['expenses']); } else @@ -564,35 +572,45 @@ class ttReportHelper { } // The assignToInvoice assigns a set of records to a specific invoice. - static function assignToInvoice($invoice_id, $time_log_ids, $expense_item_ids) - { + static function assignToInvoice($invoice_id, $time_log_ids, $expense_item_ids) { + global $user; $mdb2 = getConnection(); + + $group_id = $user->getGroup(); + $org_id = $user->org_id; + if ($time_log_ids) { $sql = "update tt_log set invoice_id = ".$mdb2->quote($invoice_id). - " where id in(".join(', ', $time_log_ids).")"; + " where id in(".join(', ', $time_log_ids).") and group_id = $group_id and org_id = $org_id"; $affected = $mdb2->exec($sql); if (is_a($affected, 'PEAR_Error')) die($affected->getMessage()); } if ($expense_item_ids) { $sql = "update tt_expense_items set invoice_id = ".$mdb2->quote($invoice_id). - " where id in(".join(', ', $expense_item_ids).")"; + " where id in(".join(', ', $expense_item_ids).") and group_id = $group_id and org_id = $org_id"; $affected = $mdb2->exec($sql); if (is_a($affected, 'PEAR_Error')) die($affected->getMessage()); } } // The markPaid marks a set of records as either paid or unpaid. - static function markPaid($time_log_ids, $expense_item_ids, $paid = true) - { + static function markPaid($time_log_ids, $expense_item_ids, $paid = true) { + global $user; $mdb2 = getConnection(); + + $group_id = $user->getGroup(); + $org_id = $user->org_id; + $paid_val = (int) $paid; if ($time_log_ids) { - $sql = "update tt_log set paid = $paid_val where id in(".join(', ', $time_log_ids).")"; + $sql = "update tt_log set paid = $paid_val". + " where id in(".join(', ', $time_log_ids).") and group_id = $group_id and org_id = $org_id"; $affected = $mdb2->exec($sql); if (is_a($affected, 'PEAR_Error')) die($affected->getMessage()); } if ($expense_item_ids) { - $sql = "update tt_expense_items set paid = $paid_val where id in(".join(', ', $expense_item_ids).")"; + $sql = "update tt_expense_items set paid = $paid_val". + " where id in(".join(', ', $expense_item_ids).") and group_id = $group_id and org_id = $org_id"; $affected = $mdb2->exec($sql); if (is_a($affected, 'PEAR_Error')) die($affected->getMessage()); } @@ -616,7 +634,7 @@ class ttReportHelper { // Use custom fields plugin if it is enabled. if ($user->isPluginEnabled('cf')) - $custom_fields = new CustomFields($user->getGroup()); + $custom_fields = new CustomFields(); // Define some styles to use in email. $style_title = 'text-align: center; font-size: 15pt; font-family: Arial, Helvetica, sans-serif;'; @@ -1010,7 +1028,7 @@ class ttReportHelper { // Check users. $users_in_bean = $bean->getAttribute('users'); if (is_array($users_in_bean)) { - $users_in_group = ttTeamHelper::getUsers(); + $users_in_group = ttGroupHelper::getUsers(); foreach ($users_in_group as $user_in_group) { $valid_ids[] = $user_in_group['id']; } @@ -1460,7 +1478,7 @@ class ttReportHelper { $join .= ' left join tt_tasks t on (l.task_id = t.id)'; } if (ttReportHelper::groupingBy('cf_1', $options)) { - $custom_fields = new CustomFields($user->getGroup()); + $custom_fields = new CustomFields(); if ($custom_fields->fields[0]['type'] == CustomFields::TYPE_TEXT) $join .= ' left join tt_custom_field_log cfl on (l.id = cfl.log_id and cfl.status = 1) left join tt_custom_field_options cfo on (cfl.value = cfo.id)'; elseif ($custom_fields->fields[0]['type'] == CustomFields::TYPE_DROPDOWN)