- // Print report items.
- if (is_array($items)) {
- foreach ($items as $record) {
- $cur_date = $record['date'];
- // Print a subtotal row after a block of grouped items.
- if ($print_subtotals) {
- $cur_grouped_by = $record['grouped_by'];
- if ($cur_grouped_by != $prev_grouped_by && !$first_pass) {
- $body .= '<tr style="'.$rowSubtotal.'">';
- $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$i18n->get('label.subtotal').'</td>';
- $subtotal_name = htmlspecialchars($subtotals[$prev_grouped_by]['name']);
- if ($canViewReports || $isClient) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'user' ? $subtotal_name : '').'</td>';
- if ($options['show_client']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'client' ? $subtotal_name : '').'</td>';
- if ($options['show_project']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'project' ? $subtotal_name : '').'</td>';
- if ($options['show_task']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'task' ? $subtotal_name : '').'</td>';
- if ($options['show_custom_field_1']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'cf_1' ? $subtotal_name : '').'</td>';
- if ($options['show_start']) $body .= '<td></td>';
- if ($options['show_end']) $body .= '<td></td>';
- if ($options['show_duration']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['time'].'</td>';
- if ($options['show_work_units']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['units'].'</td>';
- if ($options['show_note']) $body .= '<td></td>';
- if ($options['show_cost']) {
- $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
- $body .= ($canViewReports || $isClient) ? $subtotals[$prev_grouped_by]['cost'] : $subtotals[$prev_grouped_by]['expenses'];
- $body .= '</td>';
- }
- if ($options['show_paid']) $body .= '<td></td>';
- if ($options['show_ip']) $body .= '<td></td>';
- if ($options['show_invoice']) $body .= '<td></td>';
- $body .= '</tr>';
- $body .= '<tr><td> </td></tr>';
- }
- $first_pass = false;
- }
+ // makeGroupByExpensesPart builds a combined group by part for sql query for expense items using
+ // group_by1, group_by2, and group_by3 values passed in $options.
+ static function makeGroupByExpensesPart($options) {
+ $no_grouping = ($options['group_by1'] == null || $options['group_by1'] == 'no_grouping') &&
+ ($options['group_by2'] == null || $options['group_by2'] == 'no_grouping') &&
+ ($options['group_by3'] == null || $options['group_by3'] == 'no_grouping');
+ if ($no_grouping) return null;
+
+ $group_by1 = $options['group_by1'];
+ $group_by2 = $options['group_by2'];
+ $group_by3 = $options['group_by3'];
+
+ switch ($group_by1) {
+ case 'date':
+ $group_by_parts .= ', ei.date';
+ break;
+ case 'user':
+ $group_by_parts .= ', u.name';
+ break;
+ case 'client':
+ $group_by_parts .= ', c.name';
+ break;
+ case 'project':
+ $group_by_parts .= ', p.name';
+ break;
+ }
+ switch ($group_by2) {
+ case 'date':
+ $group_by_parts .= ', ei.date';
+ break;
+ case 'user':
+ $group_by_parts .= ', u.name';
+ break;
+ case 'client':
+ $group_by_parts .= ', c.name';
+ break;
+ case 'project':
+ $group_by_parts .= ', p.name';
+ break;
+ }
+ switch ($group_by3) {
+ case 'date':
+ $group_by_parts .= ', ei.date';
+ break;
+ case 'user':
+ $group_by_parts .= ', u.name';
+ break;
+ case 'client':
+ $group_by_parts .= ', c.name';
+ break;
+ case 'project':
+ $group_by_parts .= ', p.name';
+ break;
+ }
+ // Remove garbage from the beginning.
+ $group_by_parts = ltrim($group_by_parts, ', ');
+ if ($group_by_parts)
+ $group_by_part = "group by $group_by_parts";
+ return $group_by_part;
+ }
+
+ // makeConcatPart builds a concatenation part for getSubtotals query (for time items).
+ static function makeConcatPart($options) {
+ $group_by1 = $options['group_by1'];
+ $group_by2 = $options['group_by2'];
+ $group_by3 = $options['group_by3'];
+
+ switch ($group_by1) {
+ case 'date':
+ $what_to_concat .= ", ' - ', l.date";
+ break;
+ case 'user':
+ $what_to_concat .= ", ' - ', u.name";
+ $fields_part .= ', u.name as user';
+ break;
+ case 'client':
+ $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+ $fields_part .= ', c.name as client';
+ break;
+ case 'project':
+ $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+ $fields_part .= ', p.name as project';
+ break;
+ case 'task':
+ $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
+ $fields_part .= ', t.name as task';
+ break;
+ case 'cf_1':
+ $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
+ $fields_part .= ', cfo.value as cf_1';
+ break;
+ }
+ switch ($group_by2) {
+ case 'date':
+ $what_to_concat .= ", ' - ', l.date";
+ break;
+ case 'user':
+ $what_to_concat .= ", ' - ', u.name";
+ $fields_part .= ', u.name as user';
+ break;
+ case 'client':
+ $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+ $fields_part .= ', c.name as client';
+ break;
+ case 'project':
+ $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+ $fields_part .= ', p.name as project';
+ break;
+ case 'task':
+ $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
+ $fields_part .= ', t.name as task';
+ break;
+ case 'cf_1':
+ $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
+ $fields_part .= ', cfo.value as cf_1';
+ break;
+ }
+ switch ($group_by3) {
+ case 'date':
+ $what_to_concat .= ", ' - ', l.date";
+ break;
+ case 'user':
+ $what_to_concat .= ", ' - ', u.name";
+ $fields_part .= ', u.name as user';
+ break;
+ case 'client':
+ $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+ $fields_part .= ', c.name as client';
+ break;
+ case 'project':
+ $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+ $fields_part .= ', p.name as project';
+ break;
+ case 'task':
+ $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
+ $fields_part .= ', t.name as task';
+ break;
+ case 'cf_1':
+ $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
+ $fields_part .= ', cfo.value as cf_1';
+ break;
+ }
+ // Remove garbage from both ends.
+ $what_to_concat = trim($what_to_concat, "', -");
+ $concat_part = "concat($what_to_concat) as group_field";
+ $concat_part = trim($concat_part, ' -');
+ return "$concat_part $fields_part";
+ }
+
+ // makeConcatPart builds a concatenation part for getSubtotals query (for expense items).
+ static function makeConcatExpensesPart($options) {
+ $group_by1 = $options['group_by1'];
+ $group_by2 = $options['group_by2'];
+ $group_by3 = $options['group_by3'];
+
+ switch ($group_by1) {
+ case 'date':
+ $what_to_concat .= ", ' - ', ei.date";
+ break;
+ case 'user':
+ $what_to_concat .= ", ' - ', u.name";
+ $fields_part .= ', u.name as user';
+ break;
+ case 'client':
+ $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+ $fields_part .= ', c.name as client';
+ break;
+ case 'project':
+ $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+ $fields_part .= ', p.name as project';
+ break;
+
+ case 'task':
+ $what_to_concat .= ", ' - ', 'Null'";
+ $fields_part .= ', null as task';
+ break;
+
+ case 'cf_1':
+ $what_to_concat .= ", ' - ', 'Null'";
+ $fields_part .= ', null as cf_1';
+ break;
+ }
+ switch ($group_by2) {
+ case 'date':
+ $what_to_concat .= ", ' - ', ei.date";
+ break;
+ case 'user':
+ $what_to_concat .= ", ' - ', u.name";
+ $fields_part .= ', u.name as user';
+ break;
+ case 'client':
+ $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+ $fields_part .= ', c.name as client';
+ break;
+ case 'project':
+ $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+ $fields_part .= ', p.name as project';
+ break;
+
+ case 'task':
+ $what_to_concat .= ", ' - ', 'Null'";
+ $fields_part .= ', null as task';
+ break;
+
+ case 'cf_1':
+ $what_to_concat .= ", ' - ', 'Null'";
+ $fields_part .= ', null as cf_1';
+ break;
+ }
+ switch ($group_by3) {
+ case 'date':
+ $what_to_concat .= ", ' - ', ei.date";
+ break;
+ case 'user':
+ $what_to_concat .= ", ' - ', u.name";
+ $fields_part .= ', u.name as user';
+ break;
+ case 'client':
+ $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+ $fields_part .= ', c.name as client';
+ break;
+ case 'project':
+ $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+ $fields_part .= ', p.name as project';
+ break;