View source for Integrate Moodle, LDAP and SIMS.net

'''To fully integrating Moodle, SIMS.net and LDAP there is a ordered process than need to be followed, outlined below'''
<br>
<br>
<ul>
<li>Integrate SIMS.net and LDAP</li>
<li>Integrate Moodle and LDAP</li>
<li>Integrate Moodle and SIMS.net</li>
<li>Install SIMS.net Moodle Modules</li>
</ul>
<br>

=Integrate SIMS.net and LDAP=
<ul>
<li>What SIMS.net Information needs to be stored in LDAP</li>
<li>Configuring SQL Server 2005</li>
<li>Configuring Active LDAP</li>
<li>Configuring SIMS2AD vbscript</li>
</ul>
<br>
==Introduction==

All SIMS2AD Scripts are run as a scheduled task on the SIMS Sevrer from D:\SIMS2AD.
<br>
==SIMS2AD==
Runs at 22:00 every day<br><br>
===Add a new view to MS SQL Server===
----
#Open SQL Server Management Studio
#Use the Object Explorer window to browse to the database and the folder called ‘Views’.
#*[Server]\[Instance] -> Database -> sims -> Views
#Right-click the ‘Views’ folder and select ‘New View’.
#Close the Add Table dialog box – Copy and Paste the query below into the Query box 
#Save the View via File, Save [the temporary name of the view] .
#Enter the above name for the new ‘View’ and click ‘OK’.
#If you refresh the list of views by right-clicking the ‘Views’ folder you will see the new view near the top of the list.
#Close SQL Server management Studio.
<br>

===MS SQL 2005 Server View===
----
<pre>
Name: vbs_adsync
</pre>
Query to paste in:
<pre>
SELECT     person_id, legal_surname, forename, midname, dob, unique_pupil_no, reg_group, roll_mode, year_group, 
           surname, chosen_forename
