Python DBConnection.get方法代码示例

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import get [as 别名]
def wash_location():
    db = DBConnection().db
    redis = MyRedis()
    
    #sql = "select id, tid,  mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where tid = '36E2400480' "
    sql = "select id, tid,  mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO"
    #sql = "select id, tid,  mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where login = 0"
    #print 'sql', sql
    terminals  = db.query(sql)
    print 'len ', len(terminals)
    count = 0
    cnt = 0
    for i, t in enumerate(terminals):
        tid = t.tid
        key = 'location:%s' % tid
        location = redis.get(key) 
        if not location:
        #if True:
            print 'tid', tid

            location = db.get("SELECT timestamp, MAX(timestamp) as maxtime"
                              "  FROM T_LOCATION"
                              "  WHERE tid = %s"
                              "    AND type = 0"
                              "    AND latitude != 0",
                              tid)
            
            if location:
                if location.timestamp != location.maxtime:
                    print 'timestamp != maxtime, tid', tid
                    location = db.get("SELECT * FROM T_LOCATION where timestamp = %s AND tid = %s limit 1", location.maxtime, tid)
                else:
                    continue
                mem_location = DotDict({'id':location.id,
                                        'latitude':location.latitude,
                                        'longitude':location.longitude,
                                        'type':location.type,
                                        'clatitude':location.clatitude,
                                        'clongitude':location.clongitude,
                                        'timestamp':location.timestamp,
                                        'name':location.name,
                                        'degree':float(location.degree),
                                        'speed':float(location.speed),
                                        'locate_error':int(location.locate_error)})

                redis.setvalue(key, mem_location, 86400*356*2)
                count = count +1 
                print 'handled tid:', tid
            else:
                cnt = cnt + 1    
    print 'total hanlded count:', count
    print 'total not hanlded count:', cnt

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import get [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 get [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 get [as 别名]
class MGroupMixin(object):

    def __init__(self):
        self.mysql_db = DBConnection().db

    def retrieve_mixin(self, citylist=None):
        results = []
        for city in citylist:
            infos = self.mysql_db.query("SELECT thp.province_id AS id, thp.province_name AS province_name,"
                                        "       thc.city_name AS city_name"
                                        "  FROM T_HLR_CITY AS thc, T_HLR_PROVINCE AS thp"
                                        "  WHERE thc.region_code = %s"
                                        "    AND thc.province_id = thp.province_id", city)
            for info in infos:
                groups = self.mysql_db.query("SELECT txg.id, txg.xxt_id, txg.name,"
                                             " txa.xxt_id AS agency_id, txa.name AS agency_name"
                                             " FROM T_XXT_GROUP AS txg LEFT JOIN T_XXT_AGENCY AS txa"
                                             "   ON txa.xxt_id = txg.agency_id"
                                             " WHERE txg.city_id = %s", city)
                for group in groups:
                    #total_parents = self.mysql_db.get("SELECT count(*) AS total"
                    #                                  " FROM T_XXT_USER AS txu"
                    #                                  " WHERE txu.group_id = %s", group.xxt_id)
                    total_parents = self.mysql_db.get("call P_GROUP_TOTAL_PARENTS(%s)",
                                                      group.xxt_id) 
                    #total_children = self.mysql_db.get("SELECT count(*) AS total"
                    #                                   " FROM T_XXT_TARGET AS txt"
                    #                                   " WHERE txt.group_id = %s", group.xxt_id)
                    total_children = self.mysql_db.get("call P_GROUP_TOTAL_CHILDREN(%s)",
                                                       group.xxt_id) 
                    if not group.agency_name:
                        group.agency_name = u'暂无'

                    res = {'_id': None,
                           'province_id': info.id,
                           'city_id': city,
                           'id': group.id,
                           'group_id': group.xxt_id,
                           'group_name': group.name,
                           'agency_id': group.agency_id,
                           'agency': group.agency_name,
                           'total_parents': total_parents.total,
                           'total_children': total_children.total,
                           'city': info.city_name,
                           'province': info.province_name}
                    results.append(res)
        return results

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import get [as 别名]
def export_excel():
    db = DBConnection().db
    online_style = xlwt.easyxf("font: colour_index green, bold off; align: wrap on, vert centre, horiz center;")
    offline_style = xlwt.easyxf("font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;")

    wt = xlwt.Workbook()
    ws = wt.add_sheet(u"jia")

    # sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL'
    # sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO limit 5'
    # sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO  where service_status=1'
    # sql = "select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO  where service_status=1 and mobile like '%%%%14778%%%%' "
    # sql = "select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO  where group_id = 310 "
    sql = "select tid, mobile from V_TERMINAL where cid = '13823936111'"
    terminals = db.query(sql)
    print "len ", len(terminals)
    for i, t in enumerate(terminals):
        terminal = db.get("select owner_mobile from T_TERMINAL_INFO where tid = %s", t.tid)
        car = db.get("select cnum from T_CAR where tid = %s", t.tid)

        print "t", t
        print "ter", terminal
        print "car", car
        # reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime))
        ws.write(i, 0, terminal.owner_mobile)
        ws.write(i, 1, t.mobile)
        ws.write(i, 2, car.cnum)
    # 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
    wt.save("/tmp/jia_13823936111_corp.xls")

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import get [as 别名]
def execute():
    db = DBConnection().db
    redis = MyRedis()
    terminals = db.query("SELECT tid, mobile FROM T_TERMINAL_INFO ")
    print "len ", len(terminals)
    for t in terminals:
        tid = t.tid
        print "tid", tid
        car = db.get("select * from T_CAR where tid = %s", tid)
        if car:
            print "has car "
            pass
        else:
            print "insert car"

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import get [as 别名]
def send_all(content):
    db = DBConnection().db

    terminals = db.query("SELECT * FROM V_TERMINAL WHERE cid in ( '15207610001' )")
    # terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0")
    # terminals = db.query("SELECT mobile FROM T_TERMINAL_INFO where login=0")
    # terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0")
    # terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0 and offline_time>1408197915")
    # terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0 and offline_time>1411452000")
    print "len: %s", len(terminals)

    for t in terminals:
        terminal = db.get("select login, tid, mobile from T_TERMINAL_INFO where tid = %s", t.tid)
        if terminal.login == 0:  # offline
            print "--", terminal
            send(content, terminal.mobile)

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

    def __init__(self):
        self.db = DBConnection().db
        self.tid = 'B123SIMULATOR'
        self.mobiles = [15982463820, 15882081659]
        self.emails = ['[email protected]', '[email protected]']
        #self.emails = ['[email protected]', '[email protected]', '[email protected]']
        #self.emails = ['[email protected]', '[email protected]', '[email protected]', '[email protected]']

    def check_service(self):
        try:
            base_id = self.get_lid_by_tid(self.tid)
            while True:
                time.sleep(600)  # 10 minutes
                new_lid = self.get_lid_by_tid(self.tid)
                logging.info("[CK] simulator terminal location base_id:%s, new_lid:%s", 
                             base_id, new_lid)
                if new_lid > base_id:
                    base_id = new_lid
                else:
                    for mobile in self.mobiles:
                        sms = SMSCode.SMS_SERVICE_EXCEPTION_REPORT % ConfHelper.UWEB_CONF.url_out
                        SMSHelper.send(mobile, sms)
                        logging.info("[CK] Notify Administrator:%s By SMS, service exception!", 
                                     mobile)
                    for email in self.emails:
                        content = SMSCode.SMS_SERVICE_EXCEPTION_REPORT % ConfHelper.UWEB_CONF.url_out
                        EmailHelper.send(email, content)
                        logging.info("[CK] Notify Administrator:%s By EMAIL, service exception!", 
                                     email)
        except KeyboardInterrupt:
            logging.error("Ctrl-C is pressed.")
        except Exception as e:
            logging.exception("[CK] Check service failed. Exception: %s", 
                              e.args)

    def get_lid_by_tid(self, tid):
        location = self.db.get("SELECT id"
                               "  FROM T_LOCATION"
                               "  WHERE tid = %s"
                               "  ORDER BY id DESC LIMIT 1",
                               tid)
        if location:
            return location.id
        else:
            return 0

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import get [as 别名]
class MLocationMixin(object):
   
    def __init__(self):
        self.mysql_db = DBConnection().db
 
    def retrieve_mixin(self, city_list=None, start_time=None, end_time=None):
       
        results = []
        d = datetime.date.fromtimestamp(start_time/1000)
        year = d.year
        month = d.month
        cities = city_info(city_list, self.mysql_db)
        for city in cities:
            groups = self.mysql_db.query("SELECT T_XXT_GROUP.name as group_name, T_XXT_GROUP.xxt_id"
                                         " FROM T_XXT_GROUP WHERE T_XXT_GROUP.city_id = %s",
                                         city.id)
            for group in groups: 
                for item, category in [ #('custom', LOCATION.CATEGORY.CUSTOM),
                                       ('schedule', LOCATION.CATEGORY.SCHEDULE),
                                       ('realtime', LOCATION.CATEGORY.REALTIME)]:
                    #data = self.mysql_db.get("SELECT count(T_LOCATION.id) as total"
                    #                         " FROM T_LOCATION, T_XXT_TARGET, T_LBMP_TERMINAL"
                    #                         " WHERE T_LOCATION.timestamp BETWEEN %s AND %s" 
                    #                         " AND T_LOCATION.category = %s"
                    #                         " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim"
                    #                         " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
                    #                         " AND T_XXT_TARGET.group_id = %s",
                    #                         start_time, end_time, category, group.xxt_id)
                    data = self.mysql_db.get("call P_LOCATION(%s, %s, %s, %s)",
                                             start_time, end_time, category, group.xxt_id) 
                    group[item] = data.total

                res = {'_id': None,
                       'province_id': city.p_id,
                       'city_id': city.id,
                       'group_id': group.xxt_id,
                       'province': city.p_name,
                       'city': city.name,
                       'group_name': group.group_name,
                       # 'custom': group.custom,
                       'schedule': group.schedule,
                       'realtime': group.realtime,
                       'year': year,
                       'month': month}
                results.append(res)
        return results

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import get [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 get [as 别名]
def main():
    ConfHelper.load('../conf/global.conf')
    parse_command_line()
    db = DBConnection().db
    redis = MyRedis() 

    #tid = '35A60002B3'
    tid = '36E24006A6'
    location = db.get("SELECT id, speed, timestamp, category, name,"
                      "  degree, type, latitude, longitude, clatitude, clongitude,"
                      "  timestamp, locate_error"
                      "  FROM T_LOCATION"
                      "  WHERE tid = %s"
                      "    AND type = 0"
                      "    AND NOT (latitude = 0 AND longitude = 0)"
                      "    ORDER BY timestamp DESC"
                      "    LIMIT 1",
                      tid)
    print 'location:', location

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import get [as 别名]
def export_excel():
    db = DBConnection().db
    online_style = xlwt.easyxf('font: colour_index green, bold off; align: wrap on, vert centre, horiz center;')
    offline_style = xlwt.easyxf('font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;')

    wt = xlwt.Workbook()   
    ws = wt.add_sheet(u'jia') 
    
    sql = "select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO  where service_status=1 and begintime<1396281600 order by begintime "
    terminals  = db.query(sql)

    print 'len ', len(terminals)
    for i, t in enumerate(terminals):
        #terminal = db.get("select owner_mobile from T_TERMINAL_INFO where tid = %s", t.tid)
        #car= db.get("select cnum from T_CAR where tid = %s", t.tid)

        biz= db.get("select biz_type from T_BIZ_WHITELIST where mobile = %s limit 1", t.mobile)
        biz_type = 10
        if biz:
            if biz['biz_type'] == 1:
                biz_type = 20
         
        print 't', t
        reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime))
        #reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime))
        ws.write(i,0,t.owner_mobile)
        ws.write(i,1,t.mobile)
        ws.write(i,2,reg_time)
        ws.write(i,3,biz_type)
    #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
    wt.save('/tmp/jia_biz_type.xls')

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

    online_style = xlwt.easyxf('font: colour_index green, bold off; align: wrap on, vert centre, horiz center;')
    offline_style = xlwt.easyxf('font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;')

    wt = xlwt.Workbook()   
    ws = wt.add_sheet(u'jia') 

    wb = xlrd.open_workbook(file_path)
    sheet = wb.sheets()[0]
    lst = ""
    num = 0
    for i in range(0,sheet.nrows):
        row = sheet.row_values(i)
        print 'row', row
        mobile = unicode(row[0])
        mobile = mobile[0:11] 
        #mobile = unicode(row[0])[0:-2]
        print 'mobile', repr(mobile)

        sql_t = 'select tid, test, login, service_status from T_TERMINAL_INFO where mobile =' + mobile
        terminal = db.get(sql_t)
        print 'terminal', terminal
        tid = terminal.get('tid','') if terminal else ''
        sql = 'UPDATE T_TERMINAL_INFO set test=0 where mobile =' + mobile
        print 'sql', sql
        #db.execute('UPDATE T_TERMINAL_INFO set test=0 where mobile = %s', int(mobile))
        #db.execute(sql)
        sessionID_key = get_terminal_sessionID_key(tid)
        print 'sessionID_key', sessionID_key
        old_sessionid = redis.get(sessionID_key)
        if old_sessionid:
            redis.delete(sessionID_key)
            logging.info("[UWEB] Termianl %s delete session in redis.", tid)
        #if i == 3:
        #    break
    
    print num 

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import get [as 别名]
class MSubscriberMixin(object):
       
    def __init__(self):
        self.mysql_db = DBConnection().db

    def retrieve_mixin(self, citylist=None, start_time=None, end_time=None):
  
        results = []
        d = datetime.date.fromtimestamp(start_time/1000)
        year = d.year
        month = d.month
        cities = city_info(citylist, self.mysql_db)
        #optype_status = (XXT.OPER_TYPE.CREATE, XXT.OPER_TYPE.RESUME, XXT.OPER_TYPE.UPDATE)
        for city in cities:
            #new_groups = self.mysql_db.get("SELECT count(*) as total"
            #                               " FROM T_XXT_GROUP"
            #                               " WHERE timestamp BETWEEN %s AND %s"
            #                               " AND city_id = %s",
            #                               start_time, end_time, city.id)
            new_groups = self.mysql_db.get("call P_SUBSCRIBER_NEW_GROUPS(%s, %s, %s)",
                                           start_time, end_time, city.id)  
            #new_parents = self.mysql_db.get("SELECT count(T_XXT_USER.xxt_uid) as total"
            #                                " FROM T_XXT_USER, T_XXT_GROUP"
            #                                " WHERE T_XXT_USER.timestamp BETWEEN %s AND %s"
            #                                " AND optype IN %s"
            #                                " AND group_id = T_XXT_GROUP.xxt_id"
            #                                " AND T_XXT_GROUP.city_id = %s", 
            #                                start_time, end_time, tuple(optype_status), city.id)
            #NOTE: change utc to the format as 201201312359590000
            #only do this way when dealing with timestamp of T_XXT_USER and T_XXT_SUBSCRIPTION_LOG
            start_time_parent =  time.strftime("%Y%m%d%H%M%S0000", time.localtime(start_time/1000))
            end_time_parent =  time.strftime("%Y%m%d%H%M%S9999", time.localtime(start_time/1000))
            new_parents = self.mysql_db.get("call P_SUBSCRIBER_NEW_PARENTS(%s, %s, %s)",
                                            start_time_parent, end_time_parent, city.id)
 
            #new_children = self.mysql_db.get("SELECT count(T_XXT_TARGET.xxt_tid) as total"
            #                                 " FROM T_XXT_TARGET, T_XXT_GROUP"
            #                                 " WHERE T_XXT_TARGET.timestamp BETWEEN %s AND %s"
            #                                 " AND optype IN %s"
            #                                 " AND group_id = T_XXT_GROUP.xxt_id"
            #                                 " AND T_XXT_GROUP.city_id = %s", 
            #                                 start_time, end_time, tuple(optype_status), city.id)
            new_children = self.mysql_db.get("call P_SUBSCRIBER_NEW_CHILDREN(%s, %s, %s)",
                                             start_time, end_time, city.id) 

            #total_groups = self.mysql_db.get("SELECT count(*) as total"
            #                                 " FROM T_XXT_GROUP"
            #                                 " WHERE timestamp <= %s"
            #                                 " AND city_id = %s", 
            #                                 end_time, city.id)
            total_groups = self.mysql_db.get("call P_SUBSCRIBER_TOTAL_GROUPS(%s, %s)",
                                             end_time, city.id) 
            #total_parents = self.mysql_db.get("SELECT count(T_XXT_USER.xxt_uid) as total"
            #                                  " FROM T_XXT_USER, T_XXT_GROUP"
            #                                  " WHERE T_XXT_USER.timestamp <= %s"
            #                                  " AND optype IN %s"
            #                                  " AND group_id = T_XXT_GROUP.xxt_id"
            #                                  " AND T_XXT_GROUP.city_id = %s", 
            #                                  end_time, tuple(optype_status), city.id)
            total_parents = self.mysql_db.get("call P_SUBSCRIBER_TOTAL_PARENTS(%s, %s)",
                                              end_time_parent, city.id) 
            #total_children = self.mysql_db.get("SELECT count(T_XXT_TARGET.xxt_tid) as total"
            #                                   " FROM T_XXT_TARGET, T_XXT_GROUP"
            #                                   " WHERE T_XXT_TARGET.timestamp <= %s"
            #                                   " AND optype IN %s"
            #                                   " AND group_id = T_XXT_GROUP.xxt_id"
            #                                   " AND T_XXT_GROUP.city_id = %s", 
            #                                   end_time, tuple(optype_status), city.id)
            total_children = self.mysql_db.get("call P_SUBSCRIBER_TOTAL_CHILDREN(%s, %s)",
                                               end_time, city.id) 
            res = {'_id': None,
                   'province_id':city.p_id,
                   'city_id': city.id,
                   'province':city.p_name,
                   'city':city.name,
                   'new_groups': new_groups.total,
                   'new_parents': new_parents.total,
                   'new_children': new_children.total,
                   'total_groups': total_groups.total,
                   'total_parents': total_parents.total,
                   'total_children': total_children.total,
                   'year': year,
                   'month': month}
            results.append(res)
        return results

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import get [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
#.........这里部分代码省略.........