db_router.py 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474
  1. from fastapi import APIRouter,UploadFile, File,Body
  2. from supabase import create_client, Client
  3. from dotenv import load_dotenv
  4. import os
  5. from datetime import datetime
  6. from random import choice
  7. from typing import Annotated
  8. from pydantic import Field
  9. load_dotenv()
  10. # supaspace 連線
  11. url: str = os.environ.get('SUPABASE_URL')
  12. key: str = os.environ.get('SUPABASE_KEY')
  13. supabase: Client = create_client(url, key)
  14. dbRouter = APIRouter()
  15. @dbRouter.get("/click")
  16. def add_click_time(click_type :str = None,brand:str = None):
  17. try:
  18. if click_type == None :
  19. response = supabase.table('click_time').select("*").eq('id', 1).execute()
  20. click_time = response.data[0]['click_time'] + 1
  21. data, count = supabase.table('click_time') \
  22. .update({'click_time':click_time,'update_time':str(datetime.now())})\
  23. .eq('id', 1)\
  24. .execute()
  25. elif click_type == "card" :
  26. response = supabase.table('click_time').select("*").eq('id', 2).execute()
  27. click_time = response.data[0]['click_time'] + 1
  28. data, count = supabase.table('click_time') \
  29. .update({'click_time':click_time,'update_time':str(datetime.now())})\
  30. .eq('id', 2)\
  31. .execute()
  32. elif click_type == "card-en" :
  33. response = supabase.table('click_time').select("*").eq('name', "國際貴賓卡-en").execute()
  34. click_time = response.data[0]['click_time'] + 1
  35. print(response)
  36. data, count = supabase.table('click_time') \
  37. .update({'click_time':click_time,'update_time':str(datetime.now())})\
  38. .eq('id', 3)\
  39. .execute()
  40. elif click_type == "click_url" and brand:
  41. response = supabase.table('101_brand').select("*").eq("name",brand).execute()
  42. click_time = response.data[0][click_type] + 1
  43. data, count = supabase.table('101_brand') \
  44. .update({click_type:click_time})\
  45. .eq("name",brand)\
  46. .execute()
  47. elif click_type == "click_address" and brand:
  48. response = supabase.table('101_brand').select("*").eq("name",brand).execute()
  49. click_time = response.data[0][click_type] + 1
  50. data, count = supabase.table('101_brand') \
  51. .update({click_type:click_time})\
  52. .eq("name",brand)\
  53. .execute()
  54. return {"state":"success","click_time" : click_time}
  55. except Exception as e:
  56. return {"state":str(e)}
  57. @dbRouter.get("/find_brand")
  58. def find_brand(keyword:str = None,language :str = "ch",page_num : int = None,page_amount: int = None,search_name : str = None):
  59. if keyword is None :
  60. query = supabase.table('101_brand').select('*').eq("language", language)
  61. else :
  62. keyword_list = keyword.split(",")
  63. query= supabase.table('101_brand').select('*').eq("language", language)
  64. for keyword_tmp in keyword_list :
  65. query = query.like('tags', f'%{keyword_tmp}%')
  66. if search_name:
  67. query = query.like('name', f'%{search_name}%')
  68. result,_ = query.order("id", desc=True).execute()
  69. count = len(result[1])
  70. if page_num and page_amount :
  71. offset = (page_num - 1) * page_amount
  72. query = query.range(offset, offset + page_amount-1)
  73. try:
  74. data,_ = query.execute()
  75. result = []
  76. for shop in data[1] :
  77. json = {
  78. "type" : shop["type"],
  79. "info" : shop
  80. }
  81. if language != "ch" :
  82. if shop["floor"] == "館外" :
  83. json["info"]["floor"] = "outside"
  84. result.append(json)
  85. return {"state":"success","all_num" : count,"data" : result}
  86. except Exception as e:
  87. return {"state":"fail","message" :str(e)}
  88. @dbRouter.get("/arviews")
  89. def arviews(start:str,end:str,language:str = "ch"):
  90. try :
  91. data, count = supabase.table('101_arviews')\
  92. .select('*')\
  93. .eq('start_loc', start) \
  94. .like('tour_place', f'%{end}%') \
  95. .execute()
  96. result :str
  97. words :str
  98. if len(data[1]) != 0:
  99. if language == "ch" :
  100. result = data[1][0]["url"]
  101. words = data[1][0]["words"]
  102. else:
  103. result = data[1][0]["en_url"]
  104. words = data[1][0]["en_words"]
  105. else :
  106. result = "no this route"
  107. return {"state":"success","url" : result,"words" : words}
  108. except Exception as e:
  109. return {"state":"fail","message" :str(e)}
  110. @dbRouter.get("/static_tickets")
  111. async def static_tickets(is_Chinese : int = None):
  112. try:
  113. data =None
  114. if is_Chinese :
  115. data, count = supabase.table('101_ticket')\
  116. .select('*')\
  117. .in_('id', [1,3,6,7])\
  118. .execute()
  119. else :
  120. data, count = supabase.table('101_ticket')\
  121. .select('*')\
  122. .in_('id', [182,183,180])\
  123. .execute()
  124. result = []
  125. for shop in data[1] :
  126. json = {
  127. "type" : shop["type"],
  128. "info" : shop
  129. }
  130. result.append(json)
  131. return {"state":"success","result" : result}
  132. except Exception as e:
  133. return {"state":"fail","message" :str(e)}
  134. @dbRouter.get("/ad/{type}")
  135. def read_root(type:str,language :str = "ch"):
  136. keyword1 :str
  137. keyword2 :str
  138. if type == "美食伴手禮":
  139. keyword1 = "餐飲"
  140. keyword2 = "伴手禮"
  141. else :
  142. keyword1 = "住宿"
  143. keyword2 = "伴手禮"
  144. data, count = supabase.table('101_brand')\
  145. .select('*')\
  146. .eq("floor","館外")\
  147. .eq("language", language)\
  148. .or_(f"tags.ilike.%{keyword1}%,tags.ilike.%{keyword2}%")\
  149. .order("id", desc=True)\
  150. .execute()
  151. result = data[1]
  152. # 從結果中隨機選擇一筆資料
  153. random_row = choice(result)
  154. if language != "ch" :
  155. if random_row["floor"] == "館外" :
  156. random_row["floor"] = "outside"
  157. #print(random_row)
  158. return {"data": random_row}
  159. @dbRouter.post("/message_not_in_cache")
  160. def message_not_in_cache(question :str ,answer :str,client_id : str = "0",language:str = "ch" ):
  161. try:
  162. data, count = supabase.table('client_message').select('*').eq("question",question).execute()
  163. if len(data[1]) != 0 :
  164. return {"state": 200 , "message" : "have saved"}
  165. data, count = supabase.table('client_message').insert({"client_id": client_id, "question": question,"answer":answer,"language":language}).execute()
  166. return {"state": 200 , "message" : "success"}
  167. except Exception as e:
  168. return {"state": 500 , "message" : str(e)}
  169. from typing import List, Optional
  170. from pydantic import BaseModel
  171. class MessageSaveRequest(BaseModel):
  172. question: str
  173. answer: str
  174. data_list: str
  175. @dbRouter.post("/message_save")
  176. async def message_save(request:MessageSaveRequest ):
  177. try :
  178. data, count = supabase.table("log_record").insert({
  179. "question": request.question,
  180. "answer": request.answer,
  181. "data_list": request.data_list,
  182. # "mp3_url": mp3_url
  183. }).execute()
  184. return {"state": 200 , "message" : "success"}
  185. except Exception as e:
  186. return {"state": 500 , "message" : str(e)}
  187. @dbRouter.post("/message_save_mp3")
  188. async def message_save(question:str = Body(None),answer: str = Body(None),data_list: str= Body(None), mp3_file: UploadFile = File(None) ):
  189. try :
  190. mp3_url = None # 初始化 mp3_url
  191. date_time = datetime.now().strftime('%Y-%m-%d_%H:%M:%S')
  192. new_filename = f"{mp3_file.filename.split('.')[0]}_{date_time}.mp3"
  193. if mp3_file: # 检查是否提供了 MP3 文件
  194. # 定义保存文件的路径
  195. save_path = os.path.join("static", "mp3", new_filename)
  196. # 确保目录存在
  197. os.makedirs(os.path.dirname(save_path), exist_ok=True)
  198. # 将上传的文件保存到指定路径
  199. with open(save_path, "wb") as f:
  200. f.write(await mp3_file.read())
  201. mp3_url = save_path # 设置 mp3_url
  202. data, count = supabase.table("log_record").insert({
  203. "question": question,
  204. "answer": answer,
  205. "data_list": data_list,
  206. "mp3_url": f"/{mp3_url}"
  207. }).execute()
  208. return {"state": 200 , "message" : "success"}
  209. except Exception as e:
  210. return {"state": 500 , "message" : str(e)}
  211. from pydantic import BaseModel, EmailStr
  212. import base64
  213. import pickle
  214. from email.mime.text import MIMEText
  215. from google.auth.transport.requests import Request
  216. from google.oauth2.credentials import Credentials
  217. from google_auth_oauthlib.flow import InstalledAppFlow
  218. from googleapiclient.discovery import build
  219. import os
  220. SCOPES = ['https://www.googleapis.com/auth/gmail.send']
  221. class dataform(BaseModel):
  222. title: str
  223. content: str
  224. client_name: str
  225. gender: str
  226. email: EmailStr
  227. phone: str
  228. type:str
  229. def send_email(to_email,from_email,message):
  230. creds = None
  231. # 如果存在 token.pickle 文件,讀取
  232. if os.path.exists('token.pickle'):
  233. with open('token.pickle', 'rb') as token:
  234. creds = pickle.load(token)
  235. # 如果沒有有效的憑據,就進行登入
  236. if not creds or not creds.valid:
  237. if creds and creds.expired and creds.refresh_token:
  238. creds.refresh(Request())
  239. else:
  240. flow = InstalledAppFlow.from_client_secrets_file(
  241. 'credentials.json', SCOPES)
  242. creds = flow.run_local_server(port=0)
  243. # 保存憑據
  244. with open('token.pickle', 'wb') as token:
  245. pickle.dump(creds, token)
  246. service = build('gmail', 'v1', credentials=creds)
  247. # 設定郵件
  248. message = MIMEText(message)
  249. message['to'] = to_email
  250. message['from'] = from_email
  251. message['subject'] = '101 ai客服 表單新資料'
  252. raw = base64.urlsafe_b64encode(message.as_bytes()).decode()
  253. # 發送郵件
  254. try:
  255. message = service.users().messages().send(userId='me', body={'raw': raw}).execute()
  256. print(f'已發送郵件: {message["id"]}')
  257. return "success"
  258. except Exception as error:
  259. print(f'發送郵件時出錯: {error}')
  260. return "fail"
  261. @dbRouter.post("/insert_table")
  262. def insert_table(data: dataform):
  263. try:
  264. response,count = supabase.table('lost_property').insert(data.dict()).execute()
  265. email_content = f"問題類別:{response[1][0]['type']}\n標題:{response[1][0]['title']}\n內容:{response[1][0]['content']}\n姓名:{response[1][0]['client_name']}\n性別:{response[1][0]['gender']}\n電子郵件:{response[1][0]['email']}\n聯絡電話:{response[1][0]['phone']}\n"
  266. try:
  267. send_email("mallservice@tfc101.com.tw","mia@choozmo.com",str(email_content)) # mallservice@tfc101.com.tw
  268. except Exception as e:
  269. print(str(e))
  270. return {"state": 200 ,"message": "資料已成功提交"}
  271. except Exception as e:
  272. return {"state": 500 , "message" : str(e)}
  273. # @dbRouter.post("/video_save_into_cache")
  274. # def message_not_in_cache(video_name : Annotated[str, Field(description="檔案請丟進/home/mia/101/static/video_cache/others/資料夾裡")],client_message_id :str = None,question:str = None):
  275. # try:
  276. # data = []
  277. # if client_message_id :
  278. # data, count = supabase.table('client_message').select('*').eq("id",client_message_id).execute()
  279. # elif question:
  280. # data, count = supabase.table('client_message').select('*').eq("question",question).execute()
  281. # info = data[1][0]
  282. # response = supabase.table('video_cache').insert({"question": info["question"],"answer":info["answer"],"video_url":f"/static/video_cache/others/{video_name}"}).execute()
  283. # response = supabase.table('client_message').delete().eq('id', info["id"]).execute()
  284. # return {"state": 200 , "message" : "success"}
  285. # except Exception as e:
  286. # return {"state": 500 , "message" : str(e)}
  287. from sherry.semantic_search import ask_question
  288. @dbRouter.post("/video_cache")
  289. def video_cache(client_message :str,language:str ="ch"):
  290. try:
  291. # data, count = supabase.table('video_cache').select('*').like('question', f'%{client_message}%').execute()
  292. # if len(data[1]) == 0 :
  293. # return {"state": 500 , "message" : "no data"}
  294. # return {"state": 200 , "message" : data[1]}
  295. result = ask_question(client_message,language=language)
  296. # result[0]["answer"]
  297. if result == None :
  298. return {"state": 500 , "message" : "no data"}
  299. # data, count = supabase.table("log_record").insert({"question":client_message, "answer":result[0]["answer"]}).execute()
  300. return {"state": 200 , "message" : result }
  301. except Exception as e:
  302. return {"state": 500 , "message" : str(e)}
  303. # from openai import OpenAI
  304. # import json
  305. # client = OpenAI(
  306. # # This is the default and can be omitted
  307. # api_key=os.environ.get("OPENAI_API_KEY"),
  308. # )
  309. # def access_openai(prompt_value):
  310. # chat_completion = client.chat.completions.create(
  311. # messages=[
  312. # {
  313. # "role": "user",
  314. # "content": f"請將以下的內容翻譯為英文:\n\n {prompt_value}",
  315. # }
  316. # ],
  317. # model="gpt-3.5-turbo",
  318. # )
  319. # return chat_completion.choices[0].message.content
  320. # @dbRouter.post("/translate")
  321. # def translate():
  322. # try:
  323. # response = supabase.table('video_cache').select('*').eq('language', 'ch').execute()
  324. # datas = response.data
  325. # for data in datas :
  326. # translated_question = access_openai(data['question'])
  327. # translated_answer = access_openai(data['answer'])
  328. # print(data['question'])
  329. # print(translated_question)
  330. # insert = supabase.table('client_message').insert({"client_id":"0", "question":translated_question,"answer":translated_answer,"language":"en"}).execute()
  331. # return {"state": 200 }
  332. # except Exception as e:
  333. # return {"state": 500 , "message" : str(e)}