def user_dashboard():
sessionUser = "" if 'sessionUser' not in session else session['sessionUser']
categories = []
if sessionUser == "":
abort(404)
cursor = getCursor()[1]
# To fetch items for a user from db
cursor.execute(query().PRODUCTS_FOR_USER(sessionUser['u_id']))
data = cursor.fetchall()
# To fetch categories from db
cursor.execute(query().fetchAllCategories())
allCategories = cursor.fetchall()
categories = [allCategories[i][0] for i in range(len(allCategories))]
productList = []
for d in data:
if len(d) == 16:
productObject = product.makeProduct(d)
productList.append(productObject)
currentSearch = ""
categoryName = "All"
session['categories'] = categories
return render_template("user-dashboard.html",
sessionUser=sessionUser,
productList=productList,
currentSearch=currentSearch,
categoryName=categoryName,
categories=categories)
def seller_inbox(item_id):
sessionUser = "" if 'sessionUser' not in session else session['sessionUser']
if sessionUser == "":
abort(404)
cursor = getCursor()[1]
cursor.execute(query().GET_ITEM_MESSAGES(item_id))
data = cursor.fetchall()
print(data)
messageList = []
for d in data:
if len(d) > 3:
messageObject = message.makeMessage(d)
messageList.append(messageObject)
cursor.execute(query().APPROVED_ITEM(item_id))
data = cursor.fetchone()
cursor.close()
messageProduct = product.makeProduct(data)
return render_template('seller-inbox.html',
sessionUser=sessionUser,
messages=messageList,
messageProduct=messageProduct)
def productPage(product_id):
cursor = getCursor()[1]
product_id = str(bleach.clean(product_id)) # sanitizing a bad redirect
cursor.execute(query().APPROVED_ITEM(product_id))
data = cursor.fetchall()
if len(data) == 0:
abort(404)
cursor.execute(query().USER_FOR_PRODUCT(product_id))
userObject = cursor.fetchone()
cursor.close()
productObject = product.makeProduct(data[0])
try:
if productObject.getStatus(
) == 0 and not session['sessionUser']['u_is_admin'] > 0:
abort(404)
except KeyError:
abort(404)
print("Redirecting to Product page", product_id)
return render_template("products/product.html",
product=productObject,
user=userObject)
def remediate_patients(sf, p1, p2):
""" method to perform data remediation on two duplicate patients """
soql = "SELECT Id, DTPC_Patient_Formal_Name__c, Patient_Status__c FROM DTPC_Patient__c WHERE Patient_Unique_Id__c='%s'" % p1
patient_1 = queries.query(sf, soql)
soql = "SELECT DTPC_Patient_Formal_Name__c, Patient_Status__c FROM DTPC_Patient__c WHERE Patient_Unique_Id__c='%s'" % p2
patient_2 = queries.query(sf, soql)
patient_1_id = patient_1[0]['Id']
soql = "SELECT Patient__c FROM DTPC_Program__c WHERE Patient__c='%s'" % patient_1_id
p1_programs = queries.query(sf, soql)
def home():
n = 5 # number of most recent items to grab
productList = []
categories = []
cursor = getCursor()[1]
cursor.execute(query().MOST_RECENT_ITEMS(n))
data = cursor.fetchall()
cursor.execute(query().fetchAllCategories())
allCategories = cursor.fetchall()
categories = [allCategories[i][0] for i in range(len(allCategories))]
print("categories fetched are: ", categories, " and type is: ")
feedback = []
productUsers = []
for d in data:
if len(d) == 16:
productObject = product.makeProduct(d)
productList.append(productObject)
cursor.execute(query().FULL_USER_FOR_PRODUCT(str(productObject.i_id)))
productUser = user.makeUser(cursor.fetchone())
productUsers.append(productUser.toDict())
cursor.close()
feedback.append(
"" if 'otherFeedback' not in session else session['otherFeedback'])
sessionUser = "" if 'sessionUser' not in session else session['sessionUser']
if 'sessionUser' in session:
feedback.append("Welcome Back " +
session['sessionUser']['u_fname'] + " " +
session['sessionUser']['u_lname'])
feedback.append("Here are the latest Items")
try:
session.pop('otherFeedback')
except KeyError:
pass
# Reseting filter options
if 'currentCategory' in session:
session.pop('currentCategory')
if 'sortOption' in session:
session.pop('sortOption')
if 'previousQuery' in session:
session.pop('previousQuery')
# Storing previous query for filtering
session['previousQuery'] = [product.toDict() for product in productList]
currentSearch = ""
categoryName = "All"
session['categories'] = categories
return render_template("home.html", products=session['previousQuery'], feedback=feedback, sessionUser=sessionUser,
sortOption="Sort By", currentSearch=currentSearch,categoryName=categoryName,categories=categories, productUsers=productUsers)
def main():
print("Kennedy Anukam's relational database management system,"
" enter .EXIT to end program")
# Continues until exit command entered
while True:
line = input().rstrip()
# Exit condition
if line.lower() == ".exit;" or line.lower() == ".exit":
break
# Check if query statement in input
elif len(line) > 0 and line.split()[0].lower() in phrases:
queries.query(line)
else:
datahelper.statement(line)
print("All done.")
def about_mem(member):
sessionUser = "" if 'sessionUser' not in session else session['sessionUser']
categories = []
cursor = getCursor()[1]
cursor.execute(query().fetchAllCategories())
allCategories = cursor.fetchall()
categories = [allCategories[i][0] for i in range(len(allCategories))]
cursor.close()
currentSearch = ""
categoryName = "All"
session['categories'] = categories
return render_template("about/info.html", name=dev[member]['name'],
title=dev[member]['title'],
image=dev[member]['img'],
description=dev[member]['description'],
linkedin=dev[member]['linkedin'],
github=dev[member]['github'],
email=dev[member]['email'], sessionUser=sessionUser,
currentSearch=currentSearch, categoryName=categoryName, categories=categories
)
def selectCategory(catName):
sessionUser = "" if 'sessionUser' not in session else session['sessionUser']
cursor = getCursor()[1]
print(catName)
feedback = []
if 'previousQuery' in session: # Some sort of filtering before
data = session['previousQuery']
print("data from prev query in Category ", data)
else:
cursor.execute(query().APPROVED_ITEMS_FOR_CATEGORY(catName))
data = [product.makeProduct(d).toDict() for d in cursor.fetchall()]
cursor.close()
data = [d for d in data if d['c_name'] == catName]
if len(data) == 0:
feedback.append("No Results Found, Consider these")
data = session['previousQuery']
else:
feedback.append(catName)
session['currentCategory'] = catName
session['categoryName'] = catName
productList = []
if 'sortOption' in session:
data = filter_data(data, session['sortOption'])
currentSearch = "" if 'currentSearch' not in session else session['currentSearch']
categoryName = "All" if 'categoryName' not in session else session['categoryName']
return render_template("home.html", products=data, feedback=feedback, sessionUser=sessionUser, sortOption="Sort By", currentSearch=currentSearch,categoryName=categoryName)
def send_query(sf):
soql = input("type in a query to send to salesforce\n\n>> ")
try:
answer = queries.query(sf, soql)
except:
answer = "You send an invalid query, try again"
print(answer)
def gather_object_names(sf):
""" returns a list of all object names in string format """
# use SOQL statement to get all API names of objects
soql = "SELECT QualifiedApiName FROM EntityDefinition order by QualifiedApiName"
query_objects = queries.query(sf, soql)
# formatting objects into a list (stored in a dict with same key)
return_objects = []
for the_object in query_objects:
return_objects.append(the_object['QualifiedApiName'])
return return_objects
def contact_seller_action(item_id, action):
sessionUser = "" if 'sessionUser' not in session else session['sessionUser']
if sessionUser == "":
abort(404)
if action == "sold":
db, cursor = getCursor()
cursor.execute(query().SELL_ITEM(item_id))
db.commit()
return redirect("/user-dashboard")
else:
abort(404)
def makeAndInsertMessageForSeller(buyerContact, buyerMessage, item_id,
sessionUser):
cursor = getCursor()[1]
cursor.execute(query().APPROVED_ITEM(item_id))
item = product.makeProduct(cursor.fetchone())
cursor.execute(query().USER_FOR_PRODUCT(item_id))
seller = cursor.fetchone()
completeMessageList = messageForSeller(
sessionUser['u_fname'] + " " + sessionUser['u_lname'], buyerContact,
buyerMessage, item.i_title, item.i_create_ts, item.i_price)
completeMessage = '\n'.join(message for message in completeMessageList)
print(query().INSERT_MESSAGE(completeMessage, sessionUser['u_id'],
seller[0], item_id))
cursor.execute(query().INSERT_MESSAGE(completeMessage, sessionUser['u_id'],
seller[0], item_id))
db.commit()
cursor.close()
session['otherFeedback'] = "Message Sent"
def contact_seller(item_id):
sessionUser = "" if 'sessionUser' not in session else session['sessionUser']
# if sessionUser == "":
# abort(404) # TODO lazy registration
if 'lazyRegistration' in session:
makeAndInsertMessageForSeller(session['buyerContact'],
session['buyerMessage'], item_id,
sessionUser)
session.pop('lazyRegistration')
session.pop('lazyPage')
return redirect("/")
if request.method == "GET":
print("Got a Get")
cursor = getCursor()[1]
cursor.execute(query().APPROVED_ITEM(str(item_id)))
itemObject = product.makeProduct(cursor.fetchone())
cursor.close()
currentItem = itemObject.toDict()
session['contact_seller_item'] = currentItem
if request.method == "POST":
buyerContact = str(bleach.clean(request.form['contactType']))
buyerMessage = str(bleach.clean(request.form['buyerMessage']))
isRegistered = not sessionUser == ""
session['item_id'] = item_id
if not isRegistered:
session['lazyRegistration'] = True
session['lazyPage'] = 'contact-seller'
session['buyerContact'] = buyerContact
session['buyerMessage'] = buyerMessage
print("going to login?")
return redirect("/login")
makeAndInsertMessageForSeller(buyerContact, buyerMessage, item_id,
sessionUser)
return redirect("/")
currentItem = "" if 'contact_seller_item' not in session else session[
'contact_seller_item']
try:
session.pop('contact_seller_item')
except KeyError:
print('yo, dat contact_seller_item was not in the session bro')
print('Have a nice day!')
return render_template('contact-seller.html',
sessionUser=sessionUser,
id=item_id,
currentItem=currentItem)
def main():
sf = connect_to_salesforce.connect()
cap_programs = read_report('capremediation.xlsx')
ids = []
for program in cap_programs:
ids.append(program['Program: ID'])
statuses = []
for id in ids:
soql = "SELECT DTPC_Status__c FROM DTPC_Program__c WHERE Id='%s'" % id
response = queries.query(sf, soql)
for status in response:
statuses.append(status['DTPC_Status__c'])
print(set(statuses))
def login():
cursor = getCursor()[1]
if request.method == "POST":
email = str(bleach.clean(request.form['email']))
pwd = str(bleach.clean(request.form['pwd']))
print(email, " tried to login")
cursor.execute(query().GET_USER_BY_EMAIL(email))
data = cursor.fetchone()
cursor.close()
if data is None:
flash("User not found!")
print("User not found!")
return render_template("login.html",
code=404,
message="Page Not Found")
print(data)
userObject = user.makeUser(data)
if sha256_crypt.verify(pwd, userObject.u_pwd):
print("Authentication Successful")
flash("Authentication Successful")
session['sessionUser'] = userObject.toDict()
session['sessionKey'] = int(time.time() * 1000)
if 'lazyRegistration' in session:
# session.pop('lazyRegistration')
# makeAndInsertMessageForSeller()
if session['lazyPage'] == 'contact-seller':
flash('Message Sent Successfully')
return redirect("/contact-seller/" + session['item_id'])
elif session['lazyPage'] == 'item-posting':
flash('Item Pending Approval')
return redirect("/item-posting")
return redirect("/")
else:
print("Authentication Failed!")
flash("Authentication Failed!")
return render_template("login.html",
code=401,
message="Unauthorized")
return render_template("login.html")
def about():
sessionUser = "" if 'sessionUser' not in session else session['sessionUser']
categories = []
cursor = getCursor()[1]
cursor.execute(query().fetchAllCategories())
allCategories = cursor.fetchall()
categories = [allCategories[i][0] for i in range(len(allCategories))]
cursor.close()
currentSearch = ""
categoryName = "All"
session['categories'] = categories
return render_template("about/about.html", sessionUser=sessionUser,
currentSearch=currentSearch, categoryName=categoryName, categories=categories)
def admin_page(user_id):
try:
if session['sessionUser']['u_id'] < 1 or session['sessionUser'][
'u_id'] != int(user_id):
abort(404)
except KeyError:
abort(404)
conncetion, cursor = getCursor()
categories = []
cursor.execute("SELECT * FROM user;")
print(cursor.fetchall())
# fetch the items for admin approval from db
cursor.execute(query().ALL_PENDING_LISTINGS())
data = cursor.fetchall()
# Fetch the categories from db
cursor.execute(query().fetchAllCategories())
allCategories = cursor.fetchall()
categories = [allCategories[i][0] for i in range(len(allCategories))]
productList = []
productListUsers = []
for d in data:
if len(d) == 16:
productObject = product.makeProduct(d)
productList.append(productObject)
cursor.execute(query().FULL_USER_FOR_PRODUCT(
str(productObject.i_u_id)))
productListUsers.append(user.makeUser(cursor.fetchone()))
cursor.execute(query().ALL_NON_ADMIN_APPROVED_USERS())
data = cursor.fetchall()
userList = []
for d in data:
if len(d) == 9:
userObject = user.makeUser(d)
userList.append(userObject)
cursor.execute(query().ALL_APPROVED_LISTINGS())
data = cursor.fetchall()
approvedProducts = []
approvedProductsUsers = []
for d in data:
if len(d) == 16:
productObject = product.makeProduct(d)
approvedProducts.append(productObject)
cursor.execute(query().FULL_USER_FOR_PRODUCT(
str(productObject.i_u_id)))
approvedProductsUsers.append(user.makeUser(cursor.fetchone()))
sessionUser = "" if 'sessionUser' not in session else session['sessionUser']
currentSearch = ""
categoryName = "All"
session['categories'] = categories
return render_template("admin/admin.html",
sessionUser=sessionUser,
id=user_id,
products=productList,
users=userList,
approvedProducts=approvedProducts,
currentSearch=currentSearch,
categoryName=categoryName,
categories=categories,
approvedProductsUsers=approvedProductsUsers,
productListUsers=productListUsers)
def searchPage():
cursor = getCursor()[1]
print(len(request.form))
currentSearch = "" if 'currentSearch' not in session else session['currentSearch']
# categoryName = "Category" if 'categoryName' not in session else session['categoryName']
categories = [] if 'categories' not in session else session['categories']
formsLen = len(request.form)
feedback, data = [], ""
productList = []
if request.method == 'GET':
pass
# currentSearch = "" if 'currentSearch' not in session else session['currentSearch']
if request.method == 'POST':
cursor = getCursor()[1]
if categories == "":
cursor.execute(query().fetchAllCategories())
allCategories = cursor.fetchall()
categories = [allCategories[i][0] for i in range(len(allCategories))]
if formsLen > 0:
search = request.form['text']
catName = "All" if request.form['category'] == "All" else request.form['category']
if catName != "":
session['categoryName'] = catName
print("catname is: ",catName)
search = str(bleach.clean(search)) # sanitizing a bad search
print("search recieved:", search)
session['currentSearch'] = search
print("sessions's search", session['currentSearch'])
currentSearch = "" if 'currentSearch' not in session else session['currentSearch']
cursor.execute(query().SEARCH_QUERY(search,catName))
data = cursor.fetchall()
print("All items?", data)
if len(data) == 0:
if formsLen > 0:
feedback.append("No Results, Consider these Items")
cursor.execute(query().ALL_APPROVED_LISTINGS())
data = cursor.fetchall()
# cursor.close()
productUsers = []
# if catName != "":
# data = [d for d in data if d['c_name'] == catName]
for d in data:
if len(d) > 11:
productObject = product.makeProduct(d)
productList.append(productObject)
data = [productObject.toDict() for d in cursor.fetchall()]
cursor.execute(query().FULL_USER_FOR_PRODUCT(str(productObject.i_id)))
productUser = user.makeUser(cursor.fetchone())
productUsers.append(productUser)
cursor.close()
session['previousQuery'] = [productObject.toDict()
for productObject in productList]
if 'currentCategory' in session:
session.pop('currentCategory')
data = session['previousQuery']
if len(feedback) == 0 and formsLen != 0:
if len(data) == 1:
feedback.append(str(len(data)) + " Result Found")
else:
feedback.append(str(len(data)) + " Results Found")
sessionUser = "" if 'sessionUser' not in session else session['sessionUser']
# currentSearch = "" if 'currentSearch' not in session else session['currentSearch']
categoryName = "All" if 'categoryName' not in session else session['categoryName']
return render_template("home.html", products=data, feedback=feedback, sessionUser=sessionUser, sortOption="Sort By", currentSearch=currentSearch,categoryName=categoryName,categories=categories, productUsers=productUsers)
def register():
cursor = getCursor()[1]
if request.method == "POST":
print(request.form)
email = str(bleach.clean(request.form['email']))
password = sha256_crypt.encrypt(
str(bleach.clean(request.form['password'].strip())))
confirm_password = sha256_crypt.encrypt(
str(bleach.clean(request.form['confirm-password'].strip())))
fname = str(bleach.clean(request.form['fname']))
lname = str(bleach.clean(request.form['lname']))
created_ts = str(bleach.clean(time.strftime('%Y-%m-%d %H:%M:%S')))
updated_ts = str(bleach.clean(time.strftime('%Y-%m-%d %H:%M:%S')))
if not request.form['password'] == request.form['confirm-password']:
pass_temp = request.form['password']
confirm_pass_temp = request.form['confirm-password']
print(pass_temp, confirm_pass_temp)
print(pass_temp == confirm_pass_temp)
flash("passwords do not match")
return redirect("/register")
# check if user already exists
cursor.execute(query().GET_USER_BY_EMAIL(email))
data = cursor.fetchone()
if data is not None:
print("Registeration of " + email +
" Failed. User Already Exists!")
flash("Registeration of " + email +
" Failed. User Already Exists!")
return redirect("/login")
if not email.endswith("@mail.sfsu.edu"):
flash("email needs to end with @mail.sfsu.edu")
return redirect("/register")
# make new user row in db
print(query().INSERT_USER(email, password, fname, lname, created_ts,
updated_ts))
d = cursor.execute(query().INSERT_USER(email, password, fname, lname,
created_ts, updated_ts))
print(d)
db.commit()
if d == 1:
cursor.execute(query().GET_USER_BY_EMAIL(email))
session['sessionUser'] = user.makeUser(cursor.fetchone()).toDict()
print("Registeration of", email, "Successful")
flash("Registeration of " + email + " Successful")
session['sessionKey'] = int(time.time() * 1000)
if 'lazyRegistration' in session:
# session.pop('lazyRegistration')
if session['lazyPage'] == 'contact-seller':
return redirect("/contact-seller/" + session['item_id'])
elif session['lazyPage'] == 'item-posting':
return redirect("/item-posting")
return redirect("/")
cursor.close()
print("Simple Register Page Click")
return render_template("register.html")
def api_query(request):
q = request.GET.get('q', '')
n = request.GET.get('n', 20)
n = int(n)
queryresults = queries.query(searchstring=q, maxresults=n)
return HttpResponse(json.dumps(queryresults))
def item_posting():
# cursor = db.cursor()
print(request.form)
formsLen = len(request.form)
images_path = []
if 'lazyRegistration' in session:
print('session file is: ', session_file)
insertItemPost(session['item_name'], session['item_category'],
session['item_desc'], session['item_price'],
session['is_tradable'], session['item_images'],
session['sessionUser'], True)
session_file.clear()
session.pop('lazyRegistration')
session.pop('lazyPage')
print('Rediret from lazy login to home')
# return render_template('home.html', sessionUser=session['sessionUser'], id=-1,categoryName="Catogory")
return redirect("/")
sessionUser = "" if 'sessionUser' not in session else session['sessionUser']
# print("Session user", sessionUser)
if request.method == "POST":
if request.form:
print("printing request form", request.form)
if formsLen > 0:
item_name = str(bleach.clean(request.form['item_title']))
item_category = request.form['category']
item_desc = str(bleach.clean(request.form['item_desc']))
item_price = request.form['item_price']
is_tradable = '0' if 'isTradable' not in request.form else request.form[
'isTradable'] #str(1) if 'isTradable' in request.form else str(0)
item_images = []
if sessionUser == "":
session['item_images'] = []
# store image in separate folder as per category
UPLOAD_FOLDER = 'static/images/' + item_category
session['UPLOAD_FOLDER'] = UPLOAD_FOLDER
for file in request.files.getlist('file'):
if file.filename == '':
print('No file selected for uploading')
else:
# session['item_image'].append(base64.b64encode(file.read()).decode('ascii'))
if sessionUser == "":
# session_file.append(file)
if file and allowed_file(file.filename):
filename = secure_filename(file.filename)
# unique filename
uuid_val = uuid.uuid1()
filename = str(uuid_val) + '.' + \
filename.rsplit('.', 1)[1].lower()
print(os.path.curdir)
file_path = os.path.join(session['UPLOAD_FOLDER'],
filename)
print("file path from item-posting post req is:",
file_path)
# file = open(file,"wr")
file.save(file_path)
session['item_images'].append(file_path)
else:
item_images.append(file)
if sessionUser == "":
session['lazyRegistration'] = True
session['lazyPage'] = 'item-posting'
session['item_name'] = item_name
session['item_category'] = item_category
session['item_desc'] = item_desc
session['item_price'] = item_price
session['is_tradable'] = is_tradable
# session['item_userid'] =
# session['item_images'] = None #item_images
print("going to login?")
return redirect("/login")
else:
# sessionUser = session['sessionUser']
insertItemPost(item_name, item_category, item_desc, item_price,
is_tradable, item_images, sessionUser, False)
if request.method == "GET":
cursor = getCursor()[1]
cursor.execute(query().fetchAllCategories())
allCategories = cursor.fetchall()
cursor.close()
categories = [allCategories[i][0] for i in range(len(allCategories))]
return render_template("item-posting.html", categories=categories)
flash("Item Posted Successfully")
return redirect('/')
def verify_duplicate_patients(sf, p1, p2):
""" this method follows the logic in the SOP for verifying duplicate patients in the PCP """
soql = "SELECT First_Name__c, Last_Name__c, Date_of_Birth__c, Gender__c, Patient_Email__c, Primary_State__c, " \
"Primary_City__c, Primary_Street__c, Primary_ZIP__c, Phone_1_Details__c FROM DTPC_Patient__c " \
"WHERE Patient_Unique_Id__c='%s'" % p1
patient_1 = queries.query(sf, soql)
soql = "SELECT First_Name__c, Last_Name__c, Date_of_Birth__c, Gender__c, Patient_Email__c, Primary_State__c, " \
"Primary_City__c, Primary_Street__c, Primary_ZIP__c, Phone_1_Details__c FROM DTPC_Patient__c " \
"WHERE Patient_Unique_Id__c='%s'" % p2
patient_2 = queries.query(sf, soql)
print(patient_1)
print(patient_2)
# because we are using unique ID, we know there is only one record in the return string
p1 = patient_1[0]
p1_fname = p1['First_Name__c']
p1_lname = p1['Last_Name__c']
p1_name = p1_fname + p1_lname
p1_name = re.sub('DUPLICATE', '', p1_name)
p1_dob = p1['Date_of_Birth__c']
p1_gender = p1['Gender__c']
p1_email = p1['Patient_Email__c']
p1_addr = p1['Primary_Street__c'] + ', ' + p1[
'Primary_City__c'] + ', ' + p1['Primary_ZIP__c']
p2 = patient_2[0]
p2_fname = p2['First_Name__c']
p2_lname = p2['Last_Name__c']
p2_name = p2_fname + p2_lname
p2_name = re.sub('DUPLICATE', '', p2_name)
p2_dob = p2['Date_of_Birth__c']
p2_gender = p2['Gender__c']
p2_email = p2['Patient_Email__c']
p2_addr = p2['Primary_Street__c'] + ', ' + p1[
'Primary_City__c'] + ', ' + p1['Primary_ZIP__c']
if p1_name == p2_name and p1_dob == p2_dob:
return True
elif p1_name == p2_name and p1_gender == p2_gender:
return True
elif p1_email == p2_email and p1_email is not None:
return True
elif fuzz.ratio(p1_name, p2_name) > 85 and fuzz.ratio(p1_addr,
p2_addr) > 85:
return True
else:
while True:
print(
"These patients need manual verification, type T to verify, F to invalidate"
)
print(p1)
print(p2)
select = input("\n>> ")
if select == 'T':
return True
elif select == 'F':
return False
