Stacked Chart From Db to JSON

[JeniseM]

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

    }

 

[Akash Biswas]

Hi,

 

To render any chart by fetching values from database, you need to use any of the server-side wrappers provided by FusionCharts. You can check the supported wrappers in this link : https://www.fusioncharts.com/integrations?stack=back-end-integrations

Documentation link : https://www.fusioncharts.com/dev/getting-started/php/your-first-chart-using-php

 

Also find a sample multi-series chart rendered by fetching values form database using the JAVA wrapper : https://www.dropbox.com/s/5vd6hdmjiqx6yp8/DemoWrapperMultiSeriesCharts.zip?dl=0

 

Please note : Stacked charts and multi-series charts have same dataSource structure. So you can render the above chart as stacked chart by changing the chart "type" to "stackedcolumn2d".

 

Kindly refer to the sample and implement accordingly by referring to the provided documentation link.

 

Thanks,

Akash.