X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=WEB-INF%2Flib%2FttReportHelper.class.php;h=42463d08af6dcfce113a8c8f59015823ba37293b;hb=47676f16efdde0b36a4ed09d2ab0476584aeb2a6;hp=acf33681dbedcb1715100934b4ebb045e181bdd5;hpb=3d9beabc77b40f26d2ae76269d03ca1c379f0bd0;p=timetracker.git diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php index acf33681..42463d08 100644 --- a/WEB-INF/lib/ttReportHelper.class.php +++ b/WEB-INF/lib/ttReportHelper.class.php @@ -37,8 +37,7 @@ require_once(dirname(__FILE__).'/../../plugins/CustomFields.class.php'); class ttReportHelper { // getWhere prepares a WHERE clause for a report query. - // Note: $options is a future replacement of $bean, which is work in progress. - static function getWhere($bean, $options) { + static function getWhere($options) { global $user; // Prepare dropdown parts. @@ -57,71 +56,13 @@ class ttReportHelper { if ($options['paid_status']=='1') $dropdown_parts .= ' and l.paid = 1'; if ($options['paid_status']=='2') $dropdown_parts .= ' and l.paid = 0'; - // Note: "Prepare sql query part for user list" is different in getFavWhere because of - // special meaning of NULL value (all "active" users). - // - // If we are merging into one function, one needs to take care of this, perhaps, with redesign. - // Prepare sql query part for user list. $userlist = $options['users'] ? $options['users'] : '-1'; - $user_list_part = null; 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; - - // Prepare sql query part for where. - 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'])); - } - $where = " where l.status = 1 and l.date >= '".$period->getStartDate(DB_DATEFORMAT)."' and l.date <= '".$period->getEndDate(DB_DATEFORMAT)."'". - " $user_list_part $dropdown_parts"; - return $where; - } - - // getFavWhere prepares a WHERE clause for a favorite report query. - static function getFavWhere($options) { - global $user; - - // Prepare dropdown parts. - $dropdown_parts = ''; - if ($options['client_id']) - $dropdown_parts .= ' and l.client_id = '.$options['client_id']; - elseif ($user->isClient() && $user->client_id) - $dropdown_parts .= ' and l.client_id = '.$user->client_id; - if ($options['cf_1_option_id']) $dropdown_parts .= ' and l.id in(select log_id from tt_custom_field_log where status = 1 and option_id = '.$options['cf_1_option_id'].')'; - if ($options['project_id']) $dropdown_parts .= ' and l.project_id = '.$options['project_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['paid_status']=='1') $dropdown_parts .= ' and l.paid = 1'; - if ($options['paid_status']=='2') $dropdown_parts .= ' and l.paid = 0'; - - // Prepare user list part. - $userlist = -1; - if (($user->can('view_reports') || $user->isClient())) { - if ($options['users']) - $userlist = $options['users']; - else { - $active_users = ttTeamHelper::getActiveUsers(); - foreach ($active_users as $single_user) - $users[] = $single_user['id']; - $userlist = join(',', $users); - } - } - // Prepare sql query part for user list. - $user_list_part = null; - if ($user->can('view_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->getActiveGroup(); // Prepare sql query part for where. if ($options['period']) @@ -138,13 +79,13 @@ class ttReportHelper { } // getExpenseWhere prepares WHERE clause for expenses query in a report. - static function getExpenseWhere($bean, $options) { + static function getExpenseWhere($options) { global $user; // Prepare dropdown parts. $dropdown_parts = ''; if ($options['client_id']) - $dropdown_parts .= ' and l.client_id = '.$options['client_id']; + $dropdown_parts .= ' and ei.client_id = '.$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']; @@ -155,60 +96,11 @@ class ttReportHelper { // Prepare sql query part for user list. $userlist = $options['users'] ? $options['users'] : '-1'; - $user_list_part = null; 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; - - // Prepare sql query part for where. - 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'])); - } - $where = " where ei.status = 1 and ei.date >= '".$period->getStartDate(DB_DATEFORMAT)."' and ei.date <= '".$period->getEndDate(DB_DATEFORMAT)."'". - " $user_list_part $dropdown_parts"; - return $where; - } - - // getFavExpenseWhere prepares a WHERE clause for expenses query in a favorite report. - static function getFavExpenseWhere($options) { - global $user; - - // Prepare dropdown parts. - $dropdown_parts = ''; - if ($options['client_id']) - $dropdown_parts .= ' and ei.client_id = '.$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['paid_status']=='1') $dropdown_parts .= ' and ei.paid = 1'; - if ($options['paid_status']=='2') $dropdown_parts .= ' and ei.paid = 0'; - - // Prepare user list part. - $userlist = -1; - if (($user->can('view_reports') || $user->isClient())) { - if ($options['users']) - $userlist = $options['users']; - else { - $active_users = ttTeamHelper::getActiveUsers(); - foreach ($active_users as $single_user) - $users[] = $single_user['id']; - $userlist = join(',', $users); - } - } - // Prepare sql query part for user list. - $user_list_part = null; - if ($user->can('view_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->getActiveGroup(); // Prepare sql query part for where. if ($options['period']) @@ -235,8 +127,8 @@ class ttReportHelper { $canViewReports = $user->can('view_reports'); $isClient = $user->isClient(); - $group_by_option = $bean->getAttribute('group_by'); - $convertTo12Hour = ('%I:%M %p' == $user->time_format) && ($bean->getAttribute('chstart') || $bean->getAttribute('chfinish')); + $group_by_option = $options['group_by']; + $convertTo12Hour = ('%I:%M %p' == $user->time_format) && ($options['show_start'] || $options['show_end']); // Prepare a query for time items in tt_log table. $fields = array(); // An array of fields for database query. @@ -246,16 +138,16 @@ class ttReportHelper { if($canViewReports || $isClient) array_push($fields, 'u.name as user'); // Add client name if it is selected. - if ($bean->getAttribute('chclient') || 'client' == $group_by_option) + if ($options['show_client'] || 'client' == $group_by_option) array_push($fields, 'c.name as client'); // Add project name if it is selected. - if ($bean->getAttribute('chproject') || 'project' == $group_by_option) + if ($options['show_project'] || 'project' == $group_by_option) array_push($fields, 'p.name as project'); // Add task name if it is selected. - if ($bean->getAttribute('chtask') || 'task' == $group_by_option) + if ($options['show_task'] || 'task' == $group_by_option) array_push($fields, 't.name as task'); // Add custom field. - $include_cf_1 = $bean->getAttribute('chcf_1') || 'cf_1' == $group_by_option; + $include_cf_1 = $options['show_custom_field_1'] || 'cf_1' == $group_by_option; if ($include_cf_1) { $custom_fields = new CustomFields($user->group_id); $cf_1_type = $custom_fields->fields[0]['type']; @@ -266,16 +158,18 @@ class ttReportHelper { } } // Add start time. - if ($bean->getAttribute('chstart')) { + if ($options['show_start']) { array_push($fields, "l.start as unformatted_start"); array_push($fields, "TIME_FORMAT(l.start, '%k:%i') as start"); } // Add finish time. - if ($bean->getAttribute('chfinish')) + if ($options['show_end']) array_push($fields, "TIME_FORMAT(sec_to_time(time_to_sec(l.start) + time_to_sec(l.duration)), '%k:%i') as finish"); // Add duration. - if ($bean->getAttribute('chduration')) + if ($options['show_duration']) array_push($fields, "TIME_FORMAT(l.duration, '%k:%i') as duration"); + +// TODO: refactoring in progress down from here... The above is identical to getFavItems and is ready to merge. // Add work units. if ($bean->getAttribute('chunits')) { if ($user->unit_totals_only) @@ -333,7 +227,7 @@ class ttReportHelper { if ($includeCost && MODE_TIME != $user->tracking_mode) $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($bean, $options); + $where = ttReportHelper::getWhere($options); // Construct sql query for tt_log items. $sql = "select ".join(', ', $fields)." from tt_log l $left_joins $where"; @@ -401,7 +295,7 @@ class ttReportHelper { if (($canViewReports || $isClient) && $bean->getAttribute('chinvoice')) $left_joins .= " left join tt_invoices i on (i.id = ei.invoice_id and i.status = 1)"; - $where = ttReportHelper::getExpenseWhere($bean, $options); + $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"; @@ -608,7 +502,7 @@ class ttReportHelper { if ($includeCost && MODE_TIME != $user->tracking_mode) $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::getFavWhere($options); + $where = ttReportHelper::getWhere($options); // Construct sql query for tt_log items. $sql = "select ".join(', ', $fields)." from tt_log l $left_joins $where"; @@ -674,7 +568,7 @@ class ttReportHelper { if (($canViewReports || $isClient) && $options['show_invoice']) $left_joins .= " left join tt_invoices i on (i.id = ei.invoice_id and i.status = 1)"; - $where = ttReportHelper::getFavExpenseWhere($options); + $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"; @@ -784,7 +678,7 @@ class ttReportHelper { break; } - $where = ttReportHelper::getWhere($bean, $options); + $where = ttReportHelper::getWhere($options); if ($bean->getAttribute('chcost')) { if (MODE_TIME == $user->tracking_mode) { if ($group_by_option != 'user') @@ -851,7 +745,7 @@ class ttReportHelper { break; } - $where = ttReportHelper::getExpenseWhere($bean, $options); + $where = ttReportHelper::getExpenseWhere($options); $sql_for_expenses = "select $group_field as group_field, null as time"; if ($bean->getAttribute('chunits')) $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"; @@ -935,7 +829,7 @@ class ttReportHelper { break; } - $where = ttReportHelper::getFavWhere($options); + $where = ttReportHelper::getWhere($options); if ($options['show_cost']) { if (MODE_TIME == $user->tracking_mode) { if ($group_by_option != 'user') @@ -1003,7 +897,7 @@ class ttReportHelper { break; } - $where = ttReportHelper::getFavExpenseWhere($options); + $where = ttReportHelper::getExpenseWhere($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"; @@ -1049,7 +943,7 @@ class ttReportHelper { $mdb2 = getConnection(); - $where = ttReportHelper::getWhere($bean, $options); + $where = ttReportHelper::getWhere($options); // Prepare parts. $time_part = "sum(time_to_sec(l.duration)) as time"; @@ -1076,7 +970,7 @@ class ttReportHelper { // If we have expenses, query becomes a bit more complex. if ($bean->getAttribute('chcost') && $user->isPluginEnabled('ex')) { - $where = ttReportHelper::getExpenseWhere($bean, $options); + $where = ttReportHelper::getExpenseWhere($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"; @@ -1131,7 +1025,7 @@ class ttReportHelper { $mdb2 = getConnection(); - $where = ttReportHelper::getFavWhere($options); + $where = ttReportHelper::getWhere($options); // Prepare parts. $time_part = "sum(time_to_sec(l.duration)) as time"; @@ -1158,7 +1052,7 @@ class ttReportHelper { // If we have expenses, query becomes a bit more complex. if ($options['show_cost'] && $user->isPluginEnabled('ex')) { - $where = ttReportHelper::getFavExpenseWhere($options); + $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"; @@ -1912,24 +1806,34 @@ class ttReportHelper { $options['period'] = $bean->getAttribute('period'); $options['period_start'] = $bean->getAttribute('start_date'); $options['period_end'] = $bean->getAttribute('end_date'); + $options['show_client'] = $bean->getAttribute('chclient'); /* * TODO: remaining fields to fill in... - `show_client` tinyint(4) NOT NULL default 0, # whether to show client column `show_invoice` tinyint(4) NOT NULL default 0, # whether to show invoice column `show_paid` tinyint(4) NOT NULL default 0, # whether to show paid column `show_ip` tinyint(4) NOT NULL default 0, # whether to show ip column - `show_project` tinyint(4) NOT NULL default 0, # whether to show project column - `show_start` tinyint(4) NOT NULL default 0, # whether to show start field - `show_duration` tinyint(4) NOT NULL default 0, # whether to show duration field + */ + $options['show_project'] = $bean->getAttribute('chproject'); + $options['show_start'] = $bean->getAttribute('chstart'); + $options['show_duration'] = $bean->getAttribute('chduration'); +/* `show_cost` tinyint(4) NOT NULL default 0, # whether to show cost field - `show_task` tinyint(4) NOT NULL default 0, # whether to show task column - `show_end` tinyint(4) NOT NULL default 0, # whether to show end field + */ + $options['show_task'] = $bean->getAttribute('chtask'); + $options['show_end'] = $bean->getAttribute('chfinish'); +/* `show_note` tinyint(4) NOT NULL default 0, # whether to show note column `show_custom_field_1` tinyint(4) NOT NULL default 0, # whether to show custom field 1 + */ + $options['show_custom_field_1'] = $bean->getAttribute('chcf_1'); + /* `show_work_units` tinyint(4) NOT NULL default 0, # whether to show work units `show_totals_only` tinyint(4) NOT NULL default 0, # whether to show totals only - `group_by` varchar(20) default NULL, # group by field + **/ + $options['group_by'] = $bean->getAttribute('group_by'); +/* + * TODO: remaining fields to fill in... `status` tinyint(4) default 1, # favorite report status PRIMARY KEY (`id`) );