Requirement specification

  • A  new student import deletes old school classes and student accounts that are no longer needed but leaves still existing ones in place.
    (The simple deletion of all school classes and student accounts can be done by importing an empty CSV file.)
  • The CSV file to be imported can be passed as a command line parameter.

Download, installation and usage

The software solution is versioned on Github.com and can be downloaded: 

https://github.com/ateachment/AD-StudentManagement

Installation and user instructions can be found further down on this page.


Main program 'importStudents.py'

The file importStudents.py contains two functions each for deleting school classes and students that have become obsolete. Thereby deleteObsoleteStudentsFromAD() is called from deleteObsoleteSchoolClassesFromAD().

Further down, in the main programme, the CSV file to be imported is either specified as a command line parameter or entered in the programme itself.

 1  
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
from School import *                            # import classes
from User import *
from Csv import Csv 
from Ldap import PyAD
import sys


def deleteObsoleteSchoolClassesFromAD(school):
    ldap = PyAD(Settings.dnStudents)
    ldapClassNames = ldap.getSchoolClasses()
    classNames = []
    for schoolClass in school.getSchoolClasses():
        classNames.append(schoolClass.getName())
    for ldapClassName in ldapClassNames:
        if ldapClassName not in classNames:     # school class in ldap is obsolete
            ldap.deleteSchoolClass(ldapClassName)
        else:
            deleteObsoleteStudentsFromAD(school, ldapClassName)

def deleteObsoleteStudentsFromAD(school, className):
    ldap = PyAD(Settings.dnStudents)
    ldapStudents = ldap.getStudents(className)
    studentsNames = []
    for schoolClass in school.getSchoolClasses():
        if schoolClass.getName() == className:
            students = schoolClass.getStudents()
            for student in students:
                studentsNames.append([student.getSurname(), student.getFirstname(), student.getDateOfBirth()])
    for ldapStudent in ldapStudents:
        if ldapStudent not in studentsNames:
            ldap.deleteStudent(ldapStudent[0], ldapStudent[1], ldapStudent[2], className)



if __name__ == "__main__":
    if(len(sys.argv)) == 1:                 # no command line argument
        filename = input("CSV import file with students: ")
    else:                                   # filename as argument
        filename = sys.argv[1]
        
    school = School([])
    print(filename + " is being imported ...")
    importStudents = school.importStudentsFromCSV(filename)      # import students
    print("Delete obsolete school classes ...")
    deleteObsoleteSchoolClassesFromAD(school)
    print("Delete obsolete student accounts from the Active Directory and add new ones ...")
    school.addToLDAP()
    print("Done!")

Main program 'importStudents.py'


ldap.py

The class ldap has received 3 more methods getSchoolClasses(), getStudents() and findStudent(). They are used to find school classes and students that already exist in AD. In order to remove or preserve them if necessary:

  1  
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
 
from abc import ABC, abstractmethod     # import Abstract Base Classes (ABCs)

class AbstractLDAP(ABC):
 
    def __init__(self, dnStudents):
        self._dnStudents = dnStudents
        super().__init__()
    
    @abstractmethod
    def createStudent(self, username, nameSchoolClass):
        pass
    @abstractmethod
    def deleteStudent(self, username, nameSchoolClass):
        pass
    @abstractmethod
    def createSchoolClass(self, nameSchoolClass):
        pass
    @abstractmethod
    def deleteSchoolClass(self, nameSchoolClass):
        pass

from pyad import *
from Settings import Settings
from Fileserver import *

