#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ Created on Mon May 18 13:13:00 2020 @author: chencong """ import requests import hashlib import pandas as pd import time from urllib import parse import datetime import csv 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() 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)): 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) def get_zzy_order(st,et): API_list = al.zzy_account_list for x in API_list: my_key = x[0] secert = x[1] stage = x[2] my_sign=md5value(secert+'key='+my_key) url = 'https://openapi.818tu.com/partners/channel/channels/list?' parameter = 'key='+my_key+'&sign='+my_sign channel_list = requests.get(url+parameter) #获取子渠道列表 items = channel_list.json()['data']['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 = 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)) 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'] 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)) 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 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(stage,merchant_name,len(t)) 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) 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" try: cursor.executemany(sql,data) db.commit() print('access insert order',len(data)) except: db.rollback() print('defeat') def start_all_job(): st_unix = int((time.time()+8*3600)//86400*86400-8*3600-86400) et_unix = int((time.time()+8*3600)//86400*86400-8*3600) #et_unix = et_unix - 1 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-1) 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-23 01:05:00' if __name__ == '__main__': scheduler = BlockingScheduler() scheduler.add_job(start_all_job, 'interval',days=1,start_date=start_job_time) scheduler.start()