[Development] An Open Source Parent Gateway

[zag]

Small update using some dummy data

 

[zag]

So I'm just about to give up on this....

 

I've been speaking to some really helpful people at Capita but they appear to not be able to offer any API's or methods without a significant payment to them. how much you ask??

 

£12,500!!!! (5 year minimum term contract) for becoming a "Sims SQL associate partner for schools"

 

This kind of ridiculous price really does make me question their business practices even more. Its supposed to be an open source project ffs.

 

All I want to do is access the reports we create in a batch fashion. The guy couldn't explain how SLG does it either.

 

So for now this is dead in the water unless I can find a better way of scripting commandreporter.exe

Edited March 29, 2012 by zag

[PhillipHamlyn]

Zag,

 

I dont work in that area of the Capita business, but I think the situation is different dependent on whether you represent a School or are an independent software developer. Also the situation varies depending on whether you require read-only or read-write access to the system, and whether you use Command Reporter or the .net API library. I think your post might not be an accurate representation of the API licensing scheme, so its possible you might not have got through to the correct Capita dept. Again I must stress I dont work in that area of the Capita business, but recommend (if you have not already) contacting Jon Wood ([email protected]) or David Grashof ([email protected]) who will be able to give you the most accurate current information on access to SIMS .net.

[David_Grashoff]

Zag,

 

I dont work in that area of the Capita business, but I think the situation is different dependent on whether you represent a School or are an independent software developer. Also the situation varies depending on whether you require read-only or read-write access to the system, and whether you use Command Reporter or the .net API library. I think your post might not be an accurate representation of the API licensing scheme, so its possible you might not have got through to the correct Capita dept. Again I must stress I dont work in that area of the Capita business, but recommend (if you have not already) contacting Jon Wood ([email protected]) or David Grashof ([email protected]) who will be able to give you the most accurate current information on access to SIMS .net.

 

Thanks Phil. Zag my email address is slightly different to the one quoted [email protected]. However please note I am out of the office until April 10th but will repsond earlier if at all possible.

[bantonia]

Hi,

 

I contacted David Grashoff earlier in the week, he was very helpful. From the information he sent me it appears if you are a representative of an educational establishment with support from your local authority you go via them, if you are supported directly by Capita you go to Capita to obtain access to the "Business Objects". There are hoops to go through, I've still to work out what is actually required but if you do not require support and are going to take a very dangerous risk and plan to use your live SIMS database as the guinea pig, then there is no charge as long as you do not plan to pass on your implementation. The alternative is to buy support for £2500 in which you are provided with a test database to experiment on as part of the package. Again you cannot pass on your implementation. There is a non-disclosure agreement to sign also. However, if you are planning to sell your implementation then the costs are much higher as you become a "Partner", there are various levels. This is the business model rather than the proficient .net programmer in a school who wants to integrate their systems.

 

I think I got this right from what I read ........

 

Regards

Edited March 30, 2012 by bantonia

[vikpaw]

Don't be put off @zag - contact David as i suggested earlier.

As Bill says, for your own use within school, there shouldn't be a charge, especially not for read only access. You can set up your own test server with live data, it works better that way, and have a play.

Even without the APIs, there are other threads covering how commandreporter has been scripted, and you might find manually scripting a few calls to that is easier to do what you want, without the headache of understanding the business objects.

[CyberNerd]

There is a non-disclosure agreement to sign also.

 

How would that work in an Open Source project ? You can use the business objects as long as you don't release it open source?

[bantonia]

If you use the commandreporter.exe program, no problem. If you use the "Business Objects" then you have a problem regarding non-disclosure.

 

Every SIMS installation has within the SIMS .net directory the commandreporter.exe program so it is there to use. If you run it with /HELP or /? as a parameter or on its own it will return some details of the parameters required to be able to run SIMS reports from the command line. By running this from another program, with the correct parameters for access to a report you can trap the output and use the data returned. The only problem is you have to parse the XML. I do this but the calling program (client) written in Java calls a RMI routine sitting on the server which in turn runs the commandreporter.exe software with the correct parameters. The returned XML is then parsed and the data marshaled into a Java array as an object and is then sent back to the client. I have clients running from cron jobs on Linux.

 

If you go to: http://sourceforge.net/projects/simscommandrepo/ you can download the server software and library for Java. Don't know why there is one not recommended???? They did not leave any feedback hence my comments in the project summary.

Edited April 1, 2012 by bantonia

[matt40k]

From what I understand of what Zag trying to do is, it is possible via the SIMS reporting engine, so he can use the commandreporter as previously stated. I'm happy to help but I need a few bits of information. (PS: It might be possible to use the VLE tool - but I'm not sure as I've never really used it).

 

