Here are the schemas, global function and events
DROP TABLE IF EXISTS `invoices`;
CREATE TABLE `invoices` (
`uuid` varchar(36) COLLATE utf8mb3_unicode_ci NOT NULL,
`client_id` varchar(36) COLLATE utf8mb3_unicode_ci NOT NULL,
`client_name` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`invoice_no` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL,
`invoice_date` date NOT NULL,
`due_date` date NOT NULL,
`status` varchar(36) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`invoice_total` double(15,2) DEFAULT 0.00,
`payment_total` double(15,2) DEFAULT 0.00,
`terms` text COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`notes` text COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`currency` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL,
`recurring` tinyint(1) NOT NULL DEFAULT 0,
`email_id` varchar(36) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`pdf_saved` tinyint(1) NOT NULL DEFAULT 0,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`increment_num` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `invoice_items`
--
DROP TABLE IF EXISTS `invoice_items`;
CREATE TABLE `invoice_items` (
`uuid` varchar(36) COLLATE utf8mb3_unicode_ci NOT NULL,
`invoice_id` varchar(36) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`invoice_no` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`item_name` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL,
`item_description` text COLLATE utf8mb3_unicode_ci NOT NULL,
`quantity` double(9,3) NOT NULL,
`price` double(15,2) NOT NULL,
`tax_id` varchar(36) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`tax_rate` double(4,2) DEFAULT 0.00,
`item_order` int(11) NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
--
-- Indexes for table `invoices`
--
ALTER TABLE `invoices`
ADD PRIMARY KEY (`uuid`),
ADD UNIQUE KEY `invoices_number_unique` (`invoice_no`),
ADD UNIQUE KEY `increment_num` (`increment_num`),
ADD KEY `invoices_client_id_foreign` (`client_id`);
--
-- Indexes for table `invoice_items`
--
ALTER TABLE `invoice_items`
ADD PRIMARY KEY (`uuid`),
ADD KEY `invoice_items_invoice_id_foreign` (`invoice_id`),
ADD KEY `invoice_items_tax_id_foreign` (`tax_id`),
ADD KEY `invoice_items_invoice_no_foreign` (`invoice_no`);
The Master/Detail relationship is
invoices`.`uuid` = `invoice_items`.`invoice_id
I have a function to create uuid
function getGUID(){
mt_srand((double)microtime()*10000);
$charid = strtoupper(md5(uniqid(rand(), true)));
$hyphen = "-";
$uuid =
substr($charid, 0, 8).$hyphen
.substr($charid, 8, 4).$hyphen
.substr($charid,12, 4).$hyphen
.substr($charid,16, 4).$hyphen
.substr($charid,20,12);
return $uuid;
}
Invoices Row_Inserting uses GetGUID()
// Row Inserting event
public function rowInserting($rsold, &$rsnew)
{
$client_name = $rsnew["client_name"];
$client_id_sql = "SELECT uuid FROM clients WHERE name ='" . AdjustSql($client_name) . "'";
$client_id = ExecuteScalar($client_id_sql);
$rsnew["client_id"] = $client_id;
$_SESSION["uuid"] = GetGUID();
$rsnew["uuid"] = $_SESSION["uuid"];
Log("GUID ". $_SESSION["uuid"]);
return true;
}
Invoice_Items Row_Inserting sets invoice_id = $_SESSION["uuid"]
// Row Inserting event
public function rowInserting($rsold, &$rsnew)
{
$rsnew["invoice_id"] = $_SESSION["uuid"];
Log("Item ID ". $_SESSION["uuid"]);
//
return true;
}