Python DBConnection.execute方法代码示例

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
def move_data():
    db = DBConnection().db
       
    mobiles = ['18310505991', '13693675352', '13581731204']
    message = "数据库T_LOCATION已经完全转移到T_LOCATION_NEW,请及确认表信息的正确性和完整性。"
    #max_row = 1000000000
    max_row = 250000000
    begin_time = time.gmtime(time.time())
    for i in range(10000, max_row, 10000):
        sql = "INSERT INTO T_LOCATION_NEW" \
              " SELECT * FROM T_LOCATION WHERE id <=%d AND id > %d -10000" \
              " and (timestamp between 0 and 1448899200)" % (i, i)
        logging.info("exectue sql:%s", sql)
        
        n = db.execute(sql)
        #time.sleep(0.1)
        logging.info("last record  row id =%s", n)
        break
       # if i = 250000000:
        if i == 240000000:
            for mobile in mobiles:
                SMSHelper.send(mobile, message)    
                print "send", mobile
    end_time = time.gmtime(time.time())
    L_bak = "alter table T_LOCATION rename  to T_LOCATION_bak"
    NEW_L = "alter table T_LOCATION_NEW rename  to T_LOCATION"
    
    for i in range(1, 5): 
        time.sleep(1)
        logging.info("Will rename table neame after %d second", 5-i)
    
    db.execute(L_bak)
    db.execute(NEW_L)
    logging.info("exchange tables T_LOCATION and T_LOCATION_NEW is accomplished ")
    logging.info("Move table data begin_time:%s, end_time:%s", begin_time, end_time)

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
def batch_import(file_path):
    db = DBConnection().db
    wb = xlrd.open_workbook(file_path)
    sheet = wb.sheets()[0]
    lst = ""
    num = 0
    for i in range(sheet.nrows):
        row = sheet.row_values(i)
        mobile = unicode(row[0])
        mobile = mobile[0:11]
        t = db.get('select id, mobile, login, tid,owner_mobile,domain from T_TERMINAL_INFO where mobile = %s', mobile)
        if not t:
            num += 1
            print 'not: ', mobile
            db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)"
                       "  VALUES(NULL, %s)"
                       "  ON DUPLICATE KEY"
                       "  UPDATE mobile = values(mobile)", mobile)
        else:
            print 't', t


        #lst += "'" + mobile + "',"
        #print 'select id, mobile, login, tid,owner_mobile,domain from T_TERMINAL_INFO where mobile = ' + mobile + ';'
        #db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)"
        #           "  VALUES(NULL, %s)"
        #           "  ON DUPLICATE KEY"
        #           "  UPDATE mobile = values(mobile)", mobile)
        #content = ':SIM ' + umobile + ':' + mobile
        #print content
        #SMSHelper.send_to_terminal(mobile, content)
        #print '%s sucessfully.' % mobile
    print num 

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
def add_timestamp():
    db = DBConnection().db
    redis = MyRedis()
    
    e_sql = 'select id, tid, timestamp, lid from T_EVENT where timestamp = 0 order by id desc  limit 3000000'
    #e_sql = 'select id, tid, timestamp, lid from T_EVENT where timestamp = 0 '
    #e_sql = 'select id, tid, timestamp, lid from T_EVENT where timestamp = 0 limit 1000000'
    l_sql = 'select id, tid, timestamp from T_LOCATION where id = %s'
    print 'e_sql', e_sql
    event = db.query(e_sql)
    print 'len ', len(event)
    for i, e in enumerate(event):
        if not (i % 10000):
            print 'now, it is ', i
            time.sleep(2)

        print '-----i: %s, e: %s' % (i, e)

        lid = e.lid
        eid = e.id
         
        location = db.get(l_sql, lid)
        if location:
            gps_time = location.get('timestamp', 0)  
            if gps_time:
                print 'update time', gps_time
                db.execute('UPDATE T_EVENT SET timestamp = %s WHERE id = %s',
                           gps_time, eid)

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
def add_whitelist():
    db = DBConnection().db
    begin = 20000000000
    num = 100
    for mobile  in xrange(begin, begin+num+1):
        print 'mobile', mobile
        db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)"
                   "  VALUES(NULL, %s)"
                   "  ON DUPLICATE KEY"
                   "  UPDATE mobile = values(mobile)", mobile)

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
class MOACB(object):
    
    
    def __init__(self):
        ConfHelper.load(options.conf)
        self.db = DBConnection().db
    
    
    def fetch_mo_sms(self):
        status = ErrorCode.SUCCESS
        result = None
        try:
            mos = self.db.query("SELECT id, msgid, mobile, content "
                                "  FROM T_SMS "
                                "  WHERE category = %s "
                                "  AND send_status = %s"
                                "  ORDER BY id ASC"
                                "  LIMIT 10",
                                SMS.CATEGORY.MO, SMS.SENDSTATUS.PREPARING)
            
            for mo in mos:
                mobile = mo["mobile"]
                content = mo["content"]
                id = mo["id"]
                msgid = mo["msgid"]
                
                result = self.send_mo_to_acb(mobile, content, msgid)
                if result["status"] == ErrorCode.SUCCESS:
                    if int(result["ret"]) == ErrorCode.SUCCESS:
                        logging.info("SMS-->ACB success mobile = %s, content = %s", mobile, content)
                        self.db.execute("UPDATE T_SMS "
                                       "  SET send_status = %s"
                                       "  WHERE id = %s",
                                       SMS.SENDSTATUS.SUCCESS, id)
                        status = ErrorCode.SUCCESS
                    elif int(result["ret"]) == ErrorCode.FAILED:
                        logging.info("SMS-->ACB failure mobile = %s, content = %s", mobile, content)
                        self.db.execute("UPDATE T_SMS "
                                       "  SET send_status = %s"
                                       "  WHERE id = %s",
                                       SMS.SENDSTATUS.FAILURE, id)
                        status = ErrorCode.FAILURE
                    else:
                        #sms-->acb reponse error result
                        status = ErrorCode.FAILURE
                else:
                    # http response is None
                    status = ErrorCode.FAILURE
            
        except Exception, msg:
            status = ErrorCode.FAILURE
            logging.exception("Fetch mo sms exception : %s", msg)
        finally:

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
def block_test():
    db = DBConnection().db
    redis = MyRedis()

    sms = db.query("SELECT uid FROM T_SMS_OPTION")
    count = 0
    for s in sms:
        uid = s.uid
        user = db.get("select * from T_USER where uid = %s", uid)
        if user:
            pass
            # print 'pass'
        else:
            db.execute("DELETE FROM T_SMS_OPTION WHERE uid = %s", uid)
            print "delete ....", uid
            count += 1
    print "-------count: %s" % count

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
def block_test():
    db = DBConnection().db
    redis = MyRedis()

    #terminals = db.query("SELECT tid, mobile, group_id, cid FROM V_TERMINAL WHERE group_id != '-1'")
    #terminals = db.query("SELECT tid, tmobile, group_id, cid FROM V_TERMINAL WHERE cid = '13600335550'")
    #terminals = db.query("SELECT tid, mobile, group_id FROM T_TERMINAL_INFO WHERE stop_interval != 0 limit 1000")
    print 'len ', len(terminals)
    for terminal in terminals: 
        tid = terminal.tid
        db.execute("UPDATE T_TERMINAL_INFO SET stop_interval=0 WHERE tid = %s", 
                   tid)
        print 'tid: %s stop_interval is closed.' % tid
        sessionID_key = get_terminal_sessionID_key(tid)
        old_sessionid = redis.get(sessionID_key)
        if old_sessionid:
            redis.delete(sessionID_key)
            print "Termianl %s delete session in redis." % tid

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
def block_test():
    db = DBConnection().db
    redis = MyRedis()

    #terminals = db.query("select * from T_TERMINAL_INFO where mobile = 14778741722 " )
    terminals = db.query("select * from T_TERMINAL_INFO where mobile in (14778473468 ) " )
    for terminal in terminals: 
        tid = terminal.tid
        t = db.query("SELECT tid from T_TERMINAL_INFO where test !=0 and tid=%s", tid)  
        if not t:
            continue

        #print 'clear', terminal 
        db.execute("UPDATE T_TERMINAL_INFO SET test=0 WHERE tid = %s", 
                   tid)
        print 'tid: %s test is closed.' % tid
        sessionID_key = get_terminal_sessionID_key(tid)
        old_sessionid = redis.get(sessionID_key)
        if old_sessionid:
            redis.delete(sessionID_key)
            print "Termianl %s delete session in redis." % tid

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
def insert_sms():
    ConfHelper.load('../conf/global.conf')
    parse_command_line()
    db = DBConnection().db
    redis = MyRedis() 

    msgid=str(int(time.time() * 1000))[-9:]
    mobile = '18310505991'
    insert_time = int(time.time() * 1000)
    category = 2 # SMS.CATEGORY.MT
    #send_status = -1 # SMS.SENDSTATUS.PREPARING
    send_status = 0 # SMS.SENDSTATUS.SUCCESS
    count = 3

    for i in xrange(500):
        content= 'test sms'
        content = content + 'seq: %s' % i
        db.execute("INSERT INTO T_SMS(msgid, mobile, content, "
                   " insert_time, category, send_status) "
                   "  VALUES(%s, %s, %s, %s, %s, %s)",
                   msgid, mobile, content, insert_time,
                   category, send_status) 

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
def block_test():
    db = DBConnection().db
    redis = MyRedis()

    #terminals = db.query("SELECT tid, mobile, group_id, cid FROM V_TERMINAL WHERE group_id != '-1'")
    terminals = db.query("SELECT tid, tmobile, group_id, cid FROM V_TERMINAL WHERE cid = '13600335550'")
    for terminal in terminals: 
        tid = terminal.tid
        ttype = get_terminal_type_by_tid(tid) 
        print 'ttype', ttype
        if ttype == 'zj200':
            t = db.query("SELECT tid from T_TERMINAL_INFO where test !=0 and tid=%s", tid)  
            if not t:
                continue

            #print 'clear'
            db.execute("UPDATE T_TERMINAL_INFO SET test=0 WHERE tid = %s", 
                       tid)
            print 'tid: %s test is closed.' % tid
            sessionID_key = get_terminal_sessionID_key(tid)
            old_sessionid = redis.get(sessionID_key)
            if old_sessionid:
                redis.delete(sessionID_key)
                print "Termianl %s delete session in redis." % tid

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
class TerminalStatistic(object):

    def __init__(self):
        self.db = DBConnection().db
        self.redis = MyRedis()
        self.to_emails = ['[email protected]']
        self.cc_emails = ['[email protected]','[email protected]']
        #self.cc_emails = ['[email protected]','[email protected]','[email protected]']
        
    def statistic_online_terminal(self, epoch_time):
        start_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())) 
        logging.info("[CELERY] %s statistic_online_terminal started.", start_time)
        try:
            corps = self.db.query("SELECT cid FROM T_CORP")
            for corp in corps:
                if corp:
                    online_count = self.db.get("SELECT COUNT(tti.tid) AS num"
                                               "  FROM T_CORP tc, T_GROUP tg, T_TERMINAL_INFO tti" 
                                               "  WHERE tc.cid = tg.corp_id"
                                               "  AND tg.id = tti.group_id"
                                               "  AND tti.service_status = 1"
                                               "  AND tti.login != 0"
                                               "  AND  tc.cid = %s",
                                               corp.cid)
                    offline_count = self.db.get("SELECT COUNT(tti.tid) AS num"
                                                "  FROM T_CORP tc, T_GROUP tg, T_TERMINAL_INFO tti" 
                                                "  WHERE tc.cid = tg.corp_id"
                                                "  AND tg.id = tti.group_id"
                                                "  AND tti.service_status = 1"
                                                "  AND tti.login = 0"
                                                "  AND  tc.cid = %s",
                                                corp.cid)
                    if online_count:
                        online_num = online_count.num
                    else:
                        online_num = 0
                    if offline_count:
                        offline_num = offline_count.num
                    else:
                        offline_num = 0
                    
                    self.db.execute("INSERT INTO T_ONLINE_STATISTIC(online_num, offline_num, time, cid) "
                                    "  VALUES(%s, %s, %s, %s)",
                                    online_num, offline_num, int(epoch_time), corp.cid)
            convert_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(epoch_time)) 
            logging.info("[CELERY] %s statistic_online_terminal finish.", convert_time)
        except Exception as e:
            logging.exception("[CHECKER] statistic_online_terminal failed, exception: %s", e.args)

    def statistic_user(self, epoch_time):
        try:
            start_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())) 
            logging.info("[CELERY] %s statistic_user started.", start_time)

            current_day = time.localtime(epoch_time)
            day_start_time, day_end_time = start_end_of_day(current_day.tm_year, current_day.tm_mon, current_day.tm_mday)
            month_start_time, month_end_time = start_end_of_month(current_day.tm_year, current_day.tm_mon)
            year_start_time, year_end_time = start_end_of_year(current_day.tm_year)

            logging.info("[CELERY] day_start_time: %s, day_end_time: %s, month_start_time: %s, month_end_time: %s, year_start_time: %s, year_end_time: %s.", 
                        day_start_time, day_end_time, month_start_time, month_end_time, year_start_time, year_end_time)
            
            in_terminal_add_day = 0 
            in_terminal_del_day = 0 

            in_terminal_add_month = 0 
            in_terminal_del_month = 0 

            in_terminal_add_year = 0 
            in_terminal_del_year = 0 

            e_terminal_add_day = 0 
            e_terminal_del_day = 0 

            e_terminal_add_month = 0 
            e_terminal_del_month = 0 

            e_terminal_add_year = 0 
            e_terminal_del_year = 0

            def handle_dead_terminal(db, redis):
                """For the terminals to be removed, delete the associated info of it.
                @params: db, database
                """
                terminals = db.query("select tid, mobile from T_TERMINAL_INFO where service_status = 2")
                logging.info("Handle the to be removed terminals, the count of terminals: %s", len(terminals))
                for terminal in terminals:
                    logging.info("Delete the to be removed terminal:%s", terminal.mobile)
                    delete_terminal(terminal.tid, db, redis, del_user=True)
                

            def get_record_of_last_day(sta_time, sta_type, db):
                """Get record statisticted in last day.
                @params: sta_time, the statistic time
                         sta_type, the statistic type, 0: individual; 1: enterprise, 2: all
                         db, database
                """
                ## BIG NOTE: the snippet only be invoked when statistic occurs first time
                #record = {}
                #record['terminal_add_month'] = 0