class PyAD(AbstractLDAP):
    def createStudent(self, username, surname, firstname, password, dateOfBirth, nameSchoolClass):
        query = adquery.ADQuery()
        cnUnique = False
        numChar = 0
        while(cnUnique == False):
            query.execute_query(attributes = ["cn"],            # query: does cn already exist
                where_clause=("cn = '" + username + "'"),
                base_dn = Settings.dnSchool)
            if query.get_row_count() > 0:                       # yes: add character
                numChar = numChar + 1
                if(numChar <= len(firstname)):                  # if the character set of the first name is large enough
                    username = surname + firstname[:numChar]    # add number of characters from beginning of first ame
                else:                                           # if the character set of the first name is used up
                    username = surname + firstname + (numChar - len(firstname))  * 'x'  # added number of 'x'
            else:                                               # cn is unique => create user
                cnUnique = True
                ouSchoolClass = pyad.adcontainer.ADContainer("ou="+nameSchoolClass+", "+self._dnStudents, adsi_ldap_com_object=None, options={})
                student = aduser.ADUser.create(username
                                     , ouSchoolClass
                                     , password=password
                                     , upn_suffix=None
                                     , enable=True
                                     , optional_attributes={"sn":surname
                                                            , "givenName":firstname
                                                            , "employeeID":dateOfBirth  # using existing employeeId attribute is easier than extending AD scheme
                                                            , "description":"Student"
                                                            , "pwdLastSet":0            # password must be changed on first login
                                                            , "homeDrive":Settings.homeDrive                                   # set home drive
                                                            , "homeDirectory":Settings.shareHomeStudents + "\\" + username})   # set home directory
                # put new student in global group of its school class
                gl_groupSchoolClass = pyad.from_dn("cn=GL_" + nameSchoolClass + ", " + Settings.dnGLGroups)   
                gl_groupSchoolClass.add_members([student])           
        return username    # return possibly modified username     

    def deleteStudent(self, surname, firstname, dateOfBirth, nameSchoolClass):
        # print("delete ", surname, firstname, dateOfBirth, nameSchoolClass)
        dnSchoolClass = "ou="+nameSchoolClass + ", " + self._dnStudents
        query = adquery.ADQuery()
        query.execute_query(attributes = ["cn"],
                where_clause=("sn = '" + surname + "' and givenName = '" + firstname + "' and employeeID = '" + dateOfBirth + "'"),
                base_dn = dnSchoolClass)
        if query.get_row_count() >= 1:                              # if student exists => delete it
            ouSchoolClass = pyad.adcontainer.ADContainer(dnSchoolClass, adsi_ldap_com_object=None, options={})
            fs = Fileserver()
            for row in query.get_results():
                ouSchoolClass.from_cn(row["cn"]).delete()
                fs.deleteHomeDirStudent(row["cn"])
   
    def createSchoolClass(self, nameSchoolClass):
        query = adquery.ADQuery()
        query.execute_query(attributes = ["distinguishedName"],
                where_clause=("ou = '" +nameSchoolClass + "'"),
                base_dn = self._dnStudents)
        if query.get_row_count() == 0:                              # if school class not yet exists => create
            ouStudents = pyad.adcontainer.ADContainer(self._dnStudents, adsi_ldap_com_object=None, options={})
            ouStudents.create_container(nameSchoolClass)
            # create a new global group for students of school class
            ouGLgroups = pyad.adcontainer.ADContainer.from_dn(Settings.dnGLGroups)
            gl_groupSchoolClass = ouGLgroups.create_group("GL_" + nameSchoolClass
                                                 , security_enabled=True
                                                 , scope='GLOBAL'
                                                 , optional_attributes = {"description":"Students of school class " + nameSchoolClass})
            # create a new domaine local for project dir of students
            ouDLgroups = pyad.adcontainer.ADContainer.from_dn(Settings.dnDLGroups)
            dl_groupSchoolClass = ouDLgroups.create_group("DL_" + nameSchoolClass + "_F" 
                                            , security_enabled=True
                                            , scope='LOCAL'
                                            , optional_attributes = {"description":"Full access for project dir of students of school class " + nameSchoolClass})
            
            gl_groupStudents = pyad.from_dn("cn=GL_Students, " + Settings.dnGLGroups) # put global group of school class
            gl_groupStudents.add_members([gl_groupSchoolClass])             # into global group of all students "GL_Students" and
            dl_groupSchoolClass.add_members([gl_groupSchoolClass])          # into domaine local group of students of a school class
            # create folder and share
            fs = Fileserver()
            fs.createProjectDirStudent(nameSchoolClass)
            fs.addShareProjectDirStudent(nameSchoolClass)
 
    def deleteSchoolClass(self, nameSchoolClass):
        dnSchoolClass = "ou="+nameSchoolClass+", "+ self._dnStudents
        query = adquery.ADQuery()
        query.execute_query(attributes = ["distinguishedName"],
                where_clause=("ou = '" +nameSchoolClass + "'"),
                base_dn = self._dnStudents)
        if query.get_row_count() == 1:                          # if school class exists
            query = adquery.ADQuery()
            query.execute_query(attributes = ["sn", "givenName", "employeeID"],  # employeeID holds date of birth
                where_clause=("cn = '*'"),
                base_dn = dnSchoolClass)
            if query.get_row_count() > 0:                       # if school class not empty 
                for row in query.get_results():                 # delete students
                    self.deleteStudent(row["sn"], row["givenName"], row["employeeID"],  nameSchoolClass)
            ouSchoolClass = pyad.adcontainer.ADContainer(dnSchoolClass, adsi_ldap_com_object=None, options={})
            ouSchoolClass.delete()                              # delete school class ou
            # delete global and domaine local groups of school class
            gl_groupSchoolClass = pyad.from_dn("cn=GL_" + nameSchoolClass + ", " + Settings.dnGLGroups) 
            gl_groupSchoolClass.delete()
            dl_groupSchoolClass = pyad.from_dn("cn=DL_" + nameSchoolClass + "_F" + ", " + Settings.dnDLGroups) 
            dl_groupSchoolClass.delete()
            # delete folder and share
            fs = Fileserver()
            fs.deleteProjectDirStudent(nameSchoolClass)
            fs.deleteShareProjectDirStudent(nameSchoolClass)
            
    def getSchoolClasses(self):
        query = adquery.ADQuery()
        query.execute_query(attributes = ["ou"],
            where_clause=("ou = '*' and ou <> 'Students'"),     # exclude users and base dn
            base_dn = self._dnStudents)
        schoolClasses = []
        result = query.get_results()        
        for schoolClass in result:
            schoolClasses.append(schoolClass['ou'][0])
        return schoolClasses
    
    def getStudents(self, nameSchoolClass):
        dnSchoolClass = "ou="+nameSchoolClass + ", " + self._dnStudents
        query = adquery.ADQuery()
        query.execute_query(attributes = ['sn', 'givenName', 'employeeID'],
                    # surname, firstname, employeeID holds date of birth       
        # query.execute_query(attributes = ['cn', 'sn', 'givenName', 'employeeID'],  
                    # username, surname, firstname, employeeID holds date of birth       
            where_clause=("ou <> '" + nameSchoolClass + "'"),     # exclude base dn   
            base_dn = dnSchoolClass)
        students = []
        result = query.get_results()       
        for student in result:
            # students.append(student['cn'], [student['sn'], student['givenName'], student['employeeID']])
            students.append([student['sn'], student['givenName'], student['employeeID']])
        return students
    
    def findStudent(self, surname, firstname, dateOfBirth):
        # print("find ", surname, firstname, dateOfBirth)
        query = adquery.ADQuery()
        query.execute_query(attributes = ["distinguishedName"],
                where_clause=("sn = '" + surname + "' and givenName = '" + firstname + "' and employeeID = '" + dateOfBirth + "'"),
                base_dn = Settings.dnStudents)
        if query.get_row_count() >= 1:                              
            for row in query.get_results():
                # i.e. row["distinguishedName"] = CN=Colligan,OU=c1,OU=Students,OU=SchoolUsers,DC=trainingX,DC=net
                className = row["distinguishedName"].split(",")[1].split("=")[1]
                return className
        return ""   # not found -> not already in other school class

