spLoadRecord_SiteSurveys

CREATE procedure [dbo].[spLoadRecord_SiteSurveys]
(
    @PKID                     int,
    @LoadResponseSummary    bit = 0
)
as
begin
    set nocount on;
    declare @QTypeMatrixDD            integer;
    declare @hasResults                bit;
    declare @count                     integer;
    declare @numSurveyResponses     integer;
    declare @questions table(
            id                        integer,
            TotalRespondents        integer,
            Skipped                    integer
    );

    declare @responseSummary table(
            QId                integer,
            QSeq            integer, 
            AId                integer,
            ASeq            integer,
            AColId            integer,
            AColSeq            integer,
            AnswerValue        nvarchar(max), -- AnswerValue resized from nvarchar(256) to varchar(max)
            ResponsePercent    integer,
            ResponseTotal    integer
    );

    declare @matrixRows table(
            QId        integer,
            AId    integer
    );

    declare @matrixCols table(
            QId            integer,
            AColId        integer
    );

    select @numSurveyResponses = count(*)
    from SiteSurveyResponses
    where SiteSurveysId = @PKID;

    set @hasResults = 0;
    set @QTypeMatrixDD = 8; -- Question Type Matrix Multiple Answers Drop Downs

    select @count = count(*)
    from SiteSurveyResults a
    inner join SiteSurveyAnswers b on a.SiteSurveyAnswersId = b.id
    inner join SiteSurveyQuestions c on b.SiteSurveyQuestionsId = c.Id
    where c.SiteSurveysId = @PKID;

    -- if the survey is closed ignore the flag
    if exists (select * from SiteSurveys where ID = @PKID and State = 2)
        set @LoadResponseSummary = 1;

    if (@LoadResponseSummary = 1)
    begin
        insert into @responseSummary(QId, AId)
        select a.SiteSurveyQuestionsId, a.ID
        from SiteSurveyAnswers a
        inner join SiteSurveyQuestions b on a.SiteSurveyQuestionsId = b.id
        where b.SiteSurveysId = @PKID;

        insert into @matrixCols(QId, AColId)
        select b.ID, a.ID
        from SiteSurveyAnswerColumns a
        inner join SiteSurveyQuestions b on b.Id = a.SiteSurveyQuestionsId
        where b.SiteSurveysId = @PKID;

        insert into @matrixRows(QId, AId)
        select c.Qid, a.Id
        from SiteSurveyAnswers a
        inner join (select distinct QId from @matrixCols) c
        on c.Qid = a.SiteSurveyQuestionsId;

        delete @responseSummary
        from @responseSummary a
        inner join @matrixRows b
        on a.AId = b.AId;

        insert into @responseSummary(QId, AId, AColId)
        select a.QId, a.AId, b.AColId
        from @matrixRows a
        cross join @matrixCols b
        where a.QId = b.QId;

        update @responseSummary
        set AColId = 0 where AColId is null;

        update @responseSummary
        set ResponseTotal = v.ResponseTotal
        from @responseSummary a inner join 
        (
            select a.SiteSurveyAnswersId, a.SiteSurveyAnswerColumnsId, count(*) 'ResponseTotal'
            from SiteSurveyResults a
            inner join SiteSurveyAnswers b on a.SiteSurveyAnswersId = b.id
            inner join SiteSurveyQuestions c on b.SiteSurveyQuestionsId = c.Id
            where c.SiteSurveysId = @PKID        
            and c.SiteSurveyQuestionTypesId != @QTypeMatrixDD
            group by SiteSurveyAnswersId, SiteSurveyAnswerColumnsId
        ) as v
        on a.AId = v.SiteSurveyAnswersId
        and a.AColId = v.SiteSurveyAnswerColumnsId;

        update @responseSummary
        set AnswerValue = b.AnswerValue
        from @responseSummary a
        inner join SiteSurveyResults b on a.AId = b.SiteSurveyAnswersId
        and a.AColId = b.SiteSurveyAnswerColumnsId;

        -- Special case for Matrix - Multiple Answers (Dropdown)
        if exists(select c.ID
        from SiteSurveyQuestions c
        where c.SiteSurveyQuestionTypesId = @QTypeMatrixDD and c.SiteSurveysID = @PKID)
            begin -- special case
                delete from @matrixCols;
                delete from @matrixRows;

                insert into @matrixRows(QId, AId)
                select distinct a.SiteSurveyQuestionsId, a.Id
                from SiteSurveyAnswers a
                inner join SiteSurveyAnswerColumns b on b.SiteSurveyQuestionsId = a.SiteSurveyQuestionsId
                inner join SiteSurveyQuestions c on b.SiteSurveyQuestionsId = c.ID
                where c.SiteSurveyQuestionTypesId = @QTypeMatrixDD;

                insert into @matrixCols(QId, AColId)
                select b.SiteSurveyQuestionsId, a.ID
                from SiteSurveyAnswerColumnData a
                inner join SiteSurveyAnswerColumns b on a.SiteSurveyAnswerColumnsId = b.ID;

                delete @responseSummary
                from @responseSummary a
                inner join SiteSurveyQuestions b on b.ID = a.QId
                and b.SiteSurveyQuestionTypesId = @QTypeMatrixDD;

                insert into @responseSummary(QId, AId, AnswerValue)
                select a.QId, a.AId, cast(b.AColId as nvarchar(256))
                from @matrixRows a
                cross join @matrixCols b
                where a.QId = b.QId;

                update @responseSummary 
                set AColId = a.SiteSurveyAnswerColumnsId
                from SiteSurveyAnswerColumnData a
                inner join @responseSummary b on b.AnswerValue = cast(a.ID as nvarchar(50))
                inner join SiteSurveyQuestions c on c.ID = b.QId
                where c.SiteSurveyQuestionTypesId = @QTypeMatrixDD;

                update @responseSummary
                set AColSeq = c.Sequence
                from @responseSummary a
                inner join SiteSurveyQuestions b on b.ID = a.QId and b.SiteSurveyQuestionTypesId = @QTypeMatrixDD
                inner join SiteSurveyAnswerColumndata c on c.SiteSurveyAnswerColumnsID = a.AColId and cast(a.AnswerValue as int) = c.ID;

                update @responseSummary
                set ResponseTotal = v.ResponseTotal
                from @responseSummary a inner join 
                (
                    select a.SiteSurveyAnswersId, a.SiteSurveyAnswerColumnsId, a.AnswerValue, count(*) 'ResponseTotal'
                    from SiteSurveyResults a
                    inner join SiteSurveyAnswers b on a.SiteSurveyAnswersId = b.id
                    inner join SiteSurveyQuestions c on b.SiteSurveyQuestionsId = c.Id
                    where c.SiteSurveysId = @PKID        
                    and c.SiteSurveyQuestionTypesId = @QTypeMatrixDD
                    group by SiteSurveyAnswersId, SiteSurveyAnswerColumnsId, AnswerValue
                ) as v
                on a.AId = v.SiteSurveyAnswersId
                and a.AColId = v.SiteSurveyAnswerColumnsId
                and a.AnswerValue = v.AnswerValue
                collate database_default;
            end -- special case

        update @responseSummary
        set QSeq = b.Sequence
        from @responseSummary a
        inner join SiteSurveyQuestions b on b.ID = a.QId;

        update @responseSummary
        set ASeq = b.Sequence
        from @responseSummary a
        inner join SiteSurveyAnswers b on b.ID = a.AId;

        insert into @questions(id, TotalRespondents)
        select id, count(*) from
        (select distinct a.SiteSurveyResponsesId, c.id
        from SiteSurveyResults a
        inner join SiteSurveyAnswers b on a.SiteSurveyAnswersId = b.id
        inner join SiteSurveyQuestions c on b.SiteSurveyQuestionsid = c.id
        where c.SiteSurveysId = @PKID) as v
        group by id;

        insert into @questions(id, TotalRespondents)
        select id, 0 from SiteSurveyQuestions
        where SiteSurveysId = @PKID
        and id not in (select id from @questions);

        update @questions set Skipped = (@numSurveyResponses - TotalRespondents);

        update @responseSummary
        --set ResponsePercent = ((b.ResponseTotal * 100)/@numSurveyResponses),
        set ResponsePercent = ROUND((convert(decimal(30,10),b.ResponseTotal)/convert(decimal(30,10),@numSurveyResponses)) * 100.0, 0)
        from @questions a
        inner join @responseSummary b on a.ID = b.QId;
    end

    -- result set #1
    select ID, ClientSitesID, Title, TitleVisible, SubTitle, SubTitleVisible, 
    LastActivityDate, SubmitButtonText, NextButtonText, PrevButtonText, PrevButtonVisible, 
    ExitButtonText, ExitButtonVisible, AllowMultipleSubmit, OnSubmitMsg, AllowViewResults, 
    State, ClosedMsg, @hasResults 'HasResults', @numSurveyResponses 'TotalResponses'
    from SiteSurveys where ID = @PKID;

    -- result set #2
    select ssq.ID, ssq.SiteSurveysId, ssq.SiteSurveyQuestionTypesId, ssq.Sequence, ssq.Page, 
    ssq.LinkText, ssq.Question, ssq.SubTitle, ssq.RandomizeAnswers, ssq.Mandatory, ssq.MaxAnswerLen, 
    ssq.LastActivityDate, ssq.ConstantSumValue, ssq.SkipAutoNbr,
    q.id, q.TotalRespondents, q.Skipped
    from SiteSurveyQuestions ssq left outer join @questions q on q.id = ssq.id
    inner join SiteSurveys ss on ss.id = ssq.SiteSurveysId
    where ss.ID = @PKID
    order by ssq.Sequence;

    -- result set #3
    select ssa.ID, ssa.SiteSurveyQuestionsId, ssa.Sequence, ssa.Answer, ssa.NextQuestionId 
    from SiteSurveyAnswers ssa inner join SiteSurveyQuestions ssq on ssq.ID = ssa.SiteSurveyQuestionsId
    where ssq.SiteSurveysId = @PKID
    order by ssq.Sequence, ssa.Sequence;

    -- result set #4
    select ssac.ID, ssac.SiteSurveyQuestionsId, ssac.Sequence, ssac.ColumnName 'Answer', null 'NextQuestionId'
    from SiteSurveyAnswerColumns ssac inner join SiteSurveyQuestions ssq on ssq.id = ssac.SiteSurveyQuestionsId
    where ssq.SiteSurveysId = @PKID
    order by ssq.Sequence, ssac.Sequence;

    -- result set #5
    select ssacd.ID, ssacd.SiteSurveyAnswerColumnsId, ssacd.Sequence, ssacd.Data
    from SiteSurveyAnswerColumnData ssacd
    inner join SiteSurveyAnswerColumns ssac on ssac.id = ssacd.SiteSurveyAnswerColumnsId
    inner join SiteSurveyQuestions ssq on ssq.id = ssac.SiteSurveyQuestionsId
    where ssq.SiteSurveysId = @PKID
    order by ssq.Sequence, ssac.Sequence, ssacd.Sequence;

    -- result set #6
    if ((@LoadResponseSummary = 1) and (@count > 0))
    begin
        select QId 'SiteSurveyQuestionsId', AId 'SiteSurveyAnswersId', AColId 'SiteSurveyAnswerColumnsId', 
        AnswerValue, ResponsePercent, ResponseTotal
        from @responseSummary
        order by QSeq, ASeq, AColID, AColSeq;
    end
end