Pizza Delivery Tutorial: Difference between revisions
Jump to navigation
Jump to search
m (→customer) |
m (→customer) |
||
(20 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 | 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 '''Builders => Database => Import => [[Media: | * 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 == | ||
=== customer === | === customer === | ||
* Navigate to '''Builders => Fast Form''' | * Navigate to '''Home => Builders => Fast Form''' | ||
{| class="wikitable" | {| class="wikitable" | ||
!Parameter !!Value | !Parameter !!Value | ||
Line 30: | Line 124: | ||
|Use Arrow keys to set Field Name ||cus_name, cus_address, cus_phone, cus_notes | |Use Arrow keys to set Field Name ||cus_name, cus_address, cus_phone, cus_notes | ||
|- | |- | ||
|Browse Column | |Browse Column ||Tick first 3 only | ||
|- | |- | ||
|} | |} | ||
* Click the | * Click the '''Build Fast Form''' button at the top left corner | ||
* [[Media:CustomerFF.png|Screenshot]] | |||
* Navigate to '''Home => Setup => Forms''' | |||
* Click on the record having Table '''customer''' with Code '''FF0''' | |||
* [[Media:CustomerFormRename.png|Replace field Code with '''CUS''' and Description with '''Customer''']] and click Save button. | |||
* Navigate to '''Home => User Home => Customer => Add''' | |||
* 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
- Navigate to Home => Builders => Database => Import => Pizza Tutorial SQL for nuBuilder 4
- Close phpMyAdmin tab and return to Builders tab in nuBuilder4
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 |
- Click the Build Fast Form button at the top left corner
- Screenshot
- Navigate to Home => Setup => Forms
- Click on the record having Table customer with Code FF0
- Replace field Code with CUS and Description with Customer and click Save button.
- Navigate to Home => User Home => Customer => Add
- Click the Form Settings (three vertical dots) button and select Arrange Objects and arrange the form's layout (Video 3:28 mins).
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.