DROP PROCEDURE IF EXISTS sp_task_tracking_dtls;

CREATE PROCEDURE `sp_task_tracking_dtls`(IN `trackDate` VARCHAR(100), IN `staffId` INT, IN `roomId` INT, IN `bedId` INT)

BEGIN
	SELECT
		tsa.id tsa_id,
		shift_assignments.shift_date,
		tsa.room_id,
		floorwise_room.room_name,
		tsa.bed_id,
		IFNULL( bed.bed_name, '---' ) AS bed_name,
		dts.taskid,
		dts.tasktype,
		dts.taskname,
		shift_assignments.shift_code,(
		SELECT
			COUNT( dsvm.id ) 
		FROM
			daily_subtask_vital_map dsvm 
		WHERE
			dsvm.daily_subtask_id = tsa.daily_subtask_id 
			AND dsvm.is_task_or_subtask = tsa.is_task_or_subtask 
		) AS is_vital_mapping,
		(
		SELECT
			COUNT( pv.id ) 
		FROM
			patient_vitals AS pv 
		WHERE
			pv.task_staff_assignment_id = tsa.id
		) as completed_vitals,
		tat.id tat_id,
		tat.is_task_completed,
		tat.task_complication_time,
		tat.task_doer_note,
		tat.approver_id,
		tat.is_task_accepted,
		tat.task_accepted_time,
		tat.task_approver_note 
	FROM
		task_staff_assignments AS tsa
		INNER JOIN shift_assignments ON tsa.shift_assignment_id = shift_assignments.id
		INNER JOIN floorwise_room ON tsa.room_id = floorwise_room.id
		INNER JOIN (
		SELECT
			'T' AS tasktype,
			dt.id AS taskid,
			dt.task_name AS taskname 
		FROM
			daily_tasks AS dt 
		WHERE
			dt.id NOT IN ( SELECT ds.daily_task_id FROM daily_subtasks AS ds ) UNION
		SELECT
			'S' AS tasktype,
			ds.id AS taskid,
			CONCAT( dt.task_name, '/', ds.subtask_name ) AS taskname 
		FROM
			daily_tasks AS dt
			INNER JOIN daily_subtasks AS ds ON dt.id = ds.daily_task_id 
		) AS dts ON dts.tasktype = tsa.is_task_or_subtask 
		AND dts.taskid = tsa.daily_subtask_id
		LEFT JOIN taskwise_assignment_tracker tat ON tsa.id = tat.task_staff_assignment_id
		LEFT JOIN bed ON tsa.bed_id = bed.id 
	WHERE
		shift_assignments.shift_date = trackDate 
		AND shift_assignments.staff_id = staffId 
		AND (tsa.room_id = roomId OR roomId = 0)
		AND (tsa.bed_id = bedId OR bedId = 0)
	ORDER BY
	shift_assignments.shift_code, tsa.bed_id ASC;
END;