275 lines
11 KiB
Python
275 lines
11 KiB
Python
import sqlite3
|
|
import os
|
|
from datetime import date, datetime
|
|
from reportlab.lib.pagesizes import letter
|
|
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer
|
|
from reportlab.lib import colors
|
|
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
|
|
|
|
def connect_db():
|
|
"""
|
|
Connects to the SQLite database file and returns the connection object.
|
|
If the file does not exist, it prints an an error message.
|
|
"""
|
|
db_file = 'time_tracker.db'
|
|
if not os.path.exists(db_file):
|
|
print(f"Error: Database file '{db_file}' not found. Please run the database creation script first.")
|
|
return None
|
|
return sqlite3.connect(db_file)
|
|
|
|
def generate_invoice():
|
|
"""
|
|
Guides the user through selecting a client and month to generate a PDF invoice.
|
|
Calculates costs, creates the PDF, and updates the database.
|
|
"""
|
|
conn = connect_db()
|
|
if not conn:
|
|
return
|
|
|
|
try:
|
|
cursor = conn.cursor()
|
|
|
|
# Display active clients for selection
|
|
cursor.execute('SELECT client_id, client_name FROM clients WHERE active = 1 ORDER BY client_name')
|
|
active_clients = cursor.fetchall()
|
|
|
|
if not active_clients:
|
|
print("No active clients found to generate an invoice for.")
|
|
return
|
|
|
|
print("\n--- Select a Client to Invoice ---")
|
|
for client_id, client_name in active_clients:
|
|
print(f"{client_id}: {client_name}")
|
|
print("0: Exit")
|
|
print("----------------------------------\n")
|
|
|
|
while True:
|
|
try:
|
|
choice = input("Enter the ID of the client (or 0 to exit): ")
|
|
if choice == '0':
|
|
print("Exiting invoice generation.")
|
|
return
|
|
|
|
client_id = int(choice)
|
|
# Check if the entered ID is in the list of active clients
|
|
if any(c[0] == client_id for c in active_clients):
|
|
break
|
|
else:
|
|
print("Invalid client ID. Please enter a valid ID from the list.")
|
|
except ValueError:
|
|
print("Invalid input. Please enter a number.")
|
|
|
|
# Get the month from the user
|
|
month_year_str = input("Enter the month to invoice (YYYY-MM): ")
|
|
if len(month_year_str) != 7 or month_year_str[4] != '-':
|
|
print("Invalid date format. Please use YYYY-MM.")
|
|
return
|
|
|
|
# Fetch client details and billing rate
|
|
cursor.execute('SELECT * FROM clients WHERE client_id = ?', (client_id,))
|
|
client_data = cursor.fetchone()
|
|
client_name, billing_rate = client_data[1], client_data[7]
|
|
|
|
# Fetch time entries for the selected client and month
|
|
cursor.execute('''
|
|
SELECT date, hours, description, project, entry_id
|
|
FROM time_tracking
|
|
WHERE client_id = ? AND date LIKE ? AND invoiced = 0
|
|
ORDER BY date
|
|
''', (client_id, f"{month_year_str}%"))
|
|
|
|
time_entries = cursor.fetchall()
|
|
|
|
if not time_entries:
|
|
print("No new time entries found for this client and month.")
|
|
return
|
|
|
|
# Group entries by date and calculate daily totals
|
|
daily_summary = {}
|
|
for entry in time_entries:
|
|
entry_date = entry[0]
|
|
if entry_date not in daily_summary:
|
|
daily_summary[entry_date] = {'hours': 0.0, 'descriptions': [], 'projects': [], 'entry_ids': []}
|
|
|
|
daily_summary[entry_date]['hours'] += entry[1]
|
|
daily_summary[entry_date]['descriptions'].append(f"{entry[2]}")
|
|
daily_summary[entry_date]['projects'].append(f"{entry[3]}")
|
|
daily_summary[entry_date]['entry_ids'].append(entry[4])
|
|
|
|
# Prepare data for PDF and calculate invoice total
|
|
invoice_total = 0.0
|
|
# Reorder table headers as requested
|
|
data_for_pdf = [['Date', 'Description', 'Project', 'Hours', 'Rate', 'Amount']]
|
|
|
|
for daily_date, summary in daily_summary.items():
|
|
daily_hours = summary['hours']
|
|
daily_total = daily_hours * billing_rate
|
|
invoice_total += daily_total
|
|
# Join descriptions and projects with <br/> for ReportLab to create new lines
|
|
descriptions_str = "<br/>".join(summary['descriptions'])
|
|
projects_str = "<br/>".join(summary['projects'])
|
|
|
|
# Use Paragraph to handle multi-line descriptions and projects in the PDF
|
|
data_for_pdf.append([
|
|
daily_date,
|
|
Paragraph(descriptions_str, getSampleStyleSheet()['Normal']),
|
|
Paragraph(projects_str, getSampleStyleSheet()['Normal']),
|
|
f"{daily_hours:.2f}",
|
|
f"${billing_rate:.2f}",
|
|
f"${daily_total:.2f}"
|
|
])
|
|
|
|
# Display the invoice summary before creating the PDF
|
|
print("\n--- Invoice Summary ---")
|
|
for row in data_for_pdf[1:]: # Skip header row for print
|
|
print(f"Date: {row[0]}, Hours: {row[3]}, Total: {row[5]}")
|
|
print(f"\nTotal Invoice Amount: ${invoice_total:.2f}")
|
|
|
|
# Confirmation to create the PDF
|
|
confirm = input("Generate PDF and update database? (yes/no): ").lower()
|
|
if confirm not in ['yes', 'y']:
|
|
print("Invoice generation cancelled.")
|
|
return
|
|
|
|
# Get the desired save directory from the user with a default value
|
|
default_dir = os.path.expanduser("~/Documents/0 - Inbox")
|
|
save_dir = input(f"Enter directory to save PDF (default: {default_dir}): ")
|
|
if not save_dir:
|
|
save_dir = default_dir
|
|
|
|
# Ensure the directory exists
|
|
if not os.path.exists(save_dir):
|
|
os.makedirs(save_dir)
|
|
print(f"Created directory: {save_dir}")
|
|
|
|
# Generate unique invoice ID and full file path
|
|
invoice_id = datetime.now().strftime('%Y%m%d%H%M%S')
|
|
file_name = f"Invoice_{invoice_id}_{client_name.replace(' ', '_')}.pdf"
|
|
full_file_path = os.path.join(save_dir, file_name)
|
|
|
|
# Create PDF and update database
|
|
create_pdf(full_file_path, client_data, invoice_total, data_for_pdf, invoice_id)
|
|
|
|
# Update database
|
|
update_database_after_invoice(conn, cursor, time_entries, client_id, invoice_total)
|
|
|
|
print(f"\nInvoice successfully created at '{full_file_path}' and database has been updated.")
|
|
|
|
except sqlite3.Error as e:
|
|
print(f"An error occurred: {e}")
|
|
finally:
|
|
if conn:
|
|
conn.close()
|
|
|
|
def create_pdf(file_name, client_data, invoice_total, data_for_pdf, invoice_id):
|
|
"""
|
|
Creates the PDF document with the invoice details.
|
|
"""
|
|
doc = SimpleDocTemplate(file_name, pagesize=letter)
|
|
elements = []
|
|
|
|
# Define styles for the document
|
|
styles = getSampleStyleSheet()
|
|
styles.add(ParagraphStyle(name='InvoiceTitle', fontSize=24, fontName='Helvetica-Bold'))
|
|
styles.add(ParagraphStyle(name='ClientInfo', fontSize=12))
|
|
styles.add(ParagraphStyle(name='AmountDue', fontSize=12, alignment=2, fontName='Helvetica-Bold'))
|
|
|
|
today_date_str = date.today().strftime('%m/%d/%Y')
|
|
|
|
# Top header table (INVOICE and From)
|
|
header_top_table = Table([
|
|
[
|
|
Paragraph("INVOICE", styles['InvoiceTitle']),
|
|
Paragraph("<b>From:</b><br/>Matt Speer<br/>2313 Hunters Cove<br/>Vestavia Hills, AL 35216", styles['ClientInfo'])
|
|
]
|
|
], colWidths=[250, 250])
|
|
|
|
header_top_table.setStyle(TableStyle([
|
|
('VALIGN', (0, 0), (-1, -1), 'TOP'),
|
|
('LEFTPADDING', (0, 0), (-1, -1), 0),
|
|
('RIGHTPADDING', (0, 0), (-1, -1), 0),
|
|
('ALIGN', (1, 0), (1, 0), 'RIGHT')
|
|
]))
|
|
|
|
elements.append(header_top_table)
|
|
elements.append(Spacer(1, 20))
|
|
|
|
# Construct the client address string conditionally
|
|
client_address_str = f"<b>Invoice For:</b><br/>{client_data[1]}<br/>{client_data[2]}"
|
|
if client_data[3]: # Check if street_address_2 is not empty
|
|
client_address_str += f"<br/>{client_data[3]}"
|
|
client_address_str += f"<br/>{client_data[4]}, {client_data[5]} {client_data[6]}"
|
|
|
|
# Bottom header table (Invoice ID/Date and Invoice For)
|
|
header_bottom_table = Table([
|
|
[
|
|
Paragraph(f"Invoice ID: {invoice_id}<br/><br/>Invoice Date: {today_date_str}", styles['ClientInfo']),
|
|
Paragraph(client_address_str, styles['ClientInfo'])
|
|
]
|
|
], colWidths=[250, 250])
|
|
|
|
header_bottom_table.setStyle(TableStyle([
|
|
('VALIGN', (0, 0), (-1, -1), 'TOP'),
|
|
('LEFTPADDING', (0, 0), (-1, -1), 0),
|
|
('RIGHTPADDING', (0, 0), (-1, -1), 0),
|
|
('ALIGN', (1, 0), (1, 0), 'RIGHT')
|
|
]))
|
|
|
|
elements.append(header_bottom_table)
|
|
elements.append(Spacer(1, 20))
|
|
|
|
# Create the table for time entries
|
|
table = Table(data_for_pdf, colWidths=[80, 200, 80, 40, 50, 50])
|
|
table.setStyle(TableStyle([
|
|
('TEXTCOLOR', (0, 0), (-1, 0), colors.black),
|
|
('ALIGN', (0, 0), (-1, -1), 'LEFT'),
|
|
('ALIGN', (0, 0), (-1, -0), 'LEFT'),
|
|
('VALIGN', (0, 0), (-1, 0), 'MIDDLE'), # Vertical align the header to the middle
|
|
('VALIGN', (0, 1), (-1, -1), 'TOP'),
|
|
('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
|
|
('FONTSIZE', (0, 0), (-1, 0), 10),
|
|
('BOTTOMPADDING', (0, 0), (-1, 0), 12),
|
|
('LINEABOVE', (0, 0), (-1, 0), 1, colors.black),
|
|
('LINEBELOW', (0, 0), (-1, 0), 1, colors.black),
|
|
]))
|
|
|
|
elements.append(table)
|
|
elements.append(Spacer(1, 12))
|
|
|
|
# Line before the total amount due
|
|
line_table = Table([['']], colWidths=[500]) # Set width to match the time entries table
|
|
line_table.setStyle(TableStyle([
|
|
('LINEBELOW', (0, 0), (-1, -1), 1, colors.black),
|
|
('BOTTOMPADDING', (0, 0), (-1, -1), 0)
|
|
]))
|
|
elements.append(line_table)
|
|
|
|
elements.append(Spacer(1, 12))
|
|
|
|
# Add the total amount due section after the table
|
|
elements.append(Paragraph(f"AMOUNT DUE: ${invoice_total:.2f}", styles['AmountDue']))
|
|
|
|
# Build the document
|
|
doc.build(elements)
|
|
|
|
def update_database_after_invoice(conn, cursor, time_entries, client_id, invoice_total):
|
|
"""
|
|
Updates the 'invoiced' field for time entries and the client's balance.
|
|
"""
|
|
entry_ids = [entry[4] for entry in time_entries]
|
|
|
|
# Update time entries to be invoiced
|
|
cursor.executemany('UPDATE time_tracking SET invoiced = 1 WHERE entry_id = ?', [(entry_id,) for entry_id in entry_ids])
|
|
|
|
# Update the client's balance
|
|
cursor.execute('SELECT balance FROM clients WHERE client_id = ?', (client_id,))
|
|
current_balance = cursor.fetchone()[0]
|
|
|
|
new_balance = current_balance - invoice_total
|
|
cursor.execute('UPDATE clients SET balance = ? WHERE client_id = ?', (new_balance, client_id))
|
|
|
|
conn.commit()
|
|
|
|
if __name__ == "__main__":
|
|
generate_invoice()
|