import pyodbc
from PIL import Image
from openpyxl import Workbook
from openpyxl.drawing.image import Image as ExcelImage
import io

# Replace these with your database connection details
server = '192.168.1.11'
database = 'Marcoco_V3'
username = 'sa'
password = 'sa'

# Open a log file to write error messages
log_file = open("script_errors.log", "w")

try:
    # Create a connection to the database
    connection_string = f'DRIVER={{SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password};'
    connection = pyodbc.connect(connection_string)
    print("Database connection successful")

    # SQL query to retrieve the image data and other item information
    sql_query = '''
    SELECT
    [IRCode], [IR_IMcode], [IRPhoto], [IRName], [IR_IMName], [IRM3],
    [IRMinQty], [IRItemD], [IRPackUnit], [IRMaterial], [IRStructure], [IRRemarks]
    FROM [Marcoco_V3].[dbo].[Item_Raw]
    WHERE IR_FORSALE=1 AND IREnterdate>'2024.06.01'
    ORDER BY IRModifiedDate DESC
    '''

    # Execute the SQL query and fetch all rows
    cursor = connection.cursor()
    cursor.execute(sql_query)
    rows = cursor.fetchall()
    print(f"Number of rows fetched: {len(rows)}")

    # Create an Excel workbook and add a worksheet
    wb = Workbook()
    ws = wb.active

    # Define headers
    headers = ['Item Code', 'Item Name', 'Remark', 'Item Material', "Item's M3", 'Gross Weight', 
               'Packing Unit', 'Item Dimension', 'Colour', 'Image']

    # Add column headers to the worksheet
    for col_num, header in enumerate(headers, start=1):
        ws.cell(row=1, column=col_num, value=header)

    # Process each row and save the image as embedded in the Excel file
    for row_idx, row in enumerate(rows, start=2):  # Start from row 2 to leave space for headers
        (IRCode, IR_IMcode, binary_image_data, IRName, IR_IMName, IRM3, IRMinQty, IRItemD,
         IRPackUnit, IRMaterial, IRStructure, IRRemarks) = row

        # Combine item name and description fields
        item_code = IRCode
        item_name = IR_IMcode
        item_material = f"{IRMaterial} / {IRStructure}"
        gross_weight = 39.5  # Assuming a fixed value for gross weight as in the example
        colour = 'Flash Silver'  # Assuming a fixed value for colour as in the example

        # Add the row data to the worksheet
        ws.cell(row=row_idx, column=1, value=item_code)
        ws.cell(row=row_idx, column=2, value=item_name)
        ws.cell(row=row_idx, column=3, value=IRRemarks)
        ws.cell(row=row_idx, column=4, value=item_material)
        ws.cell(row=row_idx, column=5, value=IRM3)
        ws.cell(row=row_idx, column=6, value=gross_weight)
        ws.cell(row=row_idx, column=7, value=IRPackUnit)
        ws.cell(row=row_idx, column=8, value=IRItemD)
        ws.cell(row=row_idx, column=9, value=colour)

        # Process the image
        try:
            # Open the binary data as an image using Pillow
            image = Image.open(io.BytesIO(binary_image_data))

            # Save the image to a temporary file
            temp_image_path = f'{item_code}_{item_name}.jpg'
            image.save(temp_image_path, 'JPEG')
            print(f"Image saved for item {item_code}")

            # Add the image to the worksheet
            img = ExcelImage(temp_image_path)
            ws.add_image(img, f'J{row_idx}')  # Place the image in column J and the corresponding row
            print(f"Image added to Excel for item {item_code}")

        except Exception as e:
            print(f"Error processing image for row: {item_code}, {item_name}")
            print(f"Error message: {e}")

    # Save the workbook to an Excel file
    wb.save('exported_data.xlsx')
    print("Excel file saved as 'exported_data.xlsx'")

    # Close the connection
    connection.close()
    print("Database connection closed")

except Exception as e:
    print(f"An error occurred: {e}")
    log_file.write(f"An error occurred: {e}\n")

# Close the log file
log_file.close()

# Keep the window open to view the output
input("Press Enter to exit...")