event insert available aplicares

-- ----------------------------
-- Event structure for `bed_available`
-- ----------------------------
DROP EVENT IF EXISTS `bed_available`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` EVENT `bed_available` ON SCHEDULE EVERY 10 MINUTE STARTS '2018-04-23 16:40:00' ON COMPLETION PRESERVE ENABLE DO BEGIN
DELETE FROM bed_available_bpjs;

INSERT INTO bed_available_bpjs
SELECT
  CONVERT(unit_id, CHAR(10)) as koderuang,
  REPLACE(location.location_name, 'BANGSAL ', '')as namaruang,
  if(CONVERT(unit_id, CHAR(10))=1,'ICU',cos.cos_code_bpjs) as kodekelas,
  if(CONVERT(unit_id, CHAR(10))=1,'ICU', cos_name) as namakelas,
  COUNT(IF(status<>5,sub_unit_id, NULL)) as kapasitas,
  COUNT(IF(status IN(1,2), sub_unit_id, NULL)) as tersedia,
  0 as tersediapria,
  0 as tersediawanita,
  0 as tersediapriawanita
FROM
(SELECT
  unit.unit_id,
  unit.unit_name,
  sub_unit.location_id,
  sub_unit.sub_unit_id,
  status,
  sub_unit.disabled,
  IFNULL(cos_sub_unit.cos_id, IFNULL(cos_unit.cos_id,cos_dept.cos_id)) as cos_id
FROM
  sub_unit
LEFT JOIN unit ON (sub_unit.unit_id = unit.unit_id)
LEFT JOIN cos_dept ON (unit.dept_id=cos_dept.dept_id)
LEFT JOIN cos_dept cos_unit ON (unit.unit_id=cos_unit.unit_id)
LEFT JOIN cos_dept cos_sub_unit ON (sub_unit.sub_unit_id=cos_sub_unit.sub_unit_id)
WHERE (unit.sub_unit_availability = 1) AND (cos_unit.cos_id not in (34,35,36,37,38,39))
GROUP BY
  sub_unit_id) as tmp
LEFT JOIN cos ON (tmp.cos_id=cos.cos_id)
LEFT JOIN location ON (tmp.location_id=location.location_id)
WHERE ((disabled=0) OR (disabled is null)) AND (location.location_id in (36,37,38,39,40,41,48))
GROUP BY kodekelas, namaruang, namakelas
ORDER BY namaruang, kodekelas;

END
;;
DELIMITER ;