Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-77544

Include start and end dates with External database course creation

    • Icon: Improvement Improvement
    • Resolution: Unresolved
    • Icon: Minor Minor
    • None
    • 4.1.1, Future Dev
    • Administration
    • MOODLE_401_STABLE
    • MDL-77544-master
    • Hide

      Assumptions

      Your DB sever is postgres.
      Your DB server host moodle-db
      Your DB name moodle
      Your DB user moodl_user
      Your DB user password password

      Set up external table inside your Moodle DB:

      Create table

      CREATE TABLE my_courses (
        fullname  VARCHAR(300),
        shortname VARCHAR(300),
        idnumber  VARCHAR(100),
        startdate date,
        enddate   date);

       

      Insert Data

      INSERT INTO my_courses (fullname, shortname, idnumber, startdate, enddate) 
      VALUES
          ('Course one', 'c1', '', '2025-Jan-08', '2026-Jan-08'),
          ('Course two', 'c2', '', NULL, NULL),
          ('Course three', 'c3', '', '2025-Jan-18', '2024-Jan-08'),
          ('Course four', 'c4', '', NULL, '2021-Jan-08'),
          ('Course five', 'c5', '', '2027-Jan-08', NULL),
          ('Course six', 'c6', '', NULL, '2027-Jan-08')
      ;

       

      Set up Enrolment plugin

      php admin/cli/cfg.php --component=enrol_database --name=dbtype --set=postgres
      php admin/cli/cfg.php --component=enrol_database --name=dbhost --set=moodle-db
      php admin/cli/cfg.php --component=enrol_database --name=dbuser --set=moodle_user
      php admin/cli/cfg.php --component=enrol_database --name=dbpass --set=password
      php admin/cli/cfg.php --component=enrol_database --name=dbname --set=moodle
      php admin/cli/cfg.php --component=enrol_database --name=newcoursetable --set=my_courses
      php admin/cli/cfg.php --component=enrol_database --name=newcoursestartdate --set=startdate
      php admin/cli/cfg.php --component=enrol_database --name=newcourseenddate --set=enddate
      php admin/cli/cfg.php --component=enrol_database --name=newcoursestartdate --set=startdate

      Enable plugin

      Navigate to /admin/settings.php?section=manageenrols and enable External database

      Navigate to /enrol/test_settings.php?enrol=database and make sure that your configuration is correct. 

      Testing

      1. Execute following CLI

      php admin/cli/scheduled_task.php --execute="\enrol_database\task\sync_enrolments"

      2. Confirm that you see following output:

      Starting course synchronisation...
        can not insert new course, the end date must be after the start date: c3
        can not insert new course, the end date must be after the start date: c4
        creating course: 14, Course five, c5, , 1
        creating course: 15, Course two, c2, , 1
        creating course: 16, Course one, c1, , 1
        creating course: 17, Course six, c6, , 1
       
      

      4. Navigate to /course/management.php and confirm you can see Course one, Course two, Course five and Course six
      5. Check configuration of Course one and confirm start date = 2025-Jan-08 & end date = 2026-Jan-08
      6. Check configuration of Course two and confirm start date = today date & end date = today date + 1 year
      7. Check configuration of Course five and confirm start date = 2027-Jan-08 & end date = 2027-Jan-08 + 1 year
      8. Check configuration of Course six and confirm start date = today date & end date = 2027-Jan-08

      Show
      Assumptions Your DB sever is postgres. Your DB server host moodle-db Your DB name moodle Your DB user moodl_user Your DB user password password Set up external table inside your Moodle DB: Create table CREATE TABLE my_courses (   fullname  VARCHAR( 300 ),   shortname VARCHAR( 300 ),   idnumber  VARCHAR( 100 ),   startdate date,   enddate   date);   Insert Data INSERT INTO my_courses (fullname, shortname, idnumber, startdate, enddate)  VALUES     ( 'Course one' , 'c1' , '' , '2025-Jan-08' , '2026-Jan-08' ),     ( 'Course two' , 'c2' , '' , NULL, NULL),     ( 'Course three' , 'c3' , '' , '2025-Jan-18' , '2024-Jan-08' ),     ( 'Course four' , 'c4' , '' , NULL, '2021-Jan-08' ),     ( 'Course five' , 'c5' , '' , '2027-Jan-08' , NULL),     ( 'Course six' , 'c6' , '' , NULL, '2027-Jan-08' ) ;   Set up Enrolment plugin php admin/cli/cfg.php --component=enrol_database --name=dbtype --set=postgres php admin/cli/cfg.php --component=enrol_database --name=dbhost --set=moodle-db php admin/cli/cfg.php --component=enrol_database --name=dbuser --set=moodle_user php admin/cli/cfg.php --component=enrol_database --name=dbpass --set=password php admin/cli/cfg.php --component=enrol_database --name=dbname --set=moodle php admin/cli/cfg.php --component=enrol_database --name=newcoursetable --set=my_courses php admin/cli/cfg.php --component=enrol_database --name=newcoursestartdate --set=startdate php admin/cli/cfg.php --component=enrol_database --name=newcourseenddate --set=enddate php admin/cli/cfg.php --component=enrol_database --name=newcoursestartdate --set=startdate Enable plugin Navigate to /admin/settings.php?section=manageenrols and enable External database Navigate to /enrol/test_settings.php?enrol=database and make sure that your configuration is correct.  Testing 1. Execute following CLI php admin/cli/scheduled_task.php --execute= "\enrol_database\task\sync_enrolments" 2. Confirm that you see following output: Starting course synchronisation...   can not insert new course, the end date must be after the start date: c3   can not insert new course, the end date must be after the start date: c4   creating course: 14 , Course five, c5, , 1   creating course: 15 , Course two, c2, , 1   creating course: 16 , Course one, c1, , 1   creating course: 17 , Course six, c6, , 1   4. Navigate to /course/management.php and confirm you can see Course one, Course two, Course five and Course six 5. Check configuration of Course one and confirm start date = 2025-Jan-08 & end date = 2026-Jan-08 6. Check configuration of Course two and confirm start date = today date & end date = today date + 1 year 7. Check configuration of Course five and confirm start date = 2027-Jan-08 & end date = 2027-Jan-08 + 1 year 8. Check configuration of Course six and confirm start date = today date & end date = 2027-Jan-08

      When creating courses from an external database, the only fields used are fullname, shortname, courseid, and categoryid. No start and end dates are used, even when included in the the file. The start date of all the new course is the date of processing and there is no end date.

      It would be great if start and end date for new courses could be set based on the external database record at the same time. 

       

       

            dmitriim Dmitrii Metelkin
            bfriesen B. Friesen
            Votes:
            4 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.