# Author:Clown
# -*- codeing = utf-8 -*-
# @Time :2022/3/16 14:17
# @File: 食亨菜单导出.py


import requests
import pandas as pd
import time
import xlrd
from datetime import date, timedelta
import os
import shutil
import pymysql
import sys

def submit(startTime,endTime,headers,brandId):
    url_submit = "https://bi.shihengtech.com/api/excel/order/detail/export/submit"
    params_submit = {
            "brandId":brandId,
            "endTime":endTime,
            "labelIds":[],
            "orderStatusEnum":"VALID",
            "startTime":startTime,
            "platforms":[0,1],
            "provinces":None,
            "cities":None,
            "shopIds":None,
            "taskBizTypeEnum":"ORDER_DETAIL_DETAIL"}
    resp_submit = requests.post(url_submit,json = params_submit,headers = headers)
    if resp_submit.json()['code'] == 200:
        submit_result = 1
        print("成功提交请求")
        return submit_result
    else:
        submit_result = 0
        print("请求失败")
        return submit_result

def queryTasks(headers):
    url_queryTasks = "https://bi.shihengtech.com/api/shtask/queryTasks"
    params_queryTasks = {
        "pageNo":1,
        "pageSize":20
    }
    resp_queryTasks = requests.post(url_queryTasks,json = params_queryTasks,headers = headers)
    data_queryTasks = resp_queryTasks.json()['result']['result']
    df_link = data_queryTasks[0]["resultUrl"]
    df_name = data_queryTasks[0]["name"]
    return  df_link,df_name

def excel2parts(excel_file,save_path_forms,save_path_goods):
    # 打开excel文件
    for a,b,files in os.walk(excel_file,topdown = False):
        for file in files:
            path = str(excel_file)+'/'+str(file)
            workbook=xlrd.open_workbook(r'%s'%str(path))
            sheet_names=workbook.sheet_names()
            print(file, '正在拆分')
            for worksheet_name in sheet_names:
                if worksheet_name == '订单明细':
                    data_xls=pd.read_excel(path,worksheet_name,dtype=str)
                    data_xls.to_excel(save_path_forms + '/' +str(file)[:-5] + worksheet_name + '.xlsx', index=0,encoding='utf-8-sig')
                    print(file, worksheet_name, '已拆出')
                else:
                    data_xls = pd.read_excel(path, worksheet_name, dtype=str)
                    data_xls.to_excel(save_path_goods + '/' + str(file)[:-5] + worksheet_name + '.xlsx', index=0,encoding='utf-8-sig')
                    print(file, worksheet_name, '已拆出')

def linkTomySql(host,passwd,db_name):
    try:
        #本地连接为：localhost  服务器连接为：124.222.188.59
        db = pymysql.connect(
                        host=host, user="root",
                        passwd=passwd,
                        db=db_name,
                        charset='utf8mb4')
        print('connect to mysql server 成功')
        print('------------------------------------')
        return db
    except:
        print("could not connect to mysql server")
        db = "连接失败"
        return db

def update_scriptrunninglogs(log_date,running_Time,operation_results,table_name,db):
    try:
        sql = "INSERT INTO scriptrunninglogs VALUES (%s,%s,%s,%s,%s)"#date,running_Time,script_name,operation_results,table_name
        script_name = os.path.basename(sys.argv[0])
        value = (log_date,running_Time,script_name,operation_results,table_name)
        cursor = db.cursor()
        cursor.execute(sql, value)  # 执行sql语句
        db.commit()
        cursor.close()
    except:
        result = "数据库连接失败"
        print(result)

if __name__ == '__main__':
    log_date = (date.today()).strftime("%Y-%m-%d")
    start_run = time.time()

    startTime = (date.today() + timedelta(days=-1)).strftime("%Y-%m-%d")
    endTime = (date.today() + timedelta(days=-1)).strftime("%Y-%m-%d")
    # save_path = r'C:\Users\浆小白_何\Desktop\订单详情'
    # out_path = r'C:\Users\浆小白_何\Desktop\订单明细拆分'
    brandId = '102073'#浆小白
    user_token = "eyJhbGciOiJIUzUxMiJ9.eyJzdWIiOiJ7XCJ1c2VySWRcIjoyNDYyfSIsImV4cCI6MTY0ODAxNjI5MH0.jTbLT1MrLzyBleTp5jZjnvJzXG7NqSgo5ve_csbXohofdpXyvdxIpZE4POl3jp80T4wqHDwMHGglzfcvze4Byg"
    save_path = r'/home/python_flies/orderForms_jxb'
    out_path_forms  = r'/home/python_flies/orderFormsSplit_forms_jxb'
    out_path_goods  = r'/home/python_flies/orderFormsSplit_goods_jxb'
    # brandId = '105541'#粥小鲜
    # user_token = "eyJhbGciOiJIUzUxMiJ9.eyJzdWIiOiJ7XCJ1c2VySWRcIjo1MDAyfSIsImV4cCI6MTY0ODA4NzY1NX0.BgZuyoVkZU5y732blbRKmx9oswPUBnNqZJPk_34VxbFEJJlpyzEvoReaVMcXx-H0qJP8_Ry52CaE6uRscNzGhg"
    Cookie = "_ga=GA1.2.725502531.1618630729; " \
             "Hm_lvt_eaa57ca47dacb4ad4f5a257001a3457c=1625040492,1625123277,1625293351,1625448933; " \
             "_gid=GA1.2.296614433.1647229990; " \
             "acw_tc=76b20f6a16474114899837204e57f03f3766bf1b4d60819fca83da136ed7e6; " \
             "user-token=%s; " % str(user_token) + "_gat_gtag_UA_127759150_3=1"
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.4844.51 Safari/537.36",
        "Content-Type": "application/json;charset=UTF-8",
        "Accept": "application/json",
        "Accept-Encoding": "gzip, deflate, br",
        "user-token": user_token,
        "Cookie": Cookie}
    shutil.rmtree(save_path)
    shutil.rmtree(out_path_forms)
    shutil.rmtree(out_path_goods)
    os.mkdir(save_path)
    os.mkdir(out_path_forms)
    os.mkdir(out_path_goods)
    print('已清空文件夹')
    if submit(startTime, endTime, headers,brandId) == 1 :#提交请求成功执信
        s = 2
        df_link,df_name = queryTasks(headers)
        while df_link == None:
            time.sleep(2)
            print("\rloading %d"%(s),end='')
            s += 2
            df_link, df_name = queryTasks(headers)
        df_data = requests.get(df_link).content
        with open(save_path+r'/%s.xlsx' % str(df_name),mode='wb') as f:
            f.write(df_data)
        print("\n",df_name,'下载成功')
        excel2parts(save_path, out_path_forms, out_path_goods)
        operation_results = '执行成功'

    else:
        operation_results = '有未知错误，请检查'
        print("有未知错误，请检查")
    end_run = time.time()
    running_Time = start_run - end_run
    db = linkTomySql('localhost', '111???hxx', 'zuzu_data')
    update_scriptrunninglogs(log_date, running_Time, operation_results, "table_name", db)