422 lines
11 KiB
Go
422 lines
11 KiB
Go
package handlers
|
|
|
|
import (
|
|
"database/sql"
|
|
"errors"
|
|
"fmt"
|
|
"strings"
|
|
"time"
|
|
|
|
"gitea.tecamino.com/paadi/memberDB/crypto"
|
|
"gitea.tecamino.com/paadi/memberDB/models"
|
|
"gitea.tecamino.com/paadi/memberDB/utils"
|
|
)
|
|
|
|
// CreateNewMemberTable creates a new member table.
|
|
func (dh *DatabaseHandler) CreateNewMemberTable() error {
|
|
createTableSQL := `
|
|
CREATE TABLE IF NOT EXISTS members (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
first_name TEXT NOT NULL,
|
|
first_name_hash TEXT NOT NULL,
|
|
last_name TEXT NOT NULL,
|
|
last_name_hash TEXT NOT NULL,
|
|
birthday TEXT NOT NULL,
|
|
birthday_hash TEXT NOT NULL,
|
|
address TEXT NOT NULL,
|
|
zip_code TEXT NOT NULL,
|
|
town TEXT NOT NULL,
|
|
phone TEXT NOT NULL,
|
|
email TEXT UNIQUE NOT NULL,
|
|
first_visit TEXT NOT NULL,
|
|
last_visit TEXT NOT NULL,
|
|
member_group TEXT NOT NULL,
|
|
responsible_person TEXT NOT NULL
|
|
);`
|
|
|
|
_, err := dh.database.Exec(createTableSQL)
|
|
if err != nil {
|
|
return fmt.Errorf("failed to create table: %w", err)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// AddNewMember adds a new member to memeber table at least fist, las name and birthday has to be entered
|
|
func (dh *DatabaseHandler) AddNewMember(members ...models.Member) error {
|
|
for _, member := range members {
|
|
|
|
exists, err := dh.memberExists(member)
|
|
if err != nil {
|
|
return err
|
|
} else if exists {
|
|
return fmt.Errorf("member %s %s %s exists already", member.FirstName, member.LastName, member.Birthday)
|
|
}
|
|
|
|
encFirstName, err := crypto.Encrypt(member.FirstName, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
encLastName, err := crypto.Encrypt(member.LastName, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
//check correct birtday format
|
|
if member.Birthday != "" && !utils.IsValidBirthday(member.Birthday) {
|
|
return errors.New("incorrect birthday format")
|
|
}
|
|
|
|
encBirthday, err := crypto.Encrypt(member.Birthday, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
encAddress, err := crypto.Encrypt(member.Address, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
encZip, err := crypto.Encrypt(member.Zip, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
encTown, err := crypto.Encrypt(member.Town, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
encPhone, err := crypto.Encrypt(member.Phone, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
//check correct email format
|
|
if member.Email != "" && !utils.IsValidEmail(member.Email) {
|
|
return errors.New("incorrect email format")
|
|
}
|
|
|
|
encEmail, err := crypto.Encrypt(member.Email, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
now := time.Now().Format("2006-01-02 15:04:05")
|
|
encFirstVisit, err := crypto.Encrypt(now, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
encLastVisit, err := crypto.Encrypt(now, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
encGroup, err := crypto.Encrypt(member.Group, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
encResponsiblePerson, err := crypto.Encrypt(member.ResponsiblePerson, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
_, err = dh.database.Exec("INSERT INTO members (first_name, first_name_hash, last_name, last_name_hash, birthday, birthday_hash, address, zip_code, town, phone, email, first_visit, last_visit, member_group, responsible_person) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", encFirstName, dh.hashField(member.FirstName), encLastName, dh.hashField(member.LastName), encBirthday, dh.hashField(member.Birthday), encAddress, encZip, encTown, encPhone, encEmail, encFirstVisit, encLastVisit, encGroup, encResponsiblePerson)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// DeleteMember removes members by given ids
|
|
func (dh *DatabaseHandler) DeleteMember(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 members WHERE id IN (%s)", strings.Join(placeholders, ","))
|
|
|
|
_, err := dh.database.Exec(query, args...)
|
|
return err
|
|
}
|
|
|
|
// GetMember returns one member by given id
|
|
func (dh *DatabaseHandler) GetMember(id int) (members []models.Member, err error) {
|
|
var args any
|
|
query := `SELECT id, first_name, last_name, birthday, address, zip_code, town, phone, email, first_visit, last_visit, member_group, responsible_person FROM members`
|
|
|
|
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 encFirstName, encLastName, encBirthday, encAddress, encZip, encTown, encPhone, encEmail, encFirstVisit, encLastVisit, encGroup, encResponsiblePerson string
|
|
if err = rows.Scan(&id, &encFirstName, &encLastName, &encBirthday, &encAddress, &encZip, &encTown, &encPhone, &encEmail, &encFirstVisit, &encLastVisit, &encGroup, &encResponsiblePerson); err != nil {
|
|
return
|
|
}
|
|
|
|
firstName, err := crypto.Decrypt(encFirstName, dh.token)
|
|
if err != nil {
|
|
return members, err
|
|
}
|
|
|
|
lastName, err := crypto.Decrypt(encLastName, dh.token)
|
|
if err != nil {
|
|
return members, err
|
|
}
|
|
|
|
birthday, err := crypto.Decrypt(encBirthday, dh.token)
|
|
if err != nil {
|
|
return members, err
|
|
}
|
|
|
|
address, err := crypto.Decrypt(encAddress, dh.token)
|
|
if err != nil {
|
|
return members, err
|
|
}
|
|
|
|
zip, err := crypto.Decrypt(encZip, dh.token)
|
|
if err != nil {
|
|
return members, err
|
|
}
|
|
|
|
town, err := crypto.Decrypt(encTown, dh.token)
|
|
if err != nil {
|
|
return members, err
|
|
}
|
|
|
|
phone, err := crypto.Decrypt(encPhone, dh.token)
|
|
if err != nil {
|
|
return members, err
|
|
}
|
|
|
|
email, err := crypto.Decrypt(encEmail, dh.token)
|
|
if err != nil {
|
|
return members, err
|
|
}
|
|
|
|
firstVisit, err := crypto.Decrypt(encFirstVisit, dh.token)
|
|
if err != nil {
|
|
return members, err
|
|
}
|
|
|
|
lastVisit, err := crypto.Decrypt(encLastVisit, dh.token)
|
|
if err != nil {
|
|
return members, err
|
|
}
|
|
|
|
group, err := crypto.Decrypt(encGroup, dh.token)
|
|
if err != nil {
|
|
return members, err
|
|
}
|
|
|
|
responsiblePerson, err := crypto.Decrypt(encResponsiblePerson, dh.token)
|
|
if err != nil {
|
|
return members, err
|
|
}
|
|
|
|
members = append(members, models.Member{
|
|
Id: id,
|
|
FirstName: firstName,
|
|
LastName: lastName,
|
|
Birthday: birthday,
|
|
Address: address,
|
|
Zip: zip,
|
|
Town: town,
|
|
Phone: phone,
|
|
Email: email,
|
|
FirstVisit: firstVisit,
|
|
LastVisit: lastVisit,
|
|
Group: group,
|
|
ResponsiblePerson: responsiblePerson,
|
|
})
|
|
}
|
|
|
|
return members, nil
|
|
}
|
|
|
|
// UpdateMember updates/overrides all information given meber id
|
|
func (dh *DatabaseHandler) UpdateMember(id int, member models.Member) (err error) {
|
|
var queryParameters []string
|
|
var args []any
|
|
|
|
if member.FirstName != "" {
|
|
encFirstName, err := crypto.Encrypt(member.FirstName, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
queryParameters = append(queryParameters, "first_name = ?")
|
|
queryParameters = append(queryParameters, "first_name_hash = ?")
|
|
|
|
args = append(args, encFirstName)
|
|
args = append(args, dh.hashField(member.FirstName))
|
|
}
|
|
|
|
if member.LastName != "" {
|
|
encLastName, err := crypto.Encrypt(member.LastName, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
queryParameters = append(queryParameters, "last_name = ?")
|
|
queryParameters = append(queryParameters, "last_name_hash = ?")
|
|
|
|
args = append(args, encLastName)
|
|
args = append(args, dh.hashField(member.LastName))
|
|
}
|
|
|
|
//check correct birtday format
|
|
if member.Birthday != "" && utils.IsValidBirthday(member.Birthday) {
|
|
encBirthday, err := crypto.Encrypt(member.Birthday, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
queryParameters = append(queryParameters, "birthday = ?")
|
|
queryParameters = append(queryParameters, "birthday_hash = ?")
|
|
|
|
args = append(args, encBirthday)
|
|
args = append(args, dh.hashField(member.Birthday))
|
|
} else if member.Birthday != "" {
|
|
return errors.New("incorrect birthday format")
|
|
}
|
|
|
|
if member.Address != "" {
|
|
encAddress, err := crypto.Encrypt(member.Address, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
queryParameters = append(queryParameters, "address = ?")
|
|
|
|
args = append(args, encAddress)
|
|
}
|
|
|
|
if member.Zip != "" {
|
|
encZip, err := crypto.Encrypt(member.Zip, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
queryParameters = append(queryParameters, "zip_code = ?")
|
|
|
|
args = append(args, encZip)
|
|
}
|
|
|
|
if member.Town != "" {
|
|
encTown, err := crypto.Encrypt(member.Town, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
queryParameters = append(queryParameters, "town = ?")
|
|
|
|
args = append(args, encTown)
|
|
}
|
|
|
|
if member.Phone != "" {
|
|
encPhone, err := crypto.Encrypt(member.Phone, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
queryParameters = append(queryParameters, "phone = ?")
|
|
|
|
args = append(args, encPhone)
|
|
}
|
|
|
|
//check correct email format
|
|
if member.Email != "" && utils.IsValidEmail(member.Email) {
|
|
encEmail, err := crypto.Encrypt(member.Email, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
queryParameters = append(queryParameters, "email = ?")
|
|
|
|
args = append(args, encEmail)
|
|
} else if member.Email != "" {
|
|
return errors.New("incorrect email format")
|
|
}
|
|
|
|
if member.FirstVisit != "" {
|
|
encFirstVisit, err := crypto.Encrypt(member.FirstVisit, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
queryParameters = append(queryParameters, "first_visit = ?")
|
|
|
|
args = append(args, encFirstVisit)
|
|
}
|
|
|
|
if member.LastVisit != "" {
|
|
encLastVisit, err := crypto.Encrypt(member.LastVisit, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
queryParameters = append(queryParameters, "last_visit = ?")
|
|
|
|
args = append(args, encLastVisit)
|
|
}
|
|
|
|
if member.Group != "" {
|
|
encFirstVisit, err := crypto.Encrypt(member.Group, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
queryParameters = append(queryParameters, "member_group = ?")
|
|
|
|
args = append(args, encFirstVisit)
|
|
}
|
|
|
|
if member.ResponsiblePerson != "" {
|
|
encResponsiblePerson, err := crypto.Encrypt(member.ResponsiblePerson, dh.token)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
queryParameters = append(queryParameters, "responsible_person = ?")
|
|
|
|
args = append(args, encResponsiblePerson)
|
|
}
|
|
|
|
query := `UPDATE members SET `
|
|
query += strings.Join(queryParameters, ", ")
|
|
query += ` WHERE id = ?`
|
|
args = append(args, id)
|
|
|
|
_, err = dh.database.Exec(query, args...)
|
|
return err
|
|
}
|
|
|
|
// memberExists helper to check wheter member already exists
|
|
func (dh *DatabaseHandler) memberExists(member models.Member) (bool, error) {
|
|
query := `
|
|
SELECT 1 FROM members
|
|
WHERE first_name_hash = ? AND last_name_hash = ? AND birthday_hash = ?
|
|
LIMIT 1
|
|
`
|
|
var exists int
|
|
err := dh.database.QueryRow(query, dh.hashField(member.FirstName), dh.hashField(member.LastName), dh.hashField(member.Birthday)).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
|
|
}
|