Viewing 1 post (of 1 total)
  • Author
    Posts
  • #31687
    William
    Keymaster

      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 ExecuteOK 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

      Attachments:
      You must be logged in to view attached files.
    Viewing 1 post (of 1 total)
    • You must be logged in to reply to this topic.