Audits
unknown
plain_text
a month ago
14 kB
1
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