Loads a GUI, accesses a database using SQL & display data from the database.

---------------------------
---------------------------
-- Database Example.
-- Loads a GUI, accesses a database using the ADO Extension & display data from the database.
-- Copyright (c) 2007-09 Capricorn 76 Pty. Ltd.
---------------------------
---------------------------

local subGui = nil;
local envADO = nil;

local adoConnection = nil;
local adoRecordset = nil;
local errMsg;

-- Load the SQL ADO module used by this demo.
-- This module depends upon the LuaCOM module.
require("luasql.ado")

---------------------------
-- This function is called by the main program when the world is loaded
---------------------------
function OnWorldLoad(worldName)
    --print('OnWorldLoad:', worldName);
    local bOK = true;

    if (not(luasql)) then
        IApp:showMessageBoxOk('The ADO module is not available. Exiting demo.', 'Error');
        bOK = false;
    end

    -- Create ADO objects
    if (bOK) then
        -- Create the ADO environment
        envADO = assert(luasql.ado());

        -- Connect to data source
        local filename = IApp:getFilenameFromFileSystem('./Demos/Database/Resource/data.mdb');  -- Search when in a zip file
        if (not(filename)) then
            filename = '.\\Demos\\[Database] Using the ADO extension\\Resource\\data.mdb';  -- In a standard folder
        end

        adoConnection, errMsg = envADO:connect(tostring("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" .. filename));
        if (not(adoConnection)) then
            IApp:showMessageBoxOk('The ADO extension cannot create a connection. ' .. errMsg, 'Error');
            bOK = false;
        end
    end

    -- Load GUI
    if (bOK) then
        IGraphics:loadGui('database');
        subGui = IEvents:subscribeGui('OnGui');
    end

    return bOK;
end

---------------------------
-- This function is called by the main program when the world is unloaded
---------------------------
function OnWorldUnload(worldName)
    --print('OnWorldUnload');

    if (subGui) then
        IEvents:unsubscribe(subGui);
    end

    -- Close the ADO connection
    if (adoConnection) then
        adoConnection:close();
    end
end

---------------------------
-- Utility functions
---------------------------
local function loadDatabaseGui(guiName)
    if (soundMenuClick) then
        ISound:play(soundMenuClick);
    end

    IGraphics:removeChildren('tab');
    IGraphics:setGuiParent('tab');
    IGraphics:loadGui(guiName);
    IGraphics:setGuiParent();
end

---------------------------
-- COMPANY DATA
---------------------------
local function loadCompanyData()
    loadDatabaseGui('databaseCompany');
    adoRecordset, errMsg = adoConnection:execute('SELECT * FROM Company');
    if (adoRecordset) then
        local data = adoRecordset:fetch({},'a');
        IGraphics:setControlText('editCompanyName', data.CompanyName);
        IGraphics:setControlText('editAddress', data.Address);
        IGraphics:setControlText('editCity', data.City);
        IGraphics:setControlText('editState', data.State);
        IGraphics:setControlText('editPostcode', data.PostalCode);
        IGraphics:setComboboxSelectedText('comboCountry', data.Country);
        IGraphics:setControlText('editPhone', data.PhoneNumber);
        adoRecordset:close();
    else
        IApp:showMessageBoxOk(errMsg, 'Error');
        IGraphics:removeChildren('tab');
    end
end

local function saveCompanyData()
    local sql =     "UPDATE Company SET " ..
                    "CompanyName='" ..  IGraphics:getControlText('editCompanyName') .. "'," ..
                    "Address='" ..          IGraphics:getControlText('editAddress') .. "'," ..
                    "City='" ..             IGraphics:getControlText('editCity') .. "'," ..
                    "State='" ..                IGraphics:getControlText('editState') .. "'," ..
                    "PostalCode='" ..       IGraphics:getControlText('editPostcode') .. "'," ..
                    "Country='" ..          IGraphics:getComboboxSelectedText('comboCountry') .. "'," ..
                    "PhoneNumber='" ..  IGraphics:getControlText('editPhone') .. "' " ..
                    "WHERE SetupID=1";

    local updated, errMsg = adoConnection:execute(sql);
    if (updated) then
        IApp:showMessageBoxOk('Data saved', 'Save company data');
    else
        IApp:showMessageBoxOk(errMsg, 'Error');
    end
