iSAMS to Moodle View v0.4

SELECT        dbo.TblPupilManagementPupils.txtForename, dbo.TblPupilManagementPupils.txtSurname, dbo.TblPupilManagementPupils.txtEmailAddress,
                         dbo.TblTeachingManagerSubjects.txtSubjectName, dbo.TblTeachingManagerSets.txtSetCode + ' - ' +
                             (SELECT        (RIGHT(CONVERT(VARCHAR(12),
                                                             (SELECT        [txtStartYear]
                                                               FROM            [iSAMS].[dbo].[TblTimetableManagerTimetables]
                                                               WHERE        intPublished = '1')), 2) + '-' + RIGHT(CONVERT(VARCHAR(12), (CONVERT(INT,
                                                             (SELECT        [txtStartYear]
                                                               FROM            [iSAMS].[dbo].[TblTimetableManagerTimetables]
                                                               WHERE        intPublished = '1') + CONVERT(INT, 1)))), 2)) AS txtNCYearRange) AS txtSetCode, '5' AS intRoleID
FROM            dbo.TblTeachingManagerSubjects INNER JOIN
                         dbo.TblTeachingManagerSets ON dbo.TblTeachingManagerSubjects.TblTeachingManagerSubjectsID = dbo.TblTeachingManagerSets.intSubject INNER JOIN
                         dbo.TblTeachingManagerSetLists ON dbo.TblTeachingManagerSets.TblTeachingManagerSetsID = dbo.TblTeachingManagerSetLists.intSetID INNER JOIN
                         dbo.TblPupilManagementPupils ON dbo.TblTeachingManagerSetLists.txtSchoolID = dbo.TblPupilManagementPupils.txtSchoolID
WHERE        (dbo.TblPupilManagementPupils.intSystemStatus = 1) AND (dbo.TblPupilManagementPupils.txtEmailAddress IS NOT NULL) AND
                         (dbo.TblTeachingManagerSubjects.txtSubjectName <> 'Private Study') AND (dbo.TblTeachingManagerSubjects.txtSubjectName <> 'Non Class')
UNION ALL
SELECT        TblStaff.Firstname AS txtForename, TblStaff.Surname AS txtSurname, TblStaff.SchoolEmailAddress AS txtEmailAddress,
                         TblTeachingManagerSubjects.txtSubjectName, dbo.TblTeachingManagerSets.txtSetCode + ' - ' +
                             (SELECT        (RIGHT(CONVERT(VARCHAR(12),
                                                             (SELECT        [txtStartYear]
                                                               FROM            [iSAMS].[dbo].[TblTimetableManagerTimetables]
                                                               WHERE        intPublished = '1')), 2) + '-' + RIGHT(CONVERT(VARCHAR(12), (CONVERT(INT,
                                                             (SELECT        [txtStartYear]
                                                               FROM            [iSAMS].[dbo].[TblTimetableManagerTimetables]
                                                               WHERE        intPublished = '1') + CONVERT(INT, 1)))), 2)) AS txtNCYearRange) AS txtSetCode, '3' AS intRoleID
FROM            TblTeachingManagerSets INNER JOIN
                         TblTeachingManagerSubjects ON TblTeachingManagerSets.intSubject = TblTeachingManagerSubjects.TblTeachingManagerSubjectsID LEFT OUTER JOIN
                         TblStaff ON TblTeachingManagerSets.txtTeacher = TblStaff.User_Code
WHERE        (dbo.TblTeachingManagerSets.txtTeacher IS NOT NULL) AND (TblTeachingManagerSubjects.txtSubjectName <> 'Private Study') AND
                         (dbo.TblTeachingManagerSubjects.txtSubjectName <> 'Non Class')
UNION ALL
SELECT        TblAssStaff.Firstname AS txtForename, TblAssStaff.Surname AS txtSurname, TblAssStaff.SchoolEmailAddress AS txtEmailAddress,
                         TblTeachingManagerSubjects.txtSubjectName, dbo.TblTeachingManagerSets.txtSetCode + ' - ' +
                             (SELECT        (RIGHT(CONVERT(VARCHAR(12),
                                                             (SELECT        [txtStartYear]
                                                               FROM            [iSAMS].[dbo].[TblTimetableManagerTimetables]
                                                               WHERE        intPublished = '1')), 2) + '-' + RIGHT(CONVERT(VARCHAR(12), (CONVERT(INT,
                                                             (SELECT        [txtStartYear]
                                                               FROM            [iSAMS].[dbo].[TblTimetableManagerTimetables]
                                                               WHERE        intPublished = '1') + CONVERT(INT, 1)))), 2)) AS txtNCYearRange) AS txtSetCode, '3' AS intRoleID
FROM            TblStaff AS TblAssStaff INNER JOIN
                         TblTeachingManagerSetAssociatedTeachers ON TblAssStaff.User_Code = TblTeachingManagerSetAssociatedTeachers.txtTeacher INNER JOIN
                         TblTeachingManagerSubjects INNER JOIN
                         TblTeachingManagerSets ON TblTeachingManagerSubjects.TblTeachingManagerSubjectsID = TblTeachingManagerSets.intSubject ON
                         TblTeachingManagerSetAssociatedTeachers.intSetID = TblTeachingManagerSets.TblTeachingManagerSetsID
WHERE        (dbo.TblTeachingManagerSetAssociatedTeachers.txtTeacher IS NOT NULL) AND (TblTeachingManagerSubjects.txtSubjectName <> 'Private Study') AND
                         (dbo.TblTeachingManagerSubjects.txtSubjectName <> 'Non Class')
