#!/usr/bin/perl -w
#######################################################################
#
# addProduct.pl
#
# by Jacob Turino. (c) 2006
#
# See documentation for description and version history.
#
#######################################################################
use strict;
use DBI;
use Net::Amazon;
use Encode;

my $VERSION = "0.1";
my $DEBUG = 0;
my ($TEST, $testsRun, $testsFailed) = (1, 0, 0) if $ARGV[0] 
			and ( $ARGV[0] eq '-t' or $ARGV[0] eq '--test' );

my $key = ''; # enter Amazon Subscription ID here

test() if $TEST;

# if program is run from Access we're going to connect to a different db and we'll have to declare the path of all our files
my $runFromAccess = @ARGV; # equals 0 if no args

my $driver            = "ODBC";
my $db                = "TIU";
my $user              = "";
my $pass              = "";
my $output            = 'c:\perl\prog\results.txt';
my $towerSearchScript = 'c:\perl\prog\towerSearch.pl';

# if we run the program with no args, then it clearly wasn't run from access and we'll just connect to our test database.
unless ($runFromAccess) {
	$driver            = "SQLite";
	$db                = "products";
	$output            = 'results.txt';
	$towerSearchScript = 'towerSearch.pl'
}

# $0 is what you might expect $ARGV[0] to be (program name)
print "\n This is *** $0 *** (version $VERSION)." unless $runFromAccess;

# database handle
my $dbh = DBI->connect( "dbi:$driver:$db", $user, $pass, {RaiseError => 1} );

# amazon connection
my $ua = Net::Amazon->new (token => $key);

# statement handle for update query that will write the results back to the database(used in sub writeToDB())
my $updStmt = "UPDATE tblProducts SET ArtistID = ?, ProductName = ?, ListPrice = ? "
            . "WHERE UPC= ?";
my $updSth = $dbh->prepare( $updStmt );

# get upc from @ARGV (or prompt user)
print "\n\nUPC: " unless $runFromAccess;
chomp( my $upc = shift || <> );
my $genreID = shift || 25;
print "\n" unless $runFromAccess;

# search for album info on amazon
my ($artist, $album, $listPrice, $usedPrice, $newPrice) = getAlbumInfo($upc);

unless ($album) { # if amazon cannot find the upc
	print "$artist\n";
	writeToFile( "UPC not found", $upc, $genreID );
	# and we're done. (writeToFile exits)
}

print "\tArtist: $artist\n\tAlbum: $album\n\n";

# check if the artist name matches one in our list of AmazonNames
# 	(the AmazonName field stores all known names that amazon uses for a given artist
#	 for example, the AmazonName field for the artist we call "CARS" says "<THE CARS><CARS>")
my $artistID = findArtistID("AmazonName", "%<" . encode("latin1", $artist) . ">%", $artist) || 0;
# if not, test to see if it matches one of our TIU names (this should never match if the last one didn't, but it's important that we check)
unless ($artistID) {
	# if we do match here, we have to update our AmazonName field to include this name
	updateAmazonName( $artistID, $artist ) if 
		$artistID = findArtistID("ArtistName", $artist, $artist );
}

# if we got a match then we write our information to the db and exit (writeToFile exits)
if ($artistID) {
	writeToDB( $upc, $artistID, $album, $listPrice );
	
	writeToFile( "Artist Matched", $upc, $genreID, $artist, $usedPrice, $newPrice );
}

# if we're still going then we didn't get a match.
# but we can at least write the album info to the database
writeToDB( $upc, 3672, $album, $listPrice ); # 3672 is the ArtistID for ArtistName "N/A"

# run towerSearch.pl to try and match the artist	
print "Searching Tower Records...\n\n";
my @towerResults = `perl $towerSearchScript $upc $artist $album`;
chomp(@towerResults);
my ($sortAs, $others) = @towerResults;

