mp-workflows/✅ Workflow Collection.sql at main · river-creative/mp-workflows · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
-- ✅ Flags
(SELECT TOP 1
  CASE
    WHEN PM.Milestone_ID = 47 THEN N'🚩 Denied'
    WHEN PM.Milestone_ID = 56 THEN N'🚩 On Hold'
    WHEN PM.Milestone_ID = 66 THEN N'🚩 Removed'
    ELSE NULL
  END
 FROM Participant_Milestones PM WHERE PM.Participant_ID = Contacts.Participant_Record AND PM.Milestone_ID IN (47, 56, 66)) AS [Flags],

-- ✨ Flags
(SELECT STRING_AGG(M.Milestone_Title, ',') FROM Milestones M INNER JOIN Participant_Milestones PM ON PM.Milestone_ID = M.Milestone_ID
 WHERE PM.Participant_ID = Contacts.Participant_Record AND PM.Milestone_ID IN (47, 56, 66)) AS [Flags],

------------------------------

-- ✅ Members
 Participant_Record_Table_Member_Status_ID_Table.[Member_Status] +
CASE
  WHEN EXISTS (SELECT 1 FROM Group_Participants GP
  WHERE GP.Participant_ID = Contacts.Participant_Record AND GP.Group_ID IN (499, 491))
  THEN ' (' +
    CONCAT_WS(',',
      CASE WHEN EXISTS(SELECT 1 FROM Group_Participants GP WHERE GP.Participant_ID = Contacts.Participant_Record AND GP.Group_ID = 499) THEN 'S' END,
      CASE WHEN EXISTS(SELECT 1 FROM Group_Participants GP WHERE GP.Participant_ID = Contacts.Participant_Record AND GP.Group_ID = 491) THEN 'C' END
    ) + ')'
  ELSE ''
END AS [Member],

-- ✨ Members
Participant_Record_Table_Member_Status_ID_Table.[Member_Status] + ' (' + (SELECT STRING_AGG(Group_Code, ',')
  FROM Groups G INNER JOIN Group_Participants GP ON GP.Group_ID = G.Group_ID
  WHERE GP.Participant_ID = Contacts.Participant_Record AND GP.Group_ID IN (499, 491,490,536)) + ')' AS [Members],

-- ✅ Participant Statuses
(SELECT CASE WHEN Participant_Record_Table.Member_Status_ID IS NOT NULL
  THEN Participant_Record_Table_Member_Status_ID_Table.Member_Status
  ELSE Participant_Record_Table_Participant_Type_ID_Table.Participant_Type END ) + ' (' + ISNULL((SELECT STRING_AGG(Group_Code, ', ')
  FROM Groups G INNER JOIN Group_Participants GP ON GP.Group_ID = G.Group_ID
  WHERE GP.Participant_ID = Contacts.Participant_Record AND GP.Group_ID IN (499, 491,490,536)), '') + ')' AS [Participant],

-- ✨ Participant Statuses v2
(SELECT CASE WHEN Participant_Record_Table.Member_Status_ID IS NOT NULL
  THEN Participant_Record_Table_Member_Status_ID_Table.Member_Status
  ELSE Participant_Record_Table_Participant_Type_ID_Table.Participant_Type END ) + ' (' + ISNULL((SELECT STRING_AGG(Group_Code, ', ')
  FROM Groups G INNER JOIN Group_Participants GP ON GP.Group_ID = G.Group_ID
  WHERE GP.Participant_ID = Contacts.Participant_Record AND GP.Group_ID IN (499, 491,490,536)), '') + ')' AS [Participant],

-- ✨ Participant Statuses v3
(SELECT COALESCE(Participant_Record_Table_Member_Status_ID_Table.Member_Status, Participant_Record_Table_Participant_Type_ID_Table.Participant_Type) +
  COALESCE((SELECT ' (' + STRING_AGG(Group_Code, ', ') + ')' FROM Groups G JOIN Group_Participants GP ON GP.Group_ID=G.Group_ID
    WHERE GP.Participant_ID=Contacts.Participant_Record AND GP.Group_ID IN (499,491,490,536)
  ),'')) AS [Participant]


-- ✅ BGC with link
(SELECT TOP 1
  CASE
    WHEN All_Clear = 'true' THEN N'✅ Clear'
    WHEN All_Clear = 'false' THEN N'🚩 Flags'
    WHEN All_Clear IS NULL THEN '<a style="text-decoration:none;color:#475466;" href="https://mp.revival.com/mp/292-2739/'+ CONVERT(varchar(10), Contacts.Contact_ID) +'/511/'+ CONVERT(varchar(10), Background_Check_ID) + N'">⏳ Pending</a>'
  END
  FROM Background_Checks BGC
  WHERE BGC.Contact_ID = Contacts.Contact_ID
  ORDER BY Background_Check_ID DESC) AS [BGC Status],


