public static DateRange[] Coalesce(DateRange RangeA, DateRange RangeB)
{
bool CondA = RangeA.StartTime > RangeB.EndTime;
bool CondB = RangeA.EndTime < RangeB.StartTime;
bool overlaps = !(CondA || CondB);
// bool overlaps = (RangeA.StartTime > RangeB.EndTime) && (RangeA.EndTime < RangeB.StartTime);
//overlap exists if neither A or B is true.
if (overlaps)
{
//overlap exists.
//create a new starttime the lower of the given two...
DateTime newstart, newEnd;
if (RangeA.StartTime < RangeB.StartTime) newstart = RangeA.StartTime; else newstart = RangeB.StartTime;
if (RangeA.EndTime > RangeB.EndTime) newEnd = RangeA.EndTime; else newEnd = RangeB.EndTime;
return new DateRange[] { new DateRange(newstart, newEnd) };
}
else
{
//there is no overlap, return a daterange array with both the same elements.
return new DateRange[] { (DateRange)RangeA.Clone(), (DateRange)RangeB.Clone() };
}
}
/// <summary>
/// retrieves the elapsed Times for Users and Orders
/// </summary>
/// <param name="dr"></param>
/// <param name="orderTimes">resulting dictionary of order times, indexed by RO#. Values</param>
/// <param name="userTimes"></param>
/// <remarks></remarks>
public void GetElapsedData(DateRange dr, out Dictionary<String, TimeSpan> orderTimes,out Dictionary<String,TimeSpan> userTimes )
{
Dictionary<String, Stack<DateTime>> StartTimeOrderStack = new Dictionary<string, Stack<DateTime>>();
Dictionary<String, Stack<DateTime>> StartTimeUserStack = new Dictionary<string, Stack<DateTime>>();
Dictionary<String, TimeSpan> orderresult = new Dictionary<string, TimeSpan>();
Dictionary<String, TimeSpan> userresult = new Dictionary<string, TimeSpan>();
TimeSpan PrevOrderChunk = new TimeSpan();
TimeSpan PrevUserChunk = new TimeSpan();
List<ClockDataEntry> clockdata = new List<ClockDataEntry>();
String clockedorder = String.Format("SELECT * FROM `CLOCKED` ORDER BY `STAMP` WHERE `STAMP`<\"{0}\" AND `STAMP`>\"{1}\"",dr.StartTime.getSQLfmt(),dr.EndTime.getSQLfmt());
DbConnection dbcon;
DbCommand dcmd = GetCommand(out dbcon);
lock (this)
{
using(DbDataReader dreader = dcmd.ExecuteReader())
{
while (dreader.Read())
{
int get_ID = dreader.GetInt32(dreader.GetOrdinal("ID"));
String get_PIN = dreader.GetString(dreader.GetOrdinal("UserPIN"));
String get_Order = dreader.GetString(dreader.GetOrdinal("OrderID"));
DateTime get_STAMP = dreader.GetDateTime(dreader.GetOrdinal("STAMP"));
String get_Event = dreader.GetString(dreader.GetOrdinal("EventType"));
if (!orderresult.ContainsKey(get_Order)) orderresult.Add(get_Order, new TimeSpan());
if (!userresult.ContainsKey(get_PIN)) userresult.Add(get_PIN, new TimeSpan());
if (!StartTimeOrderStack.ContainsKey(get_Order)) StartTimeOrderStack.Add(get_Order, new Stack<DateTime>());
if (!StartTimeUserStack.ContainsKey(get_PIN)) StartTimeUserStack.Add(get_PIN, new Stack<DateTime>());
clockdata.Add(new ClockDataEntry(get_ID, get_PIN, get_Order, get_STAMP, get_Event));
}
}
}
foreach(var iterate in (from m in clockdata orderby m.STAMP ascending select m))
{
if (iterate.EventType == "IN")
{
StartTimeOrderStack[iterate.OrderID].Push(iterate.STAMP);
StartTimeUserStack[iterate.PIN].Push(iterate.STAMP);
}
else if (iterate.EventType == "OUT")
{
//if the stack is empty, this has no corresponding IN.
//this can happen if there was a clock-in before the daterange and we find the out entry after.
if (StartTimeOrderStack[iterate.OrderID].Count == 0)
{
//add the interval from the start of the range to this value.
//subtract prevorder chunk so we only end up with the "largest" value, if there are multiple time outs.
orderresult[iterate.OrderID] += (iterate.STAMP - dr.StartTime)-PrevOrderChunk;
PrevOrderChunk = (iterate.STAMP - dr.StartTime);
}
else
{
DateTime dopop = StartTimeOrderStack[iterate.OrderID].Pop();
if (StartTimeOrderStack[iterate.OrderID].Count == 0)
{
//if it is empty add the interval to the timespan.
orderresult[iterate.OrderID] += (iterate.STAMP - dopop);
}
}
//if the user stack is empty, then there is no corresponding user time in event for this item.
if (StartTimeUserStack[iterate.PIN].Count == 0)
{
//add the interval from the start of the range to this value.
userresult[iterate.PIN] += (iterate.STAMP - dr.StartTime)-PrevUserChunk;
PrevUserChunk = (iterate.STAMP - dr.StartTime);
}
else
{
DateTime dopopu = StartTimeUserStack[iterate.PIN].Pop();
if (StartTimeUserStack[iterate.PIN].Count == 0)
{
userresult[iterate.PIN] += (iterate.STAMP - dopopu);
}
}
}
}
//lastly, we want to get the earliest Time in the StartTimeUserStack and StartTimeOrderStack, for each Order and User,
//and add it to the respective orderresult and userresult entries. (to account for cases where we have a Time IN but no time out because of the daterange)
//iterate through the KeyValuePairs...
if (StartTimeOrderStack.Count > 0)
{
foreach (var kvp in StartTimeOrderStack)
{
//find the earliest Time in this stack.
DateTime currearliest = DateTime.MaxValue;
foreach (var iterateearly in kvp.Value)
{
if (iterateearly < currearliest)
currearliest = iterateearly;
}
orderresult[kvp.Key] += (dr.EndTime-currearliest);
}
}
//repeat for the UserStack...
if (StartTimeUserStack.Count > 0)
{
foreach (var kvp in StartTimeUserStack)
{
//find earliest time...
DateTime earlyusertime = DateTime.MaxValue;
foreach (var iterateearly in kvp.Value)
{
if (iterateearly < earlyusertime)
earlyusertime = iterateearly;
}
userresult[kvp.Key] += (dr.EndTime - earlyusertime);
}
}
//and return the results.
orderTimes = orderresult;
userTimes = userresult;
}
public List<ClockDataEntry> GetClockedInRange(DateRange userange)
{
String sqlquery = "SELECT * FROM CLOCKED WHERE `STAMP`>\"{0}\" AND `STAMP`<\"{1}\"";
string sqluse = String.Format(sqlquery, userange.StartTime.getSQLfmt(), userange.EndTime.getSQLfmt());
DbConnection dcon;
DbCommand dcmd = GetCommand(out dcon);
List<ClockDataEntry> result = new List<ClockDataEntry>();
dcmd.CommandText = sqluse;
lock (this)
{
using (DbDataReader dreader = dcmd.ExecuteReader())
{
while (dreader.Read())
{
int _ID = dreader.GetInt32(dreader.GetOrdinal("ID"));
String _PIN = dreader.GetString(dreader.GetOrdinal("UserPIN"));
String _OrderID = dreader.GetString(dreader.GetOrdinal("OrderID"));
DateTime _Stamp = dreader.GetDateTime(dreader.GetOrdinal("STAMP"));
String _EType = dreader.GetString(dreader.GetOrdinal("EventType"));
result.Add(new ClockDataEntry(_ID, _PIN, _OrderID, _Stamp, _EType));
}
}
}
return result;
}
public DateRange[] GetUserWorkOrderRanges(String UserID, String OrderID)
{
String RelevantRecordsSQL = qAdapter["USERTOTALTIMEQUERY"];
//select all records in the CLOCKED table (which is used to store all clock-in/out events) that have the given UserPIN and OrderID.
DbConnection dcon;
DbCommand usecmd = GetCommand(out dcon);
usecmd.CommandText = String.Format(RelevantRecordsSQL, UserID, OrderID);
DateTime usecurrent = DBTime();
//NOTE: ASSUMPTION: we assume, naturally, that a user cannot clock into a order twice. This could be the case as far as the CLOCKED table, though in case of DB corruption
//or other circumstances, so if that happens we will throw a exception.
//DatabaseStructureCorruptedException
List<DateRange> buildlisting = new List<DateRange>();
DateTime? getstarttime = null, getendtime = null;
TimeSpan accumulator = new TimeSpan();
using (DbDataReader readdata = usecmd.ExecuteReader())
{
DateTime laststamp = new DateTime();
while (readdata.Read())
{
DateTime getstamp = readdata.GetDateTime(readdata.GetOrdinal("STAMP"));
laststamp = getstamp;
String etype = readdata.GetString(readdata.GetOrdinal("EventType"));
if (etype == "IN")
{
getstarttime = getstamp;
}
else
{
if (getstarttime != null)
{
getendtime = getstamp;
DateRange buildrange = new DateRange(getstarttime.Value, getendtime.Value);
buildlisting.Add(buildrange);
getstarttime = null;
//accumulator += buildrange.Span;
}
}
}
if (getstarttime != null)
{
if (laststamp > usecurrent)
{
Debug.Print("laststamp > DBTime()");
}
DateRange buildrange2 = new DateRange(laststamp, usecurrent);
Debug.Print(buildrange2.ToString());
buildlisting.Add(buildrange2);
//accumulator+=buildrange2.Span;
//TimeSpan addvalue = (DBTime() - getstarttime.Value).Duration();
// Debug.Print("adding accumulated value:" + addvalue.ToString());
}
}
return buildlisting.ToArray();
}
public TimeSpan GetUserTotalTimeOnOrder(String UserID, String OrderID, out DateRange[] ranges)
{
String RelevantRecordsSQL = qAdapter["USERTOTALTIMEQUERY"];
//select all records in the CLOCKED table (which is used to store all clock-in/out events) that have the given UserPIN and OrderID.
DbConnection dcon;
DbCommand usecmd = GetCommand(out dcon);
usecmd.CommandText = String.Format(RelevantRecordsSQL,UserID,OrderID);
DateTime usecurrent = DBTime();
if (UserID.Equals("0812"))
{
Debug.Print("bug...");
}
List<DateRange> buildlisting = GetUserWorkOrderRanges(UserID, OrderID).ToList();
long sumticks = 0;
foreach (var blisting in buildlisting)
{
sumticks+=blisting.Span.Ticks;
}
ranges = buildlisting.ToArray();
TimeSpan totalresult = new TimeSpan(sumticks);
return totalresult;
//return accumulator;
}
public static DateRange[] CoalesceRanges(DateRange[] ranges)
{
//coalesces/simplifies a given set of date ranges to the simplest form. For example:
/* a
* |----|
* b
* |----| c
* |----|
* */
//will return an array of two Dateranges:
/* a
* |-----|
* b
* |----|
* */
//this is the "simplest" reduction of what the previous ranges were.
List<DateRange> userange = ranges.ToList();
//userange.Sort((w,p)=>w.StartTime.CompareTo(p.StartTime));
List<DateRange> workalist = userange;
List<DateRange> result = new List<DateRange>();
//algorithm
//set a flag indicating a coalesce was discovered to true.
//iterate while said flag is true.
//set flag to false as loop begins.
//iterate through every possible pair X and Y in the List. skip iterations where X==Y.
//for each possible pairing:
//coalesce the two DateRanges. If the results coalesce (the return value array has a length of 1)
//mark x and y for removal, set the new Array to be AddRanged() to the List, and break out of both loops, and set the flag saying that
//a coalesce was discovered.
bool coalescefound = true;
while (coalescefound)
{
List<DateRange> removalmarked = new List<DateRange>();
List<DateRange> addmarked = new List<DateRange>();
bool breakouter = false;
coalescefound = false;
foreach (DateRange x in workalist)
{
foreach (DateRange y in workalist)
{
if (x != y)
{
DateRange[] coalresult = DateRange.Coalesce(x, y);
if (coalresult.Length == 1)
{
coalescefound = true;
//add new array to be added...
addmarked.AddRange(coalresult);
//remove both x and y. We can't remove them here since we are iterating...
removalmarked.Add(x);
removalmarked.Add(y);
breakouter = true;
break;
}
}
}
if (breakouter) break; //break out of outer iterator if flag set.
}
//add and remove the marked items.
foreach (var removeme in removalmarked)
{
workalist.Remove(removeme);
}
foreach (var addme in addmarked)
{
workalist.Add(addme);
}
}
return workalist.ToArray();
}
public DateRange[] Coalesce(DateRange Otherdaterange)
{
return DateRange.Coalesce(this, Otherdaterange);
}
