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 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 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 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 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 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 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 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 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 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 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 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 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_getEqm(): sql = 'select equip_name,equip_code from equipment' cursor.execute(sql) data = cursor.fetchall() json_data=json.dumps(data,cls=CJsonEncoder) return json_data