def hist_suc_venta_fechas(suc,fecha_ini,fecha_fin):
d_fecha_ini = datetime.strptime(fecha_ini, '%Y-%m-%d').date()
d_fecha_fin = datetime.strptime(fecha_fin, '%Y-%m-%d').date()
from app import mysql
cur_sucursales = mysql.get_db().cursor()
cur_sucursales.execute("""call _get_sucursal_by_name (%s)""", (suc,))
data_sucursales = cur_sucursales.fetchall()[0]
cur_venta = mysql.get_db().cursor()
cur_venta.execute(""" call _get_venta_articulos_por_fechas(%s, %s , %s,%s)""", (data_sucursales[0],
d_fecha_ini,
d_fecha_fin,10))
data = cur_venta.fetchall()
data_json =[]
for i in data:
data_json.append([i[0], i[1],i[2], str(i[3])])
print(data_json)
return jsonify ( {'ventas': data_json ,
'sucursal':data_sucursales[1],
'fecha_ini': fecha_ini,
'fecha_fin': fecha_fin })
def agregar_par():
if request.method == 'POST':
nombre = request.form['parentesco']
id = request.form['id']
print(request.form)
from app import mysql
print('Entre al post')
if id == '0':
print('agregando')
db = mysql.get_db()
cur = db.cursor()
cur.execute(""" call add_parentesco(%s) """, (nombre, ))
db.commit()
flash('El parentesco "' + nombre + '" se ha registrado con exito',
'exito')
#print("agrego")
else:
#edit_parentesco
print('Editandoo')
db = mysql.get_db()
cur = db.cursor()
cur.execute("""call edit_parentesco (%s,%s)""", (
id,
nombre,
))
db.commit()
flash('La informacion se actualizo correctamente', 'exito')
return redirect(url_for('admin.c_parentescos'))
def agrenar_nivel():
if request.method == 'POST':
nombre = request.form['nivel']
id = request.form['id']
from app import mysql
if id == '0':
db = mysql.get_db()
cur = db.cursor()
cur.execute(""" call add_nivel_estudio(%s) """, (nombre, ))
db.commit()
flash('El nivel ' + nombre + " se ha registrado con exito",
'exito')
#print("agrego")
else:
#edit_nivel_estudio
db = mysql.get_db()
cur = db.cursor()
cur.execute("""call edit_nivel_estudio (%s,%s)""", (
id,
nombre,
))
db.commit()
flash('La informacion se actualizo correctamente', 'exito')
return redirect(url_for('admin.c_nivel_estudio'))
def delTest(id):
cur = mysql.get_db().cursor()
cur.execute('DELETE FROM EX_CONTIENE_PRE WHERE EXM_CODIGO = %s', (id, ))
cur.execute('DELETE FROM EXAMEN WHERE EXM_CODIGO = %s', (id, ))
mysql.get_db().commit()
flash(u'El examen se eliminó exitosamente', 'success')
return redirect(url_for("exams"))
def historico_suc_name_rango_fechas_periodo(nombre,fecha_ini,fecha_fin,periodo):
d_fecha_ini = datetime.strptime(fecha_ini, '%Y-%m-%d').date()
d_fecha_fin = datetime.strptime(fecha_fin, '%Y-%m-%d').date()
fecha_actual = d_fecha_ini
fechas_ini = []
for result in perdelta(d_fecha_ini, d_fecha_fin, timedelta(days=int(periodo))):
fechas_ini.append(result)
fechas = []
for i in range(len(fechas_ini) - 1):
fechas.append([fechas_ini[i] + timedelta(days=1), fechas_ini[i + 1]])
for i in fechas:
print(i)
from app import mysql
cur_sucursales = mysql.get_db().cursor()
cur_sucursales.execute ("""call _get_sucursal_by_name (%s)""",(nombre,))
data_sucursales = cur_sucursales.fetchall()[0]
list_historico=[]
for i in fechas:
cur_venta = mysql.get_db().cursor()
cur_venta.execute("""call _get_venta_suc_by_fecha (%s,%s,%s) """ , (data_sucursales[0], i[0],i[1],))
list_historico.append(cur_venta.fetchall()[0])
print(list_historico)
val_max=list_historico[0][2]
val_min=list_historico[0][2]
for i in list_historico:
if i[2]> val_max:
val_max = i[2]
if i[2]< val_min:
val_min = i[2]
print([val_max, val_min])
list_json = []
for i in list_historico:
estado = 0
if i[2] == val_max:
estado =1
if i[2] == val_min:
estado =-1
list_json.append([i[0],i[1], str(i[2]), estado])
return jsonify ( {'historico': list_json,
'ini':fecha_ini,
'fin':fecha_fin,
'ID': data_sucursales[0],
'nombre': nombre} )
def modifyTest(testID):
name = request.form['name']
year = request.form['year']
cur = mysql.get_db().cursor()
cur.execute('UPDATE EXAMEN SET NOMBRE=%s, ANHO=%s WHERE EXM_CODIGO = %s',
(name, year, testID))
mysql.get_db().commit()
flash(u'El examen se modificó exitosamente', 'success')
return redirect(url_for('exams'))
def delQuestion(id, examID):
cur = mysql.get_db().cursor()
cur.execute('DELETE FROM EX_CONTIENE_PRE WHERE CODIGO_PREGUNTA = %s',
(id, ))
cur.execute('DELETE FROM PRES_COMPUESTA_RESP WHERE CODIGO_PREGUNTA = %s',
(id, ))
cur.execute('DELETE FROM PREGUNTA WHERE CODIGO_PREGUNTA = %s', (id, ))
mysql.get_db().commit()
flash(u'La pregunta se eliminó exitosamente', 'success')
return redirect(url_for("modifyquestion", testID=examID))
def examsAdd():
name = request.form['name']
year = float(request.form['year'])
cur = mysql.get_db().cursor()
cur.execute('INSERT INTO EXAMEN (ANHO, NOMBRE) VALUES (%s,%s)',
(year, name))
mysql.get_db().commit()
flash(u'El examen se registró exitosamente', 'success')
cur.execute('SELECT * FROM EXAMEN')
data = cur.fetchall()
return render_template('admin/exams.html', exams=data)
def mud(examID):
if request.method == "POST":
question = request.form['pregunta']
idQ = request.form['id1']
print(idQ)
answer = request.form['respuesta']
idC = request.form['respC']
idI1 = request.form['respI1']
idI2 = request.form['respI2']
idI3 = request.form['respI3']
link = request.form['enlace']
answer1 = request.form['respuesta1']
answer2 = request.form['respuesta2']
answer3 = request.form['respuesta3']
fileSize = request.form['imageSize']
cur = mysql.get_db().cursor()
if request.files:
print(fileSize)
if fileSize != "x_1":
if not allowed_image_filesize(fileSize):
flash("La imagen excede el límite de tamaño", "danger")
return redirect(request.url)
image = request.files["imgInp"]
#if image.filename == "":
#flash("No filename", "danger")
#return redirect(request.url)
if allowed_image(image.filename):
image_string = base64.b64encode(image.read())
cur.execute(
'UPDATE PREGUNTA SET PREGUNTA_IMAGEN = %s WHERE CODIGO_PREGUNTA=%s',
(image_string, idQ))
else:
flash("Imagen no válida", "danger")
return redirect(request.url)
cur.execute(
'UPDATE PREGUNTA SET PREGUNTA_ENLACE=%s, TEXTO_PREGUNTA=%s WHERE CODIGO_PREGUNTA=%s',
(link, question, idQ))
cur.execute(
'UPDATE RESPUESTA SET TEXTO_RESPUESTA=%s WHERE RESP_CODIGO=%s',
(answer, idC))
cur.execute(
'UPDATE RESPUESTA SET TEXTO_RESPUESTA=%s WHERE RESP_CODIGO=%s',
(answer1, idI1))
cur.execute(
'UPDATE RESPUESTA SET TEXTO_RESPUESTA=%s WHERE RESP_CODIGO=%s',
(answer2, idI2))
cur.execute(
'UPDATE RESPUESTA SET TEXTO_RESPUESTA=%s WHERE RESP_CODIGO=%s',
(answer3, idI3))
mysql.get_db().commit()
flash('Se ha modificado correctamente', 'success')
return redirect(url_for("modifyquestion", testID=examID))
def ventas_x_mes():
fecha_ = datetime.now()
list = []
datetime_str = '2000/01/01 00:00:00'
fecha_ = datetime.strptime(datetime_str, '%Y/%m/%d %H:%M:%S')
list.append([str(fecha_.year) + '/01/01' , str(fecha_.year) + '/01/31' ]) #ene
list.append([str(fecha_.year) + '/02/01', str(fecha_.year) + '/02/28'])#feb
list.append([str(fecha_.year) + '/03/01', str(fecha_.year) + '/03/31'])#mar
"""
list.append([str(fecha_.year) + '/04/01', str(fecha_.year) + '/04/30'])#abrl
list.append([str(fecha_.year) + '/05/01', str(fecha_.year) + '/05/31'])#mayo
list.append([str(fecha_.year) + '/06/01', str(fecha_.year) + '/06/30'])#juni
list.append([str(fecha_.year) + '/07/01', str(fecha_.year) + '/07/31'])#jul
list.append([str(fecha_.year) + '/08/01', str(fecha_.year) + '/08/31'])#ago
list.append([str(fecha_.year) + '/09/01', str(fecha_.year) + '/09/30'])#sep
list.append([str(fecha_.year) + '/10/01', str(fecha_.year) + '/10/31'])#oct
list.append([str(fecha_.year) + '/11/01', str(fecha_.year) + '/11/30'])#nov
list.append([str(fecha_.year) + '/12/01', str(fecha_.year) + '/12/31'])#dic
"""
#call get_all_empresas()
from app import mysql
cur_sucursales = mysql.get_db().cursor()
cur_sucursales.execute ("""call get_all_empresas()""")
sucursales = cur_sucursales.fetchall()
list_historial=[]
for sucursal in sucursales:
his_suc=[]
for i in list:
db = mysql.get_db()
cur_venta = db.cursor()
cur_venta.execute(""" call get_ventas_por_fechas_y_sucursal(%s,%s,%s) ; """, (i[0], i[1], sucursal[0],))
data = cur_venta.fetchall()
c_data = []
for j in data:
c_data.append([sucursal[0],sucursal[1], j[0], j[1],j[2], str(j[3]),j[4]])
his_suc.append(c_data)
print(i)
print(c_data )
list_historial.append(his_suc)
print('exit')
return jsonify ( {'historico': list_historial,'ini':list[0][0], 'fin':list[2][1] } )
def saveuser():
name = request.form['name']
lastname = request.form['lastname']
user = request.form['user']
passw = request.form['pass']
admin = 'ADMIN'
cur = mysql.get_db().cursor()
cur.execute(
'INSERT INTO USUARIO (USU_NOMBRE, USU_APELLIDO, USU_CORREO, USU_CONTRASENHA, USU_TIPO) VALUES (%s, %s, %s, %s, %s)',
(name, lastname, user, passw, admin))
mysql.get_db().commit()
flash(u'El usuario se registró exitosamente', 'success')
return redirect(url_for("modify_user"))
def muser(id):
name = request.form['name']
lastname = request.form['lastname']
user = request.form['user']
passw = request.form['pass']
admin = 'ADMIN'
cur = mysql.get_db().cursor()
cur.execute(
'UPDATE USUARIO SET USU_NOMBRE=%s, USU_APELLIDO=%s, USU_CORREO=%s, USU_CONTRASENHA=%s, USU_TIPO=%s WHERE USU_CODIGO_USUARIO= %s',
(name, lastname, user, passw, admin, id))
mysql.get_db().commit()
flash(u'El usuario se modificó exitosamente', 'success')
return redirect(url_for("modify_user"))
def historico_sucursles_por_fechas_periodo(fecha_ini,fecha_fin,periodo):
d_fecha_ini = datetime.strptime(fecha_ini, '%Y-%m-%d').date()
d_fecha_fin = datetime.strptime(fecha_fin, '%Y-%m-%d').date()
fecha_actual = d_fecha_ini
fechas_ini = []
""""
while fecha_actual < d_fecha_fin:
fecha_temp = fecha_actual+ timedelta(days=int(periodo))
fechas.append([fecha_actual , fecha_temp])
fecha_actual = fecha_temp+ timedelta(days=1)
"""
for result in perdelta(d_fecha_ini,d_fecha_fin, timedelta(days=int(periodo))):
fechas_ini.append(result)
fechas=[]
for i in range(len(fechas_ini)-1):
fechas.append([fechas_ini[i]+timedelta(days=1), fechas_ini[i+1]])
for i in fechas:
print (i)
from app import mysql
cur_sucursales = mysql.get_db().cursor()
cur_sucursales.execute("call get_all_empresas(); ")
data = cur_sucursales.fetchall()
list_sucursales = []
for i in data:
list_sucursales.append([i , []])
for i in fechas:
cur_historial = mysql.get_db().cursor()
cur_historial.execute("""call get_ventas_por_fechas(%s ,%s );""" , (i[0],i[1],))
data_hist = cur_historial.fetchall()
#print(data_hist)# data_his almaceda los puntos obtenidos en las graficas
for it_data in data_hist:
for it_suc in list_sucursales:
if it_data[0] == it_suc[0][0]:
it_suc[1].append([ str(it_data[2]), i[1]])
return jsonify({'ventas': list_sucursales,
'fecha_ini':fecha_ini,
'fecha_fin':fecha_fin})
def create_user():
try:
content_json = request.json
# print content_json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
#if content_json.setdefault('isAnonymous'):
# content_json['name'] = ''
# content_json['username'] = ''
# content_json['about'] = ''
else:
if 'username' not in content_json or 'about' not in content_json or 'name' not in content_json \
or 'email' not in content_json or 'isAnonymous' not in content_json:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor()
try:
cursor.execute(
"""INSERT INTO `users` (`email`, `username`, `name`, `about`, `isAnonymous`)
VALUES (%s, %s, %s, %s, %s);""",
(
content_json['email'],
content_json['username'],
content_json['name'],
content_json['about'],
content_json['isAnonymous']
)
)
except MySQLdb.Error:
return jsonify({'code': 5, 'response': "Incorrect request: user is already exist"})
user_id = cursor.lastrowid
db.commit()
content_json.update({'id': user_id})
return jsonify({'code': 0, 'response': content_json})
def create_forum():
try:
content_json = request.json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'name' not in content_json or 'short_name' not in content_json or 'user' not in content_json:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor()
try:
cursor.execute(
"""INSERT INTO `forums` (`name`, `short_name`, `user`)
VALUES (%s, %s, %s);""",
(
content_json['name'],
content_json['short_name'],
content_json['user']
)
)
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request: name and short_name must be unique"})
forum_id = cursor.lastrowid
db.commit()
content_json.update({'id': forum_id})
return jsonify({'code': 0, 'response': content_json})
def delete_follow():
try:
content_json = request.json
# print content_json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'follower' not in content_json or 'followee' not in content_json:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(
"""DELETE FROM `followers`
WHERE `follower` = %s and `followee` = %s;""",
(
content_json['follower'],
content_json['followee']
)
)
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
db.commit()
user = functions.user_details(cursor, content_json['follower'])
return jsonify({'code': 0, 'response': user})
def ventas_fechas(fecha_ini,fecha_fin):
print('inicia')
from app import mysql
cur = mysql.get_db().cursor()
cur.execute(""" call get_ventas_por_fechas(%s ,%s ) ; """, (fecha_ini, fecha_fin,))
data = cur.fetchall()
list = []
for i in data:
list.append(i[2])
val_max = max(list)
val_min = min(list)
c_data = []
for i in data:
is_max = 0
is_min = 0
if i[2] == val_max:
is_max = 1
if i[2] == val_min:
is_min = 1
c_data.append([i[0], i[1], str(i[2]), is_max, is_min])
print(fecha_ini)
print(fecha_fin)
return jsonify ( {'ventas':c_data,
'Fecha_ini': fecha_ini,
'Fecha_fin': fecha_fin} )
def create_thread():
try:
content_json = request.json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'forum' not in content_json or 'title' not in content_json or 'isClosed' not in content_json \
or 'user' not in content_json or 'date' not in content_json or 'message' not in content_json \
or 'slug' not in content_json or 'isDeleted' not in content_json:
return jsonify({
'code': 3,
'response': "Incorrect request: some data missing"
})
db = mysql.get_db()
cursor = db.cursor()
try:
cursor.execute(
"""INSERT INTO `threads` (`forum`, `title`, `isClosed`, `user`, `date`,`message`, `slug`, `isDeleted`)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s);""",
(content_json['forum'], content_json['title'],
content_json['isClosed'], content_json['user'],
content_json['date'], content_json['message'],
content_json['slug'], content_json['isDeleted']))
thread_id = cursor.lastrowid
except MySQLdb.Error:
return jsonify({
'code': 3,
'response': "Incorrect request: user is already exist"
})
db.commit()
content_json.update({'id': thread_id})
return jsonify({'code': 0, 'response': content_json})
def community_(category):
connection = mysql.get_db()
cursor = connection.cursor()
page = request.args.get('page', type=int, default=1)
category = ",".join([(word.capitalize() if word != 'and' else word)
for word in category.split('-')])
res_len = cursor.execute(
"SELECT cid, name, uid FROM Community WHERE (cid) IN (SELECT cid FROM CommunityCategories WHERE categoryName='{}')"
.format(category))
start_row = MAX_PER_PAGE * (page - 1)
end_row = start_row + MAX_PER_PAGE if (
start_row + MAX_PER_PAGE < res_len) else res_len
communities = [
dict(cid=row[0], name=row[1].replace('/', '\''), uid=row[2])
for row in cursor.fetchall()[start_row:end_row]
]
cursor.close()
pagination = Pagination(page=page,
total=res_len,
per_page=MAX_PER_PAGE,
css_framework='bootstrap3')
return render_template('communities.html',
communities=communities,
pagination=pagination)
def update_user():
try:
content_json = request.json
# print content_json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'user' not in content_json or 'about' not in content_json or 'name' not in content_json:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(
"""UPDATE `users` SET `about` = %s, `name` = %s WHERE `email` = %s;""",
(
content_json['about'],
content_json['name'],
content_json['user']
)
)
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request: user is already exist"})
db.commit()
user = functions.user_details(cursor, content_json['user'])
return jsonify({'code': 0, 'response': user})
def index():
connection = mysql.get_db()
cursor = connection.cursor()
cursor.execute("SELECT title FROM event")
all_events = [tup[0] for tup in cursor.fetchall()]
return render_template('index.html', all_events=all_events)
def get(self, cp):
cur = mysql.get_db().cursor()
#query insert record
cur.execute(
"INSERT INTO records (url, cp, host) VALUES ('/postal',%s,%s)",
(cp, request.remote_addr))
# query consult código postal
query = cur.execute(
"SELECT cp,colonia,municipio,estado,ciudad FROM `location_postal` WHERE cp=%s ",
(cp))
cur.connection.commit()
if query > 0:
#result=cur.fetchone()
#col_names = [i[0] for i in cur.description]
col_names = cur.fetchone()
col_colonias = [i[1] for i in cur]
res = {
"status": "success",
"data": {
"cp": col_names[0],
"colonia": col_colonias,
"municipio": col_names[2],
"estado": col_names[3],
"ciudad": col_names[4]
},
"error": ""
}
return res
else:
return {
"status": "error",
"data": "",
"error": "No se encontraron coincidencias"
}
def sign_up():
connection = mysql.get_db()
cursor = connection.cursor()
form = SignupForm(request.form)
if request.method == "POST":
if form.validate() == False:
return jsonify(errors=True,
firstname_error=form.firstname.errors,
lastname_error=form.lastname.errors,
username_error=form.username.errors,
password_error=form.password.errors,
confirm_password_error=form.confirm_password.errors,
email_error=form.email.errors)
# return render_template('signup.html', form=form)
else:
password_hash = generate_password_hash(form.password.data)
attr = (form.firstname.data, form.lastname.data, form.email.data,
form.username.data, password_hash)
cursor.callproc('CreateUser',
(attr[0], attr[1], attr[2], attr[3], attr[4]))
connection.commit()
session['username'] = form.username.data
return jsonify(errors=False)
# return("thank you for signing up!")
elif request.method == 'GET':
return render_template('signup.html', form=form)
def update_post():
try:
content_json = request.json
# print content_json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'post' not in content_json or 'message' not in content_json:
return jsonify({
'code': 3,
'response': "Incorrect request: some data missing"
})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(
"""UPDATE `posts` SET `message` = %s
WHERE `id` = %s;""", (
content_json['message'],
int(content_json['post']),
))
db.commit()
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
post = functions.post_details(cursor, int(content_json['post']))
return jsonify({'code': 0, 'response': post})
def ventas_anio_actual():
fecha_ = datetime.now()
fecha_ini = str(fecha_.year) +'/01/01'
fecha_fin = str(fecha_.year) +'/12/31'
from app import mysql
cur = mysql.get_db().cursor()
cur.execute(""" call get_ventas_por_fechas(%s ,%s ) ; """, (fecha_ini, fecha_fin,) )
data = cur.fetchall()
list = []
for i in data:
list.append(i[2])
val_max = max(list)
val_min = min(list)
c_data = []
for i in data:
is_max = 0
is_min = 0
if i[2] == val_max:
is_max = 1
if i[2] == val_min:
is_min = 1
c_data.append([i[0], i[1], str(i[2]), is_max, is_min])
return jsonify ( {'historico': c_data,'anio': fecha_.year} )
def restore_post():
try:
content_json = request.json
# print content_json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'post' not in content_json:
return jsonify({
'code': 3,
'response': "Incorrect request: some data missing"
})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(
"""UPDATE `threads` SET `posts` = `posts` + 1
WHERE `id` = (
SELECT `thread`
FROM `posts`
WHERE `id` = %s
);""", (int(content_json['post']), ))
cursor.execute(
"""UPDATE `posts` SET `isDeleted` = FALSE WHERE `id` = %s;""",
(content_json['post'], ))
db.commit()
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
return jsonify({'code': 0, 'response': {'post': content_json['post']}})
def thread_detail():
thread_id = request.args.get('thread', None)
related = request.args.getlist('related')
thread_id = int(thread_id)
if thread_id is ('' or None) and thread_id <= 0:
return jsonify({
'code': 2,
'response': "Incorrect request: some data missing"
})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
thread = functions.thread_details(cursor, thread_id)
if thread is None:
return jsonify({'code': 1, 'response': "Post not found"})
if 'thread' in related:
return jsonify({'code': 3, 'response': "Incorrect request"})
if 'user' in related:
user = functions.user_details(cursor, thread['user'])
thread.update({'user': user})
if 'forum' in related:
forum = functions.forum_details(cursor, thread['forum'])
thread.update({'forum': forum})
return jsonify({'code': 0, 'response': thread})
def restore_thread():
try:
content_json = request.json
# print content_json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'thread' not in content_json or int(content_json['thread']) <= 0:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
count = cursor.execute(
"""UPDATE `posts` SET `isDeleted` = FALSE
WHERE `thread` = %s;""",
(content_json['thread'],)
)
cursor.execute(
"""UPDATE `threads` SET `isDeleted` = FALSE, `posts` = %s
WHERE `id` = %s;""",
(count, content_json['thread'])
)
db.commit()
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
return jsonify({'code': 0, 'response': {'thread': content_json['thread']}})
def settings():
form = interest_form(request.form)
categories = form.categories.data
connection = mysql.get_db()
cursor = connection.cursor()
cursor.execute("SELECT uid FROM User WHERE username = '******'".format(
session['username']))
uid = cursor.fetchall()[0][0]
if request.method == "POST":
cursor.execute(
"DELETE FROM HasInterests WHERE HasInterests.uid = '{}'".format(
uid))
connection.commit()
for category in categories:
cursor.execute(
"INSERT INTO HasInterests(uid, categoryName) VALUES('{}', '{}')"
.format(uid, category))
connection.commit()
cursor.execute(
"SELECT categoryName FROM HasInterests WHERE uid ={}".format(uid))
pre_selected = [tup[0] for tup in cursor.fetchall()]
form.categories.data = pre_selected
return render_template('settings.html', form=form)
def create_thread():
try:
content_json = request.json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'forum' not in content_json or 'title' not in content_json or 'isClosed' not in content_json \
or 'user' not in content_json or 'date' not in content_json or 'message' not in content_json \
or 'slug' not in content_json or 'isDeleted' not in content_json:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor()
try:
cursor.execute(
"""INSERT INTO `threads` (`forum`, `title`, `isClosed`, `user`, `date`,`message`, `slug`, `isDeleted`)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s);""",
(
content_json['forum'],
content_json['title'],
content_json['isClosed'],
content_json['user'],
content_json['date'],
content_json['message'],
content_json['slug'],
content_json['isDeleted']
)
)
thread_id = cursor.lastrowid
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request: user is already exist"})
db.commit()
content_json.update({'id': thread_id})
return jsonify({'code': 0, 'response': content_json})
def update_thread():
try:
content_json = request.json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'message' not in content_json or 'slug' not in content_json or 'thread' not in content_json:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(
"""UPDATE `threads` SET `message` = %s, `slug` = %s
WHERE `id` = %s;""",
(
content_json['message'],
content_json['slug'],
int(content_json['thread'])
)
)
db.commit()
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
thread = functions.thread_details(cursor, int(content_json['thread']))
return jsonify({'code': 0, 'response': {'thread': thread}})
def update_post():
try:
content_json = request.json
# print content_json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'post' not in content_json or 'message' not in content_json:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(
"""UPDATE `posts` SET `message` = %s
WHERE `id` = %s;""",
(
content_json['message'],
int(content_json['post']),
)
)
db.commit()
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
post = functions.post_details(cursor, int(content_json['post']))
return jsonify({'code': 0, 'response': post})
def thread_detail():
thread_id = request.args.get('thread', None)
related = request.args.getlist('related')
thread_id = int(thread_id)
if thread_id is ('' or None) and thread_id <= 0:
return jsonify({'code': 2, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
thread = functions.thread_details(cursor, thread_id)
if thread is None:
return jsonify({'code': 1, 'response': "Post not found"})
if 'thread' in related:
return jsonify({'code': 3, 'response': "Incorrect request"})
if 'user' in related:
user = functions.user_details(cursor, thread['user'])
thread.update({'user': user})
if 'forum' in related:
forum = functions.forum_details(cursor, thread['forum'])
thread.update({'forum': forum})
return jsonify({'code': 0, 'response': thread})
def index():
from app import mysql
cur = mysql.get_db().cursor()
cur.execute("""Select * from empresas """)
data = cur.fetchall()
print(data)
return render_template('index.html')
def vote_post():
try:
content_json = request.json
# print content_json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'post' not in content_json or 'vote' not in content_json:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
if content_json['vote'] != 1 and content_json['vote'] != -1:
return jsonify({'code': 3, 'response': "Incorrect request: vote is wrong"})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
if content_json['vote'] == 1:
cursor.execute(
"""UPDATE `posts` SET `likes` = `likes` + 1, `points` = `points` + 1
WHERE `id` = %s;""",
(int(content_json['post']),))
else:
cursor.execute(
"""UPDATE `posts` SET `dislikes` = `dislikes` + 1, `points` = `points` - 1
WHERE `id` = %s;""",
(int(content_json['post']),))
db.commit()
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
post = functions.post_details(cursor, int(content_json['post']))
return jsonify({'code': 0, 'response': post})
def restore_thread():
try:
content_json = request.json
# print content_json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'thread' not in content_json or int(content_json['thread']) <= 0:
return jsonify({
'code': 3,
'response': "Incorrect request: some data missing"
})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
count = cursor.execute(
"""UPDATE `posts` SET `isDeleted` = FALSE
WHERE `thread` = %s;""", (content_json['thread'], ))
cursor.execute(
"""UPDATE `threads` SET `isDeleted` = FALSE, `posts` = %s
WHERE `id` = %s;""", (count, content_json['thread']))
db.commit()
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
return jsonify({'code': 0, 'response': {'thread': content_json['thread']}})
def remove_post():
try:
content_json = request.json
print content_json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'post' not in content_json:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(
"""UPDATE `threads` SET `posts` = `posts` - 1
WHERE `id` = (
SELECT `thread`
FROM `posts`
WHERE `id` = %s
);""",
(int(content_json['post']),)
)
cursor.execute("""UPDATE `posts` SET `isDeleted` = TRUE WHERE `id` = %s;""", (content_json['post'],))
db.commit()
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
return jsonify({'code': 0, 'response': {'post': content_json['post']}})
def create_forum():
try:
content_json = request.json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'name' not in content_json or 'short_name' not in content_json or 'user' not in content_json:
return jsonify({
'code': 3,
'response': "Incorrect request: some data missing"
})
db = mysql.get_db()
cursor = db.cursor()
try:
cursor.execute(
"""INSERT INTO `forums` (`name`, `short_name`, `user`)
VALUES (%s, %s, %s);""", (content_json['name'], content_json['short_name'],
content_json['user']))
except MySQLdb.Error:
return jsonify({
'code':
3,
'response':
"Incorrect request: name and short_name must be unique"
})
forum_id = cursor.lastrowid
db.commit()
content_json.update({'id': forum_id})
return jsonify({'code': 0, 'response': content_json})
def forum_detail():
short_name = request.args.get('forum', None)
related = request.args.get('related', [])
if short_name is ('' or None):
return jsonify({
'code': 2,
'response': "Incorrect request: some data missing"
})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute("""SELECT * FROM `forums` WHERE `short_name` = %s;""",
(short_name, ))
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
forum = cursor.fetchone()
if related == 'user':
user = functions.user_details(cursor, forum['user'])
forum.update({'user': user})
if forum is None:
return jsonify({'code': 1, 'response': "Post not found"})
return jsonify({'code': 0, 'response': forum})
def c_nivel_estudio():
from app import mysql
cur = mysql.get_db().cursor()
cur.execute("""Select * from nivel_estudios """)
data = cur.fetchall()
print(data)
return render_template('catalogos/niveles_estudio.html', niveles=data)
def update_thread():
try:
content_json = request.json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'message' not in content_json or 'slug' not in content_json or 'thread' not in content_json:
return jsonify({
'code': 3,
'response': "Incorrect request: some data missing"
})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(
"""UPDATE `threads` SET `message` = %s, `slug` = %s
WHERE `id` = %s;""",
(content_json['message'], content_json['slug'],
int(content_json['thread'])))
db.commit()
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
thread = functions.thread_details(cursor, int(content_json['thread']))
return jsonify({'code': 0, 'response': {'thread': thread}})
def details_post():
post_id = request.args.get('post', None)
related = request.args.getlist('related')
if post_id is None:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
post_id = int(post_id)
if post_id < 1:
return jsonify({'code': 1, 'response': "Incorrect request: post don\'t found"})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
post = functions.post_details(cursor, post_id)
if 'user' in related:
user = functions.user_details(cursor, post['user'])
post.update({'user': user})
if 'forum' in related:
forum = functions.forum_details(cursor, post['forum'])
post.update({'forum': forum})
if 'thread' in related:
thread = functions.thread_details(cursor, post['thread'])
post.update({'thread': thread})
return jsonify({'code': 0, 'response': post})
def create_post():
try:
content_json = request.json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'isApproved' not in content_json or 'user' not in content_json or 'date' not in content_json \
or 'message' not in content_json or 'isSpam' not in content_json or 'isHighlighted' not in content_json \
or 'thread' not in content_json or 'forum' not in content_json or 'isDeleted' not in content_json\
or 'isEdited' not in content_json:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor()
if content_json['parent'] is None:
isRoot = True
path = ''
else:
isRoot = False
cursor.execute("""SELECT `path` FROM `posts` WHERE `id` = %s""", (content_json['parent'],))
path = cursor.fetchone()[0]
try:
cursor.execute(
"""INSERT INTO `posts` (`isApproved`, `user`, `date`, `message`, `isSpam`,`isHighlighted`, `thread`,
`forum`,`isDeleted`, `isEdited`,`parent`,`isRoot`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);""",
(
content_json['isApproved'],
content_json['user'],
content_json['date'],
content_json['message'],
content_json['isSpam'],
content_json['isHighlighted'],
content_json['thread'],
content_json['forum'],
content_json['isDeleted'],
content_json['isEdited'],
content_json['parent'],
isRoot
)
)
post_id = cursor.lastrowid
base36 = int2str(int(post_id), radix=36)
path += str(len(base36)) + base36
cursor.execute("""UPDATE `posts` SET path = %s WHERE `id` = %s""", (path, post_id))
cursor.execute("""UPDATE `threads` SET `posts` = `posts` + 1 WHERE `id` = %s;""", (content_json['thread'],))
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request: post is already exist"})
db.commit()
content_json.update({'id': post_id})
return jsonify({'code': 0, 'response': content_json})
def status():
tables = ['users', 'threads', 'forums', 'posts']
response = {}
db = mysql.get_db()
cursor = db.cursor()
for table in tables:
cursor.execute('SELECT COUNT(1) FROM %s' % table)
db.commit()
response[table] = cursor.fetchone()[0]
#db.close()
return jsonify({'code': 0, 'response': response})
def details_user():
user_email = request.args.get('user', None)
if user_email is ('' or None):
return jsonify({'code': 2, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
user = functions.user_details(cursor, user_email)
if user is None:
return jsonify({'code': 1, 'response': 'User not found '})
return jsonify({'code': 0, 'response': user})
def list_posts_from_forum():
forum = request.args.get('forum', None)
since = request.args.get('since', None)
limit = request.args.get('limit', None)
order = request.args.get('order', 'DESC')
related = request.args.getlist('related')
if forum is None:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
else:
query = """SELECT `id`, `message`, `forum`, `user`, `thread`, `likes`, `dislikes`, `points`, `isDeleted`,
`isSpam`, `isEdited`, `isApproved`, `isHighlighted`, `date`, `parent`
FROM `posts` WHERE `forum` = %s """
query_params = (forum,)
if since is not None:
query += " AND `date` >= %s "
query_params += (since,)
query += " ORDER BY `date` " + order + " "
if limit is not None:
query += " LIMIT %s;"
query_params += (int(limit),)
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(query, query_params)
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
posts = [i for i in cursor.fetchall()]
resp = []
for post in posts:
if 'user' in related:
user = functions.user_details(cursor, post['user'])
post.update({'user': user})
if 'forum' in related:
forum = functions.forum_details(cursor, post['forum'])
post.update({'forum': forum})
if 'thread' in related:
thread = functions.thread_details(cursor, post['thread'])
post.update({'thread': thread})
post.update({'date': str(post['date'])})
resp.append(post)
return jsonify({'code': 0, 'response': resp})
def list_followers():
user_email = request.args.get('user', None)
order = request.args.get('order', 'DESC')
limit = request.args.get('limit', None)
since_id = request.args.get('since_id', '1')
if user_email is None:
return jsonify({'code': 1, 'response': "User not found "})
if limit is None:
limit = " "
else:
limit = ' LIMIT ' + limit
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(
"""SELECT `about`, `email`, `id`, `isAnonymous`, `name`, `username` FROM `followers` AS `f`
JOIN `users` ON `users`.`email` = `f`.`follower`
WHERE `f`.`followee` = %s AND `users`.`id` >= %s
ORDER BY `name` """ + order + limit + " ;",
(
user_email,
int(since_id)
)
)
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
users = [i for i in cursor.fetchall()]
for user in users:
following = functions.list_following(cursor, user['email'])
followers = functions.list_followers(cursor, user['email'])
cursor.execute(
"""SELECT `thread`
FROM `subscriptions`
WHERE `user` = %s;""",
(
user['email'],
)
)
threads = [i['thread'] for i in cursor.fetchall()]
user.update({'following': following, 'followers': followers, 'subscriptions': threads})
return jsonify({'code': 0, 'response': users})
def list_threads_from_forum():
forum = request.args.get('forum', None)
since = request.args.get('since', None)
limit = request.args.get('limit', None)
order = request.args.get('order', 'DESC')
related = request.args.getlist('related')
if forum is None:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
else:
query = """ SELECT * FROM `threads` WHERE `forum` = %s """
query_params = (forum,)
if since is not None:
query += " AND `date` >= %s "
query_params += (since,)
query += " ORDER BY `date` " + order + " "
if limit is not None:
query += "LIMIT %s;"
query_params += (int(limit),)
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(query,query_params)
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
resp = []
threads = [i for i in cursor.fetchall()]
for thread in threads:
if 'user' in related:
user = functions.user_details(cursor, thread['user'])
thread.update({'user': user})
if 'forum' in related:
forum = functions.forum_details(cursor, thread['forum'])
thread.update({'forum': forum})
thread.update({'date': str(thread['date'])})
resp.append(thread)
return jsonify({'code': 0, 'response': resp})
def clear():
db = mysql.get_db()
cursor = db.cursor()
cursor.execute('SET FOREIGN_KEY_CHECKS=0')
cursor.execute('TRUNCATE TABLE users')
cursor.execute('TRUNCATE TABLE threads')
cursor.execute('TRUNCATE TABLE posts')
cursor.execute('TRUNCATE TABLE forums')
cursor.execute('TRUNCATE TABLE followers')
cursor.execute('TRUNCATE TABLE subscriptions')
cursor.execute('SET FOREIGN_KEY_CHECKS=1')
db.commit()
return jsonify({'code': 0, 'response': 'OK'})
def list_users_from_forum():
forum = request.args.get('forum', None)
since_id = request.args.get('since_id', None)
limit = request.args.get('limit', None)
order = request.args.get('order', 'DESC')
if forum is None:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
if since_id is None:
since_str = " "
else:
since_str = " AND users.id >= " + since_id
if limit is None:
limit = " "
else:
limit = ' LIMIT ' + limit
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(
"""SELECT * FROM `users`
WHERE `email` IN (SELECT DISTINCT `user` FROM `posts` WHERE `forum` = %s)"""
+ since_str +
" ORDER BY `name` " + order + limit + " ;",
(
forum,
)
)
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
resp = []
users = [i for i in cursor.fetchall()]
for user in users:
user = functions.user_details(cursor, user['email'])
resp.append(user)
return jsonify({'code': 0, 'response': resp})
def list_threads_from_thread():
forum = request.args.get('forum', None)
user = request.args.get('user', None)
since = request.args.get('since', None)
limit = request.args.get('limit', None)
order = request.args.get('order', 'DESC')
if user is None and forum is None:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
if user is not None:
query = """SELECT * FROM `threads` WHERE `user` = %s """
query_params = (user,)
else:
query = """SELECT * FROM `threads` WHERE `forum` = %s """
query_params = (forum,)
if since is not None:
query += "AND `date` >= %s "
query_params += (since,)
query += "ORDER BY `date` " + order + " "
if limit is not None:
query += "LIMIT %s;"
query_params += (int(limit),)
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(query,query_params)
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
threads = [i for i in cursor.fetchall()]
for thread in threads:
thread.update({'date': str(thread['date'])})
return jsonify({'code': 0, 'response': threads})
def list_posts_from_user():
user = request.args.get('user', None)
since = request.args.get('since', None)
limit = request.args.get('limit', None)
order = request.args.get('order', 'DESC')
if user is None:
return jsonify({'code': 1, 'response': "User not found "})
query = """SELECT `id`, `message`, `forum`, `user`, `thread`, `likes`, `dislikes`, `points`, `isDeleted`,
`isSpam`, `isEdited`, `isApproved`, `isHighlighted`, `date`, `parent`
FROM `posts`
WHERE `user` = %s """
query_params = (user,)
if since is not None:
query += "AND `date` >= %s "
query_params += (since,)
query += "ORDER BY `date` " + order + " "
if limit is not None:
query += "LIMIT %s;"
query_params += (int(limit),)
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(query, query_params)
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
posts = [i for i in cursor.fetchall()]
for post in posts:
post.update({'date': str(post['date'])})
return jsonify({'code': 0, 'response': posts})
def close_thread():
try:
content_json = request.json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'thread' not in content_json:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor()
try:
cursor.execute(
"""UPDATE `threads` SET `isClosed` = TRUE
WHERE `id` = %s;""",
(int(content_json['thread']),)
)
except MySQLdb.Error:
db.rollback()
db.commit()
return jsonify({'code': 0, 'response': content_json})
def subscribe():
try:
content_json = request.json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'user' not in content_json or 'thread' not in content_json:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor()
try:
cursor.execute(
"""INSERT INTO `subscriptions` (`user`, `thread`)
VALUES (%s, %s);""",
(
content_json['user'],
content_json['thread'],
)
)
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
db.commit()
return jsonify({'code': 0, 'response': content_json})
def list_posts():
forum = request.args.get('forum', None)
thread = request.args.get('thread', None)
since = request.args.get('since')
limit = request.args.get('limit')
order = request.args.get('order', 'desc')
if thread is None and forum is None:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
if forum is not None:
query = """SELECT * FROM `posts` WHERE `forum` = %s """
query_params = (forum,)
else:
query = """SELECT * FROM `posts` WHERE `thread` = %s """
query_params = (thread,)
if since is not None:
query += "AND `date` >= %s "
query_params += (since,)
query += "ORDER BY `date` " + order + " "
if limit is not None:
query += "LIMIT %s;"
query_params += (int(limit),)
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute(query, query_params)
posts = [i for i in cursor.fetchall()]
for post in posts:
post.update({'date': str(post['date'])})
return jsonify({'code': 0, 'response': posts})
def unsubscribe():
try:
content_json = request.json
# print content_json
except BadRequest:
return jsonify({'code': 2, 'response': "Invalid request(syntax)"})
if 'user' not in content_json or 'thread' not in content_json :
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor()
try:
cursor.execute(
"""DELETE FROM`subscriptions`
WHERE `user` = %s AND `thread` = %s ;""",
(
content_json['user'],
int(content_json['thread']),
)
)
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
db.commit()
return jsonify({'code': 0, 'response': content_json})
def forum_detail():
short_name = request.args.get('forum', None)
related = request.args.get('related', [])
if short_name is ('' or None):
return jsonify({'code': 2, 'response': "Incorrect request: some data missing"})
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute("""SELECT * FROM `forums` WHERE `short_name` = %s;""", (short_name,))
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
forum = cursor.fetchone()
if related == 'user':
user = functions.user_details(cursor, forum['user'])
forum.update({'user': user})
if forum is None:
return jsonify({'code': 1, 'response': "Post not found"})
return jsonify({'code': 0, 'response': forum})
def list_posts_from_threads():
thread = request.args.get('thread', None)
since = request.args.get('since', " ")
limit = request.args.get('limit', None)
order = request.args.get('order', 'desc')
sort = request.args.get('sort', 'flat')
if thread is None:
return jsonify({'code': 3, 'response': "Incorrect request: some data missing"})
if sort is "flat":
query = """SELECT * FROM `posts` WHERE `thread` = %s """
query_params = (thread,)
if since is not None:
query += "AND `date` >= %s "
query_params += (since,)
query += "ORDER BY `date` " + order + " "
if limit is not None:
query += "LIMIT %s;"
query_params += (int(limit),)
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(query, query_params)
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
posts = [i for i in cursor.fetchall()]
for post in posts:
post.update({'date': str(post['date'])})
return jsonify({'code': 0, 'response': posts})
else:
subquery = """SELECT `path`
FROM `posts`
WHERE `isRoot` = TRUE AND `thread` = %s """
query_params = (thread,)
if since is not None:
subquery += " AND `date` >= %s "
query_params += (since,)
if limit is not None:
subquery += " ORDER BY `path` " + order + " LIMIT %s "
query_params += (int(limit),)
query = """SELECT *
FROM ( """ + subquery + """ ) as root
INNER JOIN `posts` as child
ON child.path LIKE CONCAT(root.path,'%%') """
if since is not None:
query += " WHERE child.date >= %s "
query_params += (since,)
query += " ORDER BY root.path " + order + ", child.path ASC "
if sort == 'tree' and limit is not None:
query += " LIMIT %s;"
query_params += (int(limit),)
db = mysql.get_db()
cursor = db.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(query, query_params)
except MySQLdb.Error:
return jsonify({'code': 3, 'response': "Incorrect request"})
posts = [i for i in cursor.fetchall()]
for post in posts:
post.update({'date': str(post['date'])})
return jsonify({'code': 0, 'response': posts})
