OpenTalk/api/main.py

from enum import Enum
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
from fastapi.staticfiles import StaticFiles
import time
from datetime import datetime
from PIL import Image
import io
from typing import List
import threading

app = FastAPI()
origins = [
    "http://172.105.205.52",
    "http://172.105.205.52:8000",
]

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

serverADDR = 'welife.asia:8002/'
cover_img_dir = 'static/cover_img/'
app.mount("/static/cover_img", StaticFiles(directory="static/cover_img"), name="static/cover_img")



##########TAG CRUD################TAG CRUD##################TAG CRUD###########TAG CRUD##################
class Tag(BaseModel):
    id: int
    name: str

@app.get("/tags")
async def get_tags():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    statement = 'SELECT id,name FROM tag'
    tags = []
    for row in db.query(statement):
        tags.append({'id':row['id'],'name':row['name']})
    return tags

@app.post("/create_tag")
async def create_tags(tag:Tag):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    tag_table = db['tag']
    pk = tag_table.insert({'name':tag.name})
    tag.id = pk
    return tag
	
@app.get("/del_tag/{tag_id}")
async def delete_tags(tag_id:int):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    sqls = 'DELETE FROM tag WHERE id = '+str(tag_id)
    db.query(sqls)
    sqls = 'DELETE FROM course_tag WHERE tag_id = '+str(tag_id)
    db.query(sqls)
    return 'success'
	
@app.post("/update_tag/")
async def update_tags(tag_id,tag:Tag):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    sqls = 'UPDATE tag\
    SET name="'+tag.name+'"\
    WHERE id='+tag_id+';'
    db.query(sqls)
    tag.id=tag_id
    return tag
    
@app.get("/tags/{tag_id}")
async def get_tags(tag_id):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    statement = 'SELECT id,name FROM tag where id = '+tag_id
    for row in db.query(statement):
        result = {'id':row['id'],'name':row['name']}
    return result
	
	
	
###########COURSE CRUD###########COURSE CRUD###########COURSE CRUD###########COURSE CRUD###########
class Course(BaseModel):
    id: int
    title: str
    profile: str
    url: str #course website location url
    cover_img: str #img url form
    teacher_name: str
    teacher_img: str #teacher user image url form
    price: int
    price_discount: int
    hours: float
    units: int
@app.post("/get_courses_by_cid")
async def get_courses(cids:List[int]):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    cid_str = '('
    for t in cids:
        cid_str = cid_str + str(t) +','
    cid_str = cid_str[:-1]+')'

    courses = []
    statement = 'SELECT * FROM course WHERE id IN'+cid_str

    for row in db.query(statement):
        courses.append({'id':row['id'],'title':row['title'],'url':row['url']
        ,'teacher_name':['teacher_name'],'price':row['price'],'price_discount':row['price_discount'],'profile':row['profile']
        ,'cover_img':'https://'+serverADDR + row['cover_img'],'hours':row['hours'],'units':row['units']})
    return courses


@app.post("/get_courses")
async def get_courses(tags:List[int]):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    tag_str = '('
    for t in tags:
        tag_str = tag_str + str(t) +','
    tag_str = tag_str[:-1]+')'


    courses = []
    statement = 'SELECT * FROM course_tag '\
    'INNER JOIN course on course_tag.course_id=course.id '\
    'INNER JOIN tag on course_tag.tag_id = tag.id '\
    'WHERE tag.id IN'+tag_str+' ORDER BY clicks DESC'

    for row in db.query(statement):
        courses.append({'id':row['course_id'],'title':row['title'],'url':row['url']
        ,'teacher_name':['teacher_name'],'price':row['price'],'price_discount':row['price_discount'],'profile':row['profile']
        ,'cover_img':'https://'+serverADDR + row['cover_img'],'hours':row['hours'],'units':row['units']})
    return courses

@app.post("/create_courses")
async def create_courses(tags: list,course:Course):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    #add to course table
    course_table = db['course']
    pk = course_table.insert({'title':course.title,'profile':course.profile,'url':course.url,'cover_img':course.cover_img,'teacher_name':course.teacher_name,
    'price':course.price,'price_discount':course.price_discount,'hours':course.hours,'units':course.units})
    course.id = pk
    course_tag_table = db['course_tag']
    for tag in tags:
        course_tag_table.insert({'course_id':course.id,'tag_id':tag})
    x = threading.Thread(target=to_sheet)
    x.start()
    return {'msg':'新增成功'}
        
@app.get("/del_course/{course_id}")
async def delete_course(course_id:int):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    sqls = 'DELETE FROM course WHERE id = '+str(course_id)
    db.query(sqls)
    sqls = 'DELETE FROM course_tag WHERE course_id = '+str(course_id)
    db.query(sqls)
    return {'msg':'新增成功'}




@app.post("/upload_cover_img/")
async def create_upload_file(file: UploadFile = File(...)):
    img_name = str(time.time()).replace('.','')

    contents = await file.read()
    image = Image.open(io.BytesIO(contents))
    image= image.convert("RGB")
    image.save(cover_img_dir+img_name+'.jpg')

    return {"msg": serverADDR+cover_img_dir+img_name+'.jpg'}

    
async def to_sheet():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    statement = 'SELECT * FROM course ORDER BY clicks DESC'
    
    df = pd.DataFrame()
    for row in db.query(statement):
        date_format = "%Y-%M-%d %H:%M:%S"
        
        df = df.append({'serial id':row['id'],'標題':['Title'],'介紹':row['profile'],'url':row['url']
        ,'圖片':row['cover_img'],'價錢':row['price'],"折扣後價錢":row['price_discount'],'時數':row['hours']
        ,'單元數':row['units']}, 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('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)