def get_group_members(gid):
conn = mysql.connect()
cursor = conn.cursor()
sql_string = 'select UserId from GroupMembers where GroupId=%s'
cursor.execute(sql_string, (gid))
data = cursor.fetchall()
return userids_to_object(data)
def customer_id_to_object(userid):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute('select U.userid, U.registrationdate, U.username, U.password, U.firstname, U.lastname, u.address, u.city, u.state, u.zipcode, u.telephone, u.email, u.rating from UserData U where U.userid=%s', (userid))
d = cursor.fetchone()
user = User(userid=d[0], registrationdate=d[1], username=d[2], password=d[3], firstname=d[4], lastname=d[5], address=d[6], city=d[7], state=d[8], zipcode=d[9], telephone=d[10], email=d[11], rating=d[12])
return user
def add_user():
try:
_json = request.json
_id = int(_json['id'])
_firstname = _json['first_name']
_lastname = _json['last_name']
_emailaddress = _json['email_address']
_password = _json['password']
# validate the received values
if _firstname and _lastname and _emailaddress and _password and request.method == 'POST':
#do not save password as a plain text
_hashed_password = generate_password_hash(_password)
# save edits
sql = "INSERT INTO users(id, first_name, last_name, email_address, password) VALUES(%s,%s, %s, %s, %s)"
data = (
_id,
_firstname,
_lastname,
_emailaddress,
_hashed_password,
)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify('User added successfully!')
resp.status_code = 200
return resp
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def get_company_name(companyid):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute('select companyname from company where companyid=%s',
(companyid))
data = cursor.fetchone()
return data[0]
def record_transaction_controller(form):
conn = mysql.connect()
cursor = conn.cursor()
#first, check if there are enough units to satisfy the transaction
query = "SELECT Nunits FROM Advertisement WHERE AdId = %s"
cursor.execute(query, (form.adid.data))
data = cursor.fetchone()
nunits = data[0]
if form.quantity.data > nunits:
return False
#now, insert into the sales database
cursor.callproc('insertSales', args=(form.adid.data, form.employeeid.data, form.accountno.data, form.quantity.data))
conn.commit()
#and subtract the units left for the ad
nunits -= form.quantity.data
query2 = "UPDATE Advertisement SET Nunits = %s WHERE AdId = %s"
cursor.execute(query2, (nunits, form.adid.data))
conn.commit()
return True
def employee_search(form):
ssn, firstname, lastname = form.ssn.data, form.firstname.data, form.lastname.data
if ssn == '':
ssn = '%'
if firstname == '':
firstname = '%'
if lastname == '':
lastname = '%'
# query parameters:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
'select E.employeeid, E.startdate, E.ssn, E.firstname, E.lastname, E.address, E.city, E.state, E.zipcode, E.telephone, E.email from Employee E where E.ssn like %s and E.firstname like %s and E.lastname like %s',
(ssn, firstname, lastname))
data = cursor.fetchall()
employees = []
for d in data:
employee = Employee(employeeid=d[0],
startdate=d[1],
ssn=d[2],
firstname=d[3],
lastname=d[4],
address=d[5],
city=d[6],
state=d[7],
zipcode=d[8],
telephone=d[9],
email=d[10])
employees.append(employee)
return employees
def get_ad(adid):
conn = mysql.connect()
cursor = conn.cursor()
query = "select adid, adtype, itemname from advertisement where adid=%s"
cursor.execute(query, (adid))
data = cursor.fetchone()
return Advertisement(data[0], adtype=data[1], itemname=data[2])
def post_on_page(pageid, userid, content):
conn = mysql.connect()
cursor = conn.cursor()
sql_string = "INSERT INTO Posts(AuthorId, PageId, Content) VALUES (%s, %s, %s);"
cursor.execute(sql_string, (userid, pageid, content))
conn.commit()
def index():
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("select * from UserData")
data = cursor.fetchall()
print(data)
return render_template('home.html', data=data)
def inbox_controller():
conn = mysql.connect()
cursor = conn.cursor()
query = "SELECT U.username, M.Timesent, M.Subject, M.Content, M.messageid, U.userid FROM Messages M, UserData U WHERE M.Recipient = %s AND M.Sender = U.userid ORDER BY M.TimeSent DESC;"
cursor.execute(query, (session['userid']))
data = cursor.fetchall()
return data
def update_user():
try:
_json = request.json
_id = _json['id']
_name = _json['name']
_email = _json['email']
_password = _json['pwd']
# validate the received values
if _name and _email and _password and _id and request.method == 'POST':
#do not save password as a plain text
_hashed_password = generate_password_hash(_password)
# save edits
sql = "UPDATE tbl_user SET user_name=%s, user_email=%s, user_password=%s WHERE user_id=%s"
data = (
_name,
_email,
_hashed_password,
_id,
)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify('User updated successfully!')
resp.status_code = 200
return resp
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def get_approved_videos():
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
'''SELECT * FROM category c INNER JOIN videos v ON c.id_category = v.id_category WHERE v.approved = 1 ORDER BY c.id_category''')
data = cursor.fetchall()
print (data)
return data
def userids_to_object(userids):
conn = mysql.connect()
cursor = conn.cursor()
users = []
for u in userids:
user = userid_to_object(u)
users.append(user)
return users
def employees_to_object(employeeids):
conn = mysql.connect()
cursor = conn.cursor()
employees = []
for e in employeeids:
employee = employeeid_to_object(e)
employees.append(employee)
return employees
def get_friend_requests():
userid = session['userid']
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute('SELECT F.RequesterId from FriendRequests F WHERE F.RequestedId=%s', (userid))
data = cursor.fetchall()
users = userids_to_object(data)
return users
def get_pageid_user(userid):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("select pageid from pagedata where ownerid=%s", (userid))
data = cursor.fetchone()
if not data:
return None
return data[0]
def get_preferences(userid):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute('select type from preferences where userid=%s', (userid))
data = cursor.fetchall()
preferences = []
for d in data:
preferences.append(d[0])
return preferences
def mailing_list_controller():
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute('select email from userdata')
data = cursor.fetchall()
emails = []
for d in data:
emails.append(d[0])
return emails
def top_customer():
#todo: Fixed query to be correct. Check project phase 2 doc for views necessary to execute query.
conn = mysql.connect()
cursor = conn.cursor()
query = "select c.userid, c.userrevenue from customerrevenue c where not exists( select c1.userid, c1.userrevenue from customerrevenue c1 where c1.userid <> c.userid and c1.userrevenue > c.userrevenue);"
cursor.execute(query)
data = cursor.fetchone()
userid, revenue = data[0], data[1]
user = userid_to_object(userid)
return [user, data]
def request_friend(userid):
requester_userid = session['userid']
if userid != requester_userid:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute('INSERT INTO FriendRequests(RequesterId, RequestedId) VALUES (%s,%s);', (requester_userid, userid) )
conn.commit()
return True
else:
return False # can't add yourself as a friend, already a friend, etc
def get_group(gid):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
'select G.GroupId, G.GroupName, G.GroupType, G.GroupOwner from Groups G where G.GroupId=%s',
(gid))
data = cursor.fetchone()
if not data:
return None
return Group(data[0], data[1], data[2], data[3])
def get_comment_likes(commentid):
conn = mysql.connect()
cursor = conn.cursor()
sql_string = "select UserId from CommentLikes where CommentId=%s"
cursor.execute(sql_string, (commentid))
data = cursor.fetchall()
likes = set()
for d in data:
likes.add(d[0])
return likes
def insert_pending_group_member(gid, userid, awaiting_who):
conn = mysql.connect()
cursor = conn.cursor()
if awaiting_who == 'user':
status = 'waiting on user'
else:
status = 'waiting on group owner'
sql_string = "INSERT INTO PendingGroupRequests VALUES (%s, %s, %s);"
cursor.execute(sql_string, (gid, userid, status))
conn.commit()
def get_videos_by_category(id):
conn = mysql.connect()
cursor = conn.cursor()
if id == '0':
cursor.execute('''SELECT * FROM category c INNER JOIN videos v ON c.id_category = v.id_category WHERE v.approved = 0 ORDER BY c.id_category''')
else:
cursor.execute('''SELECT * FROM category c INNER JOIN videos v ON c.id_category = v.id_category WHERE c.id_category = %s AND v.approved = 0 ORDER BY c.id_category''', id)
data = cursor.fetchall()
# print (data)
return data
def get_page(postid):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"select p.pageid, p.ownerid, p.groupid, p.pagetype from pagedata p, posts t where t.pageid = p.pageid and t.postid=%s",
(postid))
data = cursor.fetchone()
if not data:
return None
return Page(data[0], data[3], ownerid=data[1], groupid=data[2])
def retrieve_customer_groups_controller(userid):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute('select groupid from groupmembers where userid=%s', (userid))
group_ids = cursor.fetchall()
groups = []
for group_id in group_ids:
group = get_group(group_id)
groups.append(group)
return groups
def get_accounts(userid):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute('select * from account where userid = %s', (userid))
data = cursor.fetchall()
accounts = []
for d in data:
account = Account(accountno=d[0], creationdate=d[1], creditcard=d[2], cardtype=d[3], userid=d[4])
accounts.append(account)
return accounts
def in_group(userid, groupid):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
'select userid, groupid from groupmembers where userid=%s and groupid=%s',
(userid, groupid))
data = cursor.fetchone()
print(data, userid, groupid)
if data is None:
return False
return True
def get_comment(commentid):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
'select commentid, authorid, postid, commentdate, content from comments where commentid=%s',
(commentid))
data = cursor.fetchone()
if not data:
return None
return Comment(data[0], data[1], data[2], data[3], data[4],
userid_to_object(data[1]).username)
def edit_customer_controller(form):
conn = mysql.connect()
cursor = conn.cursor()
# check that newly entered username is still unique:
cursor.execute('select * from userdata where username=%s and userid <> %s', (form.username.data, form.userid.data))
data = cursor.fetchall()
if data:
return False # user w/ this username exists
cursor.execute('update userdata set registrationdate=%s, lastname=%s, firstname=%s, address=%s, city=%s, state=%s, zipcode=%s, telephone=%s, email=%s, username=%s, password=%s, rating=%s where userid=%s', (form.registrationdate.data, form.lastname.data, form.firstname.data, form.address.data, form.city.data, form.state.data, form.zipcode.data, form.phonenumber.data, form.email.data, form.username.data, form.password.data, form.rating.data, form.userid.data))
conn.commit()
return True
def save(self): conn = mysql.connect() try: with conn: cursor = conn.cursor() params = [self.email, self.access, self.authenticated, self.name, self.user_id] save_sql = 'UPDATE lead_track_users SET email = %s , access = %s , authenticated = %s , name = %s WHERE id = %s' print save_sql cursor.execute(save_sql,params) conn.close() return True except Exception,e: print e return False
def get(self,email):
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute('SELECT id,email,access,authenticated,name FROM lead_track_users WHERE email = %s',[email,])
user = cursor.fetchone()
conn.close()
self.email = user[1]
self.user_id = user[0]
self.authenticated = user[3]
self.access = user[2]
self.name = user[4]
return True
except:
return False
