DragonZelda DragonZelda - 9 days ago 8
C# Question

Transfer Data to Time Range

I have a database something like this:

DateAndTime Column1 Column2 Column3 ... Column85
============================================================
2016-01-01 00:00:10 1 0 0 0
2016-01-01 00:00:20 1 0 0 1
2016-01-01 00:00:30 1 1 0 1
2016-01-01 00:00:40 1 1 0 0
2016-01-01 00:00:50 0 1 0 0
2016-01-01 00:01:00 1 0 0 1
2016-01-01 00:01:10 1 0 0 1
2016-01-01 00:01:20 1 0 0 0
2016-01-01 00:01:30 0 0 0 0
...
2016-01-11 00:01:30 0 0 0 0


I am using LinQ to retrieve the data.

var Data = (from row in db._Data where row.Column86 == X select row).ToList();


Is it possible to generate result output something like this or similar:

Column1
From 2016-01-01 00:00:10 To 2016-01-01 00:00:50
From 2016-01-01 00:01:00 To 2016-01-01 00:01:30
Column2
From 2016-01-01 00:00:30 To 2016-01-01 00:01:00
...
Column85
From 2016-01-01 00:00:20 To 2016-01-01 00:00:40
From 2016-01-01 00:01:00 To 2016-01-01 00:01:20


The following is the code I try:

bool alarmhistory = false; // Check if there is a true for whole table
bool from = false; // If previous 1 is activate
bool gotAlarm = false; // Check if there is a true for whole column

string RawHtml = "";
string FinalHtml = "";

RawHtml = "<tr><td style='text - align:center; '><b>";
RawHtml += "Column1";
RawHtml += "</b></td></tr>";
RawHtml += "<tr><td>";

for (int x = 0; x < Data.Count ; x++)
{
if (x == 0 && Data.Count != 1) //First Row
{
if (Data[x].Column1 == 1)
{
gotAlarm = true;
alarmhistory = true;
from = true; // Start the time
RawHtml += "From " + Data[x].DateAndTime.ToString("yyyy-MMM-dd hh:mm:ss tt");
}
}
else if (x == 0 && Data.Count == 1) //First Row but only have 1 record
{
if (Data[x].Column1 == 1)
{
gotAlarm = true;
alarmhistory = true;
from = false; // Only 1 record, meaningless
RawHtml += "From " + Data[x].DateAndTime.ToString("yyyy-MMM-dd hh:mm:ss tt");
RawHtml += " To " + Data[x].DateAndTime.ToString("yyyy-MMM-dd hh:mm:ss tt");
}
}
else if (x == (Data.Count - 1)) //Last Row
{
if (Data[x].Column1 == 1)
{
gotAlarm = true;
alarmhistory = true;
if (from == true)
{
RawHtml += " To " + Data[x].DateAndTime.ToString("yyyy-MMM-dd hh:mm:ss tt");
}
if (from == false)
{
RawHtml += "From " + Data[x].DateAndTime.ToString("yyyy-MMM-dd hh:mm:ss tt");
RawHtml += " To " + Data[x].DateAndTime.ToString("yyyy-MMM-dd hh:mm:ss tt");
}
}
if (Data[x].Column1 == 0)
{
if (from == true)
{
RawHtml += " To " + Data[x].DateAndTime.ToString("yyyy-MMM-dd hh:mm:ss tt");
}
}
}
else //Others condition
{
if (Data[x].Column1 == 1)
{
gotAlarm = true;
alarmhistory = true;
if (from == false) // If previous is 0
{
RawHtml += "From " + Data[x].DateAndTime.ToString("yyyy-MMM-dd hh:mm:ss tt");
from = true; // Start the time
}
}
if (Data[x].Column1 == 0)
{
if (from == true) // If previous is 1
{
RawHtml += " To " + Data[x].DateAndTime.ToString("yyyy-MMM-dd hh:mm:ss tt");
RawHtml += "<br />";
from = false; //End the time
}
}
}
}

RawHtml += "</td></tr>";

if (gotAlarm == true)
{
FinalHtml += RawHtml;
}

RawHtml = "";
gotAlarm = false;
from = false;


After that I will repeat and change the Column1 to Column2. But system will stack overflow after Column16 I think.

Answer

Try this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication29
{
    class Program
    {
        static void Main(string[] args)
        {

            DataTable dt = new DataTable();
            dt.Columns.Add("DateAndTime", typeof(DateTime));
            dt.Columns.Add("Column1", typeof(int));
            dt.Columns.Add("Column2", typeof(int));
            dt.Columns.Add("Column3", typeof(int));
            dt.Columns.Add("Column4", typeof(int));

            dt.Rows.Add(new object[] { DateTime.Parse("2016-01-01 00:00:10"), 1, 0, 0, 0 });
            dt.Rows.Add(new object[] { DateTime.Parse("2016-01-01 00:00:20"), 1, 0, 0, 1 });
            dt.Rows.Add(new object[] { DateTime.Parse("2016-01-01 00:00:30"), 1, 1, 0, 1 });
            dt.Rows.Add(new object[] { DateTime.Parse("2016-01-01 00:00:40"), 1, 1, 0, 0 });
            dt.Rows.Add(new object[] { DateTime.Parse("2016-01-01 00:00:50"), 0, 1, 0, 0 });
            dt.Rows.Add(new object[] { DateTime.Parse("2016-01-01 00:01:00"), 1, 0, 0, 1 });
            dt.Rows.Add(new object[] { DateTime.Parse("2016-01-01 00:01:10"), 1, 0, 0, 1 });
            dt.Rows.Add(new object[] { DateTime.Parse("2016-01-01 00:01:20"), 1, 0, 0, 0 });
            dt.Rows.Add(new object[] { DateTime.Parse("2016-01-01 00:01:30"), 0, 0, 0, 0 });
            dt.Rows.Add(new object[] { DateTime.Parse("2016-01-01 00:01:30"), 0, 0, 0, 0 });

            for (int colIndex = 1; colIndex <= dt.Columns.Count - 1; colIndex++)
            {
                List<List<DateTime>> results = GetRanges("Column" + colIndex.ToString(), dt);
                if (results != null)
                {
                    Console.WriteLine("Column" + colIndex.ToString());
                    foreach (List<DateTime> result in results)
                    {
                        Console.WriteLine("From {0} To {1}", result[0].ToString(), result[1].ToString());
                    }
                }

            }
            Console.ReadLine();
        }
        static List<List<DateTime>> GetRanges(string colName, DataTable dt)
        {
            List<List<DateTime>> results = new List<List<DateTime>>();
            List<DateTime> newResult = null;
            Boolean foundStart = false;

            foreach (DataRow row in dt.AsEnumerable())
            {
                int state = (int)row[colName];
                if (!foundStart)
                {
                    if (state == 1)
                    {
                        //add start time
                        newResult = new List<DateTime>();
                        results.Add(newResult);
                        //add date as both start and end incase there is only one item
                        newResult.Add((DateTime)row["DateAndTime"]);
                        newResult.Add((DateTime)row["DateAndTime"]);
                        foundStart = true;
                    }
                }
                else
                {
                    if (state == 0)
                    {
                        foundStart = false;
                    }
                    else
                    {
                        newResult[1] = (DateTime)row["DateAndTime"];
                    }


                }
            }
            if (results.Count == 0)
                return null;
            else
                return results;
        }
    }
}