qc-dgp/dgp/get_order_history.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

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 = [['mqud82950@163.com','74ca754515fa253c8ab790603cebc2ee'],['guangzhouliuqi2@sina.com','10ce1dd6ccb330a82b73701d1e78f518']]
    
    for secert in secert_list:
        start_time = st
        email= secert[0]
        appsecert = secert[1]
        for i in range(int((et-st)/86400+1)):
            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)
            #print(list1.json())
            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 = [

['vip-songyi','098f17cc73cc4a74861ff4a87e3b526c',''],
['vip-wanger','aefabe89dfb84227bf7dae8ef0a59c89',''],
['vip-kuangwu','de5157d0e778400d84b2730626bd5787',''],
['vip-guangzhou500','ecce5c8566c24d63b8c829a10deb8601',''],
['vip-zhousi','b226af5f3cb243638b4a29d1a343d10d',''],
['vip-diwu1','7f2a1f03e53742c4acc49814b536ff59',''],
['vip-toutiao100','10ea6f1132084d86940c9b8b56fc5dad',''],
['vip-guangzhou3000','bac6f3c5d58244559b244c52bd1696b2',''],
['vip-guangzhou600','71873a3750bf49cdabb7a10441742950',''],
['vip-guangzhou3','385c7fadd05649e689a6bf272f3d59a7',''],
['vip-guangzhou5004','b8737034f23b4e5b9fa6256166faea57',''],
['vip-guangzhou5','5c65fd4b632a401097b6ca524eab551f',''],
['vip-guangzhou6','0939540ca8f34401b20b36e6c5409031',''],
['vip-guangzhoujiuqi','a7ede742affe457e8da4da181eb643d3',''],
['vip-guangzhou5003','9fefdc098ae54fe283dd2c5a6019b769',''],
['vip-qucheng2','e8cee00b1c3d42a6a75810a0d90e9e6a',''],
['vip-quchengsanqi','afc1e30e72ad41bf90c21aac41cb6ee1',''],
['vip-qucheng7qi','f8e7b801e63e43218111c1d8a5faf857',''],
['vip-qucheng8qi','17838a8f9ea64f809a88a5cd872c7375',''],
['vip-quchengjiuqi','80034ad8e7874ab99242ccce30ac1861',''],
['vip-ccsq','d8100098bf0d4fdb85e62150bb15a0b9',''],
['vip-qucheng11qi','e274b5bb5245444f806bc36b688f7a44',''],
['vip-qucheng12qi','b9de5355acbd414cb12f97786efe0376',''],
['vip-qucheng13qi','c8c91b7fdb8e476d81160c865f9bbf7b',''],
['vip-qucheng14qi','c9c27f53e40b4372a2bfe83dbd8fde03',''],
['vip-qucheng15qi','81909093d1db4374b5c545031380f3fe',''],
['vip-zanxiang10','04a2c53fbe3546dd8feb475592f9855c',''],
['ch-zzsw','d1a8e23df03a411a8353896e00751b23',''],
['ch-zhixiang','d0fc32aefdfb46ad959d6a09943b0d9e',''],
['ch-wenlan','520514c9c0cf46e19f0707ec28113a8b',''],
['ch-qxsww','b31211c625244e1a8123adf866a74935',''],
['ch-xixsc','25fc3c26d388410dbdb6ea5ce792fc1e',''],
['ch-zzsw5','fd5b6b28997f4880b398d0188f0fe68a',''],
['ch-yysf66','b194a98901ad4508aa0a984deedc35a5',''],
['ch-chyd','370c8494fd0c480f8c0b583d5eb3bd54',''],
['ch-xiangx5','4a196a83056d43ed82db2a9f941cd6cb',''],
['ch-shwg','d406794893de4175ab948fd028e43468',''],
['vip-diliu','0858b1566e0c4debb4cab808a0e207f7',''],
['ch-haotian2','7dd4a99a49b340a2a02822d9a83f64a8',''],
['ch-fysh6','645a6357d3ea413098f689dbf3d70c68',''],
['vip-qucheng16qi','bca1090368d44ec8a498d26e02ba06fe',''],
['vip-qucheng17qi','fd31bc211b4f42908163f68fdeabf869',''],
['vip-刘勇8','e35df1ac30b844eb81bd620674dc6937','刘勇6月'],
['110456b2078','8621335fe2f61b7afa5d8697ba7669b1','趣程21期'],
['110512df033','4768493cc3c7517f1b3fcd8bfeeb80ed','趣程22期'],
['110870010d2','fb368fe82522503d06880d19c938e10c','刘勇7月','1108701f1d6','0f9c0f8429d1a16a8a78c2306e7a4db3'],
['1109652c8b6','be5b9d099ccec53524e305b4bfd96edc','趣程23期'],
['11092522a7f','bbd92f3898a2518d3456246b8b364de5','阳光1期'],
['1109295d56c','9bb955186597882ac473e86ba4576158','趣程20期'],
['1119168245a','67d4941065fb2ce4e601fd156220c579','趣程19期'],
#['106672e2105','0c3f3ab6d9a82fb515fe3dd906d4d96b','刘勇6月']
['112645ad567','67861e28b58a809ffc5af81a17e7de1f','广州10期'],
['113100128c7','adaa2b8fb53274453faaa0a70233562e','趣程25期'],
['1132659da72','de2fd503c8f0e11dc35e54d5cd1db7b5','刘勇8月']
    


]
    
    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) #获取子渠道列表
        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)
            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 = [
[10015,'0125f434-7999-a4bb-77ab-28fb0a6a68ec','丹墨悦读'],
[7406,'834a31ad-5533-d0d0-cb48-6f81f83919b0','九五月读'],
[10497,'2e4a0bd0-e222-286b-73dc-1e6a6c8a6f28','书剑文阁'],
[10507,'59babb99-9434-b1b1-7159-17afcb825cba','争锋文豪'],
[9868,'320b477e-59c6-cb9f-9fb1-b0a304c3d82b','侠客书楼'],
[10020,'9a914f89-f640-2ae8-34a7-c24b1f397d77','修焰文阅'],
[9873,'14978692-cdae-9b8e-84cf-21d3202dd521','傲龙书社'],
[10069,'ee9d7deb-8c3f-6862-31dc-779bec1cdc46','兰庭文鼎'],
[9668,'88f5d175-de02-24ed-575c-0fcf6c847e02','冥影文库'],
[10063,'016e69af-ce65-e5c0-34dc-47222cfb996a','冥影文海'],
[10119,'a2d7196f-164b-8b58-cb6d-6d49d91fb106','冥真文轩'],
[9754,'da893644-f673-42c7-65fd-f6a7ac30347a','冷锋看吧'],
[7863,'ad3b06c6-c0b4-8f5f-8270-f493864f5a83','凤情书阁'],
[10769,'ae1cb5cf-716c-f880-dcf2-ab041c2f849a','剑旭文海'],
[7405,'862a9c69-801e-23ce-5fbd-bfcb0c1de5b0','南三月读'],
[10472,'00d765f5-dd77-2b37-f0b0-bd5ac8d24a3e','南菲悦读'],
[7872,'0e0915c5-ca02-3d62-b0ca-a80f41d722db','名媛书屋'],
[10729,'08d04293-c004-7116-d260-53a340471306','坌腾文海'],
[7865,'9ae4df81-a9c1-e7d3-50e5-62e40332dbef','夜情书房'],
[9684,'db078cb4-fb45-7830-af84-736ef24912b6','大河中文网'],
[9874,'4248df4a-d573-cf02-97cb-2d185d3cfb69','天降读物'],
[10040,'116ef9c2-62e8-83ae-1c41-0f14d898dad8','奇汇看吧'],
[9860,'bd90fa50-51f7-fce1-1523-591a041a8719','如阅墨香'],   
[7873,'456af16a-fc75-420c-a6ce-234cba07eb92','媚咪文馆'],
[10543,'8304c660-a0a1-acfd-7ba7-3531383c9209','宸赫书楼'],
[9872,'80cec896-ef46-6f77-a80e-6737954dfbaa','寻界文坊'],
[9870,'f2d73b83-60e3-215c-f283-4e48a64e19e0','尊圣中文网'],
[10545,'def2f148-82c2-b42c-634c-f2b557a529ce','尚凌文轩'],
[9865,'41e1a151-397d-a08e-f582-f0394a59c748','巨龍悦读'],
[10730,'f337c426-58fc-3865-4794-d5cb2c9ceec2','彭湃阅读'],
[9685,'cb03d959-9311-f0d3-a6fb-188d79d92412','忠武文学城'],
[10234,'b5e9eaea-483f-f246-5816-23ffa9309c05','悦苒看吧'],
[9995,'d654d3ec-ec34-e243-6cab-5ba030fb1c8a','惊鸿文海'],
[9633,'e943f5cf-8726-e7a8-6b76-d0a2d8f0a67c','慕云文库'],
[10459,'781e425b-b14a-7d39-88fa-588d11d18b93','承泽文轩'],
[10009,'a72aefcf-2fde-3187-90aa-09a30273cdc6','攀登文库'],
[10003,'3c0bb2fa-e835-6f96-ffd2-185fd9dfd2af','新阅文轩'],
[9663,'e0b49bb4-0365-ec52-e8d2-a71bf9eeb2a2','无涯文海'],
[7864,'4c3ce52f-9e48-9f5a-c64f-1634dcb37d1d','日月书房'],
[10030,'8674d904-c496-bacb-35ab-7a23a21b4725','旷世文海'],
[10629,'a57edda1-766c-ae25-fb95-d57aaf144e7c','星烛书海'],
[9847,'0ffd2d6f-aa77-67a0-fda1-510eab3ce006','昱墨文海'],
[10011,'88ad507a-050a-8271-4ba4-adfd381cdaff','晨曦墨轩'],
[9846,'be33e937-c791-48fb-8137-58f0d38a70b4','月落文阁'],
[9689,'b6a4daa1-e9ce-0dd6-5714-21e089f327ab','林溪文苑'],
[10008,'06301041-96ca-b3a1-9892-e02526a3f53e','枫林文选'],
[10013,'1bfa8861-c20d-f994-b649-03bdcf40a403','桔橙文轩'],
[10441,'fb13c84a-01b1-1a84-4c84-ffbc38241477','洛书文海'],
[10235,'239c2e93-1c3c-05a9-ba69-4fb8c2d1553e','渲染悦轩'],
[10628,'78348bef-cef1-969a-a0cf-990dab656bb5','炫轩文库'],
[7890,'a1417aa5-2041-9b56-d419-4606f035d12d','烛影书屋'],
[10669,'1b3a227c-c811-d1bf-9564-6cd5c22a1fb5','猛虎阅吧'],
[10461,'755ef603-e734-bddf-e5b8-700e941c8c18','瑞祥文轩'],
[9686,'d5964a79-2b2a-5e41-f54a-5d0304cd088b','知隐书香'],
[9998,'a8d73912-364b-57aa-76e6-b38a2bb9fb7b','碧炎文海'],
[10041,'4cd2ede8-8b02-2ef1-c29f-081ac5b926e0','祁苑文阅'],
[10442,'62d5296b-9370-ecbf-e1b2-8e15158e3ed5','神龍悦读'],
[10544,'a22e664b-0fa2-f08a-c60a-1b97d180d4a8','禹诺文阁'],
[9915,'f8fe300b-eea6-9443-5a83-1b7d75ae88ad','秀丽文轩'],
[9911,'7de672c4-e526-71a2-aa08-fd6012929a43','秋霞书社'],
[10471,'b92c8ea0-f40c-b959-9c54-bdd42948fec4','空亭文苑'],
[7887,'7271c2b9-5092-aa65-fa86-786cf3ecd528','竹枝书院'],
[10460,'644d48ea-8ee3-3271-43e4-aa1e4d1eb86e','繁星书海'],
[10499,'d5c5c552-34cd-55c3-e41f-44fe443cbe56','红炎文轩'],
[9882,'3b4f7cca-ffea-086a-9444-5dfcd6c79794','绘色阅读'],
[9876,'68e66280-c3d7-e84a-8fe1-94b1f526f5b6','若汐文海'],
[10771,'982bde3b-5eb5-431e-a3f6-bac99735b759','莫宇文库'],
[10067,'9bcd9dae-7718-91da-ea16-ea224e22164c','莹悦看吧'],
[10473,'7864d9a8-df4b-d105-4f2e-e30179c2974a','蓝翼文海'],
[10024,'1b5c7edc-038e-54ad-dad5-f30216dd8c0a','虎啸文选'],
[10095,'ced46ef2-5759-6169-7e40-5624215f1f91','蛟龙书楼'],
[9883,'0d525b30-997b-c727-9f03-1dea301f7ec0','西洲书舍'],
[10668,'9839e7a4-5fc2-c05b-faaf-0ed5f2cdce81','豪天文轩'],
[9866,'04cad8fd-c599-12e8-4d66-80cab639b050','豪金书社'],
[7889,'bf0fd628-c244-52c5-ae94-d5fa0ec91f4d','踏莎小说阅读'],
[10044,'6e87b452-dcb3-9c10-de65-c5919fa3d73d','追光悦读'],
[10200,'81c06b81-1da5-53c4-3b4e-5f63a83f1579','锦墨书楼'],
[9916,'58e354ee-5f24-f02f-ee90-5f6f0e5d97c8','锦溪书楼'],
[9688,'e0a5f28c-6009-560a-6dc6-975e5e53cc74','阅欣文学'],
[9910,'5e3f1ef2-b497-8560-810e-ee92882f0726','阅胜文选'],
[10498,'89266631-e8fa-ea77-075e-d38d37a654c1','阳俊文海'],
[10770,'e389c082-d1ca-cb2a-6d0b-915db869363a','雁风文轩'],
[9900,'874d0ec8-1e85-f061-e095-d9997597bd8e','雅阁文社'],
[7866,'dfb7dcdc-a6b1-e03e-2f57-9bfcb80a32c4','雨尘书阁'],
[10474,'611bdc19-946c-945d-bcf8-2a64ca2919c0','雪松文库'],
[9875,'b1fea7ac-4e0b-4546-e41a-9181af98515d','霄雄文学'],
[10419,'8e4c73f2-50e4-dd33-3e06-63c3d8f38c04','霜华文库'],
[9938,'fc61e6fd-bf13-44ea-73e9-609971b83456','青云文轩'],
[10007,'8bc52ea8-64ec-32cf-3ef6-463abe469eed','青峰悦读'],
[8939,'b016dcab-c002-ae71-f8ad-d5ffd35d3bbc','青葱看吧'],
[10014,'4d5441a4-da30-15e4-0186-aa2aea21b286','青霞文苑'],
[10418,'e3a32b05-ac1c-f518-af33-3c0d3883307b','飞鸿文海'],
[9971,'5c625d14-8452-847e-09bd-33d30126ed31','魅力书楼'],
[9922,'d1e77bc9-3875-ad44-2253-4201f70e0bb4','鹿鸣文库'],
[10012,'90e4a5fc-4625-9710-6ab1-561ce0de0270','麒麟墨选'],
[10199,'16abc367-31e0-f0aa-4cd4-9cc64e891d8a','鼎月文苑'],
[10475,'f50879de-036f-7ec7-39dc-44ecda037dea','鼎盛文海'],
[10901,'8135ee68-4255-8da3-9b35-cef2b942f6a2','孤风书楼'],
[10899,'a8425c20-ee9a-b6ad-b8da-e6b74621be5c','冷雁文海'],
[10896,'b24bca0d-3d31-0127-04e0-b2254ebb27e9','南烟书楼'],
[10840,'e69ab01b-3725-4e70-670b-408b82860fe3','鹤鸣看吧'],
[10841,'dc577efd-aa90-4dd6-f8ab-c42b38f84faa','深洋书楼'],
[10842,'4cc12af5-2c86-2217-e5e1-ce1b28fa4758','航远文阁'],
[10843,'ca5920b0-59cb-a55e-9fd9-e5095a29c04f','神武文轩'],
[10845,'cfc93bd7-723e-0341-c6ef-c25253a8caa6','茗垣文轩'],
[10995,'2e537d2a-6b5c-a862-2cf0-30aa46bcad6e','耀杰书海'],
[10997,'d458def2-35c0-c577-d5a3-259225b9b76b','锦凌文选'],
[10996,'b1bde6ca-c518-ea3c-cf5f-5d30286b34b4','清涛文海'],
[10998,'b2b056ee-26a6-5868-874b-ab36f372900c','墨景文轩'],
[10999,'630fda00-13ff-f625-05aa-8d1862d385da','炫熙书馆'],
[11021,'90a1e372-8ce8-cd8f-e8c5-e9f603f0f9ba','狂涛文轩'],
[11020,'73445fa0-6145-149f-58eb-772eb7c78481','展宸书楼'],
[11011,'6fd7df66-8dc0-8939-759e-8e34d759e34a','阳曜文海'],
[11009,'f1d96f4a-2a3c-b24b-1f36-2398cebc19ad','高韵书楼'],
[11008,'d0a3a898-9e87-6adf-00ed-86b68040ecb0','成弘书楼'],
[10963,'3cf33177-fd73-68a5-6095-0d8b36083a9c','旋锋文轩'],
[10962,'c3f38863-6a7e-33aa-a44e-04b901ee1f04','赤虹文海'],
[10961,'11306f81-cfea-ab8f-f0a0-2da8f0acdea5','飘霜书海'],
[10942,'1235a146-7a1d-3a68-9f79-ede48cb8442c','月瑶看书'],
[10941,'3fd1d09f-c66c-bee7-021d-41510a673339','婉婉阅读'],
[10908,'2994b1bb-b51b-acd3-cad5-0055cf28104c','君浩文阅'],
[10906,'a419c6fd-9e10-c9d9-ed9a-02814014fdc3','星汉文海'],
[10879,'617a71dc-9ae0-3d5e-d373-ee833edc25df','华晖文轩'],
[10878,'d76b661f-75d5-60e7-f596-26d61054348e','承载书楼'],
[10877,'fe3c50c4-5fe7-36a1-6dc5-40ca27b3f671','鸿振书馆'],
[11076,'9a9bfeaf-2abc-495b-83f5-5f00d36cc54b','正德书楼'],
[11075,'1ab64fb6-aab3-f13a-d2f9-e079fb17d66e','泽宇文海'],
[11074,'c45b1a56-44e3-384e-25cf-63b987d6a297','吉本书海'],
[11073,'e0bf65b9-f592-1a1a-aac0-fbf710e40847','高岑文轩'],
[11044,'0cdea0ed-7a09-36f0-750e-61b75652bc87','烈风文海'],
[11043,'b605a351-81b8-910d-b0a8-66c9dc59374d','霸皇书楼'],
[11042,'35902525-03d5-e99b-ea69-d5ed1c716da1','辰宇书馆'],
[11039,'5abdd0ec-408e-ded3-d725-ac08e93eae90','吹雪文库'],
[11038,'d364471e-963b-e8a7-e89b-06568c831500','飞羽书楼'],
[10900,'e9136409-133f-37e1-7585-3f4f1675d20f','琦玉文库'],
[10998,'b2b056ee-26a6-5868-874b-ab36f372900c','墨景文轩'],
[10996,'b1bde6ca-c518-ea3c-cf5f-5d30286b34b4','清涛文海']
]
    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 = [
                   ['259900','72b49bebb7fc8df83feac8c4ed5c541b','趣程16期'],
                   ['269900','65e6d003b58ee73b5406e6cc472ebf3e','趣程14期'],
                   ['249900','f4f1475f604c4185a3e031e961dee637','趣程17期'],
                   ['4040635','c8bf5346e35f51c7ce228cff4d4a3678','趣程19期'],
                   ['2828976','20c0fb1d20964c2c42a725c72cac8863','刘勇6月'],
                   ['4747764','f350d7edd89bb7f6170b970f071ecb36','趣程20期'],
                   ['3737140','e786e59b87ebf239fdb43bf267ef4ee2','趣程13期'],
                   ['4848597','fab68fe238a8df12073295373f9e0ff7','趣程22期'],
                   ['5353590','50fefc21c7dd90cf9b584fe571f1bb64','趣程21期'],
                   ['4545355','968ba35e79d8219c47ca356d19192c6f','趣程9期'],
                   ['5858936','508c03b7f22059f1fa811c1774dbff89','阳光1期'],
                   ['6464295','20ebdb167b8ed58fc406f05cd2b78802','趣程15期'],
                   ['6666592','998aa35d1ed17635484eae5486e750db','趣程23期'],
                   ['8282535','d61b2231e589571cf2328a7855df335e','陈冲1期'],
                   ['104104493','957ad78e53a7fc2e751d36e6ec5329d5','广州10期'],
                   ['102102605','50126233532d2e4e1796a16e1247d7dc','趣程24期'],
                   ['127127862','b0af6b9ba284bd6b10b932cdeae96b74','趣程25期']]
    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+1):
                
                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={}
                                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['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 =[['趣程23期',10659,'T3A3LQZVZXN80M1A7OPVYYWT6Q2XPAJX','修明书楼','趣程23期'],
               ['趣程23期',10660,'T3A3LQZVZXN80M1A7OPVYYWT6Q2XPAJX','绍晖文海','趣程23期'],
               ['趣程21期',10656,'KXYIOQT29FDH80NUWRV9XUUK4QIOEIAB','赤凰文阁','趣程21期'],
               ['趣程21期',10678,'KXYIOQT29FDH80NUWRV9XUUK4QIOEIAB','千月书楼','趣程21期'],
               ['趣程yangg1',10672,'ZR7PM1YA5DQ6JCMVTH3G5TLHI47DG1GF','舒言文海','阳光1期'],
               ['趣程yangg1',10704,'ZR7PM1YA5DQ6JCMVTH3G5TLHI47DG1GF','向阳文海','阳光1期'],
               ['趣程清勇6',10675,'B6FK1HFW1V9ZPD6WX0FGBX3WP73E6M5V','璃月文楼','刘勇7月'],
               ['趣程清勇6',10676,'B6FK1HFW1V9ZPD6WX0FGBX3WP73E6M5V','璃月颂书','刘勇7月'],
               ['趣程清勇6',10677,'B6FK1HFW1V9ZPD6WX0FGBX3WP73E6M5V','璃月阅读','刘勇7月'],
               ['趣程15期',10696,'8OC7SNCL46ZEI7JBACXFDM8CP5JM1FSL','盛德文苑','趣程15期'],
               ['趣程15期',10697,'8OC7SNCL46ZEI7JBACXFDM8CP5JM1FSL','博涉文海','趣程15期'],
               ['趣程15期',10698,'8OC7SNCL46ZEI7JBACXFDM8CP5JM1FSL','高岑文轩','趣程15期']
               ]
    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={}
                                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
                                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['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(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 = 1599062400
    et_unix = 1599408000 
    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)
    #get_zzy_order(st_dt,et_dt)
    #get_zhangdu_order(st_unix,et_unix)
    #get_huasheng_order(st_unix,et_unix)
    #get_ysg_order(st_unix,et_unix)
start_all_job()

"""
start_job_time = '2020-07-08 00:30:00'

if __name__ == '__main__':
    scheduler = BlockingScheduler()
    scheduler.add_job(start_all_job, 'interval',days=1,start_date=start_job_time)
    scheduler.start()
"""