create assessments nested cursor

gistfile1.sql

-----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------

drop table #objectmap
create table #objectmap (
  iItem int identity,

	aObject_DBID int,
	aObjectGradeRange varchar(100),
	aObjectSortOrder int,
	aObjectSubject varchar(100),
	aObjectName varchar(100),
);


insert into #objectmap select	0,	'1-3',	null,	'Habiletés, attitudes et engagement',	'Habiletés, attitudes et engagement'
insert into #objectmap select	0,	'4-6',	null,	'Habiletés, attitudes et engagement',	'Habiletés, attitudes et engagement'

insert into #objectmap select	0,	'1-3',	1,	'Français',	'Lecture'
insert into #objectmap select	0,	'4-6',	1,	'Français',	'Lecture'

insert into #objectmap select	0,	'1-3',	2,	'Français',	'Écriture'
insert into #objectmap select	0,	'4-6',	2,	'Français',	'Écriture'

insert into #objectmap select	0,	'1-3',	3,	'Français',	'Communication orale'
insert into #objectmap select	0,	'4-6',	3,	'Français',	'Communication orale'

insert into #objectmap select	0,	'1-3',	9,	'Musique',	'Musique'
insert into #objectmap select	0,	'4-6',	9,	'Musique',	'Musique'



-----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------



drop table #map

create table #map (
	iItem int identity,

	aItemSortOrder int,
	aItemInputPreset int,
	aItemGrade varchar(25),
	aItemObject varchar(100),	
	aItemAbrev varchar(25),
	aItemName varchar(100),
);

--------Habiletés, attitudes et engagement
--1-3
insert into #map select		1,	970000,	'1-3',	'Habiletés, attitudes et engagement',	'ENG1',		'Utiliser le français pour communiquer.'		
insert into #map select		2,	970000,	'1-3',	'Habiletés, attitudes et engagement',	'ENG2',		'Soigner la présentation de ses travaux incluant la calligraphie.'
insert into #map select		4,	970000,	'1-3',	'Habiletés, attitudes et engagement',	'ENG3',		'Faire ses devoirs à la maison.'						
insert into #map select		5,	970000,	'1-3',	'Habiletés, attitudes et engagement',	'ENG4',		'Remettre les documents nécessitant une signature.'
insert into #map select		6,	970000,	'1-3',	'Habiletés, attitudes et engagement',	'ENG5',		'Être attentif en classe.'					
insert into #map select		7,	970000,	'1-3',	'Habiletés, attitudes et engagement',	'ENG6',		'Respecter les consignes de la classe et de l’école.'
insert into #map select		8,	970000,	'1-3',	'Habiletés, attitudes et engagement',	'ENG7',		'Adopter des comportements sécuritaires en tout temps. '
insert into #map select		9,	970000,	'1-3',	'Habiletés, attitudes et engagement',	'ENG8',		'Respecter le matériel (le sien et celui des autres) ainsi que l’environnement.'
insert into #map select		10,	970000,	'1-3',	'Habiletés, attitudes et engagement',	'ENG9',		'Respecter l’autorité en place.'
insert into #map select		11,	970000,	'1-3',	'Habiletés, attitudes et engagement',	'ENG10',	'Respecter les autres élèves et régler ses conflits efficacement.'
insert into #map select		12,	970000,	'1-3',	'Habiletés, attitudes et engagement',	'ENG11',	'Travailler de façon autonome.'
insert into #map select		13,	970000,	'1-3',	'Habiletés, attitudes et engagement',	'ENG12',	'Travailler en collaboration.'
--4-6
insert into #map select		1,	970000,	'4-6',	'Habiletés, attitudes et engagement',	'ENG1',		'Utiliser le français pour communiquer.'		
insert into #map select		2,	970000,	'4-6',	'Habiletés, attitudes et engagement',	'ENG2',		'Soigner la présentation de ses travaux incluant la calligraphie.'
insert into #map select		3,	970000,	'4-6',	'Habiletés, attitudes et engagement',	'ENG3',		'Remettre ses travaux selon l’échéance prévue.'	
insert into #map select		4,	970000,	'4-6',	'Habiletés, attitudes et engagement',	'ENG4',		'Faire ses devoirs à la maison.'						
insert into #map select		5,	970000,	'4-6',	'Habiletés, attitudes et engagement',	'ENG5',		'Remettre les documents nécessitant une signature.'
insert into #map select		6,	970000,	'4-6',	'Habiletés, attitudes et engagement',	'ENG6',		'Être attentif en classe.'					
insert into #map select		7,	970000,	'4-6',	'Habiletés, attitudes et engagement',	'ENG7',		'Respecter les consignes de la classe et de l’école.'
insert into #map select		8,	970000,	'4-6',	'Habiletés, attitudes et engagement',	'ENG8',		'Adopter des comportements sécuritaires en tout temps. '
insert into #map select		9,	970000,	'4-6',	'Habiletés, attitudes et engagement',	'ENG9',		'Respecter le matériel (le sien et celui des autres) ainsi que l’environnement.'
insert into #map select		10,	970000,	'4-6',	'Habiletés, attitudes et engagement',	'ENG10',	'Respecter l’autorité en place.'
insert into #map select		11,	970000,	'4-6',	'Habiletés, attitudes et engagement',	'ENG11',	'Respecter les autres élèves et régler ses conflits efficacement.'
insert into #map select		12,	970000,	'4-6',	'Habiletés, attitudes et engagement',	'ENG12',	'Travailler de façon autonome.'
insert into #map select		13,	970000,	'4-6',	'Habiletés, attitudes et engagement',	'ENG13',	'Travailler en collaboration.'
insert into #map select		14,	970000,	'4-6',	'Habiletés, attitudes et engagement',	'ENG14',	'Gérer son temps efficacement et être organisé.'


