{
  "title": "lang::report_4_title::local_kopere_bi",
  "description": "lang::report_4_description::local_kopere_bi",
  "refkey": "statistics",
  "category": {
    "refkey": "enrollments",
    "title": "lang::report_4_cat_title::local_kopere_bi",
    "sortorder": 7,
    "description": "lang::report_4_cat_description::local_kopere_bi"
  },
  "blocks": [
    {
      "refkey": "enrollments-2",
      "type": "block-1",
      "sequence": 2,
      "elements": [
        {
          "pre_requisit": "mysql",
          "title": "lang::report_4_coursesenrollmentstatus::local_kopere_bi",
          "refkey": "report_4_coursesenrollmentstatus",
          "block_num": 1,
          "type": "table",
          "theme": "light",
          "css": "",
          "html_before": "",
          "html_after": "",
          "commandsql": "SELECT \r\n    u.id AS u_id,\r\n    u.firstname,\r\n    u.lastname,\r\n    u.email,\r\n    u.city,\r\n    u.country,\r\n    c.id       AS c_id,\r\n    c.fullname AS c_fullname,\r\n    cm.id      AS cm_cmid,\r\n    m.name AS module_name,\r\n    ROUND(\r\n        CASE\r\n            WHEN g.rawgrademax > 0 THEN (g.finalgrade \/ g.rawgrademax) * 100\r\n            ELSE g.finalgrade\r\n        END, 0\r\n    ) AS grade,\r\n    CASE\r\n        WHEN cmc.completionstate = 0 THEN 'lang::report_4_completionstate_0::local_kopere_bi'\r\n        WHEN cmc.completionstate = 1 THEN 'lang::report_4_completionstate_1::local_kopere_bi'\r\n        WHEN cmc.completionstate = 2 THEN 'lang::report_4_completionstate_2::local_kopere_bi'\r\n        WHEN cmc.completionstate = 3 THEN 'lang::report_4_completionstate_3::local_kopere_bi'\r\n        WHEN cmc.completionstate = 4 THEN 'lang::report_4_completionstate_4::local_kopere_bi'\r\n        ELSE 'lang::report_4_completionstate_none::local_kopere_bi'\r\n    END AS completionstate_status,\r\n    CASE\r\n        WHEN cm.completion = 0 THEN 'lang::report_4_completion_0::local_kopere_bi'\r\n        WHEN cm.completion = 1 THEN 'lang::report_4_completion_1::local_kopere_bi'\r\n        WHEN cm.completion = 2 THEN 'lang::report_4_completion_2::local_kopere_bi'\r\n        ELSE 'lang::report_4_completion_none::local_kopere_bi'\r\n    END AS completion_status,\r\n    ue.enrol_status AS enrol_status,\r\n    ue.timeend       AS enrol_timeend\r\nFROM (\r\n    SELECT MIN(ue1.id) AS id, \r\n           ue1.userid, \r\n           e1.courseid, \r\n           MIN(ue1.status) AS enrol_status, \r\n           MIN(ue1.timeend) AS timeend\r\n    FROM mdl_user_enrolments ue1\r\n    JOIN mdl_enrol e1 ON e1.id = ue1.enrolid\r\n    GROUP BY ue1.userid, e1.courseid\r\n) ue\r\nJOIN mdl_user u \r\n         ON u.id = ue.userid\r\nJOIN mdl_course c \r\n         ON c.id = ue.courseid\r\nJOIN mdl_course_modules cm \r\n         ON cm.course = c.id\r\n        AND cm.instance > 0\r\nJOIN mdl_modules m \r\n         ON m.id = cm.module\r\nLEFT JOIN mdl_grade_items gi \r\n         ON gi.itemtype = 'mod'\r\n        AND gi.iteminstance = cm.instance\r\n        AND gi.itemmodule = m.name\r\n        AND gi.gradetype = 1\r\nLEFT JOIN mdl_grade_grades g \r\n         ON g.itemid = gi.id\r\n        AND g.userid = u.id\r\nLEFT JOIN mdl_course_modules_completion cmc \r\n         ON cmc.coursemoduleid = cm.id\r\n        AND cmc.userid = u.id\r\n\r\nWHERE c.id = :courseid\r\n  AND u.deleted = 0\r\n  AND u.suspended = 0\r\n  AND c.visible = 1\r\n  AND cm.visible = 1",
          "cache": "30m",
          "reload": "30m",
          "info": "{\n    \"column\": {\n        \"u_id\": {\n            \"key\": \"u_id\",\n            \"title\": \"#\",\n            \"type\": \"userphotoRenderer\",\n            \"mustache\": \"<a href=\\\"{{{config.wwwroot}}}\\\/user\\\/view.php?id={{{u_id}}}\\\"\\r\\n   target=\\\"profile\\\">{{{u_fullname}}}<\\\/a>\"\n        },\n        \"firstname\": {\n            \"key\": \"firstname\",\n            \"title\": \"lang::u_fullname::local_kopere_bi\",\n            \"type\": \"userfullname\",\n            \"mustache\": \"<a href=\\\"{{{config.wwwroot}}}\\\/user\\\/view.php?id={{{u_id}}}\\\"\\r\\n   target=\\\"profile\\\">{{{u_fullname}}}<\\\/a>\"\n        },\n        \"lastname\": {\n            \"key\": \"lastname\",\n            \"title\": \"\",\n            \"type\": \"none\",\n            \"mustache\": \"\"\n        },\n        \"email\": {\n            \"key\": \"email\",\n            \"title\": \"lang::email::moodle\",\n            \"type\": \"string\",\n            \"mustache\": \"<a href=\\\"mailto:{{{email}}}\\\" target=\\\"_blank\\\">{{{email}}}<\\\/a>\"\n        },\n        \"city\": {\n            \"key\": \"city\",\n            \"title\": \"lang::city_name::local_kopere_bi\",\n            \"type\": \"none\",\n            \"mustache\": \"\"\n        },\n        \"country\": {\n            \"key\": \"country\",\n            \"title\": \"lang::country_name::local_kopere_bi\",\n            \"type\": \"none\",\n            \"mustache\": \"\"\n        },\n        \"c_id\": {\n            \"key\": \"c_id\",\n            \"title\": \"c_id\",\n            \"type\": \"none\",\n            \"mustache\": \"\"\n        },\n        \"c_fullname\": {\n            \"key\": \"c_fullname\",\n            \"title\": \"lang::c_fullname::local_kopere_bi\",\n            \"type\": \"string\",\n            \"mustache\": \"<a href=\\\"{{{config.wwwroot}}}\\\/course\\\/view.php?id={{{c_id}}}\\\"\\r\\n   target=\\\"course\\\">{{{c_fullname}}}<\\\/a>\"\n        },\n        \"cm_cmid\": {\n            \"key\": \"cm_cmid\",\n            \"title\": \"cm_cmid\",\n            \"type\": \"none\",\n            \"mustache\": \"{{{cm_cmid}}}\"\n        },\n        \"module_name\": {\n            \"key\": \"module_name\",\n            \"title\": \"lang::module_name::local_kopere_bi\",\n            \"type\": \"string\",\n            \"mustache\": \"<a href=\\\"{{{config.wwwroot}}}\\\/mod\\\/{{{module_name}}}\\\/view.php?id={{{cm_cmid}}}\\\">\\r\\n    {{#str}} modulename,{{{module_name}}} {{\\\/str}}\\r\\n<\\\/a>\"\n        },\n        \"grade\": {\n            \"key\": \"grade\",\n            \"title\": \"lang::grade::local_kopere_bi\",\n            \"type\": \"number\",\n            \"mustache\": \"\"\n        },\n        \"completionstate_status\": {\n            \"key\": \"completionstate_status\",\n            \"title\": \"lang::completionstate_status::local_kopere_bi\",\n            \"type\": \"translate\",\n            \"mustache\": \"\"\n        },\n        \"completion_status\": {\n            \"key\": \"completion_status\",\n            \"title\": \"lang::completion_status::local_kopere_bi\",\n            \"type\": \"translate\",\n            \"mustache\": \"\"\n        },\n        \"enrol_status\": {\n            \"key\": \"enrol_status\",\n            \"title\": \"lang::enrol_status::local_kopere_bi\",\n            \"type\": \"statusRenderer\",\n            \"mustache\": \"<span class=\\\"w-100 badge {{^enrol_status}}bg-success{{\\\/enrol_status}}{{#enrol_status}}bg-danger{{\\\/enrol_status}}\\\">\\r\\n    {{#enrol_status}} {{#str}} inactive,moodle {{\\\/str}} {{\\\/enrol_status}}\\r\\n    {{^enrol_status}} {{#str}}   active,moodle {{\\\/str}} {{\\\/enrol_status}}\\r\\n<\\\/span>\"\n        },\n        \"enrol_timeend\": {\n            \"key\": \"enrol_timeend\",\n            \"title\": \"lang::enrol_timeend::local_kopere_bi\",\n            \"type\": \"dateRenderer\",\n            \"mustache\": \"\"\n        }\n    }\n}"
        }
      ]
    },
    {
      "refkey": "enrollments-1",
      "type": "block-1",
      "sequence": 1,
      "elements": [
        {
          "title": "lang::report_4_enrolpercourse::local_kopere_bi",
          "refkey": "report_4_enrolpercourse",
          "block_num": 1,
          "type": "area",
          "theme": "light",
          "css": "",
          "html_before": null,
          "html_after": null,
          "commandsql": "SELECT c.fullname,\r\n       SUM(CASE \r\n               WHEN ue.status = 0 AND (ue.timeend = 0 OR ue.timeend > UNIX_TIMESTAMP()) THEN 1\r\n               ELSE 0\r\n           END) AS 'lang::active_enrolments::local_kopere_bi',\r\n       SUM(CASE \r\n               WHEN ue.status = 1 THEN 1\r\n               ELSE 0\r\n           END) AS 'lang::inactive_enrolments::local_kopere_bi',\r\n       SUM(CASE \r\n               WHEN ue.status = 0 AND ue.timeend > 0 AND ue.timeend < UNIX_TIMESTAMP() THEN 1\r\n               ELSE 0\r\n           END) AS 'lang::expired_enrolments::local_kopere_bi'\r\nFROM mdl_course c\r\nJOIN mdl_enrol e ON e.courseid = c.id\r\nJOIN mdl_user_enrolments ue ON ue.enrolid = e.id\r\nGROUP BY c.id\r\nORDER BY c.fullname",
          "cache": "30m",
          "reload": "30m",
          "info": "{\n    \"chart_options\": \"{\\r\\n    stroke : {\\r\\n        colors : [\\\"#2E93fA\\\", \\\"#66DA26\\\", \\\"#546E7A\\\", \\\"#E91E63\\\", \\\"#FF9800\\\"]\\r\\n    },\\r\\n    yaxis : {\\r\\n        labels : {\\r\\n            formatter: (value) => { return value },\\r\\n        },\\r\\n    },\\r\\n}\"\n}"
        }
      ]
    }
  ]
}