#.........这里部分代码省略.........

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
class Test():

    def __init__(self):
        self.db = DBConnection().db
        self.redis = MyRedis()

    def get_track(self, tid, start_time, end_time, cellid=False):
        """NOTE: Now, only return gps point.
        """
        if cellid:
            track = self.db.query("SELECT id, latitude, longitude, clatitude,"
                                  "       clongitude, timestamp, name, type, speed, degree, locate_error"
                                  "  FROM T_LOCATION"
                                  "  WHERE tid = %s"
                                  "    AND NOT (latitude = 0 OR longitude = 0)"
                                  "    AND (timestamp BETWEEN %s AND %s)"
                                  "    GROUP BY timestamp"
                                  "    ORDER BY timestamp",
                                  tid, start_time, end_time)
        else: # gps, pvt
            track = self.db.query("SELECT id, latitude, longitude, clatitude,"
                                  "       clongitude, timestamp, name, type, speed, degree, locate_error"
                                  "  FROM T_LOCATION"
                                  "  WHERE tid = %s"
                                  "    AND category = 1"
                                  "    AND NOT (latitude = 0 OR longitude = 0)"
                                  "    AND (timestamp BETWEEN %s AND %s)"
                                  "    AND type = 0"
                                  "    GROUP BY timestamp"
                                  "    ORDER BY timestamp",
                                  tid, start_time, end_time)
        return track 

    #def get_track_distance(self, track):
    #    """Get distance of a section of track.
    #    """
    #    distance = 0 
    #    if not track:
    #        pass
    #    else:
    #        start_point = None
    #        for point in track:
    #            if not start_point: 
    #                start_point = point
    #                continue
    #            else:
    #                distance += get_distance(start_point["longitude"], start_point["latitude"], 
    #                                         point["longitude"], point["latitude"])
    #                start_point = point

    #    return distance

    def handle_stop(self, tid, start_time, end_time):

        track = self.get_track(tid, start_time, end_time)
        print 'track', len(track)
        cnt = 0  

        for i, pvt in enumerate(track):

            #print 'i: %s, speed: %s, pvt: %s' % (i, pvt['speed'], pvt)
            stop_key = 'test_stop_redis:%s' % tid
            stop = self.redis.getvalue(stop_key)

            distance_key = 'test_distance_redis:%s' % tid
            distance = self.redis.get(distance_key) 

            if not distance:
                distance = 0

            last_pvt_key = 'test_last_pvt_redis:%s' % tid
            last_pvt = self.redis.getvalue(last_pvt_key)

            if last_pvt:
                tmp = get_distance(int(last_pvt["longitude"]), int(last_pvt["latitude"]), 
                                                          int(pvt["longitude"]), int(pvt["latitude"])) 


                print 'tmp: %s, distance: %s' % (tmp, distance) 
                distance = float(distance) + tmp 
                print 'last distance: %s' % (distance) 
                #print 'add distance', i, pvt['id'], tmp, distance
                self.redis.setvalue(distance_key, distance, time=EVENTER.STOP_EXPIRY)

            if pvt['speed'] > LIMIT.SPEED_LIMIT: # 5  is moving
                if stop: #NOTE: time_diff is too short, drop the point. 
                    if pvt["timestamp"] - stop['start_time'] < 60: # 60 seconds 
                        cnt += 1  
                        _stop = self.db.get("select distance from T_STOP where lid =%s", stop['lid'])
                        if _stop:
                            tmp_dis = _stop['distance']
                        else:
                            tmp_dis = 0 
                        print 'tmp_dis', tmp_dis
                        distance = float(distance) + tmp_dis 
                        print 'tmp_dis distance', distance 

                        self.db.execute("DELETE FROM T_STOP WHERE lid = %s",
                                        stop['lid'])
                        self.redis.delete(stop_key)
