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/07/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.