We run a system for ranking customers as Platinum (top 5), Gold (6-25), Silver (25-100) by sales in the last 180 days. This is done using a Pentaho Kettle job.

I would like this to become a standard facility in OpenVPMS because a) I am the only one at our place who knows Kettle, and b) I keep forgetting to run the job.

I envisage that this would run as a job (like the Document Loader). Like the other jobs in the system, it would have run interval parameters (and suspect that it would normally be run on the first or each month). The other parameters would be the 'Check sales in previous days' (default 180), and for each of the First, Second and Third ranks: max rank and alert.  ie to implement our setup we have:

The processing is as follows:

For each of the 1st, 2nd & 3rd ranks run a query to get the matching customers
For each customer, if they have an existing matching alert (eg now Silver and has Silver) set the End Date to today+job interval+3d, and set the reason to 'Checked + today's date'

If they have a non-matching alert (eg now Silver, was Gold), set its status to Competed and set its End Date to today, then create the new alert as In Progress, start date=today, end date=today+job interval+3d, and reason 'System Calculated'

If they have a no existing rank alert, then create the new alert as In Progress, start date=today, end date=today+job interval+3d, and reason 'System Calculated'

The reason for setting the end date to today+job interval+3d is to allow for the cases where the customer drops out of the rankings. ie if they were Silver but now are no longer in the top 100, then their rank alert will auto complete. [Why the "+3d"? - It just seemed be a good idea to add a little padding to the 'next date on which this job will run' - but if this is easily calculated, then we don't need the +3d.]

If others indicate that they would support this facility, then I will create the Project for it.

Regards, Tim G