

DELETE FROM `al_public_holidays`;
INSERT INTO `al_public_holidays` (`id`, `country_id`, `name`, `color`, `status`, `is_fixed`) VALUES
	(1, 154, 'Kashmir Day', '#d4aaee', 1, 1),
	(2, 154, 'Pakistan Day', '#d4aaee', 1, 1),
	(3, 154, 'Labour Day', '#d4aaee', 1, 1),
	(4, 154, 'Eid-ul-fitr', '#bb74e7', 1, 0),
	(5, 154, 'Independance Day', '#d4aaee', 1, 1),
	(6, 154, 'Eid-ul-Azha', '#bb74e7', 1, 0),
	(7, 154, 'Ashura', '#82b082', 1, 0),
	(8, 154, 'Allama Iqbal Day', '#d4aaee', 1, 1),
	(9, 154, 'Eid Milad-un-Nabi ', '#82b082', 1, 0),
	(10, 154, 'Quaid-e-Azam Day', '#d4aaee', 1, 1),
	(11, 211, 'New Years Day', '#80d7ff', 1, 1),
	(12, 211, 'Israk Mikraj Night', '#80d7ff', 1, 0),
	(13, 211, 'Eid Al Fitr', '#80d7ff', 1, 0),
	(14, 211, 'Arafat (Haj) Day', '#80d7ff', 1, 0),
	(15, 211, 'Eid Al Adha', '#80d7ff', 1, 0),
	(16, 211, 'Hijri New Year Day', '#80d7ff', 1, 0),
	(17, 211, 'UAE National Day', '#80d7ff', 1, 1),
	(18, 212, 'New Year\'s Day', '#80d7ff', 1, 1),
	(19, 212, 'Good Friday', '#80d7ff', 1, 0),
	(20, 212, 'St Patricks Day', '#80d7ff', 1, 1),
	(21, 212, 'Easter Monday', '#80d7ff', 1, 0),
	(22, 212, 'Early May Bank Holiday', '#80d7ff', 1, 1),
	(23, 212, 'Battle of the Boyne', '#80d7ff', 1, 1),
	(24, 212, 'August Bank Holiday', '#80d7ff', 1, 1),
	(25, 212, 'August Bank Holiday', '#80d7ff', 1, 1),
	(26, 212, 'St Andrews Day', '#80d7ff', 1, 1),
	(27, 212, 'Christmas Day', '#80d7ff', 1, 1),
	(28, 212, 'Boxing Day', '#80d7ff', 1, 1);

UPDATE proll_client_weekends SET gm_id = NULL
WHERE gm_id = 0 OR gm_id = -1;

UPDATE proll_client_weekends SET region_id = NULL
WHERE region_id = 0 OR region_id = -1;

UPDATE proll_client_weekends SET subregion_id = NULL
WHERE subregion_id = 0 OR subregion_id = -1;

UPDATE proll_client_weekends SET country_id = NULL
WHERE country_id = 0 OR country_id = -1;


INSERT INTO `al_wc_settings` (`wday_id`, `half_day_id`, `half_time_in`, `half_time_out`, `cid`, `market_id`, `cluster_id`, `sub_cluster_id`, `country_id`, `state_id`, `city_id`
, `loc_id`, `department_id`, `year`, `status`, `created_by`, `created_date`, `additional_weekend`, `occurrence`, `wday_color`, `half_day_color`) 
SELECT pcw.wday_id,pcw.half_day_id,pcw.half_time_in,pcw.half_time_in,pcw.cid,pcw.gm_id,pcw.region_id,pcw.subregion_id,pcw.country_id,pcw.state_id,
pcw.city_id,pcw.loc_id,pcw.dep_id,pcw.`year`,pcw.`status`,pcw.created_by,pcw.created_date,NULL,NULL,pcw.wday_color,pcw.half_day_color
FROM proll_client_weekends pcw;


SELECT h.*,(SELECT alwc.id AS wc_id FROM al_wc_settings alwc

WHERE alwc.country_id = h.country_id

LIMIT 1)

FROM holidays h;





ALTER TABLE al_holidays
ADD COLUMN holiday_id INT(11);

INSERT INTO `al_holidays` (`wc_settings_id`, `public_holiday_id`, `start_date`, `end_date`, `color`, `year`, `hdesc`, `created_date`, `created_by`, `modified_date`, `modified_by`, `date`,holiday_id) 
SELECT (SELECT alwc.id AS wc_id FROM al_wc_settings alwc

WHERE alwc.country_id = h.country_id

LIMIT 1),NULL,h.start_date,h.end_date,h.color,h.`year`,h.hdesc,h.created_date,h.created_by,h.modified_date,h.modified_by,h.date,h.holiday_id 
FROM holidays h;




INSERT INTO `al_holiday_details` (`holiday_id`, `emp_id`, `cid`, `market_id`, `cluster_id`, `sub_cluster_id`, `country_id`, `state_id`, `city_id`, `loc_id`, `department_id`, `created_date`, `created_by`, `modified_date`, `modified_by`, `date`)
SELECT ah.id, NULL, h.cid, h.gm_id, h.region_id, h.subregion_id, h.country_id, h.state_id, h.city_id, h.loc_id, h.dep_id, h.created_date, h.created_by, h.modified_date, h.modified_by, h.date
FROM al_holidays ah
INNER JOIN holidays h ON ah.holiday_id = h.holiday_id;





INSERT INTO `al_holidays` (`wc_settings_id`, `public_holiday_id`, `start_date`, `end_date`, `color`, `year`, `hdesc`, `created_date`, `created_by`, `modified_date`, `modified_by`, `date`) 
SELECT (SELECT alwc.id AS wc_id FROM al_wc_settings alwc

WHERE alwc.country_id = h.country_id

LIMIT 1),ph.holiday_id,h.start_date,h.end_date,h.color,h.`year`,h.hdesc,h.created_date,h.created_by,h.modified_date,h.modified_by,h.date 
FROM holidays h
INNER JOIN public_holidays ph  ON ph.name = h.hdesc;


ALTER TABLE `al_holidays`
	DROP COLUMN `holiday_id`;



INSERT INTO `al_holidays` (public_holiday_id, start_date, end_date, color, YEAR, created_date, DATE, STATUS) 
VALUES (7, NOW(), NOW(), '#cccccc', YEAR(NOW()), NOW(), NOW(), 1),
(9,NOW(), NOW(), '#cccccc', YEAR(NOW()), NOW(), NOW(), 1)