Otel ön büro verisini salt-okur bağlantıyla yönetim raporuna çevirmek

Bir otelin en değerli verisi ön büro yazılımının (PMS) veritabanında durur: rezervasyonlar, konaklamalar, folyolar, acenta hesapları, tahsilatlar. Yönetim ise bu veriyi çoğu zaman elle hazırlanmış Excel dosyalarından görür. Her sabah biri raporu dışa aktarır, birkaç sayfayı birleştirir, kur farkını elle hesaplar ve genel müdüre gönderir. Bu düzen hem yavaştır hem de hataya açıktır; aynı soruya iki departmanın iki farklı rakamla gelmesi bu yüzden olağandır.

Bu yazıda, bir resort otel için kurduğumuz raporlama katmanının arkasındaki yaklaşımı anlatıyoruz. Temel ilke basit: otel yazılımına hiçbir şey yazmamak, onu yormamak ve her rakamın kaynağını açıklayabilmek. Ürün tarafı Otel Otomasyon Paketi sayfasında; burada mimari kararlara ve çalışan kod örneklerine odaklanıyoruz.

Neden salt-okur bağlantı?

PMS, otelin operasyonel kalbidir. Resepsiyonda giriş yapılırken, kasada folyo kapanırken veritabanına yazılan her satır bir başka sürecin girdisidir. Raporlama aracının bu veritabanına yazma yetkisi olması, en iyi niyetle yazılmış bir hata ayıklama komutunun bile canlı operasyonu bozabileceği anlamına gelir. Ayrıca PMS üreticisinin destek sözleşmesi, çoğu zaman veritabanına üçüncü taraf yazımını açıkça dışarıda bırakır.

Bu yüzden raporlama katmanı için ayrı bir veritabanı kullanıcısı açıyoruz ve bu kullanıcıya yalnız okuma yetkisi veriyoruz. SQL Server'da bunun en sade yolu, kullanıcıyı db_datareader rolüne eklemektir. Daha dar bir kapsam isteniyorsa rol yerine yalnız belirli şemaya ya da tablolara SELECT izni verilir:

CREATE LOGIN rapor_okur WITH PASSWORD = 'uzun-ve-rastgele-bir-parola';
GO
USE OtelPms;
CREATE USER rapor_okur FOR LOGIN rapor_okur;
-- Seçenek 1: veritabanındaki bütün tabloları okuyabilir
ALTER ROLE db_datareader ADD MEMBER rapor_okur;
-- Seçenek 2: yalnız belirli şema
-- GRANT SELECT ON SCHEMA::dbo TO rapor_okur;
-- Açıkça yazma engeli (rol üyeliğinden gelebilecek yetkileri de keser)
ALTER ROLE db_denydatawriter ADD MEMBER rapor_okur;

db_denydatawriter üyeliği, (kullanıcıya sysadmin ya da db_owner yetkisi verilmemiş olmak şartıyla) kullanıcının ileride yanlışlıkla yazma yetkisi taşıyan bir role eklenmesi durumunda bile tablolara INSERT, UPDATE ve DELETE yapamamasını sağlar. Bu küçük bir satırdır ama "salt-okur" sözünü veritabanı düzeyinde güvenceye bağlar.

ApplicationIntent=ReadOnly ne yapar, ne yapmaz?

Bağlantı dizesine ApplicationIntent=ReadOnly eklemek sık önerilir ama sık yanlış anlaşılır. Bu ayar bağlantıyı salt-okur yapmaz. Yalnız SQL Server Always On kullanılabilirlik gruplarında, dinleyici (listener) adresine bağlanıldığında ve salt-okur yönlendirme tanımlıysa, bağlantının okunabilir ikincil kopyaya yönlendirilmesini sağlar. Tek sunuculu bir kurulumda hiçbir etkisi yoktur. Gerçek koruma yukarıdaki yetki düzenidir; bu ayar ancak altyapıda okunabilir ikincil varsa yükü birincil sunucudan almak için anlamlıdır.

Python'dan güvenli ve parametreli sorgu

Raporlama katmanını Python ile yazıyoruz; SQL Server'a pyodbc üzerinden bağlanıyoruz. İki alışkanlık önemli: değerleri sorgu metnine asla birleştirmemek, her zaman ? yer tutucusuyla parametre olarak geçmek; ve sorgu zaman aşımı koymak. Zaman aşımı, beklenmedik biçimde büyüyen bir sorgunun gün içinde PMS'i kilitlemesini önler.

