combine ticket notification scrips, #15353

[freeside.git] / httemplate / search / rt_transaction.html

diff --git a/httemplate/search/rt_transaction.html b/httemplate/search/rt_transaction.html
index 8bf193c..ab56363 100644 (file)
--- 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',
@@ -70,6 +70,8 @@ my $where = "
 #AND transaction_time != 0
 #AND $wheretimeleft
 
+my $support = '';
+
 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 # TIMESTAMP is Pg-specific... ?
 if ( $beginning > 0 ) {
@@ -86,13 +88,16 @@ if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) {
 }
 
 if ( $cgi->param('ticketid') =~ /^\s*(\d+)\s*$/ ) {
-  $where .= " AND Tickets.ID = $1";
+  $where .= " AND Tickets.Id = $1";
 }
 
 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 )";
+  $support = "AND svcnum = $1";
 }
 
+my $support_time = "( SELECT SUM(support) from acct_rt_transaction where transaction_id = Transactions.id $support )";
+
 my $query = {
   'select'    => join(', ',
                    'Transactions.*',
@@ -100,7 +105,7 @@ my $query = {
                    'Tickets.Subject',
                    'Users.name AS otaker',
                    "$transactiontime AS transaction_time",
-                   '( SELECT SUM(support) from acct_rt_transaction where Transaction_id = Transactions.id ) AS support',
+                   "$support_time    AS support",
                  ),
   'table'     => 'transactions', #Pg-ism
   #'table'     => 'Transactions',
@@ -111,7 +116,10 @@ my $query = {
 
 my $count_query =
   #"SELECT COUNT(*), SUM($transactiontime), SUM(acct_rt_transaction.support) FROM Transactions $join $where";
-  "SELECT COUNT(*), SUM($transactiontime) FROM Transactions $join $where";
+  "SELECT COUNT(*),
+          SUM($transactiontime),
+          SUM($support_time)
+     FROM Transactions $join $where";
 
 my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->get('ticketid'); } ];