Claude VM
a6c03a091e
Moved from gov-agreg/src/pages/achizitii/* to root (drop prefix). - 22 pages migrated, 127 files total - All internal links: /achizitii/X → /X (176 occurrences fixed) - AchizitiiLayout subnav rewritten: /X paths, top-right link to vreaudigital.ro hub - BaseLayout new (vreau.digital branding, OG tags, site URL) - astro.config.mjs: site https://vreau.digital, server output (was static) - docker-compose: port 5096 (vreaudigital is 5095), container vreau-digital - deploy.sh: paths /opt/vreau-digital, log /var/log/vreau-digital-deploy.log Backend shared with gov-agreg: - PostgreSQL satra (same schemas: seap, firms, anaf, anre, ...) - Photon, Martin tiles - Infisical /vreaudigital path (DATABASE_URL etc. shared) build: PASS (npx astro check 0 errors, npm run build 5s vite + 10s server)
72 lines
2.8 KiB
PL/PgSQL
72 lines
2.8 KiB
PL/PgSQL
-- Map WSP rows to UAT SIRUTA codes + extend the harta UAT stats view.
|
|
-- (Suppliers may have "RO " prefix; authorities are clean. Strip both forms.)
|
|
BEGIN;
|
|
|
|
-- Indexes to make the UPDATE fast
|
|
CREATE INDEX IF NOT EXISTS idx_ann_auth_cui_wsp ON seap.announcements(authority_cui)
|
|
WHERE source LIKE 'wsp_%' AND authority_siruta IS NULL AND authority_cui IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_ann_supp_cui_wsp ON seap.announcements(supplier_cui)
|
|
WHERE source LIKE 'wsp_%' AND supplier_siruta IS NULL AND supplier_cui IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_cui_loc_cui ON seap.cui_location(cui) WHERE siruta IS NOT NULL;
|
|
|
|
-- Authority — clean numeric CUI (direct match)
|
|
UPDATE seap.announcements a
|
|
SET authority_siruta = cl.siruta
|
|
FROM seap.cui_location cl
|
|
WHERE a.source LIKE 'wsp_%'
|
|
AND a.authority_siruta IS NULL
|
|
AND a.authority_cui IS NOT NULL
|
|
AND cl.siruta IS NOT NULL
|
|
AND cl.cui = a.authority_cui;
|
|
|
|
-- Suppliers — may have "RO " prefix, strip and retry the rest
|
|
UPDATE seap.announcements a
|
|
SET supplier_siruta = cl.siruta
|
|
FROM seap.cui_location cl
|
|
WHERE a.source LIKE 'wsp_%'
|
|
AND a.supplier_siruta IS NULL
|
|
AND a.supplier_cui IS NOT NULL
|
|
AND cl.siruta IS NOT NULL
|
|
AND cl.cui = trim(regexp_replace(a.supplier_cui, '^RO\s*', '', 'i'));
|
|
|
|
-- Extend uat_procurement_stats view to include WSP types
|
|
DROP MATERIALIZED VIEW IF EXISTS seap.uat_procurement_stats CASCADE;
|
|
|
|
CREATE MATERIALIZED VIEW seap.uat_procurement_stats AS
|
|
SELECT
|
|
u.siruta,
|
|
u.name AS uat_name,
|
|
u.county,
|
|
COALESCE(s.da_count, 0::bigint) AS da_count,
|
|
COALESCE(s.da_value, 0::numeric) AS da_total_value,
|
|
COALESCE(s.contract_count, 0::bigint) AS notice_count,
|
|
COALESCE(s.contract_value, 0::numeric) AS notice_total_value,
|
|
COALESCE(s.total_count, 0::bigint) AS total_contracts,
|
|
COALESCE(s.total_value, 0::numeric) AS total_value
|
|
FROM "GisUat" u
|
|
LEFT JOIN (
|
|
SELECT
|
|
authority_siruta AS siruta,
|
|
count(*) FILTER (WHERE type = 'da') AS da_count,
|
|
sum(awarded_value) FILTER (WHERE type = 'da') AS da_value,
|
|
count(*) FILTER (WHERE type IN (
|
|
'contract', 'atribuire_fara', 'ted_notice',
|
|
'ca_notice', 'rfq_notice'
|
|
)) AS contract_count,
|
|
sum(awarded_value) FILTER (WHERE type IN (
|
|
'contract', 'atribuire_fara', 'ted_notice',
|
|
'ca_notice', 'rfq_notice'
|
|
)) AS contract_value,
|
|
count(*) AS total_count,
|
|
sum(COALESCE(awarded_value, estimated_value, 0::numeric)) AS total_value
|
|
FROM seap.announcements
|
|
WHERE authority_siruta IS NOT NULL
|
|
GROUP BY authority_siruta
|
|
) s ON s.siruta = u.siruta;
|
|
|
|
CREATE UNIQUE INDEX uq_uat_proc_stats ON seap.uat_procurement_stats(siruta);
|
|
CREATE INDEX idx_uat_proc_stats_value ON seap.uat_procurement_stats(total_value DESC NULLS LAST);
|
|
CREATE INDEX idx_uat_proc_stats_county ON seap.uat_procurement_stats(county);
|
|
|
|
COMMIT;
|