One way to historic Subtotals

Question

 

Hello

 If you need this:

 

Analyzing your requirement I remembered the issue of triggers in sql server. And the truth I suggest that this management is done that way if you want to keep a history of subtotal.

 

So my suggestion poses without detriment to other alternatives and if your requirement does not apply to no problem.

 

Add property Total - Double - Not require, in the Employee entity,

another of the same features Subtotal of Usage.

 

These fields must be initialized to zero to create the record. and the screen make them texbox, not editing.

 

As design procedures, working recursively from the record is updated or following of being deleted. To same employee. And others are updated in the order of ID number. Add this code:

 

 

Namespace LightSwitchApplication

 Public Class ApplicationDataService

  Private Sub Usages_Inserting(entity As Usage)

   If entity.UsageValue IsNot Nothing Then
    entity.Employee.TotalUsage = entity.Employee.TotalUsage + entity.UsageValue
    entity.SubTotalUsage = entity.Employee.TotalUsage
   End If

  End Sub

  Private Sub Usages_Updating(entity As Usage)

   If entity.UsageValue IsNot Nothing Then
    entity.Employee.TotalUsage = entity.SubTotalUsage - entity.Details.Properties.UsageValue.OriginalValue + entity.UsageValue
    entity.SubTotalUsage = entity.Employee.TotalUsage

    Dim us As Usage = DataWorkspace.ApplicationData.Usages.Where(Function(a) a.Id > entity.Id And a.Employee.Id = entity.Employee.Id).OrderBy(Function(b) b.Id).FirstOrDefault
    If us IsNot Nothing Then
     us.Employee.TotalUsage = us.Employee.TotalUsage + us.UsageValue
     us.SubTotalUsage = us.Employee.TotalUsage
    End If
   End If

   entity.Update = Now
  End Sub

  Private Sub Usages_Deleting(entity As Usage)

   If entity.UsageValue IsNot Nothing Then
    Dim total = entity.SubTotalUsage - entity.UsageValue

    Dim us As Usage = DataWorkspace.ApplicationData.Usages.Where(Function(a) a.DateTransaction > entity.DateTransaction And a.Employee.Id = entity.Employee.Id).OrderBy(Function(b) b.Id).FirstOrDefault
    If us IsNot Nothing Then
     us.Employee.TotalUsage = total + us.UsageValue
     us.SubTotalUsage = us.Employee.TotalUsage
    End If
   End If

  End Sub

  Private Sub Usages_All_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Usage))
   query = query.OrderByDescending(Function(a) a.DateTransaction)
  End Sub

  Private Sub Employees_All_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Employee))
   query = query.OrderByDescending(Function(a) a.Name)
  End Sub

 End Class

End Namespace

 

 

Thus no need to worry since the screen is made insert, update or delete records, which is done from the server side.

 

Now, there's the code for Inserting, Updating and Deleting. But I suggest to analyze the pros and cons of Updating and Deleting on a historic, too it may terminate demanding  reprocessing full time.

 

Therefore my suggestion is just use inserting and any correction is made by adding a record with their respective description of motive.

 

Who wants to try can download it from SkyDrive: LS_SubTotalEmployeeUsage.zip

 

Jaime

I hope that help!