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)
108 lines
3.8 KiB
Python
108 lines
3.8 KiB
Python
#!/usr/bin/env python3
|
|
"""Load EU CPV nomenclature (RO names) into seap.cpv_codes + backfill announcements."""
|
|
import json
|
|
import os
|
|
import sys
|
|
import urllib.request
|
|
from pathlib import Path
|
|
|
|
import psycopg2
|
|
from psycopg2.extras import execute_values
|
|
|
|
CPV_URL = 'https://raw.githubusercontent.com/samhallskod/cpv-eu/main/data/cpv.json'
|
|
|
|
|
|
def main():
|
|
db = os.environ.get('DATABASE_URL')
|
|
if not db:
|
|
print('DATABASE_URL not set', file=sys.stderr)
|
|
sys.exit(1)
|
|
|
|
cache = Path('/tmp/cpv.json')
|
|
if not cache.exists():
|
|
print(f'Downloading {CPV_URL}...')
|
|
urllib.request.urlretrieve(CPV_URL, cache)
|
|
data = json.loads(cache.read_text())
|
|
print(f'Loaded {len(data)} CPV codes from JSON')
|
|
|
|
rows = []
|
|
for entry in data:
|
|
code = entry['code'] # 8-digit (no dash)
|
|
labels = entry.get('labels', {})
|
|
name_ro = labels.get('ro') or labels.get('en') or code
|
|
name_en = labels.get('en')
|
|
level = entry.get('level', 0)
|
|
emoji = entry.get('emoji')
|
|
|
|
# Division = first 2 digits + 6 zeros
|
|
division = code[:2] + '000000'
|
|
|
|
# Parent = level - 1 — easiest: trim trailing zero pairs based on level
|
|
# Level mapping: 1=XX000000, 2=XXX00000, 3=XXXX0000, 4=XXXXX000, 5=XXXXXX00, 6=XXXXXXX0, 7=XXXXXXXX (full)
|
|
# For our purposes, parent is one level up — replace last non-zero digit with 0
|
|
parent = None
|
|
if level > 1:
|
|
# find rightmost non-zero, zero it out
|
|
digits = list(code)
|
|
# Find position of last meaningful digit
|
|
for i in range(len(digits) - 1, -1, -1):
|
|
if digits[i] != '0':
|
|
digits[i] = '0'
|
|
break
|
|
parent_code = ''.join(digits)
|
|
if parent_code != code:
|
|
parent = parent_code
|
|
|
|
# Build full code with check digit (synthesize a placeholder dash + 0)
|
|
# The real check digit is per-code — we don't compute it; use the 8-digit form everywhere.
|
|
rows.append((code, code, name_ro, name_en, level, division, parent, emoji))
|
|
|
|
print(f'Inserting {len(rows)} rows into seap.cpv_codes...')
|
|
conn = psycopg2.connect(db)
|
|
cur = conn.cursor()
|
|
cur.execute('TRUNCATE seap.cpv_codes')
|
|
execute_values(cur, """
|
|
INSERT INTO seap.cpv_codes (code, code_full, name_ro, name_en, level,
|
|
division_code, parent_code, emoji)
|
|
VALUES %s
|
|
""", rows, page_size=500)
|
|
conn.commit()
|
|
cur.execute('SELECT count(*), count(*) FILTER (WHERE level=1) FROM seap.cpv_codes')
|
|
total, divisions = cur.fetchone()
|
|
print(f' → {total} codes loaded, {divisions} top-level divisions')
|
|
|
|
# Backfill cpv_division + cpv_name_ro on announcements
|
|
print('Backfilling cpv_division + cpv_name_ro on announcements...')
|
|
cur.execute("""
|
|
UPDATE seap.announcements
|
|
SET cpv_division = seap.cpv_division(cpv_code),
|
|
cpv_name_ro = seap.cpv_name(cpv_code)
|
|
WHERE cpv_code IS NOT NULL
|
|
AND (cpv_division IS NULL OR cpv_name_ro IS NULL)
|
|
""")
|
|
affected = cur.rowcount
|
|
conn.commit()
|
|
print(f' → {affected:,} announcement rows enriched')
|
|
|
|
# Show top divisions
|
|
cur.execute("""
|
|
SELECT a.cpv_division, c.name_ro, c.emoji,
|
|
count(*) as n,
|
|
sum(awarded_value)::numeric(15,0) as total_value
|
|
FROM seap.announcements a
|
|
LEFT JOIN seap.cpv_codes c ON c.code = a.cpv_division
|
|
WHERE a.cpv_division IS NOT NULL AND a.source LIKE 'wsp_%'
|
|
GROUP BY 1, 2, 3
|
|
ORDER BY 4 DESC
|
|
LIMIT 15
|
|
""")
|
|
print('\nTop 15 CPV divisions in WSP data:')
|
|
for r in cur.fetchall():
|
|
print(f' {r[2] or " "} {r[0]:<10} {(r[1] or "?")[:45]:<45} {r[3]:>5} contracte {r[4] or 0:>13,} RON')
|
|
|
|
conn.close()
|
|
|
|
|
|
if __name__ == '__main__':
|
|
main()
|