DIY Incident Reporting System - php/mysql/SIMS Export - Talk Me Out Of It?

[Miscbrah]

Afternoon everyone.

 

I need talking out of an idea I've had. Or talking into it (whichever seems most sensible.) I've put together projects with mysql/phpmyadmin setups before now but it's been a few years and I am extremely rusty!

 

What we're looking to make is a little locally hosted site (if this is even the most sensible option) which some users will log into to log (and also generate reports of) any accidents that happen to students. I can manage these user logins by hand as there'll only be half a dozen.

 

A user will, in my ideal world, log in and see two buttons - one takes you to a "log an incident" page, another to a "incident reports" page. To log an incident, you'd firstly select a student by beginning to type a name which it'll try and auto-complete for you. You'd then select the location (checkbox option) type of injury (checkbox option) and select the time (widget I haven't figured out yet) and add some brief notes then hit "submit." It thanks you when you submit it. Once done it'll then immediately email a group to notify there's been an incident, and include all the info logged. Email doesn't need to look posh.

 

Second button takes you to the "incident reports" page where you can, by name or date or class or yeargroup, generate a simple web page report of that info. Ideally going back years (if this thing lasts years.)

 

So, login page I can handle. SIMS report to get the .csv of student info I can (hopefully) handle and can get that importing into mysql overnight each night to update.

 

The rest of it I'm googling at the moment.

 

Is this seeming do-able or should I abandon? Any hints or thinking or cautions on any of it?

 

Thanks everyone.

[mavhc]

seems fine, I would have said why not use your MIS, but it's SIMS, so that's why.

 

How will you outsource auth? ldap?

 

How will you prevent sql injection? backups? gdpr compliance, will you remove incidents after x days? when students leave?

[3s-gtech]

We do lots of similar things, as we use SIMS and have our own MIS extension package built around it (which as been in development for years!)

 

I would use XML for student data export/import. It is so much easier to handle with PHP. SIMS sometimes shifts the format between CommandReporter and manual reports, but that's easy enough to check.

 

PHP Mailer should be able to handle the email side, if you can use SMTP. I recommend that you put the config for that in one central file outside of the web root.

@mavhc touches on some great points - you will need to do a DPIA for this.

[David44]

As fun as it sounds, do you already have a system you can create this in rather than making your own? Can you make it in Google AppSheet or something similar? It would handle a lot of the boiler plate stuff for you.

 

If you make your own system, look in to having your users sign in with Google/Microsoft/AD accounts rather than making your own authentication system with your own accounts and passwords. Your users will thank you.

 

Also, maybe a framework like Laravel would be better nowadays than plain PHP. I used Laravel to make a student rewards system which I completed during lockdown and is still in use today.

 

Why not go all in, create a company and sell the product to the school as a SAAS product (even just for a nominal fee). It's easier to get things done if you are working on your own product for your own company rather than having to get approval to do anything from the school.

[Miscbrah]

seems fine, I would have said why not use your MIS, but it's SIMS, so that's why.

 

How will you outsource auth? ldap?

 

How will you prevent sql injection? backups? gdpr compliance, will you remove incidents after x days? when students leave?

 

Haha, yes SIMS it is. Not got to the auth yet, there'll be something token-y going around (he says. I haven't looked yet.) It'll only be a local site on a little VM so can restrict traffic to it easy enough. Was thinking on GDPR as it happens, and assume it's fine to keep those as long as SIMS records. I think it'll be some half-manual process for new year intake setup also, will tackle it then maybe unless I dream up something better. Could always add a year of leaving into the thing from the SIMS export and then if that's older than x delete the records. Needs more of a think on all that but won't be huge I don't think. All worth bearing in mind for sure though.

 

We do lots of similar things, as we use SIMS and have our own MIS extension package built around it (which as been in development for years!)

 

I would use XML for student data export/import. It is so much easier to handle with PHP. SIMS sometimes shifts the format between CommandReporter and manual reports, but that's easy enough to check.

 

PHP Mailer should be able to handle the email side, if you can use SMTP. I recommend that you put the config for that in one central file outside of the web root.

@mavhc touches on some great points - you will need to do a DPIA for this.

 

Didn't even think of xml that's much better!

 

As fun as it sounds, do you already have a system you can create this in rather than making your own? Can you make it in Google AppSheet or something similar? It would handle a lot of the boiler plate stuff for you.

 

If you make your own system, look in to having your users sign in with Google/Microsoft/AD accounts rather than making your own authentication system with your own accounts and passwords. Your users will thank you.

 

Also, maybe a framework like Laravel would be better nowadays than plain PHP. I used Laravel to make a student rewards system which I completed during lockdown and is still in use today.

 

Why not go all in, create a company and sell the product to the school as a SAAS product (even just for a nominal fee). It's easier to get things done if you are working on your own product for your own company rather than having to get approval to do anything from the school.

 

