jonwitts

iSAMS to Moodle View v0.4

Oct 5th, 2015
68
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT        dbo.TblPupilManagementPupils.txtForename, dbo.TblPupilManagementPupils.txtSurname, dbo.TblPupilManagementPupils.txtEmailAddress,
  2.                          dbo.TblTeachingManagerSubjects.txtSubjectName, dbo.TblTeachingManagerSets.txtSetCode + ' - ' +
  3.                              (SELECT        (RIGHT(CONVERT(VARCHAR(12),
  4.                                                              (SELECT        [txtStartYear]
  5.                                                                FROM            [iSAMS].[dbo].[TblTimetableManagerTimetables]
  6.                                                                WHERE        intPublished = '1')), 2) + '-' + RIGHT(CONVERT(VARCHAR(12), (CONVERT(INT,
  7.                                                              (SELECT        [txtStartYear]
  8.                                                                FROM            [iSAMS].[dbo].[TblTimetableManagerTimetables]
  9.                                                                WHERE        intPublished = '1') + CONVERT(INT, 1)))), 2)) AS txtNCYearRange) AS txtSetCode, '5' AS intRoleID
  10. FROM            dbo.TblTeachingManagerSubjects INNER JOIN
  11.                          dbo.TblTeachingManagerSets ON dbo.TblTeachingManagerSubjects.TblTeachingManagerSubjectsID = dbo.TblTeachingManagerSets.intSubject INNER JOIN
  12.                          dbo.TblTeachingManagerSetLists ON dbo.TblTeachingManagerSets.TblTeachingManagerSetsID = dbo.TblTeachingManagerSetLists.intSetID INNER JOIN
  13.                          dbo.TblPupilManagementPupils ON dbo.TblTeachingManagerSetLists.txtSchoolID = dbo.TblPupilManagementPupils.txtSchoolID
  14. WHERE        (dbo.TblPupilManagementPupils.intSystemStatus = 1) AND (dbo.TblPupilManagementPupils.txtEmailAddress IS NOT NULL) AND
  15.                          (dbo.TblTeachingManagerSubjects.txtSubjectName <> 'Private Study') AND (dbo.TblTeachingManagerSubjects.txtSubjectName <> 'Non Class')
  16. UNION ALL
  17. SELECT        TblStaff.Firstname AS txtForename, TblStaff.Surname AS txtSurname, TblStaff.SchoolEmailAddress AS txtEmailAddress,
  18.                          TblTeachingManagerSubjects.txtSubjectName, dbo.TblTeachingManagerSets.txtSetCode + ' - ' +
  19.                              (SELECT        (RIGHT(CONVERT(VARCHAR(12),
  20.                                                              (SELECT        [txtStartYear]
  21.                                                                FROM            [iSAMS].[dbo].[TblTimetableManagerTimetables]
  22.                                                                WHERE        intPublished = '1')), 2) + '-' + RIGHT(CONVERT(VARCHAR(12), (CONVERT(INT,
  23.                                                              (SELECT        [txtStartYear]
  24.                                                                FROM            [iSAMS].[dbo].[TblTimetableManagerTimetables]
  25.                                                                WHERE        intPublished = '1') + CONVERT(INT, 1)))), 2)) AS txtNCYearRange) AS txtSetCode, '3' AS intRoleID
  26. FROM            TblTeachingManagerSets INNER JOIN
  27.                          TblTeachingManagerSubjects ON TblTeachingManagerSets.intSubject = TblTeachingManagerSubjects.TblTeachingManagerSubjectsID LEFT OUTER JOIN
  28.                          TblStaff ON TblTeachingManagerSets.txtTeacher = TblStaff.User_Code
  29. WHERE        (dbo.TblTeachingManagerSets.txtTeacher IS NOT NULL) AND (TblTeachingManagerSubjects.txtSubjectName <> 'Private Study') AND
  30.                          (dbo.TblTeachingManagerSubjects.txtSubjectName <> 'Non Class')
  31. UNION ALL
  32. SELECT        TblAssStaff.Firstname AS txtForename, TblAssStaff.Surname AS txtSurname, TblAssStaff.SchoolEmailAddress AS txtEmailAddress,
  33.                          TblTeachingManagerSubjects.txtSubjectName, dbo.TblTeachingManagerSets.txtSetCode + ' - ' +
  34.                              (SELECT        (RIGHT(CONVERT(VARCHAR(12),
  35.                                                              (SELECT        [txtStartYear]
  36.                                                                FROM            [iSAMS].[dbo].[TblTimetableManagerTimetables]
  37.                                                                WHERE        intPublished = '1')), 2) + '-' + RIGHT(CONVERT(VARCHAR(12), (CONVERT(INT,
  38.                                                              (SELECT        [txtStartYear]
  39.                                                                FROM            [iSAMS].[dbo].[TblTimetableManagerTimetables]
  40.                                                                WHERE        intPublished = '1') + CONVERT(INT, 1)))), 2)) AS txtNCYearRange) AS txtSetCode, '3' AS intRoleID
  41. FROM            TblStaff AS TblAssStaff INNER JOIN
  42.                          TblTeachingManagerSetAssociatedTeachers ON TblAssStaff.User_Code = TblTeachingManagerSetAssociatedTeachers.txtTeacher INNER JOIN
  43.                          TblTeachingManagerSubjects INNER JOIN
  44.                          TblTeachingManagerSets ON TblTeachingManagerSubjects.TblTeachingManagerSubjectsID = TblTeachingManagerSets.intSubject ON
  45.                          TblTeachingManagerSetAssociatedTeachers.intSetID = TblTeachingManagerSets.TblTeachingManagerSetsID
  46. WHERE        (dbo.TblTeachingManagerSetAssociatedTeachers.txtTeacher IS NOT NULL) AND (TblTeachingManagerSubjects.txtSubjectName <> 'Private Study') AND
  47.                          (dbo.TblTeachingManagerSubjects.txtSubjectName <> 'Non Class')
  48. UNION
  49. SELECT DISTINCT
  50.                          TblStaff.Firstname AS txtForename, TblStaff.Surname AS txtSurname, TblStaff.SchoolEmailAddress AS txtEmailAddress,
  51.                          TblTeachingManagerSubjects.txtSubjectName + ' - Staff Area' AS txtSubjectName, LEFT('Stf/' + REPLACE(TblTeachingManagerSubjects.txtSubjectName, ' ', ''), 100)
  52.                          AS txtSetCode, '3' AS intRoleID
  53. FROM            TblTeachingManagerSets INNER JOIN
  54.                          TblTeachingManagerSubjects ON TblTeachingManagerSets.intSubject = TblTeachingManagerSubjects.TblTeachingManagerSubjectsID LEFT OUTER JOIN
  55.                          TblStaff ON TblTeachingManagerSets.txtTeacher = TblStaff.User_Code
  56. WHERE        (dbo.TblTeachingManagerSets.txtTeacher IS NOT NULL) AND (TblTeachingManagerSubjects.txtSubjectName <> 'Private Study') AND
  57.                          (dbo.TblTeachingManagerSubjects.txtSubjectName <> 'Non Class')
  58. UNION
  59. SELECT DISTINCT
  60.                          TblAssStaff.Firstname AS txtForename, TblAssStaff.Surname AS txtSurname, TblAssStaff.SchoolEmailAddress AS txtEmailAddress,
  61.                          TblTeachingManagerSubjects.txtSubjectName + ' - Staff Area' AS txtSubjectName, LEFT('Stf/' + REPLACE(TblTeachingManagerSubjects.txtSubjectName, ' ', ''), 100)
  62.                          AS txtSetCode, '3' AS intRoleID
  63. FROM            TblStaff AS TblAssStaff INNER JOIN
  64.                          TblTeachingManagerSetAssociatedTeachers ON TblAssStaff.User_Code = TblTeachingManagerSetAssociatedTeachers.txtTeacher INNER JOIN
  65.                          TblTeachingManagerSubjects INNER JOIN
  66.                          TblTeachingManagerSets ON TblTeachingManagerSubjects.TblTeachingManagerSubjectsID = TblTeachingManagerSets.intSubject ON
  67.                          TblTeachingManagerSetAssociatedTeachers.intSetID = TblTeachingManagerSets.TblTeachingManagerSetsID
  68. WHERE        (dbo.TblTeachingManagerSetAssociatedTeachers.txtTeacher IS NOT NULL) AND (TblTeachingManagerSubjects.txtSubjectName <> 'Private Study') AND
  69.                          (dbo.TblTeachingManagerSubjects.txtSubjectName <> 'Non Class')
  70. UNION ALL
  71. SELECT DISTINCT
  72.                          dbo.TblPupilManagementPupils.txtForename, dbo.TblPupilManagementPupils.txtSurname, dbo.TblPupilManagementPupils.txtEmailAddress,
  73.                          dbo.TblSchoolManagementYears.txtYearName AS txtSubjectName, dbo.TblSchoolManagementYears.txtYearName AS txtSetCode, '5' AS intRoleID
  74. FROM            dbo.TblSchoolManagementYears INNER JOIN
  75.                          dbo.TblPupilManagementPupils ON dbo.TblSchoolManagementYears.intNCYear = dbo.TblPupilManagementPupils.intNCYear
  76. WHERE        (dbo.TblPupilManagementPupils.intSystemStatus = 1) AND (dbo.TblPupilManagementPupils.txtEmailAddress IS NOT NULL)
  77. UNION ALL
  78. SELECT DISTINCT
  79.                          Firstname AS txtForename, Surname AS txtSurname, SchoolEmailAddress AS txtEmailAddress, 'iSAMS_Staff' AS txtSubjectName, 'iSAMS_Staff' AS txtSetCode,
  80.                          '5' AS intRoleID
  81. FROM            dbo.TblStaff
  82. WHERE        (SchoolEmailAddress IS NOT NULL) AND (SchoolEmailAddress <> '') AND (SystemStatus = '1')
  83. UNION ALL
  84. SELECT DISTINCT       dbo.TblPupilManagementPupils.txtForename, dbo.TblPupilManagementPupils.txtSurname,
  85.                 dbo.TblPupilManagementPupils.txtEmailAddress,
  86.                 dbo.TblPupilManagementPupils.txtAcademicHouse AS txtSubjectName,
  87.                 dbo.TblPupilManagementPupils.txtAcademicHouse AS txtSetCode,
  88.                 '5' AS intRoleID
  89. FROM            dbo.TblPupilManagementPupils
  90. WHERE (dbo.TblPupilManagementPupils.intSystemStatus = 1)
  91.     AND (dbo.TblPupilManagementPupils.txtEmailAddress IS NOT NULL)
  92. UNION ALL
  93. SELECT        TblStaff.Firstname AS txtForename,
  94.             TblStaff.Surname AS txtSurname,
  95.             TblStaff.SchoolEmailAddress AS txtEmailAddress,
  96.             TblSchoolManagementHouses.txtHouseName AS txtSubjectName,
  97.             TblSchoolManagementHouses.txtHouseName AS txtSetCode,
  98.             '5' AS intRoleID
  99. FROM            TblSchoolManagementHouses INNER JOIN
  100.                          TblStaffManagementAcademicHouses ON
  101.                          TblSchoolManagementHouses.TblSchoolManagementHousesID = TblStaffManagementAcademicHouses.intAcademicHouse RIGHT OUTER JOIN
  102.                          TblStaff ON TblStaffManagementAcademicHouses.intStaff = TblStaff.TblStaffID
  103. WHERE (SchoolEmailAddress IS NOT NULL)
  104.     AND (SchoolEmailAddress <> '')
  105.     AND (SystemStatus = '1')
  106.     AND (txtHouseName IS NOT NULL)
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×