X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Felements%2Fdashboard-toplist.html;h=b80af7883b51df55e09992c18e9583929c95a810;hp=5b8c524928c0fe5f9ad605aed1c4ce2d3ba52ce7;hb=37e92c220a54636ebc7900cd59ec6ce56790ede8;hpb=3f3c8af45e3450374818794baf79e3a7238a3489

diff --git a/httemplate/elements/dashboard-toplist.html b/httemplate/elements/dashboard-toplist.html
index 5b8c52492..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,20 +21,32 @@
      
         <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 %>">
+            <& /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) %>"><% 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>
 
@@ -42,7 +54,7 @@
 
         <TR>
           <TD CLASS="grid" BGCOLOR="<% $bgcolor %>">
-	    Unknown customer number <% $custnum %>
+	    <% mt("Unknown customer number [_1]", $custnum) |h %> 
 	  </TD>
         </TR>
 
@@ -51,7 +63,7 @@
 %   } elsif ( $line =~ /^\-\-+$/ ) { #divider
 %     
       <TR>
-        <TH CLASS="grid" COLSPAN="<% scalar(@custom_priorities) + 2 %>"></TH>
+        <TH CLASS="grid" COLSPAN="<% scalar(@custom_priorities) + 4 %>"></TH>
       </TR>
 
 %     next;
@@ -59,14 +71,16 @@
 %   } elsif ( $line =~ /^\s*$/ ) {
 
       <TR>
-        <TD CLASS="grid" COLSPAN="<% scalar(@custom_priorities) + 2 %>" BGCOLOR="<% $bgcolor %>">&nbsp;</TD>
+        <TD CLASS="grid" COLSPAN="<% scalar(@custom_priorities) + 4 %>" BGCOLOR="<% $bgcolor %>">&nbsp;</TD>
       </TR>
 
 %   } elsif ( $line =~ /^\S/ ) { #label line
 
       <TR>
         <TH CLASS="grid" BGCOLOR="#cccccc"><% $line %></TH>
-%       foreach my $priority ( @custom_priorities, '' ) {
+	<TH CLASS="grid" BGCOLOR="#cccccc"><% mt('Lint') |h %></TH>
+	<TH CLASS="grid" BGCOLOR="#cccccc"></TH>
+%       foreach my $priority ( @custom_priorities ) {
           <TH CLASS="grid" BGCOLOR="#cccccc">
 	    <% $priority || '<i>(none)</i>'%>
 	  </TH>
@@ -76,7 +90,7 @@
 %   } else { #regular line
 
       <TR>
-        <TD CLASS="grid"  COLSPAN="<% scalar(@custom_priorities) + 2 %>" BGCOLOR="<% $bgcolor %>"><% $line %></TD>
+        <TD CLASS="grid"  COLSPAN="<% scalar(@custom_priorities) + 4 %>" BGCOLOR="<% $bgcolor %>"><% $line %></TD>
       </TR>
 
 %   }
@@ -94,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>