# print tower results
print "\tBest Match = \t$sortAs\n\n" if $sortAs;
if ($others) {
	my @otherResults = split( '><', $others );
	print "\tOther results:\t";
	foreach (@otherResults) {print "$_\n\t\t\t" if $_};
	print "\n";
}

# if we didn't find a match on tower, we're done
writeToFile( "No Match" , $upc, $genreID, $artist, $usedPrice, $newPrice ) unless $sortAs;

# otherwise, we should try and match the tower name against our list of names.
# this probably won't ever match, but it's important to check (if we have to create a new artist, we may use this name,
# so we have to first check that this name isn't already used)
$artistID = findArtistID("ArtistName", $sortAs, $artist);

# if that didn't work check to see if we can remove ", The" from the end of the name (as in "Beatles, The") and then try again.
unless ($artistID) {
	if ($sortAs =~ s/, The//i) {
		$artistID = findArtistID("ArtistName", $sortAs, $artist);
	}
}

# if we found a match we can't assume it is correct and write the info to the db b/c the towerSearch isn't very accurate
# instead, we record that artistID in our results as a possible match and Access will ask the user whether to accept the match.
if ($artistID) {
	writeToFile( "No Match", $upc, $genreID, $artist, $usedPrice, 
				 $newPrice, $sortAs, $artistID );
}

writeToFile( "No Match" , $upc, $genreID, $artist, $usedPrice,
			 $newPrice, $sortAs );

