compliance solutions integration: import and use produce/service code catalog, RT#75262

6 files changed, 334 insertions, 139 deletions

diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm
index 99e88be..4aeebbe 100644
--- a/FS/FS/Mason.pm
+++ b/FS/FS/Mason.pm
@@ -45,7 +45,7 @@ if ( -e $addl_handler_use_file ) {
 
   use strict;
   use vars qw( %session );
-  use CGI 3.39 qw(-private_tempfiles); #3.39 for cpan#37365
+  use CGI 4.08 qw(-private_tempfiles); #4.08 for multi_param
 
   #use CGI::Carp qw(fatalsToBrowser);
   use CGI::Cookie;
diff --git a/FS/bin/freeside-compliance_solutions-import b/FS/bin/freeside-compliance_solutions-import
new file mode 100755
index 0000000..faa7f37
--- /dev/null
+++ b/FS/bin/freeside-compliance_solutions-import
@@ -0,0 +1,102 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+use FS::UID qw( adminsuidsetup );
+use Spreadsheet::ParseXLSX;
+use FS::part_pkg_taxproduct;
+
+my $user = shift or die &usage;
+my $filename = shift or die &usage;
+
+my $dbh = adminsuidsetup($user);
+$FS::UID::AutoCommit = 0;
+$FS::UID::AutoCommit = 0;
+
+my $parser = Spreadsheet::ParseXLSX->new;
+my $workbook = $parser->parse($filename);
+
+###
+# Import Product Codes
+###
+
+my %category = (
+  'C' => 'COMPUTER',
+  'G' => 'GENERAL MERCHANDISE',
+  'N' => 'NON-TAXABLE AND EXEMPT',
+  'S' => 'SATELLITE',
+  'T' => 'TELECOM',
+  'V' => 'VOIP',
+  'W' => 'WIRELESS',
+);
+
+my $num_prodcode = 0;
+my %prodcode2desc = ();
+
+my $product_sheet = $workbook->worksheet('Product Codes');
+my( $prod_min, $prod_max ) = $product_sheet->row_range();
+
+foreach my $prod_rownum ( $prod_min+1 .. $prod_max ) {
+  my $product_code = $product_sheet->get_cell($prod_rownum, 0)->value;
+  my $product_desc = $product_sheet->get_cell($prod_rownum, 1)->value;
+
+  #print "$product_code: $product_desc\n";
+
+  my $part_pkg_taxproduct = new FS::part_pkg_taxproduct {
+    data_vendor => 'compliance_solutions',
+    taxproduct  => $product_code,
+    description => join(' : ', $category{ substr($product_code,0,1) },
+                               $product_desc,
+                       ),
+  };
+  my $error = $part_pkg_taxproduct->insert;
+  if ( $error ) {
+    $dbh->rollback;# or die dbh->errstr;
+    die $error;
+  }
+
+  $prodcode2desc{ $product_code } = $part_pkg_taxproduct->description;
+
+  $num_prodcode++;
+
+}
+
+###
+# Import Service Codes
+###
+
+my $num_servcode = 0;
+
+my $service_sheet = $workbook->worksheet('Service Codes');
+my( $serv_min, $serv_max ) = $service_sheet->row_range();
+
+foreach my $serv_rownum ( $serv_min+1 .. $serv_max ) {
+  my $product_code = $service_sheet->get_cell($serv_rownum, 0)->value;
+  my $service_code = $service_sheet->get_cell($serv_rownum, 1)->value;
+  my $service_desc = $service_sheet->get_cell($serv_rownum, 2)->value;
+
+  my $part_pkg_taxproduct = new FS::part_pkg_taxproduct {
+    data_vendor => 'compliance_solutions',
+    taxproduct  => $product_code. sprintf('%03d', $service_code),
+    description => join(' : ', $prodcode2desc{ $product_code },
+                               $service_desc,
+                       ),
+  };
+  my $error = $part_pkg_taxproduct->insert;
+  if ( $error ) {
+    $dbh->rollback;# or die dbh->errstr;
+    die $error;
+  }
+  $num_servcode++;
+
+}
+
+print "Imported $num_prodcode product codes and $num_servcode service codes\n";
+
+$dbh->commit;
+
+sub usage {
+  "Usage: \n  freeside-compliance_solutions-import username \"products and services.xlsx\"\n"
+}
+
+1;
diff --git a/httemplate/browse/part_pkg_taxproduct.html b/httemplate/browse/part_pkg_taxproduct.html
new file mode 100644
index 0000000..f0fd57e
--- /dev/null
+++ b/httemplate/browse/part_pkg_taxproduct.html
@@ -0,0 +1,48 @@
+<& elements/browse.html,
+  'name_singular'  => 'tax product',
+  #'html_form'      => include('.form', $category_code),
+  'query'          => {
+                        'table'     => 'part_pkg_taxproduct',
+                        'hashref'   => $hashref,
+                        'order_by'  => 'ORDER BY taxproduct',
+                      },
+  'count_query'    => $count_query,
+  'header'         => \@header,
+  'fields'         => \@fields,
+  'align'          => $align,
+&>
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('Configuration');
+
+my $hashref = {};
+
+my $taxproduct = '%';
+
+$hashref->{taxproduct} = { op => 'LIKE', value => $taxproduct };
+
+my $count_query = "SELECT COUNT(*) FROM part_pkg_taxproduct ".
+                  "WHERE data_vendor = 'compliance_solutions' AND ".
+                  "taxproduct LIKE '$taxproduct'";
+
+my @fields = (
+  'data_vendor',
+  'taxproduct',
+  'description',
+#  'note'
+);
+
+my @header = (
+  'Vendor',
+  'Code',
+  'Description',
+#  '',
+);
+
+my $align = 'lll';
+
+
+
+</%init>
+
diff --git a/httemplate/browse/part_pkg_taxproduct/compliance_solutions.html b/httemplate/browse/part_pkg_taxproduct/compliance_solutions.html
index cf07b31..78c1b0a 100644
--- a/httemplate/browse/part_pkg_taxproduct/compliance_solutions.html
+++ b/httemplate/browse/part_pkg_taxproduct/compliance_solutions.html
@@ -1,115 +1,153 @@
-<& /elements/header-popup.html, $title &>
-<& /browse/elements/browse.html,
-  'name_singular'  => 'tax product',
-  #'html_form'      => include('.form', $category_code),
-  'query'          => {
-                        'table'     => 'part_pkg_taxproduct',
-                        'hashref'   => $hashref,
-                        'order_by'  => 'ORDER BY taxproduct',
-                      },
-  'count_query'    => $count_query,
-  'header'         => \@header,
-  'fields'         => \@fields,
-  'align'          => $align,
-  'links'          => [],
-  'link_onclicks'  => \@link_onclicks,
-  'nohtmlheader'   => 1,
-  'disable_total'  => 1,
-&>
+<& /elements/header-popup.html, 'Select tax product' &>
+
+<& '/elements/xmlhttp.html',
+     'url'  => $fsurl.'misc/xmlhttp-part_pkg_taxproduct.html',
+     'subs' => [ 'get_part_pkg_taxproduct'] &>
+
 <script>
+
 $().ready(function() {
-  var new_taxproduct = $('#new_taxproduct');
-  var new_taxproduct2 = $('#new_taxproduct2');
-//  var new_category_desc = $('#new_category_desc');
-  var new_taxproduct_desc = $('#new_taxproduct_desc');
-  var new_taxproduct_submit = $('#new_taxproduct_submit');
-
-//  new_taxproduct.on('keyup', function() {
-//    var curr_value = this.value || '';
-//    if (curr_value.match(/^\d{7}$/)) {
-//      new_taxproduct_submit.prop('disabled', false);
-//    } else {
-//      new_taxproduct_submit.prop('disabled', true);
-//    }
-//  });
-
-  new_taxproduct_submit.on('click', function() {
-    select_taxproduct( -1,
-                       new_taxproduct.val() + new_taxproduct2.val()
-                          + ' '
-//                          + new_category_desc.val()
-//                          + ':'
-                          + new_taxproduct_desc.val()
-                     );
+
+  $('#taxproduct_submit').on('click', function() {
+    select_taxproduct(
+      $('#service_code').val(),
+      $('#service_code').val() + ' ' + $('#service_code :selected').text()
+    );
   });
 });
+
 // post the values back to the parent form
 function select_taxproduct(taxproductnum, description) {
   parent.document.getElementById('<% $id %>').value = taxproductnum;
   parent.document.getElementById('<% $id %>_description').value = description;
   parent.cClick();
 }
-  
-</script>  
-<BR>
 
-Please contact <a href="http://csilongwood.com/" target="_blank">Compliance Solutions</a> for a full list of your product and service codes.<BR><BR>
+function jopt(what,value,text) {
+  var optionName = new Option(text, value, false, false);
+  what.append(optionName);
+}
+
+function category_changed(what) {
+  var category = what.options[what.selectedIndex].value;
+
+  if ( category.length == 0 ) {
+    $('#product_code').empty();
+    $('#service_code').empty();
+    $('#taxproduct_submit').prop('disabled', true);
+    return;
+  }
+
+  get_part_pkg_taxproduct(
+    'data_vendor', 'compliance_solutions', 'category', category,
+    function (data) {
+
+      $('#product_code').empty();
+      $('#service_code').empty();
+      $('#taxproduct_submit').prop('disabled', true);
+
+      var reply = JSON.parse(data);
+
+      jopt( $('#product_code'), '', 'Select product code' );
+
+      var part_pkg_taxproduct = reply.part_pkg_taxproduct;
+      for ( var s = 0; s < part_pkg_taxproduct.length; s=s+2 ) {
+        var product_code = part_pkg_taxproduct[s];
+        var description = part_pkg_taxproduct[s+1];
+        jopt( $('#product_code'), product_code, description );
+      }
+
+    },
+  );
+
+}
+
+function product_code_changed(what) {
+  var product_code = what.options[what.selectedIndex].value;
+
+  if ( product_code.length == 0 ) {
+    $('#service_code').empty();
+    $('#taxproduct_submit').prop('disabled', true);
+    return;
+  }
+
+  get_part_pkg_taxproduct(
+    'data_vendor', 'compliance_solutions', 'product_code', product_code,
+    function (data) {
+
+      $('#service_code').empty();
+      $('#taxproduct_submit').prop('disabled', true);
+
+      jopt( $('#service_code'), '', 'Select service code' );
 
-<FORM NAME="myform">
-  <FONT SIZE="+1"><B><% emt('Add tax product') %></B></FONT>
+      var reply = JSON.parse(data);
+
+      var part_pkg_taxproduct = reply.part_pkg_taxproduct;
+      for ( var s = 0; s < part_pkg_taxproduct.length; s=s+2 ) {
+        var product_service_code = part_pkg_taxproduct[s];
+        var description = part_pkg_taxproduct[s+1];
+        jopt( $('#service_code'), product_service_code, description );
+      }
+
+    },
+  );
+
+}
+
+function service_code_changed(what) {
+  var service_code = what.options[what.selectedIndex].value;
+
+  if ( service_code.length > 0 ) {
+    $('#taxproduct_submit').prop('disabled', false);
+  } else {
+    $('#taxproduct_submit').prop('disabled', true);
+  }
+}
+
+</script>  
+
+<FORM>
   <% ntable('#cccccc', 2) %>
-    <& /elements/tr-input-text.html,
-      'label'     => emt('Product code'),
-      'field'     => 'new_taxproduct',
-      'id'        => 'new_taxproduct',
-      'size'      => 4,
-      'maxlength' => 4,
+
+    <& /elements/tr-select.html,
+         label   => emt('Category'),
+         field   => 'category',
+         id      => 'category',
+         options => [ '', qw( C G N S T V W )],
+         labels  => {
+                      ''  => 'Select category',
+                      'C' => 'COMPUTER',
+                      'G' => 'GENERAL MERCHANDISE',
+                      'N' => 'NON-TAXABLE AND EXEMPT',
+                      'S' => 'SATELLITE',
+                      'T' => 'TELECOM',
+                      'V' => 'VOIP',
+                      'W' => 'WIRELESS',
+                    },
+        onchange => 'category_changed(what);',
     &>
-    <& /elements/tr-input-text.html,
-      'label'     => emt('Service code'),
-      'field'     => 'new_taxproduct2',
-      'id'        => 'new_taxproduct2',
-      'size'      => 3,
-      'maxlength' => 3,
+
+    <& /elements/tr-select.html,
+        label    => emt('Product code'),
+        field    => 'product_code',
+        id       => 'product_code',
+        onchange => 'product_code_changed(what);',
     &>
-    <& /elements/tr-input-text.html,
-      'label'     => emt('Product name'),
-      'field'     => 'new_taxproduct_desc',
-      'id'        => 'new_taxproduct_desc',
+
+    <& /elements/tr-select.html,
+        label    => emt('Service code'),
+        field    => 'service_code',
+        id       => 'service_code',
+        onchange => 'service_code_changed(what);',
     &>
+
   </table>
-%#  <input type="button" id="new_taxproduct_submit" disabled=1 value="Add">
-  <input type="button" id="new_taxproduct_submit" value="Add">
+  <BR>
+
+  <input type="button" id="taxproduct_submit" value="Select Product" DISABLED>
 </FORM>
 
 <& /elements/footer-popup.html &>
-<%shared>
-# populate dropdown
-
-#taxproduct is 7 digits: 4-digit (well, alpha) productcode + 3-digit servicecode
-# Description is also two parts, corresponding to those codes, separated with
-# a :.
-
-my (@productcodes, @servicecodes);
-foreach my $row ( qsearch({
-  table   => 'part_pkg_taxproduct',
-  select  => 'DISTINCT substr(taxproduct, 1, 4) AS productcode ',
-  hashref => { data_vendor => 'compliance_solutions' },
-  }))
-{
-  push @productcodes, $row->{productcode};
-}
-
-foreach my $row ( qsearch({
-  table   => 'part_pkg_taxproduct',
-  select  => 'DISTINCT substr(taxproduct, 4, 3) AS servicecode ',
-  hashref => { data_vendor => 'compliance_solutions' },
-  }))
-{
-  push @servicecodes, $row->{servicecode};
-}
-
-</%shared>
 <%init>
 
 die "access denied"
@@ -118,46 +156,6 @@ die "access denied"
 $cgi->param('id') =~ /^\w+$/ or die "missing id parameter";
 my $id = $cgi->param('id');
 
-my $select_onclick = sub {
-  my $row = shift;
-  my $taxnum = $row->taxproductnum;
-  my $desc = $row->taxproduct . ' ' . $row->description;
-  "select_taxproduct('$taxnum', '$desc')";
-};
-
-my @menubar;
-my $title = 'Tax Products';
-
 my $hashref = { data_vendor => 'compliance_solutions' };
 
-#my ($category_code, $taxproduct);
-#if ( $cgi->param('category_code') =~ /^(\d+)$/ ) {
-#  $category_code = $1;
-#  $taxproduct = $category_code . '%';
-#} else {
-#  $taxproduct = '%';
-#}
-my $taxproduct = '%';
-
-$hashref->{taxproduct} = { op => 'LIKE', value => $taxproduct };
-
-my $count_query = "SELECT COUNT(*) FROM part_pkg_taxproduct ".
-                  "WHERE data_vendor = 'compliance_solutions' AND ".
-                  "taxproduct LIKE '$taxproduct'";
-
-my @fields = (
-  'taxproduct',
-  'description',
-#  'note'
-);
-
-my @header = (
-  'Code',
-  'Description',
-#  '',
-);
-
-my $align = 'lll';
-my @link_onclicks = ( $select_onclick, $select_onclick );
-
 </%init>
diff --git a/httemplate/misc/taxproduct.cgi b/httemplate/misc/taxproduct.cgi
index b228493..a397f23 100644
--- a/httemplate/misc/taxproduct.cgi
+++ b/httemplate/misc/taxproduct.cgi
@@ -1,24 +1,35 @@
+<% encode_json(\@results) %>\
 <%once>
 my $conf = FS::Conf->new;
 my $vendor = $conf->config('tax_data_vendor');
 </%once>
 <%init>
+
 my $term = $cgi->param('term');
-warn "taxproduct.cgi?$term"; # XXX debug
-my $search = { table => 'part_pkg_taxproduct' };
-if ( $term =~ /^\d+$/ ) {
-  $search->{extra_sql} = " WHERE taxproduct LIKE '$term%'";
+#warn "taxproduct.cgi?$term"; # XXX debug
+
+my $search = {
+  table => 'part_pkg_taxproduct',
+  hashref => { 'data_vendor' => $vendor }
+};
+
+if ( $term =~ /^[A-Z]?\d+$/ ) {
+  $search->{extra_sql} = " AND taxproduct ILIKE '$term%'";
   $search->{order_by} = " ORDER BY taxproduct ASC";
 } elsif ( length($term) ) {
   $term = dbh->quote( lc($term) ); # protect against bad strings
-  $search->{extra_sql} = " WHERE POSITION($term IN LOWER(description)) > 0";
+  $search->{extra_sql} = " AND POSITION($term IN LOWER(description)) > 0";
   # and sort by how close to the beginning of the string it is
   $search->{order_by} = " ORDER BY POSITION($term IN LOWER(description)) ASC, LOWER(description) ASC, taxproduct ASC";
 }
+
+$search->{extra_sql} .= ' AND length(taxproduct) > 4'
+  if $vendor eq 'compliance_solutions';
+
 my @taxproducts = qsearch($search);
 my @results = map {
   { label => $_->taxproduct . ' ' . $_->description,
     value => $_->taxproductnum } 
 } @taxproducts;
+
 </%init>
-<% encode_json(\@results) %>\
diff --git a/httemplate/misc/xmlhttp-part_pkg_taxproduct.html b/httemplate/misc/xmlhttp-part_pkg_taxproduct.html
new file mode 100644
index 0000000..5e22dd8
--- /dev/null
+++ b/httemplate/misc/xmlhttp-part_pkg_taxproduct.html
@@ -0,0 +1,36 @@
+<% encode_json({ part_pkg_taxproduct => [ map { $_->taxproduct => $_->description } @part_pkg_taxproduct ] }) %>\
+<%init>
+
+#compliance solutions specific for now, since they asked for a multi-level
+# select
+
+#my $sub = $cgi->param('sub');
+
+warn join(', ', $cgi->param);
+
+my( %args ) = $cgi->multi_param('arg');
+
+my $hashref = { 'data_vendor' => $args{'data_vendor'} };
+
+my @part_pkg_taxproduct;
+if ( $args{category} =~ /^(\w)$/ ) {
+  my $category = $1;
+  @part_pkg_taxproduct = qsearch({
+    table     => 'part_pkg_taxproduct',
+    hashref   => $hashref,
+    extra_sql => " AND taxproduct LIKE '$category%' AND length(taxproduct) = 4 ",
+  });
+
+} elsif ( $args{product_code} =~ /^([A-Z]\d+)$/ ) {
+  my $product_code = $1;
+  @part_pkg_taxproduct = qsearch({
+    table     => 'part_pkg_taxproduct',
+    hashref   => $hashref,
+    extra_sql => " AND taxproduct LIKE '$product_code%' AND length(taxproduct) > 4 ",
+  });
+
+} else {
+  die 'neither category nor product_code specified';
+}
+
+</%init>