separate all 477 reports by state, #18503

[freeside.git] / httemplate / search / report_477.html

diff --git a/httemplate/search/report_477.html b/httemplate/search/report_477.html
index c9d97c5..24fee05 100755 (executable)
--- a/httemplate/search/report_477.html
+++ b/httemplate/search/report_477.html
@@ -17,6 +17,15 @@
                )
     %>
 
+    <& /elements/tr-td-label.html, 'label' => 'State' &>
+    <TD>
+      <SELECT NAME="state">
+%   foreach (@states) {
+        <OPTION VALUE="<% $_ %>"><% $_ %></OPTION>
+%   }
+      </SELECT>
+    </TD></TR>
+
     <% include( '/elements/tr-select-pkg_class.html',
                    'multiple'       => 1,
                    'empty_label' => '(empty class)',
@@ -252,4 +261,27 @@
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('List packages');
 
+my @states = (
+  qsearch({
+    'table'   => 'cust_main',
+    'select'  => 'DISTINCT(state)',
+    'hashref' => { 'country' => 'US' }, # 477 report isn't relevant elsewhere
+  }),
+  qsearch({
+    'table'   => 'cust_main',
+    'select'  => 'DISTINCT(ship_state) AS state',
+    'hashref' => { 'country' => 'US' },
+  }),
+  qsearch({
+    'table'   => 'cust_location',
+    'select'  => 'DISTINCT(state)',
+    'hashref' => { 'country' => 'US' },
+  }),
+);
+
+{
+  my %seen = map {$_->get('state') => 1} @states;
+  @states = grep $_, sort keys(%seen);
+}
+
 </%init>