def integral_user_distribution(self, indate, bid):
'''
积分用户卡等级活跃
author : yt
create date : 2018-08-23
indate 处理数据日期
bid 商圈id
'''
user_integral_log = base_mode('online_userinfo_integral_log')
userinfo = base_mode('online_userinfo')
integralLog = self.db.query(user_integral_log.userId).filter(
user_integral_log.bid == bid,
user_integral_log.indate == indate).all()
integralLog_pd = pd.DataFrame(integralLog, columns=['userId'])
userinfoList = self.db.query(
userinfo.id, userinfo.cardLeve).filter(userinfo.bid == bid).all()
userinfoList_pd = pd.DataFrame(userinfoList,
columns=['id', 'cardLeve'])
integralList_pd = pd.merge(userinfoList_pd,
integralLog_pd,
how='inner',
left_on='id',
right_on='userId') # 根据用户合并取其交集
user_distribution = base_model_report('online_user_distribution')
# 积分用户卡等级统计--start
pd_cardLeve_count = integralList_pd.groupby(
['cardLeve']).size().reset_index(name="c_count") # 去重后数据
cardLevetmpall = []
for index, row in pd_cardLeve_count.iterrows():
cardLevetmp = {}
cardLevetmp['bid'] = int(bid)
cardLevetmp['typeId'] = 7
cardLevetmp['typeValueId'] = row['cardLeve']
cardLevetmp['userCount'] = int(row['c_count'])
cardLevetmp['indate'] = indate
cardLevetmp['intime'] = int(time.time())
cardLevetmpall.append(cardLevetmp)
try:
if cardLevetmpall:
# 判断当前日期是否已存在,数据已存在执行删除后在执行添加
self.db_report.query(user_distribution).filter(
user_distribution.typeId == 7,
user_distribution.indate == indate,
user_distribution.bid == bid).delete()
self.db_report.commit()
self.db_report.execute(user_distribution.__table__.insert(),
cardLevetmpall)
self.db_report.commit()
except:
self.AppLogging.warning("%d_积分用户统计卡等级统计错误%s" %
(bid, str(cardLevetmpall)))
def integral_cardLeve(self, indate, bid, userinfoList_pd):
'''
:卡等级用户积分占比趋势（柱状占比）
:author : yt
:create date : 2018-08-24
:param indate: 统计数据日期
:param bid: 商圈id
:return:
'''
userinfo = base_mode('online_userinfo')
userinfo_member_card = base_mode('online_userinfo_member_card')
integral_card_level = base_model_report('online_integral_card_level')
card_cate_list = self.db.query(userinfo_member_card.cardLeve).filter(
userinfo_member_card.bid == bid,
userinfo_member_card.status == 1).all()
card_cate_list_pd = pd.DataFrame(card_cate_list, columns=['cardLeve'])
merge_pd = pd.merge(card_cate_list_pd, userinfoList_pd,
on="cardLeve") # 合并取值
tmpall = []
for index, row in card_cate_list_pd.iterrows():
sumStr = merge_pd[merge_pd['cardLeve'] ==
row['cardLeve']]['integral'].sum() # 取各个卡等级的积分总值
tmp = {}
tmp['bid'] = bid
tmp['cardLeve'] = row['cardLeve']
tmp['sumIntegral'] = int(sumStr)
tmp['intime'] = int(time.time())
tmp['indate'] = indate
tmpall.append(tmp)
try:
if tmpall:
# 判断当前日期是否已存在,数据已存在执行删除后在执行添加
self.db_report.query(integral_card_level).filter(
integral_card_level.indate == indate,
integral_card_level.bid == bid).delete()
self.db_report.commit()
self.db_report.execute(integral_card_level.__table__.insert(),
tmpall)
self.db_report.commit()
except:
self.AppLogging.warning("%d_卡等级用户积分占比趋势统计错误%s" %
(bid, str(tmpall)))
def messageShow(self, indate, bid):
'''
:desc: 计算消息--消息发送次数
:author: yt
:create date: 2018-08-27
:param indate: 数据日期
:param bid: 商圈id
:return:
'''
message_log = base_mode("online_message_list")
message = base_model_report("online_message")
messageList = self.db.query(message_log.errorCode,
message_log.sendType,
message_log.fromType).filter(
message_log.bid == bid,
message_log.indate == indate).all()
messageList_pd = pd.DataFrame(
messageList, columns=['errorCode', 'sendType', 'fromType'])
sendCount = messageList_pd['sendType'].count() # 发送次数
sendSuccessCount = messageList_pd[messageList_pd['errorCode'] ==
0]['errorCode'].count() # 发送成功
smsCount = messageList_pd[messageList_pd['sendType'] ==
1]['sendType'].count() # 手机短信
wxtemplateCount = messageList_pd[messageList_pd['sendType'] ==
2]['sendType'].count() # 微信模板消息
integralCount = messageList_pd[messageList_pd['fromType'] ==
1]['fromType'].count() # 积分
counponsCount = messageList_pd[messageList_pd['fromType'] ==
4]['fromType'].count() # 卡券
gameCount = messageList_pd[messageList_pd['fromType'] ==
5]['fromType'].count() # 组件
marketCount = messageList_pd[messageList_pd['fromType'] ==
6]['fromType'].count() # 营销
isHaveData = self.db_report.query(message.__table__).filter(
message.bid == bid, message.indate).scalar()
add_message = message(id=isHaveData,
bid=bid,
sendCount=int(sendCount),
smsCount=int(smsCount),
sendSuccessCount=int(sendSuccessCount),
wxtemplateCount=int(wxtemplateCount),
counponsCount=int(counponsCount),
integralCount=int(integralCount),
gameCount=int(gameCount),
marketCount=int(marketCount),
indate=indate,
intime=time.time())
try:
self.db_report.merge(add_message)
self.db_report.commit()
except:
self.AppLogging.warning("%d_计算消息统计错误%s" %
(bid, str(messageList_pd)))
def userinfo_cardLeve(self, todayHyUserList_pd, indate, bid):
'''
:desc:卡等级用户活跃：以”卡等级“为维度统计会员活跃---[查询活跃表]取出商圈的卡等级后过滤活跃用户，卡等级个数的连续柱状图 online_userinfo_member_card cardLeve
:author: yt
:create date: 2018-08-29
:param todayHyUserList: 过滤后的用户活跃数据
:param indate: 数据日期
:param bid: 商圈id
:return:
'''
userinfo_member_card = base_mode("online_userinfo_member_card")
card_cate_list = self.db.query(userinfo_member_card.cardLeve).filter(
userinfo_member_card.bid == bid,
userinfo_member_card.status == 1).all()
card_cate_list_pd = pd.DataFrame(card_cate_list, columns=['cardLeve'])
out_card_cater = todayHyUserList_pd.groupby(
['cardLeve']).size().reset_index(name='c_count')
# print(out_card_cater)
# print(out_card_cater[out_card_cater['cardLeve'].isin(['黑金卡'])])
# return
activity_card_level = base_model_report("online_activity_card_level")
tmpArr = []
for index, row in card_cate_list_pd.iterrows():
tmp = {}
tmp['bid'] = bid
tmp['cardLeve'] = row['cardLeve']
tmp['indate'] = indate
tmp['intime'] = time.time()
isHave = out_card_cater[out_card_cater['cardLeve'].isin(
[row['cardLeve']])]
if isHave.empty: # 不存在
tmp['userCount'] = 0
else: # 存在取出对应卡等级活跃用户数量
tmp['userCount'] = int(out_card_cater[
out_card_cater['cardLeve'] == row['cardLeve']]['c_count'])
tmpArr.append(tmp)
try:
# 判断当前日期是否已存在,数据已存在执行删除后在执行添加
self.db_report.query(activity_card_level).filter(
activity_card_level.indate == indate,
activity_card_level.bid == bid).delete()
self.db_report.commit()
self.db_report.execute(activity_card_level.__table__.insert(),
tmpArr)
self.db_report.commit()
except:
self.AppLogging.warning("%d_前日期商圈的卡等级用户活跃统计错误%s" %
(bid, str(tmpArr)))
def post(self):
sync_result = {
'bid': 1,
'body': 'fsfsfsfs',
'gmt_payment': '2018-06-25 18:23:42',
'gmt_refund': '2018-07-06 15:11:46.462',
'out_trade_no': '152992219042431011',
'trade_no': 'qeqeqeqe',
'total_amount': '9.60',
'refund_fee': '9.60',
' trade_no': '2018062521001004680536986176'
}
common = Common() # 实例化常用类对象
#sync_result = json.loads(self.request.body) # 获取回调全部订单参数
bid = sync_result['bid'] # 商圈ID
body = sync_result['body'] # 商品描述
out_trade_no = sync_result['out_trade_no'] # 商户订单号
trade_no = sync_result['trade_no'] # 支付宝交易号
total_amount = sync_result['total_amount'] # 订单金额 单位元
refund_fee = sync_result['refund_fee'] # 总退款金额 单位元
'''
订单信息插入mysql数据库
'''
online_alipay_order_list = base_mode('online_alipay_order_list') #直接使用引入库的表
alipay_order_list = online_alipay_order_list(bid = bid,
body = body,
out_trade_no= out_trade_no,
trade_no = trade_no,
total_amount = total_amount * 100,
refund_fee = refund_fee * 100,
tradeStatus = 1, #交易成功
intime = str(common.current_stamp())
)
try:
self.db.merge(alipay_order_list) # 类似于add添加数据，但是主键添加存在则修改信息 * db指的是shopmall_main_db库
self.db.commit()
return self.write('success')
except:
self.AppLogging.warning("支付订单信息写入数据库错误%s" % sync_result)
def autoIntegral(self, indate, bid):
'''
:desc: 自助积分统计：数据统计包含：拍照积分、扫码积分、已审核，未审核
:author: yt
:create date: 2018-08-28
:param indate: 数据日期
:param bid: 商圈id
:return:
'''
indate = 1498752000
auto_integral = base_mode("online_auto_integral")
business_shops = base_mode("online_business_shops")
auto_integral_report = base_model_report("online_auto_integral")
# 获取当前商圈可用状态的店铺
shop_list = self.db.query(business_shops.id,
business_shops.shopName).filter(
business_shops.bid == bid,
business_shops.status == 1,
business_shops.isDel == 0).all()
shop_list_pd = pd.DataFrame(shop_list, columns=['id', 'shopName'])
# 获取当前日期商圈的自助积分列表
auto_integral_list = self.db.query(
auto_integral.id, auto_integral.sid, auto_integral.type,
auto_integral.status).filter(auto_integral.bid == bid,
auto_integral.indate == indate).all()
auto_integral_list_pd = pd.DataFrame(
auto_integral_list, columns=['id', 'sid', 'type', 'status'])
tmpArr = []
for index, row in shop_list_pd.iterrows():
scavenging_integral = auto_integral_list_pd[
(auto_integral_list_pd['sid'] == row['id'])
& (auto_integral_list_pd['type'] == 1)]['id'].count(
) # 获取扫码当天扫码积分总数目
photo_integral = auto_integral_list_pd[
(auto_integral_list_pd['sid'] == row['id'])
& (auto_integral_list_pd['type'] == 2)]['id'].count(
) # 获取当天上传积分总数目
checkCount = auto_integral_list_pd[
(auto_integral_list_pd['sid'] == row['id'])
& (auto_integral_list_pd['type'] == 2) &
(auto_integral_list_pd['status']
== 2)]['id'].count() # 获取已审核总数量
noCheckCount = auto_integral_list_pd[
(auto_integral_list_pd['sid'] == row['id'])
& (auto_integral_list_pd['type'] == 2) &
(auto_integral_list_pd['status']
== 1)]['id'].count() # 获取未审核总数量
tmp = {}
tmp['bid'] = bid
tmp['shop_id'] = int(row['id'])
tmp['photo_integral'] = int(photo_integral)
tmp['scavenging_integral'] = int(scavenging_integral)
tmp['cancelCount'] = int(checkCount)
tmp['noCancelCount'] = int(noCheckCount)
tmp['intime'] = time.time()
tmp['indate'] = indate
tmpArr.append(tmp)
try:
# 判断当前日期是否已存在,数据已存在执行删除后在执行添加
self.db_report.query(auto_integral_report).filter(
auto_integral_report.indate == indate,
auto_integral_report.bid == bid).delete()
self.db_report.commit()
self.db_report.execute(auto_integral_report.__table__.insert(),
tmpArr)
self.db_report.commit()
except:
self.AppLogging.warning("%d_前日期商圈的自助积分统计错误%s" % (bid, str(tmpArr)))
# 所有扫码积分数，这里不等于商铺Id的总和，因为数据可能不存在商铺id
all_scavenging_integral = auto_integral_list_pd[(
auto_integral_list_pd['type'] == 1)]['id'].count() # 获取扫码当天扫码积分总数目
all_photo_integral = auto_integral_list_pd[(
auto_integral_list_pd['type'] == 2)]['id'].count() # 获取当天上传积分总数目
all_checkCount = \
auto_integral_list_pd[(auto_integral_list_pd['type'] == 2) & (auto_integral_list_pd['status'] == 2)][
'id'].count() # 获取已审核总数量
all_noCheckCount = \
auto_integral_list_pd[(auto_integral_list_pd['type'] == 2) & (auto_integral_list_pd['status'] == 1)][
'id'].count() # 获取未审核总数量
all_add_integral = auto_integral_report(
bid=bid,
shop_id=int(-1),
photo_integral=int(all_photo_integral),
scavenging_integral=int(all_scavenging_integral),
cancelCount=int(all_checkCount),
noCancelCount=int(all_noCheckCount),
intime=time.time(),
indate=indate)
try:
self.db_report.add(all_add_integral)
self.db_report.commit()
except:
arr = [
str(all_scavenging_integral),
str(all_photo_integral),
str(all_checkCount),
str(all_noCheckCount)
]
jsonObj = json.dumps(arr)
self.AppLogging.warning("%d_前日期商圈的自助积分统计错误%s :" %
(bid, str(jsonObj)))
def integralShop(self, indate, bid):
'''
积分商城
商品预定数---[查询商品预定log]兑换没有核销
商品核销数---[查询核销log表]
商品预定排行---[查询商品预定log]（前20个商品）时间区间排行
商品核销排行---[查询核销log表]（前20个商品）时间区间排行
:author : yt
:create date : 2018-08-24
:param indate: 数据日期
:param bid: 商圈id
:return:
'''
integral_order_log = base_mode("online_integral_order_log")
integral_goods = base_mode("online_integral_goods")
integral_shop = base_model_report("online_integral_shop")
order_log_list = self.db.query(
integral_order_log.userId, integral_order_log.goodId,
integral_order_log.buyCount,
integral_order_log.integralOrderStatus).filter(
integral_order_log.indate == indate,
integral_order_log.bid == bid).all()
order_log_list_pd = pd.DataFrame(
order_log_list,
columns=['userId', 'goodId', 'buyCount', 'integralOrderStatus'])
orderCount = order_log_list_pd[order_log_list_pd['integralOrderStatus']
== 1]['buyCount'].sum() # 下单总量
checkCount = order_log_list_pd[order_log_list_pd['integralOrderStatus']
== 3]['buyCount'].sum() # 已核销总量
orderUserCount = order_log_list_pd[
order_log_list_pd['integralOrderStatus'] == 1].groupby(
['userId']).size().count()
cancelUserCount = order_log_list_pd[
order_log_list_pd['integralOrderStatus'] == 3].groupby(
['userId']).size().count()
integral_goods_List = self.db.query(integral_goods.stock).filter(
integral_goods.bid == bid, integral_goods.isDel == 0).all()
allStork = pd.DataFrame(integral_goods_List,
columns=['stock'])['stock'].sum() # 积分商城商品库存
shoptmpAll = []
shoptmp = {}
shoptmp['bid'] = bid
shoptmp['allStork'] = int(allStork) # 总库存
shoptmp['orderUserCount'] = int(orderUserCount)
shoptmp['orderCount'] = int(orderCount)
shoptmp['cancelUserCount'] = int(cancelUserCount)
shoptmp['cancelCount'] = int(checkCount)
shoptmp['indate'] = indate
shoptmp['intime'] = int(time.time())
shoptmpAll.append(shoptmp)
try:
if shoptmpAll:
# 判断当前日期是否已存在,数据已存在执行删除后在执行添加
self.db_report.query(integral_shop).filter(
integral_shop.indate == indate,
integral_shop.bid == bid).delete()
self.db_report.commit()
self.db_report.execute(integral_shop.__table__.insert(),
shoptmpAll)
self.db_report.commit()
except:
self.AppLogging.warning("%d_积分商城统计错误%s" % (bid, str(shoptmpAll)))
# 订购排行，降序排行
integral_shop_order_ranking = base_model_report(
'online_integral_shop_order_ranking')
buyRank = order_log_list_pd[
order_log_list_pd['integralOrderStatus'] == 1].groupby([
'goodId'
]).size().reset_index(name='c_count').sort_values(by='c_count',
ascending=False)
buyRankTop20 = buyRank[0:20] # 获取排行前20
ranktmpArr = []
for index, row in buyRankTop20.iterrows():
ranktmp = {}
ranktmp['bid'] = bid
ranktmp['goodId'] = int(row['goodId'])
ranktmp['buyCount'] = int(row['c_count'])
ranktmp['indate'] = indate
ranktmp['intime'] = time.time()
ranktmpArr.append(ranktmp)
try:
if ranktmpArr:
# 判断当前日期是否已存在,数据已存在执行删除后在执行添加
self.db_report.query(integral_shop_order_ranking).filter(
integral_shop_order_ranking.indate == indate,
integral_shop_order_ranking.bid == bid).delete()
self.db_report.commit()
self.db_report.execute(
integral_shop_order_ranking.__table__.insert(), ranktmpArr)
self.db_report.commit()
except:
self.AppLogging.warning("%d_积分商城订购排行统计错误%s" %
(bid, str(ranktmpArr)))
# 核销排行，降序排行
integral_shop_cancel_ranking = base_model_report(
'online_integral_shop_cancel_ranking')
checkRank = order_log_list_pd[
order_log_list_pd['integralOrderStatus'] == 3].groupby([
'goodId'
]).size().reset_index(name='c_count').sort_values(by='c_count',
ascending=False)
checkRankTop20 = checkRank[0:20] # 获取排行前20
checkRanktmpArr = []
for index, row in checkRankTop20.iterrows():
checkRanktmp = {}
checkRanktmp['bid'] = bid
checkRanktmp['goodId'] = int(row['goodId'])
checkRanktmp['buyCount'] = int(row['c_count'])
checkRanktmp['indate'] = indate
checkRanktmp['intime'] = time.time()
checkRanktmpArr.append(checkRanktmp)
try:
if ranktmpArr:
# 判断当前日期是否已存在,数据已存在执行删除后在执行添加
self.db_report.query(integral_shop_cancel_ranking).filter(
integral_shop_cancel_ranking.indate == indate,
integral_shop_cancel_ranking.bid == bid).delete()
self.db_report.commit()
self.db_report.execute(
integral_shop_cancel_ranking.__table__.insert(),
checkRanktmpArr)
self.db_report.commit()
except:
self.AppLogging.warning("%d_积分商城核销排行统计错误%s" %
(bid, str(checkRanktmpArr)))
def integralAddUse_SumCount(self, indate, bid, userinfoList_pd):
'''
计算积分-积分增加额度和笔数-积分使用额度和笔数
author : yt
create date : 2018-08-24
indate 处理数据日期
bid 商圈id
'''
user_integral_log = base_mode('online_userinfo_integral_log')
allIntegral = userinfoList_pd['integral'].sum() # 总剩余积分
# 读取积分记列表
integralList = self.db.query(user_integral_log.userId,
user_integral_log.money,
user_integral_log.integral,
user_integral_log.changeType).filter(
user_integral_log.indate == indate,
user_integral_log.bid == bid).all()
integralList_pd = pd.DataFrame(
integralList,
columns=['userId', 'money', 'integral', 'changeType'])
pd_integral_add_sum = integralList_pd[integralList_pd['changeType'] ==
1].sum() # 积分增加计算
pd_integral_use_sum = integralList_pd[integralList_pd['changeType'] ==
2].sum() # 积分减少计算
addUserCount = integralList_pd[integralList_pd['changeType'] ==
1].groupby(['userId'
]).size().count() # 积分增加用户数
useUserCount = integralList_pd[integralList_pd['changeType'] ==
2].groupby(['userId'
]).size().count() # 积分减少用户数
moneyCount = integralList_pd[
integralList_pd['money'] > 0]['money'].count() # 消费笔数
moneyUserCount = integralList_pd[integralList_pd['money'] > 0].groupby(
['userId']).size().count() # 消费人数
activeCount = integralList_pd['userId'].count() # 积分活跃人次
activeUserCount = integralList_pd.groupby(['userId'
]).size().count() # 积分活跃人数
tmp = {}
tmp['bid'] = int(bid)
tmp['allIntegral'] = int(allIntegral) # 总剩余积分
tmp['moneySum'] = int(pd_integral_use_sum['money']) # 消费总金额
tmp['moneyCount'] = int(moneyCount) # 消费笔数
tmp['moneyUserCount'] = int(moneyUserCount) # 消费人数
tmp['addSum'] = int(pd_integral_add_sum['integral']) # 增加积分总数
tmp['addCount'] = int(pd_integral_add_sum['changeType']) # 增加笔数
tmp['addUserCount'] = int(addUserCount) # 增加人数
tmp['useSum'] = int(pd_integral_use_sum['integral']) # 使用积分总数
tmp['useCount'] = int(pd_integral_use_sum['changeType'] / 2) # 使用笔数
tmp['useUserCount'] = int(useUserCount) # 使用人数
tmp['activeCount'] = int(activeCount) # 积分活跃人次
tmp['activeUserCount'] = int(activeUserCount) # 积分活跃人数
tmp['indate'] = indate
tmp['intime'] = int(time.time())
user_integral = base_model_report('online_user_integral')
try:
# 判断当前日期是否已存在,数据已存在执行删除后在执行添加
self.db_report.query(user_integral).filter(
user_integral.indate == indate,
user_integral.bid == bid).delete()
self.db_report.commit()
self.db_report.execute(user_integral.__table__.insert(), tmp)
self.db_report.commit()
except:
self.AppLogging.warning("%d_积分增加额度和笔数统计错误%s" % (bid, str(tmp)))
def get(self):
# analysis_list = base_mode('online_analysis_list')
# dbfind = self.db.query(analysis_list.id, analysis_list.title).filter(analysis_list.id < 100).all()
# self.write(str(self.db.query(analysis_list.id, analysis_list.title).filter(analysis_list.id < 100)))#打印sql语句
# self.write( str(dbfind)) # 获取查询的内容信息
inarr = self.dbtime()
# 默认执行前一天数据
ydt = self.getDatetimeYesterday()
btime = self.get_argument(
"sDate",
int(time.mktime(time.strptime(str(ydt), "%Y-%m-%d %H:%M:%S"))))
etime = btime
userinfo = base_mode('online_userinfo')
user_active = base_model_report('online_user_active') # 用户活跃表
a_business_statistics = base_mode(
'online_a_business_statistics') # 商圈统计数据行为表
behavior_list = base_mode_conduct('online_behavior_list') # 用户行为分析表
userinfo = base_mode('online_userinfo') # 会员表
# 测试
# sindate = 1467907200
indate = 1505664000
bid = 15
# 获取某个日期及之前的数据
userinfoList = self.db.query(
userinfo.id, userinfo.sid, userinfo.cardLeve, userinfo.age,
userinfo.sex, userinfo.fromId, userinfo.integral, userinfo.wxId,
userinfo.sourceFrom).filter(userinfo.bid == bid,
userinfo.indate <= indate).all()
userinfoList_pd = pd.DataFrame(userinfoList,
columns=[
'id', 'sid', 'cardLeve', 'age',
'sex', 'fromId', 'integral', 'wxId',
'sourceFrom'
])
# 获取当前日期的数据
userinfoDayList = self.db.query(
userinfo.id, userinfo.sid, userinfo.cardLeve, userinfo.age,
userinfo.sex, userinfo.fromId, userinfo.integral, userinfo.wxId,
userinfo.sourceFrom).filter(userinfo.bid == bid,
userinfo.indate == indate).all()
userinfoDayList_pd = pd.DataFrame(userinfoDayList,
columns=[
'id', 'sid', 'cardLeve', 'age',
'sex', 'fromId', 'integral',
'wxId', 'sourceFrom'
])
# 商圈活跃数
todayHyList = self.db_conduct.query(behavior_list.__table__).filter(
behavior_list.bid == bid,
behavior_list.indate == indate).limit(500000).all()
todayHyList_pd = pd.DataFrame(todayHyList,
columns=[
'id', 'userType', 'uid', 'bid',
'sid', 'typeId', 'otherId1',
'otherId2', 'otherId3', 'otherId4',
'otherId5', 'ucount', 'card_cate',
'intime', 'indate'
])
# self.electBindingUser(indate, bid, userinfoList_pd, userinfoDayList_pd)
# 计算商圈天活跃
todayHyList = self.outDayHuoyue(todayHyList_pd, user_active, indate,
bid)
todayHyUidList = pd.merge(todayHyList[todayHyList['userType'] == 1],
userinfoList_pd,
how='left',
left_on='uid',
right_on='id') # 获取卡的等级信息
todayHyWxIdList = pd.merge(todayHyList[todayHyList['userType'] == 2],
userinfoList_pd,
how='left',
left_on='uid',
right_on='wxId') # 获取卡的等级信息根据wxID
todayHyUserList_pd = todayHyUidList.append(todayHyWxIdList)
self.userinfo_cardLeve(todayHyUserList_pd, indate, bid)
return
# 取出还没有执行的商圈（每次只执行一个商圈）, 且上次执行时间小于今天的
dt = datetime.strptime(str(date.today()),
'%Y-%m-%d') # date转str再转datetime
todayTime = int(
time.mktime(time.strptime(str(dt),
"%Y-%m-%d %H:%M:%S"))) # 今天0点时间戳
blist = self.db.query(a_business_statistics.__table__).filter(
'Behavior=0 and BehaviorTime<' + str(todayTime)).all()
if len(blist) == 0: # 都执行完，把所有商圈状态还原为0，隔天再次执行
self.db.query(a_business_statistics).update({
'Behavior':
'0',
'uptime':
int(time.time())
})
self.db.commit()
return
for i in range(btime, etime, 86400):
indate = i
for blist_value in blist: # 循环所有商圈
bid = blist_value.bid
self.db.query(a_business_statistics).filter(
a_business_statistics.id == blist_value.id).update({
'Behavior':
'1',
'uptime':
int(time.time())
}) # 把商圈状态改为1
self.db.commit()
# 获取某个日期及之前的数据
userinfoList = self.db.query(
userinfo.id, userinfo.sid, userinfo.cardLeve, userinfo.age,
userinfo.sex, userinfo.fromId, userinfo.integral,
userinfo.wxId, userinfo.sourceFrom).filter(
userinfo.bid == bid, userinfo.indate <= indate).all()
userinfoList_pd = pd.DataFrame(userinfoList,
columns=[
'id', 'sid', 'cardLeve',
'age', 'sex', 'fromId',
'integral', 'wxId',
'sourceFrom'
])
# 获取当前日期的数据
userinfoDayList = self.db.query(
userinfo.id, userinfo.sid, userinfo.cardLeve, userinfo.age,
userinfo.sex, userinfo.fromId, userinfo.integral,
userinfo.wxId, userinfo.sourceFrom).filter(
userinfo.bid == bid, userinfo.indate == indate).all()
userinfoDayList_pd = pd.DataFrame(userinfoDayList,
columns=[
'id', 'sid', 'cardLeve',
'age', 'sex', 'fromId',
'integral', 'wxId',
'sourceFrom'
])
self.get_user_distribution(indate, bid, 1,
userinfoList_pd) # 当前日期及之前用户分布统计
self.get_user_distribution(indate, bid, 2,
userinfoDayList_pd) # 当前日期会员增长分布统计
self.integral_user_distribution(indate, bid) # 万菱汇商圈积分活跃用户分布统计
self.integralAddUse_SumCount(
indate, bid, userinfoList_pd) # 计算积分-积分增加额度和笔数-积分使用额度和笔数
self.integral_cardLeve(indate, bid, userinfoList_pd) # 卡等级积分统计
self.integralShop(indate, bid) # 积分商城
self.messageShow(indate, bid) # 消息发送次数
self.autoIntegral(indate, bid) # 扫码积分，小票
self.electBindingUser(indate, bid, userinfoList_pd,
userinfoDayList_pd) # 会员数:包括电子会员和绑定的卡会员
# 商圈活跃数
todayHyList = self.db_conduct.query(
behavior_list.__table__).filter(
behavior_list.bid == bid,
behavior_list.indate == indate).limit(500000).all()
todayHyList_pd = pd.DataFrame(
todayHyList,
columns=[
'id', 'userType', 'uid', 'bid', 'sid', 'typeId',
'otherId1', 'otherId2', 'otherId3', 'otherId4',
'otherId5', 'ucount', 'card_cate', 'intime', 'indate'
])
# 计算商圈天活跃
todayHyList = self.outDayHuoyue(todayHyList_pd, user_active,
indate, bid)
todayHyUidList = pd.merge(
todayHyList[todayHyList['userType'] == 1],
userinfoList_pd,
how='left',
left_on='uid',
right_on='id') # 获取卡的等级信息根据id
todayHyWxIdList = pd.merge(
todayHyList[todayHyList['userType'] == 2],
userinfoList_pd,
how='left',
left_on='uid',
right_on='wxId') # 获取卡的等级信息根据wxID
todayHyUserList_pd = todayHyUidList.append(
todayHyWxIdList) # 合并二个pandas
self.userinfo_cardLeve(todayHyUserList_pd, indate,
bid) # 卡等级用户活跃
def electBindingUser(self, indate, bid, userinfoList_pd,
userinfoDayList_pd):
'''
:Desc:会员数:包括电子会员和绑定的卡会员---[查询用户表]isRealCard=1实体卡 电子会员增长、绑卡会员的增长、绑卡会员的解绑
:author: yt
:create date: 2018-08-29
:param indate: 数据日期
:param bid: 商圈id
:param userinfoDayList_pd: 当前日期用户数据
:return:
'''
# 根据各商圈卡等级查电子会员和绑卡会员
aBusinessUser = base_mode('online_a_business_user')
userinfo_member_card = base_mode('online_userinfo_member_card')
user_active = base_model_report('online_user_active')
card_cate_list = self.db.query(
userinfo_member_card.cardLeve,
userinfo_member_card.is_default).filter(
userinfo_member_card.bid == bid,
userinfo_member_card.status == 1).all()
card_cate_list_pd = pd.DataFrame(card_cate_list,
columns=['cardLeve', 'is_default'])
isRealCard = self.db.query(aBusinessUser).filter(
aBusinessUser.id == bid).first().isRealCard
if isRealCard == 0: # 存在实体卡
electUserCount = userinfoList_pd['id'].count()
electUserAdd = userinfoDayList_pd['id'].count()
bindingUserCount = 0
bindingUserAdd = 0
else: # 不存在实体卡
electUserAdd = 0
bindingUserAdd = 0
try:
isDefaultCardLeve = card_cate_list_pd[
card_cate_list_pd['is_default'] == 1]['cardLeve'][0]
electUserCount = userinfoList_pd[
userinfoList_pd['cardLeve'] ==
isDefaultCardLeve]['id'].count()
bindingUserCount = userinfoList_pd[
userinfoList_pd['cardLeve'] !=
isDefaultCardLeve]['id'].count()
# 当前日期增长会员
electUserAdd = userinfoDayList_pd[
userinfoDayList_pd['cardLeve'] ==
isDefaultCardLeve]['id'].count()
bindingUserAdd = userinfoDayList_pd[
userinfoDayList_pd['cardLeve'] !=
isDefaultCardLeve]['id'].count()
except:
electUserCount = 0
bindingUserCount = 0
userinfoDb = base_mode('online_userinfo')
cancelBindingUserList = self.db.query(
userinfoDb.cancelRealCardDate).filter(
userinfoDb.bid == bid,
userinfoDb.cancelRealCardDate == indate).all()
cancelBindingUserList_pd = pd.DataFrame(cancelBindingUserList,
columns=['cancelRealCardDate'])
cancelBindingUserAdd = cancelBindingUserList_pd[
'cancelRealCardDate'].count()
isHave = self.db_report.query(user_active.__table__).filter(
user_active.bid == bid, user_active.indate == indate).scalar()
add_user_active = user_active(
id=isHave,
bid=bid,
electUserCount=int(electUserCount),
bindingUserCount=int(bindingUserCount),
electUserAdd=int(electUserAdd),
bindingUserAdd=int(bindingUserAdd),
cancelBindingUserAdd=int(cancelBindingUserAdd),
intime=time.time(),
indate=indate)
try:
self.db_report.merge(add_user_active)
self.db_report.commit()
except:
self.AppLogging.warning("%d_商圈会员数统计错误:" % bid)
def get(self):
bidstr = self.get_argument("bid", None)
# bidstr = '15_0995b62416f21f59ba6baba7b203016b'
if not bidstr:
self.write('请选择商圈')
return {}
bids = bidstr.split('_')
bids.append(0)
bids.append(0)
bid = bids[0]
mkey = bids[1]
if not bid or not mkey:
self.write('参数错误')
return {}
md5key = 'requestmmmdddd5555'
m2 = hashlib.md5()
strss = md5key + '' + str(bid)
m2.update(strss.encode('utf-8'))
thismkey = m2.hexdigest()
if not thismkey == mkey:
self.write('校验串错误')
return {}
# 取出微信的微信数据
# bid = '233'
weixindb = base_mode('online_business_weixin_config')
query = self.db.query(weixindb)
s = query.filter(weixindb.bid == bid).first()
weixin_AppID = s.weixin_AppID
weixin_AppSecret = s.weixin_AppSecret
# 公用token
# 导入微信类
wxClass = class_weixin_api(bid, weixin_AppID, weixin_AppSecret)
# 正式服务器
tk = wxClass.getCredentialAccessToken()
print(tk)
##############---取微信的数据--start----################
nextOpenid = 'start'
openidList = []
self.AppLogging.info('获取所有opendid开始')
while (nextOpenid):
if nextOpenid == 'start':
nextOpenid = ''
weixinAllOpenid = wxClass.getAllOpenidList(nextOpenid)
nextOpenid = weixinAllOpenid['next_openid']
if not nextOpenid:
break
openidList.extend(weixinAllOpenid['data']['openid']) # 追加数据
self.AppLogging.info("获取到所有openidlist:%s个" % len(openidList))
# print(openidList)
# 取出商圈的所有数据
wx = base_mode('online_userinfo_weixin')
ulist = self.db.query(wx.openid, wx.id).filter(wx.bid == bid).all()
db_openlist = {}
for index in ulist:
db_openlist[index[0]] = index
producer = KafkaProducer(
bootstrap_servers=self.
DefaultValues['WeixinUpdate_bootstrap_servers'],
value_serializer=lambda v: json.dumps(v).encode('utf-8'))
# bytes(openid, encoding = "utf8")
clearCount = 0
qOpenidList = list(set(db_openlist).difference(set(openidList)))
for qopenid in qOpenidList:
updateUserId = db_openlist[qopenid][1]
dblist = {
'subscribe': 0,
'subscribe_time': 0,
'uptime': int(time.time()),
}
rr = self.db.query(wx).filter(wx.id == updateUserId).update(
dblist, synchronize_session=False)
self.db.commit()
clearCount = clearCount + 1
print("获取到所有openidlist:%s个" % len(openidList))
print('清除：' + str(clearCount))
ii = 1
openidFindList = {}
for openid in openidList:
# 吧数据库内数据添加到内部，判断是否有更改，
openidFindList['bid'] = bid
if openid in db_openlist:
openidFindList[openid] = db_openlist[openid][1]
else:
openidFindList[openid] = 0
if ii % 100 == 0: # 接口可以一次性读取100条数据，那么每100条数据提交kafka
producer.send(self.DefaultValues['WeixinUpdate_topic'],
value=openidFindList)
openidFindList = {}
ii = ii + 1
if openidFindList:
producer.send(self.DefaultValues['WeixinUpdate_topic'],
value=openidFindList)
producer.flush()
self.write('已经发送到消息队列处理中。。。。')
def post(self):
common = Common()
print(common.re_phone('13631255697'))
bid = self.get_argument('bid')
payConfig = self.DefaultValues[bid]
out_trade_no = self.get_argument('out_trade_no')
refund_amount = self.get_argument('refund_amount')
# if self.get_argument('out_request_no'):
# _request_no = self.get_argument('out_request_no')
#self.write(str(self.get_argument('out_request_no')))
out_request_no = common.random_str(16)
# 设置配置信息
alipay_client_config = AlipayClientConfig()
alipay_client_config.server_url = payConfig['zfbpay']['url']
alipay_client_config.app_id = payConfig['zfbpay']['appId']
alipay_client_config.app_private_key = payConfig['zfbpay']['app_private_key']
alipay_client_config.alipay_public_key = payConfig['zfbpay']['alipay_public_key']
# 客户端对象
client = DefaultAlipayClient(alipay_client_config=alipay_client_config, logger=logger)
# 直接使用引入库的表
online_alipay_order_list = base_mode('online_alipay_order_list')
# 获取该笔订单金额
order_info = self.db.query(online_alipay_order_list).filter(online_alipay_order_list.bid == bid,
online_alipay_order_list.out_trade_no == out_trade_no).scalar()
# 退款金额大于该笔订单金额 退款失败
if int(refund_amount) > int(order_info.total_amount):
info = {
'errCode': -1,
'errMsg': '退款失败，退款金额大于该订单金额'
}
result = {
'result': info
}
return self.write(json.dumps(result, ensure_ascii=False))
# 构造请求对象
model = AlipayTradeRefundModel()
model.out_trade_no = out_trade_no
model.refund_amount = refund_amount
model.out_request_no = out_request_no
request = AlipayTradeRefundRequest(biz_model=model)
response_content = None
try:
response_content = client.execute(request)
except Exception as e:
print('1-' + traceback.format_exc())
if not response_content:
print("2-failed execute")
else:
response = AlipayTradeRefundResponse()
# 解析响应结果
response.parse_response_content(response_content)
if response.is_success():
'''
退款成功更新订单信息
'''
if not order_info.refundList:
refund_list = []
else:
refund_list = eval(order_info.refundList)
refundInfo = {}
refundInfo['refund_no'] = out_request_no
refundInfo['refund_fee'] = int(refund_amount)
refundInfo['refund_status'] = 1 #退款成功
refundInfo['refund_date'] = common.current_stamp()
refund_list.append(refundInfo)
self.db.query(online_alipay_order_list).filter(
online_alipay_order_list.bid == bid,
online_alipay_order_list.out_trade_no == out_trade_no).update(
{
'totalFeeLeft': int(order_info.totalFeeLeft) - int(refund_amount),
'refundList': str(refund_list)
})
self.db.commit()
# 业务成功返回
info = {
'errCode': '0',
'errMsg': '',
'detail': {
'refund_fee': response.refund_fee
}
}
result = {
'result': info
}
return self.write(json.dumps(result, ensure_ascii=False))
else:
'''
退款失败更新订单信息
'''
if not order_info.refundList:
refund_list = []
else:
refund_list = eval(order_info.refundList)
refundInfo = {}
refundInfo['refund_no'] = out_request_no
refundInfo['refund_fee'] = int(refund_amount)
refundInfo['refund_status'] = 2 #退款失败
refundInfo['refund_date'] = common.current_stamp()
refundInfo['refund_errmsg'] = response.sub_msg
refund_list.append(refundInfo)
self.db.query(online_alipay_order_list).filter(
online_alipay_order_list.bid == bid,
online_alipay_order_list.out_trade_no == out_trade_no).update(
{
'refundList': str(refund_list)
})
self.db.commit()
# 业务失败
info = {
'errCode': response.code,
'errMsg': response.sub_msg
}
result = {
'result': info
}
return self.write(json.dumps(result, ensure_ascii=False))
def get(self):
dodate = int(
time.mktime(
time.strptime(time.strftime('%Y-%m-%d', time.localtime()),
'%Y-%m-%d')))
# 取出用户提交的
alist = self.db.query(user_list).filter(
user_list.isDel == 0, user_list.dodate <= dodate).all()
# alist = self.db.query(user_list).filter(user_list.isDel == 0).all()
# 有日期的节点分类
Analysis_data_list = self.DefaultValues['Analysis_data_list']
dateCateListFind = self.db.query(analysis_list.id).filter(
analysis_list.cate_id.in_(Analysis_data_list)).all()
dateCateList = []
for val in dateCateListFind:
dateCateList.append(val[0])
btime = dodate - 86400
etime = dodate
for i in range(btime, etime, 86400):
indate = i
for alist_value in alist:
funcStime = time.time() #开始时间
bid = alist_value.bid
logStrs = '当前计算日期：' + time.strftime(
"%Y-%m-%d", time.localtime(indate)) + '<br>'
logStrs = logStrs + '当前时间：' + time.strftime(
"%Y-%m-%d %H:%I:%S", time.localtime(time.time())) + '<br>'
logStrs = logStrs + '当前bid：' + str(bid) + '<br>'
logStrs = logStrs + '当前分析器id：' + str(alist_value.id) + '<br>'
logStrs = logStrs + '计算分析器名称：' + alist_value.title + '<br>'
self.checkTableName(bid)
nodeIds = alist_value.nodeIds
nodeIdsArr = nodeIds.split(',')
returnArr = {}
nodeOutArr = {}
dateCate = 0
# 日期分类,找出有日期参数的，如果没有回取默认值
# 吧数据翻转，同样的节点内的数据统一执行
for nodeIdsArr_value in nodeIdsArr:
nodeidArr = nodeIdsArr_value.split('_')
if nodeidArr[0] not in nodeOutArr:
nodeOutArr[nodeidArr[0]] = []
nodeOutArr[nodeidArr[0]].append(str(nodeidArr[1]))
if int(nodeidArr[0]) in dateCateList:
dateCate = nodeidArr[0]
# self.write(nodeOutArr)
firstii = 0
nodeParams = {'dateCate': dateCate}
returnArr = {}
logStrs = logStrs + '#####start#####循环字方法分析数据##########<br>'
logStrs = logStrs + '********************************<br>'
for k in nodeOutArr:
# 查询每个节点的配置值
aconfigFind = self.db.query(analysis_list).filter(
analysis_list.id == k).first()
if not hasattr(aconfigFind, 'id'):
logStrs = logStrs + 'err--没有找到当前节点<br>'
self.updateIndate(alist_value.id, i) #更新下次执行时间
continue
logStrs = logStrs + '计算当前的节点名称:' + aconfigFind.title + '<br>'
if firstii == 0:
nodeParams['isfirst'] = 1
else:
nodeParams['isfirst'] = 0
firstii = 1
params = nodeOutArr[k] # 必须是list数据
# returnArr = getattr(AnalysisFunctions, 'a1')(self,'33')
try:
# 调用类执行里面方法，传入参数
a = 1
tmp = 'self.' + aconfigFind.functionName + "(returnArr, bid, indate,params, aconfigFind, nodeParams)"
returnArr, funcLogStrs = eval(tmp)
# returnArr, funcLogStrs=self.analysis_coupons_use_yetai(returnArr, bid, indate,params, aconfigFind, nodeParams)
logStrs = logStrs + funcLogStrs
logStrs = logStrs + aconfigFind.functionName + '方法返回来数据条数:' + str(
len(returnArr)) + '<br>'
# logStrs = logStrs + '子方法返回来数据条数:' + str(len(returnArr)) + '<br>'
a = 1
except Exception as e:
logStrs = logStrs + 'err--子方法执行错误<br>' + str(e)
returnArr = self.empty_pd()
self.AppLogging.warning(
'err--的函数执行失败:异常%s' % aconfigFind.functionName,
str(e))
self.AppLogging.warning(str(e))
if isTest:
raise e
logStrs = logStrs + '********************************<br>'
logStrs = logStrs + '#####end#####循环字方法分析数据##########<br>'
logStrs = logStrs + '所有子方法执行后返回来数据条数:' + str(
len(returnArr)) + '<br>'
# funcLogStrs=''
# logStrs = logStrs + funcLogStrs
# print('returnArr==',len(returnArr))
# return
if returnArr.empty:
logStrs = logStrs + '所有计算完事后返回数据是空'
logStrs = logStrs + '总计执行时间:' + str(time.time() -
funcStime) + '秒<br>'
self.AppLogging.info(logStrs)
self.updateIndate(alist_value.id, i) # 更新下次执行时间
continue
# 返回的数据要去计算我所要得到的数据
# 分片数据
userType1Arr = returnArr[returnArr['userType'] == 1] #注册用户数据
userType2Arr = returnArr[returnArr['userType'] == 2] #微信用户
logStrs = logStrs + '注册用户数据:' + str(len(userType1Arr)) + '<br>'
logStrs = logStrs + '微信用户数据:' + str(len(userType2Arr)) + '<br>'
# return
if not userType1Arr.empty: #注册用户数据
userinfo_weixin = base_mode('online_userinfo_weixin')
ulist_wx = self.db.query(
userinfo_weixin.userId, userinfo_weixin.openid).filter(
userinfo_weixin.bid == bid,
userinfo_weixin.openid != None).all()
ulist_wx_pd = pd.DataFrame(ulist_wx,
columns=['userId', 'openid'])
out1 = pd.merge(userType1Arr, ulist_wx_pd, on='userId')
del ulist_wx_pd
else:
out1 = self.empty_pd()
logStrs = logStrs + '注册用户数据其中有openid的数据有:' + str(
len(out1)) + '<br>'
if not userType2Arr.empty:
online_userinfo = base_mode('online_userinfo')
ulist = self.db.query(
online_userinfo.id, online_userinfo.userPhone,
online_userinfo.age).filter(
online_userinfo.bid == bid,
online_userinfo.userPhone != 0).all()
ulist_pd = pd.DataFrame(
ulist, columns=['uid', 'userPhone', 'age'])
out2 = pd.merge(userType2Arr, ulist_pd, on='uid')
del ulist
else:
out2 = self.empty_pd()
logStrs = logStrs + '微信数据其中有用户基础数据的有:' + str(
len(out2)) + '<br>'
out = pd.concat([out1, out2],
ignore_index=True) # 两个pandas数据拼接
logStrs = logStrs + '微信数据和用户基础数据拼接后:' + str(len(out)) + '<br>'
# print(out)
# return
# 销毁变量 预防内存过高
del out1, out2
gc.collect()
tmpall = []
if not out.empty:
#先删除已经存在的数据
aid = alist_value.id
online_analysis_list = base_mode_analysis(
'online_analysis_list_' + str(bid))
self.db_analysis.query(online_analysis_list).filter(
online_analysis_list.indate == indate,
online_analysis_list.aid == aid).delete()
self.db_analysis.commit()
for index, row in out.iterrows():
tmp = {}
tmp['intime'] = int(time.time())
tmp['indate'] = indate
tmp['aid'] = aid
tmp['userType'] = row['userType']
tmp['uid'] = row['uid']
tmp['userId'] = row['userId']
tmp['openId'] = row['openid']
tmp['userPhone'] = row['userPhone']
tmpall.append(tmp)
# print(tmpall)
# return
try:
self.db_analysis.execute(
online_analysis_list.__table__.insert(), tmpall)
self.db_analysis.commit()
except:
self.AppLogging.warning("分析器out写入数据库错误%s" % tmp)
logStrs = logStrs + '写入数据库有:' + str(len(tmpall)) + '<br>'
logStrs = logStrs + '总计执行时间:' + str(time.time() -
funcStime) + '秒<br>'
self.AppLogging.warning(logStrs)
self.updateIndate(alist_value.id, i)
self.AppLogging.info('执行完毕')
self.write('ok')