[sims] Creating MSSQL scripts

[Pashers]

Hey all,

Just wondering what we are allowed to do in doing SQL queries on SIMS. I presume we're allowed to do select statements?!

Edited February 1, 2011 by Pashers
Forgot to mention we use SIMS

[Bromcom_John]

It all depends on the policy of the software supplier who provides your MIS.

[matt40k]

What are you trying to do?

[Pashers]

Well I've managed to gather the timetable for the year for an individual teacher. With teaching lessons/rooms and assigned non-teaching lessons (i.e.. if they have other duties). It'll put in in a csv format so the teacher can import into outlook's calendar. Just wondered if it was OK to do this or not. Took ages to find the correct views etc.

[matt40k]

Sorry, I'm current away banging my head against the wall. This has come up a number of times, suggest you do a search.

 

http://www.edugeek.net/forums/mis-systems/64334-commandreporter-staff-timetables.html

Edited February 1, 2011 by matt40k

[Pashers]

I did have a search around and I wanted it slightly different, I wanted it so each day had a date assigned to it (e.g. 17/01/11 was Mon1) etc. Then the staff could import it into the outlook calendar. I didn't just want each day if that makes sense.

[matt40k]

So let me get this right, you want to be able to exportthe SIMS timetable for each teacher and be able to import it into Outlook Calander?

 

The timetable needs to include, class code (10a/Eng5), Subject, Room and "day" (Mon:1)

 

And you'll want school events as well?

[Sarconia]

Generally I find that most educational software houses stipulate that schools should not even go into SQL and execute any queries at all, it's normally written in their contracts. However, select statements are harmless so as long as you don't tell the provider you actually went into SQL and had a prod around - it's all good!

[Pashers]

So let me get this right, you want to be able to exportthe SIMS timetable for each teacher and be able to import it into Outlook Calander?

 

Yes, I've managed to do the query so it retrieves the date/day/class code/non-teaching (for example if they are on call, reg, cover)/holidays/school activities.

 

And I've tested it and imported it into the outlook calendar. Works beautifully .

 

But just wondered if we were allowed to select from SIMS.

[matt40k]

What user are you running it as? SA?

 

Capita have a tool called DBDiagnose.exe, it's in the SIMS Application directory (C:\program files\sims\sims .net), basically, it runs SQL scripts on the SIMS database, BUT they need to have a signature.

 

If you're thinking of making it public (on supportnet, edugeek or such), I would try and get they them to create a signature for it. At least then it uses the SIMS permissions, it's had the "this won't destroy your data, but we won't support it if it doesn't do what it says on the tin" seal of approval from Capita.

 

Personally, you're re-inventing the wheel.

[Pashers]

Here's an example of what the outcome would look like:

Date, Day, Subject Code, Start Time, End Time, Room, Subject

02/02/2011 00:00:00, 2We, 12B/Ma3, 10:10, 11:10, D9, Ma,

 

Then the teaching staff can choose what fields they wish to have and import it into the outlook calendar. So for example they might just want the date and time (obviously needed), and subject code and the room.

 

If there is a script out there which someone has already done which gets this then yes I have re-invented the wheel :-).

 

Whereas, I got the impression that the one you showed me didn't do the date it just got the day i.e. Mon1. When I wanted 07/02/2011 - Mon1 etc.

[matt40k]

Try the attached

 

Staff timetable export example.7z

[Pashers]

It didn't have the dates assigned to the days. I've created a program now which does the following:

The staff enters in their staff code.

The script runs and saves a text document in the following format - Date/Day Name/Class Code/Period Start/Period End/Room/Class

so the result would be: 02/02/2011, 2Tu, 7DS/Ar, 10:10, 11:10, D8, Ar,

 

But it will display the results from today to the end of the year. They then could import it into the calendar.

 

The teacher could then go into Outlook and import this data into the calendar.

[matt40k]

True, but it's not that more difficult add it on. I could if I had the time, I could create a app that would run it for the school year or a specific date and give each staff a csv import, having said that, I would get it to auto import.

 

It would 1. be safe, 2. be customable by the school (just edit the SIMS report), 3. use the SIMS permissions

 

The idea of using the SIMS permissions\system is that it's all audited. I'm guessing your using the SA or such account and bypassing this.

[Pashers]

I've had a look at the report and I must admit I don't use SIMS at all. So I might be missing the point with the report, but when I run it it still shows them teaching on half term etc. Also it still didn't tell me if it was Mon1 or Mon2, it still came up with both and for the week.

 

Yes I'm using the SA account to bypass it. Now I have the query I might make it so they can access it via the VLE too. so they can download it and import it etc.

[PhilNeal]

You do need to be careful with all of this scripting.

The SA account completely by-passes every security feature that we have put into SIMS and I wonder if the school fully appreciates that this could be an issue in the future for example you will be able to edit your salary details with no audit.

 

There is an IMS feed available within SIMS that automatically updates a number of VLEs. Quite a number of VLEs automatically link to SIMS.

 

Capita does not support direct writing to or reading from tables in this way.

Edited February 7, 2011 by PhilNeal

[matt40k]

but when I run it it still shows them teaching on half term etc. Also it still didn't tell me if it was Mon1 or Mon2, it still came up with both and for the week.

 

I'm questioning if you are a school, if you are, you're poorly setup.

[Pashers]

I'm questioning if you are a school, if you are, you're poorly setup.

 

How would this relate to the school setup, it's got the holiday terms and all the information in SIMS, and the query I've created does exactly what I want it to do - the report you showed me didn't do exactly what I want it to do.

 

- Matt40k You mentioned you can get it 'wrapped' up with SIMS approval and make reports. How do you go about doing this?

[matt40k]

I'll check it out in the morning, pretty sure it didn't give you a timetable over holidays. Mon1\mon2 etc refers to you having a two week timetable, again, I'll have to check - Green Abbey only has 1 week timetable, but if it gives you just mon, I'm sure it means you have a 1 week timetable.

 

Wrapping, no idea what the official route is, I just normally kick who I think can do it until I get it or they direct me the right person (or someone that may know) - refered to as 40k magic.

[vikpaw]

I can vouch for Pashers coming from a school.

 

As for the wrap up. For an update I wanted to make, I was told to submit any request to usual support channel, though for me that's quite a nice route to someone v.knowledgeable and who can kick the right behinds to get something done.

 

The routine gets written up as a site specific patch, checked and verified then returned. So it can be applied. I'm assuming something similar could happen for your query. However if it's just for your own use, it should be fine.

 

Ideally, if you run it against views rather than tables, then at least it's abstracted away from the core data. Plus you'd hope that if the table was modified or the field moved, the view name would stay the same and still present the same data.