/// <summary>
/// Manage MonthlyStats object.
/// If the object is of type CustomObjectBase,
/// then either insert values into, delete values from, or update values in MonthlyStats.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="value">Object of type MonthlyStats.</param>
/// <returns>Object of type CustomDataAccessStatus<MonthlyStats>.</returns>
public CustomDataAccessStatus <MonthlyStats> MonthlyStatsManageAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
MonthlyStats value)
{
return(MonthlyStatsManageAuto(sqlConnection, sqlTransaction, "BHL", value));
}
/// <summary>
/// Select values from MonthlyStats by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="connectionKeyName">Connection key name located in config file.</param>
/// <param name="year"></param>
/// <param name="month"></param>
/// <param name="institutionName"></param>
/// <param name="statType"></param>
/// <returns>Object of type MonthlyStats.</returns>
public MonthlyStats MonthlyStatsSelectAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string connectionKeyName,
int year,
int month,
string institutionName,
string statType)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings(connectionKeyName), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("MonthlyStatsSelectAuto", connection, transaction,
CustomSqlHelper.CreateInputParameter("Year", SqlDbType.Int, null, false, year),
CustomSqlHelper.CreateInputParameter("Month", SqlDbType.Int, null, false, month),
CustomSqlHelper.CreateInputParameter("InstitutionName", SqlDbType.NVarChar, 255, false, institutionName),
CustomSqlHelper.CreateInputParameter("StatType", SqlDbType.NVarChar, 100, false, statType)))
{
using (CustomSqlHelper <MonthlyStats> helper = new CustomSqlHelper <MonthlyStats>())
{
CustomGenericList <MonthlyStats> list = helper.ExecuteReader(command);
if (list.Count > 0)
{
MonthlyStats o = list[0];
list = null;
return(o);
}
else
{
return(null);
}
}
}
}
public HttpResponseMessage PostMonthlyStat(MonthlyStats monthlyStat)
{
var createdMonthlyStat = MonthlyStatsServices.CreateMonthlyStat(monthlyStat);
// If stats already exists within database, return 409.
if (createdMonthlyStat == null)
{
var alreadyExistsResponse = Request.CreateResponse
(HttpStatusCode.Conflict, "(409) Monthly Stat already exists", Configuration.Formatters.JsonFormatter);
alreadyExistsResponse.Headers.Add("API-Version", apiVersion);
alreadyExistsResponse.Headers.Add("ERROR", "(409) Resource already exists");
return(alreadyExistsResponse);
}
// Daily Stat doesn't exist inside the database, proceed.
else
{
var response = Request.CreateResponse(HttpStatusCode.Created, monthlyStat, Configuration.Formatters.JsonFormatter);
response.Headers.Add("API-Version", apiVersion);
response.Headers.Add("Response-Type", "JSON");
if (Request.RequestUri.AbsoluteUri.EndsWith("/"))
{
response.Headers.Add("MonthlyStats-URL", Request.RequestUri.AbsoluteUri + monthlyStat.ID);
}
else
{
response.Headers.Add("MonthlyStats-URL", Request.RequestUri.AbsoluteUri + "/" + monthlyStat.ID);
}
return(response);
}
}
/// <summary>
/// Update values in MonthlyStats. Returns an object of type MonthlyStats.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="value">Object of type MonthlyStats.</param>
/// <returns>Object of type MonthlyStats.</returns>
public MonthlyStats MonthlyStatsUpdateAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
MonthlyStats value)
{
return(MonthlyStatsUpdateAuto(sqlConnection, sqlTransaction, "BHL", value));
}
public HttpResponseMessage UpdateMonthlyStats(int statID, MonthlyStats monthlyStat)
{
var updatedMonthlyStat = MonthlyStatsServices.UpdateMonthlyStat(statID, monthlyStat);
var response = Request.CreateResponse(HttpStatusCode.OK, updatedMonthlyStat, Configuration.Formatters.JsonFormatter);
response.Headers.Add("API-Version", apiVersion);
response.Headers.Add("Response-Type", "JSON");
response.Headers.Add("MonthlyStats-URL", Request.RequestUri.AbsoluteUri);
if (updatedMonthlyStat == null)
{
var notFoundResponse = Request.CreateResponse(HttpStatusCode.NotFound,
"(404) Monthly Stats Not Found",
Configuration.Formatters.JsonFormatter);
notFoundResponse.Headers.Add("API-Version", apiVersion);
return(notFoundResponse);
}
else
{
return(response);
}
}
/// <summary>
/// Manage MonthlyStats object.
/// If the object is of type CustomObjectBase,
/// then either insert values into, delete values from, or update values in MonthlyStats.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="connectionKeyName">Connection key name located in config file.</param>
/// <param name="value">Object of type MonthlyStats.</param>
/// <returns>Object of type CustomDataAccessStatus<MonthlyStats>.</returns>
public CustomDataAccessStatus <MonthlyStats> MonthlyStatsManageAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string connectionKeyName,
MonthlyStats value)
{
if (value.IsNew && !value.IsDeleted)
{
MonthlyStats returnValue = MonthlyStatsInsertAuto(sqlConnection, sqlTransaction, connectionKeyName,
value.Year,
value.Month,
value.InstitutionName,
value.StatType,
value.StatValue);
return(new CustomDataAccessStatus <MonthlyStats>(
CustomDataAccessContext.Insert,
true, returnValue));
}
else if (!value.IsNew && value.IsDeleted)
{
if (MonthlyStatsDeleteAuto(sqlConnection, sqlTransaction, connectionKeyName,
value.Year,
value.Month,
value.InstitutionName,
value.StatType))
{
return(new CustomDataAccessStatus <MonthlyStats>(
CustomDataAccessContext.Delete,
true, value));
}
else
{
return(new CustomDataAccessStatus <MonthlyStats>(
CustomDataAccessContext.Delete,
false, value));
}
}
else if (value.IsDirty && !value.IsDeleted)
{
MonthlyStats returnValue = MonthlyStatsUpdateAuto(sqlConnection, sqlTransaction, connectionKeyName,
value.Year,
value.Month,
value.InstitutionName,
value.StatType,
value.StatValue);
return(new CustomDataAccessStatus <MonthlyStats>(
CustomDataAccessContext.Update,
true, returnValue));
}
else
{
return(new CustomDataAccessStatus <MonthlyStats>(
CustomDataAccessContext.NA,
false, value));
}
}
/// <summary>
/// Binds the stats.
/// </summary>
private void BindStats()
{
Stats = MonthlyStats.Select(_siteId);
rprSmsSendCount.DataSource = rprEmailSendCount.DataSource = rprMonthes.DataSource = rprPageViews.DataSource = Stats;
rprPageViews.DataBind();
rprMonthes.DataBind();
rprEmailSendCount.DataBind();
rprSmsSendCount.DataBind();
}
/// <summary>
/// Update values in MonthlyStats. Returns an object of type MonthlyStats.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="connectionKeyName">Connection key name located in config file.</param>
/// <param name="value">Object of type MonthlyStats.</param>
/// <returns>Object of type MonthlyStats.</returns>
public MonthlyStats MonthlyStatsUpdateAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string connectionKeyName,
MonthlyStats value)
{
return(MonthlyStatsUpdateAuto(sqlConnection, sqlTransaction, connectionKeyName,
value.Year,
value.Month,
value.InstitutionName,
value.StatType,
value.StatValue));
}
private void CalculateTotal(GridViewRowEventArgs e, ref int total)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
MonthlyStats stats = (MonthlyStats)e.Row.DataItem;
total += stats.StatValue;
}
else if (e.Row.RowType == DataControlRowType.Footer)
{
e.Row.Cells[0].Text = "Total";
e.Row.Cells[e.Row.Cells.Count - 1].Text = total.ToString();
}
}
public static Sale CreateSale(Sale sale)
{
using (var context = new ServicesContext())
{
var currentDate = DateTime.Now.Date;
// Check if there's already a sales stat for this day.
var findDailyStat = context.dailyStats.FirstOrDefault
(b => b.salesDate == currentDate);
var findMonthlyStat = context.monthlyStats.FirstOrDefault
(b => b.salesMonth == DateTime.Now.Month && b.salesYear == DateTime.Now.Year);
// If there is an existing daily stat, sum one to the counter.
if (findDailyStat != null)
{
findDailyStat.productsSold++;
}
// If there isn't, create a new stat for this day.
else
{
DailyStats newStats = new DailyStats();
newStats.productsSold++;
context.dailyStats.Add(newStats);
}
// If the monthly stat already exists, sum one to the counter of that month.
if (findMonthlyStat != null)
{
findMonthlyStat.productsSold++;
}
// If there isn't an existing monthly stat, create one.
else
{
MonthlyStats newStats = new MonthlyStats();
newStats.productsSold++;
context.monthlyStats.Add(newStats);
}
// Sale object corrections
sale.saleDescription = sale.saleDescription.Trim();
context.Sales.Add(sale);
context.SaveChanges();
return(sale);
}
}
public IHttpActionResult GetMonthlyStat(int id)
{
MonthlyStats monthlyStats = MonthlyStatsServices.GetMonthlyStat(id);
HttpContext.Current.Response.AppendHeader("API-Version", apiVersion);
if (monthlyStats == null)
{
HttpContext.Current.Response.AppendHeader("ERROR", "(404) Resource Not Found");
return(NotFound());
}
else
{
HttpContext.Current.Response.AppendHeader("Response-Type", "JSON");
HttpContext.Current.Response.AppendHeader("Object-URL", Request.RequestUri.AbsoluteUri);
JsonSerializerSettings serializerSettings = new JsonSerializerSettings {
Formatting = Formatting.Indented
};
return(Json(monthlyStats, serializerSettings));
}
}
public static MonthlyStats UpdateMonthlyStat(int statID, MonthlyStats monthlyStat)
{
using (var context = new ServicesContext())
{
var selectedStat = context.monthlyStats.FirstOrDefault(b => b.ID == statID);
// Checking if the stat does exist within the database.
if (selectedStat != null)
{
selectedStat.salesMonth = monthlyStat.salesMonth;
selectedStat.salesYear = monthlyStat.salesYear;
context.SaveChanges();
return(selectedStat);
}
// If it does not, return null.
else
{
return(null);
}
}
}
public static MonthlyStats CreateMonthlyStat(MonthlyStats monthlyStat)
{
using (var context = new ServicesContext())
{
// Check if there's already a stat for this month.
var findMonthlyStat = context.monthlyStats.FirstOrDefault
(b => b.salesMonth == monthlyStat.salesMonth && b.salesYear == monthlyStat.salesYear);
// If there isn't, create one.
if (findMonthlyStat == null)
{
context.monthlyStats.Add(monthlyStat);
context.SaveChanges();
return(monthlyStat);
}
// If there is, return null and let the controller handle it.
else
{
return(null);
}
}
}
public static MonthlyStats RetrieveMonthlyStats(this ArchivedGame[] games, string username)
{
var stats = new MonthlyStats
{
WonByFlag = QueryArchivedGames(games, username, GameResult.Win, GameResult.Timeout).Count(),
WonByResign = QueryArchivedGames(games, username, GameResult.Win, GameResult.Resigned).Count(),
WonByCheckmate = QueryArchivedGames(games, username, GameResult.Win, GameResult.Checkmated).Count(),
WonByAbandoned = QueryArchivedGames(games, username, GameResult.Win, GameResult.Abandoned).Count(),
LostByFlag = QueryArchivedGames(games, username, GameResult.Timeout, GameResult.Win).Count(),
LostByResign = QueryArchivedGames(games, username, GameResult.Resigned, GameResult.Win).Count(),
LostByCheckmate = QueryArchivedGames(games, username, GameResult.Checkmated, GameResult.Win).Count(),
LostByAbandoned = QueryArchivedGames(games, username, GameResult.Abandoned, GameResult.Win).Count(),
DrawByRepetition = QueryArchivedGames(games, username, GameResult.Abandoned, GameResult.Win).Count(),
DrawByAgreed = QueryArchivedGames(games, username, GameResult.Abandoned, GameResult.Win).Count(),
DrawByStalemate = QueryArchivedGames(games, username, GameResult.Abandoned, GameResult.Win).Count(),
DrawByFiftyMove = QueryArchivedGames(games, username, GameResult.Abandoned, GameResult.Win).Count(),
DrawByTimeoutVsInsufficient = QueryArchivedGames(games, username, GameResult.Abandoned, GameResult.Win).Count(),
};
if (games.Any(g => g.MovesCount() < 15))
{
stats.ShortGamesWon = games.Where(g => g.MovesCount() < 15)
.Where(g => g.WhitePlayer.Username.Equals(username, StringComparison.OrdinalIgnoreCase) &&
g.WhitePlayer.GameResult == GameResult.Win)
.Count();
stats.ShortGamesLost = games.Where(g => g.MovesCount() < 15)
.Where(g => g.BlackPlayer.Username.Equals(username, StringComparison.OrdinalIgnoreCase) &&
g.BlackPlayer.GameResult == GameResult.Win)
.Count();
}
return(stats);
}
public JsonResult GetStats()
{
// make an array containing the names of the last three months
var currentMonth = CultureInfo.CurrentCulture.DateTimeFormat.GetAbbreviatedMonthName(DateTime.Now.Month);
var lastMonth = CultureInfo.CurrentCulture.DateTimeFormat.GetAbbreviatedMonthName(DateTime.Today.AddMonths(-1).Month);
var monthBeforeLast = CultureInfo.CurrentCulture.DateTimeFormat.GetAbbreviatedMonthName(DateTime.Today.AddMonths(-2).Month);
var monthArray = "['" + monthBeforeLast + "']" + "['" + lastMonth + "']" + "['" + currentMonth + "']";
// make an array with the count of logins in the last three months
List <MonthlyStats> monthlyStats = new List <MonthlyStats>();
// connect to database
var dbCon = DBConnection.Instance();
dbCon.DatabaseName = "getdrinxstg_db";
if (dbCon.IsConnect())
{
//query from the database and loop through the results so that they can be stored
string query = @"
SELECT SUM(x.Logins), SUM(x.Registrations), DATE_FORMAT(x.Date,'%M') as Date
FROM
(
SELECT COALESCE(a.LoginCount,0) as Logins, COALESCE(b.RegistrationCount,0) as Registrations, a.Date as Date
FROM
(
SELECT COUNT(*) as LoginCount, DATE(last_sign_in_at) as Date
FROM users u1
WHERE last_sign_in_at IS NOT NULL
AND last_sign_in_at BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE()
GROUP BY DATE(last_sign_in_at)
) a
CROSS JOIN
(
SELECT COUNT(*) as RegistrationCount, DATE(created_at) as Date
FROM users u2
WHERE created_at IS NOT NULL
AND created_at BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE()
GROUP BY DATE(created_at)
) b
on a.Date = b.Date
)x
GROUP BY DATE_FORMAT(x.Date,'%M')
ORDER BY x.Date ASC
";
var cmd = new MySqlCommand(query, dbCon.Connection);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
// create a new monthly stat record
MonthlyStats monthlyStat = new MonthlyStats();
//store the users data
monthlyStat.Logins = reader.GetString(0);
monthlyStat.Registrations = reader.GetString(1);
monthlyStat.Date = reader.GetString(2);
// add this user to the users list
monthlyStats.Add(monthlyStat);
}
reader.Close();
}
// make an array with the count of registrations in the last three months
return(Json(monthlyStats, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// Manage MonthlyStats object.
/// If the object is of type CustomObjectBase,
/// then either insert values into, delete values from, or update values in MonthlyStats.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="value">Object of type MonthlyStats.</param>
/// <returns>Object of type CustomDataAccessStatus<MonthlyStats>.</returns>
public CustomDataAccessStatus<MonthlyStats> MonthlyStatsManageAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
MonthlyStats value)
{
return MonthlyStatsManageAuto( sqlConnection, sqlTransaction, "BHL", value );
}
/// <summary>
/// Manage MonthlyStats object.
/// If the object is of type CustomObjectBase,
/// then either insert values into, delete values from, or update values in MonthlyStats.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="connectionKeyName">Connection key name located in config file.</param>
/// <param name="value">Object of type MonthlyStats.</param>
/// <returns>Object of type CustomDataAccessStatus<MonthlyStats>.</returns>
public CustomDataAccessStatus<MonthlyStats> MonthlyStatsManageAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string connectionKeyName,
MonthlyStats value)
{
if (value.IsNew && !value.IsDeleted)
{
MonthlyStats returnValue = MonthlyStatsInsertAuto(sqlConnection, sqlTransaction, connectionKeyName,
value.Year,
value.Month,
value.InstitutionName,
value.StatType,
value.StatValue);
return new CustomDataAccessStatus<MonthlyStats>(
CustomDataAccessContext.Insert,
true, returnValue);
}
else if (!value.IsNew && value.IsDeleted)
{
if (MonthlyStatsDeleteAuto(sqlConnection, sqlTransaction, connectionKeyName,
value.Year,
value.Month,
value.InstitutionName,
value.StatType))
{
return new CustomDataAccessStatus<MonthlyStats>(
CustomDataAccessContext.Delete,
true, value);
}
else
{
return new CustomDataAccessStatus<MonthlyStats>(
CustomDataAccessContext.Delete,
false, value);
}
}
else if (value.IsDirty && !value.IsDeleted)
{
MonthlyStats returnValue = MonthlyStatsUpdateAuto(sqlConnection, sqlTransaction, connectionKeyName,
value.Year,
value.Month,
value.InstitutionName,
value.StatType,
value.StatValue);
return new CustomDataAccessStatus<MonthlyStats>(
CustomDataAccessContext.Update,
true, returnValue);
}
else
{
return new CustomDataAccessStatus<MonthlyStats>(
CustomDataAccessContext.NA,
false, value);
}
}
/// <summary>
/// Update values in MonthlyStats. Returns an object of type MonthlyStats.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="value">Object of type MonthlyStats.</param>
/// <returns>Object of type MonthlyStats.</returns>
public MonthlyStats MonthlyStatsUpdateAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
MonthlyStats value)
{
return MonthlyStatsUpdateAuto(sqlConnection, sqlTransaction, "BHL", value );
}
/// <summary>
/// Update values in MonthlyStats. Returns an object of type MonthlyStats.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="connectionKeyName">Connection key name located in config file.</param>
/// <param name="value">Object of type MonthlyStats.</param>
/// <returns>Object of type MonthlyStats.</returns>
public MonthlyStats MonthlyStatsUpdateAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string connectionKeyName,
MonthlyStats value)
{
return MonthlyStatsUpdateAuto(sqlConnection, sqlTransaction, connectionKeyName,
value.Year,
value.Month,
value.InstitutionName,
value.StatType,
value.StatValue);
}