end

---------------------------
-- EMPLOYEE DATA
---------------------------
local function loadEmployeeData()
    -- Load the GUI
    loadDatabaseGui('databaseEmployee');

    adoRecordset, errMsg = adoConnection:execute('SELECT * FROM Employees ORDER BY LastName');
    if (adoRecordset) then
        local dataCount = 0;
        local data = adoRecordset:fetch({},'a');

        while data do
            dataCount = dataCount + 1;
            IGraphics:addListboxItem('listEmployees', data.LastName .. ', ' .. data.FirstName,  data.EmployeeID);

            -- Fetch the next row
            data = adoRecordset:fetch(data, 'a');
        end

        adoRecordset:close();

        IGraphics:setControlText('labelPageInfo', 'Viewing employees: ' .. dataCount);
    else
        IApp:showMessageBoxOk(errMsg, 'Error');
        IGraphics:removeChildren('tab');
    end
end

local function loadEmployeeSelection()
    local sel = IGraphics:getListboxSelectedData('listEmployees');
    local sql = 'SELECT * FROM Employees WHERE EmployeeID=' .. sel;
    adoRecordset, errMsg = adoConnection:execute(tostring(sql));
    if (adoRecordset) then
        local data = adoRecordset:fetch({},'a');
        IGraphics:setComboboxSelectedText('comboTitle', data.Title);
        IGraphics:setControlText('editFirstName', data.FirstName);
        IGraphics:setControlText('editLastName', data.LastName);
        IGraphics:setControlText('editPhone', data.WorkPhone);
        adoRecordset:close();
    else
        IApp:showMessageBoxOk(errMsg, 'Error');
    end
end

local function updateRecordFromEmployeeData(addNew)
    local sql;
    if (addNew) then
        sql =   "INSERT INTO Employees(Title,FirstName,LastName,WorkPhone) VALUES (" ..
                "'" .. IGraphics:getComboboxSelectedText('comboTitle') .. "'," ..
                "'" .. IGraphics:getControlText('editFirstName') .. "'," ..
                "'" .. IGraphics:getControlText('editLastName') .. "'," ..
                "'" .. IGraphics:getControlText('editPhone') .. "')";

    else
        local sel = IGraphics:getListboxSelectedData('listEmployees');
        sql =   "UPDATE Employees SET " ..
                "Title='" ..                IGraphics:getComboboxSelectedText('comboTitle') .. "'," ..
                "FirstName='" ..        IGraphics:getControlText('editFirstName') .. "'," ..
                "LastName='" ..     IGraphics:getControlText('editLastName') .. "'," ..
                "WorkPhone='" ..        IGraphics:getControlText('editPhone') .. "' " ..
                "WHERE EmployeeID=" .. sel;
    end

    local updated, errMsg = adoConnection:execute(tostring(sql));
    if (updated) then
        return true;
    else
        IApp:showMessageBoxOk(errMsg, 'Error');
    end
    return false;
end

local function updateEmployeeSelection()
    local sel = IGraphics:getListboxSelection('listEmployees');
    if (sel < 0) then
        IApp:showMessageBoxOk('Select an employee first', 'No employee selected');
        return;
    end

    if (updateRecordFromEmployeeData()) then
        loadEmployeeData();
        IApp:showMessageBoxOk('Data saved', 'Save employee data');
    end
end

local function addEmployeeData()
    if (updateRecordFromEmployeeData(true)) then
        loadEmployeeData();
        IApp:showMessageBoxOk('New data added', 'Add new employee');
    end
end

