225 lines
12 KiB
PHP
225 lines
12 KiB
PHP
<?php
|
|
|
|
namespace App\Services;
|
|
|
|
use App\Models\InvestmentDossier;
|
|
use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
|
|
use Illuminate\Database\Query\Builder;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Validation\ValidationException;
|
|
|
|
final class InvestmentGeography
|
|
{
|
|
public const FIELDS = ['province_id' => 'province', 'district_id' => 'district', 'admin_post_id' => 'admin_post', 'locality_id' => 'locality'];
|
|
|
|
/**
|
|
* Restrict published dossier searches using direct territories and public, validated structure links.
|
|
*
|
|
* @param EloquentBuilder<InvestmentDossier> $query
|
|
* @param list<string> $territoryIds
|
|
* @return EloquentBuilder<InvestmentDossier>
|
|
*/
|
|
public function constrainDossiersToTerritories(EloquentBuilder $query, array $territoryIds): EloquentBuilder
|
|
{
|
|
return $query->where(function ($matches) use ($territoryIds): void {
|
|
$matches->orWhereExists(fn ($coverage) => $coverage->selectRaw('1')->from('economic_clusters as coverage_cluster')
|
|
->whereColumn('coverage_cluster.id', 'investment_dossiers.cluster_id')->whereNull('coverage_cluster.deleted_at')
|
|
->whereRaw(app(InvestmentRelations::class)->coverageSql('coverage_cluster.metadata', $territoryIds), $territoryIds));
|
|
$matches->orWhereExists(fn ($member) => $member->selectRaw('1')->fromSub(app(InvestmentRelations::class)->memberships()->select(['cp.cluster_id', 'cp.project_id']), 'documented_member')
|
|
->join('projects as member_project', 'member_project.id', '=', 'documented_member.project_id')
|
|
->whereColumn('documented_member.cluster_id', 'investment_dossiers.cluster_id')
|
|
->where(fn ($area) => $area->whereIn('member_project.primary_territory_id', $territoryIds)
|
|
->orWhereExists(fn ($locations) => $locations->selectRaw('1')->from('project_locations as member_location')
|
|
->whereColumn('member_location.project_id', 'member_project.id')->whereNull('member_location.deleted_at')
|
|
->where('member_location.data_classification', 'public')->whereIn('member_location.geo_location_id', $territoryIds))));
|
|
$matches->orWhereIn('project_id', DB::table('projects')->whereNull('deleted_at')->whereIn('primary_territory_id', $territoryIds)->select('id'))
|
|
->orWhereExists(fn ($locations) => $locations->selectRaw('1')->from('project_locations as pl')
|
|
->whereColumn('pl.project_id', 'investment_dossiers.project_id')->whereNull('pl.deleted_at')
|
|
->where('pl.data_classification', 'public')->whereIn('pl.geo_location_id', $territoryIds))
|
|
->orWhereIn('cluster_id', DB::table('economic_clusters')->whereNull('deleted_at')->whereIn('primary_territory_id', $territoryIds)->select('id'))
|
|
->orWhereIn('corridor_id', DB::table('development_corridors')->whereNull('deleted_at')->whereIn('primary_territory_id', $territoryIds)->select('id'))
|
|
->orWhereExists(fn ($locations) => $locations->selectRaw('1')->from('corridor_locations as cl')
|
|
->whereColumn('cl.corridor_id', 'investment_dossiers.corridor_id')->whereNull('cl.deleted_at')
|
|
->where('cl.data_classification', 'public')->whereIn('cl.geo_location_id', $territoryIds));
|
|
|
|
foreach ([['cluster_projects', 'cluster_id'], ['corridor_projects', 'corridor_id']] as [$linkTable, $ownerColumn]) {
|
|
$matches->orWhereExists(fn ($links) => $links->selectRaw('1')->from($linkTable.' as territorial_link')
|
|
->join('projects as linked_project', 'linked_project.id', '=', 'territorial_link.project_id')
|
|
->whereColumn('territorial_link.'.$ownerColumn, 'investment_dossiers.'.$ownerColumn)
|
|
->whereNull('territorial_link.deleted_at')->where('territorial_link.data_classification', 'public')
|
|
->where('territorial_link.validation_status', 'validated')
|
|
->whereDate('territorial_link.valid_from', '<=', today()->toDateString())
|
|
->where(fn ($validity) => $validity->whereNull('territorial_link.valid_until')->orWhereDate('territorial_link.valid_until', '>=', today()->toDateString()))
|
|
->whereNull('linked_project.deleted_at')->where('linked_project.status', '!=', 'draft')
|
|
->where(function ($projectArea) use ($territoryIds): void {
|
|
$projectArea->whereIn('linked_project.primary_territory_id', $territoryIds)
|
|
->orWhereExists(fn ($locations) => $locations->selectRaw('1')->from('project_locations as linked_location')
|
|
->whereColumn('linked_location.project_id', 'linked_project.id')->whereNull('linked_location.deleted_at')
|
|
->where('linked_location.data_classification', 'public')->whereIn('linked_location.geo_location_id', $territoryIds));
|
|
}));
|
|
}
|
|
});
|
|
}
|
|
|
|
public function publicReferences(): Builder
|
|
{
|
|
return DB::table('geo_locations')->whereNull('deleted_at')->where('data_classification', 'public')
|
|
->whereRaw("NOT EXISTS (WITH RECURSIVE ancestors AS (
|
|
SELECT id,parent_id,deleted_at,data_classification,ARRAY[id] AS visited,false AS cycle FROM geo_locations a WHERE a.id=geo_locations.id
|
|
UNION ALL SELECT a.id,a.parent_id,a.deleted_at,a.data_classification,p.visited||a.id,a.id=ANY(p.visited)
|
|
FROM geo_locations a JOIN ancestors p ON a.id=p.parent_id WHERE NOT p.cycle
|
|
) SELECT 1 FROM ancestors WHERE deleted_at IS NOT NULL OR data_classification<>'public' OR cycle)")
|
|
->where(function ($query) {
|
|
$query->where(function ($workbook) {
|
|
$workbook->where('source_system', 'cartografia-workbook')
|
|
->where('external_id', 'like', '964a187ad32ea553b9e112cd159caa90b900050c42b7329594a75bcd798a96ff:%');
|
|
})->orWhereIn('id', app(TerritorialRegistry::class)->published()->select('g.id'));
|
|
});
|
|
}
|
|
|
|
public function references(bool $management = false): Builder
|
|
{
|
|
return $management
|
|
? DB::table('geo_locations')->whereNull('deleted_at')->whereIn('data_classification', ['public', 'internal'])
|
|
: $this->publicReferences();
|
|
}
|
|
|
|
/** @param array<string,mixed> $payload
|
|
* @return list<list<string>>
|
|
*/
|
|
public function displayPaths(array $payload): array
|
|
{
|
|
$locations = [];
|
|
if (($payload['geography_scope'] ?? null) === 'multiple') {
|
|
$decoded = json_decode((string) ($payload['locations_json'] ?? ''), true);
|
|
$locations = is_array($decoded) ? $decoded : [];
|
|
} elseif (($payload['geography_scope'] ?? null) === 'specific') {
|
|
$locations = [array_intersect_key($payload, array_flip(array_keys(self::FIELDS)))];
|
|
} else {
|
|
return [];
|
|
}
|
|
|
|
$leafIds = [];
|
|
foreach ($locations as $location) {
|
|
$leafIds[] = is_array($location)
|
|
? ($location['locality_id'] ?? $location['admin_post_id'] ?? $location['district_id'] ?? $location['province_id'] ?? null)
|
|
: null;
|
|
}
|
|
$selected = array_filter($leafIds, fn ($id) => is_string($id) && $id !== '');
|
|
if (! $selected) {
|
|
return array_fill(0, count($locations), []);
|
|
}
|
|
|
|
$values = [];
|
|
$bindings = [];
|
|
foreach ($selected as $index => $id) {
|
|
$values[] = '(?, ?::uuid)';
|
|
$bindings[] = $index;
|
|
$bindings[] = $id;
|
|
}
|
|
$rows = DB::select('WITH RECURSIVE selected(location_index, id) AS (VALUES '.implode(',', $values).'),
|
|
tree AS (
|
|
SELECT s.location_index, g.id, g.name, g.location_type, g.parent_id, ARRAY[g.id] AS visited, 0 AS depth
|
|
FROM selected s JOIN geo_locations g ON g.id=s.id
|
|
UNION ALL
|
|
SELECT tree.location_index, parent.id, parent.name, parent.location_type, parent.parent_id,
|
|
tree.visited||parent.id, tree.depth+1
|
|
FROM tree JOIN geo_locations parent ON parent.id=tree.parent_id
|
|
WHERE tree.depth<4 AND NOT parent.id=ANY(tree.visited)
|
|
)
|
|
SELECT location_index, id, name, location_type FROM tree ORDER BY location_index, depth DESC', $bindings);
|
|
$nodeIds = array_values(array_unique(array_map(fn ($row) => (string) $row->id, $rows)));
|
|
$trustedIds = $this->publicReferences()->whereIn('id', $nodeIds)->pluck('id')->map(fn ($id) => (string) $id)->all();
|
|
$trusted = array_fill_keys($trustedIds, true);
|
|
$grouped = [];
|
|
foreach ($rows as $row) {
|
|
$grouped[(int) $row->location_index][] = $row;
|
|
}
|
|
|
|
$display = [];
|
|
foreach ($locations as $index => $_location) {
|
|
$path = $grouped[$index] ?? [];
|
|
if (! $path || array_filter($path, fn ($node) => ! isset($trusted[(string) $node->id]))) {
|
|
$display[] = [];
|
|
|
|
continue;
|
|
}
|
|
$display[] = array_values(array_map(fn ($node) => (string) $node->name,
|
|
array_filter($path, fn ($node) => $node->location_type !== 'country')));
|
|
}
|
|
|
|
return $display;
|
|
}
|
|
|
|
/** @param array<string,mixed> $input
|
|
* @return list<\stdClass>
|
|
*/
|
|
public function selection(array $input, bool $management = false): array
|
|
{
|
|
$path = [];
|
|
$parent = null;
|
|
$gap = false;
|
|
foreach (self::FIELDS as $field => $type) {
|
|
if (empty($input[$field])) {
|
|
$gap = true;
|
|
|
|
continue;
|
|
}
|
|
$node = $this->references($management)->where('id', $input[$field])->where('location_type', $type)->first(['id', 'name', 'parent_id', 'location_type']);
|
|
if ($gap || ! $node || ($parent && $node->parent_id !== $parent)) {
|
|
throw ValidationException::withMessages([$field => 'Seleccione a localização dentro da hierarquia indicada.']);
|
|
}
|
|
$path[] = $node;
|
|
$parent = $node->id;
|
|
}
|
|
|
|
return $path;
|
|
}
|
|
|
|
/** @return list<string> */
|
|
public function descendants(string $id, bool $includeNonPublic = false): array
|
|
{
|
|
$rows = DB::select('WITH RECURSIVE tree AS (
|
|
SELECT id, ARRAY[id] AS visited FROM geo_locations WHERE id=? AND deleted_at IS NULL
|
|
UNION ALL SELECT g.id, tree.visited||g.id FROM geo_locations g JOIN tree ON g.parent_id=tree.id
|
|
WHERE g.deleted_at IS NULL AND NOT g.id=ANY(tree.visited) AND cardinality(tree.visited)<5
|
|
) SELECT id FROM tree', [$id]);
|
|
|
|
$query = $includeNonPublic
|
|
? DB::table('geo_locations')->whereNull('deleted_at')
|
|
: $this->publicReferences();
|
|
|
|
$treeIds = array_map(fn ($row) => $row->id, $rows);
|
|
$ids = $query->whereIn('id', $treeIds)->pluck('id')->map(fn ($id) => (string) $id)->all();
|
|
if (! $includeNonPublic) {
|
|
// Documented sites need not be administrative registry entries themselves.
|
|
// Their parent must belong to the same public, trusted administrative hierarchy.
|
|
$sites = DB::table('geo_locations')->whereIn('id', $treeIds)->where('location_type', 'site')
|
|
->whereNull('deleted_at')->where('data_classification', 'public')
|
|
->whereIn('parent_id', $this->publicReferences()->select('id'))->pluck('id')->all();
|
|
$ids = array_merge($ids, $sites);
|
|
}
|
|
|
|
return array_values(array_unique($ids));
|
|
}
|
|
|
|
/** @return list<\stdClass> */
|
|
public function path(?string $id, bool $management = false): array
|
|
{
|
|
$path = [];
|
|
for ($i = 0; $id && $i < 5; $i++) {
|
|
$query = $management ? DB::table('geo_locations') : $this->publicReferences();
|
|
$node = $query->where('id', $id)->first(['id', 'name', 'parent_id', 'location_type', 'deleted_at']);
|
|
if (! $node) {
|
|
return [];
|
|
}
|
|
if ($node->location_type !== 'country') {
|
|
array_unshift($path, $node);
|
|
}
|
|
$id = $node->parent_id;
|
|
}
|
|
|
|
return $path;
|
|
}
|
|
}
|