FROM       sims.stud_via_student_browse
WHERE      (roll_mode = 'On Roll')
</pre>
'''Script'''
----
The [https://docs.moodle.org/en/SIMS2AD.vbs SIMS2AD] script needs to copied to D:\SIMS2AD on the SIMS Server.<br>
The latest version of the script is available from:-
<Pre>
https://docs.moodle.org/en/SIMS2AD.vbs
</Pre>
<br>

===Setting Up a Scheduled Task===
----
A scheduled task needs to be created to run the SIMS2AD Script.<br>
This Task will need to run once a day at 22:00 PM 
<br><br>
#Open Control Panel
#Open Scheduled Tasks
#*This will open the Schedule Task Windows
#Add Scheduled Task
#*This will open the Schedule Task Wizard
#Click Next
#Click Browse and Navigate to D:\SIMS2AD Folder
#Select SIMS2AD_Access_Manager.vbs
#Give the the name "SIMS2AD"
#Select Daily
#Click Next
#Set The Time to 22:00
#Click Next
#Enter the user account details that will be used to run the script
#Click Next 
#Click Finish
<br>

==SIMS2AD Access Manager==
'''Not Currently Setup'''
<br>
Runs every 5 minutes, every day <br>
A log file is created in the same folder as the script, this hold a log of all processed changes.
<br><br>
===Add a new view to MS SQL Server===
----
#Open SQL Server Management Studio
#Use the Object Explorer window to browse to the database and the folder called ‘Views’.
#*[Server]\[Instance] -> Database -> sims -> Views
#Right-click the ‘Views’ folder and select ‘New View’.
#Close the Add Table dialog box – Copy and Paste the query below into the Query box 
#Save the View via File, Save [the temporary name of the view] .
#Enter the above name for the new ‘View’ and click ‘OK’.
#If you refresh the list of views by right-clicking the ‘Views’ folder you will see the new view near the top of the list.
#Close SQL Server management Studio.
<br>

===MS SQL 2005 Server View===
----
<pre>
Name: vbs_admanager
</pre>
Query to paste in:
<pre>
SELECT     TOP (100) PERCENT sims.stud_via_student_browse.unique_pupil_no, sims.stud_via_student_browse.forename, 
                      sims.stud_via_student_browse.surname, sims.udf_field.description AS type, sims.udf_lookup_value.description
FROM         sims.udf_value INNER JOIN
                      sims.udf_field ON sims.udf_value.field_id = sims.udf_field.field_id INNER JOIN
                      sims.stud_via_student_browse ON sims.udf_value.entity_id = sims.stud_via_student_browse.person_id INNER JOIN
                      sims.udf_lookup_value ON sims.udf_value.lookup_value_id = sims.udf_lookup_value.lookup_value_id
WHERE     (sims.udf_field.active = 'T')
</pre>
'''Script'''
----
The [https://docs.moodle.org/en/SIMS2AD_access_manager.vbs SIMS2AD Access Manager] script needs to copied to D:\SIMS2AD on the SIMS Server.<br>
The latest version of the script is available from:-
<Pre>
https://docs.moodle.org/en/SIMS2AD_access_manager.vbs
</Pre>
<br>
===Setting Up a Scheduled Task===
----
A scheduled task needs to be created to run the SIMS2AD Access Manager Script.<br>
This Task will need to run every 5 minutes from 08:00 AM to 20:00 PM 
<br><br>
#Open Control Panel
#Open Scheduled Tasks
#*This will open the Schedule Task Windows
#Add Scheduled Task
#*This will open the Schedule Task Wizard
#Click Next
#Click Browse and Navigate to D:\SIMS2AD Folder
#Select SIMS2AD_Access_Manager.vbs
#Give the the name "SIMS2AD Access Manager"
#Select Daily
#Click Next
#Click Next as these Settings will be modified later.
#Enter the user account details that will be used to run the script
#Click Next 
#Tick the box
#*This will open Advance Properties Window
#Click Finish
#Click the Schedule Tab
#Set the Start Time 08:00 AM
#Click the Advance Button
#*This will open an Advance Settings Window
#Set a Start date
#Tick Repeat Task
#Set it to repeat every 5 minutes
#Select Duration and set at 12 hours
#Click OK
#Click Apply
#Click OK
<br>
===Required Logon Script Changes===
----
The below code needs to be added to the beginning of the Kixtart Logon Script.<br>
This will check the current time and see if the user belongs to a group that has restricted computer access.<br>
If the user has logged on during a restricted time a message box will be displayed informing of this and then forcefully log them off.<br>
<pre>
IF ((@time > "20:00:00") AND (@time < "08:50:00"))
	If InGroup("SIMS2AD_LessonOnly_E4") or InGroup("SIMS2AD_LessonOnly_L4") or InGroup("SIMS2AD_Lesson_Evening_Only_E4") 
        or InGroup("SIMS2AD_Lesson_Evening_Only_L4")
	
                MESSAGEBOX ("Your Computer Access Been Restricted, For More Information Please Speak To Your Director Of Year", 
                "UCTC IT SERVICES", 16, 15)
                $RC = LogOff(0)
	endif

Else IF ((@time > "11:15:00") AND (@time < "11:35:00"))
	If InGroup("SIMS2AD_LessonOnly_E4") or InGroup("SIMS2AD_LessonOnly_L4") or InGroup("SIMS2AD_Lesson_Evening_Only_E4") 
        or InGroup("SIMS2AD_Lesson_Evening_Only_L4")
		
                MESSAGEBOX ("Your Computer Access Been Restricted, For More Information Please Speak To Your Director Of Year",
                "UCTC IT SERVICES", 16, 15)
                $RC = LogOff(0)
	endif

Else IF ((@time > "12:35:00") AND (@time < "13:35:00"))
	If InGroup("SIMS2AD_LessonOnly_E4")  or InGroup("SIMS2AD_Lesson_Evening_Only_E4") 

                MESSAGEBOX ("Your Computer Access Been Restricted, For More Information Please Speak To Your Director Of Year", 
                "UCTC IT SERVICES", 16, 15)
                $RC = LogOff(0)
	endif

Else IF ((@time > "13:35:00") AND (@time < "14:35:00"))
	If InGroup("SIMS2AD_LessonOnly_L4") or InGroup("SIMS2AD_Lesson_Evening_Only_L4")

		MESSAGEBOX ("Your Computer Access Been Restricted, For More Information Please Speak To Your Director Of Year", 
                "UCTC IT SERVICES", 16, 15)
		$RC = LogOff(0)
	endif

Else IF ((@time > "16:35:00") AND (@time < "18:00:00"))
	If InGroup("SIMS2AD_LessonOnly_E4") or InGroup("SIMS2AD_LessonOnly_L4") or InGroup("SIMS2AD_Lesson_Evening_Only_E4") 
        or InGroup("SIMS2AD_Lesson_Evening_Only_L4")

		MESSAGEBOX ("Your Computer Access Been Restricted, For More Information Please Speak To Your Director Of Year", 
                "UCTC IT SERVICES", 16, 15)
		$RC = LogOff(0)
	endif

Else IF ((@time > "18:00:00") AND (@time < "20:00:00"))
	If InGroup("SIMS2AD_LessonOnly_E4") or InGroup("SIMS2AD_LessonOnly_L4")

		MESSAGEBOX ("Your Computer Access Been Restricted, For More Information Please Speak To Your Director Of Year", 
                "UCTC IT SERVICES", 16, 15)
		$RC = LogOff(0)
	endif
endif
</pre>
----

=Integrate Moodle and LDAP=
==Setting up LDAP Authentication==
#Login to moodle as the admin user.
#Under Site Administration
#*Users - > Authentication -> LDAP Server
#Set the Following Fields as follows
#*'''LDAP server settings'''
#**Host Url = ldap://server.domain.com/ 
#**Version = 3
#**LDAP encoding = utf-8
#*'''Bind settings''' 
#**Hide passwords = Yes
#**Distinguished Name = cn=LDAP Guest,cn=Users,dc=domain,dc=com
#**Password = Password of LDAP Guest Account
#*'''User lookup settings'''
#**User type = MS Active Directory
#**Contexts = cn=LDAP Guest,cn=Users,dc=domain,dc=com
#**Search subcontexts = Yes
#**Dereference aliases = Yes
#**User attribute = sAMAccountName
#**Member attribute = member
#*'''Force change password'''
#**Force change password = No
#**Use standard Change Password Page = No
#*'''LDAP password expiration settings'''
#**Expiration = No
#*'''Enable user creation'''
#**Create users externally = No
#*'''Data Mapping'''
#**First Name = givenName
#**Surname = sn
#**Email address = mail
#**Description = description
#**ID number = employeeID
#**Department = department
#Click Save
<br>

=Integrate Moodle and SIMS.net=

==Configuring SQL Server 2005==

===Add a new view to MS SQL Server===
----
#Open SQL Server Management Studio
#Use the Object Explorer window to browse to the database and the folder called ‘Views’.
#*[Server]\[Instance] -> Database -> sims -> Views
#Right-click the ‘Views’ folder and select ‘New View’.
#Close the Add Table dialog box – Copy and Paste the following query into the Query box 
#Save the View via File, Save [the temporary name of the view] .
#Enter the above name for the new ‘View’ and click ‘OK’.
#If you refresh the list of views by right-clicking the ‘Views’ folder you will see the new view near the top of the list.
#Close SQL Server management Studio.
<br>

==Configuring SQL Server 2005==

===Add a new view to MS SQL Server===
----
#Open SQL Server Management Studio
#Use the Object Explorer window to browse to the database and the folder called ‘Views’.
#*[Server]\[Instance] -> Database -> sims -> Views
#Right-click the ‘Views’ folder and select ‘New View’.
#Close the Add Table dialog box – Copy and Paste the following query into the Query box 
#Save the View via File, Save [the temporary name of the view] .
#Enter the above name for the new ‘View’ and click ‘OK’.
#If you refresh the list of views by right-clicking the ‘Views’ folder you will see the new view near the top of the list.
#Close SQL Server management Studio.
<br>

===MS SQL 2005 Server View===
----
<pre>
Name: mdl_enrolments_current
</pre>
<pre>
SELECT DISTINCT     class_name AS course, staff_initials AS person, 'editingteacher' AS role, year_code, start_date
FROM                sims.curr_via_classperiods AS p
WHERE               (start_date BETWEEN '2008-09-01' AND '2009-08-31')
UNION
SELECT DISTINCT TOP (100) PERCENT p.class_name AS course, m.unique_pupil_no AS person, 'student' AS role, p.year_code, p.start_date
FROM                sims.curr_via_classperiods AS p INNER JOIN
                    sims.stud_via_group_members AS m ON m.base_group_id = p.base_group_id
WHERE               (p.start_date BETWEEN '2008-09-01' AND '2009-08-31')
ORDER BY            start_date
</pre>

==Configuring Moodle==

===FreeTDS on Linux moodle servers===

Install and configure FreeTDS so that the linux moodle server can communicate with the remote MSSQL

Modify the /etc/freetds.conf file appropriately 
<pre>
[global]
	host = xx.xx.xx.xx
	port = 1427
	client charset = UTF-8
	tds version = 7.0
	text size = 20971520
</pre>

and make sure php-mssql is installed 

<pre>
rpm -ql php-mssql
/etc/php.d/mssql.ini
/usr/lib64/php/modules/mssql.so </pre>

===Add Moodle SIMS.net Course Template===
----
#Login to moodle as the admin user.
#Under Site Administration
#*Courses - > Add/Edit Courses
#Click Miscellaneous
#Click New Course Button
#Set the Following Fields as follows
#*'''General'''
#**Full name = SIMS Template Course
#**Short name = simstemplate
#**Course ID number = simstemplate
#**Format = Topics Format
#**Number of weeks/topics = 10
#**Course Start Date = 1st Sept
#**Hidden sections = Hidden sections are shown in collapsed form
#**News Items to show = 5
#**Show gradebook to students = Yes
#**Show activity reports = No
#**Maximum upload size = [Maximum Size Allowed]
#**Is this a meta course? = No
#*'''Enrolments'''
#**Enrolment Plugins = Site Default (Internal Enrolment)
#**Default role = Site Default (Student)
#**Course enrollable = Yes
#**Start date = Disabled
#**End date = Disabled
#**Enrolment duration = Unlimited
#*'''Enrolment expiry notification'''
#**Notify = No
#**Notify Students = No
#**Threshold = 10 days
#*'''Groups'''
#**Group mode = No Groups
#**Force = No
#**Default grouping = No
#*'''Availability'''
#**Availability = This course is avaliable to students
#**Enrolment key = [Blank]
#**Guest access = Do Not Allow Guest In
#*'''Language'''
#**Force language = No
#*'''Role renaming'''
#**[All Fields are Blank]

===Moodle Settings===
----
#Login to moodle as the admin user.
#Under Site Administration
#*Courses - > Add/Edit Courses
#Click Add New Category
#*Parent Category  = Top
#*Category Name  = SIMS Courses
#Click Save
#Under Site Administration
#*Courses - > Enrolments
#Enable External Database
#Click Edit
#Set the Following Fields as follows
#*'''External Database Server Settings'''
#**enrol_dbtype = mssql
#**enrol_dbhost = sims:1427
#**enrol_dbuser = sa
#**enrol_dbpass = [SA Password]
#**enrol_dbname = sims
#**enrol_dbtable = dbo.mdl_enrolments_current
#*'''Enrolment (remote) database fields'''
#**enrol_localcoursefield = idnumber
#**enrol_localuserfield = idnumber
#**enrol_db_localrolefield = shortname
#**enrol_remotecoursefield = course
#**enrol_remoteuserfield = person
#**enrol_db_remoterolefield = role
#*'''Roles'''
#**enrol_db_defaultcourseroleid = student
#*'''Auto-creation of new courses'''
#**enrol_db_autocreate = Yes
#**enrol_db_category = SIMS Courses
#**enrol_db_template = simstemplate
#*'''General Options'''
#**enrol_db_ignorehiddencourse = No
#**enrol_db_disableunenrol = No
#Click Save

=Install SIMS.net Moodle Modules=
==SIMS.net Timetable Module==
===Add a new view to MS SQL Server===
----
#Open SQL Server Management Studio
#Use the Object Explorer window to browse to the database and the folder called ‘Views’.
#*[Server]\[Instance] -> Database -> sims -> Views
#Right-click the ‘Views’ folder and select ‘New View’.
#Close the Add Table dialog box – Copy and Paste the following query into the Query box 
#Save the View via File, Save [the temporary name of the view] .
#Enter the above name for the new ‘View’ and click ‘OK’.
#If you refresh the list of views by right-clicking the ‘Views’ folder you will see the new view near the top of the list.
#Close SQL Server management Studio.

===MS SQL 2005 Server View===
----
'''View for Student Timetable'''
<pre>
Name: mdl_student_timetable
</pre>
<pre>
SELECT     TOP (100) PERCENT sims.rpt_vix_timetable.period_name, sims.rpt_vix_timetable.class_name,
                             sims.stud_via_student_browse.unique_pupil_no, 
                             sims.rpt_vix_timetable.staff_initials, sims.rpt_vix_timetable.room_name
FROM                  sims.stud_via_group_members INNER JOIN
                      sims.curr_class_period ON sims.stud_via_group_members.base_group_id = sims.curr_class_period.base_group_id 
                      INNER JOIN sims.stud_via_student_browse ON sims.stud_via_group_members.person_id = 
                      sims.stud_via_student_browse.person_id INNER JOIN sims.rpt_vix_timetable ON 
                      sims.curr_class_period.class_period_id = sims.rpt_vix_timetable.class_period_id

WHERE                 (sims.curr_class_period.person_id IS NOT NULL) AND (sims.rpt_vix_timetable.end_date > GETDATE()) AND 
                      (sims.stud_via_group_members.start_date < GETDATE()) AND (sims.stud_via_group_members.end_date > GETDATE())
ORDER BY              sims.rpt_vix_timetable.period_name
</pre>
'''View for Staff Timetable'''
<pre>
Name: mdl_staff_timetable
</pre>
<pre>
SELECT     TOP (100) PERCENT sims.rpt_vix_timetable.period_name, sims.rpt_vix_timetable.class_name, 
                             sims.rpt_vix_timetable.staff_initials, sims.rpt_vix_timetable.room_name
FROM                         sims.curr_class_period INNER JOIN sims.rpt_vix_timetable ON sims.curr_class_period.class_period_id =  
                             sims.rpt_vix_timetable.class_period_id
WHERE                        (sims.rpt_vix_timetable.end_date > GETDATE())
ORDER BY                     sims.rpt_vix_timetable.period_name
</pre>

===SIMS.net Timetable Moodle Module===
----
The [http://www.uctc.e-sussex.sch.uk/SIMS.net_Timetable_Module.zip SISM.net Timetable Module] files needs to copied to the html folder on the Moodle server.<br>
The latest version of the files are available from:-
<Pre>
http://www.uctc.e-sussex.sch.uk/SIMS.net_Timetable_Module.zip
</Pre>
[[Category: Administrator]]