% if ( $conf->exists('dashboard-toplist') ) {
- <% include('/elements/table-grid.html') %>
+ <& /elements/table-grid.html &>
% my $bgcolor1 = '#eeeeee';
% my $bgcolor2 = '#ffffff';
<TR>
<TD CLASS="grid" BGCOLOR="<% $bgcolor %>">
- <A HREF="view/cust_main.cgi?<% $custnum %>"><% $cust_main->name %></A>
+ <A HREF="view/cust_main.cgi?<% $custnum %>"><% $cust_main->name |h %></A>
</TD>
<TD CLASS="grid" BGCOLOR="<% $bgcolor %>">
- <% include('/elements/mcp_lint.html', 'cust_main'=>$cust_main) %>
+ <& /elements/mcp_lint.html, 'cust_main'=>$cust_main &>
</TD>
<TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
<FONT SIZE="-1">
- <A HREF="<% FS::TicketSystem->href_new_ticket($cust_main) %>">(new ticket)</A>
+ <A HREF="<% FS::TicketSystem->href_new_ticket($cust_main) %>"><% mt('(new ticket)') |h %></A>
</FONT>
</TD>
-% foreach my $priority ( @custom_priorities, '' ) {
-% my $num =
-% FS::TicketSystem->num_customer_tickets($custnum,$priority);
-% my $ahref = '';
-% $ahref= '<A HREF="'.
-% FS::TicketSystem->href_customer_tickets($custnum,$priority).
-% '">'
-% if $num;
-
+% foreach my $priority ( @custom_priorities ) {
<TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
- <% $ahref.$num %></A>
- </TD>
+% my $num = $num_tickets_by_priority{$priority}->{$custnum};
+% if ( $num ) {
+ <A HREF="<%
+ FS::TicketSystem->href_customer_tickets($custnum,$priority)
+ %>"><% $num %></A>
+% 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;
+% }
+% }
+ </TD>
% }
</TR>
<TR>
<TD CLASS="grid" BGCOLOR="<% $bgcolor %>">
- Unknown customer number <% $custnum %>
+ <% mt("Unknown customer number [_1]", $custnum) |h %>
</TD>
</TR>
<TR>
<TH CLASS="grid" BGCOLOR="#cccccc"><% $line %></TH>
- <TH CLASS="grid" BGCOLOR="#cccccc">Lint</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('Lint') |h %></TH>
<TH CLASS="grid" BGCOLOR="#cccccc"></TH>
-% foreach my $priority ( @custom_priorities, '' ) {
+% foreach my $priority ( @custom_priorities ) {
<TH CLASS="grid" BGCOLOR="#cccccc">
<% $priority || '<i>(none)</i>'%>
</TH>
#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>