650 lines
22 KiB
Go
650 lines
22 KiB
Go
package store
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"encoding/json"
|
|
"errors"
|
|
"strings"
|
|
"time"
|
|
|
|
"lektorenplan/domain"
|
|
)
|
|
|
|
func encodeStrings(values []string) string {
|
|
if values == nil {
|
|
values = []string{}
|
|
}
|
|
data, err := json.Marshal(values)
|
|
if err != nil {
|
|
return "[]"
|
|
}
|
|
return string(data)
|
|
}
|
|
|
|
func decodeStrings(value string) []string {
|
|
var out []string
|
|
if err := json.Unmarshal([]byte(value), &out); err != nil {
|
|
return nil
|
|
}
|
|
return out
|
|
}
|
|
|
|
func decodeInts(value string) []int {
|
|
var out []int
|
|
if err := json.Unmarshal([]byte(value), &out); err != nil {
|
|
return nil
|
|
}
|
|
return out
|
|
}
|
|
|
|
func encodeInts(values []int) string {
|
|
if values == nil {
|
|
values = []int{}
|
|
}
|
|
data, err := json.Marshal(values)
|
|
if err != nil {
|
|
return "[]"
|
|
}
|
|
return string(data)
|
|
}
|
|
|
|
func encodeWeekdayPeriods(values []domain.WeekdayPeriodPreference) string {
|
|
if values == nil {
|
|
values = []domain.WeekdayPeriodPreference{}
|
|
}
|
|
data, err := json.Marshal(values)
|
|
if err != nil {
|
|
return "[]"
|
|
}
|
|
return string(data)
|
|
}
|
|
|
|
func decodeWeekdayPeriods(value string) []domain.WeekdayPeriodPreference {
|
|
var out []domain.WeekdayPeriodPreference
|
|
if err := json.Unmarshal([]byte(value), &out); err != nil {
|
|
return nil
|
|
}
|
|
return out
|
|
}
|
|
|
|
// Churches lists a tenant's churches.
|
|
func (s *Store) Churches(ctx context.Context, tenantID string) ([]domain.Church, error) {
|
|
rows, err := s.db.QueryContext(ctx, `
|
|
SELECT id, name, short_name, symbol, color_hex, address, sort_index, notes
|
|
FROM churches WHERE tenant_id=? ORDER BY sort_index, name COLLATE NOCASE`, tenantID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var out []domain.Church
|
|
for rows.Next() {
|
|
var church domain.Church
|
|
var symbol string
|
|
if err := rows.Scan(&church.ID, &church.Name, &church.ShortName, &symbol,
|
|
&church.ColorHex, &church.Address, &church.SortIndex, &church.Notes); err != nil {
|
|
return nil, err
|
|
}
|
|
church.Symbol = domain.ChurchSymbol(symbol)
|
|
out = append(out, church)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// Church loads one church inside a tenant.
|
|
func (s *Store) Church(ctx context.Context, tenantID, id string) (*domain.Church, error) {
|
|
var church domain.Church
|
|
var symbol string
|
|
err := s.db.QueryRowContext(ctx, `
|
|
SELECT id, name, short_name, symbol, color_hex, address, sort_index, notes
|
|
FROM churches WHERE tenant_id=? AND id=?`, tenantID, id).
|
|
Scan(&church.ID, &church.Name, &church.ShortName, &symbol,
|
|
&church.ColorHex, &church.Address, &church.SortIndex, &church.Notes)
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return nil, ErrNotFound
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
church.Symbol = domain.ChurchSymbol(symbol)
|
|
return &church, nil
|
|
}
|
|
|
|
// CountChurches returns the number of churches in a tenant.
|
|
func (s *Store) CountChurches(ctx context.Context, tenantID string) (int, error) {
|
|
var count int
|
|
err := s.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM churches WHERE tenant_id=?", tenantID).Scan(&count)
|
|
return count, err
|
|
}
|
|
|
|
// UpsertChurch creates or updates a church.
|
|
func (s *Store) UpsertChurch(ctx context.Context, tenantID string, church domain.Church) error {
|
|
if strings.TrimSpace(church.ID) == "" {
|
|
church.ID = domain.NewID()
|
|
}
|
|
if strings.TrimSpace(church.Name) == "" {
|
|
return errors.New("Der Kirchenname ist erforderlich")
|
|
}
|
|
if church.Symbol == "" {
|
|
church.Symbol = domain.SymbolChapel
|
|
}
|
|
_, err := s.db.ExecContext(ctx, `
|
|
INSERT INTO churches(tenant_id, id, name, short_name, symbol, color_hex, address, sort_index, notes)
|
|
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
ON CONFLICT(tenant_id, id) DO UPDATE SET
|
|
name=excluded.name, short_name=excluded.short_name, symbol=excluded.symbol,
|
|
color_hex=excluded.color_hex, address=excluded.address,
|
|
sort_index=excluded.sort_index, notes=excluded.notes`,
|
|
tenantID, church.ID, church.Name, church.ShortName, string(church.Symbol),
|
|
church.ColorHex, church.Address, church.SortIndex, church.Notes)
|
|
return err
|
|
}
|
|
|
|
// DeleteChurch removes a church and all planning data that depends on it.
|
|
func (s *Store) DeleteChurch(ctx context.Context, tenantID, id string) error {
|
|
return s.transaction(ctx, func(tx *sql.Tx) error {
|
|
if _, err := tx.ExecContext(ctx, `
|
|
DELETE FROM assignments WHERE tenant_id=? AND service_id IN (
|
|
SELECT id FROM services WHERE tenant_id=? AND church_id=?)`,
|
|
tenantID, tenantID, id); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx,
|
|
"DELETE FROM services WHERE tenant_id=? AND church_id=?", tenantID, id); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx, `
|
|
DELETE FROM service_exceptions WHERE tenant_id=? AND template_id IN (
|
|
SELECT id FROM service_templates WHERE tenant_id=? AND church_id=?)`,
|
|
tenantID, tenantID, id); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx,
|
|
"DELETE FROM service_templates WHERE tenant_id=? AND church_id=?", tenantID, id); err != nil {
|
|
return err
|
|
}
|
|
_, err := tx.ExecContext(ctx, "DELETE FROM churches WHERE tenant_id=? AND id=?", tenantID, id)
|
|
return err
|
|
})
|
|
}
|
|
|
|
// People lists a tenant's volunteers.
|
|
func (s *Store) People(ctx context.Context, tenantID string) ([]domain.Person, error) {
|
|
rows, err := s.db.QueryContext(ctx, `
|
|
SELECT id, short_code, first_name, last_name, roles, email, preferred_church_ids,
|
|
preferred_weekdays, preferred_weekday_periods, preferred_service_titles,
|
|
max_per_month, allow_beyond_preferences, active, notes
|
|
FROM people WHERE tenant_id=? ORDER BY last_name COLLATE NOCASE, first_name COLLATE NOCASE`, tenantID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
var out []domain.Person
|
|
for rows.Next() {
|
|
person, err := scanPerson(rows)
|
|
if err != nil {
|
|
rows.Close()
|
|
return nil, err
|
|
}
|
|
out = append(out, *person)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
rows.Close()
|
|
return nil, err
|
|
}
|
|
rows.Close()
|
|
// The connection pool has a single connection, so the rows must be closed
|
|
// before the encrypted contacts are loaded.
|
|
for index := range out {
|
|
s.hydratePersonContacts(ctx, tenantID, &out[index])
|
|
}
|
|
return out, nil
|
|
}
|
|
|
|
// Person loads one volunteer.
|
|
func (s *Store) Person(ctx context.Context, tenantID, id string) (*domain.Person, error) {
|
|
row := s.db.QueryRowContext(ctx, `
|
|
SELECT id, short_code, first_name, last_name, roles, email, preferred_church_ids,
|
|
preferred_weekdays, preferred_weekday_periods, preferred_service_titles,
|
|
max_per_month, allow_beyond_preferences, active, notes
|
|
FROM people WHERE tenant_id=? AND id=?`, tenantID, id)
|
|
person, err := scanPerson(row)
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return nil, ErrNotFound
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
s.hydratePersonContacts(ctx, tenantID, person)
|
|
return person, nil
|
|
}
|
|
|
|
// hydratePersonContacts fills the e-mail address from the encrypted store.
|
|
// Values that exist but cannot be decrypted stay empty and are marked as
|
|
// locked so the UI can explain the situation. Before a tenant has been
|
|
// migrated, plaintext contacts are only exposed to key-holding editors.
|
|
func (s *Store) hydratePersonContacts(ctx context.Context, tenantID string, person *domain.Person) {
|
|
if !s.SecretIsInitialized(ctx, tenantID) {
|
|
if KeyringFrom(ctx) == nil && strings.TrimSpace(person.Email) != "" {
|
|
person.Email = ""
|
|
person.EmailLocked = true
|
|
}
|
|
return
|
|
}
|
|
email, emailLocked := s.PersonContactSecrets(ctx, tenantID, person.ID)
|
|
if person.Email == "" {
|
|
person.Email = email
|
|
person.EmailLocked = emailLocked
|
|
}
|
|
}
|
|
|
|
func scanPerson(row rowScanner) (*domain.Person, error) {
|
|
var person domain.Person
|
|
var roles, churches, weekdays, periods, titles string
|
|
var allowBeyond, active int
|
|
if err := row.Scan(&person.ID, &person.ShortCode, &person.FirstName, &person.LastName, &roles,
|
|
&person.Email, &churches, &weekdays, &periods, &titles,
|
|
&person.MaxPerMonth, &allowBeyond, &active, &person.Notes); err != nil {
|
|
return nil, err
|
|
}
|
|
for _, raw := range decodeStrings(roles) {
|
|
role := domain.Role(raw)
|
|
if role.IsValid() {
|
|
person.Roles = append(person.Roles, role)
|
|
}
|
|
}
|
|
person.PreferredChurchIDs = decodeStrings(churches)
|
|
person.PreferredWeekdays = decodeInts(weekdays)
|
|
person.PreferredWeekdayPeriods = decodeWeekdayPeriods(periods)
|
|
person.PreferredServiceTitles = decodeStrings(titles)
|
|
person.AllowBeyondPreferences = allowBeyond != 0
|
|
person.Active = active != 0
|
|
return &person, nil
|
|
}
|
|
|
|
// UpsertPerson creates or updates a volunteer.
|
|
func (s *Store) UpsertPerson(ctx context.Context, tenantID string, person domain.Person) error {
|
|
if strings.TrimSpace(person.ID) == "" {
|
|
person.ID = domain.NewID()
|
|
}
|
|
if strings.TrimSpace(person.FirstName) == "" && strings.TrimSpace(person.LastName) == "" {
|
|
return errors.New("Der Name ist erforderlich")
|
|
}
|
|
roleStrings := make([]string, 0, len(person.Roles))
|
|
for _, role := range person.Roles {
|
|
if role.IsValid() {
|
|
roleStrings = append(roleStrings, string(role))
|
|
}
|
|
}
|
|
if person.MaxPerMonth < 0 {
|
|
person.MaxPerMonth = 0
|
|
}
|
|
initialized := s.SecretIsInitialized(ctx, tenantID)
|
|
keyring := KeyringFrom(ctx)
|
|
// Plaintext contact writes are only a migration fallback for callers
|
|
// without key material. A reader who is logged in never stores an address
|
|
// in the legacy column.
|
|
manageContacts := !initialized && keyring == nil
|
|
// A locked field that was left empty must keep its existing value. This
|
|
// also lets readers without their own data key update their profile and
|
|
// prevents an administrator who cannot see plaintext contacts from
|
|
// clearing them before the tenant has been migrated.
|
|
preserveContact := person.EmailLocked && strings.TrimSpace(person.Email) == ""
|
|
updateLegacyContact := manageContacts && !preserveContact
|
|
var dek []byte
|
|
if initialized && keyring != nil && !preserveContact {
|
|
if err := s.EnsurePersonScope(ctx, keyring, tenantID, person.ID); err != nil {
|
|
return err
|
|
}
|
|
var err error
|
|
dek, err = s.scopeDEK(ctx, keyring, KeyScope{Type: ScopePerson, TenantID: tenantID, ID: person.ID})
|
|
if err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return s.transaction(ctx, func(tx *sql.Tx) error {
|
|
email := person.Email
|
|
if initialized || !manageContacts {
|
|
// Contact details only live in the encrypted secrets table.
|
|
email = ""
|
|
}
|
|
_, err := tx.ExecContext(ctx, `
|
|
INSERT INTO people(tenant_id, id, short_code, first_name, last_name, roles, email,
|
|
preferred_church_ids, preferred_weekdays, preferred_weekday_periods,
|
|
preferred_service_titles, max_per_month, allow_beyond_preferences, active, notes)
|
|
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
ON CONFLICT(tenant_id, id) DO UPDATE SET
|
|
short_code=excluded.short_code,
|
|
first_name=excluded.first_name, last_name=excluded.last_name, roles=excluded.roles,
|
|
email=CASE WHEN ? THEN excluded.email ELSE people.email END,
|
|
preferred_church_ids=excluded.preferred_church_ids,
|
|
preferred_weekdays=excluded.preferred_weekdays,
|
|
preferred_weekday_periods=excluded.preferred_weekday_periods,
|
|
preferred_service_titles=excluded.preferred_service_titles,
|
|
max_per_month=excluded.max_per_month,
|
|
allow_beyond_preferences=excluded.allow_beyond_preferences,
|
|
active=excluded.active, notes=excluded.notes`,
|
|
tenantID, person.ID, strings.TrimSpace(person.ShortCode),
|
|
person.FirstName, person.LastName, encodeStrings(roleStrings),
|
|
email, encodeStrings(person.PreferredChurchIDs),
|
|
encodeInts(person.PreferredWeekdays),
|
|
encodeWeekdayPeriods(person.PreferredWeekdayPeriods),
|
|
encodeStrings(person.PreferredServiceTitles), person.MaxPerMonth,
|
|
boolInt(person.AllowBeyondPreferences),
|
|
boolInt(person.Active), person.Notes, boolInt(updateLegacyContact))
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if !initialized || keyring == nil {
|
|
return nil
|
|
}
|
|
setSecret := func(key, value string) error {
|
|
if strings.TrimSpace(value) == "" {
|
|
_, err := tx.ExecContext(ctx, `
|
|
DELETE FROM secrets
|
|
WHERE tenant_id=? AND scope_type=? AND scope_id=? AND secret_key=?`,
|
|
tenantID, ScopePerson, person.ID, key)
|
|
return err
|
|
}
|
|
sealed, err := sealAESGCM(dek, []byte(value),
|
|
keyAAD(ScopePerson, tenantID, person.ID, key))
|
|
if err != nil {
|
|
return err
|
|
}
|
|
_, err = tx.ExecContext(ctx, `
|
|
INSERT INTO secrets(tenant_id, scope_type, scope_id, secret_key, ciphertext, updated_at)
|
|
VALUES(?, ?, ?, ?, ?, ?)
|
|
ON CONFLICT(tenant_id, scope_type, scope_id, secret_key) DO UPDATE SET
|
|
ciphertext=excluded.ciphertext, updated_at=excluded.updated_at`,
|
|
tenantID, ScopePerson, person.ID, key, sealed,
|
|
time.Now().UTC().Format(time.RFC3339))
|
|
return err
|
|
}
|
|
// A locked field that was left empty must keep its existing secret.
|
|
if !(person.EmailLocked && strings.TrimSpace(person.Email) == "") {
|
|
if err := setSecret("email", person.Email); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return nil
|
|
})
|
|
}
|
|
|
|
// DeletePerson removes a volunteer with assignments and absences.
|
|
func (s *Store) DeletePerson(ctx context.Context, tenantID, id string) error {
|
|
return s.transaction(ctx, func(tx *sql.Tx) error {
|
|
if _, err := tx.ExecContext(ctx,
|
|
"DELETE FROM assignments WHERE tenant_id=? AND person_id=?", tenantID, id); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx,
|
|
"DELETE FROM absences WHERE tenant_id=? AND person_id=?", tenantID, id); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx,
|
|
"DELETE FROM secrets WHERE tenant_id=? AND scope_type=? AND scope_id=?",
|
|
tenantID, ScopePerson, id); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx,
|
|
"DELETE FROM key_wrappers WHERE scope_type=? AND tenant_id=? AND scope_id=?",
|
|
ScopePerson, tenantID, id); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx,
|
|
"DELETE FROM contact_shares WHERE tenant_id=? AND (owner_person_id=? OR viewer_user_id IN ("+
|
|
"SELECT id FROM users WHERE linked_tenant_id=? AND linked_person_id=?))",
|
|
tenantID, id, tenantID, id); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx,
|
|
"DELETE FROM contact_reviews WHERE tenant_id=? AND owner_person_id=?", tenantID, id); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx,
|
|
"DELETE FROM swap_requests WHERE tenant_id=? AND (requester_person_id=? OR target_person_id=?)",
|
|
tenantID, id, id); err != nil {
|
|
return err
|
|
}
|
|
_, err := tx.ExecContext(ctx, "DELETE FROM people WHERE tenant_id=? AND id=?", tenantID, id)
|
|
return err
|
|
})
|
|
}
|
|
|
|
// Absences lists all absences in a tenant.
|
|
func (s *Store) Absences(ctx context.Context, tenantID string) ([]domain.Absence, error) {
|
|
rows, err := s.db.QueryContext(ctx, `
|
|
SELECT id, person_id, start_date, end_date, reason FROM absences
|
|
WHERE tenant_id=? ORDER BY start_date`, tenantID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var out []domain.Absence
|
|
for rows.Next() {
|
|
var absence domain.Absence
|
|
var start, end string
|
|
if err := rows.Scan(&absence.ID, &absence.PersonID, &start, &end, &absence.Reason); err != nil {
|
|
return nil, err
|
|
}
|
|
absence.Start = domain.MustDate(start)
|
|
absence.End = domain.MustDate(end)
|
|
out = append(out, absence)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// AbsencesForPerson lists one person's absences.
|
|
func (s *Store) AbsencesForPerson(ctx context.Context, tenantID, personID string) ([]domain.Absence, error) {
|
|
all, err := s.Absences(ctx, tenantID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
var out []domain.Absence
|
|
for _, absence := range all {
|
|
if absence.PersonID == personID {
|
|
out = append(out, absence)
|
|
}
|
|
}
|
|
return out, nil
|
|
}
|
|
|
|
// UpsertAbsence creates or updates an absence.
|
|
func (s *Store) UpsertAbsence(ctx context.Context, tenantID string, absence domain.Absence) error {
|
|
if strings.TrimSpace(absence.ID) == "" {
|
|
absence.ID = domain.NewID()
|
|
}
|
|
if absence.PersonID == "" {
|
|
return errors.New("Es muss eine Person gewählt werden")
|
|
}
|
|
_, err := s.db.ExecContext(ctx, `
|
|
INSERT INTO absences(tenant_id, id, person_id, start_date, end_date, reason)
|
|
VALUES(?, ?, ?, ?, ?, ?)
|
|
ON CONFLICT(tenant_id, id) DO UPDATE SET
|
|
person_id=excluded.person_id, start_date=excluded.start_date,
|
|
end_date=excluded.end_date, reason=excluded.reason`,
|
|
tenantID, absence.ID, absence.PersonID,
|
|
domain.Key(absence.Start), domain.Key(absence.End), absence.Reason)
|
|
return err
|
|
}
|
|
|
|
// DeleteAbsence removes one absence.
|
|
func (s *Store) DeleteAbsence(ctx context.Context, tenantID, id string) error {
|
|
_, err := s.db.ExecContext(ctx, "DELETE FROM absences WHERE tenant_id=? AND id=?", tenantID, id)
|
|
return err
|
|
}
|
|
|
|
// Templates lists service templates.
|
|
func (s *Store) Templates(ctx context.Context, tenantID string) ([]domain.ServiceTemplate, error) {
|
|
rows, err := s.db.QueryContext(ctx, `
|
|
SELECT id, church_id, weekday, time_minutes, title, lectors_needed, helpers_needed,
|
|
valid_from, valid_to, active, notes
|
|
FROM service_templates WHERE tenant_id=? ORDER BY weekday, time_minutes`, tenantID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var out []domain.ServiceTemplate
|
|
for rows.Next() {
|
|
var item domain.ServiceTemplate
|
|
var validFrom string
|
|
var validTo sql.NullString
|
|
var active int
|
|
if err := rows.Scan(&item.ID, &item.ChurchID, &item.Weekday, &item.TimeMinutes,
|
|
&item.Title, &item.LectorsNeeded, &item.HelpersNeeded, &validFrom,
|
|
&validTo, &active, &item.Notes); err != nil {
|
|
return nil, err
|
|
}
|
|
item.ValidFrom = domain.MustDate(validFrom)
|
|
if validTo.Valid && validTo.String != "" {
|
|
date := domain.MustDate(validTo.String)
|
|
item.ValidTo = &date
|
|
}
|
|
item.Active = active != 0
|
|
out = append(out, item)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// Template loads one template.
|
|
func (s *Store) Template(ctx context.Context, tenantID, id string) (*domain.ServiceTemplate, error) {
|
|
all, err := s.Templates(ctx, tenantID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
for i := range all {
|
|
if all[i].ID == id {
|
|
return &all[i], nil
|
|
}
|
|
}
|
|
return nil, ErrNotFound
|
|
}
|
|
|
|
// UpsertTemplate creates or updates a service template.
|
|
func (s *Store) UpsertTemplate(ctx context.Context, tenantID string, template domain.ServiceTemplate) error {
|
|
if strings.TrimSpace(template.ID) == "" {
|
|
template.ID = domain.NewID()
|
|
}
|
|
if template.ChurchID == "" || strings.TrimSpace(template.Title) == "" {
|
|
return errors.New("Kirche und Titel sind erforderlich")
|
|
}
|
|
var validTo any
|
|
if template.ValidTo != nil {
|
|
validTo = domain.Key(*template.ValidTo)
|
|
}
|
|
_, err := s.db.ExecContext(ctx, `
|
|
INSERT INTO service_templates(tenant_id, id, church_id, weekday, time_minutes, title,
|
|
lectors_needed, helpers_needed, valid_from, valid_to, active, notes)
|
|
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
ON CONFLICT(tenant_id, id) DO UPDATE SET
|
|
church_id=excluded.church_id, weekday=excluded.weekday,
|
|
time_minutes=excluded.time_minutes, title=excluded.title,
|
|
lectors_needed=excluded.lectors_needed, helpers_needed=excluded.helpers_needed,
|
|
valid_from=excluded.valid_from, valid_to=excluded.valid_to,
|
|
active=excluded.active, notes=excluded.notes`,
|
|
tenantID, template.ID, template.ChurchID, template.Weekday, template.TimeMinutes,
|
|
template.Title, template.LectorsNeeded, template.HelpersNeeded,
|
|
domain.Key(template.ValidFrom), validTo, boolInt(template.Active), template.Notes)
|
|
return err
|
|
}
|
|
|
|
// DeleteTemplate removes a template, its exceptions, and detaches services.
|
|
func (s *Store) DeleteTemplate(ctx context.Context, tenantID, id string) error {
|
|
return s.transaction(ctx, func(tx *sql.Tx) error {
|
|
if _, err := tx.ExecContext(ctx,
|
|
"UPDATE services SET template_id=NULL WHERE tenant_id=? AND template_id=?", tenantID, id); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx,
|
|
"DELETE FROM service_exceptions WHERE tenant_id=? AND template_id=?", tenantID, id); err != nil {
|
|
return err
|
|
}
|
|
_, err := tx.ExecContext(ctx,
|
|
"DELETE FROM service_templates WHERE tenant_id=? AND id=?", tenantID, id)
|
|
return err
|
|
})
|
|
}
|
|
|
|
// Exceptions lists template exceptions.
|
|
func (s *Store) Exceptions(ctx context.Context, tenantID string) ([]domain.ServiceException, error) {
|
|
rows, err := s.db.QueryContext(ctx, `
|
|
SELECT id, template_id, date, reason FROM service_exceptions
|
|
WHERE tenant_id=? ORDER BY date`, tenantID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var out []domain.ServiceException
|
|
for rows.Next() {
|
|
var item domain.ServiceException
|
|
var date string
|
|
if err := rows.Scan(&item.ID, &item.TemplateID, &date, &item.Reason); err != nil {
|
|
return nil, err
|
|
}
|
|
item.Date = domain.MustDate(date)
|
|
out = append(out, item)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// UpsertException creates or updates an exception. The unique key is
|
|
// (tenant, template, date), matching the macOS app.
|
|
func (s *Store) UpsertException(ctx context.Context, tenantID string, item domain.ServiceException) error {
|
|
if strings.TrimSpace(item.ID) == "" {
|
|
item.ID = domain.NewID()
|
|
}
|
|
if item.TemplateID == "" {
|
|
return errors.New("Es muss eine Vorlage gewählt werden")
|
|
}
|
|
return s.transaction(ctx, func(tx *sql.Tx) error {
|
|
// An explicit ID means the caller is editing an existing exception in
|
|
// place. Remove a conflicting row first so moving an exception to a new
|
|
// template or date cannot violate the unique key.
|
|
if strings.TrimSpace(item.ID) != "" {
|
|
var count int
|
|
if err := tx.QueryRowContext(ctx,
|
|
"SELECT COUNT(*) FROM service_exceptions WHERE tenant_id=? AND id=?",
|
|
tenantID, item.ID).Scan(&count); err != nil {
|
|
return err
|
|
}
|
|
if count > 0 {
|
|
if _, err := tx.ExecContext(ctx, `
|
|
DELETE FROM service_exceptions
|
|
WHERE tenant_id=? AND template_id=? AND date=? AND id<>?`,
|
|
tenantID, item.TemplateID, domain.Key(item.Date), item.ID); err != nil {
|
|
return err
|
|
}
|
|
_, err := tx.ExecContext(ctx, `
|
|
UPDATE service_exceptions SET template_id=?, date=?, reason=?
|
|
WHERE tenant_id=? AND id=?`,
|
|
item.TemplateID, domain.Key(item.Date), item.Reason, tenantID, item.ID)
|
|
return err
|
|
}
|
|
}
|
|
|
|
var existingID string
|
|
err := tx.QueryRowContext(ctx, `
|
|
SELECT id FROM service_exceptions WHERE tenant_id=? AND template_id=? AND date=?`,
|
|
tenantID, item.TemplateID, domain.Key(item.Date)).Scan(&existingID)
|
|
if err != nil && !errors.Is(err, sql.ErrNoRows) {
|
|
return err
|
|
}
|
|
if existingID != "" {
|
|
_, err = tx.ExecContext(ctx,
|
|
"UPDATE service_exceptions SET reason=? WHERE tenant_id=? AND id=?",
|
|
item.Reason, tenantID, existingID)
|
|
return err
|
|
}
|
|
_, err = tx.ExecContext(ctx, `
|
|
INSERT INTO service_exceptions(tenant_id, id, template_id, date, reason)
|
|
VALUES(?, ?, ?, ?, ?)`,
|
|
tenantID, item.ID, item.TemplateID, domain.Key(item.Date), item.Reason)
|
|
return err
|
|
})
|
|
}
|
|
|
|
// DeleteException removes one exception.
|
|
func (s *Store) DeleteException(ctx context.Context, tenantID, id string) error {
|
|
_, err := s.db.ExecContext(ctx, "DELETE FROM service_exceptions WHERE tenant_id=? AND id=?", tenantID, id)
|
|
return err
|
|
}
|