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_logs(): userID = request.args.get('id') sql = 'select logs_type.logs_typename,logs.beinfo,logs.afinfo from logs_type left join logs on logs_type.id=logs.logs_typeid where logs.id=%s' % userID cursor.execute(sql) logs_data = cursor.fetchall() json_data = json.dumps(logs_data, cls=CJsonEncoder) return json_data
def getimformation(): Eqid = request.values.get('eqid') database.ping(reconnect=True) counter_lock2 = threading.Lock() counter_lock2.acquire() exist = r.exists("eqimfo" + Eqid) if exist == 0: sql = 'select * from upload_img where equip_id=%s order by date desc' % Eqid try: cursor.execute(sql) except: database.ping(reconnect=True) cursor.execute(sql) data = cursor.fetchall() message = [] y = [] for i in data: wxuser = wx_user.query.filter(wx_user.id == i[1]).first() name = wxuser.name y.append(i[0]) y.append(name) y.append(i[2]) y.append(i[3]) y.append(i[5]) y.append(i[6]) message.append(y) y = [] json_data = json.dumps(message, cls=CJsonEncoder) counter_lock2.release() r.set("eqimfo" + Eqid, json_data) return json_data else: return r.get("eqimfo" + Eqid)
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) 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 equip_infor(): received = request.get_data() equipInfor = received.decode(encoding="utf8", errors='ignore') equip_name, equip_disc, if_work, equip_adr, equip_ip, equip_posi = equipInfor.split( ",") equip = equipment.query.filter(equipment.equip_name == equip_name).first() if not equip: equip_data = equipment(equip_name=equip_name, equip_disc=equip_disc, if_work=if_work, equip_adr=equip_adr, equip_ip=equip_ip, equip_posi=equip_posi, equip_image=0) db.session.add(equip_data) db.session.commit() else: if_work = '\'%s\'' % if_work equip_name = '\'%s\'' % equip_name sql = 'update equipment set if_work=%s where equip_name=%s' % ( if_work, equip_name) cursor.execute(sql) database.commit() return 'jieshoucg'
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=0, image_desc=self.desc, 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 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 equip_work(): received = request.get_data() equipStat = bytes.decode(received) equip_name, if_work = equipStat.split(',') if_work = '\'%s\'' % if_work equip_name = '\'%s\'' % equip_name sql = 'update equipment set if_work=%s where equip_name=%s' % (if_work, equip_name) cursor.execute(sql) database.commit() return 'jieshoucg'
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 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 reviewVideo(reviewId): reviewType, reviewId = reviewId.split('=') sql = 'select image_desc,image_title,data_path from v_upImgs where upload_id=%s;' % reviewId cursor.execute(sql) data = cursor.fetchall() picsData = [] for i in range(len(data)): picsInfor = { 'image_desc': data[i][0], 'image_title': data[i][1], 'data_path': data[i][2] } picsData.append(picsInfor) json_data = json.dumps(picsData) return json_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 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 get_logsdata(): sql = 'select *from logs' cursor.execute(sql) logs_data = cursor.fetchall() fields = cursor.description column_list = [] json_list = [] for i in fields: column_list.append(i[0]) for row in logs_data: data = {} for i in range(len(column_list)): data[column_list[i]] = row[i] json_list.append(data) logsdata = json.dumps(json_list, ensure_ascii=False, cls=CJsonEncoder) return logsdata
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() fullfilename = results[0][0] # fullfilename = '/root/allfile/123.txt' fullfilenamelist = fullfilename.split('/') filename = fullfilenamelist[-1] filepath = fullfilename.replace('/%s' % filename, '') #普通下载 response = make_response( send_from_directory(filepath, filename, as_attachment=True)) response.headers["Content-Disposition"] = "attachment; filename={}".format( filepath.encode().decode('latin-1')) return send_from_directory(filepath, filename, as_attachment=True)
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 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_unread_data(): openid = request.values.get('openid') eqid = request.values.get('eqid') wxuser = wx_user.query.filter(wx_user.wx_id == openid).first() wxuser_id = wxuser.id # image_id = information.query.filter(information.user_id==wxuser_id,information.equip_id==eqid).all() 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() if image_id == (): return '' else: unread_arr = [] for (i, ) in image_id: sql = 'select id from upload_img where id=%s' % i cursor.execute(sql) unread_data = cursor.fetchall() unread_arr.append(unread_data[0][0]) json_data = json.dumps(unread_arr, cls=CJsonEncoder) return json_data
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 change_emails(): userid = request.form.get('userID') newmail = request.form.get('newMail') 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 = {'email': newmail} r.hset(k, user_infor) sql = 'update wx_user set email="' + newmail + '" where name="' + r.get( userid) + '"' cursor.execute(sql) database.commit() return ("0") else: return ("1")
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 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') if len(data) < 5: return ("1") if (verify_code is None) or (code != verify_code.lower()): return ("0") 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 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 getlogsdata(): sql = 'select *from logs' cursor.execute(sql) logs_data = cursor.fetchall() json_data = json.dumps(logs_data, cls=CJsonEncoder) return json_data