Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

holiday calculation

I am trying to add 1 day if the selected date matches with dates in a table but the code doesn't work. suppose 05/11/2011 is holiday, I provide 05/10/2011 14:30 + 1:00 hour the shoudl be 05/11/2011 8:00 but because of holiday the result should be 05/12/2011 8:00. What is wrong with the code?

 List<DateTime> dtholidays = null;
    private  DateTime AddBusinessHours(DateTime current, TimeSpan span)
    {
        TimeSpan businessDayStart = new TimeSpan(0, 7, 0, 0); ;
        TimeSpan businessDayEnd;

        dtholidays = GetPublicHolidays();

        var tempDateTime = current;
        string a = tempDateTime.ToShortDateString();

        var spanLeftInMinutes = span.TotalMinutes;
        while (spanLeftInMinutes > 0)
        {
            if (tempDateTime.DayOfWeek == DayOfWeek.Wednesday)
            {
                businessDayEnd = new TimeSpan(0, 18, 0, 0);
            }
            else
            {
                businessDayEnd = new TimeSpan(0, 15, 30, 0);
            }

            if (IsDayOff(tempDateTime) || tempDateTime.TimeOfDay >= businessDayEnd || dtholidays.Contains(DateTime.Parse(a)))
            {
                tempDateTime = tempDateTime.AddDays(1).Subtract(tempDateTime.TimeOfDay.Subtract(businessDayStart));
                continue;
            }

            if (tempDateTime.TimeOfDay < businessDayStart)
            {
                tempDateTime = new DateTime(tempDateTime.Year, tempDateTime.Month, tempDateTime.Day, businessDayStart.Hours,
                                            businessDayStart.Minutes, businessDayStart.Seconds);
            }

            var minutesLeftToday = (businessDayEnd - tempDateTime.TimeOfDay).TotalMinutes;
            tempDateTime = tempDateTime.AddMinutes(spanLeftInMinutes >= minutesLeftToday ? minutesLeftToday : spanLeftInMinutes);
            spanLeftInMinutes -= minutesLeftToday;
        }
        return tempDateTime;
    }


    private List<DateTime> GetPublicHolidays()
    {
        List<DateTime> list = new List<DateTime>();
        ...

        SqlCommand cmd = new SqlCommand("select * from holidays", cn);
        SqlDataAdapter da = new SqlDataAdapter();
        DataTable ds = new DataTable();
        da.SelectCommand = cmd;
        cn.Open();
        da.Fill(ds);

        foreach(DataRow dr in ds.Rows)
        {
            list.Add(DateTime.Parse(dr["holiday"].ToString()));
        }

        return list;
    }

Open in new window


please help.

ayha
0
ayha1999
Asked:
ayha1999
  • 5
  • 5
1 Solution
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
Ok,

first of all a small summary :

You Give a DateTime (Example 05/10/2011 14:30) and a Timespan (1 Hour)
you want to Add the Timespan to the DateTime and then see what needs to be done
(05/10/2011 15:30)

Is the Hour: Minutes combination
* smaler then the end day (15:30) Leave as is (Is Tuesday so end of day is 18:00)
* Equal or bigger => Add 1 Day and set Hour to 8 and Minutes to 0

Is the Date a Holiday then Add 1 Day (If found in Table)

Rewriting the Summary it seems normal that the Code is doing nothing with your dateTime because
05/10/2011 14:30 Added With 1 Hour gives 05/10/2011 15:30.
The result is not behind the BusinessEndTime (Tueday so 18:00) so nothing is happening.

Hope to have helped.
poor beggar
0
 
ayha1999Author Commented:
What I am trying is, if the result date is falling on public holidays, then add one day. same like a weekend day, it adds one day. even if I add 05/10/2011 14:30 + 10 hrs , the result is next day which is holiday date in the table.

hope it is clear.

thanks
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
Ayha!

it's all in the order of the actions.

lets do your example 1 time
current : 05/10/2011 14:30 
span : 10:00
--------------------------------

