|
|
import subprocess |
|
|
import sys |
|
|
|
|
|
try: |
|
|
import openpyxl |
|
|
except ImportError: |
|
|
subprocess.check_call([sys.executable, "-m", "pip", "install", "openpyxl"]) |
|
|
import openpyxl |
|
|
|
|
|
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""" |
|
|
|
|
|
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}") |
|
|
|
|
|
ws['A6'] = 'Glimmr Ltd' |
|
|
ws['A6'].font = Font(bold=True, size=16) |
|
|
|
|
|
|
|
|
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') |
|
|
|
|
|
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') |
|
|
|
|
|
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') |
|
|
|
|
|
ws.merge_cells('E7:F7') |
|
|
ws['E7'] = generate_random_invoice_number() |
|
|
ws['E7'].alignment = Alignment(horizontal='center', vertical='center') |
|
|
|
|
|
ws.merge_cells('G7:I7') |
|
|
ws['G7'] = datetime.now().strftime('%d/%m/%y') |
|
|
ws['G7'].alignment = Alignment(horizontal='center', vertical='center') |
|
|
|
|
|
ws['A8'] = '108 Westbourne Terrace' |
|
|
ws['A9'] = 'London' |
|
|
ws['A10'] = 'W2 6QJ' |
|
|
ws['A11'] = 'support@glimmr.co.uk' |
|
|
ws['A12'] = '020 8158 8505' |
|
|
|
|
|
ws['A14'] = "Payee's Name" |
|
|
ws['A14'].font = Font(bold=True) |
|
|
ws['A15'] = employee_data['Row Labels'] |
|
|
ws['A15'].font = Font(size=11) |
|
|
|
|
|
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') |
|
|
|
|
|
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') |
|
|
|
|
|
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}" |
|
|
|
|
|
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)' |
|
|
|
|
|
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) |
|
|
|
|
|
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.' |
|
|
|
|
|
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) |
|
|
|
|
|
for row in range(20, 22): |
|
|
for col in range(1, 10): |
|
|
ws.cell(row=row, column=col).border = thin_border |
|
|
|
|
|
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) |
|
|
|
|
|
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]) |
|
|
payments_ws.cell(row=row_idx, column=6, value='') |
|
|
|
|
|
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}" |
|
|
|
|
|
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): |
|
|
|
|
|
|
|
|
pivot_ws.cell(row=row_idx, column=1, value=employee[0]) |
|
|
pivot_ws.cell(row=row_idx, column=2, value=employee[4]) |
|
|
pivot_ws.cell(row=row_idx, column=3, value=employee[1]) |
|
|
pivot_ws.cell(row=row_idx, column=4, value=employee[2]) |
|
|
pivot_ws.cell(row=row_idx, column=5, value=employee[3]) |
|
|
|
|
|
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) |
|
|
|
|
|
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: |
|
|
|
|
|
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() |
|
|
|
|
|
|
|
|
report = final_data.groupby('Name').agg({ |
|
|
'Hourly Rate': 'mean', |
|
|
'Hours Worked': 'sum', |
|
|
'Total': 'sum', |
|
|
'Fixed Hour Rate': 'first' |
|
|
}).reset_index() |
|
|
|
|
|
|
|
|
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() |
|
|
|
|
|
|
|
|
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) |
|
|
|
|
|
|
|
|
|
|
|
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 |
|
|
|
|
|
|
|
|
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) |
|
|
|
|
|
|
|
|
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) |
