View source for Prescription start times are in the past

In older versions of PTS 5, occasionally prescriptions could be booked in with their start times set to several hours in the past (sometimes in the middle of the night), resulting in inflated turnaround times.
=Symptoms=
Having booked in a prescription, usually the first one of the day, it is immediately given a turnaround time of several hours or more and counting. Viewing the prescription details reveals that PTS thinks it was booked in several hours ago. The problem might be picked up when reports show that prescriptions were supposedly booked in when the dispensary was closed.
=Cause=
PTS is web based software and while this confers huge benefits PTS is also exposed to the nature of the web in that sessions can and will time out, or be otherwise terminated if a page is left open and not interacted with for some time. This normally results in the user being returned to the login screen when they next try to use parts of the system.

As the booking in screen is typically left open for long periods of time - sometimes overnight - it is particularly vulnerable to this unavoidable aspect of the web. PC's going to sleep and users being logged out of Windows while the page is open can cause the same problem.

Whatever the reason, if the session is terminated in the background while the booking in screen is still open, and somebody then books something in without reloading the page, the start time of the prescription can be assigned the time when the session was last active.

=Solution=
Do not attempt to interact with a web page that potentially has an expired session behind it, whether due to session time out or another reason, without closing the page and reloading it (this is not bad advice for the web in general). However we acknowledge that the PTS workflow encourages the booking in screen to be left open on certain PCs, therefore the issue was addressed at the API level in PTS v5.0.3.4, and prescription start times are now not allowed to be set to before the creation time except in very specific circumstances.

To fix the problem retroactively, this T-SQL script will correct the start time of any applicable prescriptions and set it to be the same as the creation time, and retain the delta between the start time and the target times:

<blockquote><nowiki>UPDATE Prescriptions SET [Started] = [Created], [Requested] = [Created], [TargetCompletionTime] = DATEADD(ss, DATEDIFF(ss, [Started], [TargetCompletionTime]), [Created]), [TargetDeliveryTime] = DATEADD(ss, DATEDIFF(ss, [Started], [TargetDeliveryTime]), [Created]) WHERE ([created] > [Started] OR [created] > Requested) AND DATEDIFF(ss, [Started], [Created]) > 60 AND [Requested] = [Started]</nowiki></blockquote>

This fix is also a component of the [[Database Maintenance Script]]. Please ensure that a database backup is taken first in case you do not like the results.

If a PTS update is not available to you for some reason, this script will create a trigger to run the fix on new prescriptions whenever they are inserted into the database:

<blockquote><nowiki>CREATE TRIGGER [dbo].[FixStartRequestedTimes] ON [dbo].[Prescriptions] AFTER INSERT AS BEGIN SET NOCOUNT ON; UPDATE Prescriptions SET [Started] = [Created], [Requested] = [Created], [TargetCompletionTime] = DATEADD(ss, DATEDIFF(ss, [Started], [TargetCompletionTime]), [Created]), [TargetDeliveryTime] = DATEADD(ss, DATEDIFF(ss, [Started], [TargetDeliveryTime]), [Created]) WHERE ([created] > [Started] OR [created] > Requested) AND DATEDIFF(ss, [Started], [Created]) > 60 AND [Requested] = [Started] AND PrescriptionID = (SELECT PrescriptionID FROM inserted) END</nowiki></blockquote>

Be sure to disable this trigger if and when you update beyond PTS v5.0.3.4.