#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
import pandas as pd
import pymysql
import json
import datetime


# 根据id给出所有尺码
conn = pymysql.connect(host="192.168.103.250", user='root',
                       passwd='123456', charset="utf8")

def readBrandFromMysql():
    sql_query = "SELECT PRODUCT_ID as productId, BRAND_ID FROM du_info.BRAND_PRODUCT WHERE 1"
    df = pd.read_sql(sql_query, con=conn)
    return df

def readBrandNameFromMysql():
    sql_query = "SELECT ID as BRAND_ID, NAME FROM du_info.BRAND_7 WHERE 1"
    df = pd.read_sql(sql_query, con=conn)
    return df

def brandAmount(file, outPath):
    df = pd.read_excel(file, 'Sheet1')
    brand_df = pd.concat([readBrandFromMysql()], sort=True)
    name_df = pd.concat([readBrandNameFromMysql()], sort=True)

    result = pd.merge(df, brand_df, on=['productId'])

    amount = result['amount'].groupby([result['BRAND_ID']]).sum()
    amount.rename(columns={'BRAND_NAME': 'name'}, inplace=True)

    priceData = pd.DataFrame(amount, columns=['amount'])
    finalData = priceData.reset_index()

    finalData2 = pd.merge(finalData, name_df, on=['BRAND_ID'])

    finalData1 = finalData2.sort_values(
        by='amount', ascending=False)

    nowTime = datetime.datetime.now()
    yesTime = nowTime + datetime.timedelta(days=-1)
    filePath = outPath+'/brand-daily-'+yesTime.strftime('%m%d')+'.xlsx'

    finalData1.to_excel(filePath, sheet_name='Sheet1', index=0)
    print(json.dumps({
        'path': filePath
    }))

if(sys.argv.__len__() > 2):
    brandAmount(sys.argv[2], sys.argv[1])