/// <summary>
/// Updates a vendor
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public static bool Store(HullVendor input)
{
int result = DBI.DoPreparedAction(
@"UPDATE HullVendor
SET name = @name, abrv = @abrv, icon = @icon
WHERE id = @id;" ,
new Tuple <string, object>("@name", input.name),
new Tuple <string, object>("@abrv", input.abrv),
new Tuple <string, object>("@icon", input.icon),
new Tuple <string, object>("@id", input.id));
if (result == 1)
{
return(true);
}
return(false);
}
public string Login(string username, string password)
{
if (username == "pacetc" && password == "pacetc")
{
string g = new Random().Next(0, 999999999).ToString();
DBI dbi = new DBI();
dbi.Execute("INSERT INTO paceLogins VALUES ('" + DateTime.Now.Date.ToString().Substring(0, 10) + "','" + DateTime.Now.TimeOfDay.ToString().Substring(0, 8) + "','" + g + "',1)");
return(g);
}
else
{
return("f");
}
}
/// <summary>
/// Gets the current assignment of the user
/// </summary>
/// <param name="output"></param>
/// <param name="userId">The assigned user</param>
/// <returns></returns>
public static bool FetchCurrentAssignment(ref Assignment output,
int userId)
{
SQLiteDataReader reader = DBI.DoPreparedQuery(
@"SELECT id, user, ship, role, start,
COALESCE(until, -1) AS until
FROM Assignment
WHERE user = @user AND until IS NULL LIMIT 1;" ,
new Tuple <string, object>("@user", userId));
if (reader != null && reader.Read())
{
output = Assignment.Factory(reader);
return(true);
}
return(false);
}
/// <summary>
/// Gets the list of all ships.
/// Does not include ships which were destroyed or decommed
/// more than a month ago
/// </summary>
/// <param name="output"></param>
/// <returns></returns>
public static bool FetchRegistry(ref List <UserShip> output)
{
output = new List <UserShip>();
SQLiteDataReader reader = DBI.DoQuery(
@"SELECT * FROM UserShip
WHERE (status != 1 AND status != 4)
OR statusDate > strftime('%s', 'now', '-7 days');" );
while (reader != null && reader.Read())
{
UserShip us = UserShip.Factory(reader);
output.Add(us);
}
return(true);
}
/// <summary>
/// Creates a new rate
/// </summary>
/// <param name="output"></param>
/// <param name="name"></param>
/// <param name="abrv"></param>
/// <param name="r2dur">Time before rank 2 expiration in seconds</param>
/// <param name="r1dur">Time before rank 1 expiration in seconds</param>
/// <returns></returns>
public static bool Create(ref Rate output, string name, string abrv,
long r2dur, long r1dur)
{
int result = DBI.DoPreparedAction(
@"INSERT INTO Rate (name, abrv, rank2duration, rank1duration)
VALUES (@name, @abrv, @rank2dur, @rank1dur);" ,
new Tuple <string, object>("@name", name),
new Tuple <string, object>("@abrv", abrv),
new Tuple <string, object>("@rank2dur", r2dur),
new Tuple <string, object>("@rank1dur", r1dur));
if (result == 1)
{
return(Rate.FetchById(ref output, DBI.LastInsertRowId));
}
return(false);
}
/// <summary>
/// Creates a new rank
/// </summary>
/// <param name="output"></param>
/// <param name="name"></param>
/// <param name="abrv"></param>
/// <param name="icon"></param>
/// <param name="ordering"></param>
/// <returns></returns>
public static bool Create(ref Rank output, string name, string abrv,
string icon, int ordering)
{
int result = DBI.DoPreparedAction(
@"INSERT INTO Rank (name, abrv, icon, ordering)
VALUES (@name, @abrv, @icon, @ordering);" ,
new Tuple <string, object>("@name", name),
new Tuple <string, object>("@abrv", abrv),
new Tuple <string, object>("@icon", icon),
new Tuple <string, object>("@ordering", ordering));
if (result == 1)
{
return(Rank.FetchById(ref output, DBI.LastInsertRowId));
}
return(false);
}
/// <summary>
/// Creates a new user without a primary rate
/// </summary>
/// <param name="output"></param>
/// <param name="name"></param>
/// <param name="auth0"></param>
/// <param name="rank"></param>
/// <returns></returns>
public static bool Create(ref User output, string name, string auth0,
int rank)
{
int result = DBI.DoPreparedAction(
@"INSERT INTO User (name, auth0, rank, rate, created)
VALUES (@name, @auth0, @rank, null,
strftime('%s','now'));" ,
new Tuple <string, object>("@name", name),
new Tuple <string, object>("@auth0", auth0),
new Tuple <string, object>("@rank", rank));
if (result == 1)
{
return(User.FetchById(ref output, DBI.LastInsertRowId));
}
return(false);
}
/// <summary>
/// Creates a new hull
/// </summary>
/// <param name="output"></param>
/// <param name="vendor"></param>
/// <param name="role"></param>
/// <param name="series"></param>
/// <param name="version"></param>
/// <param name="symbol"></param>
/// <param name="ordering"></param>
/// <returns></returns>
public static bool Create(ref Hull output, int vendor, int role,
string series, string version, string symbol, int ordering)
{
int result = DBI.DoPreparedAction(
@"INSERT INTO Hull (vendor, role, series, symbol, ordering)
VALUES (@vendor, @role, @series, @symbol, @ordering);" ,
new Tuple <string, object>("@vendor", vendor),
new Tuple <string, object>("@role", role),
new Tuple <string, object>("@series", series),
new Tuple <string, object>("@symbol", symbol),
new Tuple <string, object>("@ordering", ordering));
if (result == 1)
{
return(Hull.FetchById(ref output, DBI.LastInsertRowId));
}
return(false);
}
/// <summary>
/// Creates a new assignment with the given start and end dates
/// </summary>
/// <param name="output"></param>
/// <param name="user"></param>
/// <param name="ship"></param>
/// <param name="role"></param>
/// <param name="from"></param>
/// <param name="until"></param>
/// <returns></returns>
public static bool Create(ref Assignment output, int user, int ship,
int role, long from, long until)
{
int result = DBI.DoPreparedAction(
@"INSERT INTO Assignment (user, ship, role, start, until)
VALUES (@user, @ship, @role, @from, @until);" ,
new Tuple <string, object>("@user", user),
new Tuple <string, object>("@ship", ship),
new Tuple <string, object>("@role", role),
new Tuple <string, object>("@from", from),
new Tuple <string, object>("@until", until));
if (result == 1)
{
return(Assignment.FetchById(ref output, DBI.LastInsertRowId));
}
return(false);
}
/// <summary>
/// Updates a given assignment with an end date of today's date.
/// </summary>
/// <param name="userId"></param>
/// <param name="assignmentId"></param>
/// <returns></returns>
public static bool EndAssignment(int userId, int assignmentId)
{
int result = DBI.DoPreparedAction(
@"UPDATE Assignment
SET until = strftime('%s', 'now')
WHERE user = @user AND id = @assignment;" ,
new Tuple <string, object>("@user", userId),
new Tuple <string, object>("@assignment", assignmentId));
if (result == 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Updates only the status and as of date for a ship
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public static bool StoreUpdateStatus(UserShip input)
{
int result = DBI.DoPreparedAction(
@"UPDATE UserShip
SET status = @status, statusDate = strftime('%s', 'now')
WHERE id = @id;" ,
new Tuple <string, object>("@status", input.status),
new Tuple <string, object>("@id", input.id));
if (result == 1)
{
return(true);
}
else
{
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>
/// Updates a user
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public static bool Store(User input)
{
int result = DBI.DoPreparedAction(
@"UPDATE User SET name = @name,
auth0 = @auth0, rank = @rank,
rate = @rate WHERE id = @id;" ,
new Tuple <string, object>("@name", input.name),
new Tuple <string, object>("@auth0", input.auth0),
new Tuple <string, object>("@rank", input.rank),
new Tuple <string, object>("@rate", input.rate),
new Tuple <string, object>("@id", input.id));
if (result == 1)
{
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);
}
public JsonResult SaveFeedbackTR(Feedback feedback)
{
foreach (var prop in feedback.GetType().GetProperties())
{
if (feedback.GetType().GetProperty(prop.Name).GetValue(feedback, null) == null)
{
feedback.GetType().GetProperty(prop.Name).SetValue(feedback, "", null);
}
}
DBI dbi = new DBI();
string[] parameters =
{
"@ID",
"@Image",
"@ImageUrl",
"@Text",
"@Author",
"@Company",
"@OnAir"
};
object[] values =
{
feedback.ID,
feedback.Image,
feedback.ImageUrl,
feedback.Text,
feedback.Author,
feedback.Company,
feedback.OnAir
};
try
{
dbi.ExecuteWithParameters(new Procedure("pacetrSaveFeedback", parameters, values));
return(Json(true, JsonRequestBehavior.AllowGet));
}
catch (System.Exception ex)
{
return(Json(false, JsonRequestBehavior.AllowGet));
}
}
public void Save()
{
DBI dbi = new DBI();
string[] parameters =
{
"@Page",
"@IP",
"@Country",
"@Region",
"@City",
"@Latitude",
"@Longitude",
"@UserAgent",
"@Browser",
"@Desktop",
"@Mobile",
"@MobileOS",
"@MobileModel",
"@Date",
"@Time"
};
object[] values =
{
Page,
IP,
Country,
Region,
City,
Latitude,
Longitude,
UserAgent,
Browser,
Desktop,
Mobile,
MobileOS,
MobileModel,
Date,
Time
};
dbi.ExecuteWithParameters(new Procedure("paceLog", parameters, values));
}
/// <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>
/// Updates a rate
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public static bool Store(Rate input)
{
int result = DBI.DoPreparedAction(
@"UPDATE Rate SET name = @name, abrv = @abrv,
rank2duration = @rank2dur,
rank1duration = @rank1dur
WHERE id = @id;" ,
new Tuple <string, object>("@name", input.name),
new Tuple <string, object>("@abrv", input.abrv),
new Tuple <string, object>("@rank2dur", input.rank2duration),
new Tuple <string, object>("@rank1dur", input.rank1duration),
new Tuple <string, object>("@id", input.id));
if (result == 1)
{
return(true);
}
return(false);
}
/// <summary>
/// Updates a row
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public static bool Store(ShipEquipment input)
{
int result = DBI.DoPreparedAction(
@"UPDATE ShipEquipment
SET hull = @hull, ship = @ship
WHERE id = @id;" ,
new Tuple <string, object>("@hull", input.hull),
new Tuple <string, object>("@ship", input.ship),
new Tuple <string, object>("@id", input.id));
if (result == 1)
{
return(true);
}
else
{
return(false);
}
}
public ForumSystem()
{
_fm = new ForumManager();
_um = new UserManager();
_testFlag = false;
_db = new DB();
try
{
setup();
}
catch (ShouldNotHappenException)
{
throw new Exception("DB is badly maintained and is irrelevant.");
}
catch (Exception)
{
throw new Exception("Could not load data from DB.");
}
}
/// <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>
/// Creates a new rate. Automatically sets the earned date to today.
/// Calculates the expiration date if necessary.
/// </summary>
/// <param name="output"></param>
/// <param name="user"></param>
/// <param name="rate"></param>
/// <param name="rank"></param>
/// <returns></returns>
public static bool Create(ref StruckRate output, int user, int rate,
int rank)
{
string expQuery = getExpirationQuery(rank);
int result = DBI.DoPreparedAction(
$@"INSERT INTO StruckRate (id, user, rate,
rank, earned, expires)
VALUES (COALESCE((SELECT max(id) FROM StruckRate),0) + 1, @user, @rate,
@rank, strftime('%s', 'now'), {expQuery});" ,
new Tuple <string, object>("@user", user),
new Tuple <string, object>("@rate", rate),
new Tuple <string, object>("@rank", rank));
if (result == 1)
{
return(StruckRate.FetchById(ref output, DBI.LastInsertRowId));
}
return(false);
}
/// <summary>
/// Updates a hull in the database
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public static bool Store(Hull input)
{
int result = DBI.DoPreparedAction(
@"UPDATE Hull SET vendor = @vendor, role = @role,
series = @series, symbol = @symbol, ordering = @ordering
WHERE id = @id;" ,
new Tuple <string, object>("@vendor", input.vendor),
new Tuple <string, object>("@role", input.role),
new Tuple <string, object>("@series", input.series),
new Tuple <string, object>("@symbol", input.symbol),
new Tuple <string, object>("@ordering", input.ordering),
new Tuple <string, object>("@id", input.id));
if (result == 1)
{
return(true);
}
return(false);
}
/// <summary>
/// Updated a rate strike. Automatically moves the earned time up to
/// today and moves the expiration date accordingly.
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public static bool Store(StruckRate input)
{
string expQuery = getExpirationQuery(input.rank);
int result = DBI.DoPreparedAction(
$@"UPDATE StruckRate SET user = @user,
rate = @rate, rank = @rank,
earned = strftime('%s', 'now'), expires = {expQuery}
WHERE id = @id;" ,
new Tuple <string, object>("@user", input.user),
new Tuple <string, object>("@rate", input.rate),
new Tuple <string, object>("@rank", input.rank),
new Tuple <string, object>("@id", input.id));
if (result == 1)
{
return(true);
}
return(false);
}
/// <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);
}
public static bool Create(ref OperationRole output, string name,
int rate, bool ships, bool boats, bool squads, bool chanCdr)
{
int result = DBI.DoPreparedAction(
@"INSERT INTO OperationRole
(name, rate, onShips, onBoats, inSquads, channelCdr)
VALUES (@name, @rate, @ships, @boats, @squads, @chanCdr);" ,
new Tuple <string, object>("@name", name),
new Tuple <string, object>("@rate", rate),
new Tuple <string, object>("@ships", ships),
new Tuple <string, object>("@boats", boats),
new Tuple <string, object>("@squads", squads),
new Tuple <string, object>("@chanCdr", chanCdr));
if (result == 1)
{
return(OperationRole.FetchById(ref output, DBI.LastInsertRowId));
}
return(false);
}
/// <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>
/// Creates a new set of privileges for a user
/// </summary>
/// <param name="output"></param>
/// <param name="user"></param>
/// <param name="canPromote"></param>
/// <param name="canCertify"></param>
/// <param name="canAssign"></param>
/// <param name="canStartOps"></param>
/// <param name="isFleetAdmin"></param>
/// <returns></returns>
public static bool Create(ref UserPrivs output, int user,
bool canPromote, bool canCertify, bool canAssign, bool canStartOps,
bool isFleetAdmin)
{
int result = DBI.DoPreparedAction(
@"INSERT INTO UserPrivs (user, canPromote, canCertify,
canAssign, canStartOps, isFleetAdmin)
VALUES (@user, @promote, @cert, @assign, @ops, @fleetad);" ,
new Tuple <string, object>("@user", user),
new Tuple <string, object>("@promote", canPromote),
new Tuple <string, object>("@cert", canCertify),
new Tuple <string, object>("@assign", canAssign),
new Tuple <string, object>("@ops", canStartOps),
new Tuple <string, object>("@fleetad", isFleetAdmin));
if (result == 1)
{
return(UserPrivs.FetchByUser(ref output, user));
}
return(false);
}
/// <summary>
/// Deletes a single instance of a hull on a ship
/// </summary>
/// <param name="hullId"></param>
/// <returns></returns>
public static bool DeleteOneOfHullOnShip(int hullId, int shipId)
{
int result = DBI.DoPreparedAction(
@"DELETE FROM ShipEquipment
WHERE id IN (
SELECT id FROM ShipEquipment
WHERE hull = @hull AND ship = @ship
LIMIT 1
);" ,
new Tuple <string, object>("@hull", hullId),
new Tuple <string, object>("@ship", shipId));
if (result == 1)
{
return(true);
}
else
{
return(false);
}
}
