5 use FS::UID qw( adminsuidsetup );
6 use Spreadsheet::ParseXLSX;
7 use FS::part_pkg_taxproduct;
9 my $user = shift or die &usage;
10 my $filename = shift or die &usage;
12 my $dbh = adminsuidsetup($user);
13 $FS::UID::AutoCommit = 0;
14 $FS::UID::AutoCommit = 0;
16 my $parser = Spreadsheet::ParseXLSX->new;
17 my $workbook = $parser->parse($filename);
20 # Import Product Codes
25 'G' => 'GENERAL MERCHANDISE',
26 'N' => 'NON-TAXABLE AND EXEMPT',
34 my %prodcode2desc = ();
36 my $product_sheet = $workbook->worksheet('Product Codes');
37 my( $prod_min, $prod_max ) = $product_sheet->row_range();
39 foreach my $prod_rownum ( $prod_min+1 .. $prod_max ) {
40 my $product_code = $product_sheet->get_cell($prod_rownum, 0)->value;
41 my $product_desc = $product_sheet->get_cell($prod_rownum, 1)->value;
43 #print "$product_code: $product_desc\n";
45 my $part_pkg_taxproduct = new FS::part_pkg_taxproduct {
46 data_vendor => 'compliance_solutions',
47 taxproduct => $product_code,
48 description => join(' : ', $category{ substr($product_code,0,1) },
52 my $error = $part_pkg_taxproduct->insert;
54 $dbh->rollback;# or die dbh->errstr;
58 $prodcode2desc{ $product_code } = $part_pkg_taxproduct->description;
65 # Import Service Codes
70 my $service_sheet = $workbook->worksheet('Service Codes');
71 my( $serv_min, $serv_max ) = $service_sheet->row_range();
73 foreach my $serv_rownum ( $serv_min+1 .. $serv_max ) {
74 my $product_code = $service_sheet->get_cell($serv_rownum, 0)->value;
75 my $service_code = $service_sheet->get_cell($serv_rownum, 1)->value;
76 my $service_desc = $service_sheet->get_cell($serv_rownum, 2)->value;
78 my $part_pkg_taxproduct = new FS::part_pkg_taxproduct {
79 data_vendor => 'compliance_solutions',
80 taxproduct => $product_code. sprintf('%03d', $service_code),
81 description => join(' : ', $prodcode2desc{ $product_code },
85 my $error = $part_pkg_taxproduct->insert;
87 $dbh->rollback;# or die dbh->errstr;
94 print "Imported $num_prodcode product codes and $num_servcode service codes\n";
99 "Usage: \n freeside-compliance_solutions-import username \"products and services.xlsx\"\n"