Custom Expression Engine for Payroll

Jan 13, 2009 at 4:33 AM
Edited Jan 13, 2009 at 5:08 AM
Hi eugene,
I Using the Expression Evaluator for a Custom Evaluation Engine for Payroll Formulas.I Have 1355 payroll (Formulas/and/or Variables)

When I Load the Data For The Formulas/Variables from SQL Server Database and Create The variables/and Formulas and Evaluate them.
 The Elapsed Time is about 16 seconds mainly because of
   Dim e As IGenericExpression(Of Double) = context.CompileGeneric(Of Double)(oValue.ToString())   


Then in subsecuent evaluations (changing only the variables and reevaluate the formulas), the elapsed time is about .38 seconds for the 1355 formulas

 The Big Questions are:
   1) How I can Reduce the Time of 16 seconds During the Creation/Setup of the Formulas by the expression Evaluator.
      (I noticed that for each of the formulas/variables it adds up to the Expression.dll in memory),
   2) Is there any way to add all the expressions(1355) at once to the Expression.dll in memory?, Thus reducing the 
       Initital Creation FormulaCreation Time of 16 seconds?
     Any toughts are welcome

   I'm not using your Batch loader Class Because:
    1) it does the Natural order of recalcuation and might degrade the performance for the Evaluation of the formulas.
    2) When Im loading the data from the SQL server I load it in the natural order of recalculation(so i don't need to
         tag a reference to the evaluation result of a formula be tagged with $ ie :    loader.Add("b", "$a + 1 + $a", context)
         (imagine manually tagging the 1355 formulas !!! no way)

   Best Regards
   Arturo Ruvalcaba



Jan 13, 2009 at 5:21 AM
Edited Jan 13, 2009 at 5:31 AM
 Never Mind, I figured Out, I have to tell Flee to not to generate an assembly to a disk file with
   options.EmitToAssembly = False   ' to create the assembly in memory  



 Awesome Library I'ts 1000 zillion times faster than your Excel Like Formula Engine

Best Regards, From Mexico,
Keep the good work
Arturo Ruvalcaba

   any way tought you guys wanted to see some Stats for evaluation pourposes:
  1) Formulas and variables                                                 : 1344 
  2) Time to create Formulas and Variables (compile them) : 1.76 seconds 
  3) Compilation Method                                                    : Dim e As IGenericExpression(Of Double) = context.CompileGeneric(Of Double)(oValue.ToString())
  4) Speed                                                                          : 766 evaluations per second 

  reevaluations :
  1) Time to re evaluate Formulas and Variables  : 0.37 seconds 
  2) Speed                                                                          : 3652 evaluations per second 
  It would be nicer If  I could paste the screenshoot



Jan 13, 2009 at 6:26 PM
Hi Arturo,

Glad you like the library.

>Never Mind, I figured Out, I have to tell Flee to not to generate an assembly to a disk file with
>   options.EmitToAssembly = False   ' to create the assembly in memory  
The EmitToAssembly functionality is mostly meant to be a diagnostic tool: it makes Flee emit its IL to an assembly so that you can view it with a disassembler.

> I'm not using your Batch loader Class
Yeah, the calc engine needs refactoring.  The first thing that needs to be done is eliminate the need to reference other expressions with a '$'.  I'm also not sure how useful the calc engine is to people so I don't want to spend a lot of effort on a feature that nobody uses.  What do you think?
Jan 14, 2009 at 7:15 AM
Edited Jan 14, 2009 at 7:23 AM
  the calc engine is fine like it is, it's fine for evaluating formulas like in a spreadsheet context when you have a bunch 
  of formulas all spread around a like in a excel spreadsheet (as in your excel formula engine).

  But in my case I already have the expressions/variables in natural order of recalcuation, as Im 
  Using the expressions/formulas from an shrink wrap app, And using them in an payroll app that im
  developing for the Mexican Payrolls (personal project for late nights work is geting to boring :>). Any way. 
  The calc engine could be ok as it is, But i'm wondering if you could create another class with out 
  1)  the natural order of recalculation (topological sort)
  2)  the access to the other expressions with the $ 
  but still being able to use the batch loader class with the new class. so it will be simpler to use and will maybe have 
  better performance because of 1) and 2) overhead is eliminated particulary
  Does this make sense? or im asking to much?, and the performance gains for this new class will be worth it and
  noticeable for volumes of 1000 or more expressions/formulas

  thats what I think,  letme know what you think.

  Best Regards,
  and Late happy new year :>)
  Arturo Ruvalcaba

Jan 16, 2009 at 12:33 AM
Your idea is a simpler version of the calc engine.  If the expressions being loaded are assumed to be in natural order, then resolving expressions is much simpler and the '$' is not required.

