1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
<% include('elements/search.html',
'title' => 'Time worked summary',
'name_singular' => 'ticket',
'query' => $query,
'count_query' => $count_query,
'count_addl' => [ $format_seconds_sub, $format_seconds_sub, ],
'header' => [ 'Ticket #',
'Ticket',
'Time',
'Applied',
],
'fields' => [ 'ticketid',
sub { encode_entities(shift->get('subject')) },
sub { my $seconds = shift->get('transaction_time');
&{ $format_seconds_sub }( $seconds );
},
sub { my $seconds = shift->get('support');
&{ $format_seconds_sub }( $seconds );
},
],
'sort_fields' => [ 'ticketid',
'subject',
'transaction_time',
'support_time',
],
'links' => [
$link,
$link,
'',
'',
],
)
%>
<%once>
my $format_seconds_sub = sub {
my $seconds = shift;
#(($seconds < 0) ? '-' : '') . concise(duration($seconds));
(($seconds < 0) ? '-' : '' ). int(abs($seconds)/3600)."h".sprintf("%02d",(abs(
$seconds)%3600)/60)."m";
};
</%once>
<%init>
#all sorts of false laziness w/rt_transaction.html
die "access denied"
unless $FS::CurrentUser::CurrentUser->access_right('List rating data');
#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
END
";
my $join = 'JOIN Users ON Transactions.Creator = Users.Id '; #.
# 'LEFT JOIN acct_rt_transaction '.
# ' ON Transactions.Id = acct_rt_transaction.transaction_id';
my $twhere = "
WHERE objecttype='RT::Ticket'
AND Transactions.ObjectId = Tickets.Id
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' )
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 ) {
$beginning = "TIMESTAMP '". time2str('%Y-%m-%d %X', $beginning). "'";
$twhere .= " AND Transactions.Created >= $beginning ";
}
if ( $ending < 4294967295 ) {
$ending = "TIMESTAMP '". time2str('%Y-%m-%d %X', $ending). "'";
$twhere .= " AND Transactions.Created <= $ending ";
}
if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) {
$twhere .= " AND Users.name = '$1' ";
}
if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) {
$twhere .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )";
$support = "AND svcnum = $1";
}
my $transactions = "FROM Transactions $join $twhere";
my $where = "WHERE EXISTS ( SELECT 1 $transactions )";
my $transaction_time = "( SELECT SUM($transactiontime) $transactions )";
my $support_time = "( SELECT SUM(support) FROM acct_rt_transaction LEFT JOIN Transactions ON ( transaction_id = Id ) $twhere $support )";
my $query = {
'select' => join(', ',
'Tickets.Id AS ticketid',
'Tickets.Subject',
"$transaction_time AS transaction_time",
"$support_time AS support",
),
'table' => 'tickets', #Pg-ism
#'table' => 'Tickets',
'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) $transactions ) FROM Tickets"; # $join $where";
"SELECT COUNT(*),
SUM( $transaction_time ),
SUM( $support_time )
FROM Tickets $where"; # $join $where";
my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->get('ticketid'); } ];
</%init>
|
