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)
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
def move_data():
db = DBConnection().db
mobiles = ['18310505991', '13693675352', '13581731204']
message = "数据库T_LOCATION已经完全转移到T_LOCATION_NEW,请及确认表信息的正确性和完整性。"
#max_row = 1000000000
max_row = 250000000
begin_time = time.gmtime(time.time())
for i in range(10000, max_row, 10000):
sql = "INSERT INTO T_LOCATION_NEW" \
" SELECT * FROM T_LOCATION WHERE id <=%d AND id > %d -10000" \
" and (timestamp between 0 and 1448899200)" % (i, i)
logging.info("exectue sql:%s", sql)
n = db.execute(sql)
#time.sleep(0.1)
logging.info("last record row id =%s", n)
break
# if i = 250000000:
if i == 240000000:
for mobile in mobiles:
SMSHelper.send(mobile, message)
print "send", mobile
end_time = time.gmtime(time.time())
L_bak = "alter table T_LOCATION rename to T_LOCATION_bak"
NEW_L = "alter table T_LOCATION_NEW rename to T_LOCATION"
for i in range(1, 5):
time.sleep(1)
logging.info("Will rename table neame after %d second", 5-i)
db.execute(L_bak)
db.execute(NEW_L)
logging.info("exchange tables T_LOCATION and T_LOCATION_NEW is accomplished ")
logging.info("Move table data begin_time:%s, end_time:%s", begin_time, end_time)
def add_whitelist():
db = DBConnection().db
begin = 20000000000
num = 100
for mobile in xrange(begin, begin+num+1):
print 'mobile', mobile
db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)"
" VALUES(NULL, %s)"
" ON DUPLICATE KEY"
" UPDATE mobile = values(mobile)", mobile)
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 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 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 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)
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
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()
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
def block_test():
db = DBConnection().db
redis = MyRedis()
#terminals = db.query("SELECT tid, mobile, group_id, cid FROM V_TERMINAL WHERE group_id != '-1'")
#terminals = db.query("SELECT tid, tmobile, group_id, cid FROM V_TERMINAL WHERE cid = '13600335550'")
#terminals = db.query("SELECT tid, mobile, group_id FROM T_TERMINAL_INFO WHERE stop_interval != 0 limit 1000")
print 'len ', len(terminals)
for terminal in terminals:
tid = terminal.tid
db.execute("UPDATE T_TERMINAL_INFO SET stop_interval=0 WHERE tid = %s",
tid)
print 'tid: %s stop_interval is closed.' % tid
sessionID_key = get_terminal_sessionID_key(tid)
old_sessionid = redis.get(sessionID_key)
if old_sessionid:
redis.delete(sessionID_key)
print "Termianl %s delete session in redis." % tid
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 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
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 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
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 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
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 insert_sms():
ConfHelper.load('../conf/global.conf')
parse_command_line()
db = DBConnection().db
redis = MyRedis()
msgid=str(int(time.time() * 1000))[-9:]
mobile = '18310505991'
insert_time = int(time.time() * 1000)
category = 2 # SMS.CATEGORY.MT
#send_status = -1 # SMS.SENDSTATUS.PREPARING
send_status = 0 # SMS.SENDSTATUS.SUCCESS
count = 3
for i in xrange(500):
content= 'test sms'
content = content + 'seq: %s' % i
db.execute("INSERT INTO T_SMS(msgid, mobile, content, "
" insert_time, category, send_status) "
" VALUES(%s, %s, %s, %s, %s, %s)",
msgid, mobile, content, insert_time,
category, send_status)
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 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 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 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
class CheckService(object):
def __init__(self):
self.db = DBConnection().db
self.tid = 'B123SIMULATOR'
self.mobiles = [15982463820, 15882081659]
self.emails = ['*****@*****.**', '*****@*****.**']
#self.emails = ['*****@*****.**', '*****@*****.**', '*****@*****.**']
#self.emails = ['*****@*****.**', '*****@*****.**', '*****@*****.**', '*****@*****.**']
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
def __init__(self):
"""Provide some necessary info and create a socket.
"""
self.tid = 'T123SIMULATOR'
self.tmobile = '13011292217'
self.imsi = '18310505991'
self.imei = '18310505991'
self.login_mg = "[%s,,1,1.0.0,%s,T1,%s,18310505991,%s,%s,CLW,2,]"
self.heartbeat_mg = "[%s,%s,1,1.0.0,%s,T2,23:9:95,1,0]"
self.location_mg = "[%s,%s,1,1.0.0,%s,T3,1,E,113.25,N,22.564152,120.3,270.5,1,460:0:9876:3171,23:9:100,%s]"
self.location_mg = "[%(time)s,%(sessionid)s,1,1.0.0,%(tid)s,T3,1,E,%(lon)s,N,%(lat)s,%(speed)s,%(degree)s,1,%(cellid)s,23:9:100,%(time)s]"
self.db = DBConnection().db
self.redis = MyRedis()
self.socket = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
host = ConfHelper.GW_SERVER_CONF.host
port = int(ConfHelper.GW_SERVER_CONF.port)
self.socket.connect((host, port))
