def show_comment(cls, comment_id_list, shop_id_list): customers = Customer.objects.filter(shop_id__in = shop_id_list).only('shop_id', 'nick') cust_dict = {obj.shop_id: obj.nick for obj in customers} psusers = PSUser.objects.all() psuser_dict = {obj.id: obj.name_cn for obj in psusers} # objs = Comment.objects.filter(id__in = comment_id_list) # 用这个有问题 object_id_list = [ObjectId(comment_id) for comment_id in comment_id_list] commment_cur = event_coll.find({'type': 'comment', '_id': {'$in': object_id_list}}) title_list = [u'创建时间', u'创建人', u'店铺名', u'事件类型', u'订购项', u'评价类型', u'踩评次数', u'改评价时间', ] data_list = [title_list] article_code_dict = dict(ARTICLE_CODE_CHOICES) comment_type_dict = dict(Comment.COMMENT_TYPE_CHOICES) for comment in commment_cur: data_list.append([comment['create_time'].strftime('%Y-%m-%d %H:%M'), comment['psuser_id'] and psuser_dict[comment['psuser_id']], cust_dict[comment['shop_id']], '评论事件', article_code_dict.get(comment.get('article_code', ''), '未知'), comment_type_dict.get(comment.get('comment_type', ''), '未知'), comment.get('top_comment_times', 0), comment['modify_comment_time'].strftime('%Y-%m-%d %H:%M') if 'modify_comment_time' in comment and comment['modify_comment_time'] else '未知', ]) return data_list, shop_id_list
def _unsubscribes_apportion(cls, psuser, start_date, end_date, result_mapping): start_time = datetime.datetime(start_date.year, start_date.month, start_date.day, 0, 0, 0) end_time = datetime.datetime(end_date.year, end_date.month, end_date.day, 23, 59, 59) unsub_cursor = event_coll.find( {'type':'unsubscribe', 'refund_date':{"$gte":start_time, "$lte":end_time}, '$or':[{'saler_id':psuser.id}, {'server_id':psuser.id}, {'psuser_id':psuser.id}], 'refund_reason':{'$lt':5} # , 'refund_type':{'$lt':3} }, {'shop_id': 1, 'psuser_id': 1, 'refund_date': 1, 'saler_id':1, 'server_id':1, 'saler_apportion': 1, 'server_apportion':1} ) for unsub in unsub_cursor: apportion = 0 if 'psuser_id' in unsub and psuser.id == unsub['psuser_id']: apportion += 3 if 'saler_id' in unsub and unsub['saler_id']: if psuser.id == unsub['saler_id']: apportion += 7 elif psuser.id == unsub['server_id']: apportion += 7 result_mapping[unsub['refund_date'].date()].append(DictWrapper({ '_id':unsub['_id'], 'shop_id':unsub['shop_id'], 'apportion':apportion })) return result_mapping
def __load_pause_event(cls, manager): '''加载暂停事件''' if not hasattr(manager, '_raw_pause_event'): manager._raw_pause_event = list( event_coll.find( { 'type': 'pause', 'create_time': { '$lt': date_2datetime(manager.end_date + datetime.timedelta(days=1)) }, '$or': [{ 'proceed_date': { '$gte': date_2datetime(manager.start_date + datetime.timedelta(days=1)) } }, { 'proceed_date': { '$exists': False } }] }, { 'event_id': True, 'create_time': True, 'proceed_date': True })) return manager._raw_pause_event
def Load_operate_Event(cls, customer_mapping): shop_id_list = customer_mapping.keys() operate_dict = {} for operate in event_coll.find( { 'type': 'operate', 'shop_id': { "$in": shop_id_list } }, { 'shop_id': 1, 'create_time': 1 }).sort("create_time", pymongo.DESCENDING): operate = DictWrapper(operate) operate_dict.setdefault(operate.shop_id, []).append(operate) today = datetime.datetime.now() for shop_id, customer in customer_mapping.iteritems(): customer.operate_events = operate_dict.get(shop_id, []) if customer.operate_events: customer.last_operate_days = ( today - customer.operate_events[0].create_time).days else: customer.last_operate_days = None return customer_mapping
def _unsubscribes(cls, psuser, start_date, end_date, result_mapping): start_time = datetime.datetime(start_date.year, start_date.month, start_date.day, 0, 0, 0) end_time = datetime.datetime(end_date.year, end_date.month, end_date.day, 23, 59, 59) # temp_dict = {} # for unsubscribe in event_coll.find({'type':'unsubscribe', 'refund_date':{"$gte":start_time, "$lte":end_time}}, {'shop_id': 1, 'event_id': 1, 'refund_date': 1, 'refund': 1}): # temp_dict[unsubscribe['event_id']] = DictWrapper(unsubscribe) # query_dict = {'create_time__lt':end_time, 'end_date__gt':start_date} # if psuser.position in ['CONSULT', 'CONSULTLEADER']: # query_dict['consult'] = psuser # else: # query_dict['operater'] = psuser # sub_id_list = Subscribe.objects.filter(**query_dict).values_list('id', flat = True) # for sub_id, unsubscribe in temp_dict.items(): # if sub_id in sub_id_list: # result_mapping[unsubscribe.refund_date.date()].append(unsubscribe) unsub_cursor = event_coll.find({'type':'unsubscribe', 'refund_date':{"$gte":start_time, "$lte":end_time}, '$or':[ {'saler_id':psuser.id, 'saler_apportion':{'$gt':0}}, {'server_id':psuser.id, 'server_apportion':{'$gt':0}} ], 'refund_reason':{'$lt':5} # , 'refund_type':{'$lt':3} }, {'shop_id': 1, 'refund_date': 1, 'saler_id':1, 'server_id':1, 'saler_apportion': 1, 'server_apportion':1}) for unsub in unsub_cursor: apportion = 0 if psuser.id == unsub['saler_id']: apportion += unsub.get('saler_apportion', 0) if psuser.id == unsub['server_id']: apportion += unsub.get('server_apportion', 0) result_mapping[unsub['refund_date'].date()].append(DictWrapper({ '_id':unsub['_id'], 'shop_id':unsub['shop_id'], 'apportion':apportion })) return result_mapping
def __load_unsub_event(cls, manager): '''加载退款事件''' if not hasattr(manager, '_raw_unsub_event'): manager._raw_unsub_event = list( event_coll.find( { 'type': 'unsubscribe', 'create_time': { '$gte': date_2datetime(manager.start_date), '$lt': date_2datetime(manager.end_date + datetime.timedelta(days=1)) }, 'refund_reason': { '$lt': 5 }, }, { 'event_id': True, 'shop_id': True, 'nick': True, 'create_time': True, 'category': True, 'refund': True, 'saler_id': True, 'saler_apportion': True, 'server_id': True, 'server_apportion': True })) return manager._raw_unsub_event
def _bad_comments(cls, xfgroup, start_date, end_date, result_mapping): start_time = datetime.datetime(start_date.year, start_date.month, start_date.day, 0, 0, 0) end_time = datetime.datetime(end_date.year, end_date.month, end_date.day, 23, 59, 59) comment_cur = event_coll.find( { 'type': 'comment', 'duty_xfgroup_id': xfgroup.id, 'comment_type': { '$in': [200, 302, 303, 304] }, 'current_version': 'kcjl', 'article_code': 'ts-25811', 'create_time': { '$gte': start_time, '$lte': end_time } }, { 'shop_id': 1, 'comment_type': 1, 'create_time': 1 }) for comment in comment_cur: comment = DictWrapper(comment) result_mapping[comment.create_time.date()].append(comment) return result_mapping
def _change_comments(cls, xfgroup, start_date, end_date, result_mapping): start_time = datetime.datetime(start_date.year, start_date.month, start_date.day, 0, 0, 0) end_time = datetime.datetime(end_date.year, end_date.month, end_date.day, 23, 59, 59) comment_cur = event_coll.find( { 'type': 'comment', 'xfgroup_id': xfgroup.id, 'comment_type': { '$in': [301, 302, 303, 304, 305] }, 'create_time': { '$gte': start_time, '$lte': end_time } }, { 'shop_id': 1, 'comment_type': 1, 'create_time': 1 }) for comment in comment_cur: comment = DictWrapper(comment) result_mapping[comment.create_time.date()].append(comment) return result_mapping
def main(): psuser_dept_dict = dict(PSUser.objects.values_list('id', 'department')) unsub_list = list(event_coll.find({'type':'unsubscribe', 'create_time':{'$gte':datetime.datetime(2016,1,1)}})) i = 0 j = len(unsub_list) for unsub in unsub_list: try: sub_obj = Subscribe.objects.select_related('shop').get(id=unsub['event_id']) except Exception, e: print e continue duty_dpt = '' if sub_obj.psuser_id: duty_dpt = psuser_dept_dict.get(sub_obj.psuser_id, '') elif sub_obj.operater_id: duty_dpt = psuser_dept_dict.get(sub_obj.operater_id, '') elif sub_obj.consult_id: duty_dpt = psuser_dept_dict.get(sub_obj.consult_id, '') reimburse_dpt = psuser_dept_dict.get(unsub['psuser_id'], '') if reimburse_dpt=='QC' and sub_obj.category=='rjjh': reimburse_dpt = duty_dpt event_coll.update({'_id':unsub['_id']}, {'$set':{ 'nick':sub_obj.shop.nick, 'category':sub_obj.category, 'saler_id':sub_obj.psuser_id, 'server_id':sub_obj.operater_id or sub_obj.consult_id, 'duty_dpt':duty_dpt, 'reimburse_dpt':reimburse_dpt }}) i += 1 print i, j
def _reintros(cls, psuser, start_date, end_date, result_mapping): start_time = datetime.datetime(start_date.year, start_date.month, start_date.day, 0, 0, 0) end_time = datetime.datetime(end_date.year, end_date.month, end_date.day, 23, 59, 59) for reintro in event_coll.find({'type':'reintro', 'psuser_id':psuser.id, \ 'create_time':{"$gte":start_time, "$lte":end_time}}, {'shop_id':1, 'create_time':1}): reintro = DictWrapper(reintro) result_mapping[reintro.create_time.date()].append(reintro) return result_mapping
def _valid_contacts(cls, psuser, start_date, end_date, result_mapping): start_time = datetime.datetime(start_date.year, start_date.month, start_date.day, 0, 0, 0) end_time = datetime.datetime(end_date.year, end_date.month, end_date.day, 23, 59, 59) for contact in event_coll.find({'type':'contact', 'psuser_id':psuser.id, 'visible':1, \ 'create_time':{"$gte":start_time, "$lte":end_time}}, {'shop_id':1, 'create_time':1}): contact = DictWrapper(contact) result_mapping[contact.create_time.date()].append(contact) return result_mapping
def _change_comments(cls, psuser, start_date, end_date, result_mapping): start_time = datetime.datetime(start_date.year, start_date.month, start_date.day, 0, 0, 0) end_time = datetime.datetime(end_date.year, end_date.month, end_date.day, 23, 59, 59) for comment in event_coll.find({'type':'comment', 'psuser_id':psuser.id, \ "comment_type":{"$in":[301, 302, 303, 304, 305]}, \ 'create_time':{"$gte":start_time, "$lte":end_time}}, \ {'shop_id':1, 'comment_type':1, 'create_time':1}): comment = DictWrapper(comment) result_mapping[comment.create_time.date()].append(comment) return result_mapping
def _good_comments(cls, psuser, start_date, end_date, result_mapping): start_time = datetime.datetime(start_date.year, start_date.month, start_date.day, 0, 0, 0) end_time = datetime.datetime(end_date.year, end_date.month, end_date.day, 23, 59, 59) for comment in event_coll.find({'type':'comment', 'duty_person_id':psuser.id, \ "comment_type":{"$in":[110, 120, 301, 305]}, \ 'create_time':{"$gte":start_time, "$lte":end_time}, 'article_code': 'ts-25811'}, \ {'shop_id':1, 'comment_type':1, 'create_time':1}): comment = DictWrapper(comment) result_mapping[comment.create_time.date()].append(comment) return result_mapping
def main(): sql = 'select shop_id from ncrm_customer order by create_time desc' cus_rows = execute_query_sql_return_tuple(sql) # cus_rows = [(66247950,)] i = 0 for cus in cus_rows: shop_id = cus[0] i += 1 print i, shop_id update_dict = {} # 订单来源未知 sql = "select id from ncrm_subscribe where shop_id=%s and biz_type=6" % shop_id rows = execute_query_sql_return_tuple(sql) if rows: update_dict['unknown_subscribe'] = True # 最新业务类型、到期日期 sql = "select category, create_time, end_date from ncrm_subscribe where shop_id=%s and category not in ('zz', 'zx', 'other', 'seo', 'kfwb') order by create_time desc" % shop_id rows = execute_query_sql_return_tuple(sql) if rows: update_dict['latest_category'] = rows[0][0] update_dict['latest_end'] = rows[0][2] # 当前订单联系次数 update_dict['contact_count'] = event_coll.find({'shop_id':shop_id, 'type':'contact', 'create_time':{'$gte':rows[0][1]}}).count() # # 打过差评 # bad_comment_count = event_coll.find({'shop_id':shop_id, 'type':'comment', 'comment_type':{'$gte':200}}).count() # if bad_comment_count > 0: # update_dict['bad_comment'] = True # 最近联系日期 cur = list(event_coll.find({'shop_id':shop_id, 'type':'contact'}, {'create_time':True}, sort = [('create_time', -1)])) if cur: update_dict['latest_contact'] = cur[0]['create_time'].date() # 最近操作日期 cur = list(event_coll.find({'shop_id':shop_id, 'type':'operate'}, {'create_time':True}, sort = [('create_time', -1)])) if cur: update_dict['latest_operate'] = cur[0]['create_time'].date() # 更新数据库 if update_dict: Customer.objects.filter(shop_id = shop_id).update(**update_dict)
def _unsubscribes(cls, xfgroup, start_date, end_date, result_mapping): start_time = datetime.datetime(start_date.year, start_date.month, start_date.day, 0, 0, 0) end_time = datetime.datetime(end_date.year, end_date.month, end_date.day, 23, 59, 59) unsub_cursor = event_coll.find( { 'type': 'unsubscribe', 'refund_date': { '$gte': start_time, '$lte': end_time }, '$or': [{ 'saler_xfgroup_id': xfgroup.id, 'saler_apportion': { '$gt': 0 } }, { 'server_xfgroup_id': xfgroup.id, 'server_apportion': { '$gt': 0 } }], 'refund_reason': { '$lt': 5 }, # 'refund_type':{'$lt':3} }, { 'shop_id': 1, 'refund_date': 1, 'saler_xfgroup_id': 1, 'server_xfgroup_id': 1, 'saler_apportion': 1, 'server_apportion': 1 }) for unsub in unsub_cursor: apportion = 0 if 'saler_xfgroup_id' in unsub and xfgroup.id == unsub[ 'saler_xfgroup_id']: apportion += unsub.get('saler_apportion', 0) if 'server_xfgroup_id' in unsub and xfgroup.id == unsub[ 'server_xfgroup_id']: apportion += unsub.get('server_apportion', 0) result_mapping[unsub['refund_date'].date()].append( DictWrapper({ '_id': unsub['_id'], 'shop_id': unsub['shop_id'], 'apportion': apportion })) return result_mapping
def _bad_comments(cls, psuser, start_date, end_date, result_mapping): start_time = datetime.datetime(start_date.year, start_date.month, start_date.day, 0, 0, 0) end_time = datetime.datetime(end_date.year, end_date.month, end_date.day, 23, 59, 59) for comment in event_coll.find({'type':'comment', 'duty_person_id':psuser.id, 'comment_type': 200, 'current_version': 'kcjl', 'article_code': 'ts-25811', 'create_time':{'$gte':start_time, '$lte':end_time} }, {'shop_id':1, 'comment_type':1, 'create_time':1} ): comment = DictWrapper(comment) result_mapping[comment.create_time.date()].append(comment) return result_mapping
def load_reintro(cls, customer_mapping, start_time, end_time): shop_id_list = customer_mapping.keys() reintro_mapping = {} # for reintro in ReIntro.objects.only('shop_id', 'create_time').filter(shop_id__in = shop_id_list)\ # .filter(create_time__gte = start_time, create_time__lte = end_time).order_by('create_time'): # reintro_mapping.setdefault(reintro.shop_id, []).append(reintro) for reintro in event_coll.find({'type':'reintro', 'shop_id':{'$in':shop_id_list}, \ 'create_time':{"$gte":start_time, "$lte":end_time}}, {'shop_id':1, 'create_time':1}): reintro = DictWrapper(reintro) reintro_mapping.setdefault(reintro.shop_id, []).append(reintro) for shop_id, cust in customer_mapping.iteritems(): cust._reintro_list = reintro_mapping.get(shop_id, []) return customer_mapping
def _performance_unsubscribes(cls, psuser, start_date, end_date, result_mapping): start_time = datetime.datetime(start_date.year, start_date.month, start_date.day, 0, 0, 0) end_time = datetime.datetime(end_date.year, end_date.month, end_date.day, 23, 59, 59) temp_dict = {} for unsubscribe in event_coll.find({'type':'unsubscribe', 'refund_date':{"$gte":start_time, "$lte":end_time}}, {'shop_id': 1, 'event_id': 1, 'refund_date': 1, 'refund': 1}): temp_dict[unsubscribe['event_id']] = DictWrapper(unsubscribe) query_dict = {'create_time__lt':end_time, 'end_date__gt':start_date, 'psuser':psuser} if psuser.position in ['CONSULT', 'CONSULTLEADER']: query_dict['consult'] = psuser else: query_dict['operater'] = psuser sub_id_list = Subscribe.objects.filter(**query_dict).values_list('id', flat = True) for sub_id, unsubscribe in temp_dict.items(): if sub_id in sub_id_list: result_mapping[unsubscribe.refund_date.date()].append(unsubscribe) return result_mapping
def load_comment(cls, customer_mapping, start_time, end_time): shop_id_list = customer_mapping.keys() comment_mapping = {} # for comment in Comment.objects.only('shop_id', 'comment_type', 'create_time')\ # .filter(create_time__gte = start_time, create_time__lte = end_time)\ # .filter(shop_id__in = shop_id_list)\ # .order_by('create_time'): # comment_mapping.setdefault(comment.shop_id, []).append(comment) for comment in event_coll.find({'type':'comment', 'shop_id':{'$in':shop_id_list}, \ 'create_time':{"$gte":start_time, "$lte":end_time}}, \ {'shop_id':1, 'comment_type':1, 'create_time':1}): comment = DictWrapper(comment) comment_mapping.setdefault(comment.shop_id, []).append(comment) for shop_id, cust in customer_mapping.iteritems(): cust._comment_list = comment_mapping.get(shop_id, []) return customer_mapping
def Load_contact_Event(cls, customer_mapping): shop_id_list = customer_mapping.keys() valid_contact_dict, invalid_contact_dict = {}, {} for contact in event_coll.find( { 'type': 'contact', 'shop_id': { "$in": shop_id_list } }, { 'shop_id': 1, 'create_time': 1, 'visible': 1 }).sort("create_time", pymongo.DESCENDING): contact = DictWrapper(contact) if contact.visible == 1: valid_contact_dict.setdefault(contact.shop_id, []).append(contact) else: invalid_contact_dict.setdefault(contact.shop_id, []).append(contact) today = datetime.datetime.now() for shop_id, customer in customer_mapping.iteritems(): customer.valid_contact_events = valid_contact_dict.get(shop_id, []) customer.invalid_contact_counter = len( invalid_contact_dict.get(shop_id, [])) if customer.valid_contact_events: customer.last_contact = customer.valid_contact_events[0] customer.last_contact_days = ( today - customer.last_contact.create_time).days customer.contact_counter = len( set([ event.create_time.date() for event in customer.valid_contact_events ])) # 每天无论登陆多少次都算作一次 else: customer.last_contact = None customer.last_contact_days = -1 customer.contact_counter = 0 return customer_mapping
def load_unsubscribe(cls, customer_mapping, start_time, end_time): shop_id_list = customer_mapping.keys() unsubscribe_mapping = {} for unsubscribe in event_coll.find({'type':'unsubscribe', 'shop_id':{'$in':shop_id_list}}, \ {'shop_id':1, 'event_id':1, 'refund':1}): unsubscribe = DictWrapper(unsubscribe) unsubscribe_mapping = unsubscribe_mapping.setdefault( unsubscribe.shop_id, {}) unsubscribe_mapping[unsubscribe.event_id] = unsubscribe # for unsubscribe in Unsubscribe.objects.values("shop_id", "event_id", "refund")\ # .filter(shop_id__in = shop_id_list).order_by('create_time'): # unsubscribe = DictWrapper(unsubscribe) # unsubscribe_mapping = unsubscribe_mapping.setdefault(unsubscribe.shop_id, {}) # unsubscribe_mapping[unsubscribe.event_id] = unsubscribe for shop_id, cust in customer_mapping.iteritems(): cust._unorder_mapping = unsubscribe_mapping.get(shop_id, {}) return customer_mapping
def load_contact(cls, customer_mapping, start_time, end_time): shop_id_list = customer_mapping.keys() valid_contact_dict = {} # for event in Contact.objects.only('shop_id', 'create_time').filter(shop_id__in = shop_id_list)\ # .filter(create_time__gte = start_time, create_time__lte = end_time)\ # .filter(visible = 1).order_by('create_time'): # event = DictWrapper(event) # valid_contact_dict.setdefault(event.shop_id, []).append(event) for contact in event_coll.find({'type':'contact', 'shop_id':{'$in':shop_id_list}, 'visible':1, \ 'create_time':{"$gte":start_time, "$lte":end_time}}, {'shop_id':1, 'create_time':1}): contact = DictWrapper(contact) valid_contact_dict.setdefault(contact.shop_id, []).append(contact) for shop_id, cust in customer_mapping.iteritems(): cust._valid_contact_list = valid_contact_dict.get(shop_id, []) cust._valid_contact_count = len( set([ contact.create_time.date() for contact in cust._valid_contact_list ])) return customer_mapping