ldap.py


User.py

The addToLDAP() method has been improved here. Depending on whether and where a student already exists, different measures are taken:

If the student ...

  • already exists in its correct school class in AD, nothing needs to be done.
  • does not yet exist anywhere in AD, it is simply created in its school class.
  • already exists in another school class, it is simply deleted for the sake of simplicity and then recreated in its new school class. (A move to the new school class with all its data would be an improvement for a future version of the software).
 1  
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

from Settings import Settings
from Ldap import PyAD
from Fileserver import *

class User:                                                 # superclass "User"
    def __init__(self, surname, firstname, dateOfBirth):    # constructor 
        self._surname = surname                             # protected properties/attributes => only one "_"
        self._firstname = firstname
        self._password = firstname[0] + surname[0] + dateOfBirth  
        self._dateOfBirth = dateOfBirth
        self._username = surname 
        
    def getSurname(self):                                  
        return self._surname
    
    def getFirstname(self):
        return self._firstname

    def getPassword(self):
        return self._password

    def getDateOfBirth(self):
        return self._dateOfBirth

    def getUsername(self):
        return self._username


class Student(User):    # subclass "Student" inherits every property and method from superclass "User"
    def __init__(self, surname, firstname, dateOfBirth, schoolClass):
        User.__init__(self, surname, firstname, dateOfBirth)  # constructor of superclass is invoked
        schoolClass.addStudent(self);   # adds himself to school class
        self.__schoolClass = schoolClass

    def getSchoolClass(self):              
        return self.__schoolClass

    def addToLDAP(self):
        ldap = PyAD(Settings.dnStudents)
        # add to ldap if not already in school class
        foundStudentSchoolClass = ldap.findStudent(self._surname , self._firstname, self._dateOfBirth)
        if foundStudentSchoolClass == self.__schoolClass.getName():
            pass # do nothing
        elif foundStudentSchoolClass == "": # not already in another school class -> create student
            # ldap.createStudent() returns the possibly modified username for updating
            self._username = ldap.createStudent(self._username
                               , self._surname
                               , self._firstname
                               , self._password
                               , self._dateOfBirth
                               , self.__schoolClass.getName())

            fs = Fileserver()
            fs.createHomeDirStudent(self._username)   
        else:   # already in another school class 
                # -> delete and recreate student for simplicity reason
            ldap = PyAD(Settings.dnStudents)
            ldap.deleteStudent(self._surname, self._firstname, self._dateOfBirth, foundStudentSchoolClass)
            fs = Fileserver()
            fs.deleteHomeDirStudent(self._username)
            self._username = ldap.createStudent(self._username
                               , self._surname
                               , self._firstname
                               , self._password
                               , self._dateOfBirth
                               , self.__schoolClass.getName())
    
    def deleteFromLDAP(self):
        ldap = PyAD(Settings.dnStudents)
        ldap.deleteStudent(self._surname, self._firstname, self._dateOfBirth, self.__schoolClass.getName())

        fs = Fileserver()
        fs.deleteHomeDirStudent(self._username)