UNION
SELECT DISTINCT
                         TblStaff.Firstname AS txtForename, TblStaff.Surname AS txtSurname, TblStaff.SchoolEmailAddress AS txtEmailAddress,
                         TblTeachingManagerSubjects.txtSubjectName + ' - Staff Area' AS txtSubjectName, LEFT('Stf/' + REPLACE(TblTeachingManagerSubjects.txtSubjectName, ' ', ''), 100)
                         AS txtSetCode, '3' AS intRoleID
FROM            TblTeachingManagerSets INNER JOIN
                         TblTeachingManagerSubjects ON TblTeachingManagerSets.intSubject = TblTeachingManagerSubjects.TblTeachingManagerSubjectsID LEFT OUTER JOIN
                         TblStaff ON TblTeachingManagerSets.txtTeacher = TblStaff.User_Code
WHERE        (dbo.TblTeachingManagerSets.txtTeacher IS NOT NULL) AND (TblTeachingManagerSubjects.txtSubjectName <> 'Private Study') AND
                         (dbo.TblTeachingManagerSubjects.txtSubjectName <> 'Non Class')
UNION
SELECT DISTINCT
                         TblAssStaff.Firstname AS txtForename, TblAssStaff.Surname AS txtSurname, TblAssStaff.SchoolEmailAddress AS txtEmailAddress,
                         TblTeachingManagerSubjects.txtSubjectName + ' - Staff Area' AS txtSubjectName, LEFT('Stf/' + REPLACE(TblTeachingManagerSubjects.txtSubjectName, ' ', ''), 100)
                         AS txtSetCode, '3' AS intRoleID
FROM            TblStaff AS TblAssStaff INNER JOIN
                         TblTeachingManagerSetAssociatedTeachers ON TblAssStaff.User_Code = TblTeachingManagerSetAssociatedTeachers.txtTeacher INNER JOIN
                         TblTeachingManagerSubjects INNER JOIN
                         TblTeachingManagerSets ON TblTeachingManagerSubjects.TblTeachingManagerSubjectsID = TblTeachingManagerSets.intSubject ON
                         TblTeachingManagerSetAssociatedTeachers.intSetID = TblTeachingManagerSets.TblTeachingManagerSetsID
WHERE        (dbo.TblTeachingManagerSetAssociatedTeachers.txtTeacher IS NOT NULL) AND (TblTeachingManagerSubjects.txtSubjectName <> 'Private Study') AND
                         (dbo.TblTeachingManagerSubjects.txtSubjectName <> 'Non Class')
UNION ALL
SELECT DISTINCT
                         dbo.TblPupilManagementPupils.txtForename, dbo.TblPupilManagementPupils.txtSurname, dbo.TblPupilManagementPupils.txtEmailAddress,
                         dbo.TblSchoolManagementYears.txtYearName AS txtSubjectName, dbo.TblSchoolManagementYears.txtYearName AS txtSetCode, '5' AS intRoleID
FROM            dbo.TblSchoolManagementYears INNER JOIN
                         dbo.TblPupilManagementPupils ON dbo.TblSchoolManagementYears.intNCYear = dbo.TblPupilManagementPupils.intNCYear
WHERE        (dbo.TblPupilManagementPupils.intSystemStatus = 1) AND (dbo.TblPupilManagementPupils.txtEmailAddress IS NOT NULL)
UNION ALL
SELECT DISTINCT
                         Firstname AS txtForename, Surname AS txtSurname, SchoolEmailAddress AS txtEmailAddress, 'iSAMS_Staff' AS txtSubjectName, 'iSAMS_Staff' AS txtSetCode,
                         '5' AS intRoleID
FROM            dbo.TblStaff
WHERE        (SchoolEmailAddress IS NOT NULL) AND (SchoolEmailAddress <> '') AND (SystemStatus = '1')
UNION ALL
SELECT DISTINCT       dbo.TblPupilManagementPupils.txtForename, dbo.TblPupilManagementPupils.txtSurname,
                dbo.TblPupilManagementPupils.txtEmailAddress,
                dbo.TblPupilManagementPupils.txtAcademicHouse AS txtSubjectName,
                dbo.TblPupilManagementPupils.txtAcademicHouse AS txtSetCode,
                '5' AS intRoleID
FROM            dbo.TblPupilManagementPupils
WHERE (dbo.TblPupilManagementPupils.intSystemStatus = 1)
    AND (dbo.TblPupilManagementPupils.txtEmailAddress IS NOT NULL)
UNION ALL
SELECT        TblStaff.Firstname AS txtForename,
            TblStaff.Surname AS txtSurname,
            TblStaff.SchoolEmailAddress AS txtEmailAddress,
            TblSchoolManagementHouses.txtHouseName AS txtSubjectName,
            TblSchoolManagementHouses.txtHouseName AS txtSetCode,
            '5' AS intRoleID
FROM            TblSchoolManagementHouses INNER JOIN
                         TblStaffManagementAcademicHouses ON
                         TblSchoolManagementHouses.TblSchoolManagementHousesID = TblStaffManagementAcademicHouses.intAcademicHouse RIGHT OUTER JOIN
                         TblStaff ON TblStaffManagementAcademicHouses.intStaff = TblStaff.TblStaffID
WHERE (SchoolEmailAddress IS NOT NULL)
    AND (SchoolEmailAddress <> '')
    AND (SystemStatus = '1')
    AND (txtHouseName IS NOT NULL)