Frknrg/RgReport

1import pandas as pd2import psycopg23import os4from datetime import datetime5 6# Database credentials7DB_HOST = os.environ.get("SUPABASE_HOST", "aws-1-eu-north-1.pooler.supabase.com")8DB_PORT = os.environ.get("DB_PORT", "5432")9DB_NAME = os.environ.get("SUPABASE_DB", "postgres")10DB_USER = os.environ.get("SUPABASE_USER", "postgres.wawaztdbewvxugcfkzcw")11DB_PASS = os.environ.get("SUPABASE_PASSWORD", "f2kDElCfRDChelbh")12 13# Use absolute path for cache file14BASE_DIR = os.path.dirname(os.path.abspath(__file__))15CACHE_FILE = os.path.join(BASE_DIR, "sales_cache.parquet")16 17def get_connection():18    try:19        conn = psycopg2.connect(20            host=DB_HOST,21            port=DB_PORT,22            database=DB_NAME,23            user=DB_USER,24            password=DB_PASS,25            connect_timeout=30,26            options="-c statement_timeout=300000"27        )28        return conn29    except Exception as e:30        print(f"Error connecting to database: {e}")31        return None32 33def run_daily_refresh():34    print(f"[{datetime.now()}] Starting Sales Data Refresh...")35    conn = get_connection()36    if not conn:37        return "❌ Veritabanı bağlantı hatası."38 39    # Fetch all relevant data40    # Optimized: Fetch JSON columns as TEXT to avoid Python-side parsing overhead41    # Extract country and store_id directly in SQL42    query = """43        SELECT 44            order_date,45            order_status,46            order_total,47            ship_to::text as ship_to_json,48            ship_to->>'country' as country,49            advanced_options->>'storeId' as store_id,50            items::text as items_json51        FROM shipstation_orders52        WHERE order_status != 'cancelled'53    """54    55    try:56        print("Executing SQL query...")57        df = pd.read_sql(query, conn)58        print(f"Fetched {len(df)} rows.")59        60        if not df.empty:61            print(f"Max Date in DB: {pd.to_datetime(df['order_date']).max()}")62        63        # Pre-processing to make app faster64        print("Processing data...")65        66        # Ensure date is datetime67        df['order_date'] = pd.to_datetime(df['order_date'])68        69        # Define Channel70        def get_channel(store_id):71            if str(store_id) == '313526':72                return 'Eternate'73            elif str(store_id) == '340285':74                return 'Vianisa'75            else:76                return 'Market Place'77        78        df['channel'] = df['store_id'].apply(get_channel)79        80        # Handle nulls in JSON columns if any (though ::text usually handles it)81        df['items_json'] = df['items_json'].fillna("[]")82        df['ship_to_json'] = df['ship_to_json'].fillna("{}")83        84        # Columns are already in the format we want for parquet85        df_save = df[['order_date', 'order_status', 'order_total', 'country', 'store_id', 'channel', 'items_json', 'ship_to_json']]86        87        # Save to Parquet88        print(f"Saving to {CACHE_FILE}...")89        df_save.to_parquet(CACHE_FILE, index=False, engine='pyarrow')90        print("Success! Cache updated.")91        return "✅ Satış Verileri Başarıyla Yenilendi."92        93    except Exception as e:94        print(f"Error during refresh: {e}")95        return f"❌ Hata: {e}"96    finally:97        conn.close()98 99if __name__ == "__main__":100    run_daily_refresh()101