Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 3.9.x will end* 10 May 2021 (12 months).
  • Bug fixes for security issues in 3.9.x will end* 8 May 2023 (36 months).
  • PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.

Differences Between: [Versions 39 and 400] [Versions 39 and 401] [Versions 39 and 402] [Versions 39 and 403]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * Data provider.
  19   *
  20   * @package    core_competency
  21   * @copyright  2018 Frédéric Massart
  22   * @author     Frédéric Massart <fred@branchup.tech>
  23   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  24   */
  25  
  26  namespace core_competency\privacy;
  27  defined('MOODLE_INTERNAL') || die();
  28  
  29  use context;
  30  use context_course;
  31  use context_helper;
  32  use context_module;
  33  use context_system;
  34  use context_user;
  35  use moodle_recordset;
  36  use core_competency\api;
  37  use core_competency\competency;
  38  use core_competency\competency_framework;
  39  use core_competency\course_competency;
  40  use core_competency\course_competency_settings;
  41  use core_competency\course_module_competency;
  42  use core_competency\evidence;
  43  use core_competency\plan;
  44  use core_competency\plan_competency;
  45  use core_competency\related_competency;
  46  use core_competency\template;
  47  use core_competency\template_cohort;
  48  use core_competency\template_competency;
  49  use core_competency\user_competency;
  50  use core_competency\user_competency_course;
  51  use core_competency\user_competency_plan;
  52  use core_competency\user_evidence;
  53  use core_competency\user_evidence_competency;
  54  use core_competency\external\performance_helper;
  55  use core_privacy\local\metadata\collection;
  56  use core_privacy\local\request\approved_userlist;
  57  use core_privacy\local\request\contextlist;
  58  use core_privacy\local\request\approved_contextlist;
  59  use core_privacy\local\request\transform;
  60  use core_privacy\local\request\userlist;
  61  use core_privacy\local\request\writer;
  62  
  63  /**
  64   * Data provider class.
  65   *
  66   * @package    core_competency
  67   * @copyright  2018 Frédéric Massart
  68   * @author     Frédéric Massart <fred@branchup.tech>
  69   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  70   */
  71  class provider implements
  72      \core_privacy\local\metadata\provider,
  73      \core_privacy\local\request\core_userlist_provider,
  74      \core_privacy\local\request\subsystem\provider {
  75  
  76      /**
  77       * Returns metadata.
  78       *
  79       * @param collection $collection The initialised collection to add items to.
  80       * @return collection A listing of user data stored through this system.
  81       */
  82      public static function get_metadata(collection $collection) : collection {
  83  
  84          // Tables not related to users aside from the editing information.
  85          $collection->add_database_table('competency', [
  86              'timecreated' => 'privacy:metadata:timecreated',
  87              'timemodified' => 'privacy:metadata:timemodified',
  88              'usermodified' => 'privacy:metadata:usermodified',
  89          ], 'privacy:metadata:competency');
  90  
  91          $collection->add_database_table('competency_coursecompsetting', [
  92              'timecreated' => 'privacy:metadata:timecreated',
  93              'timemodified' => 'privacy:metadata:timemodified',
  94              'usermodified' => 'privacy:metadata:usermodified',
  95          ], 'privacy:metadata:competency_coursecompsetting');
  96  
  97          $collection->add_database_table('competency_framework', [
  98              'timecreated' => 'privacy:metadata:timecreated',
  99              'timemodified' => 'privacy:metadata:timemodified',
 100              'usermodified' => 'privacy:metadata:usermodified',
 101          ], 'privacy:metadata:competency_framework');
 102  
 103          $collection->add_database_table('competency_coursecomp', [
 104              'timecreated' => 'privacy:metadata:timecreated',
 105              'timemodified' => 'privacy:metadata:timemodified',
 106              'usermodified' => 'privacy:metadata:usermodified',
 107          ], 'privacy:metadata:competency_coursecomp');
 108  
 109          $collection->add_database_table('competency_template', [
 110              'timecreated' => 'privacy:metadata:timecreated',
 111              'timemodified' => 'privacy:metadata:timemodified',
 112              'usermodified' => 'privacy:metadata:usermodified',
 113          ], 'privacy:metadata:competency_template');
 114  
 115          $collection->add_database_table('competency_templatecomp', [
 116              'timecreated' => 'privacy:metadata:timecreated',
 117              'timemodified' => 'privacy:metadata:timemodified',
 118              'usermodified' => 'privacy:metadata:usermodified',
 119          ], 'privacy:metadata:competency_templatecomp');
 120  
 121          $collection->add_database_table('competency_templatecohort', [
 122              'timecreated' => 'privacy:metadata:timecreated',
 123              'timemodified' => 'privacy:metadata:timemodified',
 124              'usermodified' => 'privacy:metadata:usermodified',
 125          ], 'privacy:metadata:competency_templatecohort');
 126  
 127          $collection->add_database_table('competency_relatedcomp', [
 128              'timecreated' => 'privacy:metadata:timecreated',
 129              'timemodified' => 'privacy:metadata:timemodified',
 130              'usermodified' => 'privacy:metadata:usermodified',
 131          ], 'privacy:metadata:competency_relatedcomp');
 132  
 133          $collection->add_database_table('competency_modulecomp', [
 134              'timecreated' => 'privacy:metadata:timecreated',
 135              'timemodified' => 'privacy:metadata:timemodified',
 136              'usermodified' => 'privacy:metadata:usermodified',
 137          ], 'privacy:metadata:competency_modulecomp');
 138  
 139          // Tables containing user data.
 140          $collection->add_database_table('competency_plan', [
 141              'name' => 'privacy:metadata:plan:name',
 142              'description' => 'privacy:metadata:plan:description',
 143              'userid' => 'privacy:metadata:plan:userid',
 144              'status' => 'privacy:metadata:plan:status',
 145              'duedate' => 'privacy:metadata:plan:duedate',
 146              'reviewerid' => 'privacy:metadata:plan:reviewerid',
 147              'timecreated' => 'privacy:metadata:timecreated',
 148              'timemodified' => 'privacy:metadata:timemodified',
 149              'usermodified' => 'privacy:metadata:usermodified',
 150          ], 'privacy:metadata:competency_plan');
 151  
 152          $collection->add_database_table('competency_usercomp', [
 153              'userid' => 'privacy:metadata:usercomp:userid',
 154              'status' => 'privacy:metadata:usercomp:status',
 155              'reviewerid' => 'privacy:metadata:usercomp:reviewerid',
 156              'proficiency' => 'privacy:metadata:usercomp:proficiency',
 157              'grade' => 'privacy:metadata:usercomp:grade',
 158              'timecreated' => 'privacy:metadata:timecreated',
 159              'timemodified' => 'privacy:metadata:timemodified',
 160              'usermodified' => 'privacy:metadata:usermodified',
 161          ], 'privacy:metadata:competency_usercomp');
 162  
 163          $collection->add_database_table('competency_usercompcourse', [
 164              'userid' => 'privacy:metadata:usercomp:userid',
 165              'proficiency' => 'privacy:metadata:usercomp:proficiency',
 166              'grade' => 'privacy:metadata:usercomp:grade',
 167              'timecreated' => 'privacy:metadata:timecreated',
 168              'timemodified' => 'privacy:metadata:timemodified',
 169              'usermodified' => 'privacy:metadata:usermodified',
 170          ], 'privacy:metadata:competency_usercompcourse');
 171  
 172          $collection->add_database_table('competency_usercompplan', [
 173              'userid' => 'privacy:metadata:usercomp:userid',
 174              'proficiency' => 'privacy:metadata:usercomp:proficiency',
 175              'grade' => 'privacy:metadata:usercomp:grade',
 176              'timecreated' => 'privacy:metadata:timecreated',
 177              'timemodified' => 'privacy:metadata:timemodified',
 178              'usermodified' => 'privacy:metadata:usermodified',
 179          ], 'privacy:metadata:competency_usercompplan');
 180  
 181          $collection->add_database_table('competency_plancomp', [
 182              'timecreated' => 'privacy:metadata:timecreated',
 183              'timemodified' => 'privacy:metadata:timemodified',
 184              'usermodified' => 'privacy:metadata:usermodified',
 185          ], 'privacy:metadata:competency_plancomp');
 186  
 187          $collection->add_database_table('competency_evidence', [
 188              'action' => 'privacy:metadata:evidence:action',
 189              'actionuserid' => 'privacy:metadata:evidence:actionuserid',
 190              'descidentifier' => 'privacy:metadata:evidence:descidentifier',
 191              'desccomponent' => 'privacy:metadata:evidence:desccomponent',
 192              'desca' => 'privacy:metadata:evidence:desca',
 193              'url' => 'privacy:metadata:evidence:url',
 194              'grade' => 'privacy:metadata:evidence:grade',
 195              'note' => 'privacy:metadata:evidence:note',
 196              'timecreated' => 'privacy:metadata:timecreated',
 197              'timemodified' => 'privacy:metadata:timemodified',
 198              'usermodified' => 'privacy:metadata:usermodified',
 199          ], 'privacy:metadata:competency_evidence');
 200  
 201          $collection->add_database_table('competency_userevidence', [
 202              'name' => 'privacy:metadata:userevidence:name',
 203              'description' => 'privacy:metadata:userevidence:description',
 204              'url' => 'privacy:metadata:userevidence:url',
 205              'timecreated' => 'privacy:metadata:timecreated',
 206              'timemodified' => 'privacy:metadata:timemodified',
 207              'usermodified' => 'privacy:metadata:usermodified',
 208          ], 'privacy:metadata:competency_userevidence');
 209  
 210          $collection->add_database_table('competency_userevidencecomp', [
 211              'timecreated' => 'privacy:metadata:timecreated',
 212              'timemodified' => 'privacy:metadata:timemodified',
 213              'usermodified' => 'privacy:metadata:usermodified',
 214          ], 'privacy:metadata:competency_userevidencecomp');
 215  
 216          // Comments can be left on learning plans and competencies.
 217          $collection->link_subsystem('core_comment', 'privacy:metadata:core_comments');
 218  
 219          return $collection;
 220      }
 221  
 222      /**
 223       * Get the list of contexts that contain user information for the specified user.
 224       *
 225       * @param int $userid The user to search.
 226       * @return contextlist $contextlist The contextlist containing the list of contexts used in this plugin.
 227       */
 228      public static function get_contexts_for_userid(int $userid) : contextlist {
 229          global $DB;
 230          $contextlist = new \core_privacy\local\request\contextlist();
 231  
 232          // Find the contexts of the frameworks, and related data, modified by the user.
 233          $sql = "
 234               SELECT DISTINCT ctx.id
 235                 FROM {context} ctx
 236                 JOIN {" . competency_framework::TABLE . "} cf
 237                   ON cf.contextid = ctx.id
 238            LEFT JOIN {" . competency::TABLE . "} c
 239                   ON c.competencyframeworkid = cf.id
 240            LEFT JOIN {" . related_competency::TABLE . "} cr
 241                   ON cr.competencyid = c.id
 242                WHERE cf.usermodified = :userid1
 243                   OR c.usermodified = :userid2
 244                   OR cr.usermodified = :userid3";
 245          $params = ['userid1' => $userid, 'userid2' => $userid, 'userid3' => $userid];
 246          $contextlist->add_from_sql($sql, $params);
 247  
 248          // Find the contexts of the templates, and related data, modified by the user.
 249          $sql = "
 250               SELECT DISTINCT ctx.id
 251                 FROM {context} ctx
 252                 JOIN {" . template::TABLE . "} tpl
 253                   ON tpl.contextid = ctx.id
 254            LEFT JOIN {" . template_cohort::TABLE . "} tch
 255                   ON tch.templateid = tpl.id
 256                  AND tch.usermodified = :userid2
 257            LEFT JOIN {" . template_competency::TABLE . "} tc
 258                   ON tc.templateid = tpl.id
 259                  AND tc.usermodified = :userid3
 260                WHERE tpl.usermodified = :userid1
 261                   OR tch.id IS NOT NULL
 262                   OR tc.id IS NOT NULL";
 263          $params = ['userid1' => $userid, 'userid2' => $userid, 'userid3' => $userid];
 264          $contextlist->add_from_sql($sql, $params);
 265  
 266          // Find the possible course contexts.
 267          $sql = "
 268               SELECT DISTINCT ctx.id
 269                 FROM {" . course_competency::TABLE . "} cc
 270                 JOIN {context} ctx
 271                   ON ctx.instanceid = cc.courseid
 272                  AND ctx.contextlevel = :courselevel
 273                WHERE cc.usermodified = :userid";
 274          $params = ['courselevel' => CONTEXT_COURSE, 'userid' => $userid];
 275          $contextlist->add_from_sql($sql, $params);
 276  
 277          $sql = "
 278               SELECT DISTINCT ctx.id
 279                 FROM {" . course_competency_settings::TABLE . "} ccs
 280                 JOIN {context} ctx
 281                   ON ctx.instanceid = ccs.courseid
 282                  AND ctx.contextlevel = :courselevel
 283                WHERE ccs.usermodified = :userid";
 284          $params = ['courselevel' => CONTEXT_COURSE, 'userid' => $userid];
 285          $contextlist->add_from_sql($sql, $params);
 286  
 287          $sql = "
 288               SELECT DISTINCT ctx.id
 289                 FROM {" . user_competency_course::TABLE . "} ucc
 290                 JOIN {context} ctx
 291                   ON ctx.instanceid = ucc.courseid
 292                  AND ctx.contextlevel = :courselevel
 293                WHERE ucc.usermodified = :userid";
 294          $params = ['courselevel' => CONTEXT_COURSE, 'userid' => $userid];
 295          $contextlist->add_from_sql($sql, $params);
 296  
 297          // Find the possible module contexts.
 298          $sql = "
 299               SELECT DISTINCT ctx.id
 300                 FROM {" . course_module_competency::TABLE . "} cmc
 301                 JOIN {context} ctx
 302                   ON ctx.instanceid = cmc.cmid
 303                  AND ctx.contextlevel = :modulelevel
 304                WHERE cmc.usermodified = :userid";
 305          $params = ['modulelevel' => CONTEXT_MODULE, 'userid' => $userid];
 306          $contextlist->add_from_sql($sql, $params);
 307  
 308          // Add user contexts through usermodified/reviewing of plan related data.
 309          $sql = "
 310               SELECT DISTINCT ctx.id
 311                 FROM {" . plan::TABLE . "} p
 312                 JOIN {context} ctx
 313                   ON ctx.instanceid = p.userid
 314                  AND ctx.contextlevel = :userlevel
 315            LEFT JOIN {" . plan_competency::TABLE . "} pc
 316                   ON pc.planid = p.id
 317                  AND pc.usermodified = :userid3
 318            LEFT JOIN {" . user_competency_plan::TABLE . "} upc
 319                   ON upc.planid = p.id
 320                  AND upc.usermodified = :userid4
 321                WHERE p.usermodified = :userid1
 322                   OR p.reviewerid = :userid2
 323                   OR pc.id IS NOT NULL
 324                   OR upc.id IS NOT NULL";
 325          $params = [
 326              'userlevel' => CONTEXT_USER,
 327              'userid1' => $userid,
 328              'userid2' => $userid,
 329              'userid3' => $userid,
 330              'userid4' => $userid,
 331          ];
 332          $contextlist->add_from_sql($sql, $params);
 333  
 334          // Add user contexts through usermodified/reviewing of competency data.
 335          $sql = "
 336               SELECT DISTINCT ctx.id
 337                 FROM {context} ctx
 338            LEFT JOIN {" . user_competency::TABLE . "} uc
 339                   ON uc.userid = ctx.instanceid
 340                  AND ctx.contextlevel = :userlevel1
 341            LEFT JOIN {" . evidence::TABLE . "} e
 342                   ON e.usercompetencyid = uc.id
 343                  AND (e.usermodified = :userid3 OR e.actionuserid = :userid4)
 344            LEFT JOIN {" . user_evidence::TABLE . "} ue
 345                   ON ue.userid = ctx.instanceid
 346                  AND ctx.contextlevel = :userlevel2
 347                  AND ue.usermodified = :userid5
 348            LEFT JOIN {" . user_evidence_competency::TABLE . "} uec
 349                   ON uec.userevidenceid = ue.id
 350                  AND uec.usermodified = :userid6
 351                WHERE uc.usermodified = :userid1
 352                   OR uc.reviewerid = :userid2
 353                   OR e.id IS NOT NULL
 354                   OR ue.id IS NOT NULL
 355                   OR uec.id IS NOT NULL";
 356          $params = [
 357              'userlevel1' => CONTEXT_USER,
 358              'userlevel2' => CONTEXT_USER,
 359              'userid1' => $userid,
 360              'userid2' => $userid,
 361              'userid3' => $userid,
 362              'userid4' => $userid,
 363              'userid5' => $userid,
 364              'userid6' => $userid,
 365          ];
 366          $contextlist->add_from_sql($sql, $params);
 367  
 368          // Now, the easy part, we fetch the user context for user plans and competencies.
 369          // We also fetch the course context for the state of competencies for the user in courses.
 370          $sql = "
 371               SELECT DISTINCT ctx.id
 372                 FROM {context} ctx
 373            LEFT JOIN {" . plan::TABLE . "} p
 374                   ON p.userid = ctx.instanceid
 375                  AND ctx.contextlevel = :userlevel1
 376            LEFT JOIN {" . user_competency::TABLE . "} uc
 377                   ON uc.userid = ctx.instanceid
 378                  AND ctx.contextlevel = :userlevel2
 379                  AND uc.userid = :userid2
 380            LEFT JOIN {" . user_evidence::TABLE . "} ue
 381                   ON ue.userid = ctx.instanceid
 382                  AND ctx.contextlevel = :userlevel3
 383                  AND ue.userid = :userid3
 384            LEFT JOIN {" . user_competency_course::TABLE . "} ucc
 385                   ON ucc.courseid = ctx.instanceid
 386                  AND ctx.contextlevel = :courselevel
 387                  AND ucc.userid = :userid4
 388                WHERE p.userid = :userid1
 389                   OR uc.id IS NOT NULL
 390                   OR ue.id IS NOT NULL
 391                   OR ucc.id IS NOT NULL";
 392          $params = [
 393              'userlevel1' => CONTEXT_USER,
 394              'userlevel2' => CONTEXT_USER,
 395              'userlevel3' => CONTEXT_USER,
 396              'courselevel' => CONTEXT_COURSE,
 397              'userid1' => $userid,
 398              'userid2' => $userid,
 399              'userid3' => $userid,
 400              'userid4' => $userid,
 401          ];
 402          $contextlist->add_from_sql($sql, $params);
 403  
 404          // Include the user contexts in which the user commented.
 405          $sql = "
 406              SELECT ctx.id
 407                FROM {context} ctx
 408                JOIN {comments} c
 409                  ON c.contextid = ctx.id
 410               WHERE c.component = :component
 411                 AND c.commentarea IN (:planarea, :usercomparea)
 412                 AND c.userid = :userid";
 413          $params = [
 414              'component' => 'competency',    // Must not be core_competency.
 415              'planarea' => 'plan',
 416              'usercomparea' => 'user_competency',
 417              'userid' => $userid
 418          ];
 419          $contextlist->add_from_sql($sql, $params);
 420  
 421          return $contextlist;
 422      }
 423  
 424      /**
 425       * Get the list of users who have data within a context.
 426       *
 427       * @param   userlist    $userlist   The userlist containing the list of users who have data in this context/plugin combination.
 428       */
 429      public static function get_users_in_context(userlist $userlist) {
 430          $context = $userlist->get_context();
 431          $params = ['contextid' => $context->id];
 432  
 433          // Add users who have modified the frameworks and related data in the context.
 434          $sql = "
 435               SELECT cf.usermodified
 436                 FROM {" . competency_framework::TABLE . "} cf
 437                WHERE cf.contextid = :contextid";
 438          $userlist->add_from_sql('usermodified', $sql, $params);
 439  
 440          $sql = "
 441               SELECT c.usermodified
 442                 FROM {" . competency_framework::TABLE . "} cf
 443                 JOIN {" . competency::TABLE . "} c
 444                   ON c.competencyframeworkid = cf.id
 445                WHERE cf.contextid = :contextid";
 446          $userlist->add_from_sql('usermodified', $sql, $params);
 447  
 448          $sql = "
 449               SELECT cr.usermodified
 450                 FROM {" . competency_framework::TABLE . "} cf
 451                 JOIN {" . competency::TABLE . "} c
 452                   ON c.competencyframeworkid = cf.id
 453                 JOIN {" . related_competency::TABLE . "} cr
 454                   ON cr.competencyid = c.id
 455                WHERE cf.contextid = :contextid";
 456          $userlist->add_from_sql('usermodified', $sql, $params);
 457  
 458          // Add users who have modified the templates and related data in the context.
 459          $sql = "
 460               SELECT tpl.usermodified
 461                 FROM {" . template::TABLE . "} tpl
 462                WHERE tpl.contextid = :contextid";
 463          $userlist->add_from_sql('usermodified', $sql, $params);
 464  
 465          $sql = "
 466               SELECT tch.usermodified
 467                 FROM {" . template::TABLE . "} tpl
 468                 JOIN {" . template_cohort::TABLE . "} tch
 469                   ON tch.templateid = tpl.id
 470                WHERE tpl.contextid = :contextid";
 471          $userlist->add_from_sql('usermodified', $sql, $params);
 472  
 473          $sql = "
 474               SELECT tc.usermodified
 475                 FROM {" . template::TABLE . "} tpl
 476                 JOIN {" . template_competency::TABLE . "} tc
 477                   ON tc.templateid = tpl.id
 478                WHERE tpl.contextid = :contextid";
 479          $userlist->add_from_sql('usermodified', $sql, $params);
 480  
 481          // Add users if userlist is in course context.
 482          if (is_a($context, \context_course::class)) {
 483              $params = ['courseid' => $context->instanceid];
 484  
 485              $sql = "
 486                   SELECT cc.usermodified
 487                     FROM {" . course_competency::TABLE . "} cc
 488                    WHERE cc.courseid = :courseid";
 489              $userlist->add_from_sql('usermodified', $sql, $params);
 490  
 491              $sql = "
 492                   SELECT ccs.usermodified
 493                     FROM {" . course_competency_settings::TABLE . "} ccs
 494                    WHERE ccs.courseid = :courseid";
 495              $userlist->add_from_sql('usermodified', $sql, $params);
 496  
 497              $sql = "
 498                   SELECT ucc.userid, ucc.usermodified
 499                     FROM {" . user_competency_course::TABLE . "} ucc
 500                    WHERE ucc.courseid = :courseid";
 501              $userlist->add_from_sql('userid', $sql, $params);
 502              $userlist->add_from_sql('usermodified', $sql, $params);
 503  
 504          } else if (is_a($context, \context_module::class)) {
 505              // Add users if userlist is in module context.
 506              $params = ['moduleid' => $context->instanceid];
 507  
 508              $sql = "
 509                   SELECT cmc.usermodified
 510                     FROM {" . course_module_competency::TABLE . "} cmc
 511                    WHERE cmc.cmid = :moduleid";
 512              $userlist->add_from_sql('usermodified', $sql, $params);
 513  
 514          } else if (is_a($context, \context_user::class)) {
 515              $params = ['userid' => $context->instanceid];
 516  
 517              // Add users through plan related data.
 518              $sql = "
 519                   SELECT p.userid, p.usermodified, p.reviewerid
 520                     FROM {" . plan::TABLE . "} p
 521                    WHERE p.userid = :userid";
 522              $userlist->add_from_sql('userid', $sql, $params);
 523              $userlist->add_from_sql('usermodified', $sql, $params);
 524              $userlist->add_from_sql('reviewerid', $sql, $params);
 525  
 526              $sql = "
 527                   SELECT pc.usermodified
 528                     FROM {" . plan::TABLE . "} p
 529                     JOIN {" . plan_competency::TABLE . "} pc
 530                       ON pc.planid = p.id
 531                    WHERE p.userid = :userid";
 532              $userlist->add_from_sql('usermodified', $sql, $params);
 533  
 534              $sql = "
 535                   SELECT upc.usermodified
 536                     FROM {" . user_competency_plan::TABLE . "} upc
 537                    WHERE upc.userid = :userid";
 538              $userlist->add_from_sql('usermodified', $sql, $params);
 539  
 540              // Add users through competency data.
 541              $sql = "
 542                   SELECT uc.userid, uc.usermodified, uc.reviewerid
 543                     FROM {" . user_competency::TABLE . "} uc
 544                    WHERE uc.userid = :userid";
 545              $userlist->add_from_sql('userid', $sql, $params);
 546              $userlist->add_from_sql('usermodified', $sql, $params);
 547              $userlist->add_from_sql('reviewerid', $sql, $params);
 548  
 549              $sql = "
 550                   SELECT e.usermodified, e.actionuserid
 551                     FROM {" . user_competency::TABLE . "} uc
 552                     JOIN {" . evidence::TABLE . "} e
 553                       ON e.usercompetencyid = uc.id
 554                    WHERE uc.userid = :userid";
 555              $userlist->add_from_sql('usermodified', $sql, $params);
 556              $userlist->add_from_sql('actionuserid', $sql, $params);
 557  
 558              // Add users through evidence data.
 559              $sql = "
 560                   SELECT ue.userid, ue.usermodified
 561                     FROM {" . user_evidence::TABLE . "} ue
 562                    WHERE ue.userid = :userid";
 563              $userlist->add_from_sql('userid', $sql, $params);
 564              $userlist->add_from_sql('usermodified', $sql, $params);
 565  
 566              $sql = "
 567                   SELECT ue.usermodified
 568                     FROM {" . user_evidence::TABLE . "} ue
 569                     JOIN {" . user_evidence_competency::TABLE . "} uec
 570                       ON uec.userevidenceid = ue.id
 571                    WHERE ue.userid = :userid";
 572              $userlist->add_from_sql('usermodified', $sql, $params);
 573          }
 574  
 575          // Add users who commented in the context.
 576          // Note: Comment component must be competency and not core_competency.
 577          \core_comment\privacy\provider::get_users_in_context_from_sql(
 578                  $userlist, 'com', 'competency', 'plan', $context->id);
 579  
 580          \core_comment\privacy\provider::get_users_in_context_from_sql(
 581                  $userlist, 'com', 'competency', 'user_competency', $context->id);
 582      }
 583  
 584      /**
 585       * Export all user data for the specified user, in the specified contexts.
 586       *
 587       * @param approved_contextlist $contextlist The approved contexts to export information for.
 588       */
 589      public static function export_user_data(approved_contextlist $contextlist) {
 590          $user = $contextlist->get_user();
 591          $userid = $user->id;
 592  
 593          // Re-arrange the contexts by context level.
 594          $groupedcontexts = array_reduce($contextlist->get_contexts(), function($carry, $context) {
 595              $contextlevel = $context->contextlevel;
 596              if (!in_array($contextlevel, [CONTEXT_USER, CONTEXT_COURSE, CONTEXT_MODULE, CONTEXT_SYSTEM, CONTEXT_COURSECAT])) {
 597                  return $carry;
 598              }
 599              $carry[$contextlevel][] = $context;
 600              return $carry;
 601          }, [
 602              CONTEXT_COURSE => [],
 603              CONTEXT_COURSECAT => [],
 604              CONTEXT_MODULE => [],
 605              CONTEXT_SYSTEM => [],
 606              CONTEXT_USER => [],
 607          ]);
 608  
 609          // Process module contexts.
 610          static::export_user_data_in_module_contexts($userid, $groupedcontexts[CONTEXT_MODULE]);
 611  
 612          // Process course contexts.
 613          static::export_user_data_in_course_contexts($userid, $groupedcontexts[CONTEXT_COURSE]);
 614  
 615          // Process course categories context.
 616          static::export_user_data_in_category_contexts($userid, $groupedcontexts[CONTEXT_COURSECAT]);
 617  
 618          // Process system context.
 619          if (!empty($groupedcontexts[CONTEXT_SYSTEM])) {
 620              static::export_user_data_in_system_context($userid);
 621          }
 622  
 623          // Process user contexts.
 624          static::export_user_data_in_user_contexts($userid, $groupedcontexts[CONTEXT_USER]);
 625      }
 626  
 627      /**
 628       * Delete all data for all users in the specified context.
 629       *
 630       * @param context $context The specific context to delete data for.
 631       */
 632      public static function delete_data_for_all_users_in_context(context $context) {
 633          global $DB;
 634  
 635          switch ($context->contextlevel) {
 636              case CONTEXT_USER:
 637                  $userid = $context->instanceid;
 638                  static::delete_user_evidence_of_prior_learning($userid);
 639                  static::delete_user_plans($userid);
 640                  static::delete_user_competencies($userid);
 641                  break;
 642  
 643              case CONTEXT_COURSE:
 644                  static::delete_user_competencies_in_course($context->instanceid);
 645                  break;
 646          }
 647      }
 648  
 649      /**
 650       * Delete all user data for the specified user, in the specified contexts.
 651       *
 652       * Here we only delete the private data of user, not whether they modified, are reviewing,
 653       * or are associated with the record on at a second level. Only data directly linked to the
 654       * user will be affected.
 655       *
 656       * @param approved_contextlist $contextlist The approved contexts and user information to delete information for.
 657       */
 658      public static function delete_data_for_user(approved_contextlist $contextlist) {
 659          $user = $contextlist->get_user();
 660          $userid = $user->id;
 661  
 662          foreach ($contextlist as $context) {
 663              switch ($context->contextlevel) {
 664                  case CONTEXT_USER:
 665                      if ($context->instanceid == $userid) {
 666                          // Only delete the user's information when they requested their context to be deleted. We
 667                          // do not take any action on other user's contexts because we don't own the data there.
 668                          static::delete_user_evidence_of_prior_learning($userid);
 669                          static::delete_user_plans($userid);
 670                          static::delete_user_competencies($userid);
 671                      }
 672                      break;
 673  
 674                  case CONTEXT_COURSE:
 675                      static::delete_user_competencies_in_course($context->instanceid, [$userid]);
 676                      break;
 677              }
 678          }
 679      }
 680  
 681      /**
 682       * Delete multiple users within a single context.
 683       *
 684       * Here we only delete the private data of users, not whether they modified, are reviewing,
 685       * or are associated with the record on at a second level. Only data directly linked to the
 686       * user will be affected.
 687       *
 688       * @param   approved_userlist       $userlist The approved context and user information to delete information for.
 689       */
 690      public static function delete_data_for_users(approved_userlist $userlist) {
 691          $context = $userlist->get_context();
 692          $userids = $userlist->get_userids();
 693  
 694          switch ($context->contextlevel) {
 695              case CONTEXT_USER:
 696                  // Only delete the user's information when their context is being deleted.
 697                  // We do not take any action on other user's contexts because we don't own the data there.
 698                  if (in_array($context->instanceid, $userids)) {
 699                      static::delete_user_evidence_of_prior_learning($context->instanceid);
 700                      static::delete_user_plans($context->instanceid);
 701                      static::delete_user_competencies($context->instanceid);
 702                  }
 703  
 704                  break;
 705  
 706              case CONTEXT_COURSE:
 707                  static::delete_user_competencies_in_course($context->instanceid, $userids);
 708                  break;
 709          }
 710      }
 711  
 712      /**
 713       * Delete evidence of prior learning.
 714       *
 715       * @param int $userid The user ID.
 716       * @return void
 717       */
 718      protected static function delete_user_evidence_of_prior_learning($userid) {
 719          global $DB;
 720  
 721          $usercontext = context_user::instance($userid);
 722          $ueids = $DB->get_fieldset_select(user_evidence::TABLE, 'id', 'userid = :userid', ['userid' => $userid]);
 723          if (empty($ueids)) {
 724              return;
 725          }
 726          list($insql, $inparams) = $DB->get_in_or_equal($ueids, SQL_PARAMS_NAMED);
 727  
 728          // Delete competencies associated with user evidence.
 729          $DB->delete_records_select(user_evidence_competency::TABLE, "userevidenceid $insql", $inparams);
 730  
 731          // Delete the user evidence.
 732          $DB->delete_records_select(user_evidence::TABLE, "id $insql", $inparams);
 733  
 734          // Delete the user evidence files.
 735          $fs = get_file_storage();
 736          $fs->delete_area_files($usercontext->id, 'core_competency', 'userevidence');
 737      }
 738  
 739      /**
 740       * User plans.
 741       *
 742       * @param int $userid The user ID.
 743       * @return void
 744       */
 745      protected static function delete_user_plans($userid) {
 746          global $DB;
 747          $usercontext = context_user::instance($userid);
 748  
 749          // Remove all the comments made on plans.
 750          \core_comment\privacy\provider::delete_comments_for_all_users($usercontext, 'competency', 'plan');
 751  
 752          // Find the user plan IDs.
 753          $planids = $DB->get_fieldset_select(plan::TABLE, 'id', 'userid = :userid', ['userid' => $userid]);
 754          if (empty($planids)) {
 755              return;
 756          }
 757          list($insql, $inparams) = $DB->get_in_or_equal($planids, SQL_PARAMS_NAMED);
 758  
 759          // Delete all the competencies proficiency in the plans.
 760          $DB->delete_records_select(user_competency_plan::TABLE, "planid $insql", $inparams);
 761  
 762          // Delete all the competencies in the plans.
 763          $DB->delete_records_select(plan_competency::TABLE, "planid $insql", $inparams);
 764  
 765          // Delete all the plans.
 766          $DB->delete_records_select(plan::TABLE, "id $insql", $inparams);
 767      }
 768  
 769      /**
 770       * Delete user competency data.
 771       *
 772       * @param int $userid The user ID.
 773       * @return void
 774       */
 775      protected static function delete_user_competencies($userid) {
 776          global $DB;
 777          $usercontext = context_user::instance($userid);
 778  
 779          // Remove all the comments made on user competencies.
 780          \core_comment\privacy\provider::delete_comments_for_all_users($usercontext, 'competency', 'user_competency');
 781  
 782          // Find the user competency IDs.
 783          $ucids = $DB->get_fieldset_select(user_competency::TABLE, 'id', 'userid = :userid', ['userid' => $userid]);
 784          if (empty($ucids)) {
 785              return;
 786          }
 787          list($insql, $inparams) = $DB->get_in_or_equal($ucids, SQL_PARAMS_NAMED);
 788  
 789          // Delete all the evidence associated with competencies.
 790          $DB->delete_records_select(evidence::TABLE, "usercompetencyid $insql", $inparams);
 791  
 792          // Delete all the record of competency.
 793          $DB->delete_records_select(user_competency::TABLE, "id $insql", $inparams);
 794      }
 795  
 796      /**
 797       * Delete the record of competencies for user(s) in a course.
 798       *
 799       * @param int $courseid The course ID.
 800       * @param int[] $userids The user IDs, if deleting for specific user(s).
 801       * @return void
 802       */
 803      protected static function delete_user_competencies_in_course($courseid, $userids = []) {
 804          global $DB;
 805  
 806          $params = ['courseid' => $courseid];
 807          $where = "courseid = :courseid";
 808  
 809          if (!empty($userids)) {
 810              list($insql, $inparams) = $DB->get_in_or_equal($userids, SQL_PARAMS_NAMED);
 811              $params = $params + $inparams;
 812  
 813              $where .= " AND userid {$insql}";
 814          }
 815  
 816          $DB->delete_records_select(user_competency_course::TABLE, $where, $params);
 817      }
 818  
 819      /**
 820       * Export the user data in user context.
 821       *
 822       * @param int $userid The user ID.
 823       * @param array $contexts The contexts.
 824       * @return void
 825       */
 826      protected static function export_user_data_in_user_contexts($userid, array $contexts) {
 827          global $DB;
 828  
 829          $mycontext = context_user::instance($userid);
 830          $contextids = array_map(function($context) {
 831              return $context->id;
 832          }, $contexts);
 833          $exportowncontext = in_array($mycontext->id, $contextids);
 834          $othercontexts = array_filter($contextids, function($contextid) use ($mycontext) {
 835              return $contextid != $mycontext->id;
 836          });
 837  
 838          if ($exportowncontext) {
 839              static::export_user_data_learning_plans($mycontext);
 840              static::export_user_data_competencies($mycontext);
 841              static::export_user_data_user_evidence($mycontext);
 842          }
 843  
 844          foreach ($othercontexts as $contextid) {
 845              static::export_user_data_learning_plans_related_to_me($userid, context::instance_by_id($contextid));
 846              static::export_user_data_competencies_related_to_me($userid, context::instance_by_id($contextid));
 847              static::export_user_data_user_evidence_related_to_me($userid, context::instance_by_id($contextid));
 848          }
 849      }
 850  
 851      /**
 852       * Export the user data in systen context.
 853       *
 854       * @param int $userid The user ID.
 855       * @return void
 856       */
 857      protected static function export_user_data_in_system_context($userid) {
 858          static::export_user_data_frameworks_in_context($userid, context_system::instance());
 859          static::export_user_data_templates_in_context($userid, context_system::instance());
 860      }
 861  
 862      /**
 863       * Export the user data in category contexts.
 864       *
 865       * @param int $userid The user ID.
 866       * @param array $contexts The contexts.
 867       * @return void
 868       */
 869      protected static function export_user_data_in_category_contexts($userid, array $contexts) {
 870          $contexts = array_filter($contexts, function($context) {
 871              return $context->contextlevel == CONTEXT_COURSECAT;
 872          });
 873          if (empty($contexts)) {
 874              return;
 875          }
 876  
 877          foreach ($contexts as $context) {
 878              static::export_user_data_frameworks_in_context($userid, $context);
 879              static::export_user_data_templates_in_context($userid, $context);
 880          }
 881      }
 882  
 883      /**
 884       * Export the user data in course contexts.
 885       *
 886       * @param int $userid The user whose data we're exporting.
 887       * @param array $contexts A list of contexts.
 888       * @return void
 889       */
 890      protected static function export_user_data_in_course_contexts($userid, array $contexts) {
 891          global $DB;
 892  
 893          $contexts = array_filter($contexts, function($context) {
 894              return $context->contextlevel == CONTEXT_COURSE;
 895          });
 896          if (empty($contexts)) {
 897              return;
 898          }
 899  
 900          $helper = new performance_helper();
 901          $path = [get_string('competencies', 'core_competency')];
 902          $courseids = array_map(function($context) {
 903              return $context->instanceid;
 904          }, $contexts);
 905  
 906          // Fetch all the records of competency proficiency in the course.
 907          $ffields = competency_framework::get_sql_fields('f', 'f_');
 908          $compfields = competency::get_sql_fields('c', 'c_');
 909          $uccfields = user_competency_course::get_sql_fields('ucc', 'ucc_');
 910          $ctxfields = context_helper::get_preload_record_columns_sql('ctx');
 911          list($insql, $inparams) = $DB->get_in_or_equal($courseids, SQL_PARAMS_NAMED);
 912          $sql = "
 913              SELECT $ffields, $compfields, $uccfields, $ctxfields
 914                FROM {" . user_competency_course::TABLE . "} ucc
 915                JOIN {" . competency::TABLE . "} c
 916                  ON c.id = ucc.competencyid
 917                JOIN {" . competency_framework::TABLE . "} f
 918                  ON f.id = c.competencyframeworkid
 919                JOIN {context} ctx
 920                  ON ctx.id = f.contextid
 921               WHERE ucc.userid = :userid
 922                 AND ucc.courseid $insql
 923            ORDER BY ucc.courseid, c.id";
 924          $params = array_merge($inparams, ['userid' => $userid]);
 925  
 926          // Export data.
 927          $recordset = $DB->get_recordset_sql($sql, $params);
 928          static::recordset_loop_and_export($recordset, 'ucc_courseid', [], function($carry, $record) use ($helper) {
 929              context_helper::preload_from_record($record);
 930              $framework = new competency_framework(null, competency_framework::extract_record($record, 'f_'));
 931              $competency = new competency(null, competency::extract_record($record, 'c_'));
 932              $ucc = new user_competency_course(null, user_competency_course::extract_record($record, 'ucc_'));
 933              $helper->ingest_framework($framework);
 934  
 935              $carry[] = array_merge(static::transform_competency_brief($competency), [
 936                  'rating' => [
 937                      'rating' => static::transform_competency_grade($competency, $ucc->get('grade'), $helper),
 938                      'proficient' => static::transform_proficiency($ucc->get('proficiency')),
 939                      'timecreated' => transform::datetime($ucc->get('timecreated')),
 940                      'timemodified' => transform::datetime($ucc->get('timemodified')),
 941                  ]
 942              ]);
 943              return $carry;
 944  
 945          }, function($courseid, $data) use ($path) {
 946              $context = context_course::instance($courseid);
 947              writer::with_context($context)->export_data($path, (object) ['ratings' => $data]);
 948          });
 949  
 950          // Export usermodified data.
 951          static::export_user_data_in_course_contexts_associations($userid, $courseids, $path);
 952          static::export_user_data_in_course_contexts_settings($userid, $courseids, $path);
 953          static::export_user_data_in_course_contexts_rated_by_me($userid, $courseids, $path, $helper);
 954      }
 955  
 956      /**
 957       * Export the ratings given in a course.
 958       *
 959       * @param int $userid The user ID.
 960       * @param array $courseids The course IDs.
 961       * @param array $path The root path.
 962       * @param performance_helper $helper The performance helper.
 963       * @return void
 964       */
 965      protected static function export_user_data_in_course_contexts_rated_by_me($userid, $courseids, $path,
 966              performance_helper $helper) {
 967          global $DB;
 968  
 969          // Fetch all the records of competency proficiency in the course.
 970          $ffields = competency_framework::get_sql_fields('f', 'f_');
 971          $compfields = competency::get_sql_fields('c', 'c_');
 972          $uccfields = user_competency_course::get_sql_fields('ucc', 'ucc_');
 973          $ctxfields = context_helper::get_preload_record_columns_sql('ctx');
 974          list($insql, $inparams) = $DB->get_in_or_equal($courseids, SQL_PARAMS_NAMED);
 975          $sql = "
 976              SELECT $ffields, $compfields, $uccfields, $ctxfields
 977                FROM {" . user_competency_course::TABLE . "} ucc
 978                JOIN {" . competency::TABLE . "} c
 979                  ON c.id = ucc.competencyid
 980                JOIN {" . competency_framework::TABLE . "} f
 981                  ON f.id = c.competencyframeworkid
 982                JOIN {context} ctx
 983                  ON ctx.id = f.contextid
 984               WHERE ucc.usermodified = :userid
 985                 AND ucc.courseid $insql
 986            ORDER BY ucc.courseid, ucc.id";
 987          $params = array_merge($inparams, ['userid' => $userid]);
 988  
 989          // Export the data.
 990          static::recordset_loop_and_export($DB->get_recordset_sql($sql, $params), 'ucc_courseid', [],
 991              function($carry, $record) use ($helper) {
 992                  context_helper::preload_from_record($record);
 993  
 994                  $framework = new competency_framework(null, competency_framework::extract_record($record, 'f_'));
 995                  $competency = new competency(null, competency::extract_record($record, 'c_'));
 996                  $ucc = new user_competency_course(null, user_competency_course::extract_record($record, 'ucc_'));
 997                  $helper->ingest_framework($framework);
 998  
 999                  $carry[] = array_merge(static::transform_competency_brief($competency), [
1000                      'rating' => [
1001                          'userid' => transform::user($ucc->get('userid')),
1002                          'rating' => static::transform_competency_grade($competency, $ucc->get('grade'), $helper),
1003                          'proficient' => static::transform_proficiency($ucc->get('proficiency')),
1004                          'timemodified' => transform::datetime($ucc->get('timemodified')),
1005                      ]
1006                  ]);
1007                  return $carry;
1008  
1009              }, function($courseid, $data) use ($path) {
1010                  $context = context_course::instance($courseid);
1011                  writer::with_context($context)->export_related_data($path, 'rated_by_me', (object) [
1012                      'ratings' => $data
1013                  ]);
1014              }
1015          );
1016      }
1017  
1018      /**
1019       * Export user data in course contexts related to linked competencies.
1020       *
1021       * @param int $userid The user ID.
1022       * @param array $courseids The course IDs.
1023       * @param array $path The root path to export at.
1024       * @return void
1025       */
1026      protected static function export_user_data_in_course_contexts_associations($userid, $courseids, $path) {
1027          global $DB;
1028  
1029          // Fetch all the courses with associations we created or modified.
1030          $compfields = competency::get_sql_fields('c', 'c_');
1031          $ccfields = course_competency::get_sql_fields('cc', 'cc_');
1032          $ctxfields = context_helper::get_preload_record_columns_sql('ctx');
1033          list($insql, $inparams) = $DB->get_in_or_equal($courseids, SQL_PARAMS_NAMED);
1034          $sql = "
1035              SELECT $compfields, $ccfields, $ctxfields
1036                FROM {" . course_competency::TABLE . "} cc
1037                JOIN {" . competency::TABLE . "} c
1038                  ON c.id = cc.competencyid
1039                JOIN {" . competency_framework::TABLE . "} f
1040                  ON f.id = c.competencyframeworkid
1041                JOIN {context} ctx
1042                  ON ctx.id = f.contextid
1043               WHERE cc.usermodified = :userid
1044                 AND cc.courseid $insql
1045            ORDER BY cc.courseid, c.id";
1046          $params = array_merge($inparams, ['userid' => $userid]);
1047          $recordset = $DB->get_recordset_sql($sql, $params);
1048  
1049          // Export the data.
1050          static::recordset_loop_and_export($recordset, 'cc_courseid', [], function($carry, $record) {
1051              context_helper::preload_from_record($record);
1052              $competency = new competency(null, competency::extract_record($record, 'c_'));
1053              $cc = new course_competency(null, course_competency::extract_record($record, 'cc_'));
1054              $carry[] = array_merge(static::transform_competency_brief($competency), [
1055                  'timemodified' => transform::datetime($cc->get('timemodified')),
1056                  'created_or_modified_by_you' => transform::yesno(true)
1057              ]);
1058              return $carry;
1059  
1060          }, function($courseid, $data) use ($path, $userid, $DB) {
1061              $context = context_course::instance($courseid);
1062              writer::with_context($context)->export_related_data($path, 'associations', (object) ['competencies' => $data]);
1063          });
1064      }
1065  
1066      /**
1067       * Export user data in course contexts related to course settings.
1068       *
1069       * @param int $userid The user ID.
1070       * @param array $courseids The course IDs.
1071       * @param array $path The root path to export at.
1072       * @return void
1073       */
1074      protected static function export_user_data_in_course_contexts_settings($userid, $courseids, $path) {
1075          global $DB;
1076  
1077          // Fetch all the courses with associations we created or modified.
1078          $ccsfields = course_competency_settings::get_sql_fields('ccs', 'ccs_');
1079          list($insql, $inparams) = $DB->get_in_or_equal($courseids, SQL_PARAMS_NAMED);
1080          $sql = "
1081              SELECT $ccsfields
1082                FROM {" . course_competency_settings::TABLE . "} ccs
1083               WHERE ccs.usermodified = :userid
1084                 AND ccs.courseid $insql
1085            ORDER BY ccs.courseid";
1086          $params = array_merge($inparams, ['userid' => $userid]);
1087          $recordset = $DB->get_recordset_sql($sql, $params);
1088  
1089          // Export the data.
1090          static::recordset_loop_and_export($recordset, 'ccs_courseid', [], function($carry, $record) {
1091              $ccs = new course_competency_settings(null, course_competency_settings::extract_record($record, 'ccs_'));
1092              return [
1093                  'timemodified' => transform::datetime($ccs->get('timemodified')),
1094                  'created_or_modified_by_you' => transform::yesno(true)
1095              ];
1096          }, function($courseid, $data) use ($path, $userid, $DB) {
1097              $context = context_course::instance($courseid);
1098              writer::with_context($context)->export_related_data($path, 'settings', (object) $data);
1099          });
1100      }
1101  
1102      /**
1103       * Export the user data in module contexts.
1104       *
1105       * @param int $userid The user whose data we're exporting.
1106       * @param array $contexts A list of contexts.
1107       * @return void
1108       */
1109      protected static function export_user_data_in_module_contexts($userid, array $contexts) {
1110          global $DB;
1111  
1112          $contexts = array_filter($contexts, function($context) {
1113              return $context->contextlevel == CONTEXT_MODULE;
1114          });
1115          if (empty($contexts)) {
1116              return;
1117          }
1118  
1119          $path = [get_string('competencies', 'core_competency')];
1120          $cmids = array_map(function($context) {
1121              return $context->instanceid;
1122          }, $contexts);
1123  
1124          // Fetch all the modules with associations we created or modified.
1125          $compfields = competency::get_sql_fields('c', 'c_');
1126          $cmcfields = course_module_competency::get_sql_fields('cmc', 'cmc_');
1127          $ctxfields = context_helper::get_preload_record_columns_sql('ctx');
1128          list($insql, $inparams) = $DB->get_in_or_equal($cmids, SQL_PARAMS_NAMED);
1129          $sql = "
1130              SELECT $compfields, $cmcfields, $ctxfields
1131                FROM {" . course_module_competency::TABLE . "} cmc
1132                JOIN {" . competency::TABLE . "} c
1133                  ON c.id = cmc.competencyid
1134                JOIN {" . competency_framework::TABLE . "} f
1135                  ON f.id = c.competencyframeworkid
1136                JOIN {context} ctx
1137                  ON ctx.id = f.contextid
1138               WHERE cmc.usermodified = :userid
1139                 AND cmc.cmid $insql
1140            ORDER BY cmc.cmid";
1141          $params = array_merge($inparams, ['userid' => $userid]);
1142  
1143          // Export the data.
1144          $recordset = $DB->get_recordset_sql($sql, $params);
1145          static::recordset_loop_and_export($recordset, 'cmc_cmid', [], function($carry, $record) {
1146              context_helper::preload_from_record($record);
1147              $competency = new competency(null, competency::extract_record($record, 'c_'));
1148              $cmc = new course_module_competency(null, course_module_competency::extract_record($record, 'cmc_'));
1149              $carry[] = array_merge(static::transform_competency_brief($competency), [
1150                  'timecreated' => transform::datetime($cmc->get('timecreated')),
1151                  'timemodified' => transform::datetime($cmc->get('timemodified')),
1152                  'created_or_modified_by_you' => transform::yesno(true)
1153              ]);
1154              return $carry;
1155  
1156          }, function($cmid, $data) use ($path) {
1157              $context = context_module::instance($cmid);
1158              writer::with_context($context)->export_data($path, (object) ['associations' => $data]);
1159          });
1160      }
1161  
1162      /**
1163       * Export a user's competencies.
1164       *
1165       * @param context_user $context The context of the user requesting the export.
1166       * @return void
1167       */
1168      protected static function export_user_data_competencies(context_user $context) {
1169          global $DB;
1170  
1171          $userid = $context->instanceid;
1172          $path = [get_string('competencies', 'core_competency'), get_string('competencies', 'core_competency')];
1173          $helper = new performance_helper();
1174          $cfields = competency::get_sql_fields('c', 'c_');
1175          $ucfields = user_competency::get_sql_fields('uc', 'uc_');
1176          $efields = evidence::get_sql_fields('e', 'e_');
1177  
1178          $makecomppath = function($competencyid, $data) use ($path) {
1179              return array_merge($path, [$data['name'] . ' (' . $competencyid . ')']);
1180          };
1181  
1182          $sql = "
1183              SELECT $cfields, $ucfields, $efields
1184                FROM {" . user_competency::TABLE . "} uc
1185                JOIN {" . competency::TABLE . "} c
1186                  ON c.id = uc.competencyid
1187           LEFT JOIN {" . evidence::TABLE . "} e
1188                  ON uc.id = e.usercompetencyid
1189               WHERE uc.userid = :userid
1190            ORDER BY c.id, e.timecreated DESC, e.id DESC";
1191          $params = ['userid' => $userid];
1192  
1193          $recordset = $DB->get_recordset_sql($sql, $params);
1194          static::recordset_loop_and_export($recordset, 'c_id', null, function($carry, $record)
1195                  use ($context, $userid, $helper, $makecomppath) {
1196  
1197              $competency = new competency(null, competency::extract_record($record, 'c_'));
1198  
1199              if ($carry === null) {
1200                  $uc = new user_competency(null, user_competency::extract_record($record, 'uc_'));
1201                  $carry = array_merge(static::transform_competency_brief($competency), [
1202                      'rating' => static::transform_user_competency($userid, $uc, $competency, $helper),
1203                      'evidence' => []
1204                  ]);
1205                  \core_comment\privacy\provider::export_comments($context, 'competency', 'user_competency',
1206                      $uc->get('id'), $makecomppath($competency->get('id'), $carry), false);
1207              }
1208  
1209              // There is an evidence in this record.
1210              if (!empty($record->e_id)) {
1211                  $evidence = new evidence(null, evidence::extract_record($record, 'e_'));
1212                  $carry['evidence'][] = static::transform_evidence($userid, $evidence, $competency, $helper);
1213              }
1214  
1215              return $carry;
1216  
1217          }, function($competencyid, $data) use ($makecomppath, $context) {
1218              writer::with_context($context)->export_data($makecomppath($competencyid, $data), (object) $data);
1219          });
1220      }
1221  
1222      /**
1223       * Export a user's learning plans.
1224       *
1225       * @param context_user $context The context of the user requesting the export.
1226       * @return void
1227       */
1228      protected static function export_user_data_learning_plans(context_user $context) {
1229          global $DB;
1230  
1231          $userid = $context->instanceid;
1232          $path = [get_string('competencies', 'core_competency'), get_string('privacy:path:plans', 'core_competency')];
1233          $helper = new performance_helper();
1234          $pfields = plan::get_sql_fields('p', 'p_');
1235          $pcfields = plan_competency::get_sql_fields('pc', 'pc_');
1236          $cfields = competency::get_sql_fields('c', 'c_');
1237          $ucfields = user_competency::get_sql_fields('uc', 'uc_');
1238          $ucpfields = user_competency_plan::get_sql_fields('ucp', 'ucp_');
1239  
1240          // The user's learning plans.
1241          $sql = "
1242              SELECT $pfields, $pcfields, $cfields, $ucfields, $ucpfields
1243                FROM {" . plan::TABLE . "} p
1244           LEFT JOIN {" . plan_competency::TABLE . "} pc
1245                  ON p.id = pc.planid
1246                 AND p.templateid IS NULL
1247                 AND p.status != :complete1
1248           LEFT JOIN {" . template_competency::TABLE . "} tc
1249                  ON tc.templateid = p.templateid
1250                 AND p.templateid IS NOT NULL
1251                 AND p.status != :complete2
1252           LEFT JOIN {" . user_competency_plan::TABLE . "} ucp
1253                  ON ucp.planid = p.id
1254                 AND p.status = :complete3
1255           LEFT JOIN {" . competency::TABLE . "} c
1256                  ON c.id = pc.competencyid
1257                  OR c.id = tc.competencyid
1258                  OR c.id = ucp.competencyid
1259           LEFT JOIN {" . user_competency::TABLE . "} uc
1260                  ON uc.userid = p.userid
1261                 AND (uc.competencyid = pc.competencyid OR uc.competencyid = tc.competencyid)
1262               WHERE p.userid = :userid
1263            ORDER BY p.id, c.id";
1264          $params = [
1265              'userid' => $userid,
1266              'complete1' => plan::STATUS_COMPLETE,
1267              'complete2' => plan::STATUS_COMPLETE,
1268              'complete3' => plan::STATUS_COMPLETE,
1269          ];
1270  
1271          $recordset = $DB->get_recordset_sql($sql, $params);
1272          static::recordset_loop_and_export($recordset, 'p_id', null, function($carry, $record) use ($userid, $helper, $context) {
1273              $iscomplete = $record->p_status == plan::STATUS_COMPLETE;
1274  
1275              if ($carry === null) {
1276                  $plan = new plan(null, plan::extract_record($record, 'p_'));
1277                  $options = ['context' => $context];
1278                  $carry = [
1279                      'name' => format_string($plan->get('name'), true, $options),
1280                      'description' => format_text($plan->get('description'), $plan->get('descriptionformat'), $options),
1281                      'status' => $plan->get_statusname(),
1282                      'duedate' => $plan->get('duedate') ? transform::datetime($plan->get('duedate')) : '-',
1283                      'reviewerid' => $plan->get('reviewerid') ? transform::user($plan->get('reviewerid')) : '-',
1284                      'timecreated' => transform::datetime($plan->get('timecreated')),
1285                      'timemodified' => transform::datetime($plan->get('timemodified')),
1286                      'competencies' => [],
1287                  ];
1288              }
1289  
1290              // The plan is empty.
1291              if (empty($record->c_id)) {
1292                  return $carry;
1293              }
1294  
1295              $competency = new competency(null, competency::extract_record($record, 'c_'));
1296              $rating = null;
1297  
1298              if ($iscomplete) {
1299                  // When the plan is complete, we should always found the user_competency_plan.
1300                  $ucp = new user_competency_plan(null, user_competency_plan::extract_record($record, 'ucp_'));
1301                  $rating = static::transform_user_competency($userid, $ucp, $competency, $helper);
1302  
1303              } else if (!empty($record->uc_id)) {
1304                  // When the plan is complete, there are still records of user_competency but we do not
1305                  // export them here, we export them as part of the competencies structure. The reason why
1306                  // we try to get the user_competency when the plan is not complete is to give the most accurate
1307                  // representation of the plan as possible.
1308                  $uc = new user_competency(null, user_competency::extract_record($record, 'uc_'));
1309                  $rating = static::transform_user_competency($userid, $uc, $competency, $helper);
1310              }
1311  
1312              $carry['competencies'][] = array_merge(static::transform_competency_brief($competency), ['rating' => $rating]);
1313              return $carry;
1314  
1315          }, function($planid, $data) use ($context, $path) {
1316              $planpath = array_merge($path, [$data['name'] . ' (' . $planid . ')']);
1317              \core_comment\privacy\provider::export_comments($context, 'competency', 'plan', $planid, $planpath, false);
1318              writer::with_context($context)->export_data($planpath, (object) $data);
1319          });
1320      }
1321  
1322      /**
1323       * Export a user's data related to learning plans.
1324       *
1325       * @param int $userid The user ID we're exporting for.
1326       * @param context_user $context The context of the user in which we're gathering data.
1327       * @return void
1328       */
1329      protected static function export_user_data_learning_plans_related_to_me($userid, context_user $context) {
1330          global $DB;
1331  
1332          $path = [
1333              get_string('competencies', 'core_competency'),
1334              get_string('privacy:path:relatedtome', 'core_competency'),
1335              get_string('privacy:path:plans', 'core_competency'),
1336          ];
1337          $plans = [];
1338          $helper = new performance_helper();
1339          $pfields = plan::get_sql_fields('p', 'p_');
1340          $pcfields = plan_competency::get_sql_fields('pc', 'pc_');
1341          $cfields = competency::get_sql_fields('c', 'c_');
1342          $ucpfields = user_competency_plan::get_sql_fields('ucp', 'ucp_');
1343  
1344          // Function to initialise a plan record.
1345          $initplan = function($record) use ($context, $userid, &$plans) {
1346              $plan = new plan(null, plan::extract_record($record, 'p_'));
1347              $options = ['context' => $context];
1348              $plans[$plan->get('id')] = [
1349                  'name' => format_string($plan->get('name'), true, $options),
1350                  'reviewer_is_you' => transform::yesno($plan->get('reviewerid') == $userid),
1351                  'timecreated' => transform::datetime($plan->get('timecreated')),
1352                  'timemodified' => transform::datetime($plan->get('timemodified')),
1353                  'created_or_modified_by_you' => transform::yesno($plan->get('usermodified') == $userid),
1354                  'competencies' => [],
1355              ];
1356          };
1357  
1358          $initcompetency = function($record, $planid) use (&$plans) {
1359              $competency = new competency(null, competency::extract_record($record, 'c_'));
1360              $plans[$planid]['competencies'][$competency->get('id')] = static::transform_competency_brief($competency);
1361          };
1362  
1363          // Look for associations that were created.
1364          $sql = "
1365              SELECT $pfields, $pcfields, $cfields
1366                FROM {" . plan_competency::TABLE . "} pc
1367                JOIN {" . plan::TABLE . "} p
1368                  ON p.id = pc.planid
1369                JOIN {" . competency::TABLE . "} c
1370                  ON c.id = pc.competencyid
1371               WHERE p.userid = :targetuserid
1372                 AND pc.usermodified = :userid
1373            ORDER BY p.id, c.id";
1374          $params = [
1375              'targetuserid' => $context->instanceid,
1376              'userid' => $userid,
1377          ];
1378  
1379          $recordset = $DB->get_recordset_sql($sql, $params);
1380          foreach ($recordset as $record) {
1381              $planid = $record->p_id;
1382              if (!isset($plans[$planid])) {
1383                  $initplan($record);
1384              }
1385  
1386              $initcompetency($record, $planid);
1387              $pc = new plan_competency(null, plan_competency::extract_record($record, 'pc_'));
1388              $plans[$planid]['competencies'][$pc->get('competencyid')] = array_merge(
1389                  $plans[$planid]['competencies'][$pc->get('competencyid')], [
1390                      'timemodified' => $pc->get('timemodified') ? transform::datetime($pc->get('timemodified')) : '-',
1391                      'timecreated' => $pc->get('timecreated') ? transform::datetime($pc->get('timecreated')) : '-',
1392                      'created_or_modified_by_you' => transform::yesno($pc->get('usermodified') == $userid),
1393                  ]
1394              );
1395          }
1396          $recordset->close();
1397  
1398          // Look for final grades that were given.
1399          $sql = "
1400              SELECT $pfields, $ucpfields, $cfields
1401                FROM {" . user_competency_plan::TABLE . "} ucp
1402                JOIN {" . plan::TABLE . "} p
1403                  ON p.id = ucp.planid
1404                JOIN {" . competency::TABLE . "} c
1405                  ON c.id = ucp.competencyid
1406               WHERE p.userid = :targetuserid
1407                 AND ucp.usermodified = :userid
1408            ORDER BY p.id, c.id";
1409          $params = [
1410              'targetuserid' => $context->instanceid,
1411              'userid' => $userid,
1412          ];
1413  
1414          $recordset = $DB->get_recordset_sql($sql, $params);
1415          foreach ($recordset as $record) {
1416              $planid = $record->p_id;
1417              $competencyid = $record->c_id;
1418  
1419              if (!isset($plans[$planid])) {
1420                  $initplan($record);
1421              }
1422  
1423              if (!isset($plans[$planid]['competencies'][$competencyid])) {
1424                  $initcompetency($record, $planid);
1425              }
1426  
1427              $competency = new competency(null, competency::extract_record($record, 'c_'));
1428              $ucp = new user_competency_plan(null, user_competency_plan::extract_record($record, 'ucp_'));
1429              $plans[$planid]['competencies'][$competencyid]['rating'] = static::transform_user_competency($userid, $ucp,
1430                  $competency, $helper);
1431          }
1432          $recordset->close();
1433  
1434          // Find the plans that were modified or reviewed.
1435          $insql = " > 0";
1436          $inparams = [];
1437          if (!empty($plans)) {
1438              list($insql, $inparams) = $DB->get_in_or_equal(array_keys($plans), SQL_PARAMS_NAMED, 'param', false);
1439          }
1440          $sql = "
1441              SELECT $pfields
1442                FROM {" . plan::TABLE . "} p
1443           LEFT JOIN {comments} c
1444                  ON c.contextid = :contextid
1445                 AND c.commentarea = :planarea
1446                 AND c.component = :competency
1447                 AND c.itemid = p.id
1448               WHERE p.userid = :targetuserid
1449                 AND (p.usermodified = :userid1
1450                  OR p.reviewerid = :userid2
1451                  OR c.userid = :userid3)
1452                 AND p.id $insql
1453            ORDER BY p.id";
1454          $params = array_merge($inparams, [
1455              'targetuserid' => $context->instanceid,
1456              'userid1' => $userid,
1457              'userid2' => $userid,
1458              'userid3' => $userid,
1459              'contextid' => $context->id,
1460              'planarea' => 'plan',
1461              'competency' => 'competency'
1462          ]);
1463  
1464          $recordset = $DB->get_recordset_sql($sql, $params);
1465          foreach ($recordset as $record) {
1466              $planid = $record->p_id;
1467              if (!isset($plans[$planid])) {
1468                  $initplan($record);
1469              }
1470          }
1471          $recordset->close();
1472  
1473          // Export each plan on its own.
1474          foreach ($plans as $planid => $plan) {
1475              $planpath = array_merge($path, ["{$plan['name']} ({$planid})"]);
1476              $plan['competencies'] = array_values($plan['competencies']);    // Drop the keys.
1477  
1478              writer::with_context($context)->export_data($planpath, (object) $plan);
1479              \core_comment\privacy\provider::export_comments($context, 'competency', 'plan', $planid, $planpath, true);
1480          }
1481      }
1482  
1483      /**
1484       * Export a user's data related to competencies.
1485       *
1486       * @param int $userid The user ID we're exporting for.
1487       * @param context_user $context The context of the user in which we're gathering data.
1488       * @return void
1489       */
1490      protected static function export_user_data_competencies_related_to_me($userid, context_user $context) {
1491          global $DB;
1492  
1493          $path = [
1494              get_string('competencies', 'core_competency'),
1495              get_string('privacy:path:relatedtome', 'core_competency'),
1496              get_string('competencies', 'core_competency'),
1497          ];
1498          $competencies = [];
1499          $helper = new performance_helper();
1500          $cfields = competency::get_sql_fields('c', 'c_');
1501          $ucfields = user_competency::get_sql_fields('uc', 'uc_');
1502          $efields = evidence::get_sql_fields('e', 'e_');
1503  
1504          $initcompetency = function($record) use (&$competencies) {
1505              $competency = new competency(null, competency::extract_record($record, 'c_'));
1506              $competencies[$competency->get('id')] = array_merge(static::transform_competency_brief($competency), [
1507                  'evidence' => []
1508              ]);
1509          };
1510  
1511          $initusercomp = function($competency, $record) use (&$competencies, $userid, $helper) {
1512              $competencyid = $competency->get('id');
1513              $uc = new user_competency(null, user_competency::extract_record($record, 'uc_'));
1514              $competencies[$competencyid]['uc_id'] = $uc->get('id');
1515              $competencies[$competencyid]['rating'] = static::transform_user_competency($userid, $uc, $competency, $helper);
1516          };
1517  
1518          // Look for evidence.
1519          $sql = "
1520              SELECT $efields, $ucfields, $cfields
1521                FROM {" . evidence::TABLE . "} e
1522                JOIN {" . user_competency::TABLE . "} uc
1523                  ON uc.id = e.usercompetencyid
1524                JOIN {" . competency::TABLE . "} c
1525                  ON c.id = uc.competencyid
1526               WHERE uc.userid = :targetuserid
1527                 AND (e.usermodified = :userid1
1528                  OR e.actionuserid = :userid2)
1529            ORDER BY c.id, e.id";
1530          $params = [
1531              'targetuserid' => $context->instanceid,
1532              'userid1' => $userid,
1533              'userid2' => $userid,
1534          ];
1535          $recordset = $DB->get_recordset_sql($sql, $params);
1536          foreach ($recordset as $record) {
1537              $competencyid = $record->c_id;
1538              $competency = new competency(null, competency::extract_record($record, 'c_'));
1539  
1540              if (!isset($competencies[$competencyid])) {
1541                  $initcompetency($record);
1542              }
1543  
1544              if (!array_key_exists('rating', $competencies[$competencyid])) {
1545                  $competencies[$competencyid]['rating'] = null;
1546                  if ($record->uc_reviewerid == $userid || $record->uc_usermodified == $userid) {
1547                      $initusercomp($competency, $record);
1548                  }
1549              }
1550  
1551              $evidence = new evidence(null, evidence::extract_record($record, 'e_'));
1552              $competencies[$competencyid]['evidence'][] = static::transform_evidence($userid, $evidence, $competency, $helper);
1553          }
1554          $recordset->close();
1555  
1556          // Look for user competency we modified and didn't catch.
1557          $insql = ' > 0';
1558          $inparams = [];
1559          if (!empty($competencies)) {
1560              list($insql, $inparams) = $DB->get_in_or_equal(array_keys($competencies), SQL_PARAMS_NAMED, 'param', false);
1561          }
1562          $sql = "
1563              SELECT $ucfields, $cfields
1564                FROM {" . user_competency::TABLE . "} uc
1565                JOIN {" . competency::TABLE . "} c
1566                  ON c.id = uc.competencyid
1567           LEFT JOIN {comments} cmt
1568                  ON cmt.contextid = :contextid
1569                 AND cmt.commentarea = :ucarea
1570                 AND cmt.component = :competency
1571                 AND cmt.itemid = uc.id
1572               WHERE uc.userid = :targetuserid
1573                 AND (uc.usermodified = :userid1
1574                  OR uc.reviewerid = :userid2
1575                  OR cmt.userid = :userid3)
1576                 AND uc.competencyid $insql
1577            ORDER BY c.id, uc.id";
1578          $params = array_merge($inparams, [
1579              'targetuserid' => $context->instanceid,
1580              'userid1' => $userid,
1581              'userid2' => $userid,
1582              'userid3' => $userid,
1583              'contextid' => $context->id,
1584              'ucarea' => 'user_competency',
1585              'competency' => 'competency',
1586          ]);
1587  
1588          $recordset = $DB->get_recordset_sql($sql, $params);
1589          foreach ($recordset as $record) {
1590              $competency = new competency(null, competency::extract_record($record, 'c_'));
1591              if (!isset($competencies[$competency->get('id')])) {
1592                  $initcompetency($record);
1593                  $initusercomp($competency, $record);
1594              }
1595          }
1596          $recordset->close();
1597  
1598          // Export each competency on its own.
1599          foreach ($competencies as $competencyid => $competency) {
1600              $comppath = array_merge($path, ["{$competency['name']} ({$competencyid})"]);
1601              $ucid = isset($competency['uc_id']) ? $competency['uc_id'] : null;
1602              unset($competency['uc_id']);
1603  
1604              // Send to writer.
1605              writer::with_context($context)->export_data($comppath, (object) $competency);
1606              if ($ucid) {
1607                  \core_comment\privacy\provider::export_comments($context, 'competency', 'user_competency', $ucid, $comppath, true);
1608              }
1609          }
1610      }
1611  
1612      /**
1613       * Export a user's data related to evidence of prior learning.
1614       *
1615       * @param int $userid The user ID we're exporting for.
1616       * @param context_user $context The context of the user in which we're gathering data.
1617       * @return void
1618       */
1619      protected static function export_user_data_user_evidence_related_to_me($userid, context_user $context) {
1620          global $DB;
1621  
1622          $path = [
1623              get_string('competencies', 'core_competency'),
1624              get_string('privacy:path:relatedtome', 'core_competency'),
1625              get_string('privacy:path:userevidence', 'core_competency'),
1626          ];
1627          $evidence = [];
1628          $helper = new performance_helper();
1629          $cfields = competency::get_sql_fields('c', 'c_');
1630          $uecfields = user_evidence_competency::get_sql_fields('uec', 'uec_');
1631          $uefields = user_evidence::get_sql_fields('ue', 'ue_');
1632  
1633          $initevidence = function($record) use (&$evidence, $userid) {
1634              $ue = new user_evidence(null, user_evidence::extract_record($record, 'ue_'));
1635              $evidence[$ue->get('id')] = static::transform_user_evidence($userid, $ue);
1636          };
1637  
1638          // Look for evidence.
1639          $sql = "
1640              SELECT $uefields, $uecfields, $cfields
1641                FROM {" . user_evidence_competency::TABLE . "} uec
1642                JOIN {" . user_evidence::TABLE . "} ue
1643                  ON ue.id = uec.userevidenceid
1644                JOIN {" . competency::TABLE . "} c
1645                  ON c.id = uec.competencyid
1646               WHERE ue.userid = :targetuserid
1647                 AND uec.usermodified = :userid
1648            ORDER BY ue.id, c.id";
1649          $params = [
1650              'targetuserid' => $context->instanceid,
1651              'userid' => $userid,
1652          ];
1653          $recordset = $DB->get_recordset_sql($sql, $params);
1654          foreach ($recordset as $record) {
1655              $ueid = $record->ue_id;
1656              if (!isset($evidence[$ueid])) {
1657                  $initevidence($record);
1658              }
1659  
1660              $competency = new competency(null, competency::extract_record($record, 'c_'));
1661              $uec = new user_evidence_competency(null, user_evidence_competency::extract_record($record, 'uec_'));
1662              $evidence[$ueid]['competencies'][] = array_merge(static::transform_competency_brief($competency), [
1663                  'timemodified' => $uec->get('timemodified') ? transform::datetime($uec->get('timemodified')) : '-',
1664                  'timecreated' => $uec->get('timecreated') ? transform::datetime($uec->get('timecreated')) : '-',
1665                  'created_or_modified_by_you' => transform::yesno($uec->get('usermodified'))
1666              ]);
1667          }
1668          $recordset->close();
1669  
1670          // Look for user evidence we modified or reviewed and didn't catch.
1671          $insql = ' > 0';
1672          $inparams = [];
1673          if (!empty($evidence)) {
1674              list($insql, $inparams) = $DB->get_in_or_equal(array_keys($evidence), SQL_PARAMS_NAMED, 'param', false);
1675          }
1676          $sql = "
1677              SELECT $uefields
1678                FROM {" . user_evidence::TABLE . "} ue
1679               WHERE ue.userid = :targetuserid
1680                 AND ue.usermodified = :userid
1681                 AND ue.id $insql
1682            ORDER BY ue.id";
1683          $params = array_merge($inparams, [
1684              'targetuserid' => $context->instanceid,
1685              'userid' => $userid,
1686          ]);
1687  
1688          $recordset = $DB->get_recordset_sql($sql, $params);
1689          foreach ($recordset as $record) {
1690              $initevidence($record);
1691          }
1692          $recordset->close();
1693  
1694          // Export files, then content.
1695          foreach ($evidence as $ueid => $data) {
1696              $uepath = array_merge($path, ["{$data['name']} ({$ueid})"]);
1697              writer::with_context($context)->export_area_files($uepath, 'core_competency', 'userevidence', $ueid);
1698              writer::with_context($context)->export_data($uepath, (object) $data);
1699          }
1700      }
1701  
1702      /**
1703       * Export the evidence of prior learning of a user.
1704       *
1705       * @param context_user $context The context of the user we're exporting for.
1706       * @return void
1707       */
1708      protected static function export_user_data_user_evidence(context_user $context) {
1709          global $DB;
1710  
1711          $userid = $context->instanceid;
1712          $path = [get_string('competencies', 'core_competency'), get_string('privacy:path:userevidence', 'core_competency')];
1713          $uefields = user_evidence::get_sql_fields('ue', 'ue_');
1714          $cfields = competency::get_sql_fields('c', 'c_');
1715  
1716          $sql = "
1717              SELECT $uefields, $cfields
1718                FROM {" . user_evidence::TABLE . "} ue
1719           LEFT JOIN {" . user_evidence_competency::TABLE . "} uec
1720                  ON uec.userevidenceid = ue.id
1721           LEFT JOIN {" . competency::TABLE . "} c
1722                  ON c.id = uec.competencyid
1723               WHERE ue.userid = :userid
1724            ORDER BY ue.id";
1725          $params = ['userid' => $userid];
1726  
1727          $recordset = $DB->get_recordset_sql($sql, $params);
1728          static::recordset_loop_and_export($recordset, 'ue_id', null, function($carry, $record) use ($userid, $context){
1729              if ($carry === null) {
1730                  $ue = new user_evidence(null, user_evidence::extract_record($record, 'ue_'));
1731                  $carry = static::transform_user_evidence($userid, $ue);
1732              }
1733  
1734              if (!empty($record->c_id)) {
1735                  $competency = new competency(null, competency::extract_record($record, 'c_'));
1736                  $carry['competencies'][] = static::transform_competency_brief($competency);
1737              }
1738  
1739              return $carry;
1740          }, function($ueid, $data) use ($context, $path) {
1741              $finalpath = array_merge($path, [$data['name'] . ' (' . $ueid . ')']);
1742              writer::with_context($context)->export_area_files($finalpath, 'core_competency', 'userevidence', $ueid);
1743              writer::with_context($context)->export_data($finalpath, (object) $data);
1744          });
1745      }
1746  
1747      /**
1748       * Export the user data related to frameworks in context.
1749       *
1750       * @param int $userid The user ID.
1751       * @param context $context The context.
1752       * @return void
1753       */
1754      protected static function export_user_data_frameworks_in_context($userid, context $context) {
1755          global $DB;
1756  
1757          $ffields = competency_framework::get_sql_fields('f', 'f_');
1758          $cfields = competency::get_sql_fields('c', 'c_');
1759          $c2fields = competency::get_sql_fields('c2', 'c2_');
1760          $rcfields = related_competency::get_sql_fields('rc', 'rc_');
1761  
1762          $frameworks = [];
1763          $initframework = function($record) use (&$frameworks, $userid) {
1764              $framework = new competency_framework(null, competency_framework::extract_record($record, 'f_'));
1765              $frameworks[$framework->get('id')] = array_merge(static::transform_framework_brief($framework), [
1766                  'timemodified' => transform::datetime($framework->get('timemodified')),
1767                  'created_or_modified_by_you' => transform::yesno($framework->get('usermodified') == $userid),
1768                  'competencies' => []
1769              ]);
1770          };
1771          $initcompetency = function($record, $prefix) use (&$frameworks, $userid) {
1772              $competency = new competency(null, competency::extract_record($record, $prefix));
1773              $frameworks[$competency->get('competencyframeworkid')]['competencies'][$competency->get('id')] = array_merge(
1774                  static::transform_competency_brief($competency),
1775                  [
1776                      'timemodified' => transform::datetime($competency->get('timemodified')),
1777                      'created_or_modified_by_you' => transform::yesno($competency->get('usermodified') == $userid),
1778                      'related_competencies' => []
1779                  ]
1780              );
1781          };
1782  
1783          // Start by looking for related competencies.
1784          $sql = "
1785              SELECT $ffields, $cfields, $c2fields, $rcfields
1786                FROM {" . related_competency::TABLE . "} rc
1787                JOIN {" . competency::TABLE . "} c
1788                  ON c.id = rc.competencyid
1789                JOIN {" . competency::TABLE . "} c2
1790                  ON c2.id = rc.relatedcompetencyid
1791                JOIN {" . competency_framework::TABLE . "} f
1792                  ON f.id = c.competencyframeworkid
1793               WHERE rc.usermodified = :userid
1794                 AND f.contextid = :contextid
1795            ORDER BY rc.id, c.id";
1796          $params = ['userid' => $userid, 'contextid' => $context->id];
1797  
1798          $recordset = $DB->get_recordset_sql($sql, $params);
1799          foreach ($recordset as $record) {
1800              $frameworkid = $record->f_id;
1801              $comp1id = $record->c_id;
1802              $comp2id = $record->c2_id;
1803  
1804              if (!isset($frameworks[$frameworkid])) {
1805                  $initframework($record);
1806              }
1807  
1808              foreach (['c_', 'c2_'] as $key) {
1809                  $competencyid = $record->{$key . 'id'};
1810                  if (!isset($frameworks[$frameworkid]['competencies'][$competencyid])) {
1811                      $initcompetency($record, $key);
1812                  }
1813              }
1814  
1815              $relcomp = new related_competency(null, related_competency::extract_record($record, 'rc_'));
1816              foreach (['c_' => 'c2_', 'c2_' => 'c_'] as $key => $relatedkey) {
1817                  $competencyid = $record->{$key . 'id'};
1818                  $competency = new competency(null, competency::extract_record($record, $relatedkey));
1819                  $frameworks[$frameworkid]['competencies'][$competencyid]['related_competencies'][] = [
1820                      'name' => $competency->get('shortname'),
1821                      'idnumber' => $competency->get('idnumber'),
1822                      'timemodified' => transform::datetime($relcomp->get('timemodified')),
1823                      'created_or_modified_by_you' => transform::yesno($relcomp->get('usermodified') == $userid),
1824                  ];
1825              }
1826          }
1827          $recordset->close();
1828  
1829          // Now look for competencies, but skip the ones we've already seen.
1830          $competencyids = array_reduce($frameworks, function($carry, $framework) {
1831              return array_merge($carry, array_keys($framework['competencies']));
1832          }, []);
1833          $insql = ' IS NOT NULL';
1834          $inparams = [];
1835          if (!empty($competencyids)) {
1836              list($insql, $inparams) = $DB->get_in_or_equal($competencyids, SQL_PARAMS_NAMED, 'param', false);
1837          }
1838          $sql = "
1839              SELECT $ffields, $cfields
1840                FROM {" . competency::TABLE . "} c
1841                JOIN {" . competency_framework::TABLE . "} f
1842                  ON f.id = c.competencyframeworkid
1843               WHERE c.usermodified = :userid
1844                 AND f.contextid = :contextid
1845                 AND c.id $insql
1846            ORDER BY c.id";
1847          $params = array_merge($inparams, ['userid' => $userid, 'contextid' => $context->id]);
1848          $recordset = $DB->get_recordset_sql($sql, $params);
1849          foreach ($recordset as $record) {
1850              $frameworkid = $record->f_id;
1851              if (!isset($frameworks[$frameworkid])) {
1852                  $initframework($record);
1853              }
1854              $initcompetency($record, 'c_');
1855          }
1856          $recordset->close();
1857  
1858          // Now look for frameworks, but skip the ones we've already seen.
1859          $frameworkids = array_keys($frameworks);
1860          $insql = ' IS NOT NULL';
1861          $inparams = [];
1862          if (!empty($frameworkids)) {
1863              list($insql, $inparams) = $DB->get_in_or_equal($frameworkids, SQL_PARAMS_NAMED, 'param', false);
1864          }
1865          $sql = "
1866              SELECT $ffields
1867                FROM {" . competency_framework::TABLE . "} f
1868               WHERE f.usermodified = :userid
1869                 AND f.contextid = :contextid
1870                 AND f.id $insql
1871            ORDER BY f.id";
1872          $params = array_merge($inparams, ['userid' => $userid, 'contextid' => $context->id]);
1873          $recordset = $DB->get_recordset_sql($sql, $params);
1874          foreach ($recordset as $record) {
1875              context_helper::preload_from_record($record);
1876              $initframework($record);
1877          }
1878          $recordset->close();
1879  
1880          // Export all the things!
1881          writer::with_context($context)->export_related_data(
1882              [get_string('competencies', 'core_competency')],
1883              'frameworks',
1884              (object) [
1885                  // Drop the temporary IDs.
1886                  'frameworks' => array_reduce($frameworks, function($carry, $item) {
1887                      $item['competencies'] = array_values($item['competencies']);
1888                      $carry[] = $item;
1889                      return $carry;
1890                  }, [])
1891              ]
1892          );
1893      }
1894  
1895      /**
1896       * Export the user data related to templates in contexts.
1897       *
1898       * @param int $userid The user ID.
1899       * @param context $context The context.
1900       * @return void
1901       */
1902      protected static function export_user_data_templates_in_context($userid, context $context) {
1903          global $DB;
1904  
1905          $tfields = template::get_sql_fields('t', 't_');
1906          $cfields = competency::get_sql_fields('c', 'c_');
1907          $tcfields = template_competency::get_sql_fields('tc', 'tc_');
1908          $tchfields = template_cohort::get_sql_fields('tch', 'tch_');
1909  
1910          $templates = [];
1911          $inittemplate = function($record) use (&$templates, $userid) {
1912              $template = new template(null, template::extract_record($record, 't_'));
1913              $templates[$template->get('id')] = array_merge(static::transform_template_brief($template), [
1914                  'timemodified' => transform::datetime($template->get('timemodified')),
1915                  'created_or_modified_by_you' => transform::yesno($template->get('usermodified') == $userid),
1916                  'competencies' => [],
1917                  'cohorts' => []
1918              ]);
1919          };
1920  
1921          // Find the template competencies.
1922          $sql = "
1923              SELECT $tfields, $cfields, $tcfields
1924                FROM {" . template_competency::TABLE . "} tc
1925                JOIN {" . template::TABLE . "} t
1926                  ON t.id = tc.templateid
1927                JOIN {" . competency::TABLE . "} c
1928                  ON c.id = tc.competencyid
1929               WHERE t.contextid = :contextid
1930                 AND tc.usermodified = :userid
1931            ORDER BY t.id, tc.id";
1932          $params = ['userid' => $userid, 'contextid' => $context->id];
1933          $recordset = $DB->get_recordset_sql($sql, $params);
1934          foreach ($recordset as $record) {
1935              $templateid = $record->t_id;
1936              if (!isset($templates[$templateid])) {
1937                  $inittemplate($record);
1938              }
1939              $tplcomp = new template_competency(null, template_competency::extract_record($record, 'tc_'));
1940              $competency = new competency(null, competency::extract_record($record, 'c_'));
1941              $templates[$templateid]['competencies'][] = array_merge(
1942                  static::transform_competency_brief($competency),
1943                  [
1944                      'timemodified' => transform::datetime($tplcomp->get('timemodified')),
1945                      'created_or_modified_by_you' => transform::yesno($tplcomp->get('usermodified') == $userid)
1946                  ]
1947              );
1948          }
1949          $recordset->close();
1950  
1951          // Find the template cohorts.
1952          $sql = "
1953              SELECT $tfields, $tchfields, c.name AS cohortname
1954                FROM {" . template_cohort::TABLE . "} tch
1955                JOIN {" . template::TABLE . "} t
1956                  ON t.id = tch.templateid
1957                JOIN {cohort} c
1958                  ON c.id = tch.cohortid
1959               WHERE t.contextid = :contextid
1960                 AND tch.usermodified = :userid
1961            ORDER BY t.id, tch.id";
1962          $params = ['userid' => $userid, 'contextid' => $context->id];
1963          $recordset = $DB->get_recordset_sql($sql, $params);
1964          foreach ($recordset as $record) {
1965              $templateid = $record->t_id;
1966              if (!isset($templates[$templateid])) {
1967                  $inittemplate($record);
1968              }
1969              $tplcohort = new template_cohort(null, template_cohort::extract_record($record, 'tch_'));
1970              $templates[$templateid]['cohorts'][] = [
1971                  'name' => $record->cohortname,
1972                  'timemodified' => transform::datetime($tplcohort->get('timemodified')),
1973                  'created_or_modified_by_you' => transform::yesno($tplcohort->get('usermodified') == $userid)
1974              ];
1975          }
1976          $recordset->close();
1977  
1978          // Find the modified templates which we haven't been found yet.
1979          $templateids = array_keys($templates);
1980          $insql = "IS NOT NULL";
1981          $inparams = [];
1982          if (!empty($templateids)) {
1983              list($insql, $inparams) = $DB->get_in_or_equal($templateids, SQL_PARAMS_NAMED, 'param', false);
1984          }
1985          $sql = "
1986              SELECT $tfields
1987                FROM {" . template::TABLE . "} t
1988               WHERE t.contextid = :contextid
1989                 AND t.usermodified = :userid
1990                 AND t.id $insql
1991            ORDER BY t.id";
1992          $params = array_merge($inparams, ['userid' => $userid, 'contextid' => $context->id]);
1993          $recordset = $DB->get_recordset_sql($sql, $params);
1994          foreach ($recordset as $record) {
1995              $inittemplate($record);
1996          }
1997          $recordset->close();
1998  
1999          // Export all the things!
2000          writer::with_context($context)->export_related_data([get_string('competencies', 'core_competency')],
2001              'templates', (object) ['templates' => array_values($templates)]);
2002      }
2003  
2004      /**
2005       * Transform a competency into a brief description.
2006       *
2007       * @param competency $competency The competency.
2008       * @return array
2009       */
2010      protected static function transform_competency_brief(competency $competency) {
2011          global $OUTPUT;
2012          $exporter = new \core_competency\external\competency_exporter($competency, ['context' => $competency->get_context()]);
2013          $data = $exporter->export($OUTPUT);
2014          return [
2015              'idnumber' => $data->idnumber,
2016              'name' => $data->shortname,
2017              'description' => $data->description
2018          ];
2019      }
2020  
2021      /**
2022       * Transform a competency rating.
2023       *
2024       * @param competency $competency The competency.
2025       * @param int $grade The grade.
2026       * @param performance_helper $helper The performance helper.
2027       * @return string
2028       */
2029      protected static function transform_competency_grade(competency $competency, $grade, performance_helper $helper) {
2030          if ($grade === null) {
2031              return '-';
2032          }
2033          $scale = $helper->get_scale_from_competency($competency);
2034          return $scale->scale_items[$grade - 1];
2035      }
2036  
2037      /**
2038       * Transform an evidence.
2039       *
2040       * @param int $userid The user ID we are exporting for.
2041       * @param evidence $evidence The evidence.
2042       * @param competency $competency The associated competency.
2043       * @param performance_helper $helper The performance helper.
2044       * @return array
2045       */
2046      protected static function transform_evidence($userid, evidence $evidence, competency $competency, performance_helper $helper) {
2047          $action = $evidence->get('action');
2048          $actiontxt = '?';
2049          if ($action == evidence::ACTION_LOG) {
2050              $actiontxt = get_string('privacy:evidence:action:log', 'core_competency');
2051          } else if ($action == evidence::ACTION_COMPLETE) {
2052              $actiontxt = get_string('privacy:evidence:action:complete', 'core_competency');
2053          } else if ($action == evidence::ACTION_OVERRIDE) {
2054              $actiontxt = get_string('privacy:evidence:action:override', 'core_competency');
2055          }
2056  
2057          $actionuserid = $evidence->get('actionuserid');
2058  
2059          return [
2060              'action' => $actiontxt,
2061              'actionuserid' => $actionuserid ? transform::user($actionuserid) : '-',
2062              'acting_user_is_you' => transform::yesno($userid == $actionuserid),
2063              'description' => (string) $evidence->get_description(),
2064              'url' => $evidence->get('url'),
2065              'grade' => static::transform_competency_grade($competency, $evidence->get('grade'), $helper),
2066              'note' => $evidence->get('note'),
2067              'timecreated' => transform::datetime($evidence->get('timecreated')),
2068              'timemodified' => transform::datetime($evidence->get('timemodified')),
2069              'created_or_modified_by_you' => transform::yesno($userid == $evidence->get('usermodified'))
2070          ];
2071      }
2072  
2073      /**
2074       * Transform a framework into a brief description.
2075       *
2076       * @param competency_framework $framework The framework.
2077       * @return array
2078       */
2079      protected static function transform_framework_brief(competency_framework $framework) {
2080          global $OUTPUT;
2081          $exporter = new \core_competency\external\competency_framework_exporter($framework);
2082          $data = $exporter->export($OUTPUT);
2083          return [
2084              'name' => $data->shortname,
2085              'idnumber' => $data->idnumber,
2086              'description' => $data->description
2087          ];
2088      }
2089  
2090      /**
2091       * Transform a template into a brief description.
2092       *
2093       * @param template $template The Template.
2094       * @return array
2095       */
2096      protected static function transform_template_brief(template $template) {
2097          global $OUTPUT;
2098          $exporter = new \core_competency\external\template_exporter($template);
2099          $data = $exporter->export($OUTPUT);
2100          return [
2101              'name' => $data->shortname,
2102              'description' => $data->description
2103          ];
2104      }
2105  
2106      /**
2107       * Transform proficiency.
2108       *
2109       * @param null|bool $proficiency The proficiency.
2110       * @return string
2111       */
2112      protected static function transform_proficiency($proficiency) {
2113          return $proficiency !== null ? transform::yesno($proficiency) : '-';
2114      }
2115  
2116      /**
2117       * Transform user competency.
2118       *
2119       * @param int $userid The user ID we are exporting for.
2120       * @param user_competency|user_competency_plan|user_competency_course $uc The user competency.
2121       * @param competency $competency The associated competency.
2122       * @param performance_helper $helper The performance helper.
2123       * @return array
2124       */
2125      protected static function transform_user_competency($userid, $uc, competency $competency, performance_helper $helper) {
2126          $data = [
2127              'proficient' => static::transform_proficiency($uc->get('proficiency')),
2128              'rating' => static::transform_competency_grade($competency, $uc->get('grade'), $helper),
2129              'timemodified' => $uc->get('timemodified') ? transform::datetime($uc->get('timemodified')) : '-',
2130              'timecreated' => $uc->get('timecreated') ? transform::datetime($uc->get('timecreated')) : '-',
2131              'created_or_modified_by_you' => transform::yesno($uc->get('usermodified') == $userid),
2132          ];
2133  
2134          if ($uc instanceof user_competency) {
2135              $reviewer = $uc->get('reviewerid');
2136              $data['status'] = (string) user_competency::get_status_name($uc->get('status'));
2137              $data['reviewerid'] = $reviewer ? transform::user($reviewer) : '-';
2138              $data['reviewer_is_you'] = transform::yesno($reviewer == $userid);
2139          }
2140  
2141          return $data;
2142      }
2143  
2144      /**
2145       * Transform a user evidence.
2146       *
2147       * @param int $userid The user we are exporting for.
2148       * @param user_evidence $ue The evidence of prior learning.
2149       * @return array
2150       */
2151      protected static function transform_user_evidence($userid, user_evidence $ue) {
2152          $options = ['context' => $ue->get_context()];
2153          return [
2154              'name' => format_string($ue->get('name'), true, $options),
2155              'description' => format_text($ue->get('description'), $ue->get('descriptionformat'), $options),
2156              'url' => $ue->get('url'),
2157              'timecreated' => $ue->get('timecreated') ? transform::datetime($ue->get('timecreated')) : '-',
2158              'timemodified' => $ue->get('timemodified') ? transform::datetime($ue->get('timemodified')) : '-',
2159              'created_or_modified_by_you' => transform::yesno($ue->get('usermodified') == $userid),
2160              'competencies' => []
2161          ];
2162      }
2163  
2164      /**
2165       * Loop and export from a recordset.
2166       *
2167       * @param moodle_recordset $recordset The recordset.
2168       * @param string $splitkey The record key to determine when to export.
2169       * @param mixed $initial The initial data to reduce from.
2170       * @param callable $reducer The function to return the dataset, receives current dataset, and the current record.
2171       * @param callable $export The function to export the dataset, receives the last value from $splitkey and the dataset.
2172       * @return void
2173       */
2174      protected static function recordset_loop_and_export(moodle_recordset $recordset, $splitkey, $initial,
2175              callable $reducer, callable $export) {
2176  
2177          $data = $initial;
2178          $lastid = null;
2179  
2180          foreach ($recordset as $record) {
2181              if ($lastid && $record->{$splitkey} != $lastid) {
2182                  $export($lastid, $data);
2183                  $data = $initial;
2184              }
2185              $data = $reducer($data, $record);
2186              $lastid = $record->{$splitkey};
2187          }
2188          $recordset->close();
2189  
2190          if (!empty($lastid)) {
2191              $export($lastid, $data);
2192          }
2193      }
2194  }