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

    https://pioneersoftware.co.uk/wp-content/uploads/2018/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.