#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Mon May 18 13:13:00 2020
@author: chencong
## amend1 20200927 11:39:00
## amend2
"""
import requests
import hashlib
import time
import datetime
from urllib import parse
import math
import pymysql
from apscheduler.schedulers.blocking import BlockingScheduler
import account_list as al
def md5value(s):
md5 = hashlib.md5()
md5.update(s.encode("utf-8"))
return md5.hexdigest()
##《1》阅文
def get_yuewen_order(st,et):
url = 'https://open.yuewen.com/cpapi/wxRecharge/querychargelog'
version = 1
secert_list = al.yuewen_account_list
for secert in secert_list:
start_time = st
email= secert[0]
appsecert = secert[1]
for i in range(int((et-st)/86400)):
#time.sleep(61)
t =()
end_time = min(start_time+86400,et)
timestamp = int(time.time())
s=''
page = 1
order_status = 2
data = {
'email':email,
'version':version,
'timestamp':timestamp,
'start_time':start_time,
'end_time':end_time,
'page':page,
'order_status':order_status
#'last_min_id':last_min_id,
#'last_max_id':last_max_id,
#'total_count':total_count,
#'last_page':last_page
}
sorted_data = sorted(data.items())
for k,v in sorted_data:
s = s+str(k)+str(v)
sign = md5value(appsecert+s).upper()
data1 = {
'email':email,
'version':version,
'timestamp':timestamp,
'start_time':start_time,
'end_time':end_time,
'page':page,
'order_status':order_status,
'sign':sign
}
list1 = requests.get(url=url,params=data1)
total_count = list1.json()['data']['total_count']
last_min_id = list1.json()['data']['min_id']
last_max_id = list1.json()['data']['max_id']
last_page = list1.json()['data']['page']
if total_count>0:
for x in list1.json()['data']['list']:
y={}
dtime = datetime.datetime.strptime(x['order_time'],"%Y-%m-%d %H:%M:%S")
y['date']= ((int(time.mktime(dtime.timetuple()))+8*3600)//86400)*86400-8*3600
y['platform'] = '阅文'
y['channel'] = x['app_name']
y['from_novel'] = x['book_name']
y['user_id'] = x['openid']
y['stage'] = ''
y['channel_id']= 0
y['order_time']=x['order_time']
y['amount']=x['amount']
y['reg_time']=x['reg_time']
y['order_id']=x['order_id']
"""
del x['app_name']
del x['order_status']
del x['order_type']
del x['openid']
del x['user_name']
del x['sex']
del x['channel_name']
del x['book_id']
del x['book_name']
del x['report_status']
"""
y = sorted(y.items(), key=lambda item:item[0])
y = dict(y)
y = tuple(y.values())
t = t+((y),)
if total_count>100:
for page in range(2,math.ceil(total_count/100)+1):
data = {
'email':email,
'version':version,
'timestamp':timestamp,
'start_time':start_time,
'end_time':end_time,
'page':page,
'last_min_id':last_min_id,
'last_max_id':last_max_id,
'total_count':total_count,
'last_page':last_page,
'order_status':order_status
}
sorted_data = sorted(data.items())
s1 = ''
for k,v in sorted_data:
s1 = s1 + str(k)+str(v)
sign = md5value(appsecert+s1).upper()
data2 = {
'email':email,
'version':version,
'timestamp':timestamp,
'start_time':start_time,
'end_time':end_time,
'page':page,
'last_min_id':last_min_id,
'last_max_id':last_max_id,
'total_count':total_count,
'last_page':last_page,
'order_status':order_status,
'sign':sign
}
list2 = requests.get(url=url,params=data2)
for x in list2.json()['data']['list']:
y={}
dtime = datetime.datetime.strptime(x['order_time'],"%Y-%m-%d %H:%M:%S")
y['date']= ((int(time.mktime(dtime.timetuple()))+8*3600)//86400)*86400-8*3600
y['platform'] = '阅文'
y['channel'] = x['app_name']
y['from_novel'] = x['book_name']
y['user_id'] = x['openid']
y['stage'] = ''
y['channel_id']= 0
y['order_time']=x['order_time']
y['amount']=x['amount']
y['reg_time']=x['reg_time']
y['order_id']=x['order_id']
"""
del x['app_name']
del x['order_status']
del x['order_type']
del x['openid']
del x['user_name']
del x['sex']
del x['channel_name']
del x['book_id']
del x['book_name']
del x['report_status']
"""
y= sorted(y.items(), key=lambda item:item[0])
y = dict(y)
y = tuple(y.values())
t = t+((y),)
total_count = list2.json()['data']['total_count']
last_min_id = list2.json()['data']['min_id']
last_max_id = list2.json()['data']['max_id']
last_page = list2.json()['data']['page']
print(email,start_time,len(t))
start_time=start_time+86400
if len(t)>0:
mysql_insert_order(t)
##《2》掌读
def get_zhangdu_order(st,et):
secert_list = al.zhangdu_account_list
url = 'https://api.zhangdu520.com/channel/getorder'
for item in secert_list: #分渠道
t=()
uid = item[0]
appsecert = item[1]
channel = item[2]
timestamp = int(time.time())
sign = md5value(str(uid)+'&'+appsecert+'&'+str(timestamp))
page =1
starttime = st
timespace = 90*3600*24
endtime = min(et,st+timespace)
for x in range((et-st)//timespace+1): #分时段
Params = {
'uid':uid,
'timestamp':timestamp,
'sign':sign,
'starttime':starttime,
'endtime':endtime
}
list1=requests.get(url=url,params=Params)
pageCount= list1.json()['data']['pageCount']
if pageCount>0 :
for a in range(1,pageCount+1): #分页
page = a
Params = {
'uid':uid,
'timestamp':timestamp,
'sign':sign,
'starttime':starttime,
'endtime':endtime,
'page':page
}
list2=requests.get(url=url,params=Params).json()
if 'data' in list2.keys():
for b in list2['data']['list']:
c={}
c['amount']=b['amount']
c['channel_id']=uid
c['order_id']=str(b['orderno'])
c['order_time']=b['ctime']
c['user_id']=b['openid']
c['platform'] = '掌读'
c['channel'] = channel
c['reg_time']=b['regtime']
c['from_novel']=''
c['stage']=''
c['date']= ((int(b['ctime'])+8*3600)//86400)*86400-8*3600
"""
del b['openid']
del b['regtime']
del b['ip']
del b['ua']
del b['id']
del b['ctime']
del b['userid']
del b['orderno']
del b['source']
del b['sourceid']
"""
if b['status']=='1':
#del b['status']
del b
x= sorted(c.items(), key=lambda item:item[0])
x = dict(x)
x = tuple(x.values())
t = t+((x),)
else:
print(list2)
starttime =starttime+timespace
endtime = min(et,starttime+timespace)
if len(t)>0:
mysql_insert_order(t)
#print('掌读',channel,len(t))
##《3》花生
def get_huasheng_order(st,et):
apikey_list = al.huasheng_account_list
url = 'https://vip.rlcps.cn/api/getMerchants'
for key in apikey_list: #获取每个vip账号下的channel_id
apiKEY=key[0]
apiSecurity=key[1]
stage = key[2]
timestamp = str(int(time.time()))
sign = md5value(apiKEY+timestamp+apiSecurity).upper()
data = {
'apiKey':apiKEY,
'apiSecurity':apiSecurity,
'timestamp':timestamp,
'sign':sign
}
list0 = requests.post(url,data).json()
t = ()
for merchant in list0['data']:
merchant_id = merchant['merchant_id']
merchant_name = merchant['merchant_name']
url1 = 'https://vip.rlcps.cn/api/orderList'
start_time = st
for i in range((et-st)//86400):
page = 1
date = time.strftime("%Y-%m-%d",time.localtime(start_time))
sign = md5value(apiKEY+date+str(merchant_id)+timestamp+apiSecurity).upper()
data1 = {
'apiKey':apiKEY,
'apiSecurity':apiSecurity,
'timestamp':timestamp,
'date':date,
'merchant_id':merchant_id,
'sign':sign,
'page':page
}
list1 = requests.post(url1,data1).json()
if 'data' in list1.keys() and len(list1['data'])>0 :
for i in range(int(math.ceil(list1['count']/500))):
data2= {
'apiKey':apiKEY,
'apiSecurity':apiSecurity,
'timestamp':timestamp,
'date':date,
'merchant_id':merchant_id,
'sign':sign,
'page':page
}
list2 = requests.post(url1,data2).json()
for x in list2['data']:
if x['order_status']==1:
y={}
##dtime = datetime.datetime.strptime(x['pay_at'],"%Y-%m-%d")
##y['date']= ((int(time.mktime(dtime.timetuple()))+8*3600)//86400)*86400-8*3600
y['user_id'] = x['openid']
y['order_id']=x['trans_id']
y['order_time'] = x['pay_at']
y['reg_time'] = x['join_at']
y['date'] = (start_time+8*3600)//86400*86400-8*3600
y['channel'] = merchant_name
y['channel_id'] = merchant_id
y['platform'] = '花生'
y['stage'] = stage
y['from_novel'] = x['book_name']
y['amount']=x['amount']
"""
del x['order_num']
del x['book_name']
del x['trans_id']
del x['pay_at']
del x['join_at']
del x['subscribe_at']
del x['openid']
del x['charge_count']
del x['book_id']
del x['order_status']
del x['user_name']
del x['spread_name']
del x['request_at']
"""
y= sorted(y.items(), key=lambda item:item[0])
y = dict(y)
y = tuple(y.values())
t = t+((y),)
page=page+1
else:
print(list1)
start_time = start_time+86400
if len(t)>0:
mysql_insert_order(t)
print("huasheng",stage,merchant_name,len(t))
##《4》掌中云
def get_zzy_order(st,et):
API_list = al.zzy_account_list
url = 'https://openapi.818tu.com/partners/channel/channels/list?'
for x in API_list:
my_key = x[0]
secert = x[1]
stage = x[2]
my_sign=md5value(secert+'key='+my_key)
parameter = 'key='+my_key+'&sign='+my_sign
channel_list = requests.get(url+parameter) #获取子渠道列表
if 'data' in channel_list.json().keys():
items = channel_list.json()['data']['items']
elif len(x)>3:
#print(channel_list.json())
my_key=x[3]
secert=x[4]
my_sign=md5value(secert+'key='+my_key)
parameter = 'key='+my_key+'&sign='+my_sign
#url = 'https://openapi.818tu.com/partners/channel/channels/list?'
channel_list = requests.get(url+parameter)
if 'data' in channel_list.json().keys():
items = channel_list.json()['data']['items']
else:
print(channel_list.json())
items = []
else:
print(channel_list.json())
items = []
for item in items : #获取channel_id 后逐个拉取历史orders
r=()
channel_id = item['id']
channel = item['nickname']
status = str(1)
per_page = str(1000)
limit_time = et
get_time = st
lt = parse.urlencode({'created_at[lt]':limit_time})
gt = parse.urlencode({'created_at[gt]':get_time})
url_1 = 'https://openapi.818tu.com/partners/channel/orders/list?'
my_sign_1 = md5value(secert+'channel_id='+str(channel_id)+'&created_at[gt]='+get_time+'&created_at[lt]='+limit_time+'&key='+my_key+'&per_page='+per_page+'&status='+status)
parameter_1 = 'channel_id='+str(channel_id)+'&'+gt+'&'+lt+'&per_page='+per_page+'&status='+status+'&key='+my_key+'&sign='+my_sign_1
orders = requests.get(url_1+parameter_1)
t = int(orders.json()['data']['count'])
if t > 0:
t = orders.json()['data']['count']//int(per_page)+1
for page in range(1,t+1):
my_sign_2 = md5value(secert+'channel_id='+str(channel_id)+'&created_at[gt]='+get_time+'&created_at[lt]='+limit_time+'&key='+my_key+'&page='+str(page)+'&per_page='+per_page+'&status='+status)
parameter_2 ='channel_id='+str(channel_id)+'&'+gt+'&'+lt+'&page='+str(page)+'&per_page='+per_page+'&status='+status+'&key='+my_key+'&sign='+my_sign_2
orders_1 = requests.get(url_1+parameter_2)
b = orders_1.json()['data']['items']
for a in b:
c={}
c['user_id']=str(a['member']['openid'])
c['channel'] = channel
c['reg_time']=a['member']['created_at']
c['channel_id']=channel_id
c['amount']= round(a['price']/100,2)
c['order_id']=str(a['id'])
c['order_time']=a['created_at']
c['platform']='掌中云'
c['stage']=stage
#c['amount']=a['amount']
dtime = datetime.datetime.strptime(a['created_at'][0:10],"%Y-%m-%d")
c['date']= ((int(time.mktime(dtime.timetuple()))+8*3600)//86400)*86400-8*3600
if str(a['from_novel_id'])!='None':
c['from_novel']=a['from_novel']['title']
else :
c['from_novel']='None'
"""
del a['member']
del a['referral_link_id']
del a['id']
del a['created_at']
del a['paid_at']
del a['border_id']
del a['from_novel_id']
del a['status']
del a['price']
del a['agent_uid']
"""
x= sorted(c.items(), key=lambda item:item[0])
x = dict(x)
x = tuple(x.values())
r = r+((x),)
if len(r)>0:
mysql_insert_order(r)
print('zzy',channel,len(r))
##《5》 悠书阁
def get_ysg_order(st,et):
key_list =al.ysg_account_list
url='https://novel.youshuge.com/v2/open/orders'
o = ()
for key in key_list:
host_name=key[0]
channel_id = key[1]
secert_key = key[2]
channel = key[3]
stage = key[4]
timestamp = int(time.time())
start_date = time.strftime("%Y-%m-%d",time.localtime(st))
end_date = time.strftime("%Y-%m-%d",time.localtime(et))
page = 1
str1 = 'channel_id='+str(channel_id)+'&end_date='+end_date+'&host_name='+host_name+'&page='+str(page)+'&pay_status=1'+'&start_date='+start_date+'&time='+str(timestamp)+'&key='+secert_key
sign = md5value(str1).upper()
data = {
'sign':sign,
'host_name':host_name,
'time':timestamp,
'channel_id':channel_id,
'page':page,
'pay_status':1,
'start_date':start_date,
'end_date':end_date
}
r =requests.post(url,data).json()
if 'data' in r.keys():
if len(r['data'])>0:
for i in range((r['data'][0]['count']-1)//100+1):
timestamp = int(time.time())
str1 = 'channel_id='+str(channel_id)+'&end_date='+end_date+'&host_name='+host_name+'&page='+str(page)+'&pay_status=1'+'&start_date='+start_date+'&time='+str(timestamp)+'&key='+secert_key
sign = md5value(str1).upper()
data2= {
'sign':sign,
'host_name':host_name,
'time':timestamp,
'channel_id':channel_id,
'page':page,
'pay_status':1,
'start_date':start_date,
'end_date':end_date
}
r2 = requests.post(url,data2).json()
if 'data' in r2.keys():
if len(r2['data'])>0:
for x in r2['data']:
y={}
dtime = datetime.datetime.strptime(x['create_time'][0:10],"%Y-%m-%d")
y['date']= ((int(time.mktime(dtime.timetuple()))+8*3600)//86400)*86400-8*3600
y['order_id']=x['order_num']
y['amount']=round(int(x['price'])/100,2)
y['order_time']=x['create_time']
y['channel']=channel
y['from_novel']=x['book_name']
y['stage']=stage
y['user_id']=x['openid']
y['channel_id']=channel_id
y['platform']='悠书阁'
y['reg_time']=x['reg_time']
y= sorted(y.items(), key=lambda item:item[0])
y = dict(y)
y = tuple(y.values())
o = o+((y),)
page=page+1
if len(o)>0:
mysql_insert_order(o)
## 数据导入表采用replace替换主键orderid的方法
def mysql_insert_order(data):
db = pymysql.connect('rm-bp1c9cj79872tx3aaro.mysql.rds.aliyuncs.com','superc','Cc719199895','quchen_text')
cursor = db.cursor()
#sql = 'insert ignore into quchen_text.order_daily (amount,channel,channel_id,date,from_novel,order_id,order_time,platform,reg_time,stage,user_id) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'
#sql = "update quchen_text.order set amount =%s where platform='掌中云' and order_id =%s"
sql1 = 'replace into quchen_text.order_daily (amount,channel,channel_id,date,from_novel,order_id,order_time,platform,reg_time,stage,user_id) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'
sql2 = 'replace into quchen_text.order(amount,channel,channel_id,date,from_novel,order_id,order_time,platform,reg_time,stage,user_id) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'
try:
cursor.executemany(sql1,data)
db.commit()
print('access insert order_daily',len(data))
except:
db.rollback()
print('defeat order_daily')
try:
cursor.executemany(sql2,data)
db.commit()
print('access insert order',len(data))
except:
db.rollback()
print('defeat order')
def start_all_job():
request_time_stamp = time.time()
st_unix = int((request_time_stamp+8*3600)//86400*86400-8*3600-86400)
et_unix = int((request_time_stamp+8*3600)//86400*86400-8*3600)
st_dt = time.strftime("%Y-%m-%dT%H:%M:%S", time.localtime(st_unix)) + '+08:00'
et_dt = time.strftime("%Y-%m-%dT%H:%M:%S", time.localtime(et_unix)) + '+08:00'
get_yuewen_order(st_unix,et_unix)
get_ysg_order(st_unix,et_unix)
get_zhangdu_order(st_unix,et_unix)
get_zzy_order(st_dt,et_dt)
get_huasheng_order(st_unix,et_unix)
start_job_time = '2020-09-30 00:20:00'
if __name__ == '__main__':
scheduler = BlockingScheduler()
scheduler.add_job(start_all_job, 'interval',days=1,start_date=start_job_time)
scheduler.start()