Total Working Hours and Appointment Hours by Staff

[William]

This “Staff Working Hours and Appt Hours” report will display a summary each day of the total working hours a staff member is scheduled to work. It will also show the total number of appointments performed during the day; meaning blank spaces are not counted. This will also show a percentage utilisation of the day.

Before you can import this report you will first need to create two custom views and a custom database function. The steps below will outline how to do this.

1) Go to the Tools menu (tab)

2) Click the Advanced DB Operation button

3) Delete any text present in this window

4) Copy and paste the text below into the “Advanced Database Operation” window

CREATE OR REPLACE VIEW xview_daily_app_durations AS
SELECT app.start::date AS dt, app.staff_id, app.clinic_id, date_part('epoch'::text, app.finish - app.start) / 3600::double precision AS app_hours
FROM app
LEFT JOIN appstatus on (app.appstatus_id=appstatus.id)
WHERE (app.allday IS NULL OR app.allday = false) AND (app.staff_id IS NOT NULL AND app.per_id IS NOT NULL)
AND (appstatus.cancelled=false or appstatus.id is null)
ORDER BY clinic_id, staff_id;
grant all on xview_daily_app_durations to std_user;

5) Click Execute and OK

6) Delete any text present in the “Advanced Database Operation” window

7) Copy and paste all of the text below into the “Advanced Database Operation” window

CREATE OR REPLACE VIEW xview_daily_app_total AS
SELECT dt, staff_id, clinic_id, sum(app_hours) as app_hours
FROM xview_daily_app_durations
GROUP BY clinic_id, staff_id, dt
ORDER BY dt DESC, clinic_id, staff_id;
grant all on xview_daily_app_total to std_user;

8) Click Execute and OK

9) Again delete any text present in the “Advanced Database Operation” window

10) Copy and paste all of the text below into the “Advanced Database Operation” window

CREATE OR REPLACE FUNCTION xget_working_hours(staff_id bigint, clinic_id bigint, ondate timestamp without time zone)
RETURNS double precision AS
$BODY$
DECLARE
hours_row hours%ROWTYPE;
day_of_week integer;
ondate2 timestamp;
sql varchar(250);
hrs float;
begin

ondate2 := ondate + interval '1 day';
sql := 'select * from hours where (staff_id=' || staff_id || ') and (clinic_id=' || clinic_id || ') and (xdate >= ' || quote_literal(ondate) || ') and (xdate < ' || quote_literal(ondate2) || ')';
RAISE NOTICE '%', sql;
EXECUTE sql INTO hours_row;

IF (hours_row is null) then
day_of_week = extract(dow from ondate);
sql := 'select * from hours where (staff_id=' || staff_id || ') and (clinic_id=' || clinic_id || ') and (xdate < ''1900-1-8'') and (extract(dow from xdate)=' || day_of_week || ')';
RAISE NOTICE '%', sql;
EXECUTE sql INTO hours_row;

if (hours_row is null) then
RETURN 0;
END IF;
END IF;

if (hours_row.isworking = false) then
RETURN 0;
END IF;

hrs := 0;

if (hours_row.start1 is not null) and (hours_row.finish1 is not null) then
hrs := hrs + extract(hours from age(hours_row.finish1, hours_row.start1));
end if;

if (hours_row.start2 is not null) and (hours_row.finish2 is not null) then
hrs := hrs + extract(hours from age(hours_row.finish2, hours_row.start2));
end if;

if (hours_row.start3 is not null) and (hours_row.finish3 is not null) then
hrs := hrs + extract(hours from age(hours_row.finish3, hours_row.start3));
end if;

RETURN hrs;
end;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;

11) Click Execute, OK and then Close

Now that you have created the two views and single function you can download the report via the link below.

https://pioneersoftware.co.uk/wp-content/uploads/2022/07/Staff-Working-Hours-and-Appt-Hours-Staff.zip

For instructions on how to import the downloaded report, please see the article via the following link:-
https://pioneersoftware.co.uk/forums/topic/how-do-i-import-a-report-file-into-my-database

[Author]

Posts