#.........这里部分代码省略.........

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
class Test(object):

    def __init__(self):
        self.db = DBConnection().db
        self.redis = MyRedis()

    def get_track(self, tid, start_time, end_time, cellid=False):
        """NOTE: Now, only return gps point.
        """
        if cellid:
            track = self.db.query("SELECT id, latitude, longitude, clatitude,"
                                  "       clongitude, timestamp, name, type, speed, degree, locate_error"
                                  "  FROM T_LOCATION"
                                  "  WHERE tid = %s"
                                  "    AND NOT (latitude = 0 OR longitude = 0)"
                                  "    AND (timestamp BETWEEN %s AND %s)"
                                  "    GROUP BY timestamp"
                                  "    ORDER BY timestamp",
                                  tid, start_time, end_time)
        else: # gps, pvt
            track = self.db.query("SELECT id, latitude, longitude, clatitude,"
                                  "       clongitude, timestamp, name, type, speed, degree, locate_error"
                                  "  FROM T_LOCATION"
                                  "  WHERE tid = %s"
                                  "    AND category = 1"
                                  "    AND NOT (latitude = 0 OR longitude = 0)"
                                  "    AND (timestamp BETWEEN %s AND %s)"
                                  "    AND type = 0"
                                  "    GROUP BY timestamp"
                                  "    ORDER BY timestamp",
                                  tid, start_time, end_time)
        return track 

    def handle_stop(self, tid, start_time, end_time):

        track = self.get_track(tid, start_time, end_time)
        #print 'track, tid:%s, len: %s' % (tid, len(track))
        cnt = 0  

        delete_ids = []
        update_item = []
        create_item = []

        for i, pvt in enumerate(track):
            #print '------------i',i, pvt['id']
            #print 'i: %s, speed: %s, pvt: %s' % (i, pvt['speed'], pvt)
            stop_key = 'test_stop_redis:%s' % tid
            stop = self.redis.getvalue(stop_key)

            distance_key = 'test_distance_redis:%s' % tid
            distance = self.redis.get(distance_key) 

            if not distance:
                distance = 0

            last_pvt_key = 'test_last_pvt_redis:%s' % tid
            last_pvt = self.redis.getvalue(last_pvt_key)
            #if i == 0:
            #    print 'last_pvt', last_pvt
            if last_pvt:
                tmp = get_distance(int(last_pvt["longitude"]), int(last_pvt["latitude"]), 
                                   int(pvt["longitude"]), int(pvt["latitude"])) 

                #print 'tmp: %s, distance: %s' % (tmp, distance) 
                distance = float(distance) + tmp 
                #print 'last distance: %s' % (distance) 
                self.redis.setvalue(distance_key, distance, time=EVENTER.STOP_EXPIRY)

            if pvt['speed'] > LIMIT.SPEED_LIMIT: # 5  is moving
                if stop: #NOTE: time_diff is too short, drop the point. 
                    if pvt["timestamp"] - stop['start_time'] < 60: # 60 seconds 
                        cnt += 1  

                        _stop = self.db.get("SELECT distance FROM T_STOP WHERE lid =%s ", stop['lid'])
                        if _stop:
                            tmp_dis = _stop['distance']
                        else:
                            tmp_dis = 0 
                        #print 'tmp_dis', tmp_dis
                        distance = float(distance) + tmp_dis 
                        #print 'tmp_dis distance', distance 

                        test_id = self.db.execute("DELETE FROM T_STOP WHERE lid = %s",
                                        stop['lid'])
                        #print '---------delete id', test_id

                        delete_ids.append(stop['lid'])
                        self.redis.delete(stop_key)
                        self.redis.setvalue(distance_key, distance, time=EVENTER.STOP_EXPIRY) 
                        
                        logging.info("[EVENTER] Stop point is droped: %s", stop)
                    else: # close a stop point
                        cnt += 1  
                        self.redis.delete(stop_key)
                        self.db.execute("UPDATE T_STOP SET end_time = %s WHERE lid = %s",
                                        pvt["timestamp"], stop['lid'])
                        update_item.append(dict(timestamp=pvt["timestamp"],
                                                lid=stop['lid']))
                        logging.info("[EVENTER] Stop point is closed: %s", stop)
                else:
