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'

# 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
,IRPhoto
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'])

# Set a fixed width for the images
image_width = 100

# 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 = row

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

        # Calculate the corresponding height to maintain aspect ratio
        image_width, image_height = image.size
        aspect_ratio = image_width / image_height
        image_height = int(image_width / aspect_ratio)

        # Save the image to a temporary file
        temp_image_path = f'{IRcode}{IR_Imcode}.jpg'
        image.resize((image_width, image_height)).save(temp_image_path, 'JPEG')

        # Add the row data to the worksheet
        ws.cell(row=row_idx, column=1, value=IRcode)
        ws.cell(row=row_idx, column=2, value=IR_Imcode)

        # Add the image to the worksheet
        img = ExcelImage(temp_image_path)
        img.width = image_width
        img.height = image_height
        ws.column_dimensions[chr(ord('C') + row_idx - 2)].width = image_width  # Set column width to match image size
        ws.row_dimensions[row_idx].height = image_height  # Set row height to match image size
        ws.add_image(img, f'C{row_idx}')  # Place the image in column C and the corresponding row

        # Delete the temporary image file
        image.close()

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

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

# Close the connection
connection.close()