Difference between revisions of "QCS Delivery Truck Implementaton"

From Apache OpenOffice Wiki
Jump to: navigation, search
(Misc. Functions)
(cleanup, spelling)
 
(11 intermediate revisions by 2 users not shown)
Line 1: Line 1:
The Base database running on the delivery truck laptop will be responsible for managing three things:
+
The [[Base]] database running on the delivery truck laptop will be responsible for managing three things:
  
 
*Delivery Route Information
 
*Delivery Route Information
 
*Daily Delivery Records
 
*Daily Delivery Records
*On Truck Invntory of Products
+
*On Truck Inventory of Products
  
===Delivery Route Information===
+
== Delivery Route Information ==
 
The driver begins the day by selecting a route do execute.
 
The driver begins the day by selecting a route do execute.
 
The database shall have a default route.
 
The database shall have a default route.
Line 11: Line 11:
 
The driver may add a stop to an executing route.
 
The driver may add a stop to an executing route.
  
===Daily Delivery Records===
+
== Daily Delivery Records ==
 
A record of the arrival time for each stop on the route is maintainted, on the laptop.<br>
 
A record of the arrival time for each stop on the route is maintainted, on the laptop.<br>
 
Each stop will create a delivery record.<br>
 
Each stop will create a delivery record.<br>
Line 19: Line 19:
 
Each delivery and order record wil contain an internal PK field of type integer ( IDENTIFIER ).<br>
 
Each delivery and order record wil contain an internal PK field of type integer ( IDENTIFIER ).<br>
 
The databae shall generate a Delivery/Order form to be left at the customer site.<br>
 
The databae shall generate a Delivery/Order form to be left at the customer site.<br>
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.<br>
+
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 folloiwing information:
+
 
 +
*The delivery and order *NO fields are encoded with the following information:
 
**3 CHAR - Order or Delivery marker ( ORD, DEL )
 
**3 CHAR - Order or Delivery marker ( ORD, DEL )
 
**4 CHAR - Year
 
**4 CHAR - Year
Line 30: Line 31:
 
**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.
 
**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.
  
===On Truck Inventory of Products===
+
== Misc. Functions ==
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.
+
*Transfer daily information:
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.
+
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.
 
+
===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.
 
The delivery and order table are then emptied.
  
 
*Synchronize products
 
*Synchronize products
  
===Schema===
+
== Schema ==
 +
 
 +
[[Image:QCS_Route_Schema_1.png]]
 +
 
 +
== Deployment environment ==
 +
 
 +
[[Image:QCS_Deployment.png]]
 +
 
 +
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. <br>
 +
In the first case the routines would connect directly with the qcity.xdb database.
 +
*UpdateOrders( )<br>For each order<br>Execute an INSERT statement to admin.Orders<br>execute SELECT INTO to admin.orderdetails FROM driver.orderdetails
 +
*UpdateProducts( )<br> Delete all records in driver.products then insert all records from admin.products
 +
*UpdateCustomers( )<br>delete from driver.customers<br>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.<br>The files would be simple [[XML files]].<br> 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.
 +
[[Image: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.
 +
 
 +
[[Image:QCS_Navigator_screen.png]]
 +
 
 +
The navigator screen displays the current default driver and route from the config table.<br>
 +
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.<br>
 +
The button 'Start Route' would change to 'Finish Route' once a route has begun.<br>
 +
'Resume Route' would be used if the database had been closed prior to finishing the days route.<br>
 +
'Admin' would being up a second navigation screen. This would have links to run our misc. functions.
 +
*Transfer daily orders<br>
 +
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<br>
 +
Get fresh copies of the Customer, Categories, Products and Routes table.
 +
*Restock Inventory<br>
 +
This would print out a report of all the items removed from the truck.<br>
 +
A form would allow the driver to then enter how much stock has been replenished.<br>
  
 +
== Route Screen ==
 +
This is the form that will be opened when the Start or Resume Route buttons are clicked from the main navigator screen.
  
 +
[[Image:QCS_Route_screen.png]]
  
  
[[Category:Base Example]]
+
[[Category:Database Scenarios]]

Latest revision as of 03:50, 12 November 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 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.

Misc. Functions

  • 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

Schema

QCS Route Schema 1.png

Deployment environment

QCS Deployment.png

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.

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?

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.

Route Screen

This is the form that will be opened when the Start or Resume Route buttons are clicked from the main navigator screen.

QCS Route screen.png

Personal tools