hhh_flow_wk_report/main.py

"""
    目的:
        流量週報圖表自動化更新，可以往回查詢。
    參考:
        ECHARTS: https://echarts.apache.org/zh/tutorial.html#5%20%E5%88%86%E9%92%9F%E4%B8%8A%E6%89%8B%20ECharts
        templates: https://fastapi.tiangolo.com/advanced/templates/
"""
from fastapi import FastAPI, Request
from fastapi.responses import HTMLResponse
from fastapi.staticfiles import StaticFiles
from fastapi.templating import Jinja2Templates
import dataset
import numpy as np
import pandas as pd
from starlette.responses import Response
from pprint import pprint


app = FastAPI()
app.mount("/static", StaticFiles(directory="static"), name="static")
templates = Jinja2Templates(directory="templates")


WEEK_NUM = 31



def update_weekly_data():
    """
        一週更新一次。
    """
    pass


def get_hhh_weekly_data():
    """ 取得此table的資料作為後續畫圖使用。 """
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')
    table = db['hhh_weekly_report']
    df = pd.DataFrame(table)
    int_columns = ['id', 'ga:users', 'ga:newusers', 'ga:sessions', 'ga:pageviews', 'ga:isoWeek']
    float_columns = ['ga:bounceRate', 'ga:pageviewsPerSession']
    str_columns = ['category']

    for column in int_columns:
        df[column] = df[column].apply(func=lambda x: int(x))
    for column in float_columns:
        df[column] = df[column].apply(func=lambda x: float(x))
    for column in str_columns:
        df[column] = df[column].apply(func=lambda x: str(x))
    return df

def get_dates(df, weeknum):
    """ 從df得到當週日期當作X-axis給Echart使用。 """
    dates = df[df['ga:isoWeek'] == weeknum]['ga:date']
    return sorted(list(set(dates)))

def get_users(df, category='organic', weeknum=26, is_new=True):
    if is_new:
        users = df[(df['category'] == category) & (df['ga:isoWeek'] == weeknum)]['ga:users']
    else:
        users = df[(df['category'] == category) & (df['ga:isoWeek'] == weeknum)]['ga:newusers']
    return list(users)


##########################

df = get_hhh_weekly_data()
dates = get_dates(df, 25)
users_social = get_users(df, 'organic', 25, True)
print(df)
print(dates)
print(users_social)


db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')
query = f"""
select 
        A.query, A.clicks, A.ctr, A.impressions, A.weeknum, 
        B.weeknum as last_wk_weeknum, B.clicks as last_wk_clicks, B.ctr as last_wk_ctr, B.impressions as last_wk_impressions,
        ((A.clicks-B.clicks) / B.clicks)*100 as clicks_growth
    from
        ((select query, clicks, ctr, impressions, weeknum 
        from hhh.hhh_weekly_keywords 
        where weeknum={WEEK_NUM+1} and clicks >=5) as A
        inner join (select query, clicks, ctr, impressions, weeknum from hhh.hhh_weekly_keywords  where weeknum={WEEK_NUM} and clicks >= 5) as B
        on A.query=B.query)
    order by 
        clicks_growth desc
    limit 20;
"""
keywords_data = db.query(query)
keywords_data = pd.DataFrame(keywords_data, columns=['query', 'clicks', 'ctr', 'impressions', 'weeknum', 'p_weeknum', 'p_clicks', 'p_ctr', 'p_impressions', 'clicks_growth'])
# keywords_data = pd.read_csv('keywords_growth_ckover5.csv')
print(keywords_data)

###########################

@app.get('/', response_class=HTMLResponse)
def index(request: Request):
    return templates.TemplateResponse(
        name='index.html', 
        context={'request': request, 'dates': dates, 'users': users_social, 'keywords_table_html': keywords_data.to_html()}
    )


# 要改
@app.get('/keywords/table/{weeknum}', response_class=HTMLResponse)      #　https://fastapi.tiangolo.com/advanced/custom-response/
def get_keywords_table(weeknum: int, request: Request, response: Response):
    resp = keywords_data.copy()
    resp.index = range(1, len(resp)+1)
    resp = resp[['query', 'clicks_growth', 'ctr', 'clicks', 'impressions']]
    resp.loc[:, 'clicks_growth'] = resp.loc[:, 'clicks_growth'].apply(func=lambda x: '{:.2f}'.format(x))
    resp.loc[:, 'ctr'] = resp.loc[:, 'ctr'].apply(func=lambda x: str(round(x*100, 4))[:str(x).index('.')+4] + ' %')
    
    pprint(request)
    pprint(response)

    return resp.to_html(justify='center')


@app.get('/users/{category}/{weeknum}/{is_new}')
def get_users_data(category: str, weeknum: int, is_new: bool):
    dates = get_dates(df, weeknum)
    users = get_users(df, category, weeknum, is_new)
    return {
        'dates': dates,
        'users': users
    }

@app.get('/users/{weeknum}')
def get_users_overview(weeknum: int):
    dates = get_dates(df, weeknum)
    organic_newusers = get_users(df, 'organic', weeknum, True)
    organic_users = get_users(df, 'organic', weeknum, False)
    social_newusers = get_users(df, 'social', weeknum, True)
    social_users = get_users(df, 'social', weeknum, False)

    return {
        'dates': dates,
        'organic_newusers': organic_newusers,
        'organic_users': organic_users,
        'social_newusers': social_newusers,
        'social_users': social_users
    }


@app.get('/keywords/{weeknum}')
def get_keywords_sorted(weeknum: int):
    keywords = list(keywords_data.loc[:, 'query'])
    # print(keywords)
    keywords_growth = list(keywords_data.loc[:, 'clicks_growth'])
    
    return {'keywords': keywords, 'keywords_growth': keywords_growth}