script_leaves.sql

SELECT 
(CASE 
 WHEN tl.idleave = 1 THEN 'Sick Leave'
  WHEN tl.idleave = 2 THEN 'Vacation Leave'
   WHEN tl.idleave = 17 THEN 'Compensatory Leave'
   ELSE 'N/A'
END) AS `leave_type`
,
tl.hrs AS `Unit Hours`,
IF(tl.hrs = 8, 1.0, 0.5) AS `Unit Days`,

(CASE tl.stat
	WHEN 1 THEN 'Approved'
	WHEN 2 THEN 'Disapproved'
	WHEN 3 THEN 'Pending'
	WHEN 4 THEN 'Cancelled'
	ELSE
	'Pending'
END)
 AS `App Status`,
 tl.`date` AS `App Date`,
(SELECT empid FROM vw_dataemployees WHERE id=tl.idacct) AS empid,
(SELECT CONCAT(fname,' ',lname) FROM tblaccount WHERE id = tl.idacct) AS Employee,
bu.name AS `Business Unit`,

tl.date_create,

 tl.remarks,@temp:=tl.approver1 AS approver1,

IF(tl.approver1 LIKE '%,%',
(SELECT GROUP_CONCAT(sub.empname SEPARATOR '- ') AS `names`
	FROM (SELECT i.empname FROM vw_dataemployees AS i WHERE FIND_IN_SET(i.id, @temp)) AS sub)
,
(SELECT CONCAT(fname,' ',lname) FROM tblaccount WHERE id = tl.approver1)
)
AS approver1_name
,
(CASE tl.approver1_stat
	WHEN 1 THEN 'Approved'
	WHEN 2 THEN 'Disapproved'
	WHEN 3 THEN 'Pending'
	WHEN 4 THEN 'Cancelled'
	ELSE
	'Pending'
END)  AS `status1`
,
tl.approver2,
(SELECT CONCAT(fname,' ',lname) FROM tblaccount WHERE id = tl.approver2) AS approver2_name
,


(CASE tl.approver2_stat
	WHEN 1 THEN 'Approved'
	WHEN 2 THEN 'Disapproved'
	WHEN 3 THEN 'Pending'
	WHEN 4 THEN 'Cancelled'
	ELSE
	NULL
END) AS `status2`,


tl.approver3,

(SELECT CONCAT(fname,' ',lname) FROM tblaccount WHERE id = tl.approver3) AS approver3_name
,
(CASE tl.approver3_stat
	WHEN 1 THEN 'Approved'
	WHEN 2 THEN 'Disapproved'
	WHEN 3 THEN 'Pending'
	WHEN 4 THEN 'Cancelled'
	ELSE
	NULL
END) AS `status3`,
tl.approver4,
(SELECT CONCAT(fname,' ',lname) FROM tblaccount WHERE id = tl.approver4) AS approver4_name,
(CASE tl.approver4_stat
	WHEN 1 THEN 'Approved'
	WHEN 2 THEN 'Disapproved'
	WHEN 3 THEN 'Pending'
	WHEN 4 THEN 'Cancelled'
	ELSE
	NULL
END) AS `status4`
  
FROM tbltimeleaves AS tl
LEFT JOIN tblaccountjob aj ON aj.idacct = tl.idacct
LEFT JOIN tblbunits bu ON bu.id = aj.idunit
WHERE tl.stat = 3 ORDER BY tl.idleave,tl.idacct,bu.id