def personal_change():
if request.method == 'GET':
cur = db.cursor()
username_get = session.get('user_id')
sql = "select email,nickname,phone from SDWZCS.userInformation where email = '%s'" % username_get
db.ping(reconnect=True)
cur.execute(sql)
userinformation = cur.fetchone()
email = userinformation[0]
phone = userinformation[2]
nickname = userinformation[1]
return render_template('personal_change.html',
email=email,
phone=phone,
nickname=nickname)
else:
cur = db.cursor()
username_get = session.get('user_id')
nickname = request.form.get('nickname')
email = request.form.get('email')
phone = request.form.get('phone')
sql = "update userInformation set SDWZCS.userInformation.nickname = '%s', SDWZCS.userInformation.phone = '%s' where SDWZCS.userInformation.email = '%s'" % (
nickname, phone, username_get)
try:
db.ping(reconnect=True)
cur.execute(sql)
db.commit()
return redirect(url_for('personal'))
except Exception as e:
raise e
def detail_question():
formula_id = request.values.get('formula_id')
if formula_id is None:
return redirect(url_for('formula'))
if request.method == 'GET':
page = request.values.get('page')
if page is None:
page = int(1)
page = int(page)
try:
cur = db.cursor()
sql = "select max(qno) from question_detail where formula_id = '%s'" % formula_id
db.ping(reconnect=True)
cur.execute(sql)
question = cur.fetchone()[0]
page_num = int(question / 20 + 0.96)
# 防止页码溢出
if page < 1:
page = int(1)
if page > page_num:
page = int(page_num)
cur = db.cursor()
sql = "select title from SDWZCS.formula_post where formula_id = '%s'" % formula_id
db.ping(reconnect=True)
cur.execute(sql)
title = cur.fetchone()[0]
sql = "select formula_id, qno, content, datetime, nickname from question_detail,SDWZCS.userInformation where question_detail.author = userInformation.email and formula_id = '%s'" % formula_id
db.ping(reconnect=True)
cur.execute(sql)
result = cur.fetchall()
return render_template('detail_question.html',
question_inf=result,
title=title,
page=page,
page_num=page_num,
formula_id=formula_id)
except Exception as e:
raise e
if request.method == 'POST':
content = request.form.get('editorValue')
datetime = date = time.strftime("%Y-%m-%d %H:%M:%S")
username = session.get('user_id')
try:
cur = db.cursor()
sql = "select max(qno) from question_detail where formula_id = '%s'" % formula_id
db.ping(reconnect=True)
cur.execute(sql)
qno = int(cur.fetchone()[0]) + 1
sql = "insert into question_detail(formula_id, qno, content, datetime, author) VALUES ('%s','%s','%s','%s','%s')" % (
formula_id, qno, content, datetime, username)
db.ping(reconnect=True)
cur.execute(sql)
db.commit()
cur.close()
return redirect(url_for('detail_question', formula_id=formula_id))
except Exception as e:
raise e
def new_dispute(message):
cursor = db.cursor()
text = str(message.text).replace('/new_dispute', '').strip()
caption = text.split('\n')[0]
content = ''
try:
content = text.split('\n', 1)[1]
except IndexError:
pass
if len(content) + len(caption) > 160:
bot.send_message(message.chat.id, 'Нельзя создавать вопрос длиной более 160 символов')
cursor.close()
return
if caption:
cursor.execute(
"insert into Dispute(user_id,caption,content) VALUES ({0},'{1}','{2}')".format(message.from_user.id,
caption, content))
sent_message = bot.send_message(message.chat.id, 'Вопрос "{0}" создан'.format(caption),
reply_markup=markup_common)
connect_message(message, dispute_id=cursor.lastrowid)
connect_message(sent_message, dispute_id=cursor.lastrowid)
cursor.execute('update User set state=state||? WHERE id=?',
['d{}/'.format(cursor.lastrowid), message.from_user.id])
db.commit()
cursor.close()
def login():
if request.method == 'GET':
return render_template('login.html')
if request.method == 'POST':
email = request.form.get('email')
password = request.form.get('password')
if not all([email, password]):
flash("请将信息填写完整！")
return render_template('login.html')
try:
cur = db.cursor()
sql = "select password from UserInformation where email = '%s'" % email
db.ping(reconnect=True)
cur.execute(sql)
result = cur.fetchone()
if result is None:
flash("该用户不存在")
return render_template('login.html')
if check_password_hash(result[0], password):
session['email'] = email
session.permanent = True
cur.close()
return redirect(url_for('index'))
else:
flash("密码错误！")
return render_template('login.html')
except Exception as e:
raise e
def sample_get():
db = get_db()
cursor = db.cursor()
cursor.execute("select * from employees;")
result = cursor.fetchall()
cursor.close()
return jsonify(result)
def login():
if request.method == 'POST':
email = request.form.get('email')
password = request.form.get('password')
if not all([email, password]):
flash('请填写完整信息')
else:
try:
cur = db.cursor()
sql = "select password from userInformation where email='%s'" % (
email)
db.ping(reconnect=True)
cur.execute(sql)
result = cur.fetchone()
if result is None:
flash('无此用户')
if check_password_hash(result[0], password):
session['user_id'] = email
session.permanent = True
return redirect(url_for('homepage'))
else:
flash('密码错误')
return redirect(url_for('login'))
except Exception as e:
flash('无此用户')
return redirect(url_for('login'))
raise e
return render_template('login.html')
def post_question_md():
if request.method == 'GET':
return render_template('post_blog_md.html')
if request.method == 'POST':
try:
cur = db.cursor()
author = session.get('user_id')
title = request.form.get('title')
content = request.form.get('html_content')
print(request.values)
# 如果未登录 则跳转到登录页面
if author is None:
return redirect(url_for('login'))
date = time.strftime("%Y-%m-%d %H:%M:%S")
sql = "select max(bno) from SDWZCS.blog"
db.ping(reconnect=True)
cur.execute(sql)
result = cur.fetchone()[0]
if result is None:
bno = 1
else:
bno = int(result) + 1
sql = "insert into blog(bno, title, content, md_or_fwb, creatTime, author) VALUES ('%s','%s','%s','%s','%s','%s')" % (
bno, title, content, '1', date, author)
db.ping(reconnect=True)
cur.execute(sql)
db.commit()
cur.close()
return redirect(url_for('technology_Blog'))
except Exception as e:
raise e
def post_questions():
if request.method == 'GET':
return render_template('post_question_fwb.html')
else:
try:
cur = db.cursor()
email = session.get('user_id')
title = request.form.get('title')
content = request.form.get('editorValue')
date = time.strftime("%Y-%m-%d %H:%M:%S")
sql = "select max(formula_id) from SDWZCS.formula_post"
db.ping(reconnect=True)
cur.execute(sql)
result = cur.fetchone()[0]
if result is None:
formula_id = 1
else:
formula_id = int(result) + 1
sql = "insert into SDWZCS.formula_post(SDWZCS.formula_post.formula_id, SDWZCS.formula_post.author, " \
"SDWZCS.formula_post.title,SDWZCS.formula_post.creat_time) values ('%s','%s','%s','%s')" % (
formula_id,email,title,date)
db.ping(reconnect=True)
cur.execute(sql)
db.commit()
sql = "insert into question_detail(formula_id, qno, content, datetime, author) VALUES ('%s','1','%s','%s','%s')" % (
formula_id, content, date, email)
db.ping(reconnect=True)
cur.execute(sql)
db.commit()
cur.close()
return redirect(url_for('formula'))
except Exception as e:
raise e
def open_command(message):
cursor = db.cursor()
text = str(message.text.strip())
cursor.execute('select dispute_id, feedback_id from Messages WHERE chat_id={0} and message_id={1} limit 1'.format(
message.chat.id, message.reply_to_message.message_id))
rows = cursor.fetchall()
if not rows:
bot.send_message(message.chat.id, 'Ой! Вы что-то перепутали, тут не на что отвечать!',
reply_markup=markup_common)
cursor.close()
return
dispute_id, feedback_id = rows[0]
if dispute_id is not None:
step_type = 'd'
else:
cursor.execute('select is_answer from Feedback where id=?', [feedback_id])
is_answer, = cursor.fetchall()[0]
if is_answer:
step_type = 'a'
else:
step_type = 'c'
item_id = dispute_id or feedback_id
cursor.execute('update User set state=state||? WHERE id=?',
['{}{}/'.format(step_type, item_id), message.from_user.id])
db.commit()
cursor.close()
send_stuff_by_state(message.from_user.id)
def search_message(message):
cursor = db.cursor()
text = str(message.text).replace('/search', '').strip().lower().replace('/', '\\x2f')
cursor.execute('update User set state=state||? WHERE id=?',
['s({})/'.format(text), message.from_user.id])
db.commit()
cursor.close()
send_stuff_by_state(message.from_user.id)
def insert_packets(delete=False):
filename = sys.argv[1]
group_id = sys.argv[2]
upload_time = sys.argv[3]
upload_name = sys.argv[4]
cursor = db.cursor()
sql = "INSERT INTO DataGroup(ID, UPLOAD_TIME, UPLOAD_NAME, START_TIME)VALUES (" + group_id + ", '" + upload_time + "','" + upload_name + "', '" + "1000" + "')" # below 100s
cursor.execute(sql)
db.commit()
def insert_single_file(filename):
pcap = rdpcap(filename)[TCP]
for i in xrange(len(pcap)):
length = len(pcap[i])
try:
timestamp = pcap[i].time - int(
pcap[i].time) / 100 * 100 # below 100s
ip_src = pcap[i]['IP'].src
ip_dst = pcap[i]['IP'].dst
port_src = str(pcap[i]['IP'].sport)
port_dst = str(pcap[i]['IP'].dport)
apanel_cluster(pcap[i], group_id) # a-panel聚类
except:
continue
"""
<Ether dst=00:23:89:3a:8b:08 src=00:0e:c6:c2:5f:d8 type=0x800 |<IP version=4L ihl=5L tos=0x0 len=40 id=34019 flags=DF frag=0L ttl=64 proto=tcp chksum=0x9920 src=172.29.90.176 dst=42.156.235.98 options=[] |<TCP sport=47571 dport=https seq=3681001345 ack=1822908669 dataofs=5L reserved=0L flags=A window=65280 chksum=0x1ce7 urgptr=0 |>>>
"""
if bin(pcap[i][TCP].flags)[-1] == '1':
flag = 'Fin'
elif bin(pcap[i][TCP].flags)[-2] == '1':
flag = 'Syn'
else:
flag = ''
sql = "INSERT INTO Packets(GROUP_ID, TIMESTAMP,LENGTH,IP_SRC,IP_DST,PORT_SRC,PORT_DST,FLAG)VALUES (" + group_id + "," + str(
timestamp
) + "," + str(
length
) + ", '" + ip_src + "','" + ip_dst + "', '" + port_src + "', '" + port_dst + "', '" + flag + "')"
cursor.execute(sql)
db.commit()
if delete:
os.remove(filename)
if os.path.isfile(filename):
insert_single_file(filename)
elif os.path.isdir(filename):
files = os.listdir(filename)
total = len(files)
print '[loader] Total pcap files: {}'.format(total)
finished = 0
for each in files:
finished += 1
insert_single_file(os.path.abspath(os.path.join(filename, each)))
print '[loader] {}/{} finished.'.format(finished, total)
def get_all_users():
cur = db.cursor()
sql = "select email from user"
db.ping(reconnect=True)
cur.execute(sql)
result = cur.fetchall()
db.commit()
cur.close()
return result
def modify(sql, keys):
try:
with db:
cur = db.cursor()
cur.execute(sql, keys)
db.commit()
return 1
except:
return 0
def issue_detail(Ino):
if request.method == 'GET':
try:
if request.method == 'GET':
cur = db.cursor()
sql = "select Issue.title from Issue where Ino = '%s'" % Ino
db.ping(reconnect=True)
cur.execute(sql)
# 这里返回的是一个列表，即使只有一个数据，所以这里使用cur.fetchone()[0]
issue_title = cur.fetchone()[0]
sql = "select UserInformation.nickname,Comment.comment,Comment.comment_time,Comment.Cno from Comment,UserInformation where Comment.email = UserInformation.email and Ino = '%s'" % Ino
db.ping(reconnect=True)
cur.execute(sql)
comment = cur.fetchall()
cur.close()
# 返回视图，同时传递参数
return render_template('issue_detail.html',
Ino=Ino,
issue_title=issue_title,
comment=comment)
except Exception as e:
raise e
if request.method == 'POST':
Ino = request.values.get('Ino')
email = session.get('email')
comment = request.values.get('editorValue')
comment_time = time.strftime("%Y-%m-%d %H:%M:%S")
try:
cur = db.cursor()
sql = "select max(Cno) from Comment where Ino = '%s' " % Ino
db.ping(reconnect=True)
cur.execute(sql)
result = cur.fetchone()
Cno = int(result[0]) + 1
Cno = str(Cno)
sql = "insert into Comment(Cno, Ino, comment, comment_time, email) VALUES ('%s','%s','%s','%s','%s')" % (
Cno, Ino, comment, comment_time, email)
cur.execute(sql)
db.commit()
cur.close()
return redirect(url_for('issue_detail', Ino=Ino))
except Exception as e:
raise e
def reply_messages(message):
cursor = db.cursor()
text = str(message.text.strip())
cursor.execute('select dispute_id, feedback_id from Messages WHERE chat_id={0} and message_id={1} limit 1'.format(
message.chat.id, message.reply_to_message.message_id))
rows = cursor.fetchall()
if not rows:
bot.send_message(message.chat.id, 'Ты чот попутал, не на что отвечать)', reply_markup=markup_common)
cursor.close()
return
dispute_id, feedback_id = rows[0]
is_answer = dispute_id is not None
parent_id = dispute_id or feedback_id
first_line = text.splitlines()[0].strip()
if first_line == '+':
for_sign = True
elif first_line == '-':
for_sign = False
else:
for_sign = None
if for_sign is not None:
try:
content = text.split('\n', 1)[1].strip()
except IndexError:
content = None
else:
content = text
if is_answer and for_sign is not None:
bot.send_message(message.chat.id, 'Нельзя оценить вопрос.',
reply_markup=markup_common)
cursor.close()
return
params = [message.from_user.id, for_sign, parent_id, content, is_answer]
cursor.execute("insert into Feedback(user_id, for, parent_id, rating, content, is_answer) VALUES (?,?,?,0,?,?)",
params)
sent_message = bot.send_message(message.chat.id,
(('Ответ' if is_answer else 'Комментарий') + ' успешно создан') if content else (
'Ответ/комментарий успешно оценён'),
reply_markup=markup_common)
if not is_answer:
update_feedback_rating(feedback_id)
feedback_id = cursor.lastrowid
if content:
connect_message(message, feedback_id=feedback_id)
connect_message(sent_message, feedback_id=feedback_id)
cursor.execute('update User set state=state||? WHERE id=?',
['{}{}/'.format('a' if is_answer else 'c', cursor.lastrowid), message.from_user.id])
db.commit()
send_stuff_by_state(message.from_user.id)
cursor.close()
def edit_meeting_to_database(sql):
try:
cur = db.cursor()
db.ping(reconnect=True)
cur.execute(sql)
db.commit()
cur.close()
except Exception as e:
print(e)
def get_sponsor(sid):
try:
cur = db.cursor()
db.ping(reconnect=True)
sql = 'SELECT * FROM sponsors WHERE sponsor_id=%s' % sid
cur.execute(sql)
result = cur.fetchone()
return result
except Exception as e:
print(e)
def feed(message):
cursor = db.cursor()
cursor.execute('SELECT last_dispute_id FROM User WHERE id=?', [message.from_user.id])
user_last_id, = cursor.fetchall()[0]
cursor.execute('SELECT id FROM Dispute ORDER BY -id LIMIT 1;')
real_last_id, = cursor.fetchall()[0] # todo fix falling
cursor.execute('UPDATE User SET last_dispute_id=?,state=state||? WHERE id=?',
[real_last_id, 'f({}-{})/'.format(user_last_id, real_last_id), message.from_user.id])
db.commit()
cursor.close()
send_stuff_by_state(message.from_user.id)
def add_leave_to_leave_history(mid, email, processor):
try:
cur = db.cursor()
sql = "INSERT INTO leave_history(user_email, meeting_id, processor) VALUES ('%s',%s,'%s')" % (
email, mid, processor)
db.ping(reconnect=True)
cur.execute(sql)
db.commit()
cur.close()
except Exception as e:
print(e)
def add_sponsor(sql):
try:
cur = db.cursor()
db.ping(reconnect=True)
cur.execute(sql)
db.commit()
cur.close()
except Exception as e:
print(e)
def list_all_sponsors():
try:
cur = db.cursor()
sql = "select * from sponsors"
db.ping(reconnect=True)
cur.execute(sql)
result = cur.fetchall()
return result
except Exception as e:
print(e)
def list_meeting_of_user(user_email):
try:
cur = db.cursor()
sql = "select N.meeting_id,meeting_title,meeting_date,meeting_location from meeting AS M,need_to_meeting AS N where user_email='%s' and M.meeting_id = N.meeting_id" % user_email
db.ping(reconnect=True)
cur.execute(sql)
result = cur.fetchall()
return result
except Exception as e:
print(e)
def add_all_staff_to_meeting(id):
users = get_all_users()
sql = "INSERT INTO need_to_meeting (user_email,meeting_id) VALUES"
for email in users:
sql += "('%s',%s)," % (email[0], id)
sql = sql[:-1] + ";"
cur = db.cursor()
db.ping(reconnect=True)
cur.execute(sql)
db.commit()
cur.close()
def get_all_meeting_id():
try:
cur = db.cursor()
sql = "select meeting_id from meeting"
db.ping(reconnect=True)
cur.execute(sql)
result = cur.fetchall()
return result
except Exception as e:
print(e)
def formula():
if request.method == 'GET':
page = request.values.get('page')
if page is None:
page = int(1)
page = int(page)
try:
cur = db.cursor()
sql = "select count(*) from SDWZCS.formula_post"
db.ping(reconnect=True)
cur.execute(sql)
db.commit()
result = cur.fetchone()[0]
if result is None:
article_nums = 0
else:
article_nums = int(result)
page_num = int(article_nums / 5 + 0.9)
# 防止页码溢出
if page < 1:
page = int(1)
if page > page_num:
page = int(page_num)
if article_nums > 0:
sql = "select formula_id,title,creat_time,nickname from SDWZCS.formula_post, SDWZCS.userInformation where formula_post.author = userInformation.email order by formula_id DESC "
cur.execute(sql)
db.commit()
result = cur.fetchall()
formula_article = []
for iter in result:
sql = "select content from question_detail where formula_id = '%s' and qno = '1'" % iter[
0]
cur.execute(sql)
db.commit()
content = cur.fetchone()[0]
content = (content, )
formula_article.append(iter[:] + content[:])
# print(formula_article)
cur.close()
return render_template('formula.html',
article_nums=article_nums,
formula_article=formula_article,
page=page,
page_num=page_num)
else:
return render_template('formula.html',
article_nums=article_nums,
page=page,
page_num=page_num)
except Exception as e:
raise e
def start(message):
cursor = db.cursor()
try:
cursor.execute("insert into User(id,state) values ({0},'{1}')".format(message.from_user.id, '/'))
db.commit()
bot.send_message(message.from_user.id, 'Добро пожаловать! Введи /help, чтобы получить помощь')
except Exception as e:
if 'UNIQUE' in e.args[0]:
bot.send_message(message.from_user.id, 'С возвращением!')
else:
raise
cursor.close()
def approve_status(mid, email):
try:
cur = db.cursor()
sql = "update meeting_leave set status='Approved' " \
"where meeting_id=%s and user_email='%s'" % (mid, email)
db.ping(reconnect=True)
cur.execute(sql)
db.commit()
cur.close()
except Exception as e:
print(e)
def source():
if request.method == 'GET':
try:
cur = db.cursor()
sql = "select Fno,filename,file_info,file_time,nickname from Files,UserInformation where Files.email = UserInformation.email"
db.ping(reconnect=True)
cur.execute(sql)
files = cur.fetchall()
cur.close()
return render_template('source.html', files=files)
except Exception as e:
raise e
def show_issue():
if request.method == 'GET':
email = session.get('email')
try:
cur = db.cursor()
sql = "select ino, email, title, issue_time from Issue where email = '%s' order by issue_time desc" % email
db.ping(reconnect=True)
cur.execute(sql)
issue_detail = cur.fetchall()
except Exception as e:
raise e
return render_template('show_issue.html', issue_detail=issue_detail)
def personal():
if request.method == 'GET':
email = session.get('email')
try:
cur = db.cursor()
sql = "select email, nickname, type, create_time, phone from UserInformation where email = '%s'" % email
db.ping(reconnect=True)
cur.execute(sql)
personal_info = cur.fetchone()
except Exception as e:
raise e
return render_template('personal.html', personal_info=personal_info)
"""
@summary: A sample controller.
@author: Abhishek Shrivastava <i.abhi27 [at] gmail [dot] com>
"""
from config import db
from render import error
cur = db.cursor()
KEY_NAME = "name"
KEY_CITY = "city"
KEY_AGE = "age"
def output(httpin):
"""
Every controller must have this output function atleast. This is the entry point.
Input is a dict of POST/GET variables and any other extra keys added during URL parsing
Output must be a dict of Key-Value pairs that would be substituted in template using YAPTU.
"""
if KEY_NAME in httpin:
name = httpin[KEY_NAME]
else:
name = "Anonymous"
if KEY_CITY in httpin:
city = httpin[KEY_CITY]
else:
