473,327 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

tricky TimeSpan editing

I have a "Timesheet" application I am building for my company.

The way I have the sql tables setup, is each day has a unique record
for a specific user, then there is a TimeEntry table, that has multiple
TimeEntries for one day.

The TimeEntries for a specific day are like so:

7:00AM - 11:00AM : Regular Hours
11:00AM - 12:00PM : Lunch
12:00PM - 5:00PM : Regular Hours

now, when someone edits the lunch time entry and changes it to
11:30AM-12:00PM, I have some code that goes through the other
timeentries for that day and changes their startTime and EndTime to
coincide with the change to the "Lunch" entry, therefore there are no
gaps between time entries. this has turned into a little bit of a
nightmare because I currently have 7 if-elseif statements that does the
logical checking if a timeentry overlaps, and if the others need to be
changed. could there be an easier way to complete this logic without
having a number of if-elseif statements...here is my method that takes
care of changing other time entries:

/// <summary>
/// This checks overlap of times of all other TimeEntries for this
day in the database
/// if a TimeEntry overlaps ts/te, the TimeEntry is changed and saved
back to the database
/// </summary>
/// <param name="ts">TimeStart of the new TimeEntry</param>
/// <param name="te">TimeEnd of the new TimeEntry</param>
private void CheckOverlap(DateTime ts, DateTime te) {
DataSet dsTimeEntryByDay = this.GetTimeEntryByDay(DayID);
TimeSpan workingTime = te - ts;
int changed = 0;
foreach (DataRow dr in dsTimeEntryByDay.Tables["TimeEntry"].Rows) {
if (TimeEntryID != (int)dr["TimeEntryID"]) {
DateTime newTS = new DateTime();
DateTime newTE = new DateTime();
DateTime cTS = Convert.ToDateTime(dr["TimeStart"]);
DateTime cTE = Convert.ToDateTime(dr["TimeEnd"]);
changed = 0;

if (cTS < ts && cTE > ts) {
// If time is before changed time, and cTE overlaps ts
newTE = ts;
newTS = cTS;
changed = 1;
}
else if (cTE > te && cTS < te) {
// If time is after changed time, and cTS overlaps te
newTS = te;
newTE = cTE;
changed = 1;
}
else if (cTS < ts && cTE < ts) {
newTS = cTS;
newTE = ts;
changed = 1;
}

// Problem here, if you change the first entry, it changes the correct
one, and all subsequent entries
// because they all fit the criteria
else if (cTE > te && cTS > te && cTS < newTE) {
newTS = te;
newTE = cTE;
changed = 1;
}

if (changed == 1) {
this.UpdateTimeEntry((int)dr["TimeEntryID"], (int)dr["DayID"],
newTS, newTE, (int)dr["EntryTypeID"]);
}
}
}
}

Nov 17 '05 #1
7 2726
Try looking at the problem a different way.

In each of your time entries, the TimeStart is the "truth"... the
master value, if you will. The TimeEnd is just a convenient thing to
have on the record.

This point of view leads to the following:

1. If anyone changes the TimeEnd of any entry, you really need to
search for the entry with the next higher TimeStart and change that
entry's TimeStart instead. If there is no entry with a TimeStart larger
than this entry's TimeStart, then just change this entry's TimeEnd.

2. If anyone changes anything about the start / end times of an entry,
go grab all of the entries for that person for that day, sorted by
TimeStart, and make the TimeEnd of each one equal to the TimeStart of
the next one. If there is no next one, leave the TimeEnd alone.

This code will be much easier to understand and maintain. Essentially,
point #2 sweeps through the time entries and enforces your invariant
condition: that there be no overlaps and no gaps (although there may be
entries with zero duration). No matter what you do to the TimeStart and
TimeEnd of an entry, if you run all of the entries through #2, you will
restore the rule.

This is always easier and more reliable than trying to fix records
one-by-one, out of the context of the whole group of records. Notice
that with this new algorithm, even if the data becomes damaged (an
overlap or gap is somehow introduced), the next change by a user will
repair the data for that user for that day.

Nov 17 '05 #2
Try looking at the problem a different way.

In each of your time entries, the TimeStart is the "truth"... the
master value, if you will. The TimeEnd is just a convenient thing to
have on the record.

This point of view leads to the following:

1. If anyone changes the TimeEnd of any entry, you really need to
search for the entry with the next higher TimeStart and change that
entry's TimeStart instead. If there is no entry with a TimeStart larger
than this entry's TimeStart, then just change this entry's TimeEnd.

2. If anyone changes anything about the start / end times of an entry,
go grab all of the entries for that person for that day, sorted by
TimeStart, and make the TimeEnd of each one equal to the TimeStart of
the next one. If there is no next one, leave the TimeEnd alone.

This code will be much easier to understand and maintain. Essentially,
point #2 sweeps through the time entries and enforces your invariant
condition: that there be no overlaps and no gaps (although there may be
entries with zero duration). No matter what you do to the TimeStart and
TimeEnd of an entry, if you run all of the entries through #2, you will
restore the rule.

This is always easier and more reliable than trying to fix records
one-by-one, out of the context of the whole group of records. Notice
that with this new algorithm, even if the data becomes damaged (an
overlap or gap is somehow introduced), the next change by a user will
repair the data for that user for that day.

Nov 17 '05 #3
Ok, I finally got it working and it does seem to work much smoother.
here is the final code:

private bool CheckOverlap(DateTime ts, DateTime te) {
bool hasChanged = false;
DataSet ds = this.GetTimeEntryByDay(DayID);

int i;
for(i=0;i<ds.Tables["TimeEntry"].Rows.Count;i++) {
DataRow dr = ds.Tables["TimeEntry"].Rows[i];
if ((int)dr["TimeEntryID"] == TimeEntryID) {
// TimeEnd has been changed, Change the TimeStart of the next row,
if it exists
if ((DateTime)dr["TimeEnd"] != te && i !=
ds.Tables["TimeEntry"].Rows.Count - 1) {
DataRow nextDR = ds.Tables["TimeEntry"].Rows[i+1];
nextDR["TimeStart"] = te;

// Save the next TimeEntry back to the database
hasChanged = true;
this.UpdateTimeEntry((int)nextDR["TimeEntryID"],
(int)nextDR["DayID"], (DateTime)nextDR["TimeStart"],
(DateTime)nextDR["TimeEnd"], (int)nextDR["EntryTypeID"]);
}
// TimeStart has been changed, Change the previous items TimeEnd,
if it exists
if ((DateTime)dr["TimeStart"] != ts && i != 0) {
DataRow prevDR = ds.Tables["TimeEntry"].Rows[i-1];
prevDR["TimeEnd"] = ts;

// Save the prev TimeEntry back to the database
hasChanged = true;
this.UpdateTimeEntry((int)prevDR["TimeEntryID"],
(int)prevDR["DayID"], (DateTime)prevDR["TimeStart"],
(DateTime)prevDR["TimeEnd"], (int)prevDR["EntryTypeID"]);
}
}
}
return hasChanged;
}

If hasChanged is true, then I loop through each timeEntry and reset the
TimeStart's that you mentioned in Rule #2

thanks for your help!

Nov 17 '05 #4
A couple of notes on your code.

First and most important, I want to make sure that the SQL you use to
fetch your TimeEntries includes an ORDER BY on ths StartTime. If you
just do a simple SELECT then ADO.NET does not make _any_ guarantees
about the order in which the rows will be returned.

Another way to solve this problem is to use a DataView and tell the
DataView to sort the rows. That way the DataView will always maintain
the rows correctly sorted, no matter what you do to them. This is what
I would do.

Finally, I would change the structure of your code: make a method
called FindTimeEntry(DataView view, int timeEntryId) that returns an
index. That will get rid of the main loop in your method, and change it
to be simply:

private bool CheckOverlap(DateTime ts, DateTime te) {
bool hasChanged = false;

// Change to return DataView... why do you want the whole DataSet?
DataView view = this.GetTimeEntryByDay(DayID);
int rowIndex = FindTimeEntry(view, TimeEntryID);

DataRowView drv = view[rowIndex];
if ((DateTime)drv["TimeEnd"] != te && rowIndex != view.Co*unt - 1)
{
DataRowView nextDR = view[rowIndex +*1];

// Note that this may change the order of the rows in the data
view
// (as it's sorted by TimeStart), so we must assume that the
// rowIndex is invalid after we do this.
nextDR["TimeStart"] = te;

// Save the next TimeEntry back to the database
hasChanged = true;
this.UpdateTimeEntry((int)next*DR["TimeEntryID"],
(int)nextDR["DayID"], (DateTime)nextDR["TimeStart"],
(DateTime)nextDR["TimeEnd"], (int)nextDR["EntryTypeID"]);
}

// Now fix all end times
for (int i = 0; i < view.Count - 1; i++)
{
DataRowView thisRow = view[i];
DataRowView nextRow = view[i + 1];
if ((DateTime)thisRow["TimeEnd"] !=
(DateTime)nextRow["TimeStart"])
{
thisRow["TimeEnd"] = nextRow["TimeStart"];
this.UpdateTimeEntry((int)thisRow["TimeEntryID"],
(int)thisRow["DayID"], (DateTime)thisRow["TimeStart"],
(DateTime)thisRow["TimeEnd"], (int)thisRow["EntryTypeID"]);
hasChanged = true;
}
}
return hasChanged;
}

Nov 17 '05 #5
that code is alot more effecient. Thank you for your help.

One more question:

On operations such as mentioned above, since I am making so many calls
updating/grabbing data from the backend, would it be even better to
hold the data in a new dataset and then once all the changes are
complete, push the updated dataset back to the database? I suppose if i
do this I have to worry about concurrency checking etc??

thanks

Sean

Nov 17 '05 #6
You have to worry about concurrency checking anyway. Between the time
you grab all of the TimeEntrys for the person for the day and the time
you update them, something could have changed on the back end. It
doesn't matter if you do the updates all together or one at a time.
Same problem.

In order to get your head around the problem, I would break it down
this way.

First, realize that there is always the chance that an update will
fail. Given that, you want to make the operation "atomic" at the user
level. That is, the user changes a start time, or an end time, or adds
a new TimeEntry, which causes other entries to be changed as a result.
The user is expecting that either "their" update works or it fails
because someone else is changing the same person for the same day. So,
from the user's point of view, if there are two operations needed in
order to update the TimeEntrys to reflect the user's (single) change,
then the user wants _both_ of those operations to either succeed or
fail.

This tells me that you want to make all of the changes in your
disconnected dataset, and then commit those changes to the database all
at once. By this, I _don't_ mean that you want to save up multiple
changes made by the user. You want to commit each user change as it
happens, and tell the user if it didn't work. However, you _do_ want to
send together the two or three updates necessary in order to implement
one time change.

The only thing I don't know how to do in ADO.NET is to tell it that all
updates / inserts must succeed or fail together as a _transaction_.
Does ADO.NET have transactions? I'm not enough of an ADO.NET guru to
know. I took a quick look at the SDK documentation:

http://msdn.microsoft.com/library/de...singadonet.asp

It looks as though you might even be able to leave your code as it
is... just use BeginTransaction and Commit or Rollback (if you get any
errors) on your Connection object... but as I said, I've never done it,
so I don't know exactly how it works.

Something I have done, and which I recommend, is use a DataAdapter to
describe the relationship between your ADO.NET in-memory, disconnected
DataSet and your database tables. Then you can make changes to the
DataTable in response to a user action, and Commit the changes. I'm
thinking it would look something like this:

DataSet personDay = ... fetch the data for a person for a day ...
user makes a change
myConnection.BeginTransaction
change DataTable in response to user's action
personDay.Commit -- commits changes back to database
if Commit succeeded
myConnection.Commit -- commits transaction
else
myConnection.Rollback -- undoes all changes
warn user that change didn't "take" because someone else is
modifying
endif

....or something like that. Perhaps someone with more experience in this
area can correct me, or supply better pseudo-code. :)

Nov 17 '05 #7
thank you for the information. It was very informative.

If i do implement transactions, then I will probably employ it at the
t-sql level, instead of bloating my code more. Whenever I can do
something on the backend in t-sql, I try to do it there to save
bandwidth and processor usage.

I'm thinking that I might be able to port some of the original code
over to t-sql. For instance, the part of the code that fixes all of the
endtimes at the very least. But since most parts of this do need to be
processed in code, I might have to adopt ado.net transactions as well.

In reality, there will be only one user editing their timesheet at a
time. The "admins" (about 3 of us) will be editing other peoples
timesheets but not even at this level, only if there is a problem or
they can't figure something out (more of a support standpoint)

Thank you sir for all your information, you have been extremely helpful.

Nov 17 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Jeff Shantz | last post by:
Hello, I'm developing a large statistics application for a call center. It often needs to calculate time spanning over months. For example, an agent's total talk time within 30 days. Since an...
3
by: Ivan A. | last post by:
Hi! Why I can't serialize TimeSpan structure with XmlSerializer? This is what I do: using System; using System.IO; using System.Xml; using System.Xml.Serialization;
0
by: DKode | last post by:
I have a "Timesheet" application I am building for my company. The way I have the sql tables setup, is each day has a unique record for a specific user, then there is a TimeEntry table, that has...
11
by: Russ Green | last post by:
How does this: public TimeSpan Timeout { get { return timeout; } set { timeout = value; if(timeout < licenseTimeout) licenseTimeout = timeout; }
2
by: DWalker | last post by:
In Visual Studio (Visual Basic) .NET 2002, I noticed that this: Dim Elapsed as DateTime = Now - Now gives a "compile time" error (error in the IDE), saying that the '-' operator is not...
10
by: Charles Law | last post by:
If I display a TimeSpan I get something like 00:05:17.6217891 when what I would like to see is 00:05:18 Is there an easy way to get this output? Try as I might I just can't find it.
2
by: ucasesoftware | last post by:
i translate a C# funtion to VB.NET and i have this : Shared Function isAllDay(ByVal ap As Appointment) As Boolean Return ap.DateBegin.TimeOfDay = TimeSpan.Zero AndAlso ap.DateEnd.TimeOfDay =...
13
by: sd00 | last post by:
Hi all, can someone give me some coding help with a problem that *should* be really simple, yet I'm struggling with. I need the difference between 2 times (Target / Actual) However, these times...
4
by: Massimo | last post by:
Hi to All, i'm using C# in .NET 2.0 and i have a DataTable A with a column of type TimeSpan used to store HOUR info. I'm trying to filter my DataTable A selecting only rows that have the column...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.