//Huang Yuzhe
public ActionResult Dashboard(string category = "", string department = "")
{
List <DateTime> times = new List <DateTime>();
List <int> amounts_groupbyCategory = new List <int>();
List <int> amounts_groupbyDepartment = new List <int>();
DateTime today = DateTime.Now;
for (int i = 90; i >= 1; i -= 7)
{
DateTime temp = today.AddDays(-i);
times.Add(temp);
}
using (SqlConnection conn = new SqlConnection(("Server=.; Database=SSIS; Integrated Security=true")))
{
conn.Open();
foreach (DateTime time in times)
{
DateTime time_nextweek = time.AddDays(8);
string sql_groupbyCategory;
string sql_groupbyDepartment;
if (category == "" || category == "Total")
{
sql_groupbyCategory = @"SELECT isNull(SUM(Unit), 0) AS unit
FROM StockRecords sr
WHERE sr.Date BETWEEN '" + time + "' AND '" + time_nextweek + "'" +
" AND sr.IdOperation = '2'";
}
else
{
sql_groupbyCategory = @"SELECT isNull(SUM(Unit), 0) AS unit
FROM StockRecords sr
INNER JOIN Items i ON sr.IdItem = i.IdItem
INNER JOIN Categories c ON i.IdCategory = c.IdCategory
WHERE sr.Date BETWEEN '" + time + "' AND '" + time_nextweek + "'" +
" AND sr.IdOperation = '2'" +
" AND c.Label = '" + category + "'";
}
if (department == "" || department == "Total")
{
sql_groupbyDepartment = @"SELECT isNull(SUM(Unit), 0) AS unit
FROM StockRecords sr
WHERE sr.Date BETWEEN '" + time + "' AND '" + time_nextweek + "'" +
" AND sr.IdOperation = '1'";
}
else
{
sql_groupbyDepartment = @"SELECT isNull(SUM(Unit), 0) AS unit
FROM StockRecords sr
WHERE sr.Date BETWEEN '" + time + "' AND '" + time_nextweek + "'" +
" AND sr.IdOperation = '1'" +
" AND sr.IdDepartment = '" + department + "'";
}
SqlCommand cmd1 = new SqlCommand(sql_groupbyCategory, conn);
SqlCommand cmd2 = new SqlCommand(sql_groupbyDepartment, conn);
SqlDataReader reader1 = cmd1.ExecuteReader();
if (reader1.HasRows)
{
while (reader1.Read())
{
if (reader1["unit"] != null)
{
int t = (int)reader1["unit"];
amounts_groupbyCategory.Add(t);
}
else
{
amounts_groupbyCategory.Add(0);
}
}
}
else
{
amounts_groupbyCategory.Add(0);
}
reader1.Close();
SqlDataReader reader2 = cmd2.ExecuteReader();
if (reader2.HasRows)
{
while (reader2.Read())
{
if (reader2["unit"] != null)
{
int t = (int)reader2["unit"];
amounts_groupbyDepartment.Add(t);
}
else
{
amounts_groupbyDepartment.Add(0);
}
}
}
else
{
amounts_groupbyDepartment.Add(0);
}
reader2.Close();
}
}
List <DataPoint> dataPoints1 = new List <DataPoint>();
List <DataPoint> dataPoints2 = new List <DataPoint>();
foreach (DateTime time in times)
{
DataPoint d1 = new DataPoint();
DataPoint d2 = new DataPoint();
string tt = time.ToString("dd-MM-yyyy");
d1.x = tt;
d2.x = tt;
dataPoints1.Add(d1);
dataPoints2.Add(d2);
}
int j = 0;
foreach (int amount in amounts_groupbyCategory)
{
dataPoints1[j].y = amount;
j++;
}
int k = 0;
foreach (int amount in amounts_groupbyDepartment)
{
dataPoints2[k].y = amount;
k++;
}
ViewBag.categories = _categoryDAO.FindAllCategories();
ViewBag.departments = _departmentDAO.FindAllDepartments();
ViewBag.categorySelected = category == ""?"Total" : category;
ViewBag.departmentSelected = department == ""?"Total" : department;
JsonSerializerSettings _jsonSetting = new JsonSerializerSettings()
{
NullValueHandling = NullValueHandling.Ignore
};
ViewBag.DataPoints2 = JsonConvert.SerializeObject(dataPoints2, _jsonSetting);
ViewBag.DataPoints1 = JsonConvert.SerializeObject(dataPoints1, _jsonSetting);
return(View());
}
