def get_devices(self, order_tag):
devices = None
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return []
try:
cur = conn.cursor(tornado_mysql.cursors.DictCursor)
yield cur.execute(_getdevice_sql, (order_tag))
rows = cur.fetchall()
devices = [x.get("sn", "") for x in rows]
cur.close()
#return devices
except Exception as e:
logging.error("db oper failed {0}".format(e))
return []
finally:
conn.close()
#get user id
if not devices:
return None
list_sn = "("
for item in devices:
list_sn = list_sn + item + ','
list_sn = list_sn[:-1]
list_sn = list_sn + ")"
format_sql = _getdevice_userid_sql % list_sn
logging.info("format sql %s " % format_sql)
conn = yield get_mysqlcon('mxsuser')
if not conn:
logging.error("connect to mysql failed")
return []
try:
cur = conn.cursor(tornado_mysql.cursors.DictCursor)
yield cur.execute(format_sql)
rows = cur.fetchall()
device_ids = [x.get("userEntity_userID", "") for x in rows]
cur.close()
return device_ids
except Exception as e:
logging.error("db oper failed {0}".format(e))
return []
finally:
conn.close()
def check_bindcount(deviceid, bindnum):
conn = yield get_mysqlcon('mxsuser')
if not conn:
logging.error("connect to mysql failed")
return False
try:
cur = conn.cursor(tornado_mysql.cursors.DictCursor)
yield cur.execute(_checkadmin_sql, (deviceid, 'USER'))
rows = cur.fetchall()
cur.close()
if not rows:
logging.info("no user was bound to %s" % deviceid)
return True
if (len(rows) + bindnum) > 5:
logging.info("too many user was bound to %s" % deviceid)
return False
return True
except Exception as e:
logging.error("oper db failed {0}".format(e))
return False
finally:
conn.close()
return False
def do(self, userid, data):
if not data or not userid:
return (403, None)
sid = data.get("id", "")
if not sid:
logging.error("invalid parameter")
return (403, None)
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return (500, None)
rst_code = 200
try:
cur = conn.cursor()
yield cur.execute(_sql_remove_dispatch, (sid))
yield cur.execute(_sql_remove_device, (sid))
cur.close()
yield conn.commit()
except Exception as e:
logging.error("insert db failed {0}".format(e))
rst_code = 500
finally:
conn.close()
return (rst_code, None)
def check_admin(userid, deviceid):
conn = yield get_mysqlcon('mxsuser')
if not conn:
logging.error("connect to mysql failed")
return False
try:
cur = conn.cursor(tornado_mysql.cursors.DictCursor)
yield cur.execute(_checkadmin_sql, (deviceid, 'ADMIN'))
admin = cur.fetchone()
cur.close()
if not admin:
logging.error("device %s not found" % deviceid)
return False
adminid = str(admin.get("userEntity_userID", ""));
if userid != adminid:
logging.error("the user %s is not the owner of %s it is %s" % (userid, deviceid, adminid))
return False
return True
except Exception as e:
logging.error("oper db failed {0}".format(e))
return False
finally:
conn.close()
return False
def saveudp(self, udprp, taskid, s_current):
tb = udprp.get("tb", "")
direct = udprp.get("direct", "").lower()
jitter = udprp.get("jitter", "")
lose = udprp.get("lose", "")
db_direct = "1"
if direct == "up":
db_direct = "0"
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return
try:
cur = conn.cursor()
format_sql = _insertudp_sql % (taskid, self.p_userid, s_current, tb, db_direct, jitter, lose)
yield cur.execute(format_sql)
cur.close()
yield conn.commit()
except Exception as e:
logging.error("db oper failed {0}".format(e))
finally:
conn.close()
def read_auth_fromdb(self, role):
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return
auths = []
try:
cur = conn.cursor(tornado_mysql.cursors.DictCursor)
q_sql = _auth_sqlquery % role
yield cur.execute(q_sql)
rows = cur.fetchall()
for item in rows:
auths.append(item["authority"])
cur.close()
except Exception as e:
logging.error("db oper failed {0}".format(e))
finally:
conn.close()
return auths
def filter_mydevice(userid, members):
if not members:
return
conn = yield get_mysqlcon('mxsuser')
if not conn:
logging.error("connect to mysql failed")
return False
mem_list = "("
for item in members:
mem_list = mem_list + item + ','
mem_list = mem_list[:-1]
mem_list = mem_list + ")"
try:
cur = conn.cursor(tornado_mysql.cursors.DictCursor)
format_sql = _filtermydevice_sql % (userid, mem_list)
yield cur.execute(format_sql)
rows = cur.fetchall()
mylist = []
for item in rows:
dev_id = str(item.get("device_userID", ""))
if not dev_id in mylist:
mylist.append(dev_id)
cur.close()
return mylist
except Exception as e:
logging.error("oper db failed {0}".format(e))
return False
finally:
conn.close()
def update_user(userid, userinfo):
#format sql
formated_userinfo = {}
for key, value in userinfo.items():
formated_key = _userkeymap.get(key, "")
if formated_key:
formated_userinfo[formated_key] = value
else:
formated_userinfo[key] = value
update_s = ""
for key, value in formated_userinfo.items():
update_s += "%s = '%s'," % (key, value)
update_s = update_s[:-1]
conn = yield get_mysqlcon('mxsuser')
if not conn:
logging.error("connect to mysql failed")
return False
try:
cur = conn.cursor()
format_sql = _updateuser_sql % (update_s, userid)
yield cur.execute(format_sql)
cur.close()
yield conn.commit()
mickey.redis.remove_from_redis(REDIS_CONTACT_PREFIX + userid)
return True
except Exception as e:
logging.error("oper db failed {0}".format(e))
finally:
conn.close()
return False
def put(self, feedid, filename, suffix):
if not feedid or not filename or not suffix:
logging.error("filename was not set")
self.set_status(403)
self.finish()
return
logging.info("begin to receive file %s" % filename)
format_suffix = suffix.lower()
format_filename = feedid + "_" + filename + '.' + format_suffix
filepath = self.application.loadpath + feedid[-1] + '/' + format_filename
with open(filepath, "wb") as out:
out.write(bytearray(self.request.body))
filesize = os.stat(filepath).st_size
if filesize > 4200000:
logging.error("file size exceed the limit, filename = %s, size = %d" % (filename, filesize))
os.remove(filepath)
self.set_status(403)
self.finish()
return
db_filename = filepath.replace('/mntdata/feedback', '')
#update db sef filename list
updateFailed = False
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
self.set_status(500)
self.finish()
return
try:
cur = conn.cursor()
fileType = 0
if not format_suffix in _invalid_pic_suffix:
fileType = 1
yield cur.execute(_insert_file_sql, (feedid, db_filename, fileType))
cur.close()
yield conn.commit()
except Exception as e:
logging.error("db oper failed {0}".format(e))
updateFailed = True
finally:
conn.close()
if updateFailed:
self.set_status(500)
self.finish()
return
self.set_status(200)
self.finish()
def do(self, userid, data):
if not data:
return (403, None)
sid = data.get("id", "")
if not sid:
logging.error("invalid parameter no id")
return (403, None)
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return (500, None)
rst_code = 200
try:
cur = conn.cursor()
yield cur.connection.autocommit(True)
yield cur.execute(_sqlquery, (MICKEY_ORDER_STAGE_FINAL, MICKEY_DISPATCH_STAGE_FINAL, sid))
print(_sqlquery % (MICKEY_ORDER_STAGE_FINAL, MICKEY_DISPATCH_STAGE_FINAL, sid))
cur.close()
except Exception as e:
logging.error("db oper failed {0}".format(e))
rst_code = 500
finally:
conn.close()
return (rst_code, None)
def update_order_state(self, sid, state):
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return
try:
cur = conn.cursor()
yield cur.connection.autocommit(True)
yield cur.execute(_sql_status_change, (state, sid))
cur.close()
except Exception as e:
logging.error("db oper failed {0}".format(e))
finally:
conn.close()
def get_myusedevices(phone):
conn = yield get_mysqlcon('mxsuser')
if not conn:
logging.error("connect to mysql failed")
return []
try:
cur = conn.cursor(tornado_mysql.cursors.DictCursor)
yield cur.execute(_getmyusedevice_sql, (phone, 'USER', 'TerminalAccount'))
rows = cur.fetchall()
cur.close()
return rows
except Exception as e:
logging.error("db oper failed {0}".format(e))
return []
finally:
conn.close()
def check_device(self, userid, deviceid):
conn = yield get_mysqlcon('mxsuser')
if not conn:
logging.error("connect to mysql failed")
return False
try:
cur = conn.cursor()
yield cur.execute(_checkdevice_sql, (deviceid, userid))
rows = cur.fetchall()
cur.close()
return rows
except Exception as e:
logging.error("oper db failed {0}".format(e))
return False
finally:
conn.close()
def do(self, userid, data):
if not data:
return (403, None)
sid = data.get("id", "")
if not sid:
logging.error("invalid parameter")
return (403, None)
data.pop("id")
#format update string
update_s = ""
for key, value in data.items():
update_s += "%s = '%s'," % (key, value)
update_s = update_s + ("stage = '%s'" % MICKEY_DISPATCH_STAGE_OVER)
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return (403, None)
rst_code = 200
try:
#update
cur = conn.cursor()
update_sql = _sqlquery % (update_s, sid, MICKEY_DISPATCH_STAGE_SEND)
yield cur.execute(update_sql)
cur.close()
yield conn.commit()
except Exception as e:
logging.error("db oper failed {0}".format(e))
rst_code = 500
finally:
conn.close()
if rst_code == 200:
all_finished = yield self.check_all_wasfinish(sid)
if all_finished == True:
yield self.update_order_state(sid, MICKEY_DISPATCH_STAGE_OVER)
return (rst_code, None)
def un_bind(deviceid, phone):
conn = yield get_mysqlcon('mxsuser')
if not conn:
logging.error("connect to mysql failed")
return False
try:
cur = conn.cursor()
yield cur.execute(_unbind_sql, (phone, 'USER', deviceid))
cur.close()
yield conn.commit()
return True
except Exception as e:
logging.error("oper db failed {0}".format(e))
finally:
conn.close()
return False
def query_failed():
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return []
try:
cur = conn.cursor(tornado_mysql.cursors.DictCursor)
yield cur.execute(_queryoperfailed_sql)
failed_opers = cur.fetchall()
cur.close()
return failed_opers
except Exception as e:
logging.error("oper db failed {0}".format(e))
return
finally:
conn.close()
def update_failed(operid):
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return []
try:
cur = conn.cursor()
yield cur.execute(_updateretrytimes_sql, (operid))
cur.close()
yield conn.commit()
except Exception as e:
logging.error("oper db failed {0}".format(e))
return
finally:
conn.close()
def remove_expired():
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return []
try:
cur = conn.cursor()
yield cur.execute(_deleteoperfailed_sql)
cur.close()
yield conn.commit()
except Exception as e:
logging.error("oper db failed {0}".format(e))
return
finally:
conn.close()
def fetch_device(deviceid):
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return {}
try:
cur = conn.cursor(tornado_mysql.cursors.DictCursor)
qy_sql = _getdevice_sql % ('%Y-%m-%d', '%Y-%m-%d', deviceid)
yield cur.execute(qy_sql)
device = cur.fetchone()
cur.close()
return device
except Exception as e:
logging.error("db oper failed {0}".format(e))
return {}
finally:
conn.close()
def do(self, userid, data):
if not data:
return (403, None)
sid = data.get("id", "")
if not sid:
logging.error("invalid parameter")
return (403, None)
data.pop("id")
otime = data.get("otime", "")
if otime:
d_otime = datetime.datetime.utcfromtimestamp(float(otime))
data["otime"] = d_otime.strftime('%Y-%m-%d %H:%M:%S')
#format update string
update_s = ""
for key, value in data.items():
update_s += "%s = '%s'," % (key, value)
update_s = update_s[:-1]
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return (500, None)
rst_code = 200
try:
cur = conn.cursor()
update_sql = _sqlquery % (update_s, sid)
yield cur.connection.autocommit(True)
yield cur.execute(update_sql)
cur.close()
except Exception as e:
logging.error("db oper failed {0}".format(e))
rst_code = 500
finally:
conn.close()
return (rst_code, None)
def savetcp(self, tcprp, taskid, s_current):
upb = tcprp.get("upb", "")
downb = tcprp.get("downb", "")
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return
try:
cur = conn.cursor()
format_sql = _inserttcp_sql % (taskid, self.p_userid, s_current, upb, downb)
yield cur.execute(format_sql)
cur.close()
yield conn.commit()
except Exception as e:
logging.error("db oper failed {0}".format(e))
finally:
conn.close()
def do(self, userid, data):
if not data:
return (403, None)
sid = data.get("id", "")
if not sid:
logging.error("invalid parameter")
return (403, None)
data.pop("id")
pre_devices = data.get("pre_devices", [])
if pre_devices:
data["pre_devices"] = json.dumps(pre_devices)
#format update string
update_s = ""
for key, value in data.items():
update_s += "%s = '%s'," % (key, value)
update_s = update_s[:-1]
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return (403, None)
rst_code = 200
try:
#update
cur = conn.cursor()
update_sql = _sqlquery % (update_s, sid)
yield cur.execute(update_sql)
cur.close()
yield conn.commit()
except Exception as e:
logging.error("db oper failed {0}".format(e))
rst_code = 500
finally:
conn.close()
return (rst_code, None)
def fetch_com(self, comname):
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
self.set_status(403)
self.finish()
return ""
try:
cur = conn.cursor()
yield cur.execute(_query_sql, (comname))
device = cur.fetchone()
cur.close()
return device[0]
except Exception as e:
logging.error("db oper failed {0}".format(e))
return {}
finally:
conn.close()
def get_userproinfo(userid):
conn = yield get_mysqlcon('mxsuser')
if not conn:
logging.error("connect to mysql failed")
return None
try:
cur = conn.cursor(tornado_mysql.cursors.DictCursor)
yield cur.execute(_queryuser_sql, (userid))
user = cur.fetchone()
cur.close()
return user
except Exception as e:
logging.error("oper db failed {0}".format(e))
return None
finally:
conn.close()
return None
def get_userinfo(deviceid):
conn = yield get_mysqlcon('mxsuser')
if not conn:
logging.error("connect to mysql failed")
return None
try:
cur = conn.cursor(tornado_mysql.cursors.DictCursor)
yield cur.execute(_queryusers_sql, (deviceid, 'MobileAccount', 'USER'))
rows = cur.fetchall()
cur.close()
return rows
except Exception as e:
logging.error("oper db failed {0}".format(e))
return None
finally:
conn.close()
return None
def unset_flag(self, order_tag, userid):
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return False
unused_tag = order_tag + "_" + userid
try:
cur = conn.cursor()
yield cur.execute(_unset_sql, (unused_tag, self.p_userid, order_tag))
yield cur.execute(_unused_sql,(order_tag))
cur.close()
yield conn.commit()
except Exception as e:
logging.error("insert db failed {0}".format(e))
return False
finally:
conn.close()
return True
def set_tag(self, sid, tag_id):
if not sid or not tag_id:
return
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return
try:
cur = conn.cursor()
yield cur.execute(_sqlquery, (tag_id, tag_id, sid))
cur.close()
yield conn.commit()
except Exception as e:
logging.error("db oper failed {0}".format(e))
finally:
conn.close()
return
def check_alldispatch_wassend(self, sid):
is_send = False
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return is_send
try:
cur = conn.cursor()
yield cur.execute(_dischecksql, (sid, MICKEY_DISPATCH_STAGE_SEND))
unfinsh_dis = cur.fetchone()
if unfinsh_dis[0] == 0:
is_send = True
cur.close()
except Exception as e:
logging.error("db oper failed {0}".format(e))
finally:
conn.close()
return is_send
def post(self):
data = json.loads(self.request.body.decode("utf-8"))
feed_id = data.get("id", "")
logging.info("%s update feedback problem" % self.p_userid)
if not feed_id:
self.set_status(403)
self.finish()
return
update_sql = self.genupdatesql(data)
if not update_sql:
self.set_status(403)
self.finish()
return
#get mysql connect
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
self.set_status(500)
self.finish()
return
try:
cur = conn.cursor()
format_sql = _update_sql % (update_sql, feed_id)
yield cur.execute(format_sql)
cur.close()
yield conn.commit()
except Exception as e:
logging.error("db oper failed {0}".format(e))
finally:
conn.close()
self.finish()
def add_failed(groupid, userid, oper_type):
conn = yield get_mysqlcon()
if not conn:
logging.error("connect to mysql failed")
return None
operid = None
try:
cur = conn.cursor()
yield cur.execute(_addoperfailed_sql, (oper_type, groupid, userid))
operid = cur.lastrowid
cur.close()
yield conn.commit()
except Exception as e:
logging.error("oper db failed {0}".format(e))
return None
finally:
conn.close()
return operid
