From: ray.bradbury9@gmail.com   
      
   El sábado, 8 de junio de 2013 05:48:04 UTC+2, Craig  escribió:   
   > We are developing a database that handles payments to people. The area I am   
   focusing on at the moment, is the scheduled payment options. Basically, the   
   system sends payments to people once every fortnight. So, every 14 days, on a   
   specific day (   
   Wednesday, at the moment), the system must process the payments and send them   
   out.   
   >    
   >    
   >    
   > So, an example. PersonA gets $10/day. Every two weeks, the system   
   accumulates all payments for a person, sums them up, and sends out a payment.   
   >    
   >    
   >    
   > So, after the first payment, the person gets $140 sent to them.   
   >    
   >    
   >    
   > However, it's complicated by the way that, if the payment day falls on a   
   public holiday, or a shut down period, such as the christmas leave period   
   (24th Dec to 2nd Jan), the system must make the payment on the day before the   
   public holiday, or shut    
   down period, and process any future payments for that period.   
   >    
   >    
   >    
   > Example:   
   >    
   >    
   >    
   > Payment period is 1st June, to 14th June. The payments for this period will   
   be sent on the 14th, and be an accumulation of all payments for the period...   
   So, on the 14th, we do a payment run that collects all payments within that   
   period, and sends it    
   off. However, there is a company shut down period from the 10th until the   
   18th. So, on the 9th, the system must get all payments from the 1st, to the   
   14th (The building of these payments has been handled), and create the payment   
   on the 9th. The next    
   period (15th to the 29th) will work as normal.   
   >    
   >    
   >    
   > The question is: Handling this payment schedule. It's been proposed that we   
   create a schedule table, with all the periods. So, record one: Start Date =   
   1st June, End Date, 14th June. Record two has Start as the 15th, end of the   
   29th.. and so on. How    
   far to go into the future? No idea. But... that seems odd to me. I thought   
   that we just need one Schedule row, with a 'Effective Date' of the 1st of   
   June, and a 'Duration' which say 'Fortnight', or something. Then, with .Net,   
   or SQL, we can work out if    
   we're on a 'trigger' date. So, the first trigger date would be effective date   
   plus 14 days. If they then decided to change it from a Wednesday to a Monday   
   (For example), add a new Schedule row, with the Monday date that the schedule   
   becomes active, and    
   when the system date hits that date, the new schedule becomes active. There   
   would have to be some logic to handle this, but the basic concept of one row   
   in the schedule date is my idea. Holding all future fortnight dates seems -   
   odd.   
   >    
   >    
   >    
   > Handling close down periods would be handled via an 'Exception Dates', type   
   table, which simply holds public holidays and shut down periods, Basically,   
   dates where payments can't be processed.   
   >    
   >    
   >    
   > So, every night, a process runs. And takes the active schedule row, and   
   works out if we're on fortnight date matching that effective date (How?), and   
   checks the exception table to work out if we need to trigger an early payment.   
   >    
   >    
   >    
   > How we work out if we're on a payment date based on an Effective date, and   
   then checking the 'period' may be tricky... But, surely storing all future   
   fortnights is - wrong?   
      
   At job I did somehing similar. The easiest way if dealing with windows   
   environments and .NET *in my personal opinion* is create a simple console   
   application that is schedulled to run daily that gets the data from the sql   
   server database and does the    
   payment.   
      
   In the .exe.config file I would put the days it should usually trigger the   
   payments.    
   In the database I would just introduce in the exceptions table the following   
   format. Both field should make the primary key   
      
   InitialExceptionDate(DateTime)    -    EndExceptionDate(DateTime)   
      
      
   The .NET console application should check if it should do the payment   
   considering:   
      
   DateTime.DayOfWeek property to check for weekends   
   The Sql Server exceptions table (aka holidays table)   
      
   That way you can avoid a schedule table and you just should store the data at   
   the beginning of the year when the yearly company calendar is released, or   
   when changes to the holidays happen.   
      
      
      
      
   Quick sample code, untested (dont have visual studio handy), missing of   
   propper try-catch-finally code to make sure connection to database is always   
   closed (quite in a hurry at the moment):   
      
   // Get the payDay of the current month   
   DateTime dayToPay = new DateTime(DateTime.Today.Year, DateTime.Today.Month,   
   Int32.Parse(ConfigurationManager.AppSettings["dayToPay"]));   
      
   SqlConnection conn = new SqlConnection("connection string to database");   
   SqlCommand comm = new SqlCommand("select InitialExceptionDate from Holidays   
   where @dayToPay between InitialExceptionDate and EndExceptionDate", conn);   
   comm.Parameters.AddWithValue("@dayToPay", dayToPay);   
   conn.Open();   
   object firstHolidayDay = comm.ExecuteScalar();   
   conn.Close();   
      
   bool isInHoliday = (firstHolidayDay == null || > firstHolidayDay ==   
   DBNull.Value);   
      
   // if in holidays get first non-holiday day   
   if(isInHoliday) dayToPay = ((DateTime) firstHolidayDay).AddDays(-1);   
      
   // check if dayToPay is still laboral day   
   if(dayToPay.DayOfWeek == DayOfWeek.Sunday) dayToPay = dayToPay.AddDays(-2);   
   if(dayToPay.DayOfWeek == DayOfWeek.Saturday) dayToPay = dayToPay.AddDays(-1);   
      
   if(dayToPay == DateTime.Today) {   
    // Do the payment code   
   }   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)