*** CONFIDENTIAL ***
How To Update Vendor Data
NOTE: You have to run this from the Core server
After downloading and processing data (updating database) from ASI or MBE, you MUST run the "Price Recalculation" step. (This is documented first below.)
Price Recalculation (Post-Process)
Currently after using "arcsproducts" or "arcprod2" to update the database (described below), immediately run the "calcprices" script to update our prices on the products. (Right now, the price calculation algorithm used in "arcsproducts" and "arcprod2" is an older, obsolete one.)
Go to a command prompt (Start -> Run -> cmd), and get into the following directory:
D:\greeneto\programs\perl\ARCSproducts
Use the command of the form:
calcprices.090.pl > rpt_calcprices.{yymmdd}.txt
The rpt_calcprices.{yymmdd}.txt file will show you the results from running the program.
Keep in mind that this is something you need to do after updating the ASI or MBE data.
ASI Data Download/Processing Steps
Go to the ASI Partner Home Page and log in (with Partner ID and Password).
After login, click on "E-Partner" toward the right of the horizontal menu at the top of the page.
Toward the bottom, under "Daily csv file download" click on "download csv" button.
Enter Account ID and Password again, then click the "Get CSV File" button.
You'll see something like "CSV data generated on..." Note the date that the file was generated on. Right click on the link, choose "Save Target As", and download the file into the following directory:
D:\greeneto\programs\perl\ARCSproducts\dat
When the download is done, you are finished with the ASI site.
Set the file property of the data file to "read-only".
Modify the filename ("65067.csv") to include the "generated on" date, like this:
65067_{yymmdd}.csv [e.g., 65067_060221.csv]
(In other words, if the "generated on" date was "2/21/2006", then the filename should be "65067_060221.csv".)
Open a console window (a command line window) using Start->Run->cmd, and change the working directory to the following directory:
D:\greeneto\programs\perl\ARCSproducts
Enter the following command:
arcsproducts.pl -i dat/65067_{yymmdd}.csv -r rpt_arcsproducts.{yymmdd}.txt
(Note that version 091 of "arcsproducts" performs a price calculation, but the coding used is obsolete, and so after you run "arcsproducts.pl" you must also run "calcprices.090.pl" to recalculate the prices correctly. This is the same situation with the "arcprod2" script for MBE products. Later on I could separate the particular algorithm out as a separate subroutine file that "arcsproducts" and "arcprod2" could require.)
(Note also that the ARCS database for ASI products contains an "rt_priceqty" table that allows us to arbitrary override the price for any particular item. The way the web page works is that if a product has a price in the "rt_priceqty" table, then the web page script will override the calculated price pulled in from the "rt_product" table with the price pulled in from the "rt_priceqty" table.)
When you run "arcsproducts", if there are certain kinds of new values in the new input data, then the program will simply state this information in the report but it will stop processing and not update any data in the database. For example, if it's a new kind of "status", then you need to update the "ut_status_product" table with any new status values. Here are the tables in the database that may require manual updates (use MySQL Query Browser to make appropriate changes):
rt_vendor
rt_category1
rt_category2
ut_status_product
Note that there's a separate text file that the Perl program reads in that it uses as a "Vendor name translation table" (trans_vendor.dat). To alter input data vendor names used enter the translation in this file.
MBE Data Download/Processing Steps
Step 1
Go to the MBE Computers Home Page, click on "Reseller Price List", and log in (with username and password). Successful login takes you to here: Reseller Price List
Use View->Source (on the browser menu) to open the source code in the text editor, select all of the text (the HTML), and copy it into a new file (in the "D:\greeneto\programs\perl\ARCSproducts\dat" directory) which will become the input data for the perl program, using the name format:
mbe{yymmdd}.html [e.g., mbe060302.html]
Set the file property to "read-only".
Step 2
Go to a command prompt (Start -> Run -> cmd), and get into the following directory:
D:\greeneto\programs\perl\ARCSproducts
Enter the following command:
arcprod2.090.pl -i dat/mbe{yymmdd}.html -r rpt_mbe.{yymmdd}.txt
(where "{yymmdd}" is the date value you are using)
(Note that version 090 of "arcprod2" performs a price calculation, but the coding used is obsolete, and so after you run "arcprod2.090.pl" you must also run "calcprices.090.pl" to recalculate the prices correctly. This is the same situation with the "arcprod2" script for MBE products. Later on I could separate the particular algorithm out as a separate subroutine file that "arcsproducts" and "arcprod2" could require.)
Step 3
Open the "rpt_mbe.{yymmdd}.txt" report file with TextPad (Notepad won't work with these text files).
If the report says that the data was processed successfully, then the MBE Data update process is done (the "arcsproducts2" database has been updated with the latest MBE data). But don't forget that you need to run "calcprices.090.pl" to correctly update prices.
If there are any new categories or subcategories in the MBE data that are not in the "arcsproducts2" database, then the report will tell you which ones are missing. If there are any missing, then the "arcsproducts2" database has not been updated with the new download data. You must first use MySQL Query Browser to manually update (edit) the database with the missing categories (edit the "rt_category1" table) and/or subcategories (edit the "rt_category2" table). (How to use the MySQL Query Browser is outside the scope of this document.)
You'll need to make up a code to go with each description you're adding - be sure to make similar codes for descriptions that are similar, according to the codes already used in the table. Also check to see if a "missing" description is already in the table, but that MBE has just changed the text slightly, in which case you just need to edit the description text that is already in the table. When you finish updating the categories/subcategories, go back to Step 2