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_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 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 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 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