(SELECT COALESCE((
    SELECT TOP 1
      CASE
        WHEN All_Clear = 'true' THEN N'✅ Clear'
        WHEN All_Clear = 'false' THEN '<a style="text-decoration:none;color:#475466;" href="https://mp.revival.com/mp/292-2739/'+ CONVERT(varchar(10), Contacts.Contact_ID) +'/511/'+ CONVERT(varchar(10), Background_Check_ID) + N'">🚩 Flags</a>'
        WHEN All_Clear IS NULL THEN N'⏳ Processing'
      END
    FROM Background_Checks BGC
    WHERE BGC.Contact_ID = Contacts.Contact_ID
    ORDER BY Background_Check_ID DESC
  ), N'⚠️ Needs BGC')
) AS [BGC Status],


-- ✅ Form Status
(SELECT TOP 1 Status_ID FROM Form_Responses WHERE Contact_ID = Contacts.Contact_ID AND Form_ID = 95 ORDER BY Response_Date DESC ) AS [SSE],

(SELECT TOP 1 Status_Name FROM Form_Response_Statuses FRS INNER JOIN Form_Responses FR ON FR.Status_ID = FRS.Status_ID
  WHERE FR.Contact_ID = Contacts.Contact_ID AND FR.Form_ID = 95 ORDER BY FR.Response_Date DESC) AS [Form Status],


-- ✅ Participant Type (Member or Guest)
(SELECT CASE WHEN Participant_Record_Table.Member_Status_ID IS NOT NULL
  THEN Participant_Record_Table_Member_Status_ID_Table.Member_Status
  ELSE Participant_Record_Table_Participant_Type_ID_Table.Participant_Type END ) AS [Participant],


-- ✅ Group Name
(SELECT TOP 1 G.Group_Name
 FROM Group_Participants GP
 INNER JOIN Groups G ON G.Group_ID = GP.Group_ID
 WHERE Contacts.Participant_Record = GP.Participant_ID
 AND GP.Group_ID IN (550)
 AND GP.End_Date IS NULL) AS [Group_Name]

-- 🔬 TESTING


(SELECT CASE WHEN MAX(FR.Form_Response_ID) IS NOT NULL
  THEN '<a style="text-decoration:none;" target="_blank" href="https://mp.revival.com/mp/424/' +
  CONVERT(varchar(10), MAX(FR.Form_Response_ID)) + N'">View Form</a>' END FROM Form_Responses FR
  WHERE FR.Contact_ID = Contacts.Contact_ID AND FR.Form_ID = 95) AS [App],


-- ✅ Photo File
(SELECT TOP 1 Unique_Name FROM dp_Files AS F WHERE F.Table_Name = 'Contacts' AND F.Record_ID = Contacts.Contact_ID) AS [Photo],


-- ✅ Participation Notes JSON
(SELECT TOP 1 PD.Notes
  FROM Participation_Details PD
  INNER JOIN Event_Participants EP ON EP.Event_Participant_ID = PD.Event_Participant_ID
  WHERE EP.Participant_ID = Contacts.Participant_Record
    AND EP.Event_ID = 72500) AS [Participation Notes],

  (SELECT TOP 1 JSON_VALUE(PD.Notes, '$.formResponseID')
  FROM Participation_Details PD
  INNER JOIN Event_Participants EP ON EP.Event_Participant_ID = PD.Event_Participant_ID
  WHERE EP.Participant_ID = Contacts.Participant_Record
    AND EP.Event_ID = 72500) AS [Form_Response_ID]


-- ✅ Answer Field from Registrant Form via Participation Details
(SELECT COALESCE(
    (SELECT TOP 1 Response
    FROM Form_Response_Answers FRA
    INNER JOIN Form_Responses FR ON FR.Form_Response_ID = FRA.Form_Response_ID
    WHERE FR.Contact_ID = Contacts.Contact_ID
    AND FRA.Form_Field_ID = 5558),
  (SELECT TOP 1 Response
    FROM Form_Response_Answers FRA
    WHERE FRA.Form_Response_ID = (
        SELECT TOP 1 CAST(JSON_VALUE(PD.Notes, '$.formResponseID') AS INT)
        FROM Participation_Details PD
        INNER JOIN Event_Participants EP ON EP.Event_Participant_ID = PD.Event_Participant_ID
        WHERE EP.Participant_ID = Contacts.Participant_Record
        AND EP.Event_ID = 72500
    )
    AND FRA.Form_Field_ID = 5566),
    ''
)) AS [Size],