Audits

unknown
plain_text
a month ago
14 kB
Indexable
Never
----------------------------------------------------------------------------------
-- SCRIPT SETUP
----------------------------------------------------------------------------------

DECLARE @CID INT;   -- Contract ID
DECLARE @FID INT;   -- Max Form ID
DECLARE @SID INT;   -- Max Form Section ID
DECLARE @AID INT;  -- Max Cabin Area ID

SET @CID = 1026
SET @FID = ISNULL((SELECT MAX(formID) FROM dbo.Forms), 0);
SET @SID = ISNULL((SELECT MAX(formSectionID) FROM dbo.FormSections), 0);
SET @AID = ISNULL((SELECT MAX(cabinAircraftAreaID) FROM dbo.CabinAircraftAreas), 0);

----------------------------------------------------------------------------------
-- Areas
----------------------------------------------------------------------------------

UPDATE AvtechConnectTraining.dbo.Contracts SET enableAssignmentConfirmation = 0 WHERE contractID = @CID;

----------------------------------------------------------------------------------
-- Areas
----------------------------------------------------------------------------------

INSERT into dbo.CabinAircraftAreas (contractID, aircraftAreaMasterID, description, assignmentRequired, reference1ImageID, reference2ImageID, created, edited)
VALUES (@CID, 4, N'1st class lavatories', 0, null, null, N'2023-05-23 21:36:14.5286924', N'2023-05-23 21:36:14.5286924');

INSERT into dbo.CabinAircraftAreas (contractID, aircraftAreaMasterID, description, assignmentRequired, reference1ImageID, reference2ImageID, created, edited)
VALUES (@CID, 4, N'Economy class lavatories', 0, null, null, N'2023-05-23 21:36:14.5286924', N'2023-05-23 21:36:14.5286924');

INSERT into dbo.CabinAircraftAreas (contractID, aircraftAreaMasterID, description, assignmentRequired, reference1ImageID, reference2ImageID, created, edited)
VALUES (@CID, 5, N'Aisle carpets', 0, null, null, N'2023-05-23 21:36:14.5286924', N'2023-05-23 21:36:14.5286924');

INSERT into dbo.CabinAircraftAreas (contractID, aircraftAreaMasterID, description, assignmentRequired, reference1ImageID, reference2ImageID, created, edited)
VALUES (@CID, 6, N'Cockpit inside', 0, null, null, N'2023-05-23 21:36:14.5286924', N'2023-05-23 21:36:14.5286924');

INSERT into dbo.CabinAircraftAreas (contractID, aircraftAreaMasterID, description, assignmentRequired, reference1ImageID, reference2ImageID, created, edited)
VALUES (@CID, 6, N'Cockpit outside', 0, null, null, N'2023-05-23 21:36:14.5286924', N'2023-05-23 21:36:14.5286924');

----------------------------------------------------------------------------------
-- FORM 1
----------------------------------------------------------------------------------

INSERT into dbo.Forms (formCategoryID, name, description, airlineID, created, edited, reportImageID, notScored)
VALUES (1, N'Audit without areas', N'Audit without areas', null, N'2023-05-23 21:22:53.4969226', null, null, 1);

INSERT INTO dbo.ContractForms (contractID, formID)
VALUES (@CID, @FID);

