-
AuthorPosts
-
June 20, 2018 at 2:27 pm #31687
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.
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-databaseAttachments:
You must be logged in to view attached files. -
AuthorPosts
- You must be logged in to reply to this topic.