addProduct.pl
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
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.
* 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.
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 | |
Jacob Turino
Copyright 2006