From a10c9fb8cece00fc3b996f56c61b6841e93c1551 Mon Sep 17 00:00:00 2001
From: Mark Wells <mark@freeside.biz>
Date: Fri, 28 Sep 2012 16:55:34 -0700
Subject: [PATCH] speed fix for dashboard, from #17067

---
 httemplate/elements/dashboard-toplist.html | 86 +++++++++++++++++++-----------
 httemplate/index.html                      | 10 ++++
 2 files changed, 64 insertions(+), 32 deletions(-)

diff --git a/httemplate/elements/dashboard-toplist.html b/httemplate/elements/dashboard-toplist.html
index f6ebb60fe..c6362e0c9 100644
--- a/httemplate/elements/dashboard-toplist.html
+++ b/httemplate/elements/dashboard-toplist.html
@@ -122,41 +122,62 @@ 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' ) {
+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 = 
-  "SELECT cust_main.custnum AS custnum,
-          ObjectCustomFieldValues.Content as priority,
-          COUNT(DISTINCT Tickets.Id) AS num_tickets
-   FROM cust_main 
-      LEFT JOIN cust_pkg USING (custnum)
-      LEFT JOIN cust_svc USING (pkgnum)
-      JOIN Links ON (
-        ( Links.Target = 'freeside://freeside/cust_main/' || CAST(cust_main.custnum AS $text) OR
-          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)
-      LEFT JOIN ObjectCustomFields ON (
-        ObjectCustomFields.ObjectId = '0' OR 
-        ObjectCustomFields.ObjectId = 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 = Tickets.Id
-      )
-    GROUP BY cust_main.custnum, ObjectCustomFieldValues.Content";
-    #warn $sql."\n";
+  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";
+      #warn $sql."\n";
+  } 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 ) {
@@ -165,4 +186,5 @@ if ( $FS::TicketSystem::system eq 'RT_Internal' ) {
       $row->{num_tickets};
   }
 }
+#warn Dumper \%num_tickets_by_priority;
 </%init>
diff --git a/httemplate/index.html b/httemplate/index.html
index ae1509610..71926aa4e 100644
--- a/httemplate/index.html
+++ b/httemplate/index.html
@@ -1,9 +1,18 @@
+<%init>my $debug = $cgi->param('debug');</%init>
+% warn time.": header.html\n" if $debug;
+%
 <& /elements/header.html, mt('Billing Main') &>
 
+% warn time.": dashboard-install_welcome.html\n" if $debug;
+%
 <& /elements/dashboard-install_welcome.html &>
 
+% warn time.": dashboard-toplist.html\n" if $debug;
+%
 <& /elements/dashboard-toplist.html &>
 
+% warn time.": fetching recently changed customers\n" if $debug;
+%
 %  my $sth = dbh->prepare(
 %    #"SELECT DISTINCT custnum FROM h_cust_main JOIN cust_main USING ( custnum )
 %    "SELECT custnum FROM h_cust_main JOIN cust_main USING ( custnum )
@@ -20,6 +29,7 @@
 %  @custnums = splice(@custnums, 0, 10);
 %
 %  if ( @custnums ) {
+%    warn time.": displaying recently changed customers\n" if $debug;
 
   <& /elements/table-grid.html &>
 
-- 
2.11.0