USE regonline;

DROP PROCEDURE IF EXISTS sinkronisasiTaskAntrian;

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `regonline`.`sinkronisasiTaskAntrian`(
	IN `PTANGGAL` DATE
)
BEGIN
	DECLARE VTASK, VSTATUS2 INT;
	DECLARE VANTRIAN, VNOKARTU VARCHAR(50);
	DECLARE VTANGGAL DATE;
	DECLARE VTANGGALTASK DATETIME;
	DECLARE VJENISPASIEN INT;
	   
	DECLARE DATA_NOT_FOUND TINYINT DEFAULT FALSE;
	
	DECLARE CR_ANTRIAN CURSOR FOR
	SELECT b.TASK_ID, r.ID, b.TANGGAL, b.NO_KARTU, b.TANGGAL_TASK, b.JENIS
	  FROM regonline.reservasi r
	       LEFT JOIN regonline.task_antrian_bridge b ON b.NO_KARTU = r.NO_KARTU_BPJS
	       	AND b.TANGGAL = r.TANGGALKUNJUNGAN 
				AND b.SUKSES = 0 
				AND b.TANGGAL_TASK IS NOT NULL
	  		 ,  regonline.log_reservasi l
	 WHERE r.TANGGALKUNJUNGAN = PTANGGAL
	   AND l.kodebooking = r.ID
		AND l.`STATUS` = 1
		AND b.TASK_ID IS NOT NULL
	 UNION
	SELECT b.TASK_ID, r.ID, b.TANGGAL, b.NO_KARTU, b.TANGGAL_TASK
	  FROM regonline.reservasi r
	       LEFT JOIN regonline.task_antrian_bridge b ON b.NO_KARTU = r.NIK
	       	AND b.TANGGAL = r.TANGGALKUNJUNGAN 
				AND b.SUKSES = 0 
				AND b.TANGGAL_TASK IS NOT NULL
	  		 ,  regonline.log_reservasi l
	 WHERE r.TANGGALKUNJUNGAN = PTANGGAL
	   AND l.kodebooking = r.ID
		AND l.`STATUS` = 1
		AND b.TASK_ID IS NOT NULL;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET DATA_NOT_FOUND = TRUE;
			
	OPEN CR_ANTRIAN;
	EOF: LOOP
		FETCH CR_ANTRIAN INTO VTASK, VANTRIAN, VTANGGAL, VNOKARTU, VTANGGALTASK, VJENISPASIEN;
		
		IF DATA_NOT_FOUND THEN
			UPDATE temp.temp SET ID = 0 WHERE ID = 0;
			LEAVE EOF;
		END IF;
		
		IF VJENISPASIEN = 1 AND (VTASK = 1 OR VTASK = 2) THEN
			UPDATE regonline.task_antrian_bridge
			   SET SUKSES = 1
			 WHERE TANGGAL = VTANGGAL 
			   AND NO_KARTU = VNOKARTU 
				AND TASK_ID = VTASK;
		ELSE
			SELECT `STATUS` 
			  INTO VSTATUS2 
			  FROM regonline.task_action_antrian t 
			 WHERE t.TASK_ID = VTASK 
			   AND t.ANTRIAN = VANTRIAN;
			IF NOT VSTATUS2 IS NULL THEN
				IF VSTATUS2 = 0 THEN
					UPDATE regonline.task_action_antrian 
					   SET TANGGAL = VTANGGALTASK 
					 WHERE TASK_ID = VTASK 
					   AND ANTRIAN = VANTRIAN 
						AND `STATUS` = 0;
				END IF;
			ELSE
				INSERT INTO regonline.task_action_antrian (TASK_ID, ANTRIAN, TANGGAL, `STATUS`, RESPONSE) 
				VALUES (VTASK, VANTRIAN, VTANGGALTASK, 0, 'LOC');
			END IF;
			UPDATE regonline.task_antrian_bridge
			   SET SUKSES = 1
			 WHERE TANGGAL = VTANGGAL 
			   AND NO_KARTU = VNOKARTU 
				AND TASK_ID = VTASK;
		END IF;
        
	END LOOP;
	CLOSE CR_ANTRIAN;
END;
//
DELIMITER ;