E-mail notification for "Set IC Transaction Status"

[PeterFu]

Hi,

I am looking for a Business Rule (DataQualityEventhandler) to send e-mail notifications when "Set IC Transaction Status" is used. I know how to send e-mail when the "Set IC Transaction Status" is used. The challenge is to get the comment and other information from the ICMatchStatus table and to pick up the e-mail addresses to send to. So, I want to check for the entity in the ICMatchStatus table and all users with access to that entity should then receive the e-mail notification. I want to use the e-mail on the users to create the e-mail list for sending e-mails.

It's more or less the same as with the journals. If a journal is for example created, then an e-mail is sent to the users that can approve the journal. The e-mail will then also contain the journal they are suppose to approve and/or post. This I have, but the ICTransactionStatus is a bit 

Regards,

Peter

[ckattookaran]

This might need some collaboration as I've no clue what you are talking about ;).

So when you use BREventOperationType.DataQuality.ICTransStatus.AddICTransactionStatus or the Finalize one, can you check what the arg inputs are? Some of those inputs could be lead you to create the where clause that help in getting the data from the ICMatchStatus table.

[PeterFu]

My client wants an e-mail notification when IC Transactions Status are used. See print screen.

See print screen below to see the BR I am testing with

This is working with a hard-coded e-mail address and content for the e-mail, but I want to have the information from the comment and be able to send it to the correct users. Tom created something for Journals that I have been using, so I am looking for something similar to that, but for this "Set IC Transaction Status".

[ckattookaran]

Totally untested. But I think this will do it.

#Region "SetICStatus Helpers"

		Private Sub XFR_FinalizeAddICTransactionStatus(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal args As DataQualityEventHandlerArgs)

			Try
				
				Dim objICMatchStatusInfo As ICMatchStatusInfo = DirectCast(args.Inputs(0), ICMatchStatusInfo)
				
				If objICMatchStatusInfo.TransactionState = 40 'Finalized
					Dim intEntityID As Integer = objICMatchStatusInfo.ICMatchTransaction.EntityId
					Dim entMbr As Member =BRApi.Finance.Members.ReadMemberNoCache(si, DimTypeId.Entity, intEntityID)
					Dim readWriteGroup As GroupInfo = BRApi.Security.Admin.GetGroupInfoEx(si, entMbr.ReadWriteDataGroupUniqueID).GroupInfo
					Dim readWriteGroup2 As GroupInfo = BRApi.Security.Admin.GetGroupInfoEx(si, entMbr.ReadWriteDataGroupUniqueID2).GroupInfo
					Dim usrList As New List(Of UserInfo)
					Me.GetUsersInGroup(si, readWriteGroup.Group.UniqueID, usrList)
					Me.GetUsersInGroup(si, readWriteGroup2.Group.UniqueID, usrList)
					
					For Each userSummary As UserInfo In usrList.Distinct()
						'Send the email
						Me.CreateMessageAndSendMail(si, objICMatchStatusInfo, userSummary)
					Next
					
				End If	
								
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Sub
		
		Private Sub GetUsersInGroup(ByVal si As SessionInfo, ByVal groupID As Guid, ByRef usrList As list(Of UserInfo))
		
			Try
				Using dbFwConn As DbConnInfoFW = BRApi.Database.CreateFrameworkDbConnInfo(si)
					Dim sql As New Text.StringBuilder
					sql.AppendLine("WITH ChildUser as
					  (
					  SELECT ChildKey,ChildIsAUser FROM SecGroupChild
					  WHERE ChildIsAUser = 0
					  UNION ALL
					  SELECT b.ChildKey,b.ChildIsAUser FROM SecGroupChild as b
					  INNER JOIN ChildUser as a on a.ChildKey= b.GroupKey
					)
					Select distinct ChildKey from (
					SELECT distinct ChildKey FROM ChildUser
					where ChildIsAUser <> 0
					UNION ALL 
					SELECT distinct ChildKey FROM SecGroupChild
					WHERE ChildIsAUser = 1)p")
					Dim userDT As DataTable = BRApi.Database.ExecuteSqlUsingReader(dbFwConn, sql.ToString, True)
					If userDT.Rows.Count > 0 Then
						For Each userDR As DataRow In userDT.rows
							usrList.Add(BRApi.Security.Admin.GetUser(si, DirectCast(userDR("ChildKey"),Guid)))
						Next	
					End If	
				
				End Using
				
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Sub
		
		Private Sub CreateMessageAndSendMail(ByVal si As SessionInfo, objICMatchStatus As ICMatchStatusInfo, userSummary As UserInfo)
			'------------------------------------------------------------------------------------------------------------
			'Reference Code: 	CreateMessageAndSendMail 
			'
			'Description:		Create the email message body and execute the "Send Mail" function.
			'
			'------------------------------------------------------------------------------------------------------------		
			Try
				Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)					
					Dim emailInfo As New Text.StringBuilder
					emailInfo.AppendLine("To: " & userSummary.User.Email)
					
					'Create the message title
					Dim messageTitle As New Text.StringBuilder
					messageTitle.Append("IC Match for [")
					messageTitle.Append(objICMatchStatus.ICMatchTransaction.EntityId)
					messageTitle.Append(", ")
					messageTitle.Append(objICMatchStatus.ICMatchTransaction.PartnerId)
					messageTitle.Append("] is ready for you to review.")
					messageTitle.AppendLine("")	
					
					'Create the message body
					Dim messageBody As New Text.StringBuilder
					messageBody.AppendLine("IC Match Information:")				
					messageBody.AppendLine("User Name........... " & objICMatchStatus.UserName)					
					messageBody.AppendLine("Time Completed...... " & objICMatchStatus.TimeStamp)										
					messageBody.AppendLine("Comments............ " & objICMatchStatus.Comments)								
					messageBody.AppendLine("")
					
					'Test the email by writing a log message
					ErrorHandler.LogMessage(si, emailInfo.ToString & vbcrlf &  messageTitle.ToString & vbcrlf &  messageBody.ToString)				
				
					'Send an email message
					'Me.SendMail(si, userSummary.User.Email, messageTitle.ToString, messageBody.ToString)
					
				End Using
				
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Sub

