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)