def get_visitors():
if 'user' in session.keys():
cursor = db.cursor(pymysql.cursors.DictCursor)
sql_string = "select User.username, email, count(id) as visitCount "\
+ "from User left join Visits on User.username = Visits.username "\
+ "where userType = 'visitor' Group by Visits.username;"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print(e)
return
visits = cursor.fetchall()
list = []
for visit in visits:
print(visit)
dict = {
"username": visit['username'],
"email": visit['email'],
"visitCount": visit['visitCount']
}
list.append(dict)
return_string = json.dumps(list,
sort_keys=True,
indent=4,
separators=(',', ': '))
print(return_string)
return return_string
else:
return "not logged in"
def get_user_properties_from_attribute():
if 'user' in session.keys():
user = parsed_json['user']
owned_by = user['username']
parsed_json = request.get_json()
attribute = parsed_json['attribute']
value = parsed_json['value']
sort_by = parsed_json['sort_by']
order = parsed_json['order']
cursor = db.cursor(pymysql.cursors.DictCursor)
sql_string = "SELECT * from Property WHERE " + attribute + " = '" + value + "', ownedBy = '" + owned_by + "' ORDER BY " + sort_by + " " + order
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print (e)
return
list = cursor.fetchall()
print(list)
for dict in list:
dict['isPublic'] = str(dict['isPublic'])
dict['isCommercial'] = str(dict["isCommercial"])
return_string = json.dumps(list, sort_keys=True, indent=4, separators=(',', ': '))
return return_string
else:
return "not logged in"
def login():
parsed_json = request.get_json()
email = parsed_json['email']
password = User.hash_password(parsed_json['password'])
cursor = db.cursor(pymysql.cursors.DictCursor)
sql_string = "SELECT username, password, userType FROM User WHERE email = '" \
+ email + "' AND password = '******'"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
cursor.close()
return "Invalid"
user = cursor.fetchone()
cursor.close()
if user is not None:
print(user)
if 'user' in session.keys():
return "Already Logged In"
user['email'] = email
session['user'] = user
print(session.keys())
session.modified = True
session.permanent = True
return user['userType']
else:
return "Invalid Login"
def get_user_visits():
if 'user' in session.keys():
user = session['user']
email = user['email']
cursor = db.cursor(pymysql.cursors.DictCursor)
sql_string = "SELECT propertyName, date, rating FROM Visits JOIN Property" \
+ " ON Visits.id = Property.id WHERE Visits.email = '" \
+ email + "';"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print(e)
return
visits = cursor.fetchall()
list = []
for visit in visits:
print(visit)
dict = {
"propertyName": visit['propertyName'],
"date": str(visit['date']),
"rating": visit['rating']
}
list.append(dict)
return_string = json.dumps(list,
sort_keys=True,
indent=4,
separators=(',', ': '))
return return_string
else:
return "not logged in"
def update_multiple_attributes():
if 'user' in session.keys():
parsed_json = request.get_json()
print (parsed_json)
cursor = db.cursor()
sql_string = "UPDATE Property SET "
first = True
for k, v in parsed_json.items():
if k != 'id':
if not first:
sql_string += ", "
else:
first = False
if k == 'isPublic' or k == 'isCommercial':
sql_string += k + " = " + v
else:
sql_string += k + " = '" + v + "'"
sql_string += " WHERE id = " + parsed_json['id'] + ";"
print (sql_string)
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print (e)
return
return "Update successful"
else:
return "not logged in"
def get_property_by_id():
parsed_json = request.get_json()
id = parsed_json['id']
cursor = db.cursor(pymysql.cursors.DictCursor)
sql_string = "SELECT * from Property where id = '" + id + "'"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print (e)
return
property = cursor.fetchone()
print(property)
dict_json = []
property_dict = {
"propertyName": property['propertyName'],
"id": property['id'],
"isPublic": property['isPublic'].decode(),
"size": property['size'],
"isCommercial": property['isCommercial'].decode(),
"streetAddress": property['streetAddress'],
"city": property['city'],
"zip": property['zip'],
"propertyType": property['propertyType'],
"isApproved": property['approvedBy']
}
dict_json.append(property_dict)
return_string = json.dumps(dict_json, sort_keys=True, indent=4, separators=(',', ': '))
return return_string
def get_detailed_property():
if 'user' in session.keys():
parsed_json = request.get_json()
print(parsed_json)
id = parsed_json['id']
email = parsed_json['email']
dict = {}
cursor = db.cursor(pymysql.cursors.DictCursor)
sql_string = "SELECT propertyName, streetAddress, city, zip, size, propertyType, isPublic, CASE WHEN approvedBy IS NULL THEN 'False' ELSE 'True' END AS isApproved, isCommercial, Property.id, IFNULL(numVisits, 0) AS visits, ratings" \
+ " FROM Property LEFT JOIN " \
+ "(SELECT id, COUNT(*) AS numVisits, AVG(rating) AS ratings " \
+ "FROM Visits Group By id) as temp ON Property.id = temp.id " \
+ "WHERE Property.id = " + id + ";"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print (e)
return
property = cursor.fetchone()
dict.update(property)
sql_string = "SELECT username, email FROM User WHERE email = '" + email + "';"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print (e)
return
property = cursor.fetchone()
dict.update(property)
sql_string = "SELECT produceName, produceType FROM FarmGrows NATURAL JOIN Produce WHERE id = " + id + ";"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print (e)
return
produces = cursor.fetchall()
produce_dict = {}
for produce in produces:
if produce['produceType'] not in produce_dict.keys():
produce_dict[produce['produceType']] = [produce['produceName']]
else:
produce_dict[produce['produceType']].append(produce['produceName'])
dict.update(produce_dict)
print (dict)
dict['isPublic'] = str(dict['isPublic'])
dict['isCommercial'] = str(dict['isCommercial'])
dict['ratings'] = str(dict['ratings'])
return json.dumps(dict, sort_keys=True, indent=4, separators=(',', ': '))
else:
return "not logged in"
def remove_user():
parsed_json = request.get_json()
email = parsed_json['email']
cursor = db.cursor()
sql_string = "DELETE FROM User WHERE email = '" + email + "';"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
cursor.close()
return "Invalid"
cursor.close()
return "removed"
def delete_property():
if 'user' in session.keys():
json = request.get_json()
id = json['id']
cursor = db.cursor()
sql_string = "DELETE from Property WHERE id = '" + id + "'"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print (e)
return
return "Deleted property"
else:
return "not logged in"
def get_all_users():
if 'user' in session.keys():
cursor = db.cursor(pymysql.cursors.DictCursor)
sql_string = "SELECT User.username, email, IFNULL(numProperties, 0) AS propertyNum FROM User LEFT JOIN (SELECT ownedBy, COUNT(id) AS numProperties FROM Property GROUP BY ownedBy) AS temp ON User.username = temp.ownedBy WHERE userType = 'owner';"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
cursor.close()
return "Invalid"
return json.dumps(cursor.fetchall(),
sort_keys=True,
indent=4,
separators=(',', ': '))
else:
return "not logged in"
def delete_produce():
if 'user' in session.keys():
parsed_json = request.get_json()
name = parsed_json['name']
cursor = db.cursor()
sql_string = "DELETE from Produce WHERE produceName = '" + name + "'"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print(e)
return
return "Deleted produce"
else:
return "not logged in"
def approve_produce():
if 'user' in session.keys():
parsed_json = request.get_json()
name = parsed_json['name']
cursor = db.cursor()
sql_string = "UPDATE Produce SET itemApproved = 1 where produceName = '" + name + "'"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print(e)
return
return "approved"
else:
return "not logged in"
def get_user_properties():
if 'user' in session.keys():
user = session['user']
username = user['username']
parsed_json = request.get_json()
sort_by = parsed_json['sort_by']
order = parsed_json['order']
cursor = db.cursor(pymysql.cursors.DictCursor)
sql_string = "SELECT propertyName, streetAddress, city, zip, size, propertyType, isPublic, CASE WHEN approvedBy IS NULL THEN 'False' ELSE 'True' END AS isApproved, isCommercial, Property.id, IFNULL(numVisits, 0) AS visits, ratings" \
+ " FROM Property LEFT JOIN " \
+ "(SELECT id, COUNT(*) AS numVisits, AVG(rating) AS ratings " \
+ "FROM Visits Group By id) as temp ON Property.id = temp.id " \
+ "WHERE ownedBy = '" + username + "' "\
+ "ORDER BY " + sort_by + " " + order
print (sql_string)
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print (e)
return
properties = cursor.fetchall()
dict_json = []
for property in properties:
print (property)
property_dict = {
"propertyName": property['propertyName'],
"id": property['id'],
"isPublic": property['isPublic'].decode(),
"size": property['size'],
"isCommercial": property['isCommercial'].decode(),
"streetAddress": property['streetAddress'],
"city": property['city'],
"zip": property['zip'],
"propertyType": property['propertyType'],
"isApproved": property['isApproved'],
"visits": property['visits'],
"ratings": str(property['ratings'])
}
dict_json.append(property_dict)
return_string = json.dumps(dict_json, sort_keys=True, indent=4, separators=(',', ': '))
return return_string
else:
return "not logged in"
def get_approved_produce():
if 'user' in session.keys():
cursor = db.cursor(pymysql.cursors.DictCursor)
sql_string = "SELECT produceName, produceType from Produce WHERE itemApproved = 1"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print(e)
return
return_string = json.dumps(cursor.fetchall(),
sort_keys=True,
indent=4,
separators=(',', ': '))
return return_string
else:
return "not logged in"
def get_orchard_crops():
if 'user' in session.keys():
cursor = db.cursor(pymysql.cursors.DictCursor)
sql_string = "SELECT produceName FROM Produce WHERE produceType = 'fruit' OR produceType = 'nut' ORDER BY produceName ASC;"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print(e)
return
return_string = json.dumps(cursor.fetchall(),
sort_keys=True,
indent=4,
separators=(',', ': '))
return return_string
else:
return "not logged in"
def add_produce():
if 'user' in session.keys():
parsed_json = request.get_json()
name = parsed_json['name']
type = parsed_json['type']
cursor = db.cursor()
sql_string = "INSERT INTO Produce (produceName, produceType) VALUES ('" \
+ name + "', '" + type + "')"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print(e)
return
return "Added produce"
else:
return "not logged in"
def update_property():
if 'user' in session.keys():
parsed_json = request.get_json()
attribute = parsed_json['attribute']
value = parsed_json['value']
id = parsed_json['id']
cursor = db.cursor()
sql_string = "UPDATE Property SET " + attribute + " = '" + value + "' WHERE id = '" + id + "';"
print (sql_string)
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print (e)
return
return "Update successful"
else:
return "not logged in"
def add_relation():
if 'user' in session.keys():
parsed_json = request.get_json()
id = parsed_json['id']
produceName = parsed_json['produceName']
cursor = db.cursor()
sql_string = "INSERT INTO FarmGrows (id, produceName) VALUES (" \
+ id + ", '" + produceName + "')"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print(e)
return
return "Added produce"
else:
return "not logged in"
def remove_visit():
if 'user' in session.keys():
user = session['user']
parsed_json = request.get_json()
username = user['username']
id = parsed_json['id']
cursor = db.cursor()
sql_string = "DELETE FROM Visits WHERE id = '" + id + "' AND username = '******';"
print(sql_string)
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print(e)
return
return "deleted"
else:
return "not logged in"
def add_property():
if 'user' in session.keys():
user = session['user']
username = user['username']
parsed_json = request.get_json()
propertyName = parsed_json['propertyName']
isPublic = parsed_json['isPublic']
size = parsed_json['size']
isCommercial = parsed_json['isCommercial']
streetAddress = parsed_json['streetAddress']
city = parsed_json['city']
zip = parsed_json['zip']
propertyType = parsed_json['propertyType']
ownedBy = username
print ("helloooo");
cursor = db.cursor(pymysql.cursors.DictCursor)
sql_string = "SELECT MAX(id) FROM Property"
cursor.execute(sql_string)
max_id = cursor.fetchone()
if max_id is None or max_id['MAX(id)'] is None:
id = "0"
else:
id = str(int(max_id['MAX(id)']) + 1)
sql_string = "INSERT INTO Property (propertyName, id, isPublic, size, isCommercial, streetAddress, city, zip, propertyType, ownedBy) VALUES ('" \
+ propertyName + "', '" + id + "', " + isPublic + ", '" \
+ size + "', " + isCommercial + ", '" + streetAddress + "', '" \
+ city + "', '" + zip + "', '" + propertyType + "', '" + ownedBy + "');"
#console.log(sql_string);
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print (e)
return
return "Added property"
else:
return "not logged in"
def register():
json = request.get_json()
username = json['username']
email = json['email']
password = User.hash_password(json['password'])
type = json['type']
cursor = db.cursor()
sql_string = "INSERT INTO User (email, username, password, userType) VALUES ('" \
+ email + "', '" \
+ username + "', '" \
+ password + "', '" \
+ type + "');"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
cursor.close()
return "Invalid"
cursor.close()
return "Registered"
def get_new_crop_drop_down():
if 'user' in session.keys():
parsed_json = request.get_json()
id = parsed_json['id']
produce_type = parsed_json['produce_type']
cursor = db.cursor(pymysql.cursors.DictCursor)
sql_string = "SELECT produceName FROM FarmGrows NATURAL JOIN Produce WHERE id = '" + id + "' AND produceType = '" + produce_type + "';"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print(e)
return
return_string = json.dumps(cursor.fetchall(),
sort_keys=True,
indent=4,
separators=(',', ': '))
return return_string
else:
return "not logged in"
def add_visit():
if 'user' in session.keys():
parsed_json = request.get_json()
user = session['user']
email = user['email']
date = datetime.now()
rating = parsed_json['rating']
id = parsed_json['id']
cursor = db.cursor()
sql_string = "INSERT INTO Visits (email, date, rating, id) VALUES ('" \
+ email + "', '" \
+ str(date) + "', " \
+ rating + ", " \
+ id + ");"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print(e)
return
return "successfully added visit"
else:
return "not logged in"
def get_unconfirmed_properties():
if 'user' in session.keys():
cursor = db.cursor(pymysql.cursors.DictCursor)
sql_string = "SELECT * " \
+ "FROM Property JOIN User ON Property.ownedBy = User.email " \
+ "WHERE approvedBy IS NULL " \
+ "ORDER BY propertyName ASC;"
try:
cursor.execute(sql_string)
except (pymysql.Error, pymysql.Warning) as e:
print (e)
return
list = cursor.fetchall()
print (list)
for dict in list:
print (dict)
dict['isPublic'] = str(dict['isPublic'])
dict['isCommercial'] = str(dict['isCommercial'])
return json.dumps(list, sort_keys=True, indent=4, separators=(',', ': '))
else:
return "not logged in"
