Automatic generation of Student number and HR student files

Automatic generation of Student number and HR student files

by Giulia Clifford -
Number of replies: 16

Hi guys,

I have recently installed Moodle as the intranet portal for my husband's new school. He's very excited about this new adventure, but has no way to generate automatically students ID numbers and is worried that if we input them manually in Moodle and he happens to have two or more administrators that deal with admissions and student registrations, that there could be duplications and confusion.

Is there any plug in (I tried to search for one but I can't find any?) available for moodle to generate automatically a student number when the student is registered with his/her profile on the system? If there is no way of doing it with Moodle, is there any software anybody can recommend that would do that for my husband?


Also, is there any kind of plug in that allows the storage on the user's profile of files that can be seen only by Administrators and not by the students themselves? My husband needs to record a lot of details for his students under the regulations of the awarding body for the qualifications he provides and would love to have the possibility to store "HR files" like for example passport picture, scan of the student's application form, a copy of any disciplinary or other HR document related to the student? I couldn't find anything in your plug in list and I'm not as good as you are guys, i would NEVER be able to design one myself!

Thanks to anybody who can help!


Giulia

Average of ratings: -
In reply to Giulia Clifford

Re: Automatic generation of Student number and HR student files

by Michael Milette -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

Hi Giulia,

STUDENT ID

Every student in Moodle has a unique ID assigned to it which is the 'id' field in the mdl_users table. No additional plugin required.

The following SQL could be used to generate a student ID report in Ad-hoc or Customizable SQL:

SELECT RIGHT(CONCAT('00000',id),6) as "Student ID", username, firstname, lastname FROM prefix_user

Just make sure to adjust that the number '6' so that it is always equal or greater to the number of characters you will need or it will clip off leading digits.

If you wanted to display a particular student's ID in a page, the PHP code would be something like this:

echo echo str_pad($USER->id,6,'0', STR_PAD_LEFT);

Unlike the SQL code, this version will automatically adapt to longer numbers.

STORAGE OF FILES FOR ADMINISTRATORS

I've never done this myself but I do have a few suggestions to offer that you could try. Before I get into them, Since the students won't have access to these files, is there a reason that you want the files to reside within the student's profile? 

If not, I think the easiest solution would be to set up a course for administrators and store your files in there. Just create a hidden course called "Administrators". By default, it will only be available to your administrators. In it you could add 1) a "File System" repository to store files relating to each student using a naming convention to easily identify to which students they related or a database activity with either a File type field or a Text Area type filed where you could add attachments , 2) a forum for discussing system administration amongst your Moodle administrators and possibly managers and 3) a wiki for your administrators to build create online documentation over time as they work in Moodle. Keep in mind that the database activity was not meant to be very large so you may eventually run into problems depending on the number of entries you want to maintain.

Alternatively, if you or someone you know doesn't mind doing a little programming, a recent post How to selectively hide individual files that are contained inside a folder resource by Frankie Cam may also be of interest to you. The down side of his solution is that technically, if someone had the URL of a file, they could still access it since all it does is hide the link to the file and not really restrict access.

I was also going to suggest that you add a "Text area" custom profile field and set the visibility to Not Visible however it doesn't appear to support file attachments. However, if all you need is to have text, its WYSIWYG editor should do just fine and only Administrators and Managers will be able to see and edit its content.

Hope you find something helpful in all of this.

Best regards,

     Michael Milette

Average of ratings: -
In reply to Michael Milette

Re: Automatic generation of Student number and HR student files

by Michael Milette -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

To take the STUDENT ID idea one step further, it would be possible also prefix the student ID number above with the year that the Moodle account was first created so that you would end up with something like 2015000015 instead of just 000015 for student #15. In fact, the whole date and time is available so you could prefix the 000015 number with as much of that information as you want. Of course the first student of 2016 will not be 2015000001 because the ID number portion of the number is sequential.

Best regards,

    Michael Milette

Average of ratings: -
In reply to Michael Milette

Re: Automatic generation of Student number and HR student files

