def api_custom_single_index(table_name):
table=db.select_one("SELECT * FROM T_Table_Index WHERE name=%s",[table_name])
columns=db.select_all("SELECT * FROM T_Table_Column WHERE table_id=%s",
[table['id']])
item=db.select_one("SELECT id,"+','.join([x['name'] for x in columns])+" FROM T_Custom_"+table_name+\
" ORDER BY id DESC LIMIT 1;")
return json.dumps(dict(item), cls=MyEncoder)
def custom_single_index(table_name):
table=db.select_one("SELECT * FROM T_Table_Index WHERE name=%s",[table_name])
columns=db.select_all("SELECT * FROM T_Table_Column WHERE table_id=%s",
[table['id']])
item=db.select_one("SELECT id,"+','.join([x['name'] for x in columns])+" FROM T_Custom_"+table_name+\
" ORDER BY id DESC LIMIT 1;")
return render_template('custom/single/index.html',table=table,columns=columns,item=item)
def custom_multi_show(table_name,id):
table=db.select_one("SELECT * FROM T_Table_Index WHERE name=%s",[table_name])
columns=db.select_all("SELECT * FROM T_Table_Column WHERE table_id=%s",
[table['id']])
item=db.select_one("SELECT T_Custom_"+table_name+".*,T_User.name as _author_name FROM T_Custom_"+table_name+\
",T_User WHERE T_Custom_"+table_name+"._author_by=T_User.username AND id=%s;",[id])
db.execute("UPDATE T_Custom_"+table_name+" SET _read_times=_read_times+1 WHERE id=%s;",[id])
return render_template('custom/multi/show.html',table=table,columns=columns,item=item)
def custom_manage_column_delete(id):
results = db.select_one("SELECT name,table_id FROM T_Table_Column WHERE id=%s",(id,))
column_name=results[0]
table_id=results[1]
table_name = db.select_one("SELECT name FROM T_Table_Index WHERE id=%s",(table_id,))[0]
db.execute("ALTER TABLE T_Custom_"+table_name+" DROP COLUMN "+ column_name +";")
db.execute("DELETE FROM T_Table_Column WHERE id=%s",[id])
return redirect(url_for('custom_manage'))
def resource_folder_delete(id):
folders=db.select_one("SELECT COUNT(id) FROM T_Resource_Folder WHERE parent=%s;",(id,))[0]
files=db.select_one("SELECT COUNT(id) FROM T_Resource_File WHERE folder_id=%s;",(id,))[0]
if folders==0 and files==0:
parent=db.select_one("SELECT parent FROM T_Resource_Folder WHERE id=%s;",(id,))[0]
db.execute("DELETE FROM T_Resource_Folder WHERE id=%s;",(id,))
if parent:
return redirect(url_for('resource_folder_show',id=parent))
else:
return redirect(url_for('resource_index'))
else:
abort(404)
def home_index():
username=session['user']['username']
message=db.select_one("SELECT COUNT(id) FROM T_Message WHERE %s=ANY(call);",\
(username,))[0]
posts=db.select_all("SELECT T_Message.*,T_User.name FROM T_Message,T_User \
WHERE public=TRUE AND T_Message.author=T_User.username ORDER BY id DESC LIMIT 3")
return render_template('home/index.html',message=message,posts=posts)
def custom_multi_edit(table_name,id):
table=db.select_one("SELECT * FROM T_Table_Index WHERE name=%s",[table_name])
columns=db.select_all("SELECT * FROM T_Table_Column WHERE table_id=%s",
[table['id']])
if request.method == 'GET':
item=db.select_one("SELECT T_Custom_"+table_name+".*,T_User.name as _author_name FROM T_Custom_"+table_name+\
",T_User WHERE T_Custom_"+table_name+"._author_by=T_User.username AND id=%s;",[id])
return render_template('custom/multi/edit.html',table=table,columns=columns,item=item)
else:
vars=[]
for c in columns:
vars.append(c['name'] + "='" + request.form[c['name']] +"'")
username=session['user']['username']
db.execute("UPDATE T_Custom_" + table['name'] + " SET "+','.join(vars)+",_author_by=%s,\
_updated_at=%s WHERE id=%s",[username, time.strftime('%Y-%m-%d %X',time.localtime(time.time())),id])
return redirect(url_for('custom_multi_index',table_name=table['name']))
def schedule_edit(id):
if request.method == 'GET':
schedule = db.select_one("SELECT * FROM T_schedule WHERE id=%s",[id])
return render_template('schedule/edit.html',schedule=schedule)
else:
username=session['user']['username']
author=db.select_one("SELECT username FROM T_Schedule WHERE id=%s",[id])[0]
if not author==username:
abort(401)
weekday = datetime.datetime.strptime(request.form['begin_date'],'%Y-%m-%d').weekday()
db.execute("UPDATE T_schedule SET title=%s,description=%s,begin_date=%s,begin_time=%s,\
duration=%s,repeat=%s,state=%s,private=%s,position=%s,weekday=%s WHERE id=%s;",\
(request.form['title'],request.form['description'],request.form['begin_date'],
request.form['begin_time'],request.form['duration'],request.form['repeat'],request.form['state'],
('private' in request.form),request.form['position'],weekday,id))
return redirect(url_for('schedule_index'))
def wiki_show(id):
wiki = db.select_one(
"SELECT T_Wiki.*,T_User.name FROM T_Wiki,T_User \
WHERE T_Wiki.author=T_User.username AND id=%s AND old=FALSE",
[id],
)
return render_template("wiki/show.html", wiki=wiki)
def custom_manage_edit(id):
if request.method == 'GET':
columns = db.select_all("SELECT * FROM T_Table_Column WHERE table_id=%s",[id])
return render_template('custom/edit.html',columns=columns,id=id)
else:
i = 1
table_name = db.select_one("SELECT name FROM T_Table_Index WHERE id=%s",(id,))[0]
while('name-' + str(i) in request.form):
name_value = request.form['name-' + str(i)]
title_value = request.form['title-' + str(i)]
type_value = request.form['type-' + str(i)]
if type_value == 'VCHAR':
column_type = 'VARCHAR(200)'
elif type_value == 'TEXT':
column_type = 'TEXT'
elif type_value == 'UNCHANGE':
column_type = 'VARCHAR(200)'
elif type_value == 'LINK':
column_type = 'VARCHAR(200)'
else:
column_type = 'VARCHAR(200)'
db.execute("ALTER TABLE T_Custom_"+table_name+" ADD COLUMN "+ name_value +" "+column_type+";")
db.execute("INSERT INTO T_Table_Column (table_id,name,title,type) VALUES(%s,%s,%s,%s);",\
(id,name_value,title_value,type_value))
i = i + 1
return redirect(url_for('custom_manage_edit',id=id))
def forum_show(id):
forum = db.select_one("SELECT T_Forum.*,T_User.name FROM T_Forum,T_User\
WHERE id=%s AND T_Forum.author=T_User.username",[id])
replies = db.select_all("SELECT T_Forum_Reply.*,T_User.name FROM T_Forum_Reply,T_User \
WHERE forum_id=%s AND T_Forum_Reply.author=T_User.username ORDER BY created_at",[id])
db.execute("UPDATE T_Forum SET read=read+1 WHERE id=%s",(id,))
return render_template('forum/show.html',forum=forum,replies=replies)
def custom_multi_index(table_name):
page= int(request.args.get('page')) if request.args.get('page') else 1
table=db.select_one("SELECT * FROM T_Table_Index WHERE name=%s",[table_name])
columns=db.select_all("SELECT * FROM T_Table_Column WHERE table_id=%s",
[table['id']])
items=db.select_all("SELECT T_Custom_"+table_name+".*,T_User.name as _author_name FROM T_Custom_"+table_name+\
",T_User WHERE T_Custom_"+table_name+"._author_by=T_User.username LIMIT 10 OFFSET %s;",[10*(page-1)])
return render_template('custom/multi/index.html',table=table,columns=columns,items=items,page=page)
def account_secure():
username=session['user']['username']
if request.method == 'GET':
user=db.select_one("SELECT * FROM T_User WHERE username=%s",(username,))
return render_template('account/secure.html',user=user)
else:
if request.form['new_password'] == request.form['repeat_password']:
result = db.select_one("SELECT COUNT(*) FROM T_User WHERE username=%s AND password=%s",\
[username,hashlib.md5(request.form['old_password'].encode('utf-8')).hexdigest()])[0]
if result > 0:
db.execute("UPDATE T_User SET password=%s WHERE username=%s",\
(hashlib.md5(request.form['new_password'].encode('utf-8')).hexdigest(),username))
return redirect(url_for('account_index'))
else:
return redirect(url_for('account_index'))
else:
return redirect(url_for('account_index'))
def api_custom_multi_index(table_name):
page= int(request.args.get('page')) if request.args.get('page') else 1
table=db.select_one("SELECT * FROM T_Table_Index WHERE name=%s",[table_name])
columns=db.select_all("SELECT * FROM T_Table_Column WHERE table_id=%s",
[table['id']])
items=db.select_all("SELECT T_Custom_"+table_name+".*,T_User.name as _author_name FROM T_Custom_"+table_name+\
",T_User WHERE T_Custom_"+table_name+"._author_by=T_User.username LIMIT 10 OFFSET %s;",[10*(page-1)])
return json.dumps([dict(x) for x in items], cls=MyEncoder)
def account_index():
username=session['user']['username']
if request.method == 'GET':
user=db.select_one("SELECT * FROM T_User WHERE username=%s",(username,))
return render_template('account/index.html',user=user)
else:
db.execute("UPDATE T_User SET name=%s,email=%s,email_public=%s WHERE username=%s",\
(request.form['name'],request.form['email'],request.form['email_public'],username))
return redirect(url_for('account_index'))
def resource_folder_new(parent):
author=session['user']['username']
if parent == 0:
level = 1
else:
level = db.select_one("SELECT level FROM T_Resource_Folder WHERE id=%s;",(parent,))[0] + 1
db.execute("INSERT INTO T_Resource_Folder(title,author,level,parent) VALUES(%s,%s,%s,%s);",\
(request.form['title'],author,level,parent))
if parent == 0:
return redirect(url_for('resource_index'))
else:
return redirect(url_for('resource_folder_show',id=parent))
def wiki_edit(id):
if request.method == "GET":
wiki = db.select_one("SELECT * FROM T_Wiki WHERE id=%s", [id])
return render_template("wiki/edit.html", wiki=wiki)
else:
author = session["user"]["username"]
db.execute("UPDATE T_Wiki SET old=True where id=%s", [id])
db.execute(
"INSERT INTO T_Wiki (title,author,content) VALUES(%s,%s,%s);",
(request.form["title"], author, request.form["content"]),
)
return redirect(url_for("wiki_index"))
def wiki_history(id):
new = db.select_one(
"SELECT T_Wiki.*,T_User.name FROM T_Wiki,T_User \
WHERE T_Wiki.author=T_User.username AND id=%s",
[id],
)
wiki = db.select_all(
"SELECT T_Wiki.*,T_User.name FROM T_Wiki,T_User \
WHERE T_Wiki.author=T_User.username AND title=%s AND old=True",
[new["title"]],
)
return render_template("wiki/history.html", wiki=wiki, new=new)
def custom_multi_new(table_name):
table=db.select_one("SELECT * FROM T_Table_Index WHERE name=%s",[table_name])
columns=db.select_all("SELECT * FROM T_Table_Column WHERE table_id=%s",
[table['id']])
if request.method == 'GET':
return render_template('custom/multi/new.html',table=table,columns=columns)
else:
vars=[]
for c in columns:
vars.append(request.form[c['name']])
db.execute("INSERT INTO T_Custom_"+table['name']+"(_author_by,"+','.join([x['name'] for x in columns])+") "+
"VALUES(%s,'"+ "','".join(vars)+"');",[session['user']['username']])
return redirect(url_for('custom_multi_index',table_name=table['name']))
def custom_single_edit(table_name):
table=db.select_one("SELECT * FROM T_Table_Index WHERE name=%s",[table_name])
columns=db.select_all("SELECT * FROM T_Table_Column WHERE table_id=%s",[table['id']])
if request.method == 'GET':
item=db.select_one("SELECT id,"+','.join([x['name'] for x in columns])+" FROM T_Custom_"+table_name+\
" ORDER BY id DESC LIMIT 1;")
editors=len([x for x in columns if x['type']=='TEXT'])
return render_template('custom/single/edit.html',table=table,columns=columns,item=item,editors=editors)
else:
vars=[]
count=db.select_one("SELECT COUNT(id) FROM T_Custom_"+table['name'])[0]
if count>0:
for c in columns:
vars.append(c['name'] + "='" + request.form[c['name']] +"'")
db.execute("UPDATE T_Custom_" + table['name'] + " SET "+','.join(vars)+" WHERE id=\
(select id from T_Custom_" + table['name'] +" ORDER BY id DESC LIMIT 1)")
else:
for c in columns:
vars.append(request.form[c['name']])
db.execute("INSERT INTO T_Custom_" + table['name'] + "("+','.join([x['name'] for x in columns])+") "+
"VALUES('"+"','".join(vars)+"');")
return redirect(url_for('custom_single_index',table_name=table['name']))
def account_resume():
username=session['user']['username']
if request.method == 'GET':
user=db.select_one("SELECT * FROM T_User WHERE username=%s",(username,))
return render_template('account/resume.html',user=user)
else:
file = request.files['photo']
if file:
filename = uploader.save_image_file(file)
db.execute("UPDATE T_User SET position=%s,grade=%s,resume=%s,photo=%s WHERE username=%s",\
(request.form['position'],request.form['grade'],request.form['resume'],filename,username))
else:
db.execute("UPDATE T_User SET position=%s,grade=%s,resume=%s WHERE username=%s",\
(request.form['position'],request.form['grade'],request.form['resume'],username))
return redirect(url_for('account_index'))
def account_signin():
if request.method == 'GET':
return render_template('account/signin.html')
else:
user = db.select_one("SELECT * FROM T_User WHERE username=%s AND password=%s AND state=TRUE",\
[request.form['username'],hashlib.md5(request.form['password'].encode('utf-8')).hexdigest()])
if user:
session['user'] = dict(user)
ip = request.remote_addr
db.execute("UPDATE T_User SET last_sign_in_at=current_sign_in_at, last_sign_in_ip=current_sign_in_ip,\
current_sign_in_at=NOW(),current_sign_in_ip=%s WHERE username=%s",\
(ip,request.form['username']))
return redirect(url_for('home_index'))
else:
flash('用户名或密码错误！请重试。')
return render_template('account/signin.html')
def user_edit(username):
if request.method == 'GET':
user = db.select_one("SELECT * FROM T_User WHERE username=%s;",[username])
return render_template('user/edit.html',user=user)
else:
file = request.files['photo']
if file:
filename = uploader.save_image_file(file)
db.execute("UPDATE T_User SET auth_expense_approve=%s,state=%s,rank=%s,name=%s,email=%s,email_public=%s,\
position=%s,grade=%s,resume=%s,photo=%s WHERE username=%s",\
(('auth_expense_approve' in request.form),('state' in request.form),request.form['rank'],\
request.form['name'],request.form['email'],request.form['email_public'],\
request.form['position'],request.form['grade'],request.form['resume'],filename,username))
else:
db.execute("UPDATE T_User SET auth_expense_approve=%s,state=%s,rank=%s,name=%s,email=%s,email_public=%s,\
position=%s,grade=%s,resume=%s WHERE username=%s",\
(('auth_expense_approve' in request.form),('state' in request.form),request.form['rank'],\
request.form['name'],request.form['email'],request.form['email_public'],\
request.form['position'],request.form['grade'],request.form['resume'],username))
return redirect(url_for('user_index'))
def schedule_show(id):
schedule = db.select_one("SELECT T_Schedule.*,T_User.name FROM T_schedule,T_User \
WHERE T_Schedule.username=T_User.username AND id=%s",[id])
return render_template('schedule/show.html',schedule=schedule)
def expense_show(id):
expense = db.select_one("SELECT * FROM T_expense WHERE id=%s",[id])
return render_template('expense/show.html',expense=expense)
def api_user_show(username):
user = db.select_one("SELECT username,name,position,resume,photo,rank,email_public FROM T_User\
WHERE username=%s",[username])
return json.dumps(dict(user))
def resource_file_show(id):
file = db.select_one("SELECT * FROM T_Resource_File WHERE id=%s;",[id])
return send_file(app.config['RESOURCE_FOLDER'] + file['filename'])
def api_custom_multi_show(table_name,id):
item = db.select_one("SELECT T_Custom_"+table_name+".*,T_User.name as _author_name FROM T_Custom_"+table_name+\
",T_User WHERE T_Custom_"+table_name+"._author_by=T_User.username AND id=%s;",[id])
return json.dumps(dict(item), cls=MyEncoder)
def resource_file_delete(id):
file = db.select_one("SELECT * FROM T_Resource_File WHERE id=%s;",[id])
os.remove(os.path.join('rtiss/'+app.config['RESOURCE_FOLDER'],file['filename']))
db.execute("DELETE FROM T_Resource_File WHERE id=%s",[id])
return redirect(url_for('resource_index'))
def custom_manage_delete(id):
table_name = db.select_one("SELECT name FROM T_Table_Index WHERE id=%s",(id,))[0]
db.execute("DROP TABLE IF EXISTS T_Custom_"+table_name+" ;")
db.execute("DELETE FROM T_Table_Column WHERE table_id=%s",[id])
db.execute("DELETE FROM T_Table_Index WHERE id=%s",[id])
return redirect(url_for('custom_manage'))
