HHH_Home_Decoration_2/api/main.py

from enum import Enum
import time
from datetime import datetime
from typing import Optional
from pydantic import BaseModel
from fastapi import FastAPI, Query, UploadFile, File
import dataset,json
from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware
import pandas as pd
from gspread_pandas import Spread, Client
from oauth2client.service_account import ServiceAccountCredentials
import gspread
app = FastAPI()

origins = [
    "https://hhh.com.tw"
    "http://172.105.205.52",
    "http://172.105.205.52:8002",
    "http://172.104.93.163",
]

app.add_middleware(
    CORSMiddleware,
    # allow_origins=origins,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)
db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')

class deco_request(BaseModel):
    id: int
    name: str
    email: str
    phone: str
    
class deco_request_detail(BaseModel):
    id: int
    name: str
    email: str
    phone: str
    #detail
    loc: str
    h_class: str
    type: str
    budget: str
    size: float
    bed_num: int
    liv_num: int
    bath_num: int
    style: str
    prefer_date: str
    time_stamp: str
    gender: str

class client_info(BaseModel):
    name:str
    organization:str
    position:str
    phone:str
    email:str
    cert_last5:str
    where_learn:str
    why_sign:str
    
@app.get("/deco_request")
async def get_req():
    request_table = db['deco_request']
    statement = 'SELECT * FROM deco_request'
    reqs = []
    for req in db.query(statement):
        reqs.append({'name':req['name'],'email':req['email'],'phone':req['phone'],'loc':req['loc']
    ,'h_class':req['h_class'],'type':req['type'],'budget':req['budget'],'size':req['size'],'bed_num':req['bed_num']
    ,'liv_num':req['liv_num'],'bath_num':req['bath_num'],'style':req['style'],'prefer_date':req['prefer_date'],'gender':req.gender})
    return reqs

@app.post("/deco_request")
async def create_req(req:deco_request):
    request_table = db['deco_request']
    time_stamp = datetime.fromtimestamp(time.time())
    time_stamp = time_stamp.strftime("%Y-%m-%d %H:%M:%S")
 
    pk = request_table.insert({'name':req.name,'email':req.email,'phone':req.phone,'time_stamp':time_stamp})
    req.id = pk
    return req

@app.post("/add_client_info")
async def add_client_info(ci:client_info):
    request_table = db['client_info']
    time_stamp = datetime.fromtimestamp(time.time())
    time_stamp = time_stamp.strftime("%Y-%m-%d %H:%M:%S")
 
    pk = request_table.insert({'name':ci.name,'organization':ci.organization,'position':ci.position,'phone':ci.phone,'email':ci.email
    ,'cert_last5':ci.cert_last5,'where_learn':ci.where_learn,'why_sign':ci.why_sign,'time_stamp':time_stamp})
    
    return pk
@app.post("/deco_request_detail")
async def create_req_detail(req:deco_request_detail):
    
    statement = 'SELECT * FROM deco_request WHERE email="'+req.email+'"'
    email_cnt = 0
    for row in db.query(statement):
        email_cnt = email_cnt + 1
    
    statement = 'SELECT * FROM deco_request WHERE phone="'+req.phone+'"'
    phone_cnt = 0
    for row in db.query(statement):
        phone_cnt = phone_cnt + 1
    
    return_code = 0
    #return code 0=good, 1=email duplication, 2=phone duplication, 3= both duplicate
    '''
    if email_cnt>0 and phone_cnt==0:
        return_code = 1
    if email_cnt==0 and phone_cnt>0:
        return_code = 2
    if email_cnt>0 and phone_cnt>0:
        return_code = 3
    '''
    
    if return_code ==0 :
        request_table = db['deco_request']
        time_stamp = datetime.fromtimestamp(time.time())
        time_stamp = time_stamp.strftime("%Y-%m-%d %H:%M:%S")
        req.time_stamp = time_stamp
        pk = request_table.insert({'name':req.name,'email':req.email,'phone':req.phone,'loc':req.loc
        ,'h_class':req.h_class,'type':req.type,'budget':req.budget,'size':req.size,'bed_num':req.bed_num
        ,'liv_num':req.liv_num,'bath_num':req.bath_num,'style':req.style,'prefer_date':req.prefer_date,'time_stamp':req.time_stamp,'gender':req.gender})
        req.id = pk
        make_req_df()
        return return_code
    else :
        return return_code
    

def make_req_df():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')

    statement = 'SELECT * FROM deco_request ORDER BY time_stamp DESC'
    #2021-05-23 15:57:43
    df = pd.DataFrame()
    for row in db.query(statement):
        date_format = "%Y-%M-%d %H:%M:%S"
   
        #fdate = datetime.strptime(row['ts_date'],date_format)
        fdate = row['time_stamp'].strftime('%Y-%m-%d')
        
        df = df.append({'serial id':row['id'],'姓名':['name'],'email':row['email'],'電話':row['phone']
        ,'地區':row['loc'],'房屋類型':row['h_class'],"房屋型態":row['type'],'預算':row['budget']
        ,'坪數':row['size'],'房數':row['bed_num'],'廳數':row['liv_num'],'衛浴數':row['bath_num']
        ,'風格類型':row['style'],'預選裝修日期':row['prefer_date']}, ignore_index=True)
        save_sheet(df,'deco_request','sheet1')
    
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('c:\\keys\\spread2.json', scope)
    credentials = ServiceAccountCredentials.from_json_keyfile_name('spread2.json', scope)

    gc = gspread.authorize(credentials)
    spread = Spread(filename,creds=credentials)

    spread.df_to_sheet(df, index=False, sheet=tabname, start=startpos, replace=False)