local function deleteEmployeeData()
    local sel = IGraphics:getListboxSelectedData('listEmployees');
    if (string.len(sel) <= 0) then
        IApp:showMessageBoxOk('Select an employee first', 'No employee selected');
        return;
    end

    if (IApp:showMessageBoxOkCancel('Remove selected employee data. Continue?', 'Remove data')) then
        local sql = "DELETE FROM Employees WHERE EmployeeID=" .. sel;

        local deleted, errMsg = adoConnection:execute(tostring(sql));
        if (deleted) then
            loadEmployeeData();
            IApp:showMessageBoxOk('Employee data removed', 'Remove employee');
            return true;
        else
            IApp:showMessageBoxOk(errMsg, 'Error');
        end
    end
end

---------------------------
-- PRODUCT DATA
---------------------------
local function loadProductData()
    -- Load the GUI
    loadDatabaseGui('databaseProduct');

    adoRecordset, errMsg = adoConnection:execute('SELECT * FROM Products ORDER BY ProductName');
    if (adoRecordset) then
        local dataCount = 0;
        local data = adoRecordset:fetch({},'a');

        while data do
            dataCount = dataCount + 1;
            IGraphics:addListboxItem('listProducts', data.ProductName, data.ProductID);

            -- Fetch the next row
            data = adoRecordset:fetch(data, 'a');
        end

        adoRecordset:close();

        IGraphics:setControlText('labelPageInfo', 'Viewing products ' .. dataCount);
    else
        IApp:showMessageBoxOk(errMsg, 'Error');
        IGraphics:removeChildren('tab');
    end
end

local function loadProductSelection()
    local sel = IGraphics:getListboxSelectedData('listProducts');
    local sql = 'SELECT * FROM Products WHERE ProductID=' .. sel;
    adoRecordset, errMsg = adoConnection:execute(tostring(sql));
    if (adoRecordset) then
        local data = adoRecordset:fetch({},'a');
        IGraphics:setControlText('editProductName', data.ProductName);
        IGraphics:setControlText('editDescription', data.ProductDescription);
        IGraphics:setControlText('editSerialNumber', data.SerialNumber);
        IGraphics:setControlText('editPrice', data.UnitPrice);
        adoRecordset:close();
    else
        IApp:showMessageBoxOk(errMsg, 'Error');
    end
end

local function updateRecordFromProductData(addNew)
    local sql;
    if (addNew) then
        sql =   "INSERT INTO Products(ProductName,ProductDescription,SerialNumber,UnitPrice) VALUES (" ..
                "'" ..  IGraphics:getControlText('editProductName') .. "'," ..
                "'" ..  IGraphics:getControlText('editDescription') .. "'," ..
                "'" ..  IGraphics:getControlText('editSerialNumber') .. "'," ..
                        IGraphics:getControlText('editPrice') .. ")";

    else
        local sel = IGraphics:getListboxSelectedData('listProducts');
        sql =   "UPDATE Products SET " ..
                "ProductName='" ..              IGraphics:getControlText('editProductName') .. "'," ..
                "ProductDescription='" ..       IGraphics:getControlText('editDescription') .. "'," ..
                "SerialNumber='" ..             IGraphics:getControlText('editSerialNumber') .. "'," ..
                "UnitPrice=" ..                     IGraphics:getControlText('editPrice') .. " " ..
                "WHERE ProductID=" .. sel;
    end

    local updated, errMsg = adoConnection:execute(tostring(sql));
    if (updated) then
        return true;
    else
        IApp:showMessageBoxOk(errMsg, 'Error');
    end
    return false;
end

local function updateProductSelection()
    local sel = IGraphics:getListboxSelection('listProducts');
    if (sel < 0) then
        IApp:showMessageBoxOk('Select an product first', 'No product selected');
        return;
    end

    if (updateRecordFromProductData()) then
        loadProductData();
        IApp:showMessageBoxOk('Data saved', 'Save product data');
    end
end

local function addProductData()
    if (updateRecordFromProductData(true)) then
        loadProductData();
        IApp:showMessageBoxOk('New data added', 'Add new product');
    end
end

