Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --
- -- yield_calendar_omission
- --
- DELETE FROM
- e_restaurant.yield_calendar_omission
- WHERE
- restaurant_id < 5000000;
- REPLACE INTO e_restaurant.yield_calendar_omission (id, restaurant_id, version, data, created_at, updated_at)
- SELECT
- r.pkrestaurantid AS id,
- r.pkrestaurantid AS resturant_id,
- 1 AS version,
- (SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT(
- 'StartDate', DATE_FORMAT(convert_tz(re.exceptionfromdate,
- IF(r.fkcountryid = 185, 'Asia/Singapore',
- 'Asia/Bangkok'), 'GMT'),
- IF(r.fkcountryid = 185, '%Y-%m-%dT%H:%i:%S+08:00',
- '%Y-%m-%dT%H:%i:%S+07:00')),
- 'StartTime', IF(re.exceptionfullday = 'Yes',
- 0,
- convert(re.exceptionfromtime * 100, SIGNED)),
- 'EndDate', DATE_FORMAT(convert_tz(re.exceptiontodate,
- IF(r.fkcountryid = 185, 'Asia/Singapore',
- 'Asia/Bangkok'),
- 'GMT'),
- IF(r.fkcountryid = 185, '%Y-%m-%dT%H:%i:%S+08:00',
- '%Y-%m-%dT%H:%i:%S+07:00')),
- 'EndTime', IF(re.exceptionfullday = 'Yes',
- 2330,
- convert(re.exceptiontotime * 100, SIGNED))
- )),
- ']')
- FROM
- ebdb.restaurant_exceptions re
- WHERE r.pkrestaurantid = re.fkrestaurantid AND re.exceptiondatetype = 1) AS data,
- convert_tz((SELECT min(exceptiondateadded)
- FROM ebdb.restaurant_exceptions
- WHERE r.pkrestaurantid = fkrestaurantid AND exceptiondatetype = 1
- LIMIT 1), IF(r.fkcountryid = 185, 'Asia/Singapore', 'Asia/Bangkok'),
- 'GMT') AS created_at,
- convert_tz((SELECT max(exceptiondateadded)
- FROM ebdb.restaurant_exceptions
- WHERE r.pkrestaurantid = fkrestaurantid AND exceptiondatetype = 1
- LIMIT 1), IF(r.fkcountryid = 185, 'Asia/Singapore', 'Asia/Bangkok'),
- 'GMT') AS updated_at
- FROM ebdb.restaurant r
- WHERE (SELECT count(1)
- FROM ebdb.restaurant_exceptions re0
- WHERE re0.fkrestaurantid = r.pkrestaurantid AND re0.exceptiondatetype = 1) > 0;
- REPLACE INTO e_restaurant.yield_calendar_omission (id, restaurant_id, version, data, created_at, updated_at)
- SELECT
- r.pkrestaurantid AS id,
- r.pkrestaurantid AS resturant_id,
- 1 AS version,
- (SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT(
- 'StartDate', DATE_FORMAT(convert_tz(re.exceptionfromdate, 'Asia/Kuala_Lumpur', 'GMT'),
- '%Y-%m-%dT%H:%i:%S+08:00'),
- 'StartTime', IF(re.exceptionfullday = 'Yes',
- 0,
- convert(re.exceptionfromtime * 100, SIGNED)),
- 'EndDate', DATE_FORMAT(convert_tz(re.exceptiontodate, 'Asia/Kuala_Lumpur', 'GMT'),
- '%Y-%m-%dT%H:%i:%S+08:00'),
- 'EndTime', IF(re.exceptionfullday = 'Yes',
- 2330,
- convert(re.exceptiontotime * 100, SIGNED))
- )),
- ']')
- FROM
- my_eatigo.restaurant_exceptions re
- WHERE r.pkrestaurantid = re.fkrestaurantid AND re.exceptiondatetype = 1) AS data,
- convert_tz((SELECT min(exceptiondateadded)
- FROM my_eatigo.restaurant_exceptions
- WHERE r.pkrestaurantid = fkrestaurantid AND exceptiondatetype = 1
- LIMIT 1), 'Asia/Kuala_Lumpur',
- 'GMT') AS created_at,
- convert_tz((SELECT max(exceptiondateadded)
- FROM my_eatigo.restaurant_exceptions
- WHERE r.pkrestaurantid = fkrestaurantid AND exceptiondatetype = 1
- LIMIT 1), 'Asia/Kuala_Lumpur',
- 'GMT') AS updated_at
- FROM my_eatigo.restaurant r
- WHERE (SELECT count(1)
- FROM my_eatigo.restaurant_exceptions re0
- WHERE re0.fkrestaurantid = r.pkrestaurantid AND re0.exceptiondatetype = 1) > 0;
- REPLACE INTO e_restaurant.yield_calendar_omission (id, restaurant_id, version, data, created_at, updated_at)
- SELECT
- r.pkrestaurantid AS id,
- r.pkrestaurantid AS resturant_id,
- 1 AS version,
- (SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT(
- 'StartDate', DATE_FORMAT(convert_tz(re.exceptionfromdate, 'Asia/Hong_Kong', 'GMT'),
- '%Y-%m-%dT%H:%i:%S+08:00'),
- 'StartTime', IF(re.exceptionfullday = 'Yes',
- 0,
- convert(re.exceptionfromtime * 100, SIGNED)),
- 'EndDate', DATE_FORMAT(convert_tz(re.exceptiontodate, 'Asia/Hong_Kong', 'GMT'),
- '%Y-%m-%dT%H:%i:%S+08:00'),
- 'EndTime', IF(re.exceptionfullday = 'Yes',
- 2330,
- convert(re.exceptiontotime * 100, SIGNED))
- )),
- ']')
- FROM
- hk_eatigo.restaurant_exceptions re
- WHERE r.pkrestaurantid = re.fkrestaurantid AND re.exceptiondatetype = 1) AS data,
- convert_tz((SELECT min(exceptiondateadded)
- FROM hk_eatigo.restaurant_exceptions
- WHERE r.pkrestaurantid = fkrestaurantid AND exceptiondatetype = 1
- LIMIT 1), 'Asia/Hong_Kong',
- 'GMT') AS created_at,
- convert_tz((SELECT max(exceptiondateadded)
- FROM hk_eatigo.restaurant_exceptions
- WHERE r.pkrestaurantid = fkrestaurantid AND exceptiondatetype = 1
- LIMIT 1), 'Asia/Hong_Kong',
- 'GMT') AS updated_at
- FROM hk_eatigo.restaurant r
- WHERE (SELECT count(1)
- FROM hk_eatigo.restaurant_exceptions re0
- WHERE re0.fkrestaurantid = r.pkrestaurantid AND re0.exceptiondatetype = 1) > 0;
- REPLACE INTO e_restaurant.yield_calendar_omission (id, restaurant_id, version, data, created_at, updated_at)
- SELECT
- r.pkrestaurantid AS id,
- r.pkrestaurantid AS resturant_id,
- 1 AS version,
- (SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT(
- 'StartDate', DATE_FORMAT(convert_tz(re.exceptionfromdate, 'Asia/Calcutta', 'GMT'),
- '%Y-%m-%dT%H:%i:%S+05:30'),
- 'StartTime', IF(re.exceptionfullday = 'Yes',
- 0,
- convert(re.exceptionfromtime * 100, SIGNED)),
- 'EndDate', DATE_FORMAT(convert_tz(re.exceptiontodate, 'Asia/Calcutta', 'GMT'),
- '%Y-%m-%dT%H:%i:%S+05:30'),
- 'EndTime', IF(re.exceptionfullday = 'Yes',
- 2330,
- convert(re.exceptiontotime * 100, SIGNED))
- )),
- ']')
- FROM
- in_eatigo.restaurant_exceptions re
- WHERE r.pkrestaurantid = re.fkrestaurantid AND re.exceptiondatetype = 1) AS data,
- convert_tz((SELECT min(exceptiondateadded)
- FROM in_eatigo.restaurant_exceptions
- WHERE r.pkrestaurantid = fkrestaurantid AND exceptiondatetype = 1
- LIMIT 1), 'Asia/Calcutta',
- 'GMT') AS created_at,
- convert_tz((SELECT max(exceptiondateadded)
- FROM in_eatigo.restaurant_exceptions
- WHERE r.pkrestaurantid = fkrestaurantid AND exceptiondatetype = 1
- LIMIT 1), 'Asia/Calcutta',
- 'GMT') AS updated_at
- FROM in_eatigo.restaurant r
- WHERE (SELECT count(1)
- FROM in_eatigo.restaurant_exceptions re0
- WHERE re0.fkrestaurantid = r.pkrestaurantid AND re0.exceptiondatetype = 1) > 0;
- REPLACE INTO e_restaurant.yield_calendar_omission (id, restaurant_id, version, data, created_at, updated_at)
- SELECT
- r.pkrestaurantid AS id,
- r.pkrestaurantid AS resturant_id,
- 1 AS version,
- (SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT(
- 'StartDate', DATE_FORMAT(convert_tz(re.exceptionfromdate, 'Asia/Manila', 'GMT'),
- '%Y-%m-%dT%H:%i:%S+08:00'),
- 'StartTime', IF(re.exceptionfullday = 'Yes',
- 0,
- convert(re.exceptionfromtime * 100, SIGNED)),
- 'EndDate', DATE_FORMAT(convert_tz(re.exceptiontodate, 'Asia/Manila', 'GMT'),
- '%Y-%m-%dT%H:%i:%S+08:00'),
- 'EndTime', IF(re.exceptionfullday = 'Yes',
- 2330,
- convert(re.exceptiontotime * 100, SIGNED))
- )),
- ']')
- FROM
- ph_eatigo.restaurant_exceptions re
- WHERE r.pkrestaurantid = re.fkrestaurantid AND re.exceptiondatetype = 1) AS data,
- convert_tz((SELECT min(exceptiondateadded)
- FROM ph_eatigo.restaurant_exceptions
- WHERE r.pkrestaurantid = fkrestaurantid AND exceptiondatetype = 1
- LIMIT 1), 'Asia/Manila',
- 'GMT') AS created_at,
- convert_tz((SELECT max(exceptiondateadded)
- FROM ph_eatigo.restaurant_exceptions
- WHERE r.pkrestaurantid = fkrestaurantid AND exceptiondatetype = 1
- LIMIT 1), 'Asia/Manila',
- 'GMT') AS updated_at
- FROM ph_eatigo.restaurant r
- WHERE (SELECT count(1)
- FROM ph_eatigo.restaurant_exceptions re0
- WHERE re0.fkrestaurantid = r.pkrestaurantid AND re0.exceptiondatetype = 1) > 0;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement