damir/pgz-sport
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
pgz-sport/fin_import.py_prije_env_deepseek
T

268 lines
9.1 KiB
Python
Raw Permalink Blame History

#!/usr/bin/env python3
"""
PGZ Financijski import — Bilanca + PR-RAS
Stream-filter: samo PGZ sport OIBs, ključne kolone
"""
import psycopg2, psycopg2.extras, os, time
from datetime import datetime
DSN = f"host=10.10.0.2 port=6432 dbname=rinet_v3 user=rinet password={os.environ['DB_PASSWORD']}"
DIR = "/opt/pgz-sport/_downloads/CSV"
LOG = "/opt/pgz-sport/logs/fin_import.log"
TG_TOKEN = "8535797835:AAFItT-92jzZ9NWFafLxn0dLa1_n2s-JE5Y"
TG_CHAT = "7969491558"
os.makedirs("/opt/pgz-sport/logs", exist_ok=True)
def log(msg):
ts = datetime.now().strftime("%H:%M:%S")
line = f"[{ts}] {msg}"
print(line, flush=True)
with open(LOG, "a") as f: f.write(line+"\n")
def tg(msg):
try:
import urllib.request, urllib.parse
data = urllib.parse.urlencode({"chat_id": TG_CHAT, "text": f"💰 RNO Fin: {msg}"}).encode()
urllib.request.urlopen(f"https://api.telegram.org/bot{TG_TOKEN}/sendMessage", data, timeout=10)
except: pass
def parse_decimal(s):
"""Croatian decimal: 1.234.567,89 → 1234567.89"""
if not s or not s.strip(): return None
try:
return float(s.replace(".","").replace(",",".").strip())
except: return None
conn = psycopg2.connect(DSN)
conn.autocommit = True
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# Get PGZ sport OIBs
cur.execute("SELECT oib, rno_broj, naziv FROM pgz_sport.rno_organizacije WHERE oib IS NOT NULL AND oib != ''")
pgz_oibs = {row["oib"]: {"rno_broj": row["rno_broj"], "naziv": row["naziv"]} for row in cur.fetchall()}
log(f"PGZ sport OIBs: {len(pgz_oibs)}")
# ---- BILANCA ----
log("=== BILANCA ===")
cur.execute("""
CREATE TABLE IF NOT EXISTS pgz_sport.rno_bilanca (
id BIGSERIAL PRIMARY KEY,
oib TEXT NOT NULL,
rno_broj TEXT,
naziv TEXT,
godina INTEGER,
imovina_ukupno NUMERIC(15,2),
imovina_dugotrajna NUMERIC(15,2),
imovina_kratkotrajna NUMERIC(15,2),
obveze_ukupno NUMERIC(15,2),
kapital NUMERIC(15,2),
raw_json JSONB,
created_at TIMESTAMPTZ DEFAULT now(),
UNIQUE(oib, godina)
)
""")
# Key columns to extract (names from header)
BIL_KEYS = {
"BILImovinaA": "imovina_dugotrajna",
"BILImovinaB": "imovina_kratkotrajna",
"BIL0a": "imovina_ukupno_a",
"BIL0b": "imovina_ukupno_b",
"BILObveze0a": "obveze_a",
"BILObveze0b": "obveze_b",
"BILFondA": "kapital_a",
"BILFondB": "kapital_b",
}
bil_rows = []
total_read = 0
pgz_found = 0
with open(f"{DIR}/Bilanca.txt", encoding="utf-16-le", errors="replace") as f:
raw_header = f.readline().strip("\ufeff").strip()
headers = raw_header.split("$")
h = {v: i for i, v in enumerate(headers)}
oib_idx = h.get("OIB", 1)
naziv_idx = h.get("Naziv neprofitne organizacije", 0)
maticni_idx = h.get("Matični broj", 2)
god_idx = h.get("Godina", 3)
for line in f:
total_read += 1
if total_read % 100000 == 0:
log(f" Bilanca: read {total_read:,}, found {pgz_found} PGZ rows")
cols = line.strip().split("$")
if len(cols) < 5: continue
oib = cols[oib_idx].strip() if oib_idx < len(cols) else ""
if oib not in pgz_oibs:
continue
pgz_found += 1
info = pgz_oibs[oib]
try:
godina = int(cols[god_idx].strip()) if god_idx < len(cols) else 0
except: continue
# Extract key financial values
raw = {}
imovina_a = None
imovina_b = None
obveze = None
kapital = None
for col_name, field in BIL_KEYS.items():
idx = h.get(col_name)
if idx and idx < len(cols):
val = parse_decimal(cols[idx])
raw[col_name] = val
if col_name == "BILImovinaA": imovina_a = val
elif col_name == "BILImovinaB": imovina_b = val
elif col_name in ("BILObveze0a","BILObveze0b"):
obveze = val if "a" in col_name else obveze
elif col_name in ("BILFondA","BILFondB"):
kapital = val if "A" in col_name else kapital
imovina_ukupno = None
if imovina_a is not None and imovina_b is not None:
imovina_ukupno = (imovina_a or 0) + (imovina_b or 0)
bil_rows.append((
oib, info["rno_broj"], info["naziv"], godina,
imovina_ukupno, imovina_a, imovina_b, obveze, kapital,
psycopg2.extras.Json(raw)
))
if len(bil_rows) >= 500:
psycopg2.extras.execute_batch(cur, """
INSERT INTO pgz_sport.rno_bilanca
(oib, rno_broj, naziv, godina, imovina_ukupno, imovina_dugotrajna,
imovina_kratkotrajna, obveze_ukupno, kapital, raw_json)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
ON CONFLICT (oib, godina) DO UPDATE SET imovina_ukupno=EXCLUDED.imovina_ukupno
""", bil_rows, page_size=200)
bil_rows = []
if bil_rows:
psycopg2.extras.execute_batch(cur, """
INSERT INTO pgz_sport.rno_bilanca
(oib, rno_broj, naziv, godina, imovina_ukupno, imovina_dugotrajna,
imovina_kratkotrajna, obveze_ukupno, kapital, raw_json)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
ON CONFLICT (oib, godina) DO UPDATE SET imovina_ukupno=EXCLUDED.imovina_ukupno
""", bil_rows, page_size=200)
cur.execute("SELECT COUNT(*) as n FROM pgz_sport.rno_bilanca")
n_bil = cur.fetchone()["n"]
log(f"✅ Bilanca done: {n_bil} redaka za {pgz_found} PGZ sport org ({total_read:,} rows scanned)")
# ---- PR-RAS ----
log("=== PR-RAS ===")
cur.execute("""
CREATE TABLE IF NOT EXISTS pgz_sport.rno_prras (
id BIGSERIAL PRIMARY KEY,
oib TEXT NOT NULL,
rno_broj TEXT,
naziv TEXT,
godina INTEGER,
prihodi_ukupno NUMERIC(15,2),
prihodi_javni NUMERIC(15,2),
prihodi_clanski NUMERIC(15,2),
rashodi_ukupno NUMERIC(15,2),
rezultat NUMERIC(15,2),
raw_json JSONB,
created_at TIMESTAMPTZ DEFAULT now(),
UNIQUE(oib, godina)
)
""")
PR_KEYS = {
"PR3a": "prihodi_a", "PR31a": "prihodi_javni_a",
"PR3b": "prihodi_b", "PR31b": "prihodi_javni_b",
"PR5a": "rashodi_a", "PR5b": "rashodi_b",
"PR7a": "rezultat_a", "PR7b": "rezultat_b",
"PR32a": "prihodi_clanski_a",
}
pr_rows = []
total_read2 = 0
pgz_found2 = 0
with open(f"{DIR}/PR-RAS.txt", encoding="utf-16-le", errors="replace") as f:
raw_header = f.readline().strip("\ufeff").strip()
headers = raw_header.split("$")
h = {v: i for i, v in enumerate(headers)}
oib_idx = h.get("OIB", 1)
god_idx = h.get("Godina", 3)
for line in f:
total_read2 += 1
if total_read2 % 100000 == 0:
log(f" PR-RAS: read {total_read2:,}, found {pgz_found2} PGZ rows")
cols = line.strip().split("$")
if len(cols) < 5: continue
oib = cols[oib_idx].strip() if oib_idx < len(cols) else ""
if oib not in pgz_oibs: continue
pgz_found2 += 1
info = pgz_oibs[oib]
try:
godina = int(cols[god_idx].strip()) if god_idx < len(cols) else 0
except: continue
raw = {}
prihodi = rashodi = rezultat = prihodi_javni = prihodi_clan = None
for col_name, field in PR_KEYS.items():
idx = h.get(col_name)
if idx and idx < len(cols):
val = parse_decimal(cols[idx])
raw[col_name] = val
if col_name == "PR3a": prihodi = val
elif col_name == "PR5a": rashodi = val
elif col_name == "PR7a": rezultat = val
elif col_name == "PR31a": prihodi_javni = val
elif col_name == "PR32a": prihodi_clan = val
pr_rows.append((
oib, info["rno_broj"], info["naziv"], godina,
prihodi, prihodi_javni, prihodi_clan, rashodi, rezultat,
psycopg2.extras.Json(raw)
))
if len(pr_rows) >= 500:
psycopg2.extras.execute_batch(cur, """
INSERT INTO pgz_sport.rno_prras
(oib, rno_broj, naziv, godina, prihodi_ukupno, prihodi_javni,
prihodi_clanski, rashodi_ukupno, rezultat, raw_json)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
ON CONFLICT (oib, godina) DO UPDATE SET prihodi_ukupno=EXCLUDED.prihodi_ukupno
""", pr_rows, page_size=200)
pr_rows = []
if pr_rows:
psycopg2.extras.execute_batch(cur, """
INSERT INTO pgz_sport.rno_prras
(oib, rno_broj, naziv, godina, prihodi_ukupno, prihodi_javni,
prihodi_clanski, rashodi_ukupno, rezultat, raw_json)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
ON CONFLICT (oib, godina) DO UPDATE SET prihodi_ukupno=EXCLUDED.prihodi_ukupno
""", pr_rows, page_size=200)
cur.execute("SELECT COUNT(*) as n FROM pgz_sport.rno_prras")
n_pr = cur.fetchone()["n"]
log(f"✅ PR-RAS done: {n_pr} redaka")
msg = f"✅ Financijski import: Bilanca {n_bil} + PR-RAS {n_pr} redaka za PGZ sport org"
log(msg)
tg(msg)
conn.close()
Reference in New Issue View Git Blame Copy Permalink