violet-beauty/api/main.py

import time
from datetime import datetime
from colorama import Back
from fastapi import FastAPI, Form, Request, BackgroundTasks
from fastapi.templating import Jinja2Templates
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel
from oauth2client.service_account import ServiceAccountCredentials
import os, dataset, json, gspread
from gspread_pandas import Spread, Client
import pandas as pd
from fastapi_utils.tasks import repeat_every

app = FastAPI()
directory=os.path.dirname(os.getcwd())
templates = Jinja2Templates(directory)

origins = [
    "*"
]
app.add_middleware(
    CORSMiddleware,
    allow_origins=origins,
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

@app.get("/")
async def read_root(request: Request):
    return templates.TemplateResponse("index.html",{"request": request})

@app.post("/submitform")
async def formentry(bgtask: BackgroundTasks, name: str = Form(...), phone: str = Form(...), loc: str = Form(...), course_name: str = Form(...), bookdate: str = Form(...), booktime: str = Form(...)):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/violetbeauty?charset=utf8mb4')
    request_table = db['booking_info']
    time_stamp = datetime.fromtimestamp(time.time())
    time_stamp = time_stamp.strftime("%Y-%m-%d %H:%M:%S")

    print(time_stamp)
    
    request_table.insert({"name": name, "phone": phone, "location": loc, "course_name":course_name, "book_date": bookdate, "book_time": booktime, "time_stamp": time_stamp})

    bgtask.add_task(getdata, db)

    return 0

def getdata(db):
    statement = 'SELECT * FROM booking_info ORDER BY time_stamp DESC'
    df = pd.DataFrame()
    rowcount = 0

    for row in db.query(statement):
        date_format = "%Y-%M-%d %H:%M:%S"
        fdate = row['time_stamp'].strftime('%Y-%m-%d')
        
        df = df.append({'填單時間':row['time_stamp'],'姓名':row['name'],'電話':str('\'' + row['phone']),'預約體驗店家':row['location'],'選擇課程':row['course_name'],'預約日期':row['book_date'],'預約時間':row['book_time']}, ignore_index=True)

        rowcount +=1

    if rowcount!=0:
        save_sheet(df,'紫晴預約資料','預約資料')
    else:
        print('沒有資料')

    return 0

def save_sheet(df,filename,tabname,startpos='A1'):

    scope = ['https://spreadsheets.google.com/feeds',
            'https://www.googleapis.com/auth/drive']

    credentials = ServiceAccountCredentials.from_json_keyfile_name('violet-beauty-booking-data-8ef5f0d9a747.json', scope)

    gc = gspread.authorize(credentials)
    spread = Spread(filename,creds=credentials)
    spread.df_to_sheet(df, index=False, sheet=tabname, start=startpos, replace=False)

    time_stamp = datetime.fromtimestamp(time.time())
    time_stamp = time_stamp.strftime("%Y-%m-%d %H:%M:%S")
    
    spread.sheet.update('I1', '最後更新:' + time_stamp)

@app.get("/getbooking_data")
async def getbookingdata(bgtask: BackgroundTasks):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/violetbeauty?charset=utf8mb4')
    bgtask.add_task(getdata, db)
    return '資料取出中...'

@app.on_event("startup")
@repeat_every(seconds=60)
def update_booking():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/violetbeauty?charset=utf8mb4')
    getdata(db)