Reminder Run Failure

Submitted by Guest on Thu, 14/02/2013 - 01:09

General User's Discussion

All,

When printing a set of reminders, and we have a hardware or other failure, what's the correct recovery procedure? It appears that all the reminders have been marked as sent, even though they didn't print. They were spooled, but never came out... and we've now lost the run. I'm getting geared up to go back into the database and try to manually reset all of the reminders. When this happens, is there some way to tell the system to not update the status on those reminders?

Also, if anyone has run into this before, are you familiar if there are any pitfalls in directly editing the database in an attempt to reset a group of reminders?

Thanks, Paul

Re: Reminder Run Failure

Submitted by pstadler (not verified) on Thu, 14/02/2013 - 01:25.

My present guess on this is to identify the reminders using this:

 select * from acts
inner join act_details
    on act_details.act_id=acts.act_id
     and act_details.name='reminderCoutn'
where arch_short_name='act.patientReminder'
    and DATE(activity_end_time)>DATE("startdate")
    and DATE(activity_end_time)<DATE("enddate")
    and act_details.value>0

... and update the act_details.value to be 0. This won't change the description field however... where there's a string representation of how many times it has been sent. I may need to repopulate that field as well.

Cheers, Paul

Re: Reminder Run Failure

Submitted by pstadler (not verified) on Thu, 14/02/2013 - 11:09.

I've played with this a bit today, and came up w/ the attached script (in python) to rollback. Does this look right to those folks who understand the schema better than I do?

Cheers, Paul

(note, I had to rename to .txt to allow uploading)

Attachment	Size
rollback.py_.txt	1.43 KB

Re: Reminder Run Failure

Submitted by tanderson on Thu, 14/02/2013 - 14:18.

One improvement would be to set the reminderCount to reminderCount - 1 if its >= 1.

This ensures that the correct template is used if you have a different template per reminderCount.

-Tim

Re: Reminder Run Failure

Submitted by pstadler (not verified) on Thu, 14/02/2013 - 22:51.

Thanks for having a look Tim. Yes, you're right. I thought of that but didn't bother in our case since we should only have states of 0 or 1 in our configuration (no second or third reminder runs as we do these by telephone). Anyone else that attempts to use the script, should modify it do that though so that the right reminder count templates come out.

It seems to me through this that there's an enhancement opportunity here. How do you, and anyone else feel, about making these reminder print runs "foolproof" (my jamming printer being the fool) by adding a simple validation and rollback into the process. I'd propose:

Click Send-All
Let the print run go
If 'Cancel' is hit, all reminders rollback to their pre-send-all state
At the end of the run, put up a dialog box "Did all reminders print/send correctly?"
If user hits 'No', then rollback reminders like in step 3
Else proceed as we do today

Not everyone out there can fire up some python to correct their database, and so this might avert the problem for others.

Cheers, Paul

Re: Reminder Run Failure

Submitted by tanderson on Fri, 15/02/2013 - 08:53.

A problem with this approach is that if you lose your session, you cannot roll the reminders back.

There could be an administrative option to to reset reminders for a particular lastSent date, but this would not allow you to selectively reset based on how the reminder was sent. E.g email reminders may have been processed successfully, so you wouldn't want to reset them.

Another approach would be create a new archetype "act.patientReminderLog" that is created each time a reminder is generated. This would indicate:

when the reminder was sent
how the reminder was sent (email, phone, mail)
which customer the reminder was sent to
the customer contact (formatted text)
the reminder count
the document template used

This would be linked to the reminder.

It would be possible to utilise this to regenerate reminders for a particular date and send type.

E.g:

regenerate reminders done on 12/1/13.
regenerate mail reminders done on 14/2/13, for customers starting with "Z*"

-Tim

Re: Reminder Run Failure

Submitted by pstadler (not verified) on Sat, 16/02/2013 - 12:48.

Thanks Tim,

Just wanted to let everyone know that my script didn't perfectly work. I moved the database to a test instance and modified the DB there. Then I ran the script, and printer reminders. The reminders came out fine... but the DB was left in a questionable state. As a result, I didn't push this version of the DB back to our prod environment. Instead, I left it... and was happy that I had essentially run the same date range, and had some hard-copy in hand that would work for reminders. I haven't tracked down exactly what the issue was, but have ended up more motivated w/r/t solving the larger problem here.

Tim, your approach is clearly superior to what I was suggesting. My solution would still allow for any user to click 'ok' and mess the process up and make it irrovocable. Yours allows rollback or regenaration even after severe user error.

Cheers, Paul