I'm trying to create a stacked chart with from a database into a JSON Append object and am having some trouble. I have a set a weeks to display on the X axis. The Y axis will display a number of Hours per status. This is my first chart with a JSON object within MVC and instead of getting an error message, the screen just shows "Charts...." text. I believe my issue is mainly within my foreach loop through the status. I also need to implement the passing of the week along with the status so that I am getting the "Hours" for that status and week.
Please let me know if you have any questions. I have all my code in page for now.
Thank You!!
---------------------------------------------------------------
public class WOWeekData
{
//public int lworkweekcode { get; set; }
public DateTime WkDate { get; set; }
}
public class WOHrsData
{
//public int WOStatusID { get; set; }
//public int lworkweekcode { get; set; }
public string WOStatus { get; set; }
public int WkHrs { get; set; }
public DateTime WkDate { get; set; }
}
public class HomeController : Controller
{
List<WOWeekData> wdata_list = new List<WOWeekData>();
List<WOHrsData> hdata_list = new List<WOHrsData>();
public string createChart()
{
string dat = getData();
Chart cc = new Chart("stackedcolumn3d", "mychart", "750", "550", "json", dat);
return cc.Render();
}
public string getData()
{
List<DateTime> WOWkDate2_list = new List<DateTime>();
List<string> WOStatus_list = new List<string>();
List<int> WOWkHrs_list = new List<int>();
List<DateTime> WOWkDate_list = new List<DateTime>();
string db= ConfigurationManager.ConnectionStrings["string"].ConnectionString;
SqlConnection con = new SqlConnection();
con.ConnectionString = db;
con.Open();
SqlCommand com = new SqlCommand("Select distinct(WkDate) from v_SampleData2", con);
SqlDataReader sda = com.ExecuteReader();
while (sda.Read())
{
WOWeekData wod = new WOWeekData();
wod.WkDate = DateTime.Parse(sda[0].ToString());
//wod.W = Int32.Parse(sda[1].ToString());
//wod.P = Int32.Parse(sda[2].ToString());
//wod.H = Int32.Parse(sda[3].ToString());
wdata_list.Add(wod);
}
foreach (WOWeekData w in wdata_list)
{
WOWkDate_list.Add(w.WkDate);
}
sda.Close();
SqlCommand com2 = new SqlCommand("Select * from v_SampleData2", con);
SqlDataReader sda2 = com2.ExecuteReader();
while (sda2.Read())
{
WOHrsData hrs = new WOHrsData();
hrs.WOStatus = sda2[1].ToString();
hrs.WkHrs = Int32.Parse(sda2[2].ToString());
hrs.WkDate = DateTime.Parse(sda2[0].ToString());
hdata_list.Add(hrs);
}
foreach (WOHrsData h in hdata_list)
{
//WOWk.Add(h.WkDate);
WOStatus_list.Add(h.WOStatus);
WOWkHrs_list.Add(h.WkHrs);
WOWkDate2_list.Add(h.WkDate);
}
sda2.Close();
con.Close();
con.Dispose();
//building JSON String
StringBuilder JSON = new StringBuilder();
JSON.Append("{" +
"'chart': {" +
"'caption': 'One Chart Per Department'," +
// "'exportEnabled':'1'," +
"'xAxisname': 'Weeks'," +
"'yAxisName': 'Hours'," +
" },"
);
//appenfing into StringBuilder objectiterating through collections
JSON.Append("'categories': [{" +
"'category': [ ");
foreach (var wk in WOWkDate_list.Distinct())
{
//for last element escaping comma
if (wk == WOWkDate_list.Distinct().Last())
{
JSON.Append("{ 'label': '" + wk + "' }");
break;
}
JSON.Append("{ 'label': '" + wk + "' },");
}
JSON.Append("]" +
"}]," +
"'dataset': [");
foreach (var wostat in WOStatus_list.Distinct())
{
List<int> wohrsvalue = getWOHrsData(wostat);
JSON.Append("{" +
"'seriesname':" + "'" + wostat + "'," + "'data': [");
foreach (var whrs in wohrsvalue)
{
if (whrs == wohrsvalue.Last())
{
JSON.Append("{" +
"'value':" + "'" + whrs + "'}");
break;
}
JSON.Append("{" +
"'value':" + "'" + whrs + "'},");
}
if (wostat == WOStatus_list.Distinct().Last())
{
JSON.Append("]" +
" }");
break;
}
JSON.Append("]" +
" },");
}
//replacing all ' into "
string str = JSON.ToString().Replace('\'', '\"');
return str;
}
public List<int> getWOHrsData(string wostat)
{
List<int> hrsvalue = new List<int>();
var seriesLink = from wo in hdata_list
where wo.WOStatus == wostat
select new { linkdata = wo.WkHrs };
foreach (var obj in seriesLink)
{
hrsvalue.Add(obj.linkdata);
}
return hrsvalue;
}
public ActionResult Index()
{
ViewBag.mydata = createChart();
return View();
}
public ActionResult About()
{
ViewBag.Message = "Your application description page.";
return View();
}
public ActionResult Contact()
{
ViewBag.Message = "Your contact page.";
return View();
}
}