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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
|
<% include('elements/search.html',
'title' => 'Time worked summary',
'name_singular' => 'ticket',
'query' => $query,
'count_query' => $count_query,
'count_addl' => [ $format_seconds_sub,
$applied_time ? $format_seconds_sub : () ],
'header' => [ 'Ticket #',
'Ticket',
'Time',
$applied_time ? 'Applied' : (),
],
'fields' => [ 'ticketid',
sub { encode_entities(shift->get('subject')) },
sub { my $seconds = shift->get('ticket_time');
&{ $format_seconds_sub }( $seconds );
},
($applied_time ?
sub { my $seconds = shift->get('applied_time');
&{ $format_seconds_sub }( $seconds );
} : () ),
],
'sort_fields' => [ 'ticketid',
'subject',
'transaction_time',
$applied_time ? 'applied_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');
local $FS::Record::nowarn_classload = 1;
#some amount of false laziness w/timeworked.html...
my @select = (
'Tickets.Id AS ticketid',
'Tickets.Subject'
);
my @select_total = ( 'COUNT(*)' );
my ($transaction_time, $applied_time);
my $join = 'JOIN Users ON Transactions.Creator = Users.Id '; #.
my $twhere = "
WHERE Transactions.ObjectType = 'RT::Ticket'
AND Transactions.ObjectId = Tickets.Id
";
my $cfname = '';
if ( $cgi->param('cfname') =~ /^\w(\w|\s)*$/ ) {
$cfname = $cgi->param('cfname');
$transaction_time = "(CASE Transactions.Type
WHEN 'CustomField' THEN
( coalesce(to_number(ocfv_new.Content,'999999'),0)
- coalesce(to_number(ocfv_old.Content,'999999'),0) )
ELSE ( to_number(ocfv_main.Content,'999999') )
END) * 60";
$join .= "
LEFT JOIN ObjectCustomFieldValues ocfv_new
ON ( ocfv_new.Id = Transactions.NewReference )
LEFT JOIN ObjectCustomFieldValues ocfv_old
ON ( ocfv_old.Id = Transactions.OldReference )
LEFT JOIN ObjectCustomFieldValues ocfv_main
ON ( ocfv_main.ObjectType = 'RT::Transaction'
AND ocfv_main.ObjectId = Transactions.Id )
JOIN CustomFields
ON ( ( CustomFields.LookupType = 'RT::Queue-RT::Ticket-RT::Transaction'
AND CustomFields.Id = ocfv_main.CustomField
AND ocfv_main.Id IS NOT NULL
)
OR
( CustomFields.LookupType = 'RT::Queue-RT::Ticket'
AND (CustomFields.Id = ocfv_new.CustomField OR ocfv_new.Id IS NULL)
AND (CustomFields.Id = ocfv_old.CustomField OR ocfv_old.Id IS NULL)
AND ocfv_main.Id IS NULL
) )
";
$twhere .= " AND CustomFields.Name = '$cfname'
AND (ocfv_new.Id IS NOT NULL OR ocfv_old.Id IS NOT NULL OR ocfv_main.Id IS NOT NULL)";
}
else {
$transaction_time = "
CASE transactions.type when 'Set'
THEN (to_number(newvalue,'999999')-to_number(oldvalue, '999999')) * 60
ELSE timetaken*60
END";
my $applied = '';
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 )";
$applied = "AND svcnum = $1";
}
$twhere .= "
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='Touch' )
AND Transactions.TimeTaken > 0
)
)";
$applied_time = "( SELECT SUM(support) FROM acct_rt_transaction LEFT JOIN Transactions ON ( transaction_id = Id ) $twhere $applied )";
}
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' ";
}
my $transactions = "FROM Transactions $join $twhere";
my $where = "WHERE EXISTS ( SELECT 1 $transactions )";
my $ticket_time = "( SELECT SUM($transaction_time) $transactions )";
push @select, "$ticket_time AS ticket_time";
push @select_total, "SUM($ticket_time)";
if ( $applied_time) {
push @select, "$applied_time AS applied_time";
push @select_total, "SUM($applied_time)";
}
my $query = {
'select' => join(', ', @select),
'table' => 'tickets', #Pg-ism
#'table' => 'Tickets',
'addl_from' => '', #$join,
'extra_sql' => $where,
'order by' => 'ORDER BY Created',
};
my $count_query = "SELECT ".join(', ', @select_total)." FROM Tickets $where";
#"SELECT COUNT(*), SUM($transactiontime), SUM(acct_rt_transaction.support) FROM Transactions $join $where";
#"SELECT COUNT(*), ( SUM($transactiontime) $transactions ) FROM Tickets"; # $join $where";
my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->get('ticketid'); } ];
</%init>
|
