From: Ivan Kohler Date: Tue, 25 Jul 2017 03:33:01 +0000 (-0700) Subject: compliance solutions integration: import and use produce/service code catalog, RT... X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=07df8db4a07137e678a36b37070721566955295c compliance solutions integration: import and use produce/service code catalog, RT#75262 --- diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm index da2f5828a..956ea6210 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 000000000..faa7f378a --- /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 000000000..f0fd57ec3 --- /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'; + + + + + diff --git a/httemplate/browse/part_pkg_taxproduct/compliance_solutions.html b/httemplate/browse/part_pkg_taxproduct/compliance_solutions.html index cf07b3189..78c1b0a1d 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'] &> + -
-Please contact Compliance Solutions for a full list of your product and service codes.

+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' ); -
- <% emt('Add tax product') %> + 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); + } +} + + + + <% 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);', &> + -%# - +
+ +
<& /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}; -} - - <%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 ); - diff --git a/httemplate/misc/taxproduct.cgi b/httemplate/misc/taxproduct.cgi index 1533e8810..a397f2364 100644 --- a/httemplate/misc/taxproduct.cgi +++ b/httemplate/misc/taxproduct.cgi @@ -1,16 +1,20 @@ +<% encode_json(\@results) %>\ <%once> my $conf = FS::Conf->new; my $vendor = $conf->config('tax_data_vendor'); <%init> + my $term = $cgi->param('term'); -warn "taxproduct.cgi?$term"; # XXX debug +#warn "taxproduct.cgi?$term"; # XXX debug + my $search = { table => 'part_pkg_taxproduct', hashref => { 'data_vendor' => $vendor } }; -if ( $term =~ /^\d+$/ ) { - $search->{extra_sql} = " AND taxproduct LIKE '$term%'"; + +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 @@ -18,10 +22,14 @@ if ( $term =~ /^\d+$/ ) { # 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; + -<% 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 000000000..5e22dd86b --- /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'; +} + +