yMyhgECR

1alter table GPT_HOTEL add column FROZEN bit(1) default b'0';
2alter table GPT_HOTEL_LOCALIZED add column FROZEN bit(1) default b'0';
3alter table GPT_HOTEL_GROUP add column ACTIVE bit(1) default b'1';
4alter table GPT_EXCURSION add column FROZEN bit(1) default b'0';
5alter table GPT_EXCURSION_GROUP add column ACTIVE bit(1) default b'1';
6
7drop table if exists ATG_MAPPING_TOOL_LOGS;
8create table ATG_MAPPING_TOOL_LOGS (
9SESSION_ID VARCHAR(255) NOT NULL,
10MESSAGE VARCHAR(512) NOT NULL,
11HOTEL_CODE VARCHAR(128) NULL,
12CITY_CODE VARCHAR(128) NULL,
13CODE_CONTEXT VARCHAR(16) NULL,
14OLD_VALUE TEXT NULL,
15NEW_VALUE TEXT NULL,
16EVENT_TIMESTAMP TIMESTAMP default CURRENT_TIMESTAMP
17) ENGINE=InnoDB DEFAULT CHARSET=utf8;
18
19drop table if exists ATG_MAPPING_TOOL_HOTEL_LAST_UPDATE_LOGS;
20create table ATG_MAPPING_TOOL_HOTEL_LAST_UPDATE_LOGS (
21HOTEL_ID bigint(20) NOT NULL ,
22HOTEL_CODE VARCHAR(512) NOT NULL,
23CITY_CODE VARCHAR(128) NULL,
24CODE_CONTEXT VARCHAR(16) NULL,
25USER_NAME VARCHAR(255) NULL,
26UPDATE_TIMESTAMP INT(10) Unsigned,
27UNIQUE KEY (HOTEL_ID),
28CONSTRAINT `FK_MP_LAST_UPDATE_HOTEL` FOREIGN KEY (`HOTEL_ID`) REFERENCES `GPT_HOTEL` (`ID`) ON DELETE CASCADE
29) ENGINE=InnoDB DEFAULT CHARSET=utf8;
30
31drop table if exists ATG_MAPPING_TOOL_EXCURSION_LAST_UPDATE_LOGS;
32create table ATG_MAPPING_TOOL_EXCURSION_LAST_UPDATE_LOGS (
33EXCURSION_ID bigint(20) NOT NULL,
34EXCURSION_CODE VARCHAR(512) NOT NULL,
35CITY_CODE VARCHAR(128) NULL,
36CODE_CONTEXT VARCHAR(16) NULL,
37USER_NAME VARCHAR(255) NULL,
38UPDATE_TIMESTAMP INT(10) Unsigned,
39UNIQUE KEY (EXCURSION_ID),
40CONSTRAINT `FK_MP_LAST_UPDATE_EXCURSION` FOREIGN KEY (`EXCURSION_ID`) REFERENCES `GPT_HOTEL` (`ID`) ON DELETE CASCADE
41) ENGINE=InnoDB DEFAULT CHARSET=utf8;
42
43
44drop procedure if exists castIntToBit;
45DELIMITER //
46
47CREATE FUNCTION castIntToBit (N INT) RETURNS bit(1)
48BEGIN
49    RETURN N;
50END //
51DELIMITER ;
52
53
54drop procedure if exists addLogMappingTool;
55DELIMITER // 
56CREATE PROCEDURE `addLogMappingTool` (sessionId varchar(255), message varchar(512), hotelCode varchar(128), cityCode varchar(128), codeContext varchar(16), oldValue text, newValue text) 
57BEGIN 
58
59SET @oldValue=COALESCE(oldValue, '');
60
61insert 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);
62
63END //
64DELIMITER ;
65
66
67drop procedure if exists HotelLastUpdateTimeMappingTool;
68DELIMITER // 
69CREATE PROCEDURE `HotelLastUpdateTimeMappingTool` (hotelCode varchar(128), codeContext varchar(16), cityCode varchar(128), timestampUpdate timestamp)
70BEGIN 
71
72 DECLARE hotelId bigint(20);   
73 
74 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;
75
76 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;
77
78END //
79DELIMITER ;
80
81drop procedure if exists HotelLastUpdateUserMappingTool;
82DELIMITER // 
83CREATE PROCEDURE `HotelLastUpdateUserMappingTool` (hotelCode varchar(128), codeContext varchar(16), cityCode varchar(128), userName varchar(255))
84BEGIN 
85
86 DECLARE hotelId bigint(20);   
87 
88 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;
89
90 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;
91
92END //
93DELIMITER ;
94
95
96drop procedure if exists ExcursionLastUpdateUserMappingTool;
97DELIMITER // 
98CREATE PROCEDURE `ExcursionLastUpdateUserMappingTool` (excursionCode varchar(128), codeContext varchar(16), cityCode varchar(128), userName varchar(255))
99BEGIN 
100
101 DECLARE excursionId bigint(20);   
102 
103 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;
104
105 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;
106
107END //
108DELIMITER ;
109
110
111
112drop procedure if exists ExcursionLastUpdateTimeMappingTool;
113DELIMITER // 
114CREATE PROCEDURE `ExcursionLastUpdateTimeMappingTool` (excursionCode varchar(128), codeContext varchar(16), cityCode varchar(128), timestampUpdate timestamp)
115BEGIN 
116
117 DECLARE excursionId bigint(20);   
118 
119 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;
120
121 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;
122
123END //
124DELIMITER ;
125
126drop procedure if exists modifyHotelNameMappingTool;
127DELIMITER // 
128CREATE PROCEDURE `modifyHotelNameMappingTool` (newValue varchar(500), hotelCode varchar(255), codeContext varchar(16), cityCode varchar(64), sessionId varchar(255), langCode varchar(64)) 
129
130BEGIN 
131
132 DECLARE hotelId bigint(20);   
133 DECLARE oldValue VARCHAR(500);
134 DECLARE langID tinyint(3);
135
136 SELECT ID INTO langID FROM GPT_LANGUAGE where CODE=langCode;
137
138 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;
139
140 SELECT NAME INTO oldValue FROM GPT_HOTEL_NAME where HOTEL_ID = hotelId and LANG_ID=langID;
141 
142 insert into GPT_HOTEL_NAME (HOTEL_ID, LANG_ID, NAME) VALUES (hotelId, langID, newValue)  ON DUPLICATE KEY UPDATE NAME=newValue;
143 SET @oldValue=COALESCE(oldValue, '');
144 call addLogMappingTool(sessionId, 'Update hotel name', hotelCode, cityCode, codeContext, @oldValue, newValue);
145END // 
146
147DELIMITER ;
148
149
150drop procedure if exists modifyHotelAddressMappingTool;
151DELIMITER // 
152CREATE PROCEDURE `modifyHotelAddressMappingTool` (newValue varchar(255), hotelCode varchar(255), codeContext varchar(16), cityCode varchar(64), sessionId varchar(255), langCode varchar(64)) 
153BEGIN 
154 DECLARE hotelId bigint(20);   
155 DECLARE oldValue VARCHAR(255);
156 DECLARE langID tinyint(3);
157
158 SELECT ID INTO langID FROM GPT_LANGUAGE where CODE=langCode;
159 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;
160 SELECT ADDRESS INTO oldValue FROM GPT_HOTEL_ADDRESS where HOTEL_ID= hotelId;
161
162 insert into GPT_HOTEL_ADDRESS (HOTEL_ID, LANG_ID, ADDRESS) VALUES (hotelId, langID, newValue) ON DUPLICATE KEY UPDATE ADDRESS=newValue ;
163 SET @oldValue=COALESCE(oldValue, '');
164 call addLogMappingTool(sessionId, 'Update Hotel Address', hotelCode, cityCode, codeContext, @oldValue, newValue);
165
166END //
167DELIMITER ;
168
169
170drop procedure if exists modifyHotelZipCodeMappingTool;
171DELIMITER // 
172CREATE PROCEDURE `modifyHotelZipCodeMappingTool` (newValue varchar(50), hotelCode varchar(255), codeContext varchar(16), cityCode varchar(64), sessionId varchar(255)) 
173BEGIN 
174 DECLARE hotelId bigint(20);   
175 DECLARE oldValue VARCHAR(255);
176 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;
177 SELECT ZIP INTO oldValue FROM GPT_HOTEL where ID= hotelId;
178 SET @oldValue=COALESCE(oldValue, '');
179 update GPT_HOTEL set ZIP = newValue where ID= hotelId;
180 call addLogMappingTool(sessionId, 'Update Hotel ZIP code', hotelCode, cityCode, codeContext, @oldValue, newValue);
181
182END //
183DELIMITER ;
184
185
186drop procedure if exists modifyHotelPhoneCodeMappingTool;
187DELIMITER // 
188CREATE PROCEDURE `modifyHotelPhoneCodeMappingTool` (newValue varchar(50), hotelCode varchar(255), codeContext varchar(16), cityCode varchar(64), sessionId varchar(255)) 
189BEGIN 
190 DECLARE hotelId bigint(20);   
191 DECLARE oldValue VARCHAR(255);
192
193 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;
194 SELECT PHONE INTO oldValue FROM GPT_HOTEL where ID= hotelId;
195 update GPT_HOTEL set PHONE= newValue where ID= hotelId;
196 SET @oldValue=COALESCE(oldValue, '');
197 call addLogMappingTool(sessionId, 'Update Hotel Phone', hotelCode, cityCode, codeContext, @oldValue, newValue);
198
199END //
200DELIMITER ;
201
202
203drop procedure if exists modifyHotelPhone2CodeMappingTool;
204DELIMITER // 
205CREATE PROCEDURE `modifyHotelPhone2CodeMappingTool` (newValue varchar(50), hotelCode varchar(255), codeContext varchar(16), cityCode varchar(64), sessionId varchar(255)) 
206BEGIN 
207 DECLARE hotelId bigint(20);   
208 DECLARE oldValue VARCHAR(255);
209 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;
210 SELECT PHONE2 INTO oldValue FROM GPT_HOTEL where ID= hotelId;
211 update GPT_HOTEL set PHONE2= newValue where ID= hotelId;
212 SET @oldValue=COALESCE(oldValue, '');
213 call addLogMappingTool(sessionId, 'Update Hotel Phone2', hotelCode, cityCode, codeContext, @oldValue, newValue);
214END //
215DELIMITER ;
216
217
218drop procedure if exists modifyHotelPhone3CodeMappingTool;
219DELIMITER // 
220CREATE PROCEDURE `modifyHotelPhone3CodeMappingTool` (newValue varchar(50), hotelCode varchar(255), codeContext varchar(16), cityCode varchar(64), sessionId varchar(255)) 
221BEGIN 
222 DECLARE hotelId bigint(20);   
223 DECLARE oldValue VARCHAR(255);
224 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;
225 SELECT PHONE3 INTO oldValue FROM GPT_HOTEL where ID= hotelId;
226 update GPT_HOTEL set PHONE3= newValue where ID = hotelId;
227 SET @oldValue=COALESCE(oldValue, '');
228 call addLogMappingTool(sessionId, 'Update Hotel Phone3', hotelCode, cityCode, codeContext, @oldValue, newValue);
229
230END //
231DELIMITER ;
232
233drop procedure if exists modifyHotelRatingIdMappingTool;
234DELIMITER // 
235CREATE PROCEDURE `modifyHotelCategoryIdMappingTool` (newValue bigint(20), hotelCode varchar(255), codeContext varchar(16), cityCode varchar(64), sessionId varchar(255)) 
236BEGIN 
237 DECLARE hotelId bigint(20);   
238 DECLARE oldValue VARCHAR(255);
239 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;
240 SELECT CATEGORY_ID INTO oldValue FROM GPT_HOTEL where ID= hotelId;
241 update GPT_HOTEL set CATEGORY_ID= newValue where ID= hotelId;
242 SET @oldValue=COALESCE(oldValue, '');
243 call addLogMappingTool(sessionId, 'Update CategoryID', hotelCode, cityCode, codeContext, @oldValue, newValue);
244
245END //
246DELIMITER ;
247
248
249drop procedure if exists modifyHotelFrozenFlagMappingTool;
250
251DELIMITER // 
252CREATE PROCEDURE `modifyHotelFrozenFlagMappingTool` (newValue int(1), hotelCode varchar(255), codeContext varchar(16), cityCode varchar(64), sessionId varchar(255)) 
253BEGIN
254 DECLARE hotelId bigint(20);   
255 DECLARE oldValue VARCHAR(255);
256 DECLARE flag bit(1);
257
258 set flag=castIntToBit(newValue);
259
260 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;
261 SELECT FROZEN INTO oldValue FROM GPT_HOTEL where ID= hotelId;
262
263 update GPT_HOTEL set FROZEN= flag where ID= hotelId;
264 
265 SET @oldValue=COALESCE(oldValue, '');
266 call addLogMappingTool(sessionId, 'Update Hotel Frozen', hotelCode, cityCode, codeContext, @oldValue, newValue);
267 
268END //
269DELIMITER ;
270
271
272drop procedure if exists modifyHotelActiveFlagMappingTool;
273
274DELIMITER // 
275CREATE PROCEDURE `modifyHotelActiveFlagMappingTool` (newValue int(1), hotelCode varchar(255), codeContext varchar(16), cityCode varchar(64), sessionId varchar(255)) 
276BEGIN
277 DECLARE hotelId bigint(20);   
278 DECLARE oldValue VARCHAR(255);
279 DECLARE flag bit(1);
280
281 set flag=castIntToBit(newValue);
282 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;
283
284 SELECT ACTIVE INTO oldValue FROM GPT_HOTEL where ID= hotelId;
285
286 update GPT_HOTEL set ACTIVE = flag where ID= hotelId;
287 
288 SET @oldValue=COALESCE(oldValue, '');
289 call addLogMappingTool(sessionId, 'Update Hotel Active flag', hotelCode, cityCode, codeContext, @oldValue, newValue);
290
291END //
292DELIMITER ;
293
294
295drop procedure if exists modifyGroupActiveFlagMappingTool;
296
297DELIMITER // 
298CREATE PROCEDURE `modifyGroupActiveFlagMappingTool` (newValue int(1), hotelCode varchar(255), codeContext varchar(16), cityCode varchar(64), sessionId varchar(255)) 
299BEGIN
300 DECLARE hotelId bigint(20);   
301 DECLARE oldValue VARCHAR(255);
302 DECLARE flag bit(1);
303
304 set flag=castIntToBit(newValue);
305 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;
306 SELECT ACTIVE INTO oldValue FROM GPT_HOTEL where ID= hotelId;
307 update GPT_HOTEL_GROUP set ACTIVE= flag where HOTEL_ID= hotelId;
308 SET @oldValue=COALESCE(oldValue, '');
309 call addLogMappingTool(sessionId, 'Update Group Active flag', hotelCode, cityCode, codeContext, @oldValue, newValue);
310END //
311DELIMITER ;
312
313
314
315drop procedure if exists modifyExcursionNameMappingTool;
316DELIMITER // 
317CREATE PROCEDURE `modifyExcursionNameMappingTool` (newValue varchar(500), excursionCode varchar(255), codeContext varchar(16), cityCode varchar(64), sessionId varchar(255), langCode varchar(64)) 
318
319BEGIN 
320
321DECLARE excursionId bigint(20);   
322DECLARE oldValue VARCHAR(500);
323DECLARE langID tinyint(3);
324
325SELECT ID INTO langID FROM GPT_LANGUAGE where CODE=langCode;
326SELECT 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;
327SELECT NAME INTO oldValue FROM GPT_EXCURSION_NAME where EXCURSION_ID = excursionId and LANG_ID=langID;
328
329insert into GPT_EXCURSION_NAME (EXCURSION_ID, LANG_ID, NAME) VALUES (excursionId, langID, newValue) ON DUPLICATE KEY UPDATE NAME=newValue;
330SET @oldValue=COALESCE(oldValue, '');
331
332call addLogMappingTool(sessionId, 'Update Excursion name', excursionCode, cityCode, codeContext, @oldValue, newValue);
333
334END // 
335DELIMITER ;
336
337
338drop procedure if exists modifyExcursionFrozenFlagMappingTool;
339
340DELIMITER // 
341CREATE PROCEDURE `modifyExcursionFrozenFlagMappingTool` (newValue int(1), excursionCode varchar(255), codeContext varchar(16), cityCode varchar(64), sessionId varchar(255)) 
342BEGIN
343DECLARE excursionId bigint(20);   
344DECLARE oldValue VARCHAR(255);
345DECLARE flag bit(1);
346
347set flag=castIntToBit(newValue);
348
349SELECT 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;
350SELECT FROZEN INTO oldValue FROM GPT_EXCURSION where ID= excursionId;
351
352update GPT_EXCURSION set FROZEN= flag where ID= excursionId;
353SET @oldValue=COALESCE(oldValue, '');
354
355call addLogMappingTool(sessionId, 'Update Excursion Frozen', excursionCode, cityCode, codeContext, @oldValue, newValue);
356
357END //
358DELIMITER ;
359
360
361drop procedure if exists modifyExcursionActiveFlagMappingTool;
362
363DELIMITER // 
364CREATE PROCEDURE `modifyExcursionActiveFlagMappingTool` (newValue int(1), excursionCode varchar(255), codeContext varchar(16), cityCode varchar(64), sessionId varchar(255)) 
365BEGIN
366DECLARE excursionId bigint(20);   
367DECLARE oldValue VARCHAR(255);
368DECLARE flag bit(1);
369
370set flag=castIntToBit(newValue);
371SELECT 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;
372
373SELECT ACTIVE INTO oldValue FROM GPT_EXCURSION where ID= excursionId;
374update GPT_EXCURSION set ACTIVE = flag where ID= excursionId;
375SET @oldValue=COALESCE(oldValue, '');
376
377call addLogMappingTool(sessionId, 'Update Excursion Active flag', excursionCode, cityCode, codeContext, @oldValue, newValue);
378
379END //
380DELIMITER ;
381
382
383
384drop procedure if exists modifyExcursionGroupActiveFlagMappingTool;
385
386DELIMITER // 
387CREATE PROCEDURE `modifyExcursionGroupActiveFlagMappingTool` (newValue int(1), excursionCode varchar(255), codeContext varchar(16), cityCode varchar(64), sessionId varchar(255)) 
388BEGIN
389
390DECLARE excursionId bigint(20);   
391DECLARE oldValue VARCHAR(255);
392DECLARE flag bit(1);
393
394set flag=castIntToBit(newValue);
395
396SELECT 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;
397SELECT ACTIVE INTO oldValue FROM GPT_EXCURSION where ID= excursionId;
398
399update GPT_EXCURSION_GROUP set ACTIVE= flag where EXCURSION_ID= excursionId;
400SET @oldValue=COALESCE(oldValue, '');
401
402call addLogMappingTool(sessionId, 'Update Excursion Group Active flag', excursionCode, cityCode, codeContext, @oldValue, newValue);
403END //
404DELIMITER ;