/// <summary>
/// Gets a user by their Auth0 identifier
/// </summary>
/// <param name="output"></param>
/// <param name="auth0"></param>
/// <returns></returns>
public static bool FetchByAuth0(ref User output, string auth0)
{
SQLiteDataReader reader = DBI.DoPreparedQuery(
"SELECT * FROM User WHERE auth0 = @auth0 LIMIT 1;",
new Tuple <string, object>("@auth0", auth0));
if (reader != null && reader.Read())
{
output = User.Factory(reader);
return(true);
}
return(false);
}
/// <summary>
/// Gets a user by their name
/// </summary>
/// <param name="output"></param>
/// <param name="name"></param>
/// <returns></returns>
public static bool FetchByName(ref User output, string name)
{
SQLiteDataReader reader = DBI.DoPreparedQuery(
"SELECT * FROM User WHERE name = @name LIMIT 1;",
new Tuple <string, object>("@name", name));
if (reader != null && reader.Read())
{
output = User.Factory(reader);
return(true);
}
return(false);
}
/// <summary>
/// Gets a user by ID
/// </summary>
/// <param name="output"></param>
/// <param name="id"></param>
/// <returns></returns>
public static bool FetchById(ref User output, int id)
{
SQLiteDataReader reader = DBI.DoPreparedQuery(
"SELECT * FROM User WHERE id = @id LIMIT 1;",
new Tuple <string, object>("@id", id));
if (reader != null && reader.Read())
{
output = User.Factory(reader);
return(true);
}
return(false);
}
/// <summary>
/// Gets a list of all users
/// </summary>
/// <param name="output"></param>
/// <returns></returns>
public static bool FetchAll(ref List <User> output)
{
output = new List <User>();
SQLiteDataReader reader = DBI.DoQuery(
"SELECT * FROM User WHERE id != 0");
while (reader != null && reader.Read())
{
User u = User.Factory(reader);
output.Add(u);
}
return(true);
}
/// <summary>
/// Gets a list of all users without a current assignment
/// </summary>
/// <param name="output"></param>
/// <returns></returns>
public static bool FetchAllUnassigned(ref List <User> output)
{
output = new List <User>();
SQLiteDataReader reader = DBI.DoQuery(
@"SELECT u.id, u.name, u.auth0, u.rank, u.rate, u.created
FROM User u
WHERE u.id NOT IN
(SELECT user FROM Assignment WHERE until IS NULL)
AND u.id != 0;" );
while (reader != null && reader.Read())
{
User u = User.Factory(reader);
output.Add(u);
}
return(true);
}
/// <summary>
/// Gets a list of users assigned to a ship split up by company
/// and embarked.
/// </summary>
/// <param name="output"></param>
/// <param name="shipId"></param>
/// <param name="company"></param>
/// <returns></returns>
public static bool FetchAllByAssignment(ref List <User> output,
int shipId, bool company)
{
output = new List <User>();
int isCompany = Convert.ToInt32(company);
SQLiteDataReader reader = DBI.DoPreparedQuery(
@"SELECT u.id, u.name, u.auth0, u.rank, u.rate, u.created
FROM User u, Assignment a, AssignmentRole ar
WHERE a.user = u.id AND a.role = ar.id
AND ar.isCompany = @company AND a.ship = @ship
AND a.until is null ORDER BY ar.id ASC;" ,
new Tuple <string, object>("@company", isCompany),
new Tuple <string, object>("@ship", shipId));
while (reader != null && reader.Read())
{
User u = User.Factory(reader);
output.Add(u);
}
return(true);
}