qc-dgp/dgp/get_order_daily.py

#!/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()