-- Section 1
INSERT into dbo.FormSections (formID, caption, description, orderIndex, calculateScore, excludeFromTotalScore, created, edited, sectionScoreWeight, aircraftAreaMasterID, cabinAircraftAreaID, hideSectionUntilFirstSave)
VALUES (@FID+1, N'Section 1', N'Section 1', 1, 0, 1, N'2023-05-23 21:36:14.5286924', null, 1, null, null, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+1, 4, N'First?', null, 0, 1, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+1, 4, N'Second?', null, 0, 1, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+1, 4, N'Third?', null, 0, 1, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

-- Section 2
INSERT into dbo.FormSections (formID, caption, description, orderIndex, calculateScore, excludeFromTotalScore, created, edited, sectionScoreWeight, aircraftAreaMasterID, cabinAircraftAreaID, hideSectionUntilFirstSave)
VALUES (@FID, N'Section 2', N'Section 2', 2, 0, 2, N'2023-05-23 21:36:14.5286924', null, 1, null, null, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+2, 4, N'First 2?', null, 0, 1, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+2, 4, N'Second 2?', null, 1, 2, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+2, 4, N'Third 2?', null, 1, 3, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

----------------------------------------------------------------------------------
-- FORM 2
----------------------------------------------------------------------------------

INSERT into dbo.Forms (formCategoryID, name, description, airlineID, created, edited, reportImageID, notScored) VALUES (2, N'Audit with areas', N'Audit with areas', null, N'2023-05-23 21:22:53.4969226', null, null, 1);
INSERT INTO dbo.ContractForms (contractID, formID) VALUES (@CID, @FID+2);

-- Section 1
INSERT into dbo.FormSections (formID, caption, description, orderIndex, calculateScore, excludeFromTotalScore, created, edited, sectionScoreWeight, aircraftAreaMasterID, cabinAircraftAreaID, hideSectionUntilFirstSave)
VALUES (@FID+2, N'Lavatory section', N'Lavatory section', 1, 0, 1, N'2023-05-23 21:36:14.5286924', null, 1, 4, @AID+1, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+3, 4, N'Lavatory required question', null, 0, 3, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+3, 4, N'Lavatory optional question', null, 1, 3, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

-- Section 2

INSERT into dbo.FormSections (formID, caption, description, orderIndex, calculateScore, excludeFromTotalScore, created, edited, sectionScoreWeight, aircraftAreaMasterID, cabinAircraftAreaID, hideSectionUntilFirstSave)
VALUES (@FID+2, N'Lavatory 2 section', N'Lavatory 2 section', 1, 0, 1, N'2023-05-23 21:36:14.5286924', null, 1, 4, @AID+2, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+4, 4, N'Lavatory 2 required question', null, 0, 3, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+4, 4, N'Lavatory 2 optional question', null, 1, 3, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

-- Section 3
INSERT into dbo.FormSections (formID, caption, description, orderIndex, calculateScore, excludeFromTotalScore, created, edited, sectionScoreWeight, aircraftAreaMasterID, cabinAircraftAreaID, hideSectionUntilFirstSave)
VALUES (@FID+2, N'Carpet section', N'Carpet section', 2, 0, 1, N'2023-05-23 21:36:14.5286924', null, 1, 5, @AID+3, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+5, 4, N'Carpet required question', null, 0, 3, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+5, 4, N'Carpet optional question', null, 1, 3, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

-- Section 4
INSERT into dbo.FormSections (formID, caption, description, orderIndex, calculateScore, excludeFromTotalScore, created, edited, sectionScoreWeight, aircraftAreaMasterID, cabinAircraftAreaID, hideSectionUntilFirstSave)
VALUES (@FID+2, N'Cockpit section', N'Cockpit section', 3, 0, 1, N'2023-05-23 21:36:14.5286924', null, 1, 6, @AID+4, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+6, 4, N'Cockpit required question', null, 0, 3, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+6, 4, N'Cockpit optional question', null, 1, 3, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

-- Section 5
INSERT into dbo.FormSections (formID, caption, description, orderIndex, calculateScore, excludeFromTotalScore, created, edited, sectionScoreWeight, aircraftAreaMasterID, cabinAircraftAreaID, hideSectionUntilFirstSave)
VALUES (@FID+2, N'Cockpit 2 section', N'Cockpit section', 3, 0, 1, N'2023-05-23 21:36:14.5286924', null, 1, 6, @AID+5, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+7, 4, N'Cockpit 2 required question', null, 0, 3, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+7, 4, N'Cockpit 2 optional question', null, 0, 3, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

-- Section 6
INSERT into dbo.FormSections (formID, caption, description, orderIndex, calculateScore, excludeFromTotalScore, created, edited, sectionScoreWeight, aircraftAreaMasterID, cabinAircraftAreaID, hideSectionUntilFirstSave)
VALUES (@FID+2, N'GPS & Signature', N'GPS & Signature 3', 4, 0, 1, N'2023-05-23 21:36:14.5286924', null, 1, null, null, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+8, 7, N'GPS Location', null, 1, 3, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);

INSERT into dbo.FormQuestions (formSectionID, formAnswerTypeID, questionText, questionHelp, isOptional, orderIndex, formAnswerGroupID, created, edited, isHidden, isScored, questionScoreWeight, aircraftAreaMasterID, removedTimeUtc, cabinAircraftAreaID, enableQuickNoteForResponse)
VALUES (@SID+8, 8, N'Signature', null, 1, 3, 1, N'2023-05-23 21:42:10.4752778', null, 0, 0, 1, null, null, null, 0);


----------------------------------------------------------------------------------
-- Form <-> Cabin service types
----------------------------------------------------------------------------------

DECLARE @TID INT = 0

DECLARE typeCursor CURSOR FOR
    SELECT cabinServiceTypeID
    FROM dbo.CabinServiceTypes
    WHERE contractID = @CID

OPEN typeCursor
FETCH NEXT FROM typeCursor INTO @TID

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO dbo.CabinServiceTypeAuditForms (cabinServiceTypeID, formID) VALUES (@TID, @FID+1), (@TID, @FID+2);
    FETCH NEXT FROM typeCursor INTO @TID
END

CLOSE typeCursor
DEALLOCATE typeCursor