Firstly, I assume this parents portal won't be using AD authentication? I assume the school will be using a externally hosted. It isn't a problem if they aren't - I'm just assuming we're having to generate user\pass for this system - I assume the nature choice would be using email addresses. The way I would do this, is a FULL export hourly\daily\weekly\monthly\termly\yearly\whatever you set then use file compare (ideally on the webserver) to pick up changes - this way you avoid students who got deleted off SIMS and the whole unsync'd issue if the link breaks for a while.

 

Assuming I am correct you would want to pull out parents email addresses - ideally you want the parent to be able change there email address online, however without the business objects and a butt load of work this won't be possible, so if you do add a "change email address" it'll have to ping off an email to the School Office so they can manually change it in SIMS (which the site will pick up when they next reload).

 

The next bit would be an opt-in\out option, not all parents might want this, or even be allowed it. So we'll want to filter on parental consent at least, having said that, this might not be as simple, still with SIMS reports the end-user can edit them as need, for example, we could add in a UDF field for parent gateway access then only parents who have the option ticked, or unticked depending if it's a opt-in or a opt-out thing.

 

Next we need to match up parents-pupils and also each document to a pupil. Just remember we don't want to expose UPNs to parents so it might be worth using the internal SIMS person id. We also want to give the documents friendly names and in a format that most people are going to be able to open - PDF would be a good choice.

 

The final piece would be getting the data securely to web server. It MUST be over HTTPS. Maybe zipping and encrypting the files too, or is that too much? So the parent gateway website would need a backend transfer api (thingy).

 

Hope that all makes sense

[bantonia]

Hi Matt,

 

What I did for proof of concept was to use the email address of the primary contact(s), ie parent/carer would login using their email address as the username/password. Once this was verified in the SIMS database (by running a report, not "Business Objects"), an email would be sent to that address with links containing a time dependent token which was stored temporarily in a MySQL database. If there was more than one child then multiple links would be sent in the email. Each time the parent/carer set up the initial communication the tokens would be different, the connection between token and the identity of the student was stored with a copy of the token in the MySQL database. The link could go back to a server using https so the content returned would be encrypted. The server software I used was Glassfish running Java servlets. The data returned appeared on various pages reporting on attendance, behaviour, class details, timetable, basic information and assessment, the last part I did not get to finish the formatting. I had all this in place except the https stage and never let it go live out of school to parents/carers on the Internet. I've used the code elsewhere in other projects so the time was not wasted. By the way Keep Kids Safe uses the primary contact email addresses for their communications between schools and parents/carers so this is not new. Prohibiting data being made visible is easily managed by removing the parent/carer as a the primary contact, which would be the case in court orders etc. This obviously would require stringent management of email change requests.

[matt40k]

Not sure that would e the best way. Wouldn't this cause issues when generating the data collection sheets?

[bantonia]

Not sure that would e the best way. Wouldn't this cause issues when generating the data collection sheets?

 

In what way?

[matt40k]

Ok, bad example, was trying to think of a report that would be filtered based on primary contact that would be affected. Just thinking of photos - it used to be one tick box but now I've see about 5 or 6 different sub-options.

[bantonia]

Hi Matt,

 

There is probably some sort of caveat and someone might find something to make this type of authentication problematic.

 

By the way, I had also in place that the IP address of the originator of the communication was stored in the MySQL database attached to the token so if the token was intercepted on route back to client and used with a different IP address the communication would fail to that new address. The only problem I had was that traffic was going via a Apache redirector so the Glassfish server saw the originator as the Apache server all the time, probably I haven' t got the settings correct there!

[zag]

Finally got through to the correct person at Capita, it was a guy called Andy McGowen.

 

He explained that the commandreporter.exe tool is not capable of exporting already made reports in batch so thats a dead end.

 

Apparently no other school has done this yet but it would be possible to write a tool with a small amount of consulting time:

 

Something like this in pseudo code:

 

a. User will choose Profiles / IR

b. User will choose the session

c. User will choose the set of students to publish.

d. User will choose the folder to extract decrypted documents to.

e. System will queue the task to run at 21:00 that evening.

f. System will create the documents in the named folder

 

I assume it would be a command line tool that would be scheduled.

 

So now I can get on with the PHP side of things in the knowledge that it is possible (with a little money spent).

 

My ideas for the php web front-end are still the same.

 

- Parent name/pupil email CSV import

- "Single Sign on" logins with the student account

- Secure simple access to reports and profiles in PDF/Word format

 

Thanks everyone who gave advice.

Edited April 12, 2012 by zag

[bantonia]

Hi Zag,

 

