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; }