def checkUserExist(email):
cursor, conn = flaskConfig.connect_db()
cursor.execute("SELECT * FROM user WHERE user_email = (?)", (email,))
fetchall = cursor.fetchall()
conn.close()
gc.collect()
return fetchall
def livesearch(searchQuery):
cursor, conn = flaskConfig.connect_db()
# cursor.execute('SELECT * FROM part WHERE part_name LIKE (?)', ('%'+searchQuery+'%',))
cursor.execute('CREATE VIRTUAL TABLE IF NOT EXISTS virt_part USING fts4(part_id,part_name,part_manufacturer,part_model_number,part_place_of_origin,part_description,part_internal_or_external,part_type,part_med_res_img,part_thumb_img,part_view_count)')
cursor.execute("INSERT INTO virt_part(part_id,part_name,part_manufacturer,part_model_number,part_place_of_origin,part_description,part_internal_or_external,part_type,part_med_res_img,part_thumb_img,part_view_count) SELECT part_id,part_name,part_manufacturer,part_model_number,part_place_of_origin,part_description,part_internal_or_external,part_type,part_med_res_img,part_thumb_img,part_view_count FROM part")
cursor.execute('SELECT * FROM virt_part WHERE part_name MATCH (?)',(searchQuery,))
fetchall = cursor.fetchall()
cursor.execute('DROP TABLE IF EXISTS virt_part')
# -? This limits results to 50 items.
fetchall = fetchall[:51]
searchResults = {}
for i in fetchall:
dictName = str(i[0])
iDictionary = {dictName:
{'part_id':i[0],
'part_name':i[1],
'part_manufacturer':i[2],
'part_model_number':i[3],
'part_place_of_origin':i[4],
'part_description':i[5][:750],
'part_internal_or_external':i[6],
'part_type':i[7],
'part_med_res_img':i[8],
'part_thumb_img':i[9],
'part_view_count':i[10]}
}
searchResults.update(iDictionary)
return make_response(jsonify({'200': 'OK','searchResults':searchResults}), 200)
def apiPartRetailers(partId):
cursor, conn = flaskConfig.connect_db()
cursor.execute("SELECT * FROM retailer WHERE part_id = (?)", (partId,))
fetchall = cursor.fetchall()
conn.close()
gc.collect()
if len(fetchall) > 0:
fetchall = fetchall[0]
# -? Convert tuple into list.
fetch_list = list(fetchall)
# -? Split list into featured and also lists.
featured = fetch_list[2]
also_list = fetch_list[3:]
# -? removes all None from also_list
also_list = [i for i in also_list if i is not None]
# -? Shuffles order of also_list
shuffle(also_list)
return make_response(jsonify(featured=featured,also_list=also_list), 200)
else:
return make_response(jsonify({'302': 'FOUND', 'details':'no retailers found', 'tag':'noRetailers'}), 302)
def nextPartSeq():
# This will return value of the next sequence number for part table.
cursor, conn = flaskConfig.connect_db()
cursor.execute("SELECT * from sqlite_sequence WHERE name = 'part'")
fetchall = cursor.fetchall()
conn.close()
gc.collect()
nextPartId = str(fetchall[0][1] + 1)
return nextPartId
def processDisapprove(self, pendingEditId, userId, vote):
cursor, conn = flaskConfig.connect_db()
# This will add the vote to the pendingedit table.
cursor.execute("UPDATE pendingedit SET downvote = downvote + 1 WHERE pendingedit_id = (?)",(pendingEditId,))
conn.commit()
# This will now add the user to pendinvvote table so they can't vote twice.
cursor.execute("INSERT INTO pendingvote (pendingvote_id, user_id, pendingedit_id, vote) VALUES (?,?,?,?)", (None, userId, pendingEditId, vote))
conn.commit()
conn.close()
gc.collect()
def voteFetch(self, userId, pendingEditId):
cursor, conn = flaskConfig.connect_db()
cursor.execute("""SELECT pendingvote.pendingvote_id, pendingvote.user_id, pendingvote.pendingedit_id, pendingvote.vote from pendingvote
INNER JOIN pendingedit
ON pendingedit.pendingedit_id = pendingvote.pendingedit_id
WHERE pendingvote.user_id = (?) AND pendingvote.pendingedit_id = (?)""", (userId, pendingEditId))
fetchall = cursor.fetchall()
conn.close()
gc.collect()
return fetchall
def processDownvote(self, currentPartId, compatibleToPartId, userId, compatibilityId):
cursor, conn = flaskConfig.connect_db()
# This will add the downvote to the db.
cursor.execute("UPDATE compatibility SET not_compatible_score = not_compatible_score + 1 WHERE part_id = (?) AND compatible_to_part_id = (?)", (currentPartId, compatibleToPartId,))
conn.commit()
# This will now add the user to vote table so they can't vote twice.
cursor.execute("INSERT INTO vote (vote_id, user_id, compatibility_id, vote) VALUES (?,?,?,?)", (None, userId, compatibilityId, 'downvote',))
conn.commit()
conn.close()
gc.collect()
def voteFetch(self, userId, compatibilityId):
cursor, conn = flaskConfig.connect_db()
cursor.execute("""SELECT vote.vote_id, vote.user_id, vote.compatibility_id, vote.vote from vote
INNER JOIN compatibility
ON compatibility.compatibility_id = vote.compatibility_id
WHERE vote.user_id = (?) AND vote.compatibility_id = (?)""", (userId,compatibilityId,))
fetchall = cursor.fetchall()
conn.close()
gc.collect()
return fetchall
def checkExist():
cursor, conn = flaskConfig.connect_db()
cursor.execute("""SELECT part_id, compatible_to_part_id FROM compatibility
WHERE part_id == (?) AND compatible_to_part_id == (?)""" , (partId, post['compatible_to_part_id']))
fetchall = cursor.fetchall()
if len(fetchall) > 0:
# yup, this combo already exists
return True
else:
# nope, this is a unique entry
return False
def findallCompIncomp(self, partid):
cursor, conn = flaskConfig.connect_db()
cursor.execute("""SELECT compatibility.part_id, compatibility.compatible_to_part_id
FROM compatibility WHERE compatibility.part_id = (?)""", (partid,))
compatiblePairs = cursor.fetchall()
allCompatibleQuery = []
for i in compatiblePairs:
compatible_to_part_id = i[1]
cursor.execute("""SELECT *
FROM part INNER JOIN compatibility ON part.part_id = compatibility.compatible_to_part_id
WHERE compatibility.compatible_to_part_id = (?) AND compatibility.part_id = (?)""", (compatible_to_part_id, partid,))
fetch = cursor.fetchall()
allCompatibleQuery.append(fetch[0])
return allCompatibleQuery
def selectedPartInfo(self, partId):
cursor, conn = flaskConfig.connect_db()
cursor.execute("SELECT * FROM part WHERE part_id = (?)", (partId,))
curentPartFetchall = cursor.fetchall()
curentPartTuple = curentPartFetchall[0]
conn.close()
gc.collect()
currentPartDict = {'part_id':curentPartTuple[0],
'part_name':curentPartTuple[1],
'part_manufacturer':curentPartTuple[2],
'part_model_number':curentPartTuple[3],
'part_place_of_origin':curentPartTuple[4],
'part_estimated_price':curentPartTuple[5],
'part_description':curentPartTuple[6],
'part_internal_or_external':curentPartTuple[7],
'part_type':curentPartTuple[8],
'part_full_res_img':curentPartTuple[9],
'part_med_res_img':curentPartTuple[10],
'part_thumb_img':curentPartTuple[11],
'part_added_to_db':curentPartTuple[12],
'part_view_count':curentPartTuple[13]}
return currentPartDict
def apiRegister():
# Step 1: Initialize data for processing.
formData = request.get_json()
firstName = formData['firstName']
lastName = formData['lastName']
email = str(formData['email'])
password = sha256_crypt.encrypt((str(formData['password'])))
# Step 2: Verify POST method and search DB for same email to avoid duplicate accounts.
if request.method == 'POST':
cursor, conn = flaskConfig.connect_db()
cursor.execute("SELECT * FROM user WHERE user_email = (?)", (email,))
fetchall = cursor.fetchall()
# Step 3: If exist, return 302. If not, add formData to DB and create new sessions.
if len(fetchall) > 0:
# This email already exists in the database.
return make_response(jsonify({'302': 'FOUND', 'details':'This email already exists in the database.', 'tag':'emailExists'}), 302)
else:
# This email does not already exist in database. Now creating new user account.
cursor.execute("INSERT INTO user VALUES (?,?,?,?,?)", (None, email, password, firstName, lastName,))
conn.commit()
cursor.execute("SELECT * FROM user WHERE user_email = ('%s')" % (email))
fetchall = cursor.fetchall()
newUserId = fetchall[0][0]
conn.close()
gc.collect()
session['logged_in'] = True
session['user_id'] = newUserId
session['email'] = email
session['user_firstname'] = firstName
session['user_lastname'] = lastName
return make_response(jsonify({'200': 'OK','details':'New user account successfully created!','email':email,'tag':'register'}), 200)
return make_response(jsonify({'200': 'OK'}), 200)
def apiSuggestEdit(partId,inQuestion):
# -- GET REQUESTS
if request.method == 'GET':
# -? Will find all rows that match the requested partId and category in question.
cursor, conn = flaskConfig.connect_db()
cursor.execute("SELECT * FROM pendingedit WHERE part_id = (?) AND inquestion = (?)", (partId, inQuestion,))
pendingFetch = cursor.fetchall()
gc.collect()
dict = {}
for i in pendingFetch:
dictName = str(i[0])
userId = i[2]
# -? Retrieves the user name for each row in pendingFetch.
cursor, conn = flaskConfig.connect_db()
cursor.execute("SELECT user_firstname, user_lastname FROM user WHERE user_id = (?) ", (userId,))
fetchall = cursor.fetchall()
userfetch = fetchall
userfetch = userfetch[0]
gc.collect()
iDict = {dictName:{'pendingId':i[0],
'partId':i[1],
'userId':userId,
'userName':userfetch[0] + ' ' + userfetch[1][:1]+'.',
'inQuestion':i[3],
'suggestion':i[4],
'reasoning':i[5],
'downvote':i[6],
'upvote':i[7],
'timestamp':i[8]
}}
dict.update(iDict)
return make_response(jsonify(apiSuggestEdit = dict))
# -- POST REQUESTS
elif request.method == 'POST':
post = request.get_json()
print post
#HANDLE VOTE REQUEST
try:
if post['vote'] == 'approve' or 'disapprove':
# Step 1: Check if user is logged in already.
if session.get('logged_in') == None:
return make_response(jsonify({'302': 'FOUND', 'details':'login required','tag':'pendingvote'}),302)
# Step 2: Initialize data for vote processing
userId = session['user_id']
pendingEditId = post['pendingEditId']
vote = post['vote']
# Step 3: Evaluates DB query results to see if user has already voted on this item.
voteFetch = processVote().voteFetch(userId,pendingEditId)
if len(voteFetch) > 0:
# Match found. Responding with JSON error.
return make_response(jsonify({'302':'FOUND','details':'user already voted for this suggested edit','tag':'alreadyvoted'}),302)
else:
if post['vote'] == 'approve':
# No match found. Now writing approval to database.
processVote().processApprove(pendingEditId, userId, vote)
return make_response(jsonify({'200': 'OK', 'details':'approval vote to database success!', 'tag':'approval success'}), 200)
elif post['vote'] == 'disapprove':
# No match found. Now writing disapproval to database.
processVote().processDisapprove(pendingEditId, userId, vote)
return make_response(jsonify({'200': 'OK', 'details':'disapproval to database success!', 'tag':'disapproval success'}), 200)
except:
pass
try:
inQuestionTags = ['partName','manufacturer','modelNumber','placeOfOrigin','partType','internalOrExternal','description','forSaleAt']
if post['editCurrentTag'] in inQuestionTags:
inQuestion = post['editCurrentTag']
edit = post['formData']['edit']
reasoning = post['formData']['reasoning']
userId = post['userId']
partId = post['partId']
# Adds edit suggestion to database
cursor, conn = flaskConfig.connect_db()
cursor.execute('INSERT INTO pendingedit VALUES (?,?,?,?,?,?,?,?,?)',(None, partId, userId, inQuestion, edit, reasoning, 0, 0, datetime.datetime.now()))
conn.commit()
conn.close()
gc.collect()
return make_response(jsonify({'200': 'OK', 'details':'edit suggestion successfully added to database!', 'tag':'editSuggestSuccess'}))
except:
pass
return make_response(jsonify({'200': 'OK', 'details':'succesfully connected', 'tag':'test'}), 200)
def apiAddToDb(partId, compmode):
# Step 1: Obtain form data for processing.
formData = request.form
file = request.files["file"]
def nextPartSeq():
# This will return value of the next sequence number for part table.
cursor, conn = flaskConfig.connect_db()
cursor.execute("SELECT * from sqlite_sequence WHERE name = 'part'")
fetchall = cursor.fetchall()
conn.close()
gc.collect()
nextPartId = str(fetchall[0][1] + 1)
return nextPartId
# Step 2: Find what is the next seq number for part table.
nextPartId = nextPartSeq()
# This will check to verify if file name is allowed within ALLOWED_EXTENSIONS.
def allowed_file(filename):
return "." in filename and filename.rsplit(".", 1)[1] in ALLOWED_EXTENSIONS
# This will rename the uploaded file to be suited for usage in /images/part-images/..
def rename_file(filename):
partName = formData["partName"].replace(" ", "-").lower()[0:18]
filename = str(nextPartId) + "-" + "1" + "-" + str(partName)
return filename
# # This will remove quotes and single quotes from partDescription.
# def renamePartDesc(string):
# string = string.replace('"'," ")
# string = string.replace("'"," ")
# return string
# partDescription = renamePartDesc(formData['partDescription'])
if file and allowed_file(file.filename):
# DEBUG: code does not like part names that contain special characters such as '/' or '.'
filename = secure_filename(file.filename)
filename = rename_file(filename)
file.save(os.path.join(UPLOAD_FOLDER, filename))
saved_location = os.path.join(UPLOAD_FOLDER, filename)
work_file = Image.open(saved_location)
full_res = work_file.copy()
med = work_file.copy()
thumb = work_file.copy()
imgUrlDict = {"full_res_url": "", "med_res_url": "", "thumb_res_url": ""}
def make_full_res():
new_dimensions = (900, 900)
full_res.thumbnail(new_dimensions)
full_res.save(UPLOAD_FOLDER + "full-res/%s" % (filename + "-full-res") + ".jpg", "JPEG")
full_res_name = filename + "-full-res" + ".jpg"
full_res_url = "images/part-images/full-res/" + full_res_name
imgUrlDict["full_res_url"] = full_res_url
def make_med():
new_dimensions = (400, 400)
med.thumbnail(new_dimensions)
med.save(UPLOAD_FOLDER + "med-res/%s" % (filename + "-med") + ".jpg", "JPEG")
med_name = filename + "-med" + ".jpg"
med_res_url = "images/part-images/med-res/" + med_name
imgUrlDict["med_res_url"] = med_res_url
def make_thumb():
new_dimensions = (150, 150)
thumb.thumbnail(new_dimensions)
thumb.save(UPLOAD_FOLDER + "thumb/%s" % (filename + "-thumb") + ".jpg", "JPEG")
thumb_name = filename + "-thumb" + ".jpg"
thumb_res_url = "images/part-images/thumb/" + thumb_name
imgUrlDict["thumb_res_url"] = thumb_res_url
make_full_res()
make_med()
make_thumb()
# This will save new submitted part to part table.
cursor, conn = flaskConfig.connect_db()
cursor.execute(
"""INSERT INTO part
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)
""",
(
None,
formData["partName"],
formData["partManufacturer"],
formData["partModelNumber"],
formData["placeOfOrigin"],
None,
formData["partDescription"],
formData["internalOrExternal"],
formData["partType"],
imgUrlDict["full_res_url"],
imgUrlDict["med_res_url"],
imgUrlDict["thumb_res_url"],
None,
None,
),
)
conn.commit()
conn.close()
gc.collect()
# This will add new submitted part to compatibility table & rank compatibility score according to URL param.
if partId != "new" and compmode != "new":
compatible_score = 0
not_compatible_score = 0
if compmode == "green":
compatible_score = 1
elif compmode == "red":
not_compatible_score = 1
cursor, conn = flaskConfig.connect_db()
cursor.execute(
"INSERT INTO compatibility VALUES (?,?,?,?,?)",
(None, partId, nextPartId, compatible_score, not_compatible_score),
)
conn.commit()
conn.close()
gc.collect()
return make_response(
jsonify(
{"200": "OK", "details": "Part added to database success!", "nextPartId": nextPartId, "tag": "addtodb"}
),
200,
)
def apiWhyMod(compId,whymod):
# -- GET REQUESTS
if request.method == 'GET':
if whymod == 'all':
cursor, conn = flaskConfig.connect_db()
cursor.execute("SELECT * FROM whymod WHERE compatibility_id = ('%s')" % (compId))
fetchall = cursor.fetchall()
fetchall = fetchall
conn.close()
gc.collect()
if len(fetchall) < 1:
return make_response(jsonify({'302': 'FOUND', 'details':'no results found','tag':'allNoRes'}), 302)
dict = {}
for i in fetchall:
dictName = str(i[0])
iDict = {dictName:{'whymod_id':i[0],
'compatibility_id':i[1],
'user_id':i[2],
'user_firstname':i[3],
'user_lastname':i[4][:1]+'.',
'whyormod':i[5],
'title':i[6],
'desc':i[7],
'helpful':i[8],
'timestamp':i[9][0:10]}}
dict.update(iDict)
return make_response(jsonify(whymod=dict), 200)
elif whymod == 'why':
cursor, conn = flaskConfig.connect_db()
cursor.execute("SELECT * FROM whymod WHERE compatibility_id = ('%s') AND whyormod = ('%s')" % (compId,whymod))
fetchall = cursor.fetchall()
fetchall = fetchall
conn.close()
gc.collect()
if len(fetchall) < 1:
return make_response(jsonify({'302': 'FOUND', 'details':'no results found','tag':'whyNoResults'}), 302)
dict = {}
for i in fetchall:
dictName = str(i[0])
iDict = {dictName:{'whymod_id':i[0],
'compatibility_id':i[1],
'user_id':i[2],
'user_firstname':i[3],
'user_lastname':i[4][:1]+'.',
'whyormod':i[5],
'title':i[6],
'desc':i[7],
'helpful':i[8],
'timestamp':i[9][0:10]}}
dict.update(iDict)
return make_response(jsonify(whymod=dict), 200)
elif whymod == 'mod':
cursor, conn = flaskConfig.connect_db()
cursor.execute("SELECT * FROM whymod WHERE compatibility_id = ('%s') AND whyormod = ('%s')" % (compId,whymod))
fetchall = cursor.fetchall()
fetchall = fetchall
conn.close()
gc.collect()
if len(fetchall) < 1:
return make_response(jsonify({'302': 'FOUND', 'details':'no results found','tag':'modNoRes'}), 302)
dict = {}
for i in fetchall:
dictName = str(i[0])
iDict = {dictName:{'whymod_id':i[0],
'compatibility_id':i[1],
'user_id':i[2],
'user_firstname':i[3],
'user_lastname':i[4][:1]+'.',
'whyormod':i[5],
'title':i[6],
'desc':i[7],
'helpful':i[8],
'timestamp':i[9][0:10]}}
dict.update(iDict)
return make_response(jsonify(whymod=dict), 200)
# -- POST REQUESTS
elif request.method == 'POST':
# HANDLE POST REQUESTS TO 'MOD'
if whymod == 'mod':
post = request.get_json()
print post
cursor, conn = flaskConfig.connect_db()
# refer to this http://stackoverflow.com/questions/3952543/python-and-sqlite-escape-input
cursor.execute('INSERT INTO whymod VALUES (?,?,?,?,?,?,?,?,?,?)', (None, post['compatibility_id'], post['user_id'], post['user_firstname'],post['user_lastname'],'mod',post['title'],post['desc'],0,datetime.datetime.now()))
conn.commit()
conn.close()
gc.collect()
return make_response(jsonify({'200': 'OK', 'details':'mod successfully added to database!', 'tag':'shareModSuccess'}), 200)
# HANDLE POST REQUESTS TO 'WHY'
elif whymod == 'why':
post = request.get_json()
print post
# This will add the new why post to database
cursor, conn = flaskConfig.connect_db()
# refer to this http://stackoverflow.com/questions/3952543/python-and-sqlite-escape-input
cursor.execute('INSERT INTO whymod VALUES (?,?,?,?,?,?,?,?,?,?)', (None, post['compatibility_id'], post['user_id'], post['user_firstname'],post['user_lastname'],'why',post['title'],post['desc'],0,datetime.datetime.now()))
conn.commit()
conn.close()
gc.collect()
return make_response(jsonify({'200': 'OK', 'details':'why successfully added to database!', 'tag':'shareWhySuccess'}), 200)
# HANDLE POST REQUESTS TO 'VOTE'
elif whymod == 'vote':
post = request.get_json()
# Step 1: Check to see if this user has already voted on this particular whymod_id.
cursor, conn = flaskConfig.connect_db()
cursor.execute('SELECT * FROM whymodvote WHERE user_id = (?) AND whymod_id = (?)',(post['user_id'],post['whymod_id']))
fetchall = cursor.fetchall()
print fetchall
# Returns 302 error if user has already voted on this particular whymod_id.
if len(fetchall) > 0:
return make_response(jsonify({'302': 'FOUND', 'details':'user already voted for this whymod_id', 'tag':'alreadyvoted'}), 302)
# Step 2: Else writes new vote to database and returns success JSON.
else:
print 'user has not voted on this particular whymod_id'
# increment helpful in whymod by one point
cursor, conn = flaskConfig.connect_db()
cursor.execute('UPDATE whymod SET helpful = helpful + 1 WHERE whymod_id = (?)',(post['whymod_id'],))
conn.commit()
# add user to whymodvote to prevent multiple votes.
cursor.execute('INSERT INTO whymodvote VALUES (?,?,?)', (None,post['user_id'],post['whymod_id'],))
conn.commit()
conn.close()
gc.collect()
return make_response(jsonify({'200': 'OK', 'details':'whymod vote successfully added to database!', 'tag':'WhyModVoteSuccess'}), 200)
def apiAddPart(partId, compmode):
if session.get('user_id') == None:
return make_response(jsonify({'302': 'FOUND', 'details':'login required', 'tag':'addpart'}), 302)
# -- GET REQUESTS
if request.method == 'GET':
searchQuery = request.args.get('search')
cursor, conn = flaskConfig.connect_db()
cursor.execute("SELECT * FROM part WHERE part_name LIKE ('%s')" % ('%'+searchQuery+'%'))
fetchall = cursor.fetchall()
searchResults = {}
for i in fetchall:
dictName = str(i[0])
iDictionary = {dictName:
{'part_id':i[0],
'part_name':i[1],
'part_manufacturer':i[2],
'part_model_number':i[3],
'part_place_of_origin':i[4],
'part_description':i[6],
'part_internal_or_external':i[7],
'part_type':i[8],
'part_med_res_img':i[10],
'part_thumb_img':i[11],
'alreadyAssociated':None}
}
searchResults.update(iDictionary)
return jsonify(searchResults = searchResults)
# -- POST REQUESTS
elif request.method == 'POST':
# Step 1: Initialize data for add part processing
post = request.get_json()
compatible_score = 0;
not_compatible_score = 0;
if compmode == 'green':
compatible_score = 1
elif compmode == 'red':
not_compatible_score = 1
def checkExist():
cursor, conn = flaskConfig.connect_db()
cursor.execute("""SELECT part_id, compatible_to_part_id FROM compatibility
WHERE part_id == (?) AND compatible_to_part_id == (?)""" , (partId, post['compatible_to_part_id']))
fetchall = cursor.fetchall()
if len(fetchall) > 0:
# yup, this combo already exists
return True
else:
# nope, this is a unique entry
return False
# Step 2: will write to DB if this compatibility combination does not already exist.
if checkExist() == False:
cursor, conn = flaskConfig.connect_db()
cursor.execute('INSERT INTO compatibility VALUES (?,?,?,?,?)', (None, partId, post['compatible_to_part_id'], compatible_score, not_compatible_score ))
conn.commit()
conn.close()
gc.collect()
return make_response(jsonify({'200': 'OK', 'details':'Part successfully added to compatibility list', 'tag':'addpart'}), 200)
else:
return make_response(jsonify({'302': 'FOUND', 'details':'Write to DB declined. These two parts are already associated.', 'tag':'addpart'}), 302)