def editar_alumno_form():
if "usuario" not in session.keys():
return redirect("/")
if session["usuario"]["id_credencial"] != 3:
return redirect("/")
if request.method == 'POST':
val = request.form.to_dict()
query = ('''
UPDATE Seccion_alumno
SET Seccion_alumno.rut_alumno = %s
WHERE Seccion_alumno.id_seccion = %s AND Seccion_alumno.rut_alumno = %s
''')
#cursor.execute(query, (
# val['rut_alumno'],
# val['id'],
# val['rut_original']
# ))
db.query(query, (val['rut_alumno'], val['id'], val['rut_original']))
seccion = obtener_seccion(val['id'])
curso = obtener_curso(seccion['id_curso'])
flash("El alumno se ha actualizado correctamente")
return redirect('/gestion_cursos/detalles_curso/' +
curso['codigo_udp'] + '/' + str(seccion['codigo']))
def createMatchStats(match_info):
stats = []
# Set up previous stats
previous_best_score_in_frame = list(db.query("""
SELECT IFNULL(max(fs.score), 0) as best_score
FROM tframe f, tframescore fs, tmatch m
WHERE m.match_id=f.match_id and f.frame_id=fs.frame_id and m.date < $date
""", vars={'date':match_info['date']}))[0]['best_score']
previous_most_fouls_in_frame = list(db.query("""
SELECT IFNULL(max(fs.foul_points), 0) as most_foul_points
FROM tframe f, tframescore fs, tmatch m
WHERE m.match_id=f.match_id and f.frame_id=fs.frame_id and m.date < $date
""", vars={'date':match_info['date']}))[0]['most_foul_points']
# Pass for each player
for frame in match_info['frames']:
for frame_score in frame['frame_scores']:
if frame_score['score'] > previous_best_score_in_frame:
stats.append(frame_score['name']+" beat the previous best frame score of "+
str(previous_best_score_in_frame)+" with "+str(frame_score['score'])+" points")
previous_best_score_in_frame = frame_score['score']
if frame_score['foul_points'] > previous_most_fouls_in_frame:
stats.append(frame_score['name']+" broke the record for most fouls in a frame with "+
str(frame_score['foul_points']))
previous_most_fouls_in_frame = frame_score['foul_points']
return stats
def getBasicMatchInfo(match_id):
log.debug('getBasicMatchInfo('+str(match_id)+')', 'tmatch')
match_info = list(db.query("""
SELECT match_id, date, strftime("%d/%m/%Y", date) as pretty_date, confirmed
FROM tmatch
WHERE match_id = $match_id
""", vars={'match_id':match_id}))[0]
match_info['match_scores'] = list(db.query("""
SELECT p.player_id, p.name, ms.won, ms.frames_won, ms.total_points
FROM tmatchscore ms, tplayer p
WHERE ms.match_id = $match_id AND
ms.player_id=p.player_id
ORDER BY p.player_id
""", vars={'match_id':match_id}))
# if match_info['winner_frame_count'] == match_info['loser_frame_count']:
# match_info['headline'] = match_info['winner_name']+' beats '+match_info['loser_name']+' on points scored'
# else:
# match_info['headline'] = match_info['winner_name']+' beats '+match_info['loser_name']+' '+str(match_info['winner_frame_count'])+'-'+str(match_info['loser_frame_count'])
match_info['headline'] = ''
return match_info
def editar_circuito(informacion_a_actualizar): # Query UPDATE
query = ('''
UPDATE Circuito
SET Circuito.codigo = %s,
Circuito.nombre = %s,
Circuito.cantidad = %s,
Circuito.descripcion = %s,
Circuito.dias_max_prestamo= %s,
Circuito.dias_renovacion= %s,
Circuito.imagen= %s
WHERE Circuito.id = %s
''')
#cursor.execute(query,(
# informacion_a_actualizar['nombre_circuito'],
# informacion_a_actualizar['cantidad_circuito'],
# informacion_a_actualizar['descripcion_circuito'],
# informacion_a_actualizar['dias_max_prestamo'],
# informacion_a_actualizar['dias_renovacion'],
# informacion_a_actualizar['imagen_circuito'],
# informacion_a_actualizar['id_circuito']
# ))
db.query(query, (informacion_a_actualizar['codigo_componente'],
informacion_a_actualizar['nombre_circuito'],
informacion_a_actualizar['cantidad_circuito'],
informacion_a_actualizar['descripcion_circuito'],
informacion_a_actualizar['dias_max_prestamo'],
informacion_a_actualizar['dias_renovacion'],
informacion_a_actualizar['imagen_circuito'],
informacion_a_actualizar['id_circuito']))
return informacion_a_actualizar
def editar():
if "usuario" not in session.keys():
return redirect("/")
if session["usuario"]["id_credencial"] != 3:
return redirect("/")
if request.method == 'POST':
datos_usuario = request.form.to_dict()
# Comprobar que no se repita el correo.
query = ''' SELECT rut FROM Usuario WHERE email= %s'''
#cursor.execute(query, (datos_usuario['correo'],))
cursor = db.query(query, (datos_usuario['correo'], ))
error_correo = cursor.fetchone()
if error_correo['rut'] != datos_usuario['rut']:
# hay otro usuario que ya utiliza el correo
flash('error-editar-correo')
return redirect("/gestion_usuarios")
# query para actualizar datos del usuario
query = ''' UPDATE Usuario SET id_credencial = %s, nombres =%s, apellidos= %s, region = %s, comuna = %s, direccion = %s
WHERE rut= %s'''
#cursor.execute(query, (datos_usuario['id_credencial'], datos_usuario['correo'], datos_usuario['nombres'],
# datos_usuario['apellidos'], datos_usuario['region'], datos_usuario['comuna'], datos_usuario['direccion'], datos_usuario['rut']))
db.query(query, (datos_usuario['id_credencial'],
datos_usuario['nombres'], datos_usuario['apellidos'],
datos_usuario['region'], datos_usuario['comuna'],
datos_usuario['direccion'], datos_usuario['rut']))
flash('editado-correcto')
# se redirige de vuelta a la pagina principal de gestion usuarios
return redirect("/gestion_usuarios")
def gestion_aprobar_solicitud_detalle():
if request.method == "POST" and session["usuario"][
"id_credencial"] == 3: # Si el metodo es POST y la sesion es nivel administrador
try:
IDD = request.form["id_solicitud_detalle"]
fecha_vencimiento_solicitud = request.form[
"fecha_vencimiento_solicitud"]
fecha_vencimiento_solicitud = datetime.strptime(
fecha_vencimiento_solicitud,
"%Y-%m-%d") # Cambio el formato de la fecha
fecha_vencimiento_solicitud = str(
fecha_vencimiento_solicitud.replace(
hour=18, minute=30,
second=0)) # Cambia la hora a las 18:30
query = (
'''SELECT Circuito.id,
Circuito.dias_max_prestamo,
Circuito.cantidad - Circuito.prestados AS disponibles,
Circuito.prestados,
Detalle_solicitud_circuito.cantidad AS solicitados
FROM Circuito
LEFT JOIN Detalle_solicitud_circuito
ON Detalle_solicitud_circuito.id_circuito = Circuito.id
WHERE Detalle_solicitud_circuito.id = %s'''
) # Query para consultar la informacion del componente antes de actualizar la solicitud
cursor = db.query(query, (IDD, ))
circuito_data = cursor.fetchone(
) # Guarda la informacion del componente
if circuito_data == None: # Si no se encuentra informacion del componente envia el error
return jsonify({
'error': 'no existe componente'
}) # Retorna que hay un error en la información
elif int(circuito_data["solicitados"]) <= int(
circuito_data["disponibles"]
): # Comprueba que la disponibilidad del componente (si hay solicitados <= disponibles)
cursor = db.query(
'UPDATE Circuito SET prestados = %s WHERE id = %s;',
(int(circuito_data["solicitados"]) +
int(circuito_data["prestados"]), circuito_data["id"]
)) # Actualiza la cantidad de componentes prestados
cursor = db.query(
'UPDATE Detalle_solicitud_circuito SET estado = 1, fecha_vencimiento = %s WHERE id = %s;',
(fecha_vencimiento_solicitud,
IDD)) # Actualiza la solicitud detalle
###############
# Enviar correo
###############
session["flash"] = 'sol-aprobada-correctamente'
return jsonify({'sucess': 'succes', 'solicitud': IDD})
else:
session["flash"] = 'error-solicitud'
return jsonify({'error': 'no hay suficientes componentes!'})
except:
session["flash"] = 'error-BDD'
return jsonify({'error': 'BDD'})
return jsonify({'error': 'acceso denegado'})
def gestion_borrar_solicitud_detalle():
if request.method == "POST" and session["usuario"]["id_credencial"] == 3:
try:
IDD = request.form["id_solicitud_detalle"]
IDS = request.form["id_solicitud"]
query = ('''DELETE FROM Detalle_solicitud_circuito
WHERE Detalle_solicitud_circuito.id = %s'''
) # borra la solicitud detalle circuito
db.query(query, (IDD, ))
query = ('''SELECT * FROM Detalle_solicitud_circuito
WHERE Detalle_solicitud_circuito.id_solicitud_circuito = %s'''
) # consulta los elementos con esa ID solicutud
cursor = db.query(query, (IDS, )) # consulta la solicitud
if len(cursor.fetchall(
)) < 1: # si los resultados de esa ID solicitud son < 1 los borra
query = ('''DELETE FROM Solicitud_circuito
WHERE Solicitud_circuito.id = %s''')
cursor = db.query(query, (IDS, ))
session["flash"] = 'sol-eliminada-correctamente'
return jsonify({'nice': 'nice!'})
except:
session["flash"] = 'error-BDD'
return jsonify({'error': 'BDD'})
return jsonify({'error': 'missing data!'})
def getBreak(break_id):
entries = list(
db.query("""
SELECT p.name AS player_name,
b.*
FROM tbreak b, tplayer p
WHERE b.player_id=p.player_id AND
b.break_id=$break_id
""",
vars={'break_id': break_id}))
vbreak = {}
if len(entries) == 1:
vbreak = entries[0]
else:
return {}
vbreak['pots'] = list(
db.query("""
SELECT b.*
FROM tball b, tbreakpot bp
WHERE bp.ball_id=b.ball_id AND
bp.break_id=$break_id
ORDER BY bp.pot_num
""",
vars={'break_id': break_id}))
return vbreak
def changeElo(player_id, elo_change, frame_id, opp_elo):
# update player elo
db.query("""
UPDATE tplayer
SET elo = elo + $change
WHERE player_id=$player_id
""",
vars={
'player_id': player_id,
'change': elo_change
})
# add elo jrnl entry
try:
db.query("""
INSERT INTO telojrnl (player_id, frame_id, elo_change, opp_elo, new_elo)
VALUES ($player_id, $frame_id, $elo_change, $opp_elo, (SELECT elo FROM tplayer WHERE player_id=$player_id) )
""",
vars={
'player_id': player_id,
'frame_id': frame_id,
'elo_change': elo_change,
'opp_elo': opp_elo
})
except sqlite3.IntegrityError, e:
log.error('SQL error while inserting entry into tEloJrnl')
def editar_curso_form():
if "usuario" not in session.keys():
return redirect("/")
if session["usuario"]["id_credencial"] != 3:
return redirect("/")
if request.method == 'POST':
val = request.form.to_dict()
query = ('''
UPDATE Curso
SET Curso.codigo_udp = %s,
Curso.nombre = %s,
Curso.descripcion = %s
WHERE Curso.id = %s
''')
#cursor.execute(query, (
# val['nuevo_codigo_udp'],
# val['nombre'],
# val['descripcion'],
# val['id']
# ))
db.query(query, (val['nuevo_codigo_udp'], val['nombre'],
val['descripcion'], val['id']))
flash("El curso se ha actualizado correctamente")
#se redirige de vuelta a la pagina principal de gestion usuarios
return redirect("/gestion_cursos")
def configurar_perfil():
if 'usuario' not in session:
return redirect('/')
if request.method == 'POST':
informacion_a_actualizar = request.form.to_dict()
#print(informacion_a_actualizar)
query = ('''
UPDATE Usuario
SET region = %s,
comuna = %s,
direccion = %s,
celular = %s
Where Usuario.rut = %s
''')
#cursor.execute(query,(
# informacion_a_actualizar['nombres'],
# informacion_a_actualizar['apellidos'],
# informacion_a_actualizar['region'],
# informacion_a_actualizar['comuna'],
# informacion_a_actualizar['direccion'],
# informacion_a_actualizar['celular'],
# session['usuario']['rut']
# ))
db.query(
query,
(informacion_a_actualizar['region'],
informacion_a_actualizar['comuna'],
informacion_a_actualizar['direccion'],
informacion_a_actualizar['celular'], session['usuario']['rut']))
return redirect('/')
def delete_comment_by_comment_id(id, uid, pid):
db.delete('_post_comments',
vars=dict(id=id, uid=uid),
where='id = $id and uid = $uid')
#更新post评论数
db.query("UPDATE _post set comment_num=comment_num-1 WHERE id=$id",
vars=dict(id=pid))
def moveData2History(self):
#step0: 判定是否需要启动数据搬迁>_max_start_num
_ret = db.query("SELECT COUNT(*) AS sum FROM HistoryTrack")
if _ret[0].sum<_max_start_num:
return 0
#step1：确定当前流入历史库的速度
incrNum = self.getCurrentInputSpeed()
#step2: 确定迁移的目标数据库
selTable =self.getInput2Table(incrNum)
selTableId = selTable.id
#step3: 确定选定要迁移的数据
items = db.query("SELECT * FROM HistoryTrack ORDER BY report_at LIMIT {0}".format(incrNum))
for i in range(incrNum):
item = items[i]
if selTable.sum==0:
db.update("HistoryTrackTable",where='id=$selTableId',
from_time=item.report_at.strftime("%Y-%m-%d %H:%M:%S"),
vars=locals())
#将数据移入新的数据库
db.insert(selTable.tableName,imei = item.imei,
report_at = item.report_at,
gpsLat = item.gpsLat,
gpsLng = item.gpsLng,
speed = item.speed,
realState = item.realState,
lngType = item.lngType,
latType = item.latType,
direction = item.direction,
addr = item.addr,
province = item.province,
city = item.city,
gpsReport = item.gpsReport,
reportMode = item.reportMode,
ACC = item.ACC,
CellID = item.CellID,
LAC = item.LAC,
MNC = item.MNC,
MCC = item.MCC,
satNum = item.satNum,
locatedState = item.locatedState,
gpsTime = item.gpsTime,
qqLat = item.qqLat,
qqLng = item.qqLng,
baiduLat = item.baiduLat,
baiduLng = item.baiduLng,
country = ('中国' if item.country is None else item.country),
alarm = ('' if item.alarm is None else item.alarm),
height = item.height,
battery = item.battery,
gsm_intensity = item.gsm_intensity,
seatStatus = item.seatStatus,
mileage = item.mileage,
)
#删除原来库的数据
db.query("DELETE FROM HistoryTrack WHERE id={0}".format(item.id))
#刷新截至时间与最新数据
db.update("HistoryTrackTable",where='id=$selTableId',
sum = selTable.sum+incrNum,
to_time=item.report_at.strftime("%Y-%m-%d %H:%M:%S"),vars=locals())
return incrNum
def getHistoryTrack(self,imei,from_time,to_time):
ponits = []
hisTables = []
strFrom = from_time.strftime("%Y-%m-%d %H:%M:%S")
strTo = to_time.strftime("%Y-%m-%d %H:%M:%S")
#规避gps重启的历史数据
gpsTime = (from_time - datetime.timedelta(days=1)).strftime("%Y-%m-%d %H:%M:%S")
#搜索可能存在的历史库
_hisTables = db.query("""SELECT *
FROM HistoryTrackTable
WHERE from_time<='{0}' AND to_time>='{0}'
ORDER BY from_time""".format(strTo,strFrom))
for tbl in _hisTables:
hisTables.append(tbl.tableName)
hisTables.append("HistoryTrack")
#在所有可能数据库中查找满足要求的数据
for tblName in hisTables:
_ret = db.query("""SELECT * FROM {3}
WHERE imei='{2}' AND
gpsTime >='{0}' AND gpsTime <='{1}' AND gmileage>0
ORDER BY gpsTime""".format(strFrom,strTo,imei,tblName))
for pt in _ret:
ponits.append(pt)
return ponits
def extender_prestamo():
if request.method == "POST" and session["usuario"][
"sancionado"] == False: # Si el usuario esta sancionado no podra extender el prestamo
solicitud_detalle_a_extender = request.form.to_dict()
query = ''' SELECT Detalle_solicitud.id,
Equipo.dias_renovacion
FROM Detalle_solicitud
LEFT JOIN Solicitud ON Solicitud.id = Detalle_solicitud.id_solicitud
LEFT JOIN Equipo ON Equipo.id = Detalle_solicitud.id_equipo
WHERE Detalle_solicitud.id = %s
AND Solicitud.rut_alumno = %s
AND Detalle_solicitud.estado = 2'''
cursor = db.query(
query, (solicitud_detalle_a_extender["id_solicitud_detalle"],
session["usuario"]["rut"]))
equipo_data = cursor.fetchone()
if len(equipo_data) == 0:
flash('sin-permiso')
else:
query = ('''UPDATE Detalle_solicitud
SET Detalle_solicitud.fecha_termino = DATE_ADD(Detalle_solicitud.fecha_termino, INTERVAL %s DAY),
Detalle_solicitud.renovaciones = Detalle_solicitud.renovaciones + 1
WHERE Detalle_solicitud.id = %s''')
db.query(query,
(equipo_data["dias_renovacion"],
solicitud_detalle_a_extender["id_solicitud_detalle"]))
flash('extender-correcto')
return redirect('/')
flash('sin-permiso')
return redirect('/')
def cancelar_solicitud():
if request.method == "POST":
solicitud = request.form.to_dict()
query = ''' SELECT Detalle_solicitud.id
FROM Detalle_solicitud
LEFT JOIN Solicitud ON Solicitud.id = Detalle_solicitud.id_solicitud
WHERE Detalle_solicitud.id = %s
AND Solicitud.rut_alumno = %s
AND Detalle_solicitud.estado = 0'''
cursor = db.query(
query,
(solicitud["id_solicitud_detalle"], session["usuario"]["rut"]))
if len(cursor.fetchall()) == 0:
flash('sin-permiso')
else:
query = ('''UPDATE Detalle_solicitud
SET Detalle_solicitud.estado = 7
WHERE Detalle_solicitud.id = %s'''
) # 7 == cancelado
db.query(query, (solicitud["id_solicitud_detalle"], ))
flash('cancelar-correcto')
return redirect('/')
return redirect('/')
def registrar_mensaje_administrativo():
datos_formulario = request.form.to_dict()
# Se verifica si existe fecha de eliminación
if not len(datos_formulario["fecha_eliminacion"]):
# No se especificó una fecha de eliminación
datos_formulario["fecha_eliminacion"] = str(datetime.now().date())
# Se agrega la hora 23:59 a la fecha de eliminación
datos_formulario["fecha_eliminacion"] = datetime.strptime(
datos_formulario["fecha_eliminacion"], "%Y-%m-%d")
datos_formulario["fecha_eliminacion"] = str(
datos_formulario["fecha_eliminacion"].replace(hour=23,
minute=59,
second=0))
sql_query = """
INSERT INTO Mensaje_administrativo
(titulo,mensaje,fecha_eliminacion,fecha_registro)
VALUES (%s,%s,%s,%s)
"""
#cursor.execute(sql_query,(datos_formulario["titulo"],datos_formulario["mensaje"],datos_formulario["fecha_eliminacion"],datetime.now().replace(microsecond=0)))
db.query(sql_query,
(datos_formulario["titulo"], datos_formulario["mensaje"],
datos_formulario["fecha_eliminacion"],
datetime.now().replace(microsecond=0)))
return redirect(redirect_url())
def get_unreaded_articles(uid, limit = -1):
update_unreaded_articles(uid)
if limit < 0:
result = db.query('SELECT * FROM Articles INNER JOIN UserArticles WHERE Articles.aSerial = UserArticles.aSerial AND UserArticles.uSerial = $uSerial', dict(uSerial = uid))
else:
result = db.query('SELECT * FROM Articles INNER JOIN UserArticles WHERE Articles.aSerial = UserArticles.aSerial AND UserArticles.uSerial = $uSerial LIMIT $limit', dict(uSerial = uid, limit = limit))
return result
def modificar_mensaje_administrativo(id_mensaje):
datos_formulario = request.form.to_dict()
# Se verifica si existe fecha de eliminación
if not len(datos_formulario["fecha_eliminacion"]):
# No se especificó una fecha de eliminación
datos_formulario["fecha_eliminacion"] = str(datetime.now().date())
# Se agrega la hora 23:59 a la fecha de eliminación
datos_formulario["fecha_eliminacion"] = datetime.strptime(
datos_formulario["fecha_eliminacion"], "%Y-%m-%d")
datos_formulario["fecha_eliminacion"] = str(
datos_formulario["fecha_eliminacion"].replace(hour=23,
minute=59,
second=0))
sql_query = """
UPDATE Mensaje_administrativo
SET titulo=%s,mensaje=%s,fecha_eliminacion=%s,fecha_actualizacion=%s
WHERE id=%s
"""
#cursor.execute(sql_query,(datos_formulario["titulo"],datos_formulario["mensaje"],datos_formulario["fecha_eliminacion"],str(datetime.now()),id_mensaje))
db.query(sql_query,
(datos_formulario["titulo"], datos_formulario["mensaje"],
datos_formulario["fecha_eliminacion"], str(
datetime.now()), id_mensaje))
flash("mensaje-modificado")
return redirect(redirect_url())
def aceptar_solicitud(id_detalle):
if "usuario" not in session.keys():
return redirect("/")
if session["usuario"]["id_credencial"] != 3:
return redirect("/")
fecha_revision_solicitud = str(datetime.now().replace(microsecond=0))
sql_query = """
UPDATE Wishlist
SET estado_wishlist = 8,fecha_revision=%s,motivo = NULL
WHERE id = %s
"""
#cursor.execute(sql_query, (fecha_revision_solicitud, id_detalle))
db.query(sql_query, (fecha_revision_solicitud, id_detalle))
sql_query = """
SELECT *
FROM Wishlist
WHERE id = %s
"""
#cursor.execute(sql_query, (id_detalle,))
cursor = db.query(sql_query, (id_detalle, ))
datos_solicitud = cursor.fetchone()
sql_query = """
SELECT nombres,apellidos,email
FROM Usuario
WHERE rut = %s
"""
#cursor.execute(sql_query, (datos_solicitud["rut_solicitante"],))
cursor = db.query(sql_query, (datos_solicitud["rut_solicitante"], ))
datos_usuario = cursor.fetchone()
direccion_template = os.path.normpath(
os.path.join(
BASE_DIR,
"app/templates/wishlist/templates_mail/aceptacion_solicitud.html"))
archivo_html = open(direccion_template, encoding="utf-8").read()
archivo_html = archivo_html.replace("%id_solicitud%", str(id_detalle))
archivo_html = archivo_html.replace("%nombre_usuario%",
datos_usuario["nombres"])
archivo_html = archivo_html.replace(
"%equipo_solicitado%", datos_solicitud["nombre_equipo"] + " " +
datos_solicitud["marca_equipo"] + " " +
datos_solicitud["modelo_equipo"])
archivo_html = archivo_html.replace(
"%fecha_registro%", str(datos_solicitud["fecha_solicitud"]))
archivo_html = archivo_html.replace("%fecha_revision_solicitud%",
fecha_revision_solicitud)
enviar_correo_notificacion(archivo_html,
"Aprobación de solicitud de Wishlist",
datos_usuario["email"])
flash("solicitud-aceptada-correctamente")
return redirect(redirect_url())
def detalle_usuario(rut):
if "usuario" not in session.keys():
return redirect("/")
if session["usuario"]["id_credencial"] != 3:
return redirect("/")
if request.method == 'GET':
# print(rut)
query = ''' SELECT Usuario.nombres as nombres, Usuario.apellidos as apellidos, Credencial.nombre as credencial,
Usuario.email as correo, Usuario.region as region, Usuario.comuna as comuna, Usuario.rut as rut,
Usuario.direccion as direccion, Usuario.celular as celular FROM Usuario,Credencial WHERE Credencial.id=Usuario.id_credencial AND Usuario.rut=%s '''
#cursor.execute(query, (rut,))
cursor = db.query(query, (rut, ))
usuario = cursor.fetchone()
query = '''
SELECT Solicitud.id as id, Solicitud.rut_profesor as profesor, Solicitud.rut_alumno as alumno, Solicitud.motivo as motivo, Solicitud.fecha_registro as registro,
Detalle_solicitud.estado as estado, Detalle_solicitud.id as id_detalle, Equipo.nombre as equipo, Equipo.modelo as modelo, Equipo.marca as marca_equipo, Estado_detalle_solicitud.nombre AS nombre_estado
FROM Solicitud, Detalle_solicitud, Equipo, Estado_detalle_solicitud
WHERE Solicitud.id = Detalle_solicitud.id_solicitud
AND Solicitud.rut_alumno= %s
AND Equipo.id = Detalle_solicitud.id_equipo
AND Detalle_solicitud.estado = Estado_detalle_solicitud.id
'''
#cursor.execute(query, (rut,))
cursor = db.query(query, (rut, ))
solicitudes = cursor.fetchall()
query = """
SELECT Curso.*,Seccion.codigo AS codigo_seccion
FROM Curso,Seccion,Seccion_alumno
WHERE Curso.id = Seccion.id_curso
AND Seccion.id = Seccion_alumno.id_seccion
AND Seccion_alumno.rut_alumno = %s
ORDER BY Curso.nombre
"""
#cursor.execute(query, (rut,))
cursor = db.query(query, (rut, ))
cursos = cursor.fetchall()
query = '''SELECT * FROM Sanciones WHERE rut_alumno = %s'''
#cursor.execute(query, (rut,))
cursor = db.query(query, (rut, ))
sancion = cursor.fetchone()
archivo_foto_perfil = obtener_foto_perfil(rut)
return render_template(
"/vistas_gestion_usuarios/detalle_usuario.html",
usuario=usuario,
solicitudes=solicitudes,
cursos=cursos,
sancion=sancion,
dir_foto_perfil=url_for('static',
filename='imgs/profile_pics/' +
archivo_foto_perfil))
def sancion():
data = request.form.to_dict()
if data['cant_sancion'] == '0':
flash("sin-cambio")
else:
query = ''' SELECT cantidad_dias FROM Sanciones WHERE rut_alumno= %s'''
#cursor.execute(query, (data['rut'],))
cursor = db.query(query, (data['rut'], ))
dias = cursor.fetchone()
dias = dias['cantidad_dias']
if data['op_sancion'] == '1': # Aumentar dias de sancion
dias = dias + int(data['cant_sancion'])
elif data['op_sancion'] == '2': # Disminuir dias de sancion
dias = dias - int(data['cant_sancion'])
if dias < 0:
flash("Error")
return redirect(redirect_url())
elif dias == 0:
query = ''' DELETE FROM Sanciones WHERE rut_alumno= %s '''
#cursor.execute(query, (data['rut'],))
db.query(query, (data['rut'], ))
flash("cambio-realizado")
return redirect(redirect_url())
# Se actualiza el registro y se comprueba si se debe eliminar.
query = ''' UPDATE Sanciones SET cantidad_dias = %s WHERE rut_alumno = %s'''
#cursor.execute(query, (dias, data['rut']))
cursor = db.query(query, (dias, data['rut']))
# print("cambio-realizado")
flash("cambio-realizado")
return redirect(redirect_url())
def editar_equipo_general(informacion_a_actualizar):
query = ('''UPDATE Equipo
LEFT JOIN Equipo_diferenciado ON Equipo_diferenciado.codigo_equipo = Equipo.codigo
SET Equipo_diferenciado.codigo_equipo = %s,
Equipo.codigo = %s,
Equipo.nombre = %s,
Equipo.modelo = %s,
Equipo.marca = %s,
Equipo.imagen = %s,
Equipo.descripcion = %s,
Equipo.dias_max_prestamo = %s,
Equipo.dias_renovacion = %s
WHERE
Equipo.codigo = %s''')
# A partir del codigo original se actualizan los equipos
db.query(
query,
(informacion_a_actualizar['codigo'],
informacion_a_actualizar['codigo'],
informacion_a_actualizar['nombre'],
informacion_a_actualizar['modelo'], informacion_a_actualizar['marca'],
informacion_a_actualizar['imagen'],
informacion_a_actualizar['descripcion'],
informacion_a_actualizar['dias_max_prestamo'],
informacion_a_actualizar['dias_renovacion'],
informacion_a_actualizar['codigo_original']))
return informacion_a_actualizar
def CancelFavorite(u_id, i_id):
db.delete('ImageFavorite',
vars=dict(img_id=i_id, user_id=u_id),
where='img_id = $img_id and user_id = $user_id')
#同时删除image里的‘favo_num’字段
db.query("UPDATE image set favo_num=favo_num-1 WHERE id=$id",
vars=dict(id=i_id))
def extender_prestamo_componetes():
if request.method == "POST" and session["usuario"]["sancionado"] == False:
solicitud = request.form.to_dict()
query = ''' SELECT Detalle_solicitud_circuito.id,
Circuito.dias_renovacion
FROM Detalle_solicitud_circuito
LEFT JOIN Solicitud_circuito ON Solicitud_circuito.id = Detalle_solicitud_circuito.id_solicitud_circuito
LEFT JOIN Circuito ON Circuito.id = Detalle_solicitud_circuito.id_circuito
WHERE Detalle_solicitud_circuito.id = %s
AND Solicitud_circuito.rut_alumno = %s
AND Detalle_solicitud_circuito.estado = 2'''
cursor = db.query(
query, (solicitud["id_sol_componente"], session["usuario"]["rut"]))
equipo_data = cursor.fetchone()
if len(equipo_data) == 0:
flash('sin-permiso')
else:
query = '''UPDATE Detalle_solicitud_circuito
SET Detalle_solicitud_circuito.fecha_termino = DATE_ADD(Detalle_solicitud_circuito.fecha_termino, INTERVAL %s DAY),
Detalle_solicitud_circuito.renovaciones = Detalle_solicitud_circuito.renovaciones + 1
WHERE Detalle_solicitud_circuito.id = %s'''
db.query(query, (equipo_data["dias_renovacion"],
solicitud["id_sol_componente"]))
flash('extender-correcto')
return redirect('/')
flash('sin-permiso')
return redirect('/')
def modificar_password_recuperacion():
datos_formulario = request.form.to_dict()
# Se obtiene la identificación del usuario mediante el ID de token
sql_query = """
SELECT rut_usuario
FROM Token_recuperacion_password
WHERE token_id = %s
"""
#cursor.execute(sql_query, (int(datos_formulario["token_id"]),))
cursor = db.query(sql_query, (int(datos_formulario["token_id"]), ))
datos_usuario = cursor.fetchone()
# Si se ha obtenido un registro, se obtiene el rut
if datos_usuario is not None:
rut_usuario = datos_usuario["rut_usuario"]
else:
# No existe un rut de usuario asociado al token (posible error)
# Se notifica al usuario y se redirecciona
flash("error-id-token")
return redirect("/")
# ------------------------------------ El registro de ID de token y usuario existe
# Se comprueba que ambas contraseñas (nueva y confirmación) coincidan
if datos_formulario["nueva_contraseña"] != datos_formulario[
"confirmacion_contraseña"]:
# Si no coindicen, se notifica y se retorna al formulario desde donde vino
flash("contraseñas-no-coinciden")
return redirect(redirect_url())
# Si coindicen, se elimina la confirmación
del datos_formulario["confirmacion_contraseña"]
# Se realiza la encriptación de la nueva contraseña para guardar en base de datos
hashpass = bcrypt.hashpw(
datos_formulario["nueva_contraseña"].encode(encoding="UTF-8"),
bcrypt.gensalt())
# Se almacena la nueva contraseña en la base de datos
sql_query = """
UPDATE Usuario
SET contraseña = %s
WHERE rut = %s
"""
#cursor.execute(sql_query, (hashpass.decode("UTF-8"), rut_usuario))
db.query(sql_query, (hashpass.decode("UTF-8"), rut_usuario))
# Se elimina el token generado de la base de datos
sql_query = """
DELETE FROM Token_recuperacion_password
WHERE token_id = %s
"""
#cursor.execute(sql_query, (int(datos_formulario["token_id"]),))
db.query(sql_query, (int(datos_formulario["token_id"]), ))
# Se notifica el éxito al modificar la contraseña
flash("contraseña-actualizada")
return redirect("/") # Se redirecciona al login
def update_unreaded_articles_board(uid, bSerial):
subscription = db.select('Subscriptions', dict(uid=uid, bSerial=bSerial), where='uSerial=$uid and bSerial=$bSerial')
if len(subscription) is not 0:
sub = subscription[0]
val = dict(uSerial = uid, bSerial = sub.bSerial, subscriptedDate = sub.lastSubscriptedDate)
db.query('INSERT ignore INTO UserArticles (SELECT $uSerial, aSerial, NOW() FROM Articles WHERE bSerial = $bSerial AND aUpdatedDatetime > $subscriptedDate)', val)
db.update('Subscriptions', vars=dict(uSerial=uid, bSerial=sub.bSerial), where='uSerial=$uSerial and bSerial = $bSerial', lastSubscriptedDate = web.SQLLiteral('NOW()'))
def save_char_entity(entity):
chars = util.str2char(entity.name)
db.query('update char_entities set status=0 where entity_id=%d' % (entity.pk_id))
li = []
for c in chars:
li.append("('%s',%d,%d,%d,1)"%( c['char'],c['count'],entity.type_id,entity.pk_id ) )
# print ','.join(li)
db.query('replace into char_entities (char_one,char_count,entity_type_id,entity_id,status)values%s'%(','.join(li)))
def eliminar_alumno(alumno):
query = ('''
DELETE Seccion_alumno FROM Seccion_alumno WHERE Seccion_alumno.rut_alumno = %s
''')
#cursor.execute(query,(alumno['rut_alumno'],))
db.query(query, (alumno['rut_alumno'], ))
return 'OK'
def insertar_lista_equipos_detalle(codigo_equipo, valores_a_insertar):
query = (
'''INSERT INTO Equipo_diferenciado (codigo_equipo, codigo_sufijo, codigo_activo, fecha_compra)
VALUES (%s, %s, %s, %s)''')
db.query(query, (codigo_equipo, valores_a_insertar['codigo_sufijo'],
valores_a_insertar['codigo_activo'],
valores_a_insertar['fecha_compra']))
return
def eliminar_archivos_modulo(id_modulo):
# Se eliminan todos los archivos al interior de la carpeta correspondiente al módulo
# Se obtiene el nombre de carpeta del módulo
sql_query = """
SELECT nombre_carpeta
FROM Modulo
WHERE id = %s
"""
#cursor.execute(sql_query,(id_modulo,))
cursor = db.query(sql_query, (id_modulo, ))
data_modulo = cursor.fetchone()
if data_modulo is None:
# Si el registro se eliminó inesperadamente, se retorna a la lista.
flash("error-inesperado")
return redirect("/documentacion_softwares")
# Se obtiene la lista de nombres de archivos registrados en el módulo
sql_query = """
SELECT nombre
FROM Documentacion_software
WHERE id_modulo = %s
"""
#cursor.execute(sql_query,(id_modulo,))
cursor = db.query(sql_query, (id_modulo, ))
lista_nombres_archivos = cursor.fetchall()
# Se elimina cada uno de los archivos si existen en la carpeta
for archivo in lista_nombres_archivos:
# Se construye la ruta de la carpeta del módulo
ruta_carpeta_modulo = os.path.normpath(
os.path.join(
os.getcwd(), "app/static/files/documentacion_softwares/" +
data_modulo["nombre_carpeta"]))
# Se construye la ruta del archivo a partir de la ruta del módulo
ruta_archivo = os.path.normpath(
os.path.join(ruta_carpeta_modulo, archivo["nombre"]))
# Si la ruta existe, entonces se elimina el archivo
if os.path.exists(ruta_archivo):
os.remove(ruta_archivo)
# Se eliminan los archivos registrados en la base de datos
sql_query = """
DELETE FROM Documentacion_software
WHERE id_modulo = %s
"""
#cursor.execute(sql_query,(id_modulo,))
db.query(sql_query, (id_modulo, ))
flash("archivos-modulo-eliminados")
return redirect(redirect_url())
def newPost(postImage, postTitle, postCaption, postArticle, postAuthor, postTemp, nodeId):
if postImage != '':
postImage = postImage.split('_')[0] + "_" + postImage.split('_')[1] + "_" + postImage.split('_')[2];
postArticle = htmlquote(postArticle).strip()
# postArticle = postArticle.replace("\r\n", "<br/>")
db.insert('_post', postImage=postImage, postTitle=postTitle, postCaption=postCaption, postArticle=postArticle, postAuthor=postAuthor, postTemp=postTemp, nodeId= nodeId)
db.query("UPDATE _node set postMount=postMount+1 WHERE id=$id", vars=dict(id=nodeId))
def add(ids, score, user_id):
if not 0 <= score <= 5 or not user_id or not ids: return
for id in ids:
db.query(
'insert votes (user_id, applicant_id, score)' +
' values ($user_id, $applicant_id, $score)' +
' on duplicate key update' +
' user_id = $user_id, applicant_id = $applicant_id, score = $score',
vars=dict(user_id=user_id, applicant_id=id, score=score))
update_calculated_votes(id)
def add(ids, score, user_id):
if not 0 <= score <= 5 or not user_id or not ids: return
for id in ids:
db.query(
'insert votes (user_id, applicant_id, score)' +
' values ($user_id, $applicant_id, $score)' +
' on duplicate key update' +
' user_id = $user_id, applicant_id = $applicant_id, score = $score',
vars = dict(user_id=user_id, applicant_id=id, score=score))
update_calculated_votes(id)
def eliminar_mensaje_administrativo(id_mensaje):
sql_query = """
DELETE FROM Mensaje_administrativo
WHERE id = %s
"""
#cursor.execute(sql_query,(id_mensaje,))
db.query(sql_query, (id_mensaje, ))
flash("mensaje-borrado")
return redirect(redirect_url())
def add_user(phone, email, nickname, avatar_url, password):
"""增加新用户"""
# 检查手机和Email是否存在
sql = "select id from user where phone = %s;"
if db.query(sql, phone):
return 0, "该手机号已经存在"
sql = "select id from user where email = %s;"
if db.query(sql, email):
return 0, "该Email已经被注册"
regist_time = datetime.now()
sql = "insert into user (phone, email, nickname, avatar_url, password, regist_time) values (%s, %s, %s, %s, %s, %s);"
return db.execute(sql, phone, email, nickname, avatar_url, password, regist_time)
def user_grouped_fid_per_session():
sql='''
SELECT uid, group_concat(fid) as submited_fid,group_concat(distinct(session)) as belong_session
FROM (select fid,uid,session from files where status='accepted')
GROUP BY uid,session
;'''
return db.query(sql)
def run_model(modelrun_id):
modelrun = db.query(ModelRun).get(modelrun_id)
try:
tmp_dir = os.path.join('/tmp/modelruns/',str(modelrun.id))
os.makedirs(tmp_dir)
modelrun.progress_state = PROGRESS_STATES['RUNNING']
db.commit()
kwargs = {'db':db,'modelrun_id':modelrun.id}
modelrunner = model_runners[modelrun.model_name]
input_resource_map = modelrunner.get_resource_map()
input_map = resolve_input_map(tmp_dir,input_resource_map,modelrun.resources)
output_resource_map = modelrunner.get_resource_map(type='outputs')
output_map = resolve_output_map(tmp_dir,output_resource_map)
kwargs.update(input_map)
kwargs.update(output_map)
module, method = modelrunner.get_model_runner()
method(event_emitter=ee,**kwargs)
# save the output resources now
resources = create_output_resources(modelrunner,output_map)
modelrun.resources.extend(resources)
modelrun.progress_state=PROGRESS_STATES['FINISHED']
except:
modelrun.progress_state=PROGRESS_STATES['ERROR']
db.commit()
# clean up
shutil.rmtree(tmp_dir)
def login(username, password, is_hash = False):
"""
로그인 처리. 사용자 ID와 암호를 평문으로 입력받은 다음,
해당하는 사용자가 있는 지 확인하고 존재하면 세션 키,
존재하지 않으면 오류 코드를 돌려 준다.
@type username: string
@param username: 사용자 ID.
@type password: string
@param password: 암호. 평문으로 전송되어야 한다.
@type is_hash: bool
@param is_hash: 인자로 넘겨준 암호가 해시인지 여부.
@rtype tuple
@return: 로그인 성공 여부(T/F)와 사용자 또는 오류 코드(실패 시)를 포함하는 튜플.
"""
val = dict(username = username)
result = db.query('SELECT * FROM Users WHERE uId = $username COLLATE utf8_general_ci',
vars = locals())
user = None
try:
user = result[0]
except IndexError:
return (False, _('NO_SUCH_USER'))
if is_hash:
if user.uPasswd == password:
return (True, user)
else:
return (False, _('INVALID_PASSWORD'))
else:
if not password_set[len(user.uPasswd)](user.uPasswd, password):
return (False, _('INVALID_PASSWORD'))
if len(user.uPasswd) < 64:
update_password(user.uSerial, password)
return (True, user)
def GET(self, *args):
l = len(args)
if l == 0:
use_query = db.query('SELECT COUNT(DISTINCT UKEY) AS USE_COUNT FROM USEFLAGS')
use_tuple = use_query[0]
use_data = {'USE_COUNT':use_tuple['USE_COUNT']}
if helpers.is_json_request():
return helpers.serialize(use_data)
else:
return render.use(use_data)
elif l == 1:
global_use_query = db.query('SELECT COUNT(DISTINCT UUID) AS GLOBAL_COUNT\
FROM GLOBAL_USEFLAGS RIGHT OUTER JOIN USEFLAGS\
ON GLOBAL_USEFLAGS.UKEY = USEFLAGS.UKEY\
WHERE USEFLAG=$useflag', vars={'useflag':args[0]})
plus_use_query = db.query('SELECT COUNT(DISTINCT IPKEY) AS PLUS_COUNT\
FROM PLUS_USEFLAGS RIGHT OUTER JOIN USEFLAGS\
ON PLUS_USEFLAGS.UKEY = USEFLAGS.UKEY\
WHERE USEFLAG=$useflag', vars={'useflag':args[0]})
minus_use_query = db.query('SELECT COUNT(DISTINCT IPKEY) AS MINUS_COUNT\
FROM MINUS_USEFLAGS RIGHT OUTER JOIN USEFLAGS\
ON MINUS_USEFLAGS.UKEY = USEFLAGS.UKEY\
WHERE USEFLAG=$useflag', vars={'useflag':args[0]})
unset_use_query = db.query('SELECT COUNT(DISTINCT IPKEY) AS UNSET_COUNT\
FROM UNSET_USEFLAGS RIGHT OUTER JOIN USEFLAGS\
ON UNSET_USEFLAGS.UKEY = USEFLAGS.UKEY\
WHERE USEFLAG=$useflag', vars={'useflag':args[0]})
global_use_tuple = global_use_query[0]
plus_use_tuple = plus_use_query[0]
minus_use_tuple = minus_use_query[0]
unset_use_tuple = unset_use_query[0]
use_data = {
'GLOBAL_COUNT':global_use_tuple['GLOBAL_COUNT'],
'PLUS_COUNT':plus_use_tuple['PLUS_COUNT'],
'MINUS_COUNT':minus_use_tuple['MINUS_COUNT'],
'UNSET_COUNT':unset_use_tuple['UNSET_COUNT']
}
if helpers.is_json_request():
return helpers.serialize(use_data)
else:
return render.use_useflag(args[0], use_data)
else:
return config.internalerror()
def doit(district):
q = db.query("select first_name, last_name, address1, address2, city, state, postal, f.value as comment from core_user u join core_action a on (a.user_id = u.id and (a.page_id = 164 or a.page_id = 163 or a.page_id = 160 or a.page_id = 158 or a.page_id = 157 or a.page_id = 153 or a.page_id = 149 or a.page_id = 148)) join core_actionfield f on (f.parent_id = a.id and f.name = 'comment' ) join core_location l on (l.user_id = u.id) where l.us_district = $district", vars=dict(district=district))
fh = file(district + '.txt', 'w')
for r in q:
if r.address2: r.address2 = '\n' + r.address2
fh.write(u'{first_name} {last_name}\n{address1}{address2}\n{city}, {state} {postal}\n\n{comment}\n\n\n'.format(**r).encode('utf8'))
def post(id):
if not session.has_key('username'):
return render_template('info.html', message=u'尚未登录', redirect='/login')
post = db.query(Post).filter_by(id=id).first()
if post:
return render_template('post.html', post=post)
else:
return render_template('info.html', message=u'公告不存在', redirect='/posts')
def get_comments(applicant_id):
return db.query(
'''select c.*, nickname, score \
from comments as c
left join users as u on c.user_id = u.id
left join votes as v on u.id = v.user_id and v.applicant_id = $id
where c.applicant_id = $id
order by c.creation_ts desc''',
vars = dict(id=applicant_id))
def get_categories_by_object(object_id):
if isinstance(object_id, list):
where = 'object_id IN $object_ids'
vars = {'object_ids': object_id}
else:
where = 'object_id = $object_id'
vars = {'object_id': object_id}
return db.query('SELECT t1.*, t2.object_id FROM categories AS t1 join object_category_relationships AS t2 ON t1.id = t2.category_id WHERE ' + where, vars=vars)
def GET(self):
feature_count = db.query('SELECT FEATURE,COUNT(UUID) AS HOSTS\
FROM HOST_FEATURES NATURAL JOIN FEATURES GROUP BY FEATURE')
feature_data = dict()
for t in feature_count:
feature_data[t['FEATURE']] = {'HOSTS':t['HOSTS']}
if helpers.is_json_request():
return helpers.serialize(feature_data)
else:
return render.feature(feature_data)
def GET(self):
mirror_count = db.query('SELECT MIRROR,COUNT(UUID) AS HOSTS\
FROM HOST_MIRRORS NATURAL JOIN GENTOO_MIRRORS GROUP BY MIRROR')
mirror_data = dict()
for t in mirror_count:
mirror_data[t['MIRROR']] = {'HOSTS':t['HOSTS']}
if helpers.is_json_request():
return helpers.serialize(mirror_data)
else:
return render.mirror(mirror_data)
def import_db(pk_id,bd_imgs,attrs):
field_values = ['last_update=now()']
raw_kv = []
for attr in attrs:
k = attr.split(',')[0].replace('"','').strip()
v = attr.split(':')[-1].replace('"','').strip()
raw_kv.append('"%s":"%s"'%(k,v))
if k in cn_fields.keys():
field = cn_fields[k]
field_values.append("%s='%s'" %(field,v))
if field_values:
sql = "update plants set %s where pk_id=%s" % (','.join(field_values),pk_id)
db.query(sql)
str_attrs = ','.join(raw_kv)
db.update('plants',
bd_imgs = bd_imgs,
bd_baike = str_attrs,
where="pk_id=$pk_id",vars=locals())
def GET(self):
repo_count = db.query('SELECT REPO,COUNT(DISTINCT IPKEY) AS PACKAGES,\
COUNT(DISTINCT UUID) AS HOSTS\
FROM INSTALLED_PACKAGES NATURAL JOIN REPOSITORIES GROUP BY REPO')
repo_data = dict()
for t in repo_count:
repo_data[t['REPO']] = {'HOSTS':t['HOSTS'], 'PACKAGES':t['PACKAGES']}
if helpers.is_json_request():
return helpers.serialize(repo_data)
else:
return render.repo(repo_data)
def fetch_prices(url):
ret = []
if url:
results = db.query('select ts, price from product natural join product_price where url=$url',
vars={'url':url})
for result in results:
ts = result['ts']
if isinstance(ts, datetime.datetime):
ts = ts.isoformat()
ret.append((ts, result['price']))
return ret
def get_tags_by_object(object_id, model):
if isinstance(object_id, list):
where = 'object_id IN $object_ids'
vars = {'object_ids': object_id}
else:
where = 'object_id = $object_id'
vars = {'object_id': object_id}
where += ' AND model = $model'
vars['model'] = model
return db.query('SELECT t1.*, t2.object_id FROM tags AS t1 join object_tag_relationships AS t2 ON t1.id = t2.tag_id WHERE ' + where, vars=vars)
def __GET_CPV(self, cat, pkg, ver):
p_query = db.query('SELECT COUNT(DISTINCT UUID) AS HOST_COUNT\
FROM INSTALLED_PACKAGES RIGHT OUTER JOIN PACKAGES\
ON INSTALLED_PACKAGES.PKEY = PACKAGES.PKEY\
WHERE CAT=$cat AND PKG=$pkg AND VER=$ver', vars={'cat':cat, 'pkg':pkg, 'ver':ver})
p_tuple = p_query[0]
p_data = {
'HOST_COUNT':p_tuple['HOST_COUNT'],
}
if helpers.is_json_request():
return helpers.serialize(p_data)
else:
return render.package_cpv(cat, pkg, ver, p_data)
def get(req_path):
if req_path == 'top_users':
top_users_data = db.query(
'SELECT name, count(nomnoms.id) as count '
'FROM users,nomnoms '
'WHERE nomnoms.user_id = users.id '
'AND users.id != 1 '
'GROUP BY users.name ORDER BY count DESC LIMIT 6'
)
top_users = []
for user in top_users_data:
top_users.append(user)
return {'top_users': top_users}
def unread_count(uid):
"""
UID로 지정한 사용자의 받은 편지함에 있는 읽지 않은 메일 갯수를 가져온다.
@type uid: int
@param uid: 사용자 ID.
@rtype int
@return: 읽지 않은 편지 갯수.
"""
u = user.get_user(uid)
if not u[0]:
return -1
result = db.query('SELECT COUNT(*) AS c FROM Mails WHERE mReceiverSerial=$uid AND mMark = 0', vars = locals());
return result[0].c
def time_entries(where, **vars):
query = """
SELECT time_entries.*, categories.name as category
FROM time_entries left join categories ON
time_entries.category_id = categories.id
WHERE %s
ORDER BY time_entries.start_time DESC
""" % where
def map_minutes(e):
e.duration = minutes(e.end_time - e.start_time)
e.hours = round(e.duration / 60.0, 2)
return e
return [map_minutes(e) for e in db.query(query, vars=vars)]
def getAll(cls, **kw):
"""
Get All by condition
"""
L = []
args = {}
for k, v in kw.iteritems():
L.append('`{}`=${}'.format(k, k))
args[k] = v
if len(L) > 0:
d = list(db.select(cls.__table__, args, where=' and '.join(L)))
else:
d = list(db.query('select * from `{}`'.format(cls.__table__)))
return [cls(item) for item in d]
def search(query, offset=0, limit=10):
query = get_nice_query(query)
if not query:
return [], False
def sqlands(left, lst):
return left + (" and %s " % left).join(lst)
q = [str(web.sqlquote(w)) for w in query.split()]
tag_query = web.sqlors("tag = ", q)
q = [str(web.sqlquote("%%" + w + "%%")) for w in query.split()]
where = []
for c in ["title", "url", "description", "author"]:
where.append(sqlands("%s like " % c, q))
text_query = " or ".join(where)
params = {
"tag_query": tag_query,
"text_query": text_query,
"offset": offset,
"limit": limit + 1,
"size": len(query),
}
m = list(
db.query(
"\
(select distinct m.id, title, url, description, author, screenshot, \
calculated_vote as votes, m.datetime_created as dates \
from modules as m left join tags as t on m.id = t.module_id \
where %(tag_query)s \
group by t.module_id \
having count(t.module_id) >= %(size)d) \
union \
(select distinct m.id, title, url, description, author, screenshot, \
calculated_vote as votes, m.datetime_created as dates \
from modules as m \
where %(text_query)s \
order by calculated_vote desc, datetime_created desc) \
order by votes desc, dates desc limit %(limit)d offset %(offset)d"
% params
)
)
has_next = len(m) > limit
return m[:limit], has_next
def login():
if request.method == 'GET':
return render_template('login.html')
elif request.method == 'POST':
username = request.form['username']
password = request.form['password']
result = db.query(User).filter_by(username=username).first()
if result:
if result.password == password:
session['username'] = username
return render_template('info.html', message=u'登录成功', redirect='/posts')
else:
return render_template('info.html', message=u'登录失败', redirect='/login')
else:
return render_template('info.html', message=u'用户不存在', redirect='/login')
def get_article_list_by_user(uid, page_size, page_number):
val = dict(uid = uid)
total_article = get_post_count(uid)
if total_article < 0:
return []
last_page = (total_article + page_size - 1) / page_size
if not (page_number >= 1 and page_number <= last_page):
return []
begin = page_size * (page_number - 1)
val = dict(uid = uid, begin = begin, page_size = page_size)
result = db.query('''SELECT * FROM Articles
NATURAL LEFT JOIN (SELECT aSerial, COUNT(*) AS comment_count FROM Comments GROUP BY aSerial) AS comment_group
NATURAL LEFT JOIN (SELECT bSerial, bName AS board_name FROM Boards) AS board_group
WHERE uSerial=$uid
ORDER BY aDatetime DESC
LIMIT $begin, $page_size''', val)
return result
def _get_uid_from_username(username):
"""
사용자 이름을 UID로 변환한다.
@type username: string
@param username: 사용자 이름
@rtype int
@return: 이름에 해당하는 사용자 ID.
"""
val = dict(username = username)
result = db.query('SELECT * FROM Users WHERE uId = $username COLLATE utf8_general_ci', vars = locals())
try:
retvalue = int(result[0]['uSerial'])
except IndexError:
return -1
else:
return retvalue
def __GET_CP(self, top, cat, pkg):
"""
Get category/package-version
"""
p_query = db.query('SELECT COUNT(DISTINCT UUID) AS HOST_COUNT, \
COUNT(DISTINCT CAT, PKG, VER) AS CPV_COUNT\
FROM INSTALLED_PACKAGES RIGHT OUTER JOIN PACKAGES\
ON INSTALLED_PACKAGES.PKEY = PACKAGES.PKEY\
WHERE CAT=$cat AND PKG=$pkg', vars={'cat':cat, 'pkg':pkg})
p_tuple = p_query[0]
p_data = {
'HOST_COUNT':p_tuple['HOST_COUNT'],
'CPV_COUNT':p_tuple['CPV_COUNT'],
'TOP_CPV':self.__top(top, cat, pkg)
}
if helpers.is_json_request():
return helpers.serialize(p_data)
else:
return render.package_cp(cat, pkg, p_data)