private List<PlayversePurchaseAggregation> GetPurchaseTransactions(string gameId, DateTime startDate, DateTime endDate)
{
List<PlayversePurchaseAggregation> PurchaseTable = new List<PlayversePurchaseAggregation>();
if (endDate < startDate) {
//throw new Exception("end date must be before start date");
}
//more validation
string query = String.Format(@"SELECT CreatedOn AS 'RecordTimestamp', GameId, TransactionType, UserData, MAX(Credits) AS 'Cost', COUNT(*) AS 'TotalItems', SUM(Credits) AS 'TotalCredits'
FROM GameCredit_GameCreditTransactions
-- WHERE TransactionType = 1
Where CreatedOn BETWEEN '{0}' AND '{1}'
AND CreatedOn > '2015-04-28 15:00'
AND UserId NOT IN (SELECT UserId FROM ACL_UserRoleAssignment)
GROUP BY DATE(CreatedOn), UserData
ORDER BY DATE(CreatedOn) DESC, SUM(Credits) DESC;", startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss"));
DataTable response = DBManager.Instance.Query(Datastore.General, query);
if (response.Rows.Count > 0) {
foreach (DataRow row in response.Rows) {
PlayversePurchaseAggregation PurchaseRow = new PlayversePurchaseAggregation() {
Id = 0, //this will get assigned as an auto increment value by mysql so 0 is fine here
RecordTimestamp = DateTime.Parse(row["RecordTimestamp"].ToString()),
GameId = row["GameId"].ToString(),
TransactionType = Int32.Parse(row["TransactionType"].ToString()),
UserData = row["UserData"].ToString(),
TotalItems = Int32.Parse(row["TotalItems"].ToString()),
TotalCredits = Int32.Parse(row["TotalCredits"].ToString())
};
PurchaseTable.Add(PurchaseRow);
}
}
return PurchaseTable;
}
private List<PlayversePurchaseAggregation> GetLatestTransactions(string gameId)
{
DateTime lastUpdated;
List<PlayversePurchaseAggregation> PurchaseTable = new List<PlayversePurchaseAggregation>();
string latestQ = String.Format(@"SELECT MAX(RecordTimestamp) as LatestRecord FROM {0}", ECONOMY_TABLE);
try
{
DataTable response = DBManager.Instance.Query(Datastore.Monitoring, latestQ);
string latest = response.Rows[0]["LatestRecord"].ToString();
DateTime.TryParse(latest, out lastUpdated);
string query = String.Format(@"SELECT CreatedOn AS 'RecordTimestamp', GameId, TransactionType, UserData, MAX(Credits) AS 'Cost', COUNT(*) AS 'TotalItems', SUM(Credits) AS 'TotalCredits', Category
FROM {2}
Where CreatedOn BETWEEN '{0}' AND '{1}'
AND CreatedOn > '2015-04-28 15:00'
AND UserId NOT IN (SELECT UserId FROM ACL_UserRoleAssignment)
GROUP BY DATE(CreatedOn), UserData
ORDER BY DATE(CreatedOn) DESC, SUM(Credits) DESC;", lastUpdated.AddSeconds(1).ToString("yyyy-MM-dd HH:mm:ss"), DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"), TRANSACTION_TABLE);
DataTable data = DBManager.Instance.Query(Datastore.General, query);
if (data.Rows.Count > 0)
{
foreach (DataRow row in data.Rows)
{
PlayversePurchaseAggregation PurchaseRow = new PlayversePurchaseAggregation()
{
Id = 0, //this will get assigned as an auto increment value by mysql so 0 is fine here
RecordTimestamp = DateTime.Parse(row["RecordTimestamp"].ToString()),
GameId = row["GameId"].ToString(),
TransactionType = Int32.Parse(row["TransactionType"].ToString()),
UserData = row["UserData"].ToString(),
TotalItems = Int32.Parse(row["TotalItems"].ToString()),
TotalCredits = Int32.Parse(row["TotalCredits"].ToString()),
Category = row["Category"].ToString()
};
PurchaseTable.Add(PurchaseRow);
}
}
}
catch (Exception ex)
{
Logger.Instance.Exception(String.Format(@"[Transactions] {0}", ex.Message), ex.StackTrace);
}
return PurchaseTable;
}