/// <summary>
/// Insert Unit Kills History
/// </summary>
/// <param name="playerName">The Player Name</param>
/// <param name="effectiveDate">The Effective Date</param>
/// <param name="score">The Score</param>
/// <param name="rank">The Rank</param>
public void InsertUnitKillsHistory(string playerName, DateTime effectiveDate, long score, int rank)
{
try
{
string sql =
$" INSERT INTO {CotgDataContract.UnitKillsHistory.TABLE_NAME} ( " +
$" {CotgDataContract.UnitKillsHistory.COLUMN_PLAYERNAME_NAME}, " +
$" {CotgDataContract.UnitKillsHistory.COLUMN_EFFECTIVEDATE_NAME}, " +
$" {CotgDataContract.UnitKillsHistory.COLUMN_SCORE_NAME}, " +
$" {CotgDataContract.UnitKillsHistory.COLUMN_RANK_NAME} " +
$" ) VALUES ( " +
$" @playerName, @effectiveDate, @score, @rank " +
$" ) ";
using (var connection = CreateConnection())
{
connection.Open();
using (var command = CreateCommand(connection, CommandType.Text, sql))
{
command.Parameters.Add(CreateParameter("@playerName", DbType.String, playerName));
command.Parameters.Add(CreateParameter("@effectiveDate", DbType.Double, DateUtility.DateToEpoch(effectiveDate)));
command.Parameters.Add(CreateParameter("@score", DbType.Int64, score));
command.Parameters.Add(CreateParameter("@rank", DbType.Int32, rank));
command.ExecuteNonQuery();
}
}
}
catch (Exception caught)
{
logger.Error("Unexpected Error Inserting Unit Kills History Record", caught);
throw;
}
}
/// <summary>
/// Get Unit Kills History
/// </summary>
/// <param name="effectiveDate">The Effective Date</param>
/// <returns>The Unit Kills History</returns>
public IEnumerable <UnitKillsHistoryModel> GetUnitKillsHistory(DateTime effectiveDate)
{
try
{
string sql =
$" SELECT " +
$" UKH.* " +
$" FROM " +
$" {CotgDataContract.UnitKillsHistory.TABLE_NAME} UKH " +
$" WHERE " +
$" NOT EXISTS ( " +
$" SELECT 1 " +
$" FROM {CotgDataContract.UnitKillsHistory.TABLE_NAME} UKH2 " +
$" WHERE " +
$" UKH2.{CotgDataContract.UnitKillsHistory.COLUMN_PLAYERNAME_NAME} = UKH.{CotgDataContract.UnitKillsHistory.COLUMN_PLAYERNAME_NAME} " +
$" AND UKH2.{CotgDataContract.UnitKillsHistory.COLUMN_EFFECTIVEDATE_NAME} > UKH.{CotgDataContract.UnitKillsHistory.COLUMN_EFFECTIVEDATE_NAME} " +
$" AND UKH2.{CotgDataContract.UnitKillsHistory.COLUMN_EFFECTIVEDATE_NAME} <=@effectiveDate " +
$" ) " +
$" AND UKH.{CotgDataContract.UnitKillsHistory.COLUMN_EFFECTIVEDATE_NAME} <= @effectiveDate " +
$" ORDER BY " +
$" UKH.{CotgDataContract.UnitKillsHistory.COLUMN_SCORE_NAME} DESC; ";
using (var connection = CreateConnection())
{
connection.Open();
using (var command = CreateCommand(connection, CommandType.Text, sql))
{
command.Parameters.Add(CreateParameter("@effectiveDate", DbType.Double, DateUtility.DateToEpoch(effectiveDate)));
var results = new List <UnitKillsHistoryModel>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var unitKillsHistoryModel = new Models.SQLiteUnitKillsHistoryModel(reader);
results.Add(unitKillsHistoryModel);
}
}
return(results);
}
}
}
catch (Exception caught)
{
logger.Error("Unexpected Error Getting Unit Kills History", caught);
throw;
}
}
