OK, let's start
There are several abstract PHP classes:
- AnyDesk_TNxQSMjAfP.png (67.71 KiB) Viewed 3941 times
The highlighted class inherits methods from the other classes like loading the CSV-file, reading line-by-line, ...
Main method there is
importRow($index, $data). This one gets called for every line of the CSV-file, inserts a record into target-table, does some validation and mapping and sets the status per row.
Code: Select all
protected function importRow($index, $data): bool
{
// first index is headline (contains fieldnames)
$lineNo = $index;
// first, insert the row with master data
$set_array = [
\Model\Tables\DepositItems::DepositID => $this->pk,
\Model\Tables\DepositItems::DepositTypeID => $this->_deposit["DepositTypeID"],
\Model\Tables\DepositItems::DepositTypeName => $this->_deposit_type["Name"],
\Model\Tables\DepositItems::ImportRowIndex => $lineNo,
\Model\Tables\DepositItems::ImportRowData => json_encode($data),
\Model\Tables\DepositItems::ImportStatusCode => DEPOSITITEMS::IMPORTSTATUSCODE_NEW,
\Model\Tables\DepositItems::ImportMessages => null
];
$eo = null;
if (!insert(\Model\Tables::DepositItems, $set_array, $eo) || $eo)
throw new \Exception("Error importing line {$lineNo}. Insert failed: {$eo}", 1);
// get id
$deposititem_id = db_insert_id(db_link());
set_record_owner(\Model\TABLES::DepositItems, $deposititem_id, $this->getMemberID());
// then iterate through all mappings
$mappings = $this->getMappings();
$import_errors = [];
$where_array = [\Model\Tables\DepositItems::DepositItemID => $deposititem_id];
// update status: PENDING
$set_array = [
\Model\Tables\DepositItems::ImportStatusCode => DEPOSITITEMS::IMPORTSTATUSCODE_PENDING,
];
update(\Model\Tables::DepositItems, $set_array, $where_array, $eo);
foreach ($mappings as $mapping) {
$source_field = $mapping->getSource();
$target_field = $mapping->getTarget();
if (is_callable($target_field)) {
// call mapping function
$source_value = $target_field($data[$source_field]);
$set_array = [$target_field => $source_value == '' ? NULL : $source_value];
} else {
$source_value = $data[$source_field];
$set_array = [$target_field => $source_value == '' ? NULL : $source_value];
}
// update column, catch exception
// if any, append to error list
try {
$eo = null;
update(\Model\Tables::DepositItems, $set_array, $where_array, $eo);
} catch (\Throwable $th) {
$import_errors[] = "Error importing CSV-field \"{$source_field}\" into database-column \"{$target_field}\": " . $th->getMessage() . " / " . $eo;
}
}
// update import-status
$with_errors = sizeof($import_errors);
// final updates:
// Amount 10mg ==> 10 mg
$tn_items = \Model\Tables\DepositItems::NAME;
$pk_items = \Model\Tables\DepositItems::PK;
$cn_amount = \Model\Tables\DepositItems::Amount;
$cn_amount_unit = \Model\Tables\DepositItems::AmountUnit;
$cn_amount_value = \Model\Tables\DepositItems::AmountValue;
sql("UPDATE {$tn_items}
SET
{$cn_amount_unit} = RIGHT({$cn_amount}, 2)
, {$cn_amount_value} = LEFT({$cn_amount}, LENGTH({$cn_amount})-2)
WHERE RIGHT({$cn_amount},2) IN ('ul', 'mg')
AND {$pk_items}='{$deposititem_id}'", $eo);
if ($with_errors) {
// if there are errors ...
$set_array = [
\Model\Tables\DepositItems::ImportMessages => implode("\r\n", $import_errors),
\Model\Tables\DepositItems::ImportStatusCode => DEPOSITITEMS::IMPORTSTATUSCODE_INVALID
];
} else {
// no errors
$set_array = [
\Model\Tables\DepositItems::ImportMessages => null,
\Model\Tables\DepositItems::ImportStatusCode => DEPOSITITEMS::IMPORTSTATUSCODE_VALID
];
}
update(\Model\Tables\DepositItems::NAME, $set_array, $where_array, $eo);
return !$with_errors;
}
---
Working with CSV files in PHP
The following code shows the most important PHP commands for reading CSV files:
- AnyDesk_DSSinApSly.png (154.41 KiB) Viewed 3941 times
---
Model.php
If you are wondering about all those
\Model\Tables\DepositItems::ImportMessages stuff:
Model.php is an
auto-generated file holding constants for all tables, columns etc.
Excerpt:
Code: Select all
<?php
// DO NOT CHANGE THIS FILE MANUALLY
// Generated By AppGiniHelperModelBuilder.php - copyright © 2021 bizzworxx.de
namespace Model {
class Tables
{
public const Requests = 'Requests';
public const RequestResults = 'RequestResults';
public const Worklist = 'Worklist';
public const Orders = 'Orders';
// ...
}
}
- AnyDesk_v4mKlWPrQ7.png (111.64 KiB) Viewed 3941 times
Auto-Generating all table names, column names per table etc. allows me to work with variables/constants instead of writing strings. In combination with IDE's intellisense (here: VSCODE) I can avoid spelling mistakes. Especially when renaming columns, I don't have to check and replace hundreds of SQL statements but Intellisense automatically tells me about non existing column names, for example.
See here, how I can access table names and column names instead of typing strings:
- AnyDesk_l6TDtEBIM8.gif (33.81 KiB) Viewed 3941 times
Validation
For validation there is a programmed rules-engine having dozens of rules, each modeled like this (which is only one ruleset of dozens):
- AnyDesk_kizgVUjuj4.png (123.39 KiB) Viewed 3941 times
That drag-and-drop rule designer produces valid SQL.
After successful import, we check every newly imported record against all rulesets. Each imported record has a status like new|validating|validated-ok|validation-failed. Only if all checks for all records pass, that whole import-record gets a "success"-status and will be used in production.
At every time we can delete a whole import (with all records) or single records or, if validation failed, we can upload a modified file and re-validate.
You can see, this is a lot of work with thousands of lines of code. I hope for your understanding that I must not (customer owns code) and cannot post everything here. But it should give you an idea and some code fragments as a starting point.