Yes it's seeming like fun*. I think it's just a little too rich a thing for a Google sheet but I did wonder that I could get there that way. Or rather, looks like a bit too much fiddling to do that I'd rather fiddle with another thing to do.

 

I'm quite sure I don't want to set up my own company and do it also but thanks for the idea! I could call it "disappointing misadventures in mysql" I suppose!

 

Thanks all for the advice. I think I'm on the verge of saying I won't bother and buy something in. It's a nagging thought though as it's starting to come together...

[howartp]

We've just bought into the Wonde API for interacting with SIMS on similar PHP/MySQL type usages.

 

£250 setup and £10/month, per school.

 

Also using Azure/365 for auth (and thus 2FA).

[DrCheese]

Don't do self built systems, you leave your school & successor a headache when you leave, especially if you've made it *super* custom for what your school wants.

It also eats into your time to do other things as you spend half your day fixing bug

 

There's a commercially supported system for what you're after here https://www.medicaltracker.co.uk/ - Doesn't cost a fortune.

[Andrew_C]

Could always add a year of leaving into the thing from the SIMS export and then if that's older than x delete the records.

 

Concider date retention requirements. You might code it to automatically delete kids after leaving unless there is a flag set that they had an incident of "X TYPE" in which case "n" years retention.

[dmj]

SIMS report to get the .csv of student info I can (hopefully) handle and can get that importing into mysql overnight each night to update.

 

I would use XML for student data export/import. It is so much easier to handle with PHP. SIMS sometimes shifts the format between CommandReporter and manual reports, but that's easy enough to check.

 

I'm not keen on either of these methods TBH, this should be a straight API call. You shouldn't need to have a database to replicate another datasource, it just adds unnecessary duplication.

I'd also advise against Mysql, if your php framework supports postgres it is a much better choice.

 

Once done it'll then immediately email a group to notify there's been an incident, and include all the info logged. Email doesn't need to look posh.

 

Should probably send a link to the URL with the indicent, rather than emailing confidential data. Thinking data retention, GDPR etc.

There are enough libraries in php which will give you SAML/OIDC for you Azure/Google/Directory that will handle users.

[3s-gtech]

I should add, that along with LDAP internally ours uses OAuth2 for authentication so goes in our M365 for that. Makes it all a hell of a lot more secure.

 

You want not just your code to be commented heavily, but write a technical doc for it too. Mine is my bible, because I never remember the yearly tasks.

 

Try to avoid any manual DB tasks. I've spent the last five years automating data pulls from SIMS; where it used to be an Excel spreadsheet and importing with HeidiSQL there's far more XML parsing overnight now. Create a secure management web back end for select users.

 

Look at using an identifier for the students, rather than their name, in a separate table. This can be UPN (but this is supposed to be kept confidential). We use their login, which means it has to be in SIMS (UDF). You can then look this value up, but any incidents are just logged against their unique identifier.

 

I spend most of my time developing and bug chasing our system, but it is tens of thousands of lines of code so starts to justify the time commitment as it saves us a fortune in third party software.

[jthompson]

Google AppSheet app with the students and staff tables being populated automatically somehow. If it were me, I'd have that being done by some Apps Script that makes API calls to Wonde.

[Jayem]

Why bother. Look at CURA from TASC Software.

[Jayem]

There is also a Medical and Accident recording system within TALAXY - also from TASC Software.

[machy]

If you want to be talked out of it a couple of things to consider.

 

 

Is your time cheaper, when you consider building, updating, re/learning the tech to do it, fixing it when it breaks and the Liability for you and the school of data protection? When its compared to an off the shelf tool such as CPOMS or Evolve.

 

 

You get this working, 2/3 years down the line after staff are used to it, you win the lottery and decide to fall of the face of the earth. The school needs Data off it urgently for some sort of 999 level thing, but one of the weird sql calls you have to do to get it working fails for some reason, then what will the school do?

[dmj]

Is your time cheaper, when you consider building, updating, re/learning the tech to do it, fixing it when it breaks and the Liability for you and the school of data protection? When its compared to an off the shelf tool such as CPOMS or Evolve.

 

IT tech time in education is always cheaper than paying developers wages

[TriggerHappyUK]

