Using user messaging¶
This custom procedure is executed from the Context menu. example is executing it from SSMS, but it can also be executed from within M-Files.
DECLARE
@ProcessBatch_ID INT,
@Output NVARCHAR(1000);
EXEC Custom.DoCMAsyncAction
@ID = 20,
@ProcessBatch_ID = @ProcessBatch_ID OUTPUT,
@Output = @Output OUTPUT,
@WriteToMFiles = 1,
@Debug = 0;
SELECT
@ProcessBatch_ID AS ProcessBatch_ID;
SELECT
@Output AS outputmessage; --output message is only produced with sync procedures
-- Note that a user message was automatically created in table MFUserTable
SELECT
*
FROM
dbo.MFUserMessages AS mum
WHERE
mum.Mfsql_Process_Batch = 147;
-- Update Class Table: Completed Class Name: Customer #Records: 1 #Updated: 1 Process Batch#: 1330 Started On: Aug 31 2017 7:59PM Duration: 00:00:01
-- this automation is based on the executing spMFProcessBatch_Upsert with a processtype of 'Message'. A trigger in this the ProcessBatch Table inserts the message in the userMessage table.
EXEC dbo.spMFInsertUserMessage
@ProcessBatch_ID = 147, @UserMessageEnabled = 1
-- the following procedure can be used to generate user messages for different layouts
DECLARE
@MessageOUT NVARCHAR(4000),
@MessageForMFilesOUT NVARCHAR(4000),
@EMailHTMLBodyOUT NVARCHAR(MAX);
EXEC dbo.spMFResultMessageForUI
@Processbatch_ID = 147,
@MessageOUT = @MessageOUT OUTPUT,
@MessageForMFilesOUT = @MessageForMFilesOUT OUTPUT,
@GetEmailContent = 1,
@EMailHTMLBodyOUT = @EMailHTMLBodyOUT OUTPUT;
SELECT
@MessageOUT AS Context menu message,
@MessageForMFilesOUT AS MFSQL Message Property ,
@EMailHTMLBodyOUT AS HTML formatted message
--
/*
Update Class Table: Completed\nClass Name: Customer\n#Records: 1\n#Updated: 1\nProcess Batch#: 1330\nStarted On: Aug 31 2017 7:59PM\nDuration: 00:00:01
*/
SELECT
@MessageForMFilesOUT AS MessageForMFilesOUT;
/*
Update Class Table: Completed Class Name: Customer #Records: 1 #Updated: 1 Process Batch#: 1330 Started On: Aug 31 2017 7:59PM Duration: 00:00:01
*/
SELECT
@EMailHTMLBodyOUT AS EMailHTMLBodyOUT;
/*
<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <style type="text/css"> div {line-height: 100%;} body {-webkit-text-size-adjust:none;-ms-text-size-adjust:none;margin:0;padding:0;} body, #body_style {min-height:1000px;font: 10pt Verdana, Geneva, Arial, Helvetica, sans-serif;} p {margin:0; padding:0; margin-bottom:0;} h1, h2, h3, h4, h5, h6 {color: black;line-height: 100%;} table { border-collapse: collapse; border: 1px solid #3399FF; font: 10pt Verdana, Geneva, Arial, Helvetica, sans-serif; color: black; padding:5; border-spacing:1; border:0; } table caption {font-weight: bold;color: blue;} table td, table th, table tr,table caption { border: 1px solid #eaeaea;border-collapse:collapse;vertical-align: top; } table th {font-weight: bold;font-variant: small-caps;background-color: blue;color: white;vertical-align: bottom;} </style> </head> <body><div id="body_style" ><table><th>M-Files Vault: MFSQL_Samplevault_32138</th><tr><td>Update Class Table: Completed</td></tr><tr><td>Class Name: Customer</td></tr><tr><td>#Records: 1</td></tr><tr><td>#Updated: 1</td></tr><tr><td>Process Batch#: 1330</td></tr><tr><td>Started On: Aug 31 2017 7:59PM</td></tr><tr><td>Duration: 00:00:01</td></tr></table></div></body></html>
*/
/*
The email can be produced by applying the context menu and the processbatch_ID of the process.
*/
SELECT MAX(mpb.ProcessBatch_ID) AS ProcessBatch_ID FROM dbo.MFProcessBatch AS mpb
DECLARE @RecipientEmail NVARCHAR(100);
DECLARE @RecipientFromMFSettingName NVARCHAR(100);
DECLARE @ContextMenu_ID INT;
SELECT
@ContextMenu_ID = ID
FROM
MFContextMenu
WHERE
ActionName = 'Action Type 1 Async';
SELECT
@RecipientEmail = mla.EmailAddress
FROM
dbo.MFContextMenu AS mcm
INNER JOIN
dbo.MFLoginAccount AS mla
ON mcm.Last_Executed_By = mla.MFID
WHERE
mcm.ID = @ContextMenu_ID;
SELECT @RecipientFromMFSettingName = CAST(value as nvarchar(100)) FROM dbo.MFSettings AS ms
WHERE name = 'SupportEmailRecipient'
EXEC dbo.spMFProcessBatch_EMail
@ProcessBatch_ID = 1565,
@RecipientEmail = @RecipientEmail,
@RecipientFromMFSettingName = @RecipientFromMFSettingName,
@ContextMenu_ID = @ContextMenu_ID,
@Debug = 1;
GO
-- SELECT TOP 2 * FROM msdb.dbo.sysmail_mailitems AS sm ORDER BY sm.mailitem_id desc
-- -----------------------------------------------------------
-- EXAMPLE PROCEDURE TO SEND ASYNC EMAIL
-- this example is dependend on MFSQL Context Menu with sample scripts being installed
-- -----------------------------------------------------------
DECLARE
@ProcessBatch_ID INT,
@Output NVARCHAR(1000),
@ID INT;
SET @ID =
(
SELECT
ID
FROM
MFContextMenu AS cm
WHERE
Action = 'custom.DoCMAsyncAction'
);
EXEC Custom.DoCMAsyncAction
@ID = @ID,
@ProcessBatch_ID = @ProcessBatch_ID OUTPUT, -- int
@Output = @Output OUTPUT, -- nvarchar(1000)
@WriteToMFiles = 1, -- bit
@Debug = 0; -- smallint
SELECT
@Output;
SELECT
*
FROM
dbo.MFProcessBatchDetail AS mpbd
WHERE
mpbd.ProcessBatch_ID = @ProcessBatch_ID;
GO
-- -----------------------------------------------------------
-- LOGGING AND MESSAGES - backtrack from MFSQL Process Batch in M-Files
-- -----------------------------------------------------------
SELECT
MFSQL_Process_Batch,
Update_ID,
*
FROM
mfcustomer
WHERE
mfsql_process_batch IS NOT NULL;
-- Show UpdateHistory: note the update_id and MFSQL_Process_Batch for a record from the above select and replace it in the parameter
-- Execute from here
DECLARE @Update_ID INT = 6;
DECLARE @ProcessBatch_ID INT = 19;
SELECT
*
FROM
MFUpdateHistory
WHERE
Id = @Update_ID;
EXEC dbo.spMFUpdateHistoryShow
@Update_ID = @Update_ID, -- int
@IsSummary = 1, -- smallint
@UpdateColumn = 0, -- int
@Debug = 0; -- smallint
EXEC dbo.spMFUpdateHistoryShow
@Update_ID = @Update_ID, -- int
@IsSummary = 0, -- smallint
@UpdateColumn = 7, -- int
@Debug = 0; -- smallint
EXEC dbo.spMFUpdateHistoryShow
@Update_ID = @Update_ID, -- int
@IsSummary = 0, -- smallint
@UpdateColumn = 3, -- int
@Debug = 0; -- smallint
-- show batch Process result
SELECT
*
FROM
MFProcessBatch
WHERE
ProcessBatch_ID = @ProcessBatch_ID;
-- show process batch detail
SELECT
*
FROM
MFProcessBatchDetail
WHERE
ProcessBatch_ID = @ProcessBatch_ID;
-- show process message for User
SELECT
*
FROM
dbo.MFUserMessages AS mum
WHERE
mum.Mfsql_Process_Batch = 1143;
-- End Here
/*
UPDATE Class Table: Completed
Class Name: Customer
#Records: 1 #Updated: 1
Process Batch#: 1143
Started On: Aug 24 2017 7:59PM Duration: 00:00:02
*/
GO