{% endblock %}
from __future__ import annotations
import os
import time
from pathlib import Path
from typing import Dict, List, Optional, Tuple
import anyio
from fastapi import FastAPI, Request
from fastapi.responses import HTMLResponse
from starlette.templating import Jinja2Templates
# Excel okumak için
import openpyxl
APP_DIR = Path(__file__).resolve().parent
DATA_DIR = APP_DIR / “data”
TEMPLATES_DIR = APP_DIR.parent / “templates” # sende templates kökte: Panel-rapor/templates
templates = Jinja2Templates(directory=str(TEMPLATES_DIR))
app = FastAPI()
# —————————-
# AYARLAR
# —————————-
# Excel dosya yolu (senin ekran görüntüsüne göre: app/data/users.xlsx)
DEFAULT_EXCEL_PATH = str(DATA_DIR / “users.xlsx”)
EXCEL_LOGIN_PATH = os.getenv(“EXCEL_LOGIN_PATH”, DEFAULT_EXCEL_PATH)
# Hedef URL’ler (2 domain / 2 url)
# Sen 2 URL göndermiştin; burada env ile yönet:
TARGET_1_URL = os.getenv(“TARGET_1_URL”, “https://example1.com/login”)
TARGET_2_URL = os.getenv(“TARGET_2_URL”, “https://example2.com/login”)
def build_targets() -> Dict[str, str]:
# key -> kullanıcıya gözüken url
return {
“target1”: TARGET_1_URL,
“target2”: TARGET_2_URL,
}
# —————————-
# EXCEL OKUMA
# —————————-
def _norm(s: str) -> str:
return (s or “”).strip().lower()
def load_users_from_excel(excel_path: str) -> Tuple[List[dict], Optional[str]]:
“””
Excel kolonları:
Zorunlu: customer_code, username, password
Opsiyonel: musteri
Alternatif kolon adları:
eczane_kodu -> customer_code
kullanici_adi -> username
sifre -> password
“””
p = Path(excel_path)
if not p.exists():
return [], f”Excel bulunamadı: {p}”
try:
wb = openpyxl.load_workbook(p, data_only=True)
ws = wb.active
rows = list(ws.iter_rows(values_only=True))
if not rows or len(rows) < 2:
return [], "Excel boş veya satır yok."
header = [str(x).strip() if x is not None else "" for x in rows[0]]
header_norm = [_norm(x) for x in header]
# kolon index bulucu
def col_idx(*names: str) -> Optional[int]:
for nm in names:
nm2 = _norm(nm)
if nm2 in header_norm:
return header_norm.index(nm2)
return None
idx_customer = col_idx(“customer_code”, “eczane_kodu”)
idx_username = col_idx(“username”, “kullanici_adi”)
idx_password = col_idx(“password”, “sifre”)
idx_musteri = col_idx(“musteri”, “müşteri”, “customer”, “name”)
missing = []
if idx_customer is None: missing.append(“customer_code (veya eczane_kodu)”)
if idx_username is None: missing.append(“username (veya kullanici_adi)”)
if idx_password is None: missing.append(“password (veya sifre)”)
if missing:
return [], “Excel kolonları eksik: ” + “, “.join(missing)
users: List[dict] = []
for r in rows[1:]:
# güvenli al
def cell(i: Optional[int]) -> str:
if i is None:
return “”
v = r[i] if i < len(r) else None
if v is None:
return ""
return str(v).strip()
customer_code = cell(idx_customer)
username = cell(idx_username)
password = cell(idx_password)
musteri = cell(idx_musteri)
# boş satır atla
if not customer_code and not username and not password:
continue
users.append({
"customer_code": customer_code,
"username": username,
"password": password,
"musteri": musteri,
})
if not users:
return [], "Excel okundu ama kullanılabilir kullanıcı satırı yok."
return users, None
except Exception as e:
return [], f"Excel okuma hatası: {type(e).__name__}: {e}"
# ----------------------------
# PLAYWRIGHT İLE OTOMATİK GİRİŞ
# ----------------------------
def run_playwright_login(url: str, customer_code: str, username: str, password: str) -> None:
“””
Senin attığın DOM’a göre:
#pharmacyCode
#Customer_username
#Customer_password
button.login_submit.login_submit.bg-color-pink.logg_button.Customer_login_button
“””
from playwright.sync_api import sync_playwright
with sync_playwright() as p:
browser = p.chromium.launch(headless=False, slow_mo=60) # GÖRÜNÜR + yavaşlat
context = browser.new_context(viewport={“width”: 1200, “height”: 900})
page = context.new_page()
page.goto(url, wait_until=”domcontentloaded”, timeout=60000)
# Inputlar (birkaç fallback ile)
def fill_any(selectors: List[str], value: str) -> None:
last_err = None
for sel in selectors:
try:
loc = page.locator(sel)
loc.wait_for(state=”visible”, timeout=15000)
loc.click(timeout=5000)
loc.fill(value, timeout=5000)
return
except Exception as e:
last_err = e
raise RuntimeError(f”Input bulunamadı: {selectors}. Hata: {last_err}”)
fill_any([“#pharmacyCode”, “input#pharmacyCode”], customer_code)
fill_any([“#Customer_username”, “input#Customer_username”], username)
fill_any([“#Customer_password”, “input#Customer_password”], password)
# Buton tıkla (senin attığın class zinciri + fallback)
click_selectors = [
“button.login_submit.login_submit.bg-color-pink.logg_button.Customer_login_button”,
“button.Customer_login_button”,
“button:has-text(‘Giriş Yap’)”,
“text=Giriş Yap”,
]
clicked = False
last_err = None
for sel in click_selectors:
try:
btn = page.locator(sel)
btn.wait_for(state=”visible”, timeout=15000)
btn.click(timeout=5000)
clicked = True
break
except Exception as e:
last_err = e
if not clicked:
raise RuntimeError(f”Giriş butonu bulunamadı. Son hata: {last_err}”)
# Login sonrası biraz bekle
try:
page.wait_for_load_state(“networkidle”, timeout=20000)
except Exception:
pass
time.sleep(2)
# Tarayıcı açık kalsın istiyorsan kapatma:
# browser.close()
# Kapatmak istiyorsan aç:
# browser.close()
# —————————-
# ROUTES
# —————————-
@app.get(“/api/health”)
def health():
return {“ok”: True}
# /auto_login (sen böyle kullanıyorsun) + /auto-login (template action böyleydi)
@app.get(“/auto_login”, response_class=HTMLResponse)
@app.get(“/auto-login”, response_class=HTMLResponse)
def auto_login_page(request: Request):
targets = build_targets()
users, err = load_users_from_excel(EXCEL_LOGIN_PATH)
# Dropdown düzgün görünsün:
# musteri varsa: “musteri — customer_code”
# yoksa: “customer_code — username”
for u in users:
if u.get(“musteri”):
u[“label”] = f”{u[‘musteri’]} — {u[‘customer_code’]}”
else:
u[“label”] = f”{u[‘customer_code’]} — {u[‘username’]}”
return templates.TemplateResponse(
“auto_login.html”,
{
“request”: request,
“targets”: targets,
“users”: users,
“error”: err,
“result”: None,
},
)
@app.post(“/auto_login”, response_class=HTMLResponse)
@app.post(“/auto-login”, response_class=HTMLResponse)
async def auto_login_submit(request: Request):
targets = build_targets()
users, err = load_users_from_excel(EXCEL_LOGIN_PATH)
form = await request.form()
target_key = str(form.get(“target_key”) or “”).strip()
selected_customer_code = str(form.get(“selected_customer_code”) or “”).strip()
if err:
return templates.TemplateResponse(
“auto_login.html”,
{
“request”: request,
“targets”: targets,
“users”: users,
“error”: err,
“result”: None,
},
)
if target_key not in targets:
return templates.TemplateResponse(
“auto_login.html”,
{
“request”: request,
“targets”: targets,
“users”: users,
“error”: “Domain seçimi geçersiz.”,
“result”: None,
},
)
if not selected_customer_code:
return templates.TemplateResponse(
“auto_login.html”,
{
“request”: request,
“targets”: targets,
“users”: users,
“error”: “Lütfen listeden bir müşteri seç.”,
“result”: None,
},
)
# seçilen kullanıcıyı bul
selected = None
for u in users:
if str(u.get(“customer_code”, “”)).strip() == selected_customer_code:
selected = u
break
if not selected:
return templates.TemplateResponse(
“auto_login.html”,
{
“request”: request,
“targets”: targets,
“users”: users,
“error”: “Seçilen customer_code Excel’de bulunamadı.”,
“result”: None,
},
)
url = targets[target_key]
customer_code = selected[“customer_code”]
username = selected[“username”]
password = selected[“password”]
# Tarayıcı işini thread’de çalıştır (FastAPI bloklamasın)
try:
await anyio.to_thread.run_sync(
run_playwright_login,
url,
customer_code,
username,
password,
)
result_msg = f”Otomatik giriş tetiklendi: {url} | {customer_code}”
error_msg = None
except Exception as e:
result_msg = None
error_msg = f”Otomatik giriş başarısız: {type(e).__name__}: {e}”
return templates.TemplateResponse(
“auto_login.html”,
{
“request”: request,
“targets”: targets,
“users”: users,
“error”: error_msg,
“result”: result_msg,
},
)
# app/main.py
from __future__ import annotations
import os
import time
from pathlib import Path
from datetime import date, datetime, timedelta
from typing import Dict, List, Optional
from dotenv import load_dotenv
from fastapi import FastAPI, Query, Request, Form
from fastapi.responses import (
HTMLResponse,
JSONResponse,
StreamingResponse,
RedirectResponse,
)
from fastapi.templating import Jinja2Templates
from fastapi.staticfiles import StaticFiles
from openpyxl import load_workbook, Workbook
from openpyxl.utils import get_column_letter
# DB helpers
from app.db import fetch_all, fetch_one
# Router
from app.musteriler import router as musteriler_router
load_dotenv()
app = FastAPI(title=”Panel Rapor”, version=”1.0.0″)
# =============================================================================
# PATHS
# =============================================================================
APP_DIR = Path(__file__).resolve().parent
BASE_DIR = APP_DIR.parent
TEMPLATES_DIR = BASE_DIR / “templates”
STATIC_DIR = BASE_DIR / “static”
DATA_DIR = APP_DIR / “data”
DEFAULT_EXCEL_PATH = DATA_DIR / “users.xlsx” # expected correct file name
templates = Jinja2Templates(directory=str(TEMPLATES_DIR))
if STATIC_DIR.is_dir():
app.mount(“/static”, StaticFiles(directory=str(STATIC_DIR)), name=”static”)
app.include_router(musteriler_router, prefix=”/api/musteriler”, tags=[“Müşteriler”])
# =============================================================================
# AUTO LOGIN CONFIG
# =============================================================================
# IMPORTANT: Use login page URL directly (your first one should be /login)
TARGETS: Dict[str, str] = {
“target1”: “https://esiparisv2.alliance-healthcare.com.tr/login”,
“target2”: “https://esiparisv2.alliance-healthcare.com.tr/login”, # put your 2nd URL here if different
}
# Selectors from your screenshots
SEL_PHARMACY = “#pharmacyCode”
SEL_USERNAME = “#Customer_username”
SEL_PASSWORD = “#Customer_password”
SEL_LOGIN_BTN = “button.Customer_login_button” # ✅ the correct one (unique by class)
def get_excel_path() -> Path:
“””
Excel path priority:
1) ENV: EXCEL_LOGIN_PATH
2) Default: app/data/users.xlsx
Also: if user mistakenly created users.xslx, try to auto-detect it.
“””
env_path = os.getenv(“EXCEL_LOGIN_PATH”)
if env_path:
p = Path(env_path).expanduser().resolve()
return p
# default
if DEFAULT_EXCEL_PATH.exists():
return DEFAULT_EXCEL_PATH
# common typo: users.xslx
typo = DATA_DIR / “users.xslx”
if typo.exists():
return typo
return DEFAULT_EXCEL_PATH
def _norm_header(x: str) -> str:
return str(x or “”).strip().lower()
def load_users_from_excel(excel_path: Path) -> List[Dict[str, str]]:
“””
Reads users from Excel using openpyxl (no pandas needed).
Required columns (exact):
– customer_code
– username
– password
Optional:
– musteri (display name only)
Returns:
[{musteri, customer_code, username, password}, …]
“””
if not excel_path.exists():
return []
# openpyxl supports .xlsx; if someone uses .xls it will fail
try:
wb = load_workbook(filename=str(excel_path), data_only=True)
except Exception:
return []
ws = wb.active
rows = list(ws.iter_rows(values_only=True))
if not rows:
return []
headers = [_norm_header(c) for c in rows[0]]
if not headers:
return []
# map header -> index
idx = {h: i for i, h in enumerate(headers) if h}
required = [“customer_code”, “username”, “password”]
if not all(k in idx for k in required):
return []
out: List[Dict[str, str]] = []
for r in rows[1:]:
if not r:
continue
def getv(key: str) -> str:
i = idx.get(key)
if i is None or i >= len(r):
return “”
v = r[i]
if v is None:
return “”
return str(v).strip()
customer_code = getv(“customer_code”)
username = getv(“username”)
password = getv(“password”)
musteri = getv(“musteri”) if “musteri” in idx else “”
# minimal validation
if not customer_code or not username:
continue
out.append(
{
“musteri”: musteri,
“customer_code”: customer_code,
“username”: username,
“password”: password,
}
)
return out
def base_context(request: Request) -> Dict:
return {
“request”: request,
“targets”: TARGETS,
“db_connected”: True,
“excel_path”: str(get_excel_path()),
}
# =============================================================================
# PLAYWRIGHT AUTO LOGIN
# =============================================================================
def run_auto_login(url: str, customer_code: str, username: str, password: str) -> None:
“””
Opens browser, fills fields, clicks ONLY the correct “Giriş Yap” button:
button.Customer_login_button
If you run server on a different machine, browser opens on that machine.
“””
# You can control headless via env:
# HEADLESS=1 => headless True
headless = os.getenv(“HEADLESS”, “0”).strip() == “1”
try:
from playwright.sync_api import sync_playwright
except Exception as e:
raise RuntimeError(
‘Playwright is not installed. Run: “pip install playwright” and then: “playwright install”‘
) from e
with sync_playwright() as p:
browser = p.chromium.launch(headless=headless)
context = browser.new_context()
page = context.new_page()
# go to login page
page.goto(url, wait_until=”domcontentloaded”, timeout=60000)
# ensure inputs exist
page.wait_for_selector(SEL_PHARMACY, timeout=30000)
page.wait_for_selector(SEL_USERNAME, timeout=30000)
page.wait_for_selector(SEL_PASSWORD, timeout=30000)
# fill
page.fill(SEL_PHARMACY, customer_code)
page.fill(SEL_USERNAME, username)
page.fill(SEL_PASSWORD, password)
# ✅ FIX: click the UNIQUE button by class (no strict-mode violation)
# If multiple exist for any reason, we click the first visible one.
btn = page.locator(SEL_LOGIN_BTN).filter(has_text=”Giriş Yap”)
if btn.count() == 0:
# fallback: class only (some pages may not include exact text node)
btn = page.locator(SEL_LOGIN_BTN)
# wait for it to be visible/clickable
btn.first.wait_for(state=”visible”, timeout=30000)
btn.first.click()
# optional: wait a bit for redirect/login
page.wait_for_timeout(3000)
# keep browser open for a short time if headful (so you see it)
keep_open_sec = int(os.getenv(“KEEP_BROWSER_OPEN_SEC”, “2”))
if not headless and keep_open_sec > 0:
page.wait_for_timeout(keep_open_sec * 1000)
context.close()
browser.close()
# =============================================================================
# DATE HELPERS
# =============================================================================
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)
# =============================================================================
# SQL (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
“””
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,
}
# =============================================================================
# PAGES
# =============================================================================
@app.get(“/”, include_in_schema=False)
def root():
return RedirectResponse(url=”/rapor”)
@app.get(“/rapor”, response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse(“rapor.html”, {**base_context(request)})
@app.get(“/rapor2”, response_class=HTMLResponse)
def rapor2_page(request: Request):
return templates.TemplateResponse(“rapor2.html”, {**base_context(request)})
@app.get(“/rapor_pesin”, response_class=HTMLResponse)
def rapor_pesin_page(request: Request):
return templates.TemplateResponse(“rapor_pesin.html”, {**base_context(request)})
@app.get(“/musteriler”, response_class=HTMLResponse)
def musteriler_page(request: Request):
return templates.TemplateResponse(“musteriler.html”, {**base_context(request)})
# =============================================================================
# AUTO LOGIN (GET/POST) + ALIASES
# =============================================================================
def _render_auto_login(request: Request, users, error: Optional[str], result: Optional[str]):
return templates.TemplateResponse(
“auto_login.html”,
{
**base_context(request),
“users”: users,
“error”: error,
“result”: result,
},
)
@app.get(“/auto_login”, response_class=HTMLResponse)
@app.get(“/auto-login”, response_class=HTMLResponse)
def auto_login_page(request: Request):
excel_path = get_excel_path()
users = load_users_from_excel(excel_path)
error = None
if not excel_path.exists():
error = f”Excel not found: {excel_path}”
elif len(users) == 0:
error = f”Excel loaded but empty or headers mismatch. Required headers: customer_code, username, password. File: {excel_path}”
return _render_auto_login(request, users, error, None)
@app.post(“/auto_login”, response_class=HTMLResponse)
@app.post(“/auto-login”, response_class=HTMLResponse)
def auto_login_submit(
request: Request,
target_key: str = Form(…),
selected_customer_code: str = Form(…),
):
“””
IMPORTANT:
– Form(…) requires: pip install python-multipart
“””
excel_path = get_excel_path()
users = load_users_from_excel(excel_path)
if target_key not in TARGETS:
return _render_auto_login(request, users, “Invalid domain selection.”, None)
if not excel_path.exists():
return _render_auto_login(request, [], f”Excel not found: {excel_path}”, None)
if not users:
return _render_auto_login(
request,
users,
“Excel is empty or column headers do not match. Required: customer_code, username, password”,
None,
)
chosen = next((u for u in users if u[“customer_code”] == selected_customer_code), None)
if not chosen:
return _render_auto_login(request, users, “Selected customer_code not found in Excel.”, None)
url = TARGETS[target_key]
try:
run_auto_login(
url=url,
customer_code=chosen[“customer_code”],
username=chosen[“username”],
password=chosen[“password”],
)
except Exception as e:
return _render_auto_login(request, users, f”Auto login failed: {str(e)}”, None)
label = chosen[“musteri”].strip()
if label:
result = f”Auto login triggered: {url} | {label} | {chosen[‘customer_code’]} / {chosen[‘username’]}”
else:
result = f”Auto login triggered: {url} | {chosen[‘customer_code’]} / {chosen[‘username’]}”
return _render_auto_login(request, users, None, result)
# =============================================================================
# HEALTH
# =============================================================================
@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)})
# =============================================================================
# RAPOR 1 API
# =============================================================================
@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:
page = max(1, page)
page_size = min(max(1, 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
# =============================================================================
@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)
# =============================================================================
# EXCEL EXPORT (RAPOR 1)
# =============================================================================
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,
)
//// müşteriler
WITH CUSTOMERS AS (SELECT Trading_Sales_Customer.LinkID,Trading_Sales_Customer.AccountID,Trading_Sales_Customer.SalesManagerID FROM “TRADING_LINK” INNER JOIN “TRADING_SALES_CUSTOMER” ON “TRADING_LINK”.”LINKID” = “TRADING_SALES_CUSTOMER”.”LINKID” WHERE “TRADING_SALES_CUSTOMER”.”SALESMANAGERID” = :Parameter_2 AND “TRADING_LINK”.”STATUSID” = :Parameter_3)SELECT (CUSTOMERWAREHOUSE.NameTUR) AS WAREHOUSENAME, (Coalesce(UNBILLEDORDER.GROSSTOTAL,0)) AS ORDERTOTAL, ( Case when Trading_Sales_Customer.RiskLimit>-1 Then coalesce(Trading_Sales_Customer.RiskLimit,0)- coalesce(Trading_Sales_Customer.Risk,0) -(Coalesce(UNBILLEDORDER.GROSSTOTAL,0)) Else -1 End ) AS AVAILABLERISKLIMIT, (Coalesce(CUSTOMERDEAL.MAXDAYSDUE,0)) AS DEALDAYSDUE, (Coalesce(CUSTOMERDEAL.MAXRATE,0)) AS DEALRATE, ( COALESCE(( SELECT LISTAGG(CA.NameTUR ,’,’) WITHIN GROUP (ORDER BY CA.NameTUR ) FROM Crm_Audience CA INNER JOIN Crm_AudienceLink CAL ON CA.AudienceID = CAL.AudienceID WHERE CAL.LinkID = Trading_Link.LinkID AND CAL.StatusID = 12 ),’ ‘) ) AS AUDIENCES, (Select CONTENTCC_C_PROPERTYVALUE.ValueTextTUR From CONTENTCC_C_PROPERTYVALUE Inner join CONTENTCC_C_TPROPERTYVALUE on CONTENTCC_C_TPROPERTYVALUE.PropertyValueID=CONTENTCC_C_PROPERTYVALUE.PropertyValueID AND CONTENTCC_C_PROPERTYVALUE.StatusID=12 INNER JOIN ContentCC_Catalog_Title CCT ON CCT.TitleID=CONTENTCC_C_TPROPERTYVALUE.TitleID WHERE CCT.TitleID=TRADING_LINK.TitleID and CONTENTCC_C_PROPERTYVALUE.PropertyID=18 AND ROWNUM<=1 ) AS TITLEPROPERTY18, (Select CONTENTCC_C_PROPERTYVALUE.ValueTextTUR From CONTENTCC_C_PROPERTYVALUE Inner join CONTENTCC_C_TPROPERTYVALUE on CONTENTCC_C_TPROPERTYVALUE.PropertyValueID=CONTENTCC_C_PROPERTYVALUE.PropertyValueID AND CONTENTCC_C_PROPERTYVALUE.StatusID=12 INNER JOIN ContentCC_Catalog_Title CCT ON CCT.TitleID=CONTENTCC_C_TPROPERTYVALUE.TitleID WHERE CCT.TitleID=TRADING_LINK.TitleID and CONTENTCC_C_PROPERTYVALUE.PropertyID=6 AND ROWNUM<=1 ) AS TITLEPROPERTY6, (Select CONTENTCC_C_PROPERTYVALUE.ValueTextTUR From CONTENTCC_C_PROPERTYVALUE Inner join CONTENTCC_C_TPROPERTYVALUE on CONTENTCC_C_TPROPERTYVALUE.PropertyValueID=CONTENTCC_C_PROPERTYVALUE.PropertyValueID AND CONTENTCC_C_PROPERTYVALUE.StatusID=12 INNER JOIN ContentCC_Catalog_Title CCT ON CCT.TitleID=CONTENTCC_C_TPROPERTYVALUE.TitleID WHERE CCT.TitleID=TRADING_LINK.TitleID and CONTENTCC_C_PROPERTYVALUE.PropertyID=19 AND ROWNUM<=1 ) AS TITLEPROPERTY19, (Select CONTENTCC_C_PROPERTYVALUE.ValueTextTUR From CONTENTCC_C_PROPERTYVALUE Inner join CONTENTCC_C_TPROPERTYVALUE on CONTENTCC_C_TPROPERTYVALUE.PropertyValueID=CONTENTCC_C_PROPERTYVALUE.PropertyValueID AND CONTENTCC_C_PROPERTYVALUE.StatusID=12 INNER JOIN ContentCC_Catalog_Title CCT ON CCT.TitleID=CONTENTCC_C_TPROPERTYVALUE.TitleID WHERE CCT.TitleID=TRADING_LINK.TitleID and CONTENTCC_C_PROPERTYVALUE.PropertyID=20 AND ROWNUM<=1 ) AS TITLEPROPERTY20, (Select CONTENTCC_C_PROPERTYVALUE.ValueTextTUR From CONTENTCC_C_PROPERTYVALUE Inner join CONTENTCC_C_TPROPERTYVALUE on CONTENTCC_C_TPROPERTYVALUE.PropertyValueID=CONTENTCC_C_PROPERTYVALUE.PropertyValueID AND CONTENTCC_C_PROPERTYVALUE.StatusID=12 INNER JOIN ContentCC_Catalog_Title CCT ON CCT.TitleID=CONTENTCC_C_TPROPERTYVALUE.TitleID WHERE CCT.TitleID=TRADING_LINK.TitleID and CONTENTCC_C_PROPERTYVALUE.PropertyID=7 AND ROWNUM<=1 ) AS TITLEPROPERTY7, (Select CONTENTCC_C_PROPERTYVALUE.ValueTextTUR From CONTENTCC_C_PROPERTYVALUE Inner join CONTENTCC_C_TPROPERTYVALUE on CONTENTCC_C_TPROPERTYVALUE.PropertyValueID=CONTENTCC_C_PROPERTYVALUE.PropertyValueID AND CONTENTCC_C_PROPERTYVALUE.StatusID=12 INNER JOIN ContentCC_Catalog_Title CCT ON CCT.TitleID=CONTENTCC_C_TPROPERTYVALUE.TitleID WHERE CCT.TitleID=TRADING_LINK.TitleID and CONTENTCC_C_PROPERTYVALUE.PropertyID=8 AND ROWNUM<=1 ) AS TITLEPROPERTY8, "TRADING_LINK".*, "TRADING_SALES_CUSTOMER".*, "LOGISTICSREGION"."NAME" AS LOGISTICSREGIONNAME, "TRADEAGREEMENTTYPE"."NAMETUR" AS TRADEAGREEMENTTYPENAMETUR, "CUSTOMERCOIN"."APPROVEDWONCOIN" AS CUSTOMERCOINAPPROVEDWONCOIN, "DEFAULTUSER"."NAME" AS DEFAULTUSERNAME, "TYPE"."NAME" AS TYPENAME, "SALESTEAM"."NAMETUR" AS SALESTEAMNAMETUR, "SALESCLERK"."NAMETUR" AS SALESCLERKNAMETUR, "STATUS"."NAMETUR" AS STATUSNAMETUR, "DEFAULTSEGMENTATION"."NAMETUR" AS DEFAULTSEGMENTATIONNAMETUR, "PAYMENTOPTION"."NAMETUR" AS PAYMENTOPTIONNAMETUR, "CONTACTDEFAULTEMAIL"."ADDRESS" AS CONTACTDEFAULTEMAILADDRESS, "POS"."NAMETUR" AS POSNAMETUR, "REGION"."NAMETUR" AS REGIONNAMETUR, "CONTACTDEFAULTADDRESS"."COUNTRYNAME" AS CONTACTDEFAULTADDRESSCOUNTRYNAME, "CONTACTDEFAULTADDRESS"."CITYNAME" AS CONTACTDEFAULTADDRESSCITYNAME, "CONTACTDEFAULTADDRESS"."COUNTYNAME" AS CONTACTDEFAULTADDRESSCOUNTYNAME, "CONTACTDEFAULTADDRESS"."DISTRICTNAME" AS CONTACTDEFAULTADDRESSDISTRICTNAME, "CONTACTDEFAULTADDRESS"."BODY" AS CONTACTDEFAULTADDRESSBODY, "CONTACTDEFAULTADDRESS"."LATITUDE" AS CONTACTDEFAULTADDRESSLATITUDE, "CONTACTDEFAULTADDRESS"."LONGITUDE" AS CONTACTDEFAULTADDRESSLONGITUDE, "CONTACTDEFAULTPHONE"."COUNTRYCODE" AS CONTACTDEFAULTPHONECOUNTRYCODE, "CONTACTDEFAULTPHONE"."REGIONCODE" AS CONTACTDEFAULTPHONEREGIONCODE, "CONTACTDEFAULTPHONE"."NUMBER" AS CONTACTDEFAULTPHONENUMBER, "CONTACTDEFAULTPHONE"."EXTENSION" AS CONTACTDEFAULTPHONEEXTENSION FROM "TRADING_LINK" INNER JOIN "TRADING_SALES_CUSTOMER" ON "TRADING_LINK"."LINKID" = "TRADING_SALES_CUSTOMER"."LINKID" LEFT JOIN "LOGISTICS_TRANSPORTATION_REGION" "LOGISTICSREGION" ON "LOGISTICSREGION"."REGIONID" = "TRADING_SALES_CUSTOMER"."LOGISTICSREGIONID" LEFT JOIN "CRM_TRADEAGREEMENTTYPE" "TRADEAGREEMENTTYPE" ON "TRADEAGREEMENTTYPE"."TRADEAGREEMENTTYPEID" = "TRADING_LINK"."TRADEAGREEMENTTYPEID" LEFT JOIN "TRD_SAL_CSTMRCOIN" "CUSTOMERCOIN" ON "CUSTOMERCOIN"."CUSTOMERCOINID" = "TRADING_SALES_CUSTOMER"."CUSTOMERCOINID" LEFT JOIN "NETVISSA_USER" "DEFAULTUSER" ON "DEFAULTUSER"."USERID" = "TRADING_LINK"."DEFAULTUSERID" LEFT JOIN "TRADING_LINKTYPE" "TYPE" ON "TYPE"."LINKTYPEID" = "TRADING_LINK"."TYPEID" LEFT JOIN "TRADING_SALES_SALESTEAM" "SALESTEAM" ON "SALESTEAM"."SALESTEAMID" = "TRADING_SALES_CUSTOMER"."SALESTEAMID" LEFT JOIN "COLLABORATION_NODE" "SALESCLERK" ON "SALESCLERK"."NODEID" = "TRADING_SALES_CUSTOMER"."SALESCLERKID" LEFT JOIN "FRAMEWORK_STATUS" "STATUS" ON "STATUS"."STATUSID" = "TRADING_LINK"."STATUSID" LEFT JOIN "CRM_SEGMENTATION" "DEFAULTSEGMENTATION" ON "DEFAULTSEGMENTATION"."SEGMENTATIONID" = "TRADING_LINK"."DEFAULTSEGMENTATIONID" LEFT JOIN "FINANCE_PAYMENTOPTION" "PAYMENTOPTION" ON "PAYMENTOPTION"."PAYMENTOPTIONID" = "TRADING_LINK"."PAYMENTOPTIONID" LEFT JOIN "CONTACTCC_CONTACT" "CONTACT" ON "CONTACT"."CONTACTID" = "TRADING_LINK"."CONTACTID" LEFT JOIN "CONTACTCC_EMAIL" "CONTACTDEFAULTEMAIL" ON "CONTACTDEFAULTEMAIL"."EMAILID" = "CONTACT"."DEFAULTEMAILID" LEFT JOIN "COLLABORATION_NODE" "POS" ON "POS"."NODEID" = "TRADING_SALES_CUSTOMER"."POSID" LEFT JOIN "TRANSPORTATION_REGION" "REGION" ON "REGION"."REGIONID" = "TRADING_SALES_CUSTOMER"."REGIONID" LEFT JOIN "CONTACTCC_ADDRESS" "CONTACTDEFAULTADDRESS" ON "CONTACTDEFAULTADDRESS"."ADDRESSID" = "CONTACT"."DEFAULTADDRESSID" LEFT JOIN "CONTACTCC_PHONE" "CONTACTDEFAULTPHONE" ON "CONTACTDEFAULTPHONE"."PHONEID" = "CONTACT"."DEFAULTPHONEID" LEFT JOIN ( Select GT.CustomerID,Collaboration_Node.NodeID,Collaboration_Node.NameTUR FROM Collaboration_Node INNER JOIN (Select TRD_SAL_CUSTOMERWAREHOUSE.CustomerID, MAX(TRD_SAL_CUSTOMERWAREHOUSE.TradingWarehouseID) WAREHOUSEID FROM TRD_SAL_CUSTOMERWAREHOUSE INNER JOIN (SELECT CustomerID,MIN (Priority) MINPR FROM TRD_SAL_CUSTOMERWAREHOUSE Group By CustomerID ) GT ON GT.CustomerID=TRD_SAL_CUSTOMERWAREHOUSE.CustomerID AND GT.MINPR=TRD_SAL_CUSTOMERWAREHOUSE.Priority Group by TRD_SAL_CUSTOMERWAREHOUSE.CustomerID) GT ON GT.WAREHOUSEID=Collaboration_Node.NodeID ) CUSTOMERWAREHOUSE ON CUSTOMERWAREHOUSE.CustomerID=Trading_Sales_Customer.LinkID LEFT JOIN ( Select CustomerID,Sum(GROSSTOTAL) GROSSTOTAL FROM ( Select Trading_Sales_SalesOrder.CustomerID, Sum(Coalesce(Trading_Sales_SalesOrder.GrossTotal,0)) GROSSTOTAL From Trading_Sales_SalesOrder Where Trading_Sales_SalesOrder.StatusId In (8,9,103,112) GROUP BY Trading_Sales_SalesOrder.CustomerID UNION ALL Select Trading_Sales_SalesOrder.CustomerID, Sum(Coalesce(TRD_SAL_SALESORDERITEM.GrossTotal,0)) GROSSTOTAL From Trading_Sales_SalesOrder Inner join TRD_SAL_SALESORDERITEM ON TRD_SAL_SALESORDERITEM.SalesOrderID=Trading_Sales_SalesOrder.SalesOrderID Inner join TRD_SAL_SALORDITEMPCKGITEM ON TRD_SAL_SALORDITEMPCKGITEM.SalesOrderItemID=TRD_SAL_SALESORDERITEM.SalesOrderItemID Inner join Transportation_PackageItem ON Transportation_PackageItem.PackageItemID=TRD_SAL_SALORDITEMPCKGITEM.PackageItemID Inner join Transportation_Package ON Transportation_Package.PackageID=Transportation_PackageItem.PackageID Where Trading_Sales_SalesOrder.StatusID=101 AND Trading_Sales_SalesOrder.DateCreated>= TRUNC(SYSDATE) + -30 AND Coalesce(Transportation_PackageItem.InvoiceItemID,0)=0 AND Coalesce(Transportation_PackageItem.QuantityCancelled,0)=0 AND Transportation_Package.StatusID in (45,74) GROUP BY Trading_Sales_SalesOrder.CustomerID) UNBILLEDORDER GROUP BY CustomerID ) UNBILLEDORDER ON UNBILLEDORDER.CustomerID=Trading_Sales_Customer.LinkID LEFT JOIN ( SELECT CD.CustomerID,CD.SalesManagerID , MAX(CASE WHEN CD.Type = 2 THEN CDA.DaysDue ELSE 0 END) MAXDAYSDUE , MAX(CASE WHEN CD.Type IN (1,3,4) THEN CDA.Rate ELSE 0 END) MAXRATE FROM TRD_SAL_CSTMRDEALACTVTY CDA INNER JOIN TRD_SAL_CSTMRDEAL CD ON CDA.CustomerDealID = CD.CustomerDealID WHERE CDA.StatusID = 12 AND SYSDATE BETWEEN CDA.TimeStarts AND CDA.TimeEnds GROUP BY CD.CustomerID, CD.SalesManagerID ) CUSTOMERDEAL ON CUSTOMERDEAL.CustomerID=Trading_Sales_Customer.LinkID AND CUSTOMERDEAL.SalesManagerID=Trading_Sales_Customer.SalesManagerID WHERE “TRADING_SALES_CUSTOMER”.”SALESMANAGERID” = :Parameter_2 AND “TRADING_LINK”.”STATUSID” = :Parameter_3 ORDER BY “TRADING_LINK”.”NAME” ASC DECLARE :Parameter_2 AS Decimal = 9 , :Parameter_3 AS Decimal
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.
# app/musteriler.py
from datetime import date
from fastapi import APIRouter
from fastapi.responses import JSONResponse, StreamingResponse
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from app.db import fetch_all, fetch_one
router = APIRouter()
BASE_SQL = “””
SELECT
— TRADING_LINK
TL.LINKID AS linkid,
TL.CONTACTID AS contactid,
TL.TITLEID AS titleid,
TL.NAME AS name,
TL.DESCRIPTION AS description,
TL.STATUSID AS statusid,
TL.DATECREATED AS datecreated,
TL.DATEMODIFIED AS datemodified,
— TRADING_SALES_CUSTOMER
TSC.CUSTOMERID AS customerid,
TSC.ACCOUNTID AS accountid,
TSC.SALESMANAGERID AS salesmanagerid,
TSC.SALESCLERKID AS salesclerkid,
TSC.SALESCLERK2ID AS salesclerk2id,
— ✅ DÜZELTİLDİ: SALETEAMID ❌ -> SALESTEAMID ✅
TSC.SALESTEAMID AS salesteamid,
TSC.POSID AS posid,
TSC.ISACTIVE AS isactive,
TSC.RISK AS risk,
TSC.RISKLIMIT AS risklimit,
CASE
WHEN NVL(TSC.RISKLIMIT,0) > -1 THEN NVL(TSC.RISKLIMIT,0) – NVL(TSC.RISK,0)
ELSE -1
END AS availablerisklimit,
TSC.LOGISTICSREGIONID AS logisticsregionid,
TSC.REGIONID AS regionid,
TSC.MAINWAREHOUSEID AS mainwarehouseid,
TSC.CUSTOMERCOINID AS customercoinid,
TSC.DATEOFRISK AS dateofrisk,
TSC.FIRSTORDERDATE AS firstorderdate,
TSC.LASTORDERDATE AS lastorderdate,
— CONTACT + ADDRESS (doğru zincir)
C.DEFAULTADDRESSID AS defaultaddressid,
ADR.ADDRESSID AS addressid,
ADR.BODY AS body,
ADR.AVENUE AS avenue,
ADR.PARISH AS parish,
ADR.STREET AS street,
ADR.”NUMBER” AS number_,
ADR.ZIPCODE AS zipcode,
ADR.COUNTRYNAME AS countryname,
ADR.CITYNAME AS cityname,
ADR.COUNTYNAME AS countyname,
ADR.DISTRICTNAME AS districtname,
ADR.COUNTRYID AS countryid,
ADR.CITYID AS cityid,
ADR.COUNTYID AS countyid,
ADR.DISTRICTID AS districtid,
ADR.LATITUDE AS latitude,
ADR.LONGITUDE AS longitude
FROM TRADING_LINK TL
JOIN TRADING_SALES_CUSTOMER TSC
ON TSC.LINKID = TL.LINKID
LEFT JOIN CONTACTCC_CONTACT C
ON C.CONTACTID = TL.CONTACTID
LEFT JOIN CONTACTCC_ADDRESS ADR
ON ADR.ADDRESSID = C.DEFAULTADDRESSID
WHERE 1=1
“””
FILTER_SQL = “””
AND (:sales_manager_id IS NULL OR TSC.SALESMANAGERID = :sales_manager_id)
AND (:status_id IS NULL OR TL.STATUSID = :status_id)
AND (:name IS NULL OR LOWER(TL.NAME) LIKE LOWER(:name_like))
AND (:city IS NULL OR LOWER(NVL(ADR.CITYNAME,”)) LIKE LOWER(:city_like))
AND (:county IS NULL OR LOWER(NVL(ADR.COUNTYNAME,”)) LIKE LOWER(:county_like))
AND (:district IS NULL OR LOWER(NVL(ADR.DISTRICTNAME,”)) LIKE LOWER(:district_like))
“””
def _sql(with_order: bool) -> str:
order = ” ORDER BY TL.NAME ASC ” if with_order else “”
return f”{BASE_SQL}\n{FILTER_SQL}\n{order}\n”
def _make_xlsx(headers: list[str], rows: list[dict], sheet_name: str) -> 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]:
v = rr.get(h)
if v is None:
continue
s = str(v)
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()
@router.get(“”)
def musteriler_liste(
sales_manager_id: int | None = None,
status_id: int | None = None,
name: str | None = None,
city: str | None = None,
county: str | None = None,
district: 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
name = (name or “”).strip() or None
city = (city or “”).strip() or None
county = (county or “”).strip() or None
district = (district or “”).strip() or None
params = {
“sales_manager_id”: sales_manager_id,
“status_id”: status_id,
“name”: name,
“name_like”: f”%{name}%” if name else None,
“city”: city,
“city_like”: f”%{city}%” if city else None,
“county”: county,
“county_like”: f”%{county}%” if county else None,
“district”: district,
“district_like”: f”%{district}%” if district else None,
}
total_row = fetch_one(f”SELECT COUNT(1) AS total FROM ( {_sql(False)} )”, params)
total = int(total_row[“total”]) if total_row and total_row.get(“total”) is not None else 0
params_paged = dict(params)
params_paged[“offset”] = (page – 1) * page_size
params_paged[“limit”] = page_size
paged_sql = f”””
SELECT * FROM (
{_sql(True)}
)
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
“””
rows = fetch_all(paged_sql, params_paged)
return {“page”: page, “page_size”: page_size, “total”: total, “rows”: rows}
except Exception as e:
return JSONResponse(status_code=500, content={“error”: str(e)})
@router.get(“/export.xlsx”)
def musteriler_export(
sales_manager_id: int | None = None,
status_id: int | None = None,
name: str | None = None,
city: str | None = None,
county: str | None = None,
district: str | None = None,
):
try:
name = (name or “”).strip() or None
city = (city or “”).strip() or None
county = (county or “”).strip() or None
district = (district or “”).strip() or None
params = {
“sales_manager_id”: sales_manager_id,
“status_id”: status_id,
“name”: name,
“name_like”: f”%{name}%” if name else None,
“city”: city,
“city_like”: f”%{city}%” if city else None,
“county”: county,
“county_like”: f”%{county}%” if county else None,
“district”: district,
“district_like”: f”%{district}%” if district else None,
}
rows = fetch_all(_sql(True), params)
headers = [
“linkid”, “customerid”, “salesmanagerid”, “salesteamid”,
“name”, “statusid”, “isactive”,
“risk”, “risklimit”, “availablerisklimit”,
“datecreated”, “datemodified”,
“countryname”, “cityname”, “countyname”, “districtname”,
“body”, “avenue”, “parish”, “street”, “number_”, “zipcode”,
“defaultaddressid”, “addressid”,
“logisticsregionid”, “regionid”, “mainwarehouseid”,
]
data = _make_xlsx(headers, rows, “Musteriler”)
filename = f”musteriler_{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)})
{% extends “base.html” %}
{% block title %}Müşteriler Raporu{% endblock %}
{% block page_title %}Müşteriler Raporu{% endblock %}
{% block nav_musteriler_active %}active{% endblock %}
{% block head_extra %}
{% endblock %}
{% block content %}
Cencora
Yükleniyor…
Müşteriler Raporu
–
DB kontrol ediliyor…
–
–
Filtreler
Filtreleri doldur → Raporu Çalıştır. Excel, filtreli tüm satırları indirir.
Müşteri adı / kodu / telefon ile ara → Raporu Çalıştır. Excel filtreli indirir.
0 / 0
Gruplama
–
Özet
Aktif / Pasif / Sayfa içi
–
NAME
CUSTOMERID
STATUS
CITY
COUNTY
DISTRICT
ADDRESS
RISK_LIMIT
RISK
AVAILABLE_LIMIT
DATECREATED
DATEMODIFIED
Henüz veri yok.
{% endblock %}
{% block scripts %}
{% endblock %}
# app/main.py
import os
from datetime import date, datetime, timedelta
from urllib.parse import urljoin
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse, RedirectResponse
from fastapi.templating import Jinja2Templates
from fastapi.staticfiles import StaticFiles
from dotenv import load_dotenv
from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter
from app.db import fetch_all, fetch_one
# ✅ EKLENDİ: musteriler router (app/musteriler.py)
from app.musteriler import router as musteriler_router
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)
# ✅ STATIC MOUNT (base.html url_for(‘static’, …) için şart)
STATIC_DIR = os.path.join(BASE_DIR, “static”)
if os.path.isdir(STATIC_DIR):
app.mount(“/static”, StaticFiles(directory=STATIC_DIR), name=”static”)
# ✅ EKLENDİ: /api/musteriler (musteriler.py router)
app.include_router(musteriler_router, prefix=”/api/musteriler”, tags=[“Müşteriler”])
# ============================================================
# ✅ AUTO LOGIN (EXCEL) – DB YOK, python-multipart YOK
# ============================================================
AUTO_LOGIN_XLSX = os.getenv(“EXCEL_LOGIN_PATH”, os.path.join(BASE_DIR, “app”, “data”, “users.xlsx”))
# Domain seçimi (whitelist)
AUTO_LOGIN_DOMAINS = {
“firsatgunleri”: “https://firsatgunleri.alliance-healthcare.com.tr/”,
“esiparisv2”: “https://esiparisv2.alliance-healthcare.com.tr/”,
}
# Login POST ayarları (gerekirse düzelt)
# NOT: Bu action path’i ve field name’leri hedef sitenin login formuna göre değişir.
# Chrome F12 -> Network -> login isteğinde “Request URL” ve “Form Data”dan bulup burayı düzelt.
AUTO_LOGIN_CONFIG = {
# iki domain aynı site klon dediğin için tek config kullandım
“LOGIN_ACTION”: os.getenv(“AUTO_LOGIN_ACTION”, “/Account/Login”), # örnek, gerekirse değiştir
“METHOD”: os.getenv(“AUTO_LOGIN_METHOD”, “POST”),
“USER_FIELD”: os.getenv(“AUTO_LOGIN_USER_FIELD”, “Username”), # örnek, gerekirse değiştir
“PASS_FIELD”: os.getenv(“AUTO_LOGIN_PASS_FIELD”, “Password”), # örnek, gerekirse değiştir
# ekstra hidden alan gerekiyorsa buraya ekle:
“EXTRA_FIELDS”: {
# “RememberMe”: “true”,
# “__RequestVerificationToken”: “…..” # token gerekiyorsa auto-post ile olmayabilir (aşağıda not var)
},
}
def _safe_domain_key(domain_key: str) -> str:
domain_key = (domain_key or “”).strip().lower()
if domain_key not in AUTO_LOGIN_DOMAINS:
raise ValueError(“Geçersiz domain seçimi.”)
return domain_key
def _read_users_from_xlsx(xlsx_path: str) -> list[dict]:
“””
users.xlsx beklenen kolonlar (başlıklar esnek):
– kod / musteri_kodu / hedefkodu / customer_code
– kullanici / username / user
– sifre / password / pass
“””
if not os.path.isfile(xlsx_path):
return []
wb = load_workbook(xlsx_path, data_only=True)
ws = wb.active
rows = list(ws.iter_rows(values_only=True))
if not rows:
return []
header = [str(x).strip().lower() if x is not None else “” for x in rows[0]]
def find_col(*cands: str) -> int | None:
for cand in cands:
cand = cand.lower()
if cand in header:
return header.index(cand)
return None
c_kod = find_col(“kod”, “musteri_kodu”, “müşteri_kodu”, “hedefkodu”, “customer_code”)
c_user = find_col(“kullanici”, “kullanıcı”, “username”, “user”, “mail”, “email”)
c_pass = find_col(“sifre”, “şifre”, “password”, “pass”, “parola”)
# Eğer başlık yoksa, 1-2-3 kolon varsayımı (kod, user, pass)
if c_kod is None and c_user is None and c_pass is None:
c_kod, c_user, c_pass = 0, 1, 2
out: list[dict] = []
for r in rows[1:]:
if r is None:
continue
kod = str(r[c_kod]).strip() if c_kod is not None and c_kod < len(r) and r[c_kod] is not None else ""
user = str(r[c_user]).strip() if c_user is not None and c_user < len(r) and r[c_user] is not None else ""
pw = str(r[c_pass]).strip() if c_pass is not None and c_pass < len(r) and r[c_pass] is not None else ""
if kod and user and pw:
out.append({"kod": kod, "username": user, "password": pw})
return out
def _find_user_cred(kod: str) -> dict | None:
kod = (kod or “”).strip()
if not kod:
return None
users = _read_users_from_xlsx(AUTO_LOGIN_XLSX)
for u in users:
if str(u.get(“kod”)) == kod:
return u
return None
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
“””
# =========================
# ✅ PAGES
# =========================
@app.get(“/”, include_in_schema=False)
def root():
return RedirectResponse(url=”/rapor”)
@app.get(“/base”, response_class=HTMLResponse)
def base_page(request: Request):
return templates.TemplateResponse(“base.html”, {“request”: request})
@app.get(“/rapor”, response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse(“rapor.html”, {“request”: request})
@app.get(“/rapor2”, response_class=HTMLResponse)
def rapor2_page(request: Request):
return templates.TemplateResponse(“rapor2.html”, {“request”: request})
@app.get(“/rapor_pesin”, response_class=HTMLResponse)
def rapor_pesin_page(request: Request):
return templates.TemplateResponse(“rapor_pesin.html”, {“request”: request})
# ✅ EKLENDİ: Müşteriler sayfası (templates/musteriler.html)
@app.get(“/musteriler”, response_class=HTMLResponse)
def musteriler_page(request: Request):
return templates.TemplateResponse(“musteriler.html”, {“request”: request})
# ============================================================
# ✅ EKLENDİ: AUTO LOGIN SAYFALARI
# ============================================================
@app.get(“/auto_login”, response_class=HTMLResponse)
def auto_login_page(request: Request):
“””
Domain seç + müşteri kodu gir -> /auto_login/go
“””
# Exceldeki kodları dropdown yapmak istersen:
users = _read_users_from_xlsx(AUTO_LOGIN_XLSX)
kodlar = sorted({u[“kod”] for u in users})[:2000] # çoksa sınırladım
return templates.TemplateResponse(
“auto_login.html”,
{
“request”: request,
“domains”: AUTO_LOGIN_DOMAINS,
“kodlar”: kodlar,
“xlsx_path”: AUTO_LOGIN_XLSX,
},
)
@app.get(“/auto_login/go”, response_class=HTMLResponse)
def auto_login_go(
request: Request,
domain: str,
kod: str,
):
“””
Excel’den kullanıcı/şifre bulur, seçilen domaine auto-submit yapar.
“””
try:
domain_key = _safe_domain_key(domain)
except Exception as e:
return HTMLResponse(f”
Hata
{str(e)}
“, status_code=400)
cred = _find_user_cred(kod)
if not cred:
return HTMLResponse(
f”
Kayıt bulunamadı
Excel içinde bu kod yok: {kod} Dosya: {AUTO_LOGIN_XLSX}
“,
status_code=404,
)
base_url = AUTO_LOGIN_DOMAINS[domain_key]
action = urljoin(base_url, AUTO_LOGIN_CONFIG[“LOGIN_ACTION”])
# HTML: hidden form + otomatik submit
user_field = AUTO_LOGIN_CONFIG[“USER_FIELD”]
pass_field = AUTO_LOGIN_CONFIG[“PASS_FIELD”]
method = (AUTO_LOGIN_CONFIG.get(“METHOD”) or “POST”).upper()
extras = AUTO_LOGIN_CONFIG.get(“EXTRA_FIELDS”) or {}
html = f”””
Otomatik Giriş
AUTO LOGIN
Seçilen Domain:{base_url}
Müşteri Kodu:{cred[“kod”]}
Action:{action}
Birazdan otomatik giriş denenecek…
Eğer login olmuyorsa: hedef sitenin login formundaki action URL ve input name değerlerini
F12 → Network üzerinden kontrol edip main.py içindeki AUTO_LOGIN_CONFIG bölümünü güncelle.
Not: Bazı siteler CSRF token istiyorsa (RequestVerificationToken gibi) bu yöntem çalışmayabilir.
O durumda önce login sayfasını açıp token alarak (requests ile) session yönetmek gerekir.
“””
return HTMLResponse(html)
# =========================
# ✅ HEALTH
# =========================
@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)})
# ————————————————————
# AŞAĞIDAN SONRASI: SENİN MEVCUT RAPOR / EXPORT / RAPOR2 / PEŞİN
# KODLARIN AYNEN DEVAM EDİYOR (DEĞİŞTİRMEDİM)
# ————————————————————
# (Buradan sonra senin attığın dosyadaki build_params, /api/rapor, /api/rapor2,
# /api/rapor_pesin vs. aynı şekilde devam etmeli.)
{# templates/auto_login.html #}
{% extends “base.html” %}
{% block title %}Otomatik Giriş{% endblock %}
{% block page_title %}Otomatik Giriş{% endblock %}
{# Eğer base.html içinde menü active alanların varsa kendine göre ekleyebilirsin #}
{# {% block nav_auto_login_active %}active{% endblock %} #}
{% block head_extra %}
{% endblock %}
{% block content %}
Excel’den Otomatik Giriş
Excel dosyasından seçtiğin müşterinin bilgileriyle seçilen domaine tarayıcı açılır, alanlar doldurulur ve “Giriş Yap” tıklanır.
URL: /auto-login
DB Yok
Playwright
{# Hata / Başarı Mesajları #}
{% if error %}
Hata: {{ error }}
Excel yolu:EXCEL_LOGIN_PATH (env) veya /data/login_users.xlsx
{% endif %}
{% if result %}
Başarılı: {{ result }}
{% endif %}
Not: Bu işlem tarayıcıyı (Chromium) açar. Sunucu Windows’ta çalışıyorsa ekranda görürsün.
Sunucu farklı makinedeyse, tarayıcı o makinede açılır.
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.
SELECT
MCOT.TIMESTARTS AS BASLANGIC_TARIHI,
MCOT.TIMEENDS AS BITIS_TARIHI,
TL.LINKID,
HDFKOD.HEDEFKODU,
TL.NAME AS HESAPADI,
CA.DISTRICTNAME AS SEMT,
CA.CITYNAME AS SEHIR,
MCOT.NAME AS KAMPANYA
FROM MARKETING_CAMP_ACTIVITY MCA
JOIN MARKETING_CAMP_OTEMP MCOT
ON MCA.CAMPAIGNID = MCOT.ACTIVITYID
JOIN TRD_SAL_OFFRTMPLTCUSTOMER TSO
ON TSO.OFFERTEMPLATEID = MCOT.OFFERTEMPLATEID
JOIN TRADING_LINK TL
ON TL.LINKID = TSO.CUSTOMERID
JOIN CONTACTCC_CONTACT CC
ON TL.CONTACTID = CC.CONTACTID
JOIN CONTACTCC_ADDRESS CA
ON CC.DEFAULTADDRESSID = CA.ADDRESSID
JOIN (
SELECT /*+ MATERIALIZE */
CTPV.TITLEID,
CAST(CPV.VALUETEXTTUR AS NUMBER) AS HEDEFKODU
FROM CONTENTCC_C_TPROPERTYVALUE CTPV
JOIN CONTENTCC_C_PROPERTYVALUE CPV
ON CTPV.PROPERTYVALUEID = CPV.PROPERTYVALUEID
WHERE CPV.PROPERTYID = 19
) HDFKOD
ON HDFKOD.TITLEID = TL.TITLEID
WHERE
MCA.ACTIVITYID = 3
/* AND TSO.OFFERTEMPLATEID = 21 */ — Peşin statü lazımsa açın
AND MCOT.TIMEENDS >= TRUNC(SYSDATE) — Aktif kampanyalar
AND SUBSTR(MCOT.NAME, 1, 3) IN (‘005′,’006′,’007′,’008′,’015′,’044′,’045′,’046′,’047’);
///////////
{% extends “base.html” %}
{% block title %}Lokasyonsuz Ürünler{% endblock %}
{% block content %}
Lokasyonsuz Ürünler
–
–
–
–
URUNKODU
URUNADI
DEPO
LOKASYON
STOK
BASKIN_SINIF
Henüz veri yok.
{% endblock %}
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 os
from datetime import date, datetime, timedelta
from fastapi import FastAPI, Query, Request
from fastapi.responses import HTMLResponse, JSONResponse, StreamingResponse, RedirectResponse
from fastapi.templating import Jinja2Templates
from fastapi.staticfiles import StaticFiles
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″)
# ============================================================
# ✅ TEMPLATE / STATIC PATH
# ============================================================
APP_DIR = os.path.dirname(os.path.abspath(__file__)) # …/app
PROJECT_DIR = os.path.abspath(os.path.join(APP_DIR, “..”)) # proje kökü
CANDIDATE_TEMPLATE_DIRS = [
os.path.join(PROJECT_DIR, “templates”),
os.path.join(APP_DIR, “templates”),
]
TEMPLATES_DIR = next((p for p in CANDIDATE_TEMPLATE_DIRS if os.path.isdir(p)), None)
if not TEMPLATES_DIR:
raise RuntimeError(
“templates klasörü bulunamadı. Şunlardan birini oluştur:\n”
f”- {os.path.join(PROJECT_DIR, ‘templates’)}\n”
f”- {os.path.join(APP_DIR, ‘templates’)}”
)
templates = Jinja2Templates(directory=TEMPLATES_DIR)
CANDIDATE_STATIC_DIRS = [
os.path.join(PROJECT_DIR, “static”),
os.path.join(APP_DIR, “static”),
]
STATIC_DIR = next((p for p in CANDIDATE_STATIC_DIRS if os.path.isdir(p)), None)
if STATIC_DIR:
app.mount(“/static”, StaticFiles(directory=STATIC_DIR), name=”static”)
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
“””
# =========================
# ✅ PAGES
# =========================
@app.get(“/”, include_in_schema=False)
def root():
return RedirectResponse(url=”/base”, status_code=302)
@app.get(“/base”, response_class=HTMLResponse)
def base_page(request: Request):
return templates.TemplateResponse(“base.html”, {“request”: request})
@app.get(“/rapor”, response_class=HTMLResponse)
def rapor_page(request: Request):
return templates.TemplateResponse(“rapor.html”, {“request”: request})
@app.get(“/rapor2”, response_class=HTMLResponse)
def rapor2_page(request: Request):
return templates.TemplateResponse(“rapor2.html”, {“request”: request})
# =========================
# ✅ HEALTH
# =========================
@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,
}
# =========================
# ✅ RAPOR 1 API
# =========================
@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
# ============================================================
@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 olmayan ürünler)
# ============================================================
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)})
# ============================================================
# ✅ FRONTEND UYUMLULUK ALIASLARI (API ERROR ÇÖZÜMÜ)
# ============================================================
# Frontend bazı sayfalarda şunları çağırıyor olabilir:
# /api/products/no-location
# /api/products/no-location/export.xlsx
# Senin backend’de karşılığı zaten /api/rapor2 ve /api/rapor2/export.xlsx
# Bu yüzden alias ekledik.
@app.get(“/api/products/no-location”)
def products_no_location_alias(
urunkodu: str | None = None,
urunadi: str | None = None,
depo: str | None = None,
baskin_sinif: str | None = None,
page: int = 1,
page_size: int = 50,
):
return rapor2_data(
urunkodu=urunkodu,
urunadi=urunadi,
depo=depo,
baskin_sinif=baskin_sinif,
page=page,
page_size=page_size,
)
@app.get(“/api/products/no-location/export.xlsx”)
def products_no_location_export_alias(
urunkodu: str | None = None,
urunadi: str | None = None,
depo: str | None = None,
baskin_sinif: str | None = None,
):
return rapor2_export_xlsx(
urunkodu=urunkodu,
urunadi=urunadi,
depo=depo,
baskin_sinif=baskin_sinif,
)
{% extends “base.html” %}
{% block title %}Lokasyonu Olmayan Ürünler{% endblock %}
{% block page_title %}Lokasyonu Olmayan Ürünler{% endblock %}
{# base menüde aktif olsun diye (base buna bakıyorsa) #}
{% block nav_rapor2_active %}active{% endblock %}
{% block head_extra %}
{% endblock %}
{% block content %}
Cencora
Yükleniyor…
Lokasyonu Olmayan Ürünler
–
Filtreler
Ara → Getir. Excel tüm filtreli satırları indirir.
