Plugin to Write a journal entry and notification to a user

[SBarnes]

Hi All

Are there any samples on how to create a journal entry through a plug in and then also notify a user that the journal has been created, the data would becoming from an SQL database on the same server as the jiwa database, the basic idea is when an entry is created in the other system (wages) journal it into Jiwa and then let the user who may not be logged on at the time know that this has occurred. I was thinking of using the scheduling system to check on a regular basis for new entries as shown in http://forums.jiwa.com.au/viewtopic.php?f=27&t=173.

[Mike.Sheen]

Hi Stuart,

I can provide you a sample that does all that - but there would need to be a mechanism to identify which entries in the wages system have already been processed to prevent double posting. To achieve this, you could store in a custom field on the journal a unique identifier from the wages system (pay run number or something), so identifying journals yet to be processed would be done by finding all the wages system entries where the unique identifier was not in the Jiwa journals.

The notification to a user who may not be logged in at the time could be done by creating a To-Do notification in Jiwa, or by simply sending an email.

So, my initial approach would be to create a plugin which:

1. Creates a custom field on the journal set "Pay Run No." or "Pay Run ID" or equivalent
2. On a scheduled basis, query the wages database to find entries not already processed into Jiwa (ie: no matching journals with the "Pay Run No. / ID"). This query would need to use SQL credentials valid for the wages database - ideally you would create a SQL user with read-only access to the relevant tables.
3. Create journal(s)
4. Create a To-Do notifying a Jiwa user of the event

Sound about right?

I'm on leave until the 11th of April, but should be able to provide you with a plugin which does the bulk of the work in the next few days (you'll have to fill in some bits relating to the structures of the wages database).

Mike

[SBarnes]

Hi Mike,

Firstly thanks for the quick response.

Secondly I would be happy to work out the code from a rough example if you can provide it, so I don't know if you need to make if fully functional if this will save you sometime given I am already interrupting your leave, the approach you are suggesting was exactly what I had in mind given that there is a unique pay id produced by the payroll system (unique number increment by each run) although I had been thinking that the id could be held in one of the normal journal fields such as reference but either way would work.

The $ values and Jiwa general ledger account numbers would be held within the payroll system so once the journal was setup its just a matter of filling in the lines with a debit and the account number and then either offsetting the total to a clearing account or bank account, I'm looking at if this can come from the payroll system as well.

One other option I was also thinking of was to set the journal up as pending so that the notification to the user could advise them that they would need to go and approve the journal as posting it straight in could be a little dangerous.

Any help you can give would be greatly appreciated.

[Mike.Sheen]

Hi Stuart,

My apologies for the delayed reply - things got away from me the last week.

Below is a code snippet you should put in the ScheduledExecutionPlugin class of your plugin. You will need to add a reference to JiwaJournalSets.dll if your plugin does not already have that reference.

The example is not tested, but should serve as a guide on how to connect to the wages database from within the plugin, query the wages tables, construct a pending journal and send a todo notification. If you have any further questions or issues, please don't hesitate to ask.

Mike

Code: Select all

Public Sub Execute(ByVal Plugin As JiwaApplication.Plugin.Plugin, ByVal Schedule As JiwaApplication.Schedule.Schedule) Implements JiwaApplication.IJiwaScheduledExecutionPlugin.Execute
      Dim WagesSQLConnection As SqlClient.SqlConnection
            
      Try         
         ' Create a connection to the Wages SQL database
         Dim connStringBuilder As New SqlConnectionStringBuilder()
           With connStringBuilder
               .DataSource = "SQLServerName" ' Can also use JiwaApplication.Manager.Instance.Database.ServerName if it is always the same server as the where the jiwa db resides
               .InitialCatalog = "WagesDB"           
               .UserID = "SQLUser"
               .Password = "password"
               .MultipleActiveResultSets = True
               .ApplicationName = "Jiwa Financials"
               .ApplicationIntent = ApplicationIntent.ReadWrite
           End With       
           WagesSQLConnection = New System.Data.SqlClient.SqlConnection(connStringBuilder.ToString)
         WagesSQLConnection.Open
         
         Dim WagesSQL As String = "SELECT ?"  ' This is the query to get the data from the wages database.
         
         Using WagesSQLCmd As SqlCommand = New SqlCommand(WagesSQL, WagesSQLConnection)         
            Dim WagesSQLReader As SqlDataReader = WagesSQLCmd.ExecuteReader()
            
            Do While WagesSQLReader.Read = True                           
               ' Use WagesSQLReader(0) to get first field from the query results, WagesSQLReader(1) to get the second, etc
               
               Dim journalSet As JiwaJournalSets.JournalSet = JiwaApplication.Manager.Instance.BusinessLogicFactory.CreateBusinessLogic(Of JiwaJournalSets.JournalSet)(Nothing)
               journalSet.CreateNew
               journalSet.SetType = JiwaJournalSets.JournalSet.SetTypes.Pending
               journalSet.PostedDate = Now
               journalSet.Description = "Test wages journal set 123"
                     
               Dim journalLine As JiwaJournalSets.Line    
               
               journalLine = New JiwaJournalSets.Line
               journalLine.GLAccount.ReadRecordFromAccountNo("4140-200-00") ' Wages NSW
               journalLine.DebitAmount = 100
               journalLine.Reference = "123"
               journalLine.Remark = "Wages"      
               journalSet.Lines.Add(journalLine)
               
               journalLine = New JiwaJournalSets.Line
               journalLine.GLAccount.ReadRecordFromAccountNo("4140-200-00") ' Superannuation NSW
               journalLine.DebitAmount = 115
               journalLine.Reference = "123"
               journalLine.Remark = "Superannuation"      
               journalSet.Lines.Add(journalLine)
                     
               journalLine = New JiwaJournalSets.Line
               journalLine.GLAccount.ReadRecordFromAccountNo("6000-200-00") ' Cash On Hand NSW
               journalLine.CreditAmount = 215
               journalLine.Reference = "123"
               journalLine.Remark = "Cash on Hand"      
               journalSet.Lines.Add(journalLine)
               
               journalSet.Save()
               
               Dim toDo As JiwaApplication.JiwaToDos.ToDo = JiwaApplication.Manager.Instance.BusinessLogicFactory.CreateBusinessLogic(Of JiwaApplication.JiwaToDos.ToDo)(Nothing)
               toDo.CreateNew
               toDo.AssignedTo.ReadRecordByUsername("mikes")
               toDo.Body = String.Format("Wages Journal Set {0} created.", journalSet.SetNo)
               toDo.ReminderPredefinedSetting = JiwaApplication.JiwaToDos.ToDo.ReminderPredefinedSettingType.WhenDue
               toDo.ReminderSpecificDateTime = Now
               toDo.ReminderEnabled = True               
               toDo.Save()
            Loop
            
            If Not WagesSQLReader Is Nothing Then
                    WagesSQLReader.Close()
                    WagesSQLReader = Nothing
                End If
            
         End Using
      Finally
         If Not (WagesSQLConnection Is Nothing) Then
               If WagesSQLConnection.State <> System.Data.ConnectionState.Closed Then
                   WagesSQLConnection.Close()
               End If
           End If
      End Try
    End Sub

[SBarnes]

Thanks Mike,

This should be more than enough to get started.