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 _on_finish(db):
self.db = db
page_count = int(data.pagecnt)
if statistic_mode == 'all': # all
if page_count == -1:
count = len(tids)
d, m = divmod(count, page_size)
page_count = (d + 1) if m else d
reports = []
dis_count = Decimal()
for item, tid in enumerate(tids):
seq = item + 1
# NOTE: It's amazing: In database, distance's type is long. sum(distance)'s type is Decimal
mileage_log = self.db.get("SELECT SUM(distance) AS distance"
" FROM T_MILEAGE_LOG"
" WHERE tid = %s"
" AND (timestamp BETWEEN %s AND %s)",
tid, start_time,
end_time + 60 * 60 * 24)
if mileage_log and mileage_log['distance']:
dis_sum = '%0.1f' % (mileage_log['distance'] / 1000,)
else:
dis_sum = 0
alias = QueryHelper.get_alias_by_tid(
tid, self.redis, self.db)
dct = dict(seq=seq,
alias=alias,
distance=float(dis_sum))
reports.append(dct)
dis_count += Decimal(dis_sum)
counts = [float(dis_count), ]
# orgnize and store the data to be downloaded
m = hashlib.md5()
m.update(self.request.body)
hash_ = m.hexdigest()
mem_key = self.KEY_TEMPLATE % (self.current_user.uid, hash_)
self.redis.setvalue(
mem_key, (statistic_mode, reports, counts), time=UWEB.STATISTIC_INTERVAL)
reports = reports[
(page_number * page_size):((page_number + 1) * page_size)]
self.write_ret(status,
dict_=DotDict(res=reports,
pagecnt=page_count,
hash_=hash_))
else: # single
tid = tids[0]
# end_time must be bigger than start_time
delta = end_time - start_time
d, m = divmod(delta, 60 * 60 * 24)
start_date = get_date_from_utc(start_time)
end_date = get_date_from_utc(end_time)
start_day = datetime.datetime.fromtimestamp(start_time)
end_day = datetime.datetime.fromtimestamp(end_time)
# get how many days the end_time and start_time cover
days = abs(end_day - start_day).days + 1
res = []
graphics = []
counts = []
dis_sum = Decimal()
current_time = int(time.time())
for item in range(days):
timestamp = start_time + 1 * 60 * 60 * 24 * (item)
date = get_date_from_utc(timestamp)
year, month, day = date.year, date.month, date.day
start_time_, end_time_ = start_end_of_day(
year=year, month=month, day=day)
re = {}
re['alias'] = '-'.join([str(year), str(month), str(day)])
mileage_log = self.db.get("SELECT distance FROM T_MILEAGE_LOG"
" WHERE tid = %s"
" AND timestamp = %s",
tid, end_time_)
distance = mileage_log['distance'] if mileage_log else 0
# meter --> km
distance = '%0.1f' % (Decimal(distance) / 1000,)
if float(distance) == 0:
distance = 0
graphics.append(float(distance))
dis_sum += Decimal(distance)
re['distance'] = distance
re['seq'] = item + 1
res.append(re)
counts = [float(dis_sum), ]
if page_count == -1:
items_count = len(res)
d, m = divmod(items_count, page_size)
page_count = (d + 1) if m else d
# store resutl in redis
m = hashlib.md5()
m.update(self.request.body)
hash_ = m.hexdigest()
mem_key = self.KEY_TEMPLATE % (self.current_user.uid, hash_)
self.redis.setvalue(
mem_key, (statistic_mode, res, counts,), time=UWEB.STATISTIC_INTERVAL)
res = res[
page_number * page_size:(page_number + 1) * page_size]
self.write_ret(status,
dict_=dict(res=res,
counts=counts,
graphics=graphics,
pagecnt=page_count,
hash_=hash_))
self.finish()
def post(self):
"""Generate a captcha for retrieving the password."""
status = ErrorCode.SUCCESS
try:
data = DotDict(json_decode(self.request.body))
umobile = data.mobile
captcha_psd = data.captcha_psd
logging.info("[UWEB] Get captcha request: %s", data)
except Exception as e:
status = ErrorCode.ILLEGAL_DATA_FORMAT
logging.exception("[UWEB] Invalid data format. body: %s, Exception: %s",
self.request.body, e.args)
self.write_ret(status)
return
try:
status = self.check_privilege(umobile)
if status != ErrorCode.SUCCESS:
logging.error("[UWEB] User: %s is just for test, has no right to access the function.",
umobile)
self.write_ret(status)
return
captchahash = self.get_secure_cookie("captchahash_password")
m = hashlib.md5()
m.update(captcha_psd.lower())
m.update(UWEB.HASH_SALT)
hash_ = m.hexdigest()
if hash_.lower() != captchahash.lower():
status = ErrorCode.WRONG_CAPTCHA_IMAGE
logging.info("[UWEB] Come from browser, captcha-check failed.")
self.write_ret(status)
return
user = self.db.get("SELECT mobile"
" FROM T_USER"
" WHERE mobile = %s"
" LIMIT 1",
umobile)
if user:
remote_ip = self.request.remote_ip
remote_ip_key = "register_remote_ip:%s" % remote_ip
umobile_key = "register_umobile:%s" % umobile
remote_ip_times = self.redis.getvalue(remote_ip_key)
umobile_times = self.redis.getvalue(umobile_key)
if remote_ip_times is None:
remote_ip_times = 0
if umobile_times is None:
umobile_times = 0
logging.info("[UWEB] Register. umobile: %s, umobile_times: %s, remote_ip: %s, remote_ip_times: %s",
umobile, umobile_times, remote_ip, remote_ip_times)
#NOTE: In current day, the same remote_ip allows 10 times, the umobile, 3 times
current_time = int(time.time())
date = get_date_from_utc(current_time)
year, month, day = date.year, date.month, date.day
start_time_, end_time_ = start_end_of_day(year=year, month=month, day=day)
if umobile_times >= 3: # <= 3 is ok
status = ErrorCode.REGISTER_EXCESS
if remote_ip_times >= 10: # <= 10 is ok
status = ErrorCode.REGISTER_EXCESS
if status == ErrorCode.REGISTER_EXCESS:
body = u'管理员您好：检测到频繁注册，请查看. umobile: %s, umobile_times: %s, remote_ip: %s, remote_ip_times: %s' % (
umobile, umobile_times, remote_ip, remote_ip_times)
notify_maintainer(self.db, self.redis, body, 'password')
self.write_ret(status)
return
captcha = ''.join(random.choice(string.digits) for x in range(4))
getcaptcha_sms = SMSCode.SMS_CAPTCHA % (captcha)
ret = SMSHelper.send(umobile, getcaptcha_sms)
ret = DotDict(json_decode(ret))
if ret.status == ErrorCode.SUCCESS:
logging.info("[UWEB] user uid: %s get captcha success, the captcha: %s",
umobile, captcha)
captcha_key = get_captcha_key(umobile)
self.redis.setvalue(captcha_key, captcha, UWEB.SMS_CAPTCHA_INTERVAL)
self.redis.set(umobile_key, umobile_times+1)
self.redis.expireat(umobile_key, end_time_)
self.redis.set(remote_ip_key, remote_ip_times+1)
self.redis.expireat(remote_ip_key, end_time_)
else:
status = ErrorCode.SERVER_BUSY
logging.error("[UWEB] user uid: %s get captcha failed.", umobile)
else:
status = ErrorCode.USER_NOT_ORDERED
logging.error("[UWEB] user uid: %s does not exist, get captcha failed.",
umobile)
self.write_ret(status)
except Exception as e:
logging.exception("[UWEB] user uid: %s retrieve password failed. Exception: %s",
umobile, e.args)
status = ErrorCode.SERVER_BUSY
self.write_ret(status)
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 post(self):
"""Get mileage of a terminal.
"""
status = ErrorCode.SUCCESS
res = []
try:
data = DotDict(json_decode(self.request.body))
mobile = data.mobile
start_time = int(data.start_time)
end_time = int(data.end_time)
token = data.token
logging.info("[MILEAGE] Request, data:%s", data)
except Exception as e:
status = ErrorCode.DATA_FORMAT_INVALID
logging.exception("[MILEAGE] Invalid data format, body: %s, ",
self.request.body)
self.write_ret(status)
return
try:
status = self.basic_check(token, mobile)
if status != ErrorCode.SUCCESS:
self.write_ret(status)
return
terminal = QueryHelper.get_terminal_by_tmobile(mobile, self.db)
tid = terminal.tid
# end_time must bigger than start_time
delta = end_time - start_time
d, m = divmod(delta, 60 * 60 * 24)
start_date = get_date_from_utc(start_time)
end_date = get_date_from_utc(end_time)
start_day = datetime.datetime.fromtimestamp(start_time)
end_day = datetime.datetime.fromtimestamp(end_time)
# get how many days the end_time and start_time cover
days = abs(end_day - start_day).days + 1
for item in range(days):
timestamp = start_time + 1 * 60 * 60 * 24 * (item)
date = get_date_from_utc(timestamp)
year, month, day = date.year, date.month, date.day
start_time_, end_time_ = start_end_of_day(
year=year, month=month, day=day)
date = '-'.join([str(year), str(month), str(day)])
mileage_log = self.db.get("SELECT distance FROM T_MILEAGE_LOG"
" WHERE tid = %s"
" AND timestamp = %s",
tid, end_time_)
mileage = mileage_log['distance'] if mileage_log else 0
r = dict(date=date,
mileage=mileage)
res.append(r)
self.write_ret(status,
dict_=dict(res=res))
except Exception as e:
logging.exception("[MILEAGE] mobile: %s. Exception: %s",
mobile, e.args)
status = ErrorCode.FAILED
self.write_ret(status)
def get(self):
"""Send captcha to user's phone through sms.
"""
status = ErrorCode.SUCCESS
try:
umobile = self.get_argument('umobile', '')
tmobile = self.get_argument('tmobile', '')
remote_ip = self.request.remote_ip
captcha_image = self.get_argument('captcha_img', '')
captchahash = self.get_secure_cookie("captchahash_image")
logging.info("[UWEB] Get captcha-sms request. umobile:%s, tmobile: %s, captcha_img: %s",
umobile, tmobile, captcha_image)
m = hashlib.md5()
m.update(captcha_image.lower())
m.update(UWEB.HASH_SALT)
hash_ = m.hexdigest()
if hash_.lower() != captchahash.lower():
status = ErrorCode.WRONG_CAPTCHA_IMAGE
logging.info(
"[UWEB] Come from browser, captcha-check failed.")
self.write_ret(status)
return
# check tmobile is whitelist or not
white_list = check_zs_phone(tmobile, self.db)
if not white_list:
logging.info("[UWEB] %s is not whitelist", tmobile)
status = ErrorCode.MOBILE_NOT_ORDERED
message = ErrorCode.ERROR_MESSAGE[status] % tmobile
self.write_ret(status, message=message)
return
# NOTE: check times
remote_ip_key = "register_remote_ip:%s" % remote_ip
umobile_key = "register_umobile:%s" % umobile
remote_ip_times = self.redis.getvalue(remote_ip_key)
umobile_times = self.redis.getvalue(umobile_key)
if remote_ip_times is None:
remote_ip_times = 0
if umobile_times is None:
umobile_times = 0
logging.info("[UWEB] Register. umobile: %s, umobile_times: %s, remote_ip: %s, remote_ip_times: %s",
umobile, umobile_times, remote_ip, remote_ip_times)
# NOTE: In current day, the same remote_ip allows 10 times, the
# umobile, 3 times
current_time = int(time.time())
date = get_date_from_utc(current_time)
year, month, day = date.year, date.month, date.day
start_time_, end_time_ = start_end_of_day(
year=year, month=month, day=day)
if umobile_times >= 3: # <= 3 is ok
status = ErrorCode.REGISTER_EXCESS
if remote_ip_times >= 10: # <= 10 is ok
status = ErrorCode.REGISTER_EXCESS
if status == ErrorCode.REGISTER_EXCESS:
body = u'管理员您好：检测到频繁注册，请查看. umobile: %s, umobile_times: %s, remote_ip: %s, remote_ip_times: %s' % (
umobile, umobile_times, remote_ip, remote_ip_times)
notify_maintainer(self.db, self.redis, body, 'register')
self.write_ret(status)
return
psd = ''.join(random.choice(string.digits) for x in range(4))
captcha_sms = SMSCode.SMS_REG % (psd)
ret = SMSHelper.send(umobile, captcha_sms)
ret = DotDict(json_decode(ret))
if ret.status == ErrorCode.SUCCESS:
logging.info("[UWEB] Get sms captcha successfully. umobile: %s, captcha: %s.",
umobile, psd)
captcha_key = get_captcha_key(umobile)
self.redis.setvalue(
captcha_key, psd, UWEB.SMS_CAPTCHA_INTERVAL)
self.redis.set(umobile_key, umobile_times + 1)
self.redis.expireat(umobile_key, end_time_)
self.redis.set(remote_ip_key, remote_ip_times + 1)
self.redis.expireat(remote_ip_key, end_time_)
else:
status = ErrorCode.SERVER_BUSY
logging.error(
"[UWEB] Get sms captcha failed. umobile: %s.", umobile)
self.write_ret(status)
except Exception as e:
logging.exception("[UWEB] Get sms captcha failed. umobile:%s. Exception: %s",
umobile, e.args)
status = ErrorCode.SERVER_BUSY
self.write_ret(status)
def post(self):
"""Provie some statistics for one terminals.
"""
status = ErrorCode.SUCCESS
try:
data = DotDict(json_decode(self.request.body))
logging.info("[UWEB] event single statistic request: %s, uid: %s, tid: %s",
data, self.current_user.uid, self.current_user.tid)
except Exception as e:
status = ErrorCode.ILLEGAL_DATA_FORMAT
self.write_ret(status)
return
try:
page_size = UWEB.LIMIT.PAGE_SIZE_STATISTICS
page_number = int(data.pagenum)
page_count = int(data.pagecnt)
tid = data.tid
res = []
graphics = []
counts_dct= {}
counts = []
label = u''
sql_cmd = ("SELECT COUNT(*) as count FROM V_EVENT"
" WHERE tid = %s"
" AND (timestamp BETWEEN %s AND %s) AND category != 5 ")
sql_cmd_category = sql_cmd + " AND category =%s"
current_time = int(time.time())
CATEGORY_DCT = DotDict(powerlow=EVENTER.CATEGORY.POWERLOW,
illegashake=EVENTER.CATEGORY.ILLEGALSHAKE,
illegalmove=EVENTER.CATEGORY.ILLEGALMOVE,
#sos=EVENTER.CATEGORY.EMERGENCY,
heartbeat_lost=EVENTER.CATEGORY.HEARTBEAT_LOST)
CATEGORY_KEY = ['illegalmove','illegashake', 'heartbeat_lost', 'powerlow' ]
for key in CATEGORY_KEY:
counts_dct[key] = 0
statistics_type = data.statistics_type
if statistics_type == UWEB.STATISTICS_TYPE.YEAR:
label = data.year + u'年'
start_time, end_time = start_end_of_year(year=data.year)
for month in range(1,12+1):
start_time_, end_time_ = start_end_of_month(year=data.year, month=str(month))
if start_time_ > current_time:
break
re = {}
re['name'] = str(month)
events = {}
for key in CATEGORY_KEY:
res_item = {}
item = self.db.get(sql_cmd_category, tid, start_time_, end_time_, CATEGORY_DCT[key])
res_item[key] = item.count
events.update(res_item)
#event = self.db.get(sql_cmd, tid, start_time_, end_time_)
#graphics.append(event.count)
re['events'] = events
res.append(re)
#for category in CATEGORY_DCT.itervalues():
# event_count = self.db.get(sql_cmd_category, tid, start_time, end_time, category)
# counts.append(event_count.count)
for r in res:
graphics.append(sum(r['events'].itervalues()))
event = r['events']
for key in CATEGORY_KEY:
counts_dct[key] += event[key]
for key in CATEGORY_KEY:
counts.append(counts_dct[key])
elif statistics_type == UWEB.STATISTICS_TYPE.MONTH:
label = data.year + u'年' + data.month + u'月'
start_time, end_time = start_end_of_month(year=data.year, month=data.month)
days = days_of_month(year=data.year, month=data.month)
for day in range(1,days+1):
start_time_, end_time_ = start_end_of_day(year=data.year, month=data.month, day=str(day))
if start_time_ > current_time:
break
re = {}
re['name'] = str(day)
events = {}
for key in CATEGORY_KEY:
res_item = {}
item = self.db.get(sql_cmd_category, tid, start_time_, end_time_, CATEGORY_DCT[key])
res_item[key] = item.count
events.update(res_item)
re['events'] = events
res.append(re)
for r in res:
graphics.append(sum(r['events'].itervalues()))
event = r['events']
for key in CATEGORY_KEY:
counts_dct[key] += event[key]
for key in CATEGORY_KEY:
counts.append(counts_dct[key])
else:
logging.error("[UWEB] Error statistics type: %s", statistics_type)
if page_count == -1:
items_count = len(res)
d, m = divmod(items_count, page_size)
page_count = (d + 1) if m else d
# store resutl in redis
m = hashlib.md5()
m.update(self.request.body)
hash_ = m.hexdigest()
mem_key = self.KEY_TEMPLATE % (self.current_user.uid, hash_)
self.redis.setvalue(mem_key, (res, counts, label), time=UWEB.STATISTIC_INTERVAL)
res= res[page_number*page_size:(page_number+1)*page_size]
self.write_ret(status,
dict_=dict(res=res,
counts=counts,
graphics=graphics,
pagecnt=page_count,
hash_=hash_))
except Exception as e:
logging.exception("[UWEB] event statistic, uid:%s, tid:%s failed. Exception: %s",
self.current_user.uid, self.current_user.tid, e.args)
status = ErrorCode.SERVER_BUSY
self.write_ret(status)
def handle_mileage(self, pvt):
"""Check the mileage.
"""
flag = self.check_timestamp(int(pvt['gps_time']))
if not flag:
return
#NOTE: record the mileage
tid = pvt['dev_id']
mileage_key = get_mileage_key(tid)
mileage = self.redis.getvalue(mileage_key)
if not mileage:
logging.info("[EVENTER] Tid: %s, init mileage. pvt: %s.",
tid, pvt)
mileage = dict(lat=pvt.get('lat'),
lon=pvt.get('lon'),
dis=0,
gps_time=pvt['gps_time'])
self.redis.setvalue(mileage_key, mileage)
else:
if pvt['gps_time'] < mileage['gps_time']:
logging.info("[EVENTER] Tid: %s, gps_time: %s is less than mileage['gps_time']: %s, drop it. pvt: %s, mileage: %s",
tid,
pvt['gps_time'],
mileage['gps_time'],
pvt,
mileage)
pass
else:
dis = lbmphelper.get_distance(int(mileage["lon"]), int(mileage["lat"]), int(pvt["lon"]) , int(pvt["lat"]))
# for mileage notification
dis_current = mileage['dis'] + dis
self.db.execute("UPDATE T_TERMINAL_INFO"
" SET distance_current = %s"
" WHERE tid = %s",
dis_current, tid)
logging.info("[EVENTER] Tid: %s, distance: %s. pvt: %s.",
tid, dis_current, pvt)
mileage = dict(lat=pvt.get('lat'),
lon=pvt.get('lon'),
dis=dis_current,
gps_time=pvt['gps_time'])
self.redis.setvalue(mileage_key, mileage)
# for mileage junior statistic
current_day = time.localtime(pvt['gps_time'])
day_start_time, day_end_time = start_end_of_day(current_day.tm_year, current_day.tm_mon, current_day.tm_mday)
mileage_log = self.db.get("SELECT * FROM T_MILEAGE_LOG"
" WHERE tid = %s"
" AND timestamp = %s",
tid, day_end_time)
if mileage_log:
dis_day = mileage_log['distance'] + dis
else:
self.db.execute("INSERT INTO T_MILEAGE_LOG(tid, timestamp)"
" VALUES(%s, %s)",
tid, day_end_time)
dis_day = dis
self.db.execute("INSERT INTO T_MILEAGE_LOG(tid, distance, timestamp)"
" VALUES(%s, %s, %s)"
" ON DUPLICATE KEY"
" UPDATE distance=values(distance)",
tid, dis_day, day_end_time)
logging.info("[EVENTER] Tid: %s, dis_day: %s. pvt: %s.",
tid, dis_day, pvt)
def _on_finish(db):
try:
status = ErrorCode.SUCCESS
self.db = db
res = []
stop = []
track = []
track_sample = UWEB.TRACK_SAMPLE.NO
# 2014.08.01 a week.
if ((start_time < LIMIT.MASS_POINT_QUERY_TIME) and
((end_time - start_time) > LIMIT.MASS_POINT_QUERY_INTERVAL)):
status = ErrorCode.MASS_POINT_QUERY_EXCESS
self.write_ret(status)
self.finish()
return
for item in range(days):
timestamp = start_time + 1 * 60 * 60 * 24 * (item)
date = get_date_from_utc(timestamp)
year, month, day = date.year, date.month, date.day
start_time_, end_time_ = start_end_of_day(
year=year, month=month, day=day)
# NOTE: handle for the first and last point
if item == 0:
start_time_ = start_time
elif item == days - 1:
end_time_ = end_time
if cellid_flag == 1: # cellid
track = self.get_track(
tid, start_time_, end_time_, cellid=True)
else: # gps
# cellid_flag is None or 0, only gps track
track = self.get_track(
tid, start_time_, end_time_, cellid=False)
if track:
last_point = track[-1]
last_point = get_locations_with_clatlon(
[last_point], self.db)[0]
distance = self.get_track_distance(track)
r = dict(timestamp=last_point.timestamp,
distance=distance,
latitude=last_point.latitude,
longitude=last_point.longitude,
clatitude=last_point.clatitude,
clongitude=last_point.clongitude,
name=self.get_track_name(last_point))
else:
r = dict(timestamp=end_time_,
distance=0,
latitude=0,
longitude=0,
clatitude=0,
clongitude=0,
name=u'')
res.append(r)
if cellid_flag == 1: # cellid
track = self.get_track(
tid, start_time, end_time, cellid=True)
else: # gps
# cellid_flag is None or 0, only gps track
track = self.get_track(
tid, start_time, end_time, cellid=False)
if len(track) > LIMIT.MASS_POINT_NUMBER: # > 1000
track_sample = UWEB.TRACK_SAMPLE.YES
track = get_sampled_list(track, LIMIT.MASS_POINT_NUMBER)
stop = self.get_stop_point(tid, start_time, end_time)
res.reverse()
stop.reverse()
self.write_ret(status,
dict_=DotDict(res=res,
stop=stop,
track=track,
track_sample=track_sample))
self.finish()
except Exception as e:
status = ErrorCode.SERVER_BUSY
logging.exception("[UWEB] Mass-point day request failed. "
" uid: %s, tid: %s. Exception: %s.",
self.current_user.uid, tid, e.args)
self.write_ret(status)
self.finish()
def _on_finish(db):
self.db = db
page_count = int(data.pagecnt)
if statistic_mode == 'all': # all
if page_count == -1:
count = len(tids)
d, m = divmod(count, page_size)
page_count = (d + 1) if m else d
reports = []
for item, tid in enumerate(tids):
seq=item+1
dis_sum = Decimal()
start_date = get_date_from_utc(start_time)
end_date = get_date_from_utc(end_time)
start_day = datetime.datetime.fromtimestamp(start_time)
end_day = datetime.datetime.fromtimestamp(end_time)
# get how many days the end_time and start_time cover
days = abs(end_day-start_day).days+1
for item in range(days):
distance = Decimal()
timestamp = start_time+1*60*60*24*(item)
date = get_date_from_utc(timestamp)
year, month, day = date.year, date.month, date.day
start_time_, end_time_ = start_end_of_day(year=year, month=month, day=day)
points = self.db.query("SELECT longitude, latitude FROM T_LOCATION"
" WHERE tid = %s"
" AND (timestamp BETWEEN %s AND %s)"
" AND type = 0"
" ORDER BY timestamp asc",
tid, start_time_+start_period_, start_time_+end_period_)
for i in range(len(points)-1):
if points[i].longitude and points[i].latitude and \
points[i+1].longitude and points[i+1].latitude:
dis = get_distance(points[i].longitude, points[i].latitude,
points[i+1].longitude, points[i+1].latitude)
distance += Decimal(str(dis))
# meter --> km
distance = '%0.1f' % (distance/1000,)
dis_sum += Decimal(distance)
alias = QueryHelper.get_alias_by_tid(tid, self.redis, self.db)
dct = dict(seq=seq,
alias=alias,
distance=float(dis_sum))
reports.append(dct)
# orgnize and store the data to be downloaded
m = hashlib.md5()
m.update(self.request.body)
hash_ = m.hexdigest()
mem_key = self.KEY_TEMPLATE % (self.current_user.uid, hash_)
self.redis.setvalue(mem_key, (statistic_mode, reports, 0), time=UWEB.STATISTIC_INTERVAL)
reports= reports[(page_number * page_size):((page_number+1) * page_size)]
self.write_ret(status,
dict_=DotDict(res=reports,
pagecnt=page_count,
hash_=hash_))
else: # single
tid = tids[0]
delta = end_time - start_time # end_time must bigger than start_time
d, m = divmod(delta, 60*60*24)
start_date = get_date_from_utc(start_time)
end_date = get_date_from_utc(end_time)
start_day = datetime.datetime.fromtimestamp(start_time)
end_day = datetime.datetime.fromtimestamp(end_time)
# get how many days the end_time and start_time cover
days = abs(end_day-start_day).days+1
#if days == 0:
# if start_date.day == end_date.day:
# days = 1
# else:
# days = 2
#else:
# days = days+1 if m else days
# if end_day.hour*60*60 + end_day.minute*60 + end_day.second < start_day.hour*60*60 + start_day.minute*60 + start_day.second:
# days = days+1
res = []
graphics = []
counts = []
dis_sum = Decimal()
current_time = int(time.time())
sql_cmd = ("SELECT longitude, latitude FROM T_LOCATION"
" WHERE tid = %s"
" AND (timestamp BETWEEN %s AND %s)"
" AND type = 0"
" ORDER BY timestamp asc")
#last_cmd = ("SELECT timestamp FROM T_LOCATION"
# " WHERE tid = %s"
# " AND (timestamp BETWEEN %s AND %s)"
# " AND type = 0"
# " ORDER BY timestamp desc limit 1")
#next_cmd = ("SELECT timestamp FROM T_LOCATION"
# " WHERE tid = %s"
# " AND (timestamp BETWEEN %s AND %s)"
# " AND type = 0"
# " ORDER BY timestamp asc limit 1")
if days == 1: # start_time, end_time in the same day
timestamp = start_time
date = get_date_from_utc(timestamp)
re = {}
re['alias'] = '-'.join([str(date.year),str(date.month),str(date.day)])
distance = Decimal()
points = self.db.query(sql_cmd, tid, start_time+start_period_, start_time+end_period_)
for i in range(len(points)-1):
if points[i].longitude and points[i].latitude and \
points[i+1].longitude and points[i+1].latitude:
dis = get_distance(points[i].longitude, points[i].latitude,
points[i+1].longitude, points[i+1].latitude)
distance += Decimal(str(dis))
# meter --> km
distance = '%0.1f' % (distance/1000,)
graphics.append(float(distance))
dis_sum += Decimal(distance)
re['distance'] = distance
re['seq'] = 1
res.append(re)
else: # start_time, end_time in different days
for item in range(days):
timestamp = start_time+1*60*60*24*(item)
date = get_date_from_utc(timestamp)
year, month, day = date.year, date.month, date.day
start_time_, end_time_ = start_end_of_day(year=year, month=month, day=day)
## handle the first day and last day
#if item == 0:
# start_time_ = start_time
#if item == days:
# end_time_ = end_time
#last_point = self.db.get(last_cmd, tid, start_time_-60*60*24, start_time_,)
#next_point = self.db.get(next_cmd, tid, end_time_, end_time_+60*60*24)
#start_time_ = last_point['timestamp'] if last_point else start_time_
#end_time_ = next_point['timestamp'] if next_point else end_time_
re = {}
re['alias'] = '-'.join([str(year),str(month),str(day)])
distance = Decimal()
points = self.db.query(sql_cmd, tid, start_time_+start_period_, start_time_+end_period_)
for i in range(len(points)-1):
if points[i].longitude and points[i].latitude and \
points[i+1].longitude and points[i+1].latitude:
dis = get_distance(points[i].longitude, points[i].latitude,
points[i+1].longitude, points[i+1].latitude)
distance += Decimal(str(dis))
# meter --> km
distance = '%0.1f' % (distance/1000,)
graphics.append(float(distance))
dis_sum += Decimal(distance)
re['distance'] = distance
re['seq'] = item+1
res.append(re)
counts = [float(dis_sum),]
if page_count == -1:
items_count = len(res)
d, m = divmod(items_count, page_size)
page_count = (d + 1) if m else d
# store resutl in redis
m = hashlib.md5()
m.update(self.request.body)
hash_ = m.hexdigest()
mem_key = self.KEY_TEMPLATE % (self.current_user.uid, hash_)
self.redis.setvalue(mem_key, (statistic_mode, res, counts,), time=UWEB.STATISTIC_INTERVAL)
res= res[page_number*page_size:(page_number+1)*page_size]
self.write_ret(status,
dict_=dict(res=res,
counts=counts,
graphics=graphics,
pagecnt=page_count,
hash_=hash_))
self.finish()
def post(self):
"""Provide statistics about terminal.
"""
status = ErrorCode.SUCCESS
try:
data = DotDict(json_decode(self.request.body))
logging.info("[UWEB] statistic request: %s, uid: %s, tid: %s",
data, self.current_user.uid, self.current_user.tid)
except Exception as e:
status = ErrorCode.ILLEGAL_DATA_FORMAT
self.write_ret(status)
return
try:
page_size = UWEB.LIMIT.PAGE_SIZE_STATISTICS
page_number = int(data.pagenum)
page_count = int(data.pagecnt)
tid = data.tid
res = []
graphics = []
counts = []
label = u''
sql_cmd = ("SELECT longitude, latitude FROM T_LOCATION"
" WHERE tid = %s"
" AND (timestamp BETWEEN %s AND %s)"
" AND type = 0"
" ORDER BY timestamp asc")
last_cmd = ("SELECT timestamp FROM T_LOCATION"
" WHERE tid = %s"
" AND (timestamp BETWEEN %s AND %s)"
" AND type = 0"
" ORDER BY timestamp desc limit 1")
next_cmd = ("SELECT timestamp FROM T_LOCATION"
" WHERE tid = %s"
" AND (timestamp BETWEEN %s AND %s)"
" AND type = 0"
" ORDER BY timestamp asc limit 1")
dis_sum = Decimal()
current_time = int(time.time())
statistics_type = data.statistics_type
if statistics_type == UWEB.STATISTICS_TYPE.YEAR:
label = data.year + u'年'
start_time, end_time = start_end_of_year(year=data.year)
for month in range(1,12+1):
start_time_, end_time_ = start_end_of_month(year=data.year, month=str(month))
if start_time_ > current_time:
break
re = {}
re['name'] = str(month)
distance = Decimal()
points = self.db.query(sql_cmd, tid, start_time_, end_time_)
for i in range(len(points)-1):
if points[i].longitude and points[i].latitude and \
points[i+1].longitude and points[i+1].latitude:
dis = get_distance(points[i].longitude, points[i].latitude,
points[i+1].longitude, points[i+1].latitude)
distance += Decimal(str(dis))
# meter --> km
distance = '%0.1f' % (distance/1000,)
if float(distance) == 0:
distance = 0
graphics.append(float(distance))
dis_sum += Decimal(distance)
re['mileage'] = distance
res.append(re)
counts = [float(dis_sum),]
elif statistics_type == UWEB.STATISTICS_TYPE.MONTH:
label = data.year + u'年' + data.month + u'月'
start_time, end_time = start_end_of_month(year=data.year, month=data.month)
days = days_of_month(year=data.year, month=data.month)
distance = Decimal()
points_ = self.db.query(sql_cmd, tid, start_time, end_time)
for i in range(len(points_)-1):
if points_[i].longitude and points_[i].latitude and \
points_[i+1].longitude and points_[i+1].latitude:
dis = get_distance(points_[i].longitude, points_[i].latitude,
points_[i+1].longitude, points_[i+1].latitude)
dis=Decimal(str(dis))
distance += dis
distance = '%0.1f' % (distance/1000,)
dis_sum = distance
for day in range(1,days+1):
start_time_, end_time_ = start_end_of_day(year=data.year, month=data.month, day=str(day))
if start_time_ > current_time:
break
last_point = self.db.get(last_cmd, tid, start_time_-60*60*24, start_time_,)
next_point = self.db.get(next_cmd, tid, end_time_, end_time_+60*60*24)
start_time_ = last_point['timestamp'] if last_point else start_time_
end_time_ = next_point['timestamp'] if next_point else end_time_
re = {}
re['name'] = str(day)
distance = Decimal()
points = self.db.query(sql_cmd, tid, start_time_, end_time_)
for i in range(len(points)-1):
if points[i].longitude and points[i].latitude and \
points[i+1].longitude and points[i+1].latitude:
dis = get_distance(points[i].longitude, points[i].latitude,
points[i+1].longitude, points[i+1].latitude)
distance += Decimal(str(dis))
# meter --> km
distance = '%0.1f' % (distance/1000,)
if float(distance) == 0:
distance = 0
graphics.append(float(distance))
re['mileage'] = distance
res.append(re)
counts = [float(dis_sum),]
else:
logging.error("[UWEB] Error statistics type: %s", statistics_type)
if page_count == -1:
items_count = len(res)
d, m = divmod(items_count, page_size)
page_count = (d + 1) if m else d
# store resutl in redis
m = hashlib.md5()
m.update(self.request.body)
hash_ = m.hexdigest()
mem_key = self.KEY_TEMPLATE % (self.current_user.uid, hash_)
self.redis.setvalue(mem_key, (res, counts, label, statistics_type), time=UWEB.STATISTIC_INTERVAL)
res= res[page_number*page_size:(page_number+1)*page_size]
self.write_ret(status,
dict_=dict(res=res,
counts=counts,
graphics=graphics,
pagecnt=page_count,
hash_=hash_))
except Exception as e:
logging.exception("[UWEB] uid:%s, tid:%s statistic failed. Exception: %s",
self.current_user.uid, self.current_user.tid, e.args)
status = ErrorCode.SERVER_BUSY
self.write_ret(status)
def post(self):
"""Provide statistics about terminal.
"""
status = ErrorCode.SUCCESS
try:
data = DotDict(json_decode(self.request.body))
logging.info("[UWEB] Single statistic request: %s, uid: %s, tid: %s",
data, self.current_user.uid, self.current_user.tid)
except Exception as e:
status = ErrorCode.ILLEGAL_DATA_FORMAT
self.write_ret(status)
return
try:
page_size = UWEB.LIMIT.PAGE_SIZE_STATISTICS
page_number = int(data.pagenum)
page_count = int(data.pagecnt)
tid = data.tid
res = []
graphics = []
counts = []
label = u''
dis_sum = Decimal()
current_time = int(time.time())
statistics_type = data.statistics_type
if statistics_type == UWEB.STATISTICS_TYPE.YEAR:
label = data.year + u'年'
start_time, end_time = start_end_of_year(year=data.year)
for month in range(1, 12 + 1):
start_time_, end_time_ = start_end_of_month(
year=data.year, month=str(month))
if start_time_ > current_time:
break
re = {}
re['name'] = str(month)
distance = Decimal()
mileage_log = self.db.get("SELECT SUM(distance) AS distance "
" FROM T_MILEAGE_LOG"
" WHERE tid = %s"
" AND (timestamp BETWEEN %s AND %s)",
tid, start_time_, end_time_)
if mileage_log and mileage_log['distance']:
distance = '%0.1f' % (
Decimal(mileage_log['distance']) / 1000,)
else:
distance = 0
graphics.append(float(distance))
dis_sum += Decimal(distance)
re['mileage'] = float(distance)
res.append(re)
counts = [float(dis_sum), ]
elif statistics_type == UWEB.STATISTICS_TYPE.MONTH:
label = data.year + u'年' + data.month + u'月'
start_time, end_time = start_end_of_month(
year=data.year, month=data.month)
days = days_of_month(year=data.year, month=data.month)
distance = Decimal()
for day in range(1, days + 1):
start_time_, end_time_ = start_end_of_day(
year=data.year, month=data.month, day=str(day))
if start_time_ > current_time:
break
re = {}
re['name'] = str(day)
distance = Decimal()
# NOTE:TODO get mileage
mileage_log = self.db.get("SELECT distance FROM T_MILEAGE_LOG"
" WHERE tid = %s"
" AND timestamp = %s",
tid, end_time_)
distance = mileage_log['distance'] if mileage_log else 0
# meter --> km
distance = '%0.1f' % (Decimal(distance) / 1000,)
graphics.append(float(distance))
dis_sum += Decimal(distance)
re['mileage'] = float(distance)
res.append(re)
counts = [float(dis_sum), ]
else:
logging.error(
"[UWEB] Error statistics type: %s", statistics_type)
if page_count == -1:
items_count = len(res)
d, m = divmod(items_count, page_size)
page_count = (d + 1) if m else d
# store resutl in redis
m = hashlib.md5()
m.update(self.request.body)
hash_ = m.hexdigest()
mem_key = self.KEY_TEMPLATE % (self.current_user.uid, hash_)
self.redis.setvalue(
mem_key, (res, counts, label, statistics_type), time=UWEB.STATISTIC_INTERVAL)
res = res[page_number * page_size:(page_number + 1) * page_size]
self.write_ret(status,
dict_=dict(res=res,
counts=counts,
graphics=graphics,
pagecnt=page_count,
hash_=hash_))
except Exception as e:
logging.exception("[UWEB] Statistic single failed. uid:%s, tid:%s, Exception: %s",
self.current_user.uid, self.current_user.tid, e.args)
status = ErrorCode.SERVER_BUSY
self.write_ret(status)
def post(self):
"""Retrieve the password."""
status = ErrorCode.SUCCESS
try:
data = DotDict(json_decode(self.request.body))
umobile = data.mobile
captcha_psd = data.get('captcha_psd','')
captchahash = self.get_secure_cookie("captchahash_password")
logging.info("[UWEB] Corp retrieve password request: %s", data)
except Exception as e:
status = ErrorCode.ILLEGAL_DATA_FORMAT
logging.exception("[UWEB] Invalid data format. body: %s, Exception: %s",
self.request.body, e.args)
self.write_ret(status)
return
try:
# check the umobile whether belongs to guandong
is_guandong = check_gd_phone(umobile)
if is_guandong:
pass
else:
logging.info("[UWEB] Mobile is not come from GuanDong, reject it.")
status = ErrorCode.UMOBILE_REGISTER_EXCESS
self.write_ret(status)
return
#NOTE: check captcha-sms for brower
from_brower = False
if self.request.headers.get('User-Agent',None):
user_agent = self.request.headers.get('User-Agent').lower()
if re.search('darwin', user_agent): # Ios client
logging.info("[UWEB] Come from IOS client, do not check captcha-image, User-Agent: %s",
user_agent)
from_brower = False
else:
logging.info("[UWEB] Come from browser, check captcha-image, User-Agent: %s",
user_agent)
from_brower = True
else: # Android client
from_brower = False
logging.info("[UWEB] Come from Android client, do not check captcha-image")
if from_brower:
m = hashlib.md5()
m.update(captcha_psd.lower())
m.update(UWEB.HASH_SALT)
hash_ = m.hexdigest()
if hash_.lower() != captchahash.lower():
status = ErrorCode.WRONG_CAPTCHA_IMAGE
logging.info("[UWEB] Come from browser, captcha-check failed.")
self.write_ret(status)
return
user = self.db.get("SELECT mobile"
" FROM T_CORP"
" WHERE cid = %s"
" LIMIT 1",
umobile)
if not user:
user = self.db.get("SELECT mobile"
" FROM T_OPERATOR"
" WHERE oid = %s"
" LIMIT 1",
umobile)
if user:
remote_ip = self.request.remote_ip
remote_ip_key = "register_remote_ip:%s" % remote_ip
umobile_key = "register_umobile:%s" % umobile
remote_ip_times = self.redis.getvalue(remote_ip_key)
umobile_times = self.redis.getvalue(umobile_key)
if remote_ip_times is None:
remote_ip_times = 0
if umobile_times is None:
umobile_times = 0
logging.info("[UWEB] Register. umobile: %s, umobile_times: %s, remote_ip: %s, remote_ip_times: %s",
umobile, umobile_times, remote_ip, remote_ip_times)
#NOTE: In current day, the same remote_ip allows 10 times, the umobile, 3 times
current_time = int(time.time())
date = get_date_from_utc(current_time)
year, month, day = date.year, date.month, date.day
start_time_, end_time_ = start_end_of_day(year=year, month=month, day=day)
if umobile_times >= 3: # <= 3 is ok
status = ErrorCode.REGISTER_EXCESS
if remote_ip_times >= 10: # <= 10 is ok
status = ErrorCode.REGISTER_EXCESS
if status == ErrorCode.REGISTER_EXCESS:
body = u'管理员您好：检测到频繁注册，请查看. umobile: %s, umobile_times: %s, remote_ip: %s, remote_ip_times: %s' % (
umobile, umobile_times, remote_ip, remote_ip_times)
notify_maintainer(self.db, self.redis, body, 'password')
self.write_ret(status)
return
captcha = ''.join(random.choice(string.digits) for x in range(4))
getcaptcha_sms = SMSCode.SMS_CAPTCHA % (captcha)
ret = SMSHelper.send(umobile, getcaptcha_sms)
ret = DotDict(json_decode(ret))
if ret.status == ErrorCode.SUCCESS:
logging.info("[UWEB] corp mobile: %s get captcha success, the captcha: %s",
umobile, captcha)
captcha_key = get_captcha_key(umobile)
self.redis.setvalue(captcha_key, captcha, UWEB.SMS_CAPTCHA_INTERVAL)
self.redis.set(umobile_key, umobile_times+1)
self.redis.expireat(umobile_key, end_time_)
self.redis.set(remote_ip_key, remote_ip_times+1)
self.redis.expireat(remote_ip_key, end_time_)
else:
status = ErrorCode.SERVER_BUSY
logging.error("[UWEB] Get captcha failed. corp mobile: %s",
umobile)
else:
logging.error("[UWEB] Get captcha failed. corp mobile: %s does not exist.",
umobile)
status = ErrorCode.USER_NOT_ORDERED
self.write_ret(status)
except Exception as e:
logging.exception("[UWEB] Get captcha failed. corp mobile: %s, Exception: %s",
umobile, e.args)
status = ErrorCode.SERVER_BUSY
self.write_ret(status)