ClinicOffice Support Forum • View topic - Total Working Hours and Appointment Hours by Staff

Total Working Hours and Appointment Hours by Staff

Help with reports & document templates
Forum rules
*** NOTE: This is our OLD FORUM WEBSITE *** Please go to https://pioneersoftware.co.uk/forums

Total Working Hours and Appointment Hours by Staff

Postby William » June 20th, 2018, 2:27 pm

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

Code: Select all
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

Code: Select all
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

Code: Select all
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 the database function you can download the report via the link below.

Staff Working Hours and Appt Hours [Staff].zip
(23.44 KiB) Downloaded 58 times

For instructions on how to import the downloaded report, please see the article via the following link:-
http://www.clinicofficeforum.co.uk/view ... f=19&t=249
ClinicOffice Support Team
www.pioneersoftware.co.uk
User avatar
William
 
Posts: 196
Joined: November 1st, 2007, 8:52 am

Return to Reports & Templates

Who is online

Users browsing this forum: No registered users and 1 guest

cron