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)
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
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
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:
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
def wash_location():
db = DBConnection().db
redis = MyRedis()
sql = "select tmobile, from_unixtime(add_time) as add_time, from_unixtime(del_time) as del_time from T_BIND_LOG where group_id in (239, 419)"
sql_1 = "select tmobile, add_time, del_time from T_BIND_LOG where op_type = 1 and tmobile = %s"
sql_2 = "select tmobile, add_time, del_time from T_BIND_LOG where op_type = 2 and tmobile = %s"
#print 'sql', sql
res = db.query(sql)
#print 'len ', len(terminals)
for i, t in enumerate(res):
tmobile = t.tmobile
add = db.query(sql_1, tmobile)
add_num = len(add)
unbind = db.query(sql_2, tmobile)
del_num = len(unbind)
print del_num, add_num, t.del_time , t.add_time
if del_num > add_num:
print del_num, add_num, t.del_time , t.add_time,
def modify_terminal():
db = DBConnection().db
redis = MyRedis()
mobile='14778746907'
#mobile='14778741845'
#mobile='14778749172'
#mobile='14778746786'
#mobile='14778740942'
#mobile='14778745985'
#mobile='14778744628'
#mobile='14778744861'
#mobile='14778742261'
#mobile='14778744473'
#mobile='14778747112'
#mobile='14778745219'
#mobile='14778742290'
#mobile='14778749137'
#mobile='14778742587'
#mobile='14778745073'
#mobile='14778747467'
#mobile='14778741340'
#mobile='14778748943'
#mobile='14778743681'
sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where mobile= %s"
terminals = db.query(sql, mobile)
#print 'len ', len(terminals)
count = 0
cnt = 0
no_loc = 0
for i, t in enumerate(terminals):
tid = t.tid
mobile = t.mobile
owner_mobile = t.owner_mobile
terminal_info_key = get_terminal_info_key(tid)
terminal_info = redis.getvalue(terminal_info_key)
if terminal_info:
print 'umobile in redis:%s, umobile in db:%s' % (terminal_info['owner_mobile'], owner_mobile)
if terminal_info['owner_mobile'] != owner_mobile:
print 'mobile: %s, umobile in redis:%s, umobile in db:%s' % (mobile, terminal_info['owner_mobile'], owner_mobile)
cnt = cnt + 1
terminal_info['owner_mobile'] = owner_mobile
redis.setvalue(terminal_info_key, terminal_info)
else:
pass
print 'count:', count
print 'cnt:', cnt
def statistic():
db = DBConnection().db
sql = 'select from_unixtime(timestamp), terminal_online, terminal_offline from T_STATISTIC where type=2 and timestamp between 1378051140 and 1380556741'
t = db.query(sql)
sum_on = 0
sum_off = 0
for i in t:
print 'i', i
on = i.terminal_online
off = i.terminal_offline
sum_on += on
sum_off += off
print 'sum_on: %s, sum_off: %s, sum: %s' % (sum_on, sum_off, sum_on+sum_off)
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"
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
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)
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
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
def block_test():
db = DBConnection().db
redis = MyRedis()
#terminals = db.query("select tid from T_TERMINAL_INFO where group_id= '438';")
#terminals = db.query("select tid from T_TERMINAL_INFO where group_id= '438'")
terminals = db.query("select id, tid, test from T_TERMINAL_INFO where mobile = '14778748087' ")
for terminal in terminals:
tid = terminal.tid
#db.execute("UPDATE T_TERMINAL_INFO SET trace_para='60: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
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, tid, begintime from T_TERMINAL_INFO'
#sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO'
#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 mobile, owner_mobile, begintime from T_TERMINAL_INFO where service_status != 0 and begintime between 1356969600 and 1388505599"
#sql = "select mobile, owner_mobile, begintime from T_TERMINAL_INFO where service_status != 0 and begintime between 1356969600 and 1388505599"
#sql = "select mobile, owner_mobile, begintime from T_TERMINAL_INFO where service_status != 0 and (mobile like '1477874%%' or mobile like '1477847%%' )"
print 'sql', sql
terminals = db.query(sql)
print 'len ', len(terminals)
for i, t in enumerate(terminals):
print 't', t
reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime))
ws.write(i,0,i)
ws.write(i,1,t['tid'])
ws.write(i,2,reg_time)
#print 't', t
#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)
#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('./jia_new.xls')
def block_test():
db = DBConnection().db
redis = MyRedis()
#terminals = db.query("select tid, move_val, static_val from T_TERMINAL_INFO where static_val =0 and move_val=0")
terminals = db.query("select tid, move_val, static_val from T_TERMINAL_INFO where tid = '392240008A' ")
#terminals = db.query("select tid, move_val, static_val from T_TERMINAL_INFO where id in (12923, 21792 )")
#terminals = db.query("select tid, move_val, static_val from T_TERMINAL_INFO where static_val =60")
for terminal in terminals:
tid = terminal.tid
#db.execute("UPDATE T_TERMINAL_INFO SET static_val=120,move_val=0 WHERE tid = %s",
# tid)
#db.execute("UPDATE T_TERMINAL_INFO SET static_val=0, move_val=60 WHERE tid = %s",
# tid)
print 'tid: %s parking is modified.' % 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
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')
def block_test():
db = DBConnection().db
redis = MyRedis()
#start_time = int(time.mktime(time.strptime("%s-%s-%s-%s-%s-%s"%(2014,8,26,0,0,0),"%Y-%m-%d-%H-%M-%S")))
#end_time = int(time.mktime(time.strptime("%s-%s-%s-%s-%s-%s"%(2014,8,27,18,0,0),"%Y-%m-%d-%H-%M-%S")))
#print time.localtime(start_time)
#print time.localtime(end_time)
tid = '3922400068'
start_time = 1409222189
end_time = 1409223445
track = 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)
distance = 0
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
print '---distance', distance, tid, start_time, end_time
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")
def export_sms():
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 insert_time, mobile, content from T_SMS where insert_time > 1393603200000 limit 5000"
#sql = "select insert_time, mobile, content from T_SMS where insert_time between 1391184000000 and 1393603200000 limit 5000"
#sql = "select insert_time, mobile, content from T_SMS where insert_time > 1388505600000 limit 5000"
#sql = "select insert_time, mobile, content from T_SMS where insert_time > 1388505600000"
#sql = "select insert_time, mobile, content from T_SMS where content like '%%激活成功%%' "
print 'sql', sql
sms = db.query(sql)
print 'len ', len(sms)
for i, s in enumerate(sms):
#print 's', s
reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(s.insert_time/1000))
ws.write(i,0,str(s.mobile))
ws.write(i,1,reg_time)
ws.write(i,2,s.content)
wt.save('./sms_3.xls')
def wash_location():
db = DBConnection().db
redis = MyRedis()
sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO "
#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.getvalue(key)
#print 'location', location, type(location)
if location and int(location.locate_error) > 500:
print '------1 tid', tid, location
location['locate_error'] = 500
print '------2 tid', tid, location
#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':location['locate_error']})
#redis.setvalue(key, mem_location, 86400*356*2)
count = count +1
print 'handled tid:', tid
print 'total hanlded count:', count
print 'total not hanlded count:', cnt
def batch_import(file_path):
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')
wb = xlrd.open_workbook(file_path)
sheet = wb.sheets()[0]
lst = ""
num = 0
#for i in range(1,sheet.nrows):
for i in range(0,sheet.nrows):
row = sheet.row_values(i)
print 'roe', row
#mobile = unicode(row[0])[0:-2]
mobile = unicode(row[0])[0:11] # is recommend
print 'mobile', mobile
t = db.get("select id, mobile, login, tid, owner_mobile, from_unixtime(begintime) as begin_time,"
" from_unixtime(login_time) as login_time, domain from T_TERMINAL_INFO where mobile = %s and service_status=1",
mobile)
print 't', t
if not t:
label= u'未激活'
b = db.query('select * from T_BIND_LOG where tmobile=%s', mobile)
if b:
label= u'已解绑'
umobile=''
print 'not: ', mobile
else:
label = u'已激活'
print 't: ', t
umobile=t.owner_mobile
num += 1
print 'label', label
ws.write(i,0,mobile)
ws.write(i,1,umobile)
if label == u'未激活':
ws.write(i,2,label, offline_style)
else:
ws.write(i,2,label, online_style)
if t:
begin_time = t.begin_time
login_time = t.login_time
#reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime))
begintime = str(begin_time.year)+'-'+str(begin_time.month)+'-' +str(begin_time.day) +'-'+str(begin_time.hour)+':'+str(begin_time.minute)+':'+str(begin_time.second)
logintime = str(login_time.year)+'-'+str(login_time.month)+'-'+ str(login_time.day) +'-'+str(login_time.hour)+':'+str(login_time.minute)+':'+str(login_time.second)
ws.write(i,3,begintime)
ws.write(i,4,logintime)
#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
wt.save('/tmp/jia_new.xls')
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:
class TerminalStatistic(object):
def __init__(self):
self.db = DBConnection().db
self.redis = MyRedis()
self.to_emails = ['*****@*****.**']
self.cc_emails = ['*****@*****.**','*****@*****.**']
#self.cc_emails = ['*****@*****.**','*****@*****.**','*****@*****.**']
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
#record['terminal_del_month'] = 0
#record['terminal_add_year'] = 0
#record['terminal_del_year'] = 0
#return record
end_of_last_day = sta_time - 1
record = db.get("SELECT terminal_add_month, terminal_add_year, terminal_del_month, terminal_del_year"
" FROM T_STATISTIC"
" WHERE timestamp = %s AND type = %s",
end_of_last_day, sta_type)
if not record:
# it should never happen
record = {}
current_day = time.localtime(sta_time)
if current_day.tm_mday == 1: # first day of a month, year.month.01, the month-data is unavaliable
record['terminal_add_month'] = 0
record['terminal_del_month'] = 0
if current_day.tm_mon == 1: # first month of a year, 2014.01.01, the month-data and year-data are unvavliable
record['terminal_add_year'] = 0
record['terminal_del_year'] = 0
return record
def handle_in_terminal(tmobile, start_time, end_time, db):
"""Check the terminal is del or add.
@params: tmobile, the mobile of terminal
start_time, the start time of a day
end_time, the end time of a day
db, database
"""
add_num = 0
del_num = 0
add_count = db.get("SELECT COUNT(*) AS count FROM T_BIND_LOG"
" WHERE tmobile = %s AND group_id = -1 AND op_type = %s AND add_time BETWEEN %s AND %s",
tmobile, UWEB.OP_TYPE.ADD, start_time, end_time)
del_count = db.get("SELECT COUNT(*) AS count FROM T_BIND_LOG"
" WHERE tmobile = %s AND group_id = -1 AND op_type = %s AND del_time BETWEEN %s and %s",
tmobile, UWEB.OP_TYPE.DEL, start_time, end_time)
interval = add_count.count - del_count.count
if interval == 0: # +-, -+
add_num = 0
del_num = 0
elif interval == 1: # +,+-+
add_num = 1
del_num = 0
elif interval == -1: # -, -+-
add_num = 0
del_num = 1
else:
#NOTE: it should never happen
logging.error("Tmobile:%s, add_count: %s, del_count: %s",
tmobile, add_count.count, del_count.count)
return add_num, del_num
def handle_en_terminal(tmobile, start_time, end_time, db):
"""Check the terminal is del or add.
@params: tmobile, the mobile of terminal
start_time, the start time of a day
end_time, the end time of a day
db, database
"""
add_num = 0
del_num = 0
add_count = db.get("SELECT COUNT(*) AS count FROM T_BIND_LOG"
" WHERE tmobile = %s AND group_id != -1 AND op_type = %s AND add_time BETWEEN %s AND %s",
tmobile, UWEB.OP_TYPE.ADD, start_time, end_time)
del_count = db.get("SELECT COUNT(*) AS count FROM T_BIND_LOG"
" WHERE tmobile = %s AND group_id != -1 AND op_type = %s AND del_time BETWEEN %s and %s",
tmobile, UWEB.OP_TYPE.DEL, start_time, end_time)
interval = add_count.count - del_count.count
if interval == 0: # +-, -+
add_num = 0
del_num = 0
elif interval == 1: # +,+-+
add_num = 1
del_num = 0
elif interval == -1: # -, -+-
add_num = 0
del_num = 1
else:
#NOTE: it should never happen
logging.error("Tmobile:%s, add_count: %s, del_count: %s",
tmobile, add_count.count, del_count.count)
return add_num, del_num
# handle the dead terminal:
handle_dead_terminal(self.db, self.redis)
# for individual
terminals = self.db.query("SELECT DISTINCT tmobile FROM T_BIND_LOG"
" WHERE (tmobile LIKE '14778%%' OR tmobile LIKE '1847644%%')"
" AND group_id = -1")
tmobiles = [terminal.tmobile for terminal in terminals]
for tmobile in tmobiles:
add_num, del_num = handle_in_terminal(tmobile, day_start_time, day_end_time, self.db)
in_terminal_add_day += add_num
in_terminal_del_day += del_num
record = get_record_of_last_day(day_start_time, UWEB.STATISTIC_USER_TYPE.INDIVIDUAL, self.db)
logging.info("in_terminal_add_day: %s, in_terminal_del_day:%s",
in_terminal_add_day, in_terminal_del_day)
logging.info("record of last_day for individual: %s", record)
in_terminal_add_month = record['terminal_add_month'] + in_terminal_add_day
in_terminal_del_month = record['terminal_del_month'] + in_terminal_del_day
in_terminal_add_year = record['terminal_add_year'] + in_terminal_add_day
in_terminal_del_year = record['terminal_del_year'] + in_terminal_del_day
# for enterprise
terminals = self.db.query("SELECT DISTINCT tmobile FROM T_BIND_LOG"
" WHERE (tmobile LIKE '14778%%' OR tmobile LIKE '1847644%%') "
" AND group_id != -1")
tmobiles = [terminal.tmobile for terminal in terminals]
for tmobile in tmobiles:
add_num, del_num = handle_en_terminal(tmobile, day_start_time, day_end_time, self.db)
e_terminal_add_day += add_num
e_terminal_del_day += del_num
record = get_record_of_last_day(day_start_time, UWEB.STATISTIC_USER_TYPE.ENTERPRISE, self.db)
logging.info("e_terminal_add_day: %s, e_terminal_del_day:%s",
e_terminal_add_day, e_terminal_del_day)
logging.info("record of last_day for enterprise: %s", record)
e_terminal_add_month = record['terminal_add_month'] + e_terminal_add_day
e_terminal_del_month = record['terminal_del_month'] + e_terminal_del_day
e_terminal_add_year = record['terminal_add_year'] + e_terminal_add_day
e_terminal_del_year = record['terminal_del_year'] + e_terminal_del_day
sql_corp_add = ("SELECT COUNT(id) as num"
" FROM T_CORP"
" WHERE timestamp BETWEEN %s AND %s")
sql_in_login = ("SELECT COUNT(distinct tll.uid) AS num"
" FROM T_LOGIN_LOG as tll, T_TERMINAL_INFO as tti"
" WHERE tll.uid = tti.owner_mobile "
" AND (tti.mobile LIKE '14778%%' OR tti.mobile LIKE '1847644%%')"
" AND tll.role =0 AND (tll.timestamp BETWEEN %s AND %s)")
sql_en_login = ("SELECT COUNT(distinct uid) AS num"
" FROM T_LOGIN_LOG"
" WHERE role != 0 AND (timestamp BETWEEN %s AND %s)")
sql_terminal_line_count = ("SELECT COUNT(tid) AS num"
" FROM T_TERMINAL_INFO ")
sql_in_active = ("SELECT COUNT(tmp.uid) AS num"
" FROM"
" (SELECT uid "
" FROM T_LOGIN_LOG as tll, T_TERMINAL_INFO as tti"
" WHERE tll.uid = tti.owner_mobile"
" AND (tti.mobile LIKE '14778%%' OR tti.mobile LIKE '1847644%%') "
" AND (tll.timestamp BETWEEN %s AND %s)"
" AND tll.role = 0 "
" GROUP BY tll.uid"
" HAVING count(tll.id) >3) tmp")
sql_en_active = ("SELECT COUNT(tmp.uid) AS num"
" FROM"
" (SELECT uid "
" FROM T_LOGIN_LOG"
" WHERE (timestamp BETWEEN %s AND %s)"
" AND role != 0 "
" GROUP BY uid"
" HAVING count(id) >3) tmp")
sql_kept = ("INSERT INTO T_STATISTIC(corp_add_day, corp_add_month, corp_add_year,"
" terminal_add_day, terminal_add_month, terminal_add_year,"
" terminal_del_day, terminal_del_month, terminal_del_year,"
" login_day, login_month, login_year, active, deactive,"
" terminal_online, terminal_offline,"
" terminal_individual, terminal_enterprise, timestamp, type)"
" VALUES (%s,%s,%s,"
" %s, %s, %s,"
" %s, %s, %s,"
" %s, %s, %s, %s, %s,"
" %s, %s, %s, %s, %s, %s)"
" ON DUPLICATE KEY"
" UPDATE corp_add_day=values(corp_add_day),"
" corp_add_month=values(corp_add_month), "
" corp_add_year=values(corp_add_year),"
" terminal_add_day=values(terminal_add_day),"
" terminal_add_month=values(terminal_add_month),"
" terminal_add_year=values(terminal_add_year),"
" terminal_del_day=values(terminal_del_day),"
" terminal_del_month=values(terminal_del_month),"
" terminal_del_year=values(terminal_del_year),"
" login_day=values(login_day),"
" login_month=values(login_month),"
" login_year=values(login_year),"
" active=values(active),"
" deactive=values(deactive),"
" terminal_online=values(terminal_online),"
" terminal_offline=values(terminal_offline),"
" terminal_individual=values(terminal_individual),"
" terminal_enterprise=values(terminal_enterprise)")
in_login_day = self.db.get(sql_in_login,
day_start_time, day_end_time )
in_login_month = self.db.get(sql_in_login,
month_start_time, day_end_time)
in_login_year = self.db.get(sql_in_login,
year_start_time, day_end_time)
in_active = self.db.get(sql_in_active,
month_start_time, day_end_time)
individuals = self.db.get("SELECT COUNT(tu.id) AS num"
" FROM T_USER as tu, T_TERMINAL_INFO as tti"
" WHERE tu.uid = tti.owner_mobile"
" AND (tti.mobile LIKE '14778%%' OR tti.mobile LIKE '1847644%%')")
in_deactive = DotDict(num=individuals.num-in_active.num)
in_terminal_online_count = self.db.get(sql_terminal_line_count + " WHERE service_status=1 AND group_id=-1 AND login != 0 AND (mobile LIKE '14778%%' OR mobile LIKE '1847644%%')")
in_terminal_offline_count = self.db.get(sql_terminal_line_count + " WHERE service_status=1 AND group_id=-1 AND login=0 AND (mobile LIKE '14778%%' OR mobile LIKE '1847644%%')")
self.db.execute(sql_kept,
0, 0, 0,in_terminal_add_day, in_terminal_add_month, in_terminal_add_year,
in_terminal_del_day, in_terminal_del_month, in_terminal_del_year,
in_login_day.num, in_login_month.num, in_login_year.num, in_active.num, in_deactive.num,
in_terminal_online_count.num,
in_terminal_offline_count.num, 0, 0, day_end_time,
UWEB.STATISTIC_USER_TYPE.INDIVIDUAL)
#2: enterprise stattis
e_corp_add_day = self.db.get(sql_corp_add,
day_start_time, day_end_time )
e_corp_add_month = self.db.get(sql_corp_add,
month_start_time, day_end_time )
e_corp_add_year = self.db.get(sql_corp_add,
year_start_time, day_end_time )
e_login_day = self.db.get(sql_en_login,
day_start_time, day_end_time )
e_login_month = self.db.get(sql_en_login,
month_start_time, day_end_time )
e_login_year = self.db.get(sql_en_login,
year_start_time, day_end_time )
e_active = self.db.get(sql_en_active,
month_start_time, day_end_time)
oper = self.db.get("SELECT count(id) as num"
" FROM T_OPERATOR")
enterprise = self.db.get("SELECT count(id) as num"
" FROM T_CORP")
e_deactive = DotDict(num=enterprise.num+oper.num-e_active.num)
e_terminal_online_count = self.db.get(sql_terminal_line_count + " WHERE service_status=1 AND group_id != -1 AND login != 0 AND (mobile LIKE '14778%%' OR mobile LIKE '1847644%%') ")
e_terminal_offline_count = self.db.get(sql_terminal_line_count + " WHERE service_status=1 AND group_id != -1 AND login = 0 AND (mobile LIKE '14778%%' OR mobile LIKE '1847644%%')")
self.db.execute(sql_kept,
e_corp_add_day.num, e_corp_add_month.num, e_corp_add_year.num,
e_terminal_add_day, e_terminal_add_month, e_terminal_add_year,
e_terminal_del_day, e_terminal_del_month, e_terminal_del_year,
e_login_day.num, e_login_month.num, e_login_year.num, e_active.num, e_deactive.num,
e_terminal_online_count.num, e_terminal_offline_count.num, 0, 0, day_end_time,
UWEB.STATISTIC_USER_TYPE.ENTERPRISE)
# 3 total statistic
terminal_total_in = self.db.get("SELECT count(id) AS num FROM T_TERMINAL_INFO WHERE group_id = -1")
terminal_total_en = self.db.get("SELECT count(id) AS num FROM T_TERMINAL_INFO WHERE group_id != -1")
self.db.execute(sql_kept,
e_corp_add_day.num, e_corp_add_month.num, e_corp_add_year.num,
in_terminal_add_day+e_terminal_add_day,
in_terminal_add_month+e_terminal_add_month,
in_terminal_add_year+e_terminal_add_year,
in_terminal_del_day+e_terminal_del_day,
in_terminal_del_month+e_terminal_del_month,
in_terminal_del_year+e_terminal_del_year,
in_login_day.num+e_login_day.num,
in_login_month.num+e_login_month.num,
in_login_year.num+e_login_year.num,
in_active.num+e_active.num, in_deactive.num+e_deactive.num,
in_terminal_online_count.num+e_terminal_online_count.num,
in_terminal_offline_count.num+e_terminal_offline_count.num,
terminal_total_in.num, terminal_total_en.num, day_end_time,
UWEB.STATISTIC_USER_TYPE.TOTAL)
end_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
logging.info("[CELERY] %s statistic_user finished", end_time)
except Exception as e:
logging.exception("[CELERY] statistic_user terminal exception.")
def statistic_offline_terminal(self, epoch_time):
"""Export data into excel file.
"""
start_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
logging.info("[CELERY] %s statistic_offline_terminal 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)
import xlwt
import xlrd
BASE_PATH = '/var/ydcws/reports/'
# NOTE: chinese filename cannot download successfully, so here use
# english filename. wish one day chinese words can work well
OFFLINE_FILE_NAME = u"terminals_offline"
#OFFLINE_FILE_NAME = u"离线用户统计表"
cur_path = time.strftime("%Y%m%d",time.localtime(epoch_time) )
pre_path = time.strftime("%Y%m%d",time.localtime(epoch_time-60*60*24))
PRE_PATH = BASE_PATH + OFFLINE_FILE_NAME + '-' + pre_path + '.xls'
CUR_PATH = BASE_PATH + OFFLINE_FILE_NAME + '-' + cur_path + '.xls'
OFFLINE_HEADER = (u"车主号",
u"终端号",
u"电量",
u"离线时间",
u"累计离线时间",
u"离线原因",
u"唤醒指令下发频次",
u"今日新增",
u"当前状态",
u"基站定位结果",
u"备注")
OFFLINE_DETAIL_HEADER = (u"用户类型",
u"车主号",
u"终端号",
u"电量",
u"离线时间",
u"累计离线时间",
u"离线原因",
u"备注")
# offline-terminals of this day
cur_sql_cmd = ("SELECT id, owner_mobile as umobile, mobile as tmobile,"
" begintime, offline_time, pbat, remark"
" FROM T_TERMINAL_INFO"
" WHERE service_status = 1 AND login =0 "
" AND (mobile LIKE '14778%%' OR mobile LIKE '1847644%%') "
" AND (offline_time BETWEEN %s AND %s) ORDER BY pbat")
# offline-terminals till now
#terminals_offline_cmd = ("SELECT id, owner_mobile as umobile, mobile as tmobile,"
# " begintime, offline_time, pbat, remark, group_id"
# " FROM T_TERMINAL_INFO"
# " WHERE service_status = 1 AND login =0 "
# " AND (mobile LIKE '14778%%' OR mobile LIKE '1847644%%') "
# " ORDER BY group_id, pbat")
terminals_offline_cmd = ("SELECT id, owner_mobile as umobile, mobile as tmobile,"
" begintime, offline_time, pbat, remark, group_id"
" FROM T_TERMINAL_INFO"
" WHERE service_status=1 AND login=0 "
" ORDER BY group_id, pbat")
terminal_sql_cmd = "SELECT login, remark, offline_time FROM T_TERMINAL_INFO WHERE mobile = %s LIMIT 1"
cur_res = self.db.query(cur_sql_cmd, day_start_time, epoch_time)
terminals_ofline = self.db.query(terminals_offline_cmd)
tmobile_lst = []
for item in cur_res:
tmobile_lst.append(item['tmobile'])
item['offline_period'] = int(time.time()) - item['offline_time']
item['offline_cause'] = 2 if item['pbat'] < 5 else 1
item['sim_status'] = u'失败'
if item['offline_cause'] == 1: # heart beat
# check the sim status
terminal_log = self.db.get("SELECT sim_status FROM T_BIND_LOG"
" WHERE tmobile = %s LIMIT 1",
item['tmobile'])
if terminal_log.sim_status == 1:
item['sim_status'] = u'成功'
item['remark'] = safe_unicode(item['remark'])
logging.info('[CELERY] the currentrecords to be dealed with, counts: %s, cur_res: %s', len(cur_res), cur_res)
# NOTE: get last day's data
pre_res = []
if not os.path.isfile(PRE_PATH):
logging.info("[CELERY] pre_path: %s cannot be found.", PRE_PATH)
else:
wb=xlrd.open_workbook(PRE_PATH)
sh=wb.sheet_by_name(u'离线汇总分析')
for rownum in range(1,sh.nrows): # get records from the second row
row = sh.row_values(rownum)
if row[1] in tmobile_lst:
continue
if row[8] == u'在线':
continue
tmobile = row[1]
terminal = self.db.get(terminal_sql_cmd, tmobile)
current_status = u'离线'
if not terminal:
current_status = u'已解绑'
row[8] = current_status
else:
if terminal['login'] !=0:
current_status = u'在线'
row[8] = current_status
offline_period = int(time.time()) - terminal['offline_time']
row[4] = seconds_to_label(offline_period)
d,m = divmod(offline_period,60*60)
count = d+1 if m else d
row[6] = count
row[10] = safe_unicode(terminal['remark'])
pre_res.append(row)
logging.info('[CELERY] the previous records to be dealed with, count: %s, pre_res: %s', len(pre_res), pre_res)
# some styles
#date_style = xlwt.easyxf(num_format_str='YYYY-MM-DD HH:mm:ss')
title_style = xlwt.easyxf('pattern: pattern solid, fore_colour ocean_blue; font: bold off; align: wrap on, vert centre, horiz center;' "borders: top double, bottom double, left double, right double;")
abnormal_style = xlwt.easyxf('font: colour_index red, bold off; align: wrap on, vert centre, horiz center;')
add_style = xlwt.easyxf('font: colour_index blue, bold off; align: wrap on, vert centre, horiz center;')
powerlow_style = xlwt.easyxf('font: colour_index dark_yellow, bold off; align: wrap on, vert centre, horiz center;')
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;')
center_style = xlwt.easyxf('align: wrap on, vert centre, horiz center;')
wb = xlwt.Workbook()
ws = wb.add_sheet(u'离线汇总分析')
ws_detail = wb.add_sheet(u'离线明细')
# sheet 1: 离线汇总分析
start_line = 0
for i, head in enumerate(OFFLINE_HEADER):
ws.write(0, i, head, title_style)
ws.col(0).width = 4000 # umobile
ws.col(1).width = 4000 # tmobile
ws.col(3).width = 4000 * 2 # offline_time
ws.col(4).width = 4000 * 2 # offline_period
ws.col(6).width = 4000 # lq count
ws.col(9).width = 4000 # sim_status
ws.col(10).width = 4000 * 4 # remark
start_line += 1
results = cur_res
for i, result in zip(range(start_line, len(results) + start_line), results):
ws.write(i, 0, result['umobile'], center_style)
ws.write(i, 1, result['tmobile'], center_style)
ws.write(i, 2, str(result['pbat'])+'%', center_style)
ws.write(i, 3, time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(result['offline_time'])), center_style)
ws.write(i, 4, seconds_to_label(result['offline_period']), center_style)
if result['offline_cause'] == 2:
offline_cause = u'低电关机'
ws.write(i, 5, offline_cause, powerlow_style)
else:
offline_cause = u'通讯异常'
ws.write(i, 5, offline_cause, abnormal_style)
if result['sim_status'] == u'成功':
ws.write(i, 9, safe_unicode(result['sim_status']), online_style)
else:
ws.write(i, 9, safe_unicode(result['sim_status']), abnormal_style)
d,m = divmod(result['offline_period'],60*60)
count = d+1 if m else d
ws.write(i, 6, count)
terminal = self.db.get("SELECT remark FROM T_TERMINAL_INFO where id = %s", result['id'])
ws.write(i, 7, u'新增', add_style)
ws.write(i, 10, safe_unicode(terminal['remark']), center_style)
start_line += 1
logging.info('[CELERY] current offline records, count: %s, tmobile_lst: %s', len(tmobile_lst), tmobile_lst)
results = pre_res
for i, result in zip(range(start_line, len(results) + start_line), results):
#for j in range(len(OFFLINE_HEADER)):
# ws.write(i, j, result[j])
#if result[1] in tmobile_lst:
# continue
ws.write(i, 0, result[0], center_style)
ws.write(i, 1, result[1], center_style)
ws.write(i, 2, result[2], center_style)
ws.write(i, 3, result[3], center_style)
ws.write(i, 4, result[4], center_style)
if result[5] == u'低电关机':
ws.write(i, 5, u'低电关机', powerlow_style)
else:
ws.write(i, 5, u'通讯异常', abnormal_style)
ws.write(i, 6, result[6])
if result[8] == u'在线':
ws.write(i, 8, u'在线', online_style)
elif result[8] == u'已解绑':
ws.write(i, 8, u'已解绑')
else:
pass
#ws.write(i, 9, result[9], center_style)
ws.write(i, 10, result[10], center_style)
# sheet 2: 离线明细
start_line = 0
for i, head in enumerate(OFFLINE_DETAIL_HEADER):
ws_detail.write(0, i, head, title_style)
ws_detail.col(1).width = 4000
ws_detail.col(2).width = 4000
ws_detail.col(4).width = 4000 * 2
ws_detail.col(5).width = 4000 * 2
ws_detail.col(7).width = 4000 * 4
start_line += 1
results = terminals_ofline
for i, result in zip(range(start_line, len(results) + start_line), results):
# some modification
if result['group_id'] == -1:
result['user_type'] = UWEB.USER_TYPE.PERSON
else:
result['user_type'] = UWEB.USER_TYPE.CORP
offline_period = int(time.time()) - result['offline_time']
result['offline_period'] = offline_period if offline_period > 0 else 0
result['offline_cause'] = 2 if result['pbat'] < 5 else 1
ws_detail.write(i, 0, u'个人用户' if result['user_type'] == UWEB.USER_TYPE.PERSON else u'集团用户')
ws_detail.write(i, 1, result['umobile'], center_style)
ws_detail.write(i, 2, result['tmobile'], center_style)
ws_detail.write(i, 3, str(result['pbat'])+'%', center_style)
ws_detail.write(i, 4, time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(result['offline_time'])), center_style)
ws_detail.write(i, 5, seconds_to_label(result['offline_period']))
if result['offline_cause'] == 2:
offline_cause = u'低电关机'
ws_detail.write(i, 6, offline_cause, powerlow_style)
else:
offline_cause = u'通讯异常'
ws_detail.write(i, 6, offline_cause, abnormal_style)
#ws_detail.write(i, 6, u'低电关机' if result['offline_cause'] == 2 else u'通讯异常')
terminal_offline = self.db.get("SELECT remark FROM T_TERMINAL_INFO where id = %s", result['id'])
ws_detail.write(i, 7, safe_unicode(terminal_offline['remark']))
start_line += 1
wb.save(CUR_PATH)
content = u'附件是 %s 的离线报表统计，请查收! 详情请看：%s ' % (cur_path,ConfHelper.ADMIN_CONF.url)
subject = u'移动车卫士离线分析报告' + cur_path
EmailHelper.send(self.to_emails, content, self.cc_emails, files=[CUR_PATH], subject=subject)
logging.info("[CELERY] statistic_offline_terminal finished, cur_path: %s", CUR_PATH)
def statistic_misc(self):
"""Handle the old data.
#NOTE: Self-test & Deprecated
"""
##self.db.execute("truncate T_SUBSCRIPTION_LOG")
##self.db.execute("truncate T_STATISTIC")
#terminals = self.db.query("select id, tid, mobile, begintime, offline_time, group_id from T_TERMINAL_INFO")
#for terminal in terminals:
# # 1: record to T_SUBSCRIPTION
# record_terminal_subscription(self.db, terminal['mobile'], terminal['group_id'], terminal['begintime'], terminal['begintime'],1)
# # 2: modify the offline_time
# if terminal['offline_time'] == 0:
# self.db.execute("UPDATE T_TERMINAL_INFO set offline_time = %s where id = %s ",
# terminal['begintime'], terminal['id'])
#part 2: for new statistic, transfer data from T_TERMINAL to T_BIND_LOG
#self.db.execute("TRUNCATE T_BIND_LOG")
#terminals = self.db.query("SELECT id, tid, mobile, begintime, offline_time, group_id from T_TERMINAL_INFO where service_status = 1")
#for terminal in terminals:
# #1376755199, 2013.8.17; 1376668799, 2013.08.16
# #record_add_action(terminal.mobile, terminal.group_id, 1376668799, self.db)
# record_add_action(terminal.mobile, terminal.group_id, 1376755199, self.db)
# #record_add_action(terminal.mobile, terminal.group_id, int(time.time()), self.db)
# part 3: handle terminals to be removed
#terminals = self.db.query("SELECT id, tid, mobile, begintime, offline_time, group_id from T_TERMINAL_INFO where service_status = 2")
#for terminal in terminals:
# logging.info("Delete to be removed terminal:%s with no log in T_BIND_LOG.", terminal.mobile)
# delete_terminal_no_record(terminal.tid, self.db, self.redis, del_user=True)
pass
def send_all(content):
db = DBConnection().db
terminals = db.query("SELECT mobile FROM T_TERMINAL_INFO")
for terminal in terminals:
send(content, terminal.mobile)
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:
pass
else: # low speed, may stop
if stop:
stop['end_time'] = pvt["timestamp"]
self.redis.setvalue(stop_key, stop, time=EVENTER.STOP_EXPIRY)
logging.info("[EVENTER] Stop point is updated: %s", stop)
else: # NOTE: start stop. #NOTE: create a new stop point
cnt += 1
lid=pvt['id']
stop = dict(lid=lid,
tid=tid,
start_time=pvt["timestamp"],
end_time=0,
pre_lon=pvt["longitude"],
pre_lat=pvt["latitude"],
distance=distance)
self.db.execute("INSERT INTO T_STOP(lid, tid, start_time, distance) VALUES(%s, %s, %s, %s)",
lid, tid, pvt["timestamp"], distance)
create_item.append(dict(distance=distance,
tid=tid,
timestamp=pvt["timestamp"],
lid=lid))
self.redis.setvalue(stop_key, stop, time=EVENTER.STOP_EXPIRY)
self.redis.delete(distance_key)
logging.info("[EVENTER] Stop point is created: %s", stop)
last_pvt = pvt
self.redis.setvalue(last_pvt_key, last_pvt, time=EVENTER.STOP_EXPIRY)
#print '---------------------- cnt', cnt
#BIG NOTE: never use it
#if create_item:
# #_start = time.time()
# self.db.executemany("INSERT INTO T_STOP(lid, tid, start_time, distance) VALUES(%s, %s, %s, %s)",
# [(item['lid'], item['tid'], item['timestamp'], item['distance']) for item in create_item])
# #_end = time.time()
# #print 'create_item', create_item
# #print 'time_diff', _end - _start
##handle db
#if delete_ids:
# #print 'delete_ids', delete_ids
# self.db.executemany("DELETE FROM T_STOP WHERE lid = %s",
# [(item) for item in delete_ids])
#if update_item:
# #print 'update_item', update_item
# self.db.executemany("UPDATE T_STOP SET end_time = %s WHERE lid = %s",
# [(item['timestamp'], item['lid']) for item in update_item])
def clear_stop(self, tid):
self.db.execute("DELETE FROM T_STOP WHERE tid = %s", tid)
stop_key = 'test_stop_redis:%s' % tid
distance_key = 'test_distance_redis:%s' % tid
last_pvt_key = 'test_last_pvt_redis:%s' % tid
self.redis.delete(stop_key)
self.redis.delete(distance_key)
self.redis.delete(last_pvt_key)
def handle_stop_single(self, tid, start_time, end_time):
#begin_time = time.localtime()
self.clear_stop(tid)
self.handle_stop(tid, start_time, end_time)
#end_time = time.localtime()
#print 'begin_time',begin_time
#print 'end_time',end_time
def handle_stop_groups(self, tids, start_time, end_time):
if not tids:
return
for tid in tids:
self.handle_stop_single(tid, start_time, end_time)
logging.info("handle_stop_groups finished")
def get_terminals(self):
#terminals = self.db.query("SELECT * from T_TERMINAL_INFO"
# " where tid = '35C2000067'"
# " limit 50")
#terminals = self.db.query("SELECT id, tid from T_TERMINAL_INFO where tid = '35C2000067'")
terminals = self.db.query("SELECT id, tid from T_TERMINAL_INFO LIMIT 8000")
return terminals
class MSmsMixin(object):
def __init__(self):
self.mysql_db = DBConnection().db
def retrieve_mixin(self, city_list=None, start_time=None, end_time=None):
# this is for parents
#res = self.mysql_db.query("SELECT tsl.mobile, tsl.fetchtime, txu.name AS user_name,"
# " txg.xxt_id AS group_id, txg.name AS group_name,"
# " thc.region_code AS city_id, thc.city_name AS city,"
# " thp.province_id AS province_id, thp.province_name AS province"
# " FROM T_SMS_LOG AS tsl, T_XXT_USER AS txu,"
# " T_XXT_GROUP AS txg, T_HLR_CITY AS thc,"
# " T_HLR_PROVINCE AS thp"
# " WHERE tsl.fetchtime BETWEEN %s AND %s"
# " AND tsl.mobile = txu.mobile"
# " AND txu.group_id = txg.xxt_id"
# " AND txg.city_id = thc.region_code"
# " AND thc.province_id = thp.province_id",
# start_time, end_time)
results = []
cities = city_info(city_list, self.mysql_db)
for city in cities:
#res_parents = self.mysql_db.query("call P_SMS_PARENTS(%s, %s, %s)",
# start_time, end_time, city.id)
sms_pres_parent = self.mysql_db.query("SELECT DISTINCT txu.mobile, txu.name AS user_name, "
" txg.xxt_id AS group_id, txg.name AS group_name,"
" thc.region_code AS city_id, thc.city_name AS city,"
" thp.province_id AS province_id, thp.province_name AS province"
" FROM T_XXT_USER AS txu,"
" T_XXT_GROUP AS txg,"
" T_HLR_CITY AS thc,"
" T_HLR_PROVINCE AS thp"
" WHERE txu.group_id = txg.xxt_id "
" AND txg.city_id = thc.region_code"
" AND thc.province_id = thp.province_id"
" AND txg.city_id = %s",
city.id)
res_parents = []
for sms_pre in sms_pres_parent:
sms_parents = self.mysql_db.query("SELECT mobile, fetchtime"
" FROM T_SMS_LOG"
" where fetchtime BETWEEN %s AND %s"
" AND mobile = %s",
start_time, end_time,
sms_pre.mobile)
for sms_parent in sms_parents:
res_parent = DotDict({'mobile':sms_parent.mobile,
'fetchtime':sms_parent.fetchtime,
'user_name':sms_pre.user_name,
'group_id':sms_pre.group_id,
'group_name':sms_pre.group_name,
'city_id':sms_pre.city_id,
'city':sms_pre.city,
'province_id':sms_pre.province_id,
'province':sms_pre.province})
res_parents.append(res_parent)
results.extend(res_parents)
#this is for targets
#res = self.mysql_db.query("SELECT tsl.mobile, tsl.fetchtime, txt.name AS user_name,"
# " txg.xxt_id AS group_id, txg.name AS group_name,"
# " thc.region_code AS city_id, thc.city_name AS city,"
# " thp.province_id AS province_id, thp.province_name AS province"
# " FROM T_SMS_LOG AS tsl, T_XXT_TARGET AS txt,"
# " T_XXT_GROUP AS txg, T_HLR_CITY AS thc,"
# " T_HLR_PROVINCE AS thp"
# " WHERE tsl.fetchtime BETWEEN %s AND %s"
# " AND tsl.mobile = txt.mobile"
# " AND txt.group_id = txg.xxt_id"
# " AND txg.city_id = thc.region_code"
# " AND thc.province_id = thp.province_id",
# start_time, end_time)
# res_targets = self.mysql_db.query("call P_SMS_TARGETS(%s, %s, %s)",
# start_time, end_time, city.id)
sms_pres_target = self.mysql_db.query("SELECT DISTINCT txt.mobile, txt.name AS user_name, "
" txg.xxt_id AS group_id, txg.name AS group_name,"
" thc.region_code AS city_id, thc.city_name AS city,"
" thp.province_id AS province_id, thp.province_name AS province"
" FROM T_XXT_TARGET AS txt,"
" T_XXT_GROUP AS txg,"
" T_HLR_CITY AS thc,"
" T_HLR_PROVINCE AS thp"
" WHERE txt.group_id = txg.xxt_id "
" AND txg.city_id = thc.region_code"
" AND thc.province_id = thp.province_id"
" AND txg.city_id = %s",
city.id)
res_targets = []
for sms_pre in sms_pres_target:
sms_targets = self.mysql_db.query("SELECT mobile, fetchtime"
" FROM T_SMS_LOG"
" WHERE fetchtime BETWEEN %s AND %s"
" AND mobile = %s",
start_time, end_time,
sms_pre.mobile)
for sms_target in sms_targets:
res_target = DotDict({'mobile':sms_target.mobile,
'fetchtime':sms_target.fetchtime,
'user_name':sms_pre.user_name,
'group_id':sms_pre.group_id,
'group_name':sms_pre.group_name,
'city_id':sms_pre.city_id,
'city':sms_pre.city,
'province_id':sms_pre.province_id,
'province':sms_pre.province})
res_targets.append(res_target)
results.extend(res_targets)
return results
def distinct(self, results):
"""recount the result, provide for frontend"""
res = []
mobiles = [r['mobile'] for r in results]
d_mobiles = list(set([r['mobile'] for r in results]))
for mobile in d_mobiles:
for result in results:
if mobile == result['mobile']:
count = mobiles.count(mobile)
r = {'province':result['province'],
'city':result['city'],
'mobile':mobile,
'count':count,
'group_name':result['group_name'],
'group_id':result['group_id'],
'user_name':result['user_name']}
res.append(r)
break
return res
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)
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'])
logging.info("[EVENTER] Stop point is closed: %s", stop)
else:
pass
else: # low speed, may stop
if stop:
stop['end_time'] = pvt["timestamp"]
self.redis.setvalue(stop_key, stop, time=EVENTER.STOP_EXPIRY)
logging.info("[EVENTER] Stop point is updated: %s", stop)
else: # NOTE: start stop. #NOTE: create a new stop point
cnt += 1
lid=pvt['id']
stop = dict(lid=lid,
tid=tid,
start_time=pvt["timestamp"],
end_time=0,
pre_lon=pvt["longitude"],
pre_lat=pvt["latitude"],
distance=distance)
self.db.execute("INSERT INTO T_STOP(lid, tid, start_time, distance) VALUES(%s, %s, %s, %s)",
lid, tid, pvt["timestamp"], distance)
self.redis.setvalue(stop_key, stop, time=EVENTER.STOP_EXPIRY)
self.redis.delete(distance_key)
logging.info("[EVENTER] Stop point is created: %s", stop)
last_pvt = pvt
self.redis.setvalue(last_pvt_key, last_pvt, time=EVENTER.STOP_EXPIRY)
print '---------------------- cnt', cnt
def clear_stop(self, tid, start_time, end_time):
self.db.execute("DELETE FROM T_STOP WHERE tid = %s and start_time between %s and %s",
tid, start_time, end_time)
stop_key = 'test_stop_redis:%s' % tid
distance_key = 'test_distance_redis:%s' % tid
last_pvt_key = 'test_last_pvt_redis:%s' % tid
self.redis.delete(stop_key)
self.redis.delete(distance_key)
self.redis.delete(last_pvt_key)
def wash_location():
db = DBConnection().db
redis = MyRedis()
#NOTE: all offline terminal
#sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where login = 0"
#NOTE: all offline terminals who has login before.
sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where login = 0 and login_time>0"
#sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where tid = '354A000121'"
#sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where mobile = '14778742419'"
#sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where mobile = '14778749929'"
#sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where login_time>0"
#print 'sql', sql
terminals = db.query(sql)
#print 'len ', len(terminals)
count = 0
cnt = 0
no_loc = 0
for i, t in enumerate(terminals):
tid = t.tid
key = 'location:%s' % tid
location = redis.getvalue(key)
if not location:
time.sleep(2)
no_loc = no_loc + 1
print 'no location, tid', tid
#continue
##return
##redis.delete(key)
#NOTE: get latest point.
location = db.get("SELECT timestamp, MAX(timestamp) as maxtime"
" FROM T_LOCATION"
" WHERE tid = %s"
" AND type = 0"
" AND latitude != 0",
tid)
if location and location['timestamp']:
location = db.get("SELECT * FROM T_LOCATION where timestamp = %s AND tid = %s AND latitude != 0 limit 1", location.maxtime, tid)
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 '-------no_loc', no_loc
print 'total hanlded count:', count
print 'total not hanlded count:', cnt
class MDailyMixin(object):
def __init__(self):
self.mysql_db = DBConnection().db
self.collection = self.mongodb_db.daily
def retrieve_mixin(self, city_list=None, end_time=None):
results = []
cities = city_info(city_list, self.mysql_db)
#optype_status = (XXT.OPER_TYPE.CREATE, XXT.OPER_TYPE.RESUME, XXT.OPER_TYPE.UPDATE)
for city in cities:
#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_DAILY_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)
#NOTE: change utc to the format as 201201312359590000
end_time_parent = time.strftime("%Y%m%d%H%M%S9999", time.localtime(end_time/1000))
total_parents = self.mysql_db.get("call P_DAILY_TOTAL_PARENTS(%s, %s, %s)",
end_time_parent, XXT.VALID.VALID, 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_DAILY_TOTAL_CHILDREN(%s, %s, %s)",
end_time, XXT.VALID.VALID, city.id)
#mo_sms = self.mysql_db.get("SELECT count(T_SMS_LOG.id) as total"
# " FROM T_SMS_LOG"
# " WHERE T_SMS_LOG.category = %s"
# " AND T_SMS_LOG.fetchtime <= %s"
# " AND ((T_SMS_LOG.mobile in"
# " (SELECT mobile FROM T_XXT_USER, T_XXT_GROUP"
# " WHERE T_XXT_USER.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s)) OR "
# " (T_SMS_LOG.mobile in"
# " (SELECT mobile FROM T_XXT_TARGET, T_XXT_GROUP"
# " WHERE T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s)))",
# SMS.CATEGORY.RECEIVE,
# end_time, city.id, city.id)
#mo_sms = self.mysql_db.get("call P_DAILY_MO_SMS(%s, %s, %s)",
# SMS.CATEGORY.RECEIVE, end_time, city.id)
#NOTE: get mobiles for parents and children, then query sms_log through it
mobile_parents = self.mysql_db.query("SELECT mobile"
" FROM T_XXT_USER, T_XXT_GROUP"
" WHERE T_XXT_USER.group_id = T_XXT_GROUP.xxt_id"
" AND T_XXT_GROUP.city_id = %s",
city.id)
mobile_children = self.mysql_db.query("SELECT mobile"
" FROM T_XXT_TARGET, T_XXT_GROUP"
" WHERE T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
" AND T_XXT_GROUP.city_id = %s",
city.id)
mobiles=[]
mobiles.extend(mobile_parents)
mobiles.extend(mobile_children)
mobiles = [int(mobile['mobile']) for mobile in mobiles]
mo_sms = self.mysql_db.get("SELECT count(T_SMS_LOG.id) as total"
" FROM T_SMS_LOG"
" WHERE T_SMS_LOG.category = %s"
" AND fetchtime <= %s"
" AND T_SMS_LOG.mobile IN %s",
SMS.CATEGORY.RECEIVE, end_time,
tuple(mobiles + DUMMY_IDS))
#mt_sms = self.mysql_db.get("SELECT count(T_SMS_LOG.id) as total"
# " FROM T_SMS_LOG"
# " WHERE T_SMS_LOG.category = %s"
# " AND T_SMS_LOG.fetchtime <= %s"
# " AND ((T_SMS_LOG.mobile in"
# " (SELECT mobile FROM T_XXT_USER, T_XXT_GROUP"
# " WHERE T_XXT_USER.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s)) OR "
# " (T_SMS_LOG.mobile in"
# " (SELECT mobile FROM T_XXT_TARGET, T_XXT_GROUP"
# " WHERE T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s)))",
# SMS.CATEGORY.SEND,
# end_time, city.id, city.id)
# mt_sms and mo_sms call the same procedure
#mt_sms = self.mysql_db.get("call P_DAILY_MO_SMS(%s, %s, %s)",
# SMS.CATEGORY.SEND, end_time, city.id)
mt_sms = self.mysql_db.get("SELECT count(T_SMS_LOG.id) as total"
" FROM T_SMS_LOG"
" WHERE T_SMS_LOG.category = %s"
" AND fetchtime <= %s"
" AND T_SMS_LOG.mobile IN %s",
SMS.CATEGORY.SEND, end_time,
tuple(mobiles + DUMMY_IDS))
# customer, schedule and realtiem call the same procedure
#custom = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP"
# " WHERE category = %s"
# " AND T_LOCATION.timestamp <= %s"
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim"
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# LOCATION.CATEGORY.CUSTOM, end_time, city.id)
#custom = self.mysql_db.get("call P_DAILY_CUSTOM(%s, %s, %s)",
# LOCATION.CATEGORY.CUSTOM, end_time, city.id)
#schedule = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP"
# " WHERE category = %s"
# " AND T_LOCATION.timestamp <= %s"
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim"
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# LOCATION.CATEGORY.SCHEDULE, end_time, city.id)
schedule = self.mysql_db.get("call P_DAILY_CUSTOM(%s, %s, %s)",
LOCATION.CATEGORY.SCHEDULE, end_time, city.id)
#realtime= self.mysql_db.get("SELECT count(*) as total"
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP"
# " WHERE category = %s"
# " AND T_LOCATION.timestamp <= %s"
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim"
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# LOCATION.CATEGORY.REALTIME, end_time, city.id)
realtime = self.mysql_db.get("call P_DAILY_CUSTOM(%s, %s, %s)",
LOCATION.CATEGORY.REALTIME, end_time, city.id)
#bound = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP"
# " WHERE (category = %s OR category = %s)"
# " AND T_LOCATION.timestamp <= %s"
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim"
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# LOCATION.CATEGORY.REGION_ENTER, LOCATION.CATEGORY.REGION_OUT,
# end_time, city.id)
bound = self.mysql_db.get("call P_DAILY_BOUND(%s, %s, %s, %s)",
LOCATION.CATEGORY.REGION_ENTER, LOCATION.CATEGORY.REGION_OUT, end_time, city.id)
#power = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP"
# " WHERE category = %s"
# " AND T_LOCATION.timestamp <= %s"
# " AND T_LOCATION.targesimt_id = T_LBMP_TERMINAL.sim"
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# LOCATION.CATEGORY.POWERLOW, end_time, city.id)
power = self.mysql_db.get("call P_DAILY_POWER(%s, %s, %s)",
LOCATION.CATEGORY.POWERLOW, end_time, city.id)
#sos = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP"
# " WHERE category = %s"
# " AND T_LOCATION.timestamp <= %s"
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim"
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# LOCATION.CATEGORY.EMERGENCY, end_time, city.id)
sos = self.mysql_db.get("call P_DAILY_SOS(%s, %s, %s)",
LOCATION.CATEGORY.EMERGENCY, end_time, city.id)
# NOTE: query old_data from mongodb, then add the increment value in past
# day: schedule, realtime, bound, sos, power
old_daily = self.collection.find_one({'timestamp': end_time - 86400000}, {'_id':0})
#custom_total = DotDict(old_daily).custom if old_daily else 0
schedule_total = DotDict(old_daily).schedule if old_daily else 0
realtime_total = DotDict(old_daily).realtime if old_daily else 0
bound_total = DotDict(old_daily).bound if old_daily else 0
sos_total = DotDict(old_daily).sos if old_daily else 0
power_total = DotDict(old_daily).power if old_daily else 0
res = {'_id': None,
'province_id': city.p_id,
'city_id': city.id,
'province': city.p_name,
'city': city.name,
'total_groups': total_groups.total,
'total_parents': total_parents.total,
'total_children': total_children.total,
'mt_sms': mt_sms.total,
'mo_sms': mo_sms.total,
# total = new + new + ... + new
#'custom': custom.total + custom_total,
'schedule': schedule.total + schedule_total,
'realtime': realtime.total + realtime_total,
'bound': bound.total + bound_total,
'sos': sos.total + sos_total,
'power': power.total + power_total,
'timestamp': end_time}
results.append(res)
return results
