All,
Really hope someone able gave me a solution where I stuck around few week. I do using query withour getting any error. But it was not calculating at all
Hope someone able solved my problem
SELECT
ID,
apply_date,
applicant_name,
emp_no,
emp_email,
position,
emp_dept,
emp_date,
leave_type,
start_date,
end_date,
(TIMESTAMPDIFF(Day, start_date, end_date)+1) AS total_day,
leave_reason,
DATE_ADD(end_date, INTERVAL 1 DAY) AS return_date,
apply_by,
paid_type,
approval_status,
approval_by,
approval_position,
approval_remark,
ROUND((DATEDIFF(NOW(), emp_date)-186)/365) TIMESTAMPDIFF(YEAR,emp_date,Now()) AS annual_avail,
medical_avail,
ROUND(DATEDIFF(NOW(), emp_date)/7) AS off_avail,
CASE WHEN approval_status = "Approved" AND leave_type = "Annual Leave" THEN (total_day + annual_used) END AS annual_used,
CASE WHEN approval_status = "Approved" AND leave_type = "Medical Leave" THEN (total_day + medical_used) END AS medical_used,
CASE WHEN approval_status = "Approved" AND leave_type = "Off Day" THEN (total_day + off_used) END AS off_used,
(ROUND((DATEDIFF(NOW(), emp_date)-186)/365) TIMESTAMPDIFF(YEAR,emp_date,Now()) - annual_used) AS annual_balance,
(medical_avail - medical_used) AS medical_balance,
(ROUND(DATEDIFF(NOW(), emp_date)/7) - off_used) AS off_balance
FROM leave_application
WHERE (approval_status is not null) AND (approval_by is not null) AND (approval_position is not null)
I do also try before put the below code to the leave_approval_panel but it also not work the annual_balance, medical_balance and off_balance still empty. May you all advise me how may I solve this issue
global $conn;
$strSQLExists = "select from leave_application where approval_status='Approved' AND leave_type='Annual Leave'";
$rsExists = db_query($strSQLExists,$conn);
$data=db_fetch_array($rsExists);
if($data)
{
$values["annual_used"] = $data["total_day"]+$data["annual_used"];
$values["annual_balance"] = $data["annual_avail"]-$values["annual_used"];
}
global $conn;
$strSQLExists = "select from leave_application where approval_status='Approved' AND leave_type='Medical Leave'";
$rsExists = db_query($strSQLExists,$conn);
$data=db_fetch_array($rsExists);
if($data)
{
$values["medical_used"] = $data["total_day"]+$data["medical_used"];
$values["medical_balance"] = $data["medical_avail"]-$values["medical_used"];
}
global $conn;
$strSQLExists = "select * from leave_application where approval_status='Approved' AND leave_type='Off Day'";
$rsExists = db_query($strSQLExists,$conn);
$data=db_fetch_array($rsExists);
if($data)
{
$values["off_used"] = $data["total_day"]+$data["off_used"];
$values["off_balance"] = $data["off_avail"]-$values["off_used"];
}
