QCS Delivery Truck Implementaton
The Base database running on the delivery truck laptop will be responsible for managing three things:
- Delivery Route Information
- Daily Delivery Records
- On Truck Inventory 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 sync records between the laptops database and the main IS systems database.
- The delivery and order *NO fields are encoded with the following 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 database 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.
- Transfer daily information:
For the first cut on this an XML file will be created with the 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 separate basic libraries or just separate 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<>br<>The deployment graph above represents this approach and I would likely create a single library
For the second option the functions can be setup to import or export data to the admin database using text files.
The files would be simple XML files.
For the example database, I suppose, that these can just read and write these files to a disk file. It would be bonus points to extend this transfer process to something more automated.
As I mentioned before the idea will be to have every form in the database call an event handler on opening that would check to see that some configuration script has been run. Additionally it should take the user to a main navigation screen.
The navigator screen displays the current default driver and route from the config table.
I called this a screen and not a form because I am as of the moment not sure if this would best be an embedded form, a stand alone form or a dialog?
The three button are the three major functional groups mentioned before.
The button 'Start Route' would change to 'Finish Route' once a route has begun.
'Resume Route' would be used if the database had been closed prior to finishing the days route.
'Admin' would being up a second navigation screen. This would have links to run our misc. functions.
- Transfer daily orders
Create our XML export file, save to disk and empty our daily route tables. Reset the sequencer used for building delivery and order numbers.
- Update from master
Get fresh copies of the Customer, Categories, Products and Routes table.
- Restock Inventory
This would print out a report of all the items removed from the truck.
A form would allow the driver to then enter how much stock has been replenished.
This is the form that will be opened when the Start or Resume Route buttons are clicked from the main navigator screen.