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 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_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_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]));