-- ============================================================================= -- EIP Mail Approval Setup -- Migration : 20260613_EIPMailApproval_Setup.sql -- Purpose : Wire Pure-EIP one-click approval buttons into the leave workflow -- notification email, using the EXISTING GENERATELEAVEMAIL template -- (-1499999994) which already contains ##APPROVE_URL## / ##REJECT_URL## -- / ##RETURN_URL## in its BODY. -- -- Flow after this migration: -- 1. Leave saved → WorkflowBLL.StartWorkflow creates a TWORKFLOWTASK -- 2. WorkflowBLL publishes EventTypeId = -1399999789 (WF_TASK_ASGN) -- Payload : { WorkflowTaskId, AssigneeUserId, WorkflowId, EntityPayload } -- ContextJson: { "Bag": { ...FactsJson... } } ← carries "MailId" for recipient -- 3. EventSubBLL picks up the event, finds MACTION -1399999789 (DirectActionId=1) -- 4. Extracts ContextId = WorkflowTaskId, AssigneeUserId from payload -- 5. EmailActionHandler generates HMAC-signed tokens for APPROVE / REJECT / RETURN -- 6. Replaces ##APPROVE_URL## etc. in GENERATELEAVEMAIL body with /Action/Execute?token=… -- 7. Email sent to MailId (reporting manager) from ContextBag -- 8. Manager clicks Approve → token validated → POST /WorkFlow/WorkFlowActions -- { "Items": [{ "TaskId": , "Action": 1 }] } -- -- Pre-requisites (run first): -- GOP_DDL.sql — creates MDIRECTACTION, MDIRECTACTIONDETAIL, TDIRECTACTIONTOKEN, -- and adds MACTION.DIRECTACTIONID column. -- Seeds MDIRECTACTION(1) = WFAPPROVAL with -- CONTEXTIDFIELD='WorkflowTaskId', ASSIGNEEUSERIDFIELD='AssigneeUserId' -- Seeds MDIRECTACTIONDETAIL(1/2/3) → /WorkFlow/WorkFlowActions POST -- -- Idempotent: all blocks guarded by IF NOT EXISTS / IF EXISTS checks. -- ============================================================================= -- ───────────────────────────────────────────────────────────────────────────── -- §1 : MEVENTTYPE — WORKFLOW_TASK_ASSIGNED (EventTypeId = -1399999789) -- Sits in the framework workflow event-type block alongside: -- APPROVEWORKFLOW = -1399999792 -- REJECTWORKFLOW = -1399999791 -- RESENDWORKFLOW = -1399999790 -- WF_TASK_ASGN = -1399999789 ← this migration -- ───────────────────────────────────────────────────────────────────────────── IF NOT EXISTS (SELECT 1 FROM MEVENTTYPE WHERE EVENTTYPEID = -1399999789) BEGIN INSERT INTO MEVENTTYPE (EVENTTYPEID, EVENTTYPECODE, EVENTTYPENAME, EVENTCATEGORY, ISBIZDOMAIN, ISAUDIT, RETENTIONDAYS, TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, STATUS, VERSION, SORTORDER, SOURCETYPE) VALUES (-1399999789, 'WF_TASK_ASGN', 'Workflow Task Assigned', 1 /*Business*/, 1, 1, 1095 /*3 years*/, -1 /*global*/, -1, GETUTCDATE(), -1, GETUTCDATE(), 1, 0, 100, 1 /*Framework*/); PRINT 'MEVENTTYPE: WF_TASK_ASGN (-1399999789) inserted.'; END ELSE PRINT 'MEVENTTYPE: WF_TASK_ASGN (-1399999789) already exists — skipped.'; GO -- ───────────────────────────────────────────────────────────────────────────── -- §2 : MACTION — Workflow Task Assignment notification (EIP approval email) -- -- ACTIONID = -1399999789 -- ACTIONTYPE = 0 (Email) -- TEMPLATEID = -1499999994 (GENERATELEAVEMAIL — already has ##APPROVE_URL## etc.) -- SENDTO = 'MailId' → resolved from ContextBag (DeliveryType 12 first-checks bag) -- TODELIVERYTYPE = 12 → EnrichQualifier: checks ContextBag["MailId"] first, -- falls back to running "MailId" as a QueryCode -- CONDITIONEXPRESSION = NULL → always fire for task-assigned events -- DIRECTACTIONID = 1 → WFAPPROVAL group (seeded by GOP_DDL.sql) -- -- Matches the recipient-resolution pattern of the existing leave notification action -- (-1399999999), except it fires from EventTypeId -1399999789 and has DirectActionId=1 -- so EmailActionHandler generates signed Approve / Reject / Return token URLs. -- ───────────────────────────────────────────────────────────────────────────── IF NOT EXISTS (SELECT 1 FROM MACTION WHERE ACTIONID = -1399999789) BEGIN INSERT INTO MACTION (ACTIONID, ACTIONDESCRIPTION, ACTIONTYPE, SENDTO, TODELIVERYTYPE, TOCONTENTTYPE, REPLYTO, MAILCC, MAILCCDELIVERYTYPE, MAILBCC, MAILBCCDELIVERYTYPE, TEMPLATEID, WEBSERVICEID, REPORTID, CONDITIONEXPRESSION, DIRECTACTIONID, STATUS, VERSION, SORTORDER, SOURCETYPE, TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON) VALUES (-1399999789, 'Workflow Task Assignment — EIP Approval Email', 0 /*Email*/, 'MailId', 12 /*EnrichQualifier — checks ContextBag["MailId"]*/, 0 /*Direct*/, 'noreply@myunisoft.com', 'sugesh@myunisoft.com', 0, 'hr@myunisoft.com', 0, -1499999994 /*GENERATELEAVEMAIL*/, -1, -1, NULL, 1 /*WFAPPROVAL — MDIRECTACTION.DIRECTACTIONID seeded by GOP_DDL.sql*/, 1, 0, 100, 1 /*Framework*/, -1 /*global*/, -1, GETUTCDATE(), -1, GETUTCDATE()); PRINT 'MACTION: -1399999789 inserted (GENERATELEAVEMAIL + DirectActionId=1).'; END ELSE BEGIN -- Row already exists: ensure TEMPLATEID and DIRECTACTIONID are correct. UPDATE MACTION SET TEMPLATEID = -1499999994, DIRECTACTIONID = 1, TODELIVERYTYPE = 12, TOCONTENTTYPE = 0 WHERE ACTIONID = -1399999789 AND (TEMPLATEID <> -1499999994 OR DIRECTACTIONID <> 1); PRINT 'MACTION: -1399999789 already exists — TEMPLATEID + DIRECTACTIONID ensured correct.'; END GO -- ───────────────────────────────────────────────────────────────────────────── -- §3 : MEVENTTYPEACTION — group header linking WF_TASK_ASGN to its actions -- EVENTTYPEACTIONID = -1399999789 (mirrors EventTypeId for easy tracing) -- ───────────────────────────────────────────────────────────────────────────── IF NOT EXISTS (SELECT 1 FROM MEVENTTYPEACTION WHERE EVENTTYPEACTIONID = -1399999789) BEGIN INSERT INTO MEVENTTYPEACTION (EVENTTYPEACTIONID, EVENTTYPEID, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID, SORTORDER, VERSION, SOURCETYPE) VALUES (-1399999789, -1399999789 /*WF_TASK_ASGN*/, 1 /*Active*/, -1, GETUTCDATE(), -1, GETUTCDATE(), -1 /*global*/, 100, 0, 1 /*Framework*/); PRINT 'MEVENTTYPEACTION: -1399999789 inserted.'; END ELSE PRINT 'MEVENTTYPEACTION: -1399999789 already exists — skipped.'; GO -- ───────────────────────────────────────────────────────────────────────────── -- §4 : MEVENTTYPEACTIONDETAIL — binds MACTION -1399999789 to the group -- ISACTIVE = 0 (0 = active per framework convention) -- ───────────────────────────────────────────────────────────────────────────── IF NOT EXISTS ( SELECT 1 FROM MEVENTTYPEACTIONDETAIL WHERE EVENTTYPEACTIONID = -1399999789 AND ACTIONID = -1399999789) BEGIN INSERT INTO MEVENTTYPEACTIONDETAIL (EVENTTYPEACTIONID, ACTIONID, ISACTIVE, SLNO, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON) VALUES (-1399999789, -1399999789, 0 /*active*/, 1, -1, GETUTCDATE(), -1, GETUTCDATE()); PRINT 'MEVENTTYPEACTIONDETAIL: MACTION -1399999789 linked to WF_TASK_ASGN.'; END ELSE PRINT 'MEVENTTYPEACTIONDETAIL: link already exists — skipped.'; GO -- ───────────────────────────────────────────────────────────────────────────── -- §5 : VERIFY MDIRECTACTION(1) — seeded by GOP_DDL.sql -- Confirm the WFAPPROVAL row exists so the migration is self-documenting. -- ───────────────────────────────────────────────────────────────────────────── IF NOT EXISTS (SELECT 1 FROM MDIRECTACTION WHERE DIRECTACTIONID = 1) BEGIN RAISERROR ( 'MDIRECTACTION(1) = WFAPPROVAL is missing. Run GOP_DDL.sql first.', 16, 1); END ELSE BEGIN PRINT 'MDIRECTACTION(1) = WFAPPROVAL confirmed present (ContextIdField=WorkflowTaskId, AssigneeUserIdField=AssigneeUserId).'; PRINT 'MDIRECTACTIONDETAIL APPROVE/REJECT/RETURN buttons confirmed seeded by GOP_DDL.sql.'; END GO -- ============================================================================= -- DONE -- ============================================================================= -- What fires when a leave is saved: -- -- A) EventTypeId -1399999781 → MACTION -1399999999 (DirectActionId=-1) -- Plain notification email to reporting manager. No approval buttons. -- Template: GENERATELEAVEMAIL (-1499999994). Already working. -- -- B) EventTypeId -1399999789 → MACTION -1399999789 (DirectActionId=1) ← THIS MIGRATION -- Approval email to reporting manager WITH signed one-click buttons. -- Template: GENERATELEAVEMAIL (-1499999994). Buttons wired by EmailActionHandler. -- ContextId = WorkflowTaskId (from payload, MDIRECTACTION.CONTEXTIDFIELD) -- AssigneeId = AssigneeUserId (from payload, MDIRECTACTION.ASSIGNEEUSERIDFIELD) -- MailId = from ContextBag (FactsJson wrapped as {"Bag":{...}} by WorkflowBLL) -- -- appsettings.json must have: -- "DirectAction": { "TokenSecret": "<32+ char secret>" } -- "SysJobSettings": { "ServiceBaseUrl": "https://your-framework-host" } -- ============================================================================= PRINT '20260613_EIPMailApproval_Setup.sql completed.'; GO