- // Determine group by field and a required join.
- switch ($group_by_option) {
- case 'date':
- $group_field = 'l.date';
- $group_join = '';
- break;
- case 'user':
- $group_field = 'u.name';
- $group_join = 'left join tt_users u on (l.user_id = u.id) ';
- break;
- case 'client':
- $group_field = 'c.name';
- $group_join = 'left join tt_clients c on (l.client_id = c.id) ';
- break;
- case 'project':
- $group_field = 'p.name';
- $group_join = 'left join tt_projects p on (l.project_id = p.id) ';
- break;
- case 'task':
- $group_field = 't.name';
- $group_join = 'left join tt_tasks t on (l.task_id = t.id) ';
- break;
- case 'cf_1':
- $group_field = 'cfo.value';
- $custom_fields = new CustomFields($user->group_id);
- if ($custom_fields->fields[0]['type'] == CustomFields::TYPE_TEXT)
- $group_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)
- $group_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.option_id = cfo.id) ';
- break;
- }
-
- $where = ttReportHelper::getFavWhere($options);
- if ($options['show_cost']) {
- if (MODE_TIME == $user->tracking_mode) {
- if ($group_by_option != 'user')
- $left_join = 'left join tt_users u on (l.user_id = u.id)';
- $sql = "select $group_field as group_field, 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 .= ", 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 .= ", 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
- $group_join $left_join $where group by $group_field";
- } 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 $group_field as group_field, 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 .= ", 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
- $group_join
- left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id) $where group by $group_field";
- }
- } else {
- $sql = "select $group_field as group_field, 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 .= ", null as expenses from tt_log l
- $group_join $where group by $group_field";
- }
- // By now we have sql for time items.
-
- // However, when we have expenses, we need to do a union with a separate query for expense items from tt_expense_items table.
- if ($options['show_cost'] && $user->isPluginEnabled('ex')) { // if ex(penses) plugin is enabled
-
- // Determine group by field and a required join.
- $group_join = null;
- $group_field = 'null';
- switch ($group_by_option) {
- case 'date':
- $group_field = 'ei.date';
- $group_join = '';
- break;
- case 'user':
- $group_field = 'u.name';
- $group_join = 'left join tt_users u on (ei.user_id = u.id) ';
- break;
- case 'client':
- $group_field = 'c.name';
- $group_join = 'left join tt_clients c on (ei.client_id = c.id) ';
- break;
- case 'project':
- $group_field = 'p.name';
- $group_join = 'left join tt_projects p on (ei.project_id = p.id) ';
- break;
- }
-
- $where = ttReportHelper::getFavExpenseWhere($options);
- $sql_for_expenses = "select $group_field as group_field, null as time";
- if ($options['show_work_units']) $sql_for_expenses .= ", null as units";
- $sql_for_expenses .= ", sum(ei.cost) as cost, sum(ei.cost) as expenses from tt_expense_items ei $group_join $where";
- // Add a "group by" clause if we are grouping.
- if ('null' != $group_field) $sql_for_expenses .= " group by $group_field";
-
- // Create a combined query.
- $combined = "select group_field, sum(time) as time";
- if ($options['show_work_units']) $combined .= ", sum(units) as units";
- $combined .= ", sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t group by group_field";
- $sql = $combined;
- }
-
- // Execute query.
- $res = $mdb2->query($sql);
- if (is_a($res, 'PEAR_Error')) die($res->getMessage());
-
- while ($val = $res->fetchRow()) {
- if ('date' == $group_by_option) {
- // This is needed to get the date in user date format.
- $o_date = new DateAndTime(DB_DATEFORMAT, $val['group_field']);
- $val['group_field'] = $o_date->toString($user->date_format);
- unset($o_date);
- }
- $time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
- 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']);
- }
- $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time, 'units'=> $val['units'], 'cost'=>$val['cost'],'expenses'=>$val['expenses']);
- } else
- $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time, 'units'=> $val['units']);
- }
-
- return $subtotals;
- }
-
- // getTotals calculates total hours and cost for all report items.
- static function getTotals($bean, $options)
- {
- global $user;
-
- $mdb2 = getConnection();
-
- $where = ttReportHelper::getWhere($bean, $options);
-
- // Prepare parts.
- $time_part = "sum(time_to_sec(l.duration)) as time";
- if ($bean->getAttribute('chunits')) {
- $units_part = $user->unit_totals_only ? ", null as units" : ", 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";
- }
- if ($bean->getAttribute('chcost')) {
- if (MODE_TIME == $user->tracking_mode)
- $cost_part = ", sum(cast(l.billable * coalesce(u.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost, null as expenses";
- else
- $cost_part = ", sum(cast(l.billable * coalesce(upb.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost, null as expenses";
- } else {
- $cost_part = ", null as cost, null as expenses";
- }
- if ($bean->getAttribute('chcost')) {
- if (MODE_TIME == $user->tracking_mode) {
- $left_joins = "left join tt_users u on (l.user_id = u.id)";
- } else {
- $left_joins = "left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)";
- }
- }
- // Prepare a query for time items.
- $sql = "select $time_part $units_part $cost_part from tt_log l $left_joins $where";
-
- // If we have expenses, query becomes a bit more complex.
- if ($bean->getAttribute('chcost') && $user->isPluginEnabled('ex')) {
- $where = ttReportHelper::getExpenseWhere($bean, $options);
- $sql_for_expenses = "select null as time";
- if ($bean->getAttribute('chunits')) $sql_for_expenses .= ", null as units";
- $sql_for_expenses .= ", sum(cost) as cost, sum(cost) as expenses from tt_expense_items ei $where";
-
- // Create a combined query.
- $combined = "select sum(time) as time";
- if ($bean->getAttribute('chunits')) $combined .= ", sum(units) as units";
- $combined .= ", sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t";
- $sql = $combined;
- }
-
- // Execute query.
- $res = $mdb2->query($sql);
- if (is_a($res, 'PEAR_Error')) die($res->getMessage());
-
- $val = $res->fetchRow();
- $total_time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
- if ($bean->getAttribute('chcost')) {
- $total_cost = $val['cost'];
- if (!$total_cost) $total_cost = '0.00';
- if ('.' != $user->decimal_mark)
- $total_cost = str_replace('.', $user->decimal_mark, $total_cost);
- $total_expenses = $val['expenses'];
- if (!$total_expenses) $total_expenses = '0.00';
- if ('.' != $user->decimal_mark)
- $total_expenses = str_replace('.', $user->decimal_mark, $total_expenses);
- }
-
- if ($bean->getAttribute('period'))
- $period = new Period($bean->getAttribute('period'), new DateAndTime($user->date_format));
- else {
- $period = new Period();
- $period->setPeriod(
- new DateAndTime($user->date_format, $bean->getAttribute('start_date')),
- new DateAndTime($user->date_format, $bean->getAttribute('end_date')));
- }
-
- $totals['start_date'] = $period->getStartDate();
- $totals['end_date'] = $period->getEndDate();
- $totals['time'] = $total_time;
- $totals['units'] = $val['units'];
- $totals['cost'] = $total_cost;
- $totals['expenses'] = $total_expenses;
-
- return $totals;
- }
-
- // getFavTotals calculates total hours and cost for all favorite report items.
- static function getFavTotals($options)
- {
- global $user;
-
- $mdb2 = getConnection();
-
- $where = ttReportHelper::getFavWhere($options);
-
- // Prepare parts.
- $time_part = "sum(time_to_sec(l.duration)) as time";
- if ($options['show_work_units']) {
- $units_part = $user->unit_totals_only ? ", null as units" : ", 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";
- }
- if ($options['show_cost']) {
- if (MODE_TIME == $user->tracking_mode)
- $cost_part = ", sum(cast(l.billable * coalesce(u.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost, null as expenses";
- else
- $cost_part = ", sum(cast(l.billable * coalesce(upb.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost, null as expenses";
- } else {
- $cost_part = ", null as cost, null as expenses";
- }
- if ($options['show_cost']) {
- if (MODE_TIME == $user->tracking_mode) {
- $left_joins = "left join tt_users u on (l.user_id = u.id)";
- } else {
- $left_joins = "left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)";
- }
- }
- // Prepare a query for time items.
- $sql = "select $time_part $units_part $cost_part from tt_log l $left_joins $where";
-
- // If we have expenses, query becomes a bit more complex.
- if ($options['show_cost'] && $user->isPluginEnabled('ex')) {
- $where = ttReportHelper::getFavExpenseWhere($options);
- $sql_for_expenses = "select null as time";
- if ($options['show_work_units']) $sql_for_expenses .= ", null as units";
- $sql_for_expenses .= ", sum(cost) as cost, sum(cost) as expenses from tt_expense_items ei $where";
-
- // Create a combined query.
- $combined = "select sum(time) as time";
- if ($options['show_work_units']) $combined .= ", sum(units) as units";
- $combined .= ", sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t";
- $sql = $combined;
- }
-
- // Execute query.
- $res = $mdb2->query($sql);
- if (is_a($res, 'PEAR_Error')) die($res->getMessage());
-
- $val = $res->fetchRow();
- $total_time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
- if ($options['show_cost']) {
- $total_cost = $val['cost'];
- if (!$total_cost) $total_cost = '0.00';
- if ('.' != $user->decimal_mark)
- $total_cost = str_replace('.', $user->decimal_mark, $total_cost);
- $total_expenses = $val['expenses'];
- if (!$total_expenses) $total_expenses = '0.00';
- if ('.' != $user->decimal_mark)
- $total_expenses = str_replace('.', $user->decimal_mark, $total_expenses);
- }
-
- if ($options['period'])
- $period = new Period($options['period'], new DateAndTime($user->date_format));
- else {
- $period = new Period();
- $period->setPeriod(
- new DateAndTime($user->date_format, $options['period_start']),
- new DateAndTime($user->date_format, $options['period_end']));
- }
-
- $totals['start_date'] = $period->getStartDate();
- $totals['end_date'] = $period->getEndDate();
- $totals['time'] = $total_time;
- $totals['units'] = $val['units'];
- $totals['cost'] = $total_cost;
- $totals['expenses'] = $total_expenses;
-
- return $totals;
- }
-
- // The assignToInvoice assigns a set of records to a specific invoice.
- static function assignToInvoice($invoice_id, $time_log_ids, $expense_item_ids)
- {
- $mdb2 = getConnection();
- if ($time_log_ids) {
- $sql = "update tt_log set invoice_id = ".$mdb2->quote($invoice_id).
- " where id in(".join(', ', $time_log_ids).")";
- $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).")";
- $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)
- {
- $mdb2 = getConnection();
- $paid_val = (int) $paid;
- if ($time_log_ids) {
- $sql = "update tt_log set paid = $paid_val where id in(".join(', ', $time_log_ids).")";
- $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).")";
- $affected = $mdb2->exec($sql);
- if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
- }
- }
-
- // prepareReportBody - prepares an email body for report.
- static function prepareReportBody($bean, $comment)
- {
- global $user;
- global $i18n;
-
- // Determine these once as they are used in multiple places in this function.
- $canViewReports = $user->can('view_reports');
- $isClient = $user->isClient();
- $options = ttReportHelper::getReportOptions($bean);
-
- $items = ttReportHelper::getItems($bean, $options);
- $group_by = $bean->getAttribute('group_by');
- if ($group_by && 'no_grouping' != $group_by)
- $subtotals = ttReportHelper::getSubtotals($bean, $options);
- $totals = ttReportHelper::getTotals($bean, $options);
-
- // Use custom fields plugin if it is enabled.
- if ($user->isPluginEnabled('cf'))
- $custom_fields = new CustomFields($user->group_id);
-
- // Define some styles to use in email.
- $style_title = 'text-align: center; font-size: 15pt; font-family: Arial, Helvetica, sans-serif;';
- $tableHeader = 'font-weight: bold; background-color: #a6ccf7; text-align: left;';
- $tableHeaderCentered = 'font-weight: bold; background-color: #a6ccf7; text-align: center;';
- $rowItem = 'background-color: #ffffff;';
- $rowItemAlt = 'background-color: #f5f5f5;';
- $rowSubtotal = 'background-color: #e0e0e0;';
- $cellLeftAligned = 'text-align: left; vertical-align: top;';
- $cellRightAligned = 'text-align: right; vertical-align: top;';
- $cellLeftAlignedSubtotal = 'font-weight: bold; text-align: left; vertical-align: top;';
- $cellRightAlignedSubtotal = 'font-weight: bold; text-align: right; vertical-align: top;';