© 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.
This is the traditional way of integrating with SIMS and gives access to SIMS data that our web integration does not yet cover. (Work in progress)
Sometimes there is no substitute for actually sitting down and creating an exemplar for technical integrators and in so doing discover some of the challenges that the TI community faces.
Create an extract of:
and keep them up to date efficiently.
The starting point for any integration is the exemplar application which is available for download. Fortunately this already contains a read and write for session attendance and hence gives a very good starting point for the code. (It also contains examples of getting students).
The code shell looks like
DateTime LastUpdated = DateTime.Min;
DateTime ThisUpdate = DateTime.Now;
Main()
{
If ! First Time
{
LastUpdated = Read Last Run Time from file.
}
AddSIMSDllResolution();
DoTherest()
}
void DotheRest()
{
if Login to SIMS
{
Set Student Updates
Get Attendance Update
LastUpdated = ThisUpdate
Save LastUpdated
Do something with the data
}
}
Attendance change tracking is that it uses a last updated field in the marks table; however, please ensure that you do your testing for change via the SIMS / SIMS APIs because changing the marks in SQL directly including the last updated date is not always sufficient for them to appear as 'changed marks'.
As with any system that has grown over a number of years, there are a number of available APIs, some will proport to offer you the data that you seek and then disappoint. Indeed the API below seemed to fit the bill...
TPAttendanceSession tpas = new TPAttendanceSession();
tpas.GetXmlChangedStatutoryAttendanceMarks(fromWhen);
which whilst it appears to 'work' but returns:
<SessionMarks>
<SessionMark>
<StudentSchoolHistoryID>10001</StudentSchoolHistoryID>
<SchoolAttendanceCode>B</SchoolAttendanceCode>
<IsPresent>F</IsPresent>
<AttendableEventStart>2023-01-09T09:00:00</AttendableEventStart>
<MinutesLate/>
<SessionEventInstanceID>25210</SessionEventInstanceID>
<Comment/>
</SessionMark>
Which sadly misses the person id! Partner Support is available to assist contracted partners to short cut this type of 'adventure'. I asked them and was pointed to:
XmlDocument d = new XmlDocument();
TPAttendanceRead tpar = new TPAttendanceRead();
d.InnerXml = tpar.GetXmlChangedSessionAttendancesInRange(fromWhen, toWhen);
Which returns what we need...
<SessionAttendanceInRange>
<PersonID>5257</PersonID>
<SessionName>AM</SessionName>
<AttendanceMark>D</AttendanceMark>
<Minute>0</Minute>
<Comments/>
<SessionNumber>11</SessionNumber>
<EventStart>2022-10-01T09:00:00</EventStart>
<EventEnd>2022-10-01T12:30:00</EventEnd>
<LastUpdated>2023-01-09T09:57:00</LastUpdated>
<UserID/>
<SubCode/>
<SubCodeLastUpdated/>
<SubCodeUserID/>
</SessionAttendanceInRange>
and does respond to changes made to attendance within SIMS as expected.
One way of getting the data from the beginning of time would be to pass in a 'fromWhen' date of say '1980-01-01' which makes the code simpler. When I tried this on Water's Edge (Welsh) data with about 6M attendance rows on my machine it simply hung the process. When I tried a select * from marks (att_marks_normalised) it took 5 minutes and the underlying stored procedure has to do both select the 6M rows and convert the response to XML.
Thus a different approach is needed.
void DotheRest()
{
if Login to SIMS
{
If First Time
{
Load Students
Load Attendance
}
Else
{
Set Student Updates
Get Attendance Update
}
...
}
}
The first question is when is data needed from (StartDate)? It is most unlikely that an application would need data from 2011 which was about where my training data started. A more likely StartDate would be 1/9/22 or 1/9/23 at the time of writing.
Whilst the get changes call above might appear to get all the marks using that start date, there are edge cases where say holidays are entered in advance which will not have been changed within the time window and hence the better APIs are those below.
foreach (SIMSInterface.YearGroup yearGroup in SIMSInterface.GetYearGroups.YearGroups)
{
StartOfTime = DateTime.Parse("2023-09-01");
LastUpdate = DateTime.Now(); // This becomes the date time for the next get changes call.
XmlDocument d = SIMSInterface.Attendance.GetFirstMarks(yearGroup.Id, LastUpdate, StartOfTime);
// - Do something with the marks...
}
public static System.Xml.XmlDocument GetFirstMarks(int YearGroupId,DateTime fromWhen, DateTime NotBefore)
{
XmlDocument d = new XmlDocument();
SIMS.Processes.TPAttendanceRead tpar = new SIMS.Processes.TPAttendanceRead();
d.InnerXml = tpar.GetXmlSessionAttendancesExtended(0, YearGroupId, NotBefore, DateTime.Now);
return d;
}
NB: Do note that the 2 calls changes and the above return different xml structures.
Why extract by year group? A large secondary school may have 2000 pupils and 300 days in to the year (there are marks for holidays and weekends) we might expect 1.2 million marks. It doesn't really matter how the volumes are reduced, you could for example do a week at a time, it is the management of the size of the result set that matters. My available parameters are the group (could be reg group too), start and end. Code for getting year groups is at the bottom of the article.
Having experimented, this looks like the most appropriate way to meet the requirement for attendance.
For my purposes, I required a minimal student record with the following fields: PupilId, UPN, Surname, Forename, DOB, Sex, Year, DOB. These fields are covered by the change tracking on Student. If more complex structures are needed then change tracking may not be the best way to go but this needs to be decided on a case by case basis.
Based on the same principle, I got a first cut of students using to get all the pupils on roll on the date passed in.
public static XmlDocument GetStudents(DateTime CurrentOnThisDate)
{
SIMS.Processes.TPPersonStudent tpps = new SIMS.Processes.TPPersonStudent();
XmlDocument d = new XmlDocument();
d.InnerXml = tpps.GetXmlStudents(CurrentOnThisDate);
return d;
}
We should then be able to get all of the pupils whose data has changed
public static XmlDocument GetChangedStudents(DateTime fromWhen)
{
SIMS.Processes.TPPersonStudent tpps = new SIMS.Processes.TPPersonStudent();
XmlDocument d = new XmlDocument();
d.InnerXml = tpps.GetXmlChangedStudents(fromWhen);
return d;
}
Do note that the 2 student calls actually return different xml structures.
<Students>
<Student>
<PersonID>11682</PersonID>
<Forename>Lawrence</Forename>
...
from the getXmlStudents above, vs the change tracking below.
<StudentExtendeds>
<Student>
<PersonID>3827</PersonID>
<Forename>Gracy-Ann</Forename>
<Surname>Adams</Surname>
<ChosenName>Gracy-Ann</ChosenName>
<MiddleName/>
<LegalSurname>Adams</LegalSurname>
<Gender>Female</Gender>
<GenderID>1</GenderID>
<GenderCode>F</GenderCode>
<DOB>2011-12-18T00:00:00</DOB>
<PhotoID>1364</PhotoID>
<Photo/>
<ExternalId>a60641c2-9544-4b52-9c00-5645bda17b1b</ExternalId>
<BirthCertificateSeen>F</BirthCertificateSeen>
<AttendanceMode>All Day</AttendanceMode>
<AttendanceModeID>-1</AttendanceModeID>
<FormerUPN/>
<UniqueLearnerNumber/>
<YearTaughtIn>Curriculum Year 6</YearTaughtIn>
<YearTaughtInID>18</YearTaughtInID>
<EmergencyConsent>F</EmergencyConsent>
<NHSNumber/>
<BloodGroup/>
<EthnicDataSource>Provided by the parent</EthnicDataSource>
<EthnicDataSourceID>444</EthnicDataSourceID>
<HomeLanguage>English</HomeLanguage>
<HomeLanguageID>159</HomeLanguageID>
<FirstLanguage>English and/or Welsh/Cymraeg</FirstLanguage>
<FirstLanguageID>1156</FirstLanguageID>
<EnglishAdditionalLanguage>0 = Not applicable</EnglishAdditionalLanguage>
<EnglishAdditionalLanguageID>1145</EnglishAdditionalLanguageID>
<NationalIdentity>Welsh</NationalIdentity>
<NationalIdentityID>3</NationalIdentityID>
<QuickNote/>
<SchoolHistoryID/>
<EthnicityCode/>
<RegionCode/>
<LegalForename/>
<LegalMiddlenames/>
<PreferredForename>Gracy-Ann</PreferredForename>
<PreferredSurname/>
<IsMailingPoint/>
<IsDisabled/>
<IsTaughthroughIrishMedium/>
<JobSeekerAllowance/>
<DateOfBirth/>
<AdmissionNumber>001457</AdmissionNumber>
<UPN>G823299915001</UPN>
<Religion>Christian</Religion>
<ReligionID>524</ReligionID>
<Nationality/>
<NationalityID/>
<RegGroup>6VC</RegGroup>
<RegGroupID>184</RegGroupID>
<YearGroup>Year 6</YearGroup>
<YearGroupID>26</YearGroupID>
<House/>
<HouseID/>
<RollMode>Single Registration</RollMode>
<RollModeID>3</RollModeID>
<OnRollMode>C</OnRollMode>
<AdmissionDate>2015-09-01T00:00:00</AdmissionDate>
<DateOfLeaving/>
<ParentalSalutation>Mr Adams</ParentalSalutation>
<ReligionCode/>
<ReligionCategoryCode/>
<AsylumStatusCode/>
<ServiceChildrenCode/>
<FirstLanguageCode/>
</Student>
</StudentExtendeds>
When I tested this, change tracking for students returned all of the students which indicated that change tracking had not be turned on in Tools | Settings | Data Change Management. I tried again and got all of the students once more.
Even then setting this on to record change by the hour still returned all of the pupils until I had stopped for lunch (waited an hour) and then made a change before running the application again at which point it worked as intended.
Writing integration applications is not an exact science and is probably best done using an Agile model. The first iteration needs to 'work; but may need to go through additional iterations when say data volumes increase. In this case, the iterations showed that initial population via change tracking isn't a good choice for this problem and highlighted that most change tracking calls need the school to turn on change tracking at an appropriate granularity. It did however show that there were appropriate APIs available to achieve the task, even if some of the APIs found were inappropriate for the task in hand. It also showed the value of partner development support.
public class YearGroup
{
public int Id { get; set; }
public string Name { get; set; }
}
public class GetYearGroups
{
private static List<YearGroup> _YearGroups = new List<YearGroup>();
public static List<YearGroup> YearGroups
{
get
{
if (_YearGroups.Count == 0)
{
SIMS.Processes.StudentBrowseProcess sbp = new SIMS.Processes.StudentBrowseProcess();
foreach (SIMS.Entities.YearGroup y in sbp.YearGroups)
{
YearGroup x = new YearGroup();
x.Id = y.ID;
x.Name = y.ShortName;
_YearGroups.Add(x);
}
}
return _YearGroups;
}
}
}