def get_comment_value_by_assignment_id(marked_grade_cell: Cell,
marked_comment_cells: Cell, row_number,
ws: worksheet):
"""
Helper method to find the corresponding comment value for a given grade_cell.
Column index marked by assignment ID in upload_marker row. Row indexing by given row_number.
The list of cells (marked_comment_cells) is cleaned before and do not contain the
CommentMarkerPrefix anymore, but assignment IDs.
:param marked_grade_cell: Grade cell marked by assignment ID in the upload_marker row.
:param marked_comment_cells: List of all comment cells we want to find the assignment ID of marked_grade_cell in.
:param row_number: Number of current row.
:param ws: Worksheet to read from (data_only).
:return: Empty string if cell value is None or if cell is not found.
"""
if Config.get_behavior_comments_switch():
for coment_cell in marked_comment_cells:
# find matching assignment id
if str(coment_cell.value) == str(marked_grade_cell.value):
# extract coordinates (column index)
column_index_of_comment_cell = column_index_from_string(
coordinate_from_string(coment_cell.coordinate)[0])
# read value of cell
comment_value = ws.cell(
row=row_number, column=column_index_of_comment_cell).value
if not comment_value: # normalize - we want string only as return type
comment_value = ''
return comment_value
if Config.get_behavior_log():
print('Comment for grade cell ' + str(marked_grade_cell.coordinate) +
' not found!')
return ''
def enrol_student(username):
payload = {
'enrolments[0][roleid]': Config.get_enrolment_role_id_student(),
'enrolments[0][userid]': str(username),
'enrolments[0][courseid]': Config.get_current_course_id()
}
return do_post_on_webservice('enrol_manual_enrol_users', payload)
def do_send_mail(mime_message):
username = Config.get_email_user_name()
password = Config.get_email_password()
server = smtplib.SMTP(Config.get_email_server_address())
server.ehlo()
server.starttls()
server.login(username, password)
server.sendmail(mime_message['From'], mime_message['To'],
mime_message.as_string())
server.quit()
def open_moodle_in_webbrowser(parameter_string, open_in_new_tab=2):
"""
Opens the moodle website as defined in config.ini in [BAHAVIOR] according to operating system
with default webbrowser.
:param parameter_string: additional url parameters, concat with moodle domain address as configured in config.ini.
:param open_in_new_tab: 2 is default (defined by package webbrowser)
:return: None
"""
if Config.get_behavior_moodle():
# open_in_new_tab = 2 means: open in a new tab, if possible
url = Config.get_moodle_domain() + parameter_string
webbrowser.open(url, new=open_in_new_tab)
def send_email_with_error_messages_to_ovid_admin(send_commands_to_ovid_result, repair_result, action_text = None):
if str(repair_result['exit_status']) != '0':
email_subject = 'OVID: Fehler bei Gruppenwechsel für ' + Config.get_course_creation_course_name()
email_text = ''
if action_text:
email_text = action_text + '\n\n'
email_text += 'Folgende Fehler traten beim durchführen des Gruppenwechsels auf:\n' \
+ 'Exit Status: ' + str(send_commands_to_ovid_result['exit_status']) + '\n' \
+ 'Errors: ' + str(send_commands_to_ovid_result['errors']) + '\n' \
+ 'Commands: ' + send_commands_to_ovid_result['commands'] + '\n\n' \
+ 'Folgende Reparaturen wurden vorgenommen:\n' \
+ 'Repair-Status: ' + str(repair_result['exit_status']) + '\n' \
+ 'Repair-Errors: ' + str(repair_result['errors']) + '\n' \
+ 'Repaid-Commands: ' + repair_result['commands'] + '\n'
Emails.sendMailWithTextBody(Config.get_ovid_administrator_email_address(), email_subject, email_text)
def enrol_students(usernames):
student_role_id = Config.get_enrolment_role_id_student(),
course_id = Config.get_current_course_id()
entity_counter = 0
payload = {}
for username in usernames:
payload.update({
'enrolments[' + str(entity_counter) + '][roleid]':
student_role_id,
'enrolments[' + str(entity_counter) + '][userid]':
str(username),
'enrolments[' + str(entity_counter) + '][courseid]':
course_id
})
entity_counter += 1
return do_post_on_webservice('enrol_manual_enrol_users', payload)
def sendMailWithTextBody(to, subject, text):
msg = MIMEMultipart()
msg['From'] = Config.get_email_user_name()
msg['To'] = str(to)
msg['Subject'] = subject
msg.attach(MIMEText(text))
do_send_mail(msg)
def wait_for_user_input_to_keep_console_opened():
"""
As configured in config.ini in [BAHAVIOR] the user will be prompted to press any key to finish the script and exit the console.
To be used f.e. when logging to console.
:return: None
"""
if Config.get_behavior_user_prompt():
input(PRESS_ANY_KEY_MESSAGE)
def sendMailWithBodyFromFile(to, subject, filename):
msg = MIMEMultipart()
msg['From'] = Config.get_email_user_name()
msg['To'] = str(to)
msg['Subject'] = subject
with open(filename, mode='r') as file:
msg.attach(MIMEText(file.read()))
do_send_mail(msg)
def do_post_on_webservice(ws_function, payload):
"""
Wrapper function to shorten the call on moodle webservice api.
Also we want to do logging and exception handling here.
The security token for this client is put into request body
to prevent sniffing on client- / server side.
:param ws_function: Webservice function name - defined in Moodle Webservices API
:param payload: Request Body content - defined in Moodle Webservices API
:return: Response of moodle server (not RESTful) as JSON.
"""
# TODO: logging?
# TODO: exception / error handling
# concat moodle url from config.ini
moodle_ws_address = Config.get_moodle_domain(
) + Config.get_moodle_webservice_address()
# add security token into POST body to hide it
payload.update({'wstoken': Config.get_moodle_token()})
# necessary url parameter as defined in Moodle Webservices API
params = {
# the webservice function
'wsfunction': str(ws_function),
# REST service - JSON as answer
'moodlewsrestformat': 'json'
}
r = requests.post(moodle_ws_address, params=params, data=payload)
if Config.get_behavior_log():
print('********************* ' + ws_function +
' *********************')
print('### PAYLOAD ###')
util.print_json_pretty(payload)
# check for errors in response and ask user to continue
check_for_errors_in_response(r, ws_function, payload)
result_as_json = json.loads(r.text)
return result_as_json
def send_commands_to_ovid(commands):
hostname = Config.get_ovid_ssh_host_name()
username = Config.get_ovid_ssh_user_name()
password = Config.get_ovid_ssh_password()
client = pm.SSHClient()
client.set_missing_host_key_policy(
pm.AutoAddPolicy())
client.connect(hostname, username=username, password=password)
if isinstance(commands, list):
prepared_commands = '; '.join(commands)
else:
prepared_commands = commands
if Config.get_behavior_log():
print('*********** COMMANDS TO OVID ***********')
if isinstance(commands, list):
for command in commands:
print(command)
else:
print(commands)
print('****************************')
# util.ask_user_to_continue('Diese Befehle werden an OVID gesendet.\nMöchten Sie fortfahren? (ja/j = ja, ANY = nein)')
stdin, stdout, stderr = client.exec_command(prepared_commands)
exit_status = str(stdout.channel.recv_exit_status())
ssh_stdout = stdout.readlines()
ssh_stderr = stderr.readlines()
client.close()
if str(exit_status) != '0':
print('****** ERROR IN SSH TO OVID ******')
print('SSH Exit Status:')
print(str(exit_status))
print('SSH Output:')
print(ssh_stdout)
print('SSH Error Output:')
print(ssh_stderr)
print('')
return {'exit_status': exit_status, 'errors': ssh_stderr, 'commands': prepared_commands}
def check_for_values_in_row(row_number, ws: worksheet):
"""
Checks for any occurrences of value(s) in given row in given worksheet as defined in
[GRADING_SHEET_COLUMN_MAPPING][IgnoreGradingOnEntryOfValue]
:param row_number: Number of row to check.
:param ws: Worksheet(data_only mode!) to check row from.
:return: True if value(s) are in row - otherwise False.
"""
values = Config.get_column_mapping_ignore_grading_on_entry()
for i in range(1, ws.max_column + 1):
if any(val in str(ws.cell(row=row_number, column=i).value) for val in values):
return True
return False
def open_workbook(absolut_path):
"""
Opens the grading workbook as defined in config.ini in [BAHAVIOR] according to operating system
with default program for viewing *.xlsx-files. Should support Windows, MacOS and Linux.
:return: None
"""
if Config.get_behavior_excel():
if sys.platform.startswith('darwin'): # Mac OS
subprocess.call(('open', absolut_path))
elif os.name == 'nt': # Windows
os.startfile(absolut_path)
elif os.name == 'posix': # Linux
subprocess.call(('xdg-open', absolut_path))
def write_pw_from_old_group_to_new_group_and_send_email_to_student(matrnr, old_group_name, new_group_name):
ssh_password = Config.get_ovid_ssh_password()
git_dir_path = Config.get_ovid_git_dir_path()
old_group_dir = 'g' + old_group_name
new_group_dir = 'g' + new_group_name
old_group_pw_file = old_group_dir + '/.passwd'
new_group_pw_file = new_group_dir + '/.passwd'
student = 's' + str(matrnr)
command_list = [
'cd ' + git_dir_path,
'echo ' + ssh_password + ' | sudo -S chmod 666 ' + old_group_pw_file,
'sudo chmod 666 ' + new_group_pw_file,
'sudo grep ' + student + ' ' + old_group_pw_file + ' >> ' + new_group_pw_file,
'sudo chmod 755 ' + old_group_pw_file,
'sudo chmod 755 ' + new_group_pw_file,
'sudo chmod 666 ' + old_group_pw_file,
"sudo sed -i '/" + student + "/d' " + old_group_pw_file,
'sudo chmod 755 ' + old_group_pw_file,
]
result = send_commands_to_ovid(command_list)
if str(result['exit_status']) == '0':
email_subject = 'GDI: Zugangsdaten für Ihr Repository in Eclipse'
student_email_address = str(matrnr) + Config.get_email_default_email_domain_students()
email_text = \
'Die URI Ihres Repositorys: http://' + Config.get_ovid_ssh_host_name() + '/git/' + new_group_dir + '/' + new_group_dir + '.git\n' \
+ 'Ihr Account-Name: ' + student + '\n' \
+ 'Ihr Passwort: Ist gleich geblieben!\n'
Emails.sendMailWithTextBody(student_email_address, email_subject, email_text)
if Config.get_behavior_send_ovid_information_email():
Emails.sendMailWithTextBody(Config.get_ovid_administrator_email_address(), 'REMINDER: ' + email_subject,
'Student ' + str(
matrnr) + ' wechselt von Gruppe ' + old_group_name + ' (' + git_dir_path + old_group_pw_file + ') zur Gruppe ' + new_group_name + ' (' + git_dir_path + new_group_pw_file + ').\n\n' + email_text)
else:
command_list_repair = [
'cd ' + git_dir_path,
'echo ' + ssh_password + ' | sudo -S chmod 755 ' + old_group_pw_file,
'sudo chmod 755 ' + new_group_pw_file
]
repair_result = send_commands_to_ovid(command_list_repair)
if Config.get_behavior_send_ovid_error_email():
send_email_with_error_messages_to_ovid_admin(result, repair_result, 'Student ' + str(
matrnr) + ' wechselt von Gruppe ' + old_group_name + ' (' + git_dir_path + old_group_pw_file + ') zur Gruppe ' + new_group_name + ' (' + git_dir_path + new_group_pw_file + ').')
def sendMailWithSingleZipFile(to, subject, text, filename):
msg = MIMEMultipart()
msg['From'] = Config.get_email_user_name()
msg['To'] = str(to)
msg['Subject'] = subject
text += '\nDatei in .zip umbenennen und einfach auspacken!\n\n'
msg.attach(MIMEText(text))
zipMsg = MIMEBase('application', 'zip')
with open(filename, mode='r') as file:
zipMsg.set_payload(file.read())
encoders.encode_base64(zipMsg)
zipMsg.add_header('Content-Disposition',
'attachment',
filename=filename + "x")
msg.attach(zipMsg)
do_send_mail(msg)
def check_for_errors_in_response(response, ws_function, payload):
error_list = ('error', 'exception')
error_occurred = False
if response:
if response.text:
response_text_as_json = json.loads(response.text)
if response_text_as_json:
if any(entity in response_text_as_json
for entity in error_list):
error_occurred = True
elif isinstance(response_text_as_json, list):
for list_item in response_text_as_json:
if list_item.get('warnings'):
error_occurred = True
break
elif bool(response_text_as_json.get('warnings')):
error_occurred = True
if error_occurred:
print('********************* ERROR *********************')
print('### WS_FUNCTION ###')
print(str(ws_function))
print('### PAYLOAD ###')
util.print_json_pretty(payload)
print('### STATUS CODE ###')
print(response)
print('### RESPONSE HEADERS ###')
print(response.headers)
print('### RESPONSE BODY ###')
if json.loads(response.text):
util.print_json_pretty(json.loads(response.text))
util.ask_user_to_continue(
"Moodle meldet Fehler. Fortfahren? (ja/nein)", ('j', 'ja'))
elif Config.get_behavior_log():
print('### STATUS CODE ###')
print(response)
print('### RESPONSE BODY ###')
if json.loads(response.text):
util.print_json_pretty(json.loads(response.text))
def write_new_pw_to_group_and_send_email_to_student(matrnr, new_group_name):
new_password = random.choice('abcdefghij') + str(random.randrange(0, 1000001))
new_group_dir = 'g' + new_group_name
new_group_pw_file = new_group_dir + '/.passwd'
student = 's' + str(matrnr)
ssh_password = Config.get_ovid_ssh_password()
git_dir_path = Config.get_ovid_git_dir_path()
command_list = [
'cd ' + git_dir_path,
'echo ' + ssh_password + ' | sudo -S chmod 666 ' + new_group_pw_file,
'sudo htpasswd -b ' + new_group_pw_file + ' ' + student + ' ' + new_password,
'sudo chmod 755 ' + new_group_pw_file
]
result = send_commands_to_ovid(command_list)
if str(result['exit_status']) == '0':
email_subject = 'GDI: Zugangsdaten für Ihr Repository in Eclipse'
student_email_address = str(matrnr) + Config.get_email_default_email_domain_students()
email_text = \
'Die URI Ihres Repositorys: http://' + Config.get_ovid_ssh_host_name() + '/git/' + new_group_dir + '/' + new_group_dir + '.git\n' \
+ 'Ihr Account-Name: ' + student + '\n' \
+ 'Ihr Passwort: ' + new_password + '\n'
Emails.sendMailWithTextBody(student_email_address, email_subject, email_text)
if Config.get_behavior_send_ovid_information_email():
Emails.sendMailWithTextBody(Config.get_ovid_administrator_email_address(), 'REMINDER: ' + email_subject,
'Student ' + str(
matrnr) + ' wurde in Gruppe ' + new_group_name + ' (' + git_dir_path + new_group_pw_file + ') eingetragen.\n\n' + email_text)
else:
command_list_repair = [
'cd ' + git_dir_path,
'echo ' + ssh_password + ' | sudo -S chmod 755 ' + new_group_pw_file
]
repair_result = send_commands_to_ovid(command_list_repair)
if Config.get_behavior_send_ovid_error_email():
send_email_with_error_messages_to_ovid_admin(result, repair_result, 'Student ' + str(
matrnr) + ' wurde in Gruppe ' + new_group_name + ' (' + git_dir_path + new_group_pw_file + ') eingetragen.')
sys.path.append(
os.path.join(os.path.dirname(os.path.realpath(__file__)), '..'))
sys.path.append(
os.path.join(
os.path.dirname(os.path.dirname(os.path.realpath(__file__))),
'..'))
from PythonScripts.Imports import WebService as ws
from PythonScripts.Imports import Utility as util
from PythonScripts.Imports import Config
"""
Self enrolment into moodle course. This is necessary to perform any grading or user action in that course as 'Manager'.
"""
# TODO: get enrolment methods to extract instanceids?
ws.do_post_on_webservice(
'enrol_self_enrol_user',
{
'courseid': Config.get_current_course_id(),
'instanceid': Config.get_enrolment_method_id(
), # use default instance (self enrolment?) # TODO: HSMA?
'password': Config.get_enrolment_password()
}) # if set in moodle, this might be needed
# open moodle page - enrolled users view to delete student role from PyWsClient - should be Manager only (to hide it from other Students f.e.)
util.open_moodle_in_webbrowser('enrol/users.php?id=' +
Config.get_current_course_id())
# wait for user prompt
util.wait_for_user_input_to_keep_console_opened()
def check_for_student_role(id):
return str(id) == str(Config.get_enrolment_role_id_student())
A B C D ...
1 1-1 12345 123916 12313
2 1-2 13140 13157
...
"""
import sys, os
from openpyxl import Workbook
from PythonScripts.Imports import Config
from PythonScripts.Imports import Utility as util
from PythonScripts.Imports import WebService as ws
from PythonScripts.Imports.Classes import Group
# ask user if ['OUTPUT_FILE']['OutputFileNameStudent2Group'] shall be overwritten
util.ask_user_to_overwrite_file(Config.get_output_student2group())
# create a workbook in memory
wb_out = Workbook()
ws_out = wb_out.active
# get all groups of current moodle course
result_get_groups = ws.do_post_on_webservice(
'core_group_get_course_groups',
{'courseid': Config.get_current_course_id()})
# create list of Group for simpler handling
groups = []
for group in result_get_groups:
groups.append(Group.Group(group['id'], group['name'], []))
'..'))
from PythonScripts.Imports import WebService as ws
from PythonScripts.Imports import Utility as util
from PythonScripts.Imports import Config
"""
Duplicates a moodle course into a new course as defined in ['COURSE_CREATION'] section in config.ini.
Saves the freshly created course id in config.ini in section ['CURRENT_COURSE']['CurrentCourseID'].
So the freshly created course will be the course we are working on from now on. The CurrentCourseID can be changed
manually to work on another course - f.e. for testing another moodle system...
"""
# duplicate template course
payload = {
'courseid': Config.get_course_creation_template_course_id(),
'fullname': Config.get_course_creation_course_name(),
'shortname': Config.get_course_creation_course_short_name(),
'categoryid': Config.get_course_creation_course_category_id(
), # 'topics' TODO: moodle HSMA?
'visible': Config.get_course_creation_visibility()
}
result_duplicate_course = ws.do_post_on_webservice(
'core_course_duplicate_course', payload)
# save cloned course id; its used in almost each script >IMPORTANT<
new_course_id = result_duplicate_course['id']
Config.replace_current_course_id_in_config_file(new_course_id)
Config.__init__(Config.loaded_config_file_path)
def check_username_is_client_name(username):
return str(username) == str(Config.get_py_client_name())
# read value of cell
comment_value = ws.cell(
row=row_number, column=column_index_of_comment_cell).value
if not comment_value: # normalize - we want string only as return type
comment_value = ''
return comment_value
if Config.get_behavior_log():
print('Comment for grade cell ' + str(marked_grade_cell.coordinate) +
' not found!')
return ''
util.ask_user_to_continue(
'Wollen Sie wirklich Moodle mit ihrer Tabelle synchronisieren?\n' +
'Tabelle: ' + Config.get_grading_sheet_file_path() + '\n' +
'Fortfahren? (ja/j)')
# Open workbook in data_only mode to read values only and no formulas.
# Workbook in data_only mode will return calculated values for formulas: value == internal_value
wb_grading_data = load_workbook(Config.get_grading_sheet_file_path(),
data_only=True)
ws_grading_data = wb_grading_data.active
# find upload_marker row
moodle_grade_column_marker_name = Config.get_column_mapping_upload_marker_name(
)
index_moodle_grade_marker_row = util.find_row_of_value_in_column(
moodle_grade_column_marker_name,
Config.get_column_mapping_upload_marker_column(), ws_grading_data)
#!/usr/bin/env python
if __name__ == '__main__':
import os, sys
sys.path.append(os.path.join(os.path.dirname(os.path.realpath(__file__)), '..'))
sys.path.append(os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__))), '..'))
from PythonScripts.Imports import WebService as ws
from PythonScripts.Imports import Utility as util
from PythonScripts.Imports import Config
"""
Deletes groups from freshly cloned course.
Due to avoid null-errors in moodle the template course has a few (placeholder) groups for the groupchoice module.
Or if we reuse an old course several groups may be still in it.
We want to delete those groups before creating new ones.
"""
# get all groups of course
result_get_course_groups = ws.do_post_on_webservice('core_group_get_course_groups', {'courseid': Config.get_current_course_id()})
payload = {}
for n in range(0, len(result_get_course_groups)):
#if ('Platzhalter' in result_get_course_groups[n]['name']):
payload.update({'groupids[' + str(n) + ']': result_get_course_groups[n]['id']})
ws.do_post_on_webservice('core_group_delete_groups', payload)
#wait for user prompt
util.wait_for_user_input_to_keep_console_opened()
"""
Creates Workbook containing user, grade, group and assignment information of the moodle course as defined in
config.ini in section [CURRENT_COURSE]. Also the Upload_Marker row is written, to make the resulting workbook
suitable for synchronization with the moodle course (by SyncGradingSheetAndMoodle.py).
The resulting Workbook will contain four worksheets:
- 'grades' : Contains an uploadable or copy&pastable sheet to upload grades manually into moodle. Contains essential data
of students and columns for each assignment to enable grading and feedback input.
- 'assignments' : Contains all gradable assignments (Übung) in moodle course. To upload grades programmatically we need
the assignment_IDs which are held in this sheet (name and ID).
- 'groups' : Contains all groups of current moodle course (just name and ID).
- 'not_in_group': Contains all
"""
# ask user if ['OUTPUT_FILE']['OutputFileNameGradings'] shall be overwritten
util.ask_user_to_overwrite_file(Config.get_output_grading_sheet_file_path())
# initialise Workbook and Worksheets
wb_out = Workbook()
ws_grades = wb_out.active # Workbook is created with empty sheet
ws_grades.title = 'grades' # rename that empty sheet
ws_assignments = wb_out.create_sheet('assignments')
ws_groups = wb_out.create_sheet('groups')
ws_not_in_group = wb_out.create_sheet('not_in_group')
course_id = Config.get_current_course_id()
moodle_uid = Config.get_moodle_user_id_field_name()
# ****************************************************************
# START OF API CALLS - START TO FILL WORKBOOK
# ****************************************************************
sys.path.append(
os.path.join(
os.path.dirname(os.path.dirname(os.path.realpath(__file__))),
'..'))
from PythonScripts.Imports import WebService as ws
from PythonScripts.Imports import Utility as util
from PythonScripts.Imports import Config
"""
Creates Groups in a moodle course. Groupname scheme TODO
To configure see config.ini section [GROUP].
"""
# initialise variables from config.ini
course_id = Config.get_current_course_id()
number_of_groups = Config.get_groups_number_of_main_groups()
number_of_sub_groups = Config.get_groups_number_of_sub_groups()
main_group_name = Config.get_groups_main_group_naming()
sub_group_name = Config.get_groups_sub_group_naming()
# create groups for course in moodle
payload = {}
group_counter = 0
for n in range(0, number_of_groups):
for i in range(0, number_of_sub_groups):
final_group_name = str(n + 1) + '-' + str(i + 1)
if not main_group_name or not sub_group_name:
group_description = ''
else:
group_description = main_group_name + ' ' + str(