-- SPDX-FileCopyrightText: 2022-23 Sarah Vaupel ,Steffen Jost -- -- 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