When an expression references another expression, the engine will add the referenced expression as a variable to the parent expression.  Once all the expressions are loaded, you could do something like the following:
double result = calcEngine.GetResult<double>("topLevel1");
which would evaluate the top-level expression, and in turn, evaluate all the lower level expressions to get the result.

So basically, the workflow would be:
  • Load the expressions into the engine
  • Get the results of all the expressions you are interested in
Am I close to what you have in mind?
Jan 16, 2009 at 5:45 AM
Edited Jan 16, 2009 at 5:56 AM
Yes, I haven't tested, If I can do it what I need  with the Calc Engine with Out the "$" reference, I Might Try it later, but for now
I cook up a rough Payroll Engine like the code below
 If Im not wrong. I made Public these:
  Sorry for the long text (i shortened the LoadandEvaluateData proc and the builtin functions), but Might Be Intersting for others any way you might  get a better idea :>) 

 Best Regards
 Arturo Ruvalcaba

{================Payroll Engine Evaluator Sample with Out "$" ========================}

Imports ciloci.Flee
Imports System.Data.SqlClient

 Public Class frmPayroll
    Public blnFormulasCreated As Boolean
    Public myPayrollEngine As New PayrollEngine

#Region "Payroll Functions 57 Functions"
    Public Class BuiltInFunctions
        Private XctnQty As Integer
        Public Shared Function _acumulado(ByVal prm1 As Integer) As Double
            Dim number As Double = 150
            Return number
        End Function
        Public Shared Function VPagoVacaciones(ByVal prm1 As Integer) As Integer
            Dim number As Integer = 150
            Return number
        End Function
        Public Shared Function FRAC(ByVal prm1 As Double) As Double
            Dim number As Double = 150
            Return number
        End Function
        Public Shared Function INT(ByVal prm1 As Double) As Integer
            Dim number As Integer = 150
            Return number
        End Function

    End Class
