## mark a section (Learn more)

#!/usr/bin/perl # # Author : BigDiver # Copyright : 2009 all rights reserved. # # Please do not remove this copyright notice and credit the original author # # import-products is free software: you can redistribute it and/or modify # it under the terms of the GNU Affero General Public License as published by # the Free Software Foundation, either version 3 of the License, or # (at your option) any later version. # # import-products is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU Affero General Public License for more details. # # You should have received a copy of the GNU Affero General Public License # along with import-products. If not, see <http://www.gnu.org/licenses/>. # # Perl Strictness, pretty vars, and version use strict; use warnings; use English qw( -no_match_vars ); use version; my $VERSION = qv('0.01'); # Needed CPAN Modules use WWW::Salesforce::Simple; use Getopt::Long; use Text::CSV_XS; use Scalar::Util qw( looks_like_number ); # Command Line processing my %cli_options = command_line(); # Parse CSV product file and setup quantities HASH # Hash Keys : Product SKU # hash Values : Product Quantity my %quantity_of = get_products_from( $cli_options{csv} ); # Login to Salesforce with user credentials my $sf = {}; eval { $sf = WWW::Salesforce::Simple->new( 'username' => $cli_options{ user }, 'password' => $cli_options{ pass }, ); }; die $EVAL_ERROR if ( $EVAL_ERROR ); die "Error: user $cli_options{user} could not login to Salesforce.com" if( !$sf ); # Check if the user asked to delete all products before importing file if ( $cli_options{ delete } ) { salesforce_delete_products_from( $sf, $cli_options{ opportunity } ); } # Get the necessary product information from Salesforce.com # We need the PricebookEntryId, UnitPrice and ProductCode for my $product_code ( keys %quantity_of ) { my $salesforce_product_ref = salesforce_get_product_wth_code($sf, $product_code); # If the product is not Active in Salesforce do not add it next if ( !$salesforce_product_ref->{ IsActive } ); if ( !$salesforce_product_ref->{ Pricebook2Id } ) { print "Product $product_code not found in Salesforce.com. Skipping \n"; next; } # Add product to opportunity print "Adding - SKU: $salesforce_product_ref->{ProductCode}" . ", Quantity: $quantity_of{$product_code}\n"; eval { $sf->create( type=> "OpportunityLineItem", OpportunityId => $cli_options{ opportunity }, Quantity => $quantity_of{ $product_code }, Description => $salesforce_product_ref->{ Name }, UnitPrice => $salesforce_product_ref->{ UnitPrice }, PricebookEntryId => $salesforce_product_ref->{ Id }->[0], ); }; die $EVAL_ERROR if( $EVAL_ERROR ); } print "Done\n"; ### End Main ### # Get products from CSV file # Arguments: $filename - the name of the CSV file # Returns: Hash with the products sub get_products_from { my ($file_name ) = @_; my @rows; my %products; my $csv = Text::CSV_XS->new ({ binary => 1 }) or die "Cannot use CSV: ".Text::CSV->error_diag (); open my $fh, "<:encoding(utf8)", $file_name or die "$file_name: $!"; while (my $row = $csv->getline ($fh)) { if ( looks_like_number $row->[1] ) { $products{ $row->[0] } = $row->[1]; } else { die "$file_name: Bad quantity $row->[1]\n"; } } $csv->eof or $csv->error_diag (); close $fh; return %products; } # Get product information from Salesforce.com based on the product code or SKU # Arguments # $sf - WWW::Salesforce object # $product_sku - Product code # Returns: Hash ref with all the product information. Check SOQL for more info sub salesforce_get_product_wth_code { my ($sforce, $product_sku) = @_; my $query = qq{SELECT Id, Name, Pricebook2Id, Product2Id, UnitPrice, IsActive, } . qq{UseStandardPrice, CreatedDate, CreatedById, LastModifiedDate, } . qq{LastModifiedById, SystemModstamp, ProductCode, IsDeleted } . qq{FROM PricebookEntry WHERE ProductCode = '$product_sku'}; my $query_result_ref; eval { $query_result_ref = $sforce->do_query($query, 1); }; die $EVAL_ERROR if( $EVAL_ERROR ); # If no records were found just return empty hash return {} if( !$query_result_ref); return $query_result_ref->[0]; } # Delete all products associated with an opportunity # Arguments # $sf - WWW::Salesforce object # $opportunity_id - Salesforce.com opportunity ID # Returns: nothing sub salesforce_delete_products_from { my ($sforce, $opportunity_id) = @_; # SOQL Query to get the product list my $query = qq{SELECT Id FROM OpportunityLineItem } . qq{WHERE OpportunityId = '$opportunity_id'}; # Send the query to Salesforce.com my $query_result_ref; eval { $query_result_ref = $sforce->do_query($query, 1); }; die $EVAL_ERROR if( $EVAL_ERROR ); # If no records were found just return return if( !$query_result_ref); # Save the product IDs that we got back my @products_to_delete = (); for my $result_ref ( @{ $query_result_ref } ) { push @products_to_delete, $result_ref->{Id}->[0]; } # Now delete all the products from the OpportunityLineItem table eval { $sforce->delete( @products_to_delete ); }; die $EVAL_ERROR if( $EVAL_ERROR ); } # Simple command line processing sub command_line { my %cli; print "\nimport-products $VERSION"; print "\nAuthor: BigDiver (bigdiver.wordpress.com)\n\n"; ## process and check cmd line args ## GetOptions(\%cli, "help", "version", "quiet", "csv=s", "opportunity=s", "user=s", "pass=s", "delete", ); if ( $cli{ help } || $cli{ version }) { usage(); exit; } # Check validity of the must have parameters if ( !$cli{ csv } || !$cli{ opportunity } || !$cli{ user } || !$cli{ pass } ) { usage(); exit(0); } return %cli; } # Just pring script usage sub usage { print <<"HERE"; Import products from CSV formated file into Salesforce.com Opportunity import-products --csv=<product-file.csv> --opportunity=<opportunityID> --user=<user name> --pass=<password> [--delete] product-file.csv MUST be a Comma Separated file with two columns (SKU, Quantity). No header line. opportunityID MUST be an existant Salesforce Opportunity to which you want to add the products from the <products-file.csv> to. user, pass Salesforce.com user name and password. delete Delete all of the opportunities products before adding any new ones. HERE }

This paste will be private.