skip to Main Content
tel.: 0049-89-49053501 support@wptools.de

WPCubed Calculus: The Datamodule

As described in the previous topic SQLight is used as the database.  Delphi XE7 Professional includes the FireDAC components which serve as interface to the database. Since the interface components do not allow the automatic creation of tables and fields from the FieldDefinitions a SQL CREATE script is used instead. This script will be executed when a new database is generated.

All the tables use one field with the name UID as primary index field. Relational tables use fields with the name “…_ID” to connect to the main table, i.e. “CUSTOMER_ID” in the ORDER table. I use an INTEGER PRIMARY KEY AUTOINCREMENT as primary index field which provides fast lookups with SQLLight. (There are no secondary indexes defined in the database yet, to optimize sorting performance they can be added anytime later).

This is an excerpt of the SQL script which creates the tables for the WPCubed Calculus Multi-Demo. As mentioned in the introduction, only the bare minimum of tables and fields for this tasks are used by the application to not obscure what is essential for the task:

CREATE TABLE "COUNTRIES" (
    "UID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "NAME" varchar(60),
    "EU" INTEGER,
    "VAT_RATE_REDUCED" REAL,
    "VAT_RATE_REDUCED_LOW" REAL,
    "VAT_RATE" REAL,
    "CODE" varchar(6),
    "DATE" DATE
);

CREATE TABLE "CUSTOMERS" (
    "UID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "NAME" varchar(100), // We only have a limited number of adress
    "ADR1" varchar(100), // field in this demo application
    "ADR2" varchar(100),
    "CITY" varchar(100),
    "STATE" varchar(100),
    "EMAIL" varchar(100),
    "COUNTRY_ID" INTEGER,
    "VAT_ID" varchar(20), // this is the VAT tax number
    "COMMENT" varchar(100),
    "REBATE" REAL
);

CREATE TABLE "PRODUCTS" (
    "UID" INTEGER PRIMARY KEY AUTOINCREMENT,
    "NAME" varchar(100),
    "DESCRIPTION" varchar(200),
    "PRICE" REAL,
    "VAT_SELECT" INTEGER // Select VAT, 0=normal, 1=reduced
);

CREATE TABLE "ORDERS" (
    "UID" INTEGER PRIMARY KEY AUTOINCREMENT,
    "ORDER_DATE" DATE,
    "ORDER_DATE_YEAR" INTEGER,
    "ORDER_DATE_MONTH" INTEGER,
    "CUSTOMER_ID" INTEGER,
    "COUNTRY_ID" INTEGER,
    "VAT_ID" varchar(100),
    "EUTRADE" INTEGER,
    "REFERENCE" varchar(100),
    "CUSTOMER_ADR" varchar(300),
    "VAT_RATE" REAL,   
    "VAT_RATE_REDUCED" REAL,
    "VAT_RATE_REDUCED_LOW" REAL,
    "VOID" Integer
);

CREATE TABLE "ORDERITEM" (
    "UID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "ORDER_ID"    INTEGER NOT NULL,
    "CUSTOMER_ID" INTEGER NOT NULL,
    "PRODUCT_ID"  INTEGER NOT NULL,
    "VAT_SELECT"  INTEGER NOT NULL,
    "COUNT" REAL,
    "TEXT" varchar(100),
    "UNIT_PRICE" REAL,
    "REBATE_PERCENT" REAL,
    "REBATE_ABSOLUT" REAL,
    "PAYMENT_ID" INTEGER
);

These are the main component in the data module to set up the data connection:

MD_DataModule1

