Pizza Delivery Tutorial: Difference between revisions

From nuBuilderForte
Jump to navigation Jump to search
 
(14 intermediate revisions by the same user not shown)
Line 1: Line 1:
== Create the Database ==
== Create the Database ==
This is usually done in any MySQL client like <tt>phpMyAdmin (nudb)</tt> before installing NuBuilder4 and may already have been done if a login was attempted before.
This is usually done in any MySQL client like <tt>phpMyAdmin (nudb)</tt> before installing '''nuBuilder4''' and may already have been done if a login was attempted before.
<pre>
<pre>
CREATE DATABASE IF NOT EXISTS nubuilder4 CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE DATABASE IF NOT EXISTS nubuilder4 CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Line 10: Line 10:


== Install Tutorial tables and data ==
== Install Tutorial tables and data ==
* Navigate to '''Home => Builders => Database => Import => [[Media:Pizza.sql.zip|Pizza.sql.zip]]'''
* Navigate to '''Home => Builders => Database => Import => [[Media:Pizza4sql.zip|Pizza Tutorial SQL for nuBuilder 4]]'''
* Close <tt>phpMyAdmin</tt> tab and return to '''Builders''' tab in nuBuilder4
* Close <tt>phpMyAdmin</tt> tab and return to '''Builders''' tab in nuBuilder4
<pre>
CREATE TABLE `customer` (
  `customer_id` varchar(25) NOT NULL,
  `cus_name` varchar(1000) NOT NULL,
  `cus_address` text NULL,
  `cus_phone` varchar(1000) NULL,
  `cus_notes` text NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- IF autogenerated:
-- ALTER TABLE `customer` CHANGE `cus_name` `cus_name` VARCHAR(1000) CHARSET utf8 COLLATE utf8_general_ci NOT NULL;
INSERT INTO `customer` (`customer_id`, `cus_name`, `cus_address`, `cus_phone`, `cus_notes`) VALUES
('5d880729bb550cb', 'Ronald MacErnie', '3 Arch Tce\nPattyville', '0607888333', 'Deliver to back door.'),
('5d88076852e16a8', 'Terry Plot', '45 Tree St\nWoodville', '0980555333', 'Ring front door'),
('5d8807a3b558352', 'Peta Pepper', '1 Picked St\nPickleviille', '0987644333', ''),
('5d88082faf64a63', 'Thurston Ireland', '8 Uluua Rd\nRichland', '0965345833', '');
CREATE TABLE `product` (
  `product_id` varchar(25) NOT NULL,
  `pro_code` varchar(1000) NOT NULL,
  `pro_description` varchar(1000) NOT NULL,
  `pro_price` decimal(12,4) NOT NULL,
  `pro_group` varchar(1000) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- IF autogenerated:
-- ALTER TABLE `product` CHANGE `pro_code` `pro_code` VARCHAR(1000) CHARSET utf8 COLLATE utf8_general_ci NOT NULL,
--                      CHANGE `pro_description` `pro_description` VARCHAR(1000) CHARSET utf8 COLLATE utf8_general_ci NOT NULL,
--                      CHANGE `pro_price` `pro_price` DECIMAL(12,4) NOT NULL,
--                      CHANGE `pro_group` `pro_group` VARCHAR(1000) CHARSET utf8 COLLATE utf8_general_ci NOT NULL;
INSERT INTO `product` (`product_id`, `pro_code`, `pro_description`, `pro_price`, `pro_group`) VALUES
('5d881a5cc309669', 'P1', 'Large Pineapple Pizza', '15.9000', 'Pizza'),
('5d881afd0f7e7ea', 'P2', 'Large Pizza wth the Lot', '23.0000', 'Pizza'),
('5d881b2d40b46ef', 'Prawn Pizza', 'Large Prawn Pizza', '27.5000', 'Pizza'),
('5d881ba8dd326a7', 'A2', 'Spaghetti and Meatballs', '15.5000', 'Pasta'),
('5d881be75552ac6', 'D1', 'Can of Coke', '3.0000', 'Drink'),
('5d881bf6c772e79', 'D1', 'Can of Pepsi', '3.0000', 'Drink');
CREATE TABLE `invoice` (
  `invoice_id` varchar(25) NOT NULL,
  `inv_date` date NOT NULL,
  `inv_number` bigint(20) unsigned NOT NULL,
  `inv_customer_id` varchar(25) NOT NULL,
  `inv_total` decimal(12,4) NOT NULL,
  `inv_tax` decimal(12,4) NOT NULL,
  `inv_grand_total` decimal(12,4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- IF autogenerated:
-- ALTER TABLE `invoice` CHANGE `inv_date` `inv_date` DATE NOT NULL,
--                      CHANGE `inv_number` `inv_number` BIGINT(20) UNSIGNED NOT NULL,
--                      CHANGE `inv_customer_id` `inv_customer_id` VARCHAR(25) CHARSET utf8 COLLATE utf8_general_ci NOT NULL,
--                      CHANGE `inv_total` `inv_total` DECIMAL(12,4) NOT NULL,
--                      CHANGE `inv_tax` `inv_tax` DECIMAL(12,4) NOT NULL,
--                      CHANGE `inv_grand_total` `inv_grand_total` DECIMAL(12,4) NOT NULL;
INSERT INTO `invoice` (`invoice_id`, `inv_date`, `inv_number`, `inv_customer_id`, `inv_total`, `inv_tax`, `inv_grand_total`) VALUES
('5d8c0739a0667db', '2019-09-26', '1002', '5d88082faf64a63', '58.0000', '5.8000', '63.8000'),
('5d8c2908b4f8a2a', '2019-09-27', '1003', '5d8807a3b558352', '34.4000', '3.4400', '37.8300'),
('5d8c2b25abb6f2a', '2019-09-27', '1004', '5d880729bb550cb', '130.4000', '13.0400', '143.4400');
CREATE TABLE `item` (
  `item_id` varchar(25) NOT NULL,
  `ite_invoice_id` varchar(25) NOT NULL,
  `ite_product_id` varchar(25) NOT NULL,
  `ite_price` decimal(12,4) NOT NULL,
  `ite_units` decimal(12,4) NOT NULL,
  `ite_total` decimal(12,4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- IF autogenerated:
-- ALTER TABLE `item` CHANGE `ite_invoice_id` `ite_invoice_id` VARCHAR(25) CHARSET utf8 COLLATE utf8_general_ci NOT NULL,
--                    CHANGE `ite_product_id` `ite_product_id` VARCHAR(25) CHARSET utf8 COLLATE utf8_general_ci NOT NULL,
--                    CHANGE `ite_price` `ite_price` DECIMAL(12,4) NOT NULL,
--                    CHANGE `ite_units` `ite_units` DECIMAL(12,4) NOT NULL,
--                    CHANGE `ite_total` `ite_total` DECIMAL(12,4) NOT NULL;
INSERT INTO `item` (`item_id`, `ite_invoice_id`, `ite_product_id`, `ite_price`, `ite_units`, `ite_total`) VALUES
('5d8c0739a4f3d5d', '5d8c0739a0667db', '5d881afd0f7e7ea', '23.0000', '2.0000', '46.0000'),
('5d8c249bddbf302', '5d8c0739a0667db', '5d881bf6c772e79', '3.0000', '4.0000', '12.0000'),
('5d8c2908b7c208a', '5d8c2908b4f8a2a', '5d881a5cc309669', '15.9000', '1.0000', '15.9000'),
('5d8c2908b95a592', '5d8c2908b4f8a2a', '5d881ba8dd326a7', '15.5000', '1.0000', '15.5000'),
('5d8c2908bae3bdb', '5d8c2908b4f8a2a', '5d881be75552ac6', '3.0000', '1.0000', '3.0000'),
('5d8c2b25aeb7062', '5d8c2b25abb6f2a', '5d881a5cc309669', '15.9000', '1.0000', '15.9000'),
('5d8c2b25b082723', '5d8c2b25abb6f2a', '5d881ba8dd326a7', '15.5000', '1.0000', '15.5000'),
('5d8c2b25b237562', '5d8c2b25abb6f2a', '5d881be75552ac6', '3.0000', '33.0000', '99.0000');
ALTER TABLE `customer` ADD PRIMARY KEY (`customer_id`);
ALTER TABLE `product` ADD PRIMARY KEY (`product_id`);
ALTER TABLE `invoice` ADD PRIMARY KEY (`invoice_id`);
ALTER TABLE `item` ADD PRIMARY KEY (`item_id`);
</pre>


== Create Fast Forms for each tutorial table ==
== Create Fast Forms for each tutorial table ==
Line 37: Line 131:
* Navigate to '''Home => Setup => Forms'''
* Navigate to '''Home => Setup => Forms'''
* Click on the record having Table '''customer''' with Code '''FF0'''
* Click on the record having Table '''customer''' with Code '''FF0'''
* Replace field Code with '''CUS''' and Description with '''Customer''' and click Save button.
* [[Media:CustomerFormRename.png|Replace field Code with '''CUS''' and Description with '''Customer''']] and click Save button.
* Navigate to '''Home => User Home => Customer => Add'''
* Navigate to '''Home => User Home => Customer => Add'''
* Click the '''Form Settings''' (three vertical dots) button and [[Media:PageSettingsMenu.png|select '''Arrange Objects''']]
* Click the [[Media:PageSettingsMenu.png|'''Form Settings''']] (three vertical dots) button and select '''Arrange Objects''' and arrange the form's layout ([[Media:CustomerFF_mp4.zip|Video 3:28 mins]]).


=== product ===
=== product ===
* Navigate to '''Home => Builders => Fast Form'''
{| class="wikitable"
!Parameter !!Value
|-
|Form Type ||Browse and Edit
|-
|Table Name ||product
|-
|Add Objects ||Input:Text, Input:Text, Input:nuNumber, Select
|-
|Edit Labels ||Code, Product, Price, Group
|-
|Use Arrow keys to set Field Name ||pro_code, pro_description, pro_price, pro_group
|-
|Browse Column ||Tick all 4
|-
|}
* Click the '''Build Fast Form''' button at the top left corner
* [[Media:ProductFF.png|Screenshot]]


=== invoice ===
=== invoice ===
* Navigate to '''Home => Builders => Fast Form'''
{| class="wikitable"
!Parameter !!Value
|-
|Form Type ||Browse and Edit
|-
|Table Name ||invoice
|-
|Add Objects ||nuDate, nuAutoNumber, Lookup, Calc, Calc, Calc, Subform
|-
|Edit Labels ||Date, Number, Customer, Total, Tax, Grand Total, Items
|-
|Use Arrow keys to set Field Name ||inv_date, inv_number, inv_customer_id (later will change to cus_name when JOINed), inv_total, inv_tax, inv_grand_total, item_sf
|-
|Browse Column ||Untick Total and Tax, tick the rest
|-
|}
* Click the '''Build Fast Form''' button at the top left corner
* [[Media:InvoiceFF.png|Screenshot]]


=== item ''subform'' ===
=== item ''subform'' ===
* Navigate to '''Home => Builders => Fast Form'''
{| class="wikitable"
!Parameter !!Value
|-
|Form Type ||Subform
|-
|Table Name ||item
|-
|Foreign Key Field ||ite_invoice_id
|-
|Add Objects ||Lookup, nuNumber, nuNumber, Calc
|-
|Edit Labels ||Product, Price, Units, Total
|-
|Use Arrow keys to set Field Name ||ite_product_id, ite_price, ite_units, ite_total
|-
|Browse Column ||Does not exist for Subform
|-
|}
* Click the '''Build Fast Form''' button at the top left corner
* [[Media:ItemFF.png|Screenshot]]
* The Foreign Key Field will not be visible if the table already exists and is used only for creating the table when absent. It's only use is to get the name of the field for table creation and does not link to any form.

Latest revision as of 17:34, 3 May 2021

Create the Database

This is usually done in any MySQL client like phpMyAdmin (nudb) before installing nuBuilder4 and may already have been done if a login was attempted before.

CREATE DATABASE IF NOT EXISTS nubuilder4 CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Install system tables

  • nuBuilder4 Web Login with default user / pass: globeadmin / nu
  • On first login, the system tables get populated from nubuilder4.sql.

Install Tutorial tables and data

CREATE TABLE `customer` (
  `customer_id` varchar(25) NOT NULL,
  `cus_name` varchar(1000) NOT NULL,
  `cus_address` text NULL,
  `cus_phone` varchar(1000) NULL,
  `cus_notes` text NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- IF autogenerated:
-- ALTER TABLE `customer` CHANGE `cus_name` `cus_name` VARCHAR(1000) CHARSET utf8 COLLATE utf8_general_ci NOT NULL; 

INSERT INTO `customer` (`customer_id`, `cus_name`, `cus_address`, `cus_phone`, `cus_notes`) VALUES
('5d880729bb550cb', 'Ronald MacErnie', '3 Arch Tce\nPattyville', '0607888333', 'Deliver to back door.'),
('5d88076852e16a8', 'Terry Plot', '45 Tree St\nWoodville', '0980555333', 'Ring front door'),
('5d8807a3b558352', 'Peta Pepper', '1 Picked St\nPickleviille', '0987644333', ''),
('5d88082faf64a63', 'Thurston Ireland', '8 Uluua Rd\nRichland', '0965345833', '');

CREATE TABLE `product` (
  `product_id` varchar(25) NOT NULL,
  `pro_code` varchar(1000) NOT NULL,
  `pro_description` varchar(1000) NOT NULL,
  `pro_price` decimal(12,4) NOT NULL,
  `pro_group` varchar(1000) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- IF autogenerated:
-- ALTER TABLE `product` CHANGE `pro_code` `pro_code` VARCHAR(1000) CHARSET utf8 COLLATE utf8_general_ci NOT NULL,
--                       CHANGE `pro_description` `pro_description` VARCHAR(1000) CHARSET utf8 COLLATE utf8_general_ci NOT NULL,
--                       CHANGE `pro_price` `pro_price` DECIMAL(12,4) NOT NULL,
--                       CHANGE `pro_group` `pro_group` VARCHAR(1000) CHARSET utf8 COLLATE utf8_general_ci NOT NULL; 

INSERT INTO `product` (`product_id`, `pro_code`, `pro_description`, `pro_price`, `pro_group`) VALUES
('5d881a5cc309669', 'P1', 'Large Pineapple Pizza', '15.9000', 'Pizza'),
('5d881afd0f7e7ea', 'P2', 'Large Pizza wth the Lot', '23.0000', 'Pizza'),
('5d881b2d40b46ef', 'Prawn Pizza', 'Large Prawn Pizza', '27.5000', 'Pizza'),
('5d881ba8dd326a7', 'A2', 'Spaghetti and Meatballs', '15.5000', 'Pasta'),
('5d881be75552ac6', 'D1', 'Can of Coke', '3.0000', 'Drink'),
('5d881bf6c772e79', 'D1', 'Can of Pepsi', '3.0000', 'Drink');

CREATE TABLE `invoice` (
  `invoice_id` varchar(25) NOT NULL,
  `inv_date` date NOT NULL,
  `inv_number` bigint(20) unsigned NOT NULL,
  `inv_customer_id` varchar(25) NOT NULL,
  `inv_total` decimal(12,4) NOT NULL,
  `inv_tax` decimal(12,4) NOT NULL,
  `inv_grand_total` decimal(12,4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- IF autogenerated:
-- ALTER TABLE `invoice` CHANGE `inv_date` `inv_date` DATE NOT NULL, 
--                       CHANGE `inv_number` `inv_number` BIGINT(20) UNSIGNED NOT NULL, 
--                       CHANGE `inv_customer_id` `inv_customer_id` VARCHAR(25) CHARSET utf8 COLLATE utf8_general_ci NOT NULL, 
--                       CHANGE `inv_total` `inv_total` DECIMAL(12,4) NOT NULL, 
--                       CHANGE `inv_tax` `inv_tax` DECIMAL(12,4) NOT NULL, 
--                       CHANGE `inv_grand_total` `inv_grand_total` DECIMAL(12,4) NOT NULL; 

INSERT INTO `invoice` (`invoice_id`, `inv_date`, `inv_number`, `inv_customer_id`, `inv_total`, `inv_tax`, `inv_grand_total`) VALUES
('5d8c0739a0667db', '2019-09-26', '1002', '5d88082faf64a63', '58.0000', '5.8000', '63.8000'),
('5d8c2908b4f8a2a', '2019-09-27', '1003', '5d8807a3b558352', '34.4000', '3.4400', '37.8300'),
('5d8c2b25abb6f2a', '2019-09-27', '1004', '5d880729bb550cb', '130.4000', '13.0400', '143.4400');

CREATE TABLE `item` (
  `item_id` varchar(25) NOT NULL,
  `ite_invoice_id` varchar(25) NOT NULL,
  `ite_product_id` varchar(25) NOT NULL,
  `ite_price` decimal(12,4) NOT NULL,
  `ite_units` decimal(12,4) NOT NULL,
  `ite_total` decimal(12,4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- IF autogenerated:
-- ALTER TABLE `item` CHANGE `ite_invoice_id` `ite_invoice_id` VARCHAR(25) CHARSET utf8 COLLATE utf8_general_ci NOT NULL, 
--                    CHANGE `ite_product_id` `ite_product_id` VARCHAR(25) CHARSET utf8 COLLATE utf8_general_ci NOT NULL, 
--                    CHANGE `ite_price` `ite_price` DECIMAL(12,4) NOT NULL, 
--                    CHANGE `ite_units` `ite_units` DECIMAL(12,4) NOT NULL, 
--                    CHANGE `ite_total` `ite_total` DECIMAL(12,4) NOT NULL; 

INSERT INTO `item` (`item_id`, `ite_invoice_id`, `ite_product_id`, `ite_price`, `ite_units`, `ite_total`) VALUES
('5d8c0739a4f3d5d', '5d8c0739a0667db', '5d881afd0f7e7ea', '23.0000', '2.0000', '46.0000'),
('5d8c249bddbf302', '5d8c0739a0667db', '5d881bf6c772e79', '3.0000', '4.0000', '12.0000'),
('5d8c2908b7c208a', '5d8c2908b4f8a2a', '5d881a5cc309669', '15.9000', '1.0000', '15.9000'),
('5d8c2908b95a592', '5d8c2908b4f8a2a', '5d881ba8dd326a7', '15.5000', '1.0000', '15.5000'),
('5d8c2908bae3bdb', '5d8c2908b4f8a2a', '5d881be75552ac6', '3.0000', '1.0000', '3.0000'),
('5d8c2b25aeb7062', '5d8c2b25abb6f2a', '5d881a5cc309669', '15.9000', '1.0000', '15.9000'),
('5d8c2b25b082723', '5d8c2b25abb6f2a', '5d881ba8dd326a7', '15.5000', '1.0000', '15.5000'),
('5d8c2b25b237562', '5d8c2b25abb6f2a', '5d881be75552ac6', '3.0000', '33.0000', '99.0000');

ALTER TABLE `customer` ADD PRIMARY KEY (`customer_id`);
ALTER TABLE `product` ADD PRIMARY KEY (`product_id`);
ALTER TABLE `invoice` ADD PRIMARY KEY (`invoice_id`);
ALTER TABLE `item` ADD PRIMARY KEY (`item_id`);

Create Fast Forms for each tutorial table

customer

  • Navigate to Home => Builders => Fast Form
Parameter Value
Form Type Browse and Edit
Table Name customer
Add Objects Input:Text, TextArea, Input:Text, TextArea
Edit Labels Customer, Address, Phone, Notes
Use Arrow keys to set Field Name cus_name, cus_address, cus_phone, cus_notes
Browse Column Tick first 3 only

product

  • Navigate to Home => Builders => Fast Form
Parameter Value
Form Type Browse and Edit
Table Name product
Add Objects Input:Text, Input:Text, Input:nuNumber, Select
Edit Labels Code, Product, Price, Group
Use Arrow keys to set Field Name pro_code, pro_description, pro_price, pro_group
Browse Column Tick all 4
  • Click the Build Fast Form button at the top left corner
  • Screenshot

invoice

  • Navigate to Home => Builders => Fast Form
Parameter Value
Form Type Browse and Edit
Table Name invoice
Add Objects nuDate, nuAutoNumber, Lookup, Calc, Calc, Calc, Subform
Edit Labels Date, Number, Customer, Total, Tax, Grand Total, Items
Use Arrow keys to set Field Name inv_date, inv_number, inv_customer_id (later will change to cus_name when JOINed), inv_total, inv_tax, inv_grand_total, item_sf
Browse Column Untick Total and Tax, tick the rest
  • Click the Build Fast Form button at the top left corner
  • Screenshot

item subform

  • Navigate to Home => Builders => Fast Form
Parameter Value
Form Type Subform
Table Name item
Foreign Key Field ite_invoice_id
Add Objects Lookup, nuNumber, nuNumber, Calc
Edit Labels Product, Price, Units, Total
Use Arrow keys to set Field Name ite_product_id, ite_price, ite_units, ite_total
Browse Column Does not exist for Subform
  • Click the Build Fast Form button at the top left corner
  • Screenshot
  • The Foreign Key Field will not be visible if the table already exists and is used only for creating the table when absent. It's only use is to get the name of the field for table creation and does not link to any form.