--------Français
--1-3
insert into #map select		17,	970000,	'1-3',	'Lecture',		'ENGAGE',	'Engagement.'		
insert into #map select		18,	960000,	'1-3',	'Lecture',		'LEC1',		'Témoigner de son goût de lire.'		
insert into #map select		19,	960000,	'1-3',	'Lecture',		'LEC2',		'Planifier ses lectures.'
insert into #map select		20,	960000,	'1-3',	'Lecture',		'LEC3',		'Utiliser des connaissances et des stratégies pour démontrer sa compréhension.'
insert into #map select		21,	960000,	'1-3',	'Lecture',		'LEC4',		'Démontrer sa compréhension en effectuant des tâches à la suite de ses lectures.'
--4-6
insert into #map select		17,	970000,	'4-6',	'Lecture',		'ENGAGE',	'Engagement.'		
insert into #map select		18,	960000,	'4-6',	'Lecture',		'LEC1',		'Témoigner de son goût de lire.'		
insert into #map select		19,	960000,	'4-6',	'Lecture',		'LEC2',		'Planifier ses lectures.'
insert into #map select		20,	960000,	'4-6',	'Lecture',		'LEC3',		'Utiliser des connaissances et des stratégies pour démontrer sa compréhension.'
insert into #map select		21,	960000,	'4-6',	'Lecture',		'LEC4',		'Démontrer sa compréhension en effectuant des tâches à la suite de ses lectures.'


