© 2018 Capita Business Services Ltd. All rights reserved.
Capita Education Software Solutions is a trading name of Capita Business Services Ltd. Our Registered office is 30 Berners Street, London, W1T 3LR and our registered number is 02299747. Further information about Capita plc can be found in our legal statement.
SIMS the MIS of choice supporting teachers, senior leaders and wider staff with the tools to efficiently drive improvement in their schools.
Note: Example code is provided as is and subject to the terms in the header. SWC and SWAC change each year and it is the responsibility of the TI to confirm fitness for purpose which may change over time.
Reference:
Many school groups are now using external HR systems to manage their staff across a number of schools and MIS. Typically the SWC is returned once per school but the HR system has most of the data required but would lack the curriculum data below. One option is to do 2 exports, staff basic and curriculum from the MIS and the remainder including staff basic again from the HR system, however it is quite likely that the two staff basic sections are different, largely because the HR system becomes the master copy and data exchanges are 'partial'. The second option is for the HR system to use something similar to code below to enable the HR system to import the curriculum data required.
Schools required to perform an SWC / SWAC curriculum section if they belong to certain school categories. The section is fairly simple:
but requires calculation based on data that is not in the HR system.
public static void DoSWC()
{
SIMSInterface.SWC sWC = new SWC(DateTime.Today.AddDays(-14),
DateTime.Today.AddDays(1).AddMinutes(-1), Server, Database, User, Password);
string s = sWC.Export();
System.IO.File.WriteAllText(Path.Combine(OutputFolder, "SWC.json"), s);
}[
{
"StaffID": 1,
"Surname": "Blacker",
"Forename": "Adrian",
"NINumber": "ZN248685D",
"Subject": "Religious Education",
"HoursTaught": 3.0,
"NCYear": "7"
},
{
"StaffID": 1,
"Surname": "Blacker",
"Forename": "Adrian",
"NINumber": "ZN248685D",
"Subject": "Geography",
"HoursTaught": 9.0,
"NCYear": "11"
},// ---------------------------------------------------------------------------------
// Sample code provided to Education Software Solutions SIMS Partners under consultancy (Hereafter 'partners')
// Written by Andy McGowan 2024
//
// Sample code is copyright (c) Education Software Solutions
//
// Education Software Solutions grants rights to use the code on an 'as is' basis to partners for the purpose
// of interfacing with SIMS .net and is for their own use. Code may be used in whole or in part for
// the partners's application(s). Rights are subject to the restrictions below.
//
// This code may not be used for commercial purposes without a partnership agreement with Education Software Solutions
// if the code is provided to schools for their own use then further consent from Education Software Solutions
// must be obtained before the code can be re-used for commercial gain.
//
// No warranty is provided . This header must be retained within the code and Copyright acknowleged as
// Parts (c) Education Software Solutions 2024 in version info.
//
// Code samples may be used to support other partners and no exclusivity is implied herein
//
// ---------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Xml;
namespace SIMSInterface
{
#region public export classes Actual export is to JSOn
/// <summary>
/// Each record is a line in the SWC return
/// </summary>
public class SWCRecord
{
public int? StaffID { get; set; }
public string Surname { get; set; }
public string Forename { get; set; }
public string NINumber { get; set; }
public string Subject { get; set; }
public Double? HoursTaught { get; set; }
public string NCYear { get; set; }
}
#endregion
public class SWC
#region internal classes
/// <summary>
/// Used to load staff from the API call.
/// </summary>
{
private class Staff
{
/// <summary>
/// Integer ID in SIMS
/// </summary>
public int StaffID;
/// <summary>
/// Preferred Surname
/// </summary>
public string Surname;
/// <summary>
/// Preferred forename
/// </summary>
public string Forename;
/// <summary>
/// NI Number
/// </summary>
public string NINumber;
}
#endregion
#region private storage
/// <summary>
/// Start for staff engagement export
/// </summary>
private DateTime Start = DateTime.Today;
/// <summary>
/// End of staff engagement period
/// </summary>
private DateTime End = DateTime.Today;
/// <summary>
/// SQL Server for SIMS
/// </summary>
private string Server;
/// <summary>
/// SIMS Database name
/// </summary>
private string Database;
/// <summary>
/// SIMS User - Needs Personnel Officer Rights
/// </summary>
private string User;
/// <summary>
/// Password for SIMS User
/// </summary>
private string Password;
/// <summary>
/// Lookup of event instance id to eventh length.
/// </summary>
private Dictionary<int, double> EventInstanceLengths = new Dictionary<int, double>();
/// <summary>
/// Staff Lookup
/// </summary>
private Dictionary<int, Staff> StaffList = new Dictionary<int, Staff>();
/// <summary>
/// Class -> NC Year lookup
/// </summary>
private Dictionary<int, string> ClassNCYMap = new Dictionary<int, string>();
/// <summary>
/// Subject Lookup
/// </summary>
private Dictionary<int, string> SubjectLookup = new Dictionary<int, string>();
/// <summary>
/// Main export dictionary for the conolidated data
/// </summary>
private Dictionary<string, SWCRecord> SWCExport = new Dictionary<string, SWCRecord>();
#endregion
#region Private Loading methods
/// <summary>
/// We need to get the map from Class (id thereof) to NC Year
/// </summary>
private void SWCLoadNCYClassMap()
{
SIMS.Processes.CurrCache.Populate();
SIMS.Processes.CurrBrowseLessons cbl = new SIMS.Processes.CurrBrowseLessons();
foreach (SIMS.Entities.NationalCurriculumYear nc in SIMS.Entities.GroupCache.GetNationalCurriculumYears())
{
if (nc.Active == "A")
{
cbl.Load("", "", "", "", nc.BaseGroupID);
foreach (SIMS.Entities.CurrLessonSet cls in cbl.Lessons)
{
if (!ClassNCYMap.ContainsKey(cls.ClassId))
{
ClassNCYMap.Add(cls.ClassId, nc.Description);
}
}
}
}
}
/// <summary>
/// We need to load event instances to get the length of lessons.
/// </summary>
private void SWCLoadEventInstances()
{
SIMS.Processes.TPCalendar tpc = new SIMS.Processes.TPCalendar();
XmlDocument d = new XmlDocument();
d.InnerXml = tpc.GetXmlEventInstances(Start, End);
foreach (XmlNode n in d.SelectNodes("CalendarEventInstances/CalendarEventInstance"))
{
int eid = 0;
if (int.TryParse(n["EventInstanceID"].InnerXml, out eid))
{
DateTime start = DateTime.Now;
DateTime end = DateTime.Now;
if (DateTime.TryParse(n["EventStartDate"].InnerXml, out start))
{
if (DateTime.TryParse(n["EventEndDate"].InnerXml, out end))
{
TimeSpan t = end.Subtract(start);
int da = t.Days;
int hr = t.Hours;
int min = t.Minutes;
EventInstanceLengths.Add(eid, (t.Days * 24 + t.Hours + t.Minutes / 60));
}
}
}
}
}
/// <summary>
/// Load the subjects for the DfE Lookup
/// </summary>
private void SWCLoadSubjects()
{
SIMS.Processes.CLPSubjectBrowser sub = new SIMS.Processes.CLPSubjectBrowser();
SIMS.Entities.CLPSubjectSummarys subs = sub.Browse("", null, null);
foreach (SIMS.Entities.CLPSubjectSummary s in subs)
{
if (s.Active)
{
SubjectLookup.Add(s.SubjectCodeID, s.GeneralSubjectsAttribute.Value.ToString());
}
}
}
/// <summary>
/// Loads a lookup for staff
/// Not too worried if this contains leavers because leavers might just be valid
/// in the return.
/// To be included in the return they must have taught a class in the
/// required period.
/// </summary>
private void SWCLoadStaff()
{
XmlDocument d = new XmlDocument();
ThirdPartyCommands.GetXmlEmployeesCommand gec
= new ThirdPartyCommands.GetXmlEmployeesCommand(Server, Database, User.Contains("\\"), User, Password, 0);
d = gec.ExecuteCommand();
foreach (XmlNode n in d.SelectNodes("EmployeesDetails/Employees/Employee"))
{
Staff s = new Staff();
s.Surname = n["Surname"].InnerText;
s.StaffID = int.Parse(n["PersonID"].InnerXml);
s.Forename = n["Forename"].InnerText;
if (n["NINumber"] != null)
{
s.NINumber = n["NINumber"].InnerText;
StaffList.Add(s.StaffID, s);
}
// Skip any staff without NI Numbers because they can't be used in the return
}
}
#endregion
#region Constructor
/// <summary>
/// Usage
/// </summary>
/// <param name="start">start of time period for SWAC/SWC</param>
/// <param name="end">End of time period for SWAC/SWC</param>
/// <param name="server"></param>
/// <param name="database"></param>
/// <param name="user"></param>
/// <param name="password"></param>
public SWC(DateTime start, DateTime end, string server, string database, string user, string password)
{
Start = start;
End = end;
Server = server;
Database = database;
User = user;
Password = password;
SWCExport.Clear();
Load();
}
#endregion
#region Public functions
/// <summary>
/// Final export
/// </summary>
/// <returns></returns>
public string Export()
{
return Newtonsoft.Json.JsonConvert.SerializeObject(SWCExport.Values,Newtonsoft.Json.Formatting.Indented);
}
/// <summary>
/// Loads the SWC
/// </summary>
private void Load()
{
SWCLoadSubjects();
SWCLoadNCYClassMap();
SWCLoadStaff();
SWCLoadEventInstances();
SWCCalculate();
}
private void SWCCalculate()
{
SIMS.Processes.TPTimetable tpt = new SIMS.Processes.TPTimetable();
XmlDocument d = new XmlDocument();
// 0 staff id returns all staff time tables.
d.InnerXml = tpt.GetXmlEmployeeTimetable(0, Start, End);
foreach (XmlNode n in d.SelectNodes("Timetables/Timetable"))
{
SWCRecord s = new SWCRecord();
s.HoursTaught = 0;
bool OK = false;
// Lookup Staff
int staffID = 0;
if (int.TryParse(n["EmployeeID"].InnerXml, out staffID))
{
Staff st = null;
if (StaffList.TryGetValue(staffID, out st))
{
s.Surname = st.Surname;
s.Forename = st.Forename;
s.NINumber = st.NINumber;
s.StaffID = staffID;
OK = true;
}
}
// Look up period Length
if (OK)
{
OK = false;
// Now Period Length
Double _length;
int Eid;
if (int.TryParse(n["EventInstanceID"].InnerXml, out Eid))
{
if (EventInstanceLengths.TryGetValue(Eid, out _length))
{
s.HoursTaught += _length;
OK = true;
}
}
}
// Get the subject
if (OK)
{
OK = false;
string sub = "";
int subId = 0;
if (int.TryParse(n["SubjectID"].InnerXml, out subId))
{
if (SubjectLookup.TryGetValue(subId, out sub))
{
s.Subject = sub;
OK = true;
}
}
}
// Get the NC Year
if (OK)
{
OK = false;
string ncy = "";
int bgid = 0;
if (int.TryParse(n["BaseGroupID"].InnerXml, out bgid))
{
if (ClassNCYMap.TryGetValue(bgid, out ncy))
{
s.NCYear = ncy.Replace("Curriculum Year", "").Replace(" ", "");
OK = true;
}
}
}
if (OK)
{
// I have a fully formed and valid entry
// But it may exist already.
string Index = s.StaffID.ToString() + "|" + s.Subject + "|" + s.NCYear;
SWCRecord existingRecord = null;
if (SWCExport.TryGetValue(Index, out existingRecord))
{
existingRecord.HoursTaught += s.HoursTaught;
}
else
{
SWCExport.Add(Index, s);
}
}
}
}
#endregion
}
}