Exemplo n.º 1
0
    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
Exemplo n.º 2
0
    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
Exemplo n.º 3
0
    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
Exemplo n.º 4
0
Arquivo: msms.py Projeto: jcsy521/ydws
    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 
Exemplo n.º 5
0
    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