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(