{
  "title": "lang::report_1_title::local_kopere_bi",
  "description": "lang::report_1_description::local_kopere_bi",
  "user_id": null,
  "refkey": "active_students",
  "pre_requisit": "mysql",
  "category": {
    "refkey": "students",
    "title": "lang::report_1_cat_title::local_kopere_bi",
    "description": "lang::report_1_cat_description::local_kopere_bi"
  },
  "blocks": [
    {
      "refkey": "students-1",
      "type": "block-1",
      "sequence": 1,
      "elements": [
        {
          "title": "lang::report_1_user_summary::local_kopere_bi",
          "refkey": "report_1_user_summary",
          "block_num": 1,
          "type": "table",
          "theme": "light",
          "css": "",
          "html_before": null,
          "html_after": null,
          "commandsql": "SELECT \r\n    u.id AS u_id,\r\n    u.firstname,\r\n    u.lastname,\r\n    c.id AS c_id,\r\n    c.fullname AS c_fullname,\r\n    u.email,\r\n    u.lastaccess,\r\n    u.timecreated,\r\n    ROUND(\r\n        AVG(\r\n            CASE\r\n                WHEN g.rawgrademax > 0\r\n                THEN (g.finalgrade \/ g.rawgrademax) * 100\r\n                ELSE g.finalgrade \r\n            END\r\n        ), 0\r\n    ) AS g_finalgrade,\r\n    MAX(ca.completed_activities) AS ca_completed_activities\r\nFROM \r\n    mdl_course c\r\nJOIN \r\n    mdl_context ctx ON ctx.instanceid = c.id\r\nJOIN \r\n    mdl_role_assignments ra ON ra.contextid = ctx.id\r\n                            AND ra.roleid = 5\r\nJOIN \r\n    mdl_user u ON u.id = ra.userid\r\nLEFT JOIN (\r\n        SELECT \r\n            COUNT(id) AS completed_activities, \r\n            userid\r\n        FROM \r\n            mdl_course_modules_completion\r\n        WHERE \r\n            id > 1\r\n            AND completionstate IN (1, 2)\r\n        GROUP BY \r\n            userid\r\n    ) ca ON ca.userid = u.id\r\nLEFT JOIN \r\n    mdl_grade_items gi ON gi.courseid = c.id \r\n                       AND gi.itemtype = 'course'\r\nLEFT JOIN \r\n    mdl_grade_grades g ON g.itemid = gi.id \r\n                      AND g.userid = u.id \r\n                      AND g.finalgrade IS NOT NULL\r\nWHERE u.id        > 1\r\n  AND u.deleted   = 0\r\n  AND u.suspended = 0\r\n  AND u.username != 'guest'\r\nGROUP BY u.id",
          "cache": "30m",
          "reload": "30m",
          "info": "{\"column\":{\"u_id\":{\"key\":\"u_id\",\"title\":\"#\",\"type\":\"none\",\"mustache\":\"\"},\"firstname\":{\"key\":\"firstname\",\"title\":\"lang::u_fullname::local_kopere_bi\",\"type\":\"userfullname\",\"mustache\":\"<a href=\\\"{{{config.wwwroot}}}\\\/user\\\/view.php?id={{{u_id}}}\\\"\\r\\n   target=\\\"profile\\\">{{{u_fullname}}}<\\\/a>\"},\"lastname\":{\"key\":\"lastname\",\"title\":\"lang::lastname::moodle\",\"type\":\"none\",\"mustache\":\"\"},\"c_id\":{\"key\":\"c_id\",\"title\":\"c_id\",\"type\":\"none\",\"mustache\":\"\"},\"c_fullname\":{\"key\":\"c_fullname\",\"title\":\"lang::c_fullname::local_kopere_bi\",\"type\":\"string\",\"mustache\":\"<a href=\\\"{{{config.wwwroot}}}\\\/course\\\/view.php?id={{{c_id}}}\\\"\\r\\n   target=\\\"course\\\">{{{c_fullname}}}<\\\/a>\"},\"email\":{\"key\":\"email\",\"title\":\"lang::email::moodle\",\"type\":\"string\",\"mustache\":\"<a href=\\\"mailto:{{{email}}}\\\" target=\\\"_blank\\\">{{{email}}}<\\\/a>\"},\"lastaccess\":{\"key\":\"lastaccess\",\"title\":\"lang::lastaccess::moodle\",\"type\":\"datetimeRenderer\",\"mustache\":\"\"},\"timecreated\":{\"key\":\"timecreated\",\"title\":\"lang::timecreated::moodle\",\"type\":\"datetimeRenderer\",\"mustache\":\"\"},\"g_finalgrade\":{\"key\":\"g_finalgrade\",\"title\":\"lang::g_finalgrade::local_kopere_bi\",\"type\":\"number\",\"mustache\":\"\"},\"ca_completed_activities\":{\"key\":\"ca_completed_activities\",\"title\":\"lang::ca_completed_activities::local_kopere_bi\",\"type\":\"number\",\"mustache\":\"\"}}}"
        }
      ]
    }
  ]
}
