Config file for displaying iSAMS Tutorial Groups in Firefly VLE

isams-tutorial-groups-mis.config

<?xml version="1.0" encoding="UTF-8"?>
<database name="YourDBName" friendlyName="iSAMS Tutorial Groups">
   <querySets>
      <!-- ## tutorial groups relationships ## -->
      <querySet guidPrefix="Tutorial">
         <!-- Return a list of groups for the specified member guid -->
         <groupLists>
            <groupList namePrefix="Tutorial Group ">
               <query>
			   
			   <![CDATA[
			   SELECT convert(varchar(50), TblTimetableManagerTutorialsID) as [guid],
				txtDisplayName +' (' + convert(varchar,p.intDay) + ') ' +p.txtName as [name],
				'group' as [type]
				FROM
				TblTimetableManagerTutorials t inner join tblTimetableManagerTutorialsPeriods tp
				on t.TblTimetableManagerTutorialsID = tp.TblTimetableManagerTutorialsPeriodsId
				inner join TblTimetableManagerPeriods p on tp.TblTimetableManagerTutorialsPeriodsId
				= p.TblTimetableManagerPeriodsID				
				]]>	
				
			   </query>
            </groupList>
         </groupLists>
         <relationships>
            <relationship type="IsMember">
               <query>
			   
			   <![CDATA[
			   SELECT distinct convert(varchar(50), TblTimetableManagerTutorialsID) as [guid],
				'group' as [type] from TblTimetableManagerTutorialsPupils tp inner join TblTimetableManagerTutorials t
				on tp.intTutorial = t.TblTimetableManagerTutorialsId inner join TblPupilManagementPupils p on
				tp.txtSchoolId = p.txtSchoolID
				WHERE
				(p.txtUsername = @Username OR (p.txtUsername IS NULL AND (p.txtUsername = @Username OR (p.txtUsername IS NULL 
				AND (p.txtEmailAddress = @Username OR (p.txtEmailAddress IS NULL AND p.txtEmailAddress = @Username))))))
				AND	p.intSystemStatus = 1
				]]>
				
			   </query>
            </relationship>
            <!-- Return a list of members for the specified group guid -->
            <relationship type="Container">
               <query>
			   
			   <![CDATA[
			   SELECT distinct ISNULL(p.txtUsername, ISNULL(p.txtUsername, ISNULL(p.txtEmailAddress, p.txtEmailAddress))) as [username],
				'user' as [type] from TblTimetableManagerTutorialsPupils tp inner join TblTimetableManagerTutorials t
				on tp.intTutorial = t.TblTimetableManagerTutorialsId inner join TblPupilManagementPupils p on
				tp.txtSchoolId = p.txtSchoolID
				WHERE
				t.TblTimetableManagerTutorialsId = convert(int,@GUID)
				AND	p.intSystemStatus = 1
				]>
			   
			   </query>
            </relationship>
         </relationships>
      </querySet>
      <!-- ## end tutorial groups relationships ## -->
   </querySets>
</database>