Home › Forums › implementation › Implementation discussion ›

A script to correct due dates for reminders

Submitted by Matt Young on Sat, 23/07/2016 - 23:02
in

The way we have reminders setup components of a vaccination and healthcheck such C3 vaccination, Canine Cough Vaccine and health check are all set as separate reminders. This is because sometimes not all components are indicated and it allows us to customise when each component is due. A downside of this is that we end up with the components sometimes being due a few weeks or just a few days apart.

What I would like to do is to have a shell script which is run using cron that checks for reminders that are within say 4 weeks of each other for an individual patient and changes the due date to whatever the earlier of the dates is for each of those reminders.

I need help to do this as that query is too complex for me. Is anyone interested in helping me with this? I am happy to pay someone to write the script and will share it back to the community.  

‹ Ubuntu 16.04 and Tomcat8 fail A new medical record item - Summary ›

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: A script to correct due dates for reminders

Submitted by tgething (not verified) on Sun, 24/07/2016 - 06:04.

Matt - a more brute force approach would be set the reminder due dates to the 1st of the month.  This makes the SQL far simpler - look for any reminder due date not on the 1st and set it to the 1st.  This means that you do not have to worry about looking for reminders for the one patient that occur within 4 weeks of one another.

However, the above will bunch everything together on the 1st of each month.

If you don't want that then use a different day of the month for each customer - and derive the day from the customer ID - ie ((custId MOD 28)+1)

Here are two bits of SQL. This one looks for reminders that should be updated:

select a.description, a.activity_end_time, ((c.entity_id MOD 28)+1) as newDay,
    DATE_ADD(DATE_SUB(a.activity_end_time, INTERVAL (DAY(a.activity_end_time)-1) DAY), INTERVAL (c.entity_id MOD 28) DAY) as newDate,
    p.name, c.name, c.entity_id from acts a
join participations pp on pp.act_id = a.act_id and pp.arch_short_name = 'participation.patient'
join entities p on p.entity_id = pp.entity_id
join entity_relationships er on er.target_id = p.entity_id
    and er.arch_short_name = 'entityRelationship.patientOwner'
    and isnull(er.active_end_time)
join entities c on c.entity_id = er.source_id    
where a.arch_short_name = 'act.patientReminder'
and a.activity_end_time >= DATE_SUB(CURDATE(), INTERVAL (DAY(CURDATE())-1) DAY)
and DAY(a.activity_end_time) <> ((c.entity_id MOD 28)+1)
#and c.entity_id = 1130
order by c.entity_id;

Note the commented out second last line. I was using this to check my update code. I also switched the '<>' in the prior line to '=' so that I could check that the dates had been updated as expected.

Here is the update query:

update acts a
join participations pp on pp.act_id = a.act_id and pp.arch_short_name = 'participation.patient'
join entities p on p.entity_id = pp.entity_id
join entity_relationships er on er.target_id = p.entity_id
    and er.arch_short_name = 'entityRelationship.patientOwner'
    and isnull(er.active_end_time)
join entities c on c.entity_id = er.source_id  
set a.activity_end_time = DATE_ADD(DATE_SUB(a.activity_end_time, INTERVAL (DAY(a.activity_end_time)-1) DAY), INTERVAL (c.entity_id MOD 28) DAY)
where a.arch_short_name = 'act.patientReminder'
#and c.entity_id = 1130
and a.activity_end_time >= DATE_SUB(CURDATE(), INTERVAL (DAY(CURDATE())-1) DAY)
and DAY(a.activity_end_time) <> ((c.entity_id MOD 28)+1);

Again, note the commented out 3rd last line. I was using this to only update one customer's reminders.

Using the above may screw things in the cases where your 'logically together' reminders are split across a month boundary. These will be split do that they are a month apart.

 

Regards, Tim G

Syndicate content