Example #1
0
    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()
Example #2
0
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)
Example #4
0
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
Example #5
0
    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()
Example #6
0
    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
Example #7
0
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()
Example #8
0
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
Example #9
0
    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()
Example #12
0
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()
Example #13
0
    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)
Example #15
0
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
Example #16
0
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()
Example #17
0
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()
Example #18
0
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()
Example #19
0
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)
Example #21
0
    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)
Example #23
0
    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()
Example #24
0
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
Example #25
0
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
Example #26
0
    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
Example #29
0
    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()
Example #30
0
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