+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' ) {
+ 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 $sth = dbh->prepare($sql) or die dbh->errstr;
+ $sth->execute or die $sth->errstr;
+ while ( my $row = $sth->fetchrow_hashref ) {
+ #warn to_json($row)."\n";
+ $num_tickets_by_priority{ $row->{priority} }->{ $row->{custnum} } =
+ $row->{num_tickets};
+ }
+}