Inventory Stock Level Script 04.02.09
Write necessary PHP scripts and MySQL DBs to process and store consolidated inventory data that will be used to update shopping cart product availability daily; with a simple web interface for administration .
Familiarity with Magento is a plus, but not required.
collect inventory stock levels from 4 vendor warehouses.
filter out items we do not sell
distinguish between in stock, out of stock, new, closeout and special order items
consolidate the 4 warehouse reports into one summary report.
use the summary to update stock levels in the Magento shopping cart
– Inputs
warehouse 1 – stock levels generated daily and available as .xls on ftp server.
warehouse 2 – stock levels generated daily and available as .cvs on ftp server.
warehouse 3 – PHP script generates cvs file.
warehouse 4 – internal and small, need simple mysql db to track stock levels.
each warehouse uses different internal part numbers that are different from the SKUs in the shopping cart. I will provide the translation tables.
each warehouse has different costs, that will effect the sale price depending on availability.
Magento can export current product stock levels and status
– Outputs
cvs/xml file with summary stock levels ready to import into Magento (see attached sample files)
Stock Status/Cost Change Report report for management to review.
archive outputs.
error alerts and reports
– Processing
filter and normalize the 4 warehouse input files
merge the 4 inputs into a summary output
consider sale price, costs, margins, flat rate shipping, status
compare with previous cost and status to see significant changes
– Resources
ftp address: ftp.gunaccessorysupply.com
login: image
password: image
<inventory.xls>
ftp address: ftp.rsrgroup.com
login: rsrdealer
password: 429Sg81
<fullfilment-inv.txt>
Magento shopping cart hosted on SimpleHelix.com
auxiliary ftp/mysql/http site hosted by Westhost.com
– Database Structures (proposed)
product db
product_id, sku, upc, name, qty, status, preferred_vendor_id, cost, map , special_price, price
sku xref db
product_id, vendor_id, part_num,
product availability db
product_id, vendor_id, datestamp, qty, status
vendor price list db
vendor_id, product_id, datestamp, cost, min_markup, special_price, start_date, end_date
vendor db
vendor_id, vendor_name
