1 <& elements/search.html,
2 'title' => 'Time worked per-customer summary',
3 'name_singular' => 'customer',
5 'count_query' => $count_query,
6 'header' => [ FS::UI::Web::cust_header(
7 $cgi->param('cust_fields')
15 \&FS::UI::Web::cust_fields,
20 'color' => [ FS::UI::Web::cust_colors(),
23 'style' => [ FS::UI::Web::cust_styles(),
26 'align' => [ FS::UI::Web::cust_aligns(),
29 'links' => [ ( map { $_ ne 'Cust. Status' ? $link : '' }
30 FS::UI::Web::cust_header(
31 $cgi->param('cust_fields')
41 unless $FS::CurrentUser::CurrentUser->access_right('List rating data')
44 #false laziness w/cust_main.html (we're really only filtering on status for now)
48 #$search_hash{'query'} = $cgi->keywords;
52 agentnum salesnum status address city county state zip country
54 no_censustract with_geocode with_email tax no_tax POST no_POST
57 cust_fields flattened_pkgs
63 for my $param ( @scalars ) {
64 $search_hash{$param} = scalar( $cgi->param($param) )
65 if length($cgi->param($param));
69 for my $param (qw( classnum refnum tagnum pkg_classnum )) {
70 $search_hash{$param} = [ $cgi->param($param) ];
77 my $sql_query = FS::cust_main::Search->search(\%search_hash);
78 my $count_query = delete($sql_query->{'count_query'});
79 my @extra_headers = @{ delete($sql_query->{'extra_headers'}) };
80 my @extra_fields = @{ delete($sql_query->{'extra_fields'}) };
82 my $link = [ "${p}view/cust_main.cgi?", 'custnum' ];
84 #eofalse (cust_main.html)
86 #false laziness / cribbed from search/rt_ticket.html
89 WHERE Transactions.ObjectType = 'RT::Ticket'
90 "; #AND Transactions.ObjectId = Tickets.Id
92 my $transaction_time = "
93 CASE transactions.type when 'Set'
94 THEN (to_number(newvalue,'999999')-to_number(oldvalue, '999999')) * 60
99 AND ( ( Transactions.Type = 'Set'
100 AND Transactions.Field = 'TimeWorked'
101 AND Transactions.NewValue != Transactions.OldValue )
102 OR ( Transactions.Type IN ( 'Create', 'Comment', 'Correspond', 'Touch' )
103 AND Transactions.TimeTaken > 0
107 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
108 # TIMESTAMP is Pg-specific... ?
109 if ( $beginning > 0 ) {
110 $beginning = "TIMESTAMP '". time2str('%Y-%m-%d %X', $beginning). "'";
111 $twhere .= " AND Transactions.Created >= $beginning ";
113 if ( $ending < 4294967295 ) {
114 $ending = "TIMESTAMP '". time2str('%Y-%m-%d %X', $ending). "'";
115 $twhere .= " AND Transactions.Created <= $ending ";
118 my $transactions = "FROM Transactions $twhere";
120 #eofalse (rt_ticket.html)
122 my $support_time_sub = sub {
123 my $cust_main = shift;
125 foreach my $ticket ($cust_main->tickets) {
127 my $TimeType = FS::Record->scalar_sql(
128 "SELECT Content FROM ObjectCustomFieldValues
130 ON (ObjectCustomFieldValues.CustomField = CustomFields.Id)
131 WHERE CustomFields.Name = 'TimeType'
132 AND ObjectCustomFieldValues.ObjectType = 'RT::Ticket'
133 AND ObjectCustomFieldValues.Disabled = 0
134 AND ObjectId = ". $ticket->{id}
136 next unless $TimeType eq 'support';
138 $sec += FS::Record->scalar_sql(
139 "SELECT SUM($transaction_time) $transactions ".
140 " AND Transactions.ObjectId = ". $ticket->{id}
144 (($sec < 0) ? '-' : '' ). int(abs($sec)/3600)."h".sprintf("%02d",(abs($sec)%3600)/60)."m";
148 my $unclass_time_sub = sub {
149 my $cust_main = shift;
151 foreach my $ticket ($cust_main->tickets) {
153 my $TimeType = FS::Record->scalar_sql(
154 "SELECT Content FROM ObjectCustomFieldValues
156 ON (ObjectCustomFieldValues.CustomField = CustomFields.Id)
157 WHERE CustomFields.Name = 'TimeType'
158 AND ObjectCustomFieldValues.ObjectType = 'RT::Ticket'
159 AND ObjectCustomFieldValues.Disabled = 0
160 AND ObjectId = ". $ticket->{id}
162 next unless $TimeType eq '';
164 $sec += FS::Record->scalar_sql(
165 "SELECT SUM($transaction_time) $transactions ".
166 " AND Transactions.ObjectId = ". $ticket->{id}
170 (($sec < 0) ? '-' : '' ). int(abs($sec)/3600)."h".sprintf("%02d",(abs($sec)%3600)/60)."m";