--------Français
--1-3
insert into #map select		22,	970000,	'1-3',	'Écriture',			'ENGAGE',	'Engagement'	
insert into #map select		23,	960000,	'1-3',	'Écriture',			'ECR1',		'Valoriser le recours à l’écriture.'	
insert into #map select		24,	960000,	'1-3',	'Écriture',			'ECR2',		'Planifier ses productions écrites.'	
insert into #map select		25,	960000,	'1-3',	'Écriture',			'ECR3',		'Rédiger différents types de textes.'	
insert into #map select		26,	960000,	'1-3',	'Écriture',			'ECR4',		'Réviser ses productions écrites.'	
insert into #map select		27,	960000,	'1-3',	'Écriture',			'ECR5',		'Corriger ses productions écrites en appliquant les conventions de la langue.'	
insert into #map select		28,	960000,	'1-3',	'Écriture',			'ECR6',		'Travailler la présentation de ses productions écrites.'	
--4-6
insert into #map select		22,	970000,	'4-6',	'Écriture',			'ENGAGE',	'Engagement'	
insert into #map select		23,	960000,	'4-6',	'Écriture',			'ECR1',		'Valoriser le recours à l’écriture.'	
insert into #map select		24,	960000,	'4-6',	'Écriture',			'ECR2',		'Planifier ses productions écrites.'	
insert into #map select		25,	960000,	'4-6',	'Écriture',			'ECR3',		'Rédiger différents types de textes.'	
insert into #map select		26,	960000,	'4-6',	'Écriture',			'ECR4',		'Réviser ses productions écrites.'	
insert into #map select		27,	960000,	'4-6',	'Écriture',			'ECR5',		'Corriger ses productions écrites en appliquant les conventions de la langue.'	
insert into #map select		28,	960000,	'4-6',	'Écriture',			'ECR6',		'Travailler la présentation de ses productions écrites.'	


--------Français

--1-3
insert into #map select		29,	970000,	'1-3',	'Communication orale',				'ENGAGE',	'Engagement'
insert into #map select		30,	960000,	'1-3',	'Communication orale',				'COM1',		'Écouter et comprendre des messages variés.'
insert into #map select		32,	960000,	'1-3',	'Communication orale',				'COM2',		'Communiquer clairement dans divers situations.'
--4-6
insert into #map select		29,	970000,	'4-6',	'Communication orale',				'ENGAGE',	'Engagement'
insert into #map select		30,	960000,	'4-6',	'Communication orale',				'COM1',		'Écouter et comprendre des messages variés.'
insert into #map select		31,	960000,	'4-6',	'Communication orale',				'COM2',		'Planifier ses interventions verbales'
insert into #map select		32,	960000,	'4-6',	'Communication orale',				'COM3',		'Communiquer clairement dans divers situations.'


--------Musique
--1-3
insert into #map select		1,	970000,	'1-3',	'Musique',				'ENGAGE',	'Engagement'
insert into #map select		2,	960000,	'1-3',	'Musique',				'MUS1',		'Interpréter une variété de pièces musicales instrumentales et vocales.'
insert into #map select		3,	960000,	'1-3',	'Musique',				'MUS2',		'Apprécier des pièces musicales variées.'
insert into #map select		4,	960000,	'1-3',	'Musique',				'MUS3',		'Participer à la création et/ou à l’improvisation de pièces musicales.'
--4-6
insert into #map select		1,	970000,	'4-6',	'Musique',				'ENGAGE',	'Engagement'
insert into #map select		2,	960000,	'4-6',	'Musique',				'MUS1',		'Interpréter une variété de pièces musicales instrumentales et vocales.'
insert into #map select		3,	960000,	'4-6',	'Musique',				'MUS2',		'Apprécier des pièces musicales variées.'
insert into #map select		4,	960000,	'4-6',	'Musique',				'MUS3',		'Participer à la création et/ou à l’improvisation de pièces musicales.'







select
	sao.assessment_OBJECT_NAME,
	sai.label,
	saiig.preset_id,
	sai.*
from saao_assessment_item sai
join saao_assessment_object sao
	on sai.assessment_object_id = sao.assessment_object_id
join saao_assessment_item_input_config saiig
	on saiig.assessment_item_id = sai.assessment_item_id
where 
sai.assessment_object_id in (64,65,66,67,68,69,70,71,72,73,74,75) order by sai.assessment_object_id





/*


select * from saao_assessment_object





 select * from saao_assessment_item_input_config 
 where assessment_item_id in 
	 (select assessment_item_id from saao_assessment_item 
	  where assessment_object_id = 74)



*/


-----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------



BEGIN TRANSACTION

