Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
316 views
in Technique[技术] by (71.8m points)

c# - DotNet.Highcharts: Cost not plotted against the correct date

I'm creating a DotNet.Highcharts chart that is using data from two tables: Expenditures and Incomings. I am using an SQL statement to create a DataTable for each. The first (for Incomings) called Dt contains IncCost and IncDate. The second (for Expenditures) called Dt2 contains ExpCost and ExpDate.

I am able to plot IncCost against IncDate and ExpCost against ExpDate. The problem arises when I try to concatenate IncDate and ExpDate (final) as the cost for IncCost and ExpCost is plotting against the position of the date in (final), and not the actual date it is related to.

Here are the calls I use to create each DataTable:

SqlDataAdapter Adp = new SqlDataAdapter("select CONVERT(DATETIME, IncDate, 103) AS IncDate, SUM(IncCost) AS IncCost from Incomings GROUP BY CONVERT(DATETIME, IncDate, 103) ORDER BY CONVERT(DATETIME, IncDate, 103)", con);

SqlDataAdapter Adp2 = new SqlDataAdapter("select CONVERT(DATETIME, ExpDate, 103) AS ExpDate, SUM(ExpCost) AS ExpCost from Expenditures GROUP BY CONVERT(DATETIME, ExpDate, 103) ORDER BY CONVERT(DATETIME, ExpDate, 103)", con);

Here is the code for calling each of these from the datatable

var dateArr = Dt.AsEnumerable().Select(r => r.Field<DateTime>("IncDate")).ToArray();
var objectArr = Dt.AsEnumerable().Select(r => r.Field<object>("IncCost")).ToArray();

var dateArr2 = Dt2.AsEnumerable().Select(r => r.Field<DateTime>("ExpDate")).ToArray();
var objectArr2 = Dt2.AsEnumerable().Select(r => r.Field<object>("ExpCost")).ToArray();   

var res = dateArr.Concat(dateArr2).ToArray();
var final = res.Select(d => d.ToString(@"dd/MM/yyyy")).ToArray();

And here is the code I use to create my highchart:

Highcharts chart = new Highcharts("graph")
  .SetTitle(new Title { Text = "Incoming Stats" })
  .SetXAxis(new[] { new XAxis { Categories = final } })
  .SetYAxis(new YAxis { Title = new YAxisTitle { Text = "Amount Incoming" } })
  .SetSeries(new[]
    {
      new Series { Name = "inc", Data = new Data(objectArr)},
      new Series { Name = "exp", Data = new Data(objectArr2) }
    });

Plot 1

As you can see, the series are being created but they are being plotted against the number in the array, and not against the date it should be linked to.

I'm not sure how close or far I am to a solution but any help is appreciated. Here is my incomings datatable

Here is the data for my incomings datatable Dt

Here is my expenditure datatable

Here is the data for my expenditures datatable (Dt2)

Genuinely don't know how to go about this. any information is greatly appreciated

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Your Y values are completely disconnected from X values. In order to achieve what you want, you need to restructure your data as below:

        DateTime dt = new DateTime(2016, 2, 27);

        Highcharts chart = new Highcharts("graph")
           .SetTitle(new Title { Text = "Incoming Stats" })
           .SetXAxis(new XAxis { Type = AxisTypes.Datetime })
           .SetYAxis(new YAxis { Title = new YAxisTitle { Text = "Amount Incoming" } })
           .SetSeries(new[]
             {
              new Series { Name = "inc", Data = new Data(new object[,] { 
                  {dt , 23 }, 
                  {dt.AddDays(1) , 223 }, 
                  {dt.AddDays(2) , 51 }, 
                  {dt.AddDays(11) , 200 }, }) },
              new Series { Name = "exp", Data = new Data(new object[,] { 
                  {dt.AddDays(5) , 100 }, 
                  {dt.AddDays(6) , 23 }, 
                  {dt.AddDays(11) , 23 }, 
                  {dt.AddDays(19) , 35 }, 
                  {dt.AddDays(35) , 288 }, }) }
             });

enter image description here

EDIT: How to do it dynamically:

        object[,] data1 = new object[Dt.Rows.Count, 2];
        for (int i = 0; i < Dt.Rows.Count; i++)
        {
            data1[i, 0] = Dt.Rows[i]["IncDate"];
            data1[i, 1] = Dt.Rows[i]["IncCost"];
        }

        object[,] data2 = new object[Dt2.Rows.Count, 2];
        for (int i = 0; i < Dt2.Rows.Count; i++)
        {
            data2[i, 0] = Dt2.Rows[i]["ExpDate"];
            data2[i, 1] = Dt2.Rows[i]["ExpCost"];
        }

        Highcharts chart = new Highcharts("graph")
           .SetTitle(new Title { Text = "Incoming Stats" })
           .SetXAxis(new XAxis { Type = AxisTypes.Datetime })
           .SetYAxis(new YAxis { Title = new YAxisTitle { Text = "Amount Incoming" } })
           .SetSeries(new[]
             {
                    new Series { Name = "inc", Data = new Data(data1) },
                    new Series { Name = "exp", Data = new Data(data2) }
             });

enter image description here

Data retrieved from database:

enter image description here

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...