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)