Open Menu

© 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 7 - Change Tracking without change tracking

Change tracking within SIMS 7 is heavily tied to the data structures that underpin SIMS 7.  The TI Team are often presented with examples which are comprised from many different entities (tables) and have relatively high overheads if their system has to post changes which aren't changes at all.

I've been working on a proof of concept for a potential customer who expressed a need to identify changes in personnel data.  I've selected subset of the data discussed and written a report in SIMS to get the data.  In traditional TI style I then automate command reporter to get the data as an XML Document.

<SuperStarReport>
  <xs:schema id="SuperStarReport" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="SuperStarReport" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

...

 </xs:schema>
  <Record>
    <multiple_id>52,275</multiple_id>
    <ID>52</ID>
    <ExternalId>b7d7ad3c-e39b-4d85-960b-5ae19581444f</ExternalId>
    <NINumber>NJ376523A</NINumber>
    <LegalForename>Carole</LegalForename>
    <LegalSurname>Jacobson</LegalSurname>
    <Gender>F</Gender>
    <Title>Mrs</Title>
    <DOB>1962-03-17T00:00:00+00:00</DOB>
    <EthnicityCategory>White, British</EthnicityCategory>
    <Initials>CJ</Initials>
  
...

    <ClearanceDate>2004-06-14T00:00:00+01:00</ClearanceDate>
    <ClearanceLevel>List 99 Cleared</ClearanceLevel>
  </Record>
</SuperStarReport>

I've found a number of clever web sites which will write C# classes for me simply by posting the output of the report in to a window (Please don't try that with customer data) and get some C# classes for free.

For example: Convert XML to C# Classes Online - Json2CSharp Toolkit did an OK job.  Paste in the above and get

// using System.Xml.Serialization;
// XmlSerializer serializer = new XmlSerializer(typeof(SuperStarReport));
// using (StringReader reader = new StringReader(xml))
// {
//    var test = (SuperStarReport)serializer.Deserialize(reader);
// }

[XmlRoot(ElementName="element")]
public class Element { 

	[XmlAttribute(AttributeName="name")] 
	public string Name { get; set; } 

	[XmlAttribute(AttributeName="type")] 
	public string Type { get; set; } 

	[XmlAttribute(AttributeName="minOccurs")] 
	public int MinOccurs { get; set; } 

	[XmlAttribute(AttributeName="DataType")] 
	public string DataType { get; set; } 

	[XmlAttribute(AttributeName="Prefix")] 
	public string Prefix { get; set; } 

	[XmlElement(ElementName="complexType")] 
	public ComplexType ComplexType { get; set; } 

	[XmlAttribute(AttributeName="IsDataSet")] 
	public bool IsDataSet { get; set; } 

	[XmlAttribute(AttributeName="UseCurrentLocale")] 
	public bool UseCurrentLocale { get; set; } 
}

[XmlRoot(ElementName="sequence")]
public class Sequence { 

	[XmlElement(ElementName="element")] 
	public List<Element> Element { get; set; } 
}

[XmlRoot(ElementName="complexType")]
public class ComplexType { 

	[XmlElement(ElementName="sequence")] 
	public Sequence Sequence { get; set; } 

	[XmlElement(ElementName="choice")] 
	public Choice Choice { get; set; } 
}

[XmlRoot(ElementName="choice")]
public class Choice { 

	[XmlElement(ElementName="element")] 
	public Element Element { get; set; } 

	[XmlAttribute(AttributeName="minOccurs")] 
	public int MinOccurs { get; set; } 

	[XmlAttribute(AttributeName="maxOccurs")] 
	public string MaxOccurs { get; set; } 
}

[XmlRoot(ElementName="schema")]
public class Schema { 

	[XmlElement(ElementName="element")] 
	public Element Element { get; set; } 

	[XmlAttribute(AttributeName="id")] 
	public string Id { get; set; } 

	[XmlAttribute(AttributeName="xmlns")] 
	public object Xmlns { get; set; } 

	[XmlAttribute(AttributeName="xs")] 
	public string Xs { get; set; } 

	[XmlAttribute(AttributeName="msdata")] 
	public string Msdata { get; set; } 
}

[XmlRoot(ElementName="Record")]
public class Record { 

	[XmlElement(ElementName="multiple_id")] 
	public double MultipleId { get; set; } 

	[XmlElement(ElementName="ID")] 
	public int ID { get; set; } 

