def resetMaster():
"""
Function to reset/create master table to store default categorization preferences
Creates an "old" and a "new" table that are identical except for the value of is_old
Inputs: None
Output: None
"""
conn = psycopg2.connect(dbname=DBNAME,
user=AUSER,
password=DBPASSWORD,
host=HOST)
# Create the Cursor object
c = conn.cursor()
# Delete the table if it exists
c.execute("""
DROP TABLE IF EXISTS master
""")
# Delete the changelogs table
c.execute("""
DROP TABLE IF EXISTS changelog
""")
# Create table
c.execute("""
CREATE TABLE master
(Key TEXT,
PLAID_Values TEXT,
is_old BOOLEAN)
""")
# Check if table is empty
empty_query = """
SELECT count(*)
FROM master
"""
# "New" default entries in master table
dict_to_sql(current_dict=cats_dict,
is_master=True,
is_old_custom=True,
c=c)
# "Old" default entries in master table
dict_to_sql(current_dict=cats_dict,
is_master=True,
is_old_custom=False,
c=c)
# commit changes (if any)
conn.commit()
# close connection
conn.close()
from DB.userDB import updateUsers
updateUsers(cats_dict)
return 0
def getUser(user_id):
"""
Function that checks if the user is currently in the users table
If not in the table: Pulls from master to create their preferences and returns them as a dict
If in the table, pulls their preferences and returns them as a dict
Inputs: Unique user id
Output: A dict of the user's preferences
"""
conn = psycopg2.connect(dbname=DBNAME, user=AUSER,
password=DBPASSWORD, host=HOST)
c = conn.cursor()
find_user = (f"""
SELECT *
FROM users
WHERE user_id = {user_id}
""")
c.execute(find_user)
a_user = c.fetchall()
# if the user already exists in the DB, grab whats currently there
if a_user != []:
# Query the master table for the keys and save them to val
query1 = f"""
SELECT Key
from users
WHERE user_id = {user_id}
"""
# Query the master table for the strings that contain the lists of
# values separated by '/'
query2 = f"""
SELECT PLAID_Values
from users
WHERE user_id = {user_id}
"""
new_dict = sql_to_dict(query1=query1, query2=query2, c=c)
conn.close()
return new_dict
# If the user doesn't exist in the DB, provide it the master dict
else:
current_dict = masterPull()
dict_to_sql(current_dict=current_dict, is_master=False,
is_old_custom=False, c=c, user_id=user_id)
conn.commit()
conn.close()
return current_dict
def updateUsers(new_dict: dict):
"""
Function that takes in the newest default preferences, and replaces the preferences
of users that are using the defaults
Inputs: Dictionary of new defaults
Outputs: None
"""
conn = psycopg2.connect(dbname=DBNAME, user=AUSER,
password=DBPASSWORD, host=HOST)
c = conn.cursor()
modify_check = """
SELECT DISTINCT user_id
FROM users
WHERE is_custom = FALSE
"""
c.execute(modify_check)
modified = c.fetchall()
if modified == []:
return 0
user_ids = []
for tup in modified:
for value in tup:
user_ids.append(value)
delete_query = """
DELETE
FROM users
WHERE is_custom = False
"""
c.execute(delete_query)
for user in user_ids:
dict_to_sql(current_dict=new_dict, is_master=False,
is_old_custom=False, c=c, user_id=user)
conn.commit()
conn.close()
return 0
def reset_user_cats(self):
conn = psycopg2.connect(dbname=DBNAME, user=AUSER,
password=DBPASSWORD, host=HOST)
c = conn.cursor()
delete_query = f"""
DELETE
FROM users
WHERE user_id = {self.user_id}
"""
c.execute(delete_query)
current_dict = masterPull()
dict_to_sql(current_dict=current_dict, is_master=False,
is_old_custom=False, c=c, user_id=self.user_id)
conn.commit()
conn.close()
return f"User {self.user_id} categorical preferences have been reset!"
def updateMaster(old_cat, plaid_cat, destination):
"""
Function that updates the master table. If there is only an old dict, it inserts the new one.
If there is a current old and new, it deletes the old, turns the current new to old, and inserts the new.
Inputs: old_cat - the old BB category of the plaid category that needs to be remapped
: plaid_cat - the plaid category that needs to be remapped
: destination - the new BB category of the plaid category
"""
# Reformat the plaid_cat back into a comma separated list
if "_AND_" in plaid_cat:
plaid_cat = plaid_cat.split("_AND_")
else:
plaid_cat = [plaid_cat]
for i, v in enumerate(plaid_cat):
plaid_cat[i] = v.replace('_', ' ')
# Pull the current defaults
old_dict = copy.deepcopy(masterPull())
new_dict = copy.deepcopy(masterPull())
# Exception for if plaid_cats is not in old_BB
if plaid_cat not in new_dict[old_cat]:
raise HTTPException(status_code=500,
detail=f"{plaid_cat} is not in {old_cat}")
# Remove the plaid_cat from the old_cat's value list
new_dict[old_cat].remove(plaid_cat)
# Add the plaid_cat to the destination's value list
new_dict[destination].append(plaid_cat)
# Query the master table to check if there is a new dict there
# Create the Connection object to the 'BudgetBlocks' DB
conn = psycopg2.connect(dbname=DBNAME,
user=AUSER,
password=DBPASSWORD,
host=HOST)
# Create the Cursor object
c = conn.cursor()
# Delete the old
delete_query = """
DELETE
FROM master
WHERE is_old = TRUE
"""
c.execute(delete_query)
# Make the current new, old
replace_query = """
Update master
Set is_old = TRUE
"""
c.execute(replace_query)
# Insert the newly made cats as the new dict
dict_to_sql(current_dict=new_dict,
is_master=True,
is_old_custom=False,
c=c)
# commit changes (if any)
conn.commit()
# close connection
conn.close()
from DB.userDB import updateUsers
updateUsers(new_dict)
return 0
def changePreferences(self):
"""
Function to update a user's categorical preferences in the users table
Inputs: self - object that has parameters for plaid_cats, old_BB, new_BB, and user_id
Outputs: None
"""
# If the user doesn't already exist then it breaks
plaid_cats = self.plaid_cats
old_BB = self.old_BB
new_BB = self.new_BB
user_id = self.user_id
conn = psycopg2.connect(dbname=DBNAME, user=AUSER,
password=DBPASSWORD, host=HOST)
c = conn.cursor()
keys = []
values = []
# Query the users table for the keys and save them to val
query1 = f"""
SELECT Key
from users
WHERE user_id = {user_id}
"""
# Query the users table for the strings that contain the lists of
# values separated by '/'
query2 = f"""
SELECT PLAID_Values
from users
WHERE user_id = {user_id}
"""
new_dict = sql_to_dict(query1=query1, query2=query2, c=c)
# Exception for if plaid_cats is not in old_BB
if plaid_cats not in new_dict[old_BB]:
raise HTTPException(
status_code=500,
detail=f"{plaid_cats} is not in {old_BB} for user {user_id}")
else:
# Remove the plaid_cat from the old_cat's value list
new_dict[old_BB].remove(plaid_cats)
# Create the new BB category if it doesn't exist
if new_BB not in new_dict:
new_dict[new_BB] = []
# Add the plaid_cat to the destination's value list
new_dict[new_BB].append(plaid_cats)
delete_query = f"""
DELETE
FROM users
WHERE user_id = {user_id}
"""
c.execute(delete_query)
# Insert the new
dict_to_sql(current_dict=new_dict, is_master=False,
user_id=user_id, c=c, is_old_custom=True)
conn.commit()
conn.close()
return 0