#---------------- Subroutines -------------------------------------------------------------------------------------------------------------------
# writeToDB( $upc, $artistID, $albumName, $listPrice ): write information to db for product where UPC = $upc
# returns: nothing
sub writeToDB {
	my ( $targetUPC, $artistID, $productName, $listPrice ) = @_;
	$productName = encode("latin1", $productName); # "latin1" is the character set that works with Access
	
	my $x;
	while (1) {
		# check that this album name isn't already being used for this artist
		# if we already have an entry for "Abbey Road" by artist 3469 ("BEATLES"), change product name to "Abbey Road [Amazon 1]"
		my $stmt = "SELECT ProductName FROM tblProducts "
				 . "WHERE ArtistID =  $artistID AND ProductName = "
				 . $dbh->quote($productName);
		
		# if we didn't find anything, we're done
		last unless $dbh->selectrow_array( $stmt );
		$x++;
		$productName .= " [Amazon $x]";
	}
	
	print "Writing to tblProducts where UPC = $targetUPC:\n\n";
	print "\t$artistID, $productName, $listPrice\n\n";
	# execute the query that was prepared at the start of the program with the terms that we want to write
	my $rows = 
		$updSth->execute( $artistID, $productName, $listPrice, $targetUPC );
	print " Updated $rows rows where UPC = $targetUPC.\n\n";
}
# updateAmazonName( $artistID, $amazonName ): add $amazonName to AmazonName field in tblArtists 
#	where ArtistID = $artistID.
#	e.g,  if artist 3469 had "<THE BEATLES>" in it's AmazonName field and we call updateAmazonName( 3469, "BEATLES" );
#	then the AmazonName field will be changed to "<THE BEATLES><BEATLES>"
# returns: nothing
sub updateAmazonName {
	my ($artistID, $artist) = @_;
	$artist = encode("latin1", $artist);
	
	print "Updating AmazonName for ArtistID: $artistID\n";
	my $stmt = "UPDATE tblArtists SET AmazonName = [AmazonName] & '<$artist>' "
             . "WHERE ArtistID=$artistID";
	$dbh->do( $stmt );
}
# getAlbumInfo( $upc ): search $upc on amazon
# returns: $artist, $album, $listPrice
sub getAlbumInfo {
	my ($upc) = @_;

	# Get a request object
	my $response = $ua->search(upc => "$upc", mode => "music") ;

	unless ($response->is_success()) {
		return "Error: " . $response->message() . "\n";
	}
	
	my %results = %{$response->properties()};
	
	my ($artistsRef,$album,$listPrice,$usedPrice,$newPrice) = 
			@results{'artists','ProductName','ListPrice','UsedPrice','ThirdPartyNewPrice'};
			
	my @artists = @{$artistsRef};
	
	# Sometimes a cd will have multiple artists, so amazon returns a list. 
	# We don't want more than one artist, so we set artist to "Various Artists"
	my $artist;
	if (scalar(@artists) == 1) {
		($artist) = @artists;
	} else {
		$artist = "Various Artists";
	}
	
	# amazon uses charset iso-8859-1, so we need to change it to utf8, which perl uses
	$artist = decode("iso-8859-1", $artist);
	$album  = decode("iso-8859-1", $album);
	
	return ($artist, $album, $listPrice, $usedPrice, $newPrice);
}
# findArtistID( $fieldToSearch, $patternToMatch, $artistName ): finds an ArtistID in the db
# returns: $artistID
sub findArtistID {
	my ($fieldName, $searchTerm, $artist) = @_;
	my $id = 0;
	
	# hash that will store the results
	my %possibleMatches;
	
	my $stmt = "SELECT ArtistID, ArtistName FROM tblArtists "
	         . "WHERE $fieldName Like " . $dbh->quote($searchTerm);
	
	# if the artist is "Various Artists", we have a different "V/A" artist for each genre, so this query will find the right one.
	if ($artist eq "Various Artists") {
		$stmt = "SELECT ArtistID FROM tblArtists "
			  . "WHERE ArtistName Like 'V/A%' AND GenreID=$genreID";
	}
	
	my $sth = $dbh->prepare( $stmt );
	
	$sth->execute();
	
	# write the results to %possibleMatches
	while ( my ( $artistID, $artistName ) = $sth->fetchrow_array ) {
		$possibleMatches{$artistID} = $artistName;
	}
	my $numMatches = scalar( keys(%possibleMatches) );
	# if we matched 1 and only 1 ArtistID, then we're done.
	if ($numMatches == 1) {
		($id) = keys(%possibleMatches);
		print "ArtistID = $id\n\n";
		return $id ;
	# if we matched more that one ArtistID, then we need to write the results to a file and let Access prompt the user to match the artist
	} elsif ($numMatches > 1) {
		open( OUTPUT,">$output" ) or die "Can't open output '$output': $!";
		print OUTPUT "0\tMultiple results for '$artist'\n";
		foreach my $id ( keys %possibleMatches ) {
			print OUTPUT "$id\t$possibleMatches{$id}\n";
		}
		close OUTPUT;
		system( 'C:\perl\prog\frmSearchResults.MAF' );
		die "Multiple Artists Matched\n";
	}
	# if no match...
	return 0; 
}
# writeToFile( $messageText, $upc, $genreID, $amazonName, $usedPrice, $newPrice, $towerName, $artistID ):  writes the results
#	 of our search to a text file that will be read by the Access database, then exits. Args are all optional, but must appear in
#	 the order shown (e.g., $amazonName must be the 4th arg if it appears at all).
# returns: nothing
sub writeToFile {
	my @args = @_;
	open(OUTPUT,">$output") or die "Can't open output '$output': $!";
	my $x = 0;
	# output file should look like this:
		#  0	Message
		# -1	UPC
		# -2	GenreID
		# -3	AmazonName
		# -4	Used Price
		# -5	New Price
		# -6	TowerName
		# -7	ArtistID (for best match)
	# the left column will be used as an ID field when we need to look up values in Access.
	# We use negative numbers b/c it may be set up later to include other possible ArtistID matches and ArtistIDs are positive numbers
	map { tr/"/'/ if $_; print OUTPUT $x-- . "\t$_\n" } ( @args ); # why map instead of foreach? No reason.
	close OUTPUT;
	# open frmSearchResults in Access to let Access know we're done.
	system( 'C:\perl\prog\frmSearchResults.MAF' ) if $runFromAccess;
	exit;
}
# test(): I honestly have no idea what this sub does, I just found it on the web and I thought it would be cool to include it.
# returns: ???
sub test {
	print "\nTime for some tests!!!\n\n";
		
	my %dbs =  ( Access => { driver => 'ODBC',
	                         db     => 'TIU',
	                         user   => '',
	                         pass   => '',
                             },
	             SQLite => { driver => 'SQLite',
	                         db     => 'products',
	                         user   => '',
	                         pass   => '',
                           },
	           );
	
	foreach my $key ( sort keys %dbs ) {
		my $driver = $dbs{$key}{driver};
		my $db     = $dbs{$key}{db};
		my $user   = $dbs{$key}{user};
		my $pass   = $dbs{$key}{pass};
	
		my $dbh = DBI->connect( "dbi:$driver:$db", $user, $pass );
		ok( defined $dbh, "connected to $key");
		
		next unless $dbh;
		
		foreach my $table ( 'tblProducts', 'tblArtists' ) {
			my $rows = $dbh->do( "SELECT * FROM $table" );
			ok( $rows, "found $table" );
		}
		
		my ($artistName) = $dbh->selectrow_array( "SELECT ArtistName FROM tblArtists "
                                                . "WHERE ArtistID = 3672" );
		ok( $artistName eq 'N/A', 'ArtistName = "N/A" WHERE ArtistID = 3672');
	}
	
	my $ua = Net::Amazon->new (token => $key);
	ok( defined $ua, 'connected to Amazon');
	
	foreach my $towerSearchScript ('c:\perl\prog\towerSearch.pl', 'towerSearch.pl') {
		my $towerResults = `perl $towerSearchScript 0 "the beatles" "abbey road"`;
		ok( $towerResults, "towerSearchScript works at $towerSearchScript");
	}
	print "$testsRun tests run.\n";
	print "$testsFailed tests failed.\n";
	exit;
}
# ok( $value, $message ): replacement for ok() from Tests::Simple
# returns: 1 if passed.
sub ok {
	my ($passed, $message) = @_;
	$testsRun++;
	print "ok $testsRun - $message\n" and return 1 if $passed;
	$testsFailed++;
	print "not ok $testsRun - $message\n";
	return 0; 
}

__END__

=head1 NAME

addProduct.pl

=head1 SYNOPSIS

Requires: Net::Amazon, DBI, DBD::ODBC, DBD::SQLite, towerSearch.pl, appropriate databases

To test installation:

> addProduct.pl -t

This program is designed to be run from my Microsoft Access database and is called like so:

	Shell "C:\perl\bin\perl.exe C:\perl\prog\addProduct.pl " & NewData & " " _
            & Forms![frmStoreInventory]![cboGenre], vbHide

which is essentially the same as:

	>perl addProduct.pl <upc> <genreID>

The program can also be run on a test SQLite database (included) by calling it with no arguments.

	>perl addProduct.pl

or maybe just

	>addProduct.pl


=head1 DESCRIPTION

This is a program I wrote to help take inventory of the TIU stores. The inventory process is handled mainly in Access, which already has product info for many of the CDs in the stores. The program is designed to help find info for those CDs that aren't already in the database (used CDs bought from customers). Basically, someone will be going around with a barcode scanner and a laptop running the Access database. As they scan UPCs, Access will add the appropriate products to
the inventory. If a UPC isn't found, Access will launch this script to try and find product information on Amazon. If this script can match the UPC on Amazon and can match the artist name returned by Amazon to an ArtistID in the database, it will write its results directly to the Access database and the user can continue scanning barcodes. If the script does match the UPC, but can't match an ArtistID, it will write what info it can to the database (ProductName [album], ListPrice) and create a text file with whatever information couldn't be written to the database. Regardless of the script's success, it always ends by writing some results to a text file and opening a (hidden) form in Access that will read the results and proceed accordingly (prompting the user if necessary).

Sample output:

	0	No Match
	-1	759718121124
	-2	25
	-3	Team Dresch
	-4	$5.00
	-5	$10.88
	-6	Team Dresch

The numbers in the left column will be treated as an ID field in Access. 0 is the message, telling Access how to respond. In this case, it says "No Match", which means that it couldn't match an ArtistID and Access will have to ask the user how to proceed. -1 is the UPC, just as it was passed in from Access. -2 is the GenreID, again just what was passed in. -3 is the artist name as returned by our Amazon search. -4 is the lowest price that the product is selling for in Amazon's used section. If this number is higher than a certain value (designated by the user), Access will alert the user that the CD they just scanned is valuable and should be listed on Amazon rather than trying to sell it in the store. -5 is the same as -4, only for third party new price. -6 is the name returned by towerSearch.pl. If we create a new product, this may be the name we use for ArtistName, because Tower, like TIU, lists "David Bowie" as "Bowie, David". -7, if present, displays the ArtistID that matches the Tower name. Access will ask the user if the match is acceptable.


=head1 VERSION HISTORY

 * 0.8	4/26/2006	Cleaned things up a bit and added a few more comments.
 
 * 0.7  4/19/2006  	More tests added and I wrote my own ok() routine b/c Tests::Simple
					was looking for tests every time, even when I didn't run the test()
					routine, and it would announce "# No tests run!" whenever the script
					terminated. Also, finally figured out (by trial and error) how to
					encode my text so that it will show up correctly in Access. Turns
					out "latin1" works. Doesn't make any sense to me, though, b/c I thought
					ISO-8859-1 (which is how it originally comes from Amazon) was the 
					same as Latin 1.

 * 0.6  4/16/2006  	New stuff: we now look up listPrice and write that to the db. We also
					look up the thirdPartyNew price and the usedPrice and write them to our
					results file so that Access can monitor them and alert the user if 
					anything they scan is selling for a lot of $ on amazon.
 
 * 0.5  4/12/2006  	Sweet friggin' tests! Now we can test the script before we run it by
					using the "-t" arg. Tests to see if it can connect to each db, if it
					can find the necessary tables, if it can connect to amazon and if it
					can run the towerSearch script. Also, added the final step where now 
					we write instructions to a text file when we're done and lauch a form
					in Access the will read the instructions and proceed accordingly.

 * 0.4  4/5/2006  	Added SQLite abilities so I can test the script on a SQLite copy of
					my Access db. Also, now I've got it launching my towerSearch script
					(somewhat modified version of something I wrote before break) if it
					can't find a match in the database. If the name returned by amazon
					doesn't match anything in the db, then we search TowerRecords to see
					if they use a different name that we can match against an artist in our
					db. If this doesn't match, we'll probably need to create a new artist
					in the db.

 * 0.3  3/27/2006  	I know this may be frowned upon, but I thought it would be much easier
					if I combined my two scripts, rather than using one to call the other,
					so I did (and it _is_ easier now). Also, I've pretty much got all the
					basic functionality that I'll need now. It takes a UPC as an argument,
					looks it up on amazon, tries to match the artistID, then writes the
					results (artistID and productName) to the database, using "1" as an
					artistID if it couldn't find a match.

 * 0.2  3/20/2006  	Now I have a semi-functional program that takes a UPC as an argument,
					Finds the UPC on amazon (launching a separate script) and then tries
					to match the artist name that amazon returns to an artistID in the db.
					Switched to DBI because I had better docs for it and because I can use
					it on more databases.

 * 0.1  3/13/2006  	Program created. Uses Win32::ODBC to connect to the Access Database.
					Runs SELECT and UPDATE queries, but doesn't do anything real special yet.

=head1 SEE ALSO

I<Programming the Perl DBI> by Alligator Descartes & Tim Bunce.

Man page for DBI:
	http://search.cpan.org/~timb/DBI-1.50/DBI.pm

"Speeding up the DBI":
	http://www.perlmonks.org/?node=273952

Man page for Net::Amazon:
	http://search.cpan.org/~mschilli/Net-Amazon-0.34/lib/Net/Amazon.pm



=head1 AUTHOR

Jacob Turino

Copyright 2006

=cut
