[rt-users] Another script question

Mathew Snyder theillien at yahoo.com
Fri Apr 13 05:45:45 EDT 2007


Using my previous user timesheet script as my template I decided to rewrite a
script that is used to gather the total time spent on each customer over the
previous week.  It is currently set up to query the database directly using DBI.
 I've found that this creates an inaccurate tally probably because our query is
probably incorrect so I'm trying to set it up using the API as I did with the
user timesheet.

However, it doesn't seem to be giving me the results I expected.  It seems
fairly straight forward, but I can't get it to do anything.  I'm using the
following FromSQL query:
$tix->FromSQL('Queue = "CustomerCare" AND ((LastUpdated > "' . $weekStart . '"
AND LastUpdated < "' . $weekEnd . '") OR (LastUpdated = "' . $weekStart . '" OR
LastUpdated = "' . $weekEnd. '"))');

This is actually the same query I used in my user script which is providing the
desired results so I know the query is correct.

This is the block of code I would expect to provide me with the data I'm trying
to manipulate:
while (my $ticket = $tix->Next) {
        foreach my $day (@searchDate){
                my $env = $ticket->FirstCustomFieldValue('Environment');
                $tickets{$env}++;
                my $transactions = $ticket->Transactions;
                while (my $transaction  = $transactions->Next) {
                        next unless (($transaction->Created == $day) ||
($transaction->TimeTaken));
                        $envTime{$env} += $transaction->TimeTaken;
                }
        }
}

@searchDate is populated with 7 dates using the yyyy-mm-dd format found in the
database.

The script will then alter the times (in minutes) to be output in an hh:mm
format as both a total and, using the $tickets{$env} entry, a mean time.  It
could very well be that I'm not getting any output because my manipulation block
is wrong.  However, the quickness that the script completes its task and returns
a file to me leads me to believe that it isn't even pulling the data I need.  It
also stops outputting data to the file after printing out the first date.

Does the above block look right for what I'm attempting to do?

Mathew



More information about the rt-users mailing list