From 7aced3f6210fcd9bad8db0f6ff7d12d1f9daf62e Mon Sep 17 00:00:00 2001 From: Nik Okuntseff Date: Sat, 2 Mar 2019 18:44:23 +0000 Subject: [PATCH] Dropped timesheet_id from tt_expense_items and simplified sql. --- WEB-INF/lib/ttGroupExportHelper.class.php | 1 - WEB-INF/lib/ttOrgImportHelper.class.php | 5 +-- WEB-INF/lib/ttReportHelper.class.php | 54 +++-------------------- WEB-INF/templates/footer.tpl | 2 +- dbinstall.php | 11 +++-- mysql.sql | 4 +- 6 files changed, 17 insertions(+), 60 deletions(-) diff --git a/WEB-INF/lib/ttGroupExportHelper.class.php b/WEB-INF/lib/ttGroupExportHelper.class.php index 59bdc8c8..7810c79e 100644 --- a/WEB-INF/lib/ttGroupExportHelper.class.php +++ b/WEB-INF/lib/ttGroupExportHelper.class.php @@ -459,7 +459,6 @@ class ttGroupExportHelper { $expense_item_part .= " user_id=\"".$this->userMap[$expense_item['user_id']]."\""; $expense_item_part .= " client_id=\"".$this->clientMap[$expense_item['client_id']]."\""; $expense_item_part .= " project_id=\"".$this->projectMap[$expense_item['project_id']]."\""; - $expense_item_part .= " timesheet_id=\"".$this->timesheetMap[$expense_item['timesheet_id']]."\""; $expense_item_part .= " name=\"".htmlspecialchars($expense_item['name'])."\""; $expense_item_part .= " cost=\"".$expense_item['cost']."\""; $expense_item_part .= " invoice_id=\"".$this->invoiceMap[$expense_item['invoice_id']]."\""; diff --git a/WEB-INF/lib/ttOrgImportHelper.class.php b/WEB-INF/lib/ttOrgImportHelper.class.php index 0efb6b03..c29c01cc 100644 --- a/WEB-INF/lib/ttOrgImportHelper.class.php +++ b/WEB-INF/lib/ttOrgImportHelper.class.php @@ -756,7 +756,6 @@ class ttOrgImportHelper { $user_id = (int) $fields['user_id']; $client_id = $fields['client_id']; $project_id = $fields['project_id']; - $timesheet_id = $fields['timesheet_id']; $name = $fields['name']; $cost = str_replace(',', '.', $fields['cost']); $invoice_id = $fields['invoice_id']; @@ -766,10 +765,10 @@ class ttOrgImportHelper { $created = ', now(), '.$mdb2->quote($_SERVER['REMOTE_ADDR']).', '.$user->id; $sql = "insert into tt_expense_items". - " (date, user_id, group_id, org_id, client_id, project_id, timesheet_id, name,". + " (date, user_id, group_id, org_id, client_id, project_id, name,". " cost, invoice_id, approved, paid, created, created_ip, created_by, status)". " values (".$mdb2->quote($date).", $user_id, $group_id, $org_id, ".$mdb2->quote($client_id).", ".$mdb2->quote($project_id). - ", ".$mdb2->quote($timesheet_id).", ".$mdb2->quote($name).", ".$mdb2->quote($cost).", ".$mdb2->quote($invoice_id). + ", ".$mdb2->quote($name).", ".$mdb2->quote($cost).", ".$mdb2->quote($invoice_id). ", $approved, $paid $created, ".$mdb2->quote($status).")"; $affected = $mdb2->exec($sql); return (!is_a($affected, 'PEAR_Error')); diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php index 7d2e40c5..abb5ab9e 100644 --- a/WEB-INF/lib/ttReportHelper.class.php +++ b/WEB-INF/lib/ttReportHelper.class.php @@ -107,12 +107,6 @@ class ttReportHelper { $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']) @@ -122,8 +116,9 @@ class ttReportHelper { 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'; @@ -353,7 +348,7 @@ class ttReportHelper { 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; @@ -366,24 +361,10 @@ 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)"; - // 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.approve_status is null)"; - else if ($timesheet_option == TIMESHEET_APPROVED) - $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approve_status = 1)"; - else if ($timesheet_option == TIMESHEET_NOT_APPROVED) - $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approve_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)"; @@ -577,21 +558,10 @@ class ttReportHelper { // 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.approve_status is null)"; - else if ($timesheet_option == TIMESHEET_APPROVED) - $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approve_status = 1)"; - else if ($timesheet_option == TIMESHEET_NOT_APPROVED) - $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approve_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"; @@ -1658,18 +1628,6 @@ class ttReportHelper { 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.approve_status is null)"; - else if ($timesheet_option == TIMESHEET_APPROVED) - $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approve_status = 1)"; - else if ($timesheet_option == TIMESHEET_NOT_APPROVED) - $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approve_status = 0)"; - } - $join .= $inner_joins; return $join; } diff --git a/WEB-INF/templates/footer.tpl b/WEB-INF/templates/footer.tpl index f5c550ce..c7af2f27 100644 --- a/WEB-INF/templates/footer.tpl +++ b/WEB-INF/templates/footer.tpl @@ -12,7 +12,7 @@
- - - + +
 Anuko Time Tracker 1.18.50.4811 | Copyright © Anuko | +  Anuko Time Tracker 1.18.51.4812 | Copyright © Anuko | {$i18n.footer.credits} | {$i18n.footer.license} | {$i18n.footer.improve} diff --git a/dbinstall.php b/dbinstall.php index e33635aa..65a6776d 100644 --- a/dbinstall.php +++ b/dbinstall.php @@ -972,7 +972,7 @@ if ($_POST) { print "Updated $tt_expense_items_updated tt_expense_items records...
\n"; } - if ($_POST["convert11797to11850"]) { + if ($_POST["convert11797to11851"]) { ttExecute("ALTER TABLE `tt_fav_reports` CHANGE `group_by` `group_by1` varchar(20) default NULL"); ttExecute("ALTER TABLE `tt_fav_reports` ADD `group_by2` varchar(20) default NULL AFTER `group_by1`"); ttExecute("ALTER TABLE `tt_fav_reports` ADD `group_by3` varchar(20) default NULL AFTER `group_by2`"); @@ -1120,6 +1120,9 @@ if ($_POST) { ttExecute("UPDATE `tt_site_config` SET param_value = '1.18.49', modified = now() where param_name = 'version_db' and param_value = '1.18.48'"); ttExecute("ALTER TABLE `tt_timesheets` ADD `project_id` int(11) default NULL AFTER `client_id`"); ttExecute("UPDATE `tt_site_config` SET param_value = '1.18.50', modified = now() where param_name = 'version_db' and param_value = '1.18.49'"); + ttExecute("drop index timesheet_idx on tt_expense_items"); + ttExecute("ALTER TABLE `tt_expense_items` DROP `timesheet_id`"); + ttExecute("UPDATE `tt_site_config` SET param_value = '1.18.51', modified = now() where param_name = 'version_db' and param_value = '1.18.50'"); } if ($_POST["cleanup"]) { @@ -1168,7 +1171,7 @@ if ($_POST) {

DB Install

-
Create database structure (v1.18.50) + Create database structure (v1.18.51)
(applies only to new installations, do not execute when updating)
@@ -1213,8 +1216,8 @@ if ($_POST) {
Update database structure (v1.17.97 to v1.18.50)Update database structure (v1.17.97 to v1.18.51)
diff --git a/mysql.sql b/mysql.sql index 525a4769..bec26520 100644 --- a/mysql.sql +++ b/mysql.sql @@ -432,7 +432,6 @@ CREATE TABLE `tt_expense_items` ( `org_id` int(11) default NULL, # organization id `client_id` int(11) default NULL, # client id `project_id` int(11) default NULL, # project id - `timesheet_id` int(11) default NULL, # timesheet id `name` text NOT NULL, # expense item name (what is an expense for) `cost` decimal(10,2) default '0.00', # item cost (including taxes, etc.) `invoice_id` int(11) default NULL, # invoice id @@ -454,7 +453,6 @@ create index user_idx on tt_expense_items(user_id); create index group_idx on tt_expense_items(group_id); create index client_idx on tt_expense_items(client_id); create index project_idx on tt_expense_items(project_id); -create index timesheet_idx on tt_expense_items(timesheet_id); create index invoice_idx on tt_expense_items(invoice_id); @@ -527,4 +525,4 @@ CREATE TABLE `tt_site_config` ( PRIMARY KEY (`param_name`) ); -INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.18.50', now()); # TODO: change when structure changes. +INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.18.51', now()); # TODO: change when structure changes. -- 2.20.1