public ContentResult RevByAdvertiser()
{
int[] timearray = new int[96];
int[,] finaltimearray = new int[96, 59];
string[] daySlice = new string[59];
int counter = daySlice.Length;
for (int i = 0; i < daySlice.Length; i++)
{
var day = DateTime.UtcNow.AddDays(-(counter - i));
daySlice[i] = Convert.ToDateTime(day).ToString("yyyy-MM-dd");
}
DateTime startDate = DateTime.UtcNow.Date.AddDays(-61);
DateTime endDate = DateTime.UtcNow.Date.AddDays(0);
string Query = @"SELECT
date as 'date',
revenuestream as revenuestream,
SUM(revenue) AS revenue
FROM [Reports].[dbo].[DailyRevenue] WITH (NOLOCK)
WHERE 1=1 AND date >= dateAdd(DAY, -60, GETDATE()) AND date <= GETDATE() AND category = 'push'
group by
revenuestream,
date
order by revenuestream, date" ;
// Since table format is (date, revenuestream, revenue), we'll model our Json Array likewise
// Kinda like a dictionary
JArray Date = new JArray();
JArray Data = new JArray();
JArray SingleDay = new JArray();
JArray Revenue = new JArray();
JArray DayTime = new JArray();
JObject Record = new JObject();
JObject Day = new JObject();
using (var sqlConn = new SqlConnection(DataSource))
{
sqlConn.Open();
SqlCommand command = new SqlCommand(string.Format(Query, startDate.ToString(), endDate.ToShortDateString()), sqlConn);
string revenuestream = "tt";
using (var reader = command.ExecuteReader())
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
string temp = Convert.ToDateTime(reader["date"]).ToString("yyyy-MM-dd");
if (revenuestream != "tt" && revenuestream != Convert.ToString(reader["revenuestream"]))
{
while (DayTime.Count < daySlice.Length)
{
DayTime.Add(daySlice[DayTime.Count]);
Revenue.Add(0);
}
Record = new JObject();
Record.Add("Advertiser", revenuestream);
Record.Add("Revenue", Revenue);
Record.Add("Date", DayTime);
Data.Add(Record);
DayTime = new JArray();
Revenue = new JArray();
}
revenuestream = Convert.ToString(reader["revenuestream"]);
if (daySlice.Length != DayTime.Count)
{
while (daySlice[DayTime.Count] != temp)
{
DayTime.Add(daySlice[DayTime.Count]);
Revenue.Add(0);
}
}
DayTime.Add(Convert.ToDateTime(reader["date"]).ToString("yyyy-MM-dd"));
Revenue.Add(Convert.ToInt32(reader["revenue"] is DBNull ? 0 : reader["revenue"]));
}
}
}
Record = new JObject();
while (DayTime.Count < daySlice.Length)
{
DayTime.Add(daySlice[DayTime.Count]);
Revenue.Add(0);
}
Record.Add("Advertiser", revenuestream);
Record.Add("Revenue", Revenue);
Record.Add("Date", DayTime);
Data.Add(Record);
sqlConn.Close();
}
return(new ContentResult
{
Content = Data.ToString(),
ContentType = "application/json"
});
}