local function deleteProductData()
    local sel = IGraphics:getListboxSelectedData('listProducts');
    if (string.len(sel) <= 0) then
        IApp:showMessageBoxOk('Select an product first', 'No product selected');
        return;
    end

    if (IApp:showMessageBoxOkCancel('Remove selected product data. Continue?', 'Remove data')) then

        local sql = "DELETE FROM Products WHERE ProductID=" .. sel;

        local deleted, errMsg = adoConnection:execute(tostring(sql));
        if (deleted) then
            loadProductData();
            IApp:showMessageBoxOk('Product data removed', 'Remove product');
            return true;
        else
            IApp:showMessageBoxOk(errMsg, 'Error');
        end
    end
end

---------------------------
-- TRANSACTION DATA
---------------------------
local function loadTransactionData()
    loadDatabaseGui('databaseTransaction');
end

local function loadTransactionGui(guiName)
    IGraphics:removeChildren('tabTransaction');
    IGraphics:setGuiParent('tabTransaction');
    IGraphics:loadGui(guiName);
    IGraphics:setGuiParent();
end

local function loadProductCombo(comboId)
    -- Load product combo
    local sql = 'SELECT ProductID,ProductName FROM Products ORDER BY ProductName';
    adoRecordset, errMsg = adoConnection:execute(tostring(sql));

    if (adoRecordset) then
        local data = adoRecordset:fetch({},'a');

        while data do
            IGraphics:addComboboxItem(comboId, data.ProductName, data.ProductID);

            -- Fetch the next row
            data = adoRecordset:fetch(data, 'a');
        end

        adoRecordset:close();
    end
end

local function createTransaction()
    loadTransactionGui('databaseTransactionCreate');

    -- Set todays date
    IGraphics:setControlText('editDate', os.date('%d/%m/%Y'));

    -- Load product combo
    loadProductCombo('comboProduct');
end

local function updateNewTransactionPriceFromProductCombo()
    local sql = 'SELECT UnitPrice FROM Products WHERE ProductID=' .. IGraphics:getComboboxSelectedData('comboProduct');
    adoRecordset, errMsg = adoConnection:execute(tostring(sql));
    if (adoRecordset) then
        local data = adoRecordset:fetch({},'a');
        IGraphics:setControlText('editPrice', string.format('%.2f', data.UnitPrice));
    else
        IGraphics:setControlText('editPrice', '0.00');
    end
end

local function saveTransaction()
    -- Validate data
    local productID = IGraphics:getComboboxSelectedData('comboProduct');
    if (string.len(productID) <= 0) then
        IApp:showMessageBoxOk('Select a product first', 'No product selected');
        return;
    end

    local sql = 'INSERT INTO Transactions (TransactionDate,ProductID,TransactionDescription,Quantity,UnitPrice) VALUES(' ..
                    '#' .. IGraphics:getControlText('editDate') .. '#,' ..
                    productID .. ',' ..
                    "'" .. IGraphics:getControlText('editDescription') .. "'," ..
                    IGraphics:getSpinboxValue('spinQuantity') .. ',' ..
                    IGraphics:getControlText('editPrice') .. ')';

    local added, errMsg = adoConnection:execute(tostring(sql));
    if (added) then
        IApp:showMessageBoxOk('New transaction created', 'Save transaction');
    else
        IApp:showMessageBoxOk(errMsg, 'Error');
    end
end

local function searchTransactions()
    loadTransactionGui('databaseTransactionSearch');

    -- Set todays date
    IGraphics:setControlText('editSearchDayStart', os.date('%d'));
    IGraphics:setControlText('editSearchMonthStart', os.date('%m'));
    IGraphics:setControlText('editSearchYearStart', '2000');
    IGraphics:setControlText('editSearchDayEnd', os.date('%d'));
    IGraphics:setControlText('editSearchMonthEnd', os.date('%m'));
    IGraphics:setControlText('editSearchYearEnd', os.date('%Y'));

    -- Load product combo
    loadProductCombo('comboSearchProduct');
end

