import  dingtalkchatbot
import requests

from tools import *

def get_data():
    sql="""
    SELECT   
 hour(a.create_date) as hour,
case  when POSITION('天创' in a.address)>0 then '天创'
when POSITION('电子' in a.address)>0 then '电子大厦'
when POSITION('虚拟' in a.address)>0 then '折扣劵'
when POSITION('虚拟' in a.address)>0 then '折扣劵'
when LENGTH(a.address)>2 then right(a.address,10)
else '空地址'
end as 'address',
a.amount_paid as 'amount_paid'
-- a.create_date, b.name,a.address,a.amount_paid,a.fee,a.invoice_title,a.phone,a.sn,a.order_type,a.order_status,a.payment_status,a.payment_method
from shop_5000.master_xx_order_202105  a join shop_5000.master_xx_order_item_202105 b on a.id=b.orders
WHERE -- year(create_date)=2022 and month(create_date)=8 and day(create_date)=17
a.create_date>=DATE(NOW()) and a.order_status<3 and a.payment_status>0
    """
    return execmysl('onlinetob',sql)

def fill_data():
    data_tob=get_data()
    temp=[]
    # total_temp_feiqi={}
    # total_temp_feiqi['ordernos']=data_tob.shape[0]
    # total_temp_feiqi['price']=round(data_tob['amount_paid'].sum(),2)
    total_temp=[]
    total_temp.append(data_tob.shape[0])
    total_temp.append(round(data_tob['amount_paid'].sum(),2))
    total_temp=[str(i) for i in total_temp]
    if data_tob.empty:
        temp.append("没有数据")
        return temp

    df_temp=data_tob.groupby(['hour','address'])['amount_paid'].sum().reset_index()
    df_temp['amount_paid']=df_temp['amount_paid'].apply(lambda x:round(x,2))
    df_temp=df_temp.astype(str)
    for i in df_temp.itertuples():
        temp.append("###### "+i.__getattribute__('hour')+','+i.__getattribute__('address')+','+i.__getattribute__('amount_paid'))
    return temp,total_temp


def final_msg():
    temp,total_temp=fill_data()
    template_msg={
        "msgtype": "markdown",
        "markdown": {
            "title": "消费地图当天的小时段订单及收入数据 ",
            "text": """### 消费地图当天的小时段订单及收入(总订单数:{0}笔,总收入:{1})
##### 小时段,收货地址,收入
"""
        }
    }
    #print('\n'.join(temp))
    template_msg['markdown']['text']=template_msg['markdown']['text'].format(*total_temp)
    #print(template_msg['markdown']['text'])
    template_msg['markdown']['text']+='\n'.join(temp)

    return template_msg



def send(access_token):
    template=final_msg()
    print(template)
    url="https://oapi.dingtalk.com/robot/send?access_token="+str(access_token)
    header={"Content-Type": "application/json"}
    t=requests.post(url,headers=header,json=template)
    return t.json()


if __name__ == '__main__':
    #print(send('e3da3eccd05a93f0667ffe1bdd67bb68760e112cbd73c7bcfd2949811ed46c81'))
    print(final_msg())