using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
namespace ReputationLib
{
public class DisplayCategories : List<RepCategory>
{
public RepCategory Category { get; set; }
}
public class RepItemCollection : List<RepItem>
{
private const int MAX_DAYS = 365;
private int UserID = Globals.UserID;
//--------------------------------------------------------------------------------------
/// <summary>
/// Constructor
/// </summary>
public RepItemCollection()
{
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Retrieves existing data from the database.
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public bool GetData()
{
if (Globals.UserID <= 0)
{
throw new Exception("Codeproject UserID not specified.");
}
if (string.IsNullOrEmpty(Globals.ConnectionString))
{
throw new Exception("Connection string not specified.");
}
bool result = false;
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
DataTable data = new DataTable();
DateTime thePast = DateTime.Now.AddYears(-1);
try
{
conn = new SqlConnection(Globals.ConnectionString);
cmd = new SqlCommand("GetData", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@user", Globals.UserID);
cmd.Parameters.AddWithValue("@date", thePast);
conn.Open();
reader = cmd.ExecuteReader();
if (reader != null && reader.HasRows)
{
data.Load(reader);
PopulateList(data);
result = true;
}
}
catch (Exception ex)
{
if (ex != null) {}
}
finally
{
if (conn != null)
{
conn.Close();
}
}
return result;
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Adds or updates a record to the list. This is so that the user can scrape manually
/// multiple times during the day and still update the database with the most recent
/// values.
/// </summary>
/// <param name="cat"></param>
/// <param name="date"></param>
/// <param name="user"></param>
/// <param name="points"></param>
public void AddOrUpdate(RepCategory cat, DateTime date, int user, int points)
{
int change = 0;
List<RepItem> items = null;
// We're looking for an item with the current category ID, for the user, on the
// specified date. We should have only one
items = (from item in this
where (item.Category == cat && item.CPUserID == user)
select item).ToList();
RepItem lastItem = null;
if (items.Count > 0)
{
lastItem = (from item in items
where (item.Category == cat && item.CPUserID == user && item.TimeScraped.Date == date.Date)
select item).LastOrDefault();
}
// 16Aug2011 - jms - updating/adding from a manual scraping event was
// incorrectly adding a new item. to the collection (the database update
// was happening correctly).
// if we found no matching items, we add a new one
if (lastItem == null)
{
if (items.Count > 0)
{
lastItem = (from item in items
where (item.Category == cat && item.CPUserID == user)
select item).LastOrDefault();
if (lastItem != null)
{
change = points - lastItem.Value;
}
}
this.Add(new RepItem()
{
Category = cat,
CPUserID = user,
TimeScraped = date,
Value = points,
ChangeValue = change
});
}
else
// otherwise we decide how to modify the item we found
{
// set its value
lastItem.Value = points;
if (items.Count > 1)
{
// set the changed value
RepItem prevItem = items[items.Count - 2];
change = points - prevItem.Value;
}
lastItem.ChangeValue = change;
}
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Updates the database with the most recently scraped data
/// </summary>
public void UpdateDatabase(int mode = 0)
{
RemoveExpiredData();
SqlConnection conn = null;
SqlCommand cmd = null;
string query = "";
int records = 0;
DateTime now = DateTime.Now.Date;
// get all of todays records
List<RepItem> items;
int total = 0;
switch (mode)
{
default : // just the most recent data (mode 0)
items = (from item in this
where item.TimeScraped == now && item.CPUserID == Globals.UserID
select item).ToList();
// sum the categories (except the RepCategory.Total)
total = items.Sum(item => (item.Category != RepCategory.Total) ? item.Value : 0);
break;
case 1 : // db1 Total adjustment
items = (from item in this
where item.Category == RepCategory.Total && item.CPUserID == Globals.UserID
select item).ToList();
break;
}
foreach(RepItem item in items)
{
// In mode 0, we only have the most recently scraped data, so we need
// to adjust our total to account for the caching issue on
// Codeproject.
if (mode == 0 && item.Category == RepCategory.Total)
{
item.Value = total;
}
query = "AddOrUpdate";
try
{
if (conn == null)
{
conn = new SqlConnection(Globals.ConnectionString);
}
if (cmd == null)
{
cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@user", SqlDbType.Int);
cmd.Parameters.Add("@date", SqlDbType.DateTime);
cmd.Parameters.Add("@category", SqlDbType.Int);
cmd.Parameters.Add("@points", SqlDbType.Int);
}
cmd.CommandText = query;
cmd.Parameters["@user"].Value = item.CPUserID;
cmd.Parameters["@date"].Value = item.TimeScraped;
cmd.Parameters["@category"].Value = (int)(item.Category);
cmd.Parameters["@points"].Value = item.Value;
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
records = cmd.ExecuteNonQuery();
if (records != 1)
{
throw new Exception("Insert did not succeed");
} }
catch (Exception ex)
{
if (ex != null) {}
}
}
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Populates the colection with rata retrieved from the database.
/// </summary>
/// <param name="data"></param>
private void PopulateList(DataTable data)
{
foreach (DataRow row in data.Rows)
{
int value = Convert.ToInt32(row["PointValue"]);
RepItem item = new RepItem()
{
Category = Globals.IntToEnum (Convert.ToInt32(row["Category"]), RepCategory.Unknown),
TimeScraped = Convert.ToDateTime(row["ScrapeDate"]),
Value = value,
CPUserID = Convert.ToInt32 (row["CPUserID"]),
ChangeValue = 0
};
this.Add(item);
}
// add missing days
FillDataHoles();
// If true, the repcollection class will adjust all values for category Total
// to the actual sum of the sub-categories. This will account for CodeProject
// caching issues discovered on 19Aug2011. The only way this can be true is
// if the user runs the app with the "/db1" parameter. This only needs to be
// done one time.
if (Globals.AdjustExistingDbTotals)
{
NormalizeTotalCategory();
Globals.AdjustExistingDbTotals = false;
}
// calculate the amount changed for each item from the previous day
CalcChangedValues();
}
//--------------------------------------------------------------------------------------
// I found that sometimes, the sub-ategories don't add up to the actual toital reported
// on CodeProject. Even when I was checking it by hand by adding up what was currently
// on the web site, the total showed 20 points more than the sum of the categories. This
// is probably a caching problem on the part of CodeProject, so I had to write this
// method to account for it. Once confirmed, I decided that I would simply check the
// sum just before I add it to the database.
/// <summary>
/// Ensures the the Total category item is actully the sum of all of the sub-categories.
/// </summary>
public void NormalizeTotalCategory()
{
int adjusted = 0;
if (this.Count > 0)
{
DateTime startDate = this.First().TimeScraped;
DateTime endDate = this.Last().TimeScraped;
List<RepItem> list = null;
do
{
if (startDate.Date == endDate.Date)
{
int x = 0;
x++;
}
list = (from item in this
where item.TimeScraped.Date == startDate.Date && item.CPUserID == Globals.UserID && item.Category != RepCategory.Total
select item).ToList();
if (list.Count > 0)
{
RepItem totalItem = (from item in this
where item.TimeScraped.Date == startDate.Date && item.CPUserID == Globals.UserID && item.Category == RepCategory.Total
select item).FirstOrDefault();
if (totalItem != null)
{
int total = list.Sum((item) => item.Value);
if (total != totalItem.Value)
{
adjusted++;
}
totalItem.Value = total;
}
}
startDate = startDate.AddDays(1);
} while (startDate.Date <= endDate.Date);
}
// if we adjusted any totals, update the database with a mode of 1 to save just
// those adjusted totals
if (adjusted > 0)
{
UpdateDatabase(1);
}
}
//--------------------------------------------------------------------------------------
public void CalcChangedValues()
{
foreach(RepCategory cat in Enum.GetValues(typeof(RepCategory)))
{
if (cat != RepCategory.Unknown)
{
List<RepItem> list = (from item in this
where item.Category == cat
select item).ToList();
for (int i = 1; i < list.Count; i++)
{
if (i == list.Count - 1)
{
int x = 5;
x++;
}
list[i].ChangeValue = list[i].Value - list[i-1].Value;
}
}
}
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Fills in missing data by interpolating a value for a missing date.
/// </summary>
private void FillDataHoles()
{
bool needSorting = false;
foreach(RepCategory cat in Enum.GetValues(typeof(RepCategory)))
{
if (cat != RepCategory.Unknown)
{
List<RepItem> list = (from item in this
where item.Category == cat
select item).ToList();
if (list.Count > 1)
{
for (int i = 1; i < list.Count; i++)
{
if (i == 19)
{
}
RepItem currItem = list[i];
RepItem prevItem = list[i-1];
TimeSpan span = currItem.TimeScraped.Date - prevItem.TimeScraped.Date;
if (span.Days > 1)
{
needSorting = true;
int value = prevItem.Value + (int)(Math.Ceiling((double)(currItem.Value - prevItem.Value) / 2));
this.Add(new RepItem(){ Category = cat,
TimeScraped = currItem.TimeScraped.AddDays(-1),
CPUserID = currItem.CPUserID,
Value = value,
ChangeValue = 0 });
}
}
}
}
}
if (needSorting)
{
this.Sort(new GenericComparer<RepItem>(new string[]{"TimeScraped", "Category"}, GenericSortOrder.Ascending));
}
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Removes data that's more than a year old from the collection (but leaves the data
/// in the database if you want to retrieve it later with code that *you* add.
/// </summary>
private void RemoveExpiredData()
{
// get todays date just one time
DateTime now = DateTime.Now.Date;
// find all rcords that need to be deleted
var x = (from item in this
where TimeSpan.FromTicks(now.Ticks - item.TimeScraped.Ticks).Days > MAX_DAYS
select item);
// Delete the items. NOTE: this only deletes them from the list in memory, NOT
// the database itself.
foreach(RepItem item in x)
{
this.Remove(item);
}
}
#region "Getxxxx"
//--------------------------------------------------------------------------------------
/// <summary>
/// Retrieves all repitem objects that are between the specified dates (inclusive)
/// and that are of the appropriate category.
/// </summary>
/// <param name="dateStart"></param>
/// <param name="dateEnd"></param>
/// <param name="categories"></param>
/// <returns></returns>
private List<RepItem> GetItemsByDateAndCategory(DateTime dateStart, DateTime dateEnd, DisplayCategories categories)
{
// Retrieve the items that have a category that exists in the list of specified
// categories
List<RepItem> list = (from item in this
from cat in categories
where (item.Category == cat && item.TimeScraped.Between(dateStart, dateEnd, true))
select item).ToList();
return list;
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Calculates the highest value in all of the items in the collection that are of the
/// specified category
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public int GetHighestDailyValue(List<RepItem> list)
{
int value = list.Max((item) => item.Value);
return value;
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Calculates the highest value in all of the items in the collection between the
/// specified dates
/// </summary>
/// <returns></returns>
public int GetHighestDailyValue(DateTime dateStart, DateTime dateEnd, DisplayCategories categories)
{
int value = 0;
foreach (int i in Enum.GetValues(typeof(RepCategory)))
{
RepCategory category = Globals.IntToEnum(i, RepCategory.Unknown);
if (category == RepCategory.Unknown)
{
continue;
}
List<RepItem> list = GetItemsByDateAndCategory(dateStart, dateEnd, categories);
if (list != null)
{
value = GetHighestDailyValue(list);
}
}
return value;
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Calculates the highest value in all of the items in the collection
/// </summary>
/// <returns></returns>
public int GetHighestDailyValue(DisplayCategories categories)
{
DateTime dateStart = new DateTime(0).Date;
DateTime dateEnd = DateTime.Now.Date;
return GetHighestDailyValue(dateStart, dateEnd, categories);
}
//--------------------------------------------------------------------------------------
public int GetLowestDailyValue(List<RepItem> list)
{
int value = list.Min((item) => item.Value);
return value;
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Calculates the highest value in all of the items
/// </summary>
/// <returns></returns>
public int GetLowestDailyValue(DateTime dateStart, DateTime dateEnd, DisplayCategories categories)
{
int value = 0;
foreach (int i in Enum.GetValues(typeof(RepCategory)))
{
RepCategory category = Globals.IntToEnum(i, RepCategory.Unknown);
if (category == RepCategory.Unknown)
{
continue;
}
List<RepItem> list = GetItemsByDateAndCategory(dateStart, dateEnd, categories);
if (list != null)
{
value = GetLowestDailyValue(list);
}
}
return value;
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Calculates the highest value in all of the items
/// </summary>
/// <returns></returns>
public int GetLowestDailyValue(DisplayCategories categories)
{
DateTime dateStart = new DateTime(0).Date;
DateTime dateEnd = new DateTime(0).Date;
return GetLowestDailyValue(dateStart, dateEnd, categories);
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Calculates the lowest value in all of the items in the collection that are of the
/// specified category
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public int GetHighestDailyChangeValue(List<RepItem> list)
{
int value = list.Max((item) => item.ChangeValue);
return value;
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Calculates the highest value in all of the items in the collection between the
/// specified dates
/// </summary>
/// <returns></returns>
public int GetHighestDailyChangeValue(DateTime dateStart, DateTime dateEnd, DisplayCategories categories)
{
int value = 0;
foreach (int i in Enum.GetValues(typeof(RepCategory)))
{
RepCategory category = Globals.IntToEnum(i, RepCategory.Unknown);
if (category == RepCategory.Unknown)
{
continue;
}
List<RepItem> list = GetItemsByDateAndCategory(dateStart, dateEnd, categories);
if (list != null)
{
value = GetHighestDailyChangeValue(list);
}
}
return value;
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Calculates the highest value in all of the items in the collection
/// </summary>
/// <returns></returns>
public int GetHighestDailyChangeValue(DisplayCategories categories)
{
DateTime dateStart = new DateTime(0).Date;
DateTime dateEnd = DateTime.Now.Date;
return GetHighestDailyChangeValue(dateStart, dateEnd, categories);
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Gets the lowest changed value in all of the items
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public int GetLowestDailyChangeValue(List<RepItem> list)
{
int value = list.Min((item) => item.ChangeValue);
return value;
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Calculates the lowest changed value in all of the items
/// </summary>
/// <returns></returns>
public int GetLowestDailyChangeValue(DateTime dateStart, DateTime dateEnd, DisplayCategories categories)
{
int value = 0;
foreach (int i in Enum.GetValues(typeof(RepCategory)))
{
RepCategory category = Globals.IntToEnum(i, RepCategory.Unknown);
if (category == RepCategory.Unknown)
{
continue;
}
List<RepItem> list = GetItemsByDateAndCategory(dateStart, dateEnd, categories);
if (list != null)
{
value = GetLowestDailyChangeValue(list);
}
}
return value;
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Calculates the lowest changed value in all of the items
/// </summary>
/// <returns></returns>
public int GetLowestDailyChangeValue(DisplayCategories categories)
{
DateTime dateStart = new DateTime(0).Date;
DateTime dateEnd = new DateTime(0).Date;
return GetLowestDailyChangeValue(dateStart, dateEnd, categories);
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Get the most recent points value for the specified category
/// </summary>
/// <param name="category"></param>
/// <returns></returns>
public int GetLatestPointValue(RepCategory category)
{
int value = 0;
var repItem = (from item in this
where item.Category == category
select item).LastOrDefault();
if (repItem != null)
{
value = repItem.Value;
}
return value;
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Gets the daily average points earned for the specified category
/// </summary>
/// <param name="category"></param>
/// <returns></returns>
public int GetDailyAverage(RepCategory category)
{
int value = 0;
double total = 0;
DateTime now = DateTime.Now.Date;
DateTime periodStart = now.AddDays(-((int)RepPeriod.Year));
List<RepItem> list = (from item in this
where item.Category == category && item.TimeScraped.Between(periodStart, now, true)
select item).ToList();
foreach (RepItem item in list)
{
total += item.ChangeValue;
}
value = (int)(Math.Ceiling(total / list.Count));
return value;
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Determines the start date based on the specified reputation period.
/// </summary>
/// <param name="period"></param>
/// <returns></returns>
public DateTime GetPeriodStart(RepPeriod period)
{
DateTime periodStart = DateTime.Now.Date;
switch (period)
{
// for the entire week, starting on the first day of the week
case RepPeriod.Week :
periodStart = periodStart.WeekStartDate();
break;
// for the current month, beginning on the 1st
case RepPeriod.Month :
periodStart = periodStart.AddDays(-(periodStart.Day - 1));
break;
// for the current year, starting on 01 Jan
case RepPeriod.Year :
periodStart = periodStart.AddDays(-(periodStart.DayOfYear - 1));
break;
}
return periodStart;
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Gets the total points for the specified fiscal period.
/// </summary>
/// <param name="period"></param>
/// <param name="category"></param>
/// <returns></returns>
public int GetCurrentPeriodPoints(RepPeriod period, RepCategory category)
{
int value = 0;
DateTime now = DateTime.Now.Date;
DateTime periodStart = GetPeriodStart(period);
List<RepItem> list = (from item in this
where item.Category == category && item.TimeScraped.Between(periodStart, now, true)
select item).ToList();
foreach (RepItem item in list)
{
value += item.ChangeValue;
}
return value;
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Gets the total points for the specified fiscal period.
/// </summary>
/// <param name="period"></param>
/// <param name="category"></param>
/// <returns></returns>
public void GetCurrentPeriodPoints(RepPeriod period, RepCategory category, out int value, out int projected)
{
value = 0;
projected = 0;
DateTime now = DateTime.Now.Date;
DateTime periodStart = GetPeriodStart(period);
value = GetCurrentPeriodPoints(period, category);
int avg = GetDailyAverage(RepCategory.Total);
projected = value;
TimeSpan span = now - periodStart;
if (span.Days <= (int)period)
{
int daysLeft = (int)period - span.Days;
projected = value + (daysLeft * avg);
}
}
//--------------------------------------------------------------------------------------
/// <summary>
/// Gets the points for the last specified period (regardles of period starting
/// date). A week is 7 days, a month is 30, and a year is 365. No attempts are
/// made to account for leap years or months that don't have 30 days in them.
/// </summary>
/// <param name="period"></param>
/// <param name="category"></param>
/// <returns></returns>
public int GetLastPeriodPoints(RepPeriod period, RepCategory category)
{
int value = 0;
DateTime now = DateTime.Now.Date;
DateTime periodStart = DateTime.Now.Date.AddDays(-((int)period));
List<RepItem> list = (from item in this
where item.Category == category && item.TimeScraped.Between(periodStart, now, true)
select item).ToList();
foreach (RepItem item in list)
{
value += item.ChangeValue;
}
return value;
}
#endregion "Getxxxx"
/// <summary>
/// Use this method to artificially populate the reputation scores - useful if you
/// want to see how the program reacts with various amounts of data.
/// </summary>
/// <param name="startDate"></param>
/// <param name="user"></param>
public void TestFill(DateTime startDate, int user)
{
Random random = new Random(DateTime.Now.Millisecond);
random.Next(1, 1000);
Clear();
int[] points = new int[]{0,0,0,0,0,0,0,0,0};
DateTime now = DateTime.Now.Date;
TimeSpan span = now - startDate.Date;
int minRandom = 0;
int maxRandom = 1000;
int total = 0;
for (int i = 0; i < span.TotalDays; i++)
{
total = 0;
foreach (int j in Enum.GetValues(typeof(RepCategory)))
{
RepCategory category = Globals.IntToEnum(j, RepCategory.Unknown);
if (j > 0)
{
switch (category)
{
case RepCategory.Author :
minRandom = 10;
maxRandom = 100;
break;
case RepCategory.Authority:
minRandom = 200;
maxRandom = 600;
break;
case RepCategory.Debator:
minRandom = 100;
maxRandom = 500;
break;
case RepCategory.Editor:
minRandom = 0;
maxRandom = 25;
break;
case RepCategory.Enquirer:
minRandom = 0;
maxRandom = 1;
break;
case RepCategory.Organiser:
minRandom = 0;
maxRandom = 125;
break;
case RepCategory.Participant:
minRandom = 1;
maxRandom = 25;
break;
case RepCategory.Total :
minRandom = 0;
maxRandom = 0;
break;
}
if (category != RepCategory.Total)
{
points[j] += random.Next(minRandom, maxRandom);
total += points[j];
}
else
{
points[j] = total;
}
AddOrUpdate(category, startDate, user, points[j]);
}
}
startDate = startDate.AddDays(1);
}
CalcChangedValues();
int totalPoints = GetLatestPointValue(RepCategory.Total);
}
}
}
Submit an article or tip