/// <summary>
/// Gets a rate by name
/// </summary>
/// <param name="output"></param>
/// <param name="name"></param>
/// <returns></returns>
public static bool FetchByName(ref Rate output, string name)
{
SQLiteDataReader reader = DBI.DoPreparedQuery(
"SELECT * FROM Rate WHERE name = @name LIMIT 1;",
new Tuple <string, object>("@name", name));
if (reader != null && reader.Read())
{
output = Rate.Factory(reader);
return(true);
}
return(false);
}
/// <summary>
/// </summary>
/// <param name="output"></param>
/// <param name="user"></param>
/// <returns></returns>
public static bool FetchByUser(ref UserPrivs output, int user)
{
SQLiteDataReader reader = DBI.DoPreparedQuery(
"SELECT * FROM UserPrivs WHERE user=@user;",
new Tuple <string, object>("@user", user));
if (reader != null && reader.Read())
{
output = UserPrivs.Factory(reader);
return(true);
}
return(false);
}
/// <summary>
/// Gets a rate by ID
/// </summary>
/// <param name="output"></param>
/// <param name="id"></param>
/// <returns></returns>
public static bool FetchById(ref Rate output, int id)
{
SQLiteDataReader reader = DBI.DoPreparedQuery(
"SELECT * FROM Rate WHERE id = @id LIMIT 1;",
new Tuple <string, object>("@id", id));
if (reader != null && reader.Read())
{
output = Rate.Factory(reader);
return(true);
}
return(false);
}
/// <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>
/// Fetches a single row by ID.
/// </summary>
/// <param name="output"></param>
/// <param name="id"></param>
/// <returns></returns>
public static bool FetchById(ref ShipEquipment output, int id)
{
SQLiteDataReader reader = DBI.DoPreparedQuery(
@"SELECT id, hull, ship, 0 AS count
FROM ShipEquipment WHERE id = @id;" ,
new Tuple <string, object>("@id", id));
if (reader != null && reader.Read())
{
output = ShipEquipment.Factory(reader);
return(true);
}
return(false);
}
/// <summary>
/// Gets a rating by ID
/// </summary>
/// <param name="output"></param>
/// <param name="id"></param>
/// <returns></returns>
public static bool FetchById(ref StruckRate output, int id)
{
SQLiteDataReader reader = DBI.DoPreparedQuery(
@"SELECT id, user, rate, rank, earned,
COALESCE(expires, -1) AS expires
FROM StruckRate
WHERE id = @id LIMIT 1;" ,
new Tuple <string, object>("@id", id));
if (reader != null && reader.Read())
{
output = StruckRate.Factory(reader);
return(true);
}
return(false);
}
/// <summary>
/// Gets an assignment by its ID
/// </summary>
/// <param name="output"></param>
/// <param name="id"></param>
/// <returns></returns>
public static bool FetchById(ref Assignment output, int id)
{
SQLiteDataReader reader = DBI.DoPreparedQuery(
@"SELECT id, user, ship, role, start,
COALESCE(until, -1) AS until
FROM Assignment
WHERE id = @id LIMIT 1;" ,
new Tuple <string, object>("@id", id));
if (reader != null && reader.Read())
{
output = Assignment.Factory(reader);
return(true);
}
return(false);
}
/// <summary>
/// Fetches the count of a certain hull on a ship
/// </summary>
/// <param name="output"></param>
/// <param name="hullId"></param>
/// <param name="shipId"></param>
/// <returns></returns>
public static bool FetchHullByShip(ref ShipEquipment output, int hullId,
int shipId)
{
SQLiteDataReader reader = DBI.DoPreparedQuery(
@"SELECT id, hull, ship, COUNT(id) AS count
FROM ShipEquipment
WHERE ship = @ship AND hull = @hull
GROUP BY hull;" ,
new Tuple <string, object>("@ship", shipId),
new Tuple <string, object>("@hull", hullId));
if (reader != null && reader.Read())
{
output = ShipEquipment.Factory(reader);
return(true);
}
return(false);
}
/// <summary>
/// Gets a user's primary rate based on their id and rate id
/// </summary>
/// <param name="output"></param>
/// <param name="uid"></param>
/// <param name="rid"></param>
/// <returns></returns>
public static bool FetchByUserRateId(ref StruckRate output, int uid,
int rid)
{
SQLiteDataReader reader = DBI.DoPreparedQuery(
@"SELECT id, user, rate, rank, earned,
COALESCE(expires, -1) AS expires
FROM StruckRate
WHERE user = @user AND rate = @rate;" ,
new Tuple <string, object>("@user", uid),
new Tuple <string, object>("@rate", rid));
if (reader != null && reader.Read())
{
output = StruckRate.Factory(reader);
return(true);
}
return(false);
}
/// <summary>
/// Selects all hulls embarked on a given ship
/// </summary>
/// <param name="output"></param>
/// <param name="shipId"></param>
/// <returns></returns>
public static bool FetchAllByShip(ref List <ShipEquipment> output,
int shipId)
{
output = new List <ShipEquipment>();
SQLiteDataReader reader = DBI.DoPreparedQuery(
@"SELECT 0 AS id, hull, ship, COUNT(id) AS count
FROM ShipEquipment
WHERE ship = @ship
GROUP BY hull;" ,
new Tuple <string, object>("@ship", shipId));
while (reader != null && reader.Read())
{
output.Add(ShipEquipment.Factory(reader));
}
return(true);
}
/// <summary>
/// Checks if a ship exists in the registry with the given name
/// that is not destroyed or decommed
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static bool IsNameAvailable(string name)
{
SQLiteDataReader reader = DBI.DoPreparedQuery(
@"SELECT * FROM UserShip
WHERE name = @name
AND status != 1 AND status != 2 AND status != 4" ,
new Tuple <string, object>("@name", name)
);
// If results exist this name is not available
if (reader.Read())
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// Fetches a rating by ID
/// </summary>
/// <param name="output"></param>
/// <param name="rate"></param>
/// <returns></returns>
public static bool FetchByRateId(ref List <StruckRate> output,
int rate)
{
output = new List <StruckRate>();
SQLiteDataReader reader = DBI.DoPreparedQuery(
@"SELECT id, user, rate, rank, earned,
COALESCE(expires, -1) AS expires
FROM StruckRate
WHERE id = @id;" ,
new Tuple <string, object>("@id", rate));
while (reader != null && reader.Read())
{
output.Add(StruckRate.Factory(reader));
}
return(true);
}
/// <summary>
/// Fetches the full assignment history of a user ordered from most
/// recent to oldest
/// </summary>
/// <param name="output"></param>
/// <param name="userId"></param>
/// <returns></returns>
public static bool FetchAssignmentHistory(ref List <Assignment> output,
int userId)
{
output = new List <Assignment>();
SQLiteDataReader reader = DBI.DoPreparedQuery(
@"SELECT id, user, ship, role, start,
COALESCE(until, -1) AS until
FROM Assignment
WHERE user = @user ORDER BY start DESC;" ,
new Tuple <string, object>("@user", userId));
while (reader != null && reader.Read())
{
Assignment a = Assignment.Factory(reader);
output.Add(a);
}
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);
}
