def register():
openid = request.values.get('openid')
username = request.values.get('name')
id = wx_user.query.filter(wx_user.wx_id == openid).first()
if id:
return '该用户已注册'
else:
for k in r.scan_iter('wx_user*'):
r.delete(k)
user = wx_user(name=username,
wx_id=openid,
phone='无',
email='无',
password='******',
info_number=0,
control_power=0)
db.session.add(user)
db.session.commit()
for k in r.scan_iter('wx_user*'):
r.delete(k)
wxuser = wx_user.query.filter(wx_user.wx_id == openid).first()
id = wxuser.id
sql = 'select * from upload_img'
cursor.execute(sql)
image_id = cursor.fetchall()
for i in image_id:
unread = "insert into information(user_id,image_id,equip_id) VALUE (%s,%s,%s)" % (
id, i[0], i[4])
# unread = information(user_id=id,image_id=i[0],equip_id=i[4])
cursor.execute(unread)
database.commit()
return ''
def upload_count():
userid = request.form.get('userID')
user_name = r.get(userid)
wu = wx_user.query.filter(wx_user.name == user_name).first() #获取用户名
wxuser_id = wu.id #获取用户ID
img_count = request.form.get('imageCount')
equipId = request.form.get('equipID')
equipDesc = request.form.get('imageDesc')
equipTitle = request.form.get('imageTitle')
upload_data = upload_img(user_id=wxuser_id,number=img_count,equip_id=equipId,
image_desc=equipDesc,image_title=equipTitle) #将当前登录用户的id作为upload_img的user_id
db.session.add(upload_data)
db.session.commit()
upoload=upload_img.query.filter(upload_img.user_id==wxuser_id).order_by(upload_img.date.desc()).first()#获取刚刚上传图片数据的id
image_id=upoload.id#获取当前上传用户的id
sql='select id from wx_user'#查询所有用户的id
cursor.execute(sql)#执行SQL语句
user_id=cursor.fetchall()#获取所有用户的id
#使用for/in循环对所有用户添加未读数据
for (i,) in user_id:
if(i==wxuser_id):
continue
info_data=information(user_id=i,image_id=image_id,equip_id=equipId)#设备equip_id目前手动设置
db.session.add(info_data)
db.session.commit()
return ''
def wx_update_password():
password=request.values.get('password')
openid=request.values.get('openid')
sql = 'update wx_user set password="******" where wx_id="'+openid+'"'
cursor.execute(sql)
database.commit()
return jsonify({'status':'success'})
def update_info(self):
upload_data = upload_img(
user_id=self.user_id,
number=self.number,
equip_id=self.equip_id,
image_desc=self.description,
image_title=self.theme) # 将当前登录用户的id作为upload_img的user_id
db.session.add(upload_data)
db.session.commit()
# r.delete('eqimfo'+str(self.equip_id))
upoload = upload_img.query.filter(
upload_img.user_id == self.user_id).order_by(
upload_img.date.desc()).first() # 获取刚刚上传图片数据的id
image_id = upoload.id # 获取当前上传用户的id
sql = 'select id from wx_user' #查询所有用户的id
cursor.execute(sql) #执行SQL语句
alluser_id = cursor.fetchall() #获取所有用户的id
# 使用for/in循环对所有用户添加未读数据
for (i, ) in alluser_id:
if (i == self.user_id):
continue
info_data = information(user_id=i,
image_id=image_id,
equip_id=self.equip_id) # 设备equip_id目前手动设置
db.session.add(info_data)
db.session.commit()
def change_phones():
userid = request.form.get('userID')
# oldPhone = request.form.get('oldPhone')
newPhone = request.form.get('newPhone')
mailcode = request.form.get('mailcode')
usermail = request.form.get('userMail') #用户接收验证码的邮箱
codeKey = "" + usermail + ":code"
code = r.get(codeKey)
if mailcode == '%s' % code:
if len(r.keys('wx_user*')) != 0:
for k in r.scan_iter('wx_user*'):
r_username = r.hget(k, 'name')
if r.get(userid) == r_username:
user_infor = {'phone': newPhone}
r.hmset(k, user_infor)
break
sql = 'update wx_user set phone="' + newPhone + '" where name="' + r.get(
userid) + '"'
cursor.execute(sql)
database.commit()
return ("0")
else:
return ("1")
def get_imagedata():
if len(r.keys('upload_img*')) == 0:
sql='select distinct * from v_userImgs'
cursor.execute(sql)
user_data=cursor.fetchall()
fields = cursor.description
column_list = []
json_list = []
for i in fields:
column_list.append(i[0])
for row in user_data:
data = {}
for i in range(len(column_list)):
data[column_list[i]] = row[i]
json_list.append(data)
imgsData = json.dumps(json_list,ensure_ascii=False,cls=CJsonEncoder)
for i in range(len(user_data)):
imgs_infor = {'name':user_data[i][0],'id':user_data[i][1],'user_id':user_data[i][2],'number':user_data[i][3],
'date':user_data[i][4].strftime("%Y-%m-%d %H:%M:%S"),'equip_id':user_data[i][5],'image_desc':user_data[i][7],'image_title':user_data[i][6]}
r.hmset('upload_img:%d'%(user_data[i][1]),imgs_infor)
r.expire('upload_img:%d'%(user_data[i][1]),432000)
return imgsData
else:
imgsdata = []
for k in r.scan_iter('upload_img*'):
data = r.hgetall(k)
imgsdata.append(data)
imgsdata = tuple(imgsdata)
imgsdata = json.dumps(imgsdata,ensure_ascii=False,cls=CJsonEncoder)
return imgsdata
def wx_get_equip_data():
openid = request.values.get('openid')
wu = wx_user.query.filter(wx_user.wx_id == openid).first() #获取用户名
wxuser_id = wu.id #获取用户ID
equip_unread = []
database.ping(reconnect=True)
counter_lock2 = threading.Lock()
counter_lock2.acquire()
sql = 'select id from equipment'
cursor.execute(sql)
# try:
# cursor.execute(sql)
# except:
# database.ping(reconnect=True)
# cursor.execute(sql)
data=cursor.fetchall()
for (i,) in data:
sql='select count(*) from information where user_id=%s and equip_id=%s'%(wxuser_id,i)
cursor.execute(sql)
one=cursor.fetchall()
for (i,) in one:
equip_unread.append(i)
counter_lock2.release()
equip_unread_data=json.dumps(equip_unread)
return equip_unread_data
def get_is_data():
userid = request.args.get('userID')
eq_id = request.args.get('equipID')
user_name = r.get(userid)
wu = wx_user.query.filter(wx_user.name == user_name).first() #获取用户名
wxuser_id = wu.id #获取用户ID
sql='select image_id from information where user_id=%s and equip_id=%s' %(wxuser_id,eq_id)
cursor.execute(sql)
image_id=cursor.fetchall()
all_id=[]
unread_id=[]
for (i,) in image_id:
unread_id.append(i)
imgs_id = srch_hash_data('upload_img','equip_id',eq_id)
for i in imgs_id:
all_id.append(i)
# 将所有的图片id减去未读图片的id获得已读图片的ID
is_id=set(all_id)-set(unread_id)
read_arr=[]
for i in is_id:
read_data = srch_hash_data('upload_img','id',str(i))
read_arr.append(read_data[0][0])
read_arr.append(read_data[0][1])
read_arr.append(read_data[0][2])
json_data=json.dumps(read_arr,cls=CJsonEncoder)
return json_data
def search_image():
imgsid = request.args.get('id')
# imgsData = srch_hash_data('imgs','id',imgsid)
# return imgsData
sql = 'select image_desc,image_title,file_path,data_path from v_upImgs where upload_id=%s;' % imgsid
cursor.execute(sql)
results = cursor.fetchall()
data = json.dumps(results, cls=CJsonEncoder)
return data
def get_personal():
openid = request.values.get('openid')
database.ping(reconnect=True)
counter_lock2 = threading.Lock()
counter_lock2.acquire()
sql = "select * from wx_user where wx_id = '" + openid + "'"
cursor.execute(sql)
data = cursor.fetchall()
counter_lock2.release()
json_data = json.dumps(data, cls=CJsonEncoder)
return json_data
def get_deserve():
exist = r.exists('eq_data')
if exist==0:
sql = 'select *from equipment'
cursor.execute(sql)
user_data = cursor.fetchall()
json_data = json.dumps(user_data, cls=CJsonEncoder)
r.set('eq_data',json_data)
else:
json_data=r.get('eq_data')
return json_data
def del_unread_data():
userid = request.args.get('userID')
infor_id = request.args.get('inforID')
read_flag = request.args.get('read_flag')
if read_flag == 'unread_id':
user_name = r.get(userid)
wu = wx_user.query.filter(wx_user.name == user_name).first() #获取用户名
wxuser_id = wu.id #获取用户ID
sql='delete from information where image_id=%s and user_id=%s' %(infor_id,wxuser_id)
cursor.execute(sql)
database.commit()
data = srch_hash_data('imgs','id',infor_id)
return data
def wx_del_unreaddata():
openid = request.values.get('openid')
wxuser=wx_user.query.filter(wx_user.wx_id==openid).first()
wxuser_id=wxuser.id
id=request.values.get('id')
sql='delete from information where image_id=%s and user_id=%s' %(id,wxuser_id)
try:
cursor.execute(sql)
except:
database.ping(reconnect=True)
cursor.execute(sql)
database.commit()
return ''
def wx_pictureContent():
id=request.values.get('id')
sql='select * from upload_img where id=%s' %id
try:
cursor.execute(sql)
except:
database.ping()
cursor.execute(sql)
content=cursor.fetchall()
json_data = json.dumps(content, cls=CJsonEncoder)
return json_data
def user_verify():
userphone = request.form.get('userphone')
pwd = request.form.get('password')
remem_me = request.form.get('remember_me')
phone_mail = '\'%s\'' % userphone
password = '******'%s\'' % pwd
sql = 'select id from wx_user where phone=%s and password=%s or email=%s and password=%s' %(phone_mail,password,phone_mail,password)
cursor.execute(sql)
user_id = cursor.fetchall()
data = json.dumps(user_id)
if len(data) < 5:
return '0'
else:
return data
def get_equip_data():
userid = request.args.get('userID')
user_name = r.get(userid)
wu = wx_user.query.filter(wx_user.name == user_name).first() #获取用户名
wxuser_id = wu.id #获取用户ID
equip_unread=[]
data = r.sort('equip:id',by='equipment:*->id', get=['equipment:*->id'])
for equip_id in data:
sql='select count(*) from information where user_id=%s and equip_id=%s'%(wxuser_id,equip_id)
cursor.execute(sql)
one=cursor.fetchall()
for (i,) in one:
equip_unread.append(i)
equip_unread_data=json.dumps(equip_unread)
return equip_unread_data
def ctreate_ThreadPool(self):
recived = self.ws.receive()
if not recived is None:
# 接收传过来的用户名，设备id，时间戳，，图片数量，主题，描述
rec = str(recived, encoding="utf8")
# 此处为你需要传过来用户id，设备id，时间戳，图片数量，实验主题和实验描述
username, pwd, equip_code, t, number, theme, description, resume = rec.split(
SEPARATOR)
u_name = '\'%s\'' % username
u_pwd = '\'%s\'' % pwd
sql = 'select id from wx_user where name=%s and password=%s' % (
u_name, u_pwd)
cursor.execute(sql)
user_id = cursor.fetchall()
data = json.dumps(user_id)
if len(data) < 5:
self.ws.send("登录失败")
else:
self.ws.send("核对成功")
equip = equipment.query.filter(
equipment.equip_code ==
equip_code).first() # 获取刚刚上传图片数据的id
equip_id = equip.id
number = int(number) # 将number转为int型
###############此处直接调用信息处理类来处理信息############################
if resume == 'False':
d = Data_deal(user_id, number, equip_id, description,
theme)
d.update_info()
for j in r.scan_iter('imgs*'):
r.delete(j)
for k in r.scan_iter('upload_img*'):
r.delete(k)
for i in r.scan_iter("eqimfo*"):
r.delete(i)
threadPool = ThreadPoolExecutor(
max_workers=number, thread_name_prefix="pro") # 根据用户上传的文件
# 一个用户来访问就会开一个线程，然后根据他传过来的图片数量，开启线程池读取文件
for i in range(number):
future = threadPool.submit(self.re(username, user_id,
t)) # j将线程提交到线程池
time.sleep(3)
threadPool.shutdown()
print('接收完!')
def findloss():
usermail = request.form.get('userMail')
sql = 'select *from wx_user where email="' + usermail + '"'
cursor.execute(sql)
data = cursor.fetchall()
if len(data) == 0:
print('发送失败')
return ("0")
else:
msg = Message('帐号密码找回',
sender='*****@*****.**',
recipients=[usermail])
msg.body = 'sended by flask-email'
msg.html = '<b>您的帐号为%s，密码为%s<b>' %(data[0][1],data[0][4])
thread = Thread(target=send_async_email, args=[app, msg])
thread.start()
return ("1")
def getimformation():
Eqid = request.values.get('eqid')
Unread = request.values.get('unread_id')
database.ping(reconnect=True)
counter_lock2 = threading.Lock()
counter_lock2.acquire()
if Unread == '[]':
sql = 'select * from upload_img where equip_id=%s order by date desc' % Eqid
cursor.execute(sql)
data = cursor.fetchall()
json_data=json.dumps(data,cls=CJsonEncoder)
return json_data
else:
Unread=Unread[1:-1]
sql = 'select *from upload_img where not id in (%s) ORDER BY date desc;' % Unread
cursor.execute(sql)
data=cursor.fetchall()
json_data=json.dumps(data,cls=CJsonEncoder)
return json_data
def wx_update_mail():
openid=request.values.get('openid')
newmail = request.form.get('newmail')
mailcode = request.form.get('mailcode')
codeKey = ""+ newmail +":code"
code = r.get(codeKey)
isveri=request.form.get('isveri')
if isveri:
if mailcode =='%s' % code:
return jsonify({'status':'success'})
else:
return jsonify({'status':'fail'})
else:
if mailcode =='%s' % code:
sql = 'update wx_user set email="'+newmail+'" where wx_id="'+openid+'"'
cursor.execute(sql)
database.commit()
return jsonify({'status':'success'})
else:
return jsonify({'status':'fail'})
def changedata():
userid = request.args.get('id')
name = request.args.get('name')
phone = request.args.get('phone')
email = request.args.get('email')
u_name = '\'%s\'' % name
u_phone = '\'%s\'' % phone
u_mail = '\'%s\'' % email
if len(r.keys('wx_user*')) != 0:
for k in r.scan_iter('wx_user*'):
r_userid = r.hget(k, 'id')
if userid == r_userid:
print("更新缓存")
user_infor = {'name': name, 'phone': phone, 'email': email}
r.hmset(k, user_infor)
sql = 'update wx_user set name=%s,phone=%s,email=%s where id=%s' % (
u_name, u_phone, u_mail, userid)
cursor.execute(sql)
database.commit()
json_data = srch_hash_data('wx_user', 'init', 'None')
return json_data
def ctreate_ThreadPool(self):
recived = self.ws.receive()
if not recived is None:
rec = str(recived, encoding="utf8")
equip_code, t, number, resume = rec.split(SEPARATOR)
# 接收传过来的用户名，设备id，时间戳，，图片数量，主题，描述
equip = equipment.query.filter(
equipment.equip_code == equip_code).first() # 获取刚刚上传图片数据的id
equip_id = str(equip.id)
sql = 'select user_id from upload_img where equip_id=%s and number<%s' % (
equip_id, 30)
cursor.execute(sql)
data = cursor.fetchall()
print(data, type(data))
user_id = data[0][0]
user = wx_user.query.filter(
wx_user.id == user_id).first() # 获取刚刚上传图片数据的id
username = user.name
self.ws.send("核对成功")
###############此处直接调用信息处理类来处理信息############################
if resume == 'False':
d = Data_deal(number, equip_id)
d.update_info()
for j in r.scan_iter('imgs*'):
r.delete(j)
for k in r.scan_iter('upload_img*'):
r.delete(k)
for i in r.scan_iter("eqimfo*"):
r.delete(i)
number = int(number) # 将number转为int型
threadPool = ThreadPoolExecutor(
max_workers=number, thread_name_prefix="pro") # 根据用户上传的文件
# 一个用户来访问就会开一个线程，然后根据他传过来的图片数量，开启线程池读取文件
for i in range(number):
future = threadPool.submit(self.re(username, user_id,
t)) # j将线程提交到线程池
time.sleep(3)
threadPool.shutdown()
print('接收完!')
def get_userdata():
if len(r.keys('wx_user*')) == 0:
sql = 'select * from wx_user'
cursor.execute(sql)
user_data = cursor.fetchall()
fields = cursor.description
column_list = []
json_list = []
for i in fields:
column_list.append(i[0])
for row in user_data:
data = {}
for i in range(len(column_list)):
data[column_list[i]] = row[i]
json_list.append(data)
userData = json.dumps(json_list,
ensure_ascii=False,
cls=CJsonEncoder)
for i in range(len(user_data)):
user_infor = {
'id': user_data[i][0],
'name': user_data[i][1],
'phone': user_data[i][2],
'email': user_data[i][3],
'password': user_data[i][4],
'wx_id': user_data[i][5]
}
r.hmset('wx_user:%d' % (user_data[i][0]), user_infor)
r.expire('wx_user:%d' % (user_data[i][0]), 86400)
return userData
else:
userdata = []
for k in r.scan_iter('wx_user*'):
data = r.hgetall(k)
userdata.append(data)
userdata = tuple(userdata)
userdata = json.dumps(userdata, ensure_ascii=False, cls=CJsonEncoder)
return userdata
def upload_infor(user_id,theme,desc,code):
equip = equipment.query.filter(equipment.equip_code == code).first() # 获取刚刚上传图片数据的id
equip_id = equip.id
upload_data = upload_img(user_id=user_id, number=0,equip_id=equip_id,
image_desc=desc, image_title=theme) # 将当前登录用户的id作为upload_img的user_id
db.session.add(upload_data)
db.session.commit()
upoload = upload_img.query.filter(
upload_img.user_id == user_id).order_by(upload_img.date.desc()).first() # 获取刚刚上传图片数据的id
image_id = upoload.id # 获取当前上传用户的id
sql='select id from wx_user'#查询所有用户的id
cursor.execute(sql)#执行SQL语句
alluser_id=cursor.fetchall()#获取所有用户的id
# 使用for/in循环对所有用户添加未读数据
for (i,) in alluser_id:
info_data = information(user_id=i, image_id=image_id, equip_id=equip_id) # 设备equip_id目前手动设置
db.session.add(info_data)
db.session.commit()
def check_user():
userphone = request.form.get('userphone')
pwd = request.form.get('password')
code = request.form.get('code')
remem_me = request.form.get('remember_me')
phone_mail = '\'%s\'' % userphone
password = '******'%s\'' % pwd
ran_char = ''.join(random.sample(string.ascii_letters + string.digits,
6)) #新增加的
# if len(r.keys('wx_user*')) == 0:
sql = 'select name from wx_user where phone=%s and password=%s or email=%s and password=%s' % (
phone_mail, password, phone_mail, password)
cursor.execute(sql)
userdata = cursor.fetchall()
# userdata = userdata[0][0]
# else:
# print("查询缓存")
# userdata = ()
# for k in r.scan_iter('wx_user*'):
# verdata = r.hmget(k,'phone','email','password')
# if userphone == (verdata[0] or verdata[1]) and pwd == verdata[2]:
# userdata = r.hmget(k,'name')
data = json.dumps(userdata)
verify_code = session.get('imageCode')
print(verify_code)
if code != verify_code.lower():
return ("0")
if len(data) < 5:
return ("1")
else:
json_list = []
json_list.append(ran_char)
json_list.append(userdata[0][0])
username = json.dumps(json_list)
# if not remem_me is None:
r.setex(ran_char, 604800, userdata[0][0])
return username
def export_data(content,txtid):
# imgsData = srch_hash_data('imgs','id',imgsid)
# return imgsData
sql = 'select data_path from v_upImgs where upload_id=%s;' %txtid
cursor.execute(sql)
results = cursor.fetchall()
fullfilelist = results
dl_name = 'sample.zip'
# #普通下载
# response = make_response(send_from_directory(filepath, filename, as_attachment=True))
# response.headers["Content-Disposition"] = "attachment; filename={}".format(filepath.encode().decode('latin-1'))
if len(results) == 1:
fullfilenamelist = results[0][0].split('/')
filename = fullfilenamelist[-1]
filepath = results[0][0].replace('/%s'%filename, '')
return send_from_directory(filepath, filename, as_attachment=True)
else:
memory_file = BytesIO()
with zipfile.ZipFile(memory_file, "w", zipfile.ZIP_DEFLATED) as zf:
for _file in fullfilelist:
with open(_file[0], 'rb') as fp:
zf.writestr(_file[0].split('/')[-1], fp.read())
memory_file.seek(0)
return send_file(memory_file, attachment_filename=dl_name, as_attachment=True)
def searchtime():
types = request.args.get('types')
time = request.args.get('time')
time = '\'%s\'' % time
if types == "wx_user":
sql = 'select * from wx_user where date(date)=%s' % time
elif types == "upload_img":
sql='select distinct * from v_userImgs where date(date)=%s' % time
else:
sql = 'select *from logs where date(date)=%s' % time
cursor.execute(sql)
user_data=cursor.fetchall()
fields = cursor.description
column_list = []
json_list = []
for i in fields:
column_list.append(i[0])
for row in user_data:
data = {}
for i in range(len(column_list)):
data[column_list[i]] = row[i]
json_list.append(data)
alldata = json.dumps(json_list, cls=CJsonEncoder)
return alldata
def wx_get_data():
openid = request.values.get('openid')
eqid=request.values.get('eqid')
page = int(request.values.get('page'))
showList = int(request.values.get('showList'))
page_x = (page - 1) * showList
page_y=page*showList
wxuser=wx_user.query.filter(wx_user.wx_id==openid).first()
wxuser_id = wxuser.id
#获取未读信息数量
sql = 'select count(*) from information where user_id=%s and equip_id=%s' % (wxuser_id, eqid)
cursor.execute(sql)
unread_num = cursor.fetchall()
#去除tuple数组的括号，例：将(1,)变成1
for (i,) in unread_num:
unread_num=i
#获取未读信息id
sql='select image_id from information where user_id=%s and equip_id=%s' %(wxuser_id,eqid)
try:
cursor.execute(sql)
except:
database.ping(reconnect=True)
cursor.execute(sql)
image_id = cursor.fetchall()
unreadId=''
if image_id==():
return ''
else:
for (i,) in image_id:
unreadId += '%s,' % i
unreadId = unreadId[0:-1]
#联合查询，先查询未读信息再查询已读信息，分别加上不同的字段区分已读未读
sql='(select *,false as state, \'red\' as color from upload_img where id in (%s) ORDER BY date desc LIMIT %s)\
UNION ALL\
(select *,true,\'green\' from upload_img where not id in (%s) ORDER BY date desc LIMIT 100) limit %s,%s;'%(unreadId,unread_num,unreadId,page_x,page_y)
cursor.execute(sql)
unread_data = cursor.fetchall()
json_data=json.dumps(unread_data,cls=CJsonEncoder)
return json_data
def wx_upload_count():
wxuser=wx_user.query.filter(wx_user.wx_id==openid).first() #获取用户名
wxuser_id=wxuser.id #将用户id当成上传id
img_count=request.values.get('img_count')
equip_id=request.values.get('equip_id')
content=request.values.get('content')
tittle=request.values.get('tittle')
# upload_data=upload_img(user_id=wxuser.id,number=img_count)#将当前用户的wx_user.id作为upload_img的user_id
upload_data=upload_img(user_id=wxuser_id,number=img_count,equip_id=equip_id,image_desc=content,image_title=tittle)
db.session.add(upload_data)
db.session.commit()
upoload=upload_img.query.filter(upload_img.user_id==wxuser_id).order_by(upload_img.date.desc()).first()#获取刚刚上传图片数据的id
image_id=upoload.id#获取当前上传用户的id
sql='select id from wx_user'#查询所有用户的id
try:
cursor.execute(sql)
except:
database.ping(reconnect=True)
cursor.execute(sql)
user_id=cursor.fetchall()#获取所有用户的id
#使用for/in循环对所有用户添加未读数据
for (i,) in user_id:
if(i==wxuser_id):
continue
info_data=information(user_id=i,image_id=image_id,equip_id=equip_id)#设备equip_id目前手动设置
db.session.add(info_data)
db.session.commit()
#将对应的设备未读信息数加一
sql='update wx_user set info_number=info_number+1 where id=%s' %i
cursor.execute(sql)
database.commit()
return ''
def update_info(self):
sql = 'update upload_img set number="' + self.number + '" where equip_id="' + self.equip_id + '" and number=0'
cursor.execute(sql)
database.commit()
