Supplier Database Sync from Excel File on FTP

Project Specifications for FTP Excel/Database Sync I. Server Settings: Operating System: Linux [login to view URL] PHP Version: 4.4.7 (Zend: 1.3.0) Database: MySQL 4.1.22-standard Shopping Cart: Zen Cart 1.3.7.1 Website to be completed for: [login to view URL] II. Details: Our supplier keeps a file in .xls format in a password protected directory that is updated every two weeks and is assigned a new password to access it. The products in the list and in our product database need to be synchronized every two weeks to disable discontinued products and adjust price changes that take effect from the .xls file. We also will have a margin table lookup file that will have to be cross referenced in order to determine the correct pricing. Below is an example of the process: Get file from supplier directory -> Apply prices based on margins set in lookup file -> Set prices accordingly and disable discontinued items in Zen Cart Example 1: After automatically logging in and moving the .xls file to our server, the synch process should begin. A product is listed in the .xls file, and has a base price set at $19.99. It has a product number of ‘123456’. The product has a manufacturer code of ‘123’. During the synchronization, the script should analyze the manufacturer, then cross reference with our margin table by manufacturer code, (for this example manufacturer code ‘123’ has a markup of 15%.) It would then apply the markup to the base price and set the price of the product on Zen Cart at $22.99 (rounding up to the nearest $0.99). It would then move to the next product. Example 2: The script comes across a product in the .xls file with a note that says ‘DISC’. It would automatically disable the product in Zen Cart. III. Important Fields: Supplier .xls file – SUPPLIER_NUMBER = the identifier for a specific manufacturer, which will be used to apply margins STOCK_CODE = this identifies the product within the Zen Cart product database SELLING_PRICE = this is the base price that will be adjusted by the margin lookup file DISCONTINUED = if blank, this product is still current. If it contains the value ‘DISC’, it should be taken off of the site NOTE: If there are any products that are on the .xls sheet, but not in the Zen Cart already, it should output them into a single .xls file Margin Table Lookup File - SUPPLIER_NUMBER = the identifier for a specific manufacturer, which will be used to apply margins MARGIN = This will be a number between 0-100 that will determine what needs to be added to a product. (equation: roundtonearest99cents(baseprice x 1.(MARGIN)) = newprice) NOTE: The Margin Table Lookup File only contains Suppliers and their margins. Individual products will not be included in this file. IV. Multiple Site Integration: This project also needs to be expandable to other websites that we have, so any changes that are made to files or settings in the Zen Cart admin must be able to be duplicated into another Zen Cart site.