import os
import pyodbc

def baglan() -> pyodbc.Connection:
    dize = (
        "DRIVER={ODBC Driver 18 for SQL Server};"
        f"SERVER={os.environ['PMS_SUNUCU']};DATABASE={os.environ['PMS_VT']};"
        f"UID={os.environ['PMS_KULLANICI']};PWD={os.environ['PMS_PAROLA']};"
        "Encrypt=yes;TrustServerCertificate=no;"
    )
    con = pyodbc.connect(dize, timeout=15)   # bağlantı zaman aşımı (sn)
    con.timeout = 120                         # sorgu zaman aşımı (sn)
    return con

def konaklamalar(con, baslangic: str, bitis: str) -> list[tuple]:
    sql = (
        "SELECT rezervasyon_no, misafir, giris, cikis, oda_tipi, acenta_kodu, tutar, para_birimi "
        "FROM dbo.Konaklama WHERE giris >= ? AND giris < ?"
    )
    return con.cursor().execute(sql, baslangic, bitis).fetchall()

Bağlantı dizesindeki Encrypt=yes trafiği şifreler, TrustServerCertificate=no ise sunucu sertifikasının doğrulanmasını şart koşar. İç ağdaki veritabanı kendinden imzalı bir sertifika kullanıyorsa bu değeri geçici olarak yes yapmak bağlantıyı açar ama sertifika doğrulamasını kapatır ve araya giren birine karşı korumasız bırakır; doğru çözüm, sunucuya istemcinin güvendiği bir sertifika kurmaktır.

Tablo ve sütun adları her PMS'te farklıdır; yukarıdakiler örnek amaçlıdır. Kurulumun en uzun kısmı da zaten budur: otelin veri sözlüğünü çıkarmak, hangi kodun neyi temsil ettiğini ön büro ve muhasebe ekibiyle birlikte doğrulamak.

PMS'in kendi saklı yordamlarını kullanmak

Gelir gibi kritik rakamlarda kendi SQL sorgumuzu yazmak yerine, PMS'in kendi raporlarında kullandığı saklı yordamları çağırmayı tercih ediyoruz. Gerekçe tutarlılık: PMS'in ekranında görünen departman geliri belirli kurallarla hesaplanır (iptal, iade, paket fiyatın bölüştürülmesi, vergi). Bu kuralları kendi sorgumuzda yeniden üretmeye çalışmak, yönetimin iki ekranda iki farklı rakam görmesine yol açar. Aynı yordamı çağırdığımızda rapor ile PMS ekranı aynı sonucu verir; fark varsa sebep bizim katmanımızda değil, veri girişindedir.

Burada önemli bir güvenlik ayrıntısı var. Bir yordamı çalıştırmak için kullanıcıya EXECUTE izni vermek gerekir. SQL Server'da sahiplik zinciri (ownership chaining) nedeniyle, aynı sahibe ait tablolara yazan bir yordam, çağıran kullanıcının yazma yetkisi olmasa bile o yazmayı yapabilir. Bu yüzden EXECUTE iznini şemaya toptan değil, yalnız içeriği okunup yalnız okuma yaptığı doğrulanan raporlama yordamlarına tek tek veriyoruz.

GRANT EXECUTE ON OBJECT::dbo.RaporDepartmanGeliri TO rapor_okur;

def departman_geliri(con, tarih: str) -> list[tuple]:
    return con.cursor().execute("{CALL dbo.RaporDepartmanGeliri (?)}", tarih).fetchall()

Gece anlık görüntüsü: PMS'i gündüz yormamak

Yönetim raporları gün içinde defalarca açılır. Her açılışta PMS'e büyük sorgular göndermek, resepsiyonun yoğun saatlerinde sistemi yavaşlatabilir. Bunun yerine her gece, otelin gün sonu işlemleri bittikten sonra, ihtiyaç duyulan veriyi bir kez okuyup raporlama katmanının kendi SQLite veritabanına yazıyoruz. Gündüz açılan bütün raporlar bu kopyadan beslenir; PMS'e yalnız gece ve tek seferlik bir okuma gider.

import sqlite3

