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