def get_tasks():
try:
session = cassandra.connect()
session.set_keyspace("todo")
cql = "SELECT * FROM tasks"
r = list(session.execute(cql))
print(len(r))
res_htm = "<h1> Tasks to-do : </h1><ol>"
for i, row in enumerate(r, 0):
res_htm = res_htm + "<li><a href= /get_task_by_id<" + str(
row.id
) + ">" + str(
row.name
) + "</a> " + " - Click Task ID for rest call to get task details (with HATEOAS) - <a href = /rest/get_task_details_by_id<" + str(
row.id
) + ">" + str(
row.id
) + "</a>   <a href = /rest/delete_task_by_id<" + str(
row.id) + ">Delete Task</a></li>"
res_htm = res_htm + "</ol>"
result = res_htm
except Exception as e:
result = Response(
"{ \n \"success\" : \"false\", \n \"code\": \"" +
type(e).__name__ + "\",\n \"message\" : \"" + str(e) + "\"\n}",
status=500,
mimetype='application/json')
return result
def add_todo():
try:
session = cassandra.connect()
session.set_keyspace("todo")
task_name = request.form['task_name']
task_description = request.form['task_description']
task_priority = request.form['task_priority']
task_start = request.form['task_start']
task_end = request.form['task_end']
task_difficulty = request.form['task_difficulty']
task_assignee = request.form['task_assignee']
subtasks_names = request.form['subtasks_names']
subtasks_descriptions = request.form['subtasks_descriptions']
subtasks_difficulties = request.form['subtasks_difficulties']
subtasks_refs = request.form['subtasks_refs']
subtasks_assignees = request.form['subtasks_assignees']
id = str(uuid.uuid4())
task_cql = "INSERT INTO todo.tasks(id,name,description,priority,difficulty,start,end,assignee) VALUES("+id+",'"+nullTostr(task_name.replace("'","''"))+"','"+nullTostr(task_description.replace("'","''"))+"','"+nullTostr(task_priority.replace("'","''"))+"','"+nullTostr(task_difficulty.replace("'","''"))+"','"+nullTostr(task_start.replace("'","''"))+"','"+nullTostr(task_end.replace("'","''"))+"','"+nullTostr(task_assignee.replace("'","''"))+"');"
print(task_cql)
session.execute(task_cql)
for i,s in enumerate(subtasks_names.split('|')):
sub_tasks_cql = "INSERT INTO todo.sub_tasks(id,task_id,name,description,difficulty,ref,assignee) VALUES(UUID()"+",'"+id+"','"+nullTostr(s.replace("'","''"))+"','"+nullTostr(subtasks_descriptions.split('|')[i].replace("'","''"))+"','"+nullTostr(subtasks_difficulties.split('|')[i].replace("'","''"))+"','"+nullTostr(subtasks_refs.split('|')[i].replace("'","''"))+"','"+nullTostr(subtasks_assignees.split('|')[i].replace("'","''"))+"')"
print(s)
session.execute(sub_tasks_cql)
result = "{ \"success\": true,\n \"status\":\"200\",\n \"payload\":[{\"message"+":\"Task '" + task_name + "' added Successfully!\"}"+"],\n\"links\": [ { \n \"rel\" : \"self\",\n \"href\": \""+ "/rest/get_all_tasks\" \n },{ \n \"rel\" : \"self\",\n \"href\": \""+ "/rest/get_task_details_by_id<"+str(id)+"\" \n },{ \n \"rel\" : \"self\",\n \"href\": \""+ "/rest/delete_task_by_id<"+str(id)+"\" \n },{ \n \"rel\" : \"child\",\n \"href\": \""+ "/rest/get_all_sub_tasks\" \n }],\"host-IP\" :\""+request.environ.get('HTTP_X_REAL_IP', request.remote_addr)+"\" }"
return Response (result, status = 200, mimetype = 'application/json')
res="Task '" + task_name + "' added Successfully!"
result = res
except Exception as e:
result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json')
return result
def query5():
hashtag = request.form['query5']
cluster = Cluster(['127.0.0.1'])
session = cluster.connect()
session.set_keyspace("midsem")
query = session.prepare(
""" SELECT * from table_query5 where hashtag = ?""")
open_read = session.execute(query, (hashtag, ))
page = " <style> table, th, td { border: 1px solid black; } </style>"
page = page + "<table style = \"width:100%\"> <tr> <th> HASHTAG </th> <th> DATE </th> <th> TWEET COUNT </th> </tr> "
for row in open_read:
page = page + "<tr> "
page += "<td> %s </td>" % repr(row.hashtag)
page += "<td> %s </td>" % repr(row.date)
page += "<td> %s </td>" % repr(row.tweet_count)
page += "</tr>"
page += "</table>"
return page
def get_user_by_id(id):
try:
id = id.replace("<", "")
id = id.replace(">", "")
print(id)
result = ""
session = cassandra.connect()
session.set_keyspace("todo")
cql = "SELECT * FROM login where id = " + id
r = list(session.execute(cql))
res = "{ \"success\": true,\n\"payload\":["
for i, row in enumerate(r, 0):
if ((i + 1) == len(r)):
res = res + "{\"id\": \"" + str(
row.id) + "\", \"user name\": \"" + str(
row.uname) + "\", \"role\": \"" + str(
row.role) + "\"\n}"
else:
res = res + "{\"id\": \"" + str(
row.id) + "\", \"user name\": \"" + str(
row.uname) + "\", \"role\": \"" + str(
row.role) + "\"},"
res = res + "],\n\"links\": [ { \n \"rel\" : \"self\",\n \"href\": \"" + "/rest/delete_user_by_id< " + str(
row.id) + "\" \n }],\"host-IP\" :\"" + request.environ.get(
'HTTP_X_REAL_IP', request.remote_addr) + "\" }"
result = Response(res, status=200, mimetype='application/json')
except Exception as e:
result = Response(
"{ \n \"success\" : \"false\", \n \"code\": \"" +
type(e).__name__ + "\",\n \"message\" : \"" + str(e) + "\"\n}",
status=500,
mimetype='application/json')
return result
def get_all_users():
try:
session = cassandra.connect()
session.set_keyspace("todo")
cql = "SELECT * FROM login"
r = list(session.execute(cql))
print(len(r))
res_htm = "<h1> List of Application users : </h1><table><tr style ='border: 1px solid black'><th style ='border: 1px solid #dddddd'>User ID</th><th style ='border: 1px solid #dddddd'>Name</th><th style ='border: 1px solid #dddddd'>Role</th><th style ='border: 1px solid #dddddd'>Action</th></tr>"
for i, row in enumerate(r, 0):
res_htm = res_htm + "<tr><td style ='border: 1px solid #dddddd'>" + str(
row.id
) + "</td><td style ='border: 1px solid #dddddd'><a href =/rest/get_user_by_id<" + str(
row.id
) + ">" + str(row.uname).capitalize(
) + "</a></td><td style ='border: 1px solid #dddddd'>" + str(
row.role
).capitalize(
) + "</td><td style ='border: 1px solid #dddddd'><a href=/rest/delete_user_by_id<" + str(
row.id) + ">Delete</a></td></tr>"
res_htm = res_htm + "</ol>"
result = res_htm
except Exception as e:
result = Response(
"{ \n \"success\" : \"false\", \n \"code\": \"" +
type(e).__name__ + "\",\n \"message\" : \"" + str(e) + "\"\n}",
status=500,
mimetype='application/json')
return result
def add_worker():
try:
session = cassandra.connect()
session.set_keyspace("todo")
name = request.form['name']
role = request.form['role']
idcql = "SELECT MAX(id) as id FROM users "
idres = session.execute(idcql)
print(idres[0].id)
id = idres[0].id + 1
print(id)
worker_cql = "INSERT INTO todo.users(id,name,role) VALUES(" + str(
id) + ",'" + nullTostr(
name.replace("'", "''")) + "','" + nullTostr(
role.replace("'", "''")) + "');"
print(worker_cql)
session.execute(worker_cql)
result = "{ \"success\": true,\n \"status\":\"200\",\n \"payload\":[{\"message" + ":\"Employee '" + name + "' with role '" + role + "' added Successfully!\"}" + "],\n\"links\": [ { \n \"rel\" : \"self\",\n \"href\": \"" + "/rest/get_all_workers\" \n },{ \n \"rel\" : \"self\",\n \"href\": \"" + "/rest/get_worker_by_id<" + str(
id) + "\" \n }],\"host-IP\" :\"" + request.environ.get(
'HTTP_X_REAL_IP', request.remote_addr) + "\" }"
result = Response(result, status=200, mimetype='application/json')
except Exception as e:
result = Response(
"{ \n \"success\" : \"false\", \n \"code\": \"" +
type(e).__name__ + "\",\n \"message\" : \"" + str(e) + "\"\n}",
status=500,
mimetype='application/json')
return result
def delete_task_by_id(id):
try:
id=id.replace("<","")
id=id.replace(">","")
print(id)
session = cassandra.connect()
session.set_keyspace("todo")
namecql = "SELECT * FROM tasks where id = "+id
tnameres = session.execute(namecql)
tname = tnameres[0].name
tcql = "DELETE FROM tasks where id = "+id
print(tcql)
session.execute(tcql)
scql = "SELECT * FROM sub_tasks where task_id='"+id+"' ALLOW FILTERING"
print(scql)
listOfSubTasks = session.execute(scql)
for row in listOfSubTasks:
deleteSubTaskcql = "DELETE FROM sub_tasks where id="+str(row.id)
print(deleteSubTaskcql)
session.execute(deleteSubTaskcql)
result = "{ \"success\": true,\n \"status\":\"200\",\n \"payload\":[{\"message"+":\"Deleted task '"+ tname +"' and corresponding sub-tasks!\"}"+"],\n\"links\": [ { \n \"rel\" : \"child\",\n \"href\": \""+ "/rest/get_all_sub_tasks\" \n },{ \n \"rel\" : \"child\",\n \"href\": \""+ "/rest/get_sub_task_by_id<"+id+"\" \n }],\"host-IP\" :\""+request.environ.get('HTTP_X_REAL_IP', request.remote_addr)+"\" }"
result = Response (result, status = 200, mimetype = 'application/json')
except Exception as e:
result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json')
return result
def cassandra():
cluster = Cluster(['cassandra'])
session = cluster.connect()
session.set_keyspace("people")
cql = "SELECT mylist FROM mylist"
qwe = session.execute(cql)
rows = list(qwe)
row_list = list(rows)
return jsonify(row_list)
def delete_all_tasks():
try:
session = cassandra.connect()
session.set_keyspace("todo")
tcql = "TRUNCATE TABLE tasks"
session.execute(tcql)
scql = "TRUNCATE TABLE sub_tasks"
session.execute(scql)
result = "{ \"success\": true,\n \"status\":\"200\",\n \"payload\":[{\"message"+":\"Deleted all tasks and corresponding sub-tasks successfully!\"}"+"],\n\"links\": [ { \n \"rel\" : \"child\",\n \"href\": \""+ "/dashboard\" \n }],\"host-IP\" :\""+request.environ.get('HTTP_X_REAL_IP', request.remote_addr)+"\" }"
result = Response (result, status = 200, mimetype = 'application/json')
except Exception as e:
result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json')
return result
def signup():
try:
session = cassandra.connect()
session.set_keyspace("todo")
error = None
if request.method == 'POST':
pw_hash = hashing.hash_value(request.form['password'], salt=app.config['SALT'])
cql = "INSERT INTO login (id,pwd,uname,role) VALUES(UUID(),'"+pw_hash+"','"+request.form['username']+"','user')"
print(cql)
r = list (session.execute(cql))
error = "Login Created Successfully!"
return redirect(url_for('login'))
return render_template('signup.html', error=error)
except Exception as e:
result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json')
return result
def get_sub_task_by_id(id):
try:
id = id.replace("<", "")
id = id.replace(">", "")
session = cassandra.connect()
session.set_keyspace("todo")
cql = "SELECT * FROM sub_tasks where id = " + id
r = list(session.execute(cql))
res_htm = "<h1> Sub Task Details : </h1><br><h2>Click on Sub-Task ID for REST call (with HATEOAS implementation) </h2><table><tr style ='border: 1px solid black'><th style ='border: 1px solid #dddddd'>Sub-Task ID</th><th style ='border: 1px solid #dddddd'>Name</th><th style ='border: 1px solid #dddddd'>Description</th><th style ='border: 1px solid #dddddd'>Difficulty</th><th style ='border: 1px solid #dddddd'>Assignee</th><th style ='border: 1px solid #dddddd'>Role</th><th style ='border: 1px solid #dddddd'>References</th><th style ='border: 1px solid #dddddd'>Connected to Task</th></tr>"
for i, row in enumerate(r, 0):
t_cql = "SELECT * FROM tasks where id = " + str(row.task_id)
t = list(session.execute(t_cql))
u_cql = "SELECT * FROM users where id = " + str(row.assignee)
u = list(session.execute(u_cql))
res_htm = res_htm + "<tr><td style ='border: 1px solid #dddddd'><a href = /rest/get_sub_task_details_by_id<" + str(
row.id
) + ">" + str(
row.id
) + "</a></td><td style ='border: 1px solid #dddddd'>" + str(
row.name
).capitalize(
) + "</td><td style ='border: 1px solid #dddddd'>" + str(
row.description
) + "</td><td style ='border: 1px solid #dddddd'>" + str(
row.difficulty
) + "</td><td style ='border: 1px solid #dddddd'>" + str(
u[0].name
).capitalize(
) + "</td><td style ='border: 1px solid #dddddd'>" + str(
u[0].role
).capitalize(
) + "</td><td style ='border: 1px solid #dddddd'><a href = " + str(
row.ref
) + ">" + str(
row.ref
) + "</a></td><td style ='border: 1px solid #dddddd'><a href= /get_task_by_id<" + str(
row.task_id) + ">" + str(
t[0].name).capitalize() + "</a></td></tr>"
res_htm = res_htm + "</table>"
result = res_htm
except Exception as e:
result = Response(
"{ \n \"success\" : \"false\", \n \"code\": \"" +
type(e).__name__ + "\",\n \"message\" : \"" + str(e) + "\"\n}",
status=500,
mimetype='application/json')
return result
def get_all_sub_tasks_rest():
result = ""
try:
session = cassandra.connect()
session.set_keyspace("todo")
cql = "SELECT * FROM sub_tasks"
r = list (session.execute(cql))
res = "{ \"success\": true,\n\"payload\":["
for i,row in enumerate(r,0):
if((i+1)==len(r)):
res = res + "{\"id\": \""+str(row.id)+"\", \"assignee\": \""+str(row.assignee)+"\", \"description\": \""+str(row.description)+"\", \"difficulty\": \""+str(row.difficulty)+"\", \"name\": \""+str(row.name)+"\", \"ref\": \""+str(row.ref)+"\", \"task_id\": \""+str(row.task_id)+"\"}"
else:
res = res + "{\"id\": \""+str(row.id)+"\", \"assignee\": \""+str(row.assignee)+"\", \"description\": \""+str(row.description)+"\", \"difficulty\": \""+str(row.difficulty)+"\", \"name\": \""+str(row.name)+"\", \"ref\": \""+str(row.ref)+"\", \"task_id\": \""+str(row.task_id)+"\"},"
res = res + "],\n\"links\": [ { \n \"rel\" : \"self\",\n \"href\": \""+ "/get_sub_task_by_id< bae50b56-19a8-4ece-92ae-c5eeb2dc81a2\" \n }],\"host-IP\" :\""+request.environ.get('HTTP_X_REAL_IP', request.remote_addr)+"\" }"
result = Response (res, status = 200, mimetype = 'application/json')
except Exception as e:
result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json')
return result
def delete_user_by_id(id):
try:
id=id.replace("<","")
id=id.replace(">","")
print(id)
session = cassandra.connect()
session.set_keyspace("todo")
namecql = "SELECT * FROM login where id = "+id
snameres = session.execute(namecql)
sname = snameres[0].uname
cql = "DELETE FROM login where id = "+id
print(cql)
session.execute(cql)
result = "{ \"success\": true,\n \"status\":\"200\",\n \"payload\":[{\"message\""+":\"Deleted user'"+ sname +"'\"}"+"],\n\"links\": [ { \n \"rel\" : \"self\",\n \"href\": \""+ "/rest/get_all_users\" \n }],\"host-IP\" :\""+request.environ.get('HTTP_X_REAL_IP', request.remote_addr)+"\" }"
result = Response (result, status = 200, mimetype = 'application/json')
except Exception as e:
result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json')
return result
def query1():
author = request.form['query1']
cluster = Cluster(['127.0.0.1'])
session = cluster.connect()
session.set_keyspace("twitter_key_space")
query = session.prepare(""" SELECT * from table_query1 where author = ?""")
open_read = session.execute(query, (author, ))
page = ''
for row in open_read:
page += '<p>%s</p>' % repr(row)
return page
def get_task_details_by_id_rest(id):
id = id.replace("<", "")
id = id.replace(">", "")
result = ""
try:
session = cassandra.connect()
session.set_keyspace("todo")
cql = "SELECT * FROM tasks where id = " + id
r = list(session.execute(cql))
res = "{ \"success\": true,\n\"payload\":["
for i, row in enumerate(r, 0):
if ((i + 1) == len(r)):
res = res + "{\"id\": \"" + str(
row.id) + "\", \"assignee\": \"" + str(
row.assignee) + "\", \"priority\": \"" + str(
row.priority) + "\", \"description\": \"" + str(
row.description
) + "\", \"difficulty\": \"" + str(
row.difficulty) + "\", \"name\": \"" + str(
row.name) + "\", \"start\": \"" + str(
row.start) + "\", \"end\": \"" + str(
row.end) + "\"}"
else:
res = res + "{\"id\": \"" + str(
row.id) + "\", \"assignee\": \"" + str(
row.assignee) + "\", \"priority\": \"" + str(
row.priority) + "\", \"description\": \"" + str(
row.description
) + "\", \"difficulty\": \"" + str(
row.difficulty) + "\", \"name\": \"" + str(
row.name) + "\", \"start\": \"" + str(
row.start) + "\", \"end\": \"" + str(
row.end) + "\"},"
res = res + "],\n\"links\": [ { \n \"rel\" : \"child\",\n \"href\": \"" + "/rest/get_all_sub_tasks\" \n },{ \n \"rel\" : \"child\",\n \"href\": \"" + "/rest/get_sub_task_by_id<" + id + "\" \n },{ \n \"rel\" : \"self\",\n \"href\": \"" + "/rest/delete_task_by_id<" + id + "\" \n }],\"host-IP\" :\"" + request.environ.get(
'HTTP_X_REAL_IP', request.remote_addr) + "\" }"
result = Response(res, status=200, mimetype='application/json')
except Exception as e:
result = Response(
"{ \n \"success\" : \"false\", \n \"code\": \"" +
type(e).__name__ + "\",\n \"message\" : \"" + str(e) + "\"\n}",
status=500,
mimetype='application/json')
return result
def get_task_by_id(id):
try:
id=id.replace("<","")
id=id.replace(">","")
session = cassandra.connect()
session.set_keyspace("todo")
print(id)
cql = "SELECT * FROM sub_tasks where task_id = '"+str(id)+"' ALLOW FILTERING"
r = list (session.execute(cql))
print(len(r))
res_htm = "<h1>Task Details</h1><h2> Sub Tasks to perform : </h2><table><tr style ='border: 1px solid black'><th style ='border: 1px solid #dddddd'>Sequence Number</th><th style ='border: 1px solid #dddddd'>Sub-TaskName</th><th style ='border: 1px solid #dddddd'>Assignee</th><th style ='border: 1px solid #dddddd'>Role</th><th style ='border: 1px solid #dddddd'>Action</th></tr>"
for i,row in enumerate(r,0):
u_cql = "SELECT * FROM users where id = "+str(row.assignee)
u = list (session.execute(u_cql))
res_htm = res_htm +"<tr><td style ='border: 1px solid #dddddd'>"+str(i+1)+"</td><td style ='border: 1px solid #dddddd'><a href =/get_sub_task_by_id<"+str(row.id)+">"+str(row.name)+"</a></td><td style ='border: 1px solid #dddddd'>"+str(u[0].name).capitalize()+"</td><td style ='border: 1px solid #dddddd'>"+str(u[0].role).capitalize()+"</td><td style ='border: 1px solid #dddddd'><a href=\\rest\delete_sub_task_by_id<"+str(row.id)+">Delete</a></td></tr>"
res_htm = res_htm+"</ol>"
result = res_htm
except Exception as e:
result = Response ("{ \n \"success\" : \"false\", \n \"code\": \""+type(e).__name__+"\",\n \"message\" : \""+ str(e)+"\"\n}",status=500,mimetype = 'application/json')
return result
def query3():
hashtag = request.form['query3']
print(hashtag)
cluster = Cluster(['127.0.0.1'])
session = cluster.connect()
session.set_keyspace("twitter_key_space")
query = session.prepare(
""" SELECT * from table_query3 where hashtgs = ?""")
open_read = session.execute(query, (hashtag, ))
page = ''
for row in open_read:
page += '<p>%s</p>' % repr(row)
return page
def query6():
date = request.form['query6']
cluster = Cluster(['127.0.0.1'])
session = cluster.connect()
session.set_keyspace("midsem")
query = session.prepare(""" SELECT * from table_query8 where date = ?""")
open_read = session.execute(query, (date, ))
page = " <style> table, th, td { border: 1px solid black; } </style>"
page = page + "<table style = \"width:50%\"> <tr> <th> HASHTAG </th> <th> DATE </th> </tr> "
for row in open_read:
page = page + "<tr> "
page += "<td> %s </td>" % repr(row.date)
page += "<td> %s </td>" % repr(row.hashtag_mention)
page += "</tr>"
page += "</table>"
return page
def get_chart_data(session, current_time):
session.set_keyspace("cep_analytics")
cql = "SELECT * FROM cep_analytics.smarthome_cep_table LIMIT 10"
r = session.execute(cql)
return r
def render_map():
global session
session = cassandra.connect()
session.set_keyspace("cep_analytics")
return render_template('leaflet_template.html')