	[XmlElement(ElementName="ExternalId")] 
	public string ExternalId { get; set; } 

	[XmlElement(ElementName="NINumber")] 
	public string NINumber { get; set; } 

	[XmlElement(ElementName="LegalForename")] 
	public string LegalForename { get; set; } 

	[XmlElement(ElementName="LegalSurname")] 
	public string LegalSurname { get; set; } 

	[XmlElement(ElementName="Gender")] 
	public string Gender { get; set; } 

	[XmlElement(ElementName="Title")] 
	public string Title { get; set; } 

	[XmlElement(ElementName="DOB")] 
	public DateTime DOB { get; set; } 

	[XmlElement(ElementName="EthnicityCategory")] 
	public string EthnicityCategory { get; set; } 

	[XmlElement(ElementName="Initials")] 
	public string Initials { get; set; } 

	[XmlElement(ElementName="NOK-Fullname")] 
	public string NOKFullname { get; set; } 

	[XmlElement(ElementName="NOK-Home")] 
	public string NOKHome { get; set; } 

	[XmlElement(ElementName="HouseNumber")] 
	public int HouseNumber { get; set; } 

	[XmlElement(ElementName="Staff-TelephoneNumber")] 
	public string StaffTelephoneNumber { get; set; } 

	[XmlElement(ElementName="Street")] 
	public string Street { get; set; } 

	[XmlElement(ElementName="Town")] 
	public string Town { get; set; } 

	[XmlElement(ElementName="Postcode")] 
	public string Postcode { get; set; } 

	[XmlElement(ElementName="RequestedDate")] 
	public DateTime RequestedDate { get; set; } 

	[XmlElement(ElementName="ClearanceDate")] 
	public DateTime ClearanceDate { get; set; } 

	[XmlElement(ElementName="ClearanceLevel")] 
	public string ClearanceLevel { get; set; } 

	[XmlElement(ElementName="ReferenceNumber")] 
	public int ReferenceNumber { get; set; } 

	[XmlElement(ElementName="DocumentNumber")] 
	public double DocumentNumber { get; set; } 

	[XmlElement(ElementName="AuthenticatedBy")] 
	public string AuthenticatedBy { get; set; } 
}

[XmlRoot(ElementName="SuperStarReport")]
public class SuperStarReport { 

	[XmlElement(ElementName="schema")] 
	public Schema Schema { get; set; } 

	[XmlElement(ElementName="Record")] 
	public List<Record> Record { get; set; } 
}

The beauty of deserialization is that you can simply remove all the stuff that you don't want from the classes and still process the same old XML with the superfluous stuff still in the source document.  So we can remove all the schema definition and fields that we don't want.  Unfortunately the website didn't quite get everything right and needed some fixing before the errors vanished...

[XmlRoot(ElementName="Record")]
public class Record { 

	//[XmlElement(ElementName="multiple_id")] 
	//public double MultipleId { get; set; } 

..