--declare outer loop stuff
DECLARE @OUT_STATUS INT,					-- @OUT_STATUS = 1 means success, 0 means failure.  Yes this is backwards.  Yes it's annoying
        @OUT_STATUS_STRING VARCHAR(1024),	-- Error message if a problem
        @SELECTION_ID INT, 
        @ASSESSMENT_OBJECT_ID INT, 
        @ASSESSMENT_ITEM_ID INT, 
        @SUBJECT_ID INT, 
        @SUBJECT_GUID UNIQUEIDENTIFIER, 
        @NEW_PRESET_ID INT, 
        @NEW_GRADING_CODE_GROUP_ID INT,
        @UPPER_GRADE_INDEX INT,
        @LOWER_GRADE_INDEX INT,
        @TEXT_VALUE_ID INT,
        @OWNER_SELECTION_ID INT,
        @GUID uniqueidentifier


declare	@aObject_DBID int				--this is going to be updated with @ASSESSMENT_ITEM_ID 
declare @aObjectGradeRange varchar(100)
declare @aObjectName varchar(100)
declare @aObjectSubject varchar(100)
declare @aObjectSortOrder int

--declare inner loop stuff
declare	@aItemSortOrder int
declare @aItemInputPreset int
declare @aItemName varchar(100)
declare @aItemAbrev varchar(25)



---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------



DECLARE aObjectCursor CURSOR FOR 
  select aObjectGradeRange, aObjectName, aObjectSubject, aObjectSortOrder from #objectmap
    
