/// <summary>
/// Creates a new owned ship.
/// Ship starts in drydocked status.
/// </summary>
/// <param name="output"></param>
/// <param name="user"></param>
/// <param name="hull"></param>
/// <param name="insurance"></param>
/// <param name="name"></param>
/// <param name="status"></param>
/// <returns></returns>
public static bool Create(ref UserShip output, int user, int hull,
int insurance, string name, int status)
{
int result = DBI.DoPreparedAction(
$@"INSERT INTO UserShip (user, hull, insurance, number, name,
status, statusDate, final)
VALUES (@user, @hull, @insurance,
COALESCE((
SELECT MAX(number)+1 FROM UserShip
WHERE hull IN (
SELECT h1.id FROM Hull h1, Hull h2
WHERE h1.symbol = h2.symbol AND h2.id = @hull
)),1), @name, @status,
strftime('%s', 'now'), 0);" ,
new Tuple <string, object>("@user", user),
new Tuple <string, object>("@hull", hull),
new Tuple <string, object>("@insurance", insurance),
new Tuple <string, object>("@name", name),
new Tuple <string, object>("@status", status));
if (result == 1)
{
return(UserShip.FetchById(ref output, DBI.LastInsertRowId));
}
return(false);
}
/// <summary>
/// Sets the final flag on all instances of a given ship name so their
/// status cannot be changed. This is done when a new ship is created
/// with this name.
/// </summary>
/// <param name="name"></param>
public static bool FinalizeOlderShips(string name)
{
int result1 = DBI.DoPreparedAction(
@"UPDATE UserShip
SET status = 1, final = 1
WHERE name = @name
AND (status = 1 OR status = 2)" ,
new Tuple <string, object>("@name", name)
);
int result2 = DBI.DoPreparedAction(
@"UPDATE UserShip
SET final = 1
WHERE name = @name
AND status = 4" ,
new Tuple <string, object>("@name", name)
);
if (result1 >= 1 && result2 >= 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Deletes a struck rate for a user
/// </summary>
/// <param name="StruckID"></param>
/// <returns></returns>
public static bool DeleteById(int StruckID)
{
int result = DBI.DoPreparedAction(
"DELETE FROM StruckRate WHERE id = @id",
new Tuple <string, object>("@id", StruckID));
if (result == 1)
{
return(true);
}
return(false);
}
/// <summary>
/// Creates a new piece of equipment embarked on a ship
/// </summary>
/// <param name="output"></param>
/// <param name="hull"></param>
/// <param name="ship"></param>
/// <returns></returns>
public static bool Create(ref ShipEquipment output, int hull,
int ship)
{
int result = DBI.DoPreparedAction(
@"INSERT INTO ShipEquipment (hull, ship)
VALUES (@hull, @ship);" ,
new Tuple <string, object>("@hull", hull),
new Tuple <string, object>("@ship", ship));
if (result == 1)
{
return(ShipEquipment.FetchById(ref output, DBI.LastInsertRowId));
}
return(false);
}
/// <summary>
/// Creates a new Hull Role
/// </summary>
/// <param name="output"></param>
/// <param name="name"></param>
/// <param name="icon"></param>
/// <returns></returns>
public static bool Create(ref HullRole output, string name,
string icon = "")
{
int result = DBI.DoPreparedAction(
@"INSERT INTO HullRole (name, icon)
VALUES (@name, @icon);" ,
new Tuple <string, object>("@name", name),
new Tuple <string, object>("@icon", icon));
if (result == 1)
{
return(HullRole.FetchById(ref output, DBI.LastInsertRowId));
}
return(false);
}
/// <summary>
/// Updates a role in the table
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public static bool Store(AssignmentRole input)
{
int result = DBI.DoPreparedAction(
@"UPDATE AssignmentRole
SET name = @name, isCompany = @company
WHERE id = @id;" ,
new Tuple <string, object>("@name", input.name),
new Tuple <string, object>("@company", input.isCompany),
new Tuple <string, object>("@id", input.id));
if (result == 1)
{
return(true);
}
return(false);
}
/// <summary>
/// Creates a new assignment role
/// </summary>
/// <param name="output"></param>
/// <param name="name"></param>
/// <param name="isCompany"></param>
/// <returns></returns>
public static bool Create(ref AssignmentRole output, string name,
bool isCompany)
{
int result = DBI.DoPreparedAction(
@"INSERT INTO AssignmentRole (name, isCompany)
VALUES (@name, @company);" ,
new Tuple <string, object>("@name", name),
new Tuple <string, object>("@company", isCompany));
if (result == 1)
{
return(AssignmentRole.FetchById(ref output,
DBI.LastInsertRowId));
}
return(false);
}
/// <summary>
/// Creates a new assignment with a starting time of today's date
/// and a null ending date
/// </summary>
/// <param name="output"></param>
/// <param name="user"></param>
/// <param name="ship"></param>
/// <param name="role"></param>
/// <returns></returns>
public static bool Create(ref Assignment output, int user, int ship,
int role)
{
int result = DBI.DoPreparedAction(
@"INSERT INTO Assignment (user, ship, role, start)
VALUES (@user, @ship, @role, strftime('%s', 'now'));" ,
new Tuple <string, object>("@user", user),
new Tuple <string, object>("@ship", ship),
new Tuple <string, object>("@role", role));
if (result == 1)
{
return(Assignment.FetchById(ref output, DBI.LastInsertRowId));
}
return(false);
}
/// <summary>
/// Updates a role
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public static bool Store(HullRole input)
{
int result = DBI.DoPreparedAction(
@"UPDATE HullRole
SET name = @name, icon = @icon
WHERE id = @id;" ,
new Tuple <string, object>("@name", input.name),
new Tuple <string, object>("@icon", input.icon),
new Tuple <string, object>("@id", input.id));
if (result == 1)
{
return(true);
}
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 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 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>
/// 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>
/// 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>
/// 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>
/// 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);
}
}
/// <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>
/// 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>
/// 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);
}
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>
/// 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);
}
}
/// <summary>
/// Updates an assignment row in the table
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public static bool Store(Assignment input)
{
int result = DBI.DoPreparedAction(
@"UPDATE Assignment
SET user = @user, ship = @ship,
role = @role, start = @start, until = @until
WHERE id = @id;" ,
new Tuple <string, object>("@user", input.user),
new Tuple <string, object>("@ship", input.ship),
new Tuple <string, object>("@role", input.role),
new Tuple <string, object>("@from", input.from),
new Tuple <string, object>("@until", input.until),
new Tuple <string, object>("@id", input.id));
if (result == 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Updates a ship
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public static bool Store(UserShip input)
{
int result = DBI.DoPreparedAction(
@"UPDATE UserShip SET user = @user, hull = @hull,
insurance = @insurance, number = @number, name = @name,
status = @status, statusDate = @statusDate, final = @final
WHERE id = @id;" ,
new Tuple <string, object>("@user", input.user),
new Tuple <string, object>("@hull", input.hull),
new Tuple <string, object>("@insurance", input.insurance),
new Tuple <string, object>("@number", input.number),
new Tuple <string, object>("@name", input.name),
new Tuple <string, object>("@status", input.status),
new Tuple <string, object>("@statusDate", input.statusDate),
new Tuple <string, object>("@final", input.final),
new Tuple <string, object>("@id", input.id));
if (result == 1)
{
return(true);
}
return(false);
}