	[XmlElement(ElementName="DOB")] 
	public Nullable<DateTime> DOB { get; set; }

Some data items are best cast to string and dates / numbers need to be nullable in many cases but it takes the back ache out of the job. It even gives you the code to deserialize the XML in the header.

public static bool Go()
        {
            XmlDocument doc = new XmlDocument();
            doc = SIMSReportingEngine.ReportingEngine.Run("StaffChecks", ".", "engga", "blacka", "abcd");
            XmlSerializer serializer2 = new XmlSerializer(typeof(Contracts.Basic.SuperStarReport));
            ontracts.Basic.SuperStarReport reportInfo = null;
            using (StringReader reader = new StringReader(doc.InnerXml))
            {
                reportInfo = (Contracts.Basic.SuperStarReport)serializer2.Deserialize(reader);
            }
            // Load today's records
            Dictionary<string, Contracts.Basic.Record> Today = new Dictionary<string, Contracts.Basic.Record>();
            foreach (Contracts.Basic.Record r in reportInfo.Record)
            {
                Today.Add(r.MultipleId, r);
            }
            // Load Yesterday's records
            Dictionary<string, Contracts.Basic.Record> Yesterday = new Dictionary<string, Contracts.Basic.Record>();
            XmlDocument dYesterday = new XmlDocument();
            dYesterday.Load(@"c:\temp\StaffChecks.xml");
            Contracts.Basic.SuperStarReport reportInfoYesterday = null;
            using (StringReader reader = new StringReader(dYesterday.InnerXml))
            {
                reportInfoYesterday = (Contracts.Basic.SuperStarReport)serializer2.Deserialize(reader);
            }

            foreach (Contracts.Basic.Record r in reportInfoYesterday.Record)
            {
                Yesterday.Add(r.MultipleId, r);
            }
            // 4 different ourcomes are possible - create a dictionary of each.          
            Dictionary<string, Contracts.Basic.Record> New = new Dictionary<string, Contracts.Basic.Record>();
            Dictionary<string, Contracts.Basic.Record> Changed = new Dictionary<string, Contracts.Basic.Record>();
            Dictionary<string, Contracts.Basic.Record> Removed = new Dictionary<string, Contracts.Basic.Record>();
            Dictionary<string, Contracts.Basic.Record> UnChanged = new Dictionary<string, Contracts.Basic.Record>();
            // See what belongs in each.
            foreach (Contracts.Basic.Record r in Today.Values)
            {
                Contracts.Basic.Record old = null;

                if (Yesterday.TryGetValue(r.MultipleId, out old))

                {
                    // Found it.
                    if (! r.Equals(old))
                    {
                        // Add it to the list of changes.
                        Changed.Add(r.MultipleId, r);
                        // remove it from the old list
                        Yesterday.Remove(r.MultipleId);
                    }
                    else
                    {
                        UnChanged.Add(r.MultipleId, r);
                        // remove it from the old list
                        Yesterday.Remove(r.MultipleId);
                    }
                }
                else
                {
                    // it's new.
                    New.Add(r.MultipleId, r);
                }

            }
            foreach (Contracts.Basic.Record r in Yesterday.Values)
            {
                // Any left have been removed.
                Removed.Add(r.MultipleId, r);
            }
            return true;
        }

 

All looks fairly simple but requires an additional tweak to work..

Two objects in c# are only the equal if they occupy the same memory:
Record A = new Record();
A.Name = "SIMS";
Record B = new Record();
B.Name = "SIMS";
if (A == B)
.. surprisingly this is false!

In order to make it work here, i had to override equals as follows:

public class Record
	{
		public override bool Equals(System.Object obj)
		{
			//If the obj argument is null return false  
			if (obj == null)
				return false;
			//If both the references points to the same object then only return true  
			if (this == obj)
				return true;

			//If this and obj are referring to different type return false  
			if (this.GetType() != obj.GetType())
				return false;

			//Compare each field of this object with respective field of obj object  
			Record test = (Record)obj;
			if (
				this.MultipleId == test.MultipleId &&
				this.ID == test.ID &&
				this.ExternalId == test.ExternalId &&
				this.NINumber == test.NINumber &&
				this.LegalForename == test.LegalForename &&
				this.LegalSurname == test.LegalSurname &&
				this.Gender == test.Gender &&
				this.DOB == test.DOB &&
				this.EthnicityCategory == test.EthnicityCategory &&
				this.NOKFullname == test.NOKFullname &&
				this.NOKHome == test.NOKHome &&
				this.HouseNumber == test.HouseNumber &&
				this.StaffTelephoneNumber == test.StaffTelephoneNumber &&
				this.Initials == test.Initials &&
				this.Street == test.Street &&
				this.Town == test.Town &&
				this.Postcode == test.Postcode &&
				this.RequestedDate == test.RequestedDate &&
				this.ClearanceDate == test.ClearanceDate &&
				this.ClearanceLevel == test.ClearanceLevel &&
				this.ReferenceNumber == test.ReferenceNumber &&
				this.DocumentNumber == test.DocumentNumber &&
				this.AuthenticatedBy == test.AuthenticatedBy 
            )
			{
				return true;
			}
			return false;
		}

Which added 1/4h to the job. 

Conclusion

We need to take extreme care with personal data and so the idea of saving the old xml file to disk isn't acceptable unless it is suitably encrypted or securely within the TI's own system.

Alternatively, simply storing say an ID and a hash of the ToString() of the class instance and in clear (one is a string of comma separated numbers the other is a number) would offer a good approximation.

However the principles are sound and coding time here for TIs is relatively short.  Given that reporting covers most of the data and is effective for daily updates then it worth considering for appropriate integrations.

The example stores test data in clear on a machine which is contrary to GDPR if used with real data .  It is intended to offer concepts rather than a complete solution and appropriate alternative mechanisms are discussed in the text.