local function displayTransactionResults(startDate, endDate, productId)
    loadTransactionGui('databaseTransactionSearchResults');

    local sql = 'SELECT * from Transactions WHERE (TransactionDate BETWEEN #' .. startDate .. '# AND #' .. endDate .. '#)';
    if (string.len(productId) > 0) then
        sql = sql .. ' AND ProductID=' .. productId;
    end
    sql = sql .. ' ORDER BY TransactionDate';

    adoRecordset, errMsg = adoConnection:execute(tostring(sql));

    if (adoRecordset) then
        local total = 0;
        local data = adoRecordset:fetch({},'a');
        while (data) do
            local price = data.UnitPrice;
            total = total + price;

            local item = string.format('%10s %.2f %10s',
                                data.TransactionDate,
                                price,
                                data.TransactionDescription);
            IGraphics:addListboxItem('listTransactions', item);
            data = adoRecordset:fetch(data,'a');
        end
        IGraphics:setControlText('labelTransactionsSubTotal', string.format('%.2f', total));
    end

end

---------------------------
-- GUI handler
---------------------------
function OnGui(callerId, eventType, userData)
    if  (IGraphics:buttonClicked('btnBack', callerId, eventType)) then
        if (soundMenuClick) then
            ISound:play(soundMenuClick);
        end
        IApp:stop();

    elseif  (IGraphics:buttonClicked('btnCompanyInfo', callerId, eventType)) then
        loadCompanyData();

    elseif  (IGraphics:buttonClicked('btnEmployees', callerId, eventType)) then
        loadEmployeeData();

    elseif (IGraphics:listChanged('listEmployees', callerId, eventType)) then
        loadEmployeeSelection();

    elseif  (IGraphics:buttonClicked('btnUpdateEmployee', callerId, eventType)) then
        updateEmployeeSelection();

    elseif  (IGraphics:buttonClicked('btnAddEmployee', callerId, eventType)) then
        addEmployeeData();

    elseif  (IGraphics:buttonClicked('btnDeleteEmployee', callerId, eventType)) then
        deleteEmployeeData();

    elseif  (IGraphics:buttonClicked('btnProducts', callerId, eventType)) then
        loadProductData();

    elseif (IGraphics:listChanged('listProducts', callerId, eventType)) then
        loadProductSelection();

    elseif  (IGraphics:buttonClicked('btnUpdateProduct', callerId, eventType)) then
        updateProductSelection();

    elseif  (IGraphics:buttonClicked('btnAddProduct', callerId, eventType)) then
        addProductData();

    elseif  (IGraphics:buttonClicked('btnDeleteProduct', callerId, eventType)) then
        deleteProductData();

    elseif  (IGraphics:buttonClicked('btnTransactions', callerId, eventType)) then
        loadTransactionData();

    elseif  (IGraphics:buttonClicked('btnCreateTransaction', callerId, eventType)) then
        createTransaction();

    elseif (IGraphics:comboChanged('comboProduct', callerId, eventType)) then
        updateNewTransactionPriceFromProductCombo();

    elseif (IGraphics:buttonClicked('btnSaveTransaction', callerId, eventType)) then
        saveTransaction();

    elseif (IGraphics:buttonClicked('btnSearchTransactions', callerId, eventType)) then
        searchTransactions();

    elseif (IGraphics:buttonClicked('btnSearchTransactionsGo', callerId, eventType)) then
        displayTransactionResults(  IGraphics:getControlText('editSearchMonthStart') .. '/' .. IGraphics:getControlText('editSearchDayStart') .. '/' .. IGraphics:getControlText('editSearchYearStart'),
                            IGraphics:getControlText('editSearchMonthEnd') .. '/' .. IGraphics:getControlText('editSearchDayEnd') .. '/' .. IGraphics:getControlText('editSearchYearEnd'),
                            IGraphics:getComboboxSelectedData('comboSearchProduct'));

    elseif  (IGraphics:buttonClicked('btnSaveCompany', callerId, eventType)) then
        saveCompanyData();
    end
end

Copyright © 2006-23 Sep 2009 Capricorn 76 Pty. Ltd. (created on Wed Sep 23 16:49:12 2009)