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 retrieve_mixin(self, citylist=None, start_time=None, end_time=None):
results = []
d = datetime.date.fromtimestamp(start_time/1000)
year = d.year
month = d.month
cities = city_info(citylist, self.mysql_db)
#optype_status = (XXT.OPER_TYPE.CREATE, XXT.OPER_TYPE.RESUME, XXT.OPER_TYPE.UPDATE)
for city in cities:
#new_groups = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_XXT_GROUP"
# " WHERE timestamp BETWEEN %s AND %s"
# " AND city_id = %s",
# start_time, end_time, city.id)
new_groups = self.mysql_db.get("call P_SUBSCRIBER_NEW_GROUPS(%s, %s, %s)",
start_time, end_time, city.id)
#new_parents = self.mysql_db.get("SELECT count(T_XXT_USER.xxt_uid) as total"
# " FROM T_XXT_USER, T_XXT_GROUP"
# " WHERE T_XXT_USER.timestamp BETWEEN %s AND %s"
# " AND optype IN %s"
# " AND group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# start_time, end_time, tuple(optype_status), city.id)
#NOTE: change utc to the format as 201201312359590000
#only do this way when dealing with timestamp of T_XXT_USER and T_XXT_SUBSCRIPTION_LOG
start_time_parent = time.strftime("%Y%m%d%H%M%S0000", time.localtime(start_time/1000))
end_time_parent = time.strftime("%Y%m%d%H%M%S9999", time.localtime(start_time/1000))
new_parents = self.mysql_db.get("call P_SUBSCRIBER_NEW_PARENTS(%s, %s, %s)",
start_time_parent, end_time_parent, city.id)
#new_children = self.mysql_db.get("SELECT count(T_XXT_TARGET.xxt_tid) as total"
# " FROM T_XXT_TARGET, T_XXT_GROUP"
# " WHERE T_XXT_TARGET.timestamp BETWEEN %s AND %s"
# " AND optype IN %s"
# " AND group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# start_time, end_time, tuple(optype_status), city.id)
new_children = self.mysql_db.get("call P_SUBSCRIBER_NEW_CHILDREN(%s, %s, %s)",
start_time, end_time, city.id)
#total_groups = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_XXT_GROUP"
# " WHERE timestamp <= %s"
# " AND city_id = %s",
# end_time, city.id)
total_groups = self.mysql_db.get("call P_SUBSCRIBER_TOTAL_GROUPS(%s, %s)",
end_time, city.id)
#total_parents = self.mysql_db.get("SELECT count(T_XXT_USER.xxt_uid) as total"
# " FROM T_XXT_USER, T_XXT_GROUP"
# " WHERE T_XXT_USER.timestamp <= %s"
# " AND optype IN %s"
# " AND group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# end_time, tuple(optype_status), city.id)
total_parents = self.mysql_db.get("call P_SUBSCRIBER_TOTAL_PARENTS(%s, %s)",
end_time_parent, city.id)
#total_children = self.mysql_db.get("SELECT count(T_XXT_TARGET.xxt_tid) as total"
# " FROM T_XXT_TARGET, T_XXT_GROUP"
# " WHERE T_XXT_TARGET.timestamp <= %s"
# " AND optype IN %s"
# " AND group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# end_time, tuple(optype_status), city.id)
total_children = self.mysql_db.get("call P_SUBSCRIBER_TOTAL_CHILDREN(%s, %s)",
end_time, city.id)
res = {'_id': None,
'province_id':city.p_id,
'city_id': city.id,
'province':city.p_name,
'city':city.name,
'new_groups': new_groups.total,
'new_parents': new_parents.total,
'new_children': new_children.total,
'total_groups': total_groups.total,
'total_parents': total_parents.total,
'total_children': total_children.total,
'year': year,
'month': month}
results.append(res)
return results
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
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 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:
#sql = "SELECT count(*) AS new_groups" + \
# " FROM T_XXT_GROUP" + \
# " WHERE timestamp BETWEEN %s AND %s" + \
# " AND city_id = %s"
#sql = sql % (start_time, end_time, city.id)
#new_groups = self.mysql_db.get(sql)
new_groups = self.mysql_db.get("call P_MONTHLY_NEW_GROUPS(%s, %s, %s)",
start_time, end_time, city.id)
#sql = "SELECT count(T_XXT_USER.xxt_uid) as total" + \
# " FROM T_XXT_USER, T_XXT_GROUP" + \
# " WHERE T_XXT_USER.timestamp BETWEEN %s AND %s" + \
# " AND optype IN (1,3,4)" + \
# " AND group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s"
#sql = sql % (start_time, end_time, XXT.VALID.VALID, city.id)
#new_parents = self.mysql_db.get(sql)
#NOTE: change utc to the format as 201201312359590000
#only do this way when dealing with time of T_XXT_USER and T_XXT_SUBSCRIPTION_LOG
start_time_parent = time.strftime("%Y%m%d%H%M%S0000", time.localtime(start_time/1000))
end_time_parent = time.strftime("%Y%m%d%H%M%S9999", time.localtime(start_time/1000))
new_parents = self.mysql_db.get("call P_MONTHLY_NEW_PARENTS(%s, %s, %s)",
start_time_parent, end_time_parent, city.id)
#sql = "SELECT count(T_XXT_TARGET.xxt_tid) as total" + \
# " FROM T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE T_XXT_TARGET.timestamp BETWEEN %s AND %s" + \
# " AND valid = %s" + \
# " AND group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s"
#sql = sql % (start_time, end_time, XXT.VALID.VALID, city.id)
#new_children = self.mysql_db.get(sql)
new_children = self.mysql_db.get("call P_MONTHLY_NEW_CHILDREN(%s, %s, %s)",
start_time, end_time, city.id)
#sql = "SELECT count(*) as total" + \
# " FROM T_XXT_GROUP" + \
# " WHERE timestamp <= %s" + \
# " AND city_id = %s"
#sql = sql % (end_time, city.id)
#total_groups = self.mysql_db.get(sql)
total_groups = self.mysql_db.get("call P_MONTHLY_TOTAL_GROUPS(%s, %s)",
end_time, city.id)
#sql = "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 (1, 3, 4)" + \
# " AND group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s"
#sql = sql % (end_time, XXT.VALID.VALID, city.id)
#total_parents = self.mysql_db.get(sql)
total_parents = self.mysql_db.get("call P_MONTHLY_TOTAL_PARENTS(%s, %s)",
end_time_parent, city.id)
#sql = "SELECT count(T_XXT_TARGET.xxt_tid) as total" + \
# " FROM T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE T_XXT_TARGET.timestamp <= %s" + \
# " AND optyped IN (1, 3, 4)" + \
# " AND group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s"
#sql = sql % (end_time, XXT.VALID.VALID, city.id)
#total_children = self.mysql_db.get(sql)
total_children = self.mysql_db.get("call P_MONTHLY_TOTAL_CHILDREN(%s, %s)",
end_time, city.id)
#sql = "SELECT count(T_SMS_LOG.id) as total" + \
# " FROM T_SMS_LOG" + \
# " WHERE T_SMS_LOG.category = %s" + \
# " AND T_SMS_LOG.fetchtime BETWEEN %s AND %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)))"
#sql = sql % (SMS.CATEGORY.RECEIVE, start_time, end_time, city.id, city.id)
#mo_sms = self.mysql_db.get(sql)
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 BETWEEN %s AND %s"
" AND T_SMS_LOG.mobile IN %s",
SMS.CATEGORY.RECEIVE, start_time, end_time,
tuple(mobiles + DUMMY_IDS))
#mo_sms = self.mysql_db.get("call P_MONTHLY_MO_SMS(%s, %s, %s, %s)",
# SMS.CATEGORY.RECEIVE, start_time, end_time, city.id)
#sql = "SELECT count(T_SMS_LOG.id) as total" + \
# " FROM T_SMS_LOG" + \
# " WHERE T_SMS_LOG.category = %s" + \
# " AND T_SMS_LOG.fetchtime BETWEEN %s AND %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)))"
#sql = sql % (SMS.CATEGORY.SEND, start_time, end_time, city.id, city.id)
#mt_sms = self.mysql_db.get(sql)
# mt_sms and mo_sms use the same procedure#
#mt_sms = self.mysql_db.get("call P_MONTHLY_MO_SMS(%s, %s, %s, %s)",
# SMS.CATEGORY.SEND, start_time, 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 BETWEEN %s AND %s"
" AND T_SMS_LOG.mobile IN %s",
SMS.CATEGORY.SEND, start_time, end_time,
tuple(mobiles + DUMMY_IDS))
# In fact, total_sms = mo_sms + mt_sms
# there is of no necessity to query the database
#sql = "SELECT count(T_SMS_LOG.id) as total" + \
# " FROM T_SMS_LOG" + \
# " WHERE T_SMS_LOG.fetchtime BETWEEN %s AND %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)))"
#sql = sql % (start_time, end_time, city.id, city.id)
#total_sms = self.mysql_db.get(sql)
#sql = "SELECT count(*) as total" + \
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE category = %s" + \
# " AND T_LOCATION.timestamp BETWEEN %s AND %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"
#sql = sql % (LOCATION.CATEGORY.CUSTOM, start_time, end_time, city.id)
#custom = self.mysql_db.get(sql)
#custom = self.mysql_db.get("call P_MONTHLY_CUSTOM(%s, %s, %s, %s)",
# LOCATION.CATEGORY.CUSTOM, start_time, end_time, city.id)
# custom, schedule and realtime use the same schedule
#sql = "SELECT count(*) as total" + \
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE category = %s" + \
# " AND T_LOCATION.timestamp BETWEEN %s AND %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"
#sql = sql % (LOCATION.CATEGORY.SCHEDULE, start_time, end_time, city.id)
#schedule = self.mysql_db.get(sql)
schedule = self.mysql_db.get("call P_MONTHLY_CUSTOM(%s, %s, %s, %s)",
LOCATION.CATEGORY.SCHEDULE, start_time, end_time, city.id)
#sql = "SELECT count(*) as total" + \
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE category = %s" + \
# " AND T_LOCATION.timestamp BETWEEN %s AND %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"
#sql = sql % (LOCATION.CATEGORY.REALTIME, start_time, end_time, city.id)
#realtime = self.mysql_db.get(sql)
realtime = self.mysql_db.get("call P_MONTHLY_CUSTOM(%s, %s, %s, %s)",
LOCATION.CATEGORY.REALTIME, start_time, end_time, city.id)
#sql = "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 BETWEEN %s AND %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"
#sql = sql % (LOCATION.CATEGORY.REGION_ENTER,
# LOCATION.CATEGORY.REGION_OUT, start_time, end_time, city.id)
#bound = self.mysql_db.get(sql)
bound = self.mysql_db.get("call P_MONTHLY_BOUND(%s, %s, %s, %s, %s)",
LOCATION.CATEGORY.REGION_ENTER, LOCATION.CATEGORY.REGION_OUT, start_time, end_time, city.id)
#sql = "SELECT count(*) as total" + \
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE category = %s" + \
# " AND T_LOCATION.timestamp BETWEEN %s AND %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"
#sql = sql % (LOCATION.CATEGORY.POWERLOW, start_time, end_time, city.id)
#power = self.mysql_db.get(sql)
power = self.mysql_db.get("call P_MONTHLY_POWER(%s, %s, %s, %s)",
LOCATION.CATEGORY.POWERLOW, start_time, end_time, city.id)
#sql = "SELECT count(*) as total" + \
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE category = %s" + \
# " AND T_LOCATION.timestamp BETWEEN %s AND %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"
#sql = sql % (LOCATION.CATEGORY.EMERGENCY, start_time, end_time, city.id)
#sos = self.mysql_db.get(sql)
sos = self.mysql_db.get("call P_MONTHLY_SOS(%s, %s, %s, %s)",
LOCATION.CATEGORY.EMERGENCY, start_time, end_time, city.id)
res = {'_id': None,
'province_id': city.p_id,
'city_id': city.id,
'province': city.p_name,
'city': city.name,
'new_groups': new_groups.total,
'new_parents': new_parents.total,
'new_children': new_children.total,
'total_groups': total_groups.total,
'total_parents': total_parents.total,
'total_children': total_children.total,
'total_sms': mt_sms.total + mo_sms.total,
'mt_sms': mt_sms.total,
'mo_sms': mo_sms.total,
#'custom': custom.total,
'schedule': schedule.total,
'realtime': realtime.total,
'bound': bound.total,
'sos': sos.total,
'power': power.total,
'year': year,
'month': month}
results.append(res)
return results