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

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

# 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)

# SQL query to retrieve the image data and other item information
sql_query = '''
SELECT IRCODE
,IR_IMCODE
FROM [Marcoco_V3].[dbo].[Stocks]
INNER JOIN WAREHOUSE
ON WHID=STK_WHID
INNER JOIN ITEM_RAW
ON IRID=STK_IRID
where whid=21 AND STKQTY>0
ORDER BY IRCODE
'''

# Execute the SQL query and fetch all rows
cursor = connection.cursor()
cursor.execute(sql_query)
rows = cursor.fetchall()

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

# Write column headers to the worksheet
ws.append(['IRcode', 'IR_Imcode'])

# Process each row and save the image as embedded in the Excel file
# Process each row and save the image as embedded in the Excel file
# Process each row and save the image as embedded in the Excel file
for row in rows:
    IRcode, IR_Imcode, binary_image_data = row  # Update this line

    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'{IRcode}{IR_Imcode}.jpg'
        image.save(temp_image_path, 'JPEG')

        # Add the row data to the worksheet
        ws.append([IRcode, IR_Imcode])

        # Add the image to the worksheet
        img = ExcelImage(temp_image_path)
        img.width = img.width * 0.5  # Adjust image width (optional)
        img.height = img.height * 0.5  # Adjust image height (optional)
        ws.add_image(img, f'E{ws.max_row}')  # Assuming the image is inserted in column E

        # Delete the temporary image file
        image.close()
        del image
        del img
        del binary_image_data
        del temp_image_path

    except Exception as e:
        print(f"Error processing row: {IRcode}, {IR_Imcode}")
        print(f"Error message: {e}")

        try:
    # Your existing code here ...

except Exception as e:
    print(f"Error processing row: {IRcode}, {IR_Imcode}")
    print(f"Error message: {e}")

# Add a prompt to keep the window open until the user presses Enter
input("Press Enter to exit...")