· 8 years ago · Jun 18, 2018, 06:52 AM
1
2SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
3
4
5
6
7CREATE TABLE IF NOT EXISTS `custom_iconomix_reporting` (
8 `major_code` int(11) NOT NULL,
9 `minor_code` int(11) NOT NULL,
10 `is_group` tinyint(1) NOT NULL default '0',
11 `category` varchar(255) NOT NULL,
12 `subcategory` varchar(255) NOT NULL default '',
13 `notes` text,
14 UNIQUE KEY `major_code` (`major_code`,`minor_code`)
15) ENGINE=MyISAM DEFAULT CHARSET=utf8;
16
17
18
19CREATE TABLE IF NOT EXISTS `mgw_billing_bills` (
20 `id` int(11) NOT NULL default '0',
21 `userid` int(11) NOT NULL default '0',
22 `date` timestamp NOT NULL default '0000-00-00 00:00:00',
23 `projectid` int(11) NOT NULL default '0',
24 `status` varchar(50) NOT NULL default '',
25 `amount` float(10,2) NOT NULL default '0.00',
26 `payday` timestamp NOT NULL default '0000-00-00 00:00:00',
27 `paid_on` timestamp NOT NULL default '0000-00-00 00:00:00',
28 `tax_percent` float NOT NULL default '0.076',
29 `lu_user` int(11) NOT NULL default '0',
30 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
31 PRIMARY KEY (`id`)
32) ENGINE=MyISAM DEFAULT CHARSET=utf8;
33
34
35
36CREATE TABLE IF NOT EXISTS `mgw_billing_items` (
37 `id` int(11) NOT NULL auto_increment,
38 `billingid` int(11) NOT NULL default '0',
39 `amount` float(10,2) NOT NULL default '0.00',
40 `description` mediumtext NOT NULL,
41 `type` int(10) unsigned NOT NULL default '0',
42 `owner` int(11) NOT NULL default '0',
43 `lu_user` int(11) NOT NULL default '0',
44 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
45 PRIMARY KEY (`id`),
46 KEY `type` (`type`)
47) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1969 ;
48
49
50
51CREATE TABLE IF NOT EXISTS `mgw_billing_item_type` (
52 `id` int(11) NOT NULL auto_increment,
53 `name` varchar(255) NOT NULL default '',
54 `description` mediumtext NOT NULL,
55 `owner` int(11) NOT NULL default '0',
56 `lu_user` int(11) NOT NULL default '0',
57 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
58 PRIMARY KEY (`id`),
59 UNIQUE KEY `id` (`id`),
60 UNIQUE KEY `id_2` (`id`)
61) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
62
63
64
65CREATE TABLE IF NOT EXISTS `mgw_billing_status` (
66 `id` int(11) NOT NULL auto_increment,
67 `name` varchar(255) NOT NULL default '',
68 `description` mediumtext NOT NULL,
69 `owner` int(11) NOT NULL default '0',
70 `lu_user` int(11) NOT NULL default '0',
71 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
72 PRIMARY KEY (`id`),
73 UNIQUE KEY `id` (`id`),
74 UNIQUE KEY `id_2` (`id`)
75) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
76
77
78
79CREATE TABLE IF NOT EXISTS `mgw_calendar` (
80 `id` int(11) NOT NULL default '0',
81 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
82 `userid` int(11) NOT NULL default '0',
83 `subject` mediumtext NOT NULL,
84 `location` varchar(100) NOT NULL default '',
85 `private` int(11) NOT NULL default '0',
86 `duration` int(11) NOT NULL default '0',
87 `external` varchar(200) NOT NULL default '',
88 `inituserid` int(11) NOT NULL default '0',
89 `day_ind` int(11) NOT NULL default '0',
90 `rep_from` timestamp NOT NULL default '0000-00-00 00:00:00',
91 `rep_until` timestamp NOT NULL default '0000-00-00 00:00:00',
92 `dategroup_id` int(11) NOT NULL default '0',
93 `holiday_id` int(11) NOT NULL default '0',
94 PRIMARY KEY (`id`,`userid`)
95) ENGINE=MyISAM DEFAULT CHARSET=utf8;
96
97
98
99CREATE TABLE IF NOT EXISTS `mgw_calendar_tmp` (
100 `id` int(11) NOT NULL default '0',
101 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
102 `userid` int(11) NOT NULL default '0',
103 `subject` mediumtext NOT NULL,
104 `location` varchar(100) NOT NULL default '',
105 `private` int(11) NOT NULL default '0',
106 `duration` int(11) NOT NULL default '0',
107 `external` varchar(200) NOT NULL default '',
108 `inituserid` int(11) NOT NULL default '0',
109 `day_ind` int(11) NOT NULL default '0',
110 `_repeat` int(11) NOT NULL default '0',
111 `rep_from` timestamp NOT NULL default '0000-00-00 00:00:00',
112 `rep_until` timestamp NOT NULL default '0000-00-00 00:00:00',
113 `dategroup_id` int(11) NOT NULL default '0',
114 `holiday_id` int(11) NOT NULL default '0',
115 PRIMARY KEY (`id`,`userid`)
116) ENGINE=MyISAM DEFAULT CHARSET=utf8;
117
118
119
120CREATE TABLE IF NOT EXISTS `mgw_companies` (
121 `id` int(11) NOT NULL auto_increment,
122 `name1` varchar(200) default NULL,
123 `name2` varchar(150) default NULL,
124 `street` varchar(200) default NULL,
125 `additional` varchar(150) default NULL,
126 `zip` varchar(12) default NULL,
127 `city` varchar(200) default NULL,
128 `state` varchar(50) default NULL,
129 `countrycode` varchar(5) default NULL,
130 `telephone` varchar(100) default NULL,
131 `fax` varchar(100) default NULL,
132 `url` varchar(150) default NULL,
133 `email` varchar(100) default NULL,
134 `note` mediumtext,
135 `groupid` int(11) NOT NULL default '0',
136 `ispublic` int(11) default NULL,
137 `owner` int(11) default NULL,
138 `lu_user` int(11) default NULL,
139 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
140 PRIMARY KEY (`id`)
141) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1125 ;
142
143
144
145CREATE TABLE IF NOT EXISTS `mgw_config` (
146 `userid` int(11) NOT NULL default '0',
147 `configtagname` varchar(100) NOT NULL default '0',
148 `serialnr` int(11) NOT NULL default '0',
149 `value_str` mediumtext NOT NULL,
150 `value_int` int(11) NOT NULL default '0',
151 `value_txt` mediumtext NOT NULL,
152 `lu_user` int(11) NOT NULL default '0',
153 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
154 PRIMARY KEY (`userid`,`configtagname`,`serialnr`)
155) ENGINE=MyISAM DEFAULT CHARSET=utf8;
156
157
158
159CREATE TABLE IF NOT EXISTS `mgw_configtags` (
160 `configtagname` varchar(100) NOT NULL default '',
161 `modulename` varchar(100) NOT NULL default '0',
162 `description` varchar(255) NOT NULL default '',
163 `multipleselections` int(11) NOT NULL default '0',
164 `multipledimensions` int(11) NOT NULL default '0',
165 `valuetype` varchar(5) NOT NULL default '',
166 `lu_user` int(11) NOT NULL default '0',
167 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
168 PRIMARY KEY (`configtagname`)
169) ENGINE=MyISAM DEFAULT CHARSET=utf8;
170
171
172
173CREATE TABLE IF NOT EXISTS `mgw_contacts` (
174 `id` int(11) NOT NULL default '0',
175 `title` varchar(30) default NULL,
176 `honorific_prefix` varchar(100) default NULL,
177 `name_prefix` varchar(100) default NULL,
178 `firstname` varchar(100) default NULL,
179 `name_add` varchar(100) default NULL,
180 `nickname` varchar(100) default NULL,
181 `lastname` varchar(100) default NULL,
182 `name_suffix` varchar(100) default NULL,
183 `priv_street` varchar(150) default NULL,
184 `priv_additional` varchar(100) default NULL,
185 `priv_zip` varchar(12) default NULL,
186 `priv_city` varchar(150) default NULL,
187 `priv_state` varchar(50) default NULL,
188 `priv_countrycode` varchar(5) default NULL,
189 `priv_region` varchar(100) default NULL,
190 `priv_telefon` varchar(50) default NULL,
191 `priv_fax` varchar(50) default NULL,
192 `email` varchar(150) default NULL,
193 `email2` varchar(150) default NULL,
194 `email3` char(2) default NULL,
195 `tel_company` varchar(50) default NULL,
196 `tel_mobile` varchar(50) default NULL,
197 `url` varchar(150) default NULL,
198 `function` varchar(100) default NULL,
199 `birthday` date default NULL,
200 `note` mediumtext,
201 `publicKey` mediumtext,
202 `keyType` varchar(150) default NULL,
203 `agentid` int(11) default NULL,
204 `companyid` int(11) default NULL,
205 `groupid` int(11) NOT NULL default '0',
206 `ispublic` int(11) default NULL,
207 `owner` int(11) default NULL,
208 `lu_user` int(11) default NULL,
209 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
210 PRIMARY KEY (`id`)
211) ENGINE=MyISAM DEFAULT CHARSET=utf8;
212
213
214
215CREATE TABLE IF NOT EXISTS `mgw_contacts_groups` (
216 `id` int(11) NOT NULL default '0',
217 `name_key` varchar(100) NOT NULL default '',
218 `description` varchar(255) NOT NULL default '',
219 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
220 `lu_user` int(11) NOT NULL default '0',
221 PRIMARY KEY (`id`),
222 UNIQUE KEY `name_key` (`name_key`)
223) ENGINE=MyISAM DEFAULT CHARSET=utf8;
224
225
226
227CREATE TABLE IF NOT EXISTS `mgw_contacts_history` (
228 `contactid` int(11) NOT NULL default '0',
229 `contact_company` int(11) NOT NULL default '0',
230 `contact_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
231 `contact_person` mediumtext,
232 `contact_todo` mediumtext,
233 `contact_comment` mediumtext,
234 `contact_userid` int(11) default NULL,
235 `lu_user` int(11) default NULL,
236 `lu_date` timestamp NOT NULL default '0000-00-00 00:00:00',
237 PRIMARY KEY (`contactid`,`contact_date`)
238) ENGINE=MyISAM DEFAULT CHARSET=utf8;
239
240
241
242CREATE TABLE IF NOT EXISTS `mgw_countries` (
243 `name` varchar(100) NOT NULL default '',
244 `code` varchar(5) NOT NULL default '',
245 `region` varchar(100) NOT NULL default '',
246 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
247 `lu_user` int(11) NOT NULL default '0',
248 PRIMARY KEY (`code`)
249) ENGINE=MyISAM DEFAULT CHARSET=utf8;
250
251
252
253CREATE TABLE IF NOT EXISTS `mgw_files` (
254 `id` int(11) NOT NULL default '0',
255 `type` char(1) NOT NULL default 'f',
256 `resides_in` int(11) default NULL,
257 `owner` int(11) NOT NULL default '0',
258 `name` varchar(50) default NULL,
259 `mime_type` varchar(20) default NULL,
260 `owner_rights` char(1) NOT NULL default '',
261 `all_rights` char(1) NOT NULL default '',
262 `created` datetime default NULL,
263 `modified` datetime default NULL,
264 `modified_by` int(11) default NULL,
265 `links_to` int(11) default NULL,
266 PRIMARY KEY (`id`),
267 UNIQUE KEY `resides_in` (`resides_in`,`name`)
268) ENGINE=MyISAM DEFAULT CHARSET=utf8;
269
270
271
272CREATE TABLE IF NOT EXISTS `mgw_files_grouprights` (
273 `id` int(11) NOT NULL default '0',
274 `for_file` int(11) default NULL,
275 `for_group` int(11) default NULL,
276 `rights` char(1) NOT NULL default '',
277 PRIMARY KEY (`id`),
278 UNIQUE KEY `for_file` (`for_file`,`for_group`)
279) ENGINE=MyISAM DEFAULT CHARSET=utf8;
280
281
282
283CREATE TABLE IF NOT EXISTS `mgw_forum_forums` (
284 `frm_id` int(11) NOT NULL default '0',
285 `grp_id` int(11) NOT NULL default '0',
286 `frm_name` varchar(30) NOT NULL default '',
287 `frm_info` mediumtext NOT NULL,
288 `frm_rightsLevel` int(11) NOT NULL default '0',
289 `lu_user` varchar(30) NOT NULL default '',
290 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
291 PRIMARY KEY (`frm_id`)
292) ENGINE=MyISAM DEFAULT CHARSET=utf8;
293
294
295
296CREATE TABLE IF NOT EXISTS `mgw_forum_groups` (
297 `grp_id` int(11) NOT NULL default '0',
298 `grp_name` char(30) NOT NULL default '',
299 `grp_desc` char(200) NOT NULL default '',
300 `lu_user` char(30) NOT NULL default '',
301 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
302 PRIMARY KEY (`grp_id`)
303) ENGINE=MyISAM DEFAULT CHARSET=utf8;
304
305
306
307CREATE TABLE IF NOT EXISTS `mgw_forum_moderators` (
308 `frm_id` int(11) NOT NULL default '0',
309 `u_id` int(11) NOT NULL default '0',
310 `lu_user` char(30) NOT NULL default '',
311 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
312 PRIMARY KEY (`frm_id`,`u_id`)
313) ENGINE=MyISAM DEFAULT CHARSET=utf8;
314
315
316
317CREATE TABLE IF NOT EXISTS `mgw_forum_threads` (
318 `frm_id` int(11) NOT NULL default '0',
319 `thread_id` int(11) NOT NULL default '0',
320 `reply_id` int(11) NOT NULL default '0',
321 `author_id` int(11) NOT NULL default '0',
322 `thread_title` varchar(150) NOT NULL default '',
323 `thread_text` mediumtext NOT NULL,
324 `thread_smiley` varchar(50) NOT NULL default '',
325 `thread_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
326 `thread_views` varchar(11) NOT NULL default '',
327 `thread_rightsLevel` int(11) NOT NULL default '0',
328 `thread_emailOnReply` int(11) NOT NULL default '0',
329 `thread_modifiedBy` varchar(30) NOT NULL default '',
330 `thread_modifiedDate` timestamp NOT NULL default '0000-00-00 00:00:00',
331 `lu_user` varchar(30) NOT NULL default '',
332 `lu_date` timestamp NOT NULL default '0000-00-00 00:00:00',
333 PRIMARY KEY (`frm_id`,`thread_id`,`reply_id`)
334) ENGINE=MyISAM DEFAULT CHARSET=utf8;
335
336
337
338CREATE TABLE IF NOT EXISTS `mgw_forum_users` (
339 `u_id` int(11) NOT NULL default '0',
340 `last_login_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
341 `lu_user` char(30) NOT NULL default '',
342 `lu_date` timestamp NOT NULL default '0000-00-00 00:00:00',
343 PRIMARY KEY (`u_id`)
344) ENGINE=MyISAM DEFAULT CHARSET=utf8;
345
346
347
348CREATE TABLE IF NOT EXISTS `mgw_groups` (
349 `id` int(11) NOT NULL default '0',
350 `name` varchar(50) NOT NULL default '',
351 `description` varchar(250) NOT NULL default '',
352 `lu_user` int(11) NOT NULL default '0',
353 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
354 PRIMARY KEY (`id`)
355) ENGINE=MyISAM DEFAULT CHARSET=utf8;
356
357
358
359CREATE TABLE IF NOT EXISTS `mgw_groups_members` (
360 `id` int(10) unsigned NOT NULL auto_increment COMMENT 'doctrine fuckshit',
361 `groupid` int(11) NOT NULL default '0',
362 `userid` int(11) NOT NULL default '0',
363 `lu_user` int(11) NOT NULL default '0',
364 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
365 PRIMARY KEY (`id`),
366 KEY `groupid` (`groupid`),
367 KEY `userid` (`userid`)
368) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=74 ;
369
370
371
372CREATE TABLE IF NOT EXISTS `mgw_headline` (
373 `id` int(11) NOT NULL default '0',
374 `owner` int(11) NOT NULL default '0',
375 `ids_channel` varchar(200) default NULL,
376 `type` int(11) NOT NULL default '0',
377 `lu_user` int(11) default NULL,
378 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
379 PRIMARY KEY (`id`)
380) ENGINE=MyISAM DEFAULT CHARSET=utf8;
381
382
383
384CREATE TABLE IF NOT EXISTS `mgw_headline_channels` (
385 `id` int(11) NOT NULL default '0',
386 `file` varchar(80) NOT NULL default '',
387 `name` varchar(200) NOT NULL default '',
388 `private` int(11) NOT NULL default '0',
389 `lu_user` int(11) NOT NULL default '0',
390 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
391 `description` mediumtext,
392 PRIMARY KEY (`id`)
393) ENGINE=MyISAM DEFAULT CHARSET=utf8;
394
395
396
397CREATE TABLE IF NOT EXISTS `mgw_headline_rss` (
398 `id` int(11) NOT NULL default '0',
399 `name` varchar(100) NOT NULL default '',
400 `rss_url` varchar(255) NOT NULL default '',
401 `site_url` varchar(255) NOT NULL default '',
402 `private` int(11) NOT NULL default '0',
403 `lu_user` int(11) NOT NULL default '0',
404 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
405 PRIMARY KEY (`id`)
406) ENGINE=MyISAM DEFAULT CHARSET=utf8;
407
408
409
410CREATE TABLE IF NOT EXISTS `mgw_jobhours_occupation` (
411 `id` int(10) unsigned NOT NULL auto_increment,
412 `name` varchar(255) NOT NULL default '',
413 `description` int(11) NOT NULL,
414 `owner` int(11) NOT NULL default '0',
415 `lu_user` int(11) NOT NULL default '0',
416 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
417 `type_id` smallint(5) unsigned default NULL,
418 PRIMARY KEY (`id`)
419) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=79 ;
420
421
422
423CREATE TABLE IF NOT EXISTS `mgw_jobhours_occupation2project` (
424 `id` int(11) NOT NULL auto_increment,
425 `occupation_id` smallint(6) NOT NULL default '57',
426 `project_id` smallint(6) NOT NULL,
427 PRIMARY KEY (`id`),
428 KEY `occupation_id` (`occupation_id`),
429 KEY `project_id` (`project_id`)
430) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1576 ;
431
432
433
434CREATE TABLE IF NOT EXISTS `mgw_jobhours_prices` (
435 `id` smallint(6) NOT NULL auto_increment COMMENT 'fucking ugly doctrine fuck shit',
436 `projectid` int(11) NOT NULL default '0',
437 `occupid` int(11) NOT NULL default '0',
438 `rate_eur` decimal(6,2) NOT NULL default '0.00',
439 `lu_user` int(11) default NULL,
440 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
441 PRIMARY KEY (`id`),
442 KEY `a` (`projectid`,`occupid`)
443) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=68 ;
444
445
446
447CREATE TABLE IF NOT EXISTS `mgw_jobhours_time` (
448 `id` int(11) NOT NULL auto_increment,
449 `userid` int(11) NOT NULL default '0',
450 `date` timestamp NOT NULL default '0000-00-00 00:00:00',
451 `time` decimal(8,2) NOT NULL default '0.00',
452 `projectid` int(11) NOT NULL default '0',
453 `status` enum('closed','final','draft','planed') NOT NULL default 'draft',
454 `billdep` int(11) NOT NULL default '0',
455 `description` varchar(2048) NOT NULL,
456 `occupid` int(11) NOT NULL default '0',
457 `lu_user` int(11) NOT NULL default '0',
458 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
459 PRIMARY KEY (`id`),
460 KEY `date` (`date`),
461 KEY `occupid` (`occupid`),
462 KEY `projectid` (`projectid`),
463 KEY `userid` (`userid`)
464) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=30897 ;
465
466
467
468CREATE TABLE IF NOT EXISTS `mgw_languages` (
469 `langcode` varchar(5) NOT NULL default '',
470 `description` varchar(100) NOT NULL default '',
471 `charset` varchar(30) NOT NULL default '',
472 `active` int(11) NOT NULL default '0',
473 `lu_user` int(11) NOT NULL default '0',
474 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
475 PRIMARY KEY (`langcode`)
476) ENGINE=MyISAM DEFAULT CHARSET=utf8;
477
478
479
480CREATE TABLE IF NOT EXISTS `mgw_modules` (
481 `modulename` varchar(30) NOT NULL default '',
482 `description` varchar(250) NOT NULL default '',
483 `folder` varchar(50) default NULL,
484 `displevel` int(11) default NULL,
485 `active` int(11) default NULL,
486 `lu_user` int(11) NOT NULL default '0',
487 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
488 PRIMARY KEY (`modulename`)
489) ENGINE=MyISAM DEFAULT CHARSET=utf8;
490
491
492
493CREATE TABLE IF NOT EXISTS `mgw_news` (
494 `id` int(11) NOT NULL default '0',
495 `headline` varchar(100) NOT NULL default '',
496 `teaser` varchar(255) NOT NULL default '',
497 `content` mediumtext NOT NULL,
498 `channelid` int(11) NOT NULL default '0',
499 `projectid` int(11) NOT NULL default '0',
500 `ispublic` int(11) NOT NULL default '0',
501 `owner` int(11) NOT NULL default '0',
502 `lu_user` int(11) NOT NULL default '0',
503 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
504 PRIMARY KEY (`id`)
505) ENGINE=MyISAM DEFAULT CHARSET=utf8;
506
507
508
509CREATE TABLE IF NOT EXISTS `mgw_news_channels` (
510 `id` int(11) NOT NULL default '0',
511 `channelname` varchar(200) NOT NULL default '',
512 `description` mediumtext NOT NULL,
513 `ispublic` int(11) NOT NULL default '0',
514 `owner` int(11) NOT NULL default '0',
515 `lu_user` int(11) NOT NULL default '0',
516 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
517 PRIMARY KEY (`id`),
518 UNIQUE KEY `channelname` (`channelname`)
519) ENGINE=MyISAM DEFAULT CHARSET=utf8;
520
521
522
523CREATE TABLE IF NOT EXISTS `mgw_notes` (
524 `id` int(11) NOT NULL default '0',
525 `title` varchar(255) NOT NULL default '',
526 `content` mediumtext NOT NULL,
527 `ispublic` int(11) NOT NULL default '0',
528 `owner` int(11) NOT NULL default '0',
529 `catid` int(11) NOT NULL default '0',
530 `lu_user` int(11) NOT NULL default '0',
531 `lu_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
532 PRIMARY KEY (`id`)
533) ENGINE=MyISAM DEFAULT CHARSET=utf8;
534
535
536
537CREATE TABLE IF NOT EXISTS `mgw_notes_categories` (
538 `id` int(11) NOT NULL default '0',
539 `name` varchar(100) NOT NULL default '',
540 `description` varchar(255) NOT NULL default '',
541 `lu_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
542 `lu_user` int(11) NOT NULL default '0',
543 PRIMARY KEY (`id`),
544 UNIQUE KEY `name` (`name`)
545) ENGINE=MyISAM DEFAULT CHARSET=utf8;
546
547
548
549CREATE TABLE IF NOT EXISTS `mgw_notes_comments` (
550 `id` int(11) NOT NULL default '0',
551 `note_id` int(11) NOT NULL default '0',
552 `comment` mediumtext NOT NULL,
553 `lu_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
554 `lu_user` int(11) NOT NULL default '0',
555 PRIMARY KEY (`id`)
556) ENGINE=MyISAM DEFAULT CHARSET=utf8;
557
558
559
560CREATE TABLE IF NOT EXISTS `mgw_outboard` (
561 `id_username` int(11) NOT NULL default '0',
562 `tsout` int(11) NOT NULL default '0',
563 `tsin` int(11) NOT NULL default '0',
564 `location` mediumtext,
565 `notes` mediumtext,
566 `autoin` char(1) NOT NULL default '',
567 `statusid` char(2) NOT NULL default '',
568 `lu_user` int(11) NOT NULL default '0',
569 `lu_update` int(11) NOT NULL default '0',
570 PRIMARY KEY (`id_username`)
571) ENGINE=MyISAM DEFAULT CHARSET=utf8;
572
573
574
575CREATE TABLE IF NOT EXISTS `mgw_partprog` (
576 `id` int(11) NOT NULL default '0',
577 `name` varchar(255) NOT NULL default '',
578 `company` varchar(100) NOT NULL default '',
579 `account` varchar(100) NOT NULL default '',
580 `pw` varchar(100) NOT NULL default '',
581 `url` varchar(200) NOT NULL default '',
582 `email` varchar(150) default NULL,
583 `description` mediumtext NOT NULL,
584 `ispublic` int(11) NOT NULL default '0',
585 `owner` int(11) NOT NULL default '0',
586 `lu_user` int(11) NOT NULL default '0',
587 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
588 PRIMARY KEY (`id`)
589) ENGINE=MyISAM DEFAULT CHARSET=utf8;
590
591
592
593CREATE TABLE IF NOT EXISTS `mgw_projects` (
594 `id` int(11) NOT NULL auto_increment,
595 `name` varchar(250) NOT NULL default '',
596 `description` mediumtext NOT NULL,
597 `budget` int(7) NOT NULL COMMENT 'budget for the project',
598 `parentid` int(11) NOT NULL default '0',
599 `companyid` int(11) NOT NULL default '0',
600 `contactid` int(11) NOT NULL default '0',
601 `projleaderid` int(11) NOT NULL default '0',
602 `keyaccountid` int(11) NOT NULL default '0',
603 `startdate` date default NULL,
604 `enddate` date default NULL,
605 `status` int(11) NOT NULL default '0',
606 `lu_user` int(11) NOT NULL default '0',
607 `owner` int(11) NOT NULL default '0',
608 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
609 `type` enum('project','hosting','certificate','other') NOT NULL default 'project',
610 `billinginfo` enum('yearlyInAdvanace','quarterlyInAdvance','monthlyPerCall','quarterlyPerCall','biannualPerCall','fixedAmountInArrear','twoRatesAndFinal','yearlyInArrear','allAfterCompletion','noInvoice','twoRates','specialArrangement') NOT NULL default 'twoRatesAndFinal',
611 `type_id` smallint(5) unsigned default NULL,
612 PRIMARY KEY (`id`)
613) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=764 ;
614
615
616
617CREATE TABLE IF NOT EXISTS `mgw_rights` (
618 `modulename` varchar(50) NOT NULL default '0',
619 `user_group_id` int(11) NOT NULL default '0',
620 `_read` int(11) NOT NULL default '0',
621 `_write` int(11) NOT NULL default '0',
622 `_delete` int(11) NOT NULL default '0',
623 `_delete_others` int(11) NOT NULL default '0',
624 `_modify` int(11) NOT NULL default '0',
625 `_modify_others` int(11) NOT NULL default '0',
626 `_module_level` int(11) NOT NULL default '0',
627 `lu_user` int(11) NOT NULL default '0',
628 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
629 PRIMARY KEY (`modulename`,`user_group_id`)
630) ENGINE=MyISAM DEFAULT CHARSET=utf8;
631
632
633
634CREATE TABLE IF NOT EXISTS `mgw_todo` (
635 `id` int(11) NOT NULL default '0',
636 `userid` int(11) NOT NULL default '0',
637 `header` varchar(200) NOT NULL default '',
638 `description` mediumtext NOT NULL,
639 `enddate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
640 `endtype` char(1) default '1',
641 `priority` char(1) default '3',
642 `status` varchar(4) NOT NULL default '',
643 `fromuserid` int(11) default NULL,
644 `owner` int(11) default NULL,
645 `lu_user` int(11) NOT NULL default '0',
646 `lu_date` timestamp NOT NULL default '0000-00-00 00:00:00',
647 `projectid` int(11) NOT NULL default '0',
648 PRIMARY KEY (`id`)
649) ENGINE=MyISAM DEFAULT CHARSET=utf8;
650
651
652
653CREATE TABLE IF NOT EXISTS `mgw_tts` (
654 `id` int(11) NOT NULL default '0',
655 `id_category` int(11) NOT NULL default '0',
656 `id_project` int(11) NOT NULL default '0',
657 `id_priority` int(11) NOT NULL default '0',
658 `id_owner` int(11) default '-1',
659 `id_assigned` int(11) default NULL,
660 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
661 `owner_name` varchar(50) default NULL,
662 `owner_email` varchar(50) default NULL,
663 `short_desc` mediumtext NOT NULL,
664 `long_desc` mediumtext,
665 PRIMARY KEY (`id`)
666) ENGINE=MyISAM DEFAULT CHARSET=utf8;
667
668
669
670CREATE TABLE IF NOT EXISTS `mgw_tts_history` (
671 `id` int(11) NOT NULL default '0',
672 `id_tts` int(11) NOT NULL default '0',
673 `id_owner` int(11) NOT NULL default '0',
674 `start_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
675 `end_date` timestamp NOT NULL default '0000-00-00 00:00:00',
676 `lu_date` timestamp NOT NULL default '0000-00-00 00:00:00',
677 `info` mediumtext NOT NULL,
678 `state` int(11) NOT NULL default '0',
679 PRIMARY KEY (`id`)
680) ENGINE=MyISAM DEFAULT CHARSET=utf8;
681
682
683
684CREATE TABLE IF NOT EXISTS `mgw_users` (
685 `id` int(11) NOT NULL default '0',
686 `username` varchar(100) NOT NULL default '',
687 `lastname` varchar(100) default NULL,
688 `firstname` varchar(100) default NULL,
689 `email` varchar(100) default NULL,
690 `mobil` varchar(30) default NULL,
691 `skintheme` varchar(100) default NULL,
692 `pw` varchar(255) default NULL,
693 `lang` varchar(5) default NULL,
694 `level` int(11) default NULL,
695 `lu_user` int(11) default NULL,
696 `lu_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
697 `lastin` timestamp NOT NULL default '0000-00-00 00:00:00',
698 `is_ldap` int(11) NOT NULL default '1',
699 `location_id` tinyint(3) unsigned NOT NULL default '1' COMMENT 'zebra_location.location_id',
700 PRIMARY KEY (`id`),
701 UNIQUE KEY `username` (`username`),
702 KEY `location_id` (`location_id`)
703) ENGINE=MyISAM DEFAULT CHARSET=utf8;
704
705
706
707CREATE TABLE IF NOT EXISTS `mgw_webmail_accounts` (
708 `mailboxname` varchar(255) default NULL,
709 `servertype` varchar(20) default NULL,
710 `pophost` varchar(255) default NULL,
711 `smtphost` varchar(255) default NULL,
712 `smtpauth` varchar(10) default NULL,
713 `smtpusepopuser` varchar(10) default NULL,
714 `usemailfunction` varchar(10) default NULL,
715 `smtpusername` varchar(255) default NULL,
716 `smtppassword` varchar(255) default NULL,
717 `username` varchar(255) default NULL,
718 `password` varchar(255) default NULL,
719 `leavemessages` varchar(10) default NULL,
720 `popbeforesmtp` varchar(10) default NULL,
721 `enable_ssl` varchar(10) default NULL,
722 `enable_ssl_smtp` varchar(10) default NULL,
723 `activated` varchar(10) default NULL,
724 `fullname` varchar(255) default NULL,
725 `replyto` varchar(255) default NULL,
726 `user_id` int(11) NOT NULL default '0',
727 `leaveoldmails` mediumtext,
728 `id` int(11) NOT NULL default '0',
729 PRIMARY KEY (`id`)
730) ENGINE=MyISAM DEFAULT CHARSET=utf8;
731
732
733
734CREATE TABLE IF NOT EXISTS `mgw_webmail_mailboxes` (
735 `mbox_id` int(11) NOT NULL default '0',
736 `user_id` int(11) NOT NULL default '0',
737 `mboxname` varchar(255) default NULL,
738 `mboxparent` int(11) NOT NULL default '0',
739 `mbox_type` int(11) NOT NULL default '3',
740 `mbox_protect` int(11) NOT NULL default '0',
741 `isintrash` char(1) NOT NULL default '',
742 `isdraft` char(1) NOT NULL default '',
743 `readflag` char(1) NOT NULL default '',
744 `readexception` mediumtext,
745 `writeflag` char(1) NOT NULL default '',
746 `writeexception` mediumtext,
747 `delflag` char(1) NOT NULL default '',
748 `delexception` mediumtext,
749 `createsubdir` char(1) NOT NULL default '',
750 `createsubexception` mediumtext,
751 `owner` varchar(100) NOT NULL default '',
752 `addowner` mediumtext,
753 `storeallflag` char(1) NOT NULL default '',
754 `storeallexception` mediumtext,
755 PRIMARY KEY (`mbox_id`)
756) ENGINE=MyISAM DEFAULT CHARSET=utf8;
757
758
759
760CREATE TABLE IF NOT EXISTS `mgw_webmail_messages` (
761 `message_id` int(11) NOT NULL default '0',
762 `uid` varchar(255) NOT NULL default '',
763 `type` varchar(10) NOT NULL default '',
764 `encoding` int(11) default NULL,
765 `companies_id` int(11) default NULL,
766 `contact_id` int(11) default NULL,
767 `forumthreads_id` int(11) default NULL,
768 `groups_id` int(11) default NULL,
769 `projects_id` int(11) default NULL,
770 `mbox_id` int(11) NOT NULL default '0',
771 `user_id` int(11) NOT NULL default '0',
772 `header_from` mediumtext,
773 `header_to` mediumtext,
774 `header_cc` mediumtext,
775 `header_bcc` mediumtext,
776 `header_replyto` varchar(255) NOT NULL default '',
777 `header_date` varchar(100) NOT NULL default '',
778 `header_subject` varchar(255) NOT NULL default '',
779 `header_size` varchar(40) NOT NULL default '',
780 `header_inreplyto` varchar(255) NOT NULL default '',
781 `msg_body` varchar(255) NOT NULL default '',
782 `msg_cache` varchar(255) NOT NULL default '',
783 `account_name` varchar(255) NOT NULL default '',
784 `recentbit` char(1) NOT NULL default '',
785 `readbit` char(1) NOT NULL default '',
786 `replybit` char(1) NOT NULL default '',
787 `draftbit` char(1) NOT NULL default '',
788 `flaggedbit` char(1) NOT NULL default '',
789 `forwardbit` char(1) NOT NULL default '',
790 `attachbit` char(1) NOT NULL default '',
791 `replied` mediumtext,
792 `replied2` int(11) default NULL,
793 `forwarded` mediumtext,
794 PRIMARY KEY (`message_id`)
795) ENGINE=MyISAM DEFAULT CHARSET=utf8;
796
797
798
799CREATE TABLE IF NOT EXISTS `mgw_webmail_mime` (
800 `mime_type` varchar(100) NOT NULL default '',
801 `mime_ext` varchar(100) NOT NULL default '',
802 `mime_protect` varchar(10) NOT NULL default '',
803 `inline` varchar(10) NOT NULL default '',
804 `highlight` varchar(10) NOT NULL default '',
805 `danger` varchar(10) NOT NULL default ''
806) ENGINE=MyISAM DEFAULT CHARSET=utf8;
807
808
809
810CREATE TABLE IF NOT EXISTS `mgw_webmail_sidebar` (
811 `id` int(11) NOT NULL default '0',
812 `user_id` int(11) NOT NULL default '0',
813 `mbox_id` int(11) NOT NULL default '0'
814) ENGINE=MyISAM DEFAULT CHARSET=utf8;
815
816
817
818CREATE TABLE IF NOT EXISTS `mgw__seq_accounts` (
819 `id` int(11) NOT NULL default '0'
820) ENGINE=MyISAM DEFAULT CHARSET=utf8;
821
822
823
824CREATE TABLE IF NOT EXISTS `mgw__seq_billing` (
825 `id` int(11) NOT NULL default '0'
826) ENGINE=MyISAM DEFAULT CHARSET=utf8;
827
828
829
830CREATE TABLE IF NOT EXISTS `mgw__seq_calendar` (
831 `id` int(11) NOT NULL default '0'
832) ENGINE=MyISAM DEFAULT CHARSET=utf8;
833
834
835
836CREATE TABLE IF NOT EXISTS `mgw__seq_calendar_tmp` (
837 `id` int(11) NOT NULL default '0'
838) ENGINE=MyISAM DEFAULT CHARSET=utf8;
839
840
841
842CREATE TABLE IF NOT EXISTS `mgw__seq_companies` (
843 `id` int(11) NOT NULL default '0'
844) ENGINE=MyISAM DEFAULT CHARSET=utf8;
845
846
847
848CREATE TABLE IF NOT EXISTS `mgw__seq_contacts` (
849 `id` int(11) NOT NULL default '0'
850) ENGINE=MyISAM DEFAULT CHARSET=utf8;
851
852
853
854CREATE TABLE IF NOT EXISTS `mgw__seq_contacts_groups` (
855 `id` int(11) NOT NULL default '0'
856) ENGINE=MyISAM DEFAULT CHARSET=utf8;
857
858
859
860CREATE TABLE IF NOT EXISTS `mgw__seq_contacts_history` (
861 `id` int(11) NOT NULL default '0'
862) ENGINE=MyISAM DEFAULT CHARSET=utf8;
863
864
865
866CREATE TABLE IF NOT EXISTS `mgw__seq_files` (
867 `id` int(11) NOT NULL default '0'
868) ENGINE=MyISAM DEFAULT CHARSET=utf8;
869
870
871
872CREATE TABLE IF NOT EXISTS `mgw__seq_files_grouprights` (
873 `id` int(11) NOT NULL default '0'
874) ENGINE=MyISAM DEFAULT CHARSET=utf8;
875
876
877
878CREATE TABLE IF NOT EXISTS `mgw__seq_groups` (
879 `id` int(11) NOT NULL default '0'
880) ENGINE=MyISAM DEFAULT CHARSET=utf8;
881
882
883
884CREATE TABLE IF NOT EXISTS `mgw__seq_headline` (
885 `id` int(11) NOT NULL default '0'
886) ENGINE=MyISAM DEFAULT CHARSET=utf8;
887
888
889
890CREATE TABLE IF NOT EXISTS `mgw__seq_headline_rss` (
891 `id` int(11) NOT NULL default '0'
892) ENGINE=MyISAM DEFAULT CHARSET=utf8;
893
894
895
896CREATE TABLE IF NOT EXISTS `mgw__seq_jobhours_occupation` (
897 `id` int(11) NOT NULL default '0'
898) ENGINE=MyISAM DEFAULT CHARSET=utf8;
899
900
901
902CREATE TABLE IF NOT EXISTS `mgw__seq_jobhours_time` (
903 `id` int(11) NOT NULL default '0'
904) ENGINE=MyISAM DEFAULT CHARSET=utf8;
905
906
907
908CREATE TABLE IF NOT EXISTS `mgw__seq_mailboxes` (
909 `id` int(11) NOT NULL default '0'
910) ENGINE=MyISAM DEFAULT CHARSET=utf8;
911
912
913
914CREATE TABLE IF NOT EXISTS `mgw__seq_messages` (
915 `id` int(11) NOT NULL default '0'
916) ENGINE=MyISAM DEFAULT CHARSET=utf8;
917
918
919
920CREATE TABLE IF NOT EXISTS `mgw__seq_news` (
921 `id` int(11) NOT NULL default '0'
922) ENGINE=MyISAM DEFAULT CHARSET=utf8;
923
924
925
926CREATE TABLE IF NOT EXISTS `mgw__seq_notes` (
927 `id` int(11) NOT NULL default '0'
928) ENGINE=MyISAM DEFAULT CHARSET=utf8;
929
930
931
932CREATE TABLE IF NOT EXISTS `mgw__seq_notes_categories` (
933 `id` int(11) NOT NULL default '0'
934) ENGINE=MyISAM DEFAULT CHARSET=utf8;
935
936
937
938CREATE TABLE IF NOT EXISTS `mgw__seq_partprog` (
939 `id` int(11) NOT NULL default '0'
940) ENGINE=MyISAM DEFAULT CHARSET=utf8;
941
942
943
944CREATE TABLE IF NOT EXISTS `mgw__seq_projects` (
945 `id` int(11) NOT NULL default '0'
946) ENGINE=MyISAM DEFAULT CHARSET=utf8;
947
948
949
950CREATE TABLE IF NOT EXISTS `mgw__seq_todo` (
951 `id` int(11) NOT NULL default '0'
952) ENGINE=MyISAM DEFAULT CHARSET=utf8;
953
954
955
956CREATE TABLE IF NOT EXISTS `mgw__seq_tts` (
957 `id` int(11) NOT NULL default '0'
958) ENGINE=MyISAM DEFAULT CHARSET=utf8;
959
960
961
962CREATE TABLE IF NOT EXISTS `mgw__seq_tts_history` (
963 `id` int(11) NOT NULL default '0'
964) ENGINE=MyISAM DEFAULT CHARSET=utf8;
965
966
967
968CREATE TABLE IF NOT EXISTS `mgw__seq_users` (
969 `id` int(11) NOT NULL default '0'
970) ENGINE=MyISAM DEFAULT CHARSET=utf8;
971
972
973
974CREATE TABLE IF NOT EXISTS `zebra_activity_type` (
975 `type_id` smallint(5) unsigned NOT NULL auto_increment,
976 `type_key_name` varchar(128) collate utf8_unicode_ci NOT NULL,
977 PRIMARY KEY (`type_id`)
978) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;
979
980
981
982CREATE TABLE IF NOT EXISTS `zebra_employee` (
983 `employee_id` int(10) unsigned NOT NULL auto_increment,
984 `employee_username` varchar(127) collate utf8_unicode_ci NOT NULL default '',
985 `employee_firstname` varchar(127) collate utf8_unicode_ci NOT NULL default '',
986 `employee_lastname` varchar(127) collate utf8_unicode_ci NOT NULL default '',
987 `employee_ldap_username` varchar(127) collate utf8_unicode_ci NOT NULL default '',
988 `employee_pw` varchar(127) collate utf8_unicode_ci NOT NULL default '',
989 `employee_isarchive` tinyint(1) NOT NULL default '0',
990 `level_id` smallint(5) unsigned NOT NULL default '0',
991 `lang_id` tinyint(3) unsigned NOT NULL default '0',
992 `location_id` tinyint(3) unsigned NOT NULL default '0',
993 `employee_token` varchar(40) collate utf8_unicode_ci NOT NULL default '',
994 PRIMARY KEY (`employee_id`),
995 KEY `employee_isarchive` (`employee_isarchive`),
996 KEY `level_id` (`level_id`),
997 KEY `lang_id` (`lang_id`),
998 KEY `location_id` (`location_id`)
999) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=74 ;
1000
1001
1002
1003CREATE TABLE IF NOT EXISTS `zebra_employee_education` (
1004 `education_id` int(10) unsigned NOT NULL auto_increment,
1005 `education_from` datetime NOT NULL default '0000-00-00 00:00:00',
1006 `education_to` datetime NOT NULL default '0000-00-00 00:00:00',
1007 `education_budget_money` decimal(65,2) default NULL,
1008 `education_budget_time` decimal(65,2) default NULL,
1009 `education_name` varchar(255) collate utf8_unicode_ci NOT NULL,
1010 `education_description` tinytext collate utf8_unicode_ci,
1011 `education_factor_multiplier` double(2,1) default NULL,
1012 PRIMARY KEY (`education_id`)
1013) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
1014
1015
1016
1017CREATE TABLE IF NOT EXISTS `zebra_employee_level` (
1018 `level_id` smallint(5) unsigned NOT NULL auto_increment,
1019 `level_name` varchar(63) collate utf8_unicode_ci NOT NULL default '',
1020 PRIMARY KEY (`level_id`)
1021) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;
1022
1023
1024
1025CREATE TABLE IF NOT EXISTS `zebra_employee_overtime_legacy` (
1026 `legacy_id` int(10) unsigned NOT NULL auto_increment,
1027 `legacy_amount` decimal(20,2) NOT NULL,
1028 `employee_id` int(10) unsigned NOT NULL,
1029 PRIMARY KEY (`legacy_id`),
1030 KEY `employee_id` (`employee_id`)
1031) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=69 ;
1032
1033
1034
1035CREATE TABLE IF NOT EXISTS `zebra_employee_overtime_paid` (
1036 `paid_id` int(10) unsigned NOT NULL auto_increment,
1037 `paid_amount` decimal(20,2) NOT NULL,
1038 `paid_date` datetime NOT NULL,
1039 `employee_id` int(10) unsigned NOT NULL,
1040 PRIMARY KEY (`paid_id`),
1041 KEY `employee_id` (`employee_id`)
1042) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;
1043
1044
1045
1046CREATE TABLE IF NOT EXISTS `zebra_employee_period` (
1047 `period_id` int(10) unsigned NOT NULL auto_increment,
1048 `period_from` datetime NOT NULL default '0000-00-00 00:00:00',
1049 `period_to` datetime NOT NULL default '0000-00-00 00:00:00',
1050 `period_quota` float(3,2) unsigned NOT NULL default '1.00',
1051 `level_id` smallint(5) unsigned NOT NULL default '0',
1052 `employee_id` int(10) unsigned NOT NULL default '0',
1053 `location_id` tinyint(3) unsigned NOT NULL default '0',
1054 PRIMARY KEY (`period_id`),
1055 KEY `period_from` (`period_from`),
1056 KEY `period_to` (`period_to`),
1057 KEY `level_id` (`level_id`),
1058 KEY `employee_id` (`employee_id`),
1059 KEY `location_id` (`location_id`)
1060) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=137 ;
1061
1062
1063
1064CREATE TABLE IF NOT EXISTS `zebra_employee_vacation` (
1065 `vacation_id` int(10) unsigned NOT NULL auto_increment,
1066 `vacation_amount` decimal(20,2) NOT NULL,
1067 `employee_id` int(10) unsigned NOT NULL,
1068 PRIMARY KEY (`vacation_id`),
1069 KEY `employee_id` (`employee_id`)
1070) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=19 ;
1071
1072
1073
1074CREATE TABLE IF NOT EXISTS `zebra_employee_vacation_legacy` (
1075 `year` int(4) unsigned NOT NULL,
1076 `employee_id` int(10) unsigned NOT NULL,
1077 `legacy_amount` decimal(20,2) NOT NULL,
1078 PRIMARY KEY (`year`,`employee_id`),
1079 KEY `zebra_employee_vacation_legacy_ibfk_1` (`employee_id`)
1080) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1081
1082
1083
1084CREATE TABLE IF NOT EXISTS `zebra_holiday` (
1085 `holiday_id` smallint(5) unsigned NOT NULL auto_increment,
1086 `holiday_name` varchar(127) collate utf8_unicode_ci NOT NULL default '',
1087 `holiday_start` datetime NOT NULL default '0000-00-00 00:00:00',
1088 `holiday_end` datetime NOT NULL default '0000-00-00 00:00:00',
1089 `holiday_freetime` tinyint(4) NOT NULL default '0',
1090 PRIMARY KEY (`holiday_id`),
1091 KEY `holiday_start` (`holiday_start`),
1092 KEY `holiday_end` (`holiday_end`)
1093) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=919 ;
1094
1095
1096
1097CREATE TABLE IF NOT EXISTS `zebra_holiday2location` (
1098 `id` smallint(5) unsigned NOT NULL auto_increment,
1099 `holiday_id` smallint(5) unsigned NOT NULL,
1100 `location_id` tinyint(3) unsigned NOT NULL,
1101 PRIMARY KEY (`id`),
1102 KEY `holiday_id` (`holiday_id`),
1103 KEY `location_id` (`location_id`)
1104) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=65 ;
1105
1106
1107
1108CREATE TABLE IF NOT EXISTS `zebra_lang` (
1109 `lang_id` tinyint(3) unsigned NOT NULL auto_increment,
1110 `lang_shortname` varchar(2) collate utf8_unicode_ci NOT NULL default '',
1111 `lang_nativename` varchar(31) collate utf8_unicode_ci NOT NULL default '',
1112 PRIMARY KEY (`lang_id`),
1113 KEY `lang_shortname` (`lang_shortname`)
1114) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;
1115
1116
1117
1118CREATE TABLE IF NOT EXISTS `zebra_location` (
1119 `location_id` tinyint(3) unsigned NOT NULL auto_increment,
1120 `location_shortname` varchar(2) collate utf8_unicode_ci NOT NULL,
1121 PRIMARY KEY (`location_id`)
1122) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;
1123
1124
1125
1126CREATE TABLE IF NOT EXISTS `zebra_project_type` (
1127 `type_id` smallint(5) unsigned NOT NULL auto_increment,
1128 `type_key_name` varchar(128) collate utf8_unicode_ci NOT NULL,
1129 PRIMARY KEY (`type_id`)
1130) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;
1131
1132
1133
1134CREATE TABLE IF NOT EXISTS `zebra_timesheet_education` (
1135 `education_id` int(10) unsigned NOT NULL auto_increment,
1136 `education_amount_time` decimal(65,2) default NULL,
1137 `education_amount_money` decimal(65,2) default NULL,
1138 `education_date` datetime NOT NULL default '0000-00-00 00:00:00',
1139 `education_description` tinytext collate utf8_unicode_ci,
1140 `education_factor_multiplier` double(2,1) default NULL,
1141 `employee_id` int(10) unsigned NOT NULL,
1142 `activity_id` int(10) unsigned NOT NULL,
1143 `timesheet_id` int(10) unsigned NOT NULL,
1144 PRIMARY KEY (`education_id`)
1145) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
1146
1147
1148
1149CREATE TABLE IF NOT EXISTS `zebra_timesheet_favorite` (
1150 `favorite_id` int(10) unsigned NOT NULL auto_increment,
1151 `favorite_name` varchar(32) collate utf8_unicode_ci NOT NULL,
1152 `favorite_time` int(10) unsigned default NULL,
1153 `favorite_description` varchar(2048) collate utf8_unicode_ci default NULL,
1154 `employee_id` int(10) unsigned NOT NULL,
1155 `project_id` int(10) unsigned default NULL,
1156 `activity_id` int(10) unsigned default NULL,
1157 PRIMARY KEY (`favorite_id`)
1158) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;
1159
1160
1161
1162CREATE TABLE IF NOT EXISTS `zebra_timesheet_finish` (
1163 `finish_id` int(10) unsigned NOT NULL auto_increment,
1164 `finish_date` date NOT NULL,
1165 PRIMARY KEY (`finish_id`)
1166) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;
1167
1168
1169ALTER TABLE `zebra_employee`
1170 ADD CONSTRAINT `zebra_employee_ibfk_1` FOREIGN KEY (`level_id`) REFERENCES `zebra_employee_level` (`level_id`) ON UPDATE CASCADE,
1171 ADD CONSTRAINT `zebra_employee_ibfk_2` FOREIGN KEY (`lang_id`) REFERENCES `zebra_lang` (`lang_id`) ON UPDATE CASCADE,
1172 ADD CONSTRAINT `zebra_employee_ibfk_3` FOREIGN KEY (`location_id`) REFERENCES `zebra_location` (`location_id`) ON UPDATE CASCADE;
1173
1174ALTER TABLE `zebra_employee_overtime_legacy`
1175 ADD CONSTRAINT `zebra_employee_overtime_legacy_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `zebra_employee` (`employee_id`) ON DELETE CASCADE ON UPDATE CASCADE;
1176
1177ALTER TABLE `zebra_employee_overtime_paid`
1178 ADD CONSTRAINT `zebra_employee_overtime_paid_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `zebra_employee` (`employee_id`) ON DELETE CASCADE ON UPDATE CASCADE;
1179
1180ALTER TABLE `zebra_employee_period`
1181 ADD CONSTRAINT `zebra_employee_period_ibfk_1` FOREIGN KEY (`level_id`) REFERENCES `zebra_employee_level` (`level_id`) ON UPDATE CASCADE,
1182 ADD CONSTRAINT `zebra_employee_period_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `zebra_employee` (`employee_id`) ON UPDATE CASCADE,
1183 ADD CONSTRAINT `zebra_employee_period_ibfk_3` FOREIGN KEY (`location_id`) REFERENCES `zebra_location` (`location_id`) ON UPDATE CASCADE;
1184
1185ALTER TABLE `zebra_employee_vacation`
1186 ADD CONSTRAINT `zebra_employee_vacation_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `zebra_employee` (`employee_id`) ON DELETE CASCADE ON UPDATE CASCADE;
1187
1188ALTER TABLE `zebra_employee_vacation_legacy`
1189 ADD CONSTRAINT `zebra_employee_vacation_legacy_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `zebra_employee` (`employee_id`) ON DELETE CASCADE ON UPDATE CASCADE;
1190
1191ALTER TABLE `zebra_holiday2location`
1192 ADD CONSTRAINT `zebra_holiday2location_ibfk_1` FOREIGN KEY (`holiday_id`) REFERENCES `zebra_holiday` (`holiday_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1193 ADD CONSTRAINT `zebra_holiday2location_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `zebra_location` (`location_id`) ON DELETE CASCADE ON UPDATE CASCADE;