Revize 99c92c11

re #9429 Added class and funcionality for Teams table. Modified date parsing and storing. Modified View at /usb-logs/. Replaced "_" with "-" in all endpoints.

server/sql_app/api/devices.py

@@ -23 +23 @@
         db.close()
 
 
[e-mail]("/devices_web/", response_class=HTMLResponse)
[e-mail]("/devices-web/", response_class=HTMLResponse)
 async def read_devices(request: Request, skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
     devices = crud.get_devices(db, skip=skip, limit=limit)
     return templates.TemplateResponse("devices.html", {"request": request, "devs": devices})

server/sql_app/api/licenses.py

@@ -2 +2 @@
 
 from fastapi import Depends, FastAPI, HTTPException, APIRouter
 from sqlalchemy.orm import Session
+from datetime import date
 from ...sql_app import crud, models, schemas
 from ..database import SessionLocal, engine
 from fastapi import FastAPI, Request
@@ -24 +25 @@
         db.close()
 
 
[e-mail]("/licenses_web/", response_class=HTMLResponse)
[e-mail]("/licenses-web/", response_class=HTMLResponse)
 async def read_pcs(request: Request, skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
     licenses = crud.get_licenses(db, skip=skip, limit=limit)
     return templates.TemplateResponse("licenses.html", {"request": request, "licenses": licenses})
@@ -32 +33 @@
 
 @licenses.post("/license/", response_model=schemas.License)
 def create_license(license: schemas.LicenseCreate, db: Session = Depends(get_db)):
-    print(crud.create_license(db=db, license=license))
+    print(crud.create_license(db=db, name=license.name, expdate=license.expiration_date))
 
 
 @licenses.get("/licenses/", response_model=List[schemas.License])
@@ -41 +42 @@
     return licenses
 
 
[e-mail]("/device_license/", response_model=schemas.DeviceLicense)
[e-mail]("/device-license/", response_model=schemas.DeviceLicense)
 def create_device_license(device_license: schemas.DeviceLicenseCreate, db: Session = Depends(get_db)):
     print(crud.create_device_license(db=db, device_license=device_license))

server/sql_app/api/pcs.py

@@ -23 +23 @@
         db.close()
 
 
[e-mail]("/pcs_web/", response_class=HTMLResponse)
[e-mail]("/pcs-web/", response_class=HTMLResponse)
 async def read_pcs(request: Request, skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
     pcs = crud.get_pcs(db, skip=skip, limit=limit)
     return templates.TemplateResponse("pcs.html", {"request": request, "pcs": pcs})

server/sql_app/api/teams.py

@@ +1 @@
+from typing import List
+
+from fastapi import Depends, FastAPI, HTTPException, APIRouter
+from sqlalchemy.orm import Session
+from ...sql_app import crud, models, schemas
+from ..database import SessionLocal, engine
+from fastapi import FastAPI, Request
+from fastapi.responses import HTMLResponse
+from fastapi.staticfiles import StaticFiles
+from fastapi.templating import Jinja2Templates
+
+models.Base.metadata.create_all(bind=engine)
+templates = Jinja2Templates(directory="../templates/teams")
+
+teams = APIRouter()
+
+# Dependency
+def get_db():
+    db = SessionLocal()
+    try:
+        yield db
+    finally:
+        db.close()
+
+
[e-mail]("/teams-web/", response_class=HTMLResponse)
+async def read_devices(request: Request, skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
+    teams = crud.get_teams(db, skip=skip, limit=limit)
+    return templates.TemplateResponse("teams.html", {"request": request, "teams": teams})
+
+
[e-mail]("/team/", response_model=schemas.Team)
+def create_device(team: schemas.TeamCreate, db: Session = Depends(get_db)):
+    print(crud.create_team(db=db, name=team.name))
+
+
[e-mail]("/teams/", response_model=List[schemas.Device])
+def read_devices(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
+    teams = crud.get_teams(db, skip=skip, limit=limit)
+    return teams
+
+
[e-mail]("/team/{team_id}", response_model=schemas.Device)
+def read_device(team_id: int, db: Session = Depends(get_db)):
+    db_team = crud.get_team(db, team_id=team_id)
+    if db_team is None:
+        raise HTTPException(status_code=404, detail="Device not found")
+    return db_team

server/sql_app/api/usb_logs.py

@@ -1 +1 @@
 from typing import List
 from fastapi import Depends, FastAPI, HTTPException, APIRouter, Form
+from datetime import datetime
 from sqlalchemy.orm import Session
 from ...sql_app import crud, models, schemas
 from ..database import SessionLocal, engine
@@ -23 +24 @@
         db.close()
 
 
[e-mail]("/logs_web/", response_class=HTMLResponse)
[e-mail]("/logs-web/", response_class=HTMLResponse)
 async def read_logs(request: Request, skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
     logs = crud.get_logs(db, skip=skip, limit=limit)
-    pcs = []
-    for log in logs:
-        if log.pc_id not in pcs:
-            pcs.append(log.pc_id)
     return templates.TemplateResponse("logs.html", {"request": request, "logs": logs})
 
 
 @usblogs.post("/usb-logs/", response_model=schemas.USBLog)
 def create_device_logs(log: schemas.USBTempBase, db: Session = Depends(get_db)):
     dev = crud.find_device(db, log.device)
+    dat = datetime.strptime(log.timestamp, '%Y-%m-%d %H:%M:%S.%f')
     if dev is None:
         dev = crud.create_device(db=db, device=log.device)
     pc = crud.find_pc(db, log.username, log.hostname)
     if pc is None:
         pc = crud.create_pc(db=db, user=log.username, host=log.hostname)
 
-    print(crud.create_device_logs(db=db, item=log, dev_id=dev.id, pc_id=pc.id))
+    print(crud.create_device_logs(db=db, item=log, dev_id=dev.id, pc_id=pc.id, date=dat))
 
 
 @usblogs.get("/logs/", response_model=List[schemas.USBLog])

server/sql_app/crud.py

@@ -1 @@
-from sqlalchemy.orm import Session
+from datetime import datetime, date
 
+from sqlalchemy.orm import Session
+from sqlalchemy import and_
 from . import models, schemas
 
 
@@ -12 +14 @@
 
 
 def find_device(db: Session, device: schemas.DeviceBase):
-    return db.query(models.Device).filter(models.Device.product_id == device.product_id and
-                                          models.Device.vendor_id == device.vendor_id and
-                                          models.Device.serial_number == device.serial_number).first()
+    return db.query(models.Device).filter(and_(models.Device.product_id == device.product_id,
+                                          models.Device.vendor_id == device.vendor_id,
+                                          models.Device.serial_number == device.serial_number)).first()
 
 
 def create_device(db: Session, device: schemas.DeviceBase):
     db_device = models.Device(vendor_id=device.vendor_id, product_id=device.product_id,
-                              serial_number=device.serial_number)
+                              serial_number=device.serial_number, assigned=False)
     db.add(db_device)
     db.commit()
     db.refresh(db_device)
@@ -34 +36 @@
     return db.query(models.License).offset(skip).limit(limit).all()
 
 
-def create_license(db: Session, license: schemas.LicenseBase):
-    db_license = models.License(name=license.name, expiration_date=license.expiration_date)
+def create_license(db: Session, name: str, expdate: date):
+    db_license = models.License(name=name, expiration_date=expdate)
     db.add(db_license)
     db.commit()
     db.refresh(db_license)
@@ -60 +62 @@
 
 
 def find_pc(db: Session, username: str, hostname: str):
-    return db.query(models.PC).filter(models.PC.username == username and
-                                      models.PC.hostname == hostname).first()
+    return db.query(models.PC).filter(and_(models.PC.username == username,
+                                      models.PC.hostname == hostname)).first()
+
+
+def find_pcs(db: Session, pcs: []):
+    return db.query(models.PC).filter(models.PC.id.in_(pcs)).all()
 
 
 def create_pc(db: Session, user: str, host: str):
-    db_pc = models.PC(username=user, hostname=host)
+    db_pc = models.PC(username=user, hostname=host, assigned=False)
     db.add(db_pc)
     db.commit()
     db.refresh(db_pc)
     return db_pc
 
 
+def get_team(db: Session, team_id: int):
+    return db.query(models.Team).filter(models.Team.id == team_id).first()
+
+
+def get_teams(db: Session, skip: int = 0, limit: int = 100):
+    return db.query(models.Team).offset(skip).limit(limit).all()
+
+
+def create_team(db: Session, name: str):
+    db_team = models.PC(name=name)
+    db.add(db_team)
+    db.commit()
+    db.refresh(db_team)
+    return db_team
+
+
 def get_logs(db: Session, skip: int = 0, limit: int = 100):
     return db.query(models.USBLog).offset(skip).limit(limit).all()
 
@@ -80 +102 @@
     return db.query(models.USBLog).filter(models.USBLog.device_id == device_id).offset(skip).limit(limit).all()
 
 
-def create_device_logs(db: Session, item: schemas.USBTempBase, dev_id: int, pc_id: int):
-    db_log = models.USBLog(pc_id=pc_id, timestamp=item.timestamp, status=item.status, device_id=dev_id)
+def create_device_logs(db: Session, item: schemas.USBTempBase, dev_id: int, pc_id: int, date: datetime):
+    db_log = models.USBLog(pc_id=pc_id, timestamp=date, status=item.status, device_id=dev_id)
     db.add(db_log)
     db.commit()
     db.refresh(db_log)
     return db_log
+

server/sql_app/main.py

@@ -3 +3 @@
 from server.sql_app.api.licenses import licenses
 from server.sql_app.api.pcs import pcs
 from server.sql_app.api.usb_logs import usblogs
+from server.sql_app.api.teams import teams
 from fastapi import FastAPI
 
 
@@ -11 +12 @@
 app.include_router(licenses)
 app.include_router(pcs)
 app.include_router(usblogs)
+app.include_router(teams)
 
 if __name__ == "__main__":
     uvicorn.run(app, host="192.168.0.22", port=8000)

server/sql_app/models.py

@@ -1 @@
-from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
+from sqlalchemy import Boolean, Column, ForeignKey, Integer, String, DateTime
 from sqlalchemy.orm import relationship
-
+from sqlalchemy.sql import func
 from .database import Base
 
 
@@ -8 +8 @@
     __tablename__ = "devices"
 
     id = Column(Integer, primary_key=True, index=True)
-    vendor_id = Column(Integer, index=True, nullable=False)
-    product_id = Column(Integer, index=True, nullable=False)
+    vendor_id = Column(String, index=True, nullable=False)
+    product_id = Column(String, index=True, nullable=False)
     serial_number = Column(String, index=True, nullable=False)
+    assigned = Column(Boolean, index=True, nullable=False)
 
     logs = relationship("USBLog", back_populates="device")
     licenses = relationship("DeviceLicense", back_populates="device_lic")
@@ -21 +22 @@
 
     id = Column(Integer, primary_key=True, index=True)
     pc_id = Column(Integer, ForeignKey("pc.id"))
-    timestamp = Column(String, index=True, nullable=False)
+    timestamp = Column(DateTime(timezone=True), server_default=func.now())
     status = Column(String, index=True, nullable=False)
     device_id = Column(Integer, ForeignKey("devices.id"))
 
@@ -34 +35 @@
 
     id = Column(Integer, primary_key=True, index=True)
     name = Column(String, index=True, nullable=False)
-    expiration_date = Column(String, index=True, nullable=False)
+    expiration_date = Column(DateTime(timezone=True), server_default=func.now())
 
     devices = relationship("DeviceLicense", back_populates="licenses")
 
@@ -43 +44 @@
     __tablename__ = "devices_licenses"
 
     id = Column(Integer, primary_key=True, index=True)
-    device_id = Column(Integer, ForeignKey("devices.id"))
-    license_id = Column(Integer, ForeignKey("licenses.id"))
+    device_id = Column(String, ForeignKey("devices.id"))
+    license_id = Column(String, ForeignKey("licenses.id"))
     assigned_datetime = Column(String, index=True, nullable=False)
 
     device_lic = relationship("Device", back_populates="licenses")
@@ -57 +58 @@
     id = Column(Integer, primary_key=True, index=True)
     username = Column(String, index=True, nullable=False)
     hostname = Column(String, index=True, nullable=False)
+    assigned = Column(Boolean, index=True, nullable=False)
+    team_id = Column(Integer, ForeignKey("teams.id"))
+
+    team = relationship("Team", back_populates="pcs")
     logs_pc = relationship("USBLog", back_populates="pc")
+
+
+class Team(Base):
+    __tablename__ = "teams"
+
+    id = Column(Integer, primary_key=True, index=True)
+    name = Column(String, index=True, nullable=False)
+    pcs = relationship("PC", back_populates="team")

server/sql_app/schemas.py

@@ -1 +1 @@
 from typing import List, Optional
-
+from datetime import datetime, date
 from pydantic import BaseModel
 
 
-
 class DeviceLicenseBase(BaseModel):
     device_id: int
     license_id: int
@@ -22 +21 @@
 
 
 class USBLogBase(BaseModel):
-    timestamp: str
+    timestamp: datetime
     status: str
 
 
@@ -40 +39 @@
 
 
 class DeviceBase(BaseModel):
-    vendor_id: int
-    product_id: int
+    vendor_id: str
+    product_id: str
     serial_number: str
 
 
@@ -49 +48 @@
     pass
 
 
-class Device(DeviceBase):
+class Device(DeviceCreate):
     id: int
+    assigned: bool
     logs: List[USBLog] = []
     licenses: List[DeviceLicense] = []
 
@@ -69 +69 @@
 
 class PC(PCCreate):
     id: int
+    assigned: bool
     logs_pc: List[USBLog] = []
 
     class Config:
         orm_mode = True
 
 
+class TeamBase(BaseModel):
+    name: str
+
+
+class TeamCreate(TeamBase):
+    pass
+
+
+class Team(TeamCreate):
+    id: int
+    pcs: List[PC] = []
+
+    class Config:
+        orm_mode = True
+
+
 class LicenseBase(BaseModel):
     name: str
-    expiration_date: str
+    expiration_date: date
 
 
 class LicenseCreate(LicenseBase):

server/templates/teams/teams.html

@@ +1 @@
+<html>
+<head>
+    <title>Teams Details</title>
+</head>
+<body>
+<table>
+    <TR>
+        <TH>ID</TH>
+        <TH>Name</TH>
+    </TR>
+    {% for team in teams %}
+    <TR>
+        <TD class="ID">{{team.id}}</TD>
+        <TD class="Vendor ID">{{team.name}}</TD>
+    </TR>
+    {% endfor %}
+</table>
+</body>
+</html>

server/templates/usb-logs/logs.html

@@ -6 +6 @@
     <table>
     <TR>
         <TH>ID</TH>
-        <TH>PC ID</TH>
+        <TH>PC Username</TH>
+        <TH>PC Hostname</TH>
         <TH>Timestamp</TH>
         <TH>Status</TH>
-        <TH>Device ID</TH>
+        <TH>Device Product ID</TH>
+        <TH>Device Serial Number</TH>
     </TR>
     {% for log in logs %}
     <TR>
         <TD class="ID">{{log.id}}</TD>
-        <TD class="Username">{{log.pc_id}}</TD>
+        <TD class="Username">{{log.pc.username}}</TD>
+        <TD class="Hostname">{{log.pc.hostname}}</TD>
         <TD class="Timestamp">{{log.timestamp}}</TD>
         <TD class="Status">{{log.status}}</TD>
-        <TD class="Device ID">{{log.device_id}}</TD>
+        <TD class="DeviceProductID">{{log.device.product_id}}</TD>
+        <TD class="DeviceSerialNumber">{{log.device.serial_number}}</TD>
     </TR>
     {% endfor %}
 </table>