require_once(dirname(__FILE__).'/../../plugins/CustomFields.class.php');
+// Definitions of types for timesheet dropdown.
+define('TIMESHEET_ALL', 0); // Include all records.
+define('TIMESHEET_NOT_ASSIGNED', 1); // Include records not assigned to timesheets.
+define('TIMESHEET_ASSIGNED', 2); // Include records assigned to timesheets.
+define('TIMESHEET_PENDING', 3); // Include records in submitted timesheets that are pending manager approval.
+define('TIMESHEET_APPROVED', 4); // Include records in approved timesheets.
+define('TIMESHEET_NOT_APPROVED', 5); // Include records in disapproved timesheets.
+
// Class ttReportHelper is used for help with reports.
class ttReportHelper {
$group_id = $user->getGroup();
$org_id = $user->org_id;
+ // A shortcut for timesheets.
+ if ($options['timesheet_id']) {
+ $where = " where l.timesheet_id = ".$options['timesheet_id']." and l.group_id = $group_id and l.org_id = $org_id";
+ return $where;
+ }
+
// Prepare dropdown parts.
$dropdown_parts = '';
if ($options['client_id'])
if ($options['task_id']) $dropdown_parts .= ' and l.task_id = '.$options['task_id'];
if ($options['billable']=='1') $dropdown_parts .= ' and l.billable = 1';
if ($options['billable']=='2') $dropdown_parts .= ' and l.billable = 0';
- if ($options['invoice']=='1') $dropdown_parts .= ' and l.invoice_id is not NULL';
- if ($options['invoice']=='2') $dropdown_parts .= ' and l.invoice_id is NULL';
+ if ($options['invoice']=='1') $dropdown_parts .= ' and l.invoice_id is not null';
+ 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['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'])
elseif ($user->isClient() && $user->client_id)
$dropdown_parts .= ' and ei.client_id = '.$user->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['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 ($options['paid_status']=='1') $dropdown_parts .= ' and ei.paid = 1';
if ($options['paid_status']=='2') $dropdown_parts .= ' and ei.paid = 0';
// Add invoice name if it is selected.
if (($canViewReports || $isClient) && $options['show_invoice'])
array_push($fields, 'i.name as invoice');
+ // Add timesheet name if it is selected.
+ if ($options['show_timesheet'])
+ array_push($fields, 'ts.name as timesheet_name');
// Prepare sql query part for left joins.
$left_joins = null;
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)";
+ // 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 (l.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 (l.timesheet_id = ts.id and ts.approval_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)";
+ else if ($options['show_timesheet'])
+ $inner_joins .= " left join tt_timesheets ts on (l.timesheet_id = ts.id)"; // Left join for timesheet nme.
+ }
+
$where = ttReportHelper::getWhere($options);
// Construct sql query for tt_log items.
- $sql = "select ".join(', ', $fields)." from tt_log l $left_joins $where";
+ $sql = "select ".join(', ', $fields)." from tt_log l $left_joins $inner_joins $where";
// If we don't have expense items (such as when the Expenses plugin is disabled), the above is all sql we need,
// with an exception of sorting part, that is added in the end.
// Add invoice name if it is selected.
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');
// 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 $where";
+ $sql_for_expense_items = "select ".join(', ', $fields)." from tt_expense_items ei $left_joins $inner_joins $where";
// Construct a union.
$sql = "($sql) union all ($sql_for_expense_items)";
$left_joins = "left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)";
}
}
+ // 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 (l.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 (l.timesheet_id = ts.id and ts.approval_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)";
+ }
// Prepare a query for time items.
- $sql = "select $time_part $units_part $cost_part from tt_log l $left_joins $where";
+ $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 $where";
+ $sql_for_expenses .= ", sum(cost) as cost, sum(cost) as expenses from tt_expense_items ei $inner_joins $where";
// Create a combined query.
$combined = "select sum(time) as time";
$options['billable'] = $bean->getAttribute('include_records');
$options['invoice'] = $bean->getAttribute('invoice');
$options['paid_status'] = $bean->getAttribute('paid_status');
+ $options['timesheet'] = $bean->getAttribute('timesheet');
+ if ($user->isPluginEnabled('ts') && $user->isClient() && !$user->can('view_client_unapproved'))
+ $options['timesheet'] = TIMESHEET_APPROVED; // Restrict clients to approved timesheet records only.
if (is_array($bean->getAttribute('users'))) $options['users'] = join(',', $bean->getAttribute('users'));
$options['period'] = $bean->getAttribute('period');
$options['period_start'] = $bean->getAttribute('start_date');
$options['show_note'] = $bean->getAttribute('chnote');
$options['show_custom_field_1'] = $bean->getAttribute('chcf_1');
$options['show_work_units'] = $bean->getAttribute('chunits');
+ $options['show_timesheet'] = $bean->getAttribute('chtimesheet');
$options['show_totals_only'] = $bean->getAttribute('chtotalsonly');
$options['group_by1'] = $bean->getAttribute('group_by1');
$options['group_by2'] = $bean->getAttribute('group_by2');
if ($options['show_cost'] && $trackingMode != MODE_TIME) {
$join .= ' left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_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 (l.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 (l.timesheet_id = ts.id and ts.approval_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)";
+ }
+ $join .= $inner_joins;
return $join;
}
// makeJoinExpensesPart builds a left join part for getSubtotals query for expense items.
static function makeJoinExpensesPart($options) {
+ global $user;
+
if (ttReportHelper::groupingBy('user', $options)) {
$join .= ' left join tt_users u on (ei.user_id = u.id)';
}
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;
}
projects / timetracker.git / blobdiff