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
import asyncio
app = FastAPI()
import nest_asyncio
import threading

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=["*"],
)


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: str
    # bed_num: int
    # liv_num: int
    # bath_num: int
    # style: str
    # prefer_date: str
    time_stamp: str
    # gender: str
    version: 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():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')
    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})
    '''
        reqs.append({'name':req['name'],'email':req['email'],'phone':req['phone'],'loc':req['loc']
        ,'h_class':req['h_class'], 'size':req['size']})
    
    return reqs

@app.post("/deco_request")
async def create_req(req:deco_request):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')
    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):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')
    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):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')
    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,'version':req.version})
        '''
        pk = request_table.insert({'name':req.name,'email':req.email,'phone':req.phone,'loc':req.loc
        , 'h_class':req.h_class, 'size':req.size,'time_stamp':req.time_stamp,'version':req.version})
        req.id = pk
        x = threading.Thread(target=make_req_df)
        x.start()
        #asyncio.run(make_req_df())23
        return return_code
    else :
        return return_code
    

async 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)