314 lines
7.2 KiB
Go
314 lines
7.2 KiB
Go
package services
|
|
|
|
import (
|
|
"authentication/db"
|
|
"authentication/models"
|
|
"database/sql"
|
|
"fmt"
|
|
"log"
|
|
"sort"
|
|
"strconv"
|
|
"strings"
|
|
)
|
|
|
|
func GetUserID(email string) (string, error) {
|
|
log.Print(email)
|
|
query := `SELECT users_id FROM uess_user_management.users WHERE email_address = ? AND is_deleted = 0 LIMIT 1;`
|
|
var id string
|
|
err := db.DB.QueryRow(query, email).Scan(&id)
|
|
if err != nil {
|
|
return "", err
|
|
}
|
|
return id, nil
|
|
}
|
|
|
|
func CheckEmailInDB(email string) (bool, error) {
|
|
var exists bool
|
|
query := `SELECT EXISTS (
|
|
SELECT 1 FROM uess_user_management.users WHERE email_address = ? AND is_deleted = 0)`
|
|
err := db.DB.QueryRow(query, email).Scan(&exists)
|
|
if err != nil {
|
|
return false, err
|
|
}
|
|
return exists, nil
|
|
}
|
|
|
|
func GetUserIDFromEmail(email string) (string, error) {
|
|
log.Print(email)
|
|
query := `SELECT users_id
|
|
FROM (
|
|
SELECT users_id, 1 AS priority
|
|
FROM users
|
|
WHERE email_address = ?
|
|
AND is_deleted = 0
|
|
) t
|
|
ORDER BY priority ASC
|
|
LIMIT 1;
|
|
`
|
|
|
|
var id string
|
|
err := db.DB.QueryRow(query, email).Scan(&id)
|
|
if err != nil {
|
|
log.Println("Hello")
|
|
|
|
return "", err
|
|
}
|
|
|
|
return id, nil
|
|
}
|
|
|
|
func GetRoleIDsFromEmail(email string) ([]int, error) {
|
|
log.Print(email)
|
|
|
|
globalQuery := `SELECT DISTINCT ur.role_id
|
|
FROM uess_user_management.users u
|
|
JOIN uess_user_management.user_roles ur ON u.users_id = ur.users_id
|
|
WHERE u.email_address = ?
|
|
AND u.is_deleted = 0
|
|
AND ur.role_id IS NOT NULL`
|
|
|
|
globalRows, err := db.DB.Query(globalQuery, email)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer globalRows.Close()
|
|
|
|
projectQuery := `SELECT DISTINCT pa.role_id
|
|
FROM uess_user_management.users u
|
|
JOIN uess_project_management.project_assignment pa ON u.users_id = pa.users_id
|
|
WHERE u.email_address = ?
|
|
AND u.is_deleted = 0
|
|
AND pa.is_active = 1
|
|
AND pa.role_id IS NOT NULL`
|
|
|
|
projectRows, err := db.DB.Query(projectQuery, email)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer projectRows.Close()
|
|
|
|
roleIDs := make([]int, 0)
|
|
seen := make(map[int]struct{})
|
|
|
|
for globalRows.Next() {
|
|
var roleID int
|
|
if err := globalRows.Scan(&roleID); err != nil {
|
|
return nil, err
|
|
}
|
|
if _, exists := seen[roleID]; !exists {
|
|
seen[roleID] = struct{}{}
|
|
roleIDs = append(roleIDs, roleID)
|
|
}
|
|
}
|
|
if err := globalRows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
for projectRows.Next() {
|
|
var roleID int
|
|
if err := projectRows.Scan(&roleID); err != nil {
|
|
return nil, err
|
|
}
|
|
if _, exists := seen[roleID]; !exists {
|
|
seen[roleID] = struct{}{}
|
|
roleIDs = append(roleIDs, roleID)
|
|
}
|
|
}
|
|
if err := projectRows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
sort.Ints(roleIDs)
|
|
return roleIDs, nil
|
|
}
|
|
|
|
func FetchUserByEmail(email string) (models.User, error) {
|
|
query := `SELECT u.users_id, u.first_name, u.middle_initial, u.last_name, u.suffix, GROUP_CONCAT(DISTINCT ur.role_id ORDER BY ur.role_id) AS role_ids, u.office_id, u.email_address, MAX(ur.MIS), MAX(ur.CAPI), MAX(ur.CAWI), MAX(ur.DPS),
|
|
u.sex, u.status_of_employment, u.home_address, u.contact_number, u.user_type
|
|
FROM uess_user_management.users u
|
|
LEFT JOIN uess_user_management.user_roles ur
|
|
ON u.users_id = ur.users_id
|
|
WHERE u.email_address = ?
|
|
GROUP BY u.users_id, u.first_name, u.middle_initial, u.last_name, u.suffix, u.office_id, u.email_address, u.sex, u.status_of_employment, u.home_address, u.contact_number, u.user_type`
|
|
|
|
var user models.User
|
|
var roleIDsCSV sql.NullString
|
|
|
|
var middleInitial sql.NullString
|
|
var suffix sql.NullString
|
|
var officeID sql.NullInt64
|
|
var mis sql.NullInt64
|
|
var capi sql.NullInt64
|
|
var cawi sql.NullInt64
|
|
var dps sql.NullInt64
|
|
var sex sql.NullString
|
|
var statusOfEmployment sql.NullString
|
|
var homeAddress sql.NullString
|
|
var contactNumber sql.NullString
|
|
var userType sql.NullInt64
|
|
|
|
err := db.DB.QueryRow(query, email).Scan(
|
|
&user.UserID,
|
|
&user.FirstName,
|
|
&middleInitial,
|
|
&user.LastName,
|
|
&suffix,
|
|
&roleIDsCSV,
|
|
&officeID,
|
|
&user.EmailAddress,
|
|
&mis,
|
|
&capi,
|
|
&cawi,
|
|
&dps,
|
|
&sex,
|
|
&statusOfEmployment,
|
|
&homeAddress,
|
|
&contactNumber,
|
|
&userType,
|
|
)
|
|
if err != nil {
|
|
return user, err
|
|
}
|
|
|
|
if middleInitial.Valid {
|
|
value := middleInitial.String
|
|
user.MiddleInitial = &value
|
|
}
|
|
if suffix.Valid {
|
|
value := suffix.String
|
|
user.Suffix = &value
|
|
}
|
|
if officeID.Valid {
|
|
value := int(officeID.Int64)
|
|
user.OfficeID = &value
|
|
}
|
|
if mis.Valid {
|
|
value := int(mis.Int64)
|
|
user.MIS = &value
|
|
}
|
|
if capi.Valid {
|
|
value := int(capi.Int64)
|
|
user.CAPI = &value
|
|
}
|
|
if cawi.Valid {
|
|
value := int(cawi.Int64)
|
|
user.CAWI = &value
|
|
}
|
|
if dps.Valid {
|
|
value := int(dps.Int64)
|
|
user.DPS = &value
|
|
}
|
|
if sex.Valid {
|
|
value := sex.String
|
|
user.Sex = &value
|
|
}
|
|
if statusOfEmployment.Valid {
|
|
value := statusOfEmployment.String
|
|
user.StatusOfEmployment = &value
|
|
}
|
|
if homeAddress.Valid {
|
|
value := homeAddress.String
|
|
user.HomeAddress = &value
|
|
}
|
|
if contactNumber.Valid {
|
|
value := contactNumber.String
|
|
user.ContactNumber = &value
|
|
}
|
|
if userType.Valid {
|
|
value := int(userType.Int64)
|
|
user.UserType = &value
|
|
}
|
|
|
|
baseRoleIDs, parseErr := parseRoleIDsCSV(roleIDsCSV.String)
|
|
if parseErr != nil {
|
|
return user, parseErr
|
|
}
|
|
if len(baseRoleIDs) > 0 {
|
|
primaryRoleID := baseRoleIDs[0]
|
|
user.RoleID = &primaryRoleID
|
|
}
|
|
|
|
projectsQuery := `SELECT pa.project_id, p.alias, GROUP_CONCAT(DISTINCT pa.role_id ORDER BY pa.role_id) AS role_ids, u.office_id
|
|
FROM uess_user_management.users u
|
|
LEFT JOIN uess_project_management.project_assignment pa
|
|
ON u.users_id = pa.users_id AND pa.is_active = 1
|
|
LEFT JOIN uess_project_management.project p
|
|
ON pa.project_id = p.project_id
|
|
WHERE u.email_address = ? AND pa.project_id IS NOT NULL
|
|
GROUP BY pa.project_id, p.alias, u.office_id`
|
|
|
|
rows, err := db.DB.Query(projectsQuery, email)
|
|
if err != nil {
|
|
return user, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
projects := make([]models.ProjectMetadata, 0)
|
|
for rows.Next() {
|
|
var project models.ProjectMetadata
|
|
var projectAlias sql.NullString
|
|
var projectRoleIDsCSV sql.NullString
|
|
var projectOfficeID sql.NullInt64
|
|
|
|
if scanErr := rows.Scan(&project.ProjectID, &projectAlias, &projectRoleIDsCSV, &projectOfficeID); scanErr != nil {
|
|
return user, scanErr
|
|
}
|
|
|
|
if projectAlias.Valid {
|
|
alias := projectAlias.String
|
|
project.Alias = &alias
|
|
}
|
|
|
|
if projectOfficeID.Valid {
|
|
office := int(projectOfficeID.Int64)
|
|
project.OfficeID = &office
|
|
}
|
|
|
|
roleIDs, parseErr := parseRoleIDsCSV(projectRoleIDsCSV.String)
|
|
if parseErr != nil {
|
|
return user, parseErr
|
|
}
|
|
project.RoleID = roleIDs
|
|
projects = append(projects, project)
|
|
}
|
|
|
|
if err := rows.Err(); err != nil {
|
|
return user, err
|
|
}
|
|
|
|
if len(projects) > 0 {
|
|
user.Projects = &projects
|
|
if user.RoleID == nil && len(projects[0].RoleID) > 0 {
|
|
primaryRoleID := projects[0].RoleID[0]
|
|
user.RoleID = &primaryRoleID
|
|
}
|
|
}
|
|
|
|
return user, nil
|
|
}
|
|
|
|
func parseRoleIDsCSV(roleIDsCSV string) ([]int, error) {
|
|
trimmed := strings.TrimSpace(roleIDsCSV)
|
|
if trimmed == "" {
|
|
return make([]int, 0), nil
|
|
}
|
|
|
|
parts := strings.Split(trimmed, ",")
|
|
roleIDs := make([]int, 0, len(parts))
|
|
|
|
for _, part := range parts {
|
|
value := strings.TrimSpace(part)
|
|
if value == "" {
|
|
continue
|
|
}
|
|
|
|
parsed, err := strconv.Atoi(value)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("invalid role id %q: %w", value, err)
|
|
}
|
|
roleIDs = append(roleIDs, parsed)
|
|
}
|
|
|
|
return roleIDs, nil
|
|
}
|