{# templates/realize.html #} {% extends “base.html” %} {% block title %}Realize Tablosu{% endblock %} {% block page_title %}Realize Tablosu{% endblock %} {% block nav_realize_active %}active{% endblock %} {% block head_extra %} {% endblock %} {% block content %} {% 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