def anlik_goruntu_al(pms, yerel_yol: str, baslangic: str, bitis: str) -> int:
    satirlar = konaklamalar(pms, baslangic, bitis)
    with sqlite3.connect(yerel_yol) as yerel:
        yerel.execute(
            "CREATE TABLE IF NOT EXISTS konaklama ("
            "rezervasyon_no TEXT, misafir TEXT, giris TEXT, cikis TEXT, oda_tipi TEXT, "
            "acenta_kodu TEXT, tutar REAL, para_birimi TEXT)"
        )
        yerel.execute("DELETE FROM konaklama WHERE giris >= ? AND giris < ?", (baslangic, bitis))
        yerel.executemany(
            "INSERT INTO konaklama VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            [(r[0], r[1], str(r[2]), str(r[3]), r[4], r[5], float(r[6]), r[7]) for r in satirlar],
        )
    return len(satirlar)

Silme ve ekleme aynı işlem içinde yapıldığı için yarıda kalan bir aktarım eski veriyi yok etmez; with bloğu hata olursa işlemi geri alır. Anlık görüntünün bir bedeli de var: gün içindeki rakamlar dünün kapanışını gösterir. Bunu raporun başlığında açıkça yazıyoruz. Gerçekten anlık olması gereken birkaç küçük gösterge (bugünkü giriş-çıkış listesi gibi) ise doğrudan, küçük ve parametreli sorgularla okunur.

Kur önbelleği

Turizmde gelir döviz, giderin önemli kısmı Türk lirasıdır. Raporların karşılaştırılabilir olması için her tutarın hangi günün kuruyla çevrildiği belli olmalıdır. Kuru her rapor açılışında dış bir servisten çekmek hem yavaştır hem de servis erişilemezse raporu bozar. Bu yüzden günlük kurları kendi veritabanımızda saklıyor, bir tarih için kur yoksa geriye doğru en yakın iş gününün kurunu kullanıyoruz. Hafta sonu ve resmî tatillerde yayımlanmış kur olmadığı için bu geri düşüş zorunludur.

from datetime import date, timedelta

def kur(yerel: sqlite3.Connection, gun: date, doviz: str) -> float:
    for geri in range(10):                       # en çok 10 gün geriye bak
        t = (gun - timedelta(days=geri)).isoformat()
        satir = yerel.execute(
            "SELECT alis FROM kur WHERE tarih = ? AND doviz = ?", (t, doviz)
        ).fetchone()
        if satir:
            return satir[0]
    raise LookupError(f"{gun} için {doviz} kuru bulunamadı")

Kurların kendisi gece görevinde, resmî kaynaktan bir kez alınıp bu tabloya yazılır. Hata durumunda rapor sessizce yanlış kurla çalışmaz; kur bulunamazsa açık bir hata verir ve gece görevinin uyarısı bunu yakalar.

İstisna robotları: bulur, işaretler, yazmaz

Raporlamanın bir adım ötesi, insan gözünün kaçırabileceği tutarsızlıkları kendiliğinden bulmaktır. Bunlara "robot" diyoruz ama yaptıkları iş mütevazıdır: gece anlık görüntüsü üzerinde belirli kuralları çalıştırır, kurala uymayan kayıtları bir istisna kuyruğuna yazar. Örnek kurallar: aynı misafir, aynı giriş tarihi ve aynı oda tipiyle açılmış iki rezervasyon; vadesi geçtiği hâlde tahsil edilmemiş acenta bakiyesi; çıkışı yapılmış ama folyosu açık kalmış konaklama.

Kritik ilke şu: robotlar PMS'e hiçbir şey yazmaz, hiçbir kaydı düzeltmez. Yalnız bulur ve işaretler. Düzeltmeyi yetkili kişi, PMS'in kendi ekranında yapar. Böylece bir kural yanlış yazılsa bile sonuç en fazla gereksiz bir uyarıdır, bozulmuş bir kayıt değil.

KURALLAR = {
    "cift_rezervasyon": """
        SELECT 'cift_rezervasyon', MIN(rezervasyon_no) || ',' || MAX(rezervasyon_no)
        FROM konaklama
        GROUP BY misafir, giris, oda_tipi HAVING COUNT(*) > 1
    """,
    "vadesi_gecmis_tahsilat": """
        SELECT 'vadesi_gecmis_tahsilat', acenta_kodu
        FROM acenta_bakiye
        WHERE vade < DATE('now') AND bakiye > 0
    """,
}

