290 lines
9.8 KiB
Python
290 lines
9.8 KiB
Python
import sqlite3
|
|
import os
|
|
|
|
def connect_db():
|
|
"""
|
|
Connects to the SQLite database file and returns the connection object.
|
|
If the file does not exist, it prints 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 display_client_details(client_data):
|
|
"""
|
|
Displays the details of a single client in a readable format.
|
|
"""
|
|
print("\n--- Client Details ---")
|
|
print(f"ID: {client_data[0]}")
|
|
print(f"Name: {client_data[1]}")
|
|
print(f"Street Address 1: {client_data[2]}")
|
|
print(f"Street Address 2: {client_data[3]}")
|
|
print(f"City: {client_data[4]}")
|
|
print(f"State: {client_data[5]}")
|
|
print(f"Zip Code: {client_data[6]}")
|
|
print(f"Billing Rate: {client_data[7]:.2f}")
|
|
print(f"Balance: {client_data[8]:.2f}")
|
|
print(f"Active: {'Yes' if client_data[9] else 'No'}")
|
|
print("----------------------\n")
|
|
|
|
def add_new_client():
|
|
"""
|
|
Prompts the user for information to create a new client and adds it to the database.
|
|
"""
|
|
conn = connect_db()
|
|
if not conn:
|
|
return
|
|
|
|
try:
|
|
cursor = conn.cursor()
|
|
print("\n--- Add New Client ---")
|
|
|
|
# Prompt for each field
|
|
client_name = input("Enter client name: ")
|
|
street_address_1 = input("Enter street address 1: ")
|
|
street_address_2 = input("Enter street address 2 (optional): ")
|
|
city = input("Enter city: ")
|
|
state = input("Enter state: ")
|
|
zip_code = input("Enter zip code: ")
|
|
|
|
# Validate billing rate and balance
|
|
while True:
|
|
try:
|
|
billing_rate = float(input("Enter billing rate: "))
|
|
break
|
|
except ValueError:
|
|
print("Invalid input. Please enter a number for billing rate.")
|
|
|
|
while True:
|
|
try:
|
|
balance = float(input("Enter starting balance: "))
|
|
break
|
|
except ValueError:
|
|
print("Invalid input. Please enter a number for balance.")
|
|
|
|
# Active status is boolean, use 1 or 0
|
|
active_input = input("Is the client active? (yes/no): ").lower()
|
|
active = 1 if active_input in ['yes', 'y'] else 0
|
|
|
|
# Insert the new client into the database
|
|
cursor.execute('''
|
|
INSERT INTO clients (client_name, street_address_1, street_address_2, city, state, zip_code, billing_rate, balance, active)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
''', (client_name, street_address_1, street_address_2, city, state, zip_code, billing_rate, balance, active))
|
|
|
|
conn.commit()
|
|
print("\nClient added successfully!")
|
|
|
|
# Fetch the newly created client to display it
|
|
last_id = cursor.lastrowid
|
|
cursor.execute('SELECT * FROM clients WHERE client_id = ?', (last_id,))
|
|
new_client = cursor.fetchone()
|
|
display_client_details(new_client)
|
|
|
|
except sqlite3.IntegrityError:
|
|
print("\nError: A client with that name already exists. Please choose a unique name.")
|
|
except sqlite3.Error as e:
|
|
print(f"\nAn error occurred: {e}")
|
|
finally:
|
|
if conn:
|
|
conn.close()
|
|
|
|
def edit_existing_client():
|
|
"""
|
|
Displays a list of clients and allows the user to select and edit one.
|
|
"""
|
|
conn = connect_db()
|
|
if not conn:
|
|
return
|
|
|
|
try:
|
|
cursor = conn.cursor()
|
|
cursor.execute('SELECT client_id, client_name FROM clients')
|
|
clients = cursor.fetchall()
|
|
|
|
if not clients:
|
|
print("No clients found to edit.")
|
|
return
|
|
|
|
print("\n--- Existing Clients ---")
|
|
for client_id, client_name in clients:
|
|
print(f"{client_id}: {client_name}")
|
|
print("----------------------\n")
|
|
|
|
while True:
|
|
try:
|
|
choice = input("Enter the ID of the client you want to edit (or 'back' to return to menu): ").strip().lower()
|
|
if choice == 'back':
|
|
return
|
|
|
|
client_id = int(choice)
|
|
cursor.execute('SELECT * FROM clients WHERE client_id = ?', (client_id,))
|
|
client_data = cursor.fetchone()
|
|
|
|
if client_data:
|
|
break
|
|
else:
|
|
print("Invalid client ID. Please try again.")
|
|
except ValueError:
|
|
print("Invalid input. Please enter a number or 'back'.")
|
|
|
|
display_client_details(client_data)
|
|
|
|
# The fields that can be edited, mapped to their database column names
|
|
field_map = {
|
|
'1': 'client_name',
|
|
'2': 'street_address_1',
|
|
'3': 'street_address_2',
|
|
'4': 'city',
|
|
'5': 'state',
|
|
'6': 'zip_code',
|
|
'7': 'billing_rate',
|
|
'8': 'balance',
|
|
'9': 'active'
|
|
}
|
|
|
|
print("\n--- Select a Field to Edit ---")
|
|
print("1: Client Name")
|
|
print("2: Street Address 1")
|
|
print("3: Street Address 2")
|
|
print("4: City")
|
|
print("5: State")
|
|
print("6: Zip Code")
|
|
print("7: Billing Rate")
|
|
print("8: Balance")
|
|
print("9: Active Status")
|
|
print("0: Cancel and Return to Main Menu")
|
|
|
|
while True:
|
|
edit_choice = input("Enter the number of the field you want to edit: ")
|
|
if edit_choice == '0':
|
|
print("Edit cancelled.")
|
|
return
|
|
|
|
column_name = field_map.get(edit_choice)
|
|
if column_name:
|
|
break
|
|
else:
|
|
print("Invalid choice. Please enter a valid number.")
|
|
|
|
# Get the new value from the user, prepopulating with old value
|
|
current_value_index = list(field_map.keys()).index(edit_choice) + 1
|
|
|
|
# Handle special cases for data types
|
|
if column_name in ['billing_rate', 'balance']:
|
|
new_value_str = input(f"Enter new value for {column_name.replace('_', ' ')} (current: {client_data[current_value_index]}): ")
|
|
if not new_value_str:
|
|
new_value = client_data[current_value_index]
|
|
else:
|
|
try:
|
|
new_value = float(new_value_str)
|
|
except ValueError:
|
|
print("Invalid input. Please enter a number.")
|
|
return
|
|
elif column_name == 'active':
|
|
new_value_input = input(f"Is the client active? (yes/no, current: {'Yes' if client_data[current_value_index] else 'No'}): ").lower()
|
|
if not new_value_input:
|
|
new_value = client_data[current_value_index]
|
|
else:
|
|
new_value = 1 if new_value_input in ['yes', 'y'] else 0
|
|
else:
|
|
new_value_input = input(f"Enter new value for {column_name.replace('_', ' ')} (current: {client_data[current_value_index]}): ")
|
|
if not new_value_input:
|
|
new_value = client_data[current_value_index]
|
|
else:
|
|
new_value = new_value_input
|
|
|
|
# Update the database
|
|
cursor.execute(f"UPDATE clients SET {column_name} = ? WHERE client_id = ?", (new_value, client_id))
|
|
conn.commit()
|
|
|
|
print("\nClient information updated successfully!")
|
|
|
|
# Fetch and display the updated client details
|
|
cursor.execute('SELECT * FROM clients WHERE client_id = ?', (client_id,))
|
|
updated_client = cursor.fetchone()
|
|
display_client_details(updated_client)
|
|
|
|
except sqlite3.Error as e:
|
|
print(f"\nAn error occurred: {e}")
|
|
finally:
|
|
if conn:
|
|
conn.close()
|
|
|
|
def display_client_info():
|
|
"""
|
|
Displays a list of clients and allows the user to select one to view its details.
|
|
"""
|
|
conn = connect_db()
|
|
if not conn:
|
|
return
|
|
|
|
try:
|
|
cursor = conn.cursor()
|
|
cursor.execute('SELECT client_id, client_name FROM clients ORDER BY client_name')
|
|
clients = cursor.fetchall()
|
|
|
|
if not clients:
|
|
print("No clients found.")
|
|
return
|
|
|
|
print("\n--- Existing Clients ---")
|
|
for client_id, client_name in clients:
|
|
print(f"{client_id}: {client_name}")
|
|
print("----------------------\n")
|
|
|
|
while True:
|
|
try:
|
|
choice = input("Enter the ID of the client you want to view (or 'back' to return to menu): ").strip().lower()
|
|
if choice == 'back':
|
|
return
|
|
|
|
client_id = int(choice)
|
|
cursor.execute('SELECT * FROM clients WHERE client_id = ?', (client_id,))
|
|
client_data = cursor.fetchone()
|
|
|
|
if client_data:
|
|
display_client_details(client_data)
|
|
break
|
|
else:
|
|
print("Invalid client ID. Please try again.")
|
|
except ValueError:
|
|
print("Invalid input. Please enter a number or 'back'.")
|
|
|
|
except sqlite3.Error as e:
|
|
print(f"An error occurred: {e}")
|
|
finally:
|
|
if conn:
|
|
conn.close()
|
|
|
|
def main_menu():
|
|
"""
|
|
Displays the main menu and handles user choices.
|
|
"""
|
|
while True:
|
|
print("\n--- Time Tracker Client Manager ---")
|
|
print("1. Add a new client")
|
|
print("2. Display client information")
|
|
print("3. Edit an existing client")
|
|
print("0. Exit")
|
|
|
|
choice = input("Enter your choice: ").strip()
|
|
|
|
if choice == '1':
|
|
add_new_client()
|
|
elif choice == '2':
|
|
display_client_info()
|
|
elif choice == '3':
|
|
edit_existing_client()
|
|
elif choice == '0':
|
|
print("Exiting. Goodbye!")
|
|
break
|
|
else:
|
|
print("Invalid choice. Please try again.")
|
|
|
|
if __name__ == "__main__":
|
|
main_menu()
|