by Giulia Clifford -

sad is there anybody who can help me? Sorry to be a pain...

Average of ratings: -
In reply to Giulia Clifford

Re: Automatic generation of Student number and HR student files

by Conn Warwicker -
Picture of Core developers Picture of Plugin developers

I don't think his SQL answer will really be of help, as it would only create a student number (e.g. 0000123) on the fly when running a report, it wouldn't store it anywhere in Moodle.

I assume you want something which will automatically fill in either the "username" or "idnumber" field on each user's profile, with an incremental number?

I will have a think about it over the weekend and see if I can come up with a solution, unless anyone else knows of one.

Average of ratings: -
In reply to Michael Milette

Re: Automatic generation of Student number and HR student files

by Giulia Clifford -

Thanks very much Michael I will need to digest these instructions and try to understand how to use them. I'm not a programmer so I have a very limited knowledge of sql. I'll see if I can understand where to add the code you kindly wrote for me. As of the students ids it sounds a great idea to have an administration course to add all details I'll tell my hubby and see what we can do. Thanks for that!

Average of ratings: -
In reply to Michael Milette

Re: Automatic generation of Student number and HR student files

by Giulia Clifford -
Michael, sorry just one question. Where would you put this SQL code to generate the Student ID? Sorry I know it sounds dumb to programmers like you but I have no idea where to put it in the code to make the student number be displayed... as I said, I'm not a programmer sad

My husband wants me to have this code to show the student numbers by tonight and I haven't got the slightest idea as of where should I put it on. (yes, I have a very bossy husband...thoughtful angry )

Thanks for your help

Regards,

Giulia
Average of ratings: -
In reply to Giulia Clifford

Re: Automatic generation of Student number and HR student files

by Michael Milette -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

Sorry Giulia, just read your message.

The easiest place to put it would probably be in one of the following plugins:

Both of these plugins are used to create reports in Moodle.

Here is an example of how you would go about using Configurable reports. 

  1. Install the Configurable reports plugin.
  2. Login as a site adminstrator
  3. Go to the Front Page and turn on Editing.
  4. Add the Configurable Reports block to the page.
  5. Click Manage Reports.
  6. Click the Add Report button.
  7. For the name, enter "Show my student ID" or something like that. This will be the link that students will see.
  8. Set Type of report to SQL Report and click the Add button.
  9. In the SQL Query field, add the following code and click the Save Changes button:

SELECT RIGHT(CONCAT('00000',id),6) as "Student ID", username, firstname, lastname FROM prefix_user where id = %%USERID%%

You're done. Go back to the Home page. You should now see a link called "Show my student ID". When a student clicks on the link, it will show them a small report displaying their student ID along with their username, first name and last name.

By the way, this SQL Query works in both of the above mentioned plugins however I am not sure students will have access the report if you use the Adhoc Database Queries plugin unless you give the required capability (report/customsql:view).

Let me know which plugin you used and how it went.

Hope this helps!

Best regards,

Michael Milette

Average of ratings: -
In reply to Michael Milette

Re: Automatic generation of Student number and HR student files

by Giulia Clifford -

Hi Michael,


I have just installed the configurable reports block and fought a bit with it to look the way I wanted and to show in the right place and it seems to be working well. Thanks for that. I'll show it to my husband in a minute and I hope he will be happy with it.


Sorry about the late reply, I had a full couple of days!


ETA: I showed it to my husband and he's happy and thanks you for it but he's saying that he needs to have another report that teachers can see with a list of all the students ID numbers in their course and another that admins can see with a list of all the student IDs of every single student in every course and in the whole school because he will need that for the awarding body he teaches the qualifications for. He's saying that unfortunately the fact that the person can see their own ID is great but not enough? sad


Anything you can suggest to help? sad

Average of ratings: -
In reply to Giulia Clifford

Re: Automatic generation of Student number and HR student files

by Conn Warwicker -
Picture of Core developers Picture of Plugin developers

If you want a report to show just the students on a specific course:

Create a new SQL report and make it global (so it can be accessed from any course).

Use the SQL:


SELECT

    RIGHT(CONCAT('00000',u.id),6) as "Student ID", u.firstname, u.lastname, u.username

FROM

    {user} u

INNER JOIN

    {role_assignments} r ON r.userid = u.id

INNER JOIN

    {context} c ON c.id = r.contextid

WHERE

    r.roleid = 5

    AND c.instanceid = %%COURSEID%%


(If for any reason your student role is not ID 5, then you will need to change that number).


Then if you add the Configurable reports block to any course, you should be able to run that report and get a list of the students on the course, with the ID number in that format you wanted.

Average of ratings: Useful (2)
In reply to Conn Warwicker

Re: Automatic generation of Student number and HR student files

by David Mesa -

Hello Conn Warwicker

 

generate your SQL code in my configure reports and served me a lot, I wonder if I could help one to show me the time and date of entry of students and the same teacher and show me who entered this day not I go five days ago .

 

Thank you very much for your help



Average of ratings: -
In reply to Giulia Clifford

Re: Automatic generation of Student number and HR student files

by Michael Milette -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

Hi Giulia,

Conn already provided the code for a per course basis. 

For the list of students for the whole school, use the following SQL code:

SELECT RIGHT(CONCAT('00000',id),6) as "Student ID", username, firstname, lastname FROM mdl_user


Best regards,

Michael Milette

Average of ratings: -
In reply to Michael Milette

Re: Automatic generation of Student number and HR student files

by Giulia Clifford -

Hi guys,


thanks very much for all your help you're wonderful!!!!


Conn, thank you so much the code works perfectly. However, I tried to set up the permission for having the teacher of the course or non editing teacher to see it but if I log in as the teacher of the course it's not showing me the report? Any idea why? sad


Michael, thank you very much! When I try to put the code you gave me on the report the plug in gives me this error: "No explicit prefix" Do you know what it means and how can I fix it? The code I put on is the following, just adapted to my needs:

SELECT RIGHT(CONCAT('BTS00000',id),11) as "Personal ID Number", username, firstname, lastname FROM mdl_user

Anything I've done wrong? sad


thanks again for all your help guys, I would have really been stuck without you!

Average of ratings: -
In reply to Giulia Clifford

Re: Automatic generation of Student number and HR student files

by Conn Warwicker -
Picture of Core developers Picture of Plugin developers

Not sure about the permission, presumably they just need the 'configurable_reports:viewreports' capability. If it's ticked for teacher role, try doing a permission check for a user and see what it says for that capability.


That error can be fixed by changing "mdl_user" to "{user}"

Average of ratings: -
In reply to Conn Warwicker

Re: Automatic generation of Student number and HR student files

by Giulia Clifford -

Hi Connm thanks very much for that change in the script, now that report works for the administrator profile as we needed it. You've been a gem!


ETA: I have also resolved the permissions problem using your suggestions. thank you for all your help, you've all been great to me.


Giulia

Average of ratings: -
In reply to Conn Warwicker

Re: Automatic generation of Student number and HR student files

by Giulia Clifford -

Hi Conn,


I was wondering if you could help me again. I changed the permissions of the teacher and non editing teacher to show them the reports for the students in their class with their students number, but the only way it allows me to see the reports as the teacher is by allowing the teacher to manage the reports as well, so they get access to the SQL string and to all of the information about the module. I tried to tick only the "view report" permission but it doesn't show the report at all.


is there anything else I can do to allow the teachers to see the students ID numbers in their class?


Thanks for all your help!

Giulia

Average of ratings: -
In reply to Giulia Clifford

Re: Automatic generation of Student number and HR student files

by Conn Warwicker -
Picture of Core developers Picture of Plugin developers

Not sure, I've never really used the COnfigurable Reports plugin myself.

Might be better to ask in its own forum section: https://moodle.org/mod/forum/view.php?id=7979

As people there might have a better idea about how to get the permissions to work how you want.

Average of ratings: -