703 lines
17 KiB
Python
703 lines
17 KiB
Python
import os
|
|
from datetime import datetime, timezone, timedelta, date
|
|
import time
|
|
import pytz
|
|
import sys
|
|
import psycopg2
|
|
from mastodon import Mastodon
|
|
from mastodon.Mastodon import MastodonMalformedEventError, MastodonNetworkError, MastodonReadTimeout, MastodonAPIError, MastodonIllegalArgumentError
|
|
from prettytable import PrettyTable
|
|
import pdb
|
|
|
|
tz = pytz.timezone('Europe/Madrid')
|
|
|
|
menu_options = {
|
|
1: 'Add Bill',
|
|
2: 'List Bills',
|
|
3: 'Add Donation',
|
|
4: 'List Donations',
|
|
5: 'Balance',
|
|
6: 'Exit',
|
|
}
|
|
list_show_options = {
|
|
1: 'Month',
|
|
2: 'Year',
|
|
3: 'Total',
|
|
4: 'Back',
|
|
}
|
|
|
|
def print_menu():
|
|
|
|
for key in menu_options.keys():
|
|
print (key, '-', menu_options[key] )
|
|
|
|
def list_show_menu():
|
|
|
|
for key in list_show_options.keys():
|
|
print (key, '-', list_show_options[key] )
|
|
|
|
def check_db_conn():
|
|
|
|
try:
|
|
|
|
conn = None
|
|
|
|
conn = psycopg2.connect(database = budget_db, user = budget_db_user, password = "", host = "/var/run/postgresql", port = "5432")
|
|
|
|
except (Exception, psycopg2.DatabaseError) as error:
|
|
|
|
sys.stdout.write(f'\n{str(error)}\n')
|
|
|
|
sys.exit("Exiting. Run 'python db-setup'")
|
|
|
|
class Bill:
|
|
|
|
name = "Bill"
|
|
|
|
def __init__(self):
|
|
|
|
self.domain = float(input('Enter Domain Bill: ') or '0.00')
|
|
self.server = float(input('Server Bill: ') or '0.00')
|
|
self.backup = float(input('Backup Bill: ') or '0.00')
|
|
self.fileserver = float(input("File server Bill: ") or '0.00')
|
|
self.setup = float(input(f'Setup Bill: ') or '0.00')
|
|
self.bank_fee = float(input(f'Bank Maintenance Fee: ') or '0.00')
|
|
|
|
while True:
|
|
|
|
try:
|
|
|
|
self.date = datetime.strptime(input('Enter Bill date in the format d.m.yyyy: '), '%d.%m.%Y')
|
|
|
|
now = datetime.now(tz)
|
|
|
|
self.date = self.date.replace(hour=now.hour, minute=now.minute, second=now.second)
|
|
|
|
break
|
|
|
|
except:
|
|
|
|
self.date = format(datetime.now(tz).strftime('%d.%m.%Y %H:%M:%S'))
|
|
|
|
break
|
|
|
|
def save(self):
|
|
|
|
sql = "INSERT INTO bills(datetime, domain, server, backup, fileserver, setup, bank_fee) VALUES(%s,%s,%s,%s,%s,%s,%s)"
|
|
|
|
try:
|
|
|
|
conn = psycopg2.connect(database = budget_db, user = budget_db_user, password = "", host = "/var/run/postgresql", port = "5432")
|
|
|
|
cur = conn.cursor()
|
|
|
|
cur.execute(sql, (self.date, self.domain, self.server, self.backup, self.fileserver, self.setup, self.bank_fee))
|
|
|
|
print("\nUpdating bills...")
|
|
|
|
conn.commit()
|
|
|
|
cur.close()
|
|
|
|
except (Exception, psycopg2.DatabaseError) as error:
|
|
|
|
print(error)
|
|
|
|
finally:
|
|
|
|
if conn is not None:
|
|
|
|
conn.close()
|
|
|
|
class Donation:
|
|
|
|
name = "Donation"
|
|
|
|
def __init__(self):
|
|
|
|
self.amount = float(input('Donation: ') or '0.00')
|
|
|
|
while True:
|
|
|
|
try:
|
|
|
|
self.date = datetime.strptime(input('Enter Donation date in the format d.m.yyyy: '), '%d.%m.%Y')
|
|
|
|
now = datetime.now(tz)
|
|
|
|
self.date = self.date.replace(hour=now.hour, minute=now.minute, second=now.second)
|
|
|
|
break
|
|
|
|
except:
|
|
|
|
self.date = format(datetime.now(tz).strftime('%d.%m.%Y %H:%M:%S'))
|
|
|
|
break
|
|
|
|
def save(self):
|
|
|
|
sql = "INSERT INTO incomes(datetime, donations) VALUES(%s,%s)"
|
|
|
|
try:
|
|
|
|
conn = psycopg2.connect(database = budget_db, user = budget_db_user, password = "", host = "/var/run/postgresql", port = "5432")
|
|
|
|
cur = conn.cursor()
|
|
|
|
cur.execute(sql, (self.date, self.amount))
|
|
|
|
print("\nUpdating donations...")
|
|
|
|
conn.commit()
|
|
|
|
cur.close()
|
|
|
|
except (Exception, psycopg2.DatabaseError) as error:
|
|
|
|
print(error)
|
|
|
|
finally:
|
|
|
|
if conn is not None:
|
|
|
|
conn.close()
|
|
|
|
class BillShow:
|
|
|
|
name = "BillShow"
|
|
|
|
def __init__(self):
|
|
|
|
while(True):
|
|
|
|
print('\n')
|
|
|
|
list_show_menu()
|
|
|
|
show_option = ''
|
|
|
|
now = datetime.now(tz)
|
|
|
|
try:
|
|
|
|
show_option = int(input('\nEnter your choice: '))
|
|
|
|
except:
|
|
|
|
print('\nWrong input. Please enter a number between 1 and 4.\n')
|
|
|
|
if show_option == 1:
|
|
|
|
self.option = 1
|
|
self.criteria = input('Month (enter = current month)? ') or str(now.month)
|
|
break
|
|
|
|
elif show_option == 2:
|
|
|
|
self.option = 2
|
|
self.criteria = input('Year (enter = current year)? ') or str(now.year)
|
|
break
|
|
|
|
elif show_option == 3:
|
|
|
|
self.option = 3
|
|
self.criteria = None
|
|
break
|
|
|
|
elif show_option == 4:
|
|
|
|
self.option = 4
|
|
self.criteria = None
|
|
break
|
|
|
|
def show(self, criteria):
|
|
|
|
month_date = []
|
|
|
|
month_domain = []
|
|
|
|
month_server = []
|
|
|
|
month_backup = []
|
|
|
|
month_fileserver = []
|
|
|
|
month_setup = []
|
|
|
|
month_bank_fee = []
|
|
|
|
if self.option == 1:
|
|
|
|
sql = "select datetime, domain, server, backup, fileserver, setup, bank_fee from bills where date_part('year', datetime) = date_part('year', CURRENT_DATE) and date_part('month', datetime) = (%s) order by 1 asc"
|
|
|
|
elif self.option == 2:
|
|
|
|
sql = "select datetime, domain, server, backup, fileserver, setup, bank_fee from bills where date_part('year', datetime) = (%s) order by 1 asc"
|
|
|
|
elif self.option == 3:
|
|
|
|
sql = "select datetime, domain, server, backup, fileserver, setup, bank_fee from bills order by 1 asc"
|
|
|
|
try:
|
|
|
|
conn = psycopg2.connect(database = budget_db, user = budget_db_user, password = "", host = "/var/run/postgresql", port = "5432")
|
|
|
|
cur = conn.cursor()
|
|
|
|
if self.option == 1 or self.option == 2:
|
|
|
|
cur.execute(sql, (self.criteria,))
|
|
|
|
else:
|
|
|
|
cur.execute(sql)
|
|
|
|
rows = cur.fetchall()
|
|
|
|
for row in rows:
|
|
|
|
month_date.append(row[0])
|
|
|
|
month_domain.append(row[1])
|
|
|
|
month_server.append(row[2])
|
|
|
|
month_backup.append(row[3])
|
|
|
|
month_fileserver.append(row[4])
|
|
|
|
month_setup.append(row[5])
|
|
|
|
if row[6] != None:
|
|
|
|
month_bank_fee.append(row[6])
|
|
|
|
else:
|
|
|
|
month_bank_fee.append('0.00')
|
|
|
|
cur.close()
|
|
|
|
except (Exception, psycopg2.DatabaseError) as error:
|
|
|
|
print(error)
|
|
|
|
finally:
|
|
|
|
if conn is not None:
|
|
|
|
conn.close()
|
|
|
|
return (month_date, month_domain, month_server, month_backup, month_fileserver, month_setup, month_bank_fee)
|
|
|
|
class DonationShow:
|
|
|
|
name = "DonationShow"
|
|
|
|
def __init__(self):
|
|
|
|
while(True):
|
|
|
|
print('\n')
|
|
|
|
list_show_menu()
|
|
|
|
show_option = ''
|
|
|
|
now = datetime.now(tz)
|
|
|
|
try:
|
|
|
|
show_option = int(input('\nEnter your choice: '))
|
|
|
|
except:
|
|
|
|
print('\nWrong input. Please enter a number between 1 and 4.\n')
|
|
|
|
if show_option == 1:
|
|
|
|
self.option = 1
|
|
self.criteria = input('Month (enter = current month)? ') or str(now.month)
|
|
break
|
|
|
|
elif show_option == 2:
|
|
|
|
self.option = 2
|
|
self.criteria = input('Year (enter = current year)? ') or str(now.year)
|
|
break
|
|
|
|
elif show_option == 3:
|
|
|
|
self.option = 3
|
|
self.criteria = None
|
|
break
|
|
|
|
elif show_option == 4:
|
|
|
|
self.option = 4
|
|
self.criteria = None
|
|
break
|
|
|
|
def show(self, criteria):
|
|
|
|
month_date = []
|
|
|
|
month_donation = []
|
|
|
|
if self.option == 1:
|
|
|
|
sql = "select datetime, donations from incomes where date_part('year', datetime) = date_part('year', CURRENT_DATE) and date_part('month', datetime) = (%s) order by 1 asc"
|
|
|
|
elif self.option == 2:
|
|
|
|
sql = "select datetime, donations from incomes where date_part('year', datetime) = (%s) order by 1 asc"
|
|
|
|
elif self.option == 3:
|
|
|
|
sql = "select datetime, donations from incomes order by 1 asc"
|
|
|
|
try:
|
|
|
|
conn = psycopg2.connect(database = budget_db, user = budget_db_user, password = "", host = "/var/run/postgresql", port = "5432")
|
|
|
|
cur = conn.cursor()
|
|
|
|
if self.option == 1 or self.option == 2:
|
|
|
|
cur.execute(sql, (self.criteria,))
|
|
|
|
else:
|
|
|
|
cur.execute(sql)
|
|
|
|
rows = cur.fetchall()
|
|
|
|
for row in rows:
|
|
|
|
month_date.append(row[0])
|
|
|
|
month_donation.append(row[1])
|
|
|
|
cur.close()
|
|
|
|
except (Exception, psycopg2.DatabaseError) as error:
|
|
|
|
print(error)
|
|
|
|
finally:
|
|
|
|
if conn is not None:
|
|
|
|
conn.close()
|
|
|
|
return (month_date, month_donation)
|
|
|
|
class BalanceShow:
|
|
|
|
name = "BalanceShow"
|
|
|
|
def __init__(self):
|
|
|
|
while(True):
|
|
|
|
print('\n')
|
|
|
|
list_show_menu()
|
|
|
|
show_option = ''
|
|
|
|
now = datetime.now(tz)
|
|
|
|
try:
|
|
|
|
show_option = int(input('\nEnter your choice: '))
|
|
|
|
except:
|
|
|
|
print('\nWrong input. Please enter a number between 1 and 4.\n')
|
|
|
|
if show_option == 1:
|
|
|
|
self.option = 1
|
|
self.criteria = input('Month (enter = current month)? ') or str(now.month)
|
|
break
|
|
|
|
elif show_option == 2:
|
|
|
|
self.option = 2
|
|
self.criteria = input('Year (enter = current year)? ') or str(now.year)
|
|
break
|
|
|
|
elif show_option == 3:
|
|
|
|
self.option = 3
|
|
self.criteria = None
|
|
break
|
|
|
|
elif show_option == 4:
|
|
|
|
self.option = 4
|
|
self.criteria = None
|
|
break
|
|
|
|
def show(self, criteria):
|
|
|
|
balance = 0
|
|
|
|
if self.option == 1:
|
|
|
|
incomes_sql = "select sum(donations) from incomes where date_part('year', datetime) = date_part('year', CURRENT_DATE) and date_part('month', datetime) = (%s)"
|
|
|
|
bills_sql = "select sum(coalesce(domain) + coalesce(server) + coalesce(backup) + coalesce(fileserver) + coalesce(setup) + coalesce(bank_fee,0)) from bills where date_part('year', datetime) = date_part('year', CURRENT_DATE) and date_part('month', datetime) = (%s)"
|
|
|
|
elif self.option == 2:
|
|
|
|
incomes_sql = "select sum(donations) from incomes where date_part('year', datetime) = (%s)"
|
|
|
|
bills_sql = "select sum(coalesce(domain) + coalesce(server) + coalesce(backup) + coalesce(fileserver) + coalesce(setup) + coalesce(bank_fee,0)) from bills where date_part('year', datetime) = (%s)"
|
|
|
|
elif self.option == 3:
|
|
|
|
incomes_sql = "select sum(donations) from incomes order by 1 asc"
|
|
|
|
bills_sql = "select sum(coalesce(domain) + coalesce(server) + coalesce(backup) + coalesce(fileserver) + coalesce(setup) + coalesce(bank_fee,0)) from bills"
|
|
|
|
try:
|
|
|
|
conn = psycopg2.connect(database = budget_db, user = budget_db_user, password = "", host = "/var/run/postgresql", port = "5432")
|
|
|
|
cur = conn.cursor()
|
|
|
|
if self.option == 1 or self.option == 2:
|
|
|
|
cur.execute(incomes_sql, (self.criteria,))
|
|
|
|
row = cur.fetchone()
|
|
|
|
incomes = row[0] if row[0] != None else 0
|
|
|
|
cur.execute(bills_sql, (self.criteria,))
|
|
|
|
row = cur.fetchone()
|
|
|
|
bills = row[0]
|
|
|
|
bills = row[0] if row[0] != None else 0
|
|
|
|
else:
|
|
|
|
cur.execute(incomes_sql)
|
|
|
|
row = cur.fetchone()
|
|
|
|
incomes = row[0]
|
|
|
|
cur.execute(bills_sql)
|
|
|
|
row = cur.fetchone()
|
|
|
|
bills = row[0]
|
|
|
|
balance = incomes - bills
|
|
|
|
cur.close()
|
|
|
|
except (Exception, psycopg2.DatabaseError) as error:
|
|
|
|
print(error)
|
|
|
|
finally:
|
|
|
|
if conn is not None:
|
|
|
|
conn.close()
|
|
|
|
return (balance)
|
|
|
|
def get_config():
|
|
|
|
global budget_db
|
|
global budget_db_user
|
|
|
|
# Load configuration from config file
|
|
config_filepath = "config/db_config.txt"
|
|
budget_db = get_db_params("budget_db", config_filepath)
|
|
budget_db_user = get_db_params("budget_db_user", config_filepath)
|
|
|
|
return (config_filepath, budget_db, budget_db_user)
|
|
|
|
def get_db_params( parameter, file_path ):
|
|
|
|
if not os.path.isfile(file_path):
|
|
print("File %s not found, exiting."%file_path)
|
|
sys.exit(0)
|
|
|
|
# Find parameter in file
|
|
with open( file_path ) as f:
|
|
for line in f:
|
|
if line.startswith( parameter ):
|
|
return line.replace(parameter + ":", "").strip()
|
|
|
|
# Cannot find parameter, exit
|
|
print(file_path + " Missing parameter %s "%parameter)
|
|
sys.exit(0)
|
|
|
|
# main
|
|
|
|
if __name__ == '__main__':
|
|
|
|
get_config()
|
|
|
|
check_db_conn()
|
|
|
|
while(True):
|
|
|
|
print('\n')
|
|
|
|
print_menu()
|
|
|
|
option = ''
|
|
|
|
try:
|
|
|
|
option = int(input('\nEnter your choice: '))
|
|
|
|
except:
|
|
|
|
print('\nWrong input. Please enter a number between 1 and 5.\n')
|
|
|
|
if option == 1:
|
|
|
|
bill = Bill()
|
|
|
|
if bill.domain == bill.server == bill.backup == bill.fileserver == bill.setup == bill.bank_fee == 0.0:
|
|
|
|
print('\nAny bill added')
|
|
|
|
else:
|
|
|
|
bill.save()
|
|
|
|
elif option == 2:
|
|
|
|
bill = BillShow()
|
|
|
|
if bill.option == 1 or bill.option == 2 or bill.option == 3:
|
|
|
|
month_date, month_domain, month_server, month_backup, month_fileserver, month_setup, month_bank_fee = bill.show(bill.criteria)
|
|
|
|
if bill.option != 4:
|
|
|
|
print_table = PrettyTable()
|
|
|
|
print_table.field_names = ['Date', 'Domain', 'Server', 'Backup', 'File server', 'Setup', 'Bank fee']
|
|
|
|
i = 0
|
|
|
|
total_amount = 0
|
|
|
|
while i < len(month_date):
|
|
|
|
b_date = format(month_date[i].strftime('%d.%m.%Y %H:%M:%S'))
|
|
|
|
b_domain = float(month_domain[i])
|
|
|
|
b_server = float(month_server[i])
|
|
|
|
b_backup = float(month_backup[i])
|
|
|
|
b_fileserver = float(month_fileserver[i])
|
|
|
|
b_setup = float(month_setup[i])
|
|
|
|
b_bank_fee = float(month_bank_fee[i])
|
|
|
|
total_amount = total_amount + b_domain + b_server + b_backup + b_fileserver + b_setup + b_bank_fee
|
|
|
|
print_table.add_row([b_date, b_domain, b_server, b_backup, b_fileserver, b_setup, b_bank_fee])
|
|
|
|
i += 1
|
|
|
|
print_table.add_row(['', '', '', '', '', 'Total', round(total_amount, 2)])
|
|
|
|
print(print_table)
|
|
|
|
elif option == 3:
|
|
|
|
donation = Donation()
|
|
|
|
if donation.amount == 0.0:
|
|
|
|
print('\nAny donation added')
|
|
|
|
else:
|
|
|
|
donation.save()
|
|
|
|
elif option == 4:
|
|
|
|
donation = DonationShow()
|
|
|
|
if donation.option == 1 or donation.option == 2 or donation.option == 3:
|
|
|
|
date_lst, donation_lst = donation.show(donation.criteria)
|
|
|
|
if donation.option != 4:
|
|
|
|
print_table = PrettyTable()
|
|
|
|
print_table.field_names = ['Date','Donation']
|
|
|
|
print_table.align['Donation'] = "r"
|
|
|
|
i = 0
|
|
|
|
total_amount = 0
|
|
|
|
while i < len(donation_lst):
|
|
|
|
d_date = format(date_lst[i].strftime('%d.%m.%Y %H:%M:%S'))
|
|
|
|
d_amount = float(donation_lst[i])
|
|
|
|
total_amount = total_amount + d_amount
|
|
|
|
print_table.add_row([d_date, d_amount])
|
|
|
|
i += 1
|
|
|
|
print_table.add_row(['Total', round(total_amount, 2)])
|
|
|
|
print(print_table)
|
|
|
|
elif option == 5:
|
|
|
|
balance = BalanceShow()
|
|
|
|
if balance.option == 1 or balance.option == 2 or balance.option == 3:
|
|
|
|
balance_result = balance.show(balance.criteria)
|
|
|
|
if balance.option != 4:
|
|
|
|
print_table = PrettyTable()
|
|
|
|
print_table.field_names = ['Balance']
|
|
|
|
print_table.align['Balance'] = "r"
|
|
|
|
b_amount = float(balance_result)
|
|
|
|
print_table.add_row([b_amount])
|
|
|
|
#print_table.add_row(['Total', round(total_amount, 2)])
|
|
|
|
print(print_table)
|
|
|
|
elif option == 6:
|
|
|
|
print('Bye!')
|
|
|
|
exit()
|
|
|