[rt-devel] (RT 1.0.7 - SQL) Opening due stalled tickets automatically?

[Lorens Kockum]

On Wed, May 09, 2001 at 03:17:28PM +0200, Lorens Kockum wrote:
> I'm looking for a handy command to run from the command line
> (i.e. in a script called from a crontab) that will set to "open"
> any tickets that are "stalled" but with a "due" date in the
> past.

Since nobody's replied, I might as well . . . I found someone
to refresh my memories of SQL :-) It's amazing the number of
SQL tutorials you can find on the web, and even more amazing
the percentage of those that only cover the first two hours
of an SQL class, without ever going any further. Didn't
remember/realize that the select arguments could be constants,
and didn't have to be column names.

I'll be running this every night after the db backup.

#!/bin/sh
mysql rt <<EOF
begin
insert
    into transactions
        (effective_sn,serial_num,actor,type,trans_data,trans_date)
    select
        effective_sn,serial_num,'_rt_cron', 'status', 'open', unix_timestamp()
    from each_req
    where date_due < unix_timestamp()
        and status='stalled'
        and effective_sn = serial_num ;
update each_req
    set status='open'
    where date_due < unix_timestamp() and status='stalled' ;
commit
EOF

The "effective_sn = serial_num" makes for only one transaction
line in the WUI; I tried to mimick the behaviour I saw
when I opened/stalled tickets from the WUI.

(Did I just invent the acronym WUI?)
-- 
#include <std_disclaim.h>                          Lorens Kockum