68 lines
4.2 KiB
PHP
68 lines
4.2 KiB
PHP
<?php
|
|
|
|
use Illuminate\Contracts\Console\Kernel;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Str;
|
|
|
|
require __DIR__.'/../vendor/autoload.php';
|
|
$app = require __DIR__.'/../bootstrap/app.php';
|
|
$app->make(Kernel::class)->bootstrap();
|
|
if (! $app->environment('local') || DB::connection()->getDriverName() !== 'pgsql') {
|
|
throw new RuntimeException('Local PostgreSQL only.');
|
|
}
|
|
$checksum = '964a187ad32ea553b9e112cd159caa90b900050c42b7329594a75bcd798a96ff';
|
|
if (hash_file('sha256', 'C:/Mac/Home/Documents/eBAU/cartografia_moz.xlsx') !== $checksum) {
|
|
throw new RuntimeException('Source changed.');
|
|
}
|
|
$data = json_decode(file_get_contents(storage_path('app/private/territory-workbook-candidates.json')), true, 512, JSON_THROW_ON_ERROR);
|
|
$result = DB::transaction(function () use ($data, $checksum) {
|
|
DB::select('SELECT pg_advisory_xact_lock(280926, 150000)');
|
|
$ids = [];
|
|
foreach ($data['nodes'] as $node) {
|
|
$record = DB::table('geo_locations')->where('id', $node['id'])->where('source_system', 'cartografia-workbook')->where('external_id', $checksum.':'.$node['id'])->whereNull('deleted_at')->first();
|
|
if (! $record || ! in_array($record->data_classification, ['internal', 'public'], true)) {
|
|
throw new RuntimeException('Reference unavailable or reclassified.');
|
|
}
|
|
foreach (['name', 'parent_id', 'location_type', 'code'] as $field) {
|
|
if ($node[$field] !== $record->$field) {
|
|
throw new RuntimeException('Reference changed since import.');
|
|
}
|
|
}
|
|
$ids[] = $node['id'];
|
|
}
|
|
$published = DB::table('geo_locations')->whereIn('id', $ids)->where('data_classification', 'internal')->update(['data_classification' => 'public']);
|
|
$matched = $unmatched = [];
|
|
foreach (['opportunities', 'investment_zones'] as $table) {
|
|
foreach (DB::table($table)->whereNull('deleted_at')->whereNull('primary_territory_id')->whereNotNull('province')->get() as $record) {
|
|
$normalise = fn ($value) => mb_strtolower(Str::ascii(trim((string) $value)));
|
|
$province = DB::table('geo_locations')->whereIn('id', $ids)->where('location_type', 'province')->get()->filter(fn ($node) => $normalise($node->name) === $normalise($record->province))->values();
|
|
if ($province->count() !== 1) {
|
|
$unmatched[] = ['table' => $table, 'id' => $record->id, 'reason' => 'province_not_unique'];
|
|
|
|
continue;
|
|
}
|
|
$location = $province->first();
|
|
if (trim((string) $record->district) !== '') {
|
|
$district = DB::table('geo_locations')->whereIn('id', $ids)->where('location_type', 'district')->where('parent_id', $location->id)->get()->filter(fn ($node) => $normalise($node->name) === $normalise($record->district))->values();
|
|
if ($district->count() !== 1) {
|
|
$unmatched[] = ['table' => $table, 'id' => $record->id, 'reason' => 'district_not_unique'];
|
|
|
|
continue;
|
|
}
|
|
$location = $district->first();
|
|
}
|
|
DB::table($table)->where('id', $record->id)->update(['primary_territory_id' => $location->id, 'territory_version' => $record->territory_version + 1]);
|
|
$matched[] = ['table' => $table, 'id' => $record->id, 'territory_id' => $location->id];
|
|
}
|
|
}
|
|
$result = ['published_reference_names' => $published, 'matched' => $matched, 'unmatched' => $unmatched,
|
|
'basis' => 'owner_authorised_public_dropdowns', 'checksum' => $checksum, 'official_territory_publication' => false];
|
|
if ($published || $matched !== []) {
|
|
DB::table('audit_events')->insert(['source_system' => 'territory-reference-publication', 'entity_type' => 'geo_locations', 'action' => 'reference_names_enabled', 'after_data' => json_encode($result, JSON_THROW_ON_ERROR)]);
|
|
}
|
|
|
|
return $result;
|
|
});
|
|
file_put_contents(storage_path('app/private/territory-publication-'.date('Ymd-His').'.json'), json_encode($result, JSON_PRETTY_PRINT | JSON_THROW_ON_ERROR));
|
|
echo json_encode(['reference_names_enabled' => $result['published_reference_names'], 'exact_associations' => count($result['matched']), 'unmatched' => count($result['unmatched'])], JSON_THROW_ON_ERROR).PHP_EOL;
|