—
# app/main.py
import os
from datetime import date, datetime, timedelta
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from app.db import fetch_all, fetch_one
load_dotenv()
app = FastAPI(title=”Panel Rapor”, version=”1.0.0″)
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, “templates”)
templates = Jinja2Templates(directory=TEMPLATES_DIR)
def _date_start_dt(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0)
def _date_end_dt_exclusive(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0) + timedelta(days=1)
def _first_day_of_month(dt: datetime) -> datetime:
return datetime(dt.year, dt.month, 1, 0, 0, 0)
def _add_months(dt: datetime, months: int) -> datetime:
y = dt.year + (dt.month – 1 + months) // 12
m = (dt.month – 1 + months) % 12 + 1
return datetime(y, m, 1, 0, 0, 0)
# =========================
# ✅ YOUR INNER SELECT (RAPOR 1)
# =========================
BASE_INNER_SQL = “””
SELECT
(
SELECT MAX(TO_NUMBER(pv.VALUETEXTTUR))
FROM CONTENTCC_C_PROPERTYVALUE pv
JOIN CONTENTCC_C_TPROPERTYVALUE tpv
ON pv.PROPERTYVALUEID = tpv.PROPERTYVALUEID
WHERE pv.PROPERTYID = 19
AND tpv.TITLEID = tl.TITLEID
) AS HEDEFKODU,
(pl.NAME || ‘,’ || adr.DISTRICTNAME || ‘,’ || adr.CITYNAME) AS MUSTERI,
ppb.DATECREATED,
srv.TIMESTARTS AS SERVICETIMESTARTS,
ppb.DATEMODIFIED,
(
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) AS INVOICECODE,
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN ‘Kart Okutuldu’
WHEN 0 THEN ‘Kart Okutulmadı’
ELSE NULL
END AS DELIVERYVALIDATIONTYPE_TEXT,
ppb.BARCODE,
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN ‘Sevk Bekliyor’
WHEN 1 THEN ‘Sevk Kontrolü Yapıldı’
WHEN 2 THEN ‘Sevk Başladı’
WHEN 3 THEN ‘Transfer Edildi’
WHEN 4 THEN ‘Teslim Edildi’
WHEN 5 THEN ‘Teslim Edilemedi’
WHEN 99 THEN ‘İptal Edildi’
ELSE NULL
END AS SHIPPINGSTATUS_TEXT,
CASE srv.ISEXPRESSSERVICE
WHEN 1 THEN ‘Hızlı Teslimat’
WHEN 0 THEN ‘Servis’
ELSE NULL
END AS SERVICETYPE_TEXT,
reg.NAMETUR AS PACKAGEREGIONNAMETUR,
srv.NAMETUR AS SERVICENAMETUR,
wh.NAMETUR AS PACKAGEWAREHOUSENAMETUR,
dep.NAMETUR AS DEPARTUREMANAGERNAMETUR,
arr.NAMETUR AS ARRIVALMANAGERNAMETUR,
drv.NAME AS SERVICEDRIVERNAME,
veh.PLATE AS SERVICEVEHICLEPLATE,
(
SELECT DeliveredPoint
FROM (
SELECT sp.DeliveredPoint,
ROW_NUMBER() OVER (ORDER BY sp.DeliveredTime DESC) rn
FROM Transportation_ServicePackage sp
WHERE sp.PackagePackBarcodeID = ppb.PackagePackBarcodeID
AND sp.IsDelivered = 1
)
WHERE rn = 1
) AS DELIVEREDLOCATION
FROM TRS_PACKAGEPACKBARCODE ppb
JOIN TRANSPORTATION_PACKAGE pkg
ON pkg.PACKAGEID = ppb.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION reg
ON reg.REGIONID = pkg.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE srv
ON srv.SERVICEID = ppb.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE veh
ON veh.VEHICLEID = srv.VEHICLEID
LEFT JOIN COLLABORATION_NODE wh
ON wh.NODEID = pkg.WAREHOUSEID
LEFT JOIN COLLABORATION_NODE dep
ON dep.NODEID = ppb.DEPARTUREMANAGERID
LEFT JOIN COLLABORATION_NODE arr
ON arr.NODEID = ppb.ARRIVALMANAGERID
LEFT JOIN COLLABORATION_EMPLOYEE drv
ON drv.EMPLOYEEID = srv.DRIVERID
LEFT JOIN TRADING_LINK pl
ON pl.LINKID = pkg.LINKID
INNER JOIN TRADING_LINK tl
ON tl.LINKID = pkg.LINKID
LEFT JOIN CONTACTCC_CONTACT c
ON c.CONTACTID = pl.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS adr
ON adr.ADDRESSID = c.DEFAULTADDRESSID
WHERE
pl.NAME NOT LIKE ‘%All%’
AND reg.NAMETUR IS NOT NULL
AND reg.NAMETUR <> ‘NULL’
AND ppb.DATECREATED >= :start_date
AND ppb.DATECREATED < :end_date
"""
def filtered_outer_sql(with_order: bool) -> str:
where = “””
WHERE 1=1
AND (:barcode IS NULL OR LOWER(t.barcode) LIKE LOWER(:barcode_like))
AND (:invoice IS NULL OR NVL(LOWER(t.invoicecode),”) = LOWER(:invoice))
AND (:status IS NULL OR t.shippingstatus_text = :status)
AND (:driver IS NULL OR LOWER(NVL(t.servicedrivername,”)) LIKE LOWER(:driver_like))
AND (:region IS NULL OR LOWER(NVL(t.packageregionnametur,”)) LIKE LOWER(:region_like))
AND (:warehouse IS NULL OR LOWER(NVL(t.packagewarehousenametur,”)) LIKE LOWER(:warehouse_like))
AND (:departure IS NULL OR LOWER(NVL(t.departuremanagernametur,”)) LIKE LOWER(:departure_like))
AND (:card IS NULL OR t.deliveryvalidationtype_text = :card)
AND (:service IS NULL OR LOWER(NVL(t.servicenametur,”)) LIKE LOWER(:service_like))
“””
order = ” ORDER BY t.datecreated DESC ” if with_order else “”
return f”””
SELECT *
FROM (
{BASE_INNER_SQL}
) t
{where}
{order}
“””
def count_sql() -> str:
return f”SELECT COUNT(1) AS total FROM ( {filtered_outer_sql(with_order=False)} )”
def paged_sql() -> str:
return f”””
SELECT * FROM (
{filtered_outer_sql(with_order=True)}
)
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
“””
@app.get(“/”, response_class=HTMLResponse)
@app.get(“/rapor”, response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse(“rapor.html”, {“request”: request})
# ✅ NEW PAGE: RAPOR 2
@app.get(“/rapor2”, response_class=HTMLResponse)
def rapor2_page(request: Request):
return templates.TemplateResponse(“rapor2.html”, {“request”: request})
@app.get(“/api/health”)
def health():
try:
row = fetch_one(“SELECT 1 AS ok FROM dual”)
return {“db”: “ok”, “ok”: row.get(“ok”, 1) if row else 1}
except Exception as e:
return JSONResponse(status_code=500, content={“db”: “fail”, “error”: str(e)})
def build_params(
start: date,
end: date,
barcode: str | None,
invoice: str | None,
status: str | None,
driver: str | None,
region: str | None,
warehouse: str | None,
departure: str | None,
card: str | None,
service: str | None,
) -> dict:
start_dt = _date_start_dt(start)
end_dt = _date_end_dt_exclusive(end)
barcode = (barcode or “”).strip() or None
invoice = (invoice or “”).strip() or None
status = (status or “”).strip() or None
driver = (driver or “”).strip() or None
region = (region or “”).strip() or None
warehouse = (warehouse or “”).strip() or None
departure = (departure or “”).strip() or None
card = (card or “”).strip() or None
service = (service or “”).strip() or None
return {
“start_date”: start_dt,
“end_date”: end_dt,
“barcode”: barcode,
“barcode_like”: f”{barcode}%” if barcode else None,
“invoice”: invoice,
“status”: status,
“driver”: driver,
“driver_like”: f”%{driver}%” if driver else None,
“region”: region,
“region_like”: f”%{region}%” if region else None,
“warehouse”: warehouse,
“warehouse_like”: f”%{warehouse}%” if warehouse else None,
“departure”: departure,
“departure_like”: f”%{departure}%” if departure else None,
“card”: card,
“service”: service,
“service_like”: f”%{service}%” if service else None,
}
@app.get(“/api/rapor”)
def rapor_data(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barcode: str | None = None,
invoice: str | None = None,
status: str | None = None,
driver: str | None = None,
region: str | None = None,
warehouse: str | None = None,
departure: str | None = None,
card: str | None = None,
service: str | None = None,
page: int = 1,
page_size: int = 50,
):
try:
if page < 1:
page = 1
if page_size < 1:
page_size = 50
if page_size > 500:
page_size = 500
params = build_params(start, end, barcode, invoice, status, driver, region, warehouse, departure, card, service)
total_row = fetch_one(count_sql(), params)
total = int(total_row[“total”]) if total_row and total_row.get(“total”) is not None else 0
params_items = dict(params)
params_items[“offset”] = (page – 1) * page_size
params_items[“limit”] = page_size
rows = fetch_all(paged_sql(), params_items)
kpi_sql = f”””
SELECT shippingstatus_text AS durum, COUNT(1) AS adet
FROM ( {filtered_outer_sql(with_order=False)} )
GROUP BY shippingstatus_text
ORDER BY COUNT(1) DESC
“””
kpi_rows = fetch_all(kpi_sql, params)
kpis = []
for r in kpi_rows:
adet = int(r.get(“adet”) or 0)
yuzde = round((adet / total) * 100, 2) if total else 0
kpis.append({“durum”: r.get(“durum”), “adet”: adet, “yuzde”: yuzde})
return {
“range”: {“start”: str(start), “end”: str(end)},
“page”: page,
“page_size”: page_size,
“total”: total,
“kpis”: kpis,
“rows”: rows,
}
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
@app.get(“/api/rapor/group”)
def rapor_group(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barcode: str | None = None,
invoice: str | None = None,
status: str | None = None,
driver: str | None = None,
region: str | None = None,
warehouse: str | None = None,
departure: str | None = None,
card: str | None = None,
service: str | None = None,
group_by: str = “service”,
):
try:
params = build_params(start, end, barcode, invoice, status, driver, region, warehouse, departure, card, service)
group_map = {
“service”: “t.servicenametur”,
“customer”: “t.musteri”,
“region”: “t.packageregionnametur”,
“warehouse”: “t.packagewarehousenametur”,
“departure”: “t.departuremanagernametur”,
“card”: “t.deliveryvalidationtype_text”,
“status”: “t.shippingstatus_text”,
“driver”: “t.servicedrivername”,
}
col = group_map.get(group_by, “t.servicenametur”)
sql = f”””
SELECT
NVL({col}, ‘-‘) AS label,
COUNT(1) AS adet
FROM (
{filtered_outer_sql(with_order=False)}
) t
GROUP BY {col}
ORDER BY COUNT(1) DESC
“””
items_raw = fetch_all(sql, params)
total = sum(int(x.get(“adet”) or 0) for x in items_raw) or 0
items = []
for x in items_raw[:200]:
adet = int(x.get(“adet”) or 0)
yuzde = round((adet / total) * 100, 2) if total else 0
items.append({“label”: x.get(“label”), “adet”: adet, “yuzde”: yuzde})
return {“group_by”: group_by, “total”: total, “items”: items}
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
# ============================================================
# ✅ TREND KPI (unchanged from your “updated” version)
# ============================================================
@app.get(“/api/rapor/trend-kpi”)
def rapor_trend_kpi(
months: int = Query(default=6, ge=1, le=24),
start: date | None = None,
end: date | None = None,
):
try:
if start and end:
use_start = _date_start_dt(start)
use_end = _date_end_dt_exclusive(end)
else:
now = datetime.now()
cur_month = _first_day_of_month(now)
start_month = _add_months(cur_month, -(months – 1))
end_month_excl = _add_months(cur_month, 1)
use_start = start_month
use_end = end_month_excl
sql = “””
SELECT
TO_CHAR(TRUNC(ppb.DATECREATED, ‘MM’), ‘YYYY-MM’) AS ay,
SUM(CASE WHEN ppb.SHIPPINGSTATUS = 4 THEN 1 ELSE 0 END) AS teslim_edildi,
SUM(CASE WHEN ppb.SHIPPINGSTATUS = 0 THEN 1 ELSE 0 END) AS sevk_bekliyor,
SUM(CASE WHEN ppb.DELIVERYVALIDATIONTYPE = 0 THEN 1 ELSE 0 END) AS kart_okutulmadi
FROM TRS_PACKAGEPACKBARCODE ppb
WHERE ppb.DATECREATED >= :start_dt
AND ppb.DATECREATED < :end_dt
GROUP BY TRUNC(ppb.DATECREATED, 'MM')
ORDER BY TRUNC(ppb.DATECREATED, 'MM')
"""
rows = fetch_all(sql, {"start_dt": use_start, "end_dt": use_end})
def pct(curr: int, prev: int):
if prev == 0:
return None
return round(((curr - prev) / prev) * 100, 2)
out = []
prev = None
for r in rows:
cur = {
"ay": r.get("ay"),
"teslim_edildi": int(r.get("teslim_edildi") or 0),
"sevk_bekliyor": int(r.get("sevk_bekliyor") or 0),
"kart_okutulmadi": int(r.get("kart_okutulmadi") or 0),
}
if prev:
cur["degisim"] = {
"teslim_edildi_pct": pct(cur["teslim_edildi"], prev["teslim_edildi"]),
"sevk_bekliyor_pct": pct(cur["sevk_bekliyor"], prev["sevk_bekliyor"]),
"kart_okutulmadi_pct": pct(cur["kart_okutulmadi"], prev["kart_okutulmadi"]),
}
else:
cur["degisim"] = {
"teslim_edildi_pct": None,
"sevk_bekliyor_pct": None,
"kart_okutulmadi_pct": None,
}
out.append(cur)
prev = cur
return {"months": out}
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
@app.get("/api/trend-kpi")
def trend_kpi_alias(
months: int = Query(default=6, ge=1, le=24),
start: date | None = None,
end: date | None = None,
):
return rapor_trend_kpi(months=months, start=start, end=end)
def _make_xlsx(headers: list[str], rows: list[dict], sheet_name: str = "Rapor") -> bytes:
wb = Workbook()
ws = wb.active
ws.title = sheet_name
ws.append(headers)
for r in rows:
ws.append([r.get(h) for h in headers])
for i, h in enumerate(headers, start=1):
max_len = len(h)
for rr in rows[:300]:
val = rr.get(h)
if val is None:
continue
s = str(val)
if len(s) > max_len:
max_len = len(s)
ws.column_dimensions[get_column_letter(i)].width = max(12, min(48, max_len + 2))
import io
bio = io.BytesIO()
wb.save(bio)
return bio.getvalue()
@app.get(“/api/rapor/export.xlsx”)
def export_xlsx(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barcode: str | None = None,
invoice: str | None = None,
status: str | None = None,
driver: str | None = None,
region: str | None = None,
warehouse: str | None = None,
departure: str | None = None,
card: str | None = None,
service: str | None = None,
):
try:
params = build_params(start, end, barcode, invoice, status, driver, region, warehouse, departure, card, service)
sql = filtered_outer_sql(with_order=True)
rows = fetch_all(sql, params)
headers = [
“datecreated”,
“hedefkodu”,
“musteri”,
“barcode”,
“invoicecode”,
“shippingstatus_text”,
“servicetype_text”,
“packageregionnametur”,
“servicenametur”,
“packagewarehousenametur”,
“departuremanagernametur”,
“arrivalmanagernametur”,
“servicedrivername”,
“servicevehicleplate”,
“deliveredlocation”,
“deliveryvalidationtype_text”,
“servicetimestarts”,
“datemodified”,
]
data = _make_xlsx(headers, rows, sheet_name=”Sevkiyat”)
filename = f”sevkiyat_{start}_{end}.xlsx”
return StreamingResponse(
iter([data]),
media_type=”application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”,
headers={“Content-Disposition”: f’attachment; filename=”{filename}”‘},
)
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
@app.get(“/api/export.xlsx”)
def export_xlsx_alias(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barcode: str | None = None,
invoice: str | None = None,
status: str | None = None,
driver: str | None = None,
region: str | None = None,
warehouse: str | None = None,
departure: str | None = None,
card: str | None = None,
service: str | None = None,
):
return export_xlsx(
start=start, end=end,
barcode=barcode, invoice=invoice, status=status,
driver=driver, region=region, warehouse=warehouse,
departure=departure, card=card, service=service
)
# ============================================================
# ✅ RAPOR 2: “Lokasyonu Tanımsız Ürünler”
# – /rapor2 -> rapor2.html
# – /api/rapor2 -> JSON
# – /api/rapor2/export.xlsx -> Excel
# ============================================================
RAPOR2_BASE_SQL = “””
SELECT
CI.ITEMID AS urunkodu,
CI.NAMETUR AS urunadi,
CN.NAMETUR AS depo,
LOK.DISPLAYNAME AS lokasyon,
SUM(SP.QUANTITY) AS stok,
CC.NAMETUR AS baskin_sinif
FROM CATALOG_ITEM CI
INNER JOIN STOCK_PARTY SP
ON CI.ITEMID = SP.ITEMID
LEFT JOIN CATALOG_CLASS CC
ON CI.DOMINANTCLASSID = CC.CLASSID
INNER JOIN COLLABORATION_NODE CN
ON SP.WAREHOUSEID = CN.NODEID
LEFT JOIN (
SELECT DISTINCT
CII.ITEMID,
SC.WAREHOUSEID,
SC.DISPLAYNAME
FROM CATALOG_INVENTORYITEM CII
INNER JOIN STOCK_STOCKINVENTORYITEM SSII
ON CII.INVENTORYITEMID = SSII.INVENTORYITEMID
INNER JOIN STOCK_CONTAINER SC
ON SSII.EXITCONTAINERID = SC.CONTAINERID
) LOK
ON CI.ITEMID = LOK.ITEMID
AND SP.WAREHOUSEID = LOK.WAREHOUSEID
WHERE LOK.ITEMID IS NULL
GROUP BY CI.ITEMID, CI.NAMETUR, CN.NAMETUR, LOK.DISPLAYNAME, CC.NAMETUR
HAVING (SUM(SP.QUANTITY) > 0)
“””
def rapor2_filtered_sql(with_order: bool) -> str:
where = “””
WHERE 1=1
AND (:urunkodu IS NULL OR LOWER(t.urunkodu) LIKE LOWER(:urunkodu_like))
AND (:urunadi IS NULL OR LOWER(t.urunadi) LIKE LOWER(:urunadi_like))
AND (:depo IS NULL OR LOWER(t.depo) LIKE LOWER(:depo_like))
AND (:baskin_sinif IS NULL OR LOWER(NVL(t.baskin_sinif,”)) LIKE LOWER(:baskin_sinif_like))
“””
order = ” ORDER BY t.stok DESC ” if with_order else “”
return f”””
SELECT *
FROM (
{RAPOR2_BASE_SQL}
) t
{where}
{order}
“””
def rapor2_count_sql() -> str:
return f”SELECT COUNT(1) AS total FROM ( {rapor2_filtered_sql(with_order=False)} )”
def rapor2_paged_sql() -> str:
return f”””
SELECT * FROM (
{rapor2_filtered_sql(with_order=True)}
)
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
“””
def rapor2_params(
urunkodu: str | None,
urunadi: str | None,
depo: str | None,
baskin_sinif: str | None,
) -> dict:
urunkodu = (urunkodu or “”).strip() or None
urunadi = (urunadi or “”).strip() or None
depo = (depo or “”).strip() or None
baskin_sinif = (baskin_sinif or “”).strip() or None
return {
“urunkodu”: urunkodu,
“urunkodu_like”: f”{urunkodu}%” if urunkodu else None,
“urunadi”: urunadi,
“urunadi_like”: f”%{urunadi}%” if urunadi else None,
“depo”: depo,
“depo_like”: f”%{depo}%” if depo else None,
“baskin_sinif”: baskin_sinif,
“baskin_sinif_like”: f”%{baskin_sinif}%” if baskin_sinif else None,
}
@app.get(“/api/rapor2”)
def rapor2_data(
urunkodu: str | None = None,
urunadi: str | None = None,
depo: str | None = None,
baskin_sinif: str | None = None,
page: int = 1,
page_size: int = 50,
):
try:
if page < 1:
page = 1
if page_size < 1:
page_size = 50
if page_size > 500:
page_size = 500
params = rapor2_params(urunkodu, urunadi, depo, baskin_sinif)
total_row = fetch_one(rapor2_count_sql(), params)
total = int(total_row[“total”]) if total_row and total_row.get(“total”) is not None else 0
params_items = dict(params)
params_items[“offset”] = (page – 1) * page_size
params_items[“limit”] = page_size
rows = fetch_all(rapor2_paged_sql(), params_items)
return {
“page”: page,
“page_size”: page_size,
“total”: total,
“rows”: rows,
}
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
@app.get(“/api/rapor2/export.xlsx”)
def rapor2_export_xlsx(
urunkodu: str | None = None,
urunadi: str | None = None,
depo: str | None = None,
baskin_sinif: str | None = None,
):
try:
params = rapor2_params(urunkodu, urunadi, depo, baskin_sinif)
rows = fetch_all(rapor2_filtered_sql(with_order=True), params)
headers = [“urunkodu”, “urunadi”, “depo”, “lokasyon”, “stok”, “baskin_sinif”]
data = _make_xlsx(headers, rows, sheet_name=”Lokasyonsuz_Urunler”)
filename = f”lokasyonsuz_urunler_{date.today()}.xlsx”
return StreamingResponse(
iter([data]),
media_type=”application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”,
headers={“Content-Disposition”: f’attachment; filename=”{filename}”‘},
)
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
base html
{% extends “base.html” %}
{% block title %}Sevkiyat Raporu{% endblock %}
{% block page_title %}Sevkiyat Raporu{% endblock %}
{% block nav_rapor_active %}active{% endblock %}
{% block head_extra %}
{% endblock %}
{% block content %}
Sütun Ayarları
Sütunları aç/kapat (tarayıcıya kaydedilir).
Görünen
Gizli
Det
—
Sevkiyat Raporu
Cencora
Yükleniyor…
Sütun Ayarları
Sütunları aç/kapat (tarayıcıya kaydedilir).
Görünen
Gizli
Detay
–
Sevkiyat Raporu
Filtreleri doldur → Raporu Çalıştır. Excel, filtreli tüm satırları indirir.
Gruplama
–
Gruplama (2)
–
–
DATECREATED
HEDEFKODU
MUSTERI
BARCODE
INVOICE
DURUM
SERVIS_TIPI
BOLGE
SERVIS
WAREHOUSE
DEPARTURE
ARRIVAL
SOFOR
ARAC
DELIVERED_LOC
KART
SERVICE_START
DATEMODIFIED
Henüz veri yok.
# app/main.py
import os
from datetime import date, datetime, timedelta
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from app.db import fetch_all, fetch_one
load_dotenv()
app = FastAPI(title=”Panel Rapor”, version=”1.0.0″)
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, “templates”)
templates = Jinja2Templates(directory=TEMPLATES_DIR)
def _date_start_dt(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0)
def _date_end_dt_exclusive(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0) + timedelta(days=1)
# =========================
# REPORT #1 (YOUR EXISTING)
# =========================
# … (your existing report-1 code stays exactly as you have it)
# =========================
# ✅ REPORT #2: “LOKASYONSUZ URUNLER”
# Only items with NO location (LOK.ITEMID IS NULL)
# =========================
BASE2_INNER_SQL = “””
SELECT
CI.ITEMID AS URUNKODU,
CI.NAMETUR AS URUNADI,
CN.NAMETUR AS DEPO,
NVL(LOK.DISPLAYNAME, ‘-‘) AS LOKASYON,
SUM(SP.QUANTITY) AS STOK,
CC.NAMETUR AS BASKIN_SINIF
FROM CATALOG_ITEM CI
INNER JOIN STOCK_PARTY SP
ON CI.ITEMID = SP.ITEMID
LEFT JOIN CATALOG_CLASS CC
ON CI.DOMINANTCLASSID = CC.CLASSID
INNER JOIN COLLABORATION_NODE CN
ON SP.WAREHOUSEID = CN.NODEID
LEFT JOIN (
SELECT DISTINCT
CII.ITEMID,
SC.WAREHOUSEID,
SC.DISPLAYNAME
FROM CATALOG_INVENTORYITEM CII
INNER JOIN STOCK_STOCKINVENTORYITEM SSII
ON CII.INVENTORYITEMID = SSII.INVENTORYITEMID
INNER JOIN STOCK_CONTAINER SC
ON SSII.EXITCONTAINERID = SC.CONTAINERID
) LOK
ON CI.ITEMID = LOK.ITEMID
AND SP.WAREHOUSEID = LOK.WAREHOUSEID
WHERE LOK.ITEMID IS NULL
GROUP BY
CI.ITEMID,
CI.NAMETUR,
CN.NAMETUR,
NVL(LOK.DISPLAYNAME, ‘-‘),
CC.NAMETUR
HAVING SUM(SP.QUANTITY) > 0
“””
def filtered_outer_sql2(with_order: bool) -> str:
“””
Outer filter (safe for Oracle): SELECT * FROM (INNER) t WHERE …
“””
where = “””
WHERE 1=1
AND (:urunkodu IS NULL OR TO_CHAR(t.urunkodu) = :urunkodu)
AND (:urunadi IS NULL OR LOWER(NVL(t.urunadi,”)) LIKE LOWER(:urunadi_like))
AND (:depo IS NULL OR LOWER(NVL(t.depo,”)) LIKE LOWER(:depo_like))
AND (:baskin_sinif IS NULL OR LOWER(NVL(t.baskin_sinif,”)) LIKE LOWER(:baskin_sinif_like))
“””
order = ” ORDER BY t.stok DESC, t.depo ASC, t.urunadi ASC ” if with_order else “”
return f”””
SELECT *
FROM (
{BASE2_INNER_SQL}
) t
{where}
{order}
“””
def count_sql2() -> str:
return f”SELECT COUNT(1) AS total FROM ( {filtered_outer_sql2(with_order=False)} )”
def paged_sql2() -> str:
return f”””
SELECT * FROM (
{filtered_outer_sql2(with_order=True)}
)
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
“””
def build_params2(
urunkodu: str | None,
urunadi: str | None,
depo: str | None,
baskin_sinif: str | None,
) -> dict:
urunkodu = (urunkodu or “”).strip() or None
urunadi = (urunadi or “”).strip() or None
depo = (depo or “”).strip() or None
baskin_sinif = (baskin_sinif or “”).strip() or None
return {
“urunkodu”: urunkodu,
“urunadi”: urunadi,
“urunadi_like”: f”%{urunadi}%” if urunadi else None,
“depo”: depo,
“depo_like”: f”%{depo}%” if depo else None,
“baskin_sinif”: baskin_sinif,
“baskin_sinif_like”: f”%{baskin_sinif}%” if baskin_sinif else None,
}
def _make_xlsx(headers: list[str], rows: list[dict]) -> bytes:
wb = Workbook()
ws = wb.active
ws.title = “Rapor”
ws.append(headers)
for r in rows:
ws.append([r.get(h) for h in headers])
for i, h in enumerate(headers, start=1):
ws.column_dimensions[get_column_letter(i)].width = max(14, min(44, len(h) + 2))
import io
bio = io.BytesIO()
wb.save(bio)
return bio.getvalue()
@app.get(“/rapor2”, response_class=HTMLResponse)
def rapor2_page(request: Request):
return templates.TemplateResponse(“rapor2.html”, {“request”: request})
@app.get(“/api/rapor2”)
def rapor2_data(
urunkodu: str | None = None,
urunadi: str | None = None,
depo: str | None = None,
baskin_sinif: str | None = None,
page: int = 1,
page_size: int = 50,
):
try:
if page < 1:
page = 1
if page_size < 1:
page_size = 50
if page_size > 1000:
page_size = 1000
params = build_params2(urunkodu, urunadi, depo, baskin_sinif)
total_row = fetch_one(count_sql2(), params)
total = int(total_row[“total”]) if total_row and total_row.get(“total”) is not None else 0
params_items = dict(params)
params_items[“offset”] = (page – 1) * page_size
params_items[“limit”] = page_size
rows = fetch_all(paged_sql2(), params_items)
return {
“page”: page,
“page_size”: page_size,
“total”: total,
“rows”: rows,
}
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
@app.get(“/api/rapor2/export.xlsx”)
def rapor2_export_xlsx(
urunkodu: str | None = None,
urunadi: str | None = None,
depo: str | None = None,
baskin_sinif: str | None = None,
):
try:
params = build_params2(urunkodu, urunadi, depo, baskin_sinif)
# ✅ ALL filtered rows (no pagination)
rows = fetch_all(filtered_outer_sql2(with_order=True), params)
headers = [“urunkodu”, “urunadi”, “depo”, “lokasyon”, “stok”, “baskin_sinif”]
data = _make_xlsx(headers, rows)
filename = “lokasyonsuz_urunler.xlsx”
return StreamingResponse(
iter([data]),
media_type=”application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”,
headers={“Content-Disposition”: f’attachment; filename=”{filename}”‘},
)
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
/////
Sevkiyat Raporu
Cencora
Yükleniyor…
Sütun Ayarları
Sütunları aç/kapat (tarayıcıya kaydedilir).
Görünen
Gizli
Detay
–
Sevkiyat Raporu
–
DB kontrol ediliyor…
–
–
Filtreler
Filtreleri doldur → Raporu Çalıştır. Excel, filtreli tüm satırları indirir.
Gruplama
–
Gruplama (2)
–
–
DATECREATED
HEDEFKODU
MUSTERI
BARCODE
INVOICE
DURUM
SERVIS_TIPI
BOLGE
SERVIS
WAREHOUSE
DEPARTURE
ARRIVAL
SOFOR
ARAC
DELIVERED_LOC
KART
SERVICE_START
DATEMODIFIED
Henüz veri yok.
# main.py (updated)
# Backend for Shipment Report UI
#
# ✅ Includes:
# – /api/health
# – /api/rapor (paged rows + KPIs)
# – /api/rapor/group (grouped counts)
# – /api/rapor/trend-kpi (last N months; Delivered / Awaiting / NotScanned / NotScanned+Delivered + pct deltas)
# – /api/rapor/export.xlsx (exports ALL filtered rows)
#
# ✅ Trend KPI logic (as you requested):
# – Delivered: increase => +%
# – Awaiting Dispatch (Sevk Bekliyor): increase => -% (because decrease is good)
# – Card Not Scanned (Kart Okutulmadı): increase => -% (because decrease is good)
# – Also returns: Not Scanned + Delivered per month
#
# NOTE:
# – You MUST adapt TABLE_NAME / COLUMN mappings to your real DB schema.
# – This file assumes MS SQL via pyodbc (most common in corporate setups).
# – Set env var: DB_CONN (ODBC connection string)
import os
import math
import time
from datetime import date, datetime
from typing import Any, Dict, List, Optional, Tuple
from fastapi import FastAPI, Query, HTTPException
from fastapi.responses import JSONResponse, StreamingResponse
from fastapi.middleware.cors import CORSMiddleware
import pyodbc
from openpyxl import Workbook
APP_NAME = “Cencora Report API”
DB_CONN = os.getenv(“DB_CONN”, “”).strip()
# ======== CONFIG (edit to your DB) ========
# Main view/table for report rows
TABLE_NAME = os.getenv(“REPORT_TABLE”, “PackageProcessBarcode”) # example
# Column map used by SELECT (edit if your names differ)
COLS = {
“datecreated”: “DATECREATED”,
“hedefkodu”: “HEDEFKODU”,
“musteri”: “MUSTERI”,
“barcode”: “BARCODE”,
“invoicecode”: “INVOICECODE”,
“shippingstatus_text”: “SHIPPINGSTATUS_TEXT”,
“servicetype_text”: “SERVICETYPE_TEXT”,
“packageregionnametur”: “PACKAGEREGIONNAMETUR”,
“servicenametur”: “SERVICENAMETUR”,
“packagewarehousenametur”: “PACKAGEWAREHOUSENAMETUR”,
“departuremanagernametur”: “DEPARTUREMANAGERNAMETUR”,
“arrivalmanagernametur”: “ARRIVALMANAGERNAMETUR”,
“servicedrivername”: “SERVICEDRIVERNAME”,
“servicevehicleplate”: “SERVICEVEHICLEPLATE”,
“deliveredlocation”: “DELIVEREDLOCATION”,
“deliveryvalidationtype_text”: “DELIVERYVALIDATIONTYPE_TEXT”,
“servicetimestarts”: “SERVICETIMESTARTS”,
“datemodified”: “DATEMODIFIED”,
}
# These are needed for KPI logic and trend:
RAW_STATUS_COL = os.getenv(“RAW_STATUS_COL”, “SHIPPINGSTATUS”) # numeric or text; used for trend mapping
RAW_CARD_COL = os.getenv(“RAW_CARD_COL”, “DELIVERYVALIDATIONTYPE”) # 1/0 or something similar
STATUS_TEXT_COL = os.getenv(“STATUS_TEXT_COL”, “SHIPPINGSTATUS_TEXT”) # text status for UI filter & KPI cards
CARD_TEXT_COL = os.getenv(“CARD_TEXT_COL”, “DELIVERYVALIDATIONTYPE_TEXT”) # “Kart Okutuldu/Kart Okutulmadı”
DATE_COL = os.getenv(“DATE_COL”, “DATECREATED”) # filter date range on this col
# Status mappings (edit if your codes differ)
# Common approach: Delivered=4, Awaiting=1 (example)
DELIVERED_CODE = int(os.getenv(“DELIVERED_CODE”, “4”))
AWAITING_CODE = int(os.getenv(“AWAITING_CODE”, “1”))
# Card not scanned code: often 0
CARD_NOT_SCANNED_CODE = int(os.getenv(“CARD_NOT_SCANNED_CODE”, “0”))
# =========================================
app = FastAPI(title=APP_NAME)
app.add_middleware(
CORSMiddleware,
allow_origins=[“*”], # adjust for production
allow_credentials=True,
allow_methods=[“*”],
allow_headers=[“*”],
)
def _require_db():
if not DB_CONN:
raise HTTPException(status_code=500, detail=”DB_CONN env var is not set.”)
def db_connect() -> pyodbc.Connection:
_require_db()
# autocommit off for consistency
return pyodbc.connect(DB_CONN, autocommit=False)
def iso_date(s: str) -> date:
try:
return datetime.strptime(s, “%Y-%m-%d”).date()
except Exception:
raise HTTPException(status_code=400, detail=f”Invalid date: {s}. Expected YYYY-MM-DD.”)
def clamp_int(x: Any, lo: int, hi: int, default: int) -> int:
try:
v = int(x)
except Exception:
return default
return max(lo, min(hi, v))
def safe_like_prefix(value: str) -> str:
# for “starts with”
return value.replace(“%”, “”).replace(“_”, “”) + “%”
def build_where(
start: date,
end: date,
barcode: str = “”,
invoice: str = “”,
status: str = “”,
driver: str = “”,
region: str = “”,
warehouse: str = “”,
departure: str = “”,
card: str = “”,
service: str = “”,
) -> Tuple[str, List[Any]]:
“””
Builds WHERE clause and parameters (pyodbc param style: ?)
“””
where = [f”{DATE_COL} >= ? AND {DATE_COL} < ?"]
params: List[Any] = [start, (end.toordinal() and (end.toordinal()))] # placeholder, replaced below
# Use end+1 day as exclusive upper bound
# NOTE: safest is datetime; but date works if DATE_COL is date/datetime
end_exclusive = datetime.combine(end, datetime.min.time()) # midnight end day
end_exclusive = end_exclusive.replace(hour=23, minute=59, second=59, microsecond=999000)
# Better: end + 1 day at 00:00, but some DBs do inclusive; keeping consistent:
# We'll do: DATE >= start AND DATE < (end + 1 day)
end_plus_one = datetime.combine(end, datetime.min.time()) + timedelta(days=1) # noqa
params = [datetime.combine(start, datetime.min.time()), end_plus_one]
def add_contains(col: str, val: str):
where.append(f"{col} LIKE ?")
params.append(f"%{val}%")
def add_equals(col: str, val: str):
where.append(f"{col} = ?")
params.append(val)
def add_starts(col: str, val: str):
where.append(f"{col} LIKE ?")
params.append(safe_like_prefix(val))
if barcode:
add_starts(COLS["barcode"], barcode)
if invoice:
add_equals(COLS["invoicecode"], invoice)
if status:
add_equals(STATUS_TEXT_COL, status)
if driver:
add_contains(COLS["servicedrivername"], driver)
if region:
add_contains(COLS["packageregionnametur"], region)
if warehouse:
add_contains(COLS["packagewarehousenametur"], warehouse)
if departure:
add_contains(COLS["departuremanagernametur"], departure)
if card:
add_equals(CARD_TEXT_COL, card)
if service:
add_contains(COLS["servicenametur"], service)
return " WHERE " + " AND ".join(where), params
def fetch_one(conn: pyodbc.Connection, sql: str, params: List[Any]) -> Any:
cur = conn.cursor()
cur.execute(sql, params)
row = cur.fetchone()
return row[0] if row else None
def fetch_all(conn: pyodbc.Connection, sql: str, params: List[Any]) -> List[pyodbc.Row]:
cur = conn.cursor()
cur.execute(sql, params)
return cur.fetchall()
def pct_change(curr: int, prev: int) -> Optional[int]:
“””
Standard percent change: (curr-prev)/prev * 100
If prev=0 and curr>0 => 100
If prev=0 and curr=0 => 0
“””
if prev is None:
return None
if prev == 0:
if curr == 0:
return 0
return 100
return int(round(((curr – prev) / prev) * 100))
def score_pct(change_pct: Optional[int], positive_is_good: bool) -> Optional[int]:
“””
Converts raw change pct into “goodness” score:
– If positive_is_good=True: score = change_pct
– If positive_is_good=False (decrease is good): score = -change_pct
“””
if change_pct is None:
return None
return change_pct if positive_is_good else (-1 * change_pct)
@app.get(“/api/health”)
def health():
_require_db()
try:
with db_connect() as conn:
v = fetch_one(conn, “SELECT 1”, [])
return {“ok”: True, “db”: True, “ping”: v}
except Exception as e:
return JSONResponse(status_code=500, content={“ok”: False, “db”: False, “error”: str(e)})
@app.get(“/api/rapor”)
def rapor(
start: str = Query(…),
end: str = Query(…),
page: int = Query(1),
page_size: int = Query(50),
barcode: str = Query(“”),
invoice: str = Query(“”),
status: str = Query(“”),
driver: str = Query(“”),
region: str = Query(“”),
warehouse: str = Query(“”),
departure: str = Query(“”),
card: str = Query(“”),
service: str = Query(“”),
):
t0 = time.time()
s = iso_date(start)
e = iso_date(end)
page = clamp_int(page, 1, 1_000_000, 1)
page_size = clamp_int(page_size, 10, 500, 50)
offset = (page – 1) * page_size
where_sql, params = build_where(
s, e,
barcode=barcode, invoice=invoice, status=status, driver=driver, region=region,
warehouse=warehouse, departure=departure, card=card, service=service
)
select_cols = “, “.join([f”{v} AS {k}” for k, v in COLS.items()])
count_sql = f”SELECT COUNT(1) FROM {TABLE_NAME} {where_sql}”
rows_sql = f”””
SELECT {select_cols}
FROM {TABLE_NAME}
{where_sql}
ORDER BY {DATE_COL} DESC
OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
“””
# KPI: group by status text
kpi_sql = f”””
SELECT {STATUS_TEXT_COL} AS durum, COUNT(1) AS adet
FROM {TABLE_NAME}
{where_sql}
GROUP BY {STATUS_TEXT_COL}
ORDER BY COUNT(1) DESC
“””
try:
with db_connect() as conn:
total = int(fetch_one(conn, count_sql, params) or 0)
rows = fetch_all(conn, rows_sql, params + [offset, page_size])
out_rows: List[Dict[str, Any]] = []
for r in rows:
out_rows.append({k: getattr(r, k) for k in COLS.keys()})
kpi_rows = fetch_all(conn, kpi_sql, params)
kpis = []
for rr in kpi_rows:
durum = rr.durum if rr.durum is not None else “-”
adet = int(rr.adet or 0)
yuzde = int(round((adet / total) * 100)) if total else 0
kpis.append({“durum”: durum, “adet”: adet, “yuzde”: yuzde})
ms = int(round((time.time() – t0) * 1000))
return {
“range”: {“start”: start, “end”: end},
“page”: page,
“page_size”: page_size,
“total”: total,
“kpis”: kpis,
“rows”: out_rows,
“ms”: ms,
}
except Exception as ex:
raise HTTPException(status_code=500, detail=str(ex))
@app.get(“/api/rapor/group”)
def rapor_group(
start: str = Query(…),
end: str = Query(…),
group_by: str = Query(“service”),
barcode: str = Query(“”),
invoice: str = Query(“”),
status: str = Query(“”),
driver: str = Query(“”),
region: str = Query(“”),
warehouse: str = Query(“”),
departure: str = Query(“”),
card: str = Query(“”),
service: str = Query(“”),
):
s = iso_date(start)
e = iso_date(end)
# Allowed grouping fields
group_map = {
“service”: COLS[“servicenametur”],
“customer”: COLS[“musteri”],
“region”: COLS[“packageregionnametur”],
“warehouse”: COLS[“packagewarehousenametur”],
“departure”: COLS[“departuremanagernametur”],
“card”: CARD_TEXT_COL,
“status”: STATUS_TEXT_COL,
“driver”: COLS[“servicedrivername”],
}
if group_by not in group_map:
raise HTTPException(status_code=400, detail=f”Invalid group_by: {group_by}”)
group_col = group_map[group_by]
where_sql, params = build_where(
s, e,
barcode=barcode, invoice=invoice, status=status, driver=driver, region=region,
warehouse=warehouse, departure=departure, card=card, service=service
)
total_sql = f”SELECT COUNT(1) FROM {TABLE_NAME} {where_sql}”
grp_sql = f”””
SELECT
COALESCE(CAST({group_col} AS NVARCHAR(400)), ‘-‘) AS label,
COUNT(1) AS adet
FROM {TABLE_NAME}
{where_sql}
GROUP BY {group_col}
ORDER BY COUNT(1) DESC
“””
try:
with db_connect() as conn:
total = int(fetch_one(conn, total_sql, params) or 0)
rows = fetch_all(conn, grp_sql, params)
items = []
for r in rows:
adet = int(r.adet or 0)
yuzde = int(round((adet / total) * 100)) if total else 0
items.append({“label”: r.label, “adet”: adet, “yuzde”: yuzde})
return {“total”: total, “group_by”: group_by, “items”: items}
except Exception as ex:
raise HTTPException(status_code=500, detail=str(ex))
@app.get(“/api/rapor/trend-kpi”)
def trend_kpi(months: int = Query(6, ge=1, le=24)):
“””
Returns last N months KPIs:
– teslim_edildi
– sevk_bekliyor
– kart_okutulmadi
– teslim_kart_okutulmadi (Delivered AND Card Not Scanned)
plus month-over-month changes:
– teslim_edildi_pct (increase good)
– sevk_bekliyor_pct (decrease good -> we return raw change, UI flips if needed)
– kart_okutulmadi_pct (decrease good -> raw change, UI flips if needed)
“””
# We group by month from DATE_COL.
# MSSQL: DATENAME(YEAR, d), DATENAME(MONTH, d) etc.
# We’ll use: FORMAT(DATE_COL, ‘yyyy-MM’) for stable ordering (SQL Server 2012+).
# If your SQL Server is older, replace FORMAT with CONVERT.
months = clamp_int(months, 1, 24, 6)
sql = f”””
WITH x AS (
SELECT
FORMAT({DATE_COL}, ‘yyyy-MM’) AS ym,
SUM(CASE WHEN {RAW_STATUS_COL} = ? THEN 1 ELSE 0 END) AS teslim_edildi,
SUM(CASE WHEN {RAW_STATUS_COL} = ? THEN 1 ELSE 0 END) AS sevk_bekliyor,
SUM(CASE WHEN {RAW_CARD_COL} = ? THEN 1 ELSE 0 END) AS kart_okutulmadi,
SUM(CASE WHEN {RAW_STATUS_COL} = ? AND {RAW_CARD_COL} = ? THEN 1 ELSE 0 END) AS teslim_kart_okutulmadi
FROM {TABLE_NAME}
WHERE {DATE_COL} >= DATEADD(MONTH, -?, CAST(GETDATE() AS DATE))
GROUP BY FORMAT({DATE_COL}, ‘yyyy-MM’)
)
SELECT TOP (?)
ym,
teslim_edildi,
sevk_bekliyor,
kart_okutulmadi,
teslim_kart_okutulmadi
FROM x
ORDER BY ym DESC
“””
params = [
DELIVERED_CODE,
AWAITING_CODE,
CARD_NOT_SCANNED_CODE,
DELIVERED_CODE,
CARD_NOT_SCANNED_CODE,
months,
months,
]
try:
with db_connect() as conn:
rows = fetch_all(conn, sql, params)
# rows are DESC; we want oldest->newest for UI table
rows = list(reversed(rows))
out_months: List[Dict[str, Any]] = []
prev = None
for r in rows:
curr = {
“ay”: r.ym,
“teslim_edildi”: int(r.teslim_edildi or 0),
“sevk_bekliyor”: int(r.sevk_bekliyor or 0),
“kart_okutulmadi”: int(r.kart_okutulmadi or 0),
“teslim_kart_okutulmadi”: int(r.teslim_kart_okutulmadi or 0),
“degisim”: {
# Raw change percent. UI can flip sign for risk metrics.
“teslim_edildi_pct”: None,
“sevk_bekliyor_pct”: None,
“kart_okutulmadi_pct”: None,
}
}
if prev is not None:
curr[“degisim”][“teslim_edildi_pct”] = pct_change(curr[“teslim_edildi”], prev[“teslim_edildi”])
curr[“degisim”][“sevk_bekliyor_pct”] = pct_change(curr[“sevk_bekliyor”], prev[“sevk_bekliyor”])
curr[“degisim”][“kart_okutulmadi_pct”] = pct_change(curr[“kart_okutulmadi”], prev[“kart_okutulmadi”])
out_months.append(curr)
prev = curr
return {“months”: out_months}
except Exception as ex:
raise HTTPException(status_code=500, detail=str(ex))
@app.get(“/api/rapor/export.xlsx”)
def export_xlsx(
start: str = Query(…),
end: str = Query(…),
barcode: str = Query(“”),
invoice: str = Query(“”),
status: str = Query(“”),
driver: str = Query(“”),
region: str = Query(“”),
warehouse: str = Query(“”),
departure: str = Query(“”),
card: str = Query(“”),
service: str = Query(“”),
):
s = iso_date(start)
e = iso_date(end)
where_sql, params = build_where(
s, e,
barcode=barcode, invoice=invoice, status=status, driver=driver, region=region,
warehouse=warehouse, departure=departure, card=card, service=service
)
select_cols = “, “.join([f”{v} AS {k}” for k, v in COLS.items()])
sql = f”””
SELECT {select_cols}
FROM {TABLE_NAME}
{where_sql}
ORDER BY {DATE_COL} DESC
“””
try:
with db_connect() as conn:
rows = fetch_all(conn, sql, params)
wb = Workbook()
ws = wb.active
ws.title = “Report”
headers = list(COLS.keys())
ws.append(headers)
for r in rows:
ws.append([getattr(r, h) for h in headers])
def iter_bytes():
from io import BytesIO
bio = BytesIO()
wb.save(bio)
bio.seek(0)
yield from bio
filename = f”sevkiyat_raporu_{start}_to_{end}.xlsx”
return StreamingResponse(
iter_bytes(),
media_type=”application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”,
headers={“Content-Disposition”: f’attachment; filename=”{filename}”‘},
)
except Exception as ex:
raise HTTPException(status_code=500, detail=str(ex))
# ======== Fix for timedelta import (used in build_where) ========
from datetime import timedelta # keep at end to avoid clutter
SELECT CI.ITEMID URUNKODU,
CI.NAMETUR URUNADI,
CN.NAMETUR DEPO,
LOK.DISPLAYNAME LOKASYON,
SUM(SP.QUANTITY) STOK,
CC.NAMETUR BASKIN_SINIF
FROM CATALOG_ITEM CI
INNER JOIN STOCK_PARTY SP
ON CI.ITEMID = SP.ITEMID
LEFT JOIN CATALOG_CLASS CC
ON CI.DOMINANTCLASSID = CC.CLASSID
INNER JOIN COLLABORATION_NODE CN
ON SP.WAREHOUSEID = CN.NODEID
LEFT JOIN (SELECT DISTINCT CII.ITEMID, SC.WAREHOUSEID, SC.DISPLAYNAME
FROM CATALOG_INVENTORYITEM CII
INNER JOIN STOCK_STOCKINVENTORYITEM SSII
ON CII.INVENTORYITEMID = SSII.INVENTORYITEMID
INNER JOIN STOCK_CONTAINER SC
ON SSII.EXITCONTAINERID = SC.CONTAINERID) LOK
ON CI.ITEMID = LOK.ITEMID
AND SP.WAREHOUSEID = LOK.WAREHOUSEID
WHERE LOK.ITEMID IS NULL
GROUP BY CI.ITEMID, CI.NAMETUR, CN.NAMETUR, LOK.DISPLAYNAME, CC.NAMETUR
HAVING(SUM(SP.QUANTITY) > 0)
# app/main.py
import os
from datetime import date, datetime, timedelta
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from app.db import fetch_all, fetch_one
load_dotenv()
app = FastAPI(title=”Panel Rapor”, version=”1.0.0″)
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, “templates”)
templates = Jinja2Templates(directory=TEMPLATES_DIR)
def _date_start_dt(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0)
def _date_end_dt_exclusive(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0) + timedelta(days=1)
def _first_day_of_month(dt: datetime) -> datetime:
return datetime(dt.year, dt.month, 1, 0, 0, 0)
def _add_months(dt: datetime, months: int) -> datetime:
y = dt.year + (dt.month – 1 + months) // 12
m = (dt.month – 1 + months) % 12 + 1
return datetime(y, m, 1, 0, 0, 0)
# =========================
# ✅ YOUR INNER SELECT (RAPOR 1)
# =========================
BASE_INNER_SQL = “””
SELECT
(
SELECT MAX(TO_NUMBER(pv.VALUETEXTTUR))
FROM CONTENTCC_C_PROPERTYVALUE pv
JOIN CONTENTCC_C_TPROPERTYVALUE tpv
ON pv.PROPERTYVALUEID = tpv.PROPERTYVALUEID
WHERE pv.PROPERTYID = 19
AND tpv.TITLEID = tl.TITLEID
) AS HEDEFKODU,
(pl.NAME || ‘,’ || adr.DISTRICTNAME || ‘,’ || adr.CITYNAME) AS MUSTERI,
ppb.DATECREATED,
srv.TIMESTARTS AS SERVICETIMESTARTS,
ppb.DATEMODIFIED,
(
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) AS INVOICECODE,
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN ‘Kart Okutuldu’
WHEN 0 THEN ‘Kart Okutulmadı’
ELSE NULL
END AS DELIVERYVALIDATIONTYPE_TEXT,
ppb.BARCODE,
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN ‘Sevk Bekliyor’
WHEN 1 THEN ‘Sevk Kontrolü Yapıldı’
WHEN 2 THEN ‘Sevk Başladı’
WHEN 3 THEN ‘Transfer Edildi’
WHEN 4 THEN ‘Teslim Edildi’
WHEN 5 THEN ‘Teslim Edilemedi’
WHEN 99 THEN ‘İptal Edildi’
ELSE NULL
END AS SHIPPINGSTATUS_TEXT,
CASE srv.ISEXPRESSSERVICE
WHEN 1 THEN ‘Hızlı Teslimat’
WHEN 0 THEN ‘Servis’
ELSE NULL
END AS SERVICETYPE_TEXT,
reg.NAMETUR AS PACKAGEREGIONNAMETUR,
srv.NAMETUR AS SERVICENAMETUR,
wh.NAMETUR AS PACKAGEWAREHOUSENAMETUR,
dep.NAMETUR AS DEPARTUREMANAGERNAMETUR,
arr.NAMETUR AS ARRIVALMANAGERNAMETUR,
drv.NAME AS SERVICEDRIVERNAME,
veh.PLATE AS SERVICEVEHICLEPLATE,
(
SELECT DeliveredPoint
FROM (
SELECT sp.DeliveredPoint,
ROW_NUMBER() OVER (ORDER BY sp.DeliveredTime DESC) rn
FROM Transportation_ServicePackage sp
WHERE sp.PackagePackBarcodeID = ppb.PackagePackBarcodeID
AND sp.IsDelivered = 1
)
WHERE rn = 1
) AS DELIVEREDLOCATION
FROM TRS_PACKAGEPACKBARCODE ppb
JOIN TRANSPORTATION_PACKAGE pkg
ON pkg.PACKAGEID = ppb.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION reg
ON reg.REGIONID = pkg.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE srv
ON srv.SERVICEID = ppb.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE veh
ON veh.VEHICLEID = srv.VEHICLEID
LEFT JOIN COLLABORATION_NODE wh
ON wh.NODEID = pkg.WAREHOUSEID
LEFT JOIN COLLABORATION_NODE dep
ON dep.NODEID = ppb.DEPARTUREMANAGERID
LEFT JOIN COLLABORATION_NODE arr
ON arr.NODEID = ppb.ARRIVALMANAGERID
LEFT JOIN COLLABORATION_EMPLOYEE drv
ON drv.EMPLOYEEID = srv.DRIVERID
LEFT JOIN TRADING_LINK pl
ON pl.LINKID = pkg.LINKID
INNER JOIN TRADING_LINK tl
ON tl.LINKID = pkg.LINKID
LEFT JOIN CONTACTCC_CONTACT c
ON c.CONTACTID = pl.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS adr
ON adr.ADDRESSID = c.DEFAULTADDRESSID
WHERE
pl.NAME NOT LIKE ‘%All%’
AND reg.NAMETUR IS NOT NULL
AND reg.NAMETUR <> ‘NULL’
AND ppb.DATECREATED >= :start_date
AND ppb.DATECREATED < :end_date
"""
def filtered_outer_sql(with_order: bool) -> str:
where = “””
WHERE 1=1
AND (:barcode IS NULL OR LOWER(t.barcode) LIKE LOWER(:barcode_like))
AND (:invoice IS NULL OR NVL(LOWER(t.invoicecode),”) = LOWER(:invoice))
AND (:status IS NULL OR t.shippingstatus_text = :status)
AND (:driver IS NULL OR LOWER(NVL(t.servicedrivername,”)) LIKE LOWER(:driver_like))
AND (:region IS NULL OR LOWER(NVL(t.packageregionnametur,”)) LIKE LOWER(:region_like))
AND (:warehouse IS NULL OR LOWER(NVL(t.packagewarehousenametur,”)) LIKE LOWER(:warehouse_like))
AND (:departure IS NULL OR LOWER(NVL(t.departuremanagernametur,”)) LIKE LOWER(:departure_like))
AND (:card IS NULL OR t.deliveryvalidationtype_text = :card)
AND (:service IS NULL OR LOWER(NVL(t.servicenametur,”)) LIKE LOWER(:service_like))
“””
order = ” ORDER BY t.datecreated DESC ” if with_order else “”
return f”””
SELECT *
FROM (
{BASE_INNER_SQL}
) t
{where}
{order}
“””
def count_sql() -> str:
return f”SELECT COUNT(1) AS total FROM ( {filtered_outer_sql(with_order=False)} )”
def paged_sql() -> str:
return f”””
SELECT * FROM (
{filtered_outer_sql(with_order=True)}
)
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
“””
@app.get(“/”, response_class=HTMLResponse)
@app.get(“/rapor”, response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse(“rapor.html”, {“request”: request})
# ✅ NEW PAGE: RAPOR 2
@app.get(“/rapor2”, response_class=HTMLResponse)
def rapor2_page(request: Request):
return templates.TemplateResponse(“rapor2.html”, {“request”: request})
@app.get(“/api/health”)
def health():
try:
row = fetch_one(“SELECT 1 AS ok FROM dual”)
return {“db”: “ok”, “ok”: row.get(“ok”, 1) if row else 1}
except Exception as e:
return JSONResponse(status_code=500, content={“db”: “fail”, “error”: str(e)})
def build_params(
start: date,
end: date,
barcode: str | None,
invoice: str | None,
status: str | None,
driver: str | None,
region: str | None,
warehouse: str | None,
departure: str | None,
card: str | None,
service: str | None,
) -> dict:
start_dt = _date_start_dt(start)
end_dt = _date_end_dt_exclusive(end)
barcode = (barcode or “”).strip() or None
invoice = (invoice or “”).strip() or None
status = (status or “”).strip() or None
driver = (driver or “”).strip() or None
region = (region or “”).strip() or None
warehouse = (warehouse or “”).strip() or None
departure = (departure or “”).strip() or None
card = (card or “”).strip() or None
service = (service or “”).strip() or None
return {
“start_date”: start_dt,
“end_date”: end_dt,
“barcode”: barcode,
“barcode_like”: f”{barcode}%” if barcode else None,
“invoice”: invoice,
“status”: status,
“driver”: driver,
“driver_like”: f”%{driver}%” if driver else None,
“region”: region,
“region_like”: f”%{region}%” if region else None,
“warehouse”: warehouse,
“warehouse_like”: f”%{warehouse}%” if warehouse else None,
“departure”: departure,
“departure_like”: f”%{departure}%” if departure else None,
“card”: card,
“service”: service,
“service_like”: f”%{service}%” if service else None,
}
@app.get(“/api/rapor”)
def rapor_data(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barcode: str | None = None,
invoice: str | None = None,
status: str | None = None,
driver: str | None = None,
region: str | None = None,
warehouse: str | None = None,
departure: str | None = None,
card: str | None = None,
service: str | None = None,
page: int = 1,
page_size: int = 50,
):
try:
if page < 1:
page = 1
if page_size < 1:
page_size = 50
if page_size > 500:
page_size = 500
params = build_params(start, end, barcode, invoice, status, driver, region, warehouse, departure, card, service)
total_row = fetch_one(count_sql(), params)
total = int(total_row[“total”]) if total_row and total_row.get(“total”) is not None else 0
params_items = dict(params)
params_items[“offset”] = (page – 1) * page_size
params_items[“limit”] = page_size
rows = fetch_all(paged_sql(), params_items)
kpi_sql = f”””
SELECT shippingstatus_text AS durum, COUNT(1) AS adet
FROM ( {filtered_outer_sql(with_order=False)} )
GROUP BY shippingstatus_text
ORDER BY COUNT(1) DESC
“””
kpi_rows = fetch_all(kpi_sql, params)
kpis = []
for r in kpi_rows:
adet = int(r.get(“adet”) or 0)
yuzde = round((adet / total) * 100, 2) if total else 0
kpis.append({“durum”: r.get(“durum”), “adet”: adet, “yuzde”: yuzde})
return {
“range”: {“start”: str(start), “end”: str(end)},
“page”: page,
“page_size”: page_size,
“total”: total,
“kpis”: kpis,
“rows”: rows,
}
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
@app.get(“/api/rapor/group”)
def rapor_group(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barcode: str | None = None,
invoice: str | None = None,
status: str | None = None,
driver: str | None = None,
region: str | None = None,
warehouse: str | None = None,
departure: str | None = None,
card: str | None = None,
service: str | None = None,
group_by: str = “service”,
):
try:
params = build_params(start, end, barcode, invoice, status, driver, region, warehouse, departure, card, service)
group_map = {
“service”: “t.servicenametur”,
“customer”: “t.musteri”,
“region”: “t.packageregionnametur”,
“warehouse”: “t.packagewarehousenametur”,
“departure”: “t.departuremanagernametur”,
“card”: “t.deliveryvalidationtype_text”,
“status”: “t.shippingstatus_text”,
“driver”: “t.servicedrivername”,
}
col = group_map.get(group_by, “t.servicenametur”)
sql = f”””
SELECT
NVL({col}, ‘-‘) AS label,
COUNT(1) AS adet
FROM (
{filtered_outer_sql(with_order=False)}
) t
GROUP BY {col}
ORDER BY COUNT(1) DESC
“””
items_raw = fetch_all(sql, params)
total = sum(int(x.get(“adet”) or 0) for x in items_raw) or 0
items = []
for x in items_raw[:200]:
adet = int(x.get(“adet”) or 0)
yuzde = round((adet / total) * 100, 2) if total else 0
items.append({“label”: x.get(“label”), “adet”: adet, “yuzde”: yuzde})
return {“group_by”: group_by, “total”: total, “items”: items}
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
# ============================================================
# ✅ TREND KPI (unchanged from your “updated” version)
# ============================================================
@app.get(“/api/rapor/trend-kpi”)
def rapor_trend_kpi(
months: int = Query(default=6, ge=1, le=24),
start: date | None = None,
end: date | None = None,
):
try:
if start and end:
use_start = _date_start_dt(start)
use_end = _date_end_dt_exclusive(end)
else:
now = datetime.now()
cur_month = _first_day_of_month(now)
start_month = _add_months(cur_month, -(months – 1))
end_month_excl = _add_months(cur_month, 1)
use_start = start_month
use_end = end_month_excl
sql = “””
SELECT
TO_CHAR(TRUNC(ppb.DATECREATED, ‘MM’), ‘YYYY-MM’) AS ay,
SUM(CASE WHEN ppb.SHIPPINGSTATUS = 4 THEN 1 ELSE 0 END) AS teslim_edildi,
SUM(CASE WHEN ppb.SHIPPINGSTATUS = 0 THEN 1 ELSE 0 END) AS sevk_bekliyor,
SUM(CASE WHEN ppb.DELIVERYVALIDATIONTYPE = 0 THEN 1 ELSE 0 END) AS kart_okutulmadi
FROM TRS_PACKAGEPACKBARCODE ppb
WHERE ppb.DATECREATED >= :start_dt
AND ppb.DATECREATED < :end_dt
GROUP BY TRUNC(ppb.DATECREATED, 'MM')
ORDER BY TRUNC(ppb.DATECREATED, 'MM')
"""
rows = fetch_all(sql, {"start_dt": use_start, "end_dt": use_end})
def pct(curr: int, prev: int):
if prev == 0:
return None
return round(((curr - prev) / prev) * 100, 2)
out = []
prev = None
for r in rows:
cur = {
"ay": r.get("ay"),
"teslim_edildi": int(r.get("teslim_edildi") or 0),
"sevk_bekliyor": int(r.get("sevk_bekliyor") or 0),
"kart_okutulmadi": int(r.get("kart_okutulmadi") or 0),
}
if prev:
cur["degisim"] = {
"teslim_edildi_pct": pct(cur["teslim_edildi"], prev["teslim_edildi"]),
"sevk_bekliyor_pct": pct(cur["sevk_bekliyor"], prev["sevk_bekliyor"]),
"kart_okutulmadi_pct": pct(cur["kart_okutulmadi"], prev["kart_okutulmadi"]),
}
else:
cur["degisim"] = {
"teslim_edildi_pct": None,
"sevk_bekliyor_pct": None,
"kart_okutulmadi_pct": None,
}
out.append(cur)
prev = cur
return {"months": out}
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
@app.get("/api/trend-kpi")
def trend_kpi_alias(
months: int = Query(default=6, ge=1, le=24),
start: date | None = None,
end: date | None = None,
):
return rapor_trend_kpi(months=months, start=start, end=end)
def _make_xlsx(headers: list[str], rows: list[dict], sheet_name: str = "Rapor") -> bytes:
wb = Workbook()
ws = wb.active
ws.title = sheet_name
ws.append(headers)
for r in rows:
ws.append([r.get(h) for h in headers])
for i, h in enumerate(headers, start=1):
max_len = len(h)
for rr in rows[:300]:
val = rr.get(h)
if val is None:
continue
s = str(val)
if len(s) > max_len:
max_len = len(s)
ws.column_dimensions[get_column_letter(i)].width = max(12, min(48, max_len + 2))
import io
bio = io.BytesIO()
wb.save(bio)
return bio.getvalue()
@app.get(“/api/rapor/export.xlsx”)
def export_xlsx(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barcode: str | None = None,
invoice: str | None = None,
status: str | None = None,
driver: str | None = None,
region: str | None = None,
warehouse: str | None = None,
departure: str | None = None,
card: str | None = None,
service: str | None = None,
):
try:
params = build_params(start, end, barcode, invoice, status, driver, region, warehouse, departure, card, service)
sql = filtered_outer_sql(with_order=True)
rows = fetch_all(sql, params)
headers = [
“datecreated”,
“hedefkodu”,
“musteri”,
“barcode”,
“invoicecode”,
“shippingstatus_text”,
“servicetype_text”,
“packageregionnametur”,
“servicenametur”,
“packagewarehousenametur”,
“departuremanagernametur”,
“arrivalmanagernametur”,
“servicedrivername”,
“servicevehicleplate”,
“deliveredlocation”,
“deliveryvalidationtype_text”,
“servicetimestarts”,
“datemodified”,
]
data = _make_xlsx(headers, rows, sheet_name=”Sevkiyat”)
filename = f”sevkiyat_{start}_{end}.xlsx”
return StreamingResponse(
iter([data]),
media_type=”application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”,
headers={“Content-Disposition”: f’attachment; filename=”{filename}”‘},
)
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
@app.get(“/api/export.xlsx”)
def export_xlsx_alias(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barcode: str | None = None,
invoice: str | None = None,
status: str | None = None,
driver: str | None = None,
region: str | None = None,
warehouse: str | None = None,
departure: str | None = None,
card: str | None = None,
service: str | None = None,
):
return export_xlsx(
start=start, end=end,
barcode=barcode, invoice=invoice, status=status,
driver=driver, region=region, warehouse=warehouse,
departure=departure, card=card, service=service
)
# ============================================================
# ✅ RAPOR 2: “Lokasyonu Tanımsız Ürünler”
# – /rapor2 -> rapor2.html
# – /api/rapor2 -> JSON
# – /api/rapor2/export.xlsx -> Excel
# ============================================================
RAPOR2_BASE_SQL = “””
SELECT
CI.ITEMID AS urunkodu,
CI.NAMETUR AS urunadi,
CN.NAMETUR AS depo,
LOK.DISPLAYNAME AS lokasyon,
SUM(SP.QUANTITY) AS stok,
CC.NAMETUR AS baskin_sinif
FROM CATALOG_ITEM CI
INNER JOIN STOCK_PARTY SP
ON CI.ITEMID = SP.ITEMID
LEFT JOIN CATALOG_CLASS CC
ON CI.DOMINANTCLASSID = CC.CLASSID
INNER JOIN COLLABORATION_NODE CN
ON SP.WAREHOUSEID = CN.NODEID
LEFT JOIN (
SELECT DISTINCT
CII.ITEMID,
SC.WAREHOUSEID,
SC.DISPLAYNAME
FROM CATALOG_INVENTORYITEM CII
INNER JOIN STOCK_STOCKINVENTORYITEM SSII
ON CII.INVENTORYITEMID = SSII.INVENTORYITEMID
INNER JOIN STOCK_CONTAINER SC
ON SSII.EXITCONTAINERID = SC.CONTAINERID
) LOK
ON CI.ITEMID = LOK.ITEMID
AND SP.WAREHOUSEID = LOK.WAREHOUSEID
WHERE LOK.ITEMID IS NULL
GROUP BY CI.ITEMID, CI.NAMETUR, CN.NAMETUR, LOK.DISPLAYNAME, CC.NAMETUR
HAVING (SUM(SP.QUANTITY) > 0)
“””
def rapor2_filtered_sql(with_order: bool) -> str:
where = “””
WHERE 1=1
AND (:urunkodu IS NULL OR LOWER(t.urunkodu) LIKE LOWER(:urunkodu_like))
AND (:urunadi IS NULL OR LOWER(t.urunadi) LIKE LOWER(:urunadi_like))
AND (:depo IS NULL OR LOWER(t.depo) LIKE LOWER(:depo_like))
AND (:baskin_sinif IS NULL OR LOWER(NVL(t.baskin_sinif,”)) LIKE LOWER(:baskin_sinif_like))
“””
order = ” ORDER BY t.stok DESC ” if with_order else “”
return f”””
SELECT *
FROM (
{RAPOR2_BASE_SQL}
) t
{where}
{order}
“””
def rapor2_count_sql() -> str:
return f”SELECT COUNT(1) AS total FROM ( {rapor2_filtered_sql(with_order=False)} )”
def rapor2_paged_sql() -> str:
return f”””
SELECT * FROM (
{rapor2_filtered_sql(with_order=True)}
)
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
“””
def rapor2_params(
urunkodu: str | None,
urunadi: str | None,
depo: str | None,
baskin_sinif: str | None,
) -> dict:
urunkodu = (urunkodu or “”).strip() or None
urunadi = (urunadi or “”).strip() or None
depo = (depo or “”).strip() or None
baskin_sinif = (baskin_sinif or “”).strip() or None
return {
“urunkodu”: urunkodu,
“urunkodu_like”: f”{urunkodu}%” if urunkodu else None,
“urunadi”: urunadi,
“urunadi_like”: f”%{urunadi}%” if urunadi else None,
“depo”: depo,
“depo_like”: f”%{depo}%” if depo else None,
“baskin_sinif”: baskin_sinif,
“baskin_sinif_like”: f”%{baskin_sinif}%” if baskin_sinif else None,
}
@app.get(“/api/rapor2”)
def rapor2_data(
urunkodu: str | None = None,
urunadi: str | None = None,
depo: str | None = None,
baskin_sinif: str | None = None,
page: int = 1,
page_size: int = 50,
):
try:
if page < 1:
page = 1
if page_size < 1:
page_size = 50
if page_size > 500:
page_size = 500
params = rapor2_params(urunkodu, urunadi, depo, baskin_sinif)
total_row = fetch_one(rapor2_count_sql(), params)
total = int(total_row[“total”]) if total_row and total_row.get(“total”) is not None else 0
params_items = dict(params)
params_items[“offset”] = (page – 1) * page_size
params_items[“limit”] = page_size
rows = fetch_all(rapor2_paged_sql(), params_items)
return {
“page”: page,
“page_size”: page_size,
“total”: total,
“rows”: rows,
}
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
@app.get(“/api/rapor2/export.xlsx”)
def rapor2_export_xlsx(
urunkodu: str | None = None,
urunadi: str | None = None,
depo: str | None = None,
baskin_sinif: str | None = None,
):
try:
params = rapor2_params(urunkodu, urunadi, depo, baskin_sinif)
rows = fetch_all(rapor2_filtered_sql(with_order=True), params)
headers = [“urunkodu”, “urunadi”, “depo”, “lokasyon”, “stok”, “baskin_sinif”]
data = _make_xlsx(headers, rows, sheet_name=”Lokasyonsuz_Urunler”)
filename = f”lokasyonsuz_urunler_{date.today()}.xlsx”
return StreamingResponse(
iter([data]),
media_type=”application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”,
headers={“Content-Disposition”: f’attachment; filename=”{filename}”‘},
)
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
Sevkiyat Raporu
Cencora
Yükleniyor…
Sütun Ayarları
Sütunları aç/kapat (tarayıcıya kaydedilir).
Görünen
Gizli
Detay
–
Sevkiyat Raporu
–
DB kontrol ediliyor…
–
–
Filtreler
Filtreleri doldur → Raporu Çalıştır. Excel, filtreli tüm satırları indirir.
Gruplama
–
Gruplama (2)
–
–
DATECREATED
HEDEFKODU
MUSTERI
BARCODE
INVOICE
DURUM
SERVIS_TIPI
BOLGE
SERVIS
WAREHOUSE
DEPARTURE
ARRIVAL
SOFOR
ARAC
DELIVERED_LOC
KART
SERVICE_START
DATEMODIFIED
Henüz veri yok.
—
Güncel Main
# app/main.py
import os
from datetime import date, datetime, timedelta
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from app.db import fetch_all, fetch_one
load_dotenv()
app = FastAPI(title=”Panel Rapor”, version=”1.0.0″)
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, “templates”)
templates = Jinja2Templates(directory=TEMPLATES_DIR)
def _date_start_dt(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0)
def _date_end_dt_exclusive(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0) + timedelta(days=1)
def _first_day_of_month(dt: datetime) -> datetime:
return datetime(dt.year, dt.month, 1, 0, 0, 0)
def _add_months(dt: datetime, months: int) -> datetime:
# safe month add
y = dt.year + (dt.month – 1 + months) // 12
m = (dt.month – 1 + months) % 12 + 1
return datetime(y, m, 1, 0, 0, 0)
# =========================
# ✅ YOUR INNER SELECT
# =========================
BASE_INNER_SQL = “””
SELECT
(
SELECT MAX(TO_NUMBER(pv.VALUETEXTTUR))
FROM CONTENTCC_C_PROPERTYVALUE pv
JOIN CONTENTCC_C_TPROPERTYVALUE tpv
ON pv.PROPERTYVALUEID = tpv.PROPERTYVALUEID
WHERE pv.PROPERTYID = 19
AND tpv.TITLEID = tl.TITLEID
) AS HEDEFKODU,
(pl.NAME || ‘,’ || adr.DISTRICTNAME || ‘,’ || adr.CITYNAME) AS MUSTERI,
ppb.DATECREATED,
srv.TIMESTARTS AS SERVICETIMESTARTS,
ppb.DATEMODIFIED,
(
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) AS INVOICECODE,
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN ‘Kart Okutuldu’
WHEN 0 THEN ‘Kart Okutulmadı’
ELSE NULL
END AS DELIVERYVALIDATIONTYPE_TEXT,
ppb.BARCODE,
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN ‘Sevk Bekliyor’
WHEN 1 THEN ‘Sevk Kontrolü Yapıldı’
WHEN 2 THEN ‘Sevk Başladı’
WHEN 3 THEN ‘Transfer Edildi’
WHEN 4 THEN ‘Teslim Edildi’
WHEN 5 THEN ‘Teslim Edilemedi’
WHEN 99 THEN ‘İptal Edildi’
ELSE NULL
END AS SHIPPINGSTATUS_TEXT,
CASE srv.ISEXPRESSSERVICE
WHEN 1 THEN ‘Hızlı Teslimat’
WHEN 0 THEN ‘Servis’
ELSE NULL
END AS SERVICETYPE_TEXT,
reg.NAMETUR AS PACKAGEREGIONNAMETUR,
srv.NAMETUR AS SERVICENAMETUR,
wh.NAMETUR AS PACKAGEWAREHOUSENAMETUR,
dep.NAMETUR AS DEPARTUREMANAGERNAMETUR,
arr.NAMETUR AS ARRIVALMANAGERNAMETUR,
drv.NAME AS SERVICEDRIVERNAME,
veh.PLATE AS SERVICEVEHICLEPLATE,
(
SELECT DeliveredPoint
FROM (
SELECT sp.DeliveredPoint,
ROW_NUMBER() OVER (ORDER BY sp.DeliveredTime DESC) rn
FROM Transportation_ServicePackage sp
WHERE sp.PackagePackBarcodeID = ppb.PackagePackBarcodeID
AND sp.IsDelivered = 1
)
WHERE rn = 1
) AS DELIVEREDLOCATION
FROM TRS_PACKAGEPACKBARCODE ppb
JOIN TRANSPORTATION_PACKAGE pkg
ON pkg.PACKAGEID = ppb.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION reg
ON reg.REGIONID = pkg.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE srv
ON srv.SERVICEID = ppb.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE veh
ON veh.VEHICLEID = srv.VEHICLEID
LEFT JOIN COLLABORATION_NODE wh
ON wh.NODEID = pkg.WAREHOUSEID
LEFT JOIN COLLABORATION_NODE dep
ON dep.NODEID = ppb.DEPARTUREMANAGERID
LEFT JOIN COLLABORATION_NODE arr
ON arr.NODEID = ppb.ARRIVALMANAGERID
LEFT JOIN COLLABORATION_EMPLOYEE drv
ON drv.EMPLOYEEID = srv.DRIVERID
LEFT JOIN TRADING_LINK pl
ON pl.LINKID = pkg.LINKID
INNER JOIN TRADING_LINK tl
ON tl.LINKID = pkg.LINKID
LEFT JOIN CONTACTCC_CONTACT c
ON c.CONTACTID = pl.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS adr
ON adr.ADDRESSID = c.DEFAULTADDRESSID
WHERE
pl.NAME NOT LIKE ‘%All%’
AND reg.NAMETUR IS NOT NULL
AND reg.NAMETUR <> ‘NULL’
AND ppb.DATECREATED >= :start_date
AND ppb.DATECREATED < :end_date
"""
def filtered_outer_sql(with_order: bool) -> str:
where = “””
WHERE 1=1
AND (:barcode IS NULL OR LOWER(t.barcode) LIKE LOWER(:barcode_like))
AND (:invoice IS NULL OR NVL(LOWER(t.invoicecode),”) = LOWER(:invoice))
AND (:status IS NULL OR t.shippingstatus_text = :status)
AND (:driver IS NULL OR LOWER(NVL(t.servicedrivername,”)) LIKE LOWER(:driver_like))
AND (:region IS NULL OR LOWER(NVL(t.packageregionnametur,”)) LIKE LOWER(:region_like))
AND (:warehouse IS NULL OR LOWER(NVL(t.packagewarehousenametur,”)) LIKE LOWER(:warehouse_like))
AND (:departure IS NULL OR LOWER(NVL(t.departuremanagernametur,”)) LIKE LOWER(:departure_like))
AND (:card IS NULL OR t.deliveryvalidationtype_text = :card)
AND (:service IS NULL OR LOWER(NVL(t.servicenametur,”)) LIKE LOWER(:service_like))
“””
order = ” ORDER BY t.datecreated DESC ” if with_order else “”
return f”””
SELECT *
FROM (
{BASE_INNER_SQL}
) t
{where}
{order}
“””
def count_sql() -> str:
return f”SELECT COUNT(1) AS total FROM ( {filtered_outer_sql(with_order=False)} )”
def paged_sql() -> str:
return f”””
SELECT * FROM (
{filtered_outer_sql(with_order=True)}
)
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
“””
@app.get(“/”, response_class=HTMLResponse)
@app.get(“/rapor”, response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse(“rapor.html”, {“request”: request})
@app.get(“/api/health”)
def health():
try:
row = fetch_one(“SELECT 1 AS ok FROM dual”)
return {“db”: “ok”, “ok”: row.get(“ok”, 1) if row else 1}
except Exception as e:
return JSONResponse(status_code=500, content={“db”: “fail”, “error”: str(e)})
def build_params(
start: date,
end: date,
barcode: str | None,
invoice: str | None,
status: str | None,
driver: str | None,
region: str | None,
warehouse: str | None,
departure: str | None,
card: str | None,
service: str | None,
) -> dict:
start_dt = _date_start_dt(start)
end_dt = _date_end_dt_exclusive(end)
barcode = (barcode or “”).strip() or None
invoice = (invoice or “”).strip() or None
status = (status or “”).strip() or None
driver = (driver or “”).strip() or None
region = (region or “”).strip() or None
warehouse = (warehouse or “”).strip() or None
departure = (departure or “”).strip() or None
card = (card or “”).strip() or None
service = (service or “”).strip() or None
return {
“start_date”: start_dt,
“end_date”: end_dt,
“barcode”: barcode,
“barcode_like”: f”{barcode}%” if barcode else None,
“invoice”: invoice,
“status”: status,
“driver”: driver,
“driver_like”: f”%{driver}%” if driver else None,
“region”: region,
“region_like”: f”%{region}%” if region else None,
“warehouse”: warehouse,
“warehouse_like”: f”%{warehouse}%” if warehouse else None,
“departure”: departure,
“departure_like”: f”%{departure}%” if departure else None,
“card”: card,
“service”: service,
“service_like”: f”%{service}%” if service else None,
}
@app.get(“/api/rapor”)
def rapor_data(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barcode: str | None = None,
invoice: str | None = None,
status: str | None = None,
driver: str | None = None,
region: str | None = None,
warehouse: str | None = None,
departure: str | None = None,
card: str | None = None,
service: str | None = None,
page: int = 1,
page_size: int = 50,
):
try:
if page < 1:
page = 1
if page_size < 1:
page_size = 50
if page_size > 500:
page_size = 500
params = build_params(start, end, barcode, invoice, status, driver, region, warehouse, departure, card, service)
total_row = fetch_one(count_sql(), params)
total = int(total_row[“total”]) if total_row and total_row.get(“total”) is not None else 0
params_items = dict(params)
params_items[“offset”] = (page – 1) * page_size
params_items[“limit”] = page_size
rows = fetch_all(paged_sql(), params_items)
kpi_sql = f”””
SELECT shippingstatus_text AS durum, COUNT(1) AS adet
FROM ( {filtered_outer_sql(with_order=False)} )
GROUP BY shippingstatus_text
ORDER BY COUNT(1) DESC
“””
kpi_rows = fetch_all(kpi_sql, params)
kpis = []
for r in kpi_rows:
adet = int(r.get(“adet”) or 0)
yuzde = round((adet / total) * 100, 2) if total else 0
kpis.append({“durum”: r.get(“durum”), “adet”: adet, “yuzde”: yuzde})
return {
“range”: {“start”: str(start), “end”: str(end)},
“page”: page,
“page_size”: page_size,
“total”: total,
“kpis”: kpis, # ✅ frontend expects “kpis”
“rows”: rows, # ✅ frontend expects “rows”
}
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
@app.get(“/api/rapor/group”)
def rapor_group(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barcode: str | None = None,
invoice: str | None = None,
status: str | None = None,
driver: str | None = None,
region: str | None = None,
warehouse: str | None = None,
departure: str | None = None,
card: str | None = None,
service: str | None = None,
group_by: str = “service”,
):
try:
params = build_params(start, end, barcode, invoice, status, driver, region, warehouse, departure, card, service)
group_map = {
“service”: “t.servicenametur”,
“customer”: “t.musteri”,
“region”: “t.packageregionnametur”,
“warehouse”: “t.packagewarehousenametur”,
“departure”: “t.departuremanagernametur”,
“card”: “t.deliveryvalidationtype_text”,
“status”: “t.shippingstatus_text”,
“driver”: “t.servicedrivername”,
}
col = group_map.get(group_by, “t.servicenametur”)
sql = f”””
SELECT
NVL({col}, ‘-‘) AS label,
COUNT(1) AS adet
FROM (
{filtered_outer_sql(with_order=False)}
) t
GROUP BY {col}
ORDER BY COUNT(1) DESC
“””
items_raw = fetch_all(sql, params)
total = sum(int(x.get(“adet”) or 0) for x in items_raw) or 0
items = []
for x in items_raw[:200]:
adet = int(x.get(“adet”) or 0)
yuzde = round((adet / total) * 100, 2) if total else 0
items.append({“label”: x.get(“label”), “adet”: adet, “yuzde”: yuzde})
return {“group_by”: group_by, “total”: total, “items”: items}
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
# ============================================================
# ✅ TREND KPI (UPDATED)
# – Default: last 6 months (by month)
# – Supports: ?months=6
# – Supports: ?start=YYYY-MM-DD&end=YYYY-MM-DD (forces range)
# ============================================================
@app.get(“/api/rapor/trend-kpi”)
def rapor_trend_kpi(
months: int = Query(default=6, ge=1, le=24),
start: date | None = None,
end: date | None = None,
):
“””
Button-triggered.
Monthly aggregates:
– Delivered (shippingstatus=4)
– Awaiting dispatch (shippingstatus=0)
– Card not scanned (deliveryvalidationtype=0)
Default = last 6 months.
“””
try:
# If explicit range provided -> use it
if start and end:
start_dt = _date_start_dt(start)
end_dt = _date_end_dt_exclusive(end)
start_month = _first_day_of_month(start_dt)
end_month_excl = _first_day_of_month(_date_end_dt_exclusive(end).replace(day=1))
# safer: end_dt already exclusive day; but month grouping needs full coverage.
# We’ll just use start_dt/end_dt for filter.
use_start = start_dt
use_end = end_dt
else:
# last N months including current month
now = datetime.now()
cur_month = _first_day_of_month(now)
start_month = _add_months(cur_month, -(months – 1))
end_month_excl = _add_months(cur_month, 1)
use_start = start_month
use_end = end_month_excl
sql = “””
SELECT
TO_CHAR(TRUNC(ppb.DATECREATED, ‘MM’), ‘YYYY-MM’) AS ay,
SUM(CASE WHEN ppb.SHIPPINGSTATUS = 4 THEN 1 ELSE 0 END) AS teslim_edildi,
SUM(CASE WHEN ppb.SHIPPINGSTATUS = 0 THEN 1 ELSE 0 END) AS sevk_bekliyor,
SUM(CASE WHEN ppb.DELIVERYVALIDATIONTYPE = 0 THEN 1 ELSE 0 END) AS kart_okutulmadi
FROM TRS_PACKAGEPACKBARCODE ppb
WHERE ppb.DATECREATED >= :start_dt
AND ppb.DATECREATED < :end_dt
GROUP BY TRUNC(ppb.DATECREATED, 'MM')
ORDER BY TRUNC(ppb.DATECREATED, 'MM')
"""
rows = fetch_all(sql, {"start_dt": use_start, "end_dt": use_end})
def pct(curr: int, prev: int):
if prev == 0:
return None
return round(((curr - prev) / prev) * 100, 2)
out = []
prev = None
for r in rows:
cur = {
"ay": r.get("ay"),
"teslim_edildi": int(r.get("teslim_edildi") or 0),
"sevk_bekliyor": int(r.get("sevk_bekliyor") or 0),
"kart_okutulmadi": int(r.get("kart_okutulmadi") or 0),
}
if prev:
cur["degisim"] = {
"teslim_edildi_pct": pct(cur["teslim_edildi"], prev["teslim_edildi"]),
"sevk_bekliyor_pct": pct(cur["sevk_bekliyor"], prev["sevk_bekliyor"]),
"kart_okutulmadi_pct": pct(cur["kart_okutulmadi"], prev["kart_okutulmadi"]),
}
else:
cur["degisim"] = {
"teslim_edildi_pct": None,
"sevk_bekliyor_pct": None,
"kart_okutulmadi_pct": None,
}
out.append(cur)
prev = cur
return {"months": out}
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
# ✅ ALIAS: frontend calls /api/trend-kpi
@app.get("/api/trend-kpi")
def trend_kpi_alias(
months: int = Query(default=6, ge=1, le=24),
start: date | None = None,
end: date | None = None,
):
return rapor_trend_kpi(months=months, start=start, end=end)
def _make_xlsx(headers: list[str], rows: list[dict]) -> bytes:
wb = Workbook()
ws = wb.active
ws.title = “Sevkiyat”
ws.append(headers)
for r in rows:
ws.append([r.get(h) for h in headers])
# Column widths (better than just header length)
# Looks cleaner in Excel.
for i, h in enumerate(headers, start=1):
max_len = len(h)
for rr in rows[:300]: # sample for performance
val = rr.get(h)
if val is None:
continue
s = str(val)
if len(s) > max_len:
max_len = len(s)
ws.column_dimensions[get_column_letter(i)].width = max(12, min(48, max_len + 2))
import io
bio = io.BytesIO()
wb.save(bio)
return bio.getvalue()
@app.get(“/api/rapor/export.xlsx”)
def export_xlsx(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barcode: str | None = None,
invoice: str | None = None,
status: str | None = None,
driver: str | None = None,
region: str | None = None,
warehouse: str | None = None,
departure: str | None = None,
card: str | None = None,
service: str | None = None,
):
try:
params = build_params(start, end, barcode, invoice, status, driver, region, warehouse, departure, card, service)
sql = filtered_outer_sql(with_order=True)
rows = fetch_all(sql, params)
headers = [
“datecreated”,
“hedefkodu”,
“musteri”,
“barcode”,
“invoicecode”,
“shippingstatus_text”,
“servicetype_text”,
“packageregionnametur”,
“servicenametur”,
“packagewarehousenametur”,
“departuremanagernametur”,
“arrivalmanagernametur”,
“servicedrivername”,
“servicevehicleplate”,
“deliveredlocation”,
“deliveryvalidationtype_text”,
“servicetimestarts”,
“datemodified”,
]
data = _make_xlsx(headers, rows)
filename = f”sevkiyat_{start}_{end}.xlsx”
return StreamingResponse(
iter([data]),
media_type=”application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”,
headers={“Content-Disposition”: f’attachment; filename=”{filename}”‘},
)
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
# ✅ ALIAS: frontend calls /api/export.xlsx
@app.get(“/api/export.xlsx”)
def export_xlsx_alias(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barcode: str | None = None,
invoice: str | None = None,
status: str | None = None,
driver: str | None = None,
region: str | None = None,
warehouse: str | None = None,
departure: str | None = None,
card: str | None = None,
service: str | None = None,
):
return export_xlsx(
start=start, end=end,
barcode=barcode, invoice=invoice, status=status,
driver=driver, region=region, warehouse=warehouse,
departure=departure, card=card, service=service
)
——-
güncel rapor html
Sevkiyat Raporu
Cencora
Yükleniyor…
Sütun Ayarları
İstediğin sütunları aç/kapat (kaydedilir).
Görünür Sütunlar
Gizli Sütunlar
Sevkiyat Raporu
–
DB kontrol ediliyor…
–
–
Filtreleri doldur → Raporu Getir ile sorguyu çalıştır. Excel: filtreli tüm kayıt iner.
Gruplama
–
Özet
Kural: Teslim Edildi artarsa iyi ✅ |
Sevk Bekliyor ve Kart Okutulmadı azalırsa iyi ✅
Durum
Adet
%
Henüz KPI yok.
“Son 6 Ay KPI” butonuna basınca üstte trend tablosu görünür.
–
DATECREATED
HEDEFKODU
MUSTERI
BARCODE
INVOICE
DURUM
SERVIS_TIPI
BOLGE
SERVIS
WAREHOUSE
DEPARTURE
ARRIVAL
SOFOR
ARAC
DELIVERED_LOC
KART
SERVICE_START
DATEMODIFIED
Henüz veri yok.
————–
# app/main.py
import csv
import io
import os
from datetime import date, datetime, timedelta
from typing import Any, Dict, Optional, Tuple
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from app.db import fetch_all, fetch_one
load_dotenv()
app = FastAPI(title=”Panel Rapor”, version=”1.1.0″)
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, “templates”)
templates = Jinja2Templates(directory=TEMPLATES_DIR)
def _date_start_dt(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0)
def _date_end_dt_exclusive(d: date) -> datetime:
# end day inclusive -> +1 day and “< end_dt"
return datetime(d.year, d.month, d.day, 0, 0, 0) + timedelta(days=1)
# =========================
# BASE SQL (NO ORDER BY here!)
# We will append ORDER BY at the end, and we will also inject FILTERS.
# =========================
BASE_SQL_CORE = """
SELECT
(
SELECT MAX(TO_NUMBER(pv.VALUETEXTTUR))
FROM CONTENTCC_C_PROPERTYVALUE pv
JOIN CONTENTCC_C_TPROPERTYVALUE tpv
ON pv.PROPERTYVALUEID = tpv.PROPERTYVALUEID
WHERE pv.PROPERTYID = 19
AND tpv.TITLEID = tl.TITLEID
) AS HEDEFKODU,
(pl.NAME || ',' || adr.DISTRICTNAME || ',' || adr.CITYNAME) AS MUSTERI,
ppb.DATECREATED,
srv.TIMESTARTS AS SERVICETIMESTARTS,
ppb.DATEMODIFIED,
(
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) AS INVOICECODE,
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN 'Kart Okutuldu'
WHEN 0 THEN 'Kart Okutulmadı'
ELSE NULL
END AS DELIVERYVALIDATIONTYPE_TEXT,
ppb.BARCODE,
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN 'Sevk Bekliyor'
WHEN 1 THEN 'Sevk Kontrolü Yapıldı'
WHEN 2 THEN 'Sevk Başladı'
WHEN 3 THEN 'Transfer Edildi'
WHEN 4 THEN 'Teslim Edildi'
WHEN 5 THEN 'Teslim Edilemedi'
WHEN 99 THEN 'İptal Edildi'
ELSE NULL
END AS SHIPPINGSTATUS_TEXT,
CASE srv.ISEXPRESSSERVICE
WHEN 1 THEN 'Hızlı Teslimat'
WHEN 0 THEN 'Servis'
ELSE NULL
END AS SERVICETYPE_TEXT,
reg.NAMETUR AS PACKAGEREGIONNAMETUR,
srv.NAMETUR AS SERVICENAMETUR,
wh.NAMETUR AS PACKAGEWAREHOUSENAMETUR,
dep.NAMETUR AS DEPARTUREMANAGERNAMETUR,
arr.NAMETUR AS ARRIVALMANAGERNAMETUR,
drv.NAME AS SERVICEDRIVERNAME,
veh.PLATE AS SERVICEVEHICLEPLATE,
(
SELECT DeliveredPoint
FROM (
SELECT sp.DeliveredPoint,
ROW_NUMBER() OVER (ORDER BY sp.DeliveredTime DESC) rn
FROM Transportation_ServicePackage sp
WHERE sp.PackagePackBarcodeID = ppb.PackagePackBarcodeID
AND sp.IsDelivered = 1
)
WHERE rn = 1
) AS DELIVEREDLOCATION
FROM TRS_PACKAGEPACKBARCODE ppb
JOIN TRANSPORTATION_PACKAGE pkg
ON pkg.PACKAGEID = ppb.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION reg
ON reg.REGIONID = pkg.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE srv
ON srv.SERVICEID = ppb.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE veh
ON veh.VEHICLEID = srv.VEHICLEID
LEFT JOIN COLLABORATION_NODE wh
ON wh.NODEID = pkg.WAREHOUSEID
LEFT JOIN COLLABORATION_NODE dep
ON dep.NODEID = ppb.DEPARTUREMANAGERID
LEFT JOIN COLLABORATION_NODE arr
ON arr.NODEID = ppb.ARRIVALMANAGERID
LEFT JOIN COLLABORATION_EMPLOYEE drv
ON drv.EMPLOYEEID = srv.DRIVERID
LEFT JOIN TRADING_LINK pl
ON pl.LINKID = pkg.LINKID
INNER JOIN TRADING_LINK tl
ON tl.LINKID = pkg.LINKID
LEFT JOIN CONTACTCC_CONTACT c
ON c.CONTACTID = pl.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS adr
ON adr.ADDRESSID = c.DEFAULTADDRESSID
WHERE
pl.NAME NOT LIKE '%All%'
AND reg.NAMETUR IS NOT NULL
AND reg.NAMETUR <> ‘NULL’
AND ppb.DATECREATED >= :start_date
AND ppb.DATECREATED < :end_date
/*__FILTERS__*/
"""
STATUS_TEXT_TO_CODE = {
"Sevk Bekliyor": 0,
"Sevk Kontrolü Yapıldı": 1,
"Sevk Başladı": 2,
"Transfer Edildi": 3,
"Teslim Edildi": 4,
"Teslim Edilemedi": 5,
"İptal Edildi": 99,
}
def _like(v: str) -> str:
# Oracle LIKE bind value
return f”%{v.strip()}%”
def build_filtered_sql(
include_order: bool = True,
order_by: str = “ppb.DATECREATED DESC”,
barcode: Optional[str] = None,
invoice: Optional[str] = None,
status_text: Optional[str] = None, # frontend sends text
driver: Optional[str] = None,
region: Optional[str] = None,
) -> Tuple[str, Dict[str, Any]]:
“””
Returns (sql, params) with ALL filters applied consistently.
This fixes:
– “filters not working”
– “invoice typed but different invoices show”
– “CSV exports only current page” (export will not paginate)
“””
filters_sql = []
params: Dict[str, Any] = {}
# BARCODE filter (contains)
if barcode and barcode.strip():
filters_sql.append(“AND UPPER(ppb.BARCODE) LIKE UPPER(:barcode_like)”)
params[“barcode_like”] = _like(barcode)
# REGION filter (packageregionnametur contains)
if region and region.strip():
filters_sql.append(“AND UPPER(reg.NAMETUR) LIKE UPPER(:region_like)”)
params[“region_like”] = _like(region)
# DRIVER filter (servicedrivername contains)
# NOTE: drv.NAME can be NULL; filter only matches when exists and like.
if driver and driver.strip():
filters_sql.append(“AND UPPER(drv.NAME) LIKE UPPER(:driver_like)”)
params[“driver_like”] = _like(driver)
# STATUS filter (text -> code -> filter by ppb.SHIPPINGSTATUS)
if status_text and status_text.strip():
code = STATUS_TEXT_TO_CODE.get(status_text.strip())
if code is not None:
filters_sql.append(“AND ppb.SHIPPINGSTATUS = :status_code”)
params[“status_code”] = code
# INVOICE filter (filters by the SAME “latest invoicecode” logic)
# This ensures if you type 40770618, only rows whose latest invoice equals/contains it will show.
if invoice and invoice.strip():
filters_sql.append(
“””
AND (
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) IS NOT NULL
AND UPPER((
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
)) LIKE UPPER(:invoice_like)
“””
)
params[“invoice_like”] = _like(invoice)
sql = BASE_SQL_CORE.replace(“/*__FILTERS__*/”, “\n ” + “\n “.join(filters_sql) if filters_sql else “”)
if include_order:
sql += f”\nORDER BY {order_by}”
return sql, params
def build_paged_sql(sql: str) -> str:
# Oracle pagination wrapper
return f”””
SELECT * FROM (
{sql}
)
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
“””
def build_count_sql(sql: str) -> str:
return f”SELECT COUNT(1) AS total FROM ( {sql} )”
# =========
# ROUTES
# =========
@app.get(“/”, response_class=HTMLResponse)
def root_redirect(request: Request):
return templates.TemplateResponse(“rapor.html”, {“request”: request})
@app.get(“/rapor”, response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse(“rapor.html”, {“request”: request})
@app.get(“/db-test”)
@app.get(“/api/db-test”)
def db_test():
try:
row = fetch_one(“SELECT 1 AS ok FROM dual”)
return {“ok”: row[“ok”] if row else 1}
except Exception as e:
return JSONResponse(status_code=500, content={“ok”: False, “error”: str(e)})
# ✅ /api/rapor (filters + paging)
@app.get(“/rapor-data”)
@app.get(“/api/rapor”)
def rapor_data(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
page: int = 1,
page_size: int = 50,
# NEW FILTERS (match your latest HTML)
barcode: Optional[str] = None,
invoice: Optional[str] = None,
status: Optional[str] = None, # status TEXT from UI
driver: Optional[str] = None,
region: Optional[str] = None,
):
try:
if page < 1:
page = 1
if page_size < 1:
page_size = 50
if page_size > 500:
page_size = 500
start_dt = _date_start_dt(start)
end_dt = _date_end_dt_exclusive(end)
# build filtered SQL ONCE and reuse for total/items/kpi
base_sql, filter_params = build_filtered_sql(
include_order=True,
barcode=barcode,
invoice=invoice,
status_text=status,
driver=driver,
region=region,
)
params_common = {“start_date”: start_dt, “end_date”: end_dt, **filter_params}
# total
total_row = fetch_one(build_count_sql(base_sql), params_common)
total = int(total_row[“total”]) if total_row and total_row.get(“total”) is not None else 0
# items (paged)
params_items = dict(params_common)
params_items[“offset”] = (page – 1) * page_size
params_items[“limit”] = page_size
items = fetch_all(build_paged_sql(base_sql), params_items)
# KPI (same filters!)
kpi_sql = f”””
SELECT shippingstatus_text AS durum, COUNT(1) AS adet
FROM ( {base_sql} )
GROUP BY shippingstatus_text
ORDER BY COUNT(1) DESC
“””
kpi_rows = fetch_all(kpi_sql, params_common)
kpi = []
for r in kpi_rows:
adet = int(r.get(“adet”) or 0)
pct = round((adet / total) * 100, 2) if total else 0
kpi.append({“durum”: r.get(“durum”), “adet”: adet, “yuzde”: pct})
return {
“range”: {“start”: str(start), “end”: str(end)},
“page”: page,
“page_size”: page_size,
“total”: total,
“kpis”: kpi, # (frontend expects kpis)
“items”: items,
}
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
# ✅ CSV EXPORT (ALL ROWS, NO PAGING) + SAME FILTERS
# This fixes: “CSV only downloads current open page”
@app.get(“/rapor/export”)
@app.get(“/api/rapor/export”)
def export_csv(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
# SAME FILTERS AS /api/rapor
barcode: Optional[str] = None,
invoice: Optional[str] = None,
status: Optional[str] = None,
driver: Optional[str] = None,
region: Optional[str] = None,
):
try:
start_dt = _date_start_dt(start)
end_dt = _date_end_dt_exclusive(end)
base_sql, filter_params = build_filtered_sql(
include_order=True,
barcode=barcode,
invoice=invoice,
status_text=status,
driver=driver,
region=region,
)
params = {“start_date”: start_dt, “end_date”: end_dt, **filter_params}
rows = fetch_all(base_sql, params)
output = io.StringIO()
writer = csv.writer(output)
headers = [
“hedefkodu”, “musteri”, “datecreated”, “servicetimestarts”, “datemodified”,
“invoicecode”, “deliveryvalidationtype_text”, “barcode”, “shippingstatus_text”,
“servicetype_text”, “packageregionnametur”, “servicenametur”,
“packagewarehousenametur”, “departuremanagernametur”, “arrivalmanagernametur”,
“servicedrivername”, “servicevehicleplate”, “deliveredlocation”,
]
writer.writerow(headers)
for r in rows:
writer.writerow([r.get(h) for h in headers])
output.seek(0)
filename = f”rapor_{start}_{end}.csv”
return StreamingResponse(
iter([output.getvalue()]),
media_type=”text/csv; charset=utf-8″,
headers={“Content-Disposition”: f’attachment; filename=”{filename}”‘},
)
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
# ✅ Backward compatibility (your old paths)
# Keep these so old buttons/links still work
@app.get(“/export.csv”)
@app.get(“/api/export.csv”)
def export_csv_legacy(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barcode: Optional[str] = None,
invoice: Optional[str] = None,
status: Optional[str] = None,
driver: Optional[str] = None,
region: Optional[str] = None,
):
return export_csv(
start=start,
end=end,
barcode=barcode,
invoice=invoice,
status=status,
driver=driver,
region=region,
)
# app/main.py
import csv
import io
import os
from datetime import date, datetime, timedelta
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from app.db import fetch_all, fetch_one
load_dotenv()
app = FastAPI(title=”Panel Rapor”, version=”1.0.0″)
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, “templates”)
templates = Jinja2Templates(directory=TEMPLATES_DIR)
def _date_start_dt(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0)
def _date_end_dt_exclusive(d: date) -> datetime:
# end günü dahil olsun diye +1 gün ve “< end_dt"
return datetime(d.year, d.month, d.day, 0, 0, 0) + timedelta(days=1)
# =========================
# ✅ SENİN SQL (Temel Query)
# =========================
BASE_SQL = """
SELECT
(
SELECT MAX(TO_NUMBER(pv.VALUETEXTTUR))
FROM CONTENTCC_C_PROPERTYVALUE pv
JOIN CONTENTCC_C_TPROPERTYVALUE tpv
ON pv.PROPERTYVALUEID = tpv.PROPERTYVALUEID
WHERE pv.PROPERTYID = 19
AND tpv.TITLEID = tl.TITLEID
) AS HEDEFKODU,
(pl.NAME || ',' || adr.DISTRICTNAME || ',' || adr.CITYNAME) AS MUSTERI,
ppb.DATECREATED,
srv.TIMESTARTS AS SERVICETIMESTARTS,
ppb.DATEMODIFIED,
(
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) AS INVOICECODE,
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN 'Kart Okutuldu'
WHEN 0 THEN 'Kart Okutulmadı'
ELSE NULL
END AS DELIVERYVALIDATIONTYPE_TEXT,
ppb.BARCODE,
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN 'Sevk Bekliyor'
WHEN 1 THEN 'Sevk Kontrolü Yapıldı'
WHEN 2 THEN 'Sevk Başladı'
WHEN 3 THEN 'Transfer Edildi'
WHEN 4 THEN 'Teslim Edildi'
WHEN 5 THEN 'Teslim Edilemedi'
WHEN 99 THEN 'İptal Edildi'
ELSE NULL
END AS SHIPPINGSTATUS_TEXT,
CASE srv.ISEXPRESSSERVICE
WHEN 1 THEN 'Hızlı Teslimat'
WHEN 0 THEN 'Servis'
ELSE NULL
END AS SERVICETYPE_TEXT,
reg.NAMETUR AS PACKAGEREGIONNAMETUR,
srv.NAMETUR AS SERVICENAMETUR,
wh.NAMETUR AS PACKAGEWAREHOUSENAMETUR,
dep.NAMETUR AS DEPARTUREMANAGERNAMETUR,
arr.NAMETUR AS ARRIVALMANAGERNAMETUR,
drv.NAME AS SERVICEDRIVERNAME,
veh.PLATE AS SERVICEVEHICLEPLATE,
(
SELECT DeliveredPoint
FROM (
SELECT sp.DeliveredPoint,
ROW_NUMBER() OVER (ORDER BY sp.DeliveredTime DESC) rn
FROM Transportation_ServicePackage sp
WHERE sp.PackagePackBarcodeID = ppb.PackagePackBarcodeID
AND sp.IsDelivered = 1
)
WHERE rn = 1
) AS DELIVEREDLOCATION
FROM TRS_PACKAGEPACKBARCODE ppb
JOIN TRANSPORTATION_PACKAGE pkg
ON pkg.PACKAGEID = ppb.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION reg
ON reg.REGIONID = pkg.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE srv
ON srv.SERVICEID = ppb.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE veh
ON veh.VEHICLEID = srv.VEHICLEID
LEFT JOIN COLLABORATION_NODE wh
ON wh.NODEID = pkg.WAREHOUSEID
LEFT JOIN COLLABORATION_NODE dep
ON dep.NODEID = ppb.DEPARTUREMANAGERID
LEFT JOIN COLLABORATION_NODE arr
ON arr.NODEID = ppb.ARRIVALMANAGERID
LEFT JOIN COLLABORATION_EMPLOYEE drv
ON drv.EMPLOYEEID = srv.DRIVERID
LEFT JOIN TRADING_LINK pl
ON pl.LINKID = pkg.LINKID
INNER JOIN TRADING_LINK tl
ON tl.LINKID = pkg.LINKID
LEFT JOIN CONTACTCC_CONTACT c
ON c.CONTACTID = pl.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS adr
ON adr.ADDRESSID = c.DEFAULTADDRESSID
WHERE
pl.NAME NOT LIKE '%All%'
AND reg.NAMETUR IS NOT NULL
AND reg.NAMETUR <> ‘NULL’
AND ppb.DATECREATED >= :start_date
AND ppb.DATECREATED < :end_date
ORDER BY ppb.DATECREATED DESC
"""
def build_paged_sql(base_sql: str) -> str:
# Oracle pagination için dıştan sarıyoruz
return f”””
SELECT * FROM (
{base_sql}
)
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
“””
def build_count_sql(base_sql: str) -> str:
# ORDER BY sayımda gereksiz, ama en güvenlisi: subquery ile count
return f”SELECT COUNT(1) AS total FROM ( {base_sql} )”
# =========
# ROUTES
# =========
@app.get(“/”, response_class=HTMLResponse)
def root_redirect(request: Request):
# İstersen direkt rapor sayfası açılsın
return templates.TemplateResponse(“rapor.html”, {“request”: request})
@app.get(“/rapor”, response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse(“rapor.html”, {“request”: request})
# ✅ hem /db-test hem /api/db-test destek
@app.get(“/db-test”)
@app.get(“/api/db-test”)
def db_test():
try:
row = fetch_one(“SELECT 1 AS ok FROM dual”)
return {“ok”: row[“ok”] if row else 1}
except Exception as e:
return JSONResponse(status_code=500, content={“ok”: False, “error”: str(e)})
# ✅ hem /rapor-data hem /api/rapor destek
@app.get(“/rapor-data”)
@app.get(“/api/rapor”)
def rapor_data(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
page: int = 1,
page_size: int = 50,
):
try:
if page < 1:
page = 1
if page_size < 1:
page_size = 50
if page_size > 500:
page_size = 500
start_dt = _date_start_dt(start)
end_dt = _date_end_dt_exclusive(end)
params_common = {
“start_date”: start_dt,
“end_date”: end_dt,
}
# total
total_row = fetch_one(build_count_sql(BASE_SQL), params_common)
total = int(total_row[“total”]) if total_row and total_row.get(“total”) is not None else 0
# items
params_items = dict(params_common)
params_items[“offset”] = (page – 1) * page_size
params_items[“limit”] = page_size
items = fetch_all(build_paged_sql(BASE_SQL), params_items)
# KPI örneği: SHIPPINGSTATUS_TEXT’e göre adet/yüzde
kpi_sql = f”””
SELECT shippingstatus_text AS durum, COUNT(1) AS adet
FROM ( {BASE_SQL} )
GROUP BY shippingstatus_text
ORDER BY COUNT(1) DESC
“””
kpi_rows = fetch_all(kpi_sql, params_common)
kpi = []
for r in kpi_rows:
adet = int(r.get(“adet”) or 0)
pct = round((adet / total) * 100, 2) if total else 0
kpi.append({“durum”: r.get(“durum”), “adet”: adet, “yuzde”: pct})
return {
“range”: {“start”: str(start), “end”: str(end)},
“page”: page,
“page_size”: page_size,
“total”: total,
“kpi”: kpi,
“items”: items,
}
except Exception as e:
# Frontend “Unexpected token I” yemesin diye daima JSON dön
return JSONResponse(status_code=500, content={“error”: str(e)})
# ✅ CSV export (hem /export.csv hem /api/export.csv)
@app.get(“/export.csv”)
@app.get(“/api/export.csv”)
def export_csv(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
):
try:
start_dt = _date_start_dt(start)
end_dt = _date_end_dt_exclusive(end)
params = {“start_date”: start_dt, “end_date”: end_dt}
rows = fetch_all(BASE_SQL, params)
output = io.StringIO()
writer = csv.writer(output)
# SQL’deki aliaslara göre header
headers = [
“hedefkodu”, “musteri”, “datecreated”, “servicetimestarts”, “datemodified”,
“invoicecode”, “deliveryvalidationtype_text”, “barcode”, “shippingstatus_text”,
“servicetype_text”, “packageregionnametur”, “servicenametur”,
“packagewarehousenametur”, “departuremanagernametur”, “arrivalmanagernametur”,
“servicedrivername”, “servicevehicleplate”, “deliveredlocation”,
]
writer.writerow(headers)
for r in rows:
writer.writerow([r.get(h) for h in headers])
output.seek(0)
filename = f”rapor_{start}_{end}.csv”
return StreamingResponse(
iter([output.getvalue()]),
media_type=”text/csv; charset=utf-8″,
headers={“Content-Disposition”: f’attachment; filename=”{filename}”‘},
)
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
——
Rapor Dashboard
Raporlar
–
Durum seçince anında filtreler. Barkod/Fatura/Şoför/Bölge yazınca otomatik arar (350ms).
––
DATECREATED
HEDEFKODU
MÜŞTERİ
BARKOD
INVOICE
DURUM
SERVİS
SERVICENAME
BÖLGE
DEPO
DEPARTURE
ARRIVAL
VALIDATION
ŞOFÖR
ARAÇ
TESLİM LOK.
SERVICETIME
DATEMODIFIED
Henüz veri yok.
------
Rapor Dashboard
Raporlar
Durum/tarih/sayfa boyutu değişince otomatik yeniler. Barkod/Fatura yazınca 350ms sonra arar.
CSV endpoint yoksa ekrandaki veriden CSV üretip indirir.
--
TARİH
HEDEFKODU
MÜŞTERİ
BARKOD
DURUM
SERVİS
FATURA
ŞOFÖR
ARAÇ
TESLİM LOK.
Henüz veri yok.
Rapor Dashboard
Raporlar
Durum seçince anında filtreler. Barkod/Fatura yazınca otomatik arar (350ms).
-
TARİH
HEDEFKODU
MÜŞTERİ
BARKOD
DURUM
SERVİS
FATURA
ŞOFÖR
ARAÇ
TESLİM LOK.
Henüz veri yok.
Rapor Dashboard
Raporlar
Filtre değişince otomatik yeniler. Enter’a basınca “Raporu Getir”.
-
TARİH
HEDEFKODU
MÜŞTERİ
BARKOD
DURUM
SERVİS
FATURA
ŞOFÖR
ARAÇ
TESLİM LOK.
Henüz veri yok.
Rapor Dashboard
Raporlar
-
TARİH
HEDEFKODU
MÜŞTERİ
BARKOD
DURUM
SERVİS
FATURA
ŞOFÖR
ARAÇ
TESLİM LOK.
Henüz veri yok.
Rapor Dashboard
Raporlar
-
TARİH
HEDEFKODU
MÜŞTERİ
BARKOD
DURUM
SERVİS
FATURA
ŞOFÖR
ARAÇ
TESLİM LOK.
Henüz veri yok.
# app/main.py
import csv
import io
import os
from datetime import date, datetime, timedelta
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from app.db import fetch_all, fetch_one
load_dotenv()
app = FastAPI(title="Panel Rapor", version="1.0.0")
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, "templates")
templates = Jinja2Templates(directory=TEMPLATES_DIR)
def _date_start_dt(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0)
def _date_end_dt_exclusive(d: date) -> datetime:
# end günü dahil olsun diye +1 gün ve "< end_dt"
return datetime(d.year, d.month, d.day, 0, 0, 0) + timedelta(days=1)
# =========================
# ✅ SENİN SQL (Temel Query)
# =========================
BASE_SQL = """
SELECT
(
SELECT MAX(TO_NUMBER(pv.VALUETEXTTUR))
FROM CONTENTCC_C_PROPERTYVALUE pv
JOIN CONTENTCC_C_TPROPERTYVALUE tpv
ON pv.PROPERTYVALUEID = tpv.PROPERTYVALUEID
WHERE pv.PROPERTYID = 19
AND tpv.TITLEID = tl.TITLEID
) AS HEDEFKODU,
(pl.NAME || ',' || adr.DISTRICTNAME || ',' || adr.CITYNAME) AS MUSTERI,
ppb.DATECREATED,
srv.TIMESTARTS AS SERVICETIMESTARTS,
ppb.DATEMODIFIED,
(
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) AS INVOICECODE,
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN 'Kart Okutuldu'
WHEN 0 THEN 'Kart Okutulmadı'
ELSE NULL
END AS DELIVERYVALIDATIONTYPE_TEXT,
ppb.BARCODE,
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN 'Sevk Bekliyor'
WHEN 1 THEN 'Sevk Kontrolü Yapıldı'
WHEN 2 THEN 'Sevk Başladı'
WHEN 3 THEN 'Transfer Edildi'
WHEN 4 THEN 'Teslim Edildi'
WHEN 5 THEN 'Teslim Edilemedi'
WHEN 99 THEN 'İptal Edildi'
ELSE NULL
END AS SHIPPINGSTATUS_TEXT,
CASE srv.ISEXPRESSSERVICE
WHEN 1 THEN 'Hızlı Teslimat'
WHEN 0 THEN 'Servis'
ELSE NULL
END AS SERVICETYPE_TEXT,
reg.NAMETUR AS PACKAGEREGIONNAMETUR,
srv.NAMETUR AS SERVICENAMETUR,
wh.NAMETUR AS PACKAGEWAREHOUSENAMETUR,
dep.NAMETUR AS DEPARTUREMANAGERNAMETUR,
arr.NAMETUR AS ARRIVALMANAGERNAMETUR,
drv.NAME AS SERVICEDRIVERNAME,
veh.PLATE AS SERVICEVEHICLEPLATE,
(
SELECT DeliveredPoint
FROM (
SELECT sp.DeliveredPoint,
ROW_NUMBER() OVER (ORDER BY sp.DeliveredTime DESC) rn
FROM Transportation_ServicePackage sp
WHERE sp.PackagePackBarcodeID = ppb.PackagePackBarcodeID
AND sp.IsDelivered = 1
)
WHERE rn = 1
) AS DELIVEREDLOCATION
FROM TRS_PACKAGEPACKBARCODE ppb
JOIN TRANSPORTATION_PACKAGE pkg
ON pkg.PACKAGEID = ppb.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION reg
ON reg.REGIONID = pkg.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE srv
ON srv.SERVICEID = ppb.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE veh
ON veh.VEHICLEID = srv.VEHICLEID
LEFT JOIN COLLABORATION_NODE wh
ON wh.NODEID = pkg.WAREHOUSEID
LEFT JOIN COLLABORATION_NODE dep
ON dep.NODEID = ppb.DEPARTUREMANAGERID
LEFT JOIN COLLABORATION_NODE arr
ON arr.NODEID = ppb.ARRIVALMANAGERID
LEFT JOIN COLLABORATION_EMPLOYEE drv
ON drv.EMPLOYEEID = srv.DRIVERID
LEFT JOIN TRADING_LINK pl
ON pl.LINKID = pkg.LINKID
INNER JOIN TRADING_LINK tl
ON tl.LINKID = pkg.LINKID
LEFT JOIN CONTACTCC_CONTACT c
ON c.CONTACTID = pl.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS adr
ON adr.ADDRESSID = c.DEFAULTADDRESSID
WHERE
pl.NAME NOT LIKE '%All%'
AND reg.NAMETUR IS NOT NULL
AND reg.NAMETUR <> 'NULL'
AND ppb.DATECREATED >= :start_date
AND ppb.DATECREATED < :end_date
ORDER BY ppb.DATECREATED DESC
"""
def build_paged_sql(base_sql: str) -> str:
# Oracle pagination için dıştan sarıyoruz
return f"""
SELECT * FROM (
{base_sql}
)
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
"""
def build_count_sql(base_sql: str) -> str:
# ORDER BY sayımda gereksiz, ama en güvenlisi: subquery ile count
return f"SELECT COUNT(1) AS total FROM ( {base_sql} )"
# =========
# ROUTES
# =========
@app.get("/", response_class=HTMLResponse)
def root_redirect(request: Request):
# İstersen direkt rapor sayfası açılsın
return templates.TemplateResponse("rapor.html", {"request": request})
@app.get("/rapor", response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse("rapor.html", {"request": request})
# ✅ hem /db-test hem /api/db-test destek
@app.get("/db-test")
@app.get("/api/db-test")
def db_test():
try:
row = fetch_one("SELECT 1 AS ok FROM dual")
return {"ok": row["ok"] if row else 1}
except Exception as e:
return JSONResponse(status_code=500, content={"ok": False, "error": str(e)})
# ✅ hem /rapor-data hem /api/rapor destek
@app.get("/rapor-data")
@app.get("/api/rapor")
def rapor_data(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
page: int = 1,
page_size: int = 50,
):
try:
if page < 1:
page = 1
if page_size < 1:
page_size = 50
if page_size > 500:
page_size = 500
start_dt = _date_start_dt(start)
end_dt = _date_end_dt_exclusive(end)
params_common = {
"start_date": start_dt,
"end_date": end_dt,
}
# total
total_row = fetch_one(build_count_sql(BASE_SQL), params_common)
total = int(total_row["total"]) if total_row and total_row.get("total") is not None else 0
# items
params_items = dict(params_common)
params_items["offset"] = (page - 1) * page_size
params_items["limit"] = page_size
items = fetch_all(build_paged_sql(BASE_SQL), params_items)
# KPI örneği: SHIPPINGSTATUS_TEXT'e göre adet/yüzde
kpi_sql = f"""
SELECT shippingstatus_text AS durum, COUNT(1) AS adet
FROM ( {BASE_SQL} )
GROUP BY shippingstatus_text
ORDER BY COUNT(1) DESC
"""
kpi_rows = fetch_all(kpi_sql, params_common)
kpi = []
for r in kpi_rows:
adet = int(r.get("adet") or 0)
pct = round((adet / total) * 100, 2) if total else 0
kpi.append({"durum": r.get("durum"), "adet": adet, "yuzde": pct})
return {
"range": {"start": str(start), "end": str(end)},
"page": page,
"page_size": page_size,
"total": total,
"kpi": kpi,
"items": items,
}
except Exception as e:
# Frontend “Unexpected token I” yemesin diye daima JSON dön
return JSONResponse(status_code=500, content={"error": str(e)})
# ✅ CSV export (hem /export.csv hem /api/export.csv)
@app.get("/export.csv")
@app.get("/api/export.csv")
def export_csv(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
):
try:
start_dt = _date_start_dt(start)
end_dt = _date_end_dt_exclusive(end)
params = {"start_date": start_dt, "end_date": end_dt}
rows = fetch_all(BASE_SQL, params)
output = io.StringIO()
writer = csv.writer(output)
# SQL'deki aliaslara göre header
headers = [
"hedefkodu", "musteri", "datecreated", "servicetimestarts", "datemodified",
"invoicecode", "deliveryvalidationtype_text", "barcode", "shippingstatus_text",
"servicetype_text", "packageregionnametur", "servicenametur",
"packagewarehousenametur", "departuremanagernametur", "arrivalmanagernametur",
"servicedrivername", "servicevehicleplate", "deliveredlocation",
]
writer.writerow(headers)
for r in rows:
writer.writerow([r.get(h) for h in headers])
output.seek(0)
filename = f"rapor_{start}_{end}.csv"
return StreamingResponse(
iter([output.getvalue()]),
media_type="text/csv; charset=utf-8",
headers={"Content-Disposition": f'attachment; filename="{filename}"'},
)
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
# app/main.py
import csv
import io
import os
from datetime import date, datetime, timedelta
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from app.db import fetch_all, fetch_one
load_dotenv()
app = FastAPI(title="Panel Rapor", version="1.0.0")
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, "templates")
templates = Jinja2Templates(directory=TEMPLATES_DIR)
def _date_start_dt(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0)
def _date_end_dt_exclusive(d: date) -> datetime:
# end günü dahil olsun diye +1 gün ve "< end_dt"
return datetime(d.year, d.month, d.day, 0, 0, 0) + timedelta(days=1)
# =========================
# ✅ SENİN SQL (Temel Query)
# =========================
BASE_SQL = """
SELECT
(
SELECT MAX(TO_NUMBER(pv.VALUETEXTTUR))
FROM CONTENTCC_C_PROPERTYVALUE pv
JOIN CONTENTCC_C_TPROPERTYVALUE tpv
ON pv.PROPERTYVALUEID = tpv.PROPERTYVALUEID
WHERE pv.PROPERTYID = 19
AND tpv.TITLEID = tl.TITLEID
) AS HEDEFKODU,
(pl.NAME || ',' || adr.DISTRICTNAME || ',' || adr.CITYNAME) AS MUSTERI,
ppb.DATECREATED,
srv.TIMESTARTS AS SERVICETIMESTARTS,
ppb.DATEMODIFIED,
(
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) AS INVOICECODE,
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN 'Kart Okutuldu'
WHEN 0 THEN 'Kart Okutulmadı'
ELSE NULL
END AS DELIVERYVALIDATIONTYPE_TEXT,
ppb.BARCODE,
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN 'Sevk Bekliyor'
WHEN 1 THEN 'Sevk Kontrolü Yapıldı'
WHEN 2 THEN 'Sevk Başladı'
WHEN 3 THEN 'Transfer Edildi'
WHEN 4 THEN 'Teslim Edildi'
WHEN 5 THEN 'Teslim Edilemedi'
WHEN 99 THEN 'İptal Edildi'
ELSE NULL
END AS SHIPPINGSTATUS_TEXT,
CASE srv.ISEXPRESSSERVICE
WHEN 1 THEN 'Hızlı Teslimat'
WHEN 0 THEN 'Servis'
ELSE NULL
END AS SERVICETYPE_TEXT,
reg.NAMETUR AS PACKAGEREGIONNAMETUR,
srv.NAMETUR AS SERVICENAMETUR,
wh.NAMETUR AS PACKAGEWAREHOUSENAMETUR,
dep.NAMETUR AS DEPARTUREMANAGERNAMETUR,
arr.NAMETUR AS ARRIVALMANAGERNAMETUR,
drv.NAME AS SERVICEDRIVERNAME,
veh.PLATE AS SERVICEVEHICLEPLATE,
(
SELECT DeliveredPoint
FROM (
SELECT sp.DeliveredPoint,
ROW_NUMBER() OVER (ORDER BY sp.DeliveredTime DESC) rn
FROM Transportation_ServicePackage sp
WHERE sp.PackagePackBarcodeID = ppb.PackagePackBarcodeID
AND sp.IsDelivered = 1
)
WHERE rn = 1
) AS DELIVEREDLOCATION
FROM TRS_PACKAGEPACKBARCODE ppb
JOIN TRANSPORTATION_PACKAGE pkg
ON pkg.PACKAGEID = ppb.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION reg
ON reg.REGIONID = pkg.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE srv
ON srv.SERVICEID = ppb.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE veh
ON veh.VEHICLEID = srv.VEHICLEID
LEFT JOIN COLLABORATION_NODE wh
ON wh.NODEID = pkg.WAREHOUSEID
LEFT JOIN COLLABORATION_NODE dep
ON dep.NODEID = ppb.DEPARTUREMANAGERID
LEFT JOIN COLLABORATION_NODE arr
ON arr.NODEID = ppb.ARRIVALMANAGERID
LEFT JOIN COLLABORATION_EMPLOYEE drv
ON drv.EMPLOYEEID = srv.DRIVERID
LEFT JOIN TRADING_LINK pl
ON pl.LINKID = pkg.LINKID
INNER JOIN TRADING_LINK tl
ON tl.LINKID = pkg.LINKID
LEFT JOIN CONTACTCC_CONTACT c
ON c.CONTACTID = pl.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS adr
ON adr.ADDRESSID = c.DEFAULTADDRESSID
WHERE
pl.NAME NOT LIKE '%All%'
AND reg.NAMETUR IS NOT NULL
AND reg.NAMETUR <> 'NULL'
AND ppb.DATECREATED >= :start_date
AND ppb.DATECREATED < :end_date
ORDER BY ppb.DATECREATED DESC
"""
def build_paged_sql(base_sql: str) -> str:
# Oracle pagination için dıştan sarıyoruz
return f"""
SELECT * FROM (
{base_sql}
)
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
"""
def build_count_sql(base_sql: str) -> str:
# ORDER BY sayımda gereksiz, ama en güvenlisi: subquery ile count
return f"SELECT COUNT(1) AS total FROM ( {base_sql} )"
# =========
# ROUTES
# =========
@app.get("/", response_class=HTMLResponse)
def root_redirect(request: Request):
# İstersen direkt rapor sayfası açılsın
return templates.TemplateResponse("rapor.html", {"request": request})
@app.get("/rapor", response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse("rapor.html", {"request": request})
# ✅ hem /db-test hem /api/db-test destek
@app.get("/db-test")
@app.get("/api/db-test")
def db_test():
try:
row = fetch_one("SELECT 1 AS ok FROM dual")
return {"ok": row["ok"] if row else 1}
except Exception as e:
return JSONResponse(status_code=500, content={"ok": False, "error": str(e)})
# ✅ hem /rapor-data hem /api/rapor destek
@app.get("/rapor-data")
@app.get("/api/rapor")
def rapor_data(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
page: int = 1,
page_size: int = 50,
):
try:
if page < 1:
page = 1
if page_size < 1:
page_size = 50
if page_size > 500:
page_size = 500
start_dt = _date_start_dt(start)
end_dt = _date_end_dt_exclusive(end)
params_common = {
"start_date": start_dt,
"end_date": end_dt,
}
# total
total_row = fetch_one(build_count_sql(BASE_SQL), params_common)
total = int(total_row["total"]) if total_row and total_row.get("total") is not None else 0
# items
params_items = dict(params_common)
params_items["offset"] = (page - 1) * page_size
params_items["limit"] = page_size
items = fetch_all(build_paged_sql(BASE_SQL), params_items)
# KPI örneği: SHIPPINGSTATUS_TEXT'e göre adet/yüzde
kpi_sql = f"""
SELECT shippingstatus_text AS durum, COUNT(1) AS adet
FROM ( {BASE_SQL} )
GROUP BY shippingstatus_text
ORDER BY COUNT(1) DESC
"""
kpi_rows = fetch_all(kpi_sql, params_common)
kpi = []
for r in kpi_rows:
adet = int(r.get("adet") or 0)
pct = round((adet / total) * 100, 2) if total else 0
kpi.append({"durum": r.get("durum"), "adet": adet, "yuzde": pct})
return {
"range": {"start": str(start), "end": str(end)},
"page": page,
"page_size": page_size,
"total": total,
"kpi": kpi,
"items": items,
}
except Exception as e:
# Frontend “Unexpected token I” yemesin diye daima JSON dön
return JSONResponse(status_code=500, content={"error": str(e)})
# ✅ CSV export (hem /export.csv hem /api/export.csv)
@app.get("/export.csv")
@app.get("/api/export.csv")
def export_csv(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
):
try:
start_dt = _date_start_dt(start)
end_dt = _date_end_dt_exclusive(end)
params = {"start_date": start_dt, "end_date": end_dt}
rows = fetch_all(BASE_SQL, params)
output = io.StringIO()
writer = csv.writer(output)
# SQL'deki aliaslara göre header
headers = [
"hedefkodu", "musteri", "datecreated", "servicetimestarts", "datemodified",
"invoicecode", "deliveryvalidationtype_text", "barcode", "shippingstatus_text",
"servicetype_text", "packageregionnametur", "servicenametur",
"packagewarehousenametur", "departuremanagernametur", "arrivalmanagernametur",
"servicedrivername", "servicevehicleplate", "deliveredlocation",
]
writer.writerow(headers)
for r in rows:
writer.writerow([r.get(h) for h in headers])
output.seek(0)
filename = f"rapor_{start}_{end}.csv"
return StreamingResponse(
iter([output.getvalue()]),
media_type="text/csv; charset=utf-8",
headers={"Content-Disposition": f'attachment; filename="{filename}"'},
)
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
from fastapi import FastAPI, Query
from fastapi.responses import JSONResponse
from datetime import datetime, timedelta
from typing import Optional, Any, Dict, List
# Eğer db.py kökteyse: from db import get_connection
# Eğer app/db.py ise:
from app.db import get_connection
app = FastAPI(title="Rapor Dashboard API")
SQL_BASE = r"""
SELECT
(
SELECT MAX(TO_NUMBER(pv.VALUETEXTTUR))
FROM CONTENTCC_C_PROPERTYVALUE pv
JOIN CONTENTCC_C_TPROPERTYVALUE tpv
ON pv.PROPERTYVALUEID = tpv.PROPERTYVALUEID
WHERE pv.PROPERTYID = 19
AND tpv.TITLEID = tl.TITLEID
) AS HEDEFKODU,
(pl.NAME || ',' || adr.DISTRICTNAME || ',' || adr.CITYNAME) AS MUSTERI,
ppb.DATECREATED,
srv.TIMESTARTS AS SERVICETIMESTARTS,
ppb.DATEMODIFIED,
(
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) AS INVOICECODE,
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN 'Kart Okutuldu'
WHEN 0 THEN 'Kart Okutulmadı'
ELSE NULL
END AS DELIVERYVALIDATIONTYPE_TEXT,
ppb.BARCODE,
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN 'Sevk Bekliyor'
WHEN 1 THEN 'Sevk Kontrolü Yapıldı'
WHEN 2 THEN 'Sevk Başladı'
WHEN 3 THEN 'Transfer Edildi'
WHEN 4 THEN 'Teslim Edildi'
WHEN 5 THEN 'Teslim Edilemedi'
WHEN 99 THEN 'İptal Edildi'
ELSE NULL
END AS SHIPPINGSTATUS_TEXT,
CASE srv.ISEXPRESSSERVICE
WHEN 1 THEN 'Hızlı Teslimat'
WHEN 0 THEN 'Servis'
ELSE NULL
END AS SERVICETYPE_TEXT,
reg.NAMETUR AS PACKAGEREGIONNAMETUR,
srv.NAMETUR AS SERVICENAMETUR,
wh.NAMETUR AS PACKAGEWAREHOUSENAMETUR,
dep.NAMETUR AS DEPARTUREMANAGERNAMETUR,
arr.NAMETUR AS ARRIVALMANAGERNAMETUR,
drv.NAME AS SERVICEDRIVERNAME,
veh.PLATE AS SERVICEVEHICLEPLATE,
(
SELECT DeliveredPoint
FROM (
SELECT sp.DeliveredPoint,
ROW_NUMBER() OVER (ORDER BY sp.DeliveredTime DESC) rn
FROM Transportation_ServicePackage sp
WHERE sp.PackagePackBarcodeID = ppb.PackagePackBarcodeID
AND sp.IsDelivered = 1
)
WHERE rn = 1
) AS DELIVEREDLOCATION
FROM TRS_PACKAGEPACKBARCODE ppb
JOIN TRANSPORTATION_PACKAGE pkg
ON pkg.PACKAGEID = ppb.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION reg
ON reg.REGIONID = pkg.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE srv
ON srv.SERVICEID = ppb.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE veh
ON veh.VEHICLEID = srv.VEHICLEID
LEFT JOIN COLLABORATION_NODE wh
ON wh.NODEID = pkg.WAREHOUSEID
LEFT JOIN COLLABORATION_NODE dep
ON dep.NODEID = ppb.DEPARTUREMANAGERID
LEFT JOIN COLLABORATION_NODE arr
ON arr.NODEID = ppb.ARRIVALMANAGERID
LEFT JOIN COLLABORATION_EMPLOYEE drv
ON drv.EMPLOYEEID = srv.DRIVERID
LEFT JOIN TRADING_LINK pl
ON pl.LINKID = pkg.LINKID
INNER JOIN TRADING_LINK tl
ON tl.LINKID = pkg.LINKID
LEFT JOIN CONTACTCC_CONTACT c
ON c.CONTACTID = pl.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS adr
ON adr.ADDRESSID = c.DEFAULTADDRESSID
WHERE
pl.NAME NOT LIKE '%All%'
AND reg.NAMETUR IS NOT NULL
AND reg.NAMETUR <> 'NULL'
AND ppb.DATECREATED >= :start_date
AND ppb.DATECREATED < :end_date
"""
def _parse_date_only(s: str) -> datetime:
# UI genelde "YYYY-MM-DD" gönderiyor
return datetime.strptime(s.strip(), "%Y-%m-%d")
def _make_end_exclusive(end_date_str: str) -> datetime:
# SQL < :end_date olduğu için bitişi +1 gün yapıyoruz
end_dt = _parse_date_only(end_date_str)
return end_dt + timedelta(days=1)
def _row_to_ui_item(r: Dict[str, Any]) -> Dict[str, Any]:
# Frontend’in tablo başlıklarına göre map
# TARİH -> DATECREATED
tarih = r.get("DATECREATED")
if isinstance(tarih, datetime):
tarih = tarih.isoformat()
return {
"tarih": tarih,
"hedefkodu": r.get("HEDEFKODU"),
"musteri": r.get("MUSTERI"),
"barkod": r.get("BARCODE"),
"durum": r.get("SHIPPINGSTATUS_TEXT"),
"servis": r.get("SERVICENAMETUR"),
"fatura": r.get("INVOICECODE"),
"sofor": r.get("SERVICEDRIVERNAME"),
"arac": r.get("SERVICEVEHICLEPLATE"),
"teslim_lok": r.get("DELIVEREDLOCATION"),
# İstersen ekstra alanlar:
"teslim_dogrulama": r.get("DELIVERYVALIDATIONTYPE_TEXT"),
"servis_tipi": r.get("SERVICETYPE_TEXT"),
"bolge": r.get("PACKAGEREGIONNAMETUR"),
"depo": r.get("PACKAGEWAREHOUSENAMETUR"),
"cikis": r.get("DEPARTUREMANAGERNAMETUR"),
"varis": r.get("ARRIVALMANAGERNAMETUR"),
"datemodified": (r.get("DATEMODIFIED").isoformat() if isinstance(r.get("DATEMODIFIED"), datetime) else r.get("DATEMODIFIED")),
"servicetimestarts": (r.get("SERVICETIMESTARTS").isoformat() if isinstance(r.get("SERVICETIMESTARTS"), datetime) else r.get("SERVICETIMESTARTS")),
}
def _build_filters(
barcode: Optional[str],
invoice: Optional[str],
durum: Optional[str],
) -> (str, Dict[str, Any]):
extra = ""
binds: Dict[str, Any] = {}
if barcode:
extra += " AND ppb.BARCODE LIKE :barcode "
binds["barcode"] = f"%{barcode.strip()}%"
if invoice:
# invoicecode subquery’den geldiği için burada aynı subquery’yi WHERE’de tekrar kullanmak pahalı.
# Basit çözüm: outer query’i CTE’ye alıp invoicecode üzerinde filtrelemek.
# O yüzden bunu CTE’li sorguda uygulayacağız.
binds["invoice"] = invoice.strip()
if durum and durum != "Hepsi":
# Frontend "Teslim Edildi" gibi metin yolluyorsa:
extra += " AND (CASE ppb.SHIPPINGSTATUS " \
" WHEN 0 THEN 'Sevk Bekliyor' " \
" WHEN 1 THEN 'Sevk Kontrolü Yapıldı' " \
" WHEN 2 THEN 'Sevk Başladı' " \
" WHEN 3 THEN 'Transfer Edildi' " \
" WHEN 4 THEN 'Teslim Edildi' " \
" WHEN 5 THEN 'Teslim Edilemedi' " \
" WHEN 99 THEN 'İptal Edildi' " \
" ELSE NULL END) = :durum "
binds["durum"] = durum.strip()
return extra, binds
def _fetch_report(
start: str,
end: str,
page: int,
page_size: int,
barcode: Optional[str],
invoice: Optional[str],
durum: Optional[str],
) -> Dict[str, Any]:
start_dt = _parse_date_only(start)
end_dt_excl = _make_end_exclusive(end)
base_extra_where, extra_binds = _build_filters(barcode, invoice, durum)
# CTE: invoice filtreleme vb. için outer katmanda kullanacağız
cte = f"""
WITH base AS (
{SQL_BASE}
{base_extra_where}
)
"""
# invoice filtre (outer)
invoice_where = ""
if invoice:
invoice_where = " WHERE INVOICECODE LIKE :invoice "
# total
total_sql = cte + f"SELECT COUNT(1) AS CNT FROM base{invoice_where}"
# kpi
kpi_sql = cte + f"""
SELECT SHIPPINGSTATUS_TEXT AS DURUM, COUNT(1) AS ADET
FROM base
{invoice_where}
GROUP BY SHIPPINGSTATUS_TEXT
ORDER BY ADET DESC
"""
# items (pagination)
offset = max(page - 1, 0) * page_size
items_sql = cte + f"""
SELECT *
FROM base
{invoice_where}
ORDER BY DATECREATED DESC
OFFSET :_offset ROWS FETCH NEXT :_limit ROWS ONLY
"""
binds = {
"start_date": start_dt,
"end_date": end_dt_excl,
**extra_binds,
"_offset": offset,
"_limit": page_size,
}
with get_connection() as conn:
cur = conn.cursor()
# total
cur.execute(total_sql, binds)
total = int(cur.fetchone()[0])
# kpi
cur.execute(kpi_sql, binds)
kpi_rows = cur.fetchall()
# items
cur.execute(items_sql, binds)
cols = [d[0] for d in cur.description]
rows = cur.fetchall()
# KPI yüzdeler
kpi: List[Dict[str, Any]] = []
for durum_text, adet in kpi_rows:
adet_i = int(adet)
yuzde = round((adet_i / total * 100), 2) if total else 0
kpi.append({"durum": durum_text, "adet": adet_i, "yuzde": yuzde})
# satırları dict’e çevir
items_dicts = [dict(zip(cols, r)) for r in rows]
items = [_row_to_ui_item(r) for r in items_dicts]
return {
"range": {"start": start, "end": end},
"page": page,
"page_size": page_size,
"total": total,
"kpi": kpi,
"items": items,
}
@app.get("/db-test")
def db_test():
try:
with get_connection() as conn:
cur = conn.cursor()
cur.execute("SELECT 1 FROM dual")
_ = cur.fetchone()
return {"ok": 1}
except Exception as e:
return JSONResponse(status_code=500, content={"ok": 0, "error": str(e)})
# ✅ Frontend hangisini çağırırsa çağırsa çalışsın diye iki route:
@app.get("/rapor")
@app.get("/api/rapor")
def rapor_api(
start: str = Query(..., description="YYYY-MM-DD"),
end: str = Query(..., description="YYYY-MM-DD"),
page: int = Query(1, ge=1),
page_size: int = Query(50, ge=1, le=500),
barcode: Optional[str] = Query(None),
invoice: Optional[str] = Query(None),
durum: Optional[str] = Query(None),
):
try:
data = _fetch_report(start, end, page, page_size, barcode, invoice, durum)
return data
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
*/*/*/*/*/**
# app/db.py
import os
import oracledb
from dotenv import load_dotenv
load_dotenv()
_client_inited = False
def _init_oracle_client_if_needed():
"""Thin mode yeterliyse client gerekmez. Instant Client varsa kullan."""
global _client_inited
if _client_inited:
return
lib_dir = os.getenv("ORACLE_CLIENT_LIB_DIR")
if lib_dir:
try:
oracledb.init_oracle_client(lib_dir=lib_dir)
except Exception:
# zaten init edilmiş olabilir, sorun değil
pass
_client_inited = True
def get_conn():
_init_oracle_client_if_needed()
user = os.getenv("ORACLE_USER")
password = os.getenv("ORACLE_PASSWORD")
dsn = os.getenv("ORACLE_DSN") # ör: host:port/service_name veya TNS alias
if not all([user, password, dsn]):
raise RuntimeError("ORACLE_USER / ORACLE_PASSWORD / ORACLE_DSN .env içinde eksik")
return oracledb.connect(user=user, password=password, dsn=dsn)
def _rows_to_dicts(cursor, rows):
cols = [d[0].lower() for d in cursor.description] # kolon adlarını lower yap
out = []
for row in rows:
out.append({cols[i]: row[i] for i in range(len(cols))})
return out
def fetch_all(sql: str, params: dict | None = None):
params = params or {}
with get_conn() as conn:
with conn.cursor() as cur:
cur.execute(sql, params)
rows = cur.fetchall()
return _rows_to_dicts(cur, rows)
def fetch_one(sql: str, params: dict | None = None):
params = params or {}
with get_conn() as conn:
with conn.cursor() as cur:
cur.execute(sql, params)
row = cur.fetchone()
if not row:
return None
cols = [d[0].lower() for d in cur.description]
return {cols[i]: row[i] for i in range(len(cols))}
# app/main.py
import csv
import io
import os
from datetime import date, datetime, timedelta
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from app.db import fetch_all, fetch_one
load_dotenv()
app = FastAPI(title="Rapor Dashboard", version="1.0.0")
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
templates = Jinja2Templates(directory=os.path.join(BASE_DIR, "templates"))
def _date_to_dt(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0)
# ✅ SQL: UI kolon isimleriyle alias
BASE_SQL = """
SELECT
ppb.DATECREATED AS tarih,
(
SELECT MAX(TO_NUMBER(pv.VALUETEXTTUR))
FROM CONTENTCC_C_PROPERTYVALUE pv
JOIN CONTENTCC_C_TPROPERTYVALUE tpv
ON pv.PROPERTYVALUEID = tpv.PROPERTYVALUEID
WHERE pv.PROPERTYID = 19
AND tpv.TITLEID = tl.TITLEID
) AS hedefkodu,
(pl.NAME || ',' || adr.DISTRICTNAME || ',' || adr.CITYNAME) AS musteri,
ppb.BARCODE AS barkod,
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN 'Sevk Bekliyor'
WHEN 1 THEN 'Sevk Kontrolü Yapıldı'
WHEN 2 THEN 'Sevk Başladı'
WHEN 3 THEN 'Transfer Edildi'
WHEN 4 THEN 'Teslim Edildi'
WHEN 5 THEN 'Teslim Edilemedi'
WHEN 99 THEN 'İptal Edildi'
ELSE NULL
END AS durum,
srv.NAMETUR AS servis,
(
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) AS fatura,
drv.NAME AS sofor,
veh.PLATE AS arac,
(
SELECT DeliveredPoint
FROM (
SELECT sp.DeliveredPoint,
ROW_NUMBER() OVER (ORDER BY sp.DeliveredTime DESC) rn
FROM Transportation_ServicePackage sp
WHERE sp.PackagePackBarcodeID = ppb.PackagePackBarcodeID
AND sp.IsDelivered = 1
)
WHERE rn = 1
) AS teslim_lok
FROM TRS_PACKAGEPACKBARCODE ppb
JOIN TRANSPORTATION_PACKAGE pkg
ON pkg.PACKAGEID = ppb.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION reg
ON reg.REGIONID = pkg.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE srv
ON srv.SERVICEID = ppb.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE veh
ON veh.VEHICLEID = srv.VEHICLEID
LEFT JOIN COLLABORATION_EMPLOYEE drv
ON drv.EMPLOYEEID = srv.DRIVERID
LEFT JOIN TRADING_LINK pl
ON pl.LINKID = pkg.LINKID
INNER JOIN TRADING_LINK tl
ON tl.LINKID = pkg.LINKID
LEFT JOIN CONTACTCC_CONTACT c
ON c.CONTACTID = pl.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS adr
ON adr.ADDRESSID = c.DEFAULTADDRESSID
WHERE
pl.NAME NOT LIKE '%All%'
AND reg.NAMETUR IS NOT NULL
AND reg.NAMETUR <> 'NULL'
AND ppb.DATECREATED >= :start_dt
AND ppb.DATECREATED < :end_dt
"""
FILTERS_SQL = """
AND (:barkod IS NULL OR LOWER(barkod) LIKE LOWER(:barkod_like))
AND (:fatura IS NULL OR TO_CHAR(fatura) LIKE :fatura_like)
AND (:durum IS NULL OR durum = :durum)
"""
ORDER_SQL = " ORDER BY tarih DESC "
@app.get("/", response_class=HTMLResponse)
def home(request: Request):
# direkt rapor sayfasına yönlendirilebilir istersen
return templates.TemplateResponse("rapor.html", {"request": request})
@app.get("/rapor", response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse("rapor.html", {"request": request})
@app.get("/db-test")
def db_test():
try:
row = fetch_one("SELECT 1 AS ok FROM dual")
return {"ok": row["ok"] if row else 1}
except Exception as e:
return JSONResponse(status_code=500, content={"ok": False, "error": str(e)})
@app.get("/api/rapor")
def rapor_api(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barkod: str | None = None,
fatura: str | None = None,
durum: str | None = None,
page: int = 1,
page_size: int = 50,
):
"""
✅ Frontend KPI+Tablo kesin dolsun diye:
- items + rows ikisini de döndürür
- kpi + stats ikisini de döndürür
- satırların içinde hem yeni alanlar hem eski olası alanlar var
"""
try:
page = max(1, page)
page_size = min(max(1, page_size), 500)
start_dt = _date_to_dt(start)
end_dt = _date_to_dt(end) + timedelta(days=1) # ✅ bitiş günü dahil
params = {
"start_dt": start_dt,
"end_dt": end_dt,
"barkod": barkod,
"barkod_like": f"%{barkod}%" if barkod else None,
"fatura": fatura,
"fatura_like": f"%{fatura}%" if fatura else None,
"durum": durum,
"offset": (page - 1) * page_size,
"limit": page_size,
}
count_sql = f"""
SELECT COUNT(1) AS total
FROM (
{BASE_SQL}
{FILTERS_SQL}
)
"""
total_row = fetch_one(count_sql, params)
total = int(total_row["total"]) if total_row and total_row.get("total") is not None else 0
data_sql = f"""
SELECT *
FROM (
{BASE_SQL}
{FILTERS_SQL}
)
{ORDER_SQL}
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
"""
raw_items = fetch_all(data_sql, params)
# KPI
kpi_sql = f"""
SELECT durum, COUNT(1) AS adet
FROM (
{BASE_SQL}
{FILTERS_SQL}
)
GROUP BY durum
ORDER BY COUNT(1) DESC
"""
kpi_rows = fetch_all(kpi_sql, params)
kpi = []
for r in kpi_rows:
adet = int(r.get("adet") or 0)
pct = round((adet / total) * 100, 2) if total else 0
kpi.append({"durum": r.get("durum"), "adet": adet, "yuzde": pct})
# ✅ UI’ye uyumlu item map (hem yeni hem eski alan adları)
items = []
for r in raw_items:
items.append({
# UI alanları
"tarih": r.get("tarih"),
"hedefkodu": r.get("hedefkodu"),
"musteri": r.get("musteri"),
"barkod": r.get("barkod"),
"durum": r.get("durum"),
"servis": r.get("servis"),
"fatura": r.get("fatura"),
"sofor": r.get("sofor"),
"arac": r.get("arac"),
"teslim_lok": r.get("teslim_lok"),
# Olası eski alanlar (frontend bunları çağırıyorsa)
"datecreated": r.get("tarih"),
"barcode": r.get("barkod"),
"shippingstatus_text": r.get("durum"),
"servicenametur": r.get("servis"),
"invoicecode": r.get("fatura"),
"servicedrivername": r.get("sofor"),
"servicevehicleplate": r.get("arac"),
"deliveredlocation": r.get("teslim_lok"),
})
return {
"range": {"start": str(start), "end": str(end)},
"page": page,
"page_size": page_size,
"total": total,
# iki isim (kpi/stats)
"kpi": kpi,
"stats": kpi,
# iki isim (items/rows)
"items": items,
"rows": items,
}
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
@app.get("/api/export.csv")
def export_csv(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barkod: str | None = None,
fatura: str | None = None,
durum: str | None = None,
):
try:
start_dt = _date_to_dt(start)
end_dt = _date_to_dt(end) + timedelta(days=1)
params = {
"start_dt": start_dt,
"end_dt": end_dt,
"barkod": barkod,
"barkod_like": f"%{barkod}%" if barkod else None,
"fatura": fatura,
"fatura_like": f"%{fatura}%" if fatura else None,
"durum": durum,
}
sql = f"""
SELECT *
FROM (
{BASE_SQL}
{FILTERS_SQL}
)
{ORDER_SQL}
"""
rows = fetch_all(sql, params)
output = io.StringIO()
writer = csv.writer(output)
headers = ["tarih", "hedefkodu", "musteri", "barkod", "durum", "servis", "fatura", "sofor", "arac", "teslim_lok"]
writer.writerow(headers)
for r in rows:
writer.writerow([r.get(h) for h in headers])
output.seek(0)
filename = f"rapor_{start}_{end}.csv"
return StreamingResponse(
iter([output.getvalue()]),
media_type="text/csv; charset=utf-8",
headers={"Content-Disposition": f'attachment; filename="{filename}"'},
)
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
*********************************
# app/db.py
import os
import oracledb
from dotenv import load_dotenv
# .env'yi burada da garanti yükleyelim
load_dotenv()
_client_inited = False
_pool = None
def _init_oracle_client_if_needed():
"""
ORACLE_CLIENT_LIB_DIR doluysa thick mode dener.
Boşsa thin mode ile devam eder.
"""
global _client_inited
if _client_inited:
return
lib_dir = os.getenv("ORACLE_CLIENT_LIB_DIR")
if lib_dir and os.path.isdir(lib_dir):
try:
oracledb.init_oracle_client(lib_dir=lib_dir)
except Exception:
# Zaten init edilmiş olabilir / thin'de kalabilir
pass
_client_inited = True
def _get_env(name: str, default=None):
v = os.getenv(name, default)
if v is None:
return None
v = str(v).strip()
return v if v != "" else None
def get_pool():
"""
Tek bir pool oluşturup reuse eder.
"""
global _pool
if _pool is not None:
return _pool
_init_oracle_client_if_needed()
user = _get_env("ORACLE_USER")
password = _get_env("ORACLE_PASSWORD")
host = _get_env("ORACLE_HOST")
port = _get_env("ORACLE_PORT", "1521")
service = _get_env("ORACLE_SERVICE")
if not all([user, password, host, port, service]):
raise RuntimeError("ENV eksik: ORACLE_USER / ORACLE_PASSWORD / ORACLE_HOST / ORACLE_PORT / ORACLE_SERVICE")
# DSN
dsn = f"{host}:{port}/{service}"
# Pool
_pool = oracledb.create_pool(
user=user,
password=password,
dsn=dsn,
min=1,
max=5,
increment=1,
timeout=60,
wait_timeout=60,
)
return _pool
def _dict_row_factory(cursor):
"""
oracledb.dictfactory yok -> biz yapıyoruz.
Kolonları küçük harfe çevirip dict döndürür.
"""
colnames = [d[0].lower() for d in cursor.description]
def _make_row(*args):
return {colnames[i]: args[i] for i in range(len(colnames))}
return _make_row
def fetch_one(sql: str, params: dict | None = None):
pool = get_pool()
with pool.acquire() as conn:
with conn.cursor() as cur:
cur.rowfactory = _dict_row_factory(cur)
cur.execute(sql, params or {})
return cur.fetchone()
def fetch_all(sql: str, params: dict | None = None):
pool = get_pool()
with pool.acquire() as conn:
with conn.cursor() as cur:
cur.rowfactory = _dict_row_factory(cur)
cur.execute(sql, params or {})
return cur.fetchall()
# app/main.py
import csv
import io
import os
from datetime import date, datetime, timedelta
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from app.db import fetch_all, fetch_one
load_dotenv()
app = FastAPI(title="Panel Rapor", version="1.0.0")
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, "templates")
templates = Jinja2Templates(directory=TEMPLATES_DIR)
def _date_start_dt(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0)
def _date_end_dt_exclusive(d: date) -> datetime:
# end günü dahil olsun diye +1 gün ve "< end_dt"
return datetime(d.year, d.month, d.day, 0, 0, 0) + timedelta(days=1)
# =========================
# ✅ SENİN SQL (Temel Query)
# =========================
BASE_SQL = """
SELECT
(
SELECT MAX(TO_NUMBER(pv.VALUETEXTTUR))
FROM CONTENTCC_C_PROPERTYVALUE pv
JOIN CONTENTCC_C_TPROPERTYVALUE tpv
ON pv.PROPERTYVALUEID = tpv.PROPERTYVALUEID
WHERE pv.PROPERTYID = 19
AND tpv.TITLEID = tl.TITLEID
) AS HEDEFKODU,
(pl.NAME || ',' || adr.DISTRICTNAME || ',' || adr.CITYNAME) AS MUSTERI,
ppb.DATECREATED,
srv.TIMESTARTS AS SERVICETIMESTARTS,
ppb.DATEMODIFIED,
(
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) AS INVOICECODE,
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN 'Kart Okutuldu'
WHEN 0 THEN 'Kart Okutulmadı'
ELSE NULL
END AS DELIVERYVALIDATIONTYPE_TEXT,
ppb.BARCODE,
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN 'Sevk Bekliyor'
WHEN 1 THEN 'Sevk Kontrolü Yapıldı'
WHEN 2 THEN 'Sevk Başladı'
WHEN 3 THEN 'Transfer Edildi'
WHEN 4 THEN 'Teslim Edildi'
WHEN 5 THEN 'Teslim Edilemedi'
WHEN 99 THEN 'İptal Edildi'
ELSE NULL
END AS SHIPPINGSTATUS_TEXT,
CASE srv.ISEXPRESSSERVICE
WHEN 1 THEN 'Hızlı Teslimat'
WHEN 0 THEN 'Servis'
ELSE NULL
END AS SERVICETYPE_TEXT,
reg.NAMETUR AS PACKAGEREGIONNAMETUR,
srv.NAMETUR AS SERVICENAMETUR,
wh.NAMETUR AS PACKAGEWAREHOUSENAMETUR,
dep.NAMETUR AS DEPARTUREMANAGERNAMETUR,
arr.NAMETUR AS ARRIVALMANAGERNAMETUR,
drv.NAME AS SERVICEDRIVERNAME,
veh.PLATE AS SERVICEVEHICLEPLATE,
(
SELECT DeliveredPoint
FROM (
SELECT sp.DeliveredPoint,
ROW_NUMBER() OVER (ORDER BY sp.DeliveredTime DESC) rn
FROM Transportation_ServicePackage sp
WHERE sp.PackagePackBarcodeID = ppb.PackagePackBarcodeID
AND sp.IsDelivered = 1
)
WHERE rn = 1
) AS DELIVEREDLOCATION
FROM TRS_PACKAGEPACKBARCODE ppb
JOIN TRANSPORTATION_PACKAGE pkg
ON pkg.PACKAGEID = ppb.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION reg
ON reg.REGIONID = pkg.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE srv
ON srv.SERVICEID = ppb.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE veh
ON veh.VEHICLEID = srv.VEHICLEID
LEFT JOIN COLLABORATION_NODE wh
ON wh.NODEID = pkg.WAREHOUSEID
LEFT JOIN COLLABORATION_NODE dep
ON dep.NODEID = ppb.DEPARTUREMANAGERID
LEFT JOIN COLLABORATION_NODE arr
ON arr.NODEID = ppb.ARRIVALMANAGERID
LEFT JOIN COLLABORATION_EMPLOYEE drv
ON drv.EMPLOYEEID = srv.DRIVERID
LEFT JOIN TRADING_LINK pl
ON pl.LINKID = pkg.LINKID
INNER JOIN TRADING_LINK tl
ON tl.LINKID = pkg.LINKID
LEFT JOIN CONTACTCC_CONTACT c
ON c.CONTACTID = pl.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS adr
ON adr.ADDRESSID = c.DEFAULTADDRESSID
WHERE
pl.NAME NOT LIKE '%All%'
AND reg.NAMETUR IS NOT NULL
AND reg.NAMETUR <> 'NULL'
AND ppb.DATECREATED >= :start_date
AND ppb.DATECREATED < :end_date
ORDER BY ppb.DATECREATED DESC
"""
def build_paged_sql(base_sql: str) -> str:
# Oracle pagination için dıştan sarıyoruz
return f"""
SELECT * FROM (
{base_sql}
)
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
"""
def build_count_sql(base_sql: str) -> str:
# ORDER BY sayımda gereksiz, ama en güvenlisi: subquery ile count
return f"SELECT COUNT(1) AS total FROM ( {base_sql} )"
# =========
# ROUTES
# =========
@app.get("/", response_class=HTMLResponse)
def root_redirect(request: Request):
# İstersen direkt rapor sayfası açılsın
return templates.TemplateResponse("rapor.html", {"request": request})
@app.get("/rapor", response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse("rapor.html", {"request": request})
# ✅ hem /db-test hem /api/db-test destek
@app.get("/db-test")
@app.get("/api/db-test")
def db_test():
try:
row = fetch_one("SELECT 1 AS ok FROM dual")
return {"ok": row["ok"] if row else 1}
except Exception as e:
return JSONResponse(status_code=500, content={"ok": False, "error": str(e)})
# ✅ hem /rapor-data hem /api/rapor destek
@app.get("/rapor-data")
@app.get("/api/rapor")
def rapor_data(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
page: int = 1,
page_size: int = 50,
):
try:
if page < 1:
page = 1
if page_size < 1:
page_size = 50
if page_size > 500:
page_size = 500
start_dt = _date_start_dt(start)
end_dt = _date_end_dt_exclusive(end)
params_common = {
"start_date": start_dt,
"end_date": end_dt,
}
# total
total_row = fetch_one(build_count_sql(BASE_SQL), params_common)
total = int(total_row["total"]) if total_row and total_row.get("total") is not None else 0
# items
params_items = dict(params_common)
params_items["offset"] = (page - 1) * page_size
params_items["limit"] = page_size
items = fetch_all(build_paged_sql(BASE_SQL), params_items)
# KPI örneği: SHIPPINGSTATUS_TEXT'e göre adet/yüzde
kpi_sql = f"""
SELECT shippingstatus_text AS durum, COUNT(1) AS adet
FROM ( {BASE_SQL} )
GROUP BY shippingstatus_text
ORDER BY COUNT(1) DESC
"""
kpi_rows = fetch_all(kpi_sql, params_common)
kpi = []
for r in kpi_rows:
adet = int(r.get("adet") or 0)
pct = round((adet / total) * 100, 2) if total else 0
kpi.append({"durum": r.get("durum"), "adet": adet, "yuzde": pct})
return {
"range": {"start": str(start), "end": str(end)},
"page": page,
"page_size": page_size,
"total": total,
"kpi": kpi,
"items": items,
}
except Exception as e:
# Frontend “Unexpected token I” yemesin diye daima JSON dön
return JSONResponse(status_code=500, content={"error": str(e)})
# ✅ CSV export (hem /export.csv hem /api/export.csv)
@app.get("/export.csv")
@app.get("/api/export.csv")
def export_csv(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
):
try:
start_dt = _date_start_dt(start)
end_dt = _date_end_dt_exclusive(end)
params = {"start_date": start_dt, "end_date": end_dt}
rows = fetch_all(BASE_SQL, params)
output = io.StringIO()
writer = csv.writer(output)
# SQL'deki aliaslara göre header
headers = [
"hedefkodu", "musteri", "datecreated", "servicetimestarts", "datemodified",
"invoicecode", "deliveryvalidationtype_text", "barcode", "shippingstatus_text",
"servicetype_text", "packageregionnametur", "servicenametur",
"packagewarehousenametur", "departuremanagernametur", "arrivalmanagernametur",
"servicedrivername", "servicevehicleplate", "deliveredlocation",
]
writer.writerow(headers)
for r in rows:
writer.writerow([r.get(h) for h in headers])
output.seek(0)
filename = f"rapor_{start}_{end}.csv"
return StreamingResponse(
iter([output.getvalue()]),
media_type="text/csv; charset=utf-8",
headers={"Content-Disposition": f'attachment; filename="{filename}"'},
)
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
////////////////////////
# app/db.py
import os
import oracledb
from dotenv import load_dotenv
# .env mutlaka yüklensin
load_dotenv()
_client_inited = False
def _init_oracle_client_if_needed():
"""Instant Client varsa thick mode, yoksa thin mode ile devam."""
global _client_inited
if _client_inited:
return
lib_dir = os.getenv("ORACLE_CLIENT_LIB_DIR")
if lib_dir and os.path.isdir(lib_dir):
try:
oracledb.init_oracle_client(lib_dir=lib_dir)
except Exception:
# zaten init edilmiş olabilir, sorun etmeyelim
pass
_client_inited = True
def _get_env(name: str, required: bool = True, default: str | None = None) -> str | None:
val = os.getenv(name, default)
if val is None:
if required:
raise RuntimeError(f"ENV eksik: {name}")
return None
val = str(val).strip()
if val == "":
if required:
raise RuntimeError(f"ENV boş: {name}")
return None
return val
def get_connection():
_init_oracle_client_if_needed()
user = _get_env("ORACLE_USER")
password = _get_env("ORACLE_PASSWORD")
host = _get_env("ORACLE_HOST")
port = _get_env("ORACLE_PORT")
service = _get_env("ORACLE_SERVICE")
# DSN
dsn = oracledb.makedsn(host, int(port), service_name=service)
conn = oracledb.connect(user=user, password=password, dsn=dsn)
return conn
def fetch_all(sql: str, params: dict | None = None) -> list[dict]:
params = params or {}
with get_connection() as conn:
conn.rowfactory = oracledb.dictfactory
with conn.cursor() as cur:
cur.execute(sql, params)
rows = cur.fetchall()
# dictfactory zaten dict döner
return rows
def fetch_one(sql: str, params: dict | None = None) -> dict | None:
params = params or {}
with get_connection() as conn:
conn.rowfactory = oracledb.dictfactory
with conn.cursor() as cur:
cur.execute(sql, params)
row = cur.fetchone()
return row
# app/main.py
import csv
import io
import os
from datetime import date, datetime, timedelta
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from app.db import fetch_all, fetch_one
load_dotenv()
app = FastAPI(title="Panel Rapor", version="1.0.0")
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, "templates")
templates = Jinja2Templates(directory=TEMPLATES_DIR)
def _date_to_dt(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0)
# ✅ SENİN SQL (aynı mantık) - bind adlarını ORA-01745 yememek için p1,p2 yaptım
REPORT_SQL_BASE = """
SELECT
(
SELECT MAX(TO_NUMBER(pv.VALUETEXTTUR))
FROM CONTENTCC_C_PROPERTYVALUE pv
JOIN CONTENTCC_C_TPROPERTYVALUE tpv
ON pv.PROPERTYVALUEID = tpv.PROPERTYVALUEID
WHERE pv.PROPERTYID = 19
AND tpv.TITLEID = tl.TITLEID
) AS HEDEFKODU,
(pl.NAME || ',' || adr.DISTRICTNAME || ',' || adr.CITYNAME) AS MUSTERI,
ppb.DATECREATED,
srv.TIMESTARTS AS SERVICETIMESTARTS,
ppb.DATEMODIFIED,
(
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) AS INVOICECODE,
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN 'Kart Okutuldu'
WHEN 0 THEN 'Kart Okutulmadı'
ELSE NULL
END AS DELIVERYVALIDATIONTYPE_TEXT,
ppb.BARCODE,
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN 'Sevk Bekliyor'
WHEN 1 THEN 'Sevk Kontrolü Yapıldı'
WHEN 2 THEN 'Sevk Başladı'
WHEN 3 THEN 'Transfer Edildi'
WHEN 4 THEN 'Teslim Edildi'
WHEN 5 THEN 'Teslim Edilemedi'
WHEN 99 THEN 'İptal Edildi'
ELSE NULL
END AS SHIPPINGSTATUS_TEXT,
CASE srv.ISEXPRESSSERVICE
WHEN 1 THEN 'Hızlı Teslimat'
WHEN 0 THEN 'Servis'
ELSE NULL
END AS SERVICETYPE_TEXT,
reg.NAMETUR AS PACKAGEREGIONNAMETUR,
srv.NAMETUR AS SERVICENAMETUR,
wh.NAMETUR AS PACKAGEWAREHOUSENAMETUR,
dep.NAMETUR AS DEPARTUREMANAGERNAMETUR,
arr.NAMETUR AS ARRIVALMANAGERNAMETUR,
drv.NAME AS SERVICEDRIVERNAME,
veh.PLATE AS SERVICEVEHICLEPLATE,
(
SELECT DeliveredPoint
FROM (
SELECT sp.DeliveredPoint,
ROW_NUMBER() OVER (ORDER BY sp.DeliveredTime DESC) rn
FROM Transportation_ServicePackage sp
WHERE sp.PackagePackBarcodeID = ppb.PackagePackBarcodeID
AND sp.IsDelivered = 1
)
WHERE rn = 1
) AS DELIVEREDLOCATION
FROM TRS_PACKAGEPACKBARCODE ppb
JOIN TRANSPORTATION_PACKAGE pkg
ON pkg.PACKAGEID = ppb.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION reg
ON reg.REGIONID = pkg.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE srv
ON srv.SERVICEID = ppb.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE veh
ON veh.VEHICLEID = srv.VEHICLEID
LEFT JOIN COLLABORATION_NODE wh
ON wh.NODEID = pkg.WAREHOUSEID
LEFT JOIN COLLABORATION_NODE dep
ON dep.NODEID = ppb.DEPARTUREMANAGERID
LEFT JOIN COLLABORATION_NODE arr
ON arr.NODEID = ppb.ARRIVALMANAGERID
LEFT JOIN COLLABORATION_EMPLOYEE drv
ON drv.EMPLOYEEID = srv.DRIVERID
LEFT JOIN TRADING_LINK pl
ON pl.LINKID = pkg.LINKID
INNER JOIN TRADING_LINK tl
ON tl.LINKID = pkg.LINKID
LEFT JOIN CONTACTCC_CONTACT c
ON c.CONTACTID = pl.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS adr
ON adr.ADDRESSID = c.DEFAULTADDRESSID
WHERE
pl.NAME NOT LIKE '%All%'
AND reg.NAMETUR IS NOT NULL
AND reg.NAMETUR <> 'NULL'
AND ppb.DATECREATED >= :p1
AND ppb.DATECREATED < :p2
"""
@app.get("/", response_class=HTMLResponse)
def home(request: Request):
# İstersen direkt /rapor'a yönlendirirsin; şimdilik basit sayfa
return templates.TemplateResponse("rapor.html", {"request": request})
@app.get("/rapor", response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse("rapor.html", {"request": request})
# ✅ Hem /db-test hem de eski alışkanlık için /api/db-test
@app.get("/db-test")
@app.get("/api/db-test")
def db_test():
try:
row = fetch_one("SELECT 1 AS ok FROM dual")
return {"ok": row["OK"] if row and "OK" in row else 1}
except Exception as e:
return JSONResponse(status_code=500, content={"ok": False, "error": str(e)})
def _apply_filters(sql: str, barkod: str | None, invoicecode: str | None, shippingstatus: str | None):
# Filtreleri sonradan ekliyoruz (bind isimleri güvenli)
extra = ""
params = {}
if barkod:
extra += " AND LOWER(ppb.BARCODE) LIKE :p3 "
params["p3"] = f"%{barkod.lower()}%"
if invoicecode:
# invoicecode SELECT içinden geliyor, aynı subquery'yi WHERE'e koymak ağır olur.
# En pratik çözüm: dışarıda tekrar subquery ile filtre.
extra += """
AND (
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) LIKE :p4
"""
params["p4"] = f"%{invoicecode}%"
if shippingstatus:
# shippingstatus_text CASE ile geliyor, direk CASE'i tekrar yazıp filtreleyeceğiz
extra += """
AND (
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN 'Sevk Bekliyor'
WHEN 1 THEN 'Sevk Kontrolü Yapıldı'
WHEN 2 THEN 'Sevk Başladı'
WHEN 3 THEN 'Transfer Edildi'
WHEN 4 THEN 'Teslim Edildi'
WHEN 5 THEN 'Teslim Edilemedi'
WHEN 99 THEN 'İptal Edildi'
ELSE NULL
END
) = :p5
"""
params["p5"] = shippingstatus
return sql + extra, params
# ✅ Data endpoint: /rapor-data (JSON)
# ✅ Eski JS bozulmasın diye: /api/rapor da aynı
@app.get("/rapor-data")
@app.get("/api/rapor")
def rapor_data(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barkod: str | None = None,
invoicecode: str | None = None,
shippingstatus: str | None = None,
page: int = 1,
page_size: int = 50,
):
try:
if page < 1:
page = 1
if page_size < 1:
page_size = 50
if page_size > 500:
page_size = 500
start_dt = _date_to_dt(start)
end_dt = _date_to_dt(end) + timedelta(days=1) # end günü dahil
base_sql, filter_params = _apply_filters(REPORT_SQL_BASE, barkod, invoicecode, shippingstatus)
# COUNT (total)
count_sql = f"SELECT COUNT(1) AS TOTAL FROM ({base_sql}) X"
total_row = fetch_one(count_sql, {"p1": start_dt, "p2": end_dt, **filter_params})
total = int(total_row["TOTAL"]) if total_row and total_row.get("TOTAL") is not None else 0
# DATA (pagination)
offset = (page - 1) * page_size
data_sql = f"""
SELECT * FROM (
{base_sql}
ORDER BY ppb.DATECREATED DESC
)
OFFSET :p6 ROWS FETCH NEXT :p7 ROWS ONLY
"""
params = {"p1": start_dt, "p2": end_dt, "p6": offset, "p7": page_size, **filter_params}
items = fetch_all(data_sql, params)
# KPI: SHIPPINGSTATUS_TEXT bazında sayım
kpi_sql = f"""
SELECT SHIPPINGSTATUS_TEXT, COUNT(1) AS ADET
FROM (
{base_sql}
)
GROUP BY SHIPPINGSTATUS_TEXT
ORDER BY COUNT(1) DESC
"""
kpi_rows = fetch_all(kpi_sql, {"p1": start_dt, "p2": end_dt, **filter_params})
kpi = []
for r in kpi_rows:
adet = int(r.get("ADET") or 0)
pct = round((adet / total) * 100, 2) if total else 0
kpi.append(
{"durum": r.get("SHIPPINGSTATUS_TEXT"), "adet": adet, "yuzde": pct}
)
return {
"range": {"start": str(start), "end": str(end)},
"page": page,
"page_size": page_size,
"total": total,
"kpi": kpi,
"items": items,
}
except Exception as e:
# ✅ JSON dönmezsen tarayıcı "Unexpected token I" görür
return JSONResponse(status_code=500, content={"error": str(e)})
@app.get("/export.csv")
def export_csv(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barkod: str | None = None,
invoicecode: str | None = None,
shippingstatus: str | None = None,
):
try:
start_dt = _date_to_dt(start)
end_dt = _date_to_dt(end) + timedelta(days=1)
base_sql, filter_params = _apply_filters(REPORT_SQL_BASE, barkod, invoicecode, shippingstatus)
sql = f"""
{base_sql}
ORDER BY ppb.DATECREATED DESC
"""
rows = fetch_all(sql, {"p1": start_dt, "p2": end_dt, **filter_params})
output = io.StringIO()
writer = csv.writer(output)
# kolon başlıkları (DB'den gelen key'lere göre)
headers = [
"HEDEFKODU", "MUSTERI", "DATECREATED", "SERVICETIMESTARTS", "DATEMODIFIED",
"INVOICECODE", "DELIVERYVALIDATIONTYPE_TEXT", "BARCODE",
"SHIPPINGSTATUS_TEXT", "SERVICETYPE_TEXT",
"PACKAGEREGIONNAMETUR", "SERVICENAMETUR", "PACKAGEWAREHOUSENAMETUR",
"DEPARTUREMANAGERNAMETUR", "ARRIVALMANAGERNAMETUR",
"SERVICEDRIVERNAME", "SERVICEVEHICLEPLATE", "DELIVEREDLOCATION",
]
writer.writerow(headers)
for r in rows:
writer.writerow([r.get(h) for h in headers])
output.seek(0)
filename = f"rapor_{start}_{end}.csv"
return StreamingResponse(
iter([output.getvalue()]),
media_type="text/csv; charset=utf-8",
headers={"Content-Disposition": f'attachment; filename="{filename}"'},
)
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
# app/main.py
import csv
import io
import os
from datetime import date, datetime, timedelta
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from app.db import fetch_all, fetch_one
load_dotenv()
app = FastAPI(title="Panel Rapor", version="1.0.0")
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, "templates")
templates = Jinja2Templates(directory=TEMPLATES_DIR)
def _date_to_dt(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0)
# =========================================================
# ✅ SENİN SQL (aynı mantık, sadece sayfalama/kpi için sarmalladık)
# =========================================================
REPORT_SQL_BASE = """
SELECT
(
SELECT MAX(TO_NUMBER(pv.VALUETEXTTUR))
FROM CONTENTCC_C_PROPERTYVALUE pv
JOIN CONTENTCC_C_TPROPERTYVALUE tpv
ON pv.PROPERTYVALUEID = tpv.PROPERTYVALUEID
WHERE pv.PROPERTYID = 19
AND tpv.TITLEID = tl.TITLEID
) AS HEDEFKODU,
(pl.NAME || ',' || adr.DISTRICTNAME || ',' || adr.CITYNAME) AS MUSTERI,
ppb.DATECREATED AS TARIH,
srv.TIMESTARTS AS SERVIS,
ppb.DATEMODIFIED,
(
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) AS FATURA,
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN 'Kart Okutuldu'
WHEN 0 THEN 'Kart Okutulmadı'
ELSE NULL
END AS DURUM,
ppb.BARCODE AS BARKOD,
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN 'Sevk Bekliyor'
WHEN 1 THEN 'Sevk Kontrolü Yapıldı'
WHEN 2 THEN 'Sevk Başladı'
WHEN 3 THEN 'Transfer Edildi'
WHEN 4 THEN 'Teslim Edildi'
WHEN 5 THEN 'Teslim Edilemedi'
WHEN 99 THEN 'İptal Edildi'
ELSE NULL
END AS SHIPPINGSTATUS_TEXT,
CASE srv.ISEXPRESSSERVICE
WHEN 1 THEN 'Hızlı Teslimat'
WHEN 0 THEN 'Servis'
ELSE NULL
END AS SERVICETYPE_TEXT,
reg.NAMETUR AS PACKAGEREGIONNAMETUR,
srv.NAMETUR AS SERVICENAMETUR,
wh.NAMETUR AS PACKAGEWAREHOUSENAMETUR,
dep.NAMETUR AS DEPARTUREMANAGERNAMETUR,
arr.NAMETUR AS ARRIVALMANAGERNAMETUR,
drv.NAME AS SOFOR,
veh.PLATE AS ARAC,
(
SELECT DeliveredPoint
FROM (
SELECT sp.DeliveredPoint,
ROW_NUMBER() OVER (ORDER BY sp.DeliveredTime DESC) rn
FROM Transportation_ServicePackage sp
WHERE sp.PackagePackBarcodeID = ppb.PackagePackBarcodeID
AND sp.IsDelivered = 1
)
WHERE rn = 1
) AS TESLIM_LOK
FROM TRS_PACKAGEPACKBARCODE ppb
JOIN TRANSPORTATION_PACKAGE pkg
ON pkg.PACKAGEID = ppb.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION reg
ON reg.REGIONID = pkg.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE srv
ON srv.SERVICEID = ppb.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE veh
ON veh.VEHICLEID = srv.VEHICLEID
LEFT JOIN COLLABORATION_NODE wh
ON wh.NODEID = pkg.WAREHOUSEID
LEFT JOIN COLLABORATION_NODE dep
ON dep.NODEID = ppb.DEPARTUREMANAGERID
LEFT JOIN COLLABORATION_NODE arr
ON arr.NODEID = ppb.ARRIVALMANAGERID
LEFT JOIN COLLABORATION_EMPLOYEE drv
ON drv.EMPLOYEEID = srv.DRIVERID
LEFT JOIN TRADING_LINK pl
ON pl.LINKID = pkg.LINKID
INNER JOIN TRADING_LINK tl
ON tl.LINKID = pkg.LINKID
LEFT JOIN CONTACTCC_CONTACT c
ON c.CONTACTID = pl.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS adr
ON adr.ADDRESSID = c.DEFAULTADDRESSID
WHERE
pl.NAME NOT LIKE '%All%'
AND reg.NAMETUR IS NOT NULL
AND reg.NAMETUR <> 'NULL'
AND ppb.DATECREATED >= :start_dt
AND ppb.DATECREATED < :end_dt
"""
# Not: ORDER BY + sayfalama bunu dışarıdan ekleyeceğiz
REPORT_ORDER = "ORDER BY TARIH DESC"
@app.get("/", response_class=HTMLResponse)
@app.get("/rapor", response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse("rapor.html", {"request": request})
@app.get("/db-test")
def db_test():
try:
row = fetch_one("SELECT 1 AS ok FROM dual")
return {"ok": row["ok"] if row and "ok" in row else 1}
except Exception as e:
return JSONResponse(status_code=500, content={"ok": False, "error": str(e)})
@app.get("/rapor-data")
def rapor_data(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
# İsteğe bağlı filtreler (UI kullanıyorsa)
barkod: str | None = None,
fatura: str | None = None,
durum: str | None = None,
page: int = 1,
page_size: int = 50,
):
"""
✅ SQL senin sorgun
✅ Tarih filtresi: ppb.DATECREATED (start_dt <= ... < end_dt)
✅ end günü dahil: end + 1 gün
✅ pagination: OFFSET/FETCH
✅ KPI: DURUM'a göre adet/yüzde
"""
try:
if page < 1:
page = 1
if page_size < 1:
page_size = 50
if page_size > 500:
page_size = 500
start_dt = _date_to_dt(start)
end_dt = _date_to_dt(end) + timedelta(days=1) # end günü dahil
# Base SQL'e ekstra filtre ekleyeceğiz (kendi alias'larımız üzerinden)
extra_where = """
AND (:barkod IS NULL OR LOWER(ppb.BARCODE) LIKE LOWER(:barkod_like))
AND (:fatura IS NULL OR EXISTS (
SELECT 1
FROM Finance_InvoicePackage ip2
JOIN Finance_Invoice i2 ON i2.InvoiceID = ip2.InvoiceID
WHERE ip2.TradingPackageID = ppb.PackageID
AND i2.Code LIKE :fatura_like
))
AND (:durum IS NULL OR (
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN 'Kart Okutuldu'
WHEN 0 THEN 'Kart Okutulmadı'
ELSE NULL
END
) = :durum)
"""
params = {
"start_dt": start_dt,
"end_dt": end_dt,
"barkod": barkod,
"barkod_like": f"%{barkod}%" if barkod else None,
"fatura": fatura,
"fatura_like": f"%{fatura}%" if fatura else None,
"durum": durum,
"offset": (page - 1) * page_size,
"limit": page_size,
}
# COUNT (aynı join/where ile)
count_sql = f"""
SELECT COUNT(1) AS total
FROM (
{REPORT_SQL_BASE}
{extra_where}
) X
"""
total_row = fetch_one(count_sql, params)
total = int(total_row["total"]) if total_row and total_row.get("total") is not None else 0
# DATA (pagination)
data_sql = f"""
SELECT *
FROM (
{REPORT_SQL_BASE}
{extra_where}
) X
{REPORT_ORDER}
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
"""
items = fetch_all(data_sql, params)
# KPI: DURUM'a göre (X.DURUM)
kpi_sql = f"""
SELECT X.DURUM AS durum, COUNT(1) AS adet
FROM (
{REPORT_SQL_BASE}
{extra_where}
) X
GROUP BY X.DURUM
ORDER BY COUNT(1) DESC
"""
kpi_rows = fetch_all(kpi_sql, params)
kpi = []
for r in kpi_rows:
adet = int(r.get("adet") or 0)
yuzde = round((adet / total) * 100, 2) if total else 0
kpi.append({"durum": r.get("durum"), "adet": adet, "yuzde": yuzde})
return {
"range": {"start": str(start), "end": str(end)},
"page": page,
"page_size": page_size,
"total": total,
"kpi": kpi,
"items": items,
}
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
@app.get("/export.csv")
def export_csv(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barkod: str | None = None,
fatura: str | None = None,
durum: str | None = None,
):
try:
start_dt = _date_to_dt(start)
end_dt = _date_to_dt(end) + timedelta(days=1)
extra_where = """
AND (:barkod IS NULL OR LOWER(ppb.BARCODE) LIKE LOWER(:barkod_like))
AND (:fatura IS NULL OR EXISTS (
SELECT 1
FROM Finance_InvoicePackage ip2
JOIN Finance_Invoice i2 ON i2.InvoiceID = ip2.InvoiceID
WHERE ip2.TradingPackageID = ppb.PackageID
AND i2.Code LIKE :fatura_like
))
AND (:durum IS NULL OR (
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN 'Kart Okutuldu'
WHEN 0 THEN 'Kart Okutulmadı'
ELSE NULL
END
) = :durum)
"""
params = {
"start_dt": start_dt,
"end_dt": end_dt,
"barkod": barkod,
"barkod_like": f"%{barkod}%" if barkod else None,
"fatura": fatura,
"fatura_like": f"%{fatura}%" if fatura else None,
"durum": durum,
}
sql = f"""
SELECT *
FROM (
{REPORT_SQL_BASE}
{extra_where}
) X
{REPORT_ORDER}
"""
rows = fetch_all(sql, params)
output = io.StringIO()
writer = csv.writer(output)
# CSV kolonları (bizim alias’larımız)
headers = [
"HEDEFKODU",
"MUSTERI",
"TARIH",
"SERVIS",
"DATEMODIFIED",
"FATURA",
"DURUM",
"BARKOD",
"SHIPPINGSTATUS_TEXT",
"SERVICETYPE_TEXT",
"PACKAGEREGIONNAMETUR",
"SERVICENAMETUR",
"PACKAGEWAREHOUSENAMETUR",
"DEPARTUREMANAGERNAMETUR",
"ARRIVALMANAGERNAMETUR",
"SOFOR",
"ARAC",
"TESLIM_LOK",
]
writer.writerow(headers)
for r in rows:
writer.writerow([r.get(h) for h in headers])
output.seek(0)
filename = f"rapor_{start}_{end}.csv"
return StreamingResponse(
iter([output.getvalue()]),
media_type="text/csv; charset=utf-8",
headers={"Content-Disposition": f'attachment; filename="{filename}"'},
)
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
# app/db.py
import os
import oracledb
from dotenv import load_dotenv
# .env'yi her ihtimale karşı burada da yükleyelim
load_dotenv()
_client_inited = False
def _init_oracle_client_if_needed():
"""
Instant Client varsa thick mode'a geçer.
Yoksa thin mode ile devam eder (oracledb bunu destekler).
"""
global _client_inited
if _client_inited:
return
lib_dir = os.getenv("ORACLE_CLIENT_LIB_DIR")
if lib_dir and os.path.isdir(lib_dir):
try:
oracledb.init_oracle_client(lib_dir=lib_dir)
except Exception:
# zaten init edilmiş olabilir veya thin'de kalabilir; problem etmiyoruz
pass
_client_inited = True
def _get_env(name: str, default: str | None = None) -> str | None:
val = os.getenv(name, default)
if val is None:
return None
val = str(val).strip()
return val if val != "" else None
def get_connection():
"""
.env'den Oracle bağlantısı oluşturur.
ORACLE_USER, ORACLE_PASSWORD, ORACLE_HOST, ORACLE_PORT, ORACLE_SERVICE zorunlu.
"""
_init_oracle_client_if_needed()
user = _get_env("ORACLE_USER")
password = _get_env("ORACLE_PASSWORD")
host = _get_env("ORACLE_HOST")
port = _get_env("ORACLE_PORT", "1521")
service = _get_env("ORACLE_SERVICE")
missing = [k for k, v in {
"ORACLE_USER": user,
"ORACLE_PASSWORD": password,
"ORACLE_HOST": host,
"ORACLE_SERVICE": service,
}.items() if not v]
if missing:
raise RuntimeError(f"ENV eksik: {', '.join(missing)}")
# Host kontrolü (senin ekranında 10.34.11.310 var; bu geçersiz)
# Burada sadece çok bariz hataları yakalayıp daha anlaşılır mesaj veriyoruz.
if host and host.count(".") == 3:
parts = host.split(".")
try:
nums = [int(p) for p in parts]
if any(n < 0 or n > 255 for n in nums):
raise ValueError
except Exception:
raise RuntimeError(f"ORACLE_HOST geçersiz görünüyor: {host}")
dsn = f"{host}:{port}/{service}"
# timeout'lar (donmayı azaltır)
# Not: oracledb sürümüne göre param isimleri değişebilir,
# o yüzden minimal ve güvenli tutuyoruz.
conn = oracledb.connect(user=user, password=password, dsn=dsn)
return conn
def _row_to_dict(cursor, row):
if row is None:
return None
cols = [d[0].lower() for d in cursor.description]
return dict(zip(cols, row))
def fetch_one(sql: str, params: dict | None = None):
"""
Tek satır döndürür (dict). Yoksa None.
"""
conn = get_connection()
try:
with conn.cursor() as cur:
cur.execute(sql, params or {})
row = cur.fetchone()
return _row_to_dict(cur, row)
finally:
try:
conn.close()
except Exception:
pass
def fetch_all(sql: str, params: dict | None = None):
"""
Liste döndürür (list[dict]).
"""
conn = get_connection()
try:
with conn.cursor() as cur:
cur.execute(sql, params or {})
rows = cur.fetchall()
cols = [d[0].lower() for d in cur.description]
return [dict(zip(cols, r)) for r in rows]
finally:
try:
conn.close()
except Exception:
pass
# app/main.py
import csv
import io
import os
from datetime import date, datetime, timedelta
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from app.db import fetch_all, fetch_one
# .env kök dizinde (Panel-rapor/.env)
load_dotenv()
app = FastAPI(title="Panel Rapor", version="1.0.0")
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, "templates")
templates = Jinja2Templates(directory=TEMPLATES_DIR)
# ------------------------------------------------------------
# ✅ SQL - Burayı kendi gerçek tablo/view adınla düzenle
# ------------------------------------------------------------
REPORT_FROM = "FROM YOUR_VIEW_OR_TABLE tpf"
REPORT_SELECT = """
SELECT
tpf.tarih AS tarih,
tpf.hedefkodu AS hedefkodu,
tpf.musteri AS musteri,
tpf.barkod AS barkod,
tpf.durum AS durum,
tpf.servis AS servis,
tpf.fatura AS fatura,
tpf.sofor AS sofor,
tpf.arac AS arac,
tpf.teslim_lok AS teslim_lok
"""
# ✅ ORA-01745 fix: bind isimleri düzgün (harf/underscore)
REPORT_WHERE = """
WHERE tpf.tarih >= :start_dt
AND tpf.tarih < :end_dt
AND (:barkod IS NULL OR LOWER(tpf.barkod) LIKE LOWER(:barkod_like))
AND (:fatura IS NULL OR TO_CHAR(tpf.fatura) LIKE :fatura_like)
AND (:durum IS NULL OR tpf.durum = :durum)
"""
REPORT_ORDER = "ORDER BY tpf.tarih DESC"
def _date_to_dt(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0)
@app.get("/", response_class=HTMLResponse)
@app.get("/rapor", response_class=HTMLResponse)
def rapor_page(request: Request):
# templates/rapor.html
return templates.TemplateResponse("rapor.html", {"request": request})
# ✅ Sen /db-test açıyorsun diye alias bıraktım
@app.get("/db-test")
@app.get("/api/db-test")
def db_test():
try:
row = fetch_one("SELECT 1 AS ok FROM dual")
return {"ok": row["ok"] if row and "ok" in row else 1}
except Exception as e:
# JSON dönelim ki frontend "Unexpected token I" yemesin
return JSONResponse(status_code=500, content={"ok": False, "error": str(e)})
# ✅ API klasörü yok: endpoint'ler direkt kökte
@app.get("/rapor-data")
def rapor_data(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barkod: str | None = None,
fatura: str | None = None,
durum: str | None = None,
page: int = 1,
page_size: int = 50,
):
"""
- start/end default var (Field required yok)
- pagination: OFFSET/FETCH
- KPI: duruma göre adet/yüzde
"""
try:
if page < 1:
page = 1
if page_size < 1:
page_size = 50
if page_size > 500:
page_size = 500
start_dt = _date_to_dt(start)
# end gününü kapsamak için +1 gün ve < end_dt
end_dt = _date_to_dt(end) + timedelta(days=1)
params = {
"start_dt": start_dt,
"end_dt": end_dt,
"barkod": barkod,
"barkod_like": f"%{barkod}%" if barkod else None,
"fatura": fatura,
"fatura_like": f"%{fatura}%" if fatura else None,
"durum": durum,
"offset": (page - 1) * page_size,
"limit": page_size,
}
# total
count_sql = f"""
SELECT COUNT(1) AS total
{REPORT_FROM}
{REPORT_WHERE}
"""
total_row = fetch_one(count_sql, params)
total = int(total_row["total"]) if total_row and total_row.get("total") is not None else 0
# data
data_sql = f"""
{REPORT_SELECT}
{REPORT_FROM}
{REPORT_WHERE}
{REPORT_ORDER}
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
"""
items = fetch_all(data_sql, params)
# KPI
kpi_sql = f"""
SELECT tpf.durum AS durum, COUNT(1) AS adet
{REPORT_FROM}
{REPORT_WHERE}
GROUP BY tpf.durum
ORDER BY COUNT(1) DESC
"""
kpi_rows = fetch_all(kpi_sql, params)
kpi = []
for r in kpi_rows:
adet = int(r.get("adet") or 0)
yuzde = round((adet / total) * 100, 2) if total else 0
kpi.append({"durum": r.get("durum"), "adet": adet, "yuzde": yuzde})
return {
"range": {"start": str(start), "end": str(end)},
"page": page,
"page_size": page_size,
"total": total,
"kpi": kpi,
"items": items,
}
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
@app.get("/export.csv")
def export_csv(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barkod: str | None = None,
fatura: str | None = None,
durum: str | None = None,
):
"""
CSV export (Excel açar)
"""
try:
start_dt = _date_to_dt(start)
end_dt = _date_to_dt(end) + timedelta(days=1)
params = {
"start_dt": start_dt,
"end_dt": end_dt,
"barkod": barkod,
"barkod_like": f"%{barkod}%" if barkod else None,
"fatura": fatura,
"fatura_like": f"%{fatura}%" if fatura else None,
"durum": durum,
}
sql = f"""
{REPORT_SELECT}
{REPORT_FROM}
{REPORT_WHERE}
{REPORT_ORDER}
"""
rows = fetch_all(sql, params)
output = io.StringIO()
writer = csv.writer(output)
headers = ["tarih", "hedefkodu", "musteri", "barkod", "durum", "servis", "fatura", "sofor", "arac", "teslim_lok"]
writer.writerow(headers)
for r in rows:
writer.writerow([r.get(h) for h in headers])
output.seek(0)
filename = f"rapor_{start}_{end}.csv"
return StreamingResponse(
iter([output.getvalue()]),
media_type="text/csv; charset=utf-8",
headers={"Content-Disposition": f'attachment; filename="{filename}"'},
)
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
----------
$subnet = "192.125.1" # <-- SENİN BLOK
$start = 1
$end = 254
$timeoutMs = 600
function Test-TcpPort {
param([string]$IP, [int]$Port, [int]$TimeoutMs = 600)
try {
$client = New-Object System.Net.Sockets.TcpClient
$iar = $client.BeginConnect($IP, $Port, $null, $null)
$ok = $iar.AsyncWaitHandle.WaitOne($TimeoutMs, $false)
if (-not $ok) { $client.Close(); return $false }
$client.EndConnect($iar) | Out-Null
$client.Close()
return $true
} catch { return $false }
}
$found = @()
for ($i=$start; $i -le $end; $i++) {
$ip = "$subnet.$i"
$p9100 = Test-TcpPort -IP $ip -Port 9100 -TimeoutMs $timeoutMs
$p515 = Test-TcpPort -IP $ip -Port 515 -TimeoutMs $timeoutMs
$p631 = Test-TcpPort -IP $ip -Port 631 -TimeoutMs $timeoutMs
if ($p9100 -or $p515 -or $p631) {
$ports = @()
if ($p9100) { $ports += "9100" }
if ($p515) { $ports += "515" }
if ($p631) { $ports += "631" }
$found += [PSCustomObject]@{
IP = $ip
Ports = ($ports -join ",")
}
}
}
# ALT ALTA IP'LER
$found | Sort-Object IP | ForEach-Object { "{0} (Ports: {1})" -f $_.IP, $_.Ports }
"`nToplam bulunan cihaz: $($found.Count)"
$subnet="192.125.1"
$community="public" # sizde farklı olabilir
1..254 | ForEach-Object {
$ip="$subnet.$_"
if (Test-NetConnection $ip -Port 161 -InformationLevel Quiet) {
$sysDescr = (& snmpget -v 2c -c $community $ip 1.3.6.1.2.1.1.1.0 2>$null) -join " "
if ($sysDescr) {
"{0} -> {1}" -f $ip, $sysDescr
}
}
}
python -m venv .venv
.\.venv\Scripts\activate
Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned
pip install -r requirements.txt
rmdir .venv -Recurse -Force
py -3.11 -m venv .venv
python -m venv .venv
.\.venv\Scripts\Activate.ps1
.\.venv\Scripts\Activate.ps1
pip install -r requirements.txt
cd C:\Projeler\Rapor-paneli
rmdir .venv -Recurse -Force
"C:\Users\servetcelik\AppData\Local\Programs\Python\Python311\python.exe" -m venv .venv
.\.venv\Scripts\Activate.ps1
"C:\Users\servetcelik\AppData\Local\Programs\Python\Python311\python.exe" -m venv .venv
$py="C:\Users\servetcelik\AppData\Local\Programs\Python\Python311\python.exe"
& $py -m venv .venv
py -3.11 -m venv .venv
python -m pip install -r requirements.txt
python -m pip install
.\.venv\Scripts\python.exe -m pip install -r requirements.txt
cd C:\Projeler\Rapor-paneli
.\.venv\Scripts\python.exe -m pip install -r requirements.txt
.\.venv\Scripts\python.exe -m ensurepip --upgrade
.\.venv\Scripts\python.exe -m pip install --upgrade pip
.\.venv\Scripts\python.exe -m pip install -r requirements.txt
.\.venv\Scripts\python.exe --version
from fastapi import FastAPI
from fastapi.responses import HTMLResponse
app = FastAPI(title="Oracle Rapor Paneli")
@app.get("/")
def home():
return {"status": "OK", "message": "Rapor paneli çalışıyor"}
@app.get("/health")
def health():
return {"health": "UP"}
(.venv) PS C:\Projeler\Rapor-paneli>
.\.venv\Scripts\python.exe -m uvicorn app.main:app --reload
http://127.0.0.1:8000/docs
uvicorn app.main:app --reload
http://127.0.0.1:8000/
import oracledb
# THIN MODE (ek client gerekmez)
oracledb.init_oracle_client = None
def get_connection():
connection = oracledb.connect(
user="REPORT_USER",
password="PASSWORD",
host="IP_ADRESI",
port=1521,
service_name="ORCL"
)
return connection
from app.db import get_connection
@app.get("/db-test")
def db_test():
try:
conn = get_connection()
conn.close()
return {"db": "Oracle bağlantısı başarılı"}
except Exception as e:
return {"error": str(e)}
from fastapi import FastAPI
from app.db import get_connection
app = FastAPI(title="Oracle Rapor Paneli")
@app.get("/")
def home():
return {
"status": "OK",
"message": "Rapor paneli çalışıyor"
}
@app.get("/health")
def health():
return {
"health": "UP"
}
@app.get("/db-test")
def db_test():
try:
conn = get_connection()
conn.close()
return {
"db": "Oracle bağlantısı başarılı"
}
except Exception as e:
return {
"error": str(e)
}
from fastapi import FastAPI
import oracledb
# 🔴 EN ÜSTE EKLE (ÇOK ÖNEMLİ)
oracledb.init_oracle_client(lib_dir=r"C:\oracle\instantclient")
app = FastAPI(title="Oracle Rapor Paneli")
@app.get("/")
def home():
return {"status": "OK", "message": "Rapor paneli çalışıyor"}
@app.get("/health")
def health():
return {"health": "UP"}
def get_connection():
return oracledb.connect(
user="REPORT_USER",
password="PASSWORD",
host="IP_ADRESI",
port=1521,
service_name="ORCL"
)
@app.get("/db-test")
def db_test():
try:
conn = get_connection()
conn.close()
return {"db": "Oracle bağlantısı başarılı (THICK MODE)"}
except Exception as e:
return {"error": str(e)}
cd C:\Projeler\Rapor-paneli
# venv aktif et
.\.venv\Scripts\Activate.ps1
# (opsiyonel) pip güncelle
python -m pip install --upgrade pip
# server başlat
python -m uvicorn app.main:app --reload --host 127.0.0.1 --port 8000
cd C:\Projeler\Rapor-paneli
.\.venv\Scripts\python.exe -m uvicorn app.main:app --reload --host 127.0.0.1 --port 8000
.\.venv\Scripts\Activate.ps1
python -c "import sys; print(sys.executable)"
import os
from pathlib import Path
import oracledb
def load_env_force(env_file: Path) -> None:
"""
.env dosyasını KENDİMİZ okuruz ve os.environ içine yazarız.
python-dotenv'e bağlı kalmayız -> en garanti yöntem.
"""
if not env_file.exists():
raise RuntimeError(f".env bulunamadı: {env_file}")
for raw in env_file.read_text(encoding="utf-8").splitlines():
line = raw.strip()
if not line or line.startswith("#"):
continue
if "=" not in line:
continue
k, v = line.split("=", 1)
k = k.strip()
v = v.strip().strip('"').strip("'")
os.environ[k] = v # ZORLA ENV'e bas
def get_oracle_conn():
# Proje root: ...\Panel-rapor
base_dir = Path(__file__).resolve().parent.parent
env_path = base_dir / ".env"
# ENV'i kesin yükle
load_env_force(env_path)
user = os.environ.get("ORACLE_USER")
password = os.environ.get("ORACLE_PASSWORD")
host = os.environ.get("ORACLE_HOST")
port = os.environ.get("ORACLE_PORT", "1521")
service = os.environ.get("ORACLE_SERVICE")
missing = [k for k in ["ORACLE_USER","ORACLE_PASSWORD","ORACLE_HOST","ORACLE_SERVICE"]
if not os.environ.get(k)]
if missing:
raise RuntimeError(f"ENV eksik: {', '.join(missing)} (okunan dosya: {env_path})")
# Instant Client (thick mode) - varsa yükle
client_dir = os.environ.get("ORACLE_CLIENT_LIB_DIR")
if client_dir and os.path.isdir(client_dir):
try:
oracledb.init_oracle_client(lib_dir=client_dir)
except Exception:
# init_oracle_client iki kere çağrılırsa hata verebilir, sorun değil
pass
dsn = f"{host}:{port}/{service}"
# Bağlantı
conn = oracledb.connect(user=user, password=password, dsn=dsn)
return conn
from fastapi import FastAPI
from app.db import get_oracle_conn
import os
from pathlib import Path
app = FastAPI()
@app.get("/db-test")
def db_test():
# env dosyasını gerçekten görüyor mu? kanıt için gösteriyoruz
base_dir = Path(__file__).resolve().parent.parent
env_path = base_dir / ".env"
try:
conn = get_oracle_conn()
conn.close()
return {
"status": "OK",
"env_path": str(env_path),
"oracle_user": os.environ.get("ORACLE_USER"),
"oracle_host": os.environ.get("ORACLE_HOST"),
"oracle_service": os.environ.get("ORACLE_SERVICE"),
"message": "Oracle bağlantısı başarılı"
}
except Exception as e:
return {
"status": "FAIL",
"env_path": str(env_path),
"oracle_user": os.environ.get("ORACLE_USER"),
"oracle_host": os.environ.get("ORACLE_HOST"),
"oracle_service": os.environ.get("ORACLE_SERVICE"),
"error": str(e)
}
cd C:\Projeler\Panel-rapor
.\.venv\Scripts\activate
python -m uvicorn app.main:app --host 127.0.0.1 --port 8000
reports.py
from fastapi import APIRouter, Request, Query
from fastapi.responses import HTMLResponse, JSONResponse
from fastapi.templating import Jinja2Templates
from app.db import get_connection
router = APIRouter()
templates = Jinja2Templates(directory="templates")
@router.get("/rapor", response_class=HTMLResponse)
def rapor_page(request: Request):
# sayfa ilk açılış
return templates.TemplateResponse(
"rapor.html",
{"request": request, "rows": [], "error": None}
)
@router.get("/rapor-data")
def rapor_data(
baslangic: str = Query(None, description="YYYY-MM-DD"),
bitis: str = Query(None, description="YYYY-MM-DD"),
):
"""
JSON veri döner (frontend fetch ile çeker).
İstersen tarih filtrelerini SQL'e entegre edeceğiz.
"""
try:
conn = get_connection()
cur = conn.cursor()
# ÖRNEK SQL (bunu senin tablo/kolonlara göre düzenleyeceğiz)
sql = """
SELECT
SYSDATE AS TARIH,
USER AS KULLANICI
FROM dual
"""
cur.execute(sql)
cols = [d[0] for d in cur.description]
rows = [dict(zip(cols, r)) for r in cur.fetchall()]
cur.close()
conn.close()
return {"ok": True, "columns": cols, "rows": rows}
except Exception as e:
return JSONResponse(status_code=500, content={"ok": False, "error": str(e)})
from fastapi import FastAPI, Request, Query
from fastapi.responses import HTMLResponse, JSONResponse
from fastapi.templating import Jinja2Templates
# db bağlantın burada olmalı:
# C:\Projeler\Panel-rapor\app\db.py içinde get_connection() fonksiyonu
from app.db import get_connection
app = FastAPI()
templates = Jinja2Templates(directory="templates")
@app.get("/", response_class=HTMLResponse)
def home(request: Request):
# direkt rapor sayfasına yönlendirme gibi düşünebilirsin
return templates.TemplateResponse("rapor.html", {"request": request})
@app.get("/rapor", response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse("rapor.html", {"request": request})
@app.get("/rapor-data")
def rapor_data(
baslangic: str | None = Query(default=None, description="YYYY-MM-DD"),
bitis: str | None = Query(default=None, description="YYYY-MM-DD"),
):
"""
JSON veri döner. Şu an test için DUAL'dan veri çekiyor.
Daha sonra buraya gerçek rapor SQL'ini koyacağız.
"""
try:
conn = get_connection()
cur = conn.cursor()
# TEST SQL (çalıştığını kanıtlar)
sql = """
SELECT
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS TARIH,
USER AS KULLANICI
FROM dual
"""
cur.execute(sql)
cols = [d[0] for d in cur.description]
rows = [dict(zip(cols, r)) for r in cur.fetchall()]
cur.close()
conn.close()
return {"ok": True, "columns": cols, "rows": rows}
except Exception as e:
return JSONResponse(status_code=500, content={"ok": False, "error": str(e)})
# İstersen DB test endpoint'i de kalsın:
@app.get("/db-test")
def db_test():
try:
conn = get_connection()
cur = conn.cursor()
cur.execute("SELECT 1 FROM dual")
val = cur.fetchone()[0]
cur.close()
conn.close()
return {"ok": True, "db": "connected", "val": val}
except Exception as e:
return {"ok": False, "error": str(e)}
Rapor
Rapor Sayfası
sfd-----------------
import os
import oracledb
from dotenv import load_dotenv
def _init_oracle_client_if_needed():
"""
Instant Client varsa thick mode'a geçer.
Yoksa thin mode ile devam eder.
"""
lib_dir = os.getenv("ORACLE_CLIENT_LIB_DIR")
# env'de varsa onu dene
if lib_dir and os.path.isdir(lib_dir):
try:
oracledb.init_oracle_client(lib_dir=lib_dir)
return
except Exception:
pass
# yoksa yaygın dizinleri dene (sende var: C:\oracle\instantclient)
for p in [
r"C:\oracle\instantclient",
r"C:\oracle\instantclient_21_20",
r"C:\oracle\instantclient_21_11",
r"C:\oracle\instantclient_19_20",
]:
if os.path.isdir(p):
try:
oracledb.init_oracle_client(lib_dir=p)
return
except Exception:
pass
def get_connection():
load_dotenv() # .env oku
user = os.getenv("ORACLE_USER")
password = os.getenv("ORACLE_PASSWORD")
host = os.getenv("ORACLE_HOST")
port = int(os.getenv("ORACLE_PORT", "1521"))
service = os.getenv("ORACLE_SERVICE")
if not all([user, password, host, service]):
missing = []
if not user: missing.append("ORACLE_USER")
if not password: missing.append("ORACLE_PASSWORD")
if not host: missing.append("ORACLE_HOST")
if not service: missing.append("ORACLE_SERVICE")
raise RuntimeError("ENV eksik: " + ", ".join(missing))
_init_oracle_client_if_needed()
dsn = f"{host}:{port}/{service}"
return oracledb.connect(user=user, password=password, dsn=dsn)
from fastapi import FastAPI, Request
from fastapi.responses import HTMLResponse, JSONResponse
from fastapi.templating import Jinja2Templates
from app.db import get_connection
app = FastAPI()
templates = Jinja2Templates(directory="templates")
@app.get("/", response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse("rapor.html", {"request": request})
@app.get("/db-test")
def db_test():
try:
conn = get_connection()
cur = conn.cursor()
cur.execute("SELECT 1 AS OK FROM dual")
val = cur.fetchone()[0]
cur.close()
conn.close()
return {"ok": True, "val": val}
except Exception as e:
return JSONResponse(status_code=500, content={"ok": False, "error": str(e)})
@app.get("/rapor-data")
def rapor_data():
try:
conn = get_connection()
cur = conn.cursor()
# Şimdilik test veri:
cur.execute("""
SELECT
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS TARIH,
USER AS KULLANICI
FROM dual
""")
cols = [d[0] for d in cur.description]
rows = [dict(zip(cols, r)) for r in cur.fetchall()]
cur.close()
conn.close()
return {"ok": True, "columns": cols, "rows": rows}
except Exception as e:
return JSONResponse(status_code=500, content={"ok": False, "error": str(e)})
from fastapi import FastAPI, Request
from fastapi.responses import HTMLResponse, JSONResponse
from fastapi.templating import Jinja2Templates
from app.db import get_connection
app = FastAPI()
templates = Jinja2Templates(directory="templates")
@app.get("/", response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse("rapor.html", {"request": request})
@app.get("/db-test")
def db_test():
try:
conn = get_connection()
cur = conn.cursor()
cur.execute("SELECT 1 AS OK FROM dual")
val = cur.fetchone()[0]
cur.close()
conn.close()
return {"ok": True, "val": val}
except Exception as e:
return JSONResponse(status_code=500, content={"ok": False, "error": str(e)})
@app.get("/rapor-data")
def rapor_data():
try:
conn = get_connection()
cur = conn.cursor()
# Şimdilik test veri:
cur.execute("""
SELECT
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS TARIH,
USER AS KULLANICI
FROM dual
""")
cols = [d[0] for d in cur.description]
rows = [dict(zip(cols, r)) for r in cur.fetchall()]
cur.close()
conn.close()
return {"ok": True, "columns": cols, "rows": rows}
except Exception as e:
return JSONResponse(status_code=500, content={"ok": False, "error": str(e)})
Rapor Dashboard
Toplam Kayıt
-
Tarih Aralığı
-
Son Güncelleme
-
* Excel export’u bir sonraki adımda aktif edeceğiz.
Sonuçlar
Hazır
TARİH
Filtre seçip “Raporu Getir” tıklayın.
from fastapi import FastAPI, Query
from fastapi.responses import HTMLResponse
from datetime import datetime
from app.db import get_connection
app = FastAPI(title="Panel Rapor API")
@app.get("/")
def root():
return {"status": "ok"}
@app.get("/db-test")
def db_test():
try:
conn = get_connection()
cur = conn.cursor()
cur.execute("SELECT 1 FROM dual")
val = cur.fetchone()[0]
cur.close()
conn.close()
return {"ok": True, "val": val}
except Exception as e:
return {"error": str(e)}
@app.get("/rapor", response_class=HTMLResponse)
def rapor_page():
with open("templates/rapor.html", "r", encoding="utf-8") as f:
return f.read()
@app.get("/api/rapor")
def rapor_api(
start: str = Query(..., description="YYYY-MM-DD"),
end: str = Query(..., description="YYYY-MM-DD"),
):
"""
Dashboard'ın veri kaynağı.
start/end geliyor, burada Oracle sorgusu çalıştırıp tablo döndürüyoruz.
"""
# Basit tarih validasyonu
try:
start_dt = datetime.strptime(start, "%Y-%m-%d")
end_dt = datetime.strptime(end, "%Y-%m-%d")
if end_dt < start_dt:
return {"columns": ["HATA"], "rows": [["Bitiş tarihi başlangıçtan küçük olamaz."]]}
except Exception:
return {"columns": ["HATA"], "rows": [["Tarih formatı yanlış. Örn: 2026-01-13"]]}
# ŞİMDİLİK TEST SORGUSU (senin sorgunu az sonra buraya koyacağız)
sql = "SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS TARIH FROM dual"
conn = None
cur = None
try:
conn = get_connection()
cur = conn.cursor()
cur.execute(sql)
columns = [d[0] for d in cur.description] # kolon adları
rows = cur.fetchall()
return {"columns": columns, "rows": rows}
except Exception as e:
return {"columns": ["HATA"], "rows": [[str(e)]]}
finally:
try:
if cur: cur.close()
if conn: conn.close()
except Exception:
pass
msql = msql & " SELECT (SELECT DISTINCT CONTENTCC_C_PROPERTYVALUE.VALUETEXTTUR *1 FROM CONTENTCC_C_PROPERTYVALUE, CONTENTCC_C_TPROPERTYVALUE WHERE CONTENTCC_C_PROPERTYVALUE.PROPERTYVALUEID = CONTENTCC_C_TPROPERTYVALUE.PROPERTYVALUEID AND PROPERTYID=19 AND TL.TITLEID=CONTENTCC_C_TPROPERTYVALUE.TITLEID) AS HEDEFKODU,PACKAGELINK.NAME|| ',' ||PACKAGELINKCONTACTDEFAULTADDRESS.DISTRICTNAME|| ',' ||PACKAGELINKCONTACTDEFAULTADDRESS.CITYNAME AS musterı,TRS_PACKAGEPACKBARCODE.DATECREATED ,SERVICE.TIMESTARTS AS SERVICETIMESTARTS,TRS_PACKAGEPACKBARCODE.DATEMODIFIED,(SELECT GT.InvoiceCode FROM ( SELECT Invoice.Code InvoiceCode, "
msql = msql & " ROW_NUMBER() OVER(ORDER BY InvoicePackage.InvoiceID DESC) AS NPR FROM Finance_InvoicePackage InvoicePackage INNER JOIN Finance_Invoice Invoice ON Invoice.InvoiceID = InvoicePackage.InvoiceID "
msql = msql & " WHERE InvoicePackage.TradingPackageID = TRS_PACKAGEPACKBARCODE.PackageID ) GT WHERE GT.NPR = 1) AS INVOICECODE,"
'msql = msql & " (SELECT GT.DeliveredTime FROM ( SELECT ServicePackage.DeliveredTime, ROW_NUMBER() OVER(ORDER BY DeliveredTime DESC) NPR "
'msql = msql & " FROM Transportation_ServicePackage ServicePackage WHERE ServicePackage.PackagePackBarcodeID = TRS_PACKAGEPACKBARCODE.PackagePackBarcodeID AND ServicePackage.IsDelivered=1 ) GT WHERE GT.NPR=1 ) AS DELIVEREDTIME,"
msql = msql & " (CASE TRS_PACKAGEPACKBARCODE.DELIVERYVALIDATIONTYPE "
msql = msql & " WHEN 1"
msql = msql & " THEN 'Kart Okutuldu' "
msql = msql & " WHEN 0"
msql = msql & " THEN 'kart Okutulmadı' "
msql = msql & " END)"
msql = msql & " ,TRS_PACKAGEPACKBARCODE.BARCODE,"
msql = msql & " (CASE TRS_PACKAGEPACKBARCODE.SHIPPINGSTATUS "
msql = msql & " WHEN 0"
msql = msql & " THEN 'Sevk Bekliyor' "
msql = msql & " WHEN 1"
msql = msql & " THEN 'Sevk KOntrolü Yapıldı' "
msql = msql & " WHEN 2"
msql = msql & " THEN 'Sevk Başladı' "
msql = msql & " WHEN 3"
msql = msql & " THEN 'Transfer Edildi' "
msql = msql & " WHEN 4"
msql = msql & " THEN 'Teslim Edildi' "
msql = msql & " WHEN 5"
msql = msql & " THEN 'Teslim Edilemedi'"
msql = msql & " WHEN 99"
msql = msql & " THEN 'İptal Edildi' "
msql = msql & " END),"
msql = msql & " (CASE SERVICE.ISEXPRESSSERVICE WHEN 1 THEN 'Hızlı Teslimat' WHEN 0 THEN 'Servis' END),"
msql = msql & " PACKAGEREGION.NAMETUR AS PACKAGEREGIONNAMETUR, SERVICE.NAMETUR AS SERVICENAMETUR, PACKAGEWAREHOUSE.NAMETUR AS PACKAGEWAREHOUSENAMETUR, DEPARTUREMANAGER.NAMETUR AS DEPARTUREMANAGERNAMETUR, ARRIVALMANAGER.NAMETUR AS ARRIVALMANAGERNAMETUR, "
msql = msql & " SERVICEDRIVER.NAME AS SERVICEDRIVERNAME,SERVICEVEHICLE.PLATE AS SERVICEVEHICLEPLATE,"
msql = msql & " (SELECT GT.DeliveredPoint FROM ( SELECT ServicePackage.DeliveredPoint, ROW_NUMBER() OVER(ORDER BY DeliveredTime DESC) NPR "
msql = msql & " FROM Transportation_ServicePackage ServicePackage WHERE ServicePackage.PackagePackBarcodeID = TRS_PACKAGEPACKBARCODE.PackagePackBarcodeID AND ServicePackage.IsDelivered=1 ) GT WHERE GT.NPR=1 ) AS DELIVEREDLOCATION "
msql = msql & " FROM TRS_PACKAGEPACKBARCODE "
msql = msql & " INNER JOIN TRANSPORTATION_PACKAGE PACKAGE ON PACKAGE.PACKAGEID = TRS_PACKAGEPACKBARCODE.PACKAGEID "
msql = msql & " INNER JOIN TRNS_TRANSPORTATIONMANAGER PACKAGETRANSPORTATIONMANAGER ON PACKAGETRANSPORTATIONMANAGER.NODEID = PACKAGE.TRANSPORTATIONMANAGERID "
msql = msql & " LEFT JOIN COLLABORATION_NODE PACKAGETRANSPORTATIONMANAGERNODE ON PACKAGETRANSPORTATIONMANAGERNODE.NODEID = PACKAGE.TRANSPORTATIONMANAGERID "
msql = msql & " LEFT JOIN TRADING_DELIVERYCANCELREASON DELIVERYCANCELREASON ON DELIVERYCANCELREASON.DELIVERYCANCELREASONID = TRS_PACKAGEPACKBARCODE.DELIVERYCANCELREASONID "
msql = msql & " LEFT JOIN TRANSPORTATION_REGION PACKAGEREGION ON PACKAGEREGION.REGIONID = PACKAGE.REGIONID "
msql = msql & " LEFT JOIN TRANSPORTATION_SERVICE SERVICE ON SERVICE.SERVICEID = TRS_PACKAGEPACKBARCODE.SERVICEID "
msql = msql & " LEFT JOIN TRANSPORTATION_ROUTE SERVICEROUTE ON SERVICEROUTE.ROUTEID = SERVICE.ROUTEID "
msql = msql & " LEFT JOIN TRANSPORTATION_VEHICLE SERVICEVEHICLE ON SERVICEVEHICLE.VEHICLEID = SERVICE.VEHICLEID "
msql = msql & " LEFT JOIN COLLABORATION_NODE PACKAGEWAREHOUSE ON PACKAGEWAREHOUSE.NODEID = PACKAGE.WAREHOUSEID "
msql = msql & " LEFT JOIN COLLABORATION_NODE DEPARTUREMANAGER ON DEPARTUREMANAGER.NODEID = TRS_PACKAGEPACKBARCODE.DEPARTUREMANAGERID "
msql = msql & " LEFT JOIN COLLABORATION_NODE ARRIVALMANAGER ON ARRIVALMANAGER.NODEID = TRS_PACKAGEPACKBARCODE.ARRIVALMANAGERID "
msql = msql & " LEFT JOIN TRADING_DELIVERYFAULTHTYPE DELIVERYFAULTHTYPE ON DELIVERYFAULTHTYPE.DELIVERYFAULTHTYPEID = TRS_PACKAGEPACKBARCODE.DELIVERYFAULTHTYPEID "
msql = msql & " LEFT JOIN COLLABORATION_EMPLOYEE SERVICEDRIVER ON SERVICEDRIVER.EMPLOYEEID = SERVICE.DRIVERID "
msql = msql & " LEFT JOIN TRADING_LINK PACKAGELINK ON PACKAGELINK.LINKID = PACKAGE.LINKID "
msql = msql & " INNER JOIN TRADING_LINK TL ON TL.LINKID=package.LINKID"
msql = msql & " LEFT JOIN CONTACTCC_CONTACT PACKAGELINKCONTACT ON PACKAGELINKCONTACT.CONTACTID = PACKAGELINK.CONTACTID "
msql = msql & " LEFT JOIN CONTACTCC_ADDRESS PACKAGELINKCONTACTDEFAULTADDRESS ON PACKAGELINKCONTACTDEFAULTADDRESS.ADDRESSID = PACKAGELINKCONTACT.DEFAULTADDRESSID "
msql = msql & " WHERE TRS_PACKAGEPACKBARCODE.DATECREATED BETWEEN TO_DATE('" & tar1 & " 00:00:00', 'DD.MM.YYYY HH24:MI:SS') AND TO_DATE('" & tar2 & " 23:59:00', 'DD.MM.YYYY HH24:MI:SS') and PACKAGELINK.NAME NOT LIKE '%All%' AND PACKAGEREGION.NAMETUR NOT LIKE 'NULL'"
-------------------------------
import os
import oracledb
from dotenv import load_dotenv
load_dotenv()
def _init_oracle_client():
# Thick mode (Instant Client) varsa kullan
lib_dir = os.getenv("ORACLE_CLIENT_LIB_DIR")
if lib_dir and os.path.isdir(lib_dir):
try:
oracledb.init_oracle_client(lib_dir=lib_dir)
except Exception:
# ikinci kez init edilirse hata verebilir; önemsemiyoruz
pass
_init_oracle_client()
def get_connection():
user = os.getenv("ORACLE_USER")
password = os.getenv("ORACLE_PASSWORD")
host = os.getenv("ORACLE_HOST")
port = os.getenv("ORACLE_PORT", "1521")
service = os.getenv("ORACLE_SERVICE")
missing = [k for k, v in {
"ORACLE_USER": user,
"ORACLE_PASSWORD": password,
"ORACLE_HOST": host,
"ORACLE_SERVICE": service
}.items() if not v]
if missing:
raise RuntimeError(f"ENV eksik: {', '.join(missing)}")
dsn = f"{host}:{port}/{service}"
return oracledb.connect(user=user, password=password, dsn=dsn)
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse
from fastapi.templating import Jinja2Templates
from datetime import datetime, date
from app.db import get_connection
app = FastAPI()
templates = Jinja2Templates(directory="templates")
# ✅ Optimize SQL (bind parametreli)
SQL_RAPOR = r"""
SELECT
pv.HEDEFKODU,
(pl.NAME || ',' || adr.DISTRICTNAME || ',' || adr.CITYNAME) AS MUSTERI,
ppb.DATECREATED,
srv.TIMESTARTS AS SERVICETIMESTARTS,
ppb.DATEMODIFIED,
inv.INVOICECODE,
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN 'Kart Okutuldu'
WHEN 0 THEN 'Kart Okutulmadı'
ELSE NULL
END AS DELIVERYVALIDATIONTYPE_TEXT,
ppb.BARCODE,
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN 'Sevk Bekliyor'
WHEN 1 THEN 'Sevk Kontrolü Yapıldı'
WHEN 2 THEN 'Sevk Başladı'
WHEN 3 THEN 'Transfer Edildi'
WHEN 4 THEN 'Teslim Edildi'
WHEN 5 THEN 'Teslim Edilemedi'
WHEN 99 THEN 'İptal Edildi'
ELSE NULL
END AS SHIPPINGSTATUS_TEXT,
CASE srv.ISEXPRESSSERVICE
WHEN 1 THEN 'Hızlı Teslimat'
WHEN 0 THEN 'Servis'
ELSE NULL
END AS SERVICETYPE_TEXT,
reg.NAMETUR AS PACKAGEREGIONNAMETUR,
srv.NAMETUR AS SERVICENAMETUR,
wh.NAMETUR AS PACKAGEWAREHOUSENAMETUR,
dep.NAMETUR AS DEPARTUREMANAGERNAMETUR,
arr.NAMETUR AS ARRIVALMANAGERNAMETUR,
drv.NAME AS SERVICEDRIVERNAME,
veh.PLATE AS SERVICEVEHICLEPLATE,
spx.DELIVEREDLOCATION
FROM TRS_PACKAGEPACKBARCODE ppb
JOIN TRANSPORTATION_PACKAGE pkg
ON pkg.PACKAGEID = ppb.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION reg
ON reg.REGIONID = pkg.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE srv
ON srv.SERVICEID = ppb.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE veh
ON veh.VEHICLEID = srv.VEHICLEID
LEFT JOIN COLLABORATION_NODE wh
ON wh.NODEID = pkg.WAREHOUSEID
LEFT JOIN COLLABORATION_NODE dep
ON dep.NODEID = ppb.DEPARTUREMANAGERID
LEFT JOIN COLLABORATION_NODE arr
ON arr.NODEID = ppb.ARRIVALMANAGERID
LEFT JOIN COLLABORATION_EMPLOYEE drv
ON drv.EMPLOYEEID = srv.DRIVERID
LEFT JOIN TRADING_LINK pl
ON pl.LINKID = pkg.LINKID
JOIN TRADING_LINK tl
ON tl.LINKID = pkg.LINKID
LEFT JOIN CONTACTCC_CONTACT c
ON c.CONTACTID = pl.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS adr
ON adr.ADDRESSID = c.DEFAULTADDRESSID
LEFT JOIN (
SELECT
tpf.TITLEID,
MAX(TO_NUMBER(pv.VALUETEXTTUR)) AS HEDEFKODU
FROM CONTENTCC_C_TPROPERTYVALUE tpf
JOIN CONTENTCC_C_PROPERTYVALUE pv
ON pv.PROPERTYVALUEID = tpf.PROPERTYVALUEID
WHERE tpf.PROPERTYID = 19
GROUP BY tpf.TITLEID
) pv
ON pv.TITLEID = tl.TITLEID
LEFT JOIN (
SELECT TradingPackageID, InvoiceCode
FROM (
SELECT
ip.TradingPackageID,
i.Code AS InvoiceCode,
ROW_NUMBER() OVER (PARTITION BY ip.TradingPackageID ORDER BY ip.InvoiceID DESC) AS rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i
ON i.InvoiceID = ip.InvoiceID
)
WHERE rn = 1
) inv
ON inv.TradingPackageID = ppb.PackageID
LEFT JOIN (
SELECT PackagePackBarcodeID, DeliveredPoint AS DeliveredLocation
FROM (
SELECT
sp.PackagePackBarcodeID,
sp.DeliveredPoint,
ROW_NUMBER() OVER (PARTITION BY sp.PackagePackBarcodeID ORDER BY sp.DeliveredTime DESC) AS rn
FROM Transportation_ServicePackage sp
WHERE sp.IsDelivered = 1
)
WHERE rn = 1
) spx
ON spx.PackagePackBarcodeID = ppb.PackagePackBarcodeID
WHERE
pl.NAME NOT LIKE '%All%'
AND reg.NAMETUR IS NOT NULL
AND reg.NAMETUR <> 'NULL'
AND ppb.DATECREATED >= :start_date
AND ppb.DATECREATED < :end_date
ORDER BY ppb.DATECREATED DESC
"""
def _parse_date_ymd(s: str) -> date:
# "YYYY-MM-DD"
return datetime.strptime(s, "%Y-%m-%d").date()
@app.get("/db-test")
def db_test():
try:
conn = get_connection()
with conn.cursor() as cur:
cur.execute("SELECT 1 FROM dual")
val = cur.fetchone()[0]
conn.close()
return {"ok": True, "val": val}
except Exception as e:
return JSONResponse({"error": str(e)}, status_code=500)
@app.get("/rapor", response_class=HTMLResponse)
def rapor_page(request: Request):
# varsayılan: bugün
today = date.today().strftime("%Y-%m-%d")
return templates.TemplateResponse(
"rapor.html",
{"request": request, "start": today, "end": today}
)
@app.get("/api/rapor")
def api_rapor(
start: str = Query(..., description="YYYY-MM-DD"),
end: str = Query(..., description="YYYY-MM-DD"),
):
"""
start/end: dashboard'dan gelir (YYYY-MM-DD)
Oracle için: [start_date, end_date+1) aralığı uygulanır.
"""
try:
start_d = _parse_date_ymd(start)
end_d = _parse_date_ymd(end)
# end gününü dahil etmek için +1 gün
start_dt = datetime.combine(start_d, datetime.min.time())
end_dt = datetime.combine(end_d, datetime.min.time()) + timedelta(days=1)
conn = get_connection()
conn.autocommit = True
with conn.cursor() as cur:
cur.execute(SQL_RAPOR, start_date=start_dt, end_date=end_dt)
cols = [c[0].lower() for c in cur.description]
rows = []
for r in cur.fetchall():
obj = {}
for i, v in enumerate(r):
# datetime ise ISO string'e çevir
if isinstance(v, (datetime, date)):
obj[cols[i]] = v.isoformat(sep=" ")
else:
obj[cols[i]] = v
rows.append(obj)
conn.close()
return {"ok": True, "count": len(rows), "rows": rows}
except Exception as e:
return JSONResponse({"ok": False, "error": str(e)}, status_code=500)
# ✅ eksik import düzeltmesi
from datetime import timedelta
Rapor Dashboard
Panel Rapor Dashboard
Raporlar
Hazır
Seçtiğiniz tarih aralığına göre rapor listelenecek.
TARİH
HEDEFKODU
MÜŞTERİ
BARKOD
Durum
Servis
Fatura
Şoför
Araç
Teslim Lok.
Henüz veri yok.
SELECT
(
SELECT MAX(TO_NUMBER(pv.VALUETEXTTUR))
FROM CONTENTCC_C_PROPERTYVALUE pv
JOIN CONTENTCC_C_TPROPERTYVALUE tpv
ON pv.PROPERTYVALUEID = tpv.PROPERTYVALUEID
WHERE pv.PROPERTYID = 19
AND tpv.TITLEID = tl.TITLEID
) AS HEDEFKODU,
(pl.NAME || ',' || adr.DISTRICTNAME || ',' || adr.CITYNAME) AS MUSTERI,
ppb.DATECREATED,
srv.TIMESTARTS AS SERVICETIMESTARTS,
ppb.DATEMODIFIED,
(
SELECT InvoiceCode
FROM (
SELECT i.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY ip.InvoiceID DESC) rn
FROM Finance_InvoicePackage ip
JOIN Finance_Invoice i ON i.InvoiceID = ip.InvoiceID
WHERE ip.TradingPackageID = ppb.PackageID
)
WHERE rn = 1
) AS INVOICECODE,
CASE ppb.DELIVERYVALIDATIONTYPE
WHEN 1 THEN 'Kart Okutuldu'
WHEN 0 THEN 'Kart Okutulmadı'
ELSE NULL
END AS DELIVERYVALIDATIONTYPE_TEXT,
ppb.BARCODE,
CASE ppb.SHIPPINGSTATUS
WHEN 0 THEN 'Sevk Bekliyor'
WHEN 1 THEN 'Sevk Kontrolü Yapıldı'
WHEN 2 THEN 'Sevk Başladı'
WHEN 3 THEN 'Transfer Edildi'
WHEN 4 THEN 'Teslim Edildi'
WHEN 5 THEN 'Teslim Edilemedi'
WHEN 99 THEN 'İptal Edildi'
ELSE NULL
END AS SHIPPINGSTATUS_TEXT,
CASE srv.ISEXPRESSSERVICE
WHEN 1 THEN 'Hızlı Teslimat'
WHEN 0 THEN 'Servis'
ELSE NULL
END AS SERVICETYPE_TEXT,
reg.NAMETUR AS PACKAGEREGIONNAMETUR,
srv.NAMETUR AS SERVICENAMETUR,
wh.NAMETUR AS PACKAGEWAREHOUSENAMETUR,
dep.NAMETUR AS DEPARTUREMANAGERNAMETUR,
arr.NAMETUR AS ARRIVALMANAGERNAMETUR,
drv.NAME AS SERVICEDRIVERNAME,
veh.PLATE AS SERVICEVEHICLEPLATE,
(
SELECT DeliveredPoint
FROM (
SELECT sp.DeliveredPoint,
ROW_NUMBER() OVER (ORDER BY sp.DeliveredTime DESC) rn
FROM Transportation_ServicePackage sp
WHERE sp.PackagePackBarcodeID = ppb.PackagePackBarcodeID
AND sp.IsDelivered = 1
)
WHERE rn = 1
) AS DELIVEREDLOCATION
FROM TRS_PACKAGEPACKBARCODE ppb
JOIN TRANSPORTATION_PACKAGE pkg
ON pkg.PACKAGEID = ppb.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION reg
ON reg.REGIONID = pkg.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE srv
ON srv.SERVICEID = ppb.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE veh
ON veh.VEHICLEID = srv.VEHICLEID
LEFT JOIN COLLABORATION_NODE wh
ON wh.NODEID = pkg.WAREHOUSEID
LEFT JOIN COLLABORATION_NODE dep
ON dep.NODEID = ppb.DEPARTUREMANAGERID
LEFT JOIN COLLABORATION_NODE arr
ON arr.NODEID = ppb.ARRIVALMANAGERID
LEFT JOIN COLLABORATION_EMPLOYEE drv
ON drv.EMPLOYEEID = srv.DRIVERID
LEFT JOIN TRADING_LINK pl
ON pl.LINKID = pkg.LINKID
INNER JOIN TRADING_LINK tl
ON tl.LINKID = pkg.LINKID
LEFT JOIN CONTACTCC_CONTACT c
ON c.CONTACTID = pl.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS adr
ON adr.ADDRESSID = c.DEFAULTADDRESSID
WHERE
pl.NAME NOT LIKE '%All%'
AND reg.NAMETUR IS NOT NULL
AND reg.NAMETUR <> 'NULL'
AND ppb.DATECREATED >= :start_date
AND ppb.DATECREATED < :end_date
ORDER BY ppb.DATECREATED DESC
main
# app/main.py
import os
from datetime import date
from fastapi import FastAPI, Request, Query, HTTPException
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from fastapi.staticfiles import StaticFiles
from app.db import fetch_all # db.py içindeki fonksiyon
app = FastAPI(title="Panel Rapor", version="1.0.0")
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, "templates")
STATIC_DIR = os.path.join(BASE_DIR, "static")
templates = Jinja2Templates(directory=TEMPLATES_DIR)
# static klasörün varsa aç (yoksa bu satırı silebilirsin)
if os.path.isdir(STATIC_DIR):
app.mount("/static", StaticFiles(directory=STATIC_DIR), name="static")
@app.get("/", include_in_schema=False)
def root():
return {"ok": True, "service": "panel-rapor"}
@app.get("/rapor", response_class=HTMLResponse, include_in_schema=False)
def rapor_page(request: Request):
# DİKKAT: burada DB çalıştırmıyoruz -> sayfa her durumda açılır
return templates.TemplateResponse("rapor.html", {"request": request})
@app.get("/api/rapor")
def api_rapor(
start: str = Query(..., description="YYYY-MM-DD"),
end: str = Query(..., description="YYYY-MM-DD"),
page: int = Query(1, ge=1),
page_size: int = Query(50, ge=10, le=500),
barcode: str | None = Query(None),
invoice: str | None = Query(None),
status: str | None = Query(None),
):
"""
Server-side pagination + filtreler.
"""
try:
offset = (page - 1) * page_size
# WHERE parçaları
where = ["TRS_PACKAGEPACKBARCODE.DATECREATED >= TO_DATE(:start || ' 00:00:00','YYYY-MM-DD HH24:MI:SS')",
"TRS_PACKAGEPACKBARCODE.DATECREATED <= TO_DATE(:end || ' 23:59:59','YYYY-MM-DD HH24:MI:SS')",
"PACKAGELINK.NAME NOT LIKE '%All%'",
"PACKAGEREGION.NAMETUR IS NOT NULL"]
params = {
"start": start,
"end": end,
}
if barcode:
where.append("TRS_PACKAGEPACKBARCODE.BARCODE LIKE :barcode")
params["barcode"] = f"%{barcode}%"
if invoice:
# InvoiceCode subquery alias'ı ile filtrelemek için outer select kullanmak daha güvenli.
# Burada basit yaklaşım: invoicecode hesaplayan subquery aynı şekilde kalacak.
# Filtreyi outer'da uygulayacağız: aşağıda SQL'i outer select yaptım.
params["invoice"] = f"%{invoice}%"
if status:
# status değeri: 'Sevk Bekliyor' vb. biz CASE sonucu üzerinden filtreleyeceğiz (outer)
params["status"] = status
where_sql = " AND ".join(where)
# Ana sorgu: önce tüm kolonları üret, sonra dış sorguda invoice/status filtrele
base_sql = f"""
SELECT *
FROM (
SELECT
TRS_PACKAGEPACKBARCODE.DATECREATED AS TARIH,
(SELECT DISTINCT PV.VALUETEXTTUR
FROM CONTENTCC_C_PROPERTYVALUE PV
JOIN CONTENTCC_C_TPROPERTYVALUE TPV ON PV.PROPERTYVALUEID = TPV.PROPERTYVALUEID
WHERE TPV.TITLEID = TL.TITLEID
AND PV.PROPERTYID = 19
FETCH FIRST 1 ROWS ONLY
) AS HEDEFKODU,
PACKAGELINK.NAME || ',' || PACKAGELINKCONTACTDEFAULTADDRESS.DISTRICTNAME || ',' || PACKAGELINKCONTACTDEFAULTADDRESS.CITYNAME AS MUSTERI,
TRS_PACKAGEPACKBARCODE.BARCODE AS BARKOD,
(CASE TRS_PACKAGEPACKBARCODE.SHIPPINGSTATUS
WHEN 0 THEN 'Sevk Bekliyor'
WHEN 1 THEN 'Sevk Kontrolü Yapıldı'
WHEN 2 THEN 'Sevk Başladı'
WHEN 3 THEN 'Transfer Edildi'
WHEN 4 THEN 'Teslim Edildi'
WHEN 5 THEN 'Teslim Edilemedi'
WHEN 99 THEN 'İptal Edildi'
END) AS DURUM,
(CASE SERVICE.ISEXPRESSSERVICE WHEN 1 THEN 'Hızlı Teslimat' ELSE 'Servis' END) AS SERVIS,
(SELECT InvoiceCode FROM (
SELECT I.CODE AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY IP.InvoiceID DESC) AS RN
FROM Finance_InvoicePackage IP
JOIN Finance_Invoice I ON I.InvoiceID = IP.InvoiceID
WHERE IP.TradingPackageID = TRS_PACKAGEPACKBARCODE.PackageID
) X WHERE X.RN = 1) AS FATURA,
SERVICEDRIVER.NAME AS SOFOR,
SERVICEVEHICLE.PLATE AS ARAC,
(SELECT DeliveredPoint FROM (
SELECT SP.DeliveredPoint,
ROW_NUMBER() OVER (ORDER BY SP.DeliveredTime DESC) AS RN
FROM Transportation_ServicePackage SP
WHERE SP.PackagePackBarcodeID = TRS_PACKAGEPACKBARCODE.PackagePackBarcodeID
AND SP.IsDelivered = 1
) Y WHERE Y.RN = 1) AS TESLIM_LOK
FROM TRS_PACKAGEPACKBARCODE
INNER JOIN TRANSPORTATION_PACKAGE PACKAGE ON PACKAGE.PACKAGEID = TRS_PACKAGEPACKBARCODE.PACKAGEID
LEFT JOIN TRANSPORTATION_REGION PACKAGEREGION ON PACKAGEREGION.REGIONID = PACKAGE.REGIONID
LEFT JOIN TRANSPORTATION_SERVICE SERVICE ON SERVICE.SERVICEID = TRS_PACKAGEPACKBARCODE.SERVICEID
LEFT JOIN TRANSPORTATION_VEHICLE SERVICEVEHICLE ON SERVICEVEHICLE.VEHICLEID = SERVICE.VEHICLEID
LEFT JOIN COLLABORATION_EMPLOYEE SERVICEDRIVER ON SERVICEDRIVER.EMPLOYEEID = SERVICE.DRIVERID
LEFT JOIN TRADING_LINK PACKAGELINK ON PACKAGELINK.LINKID = PACKAGE.LINKID
INNER JOIN TRADING_LINK TL ON TL.LINKID = PACKAGE.LINKID
LEFT JOIN CONTACTCC_CONTACT PACKAGELINKCONTACT ON PACKAGELINKCONTACT.CONTACTID = PACKAGELINK.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS PACKAGELINKCONTACTDEFAULTADDRESS ON PACKAGELINKCONTACTDEFAULTADDRESS.ADDRESSID = PACKAGELINKCONTACT.DEFAULTADDRESSID
WHERE {where_sql}
) Q
WHERE 1=1
"""
# outer filtreler
if invoice:
base_sql += " AND Q.FATURA LIKE :invoice "
if status:
base_sql += " AND Q.DURUM = :status "
# total sayısı
count_sql = f"SELECT COUNT(1) AS CNT FROM ({base_sql})"
total_row = fetch_all(count_sql, params)
total = int(total_row[0]["cnt"]) if total_row else 0
# data + pagination (Oracle 12c+)
data_sql = f"""
SELECT * FROM ({base_sql})
ORDER BY TARIH DESC
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
"""
params2 = dict(params)
params2["offset"] = offset
params2["limit"] = page_size
rows = fetch_all(data_sql, params2)
# KPI (sayfaya göre değil, tüm aralığa göre)
kpi_sql = f"""
SELECT DURUM, COUNT(1) AS ADET
FROM ({base_sql})
GROUP BY DURUM
"""
kpis = fetch_all(kpi_sql, params)
grand_total = sum(int(x["adet"]) for x in kpis) if kpis else 0
for x in kpis:
x["yuzde"] = round((int(x["adet"]) / grand_total) * 100, 2) if grand_total else 0
return {
"range": {"start": start, "end": end},
"page": page,
"page_size": page_size,
"total": total,
"kpis": kpis,
"rows": rows,
}
except Exception as e:
# 500 yerine okunur hata
raise HTTPException(status_code=500, detail=str(e))
@app.get("/api/rapor/export")
def api_export(
start: str = Query(...),
end: str = Query(...),
barcode: str | None = Query(None),
invoice: str | None = Query(None),
status: str | None = Query(None),
):
"""
Basit CSV export (donmayı azaltır).
"""
# CSV: bu endpoint'e sonra async/chunk ekleriz.
data = api_rapor(start=start, end=end, page=1, page_size=500, barcode=barcode, invoice=invoice, status=status)
rows = data["rows"]
headers = ["tarih", "hedefkodu", "musteri", "barkod", "durum", "servis", "fatura", "sofor", "arac", "teslim_lok"]
def gen():
yield ",".join(headers) + "\n"
for r in rows:
line = []
for h in headers:
v = r.get(h)
s = "" if v is None else str(v).replace('"', '""')
if "," in s or "\n" in s:
s = f'"{s}"'
line.append(s)
yield ",".join(line) + "\n"
filename = f"rapor_{start}_{end}.csv"
return StreamingResponse(gen(), media_type="text/csv",
headers={"Content-Disposition": f'attachment; filename="{filename}"'})
Rapor Dashboard
Raporlar
-
TARİH
HEDEFKODU
MÜŞTERİ
BARKOD
DURUM
SERVİS
FATURA
ŞOFÖR
ARAÇ
TESLİM LOK.
Henüz veri yok.
pip install -U fastapi uvicorn jinja2 python-dotenv oracledb
# app/db.py
import os
import oracledb
_client_inited = False
def init_oracle_client_if_needed():
"""
ORACLE_CLIENT_LIB_DIR varsa Thick mode (Instant Client) açar.
Yoksa Thin mode ile devam eder.
"""
global _client_inited
if _client_inited:
return
lib_dir = os.getenv("ORACLE_CLIENT_LIB_DIR")
if lib_dir and os.path.isdir(lib_dir):
try:
oracledb.init_oracle_client(lib_dir=lib_dir)
except Exception:
# zaten init edilmiş olabilir
pass
_client_inited = True
def get_connection():
init_oracle_client_if_needed()
user = os.getenv("ORACLE_USER")
password = os.getenv("ORACLE_PASSWORD")
# 1) En sağlam yöntem: ORACLE_DSN kullan
dsn = os.getenv("ORACLE_DSN")
# 2) Yoksa host/port/service ile üret
if not dsn:
host = os.getenv("ORACLE_HOST")
port = os.getenv("ORACLE_PORT", "1521")
service = os.getenv("ORACLE_SERVICE")
missing = [k for k, v in {
"ORACLE_USER": user,
"ORACLE_PASSWORD": password,
"ORACLE_HOST": host,
"ORACLE_SERVICE": service
}.items() if not v]
if missing:
raise RuntimeError(f"ENV eksik: {', '.join(missing)}")
# ✅ DOĞRU DSN ÜRETİMİ (ORA-01745'i kesen nokta burası)
dsn = oracledb.makedsn(host, int(port), service_name=service)
else:
missing = [k for k, v in {
"ORACLE_USER": user,
"ORACLE_PASSWORD": password,
"ORACLE_DSN": dsn
}.items() if not v]
if missing:
raise RuntimeError(f"ENV eksik: {', '.join(missing)}")
return oracledb.connect(user=user, password=password, dsn=dsn)
def fetch_all(sql: str, params: dict | None = None):
conn = get_connection()
try:
cur = conn.cursor()
cur.execute(sql, params or {})
cols = [d[0].lower() for d in cur.description]
rows = [dict(zip(cols, r)) for r in cur.fetchall()]
return rows
finally:
try:
conn.close()
except Exception:
pass
from dotenv import load_dotenv
load_dotenv()
pip install python-dotenv
ORACLE_USER=KULLANICI_ADI
ORACLE_PASSWORD=SIFRE
ORACLE_DSN=HOST_ADI_VEYA_IP:1521/SERVICE_NAME
# Instant Client kullanıyorsan (opsiyonel)
# ORACLE_CLIENT_LIB_DIR=C:\oracle\instantclient_21_11
# app/db.py
import os
import oracledb
_client_inited = False
def init_oracle_client_if_needed():
"""
ORACLE_CLIENT_LIB_DIR varsa thick mode dener.
Yoksa thin mode ile devam eder (en stabil).
"""
global _client_inited
if _client_inited:
return
lib_dir = os.getenv("ORACLE_CLIENT_LIB_DIR")
if lib_dir and os.path.isdir(lib_dir):
try:
oracledb.init_oracle_client(lib_dir=lib_dir)
except Exception:
# Zaten init edilmiş olabilir, sorun etmiyoruz
pass
_client_inited = True
def get_connection():
init_oracle_client_if_needed()
user = os.getenv("ORACLE_USER")
password = os.getenv("ORACLE_PASSWORD")
dsn = os.getenv("ORACLE_DSN") # Örn: 10.10.10.10:1521/ORCLPDB1
missing = [k for k, v in {
"ORACLE_USER": user,
"ORACLE_PASSWORD": password,
"ORACLE_DSN": dsn
}.items() if not v]
if missing:
raise RuntimeError(f"ENV eksik: {', '.join(missing)}")
# DSN parantezsiz olmalı: HOST:PORT/SERVICE
# (service) gibi parantez kullanma -> ORA-01745 tetikleyebilir
return oracledb.connect(user=user, password=password, dsn=dsn, encoding="UTF-8")
def fetch_all(sql: str, params: dict | None = None, arraysize: int = 1000):
conn = get_connection()
try:
cur = conn.cursor()
cur.arraysize = arraysize
cur.execute(sql, params or {})
cols = [d[0].lower() for d in cur.description]
rows = [dict(zip(cols, r)) for r in cur.fetchall()]
return rows
finally:
conn.close()
def fetch_page(sql: str, params: dict | None, limit: int, offset: int, arraysize: int = 1000):
"""
Oracle 12c+ OFFSET/FETCH ile server-side pagination.
"""
paged_sql = f"""
SELECT * FROM (
{sql}
)
OFFSET :_offset ROWS FETCH NEXT :_limit ROWS ONLY
"""
p = dict(params or {})
p["_offset"] = offset
p["_limit"] = limit
return fetch_all(paged_sql, p, arraysize=arraysize)
# app/main.py
import os
from datetime import date
from fastapi import FastAPI, Request, Query
from fastapi.responses import HTMLResponse, JSONResponse
from fastapi.templating import Jinja2Templates
from fastapi.staticfiles import StaticFiles
from dotenv import load_dotenv
from app.db import fetch_all, fetch_page
load_dotenv()
app = FastAPI(title="Panel Rapor", version="1.0.0")
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, "templates")
STATIC_DIR = os.path.join(BASE_DIR, "static")
templates = Jinja2Templates(directory=TEMPLATES_DIR)
if os.path.isdir(STATIC_DIR):
app.mount("/static", StaticFiles(directory=STATIC_DIR), name="static")
# ✅ Bağlantı test endpoint’i (404 olmasın diye ekledim)
@app.get("/db-test")
def db_test():
rows = fetch_all("SELECT 1 AS ok FROM dual")
return {"ok": rows[0]["ok"] if rows else 0}
# ✅ HTML sayfa: parametresiz açılır (patlamaz)
@app.get("/rapor", response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse("rapor.html", {"request": request})
# ✅ JSON API: rapor verisi (pagination + filtre)
@app.get("/api/rapor")
def rapor_api(
start: str = Query(..., description="YYYY-MM-DD"),
end: str = Query(..., description="YYYY-MM-DD"),
durum: str | None = Query(None, description="Durum filtresi (örn: Sevk Bekliyor)"),
barcode: str | None = Query(None, description="Barkod arama"),
invoice: str | None = Query(None, description="Fatura arama"),
page: int = Query(1, ge=1),
page_size: int = Query(50, ge=10, le=500),
):
offset = (page - 1) * page_size
# Buraya SENİN optimize SQL’in gelecek.
# Örnek: :start_date ve :end_date bind param kullanıyoruz.
base_sql = """
SELECT
TRS_PACKAGEPACKBARCODE.DATECREATED AS tarih,
/* HEDEFKODU */
(
SELECT DISTINCT PV.VALUETEXTTUR
FROM CONTENTCC_C_PROPERTYVALUE PV
JOIN CONTENTCC_C_TPROPERTYVALUE TPV ON PV.PROPERTYVALUEID = TPV.PROPERTYVALUEID
WHERE TPV.TITLEID = TL.TITLEID
AND TPV.PROPERTYID = 19
FETCH FIRST 1 ROWS ONLY
) AS hedefkodu,
PACKAGELINK.NAME || ',' || ADDR.DISTRICTNAME || ',' || ADDR.CITYNAME AS musteri,
TRS_PACKAGEPACKBARCODE.BARCODE AS barkod,
(CASE TRS_PACKAGEPACKBARCODE.SHIPPINGSTATUS
WHEN 0 THEN 'Sevk Bekliyor'
WHEN 1 THEN 'Sevk Kontrolü Yapıldı'
WHEN 2 THEN 'Sevk Başladı'
WHEN 3 THEN 'Transfer Edildi'
WHEN 4 THEN 'Teslim Edildi'
WHEN 5 THEN 'Teslim Edilemedi'
WHEN 99 THEN 'İptal Edildi'
END) AS durum,
(CASE SERVICE.ISEXPRESSSERVICE WHEN 1 THEN 'Hızlı Teslimat' ELSE 'Servis' END) AS servis,
(
SELECT InvoiceCode FROM (
SELECT I.Code AS InvoiceCode,
ROW_NUMBER() OVER (ORDER BY IP.InvoiceID DESC) AS rn
FROM Finance_InvoicePackage IP
JOIN Finance_Invoice I ON I.InvoiceID = IP.InvoiceID
WHERE IP.TradingPackageID = TRS_PACKAGEPACKBARCODE.PackageID
) WHERE rn = 1
) AS fatura
FROM TRS_PACKAGEPACKBARCODE
JOIN TRANSPORTATION_PACKAGE PACKAGE ON PACKAGE.PACKAGEID = TRS_PACKAGEPACKBARCODE.PACKAGEID
LEFT JOIN TRANSPORTATION_SERVICE SERVICE ON SERVICE.SERVICEID = TRS_PACKAGEPACKBARCODE.SERVICEID
LEFT JOIN TRADING_LINK PACKAGELINK ON PACKAGELINK.LINKID = PACKAGE.LINKID
JOIN TRADING_LINK TL ON TL.LINKID = PACKAGE.LINKID
LEFT JOIN CONTACTCC_CONTACT C ON C.CONTACTID = PACKAGELINK.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS ADDR ON ADDR.ADDRESSID = C.DEFAULTADDRESSID
WHERE TRS_PACKAGEPACKBARCODE.DATECREATED >= TO_DATE(:start_date, 'YYYY-MM-DD')
AND TRS_PACKAGEPACKBARCODE.DATECREATED < TO_DATE(:end_date, 'YYYY-MM-DD') + 1
AND PACKAGELINK.NAME NOT LIKE '%All%'
"""
params = {"start_date": start, "end_date": end}
# Filtreler
if durum:
base_sql += " AND (CASE TRS_PACKAGEPACKBARCODE.SHIPPINGSTATUS " \
"WHEN 0 THEN 'Sevk Bekliyor' WHEN 1 THEN 'Sevk Kontrolü Yapıldı' " \
"WHEN 2 THEN 'Sevk Başladı' WHEN 3 THEN 'Transfer Edildi' " \
"WHEN 4 THEN 'Teslim Edildi' WHEN 5 THEN 'Teslim Edilemedi' " \
"WHEN 99 THEN 'İptal Edildi' END) = :durum"
params["durum"] = durum
if barcode:
base_sql += " AND TRS_PACKAGEPACKBARCODE.BARCODE LIKE :barcode"
params["barcode"] = f"%{barcode}%"
if invoice:
base_sql += " AND EXISTS (SELECT 1 FROM Finance_InvoicePackage IP WHERE IP.TradingPackageID = TRS_PACKAGEPACKBARCODE.PackageID) "
# istersen burada invoice code LIKE ile daha detaylı filtre de ekleriz.
# Sıralama (index varsa DATECREATED ile iyi gider)
base_sql += " ORDER BY TRS_PACKAGEPACKBARCODE.DATECREATED DESC"
rows = fetch_page(base_sql, params, limit=page_size, offset=offset, arraysize=2000)
# KPI (sayfa bazlı değil, total için ayrı COUNT gerekir; şimdilik gelen veri üzerinden)
total = len(rows)
kpi = {}
for r in rows:
kpi[r["durum"]] = kpi.get(r["durum"], 0) + 1
kpi_list = [
{"durum": k, "adet": v, "oran": round((v * 100 / total), 2) if total else 0}
for k, v in sorted(kpi.items(), key=lambda x: x[1], reverse=True)
]
return JSONResponse({
"page": page,
"page_size": page_size,
"returned": total,
"kpi": kpi_list,
"rows": rows
})
import os
import oracledb
_client_inited = False
def init_oracle_client_if_needed():
global _client_inited
if _client_inited:
return
lib_dir = os.getenv("ORACLE_CLIENT_LIB_DIR")
if lib_dir and os.path.isdir(lib_dir):
try:
oracledb.init_oracle_client(lib_dir=lib_dir)
except Exception:
pass
_client_inited = True
def get_connection():
init_oracle_client_if_needed()
user = os.getenv("ORACLE_USER")
password = os.getenv("ORACLE_PASSWORD")
host = os.getenv("ORACLE_HOST")
port = os.getenv("ORACLE_PORT", "1521")
service = os.getenv("ORACLE_SERVICE")
if not all([user, password, host, service]):
raise RuntimeError("ENV eksik")
dsn = f"{host}:{port}/{service}"
return oracledb.connect(
user=user,
password=password,
dsn=dsn,
encoding="UTF-8"
)
def fetch_all(sql, params=None):
conn = get_connection()
try:
cur = conn.cursor()
cur.execute(sql, params or {})
cols = [c[0].lower() for c in cur.description]
return [dict(zip(cols, row)) for row in cur.fetchall()]
finally:
conn.close()
import os
from fastapi import FastAPI, Request
from fastapi.responses import HTMLResponse, JSONResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from app.db import fetch_all, get_connection
load_dotenv()
app = FastAPI(title="Panel Rapor")
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
templates = Jinja2Templates(directory=os.path.join(BASE_DIR, "templates"))
@app.get("/db-test")
def db_test():
try:
conn = get_connection()
conn.close()
return {"ok": 1}
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
@app.get("/rapor", response_class=HTMLResponse)
def rapor(request: Request):
sql = "SELECT 1 AS test FROM dual"
rows = fetch_all(sql)
return templates.TemplateResponse(
"rapor.html",
{"request": request, "rows": rows}
)
# app/main.py
import csv
import io
import os
from datetime import date, datetime, timedelta
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from dotenv import load_dotenv
from app.db import fetch_all, fetch_one
load_dotenv()
app = FastAPI(title="Panel Rapor", version="1.0.0")
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
TEMPLATES_DIR = os.path.join(BASE_DIR, "templates")
templates = Jinja2Templates(directory=TEMPLATES_DIR)
# ✅ Burayı kendi kaynağına göre düzenle:
# Örn: FROM TPF_VIEW tpf veya FROM SOME_SCHEMA.TPF tpf
REPORT_FROM = "FROM YOUR_VIEW_OR_TABLE tpf"
# Kolonları kendi gerçek kolon adlarınla eşle:
REPORT_SELECT = """
SELECT
tpf.tarih AS tarih,
tpf.hedefkodu AS hedefkodu,
tpf.musteri AS musteri,
tpf.barkod AS barkod,
tpf.durum AS durum,
tpf.servis AS servis,
tpf.fatura AS fatura,
tpf.sofor AS sofor,
tpf.arac AS arac,
tpf.teslim_lok AS teslim_lok
"""
# ⚠️ ORA-01745 fix: bind isimlerini p1,p2.. yaptık
REPORT_WHERE = """
WHERE tpf.tarih >= :p1
AND tpf.tarih < :p2
AND (:p3 IS NULL OR LOWER(tpf.barkod) LIKE LOWER(:p4))
AND (:p5 IS NULL OR TO_CHAR(tpf.fatura) LIKE :p6)
AND (:p7 IS NULL OR tpf.durum = :p7)
"""
REPORT_ORDER = "ORDER BY tpf.tarih DESC"
def _start_of_day(d: date) -> datetime:
return datetime(d.year, d.month, d.day, 0, 0, 0)
def _end_exclusive(d: date) -> datetime:
# end günü dahil olsun istiyoruz => < (end + 1 gün)
return _start_of_day(d) + timedelta(days=1)
@app.get("/rapor", response_class=HTMLResponse)
def rapor_page(request: Request):
# templates/rapor.html
return templates.TemplateResponse("rapor.html", {"request": request})
# ✅ Seçenek-1: /api ile kalsın (senin mevcut frontend buna göre çağırıyorsa)
@app.get("/api/db-test")
def db_test():
try:
row = fetch_one("SELECT 1 AS ok FROM dual")
return {"ok": int(row["ok"]) if row and row.get("ok") is not None else 1}
except Exception as e:
return JSONResponse(status_code=500, content={"ok": False, "error": str(e)})
@app.get("/api/rapor")
def rapor_api(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barkod: str | None = None,
fatura: str | None = None,
durum: str | None = None,
page: int = 1,
page_size: int = 50,
):
"""
✅ start/end zorunlu değil (Field required bitti)
✅ Server-side pagination (OFFSET/FETCH)
✅ KPI (duruma göre adet ve yüzde)
"""
try:
# güvenli limitler
page = max(page, 1)
page_size = max(min(page_size, 500), 1)
start_dt = _start_of_day(start)
end_dt = _end_exclusive(end)
offset = (page - 1) * page_size
limit = page_size
# bind paramlar: p1..p9 (ORA-01745 fix)
params = {
"p1": start_dt,
"p2": end_dt,
"p3": barkod,
"p4": f"%{barkod}%" if barkod else None,
"p5": fatura,
"p6": f"%{fatura}%" if fatura else None,
"p7": durum,
"p8": offset,
"p9": limit,
}
# total
count_sql = f"""
SELECT COUNT(1) AS total
{REPORT_FROM}
{REPORT_WHERE}
"""
total_row = fetch_one(count_sql, params)
total = int(total_row["total"]) if total_row and total_row.get("total") is not None else 0
# data (pagination)
data_sql = f"""
{REPORT_SELECT}
{REPORT_FROM}
{REPORT_WHERE}
{REPORT_ORDER}
OFFSET :p8 ROWS FETCH NEXT :p9 ROWS ONLY
"""
items = fetch_all(data_sql, params)
# KPI
kpi_sql = f"""
SELECT tpf.durum AS durum, COUNT(1) AS adet
{REPORT_FROM}
{REPORT_WHERE}
GROUP BY tpf.durum
ORDER BY COUNT(1) DESC
"""
kpi_rows = fetch_all(kpi_sql, params)
kpi = []
for r in kpi_rows:
adet = int(r.get("adet") or 0)
pct = round((adet / total) * 100, 2) if total else 0
kpi.append({"durum": r.get("durum"), "adet": adet, "yuzde": pct})
return {
"range": {"start": str(start), "end": str(end)},
"page": page,
"page_size": page_size,
"total": total,
"kpi": kpi,
"items": items,
}
except Exception as e:
# ✅ JSON dön -> frontend json parse ederken patlamaz
return JSONResponse(status_code=500, content={"error": str(e)})
@app.get("/api/export.csv")
def export_csv(
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
barkod: str | None = None,
fatura: str | None = None,
durum: str | None = None,
):
"""
✅ Excel için CSV export
"""
try:
start_dt = _start_of_day(start)
end_dt = _end_exclusive(end)
params = {
"p1": start_dt,
"p2": end_dt,
"p3": barkod,
"p4": f"%{barkod}%" if barkod else None,
"p5": fatura,
"p6": f"%{fatura}%" if fatura else None,
"p7": durum,
}
sql = f"""
{REPORT_SELECT}
{REPORT_FROM}
{REPORT_WHERE}
{REPORT_ORDER}
"""
rows = fetch_all(sql, params)
output = io.StringIO()
writer = csv.writer(output)
headers = ["tarih", "hedefkodu", "musteri", "barkod", "durum", "servis", "fatura", "sofor", "arac", "teslim_lok"]
writer.writerow(headers)
for r in rows:
writer.writerow([r.get(h) for h in headers])
output.seek(0)
filename = f"rapor_{start}_{end}.csv"
return StreamingResponse(
iter([output.getvalue()]),
media_type="text/csv; charset=utf-8",
headers={"Content-Disposition": f'attachment; filename="{filename}"'},
)
except Exception as e:
return JSONResponse(status_code=500, content={"error": str(e)})
