USP_CUSTOMFORMDASHBOARD_UPDATEFORMENTRYCACHE
Definition
Copy
CREATE procedure dbo.USP_CUSTOMFORMDASHBOARD_UPDATEFORMENTRYCACHE
as
begin
set nocount on;
declare @FormInfo table
(
FormEntryID uniqueidentifier,
GradeLevelApplyGuid nvarchar(50),
GradeLevelApplyValue nvarchar(100),
CurrentGradeLevelGuid nvarchar(50),
CurrentGradeLevelValue nvarchar(100),
SchoolYearGuid nvarchar(50),
SchoolYearValue nvarchar(100),
AcademicYearID nvarchar(50), -- EA7AcademicYearsID, note that this field can be blank or null
ChangeDate datetime,
StudentFirstNameGuid nvarchar(50),
StudentFirstNameValue nvarchar(50),
StudentLastNameGuid nvarchar(50),
StudentLastNameValue nvarchar(100),
SubmitDate datetime,
FormGuid uniqueidentifier,
IsCommonForm tinyint
)
--quick insert of all forms we want to work with
insert into @FormInfo
(
FormEntryID,
FormGuid,
ChangeDate,
SubmitDate,
IsCommonForm
)
select
FormEntry.EntryID as FormEntryID,
FormEntry.FormGuid,
StatusAttribute.ChangeDate,
case when StatusAttribute.AttributeType in (2,5) then StatusAttribute.ChangeDate else null end SubmitDate,
case when FormSource.DataSource = '132E3702-0207-4d32-AA6F-7B265B1DC7D0' then 0 --Online Admissions
when FormSource.DataSource = '1927B45A-F7F8-44d7-BF45-66899B8C7D43' then 0 --Online Reenrollment
else 1 end
from dbo.CustomFormEntry FormEntry
inner join dbo.CustomFormEntryAttribute StatusAttribute on FormEntry.EntryID = statusAttribute.EntryID
inner join dbo.CustomFormDatasource FormSource on FormEntry.FormGuid = FormSource.FormGuid
left join dbo.CUSTOMFORMDASHBOARDFORMENTRY on FormEntry.EntryID = CUSTOMFORMDASHBOARDFORMENTRY.CUSTOMFORMENTRYID
where FormEntry.EntryState = statusAttribute.AttributeType
and (CUSTOMFORMDASHBOARDFORMENTRY.CUSTOMFORMENTRYID is null or StatusAttribute.ChangeDate > CUSTOMFORMDASHBOARDFORMENTRY.CHANGEDATE)
--Get extra info needed for Admissions and Reenrollment forms
update @FormInfo
set GradeLevelApplyGuid = MapSchema.MappingSchema.value('(/MappingSchema/MappedComponents/MappedComponent[Type="6"]/MappingItemCollection/MappingItems/MappingItem[FieldID="18"]/Payload/Value)[1]', 'nvarchar(50)'),
CurrentGradeLevelGuid = MapSchema.MappingSchema.value('(/MappingSchema/MappedComponents/MappedComponent[Type="1"]/MappingItemCollection/MappingItems/MappingItem[FieldID="32"]/Payload/Value)[1]', 'nvarchar(50)'),
SchoolYearGuid = MapSchema.MappingSchema.value('(/MappingSchema/MappedComponents/MappedComponent[Type="6"]/MappingItemCollection/MappingItems/MappingItem[FieldID="1000"]/Payload/Value)[1]', 'nvarchar(50)'),
StudentFirstNameGuid = MapSchema.MappingSchema.value('(/MappingSchema/MappedComponents/MappedComponent[Type="1"]/MappingItemCollection/MappingItems/MappingItem[FieldID="30"]/Payload/Value)[1]', 'nvarchar(50)'),
StudentLastNameGuid = MapSchema.MappingSchema.value('(/MappingSchema/MappedComponents/MappedComponent[Type="1"]/MappingItemCollection/MappingItems/MappingItem[FieldID="34"]/Payload/Value)[1]', 'nvarchar(50)')
from @FormInfo FormInfo
inner join dbo.CustomFormMappingSchema MapSchema on FormInfo.FormGuid = MapSchema.FormGuid
where FormInfo.IsCommonForm = 0
-- (Client data was found with random content in SchoolYearGuid, so check that it is 36 chars i.e. a guid.)
delete from @FormInfo
where LEN(SchoolYearGuid) != 36
update @FormInfo
set SchoolYearValue = FormEntry.EntryData.value('(/CustomFormEntry/Responses/Response[FieldKey=lower-case(sql:column("FormInfo.SchoolYearGuid"))]/Values/string)[1]', 'nvarchar(100)')
from @FormInfo FormInfo
inner join dbo.CustomFormEntry FormEntry on FormInfo.FormEntryID = FormEntry.EntryID
where FormInfo.SchoolYearGuid is not null
-- Use custom form element to get the EA7AcademicYearID
update @FormInfo
set AcademicYearID = Properties.value('(/CustomFormElementProperties/TableEntries/FieldItem[Description=sql:column("FormInfo.SchoolYearValue")]/Value)[1]', 'nvarchar(50)')
from @FormInfo FormInfo
inner join dbo.CustomFormElement on FormInfo.SchoolYearGuid = CustomFormElement.[Guid]
where FormInfo.SchoolYearGuid is not null
update @FormInfo
set
GradeLevelApplyValue = FormEntry.EntryData.value('(/CustomFormEntry/Responses/Response[FieldKey=lower-case(sql:column("FormInfo.GradeLevelApplyGuid"))]/Values/string)[1]', 'nvarchar(100)'),
CurrentGradeLevelValue = FormEntry.EntryData.value('(/CustomFormEntry/Responses/Response[FieldKey=lower-case(sql:column("FormInfo.CurrentGradeLevelGuid"))]/Values/string)[1]', 'nvarchar(100)'),
StudentFirstNameValue = FormEntry.EntryData.value('(/CustomFormEntry/Responses/Response[FieldKey=lower-case(sql:column("FormInfo.StudentFirstNameGuid"))]/Values/string)[1]', 'nvarchar(50)'),
StudentLastNameValue = FormEntry.EntryData.value('(/CustomFormEntry/Responses/Response[FieldKey=lower-case(sql:column("FormInfo.StudentLastNameGuid"))]/Values/string)[1]', 'nvarchar(100)')
from @FormInfo FormInfo
inner join dbo.CustomFormEntry FormEntry on FormInfo.FormEntryID = FormEntry.EntryID
where FormInfo.IsCommonForm = 0
merge dbo.CUSTOMFORMDASHBOARDFORMENTRY as [Target]
using (select
FormEntryID,
coalesce(GradeLevelApplyValue, '') as GRADELEVELAPPLY,
coalesce(CurrentGradeLevelValue, '') as CURRENTGRADELEVEL,
coalesce(AcademicYearID, '') as ACADEMICYEARID,
coalesce(StudentFirstNameValue, '') as STUDENTFIRSTNAME,
coalesce(StudentLastNameValue, '') as STUDENTLASTNAME,
ChangeDate as CHANGEDATE,
SubmitDate as SUBMITDATE
from @FormInfo FormInfo) as [Source]
on ([Target].CUSTOMFORMENTRYID = [Source].FormEntryID)
when matched then
update set
[Target].GRADELEVELAPPLY = [Source].GRADELEVELAPPLY,
[Target].CURRENTGRADELEVEL = [Source].CURRENTGRADELEVEL,
[Target].ACADEMICYEARID = [Source].ACADEMICYEARID,
[Target].STUDENTFIRSTNAME = [Source].STUDENTFIRSTNAME,
[Target].STUDENTLASTNAME = [Source].STUDENTLASTNAME,
[Target].CHANGEDATE = [Source].CHANGEDATE,
[Target].SUBMITDATE = [Source].SUBMITDATE
when not matched by target then
insert
(
CUSTOMFORMENTRYID,
CURRENTGRADELEVEL,
GRADELEVELAPPLY,
ACADEMICYEARID,
STUDENTFIRSTNAME,
STUDENTLASTNAME,
CHANGEDATE,
SUBMITDATE
)
values
(
[Source].FormEntryID,
[Source].CURRENTGRADELEVEL,
[Source].GRADELEVELAPPLY,
[Source].ACADEMICYEARID,
[Source].STUDENTFIRSTNAME,
[Source].STUDENTLASTNAME,
[Source].CHANGEDATE,
[Source].SUBMITDATE
);
end