'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',
#some amount of false laziness w/timeworked.html...
my $transactiontime = "
- CASE transactions.type when 'Set'
- THEN (to_number(newvalue,'999999')-to_number(oldvalue, '999999')) * 60
- ELSE timetaken*60
+ CASE Transactions.Type when 'Set'
+ THEN (to_number(NewValue,'999999')-to_number(OldValue, '999999')) * 60
+ ELSE TimeTaken*60
END
";
AND ( ( Transactions.Type = 'Set'
AND Transactions.Field = 'TimeWorked'
AND Transactions.NewValue != Transactions.OldValue )
- OR ( ( Transactions.Type='Create' OR Transactions.Type='Comment' OR Transactions.Type='Correspond' )
+ OR ( ( Transactions.Type='Create' OR Transactions.Type='Comment' OR Transactions.Type='Correspond' OR Transactions.Type='Touch' )
AND Transactions.TimeTaken > 0
)
)
#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 ) {
}
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 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.*',
'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',
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'); } ];