diff options
Diffstat (limited to 'httemplate/search/rt_transaction.html')
| -rw-r--r-- | httemplate/search/rt_transaction.html | 24 |
1 files changed, 16 insertions, 8 deletions
diff --git a/httemplate/search/rt_transaction.html b/httemplate/search/rt_transaction.html index 8dda4ba..fb828af 100644 --- a/httemplate/search/rt_transaction.html +++ b/httemplate/search/rt_transaction.html @@ -3,7 +3,7 @@ 'name_singular' => 'transaction', 'query' => $query, 'count_query' => $count_query, - 'count_addl' => [ $format_seconds_sub, $format_seconds_sub, ], + 'count_addl' => [ $format_seconds_sub ],#$format_seconds_sub, ], 'header' => [ 'Ticket #', 'Ticket', 'Date', @@ -54,9 +54,9 @@ my $transactiontime = " "; my $join = 'JOIN Tickets ON Transactions.ObjectId = Tickets.Id '. - 'JOIN Users ON Transactions.Creator = Users.Id '. - 'LEFT JOIN acct_rt_transaction '. - ' ON Transactions.Id = acct_rt_transaction.transaction_id'; + 'JOIN Users ON Transactions.Creator = Users.Id '; #. +# 'LEFT JOIN acct_rt_transaction '. +# ' ON Transactions.Id = acct_rt_transaction.transaction_id'; my $where = " WHERE objecttype='RT::Ticket' AND ( ( Transactions.Type = 'Set' @@ -90,20 +90,28 @@ if ( $cgi->param('ticketid') =~ /^\s*(\d+)\s*$/ ) { } if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) { - $where .= " AND acct_rt_transaction.svcnum = $1"; + $where .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )"; } my $query = { - 'select' => "Transactions.*, Tickets.Id AS ticketid, Tickets.Subject, Users.name as otaker, $transactiontime AS transaction_time, acct_rt_transaction.support", + 'select' => join(', ', + 'Transactions.*', + 'Tickets.Id AS ticketid', + 'Tickets.Subject', + 'Users.name AS otaker', + "$transactiontime AS transaction_time", + '( SELECT SUM(support) from acct_rt_transaction where transaction_id = Transactions.id ) AS support', + ), + 'table' => 'transactions', #Pg-ism #'table' => 'Transactions', - 'table' => 'transactions', 'addl_from' => $join, 'extra_sql' => $where, 'order by' => 'ORDER BY Created', }; my $count_query = - "SELECT COUNT(*), SUM($transactiontime), SUM(acct_rt_transaction.support) FROM Transactions $join $where"; + #"SELECT COUNT(*), SUM($transactiontime), SUM(acct_rt_transaction.support) FROM Transactions $join $where"; + "SELECT COUNT(*), SUM($transactiontime) FROM Transactions $join $where"; my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->get('ticketid'); } ]; |