QCS Delivery Truck Implementaton

From Apache OpenOffice Wiki
Revision as of 17:30, 3 July 2006 by DrewJensen (Talk | contribs)

Jump to: navigation, search

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.

Misc. Functions

  • 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

Schema

QCS Route Schema 1.png

Deployment environment

QCS Deployment.png

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<>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.

For this option I would create two library files as showen here. QCS Deployment 2.png


Startup Screen

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.

QCS Navigator screen.png

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?

Personal tools