if ($options['invoice']=='2') $dropdown_parts .= ' and l.invoice_id is null';
if ($options['timesheet']==TIMESHEET_NOT_ASSIGNED) $dropdown_parts .= ' and l.timesheet_id is null';
if ($options['timesheet']==TIMESHEET_ASSIGNED) $dropdown_parts .= ' and l.timesheet_id is not null';
+ if ($options['approved']=='1') $dropdown_parts .= ' and l.approved = 1';
+ if ($options['approved']=='2') $dropdown_parts .= ' and l.approved = 0';
if ($options['paid_status']=='1') $dropdown_parts .= ' and l.paid = 1';
if ($options['paid_status']=='2') $dropdown_parts .= ' and l.paid = 0';
$group_id = $user->getGroup();
$org_id = $user->org_id;
- // A shortcut for timesheets.
- if ($options['timesheet_id']) {
- $where = " where ei.timesheet_id = ".$options['timesheet_id']." and ei.group_id = $group_id and ei.org_id = $org_id";
- return $where;
- }
-
// Prepare dropdown parts.
$dropdown_parts = '';
if ($options['client_id'])
if ($options['project_id']) $dropdown_parts .= ' and ei.project_id = '.$options['project_id'];
if ($options['invoice']=='1') $dropdown_parts .= ' and ei.invoice_id is not null';
if ($options['invoice']=='2') $dropdown_parts .= ' and ei.invoice_id is null';
- if ($options['timesheet']==TIMESHEET_NOT_ASSIGNED) $dropdown_parts .= ' and ei.timesheet_id is null';
- if ($options['timesheet']==TIMESHEET_ASSIGNED) $dropdown_parts .= ' and ei.timesheet_id is not null';
+ if (isset($options['timesheet']) && ($options['timesheet']!=TIMESHEET_ALL && $options['timesheet']!=TIMESHEET_NOT_ASSIGNED)) {
+ $dropdown_parts .= ' and 0 = 1'; // Expense items do not have a timesheet_id.
+ }
+ if ($options['approved']=='1') $dropdown_parts .= ' and ei.approved = 1';
+ if ($options['approved']=='2') $dropdown_parts .= ' and ei.approved = 0';
if ($options['paid_status']=='1') $dropdown_parts .= ' and ei.paid = 1';
if ($options['paid_status']=='2') $dropdown_parts .= ' and ei.paid = 0';
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.
array_push($fields, "null as expense");
}
+ // Add approved.
+ if ($options['show_approved'])
+ array_push($fields, 'l.approved');
// Add paid status.
if ($canViewReports && $options['show_paid'])
array_push($fields, 'l.paid');
if ($user->isPluginEnabled('ts')) {
$timesheet_option = $options['timesheet'];
if ($timesheet_option == TIMESHEET_PENDING)
- $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.submit_status = 1 and ts.approval_status is null)";
+ $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.submit_status = 1 and ts.approve_status is null)";
else if ($timesheet_option == TIMESHEET_APPROVED)
- $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approval_status = 1)";
+ $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 1)";
else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
- $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approval_status = 0)";
+ $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 0)";
else if ($options['show_timesheet'])
$inner_joins .= " left join tt_timesheets ts on (l.timesheet_id = ts.id)"; // Left join for timesheet nme.
}
array_push($fields, 'ei.name as note');
array_push($fields, 'ei.cost as cost');
array_push($fields, 'ei.cost as expense');
+ // Add approved.
+ if ($options['show_approved'])
+ array_push($fields, 'ei.approved');
// Add paid status.
if ($canViewReports && $options['show_paid'])
array_push($fields, 'ei.paid');
if (($canViewReports || $isClient) && $options['show_invoice'])
array_push($fields, 'i.name as invoice');
if ($options['show_timesheet'])
- array_push($fields, 'ts.name as timesheet_name');
+ array_push($fields, 'null as timesheet_name');
// Prepare sql query part for left joins.
$left_joins = null;
if (($canViewReports || $isClient) && $options['show_invoice'])
$left_joins .= " left join tt_invoices i on (i.id = ei.invoice_id and i.status = 1)";
- // Prepare sql query part for inner joins.
- $inner_joins = null;
- if ($user->isPluginEnabled('ts')) {
- $timesheet_option = $options['timesheet'];
- if ($timesheet_option == TIMESHEET_PENDING)
- $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.submit_status = 1 and ts.approval_status is null)";
- else if ($timesheet_option == TIMESHEET_APPROVED)
- $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approval_status = 1)";
- else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
- $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approval_status = 0)";
- else if ($options['show_timesheet'])
- $inner_joins .= " left join tt_timesheets ts on (ei.timesheet_id = ts.id)"; // Left join for timesheet name.
- }
-
$where = ttReportHelper::getExpenseWhere($options);
// Construct sql query for expense items.
- $sql_for_expense_items = "select ".join(', ', $fields)." from tt_expense_items ei $left_joins $inner_joins $where";
+ $sql_for_expense_items = "select ".join(', ', $fields)." from tt_expense_items ei $left_joins $where";
// Construct a union.
$sql = "($sql) union all ($sql_for_expense_items)";
if ($user->isPluginEnabled('ts') && $options['timesheet']) {
$timesheet_option = $options['timesheet'];
if ($timesheet_option == TIMESHEET_PENDING)
- $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.submit_status = 1 and ts.approval_status is null)";
+ $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.submit_status = 1 and ts.approve_status is null)";
else if ($timesheet_option == TIMESHEET_APPROVED)
- $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approval_status = 1)";
+ $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 1)";
else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
- $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approval_status = 0)";
+ $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 0)";
}
// Prepare a query for time items.
$sql = "select $time_part $units_part $cost_part from tt_log l $left_joins $inner_joins $where";
// If we have expenses, query becomes a bit more complex.
if ($options['show_cost'] && $user->isPluginEnabled('ex')) {
- // Prepare sql query part for inner joins.
- $inner_joins = null;
- if ($user->isPluginEnabled('ts') && $options['timesheet']) {
- $timesheet_option = $options['timesheet'];
- if ($timesheet_option == TIMESHEET_PENDING)
- $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.submit_status = 1 and ts.approval_status is null)";
- else if ($timesheet_option == TIMESHEET_APPROVED)
- $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approval_status = 1)";
- else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
- $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approval_status = 0)";
- }
$where = ttReportHelper::getExpenseWhere($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 $inner_joins $where";
+ $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";
}
}
+ // The assignToTimesheet assigns a set of tt_log records to a specific timesheet.
+ static function assignToTimesheet($timesheet_id, $time_log_ids) {
+ global $user;
+ $mdb2 = getConnection();
+
+ $user_id = $user->getUser();
+ $group_id = $user->getGroup();
+ $org_id = $user->org_id;
+
+ if ($time_log_ids) {
+ // Use inner join as a protection mechanism not to do anything with "acted upon" timesheets.
+ // Allow oprations only with pending timesheets.
+ if ($timesheet_id) {
+ // Assigning a timesheet to records.
+ $inner_join = " inner join tt_timesheets ts on (ts.id = $timesheet_id".
+ " and ts.user_id = $user_id and ts.approve_status is null". // Timesheet to assign to is pending.
+ // Part below: existing timesheet either not exists or is also pending.
+ " and (l.timesheet_id is null or (l.timesheet_id = ts.id and ts.approve_status is null)))";
+ } else {
+ $inner_join = " inner join tt_timesheets ts on (ts.id = l.timesheet_id".
+ " and ts.user_id = $user_id and ts.approve_status is null)"; // Do not deassign from acted-upon timesheets.
+ }
+
+ $sql = "update tt_log l $inner_join".
+ " set l.timesheet_id = ".$mdb2->quote($timesheet_id).
+ " where l.id in(".join(', ', $time_log_ids).") and l.user_id = $user_id and l.group_id = $group_id and l.org_id = $org_id";
+ $affected = $mdb2->exec($sql);
+ if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
+ }
+ }
+
+ // The markApproved marks a set of records as either approved or unapproved.
+ static function markApproved($time_log_ids, $expense_item_ids, $approved = true) {
+ global $user;
+ $mdb2 = getConnection();
+
+ $group_id = $user->getGroup();
+ $org_id = $user->org_id;
+
+ $approved_val = (int) $approved;
+ if ($time_log_ids) {
+ $sql = "update tt_log set approved = $approved_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 approved = $approved_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());
+ }
+ }
+
// The markPaid marks a set of records as either paid or unpaid.
static function markPaid($time_log_ids, $expense_item_ids, $paid = true) {
global $user;
$cellLeftAlignedSubtotal = 'font-weight: bold; text-align: left; vertical-align: top;';
$cellRightAlignedSubtotal = 'font-weight: bold; text-align: right; vertical-align: top;';
+ // Determine column span for note field.
+ $colspan = 1;
+ if ($user->can('view_reports') || $user->can('view_all_reports') || $user->isClient()) $colspan++;
+ if ($options['show_client']) $colspan++;
+ if ($options['show_project']) $colspan++;
+ if ($options['show_task']) $colspan++;
+ if ($options['show_custom_field_1']) $colspan++;
+ if ($options['show_start']) $colspan++;
+ if ($options['show_end']) $colspan++;
+ if ($options['show_duration']) $colspan++;
+ if ($options['show_work_units']) $colspan++;
+ if ($options['show_cost']) $colspan++;
+ if ($options['show_approved']) $colspan++;
+ if ($options['show_paid']) $colspan++;
+ if ($options['show_ip']) $colspan++;
+ if ($options['show_invoice']) $colspan++;
+ if ($options['show_timesheet']) $colspan++;
+
// Start creating email body.
$body = '<html>';
$body .= '<head><meta http-equiv="content-type" content="text/html; charset='.CHARSET.'"></head>';
$body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.duration').'</td>';
if ($options['show_work_units'])
$body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.work_units_short').'</td>';
- if ($options['show_note'])
- $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.note').'</td>';
if ($options['show_cost'])
$body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.cost').'</td>';
+ if ($options['show_approved'])
+ $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.approved').'</td>';
if ($options['show_paid'])
$body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.paid').'</td>';
if ($options['show_ip'])
$body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.ip').'</td>';
if ($options['show_invoice'])
$body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.invoice').'</td>';
+ if ($options['show_timesheet'])
+ $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.timesheet').'</td>';
$body .= '</tr>';
// Initialize variables to print subtotals.
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_approved']) $body .= '<td></td>';
if ($options['show_paid']) $body .= '<td></td>';
if ($options['show_ip']) $body .= '<td></td>';
if ($options['show_invoice']) $body .= '<td></td>';
+ if ($options['show_timesheet']) $body .= '<td></td>';
$body .= '</tr>';
$body .= '<tr><td> </td></tr>';
}
$body .= '<td style="'.$cellRightAligned.'">'.$record['duration'].'</td>';
if ($options['show_work_units'])
$body .= '<td style="'.$cellRightAligned.'">'.$record['units'].'</td>';
- if ($options['show_note'])
- $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['note']).'</td>';
if ($options['show_cost'])
$body .= '<td style="'.$cellRightAligned.'">'.$record['cost'].'</td>';
+ if ($options['show_approved']) {
+ $body .= '<td style="'.$cellRightAligned.'">';
+ $body .= $record['approved'] == 1 ? $i18n->get('label.yes') : $i18n->get('label.no');
+ $body .= '</td>';
+ }
if ($options['show_paid']) {
$body .= '<td style="'.$cellRightAligned.'">';
$body .= $record['paid'] == 1 ? $i18n->get('label.yes') : $i18n->get('label.no');
}
if ($options['show_invoice'])
$body .= '<td style="'.$cellRightAligned.'">'.htmlspecialchars($record['invoice']).'</td>';
+ if ($options['show_timesheet'])
+ $body .= '<td style="'.$cellRightAligned.'">'.htmlspecialchars($record['timesheet']).'</td>';
$body .= '</tr>';
-
+ if ($options['show_note'] && $record['note']) {
+ $body .= '<tr style="'.$row_style.'">';
+ $body .= '<td style="'.$cellRightAligned.'">'.$i18n->get('label.note').':</td>';
+ $body .= '<td colspan="'.$colspan.'">'.$record['note'].'</td>';
+ $body .= '</tr>';
+ }
$prev_date = $record['date'];
if ($print_subtotals)
$prev_grouped_by = $record['grouped_by'];
if ($options['show_end']) $body .= '<td></td>';
if ($options['show_duration']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$cur_grouped_by]['time'].'</td>';
if ($options['show_work_units']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$cur_grouped_by]['units'].'</td>';
- if ($options['show_note']) $body .= '<td></td>';
if ($options['show_cost']) {
$body .= '<td style="'.$cellRightAlignedSubtotal.'">';
$body .= ($canViewReports || $isClient) ? $subtotals[$cur_grouped_by]['cost'] : $subtotals[$cur_grouped_by]['expenses'];
$body .= '</td>';
}
+ if ($options['show_approved']) $body .= '<td></td>';
if ($options['show_paid']) $body .= '<td></td>';
if ($options['show_ip']) $body .= '<td></td>';
if ($options['show_invoice']) $body .= '<td></td>';
+ if ($options['show_timesheet']) $body .= '<td></td>';
$body .= '</tr>';
}
if ($options['show_end']) $body .= '<td></td>';
if ($options['show_duration']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$totals['time'].'</td>';
if ($options['show_work_units']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$totals['units'].'</td>';
- if ($options['show_note']) $body .= '<td></td>';
if ($options['show_cost']) {
$body .= '<td nowrap style="'.$cellRightAlignedSubtotal.'">'.htmlspecialchars($user->currency).' ';
$body .= ($canViewReports || $isClient) ? $totals['cost'] : $totals['expenses'];
$body .= '</td>';
}
+ if ($options['show_approved']) $body .= '<td></td>';
if ($options['show_paid']) $body .= '<td></td>';
if ($options['show_ip']) $body .= '<td></td>';
if ($options['show_invoice']) $body .= '<td></td>';
+ if ($options['show_timesheet']) $body .= '<td></td>';
$body .= '</tr>';
$body .= '</table>';
$options['billable'] = $bean->getAttribute('include_records');
$options['invoice'] = $bean->getAttribute('invoice');
$options['paid_status'] = $bean->getAttribute('paid_status');
+ $options['approved'] = $bean->getAttribute('approved');
+ if ($user->isPluginEnabled('ap') && $user->isClient() && !$user->can('view_client_unapproved'))
+ $options['approved'] = 1; // Restrict clients to approved records only.
$options['timesheet'] = $bean->getAttribute('timesheet');
if (is_array($bean->getAttribute('users'))) $options['users'] = join(',', $bean->getAttribute('users'));
$options['period'] = $bean->getAttribute('period');
$options['period_end'] = $bean->getAttribute('end_date');
$options['show_client'] = $bean->getAttribute('chclient');
$options['show_invoice'] = $bean->getAttribute('chinvoice');
+ $options['show_approved'] = $bean->getAttribute('chapproved');
$options['show_paid'] = $bean->getAttribute('chpaid');
$options['show_ip'] = $bean->getAttribute('chip');
$options['show_project'] = $bean->getAttribute('chproject');
if ($user->isPluginEnabled('ts') && $options['timesheet']) {
$timesheet_option = $options['timesheet'];
if ($timesheet_option == TIMESHEET_PENDING)
- $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.submit_status = 1 and ts.approval_status is null)";
+ $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.submit_status = 1 and ts.approve_status is null)";
else if ($timesheet_option == TIMESHEET_APPROVED)
- $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approval_status = 1)";
+ $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 1)";
else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
- $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approval_status = 0)";
+ $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 0)";
}
$join .= $inner_joins;
return $join;
if (ttReportHelper::groupingBy('project', $options)) {
$join .= ' left join tt_projects p on (ei.project_id = p.id)';
}
- // Prepare inner joins.
- $inner_joins = null;
- if ($user->isPluginEnabled('ts') && $options['timesheet']) {
- $timesheet_option = $options['timesheet'];
- if ($timesheet_option == TIMESHEET_PENDING)
- $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.submit_status = 1 and ts.approval_status is null)";
- else if ($timesheet_option == TIMESHEET_APPROVED)
- $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approval_status = 1)";
- else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
- $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approval_status = 0)";
- }
- $join .= $inner_joins;
return $join;
}