locked
Working with dates in LightSwitch RRS feed

  • Question

  • Hi,

    Working with dates is fairly common in an LOB application, but this scenario is causing a real headache for my LS app.

    Background:

    Two relevant tables: Vendors, Subcontracts.  Vendors has a zero or one to many relationship to Subcontracts.

    The Vendor's table has a property "FYEndDate" where the Vendor's fiscal year end date is set at the time the vendor record is created.  There is a computed property called "CurrentFYEndDate" that calculates the Vendor's current FY end date (e.g., if the FYEndDate was entered as 12/31/2010, the CurrentFYEndDate would return 12/31/2012).

    The Subcontracts table tracks subcontract period of performance with two date fields, "POPStartDate" and "POPEndDate".

    Problem:

    There is a requirement to send a notice to a subcontractor 30 days following the end of their fiscal year for each fiscal year they perform on particular subcontract.  For example, if the subcontract POP is 7/1/11 - 6/30/12, and the subcontractor's fiscal year ends 12/31/(each year), they would need 2 notices; notice 1 on 1/30/12 and notice 2 on 1/30/13. 

    The plan to deal with tracking this requirement in LS is to have a table named "Notices" that will be a child of Subcontracts.  When a subcontract record is inserted, record(s) will be inserted into Notices, which will have a property "DueDate".  Handling the insert operation to Notices is no problem.  What I can't figure out is how to calculate in code (VB) the number of notices required and their due dates based on the known data (CurrentFYEndDate, POPStartDate, and POPEndDate).

    I have been trying to get the month and day values of the FYEndDate and compare them to the POP start and end dates to determine which of the vendor's fiscal years contained performance.  A count of those fiscal years would provide the number of notices.  The dates of the fiscal years would provide a basis for an .AddDays(30) to get the due date for the notice.  Any ideas how to do this?  This one is killing me!

    Thanks!

    Friday, December 14, 2012 2:36 AM

Answers

  • Here is the answer in case it helps someone:

    Private Sub Subcontracts_Inserting(entity As Subcontract)
                If (entity.POPStartDate IsNot Nothing) AndAlso (entity.POPEndDate IsNot Nothing) AndAlso (entity.Vendor1.FYEnd IsNot Nothing) Then
    
                    'get relevant dates
                    Dim POPStartDate As Date = entity.POPStartDate
                    Dim POPEndDAte As Date = entity.POPEndDate
                    Dim FYEnd As Date = entity.Vendor1.FYEnd
    
                    'separate year, month, and day parts
                    Dim FYMonth As Integer = Month(FYEnd)
                    Dim FYDay As Integer = Day(FYEnd)
    
                    Dim POPStartYear As Integer = Year(POPStartDate)
                    Dim POPStartMonth As Integer = Month(POPStartDate)
                    Dim POPStartDay As Integer = Day(POPStartDate)
    
                    Dim POPEndYear As Integer = Year(POPEndDAte)
                    Dim POPEndMonth As Integer = Month(POPEndDAte)
                    Dim POPEndDay As Integer = Day(POPEndDAte)
    
                    'build performance fiscal year start
                    Dim PFY1 As Date = DateSerial(POPStartYear, FYMonth, FYDay)
    
                    'adjust for calendar month
                    If (PFY1 < POPStartDate) Then
                        PFY1 = PFY1.AddYears(1)
                    End If
    
                    'build performance fiscal year end
                    Dim PFYLast As Date = DateSerial(POPEndYear, FYMonth, FYDay)
    
                    'adjust for calendar month
                    If (PFYLast < POPEndDAte) Then
                        PFYLast = PFYLast.AddYears(1)
                    End If
    
                    'determine period of performance length in years
                    Dim POPYears As Integer = DateDiff(DateInterval.Year, POPStartDate, POPEndDAte)
    
                    'determine number of fiscal years where performance occurred
                    Dim PFYears As Integer = DateDiff(DateInterval.Year, PFY1.AddYears(-1), PFYLast)
    
                    Dim intLetter As Integer = 0
    
                    'calculate number of letter records (notices), the due date for each letter, and insert into entity
                    While PFY1 <= PFYLast
    
                        Dim IncurredCostLetter = New IncurredCostLetter()
                        intLetter += 1
                        Dim strLetter As String = "Performance FY (" + intLetter.ToString + ")"
                        IncurredCostLetter.Subcontract1 = entity
                        IncurredCostLetter.DueDate = PFY1.AddDays(30)
                        IncurredCostLetter.Letter = strLetter
                        PFY1 = PFY1.AddYears(1)
    
                    End While
    
                End If
            End Sub

    Wednesday, December 19, 2012 1:22 AM