def robotlari_calistir(yerel: sqlite3.Connection) -> int:
    yerel.execute(
        "CREATE TABLE IF NOT EXISTS istisna (kural TEXT, anahtar TEXT, "
        "bulundu TEXT DEFAULT CURRENT_TIMESTAMP, durum TEXT DEFAULT 'acik', "
        "UNIQUE (kural, anahtar))"
    )
    once = yerel.total_changes
    for sql in KURALLAR.values():
        yerel.executemany(
            "INSERT OR IGNORE INTO istisna (kural, anahtar) VALUES (?, ?)",
            yerel.execute(sql).fetchall(),
        )
    yerel.commit()
    return yerel.total_changes - once

Örnekteki acenta_bakiye tablosu, konaklama gibi gece görevinde doldurulan bir başka anlık görüntü tablosudur. UNIQUE (kural, anahtar) kısıtı, aynı istisnanın her gece yeniden açılmasını engeller; kuyrukta bir kayıt kapatılmadıkça tek kez görünür. Kuyruk ekranında her istisnadan ilgili rezervasyonun ayrıntısına inilebilir ve günlük bir e-posta özeti yalnız yeni açılanları listeler.

Excel, PDF ve iki dilli rapor

Yönetim ekranı ne kadar iyi olursa olsun, muhasebe ve yönetim kurulu Excel ister. Bu yüzden her raporun ekrandaki tablosu ile Excel çıktısı aynı veri yapısından üretilir; ekranda görünen rakam ile dosyadaki rakam arasında ayrı bir hesap katmanı yoktur. Formül gereken yerlerde (toplamlar, oranlar) değer yerine gerçek Excel formülü yazmak, dosyayı alan kişinin rakamın nereden geldiğini görmesini sağlar.

from openpyxl import Workbook

ETIKET = {
    "tr": {"acenta": "Acenta", "gece": "Geceleme", "gelir": "Gelir (EUR)", "toplam": "Toplam"},
    "en": {"acenta": "Agency", "gece": "Room nights", "gelir": "Revenue (EUR)", "toplam": "Total"},
}

def acenta_excel(satirlar: list[tuple], dil: str, yol: str) -> None:
    e = ETIKET[dil]
    wb = Workbook()
    ws = wb.active
    ws.append([e["acenta"], e["gece"], e["gelir"]])
    for satir in satirlar:
        ws.append(list(satir))
    son = ws.max_row
    ws.append([e["toplam"], f"=SUM(B2:B{son})", f"=SUM(C2:C{son})"])
    wb.save(yol)

Otellerde yönetim kadrosu ve zincir merkezi çoğu zaman farklı dilleri konuşur. Etiketleri tek bir sözlükte tutup rapor üretimine dil parametresi vermek, aynı raporun Türkçe ve İngilizce sürümünü ek iş olmadan üretmeyi sağlar. PDF çıktısında da aynı sözlük kullanılır; böylece iki dil arasında rakam değil yalnız etiket değişir.

Kurulumda izlediğimiz sıra

1. Otelin BT sorumlusuyla salt-okur kullanıcıyı ve ağ erişimini birlikte tanımlamak.
2. Veri sözlüğünü çıkarmak: hangi tablo, hangi kod, hangi durum değeri neyi temsil ediyor.
3. İlk raporu PMS'in kendi ekranıyla yan yana koyup farkları satır satır kapatmak.
4. Gece anlık görüntüsünü ve kur tablosunu devreye almak.
5. İstisna kurallarını birer birer, departman sorumlusunun onayıyla eklemek.

Üçüncü adım en çok zaman alanıdır ve atlanmamalıdır. Yönetim, yeni bir rapora ancak alıştığı ekranla aynı rakamı gördüğünde güvenir.

Sonuç

Otel verisinden güvenilir yönetim raporu üretmek için PMS'i değiştirmeye ya da ona yazmaya gerek yok. Veritabanı düzeyinde sınırlandırılmış bir okuma kullanıcısı, PMS'in kendi hesap kurallarını taşıyan yordamlar, gece alınan bir anlık görüntü, saklanan kurlar ve yalnız işaretleyen istisna robotları; operasyonu riske atmadan yönetime her sabah aynı dili konuşan rakamlar verir. Kendi otelinizin verisiyle neyin mümkün olduğunu konuşmak isterseniz iletişim sayfamızdan bize yazabilirsiniz.