#.........这里部分代码省略.........

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
class MT(object):
    
    def __init__(self):
        ConfHelper.load(options.conf)
        self.db = DBConnection().db
    
    def fetch_mt_sms(self):
        status = ErrorCode.SUCCESS
        result = None
        try:
            mts = self.db.query("SELECT id, msgid, mobile, content, nosign"
                                "  FROM T_SMS"
                                "  WHERE category = %s"
                                "  AND send_status = %s"
                                "  ORDER BY id ASC"
                                "  LIMIT 50",
                                SMS.CATEGORY.MT, SMS.SENDSTATUS.PREPARING)

            for mt in mts:
                mobile = mt["mobile"]
                content = mt["content"]
                msgid = mt["msgid"]
                id = mt["id"]
                if not mobile:
                    logging.error("[SMS] Mobile is missing, drop it. mt: %s", mt)
                    continue
                if True: #mt["nosign"]:
                    send_status, result = self.send_mt_nosign(id, msgid, mobile, content)  

                    result = eval(result)
                    if send_status["status"] == '200':
                        if result["resultCode"] == "0":
                        #if result["flag"] == "success":
                            logging.info("SMS-->Gateway nosign message send successfully, mobile:%s, content:%s",
                                         mobile, content)
                            status = ErrorCode.SUCCESS
                            self.db.execute("UPDATE T_SMS "
                                           "  SET send_status = %s"
                                           "  WHERE id = %s",
                                           SMS.SENDSTATUS.SUCCESS, id)
                        else:
                            logging.error("SMS-->Gateway nosign message send failed, result:%s",
                                          result)
                            status = ErrorCode.FAILED
                            self.db.execute("UPDATE T_SMS "
                                                "  SET send_status = %s"
                                                "  WHERE id = %s",
                                                SMS.SENDSTATUS.FAILURE, id)
                    else:
                        logging.error("SMS-->Gateway nosign message failed, send_status:%s",
                                      send_status)
                        status = ErrorCode.FAILED
                        self.db.execute("UPDATE T_SMS "
                                        "  SET send_status = %s"
                                        "  WHERE id = %s",
                                        SMS.SENDSTATUS.FAILURE, id)
                    
                else:
                    result = self.send_mt(id, msgid, mobile, content)
                    result = json_decode(result)
                    
                    if result["status"] == ErrorCode.SUCCESS:
                        if result["ret"] == "100":
                            logging.info("SMS-->Gateway success mobile = %s, content = %s, id = %s ", mobile, content, id)
                            self.db.execute("UPDATE T_SMS "
                                           "  SET send_status = %s"
                                           "  WHERE id = %s",
                                           SMS.SENDSTATUS.SUCCESS, id)
                            status = ErrorCode.SUCCESS
                        else:
                            if result["ret"] == "101":
                                logging.error("SMS-->Gateway failure, errorcode = 101, mobile = %s, content = %s, id = %s ", mobile, content, id)
                            elif result["ret"] == "104":
                                logging.error("SMS-->Gateway content error, errorcode = 104, mobile = %s, content = %s, id = %s ", mobile, content, id)
                            elif result["ret"] == "105":
                                logging.error("SMS-->Gateway frequency too fast, errorcode = 105, mobile = %s, content = %s, id = %s ", mobile, content, id)
                            elif result["ret"] == "106":
                                logging.error("SMS-->Gateway number limited, errorcode = 106, mobile = %s, content = %s, id = %s ", mobile, content, id)
                            else:
                                logging.error("SMS-->Gateway other error, errorcode unknown, mobile = %s, content = %s, id = %s ", mobile, content, id)
                                
                            if  result["ret"] != "105":
                                self.db.execute("UPDATE T_SMS "
                                                "  SET send_status = %s"
                                                "  WHERE id = %s",
                                                SMS.SENDSTATUS.FAILURE, id)
                            status = ErrorCode.FAILED
                    else:
                        # http response is None
                        logging.info("SMS-->Gateway failed, mobile = %s, content = %s, id = %s ", mobile, content, id)
                        status = ErrorCode.FAILED
                        self.db.execute("UPDATE T_SMS "
                                        "  SET send_status = %s"
                                        "  WHERE id = %s",
                                        SMS.SENDSTATUS.FAILURE, id)
            
        except Exception, msg:
            status = ErrorCode.FAILED
            logging.exception("Fetch mt sms exception : %s", msg)
        finally:

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import execute [as 别名]
class CheckTask(object):

    def __init__(self):
        self.db = DBConnection().db
        self.redis = MyRedis()

    def check_track_status(self):
        logging.info("[CELERY] checkertask check track status started.")
        try:
            terminals = self.db.query("SELECT tid FROM T_TERMINAL_INFO"
                                      "  WHERE track = 1"
                                      "    AND service_status = 1")
            for terminal in terminals:
                track_key = get_track_key(terminal.tid)
                track = self.redis.get(track_key)
                logging.info("[CK] track: %s, tid: %s", track, terminal.tid)
                if not track:
                    self.db.execute("UPDATE T_TERMINAL_INFO"
                                    "  SET track = 0"
                                    "  WHERE tid = %s LIMIT 1",
                                    terminal.tid)
                    sessionID_key = get_terminal_sessionID_key(terminal.tid)
                    self.redis.delete(sessionID_key)
                    logging.info("[CK] Turn off track of terminal: %s", terminal.tid)
        except Exception as e:
            logging.exception("[CELERY] Check track status exception.")

    def check_poweroff_timeout(self):
        logging.info("[CELERY] checkertask check poweroff timeout started.")
        try:
            terminals = self.db.query("SELECT tpt.tid, tpt.sms_flag, tpt.timestamp"
                                      "  FROM T_POWEROFF_TIMEOUT as tpt, T_TERMINAL_INFO as tti"
                                      "  WHERE tti.tid = tpt.tid"
                                      "  AND tti.service_status = 1"
                                      "  AND tti.login = %s"
                                      "  AND tpt.sms_flag = %s"
                                      "  AND tpt.timestamp < %s",
                                      GATEWAY.TERMINAL_LOGIN.OFFLINE, GATEWAY.POWEROFF_TIMEOUT_SMS.UNSEND, (time.time() - 2*60*60))
            for terminal in terminals:
                terminal_info = QueryHelper.get_terminal_info(terminal.tid, self.db, self.redis)
                if int(terminal_info['pbat']) < 5:
                    user = QueryHelper.get_user_by_tid(terminal.tid, self.db)
                    sms = SMSCode.SMS_POWEROFF_TIMEOUT % terminal_info['alias'] 
                    SMSHelper.send(user.owner_mobile, sms)
                    self.update_sms_flag(terminal.tid)
                    logging.info("[CELERY] Send poweroff timeout sms to user:%s, tid:%s", user.owner_mobile, terminal.tid)
        except Exception as e:
            logging.exception("[CELERY] Check terminal poweroff timeout exception.")

    def update_sms_flag(self, tid):
        self.db.execute("UPDATE T_POWEROFF_TIMEOUT"
                        "  SET sms_flag = %s"
                        "  WHERE tid = %s",
                        GATEWAY.POWEROFF_TIMEOUT_SMS.SEND, tid)

    def send_offline_remind_sms(self):
        logging.info("[CELERY] checkertask send offline remind sms started.")
        try:
            currenttime = int(time.time())

            terminals = self.db.query("SELECT tid, alias, mobile, owner_mobile, offline_time"
                                      "  FROM T_TERMINAL_INFO"
                                      "  WHERE login = 0"
                                      "  AND service_status = 1"
                                      "  AND offline_time < %s",
                                      (currenttime - 24*60*60))
            for terminal in terminals:
                sms_option = QueryHelper.get_sms_option_by_uid(terminal.owner_mobile, 'heartbeat_lost', self.db)
                if sms_option == UWEB.SMS_OPTION.SEND:
                    ctime = get_terminal_time(currenttime)
                    ctime = safe_unicode(ctime)

                    alias = terminal['alias'] if terminal['alias'] else terminal['mobile']
                    sms = SMSCode.SMS_HEARTBEAT_LOST % (alias, ctime)
                    SMSHelper.send(terminal.owner_mobile, sms)
                    logging.info("[CELERY] Send offline remind sms to user:%s, tid:%s", terminal.owner_mobile, terminal.tid)
           
            logging.info("[CELERY] checkertask send offline remind sms finished.")
        except Exception as e:
            logging.exception("[CELERY] Check terminal poweroff timeout exception.")


    def check_charge_remind(self):
        logging.exception("[CELERY] checkertask charge remind started")
        try:
            terminals = self.db.query("SELECT tid, mobile, owner_mobile, begintime"
                                      "  FROM T_TERMINAL_INFO "
                                      "  WHERE service_status = 1")
            for terminal in terminals:
                begintime = int(terminal.begintime)
                begintime = time.strftime("%Y,%m,%d", time.localtime(begintime)).split(",")
                b_year = int(begintime[0])
                b_month = int(begintime[1])
                b_day = int(begintime[2])

                currenttime = int(time.time())
                currenttime = time.strftime("%Y,%m,%d", time.localtime(currenttime)).split(",")
                c_year = int(currenttime[0])
                c_month = int(currenttime[1])
                c_day = int(currenttime[2])
#.........这里部分代码省略.........