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
