main.py 52 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341
  1. # fastapi
  2. from fastapi import FastAPI, Request, Response, HTTPException, status, Depends , Form
  3. from fastapi import templating
  4. from fastapi.templating import Jinja2Templates
  5. from fastapi.responses import HTMLResponse, RedirectResponse, JSONResponse
  6. from fastapi.middleware.cors import CORSMiddleware
  7. from fastapi.staticfiles import StaticFiles
  8. # fastapi view function parameters
  9. from typing import List, Optional
  10. import json
  11. # path
  12. import sys
  13. from pydantic.errors import ArbitraryTypeError
  14. from sqlalchemy.sql.elements import False_
  15. # time
  16. # import datetime
  17. from datetime import timedelta, datetime
  18. # db
  19. import dataset
  20. from passlib import context
  21. from sqlalchemy.sql.expression import true
  22. import models
  23. from random import randint,uniform
  24. # authorize
  25. from passlib.context import CryptContext
  26. pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
  27. import jwt
  28. from fastapi_jwt_auth import AuthJWT
  29. from fastapi_jwt_auth.exceptions import AuthJWTException
  30. from fastapi.security import OAuth2AuthorizationCodeBearer, OAuth2PasswordRequestForm
  31. import numpy as np
  32. import pymysql
  33. class dateEncode(json.JSONEncoder):
  34. def default(self, obj):
  35. if isinstance(obj, datetime):
  36. return obj.strftime('%Y-%m-%d %H:%M:%S')
  37. else:
  38. return json.JSONEncoder.default(self, obj)
  39. pymysql.install_as_MySQLdb()
  40. db_settings = {
  41. "host": "db.ptt.cx",
  42. "port": 3306,
  43. "user": "choozmo",
  44. "password": "pAssw0rd",
  45. "db": "Water_tower",
  46. "charset": "utf8mb4"
  47. }
  48. # app
  49. app = FastAPI()
  50. app.add_middleware(
  51. CORSMiddleware,
  52. allow_origins=["*"],
  53. allow_credentials=True,
  54. allow_methods=["*"],
  55. allow_headers=["*"],
  56. )
  57. SECRET_KEY = "df2f77bd544240801a048bd4293afd8eeb7fff3cb7050e42c791db4b83ebadcd"
  58. ALGORITHM = "HS256"
  59. ACCESS_TOKEN_EXPIRE_MINUTES = 3000
  60. pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
  61. #
  62. app.mount(path='/templates', app=StaticFiles(directory='templates'), name='templates')
  63. app.mount(path='/static', app=StaticFiles(directory='static'), name='static ')
  64. #
  65. templates = Jinja2Templates(directory='templates')
  66. @AuthJWT.load_config
  67. def get_config():
  68. return models.Settings()
  69. # view
  70. @app.get('/', response_class=HTMLResponse)
  71. async def index(request: Request):
  72. print(request)
  73. return templates.TemplateResponse(name='index.html', context={'request': request})
  74. @app.get('/login', response_class=HTMLResponse)
  75. async def login(request: Request):
  76. return templates.TemplateResponse(name='login.html', context={'request': request})
  77. @app.post("/login")
  78. async def login_for_access_token(request: Request, form_data: OAuth2PasswordRequestForm = Depends(), Authorize: AuthJWT = Depends()):
  79. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  80. user = authenticate_user(form_data.username, form_data.password)
  81. if not user:
  82. raise HTTPException(
  83. status_code=status.HTTP_401_UNAUTHORIZED,
  84. detail="Incorrect username or password",
  85. headers={"WWW-Authenticate": "Bearer"},
  86. )
  87. access_token_expires = timedelta(minutes=ACCESS_TOKEN_EXPIRE_MINUTES)
  88. access_token = create_access_token(
  89. data={"sub": user.username}, expires_delta=access_token_expires
  90. )
  91. table = db['users']
  92. user.token = access_token
  93. print(user)
  94. table.update(dict(user), ['username'],['password'])
  95. access_token = Authorize.create_access_token(subject=user.username)
  96. refresh_token = Authorize.create_refresh_token(subject=user.username)
  97. Authorize.set_access_cookies(access_token)
  98. Authorize.set_refresh_cookies(refresh_token)
  99. #return templates.TemplateResponse("home.html", {"request": request, "msg": 'Login'})
  100. return {"access_token": access_token, "token_type": "bearer"} # 回傳token給前端
  101. @app.get('/register', response_class=HTMLResponse)
  102. async def login(request: Request):
  103. return templates.TemplateResponse(name='rigister_test.html', context={'request': request})
  104. @app.post('/register')
  105. async def register(request: Request, form_data: OAuth2PasswordRequestForm = Depends()):
  106. user = models.User(**await request.form())
  107. print(form_data.username, form_data.password, user)
  108. user.id = randint(1000, 9999)
  109. user.isAdmin = 0 #預設為非管理者
  110. user.roleType = 0 #預設為employee
  111. # 密碼加密
  112. #user.password = get_password_hash(user.password)
  113. # 存入DB
  114. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  115. user_table = db['users']
  116. user_table.insert(dict(user))
  117. # 跳轉頁面至登入
  118. return templates.TemplateResponse(name='login.html', context={'request': request})
  119. @app.post('/record_tower')
  120. async def record_tower(request: Request,data : models.record_tower_data,key:str):
  121. #data = models.tower_data(**await request.form())
  122. if key!="21232f297a57a5a743894a0e4a801fc3":
  123. return {'msg':'no access'}
  124. loc_dt = datetime.today()
  125. loc_dt_format = loc_dt.strftime("%Y-%m-%d %H:%M:%S")
  126. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  127. cmd ="SELECT * FROM device"
  128. cmd2={}
  129. check = False
  130. # for row in db.query(cmd):
  131. # if row['id']== tower_id:
  132. # check = True
  133. if check :
  134. result={'msg':"success"}
  135. else:
  136. result = {'msg':"no device"}
  137. return json.dumps(result,ensure_ascii=False)
  138. @app.post('/record_dcs')
  139. async def record_dcs(request: Request,data : models.record_tower_data,key:str):
  140. #data = models.tower_data(**await request.form())
  141. if key!="21232f297a57a5a743894a0e4a801fc3":
  142. return {'msg':'no access'}
  143. loc_dt = datetime.today()
  144. loc_dt_format = loc_dt.strftime("%Y-%m-%d %H:%M:%S")
  145. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  146. cmd ="SELECT * FROM device"
  147. cmd2={}
  148. check = False
  149. if check :
  150. result={'msg':"success"}
  151. else:
  152. result = {'msg':"no device"}
  153. return json.dumps(result,ensure_ascii=False)
  154. @app.post('/record_diagnosis')
  155. async def record_diagnosis(request: Request,data : models.record_diagnosis_data,key:str):
  156. #data = models.tower_data(**await request.form())
  157. if key!="21232f297a57a5a743894a0e4a801fc3":
  158. return {'msg':'no access'}
  159. loc_dt = datetime.today()
  160. loc_dt_format = loc_dt.strftime("%Y-%m-%d %H:%M:%S")
  161. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  162. dict_tmp = data.__dict__
  163. dict_tmp['createTime']=loc_dt_format
  164. result = db['record_diagnosis'].update(dict(dict_tmp),['vibration_id'])
  165. #print(result)
  166. if result :
  167. result={'msg':'success insert'}
  168. else :
  169. result={'msg':'fail to insert'}
  170. return json.dumps(result,ensure_ascii=False)
  171. @app.post('/record_health')
  172. async def record_health(request: Request,data : models.record_health_data,key:str):
  173. #data = models.tower_data(**await request.form())
  174. if key!="21232f297a57a5a743894a0e4a801fc3":
  175. return {'msg':'no access'}
  176. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  177. dict_tmp = data.__dict__
  178. result = db['record_health'].insert(dict(dict_tmp))
  179. #print(result)
  180. if result :
  181. result={'msg':'success insert'}
  182. else :
  183. result={'msg':'fail to insert'}
  184. return json.dumps(result,ensure_ascii=False)
  185. @app.post('/record_performance')
  186. async def record_performance(request: Request,data : models.record_performance_data,key:str):
  187. #data = models.tower_data(**await request.form())
  188. if key!="21232f297a57a5a743894a0e4a801fc3":
  189. return {'msg':'no access'}
  190. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  191. dict_tmp = data.__dict__
  192. result = db['record_performance'].insert(dict(dict_tmp))
  193. #print(result)
  194. if result :
  195. result={'msg':'success insert'}
  196. else :
  197. result={'msg':'fail to insert'}
  198. return json.dumps(result,ensure_ascii=False)
  199. @app.post('/record_prediction')
  200. async def record_prediction(request: Request,data : models.record_prediction_data,key:str):
  201. #data = models.tower_data(**await request.form())
  202. if key!="21232f297a57a5a743894a0e4a801fc3":
  203. return {'msg':'no access'}
  204. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  205. dict_tmp = data.__dict__
  206. result = db['record_prediction'].insert(dict(dict_tmp))
  207. #print(result)
  208. if result :
  209. result={'msg':'success insert'}
  210. else :
  211. result={'msg':'fail to insert'}
  212. return json.dumps(result,ensure_ascii=False)
  213. @app.post('/record_prediction_upd')
  214. async def record_prediction(request: Request,data : models.record_prediction_upd_data,key:str):
  215. #data = models.tower_data(**await request.form())
  216. if key!="21232f297a57a5a743894a0e4a801fc3":
  217. return {'msg':'no access'}
  218. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  219. dict_tmp = data.__dict__
  220. result = db['record_prediction_upd'].insert(dict(dict_tmp))
  221. #print(result)
  222. if result :
  223. result={'msg':'success insert'}
  224. else :
  225. result={'msg':'fail to insert'}
  226. return json.dumps(result,ensure_ascii=False)
  227. @app.get('/add_tower')
  228. async def home(request: Request, Authorize: AuthJWT = Depends()):
  229. return templates.TemplateResponse(name='add_tower.html', context={'request': request})
  230. @app.post('/add_tower')
  231. async def home(request: Request, Authorize: AuthJWT = Depends()):
  232. data=request.form()
  233. print(data.device)
  234. @app.get('/home', response_class=HTMLResponse)
  235. async def home(request: Request, Authorize: AuthJWT = Depends()):
  236. try:
  237. Authorize.jwt_required()
  238. except Exception as e:
  239. print(e)
  240. return RedirectResponse('/login')
  241. #add_data()
  242. return templates.TemplateResponse(name='home.html', context={'request': request})
  243. @app.get('/home/show', response_class=HTMLResponse)
  244. async def home(request: Request, Authorize: AuthJWT = Depends()):
  245. try:
  246. Authorize.jwt_required()
  247. except Exception as e:
  248. print(e)
  249. return RedirectResponse('/login')
  250. current_user = Authorize.get_jwt_subject()
  251. result = [{'user_role':check_role_type(current_user)}]
  252. result.append(check_tower_health(current_user))
  253. #print(result)
  254. return json.dumps(result,ensure_ascii=False)
  255. @app.get('/org', response_class=HTMLResponse)
  256. async def tower(request: Request, Authorize: AuthJWT = Depends()):
  257. try:
  258. Authorize.jwt_required()
  259. except Exception as e:
  260. print(e)
  261. return RedirectResponse('/login')
  262. current_user = Authorize.get_jwt_subject()
  263. result = get_user_under_organization(current_user)
  264. return json.dumps(result,ensure_ascii=False)
  265. @app.get('/user_role', response_class=HTMLResponse)
  266. async def user_role(request: Request, Authorize: AuthJWT = Depends()):
  267. try:
  268. Authorize.jwt_required()
  269. except Exception as e:
  270. print(e)
  271. return RedirectResponse('/login')
  272. current_user = Authorize.get_jwt_subject()
  273. result = {'role':check_role_type(current_user)}
  274. print(result)
  275. return json.dumps(result,ensure_ascii=False)
  276. @app.get('/tower', response_class=HTMLResponse)
  277. async def tower(request: Request, Authorize: AuthJWT = Depends()):
  278. try:
  279. Authorize.jwt_required()
  280. except Exception as e:
  281. print(e)
  282. return RedirectResponse('/login')
  283. current_user = Authorize.get_jwt_subject()
  284. result = get_user_under_organization(current_user)
  285. result.append({'Data' : get_tower_info('dev001')})
  286. return templates.TemplateResponse(name='tower.html', context={"request":request})
  287. @app.get('/tower/org', response_class=HTMLResponse)
  288. async def tower(request: Request, Authorize: AuthJWT = Depends()):
  289. try:
  290. Authorize.jwt_required()
  291. except Exception as e:
  292. print(e)
  293. return RedirectResponse('/login')
  294. current_user = Authorize.get_jwt_subject()
  295. result = get_user_under_organization(current_user)
  296. return json.dumps(result,ensure_ascii=False)
  297. @app.get('/tower/', response_class=HTMLResponse)
  298. async def tower(request: Request,company:str,factory:str,department:str,towerGroup:str, Authorize: AuthJWT = Depends()):
  299. try:
  300. Authorize.jwt_required()
  301. except Exception as e:
  302. print(e)
  303. return RedirectResponse('/login')
  304. #current_user = Authorize.get_jwt_subject()
  305. tower_arr = get_tower(company,factory,department,towerGroup)
  306. result = []
  307. for tower in tower_arr:
  308. result.append({'tower_name': tower,'tower_data': get_tower_info(tower)})
  309. return json.dumps(result,ensure_ascii=False, cls = dateEncode)
  310. @app.get('/tower/performance/{tower_id}', response_class=HTMLResponse)
  311. async def member_authority(request:Request,tower_id: str,Authorize: AuthJWT = Depends()):
  312. """設定成員權限"""
  313. try:
  314. Authorize.jwt_required()
  315. except Exception as e:
  316. print(e)
  317. return RedirectResponse('/login')
  318. result = get_tower_perform(tower_id)
  319. #print(result)
  320. return json.dumps(result,ensure_ascii=False, cls = dateEncode)
  321. @app.get('/optim', response_class=HTMLResponse)
  322. async def optim(request: Request, Authorize: AuthJWT = Depends()):
  323. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  324. statement = 'SELECT value FROM record_tower WHERE record_tower.key = "hotTemp"'
  325. x = 0
  326. y = 0
  327. z = 0
  328. count = 0
  329. waterflow = 0
  330. fannum = 0
  331. savewater = 0
  332. fanchange = 0
  333. saveelec = 0
  334. total = 0
  335. flowchange = 0
  336. try:
  337. Authorize.jwt_required()
  338. except Exception as e:
  339. print(e)
  340. return RedirectResponse('/login')
  341. current_user = Authorize.get_jwt_subject()
  342. print(check_role_type(current_user))
  343. role = int(check_role_type(current_user))
  344. print(check_role_type(current_user))
  345. if role == 1:
  346. statement = 'SELECT value FROM record_tower WHERE record_tower.key = "hotTemp"'
  347. for temp in db.query(statement):
  348. print(temp['value'])
  349. x=temp['value']
  350. statement2 = 'SELECT value FROM record_tower WHERE record_tower.key = "coldTempData1"'
  351. for temp2 in db.query(statement2):
  352. print(temp2['value'])
  353. y=temp2['value']
  354. statement3 = 'SELECT value FROM record_tower WHERE record_tower.key = "wetTemp"'
  355. for temp3 in db.query(statement3):
  356. print(temp3['value'])
  357. z=temp3['value']
  358. statement4 = 'SELECT value FROM record_tower WHERE record_tower.key = "count"'
  359. for tower in db.query(statement4):
  360. print(tower['value'])
  361. count=tower['value']
  362. statement5 = 'SELECT value FROM record_tower WHERE record_tower.key = "waterflow"'
  363. for tower in db.query(statement5):
  364. waterflow=tower['value']
  365. statement6 = 'SELECT value FROM record_tower WHERE record_tower.key = "fannum"'
  366. for tower in db.query(statement6):
  367. fannum=tower['value']
  368. statement7 = 'SELECT value FROM record_tower WHERE record_tower.key = "flowchange"'
  369. for tower in db.query(statement7):
  370. flowchange=tower['value']
  371. statement8 = 'SELECT value FROM record_tower WHERE record_tower.key = "savewater"'
  372. for tower in db.query(statement8):
  373. savewater=tower['value']
  374. statement9 = 'SELECT value FROM record_tower WHERE record_tower.key = "fanchange"'
  375. for tower in db.query(statement9):
  376. fanchange=tower['value']
  377. statement10 = 'SELECT value FROM record_tower WHERE record_tower.key = "saveelec"'
  378. for tower in db.query(statement10):
  379. saveelec=tower['value']
  380. statement11 = 'SELECT value FROM record_tower WHERE record_tower.key = "total"'
  381. for tower in db.query(statement11):
  382. total=tower['value']
  383. elif role == 3 :
  384. statement = 'SELECT value FROM record_tower WHERE record_tower.key = "hotTemp1"'
  385. for temp in db.query(statement):
  386. print(temp['value'])
  387. x=temp['value']
  388. statement2 = 'SELECT value FROM record_tower WHERE record_tower.key = "coldTempData2"'
  389. for temp2 in db.query(statement2):
  390. print(temp2['value'])
  391. y=temp2['value']
  392. statement3 = 'SELECT value FROM record_tower WHERE record_tower.key = "wetTemp1"'
  393. for temp3 in db.query(statement3):
  394. print(temp3['value'])
  395. z=temp3['value']
  396. statement4 = 'SELECT value FROM record_tower WHERE record_tower.key = "count1"'
  397. for tower in db.query(statement4):
  398. print(tower['value'])
  399. count=tower['value']
  400. statement5 = 'SELECT value FROM record_tower WHERE record_tower.key = "waterflow1"'
  401. for tower in db.query(statement5):
  402. waterflow=tower['value']
  403. statement6 = 'SELECT value FROM record_tower WHERE record_tower.key = "fannum1"'
  404. for tower in db.query(statement6):
  405. fannum=tower['value']
  406. statement7 = 'SELECT value FROM record_tower WHERE record_tower.key = "flowchange1"'
  407. for tower in db.query(statement7):
  408. flowchange=tower['value']
  409. statement8 = 'SELECT value FROM record_tower WHERE record_tower.key = "savewater1"'
  410. for tower in db.query(statement8):
  411. savewater=tower['value']
  412. statement9 = 'SELECT value FROM record_tower WHERE record_tower.key = "fanchange1"'
  413. for tower in db.query(statement9):
  414. fanchange=tower['value']
  415. statement10 = 'SELECT value FROM record_tower WHERE record_tower.key = "saveelec1"'
  416. for tower in db.query(statement10):
  417. saveelec=tower['value']
  418. statement11 = 'SELECT value FROM record_tower WHERE record_tower.key = "total1"'
  419. for tower in db.query(statement11):
  420. total=tower['value']
  421. elif role == 4 :
  422. statement = 'SELECT value FROM record_tower WHERE record_tower.key = "hotTemp2"'
  423. for temp in db.query(statement):
  424. print(temp['value'])
  425. x=temp['value']
  426. statement2 = 'SELECT value FROM record_tower WHERE record_tower.key = "coldTempData3"'
  427. for temp2 in db.query(statement2):
  428. print(temp2['value'])
  429. y=temp2['value']
  430. statement3 = 'SELECT value FROM record_tower WHERE record_tower.key = "wetTemp2"'
  431. for temp3 in db.query(statement3):
  432. print(temp3['value'])
  433. z=temp3['value']
  434. statement4 = 'SELECT value FROM record_tower WHERE record_tower.key = "count2"'
  435. for tower in db.query(statement4):
  436. print(tower['value'])
  437. count=tower['value']
  438. statement5 = 'SELECT value FROM record_tower WHERE record_tower.key = "waterflow2"'
  439. for tower in db.query(statement5):
  440. waterflow=tower['value']
  441. statement6 = 'SELECT value FROM record_tower WHERE record_tower.key = "fannum2"'
  442. for tower in db.query(statement6):
  443. fannum=tower['value']
  444. statement7 = 'SELECT value FROM record_tower WHERE record_tower.key = "flowchange2"'
  445. for tower in db.query(statement7):
  446. flowchange=tower['value']
  447. statement8 = 'SELECT value FROM record_tower WHERE record_tower.key = "savewater2"'
  448. for tower in db.query(statement8):
  449. savewater=tower['value']
  450. statement9 = 'SELECT value FROM record_tower WHERE record_tower.key = "fanchange2"'
  451. for tower in db.query(statement9):
  452. fanchange=tower['value']
  453. statement10 = 'SELECT value FROM record_tower WHERE record_tower.key = "saveelec2"'
  454. for tower in db.query(statement10):
  455. saveelec=tower['value']
  456. statement11 = 'SELECT value FROM record_tower WHERE record_tower.key = "total2"'
  457. for tower in db.query(statement11):
  458. total=tower['value']
  459. else :
  460. print("noright")
  461. return templates.TemplateResponse(name='optim.html',context=
  462. {
  463. 'request': request,"x":x,"y":y,"z":z,"count":count,"waterflow":waterflow
  464. ,"fannum":fannum , "flowchange":flowchange , "savewater":savewater
  465. , "fanchange":fanchange , "saveelec":saveelec , "total":total
  466. })
  467. @app.get('/vibration', response_class=HTMLResponse)
  468. async def vibration(request: Request, Authorize: AuthJWT = Depends()):
  469. try:
  470. Authorize.jwt_required()
  471. except Exception as e:
  472. print(e)
  473. return RedirectResponse('/login')
  474. return templates.TemplateResponse(name='vibration_test.html', context={'request': request})
  475. @app.get('/channel', response_class=HTMLResponse)
  476. async def vibration(request: Request, Authorize: AuthJWT = Depends()):
  477. try:
  478. Authorize.jwt_required()
  479. except Exception as e:
  480. print(e)
  481. return RedirectResponse('/login')
  482. return templates.TemplateResponse(name='channel.html', context={'request': request})
  483. @app.get('/channel/{tower_id}/{channel_id}', response_class=HTMLResponse)
  484. async def vibration(request: Request,tower_id:str,channel_id:str,Authorize: AuthJWT = Depends()):
  485. try:
  486. Authorize.jwt_required()
  487. except Exception as e:
  488. print(e)
  489. return RedirectResponse('/login')
  490. print(find_vibration_id(tower_id,channel_id))
  491. result = get_channel_info(find_vibration_id(tower_id,channel_id))
  492. return json.dumps(result,ensure_ascii=False, cls = dateEncode)
  493. @app.get('/channel_chart/{tower_id}/{channel_id}', response_class=HTMLResponse)
  494. async def vibration(request: Request,tower_id:str,channel_id:str,Authorize: AuthJWT = Depends()):
  495. try:
  496. Authorize.jwt_required()
  497. except Exception as e:
  498. print(e)
  499. return RedirectResponse('/login')
  500. print(find_vibration_id(tower_id,channel_id))
  501. result = get_channel_health(find_vibration_id(tower_id,channel_id))
  502. return json.dumps(result,ensure_ascii=False, cls = dateEncode)
  503. @app.get('/channel_predict/{tower_id}/{channel_id}', response_class=HTMLResponse)
  504. async def vibration(request: Request,tower_id:str,channel_id:str,Authorize: AuthJWT = Depends()):
  505. try:
  506. Authorize.jwt_required()
  507. except Exception as e:
  508. print(e)
  509. return RedirectResponse('/login')
  510. #print(find_vibration_id(tower_id,channel_id))
  511. result = get_predect_data(find_vibration_id(tower_id,channel_id))
  512. return json.dumps(result,ensure_ascii=False, cls = dateEncode)
  513. @app.get('/channel_frequency/{tower_id}/{channel_id}', response_class=HTMLResponse)
  514. async def vibration(request: Request,tower_id:str,channel_id:str,Authorize: AuthJWT = Depends()):
  515. try:
  516. Authorize.jwt_required()
  517. except Exception as e:
  518. print(e)
  519. return RedirectResponse('/login')
  520. #print(find_vibration_id(tower_id,channel_id))
  521. result = get_frequency(find_vibration_id(tower_id,channel_id))
  522. return json.dumps(result,ensure_ascii=False, cls = dateEncode)
  523. @app.get('/history', response_class=HTMLResponse)
  524. async def history(request: Request, Authorize: AuthJWT = Depends()):
  525. try:
  526. Authorize.jwt_required()
  527. except Exception as e:
  528. print(e)
  529. return RedirectResponse('/login')
  530. # current_user = Authorize.get_jwt_subject()
  531. return templates.TemplateResponse(name='history.html', context={'request': request})
  532. @app.get('/device', response_class=HTMLResponse)
  533. async def device(request: Request, Authorize: AuthJWT = Depends()):
  534. try:
  535. Authorize.jwt_required()
  536. except Exception as e:
  537. print(e)
  538. return RedirectResponse('/login')
  539. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  540. statement = 'SELECT * FROM device '
  541. a = []
  542. print("start")
  543. for row in db.query(statement):
  544. print(row['id'],row['deviceName'],row['hostIP'],row['CompanyCode'],row['FactoryCode'],row['DepartmentCode'])
  545. a.append({row['id'],row['deviceName'],row['hostIP']})
  546. a.append({row['CompanyCode'],row['FactoryCode'],row['DepartmentCode']})
  547. print(a)
  548. print("over3")
  549. #result = json.dumps(b,ensure_ascii=False)
  550. # current_user = Authorize.get_jwt_subject()
  551. return templates.TemplateResponse(name='device.html', context={'request': request,'a':a})
  552. @app.get('/system', response_class=HTMLResponse)
  553. async def system(request: Request, Authorize: AuthJWT = Depends()):
  554. try:
  555. Authorize.jwt_required()
  556. except Exception as e:
  557. print(e)
  558. return RedirectResponse('/login')
  559. # current_user = Authorize.get_jwt_subject()
  560. return templates.TemplateResponse(name='system.html', context={'request': request})
  561. @app.get('/member', response_class=HTMLResponse)
  562. async def get_member(request: Request, Authorize: AuthJWT = Depends()):
  563. """獲取所有帳號資訊"""
  564. try:
  565. Authorize.jwt_required()
  566. except Exception as e:
  567. print(e)
  568. return RedirectResponse('/login')
  569. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  570. statement = 'SELECT id,username,isAdmin FROM users'
  571. json_dic = []
  572. for row in db.query(statement):
  573. #print(row['id'],row['username'])
  574. json_dic.append({'username':row['username'],'isAdmin':row['isAdmin'],'roleType':check_role_type(row['username']),'role_name' :get_role_name(check_role_type(row['username']))})
  575. result = json.dumps(json_dic,ensure_ascii=False)
  576. return result
  577. @app.get('/member/edit/', response_class=HTMLResponse)
  578. async def login(request: Request, name:str,isAdmin:int,isEnable:int ,Authorize: AuthJWT = Depends()):
  579. try:
  580. Authorize.jwt_required()
  581. except Exception as e:
  582. print(e)
  583. return RedirectResponse('/login')
  584. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  585. current_user = Authorize.get_jwt_subject()
  586. current_user_roleType = check_role_type(current_user)
  587. del_user_roleType = check_role_type(name)
  588. statement = 'SELECT isAdmin FROM users WHERE userName = "'+current_user+'"'
  589. for row in db.query(statement):
  590. if row['isAdmin']!=1:
  591. return json.dumps([{'msg':'你沒有權限'}],ensure_ascii=False)
  592. if del_user_roleType == None:
  593. return json.dumps([{'msg':'不存在使用者'}],ensure_ascii=False)
  594. elif current_user_roleType>del_user_roleType or current_user_roleType==del_user_roleType:
  595. return json.dumps([{'msg':'你沒有權限'}],ensure_ascii=False)
  596. user_dic = get_user(name)
  597. print(user_dic)
  598. user_dic.isAdmin = isAdmin
  599. user_dic.isEnable = isEnable
  600. table = db['users']
  601. table.update(dict(user_dic), ['username'])
  602. return json.dumps([{'msg':"成功更改"}],ensure_ascii=False)
  603. @app.get('/member_delete', response_class=HTMLResponse)
  604. async def login(request: Request, Authorize: AuthJWT = Depends()):
  605. try:
  606. Authorize.jwt_required()
  607. except Exception as e:
  608. print(e)
  609. return RedirectResponse('/login')
  610. return templates.TemplateResponse(name='delete_member_test2.html', context={'request': request})
  611. @app.post('/member_delete')
  612. async def delete_member(request: Request):
  613. """刪除成員"""
  614. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  615. del_user = models.del_user(**await request.form())
  616. delete_name = del_user.del_name
  617. statement = 'SELECT * FROM users'
  618. current_user = ''
  619. for row in db.query(statement):
  620. if row['token'] != None :
  621. if compare_jwt_token(row['token'],del_user.access_token):
  622. current_user = row['username']
  623. if current_user == '':
  624. return {'msg':'尚未登入'}
  625. statement = 'SELECT isAdmin FROM users WHERE userName = "'+current_user+'"'
  626. for row in db.query(statement):
  627. if row['isAdmin']!=1:
  628. return {'msg': ' 你沒有權限'}
  629. current_user_roleType = check_role_type(current_user)
  630. del_user_roleType = check_role_type(delete_name)
  631. if del_user_roleType == None:
  632. return {'msg':'不存在使用者'}
  633. elif current_user_roleType>del_user_roleType or current_user_roleType==del_user_roleType:
  634. return {'msg': ' 你沒有權限'}
  635. else :
  636. table = db['users']
  637. table.delete(username=delete_name)
  638. return {'msg': ' 成功刪除'}
  639. @app.get('/member_authority/{edit_one}', response_class=HTMLResponse)
  640. async def member_authority(request:Request,edit_one: int,Authorize: AuthJWT = Depends()):
  641. """設定成員權限"""
  642. try:
  643. Authorize.jwt_required()
  644. except Exception as e:
  645. print(e)
  646. return RedirectResponse('/login')
  647. context = {'request': request}
  648. current_user = Authorize.get_jwt_subject()
  649. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  650. statement = check_isAdmin(current_user)
  651. if statement == "no user":
  652. return templates.TemplateResponse(name='notice.html', context={"request":request,'msg':'no user' })
  653. elif statement == 0:
  654. return templates.TemplateResponse(name='notice.html', context={"request":request,'msg':"沒有權限" })
  655. current_user_roleType = check_role_type(current_user)
  656. if edit_one == None:
  657. return templates.TemplateResponse(name='notice.html', context={"request":request,'msg':'no role' })
  658. elif int(current_user_roleType)>int(edit_one) or int(current_user_roleType)==int(edit_one):
  659. return templates.TemplateResponse(name='notice.html', context={"request":request,'msg':"沒有權限" })
  660. result = check_role_acl(edit_one)
  661. if result == []:
  662. cmd = 'SELECT id FROM module'
  663. for row in db.query(cmd):
  664. dic_tmp = {'id':0,'isView':0,'isAdd':0 ,'isEdit':0,'isDel':0,'role_id' : edit_one}
  665. context[get_modul_name(row['id']) ] = dic_tmp
  666. else:
  667. for dic in result:
  668. modul_name = get_modul_name(dic['module_id'])
  669. del dic['module_id']
  670. context[modul_name ] = dic
  671. return templates.TemplateResponse(name='member_authority_test.html', context=context)
  672. @app.post('/member_authority')
  673. async def member_authority(request: Request):
  674. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  675. edit_one = models.user_authority(**await request.form())
  676. statement = 'SELECT * FROM users'
  677. current_user = ''
  678. for row in db.query(statement):
  679. if row['token'] != None :
  680. if compare_jwt_token(row['token'],edit_one.access_token):
  681. current_user = row['username']
  682. if current_user == '':
  683. return templates.TemplateResponse(name='notice.html', context={"request":request,'msg':'尚未登入'})
  684. statement = check_isAdmin(current_user)
  685. if statement == "no user":
  686. return templates.TemplateResponse(name='notice.html', context={"request":request,'msg':statement })
  687. elif statement == 0:
  688. return templates.TemplateResponse(name='notice.html', context={"request":request,'msg':'你沒有權限' })
  689. current_user_roleType = check_role_type(current_user)
  690. edit_one_roleType = edit_one.role_id
  691. if current_user_roleType>edit_one_roleType or current_user_roleType==edit_one_roleType:
  692. return templates.TemplateResponse(name='notice.html', context={"request":request,'msg': ' 你沒有權限'})
  693. else :
  694. row = ['ai_prediction' ,'channel' ,'device', 'event', 'index' ,'performance', 'record', 'setting_device' ,'setting_system','tower']
  695. if check_role_acl(edit_one.role_id) == []:
  696. for module in row :
  697. new_dict = edit_one.get_acl_from_module_name(module)
  698. new_dict["id"]= None
  699. table = db['role_acl']
  700. table.insert(new_dict)
  701. else:
  702. for module in row :
  703. new_dict = edit_one.get_acl_from_module_name(module)
  704. table = db['role_acl']
  705. table.update(new_dict, ['id'])
  706. return templates.TemplateResponse(name='notice.html', context={"request":request,'msg': '成功更改權限'})
  707. # 溫度API
  708. @app.get('/temperature')
  709. async def get_temperatures():
  710. """ 撈DB溫度 """
  711. return {'hot_water': 30.48, 'cold_water': 28.10, 'wet_ball': 25.14}
  712. @app.post("/example")
  713. async def example(request: Request,Authorize: AuthJWT = Depends()):
  714. try:
  715. Authorize.jwt_required()
  716. except Exception as e:
  717. print(e)
  718. current_user = Authorize.get_jwt_subject()
  719. #form_data = await request.form()
  720. print( current_user)
  721. return current_user
  722. @app.post('/user')
  723. def user(Authorize: AuthJWT = Depends()):
  724. Authorize.jwt_required()
  725. current_user = Authorize.get_jwt_subject()
  726. return {"user": current_user}
  727. @app.get("/add_data", response_class=HTMLResponse)
  728. async def example(request: Request,Authorize: AuthJWT = Depends()):
  729. try:
  730. Authorize.jwt_required()
  731. except Exception as e:
  732. print(e)
  733. return RedirectResponse('/login')
  734. add_data()
  735. return templates.TemplateResponse(name='test.html', context={'request': request})
  736. @app.get('/health')
  737. async def get_health(date: str):
  738. """ 撈健康指標、預設健康指標 """
  739. date = str(datetime.strptime(date, "%Y-%m-%d"))[:10]
  740. print(date)
  741. print(str(datetime.today()))
  742. print(str(datetime.today()-timedelta(days=1)))
  743. fake_data = {
  744. str(datetime.today())[:10]: {'curr_health': 0.7, 'pred_health': 0.8},
  745. str(datetime.today()-timedelta(days=1))[:10]: {'curr_health': 0.6, 'pred_health': 0.7},
  746. }
  747. return fake_data[date]
  748. @app.get('/history_data')
  749. async def get_history(time_end: str):
  750. """ 透過終點時間,抓取歷史資料。 """
  751. date = str(datetime.strptime(time_end, "%Y-%m-%d"))[:10]
  752. print(date)
  753. print(str(datetime.today()))
  754. print(str(datetime.today()-timedelta(days=1)))
  755. fake_data = {
  756. str(datetime.today())[:10]: {
  757. 'curr_history': {
  758. 'RPM_1X': list(np.random.rand(13)),
  759. 'RPM_2X': list(np.random.rand(13)),
  760. 'RPM_3X': list(np.random.rand(13)),
  761. 'RPM_4X': list(np.random.rand(13)),
  762. 'RPM_5X': list(np.random.rand(13)),
  763. 'RPM_6X': list(np.random.rand(13)),
  764. 'RPM_7X': list(np.random.rand(13)),
  765. 'RPM_8X': list(np.random.rand(13)),
  766. 'Gear_1X': list(np.random.rand(13)),
  767. 'Gear_2X': list(np.random.rand(13)),
  768. 'Gear_3X': list(np.random.rand(13)),
  769. 'Gear_4X': list(np.random.rand(13)),
  770. },
  771. 'past_history': {
  772. 'RPM_1X': list(np.random.rand(13)),
  773. 'RPM_2X': list(np.random.rand(13)),
  774. 'RPM_3X': list(np.random.rand(13)),
  775. 'RPM_4X': list(np.random.rand(13)),
  776. 'RPM_5X': list(np.random.rand(13)),
  777. 'RPM_6X': list(np.random.rand(13)),
  778. 'RPM_7X': list(np.random.rand(13)),
  779. 'RPM_8X': list(np.random.rand(13)),
  780. 'Gear_1X': list(np.random.rand(13)),
  781. 'Gear_2X': list(np.random.rand(13)),
  782. 'Gear_3X': list(np.random.rand(13)),
  783. 'Gear_4X': list(np.random.rand(13)),
  784. }
  785. },
  786. str(datetime.today()-timedelta(days=1))[:10]: {
  787. 'curr_history': {
  788. 'RPM_1X': list(np.random.rand(13)),
  789. 'RPM_2X': list(np.random.rand(13)),
  790. 'RPM_3X': list(np.random.rand(13)),
  791. 'RPM_4X': list(np.random.rand(13)),
  792. 'RPM_5X': list(np.random.rand(13)),
  793. 'RPM_6X': list(np.random.rand(13)),
  794. 'RPM_7X': list(np.random.rand(13)),
  795. 'RPM_8X': list(np.random.rand(13)),
  796. 'Gear_1X': list(np.random.rand(13)),
  797. 'Gear_2X': list(np.random.rand(13)),
  798. 'Gear_3X': list(np.random.rand(13)),
  799. 'Gear_4X': list(np.random.rand(13)),
  800. },
  801. 'past_history': {
  802. 'RPM_1X': list(np.random.rand(13)),
  803. 'RPM_2X': list(np.random.rand(13)),
  804. 'RPM_3X': list(np.random.rand(13)),
  805. 'RPM_4X': list(np.random.rand(13)),
  806. 'RPM_5X': list(np.random.rand(13)),
  807. 'RPM_6X': list(np.random.rand(13)),
  808. 'RPM_7X': list(np.random.rand(13)),
  809. 'RPM_8X': list(np.random.rand(13)),
  810. 'Gear_1X': list(np.random.rand(13)),
  811. 'Gear_2X': list(np.random.rand(13)),
  812. 'Gear_3X': list(np.random.rand(13)),
  813. 'Gear_4X': list(np.random.rand(13)),
  814. }
  815. },
  816. }
  817. return fake_data[date]
  818. # Login funtion part
  819. def check_user_exists(username):
  820. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  821. if int(next(iter(db.query('SELECT COUNT(*) FROM Water_tower.users WHERE userName = "'+username+'"')))['COUNT(*)']) > 0:
  822. return True
  823. else:
  824. return False
  825. def get_user(username: str):
  826. """ 取得使用者資訊(Model) """
  827. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  828. if not check_user_exists(username): # if user don't exist
  829. return False
  830. user_dict = next(
  831. iter(db.query('SELECT * FROM Water_tower.users where userName ="'+username+'"')))
  832. user = models.User(**user_dict)
  833. return user
  834. def user_register(user):
  835. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  836. table = db['users']
  837. #user.password = get_password_hash(user.password)
  838. table.insert(dict(user))
  839. def get_password_hash(password):
  840. """ 加密密碼 """
  841. return pwd_context.hash(password)
  842. def verify_password(plain_password, hashed_password):
  843. """ 驗證密碼(hashed) """
  844. return pwd_context.verify(plain_password, hashed_password)
  845. def authenticate_user(username: str, password: str):
  846. """ 連線DB,讀取使用者是否存在。 """
  847. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  848. if not check_user_exists(username): # if user don't exist
  849. return False
  850. if not check_user_isEnable(username):
  851. return False
  852. user_dict = next(iter(db.query('SELECT * FROM Water_tower.users where userName ="'+username+'"')))
  853. user = models.User(**user_dict)
  854. #if not verify_password(password, user.password):
  855. #return False
  856. return user
  857. def create_access_token(data: dict, expires_delta: Optional[timedelta] = None):
  858. """ 創建token,並設定過期時間。 """
  859. to_encode = data.copy()
  860. if expires_delta:
  861. expire = datetime.utcnow() + expires_delta
  862. else:
  863. expire = datetime.utcnow() + timedelta(minutes=15)
  864. to_encode.update({"exp": expire})
  865. encoded_jwt = jwt.encode(to_encode, SECRET_KEY, algorithm=ALGORITHM)
  866. return encoded_jwt
  867. def compare_jwt_token(access_token: str, token: str):
  868. """比對jwt token"""
  869. if len(access_token) < len(token):
  870. if access_token in token:
  871. return True
  872. else :
  873. return False
  874. elif len(access_token) > len(token):
  875. if token in access_token:
  876. return True
  877. else :
  878. return False
  879. else :
  880. if token == access_token:
  881. return True
  882. else :
  883. return False
  884. def check_isAdmin(user_name:str):
  885. """查看是否為管理員"""
  886. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  887. isAdmin = None
  888. cmd = 'SELECT isAdmin FROM users WHERE userName = "'+user_name+'"'
  889. for row in db.query(cmd) :
  890. isAdmin = row['isAdmin']
  891. if isAdmin== None:
  892. return "no user"
  893. return isAdmin
  894. def check_role_type(user_name:str)->int:
  895. """查看使用者權限"""
  896. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  897. cmd = 'SELECT user_role.role_id FROM `users` JOIN `user_role` ON `users`.id = `user_role`.user_id where `users`.username = "'+user_name+'"'
  898. role_type = None
  899. for row in db.query(cmd) :
  900. role_type = row['role_id']
  901. return role_type
  902. def check_role_acl(role:int):
  903. """查看權限"""
  904. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  905. cmd = 'SELECT * FROM role_acl where role_id = '+str(role)
  906. result = []
  907. for row in db.query(cmd) :
  908. dic ={}
  909. for col_name in db['role_acl'].columns:
  910. dic[col_name] = row[col_name]
  911. if dic != {}:
  912. result.append(dic)
  913. return result
  914. def get_role_name(role_id:int):
  915. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  916. cmd = 'SELECT * FROM role where id = '+str(role_id)
  917. role:str
  918. for row in db.query(cmd) :
  919. role = row['name']
  920. return role
  921. def check_user_isEnable(user_name:str)->bool:
  922. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  923. cmd = 'SELECT isEnable FROM users where username = "'+str(user_name)+'"'
  924. able:bool
  925. for row in db.query(cmd) :
  926. able = row['isEnable']
  927. return able
  928. def get_user_under_organization(user_name:str):
  929. """查看所屬公司"""
  930. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  931. user_role = check_role_type(user_name)
  932. print(user_name,user_role)
  933. cmd = 'SELECT * FROM organization'
  934. result = []
  935. if int(user_role) == 1 :
  936. for row in db.query(cmd) :
  937. company = row['Company']
  938. factory = row['Factory']
  939. department = row['Department']
  940. cmd2 = 'SELECT TowerGroupCode FROM device WHERE CompanyCode = "' + company + '" AND FactoryCode = "' + factory + '" AND DepartmentCode = "' + department + '"'
  941. group = []
  942. for row2 in db.query(cmd2):
  943. if row2['TowerGroupCode'] not in group :
  944. group.append(row2['TowerGroupCode'])
  945. result.append({'company':company,'factory':factory,'department':department,'group':group,'able':1})
  946. elif int(user_role) == 2:
  947. cmd2 = 'SELECT company FROM user WHERE user.username ="'+user_name +'"'
  948. company_able:str
  949. for row in db.query(cmd2) :
  950. company_able = row['company']
  951. for row in db.query(cmd) :
  952. company = row['Company']
  953. factory = row['Factory']
  954. department = row['Department']
  955. cmd3 = 'SELECT TowerGroupCode FROM device WHERE CompanyCode = "' + company + '" AND FactoryCode = "' + factory + '" AND DepartmentCode = "' + department + '"'
  956. group = []
  957. for row2 in db.query(cmd3):
  958. if row2['TowerGroupCode'] not in group :
  959. group.append(row2['TowerGroupCode'])
  960. if company == company_able:
  961. result.append({'company':company,'factory':factory,'department':department,'group':group,'able':1})
  962. else:
  963. result.append({'company':company,'factory':factory,'department':department,'group':group,'able':0})
  964. elif int(user_role) == 3:
  965. cmd2 = 'SELECT company,factory FROM users WHERE users.username = "'+user_name +'"'
  966. company_able:str
  967. factory_able:str
  968. num = 0
  969. for row in db.query(cmd2) :
  970. company_able = row['company']
  971. factory_able = row['factory']
  972. for row in db.query(cmd) :
  973. company = row['Company']
  974. factory = row['Factory']
  975. department = row['Department']
  976. cmd3 = 'SELECT TowerGroupCode FROM device WHERE CompanyCode = "' + company + '" AND FactoryCode = "' + factory + '" AND DepartmentCode = "' + department + '"'
  977. group = []
  978. for row2 in db.query(cmd3):
  979. if row2['TowerGroupCode'] not in group :
  980. group.append(row2['TowerGroupCode'])
  981. if company == company_able and factory==factory_able:
  982. result.append({'company':company,'factory':factory,'department':department,'group':group,'able':1})
  983. else:
  984. result.append({'company':company,'factory':factory,'department':department,'group':group,'able':0})
  985. elif int(user_role) == 4:
  986. cmd2 = 'SELECT company,factory,department FROM users WHERE username = "'+user_name +'"'
  987. company_able:str
  988. factory_able:str
  989. department_able:str
  990. for row in db.query(cmd2) :
  991. company_able = row['company']
  992. factory_able = row['factory']
  993. department_able = row['department']
  994. for row in db.query(cmd) :
  995. company = row['Company']
  996. factory = row['Factory']
  997. department = row['Department']
  998. cmd3 = 'SELECT TowerGroupCode FROM device WHERE CompanyCode = "' + company + '" AND FactoryCode = "' + factory + '" AND DepartmentCode = "' + department + '"'
  999. group = []
  1000. for row2 in db.query(cmd3):
  1001. if row2['TowerGroupCode'] not in group :
  1002. group.append(row2['TowerGroupCode'])
  1003. if company == company_able and factory==factory_able and department==department_able:
  1004. result.append({'company':company,'factory':factory,'department':department,'group':group,'able':1})
  1005. else:
  1006. result.append({'company':company,'factory':factory,'department':department,'group':group,'able':0})
  1007. else :
  1008. result =[ {'msg':"error"}]
  1009. return result
  1010. def get_user_id(user_name:str):
  1011. """獲取user id"""
  1012. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  1013. cmd = 'SELECT id FROM `users` where username = "'+user_name+'"'
  1014. id = None
  1015. for row in db.query(cmd) :
  1016. id = row['id']
  1017. return id
  1018. def get_user_name(user_id:int):
  1019. """獲取user name"""
  1020. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  1021. cmd = 'SELECT username FROM `users` where id = "'+user_id+'"'
  1022. id = None
  1023. for row in db.query(cmd) :
  1024. id = row['username']
  1025. return id
  1026. def get_modul_name(modul_id:str):
  1027. """獲取modul名稱"""
  1028. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  1029. cmd = 'SELECT moduleName FROM `module` where id = "'+modul_id+'"'
  1030. modul_name = None
  1031. for row in db.query(cmd) :
  1032. modul_name = row['moduleName']
  1033. return modul_name
  1034. def get_tower_info(tower_id:str):
  1035. """獲取水塔資料"""
  1036. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  1037. cmd = 'SELECT * FROM `record_dcs` where device_id = "'+tower_id+'"'
  1038. result ={'DCS':{},'Fan':{},'Moter':{},'Device_info':{}}
  1039. for row in db.query(cmd) :
  1040. result['DCS'][row['key']]=row['value']
  1041. cmd = 'SELECT * FROM `record_tower` where device_id = "'+tower_id+'"'
  1042. for row in db.query(cmd) :
  1043. result['Fan'][row['key']]=row['value']
  1044. result['Moter'] = []
  1045. cmd = 'SELECT * FROM `vibration` where device_id = "'+tower_id+'"'
  1046. tmp = {}
  1047. for row in db.query(cmd) :
  1048. for col in db['vibration'].columns :
  1049. tmp[col] = row[col]
  1050. result['Moter'].append(tmp)
  1051. tmp = {}
  1052. cmd = 'SELECT * FROM `device` where id = "'+tower_id+'"'
  1053. for row in db.query(cmd):
  1054. for col in db['device'].columns :
  1055. if col != 'createTime':
  1056. result['Device_info'][col] = row[col]
  1057. return result
  1058. def get_tower_perform(tower_id:str):
  1059. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  1060. cmd = 'SELECT * FROM `record_performance` where deviceCode = "'+tower_id+'" AND designWFR = 27000'
  1061. result = [{}]
  1062. for row in db.query(cmd):
  1063. for col in db['record_performance'].columns :
  1064. if col != 'createTime':
  1065. result[0][col] = row[col]
  1066. #print(result)
  1067. return result
  1068. def get_tower(company:str,factory:str,department:str,towerGroup:str):
  1069. towergroup_arr =[]
  1070. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  1071. cmd = 'SELECT id FROM `device` where CompanyCode = "'+company+'" AND FactoryCode = "' +factory+'" AND DepartmentCode = "'+department+'" AND TowerGroupCode = "' + towerGroup + '"'
  1072. for row in db.query(cmd) :
  1073. towergroup_arr.append(row['id'])
  1074. return towergroup_arr
  1075. def check_tower_health(user_name:str):
  1076. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  1077. org = get_user_under_organization(user_name)
  1078. #print(org)
  1079. towers : list
  1080. result = []
  1081. for dic in org:
  1082. #print(dic)
  1083. if dic['able'] == 1:
  1084. for group in dic['group']:
  1085. towers = get_tower(dic['company'],dic['factory'],dic['department'],group)
  1086. for tower in towers:
  1087. cmd = 'SELECT CVIndex,threshold FROM `vibration` where device_id = "'+tower+'"'
  1088. health =1
  1089. for row in db.query(cmd) :
  1090. print(row['CVIndex'],row['threshold'])
  1091. if row['CVIndex'] < int(row['threshold']):
  1092. health = 0
  1093. print("heheh")
  1094. print(health)
  1095. result.append({'company':dic['company'], 'factory':dic['factory'], 'department':dic['department'], 'group': group, 'tower': tower, 'health': health})
  1096. return result
  1097. def find_vibration_id(tower_id:str,channel_id:str):
  1098. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  1099. cmd = 'SELECT id FROM `vibration` where device_id = "'+tower_id+'" AND channelName = "' +channel_id+'"'
  1100. for row in db.query(cmd) :
  1101. return row['id']
  1102. def get_channel_info(vibration_id):
  1103. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  1104. cmd = 'SELECT * FROM `record_diagnosis` where vibration_id = "'+vibration_id+'"'
  1105. result = {}
  1106. for row in db.query(cmd) :
  1107. for col in db['record_diagnosis'].columns :
  1108. result[col] = row[col]
  1109. cmd2='SELECT CVIndex,threshold FROM `vibration` where id = "'+vibration_id+'"'
  1110. for row2 in db.query(cmd2) :
  1111. result['CVIndex'] = row2['CVIndex']
  1112. result['threshold'] = row2['threshold']
  1113. print(result)
  1114. return result
  1115. def get_channel_health(vibration_id:str):
  1116. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  1117. result = []
  1118. data_name = ['CV_index', 'Vrms' ,'RPM']
  1119. for row in data_name:
  1120. result.append({'name':row,'data':[]})
  1121. cmd = 'SELECT * FROM record_health where vibration_id = "'+vibration_id+'"'
  1122. for row in db.query(cmd) :
  1123. for dic in result:
  1124. dic['data'].append(row[dic['name']])
  1125. return result
  1126. def get_predect_data(vibration_id:str):
  1127. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  1128. result = {}
  1129. cmd = 'SELECT * FROM record_prediction_upd where vibration_id = "'+vibration_id+'"'
  1130. for row in db.query(cmd) :
  1131. arr = row['predictData'].split(',')
  1132. result['data']=arr
  1133. return result
  1134. def get_frequency(vibration_id:str):
  1135. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  1136. result = {'Hz':[],'value':[]}
  1137. print(vibration_id)
  1138. cmd = 'SELECT * FROM record_frequency where vibration_id = "'+vibration_id+'"'
  1139. for row in db.query(cmd) :
  1140. result['Hz'].append(row['MFHz'])
  1141. result['value'].append(row['MFValue'])
  1142. return result
  1143. def add_data():
  1144. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  1145. loc_dt = datetime.today()
  1146. loc_dt_format = loc_dt.strftime("%Y-%m-%d %H:%M:%S")
  1147. cmd = "TRUNCATE TABLE record_health"
  1148. db.query(cmd)
  1149. for j in range(1,5):
  1150. for i in range(0,7):
  1151. time_del = timedelta(days=i)
  1152. date=loc_dt-time_del
  1153. db['record_health'].insert(dict(time_stamp=date, CV_index=uniform(0.6, 0.8), Vrms=uniform(1,3),Grms = uniform(1,3),RPM=uniform(1,3),vibration_id = j))
  1154. cmd = "TRUNCATE TABLE record_frequency"
  1155. db.query(cmd)
  1156. for i in range(0,1000):
  1157. print(i)
  1158. db['record_frequency'].insert(dict(time_stamp=loc_dt_format, MFHz=i, MFValue=uniform(0,0.12),vibration_id = 1))
  1159. cmd = "TRUNCATE TABLE record_frequency"
  1160. db.query(cmd)
  1161. for i in range(0,1000):
  1162. print(i)
  1163. db['record_frequency'].insert(dict(time_stamp=loc_dt_format, MFHz=i, MFValue=uniform(0,0.12),vibration_id = 1))
  1164. def add_tower():
  1165. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/Water_tower?charset=utf8mb4')
  1166. loc_dt = datetime.today()
  1167. loc_dt_format = loc_dt.strftime("%Y-%m-%d %H:%M:%S")
  1168. for device in ['dev005','dev006','dev007','dev008','dev009']:
  1169. for key in ['hotTemp','waterFlow','coldTemp','fanMotorCur','fanMotorSpeedFreq']:
  1170. db['record_dcs'].insert(dict(time_stamp=loc_dt_format, MFValue=uniform(0,0.12),vibration_id = 1))