db.py

import pyodbc

# פונקציה ליצירת חיבור חדש למסד הנתונים
def get_db_connection():
    return pyodbc.connect(
        "DRIVER={ODBC Driver 17 for SQL Server};"
        "SERVER=localhost;"
        "DATABASE=RecipeDB;"
        "Trusted_Connection=yes;"
    )

def get_all_recipes():
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM Recipes")
            # חילוץ שמות העמודות כדי להפוך את התוצאה למילון
            columns = [column[0] for column in cursor.description]
            return [dict(zip(columns, row)) for row in cursor.fetchall()]
    except pyodbc.Error as e:
        return {"error": f"שגיאה בבסיס הנתונים: {str(e)}"}
    finally:
        conn.close()

def get_recipe_by_id(recipe_id):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM Recipes WHERE RecipeCode = ?", (recipe_id,))
            columns = [column[0] for column in cursor.description]
            row = cursor.fetchone()
            if not row:
                return {"error": "מתכון לא נמצא"}
            return dict(zip(columns, row))
    except pyodbc.Error as e:
        return {"error": f"שגיאה בבסיס הנתונים: {str(e)}"}
    finally:
        conn.close()

def get_recipe_by_category(category_id):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM Recipes WHERE CategoryID = ?", (category_id,))
            columns = [column[0] for column in cursor.description]
            rows = cursor.fetchall()
            if not rows:
                return {"error": "לא נמצאו מתכונים בקטגוריה זו"}
            return [dict(zip(columns, row)) for row in rows]
    except pyodbc.Error as e:
        return {"error": f"שגיאה בבסיס הנתונים: {str(e)}"}
    finally:
        conn.close()

def add_recipe(recipe_data):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                """
                INSERT INTO Recipes (RecipeName, CategoryID, PreparationMethod, Products, PrepTime, CommentCount, TotalPoints ,ImageUrl)
                VALUES (?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    recipe_data.get("RecipeName"),
                    recipe_data.get("CategoryID"),
                    recipe_data.get("PreparationMethod"),
                    recipe_data.get("Products"),
                    recipe_data.get("PrepTime"),
                    recipe_data.get("CommentCount"),
                    recipe_data.get("TotalPoints"),
                    recipe_data.get("ImageUrl")
                )
            )
            conn.commit()
            return {"success": "המתכון נוסף בהצלחה"}
    except pyodbc.Error as e:
        conn.rollback()
        return {"error": f"שגיאה בבסיס הנתונים: {str(e)}"}
    finally:
        conn.close()

def delete_recipe(recipe_id):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("DELETE FROM Recipes WHERE RecipeCode = ?", (recipe_id,))
            conn.commit()
            return {"success": "המתכון נמחק בהצלחה"}
    except pyodbc.Error as e:
        conn.rollback()
        return {"error": f"שגיאה בבסיס הנתונים: {str(e)}"}
    finally:
        conn.close()

def update_recipe(recipe_data,RecipeCode):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                """
                UPDATE Recipes
                SET RecipeName = ?,
                    CategoryID = ?,
                    PreparationMethod = ?,
                    Products = ?,
                    PrepTime = ?,
                    CommentCount = ?,
                    TotalPoints = ?,
                    ImageUrl = ?
                    
                WHERE RecipeCode = ?
                """,
                (
                    recipe_data.get("RecipeName"),
                    recipe_data.get("CategoryID"),
                    recipe_data.get("PreparationMethod"),
                    recipe_data.get("Products"),
                    recipe_data.get("PrepTime"),
                    recipe_data.get("CommentCount"),
                    recipe_data.get("TotalPoints"),
                    recipe_data.get("ImageUrl"),
                    RecipeCode
                )
            )
            conn.commit()
            return {"success": "המתכון עודכן בהצלחה"}
    except pyodbc.Error as e:
        conn.rollback()
        return {"error": f"שגיאה בבסיס הנתונים: {str(e)}"}
    finally:
        conn.close()