1775 lines
79 KiB
PHP
1775 lines
79 KiB
PHP
<?php
|
|
|
|
namespace App\Services;
|
|
|
|
use App\Models\Account;
|
|
use App\Models\ClientCase;
|
|
use App\Models\Contract;
|
|
use App\Models\Email;
|
|
use App\Models\Import; // chain resolution
|
|
use App\Models\Payment; // chain resolution
|
|
use App\Models\Person\Person; // chain email
|
|
use App\Models\Person\PersonAddress; // chain phone
|
|
use App\Models\Person\PersonPhone; // chain address
|
|
use Illuminate\Support\Facades\Storage;
|
|
|
|
/**
|
|
* Generic import simulation service.
|
|
* Examines saved mappings and produces a lightweight projection of what would happen
|
|
* for the first N rows without persisting anything. Pluggable strategy per entity root.
|
|
*/
|
|
class ImportSimulationService
|
|
{
|
|
/**
|
|
* Optional client scoping for lookups during a simulation run.
|
|
*/
|
|
private ?int $clientId = null;
|
|
|
|
/**
|
|
* Public entry: simulate import applying mappings to first $limit rows.
|
|
* Keeps existing machine keys for backward compatibility, but adds Slovenian
|
|
* (sl) human labels and trims verbose data (removed raw_amount, notes, sources unless verbose).
|
|
*/
|
|
public function simulate(Import $import, int $limit = 100, bool $verbose = false): array
|
|
{
|
|
// Store client context for the duration of this simulation
|
|
$this->clientId = $import->client_id ?: null;
|
|
$meta = $import->meta ?? [];
|
|
$hasHeader = (bool) ($meta['has_header'] ?? true);
|
|
$delimiter = $meta['forced_delimiter'] ?? $meta['detected_delimiter'] ?? ',';
|
|
$columns = is_array($meta['columns'] ?? null) ? $meta['columns'] : [];
|
|
|
|
// Build both flat and grouped lookups
|
|
[$targetToSource, $groupedLookup] = $this->buildTargetLookup($import);
|
|
if (! $targetToSource && ! $groupedLookup) {
|
|
return $this->errorPayload('Ni shranjenih mapiranj za ta uvoz.');
|
|
}
|
|
|
|
$fileResult = $this->readFileRows($import, $hasHeader, $delimiter, $columns, $limit);
|
|
if (isset($fileResult['error'])) {
|
|
return $this->errorPayload($fileResult['error']);
|
|
}
|
|
// Extract by reference modifications (columns adjusted if no header)
|
|
$rows = $fileResult['rows'];
|
|
$columns = $fileResult['columns'];
|
|
|
|
// Discover mapped entity roots and then filter by supported list with safe fallbacks
|
|
$detectedRoots = $this->detectEntityRoots($targetToSource);
|
|
// Roots that support multiple grouped entries
|
|
$multiRoots = $this->loadSupportsMultipleRoots();
|
|
$supported = $this->loadSupportedEntityRoots();
|
|
$entityRoots = $this->filterEntityRoots($detectedRoots, $supported, $targetToSource);
|
|
$summaries = $this->initSummaries($entityRoots);
|
|
|
|
// Caches & running state
|
|
$contractCache = [];
|
|
$accountCache = [];
|
|
$genericCaches = []; // per root generic caches: [root => [reference => model|null]]
|
|
$runningBalances = [];
|
|
// Duplicate detection state: existing payment references per account + seen in this simulation
|
|
$existingPaymentRefs = []; // [account_id => [ref => true]]
|
|
$seenPaymentRefs = []; // [account_id => [ref => true]]
|
|
// Generic duplicate detection (by identity keys per root)
|
|
$genericExistingIdentities = []; // [root => [identity => true]]
|
|
$genericSeenIdentities = []; // [root => [identity => true]]
|
|
|
|
$translatedActions = $this->actionTranslations();
|
|
$translatedStatuses = $this->statusTranslations();
|
|
|
|
$simRows = [];
|
|
// Determine keyref behavior for contract.reference from mappings/template
|
|
$tplMeta = optional($import->template)->meta ?? [];
|
|
$contractKeyModeTpl = $tplMeta['contract_key_mode'] ?? null; // e.g. 'reference'
|
|
$contractRefMode = $this->mappingModeForImport($import, 'contract.reference'); // e.g. 'keyref'
|
|
foreach ($rows as $idx => $rawValues) {
|
|
$assoc = $this->associateRow($columns, $rawValues);
|
|
$rowEntities = [];
|
|
$keyrefSkipRow = false; // if true, downstream creations are skipped for this row
|
|
|
|
// Reactivation intent detection (row > import > template)
|
|
$rowReactivate = false;
|
|
if (array_key_exists('reactivate', $assoc)) {
|
|
$rawReactivateVal = $assoc['reactivate'];
|
|
if (! is_null($rawReactivateVal) && $rawReactivateVal !== '') {
|
|
$rowReactivate = filter_var($rawReactivateVal, FILTER_VALIDATE_BOOLEAN, FILTER_NULL_ON_FAILURE) ?? false;
|
|
}
|
|
}
|
|
$importReactivate = (bool) ($import->reactivate ?? false);
|
|
$templateReactivate = (bool) (optional($import->template)->reactivate ?? false);
|
|
$reactivateMode = $rowReactivate || $importReactivate || $templateReactivate;
|
|
|
|
// Helper closure to resolve mapping value (with normalization fallbacks)
|
|
$val = function (string $tf) use ($assoc, $targetToSource) {
|
|
// Direct hit
|
|
if (isset($targetToSource[$tf])) {
|
|
return $assoc[$targetToSource[$tf]] ?? null;
|
|
}
|
|
// Fallback: normalize root part (contracts.reference -> contract.reference)
|
|
if (str_contains($tf, '.')) {
|
|
[$root, $rest] = explode('.', $tf, 2);
|
|
$norm = $this->normalizeRoot($root);
|
|
if ($norm !== $root) {
|
|
$alt = $norm.'.'.$rest;
|
|
if (isset($targetToSource[$alt])) {
|
|
return $assoc[$targetToSource[$alt]] ?? null;
|
|
}
|
|
}
|
|
}
|
|
|
|
return null;
|
|
};
|
|
|
|
// Grouped values accessor: returns [group => value] for given root.field
|
|
$groupVals = function (string $root, string $field) use ($assoc, $groupedLookup, $targetToSource) {
|
|
$out = [];
|
|
$key = $root.'.'.$field;
|
|
if (isset($groupedLookup[$root])) {
|
|
foreach ($groupedLookup[$root] as $g => $fields) {
|
|
if (isset($fields[$field])) {
|
|
$col = $fields[$field];
|
|
$out[$g] = $assoc[$col] ?? null;
|
|
}
|
|
}
|
|
}
|
|
// Also include ungrouped flat mapping as default group when no explicit group exists
|
|
if (isset($targetToSource[$key]) && ! isset($out[''])) {
|
|
$out[''] = $assoc[$targetToSource[$key]] ?? null;
|
|
}
|
|
|
|
return $out;
|
|
};
|
|
|
|
// Contract
|
|
if (isset($entityRoots['contract'])) {
|
|
[$contractEntity, $summaries, $contractCache] = $this->simulateContract($val, $summaries, $contractCache, $val('contract.reference'));
|
|
// If reactivation requested and contract exists but is inactive / soft-deleted, mark action as reactivate for UI clarity
|
|
if ($reactivateMode && ($contractEntity['action'] === 'update') && (
|
|
(isset($contractEntity['active']) && $contractEntity['active'] === 0) ||
|
|
(! empty($contractEntity['deleted_at']))
|
|
)) {
|
|
$contractEntity['original_action'] = $contractEntity['action'];
|
|
$contractEntity['action'] = 'reactivate';
|
|
$contractEntity['reactivation'] = true;
|
|
}
|
|
// Keyref enforcement: if mapping is keyref (or template says reference) and contract doesn't exist, skip row creations
|
|
$ref = $contractEntity['reference'] ?? null;
|
|
if (($contractRefMode === 'keyref' || $contractKeyModeTpl === 'reference')
|
|
&& ($contractEntity['action'] === 'create')
|
|
) {
|
|
// Adjust summaries: revert create -> invalid
|
|
if (isset($summaries['contract'])) {
|
|
if (($summaries['contract']['create'] ?? 0) > 0) {
|
|
$summaries['contract']['create']--;
|
|
}
|
|
$summaries['contract']['invalid'] = ($summaries['contract']['invalid'] ?? 0) + 1;
|
|
}
|
|
$contractEntity['original_action'] = 'create';
|
|
$contractEntity['action'] = 'skip';
|
|
$contractEntity['warning'] = 'Contract reference '.(string) $ref.' does not exist (keyref); row skipped.';
|
|
$contractEntity['skipped_due_to_keyref'] = true;
|
|
$keyrefSkipRow = true;
|
|
}
|
|
// Attach contract meta preview from mappings (group-aware)
|
|
$metaGroups = [];
|
|
// Grouped contract.meta.* via groupedLookup
|
|
if (isset($groupedLookup['contract'])) {
|
|
foreach ($groupedLookup['contract'] as $g => $fields) {
|
|
foreach ($fields as $f => $srcCol) {
|
|
if (str_starts_with($f, 'meta.')) {
|
|
$key = substr($f, strlen('meta.'));
|
|
if ($key !== '') {
|
|
if (! isset($metaGroups[$g])) {
|
|
$metaGroups[$g] = [];
|
|
}
|
|
$metaGroups[$g][$key] = [
|
|
'title' => $srcCol,
|
|
'value' => $assoc[$srcCol] ?? null,
|
|
];
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
// Flat contract.meta.* (no group): assign to group '1'
|
|
foreach ($targetToSource as $tf => $srcCol) {
|
|
if (str_starts_with($tf, 'contract.meta.')) {
|
|
$key = substr($tf, strlen('contract.meta.'));
|
|
if ($key !== '') {
|
|
$g = '1';
|
|
if (! isset($metaGroups[$g])) {
|
|
$metaGroups[$g] = [];
|
|
}
|
|
// Do not override grouped if already present
|
|
if (! isset($metaGroups[$g][$key])) {
|
|
$metaGroups[$g][$key] = [
|
|
'title' => $srcCol,
|
|
'value' => $assoc[$srcCol] ?? null,
|
|
];
|
|
}
|
|
}
|
|
}
|
|
}
|
|
if (! empty($metaGroups)) {
|
|
$contractEntity['meta'] = $metaGroups;
|
|
}
|
|
$rowEntities['contract'] = $contractEntity + [
|
|
'action_label' => $translatedActions[$contractEntity['action']] ?? $contractEntity['action'],
|
|
];
|
|
}
|
|
|
|
// Account (explicit mapping with fallback inheritance from contract.reference when missing)
|
|
if (isset($entityRoots['account'])) {
|
|
$rawAccountRef = $val('account.reference');
|
|
$inherited = false;
|
|
if (($rawAccountRef === null || $rawAccountRef === '') && isset($entityRoots['contract'])) {
|
|
$contractRef = $val('contract.reference');
|
|
if ($contractRef !== null && $contractRef !== '') {
|
|
$rawAccountRef = $contractRef;
|
|
$inherited = true;
|
|
}
|
|
}
|
|
[$accountEntity, $summaries, $accountCache] = $this->simulateAccount($val, $summaries, $accountCache, $rawAccountRef);
|
|
// If row is being skipped due to keyref missing contract, do not create accounts
|
|
if ($keyrefSkipRow && ($accountEntity['action'] ?? null) === 'create') {
|
|
if (isset($summaries['account'])) {
|
|
if (($summaries['account']['create'] ?? 0) > 0) {
|
|
$summaries['account']['create']--;
|
|
}
|
|
$summaries['account']['invalid'] = ($summaries['account']['invalid'] ?? 0) + 1;
|
|
}
|
|
$accountEntity['original_action'] = 'create';
|
|
$accountEntity['action'] = 'skip';
|
|
$accountEntity['warning'] = 'Skipped due to missing contract.reference in keyref mode.';
|
|
$accountEntity['skipped_due_to_keyref'] = true;
|
|
}
|
|
if ($inherited) {
|
|
$accountEntity['inherited_reference'] = true;
|
|
}
|
|
$rowEntities['account'] = $accountEntity + [
|
|
'action_label' => $translatedActions[$accountEntity['action']] ?? $accountEntity['action'],
|
|
];
|
|
}
|
|
|
|
// Determine if we have an existing contract (update) to derive chain entities later
|
|
$existingContract = isset($rowEntities['contract']['action']) && $rowEntities['contract']['action'] === 'update';
|
|
|
|
// Generic roots (person, address, email, phone, client_case, etc.) excluding already handled ones
|
|
foreach (array_keys($entityRoots) as $rootKey) {
|
|
// If keyref skip is active for this row, suppress downstream creations (person, client_case, etc.)
|
|
if ($keyrefSkipRow && ! in_array($rootKey, ['contract', 'account', 'payment'], true)) {
|
|
continue;
|
|
}
|
|
if (in_array($rootKey, ['contract', 'account', 'payment'], true)) {
|
|
continue; // already simulated explicitly
|
|
}
|
|
// If contract already exists, we skip simulating person / client_case generically.
|
|
// ImportProcessor will not create new ones in that scenario; it reuses the chain.
|
|
if ($existingContract && in_array($rootKey, ['person', 'client_case'], true)) {
|
|
continue;
|
|
}
|
|
// Special-case: when contract exists and email/phone/address mapping uses the same column as contract.reference,
|
|
// treat it as a root declaration only and defer to chain attachments instead of generic simulation.
|
|
if ($existingContract && in_array($rootKey, ['email', 'phone', 'address'], true)) {
|
|
$crSrc = $targetToSource['contract.reference'] ?? null;
|
|
$rk = $rootKey === 'email' ? 'email.value' : ($rootKey === 'phone' ? 'phone.nu' : 'address.address');
|
|
$rkSrc = $targetToSource[$rk] ?? null;
|
|
if ($crSrc !== null && $rkSrc !== null && $crSrc === $rkSrc) {
|
|
continue;
|
|
}
|
|
}
|
|
$reference = $val($rootKey.'.reference');
|
|
$identityCandidates = $this->genericIdentityCandidates($rootKey, $val);
|
|
if (isset($multiRoots[$rootKey]) && $multiRoots[$rootKey] === true) {
|
|
// Multi-item simulation per group
|
|
[$items, $summaries, $genericCaches, $genericExistingIdentities, $genericSeenIdentities]
|
|
= $this->simulateGenericRootMulti(
|
|
$rootKey,
|
|
$val,
|
|
$groupVals,
|
|
$summaries,
|
|
$genericCaches,
|
|
$identityCandidates,
|
|
$genericExistingIdentities,
|
|
$genericSeenIdentities,
|
|
$verbose,
|
|
$targetToSource
|
|
);
|
|
// Add action labels
|
|
$items = array_map(function ($ent) use ($translatedActions) {
|
|
$ent['action_label'] = $translatedActions[$ent['action']] ?? $ent['action'];
|
|
|
|
return $ent;
|
|
}, $items);
|
|
// If only a single, ungrouped item, flatten to a single entity for convenience
|
|
if (count($items) === 1 && (($items[0]['group'] ?? '') === '')) {
|
|
$rowEntities[$rootKey] = $items[0];
|
|
} else {
|
|
$rowEntities[$rootKey] = $items;
|
|
}
|
|
} else {
|
|
[$genericEntity, $summaries, $genericCaches, $genericExistingIdentities, $genericSeenIdentities]
|
|
= $this->simulateGenericRoot(
|
|
$rootKey,
|
|
$val,
|
|
$summaries,
|
|
$genericCaches,
|
|
$reference,
|
|
$identityCandidates,
|
|
$genericExistingIdentities,
|
|
$genericSeenIdentities,
|
|
$verbose,
|
|
$targetToSource,
|
|
);
|
|
$rowEntities[$rootKey] = $genericEntity + [
|
|
'action_label' => $translatedActions[$genericEntity['action']] ?? $genericEntity['action'],
|
|
];
|
|
}
|
|
}
|
|
|
|
// Attach chain entities (client_case, person) if contract already existed
|
|
if ($existingContract && isset($rowEntities['contract']['reference'])) {
|
|
$contractRef = $rowEntities['contract']['reference'];
|
|
$contractModel = $contractRef && isset($contractCache[$contractRef]) ? $contractCache[$contractRef] : null;
|
|
if ($contractModel) {
|
|
// Load client_case if mapped root present
|
|
if (isset($entityRoots['client_case']) && $contractModel->client_case_id) {
|
|
$cc = ClientCase::query()->find($contractModel->client_case_id, ['id', 'client_ref', 'person_id']);
|
|
if ($cc) {
|
|
if (! isset($summaries['client_case'])) {
|
|
$summaries['client_case'] = [
|
|
'root' => 'client_case',
|
|
'total_rows' => 0,
|
|
'create' => 0,
|
|
'update' => 0,
|
|
'missing_ref' => 0,
|
|
'invalid' => 0,
|
|
'duplicate' => 0,
|
|
'duplicate_db' => 0,
|
|
];
|
|
}
|
|
$summaries['client_case']['total_rows']++;
|
|
$summaries['client_case']['update']++;
|
|
$rowEntities['client_case'] = [
|
|
'id' => $cc->id,
|
|
'reference' => $cc->client_ref,
|
|
'exists' => true,
|
|
'action' => 'update',
|
|
'action_label' => $translatedActions['update'] ?? 'posodobi',
|
|
'existing_chain' => true,
|
|
];
|
|
// Person from chain if mapped
|
|
if (isset($entityRoots['person']) && $cc->person_id) {
|
|
$p = Person::query()->find($cc->person_id, ['id', 'nu', 'full_name', 'first_name', 'last_name', 'birthday', 'description']);
|
|
if ($p) {
|
|
if (! isset($summaries['person'])) {
|
|
$summaries['person'] = [
|
|
'root' => 'person',
|
|
'total_rows' => 0,
|
|
'create' => 0,
|
|
'update' => 0,
|
|
'missing_ref' => 0,
|
|
'invalid' => 0,
|
|
'duplicate' => 0,
|
|
'duplicate_db' => 0,
|
|
];
|
|
}
|
|
$summaries['person']['total_rows']++;
|
|
$summaries['person']['update']++;
|
|
$rowEntities['person'] = [
|
|
'id' => $p->id,
|
|
'reference' => $p->nu ?? (string) $p->id,
|
|
'exists' => true,
|
|
'action' => 'update',
|
|
'action_label' => $translatedActions['update'] ?? 'posodobi',
|
|
'existing_chain' => true,
|
|
'full_name' => $p->full_name,
|
|
'first_name' => $p->first_name,
|
|
'last_name' => $p->last_name,
|
|
'birthday' => $p->birthday,
|
|
'description' => $p->description,
|
|
];
|
|
// Attach email/phone/address if their roots are mapped and we skipped generic simulation
|
|
if ($p->id) {
|
|
// Email
|
|
if (isset($entityRoots['email']) && ! isset($rowEntities['email'])) {
|
|
$em = Email::query()->where('person_id', $p->id)->orderBy('id')->first(['id', 'value']);
|
|
if ($em) {
|
|
if (! isset($summaries['email'])) {
|
|
$summaries['email'] = [
|
|
'root' => 'email', 'total_rows' => 0, 'create' => 0, 'update' => 0, 'missing_ref' => 0, 'invalid' => 0, 'duplicate' => 0, 'duplicate_db' => 0,
|
|
];
|
|
}
|
|
$summaries['email']['total_rows']++;
|
|
$summaries['email']['update']++;
|
|
$rowEntities['email'] = [
|
|
'id' => $em->id,
|
|
'reference' => $em->value,
|
|
'value' => $em->value,
|
|
'exists' => true,
|
|
'action' => 'update',
|
|
'action_label' => $translatedActions['update'] ?? 'posodobi',
|
|
'existing_chain' => true,
|
|
];
|
|
}
|
|
}
|
|
// Phone
|
|
if (isset($entityRoots['phone']) && ! isset($rowEntities['phone'])) {
|
|
$ph = PersonPhone::query()->where('person_id', $p->id)->orderBy('id')->first(['id', 'nu']);
|
|
if ($ph) {
|
|
if (! isset($summaries['phone'])) {
|
|
$summaries['phone'] = [
|
|
'root' => 'phone', 'total_rows' => 0, 'create' => 0, 'update' => 0, 'missing_ref' => 0, 'invalid' => 0, 'duplicate' => 0, 'duplicate_db' => 0,
|
|
];
|
|
}
|
|
$summaries['phone']['total_rows']++;
|
|
$summaries['phone']['update']++;
|
|
$rowEntities['phone'] = [
|
|
'id' => $ph->id,
|
|
'reference' => $ph->nu,
|
|
'nu' => $ph->nu,
|
|
'exists' => true,
|
|
'action' => 'update',
|
|
'action_label' => $translatedActions['update'] ?? 'posodobi',
|
|
'existing_chain' => true,
|
|
];
|
|
}
|
|
}
|
|
// Address
|
|
if (isset($entityRoots['address']) && ! isset($rowEntities['address'])) {
|
|
$ad = PersonAddress::query()->where('person_id', $p->id)->orderBy('id')->first(['id', 'address', 'country']);
|
|
if ($ad) {
|
|
if (! isset($summaries['address'])) {
|
|
$summaries['address'] = [
|
|
'root' => 'address', 'total_rows' => 0, 'create' => 0, 'update' => 0, 'missing_ref' => 0, 'invalid' => 0, 'duplicate' => 0, 'duplicate_db' => 0,
|
|
];
|
|
}
|
|
$summaries['address']['total_rows']++;
|
|
$summaries['address']['update']++;
|
|
$rowEntities['address'] = [
|
|
'id' => $ad->id,
|
|
'reference' => $ad->address,
|
|
'address' => $ad->address,
|
|
// postal_code removed (not in schema)
|
|
'country' => $ad->country,
|
|
'exists' => true,
|
|
'action' => 'update',
|
|
'action_label' => $translatedActions['update'] ?? 'posodobi',
|
|
'existing_chain' => true,
|
|
];
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
// If existing contract: upgrade generic email/phone/address entities (already simulated) to mark as chain if corresponding person attached
|
|
if ($existingContract && isset($rowEntities['person']['id'])) {
|
|
foreach (['email', 'phone', 'address'] as $gRoot) {
|
|
if (! isset($rowEntities[$gRoot])) {
|
|
continue;
|
|
}
|
|
$valRef = $rowEntities[$gRoot];
|
|
// If multi items array, set flag on each item
|
|
if (is_array($valRef) && isset($valRef[0]) && is_array($valRef[0])) {
|
|
foreach ($rowEntities[$gRoot] as &$it) {
|
|
if (! ($it['existing_chain'] ?? false)) {
|
|
$it['existing_chain'] = true;
|
|
}
|
|
}
|
|
unset($it);
|
|
} else {
|
|
if (! ($rowEntities[$gRoot]['existing_chain'] ?? false)) {
|
|
$rowEntities[$gRoot]['existing_chain'] = true; // mark for UI toggle
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
// Payment (affects account balance; may create implicit account)
|
|
if (isset($entityRoots['payment'])) {
|
|
// Inject inferred account if none mapped explicitly
|
|
if (! isset($entityRoots['account']) && isset($rowEntities['contract']['id'])) {
|
|
[$implicitAccount, $summaries, $accountCache] = $this->simulateImplicitAccount($rowEntities['contract']['id'], $summaries, $accountCache);
|
|
if ($implicitAccount) {
|
|
$rowEntities['account'] = $implicitAccount + [
|
|
'action_label' => $translatedActions[$implicitAccount['action']] ?? $implicitAccount['action'],
|
|
];
|
|
}
|
|
}
|
|
[$paymentEntity, $rowEntities, $summaries, $runningBalances, $existingPaymentRefs, $seenPaymentRefs] = $this->simulatePayment(
|
|
$val,
|
|
$rowEntities,
|
|
$summaries,
|
|
$runningBalances,
|
|
$targetToSource,
|
|
$verbose,
|
|
$existingPaymentRefs,
|
|
$seenPaymentRefs
|
|
);
|
|
$paymentEntity['status_label'] = $translatedStatuses[$paymentEntity['status']] ?? $paymentEntity['status'];
|
|
$rowEntities['payment'] = $paymentEntity;
|
|
}
|
|
|
|
// If verbose, attach source metadata for non-payment entities (reference fields) to aid debugging
|
|
if ($verbose) {
|
|
foreach ($rowEntities as $eroot => &$ent) {
|
|
$tf = $eroot.'.reference';
|
|
if (! isset($targetToSource[$tf])) {
|
|
continue;
|
|
}
|
|
if (is_array($ent) && isset($ent[0]) && is_array($ent[0])) {
|
|
foreach ($ent as &$item) {
|
|
$item['sources'] = $item['sources'] ?? [];
|
|
if (! isset($item['sources'][$tf])) {
|
|
$item['sources'][$tf] = [
|
|
'source_column' => $targetToSource[$tf],
|
|
'value' => $val($tf),
|
|
];
|
|
}
|
|
}
|
|
unset($item);
|
|
} else {
|
|
$ent['sources'] = $ent['sources'] ?? [];
|
|
if (! isset($ent['sources'][$tf])) {
|
|
$ent['sources'][$tf] = [
|
|
'source_column' => $targetToSource[$tf],
|
|
'value' => $val($tf),
|
|
];
|
|
}
|
|
}
|
|
}
|
|
unset($ent);
|
|
}
|
|
|
|
// Compute delta for account if present (frontend may filter on this)
|
|
if (isset($rowEntities['account']['balance_before'], $rowEntities['account']['balance_after'])) {
|
|
$rowEntities['account']['delta'] = $rowEntities['account']['balance_after'] - $rowEntities['account']['balance_before'];
|
|
}
|
|
|
|
$rowStatus = 'ok';
|
|
if (isset($rowEntities['payment']['status']) && $rowEntities['payment']['status'] !== 'ok') {
|
|
$rowStatus = $rowEntities['payment']['status'];
|
|
}
|
|
// If we skipped due to keyref, surface a warning status for the row if not already set
|
|
if ($rowStatus === 'ok' && $keyrefSkipRow) {
|
|
$rowStatus = 'warning';
|
|
}
|
|
$simRows[] = [
|
|
'index' => $idx + 1,
|
|
'entities' => $rowEntities,
|
|
'status' => $rowStatus,
|
|
];
|
|
}
|
|
|
|
// Prune roots that are entirely empty (all rows action=skip and no identity or preview data)
|
|
$nonEmptyRoots = [];
|
|
// Map whether root has any mapping keys (after normalization) to avoid hiding legitimately mapped-but-empty columns early
|
|
$rootHasMapping = [];
|
|
foreach (array_keys($targetToSource) as $tfKey) {
|
|
if (str_contains($tfKey, '.')) {
|
|
[$r] = explode('.', $tfKey, 2);
|
|
$rootHasMapping[$r] = true;
|
|
}
|
|
}
|
|
foreach ($simRows as $row) {
|
|
if (! isset($row['entities'])) {
|
|
continue;
|
|
}
|
|
foreach ($row['entities'] as $root => $ent) {
|
|
if (! isset($entityRoots[$root])) {
|
|
continue;
|
|
}
|
|
// Determine if entity has meaningful data
|
|
$hasData = false;
|
|
foreach (['reference', 'identity_used', 'identity_candidates', 'full_name', 'first_name', 'last_name', 'address', 'country', 'nu', 'value'] as $k) {
|
|
if (isset($ent[$k]) && $ent[$k]) {
|
|
$hasData = true;
|
|
}
|
|
}
|
|
// Some entities (e.g. payment) do not have 'action'; treat them as non-empty if they have data or status
|
|
if (! isset($ent['action']) || $ent['action'] !== 'skip' || $hasData || isset($ent['status'])) {
|
|
$nonEmptyRoots[$root] = true;
|
|
}
|
|
}
|
|
}
|
|
// Filter entityRoots and rows
|
|
$neverPrune = ['person', 'address', 'client_case'];
|
|
foreach (array_keys($entityRoots) as $root) {
|
|
if (! isset($nonEmptyRoots[$root])
|
|
&& ! in_array($root, ['contract', 'account', 'payment'], true)
|
|
&& ! in_array($root, $neverPrune, true)
|
|
&& empty($rootHasMapping[$root])
|
|
) {
|
|
unset($entityRoots[$root]);
|
|
unset($summaries[$root]);
|
|
// Remove from each row
|
|
foreach ($simRows as &$row) {
|
|
if (isset($row['entities'][$root])) {
|
|
unset($row['entities'][$root]);
|
|
}
|
|
}
|
|
unset($row);
|
|
}
|
|
}
|
|
|
|
// Add Slovenian summary mirror (does not replace original machine keys)
|
|
$localizedSummaries = $this->localizeSummaries($summaries);
|
|
|
|
return [
|
|
'rows' => $simRows,
|
|
'entities' => array_keys($entityRoots),
|
|
'summaries' => $summaries,
|
|
'povzetki' => $localizedSummaries, // Slovenian friendly summaries
|
|
'lokalizacija' => [
|
|
'dejanja' => $translatedActions,
|
|
'statusi' => $translatedStatuses,
|
|
],
|
|
];
|
|
}
|
|
|
|
/* ---------------------------- Helper: structure ---------------------------- */
|
|
|
|
private function buildTargetLookup(Import $import): array
|
|
{
|
|
$mappings = \DB::table('import_mappings')
|
|
->where('import_id', $import->id)
|
|
->orderBy('position')
|
|
->get(['source_column', 'target_field', 'options']);
|
|
|
|
$lookup = [];
|
|
$grouped = [];
|
|
foreach ($mappings as $m) {
|
|
$target = trim((string) $m->target_field);
|
|
$source = trim((string) $m->source_column);
|
|
if ($target === '' || $source === '') {
|
|
continue;
|
|
}
|
|
|
|
// Parse grouping and field
|
|
$group = null;
|
|
$root = null;
|
|
$rest = null;
|
|
$restEffective = null;
|
|
$opts = [];
|
|
if (preg_match('/^([a-zA-Z0-9_]+)\\[([^\]]+)\\]\\.([a-zA-Z0-9_]+)$/', $target, $mm)) {
|
|
$root = $mm[1];
|
|
$group = (string) $mm[2];
|
|
$rest = $mm[3];
|
|
$restEffective = $rest;
|
|
} else {
|
|
if (str_contains($target, '.')) {
|
|
[$root, $rest] = explode('.', $target, 2);
|
|
}
|
|
try {
|
|
$opts = is_array($m->options) ? $m->options : (json_decode((string) $m->options, true) ?: []);
|
|
} catch (\Throwable) {
|
|
$opts = [];
|
|
}
|
|
if (is_array($opts) && array_key_exists('group', $opts) && $opts['group'] !== '' && $opts['group'] !== null) {
|
|
$group = (string) $opts['group'];
|
|
}
|
|
$restEffective = $rest;
|
|
// Alias meta with options.key => meta.{key}
|
|
if ($rest === 'meta' && is_array($opts) && ! empty($opts['key'])) {
|
|
$restEffective = 'meta'.'.'.(string) $opts['key'];
|
|
}
|
|
}
|
|
|
|
// Register flat lookups
|
|
if (! isset($lookup[$target])) {
|
|
$lookup[$target] = $source;
|
|
}
|
|
if ($rest !== null) {
|
|
$normRoot = $this->normalizeRoot((string) $root);
|
|
$tfNorm = $normRoot.'.'.$restEffective;
|
|
if (! isset($lookup[$tfNorm])) {
|
|
$lookup[$tfNorm] = $source;
|
|
}
|
|
if (str_ends_with((string) $root, 's')) {
|
|
$sing = substr((string) $root, 0, -1);
|
|
if ($sing) {
|
|
$tfSing = $sing.'.'.$restEffective;
|
|
if (! isset($lookup[$tfSing])) {
|
|
$lookup[$tfSing] = $source;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
if (str_ends_with($target, '.client_ref')) {
|
|
$alias = substr($target, 0, -strlen('.client_ref')).'.reference';
|
|
if (! isset($lookup[$alias])) {
|
|
$lookup[$alias] = $source;
|
|
}
|
|
}
|
|
|
|
// Register grouped lookup per normalized root
|
|
if ($group !== null && $rest !== null) {
|
|
$normRoot = $this->normalizeRoot((string) $root);
|
|
if (! isset($grouped[$normRoot])) {
|
|
$grouped[$normRoot] = [];
|
|
}
|
|
if (! isset($grouped[$normRoot][$group])) {
|
|
$grouped[$normRoot][$group] = [];
|
|
}
|
|
$grouped[$normRoot][$group][$restEffective] = $source;
|
|
}
|
|
}
|
|
|
|
return [$lookup, $grouped];
|
|
}
|
|
|
|
private function readFileRows(Import $import, bool $hasHeader, string $delimiter, array $columns, int $limit): array
|
|
{
|
|
$path = Storage::disk($import->disk)->path($import->path);
|
|
if (! is_readable($path)) {
|
|
return ['error' => 'Datoteka ni berljiva'];
|
|
}
|
|
$fh = @fopen($path, 'r');
|
|
if (! $fh) {
|
|
return ['error' => 'Datoteke ni mogoče odpreti'];
|
|
}
|
|
if ($hasHeader) {
|
|
$header = fgetcsv($fh, 0, $delimiter) ?: [];
|
|
$columns = array_map(static fn ($h) => is_string($h) ? trim($h) : (string) $h, $header);
|
|
}
|
|
$rows = [];
|
|
$widest = count($columns);
|
|
while (($data = fgetcsv($fh, 0, $delimiter)) !== false && count($rows) < $limit) {
|
|
if (! $hasHeader) {
|
|
$widest = max($widest, count($data));
|
|
}
|
|
$rows[] = $data;
|
|
}
|
|
fclose($fh);
|
|
if (! $hasHeader && $widest > count($columns)) {
|
|
$columns = array_map(static fn ($i) => 'col_'.($i + 1), range(0, $widest - 1));
|
|
}
|
|
|
|
return compact('rows', 'columns');
|
|
}
|
|
|
|
private function detectEntityRoots(array $targetToSource): array
|
|
{
|
|
$roots = [];
|
|
foreach (array_keys($targetToSource) as $tf) {
|
|
if (str_contains($tf, '.')) {
|
|
[$root] = explode('.', $tf, 2);
|
|
$roots[$this->normalizeRoot($root)] = true;
|
|
}
|
|
}
|
|
|
|
return $roots; // associative for faster isset checks
|
|
}
|
|
|
|
/**
|
|
* Normalize mapping root keys (plural or table-like) to canonical simulation roots.
|
|
*/
|
|
private function normalizeRoot(string $root): string
|
|
{
|
|
static $map = [
|
|
'contracts' => 'contract',
|
|
'contract' => 'contract',
|
|
'accounts' => 'account',
|
|
'account' => 'account',
|
|
'payments' => 'payment',
|
|
'payment' => 'payment',
|
|
'emails' => 'email',
|
|
'email' => 'email',
|
|
'person_addresses' => 'address',
|
|
'person_address' => 'address',
|
|
'person_addresse' => 'address',
|
|
'addresses' => 'address',
|
|
'address' => 'address',
|
|
'person_phones' => 'phone',
|
|
'person_phone' => 'phone',
|
|
'phones' => 'phone',
|
|
'phone' => 'phone',
|
|
'client_cases' => 'client_case',
|
|
'client_case' => 'client_case',
|
|
'people' => 'person',
|
|
'persons' => 'person',
|
|
'person' => 'person',
|
|
];
|
|
|
|
return $map[$root] ?? $root;
|
|
}
|
|
|
|
/**
|
|
* Filter detected entity roots against supported list coming from import_entities table.
|
|
* Guarantees that core roots (payment, account, contract) are retained if they are mapped.
|
|
* If supported list is empty (e.g. table empty / query failure), falls back to all detected.
|
|
* Additionally, if filtering would yield an empty set while we still have mappings, it will
|
|
* keep the original detected set to avoid hiding entities (fail-open strategy for UX).
|
|
*/
|
|
private function filterEntityRoots(array $detected, array $supported, array $targetToSource): array
|
|
{
|
|
// Fail-open if no supported list gathered
|
|
if (empty($supported)) {
|
|
return $detected;
|
|
}
|
|
|
|
$supportedFlip = array_flip($supported);
|
|
$filtered = [];
|
|
foreach ($detected as $root => $flag) {
|
|
if (isset($supportedFlip[$root])) {
|
|
$filtered[$root] = $flag;
|
|
}
|
|
}
|
|
|
|
// Always retain core roots if they were mapped, even if not in supported list
|
|
foreach (['payment', 'account', 'contract', 'address'] as $core) {
|
|
if (isset($detected[$core])) {
|
|
$filtered[$core] = true;
|
|
}
|
|
}
|
|
|
|
// If after filtering nothing remains but mappings exist, revert (avoid confusing empty output)
|
|
if (empty($filtered) && ! empty($detected)) {
|
|
return $detected;
|
|
}
|
|
|
|
return $filtered;
|
|
}
|
|
|
|
private function initSummaries(array $entityRoots): array
|
|
{
|
|
$summaries = [];
|
|
foreach (array_keys($entityRoots) as $root) {
|
|
$summaries[$root] = [
|
|
'root' => $root,
|
|
'total_rows' => 0,
|
|
'create' => 0,
|
|
'update' => 0,
|
|
'missing_ref' => 0,
|
|
'invalid' => 0,
|
|
'duplicate' => 0,
|
|
'duplicate_db' => 0,
|
|
];
|
|
}
|
|
|
|
return $summaries;
|
|
}
|
|
|
|
private function associateRow(array $columns, array $values): array
|
|
{
|
|
$assoc = [];
|
|
foreach ($columns as $i => $col) {
|
|
$assoc[$col] = $values[$i] ?? null;
|
|
}
|
|
|
|
return $assoc;
|
|
}
|
|
|
|
/* -------------------------- Entity simulation parts -------------------------- */
|
|
|
|
private function simulateContract(callable $val, array $summaries, array $cache, ?string $reference): array
|
|
{
|
|
$contract = null;
|
|
if ($reference) {
|
|
if (array_key_exists($reference, $cache)) {
|
|
$contract = $cache[$reference];
|
|
} else {
|
|
$q = Contract::query()->where('reference', $reference);
|
|
// Scope to selected client when available
|
|
if (! is_null($this->clientId)) {
|
|
$q->whereHas('clientCase', function ($qq): void {
|
|
$qq->where('client_id', $this->clientId);
|
|
});
|
|
}
|
|
$contract = $q->first(['id', 'reference', 'client_case_id', 'active', 'deleted_at']);
|
|
$cache[$reference] = $contract; // may be null
|
|
}
|
|
}
|
|
$entity = [
|
|
'reference' => $reference,
|
|
'id' => $contract?->id,
|
|
'exists' => (bool) $contract,
|
|
'client_case_id' => $contract?->client_case_id,
|
|
'active' => $contract?->active,
|
|
'deleted_at' => $contract?->deleted_at,
|
|
'action' => $contract ? 'update' : ($reference ? 'create' : 'skip'),
|
|
];
|
|
$summaries['contract']['total_rows']++;
|
|
if (! $reference) {
|
|
$summaries['contract']['missing_ref']++;
|
|
} elseif ($contract) {
|
|
$summaries['contract']['update']++;
|
|
} else {
|
|
$summaries['contract']['create']++;
|
|
}
|
|
|
|
return [$entity, $summaries, $cache];
|
|
}
|
|
|
|
private function simulateAccount(callable $val, array $summaries, array $cache, ?string $reference): array
|
|
{
|
|
$account = null;
|
|
if ($reference) {
|
|
if (array_key_exists($reference, $cache)) {
|
|
$account = $cache[$reference];
|
|
} else {
|
|
$q = Account::query()
|
|
->where('reference', $reference)
|
|
->where('active', 1);
|
|
// Scope to selected client when available via contract -> clientCase
|
|
if (! is_null($this->clientId)) {
|
|
$q->whereHas('contract.clientCase', function ($qq): void {
|
|
$qq->where('client_id', $this->clientId);
|
|
});
|
|
}
|
|
$account = $q->first(['id', 'reference', 'balance_amount']);
|
|
$cache[$reference] = $account;
|
|
}
|
|
}
|
|
$entity = [
|
|
'reference' => $reference,
|
|
'id' => $account?->id,
|
|
'exists' => (bool) $account,
|
|
'balance_before' => $account?->balance_amount,
|
|
'balance_after' => $account?->balance_amount,
|
|
'action' => $account ? 'update' : ($reference ? 'create' : 'skip'),
|
|
];
|
|
|
|
// Direct balance override support.
|
|
// Some mappings may have stored the plural root ("accounts.balance_amount") instead of the singular
|
|
// that the value resolver expects. Also allow a simpler fallback key (account.balance).
|
|
$rawIncoming = $val('account.balance_amount')
|
|
?? $val('accounts.balance_amount')
|
|
?? $val('account.balance');
|
|
if ($rawIncoming !== null && $rawIncoming !== '') {
|
|
$rawStr = (string) $rawIncoming;
|
|
// Remove currency symbols and non numeric punctuation except , . -
|
|
$clean = preg_replace('/[^0-9,\.\-]+/', '', $rawStr) ?? '';
|
|
if ($clean !== '') {
|
|
// If both comma and dot exist, assume dot is decimal separator => strip commas (thousands)
|
|
if (str_contains($clean, ',') && str_contains($clean, '.')) {
|
|
$normalized = str_replace(',', '', $clean);
|
|
} else {
|
|
// Only one of them present -> treat comma as decimal separator
|
|
$normalized = str_replace(',', '.', $clean);
|
|
}
|
|
// Collapse multiple dots keeping last as decimal (edge case). If multiple appear, remove all but last.
|
|
if (substr_count($normalized, '.') > 1) {
|
|
$parts = explode('.', $normalized);
|
|
$last = array_pop($parts);
|
|
$normalized = preg_replace('/\.+/', '', implode('', $parts)).'.'.$last; // join integer part
|
|
}
|
|
if (is_numeric($normalized)) {
|
|
$incoming = (float) $normalized;
|
|
$entity['balance_after'] = $incoming;
|
|
$entity['direct_balance_override'] = true;
|
|
}
|
|
}
|
|
}
|
|
$summaries['account']['total_rows']++;
|
|
if (! $reference) {
|
|
$summaries['account']['missing_ref']++;
|
|
} elseif ($account) {
|
|
$summaries['account']['update']++;
|
|
} else {
|
|
$summaries['account']['create']++;
|
|
}
|
|
|
|
return [$entity, $summaries, $cache];
|
|
}
|
|
|
|
/**
|
|
* Lookup apply_mode for a mapping target field on this import.
|
|
* Returns lowercased mode like 'insert', 'update', 'both', 'keyref', or null if not found.
|
|
*/
|
|
private function mappingModeForImport(Import $import, string $targetField): ?string
|
|
{
|
|
$rows = \DB::table('import_mappings')
|
|
->where('import_id', $import->id)
|
|
->get(['target_field', 'apply_mode']);
|
|
foreach ($rows as $row) {
|
|
$tf = (string) ($row->target_field ?? '');
|
|
if ($tf === '') {
|
|
continue;
|
|
}
|
|
// Normalize root part to match canonical keys like contract.reference
|
|
if (str_contains($tf, '.')) {
|
|
[$root, $rest] = explode('.', $tf, 2);
|
|
} else {
|
|
$root = $tf;
|
|
$rest = null;
|
|
}
|
|
$norm = $this->normalizeRoot($root);
|
|
$tfNorm = $rest !== null ? ($norm.'.'.$rest) : $norm;
|
|
if ($tfNorm === $targetField) {
|
|
$mode = $row->apply_mode ?? null;
|
|
|
|
return is_string($mode) ? strtolower($mode) : null;
|
|
}
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
private function simulateImplicitAccount(int $contractId, array $summaries, array $cache): array
|
|
{
|
|
$acct = Account::query()->where('contract_id', $contractId)->orderBy('id')->first(['id', 'reference', 'balance_amount']);
|
|
if (! $acct) {
|
|
return [null, $summaries, $cache];
|
|
}
|
|
$entity = [
|
|
'reference' => $acct->reference,
|
|
'id' => $acct->id,
|
|
'exists' => true,
|
|
'balance_before' => $acct->balance_amount,
|
|
'balance_after' => $acct->balance_amount,
|
|
'action' => 'implicit',
|
|
'inferred' => true,
|
|
];
|
|
if (! isset($summaries['account'])) {
|
|
$summaries['account'] = [
|
|
'root' => 'account',
|
|
'total_rows' => 0,
|
|
'create' => 0,
|
|
'update' => 0,
|
|
'missing_ref' => 0,
|
|
'invalid' => 0,
|
|
];
|
|
}
|
|
$summaries['account']['total_rows']++;
|
|
$summaries['account']['update']++;
|
|
$cache[$acct->reference] = $acct;
|
|
|
|
return [$entity, $summaries, $cache];
|
|
}
|
|
|
|
private function simulatePayment(
|
|
callable $val,
|
|
array $rowEntities,
|
|
array $summaries,
|
|
array $runningBalances,
|
|
array $targetToSource,
|
|
bool $verbose,
|
|
array $existingPaymentRefs,
|
|
array $seenPaymentRefs,
|
|
): array {
|
|
$rawAmount = $val('payment.amount');
|
|
$amount = null;
|
|
if ($rawAmount !== null && $rawAmount !== '') {
|
|
$norm = str_replace([' ', ','], ['', '.'], (string) $rawAmount);
|
|
if (is_numeric($norm)) {
|
|
$amount = (float) $norm;
|
|
}
|
|
}
|
|
$date = $val('payment.payment_date');
|
|
$reference = $val('payment.reference');
|
|
|
|
// Adjust account running balance
|
|
if (isset($rowEntities['account']['id']) && empty($rowEntities['account']['direct_balance_override'])) {
|
|
$accId = $rowEntities['account']['id'];
|
|
$initial = $runningBalances[$accId] ?? (float) $rowEntities['account']['balance_before'];
|
|
$before = $initial;
|
|
$after = $initial;
|
|
if ($amount !== null) {
|
|
$after = $initial - $amount; // payment reduces balance
|
|
$runningBalances[$accId] = $after;
|
|
$rowEntities['account']['balance_before'] = $before;
|
|
$rowEntities['account']['balance_after'] = $after;
|
|
}
|
|
}
|
|
|
|
$entity = [
|
|
'amount' => $amount,
|
|
'payment_date' => $date,
|
|
'reference' => $reference,
|
|
'status' => $amount === null ? 'invalid_amount' : 'ok',
|
|
];
|
|
|
|
if ($verbose) { // Only include verbose structures when requested
|
|
$effectiveSources = [];
|
|
foreach (['payment.amount', 'payment.payment_date', 'payment.reference', 'contract.reference', 'account.reference'] as $tf) {
|
|
if (isset($targetToSource[$tf])) {
|
|
$effectiveSources[$tf] = [
|
|
'source_column' => $targetToSource[$tf],
|
|
'value' => $val($tf),
|
|
];
|
|
if ($tf === 'payment.amount') {
|
|
$effectiveSources[$tf]['normalized'] = $amount;
|
|
}
|
|
}
|
|
}
|
|
$entity['sources'] = $effectiveSources;
|
|
$entity['raw_amount'] = $rawAmount;
|
|
}
|
|
|
|
// Duplicate detection (only if have reference and an account id and status ok so far)
|
|
if ($entity['status'] === 'ok' && $reference !== null && $reference !== '' && isset($rowEntities['account']['id'])) {
|
|
$accId = $rowEntities['account']['id'];
|
|
// Load existing refs lazily
|
|
if (! isset($existingPaymentRefs[$accId])) {
|
|
$existingPaymentRefs[$accId] = [];
|
|
// Only query if account exists in DB (id assumed existing if action update/implicit)
|
|
if (! empty($accId)) {
|
|
foreach (Payment::query()->where('account_id', $accId)->pluck('reference') as $ref) {
|
|
if ($ref !== null && $ref !== '') {
|
|
$existingPaymentRefs[$accId][$ref] = true;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
if (isset($existingPaymentRefs[$accId][$reference])) {
|
|
$entity['status'] = 'duplicate_db';
|
|
} else {
|
|
if (! isset($seenPaymentRefs[$accId])) {
|
|
$seenPaymentRefs[$accId] = [];
|
|
}
|
|
if (isset($seenPaymentRefs[$accId][$reference])) {
|
|
$entity['status'] = 'duplicate';
|
|
} else {
|
|
$seenPaymentRefs[$accId][$reference] = true;
|
|
}
|
|
}
|
|
}
|
|
|
|
$summaries['payment']['total_rows'] = ($summaries['payment']['total_rows'] ?? 0) + 1;
|
|
if ($amount === null) {
|
|
$summaries['payment']['invalid'] = ($summaries['payment']['invalid'] ?? 0) + 1;
|
|
}
|
|
if (isset($entity['status']) && $entity['status'] === 'duplicate') {
|
|
$summaries['payment']['duplicate'] = ($summaries['payment']['duplicate'] ?? 0) + 1;
|
|
}
|
|
if (isset($entity['status']) && $entity['status'] === 'duplicate_db') {
|
|
$summaries['payment']['duplicate_db'] = ($summaries['payment']['duplicate_db'] ?? 0) + 1;
|
|
}
|
|
|
|
return [$entity, $rowEntities, $summaries, $runningBalances, $existingPaymentRefs, $seenPaymentRefs];
|
|
}
|
|
|
|
private function simulateGenericRoot(
|
|
string $root,
|
|
callable $val,
|
|
array $summaries,
|
|
array $genericCaches,
|
|
?string $reference,
|
|
array $identityCandidates,
|
|
array $genericExistingIdentities,
|
|
array $genericSeenIdentities,
|
|
bool $verbose = false,
|
|
array $targetToSource = [],
|
|
): array {
|
|
// Ensure summary bucket exists
|
|
if (! isset($summaries[$root])) {
|
|
$summaries[$root] = [
|
|
'root' => $root,
|
|
'total_rows' => 0,
|
|
'create' => 0,
|
|
'update' => 0,
|
|
'missing_ref' => 0,
|
|
'invalid' => 0,
|
|
'duplicate' => 0,
|
|
'duplicate_db' => 0,
|
|
];
|
|
}
|
|
$summaries[$root]['total_rows']++;
|
|
|
|
$modelClass = $this->modelClassForGeneric($root);
|
|
$record = null;
|
|
if ($reference) {
|
|
if (! isset($genericCaches[$root])) {
|
|
$genericCaches[$root] = [];
|
|
}
|
|
if (array_key_exists($reference, $genericCaches[$root])) {
|
|
$record = $genericCaches[$root][$reference];
|
|
} elseif ($modelClass && class_exists($modelClass)) {
|
|
// Try/catch to avoid issues if column doesn't exist
|
|
try {
|
|
if (\Schema::hasColumn((new $modelClass)->getTable(), 'reference')) {
|
|
$qb = $modelClass::query()->where('reference', $reference);
|
|
// Scope client_case lookups to selected client
|
|
if ($modelClass === \App\Models\ClientCase::class && ! is_null($this->clientId)) {
|
|
$qb->where('client_id', $this->clientId);
|
|
}
|
|
$record = $qb->first(['id', 'reference']);
|
|
}
|
|
} catch (\Throwable) {
|
|
$record = null;
|
|
}
|
|
$genericCaches[$root][$reference] = $record; // may be null
|
|
}
|
|
}
|
|
|
|
// Fallback reference derivation for specific roots when explicit reference missing
|
|
if (! $reference) {
|
|
if ($root === 'client_case') {
|
|
$reference = $val('client_case.client_ref');
|
|
} elseif ($root === 'person') {
|
|
// Derive pseudo-reference from first_name (or full_name) if nothing else present so UI shows something
|
|
$reference = $val('person.first_name') ?: $val('person.full_name');
|
|
} elseif ($root === 'address') {
|
|
$reference = $val('address.address');
|
|
} elseif ($root === 'phone') {
|
|
$reference = $val('phone.nu');
|
|
} elseif ($root === 'email') {
|
|
$reference = $val('email.value');
|
|
}
|
|
}
|
|
|
|
$entity = [
|
|
'reference' => $reference,
|
|
'id' => $record?->id,
|
|
'exists' => (bool) $record,
|
|
'action' => $reference ? ($record ? 'update' : 'create') : 'skip',
|
|
// collect identity candidates for UI (raw list) and chosen identity marker
|
|
'identity_candidates' => $identityCandidates,
|
|
];
|
|
|
|
// Lightweight attribute previews (non-persistent, for UI clarity only)
|
|
switch ($root) {
|
|
case 'person':
|
|
$entity['full_name'] = $val('person.full_name') ?? null;
|
|
$entity['first_name'] = $val('person.first_name') ?? null;
|
|
$entity['last_name'] = $val('person.last_name') ?? null;
|
|
$entity['description'] = $val('person.description') ?? null;
|
|
$entity['birthday'] = $val('person.birthday') ?? null;
|
|
break;
|
|
case 'address':
|
|
$entity['address'] = $val('address.address') ?? null;
|
|
// postal_code not present in schema
|
|
$entity['country'] = $val('address.country') ?? null;
|
|
break;
|
|
case 'phone':
|
|
$rawNu = $val('phone.nu') ?? null;
|
|
// Strip all non-numeric characters from phone number
|
|
$entity['nu'] = $rawNu !== null ? preg_replace('/[^0-9]/', '', (string) $rawNu) : null;
|
|
break;
|
|
case 'email':
|
|
$entity['value'] = $val('email.value') ?? null;
|
|
break;
|
|
case 'client_case':
|
|
$entity['title'] = $val('client_case.title') ?? null;
|
|
$entity['status'] = $val('client_case.status') ?? null;
|
|
break;
|
|
case 'case_object':
|
|
$entity['name'] = $val('case_object.name') ?? null;
|
|
$entity['description'] = $val('case_object.description') ?? null;
|
|
$entity['type'] = $val('case_object.type') ?? null;
|
|
break;
|
|
}
|
|
|
|
if ($verbose) {
|
|
$srcs = [];
|
|
foreach ($targetToSource as $tf => $col) {
|
|
if (str_starts_with($tf, $root.'.')) {
|
|
$srcs[$tf] = [
|
|
'source_column' => $col,
|
|
'value' => $val($tf),
|
|
];
|
|
}
|
|
}
|
|
if ($srcs) {
|
|
$entity['sources'] = $entity['sources'] ?? [];
|
|
$entity['sources'] += $srcs;
|
|
}
|
|
}
|
|
if (! $reference) {
|
|
$summaries[$root]['missing_ref']++;
|
|
} elseif ($record) {
|
|
$summaries[$root]['update']++;
|
|
} else {
|
|
$summaries[$root]['create']++;
|
|
}
|
|
// Duplicate detection based on identity candidates (first successful identity used)
|
|
foreach ($identityCandidates as $identity) {
|
|
if ($identity === null || $identity === '') {
|
|
continue;
|
|
}
|
|
// Load existing identities once per root
|
|
if (! isset($genericExistingIdentities[$root])) {
|
|
$genericExistingIdentities[$root] = $this->loadExistingGenericIdentities($root);
|
|
}
|
|
if (isset($genericExistingIdentities[$root][$identity])) {
|
|
$entity['duplicate_db'] = true;
|
|
$entity['identity_used'] = $identity;
|
|
$summaries[$root]['duplicate_db']++;
|
|
break;
|
|
}
|
|
if (! isset($genericSeenIdentities[$root])) {
|
|
$genericSeenIdentities[$root] = [];
|
|
}
|
|
if (isset($genericSeenIdentities[$root][$identity])) {
|
|
$entity['duplicate'] = true;
|
|
$entity['identity_used'] = $identity;
|
|
$summaries[$root]['duplicate']++;
|
|
break;
|
|
}
|
|
// Mark seen and continue to next identity candidate (only first unique tracked)
|
|
$genericSeenIdentities[$root][$identity] = true;
|
|
$entity['identity_used'] = $identity;
|
|
break;
|
|
}
|
|
|
|
return [$entity, $summaries, $genericCaches, $genericExistingIdentities, $genericSeenIdentities];
|
|
}
|
|
|
|
private function genericIdentityCandidates(string $root, callable $val): array
|
|
{
|
|
switch ($root) {
|
|
case 'email':
|
|
$v = $val('email.value');
|
|
|
|
return $v ? ['value:'.mb_strtolower(trim((string) $v))] : [];
|
|
case 'phone':
|
|
$nu = $val('phone.nu');
|
|
if ($nu) {
|
|
// Strip all non-numeric characters from phone number
|
|
$norm = preg_replace('/[^0-9]/', '', (string) $nu) ?? '';
|
|
|
|
return $norm ? ['nu:'.$norm] : [];
|
|
}
|
|
|
|
return [];
|
|
case 'person':
|
|
$ids = [];
|
|
$tax = $val('person.tax_number');
|
|
if ($tax) {
|
|
$ids[] = 'tax:'.mb_strtolower(trim((string) $tax));
|
|
}
|
|
$ssn = $val('person.social_security_number');
|
|
if ($ssn) {
|
|
$ids[] = 'ssn:'.mb_strtolower(trim((string) $ssn));
|
|
}
|
|
$full = $val('person.full_name');
|
|
if ($full) {
|
|
$ids[] = 'full:'.mb_strtolower(trim((string) $full));
|
|
}
|
|
|
|
return $ids;
|
|
case 'address':
|
|
$addr = $val('address.address');
|
|
$pc = null; // postal code not stored
|
|
$country = $val('address.country');
|
|
if ($addr || $pc || $country) {
|
|
$key = mb_strtolower(trim((string) ($addr ?? ''))).'|'.mb_strtolower(trim((string) ($pc ?? ''))).'|'.mb_strtolower(trim((string) ($country ?? '')));
|
|
|
|
return ['addr:'.$key];
|
|
}
|
|
|
|
return [];
|
|
case 'case_object':
|
|
$ref = $val('case_object.reference');
|
|
$name = $val('case_object.name');
|
|
$ids = [];
|
|
if ($ref) {
|
|
// Normalize reference (remove spaces)
|
|
$normRef = preg_replace('/\s+/', '', trim((string) $ref));
|
|
$ids[] = 'ref:'.$normRef;
|
|
}
|
|
if ($name) {
|
|
$ids[] = 'name:'.mb_strtolower(trim((string) $name));
|
|
}
|
|
|
|
return $ids;
|
|
default:
|
|
return [];
|
|
}
|
|
}
|
|
|
|
private function loadExistingGenericIdentities(string $root): array
|
|
{
|
|
$set = [];
|
|
try {
|
|
switch ($root) {
|
|
case 'email':
|
|
foreach (\App\Models\Email::query()->pluck('value') as $v) {
|
|
if ($v) {
|
|
$set['value:'.mb_strtolower(trim((string) $v))] = true;
|
|
}
|
|
}
|
|
break;
|
|
case 'phone':
|
|
foreach (\App\Models\Person\PersonPhone::query()->pluck('nu') as $p) {
|
|
if ($p) {
|
|
// Strip all non-numeric characters from phone number
|
|
$set['nu:'.preg_replace('/[^0-9]/', '', (string) $p)] = true;
|
|
}
|
|
}
|
|
break;
|
|
case 'person':
|
|
foreach (\App\Models\Person\Person::query()->get(['tax_number', 'social_security_number', 'full_name']) as $rec) {
|
|
if ($rec->tax_number) {
|
|
$set['tax:'.mb_strtolower(trim((string) $rec->tax_number))] = true;
|
|
}
|
|
if ($rec->social_security_number) {
|
|
$set['ssn:'.mb_strtolower(trim((string) $rec->social_security_number))] = true;
|
|
}
|
|
if ($rec->full_name) {
|
|
$set['full:'.mb_strtolower(trim((string) $rec->full_name))] = true;
|
|
}
|
|
}
|
|
break;
|
|
case 'address':
|
|
foreach (\App\Models\Person\PersonAddress::query()->get(['address', 'country']) as $rec) {
|
|
$key = mb_strtolower(trim((string) ($rec->address ?? ''))).'|'.mb_strtolower(trim((string) ($rec->country ?? '')));
|
|
if (trim($key, '|') !== '') {
|
|
$set['addr:'.$key] = true;
|
|
}
|
|
}
|
|
break;
|
|
case 'case_object':
|
|
foreach (\App\Models\CaseObject::query()->get(['reference', 'name']) as $rec) {
|
|
if ($rec->reference) {
|
|
// Normalize reference (remove spaces)
|
|
$normRef = preg_replace('/\s+/', '', trim((string) $rec->reference));
|
|
$set['ref:'.$normRef] = true;
|
|
}
|
|
if ($rec->name) {
|
|
$set['name:'.mb_strtolower(trim((string) $rec->name))] = true;
|
|
}
|
|
}
|
|
break;
|
|
}
|
|
} catch (\Throwable) {
|
|
// swallow and return what we have
|
|
}
|
|
|
|
return $set;
|
|
}
|
|
|
|
private function modelClassForGeneric(string $root): ?string
|
|
{
|
|
// Explicit mapping for known roots; extend as needed
|
|
return [
|
|
'person' => \App\Models\Person\Person::class,
|
|
'address' => \App\Models\Person\PersonAddress::class,
|
|
'phone' => \App\Models\Person\PersonPhone::class,
|
|
'email' => \App\Models\Email::class,
|
|
'booking' => \App\Models\Booking::class,
|
|
'activity' => \App\Models\Activity::class,
|
|
'client' => \App\Models\Client::class,
|
|
'client_case' => \App\Models\ClientCase::class,
|
|
'case_object' => \App\Models\CaseObject::class,
|
|
][$root] ?? null;
|
|
}
|
|
|
|
private function loadSupportedEntityRoots(): array
|
|
{
|
|
// Pull keys + canonical_root from import_entities table to determine allowed roots
|
|
try {
|
|
$rows = \App\Models\ImportEntity::query()->get(['key', 'canonical_root']);
|
|
$roots = [];
|
|
foreach ($rows as $r) {
|
|
if ($r->canonical_root) {
|
|
$roots[] = $r->canonical_root;
|
|
}
|
|
if ($r->key) {
|
|
// keys sometimes plural; we only want canonical forms for simulation root detection
|
|
// keep both to be safe
|
|
$roots[] = $r->key;
|
|
}
|
|
}
|
|
// Normalize underscores plural forms to canonical ones (contracts -> contract) where possible
|
|
$roots = array_unique(array_map(function ($v) {
|
|
if (str_ends_with($v, 's')) {
|
|
$sing = substr($v, 0, -1);
|
|
|
|
return $sing ?: $v;
|
|
}
|
|
|
|
return $v;
|
|
}, $roots));
|
|
|
|
return $roots;
|
|
} catch (\Throwable) {
|
|
// Fallback: allow existing known roots if table unavailable
|
|
return ['contract', 'account', 'payment', 'person', 'address', 'phone', 'email', 'booking', 'activity', 'client', 'client_case'];
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Load which canonical roots support multiple items (grouped) from import_entities.supports_multiple.
|
|
* Falls back to known defaults if table/column is unavailable.
|
|
*/
|
|
private function loadSupportsMultipleRoots(): array
|
|
{
|
|
$map = [];
|
|
try {
|
|
if (\Schema::hasTable('import_entities') && \Schema::hasColumn('import_entities', 'supports_multiple')) {
|
|
$rows = \App\Models\ImportEntity::query()->get(['key', 'canonical_root', 'supports_multiple']);
|
|
foreach ($rows as $r) {
|
|
$root = $r->canonical_root ?: $r->key;
|
|
if (! $root) {
|
|
continue;
|
|
}
|
|
$norm = $this->normalizeRoot($root);
|
|
$map[$norm] = (bool) $r->supports_multiple;
|
|
}
|
|
}
|
|
} catch (\Throwable) {
|
|
// ignore and fallback
|
|
}
|
|
if (empty($map)) {
|
|
// Conservative defaults: only known contact types are multi
|
|
$map = [
|
|
'email' => true,
|
|
'phone' => true,
|
|
'address' => true,
|
|
];
|
|
}
|
|
|
|
return $map;
|
|
}
|
|
|
|
/**
|
|
* Simulate generic root that supports multiple items (grouped). Returns array of entities per group.
|
|
* Mirrors simulateGenericRoot per item while grouping by provided group values.
|
|
*
|
|
* @return array{0: array<int, array>, 1: array, 2: array, 3: array, 4: array}
|
|
*/
|
|
private function simulateGenericRootMulti(
|
|
string $root,
|
|
callable $val,
|
|
callable $groupVals,
|
|
array $summaries,
|
|
array $genericCaches,
|
|
array $identityCandidatesBase,
|
|
array $genericExistingIdentities,
|
|
array $genericSeenIdentities,
|
|
bool $verbose = false,
|
|
array $targetToSource = [],
|
|
): array {
|
|
// Build per-group entities
|
|
$items = [];
|
|
|
|
// Determine fields per root to preview
|
|
$previewFields = [];
|
|
if ($root === 'email') {
|
|
$previewFields = ['value'];
|
|
} elseif ($root === 'phone') {
|
|
$previewFields = ['nu'];
|
|
} elseif ($root === 'address') {
|
|
$previewFields = ['address', 'country']; // postal_code not present
|
|
}
|
|
|
|
// Collect groups seen for this root
|
|
$groups = [];
|
|
foreach ($previewFields as $pf) {
|
|
$map = $groupVals($root, $pf);
|
|
foreach ($map as $g => $v) {
|
|
$groups[$g] = true;
|
|
}
|
|
}
|
|
// If no groups but flat mapping exists, still simulate single default item
|
|
if (empty($groups)) {
|
|
$groups[''] = true;
|
|
}
|
|
|
|
// Ensure summary bucket exists and count total_rows once per row (not per item)
|
|
if (! isset($summaries[$root])) {
|
|
$summaries[$root] = [
|
|
'root' => $root,
|
|
'total_rows' => 0,
|
|
'create' => 0,
|
|
'update' => 0,
|
|
'missing_ref' => 0,
|
|
'invalid' => 0,
|
|
'duplicate' => 0,
|
|
'duplicate_db' => 0,
|
|
];
|
|
}
|
|
$summaries[$root]['total_rows']++;
|
|
|
|
// For each group, produce an item entity similar to simulateGenericRoot
|
|
foreach (array_keys($groups) as $g) {
|
|
// Build reference and identity candidates per group
|
|
$reference = null;
|
|
if ($root === 'email') {
|
|
$reference = $groupVals('email', 'value')[$g] ?? null;
|
|
} elseif ($root === 'phone') {
|
|
$reference = $groupVals('phone', 'nu')[$g] ?? null;
|
|
} elseif ($root === 'address') {
|
|
$reference = $groupVals('address', 'address')[$g] ?? null;
|
|
}
|
|
|
|
$identityCandidates = $identityCandidatesBase;
|
|
// Override with group-specific identity when present
|
|
if ($root === 'email') {
|
|
$v = $groupVals('email', 'value')[$g] ?? null;
|
|
if ($v) {
|
|
$identityCandidates = ['value:'.mb_strtolower(trim((string) $v))];
|
|
}
|
|
} elseif ($root === 'phone') {
|
|
$nu = $groupVals('phone', 'nu')[$g] ?? null;
|
|
if ($nu) {
|
|
// Strip all non-numeric characters from phone number
|
|
$norm = preg_replace('/[^0-9]/', '', (string) $nu) ?? '';
|
|
if ($norm) {
|
|
$identityCandidates = ['nu:'.$norm];
|
|
}
|
|
}
|
|
} elseif ($root === 'address') {
|
|
$addr = $groupVals('address', 'address')[$g] ?? null;
|
|
$country = $groupVals('address', 'country')[$g] ?? null;
|
|
if ($addr || $country) {
|
|
$key = mb_strtolower(trim((string) ($addr ?? ''))).'|'.mb_strtolower(trim((string) ($country ?? '')));
|
|
$identityCandidates = ['addr:'.$key];
|
|
}
|
|
}
|
|
|
|
// Query existing by reference when available
|
|
$modelClass = $this->modelClassForGeneric($root);
|
|
$record = null;
|
|
if ($reference) {
|
|
if (! isset($genericCaches[$root])) {
|
|
$genericCaches[$root] = [];
|
|
}
|
|
if (array_key_exists($reference, $genericCaches[$root])) {
|
|
$record = $genericCaches[$root][$reference];
|
|
} elseif ($modelClass && class_exists($modelClass)) {
|
|
try {
|
|
if (\Schema::hasColumn((new $modelClass)->getTable(), 'reference')) {
|
|
$qb = $modelClass::query()->where('reference', $reference);
|
|
if ($modelClass === \App\Models\ClientCase::class && ! is_null($this->clientId)) {
|
|
$qb->where('client_id', $this->clientId);
|
|
}
|
|
$record = $qb->first(['id', 'reference']);
|
|
}
|
|
} catch (\Throwable) {
|
|
$record = null;
|
|
}
|
|
$genericCaches[$root][$reference] = $record; // may be null
|
|
}
|
|
}
|
|
|
|
$entity = [
|
|
'reference' => $reference,
|
|
'id' => $record?->id,
|
|
'exists' => (bool) $record,
|
|
'action' => $reference ? ($record ? 'update' : 'create') : 'skip',
|
|
'group' => $g,
|
|
'identity_candidates' => $identityCandidates,
|
|
];
|
|
|
|
// Previews
|
|
if ($root === 'email') {
|
|
$entity['value'] = $groupVals('email', 'value')[$g] ?? null;
|
|
} elseif ($root === 'phone') {
|
|
$rawNu = $groupVals('phone', 'nu')[$g] ?? null;
|
|
// Strip all non-numeric characters from phone number
|
|
$entity['nu'] = $rawNu !== null ? preg_replace('/[^0-9]/', '', (string) $rawNu) : null;
|
|
} elseif ($root === 'address') {
|
|
$entity['address'] = $groupVals('address', 'address')[$g] ?? null;
|
|
$entity['country'] = $groupVals('address', 'country')[$g] ?? null;
|
|
}
|
|
|
|
if ($verbose) {
|
|
$srcs = [];
|
|
foreach ($targetToSource as $tf => $col) {
|
|
if (str_starts_with($tf, $root.'.')) {
|
|
// only include fields that match this group if grouped mapping exists
|
|
// We can't easily map back to group here without reverse index; include anyway for now
|
|
$srcs[$tf] = [
|
|
'source_column' => $col,
|
|
'value' => $val($tf),
|
|
];
|
|
}
|
|
}
|
|
if ($srcs) {
|
|
$entity['sources'] = $entity['sources'] ?? [];
|
|
$entity['sources'] += $srcs;
|
|
}
|
|
}
|
|
|
|
if (! $reference) {
|
|
$summaries[$root]['missing_ref']++;
|
|
} elseif ($record) {
|
|
$summaries[$root]['update']++;
|
|
} else {
|
|
$summaries[$root]['create']++;
|
|
}
|
|
|
|
// Duplicate detection per root across items in stream
|
|
foreach ($identityCandidates as $identity) {
|
|
if ($identity === null || $identity === '') {
|
|
continue;
|
|
}
|
|
if (! isset($genericExistingIdentities[$root])) {
|
|
$genericExistingIdentities[$root] = $this->loadExistingGenericIdentities($root);
|
|
}
|
|
if (isset($genericExistingIdentities[$root][$identity])) {
|
|
$entity['duplicate_db'] = true;
|
|
$entity['identity_used'] = $identity;
|
|
$summaries[$root]['duplicate_db']++;
|
|
break;
|
|
}
|
|
if (! isset($genericSeenIdentities[$root])) {
|
|
$genericSeenIdentities[$root] = [];
|
|
}
|
|
if (isset($genericSeenIdentities[$root][$identity])) {
|
|
$entity['duplicate'] = true;
|
|
$entity['identity_used'] = $identity;
|
|
$summaries[$root]['duplicate']++;
|
|
break;
|
|
}
|
|
$genericSeenIdentities[$root][$identity] = true;
|
|
$entity['identity_used'] = $identity;
|
|
break;
|
|
}
|
|
|
|
$items[] = $entity;
|
|
}
|
|
|
|
return [$items, $summaries, $genericCaches, $genericExistingIdentities, $genericSeenIdentities];
|
|
}
|
|
|
|
/* ------------------------------- Localization ------------------------------- */
|
|
|
|
private function actionTranslations(): array
|
|
{
|
|
return [
|
|
'create' => 'ustvari',
|
|
'update' => 'posodobi',
|
|
'skip' => 'preskoči',
|
|
'implicit' => 'posredno',
|
|
'reactivate' => 'reaktiviraj',
|
|
];
|
|
}
|
|
|
|
private function statusTranslations(): array
|
|
{
|
|
return [
|
|
'ok' => 'v_redu',
|
|
'invalid_amount' => 'neveljaven_znesek',
|
|
'duplicate' => 'podvojen',
|
|
'duplicate_db' => 'podvojen_v_bazi',
|
|
];
|
|
}
|
|
|
|
private function localizeSummaries(array $summaries): array
|
|
{
|
|
$map = [];
|
|
foreach ($summaries as $root => $s) {
|
|
$map[$root] = [
|
|
'koren' => $root,
|
|
'vrstice_skupaj' => $s['total_rows'],
|
|
'za_ustvariti' => $s['create'],
|
|
'za_posodobiti' => $s['update'],
|
|
'manjkajoca_referenca' => $s['missing_ref'],
|
|
'neveljavno' => $s['invalid'],
|
|
'podvojeni' => $s['duplicate'] ?? 0,
|
|
'podvojeni_v_bazi' => $s['duplicate_db'] ?? 0,
|
|
];
|
|
}
|
|
|
|
return $map;
|
|
}
|
|
|
|
private function errorPayload(string $message): array
|
|
{
|
|
return [
|
|
'rows' => [],
|
|
'entities' => [],
|
|
'summaries' => [],
|
|
'error' => $message,
|
|
];
|
|
}
|
|
}
|