Difference between revisions of "QCS Delivery Truck Implementaton"
|Line 49:||Line 49:|
Revision as of 18:50, 2 July 2006
The Base database running on the delivery truck laptop will be responsible for managing three things:
- Delivery Route Information
- Daily Delivery Records
- On Truck Invntory of Products
Delivery Route Information
The driver begins the day by selecting a route do execute. The database shall have a default route. The driver can edit a route, set a route as the default, add a new route or delete an exisitng route. The driver may add a stop to an executing route.
Daily Delivery Records
A record of the arrival time for each stop on the route is maintainted, on the laptop.
Each stop will create a delivery record.
Each delivery records will create a new Order record.
All delivery and order records will be transfered to the main IS system at the end of the day.
After a successful transfer of the delivery and order records these tables will be emptied.
Each delivery and order record wil contain an internal PK field of type integer ( IDENTIFIER ).
The databae shall generate a Delivery/Order form to be left at the customer site.
Each delivery and order record will contain a *NO (DeliveryNo, OrderNo) field of type varchar(20) - this field will be programmintaclly generated and is used to synch records between the laptops database and the main IS systems database.
- The delivery and order *NO fields are encoded with the folloiwing information:
- 3 CHAR - Order or Delivery marker ( ORD, DEL )
- 4 CHAR - Year
- 4 CHAR - Number of the day of the year
- 3 CHAR - Driver's initials
- 1 CHAR - The dash character"-"
- VARIABLE COUNT CHAR - Sequencial stop number, starting at 1 for each delivery day
- So for a driver named Andrew Torolf Jensen, on July 2, 2006 making the first stop of the day delivery and order numbers generated would be: DEL2006183ATJ-1 ORD2006183ATJ-1
- The database will utilize a sequence to generate these numbers. The sequence will be reset each day, after the successfull transfer of the days records to the main system.
On Truck Inventory of Products
At the end of a day the databse will generate a pick list for the driver, showing them what products need to be restocked on the truck. The database will allow the driver to add new products to the truck inventory, remove products from the truck or replenish products on the truck.
- Ttransfer daily information:
For the first cut on this an XML file will be created witht he delivery and order record information. The file will be saved to disk. The delivery and order table are then emptied.
- Synchronize products
My thoughts here are that ROUTE.BAS and DRIVER.BAS be either seperate basic libraries or just seperate modules in a single library.
The function UpdateOrders would be the end of day function on the laptop.
The functions in DRIVER.BAS could be implemented in either of two ways.
In the first case the routines would connect directly with the qcity.xdb database.
- UpdateOrders( )
For each order
Execute an INSERT statement to admin.Orders
execute SELECT INTO to admin.orderdetails FROM driver.orderdetails
- UpdateProducts( )
Delete all records in driver.products then insert all records from admin.products
- UpdateCustomers( )
delete from driver.customers
SELECT INTO driver.customers FROM admin.customers