/// <summary>
/// Fetches a contributor object from the database.
/// </summary>
/// <param name="xenforoID">The contributor's xenforo ID, if any.</param>
/// <returns>A contributor object, or null if not found.</returns>
public Contributor GetByXenforoId(int xenforoID)
{
#region SQL(select_con)
string select_con = PrepareSql($@"
SELECT *
FROM {Main.Config.ContributorTableName}
WHERE XenforoID = @XenforoID" );
#endregion
#region SQL(select_a)
string select_a = $@"
SELECT UserID
FROM {Main.Config.ContributorAccountsTableName}
WHERE ContributorID = @ID" ;
#endregion
using (var db = OpenConnection())
{
Contributor con = (Contributor)db.QuerySingleOrDefault <Contributor.DataModel>(select_con, new { XenforoID = xenforoID });
if (con == null)
{
return(null);
}
con.Accounts = new List <int>(db.Query <int>(select_a, con.ToDataModel()));
return(con);
}
}
/// <summary>
/// Fetches a contributor object from the database.
/// </summary>
/// <param name="userID">The user ID of an authenticated account.</param>
/// <returns>A contributor object, or null if not found.</returns>
public Contributor Get(int userID)
{
#region SQL(select_id)
string select_id = PrepareSql($@"
SELECT ContributorID
FROM {Main.Config.ContributorAccountsTableName}
WHERE UserID = @UserID" );
#endregion
#region SQL(select_con)
string select_con = PrepareSql($@"
SELECT *
FROM {Main.Config.ContributorTableName}
WHERE ID = @Id" );
#endregion
#region SQL(select_a)
string select_a = PrepareSql($@"
SELECT UserID
FROM {Main.Config.ContributorAccountsTableName}
WHERE ContributorID = @ID" );
#endregion
int contributorID;
using (var db = OpenConnection())
{
contributorID = db.QuerySingleOrDefault <int>(select_id, new { UserID = userID });
if (contributorID == 0)
{
return(null);
}
Contributor con = (Contributor)db.QuerySingleOrDefault <Contributor.DataModel>(select_con, new { Id = contributorID });
// The foreign key constraints would have to fail for this to happen, but better safe than sorry
if (con == null)
{
return(null);
}
con.Accounts = new List <int>(db.Query <int>(select_a, con.ToDataModel()));
return(con);
}
}
/// <summary>
/// Sends updated contributor data to the database.
/// Logs any exception thrown.
/// </summary>
/// <param name="contributor">The contributor to update with the already-updated values set.</param>
/// <param name="updates">The list of values to update.</param>
/// <returns>True if it updates one row, false if anything else..</returns>
public bool Update(Contributor contributor, ContributorUpdates updates)
{
if (updates == 0)
return true;
List<string> updatesList = new List<string>();
if ((updates & ContributorUpdates.XenforoID) == ContributorUpdates.XenforoID)
updatesList.Add("XenforoID = @XenforoID");
if ((updates & ContributorUpdates.TotalCredits) == ContributorUpdates.TotalCredits)
updatesList.Add("TotalCredits = @TotalCredits");
if ((updates & ContributorUpdates.LastDonation) == ContributorUpdates.LastDonation)
updatesList.Add("LastDonation = @LastDonation");
if ((updates & ContributorUpdates.LastAmount) == ContributorUpdates.LastAmount)
updatesList.Add("LastAmount = @LastAmount");
if ((updates & ContributorUpdates.Tier) == ContributorUpdates.Tier)
updatesList.Add("Tier = @Tier");
if ((updates & ContributorUpdates.ChatColor) == ContributorUpdates.ChatColor)
updatesList.Add("ChatColor = @ChatColor");
if ((updates & ContributorUpdates.Notifications) == ContributorUpdates.Notifications)
updatesList.Add("Notifications = @Notifications");
if ((updates & ContributorUpdates.Settings) == ContributorUpdates.Settings)
updatesList.Add("Settings = @Settings");
#region SQL(update)
string update = PrepareSql($@"
UPDATE {Main.Config.ContributorTableName}
SET {String.Join(", ", updatesList)}
WHERE ID = @ID");
#endregion
try
{
lock (syncLock)
{
using (var db = OpenConnection())
{
return db.Execute(update, contributor.ToDataModel()) == 1;
}
}
}
catch (Exception ex)
{
TShock.Log.ConsoleError("{0}\n{1}\n{2}",
"CTRS: An error occurred while updating a contributor's info",
$"Message: {ex.Message}",
"Check logs for more details");
TShock.Log.Error(ex.ToString());
return false;
}
}
/// <summary>
/// Inserts data for a contributor object into the database.
/// The contributor object must have a Xenforo Id associated with it.
/// </summary>
/// <param name="contributor">The contributor object to save.</param>
/// <returns>A <see cref="bool"/> representing whether the operation was successful or not.</returns>
public bool Add(Contributor contributor)
{
#region SQL(query)
string query = PrepareSql($@"
INSERT INTO {Main.Config.ContributorTableName} (
XenforoID,
TotalCredits,
LastAmount,
Notifications,
Settings
)
VALUES (
@XenforoID,
@TotalCredits,
@LastAmount,
@Notifications,
@Settings
)");
if (contributor.LastDonation != DateTime.MinValue)
{
query = PrepareSql($@"
INSERT INTO {Main.Config.ContributorTableName} (
XenforoID,
TotalCredits,
LastDonation,
LastAmount,
Notifications,
Settings
)
VALUES (
@XenforoID,
@TotalCredits,
@LastDonation,
@LastAmount,
@Notifications,
@Settings
)");
}
#endregion
#region SQL(query_a)
string query_a = PrepareSql($@"
INSERT INTO {Main.Config.ContributorAccountsTableName} (
UserID,
ContributorID
)
VALUES (
@UserID,
@ContributorID
)");
#endregion
try
{
lock (syncLock)
{
using (var db = OpenConnection())
{
if (db.Execute(query, contributor.ToDataModel()) == 1)
{
contributor.Id = GetLastInsertId();
for (int i = 0; i < contributor.Accounts.Count; i++)
{
if (db.Execute(query_a, new { UserID = contributor.Accounts[i], ContributorID = contributor.Id }) == 0)
{
return false;
}
}
return true;
}
return false;
}
}
}
catch (Exception ex)
{
if (Main.Config.LogDatabaseErrors)
{
TShock.Log.ConsoleError($"CTRS-DB: Unable to add contributor with xenforoID:{contributor.XenforoId.Value}\nMessage: " + ex.Message);
TShock.Log.Error(ex.ToString());
}
return false;
}
}
/// <summary>
/// Sends updated contributor data to the database.
/// Logs any exception thrown.
/// </summary>
/// <param name="contributor">The contributor to update with the already-updated values set.</param>
/// <param name="updates">The list of values to update.</param>
/// <returns>True if it updates one row, false if anything else..</returns>
public bool Update(Contributor contributor, ContributorUpdates updates)
{
if (updates == 0)
{
return(true);
}
List <string> updatesList = new List <string>();
if ((updates & ContributorUpdates.XenforoID) == ContributorUpdates.XenforoID)
{
updatesList.Add("XenforoID = @XenforoID");
}
if ((updates & ContributorUpdates.TotalCredits) == ContributorUpdates.TotalCredits)
{
updatesList.Add("TotalCredits = @TotalCredits");
}
if ((updates & ContributorUpdates.LastDonation) == ContributorUpdates.LastDonation)
{
updatesList.Add("LastDonation = @LastDonation");
}
if ((updates & ContributorUpdates.LastAmount) == ContributorUpdates.LastAmount)
{
updatesList.Add("LastAmount = @LastAmount");
}
if ((updates & ContributorUpdates.Tier) == ContributorUpdates.Tier)
{
updatesList.Add("Tier = @Tier");
}
if ((updates & ContributorUpdates.ChatColor) == ContributorUpdates.ChatColor)
{
updatesList.Add("ChatColor = @ChatColor");
}
if ((updates & ContributorUpdates.Notifications) == ContributorUpdates.Notifications)
{
updatesList.Add("Notifications = @Notifications");
}
if ((updates & ContributorUpdates.Settings) == ContributorUpdates.Settings)
{
updatesList.Add("Settings = @Settings");
}
#region SQL(update)
string update = PrepareSql($@"
UPDATE {Main.Config.ContributorTableName}
SET {String.Join(", ", updatesList)}
WHERE ID = @ID" );
#endregion
try
{
lock (syncLock)
{
using (var db = OpenConnection())
{
return(db.Execute(update, contributor.ToDataModel()) == 1);
}
}
}
catch (Exception ex)
{
TShock.Log.ConsoleError("{0}\n{1}\n{2}",
"CTRS: An error occurred while updating a contributor's info",
$"Message: {ex.Message}",
"Check logs for more details");
TShock.Log.Error(ex.ToString());
return(false);
}
}
/// <summary>
/// Inserts data for a contributor object into the database.
/// The contributor object must have a Xenforo Id associated with it.
/// </summary>
/// <param name="contributor">The contributor object to save.</param>
/// <returns>A <see cref="bool"/> representing whether the operation was successful or not.</returns>
public bool Add(Contributor contributor)
{
#region SQL(query)
string query = PrepareSql($@"
INSERT INTO {Main.Config.ContributorTableName} (
XenforoID,
TotalCredits,
LastAmount,
Notifications,
Settings
)
VALUES (
@XenforoID,
@TotalCredits,
@LastAmount,
@Notifications,
@Settings
)" );
if (contributor.LastDonation != DateTime.MinValue)
{
query = PrepareSql($@"
INSERT INTO {Main.Config.ContributorTableName} (
XenforoID,
TotalCredits,
LastDonation,
LastAmount,
Notifications,
Settings
)
VALUES (
@XenforoID,
@TotalCredits,
@LastDonation,
@LastAmount,
@Notifications,
@Settings
)" );
}
#endregion
#region SQL(query_a)
string query_a = PrepareSql($@"
INSERT INTO {Main.Config.ContributorAccountsTableName} (
UserID,
ContributorID
)
VALUES (
@UserID,
@ContributorID
)" );
#endregion
try
{
lock (syncLock)
{
using (var db = OpenConnection())
{
if (db.Execute(query, contributor.ToDataModel()) == 1)
{
contributor.Id = GetLastInsertId();
for (int i = 0; i < contributor.Accounts.Count; i++)
{
if (db.Execute(query_a, new { UserID = contributor.Accounts[i], ContributorID = contributor.Id }) == 0)
{
return(false);
}
}
return(true);
}
return(false);
}
}
}
catch (Exception ex)
{
if (Main.Config.LogDatabaseErrors)
{
TShock.Log.ConsoleError($"CTRS-DB: Unable to add contributor with xenforoID:{contributor.XenforoId.Value}\nMessage: " + ex.Message);
TShock.Log.Error(ex.ToString());
}
return(false);
}
}