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_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 expense_new():
if request.method == 'GET':
return render_template('expense/new.html')
else:
author=session['user']['username']
db.execute("INSERT INTO T_Expense(title,author,details,cost,finished) VALUES(%s,%s,%s,%s,%s);",\
(request.form['title'],author,request.form['details'],request.form['cost'],request.form['finished']))
return redirect(url_for('expense_index'))
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 forum_new():
if request.method == 'GET':
return render_template('forum/new.html')
else:
author=session['user']['username']
db.execute("INSERT INTO T_Forum(title,author,content) VALUES(%s,%s,%s);",\
(request.form['title'],author,request.form['content']))
return redirect(url_for('forum_index'))
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 user_new():
if request.method == 'GET':
return render_template('user/new.html')
else:
password_md5 = hashlib.md5(request.form['password'].encode('utf-8')).hexdigest()
db.execute("INSERT INTO T_User(username,name,password) VALUES(%s,%s,%s);",\
(request.form['username'],request.form['name'],password_md5))
return redirect(url_for('user_index'))
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 wiki_new():
if request.method == "GET":
return render_template("wiki/new.html")
else:
author = session["user"]["username"]
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_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 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 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 schedule_new():
if request.method == 'GET':
return render_template('schedule/new.html')
else:
username=session['user']['username']
weekday = datetime.datetime.strptime(request.form['begin_date'],'%Y-%m-%d').weekday()
db.execute("INSERT INTO T_Schedule(username,title,description,begin_date,begin_time,duration,\
repeat,state,private,position,weekday) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);",
(username,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))
return redirect(url_for('schedule_index'))
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 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 resource_file_new(folder):
author=session['user']['username']
file = request.files['file']
if file:
filename = uploader.save_resource_file(file)
if 'public' in request.form:
public = True
else:
public = False
if folder == 0:
db.execute("INSERT INTO T_Resource_File(title,author,filename,public) VALUES(%s,%s,%s,%s);",\
(request.form['title'],author,filename,public))
else:
db.execute("INSERT INTO T_Resource_File(title,author,filename,folder_id,public) VALUES(%s,%s,%s,%s,%s);",\
(request.form['title'],author,filename,folder,public))
return redirect(url_for('resource_folder_show',id=folder))
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 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 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 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 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 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 custom_manage_new():
if request.method == 'GET':
return render_template('custom/new.html')
else:
multi= 'multi' in request.form
db.execute("INSERT INTO T_Table_Index(name,title,multi) VALUES(%s,%s,%s);",\
(request.form['name'],request.form['title'],multi))
table_name="T_Custom_"+ request.form['name']
if multi:
db.execute("DROP TABLE IF EXISTS "+table_name+";CREATE TABLE "+table_name+"(id SERIAL PRIMARY KEY,\
_read_times INTEGER DEFAULT 0,\
_author_by VARCHAR(20),\
_updated_at TIMESTAMP DEFAULT NOW() );")
else:
db.execute("DROP TABLE IF EXISTS "+table_name+";CREATE TABLE "+table_name+"(id SERIAL PRIMARY KEY);")
return redirect(url_for('custom_manage'))
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 schedule_delete(id):
db.execute("DELETE FROM T_schedule WHERE id=%s",[id])
return redirect(url_for('schedule_index'))
def wiki_delete(id):
db.execute("DELETE FROM T_wiki WHERE id=%s", [id])
return redirect(url_for("wiki_index"))
def custom_multi_delete(table_name,id):
db.execute("DELETE FROM T_Custom_"+table_name+" WHERE id=%s;",[id])
return redirect(url_for("custom_multi_index",table_name=table_name))
def expense_delete(id):
db.execute("DELETE FROM T_expense WHERE id=%s",[id])
return redirect(url_for('expense_index'))
def expense_approve(id):
username=session['user']['username']
db.execute("UPDATE T_expense SET approved=TRUE, approved_by=%s WHERE id=%s;",[username,id])
return redirect(url_for('expense_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'))