X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Felements%2Fdashboard-toplist.html;h=b80af7883b51df55e09992c18e9583929c95a810;hb=e9e0cf0989259b94d9758eceff448666a2e5a5cc;hp=d8cd7f306dc1ec096dd002fd94fd0981cf57cf4d;hpb=624b2d44625f69d71175c3348cae635d580c890b;p=freeside.git
diff --git a/httemplate/elements/dashboard-toplist.html b/httemplate/elements/dashboard-toplist.html
index d8cd7f306..b80af7883 100644
--- a/httemplate/elements/dashboard-toplist.html
+++ b/httemplate/elements/dashboard-toplist.html
@@ -1,6 +1,6 @@
% if ( $conf->exists('dashboard-toplist') ) {
- <% include('/elements/table-grid.html') %>
+ <& /elements/table-grid.html &>
% my $bgcolor1 = '#eeeeee';
% my $bgcolor2 = '#ffffff';
@@ -21,27 +21,32 @@
- <% $cust_main->name %>
+ <% $cust_main->name |h %>
|
- <% include('/elements/mcp_lint.html', 'cust_main'=>$cust_main) %>
+ <& /elements/mcp_lint.html, 'cust_main'=>$cust_main &>
|
- (new ticket)
+
+ <% mt('(new ticket)') |h %>
+
|
-% foreach my $priority ( @custom_priorities, '' ) {
-% my $num =
-% FS::TicketSystem->num_customer_tickets($custnum,$priority);
-% my $ahref = '';
-% $ahref= ''
-% if $num;
-
+% foreach my $priority ( @custom_priorities ) {
- <% $ahref.$num %>
- |
+% my $num = $num_tickets_by_priority{$priority}->{$custnum};
+% if ( $num ) {
+ <% $num %>
+% if ( $priority &&
+% exists($num_tickets_by_priority{''}{$custnum}) ) {
+% # decrement the customer's total by the number in
+% # this priority bin
+% $num_tickets_by_priority{''}{$custnum} -= $num;
+% }
+% }
+
% }
@@ -49,7 +54,7 @@
- Unknown customer number <% $custnum %>
+ <% mt("Unknown customer number [_1]", $custnum) |h %>
|
@@ -73,9 +78,9 @@
<% $line %> |
- Lint |
+ <% mt('Lint') |h %> |
|
-% foreach my $priority ( @custom_priorities, '' ) {
+% foreach my $priority ( @custom_priorities ) {
<% $priority || '(none)'%>
|
@@ -103,11 +108,80 @@ my $conf = new FS::Conf;
#false laziness w/httemplate/search/cust_main.cgi... care if
# custom_priority_field becomes anything but a local hack...
+
my @custom_priorities = ();
-if ( $conf->config('ticket_system-custom_priority_field')
+my $custom_priority_field = $conf->config('ticket_system-custom_priority_field');
+if ( $custom_priority_field
&& @{[ $conf->config('ticket_system-custom_priority_field-values') ]} ) {
@custom_priorities =
$conf->config('ticket_system-custom_priority_field-values');
}
-
+push @custom_priorities, '';
+
+my %num_tickets_by_priority = map { $_ => {} } @custom_priorities;
+# "optimization" (i.e. "terrible hack") to avoid constructing
+# (@custom_priorities) x (cust_main) queries with a bazillion
+# joins each just to count tickets
+if ( $FS::TicketSystem::system eq 'RT_Internal'
+ and $conf->config('dashboard-toplist') )
+{
+ my $text = (driver_name =~ /^Pg/) ? 'text' : 'char';
+ # The RT API does not play nicely with aggregate queries,
+ # so we're going to go around it.
+ my $sql;
+ # optimization to keep this from taking a million years
+ my $cust_tickets =
+ "SELECT custnum, Tickets.Id, Tickets.Queue
+ FROM cust_main
+ JOIN Links ON (
+ Links.Target = 'freeside://freeside/cust_main/' || CAST(cust_main.custnum AS $text)
+ AND Links.Base LIKE '%rt://%/ticket/%'
+ AND Links.Type = 'MemberOf'
+ ) JOIN Tickets ON (Links.LocalBase = Tickets.Id)
+ UNION
+ SELECT custnum, Tickets.Id, Tickets.Queue
+ FROM cust_pkg JOIN cust_svc USING (pkgnum)
+ JOIN Links ON (
+ Links.Target = 'freeside://freeside/cust_svc/' || CAST(cust_svc.svcnum AS $text)
+ AND Links.Base LIKE '%rt://%/ticket/%'
+ AND Links.Type = 'MemberOf'
+ ) JOIN Tickets ON (Links.LocalBase = Tickets.Id)
+ ";
+
+ if ( $custom_priority_field ) {
+ $sql =
+ "SELECT cust_tickets.custnum AS custnum,
+ ObjectCustomFieldValues.Content as priority,
+ COUNT(DISTINCT cust_tickets.Id) AS num_tickets
+ FROM ($cust_tickets) AS cust_tickets
+ LEFT JOIN ObjectCustomFields ON (
+ ObjectCustomFields.ObjectId = '0' OR
+ ObjectCustomFields.ObjectId = cust_tickets.Queue
+ )
+ LEFT JOIN CustomFields ON (
+ ObjectCustomFields.CustomField = CustomFields.Id AND
+ CustomFields.Name = '$custom_priority_field'
+ )
+ LEFT JOIN ObjectCustomFieldValues ON (
+ ObjectCustomFieldValues.CustomField = CustomFields.Id AND
+ ObjectCustomFieldValues.ObjectType = 'RT::Ticket' AND
+ ObjectCustomFieldValues.Disabled = '0' AND
+ ObjectCustomFieldValues.ObjectId = cust_tickets.Id
+ )
+ GROUP BY cust_tickets.custnum, ObjectCustomFieldValues.Content";
+ } else { # no custom_priority_field
+ $sql =
+ "SELECT cust_tickets.custnum,
+ '' as priority,
+ COUNT(DISTINCT cust_tickets.Id) AS num_tickets
+ FROM ($cust_tickets) AS cust_tickets
+ GROUP BY cust_tickets.custnum";
+ }
+ my $sth = dbh->prepare($sql) or die dbh->errstr;
+ $sth->execute or die $sth->errstr;
+ while ( my $row = $sth->fetchrow_hashref ) {
+ $num_tickets_by_priority{ $row->{priority} }->{ $row->{custnum} } =
+ $row->{num_tickets};
+ }
+}
%init>