Dear Forum Members,
I am working on Employees Management System in which Attendance System is a key part.
Right Now I am calculating, Late Coming Time, Overtime, Total Working Hours and Actual Working Hours (without Overtime) directly in MySQL.
Below is the query.
SELECT sr_no,
emp_name,
emp_id,
date,
time_in_official,
time_in_employee,
TIMEDIFF(time_in_employee,time_in_official) AS late_coming,
reason_late_coming,
time_out_official,
time_out_employee,
CASE WHEN TIMEDIFF(time_out_employee,time_out_official) > 0 THEN TIMEDIFF(time_out_employee,time_out_official) ELSE 0 END overtime,
TIMEDIFF(time_out_employee,time_in_employee) AS total_working_hours,
TIMEDIFF(TIMEDIFF(time_out_employee,time_in_employee),(CASE WHEN TIMEDIFF(time_out_employee,time_out_official) > 0 THEN TIMEDIFF(time_out_employee,time_out_official) ELSE 0 END)) AS normal_working_hours,
reason_late_sitting,
ip_address,
os_browser,
remarks,
submitted_by,
submitted_on
FROM attendance
This is working fine in mysql but in generated application when user add or edit record, values for (late_coming, overtime, total_working_hours, normal_working_hours) are not being inserted/updated in table, while on list/view page they are being shown, because of direct mysql calculation, therefore, what I want is:
Calculate Time Differences (late_coming, overtime, total_working_hours, normal_working_hours) on the fly, (javascript onload event) on add and edit pages.
If on the fly is not possible then I want to calculate (late_coming, overtime, total_working_hours, normal_working_hours) before record added and before record updated events.
When any of the above two option is successful, i will reset the query to normal, means no calculation in the query.
Also I need to SUM (late_coming, overtime, total_working_hours, normal_working_hours) on monthly basis (group by MONTH(date)), means I will create a custom view in which whole month's total for (late_coming, overtime, total_working_hours, normal_working_hours) will be calculated and that total hours:minutes will be used in Payroll to calculate payroll (that is other part).
Thanking you in anticipation