{# DATA (grouped) #}
{% for section in rows_grouped or [] %}
{{ section.group_name }}
{% for r in section.items %}
{# KPI class example: green if >=100, amber if 85-99, red if <85 #}
{% set ilac_kpi = 'kpi-ok' if (r.ilac_rea|float) >= 100 else (‘kpi-warn’ if (r.ilac_rea|float) >= 85 else ‘kpi-bad’) %}
{% set disi_kpi = ‘kpi-ok’ if (r.ilacdisi_rea|float) >= 100 else (‘kpi-warn’ if (r.ilacdisi_rea|float) >= 85 else ‘kpi-bad’) %}
{% set depo_kpi = ‘kpi-ok’ if (r.depo_rea|float) >= 100 else (‘kpi-warn’ if (r.depo_rea|float) >= 85 else ‘kpi-bad’) %}
{{ r.kod }}
{{ r.satici }}
{{ r.ilac_hedef }}
{{ r.ilac_satis }}
{{ r.ilac_rea }}
{{ r.ilacdisi_hedef }}
{{ r.ilacdisi_satis }}
{{ r.ilacdisi_rea }}
{{ r.depo_hedef }}
{{ r.depo_satis }}
{{ r.depo_rea }}
{{ r.ilac_adet }}
{{ r.ilacdisi_adet }}
{% endfor %}
{% endfor %}
{# EMPTY STATE #}
{% if not rows_grouped %}
Kayıt bulunamadı. Filtreleri değiştirip tekrar deneyin.
{% endif %}
{% endblock %}
# 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”,
“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,
)
# =============================================================================
# REALIZE PAGE
# =============================================================================
@app.get(“/realize”, response_class=HTMLResponse)
def realize_page(
request: Request,
start: date = Query(default_factory=date.today),
end: date = Query(default_factory=date.today),
seller: str | None = None,
group: str | None = None,
status: str | None = None,
):
# Şimdilik UI açılsın diye dummy veriler
q = {
“start”: str(start) if start else “”,
“end”: str(end) if end else “”,
“seller”: (seller or “”),
“group”: (group or “”),
“status”: (status or “”),
}
rows_grouped = [] # sonra SQL’den gelecek
totals = {
“ilac_hedef”: 0,
“ilac_satis”: 0,
“ilac_rea”: 0,
“ilacdisi_hedef”: 0,
“ilacdisi_satis”: 0,
“ilacdisi_rea”: 0,
“depo_hedef”: 0,
“depo_satis”: 0,
“depo_rea”: 0,
“ilac_adet”: 0,
“ilacdisi_adet”: 0,
}
groups = [] # dropdown için grup listesi
return templates.TemplateResponse(
“realize.html”,
{
**base_context(request),
“q”: q,
“rows_grouped”: rows_grouped,
“totals”: totals,
“groups”: groups,
“last_updated”: datetime.now().strftime(“%d.%m.%Y %H:%M”),
“period_label”: “OCAK”,
“total_rows”: 0,
“export_url”: “#”,
},
)
satıcı sql
SELECT “COLLABORATION_NODE”.*, “TRADING_POINT”.*, “TRADING_SALES_POS”.*, “STATUS”.”NAMETUR” AS STATUSNAMETUR, “MARKETINGCHANNEL”.”NAMETUR” AS MARKETINGCHANNELNAMETUR FROM “COLLABORATION_NODE” INNER JOIN “TRADING_POINT” ON “COLLABORATION_NODE”.”NODEID” = “TRADING_POINT”.”NODEID” INNER JOIN “TRADING_SALES_POS” ON “TRADING_POINT”.”NODEID” = “TRADING_SALES_POS”.”POINTID” LEFT JOIN “FRAMEWORK_STATUS” “STATUS” ON “STATUS”.”STATUSID” = “COLLABORATION_NODE”.”STATUSID” LEFT JOIN “MARKETING_CHANNEL” “MARKETINGCHANNEL” ON “MARKETINGCHANNEL”.”CHANNELID” = “TRADING_POINT”.”MARKETINGCHANNELID” WHERE “TRADING_SALES_POS”.”SALESMANAGERID” = :Parameter_2 AND “COLLABORATION_NODE”.”STATUSID” IN (13) AND “TRADING_SALES_POS”.”POSTYPE” IN (1) ORDER BY “COLLABORATION_NODE”.”SORTORDER” ASC DECLARE :Parameter_2 AS Decimal = 9;
SELECT
cn.NODEID AS SELLER_ID,
cn.*,
tp.*,
tsp.*,
st.NAMETUR AS STATUSNAMETUR,
mc.NAMETUR AS MARKETINGCHANNELNAMETUR
FROM COLLABORATION_NODE cn
JOIN TRADING_POINT tp
ON cn.NODEID = tp.NODEID
JOIN TRADING_SALES_POS tsp
ON tp.NODEID = tsp.POINTID
LEFT JOIN FRAMEWORK_STATUS st
ON st.STATUSID = cn.STATUSID
LEFT JOIN MARKETING_CHANNEL mc
ON mc.CHANNELID = tp.MARKETINGCHANNELID
WHERE cn.STATUSID IN (13)
AND tsp.POSTYPE IN (1)
AND cn.NODEID IN (
SELECT TO_NUMBER(REGEXP_SUBSTR(:seller_ids, ‘[^,]+’, 1, LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:seller_ids, ‘[^,]+’, 1, LEVEL) IS NOT NULL
)
ORDER BY cn.SORTORDER ASC
SELECT
cn.”NODEID” AS “SELLER_ID”,
cn.”NAMETUR” AS “SELLER_NAME”,
cn.”SORTORDER” AS “SORTORDER”
FROM “COLLABORATION_NODE” cn
INNER JOIN “TRADING_POINT” tp
ON cn.”NODEID” = tp.”NODEID”
INNER JOIN “TRADING_SALES_POS” tsp
ON tp.”NODEID” = tsp.”POINTID”
WHERE cn.”STATUSID” = 13 — AKTİF SATICI
AND tsp.”POSTYPE” = 1 — SATIŞ NOKTASI
ORDER BY cn.”SORTORDER” ASC
SELECT
cn.”STATUSID”,
fs.”NAMETUR” AS “STATUS_NAME”,
COUNT(*) AS “ADET”
FROM “COLLABORATION_NODE” cn
LEFT JOIN “FRAMEWORK_STATUS” fs
ON fs.”STATUSID” = cn.”STATUSID”
GROUP BY cn.”STATUSID”, fs.”NAMETUR”
ORDER BY COUNT(*) DESC
satış
SELECT Trading_Link.Name NAME, (SELECT DISTINCT ContactCC_Address.DistrictName FROM ContactCC_Address ,Trading_Link WHERE Trading_Link.LinkID=Trading_Sales_Customer.LinkID AND Trading_Link.ContactID=ContactCC_Address.ContactID AND IsDefault = 1 AND ROWNUM<=1 ) AS SEMT,(SELECT DISTINCT ContactCC_Address.CountyName FROM ContactCC_Address ,Trading_Link WHERE Trading_Link.LinkID=Trading_Sales_Customer.LinkID AND Trading_Link.ContactID=ContactCC_Address.ContactID AND IsDefault = 1 AND ROWNUM<=1 ) AS ILCE,COALESCE(Trading_Link.LinkCode,' ') AS HEDEFKODU,Trading_Sales_Customer.LinkID ID, Trading_Sales_Customer.Code AccountCode, Trading_Link.GlobalIdentity GLN, COALESCE((SELECT NameTUR FROM Collaboration_Node WHERE NodeID = Trading_Sales_Customer.PosID),' ') POS, COALESCE((SELECT NameTUR FROM Collaboration_Node WHERE NodeID = Trading_Sales_Customer.SalesClerkID),' ') SALESCLERK, COALESCE((SELECT NameTUR FROM Collaboration_Node WHERE NodeID = COALESCE(Trading_Sales_Customer.CollectorID,0)),' ') COLLECTOR, SUM((Finance_Invoice.InnerType-2)*(-1)*Finance_InvoiceItem.TotalAfterDiscounts) TOTALAFTERDISCOUNTS, SUM((Finance_Invoice.InnerType-3)*(-1)*Finance_InvoiceItem.TotalAfterDiscounts/2) ATOTALAFTERDISCOUNTS, Coalesce( sum(Case when Finance_Invoice.InnerType=1 And Finance_InvoiceItem.ItemID>0 then (Case When Coalesce(TO_NUMBER(Finance_InvoiceItem.PriceLevel),0)=0 then Finance_InvoiceItem.TotalAfterDiscounts else 0 end ) end ),0) HKDM0 ,Coalesce( sum(Case when Finance_Invoice.InnerType=1 And Finance_InvoiceItem.ItemID>0 and Coalesce(TO_NUMBER(Finance_InvoiceItem.PriceLevel),0)>0 then (Case When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier<10.90 Then Finance_InvoiceItem.TotalAfterDiscounts When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=10.90 Then(Finance_InvoiceItem.NetPrice*(round((10.9/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) else 0 end ) end ),0) HKDM1 ,Coalesce( sum(Case when Finance_Invoice.InnerType=1 And Finance_InvoiceItem.ItemID>0 and Coalesce(TO_NUMBER(Finance_InvoiceItem.PriceLevel),0)>0 then (Case When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier<54.1 And Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=10.9 then (Finance_InvoiceItem.NetPrice*(round((((Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)-10.9)/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=54.1 And Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=10.9 then (Finance_InvoiceItem.NetPrice*(round((43.2/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) else 0 end ) end ),0) HKDM2 ,Coalesce( sum(Case when Finance_Invoice.InnerType=1 And Finance_InvoiceItem.ItemID>0 and Coalesce(TO_NUMBER(Finance_InvoiceItem.PriceLevel),0)>0 then (Case When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier<107.6 And Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=54.1 then (Finance_InvoiceItem.NetPrice*(round((((Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)-54.1)/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=107.6 And Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=54.1 then (Finance_InvoiceItem.NetPrice*(round((53.5/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) else 0 end ) end ),0) HKDM3 ,Coalesce( sum(Case when Finance_Invoice.InnerType=1 And Finance_InvoiceItem.ItemID>0 and Coalesce(TO_NUMBER(Finance_InvoiceItem.PriceLevel),0)>0 then (Case When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier<211.6 And Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=107.6 then (Finance_InvoiceItem.NetPrice*(round((((Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)-107.6)/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=211.6 And Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=107.6 then (Finance_InvoiceItem.NetPrice*(round((104/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) else 0 end ) end ),0) HKDM4 ,Coalesce( sum(Case when Finance_Invoice.InnerType=1 And Finance_InvoiceItem.ItemID>0 and Coalesce(TO_NUMBER(Finance_InvoiceItem.PriceLevel),0)>0 then (Case When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=211.6 then (Finance_InvoiceItem.NetPrice*(round((((Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)-211.6)/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) else 0 end ) end ),0) HKDM5 , Coalesce( sum(Case when Finance_Invoice.InnerType=3 And Finance_InvoiceItem.ItemID>0 then (Case When Coalesce(TO_NUMBER(Finance_InvoiceItem.PriceLevel),0)=0 then Finance_InvoiceItem.TotalAfterDiscounts else 0 end ) end ),0) AHKDM0 ,Coalesce( sum(Case when Finance_Invoice.InnerType=3 And Finance_InvoiceItem.ItemID>0 and Coalesce(TO_NUMBER(Finance_InvoiceItem.PriceLevel),0)>0 then (Case When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier<10.90 Then Finance_InvoiceItem.TotalAfterDiscounts When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=10.90 Then(Finance_InvoiceItem.NetPrice*(round((10.9/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) else 0 end ) end ),0) AHKDM1 ,Coalesce( sum(Case when Finance_Invoice.InnerType=3 And Finance_InvoiceItem.ItemID>0 and Coalesce(TO_NUMBER(Finance_InvoiceItem.PriceLevel),0)>0 then (Case When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier<54.1 And Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=10.9 then (Finance_InvoiceItem.NetPrice*(round((((Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)-10.9)/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=54.1 And Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=10.9 then (Finance_InvoiceItem.NetPrice*(round((43.2/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) else 0 end ) end ),0) AHKDM2 ,Coalesce( sum(Case when Finance_Invoice.InnerType=3 And Finance_InvoiceItem.ItemID>0 and Coalesce(TO_NUMBER(Finance_InvoiceItem.PriceLevel),0)>0 then (Case When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier<107.6 And Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=54.1 then (Finance_InvoiceItem.NetPrice*(round((((Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)-54.1)/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=107.6 And Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=54.1 then (Finance_InvoiceItem.NetPrice*(round((53.5/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) else 0 end ) end ),0) AHKDM3 ,Coalesce( sum(Case when Finance_Invoice.InnerType=3 And Finance_InvoiceItem.ItemID>0 and Coalesce(TO_NUMBER(Finance_InvoiceItem.PriceLevel),0)>0 then (Case When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier<211.6 And Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=107.6 then (Finance_InvoiceItem.NetPrice*(round((((Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)-107.6)/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=211.6 And Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=107.6 then (Finance_InvoiceItem.NetPrice*(round((104/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) else 0 end ) end ),0) AHKDM4 ,Coalesce( sum(Case when Finance_Invoice.InnerType=3 And Finance_InvoiceItem.ItemID>0 and Coalesce(TO_NUMBER(Finance_InvoiceItem.PriceLevel),0)>0 then (Case When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=211.6 then (Finance_InvoiceItem.NetPrice*(round((((Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)-211.6)/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100) *(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) else 0 end ) end ),0) AHKDM5 ,Coalesce( sum(Case when Finance_Invoice.InnerType=1 And Finance_InvoiceItem.ItemID=0 then Finance_InvoiceItem.TotalAfterDiscounts Else 0 End) ,0) HSRV ,Coalesce( sum(Case when Finance_Invoice.InnerType=3 And Finance_InvoiceItem.ItemID=0 then Finance_InvoiceItem.TotalAfterDiscounts Else 0 End) ,0) AHSRV FROM Finance_Invoice Inner join Finance_InvoiceItem on Finance_InvoiceItem.InvoiceID=Finance_Invoice.InvoiceID Inner join Trading_Link on Trading_Link.LinkID= Finance_InvoiceItem.LinkID Inner join Trading_Sales_Customer on Trading_Sales_Customer.LinkID=Finance_InvoiceItem.LinkID And Trading_Sales_Customer.SalesManagerID=9WHERE Finance_Invoice.DateSent>=TO_DATE(‘01.01.2026 00:00:00′,’DD.MM.YYYY HH24:MI:SS’) AND Finance_Invoice.DateSent<=TO_DATE('29.01.2026 23:59:59','DD.MM.YYYY HH24:MI:SS') AND Coalesce(Finance_InvoiceItem.ItemID,0)>0 AND Finance_Invoice.Type IN (1,3) AND Finance_Invoice.StatusID=21 AND Finance_InvoiceItem.Price>0 GROUP BY Trading_Sales_Customer.LinkID,Trading_Link.Name,Trading_Sales_Customer.Code,COALESCE(Trading_Link.LinkCode,’ ‘) ,Trading_Link.GlobalIdentity,Trading_Sales_Customer.PosID,Trading_Sales_Customer.SalesClerkID,COALESCE(Trading_Sales_Customer.CollectorID,0) Order BY Name, Trading_Sales_Customer.LinkID
SELECT
Trading_Link.Name NAME,
(SELECT DISTINCT ContactCC_Address.DistrictName
FROM ContactCC_Address, Trading_Link
WHERE Trading_Link.LinkID = Trading_Sales_Customer.LinkID
AND Trading_Link.ContactID = ContactCC_Address.ContactID
AND IsDefault = 1
AND ROWNUM <= 1
) AS SEMT,
(SELECT DISTINCT ContactCC_Address.CountyName
FROM ContactCC_Address, Trading_Link
WHERE Trading_Link.LinkID = Trading_Sales_Customer.LinkID
AND Trading_Link.ContactID = ContactCC_Address.ContactID
AND IsDefault = 1
AND ROWNUM <= 1
) AS ILCE,
COALESCE(Trading_Link.LinkCode,' ') AS HEDEFKODU,
Trading_Sales_Customer.LinkID ID,
Trading_Sales_Customer.Code AccountCode,
Trading_Link.GlobalIdentity GLN,
COALESCE((SELECT NameTUR FROM Collaboration_Node WHERE NodeID = Trading_Sales_Customer.PosID),' ') POS,
COALESCE((SELECT NameTUR FROM Collaboration_Node WHERE NodeID = Trading_Sales_Customer.SalesClerkID),' ') SALESCLERK,
COALESCE((SELECT NameTUR FROM Collaboration_Node WHERE NodeID = COALESCE(Trading_Sales_Customer.CollectorID,0)),' ') COLLECTOR,
SUM((Finance_Invoice.InnerType-2)*(-1)*Finance_InvoiceItem.TotalAfterDiscounts) TOTALAFTERDISCOUNTS,
SUM((Finance_Invoice.InnerType-3)*(-1)*Finance_InvoiceItem.TotalAfterDiscounts/2) ATOTALAFTERDISCOUNTS,
Coalesce( sum(Case when Finance_Invoice.InnerType=1 And Finance_InvoiceItem.ItemID>0 then
(Case When Coalesce(TO_NUMBER(Finance_InvoiceItem.PriceLevel),0)=0 then Finance_InvoiceItem.TotalAfterDiscounts else 0 end )
end ),0) HKDM0,
Coalesce( sum(Case when Finance_Invoice.InnerType=1 And Finance_InvoiceItem.ItemID>0
and Coalesce(TO_NUMBER(Finance_InvoiceItem.PriceLevel),0)>0 then
(Case
When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier<10.90 Then Finance_InvoiceItem.TotalAfterDiscounts
When Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier>=10.90 Then
(Finance_InvoiceItem.NetPrice*(round((10.9/(Finance_InvoiceItem.Price*Finance_InvoiceItem.AssemblyMultiplier)*100),10))/100)
*(Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity)
else 0
end )
end ),0) HKDM1,
— (devamı aynı bırakıyorum: HKDM2..HKDM5, AHKDM0..AHKDM5, HSRV, AHSRV)
— Senin verdiğin ham koddaki tüm hesaplar aynen kalabilir.
Coalesce( sum(Case when Finance_Invoice.InnerType=1 And Finance_InvoiceItem.ItemID=0 then Finance_InvoiceItem.TotalAfterDiscounts Else 0 End) ,0) HSRV,
Coalesce( sum(Case when Finance_Invoice.InnerType=3 And Finance_InvoiceItem.ItemID=0 then Finance_InvoiceItem.TotalAfterDiscounts Else 0 End) ,0) AHSRV
FROM Finance_Invoice
INNER JOIN Finance_InvoiceItem
ON Finance_InvoiceItem.InvoiceID = Finance_Invoice.InvoiceID
INNER JOIN Trading_Link
ON Trading_Link.LinkID = Finance_InvoiceItem.LinkID
INNER JOIN Trading_Sales_Customer
ON Trading_Sales_Customer.LinkID = Finance_InvoiceItem.LinkID
AND Trading_Sales_Customer.SalesManagerID = 9 — ✅ boşluk eklendi, hata düzeldi
WHERE Finance_Invoice.DateSent >= TRUNC(SYSDATE,’MM’) — ✅ ayın 1’i
AND Finance_Invoice.DateSent < TRUNC(SYSDATE) -- ✅ bugün 00:00:00 (dün dahil)
AND Coalesce(Finance_InvoiceItem.ItemID,0) > 0
AND Finance_Invoice.Type IN (1,3)
AND Finance_Invoice.StatusID = 21
AND Finance_InvoiceItem.Price > 0
GROUP BY
Trading_Sales_Customer.LinkID,
Trading_Link.Name,
Trading_Sales_Customer.Code,
COALESCE(Trading_Link.LinkCode,’ ‘),
Trading_Link.GlobalIdentity,
Trading_Sales_Customer.PosID,
Trading_Sales_Customer.SalesClerkID,
COALESCE(Trading_Sales_Customer.CollectorID,0)
ORDER BY Name, Trading_Sales_Customer.LinkID
ciro
SELECT
LinkView.SalesPos,
(CASE
WHEN CI.IntegerQuantity = 1
THEN SUM(CEIL(Finance_InvoiceItem.Quantity / COALESCE(Finance_InvoiceItem.AssemblyMultiplier, 1)))
ELSE SUM(Finance_InvoiceItem.Quantity / COALESCE(Finance_InvoiceItem.AssemblyMultiplier, 1))
END) AS Quantity,
(CASE
WHEN CI.IntegerQuantity = 1
THEN SUM(CEIL(Finance_InvoiceItem.ExtraQuantity / COALESCE(Finance_InvoiceItem.AssemblyMultiplier, 1)))
ELSE SUM(Finance_InvoiceItem.ExtraQuantity / COALESCE(Finance_InvoiceItem.AssemblyMultiplier, 1))
END) AS ExtraQuantity,
SUM(Finance_InvoiceItem.PurchasingValue) AS PurchasingValue,
SUM(((Finance_InvoiceItem.Quantity + Finance_InvoiceItem.ExtraQuantity) / COALESCE(Finance_InvoiceItem.AssemblyMultiplier, 1))
* Finance_InvoiceItem.PurchasingPriceOutOfTaxes) AS PurchasingValueTotal,
SUM(Finance_InvoiceItem.SubTotal) AS SubTotal,
SUM(Finance_InvoiceItem.Profit) AS Profit,
SUM(Finance_InvoiceItem.DiscountTotal) AS DiscountTotal,
SUM(Finance_InvoiceItem.TotalAfterDiscounts) AS TotalAfterDiscounts,
SUM(Finance_InvoiceItem.TaxTotal) AS TaxTotal,
SUM(Finance_InvoiceItem.GrossTotal) AS GrossTotal,
SUM(Finance_InvoiceItem.GainedCoin) AS GainedCoin
FROM Finance_InvoiceItem
INNER JOIN (
SELECT
Finance_Invoice.InvoiceID,
Finance_Invoice.LinkID,
Finance_Invoice.ControllerID,
Finance_Invoice.DateSent,
Finance_Invoice.HasPriority,
Finance_Invoice.WarehouseID
FROM Finance_Invoice
INNER JOIN TRD_SAL_SALINVOICE
ON TRD_SAL_SALINVOICE.InvoiceID = Finance_Invoice.InvoiceID
WHERE Finance_Invoice.InnerType = 1
AND Finance_Invoice.StatusID = 21
AND Finance_Invoice.DateSent >= TRUNC(SYSDATE,’MM’) — Ayın 1’i
AND Finance_Invoice.DateSent < TRUNC(SYSDATE) -- Bugün 00:00:00 (dün dahil)
AND Finance_Invoice.Type = 1
) Finance_Invoice
ON Finance_InvoiceItem.InvoiceID = Finance_Invoice.InvoiceID
INNER JOIN (
SELECT
Catalog_Item.ItemID,
Catalog_Item.NameTur,
Catalog_Item.Code,
Catalog_Item.Barcode,
Catalog_Class.IntegerQuantity,
Catalog_Item.ManufacturerID,
Catalog_Item.HasTracing,
Catalog_Item.HarmonizedCodeID
FROM Catalog_Item
INNER JOIN Catalog_Class
ON Catalog_Class.ClassID = Catalog_Item.DominantClassID
WHERE Catalog_Item.DominantClassID IN (3,4,5,6,7,8,13,14) -- İlaç dışı sınıflar
) CI
ON CI.ItemID = Finance_InvoiceItem.ItemID
LEFT OUTER JOIN Catalog_HarmonizedCode
ON Catalog_HarmonizedCode.HarmonizedCodeID = CI.HarmonizedCodeID
INNER JOIN (
SELECT
Trading_Link.LinkID,
Trading_Link.Name LinkName,
Trading_Link.OfficialName LinkOfficialName,
Trading_Link.TitleID TITLEID,
Trading_Link.TypeID LinkTypeID,
Trading_Link.TaxNumber LinkTaxNumber,
ContactCC_Address.DistrictName LinkDistrictName,
ContactCC_Address.CityName LinkCityName,
ContactCC_Address.CityID LinkCityID,
ContactCC_Address.CountyName LinkCountyName,
CN1.NameTUR SalesPos,
CN2.NameTUR SalesClerk,
CN3.NameTUR CustomerPos,
Collector.NameTUR Collector,
CN4.NameTUR SalesClerk2,
Trading_Sales_SalesTeam.NameTUR SalesTeam,
Trading_LinkType.Name LinkTypeName,
Trading_Sales_Customer.PosID CustomerPosID,
Trading_Sales_Customer.CollectorID
FROM Trading_Link
INNER JOIN Trading_Sales_Customer
ON Trading_Sales_Customer.LinkID = Trading_Link.LinkID
LEFT OUTER JOIN Collaboration_Node Collector
ON Collector.NodeID = Trading_Sales_Customer.CollectorID
LEFT OUTER JOIN Trading_Sales_Pos
ON Trading_Sales_Pos.PointID = Trading_Sales_Customer.PosID
LEFT JOIN Collaboration_Node CN1
ON Trading_Sales_Pos.PointID = CN1.NodeID
LEFT JOIN Collaboration_Node CN2
ON CN2.NodeID = Trading_Sales_Customer.SalesClerkID
LEFT JOIN Collaboration_Node CN3
ON CN3.NodeID = Trading_Sales_Customer.PosID
LEFT JOIN Collaboration_Node CN4
ON CN4.NodeID = Trading_Sales_Customer.SalesClerk2ID
LEFT OUTER JOIN Trading_Sales_SalesTeam
ON Trading_Sales_SalesTeam.SalesTeamID = Trading_Sales_Customer.SalesTeamID
LEFT OUTER JOIN ContactCC_Contact
ON ContactCC_Contact.ContactID = Trading_Link.ContactID
LEFT OUTER JOIN ContactCC_Address
ON ContactCC_Address.AddressID = ContactCC_Contact.DefaultAddressID
LEFT OUTER JOIN Trading_LinkType
ON Trading_LinkType.LinkTypeID = Trading_Link.TypeID
) LinkView
ON LinkView.LinkID = Finance_InvoiceItem.LinkID
WHERE Finance_InvoiceItem.InnerType = 1
AND Finance_InvoiceItem.StatusID = 21
AND Finance_InvoiceItem.DateSent >= TRUNC(SYSDATE,’MM’) — Ayın 1’i
AND Finance_InvoiceItem.DateSent < TRUNC(SYSDATE) -- Bugün 00:00:00 (dün dahil)
AND Finance_InvoiceItem.Type = 1
GROUP BY LinkView.SalesPos, CI.IntegerQuantity
ORDER BY LinkView.SalesPos
