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 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 resource_folder_show(id):
if id==0:
return redirect(url_for('resource_index'))
folders = db.select_all("SELECT T_Resource_Folder.*,T_User.name FROM T_Resource_Folder,T_User \
WHERE parent=%s AND T_Resource_Folder.author=T_User.username;",(id,))
files = db.select_all("SELECT T_Resource_File.*,T_User.name FROM T_Resource_File,T_User \
WHERE folder_id=%s AND T_Resource_File.author=T_User.username;",(id,))
path=db.select_all("WITH RECURSIVE r AS (SELECT * FROM T_Resource_Folder WHERE id=%s union \
ALL SELECT T_Resource_Folder.* FROM T_Resource_Folder, r WHERE T_Resource_Folder.id = r.parent )\
SELECT * FROM r ORDER BY id;",(id,))
return render_template('resource/index.html',id=id,folders=folders,files=files,path=path)
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 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 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 forum_index():
page= int(request.args.get('page')) if request.args.get('page') else 1
forum = db.select_all("SELECT T_Forum.*,T_User.name,(SELECT COUNT(id)\
FROM T_Forum_Reply WHERE T_Forum_Reply.forum_id = T_Forum.id) AS comments \
FROM T_Forum,T_User WHERE T_Forum.author=T_User.username ORDER BY updated_at DESC \
LIMIT 15 OFFSET %s",[15*(page-1)])
return render_template('forum/index.html',forum=forum,page=page)
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 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 forum_search():
page= int(request.args.get('page')) if request.args.get('page') else 1
keyword = request.form['keyword']
forum = db.select_all("SELECT T_Forum.*,T_User.name,(SELECT COUNT(id)\
FROM T_Forum_Reply WHERE T_Forum_Reply.forum_id = T_Forum.id) AS comments \
FROM T_Forum,T_User WHERE T_Forum.author=T_User.username AND T_Forum.title ~* %s \
ORDER BY updated_at DESC",(keyword,))
return render_template('forum/index.html',forum=forum,page=page,keyword=keyword)
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 wiki_index():
page = int(request.args.get("page")) if request.args.get("page") else 1
wiki = db.select_all(
"SELECT T_Wiki.*,T_User.name FROM T_Wiki,T_User \
WHERE T_Wiki.author=T_User.username AND old=FALSE LIMIT 10 OFFSET %s",
[10 * (page - 1)],
)
return render_template("wiki/index.html", wiki=wiki, page=page)
def wiki_search():
page = int(request.args.get("page")) if request.args.get("page") else 1
keyword = request.form["keyword"]
wiki = db.select_all(
"SELECT T_Wiki.*,T_User.name FROM T_Wiki,T_User \
WHERE T_Wiki.author=T_User.username AND T_Wiki.title ~* %s AND old=FALSE",
(keyword,),
)
return render_template("wiki/index.html", wiki=wiki, keyword=keyword, page=page)
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 schedule_index():
# 所有用户
users=db.select_all("SELECT username,name FROM T_User")
user = session['user']['username'] if not request.args.get('username','') else request.args.get('username','')
# 要显示月份的时间
today = datetime.date.today()
year = (today.year if not request.args.get('year','') else int(request.args.get('year','')))
month = (today.month if not request.args.get('month','') else int(request.args.get('month','')))
begin_date = datetime.date(year, month, 1)
first_day_weekday = begin_date.weekday()
days_of_month = calendar.monthrange(year, month)[1]
end_date = begin_date + datetime.timedelta(days=days_of_month)
if session['user']['username']==user:
schedule = db.select_all("SELECT * FROM T_Schedule WHERE username=%s AND \
((begin_date>=%s AND begin_date<=%s) OR repeat > 0)",\
(user,begin_date,end_date))
else:
schedule = db.select_all("SELECT * FROM T_Schedule WHERE username=%s AND private=FALSE AND \
((begin_date>=%s AND begin_date<=%s) OR repeat > 0)",\
(user,begin_date,end_date))
return render_template('schedule/index.html',schedule=schedule,year=year,month=month,\
first_day_weekday = first_day_weekday, days_of_month = days_of_month,users=users,user=user)
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 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 user_index():
#if 'username' not in session:
#return redirect(url_for('account_signin'))
users = db.select_all("SELECT * FROM T_User WHERE username<>'admin';")
return render_template('user/index.html',users=users)
def resource_index():
folders = db.select_all("SELECT T_Resource_Folder.*,T_User.name FROM T_Resource_Folder,T_User \
WHERE LEVEL = 1 AND T_Resource_Folder.author=T_User.username;")
files = db.select_all("SELECT T_Resource_File.*,T_User.name FROM T_Resource_File,T_User \
WHERE folder_id IS NULL AND T_Resource_File.author=T_User.username;")
return render_template('resource/index.html',id=0,folders=folders,files=files,path=[])
def custom_tables():
tables=db.select_all("SELECT * FROM T_Table_Index")
return dict(custom_tables=tables)
def expense_index():
page= int(request.args.get('page')) if request.args.get('page') else 1
expenses = db.select_all("SELECT T_Expense.*,T_User.name FROM T_Expense,T_User \
WHERE T_Expense.author=T_User.username LIMIT 15 OFFSET %s",[15*(page-1)])
return render_template('expense/index.html',expenses=expenses,page=page)
def api_user_index():
users = db.select_all("SELECT username,name,position,resume,photo,rank,email_public FROM T_User WHERE username<>'admin'")
return json.dumps([dict(x) for x in users])
def api_resource_index():
page= int(request.args.get('page')) if request.args.get('page') else 1
items=db.select_all("SELECT id,title,filename,created_at FROM T_Resource_File WHERE public=TRUE \
LIMIT 10 OFFSET %s;",[10*(page-1)])
return json.dumps([dict(x) for x in items], cls=MyEncoder)
def custom_manage():
tables = db.select_all("SELECT * FROM T_Table_Index")
return render_template('custom/manage.html',tables=tables)