public List<CoinFlowVector> GetFlowDataByCategory(string gameId, TransactionType type, DateTime startDate, DateTime endDate)
{
//GameMonitoringConfig game =
List<CoinFlowVector> ReturnList = new List<CoinFlowVector>();
string query = String.Format(
@"SELECT DATE(RecordTimestamp) as RecordTimestamp,
Type,
sum(TotalBought) as ItemCount,
sum(TotalCredits) as CreditAmount,
Category
FROM Economy_PurchaseBreakdownRaw
WHERE GameId = '{3}'
AND DATE(RecordTimestamp) BETWEEN '{0}' and '{1}'
AND Type = {2}
group by Category, Type,
DATE(RecordTimestamp)
order by DATE(RecordTimestamp);", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), (int)type, gameId);
DataTable SourceTable = DBManager.Instance.Query(Datastore.Monitoring, query);
string EMPTYCAT = "No Category";
if (SourceTable.Rows.Count > 0)
{
CoinFlowVector flowSource = new CoinFlowVector();
//category and group by
List<string> categories = SourceTable.AsEnumerable().Select(s =>
{
string catname = EMPTYCAT;
if (!String.IsNullOrEmpty(s.Field<string>("Category")))
{
catname = s.Field<string>("Category");
}
return catname;
}).Distinct().ToList();
foreach (string cat in categories)
{
DateTime firstRecordTimestamp = SourceTable.AsEnumerable()
.FirstOrDefault(y =>
{
string catname = y.Field<string>("Category");
if (string.IsNullOrEmpty(catname))
{
catname = EMPTYCAT;
}
return cat == catname;
})
.Field<DateTime>("RecordTimestamp");
ReturnList.Add(new CoinFlowVector()
{
Category = cat,
coinData = new List<double>(),
itemData = new List<double>(),
StartDate = firstRecordTimestamp.Date.ToUnixTimestamp() * 1000,
interval = 1 * 24 * 60 * 60 * 1000,
Type = type.ToString()
});
}
DateTime lastDay = startDate.Date;
foreach (DataRow source in SourceTable.Rows)
{
DateTime currentDay = DateTime.Parse(source["RecordTimestamp"].ToString());
int daysBetween = (currentDay - lastDay).Days;
if (daysBetween > 1)
{
while (--daysBetween != 0)
{
foreach (CoinFlowVector series in ReturnList)
{
series.coinData.Add(0);
series.itemData.Add(0);
}
}
}
lastDay = currentDay;
string cat = EMPTYCAT;
if (!string.IsNullOrEmpty(source["Category"].ToString()))
{
cat = source["Category"].ToString();
}
ReturnList.FirstOrDefault(x => x.Category == cat).coinData.Add(Double.Parse(source["CreditAmount"].ToString()));
ReturnList.FirstOrDefault(x => x.Category == cat).itemData.Add(Double.Parse(source["ItemCount"].ToString()));
}
}
return ReturnList;
}
public List<CoinFlowVector> GetFlowData(string gameId, TransactionType type, DateTime startDate, DateTime endDate)
{
//GameMonitoringConfig game =
List<CoinFlowVector> ReturnList = new List<CoinFlowVector>();
string query = String.Format(@"SELECT DATE(RecordTimestamp) as RecordTimestamp, Type, sum(TotalBought) as ItemCount, sum(TotalCredits) as CreditAmount FROM Economy_PurchaseBreakdownRaw WHERE GameId = '{3}' AND DATE(RecordTimestamp) BETWEEN '{0}' and '{1}' AND Type = {2} group by Type, DATE(RecordTimestamp) order by DATE(RecordTimestamp);", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), (int)type, gameId);
DataTable SourceTable = DBManager.Instance.Query(Datastore.Monitoring, query);
if (SourceTable.Rows.Count > 0) {
//category and group by
CoinFlowVector flowSource = new CoinFlowVector();
flowSource.coinData = new List<double>();
flowSource.itemData = new List<double>();
flowSource.StartDate = startDate.Date.ToUnixTimestamp() * 1000;
flowSource.interval = 1 * 24 * 60 * 60 * 1000; //1 day in ms
flowSource.Category = "Everything";
flowSource.Type = type.ToString();
DateTime lastDay = startDate.Date;
foreach (DataRow source in SourceTable.Rows)
{
DateTime currentDay = DateTime.Parse(source["RecordTimestamp"].ToString());
int daysBetween = (currentDay - lastDay).Days;
if (daysBetween > 1)
{
while(--daysBetween != 0)
{
flowSource.coinData.Add(0);
flowSource.itemData.Add(0);
}
}
flowSource.coinData.Add(Double.Parse(source["CreditAmount"].ToString()));
flowSource.itemData.Add(Double.Parse(source["ItemCount"].ToString()));
lastDay = currentDay;
}
ReturnList.Add(flowSource);
}
return ReturnList;
}