In my previous job we used MyConcern (https://www.ess-sims.co.uk/myconcern)

 

It synced nicely with our AD/AAD and is compatible with SIMS.

 

Worked really well and easy to manage/maintain

 

 

Like DrCheese says above: Why reinvent the wheel and leave your school/successor a headache if/when you leave.

Edited October 17, 2024 by TriggerHappyUK

[Jayem]

For busy schools with complex communications channels, Talaxy’s accident recording helps keep records in order by giving staff a straightforward way to record accidents and other medical incidents in a clear and unambiguous format.

Key features include;

 

• Supports the recording of injury location on a body map

 

• Informs key staff when a student is sent home or to hospital in the aftermath of an incident

 

• Record both treatments and outcomes to complete the picture

 

Provides parents with real-time updates.

 

And as it is cloud based, accidents can be logged anywhere you have a phone signal.

[dmj]

For busy schools with complex communications channels, Talaxy’s accident recording helps keep records in order by giving staff a straightforward way to record accidents and other medical incidents in a clear and unambiguous format.

Key features include;

 

• Supports the recording of injury location on a body map

 

• Informs key staff when a student is sent home or to hospital in the aftermath of an incident

 

• Record both treatments and outcomes to complete the picture

 

Provides parents with real-time updates.

 

And as it is cloud based, accidents can be logged anywhere you have a phone signal.

@miscbrah Looks like you found yourself a product manager for this project. Add these to the roadmap

[DrCheese]

If you want to be talked out of it a couple of things to consider.

 

 

Is your time cheaper, when you consider building, updating, re/learning the tech to do it, fixing it when it breaks and the Liability for you and the school of data protection? When its compared to an off the shelf tool such as CPOMS or Evolve.

 

 

You get this working, 2/3 years down the line after staff are used to it, you win the lottery and decide to fall of the face of the earth. The school needs Data off it urgently for some sort of 999 level thing, but one of the weird sql calls you have to do to get it working fails for some reason, then what will the school do?

 

Yes, this is why I said not to do it.

 

The former NM here wrote a reporting system in MS Access & MSSQL that the school relied on - I did not know Access all that well so it was a massive pain in the backside for me. It would break often & I ended up having to pay consultancy fees for his time after - Because it was super custom for the school it was difficult to get rid of.

 

I then allowed my new tech to recode it all in PHP/MySQL which he did, but once he left it (& other pieces of software he wrote) became a pain again - Admittedly not as much as the MS Access one but still.

 

I just won't allow custom software that isn't supported anymore to be built by anyone in the organisation - If software doesn't exist for what we're trying to do, then we're probably doing something that we don't really need to do (Schools are Schools) - We (& the school) need to be able to reach out to support & have them fix any issues.

 

Custom software works in large organisations whereby you have a team of developers who can support it so if someone leaves it's not the end of the world, but it just doesn't work in small organisations - Especially if they then go and base their entire business processes on it.

[dmj]

Yes, this is why I said not to do it.

 

The former NM here wrote a reporting system in MS Access & MSSQL that the school relied on - I did not know Access all that well so it was a massive pain in the backside for me. It would break often & I ended up having to pay consultancy fees for his time after - Because it was super custom for the school it was difficult to get rid of.

 

I then allowed my new tech to recode it all in PHP/MySQL which he did, but once he left it (& other pieces of software he wrote) became a pain again - Admittedly not as much as the MS Access one but still.

 

I just won't allow custom software that isn't supported anymore to be built by anyone in the organisation - If software doesn't exist for what we're trying to do, then we're probably doing something that we don't really need to do (Schools are Schools) - We (& the school) need to be able to reach out to support & have them fix any issues.

 

Custom software works in large organisations whereby you have a team of developers who can support it so if someone leaves it's not the end of the world, but it just doesn't work in small organisations - Especially if they then go and base their entire business processes on it.

 

This is also how Opensource projects get started, and companies form around them.

Sounds like the mistakes your school made was not to share.

[DrCheese]

This is also how Opensource projects get started, and companies form around them.

Sounds like the mistakes your school made was not to share.

 

Right but all of which takes time to support & sort - A lot was hard coded or built for how the school wanted it.

I honestly just don't think it's worth it.

[dmj]

Right but all of which takes time to support & sort - A lot was hard coded or built for how the school wanted it.

I honestly just don't think it's worth it.

 

It probably depends on the team, their skillset and the school philosophy. There's not a right or wrong answer: it will work for some but not others.

[DrCheese]

It probably depends on the team, their skillset and the school philosophy. There's not a right or wrong answer: it will work for some but not others.

 

Yeah maybe - I don't think it would have solved the root problem tho, that if something is mission critical you don't have people to call up for support who *must* support you rather than doing it out of goodwill, assuming a big enough community is around it in the first place.

 

But this of course, comes down to your own/schools risk profile

[Miscbrah]

Thanks all for the input. I probably won't.

 

I've been a great fan of not having weird, homemade things/mystery boxes/oddness in place on networks but this felt slightly more achievable and easy to undo should the worst happen (like that lottery-win-instant-fleeing scenario) - however that principle is VERY sound indeed and so I'll be parking this.

 

Thanks again everyone for mulling it over with me.

[Jayem]

TASC Software has been working with schools for over 25 years and has an excellent reputation for the levels of support they give to users of their software.