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 _active_customers(cls, xfgroup, start_date, end_date, result_mapping):
# 托管计划日花费之和
mcs_list = list(
mcs_coll.find({
'rpt_date': {
'$gte': date_2datetime(start_date),
'$lt':
date_2datetime(end_date + datetime.timedelta(days=1))
},
'xfgroup_id': xfgroup.id,
'category': 'kcjl'
}))
shop_cost_dict = {} # {date:{shop_id:cost, ...}, ...}
for mcs in mcs_list:
temp_dict = shop_cost_dict.setdefault(mcs['rpt_date'].date(), {})
temp_dict.setdefault(mcs['shop_id'], 0)
temp_dict[mcs['shop_id']] += mcs['cost']
for dt, temp_dict in shop_cost_dict.items():
for shop_id, cost in temp_dict.items():
if cost >= 5000: # 托管计划日花费之和大于等于50元
result_mapping[dt].append(
DictWrapper({
'shop_id': shop_id,
'cost': cost
}))
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 report_download(cls, shop_id, tapi, token, time_scope):
"""下载店铺的报表"""
result_flag = 'FAILED'
try:
rpt_list = []
for _, source in cls.Report.REPORT_CFG:
top_base_objs = tapi.simba_rpt_custbase_get(
start_time=time_scope[0],
end_time=time_scope[1],
source=source,
subway_token=token,
retry_count=settings.TAPI_RETRY_COUNT * 4,
retry_delay=1)
base_dict, effect_dict = {}, {}
if top_base_objs and hasattr(
top_base_objs, 'rpt_cust_base_list'
) and top_base_objs.rpt_cust_base_list:
for base in top_base_objs.rpt_cust_base_list:
base_dict.update(cls.Report.parse_rpt(base, 'base'))
top_effect_objs = tapi.simba_rpt_custeffect_get(
start_time=time_scope[0],
end_time=time_scope[1],
source=source,
subway_token=token)
if top_effect_objs and hasattr(
top_effect_objs, 'rpt_cust_effect_list'
) and top_effect_objs.rpt_cust_effect_list:
for effect in top_effect_objs.rpt_cust_effect_list:
effect_dict.update(
cls.Report.parse_rpt(effect, 'effect'))
if base_dict:
rpt_list.extend(
cls.Report.merge_rpt_dict(base_dict, effect_dict,
{'shop_id': shop_id}))
if rpt_list:
remove_query = {
'shop_id': shop_id,
'date': {
'$lte': date_2datetime(time_scope[1]),
'$gte': date_2datetime(time_scope[0])
}
}
cls.Report.update_rpt_list(remove_query, rpt_list)
result_flag = 'OK'
else:
result_flag = 'EMPTY'
log.info('download shop rpt OK, shop_id = %s' % (shop_id))
except Exception, e:
log.error('download shop rpt FAILED, shop_id = %s, e = %s' %
(shop_id, e))
def bulk_del_blackword(shop_id, word_list, camp_id = None, adg_id_list = None, opter = 1, opter_name = ''):
del_id_list = []
if (not camp_id) and (not adg_id_list):
return del_id_list
word_list_escaped = []
for word in word_list:
for sign in Const.COMMON_RE_SIGN:
word = word.replace(sign, '\%s' % sign)
word_list_escaped.append(word)
start_time = date_2datetime(datetime.date.today() - datetime.timedelta(15))
filter_dict = {'shop_id':shop_id,
'$or':[{'word':re.compile(word)} for word in word_list_escaped],
'rpt_list':{'$not':{'$elemMatch':{'date':{'$gte':start_time},
'$or':[{'directpaycount':{'$gt':0}}, {'indirectpaycount':{'$gt':0}}]
}}}
}
if adg_id_list:
filter_dict.update({'adgroup_id':{'$in':adg_id_list}})
elif camp_id:
filter_dict.update({'campaign_id':camp_id})
else:
return del_id_list
kw_cursor = kw_coll.find(filter_dict, {'campaign_id':1, 'adgroup_id':1, '_id':1, 'word':1})
kw_arg_dict = {}
for keyword in kw_cursor:
campaign_id, adgroup_id, keyword_id, word = keyword['campaign_id'], keyword['adgroup_id'], keyword['_id'], keyword['word']
if not kw_arg_dict.has_key(campaign_id):
kw_arg_dict[campaign_id] = []
kw_arg_list = kw_arg_dict[campaign_id]
kw_arg_list.append([adgroup_id, keyword_id, word, 0, 0, '包含屏蔽词'])
for campaign_id, kw_arg_list in kw_arg_dict.items():
sub_del_id_list = delete_keywords(shop_id = shop_id, campaign_id = campaign_id, kw_arg_list = kw_arg_list, data_type = 404, opter = opter, opter_name = opter_name)
del_id_list.extend(sub_del_id_list)
return del_id_list
def __load_psuser_subscribe(cls, manager):
'''加载进账订单'''
if not hasattr(manager, '_raw_psuser_subscribe'):
manager._raw_psuser_subscribe = list(
Subscribe.objects.select_related('shop').filter(
**{
'psuser_id__in':
manager.psuser_id_list,
'create_time__gte':
date_2datetime(manager.start_date),
'create_time__lt':
date_2datetime(manager.end_date +
datetime.timedelta(days=1)),
}).exclude(biz_type=6).only('id', 'shop', 'psuser',
'category', 'pay',
'create_time', 'start_date',
'end_date', 'approval_status'))
return manager._raw_psuser_subscribe
def get_valid_task(need_count):
"""获取符合条件的任务队列"""
valid_id_list = []
is_mutual = CacheAdpter.get(CacheKey.ENGINE_SHOPMNG_TASK_MUTUAL_LOCK,
'web')
if is_mutual: # 如果能够取到缓存，说明门关了
return valid_id_list
else: # 否则，马上设置缓存，将门关了
try:
CacheAdpter.set(CacheKey.ENGINE_SHOPMNG_TASK_MUTUAL_LOCK, True,
'web', 60 * 2)
test_shop_id_list = Config.get_value('mnt.TEST_SHOP_IDLIST',
default=[])
today_0time = date_2datetime(datetime.date.today())
recyle_time = datetime.datetime.today() - datetime.timedelta(
hours=6) # 回收时间
cursor = shopmng_task_coll.find(
{
'_id': {
'$nin': test_shop_id_list
},
'$or': [{
'status': 1
}, {
'status': {
'$in': [-1, 2, 4]
},
'last_start_time': {
'$lte': recyle_time,
'$gte': today_0time
}
}]
}, {
'_id': 1
}).sort([('manual_priority', -1), ('priority', -1)])
cursor = cursor.limit(need_count)
valid_id_list = [i['_id'] for i in cursor]
if valid_id_list:
shopmng_task_coll.update({'_id': {
'$in': valid_id_list
}}, {
'$set': {
'status': -1,
'last_start_time': datetime.datetime.now()
}
},
multi=True)
return valid_id_list
except Exception, e:
log.error("shopmng_task get_valid_task error, e=%s" % e)
return valid_id_list
finally:
def insert_rpt_list(cls, shop_id, end_date, adg_date_dict, rpt_list):
coll = cls._get_collection()
bulk = coll.initialize_unordered_bulk_op()
for adgroup_id, last_sync_time in adg_date_dict.items():
bulk.find({
'shop_id': shop_id,
'adgroup_id': adgroup_id,
'date': {
'$lte': date_2datetime(end_date),
'$gte': date_2datetime(last_sync_time)
}
}).remove()
try:
bulk.execute()
except BulkWriteError as e:
log.error("bulk remove keyword rpt error, e=%s" % e)
# 这里可能还需要再作转义？将每个keyword的数据拆分成三份？
coll.insert(rpt_list)
def __get_rpt_list_8id(cls, cat_id):
cachekey = CacheKey.KWLIB_CAT_STATIC_RPT % cat_id
cat_data_list = CacheAdpter.get(cachekey, 'web', None)
if cat_data_list is None: # 不能简写为 if not cat_data_list: 因为有可能 缓存中有值，但为 []
rpt_day = 15
now = datetime.datetime.now()
# 淘宝接口下线维护，这里临时使用旧数据
rpt_day = 7
now = datetime.datetime(2017, 3, 12)
start_date = date_2datetime(now - datetime.timedelta(days=rpt_day))
objs = cls.objects.filter(
cat_id=cat_id, rpt_date__gte=start_date).order_by('rpt_date')
cat_data_list = [obj for obj in objs]
# 缓存在第二天7点过期，因为此时数据库中已刷新昨日数据
timeout = (date_2datetime(now) +
datetime.timedelta(days=1, hours=7) - now).seconds
CacheAdpter.set(cachekey, cat_data_list, 'web', timeout)
return cat_data_list
def __main__():
now = datetime.datetime.now()
days_90ago = now.date() - datetime.timedelta(days=89)
start_time = date_2datetime(days_90ago)
while start_time < now:
end_time = start_time + datetime.timedelta(days=1)
sync_bizorder(kcjl_code, start_time + datetime.timedelta(hours=12),
end_time)
time.sleep(60 * 1)
sync_bizorder(qn_code, start_time + datetime.timedelta(hours=12),
end_time)
time.sleep(60 * 1)
start_time = end_time
def get_garbage_words(shop_id):
'''获取整店所有无展现词 (无展现词定义：连续15天展现为0且未激活）'''
start_time = date_2datetime(datetime.datetime.now() - datetime.timedelta(days = 15))
result = []
mnt_campid_list = MntMnger.get_longtail_camp_ids(shop_id = shop_id)
kw_cur = kw_coll.find({'shop_id': shop_id, 'is_garbage':True, 'campaign_id':{'$ne':mnt_campid_list}}, {'campaign_id':1, 'adgroup_id':1, 'word':1, 'rpt_list': {'$slice':-15}})
for kw in kw_cur:
impr = 0
if kw.has_key('rpt_list'):
for rpt in kw['rpt_list']:
if rpt['date'] >= start_time:
impr += rpt['impressions']
if impr == 0:
result.append({'kw_id':kw['_id'], 'word':kw['word'], 'camp_id':kw['campaign_id'], 'adg_id':kw['adgroup_id']})
return result
def export_oldxfg_2new():
print 'start export_oldxfg_2new'
if XiaoFuGroup.objects.count() > 0:
print 'XiaoFuGroup has data, exit'
return
xfs = XFGroup.objects.all()
for xf in xfs:
XiaoFuGroup.objects.create(
consult=xf.consult,
seller=xf.seller,
is_active=True,
name='%s+%s' % (xf.consult.name_cn, xf.seller.name_cn),
department=xf.consult.department,
create_time=date_2datetime(xf.consult.entry_date))
print 'finish export_oldxfg_2new'
def __load_allocation_record(cls, manager, psuser_id):
'''加载订单分配记录'''
_raw_allocation_record = getattr(manager, '_raw_allocation_record', {})
if psuser_id not in _raw_allocation_record:
_raw_allocation_record[psuser_id] = list(
ar_coll.find(
{
'create_time': {
'$gte':
date_2datetime(manager.start_date),
'$lt':
date_2datetime(manager.end_date +
datetime.timedelta(days=1))
},
'org_id_list': re.compile('.*, %s, .*' % psuser_id),
'new_id_list': {
'$not': re.compile('.*, %s, .*' % psuser_id)
}
}, {
'subscribe_id': True,
'create_time': True
}))
manager._raw_allocation_record = _raw_allocation_record
return _raw_allocation_record[psuser_id]
def filter_dupl_words(shop_id, condition, word_list):
'''获取一批重复词按条件过滤后的所有关键词信息'''
del_day, del_level, statistics_type, del_offline = condition['del_day'], condition['del_level'], condition['del_statistics_type'], condition['del_offline']
mnt_campid_list = MntMnger.get_longtail_camp_ids(shop_id = shop_id)
if not word_list:
return []
kw_list = list(kw_coll.find({'shop_id': shop_id, 'campaign_id':{'$ne':mnt_campid_list}, 'word': {'$in':word_list}}, {'word':1, 'qscore':1, 'campaign_id':1, 'adgroup_id':1, 'rpt_list': {'$slice':-del_day}}).sort('word'))
if del_offline:
camp_id_list, adg_id_list = [], []
for kw in kw_list:
camp_id_list.append(kw['campaign_id'])
adg_id_list.append(kw['adgroup_id'])
camp_cur = camp_coll.find({'_id':{'$in':camp_id_list}}, {'online_status':1})
camp_status_dict = {camp['_id']:camp['online_status'] for camp in camp_cur}
adg_cur = adg_coll.find({'shop_id':shop_id, '_id':{'$in':adg_id_list}}, {'online_status':1})
adg_status_dict = {adg['_id']:adg['online_status'] for adg in adg_cur}
kw_list.append({'word':None})
temp_word, temp_list, result, count = '', [], [], 0
start_time = date_2datetime(datetime.datetime.now() - datetime.timedelta(days = del_day))
for kw in kw_list:
# 当一个重复词遍历完后，排序，过滤后加入到result, 并初始化temp_word, temp_list
if temp_word != kw['word']:
temp_list = sorted(temp_list, key = itemgetter('ctr', 'impr', 'qscore'), reverse = True) # 按点击率、展现量、质量得分降序排序
count = 0
for obj in temp_list:
count += 1
if count > del_level and (statistics_type == '0' or obj[statistics_type] == 0) and (del_offline == 0 or obj['camp_status'] == obj['adg_status'] == 'online'):
result.append({'kw_id':obj['kw_id'], 'word':temp_word, 'camp_id':obj['camp_id'], 'adg_id':obj['adg_id']})
temp_word, temp_list = kw['word'], []
# 将当前词的相关信息存入 temp_list
if kw['word']:
impr, click = 0, 0
if del_offline and kw.has_key('rpt_list'):
for rpt in kw['rpt_list']:
if rpt['date'] >= start_time:
impr += rpt['impressions']
click += rpt['click']
temp_list.append({'kw_id': kw['_id'], 'impr': impr, 'click': click, 'camp_id': kw['campaign_id'], 'adg_id': kw['adgroup_id'], 'qscore':kw['qscore'],
'ctr': impr and click / impr,
'adg_status':del_offline and adg_status_dict[kw['adgroup_id']] or '',
'camp_status': del_offline and camp_status_dict[kw['campaign_id']] or '',
})
return result
def get_snap_list(cls,
query_dict,
rpt_days=None,
start_date=None,
end_date=None,
source=-1,
search_type=-1):
"""
:argument
query_dict 查询条件
rpt_days 报表天数 与下面的只用选择其一即可
start_date 起始日期 字符串，形如'2015-10-12'
end_date 结束日期 字符串，形如'2015-10-12'
search_type 报表分类，目前可选(-1, 0, 2) 分别代表(汇总、搜索、定向)，这里也可以写query格式，如 {'$in': [0,2]}
source 报表来源，可选(-1,1,2,4,5) 分别代表(汇总、PC站内、PC站外、移动站内、移动站外)，格式也同上
:return
{4258150:{'date':'2015-09-30', 'impressions': 300}}
"""
result_dict = collections.defaultdict(list)
base_query = {'source': source, 'search_type': search_type}
if rpt_days:
date_query = {
'date': {
'$gte':
date_2datetime(datetime.date.today() -
datetime.timedelta(days=rpt_days))
}
}
elif start_date:
date_query = {
'date': {
'$gte': string_2datetime(start_date, fmt='%Y-%m-%d')
}
}
if end_date:
date_query['date'].update(
{'$lte': string_2datetime(end_date, fmt='%Y-%m-%d')})
else:
date_query = {}
base_query.update(date_query)
base_query.update(query_dict)
cursor = cls._get_collection().find(base_query).sort([('date', 1)])
for i in cursor:
result_dict[i[cls.IDENTITY]].append(ReportDictWrapper(i))
return result_dict
def get_dupl_word_info(shop_id, last_day, word):
'''获取一个重复词的所有关键词信息'''
mnt_campid_list = MntMnger.get_longtail_camp_ids(shop_id = shop_id)
kw_list = list(kw_coll.find({'shop_id': shop_id, 'word': word, 'campaign_id':{'$nin':mnt_campid_list}}, {'rpt_list':{'$slice':-last_day}}))
camp_id_list, adg_id_list = [], []
for kw in kw_list:
camp_id_list.append(kw['campaign_id'])
adg_id_list.append(kw['adgroup_id'])
camp_cur = camp_coll.find({'_id':{'$in':camp_id_list}}, {'title':1})
camp_dict = {camp['_id']:camp['title'] for camp in camp_cur}
adg_cur = adg_coll.find({'shop_id':shop_id, '_id':{'$in':adg_id_list}}, {'item_id':1})
adg_dict = {adg['_id']:adg['item_id'] for adg in adg_cur}
item_cur = item_coll.find({'shop_id':shop_id, '_id':{'$in':adg_dict.values()}}, {'title':1, 'pic_url':1, 'price':1})
item_dict = {item['_id']:(item['title'], item['price'], item['pic_url']) for item in item_cur }
result = []
start_time = date_2datetime(datetime.datetime.now() - datetime.timedelta(days = last_day))
no_item_tuple = ('该宝贝可能不存在或者下架，请尝试同步数据', 0, '/site_media/jl/img/no_photo') # 当item不存在时，使用默认
for kw in kw_list:
cost, impr, click, pay = 0, 0, 0, 0
if kw.has_key('rpt_list'):
for rpt in kw['rpt_list']:
if rpt['date'] >= start_time:
cost += rpt['cost']
impr += rpt['impressions']
click += rpt['click']
pay += rpt['directpay'] + rpt['indirectpay']
temp_key = kw['adgroup_id']
if temp_key in adg_dict:
item_id = adg_dict[kw['adgroup_id']]
temp_item_info = item_dict.get(item_id, no_item_tuple)
result.append({'kw_id':kw['_id'], 'max_price': kw['max_price'], 'qscore': kw['qscore'], 'impr': impr, 'click': click, 'pay': pay,
'cpc': click and cost / click, 'ctr': impr and click / impr, 'camp_title': camp_dict[kw['campaign_id']],
'item_title': temp_item_info[0], 'item_price':temp_item_info[1], 'pic_url': temp_item_info[2], 'item_id':item_id
})
result = sorted(result, key = itemgetter('ctr', 'impr', 'qscore', 'max_price'), reverse = True) # 按点击率、展现量、质量得分、出价降序排序
return result
def get_valid_account_list():
"""获取有效的账户列表"""
valid_list = crm_cache.get(CacheKey.CRM_VALID_ACCOUNT_FLAG)
if not valid_list:
try:
valid_list = []
yes_time = date_2datetime(datetime.date.today() -
datetime.timedelta(days=1))
for dl in dler_coll.find({'api_valid_time': {
"$gte": yes_time
}}, {'_id': 1}):
try:
valid_list.append(int(dl['_id']))
except Exception, e:
log.error("get shop_id error, e=%s" % (e))
# 每 20 分钟刷新一次
crm_cache.set(CacheKey.CRM_VALID_ACCOUNT_FLAG,
valid_list,
time_out=20 * 60)
except Exception, e:
log.error("get valid accounts error, e=%s" % e)
return []
def __load_prev_subscribe(cls, manager):
'''加载客户流失保护期内的订单'''
if not hasattr(manager, '_raw_prev_subscribe'):
raw_psuser_subscribe = cls.__load_psuser_subscribe(manager)
shop_id_list = list(
set([obj.shop_id for obj in raw_psuser_subscribe]))
manager._raw_prev_subscribe = list(
Subscribe.objects.filter(
**{
'shop_id__in':
shop_id_list,
'create_time__lt':
date_2datetime(manager.end_date +
datetime.timedelta(days=1)),
'end_date__gt':
manager.start_date -
datetime.timedelta(days=cls.HOLD_DAYS),
'category__in':
['ztc', 'vip', 'rjjh', 'zz', 'dyy', 'ztbd']
}).exclude(biz_type=6).only(
'id', 'shop', 'category', 'create_time',
'end_date').order_by('end_date'))
return manager._raw_prev_subscribe
def remove_outdated():
last_date = datetime.date.today() - datetime.timedelta(30)
default_deadline = date_2datetime(last_date)
from django.contrib.sessions.models import Session
Session.objects.filter(expire_date__lte=default_deadline).delete()
from apps.subway.models_report import AccountRpt, CampaignRpt, AdgroupRpt, CreativeRpt, KeywordRpt
AccountRpt.clean_outdated()
CampaignRpt.clean_outdated()
AdgroupRpt.clean_outdated()
CreativeRpt.clean_outdated()
KeywordRpt.clean_outdated()
from apps.subway.models_upload import UploadRecord
UploadRecord.clean_outdated()
from apps.alg.models import OptimizeRecord
OptimizeRecord.clean_outdated()
# 删除冻结超过30天的积分数据
from apps.web.models import PointActivity
PointActivity.clean_outdated()
def download_crtrpt_byadg(cls, shop_id, campaign_id, adgroup_id, token,
time_scope, tapi):
"""下载单个广告组下面的创意报表"""
try:
rpt_list = []
for search_type, source in cls.Report.REPORT_CFG:
base_dict, effect_dict = collections.defaultdict(
dict), collections.defaultdict(dict)
top_base_objs = tapi.simba_rpt_adgroupcreativebase_get(
campaign_id=campaign_id,
adgroup_id=adgroup_id,
start_time=time_scope[0],
end_time=time_scope[1],
search_type=search_type,
source=source,
subway_token=token,
retry_count=settings.TAPI_RETRY_COUNT * 4,
retry_delay=1)
if top_base_objs and hasattr(
top_base_objs, 'rpt_adgroupcreative_base_list'
) and top_base_objs.rpt_adgroupcreative_base_list:
for base in top_base_objs.rpt_adgroupcreative_base_list:
base_dict[base.creative_id].update(
cls.Report.parse_rpt(base, 'base'))
top_effect_objs = tapi.simba_rpt_adgroupcreativeeffect_get(
campaign_id=campaign_id,
adgroup_id=adgroup_id,
start_time=time_scope[0],
end_time=time_scope[1],
search_type=search_type,
source=source,
subway_token=token,
retry_count=settings.TAPI_RETRY_COUNT * 4,
retry_delay=1)
if top_effect_objs and hasattr(
top_effect_objs, 'rpt_adgroupcreative_effect_list'
) and top_effect_objs.rpt_adgroupcreative_effect_list:
for effect in top_effect_objs.rpt_adgroupcreative_effect_list:
effect_dict[effect.creative_id].update(
cls.Report.parse_rpt(effect, 'effect'))
if base_dict:
for crt_id, base_rpt_dict in base_dict.items():
rpt_list.extend(
cls.Report.merge_rpt_dict(
base_rpt_dict, effect_dict.get(crt_id, {}), {
'shop_id': shop_id,
'campaign_id': campaign_id,
'adgroup_id': adgroup_id,
'creative_id': crt_id
}))
if rpt_list:
cls.Report.update_rpt_list(
{
'shop_id': shop_id,
'adgroup_id': adgroup_id,
'date': {
'$lte': date_2datetime(time_scope[1]),
'$gte': date_2datetime(time_scope[0])
}
}, rpt_list)
sync_time = datetime.datetime.now()
if sync_time.hour < 6:
sync_time = sync_time - datetime.timedelta(days=1)
adg_coll.update({
'shop_id': shop_id,
'_id': adgroup_id
}, {'$set': {
'crtrpt_sync_time': sync_time
}})
return True
except TopError, e:
log.error(
'download creative rpt FAILED, shop_id = %s, adgroup_id=%s, e = %s'
% (shop_id, adgroup_id, e))
return False
def main():
# 找出在服务有效期内指定天数refresh_min_days未登录的用户信息
refresh_min_days = 7
today_datetime = date_2datetime(date.today())
refresh_max_date = today_datetime - timedelta(days=refresh_min_days)
sql1 = "SELECT nick FROM router_articleusersubscribe WHERE article_code='%%s' AND deadline>'%s'" % today_datetime
sql2 = "SELECT nick, shop_id, session FROM auth_user WHERE nick IN (%s) AND last_login<'%s'" % (
sql1, refresh_max_date)
# sql2 = "SELECT username, shop_id, email FROM auth_user WHERE username IN (%s) AND last_login>'2014-08-11'" % sql1
refresh_failed_nick = []
#===========================================================================
# TOP授权协议 web平台
#===========================================================================
platform = 'web'
appkey = settings.APP_KEY
appsecret = settings.APP_SECRET
sql = sql2 % settings.APP_ARTICLE_CODE
# sql = "SELECT username, shop_id, email FROM auth_user WHERE shop_id IN (%s)" % "'60605073'" # 修复指定用户用
user_list = execute_query_sql_return_tuple(sql)
# 从目标用户中过滤掉指定天数refresh_min_days内已经刷新过session的用户
shop_id_list = [int(user_info[1]) for user_info in user_list]
refresh_token_qs = dler_coll.find(
{
'_id': {
'$in': shop_id_list
},
'$or': [{
'top_refresh_time': {
'$lte': refresh_max_date
}
}, {
'top_refresh_time': None
}]
}, {
'_id': 1,
'top_refresh_token': 1
})
# refresh_token_qs = dler_coll.find({'_id':{'$in':shop_id_list}}, {'_id':1, 'top_refresh_token':1}) # 修复指定用户用
refresh_token_dict = {
qs['_id']: qs.get('top_refresh_token', None)
for qs in refresh_token_qs
}
update_session_list, update_refresh_list = [], []
for nick, shop_id, session in user_list:
shop_id = int(shop_id)
if refresh_token_dict.has_key(shop_id):
refresh_token = refresh_token_dict[shop_id]
if not refresh_token:
refresh_token = session
sign = md5.new('appkey%srefresh_token%ssessionkey%s%s' %
(appkey, refresh_token, session,
appsecret)).hexdigest().upper()
url_params = urllib.urlencode([('appkey', appkey),
('refresh_token', refresh_token),
('sessionkey', session),
('sign', sign)])
resp = AccessToken.sync_from_top(url_params,
host='container.api.taobao.com',
method='GET',
url='/container/refresh',
secure=False)
if resp.has_key('top_session'):
session = resp['top_session']
password = set_password(session)
if resp.has_key('refresh_token'):
refresh_token = resp['refresh_token']
else:
refresh_token = session
update_session_list.append((session, password, shop_id, nick))
update_refresh_list.append(({
'_id': shop_id
}, {
'$set': {
'top_refresh_token': refresh_token,
'top_refresh_time': today_datetime
}
}))
set_session_to_cache(1, shop_id, platform, session)
set_session_to_cache(2, nick, platform, session)
log.info('刷新session成功：%s' % nick)
else:
send_short_message(nick)
refresh_failed_nick.append(nick)
log.info('刷新session失败：%s' % nick)
session_count = refresh_count = 0
if update_session_list:
sql = "UPDATE auth_user SET session=%s, password=%s WHERE shop_id=%s AND nick=%s" # 作为executemany参数时，内部的字符串参数会自动加上引号，所以不用再sql语句里加引号！
session_count = bulk_update_for_sql(sql, update_session_list)
log.info('自动刷新session脚本：成功批量刷新%s个web平台账户的session' % session_count)
del update_session_list
if update_refresh_list:
refresh_count = bulk_update_mongodb(dler_coll, update_refresh_list)
log.info('自动刷新session脚本：成功批量刷新%s个web平台账户的refresh_token' % refresh_count)
del update_refresh_list
#===========================================================================
# oAuth2.0授权协议 qn平台
#===========================================================================
platform = 'qn'
appkey = settings.APP_KEY
appsecret = settings.APP_SECRET
sql = sql2 % settings.APP_ARTICLE_CODE
# sql = "SELECT username, shop_id, email FROM auth_user WHERE shop_id IN (%s)" % "'60605073'" # 修复指定用户用
user_list = execute_query_sql_return_tuple(sql)
# 从目标用户中过滤掉指定天数refresh_min_days内已经刷新过session的用户
nick_list = [user_info[0] for user_info in user_list]
nick_list = "('%s')" % ("','".join(nick_list))
sql = "SELECT nick, access_token, refresh_token FROM router_accesstoken WHERE nick in %s AND platform='qn' AND (create_time<='%s' OR create_time is NULL)" % (
nick_list, refresh_max_date)
# sql = "SELECT nick, access_token, refresh_token FROM router_accesstoken WHERE nick in %s AND platform='qn'" % nick_list # 修复指定用户用
user_list2 = execute_query_sql_return_tuple(sql)
update_token_list = []
for nick, access_token, refresh_token in user_list2:
if not refresh_token:
refresh_token = access_token
url_params = urllib.urlencode([('client_id', appkey),
('client_secret', appsecret),
('grant_type', 'refresh_token'),
('refresh_token', refresh_token)])
resp = AccessToken.sync_from_top(url_params,
host='oauth.taobao.com',
method='POST',
url='/token',
secure=True)
if resp.has_key('access_token'):
access_token = resp['access_token']
if resp.has_key('refresh_token'):
refresh_token = resp['refresh_token']
else:
refresh_token = access_token
update_token_list.append(
(access_token, refresh_token, today_datetime, nick))
set_session_to_cache(1, shop_id, platform, access_token)
set_session_to_cache(2, nick, platform, access_token)
log.info('刷新access_token成功：%s' % nick)
else:
if nick not in refresh_failed_nick:
send_short_message(nick)
refresh_failed_nick.append(nick)
log.info('刷新access_token失败：%s' % nick)
token_count = 0
if update_token_list:
sql = "UPDATE router_accesstoken SET access_token=%s, refresh_token=%s, create_time=%s WHERE nick=%s AND platform='qn'"
token_count = bulk_update_for_sql(sql, update_token_list)
log.info('自动刷新access_token脚本：成功批量刷新%s个qn平台账户的access_token' % token_count)
if refresh_failed_nick:
log.info('自动刷新session&access_token脚本：共%s个用户刷新失败，分别是：%s' %
(len(refresh_failed_nick), '，'.join(refresh_failed_nick)))
def __call__(self):
"""
1，客户群过滤
2，具体查询函数
3，添加过滤器
3，分页
4，绑定特殊数据
"""
self.analysis_server_info()
client_id_list, all_client_group = self.group_filter()
self.do_query()
# 分页选择 50 100 150
# page_info, shop_id_list = pagination_tool(page = self.page_no, record = self.shop_ids)
page_count = self.request.GET.get('page_count', '50').strip()
page_info, shop_id_list = pagination_tool(page=self.page_no,
record=self.shop_ids,
page_count=int(page_count))
customer_list = self.get_customers(shop_id_list)
self.do_bind(customer_list)
self.bind_client_group_ids(customer_list, all_client_group)
extra_data = self.bind_extra_data()
common_groups = CacheAdpter.get(CacheKey.NCRM_COMMON_GROUP_STATISTIC %
self.myself.id, 'crm', {}) # 通用客户群统计数
self.shop_balance_dict = self.request.session.get(
CacheKey.NCRM_SHOP_BALANCE % self.myself.id, {}) # 人机账户绑定余额
if not self.shop_balance_dict and self.myself.position in [
'RJJH', 'RJJHLEADER'
]:
self.shop_balance_dict = self.myself.get_mycustomers_balance()
self.request.session[CacheKey.NCRM_SHOP_BALANCE %
self.myself.id] = self.shop_balance_dict
common_groups['no_balance'] = [
shop_id for shop_id, balance in self.shop_balance_dict.items()
if balance <= 0
]
# common_groups['no_balance'] = [63518068, 123123]
self.bind_common_groups(customer_list)
self.handle_customer_info(customer_list)
data_dict = {
'common_groups':
common_groups,
'client_id_list':
client_id_list,
'all_client_group':
all_client_group,
'page_info':
page_info,
'customer_list':
customer_list,
'work_type':
self.work_type,
'extra':
extra_data,
'myself':
self.myself,
'can_operate':
self.myself.position in [
'COMMANDER', 'RJJH', 'RJJHLEADER', 'TPAE', 'TPLEADER', 'DEV',
'DESIGNER', 'OPTAGENT', 'SHOPMANAGER', 'SHOPASSISTANT'
],
"psuser_id":
int(self.psuser_id),
'is_show_my_customers':
self.is_show_my_customers,
'nick_list':
self.request.GET.getlist('nick', [])
}
# 退款原因
data_dict['refund_reason_checkbox'] = Unsubscribe.REFUND_REASON_CHOICES
# 业务类型
# data_dict['latest_category_choice'] = CATEGORY_CHOICES
import settings
redirect_url = "{}/{}/".format(settings.LOGIN_URL, settings.ADMIN_URL)
data_dict['redirect_url'] = redirect_url
# 绑定CRM公告、意见反馈、提醒、积分兑换及检查好评
now = datetime.datetime.now()
crm_ad = list(
main_ad_coll.find(
{
'ad_position': 'rightnotice',
'ad_display': 1,
'ad_status': 2,
'ad_user_type': re.compile(r'.*2.*'),
'ad_start_time': {
'$lte': now
},
'ad_end_time': {
'$gt': now
}
}, {
'ad_title': True
}).sort('ad_put_time', -1))
for ad in crm_ad:
ad['id'] = ad['_id']
data_dict['crm_ad'] = crm_ad
crm_feedback = Feedback.objects.select_related('shop').filter(
consult=self.myself, handle_status=-1).order_by('-create_time')
data_dict['crm_feedback'] = crm_feedback
crm_reminder = list(
reminder_coll.find(
{
'receiver_id': self.myself.id,
'handle_status': -1
}, {
'content': True
}).sort('create_time', -1))
for reminder in crm_reminder:
reminder['id'] = reminder['_id']
data_dict['crm_reminder'] = crm_reminder
crm_pa = list(
pa_coll.find(
{
'consult_id':
self.myself.id,
'consult_flag':
0,
'$or': [{
'type': 'appraise',
'is_freeze': 1,
'create_time': {
'$gte': now - datetime.timedelta(days=7),
'$lt': date_2datetime(datetime.date.today())
}
}, {
'type': 'virtual',
'exchange_status': 0
}, {
'type': 'gift',
'logistics_state': 0
}]
}, {
'nick': True,
'type': True
}).sort('create_time', -1))
for pa in crm_pa:
pa['id'] = pa['_id']
data_dict['crm_pa'] = crm_pa
return render_to_response(self.template,
data_dict,
context_instance=RequestContext(
self.request))
def get_top_keywords(self, limit_count=50, rpt_days=7):
"""取店铺TOP词，使用报表聚集取出来"""
# TODO: wangqi 20151216 这里可以使用K均值聚类算法根据多个维度综合找出
pipeline = [
{
'$match': {
'shop_id': self.shop_id,
'date': {
'$gte':
date_2datetime(datetime.date.today() -
datetime.timedelta(days=rpt_days))
}
}
},
{
'$group': {
'_id': '$keyword_id',
'campaign_id': {
'$first': '$campaign_id'
},
'adgroup_id': {
'$first': '$adgroup_id'
},
# 'impressions': {'$sum': '$impressions'},
# 'click':{'$sum':'$click'},
'cost': {
'$sum': '$cost'
},
# 'aclick':{'$sum':'$aclick'},
# 'avgpos':{'$last':'$avgpos'},
'directpay': {
'$sum': '$directpay'
},
'indirectpay': {
'$sum': '$indirectpay'
},
# 'directpaycount':{'$sum':'$directpaycount'},
# 'indirectpaycount':{'$sum':'$indirectpaycount'},
# 'favitemcount':{'$sum':'$favitemcount'},
# 'favshopcount':{'$sum':'$favshopcount'},
# 'carttotal': {'$sum': '$carttotal'},
# 'rpt_days': {'$sum': 1}
}
},
{
'$project': {
'_id': '$_id',
'campaign_id': '$campaign_id',
'adgroup_id': '$adgroup_id',
'cost': '$cost',
'pay': {
'$add': ['$directpay', '$indirectpay']
},
}
},
{
'$sort': SON([('cost', -1), ('pay', -1)])
},
{
'$project': {
'_id': '$_id',
'adgroup_id': '$adgroup_id',
'campaign_id': '$campaign_id'
}
},
{
'$limit': limit_count
}
]
aggr_result = Keyword.Report._get_collection().aggregate(pipeline)
kw_dict_list = []
if aggr_result['ok']:
for temp_result in aggr_result['result']:
kw_dict_list.append({
'keyword_id': temp_result['_id'],
'adgroup_id': temp_result['adgroup_id'],
'campaign_id': temp_result['campaign_id']
})
self.update_core_kw(kw_dict_list)
return kw_dict_list
def aggregate_rpt(cls,
query_dict,
group_keys,
rpt_days=None,
start_date=None,
end_date=None,
source=-1,
search_type=-1):
"""
:argument
query_dict 查询条件
group_keys 类似mysql的group by字段，即分组的键，形如'adgroup_id,source,search_type'
rpt_days 报表天数 与下面的只用选择其一即可
start_date 起始日期 字符串，形如'2015-10-12'
end_date 结束日期 字符串，形如'2015-10-12'
search_type 报表分类，目前可选(-1, 0, 2) 分别代表(汇总、搜索、定向)，这里也可以写query格式，如 {'$in': [0,2]}
source 报表来源，可选(-1,1,2,4,5) 分别代表(汇总、PC站内、PC站外、移动站内、移动站外)，格式也同上
:return
[{'_id':{group_key1: <value>, group_key2: <value>}, 'click': 200}]
"""
base_query = {'source': source, 'search_type': search_type}
if rpt_days:
date_query = {
'date': {
'$gte':
date_2datetime(datetime.date.today() -
datetime.timedelta(days=rpt_days))
}
}
elif start_date:
date_query = {
'date': {
'$gte': string_2datetime(start_date, fmt='%Y-%m-%d')
}
}
if end_date:
date_query['date'].update(
{'$lte': string_2datetime(end_date, fmt='%Y-%m-%d')})
else:
date_query = {}
base_query.update(date_query)
base_query.update(query_dict)
group_key_dict = {
key.strip(): '$%s' % key.strip()
for key in group_keys.split(",")
}
pipeline = [
{
'$match': base_query
},
{
'$group': {
'_id': group_key_dict,
'impressions': {
'$sum': '$impressions'
},
'click': {
'$sum': '$click'
},
'cost': {
'$sum': '$cost'
},
'aclick': {
'$sum': '$aclick'
},
'avgpos': {
'$last': '$avgpos'
},
'directpay': {
'$sum': '$directpay'
},
'indirectpay': {
'$sum': '$indirectpay'
},
'directpaycount': {
'$sum': '$directpaycount'
},
'indirectpaycount': {
'$sum': '$indirectpaycount'
},
'favitemcount': {
'$sum': '$favitemcount'
},
'favshopcount': {
'$sum': '$favshopcount'
},
'carttotal': {
'$sum': '$carttotal'
},
'rpt_days': {
'$sum': 1
}
}
},
]
result = cls._get_collection().aggregate(pipeline)
if result['ok']:
return result['result']
else:
log.error("get_summed_detail_rpt error, result=%s" % result)
return []
def download_camp_bycampids(cls, shop_id, camp_id_list, tapi, token,
time_scope):
result_flag = 'FAILED'
try:
rpt_list = []
for search_type, source in cls.Report.REPORT_CFG:
for camp_id in camp_id_list:
top_base_objs = tapi.simba_rpt_campaignbase_get(
campaign_id=camp_id,
start_time=time_scope[0],
end_time=time_scope[1],
search_type=search_type,
source=source,
subway_token=token,
retry_count=settings.TAPI_RETRY_COUNT * 4,
retry_delay=1)
if top_base_objs and hasattr(
top_base_objs, 'rpt_campaign_base_list'
) and top_base_objs.rpt_campaign_base_list:
base_dict, effect_dict = {}, {}
for base in top_base_objs.rpt_campaign_base_list:
base_dict.update(cls.Report.parse_rpt(
base, 'base'))
top_effect_objs = tapi.simba_rpt_campaigneffect_get(
campaign_id=camp_id,
start_time=time_scope[0],
end_time=time_scope[1],
search_type=search_type,
source=source,
subway_token=token,
retry_count=settings.TAPI_RETRY_COUNT * 4,
retry_delay=1)
if top_effect_objs and hasattr(
top_effect_objs, 'rpt_campaign_effect_list'
) and top_effect_objs.rpt_campaign_effect_list:
for effect in top_effect_objs.rpt_campaign_effect_list:
effect_dict.update(
cls.Report.parse_rpt(effect, 'effect'))
if base_dict:
rpt_list.extend(
cls.Report.merge_rpt_dict(
base_dict, effect_dict, {
'shop_id': shop_id,
'campaign_id': camp_id
}))
if rpt_list:
remove_query = {
'shop_id': shop_id,
'campaign_id': {
'$in': camp_id_list
},
'date': {
'$lte': date_2datetime(time_scope[1]),
'$gte': date_2datetime(time_scope[0])
}
}
cls.Report.update_rpt_list(remove_query, rpt_list)
result_flag = 'OK'
else:
result_flag = 'EMPTY'
log.info('download campaign rpt OK, shop_id = %s, camp_list = %s' %
(shop_id, camp_id_list))
except Exception, e:
log.error('download campaign rpt FAILED, shop_id = %s, e = %s' %
(shop_id, e))
def get_rpt(self, days):
last_date = date_2datetime(datetime.date.today() - datetime.timedelta(days = days))
return self.rpt_dict.get(last_date, self.empty_rpt)
def get_top_roi_kw(shop_id, rpt_days, kw_count, sort_mode):
'''sort_mode=-1 时，获取全店效果最好的关键词；sort_mode=1 时，获取全店效果最差的关键词'''
start_time = date_2datetime(datetime.date.today() -
datetime.timedelta(days=rpt_days))
query_dict = {
'shop_id': shop_id,
'cost': {
'$gt': 0
},
'date': {
'$gte': start_time
}
}
# match_str = (sort_mode > 0 and '$lt' or '$gt')
pipeline = [
{
'$match': query_dict
},
{
'$group': {
'_id': '$keyword_id',
'click': {
'$sum': '$click'
},
'cost': {
'$sum': '$cost'
},
'directpay': {
'$sum': '$directpay'
},
'indirectpay': {
'$sum': '$indirectpay'
},
'rpt_days': {
'$sum': 1
},
},
},
# {'$project':{'_id':1, 'pay':{'$sum': ['$directpay', '$indirectpay']}, 'cost':1}},
# {'$project':{'_id':1, 'roi':{'$divide':['$pay', '$cost']}, 'cost':1}},
{
'$project': {
'_id': 1,
'roi': {
'$divide': [{
'$add': ['$directpay', '$indirectpay']
}, '$cost']
},
'cost': 1
}
},
# {'$match':{'roi':{match_str:1}}},
{
'$sort': {
'roi': sort_mode,
'cost': -1
}
},
{
'$limit': kw_count
},
]
kw_list = Keyword.Report._get_collection().aggregate(pipeline)['result']
kw_id_list = [kw['_id'] for kw in kw_list]
return kw_id_list
