1 |
99c92c11
|
Matej Zeman
|
from datetime import datetime, date
|
2 |
aba60b70
|
Matej Zeman
|
|
3 |
99c92c11
|
Matej Zeman
|
from sqlalchemy.orm import Session
|
4 |
b6f0e019
|
Matej Zeman
|
from sqlalchemy import and_, desc
|
5 |
aba60b70
|
Matej Zeman
|
from . import models, schemas
|
6 |
|
|
|
7 |
|
|
|
8 |
|
|
def get_device(db: Session, device_id: int):
|
9 |
|
|
return db.query(models.Device).filter(models.Device.id == device_id).first()
|
10 |
|
|
|
11 |
|
|
|
12 |
|
|
def get_devices(db: Session, skip: int = 0, limit: int = 100):
|
13 |
|
|
return db.query(models.Device).offset(skip).limit(limit).all()
|
14 |
|
|
|
15 |
|
|
|
16 |
|
|
def find_device(db: Session, device: schemas.DeviceBase):
|
17 |
99c92c11
|
Matej Zeman
|
return db.query(models.Device).filter(and_(models.Device.product_id == device.product_id,
|
18 |
b6f0e019
|
Matej Zeman
|
models.Device.vendor_id == device.vendor_id,
|
19 |
|
|
models.Device.serial_number == device.serial_number)).first()
|
20 |
aba60b70
|
Matej Zeman
|
|
21 |
|
|
|
22 |
|
|
def create_device(db: Session, device: schemas.DeviceBase):
|
23 |
|
|
db_device = models.Device(vendor_id=device.vendor_id, product_id=device.product_id,
|
24 |
99c92c11
|
Matej Zeman
|
serial_number=device.serial_number, assigned=False)
|
25 |
aba60b70
|
Matej Zeman
|
db.add(db_device)
|
26 |
|
|
db.commit()
|
27 |
|
|
db.refresh(db_device)
|
28 |
|
|
return db_device
|
29 |
|
|
|
30 |
|
|
|
31 |
6c1e92e3
|
Matej Zeman
|
def get_license(db: Session, license_id: int):
|
32 |
|
|
return db.query(models.License).filter(models.License.id == license_id).first()
|
33 |
|
|
|
34 |
|
|
|
35 |
|
|
def get_licenses(db: Session, skip: int = 0, limit: int = 100):
|
36 |
|
|
return db.query(models.License).offset(skip).limit(limit).all()
|
37 |
|
|
|
38 |
|
|
|
39 |
b6f0e019
|
Matej Zeman
|
def find_license(db: Session, name: str):
|
40 |
|
|
return db.query(models.License).filter(models.License.name == name).first()
|
41 |
|
|
|
42 |
|
|
|
43 |
99c92c11
|
Matej Zeman
|
def create_license(db: Session, name: str, expdate: date):
|
44 |
|
|
db_license = models.License(name=name, expiration_date=expdate)
|
45 |
6c1e92e3
|
Matej Zeman
|
db.add(db_license)
|
46 |
|
|
db.commit()
|
47 |
|
|
db.refresh(db_license)
|
48 |
|
|
return db_license
|
49 |
|
|
|
50 |
|
|
|
51 |
b6f0e019
|
Matej Zeman
|
def get_license_devices(db: Session, license_id: int):
|
52 |
|
|
return db.query(models.DeviceLicense).filter(models.DeviceLicense.license_id == license_id).all()
|
53 |
|
|
|
54 |
|
|
|
55 |
|
|
def create_device_license(db: Session, device: int, license: int, time: datetime):
|
56 |
|
|
db_device_license = models.DeviceLicense(device_id=device, license_id=license,
|
57 |
|
|
assigned_datetime=time)
|
58 |
6c1e92e3
|
Matej Zeman
|
db.add(db_device_license)
|
59 |
|
|
db.commit()
|
60 |
|
|
db.refresh(db_device_license)
|
61 |
|
|
return db_device_license
|
62 |
|
|
|
63 |
|
|
|
64 |
b6f0e019
|
Matej Zeman
|
def find_pc_by_username(db: Session, name: str):
|
65 |
|
|
return db.query(models.PC).filter(models.PC.username == name).first()
|
66 |
|
|
|
67 |
|
|
|
68 |
6c1e92e3
|
Matej Zeman
|
def get_pc(db: Session, pc_id: int):
|
69 |
|
|
return db.query(models.PC).filter(models.PC.id == pc_id).first()
|
70 |
|
|
|
71 |
|
|
|
72 |
b6f0e019
|
Matej Zeman
|
def update_pc(db: Session, pc_id: int, team: str):
|
73 |
|
|
old_pc = get_pc(db, pc_id)
|
74 |
|
|
team = get_team(db, int(team))
|
75 |
|
|
new = {'id': old_pc.id, 'username': old_pc.username, 'hostname': old_pc.hostname, 'assigned': True,
|
76 |
|
|
'team_id': team.id}
|
77 |
|
|
for key, value in new.items():
|
78 |
|
|
setattr(old_pc, key, value)
|
79 |
|
|
db.commit()
|
80 |
|
|
db.refresh(old_pc)
|
81 |
|
|
return old_pc
|
82 |
|
|
|
83 |
|
|
|
84 |
6c1e92e3
|
Matej Zeman
|
def get_pcs(db: Session, skip: int = 0, limit: int = 100):
|
85 |
|
|
return db.query(models.PC).offset(skip).limit(limit).all()
|
86 |
|
|
|
87 |
|
|
|
88 |
|
|
def find_pc(db: Session, username: str, hostname: str):
|
89 |
99c92c11
|
Matej Zeman
|
return db.query(models.PC).filter(and_(models.PC.username == username,
|
90 |
b6f0e019
|
Matej Zeman
|
models.PC.hostname == hostname)).first()
|
91 |
|
|
|
92 |
|
|
|
93 |
|
|
def find_pc_by_name(db: Session, username: str):
|
94 |
|
|
return db.query(models.PC).filter(models.PC.username == username).first()
|
95 |
|
|
|
96 |
|
|
|
97 |
|
|
def find_pc_by_name_all(db: Session, username: str):
|
98 |
|
|
return db.query(models.PC).filter(models.PC.username == username).offset(0).limit(100).all()
|
99 |
99c92c11
|
Matej Zeman
|
|
100 |
|
|
|
101 |
|
|
def find_pcs(db: Session, pcs: []):
|
102 |
|
|
return db.query(models.PC).filter(models.PC.id.in_(pcs)).all()
|
103 |
6c1e92e3
|
Matej Zeman
|
|
104 |
|
|
|
105 |
b6f0e019
|
Matej Zeman
|
def get_pcs_by_team(db: Session, team_id: int):
|
106 |
|
|
return db.query(models.PC).filter(models.PC.team_id == team_id).all()
|
107 |
|
|
|
108 |
|
|
|
109 |
6c1e92e3
|
Matej Zeman
|
def create_pc(db: Session, user: str, host: str):
|
110 |
99c92c11
|
Matej Zeman
|
db_pc = models.PC(username=user, hostname=host, assigned=False)
|
111 |
6c1e92e3
|
Matej Zeman
|
db.add(db_pc)
|
112 |
|
|
db.commit()
|
113 |
|
|
db.refresh(db_pc)
|
114 |
|
|
return db_pc
|
115 |
|
|
|
116 |
|
|
|
117 |
99c92c11
|
Matej Zeman
|
def get_team(db: Session, team_id: int):
|
118 |
|
|
return db.query(models.Team).filter(models.Team.id == team_id).first()
|
119 |
|
|
|
120 |
|
|
|
121 |
|
|
def get_teams(db: Session, skip: int = 0, limit: int = 100):
|
122 |
|
|
return db.query(models.Team).offset(skip).limit(limit).all()
|
123 |
|
|
|
124 |
|
|
|
125 |
b6f0e019
|
Matej Zeman
|
def find_team(db: Session, name: str):
|
126 |
|
|
return db.query(models.Team).filter(models.Team.name == name).first()
|
127 |
|
|
|
128 |
|
|
|
129 |
99c92c11
|
Matej Zeman
|
def create_team(db: Session, name: str):
|
130 |
b6f0e019
|
Matej Zeman
|
db_team = models.Team(name=name)
|
131 |
99c92c11
|
Matej Zeman
|
db.add(db_team)
|
132 |
|
|
db.commit()
|
133 |
|
|
db.refresh(db_team)
|
134 |
|
|
return db_team
|
135 |
|
|
|
136 |
|
|
|
137 |
eaf8ace4
|
Matej Zeman
|
def get_head_device(db: Session, head_id: int):
|
138 |
|
|
return db.query(models.HeadDevice).filter(models.HeadDevice.id == head_id).first()
|
139 |
|
|
|
140 |
|
|
|
141 |
|
|
def get_head_devices(db: Session, skip: int = 0, limit: int = 100):
|
142 |
|
|
return db.query(models.HeadDevice).offset(skip).limit(limit).all()
|
143 |
|
|
|
144 |
|
|
|
145 |
|
|
def find_head_device(db: Session, serial: schemas.HeadDeviceBase):
|
146 |
|
|
return db.query(models.HeadDevice).filter(models.HeadDevice.serial_number == serial.serial_number).first()
|
147 |
|
|
|
148 |
|
|
|
149 |
|
|
def create_head_device(db: Session, log: schemas.HeadDeviceBase):
|
150 |
|
|
db_head = models.HeadDevice(serial_number=log.serial_number)
|
151 |
|
|
db.add(db_head)
|
152 |
|
|
db.commit()
|
153 |
|
|
db.refresh(db_head)
|
154 |
|
|
return db_head
|
155 |
|
|
|
156 |
|
|
|
157 |
|
|
def get_body_device(db: Session, body_id: int):
|
158 |
|
|
return db.query(models.BodyDevice).filter(models.BodyDevice.id == body_id).first()
|
159 |
|
|
|
160 |
|
|
|
161 |
|
|
def get_body_devices(db: Session, skip: int = 0, limit: int = 100):
|
162 |
|
|
return db.query(models.BodyDevice).offset(skip).limit(limit).all()
|
163 |
|
|
|
164 |
|
|
|
165 |
|
|
def find_body_device(db: Session, serial: schemas.BodyDeviceBase):
|
166 |
|
|
return db.query(models.BodyDevice).filter(models.BodyDevice.serial_number == serial.serial_number).first()
|
167 |
|
|
|
168 |
|
|
|
169 |
|
|
def create_body_device(db: Session, log: schemas.BodyDeviceBase):
|
170 |
|
|
db_body = models.BodyDevice(serial_number=log.serial_number)
|
171 |
|
|
db.add(db_body)
|
172 |
|
|
db.commit()
|
173 |
|
|
db.refresh(db_body)
|
174 |
|
|
return db_body
|
175 |
|
|
|
176 |
|
|
|
177 |
|
|
def get_ld_logs(db: Session, skip: int = 0, limit: int = 100):
|
178 |
|
|
return db.query(models.LDLog).offset(skip).limit(limit).all()
|
179 |
|
|
|
180 |
|
|
|
181 |
|
|
def create_ld_logs(db: Session, item: schemas.LDTempBase, head_id: int, body_id: int, pc_id: int, date: datetime):
|
182 |
|
|
db_ld = models.LDLog(pc_id=pc_id, timestamp=date, status=item.status, head_id=head_id, body_id=body_id)
|
183 |
|
|
db.add(db_ld)
|
184 |
|
|
db.commit()
|
185 |
|
|
db.refresh(db_ld)
|
186 |
|
|
return db_ld
|
187 |
|
|
|
188 |
|
|
|
189 |
aba60b70
|
Matej Zeman
|
def get_logs(db: Session, skip: int = 0, limit: int = 100):
|
190 |
b6f0e019
|
Matej Zeman
|
return db.query(models.USBLog).order_by(desc(models.USBLog.timestamp)).offset(skip).limit(limit).all()
|
191 |
aba60b70
|
Matej Zeman
|
|
192 |
|
|
|
193 |
|
|
def get_log(db: Session, device_id: int, skip: int = 0, limit: int = 100):
|
194 |
|
|
return db.query(models.USBLog).filter(models.USBLog.device_id == device_id).offset(skip).limit(limit).all()
|
195 |
|
|
|
196 |
|
|
|
197 |
b6f0e019
|
Matej Zeman
|
def find_filtered_logs(db: Session, logs: []):
|
198 |
|
|
return db.query(models.USBLog).filter(models.USBLog.id.in_(logs)).order_by(desc(models.USBLog.timestamp)).all()
|
199 |
|
|
|
200 |
|
|
|
201 |
|
|
def get_filtered_logs(db: Session, pc: str, tema: str, lic: str):
|
202 |
|
|
execute_string = "SELECT * FROM usb_logs AS logs"
|
203 |
|
|
if pc != "all":
|
204 |
|
|
pcs = find_pc_by_username(db, pc)
|
205 |
|
|
execute_string += " WHERE logs.pc_id = " + str(pcs.id)
|
206 |
|
|
if tema != "all":
|
207 |
|
|
team = find_team(db, tema)
|
208 |
|
|
pcs = get_pcs_by_team(db, team.id)
|
209 |
|
|
pc_ids = "("
|
210 |
|
|
for p in pcs:
|
211 |
|
|
pc_ids += str(p.id) + ", "
|
212 |
|
|
def_pc_ids = pc_ids[:-2] + ")"
|
213 |
|
|
if pc != "all":
|
214 |
|
|
execute_string += " AND logs.pc_id IN " + def_pc_ids
|
215 |
|
|
else:
|
216 |
|
|
execute_string += " WHERE logs.pc_id IN " + def_pc_ids
|
217 |
|
|
if lic != "all":
|
218 |
|
|
license = find_license(db, lic)
|
219 |
|
|
device_licenses = get_license_devices(db, license.id)
|
220 |
|
|
dev_ids = "("
|
221 |
|
|
for dev in device_licenses:
|
222 |
|
|
dev_ids += str(dev.device_id) + ", "
|
223 |
|
|
defin_ids = dev_ids[:-2] + ")"
|
224 |
|
|
if pc != "all" or tema != "all":
|
225 |
|
|
execute_string += " AND logs.device_id IN " + defin_ids
|
226 |
|
|
else:
|
227 |
|
|
execute_string += " WHERE logs.device_id IN " + defin_ids
|
228 |
|
|
|
229 |
|
|
result = db.execute(execute_string)
|
230 |
|
|
return result
|
231 |
|
|
|
232 |
|
|
|
233 |
99c92c11
|
Matej Zeman
|
def create_device_logs(db: Session, item: schemas.USBTempBase, dev_id: int, pc_id: int, date: datetime):
|
234 |
|
|
db_log = models.USBLog(pc_id=pc_id, timestamp=date, status=item.status, device_id=dev_id)
|
235 |
aba60b70
|
Matej Zeman
|
db.add(db_log)
|
236 |
|
|
db.commit()
|
237 |
|
|
db.refresh(db_log)
|
238 |
|
|
return db_log
|