Spaces:
Sleeping
Sleeping
| import subprocess | |
| import sys | |
| # Install openpyxl if not available | |
| try: | |
| import openpyxl | |
| except ImportError: | |
| subprocess.check_call([sys.executable, "-m", "pip", "install", "openpyxl"]) | |
| import openpyxl | |
| # Install additional dependencies for PDF generation | |
| try: | |
| from reportlab.pdfgen import canvas | |
| from reportlab.lib.pagesizes import A4 | |
| from reportlab.lib.units import inch | |
| from reportlab.lib.colors import HexColor | |
| from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, Image | |
| from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle | |
| from reportlab.lib import colors | |
| except ImportError: | |
| subprocess.check_call([sys.executable, "-m", "pip", "install", "reportlab"]) | |
| from reportlab.pdfgen import canvas | |
| from reportlab.lib.pagesizes import A4 | |
| from reportlab.lib.units import inch | |
| from reportlab.lib.colors import HexColor | |
| from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, Image | |
| from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle | |
| from reportlab.lib import colors | |
| import gradio as gr | |
| import pandas as pd | |
| import tempfile | |
| from datetime import datetime, timedelta | |
| import openpyxl | |
| from openpyxl.styles import Font, Alignment, Border, Side, PatternFill | |
| from openpyxl.drawing.image import Image as ExcelImage | |
| import random | |
| import base64 | |
| from io import BytesIO | |
| import os | |
| import zipfile | |
| def generate_random_invoice_number(): | |
| """Generate random 6-digit invoice number""" | |
| return str(random.randint(100000, 999999)) | |
| def create_professional_individual_invoice(workbook, employee_data, start_date, end_date): | |
| """Create individual invoice sheet with professional Glimmr template formatting and logo""" | |
| # --- PASTE YOUR CLEANED BASE64 STRING HERE --- | |
| GLIMMR_LOGO_BASE64 = """ | |
| iVBORw0KGgoAAAANSUhEUgAAAOEAAADhCAMAAAAJbSJIAAAAnFBMVEX///8An9r/nzcAm9kAmdgAndkAl9fo8/rQ5/Vjt+L/myug0OzU6fb/yZzC4PIAldeBwudPsOCn1O05qd7/3MDk8fn/mSH/59Xd7vh6v+a73fFrvOWy2vD/nC74/P6Hx+n/qVP/rl3/0qshpdz/8ub//PlYteL/1rX/lACUy+r/o0L/5Mv/lxr/uXj/vYH/69r/w4z/t3Mfm83/y6CSnfTNAAAGLElEQVR4nO2cfXeiOBTGiXlBxRGF1iJabIeOHabOtLv9/t9tkxBAq+juWV4OOc/vn0ajOfcxbzf3hjoOAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH0wSyd9m9AuCeUi7tuIVllyIqK+jWiTjBG+6duINokE4bxvI1qFyDE67tuINplwQtd9G/F/ePlxvX4rJyHpxpSWeH24Wu0KMvQx+uvpanUox+iiG0va4eX5cfr8Ul+/o4Sn3ZnTPG+/DqPR4emtrn6sxuisS4ua5/vhcFdbmXA5RncdWtMKvz/r66S3Nuwxqnmo3y08Ruz2R6W3RmjWtxUNcraippyQ4Y/Rivc/X97YUDlG73uxpRXeR4fPkxkZyzHKhj1GTxzSl4/D9GRbTGQP8rBrm5rl7nSff30+eSm9NSLcLu1pnqen09fvxy8y2YXM69CaFvhYTX/XVs7kJOTLDq1pnJe7p+loNB19r3G6+eDH6I/338rl/vN+uXqvBAbdmtQ8r/UudzD4MZrz10ddDVHLTNKlLe3wUH/w3Qix7dCStrh4pnDDjeq8rQVjtIaUcz7suNMN9D4ohu2NXieWa4xcZZYWLDOXcIOQEc3wQxfBPotnbtVRSTQP1qmgnBiFg19oIp9SxgTjqYRQKgSjvJAn8YcfnAmNHK4gX6EDj5Aq5uJM1hGib/OaYH/ec0cKrYjOEEE1l5QyK/b8ZB54WRZsdxckUhuc0pwkXtILfUgHfzTM2S6JuDhI7Ql0h6f9V4nlg083FRwPUc6WpUSLLs/sS4l/k3Foo0J5mM8lid2PjRbIJym3SqGTCc6p2EfOjpmNwjaFTpzuPddxdsaNEwm3apSWoZpNcTDcO8IqhYZxWqw4bD5jNu0WBVGhkBPHk0U68JTMJTymNUppalekNt4HTnbSg+PcSdSKY8fp6YzES/2ts6Vk6FcRrxFJX1UeG/3BH54iz6s94rpx4M27NKYpgkkYLnd5ynPtUyoGfgfhnL1aRqi+ZbjV3suw7zVfYJK71WlZJLRvkxrGyBKJvm+oi32b1DCFQlff+iXDvwx0xpHChKs56VsQ1j7hSKGTLNJ0Yp3XcqzQCmZxfJo9uqlQfsN4Ack4np/3sDuP5+bLUfnRY97urj+30Sgel66XSMdOsk7lmUHlAo8UeillfK/9llgWaRhIh4Ax+Q11jy2ZCFVM82kacsrI+t5xlTPHxFJ+PeC6mD+C8fprupo+vzrO52G1OoxuPH3TGEuWb34i0DlB7p8qXCjfmvsqrO3pZVVMMhPCYIvEhIi5r7vJV9/nYl68S5O1MNupzqF+m45Go8fDx8dK/h1Nay/oNMumjBCagviisNoPc4WkyiDStMwC68fxxElDymUoi/rCjVYoNT7qP9NvnQicnWUGaxTOSoVHVHFvNq8UXqrXG6pRaFjVP7jRJJMiu0sL8y8rZMcKq14sU8I6ViPO6iuN4kzhoZt5WBgVBsG/Vkj3ZdabTIwGHW8zjfG0+N3k/CuC/6qtUqFccB4fn2/Z1ggzExZUjnVYzbirClV3FWbfO+O8Bb6vFMrGAlrqNnNVhToKhavPt9enn90M0q2xZPIfFKp0ofksc517E+hfVgrlqjOvfgzTFotKhVP9YNFrJwJLo7MuFXa0xORkxmjPeoX29mGxeKytVRgbS4i1Cu+LBT6zVWGxWxF5VCj2OMsUBmbLr/iicHdDoXtL4aZnhWUnli6kUrioFBpZtQqTWwoXfStMuDGmOAopheYuolIY+dcVmmK9QtNWfwrlj8zUMZwHxiodGV348jhMfRWe8PKiPOJmPlP40j1IRV6UCl2urtMydTrK65lsIc6L+mxv2pI/0befK0VHHukx0XwelT5qHvuNvMU6y3Muri4msjDOkbJmpqhvRse79cJTgR7z5rj66H3ZlsqivjzkXPmnDG1QhpHyCWNfNjD2zb071xyDrLtYoU73i3g8z0z4iNmWlI+ZXkdZEcSw7z88fb3z61txo/mY7Ul8LI+LWsZSlJExOSMHmZO/xXjDhdq/hQgtvNtkSKLxeGZdAg0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABgMPwD+p9Y6vy1tXkAAAAASUVORK5CYII= | |
| """ | |
| ws = workbook.create_sheet(title=employee_data['Row Labels']) | |
| column_widths = {'A': 25, 'B': 15, 'C': 15, 'D': 15, 'E': 15, 'F': 12, 'G': 12, 'H': 12, 'I': 12} | |
| for col, width in column_widths.items(): | |
| ws.column_dimensions[col].width = width | |
| try: | |
| if GLIMMR_LOGO_BASE64.strip() and "PASTE" not in GLIMMR_LOGO_BASE64 and "YOUR BASE64" not in GLIMMR_LOGO_BASE64: | |
| logo_data = base64.b64decode(GLIMMR_LOGO_BASE64.strip()) | |
| logo_stream = BytesIO(logo_data) | |
| img = ExcelImage(logo_stream) | |
| img.width = 200 | |
| img.height = 100 | |
| ws.add_image(img, 'A1') | |
| except Exception as e: | |
| print(f"Logo embedding failed: {e}") | |
| # Company name | |
| ws['A6'] = 'Glimmr Ltd' | |
| ws['A6'].font = Font(bold=True, size=16) | |
| # --- CORRECTED HEADER STRUCTURE TO MATCH ORIGINAL --- | |
| # Main header spanning wider (extended one cell left) | |
| ws.merge_cells('E2:I2') | |
| ws['E2'] = 'CLEANER INVOICE' | |
| ws['E2'].font = Font(bold=True, size=14, color='FFFFFF') | |
| ws['E2'].alignment = Alignment(horizontal='center', vertical='center') | |
| ws['E2'].fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid') | |
| # Invoice # header (left side) | |
| ws.merge_cells('E6:F6') | |
| ws['E6'] = 'Invoice #' | |
| ws['E6'].font = Font(bold=True, color='FFFFFF') | |
| ws['E6'].fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid') | |
| ws['E6'].alignment = Alignment(horizontal='center', vertical='center') | |
| # Date header (right side) | |
| ws.merge_cells('G6:I6') | |
| ws['G6'] = 'Date' | |
| ws['G6'].font = Font(bold=True, color='FFFFFF') | |
| ws['G6'].fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid') | |
| ws['G6'].alignment = Alignment(horizontal='center', vertical='center') | |
| # Invoice # value (below header) | |
| ws.merge_cells('E7:F7') | |
| ws['E7'] = generate_random_invoice_number() | |
| ws['E7'].alignment = Alignment(horizontal='center', vertical='center') | |
| # Date value (below header) | |
| ws.merge_cells('G7:I7') | |
| ws['G7'] = datetime.now().strftime('%d/%m/%y') | |
| ws['G7'].alignment = Alignment(horizontal='center', vertical='center') | |
| # Company address | |
| ws['A8'] = '108 Westbourne Terrace' | |
| ws['A9'] = 'London' | |
| ws['A10'] = 'W2 6QJ' | |
| ws['A11'] = 'support@glimmr.co.uk' | |
| ws['A12'] = '020 8158 8505' | |
| # Payee information | |
| ws['A14'] = "Payee's Name" | |
| ws['A14'].font = Font(bold=True) | |
| ws['A15'] = employee_data['Row Labels'] | |
| ws['A15'].font = Font(size=11) | |
| # Payment period header | |
| ws.merge_cells('A20:D20') | |
| ws['A20'] = 'Payment Period' | |
| ws['A20'].font = Font(bold=True, color='FFFFFF') | |
| ws['A20'].fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid') | |
| ws['A20'].alignment = Alignment(horizontal='center') | |
| # Table headers - adjusted positions | |
| ws['E20'] = 'Hours' | |
| ws['F20'] = '£ / Hour' | |
| ws.merge_cells('G20:I20') | |
| ws['G20'] = 'Amount' | |
| for cell in ['E20', 'F20', 'G20']: | |
| ws[cell].font = Font(bold=True, color='FFFFFF') | |
| ws[cell].fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid') | |
| ws[cell].alignment = Alignment(horizontal='center') | |
| # Payment period data with center alignment | |
| ws.merge_cells('A21:D21') | |
| ws['A21'] = f"{start_date} to {end_date}" | |
| ws['A21'].alignment = Alignment(horizontal='center') | |
| ws['E21'] = employee_data['Sum of Hours Worked'] | |
| ws['F21'] = f"£ {employee_data['Average of Hourly Rate']:.2f}" | |
| ws.merge_cells('G21:I21') | |
| ws['G21'] = f"£ {employee_data['Sum of Total']:.2f}" | |
| # Total sections - adjusted positions | |
| ws.merge_cells('F24:G24') | |
| ws['F24'] = 'Total Labor' | |
| ws['F24'].font = Font(bold=True) | |
| ws.merge_cells('H24:I24') | |
| ws['H24'] = f"£ {employee_data['Sum of Total']:.2f}" | |
| ws.merge_cells('F26:G26') | |
| ws['F26'] = 'Tax (Withholding)' | |
| # Payment due date and total | |
| due_date = (datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)).strftime('%d/%m/%Y') | |
| ws['A27'] = f'Payment is due on {due_date}' | |
| ws.merge_cells('F27:G27') | |
| ws['F27'] = 'TOTAL' | |
| ws['F27'].font = Font(bold=True) | |
| ws.merge_cells('H27:I27') | |
| ws['H27'] = f"£ {employee_data['Sum of Total']:.2f}" | |
| ws['H27'].font = Font(bold=True) | |
| # Comments section | |
| ws['A28'] = 'Comments or Special Instructions:' | |
| ws['A28'].font = Font(bold=True) | |
| ws['A29'] = 'Payments to be made every other Saturday (or as close to these days).' | |
| ws['A30'] = 'Please let us know immediately if there are any issues.' | |
| # Apply borders - updated ranges | |
| thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) | |
| # Border for payment period table | |
| for row in range(20, 22): | |
| for col in range(1, 10): | |
| ws.cell(row=row, column=col).border = thin_border | |
| # Border for header section | |
| for row in range(6, 8): | |
| for col_letter in ['E', 'F', 'G', 'H', 'I']: | |
| ws[f'{col_letter}{row}'].border = thin_border | |
| def create_individual_pdf_invoice(employee_data, start_date, end_date, output_dir): | |
| """Create individual PDF invoice for each cleaner""" | |
| GLIMMR_LOGO_BASE64 = """ | |
| iVBORw0KGgoAAAANSUhEUgAAAOEAAADhCAMAAAAJbSJIAAAAnFBMVEX///8An9r/nzcAm9kAmdgAndkAl9fo8/rQ5/Vjt+L/myug0OzU6fb/yZzC4PIAldeBwudPsOCn1O05qd7/3MDk8fn/mSH/59Xd7vh6v+a73fFrvOWy2vD/nC74/P6Hx+n/qVP/rl3/0qshpdz/8ub//PlYteL/1rX/lACUy+r/o0L/5Mv/lxr/uXj/vYH/69r/w4z/t3Mfm83/y6CSnfTNAAAGLElEQVR4nO2cfXeiOBTGiXlBxRGF1iJabIeOHabOtLv9/t9tkxBAq+juWV4OOc/vn0ajOfcxbzf3hjoOAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH0wSyd9m9AuCeUi7tuIVllyIqK+jWiTjBG+6duINokE4bxvI1qFyDE67tuINplwQtd9G/F/ePlxvX4rJyHpxpSWeH24Wu0KMvQx+uvpanUox+iiG0va4eX5cfr8Ul+/o4Sn3ZnTPG+/DqPR4emtrn6sxuisS4ua5/vhcFdbmXA5RncdWtMKvz/r66S3Nuwxqnmo3y08Ruz2R6W3RmjWtxUNcraippyQ4Y/Rivc/X97YUDlG73uxpRXeR4fPkxkZyzHKhj1GTxzSl4/D9GRbTGQP8rBrm5rl7nSff30+eSm9NSLcLu1pnqen09fvxy8y2YXM69CaFvhYTX/XVs7kJOTLDq1pnJe7p+loNB19r3G6+eDH6I/338rl/vN+uXqvBAbdmtQ8r/UudzD4MZrz10ddDVHLTNKlLe3wUH/w3Qix7dCStrh4pnDDjeq8rQVjtIaUcz7suNMN9D4ohu2NXieWa4xcZZYWLDOXcIOQEc3wQxfBPotnbtVRSTQP1qmgnBiFg19oIp9SxgTjqYRQKgSjvJAn8YcfnAmNHK4gX6EDj5Aq5uJM1hGib/OaYH/ec0cKrYjOEEE1l5QyK/b8ZB54WRZsdxckUhuc0pwkXtILfUgHfzTM2S6JuDhI7Ql0h6f9V4nlg083FRwPUc6WpUSLLs/sS4l/k3Foo0J5mM8lid2PjRbIJym3SqGTCc6p2EfOjpmNwjaFTpzuPddxdsaNEwm3apSWoZpNcTDcO8IqhYZxWqw4bD5jNu0WBVGhkBPHk0U68JTMJTymNUppalekNt4HTnbSg+PcSdSKY8fp6YzES/2ts6Vk6FcRrxFJX1UeG/3BH54iz6s94rpx4M27NKYpgkkYLnd5ynPtUyoGfgfhnL1aRqi+ZbjV3suw7zVfYJK71WlZJLRvkxrGyBKJvm+oi32b1DCFQlff+iXDvwx0xpHChKs56VsQ1j7hSKGTLNJ0Yp3XcqzQCmZxfJo9uqlQfsN4Ack4np/3sDuP5+bLUfnRY97urj+30Sgel66XSMdOsk7lmUHlAo8UeillfK/9llgWaRhIh4Ax+Q11jy2ZCFVM82kacsrI+t5xlTPHxFJ+PeC6mD+C8fprupo+vzrO52G1OoxuPH3TGEuWb34i0DlB7p8qXCjfmvsqrO3pZVVMMhPCYIvEhIi5r7vJV9/nYl68S5O1MNupzqF+m45Go8fDx8dK/h1Nay/oNMumjBCagviisNoPc4WkyiDStMwC68fxxElDymUoi/rCjVYoNT7qP9NvnQicnWUGaxTOSoVHVHFvNq8UXqrXG6pRaFjVP7jRJJMiu0sL8y8rZMcKq14sU8I6ViPO6iuN4kzhoZt5WBgVBsG/Vkj3ZdabTIwGHW8zjfG0+N3k/CuC/6qtUqFccB4fn2/Z1ggzExZUjnVYzbirClV3FWbfO+O8Bb6vFMrGAlrqNnNVhToKhavPt9enn90M0q2xZPIfFKp0ofksc517E+hfVgrlqjOvfgzTFotKhVP9YNFrJwJLo7MuFXa0xORkxmjPeoX29mGxeKytVRgbS4i1Cu+LBT6zVWGxWxF5VCj2OMsUBmbLr/iicHdDoXtL4aZnhWUnli6kUrioFBpZtQqTWwoXfStMuDGmOAopheYuolIY+dcVmmK9QtNWfwrlj8zUMZwHxiodGV348jhMfRWe8PKiPOJmPlP40j1IRV6UCl2urtMydTrK65lsIc6L+mxv2pI/0befK0VHHukx0XwelT5qHvuNvMU6y3Muri4msjDOkbJmpqhvRse79cJTgR7z5rj66H3ZlsqivjzkXPmnDG1QhpHyCWNfNjD2zb071xyDrLtYoU73i3g8z0z4iNmWlI+ZXkdZEcSw7z88fb3z61txo/mY7Ul8LI+LWsZSlJExOSMHmZO/xXjDhdq/hQgtvNtkSKLxeGZdAg0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABgMPwD+p9Y6vy1tXkAAAAASUVORK5CYII= | |
| """ | |
| clean_name = "".join(c for c in employee_data['Row Labels'] if c.isalnum() or c in (' ', '-', '_')).rstrip() | |
| clean_name = clean_name.replace(' ', '_') | |
| start_dt = datetime.strptime(start_date, '%Y-%m-%d') | |
| end_dt = datetime.strptime(end_date, '%Y-%m-%d') | |
| date_period = f"{start_dt.strftime('%d%b%Y')}-{end_dt.strftime('%d%b%Y')}" | |
| filename = f"{clean_name}_{date_period}_Glimmr_Invoice.pdf" | |
| filepath = os.path.join(output_dir, filename) | |
| doc = SimpleDocTemplate(filepath, pagesize=A4, rightMargin=72, leftMargin=72, topMargin=72, bottomMargin=18) | |
| story = [] | |
| try: | |
| if GLIMMR_LOGO_BASE64.strip(): | |
| logo_data = base64.b64decode(GLIMMR_LOGO_BASE64.strip()) | |
| logo_stream = BytesIO(logo_data) | |
| logo_img = Image(logo_stream) | |
| logo_img.drawWidth = 2*inch | |
| logo_img.drawHeight = 1*inch | |
| story.append(logo_img) | |
| story.append(Spacer(1, 12)) | |
| except Exception as e: | |
| print(f"PDF Logo embedding failed: {e}") | |
| styles = getSampleStyleSheet() | |
| title_style = ParagraphStyle('CustomTitle', parent=styles['Heading1'], fontSize=18, spaceAfter=30, textColor=HexColor('#4472C4'), alignment=1) | |
| header_style = ParagraphStyle('CustomHeader', parent=styles['Heading2'], fontSize=14, spaceAfter=12, textColor=HexColor('#4472C4')) | |
| normal_style = styles['Normal'] | |
| story.append(Paragraph('<b>Glimmr Ltd</b>', title_style)) | |
| address_text = "108 Westbourne Terrace<br/>London<br/>W2 6QJ<br/>support@glimmr.co.uk<br/>020 8158 8505" | |
| story.append(Paragraph(address_text, normal_style)) | |
| story.append(Spacer(1, 20)) | |
| invoice_number = generate_random_invoice_number() | |
| current_date = datetime.now().strftime('%d/%m/%Y') | |
| invoice_header_data = [['CLEANER INVOICE', ''], ['Invoice #: ' + invoice_number, 'Date: ' + current_date]] | |
| invoice_header_table = Table(invoice_header_data, colWidths=[3*inch, 3*inch]) | |
| invoice_header_table.setStyle(TableStyle([ | |
| ('BACKGROUND', (0, 0), (-1, 0), HexColor('#4472C4')), | |
| ('TEXTCOLOR', (0, 0), (-1, 0), colors.white), | |
| ('ALIGN', (0, 0), (-1, -1), 'CENTER'), | |
| ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'), | |
| ('FONTSIZE', (0, 0), (-1, 0), 14), | |
| ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'), | |
| ('FONTSIZE', (0, 1), (-1, -1), 12), | |
| ('BOTTOMPADDING', (0, 0), (-1, -1), 12), | |
| ('GRID', (0, 0), (-1, -1), 1, colors.black) | |
| ])) | |
| story.append(invoice_header_table) | |
| story.append(Spacer(1, 20)) | |
| story.append(Paragraph('<b>Payee\'s Name:</b>', header_style)) | |
| story.append(Paragraph(employee_data['Row Labels'], normal_style)) | |
| story.append(Spacer(1, 20)) | |
| due_date = (datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)).strftime('%d/%m/%Y') | |
| payment_data = [ | |
| ['Payment Period', 'Hours', '£ / Hour', 'Amount'], | |
| [f"{start_date} to {end_date}", | |
| str(employee_data['Sum of Hours Worked']), | |
| f"£ {employee_data['Average of Hourly Rate']:.2f}", | |
| f"£ {employee_data['Sum of Total']:.2f}"] | |
| ] | |
| payment_table = Table(payment_data, colWidths=[2*inch, 1*inch, 1*inch, 1.5*inch]) | |
| payment_table.setStyle(TableStyle([ | |
| ('BACKGROUND', (0, 0), (-1, 0), HexColor('#4472C4')), | |
| ('TEXTCOLOR', (0, 0), (-1, 0), colors.white), | |
| ('ALIGN', (0, 0), (-1, -1), 'CENTER'), | |
| ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'), | |
| ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'), | |
| ('FONTSIZE', (0, 0), (-1, -1), 10), | |
| ('BOTTOMPADDING', (0, 0), (-1, -1), 8), | |
| ('GRID', (0, 0), (-1, -1), 1, colors.black) | |
| ])) | |
| story.append(payment_table) | |
| story.append(Spacer(1, 20)) | |
| total_data = [ | |
| ['Total Labor', f"£ {employee_data['Sum of Total']:.2f}"], | |
| ['Tax (Withholding)', '£ 0.00'], | |
| ['TOTAL', f"£ {employee_data['Sum of Total']:.2f}"] | |
| ] | |
| total_table = Table(total_data, colWidths=[3*inch, 2*inch]) | |
| total_table.setStyle(TableStyle([ | |
| ('ALIGN', (0, 0), (-1, -1), 'CENTER'), | |
| ('FONTNAME', (0, 0), (-1, 1), 'Helvetica'), | |
| ('FONTNAME', (0, 2), (-1, 2), 'Helvetica-Bold'), | |
| ('FONTSIZE', (0, 0), (-1, -1), 12), | |
| ('BOTTOMPADDING', (0, 0), (-1, -1), 8), | |
| ('GRID', (0, 0), (-1, -1), 1, colors.black), | |
| ('BACKGROUND', (0, 2), (-1, 2), colors.lightgrey) | |
| ])) | |
| story.append(total_table) | |
| story.append(Spacer(1, 20)) | |
| story.append(Paragraph(f'<b>Payment is due on {due_date}</b>', normal_style)) | |
| story.append(Spacer(1, 20)) | |
| story.append(Paragraph('<b>Comments or Special Instructions:</b>', header_style)) | |
| story.append(Paragraph('Payments to be made every other Saturday (or as close to these days).', normal_style)) | |
| story.append(Paragraph('Please let us know immediately if there are any issues.', normal_style)) | |
| doc.build(story) | |
| return filepath | |
| def create_invoices_from_template_with_logo(report_data, start_date, end_date): | |
| wb = openpyxl.Workbook() | |
| wb.remove(wb.active) | |
| # Create Payments sheet | |
| payments_ws = wb.create_sheet(title="Payments") | |
| payments_ws['C1'] = 'DO NOT ALTER THIS TEMPLATE' | |
| payments_ws['C1'].font = Font(bold=True) | |
| headers = ['First Name', 'Last Name', 'Stripe Name', 'Reference', 'Amount (GBP)', 'Remarks'] | |
| for col, header in enumerate(headers, 1): | |
| cell = payments_ws.cell(row=2, column=col, value=header) | |
| cell.font = Font(bold=True) | |
| for row_idx, employee in enumerate(report_data[report_data['Row Labels'] != 'Grand Total'].itertuples(), 3): | |
| name_parts = employee[1].split(' ', 1) | |
| first_name = name_parts[0] if name_parts else '' | |
| last_name = name_parts[1] if len(name_parts) > 1 else '' | |
| payments_ws.cell(row=row_idx, column=1, value=first_name) | |
| payments_ws.cell(row=row_idx, column=2, value=last_name) | |
| payments_ws.cell(row=row_idx, column=3, value=employee[1]) | |
| payments_ws.cell(row=row_idx, column=4, value='') | |
| payments_ws.cell(row=row_idx, column=5, value=employee[4]) # Sum of Total | |
| payments_ws.cell(row=row_idx, column=6, value='') | |
| # Create Pivot sheet | |
| pivot_ws = wb.create_sheet(title="Pivot") | |
| pivot_ws['A1'] = f"{start_date} to {end_date}" | |
| pivot_ws['A1'].font = Font(bold=True) | |
| due_date = (datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)).strftime('%d/%m/%Y') | |
| pivot_ws['A2'] = f"Payment is due on {due_date}" | |
| # MODIFIED: Updated header name as requested | |
| pivot_headers = ['Name', 'Fixed Hourly Rate(without TIP)', 'Average of Hourly Rate', 'Sum of Hours Worked', 'Sum of Total'] | |
| for col, header in enumerate(pivot_headers, 1): | |
| cell = pivot_ws.cell(row=3, column=col, value=header) | |
| cell.font = Font(bold=True) | |
| for row_idx, employee in enumerate(report_data.itertuples(index=False), 4): | |
| # The tuple index corresponds to the column order in the final_invoice DataFrame | |
| # [0]: Row Labels, [1]: Avg Rate, [2]: Sum Hours, [3]: Sum Total, [4]: Fixed Rate | |
| pivot_ws.cell(row=row_idx, column=1, value=employee[0]) # Name | |
| pivot_ws.cell(row=row_idx, column=2, value=employee[4]) # Fixed Hourly Rate(without TIP) | |
| pivot_ws.cell(row=row_idx, column=3, value=employee[1]) # Average of Hourly Rate | |
| pivot_ws.cell(row=row_idx, column=4, value=employee[2]) # Sum of Hours Worked | |
| pivot_ws.cell(row=row_idx, column=5, value=employee[3]) # Sum of Total | |
| # Create individual invoice sheets | |
| individual_employees = report_data[report_data['Row Labels'] != 'Grand Total'] | |
| for _, employee in individual_employees.iterrows(): | |
| create_professional_individual_invoice(wb, employee, start_date, end_date) | |
| return wb | |
| def process_invoice_files_with_professional_excel(file1, file2, file3, bookings_file, start_date, end_date): | |
| try: | |
| employee_files = [f for f in [file1, file2, file3] if f is not None] | |
| if not employee_files: | |
| return None, None, "❗ **Error:** At least one Employee CSV must be uploaded." | |
| if bookings_file is None: | |
| return None, None, "❗ **Error:** Bookings CSV is required." | |
| employee_dfs = [pd.read_csv(ef.name) for ef in employee_files] | |
| all_employee_data = pd.concat(employee_dfs, ignore_index=True) | |
| # Add the new Fixed Hour Rate column | |
| all_employee_data['Fixed Hour Rate'] = all_employee_data['Hourly Rate'] | |
| pdf_bookings = pd.read_csv(bookings_file.name) | |
| all_employee_data['Date'] = pd.to_datetime(all_employee_data['Date'], dayfirst=True, errors='coerce') | |
| pdf_bookings['Date'] = pd.to_datetime(pdf_bookings['Date'], dayfirst=True, errors='coerce') | |
| start_date_dt = pd.to_datetime(start_date) | |
| end_date_dt = pd.to_datetime(end_date) | |
| filtered_data = all_employee_data[(all_employee_data['Date'] >= start_date_dt) & (all_employee_data['Date'] <= end_date_dt)].copy() | |
| filtered_bookings = pdf_bookings[(pdf_bookings['Date'] >= start_date_dt) & (pdf_bookings['Date'] <= end_date_dt)].copy() | |
| def extract_employee_name(team_string): | |
| if pd.isna(team_string): return None | |
| name = team_string.split(',')[0].strip() | |
| return name.split('(')[0].strip() | |
| filtered_bookings['Employee_Name'] = filtered_bookings['Teams Assigned (without IDs)'].apply(extract_employee_name) | |
| tips_summary = filtered_bookings.groupby('Employee_Name')['Tip'].sum().reset_index() | |
| tips_with_amount = tips_summary[tips_summary['Tip'] > 0] | |
| if not tips_with_amount.empty: | |
| # Add Fixed Hour Rate to tip rows as 0 | |
| tip_rows = [{'Name': row['Employee_Name'], 'Hourly Rate': 0, 'Hours Worked': 0, 'Total': row['Tip'], 'Fixed Hour Rate': 0} for _, row in tips_with_amount.iterrows()] | |
| tip_df = pd.DataFrame(tip_rows) | |
| final_data = pd.concat([filtered_data, tip_df], ignore_index=True) | |
| else: | |
| final_data = filtered_data.copy() | |
| # Update aggregation to include Fixed Hour Rate | |
| report = final_data.groupby('Name').agg({ | |
| 'Hourly Rate': 'mean', | |
| 'Hours Worked': 'sum', | |
| 'Total': 'sum', | |
| 'Fixed Hour Rate': 'first' # Use 'first' to get the original rate | |
| }).reset_index() | |
| # MODIFIED: Update column renaming as requested | |
| report = report.rename(columns={ | |
| 'Name': 'Row Labels', | |
| 'Hourly Rate': 'Average of Hourly Rate', | |
| 'Hours Worked': 'Sum of Hours Worked', | |
| 'Total': 'Sum of Total', | |
| 'Fixed Hour Rate': 'Fixed Hourly Rate(without TIP)' | |
| }) | |
| report['Average of Hourly Rate'] = report['Average of Hourly Rate'].round(8) | |
| report['Sum of Hours Worked'] = report['Sum of Hours Worked'].round(2) | |
| report['Sum of Total'] = report['Sum of Total'].round(2) | |
| total_hours = report['Sum of Hours Worked'].sum() | |
| total_sum = report['Sum of Total'].sum() | |
| # MODIFIED: Update Grand Total DataFrame to use new column name | |
| grand_total = pd.DataFrame({ | |
| 'Row Labels': ['Grand Total'], | |
| 'Average of Hourly Rate': [total_sum / total_hours if total_hours > 0 else 0], | |
| 'Sum of Hours Worked': [total_hours], | |
| 'Sum of Total': [total_sum], | |
| 'Fixed Hourly Rate(without TIP)': [None] | |
| }) | |
| final_invoice = pd.concat([report, grand_total], ignore_index=True) | |
| # Reorder columns for the create_invoices function to process correctly | |
| # The order here determines the order in `itertuples` | |
| final_invoice = final_invoice[['Row Labels', 'Average of Hourly Rate', 'Sum of Hours Worked', 'Sum of Total', 'Fixed Hourly Rate(without TIP)']] | |
| wb = create_invoices_from_template_with_logo(final_invoice, start_date, end_date) | |
| with tempfile.NamedTemporaryFile(delete=False, suffix='.xlsx') as tmp: | |
| wb.save(tmp.name) | |
| temp_excel_file_path = tmp.name | |
| # Create PDF files for individual cleaners | |
| pdf_temp_dir = tempfile.mkdtemp() | |
| individual_employees = final_invoice[final_invoice['Row Labels'] != 'Grand Total'] | |
| pdf_files = [] | |
| for _, employee in individual_employees.iterrows(): | |
| pdf_path = create_individual_pdf_invoice(employee, start_date, end_date, pdf_temp_dir) | |
| pdf_files.append(pdf_path) | |
| # Create a ZIP file containing all PDFs | |
| zip_temp_file = tempfile.NamedTemporaryFile(delete=False, suffix='.zip') | |
| with zipfile.ZipFile(zip_temp_file.name, 'w') as zipf: | |
| for pdf_file in pdf_files: | |
| zipf.write(pdf_file, os.path.basename(pdf_file)) | |
| individual_employees_count = len(final_invoice[final_invoice['Row Labels'] != 'Grand Total']) | |
| summary_text = f"🎉 **Professional Invoice Generated Successfully!**\n\n- ✅ **Individual Professional Invoices** for each employee ({individual_employees_count} Excel sheets)\n- ✅ **Individual PDF Invoices** for each cleaner ({individual_employees_count} PDFs)\n- ✅ **Payments Summary & Pivot Data** sheets included\n- ✅ **Glimmr Logo & Branding** applied\n\n**Summary for {start_date} to {end_date}:**\n- Total hours: {total_hours:.2f}\n- Total amount: £{total_sum:.2f}\n\n**Files Generated:**\n- Excel file with all data and individual sheets\n- ZIP file containing individual PDF invoices for each cleaner" | |
| return temp_excel_file_path, zip_temp_file.name, summary_text | |
| except Exception as e: | |
| return None, None, f"❌ **An error occurred:**\n\n{str(e)}\n\nPlease check your input files and date formats (YYYY-MM-DD)." | |
| with gr.Blocks(title="Complete Professional Invoice Generator", theme=gr.themes.Soft()) as interface: | |
| gr.Markdown("# 🧾 Complete Professional Invoice Generator for Launch27 Data") | |
| with gr.Row(): | |
| with gr.Column(scale=2): | |
| gr.Markdown("### 📁 Upload CSV Files") | |
| file1 = gr.File(label="Upload 1.csv (Optional Employee Data)", file_types=[".csv"]) | |
| file2 = gr.File(label="Upload 2.csv (Optional Employee Data)", file_types=[".csv"]) | |
| file3 = gr.File(label="Upload 3.csv (Optional Employee Data)", file_types=[".csv"]) | |
| bookings_file = gr.File(label="Upload Bookings CSV (Required)", file_types=[".csv"]) | |
| with gr.Column(scale=1): | |
| gr.Markdown("### 📅 Select Date Range") | |
| start_date = gr.Textbox(label="Start Date (YYYY-MM-DD)", value="2025-08-09") | |
| end_date = gr.Textbox(label="End Date (YYYY-MM-DD)", value="2025-08-22") | |
| process_btn = gr.Button("🚀 Generate Professional Invoice Excel & PDFs", variant="primary", size="lg") | |
| gr.Markdown("---") | |
| gr.Markdown("### 📋 Results") | |
| with gr.Row(): | |
| summary_output = gr.Markdown(label="Summary") | |
| with gr.Row(): | |
| download_excel_file = gr.File(label="📥 Download Professional Invoice Excel (.xlsx)") | |
| download_pdf_zip = gr.File(label="📥 Download Individual PDF Invoices (.zip)") | |
| process_btn.click( | |
| fn=process_invoice_files_with_professional_excel, | |
| inputs=[file1, file2, file3, bookings_file, start_date, end_date], | |
| outputs=[download_excel_file, download_pdf_zip, summary_output] | |
| ) | |
| with gr.Accordion("📖 How to Use", open=False): | |
| gr.Markdown(""" | |
| 1. **Upload Files**: Upload 1-3 employee CSVs and the required bookings CSV. | |
| 2. **Select Dates**: Enter the date range in YYYY-MM-DD format. | |
| 3. **Generate**: Click the button. | |
| 4. **Download**: Your complete Excel file and ZIP of individual PDF invoices will appear for download. | |
| **New Features:** | |
| - Individual PDF invoices for each cleaner with the naming convention: `Cleaner_Name_Dateperiod_Glimmr_Invoice.pdf` | |
| - All PDF files are packaged in a convenient ZIP file for download | |
| - Professional PDF formatting matching the Excel invoice design | |
| """) | |
| if __name__ == "__main__": | |
| interface.launch(debug=True) |