X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=mysql.sql;h=e526e614e5c48e1503cf1ab07b1bf14ce9297c5f;hb=ccf6c015e1f48d747ae697944c3d50e8fccf8080;hp=0218cf75041612271d1ebfd3b6ac101d26813295;hpb=6d72b9b2a73391e8e6b2a3586b4972c1a2e8160d;p=timetracker.git diff --git a/mysql.sql b/mysql.sql index 0218cf75..e526e614 100644 --- a/mysql.sql +++ b/mysql.sql @@ -16,6 +16,7 @@ CREATE TABLE `tt_groups` ( `id` int(11) NOT NULL auto_increment, # group id `parent_id` int(11) default NULL, # parent group id `org_id` int(11) default NULL, # organization id (id of top group) + `group_key` varchar(32) default NULL, # group key `name` varchar(80) default NULL, # group name `description` varchar(255) default NULL, # group description `currency` varchar(7) default NULL, # currency symbol @@ -75,7 +76,7 @@ create unique index role_idx on tt_roles(group_id, rank, status); # Insert site-wide roles - site administrator and top manager. INSERT INTO `tt_roles` (`group_id`, `name`, `rank`, `rights`) VALUES (0, 'Site administrator', 1024, 'administer_site'); -INSERT INTO `tt_roles` (`group_id`, `name`, `rank`, `rights`) VALUES (0, 'Top manager', 512, 'track_own_time,track_own_expenses,view_own_reports,view_own_timesheets,manage_own_timesheets,view_own_charts,view_own_projects,view_own_tasks,manage_own_settings,view_users,view_client_reports,view_client_timesheets,view_client_invoices,track_time,track_expenses,view_reports,view_timesheets,manage_timesheets,approve_timesheets,view_charts,view_own_clients,override_punch_mode,override_own_punch_mode,override_date_lock,override_own_date_lock,swap_roles,manage_own_account,manage_users,manage_projects,manage_tasks,manage_custom_fields,manage_clients,manage_invoices,override_allow_ip,manage_basic_settings,view_all_reports,view_all_timesheets,manage_all_timesheets,manage_features,manage_advanced_settings,manage_roles,export_data,approve_all_timesheets,manage_subgroups,delete_group'); +INSERT INTO `tt_roles` (`group_id`, `name`, `rank`, `rights`) VALUES (0, 'Top manager', 512, 'track_own_time,track_own_expenses,view_own_reports,view_own_charts,view_own_projects,view_own_tasks,manage_own_settings,view_users,view_client_reports,view_client_invoices,track_time,track_expenses,view_reports,approve_reports,approve_timesheets,view_charts,view_own_clients,override_punch_mode,override_own_punch_mode,override_date_lock,override_own_date_lock,swap_roles,manage_own_account,manage_users,manage_projects,manage_tasks,manage_custom_fields,manage_clients,manage_invoices,override_allow_ip,manage_basic_settings,view_all_reports,manage_features,manage_advanced_settings,manage_roles,export_data,approve_all_reports,approve_own_timesheets,manage_subgroups,view_client_unapproved,delete_group'); # @@ -201,6 +202,7 @@ CREATE TABLE `tt_log` ( `invoice_id` int(11) default NULL, # invoice id `comment` text, # user provided comment for time record `billable` tinyint(4) default 0, # whether the record is billable or not + `approved` tinyint(4) default 0, # whether the record is approved `paid` tinyint(4) default 0, # whether the record is paid `created` datetime default NULL, # creation timestamp `created_ip` varchar(45) default NULL, # creator ip @@ -266,7 +268,9 @@ CREATE TABLE `tt_fav_reports` ( `project_id` int(11) default NULL, # project id (if selected) `task_id` int(11) default NULL, # task id (if selected) `billable` tinyint(4) default NULL, # whether to include billable, not billable, or all records + `approved` tinyint(4) default NULL, # whether to include approved, unapproved, or all records `invoice` tinyint(4) default NULL, # whether to include invoiced, not invoiced, or all records + `timesheet` tinyint(4) default NULL, # include records with a specific timesheet status, or all records `paid_status` tinyint(4) default NULL, # whether to include paid, not paid, or all records `users` text default NULL, # Comma-separated list of user ids. Nothing here means "all" users. `period` tinyint(4) default NULL, # selected period type for report @@ -277,12 +281,14 @@ CREATE TABLE `tt_fav_reports` ( `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_timesheet` tinyint(4) NOT NULL default 0, # whether to show timesheet 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 `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 `show_note` tinyint(4) NOT NULL default 0, # whether to show note column + `show_approved` tinyint(4) NOT NULL default 0, # whether to show approved column `show_custom_field_1` tinyint(4) NOT NULL default 0, # whether to show custom field 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 @@ -427,10 +433,10 @@ 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 + `approved` tinyint(4) default 0, # whether the item is approved `paid` tinyint(4) default 0, # whether the item is paid `created` datetime default NULL, # creation timestamp `created_ip` varchar(45) default NULL, # creator ip @@ -448,7 +454,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); @@ -489,11 +494,14 @@ CREATE TABLE `tt_timesheets` ( `group_id` int(11) default NULL, # group id `org_id` int(11) default NULL, # organization id `client_id` int(11) default NULL, # client id + `project_id` int(11) default NULL, # project id `name` varchar(80) COLLATE utf8mb4_bin NOT NULL, # timesheet name + `comment` text, # timesheet comment + `start_date` date NOT NULL, # timesheet start date + `end_date` date NOT NULL, # timesheet end date `submit_status` tinyint(4) default NULL, # submit status - `submitter_comment` text, # submitter comment - `approval_status` tinyint(4) default NULL, # approval status - `manager_comment` text, # manager comment + `approve_status` tinyint(4) default NULL, # approve status + `approve_comment` text, # approve comment `created` datetime default NULL, # creation timestamp `created_ip` varchar(45) default NULL, # creator ip `created_by` int(11) default NULL, # creator user_id @@ -505,6 +513,53 @@ CREATE TABLE `tt_timesheets` ( ); +# +# Structure for table tt_templates. +# This table keeps templates used in groups. +# +CREATE TABLE `tt_templates` ( + `id` int(11) NOT NULL auto_increment, # template id + `group_id` int(11) default NULL, # group id + `org_id` int(11) default NULL, # organization id + `name` varchar(80) COLLATE utf8mb4_bin NOT NULL, # template name + `description` varchar(255) default NULL, # template description + `content` text, # template content + `created` datetime default NULL, # creation timestamp + `created_ip` varchar(45) default NULL, # creator ip + `created_by` int(11) default NULL, # creator user_id + `modified` datetime default NULL, # modification timestamp + `modified_ip` varchar(45) default NULL, # modifier ip + `modified_by` int(11) default NULL, # modifier user_id + `status` tinyint(4) default 1, # template status + PRIMARY KEY (`id`) +); + + +# +# Structure for table tt_files. +# This table keeps file attachment information. +# +CREATE TABLE `tt_files` ( + `id` int(10) unsigned NOT NULL auto_increment, # file id + `group_id` int(10) unsigned, # group id + `org_id` int(10) unsigned, # organization id + `remote_id` bigint(20) unsigned, # file id in storage facility + `file_key` varchar(32), # file key + `entity_type` varchar(32), # type of entity file is associated with (project, task, etc.) + `entity_id` int(10) unsigned, # entity id + `file_name` varchar(80) COLLATE utf8mb4_bin NOT NULL, # file name + `description` varchar(255) default NULL, # file description + `created` datetime default NULL, # creation timestamp + `created_ip` varchar(45) default NULL, # creator ip + `created_by` int(10) unsigned, # creator user_id + `modified` datetime default NULL, # modification timestamp + `modified_ip` varchar(45) default NULL, # modifier ip + `modified_by` int(10) unsigned, # modifier user_id + `status` tinyint(1) default 1, # file status + PRIMARY KEY (`id`) +); + + # # Structure for table tt_site_config. This table stores configuration data # for Time Tracker site as a whole. @@ -518,4 +573,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.40', now()); # TODO: change when structure changes. +INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.18.61', now()); # TODO: change when structure changes.