/// <summary>
/// Update the year stats
/// </summary>
public void UpdateYearStatsInternal()
{
DateTime currentDate = DateTime.Today;
int lastYear = currentDate.Year - 1;
// If there are no stats for last year, so this will only run if a new year occurs!
YearStats lastYearStats = GetYearStats(lastYear);
if (lastYearStats == null)
{
YearStats newStats = new YearStats()
{
Year = lastYear,
StartListeners = GetListenersAtYearStart(lastYear),
EndListeners = GetListenersByStatus(ListenerStates.ACTIVE).Count,
NewListeners = GetNewListenersForYear(lastYear),
AverageSent = GetAverageDispatchedWallets(lastYear),
AveragePaused = GetAveragePausedWallets(lastYear),
AvListeners = GetAverageListenersForYear(lastYear),
InactiveTotal = GetInactiveWalletNumbers(),
DeletedListeners = GetLostListenersForYear(lastYear),
MagazinesSent = 0, // TODO (L) Fill this MagazinesSent feld in?
MagazineTotal = 0, // TODO (L) Fill this MagazineTotal feld in?
DeletedTotal = GetListenersByStatus(ListenerStates.DELETED).Count,
MemStickPlayerLoanTotal = GetMemorySticksOnLoan(),
SentTotal = GetWalletsDispatchedForYear(lastYear),
PausedTotal = GetListenersByStatus(ListenerStates.PAUSED).Count,
PercentSent = 0 // TODO (L) Fill this PercentSent feld in?
};
repoLayer.InsertYearStats(connection, newStats);
}
}
/// <summary>
/// Delete yearly stats
/// </summary>
/// <param name="objConn"></param>
/// <param name="yearStats"></param>
public void DeleteYearStats(SQLiteConnection objConn, YearStats yearStats)
{
if (yearStats == null)
{
throw new ArgumentNullException("yearStats");
}
DoNoResultQuery(objConn, "DELETE FROM YearStats WHERE Year = " + yearStats.Year);
}
/// <summary>
/// Insert into the year stats
/// </summary>
/// <param name="objConn"></param>
/// <param name="yearStats"></param>
public void InsertYearStats(SQLiteConnection objConn, YearStats yearStats)
{
if (yearStats == null)
{
throw new ArgumentNullException("yearStats");
}
DoNoResultQuery(objConn, "INSERT INTO YearStats (Year, StartListeners, EndListeners, NewListeners, DeletedListeners, AverageListeners, InactiveTotal, MagazineTotal, AverageSent, SentTotal, MagazinesSent, PercentSent, MemStickPlayerLoanTotal, PausedTotal, AveragePaused, DeletedTotal) VALUES (" + yearStats.Year + ", " + yearStats.StartListeners + ", " + yearStats.EndListeners + ", " + yearStats.NewListeners + ", " + yearStats.DeletedListeners + ", " + yearStats.AvListeners + ", " + yearStats.InactiveTotal + ", " + yearStats.MagazineTotal + ", " + yearStats.AverageSent + ", " + yearStats.SentTotal + ", " + yearStats.MagazinesSent + ", " + yearStats.PercentSent + ", " + yearStats.MemStickPlayerLoanTotal + ", " + yearStats.PausedTotal + ", " + yearStats.AveragePaused + ", " + yearStats.DeletedTotal + ");");
}
/// <summary>
/// Update some year stats
/// </summary>
/// <param name="objConn"></param>
/// <param name="yearStats"></param>
public void UpdateYearStats(SQLiteConnection objConn, YearStats yearStats)
{
if (yearStats == null)
{
throw new ArgumentNullException("yearStats");
}
string sql = "UPDATE YearStats SET StartListeners = " + yearStats.StartListeners + ", EndListeners = " + yearStats.EndListeners + ", NewListeners = " + yearStats.NewListeners + ", DeletedListeners = " + yearStats.DeletedListeners + ", AverageListeners = " + yearStats.AvListeners + ", InactiveTotal = " + yearStats.InactiveTotal + ", MagazineTotal = " + yearStats.MagazineTotal + ", AverageSent = " + yearStats.AverageSent + ", SentTotal = " + yearStats.SentTotal + ", MagazinesSent = " + yearStats.MagazinesSent + ", PercentSent = " + yearStats.PercentSent + ", MemStickPlayerLoanTotal = " + yearStats.MemStickPlayerLoanTotal + ", PausedTotal = " + yearStats.PausedTotal + ", AveragePaused = " + yearStats.AveragePaused + ", DeletedTotal = " + yearStats.DeletedTotal + " WHERE Year = " + yearStats.Year;
DoNoResultQuery(objConn, sql);
}
private void SetYear(int year)
{
currentYear = year;
lblYear.Text = year.ToString();
try {
// Set the year stats.
YearStats yearStats = serviceLayer.GetYearStats(year);
lstYearStats.Items.Clear();
//Add items in the listview
string[] arr = new string[7];
ListViewItem itm = null;
arr[0] = yearStats.StartListeners.ToString();
arr[1] = yearStats.EndListeners.ToString();
arr[2] = yearStats.SentTotal.ToString();
arr[3] = yearStats.MagazinesSent.ToString();
arr[4] = yearStats.MemStickPlayerLoanTotal.ToString();
arr[5] = yearStats.NewListeners.ToString();
itm = new ListViewItem(arr);
lstYearStats.Items.Add(itm);
} catch (Exception ex) {
// Do not log if the year is the current year as this would be expected.
if (!(year == DateTime.Now.Year))
{
log.Warn(ex, "Could not load yearly stats for year: " + year);
}
}
// Set the weekly stats.
lstWeekStats.Items.Clear();
List <WeeklyStats> weeklyStats = serviceLayer.GetWeeklyStatsForYear(year);
for (int index = 0; index <= weeklyStats.Count - 1; index++)
{
WeeklyStats weekStats = weeklyStats[index];
//Add items in the listview
string[] arr = new string[7];
ListViewItem itm = null;
arr[0] = weekStats.WeekNumber.ToString();
arr[1] = weekStats.ScannedIn.ToString();
arr[2] = weekStats.ScannedOut.ToString();
arr[3] = weekStats.PausedCount.ToString();
arr[4] = weekStats.WeekDate.ToNiceStr();
arr[5] = weekStats.TotalListeners.ToString();
itm = new ListViewItem(arr);
lstWeekStats.Items.Add(itm);
}
}
/// <summary>
/// Insert example year stats
/// </summary>
private void InsertYearStats()
{
YearStats y1 = new YearStats()
{
AveragePaused = 1, AverageSent = 2, AvListeners = 3, DeletedListeners = 4, DeletedTotal = 5, EndListeners = 6, InactiveTotal = 7, MagazinesSent = 8, MagazineTotal = 9, MemStickPlayerLoanTotal = 10, NewListeners = 11, PausedTotal = 12, PercentSent = 13, SentTotal = 14, StartListeners = 15, Year = 2016
};
serviceLayer.SaveYearStats(y1);
YearStats y2 = new YearStats()
{
AveragePaused = 21, AverageSent = 22, AvListeners = 23, DeletedListeners = 24, DeletedTotal = 25, EndListeners = 26, InactiveTotal = 27, MagazinesSent = 28, MagazineTotal = 29, MemStickPlayerLoanTotal = 30, NewListeners = 31, PausedTotal = 32, PercentSent = 33, SentTotal = 34, StartListeners = 35, Year = 2017
};
serviceLayer.SaveYearStats(y2);
}
/// <summary>
/// Get all year stats
/// </summary>
/// <param name="objConn"></param>
/// <returns></returns>
public List <YearStats> GetYearStats(SQLiteConnection objConn)
{
List <YearStats> theYearStats = new List <YearStats>();
SQLiteCommand objCommand = null;
SQLiteDataReader objReader = null;
try
{
//Create a new SQL command to read all records from the customer table
objCommand = objConn.CreateCommand();
objCommand.CommandText = "SELECT * FROM YearStats";
log.Debug("QUERY: " + objCommand.CommandText);
//Execute the command returning a reader object
objReader = objCommand.ExecuteReader();
//Iterate through the rows in the reader, if we have one, we have a match!
while ((objReader.Read()))
{
YearStats tempStats = YearlyStatsFromObject(objReader);
theYearStats.Add(tempStats);
}
objReader.Close();
}
finally
{
//Cleanup and close the connection
if ((objReader != null))
{
objReader.Close();
}
if ((objCommand != null))
{
objCommand.Dispose();
}
}
// Return the year stats.
return(theYearStats);
}
public void Repo_YearStats_Tests()
{
YearStats toInsert = CreateExtensions.DummyYearStats();
// Insert the year stats
repoLayer.InsertYearStats(connection, toInsert);
// Edit and re-insert.
toInsert.MemStickPlayerLoanTotal = 1000;
toInsert.Year = 2017;
repoLayer.InsertYearStats(connection, toInsert);
// Check the results
List <YearStats> results = repoLayer.GetYearStats(connection);
Assert.AreEqual(2, results.Count);
Assert.AreEqual(CreateExtensions.DummyYearStats().Serialize(), results[0].Serialize());
Assert.AreEqual(toInsert.Serialize(), results[1].Serialize());
// Update one of the listeners
results[1].AvListeners = 19;
YearStats updated = results[1];
repoLayer.UpdateYearStats(connection, updated);
// Check the results
results = repoLayer.GetYearStats(connection);
Assert.AreEqual(2, results.Count);
Assert.AreEqual(CreateExtensions.DummyYearStats().Serialize(), results[0].Serialize());
Assert.AreEqual(updated.Serialize(), results[1].Serialize());
// Delete a listener
repoLayer.DeleteYearStats(connection, results[1]);
// Check the results
results = repoLayer.GetYearStats(connection);
Assert.AreEqual(1, results.Count);
Assert.AreEqual(CreateExtensions.DummyYearStats().Serialize(), results[0].Serialize());
}
/// <summary>
/// Get the year stats from a database object
/// </summary>
/// <param name="myReader"></param>
/// <returns></returns>
private static YearStats YearlyStatsFromObject(SQLiteDataReader myReader)
{
YearStats tempStats = new YearStats();
tempStats.Year = (int)(long)myReader["Year"];
tempStats.StartListeners = (int)(long)myReader["StartListeners"];
tempStats.EndListeners = (int)(long)myReader["EndListeners"];
tempStats.NewListeners = (int)(long)myReader["NewListeners"];
tempStats.DeletedListeners = (int)(long)myReader["DeletedListeners"];
tempStats.AvListeners = (int)(long)myReader["AverageListeners"];
tempStats.InactiveTotal = (int)(long)myReader["InactiveTotal"];
tempStats.MagazineTotal = (int)(long)myReader["MagazineTotal"];
tempStats.AverageSent = (int)(long)myReader["AverageSent"];
tempStats.SentTotal = (int)(long)myReader["SentTotal"];
tempStats.MagazinesSent = (int)(long)myReader["MagazinesSent"];
tempStats.PercentSent = (int)(long)myReader["PercentSent"];
tempStats.MemStickPlayerLoanTotal = (int)(long)myReader["MemStickPlayerLoanTotal"];
tempStats.PausedTotal = (int)(long)myReader["PausedTotal"];
tempStats.AveragePaused = (int)(long)myReader["AveragePaused"];
tempStats.DeletedTotal = (int)(long)myReader["DeletedTotal"];
return(tempStats);
}
/// <summary>
/// Create dummy year stats
/// </summary>
/// <returns></returns>
public static YearStats DummyYearStats()
{
YearStats temp = new YearStats()
{
AverageSent = 1,
AvListeners = 2,
AveragePaused = 3,
EndListeners = 4,
InactiveTotal = 5,
MagazinesSent = 6,
MagazineTotal = 7,
NewListeners = 8,
DeletedListeners = 9,
DeletedTotal = 10,
PercentSent = 11,
SentTotal = 12,
StartListeners = 13,
PausedTotal = 14,
MemStickPlayerLoanTotal = 15,
Year = 2016
};
return(temp);
}
private void Button1_Click(object sender, EventArgs e)
{
YearStats tempStats = serviceLayer.GetYearStats(2013);
Interaction.MsgBox(tempStats.EndListeners);
}
/// <summary>
/// Update the year stats
/// </summary>
/// <param name="stats"></param>
/// <returns></returns>
public bool UpdateYearStats(YearStats stats)
{
repoLayer.UpdateYearStats(connection, stats);
return(true);
}
/// <summary>
/// Save some year stats
/// </summary>
/// <param name="stats"></param>
/// <returns></returns>
public bool SaveYearStats(YearStats stats)
{
repoLayer.InsertYearStats(connection, stats);
return(true);
}
