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
|
<& elements/search.html,
'title' => 'Time worked',
'name_singular' => 'transaction',
'query' => $query,
'count_query' => $count_query,
'count_addl' => [ $format_seconds_sub,
$applied_time ? $format_seconds_sub : () ],
'header' => [ 'Ticket #',
'Ticket',
'Date',
'Time',
$applied_time ? 'Applied' : (),
],
'fields' => [ 'ticketid',
sub { encode_entities(shift->get('subject')) },
'created',
sub { my $seconds = shift->get('transaction_time');
&{ $format_seconds_sub }( $seconds );
},
($applied_time ?
sub { my $seconds = shift->get('applied_time');
&{ $format_seconds_sub }( $seconds );
} : () ),
],
'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>
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 = (
'Transactions.*',
'Tickets.Id AS ticketid',
'Tickets.Subject',
'Users.name AS otaker',
);
my @select_total = ( 'COUNT(*)' );
my ($transaction_time, $applied_time);
my $join = 'JOIN Tickets ON Transactions.ObjectId = Tickets.Id '.
'JOIN Users ON Transactions.Creator = Users.Id '; #.
my $where = "WHERE Transactions.ObjectType = 'RT::Ticket'";
my $cfname = '';
if ( $cgi->param('cfname') =~ /^\w(\w|\s)*$/ ) {
# a TimeValue-type custom field
$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";
# complicated because we have to deal with the case of editing the
# ticket custom field directly (OldReference/NewReference) as well as
# entering a transaction with a custom field value (ObjectId)
$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
) )
";
$where .= " 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 {
# the intrinsic TimeWorked/TimeTaken fields
$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*$/ ) {
$where .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )";
$applied = "AND svcnum = $1";
}
$applied_time = "( SELECT SUM(support) from acct_rt_transaction where transaction_id = Transactions.id $applied )";
$where .= "
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
)
)
";
}
#AND transaction_time != 0
#AND $wheretimeleft
push @select, "($transaction_time) AS transaction_time";
push @select_total, "SUM($transaction_time)";
if ( $applied_time ) {
push @select, "($applied_time) AS applied_time";
push @select_total, "SUM($applied_time)";
}
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). "'";
$where .= " AND Transactions.Created >= $beginning ";
}
if ( $ending < 4294967295 ) {
$ending = "TIMESTAMP '". time2str('%Y-%m-%d %X', $ending). "'";
$where .= " AND Transactions.Created <= $ending ";
}
if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) {
$where .= " AND Users.name = '$1' ";
}
if ( $cgi->param('ticketid') =~ /^\s*(\d+)\s*$/ ) {
$where .= " AND Tickets.Id = $1";
}
my $query = {
'select' => join(', ', @select),
'table' => 'transactions', #Pg-ism
#'table' => 'Transactions',
'addl_from' => $join,
'extra_sql' => $where,
'order by' => 'ORDER BY Created',
};
my $count_query = 'SELECT '.join(', ', @select_total). " FROM Transactions $join $where";
my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->get('ticketid'); } ];
</%init>
|