businessDayStart : 7:00
tempDateTime : 05/10/2011 14:30 
a = "05/10/2011";

*********************************************************************************************************
spanLeftInMinutes = 10:00 = 10 * 60 + 0 = 600 Minutes


(spanLeftInMinutes = 600 )  ==> Go in Loop

tempDateTime is tuesday - FALSE
==> businessDayEnd = 15:30


tempDateTime is nog DayOff - FALSE
tempDateTime.TimeOfDay >= businessDayEnd - 14:30 >=15:30 - FALSE
dtholidays Contains a ("05/10/2011") - FALSE
==> DO NOTHING


tempDateTime.TimeOfDay < businessDayStart - 14:30 < 7:00 - FALSE
==> DO NOTHING


minutesLeftToday = 15:30 - 14:30 = 1:00 = 1*60+0=60 Minutes
tempDateTime = tempDateTime + 60 Minutes (smallest between minutesLeftToday(60) and spanLeftInMinutes(600)
             = 05/10/2011 14:30 + 1:00 = 05/10/2011 15:30
spanLeftInMinutes = 600 - 60 = 540 

*********************************************************************************************************
(spanLeftInMinutes = 540)  ==> Go in Loop
tempDateTime is tuesday - FALSE
==> businessDayEnd = 15:30


tempDateTime is nog DayOff - FALSE
tempDateTime.TimeOfDay >= businessDayEnd - 15:30>=15:30 - TRUE
dtholidays Contains a ("05/10/2011") - FALSE
==> tempDateTime = tempDateTime +1Day - (15:30 - 7:00 = 8:30)
    tempDateTime = 05/11/2011 7:00


tempDateTime.TimeOfDay < businessDayStart - 7:00 < 7:00 - FALSE
==> DO NOTHING


minutesLeftToday = 15:30 - 7:00 = 8:30 = 8*60+30=510 Minutes
tempDateTime = tempDateTime + 510 Minutes (smallest between minutesLeftToday(510)and spanLeftInMinutes(540)
             = 05/11/2011 7:00 + 8:30 = 05/11/2011 15:30
spanLeftInMinutes = 540 - 510 = 30


*********************************************************************************************************
(spanLeftInMinutes = 30)  ==> Go in Loop
tempDateTime is WEDNESDAY - TRUE
==> businessDayEnd = 18:00


tempDateTime is nog DayOff - FALSE
tempDateTime.TimeOfDay >= businessDayEnd - 15:30>=18:00 - FALSE
dtholidays Contains a ("05/10/2011") - FALSE

==> DO NOTHING (AND HERE YOU WOULD HAVE SEEN THE IT DO SOMETHING


tempDateTime.TimeOfDay < businessDayStart - 15:30 < 7:00 - FALSE
==> DO NOTHING


minutesLeftToday = 15:30 - 7:00 = 8:30 = 8*60+30=510 Minutes
tempDateTime = tempDateTime + 30 Minutes (smallest between minutesLeftToday(510)and spanLeftInMinutes(30)
             = 05/11/2011 14:30 + 0:30 = 05/11/2011 16:00
spanLeftInMinutes = 30 - 510 = -480


*********************************************************************************************************
return 05/11/2011 16:00

Open in new window


What is going Wrong?
The setting of a should be placed INSIDE the Loop
The Third Loop would then have been a case of Holiday adding a Day and now we're still searching for 05/10/2011 inside the Holiday list

Regards
poor beggar
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
Ayha,

Did the change of order fix the problem?

Regards
poor beggar
0
 
ayha1999Author Commented:
poor_beggar,

I was away for two days. I am not able find the change of order the code. Please underline it where I have to make changes.

thanks

ayha
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
ayha,

please move line         string a = tempDateTime.ToShortDateString();
3 Lines down so it's inside the while loop.

your using this to find the date in the holiday array but you never changed it after initial set.
So if moved inside the While loop (3  lines down) every loop it will be set and in my example you'll see in the 3the loop the value canching causing the final date to be changed also.

regards

poor beggar
0
 
ayha1999Author Commented:
I moved the line but still getting wrong result
5/17/2011 is holiday in the table
I added 1 hour with 5/16/2011 15:30 (businessEndTIme), the result should be 5/18/2011 8:00 because 5/17 is a holiday but  got 5/17/2011 8:00.
...
        var spanLeftInMinutes = span.TotalMinutes;
        while (spanLeftInMinutes > 0)
        {
            string a = tempDateTime.ToShortDateString();
            if (tempDateTime.DayOfWeek == DayOfWeek.Wednesday)
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
Ayha,

if I manualy run throug the program this are my Debug values
As you see at the end It should return 05/18/2011 8:00 but only when the Date 05/17/2011 is indeed inside the array dtholidays.  can you double check using debug mode that this value is indeed inside the Array (Exact match is needed)

current : 05/16/2011 15:30
span : 1:00
--------------------------------

businessDayStart : 7:00
tempDateTime : 05/16/2011 15:30

*********************************************************************************************************
spanLeftInMinutes = 1:00 = 1 * 60 + 0 = 60 Minutes


(spanLeftInMinutes = 60 )  ==> Go in Loop
a = "05/16/2011";
tempDateTime is Monday - FALSE
==> businessDayEnd = 15:30


tempDateTime is no DayOff - FALSE
tempDateTime.TimeOfDay >= businessDayEnd - 15:30 >=15:30 - TRUE
dtholidays Contains a ("05/16/2011") - FALSE
==> tempDateTime = tempDateTime + 1Day - (15:30 - 7:00 = 8:30)
    tempDateTime = 05/17/2011 7:00
==> CONTINUE NEXT WHILE JUMP

*********************************************************************************************************
(spanLeftInMinutes = 60 )  ==> Go in Loop
a = "05/17/2011";
tempDateTime is Tuesday - FALSE
==> businessDayEnd = 15:30


tempDateTime is no DayOff - FALSE
tempDateTime.TimeOfDay >= businessDayEnd - 7:00 >=15:30 - FALSE
dtholidays Contains a ("05/17/2011") - SHOULD BE TRUE ==> please check in WatchList that value is there
==> tempDateTime = tempDateTime + 1 Day - (07:00 - 7:00 = 0:00)
    tempDateTime = 05/18/2011 7:00
==> CONTINUE NEXT WHILE JUMP

*********************************************************************************************************
(spanLeftInMinutes = 60 )  ==> Go in Loop
a = "05/18/2011";
tempDateTime is WednesDay - FALSE
==> businessDayEnd = 18:00


tempDateTime is no DayOff - FALSE
tempDateTime.TimeOfDay >= businessDayEnd - 7:00 >=18:00 - FALSE
dtholidays Contains a ("05/18/2011") - FALSE
==> DO NOTHING
      
tempDateTime.TimeOfDay < businessDayStart - 7:00 < 7:00 - FALSE
==> DO NOTHING


minutesLeftToday = 18:00 - 7:00 = 11:00 = 11*60+00=660 Minutes
tempDateTime = tempDateTime + 60 Minutes (smallest between minutesLeftToday(660) and spanLeftInMinutes(60)
             = 05/18/2011 7:00 + 1:00 = 05/18/2011 8:00
spanLeftInMinutes = 60 - 660 = -600
*********************************************************************************************************
(spanLeftInMinutes = -600 )  ==> WE DON'T GO in Loop
*********************************************************************************************************
return 05/18/2011 08:00

0
 
ayha1999Author Commented:
As you said the problem was because of no matching item in the arraylist. I changed the following line and it worked.

from:
 SqlCommand cmd = new SqlCommand("select * from holidays", cn);
 
to:
SqlCommand cmd = new SqlCommand("SELECT convert(varchar, holiday, 103)  as holiday from holidays", cn);
0
 
ayha1999Author Commented:
Thank you very much for your time and support. I am giving the max. point.

thanks once again.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now