def signin_user(self, username, password):
"""Login for existing user, returns user object with valid id."""
try:
db_obj = SqlConn()
query = "Select * from donor where username = %s and password= %s"
data = (
username,
password,
)
result = db_obj.get_query(query, data)
print(result)
uid = 0
user = None
if len(result) > 0 and result[0][-3] == 1:
uid = result[0][0]
user = Donor(uid)
print(uid, user)
self.status = True
return user, self.status
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()
def set_name(self, name, fav_causes):
"""Saves user profile details"""
self.name=name
try:
db_obj=SqlConn()
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()
def signup_user(self, username, password):
"""Creates new user, returns user object with valid id."""
# call db to save user - for now list
# Have to handle the exsiting user scenarios
try:
db_obj = SqlConn()
query = "Select * from donor where username = %s"
data = (username, )
result = db_obj.get_query(query, data)
if len(result) > 0:
raise Exception(
"The username has already been registered, pick another username or try Logging In, instead."
)
else:
query = "Insert into donor (username, password, name, activation_date, account_status) values(%s,%s,%s,%s,%s)"
data = (
username,
password,
"Donor",
datetime.datetime.now(),
True,
)
db_obj.set_query(query, data)
result = db_obj.get_query(
"Select donor_id from donor where username = %s",
(username, ))
uid = result[0][0]
user = Donor(uid)
return user, True
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()
def reset_password(self, username):
"""Change credentials for existing user, returns user object with valid id."""
try:
db_obj = SqlConn()
query = "Select * from donor where username = %s"
data = (username, )
result = db_obj.get_query(query, data)
if len(result) > 0:
#update pwd in db
#send pwd in mail
return True
else:
raise Exception("Username does not exist")
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()
def get_transactions_from_plaid():
"""pull daily transactions"""
start_date = '{:%Y-%m-%d}'.format(datetime.datetime.now() + datetime.timedelta(-1))
end_date = '{:%Y-%m-%d}'.format(datetime.datetime.now()+ datetime.timedelta(-1))
try:
db_obj=SqlConn()
query="Select donor_id,plaid_access_token,plaid_item_id from donor where account_status = %s \
and plaid_access_token is not NULL"
data=(True,)
result=db_obj.get_query(query,data)
transactions={}
if len(result)==0:
return "no plaid linked users"
for row in result:
# user_id,access_token,item_id=result[0]:
transactions_response = client.Transactions.get(row[1], start_date, end_date)
# transactions.append((user_id,transactions_response["transactions"]))
for transaction in transactions_response["transactions"]:
pt_obj=PlaidTransaction(
row[0],
transaction["name"],
transaction["amount"],
transaction["date"],
transaction["location"]["city"],
transaction["transaction_id"],
transaction["transaction_type"],
transaction["account_id"],
transaction["account_owner"]
)
pt_obj.save_tran()
transactions[row[0]]=transactions_response["transactions"]
# print("whts that",transaction)
for row in result:
donor_obj=Donor(row[0])
donor_obj.set_month_total()
return transactions
# return True,transactions_response
except Exception as e:
logging.info(e)
raise
def save_tran(self):
"""Creates new user, returns user object with valid id."""
# call db to save user - for now list
# Have to handle the exsiting user scenarios
try:
db_obj = SqlConn()
query = "Insert into plaid_transaction (donor_id,plaid_transaction_name,entry_date, \
plaid_transaction_amt,plaid_transaction_date,plaid_transaction_city,\
plaid_transaction_id, plaid_transaction_type,plaid_account_id,plaid_account_owner,\
donation_amt) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
data = (
self.donor_id,
self.name,
datetime.datetime.now(),
self.amount,
self.date,
self.city,
self.transaction_id,
self.transaction_type,
self.account_id,
self.account_owner,
self.calc_change(),
)
db_obj.set_query(query, data)
# return True;
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()
def get_account_info_from_plaid(user_id):
"""pull account info"""
try:
db_obj = SqlConn()
query = "Select donor_id,plaid_access_token,plaid_item_id from donor where account_status = %s \
and plaid_access_token is not NULL and donor_id=%s"
data = (
True,
user_id,
)
result = db_obj.get_query(query, data)
# accounts={}
if len(result) == 0:
return "No accounts linked to Plaid - No Spending Accounts"
for row in result:
accounts_response = client.Accounts.get(row[1])
# accounts[row[0]]=accounts_response
# print("whts that",accounts)
# for account in accounts_response["transactions"]:
# pt_obj=PlaidTransaction(
# row[0],
# transaction["name"],
# transaction["amount"],
# transaction["date"],
# transaction["location"]["city"],
# transaction["transaction_id"],
# transaction["transaction_type"],
# transaction["account_id"],
# transaction["account_owner"]
# )
# pt_obj.save_tran()
return accounts_response["accounts"]
# return True,transactions_response
except Exception as e:
logging.info(e)
raise
def select_drive(self,drive_id,charity_id):
print("sel drive")
try:
db_obj=SqlConn()
query="Insert into donor_drive values(%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE \
status=IF(status, 0, 1)"
data = (drive_id,self.uid,charity_id, datetime.datetime.now(),True,)
db_obj.set_query(query,data)
return "Drive Toggle Sel/Unsel successful"
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()
def set_causes(self, causes):
#update in obj and save to db
try:
db_obj=SqlConn()
query="Update donor set fav_causes= %s where donor_id = %s"
data = (','.join([str(x) for x in causes]),self.uid,)
print(data)
db_obj.set_query(query,data)
self.fav_causes=causes
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()
def set_month_total(self):
#update in obj and save to db
try:
db_obj=SqlConn()
query1="Select donation_cycle_start_date from donor where \
donor_id=%s"
data1 = (self.uid,)
result1=db_obj.get_query(query1,data1)
# print(datetime.datetime(cycle_start[0]))
# print(cycle_start[0],)
cycle_start=result1[0][0]
if cycle_start > datetime.datetime.now().replace(day=1):
donation_start=(cycle_start+ datetime.timedelta(-1)).replace(hour=0,minute=0, second=0)
else:
donation_start=datetime.datetime.now().replace(day=1).replace(hour=0,minute=0, second=0)
query2="Select sum(donation_amt) from plaid_transaction where \
donor_id=%s and plaid_transaction_date between %s and %s"
data2 = (self.uid,donation_start,datetime.datetime.now()+datetime.timedelta(-1),)
month_total=db_obj.get_query(query2,data2)
print(data2,month_total)
if month_total is None:
month_total=0.0
query3="Update donor set monthly_collected = %s where \
donor_id=%s"
data3 = (month_total,self.uid,)
db_obj.set_query(query3,data3)
# return True
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()
def set_access_token(self, item_id, access_token):
#update in obj and save to db
try:
db_obj=SqlConn()
query="Update donor set plaid_item_id= %s, plaid_access_token=%s, \
donation_cycle_start_date=%s where donor_id = %s"
data = (item_id,access_token, datetime.datetime.now(), self.uid,)
db_obj.set_query(query,data)
self.plaid_item=item_id
self.plaid_access_token=access_token
return True
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()
def get_user_totals(self):
print("in user tots")
try:
db_obj=SqlConn()
query="Select lifetime_donation, monthly_collected from donor \
where donor_id = %s"
data = (int(self.uid),)
result = db_obj.get_query(query,data)
query2="Select count(distinct drive_id), \
count(distinct charity_id) from donor_drive \
where donor_id = %s and status=%s"
data2 = (int(self.uid),True,)
result2 = db_obj.get_query(query2,data2)
return {'user_id':self.uid,'month_total':result[0][1],'lifetime_total':result[0][0],'active_drives':result2[0][0],'active_charities':result2[0][1]}
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()
def get_recommended_drives(uid):
"""fetches drives from drive table based on user fav causes"""
drives = []
try:
db_obj = SqlConn()
query = "Select fav_causes from donor where donor_id=%s"
data = (uid, )
causes = db_obj.get_query(query, data)
print("causes:", causes)
if len(causes) == 0:
print("No causes selected found, Falling back to causes - Animals")
# return get_drives(uid,0)
cause_list = ["Animals"]
else:
if causes[0][0] is None:
print(
"No causes found in db, , Falling back to causes - Animals"
)
cause_list = ["Animals"]
else:
cause_list = causes[0][0].split(",")
print(cause_list)
drives = []
for cause in cause_list:
print(cause)
query2 = "Select drive_id from drive where find_in_set(%s,causes) and active_status = True"
data2 = (cause, )
result_drives = db_obj.get_query(query2, data2)
# drives.add(result_drives[0])
for drive in result_drives:
drives.append(drive[0])
print("rd: ", result_drives)
drive_ids = list(set(drives))
if len(drive_ids) > 0:
query3 = "Select A.*,B.char_name,B.charity_nav_score,B.tax_deductible from drive A, charity B where drive_id in %s and A.charity_id=B.charity_id"
data3 = (drive_ids, )
result_drives = db_obj.get_query(query3, data3)
drives = []
for record in result_drives:
num_donor = ((randint(0, 20)), )
userDrive = False
if record[11] == 1:
if record[-3]:
isd = True
else:
isd = False
if record[-2] == 0.0:
score = "Not Available"
else:
score = record[-2]
if record[-1] == 0:
td = "No"
else:
td = "Yes"
r = {
'drive_id': record[0],
'charity_id': record[1],
'driveTitle': record[2],
'charityName': record[-3],
'driveAbout': record[3],
'driveImageURL': record[4],
'targetMoney': record[5],
'currentMoney': record[6],
'driveCity': record[7],
'driveState': record[8],
'causes': record[12].split(","),
'percentCompleted': float(record[6] / record[5]),
'is_default': isd,
'numDonations': num_donor[0],
'userSelected': userDrive,
'charityNavigatorScore': score,
'deductibility': td
}
drives.append(r)
return drives
else:
raise Exception("No Recommended Drives")
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()
def get_charities(uid):
"""fetches charities from charity table"""
charities = []
try:
db_obj = SqlConn()
if uid == 0:
#get all charities
print("all")
query = "Select * from charity"
data = None
else:
query = "Select * from charity where charity_id \
in (Select distinct charity_id from donor_drive \
where donor_id=%s and status = %s)"
data = (
uid,
True,
)
result = db_obj.get_query(query, data)
if len(result) > 0:
print("db queried")
for record in result:
logging.info(record)
if record[14] == 0.0:
score = "Not Available"
else:
score = record[14]
if record[13] is None:
ctype = "Not Available"
else:
ctype = record[13]
if record[15] == 0:
td = "No"
else:
td = "Yes"
result2 = db_obj.get_query(query, data)
r = {
'charity_id': record[0],
'charityName': record[1],
'charityAbout': record[2],
'charityImageURL': record[3],
'charityAddress': record[4],
'charityCity': record[5],
'charityState': record[6],
'charityActiveDrives': record[11],
'charityCauses': record[12].split(","),
'charityType': ctype,
'charityNavigatorScore': score,
'deductibility': td
}
charities.append(r)
else:
raise Exception("No charities found")
return charities
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()
def get_drive_updates(uid, drive_id):
"""fetches updates from drive_update table"""
drive_updates = []
try:
db_obj = SqlConn()
if uid != 0:
print("user feed")
query = "Select A.charity_id,A.char_name, A.char_image, B.drive_id, B.name, B.drive_image, \
B.upd_id,B.drive_update,B.update_image from charity A, \
(Select a.upd_id,a.drive_update,a.update_image, b.drive_id,b.name,b.drive_image,b.charity_id \
from drive_update a, drive b where a.drive_id=b.drive_id and b.drive_id \
in (Select drive_id from donor_drive where donor_id=%s and status=%s)) B where A.charity_id = B.charity_id"
data = (
uid,
True,
)
elif drive_id != 0:
print("drive feed")
query = "Select A.charity_id,A.char_name, A.char_image, B.drive_id, B.name, B.drive_image, \
B.upd_id,B.drive_update,B.update_image from charity A, \
(Select a.upd_id,a.drive_update,a.update_image, b.drive_id,b.name,b.drive_image,b.charity_id \
from drive_update a, drive b where a.drive_id=b.drive_id) B\
where A.charity_id = B.charity_id and B.drive_id=%s"
data = (drive_id, )
result = db_obj.get_query(query, data)
print(query, data, result)
if len(result) > 0:
print("db queried")
for record in result:
logging.info(record)
num_donor = ((randint(0, 20)), )
r = {
'charity_id': record[0],
'charityName': record[1],
'charityImageURL': record[2],
'drive_id': record[3],
'driveTitle': record[4],
'driveImageURL': record[5],
'update_id': record[6],
'feedMessage': record[7],
'feedImageURL': record[8],
'numDonations': num_donor[0]
}
drive_updates.append(r)
else:
if uid == 0 and drive_id != 0:
raise Exception("No updates present for the drive.")
elif uid != 0 and drive_id == 0:
raise Exception(
"No updates for your drives. Support more drives to see how your change is changing the world."
)
return drive_updates
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()
def make_donation(self,amount=0):
try:
db_obj=SqlConn()
if amount == 0:
query="Select monthly_collected from donor where donor_id = %s"
data=(self.uid,)
result1=db_obj.get_query(query,data)
query2= "Select distinct drive_id,charity_id from donor_drive where donor_id=%s and status=%s"
data2=(self.uid,True,)
result2=db_obj.get_query(query2,data2)
print(result1[0][0])
print(result2, len(result2))
if len(result2)>0:
donation_amt_per_drive = result1[0][0]/len(result2)
for drive in result2:
query3= "Insert into donation (donor_id, drive_id,charity_id, donation_date, donation_amt, donation_type)\
values(%s,%s,%s,%s,%s,%s)"
data3=(self.uid,drive[0],drive[1],datetime.datetime.now(),donation_amt_per_drive,"OTD",)
print(query3, data3)
db_obj.set_query(query3,data3)
query4= "Update donor set monthly_collected = %s, donation_cycle_start_date =%s, \
lifetime_donation=lifetime_donation+%s where donor_id=%s"
data4=(0,datetime.datetime.now(),result1[0][0],self.uid,)
db_obj.set_query(query4,data4)
print(query4, data4)
return True, "Donation Successfull"
else:
return False, "No selected drive"
else:
# return False, "Donation amount needs to be greater than 0"
query2= "Select distinct drive_id,charity_id from donor_drive where donor_id=%s and status=%s"
data2=(self.uid,True,)
result2=db_obj.get_query(query2,data2)
print("why",query2,data2)
print("Drive ids",result2, len(result2))
if len(result2)>0:
donation_amt_per_drive = float(amount)/len(result2)
for drive in result2:
query3= "Insert into donation (donor_id, drive_id,charity_id, donation_date, donation_amt, donation_type)\
values(%s,%s,%s,%s,%s,%s)"
data3=(self.uid,drive[0],drive[1],datetime.datetime.now(),donation_amt_per_drive,"OTD",)
print(query3, data3)
db_obj.set_query(query3,data3)
query4= "Update donor set monthly_collected = %s, donation_cycle_start_date =%s, \
lifetime_donation=lifetime_donation+%s where donor_id=%s"
data4=(0,datetime.datetime.now(),float(amount),self.uid,)
db_obj.set_query(query4,data4)
print(query4, data4)
return True, "Donation Successfull"
else:
return False, "No selected drive"
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()
def get_charity_drives(cid, uid=0):
"""fetches drives from drive table for the charity"""
drives = []
try:
db_obj = SqlConn()
query = "Select * from drive where charity_id= %s order by active_status"
data = (cid, )
result = db_obj.get_query(query, data)
query2 = "Select charity_id,char_name,charity_nav_score,tax_deductible from charity where charity_id=%s"
data2 = (cid, )
result_charities = db_obj.get_query(query2, data2)
query4 = "Select distinct drive_id from donor_drive \
where donor_id=%s and status = %s"
data4 = (
uid,
True,
)
ud = db_obj.get_query(query4, data4)
user_drives = []
for record in ud:
user_drives.append(record[0])
print("user_drives", user_drives)
if len(result) > 0:
for record in result:
print(record)
if record[11] == 1:
activeStatus = True
else:
activeStatus = False
if record[-1]:
isd = True
else:
isd = False
charityName = ""
for rc in result_charities:
if record[1] == rc[0]:
charityName = rc[1]
if rc[2] == 0.0:
score = "Not Available"
else:
score = rc[2]
if rc[3] == 0:
td = "No"
else:
td = "Yes"
data3 = (
record[0],
["MON", "OTD"],
)
# num_donor=get_query(query3,data3)
num_donor = ((randint(0, 20)), )
if record[0] in user_drives:
userDrive = True
else:
userDrive = False
r = {
'drive_id': record[0],
'charity_id': record[1],
'driveTitle': record[2],
'charityName': charityName,
'driveAbout': record[3],
'driveImageURL': record[4],
'targetMoney': record[5],
'currentMoney': record[6],
'driveCity': record[7],
'driveState': record[8],
'causes': record[12].split(","),
'percentCompleted': float(record[6] / record[5]),
'is_default': isd,
'numDonations': num_donor[0],
'activeStatus': activeStatus,
'userSelected': userDrive,
'charityNavigatorScore': score,
'deductibility': td
}
drives.append(r)
else:
raise Exception("No Drives Found")
print("drives", drives)
return drives
except Exception as e:
logging.info(e)
raise
finally:
db_obj.close_conn()