111 lines
2.5 KiB
Go
111 lines
2.5 KiB
Go
package handlers
|
|
|
|
import (
|
|
"database/sql"
|
|
"errors"
|
|
"fmt"
|
|
"strings"
|
|
|
|
"gitea.tecamino.com/paadi/memberDB/models"
|
|
)
|
|
|
|
func (dh *DatabaseHandler) CreateNewResponsibleTable() error {
|
|
createTableSQL := `
|
|
CREATE TABLE IF NOT EXISTS responsible (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
first_name TEXT NOT NULL,
|
|
last_name TEXT UNIQUE NOT NULL
|
|
);`
|
|
|
|
_, err := dh.database.Exec(createTableSQL)
|
|
if err != nil {
|
|
return fmt.Errorf("failed to create table: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (dh *DatabaseHandler) AddNewResponsible(responsibles ...models.Person) error {
|
|
for _, responsible := range responsibles {
|
|
|
|
exists, err := dh.responsibleExists(responsible)
|
|
if err != nil {
|
|
return err
|
|
} else if exists {
|
|
continue
|
|
}
|
|
|
|
_, err = dh.database.Exec("INSERT INTO responsible (first_name, last_name) VALUES (?, ?)", responsible.FirstName, responsible.LastName)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (dh *DatabaseHandler) GetResponsible(id int) (persons []models.Person, err error) {
|
|
var args any
|
|
query := `SELECT id, first_name, last_name FROM responsible`
|
|
|
|
if id > 0 {
|
|
query = ` WHERE id = ?`
|
|
args = id
|
|
}
|
|
|
|
rows, err := dh.database.Query(query, args)
|
|
if err != nil {
|
|
return
|
|
}
|
|
defer rows.Close()
|
|
|
|
for rows.Next() {
|
|
var id int
|
|
var firstName, lastName string
|
|
if err = rows.Scan(&id, &firstName, &lastName); err != nil {
|
|
return
|
|
}
|
|
|
|
persons = append(persons, models.Person{
|
|
Id: id,
|
|
FirstName: firstName,
|
|
LastName: lastName,
|
|
})
|
|
}
|
|
|
|
return persons, nil
|
|
}
|
|
|
|
func (dh *DatabaseHandler) DeleteResponsible(ids ...int) error {
|
|
if len(ids) == 0 {
|
|
return errors.New("no ids given to be deleted")
|
|
}
|
|
|
|
placeholders := make([]string, len(ids))
|
|
args := make([]any, len(ids))
|
|
for i, id := range ids {
|
|
placeholders[i] = "?"
|
|
args[i] = id
|
|
}
|
|
query := fmt.Sprintf("DELETE FROM responsible WHERE id IN (%s)", strings.Join(placeholders, ","))
|
|
|
|
_, err := dh.database.Exec(query, args...)
|
|
return err
|
|
}
|
|
|
|
func (dh *DatabaseHandler) responsibleExists(responsible models.Person) (bool, error) {
|
|
query := `
|
|
SELECT 1 FROM responsible
|
|
WHERE first_name = ? AND last_name = ?
|
|
LIMIT 1
|
|
`
|
|
var exists int
|
|
err := dh.database.QueryRow(query, responsible.FirstName, responsible.LastName).Scan(&exists)
|
|
|
|
if err == sql.ErrNoRows {
|
|
return false, nil // no match
|
|
} else if err != nil {
|
|
return false, err // db error
|
|
}
|
|
|
|
return true, nil // match found
|
|
}
|