def create_ratingitem(project):
with context(project, 'write') as cntx:
item = request.json
dbid = cntx.db.insert("""INSERT INTO ratingitem (id, name, description, category, creation_author, creation_time, project_id)
VALUES (NULL, %s, %s, %s, %s, NOW(), %s)""",
[escape(item['name']), escape(item['description']), escape(item['category']), cntx.userid, cntx.pid])
return redirect("/api/" +project+ "/ratingitem/" + str(dbid), 201);
def create_ratingitem(project):
with context(project, 'write') as cntx:
item = request.json
dbid = cntx.db.insert("""INSERT INTO ratingitem (id, name, description, category, creation_author, creation_time, project_id)
VALUES (NULL, %s, %s, %s, %s, NOW(), %s)""",
[escape(item['name']), escape(item['description']), escape(item['category']), cntx.userid, cntx.pid])
return redirect("/api/" +project+ "/ratingitem/" + str(dbid), 201);
def update_ratingitem(project, no):
with context(project, 'write') as cntx:
item = request.json
dbid = cntx.db.execute("""UPDATE ratingitem SET
name=%s, description=%s, category=%s, creation_author=%s, creation_time="""+cntx.db.time_now()+"""
WHERE id = %s AND project_id = %s""",
[escape(item['name']), escape(item['description']), escape(item['category']), cntx.userid, no, cntx.pid])
return '{"status": "ok"}'
def update_ratingitem(project, no):
with context(project, 'write') as cntx:
item = request.json
dbid = cntx.db.execute("""UPDATE ratingitem SET
name=%s, description=%s, category=%s, creation_author=%s, creation_time="""+cntx.db.time_now()+"""
WHERE id = %s AND project_id = %s""",
[escape(item['name']), escape(item['description']), escape(item['category']), cntx.userid, no, cntx.pid])
return '{"status": "ok"}'
def get_fullratingitems(project):
with context(project, 'read') as cntx:
category = request.query.get('category')
if category:
dbitems = cntx.db.fetchdicts("SELECT * FROM ratingitem WHERE category = %s AND project_id = %s ORDER BY name", [category, cntx.pid])
else:
dbitems = cntx.db.fetchdicts("SELECT * FROM ratingitem WHERE project_id = %s ORDER BY name", [cntx.pid])
result = []
for item in dbitems:
result.append(get_ratingitem_data(cntx, project, item['id']))
return jdump(result);
def create_advice(project):
with context(project, 'write') as cntx:
advice = request.json
dbid = cntx.db.execute("""DELETE FROM advice WHERE user_id = %s and ratingitem_id = %s AND project_id = %s""",
[cntx.userid, advice['ratingitem_id'], cntx.pid]);
dbid = cntx.db.execute("""INSERT INTO advice (user_id, ratingitem_id, advice, creation_time, project_id)
VALUES (%s, %s, %s, NOW(), %s)""",
[cntx.userid, escape(str(advice['ratingitem_id'])), escape(advice['advice']), cntx.pid]);
return redirect("/api/"+project+"/advice/" + str(cntx.userid) + "/"+ str(advice['ratingitem_id']), 201);
def get_fullratingitems(project):
with context(project, 'read') as cntx:
category = request.query.get('category')
if category:
dbitems = cntx.db.fetchdicts("SELECT * FROM ratingitem WHERE category = %s AND project_id = %s ORDER BY name", [category, cntx.pid])
else:
dbitems = cntx.db.fetchdicts("SELECT * FROM ratingitem WHERE project_id = %s ORDER BY name", [cntx.pid])
result = []
for item in dbitems:
result.append(get_ratingitem_data(cntx, project, item['id']))
return jdump(result);
def create_advice(project):
with context(project, 'write') as cntx:
advice = request.json
dbid = cntx.db.execute("""DELETE FROM advice WHERE user_id = %s and ratingitem_id = %s AND project_id = %s""",
[cntx.userid, advice['ratingitem_id'], cntx.pid]);
dbid = cntx.db.execute("""INSERT INTO advice (user_id, ratingitem_id, advice, creation_time, project_id)
VALUES (%s, %s, %s, NOW(), %s)""",
[cntx.userid, escape(str(advice['ratingitem_id'])), escape(advice['advice']), cntx.pid]);
return redirect("/api/"+project+"/advice/" + str(cntx.userid) + "/"+ str(advice['ratingitem_id']), 201);
def get_timeline(project):
offset = 0
limit = 300
with context(project, 'read') as cntx:
return jdump(cntx.db.fetchdicts("""SELECT user.displayname as user, innerSelect.* FROM user, ((SELECT advice.user_id as user_id, name as targetLabel, 'advice' as action, advice.advice as value, advice.creation_time as time
FROM ratingitem, advice WHERE advice.ratingitem_id = ratingitem.id AND advice.project_id = %s)
UNION
(SELECT creation_author as user_id, name as targetLabel, 'new' as action,
'created' as value, creation_time as time FROM ratingitem WHERE ratingitem.project_id = %s)) innerSelect
WHERE innerSelect.user_id = user.id
ORDER BY time DESC
LIMIT %s, %s""",
[cntx.pid, cntx.pid, offset, limit]));
def get_timeline(project):
offset = 0
limit = 300
with context(project, 'read') as cntx:
return jdump(cntx.db.fetchdicts("""SELECT user.displayname as user, innerSelect.* FROM user, ((SELECT advice.user_id as user_id, name as targetLabel, 'advice' as action, advice.advice as value, advice.creation_time as time
FROM ratingitem, advice WHERE advice.ratingitem_id = ratingitem.id AND advice.project_id = %s)
UNION
(SELECT creation_author as user_id, name as targetLabel, 'new' as action,
'created' as value, creation_time as time FROM ratingitem WHERE ratingitem.project_id = %s)) innerSelect
WHERE innerSelect.user_id = user.id
ORDER BY time DESC
LIMIT %s, %s""",
[cntx.pid, cntx.pid, offset, limit]));
def get_categories(project):
with context(project, 'read') as cntx:
return jdump(cntx.db.fetchdicts("SELECT * FROM category ORDER BY orderindex"))
def delete_ratingitem(project, no):
with context(project, 'delete') as cntx:
cntx.db.execute("DELETE FROM ratingitem WHERE id = %s AND project_id = %s", [no, cntx.pid]);
cntx.db.execute("DELETE FROM advice WHERE ratingitem_id = %s AND project_id = %s", [no, cntx.pid]);
return '{"status": "ok"}'
def get_ratingitem(project, no):
with context(project, 'read') as cntx:
return jdump(get_ratingitem_data(cntx, project, no));
def get_ratingitems(project):
with context(project, 'read') as cntx:
item_self_link = cntx.db.concat("'" + "/api/"+ project + "/ratingitem/'", "id");
return jdump(cntx.db.fetchdicts("SELECT *, "+item_self_link+" as self FROM ratingitem WHERE project_id = %s ORDER BY name", [cntx.pid]))
def get_advices_bv_user(project):
with context(project, 'read') as cntx:
return jdump(cntx.db.fetchdicts("SELECT * FROM advice WHERE user_id = %s AND project_id = %s""",
[cntx.userid, cntx.pid]));
def get_advices_bv_user(project):
with context(project, 'read') as cntx:
return jdump(cntx.db.fetchdicts("SELECT * FROM advice WHERE user_id = %s AND project_id = %s""",
[cntx.userid, cntx.pid]));
def get_categories(project):
with context(project, 'read') as cntx:
return jdump(cntx.db.fetchdicts("SELECT * FROM category ORDER BY orderindex"))
def get_ratingitems(project):
with context(project, 'read') as cntx:
item_self_link = cntx.db.concat("'" + "/api/"+ project + "/ratingitem/'", "id");
return jdump(cntx.db.fetchdicts("SELECT *, "+item_self_link+" as self FROM ratingitem WHERE project_id = %s ORDER BY name", [cntx.pid]))
def delete_ratingitem(project, no):
with context(project, 'delete') as cntx:
cntx.db.execute("DELETE FROM ratingitem WHERE id = %s AND project_id = %s", [no, cntx.pid]);
cntx.db.execute("DELETE FROM advice WHERE ratingitem_id = %s AND project_id = %s", [no, cntx.pid]);
return '{"status": "ok"}'
def get_ratingitem(project, no):
with context(project, 'read') as cntx:
return jdump(get_ratingitem_data(cntx, project, no));
def get_advice(project, user, ratingitem_id):
with context(project, 'read') as cntx:
return jdump(cntx.db.fetchdict("SELECT * FROM advice WHERE user_id = %s and ratingitem_id = %s AND project_id = %s""",
[user, ratingitem_id, cntx.pid]));
def get_advice(project, user, ratingitem_id):
with context(project, 'read') as cntx:
return jdump(cntx.db.fetchdict("SELECT * FROM advice WHERE user_id = %s and ratingitem_id = %s AND project_id = %s""",
[user, ratingitem_id, cntx.pid]));
