wrest.go

/*
 * Copyright (C) 2022 Associated Universities, Inc. Washington DC, USA.
 *
 * This file is part of NRAO Workspaces.
 *
 * Workspaces 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.
 *
 * Workspaces 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 Workspaces.  If not, see <https://www.gnu.org/licenses/>.
 */

/*
Package wrest looks up contact authors for a given project.

If this tool isn't working, the same information can be obtained by logging
into the OPT, opening the project in question, and clicking on the coauthors.
*/
package wrest

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	_ "github.com/lib/pq"
	"strings"
)

// RetrieveContacts for a given project code
func RetrieveContacts(projectCode string) ([]string, error) {
	// Get the designated contact(s) for this project.

	// step 1: retrieve the contact author globalIDs from the OPT
	globalIds, err := retrieveGlobalIds(projectCode)

	if err != nil {
		return nil, fmt.Errorf("unable to retrieve global ids: %v", err)
	}

	// step 2: look up email addresses in the my.nrao.edu database
	emails, err := retrieveEmailsForGlobalIds(globalIds)
	if err != nil {
		return nil, fmt.Errorf("unable to retrieve emails: %v", err)
	}

	return emails, nil
}

// retrieveGlobalIds for a given project code
func retrieveGlobalIds(projectCode string) ([]int, error) {
	// connect to the database
	connection, err := GetArchiveDBConnection()
	if err != nil {
		return nil, fmt.Errorf("unable to connect to archive database: %v", err)
	}

	// build the query
	query := `select globalid
				 from project
				 join projectauthor p on project.pi = p.id
				 where projectcode = $1 and receivesemail
				 union
				 select globalid
				 from project
				 join projectauthor p on project.contactauthor = p.id
				 where projectcode = $1 and receivesemail
				 union
				 select globalid
				 from project
				 join coauthors c on project.id = c.project_id
				 join projectauthor p on c.projectauthor_id = p.id
				 where projectcode = $1 and receivesemail`

	// close the database when we're done
	defer func(connection *sql.DB) {
		if err := connection.Close(); err != nil {
			fmt.Printf("unable to close the database connection: %v\n", err)
		}
	}(connection)

	// run the query and get the result object
	rows, err := connection.Query(query, projectCode)
	if err != nil {
		return nil, fmt.Errorf("unable to execute project code query: %v", err)
	}

	// make a result slice
	globalIds := make([]int, 0)

	// clean up
	defer rows.Close()

	// walk through the list, scanning into a variable and appending to the result slice
	for rows.Next() {
		globalId := 0
		if err := rows.Scan(&globalId); err != nil {
			return globalIds, fmt.Errorf("unable to read global ID from the user query: %v", err)
		}
		globalIds = append(globalIds, globalId)
	}

	// done, return
	return globalIds, nil
}

// retrieveEmailsForGlobalIds obtains the emails for the given slice of global IDs
func retrieveEmailsForGlobalIds(globalIds []int) ([]string, error) {
	// Now that we have the global IDs, we can use them to look up contacts' email addresses
	connection, err := GetProposalsDBConnection()
	if err != nil {
		return nil, fmt.Errorf("unable to connect to PST database: %v", err)
	}

	// build the IN list, this is so gross
	inClause := strings.Trim(strings.Join(strings.Fields(fmt.Sprint(globalIds)), ","), "[]")

	// build the query
	query := fmt.Sprintf(`select email
			  from email
			  where person_id IN (%s) and defaultEmail`, inClause)

	// close the database when we're done
	defer func(connection *sql.DB) {
		if err := connection.Close(); err != nil {
			fmt.Printf("unable to close the database connection: %v\n", err)
		}
	}(connection)

	// run the query and get the result object
	rows, err := connection.Query(query)
	if err != nil {
		return nil, fmt.Errorf("unable to execute the project code query: %v", err)
	}

	// make a result slice
	emails := make([]string, 0)

	// clean up
	defer rows.Close()

	// walk through the list, scanning into a variable and appending to the result slice
	for rows.Next() {
		var email string
		err := rows.Scan(&email)
		if err != nil {
			return nil, fmt.Errorf("unable to read email from the email query: %v", err)
		}
		emails = append(emails, email)
	}

	// done, return
	return emails, nil
}