326 lines
7.4 KiB
Go
326 lines
7.4 KiB
Go
/*
|
|
* Sicherheitsunterweisung
|
|
* Copyright (C) 2023 Jason Streifling
|
|
*
|
|
* This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
|
|
*
|
|
* This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
|
|
*
|
|
* You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>.
|
|
*/
|
|
|
|
package data
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
"strings"
|
|
|
|
"github.com/go-sql-driver/mysql"
|
|
)
|
|
|
|
func OpenDB(dbName string) (*DB, error) {
|
|
var err error
|
|
db := new(DB)
|
|
|
|
cfg := mysql.NewConfig()
|
|
cfg.DBName = dbName
|
|
cfg.User, cfg.Passwd, err = getCredentials()
|
|
if err != nil {
|
|
return nil, fmt.Errorf("error: OpenDB: getCredentials(): %v", err)
|
|
}
|
|
|
|
db.DB, err = sql.Open("mysql", cfg.FormatDSN())
|
|
if err != nil {
|
|
return nil, fmt.Errorf("error: OpenDB: sql.Open(\"mysql\", cfg.FormatDSN()): %v", err)
|
|
}
|
|
if err := db.Ping(); err != nil {
|
|
return nil, fmt.Errorf("error: OpenDB: db.Ping(): %v", err)
|
|
}
|
|
|
|
return db, nil
|
|
}
|
|
|
|
func (db *DB) WriteBriefing(b *Briefing) error {
|
|
query := `
|
|
INSERT INTO briefings
|
|
(datetime, location, document, as_of, instructor_id)
|
|
VALUES
|
|
(?, ?, ?, ?, ?)
|
|
`
|
|
|
|
result, err := db.Exec(query, b.DateTime, b.Location, b.Document, b.AsOf, b.InstructorID)
|
|
if err != nil {
|
|
return fmt.Errorf("error: *DB.writeBriefing: db.Exec(): %v", err)
|
|
}
|
|
|
|
b.ID, err = result.LastInsertId()
|
|
if err != nil {
|
|
return fmt.Errorf("error: *DB.writeBriefing: result.LastInsertId(): %v", err)
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func (db *DB) WriteParticipant(p *Participant) error {
|
|
query := `
|
|
INSERT INTO participants
|
|
(first_name, last_name, company)
|
|
VALUES
|
|
(?, ?, ?)
|
|
`
|
|
|
|
result, err := db.Exec(query, p.FirstName, p.LastName, p.Company)
|
|
if err != nil {
|
|
return fmt.Errorf("error: *DB.writeParticipants: db.Exec(): %v", err)
|
|
}
|
|
|
|
p.ID, err = result.LastInsertId()
|
|
if err != nil {
|
|
return fmt.Errorf("error: *DB.writeParticipants: result.LastInsertId(): %v", err)
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func (db *DB) WriteGivenAnswers(b Briefing, p Participant, sq []Question, givenAnswers []int) error {
|
|
query := `
|
|
INSERT INTO given_answers
|
|
(briefing_id, participant_id, question_id, given_answer)
|
|
VALUES
|
|
(?, ?, ?, ?)
|
|
`
|
|
|
|
for i, q := range sq {
|
|
_, err := db.Exec(query, b.ID, p.ID, q.ID, givenAnswers[i])
|
|
if err != nil {
|
|
return fmt.Errorf("error: *DB.WriteGivenAnswers: db.Exec(): %v", err)
|
|
}
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func (db *DB) GetAllOverviewTableData() ([]OverviewTableData, error) {
|
|
query := `
|
|
SELECT
|
|
i.first_name,
|
|
i.last_name,
|
|
b.datetime,
|
|
b.location,
|
|
b.document,
|
|
b.as_of,
|
|
p.first_name,
|
|
p.last_name,
|
|
p.company
|
|
FROM given_answers AS g
|
|
INNER JOIN briefings AS b
|
|
ON b.id = g.briefing_id
|
|
INNER JOIN participants AS p
|
|
ON p.id = g.participant_id
|
|
INNER JOIN questions AS q
|
|
ON q.id = g.question_id
|
|
INNER JOIN instructors AS i
|
|
ON i.id = b.instructor_id
|
|
WHERE
|
|
q.id = 1
|
|
ORDER BY
|
|
b.id DESC,
|
|
p.id
|
|
`
|
|
|
|
rows, err := db.Query(query)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("error: *DB.ReadAllBriefings: db.Query(): %v", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
data := make([]OverviewTableData, 0)
|
|
for rows.Next() {
|
|
otd := OverviewTableData{}
|
|
|
|
err := rows.Scan(
|
|
&otd.InstructorFirstName,
|
|
&otd.InstructorLastName,
|
|
&otd.BriefingDateTime,
|
|
&otd.BriefingLocation,
|
|
&otd.BriefingDocument,
|
|
&otd.BriefingAsOf,
|
|
&otd.ParticipantFirstName,
|
|
&otd.ParticipantLastName,
|
|
&otd.ParticipantCompany,
|
|
)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("error: *DB.ReadAllBriefings: rows.Scan(): %v", err)
|
|
}
|
|
|
|
data = append(data, otd)
|
|
}
|
|
|
|
return data, nil
|
|
}
|
|
|
|
func (db *DB) GetOverviewTableDataByName(n string) ([]OverviewTableData, error) {
|
|
query := `
|
|
SELECT
|
|
i.first_name,
|
|
i.last_name,
|
|
b.datetime,
|
|
b.location,
|
|
b.document,
|
|
b.as_of,
|
|
p.first_name,
|
|
p.last_name,
|
|
p.company
|
|
FROM given_answers AS g
|
|
INNER JOIN briefings AS b
|
|
ON b.id = g.briefing_id
|
|
INNER JOIN participants AS p
|
|
ON p.id = g.participant_id
|
|
INNER JOIN questions AS q
|
|
ON q.id = g.question_id
|
|
INNER JOIN instructors AS i
|
|
ON i.id = b.instructor_id
|
|
WHERE
|
|
q.id = 1 AND
|
|
(
|
|
i.first_name LIKE ? OR
|
|
i.last_name LIKE ? OR
|
|
p.first_name LIKE ? OR
|
|
p.last_name LIKE ?
|
|
)
|
|
ORDER BY
|
|
b.id DESC,
|
|
p.id
|
|
`
|
|
|
|
rows, err := db.Query(query, "%"+n+"%", "%"+n+"%", "%"+n+"%", "%"+n+"%")
|
|
if err != nil {
|
|
return nil, fmt.Errorf("error: *DB.GetOverviewTableDataByName: db.Query(): %v", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
data := make([]OverviewTableData, 0)
|
|
for rows.Next() {
|
|
otd := OverviewTableData{}
|
|
|
|
err := rows.Scan(
|
|
&otd.InstructorFirstName,
|
|
&otd.InstructorLastName,
|
|
&otd.BriefingDateTime,
|
|
&otd.BriefingLocation,
|
|
&otd.BriefingDocument,
|
|
&otd.BriefingAsOf,
|
|
&otd.ParticipantFirstName,
|
|
&otd.ParticipantLastName,
|
|
&otd.ParticipantCompany,
|
|
)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("error: *DB.ReadAllBriefings: rows.Scan(): %v", err)
|
|
}
|
|
|
|
data = append(data, otd)
|
|
}
|
|
|
|
return data, nil
|
|
}
|
|
|
|
func (db *DB) GetLastID(table string) (int, error) {
|
|
var id int
|
|
query := `
|
|
SELECT id
|
|
FROM ?
|
|
ORDER BY id DESC
|
|
LIMIT 0, 1
|
|
`
|
|
|
|
row := db.QueryRow(query, table)
|
|
if err := row.Scan(&id); err != nil {
|
|
return -1, fmt.Errorf("error: *DB.GetLastID: row.Scan(): %v", err)
|
|
}
|
|
|
|
return id, nil
|
|
}
|
|
|
|
func (db *DB) GetInstructors() ([]*Instructor, error) {
|
|
query := `
|
|
SELECT *
|
|
FROM instructors
|
|
ORDER BY
|
|
last_name,
|
|
first_name
|
|
`
|
|
|
|
rows, err := db.Query(query)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("error: *DB.GetInstructors: db.Query(): %v", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
instructors := make([]*Instructor, 0)
|
|
for rows.Next() {
|
|
instructor := new(Instructor)
|
|
|
|
if err = rows.Scan(&instructor.ID, &instructor.FirstName, &instructor.LastName); err != nil {
|
|
return nil, fmt.Errorf("error: *DB.GetInstructors: rows.Scan(): %v", err)
|
|
}
|
|
|
|
instructors = append(instructors, instructor)
|
|
}
|
|
|
|
return instructors, nil
|
|
}
|
|
|
|
func (db *DB) GetQuestions(nums []string) ([]Question, error) {
|
|
query := `
|
|
SELECT *
|
|
FROM questions
|
|
WHERE id IN (` + strings.Join(nums, ", ") + `)
|
|
`
|
|
|
|
rows, err := db.Query(query)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("error: *DB.GetQuestions: db.Query(): %v", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
// TODO: not scalable
|
|
questions := make([]Question, 0)
|
|
for rows.Next() {
|
|
q := Question{Answers: make([]Answer, 4)}
|
|
q.Answers[0].ID = 1
|
|
q.Answers[1].ID = 2
|
|
q.Answers[2].ID = 3
|
|
q.Answers[3].ID = 4
|
|
|
|
if err := rows.Scan(&q.ID, &q.Text, &q.Answers[0].Text, &q.Answers[1].Text, &q.Answers[2].Text, &q.Answers[3].Text, &q.Correct); err != nil {
|
|
return nil, fmt.Errorf("error: *DB.GetQuestions: rows.Scan(): %v", err)
|
|
}
|
|
|
|
questions = append(questions, q)
|
|
}
|
|
|
|
return questions, nil
|
|
}
|
|
|
|
func (db *DB) GetGivenAnswers(bid, pid int64, sq []Question) ([]int, error) {
|
|
answers := make([]int, len(sq))
|
|
query := `
|
|
SELECT given_answer
|
|
FROM given_answers
|
|
WHERE
|
|
briefing_id = ? AND
|
|
participant_id = ? AND
|
|
question_id = ?
|
|
`
|
|
|
|
for i, q := range sq {
|
|
row := db.QueryRow(query, bid, pid, q.ID)
|
|
if err := row.Scan(&answers[i]); err != nil {
|
|
return nil, fmt.Errorf("error: *DB.GetGivenAnswers: row.Scan(): %v", err)
|
|
}
|
|
}
|
|
|
|
return answers, nil
|
|
}
|