open aObjectCursor
fetch next from aObjectCursor into @aObjectGradeRange, @aObjectName, @aObjectSubject, @aObjectSortOrder
while @@FETCH_STATUS=0
begin




		SELECT top 1 
				@OWNER_SELECTION_ID = SELECTION_ID
		FROM    SASEL_SELECTION



		------------------------------------------------------------
		------------------------------------------------------------


		--Create Selection object
		EXEC sasel_entityselection_AddNew 
			@IN_SELECTION_NAME					= @aObjectName,  
			@IN_SELECTION_TYPE					= 'ENTITY_SELECTION',
			@IN_PRIMARY_SELECTION_ENTITY_OBJECT_CODE = NULL,
			@IN_OWNER_SELECTION_ID				= @OWNER_SELECTION_ID,
			@IN_DELETE_SCHEDULED				= 0,
			@IN_SELECTION_CONTEXT				= 'ASSESSMENT',
			@IN_INCLUDE_INACTIVE_STUDENTS		= 0,
			@OUT_SELECTION_ID					= @SELECTION_ID		OUTPUT,
			@OUT_STATUS							= @OUT_STATUS			OUTPUT,
			@OUT_STATUS_STRING					= @OUT_STATUS_STRING  OUTPUT


		--Leave this here - it just keeps the assessment system from being too annoying
		INSERT INTO SASEL_SELECTION_VALUES
				(
					SELECTION_ID,
					COLUMN_ID,
					COLUMN_VALUE
				)
		SELECT   TOP 1   @SELECTION_ID,
					32000,
					TEACHER_CLASS_ID
		FROM        SA_TEACHER_CLASS_PR


		-- Create Assessment Object
		set @guid = newid()
		exec saao_sp_assessment_object_AddNew
				 @IN_ASSESSMENT_OBJECT_NAME			=	@aObjectName,
				 @IN_SOURCE_ASSESSMENT_OBJECT_ID	=	NULL,
				 @IN_ASSESSMENT_OBJECT_TYPE			=	'REPORT_CARD',
				 @IN_ENTITY_OBJECT_REPEATER_ID		=	26, 
				 @IN_SCHOOL_ID						=	NULL,
				 @IN_SCHOOLBOARD_ID					=	NULL,
				 @IN_IS_SUBLIST						=	0,
				 @IN_ALLOW_MULTIPLE_CHECKOUT		=	0,
				 @IN_COPY							=	0,
				 @IN_PARENT_ASSESSMENT_OBJECT_ID	=	NULL,
				 @IN_WEIGHT							=	NULL,
				 @IN_SORT_ORDER						=	@aObjectSortOrder,
				 @IN_SELECTION_ID					=	@SELECTION_ID,
				 @IN_DISPLAY_GROUP_ID				=	110,
				 @IN_GUID                           =   @guid,   --where does this come from
				 @IN_ASSESSMENT_OBJECT_LABEL		=	'',
				 @IN_JURISDICTION					=	'CSLF',
				 @IN_SUB_GROUP						=	'',
				 @IN_SUBJECT						=	@aObjectSubject,
				 @IN_GRADE_LEVEL					=	@aObjectGradeRange,
				 @OUT_ASSESSMENT_OBJECT_ID			=	@ASSESSMENT_OBJECT_ID  	  OUTPUT,
				 @OUT_STATUS						=	@OUT_STATUS       OUTPUT,
				 @OUT_STATUS_STRING					=	@OUT_STATUS_STRING  OUTPUT



		---------------------------------------------------------------------------------
		---------------------------------------------------------------------------------
		---------------------------------------------------------------------------------
		---------------------------------------------------------------------------------



		DECLARE aItemCursor CURSOR FOR 
		  select aItemSortOrder, aItemInputPreset, aItemName, aItemAbrev from #map 
		  where aItemGrade = @aObjectGradeRange
		  and aItemObject = @aObjectName
		    
		open aItemCursor
		fetch next from aItemCursor into @aItemSortOrder, @aItemInputPreset, @aItemName, @aItemAbrev
		while @@FETCH_STATUS=0
		begin

					set @guid = newid();
					exec saao_sp_assessment_item_AddNew
						@IN_ASSESSMENT_OBJECT_ID	=	@ASSESSMENT_OBJECT_ID,
						@IN_CODETABLE				=	NULL,
						@IN_SORTORDER				=	@aItemSortOrder,
						@IN_LABEL					=	@aItemName,
						@IN_ABREV					=	@aItemAbrev,
						@IN_ASSESSMENT_ITEM_TYPE	=	NULL,
						@IN_LINKED					=	0,
						@IN_WEIGHT					=	1,
						@IN_SOURCE_ASSESSMENT_ITEM_ID=	NULL,
						@IN_GUID                    =   @guid,
						@IN_DESCRIPTION				=	'',
						@OUT_ASSESSMENT_ITEM_ID		=	@ASSESSMENT_ITEM_ID	OUTPUT,
						@OUT_STATUS					=	@OUT_STATUS			OUTPUT,
						@OUT_STATUS_STRING			=	@OUT_STATUS_STRING	OUTPUT



					-- Define Assessment Item's input preset
					--find assesment item input presets in table: 
					exec saao_sp_assessment_item_input_config_createForAssessmentItemFromPreset
						@IN_ASSESSMENT_ITEM_ID = @ASSESSMENT_ITEM_ID,
						@IN_PRESET_ID = @aItemInputPreset,
						@OUT_STATUS = @OUT_STATUS OUTPUT,
						@OUT_STATUS_STRING = @OUT_STATUS_STRING OUTPUT
 
		 
		fetch next from aItemCursor into @aItemSortOrder, @aItemInputPreset, @aItemName, @aItemAbrev
		end
		close aItemCursor
		deallocate aItemCursor



		-- select * from saao_assessment_item_input_config 
		-- where assessment_item_id in 
		--	 (select assessment_item_id from saao_assessment_item 
		--	  where assessment_object_id = 64)

		---------------------------------------------------------------------------------
		---------------------------------------------------------------------------------
		---------------------------------------------------------------------------------
		---------------------------------------------------------------------------------



fetch next from aObjectCursor into  @aObjectGradeRange, @aObjectName, @aObjectSubject
end
close aObjectCursor
deallocate aObjectCursor




---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------




--verify
select * from saao_assessment_object 
order by assessment_object_id

select * from saao_assessment_item
order by assessment_item_id


COMMIT TRANSACTION
--ROLLBACK TRANSACTION