#End Region

    Public Class PayrollEngine
        ' Use same options for all expressions
        Public context As ExpressionContext
        Private options As ExpressionOptions
        Public vc As VariableCollection

        Public SQLCnx As SqlConnection
        Public SQLCmd As SqlCommand
        Public cont As Integer = 0
        Private SQLDtR As SqlDataReader
        Private sVarName As String = ""
        Private sVarExpr As String = ""
        Private sStype As String = ""

        Public Sub LoadPayrollInfo()
            context = New ExpressionContext()
            options = context.Options
            vc = context.Variables

            options.IntegersAsDoubles = False
            options.EmitToAssembly = False
            ' Import math so we have access to its functions in expressions

            SQLCnx = New SqlConnection("Data Source=(local);Initial Catalog=Nomipaq;Integrated Security=SSPI;")
            SQLCmd = New SqlCommand
            SQLCmd.Connection = SQLCnx
            SQLCmd.CommandType = CommandType.Text


        End Sub

        Public Sub LoadandEvaluateData(ByVal bShow As Boolean)
            '==============Load all Info and Formulas from database and evaluate them
            Dim icnt As Integer
            Dim arr(20, 2) As String
            Dim elem As Integer
            Dim bnd As Boolean

            arr(0, 0) = "Select * from Nom10000 where IDEmpresa =3                          " 'Company      SomeCompany
            arr(17, 0) = "Select * from Nom10004 Where TipoConcepto = 'P' order by NumeroConcepto" 'Payroll Concepts  P = Income
            arr(18, 0) = "Select * from Nom10004 Where TipoConcepto = 'D' order by NumeroConcepto" 'Payroll Concepts D = Withdrawals (Taxes for income)
            arr(19, 0) = "Select * from Nom10004 Where TipoConcepto = 'O' order by NumeroConcepto" 'Payroll Concepts O = Employeer Obligations (Taxes on behaf of employee)
            arr(20, 0) = "Select * from Nom10004 Where TipoConcepto = 'N' order by NumeroConcepto" 'Payroll Concepts N = Net Income
            arr(0, 1) = "empresa"
            arr(17, 1) = "conceptos"
            arr(18, 1) = "conceptos"
            arr(19, 1) = "conceptos"
            arr(20, 1) = "conceptos"
            sVarName = "_0"
            bnd = DoAdd("Var", 0)
            For elem = 0 To 20
                If arr(elem, 1) <> "" Then
                    With SQLCmd
                        .CommandText = arr(elem, 0)
                        SQLDtR = .ExecuteReader()
                        If SQLDtR.HasRows = True Then
                            While SQLDtR.Read()
                                Dim ifld As Integer
                                If elem = 15 Then
                                End If
                                If arr(elem, 1) <> "" Then
                                    If arr(elem, 1) = "conceptos" Then
                                        For ifld = 19 To 23
                                            Select Case ifld
                                                Case 19 : icnt = 5
                                                Case 20 : icnt = 1
                                                Case 21 : icnt = 2
                                                Case 22 : icnt = 3
                                                Case 23 : icnt = 4
                                                    'Case 0: icnt = 6
                                            End Select

                                            If icnt > 0 Then
                                                sStype = SQLDtR.GetFieldType(ifld).ToString()
                                                If ifld >= 0 Then
                                                    sVarName = "_concepto_" & LCase(SQLDtR.GetValue(2).ToString) & "_" & SQLDtR.GetValue(0).ToString & "_" & icnt.ToString
                                                    sVarExpr = IIf(SQLDtR.GetValue(ifld) Is DBNull.Value, 0, SQLDtR.GetValue(ifld))
                                                    If (SQLDtR.GetValue(ifld) Is DBNull.Value) Then
                                                        bnd = DoAdd("Var", CDbl(0))
                                                        sVarExpr = SQLDtR.GetValue(ifld).ToString
                                                        bnd = DoAdd("Exp", New String(sVarExpr))
                                                    End If
                                                End If
                                            End If
                                        Next ifld
                            End While
                        End If
                    End With
                End If
            Next elem
            frmPayroll.blnFormulasCreated = True
        End Sub
        Private Function DoAdd(ByVal sKind As String, ByVal oValue As Object) As Boolean
            Dim Result As Object
            cont = cont + 1
            Dim x As Integer = 0
            If x = 0 Then
                If frmPayroll.blnFormulasCreated = False Then
                    If sKind = "Exp" Then
                        If oValue.GetType().FullName = "System.String" Then
                            oValue = oValue.ToString().Replace("IIF", "if")
                            oValue = oValue.ToString().Replace("iif", "if")
                        End If
                        Dim e As IGenericExpression(Of Double) = context.CompileGeneric(Of Double)(oValue.ToString())
                        vc.Add(sVarName, e)
                        vc.Add(sVarName, oValue)
                    End If
                    If sKind = "Exp" Then
                        Result = CType(context.Variables.Item(sVarName), IGenericExpression(Of Double)).Evaluate()
                        vc(sVarName) = oValue
                    End If
                End If
            End If
        End Function

    End Class

    Protected Overrides Sub OnLoad(ByVal e As System.EventArgs)
        Me.blnFormulasCreated = False
    End Sub

    Private Sub EvaluateFormulas_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EvaluateFormulas.Click
        Dim sw As New Stopwatch()
        Dim seconds As Double
        Dim iEvalRun As Integer = 0
        seconds = sw.ElapsedMilliseconds / 1000
        Dim NUM_EVALS As Integer = myPayrollEngine.cont
        Debug.Print("Evaluations: {1:n0}{0}Time: {2:n2} seconds{0}Speed: {3:n0} evaluations/sec", System.Environment.NewLine, NUM_EVALS, seconds, NUM_EVALS / seconds)
        Me.lblStatus.Text = String.Format("Evaluations: {1:n0}{0}Time: {2:n2} seconds{0}Speed: {3:n0} evaluations/sec", System.Environment.NewLine, NUM_EVALS, seconds, NUM_EVALS / seconds)
        For iEvalRun = 1 To 1
            seconds = sw.ElapsedMilliseconds / 1000
            Debug.Print("Evaluations: {1:n0}{0}Time: {2:n2} seconds{0}Speed: {3:n0} evaluations/sec", System.Environment.NewLine, NUM_EVALS, seconds, NUM_EVALS / seconds)
        Next iEvalRun
        myPayrollEngine.SQLCmd = Nothing
    End Sub
    Private Sub LoadNames()
        ' Get the names of all named references with formulas in the engine and Put them in a DatagridView (Payroll Calculation Work Sheet)
        Dim retval As String
        Dim item As String
        Dim rowcnt As Integer = 0
        retval = ""
        For Each item In TryCast(, System.Collections.Generic.ICollection(Of String))
            DataGridView1.Rows(rowcnt).Cells(1).Value = item
            DataGridView1.Rows(rowcnt).Cells(0).Value = rowcnt
            rowcnt = rowcnt + 1
        rowcnt = 0
        Dim item2 As Object
        Dim result As Double
        Dim sVARName As String
        For Each item2 In TryCast(, System.Collections.Generic.ICollection(Of Object))
            DataGridView1.Rows(rowcnt).Cells(2).Value = item2.ToString()
            sVARName = DataGridView1.Rows(rowcnt).Cells(1).Value
            If myPayrollEngine.context.Variables.Item(sVARName).GetType().Name = "Expression`1" Then
                result = CType(myPayrollEngine.context.Variables.Item(sVARName), IGenericExpression(Of Double)).Evaluate()
                'Debug.Print("Type = {0} Value = {1}", context.Variables.Item(sVarName).GetType().Name, Result)
                DataGridView1.Rows(rowcnt).Cells(3).Value = result
            End If
            rowcnt = rowcnt + 1
    End Sub
End Class