|
1
|
use salarymarket ;
|
|
2
|
CREATE TABLE company_reports_authorization (
|
|
3
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
4
|
report_generated INT NOT NULL,
|
|
5
|
report_authorized VARCHAR(255) NOT NULL,
|
|
6
|
companyId INT NOT NULL,
|
|
7
|
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
8
|
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
9
|
FOREIGN KEY (companyId) REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE);
|
|
10
|
ALTER TABLE company
|
|
11
|
DROP COLUMN logo ;
|
|
12
|
ALTER TABLE company
|
|
13
|
ADD COLUMN actif boolean NOT NULL;
|
|
14
|
ALTER TABLE solution_setting_country
|
|
15
|
ADD COLUMN round INT;
|
|
16
|
UPDATE solution_setting_country
|
|
17
|
SET round = 3
|
|
18
|
WHERE code = 'TN';
|
|
19
|
UPDATE solution_setting_country
|
|
20
|
SET round = 2
|
|
21
|
WHERE code = 'FR';
|
|
22
|
CREATE TABLE company_activities_translate (
|
|
23
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
24
|
description VARCHAR(255),
|
|
25
|
activityId INT NOT NULL,
|
|
26
|
languageId INT NOT NULL,
|
|
27
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
28
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
29
|
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
30
|
FOREIGN KEY (activityId) REFERENCES company_activities(id) ON DELETE CASCADE ON UPDATE CASCADE
|
|
31
|
);
|
|
32
|
|
|
33
|
alter table company_compensation DROP COLUMN annual_cost_car_depreciation;
|
|
34
|
-- fix translate tag language
|
|
35
|
UPDATE solution_setting_business_sector_translate
|
|
36
|
SET languageId = 1
|
|
37
|
WHERE businessSectorCode = 'BS04'
|
|
38
|
AND label = 'Industrial market';
|
|
39
|
|
|
40
|
INSERT INTO company_reports_authorization (companyId, report_generated, report_authorized)
|
|
41
|
SELECT
|
|
42
|
c.id AS companyId,
|
|
43
|
COUNT(cr.id) AS report_generated,
|
|
44
|
CASE
|
|
45
|
WHEN c.subscription_type = 'Advanced' THEN 'Illimited'
|
|
46
|
ELSE '1'
|
|
47
|
END AS report_authorized
|
|
48
|
FROM company c
|
|
49
|
LEFT JOIN company_reports cr ON cr.companyId = c.id
|
|
50
|
WHERE c.id NOT IN (
|
|
51
|
SELECT companyId FROM company_reports_authorization
|
|
52
|
)
|
|
53
|
GROUP BY c.id, c.subscription_type;
|
|
54
|
|
|
55
|
|
|
56
|
use salarymarket;
|
|
57
|
INSERT INTO company_activities_translate (description, activityId, languageId)
|
|
58
|
SELECT
|
|
59
|
ca.description,
|
|
60
|
ca.id AS activityId,
|
|
61
|
2 AS languageId -- Français
|
|
62
|
FROM company_activities ca
|
|
63
|
WHERE ca.description IS NOT NULL ;
|
|
64
|
|
|
65
|
INSERT INTO company_activities_translate (description, activityId, languageId)
|
|
66
|
VALUES
|
|
67
|
('Ibtihel HR Group from HR House International deleted a user Eya HR Group.', 1, 1),
|
|
68
|
('Tarek HR Group of HR House International created a company MS Solutions.', 2, 1),
|
|
69
|
('Tarek HR Group of HR House International created a profile.', 3, 1),
|
|
70
|
('Ibtihel HR Group from HR House International sent an email to Henda Msaed.', 4, 1),
|
|
71
|
('Ibtihel HR Group of HR House International validated an MS Solutions company.', 5, 1),
|
|
72
|
('Tarek HR Group of HR House International created a company Telnet Groupe.', 6, 1),
|
|
73
|
('Tarek HR Group of HR House International created a profile.', 7, 1),
|
|
74
|
('Tarek HR Group of HR House International sent an email to Yousra ZIADA.', 8, 1),
|
|
75
|
('Tarek HR Group of HR House International validated a Telnet Group company.', 9, 1),
|
|
76
|
('Ahlem HR Group from HR House International sent an email to Sabrine Trabelsi.', 10, 1),
|
|
77
|
('Ahlem HR Group from HR House International created a user Sabrine Trabelsi.', 11, 1);
|