As described in the introduction the demo was created to also integrate value added tax handling (VAT). To do so, I located the VAT rates (http://ec.europa.eu/taxation_customs/taxation/vat/how_vat_works/rates/index_en.htm) and created a constant record array to initialize the country table. Please use this table with care since the values may be outdated in the meantime.

type TEUCountry = record
    name, en_name, code : String;
    red1, red2, vat : Single;
end;

// VAT table of July 2014 - Source:
// http://ec.europa.eu/taxation_customs/taxation/vat/how_vat_works/rates/index_en.htm
// We did not copy the "parking rate" and the "super reduced rate"


const EUCountries : array[1..28] of TEUCountry

= ( (name:'Belgien';  en_name:'Belgium';  code:'BE'; red1:6; red2:12; vat:21),
(name:'Bulgarien';  en_name:'Bulgaria';  code:'BG'; red1:9; red2:9; vat:20),
(name:'Tschechische Republik';  en_name:'CzechRepublic';  code:'CZ'; red1:15; red2:15; vat:21),
(name:'Dänemark';  en_name:'Denmark';  code:'DK'; red1:25; red2:25; vat:25),  // DK does not have a reduced rate!
(name:'Deutschland';  en_name:'Germany';  code:'DE'; red1:7; red2:7; vat:19),
(name:'Estland';  en_name:'Estonia';  code:'EE'; red1:9; red2:9; vat:20),
(name:'Griechenland';  en_name:'Greece';  code:'EL'; red1:6.5; red2:13; vat:23),
(name:'Spanien';  en_name:'Spain';  code:'ES'; red1:10; red2:10; vat:21),
(name:'Frankreich';  en_name:'France';  code:'FR'; red1:5.5; red2:10; vat:20),
(name:'Kroatien';  en_name:'Croatia';  code:'HR'; red1:5; red2:13; vat:25),
(name:'Irland';  en_name:'Ireland';  code:'IE'; red1:9; red2:13.5; vat:23),
(name:'Italien';  en_name:'Italy';  code:'IT'; red1:10; red2:10; vat:22),
(name:'Zypern';  en_name:'Cyprus';  code:'CY'; red1:5; red2:9; vat:19),
(name:'Lettland';  en_name:'Latvia';  code:'LV'; red1:12; red2:12; vat:21),
(name:'Litauen';  en_name:'Lithuania';  code:'LT'; red1:5; red2:9; vat:21),
(name:'Luxemburg';  en_name:'Luxembourg';  code:'LU'; red1:6; red2:12; vat:15),
(name:'Ungarn';  en_name:'Hungary';  code:'HU'; red1:5; red2:18; vat:27),
(name:'Malta';  en_name:'Malta';  code:'MT'; red1:5; red2:7; vat:18),
(name:'Niederlande';  en_name:'Netherlands';  code:'NL'; red1:6; red2:6; vat:21),
(name:'Österreich';  en_name:'Austria';  code:'AT'; red1:10; red2:10; vat:20),
(name:'Polen';  en_name:'Poland';  code:'PL'; red1:5; red2:8; vat:23),
(name:'Portugal';  en_name:'Portugal';  code:'PT'; red1:6; red2:13; vat:23),
(name:'Rumänien';  en_name:'Romania';  code:'RO'; red1:5; red2:9; vat:24),
(name:'Slowenien';  en_name:'Slovenia';  code:'SI'; red1:9.5; red2:9.5; vat:22),
(name:'Slowakei';  en_name:'Slovakia';  code:'SK'; red1:10; red2:10; vat:20),
(name:'Finnland';  en_name:'Finland';  code:'FI'; red1:10; red2:14; vat:24),
(name:'Schweden';  en_name:'Sweden';  code:'SE'; red1:6; red2:12; vat:25),
(name:'Vereinigtes Königreich';  en_name:'United Kingdom';  code:'UK'; red1:5; red2:5; vat:20) );

Adding the EU countries to the table is easy:

procedure TWPDataModule.AddEUCountries;
var i : Integer;
begin
  for I := Low(EUCountries) to High(EUCountries) do
  begin
    tblCountry.Append;
    tblCountryNAME.AsString := EUCountries[i].en_name;
    tblCountryCODE.AsString := EUCountries[i].code;
    tblCountryEU.AsInteger := 1;
    tblCountryVAT_RATE_REDUCED.AsFloat := EUCountries[i].red2;
    tblCountryVAT_RATE_REDUCED_LOW.AsFloat := EUCountries[i].red1;
    tblCountryVAT_RATE.AsFloat := EUCountries[i].vat;

// This is the date the VAT value was valid – according to

// the quoted document.
    tblCountryDATE.AsDateTime := EncodeDate( 2014, 7, 1);      tblCountry.Post;
  end;
end;

A database is opened or created with this method:

procedure TWPDataModule.DataOpen(Filename : String);
  var createnew : Boolean;
begin
    createnew := not FileExists(Filename);
    with dbMain do begin
    Close;

    // create temporary connection definition
    with Params do begin
      Clear;

      Add('DriverID=SQLite');
      Add('Database=' + FileName);
    end;

    Open;

    if createnew then
    begin
      FDQuery1.ExecSQL;
    end;

      try
        tblCountry.Open;
        tblCustomers.Open;
        tblCustomersLookup.Open;
        tblOrderItems.Open;
        tblOrders.Open;
        tblProducts.Open;
        // … open other tables

      except
         Close;
         createnew := false;
      end;

      if createnew then
      begin
          AddEUCountries;
          AddSampleData;
      end;
    end;
end;

The Methods AddEUCountries fills the country table with the items of the VAT list above, AddSampleData adds some customer and product data so the program does not start completely empty.

Back To Top