def get_post_tribe(userid):
db_connection = conn.create_connection()
db_cursor = db_connection.cursor()
#sql = 'select * from post where userid=%s', (userid,);
db_cursor.execute(
'select * from post where userid in (select userid from users where tribe_id in (select tribe_id from users where userid= %s))',
(userid, ))
rv = db_cursor.fetchall()
db_cursor.close()
db_connection.close()
if (rv):
payload = []
content = {}
for result in rv:
content = {
'postid': result[0],
'posttext': result[2],
'imageurl': result[3],
'timestamp': result[4]
}
payload.append(content)
content = {}
result_json = jsonify(payload)
else:
result_json = "don't have post yet"
return result_json
def add_like(postid, userid, timestamp):
db_connection = conn.create_connection()
db_cursor = db_connection.cursor()
db_cursor.execute('select * from likes where userid = %s and postid= %s',
(userid, postid))
result = db_cursor.fetchone()
print(result)
# status 0 means likes, 1 means disliked
if (result):
if (result[3] == 0):
db_cursor.execute(
'update likes set active_likes = %s where userid = %s and postid= %s',
(1, userid, postid))
db_connection.commit()
else:
db_cursor.execute(
'update likes set active_likes = %s where userid = %s and postid= %s',
(0, userid, postid))
db_connection.commit()
msg = 'like updated'
else:
sql = 'INSERT INTO likes (postid, userid,active_likes) VALUES(%s,%s,%s)'
val = (postid, userid, 0)
db_cursor.execute(sql, val)
db_connection.commit()
msg = 'like added'
return msg
def login(username, password):
# Creating Db connection and cursor
db_connection = conn.create_connection()
db_cursor = db_connection.cursor()
#db_cursor.execute('SELECT * FROM users WHERE userid = %s AND password = %s', (username, password))
db_cursor.execute('SELECT users.userid, users.name, users.email,users.contact,users.address, tribe.tribename, mentor.mentorname FROM users INNER JOIN tribe ON users.tribe_id=tribe.tribeid INNER JOIN mentor ON users.mentor_id = mentor.mentorid where users.userid = %s and users.password = %s', (username, password))
#db_cursor.execute('SELECT users.userid, users.name, users.email,users.contact,users.address, tribe.tribename, mentor.mentorname FROM users INNER JOIN tribe ON users.tribe_id=tribe.tribeid INNER JOIN mentor ON users.mentor_id = mentor.mentorid where users.userid='shiva123' and users.password='******')
# Fetch one record and return result
account = db_cursor.fetchone() # fetching data
# If account exists in accounts table in out database
if account:
# Create session data, we can access this data in other routes
session['loggedin'] = True
session['userid'] = account[0]
session['name'] = account[1]
# Close Db connection and cursor
db_cursor.close()
db_connection.close()
#print(account)
response = {"userid":account[0],"name":account[1],"email":account[2],"contact":account[3],"address":account[4],"tribename":account[5],"mentorname":account[6]}
else:
# Account doesnt exist or username/password incorrect
response = 'Incorrect username/password!'
return response # returnd data
def add_comment(postid, commenttext, timestamp):
db_connection = conn.create_connection()
db_cursor = db_connection.cursor()
sql = 'INSERT INTO comment (commenttext, timestamp, postid) VALUES(%s,%s,%s)'
val = (commenttext, timestamp, postid)
db_cursor.execute(sql, val)
db_connection.commit()
return "Comment Added"
def add_post(userid, text, timestamp):
db_connection = conn.create_connection()
db_cursor = db_connection.cursor()
sql = 'INSERT INTO post (userid,text,imageurl,timestamp) VALUES(%s,%s,%s,%s)'
val = (userid, text, '', timestamp)
db_cursor.execute(sql, val)
db_connection.commit()
return "Post Added"
def get_course_assignment(coursename):
# Creating Db connection and cursor
db_connection = conn.create_connection()
db_cursor = db_connection.cursor()
db_cursor.execute('select assesment.assesmentid, assesment.assesment_name, assesment.assesment_score, course.coursename from assesment inner join course on course.courseid = assesment.course_id where course.coursename=%s',(coursename,))
# Fetch all record and return result
result = db_cursor.fetchall()
# Close Db connection and cursor
db_cursor.close()
db_connection.close()
return result
def get_course_progress(username):
# Creating Db connection and cursor
db_connection = conn.create_connection()
db_cursor = db_connection.cursor()
db_cursor.execute('SELECT COUNT(assesmentid), course.coursename FROM user_course_assesment INNER JOIN course ON user_course_assesment.courseid=course.courseid where userid = %s and assesment_state = %s GROUP BY user_course_assesment.userid,user_course_assesment.courseid',(username,'finished'))
# Fetch all record and return result
result = db_cursor.fetchall()
# Close Db connection and cursor
db_cursor.close()
db_connection.close()
return result
def register(userid, password, name, email, address, tribe_id, mentor_id):
# Creating Db connection and cursor
db_connection = conn.create_connection()
db_cursor = db_connection.cursor()
sql = 'INSERT INTO users (userid,password,name,email,address,tribe_id,mentor_id) VALUES(%s,%s,%s,%s,%s,%s,%s)'
val = (userid, password, name, email, address, tribe_id, mentor_id)
db_cursor.execute(sql, val)
db_connection.commit()
# Close Db connection and cursor
db_cursor.close()
db_connection.close()
return "Registration Successful"
def get_alllikes(postid):
db_connection = conn.create_connection()
db_cursor = db_connection.cursor()
#sql = 'select * from post where userid=%s', (userid,);
db_cursor.execute(
'select count(likeid) from likes where postid=%s and active_likes=%s group by(postid);',
(postid, 0))
result = db_cursor.fetchone()
if (result):
all_likes = {'alllikes': result[0]}
else:
all_likes = 'post have 0 likes'
db_cursor.close()
db_connection.close()
print(all_likes)
return all_likes
def get_points(username):
# Creating Db connection and cursor
db_connection = conn.create_connection()
db_cursor = db_connection.cursor()
db_cursor.execute('SELECT * FROM points WHERE userid = %s', (username, ))
# Fetch one record and return result
result = db_cursor.fetchone()
# Close Db connection and cursor
db_cursor.close()
db_connection.close()
if (result):
result_json = {
"wisdompoints": result[1],
"connectionpoints": result[2]
}
else:
result_json = "User don't have points"
return result_json
def get_tedencies(username):
# Creating Db connection and cursor
db_connection = conn.create_connection()
db_cursor = db_connection.cursor()
db_cursor.execute('SELECT * FROM tendencies WHERE userid = %s',
(username, ))
# Fetch one record and return result
result = db_cursor.fetchone()
# Close Db connection and cursor
db_cursor.close()
db_connection.close()
if (result):
result_json = {
"tendencie1": result[1],
"tendencie2": result[2],
"tendencie3": result[3]
}
else:
result_json = "User don't have tendencies"
return result_json
def get_all_course():
# Creating Db connection and cursor
db_connection = conn.create_connection()
db_cursor = db_connection.cursor()
db_cursor.execute('SELECT * FROM course')
# Fetch all record and append these into a json and return result
rv = db_cursor.fetchall()
# Close Db connection and cursor
db_cursor.close()
db_connection.close()
if (rv):
payload = []
content = {}
for result in rv:
content = {'courseid': result[0], 'coursename': result[1]}
payload.append(content)
content = {}
result_json = jsonify(payload)
else:
result_json = "don't have any course"
return result_json
def get_commnets(postid):
db_connection = conn.create_connection()
db_cursor = db_connection.cursor()
#sql = 'select * from post where userid=%s', (userid,);
db_cursor.execute('select * from comment where postid = %s', (postid, ))
rv = db_cursor.fetchall()
db_cursor.close()
db_connection.close()
if (rv):
payload = []
content = {}
for result in rv:
content = {
'commentid': result[0],
'commenttext': result[1],
'timestamp': result[2]
}
payload.append(content)
content = {}
result_json = jsonify(payload)
else:
result_json = "no comment on this post"
return result_json
