X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=mysql.sql;h=d6e1d2d321032c47f39ae04ae135ecd75b9b8fde;hb=33fe605aa1936612a3e358102b59e411a0a30f07;hp=3e28325539cbf13f2819ea3dc4919c5684c72be2;hpb=098a79f0819ebb89b7d48df4a6b154af4560f68e;p=timetracker.git diff --git a/mysql.sql b/mysql.sql index 3e283255..d6e1d2d3 100644 --- a/mysql.sql +++ b/mysql.sql @@ -13,22 +13,27 @@ # This table stores settings common to all team members such as language, week start day, etc. # CREATE TABLE `tt_teams` ( - `id` int(11) NOT NULL auto_increment, # team id - `timestamp` timestamp NOT NULL, # modification timestamp - `name` varchar(80) default NULL, # team name - `address` varchar(255) default NULL, # team address, used in invoices - `currency` varchar(7) default NULL, # team currency symbol - `decimal_mark` char(1) NOT NULL default '.', # separator in decimals - `locktime` int(4) default '0', # lock interval in days - `lang` varchar(10) NOT NULL default 'en', # language - `date_format` varchar(20) NOT NULL default '%Y-%m-%d', # date format - `time_format` varchar(20) NOT NULL default '%H:%M', # time format - `week_start` smallint(2) NOT NULL DEFAULT '0', # Week start day, 0 == Sunday. - `tracking_mode` smallint(2) NOT NULL DEFAULT '1', # tracking mode ("projects" or "projects and tasks") - `record_type` smallint(2) NOT NULL DEFAULT '0', # time record type ("start and finish", "duration", or both) - `plugins` varchar(255) default NULL, # a list of enabled plugins for team - `custom_logo` tinyint(4) default '0', # whether to use a custom logo or not - `status` tinyint(4) default '1', # team status + `id` int(11) NOT NULL auto_increment, # team id + `timestamp` timestamp NOT NULL, # modification timestamp + `name` varchar(80) default NULL, # team name + `currency` varchar(7) default NULL, # team currency symbol + `decimal_mark` char(1) NOT NULL default '.', # separator in decimals + `lang` varchar(10) NOT NULL default 'en', # language + `date_format` varchar(20) NOT NULL default '%Y-%m-%d', # date format + `time_format` varchar(20) NOT NULL default '%H:%M', # time format + `week_start` smallint(2) NOT NULL DEFAULT '0', # Week start day, 0 == Sunday. + `tracking_mode` smallint(2) NOT NULL DEFAULT '1', # tracking mode ("time", "projects" or "projects and tasks") + `project_required` smallint(2) NOT NULL DEFAULT '0', # whether a project selection is required or optional + `task_required` smallint(2) NOT NULL DEFAULT '0', # whether a task selection is required or optional + `record_type` smallint(2) NOT NULL DEFAULT '0', # time record type ("start and finish", "duration", or both) + `uncompleted_indicators` smallint(2) NOT NULL DEFAULT '0', # whether to show indicators for users with uncompleted time entries + `bcc_email` varchar(100) default NULL, # bcc email to copy all reports to + `plugins` varchar(255) default NULL, # a list of enabled plugins for team + `lock_spec` varchar(255) default NULL, # Cron specification for record locking, + # for example: "0 10 * * 1" for "weekly on Mon at 10:00". + `workday_hours` decimal(5,2) DEFAULT '8.00', # number of work hours in a regular day + `custom_logo` tinyint(4) default '0', # whether to use a custom logo or not + `status` tinyint(4) default '1', # team status PRIMARY KEY (`id`) ); @@ -68,7 +73,7 @@ CREATE TABLE `tt_projects` ( `name` varchar(80) COLLATE utf8_bin NOT NULL, # project name `description` varchar(255) default NULL, # project description `tasks` text default NULL, # comma-separated list of task ids associated with this project - `status` tinyint(4) default '1', # project status + `status` tinyint(4) default '1', # project status PRIMARY KEY (`id`) ); @@ -112,7 +117,7 @@ create unique index bind_idx on tt_user_project_binds(user_id, project_id); # Structure for table tt_project_task_binds. This table maps projects to assigned tasks. # CREATE TABLE `tt_project_task_binds` ( - `project_id` int(11) NOT NULL, # project id + `project_id` int(11) NOT NULL, # project id `task_id` int(11) NOT NULL # task id ); @@ -136,8 +141,9 @@ CREATE TABLE `tt_log` ( `project_id` int(11) default NULL, # project id `task_id` int(11) default NULL, # task id `invoice_id` int(11) default NULL, # invoice id - `comment` blob, # user provided comment for time record + `comment` text, # user provided comment for time record `billable` tinyint(4) default '0', # whether the record is billable or not + `paid` tinyint(4) default '0', # whether the record is paid `status` tinyint(4) default '1', # time record status PRIMARY KEY (`id`) ); @@ -185,28 +191,32 @@ CREATE TABLE `tt_fav_reports` ( `id` int(11) NOT NULL auto_increment, # favorite report id `name` varchar(200) NOT NULL, # favorite report name `user_id` int(11) NOT NULL, # user id favorite report belongs to + `report_spec` text default NULL, # future replacement field for all report settings `client_id` int(11) default NULL, # client id (if selected) `cf_1_option_id` int(11) default NULL, # custom field 1 option id (if selected) `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 `invoice` tinyint(4) default NULL, # whether to include invoiced, not invoiced, or all records - `users` text default NULL, # Comma-separated list of user ids. Nothing here means "all" users. + `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 `period_start` date default NULL, # period start `period_end` date default NULL, # period end `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_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_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 - `show_cost` tinyint(4) NOT NULL default '0', # whether to show cost 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_custom_field_1` tinyint(4) NOT NULL default '0', # whether to show custom field 1 `show_totals_only` tinyint(4) NOT NULL default '0', # whether to show totals only `group_by` varchar(20) default NULL, # group by field + `status` tinyint(4) default '1', # favorite report status PRIMARY KEY (`id`) ); @@ -222,6 +232,9 @@ CREATE TABLE `tt_cron` ( `next` int(11) default NULL, # UNIX timestamp of when to run next job `report_id` int(11) default NULL, # report id from tt_fav_reports, a report to mail on schedule `email` varchar(100) default NULL, # email to send results to + `cc` varchar(100) default NULL, # cc email to send results to + `subject` varchar(100) default NULL, # email subject + `report_condition` varchar(255) default NULL, # report condition, "count > 0" for sending not empty reports `status` tinyint(4) default '1', # entry status PRIMARY KEY (`id`) ); @@ -293,7 +306,7 @@ CREATE TABLE `tt_custom_fields` ( # CREATE TABLE `tt_custom_field_options` ( `id` int(11) NOT NULL auto_increment, # option id - `field_id` int(11) NOT NULL, # custom field id + `field_id` int(11) NOT NULL, # custom field id `value` varchar(32) NOT NULL default '', # option value PRIMARY KEY (`id`) ); @@ -324,9 +337,10 @@ CREATE TABLE `tt_expense_items` ( `user_id` int(11) NOT NULL, # user id the expense item is reported by `client_id` int(11) default NULL, # client id `project_id` int(11) default NULL, # project id - `name` varchar(255) NOT NULL, # expense item name (what is an expense for) + `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 + `paid` tinyint(4) default '0', # whether the item is paid `status` tinyint(4) default '1', # item status PRIMARY KEY (`id`) ); @@ -337,3 +351,32 @@ create index user_idx on tt_expense_items(user_id); create index client_idx on tt_expense_items(client_id); create index project_idx on tt_expense_items(project_id); create index invoice_idx on tt_expense_items(invoice_id); + + +# +# Structure for table tt_predefined_expenses. +# This table keeps names and costs for predefined expenses. +# +CREATE TABLE `tt_predefined_expenses` ( + `id` int(11) NOT NULL auto_increment, # predefined expense id + `team_id` int(11) NOT NULL, # team id + `name` varchar(255) NOT NULL, # predefined expense name, such as mileage + `cost` decimal(10,2) default '0.00', # cost for one unit + PRIMARY KEY (`id`) +); + + +# +# Structure for table tt_monthly_quotas. +# This table keeps monthly work hour quotas for teams. +# +CREATE TABLE `tt_monthly_quotas` ( + `team_id` int(11) NOT NULL, # team id + `year` smallint(5) UNSIGNED NOT NULL, # quota year + `month` tinyint(3) UNSIGNED NOT NULL, # quota month + `quota` decimal(5,2) NOT NULL, # number of work hours in specified month and year + PRIMARY KEY (`team_id`,`year`,`month`) +); + +ALTER TABLE `tt_monthly_quotas` + ADD CONSTRAINT `FK_TT_TEAM_CONSTRAING` FOREIGN KEY (`team_id`) REFERENCES `tt_teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;