We have a database in MSSql approximately 80 GB in size that was created and populated by Sap Business one. It contains all of our information on Customers, Orders, Purchase orders, Invoices, and Inventory.


Problem the Default SAP interface is too cumbersome for our Customer Service department to quickly and easily retrieve information.


Desired solution a simplified web browser based interface written in PHP that access the MSSql database.

Needs to be able to
Record sort and assign permissions to individual people.
Search for information by customer (name or identification number)
Search for customer order (our order number or their purchase order)
Check available inventory levels by searching for item based on a name comparison  and figuring current availability based on what is on order and what is in the warehouse and what is reserved for existing orders



Customer Page
Upon finding the desired customer it needs to display all pertinent information about the customer including name number address and available credit lines.
This also needs a dedicated notes section that  will be stored in an  Mysql database  this page also needs to have an easily to access and sorted list of existing and past orders  that will be links to the orders they refer to.



Order Page
Upon reaching the order page customer it needs to display all pertinent information about the order including billing  name  phone number, address as well as the same information on the ship to location. It should also include the current status of the order and  a list of all the  ordered items and quantities and dollar amounts for the items. This page will also need to display shipping type and contact info for the customer service rep. after the order is completed and invoiced the order page needs to pull data from the invoice to display the exact and final amounts. Once the order has been shipped it needs to have a link to a separate tracking page This page also needs to have an easily to access and sorted list of existing and past orders  that will be links to the orders they refer to.

This also needs a dedicated notes section that  will be stored in an  Mysql database

Additionally the order page needs section to read a directory that houses any related documents (saved proofs or emailed Pos or saved Emails. This section will provide links to individual files in the directory and supply  an upload form to users who have permission to attach files.



Tracking page

This page will need to access the Sap database and retrieve tracking numbers for existing orders and provide a field to enter an email address and a send button that will send an email via the existing exchange server  to the entered email address from the email address of the person logged in.

This page also needs to provide links both on the page and in the email to the proper shipping companys tracking section directly to the tracked item.



Supplemental tools

Open Orders
This page needs to list all the open orders for each customer service rep based on the logged in user and offer all results and a filter section for management

Address required
This page needs to list all the open orders that ship within 7 days that still need a shipping address based on the logged in user and offer all results and a filter section for management


Art required
Same as address required but checks for orders that require art to be sent from the customer



Inventory System
This system needs to be web based
Needs to be stored in a mysql database
Needs to track incoming and outgoing items
Needs to print barcode labels from a roll paper based  label printer.
Labels must contain barcodes to identify boxes
Barcodes must be scannable via keyboard bridge pen scanner to enter box id into a search form
Needs to track contains of each box in inventory and quantity their in.
Needs to maintain same naming structure as the SAP database
Needs to track location in the warehouse of each box and quantity of items in each location (can be calculated on the fly)
Needs to be searchable to find total quantities  of single items or categories of items
Needs to export to Excel a report of all incoming and outgoing transactions of all items (as well as having this available via browser)
Needs to export to excel a report of all totals (as well as having this available via browser).
Needs to export to Excel, a report of comparisons between its own inventory totals, and SAP showing the items that have substantial difference in inventory levels.
Needs to generate a report that displays both its own inventory levels saps inventory levels the amount on order the last evaluated price and last purchased price of each item as well as referenceing another database of product->raw goods yield ratios to determine how many of each item can be manufactured based on raw goods inventory.
Needs to generate 3 different  usage reports based on date range for  3 different departments up pull from inventory