Polarstorm Inc. Database

When testing the database access functions of the HBasic IDE and the database methods to be used in BASIC sourcecode I think it is useful to deliver a common example database. At the time I've been working with Microsoft tools like Access I always found documents working with the Northwind Traders database. Creating a database that may be used for order handling might be a good example because many people use this type of databases in business. The name of the database I have created for this purpose is polarstorm and I think the example company "Polarstorm Inc." will sell food for penguins.

After I've started to create a database I also wanted to set up a program to add or change datasets in this database. I first tried to use existing database structures of open source ERP projects but this projects either didn't use open source databases (like compiere) or they have been created with java programs or php scripts which could not simply be combined with HBasic C++ programs. I think the database editor functions should
I have therefore started to create a database structure for my own ERP functions which stores information about Vendors, Customers, Items and other supplemental data. and some editor dialogs to change the data within this database.



Currently the database structure and edit dialogs are simple but working. Additional features planned to be implemented in the future are:
The following image shows the main data stored in the database. All types of datasets may be added, changed or deleted. I have defined some steps how purchase and sales orders should be treated in this data model. This steps show the basic handling of orders in the company I' currently working for.



These steps are:

Purchase Order:
  1. Create purchase order with list of items to be ordered
  2. Send purchase order to customer (Print order)
  3. Receive items listed in the purchase order from customer
  4. Pay invoice to vendor
Sales Order
  1. Create Sales Order with entries sold
  2. Print Invoice for customer
  3. Deliver items to customer (with invoice)
  4. Pay customer for Sales Order

How to install the ERP functions

The sourcecode for the ERP program will be delivered in the erp subdirectory of the hbasic package. At this time the polarstorm database could only be created on a MySQL database server. I you want to test these functions you have to install a MySQL database server, the MySQL client package and the Qt-mysql plugin on the client where you want to start HBasic.

Required: MySQL Database Server + Qt MySQL Connection Plugin = Qt Database Access for MySQL databases.

I will call this extension of HBasic ERP program. ERP support will be included in the HBasic distribution in three differen versions:

1) Compiled as standalone program. I have used the MDI example program from the Qt library to create a simple program which starts the ERP mainwindow. The name of the program is erp. This program will be created in the erp/src subdirectory and may be started without HBasic.
2) Integrated into HBasic GUI. This will include a new Icon into the HBasic toolbar. You can open the ERP mainwindow with a mouseclick on this icon.
3) Integrate ERP mainwindow as a component in your HBasic program. This also enables the ERP editing functions in your own HBasic program. This will especially be an advantage if the ERP data has been integrated into the HBasic object model and you want to access this data from your basic program. You can find a small HBasic example program called erp.bas in the code_examples subdirectory that will use this component. After you have started this program you should see the ERP mainwindow in the dialog of the HBasic window.

The following image shows the ERP program started as a standalone application. You can see the Main Menu window on the left side. Selecting one of the menu entries will open the appropriate editor window in the ERP mainwindow. In this example the vendor editor has been opened as a second window.



First step: Create erp database

Before you can use the ERP application you have to create the required database. Currently the ERP database can only be created on a MySQL server. If you try to startup the ERP program for the first time you will normally see a popup dialog (showing you that ERP cannot find an existing database connection description).



If you acknoledge this dialog ERP will open a window where you may insert the database connection properties and create the required initial database.




In this dialog you have to insert the user and password for the ERP database server first. This properties will be saved for the next startup of the ERP program so that you don't have to insert them again. On the first startup you normally also want to create the required ERP database. Click on the "Create DB" button to create the structure of the ERP database on the DB server. The erp program will display the number of SQL statements that have been executed successfully. To recreate the ERP database for an update later open the "Admin / DB Connect" menu entry with a mouseclick in the ERP Main Menu. This will also show the Database connection dialog. Recreating the ERP DB at a later time will delete all data from the old database so be careful if you are sure that you really want to do this.

If you click on the OK button the ERP program tries to connect to an existing database with the properties used. If a valid database connection can be established all menu entries will be displayed in the main menu window.

Editing data in database tables

The main menu of the ERP application has three types of functions. At the top of this document you already saw the function to create and update sales and purchase orders. These dialogs handle the main data structures of the ERP application. Beside this SO (Sales Order) and PO (Purchase Order) dialogs there are some dialogs to edit required supplemental data like vendors, customers and items. To start the editing functions for this data you will find the appropriate menu entries in the "Master Data" menu. A mouseclick on one of this entries will open the appropriate dialog where you can add, update or delete the entries  of the selected type in the database.



As a third group of functions I tried to create some simple reports to check how reporting could be implemented. I have created C++ functions that can print or preview data from a report. Later it should be possible to create and edit a report with the HBasic editing functions and use the report in the ERP program. The last menu entry "Admin / DB-Connect" may be used to change the database connection properties and to recreate the database structure that's required in the ERP program



Further information

This paragraph should show some images how data could be changed with the dialogs from the ERP program.

Editing supplemental data in the database

There are two types of dialogs used in the ERP program. The first type opens a list of subentries in the editor dialog. This for example will be used to edit bank accounts which will be displayed in the following image. The second type of editor will display a list of subentries in the right side of the main menu dialog. Editing vendors displayed in an image at the top of this document is an example of this type of editor. If subentries like customers, orders or vendors will be displayed on the right side of the Main Menu window you should double click on the entry that you want to change.



Edit windows of the first will display the list of defined subentries in the editor dialog that pop's up after you have selected a menu entry in the Main menu window. In this edit dialogs you may select if you want to see the list of subentries or a detailed view of the currently selected entry. With a mouseclick in the toolbar or menubar you may create new entries, drop existing entries or update properties for the entry currently selected in the entry list.



Handling Purchase/Sales Orders

Sales- or Purchase Orders will be displayed on two pages. The first page display the details of the header information of the order. The second page in the dialog shows the order entries. To add new entries in this list click on the "New entry" toolbar button or click on the empty rows in the entry list view. For each entry you may change the value in the item and ordered column. Insert a valid item ID in the item column and the number of items in the ordered column.





To further handle a purchase or sales order click on the appropriate entry in the main menu like "Ship Order". This will display the list of orders which are in a correct state to be shipped. Double click on the entry of the order that should be shipped. This will open a new dialog where you only may press a button to acknowledge the selected operation for the order. Later there may be additional edit field for data that could be changed during delivery of an order. when receiving customer payment for a sales order you may select for example to which account this order should be boocked.



Display/Print Reports

To implement printing reports for the data in the database I have created some simple reporting functions that may be used in the ERP program. In the future it should be possible to create queries or reports with the HBasic visual editor functions and use them in the ERP program.

Sample output of "Print invoice" function displayed in konqueror after sending the output to a file.