USP_CUSTOMFORMDASHBOARD_SAVECONFIGURATION

CREATE procedure dbo.USP_CUSTOMFORMDASHBOARD_SAVECONFIGURATION
(
    @REENROLLMENT_INVITEDBACK_ISINCLUDED bit,
    @REENROLLMENT_CONTRACTWITHHELD_ISINCLUDED bit,
    @REENROLLMENT_STILLWAITING_ISINCLUDED bit,
    @REENROLLMENT_COMINGBACK_ISINCLUDED bit,
    @REENROLLMENT_NOTRETURNING_ISINCLUDED bit,
    @REENROLLMENT_PERCENTAGENCOMINGBACK_ISINCLUDED bit,

    @REENROLLMENT_CONTRACTWITHHELD_CONTRACTSTATUS int,
    @REENROLLMENT_STILLWAITING_CONTRACTSTATUS int,
    @REENROLLMENT_COMINGBACK_CONTRACTSTATUS int,
    @REENROLLMENT_NOTRETURNING_CONTRACTSTATUS int,

    @ADMISSIONS_FORMSSTARTED_ISINCLUDED bit,
    @ADMISSIONS_FORMSCOMPLETED_ISINCLUDED bit,
    @ADMISSIONS_TOTALAPPLIED_ISINCLUDED bit,
    @ADMISSIONS_ACCEPTED_ISINCLUDED bit,
    @ADMISSIONS_ACCEPTEDSTILLWAITING_ISINCLUDED bit,
    @ADMISSIONS_ACCEPTEDCOMING_ISINCLUDED bit,
    @ADMISSIONS_ACCEPTEDNOTCOMING_ISINCLUDED bit,
    @ADMISSIONS_WAITPOOLED_ISINCLUDED bit,
    @ADMISSIONS_WAITPOOLACCEPTED_ISINCLUDED bit,
    @ADMISSIONS_WAITPOOLDENIED_ISINCLUDED bit,
    @ADMISSIONS_TOTALCOMING_ISINCLUDED bit,
    @ADMISSIONS_PERCENTAGEENROLLING_ISINCLUDED bit,

    @ADMISSIONS_ACCEPTED_STATUS int,
    @ADMISSIONS_ACCEPTEDSTILLWAITING_STATUS int,
    @ADMISSIONS_ACCEPTEDCOMING_STATUS int,
    @ADMISSIONS_ACCEPTEDNOTCOMING_STATUS int,
    @ADMISSIONS_WAITPOOLED_STATUS int,
    @ADMISSIONS_WAITPOOLACCEPTED_STATUS int,
    @ADMISSIONS_WAITPOOLDENIED_STATUS int,
    @ADMISSIONS_TOTALCOMING_STATUS int,

    @TOTALS_TOTALDEFINITELYCOMING_ISINCLUDED bit,
    @TOTALS_MAYBES_ISINCLUDED bit,
    @TOTALS_TOTALEXPECTED_ISINCLUDED bit,
    @TOTALS_SEATSREMAINING_ISINCLUDED bit,

    @FINANCIALAID_FINANCIALAIDDESIRED_ISINCLUDED bit,
    @FINANCIALAID_FINANCIALAIDGRANTED_ISINCLUDED bit,

    @GRADEYEARMODECODE int,
    @GRADELEVELS xml = null output
)
as
begin
    set nocount on;

    truncate table dbo.CUSTOMFORMDASHBOARDCONFIGURATION

    insert into dbo.CUSTOMFORMDASHBOARDCONFIGURATION
    (
        REENROLLMENT_INVITEDBACK_ISINCLUDED,
        REENROLLMENT_CONTRACTWITHHELD_ISINCLUDED,
        REENROLLMENT_STILLWAITING_ISINCLUDED,
        REENROLLMENT_COMINGBACK_ISINCLUDED,
        REENROLLMENT_NOTRETURNING_ISINCLUDED,
        REENROLLMENT_PERCENTAGENCOMINGBACK_ISINCLUDED,

        REENROLLMENT_CONTRACTWITHHELD_CONTRACTSTATUS,
        REENROLLMENT_STILLWAITING_CONTRACTSTATUS,
        REENROLLMENT_COMINGBACK_CONTRACTSTATUS,
        REENROLLMENT_NOTRETURNING_CONTRACTSTATUS,

        ADMISSIONS_FORMSSTARTED_ISINCLUDED,
        ADMISSIONS_FORMSCOMPLETED_ISINCLUDED,
        ADMISSIONS_TOTALAPPLIED_ISINCLUDED,
        ADMISSIONS_ACCEPTED_ISINCLUDED,
        ADMISSIONS_ACCEPTEDSTILLWAITING_ISINCLUDED,
        ADMISSIONS_ACCEPTEDCOMING_ISINCLUDED,
        ADMISSIONS_ACCEPTEDNOTCOMING_ISINCLUDED,
        ADMISSIONS_WAITPOOLED_ISINCLUDED,
        ADMISSIONS_WAITPOOLACCEPTED_ISINCLUDED,
        ADMISSIONS_WAITPOOLDENIED_ISINCLUDED,
        ADMISSIONS_TOTALCOMING_ISINCLUDED,
        ADMISSIONS_PERCENTAGEENROLLING_ISINCLUDED,

        ADMISSIONS_ACCEPTED_STATUS,
        ADMISSIONS_ACCEPTEDSTILLWAITING_STATUS,
        ADMISSIONS_ACCEPTEDCOMING_STATUS,
        ADMISSIONS_ACCEPTEDNOTCOMING_STATUS,
        ADMISSIONS_WAITPOOLED_STATUS,
        ADMISSIONS_WAITPOOLACCEPTED_STATUS,
        ADMISSIONS_WAITPOOLDENIED_STATUS,
        ADMISSIONS_TOTALCOMING_STATUS,

        TOTALS_TOTALDEFINITELYCOMING_ISINCLUDED,
        TOTALS_MAYBES_ISINCLUDED,
        TOTALS_TOTALEXPECTED_ISINCLUDED,
        TOTALS_SEATSREMAINING_ISINCLUDED,

        FINANCIALAID_FINANCIALAIDDESIRED_ISINCLUDED,
        FINANCIALAID_FINANCIALAIDGRANTED_ISINCLUDED,

        GRADEYEARMODECODE
    )
    values
    (
        @REENROLLMENT_INVITEDBACK_ISINCLUDED,
        @REENROLLMENT_CONTRACTWITHHELD_ISINCLUDED,
        @REENROLLMENT_STILLWAITING_ISINCLUDED,
        @REENROLLMENT_COMINGBACK_ISINCLUDED,
        @REENROLLMENT_NOTRETURNING_ISINCLUDED,
        @REENROLLMENT_PERCENTAGENCOMINGBACK_ISINCLUDED,

        @REENROLLMENT_CONTRACTWITHHELD_CONTRACTSTATUS,
        @REENROLLMENT_STILLWAITING_CONTRACTSTATUS,
        @REENROLLMENT_COMINGBACK_CONTRACTSTATUS,
        @REENROLLMENT_NOTRETURNING_CONTRACTSTATUS,

        @ADMISSIONS_FORMSSTARTED_ISINCLUDED,
        @ADMISSIONS_FORMSCOMPLETED_ISINCLUDED,
        @ADMISSIONS_TOTALAPPLIED_ISINCLUDED,
        @ADMISSIONS_ACCEPTED_ISINCLUDED,
        @ADMISSIONS_ACCEPTEDSTILLWAITING_ISINCLUDED,
        @ADMISSIONS_ACCEPTEDCOMING_ISINCLUDED,
        @ADMISSIONS_ACCEPTEDNOTCOMING_ISINCLUDED,
        @ADMISSIONS_WAITPOOLED_ISINCLUDED,
        @ADMISSIONS_WAITPOOLACCEPTED_ISINCLUDED,
        @ADMISSIONS_WAITPOOLDENIED_ISINCLUDED,
        @ADMISSIONS_TOTALCOMING_ISINCLUDED,
        @ADMISSIONS_PERCENTAGEENROLLING_ISINCLUDED,

        @ADMISSIONS_ACCEPTED_STATUS,
        @ADMISSIONS_ACCEPTEDSTILLWAITING_STATUS,
        @ADMISSIONS_ACCEPTEDCOMING_STATUS,
        @ADMISSIONS_ACCEPTEDNOTCOMING_STATUS,
        @ADMISSIONS_WAITPOOLED_STATUS,
        @ADMISSIONS_WAITPOOLACCEPTED_STATUS,
        @ADMISSIONS_WAITPOOLDENIED_STATUS,
        @ADMISSIONS_TOTALCOMING_STATUS,

        @TOTALS_TOTALDEFINITELYCOMING_ISINCLUDED,
        @TOTALS_MAYBES_ISINCLUDED,
        @TOTALS_TOTALEXPECTED_ISINCLUDED,
        @TOTALS_SEATSREMAINING_ISINCLUDED,

        @FINANCIALAID_FINANCIALAIDDESIRED_ISINCLUDED,
        @FINANCIALAID_FINANCIALAIDGRANTED_ISINCLUDED,

        @GRADEYEARMODECODE
    )

    declare @TempTbl table (
       [ID] uniqueidentifier,
       [GRADELEVELID] int,
       [SEATSAVAILABLE] int)

    insert into @TempTbl select 
        [ID],
        [GRADELEVELID],
        [SEATSAVAILABLE]
    from dbo.UFN_CUSTOMFORMDASHBOARD_GETGRADELEVELS_FROMITEMLISTXML(@GRADELEVELS)

    update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

    delete from dbo.[CUSTOMFORMDASHBOARDGRADELEVEL] where [CUSTOMFORMDASHBOARDGRADELEVEL].ID in 
    (
        select ID from dbo.CUSTOMFORMDASHBOARDGRADELEVEL
        EXCEPT select ID from @TempTbl
    )

    update dbo.CUSTOMFORMDASHBOARDGRADELEVEL set
        CUSTOMFORMDASHBOARDGRADELEVEL.ID = temp.ID,
        CUSTOMFORMDASHBOARDGRADELEVEL.GRADELEVELID = temp.GRADELEVELID,
        CUSTOMFORMDASHBOARDGRADELEVEL.SEATSAVAILABLE = temp.SEATSAVAILABLE
    from dbo.CUSTOMFORMDASHBOARDGRADELEVEL inner join @TempTbl as [temp] on [CUSTOMFORMDASHBOARDGRADELEVEL].ID = [temp].ID

    insert into [CUSTOMFORMDASHBOARDGRADELEVEL] 
    (
        [ID],
        [GRADELEVELID],
        [SEATSAVAILABLE]
    )
    select
        [ID],
        [GRADELEVELID],
        [SEATSAVAILABLE]
    from @TempTbl as [temp]
    where not exists (select ID from dbo.[CUSTOMFORMDASHBOARDGRADELEVEL] as data where data.ID = [temp].ID)

end