· 9 years ago · Dec 13, 2016, 11:26 PM
1ALTER TABLE `tm_emp_ts_notes`
2ADD `sun_reject_note` varchar(200) NULL after `sun_note`,
3ADD `mon_reject_note` varchar(200) NULL after `mon_note`,
4ADD `tue_reject_note` varchar(200) NULL after `tue_note`,
5ADD `wed_reject_note` varchar(200) NULL after `wed_note`,
6ADD `thu_reject_note` varchar(200) NULL after `thu_note`,
7ADD `fri_reject_note` varchar(200) NULL after `fri_note`,
8ADD `sat_reject_note` varchar(200) NULL after `sat_note`;
9
10ALTER TABLE `tm_projects`
11ADD `currency_id` int(10) unsigned NOT NULL after `client_id`,
12ADD `project_type` enum('billable','non_billable','revenue') NOT NULL after `currency_id`,
13ADD `lead_approve_ts` tinyint(1) NOT NULL after `project_type`,
14ADD `estimated_hrs` MEDIUMINT(5) DEFAULT NULL after `lead_approve_ts`,
15ADD `start_date` date DEFAULT NULL after `estimated_hrs`,
16ADD `end_date` date DEFAULT NULL after `start_date`,
17ADD `initiated_date` timestamp NULL DEFAULT NULL after `end_date`,
18ADD `hold_date` timestamp NULL DEFAULT NULL after `initiated_date`,
19ADD `completed_date` timestamp NULL DEFAULT NULL after `hold_date`;
20
21update tm_projects set currency_id = (select id from main_currency limit 0,1);
22
23ALTER TABLE tm_projects
24ADD CONSTRAINT `FK_tm_projects_currency`
25FOREIGN KEY (`currency_id`) REFERENCES `main_currency` (`id`);
26
27ALTER TABLE `tm_project_employees`
28ADD `cost_rate` decimal(8,2) unsigned DEFAULT NULL after `emp_id`,
29ADD `billable_rate` decimal(7,2) unsigned DEFAULT NULL after `cost_rate`;
30
31ALTER TABLE `tm_project_tasks`
32ADD `estimated_hrs` MEDIUMINT(5) unsigned DEFAULT NULL after `task_id`,
33ADD `is_billable` tinyint(1) DEFAULT '0' after `estimated_hrs`,
34ADD `billable_rate` decimal(25,2) DEFAULT NULL after `is_billable`;
35
36ALTER TABLE `tm_ts_status`
37ADD `sun_reject_note` varchar(200) NULL after `sun_status_date`,
38ADD `mon_reject_note` varchar(200) NULL after `mon_status_date`,
39ADD `tue_reject_note` varchar(200) NULL after `tue_status_date`,
40ADD `wed_reject_note` varchar(200) NULL after `wed_status_date`,
41ADD `thu_reject_note` varchar(200) NULL after `thu_status_date`,
42ADD `fri_reject_note` varchar(200) NULL after `fri_status_date`,
43ADD `sat_reject_note` varchar(200) NULL after `sat_status_date`;
44
45ALTER TABLE `tm_ts_status`
46CHANGE `sun_project_status` `sun_project_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL,
47CHANGE `sun_status` `sun_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL,
48CHANGE `mon_project_status` `mon_project_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL,
49CHANGE `mon_status` `mon_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL,
50CHANGE `tue_project_status` `tue_project_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL,
51CHANGE `tue_status` `tue_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL,
52CHANGE `wed_project_status` `wed_project_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL,
53CHANGE `wed_status` `wed_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL,
54CHANGE `thu_project_status` `thu_project_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL,
55CHANGE `thu_status` `thu_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL,
56CHANGE `fri_project_status` `fri_project_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL,
57CHANGE `fri_status` `fri_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL,
58CHANGE `sat_project_status` `sat_project_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL,
59CHANGE `sat_status` `sat_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL,
60CHANGE `week_status` `week_status` enum('saved','submitted','approved','enabled','rejected','blocked','no_entry') character set utf8 collate utf8_general_ci NULL;
61
62/*Table structure for table `tm_configuration` */
63
64DROP TABLE IF EXISTS `tm_configuration`;
65
66CREATE TABLE `tm_configuration` (
67 `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
68 `ts_weekly_reminder_day` enum('sun','mon','tue','wed','thu','fri','sat') NOT NULL,
69 `ts_block_dates_range` varchar(100) NOT NULL,
70 `created_by` int(10) unsigned NOT NULL,
71 `modified_by` int(10) unsigned DEFAULT NULL,
72 `is_active` tinyint(1) unsigned NOT NULL,
73 `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
74 `modified` timestamp NULL DEFAULT '0000-00-00 00:00:00',
75 PRIMARY KEY (`id`)
76) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
77
78/*Table structure for table `tm_cronjob_status` */
79
80DROP TABLE IF EXISTS `tm_cronjob_status`;
81
82CREATE TABLE `tm_cronjob_status` (
83 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
84 `cronjob_status` enum('running','stopped') DEFAULT NULL,
85 `start_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
86 `end_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
87 PRIMARY KEY (`id`)
88) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
89
90/*Table structure for table `tm_mailing_list` */
91
92DROP TABLE IF EXISTS `tm_mailing_list`;
93
94CREATE TABLE `tm_mailing_list` (
95 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
96 `emp_id` int(10) unsigned DEFAULT NULL,
97 `emp_full_name` varchar(150) DEFAULT NULL,
98 `email` varchar(200) DEFAULT NULL,
99 `mail_type` enum('submit_pending','reminder','block') DEFAULT NULL,
100 `ts_dates` text,
101 `ts_start_date` date DEFAULT NULL,
102 `ts_end_date` date DEFAULT NULL,
103 `mail_content` text,
104 `is_mail_sent` tinyint(1) unsigned DEFAULT NULL,
105 `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
106 PRIMARY KEY (`id`),
107 KEY `FK_tm_mailing_list_employee` (`emp_id`),
108 CONSTRAINT `FK_tm_mailing_list_employee` FOREIGN KEY (`emp_id`) REFERENCES `main_users` (`id`)
109) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
110
111/*Table structure for table `tm_process_updates` */
112
113DROP TABLE IF EXISTS `tm_process_updates`;
114
115CREATE TABLE `tm_process_updates` (
116 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
117 `emp_id` int(10) unsigned NOT NULL,
118 `ts_dates` text NOT NULL,
119 `action_type` enum('edited','rejected','approved','enabled') NOT NULL,
120 `note` varchar(200) DEFAULT NULL,
121 `alert` enum('open','closed') DEFAULT NULL,
122 `action_by` int(10) unsigned NOT NULL,
123 `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
124 `modified` timestamp NULL DEFAULT '0000-00-00 00:00:00',
125 PRIMARY KEY (`id`),
126 KEY `FK_tm_process_updates_employee` (`emp_id`),
127 CONSTRAINT `FK_tm_process_updates_employee` FOREIGN KEY (`emp_id`) REFERENCES `main_users` (`id`)
128) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
129
130ALTER TABLE `main_menu` add column `modulename` varchar(50) NULL after `isactive`;
131
132ALTER TABLE `main_sd_configurations` ADD COLUMN `request_for` TINYINT(1) DEFAULT 1 NULL COMMENT '1=service request,2=asset request' AFTER `service_desk_flag`;
133
134ALTER TABLE `main_sd_requests` ADD COLUMN `request_for` TINYINT(1) DEFAULT 1 NULL COMMENT '1=service request, 2= asset request' AFTER `id`, CHANGE `service_desk_id` `service_desk_id` BIGINT(20) UNSIGNED NULL COMMENT 'if request_for is equal to 2 then dump id from asset table', CHANGE `service_request_id` `service_request_id` BIGINT(20) UNSIGNED NULL COMMENT 'If request_for is equal to 2 then dump category from asset table';
135
136ALTER TABLE `main_sd_requests_summary` ADD COLUMN `request_for` TINYINT(1) DEFAULT 1 NULL COMMENT '1=service request,2=asset request' AFTER `id`,CHANGE `service_desk_id` `service_desk_id` BIGINT(20) UNSIGNED NULL COMMENT 'If request_for equal to 2 then dump asset id from asset table', CHANGE `service_desk_name` `service_desk_name` VARCHAR(250) CHARSET latin1 COLLATE latin1_swedish_ci NULL COMMENT 'If request_for equal to 2 then dump asset name from asset table', CHANGE `service_request_name` `service_request_name` VARCHAR(250) CHARSET latin1 COLLATE latin1_swedish_ci NULL COMMENT 'If request_for equal to 2 then dump asset category from asset table', CHANGE `service_request_id` `service_request_id` BIGINT(20) UNSIGNED NULL COMMENT 'If request_for equal to 2 then dump asset name from asset_categories table';
137
138alter table `main_empsalarydetails` change `salary` `salary` varchar(100) NULL;
139
140update main_menu set isactive=0 where id IN(63,64,135);
141update main_privileges set isactive=0 where object IN(63,64,135,175);
142update `main_menu` set `menuName`='My Leaves' where `id`='62';
143update `main_menu` set `url`='/#',`parent`='149',`isactive`='1' where `id`='175';
144update `main_menu` set `parent`='0',`isactive`='0' where `id`='155';
145
146insert into `main_menu`(`id`,`menuName`,`url`,`helpText`,`toolTip`,`iconPath`,`parent`,`menuOrder`,`nav_ids`,`isactive`,`modulename`,`segment_flag`,`org_menuid`,`menufields`,`menuQuery`,`hasJoins`,`modelName`,`functionName`,`defaultOrderBy`) values
147(185,'Expenses','/#','Add Employee Expenses','Add Employee Expenses',NULL,0,18,',185,',1,'expenses',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
148(186,'Assets','/#','Add Company Assets','Add Company Assets',NULL,0,19,',186,',1,'assets',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
149(187,'Vendors','/vendors','Add Vendor for Assets','Add Vendor for Assets',NULL,0,20,',187,',0,'default',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
150(188,'My Appraisal History','/appraisalhistoryself','My Appraisal History','My Appraisal History',NULL,175,1,',149,175,188,',1,'default',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
151(189,'Team Appraisal History','/appraisalhistoryteam','Team Appraisal History','Team Appraisal History',NULL,175,2,',149,175,189,',1,'default',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
152(190,'Asset Categories','/assets/assetcategories','Add category and sub cateegory for Assets','Add category and sub cateegory for Assets','',186,2,',186,190,',1,'assets',2,0,'','',0,'','',''),
153(191,'Category','/expenses/expensecategories','Add category and sub cateegory for Expenses','Add category and sub cateegory for Expenses','',185,2,',185,191,',1,'expenses',2,0,'','',0,'','',''),
154(192,'Payment Mode','/expenses/paymentmode','Add payment modes for Expenses','Add payment modes for Expenses','',185,3,',185,192,',1,'expenses',2,0,'','',0,'','',''),
155(193,'Receipts','/expenses/receipts','Add receipts for Expenses','Add receipts for Expenses','',185,4,',185,193,',1,'expenses',2,0,'','',0,'','',''),
156(194,'Trips','/expenses/trips','Add trips for Expenses','Add trips for Expenses','',185,5,',185,194,',1,'expenses',2,0,'','',0,'','',''),
157(195,'Advances','/expenses/advances','Add advance for Employ','Add advance for Employ','',185,6,',185,195,',1,'expenses',2,0,'','',0,'','',''),
158(196,'My Advances','/expenses/advances/myadvances','View list of my advances','View list of my advances','',195,7,',185,195,196,',1,'expenses',2,0,'','',0,'','',''),
159(197,'Employee Advances','/expenses/employeeadvances','View list of Employee advances','View list of Employee advances','',195,7,',185,195,197,',1,'expenses',2,0,'','',0,'','',''),
160(198,'Expenses','/expenses/expenses','Add Employee Expenses',NULL,NULL,185,1,',185,198,',1,'expenses',2,0,NULL,NULL,NULL,NULL,NULL,NULL),
161(199,'My Employee Expenses','/expenses/myemployeeexpenses','Submitted Employee Expenses',NULL,NULL,185,9,',185,199,',1,'expenses',2,0,NULL,NULL,NULL,NULL,NULL,NULL),
162(200,'Assets','/assets/assets','Assets',NULL,NULL,186,1,',186,200,',1,'assets',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
163
164
165insert into `main_privileges`(`role`,`group_id`,`object`,`addpermission`,`editpermission`,`deletepermission`,`viewpermission`,`uploadattachments`,`viewattachments`,`createdby`,`modifiedby`,`createddate`,`modifieddate`,`isactive`) values
166(1,NULL,175,'No','No','No','Yes','No','No',1,1,now(),now(),1),
167(NULL,1,175,'No','No','No','Yes','No','No',1,1,now(),now(),1),
168(NULL,2,175,'No','No','No','Yes','No','No',1,1,now(),now(),1),
169(NULL,3,175,'No','No','No','Yes','No','No',1,1,now(),now(),1),
170(NULL,4,175,'No','No','No','Yes','No','No',1,1,now(),now(),1),
171(NULL,6,175,'No','No','No','Yes','No','No',1,1,now(),now(),1),
172(2,1,175,'No','No','No','Yes','No','No',1,1,now(),now(),1),
173(3,2,175,'No','No','No','Yes','No','No',1,1,now(),now(),1),
174(4,3,175,'No','No','No','Yes','No','No',1,1,now(),now(),1),
175(5,4,175,'No','No','No','Yes','No','No',1,1,now(),now(),1),
176(8,6,175,'No','No','No','Yes','No','No',1,1,now(),now(),1),
177(9,4,175,'No','No','No','Yes','No','No',1,1,now(),now(),1),
178(1,NULL,185,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
179(NULL,1,185,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
180(NULL,2,185,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
181(NULL,3,185,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
182(NULL,4,185,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
183(NULL,6,185,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
184(2,1,185,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
185(3,2,185,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
186(4,3,185,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
187(5,4,185,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
188(8,6,185,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
189(9,4,185,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
190(1,NULL,186,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
191(NULL,1,186,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
192(NULL,2,186,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
193(NULL,3,186,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
194(NULL,4,186,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
195(NULL,6,186,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
196(2,1,186,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
197(3,2,186,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
198(4,3,186,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
199(5,4,186,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
200(8,6,186,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
201(9,4,186,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
202(1,NULL,188,'No','No','No','Yes','No','No',1,1,now(),now(),1),
203(NULL,1,188,'No','No','No','Yes','No','No',1,1,now(),now(),1),
204(NULL,2,188,'No','No','No','Yes','No','No',1,1,now(),now(),1),
205(NULL,3,188,'No','No','No','Yes','No','No',1,1,now(),now(),1),
206(NULL,4,188,'No','No','No','Yes','No','No',1,1,now(),now(),1),
207(NULL,6,188,'No','No','No','Yes','No','No',1,1,now(),now(),1),
208(2,1,188,'No','No','No','Yes','No','No',1,1,now(),now(),1),
209(3,2,188,'No','No','No','Yes','No','No',1,1,now(),now(),1),
210(4,3,188,'No','No','No','Yes','No','No',1,1,now(),now(),1),
211(5,4,188,'No','No','No','Yes','No','No',1,1,now(),now(),1),
212(8,6,188,'No','No','No','Yes','No','No',1,1,now(),now(),1),
213(9,4,188,'No','No','No','Yes','No','No',1,1,now(),now(),1),
214(1,NULL,189,'No','No','No','Yes','No','No',1,1,now(),now(),1),
215(NULL,1,189,'No','No','No','Yes','No','No',1,1,now(),now(),1),
216(NULL,2,189,'No','No','No','Yes','No','No',1,1,now(),now(),1),
217(NULL,3,189,'No','No','No','Yes','No','No',1,1,now(),now(),1),
218(NULL,4,189,'No','No','No','Yes','No','No',1,1,now(),now(),1),
219(NULL,6,189,'No','No','No','Yes','No','No',1,1,now(),now(),1),
220(2,1,189,'No','No','No','Yes','No','No',1,1,now(),now(),1),
221(3,2,189,'No','No','No','Yes','No','No',1,1,now(),now(),1),
222(4,3,189,'No','No','No','Yes','No','No',1,1,now(),now(),1),
223(5,4,189,'No','No','No','Yes','No','No',1,1,now(),now(),1),
224(8,6,189,'No','No','No','Yes','No','No',1,1,now(),now(),1),
225(9,4,189,'No','No','No','Yes','No','No',1,1,now(),now(),1),
226(NULL,3,8,'No','No','No','No','No','No',1,1,now(),now(),1),
227(4,3,8,'No','No','No','No','No','No',1,1,now(),now(),1),
228(1,NULL,190,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
229(NULL,1,190,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
230(NULL,2,190,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
231(NULL,3,190,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
232(NULL,4,190,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
233(NULL,6,190,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
234(2,1,190,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
235(3,2,190,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
236(4,3,190,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
237(5,4,190,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
238(8,6,190,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
239(9,4,190,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
240(1,NULL,191,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
241(NULL,1,191,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
242(NULL,2,191,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
243(NULL,3,191,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
244(NULL,4,191,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
245(NULL,6,191,'NO','No','No','No','No','No',1,1,NOW(),NOW(),0),
246(2,1,191,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
247(3,2,191,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
248(4,3,191,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
249(5,4,191,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
250(8,6,191,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
251(9,4,191,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
252(1,NULL,192,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
253(NULL,1,192,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
254(NULL,2,192,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
255(NULL,3,192,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
256(NULL,4,192,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
257(NULL,6,192,'NO','No','No','No','No','No',1,1,NOW(),NOW(),0),
258(2,1,192,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
259(3,2,192,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
260(4,3,192,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
261(5,4,192,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
262(8,6,192,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
263(9,4,192,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
264(1,NULL,193,'No','No','No','No','No','No',1,1,NOW(),NOW(),1),
265(NULL,1,193,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
266(NULL,2,193,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
267(NULL,3,193,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
268(NULL,4,193,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
269(NULL,6,193,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
270(2,1,193,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
271(3,2,193,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
272(4,3,193,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
273(5,4,193,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
274(8,6,193,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
275(9,4,193,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
276(1,NULL,194,'No','No','No','No','No','No',1,1,NOW(),NOW(),1),
277(NULL,1,194,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
278(NULL,2,194,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
279(NULL,3,194,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
280(NULL,4,194,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
281(NULL,6,194,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
282(2,1,194,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
283(3,2,194,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
284(4,3,194,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
285(5,4,194,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
286(8,6,194,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
287(9,4,194,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
288(1,NULL,195,'No','No','No','No','No','No',1,1,NOW(),NOW(),1),
289(NULL,1,195,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
290(NULL,2,195,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
291(NULL,3,195,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
292(NULL,4,195,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
293(NULL,6,195,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
294(2,1,195,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
295(3,2,195,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
296(4,3,195,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
297(5,4,195,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
298(8,6,195,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
299(9,4,195,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
300(1,NULL,196,'No','No','No','No','No','No',1,1,NOW(),NOW(),1),
301(NULL,1,196,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
302(NULL,2,196,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
303(NULL,3,196,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
304(NULL,4,196,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
305(NULL,6,196,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
306(2,1,196,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
307(3,2,196,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
308(4,3,196,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
309(5,4,196,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
310(8,6,196,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
311(9,4,196,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
312(1,NULL,197,'No','No','No','No','No','No',1,1,NOW(),NOW(),1),
313(NULL,1,197,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
314(NULL,2,197,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
315(NULL,3,197,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
316(NULL,4,197,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
317(NULL,6,197,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
318(2,1,197,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
319(3,2,197,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
320(4,3,197,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
321(5,4,197,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
322(8,6,197,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
323(9,4,197,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
324(1,NULL,198,'No','No','No','No','No','No',1,1,NOW(),NOW(),1),
325(NULL,1,198,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
326(NULL,2,198,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
327(NULL,3,198,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
328(NULL,4,198,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
329(NULL,6,198,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
330(2,1,198,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
331(3,2,198,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
332(4,3,198,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
333(5,4,198,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
334(8,6,198,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
335(9,4,198,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
336(1,NULL,199,'No','No','No','No','No','No',1,1,NOW(),NOW(),1),
337(NULL,1,199,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
338(NULL,2,199,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
339(NULL,3,199,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
340(NULL,4,199,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
341(NULL,6,199,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
342(2,1,199,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
343(3,2,199,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
344(4,3,199,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
345(5,4,199,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
346(8,6,199,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
347(9,4,199,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
348(1,NULL,200,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
349(NULL,1,200,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
350(NULL,2,200,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
351(NULL,3,200,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
352(NULL,4,200,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
353(NULL,6,200,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
354(2,1,200,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
355(3,2,200,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
356(4,3,200,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
357(5,4,200,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
358(8,6,200,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
359(9,4,200,'No','No','No','No','No','No',1,1,NOW(),NOW(),0),
360(NULL,3,8,'No','No','No','No','No','No',1,1,now(),now(),1),
361(4,3,8,'No','No','No','No','No','No',1,1,now(),now(),1);
362
363
364CREATE TABLE `assets` (
365 `id` int(11) NOT NULL AUTO_INCREMENT,
366 `category` int(50) NOT NULL,
367 `sub_category` int(50) NOT NULL,
368 `company_asset_code` int(50) NOT NULL,
369 `name` varchar(50) NOT NULL,
370 `location` varchar(15) NOT NULL,
371 `allocated_to` int(11) DEFAULT NULL,
372 `responsible_technician` int(50) NOT NULL,
373 `vendor` int(11) NOT NULL,
374 `asset_classification` varchar(50) NOT NULL,
375 `purchase_date` date DEFAULT NULL,
376 `invoice_number` varchar(11) NOT NULL,
377 `manufacturer` varchar(50) NOT NULL,
378 `key_number` varchar(11) NOT NULL,
379 `warenty_status` enum('Yes','No') NOT NULL,
380 `warenty_end_date` date DEFAULT NULL,
381 `is_working` enum('No','Yes') NOT NULL,
382 `notes` text,
383 `image` text,
384 `imagencrpname` text NOT NULL,
385 `qr_image` text NOT NULL,
386 `isactive` tinyint(4) NOT NULL,
387 `created_by` int(50) NOT NULL,
388 `modified_by` int(50) NOT NULL,
389 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
390 `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
391 PRIMARY KEY (`id`)
392) ENGINE=InnoDB DEFAULT CHARSET=utf8;
393
394CREATE TABLE `assets_categories` (
395 `id` int(11) NOT NULL AUTO_INCREMENT,
396 `name` varchar(50) CHARACTER SET latin1 NOT NULL,
397 `parent` int(11) NOT NULL,
398 `is_active` tinyint(11) NOT NULL DEFAULT '1',
399 `created_by` int(11) NOT NULL,
400 `modified_by` varchar(11) CHARACTER SET latin1 NOT NULL,
401 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
402 `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
403 PRIMARY KEY (`id`)
404) ENGINE=InnoDB AUTO_INCREMENT=198 DEFAULT CHARSET=utf8;
405
406CREATE TABLE `assets_history` (
407 `id` int(11) NOT NULL AUTO_INCREMENT,
408 `asset_id` int(11) DEFAULT NULL,
409 `user_id` int(11) DEFAULT NULL,
410 `history` varchar(500) DEFAULT NULL,
411 `isactive` tinyint(1) DEFAULT NULL,
412 `createdby` int(11) DEFAULT NULL,
413 `modifiedby` int(11) DEFAULT NULL,
414 `createddate` datetime DEFAULT NULL,
415 `modifieddate` datetime DEFAULT NULL,
416 PRIMARY KEY (`id`)
417) ENGINE=InnoDB DEFAULT CHARSET=utf8;
418
419CREATE TABLE `expense_advacne_summary` (
420 `id` int(11) NOT NULL auto_increment,
421 `employee_id` int(11) default NULL,
422 `total` float(10,2) default NULL,
423 `utilized` float(10,2) default NULL,
424 `returned` float(10,2) default NULL,
425 `balance` float(10,2) default NULL,
426 `createdby` int(11) default NULL,
427 `modifiedby` int(11) default NULL,
428 `createddate` datetime default NULL,
429 `modifieddate` datetime default NULL,
430 `isactive` tinyint(1) default '1',
431 PRIMARY KEY (`id`),
432 UNIQUE KEY `UNIQUEEMP` (`employee_id`)
433) ENGINE=InnoDB DEFAULT CHARSET=utf8;
434
435CREATE TABLE `expense_advance` (
436 `id` int(11) NOT NULL auto_increment,
437 `type` enum('advance','return') default 'advance',
438 `from_id` int(11) default NULL,
439 `to_id` int(11) default NULL,
440 `payment_ref_number` varchar(200) default NULL,
441 `payment_mode_id` int(11) default NULL,
442 `project_id` int(11) default NULL,
443 `currency_id` int(11) default NULL,
444 `amount` float(10,2) default NULL,
445 `application_currency_id` int(11) default NULL,
446 `application_amount` float(10,2) default NULL,
447 `advance_conversion_rate` float(10,2) default NULL,
448 `description` text,
449 `createdby` int(11) default NULL,
450 `modifiedby` int(11) default NULL,
451 `createddate` datetime NOT NULL,
452 `modifieddate` datetime default NULL,
453 `isactive` tinyint(1) default '1',
454 PRIMARY KEY (`id`)
455) ENGINE=InnoDB DEFAULT CHARSET=utf8;
456
457CREATE TABLE `expense_categories` (
458 `id` int(11) NOT NULL auto_increment,
459 `expense_category_name` varchar(100) default NULL,
460 `unit_price` varchar(50) default NULL,
461 `unit_name` varchar(50) default NULL,
462 `createdby` int(11) default NULL,
463 `modifiedby` int(11) default NULL,
464 `created_date` datetime default NULL,
465 `modifieddate` datetime default NULL,
466 `isactive` tinyint(1) default '1',
467 PRIMARY KEY (`id`)
468) ENGINE=InnoDB DEFAULT CHARSET=utf8;
469
470CREATE TABLE `expense_forward` (
471 `id` int(11) NOT NULL auto_increment,
472 `expense_id` int(11) default NULL,
473 `trip_id` int(11) default NULL,
474 `from_id` int(11) default NULL,
475 `to_id` int(11) default NULL,
476 `createdby` int(11) default NULL,
477 `modifiedby` int(11) default NULL,
478 `createddate` datetime default NULL,
479 `modifieddate` datetime default NULL,
480 `isactive` tinyint(1) default '1',
481 PRIMARY KEY (`id`)
482) ENGINE=InnoDB DEFAULT CHARSET=utf8;
483
484CREATE TABLE `expense_history` (
485 `id` int(11) NOT NULL auto_increment,
486 `expense_id` int(11) default NULL,
487 `trip_id` int(11) default NULL,
488 `history` varchar(500) default NULL,
489 `createdby` int(11) default NULL,
490 `modifiedby` int(11) default NULL,
491 `createddate` datetime default NULL,
492 `modifieddate` datetime default NULL,
493 `isactive` tinyint(1) default '1',
494 PRIMARY KEY (`id`)
495) ENGINE=InnoDB DEFAULT CHARSET=utf8;
496
497CREATE TABLE `expense_notifications` (
498 `id` int(11) NOT NULL auto_increment,
499 `expense_id` int(11) default NULL,
500 `trip_id` int(11) default NULL,
501 `notification` varchar(500) default NULL,
502 `link` varchar(200) default NULL,
503 `createdby` int(11) default NULL,
504 `modifiedby` int(11) default NULL,
505 `createddate` datetime default NULL,
506 `modifieddate` datetime default NULL,
507 `isactive` tinyint(1) default '1',
508 PRIMARY KEY (`id`)
509) ENGINE=InnoDB DEFAULT CHARSET=utf8;
510
511CREATE TABLE `expense_payment_methods` (
512 `id` int(11) NOT NULL auto_increment,
513 `payment_method_name` varchar(100) default NULL,
514 `createdby` int(11) default NULL,
515 `modifiedby` int(11) default NULL,
516 `created_date` datetime default NULL,
517 `modifieddate` datetime default NULL,
518 `isactive` tinyint(1) default '1',
519 PRIMARY KEY (`id`)
520) ENGINE=InnoDB DEFAULT CHARSET=utf8;
521
522CREATE TABLE `expense_receipts` (
523 `id` int(11) NOT NULL auto_increment,
524 `expense_id` int(11) default NULL,
525 `trip_id` int(11) default NULL,
526 `receipt_name` varchar(100) default NULL COMMENT 'orginal file name',
527 `receipt_filename` varchar(100) default NULL COMMENT 'auto generated file name',
528 `receipt_file_type` varchar(5) default NULL,
529 `createdby` int(11) default NULL,
530 `modifiedby` int(11) default NULL,
531 `createddate` datetime default NULL,
532 `modifieddate` datetime default NULL,
533 `isactive` tinyint(1) default '1',
534 PRIMARY KEY (`id`)
535) ENGINE=InnoDB DEFAULT CHARSET=utf8;
536
537CREATE TABLE `expense_return_advance` (
538 `id` int(11) NOT NULL auto_increment,
539 `from_id` int(11) default NULL,
540 `to_id` int(11) default NULL,
541 `currency_id` int(11) default NULL,
542 `returned_amount` float(10,2) default NULL,
543 `createdby` int(11) default NULL,
544 `modifiedby` int(11) default NULL,
545 `createddate` datetime default NULL,
546 `modifieddate` datetime default NULL,
547 `isactive` tinyint(1) default '1',
548 PRIMARY KEY (`id`)
549) ENGINE=InnoDB DEFAULT CHARSET=utf8;
550
551CREATE TABLE `expense_trip_history` (
552 `id` int(11) NOT NULL auto_increment,
553 `trip_id` int(11) default NULL,
554 `expense_id` int(11) default NULL,
555 `history` varchar(500) default NULL,
556 `createdby` int(11) default NULL,
557 `modifiedby` int(11) default NULL,
558 `createddate` datetime default NULL,
559 `modifieddate` datetime default NULL,
560 `isactive` tinyint(1) default '1',
561 PRIMARY KEY (`id`)
562) ENGINE=InnoDB DEFAULT CHARSET=utf8;
563
564CREATE TABLE `expense_trips` (
565 `id` int(11) NOT NULL auto_increment,
566 `manager_id` int(11) default NULL,
567 `trip_name` varchar(100) default NULL,
568 `from_date` date default NULL,
569 `to_date` date default NULL,
570 `description` text,
571 `status` enum('NS','S','A','R') default 'NS' COMMENT 'NS-Notsubmitted,S-submitted,R-Rejected,A-Approved',
572 `rejected_note` text,
573 `createdby` int(11) default NULL,
574 `modifiedby` int(11) default NULL,
575 `createddate` datetime default NULL,
576 `modifieddate` datetime default NULL,
577 `isactive` tinyint(1) default '1',
578 PRIMARY KEY (`id`)
579) ENGINE=InnoDB DEFAULT CHARSET=utf8;
580
581CREATE TABLE `expenses` (
582 `id` int(11) NOT NULL auto_increment,
583 `expense_name` varchar(100) default NULL,
584 `category_id` int(11) default NULL,
585 `project_id` int(11) default NULL,
586 `client_id` int(11) default NULL,
587 `trip_id` int(11) default NULL,
588 `manager_id` int(11) default NULL,
589 `expense_date` date default NULL,
590 `expense_currency_id` int(11) default NULL,
591 `expense_amount` float(10,2) default NULL,
592 `expense_conversion_rate` float(5,2) default NULL,
593 `application_currency_id` int(11) default NULL,
594 `application_amount` float(10,2) default NULL,
595 `advance_amount` float(10,2) default NULL,
596 `is_reimbursable` tinyint(1) default NULL,
597 `is_from_advance` tinyint(1) default '0',
598 `expense_payment_id` int(11) default NULL,
599 `expense_payment_ref_no` varchar(200) default NULL,
600 `description` text,
601 `status` enum('saved','submitted','approved','rejected') default 'saved',
602 `createdby` int(11) default NULL,
603 `modifiedby` int(11) default NULL,
604 `createddate` datetime default NULL,
605 `modifieddate` datetime default NULL,
606 `isactive` tinyint(1) default NULL,
607 PRIMARY KEY (`id`)
608) ENGINE=InnoDB DEFAULT CHARSET=utf8;
609
610
611DELIMITER $$
612
613DROP TRIGGER `main_sd_request_aft_ins`$$
614
615CREATE
616 TRIGGER `main_sd_request_aft_ins` AFTER INSERT ON `main_sd_requests`
617 FOR EACH ROW BEGIN
618 DECLARE x_service_desk_name,x_service_request_name,x_raised_by_name,x_executor_name,
619 x_reporting_manager_name,x_approver_1_name,x_approver_2_name,x_approver_3_name,raised_empid,raised_img
620 VARCHAR(250);
621
622 IF(new.request_for=1) THEN
623 SELECT service_desk_name INTO x_service_desk_name FROM main_sd_depts WHERE id = new.service_desk_id;
624 SELECT service_request_name INTO x_service_request_name FROM main_sd_reqtypes WHERE id = new.service_request_id;
625 ELSE
626 SELECT NAME INTO x_service_desk_name FROM assets WHERE id = new.service_desk_id;
627 SELECT NAME INTO x_service_request_name FROM assets_categories WHERE id = new.service_request_id AND parent=0;
628 END IF;
629 SELECT userfullname,employeeId,profileimg INTO x_raised_by_name,raised_empid,raised_img FROM main_employees_summary WHERE user_id = new.raised_by;
630 SELECT userfullname INTO x_executor_name FROM main_employees_summary WHERE user_id = new.executor_id;
631 SELECT userfullname INTO x_reporting_manager_name FROM main_employees_summary WHERE user_id = new.reporting_manager_id;
632 SELECT userfullname INTO x_approver_1_name FROM main_employees_summary WHERE user_id = new.approver_1;
633 SELECT userfullname INTO x_approver_2_name FROM main_employees_summary WHERE user_id = new.approver_2;
634 SELECT userfullname INTO x_approver_3_name FROM main_employees_summary WHERE user_id = new.approver_3;
635
636 INSERT INTO main_sd_requests_summary (
637 request_for,sd_requests_id, service_desk_id, service_desk_name, service_desk_conf_id, service_request_name, service_request_id,
638 priority, description, attachment, STATUS, raised_by, raised_by_name, ticket_number, executor_id, executor_name, executor_comments,
639 reporting_manager_id, reporting_manager_name, approver_status_1, approver_status_2, approver_status_3, reporting_manager_status,
640 approver_1, approver_1_name, approver_2, approver_2_name, approver_3, approver_3_name, isactive, createdby, modifiedby,
641 createddate, modifieddate,raised_by_empid,approver_1_comments,approver_2_comments,approver_3_comments,reporting_manager_comments,
642 to_mgmt_comments,to_manager_comments
643 )
644 VALUES (
645 new.request_for,new.id, new.service_desk_id, x_service_desk_name, new.service_desk_conf_id, x_service_request_name, new.service_request_id,
646 new.priority, new.description, new.attachment, new.status, new.raised_by, x_raised_by_name, new.ticket_number, new.executor_id,
647 x_executor_name, new.executor_comments, new.reporting_manager_id, x_reporting_manager_name, new.approver_status_1,
648 new.approver_status_2, new.approver_status_3, new.reporting_manager_status, new.approver_1, x_approver_1_name, new.approver_2,
649 x_approver_2_name, new.approver_3, x_approver_3_name, new.isactive, new.createdby, new.modifiedby, new.createddate, new.modifieddate,
650 raised_empid,new.approver_1_comments,new.approver_2_comments,new.approver_3_comments,new.reporting_manager_comments,
651 new.to_mgmt_comments,new.to_manager_comments
652 );
653 INSERT INTO main_request_history(request_id,description,emp_id,emp_name,createdby,modifiedby,createddate,modifieddate,isactive,emp_profileimg)
654 VALUE (new.id,CONCAT(CONCAT(UCASE(LEFT(x_service_desk_name, 1)), SUBSTRING(x_service_desk_name, 2)) ,' Request has been raised by '),new.raised_by,CONCAT(UCASE(LEFT(x_raised_by_name, 1)), SUBSTRING(x_raised_by_name, 2)),new.createdby,new.createdby,new.createddate,new.modifieddate,new.isactive,raised_img);
655 END;
656
657$$
658
659DELIMITER ;
660
661DELIMITER $$
662
663DROP TRIGGER `main_sd_request_aft_upd`$$
664
665CREATE
666 TRIGGER `main_sd_request_aft_upd` AFTER UPDATE ON `main_sd_requests`
667 FOR EACH ROW BEGIN
668 DECLARE x_service_desk_name,x_service_request_name,x_raised_by_name,x_executor_name,
669 x_reporting_manager_name,x_approver_1_name,x_approver_2_name,x_approver_3_name
670 VARCHAR(250);
671
672 IF(new.request_for=1) THEN
673 SELECT service_desk_name INTO x_service_desk_name FROM main_sd_depts WHERE id = new.service_desk_id;
674 SELECT service_request_name INTO x_service_request_name FROM main_sd_reqtypes WHERE id = new.service_request_id;
675 ELSE
676 SELECT NAME INTO x_service_desk_name FROM assets WHERE id = new.service_desk_id;
677 SELECT NAME INTO x_service_request_name FROM assets_categories WHERE id = new.service_request_id AND parent=0;
678 END IF;
679 SELECT userfullname INTO x_raised_by_name FROM main_employees_summary WHERE user_id = new.raised_by;
680 SELECT userfullname INTO x_executor_name FROM main_employees_summary WHERE user_id = new.executor_id;
681 SELECT userfullname INTO x_reporting_manager_name FROM main_employees_summary WHERE user_id = new.reporting_manager_id;
682 SELECT userfullname INTO x_approver_1_name FROM main_employees_summary WHERE user_id = new.approver_1;
683 SELECT userfullname INTO x_approver_2_name FROM main_employees_summary WHERE user_id = new.approver_2;
684 SELECT userfullname INTO x_approver_3_name FROM main_employees_summary WHERE user_id = new.approver_3;
685
686 UPDATE main_sd_requests_summary SET
687 request_for=new.request_for,service_desk_id = new.service_desk_id, service_desk_name = x_service_desk_name, service_desk_conf_id = new.service_desk_conf_id,
688 service_request_name = x_service_request_name, service_request_id = new.service_request_id, priority = new.priority,
689 description = new.description, attachment = new.attachment, STATUS = new.status, raised_by = new.raised_by,
690 raised_by_name = x_raised_by_name, ticket_number = new.ticket_number, executor_id = new.executor_id, executor_name = x_executor_name,
691 executor_comments = new.executor_comments, reporting_manager_id = new.reporting_manager_id, reporting_manager_name = x_reporting_manager_name,
692 approver_status_1 = new.approver_status_1, approver_status_2 = new.approver_status_2, approver_status_3 = new.approver_status_3,
693 reporting_manager_status = new.reporting_manager_status, approver_1 = new.approver_1, approver_1_name = x_approver_1_name,
694 approver_2 = new.approver_2, approver_2_name = x_approver_2_name, approver_3 = new.approver_3, approver_3_name = x_approver_3_name,
695 isactive = new.isactive, createdby = new.createdby, modifiedby = new.modifiedby, createddate = new.createddate, modifieddate = new.modifieddate
696 ,approver_1_comments = new.approver_1_comments,approver_2_comments = new.approver_2_comments,approver_3_comments = new.approver_3_comments,reporting_manager_comments = new.reporting_manager_comments,
697 to_mgmt_comments = new.to_mgmt_comments,to_manager_comments = new.to_manager_comments
698 WHERE sd_requests_id = new.id;
699 END;
700
701$$
702
703DELIMITER ;
704
705set global sql_mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
706ALTER TABLE assets MODIFY purchase_date DATE;
707ALTER TABLE assets MODIFY warenty_end_date DATE;
708ALTER TABLE main_accountclasstype MODIFY createddate DATETIME;
709ALTER TABLE main_accountclasstype MODIFY modifieddate DATETIME;
710ALTER TABLE main_announcements MODIFY createddate DATETIME;
711ALTER TABLE main_announcements MODIFY modifieddate DATETIME;
712ALTER TABLE main_assignmententryreasoncode MODIFY createddate DATETIME;
713ALTER TABLE main_assignmententryreasoncode MODIFY modifieddate DATETIME;
714ALTER TABLE main_attendancestatuscode MODIFY createddate DATETIME;
715ALTER TABLE main_attendancestatuscode MODIFY modifieddate DATETIME;
716ALTER TABLE main_bankaccounttype MODIFY createddate DATETIME;
717ALTER TABLE main_bankaccounttype MODIFY modifieddate DATETIME;
718ALTER TABLE main_cities MODIFY createddate DATETIME;
719ALTER TABLE main_cities MODIFY modifieddate DATETIME;
720ALTER TABLE main_competencylevel MODIFY createddate DATETIME;
721ALTER TABLE main_competencylevel MODIFY modifieddate DATETIME;
722ALTER TABLE main_countries MODIFY createddate DATETIME;
723ALTER TABLE main_countries MODIFY modifieddate DATETIME;
724ALTER TABLE main_currency MODIFY createddate DATETIME;
725ALTER TABLE main_currency MODIFY modifieddate DATETIME;
726ALTER TABLE main_currencyconverter MODIFY createddate DATETIME;
727ALTER TABLE main_currencyconverter MODIFY modifieddate DATETIME;
728ALTER TABLE main_dateformat MODIFY createddate DATETIME;
729ALTER TABLE main_dateformat MODIFY modifieddate DATETIME;
730ALTER TABLE main_educationlevelcode MODIFY createddate DATETIME;
731ALTER TABLE main_educationlevelcode MODIFY modifieddate DATETIME;
732ALTER TABLE main_eeoccategory MODIFY createddate DATETIME;
733ALTER TABLE main_eeoccategory MODIFY modifieddate DATETIME;
734ALTER TABLE main_empadditionaldetails MODIFY createddate DATETIME;
735ALTER TABLE main_empadditionaldetails MODIFY modifieddate DATETIME;
736ALTER TABLE main_empcommunicationdetails MODIFY createddate DATETIME;
737ALTER TABLE main_empcommunicationdetails MODIFY modifieddate DATETIME;
738ALTER TABLE main_empeducationdetails MODIFY createddate DATETIME;
739ALTER TABLE main_empeducationdetails MODIFY modifieddate DATETIME;
740ALTER TABLE main_empexperiancedetails MODIFY createddate DATETIME;
741ALTER TABLE main_empexperiancedetails MODIFY modifieddate DATETIME;
742ALTER TABLE main_empholidays MODIFY createddate DATETIME;
743ALTER TABLE main_empholidays MODIFY modifieddate DATETIME;
744ALTER TABLE main_empjobhistory MODIFY createddate DATETIME;
745ALTER TABLE main_empjobhistory MODIFY modifieddate DATETIME;
746ALTER TABLE main_employeedocuments MODIFY createddate DATETIME;
747ALTER TABLE main_employeedocuments MODIFY modifieddate DATETIME;
748ALTER TABLE main_employeeleavetypes MODIFY createddate DATETIME;
749ALTER TABLE main_employeeleavetypes MODIFY modifieddate DATETIME;
750ALTER TABLE main_employmentstatus MODIFY createddate DATETIME;
751ALTER TABLE main_employmentstatus MODIFY modifieddate DATETIME;
752ALTER TABLE main_empmedicalclaims MODIFY createddate DATETIME;
753ALTER TABLE main_empmedicalclaims MODIFY modifieddate DATETIME;
754ALTER TABLE main_emppersonaldetails MODIFY createddate DATETIME;
755ALTER TABLE main_emppersonaldetails MODIFY modifieddate DATETIME;
756ALTER TABLE main_empsalarydetails MODIFY createddate DATETIME;
757ALTER TABLE main_empsalarydetails MODIFY modifieddate DATETIME;
758ALTER TABLE main_empskills MODIFY createddate DATETIME;
759ALTER TABLE main_empskills MODIFY modifieddate DATETIME;
760ALTER TABLE main_empworkeligibility MODIFY createddate DATETIME;
761ALTER TABLE main_empworkeligibility MODIFY modifieddate DATETIME;
762ALTER TABLE main_ethniccode MODIFY createddate DATETIME;
763ALTER TABLE main_ethniccode MODIFY modifieddate DATETIME;
764ALTER TABLE main_gender MODIFY createddate DATETIME;
765ALTER TABLE main_gender MODIFY modifieddate DATETIME;
766ALTER TABLE main_geographygroup MODIFY createddate DATETIME;
767ALTER TABLE main_geographygroup MODIFY modifieddate DATETIME;
768ALTER TABLE main_holidaygroups MODIFY createddate DATETIME;
769ALTER TABLE main_holidaygroups MODIFY modifieddate DATETIME;
770ALTER TABLE main_jobtitles MODIFY createddate DATETIME;
771ALTER TABLE main_jobtitles MODIFY modifieddate DATETIME;
772ALTER TABLE main_language MODIFY createddate DATETIME;
773ALTER TABLE main_language MODIFY modifieddate DATETIME;
774ALTER TABLE main_licensetype MODIFY createddate DATETIME;
775ALTER TABLE main_licensetype MODIFY modifieddate DATETIME;
776ALTER TABLE main_maritalstatus MODIFY createddate DATETIME;
777ALTER TABLE main_maritalstatus MODIFY modifieddate DATETIME;
778ALTER TABLE main_militaryservice MODIFY createddate DATETIME;
779ALTER TABLE main_militaryservice MODIFY modifieddate DATETIME;
780ALTER TABLE main_monthslist MODIFY createddate DATETIME;
781ALTER TABLE main_monthslist MODIFY modifieddate DATETIME;
782ALTER TABLE main_nationality MODIFY createddate DATETIME;
783ALTER TABLE main_nationality MODIFY modifieddate DATETIME;
784ALTER TABLE main_nationalitycontextcode MODIFY createddate DATETIME;
785ALTER TABLE main_nationalitycontextcode MODIFY modifieddate DATETIME;
786ALTER TABLE main_numberformats MODIFY createddate DATETIME;
787ALTER TABLE main_numberformats MODIFY modifieddate DATETIME;
788ALTER TABLE main_payfrequency MODIFY createddate DATETIME;
789ALTER TABLE main_payfrequency MODIFY modifieddate DATETIME;
790ALTER TABLE main_pa_category MODIFY createddate DATETIME;
791ALTER TABLE main_pa_category MODIFY modifieddate DATETIME;
792ALTER TABLE main_pa_employee_ratings MODIFY createddate DATETIME;
793ALTER TABLE main_pa_employee_ratings MODIFY modifieddate DATETIME;
794ALTER TABLE main_pa_ff_employee_ratings MODIFY createddate DATETIME;
795ALTER TABLE main_pa_ff_employee_ratings MODIFY modifieddate DATETIME;
796ALTER TABLE main_pa_manager_initialization MODIFY createddate DATETIME;
797ALTER TABLE main_pa_manager_initialization MODIFY modifieddate DATETIME;
798ALTER TABLE main_pa_ratings MODIFY createddate DATETIME;
799ALTER TABLE main_pa_ratings MODIFY modifieddate DATETIME;
800ALTER TABLE main_positions MODIFY createddate DATETIME;
801ALTER TABLE main_positions MODIFY modifieddate DATETIME;
802ALTER TABLE main_prefix MODIFY createddate DATETIME;
803ALTER TABLE main_prefix MODIFY modifieddate DATETIME;
804ALTER TABLE main_racecode MODIFY createddate DATETIME;
805ALTER TABLE main_racecode MODIFY modifieddate DATETIME;
806ALTER TABLE main_remunerationbasis MODIFY createddate DATETIME;
807ALTER TABLE main_remunerationbasis MODIFY modifieddate DATETIME;
808ALTER TABLE main_roles MODIFY createddate DATETIME;
809ALTER TABLE main_roles MODIFY modifieddate DATETIME;
810ALTER TABLE main_sd_configurations MODIFY createddate DATETIME;
811ALTER TABLE main_sd_configurations MODIFY modifieddate DATETIME;
812ALTER TABLE main_sd_depts MODIFY createddate DATETIME;
813ALTER TABLE main_sd_depts MODIFY modifieddate DATETIME;
814ALTER TABLE main_sd_reqtypes MODIFY createddate DATETIME;
815ALTER TABLE main_sd_reqtypes MODIFY modifieddate DATETIME;
816ALTER TABLE main_states MODIFY createddate DATETIME;
817ALTER TABLE main_states MODIFY modifieddate DATETIME;
818ALTER TABLE main_timeformat MODIFY createddate DATETIME;
819ALTER TABLE main_timeformat MODIFY modifieddate DATETIME;
820ALTER TABLE main_timezone MODIFY createddate DATETIME;
821ALTER TABLE main_timezone MODIFY modifieddate DATETIME;
822ALTER TABLE main_userloginlog MODIFY logindatetime DATETIME;
823ALTER TABLE main_users MODIFY createddate DATETIME;
824ALTER TABLE main_users MODIFY modifieddate DATETIME;
825ALTER TABLE main_veteranstatus MODIFY createddate DATETIME;
826ALTER TABLE main_veteranstatus MODIFY modifieddate DATETIME;
827ALTER TABLE main_weekdays MODIFY createddate DATETIME;
828ALTER TABLE main_weekdays MODIFY modifieddate DATETIME;
829ALTER TABLE main_workeligibilitydoctypes MODIFY createddate DATETIME;
830ALTER TABLE main_workeligibilitydoctypes MODIFY modifieddate DATETIME;
831ALTER TABLE tbl_employmentstatus MODIFY createddate DATETIME;
832ALTER TABLE tbl_employmentstatus MODIFY modifieddate DATETIME;
833ALTER TABLE tbl_weeks MODIFY createddate DATETIME;
834ALTER TABLE tbl_weeks MODIFY modifieddate DATETIME;
835ALTER TABLE tm_emp_ts_notes MODIFY sun_date DATE;
836ALTER TABLE assets MODIFY category int;
837ALTER TABLE assets MODIFY sub_category int;
838ALTER TABLE assets MODIFY company_asset_code int;
839ALTER TABLE assets MODIFY allocated_to int;
840ALTER TABLE assets MODIFY responsible_technician int;
841ALTER TABLE assets MODIFY vendor int;
842ALTER TABLE assets MODIFY created_by int;
843ALTER TABLE assets MODIFY modified_by int;
844ALTER TABLE assets_categories MODIFY parent int;
845ALTER TABLE assets_categories MODIFY created_by int;
846ALTER TABLE main_candidatedetails MODIFY requisition_id int;
847ALTER TABLE main_cronstatus MODIFY cron_status int;
848ALTER TABLE main_currencyconverter MODIFY basecurrency int;
849ALTER TABLE main_currencyconverter MODIFY targetcurrency int;
850ALTER TABLE main_emailcontacts MODIFY group_id int;
851ALTER TABLE main_emailcontacts MODIFY business_unit_id int;
852ALTER TABLE main_employees_summary MODIFY user_id int;
853ALTER TABLE main_empworkdetails MODIFY user_id int;
854ALTER TABLE main_hierarchylevels MODIFY level_number int;
855ALTER TABLE main_interviewrounddetails MODIFY interview_round_number int;
856ALTER TABLE main_mail_settings MODIFY port int;
857ALTER TABLE main_requisition MODIFY position_id int;
858ALTER TABLE main_requisition_summary MODIFY req_id int;
859ALTER TABLE main_requisition_summary MODIFY position_id int;
860ALTER TABLE main_roles MODIFY levelid int;
861ALTER TABLE main_settings MODIFY userid int;
862ALTER TABLE tm_clients MODIFY created_by int;
863ALTER TABLE tm_emp_timesheets MODIFY created_by int;
864ALTER TABLE tm_projects MODIFY created_by int;
865ALTER TABLE tm_project_employees MODIFY created_by int;
866ALTER TABLE tm_project_tasks MODIFY created_by int;
867ALTER TABLE tm_project_task_employees MODIFY created_by int;
868ALTER TABLE tm_tasks MODIFY created_by int;
869ALTER TABLE main_empadditionaldetails MODIFY user_id bigint;
870ALTER TABLE main_empcommunicationdetails MODIFY user_id bigint;
871ALTER TABLE main_empcreditcarddetails MODIFY user_id bigint;
872ALTER TABLE main_empdependencydetails MODIFY user_id bigint;
873ALTER TABLE main_empdisabilitydetails MODIFY user_id bigint;
874ALTER TABLE main_empeducationdetails MODIFY user_id bigint;
875ALTER TABLE main_empexperiancedetails MODIFY user_id bigint;
876ALTER TABLE main_empjobhistory MODIFY user_id bigint;
877ALTER TABLE main_employeedocuments MODIFY user_id bigint;
878ALTER TABLE main_employees MODIFY user_id bigint;
879ALTER TABLE main_empmedicalclaims MODIFY user_id bigint;
880ALTER TABLE main_emppersonaldetails MODIFY user_id bigint;
881ALTER TABLE main_empsalarydetails MODIFY user_id bigint;
882ALTER TABLE main_empvisadetails MODIFY user_id bigint;
883ALTER TABLE main_empvisadetails MODIFY createdby bigint;
884ALTER TABLE main_empvisadetails MODIFY modifiedby bigint;
885ALTER TABLE main_empworkeligibility MODIFY user_id bigint;
886ALTER TABLE main_emp_reporting MODIFY emp_id bigint;
887ALTER TABLE main_emp_reporting MODIFY reporting_manager_id bigint;
888ALTER TABLE main_hierarchylevels MODIFY parent bigint;
889ALTER TABLE main_hierarchylevels MODIFY userid bigint;
890ALTER TABLE main_logmanager MODIFY menuId bigint;
891ALTER TABLE main_logmanagercron MODIFY menuId bigint;
892ALTER TABLE main_pd_categories MODIFY modifiedby bigint;
893ALTER TABLE main_pd_categories MODIFY createdby bigint;
894ALTER TABLE main_pd_documents MODIFY category_id bigint;
895ALTER TABLE main_sd_reqtypes MODIFY service_desk_id bigint;
896ALTER TABLE main_sd_requests_summary MODIFY sd_requests_id bigint;
897ALTER TABLE main_weekdays MODIFY day_name bigint;
898ALTER TABLE main_candidatedetails MODIFY experience float;
899ALTER TABLE assets_categories MODIFY modified_by varchar(11);
900ALTER TABLE tm_projects MODIFY lead_approve_ts TINYINT;
901ALTER TABLE tbl_countries Modify country_code CHAR(10) CHARSET utf8 COLLATE utf8_general_ci NOT NULL;
902ALTER TABLE assets MODIFY isactive TINYINT;
903ALTER TABLE assets_categories MODIFY is_active TINYINT;
904ALTER TABLE main_pa_initialization MODIFY performance_app_flag TINYINT;
905ALTER TABLE main_pd_categories MODIFY isused TINYINT;
906ALTER TABLE main_pd_categories MODIFY isactive TINYINT;
907ALTER TABLE main_pd_documents MODIFY isactive TINYINT;
908ALTER TABLE main_settings MODIFY flag TINYINT;
909ALTER TABLE main_settings MODIFY isactive TINYINT;
910ALTER TABLE tbl_cities MODIFY is_active TINYINT;
911ALTER TABLE tbl_countries MODIFY is_active TINYINT;
912ALTER TABLE tbl_states MODIFY isactive TINYINT;
913ALTER TABLE tm_clients MODIFY is_active TINYINT;
914ALTER TABLE tm_emp_timesheets MODIFY cal_week TINYINT;
915ALTER TABLE tm_emp_timesheets MODIFY is_active TINYINT;
916ALTER TABLE tm_emp_ts_notes MODIFY cal_week TINYINT;
917ALTER TABLE tm_emp_ts_notes MODIFY is_active TINYINT;
918ALTER TABLE tm_projects MODIFY is_active TINYINT;
919ALTER TABLE tm_project_employees MODIFY is_active TINYINT;
920ALTER TABLE tm_project_tasks MODIFY is_active TINYINT;
921ALTER TABLE tm_project_task_employees MODIFY is_active TINYINT;
922ALTER TABLE tm_tasks MODIFY is_default TINYINT;
923ALTER TABLE tm_tasks MODIFY is_active TINYINT;
924ALTER TABLE tm_ts_status MODIFY is_active TINYINT;
925ALTER TABLE `assets` CHANGE `invoice_number` `invoice_number` VARCHAR(50) NULL;
926
927
928insert into `main_menu`(`id`,`menuName`,`url`,`helpText`,`toolTip`,`iconPath`,`parent`,`menuOrder`,`nav_ids`,`isactive`,`modulename`,`segment_flag`,`org_menuid`,`menufields`,`menuQuery`,`hasJoins`,`modelName`,`functionName`,`defaultOrderBy`) values(201,'Disciplinary','/#','','','',0,21,',21,',1,'default',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
929(202,'Violation Type','/disciplinaryviolation','','','',201,1,',201,202,',1,'default',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
930(203,'Raise An Incident','/disciplinaryincident','','','',201,2,',201,203,',1,'default',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
931(204,'My Incidents','/disciplinarymyincidents','','','',201,3,',201,204,',1,'default',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
932(205,'Team Incidents','/disciplinaryteamincidents','','','',201,4,',201,205,',1,'default',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
933(206,'All Incidents','/disciplinaryallincidents','','','',201,5,',201,206,',1,'default',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
934
935
936
937INSERT INTO `main_privileges` (`role`, `group_id`, `object`, `addpermission`, `editpermission`, `deletepermission`, `viewpermission`, `uploadattachments`, `viewattachments`, `createdby`, `modifiedby`, `createddate`, `modifieddate`, `isactive`) VALUES
938(1, NULL, 201, 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 1, 1, NOW(), NOW(), 1),
939(1, NULL, 202, 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 1, 1, NOW(), NOW(), 1),
940(1, NULL, 203, 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 1, 1, NOW(), NOW(), 1),
941(1, NULL, 204, 'No', 'Yes', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
942(1, NULL, 205, 'No', 'No', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
943(1, NULL, 206, 'No', 'No', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
944
945(NULL,1,201,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
946(NULL,1,202,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
947(NULL,1,203,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
948(NULL,1,204,'No','Yes','No','Yes','No','No',1,1,NOW(),NOW(),1),
949(NULL,1,205,'No','No','No','Yes','No','No',1,1,NOW(),NOW(),1),
950(NULL,1,206,'No','No','No','Yes','No','No',1,1,NOW(),NOW(),1),
951
952(NULL,2,201,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
953(NULL,2,204,'No','Yes','No','Yes','No','No',1,1,NOW(),NOW(),1),
954(NULL,2,205,'No','No','No','Yes','No','No',1,1,NOW(),NOW(),1),
955
956(NULL,3,201,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
957(NULL,3,204,'No','Yes','No','Yes','No','No',1,1,NOW(),NOW(),1),
958(NULL,3,205,'No','No','No','Yes','No','No',1,1,NOW(),NOW(),1),
959(NULL,3,206,'No','No','No','Yes','No','No',1,1,NOW(),NOW(),1),
960
961(NULL,4,201,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
962(NULL,4,204,'No','Yes','No','Yes','No','No',1,1,NOW(),NOW(),1),
963(NULL,4,205,'No','No','No','Yes','No','No',1,1,NOW(),NOW(),1),
964
965(NULL,6,201,'Yes','Yes','Yes','Yes','No','No',1,1,NOW(),NOW(),1),
966(NULL,6,204,'No','Yes','No','Yes','No','No',1,1,NOW(),NOW(),1),
967(NULL,6,205,'No','No','No','Yes','No','No',1,1,NOW(),NOW(),1),
968
969(2, 1, 201, 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 1, 1, NOW(), NOW(), 1),
970(2, 1, 202, 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 1, 1, NOW(), NOW(), 1),
971(2, 1, 203, 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 1, 1, NOW(), NOW(), 1),
972(2, 1, 204, 'No', 'Yes', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
973(2, 1, 205, 'No', 'No', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
974(2, 1, 206, 'No', 'No', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
975
976(3, 2, 201, 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 1, 1, NOW(), NOW(), 1),
977(3, 2, 204, 'No', 'Yes', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
978(3, 2, 205, 'No', 'No', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
979
980(4, 3, 201, 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 1, 1, NOW(), NOW(), 1),
981(4, 3, 204, 'No', 'Yes', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
982(4, 3, 205, 'No', 'No', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
983(4, 3, 206, 'No', 'No', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
984
985(5, 4, 201, 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 1, 1, NOW(), NOW(), 1),
986(5, 4, 204, 'No', 'Yes', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
987(5, 4, 205, 'No', 'No', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
988
989(8, 6, 201, 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 1, 1, NOW(), NOW(), 1),
990(8, 6, 204, 'No', 'Yes', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
991(8, 6, 205, 'No', 'No', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
992
993(9, 4, 201, 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 'Yes', 1, 1, NOW(), NOW(), 1),
994(9, 4, 204, 'No', 'Yes', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1),
995(9, 4, 205, 'No', 'No', 'No', 'Yes', 'No', 'No', 1, 1, NOW(), NOW(), 1);
996
997update `main_menu` set `parent`='0',`isactive`='0'where `id` IN(177,178,179,180,181);
998
999CREATE TABLE `main_disciplinary_violation_types` (
1000 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
1001 `violationname` varchar(255) NOT NULL,
1002 `description` varchar(255) DEFAULT NULL,
1003 `createdby` int(11) unsigned DEFAULT NULL,
1004 `modifiedby` int(11) unsigned DEFAULT NULL,
1005 `createddate` datetime DEFAULT NULL,
1006 `modifieddate` datetime DEFAULT NULL,
1007 `isactive` tinyint(1) unsigned DEFAULT '1',
1008 PRIMARY KEY (`id`)
1009) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1010
1011
1012CREATE TABLE `main_disciplinary_incident` (
1013`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
1014`incident_raised_by` bigint(20) unsigned DEFAULT NULL,
1015`employee_bu_id` bigint(20) unsigned DEFAULT NULL,
1016`employee_dept_id` bigint(20) unsigned DEFAULT NULL,
1017`employee_id` bigint(20) unsigned DEFAULT NULL,
1018`employee_rep_mang_id` bigint(20) unsigned DEFAULT NULL,
1019
1020`date_of_occurrence` date DEFAULT NULL,
1021
1022`violation_id` bigint(20) unsigned DEFAULT NULL,
1023`violation_expiry` date DEFAULT NULL,
1024`employee_job_title_id` bigint(20) unsigned DEFAULT NULL,
1025`employer_statement` text,
1026`employee_appeal` tinyint(1) DEFAULT '1' COMMENT '1=Yes,2=No',
1027`employee_statement` text,
1028`cao_verdict` tinyint(1) DEFAULT '1' COMMENT '1=guilty,2=not guilty',
1029`corrective_action` enum('Suspension With Pay','Suspension W/O Pay','Termination','Other') DEFAULT 'Suspension With Pay',
1030`corrective_action_text` varchar(255) DEFAULT NULL,
1031`incident_status` enum('Initiated','Appealed','Closed') DEFAULT 'Initiated',
1032`createdby` bigint(20) unsigned DEFAULT NULL,
1033`modifiedby` bigint(20) unsigned DEFAULT NULL,
1034`createddate` datetime DEFAULT NULL,
1035`modifieddate` datetime DEFAULT NULL,
1036`isactive` tinyint(1) DEFAULT '1',
1037PRIMARY KEY (`id`)
1038) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1039
1040
1041CREATE TABLE `main_disciplinary_history` (
1042 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
1043 `incident_id` bigint(20) unsigned DEFAULT NULL,
1044 `description` varchar(300) DEFAULT NULL,
1045 `action_emp_id` bigint(20) unsigned DEFAULT NULL,
1046 `createdby` bigint(20) unsigned DEFAULT NULL,
1047 `createddate` datetime DEFAULT NULL,
1048 PRIMARY KEY (`id`)
1049) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1050
1051
1052DELIMITER $$
1053
1054DROP TRIGGER `main_users_aft_upd`$$
1055
1056CREATE
1057
1058 TRIGGER `main_users_aft_upd` AFTER UPDATE ON `main_users`
1059 FOR EACH ROW BEGIN
1060 declare groupid int(11);
1061
1062 select group_id into groupid from main_roles where id = old.emprole;
1063 if old.userfullname != new.userfullname then
1064 begin
1065
1066 if (groupid != 5 or groupid is null) then
1067 begin
1068
1069 update main_leaverequest_summary set rep_manager_name = new.userfullname,modifieddate = utc_timestamp() where rep_mang_id = new.id and isactive = 1;
1070 update main_leaverequest_summary set user_name = new.userfullname,modifieddate = utc_timestamp() where user_id = new.id and isactive = 1;
1071
1072
1073 update main_requisition_summary set reporting_manager_name = new.userfullname,modifiedon = utc_timestamp() where reporting_id = new.id and isactive = 1;
1074 update main_requisition_summary set approver1_name = new.userfullname,modifiedon = utc_timestamp() where approver1 = new.id and isactive = 1;
1075 update main_requisition_summary set approver2_name = new.userfullname,modifiedon = utc_timestamp() where approver2 = new.id and isactive = 1;
1076 update main_requisition_summary set approver3_name = new.userfullname,modifiedon = utc_timestamp() where approver3 = new.id and isactive = 1;
1077 update main_requisition_summary set createdby_name = new.userfullname,modifiedon = utc_timestamp() where createdby = new.id and isactive = 1;
1078
1079
1080 update main_employees_summary set reporting_manager_name = new.userfullname,modifieddate = utc_timestamp() where reporting_manager = new.id and isactive = 1;
1081 update main_employees_summary set referer_name = new.userfullname,modifieddate = utc_timestamp() where candidatereferredby = new.id and isactive = 1;
1082 update main_employees_summary set createdby_name = new.userfullname,modifieddate = utc_timestamp() where createdby = new.id and isactive = 1;
1083 update main_employees_summary set userfullname = new.userfullname,modifieddate = utc_timestamp() where user_id = new.id and isactive = 1;
1084
1085
1086 update main_bgchecks_summary set specimen_name = new.userfullname,modifieddate = utc_timestamp() where specimen_id = new.id and specimen_flag = 1 and isactive = 1;
1087 update main_bgchecks_summary set createdname = new.userfullname,modifieddate = utc_timestamp() where createdby = new.id and isactive = 1;
1088 update main_bgchecks_summary set modifiedname = new.userfullname,modifieddate = utc_timestamp() where modifiedby = new.id and isactive = 1;
1089
1090
1091 update main_interviewrounds_summary set interviewer_name = new.userfullname,modified_date = utc_timestamp() where interviewer_id = new.id and isactive = 1;
1092 update main_interviewrounds_summary set created_by_name = new.userfullname,modified_date = utc_timestamp() where created_by = new.id and isactive = 1;
1093
1094
1095 update main_userloginlog set userfullname = new.userfullname where userid = new.id;
1096
1097
1098 update main_sd_requests_summary set raised_by_name = new.userfullname,modifieddate = utc_timestamp() where raised_by = new.id;
1099 update main_sd_requests_summary set executor_name = new.userfullname,modifieddate = utc_timestamp() where executor_id = new.id;
1100 update main_sd_requests_summary set reporting_manager_name = new.userfullname,modifieddate = utc_timestamp() where reporting_manager_id = new.id;
1101 update main_sd_requests_summary set approver_1_name = new.userfullname,modifieddate = utc_timestamp() where approver_1 = new.id;
1102 update main_sd_requests_summary set approver_2_name = new.userfullname,modifieddate = utc_timestamp() where approver_2 = new.id;
1103 update main_sd_requests_summary set approver_3_name = new.userfullname,modifieddate = utc_timestamp() where approver_3 = new.id;
1104
1105 end;
1106 end if;
1107 end;
1108 end if;
1109 if old.employeeId != new.employeeId then
1110 begin
1111 if (groupid != 5 or groupid is null) then
1112 begin
1113
1114 update main_employees_summary set employeeId = new.employeeId,modifieddate = utc_timestamp() where user_id = new.id;
1115
1116 end;
1117 end if;
1118 end;
1119 end if;
1120 if old.isactive != new.isactive then
1121 begin
1122 if (groupid != 5 or groupid is null) then
1123 begin
1124
1125 update main_employees_summary set isactive = new.isactive,modifieddate = utc_timestamp() where user_id = new.id;
1126
1127 end;
1128 end if;
1129 end;
1130 end if;
1131 if old.profileimg != new.profileimg then
1132 begin
1133 if (groupid != 5 or groupid is null) then
1134 begin
1135
1136 update main_employees_summary set profileimg = new.profileimg,modifieddate = utc_timestamp() where user_id = new.id;
1137
1138
1139 update main_request_history set emp_profileimg = new.profileimg,modifieddate = utc_timestamp() where emp_id = new.id;
1140
1141 end;
1142 end if;
1143 end;
1144 end if;
1145 if old.backgroundchk_status != new.backgroundchk_status then
1146 begin
1147 if (groupid != 5 or groupid is null) then
1148 begin
1149
1150 update main_employees_summary set backgroundchk_status = new.backgroundchk_status,modifieddate = utc_timestamp() where user_id = new.id;
1151
1152 end;
1153 end if;
1154 end;
1155 end if;
1156if (old.contactnumber != new.contactnumber || new.contactnumber IS NOT NULL) then
1157 begin
1158 if (groupid != 5 or groupid is null) then
1159 begin
1160
1161 update main_employees_summary set contactnumber = new.contactnumber,modifieddate = utc_timestamp() where user_id = new.id;
1162
1163 end;
1164 end if;
1165 end;
1166 end if;
1167
1168 END;
1169$$
1170
1171DELIMITER ;
1172
1173
1174UPDATE main_patches_version SET isactive=0;
1175
1176INSERT INTO main_patches_version (version, createddate, modifieddate, isactive) VALUES ("3.0", now(), now(),1);