165 lines
10 KiB
Plaintext
165 lines
10 KiB
Plaintext
-- SPDX-FileCopyrightText: 2022-23 Sarah Vaupel <sarah.vaupel@ifi.lmu.de>,Steffen Jost <jost@tcs.ifi.lmu.de>
|
|
--
|
|
-- SPDX-License-Identifier: AGPL-3.0-or-later
|
|
|
|
Qualification
|
|
-- INVARIANT: 2*refreshWithin < validDuration
|
|
school SchoolId --TODO: Ansprechpartner der Schule in Briefe erwähnen
|
|
shorthand (CI Text)
|
|
name (CI Text)
|
|
description StoredMarkup Maybe -- user-defined large Html, ought to contain full description
|
|
validDuration Int Maybe -- > 0, qualification is valid indefinitely or for a specified number of months, use with addMonthsDay
|
|
auditDuration Int Maybe -- > 0, number of months to keep audit log and LmsUserIdents; or indefinitely (dangerous, since LmsIdents may run out)
|
|
refreshWithin CalendarDiffDays Maybe -- notify users about renewal within this number of month/days before expiry; to be used with addGregorianDurationClip
|
|
refreshReminder CalendarDiffDays Maybe -- send a second notification about renewal within this number of month/days before expiry
|
|
elearningStart Bool -- automatically schedule e-refresher
|
|
-- elearningOnly Bool -- successful E-learing automatically increases validity. NO!
|
|
expiryNotification Bool default=true -- should expiryNotification be generated for this qualification?
|
|
avsLicence AvsLicence Maybe -- if set, valid QualificationUsers are synchronized to AVS as a driving licence
|
|
sapId Text Maybe -- if set, valid QualificationUsers with userCompanyPersonalNumber are transmitted via SAP interface under this id
|
|
SchoolQualificationShort school shorthand -- must be unique per school and shorthand
|
|
SchoolQualificationName school name -- must be unique per school and name
|
|
-- across all schools, only one qualification may be a driving licence:
|
|
UniqueQualificationAvsLicence avsLicence !force -- either empty or unique
|
|
-- NOTE: two NULL values are not equal for the purpose of Uniqueness constraints!
|
|
deriving Eq Generic
|
|
|
|
-- TODOs:
|
|
-- - Enstehen Kosten, wenn Teilnehmer für KnowHow eingereiht werden, aber nicht am Kurs teilnehmen?
|
|
-- Falls ja, so sollte bei automatischem refresher vorher der Kunde durch FRADrive befragt werden?!
|
|
-- A: Der Inhaber per Email informieren!
|
|
-- A: Es kann gleich eine LMS Pin generiert und verschickt werden!
|
|
-- - Aufteilung Qualification "R" in zwei Teile: "R e-learning" und "R praxis" okay?
|
|
|
|
-- Besonderheiten:
|
|
-- - LmsIdent muss für alle Qualificationen einzigartig sein!
|
|
-- - Durchfallen wird mit UserList ständig erneut gesandt, bis Löschantrag gestellt wurde.
|
|
-- - Bestehen mit Result wird nur ein einziges mal gesendet! (Ausfallrisiko: keine Bestätigung der Kommunikation!)
|
|
-- - Explizites Löschen eines LmsIdent nach Success/Failure ist notwendig (feedback bei Block)
|
|
-- - LmsUser soll nur DELTA übermitteln. (GET Request will always return the same; until POST Request was processed!)
|
|
-- - PinReset==1 mit bestehendem Passwort kann problemlos erneut gesendet werden
|
|
-- - Flag "interner Mitarbeiter" wird von Know-How ignoriert / nicht ausgewertet (legacy)
|
|
|
|
-- QualificationPrecondition -- NOTE: this can only be enforced through a background job adding or removing qualifications
|
|
-- qualification QualificationId OnDeleteCascade OnUpdateCascade -- AND: not unique, ie. qualification can have multiple required preconditions
|
|
-- required [QualificationId] -- OR : alternatives, any one will suffice
|
|
-- continuous Bool -- expiring precondition blocks qualification
|
|
-- deriving Generic
|
|
|
|
-- Maybe an alternative for online qualification validity checking, transitivity through recursive CTEs? (already available in our version)
|
|
--QualificationRequirement
|
|
-- qualification QualificationId OnDeleteCascade OnUpdateCascade
|
|
-- requirement QualificationId OnDeleteCascade OnUpdateCascade
|
|
-- group Text -- OR: several requirements within the same group are considered equivalent
|
|
-- UniqueQualificationRequirement qualification requirement
|
|
-- deriving Generic
|
|
--
|
|
|
|
-- TODO: connect Qualifications with Exams!?
|
|
|
|
QualificationEdit
|
|
user UserId
|
|
time UTCTime
|
|
qualification QualificationId OnDeleteCascade OnUpdateCascade
|
|
deriving Generic
|
|
|
|
QualificationUser
|
|
user UserId OnDeleteCascade OnUpdateCascade
|
|
qualification QualificationId OnDeleteCascade OnUpdateCascade
|
|
validUntil Day -- addGregorianMonthsRollOver (toInteger renewalMonths) qualificationUserValidUntil
|
|
lastRefresh Day -- lastRefresh > validUntil possible, if Qualification^elearningOnly == False
|
|
firstHeld Day -- first time the qualification was earned, should never change
|
|
scheduleRenewal Bool default=true -- if false, no automatic renewal is scheduled and the qualification expires
|
|
lastNotified UTCTime default=now() -- last notficiation about being invalid
|
|
-- Reasons and temporary revocations are implemented through QualificationUserBlock
|
|
-- TODO: adjust SAP interface to transmit end dates
|
|
UniqueQualificationUser qualification user
|
|
deriving Generic
|
|
|
|
QualificationUserBlock
|
|
qualificationUser QualificationUserId OnDeleteCascade OnUpdateCascade
|
|
unblock Bool
|
|
from UTCTime
|
|
reason Text
|
|
blocker UserId Maybe
|
|
-- precondition Bool default=false -- if true, this was due to a precondition
|
|
deriving Eq Ord Read Show Generic
|
|
|
|
-- LMS Interface Tables, need regular processing by background jobs, per QualificationId:
|
|
--
|
|
-- 1. Daily Job: Add to LmsUser daily all qualification holders with
|
|
-- QualificationUserValidUntil >= now
|
|
-- /\ QualificationUserValudUntil <= now + QualificationRefreshWithin (time to schedule refresher)
|
|
-- /\ not already enlisted
|
|
--
|
|
-- 2. REST GET User.csv:
|
|
-- - where LmsUserReceived == Nothing \/ (LmsUserResetPin /\ LmsUserEnded == Nothing)
|
|
-- - delete-flag: isJust LmsUserStatus
|
|
-- Note: REST means that LmsUserResetPin and LmsUserDelete remain unchanged by this GET request!
|
|
--
|
|
-- 3. REST POST Report.csv: just save as is to LmsReport for later processing
|
|
--
|
|
-- 4. When received: Job LmsReport: -- Note: containment needs at-once processing
|
|
-- - For all LmsUser:
|
|
-- + if contained:
|
|
-- set LmsUserReceived to Just now()
|
|
-- if Failed: set LmsUserStatus to Just LmsBlocked now
|
|
-- if Success: set LmsUserStatus to Just LmsSuccess now
|
|
-- and renew QualificationValidTo
|
|
-- + not contained, by LmsUserReceived is set: set LmsUserEnded to Just now()
|
|
-- - move row to LmsAudit
|
|
--
|
|
-- 5. Daily Job: dequeue LMS Users
|
|
-- - fail and mark expired LmsUser
|
|
-- - remove from LmsUser after audit Period has passed
|
|
|
|
LmsUser
|
|
qualification QualificationId OnDeleteCascade OnUpdateCascade
|
|
user UserId OnDeleteCascade OnUpdateCascade
|
|
ident LmsIdent -- must be unique accross all LMS courses!
|
|
pin Text
|
|
resetPin Bool default=false -- should pin be reset?
|
|
datePin UTCTime default=now() -- time pin was created
|
|
status LmsStatus Maybe -- Nothing=open, LmsSuccess, LmsBlocked or LmsExpired; status should never change unless isNothing; isJust indicates lms is finished and user shall be deleted from LMS
|
|
--toDelete encoded by Handler.Utils.LMS.lmsUserToDelete
|
|
statusDay UTCTime Maybe -- last status change; should be isJust iff isJust status; modelling as a separate table too bothersome, unlike qualification block
|
|
started UTCTime default=now()
|
|
received UTCTime Maybe -- last acknowledgement by LMS
|
|
notified UTCTime Maybe -- last notified by FRADrive
|
|
ended UTCTime Maybe -- ident was deleted from LMS
|
|
resetTries Bool default=false -- V2 should e-learning exam tries be reset?
|
|
locked Bool default=false -- V2 last returned lock status
|
|
-- Primary ident -- newtype Key LmsUserId = LmsUserKey { unLmsUser :: Text } -- change LmsIdent -> Text. Do we want this? No.
|
|
UniqueLmsIdent ident -- idents must be unique accross all qualifications, since idents are global within LMS!
|
|
UniqueLmsQualificationUser qualification user -- each user may be enrolled at most once per course
|
|
deriving Generic
|
|
|
|
-- LmsUserStatus
|
|
-- lmsUser LmsUserId OnDeleteCascade OnUpdateCascade
|
|
-- result LmsStatus -- data LmsStatus = LmsBlocked | LmsExpired | LmsSuccess
|
|
-- day Day
|
|
-- UniqueLmsUserStatus lmsUser -- enforcing uniqueness prohibits history
|
|
-- deriving Generic
|
|
|
|
-- V2 Stores LMS upload for processing in Background Job
|
|
LmsReport
|
|
qualification QualificationId OnDeleteCascade OnUpdateCascade
|
|
ident LmsIdent
|
|
date UTCTime Maybe -- BEWARE: timezone is local as submitted by LMS
|
|
result LmsState -- (0|1|2) 0=LmsFailed[too many tries], 1=LmsOpen, 2=LmsPassed[success]
|
|
lock Bool -- (0|1)
|
|
timestamp UTCTime default=now()
|
|
UniqueLmsReport qualification ident -- required by DBTable
|
|
deriving Generic
|
|
|
|
-- LmsAudit removed by commit 71cde92a
|
|
-- due to frequent transmit errors, a separate lms tranmission log is necessary again
|
|
LmsReportLog
|
|
qualification QualificationId OnDeleteCascade OnUpdateCascade
|
|
ident LmsIdent
|
|
date UTCTime Maybe -- BEWARE: timezone is local as submitted by LMS
|
|
result LmsState -- (0|1|2) 0=LmsFailed[too many tries], 1=LmsOpen, 2=LmsPassed[success]
|
|
lock Bool -- (0|1)
|
|
timestamp UTCTime default=now()
|
|
missing Bool default=false
|
|
deriving Generic |