{ "nbformat": 4, "nbformat_minor": 5, "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "name": "python" }, "colab": { "provenance": [] } }, "cells": [ { "cell_type": "markdown", "metadata": { "id": "Tnztin6akF04" }, "source": "# QAPU Canlı Analiz + Müşteri Raporları + Sohbet Botu\n### Tek notebook: canlı veritabanı bağlantısı → analiz → müşteri bazlı Word raporları → sohbet arayüzü\n\nBu not defteri **doğrudan canlı PostgreSQL veritabanına** bağlanır (dump dosyası gerekmez) ve\naynı bağlantıyla iki şey sunar:\n\n**A. Rapor üretimi** — reaktif analiz (EPDK %20/%15), kestirimci bakım (THD, dengesizlik,\ntrend, anomali), sağlık skoru; müşteri bazlı profesyonel Word raporları\n(KOSKİ / Koyuncu A.Ş. / KOP — her biri kendi klasöründe, teknik + yönetici + vatandaş sürümleri).\n\n**B. Sohbet botu** — Gradio arayüzünde Türkçe soru → SQL → Türkçe cevap.\n\n**Güvenlik**\n* Kimlik bilgileri koda yazılmaz; çalışma anında gizli sorulur (`getpass`).\n* Tüm bağlantılar **salt-okunur** açılır; sohbet botu ayrıca SQL ayrıştırıcı korumasından geçer\n (yalnızca tek SELECT, otomatik LIMIT 500, 20 sn zaman aşımı).\n* Önerilen: veritabanında salt-okunur kullanıcı:\n```sql\nCREATE ROLE qapu_bot LOGIN PASSWORD '';\nGRANT CONNECT ON DATABASE qapu TO qapu_bot;\nGRANT USAGE ON SCHEMA public TO qapu_bot;\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO qapu_bot;\nALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO qapu_bot;\n```\n> Not: Veritabanı sunucusunun Colab'dan erişime açık olması gerekir (genel IP veya tünel).\n> Sorgu metinleri ve şema LLM servisine gider; veri gizliliği kritikse OpenAI-uyumlu\n> şirket içi bir model seçin (5B hücresi).\n\nHücreleri sırayla çalıştırın: 1 → 2 (modüller) → 3 (bağlantı) → 4 (analiz) → 5 (raporlar)\n→ 6-7 (sohbet).\n", "id": "Tnztin6akF04" }, { "cell_type": "code", "metadata": { "id": "DMR5KItwkF08", "executionInfo": { "status": "ok", "timestamp": 1793477131396, "user_tz": -180, "elapsed": 12259, "user": { "displayName": "Joseph Volkan ÖZCAN", "userId": "11389530967736397480" } }, "outputId": "8dc93e78-8175-41f0-defe-3b759673f83f", "colab": { "base_uri": "https://localhost:8080/" } }, "execution_count": 1, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "\u001b[31mERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.\n", "google-cloud-spanner 3.71.0 requires protobuf<8.0.0,>=6.33.5, but you have protobuf 5.29.6 which is incompatible.\n", "google-cloud-language 2.22.0 requires protobuf<8.0.0,>=6.33.5, but you have protobuf 5.29.6 which is incompatible.\n", "tensorflow 2.21.0 requires protobuf<8.0.0,>=6.31.1, but you have protobuf 5.29.6 which is incompatible.\n", "google-cloud-firestore 2.34.1 requires protobuf<8.0.0,>=6.33.5, but you have protobuf 5.29.6 which is incompatible.\n", "google-cloud-monitoring 2.32.0 requires protobuf<8.0.0,>=6.33.5, but you have protobuf 5.29.6 which is incompatible.\n", "google-cloud-bigquery-storage 2.43.0 requires protobuf<8.0.0,>=6.33.5, but you have protobuf 5.29.6 which is incompatible.\n", "grpc-google-iam-v1 0.14.6 requires protobuf<8.0.0,>=6.33.5, but you have protobuf 5.29.6 which is incompatible.\n", "tensorboard 2.21.0 requires protobuf<8.0.0,>=6.31.1, but you have protobuf 5.29.6 which is incompatible.\n", "google-cloud-functions 1.25.0 requires protobuf<8.0.0,>=6.33.5, but you have protobuf 5.29.6 which is incompatible.\n", "google-cloud-bigquery-connection 1.23.0 requires protobuf<8.0.0,>=6.33.5, but you have protobuf 5.29.6 which is incompatible.\n", "google-cloud-storage-control 1.17.0 requires protobuf<8.0.0,>=6.33.5, but you have protobuf 5.29.6 which is incompatible.\n", "google-cloud-resource-manager 1.19.0 requires protobuf<8.0.0,>=6.33.5, but you have protobuf 5.29.6 which is incompatible.\n", "google-cloud-translate 3.28.0 requires protobuf<8.0.0,>=6.33.5, but you have protobuf 5.29.6 which is incompatible.\n", "google-cloud-dataproc 5.32.0 requires protobuf<8.0.0,>=6.33.5, but you have protobuf 5.29.6 which is incompatible.\n", "google-cloud-datastore 2.27.0 requires protobuf<8.0.0,>=6.33.5, but you have protobuf 5.29.6 which is incompatible.\u001b[0m\u001b[31m\n", "\u001b[0mHazır.\n" ] } ], "source": "# 1) Kurulum\n!pip -q install psycopg2-binary sqlglot gradio python-docx cerebras_cloud_sdk google-generativeai openai\nprint(\"Hazır.\")", "id": "DMR5KItwkF08" }, { "cell_type": "code", "metadata": { "id": "5U7d3cbzkF09" }, "execution_count": null, "outputs": [], "source": "%%writefile qapu_pm.py\n# -*- coding: utf-8 -*-\n\"\"\"\nQAPU Elektrik Analizörü — Reaktif Analiz + Kestirimci Bakım + Offline Narrator\n==============================================================================\nEnerji analizörü (MAX78630) verilerinden:\n 1. Reaktif enerji uygunluk analizi (EPDK endüktif %20 / kapasitif %15 sınırları)\n 2. Kestirimci bakım: güç faktörü, harmonik (THD), faz dengesizliği, trend,\n anomali tespiti (IsolationForest), cihaz sağlık skoru (0-100)\n 3. Offline narrator: 4 hedef kitle için şablon tabanlı Türkçe rapor\n (teknik uzman, özel sektör yöneticisi, kamu yöneticisi, vatandaş)\nİnternet/LLM gerektirmez; tamamen deterministik şablonlarla çalışır.\n\"\"\"\nimport os, math, json\nimport numpy as np\nimport pandas as pd\n\n# ----------------------------------------------------------------------------\n# KONFİGÜRASYON — eşikler ve standart referansları\n# ----------------------------------------------------------------------------\nCONFIG = {\n \"ind_limit\": 0.20, # EPDK: endüktif reaktif / aktif enerji aylık sınırı\n \"cap_limit\": 0.15, # EPDK: kapasitif reaktif / aktif enerji aylık sınırı\n \"pf_target\": 0.95, # hedef güç faktörü (yük altında)\n \"thd_v_limit\": 0.08, # IEEE 519 esinli gerilim THD sınırı (%8)\n \"thd_i_limit\": 0.15, # akım THD uyarı eşiği (%15)\n \"v_imb_limit\": 0.02, # gerilim dengesizliği sınırı (%2, IEC/NEMA)\n \"i_imb_limit\": 0.10, # akım dengesizliği uyarı eşiği (%10)\n \"pcb_t_limit\": 60.0, # elektronik sıcaklık uyarı eşiği (°C)\n \"load_frac\": 0.05, # \"yük altında\" kabulü: S > p95(S) * 0.05\n \"min_days_trend\": 7, # trend için asgari gün\n \"min_days_iforest\": 10, # anomali modeli için asgari gün\n \"anomaly_contamination\": 0.08,\n \"vrms_plausible\": (120.0, 300.0), # kalibrasyon kontrolü (faz-nötr V)\n}\n\nRISK_BANDS = [(85, \"İYİ\"), (70, \"İZLENMELİ\"), (50, \"RİSKLİ\"), (-1, \"KRİTİK\")]\n\n\ndef _risk(score):\n for th, name in RISK_BANDS:\n if score >= th:\n return name\n return \"KRİTİK\"\n\n\n# ----------------------------------------------------------------------------\n# 1. VERİ YÜKLEME / HAZIRLIK\n# ----------------------------------------------------------------------------\ndef load_csvs(data_dir):\n \"\"\"extract_tables ile üretilen CSV'leri okur; tablolar sözlüğü döner.\"\"\"\n tabs = {}\n for name in [\"measurements_energy\", \"measurements_power\", \"measurements_voltage\",\n \"measurements_current\", \"measurements_device\",\n \"measurements_environment\", \"devices\"]:\n p = os.path.join(data_dir, name + \".csv\")\n if os.path.exists(p):\n df = pd.read_csv(p, low_memory=False)\n if \"device_time\" in df.columns:\n df[\"device_time\"] = pd.to_datetime(df[\"device_time\"], utc=True, errors=\"coerce\")\n df = df.dropna(subset=[\"device_time\"])\n if \"is_valid\" in df.columns:\n df = df[df[\"is_valid\"].astype(str).str.lower().isin([\"t\", \"true\", \"1\"])]\n tabs[name] = df\n return tabs\n\n\ndef _thd(df, prefix, phases=(\"r\", \"s\", \"t\"), harms=(3, 5, 7, 9)):\n \"\"\"sqrt(Σ harmonik²)/temel — faz başına; fazların ortalaması.\"\"\"\n vals = []\n for ph in phases:\n fund = df.get(f\"{prefix}fund_{ph}\")\n if fund is None:\n continue\n h2 = sum(df.get(f\"{prefix}harm_{ph}_{h}\", pd.Series(np.nan, index=df.index)) ** 2\n for h in harms)\n thd = np.sqrt(h2) / fund.where(fund > 1e-6)\n vals.append(thd)\n if not vals:\n return pd.Series(np.nan, index=df.index)\n return pd.concat(vals, axis=1).mean(axis=1)\n\n\ndef _imbalance(df, cols):\n \"\"\"Üç faz dengesizliği: max |x_i - ort| / ort (ort anlamlıysa).\"\"\"\n sub = df[list(cols)]\n mean = sub.mean(axis=1)\n dev = sub.sub(mean, axis=0).abs().max(axis=1)\n return (dev / mean.where(mean > 1e-6)).rename(\"imb\")\n\n\ndef build_daily(tabs, cfg=CONFIG):\n \"\"\"Cihaz × gün bazında özellik matrisi üretir.\"\"\"\n frames = []\n\n e = tabs.get(\"measurements_energy\")\n if e is not None and len(e):\n e = e.copy()\n for c in [\"ae_r\", \"ae_s\", \"ae_t\", \"re_l_r\", \"re_l_s\", \"re_l_t\",\n \"re_g_r\", \"re_g_s\", \"re_g_t\"]:\n if c in e:\n e[c] = pd.to_numeric(e[c], errors=\"coerce\").clip(lower=0) # negatifler geçersiz\n e[\"ae\"] = e[[\"ae_r\", \"ae_s\", \"ae_t\"]].sum(axis=1, min_count=1)\n e[\"re_l\"] = e[[\"re_l_r\", \"re_l_s\", \"re_l_t\"]].sum(axis=1, min_count=1)\n e[\"re_g\"] = e[[\"re_g_r\", \"re_g_s\", \"re_g_t\"]].sum(axis=1, min_count=1)\n g = (e.set_index(\"device_time\").groupby(\"device_id\")\n .resample(\"1D\")[[\"ae\", \"re_l\", \"re_g\"]].sum(min_count=1))\n frames.append(g)\n\n p = tabs.get(\"measurements_power\")\n if p is not None and len(p):\n p = p.copy()\n for c in p.columns:\n if c not in (\"device_id\", \"device_time\", \"is_valid\"):\n p[c] = pd.to_numeric(p[c], errors=\"coerce\")\n p[\"s_tot\"] = p[[\"s_r\", \"s_s\", \"s_t\"]].abs().sum(axis=1, min_count=1)\n p[\"pf_abs\"] = p[[\"pf_r\", \"pf_s\", \"pf_t\"]].abs().mean(axis=1)\n # yük eşiği: cihaz bazında p95'in %5'i\n thr = p.groupby(\"device_id\")[\"s_tot\"].transform(lambda s: s.quantile(0.95) * cfg[\"load_frac\"])\n p[\"loaded\"] = p[\"s_tot\"] > thr\n p[\"pf_loaded\"] = p[\"pf_abs\"].where(p[\"loaded\"])\n p[\"fq_dev\"] = (p[\"fq\"] - 50.0).abs().where(p[\"fq\"].between(40, 70))\n g = (p.set_index(\"device_time\").groupby(\"device_id\")\n .resample(\"1D\").agg(pf_loaded=(\"pf_loaded\", \"mean\"),\n load_hours=(\"loaded\", \"sum\"),\n s_max=(\"s_tot\", \"max\"),\n fq_dev=(\"fq_dev\", \"mean\")))\n g[\"load_hours\"] *= 5 / 60.0 # ~5 dk örnekleme → saat\n frames.append(g)\n\n v = tabs.get(\"measurements_voltage\")\n if v is not None and len(v):\n v = v.copy()\n for c in v.columns:\n if c not in (\"device_id\", \"device_time\", \"is_valid\"):\n v[c] = pd.to_numeric(v[c], errors=\"coerce\")\n v[\"thd_v\"] = _thd(v, \"v\")\n v[\"v_imb\"] = _imbalance(v, [\"vrms_r\", \"vrms_s\", \"vrms_t\"])\n v[\"vrms_avg\"] = v[[\"vrms_r\", \"vrms_s\", \"vrms_t\"]].mean(axis=1)\n g = (v.set_index(\"device_time\").groupby(\"device_id\")\n .resample(\"1D\").agg(thd_v=(\"thd_v\", \"median\"),\n v_imb=(\"v_imb\", \"median\"),\n vrms_med=(\"vrms_avg\", \"median\")))\n frames.append(g)\n\n c = tabs.get(\"measurements_current\")\n if c is not None and len(c):\n c = c.copy()\n for col in c.columns:\n if col not in (\"device_id\", \"device_time\", \"is_valid\"):\n c[col] = pd.to_numeric(c[col], errors=\"coerce\")\n c[\"thd_i\"] = _thd(c, \"i\")\n c[\"i_imb\"] = _imbalance(c, [\"irms_r\", \"irms_s\", \"irms_t\"])\n c[\"irms_avg\"] = c[[\"irms_r\", \"irms_s\", \"irms_t\"]].mean(axis=1)\n # yüksüzken dengesizlik anlamsız → medyan akımın %10'u altını at\n thr = c.groupby(\"device_id\")[\"irms_avg\"].transform(lambda s: max(s.quantile(0.9) * 0.1, 0.2))\n c.loc[c[\"irms_avg\"] < thr, [\"i_imb\", \"thd_i\"]] = np.nan\n g = (c.set_index(\"device_time\").groupby(\"device_id\")\n .resample(\"1D\").agg(thd_i=(\"thd_i\", \"median\"),\n i_imb=(\"i_imb\", \"median\"),\n irms_max=(\"irms_avg\", \"max\")))\n frames.append(g)\n\n env = tabs.get(\"measurements_environment\")\n if env is not None and len(env):\n env = env.copy()\n env[\"pcb_t\"] = pd.to_numeric(env.get(\"pcb_t\"), errors=\"coerce\")\n g = (env.set_index(\"device_time\").groupby(\"device_id\")\n .resample(\"1D\").agg(pcb_t_max=(\"pcb_t\", \"max\")))\n frames.append(g)\n\n d = tabs.get(\"measurements_device\")\n if d is not None and len(d):\n d = d.copy()\n for col in [\"rssi\", \"b_soc\"]:\n if col in d:\n d[col] = pd.to_numeric(d[col], errors=\"coerce\")\n g = (d.set_index(\"device_time\").groupby(\"device_id\")\n .resample(\"1D\").agg(rssi=(\"rssi\", \"median\"),\n b_soc=(\"b_soc\", \"median\")))\n frames.append(g)\n\n if not frames:\n raise ValueError(\"Hiç ölçüm tablosu bulunamadı\")\n daily = pd.concat(frames, axis=1)\n daily = daily.dropna(how=\"all\")\n daily[\"ind_ratio\"] = daily[\"re_l\"] / daily[\"ae\"].where(daily[\"ae\"] > 0)\n daily[\"cap_ratio\"] = daily[\"re_g\"] / daily[\"ae\"].where(daily[\"ae\"] > 0)\n return daily.reset_index()\n\n\n# ----------------------------------------------------------------------------\n# 2. ANALİZ — cihaz özetleri, trendler, anomaliler, sağlık skoru\n# ----------------------------------------------------------------------------\ndef _slope_per_day(sub, col):\n s = sub[[\"day_idx\", col]].dropna()\n if len(s) < CONFIG[\"min_days_trend\"]:\n return np.nan\n x, y = s[\"day_idx\"].values.astype(float), s[col].values.astype(float)\n if np.ptp(x) < 1:\n return np.nan\n return float(np.polyfit(x, y, 1)[0])\n\n\ndef _days_to_limit(current, slope, limit, rising=True):\n \"\"\"Mevcut değer + eğimle sınıra kaç günde ulaşılır (365 ile sınırlı).\"\"\"\n if not np.isfinite(current) or not np.isfinite(slope):\n return None\n if rising:\n if current >= limit:\n return 0\n if slope <= 1e-12:\n return None\n d = (limit - current) / slope\n else:\n if current <= limit:\n return 0\n if slope >= -1e-12:\n return None\n d = (limit - current) / slope\n return int(d) if 0 <= d <= 365 else None\n\n\ndef analyze(daily, devices_df=None, cfg=CONFIG):\n \"\"\"Cihaz başına özet, bulgular ve sağlık skoru üretir.\"\"\"\n try:\n from sklearn.ensemble import IsolationForest\n HAS_SK = True\n except Exception:\n HAS_SK = False\n\n names = {}\n if devices_df is not None:\n names = (devices_df.dropna(subset=[\"id\"]).set_index(\"id\")[\"name\"]\n .fillna(\"\").to_dict())\n\n results = {}\n feat_cols = [\"ind_ratio\", \"cap_ratio\", \"pf_loaded\", \"thd_v\", \"thd_i\",\n \"v_imb\", \"i_imb\", \"pcb_t_max\", \"fq_dev\"]\n\n for dev, sub in daily.groupby(\"device_id\"):\n sub = sub.sort_values(\"device_time\").copy()\n sub[\"day_idx\"] = (sub[\"device_time\"] - sub[\"device_time\"].min()).dt.days\n n_days = int(sub[\"ae\"].notna().sum()) if \"ae\" in sub else len(sub)\n ae_tot = float(np.nansum(sub.get(\"ae\", np.nan)))\n if n_days < 3 or ae_tot <= 0:\n continue # analiz için yetersiz veri\n\n r = {\"device_id\": dev,\n \"name\": names.get(dev, \"\") or dev,\n \"days\": int(len(sub)),\n \"first\": str(sub[\"device_time\"].min().date()),\n \"last\": str(sub[\"device_time\"].max().date()),\n \"ae_total_kwh\": ae_tot / 1000.0,\n \"re_l_total_kvarh\": float(np.nansum(sub.get(\"re_l\", np.nan))) / 1000.0,\n \"re_g_total_kvarh\": float(np.nansum(sub.get(\"re_g\", np.nan))) / 1000.0}\n r[\"ind_ratio\"] = r[\"re_l_total_kvarh\"] / r[\"ae_total_kwh\"] if r[\"ae_total_kwh\"] > 0 else np.nan\n r[\"cap_ratio\"] = r[\"re_g_total_kvarh\"] / r[\"ae_total_kwh\"] if r[\"ae_total_kwh\"] > 0 else np.nan\n\n # son 30 gün oranı (güncel durum)\n last30 = sub[sub[\"device_time\"] >= sub[\"device_time\"].max() - pd.Timedelta(days=30)]\n ae30 = np.nansum(last30.get(\"ae\", np.nan))\n r[\"ind_ratio_30g\"] = float(np.nansum(last30.get(\"re_l\", np.nan)) / ae30) if ae30 > 0 else np.nan\n r[\"cap_ratio_30g\"] = float(np.nansum(last30.get(\"re_g\", np.nan)) / ae30) if ae30 > 0 else np.nan\n\n for col in [\"pf_loaded\", \"thd_v\", \"thd_i\", \"v_imb\", \"i_imb\", \"pcb_t_max\", \"rssi\"]:\n r[col] = float(np.nanmedian(sub[col])) if col in sub and sub[col].notna().any() else np.nan\n\n vrms = float(np.nanmedian(sub[\"vrms_med\"])) if \"vrms_med\" in sub and sub[\"vrms_med\"].notna().any() else np.nan\n lo, hi = cfg[\"vrms_plausible\"]\n r[\"calibrated\"] = bool(np.isfinite(vrms) and lo <= vrms <= hi)\n r[\"vrms_med\"] = vrms\n\n # trendler (birim/gün)\n r[\"trend\"] = {c: _slope_per_day(sub, c) for c in\n [\"pf_loaded\", \"thd_i\", \"i_imb\", \"ind_ratio\", \"pcb_t_max\"]}\n # sınır projeksiyonları\n r[\"proj\"] = {\n \"ind_ratio\": _days_to_limit(r[\"ind_ratio_30g\"], r[\"trend\"][\"ind_ratio\"], cfg[\"ind_limit\"], rising=True),\n \"thd_i\": _days_to_limit(r[\"thd_i\"], r[\"trend\"][\"thd_i\"], cfg[\"thd_i_limit\"], rising=True),\n \"i_imb\": _days_to_limit(r[\"i_imb\"], r[\"trend\"][\"i_imb\"], cfg[\"i_imb_limit\"], rising=True),\n \"pf_loaded\": _days_to_limit(r[\"pf_loaded\"], r[\"trend\"][\"pf_loaded\"], cfg[\"pf_target\"], rising=False),\n }\n\n # anomali tespiti (IsolationForest, gün bazında)\n r[\"anomaly_ratio\"] = np.nan\n r[\"anomaly_days\"] = []\n X = sub[[c for c in feat_cols if c in sub]].copy()\n X = X.loc[:, X.notna().mean() > 0.5]\n X = X.dropna()\n if HAS_SK and len(X) >= cfg[\"min_days_iforest\"] and X.shape[1] >= 3:\n iso = IsolationForest(contamination=cfg[\"anomaly_contamination\"], random_state=42)\n lab = iso.fit_predict((X - X.mean()) / X.std(ddof=0).replace(0, 1))\n anom_idx = X.index[lab == -1]\n r[\"anomaly_ratio\"] = float(len(anom_idx) / len(X))\n r[\"anomaly_days\"] = [str(d.date()) for d in\n sub.loc[anom_idx, \"device_time\"].sort_values().tail(5)]\n\n r[\"findings\"] = _findings(r, cfg)\n r[\"score\"] = _score(r, cfg)\n r[\"risk\"] = _risk(r[\"score\"])\n results[dev] = r\n\n return results\n\n\ndef _findings(r, cfg):\n F = []\n\n def add(code, severity, value, limit, **kw):\n F.append(dict(code=code, severity=severity, value=value, limit=limit, **kw))\n\n ir = r.get(\"ind_ratio_30g\")\n if np.isfinite(ir or np.nan):\n if ir > cfg[\"ind_limit\"]:\n add(\"REAKTIF_IND_ASIM\", \"yüksek\" if ir > 1.5 * cfg[\"ind_limit\"] else \"orta\",\n ir, cfg[\"ind_limit\"])\n elif ir > 0.8 * cfg[\"ind_limit\"]:\n add(\"REAKTIF_IND_YAKIN\", \"düşük\", ir, cfg[\"ind_limit\"])\n cr = r.get(\"cap_ratio_30g\")\n if np.isfinite(cr or np.nan):\n if cr > cfg[\"cap_limit\"]:\n add(\"REAKTIF_CAP_ASIM\", \"yüksek\" if cr > 1.5 * cfg[\"cap_limit\"] else \"orta\",\n cr, cfg[\"cap_limit\"])\n elif cr > 0.8 * cfg[\"cap_limit\"]:\n add(\"REAKTIF_CAP_YAKIN\", \"düşük\", cr, cfg[\"cap_limit\"])\n\n pf = r.get(\"pf_loaded\")\n if np.isfinite(pf or np.nan) and pf < cfg[\"pf_target\"]:\n add(\"PF_DUSUK\", \"yüksek\" if pf < 0.85 else \"orta\", pf, cfg[\"pf_target\"])\n\n for key, code in [(\"thd_i\", \"THD_AKIM\"), (\"thd_v\", \"THD_GERILIM\")]:\n val = r.get(key)\n lim = cfg[key + \"_limit\"]\n if np.isfinite(val or np.nan) and val > lim:\n add(code, \"yüksek\" if val > 2 * lim else \"orta\", val, lim)\n\n for key, code, lim in [(\"i_imb\", \"AKIM_DENGESIZ\", cfg[\"i_imb_limit\"]),\n (\"v_imb\", \"GERILIM_DENGESIZ\", cfg[\"v_imb_limit\"])]:\n val = r.get(key)\n if r.get(\"calibrated\") is False and code == \"GERILIM_DENGESIZ\":\n pass # dengesizlik oransal, kalibrasyonsuz da anlamlı — yine de raporla\n if np.isfinite(val or np.nan) and val > lim:\n add(code, \"yüksek\" if val > 2 * lim else \"orta\", val, lim)\n\n t = r.get(\"pcb_t_max\")\n if np.isfinite(t or np.nan) and t > cfg[\"pcb_t_limit\"]:\n add(\"SICAKLIK\", \"orta\", t, cfg[\"pcb_t_limit\"])\n\n ar = r.get(\"anomaly_ratio\")\n if np.isfinite(ar or np.nan) and ar > 0.12:\n add(\"ANOMALI\", \"orta\", ar, 0.12, days=r.get(\"anomaly_days\", []))\n\n for key, code in [(\"ind_ratio\", \"TREND_REAKTIF\"), (\"thd_i\", \"TREND_THD\"),\n (\"i_imb\", \"TREND_DENGESIZ\")]:\n d = r[\"proj\"].get(key if key != \"ind_ratio\" else \"ind_ratio\")\n if d is not None and 0 < d <= 90:\n add(code, \"orta\" if d > 30 else \"yüksek\", d, 90, metric=key)\n dpf = r[\"proj\"].get(\"pf_loaded\")\n if dpf is not None and 0 < dpf <= 90:\n add(\"TREND_PF\", \"orta\" if dpf > 30 else \"yüksek\", dpf, 90, metric=\"pf_loaded\")\n\n if not r.get(\"calibrated\", True):\n add(\"KALIBRASYON\", \"bilgi\", r.get(\"vrms_med\"), None)\n if r[\"days\"] < 14:\n add(\"VERI_AZ\", \"bilgi\", r[\"days\"], 14)\n return F\n\n\ndef _score(r, cfg):\n s = 100.0\n ir, cr = r.get(\"ind_ratio_30g\"), r.get(\"cap_ratio_30g\")\n if np.isfinite(ir or np.nan) and ir > cfg[\"ind_limit\"]:\n s -= min(25, 25 * (ir - cfg[\"ind_limit\"]) / cfg[\"ind_limit\"] + 10)\n if np.isfinite(cr or np.nan) and cr > cfg[\"cap_limit\"]:\n s -= min(15, 15 * (cr - cfg[\"cap_limit\"]) / cfg[\"cap_limit\"] + 6)\n pf = r.get(\"pf_loaded\")\n if np.isfinite(pf or np.nan) and pf < cfg[\"pf_target\"]:\n s -= min(15, (cfg[\"pf_target\"] - pf) * 100)\n if np.isfinite(r.get(\"thd_i\") or np.nan) and r[\"thd_i\"] > cfg[\"thd_i_limit\"]:\n s -= min(10, 50 * (r[\"thd_i\"] - cfg[\"thd_i_limit\"]))\n if np.isfinite(r.get(\"thd_v\") or np.nan) and r[\"thd_v\"] > cfg[\"thd_v_limit\"]:\n s -= min(10, 100 * (r[\"thd_v\"] - cfg[\"thd_v_limit\"]))\n if np.isfinite(r.get(\"i_imb\") or np.nan) and r[\"i_imb\"] > cfg[\"i_imb_limit\"]:\n s -= min(10, 50 * (r[\"i_imb\"] - cfg[\"i_imb_limit\"]))\n if np.isfinite(r.get(\"v_imb\") or np.nan) and r[\"v_imb\"] > cfg[\"v_imb_limit\"]:\n s -= min(5, 100 * (r[\"v_imb\"] - cfg[\"v_imb_limit\"]))\n ar = r.get(\"anomaly_ratio\")\n if np.isfinite(ar or np.nan):\n s -= min(10, max(0, (ar - 0.08)) * 100)\n n_trend = sum(1 for f in r[\"findings\"] if f[\"code\"].startswith(\"TREND\"))\n s -= min(10, 5 * n_trend)\n return round(max(0.0, s), 1)\n", "id": "5U7d3cbzkF09" }, { "cell_type": "code", "metadata": { "id": "NqMboKlUkF0-" }, "execution_count": null, "outputs": [], "source": "%%writefile qapu_narrator.py\n# -*- coding: utf-8 -*-\n\"\"\"\nQAPU Offline Narrator — şablon tabanlı, internet/LLM gerektirmeyen Türkçe rapor üretici.\nDört hedef kitle: teknik uzman, özel sektör yöneticisi, kamu yöneticisi, vatandaş.\n\"\"\"\nimport numpy as np\n\nAUDIENCES = [\"teknik\", \"yonetici_ozel\", \"yonetici_kamu\", \"vatandas\"]\n\nAUDIENCE_META = {\n \"teknik\": dict(baslik=\"Teknik Uzman Raporu\",\n dil=\"mühendislik terminolojisi, sayısal detay, standart referansları\"),\n \"yonetici_ozel\": dict(baslik=\"Yönetici Özeti (Özel Sektör)\",\n dil=\"iş etkisi, maliyet/ceza riski, önceliklendirilmiş aksiyon\"),\n \"yonetici_kamu\": dict(baslik=\"Yönetici Özeti (Kamu)\",\n dil=\"mevzuat uyumu, hizmet sürekliliği, kamu kaynağı verimliliği\"),\n \"vatandas\": dict(baslik=\"Vatandaş Bilgilendirme Notu\",\n dil=\"sade Türkçe, benzetmeli anlatım, teknik terim yok\"),\n}\n\n\n# ---------------------------------------------------------------- yardımcılar\ndef pct(x, nd=1):\n return \"—\" if x is None or not np.isfinite(x) else f\"%{x*100:.{nd}f}\".replace(\".\", \",\")\n\ndef num(x, nd=2, unit=\"\"):\n if x is None or not np.isfinite(x):\n return \"—\"\n s = f\"{x:,.{nd}f}\".replace(\",\", \"§\").replace(\".\", \",\").replace(\"§\", \".\")\n return s + ((\" \" + unit) if unit else \"\")\n\ndef _sev_word(sev, aud):\n tablo = {\n \"teknik\": {\"yüksek\": \"KRİTİK\", \"orta\": \"UYARI\", \"düşük\": \"İZLEME\", \"bilgi\": \"BİLGİ\"},\n \"yonetici_ozel\": {\"yüksek\": \"Acil\", \"orta\": \"Önemli\", \"düşük\": \"Takip\", \"bilgi\": \"Not\"},\n \"yonetici_kamu\": {\"yüksek\": \"Acil\", \"orta\": \"Önemli\", \"düşük\": \"Takip\", \"bilgi\": \"Not\"},\n \"vatandas\": {\"yüksek\": \"önemli bir sorun\", \"orta\": \"dikkat edilmesi gereken bir durum\",\n \"düşük\": \"küçük bir uyarı\", \"bilgi\": \"bilgi notu\"},\n }\n return tablo[aud][sev]\n\n\n# ------------------------------------------------- bulgu cümleleri (kitleye göre)\ndef _finding_text(f, r, aud):\n c, v, lim, sev = f[\"code\"], f.get(\"value\"), f.get(\"limit\"), f[\"severity\"]\n S = _sev_word(sev, aud)\n\n if c == \"REAKTIF_IND_ASIM\":\n t = {\n \"teknik\": f\"[{S}] Endüktif reaktif/aktif enerji oranı son 30 günde {pct(v)} — EPDK sınırı {pct(lim,0)}'in üzerinde. Kompanzasyon panosu devre dışı ya da kondansatör kademeleri yetersiz/arızalı olabilir; motor yüklerinin cosφ'si düşük.\",\n \"yonetici_ozel\": f\"[{S}] Bu tesis reaktif enerji sınırını aşıyor (ölçülen {pct(v)}, yasal sınır {pct(lim,0)}). Dağıtım şirketi bu durumda reaktif ceza bedeli tahakkuk ettirir; kompanzasyon yatırımı/bakımı bu cezayı tamamen ortadan kaldırabilir.\",\n \"yonetici_kamu\": f\"[{S}] Tesis, elektrik mevzuatındaki reaktif enerji sınırını ({pct(lim,0)}) aşmaktadır (ölçülen {pct(v)}). Bu, elektrik faturasına yansıyan ve önlenebilir bir kamu kaynağı kaybıdır; kompanzasyon sisteminin kontrolü gerekir.\",\n \"vatandas\": f\"Bu tesiste {S} tespit edildi: tesis, şebekeden aldığı elektriğin bir kısmını verimsiz kullanıyor (buna 'reaktif enerji' denir — bardağı doldururken yanına taşan su gibi düşünebilirsiniz). Bu durum faturayı artırır; basit bir elektrik panosu bakımı ile düzeltilebilir.\",\n }\n elif c == \"REAKTIF_IND_YAKIN\":\n t = {\n \"teknik\": f\"[{S}] Endüktif oran {pct(v)} — sınırın ({pct(lim,0)}) %80'ini geçti. Kademe ayarları gözden geçirilmeli.\",\n \"yonetici_ozel\": f\"[{S}] Reaktif oran {pct(v)} ile ceza sınırına yaklaşıyor; küçük bir ayarla ceza riski önlenebilir.\",\n \"yonetici_kamu\": f\"[{S}] Reaktif oran {pct(v)}; yasal sınıra yaklaşılmıştır, önleyici kontrol önerilir.\",\n \"vatandas\": f\"Tesisin elektrik kullanım verimliliği sınıra yaklaşıyor; şimdiden yapılacak küçük bir bakım ileride fatura artışını önler.\",\n }\n elif c == \"REAKTIF_CAP_ASIM\":\n t = {\n \"teknik\": f\"[{S}] Kapasitif reaktif oran {pct(v)} — EPDK sınırı {pct(lim,0)} aşıldı. Aşırı kompanzasyon: kondansatör kademeleri düşük yükte devrede kalıyor; RG rölesi ayarı/kademe küçültme gerekli.\",\n \"yonetici_ozel\": f\"[{S}] Tesis şebekeye gereksiz 'kapasitif' enerji veriyor ({pct(v)}, sınır {pct(lim,0)}) — bu da ceza kalemidir. Kompanzasyon ayarı tek seferlik düşük maliyetli bir müdahaledir.\",\n \"yonetici_kamu\": f\"[{S}] Kapasitif reaktif sınır aşımı ({pct(v)} > {pct(lim,0)}) tespit edildi; fatura cezası riski için kompanzasyon ayarı gereklidir.\",\n \"vatandas\": f\"Tesisin elektrik düzeltme sistemi gereğinden fazla çalışıyor; bu da tıpkı eksik çalışması gibi faturaya ceza olarak yansıyabilir. Ayar gerektiren bir durumdur.\",\n }\n elif c == \"REAKTIF_CAP_YAKIN\":\n t = {\n \"teknik\": f\"[{S}] Kapasitif oran {pct(v)}, sınırın %80'inde. Düşük yük saatlerinde kademe çıkışı kontrol edilmeli.\",\n \"yonetici_ozel\": f\"[{S}] Kapasitif reaktif oran sınıra yaklaşıyor ({pct(v)}); önleyici ayar önerilir.\",\n \"yonetici_kamu\": f\"[{S}] Kapasitif oran {pct(v)}; sınır aşımı öncesi kontrol önerilir.\",\n \"vatandas\": f\"Tesisin elektrik sisteminde küçük bir ayar ihtiyacı görünüyor; şu an sorun yok ama takipte.\",\n }\n elif c == \"PF_DUSUK\":\n t = {\n \"teknik\": f\"[{S}] Yük altındaki ortalama güç faktörü {num(v)} (hedef ≥ {num(lim)}). Düşük cosφ hat kayıplarını ve trafo/kablo ısınmasını artırır; motor verimsizliği veya kompanzasyon eksikliği göstergesidir.\",\n \"yonetici_ozel\": f\"[{S}] Güç faktörü {num(v)} — hedefin ({num(lim)}) altında. Aynı iş için şebekeden daha fazla akım çekiliyor: kayıplar, ısınma ve ceza riski artıyor.\",\n \"yonetici_kamu\": f\"[{S}] Tesiste güç faktörü {num(v)} ölçülmüştür (hedef {num(lim)}). Enerji verimliliği düşüktür; iyileştirme, işletme giderini kalıcı olarak azaltır.\",\n \"vatandas\": f\"Tesis, kullandığı elektriği ideal verimle kullanamıyor (verim göstergesi {num(v)}; ideali {num(lim)} ve üzeri). Bakımla düzeltilebilir, hizmetin aksaması söz konusu değil.\",\n }\n elif c == \"THD_AKIM\":\n t = {\n \"teknik\": f\"[{S}] Akım THD medyanı {pct(v)} (eşik {pct(lim,0)}). Harmonik kaynaklı ek ısınma motor/trafo ömrünü kısaltır; sürücü (VFD) filtreleri ve kondansatör rezonansı kontrol edilmeli (IEEE 519 referansı).\",\n \"yonetici_ozel\": f\"[{S}] Elektrik akımında yüksek bozulma (harmonik) ölçüldü ({pct(v)}). Bu, ekipman ömrünü kısaltan ve arıza sıklığını artıran sinsi bir maliyet kalemidir; filtre yatırımı değerlendirilmelidir.\",\n \"yonetici_kamu\": f\"[{S}] Tesiste akım harmonik bozulması {pct(v)} seviyesindedir (eşik {pct(lim,0)}). Ekipman ömrü ve arıza riski açısından planlı bakım kapsamına alınmalıdır.\",\n \"vatandas\": f\"Tesisteki elektrik akımında 'parazit' benzeri bir bozulma ölçüldü. Bu, cihazların zamanla daha çabuk yıpranmasına yol açabilir; teknik ekipler gerekli bakımı planlayabilir.\",\n }\n elif c == \"THD_GERILIM\":\n t = {\n \"teknik\": f\"[{S}] Gerilim THD {pct(v)} (sınır {pct(lim,0)}, IEEE 519). Şebeke tarafı veya tesis içi harmonik kaynakları araştırılmalı.\",\n \"yonetici_ozel\": f\"[{S}] Şebeke geriliminde bozulma ölçüldü ({pct(v)}); hassas cihazlar için risk oluşturabilir.\",\n \"yonetici_kamu\": f\"[{S}] Gerilim kalitesinde bozulma ({pct(v)}) tespit edildi; dağıtım şirketi ile birlikte incelenmesi önerilir.\",\n \"vatandas\": f\"Tesise gelen elektriğin kalitesinde dalgalanma görüldü; takip ediliyor.\",\n }\n elif c == \"AKIM_DENGESIZ\":\n t = {\n \"teknik\": f\"[{S}] Üç faz akım dengesizliği {pct(v)} (eşik {pct(lim,0)}). Olası nedenler: tek faz yük dağılımı, gevşek klemens, sargı direnç farkı, kondansatör faz arızası. Dengesizlik negatif bileşen ısınması üzerinden motor ömrünü hızla tüketir.\",\n \"yonetici_ozel\": f\"[{S}] Motorun üç fazı eşit yüklenmiyor (dengesizlik {pct(v)}). Bu, motor arızasının en sık erken habercisidir; planlı kontrol plansız duruştan kat kat ucuzdur.\",\n \"yonetici_kamu\": f\"[{S}] Tesiste faz dengesizliği {pct(v)} ölçülmüştür. Pompanın/motorun beklenmedik arızasını önlemek için planlı bakım önerilir; hizmet kesintisi riski azaltılır.\",\n \"vatandas\": f\"Tesisteki motorun bir tarafı diğerlerinden fazla zorlanıyor — üç tekerleği sağlam, biri inik bir araç gibi. Erken bakım yapılırsa büyük arıza ve hizmet kesintisi yaşanmaz.\",\n }\n elif c == \"GERILIM_DENGESIZ\":\n t = {\n \"teknik\": f\"[{S}] Gerilim dengesizliği {pct(v)} (sınır {pct(lim,0)}, IEC/NEMA). %1 gerilim dengesizliği ~%6-10 akım dengesizliğine yol açar; besleme ve bağlantılar kontrol edilmeli.\",\n \"yonetici_ozel\": f\"[{S}] Besleme geriliminde dengesizlik ({pct(v)}) var; motor ısınması ve verim kaybına yol açar.\",\n \"yonetici_kamu\": f\"[{S}] Besleme gerilimi dengesizliği ({pct(v)}) tespit edildi; şebeke bağlantısı kontrol edilmelidir.\",\n \"vatandas\": f\"Tesise gelen elektrik hatlarında küçük bir dengesizlik ölçüldü; teknik ekip takibinde.\",\n }\n elif c == \"SICAKLIK\":\n t = {\n \"teknik\": f\"[{S}] Pano/elektronik sıcaklığı {num(v,1,'°C')} (eşik {num(lim,0,'°C')}). Havalandırma, toz ve bağlantı temasları kontrol edilmeli.\",\n \"yonetici_ozel\": f\"[{S}] Kontrol panosunda yüksek sıcaklık ({num(v,1,'°C')}); elektronik ömrü ve yangın riski açısından kontrol edilmeli.\",\n \"yonetici_kamu\": f\"[{S}] Panoda yüksek sıcaklık ({num(v,1,'°C')}) ölçüldü; iş güvenliği ve ekipman ömrü için kontrol planlanmalıdır.\",\n \"vatandas\": f\"Cihaz dolabında normalden yüksek sıcaklık ölçüldü; kontrol planlandı.\",\n }\n elif c == \"ANOMALI\":\n days = \", \".join(f.get(\"days\", [])[-3:])\n t = {\n \"teknik\": f\"[{S}] Makine öğrenmesi (IsolationForest) günlerin {pct(v)}'ini anormal işaretledi (son örnekler: {days}). İşletme kayıtlarıyla karşılaştırılmalı.\",\n \"yonetici_ozel\": f\"[{S}] Otomatik analiz, çalışma günlerinin {pct(v)}'inde olağan dışı davranış tespit etti; teknik inceleme önerilir.\",\n \"yonetici_kamu\": f\"[{S}] Otomatik izleme sistemi olağan dışı çalışma günleri tespit etti ({pct(v)}); teknik birim bilgilendirilmelidir.\",\n \"vatandas\": f\"Otomatik izleme sistemi bazı günlerde alışılmadık çalışma fark etti; uzmanlar inceliyor.\",\n }\n elif c in (\"TREND_REAKTIF\", \"TREND_THD\", \"TREND_DENGESIZ\", \"TREND_PF\"):\n metric_ad = {\"TREND_REAKTIF\": \"reaktif oran\", \"TREND_THD\": \"akım harmoniği\",\n \"TREND_DENGESIZ\": \"faz dengesizliği\", \"TREND_PF\": \"güç faktörü\"}[c]\n t = {\n \"teknik\": f\"[{S}] Kestirim: mevcut eğilim sürerse {metric_ad} ~{int(v)} gün içinde sınırı aşacak. Kök neden analizi için pencere: önümüzdeki {max(7,int(v)//2)} gün.\",\n \"yonetici_ozel\": f\"[{S}] Öngörü: {metric_ad} kötüleşiyor; yaklaşık {int(v)} gün içinde sınır aşımı bekleniyor. Şimdi planlanan bakım, ceza/arıza gerçekleşmeden sorunu çözer.\",\n \"yonetici_kamu\": f\"[{S}] Öngörü: {metric_ad} göstergesi yaklaşık {int(v)} gün içinde sınırı aşabilir; bütçeli planlı bakım için zaman penceresi mevcuttur.\",\n \"vatandas\": f\"Ölçümler, yaklaşık {int(v)} gün içinde bakım gerekebileceğini gösteriyor. Sorun büyümeden planlama yapılıyor — hizmette kesinti beklenmiyor.\",\n }\n elif c == \"KALIBRASYON\":\n t = {\n \"teknik\": f\"[{S}] Gerilim ölçümleri kalibre edilmemiş görünüyor (medyan VRMS {num(v,0)}). Mutlak değerler yerine oransal metrikler (dengesizlik, THD, PF, reaktif oran) esas alındı; VT/CT kalibrasyonu önerilir.\",\n \"yonetici_ozel\": f\"[{S}] Bu cihazın ölçüm kalibrasyonu tamamlanmamış; oransal göstergeler güvenilir, mutlak kW/V değerleri kalibrasyon sonrası netleşecek.\",\n \"yonetici_kamu\": f\"[{S}] Cihaz kalibrasyonu beklemede; raporda oransal göstergeler esas alınmıştır.\",\n \"vatandas\": f\"Bu noktadaki ölçüm cihazının ince ayarı henüz tamamlanmadı; genel değerlendirme yine de güvenilirdir.\",\n }\n elif c == \"VERI_AZ\":\n t = {\n \"teknik\": f\"[{S}] Yalnızca {int(v)} günlük veri mevcut (önerilen ≥ {int(lim)}); trend ve anomali kestirimlerinin güven aralığı geniştir.\",\n \"yonetici_ozel\": f\"[{S}] Veri geçmişi kısa ({int(v)} gün); sonuçlar ilk izlenimdir, izleme sürüyor.\",\n \"yonetici_kamu\": f\"[{S}] İzleme yeni başlamıştır ({int(v)} gün); değerlendirme güncellenecektir.\",\n \"vatandas\": f\"Bu nokta yakın zamanda izlemeye alındı; değerlendirme her gün biraz daha kesinleşiyor.\",\n }\n else:\n t = {a: f\"[{S}] {c}: {v}\" for a in AUDIENCES}\n return t[aud]\n\n\n# ------------------------------------------------------------- öneri motoru\n_RECS = {\n \"REAKTIF_IND_ASIM\": {\n \"teknik\": \"Kompanzasyon panosu: RG rölesi ayarları, kondansatör kapasite ölçümü, kontaktör/sigorta kontrolü; gerekirse kademe ekleme.\",\n \"mgr\": \"Kompanzasyon bakımı/yatırımı için teknik ekibe iş emri açılması (tipik geri ödeme süresi aylar mertebesinde).\",\n \"vatandas\": \"Elektrik panosunda planlı bakım yapılacak.\",\n },\n \"REAKTIF_CAP_ASIM\": {\n \"teknik\": \"Düşük yükte kademe çıkışını sağlayacak RG röle ayarı; en küçük kademenin küçültülmesi veya şönt reaktör değerlendirmesi.\",\n \"mgr\": \"Kompanzasyon ayarı için kısa süreli teknik müdahale planlanması.\",\n \"vatandas\": \"Sistem ayarı yapılacak.\",\n },\n \"PF_DUSUK\": {\n \"teknik\": \"Motor yüklenme analizi (boşta/az yükte çalışmayı azalt), kompanzasyon kademe yeterliliği, gerekiyorsa VFD parametre kontrolü.\",\n \"mgr\": \"Verimlilik iyileştirmesi: pompa çalışma programı ve kompanzasyon birlikte gözden geçirilmeli.\",\n \"vatandas\": \"Verimlilik bakımı planlanacak.\",\n },\n \"THD_AKIM\": {\n \"teknik\": \"Harmonik kaynak taraması (VFD, doğrultucular); pasif/aktif filtre; kondansatörlerde detuned reaktör kontrolü.\",\n \"mgr\": \"Harmonik filtre yatırımı için teknik fizibilite talep edilmeli.\",\n \"vatandas\": \"Elektrik kalitesi için ek donanım değerlendirilecek.\",\n },\n \"AKIM_DENGESIZ\": {\n \"teknik\": \"Termal kamera ile klemens taraması, faz akım ölçüm doğrulaması, motor sargı direnç/izolasyon testi (megger), kondansatör faz kontrolü.\",\n \"mgr\": \"Planlı motor/pano kontrolü — plansız duruş riskine karşı en yüksek öncelik.\",\n \"vatandas\": \"Motor kontrol bakımı planlanacak.\",\n },\n \"GERILIM_DENGESIZ\": {\n \"teknik\": \"Besleme bağlantıları ve şebeke tarafı ölçüm; dağıtım şirketi ile gerilim kalitesi kaydı paylaşımı.\",\n \"mgr\": \"Dağıtım şirketi nezdinde gerilim kalitesi başvurusu değerlendirilmeli.\",\n \"vatandas\": \"Elektrik şirketi ile birlikte kontrol yapılacak.\",\n },\n \"SICAKLIK\": {\n \"teknik\": \"Pano havalandırma/filtre temizliği, termal tarama, bağlantı tork kontrolü.\",\n \"mgr\": \"Pano bakımı iş emri.\",\n \"vatandas\": \"Dolap bakımı yapılacak.\",\n },\n}\n\n\ndef _recs_for(findings, aud):\n out, seen = [], set()\n key = \"teknik\" if aud == \"teknik\" else (\"vatandas\" if aud == \"vatandas\" else \"mgr\")\n for f in sorted(findings, key=lambda x: {\"yüksek\": 0, \"orta\": 1, \"düşük\": 2, \"bilgi\": 3}[x[\"severity\"]]):\n base = f[\"code\"].replace(\"_YAKIN\", \"_ASIM\").replace(\"TREND_REAKTIF\", \"REAKTIF_IND_ASIM\") \\\n .replace(\"TREND_THD\", \"THD_AKIM\").replace(\"TREND_DENGESIZ\", \"AKIM_DENGESIZ\") \\\n .replace(\"TREND_PF\", \"PF_DUSUK\")\n rec = _RECS.get(base, {}).get(key)\n if rec and base not in seen:\n out.append(rec)\n seen.add(base)\n return out\n\n\n# ------------------------------------------------------------ cihaz raporu\ndef device_report(r, aud):\n meta = AUDIENCE_META[aud]\n L = []\n L.append(f\"## {r['name']} — {meta['baslik']}\")\n L.append(f\"*Dönem: {r['first']} → {r['last']} ({r['days']} gün) · Rapor: otomatik üretim (offline narrator)*\\n\")\n\n risk_tr = {\"İYİ\": \"iyi durumda\", \"İZLENMELİ\": \"izlenmesi gereken\",\n \"RİSKLİ\": \"riskli\", \"KRİTİK\": \"kritik\"}\n\n if aud == \"teknik\":\n L.append(f\"**Sağlık skoru: {r['score']}/100 ({r['risk']})**\\n\")\n L.append(\"| Metrik | Değer | Eşik |\")\n L.append(\"|---|---|---|\")\n L.append(f\"| Aktif enerji (dönem) | {num(r['ae_total_kwh'],1,'kWh')} | — |\")\n L.append(f\"| Endüktif oran (30g) | {pct(r.get('ind_ratio_30g'))} | %20 (EPDK) |\")\n L.append(f\"| Kapasitif oran (30g) | {pct(r.get('cap_ratio_30g'))} | %15 (EPDK) |\")\n L.append(f\"| Güç faktörü (yükte) | {num(r.get('pf_loaded'))} | ≥0,95 |\")\n L.append(f\"| THD-I / THD-V (medyan) | {pct(r.get('thd_i'))} / {pct(r.get('thd_v'))} | %15 / %8 |\")\n L.append(f\"| Akım / gerilim dengesizliği | {pct(r.get('i_imb'))} / {pct(r.get('v_imb'))} | %10 / %2 |\")\n if np.isfinite(r.get(\"anomaly_ratio\") or np.nan):\n L.append(f\"| Anomali oranı (IForest) | {pct(r['anomaly_ratio'])} | %12 |\")\n L.append(\"\")\n elif aud == \"vatandas\":\n durum = risk_tr[r[\"risk\"]]\n L.append(f\"Bu tesis şu anda **{durum}** olarak değerlendiriliyor \"\n f\"(100 üzerinden {int(r['score'])} puan). Değerlendirme, tesisteki elektrik \"\n f\"ölçüm cihazının {r['days']} günlük verisine dayanıyor.\\n\")\n else:\n kamu = \" Kamu hizmet sürekliliği önceliklidir.\" if aud == \"yonetici_kamu\" else \"\"\n L.append(f\"**Durum: {r['risk']} (skor {r['score']}/100)** — dönem tüketimi \"\n f\"{num(r['ae_total_kwh'],0,'kWh')}.{kamu}\\n\")\n\n if r[\"findings\"]:\n L.append(\"**Bulgular**\" if aud != \"vatandas\" else \"**Tespit edilenler**\")\n for f in sorted(r[\"findings\"], key=lambda x: {\"yüksek\": 0, \"orta\": 1, \"düşük\": 2, \"bilgi\": 3}[x[\"severity\"]]):\n L.append(f\"- {_finding_text(f, r, aud)}\")\n L.append(\"\")\n else:\n msg = {\"teknik\": \"Tüm metrikler eşik değerlerin içinde; rutin izleme sürdürülmeli.\",\n \"vatandas\": \"Her şey yolunda görünüyor; düzenli izleme devam ediyor.\"}\n L.append(msg.get(aud, \"Önemli bir bulgu yok; rutin izleme sürüyor.\") + \"\\n\")\n\n recs = _recs_for(r[\"findings\"], aud)\n if recs:\n L.append(\"**Önerilen aksiyonlar**\" if aud != \"vatandas\" else \"**Ne yapılacak?**\")\n for i, rec in enumerate(recs, 1):\n L.append(f\"{i}. {rec}\")\n L.append(\"\")\n return \"\\n\".join(L)\n\n\n# -------------------------------------------------------------- filo raporu\ndef fleet_report(results, aud):\n meta = AUDIENCE_META[aud]\n rs = sorted(results.values(), key=lambda r: r[\"score\"])\n n = len(rs)\n krit = [r for r in rs if r[\"risk\"] == \"KRİTİK\"]\n risk = [r for r in rs if r[\"risk\"] == \"RİSKLİ\"]\n izle = [r for r in rs if r[\"risk\"] == \"İZLENMELİ\"]\n iyi = [r for r in rs if r[\"risk\"] == \"İYİ\"]\n toplam_kwh = sum(r[\"ae_total_kwh\"] for r in rs)\n reaktif_asim = [r for r in rs if any(f[\"code\"].startswith(\"REAKTIF\") and f[\"code\"].endswith(\"ASIM\")\n for f in r[\"findings\"])]\n L = [f\"# Filo Genel Durum — {meta['baslik']}\",\n f\"*{n} cihaz · toplam {num(toplam_kwh,0,'kWh')} aktif enerji · offline narrator ile otomatik üretildi*\\n\"]\n\n if aud == \"vatandas\":\n L.append(f\"İzlenen **{n} tesisin** {len(iyi)} tanesi iyi durumda, \"\n f\"{len(izle)} tanesi yakından izleniyor, {len(risk)+len(krit)} tanesinde \"\n f\"bakım planlaması yapılıyor. Amaç: arızaları olmadan önce yakalamak, \"\n f\"kesintisiz hizmet ve gereksiz masrafların önlenmesi.\\n\")\n if risk or krit:\n L.append(\"Bakım planlanan tesisler: \" + \", \".join(r[\"name\"] for r in (krit + risk)) + \".\\n\")\n else:\n L.append(f\"**Dağılım:** KRİTİK {len(krit)} · RİSKLİ {len(risk)} · İZLENMELİ {len(izle)} · İYİ {len(iyi)}\\n\")\n if reaktif_asim:\n if aud == \"teknik\":\n L.append(f\"**Reaktif sınır aşımı olan {len(reaktif_asim)} tesis:** \" +\n \", \".join(f\"{r['name']} (end. {pct(r.get('ind_ratio_30g'))}, kap. {pct(r.get('cap_ratio_30g'))})\"\n for r in reaktif_asim) + \"\\n\")\n else:\n L.append(f\"**Reaktif ceza riski taşıyan {len(reaktif_asim)} tesis var** — bu, doğrudan \"\n f\"faturaya yansıyan ve kompanzasyon bakımı ile tamamen önlenebilir bir kalemdir: \" +\n \", \".join(r[\"name\"] for r in reaktif_asim) + \"\\n\")\n L.append(\"| Tesis | Skor | Durum | End. oran | PF | Öncelikli bulgu |\")\n L.append(\"|---|---|---|---|---|---|\")\n for r in rs:\n top = r[\"findings\"][0][\"code\"] if r[\"findings\"] else \"—\"\n L.append(f\"| {r['name']} | {r['score']} | {r['risk']} | \"\n f\"{pct(r.get('ind_ratio_30g'))} | {num(r.get('pf_loaded'))} | {top} |\")\n L.append(\"\")\n if aud == \"teknik\":\n L.append(\"Eşikler: EPDK reaktif sınırları (end. %20 / kap. %15), IEEE 519 THD, \"\n \"IEC/NEMA gerilim dengesizliği %2. Anomali: IsolationForest, gün bazlı özellik uzayı.\\n\")\n else:\n oncelik = krit + risk\n if oncelik:\n L.append(\"**Önerilen öncelik sırası:** \" +\n \" → \".join(f\"{i+1}) {r['name']}\" for i, r in enumerate(oncelik[:5])) + \"\\n\")\n return \"\\n\".join(L)\n", "id": "NqMboKlUkF0-" }, { "cell_type": "code", "metadata": { "id": "UUZoxGUMkF0_" }, "execution_count": null, "outputs": [], "source": "%%writefile qapu_report.py\n# -*- coding: utf-8 -*-\n\"\"\"Grafik üretimi + rapor dosyaları (MD/HTML) + paketleme.\"\"\"\nimport os, base64, html, zipfile\nimport numpy as np\nimport matplotlib\nmatplotlib.use(\"Agg\")\nimport matplotlib.pyplot as plt\n\nimport qapu_narrator as nar\nfrom qapu_pm import CONFIG\n\nplt.rcParams.update({\"figure.dpi\": 110, \"font.size\": 9, \"axes.grid\": True,\n \"grid.alpha\": 0.3, \"axes.spines.top\": False, \"axes.spines.right\": False})\n\nRISK_COLORS = {\"İYİ\": \"#2e7d32\", \"İZLENMELİ\": \"#f9a825\", \"RİSKLİ\": \"#ef6c00\", \"KRİTİK\": \"#c62828\"}\n\n\ndef fleet_chart(results, path):\n rs = sorted(results.values(), key=lambda r: r[\"score\"])\n fig, ax = plt.subplots(figsize=(8, max(3, 0.35 * len(rs))))\n names = [r[\"name\"][:28] for r in rs]\n scores = [r[\"score\"] for r in rs]\n colors = [RISK_COLORS[r[\"risk\"]] for r in rs]\n ax.barh(names, scores, color=colors)\n for y, s in enumerate(scores):\n ax.text(s + 1, y, f\"{s:.0f}\", va=\"center\", fontsize=8)\n ax.axvline(70, color=\"#999\", ls=\"--\", lw=1)\n ax.axvline(85, color=\"#999\", ls=\":\", lw=1)\n ax.set_xlim(0, 108)\n ax.set_xlabel(\"Sağlık skoru (0–100)\")\n ax.set_title(\"Filo Sağlık Durumu — cihaz bazında\")\n fig.tight_layout()\n fig.savefig(path)\n plt.close(fig)\n\n\ndef device_chart(daily, dev_id, r, path, cfg=CONFIG):\n sub = daily[daily[\"device_id\"] == dev_id].sort_values(\"device_time\")\n if len(sub) < 2:\n return False\n t = sub[\"device_time\"]\n fig, axes = plt.subplots(2, 2, figsize=(10, 6), sharex=True)\n fig.suptitle(f\"{r['name']} — günlük göstergeler\", fontsize=11)\n\n ax = axes[0, 0]\n ax.plot(t, sub[\"ind_ratio\"] * 100, \"o-\", ms=3, color=\"#c62828\", label=\"Endüktif\")\n ax.plot(t, sub[\"cap_ratio\"] * 100, \"s-\", ms=3, color=\"#1565c0\", label=\"Kapasitif\")\n ax.axhline(cfg[\"ind_limit\"] * 100, color=\"#c62828\", ls=\"--\", lw=1)\n ax.axhline(cfg[\"cap_limit\"] * 100, color=\"#1565c0\", ls=\":\", lw=1)\n ax.set_title(\"Reaktif/Aktif oran (%) ve EPDK sınırları\")\n ax.legend(fontsize=7)\n\n ax = axes[0, 1]\n if \"pf_loaded\" in sub:\n ax.plot(t, sub[\"pf_loaded\"], \"o-\", ms=3, color=\"#2e7d32\")\n ax.axhline(cfg[\"pf_target\"], color=\"#999\", ls=\"--\", lw=1)\n ax.set_ylim(0, 1.05)\n ax.set_title(\"Güç faktörü (yük altında)\")\n\n ax = axes[1, 0]\n if \"thd_i\" in sub:\n ax.plot(t, sub[\"thd_i\"] * 100, \"o-\", ms=3, color=\"#6a1b9a\", label=\"THD-I\")\n if \"thd_v\" in sub:\n ax.plot(t, sub[\"thd_v\"] * 100, \"s-\", ms=3, color=\"#00838f\", label=\"THD-V\")\n ax.axhline(cfg[\"thd_i_limit\"] * 100, color=\"#6a1b9a\", ls=\"--\", lw=1)\n ax.set_title(\"Harmonik bozulma (%)\")\n ax.legend(fontsize=7)\n\n ax = axes[1, 1]\n if \"i_imb\" in sub:\n ax.plot(t, sub[\"i_imb\"] * 100, \"o-\", ms=3, color=\"#ef6c00\", label=\"Akım\")\n if \"v_imb\" in sub:\n ax.plot(t, sub[\"v_imb\"] * 100, \"s-\", ms=3, color=\"#455a64\", label=\"Gerilim\")\n ax.axhline(cfg[\"i_imb_limit\"] * 100, color=\"#ef6c00\", ls=\"--\", lw=1)\n ax.set_title(\"Faz dengesizliği (%)\")\n ax.legend(fontsize=7)\n\n for ax in axes.flat:\n ax.tick_params(axis=\"x\", rotation=30)\n fig.tight_layout()\n fig.savefig(path)\n plt.close(fig)\n return True\n\n\n# ------------------------------------------------- basit MD → HTML çevirici\ndef _md_to_html(md):\n try:\n import markdown\n return markdown.markdown(md, extensions=[\"tables\"])\n except Exception:\n return \"
\" + html.escape(md) + \"
\"\n\n\n_HTML_TMPL = \"\"\"\n{title}\n{body}\n

Bu rapor, enerji analizörü verilerinden offline narrator ile tamamen\nçevrimdışı/deterministik olarak üretilmiştir (LLM kullanılmamıştır).

\"\"\"\n\n\ndef _embed_img(path):\n with open(path, \"rb\") as fh:\n b64 = base64.b64encode(fh.read()).decode()\n return f'\"grafik\"'\n\n\ndef generate_reports(results, daily, outdir=\"raporlar\", audiences=None, top_device_charts=8):\n \"\"\"Tüm kitleler için filo + cihaz raporlarını MD ve HTML olarak üretir; zip döner.\"\"\"\n audiences = audiences or nar.AUDIENCES\n os.makedirs(outdir, exist_ok=True)\n chart_dir = os.path.join(outdir, \"grafikler\")\n os.makedirs(chart_dir, exist_ok=True)\n\n fleet_png = os.path.join(chart_dir, \"filo_saglik.png\")\n fleet_chart(results, fleet_png)\n\n rs_sorted = sorted(results.values(), key=lambda r: r[\"score\"])\n dev_pngs = {}\n for r in rs_sorted[:top_device_charts]:\n p = os.path.join(chart_dir, f\"cihaz_{r['device_id']}.png\")\n if device_chart(daily, r[\"device_id\"], r, p):\n dev_pngs[r[\"device_id\"]] = p\n\n written = []\n for aud in audiences:\n md = [nar.fleet_report(results, aud)]\n html_parts = [_md_to_html(md[0]), _embed_img(fleet_png)]\n for r in rs_sorted:\n sec = nar.device_report(r, aud)\n md.append(\"\\n---\\n\\n\" + sec)\n html_parts.append(\"
\" + _md_to_html(sec))\n if r[\"device_id\"] in dev_pngs and aud != \"vatandas\":\n html_parts.append(_embed_img(dev_pngs[r[\"device_id\"]]))\n base = os.path.join(outdir, f\"rapor_{aud}\")\n with open(base + \".md\", \"w\") as fh:\n fh.write(\"\\n\".join(md))\n title = nar.AUDIENCE_META[aud][\"baslik\"]\n with open(base + \".html\", \"w\") as fh:\n fh.write(_HTML_TMPL.format(title=title, body=\"\\n\".join(html_parts)))\n written += [base + \".md\", base + \".html\"]\n\n zpath = os.path.join(outdir, \"qapu_raporlar.zip\")\n with zipfile.ZipFile(zpath, \"w\", zipfile.ZIP_DEFLATED) as z:\n for p in written + [fleet_png] + list(dev_pngs.values()):\n z.write(p, os.path.relpath(p, outdir))\n return zpath, written\n", "id": "UUZoxGUMkF0_" }, { "cell_type": "code", "metadata": { "id": "_M-MqlZ_kF0_" }, "execution_count": null, "outputs": [], "source": "%%writefile qapu_docx.py\n# -*- coding: utf-8 -*-\n\"\"\"\nQAPU — Profesyonel Word (.docx) rapor üretici\nKapak sayfası, KPI özeti, kurumsal renkli tablolar, risk rozetleri,\ngömülü grafikler ve sayfa numaralı alt bilgi ile.\n\"\"\"\nimport os\nimport numpy as np\nfrom docx import Document\nfrom docx.shared import Pt, Cm, RGBColor\nfrom docx.enum.text import WD_ALIGN_PARAGRAPH\nfrom docx.enum.table import WD_TABLE_ALIGNMENT\nfrom docx.oxml.ns import qn\nfrom docx.oxml import OxmlElement\n\nimport qapu_narrator as nar\nimport qapu_report as rep\n\n# ----------------------------------------------------------- kurumsal palet\nNAVY = RGBColor(0x1F, 0x38, 0x64)\nBLUE = RGBColor(0x2E, 0x74, 0xB5)\nGRAY = RGBColor(0x6B, 0x6B, 0x6B)\nLIGHT = \"F2F5FA\" # zebra satır dolgusu (hex, # yok)\nRISK_HEX = {\"İYİ\": \"2E7D32\", \"İZLENMELİ\": \"F9A825\", \"RİSKLİ\": \"EF6C00\", \"KRİTİK\": \"C62828\"}\nSEV_HEX = {\"yüksek\": \"C62828\", \"orta\": \"EF6C00\", \"düşük\": \"F9A825\", \"bilgi\": \"6B6B6B\"}\n\n\n# ------------------------------------------------------------- xml yardımcıları\ndef _shade(cell, hexcolor):\n tcPr = cell._tc.get_or_add_tcPr()\n shd = OxmlElement(\"w:shd\")\n shd.set(qn(\"w:val\"), \"clear\")\n shd.set(qn(\"w:fill\"), hexcolor)\n tcPr.append(shd)\n\n\ndef _cell_text(cell, text, bold=False, color=None, size=9, align=None):\n p = cell.paragraphs[0]\n p.text = \"\"\n r = p.add_run(text)\n r.bold = bold\n r.font.size = Pt(size)\n if color:\n r.font.color.rgb = color\n if align:\n p.alignment = align\n return p\n\n\ndef _page_number_footer(section, left_text):\n foot = section.footer\n p = foot.paragraphs[0]\n p.text = \"\"\n r = p.add_run(left_text + \" · Sayfa \")\n r.font.size = Pt(8)\n r.font.color.rgb = GRAY\n fld = OxmlElement(\"w:fldSimple\")\n fld.set(qn(\"w:instr\"), \"PAGE\")\n rpr = OxmlElement(\"w:rPr\")\n sz = OxmlElement(\"w:sz\"); sz.set(qn(\"w:val\"), \"16\")\n rpr.append(sz)\n fld.append(rpr)\n p._p.append(fld)\n\n\ndef _hr(par):\n pPr = par._p.get_or_add_pPr()\n pbdr = OxmlElement(\"w:pBdr\")\n b = OxmlElement(\"w:bottom\")\n b.set(qn(\"w:val\"), \"single\"); b.set(qn(\"w:sz\"), \"8\")\n b.set(qn(\"w:space\"), \"1\"); b.set(qn(\"w:color\"), \"1F3864\")\n pbdr.append(b)\n pPr.append(pbdr)\n\n\ndef _styled_table(doc, header, rows, widths_cm, risk_col=None, zebra=True):\n t = doc.add_table(rows=1 + len(rows), cols=len(header))\n t.style = \"Table Grid\"\n t.alignment = WD_TABLE_ALIGNMENT.CENTER\n for j, h in enumerate(header):\n c = t.rows[0].cells[j]\n _cell_text(c, h, bold=True, color=RGBColor(0xFF, 0xFF, 0xFF), size=9)\n _shade(c, \"1F3864\")\n for i, row in enumerate(rows, start=1):\n for j, val in enumerate(row):\n c = t.rows[i].cells[j]\n _cell_text(c, str(val), size=9)\n if zebra and i % 2 == 0:\n _shade(c, LIGHT)\n if risk_col is not None and j == risk_col and str(val) in RISK_HEX:\n _shade(c, RISK_HEX[str(val)])\n _cell_text(c, str(val), bold=True, color=RGBColor(0xFF, 0xFF, 0xFF), size=9,\n align=WD_ALIGN_PARAGRAPH.CENTER)\n for j, w in enumerate(widths_cm):\n for row in t.rows:\n row.cells[j].width = Cm(w)\n return t\n\n\ndef _heading(doc, text, level=1, color=NAVY, rule=False):\n h = doc.add_heading(\"\", level=level)\n r = h.add_run(text)\n r.font.color.rgb = color\n if rule:\n _hr(h)\n return h\n\n\n# --------------------------------------------------------------- kapak sayfası\ndef _cover(doc, aud, results, customer=None):\n meta = nar.AUDIENCE_META[aud]\n rs = list(results.values())\n first = min(r[\"first\"] for r in rs)\n last = max(r[\"last\"] for r in rs)\n krit = sum(1 for r in rs if r[\"risk\"] == \"KRİTİK\")\n risk = sum(1 for r in rs if r[\"risk\"] == \"RİSKLİ\")\n reaktif = sum(1 for r in rs if any(f[\"code\"].startswith(\"REAKTIF\") and f[\"code\"].endswith(\"ASIM\")\n for f in r[\"findings\"]))\n kwh = sum(r[\"ae_total_kwh\"] for r in rs)\n\n for _ in range(5):\n doc.add_paragraph()\n p = doc.add_paragraph()\n p.alignment = WD_ALIGN_PARAGRAPH.CENTER\n r = p.add_run(\"QAPU ENERJİ İZLEME\")\n r.font.size = Pt(14); r.font.color.rgb = BLUE; r.bold = True\n\n p = doc.add_paragraph()\n p.alignment = WD_ALIGN_PARAGRAPH.CENTER\n r = p.add_run(\"Reaktif Enerji ve Kestirimci Bakım Raporu\")\n r.font.size = Pt(26); r.font.color.rgb = NAVY; r.bold = True\n\n if customer:\n p = doc.add_paragraph()\n p.alignment = WD_ALIGN_PARAGRAPH.CENTER\n r = p.add_run(customer[\"ad\"])\n r.font.size = Pt(16); r.font.color.rgb = NAVY; r.bold = True\n if customer.get(\"birim\"):\n p = doc.add_paragraph()\n p.alignment = WD_ALIGN_PARAGRAPH.CENTER\n r = p.add_run(customer[\"birim\"])\n r.font.size = Pt(12); r.font.color.rgb = GRAY\n\n p = doc.add_paragraph()\n p.alignment = WD_ALIGN_PARAGRAPH.CENTER\n r = p.add_run(meta[\"baslik\"])\n r.font.size = Pt(15); r.font.color.rgb = GRAY\n\n p = doc.add_paragraph()\n p.alignment = WD_ALIGN_PARAGRAPH.CENTER\n r = p.add_run(f\"Veri dönemi: {first} – {last}\")\n r.font.size = Pt(11); r.font.color.rgb = GRAY\n\n doc.add_paragraph(); doc.add_paragraph()\n\n # KPI şeridi\n kpis = [(f\"{len(rs)}\", \"İzlenen Tesis\"),\n (f\"{nar.num(kwh,0)}\", \"Toplam kWh\"),\n (f\"{krit + risk}\", \"Kritik/Riskli Tesis\"),\n (f\"{reaktif}\", \"Reaktif Sınır Aşımı\")]\n t = doc.add_table(rows=2, cols=4)\n t.alignment = WD_TABLE_ALIGNMENT.CENTER\n for j, (val, lab) in enumerate(kpis):\n c = t.rows[0].cells[j]\n _cell_text(c, val, bold=True, color=RGBColor(0xFF, 0xFF, 0xFF), size=20,\n align=WD_ALIGN_PARAGRAPH.CENTER)\n _shade(c, \"1F3864\" if j % 2 == 0 else \"2E74B5\")\n c2 = t.rows[1].cells[j]\n _cell_text(c2, lab, size=9, color=GRAY, align=WD_ALIGN_PARAGRAPH.CENTER)\n _shade(c2, \"F2F5FA\")\n for row in t.rows:\n row.cells[j].width = Cm(4.1)\n\n for _ in range(6):\n doc.add_paragraph()\n p = doc.add_paragraph()\n p.alignment = WD_ALIGN_PARAGRAPH.CENTER\n r = p.add_run(\"Bu rapor, enerji analizörü verilerinden offline narrator ile tamamen \"\n \"çevrimdışı ve deterministik olarak üretilmiştir (LLM kullanılmamıştır).\")\n r.font.size = Pt(8); r.italic = True; r.font.color.rgb = GRAY\n doc.add_page_break()\n\n\n# -------------------------------------------------------------- filo bölümü\ndef _fleet_section(doc, results, aud, fleet_png):\n rs = sorted(results.values(), key=lambda r: r[\"score\"])\n _heading(doc, \"1. Filo Genel Durumu\", level=1, rule=True)\n\n krit = [r for r in rs if r[\"risk\"] == \"KRİTİK\"]\n risk = [r for r in rs if r[\"risk\"] == \"RİSKLİ\"]\n izle = [r for r in rs if r[\"risk\"] == \"İZLENMELİ\"]\n iyi = [r for r in rs if r[\"risk\"] == \"İYİ\"]\n\n if aud == \"vatandas\":\n p = doc.add_paragraph()\n p.add_run(f\"İzlenen {len(rs)} tesisin {len(iyi)} tanesi iyi durumda, {len(izle)} tanesi \"\n f\"yakından izleniyor, {len(risk)+len(krit)} tanesinde bakım planlaması yapılıyor. \"\n \"Amaç: arızaları olmadan önce yakalamak, kesintisiz hizmet ve gereksiz \"\n \"masrafların önlenmesi.\")\n else:\n p = doc.add_paragraph()\n for lab, grp, hx in [(\"KRİTİK\", krit, \"C62828\"), (\"RİSKLİ\", risk, \"EF6C00\"),\n (\"İZLENMELİ\", izle, \"F9A825\"), (\"İYİ\", iyi, \"2E7D32\")]:\n r = p.add_run(f\" ● {lab}: {len(grp)} \")\n r.font.color.rgb = RGBColor(int(hx[0:2], 16), int(hx[2:4], 16), int(hx[4:6], 16))\n r.bold = True\n\n if os.path.exists(fleet_png):\n doc.add_picture(fleet_png, width=Cm(16.5))\n\n reaktif = [r for r in rs if any(f[\"code\"].startswith(\"REAKTIF\") and f[\"code\"].endswith(\"ASIM\")\n for f in r[\"findings\"])]\n if reaktif and aud != \"vatandas\":\n p = doc.add_paragraph()\n msg = (f\"Reaktif sınır aşımı olan {len(reaktif)} tesis: \" if aud == \"teknik\" else\n f\"Reaktif ceza riski taşıyan {len(reaktif)} tesis var — kompanzasyon bakımı ile \"\n f\"tamamen önlenebilir bir fatura kalemidir: \")\n r = p.add_run(msg)\n r.bold = True\n p.add_run(\", \".join(x[\"name\"] for x in reaktif) + \".\")\n\n if aud != \"vatandas\":\n header = [\"Tesis\", \"Skor\", \"Durum\", \"End. oran\", \"PF\", \"Öncelikli bulgu\"]\n rows = []\n for r in rs:\n top = r[\"findings\"][0][\"code\"].replace(\"_\", \" \") if r[\"findings\"] else \"—\"\n rows.append([r[\"name\"], f\"{r['score']:.0f}\", r[\"risk\"],\n nar.pct(r.get(\"ind_ratio_30g\")), nar.num(r.get(\"pf_loaded\")), top])\n _styled_table(doc, header, rows, [5.2, 1.4, 2.4, 2.0, 1.6, 4.0], risk_col=2)\n else:\n onc = krit + risk\n if onc:\n p = doc.add_paragraph()\n p.add_run(\"Bakım planlanan tesisler: \").bold = True\n p.add_run(\", \".join(r[\"name\"] for r in onc) + \".\")\n\n onc = krit + risk\n if onc and aud != \"vatandas\":\n p = doc.add_paragraph()\n p.add_run(\"Önerilen öncelik sırası: \").bold = True\n p.add_run(\" → \".join(f\"{i+1}) {r['name']}\" for i, r in enumerate(onc[:5])))\n doc.add_page_break()\n\n\n# -------------------------------------------------------------- cihaz bölümü\ndef _device_section(doc, r, idx, aud, chart_png=None):\n h = _heading(doc, f\"{idx}. {r['name']}\", level=1, rule=True)\n badge = h.add_run(f\" {r['risk']}\")\n hx = RISK_HEX[r[\"risk\"]]\n badge.font.color.rgb = RGBColor(int(hx[0:2], 16), int(hx[2:4], 16), int(hx[4:6], 16))\n badge.font.size = Pt(12)\n\n p = doc.add_paragraph()\n rr = p.add_run(f\"Dönem: {r['first']} – {r['last']} ({r['days']} gün) · \"\n f\"Sağlık skoru: {r['score']:.0f}/100 · \"\n f\"Tüketim: {nar.num(r['ae_total_kwh'],0,'kWh')}\")\n rr.font.size = Pt(9); rr.font.color.rgb = GRAY\n\n if aud == \"vatandas\":\n durum = {\"İYİ\": \"iyi durumda\", \"İZLENMELİ\": \"izlenmesi gereken\",\n \"RİSKLİ\": \"riskli\", \"KRİTİK\": \"kritik\"}[r[\"risk\"]]\n doc.add_paragraph(f\"Bu tesis şu anda {durum} olarak değerlendiriliyor \"\n f\"(100 üzerinden {int(r['score'])} puan).\")\n elif aud == \"teknik\":\n header = [\"Metrik\", \"Değer\", \"Eşik\"]\n rows = [[\"Endüktif oran (30g)\", nar.pct(r.get(\"ind_ratio_30g\")), \"%20 (EPDK)\"],\n [\"Kapasitif oran (30g)\", nar.pct(r.get(\"cap_ratio_30g\")), \"%15 (EPDK)\"],\n [\"Güç faktörü (yükte)\", nar.num(r.get(\"pf_loaded\")), \"≥0,95\"],\n [\"THD-I / THD-V\", f\"{nar.pct(r.get('thd_i'))} / {nar.pct(r.get('thd_v'))}\", \"%15 / %8\"],\n [\"Akım / gerilim dengesizliği\", f\"{nar.pct(r.get('i_imb'))} / {nar.pct(r.get('v_imb'))}\", \"%10 / %2\"]]\n if np.isfinite(r.get(\"anomaly_ratio\") or np.nan):\n rows.append([\"Anomali oranı (IForest)\", nar.pct(r[\"anomaly_ratio\"]), \"%12\"])\n _styled_table(doc, header, rows, [6.5, 5.0, 4.0])\n doc.add_paragraph()\n\n if chart_png and aud != \"vatandas\" and os.path.exists(chart_png):\n doc.add_picture(chart_png, width=Cm(16.5))\n\n if r[\"findings\"]:\n p = doc.add_paragraph()\n p.add_run(\"Bulgular\" if aud != \"vatandas\" else \"Tespit edilenler\").bold = True\n for f in sorted(r[\"findings\"], key=lambda x: {\"yüksek\": 0, \"orta\": 1, \"düşük\": 2, \"bilgi\": 3}[x[\"severity\"]]):\n text = nar._finding_text(f, r, aud)\n par = doc.add_paragraph(style=\"List Bullet\")\n m = text.split(\"]\", 1)\n if text.startswith(\"[\") and len(m) == 2 and aud != \"vatandas\":\n tag = par.add_run(m[0] + \"]\")\n hx = SEV_HEX[f[\"severity\"]]\n tag.font.color.rgb = RGBColor(int(hx[0:2], 16), int(hx[2:4], 16), int(hx[4:6], 16))\n tag.bold = True\n par.add_run(m[1])\n else:\n par.add_run(text)\n else:\n doc.add_paragraph(\"Önemli bir bulgu yok; rutin izleme sürüyor.\")\n\n recs = nar._recs_for(r[\"findings\"], aud)\n if recs:\n p = doc.add_paragraph()\n p.add_run(\"Önerilen aksiyonlar\" if aud != \"vatandas\" else \"Ne yapılacak?\").bold = True\n for rec in recs:\n doc.add_paragraph(rec, style=\"List Number\")\n\n\n# ------------------------------------------------------------------- ana giriş\ndef build_docx(results, daily, aud, out_path, chart_dir, customer=None):\n doc = Document()\n st = doc.styles[\"Normal\"]\n st.font.name = \"Calibri\"\n st.font.size = Pt(10.5)\n for lvl in [\"Heading 1\", \"Heading 2\"]:\n doc.styles[lvl].font.color.rgb = NAVY\n\n sec = doc.sections[0]\n sec.top_margin = sec.bottom_margin = Cm(2.0)\n sec.left_margin = sec.right_margin = Cm(2.2)\n kurum = (customer[\"ad\"].split(\"(\")[-1].rstrip(\")\") if customer and \"(\" in customer[\"ad\"]\n else (customer[\"ad\"] if customer else \"QAPU Enerji İzleme\"))\n _page_number_footer(sec, f\"{kurum} · {nar.AUDIENCE_META[aud]['baslik']}\")\n\n _cover(doc, aud, results, customer=customer)\n fleet_png = os.path.join(chart_dir, \"filo_saglik.png\")\n _fleet_section(doc, results, aud, fleet_png)\n\n rs_sorted = sorted(results.values(), key=lambda r: r[\"score\"])\n for i, r in enumerate(rs_sorted, start=2):\n png = os.path.join(chart_dir, f\"cihaz_{r['device_id']}.png\")\n _device_section(doc, r, i, aud, chart_png=png if os.path.exists(png) else None)\n\n doc.save(out_path)\n return out_path\n\n\ndef generate_docx_reports(results, daily, outdir=\"raporlar\", audiences=None, top_device_charts=8):\n \"\"\"4 kitle için profesyonel .docx raporları üretir; dosya listesi döner.\"\"\"\n audiences = audiences or nar.AUDIENCES\n os.makedirs(outdir, exist_ok=True)\n chart_dir = os.path.join(outdir, \"grafikler\")\n os.makedirs(chart_dir, exist_ok=True)\n\n fleet_png = os.path.join(chart_dir, \"filo_saglik.png\")\n if not os.path.exists(fleet_png):\n rep.fleet_chart(results, fleet_png)\n rs_sorted = sorted(results.values(), key=lambda r: r[\"score\"])\n for r in rs_sorted[:top_device_charts]:\n p = os.path.join(chart_dir, f\"cihaz_{r['device_id']}.png\")\n if not os.path.exists(p):\n rep.device_chart(daily, r[\"device_id\"], r, p)\n\n return [build_docx(results, daily, aud, os.path.join(outdir, f\"rapor_{aud}.docx\"), chart_dir)\n for aud in audiences]\n\n\ndef generate_customer_reports(results, daily, outdir=\"raporlar_musteri\",\n include_test=False, top_device_charts=20):\n \"\"\"Müşteri bazında rapor setleri üretir: //rapor_.docx\n Kitle seçimi müşteri türüne göre: kamu → yonetici_kamu, özel → yonetici_ozel;\n teknik ve vatandaş raporu her müşteri için üretilir.\"\"\"\n import qapu_customers as qc\n os.makedirs(outdir, exist_ok=True)\n gruplar = qc.split_by_customer(results, include_test=include_test)\n written = {}\n for kod, sub_results in gruplar.items():\n cust = qc.CUSTOMERS[kod]\n cdir = os.path.join(outdir, kod)\n chart_dir = os.path.join(cdir, \"grafikler\")\n os.makedirs(chart_dir, exist_ok=True)\n rep.fleet_chart(sub_results, os.path.join(chart_dir, \"filo_saglik.png\"))\n rs_sorted = sorted(sub_results.values(), key=lambda r: r[\"score\"])\n for r in rs_sorted[:top_device_charts]:\n p = os.path.join(chart_dir, f\"cihaz_{r['device_id']}.png\")\n rep.device_chart(daily, r[\"device_id\"], r, p)\n auds = [\"teknik\", \"vatandas\",\n \"yonetici_kamu\" if cust[\"tur\"] == \"kamu\" else \"yonetici_ozel\"]\n if cust[\"tur\"] == \"ic\":\n auds = [\"teknik\"]\n files = [build_docx(sub_results, daily, aud,\n os.path.join(cdir, f\"rapor_{aud}.docx\"),\n chart_dir, customer=cust)\n for aud in auds]\n written[kod] = files\n return written\n", "id": "_M-MqlZ_kF0_" }, { "cell_type": "code", "metadata": { "id": "adcw44mnkF1A" }, "execution_count": null, "outputs": [], "source": "%%writefile qapu_customers.py\n# -*- coding: utf-8 -*-\n\"\"\"Cihaz → müşteri eşleştirmesi (Cinga AI kurum kaydına göre, 31.10.2026).\"\"\"\n\nCUSTOMERS = {\n \"KOSKI\": {\n \"ad\": \"Konya Büyükşehir Belediyesi Su ve Kanalizasyon İdaresi (KOSKİ)\",\n \"birim\": \"Su Tesisleri Dairesi Başkanlığı\",\n \"tur\": \"kamu\",\n },\n \"KOYUNCU\": {\n \"ad\": \"Koyuncu A.Ş.\",\n \"birim\": \"\",\n \"tur\": \"ozel\",\n },\n \"KOP\": {\n \"ad\": \"KOP (Konya Ovası Projesi)\",\n \"birim\": \"\",\n \"tur\": \"kamu\",\n },\n \"TEST\": {\n \"ad\": \"Test / Kurum Atanmamış Cihazlar\",\n \"birim\": \"\",\n \"tur\": \"ic\",\n },\n}\n\nDEVICE_CUSTOMER = {\n # KOSKİ — 15 cihaz (tümü pompa)\n \"DE000000F6191870\": \"KOSKI\", # Ali Taşoluk\n \"4E000000EAD39270\": \"KOSKI\", # Yeşildere 2\n \"1A000000F6356F70\": \"KOSKI\", # Seyrantepe 2\n \"6E000000F5F23770\": \"KOSKI\", # Eğribayat Kuyu\n \"75000000F5FCEB70\": \"KOSKI\", # Çatalhöyük 3\n \"C6000000F6285770\": \"KOSKI\", # Hayvan Pazarı\n \"4B000000F5FA7570\": \"KOSKI\", # Yeşilbahçe 1\n \"28000000F6231270\": \"KOSKI\", # Hocacihan Sarayköy\n \"DD000000F6352970\": \"KOSKI\", # Hocacihan Sarayköy Pompa\n \"64000000C466EF70\": \"KOSKI\", # Meram 2\n \"45000000F621B470\": \"KOSKI\", # Akçalar\n \"18000000F5DB3B70\": \"KOSKI\", # Orman Bölge\n \"A6000000F6117F70\": \"KOSKI\", # Karaaslan Hobi 2\n \"5A000000F63DF570\": \"KOSKI\", # 100.Yıl\n \"BE000000F64BCE70\": \"KOSKI\", # Şehir Parkı 2\n # Koyuncu A.Ş. — 2 cihaz\n \"01000000C39AB270\": \"KOYUNCU\", # Koyuncu Klor Alkali OG (Endüstriyel)\n \"4C000000C3A62170\": \"KOYUNCU\", # Girayhan-3 GES OG (GES)\n # KOP\n \"DD000000C41F7670\": \"KOP\", # KOP - Alibeyhüyüğü Pompası\n # Test / atanmamış\n \"400000011D081B70\": \"TEST\", # Recep Test Cihazı\n \"C5000000C4965F70\": \"TEST\", # Sait Test\n \"CB000000F60B9870\": \"TEST\", # Cinga 01 (Recep)\n \"1000000000000001\": \"TEST\", # Qapu-01\n \"1000000000000002\": \"TEST\", # Qapu-02\n \"1000000000000003\": \"TEST\", # Qapu-03\n \"1000000000000004\": \"TEST\", # Qapu-04\n \"1000000000000005\": \"TEST\", # Qapu-05\n}\n\n\ndef customer_of(device_id):\n return DEVICE_CUSTOMER.get(str(device_id), \"TEST\")\n\n\ndef split_by_customer(results, include_test=False):\n \"\"\"analyze() çıktısını müşteri koduna göre böler: {musteri_kodu: results_dict}.\"\"\"\n out = {}\n for dev, r in results.items():\n c = customer_of(dev)\n if c == \"TEST\" and not include_test:\n continue\n out.setdefault(c, {})[dev] = r\n return out\n", "id": "adcw44mnkF1A" }, { "cell_type": "code", "metadata": { "id": "f2797v9akF1A" }, "execution_count": null, "outputs": [], "source": "%%writefile qapu_dbload.py\n# -*- coding: utf-8 -*-\n\"\"\"Canlı PostgreSQL'den analiz tablolarını çeker (dump gerekmez).\nYalnızca analiz için gereken kolonlar, istenen gün aralığıyla sorgulanır —\npartisyonlu tablolarda ana tabloyu sorgulamak tüm partisyonları kapsar.\"\"\"\nimport urllib.parse\nimport pandas as pd\nfrom sqlalchemy import create_engine, text\n\n# build_daily()'nin ihtiyaç duyduğu kolonlar\nMEAS_TABLES = {\n \"measurements_energy\": [\"ae_r\", \"ae_s\", \"ae_t\", \"re_l_r\", \"re_l_s\", \"re_l_t\",\n \"re_g_r\", \"re_g_s\", \"re_g_t\"],\n \"measurements_power\": [\"s_r\", \"s_s\", \"s_t\", \"pf_r\", \"pf_s\", \"pf_t\", \"fq\"],\n \"measurements_voltage\": [\"vrms_r\", \"vrms_s\", \"vrms_t\",\n \"vfund_r\", \"vfund_s\", \"vfund_t\"] +\n [f\"vharm_{p}_{h}\" for p in \"rst\" for h in (3, 5, 7, 9)],\n \"measurements_current\": [\"irms_r\", \"irms_s\", \"irms_t\",\n \"ifund_r\", \"ifund_s\", \"ifund_t\"] +\n [f\"iharm_{p}_{h}\" for p in \"rst\" for h in (3, 5, 7, 9)],\n \"measurements_environment\": [\"pcb_t\"],\n \"measurements_device\": [\"rssi\", \"b_soc\"],\n}\nBASE_COLS = [\"device_id\", \"device_time\", \"is_valid\"]\n\n\ndef make_engine(host, port, dbname, user, password, sslmode=\"prefer\"):\n url = (f\"postgresql+psycopg2://{urllib.parse.quote_plus(user)}:\"\n f\"{urllib.parse.quote_plus(password)}@{host}:{port}/{dbname}\"\n f\"?sslmode={sslmode}\")\n return create_engine(url, pool_pre_ping=True,\n connect_args={\"options\": \"-c statement_timeout=120000 \"\n \"-c default_transaction_read_only=on\"})\n\n\ndef load_db(engine, days=120, verbose=True):\n \"\"\"Analiz için gereken tabloları DataFrame sözlüğü olarak döner.\"\"\"\n tabs = {}\n with engine.connect() as con:\n for table, cols in MEAS_TABLES.items():\n collist = \", \".join(BASE_COLS + cols)\n q = text(f\"SELECT {collist} FROM {table} \"\n f\"WHERE device_time >= now() - (:d || ' days')::interval \"\n f\"AND is_valid\")\n try:\n df = pd.read_sql(q, con, params={\"d\": str(int(days))})\n except Exception as e:\n if verbose:\n print(f\" ! {table} okunamadı: {str(e).splitlines()[0]}\")\n con.rollback() if hasattr(con, \"rollback\") else None\n continue\n df[\"device_time\"] = pd.to_datetime(df[\"device_time\"], utc=True)\n tabs[table] = df\n if verbose:\n print(f\" {table}: {len(df):,} satır\")\n try:\n tabs[\"devices\"] = pd.read_sql(text(\"SELECT id, name FROM devices\"), con)\n if verbose:\n print(f\" devices: {len(tabs['devices'])} cihaz\")\n except Exception as e:\n if verbose:\n print(\" ! devices okunamadı:\", str(e).splitlines()[0])\n return tabs\n", "id": "f2797v9akF1A" }, { "cell_type": "code", "metadata": { "id": "YWbelFFRkF1B" }, "execution_count": null, "outputs": [], "source": "%%writefile qapu_chatbot.py\n# -*- coding: utf-8 -*-\n\"\"\"\nQAPU Veritabanı Sohbet Botu — çekirdek\n=====================================\nDoğal dildeki soruyu LLM ile SQL'e çevirir, korumalı şekilde (yalnızca SELECT,\notomatik LIMIT, zaman aşımı, salt-okunur işlem) PostgreSQL'de çalıştırır ve\nsonucu Türkçe özetler. LLM arka ucu takılabilir (Cerebras / Gemini / OpenAI\nuyumlu / yerel model); veritabanı kimlik bilgileri koda yazılmaz.\n\"\"\"\nimport re, json, decimal, datetime\nimport sqlglot\nfrom sqlglot import exp\n\nMAX_ROWS = 500 # otomatik LIMIT\nSTMT_TIMEOUT_MS = 20000 # sorgu zaman aşımı\nMAX_RETRY = 2 # hatalı SQL'de LLM'e yeniden deneme hakkı\n\n\n# ---------------------------------------------------------------- SQL GUARD\nclass GuardError(Exception):\n pass\n\n\ndef guard_sql(sql: str) -> str:\n \"\"\"Yalnızca tek bir SELECT/WITH ifadesine izin verir; LIMIT yoksa ekler.\"\"\"\n sql = sql.strip().rstrip(\";\").strip()\n if not sql:\n raise GuardError(\"Boş SQL\")\n try:\n stmts = sqlglot.parse(sql, read=\"postgres\")\n except Exception as e:\n raise GuardError(f\"SQL ayrıştırılamadı: {e}\")\n if len(stmts) != 1:\n raise GuardError(\"Tek bir sorguya izin var\")\n tree = stmts[0]\n root = tree\n while isinstance(root, exp.With):\n root = root.this\n if not isinstance(root, (exp.Select, exp.Union, exp.Intersect, exp.Except)):\n raise GuardError(\"Yalnızca SELECT sorgularına izin var\")\n # yazma / DDL / tehlikeli düğüm taraması\n banned = (exp.Insert, exp.Update, exp.Delete, exp.Drop, exp.Create,\n exp.Alter, exp.Merge, exp.Command, exp.Grant, exp.TruncateTable)\n for node in tree.walk():\n if isinstance(node, banned):\n raise GuardError(f\"İzin verilmeyen ifade: {type(node).__name__}\")\n if isinstance(node, exp.Anonymous) and node.name and \\\n node.name.lower() in {\"pg_sleep\", \"dblink\", \"pg_read_file\", \"lo_import\", \"lo_export\"}:\n raise GuardError(f\"İzin verilmeyen fonksiyon: {node.name}\")\n # LIMIT enjeksiyonu (en dış seviyeye)\n if not tree.args.get(\"limit\") and not (isinstance(root, exp.Select) and root.args.get(\"limit\")):\n sql = f\"SELECT * FROM ({sql}) AS _guarded LIMIT {MAX_ROWS}\"\n return sql\n\n\n# ------------------------------------------------------------------ VERİTABANI\nclass DB:\n def __init__(self, host, port, dbname, user, password, sslmode=\"prefer\"):\n import psycopg2\n self.conn = psycopg2.connect(host=host, port=port, dbname=dbname,\n user=user, password=password,\n sslmode=sslmode,\n options=f\"-c statement_timeout={STMT_TIMEOUT_MS} \"\n f\"-c default_transaction_read_only=on\")\n self.conn.autocommit = True\n\n def run(self, sql):\n \"\"\"Guard'dan geçmiş SQL'i çalıştırır; (kolonlar, satırlar) döner.\"\"\"\n with self.conn.cursor() as cur:\n cur.execute(sql)\n cols = [d[0] for d in cur.description] if cur.description else []\n rows = cur.fetchall() if cur.description else []\n return cols, rows\n\n # ---- şema tanıtımı ----\n def schema_card(self, schemas=(\"public\",), max_cols=40):\n \"\"\"information_schema'dan kompakt şema kartı üretir (partisyonlar hariç).\"\"\"\n q = \"\"\"\n SELECT c.table_name, c.column_name, c.data_type\n FROM information_schema.columns c\n JOIN information_schema.tables t\n ON t.table_schema = c.table_schema AND t.table_name = c.table_name\n WHERE c.table_schema = ANY(%s) AND t.table_type = 'BASE TABLE'\n ORDER BY c.table_name, c.ordinal_position\"\"\"\n with self.conn.cursor() as cur:\n cur.execute(q, (list(schemas),))\n rows = cur.fetchall()\n tables = {}\n for tname, col, typ in rows:\n if re.search(r\"_(\\d{4}_\\d{2}|default)$\", tname):\n continue # partisyonları gizle — ana tablo yeterli\n tables.setdefault(tname, []).append(f\"{col} {typ}\")\n lines = []\n for t, cols in sorted(tables.items()):\n shown = cols[:max_cols]\n extra = f\" …(+{len(cols)-max_cols} kolon)\" if len(cols) > max_cols else \"\"\n lines.append(f\"- {t}({', '.join(shown)}){extra}\")\n return \"\\n\".join(lines)\n\n def data_dictionary(self):\n \"\"\"variables tablosundan Türkçe veri sözlüğü (kolon kodu → anlam/birim).\"\"\"\n try:\n cols, rows = self.run(\n \"SELECT v.id, v.description, COALESCE(v.unit,''), s.name \"\n \"FROM variables v JOIN variable_segments s ON s.id = v.segment_id \"\n \"ORDER BY s.name, v.id LIMIT 400\")\n except Exception:\n return \"\"\n return \"\\n\".join(f\"- {r[0]}: {r[1]}\" + (f\" [{r[2]}]\" if r[2] else \"\")\n for r in rows)\n\n\n# ------------------------------------------------------------------- PROMPT\nDOMAIN_NOTES = \"\"\"\\\nAlan bilgisi (QAPU tarımsal sulama / enerji izleme veritabanı):\n- Ölçüm tabloları cihaz×zaman serisidir; ana tablolar: measurements_energy (ae_*: aktif enerji Wh,\n re_l_*: endüktif reaktif, re_g_*: kapasitif reaktif — faz başına, örnekleme aralığı ~5 dk,\n değerler aralık enerjisidir, kümülatif DEĞİLDİR), measurements_power (p/q/s_*: W/var/VA,\n pf_*: güç faktörü, fq: frekans Hz), measurements_voltage (vrms_*, vfund_*, vharm_*),\n measurements_current (irms_*, ifund_*, iharm_*, ipeak_*), measurements_environment (pcb_t, pcb_h),\n measurements_device (batarya b_*, GSM rssi/mcc/mnc).\n- _r/_s/_t son ekleri üç fazı belirtir. Cihaz kimliği: device_id (varchar) → devices.id;\n cihaz adı devices.name. Zaman kolonu: device_time (timestamptz).\n- Reaktif oran = SUM(re_l_r+re_l_s+re_l_t) / NULLIF(SUM(ae_r+ae_s+ae_t),0).\n EPDK sınırları: endüktif %20, kapasitif %15. is_valid = true filtresi kullan.\n- Negatif enerji değerleri hatalıdır; GREATEST(x,0) ile ele.\n- Tarih filtrelerinde device_time kullan (partisyon budaması için).\"\"\"\n\nFEWSHOTS = [\n (\"Kaç cihaz var?\",\n \"SELECT COUNT(*) AS cihaz_sayisi FROM devices\"),\n (\"Son 7 günde en çok enerji tüketen 5 tesis hangileri?\",\n \"SELECT d.name, ROUND(SUM(GREATEST(e.ae_r,0)+GREATEST(e.ae_s,0)+GREATEST(e.ae_t,0))/1000.0, 1) AS kwh\\n\"\n \"FROM measurements_energy e JOIN devices d ON d.id = e.device_id\\n\"\n \"WHERE e.is_valid AND e.device_time >= now() - interval '7 days'\\n\"\n \"GROUP BY d.name ORDER BY kwh DESC LIMIT 5\"),\n (\"Hangi cihazlar son 30 günde endüktif reaktif sınırını aştı?\",\n \"SELECT d.name,\\n\"\n \" ROUND(100.0*SUM(GREATEST(e.re_l_r,0)+GREATEST(e.re_l_s,0)+GREATEST(e.re_l_t,0))\\n\"\n \" /NULLIF(SUM(GREATEST(e.ae_r,0)+GREATEST(e.ae_s,0)+GREATEST(e.ae_t,0)),0), 1) AS enduktif_oran_yuzde\\n\"\n \"FROM measurements_energy e JOIN devices d ON d.id = e.device_id\\n\"\n \"WHERE e.is_valid AND e.device_time >= now() - interval '30 days'\\n\"\n \"GROUP BY d.name\\n\"\n \"HAVING SUM(GREATEST(e.re_l_r,0)+GREATEST(e.re_l_s,0)+GREATEST(e.re_l_t,0))\\n\"\n \" > 0.20*SUM(GREATEST(e.ae_r,0)+GREATEST(e.ae_s,0)+GREATEST(e.ae_t,0))\\n\"\n \"ORDER BY enduktif_oran_yuzde DESC\"),\n]\n\n\ndef build_system_prompt(schema_card, data_dict):\n fs = \"\\n\\n\".join(f\"Soru: {q}\\nSQL:\\n{s}\" for q, s in FEWSHOTS)\n return f\"\"\"Sen QAPU enerji izleme veritabanı için bir SQL asistanısın.\nGörevin: kullanıcının Türkçe sorusunu TEK bir PostgreSQL SELECT sorgusuna çevirmek.\n\nKURALLAR:\n1. YALNIZCA SELECT yazabilirsin (INSERT/UPDATE/DELETE/DDL yasak, zaten engellenir).\n2. Cevabında yalnızca ```sql ... ``` bloğu olsun, açıklama yazma.\n3. Sonuç satır sayısını makul tut (gerekirse LIMIT; zaten {MAX_ROWS} satırda kesilir).\n4. Bilinmeyen kolon uydurma; yalnızca şemadaki tablo ve kolonları kullan.\n5. Soru belirsizse en makul yorumu seç; cevap aşamasında varsayımını belirtirsin.\n\nŞEMA:\n{schema_card}\n\n{DOMAIN_NOTES}\n\nVERİ SÖZLÜĞÜ (variables kataloğu — ölçüm kolonlarının anlamı):\n{data_dict}\n\nÖRNEKLER:\n{fs}\"\"\"\n\n\nANSWER_PROMPT = \"\"\"Kullanıcının sorusu: {question}\n\nÇalıştırılan SQL:\n{sql}\n\nSonuç ({nrows} satır{truncated}):\n{result}\n\nBu sonucu kullanıcıya Türkçe, kısa ve net biçimde açıkla. Sayıları okunur yaz\n(binlik ayraç, en fazla 1-2 ondalık). Sonuç boşsa bunun ne anlama gelebileceğini söyle.\nTeknik SQL detayı anlatma; yöneticiye rapor verir gibi yaz. Varsayım yaptıysan tek cümleyle belirt.\"\"\"\n\n\n# -------------------------------------------------------------------- AGENT\ndef _extract_sql(text):\n m = re.search(r\"```sql\\s*(.*?)```\", text, re.S | re.I)\n if m:\n return m.group(1).strip()\n m = re.search(r\"```\\s*(SELECT.*?)```\", text, re.S | re.I)\n if m:\n return m.group(1).strip()\n m = re.search(r\"(WITH\\b.*|SELECT\\b.*)\", text, re.S | re.I)\n if m:\n return m.group(1).strip()\n raise GuardError(\"Yanıtta SQL bulunamadı\")\n\n\ndef _fmt_cell(v):\n if isinstance(v, decimal.Decimal):\n v = float(v)\n if isinstance(v, float):\n return f\"{v:,.2f}\".rstrip(\"0\").rstrip(\".\")\n if isinstance(v, (datetime.datetime, datetime.date)):\n return str(v)\n return str(v)\n\n\ndef rows_to_markdown(cols, rows, max_show=30):\n if not cols:\n return \"(sonuç yok)\"\n head = \"| \" + \" | \".join(cols) + \" |\"\n sep = \"|\" + \"---|\" * len(cols)\n body = [\"| \" + \" | \".join(_fmt_cell(v) for v in r) + \" |\" for r in rows[:max_show]]\n return \"\\n\".join([head, sep] + body)\n\n\nclass ChatAgent:\n \"\"\"backend: callable(messages: list[{'role','content'}]) -> str\"\"\"\n\n def __init__(self, db: DB, backend, system_prompt=None):\n self.db = db\n self.backend = backend\n self.system = system_prompt or build_system_prompt(\n db.schema_card(), db.data_dictionary())\n self.history = [] # (soru, sql, cevap)\n\n def ask(self, question, verbose=False):\n msgs = [{\"role\": \"system\", \"content\": self.system}]\n for q, s, _ in self.history[-3:]: # kısa bağlam: son 3 soru\n msgs.append({\"role\": \"user\", \"content\": q})\n msgs.append({\"role\": \"assistant\", \"content\": f\"```sql\\n{s}\\n```\"})\n msgs.append({\"role\": \"user\", \"content\": question})\n\n sql, cols, rows, err = None, [], [], None\n for attempt in range(MAX_RETRY + 1):\n out = self.backend(msgs)\n try:\n sql = _extract_sql(out)\n safe = guard_sql(sql)\n cols, rows = self.db.run(safe)\n err = None\n break\n except Exception as e:\n err = str(e)\n if verbose:\n print(f\"[deneme {attempt+1}] hata: {err}\")\n msgs.append({\"role\": \"assistant\", \"content\": out})\n msgs.append({\"role\": \"user\",\n \"content\": f\"Bu SQL hata verdi: {err}\\nLütfen düzeltilmiş tek bir SELECT yaz.\"})\n if err:\n return {\"answer\": f\"Üzgünüm, sorguyu çalıştıramadım: {err}\",\n \"sql\": sql, \"table_md\": \"\", \"rows\": []}\n\n table_md = rows_to_markdown(cols, rows)\n trunc = f\", ilk 30 gösteriliyor\" if len(rows) > 30 else \"\"\n ans_msgs = [{\"role\": \"user\", \"content\": ANSWER_PROMPT.format(\n question=question, sql=sql, nrows=len(rows), truncated=trunc,\n result=table_md)}]\n answer = self.backend(ans_msgs).strip()\n self.history.append((question, sql, answer))\n return {\"answer\": answer, \"sql\": sql, \"table_md\": table_md,\n \"rows\": rows, \"cols\": cols}\n", "id": "YWbelFFRkF1B" }, { "cell_type": "code", "metadata": { "id": "TgPoqujnkF1B" }, "execution_count": null, "outputs": [], "source": "# 3) Canlı veritabanı bağlantısı — bilgiler gizli sorulur, koda yazılmaz\nfrom getpass import getpass\nimport qapu_dbload as dbl\nimport qapu_chatbot as cb\n\nDB_HOST = input(\"Sunucu (host/endpoint): \").strip()\nDB_PORT = input(\"Port [5432]: \").strip() or \"5432\"\nDB_NAME = input(\"Veritabanı adı [qapu]: \").strip() or \"qapu\"\nDB_USER = input(\"Kullanıcı: \").strip()\nDB_PASS = getpass(\"Şifre: \")\nSSLMODE = input(\"sslmode [prefer] (require/prefer/disable): \").strip() or \"prefer\"\n\n# Analiz için engine + sohbet için korumalı bağlantı (ikisi de salt-okunur)\nengine = dbl.make_engine(DB_HOST, int(DB_PORT), DB_NAME, DB_USER, DB_PASS, sslmode=SSLMODE)\ndb = cb.DB(DB_HOST, int(DB_PORT), DB_NAME, DB_USER, DB_PASS, sslmode=SSLMODE)\ncols, rows = db.run(\"SELECT current_database(), now()\")\nprint(\"Bağlandı:\", rows[0][0], \"· sunucu saati:\", rows[0][1])", "id": "TgPoqujnkF1B" }, { "cell_type": "code", "metadata": { "id": "yHFeDJhakF1B" }, "execution_count": null, "outputs": [], "source": "# 4) Canlı veriyi çek ve analiz et\nimport pandas as pd, numpy as np\nimport qapu_pm as q\n\nDAYS = 120 # analiz penceresi (gün) — geçmiş büyüdükçe artırabilirsiniz\n\nprint(\"Veri çekiliyor...\")\ntabs = dbl.load_db(engine, days=DAYS)\ndaily = q.build_daily(tabs)\nresults = q.analyze(daily, tabs.get(\"devices\"))\n\nimport qapu_customers as qc\nozet = pd.DataFrame([{\n \"Müşteri\": qc.CUSTOMERS[qc.customer_of(r[\"device_id\"])][\"ad\"].split(\"(\")[0].strip()[:28],\n \"Tesis\": r[\"name\"], \"Gün\": r[\"days\"], \"kWh\": round(r[\"ae_total_kwh\"], 1),\n \"End.% (30g)\": None if not np.isfinite(r[\"ind_ratio_30g\"] or np.nan) else round(100*r[\"ind_ratio_30g\"], 1),\n \"PF\": None if not np.isfinite(r[\"pf_loaded\"] or np.nan) else round(r[\"pf_loaded\"], 3),\n \"Skor\": r[\"score\"], \"Durum\": r[\"risk\"],\n} for r in sorted(results.values(), key=lambda x: x[\"score\"])])\nprint(f\"{len(results)} cihaz analiz edildi.\")\nozet", "id": "yHFeDJhakF1B" }, { "cell_type": "code", "metadata": { "id": "7Nyk2ZdjkF1B" }, "execution_count": null, "outputs": [], "source": "# 5) Müşteri bazlı Word raporları üret ve ZIP olarak indir\nimport os, zipfile\nimport qapu_docx as qdocx\n\nINCLUDE_TEST = False # test cihazlarını da raporlamak için True\n\nwritten = qdocx.generate_customer_reports(results, daily,\n outdir=\"raporlar_musteri\",\n include_test=INCLUDE_TEST)\nzip_path = \"raporlar_musteri/qapu_musteri_raporlari.zip\"\nwith zipfile.ZipFile(zip_path, \"w\", zipfile.ZIP_DEFLATED) as z:\n for root, _, fs in os.walk(\"raporlar_musteri\"):\n for f in fs:\n p = os.path.join(root, f)\n if p != zip_path:\n z.write(p, os.path.relpath(p, \"raporlar_musteri\"))\n\nfor kod, files_ in written.items():\n print(f\"== {qc.CUSTOMERS[kod]['ad']}\")\n for f in files_:\n print(\" \", f)\nprint(\"ZIP:\", zip_path)\n\ntry:\n from google.colab import files\n files.download(zip_path)\nexcept ImportError:\n pass", "id": "7Nyk2ZdjkF1B" }, { "cell_type": "code", "metadata": { "id": "FZvCysDpkF1C" }, "execution_count": null, "outputs": [], "source": "# 6) Sohbet botu — LLM arka ucu seçimi\nfrom getpass import getpass\n\nBACKEND = \"cerebras\" # \"cerebras\" | \"gemini\" | \"openai\" (OpenAI uyumlu her uç / şirket içi vLLM)\n\ndef make_backend(kind):\n if kind == \"cerebras\":\n from cerebras.cloud.sdk import Cerebras\n client = Cerebras(api_key=getpass(\"Cerebras API anahtarı: \"))\n def call(messages):\n r = client.chat.completions.create(model=\"llama-3.3-70b\", messages=messages,\n temperature=0, max_tokens=1200)\n return r.choices[0].message.content\n return call\n if kind == \"gemini\":\n import google.generativeai as genai\n genai.configure(api_key=getpass(\"Gemini API anahtarı: \"))\n def call(messages):\n system = \"\\n\".join(m[\"content\"] for m in messages if m[\"role\"] == \"system\") or None\n model = genai.GenerativeModel(\"gemini-2.0-flash\", system_instruction=system)\n hist = [{\"role\": \"user\" if m[\"role\"] == \"user\" else \"model\", \"parts\": [m[\"content\"]]}\n for m in messages if m[\"role\"] != \"system\"]\n return model.generate_content(hist).text\n return call\n if kind == \"openai\":\n from openai import OpenAI\n base = input(\"Base URL (ör. http://sunucu:8000/v1): \").strip()\n key = getpass(\"API anahtarı (yoksa boş): \") or \"EMPTY\"\n model = input(\"Model adı: \").strip()\n client = OpenAI(api_key=key, base_url=base)\n def call(messages):\n r = client.chat.completions.create(model=model, messages=messages,\n temperature=0, max_tokens=1200)\n return r.choices[0].message.content\n return call\n raise ValueError(kind)\n\nbackend = make_backend(BACKEND)\nagent = cb.ChatAgent(db, backend)\nprint(\"Bot hazır. Hızlı test:\")\nr = agent.ask(\"Kaç cihaz kayıtlı?\")\nprint(r[\"answer\"], \"\\nSQL:\", r[\"sql\"])", "id": "FZvCysDpkF1C" }, { "cell_type": "code", "metadata": { "id": "I9AB6GdOkF1C" }, "execution_count": null, "outputs": [], "source": "# 7) Gradio sohbet arayüzü (geçici paylaşım linkiyle)\nimport gradio as gr\n\nORNEKLER = [\n \"Son 7 günde en çok enerji tüketen 5 tesis hangileri?\",\n \"Hangi cihazlar son 30 günde endüktif reaktif sınırını (%20) aştı?\",\n \"KOSKİ cihazlarının bu haftaki toplam tüketimi ne kadar?\",\n \"Meram 2 cihazının günlük ortalama güç faktörü son 2 haftada nasıl değişti?\",\n \"Dün hiç veri göndermeyen cihazlar var mı?\",\n]\n\ndef chat_fn(message, history):\n r = agent.ask(message)\n out = r[\"answer\"]\n if r.get(\"sql\"):\n out += f\"\\n\\n
🔍 Çalıştırılan SQL\\n\\n```sql\\n{r['sql']}\\n```\\n\\n
\"\n if r.get(\"table_md\"):\n out += \"\\n\\n\" + r[\"table_md\"]\n return out\n\ndemo = gr.ChatInterface(\n chat_fn,\n title=\"QAPU Veritabanı Asistanı\",\n description=\"Canlı enerji izleme veritabanına Türkçe soru sorun. Bot yalnızca okuma (SELECT) yapabilir.\",\n examples=ORNEKLER,\n)\ndemo.launch(debug=False, share=True) # share=True: 72 saatlik *.gradio.live linki (telefon dahil erişim)", "id": "I9AB6GdOkF1C" }, { "cell_type": "markdown", "metadata": { "id": "AV_YYlufkF1C" }, "source": "---\n### Notlar\n* **DAYS** (4. hücre): analiz penceresi; **INCLUDE_TEST** (5. hücre): test cihazlarını dahil eder.\n* Yeni cihaz/müşteri eşleşmesi: `qapu_customers.py` hücresindeki `DEVICE_CUSTOMER` sözlüğüne satır ekleyin.\n* Rapor eşikleri `qapu_pm.py` içindeki `CONFIG`; bot alan bilgisi `qapu_chatbot.py` içindeki\n `DOMAIN_NOTES`/`FEWSHOTS`.\n* Gradio linki geçicidir (72 saat). Kalıcı erişim için aynı dosyalar bir sunucuda\n `python -c \"...\"` ile çalıştırılabilir — kod Colab'a bağımlı değildir.\n* Raporlar deterministik (offline narrator, LLM'siz) üretilir; LLM yalnızca sohbet botunda kullanılır.\n", "id": "AV_YYlufkF1C" } ] }