Viewing 1 post (of 1 total)
  • Author
    Posts
  • #31639
    Support
    Keymaster

    **SERVER EDITION ONLY**

    Please find attached a new “Contact Journal” report which prints the full contact journal and all associated notes and SMS messages sent to patients/contacts.

    Before importing this report you will first need to create a custom view. 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_logsms as

    select

    per.id as per_id, trim(per.lastname || ‘, ‘ || coalesce(per.firstname,”) || ‘ ‘ || coalesce(per.title,”)) as _patient_name,
    per.code, per.company,

    (case
    when extract(dow from actlog.datetime) = 1 then ‘Mon’
    when extract(dow from actlog.datetime) = 2 then ‘Tue’
    when extract(dow from actlog.datetime) = 3 then ‘Wed’
    when extract(dow from actlog.datetime) = 4 then ‘Thu’
    when extract(dow from actlog.datetime) = 5 then ‘Fri’
    when extract(dow from actlog.datetime) = 6 then ‘Sat’
    when extract(dow from actlog.datetime) = 7 then ‘Sun’ end)
    as day_of_week,

    actlog.createdon, actlog.createdby_staff_id, cr_staff.knownas as createdby,
    actlog.updatedon, actlog.updatedby_staff_id, up_staff.knownas as updatedby,
    actlog.datetime, actlog.notes, cast(” as varchar) as result,

    actlog.actlogtype_id, actlogtype.name as _act_log_type,
    per.clinic_id, clinic.name as _clinic

    from per
    join actlog on (actlog.per_id=per.id)
    left join actlogtype on (actlogtype.id=actlog.actlogtype_id)
    left join staff as cr_staff on (actlog.createdby_staff_id=cr_staff.id)
    left join staff as up_staff on (actlog.updatedby_staff_id=up_staff.id)
    left join clinic on (per.clinic_id=clinic.id)

    union all

    select

    per.id as per_id, trim(per.lastname || ‘, ‘ || coalesce(per.firstname,”) || ‘ ‘ || coalesce(per.title,”)) as _patient_name,
    per.code, per.company,

    (case
    when extract(dow from sms_out.datetime) = 1 then ‘Mon’
    when extract(dow from sms_out.datetime) = 2 then ‘Tue’
    when extract(dow from sms_out.datetime) = 3 then ‘Wed’
    when extract(dow from sms_out.datetime) = 4 then ‘Thu’
    when extract(dow from sms_out.datetime) = 5 then ‘Fri’
    when extract(dow from sms_out.datetime) = 6 then ‘Sat’
    when extract(dow from sms_out.datetime) = 7 then ‘Sun’ end)
    as day_of_week,

    sms_out.createdon, sms_out.createdby_staff_id, cr_staff.knownas as createdby,
    sms_out.updatedon, sms_out.updatedby_staff_id, up_staff.knownas as updatedby,
    sms_out.datetime, sms_out.msg as notes, sms_out.result,

    cast(null as bigint) as actlogtype_id, cast(‘SMS OUT’ as varchar) as _act_log_type,
    per.clinic_id, clinic.name as _clinic

    from per
    join sms_out on (sms_out.per_id=per.id)
    left join staff as cr_staff on (sms_out.createdby_staff_id=cr_staff.id)
    left join staff as up_staff on (sms_out.updatedby_staff_id=up_staff.id)
    left join clinic on (per.clinic_id=clinic.id);

    ALTER TABLE xview_logsms
    OWNER TO admin;
    GRANT ALL ON TABLE xview_logsms TO admin;
    GRANT ALL ON TABLE xview_logsms TO std_user;

    5) Click Execute and OK

    Now that you have created the view you can download the report via the link below.

    https://pioneersoftware.co.uk/wp-content/uploads/2022/05/Contact-Journal-Report-Patients.zip

    For instructions on how to import report files, please follow the instructions in this post:-
    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.