User.py


Test program 'testImportStudents.py'

Automatically executed unit tests are used here, which test the methods of the classes for their expected return values.
Attention all existing school classes and students in the AD are deleted in the process. Therefore, so that the test programme is not executed by mistake, programme lines 2 and 3 must first be commented out. Only then is the file executable.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
# filename: testImportStudents.py 
CAUTION: Running the Unittest file will delete all school classes and students from AD. 
Do NOT run the tests in production mode.

from School import *                        
from User import *
from Fileserver import *
from Ldap import PyAD
import importStudents 

import unittest                                     # import unit tests


class TestImportStudents(unittest.TestCase):
    @classmethod
    def setUpClass(self):                           # is executed once for all tests    
        self.schoolClass1 = SchoolClass("c1",[])         
        self.schoolClass2 = SchoolClass("c2",[])

        self.student1 = Student("Miller","Sam","22.11.2004",self.schoolClass1)  
        self.student2 = Student("Smith","Jake","13.02.2003",self.schoolClass1)
        self.student3 = Student("Hampton","Oliver","11.01.2004",self.schoolClass2)

        self.school = School([self.schoolClass1, self.schoolClass2]) 
    
    def test_School(self):                      # test names must start with 'test_' otherwise will not executed
        schoolClasses = self.school.getSchoolClasses()
        self.assertEqual(len(schoolClasses), 2, 'Number of school classes wrong.')
        self.assertEqual(schoolClasses[0].getName(), "c1", '1st school class wrong.')
        self.assertEqual(schoolClasses[1].getName(), "c2", '2nd school class wrong.')
        students = self.schoolClass1.getStudents()
        self.assertEqual(len(students), 2, 'Number of students wrong.')
        self.assertEqual(students[0].getUsername(),'Miller', 'Students username wrong.')

    def test_AddToLDAP(self):
        # remove school classes and/or students if there are any left in AD
        ldap = PyAD(Settings.dnStudents) 
        classNames = ldap.getSchoolClasses()
        for className in classNames:
            # print("delete school class: " + className)
            ldap.deleteSchoolClass(className)
        
        self.school.addToLDAP()
        ldap = PyAD(Settings.dnStudents)
        classNames = ldap.getSchoolClasses()
        self.assertEqual(classNames, ['c1', 'c2'], 'From ldap returned school classes wrong.')
        students = ldap.getStudents('c1')
        self.assertEqual(students, [["Miller", "Sam", "22.11.2004"], ["Smith", "Jake", "13.02.2003"]], 'From ldap returned students wrong.')
        # clean up
        schoolClasses = self.school.getSchoolClasses()
        for schoolClass in schoolClasses:
            schoolClass.deleteFromLDAP()
        classNames = ldap.getSchoolClasses()
        self.assertEqual(classNames, [], 'Ldap returns still school classe(s). Still not empty.')

    def test_ImportCsvFile(self):
        # remove school classes and/or students if there are any left in AD
        ldap = PyAD(Settings.dnStudents) 
        classNames = ldap.getSchoolClasses()
        for className in classNames:
            ldap.deleteSchoolClass(className)
        
        school = School([])
        school.importStudentsFromCSV("import/students.csv")
        school.addToLDAP()

        classNames = ldap.getSchoolClasses()
        
        self.assertEqual(classNames, ['c1', 'c2'], 'From ldap returned school classes wrong.')
        students = ldap.getStudents('c1')
        self.assertEqual(students, [["Colligan", "Manda", "22.09.2004"], ["Thieme", "Madelaine", "02.12.2000"]], 'From ldap returned students wrong.')
        
        school2 = School([])
        school2.importStudentsFromCSV("import/students2.csv")
        importStudents.deleteObsoleteSchoolClassesFromAD(school2)  # 'c2' will be deleted

        classNames = ldap.getSchoolClasses()
        self.assertEqual(classNames, ['c1'], 'From ldap returned school classes wrong.')

        school2.addToLDAP()
        classNames = ldap.getSchoolClasses()
        self.assertEqual(classNames, ['c1', 'c3'], 'From ldap returned school classes wrong.')
        students = ldap.getStudents('c1')
        self.assertEqual(students, [["Colligan", "Manda", "22.09.2004"], ["Belafonte", "Harry", "01.03.1927"]], 'From ldap returned students wrong.')
        students = ldap.getStudents('c3')
        self.assertEqual(students, [["Tarleton","Vito","22.02.2004"]], 'From ldap returned students wrong.')

        # clean up
        classNames = ldap.getSchoolClasses()
        for className in classNames:
            ldap.deleteSchoolClass(className)

if __name__ == '__main__':
    unittest.main()

testImportStudents.py


All other programme files remain unchanged and are explained in sections above.