Eb8drsQJ

1drop table if exists ATG_MAPPING_TOOL_LOGS;
2create table ATG_MAPPING_TOOL_LOGS (
3SESSION_ID VARCHAR(255) NOT NULL,
4MESSAGE VARCHAR(512) NOT NULL,
5HOTEL_CODE VARCHAR(128) NULL,
6CITY_CODE VARCHAR(128) NULL,
7CODE_CONTEXT VARCHAR(16) NULL,
8OLD_VALUE TEXT NULL,
9NEW_VALUE TEXT NULL,
10EVENT_TIMESTAMP TIMESTAMP default CURRENT_TIMESTAMP
11) ENGINE=InnoDB DEFAULT CHARSET=utf8;
12
13drop table if exists ATG_MAPPING_TOOL_HOTEL_LAST_UPDATE_LOGS;
14create table ATG_MAPPING_TOOL_HOTEL_LAST_UPDATE_LOGS (
15HOTEL_ID bigint(20) NOT NULL ,
16HOTEL_CODE VARCHAR(512) NOT NULL,
17CITY_CODE VARCHAR(128) NULL,
18CODE_CONTEXT VARCHAR(16) NULL,
19USER_NAME VARCHAR(255) NULL,
20UPDATE_TIMESTAMP TIMESTAMP default CURRENT_TIMESTAMP,
21UNIQUE KEY (HOTEL_ID),
22CONSTRAINT `FK_MP_LAST_UPDATE_HOTEL` FOREIGN KEY (`HOTEL_ID`) REFERENCES `GPT_HOTEL` (`ID`) ON DELETE CASCADE
23) ENGINE=InnoDB DEFAULT CHARSET=utf8;
24
25drop table if exists ATG_MAPPING_TOOL_EXCURSION_LAST_UPDATE_LOGS;
26create table ATG_MAPPING_TOOL_EXCURSION_LAST_UPDATE_LOGS (
27EXCURSION_ID bigint(20) NOT NULL,
28EXCURSION_CODE VARCHAR(512) NOT NULL,
29CITY_CODE VARCHAR(128) NULL,
30CODE_CONTEXT VARCHAR(16) NULL,
31USER_NAME VARCHAR(255) NULL,
32UPDATE_TIMESTAMP TIMESTAMP default CURRENT_TIMESTAMP,
33UNIQUE KEY (EXCURSION_ID),
34CONSTRAINT `FK_MP_LAST_UPDATE_EXCURSION` FOREIGN KEY (`EXCURSION_ID`) REFERENCES `GPT_HOTEL` (`ID`) ON DELETE CASCADE
35) ENGINE=InnoDB DEFAULT CHARSET=utf8;
36
37
38drop procedure if exists castIntToBit;
39DELIMITER //
40
41CREATE FUNCTION castIntToBit (N INT) RETURNS bit(1)
42BEGIN
43    RETURN N;
44END //
45DELIMITER ;
46
47
48drop procedure if exists addLogMappingTool;
49DELIMITER // 
50CREATE PROCEDURE `addLogMappingTool` (sessionId varchar(255), message varchar(512), hotelCode varchar(128), cityCode varchar(128), codeContext varchar(16), oldValue text, newValue text) 
51BEGIN 
52
53SET @oldValue=COALESCE(oldValue, '');
54
55insert into ATG_MAPPING_TOOL_LOGS (SESSION_ID, MESSAGE, HOTEL_CODE, CITY_CODE, CODE_CONTEXT, OLD_VALUE, NEW_VALUE) VALUES (sessionId, message, hotelCode, cityCode, codeContext, @oldValue, newValue);
56
57END //
58DELIMITER ;
59
60
61drop procedure if exists HotelLastUpdateTimeMappingTool;
62DELIMITER // 
63CREATE PROCEDURE `HotelLastUpdateTimeMappingTool` (hotelCode varchar(128), cityCode varchar(128), codeContext varchar(16), timestampUpdate timestamp)
64BEGIN 
65
66 DECLARE hotelId bigint(20);   
67 
68 SELECT h.ID INTO hotelId FROM GPT_HOTEL h join GPT_SUPPLIER s on s.ID=h.SUPPLIER_ID join GPT_LOCATION_CODE lc on lc.LOCATION_ID=h.PARENT where h.SUPPLIER_SPECIFIC_CODE=hotelCode and s.SUPPLIER_CODE=codeContext and lc.CODE_VALUE=cityCode;
69
70 insert into ATG_MAPPING_TOOL_HOTEL_LAST_UPDATE_LOGS (HOTEL_ID, HOTEL_CODE, CITY_CODE, CODE_CONTEXT, UPDATE_TIMESTAMP) VALUES (hotelId, hotelCode, cityCode, codeContext, timestampUpdate) ON DUPLICATE KEY UPDATE UPDATE_TIMESTAMP=timestampUpdate;
71
72END //
73DELIMITER ;
74
75drop procedure if exists HotelLastUpdateUserMappingTool;
76DELIMITER // 
77CREATE PROCEDURE `HotelLastUpdateUserMappingTool` (hotelCode varchar(128), cityCode varchar(128), codeContext varchar(16), userName varchar(255))
78BEGIN 
79
80 DECLARE hotelId bigint(20);   
81 
82 SELECT h.ID INTO hotelId FROM GPT_HOTEL h join GPT_SUPPLIER s on s.ID=h.SUPPLIER_ID join GPT_LOCATION_CODE lc on lc.LOCATION_ID=h.PARENT where h.SUPPLIER_SPECIFIC_CODE=hotelCode and s.SUPPLIER_CODE=codeContext and lc.CODE_VALUE=cityCode;
83
84 insert into ATG_MAPPING_TOOL_HOTEL_LAST_UPDATE_LOGS (HOTEL_ID, HOTEL_CODE, CITY_CODE, CODE_CONTEXT, USER_NAME) VALUES (hotelId, hotelCode, cityCode, codeContext, userName) ON DUPLICATE KEY UPDATE USER_NAME=userName;
85
86END //
87DELIMITER ;
88
89
90drop procedure if exists ExcursionLastUpdateUserMappingTool;
91DELIMITER // 
92CREATE PROCEDURE `ExcursionLastUpdateUserMappingTool` (excursionCode varchar(128), cityCode varchar(128), codeContext varchar(16), userName varchar(255))
93BEGIN 
94
95 DECLARE excursionId bigint(20);   
96 
97 SELECT h.ID INTO excursionId FROM GPT_EXCURSION h join GPT_SUPPLIER s on s.ID=h.SUPPLIER_ID join GPT_LOCATION_CODE lc on lc.LOCATION_ID=h.PARENT where h.SUPPLIER_SPECIFIC_CODE=excursionCode and s.SUPPLIER_CODE=codeContext and lc.CODE_VALUE=cityCode;
98
99 insert into ATG_MAPPING_TOOL_EXCURSION_LAST_UPDATE_LOGS (HOTEL_ID, HOTEL_CODE, CITY_CODE, CODE_CONTEXT, USER_NAME) VALUES (excursionId, excursionCode, cityCode, codeContext, userName) ON DUPLICATE KEY UPDATE USER_NAME=userName;
100
101END //
102DELIMITER ;
103
104
105
106drop procedure if exists ExcursionLastUpdateTimeMappingTool;
107DELIMITER // 
108CREATE PROCEDURE `ExcursionLastUpdateTimeMappingTool` (excursionCode varchar(128), cityCode varchar(128), codeContext varchar(16), timestampUpdate timestamp)
109BEGIN 
110
111 DECLARE excursionId bigint(20);   
112 
113 SELECT h.ID INTO excursionId FROM GPT_EXCURSION h join GPT_SUPPLIER s on s.ID=h.SUPPLIER_ID join GPT_LOCATION_CODE lc on lc.LOCATION_ID=h.PARENT where h.SUPPLIER_SPECIFIC_CODE=excursionCode and s.SUPPLIER_CODE=codeContext and lc.CODE_VALUE=cityCode;
114
115 insert into ATG_MAPPING_TOOL_EXCURSION_LAST_UPDATE_LOGS (HOTEL_ID, HOTEL_CODE, CITY_CODE, CODE_CONTEXT, UPDATE_TIMESTAMP) VALUES (excursionId, excursionCode, cityCode, codeContext, timestampUpdate) ON DUPLICATE KEY UPDATE UPDATE_TIMESTAMP=timestampUpdate;
116
117END //
118DELIMITER ;