1
|
from datetime import datetime, date
|
2
|
|
3
|
from sqlalchemy.orm import Session
|
4
|
from sqlalchemy import and_, desc
|
5
|
from . import models, schemas
|
6
|
|
7
|
|
8
|
def get_device(db: Session, device_id: int):
|
9
|
"""
|
10
|
returns one specific devices by given id
|
11
|
"""
|
12
|
return db.query(models.Device).filter(models.Device.id == device_id).first()
|
13
|
|
14
|
|
15
|
def get_devices(db: Session, skip: int = 0, limit: int = 100):
|
16
|
"""
|
17
|
returns all devices in database
|
18
|
"""
|
19
|
return db.query(models.Device).offset(skip).limit(limit).all()
|
20
|
|
21
|
|
22
|
def find_device(db: Session, device: schemas.DeviceBase):
|
23
|
"""
|
24
|
finds one device with product_id, vendor_id and serial_number same as in given DeviceBase object
|
25
|
"""
|
26
|
return db.query(models.Device).filter(and_(models.Device.product_id == device.product_id,
|
27
|
models.Device.vendor_id == device.vendor_id,
|
28
|
models.Device.serial_number == device.serial_number)).first()
|
29
|
|
30
|
|
31
|
def create_device(db: Session, device: schemas.DeviceBase):
|
32
|
"""
|
33
|
creates new device with data from given DeviceBase object
|
34
|
"""
|
35
|
db_device = models.Device(vendor_id=device.vendor_id, product_id=device.product_id,
|
36
|
serial_number=device.serial_number, assigned=False)
|
37
|
db.add(db_device)
|
38
|
db.commit()
|
39
|
db.refresh(db_device)
|
40
|
return db_device
|
41
|
|
42
|
|
43
|
def get_license(db: Session, license_id: int):
|
44
|
"""
|
45
|
returns one specific license by given id
|
46
|
"""
|
47
|
return db.query(models.License).filter(models.License.id == license_id).first()
|
48
|
|
49
|
|
50
|
def get_licenses(db: Session, skip: int = 0, limit: int = 100):
|
51
|
"""
|
52
|
returns all licenses in database
|
53
|
"""
|
54
|
return db.query(models.License).offset(skip).limit(limit).all()
|
55
|
|
56
|
|
57
|
def find_license(db: Session, name: str):
|
58
|
"""
|
59
|
finds one license by given string name
|
60
|
"""
|
61
|
return db.query(models.License).filter(models.License.name == name).first()
|
62
|
|
63
|
|
64
|
def create_license(db: Session, name: str, expdate: date):
|
65
|
"""
|
66
|
creates new license with given name and expiration date
|
67
|
"""
|
68
|
db_license = models.License(name=name, expiration_date=expdate)
|
69
|
db.add(db_license)
|
70
|
db.commit()
|
71
|
db.refresh(db_license)
|
72
|
return db_license
|
73
|
|
74
|
|
75
|
def get_license_devices(db: Session, license_id: int):
|
76
|
"""
|
77
|
returns all entries in devices_licenses table with given license_id
|
78
|
"""
|
79
|
return db.query(models.DeviceLicense).filter(models.DeviceLicense.license_id == license_id).all()
|
80
|
|
81
|
|
82
|
def get_device_licenses(db: Session, device_id: int):
|
83
|
"""
|
84
|
returns all entries in devices_licenses table with given license_id
|
85
|
"""
|
86
|
return db.query(models.DeviceLicense).filter(models.DeviceLicense.device_id == device_id).all()
|
87
|
|
88
|
|
89
|
def get_devicelicense_by_devicelicense(db: Session, device_id: int, license_id: int):
|
90
|
"""
|
91
|
returns entry in devices_licenses table with given device id and license id
|
92
|
"""
|
93
|
return db.query(models.DeviceLicense).filter(and_(models.DeviceLicense.device_id == device_id,
|
94
|
models.DeviceLicense.license_id == license_id)).first()
|
95
|
|
96
|
|
97
|
def get_bodydevicelicense_by_bodydevicelicense(db: Session, device_id: int, license_id: int):
|
98
|
"""
|
99
|
returns entry in bodydevices_licenses table with given body device id and license id
|
100
|
"""
|
101
|
return db.query(models.BodyDeviceLicense).filter(and_(models.BodyDeviceLicense.bodydevice_id == device_id,
|
102
|
models.BodyDeviceLicense.license_id == license_id)).first()
|
103
|
|
104
|
|
105
|
def get_license_bodydevice(db: Session, license_id: int):
|
106
|
"""
|
107
|
returns all entries in bodydevices_licenses with given license_id
|
108
|
"""
|
109
|
return db.query(models.BodyDeviceLicense).filter(models.BodyDeviceLicense.license_id == license_id).all()
|
110
|
|
111
|
|
112
|
def get_bodydevice_license(db: Session, device_id: int):
|
113
|
"""
|
114
|
returns all entries in bodydevices_licenses with given license_id
|
115
|
"""
|
116
|
return db.query(models.BodyDeviceLicense).filter(models.BodyDeviceLicense.bodydevice_id == device_id).all()
|
117
|
|
118
|
|
119
|
def create_device_license(db: Session, device: int, license: int, time: datetime):
|
120
|
"""
|
121
|
creates new entry in devices_licenses table with device id, license id and time.
|
122
|
"""
|
123
|
db_device_license = models.DeviceLicense(device_id=device, license_id=license,
|
124
|
assigned_datetime=time)
|
125
|
db.add(db_device_license)
|
126
|
db.commit()
|
127
|
db.refresh(db_device_license)
|
128
|
return db_device_license
|
129
|
|
130
|
|
131
|
def delete_device_license(db: Session, device: int, license: int):
|
132
|
"""
|
133
|
deletes entry in devices_licenses table with device id, license id and time.
|
134
|
"""
|
135
|
db_device_license = get_devicelicense_by_devicelicense(db, device, license)
|
136
|
db_lic = db.delete(db_device_license)
|
137
|
db.commit()
|
138
|
return db_lic
|
139
|
|
140
|
|
141
|
def delete_bodydevice_license(db: Session, device: int, license: int):
|
142
|
"""
|
143
|
deletes entry in devices_licenses table with device id, license id and time.
|
144
|
"""
|
145
|
db_device_license = get_bodydevicelicense_by_bodydevicelicense(db, device, license)
|
146
|
db_lic = db.delete(db_device_license)
|
147
|
db.commit()
|
148
|
return db_lic
|
149
|
|
150
|
|
151
|
def create_body_device_license(db: Session, device: int, license: int, time: datetime):
|
152
|
"""
|
153
|
creates new entry in devices_licenses table with device id, license id and time.
|
154
|
"""
|
155
|
db_device_license = models.BodyDeviceLicense(bodydevice_id=device, license_id=license,
|
156
|
assigned_datetime=time)
|
157
|
db.add(db_device_license)
|
158
|
db.commit()
|
159
|
db.refresh(db_device_license)
|
160
|
return db_device_license
|
161
|
|
162
|
|
163
|
def find_pc_by_username(db: Session, name: str):
|
164
|
"""
|
165
|
Finds one pc by given username
|
166
|
"""
|
167
|
return db.query(models.PC).filter(models.PC.username == name).first()
|
168
|
|
169
|
|
170
|
def get_pc(db: Session, pc_id: int):
|
171
|
"""
|
172
|
returns one specific pc by given id
|
173
|
"""
|
174
|
return db.query(models.PC).filter(models.PC.id == pc_id).first()
|
175
|
|
176
|
|
177
|
def update_pc(db: Session, pc_id: int, team: str):
|
178
|
"""
|
179
|
Updates team of one specific pc
|
180
|
"""
|
181
|
old_pc = get_pc(db, pc_id)
|
182
|
team = get_team(db, int(team))
|
183
|
new = {'id': old_pc.id, 'username': old_pc.username, 'hostname': old_pc.hostname, 'assigned': True,
|
184
|
'team_id': team.id}
|
185
|
for key, value in new.items():
|
186
|
setattr(old_pc, key, value)
|
187
|
db.commit()
|
188
|
db.refresh(old_pc)
|
189
|
return old_pc
|
190
|
|
191
|
def change_role(db: Session, usr_id: int, role: str):
|
192
|
"""
|
193
|
Updates team of one specific pc
|
194
|
"""
|
195
|
old_usr = find_user_byid(db, usr_id)
|
196
|
new = {'id': old_usr.id, 'username': old_usr.username, 'password': old_usr.password, 'role': role}
|
197
|
for key, value in new.items():
|
198
|
setattr(old_usr, key, value)
|
199
|
db.commit()
|
200
|
db.refresh(old_usr)
|
201
|
return old_usr
|
202
|
|
203
|
|
204
|
def get_pcs(db: Session, skip: int = 0, limit: int = 100):
|
205
|
"""
|
206
|
returns all pcs in database
|
207
|
"""
|
208
|
return db.query(models.PC).offset(skip).limit(limit).all()
|
209
|
|
210
|
|
211
|
def find_pc(db: Session, username: str, hostname: str):
|
212
|
"""
|
213
|
Finds one pc with given username and hostname
|
214
|
"""
|
215
|
return db.query(models.PC).filter(and_(models.PC.username == username,
|
216
|
models.PC.hostname == hostname)).first()
|
217
|
|
218
|
|
219
|
def find_pc_by_name(db: Session, username: str):
|
220
|
"""
|
221
|
Finds one pc by its username
|
222
|
"""
|
223
|
return db.query(models.PC).filter(models.PC.username == username).first()
|
224
|
|
225
|
|
226
|
def find_pc_by_name_all(db: Session, username: str):
|
227
|
"""
|
228
|
Finds all pcs with same username
|
229
|
"""
|
230
|
return db.query(models.PC).filter(models.PC.username == username).offset(0).limit(100).all()
|
231
|
|
232
|
|
233
|
def find_pcs(db: Session, pcs: []):
|
234
|
"""
|
235
|
Finds all pcs with ids in given id array
|
236
|
"""
|
237
|
return db.query(models.PC).filter(models.PC.id.in_(pcs)).all()
|
238
|
|
239
|
|
240
|
def get_pcs_by_team(db: Session, team_id: int):
|
241
|
"""
|
242
|
returns all pcs in given team by team id
|
243
|
"""
|
244
|
return db.query(models.PC).filter(models.PC.team_id == team_id).all()
|
245
|
|
246
|
|
247
|
def create_pc(db: Session, user: str, host: str):
|
248
|
"""
|
249
|
creates new pc with given username and hostname
|
250
|
"""
|
251
|
db_pc = models.PC(username=user, hostname=host, assigned=False)
|
252
|
db.add(db_pc)
|
253
|
db.commit()
|
254
|
db.refresh(db_pc)
|
255
|
return db_pc
|
256
|
|
257
|
|
258
|
def get_team(db: Session, team_id: int):
|
259
|
"""
|
260
|
returns one specific team wit given id
|
261
|
"""
|
262
|
return db.query(models.Team).filter(models.Team.id == team_id).first()
|
263
|
|
264
|
|
265
|
def get_teams(db: Session, skip: int = 0, limit: int = 100):
|
266
|
"""
|
267
|
returns all teams currently saved in database
|
268
|
"""
|
269
|
return db.query(models.Team).offset(skip).limit(limit).all()
|
270
|
|
271
|
|
272
|
def find_team(db: Session, name: str):
|
273
|
"""
|
274
|
Finds one specific team by its name
|
275
|
"""
|
276
|
return db.query(models.Team).filter(models.Team.name == name).first()
|
277
|
|
278
|
|
279
|
def create_team(db: Session, name: str):
|
280
|
"""
|
281
|
Creates new team with given name
|
282
|
"""
|
283
|
db_team = models.Team(name=name)
|
284
|
db.add(db_team)
|
285
|
db.commit()
|
286
|
db.refresh(db_team)
|
287
|
return db_team
|
288
|
|
289
|
|
290
|
def get_head_device(db: Session, head_id: int):
|
291
|
"""
|
292
|
Returns one specific head device by given id
|
293
|
"""
|
294
|
return db.query(models.HeadDevice).filter(models.HeadDevice.id == head_id).first()
|
295
|
|
296
|
|
297
|
def get_head_devices(db: Session, skip: int = 0, limit: int = 100):
|
298
|
"""
|
299
|
Returns all head devices saved in database
|
300
|
"""
|
301
|
return db.query(models.HeadDevice).offset(skip).limit(limit).all()
|
302
|
|
303
|
|
304
|
def find_head_device(db: Session, serial: schemas.HeadDeviceBase):
|
305
|
"""
|
306
|
Finds one head device by its serial number
|
307
|
"""
|
308
|
return db.query(models.HeadDevice).filter(models.HeadDevice.serial_number == serial.serial_number).first()
|
309
|
|
310
|
|
311
|
def create_head_device(db: Session, log: schemas.HeadDeviceBase):
|
312
|
"""
|
313
|
Creates new head device
|
314
|
"""
|
315
|
db_head = models.HeadDevice(serial_number=log.serial_number)
|
316
|
db.add(db_head)
|
317
|
db.commit()
|
318
|
db.refresh(db_head)
|
319
|
return db_head
|
320
|
|
321
|
|
322
|
def get_body_device(db: Session, body_id: int):
|
323
|
"""
|
324
|
Returns one specific body device by given id
|
325
|
"""
|
326
|
return db.query(models.BodyDevice).filter(models.BodyDevice.id == body_id).first()
|
327
|
|
328
|
|
329
|
def get_body_devices(db: Session, skip: int = 0, limit: int = 100):
|
330
|
"""
|
331
|
Returns all body devices saved in database
|
332
|
"""
|
333
|
return db.query(models.BodyDevice).offset(skip).limit(limit).all()
|
334
|
|
335
|
|
336
|
def find_body_device(db: Session, serial: schemas.BodyDeviceBase):
|
337
|
"""
|
338
|
Finds one body device by its serial number
|
339
|
"""
|
340
|
return db.query(models.BodyDevice).filter(models.BodyDevice.serial_number == serial.serial_number).first()
|
341
|
|
342
|
|
343
|
def create_body_device(db: Session, log: schemas.BodyDeviceBase):
|
344
|
"""
|
345
|
Creates new Body device
|
346
|
"""
|
347
|
db_body = models.BodyDevice(serial_number=log.serial_number)
|
348
|
db.add(db_body)
|
349
|
db.commit()
|
350
|
db.refresh(db_body)
|
351
|
return db_body
|
352
|
|
353
|
|
354
|
def get_ld_logs(db: Session, skip: int = 0, limit: int = 100):
|
355
|
"""
|
356
|
Returns all ld debugger logs in database
|
357
|
"""
|
358
|
return db.query(models.LDLog).order_by(desc(models.LDLog.timestamp)).offset(skip).limit(limit).all()
|
359
|
|
360
|
|
361
|
def create_ld_logs(db: Session, item: schemas.LDTempBase, head_id: int, body_id: int, pc_id: int, date: datetime):
|
362
|
"""
|
363
|
Creates new ld log for ld_logs database table
|
364
|
"""
|
365
|
db_ld = models.LDLog(pc_id=pc_id, timestamp=date, status=item.status, head_id=head_id, body_id=body_id)
|
366
|
db.add(db_ld)
|
367
|
db.commit()
|
368
|
db.refresh(db_ld)
|
369
|
return db_ld
|
370
|
|
371
|
|
372
|
def get_logs(db: Session, skip: int = 0, limit: int = 100):
|
373
|
"""
|
374
|
Returns all usb logs in database ordered by timestamp
|
375
|
"""
|
376
|
return db.query(models.USBLog).order_by(desc(models.USBLog.timestamp)).offset(skip).limit(limit).all()
|
377
|
|
378
|
|
379
|
def get_log(db: Session, device_id: int, skip: int = 0, limit: int = 100):
|
380
|
"""
|
381
|
Returns all usb logs in database sorted by id
|
382
|
"""
|
383
|
return db.query(models.USBLog).filter(models.USBLog.device_id == device_id).offset(skip).limit(limit).all()
|
384
|
|
385
|
|
386
|
def find_filtered_logs(db: Session, logs: []):
|
387
|
"""
|
388
|
Returns all usb logs with ids in given id array.
|
389
|
"""
|
390
|
return db.query(models.USBLog).filter(models.USBLog.id.in_(logs)).order_by(desc(models.USBLog.timestamp)).all()
|
391
|
|
392
|
|
393
|
def find_filtered_ldlogs(db: Session, logs: []):
|
394
|
"""
|
395
|
Returns all ld logs with ids in given id array.
|
396
|
"""
|
397
|
return db.query(models.LDLog).filter(models.LDLog.id.in_(logs)).order_by(desc(models.LDLog.timestamp)).all()
|
398
|
|
399
|
|
400
|
def get_filtered_ldlogs(db: Session, pc: str, tema: str, lic: str):
|
401
|
"""
|
402
|
Function creates query string used for filtering by pc username, team name and license name.
|
403
|
Depending on selected filters assembles query string for database
|
404
|
"""
|
405
|
execute_string = "SELECT * FROM ld_logs AS logs"
|
406
|
pcs = find_pc_by_username(db, pc)
|
407
|
if pc != "all":
|
408
|
if pcs is not None:
|
409
|
execute_string += " WHERE logs.pc_id = " + str(pcs.id)
|
410
|
if tema != "all":
|
411
|
team = find_team(db, tema)
|
412
|
if team is not None:
|
413
|
pcst = get_pcs_by_team(db, team.id)
|
414
|
pc_ids = "("
|
415
|
for p in pcst:
|
416
|
pc_ids += str(p.id) + ", "
|
417
|
def_pc_ids = pc_ids[:-2] + ")"
|
418
|
if pc != "all" and pcs is not None:
|
419
|
if len(def_pc_ids) > 1:
|
420
|
execute_string += " AND logs.pc_id IN " + def_pc_ids
|
421
|
else:
|
422
|
if len(def_pc_ids) > 1:
|
423
|
execute_string += " WHERE logs.pc_id IN " + def_pc_ids
|
424
|
if lic != "all":
|
425
|
license = find_license(db, lic)
|
426
|
if license is not None:
|
427
|
device_licenses = get_license_bodydevice(db, license.id)
|
428
|
dev_ids = "("
|
429
|
for dev in device_licenses:
|
430
|
dev_ids += str(dev.bodydevice_id) + ", "
|
431
|
defin_ids = dev_ids[:-2] + ")"
|
432
|
if pc != "all" or tema != "all":
|
433
|
if len(defin_ids) > 1:
|
434
|
execute_string += " AND logs.body_id IN " + defin_ids
|
435
|
else:
|
436
|
if len(defin_ids) > 1:
|
437
|
execute_string += " WHERE logs.body_id IN " + defin_ids
|
438
|
|
439
|
# executing assembled query string
|
440
|
result = db.execute(execute_string)
|
441
|
return result
|
442
|
|
443
|
|
444
|
def get_filtered_logs(db: Session, pc: str, tema: str, lic: str):
|
445
|
"""
|
446
|
Function creates query string used for filtering by pc username, team name and license name.
|
447
|
Depending on selected filters assembles query string for database
|
448
|
"""
|
449
|
execute_string = "SELECT * FROM usb_logs AS logs"
|
450
|
pcs = find_pc_by_username(db, pc)
|
451
|
if pc != "all":
|
452
|
if pcs is not None:
|
453
|
execute_string += " WHERE logs.pc_id = " + str(pcs.id)
|
454
|
if tema != "all":
|
455
|
team = find_team(db, tema)
|
456
|
if team is not None:
|
457
|
pcst = get_pcs_by_team(db, team.id)
|
458
|
pc_ids = "("
|
459
|
for p in pcst:
|
460
|
pc_ids += str(p.id) + ", "
|
461
|
def_pc_ids = pc_ids[:-2] + ")"
|
462
|
if pc != "all" and pcs is not None:
|
463
|
if len(def_pc_ids) > 1:
|
464
|
execute_string += " AND logs.pc_id IN " + def_pc_ids
|
465
|
else:
|
466
|
if len(def_pc_ids) > 1:
|
467
|
execute_string += " WHERE logs.pc_id IN " + def_pc_ids
|
468
|
if lic != "all":
|
469
|
license = find_license(db, lic)
|
470
|
if license is not None:
|
471
|
device_licenses = get_license_devices(db, license.id)
|
472
|
dev_ids = "("
|
473
|
for dev in device_licenses:
|
474
|
dev_ids += str(dev.device_id) + ", "
|
475
|
defin_ids = dev_ids[:-2] + ")"
|
476
|
if pc != "all" or tema != "all":
|
477
|
if len(defin_ids) > 1:
|
478
|
execute_string += " AND logs.device_id IN " + defin_ids
|
479
|
else:
|
480
|
if len(defin_ids) > 1:
|
481
|
execute_string += " WHERE logs.device_id IN " + defin_ids
|
482
|
|
483
|
# executing assembled query string
|
484
|
result = db.execute(execute_string)
|
485
|
return result
|
486
|
|
487
|
|
488
|
def create_device_logs(db: Session, item: schemas.USBTempBase, dev_id: int, pc_id: int, date: datetime):
|
489
|
"""
|
490
|
Creates new USB log for usb_logs database table
|
491
|
"""
|
492
|
db_log = models.USBLog(pc_id=pc_id, timestamp=date, status=item.status, device_id=dev_id)
|
493
|
db.add(db_log)
|
494
|
db.commit()
|
495
|
db.refresh(db_log)
|
496
|
return db_log
|
497
|
|
498
|
|
499
|
def get_users(db: Session, skip: int = 0, limit: int = 100):
|
500
|
"""
|
501
|
Returns all users saved in database
|
502
|
"""
|
503
|
return db.query(models.User).offset(skip).limit(limit).all()
|
504
|
|
505
|
|
506
|
def find_user(db: Session, name: str):
|
507
|
"""
|
508
|
Finds one user by given username
|
509
|
"""
|
510
|
return db.query(models.User).filter(models.User.username == name).first()
|
511
|
|
512
|
|
513
|
def find_user_byid(db: Session, id: int):
|
514
|
"""
|
515
|
Finds one user by given id
|
516
|
"""
|
517
|
return db.query(models.User).filter(models.User.id == id).first()
|
518
|
|
519
|
|
520
|
def create_user(db: Session, name: str, passw: str, rol: str):
|
521
|
"""
|
522
|
Creates new user
|
523
|
"""
|
524
|
db_user = models.User(username=name, password=passw, role=rol)
|
525
|
db.add(db_user)
|
526
|
db.commit()
|
527
|
db.refresh(db_user)
|
528
|
return db_user
|