#End Region

[ckattookaran]

Also getting the users from a child group using the SQL CTE was tested using a small set. It is a recursive query and gets you all users from child groups as well.

[PeterFu]

This is what I am looking for. It's not working as is without some changes. The send e-mail syntax is the old one, so I replaced it with this, BRApi.Utilities.SendMail(si, "OneStreamEmail", distributionList, subject, messageBody.ToString, fileAttachmentPaths). It seems to be some issues with the e-mail subject/ title, so I just changed it to one line with text combined with system information, not using the append function. I also removed the If-statement to only run for "Finalized" status since I want a notification for all status updates.

Thanks a lot for this, will save me for a lot work trying to write all this from scratch,

Regards,

Peter

[ckattookaran]

You can get the member name using the ids for the title. I was lazy 🙂 I used the existing one in GolfStream for the emailing part.

[PeterFu]

I saw that, so I will update it to look up the name.

I saw your reply now regarding Sql and child groups. Is the current file not giving us all users with access to the active entity (I did not look much at the user part)? 

[ckattookaran]

I'm getting the entityid, then go and look up the ReadWriteGroup and ReadWriteGroup2 of the entity. Use those two groups and get all the users in it or the users in the child groups. It is a recursive query that gets all users from all the groups. I tested it for a small set only. I've not used this anywhere else except to write it up when I saw your request.

[PeterFu]

Hi Celvin,

I will need to test it properly at the client's environment, but it looks good so far. I noticed one more thing. The intention is to send an e-mail to the partner when an entity is updating the Set IC Transaction status, so I changed this line from EntityId to PartnerId, "Dim intEntityID As Integer = objICMatchStatusInfo.ICMatchTransaction.PartnerId".  

Again, thanks a lot for your effort.

Peter