NFTBoard/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
from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware
import json
import urllib.request
import dataset,json
import pandas as pd
from gspread_pandas import Spread, Client
from oauth2client.service_account import ServiceAccountCredentials
import gspread
from first import first

app = FastAPI()

origins = [
    "http://172.105.205.52",
    "http://172.105.205.52:8001",
]

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/NFTBoard?charset=utf8mb4')
class subreq(BaseModel):
    email:str

class ContactUs(BaseModel):
    country_code: str
    username: str
    email: str
    phone: str
    coop_dif: Optional[bool] = 0
    coop_ad: Optional[bool] = 0
    coop_acting: Optional[bool] = 0
    coop_marketing: Optional[bool] = 0
    work_address: Optional[str] = ''


@app.get("/showdata/{limit}")
async def showdata(limit):
    # url = 'https://api.coinmarketcap.com/data-api/v3/nft/collections?start=0&limit=' + limit
    url = 'https://api.coinmarketcap.com/data-api/v3/nft/collections?start=0&limit=' + limit + '&period=2'
    r = urllib.request.urlopen(url)
    collections = json.loads(r.read())['data']['collections']

    reqs = []

    for req in collections:
        website = '#'

        if req.get('website') is not None:
            website = req['website']

        collection_net_worth = round(float(req['netWorth']), 2)
        volume_7d = round(float(req['volume7d']), 2)
        volume_all_time = round(float(req['volumeAT']), 2)
        
        reqs.append({'rank':req['rank'],'slug':req['slug'],'logo':req['logo'],'website':website,'name':req['name'],'assets':req['assets'],'collection_net_worth':collection_net_worth,'volume_7d':volume_7d,'sales_7d':req['sales7d'],'volume_all_time':volume_all_time,'sales_all_time':req['salesAT'],'owners':req['owners']})
    return reqs

@app.post("/add_sub")
async def add_subclient_info(req:subreq):
    code = 1
    sub_table = db['Subscribe']
    time_stamp = datetime.fromtimestamp(time.time()).strftime("%Y-%m-%d %H:%M:%S")

    statement='SELECT COUNT(1) FROM Subscribe WHERE email = "'+req.email+'"'
    if first(db.query(statement))['COUNT(1)']==0:
        pk = sub_table.insert({'email':req.email,'timestamp':time_stamp})
        make_ci_df()
        code = 0
    return {'msg':code}#0 means succss added, 1 means email is duplicated


@app.post('/add_contact_us')
async def add_contact_us_client_info(req: ContactUs):
    """ 新增Contact Us資料，先存入DB，並從DB撈回全部資料，更新Google Sheet. """
    code = 1
    table = db['ContactUs']
    time_stamp = datetime.fromtimestamp(time.time()).strftime("%Y-%m-%d %H:%M:%S")

    # 會根據email做判斷，此處先comment給測試使用。
    # statement = 'SELECT COUNT(1) FROM ContactUs WHERE email = "'+ req.email +'"'
    # if first(db.query(statement))['COUNT(1)'] == 0:
    #     table.insert(
    #         {
    #             'country_code':req.country_code,
    #             'username': req.username,
    #             'email': req.email,
    #             'phone': req.phone,
    #             'coop_dif': req.coop_dif,
    #             'coop_ad': req.coop_ad,
    #             'coop_acting': req.coop_acting,
    #             'coop_marketing': req.coop_marketing,
    #             'work_address': req.work_address,
    #             'timestamp': time_stamp
    #         }
    #     )
    #     code = 0
    #     # 更新sheet
    #     make_contact_us_df()

    table.insert(
        {
            'country_code':req.country_code,
            'username': req.username,
            'email': req.email,
            'phone': req.phone,
            'coop_dif': req.coop_dif,
            'coop_ad': req.coop_ad,
            'coop_acting': req.coop_acting,
            'coop_marketing': req.coop_marketing,
            'work_address': req.work_address,
            'timestamp': time_stamp
        }
    )
    code = 0
    # 更新sheet
    make_contact_us_df()

    return {'msg':code}#0 means succss added, 1 means email is duplicated



@app.get("/get_sub_list")
async def add_subclient_info():
    statement = 'SELECT * FROM Subscribe'
    result = []
    for row in db.query(statement):
        result += [{'id':row['id'],'email':row['email'],'timeStamp':row['timestamp']}]
    return result


def make_ci_df():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/NFTBoard?charset=utf8mb4')
    statement = 'SELECT * FROM Subscribe ORDER BY timestamp 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 = row['timestamp'].strftime('%Y-%m-%d %H時 %M分')

        df = df.append({'serial id':row['id'],'e-mail':row['email'],'登記時間':fdate}, ignore_index=True)

    df = df[['serial id','e-mail','登記時間']]
    save_sheet(df,'subscribe_rec','Sheet1')


def make_contact_us_df():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/NFTBoard?charset=utf8mb4')
    statement = 'SELECT * FROM ContactUs ORDER BY timestamp DESC'
    df = pd.DataFrame()
    for row in db.query(statement):
        
        df = df.append({'id':  row['id'], '國家區域': row['country_code'], '姓名': row['username'], '聯絡Email': row['email'], '聯絡手機': row['phone'],  '合作方式_異業合作': row['coop_dif'], '合作方式_廣告刊登': row['coop_ad'], '合作方式_代理發行':  row['coop_acting'], '合作方式_知識產權行銷': row['coop_marketing'], '作品連結': row['work_address'], '申請時間': row['timestamp']}, ignore_index=True)
    df = df[['id', '國家區域', '姓名', '聯絡Email', '聯絡手機', '合作方式_異業合作', '合作方式_廣告刊登', '合作方式_代理發行', '合作方式_知識產權行銷', '作品連結', '申請時間']]
    # print(df)
    save_sheet(df, '聯絡NFTBoard (回應)', 'Sheet1',  json_name='cred.json')


def save_sheet(df,filename,tabname,startpos='A1', json_name='spread2.json'):
    scope = ['https://spreadsheets.google.com/feeds',
            'https://www.googleapis.com/auth/drive']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(json_name, scope)
    gc = gspread.authorize(credentials)
    spread = Spread(filename,creds=credentials)
    spread.df_to_sheet(df, sheet=tabname, start=startpos, replace=True, index=False)