I run reports in batch and have done so for quite some time using a library and RMI server I've developed in Java. The data is sent directly as objects back to the client from multiple reports. Client running on Linux or MS Windows if you want to. Can also use data from SIMS in a desktop Java app but the response is slow due to the speed of running reports. For example, creating comboboxes in a Java app or applet from data derived from a report, amongst other widgets etc.

[matt40k]

He explained that the commandreporter.exe tool is not capable of exporting already made reports in batch so thats a dead end.

 

Eh? That's rubbish. You run 1 report to get your list of users, then you loop through for each user to run the report.

[rpwillis]

Eh? That's rubbish. You run 1 report to get your list of users, then you loop through for each user to run the report.

 

I think that @zag is talking about reports saved in the document server, which I don't think can be accessed via Command Reporter. When one of our customers moved from Sims to another MIS last year, I wrote a little utility for them which extracted all the documents from the document server for each pupil, so it can be done.

[matt40k]

What reports are they out of interest?

[zag]

I think that @zag is talking about reports saved in the document server, which I don't think can be accessed via Command Reporter. When one of our customers moved from Sims to another MIS last year, I wrote a little utility for them which extracted all the documents from the document server for each pupil, so it can be done.

 

Exactly!

 

Is this utility available? Sounds like exactly what I have been trying to achieve.

 

They are progress reports saved on the document server and I have logic to select the correct files done already. Its pretty easy based on the filenames.

[bantonia]

Hi,

 

Just for information really, I've been updating the way I access SIMS reports remotely and have created a .war file which will run via a GlassFish server installed on either a SIMS workstation or server. It creates a WSDL file automatically which can be used to generate artifacts in the programming language of your choice. Anyway, it can be downloaded from:

 

http://www.beaconhillcott.net

 

Happy programming, (I hope!)

[zag]

Just an update on this.

 

After speaking to many people about this there didnt seem to be an easy way to access the document server.

 

We recently ditched Capita SLG and replaced it with TASC insight

 

I'll do a full review later but I must say this piece of software is superb. Not only is it an excellent, intuitive, easy to use and administer Parental Gateway. It also includes a an open access API that I am hoping to use for the customized projects I was talking about at the begging of this thread.

 

Consider me impressed with this software. I'll try to talk some more about it in another post.

Edited May 30, 2012 by zag

[Tim158]

As a full-time teacher at the time, I was trying to develop a rewards system for my school (1-2 years ago) and eventually managed to produce a PHP / batch file solution to extract data / reports via commandreporter. I eventually decided to sell my rewards system and now about 10 schools have adopted it all over the country. Capita did catch up with me recently and informed me that I was breaching their licence by using commandreporter as a business... and so I now have a paid-for third party licence from Capita. Please be aware that you do need a licence if you ever want to sell a product that uses commandreporter! I was not aware of this. I can now use the licence to brand reports etc.

 

My rewards system uses data from SIMS to keep class lists up-to-date, display attendance data to parents and students, automatically create new user accounts etc. I have since developed a Moodle add-on for schools that use the rewards system and Moodle (single login).

 

Another word of warning - Capita are not impressed by the use of a batch file to run commandreporter as the username and password has to be stored in plain text. Although I don't see the problem if the bat file is not accessible to users on the network, I now have a single non-readable file that does all the work instead i.e. a Java app, which means it is no longer necessary to install IIS, PHP etc to extract and send data - makes installation simple. Having any port open to allow access to a school server has always sounded dangerous to me so the communication to my rewards system is only one-way (sent encrypted from the school server, via the proxy, to my server).

Edited June 13, 2012 by vikpaw
Unauthorised Advertising

[bantonia]

All my stuff is open source. I think of it as repayment for all the other open source I use.

 

As for my current "Learning Gateway" like software, it is currently mothballed but may be revived as and when required by my College. Anyway, apart from SIF and Moodle web services, which have been put aside for the moment, current development is towards calculating value added using an Applet. Data being extracted from SIMS via CommandReporter using SOAP calls from another server which stores the data in a MySQL intermediate database. The Applet is accessed via the same intermediate server but authentication is required from AD to gain access to that web page. The Applet uses REST calls to get its data. Communication between Applet and intermediate server is via HTTPS, no username or password being stored within the Applet or files as clear text, the username and password used to access the site is also passed using HTTPS.

 

My mothballed "Learning Gateway" lookalike currently uses Java RMI, I will probably recode it to use SOAP to fetch the data from SIMS over HTTPS. Eventually I hope, in the future to have that data being passed using SIF messages, but that's some way off at the moment.

[matt40k]

As a full-time teacher at the time, I was trying to develop a rewards system for my school (1-2 years ago)

 

Shame your school dropped SIMS.