Month: May 2019

Referensi Moodle API

~ suji2019 ~ Leave a comment

https://stackoverflow.com/questions/35881584/using-moodle-create-users-and-enroll-them-in-courses-via-sql

http://www.refineddata.com/api

https://stackoverflow.com/questions/22161606/sql-query-for-courses-enrolment-on-moodle?rq=1

    CREATE DEFINER = `your_user`@`%` PROCEDURE `RemoveUserDataFromCourse`(IN `c_id` int,IN `u_id` int)
BEGIN
    # ENROLLMENT
    DELETE FROM mdl_user_enrolments WHERE userid = u_id AND enrolid IN (SELECT id FROM mdl_enrol WHERE courseid = c_id);

  # LOG, EVENT, POSTS
    DELETE FROM mdl_log WHERE course = c_id AND userid = u_id;
    DELETE FROM mdl_event WHERE courseid = c_id AND userid = u_id;
    DELETE FROM mdl_post WHERE courseid = c_id AND userid = u_id;

    # Course completion
    DELETE FROM mdl_course_modules_completion WHERE coursemoduleid IN (SELECT id FROM mdl_course_modules WHERE course= c_id and userid=u_id);
    DELETE FROM mdl_course_completions WHERE course = c_id AND userid = u_id;
    DELETE FROM mdl_course_completion_crit_compl WHERE course = c_id AND userid = u_id;

    # Grades from all modules
    DELETE FROM mdl_grade_grades WHERE userid = u_id and itemid IN (SELECT id FROM mdl_grade_items WHERE courseid = c_id);

    # MODULES

    # ASSIGNMENTS
    DELETE FROM mdl_assign_grades WHERE userid = u_id AND assignment IN (SELECT id FROM mdl_assign WHERE course = c_id);    

    # QUIZ
    DELETE FROM mdl_quiz_attempts WHERE userid = u_id AND quiz IN (SELECT id FROM mdl_quiz WHERE course = c_id);
    DELETE FROM mdl_quiz_grades WHERE userid = u_id AND quiz IN (SELECT id FROM mdl_quiz WHERE course = c_id);

    # SCORM
    DELETE FROM mdl_scorm_scoes_track WHERE userid = u_id AND scormid IN (SELECT id FROM mdl_scorm WHERE course = c_id);

    # CHECK LIST
    DELETE FROM mdl_checklist_check WHERE userid = u_id AND item IN ( SELECT mci.id FROM mdl_checklist mc INNER JOIN mdl_checklist_item mci ON mci.checklist = mc.id WHERE course = c_id );
    DELETE FROM mdl_checklist_comment WHERE userid = u_id AND itemid IN ( SELECT mci.id FROM mdl_checklist mc INNER JOIN mdl_checklist_item mci ON mci.checklist = mc.id WHERE course = c_id );

    # CHOICE
    DELETE FROM mdl_choice_answers WHERE userid = u_id AND choiceid IN (SELECT id FROM mdl_choice WHERE course = c_id);
END;



SELECT c.fullname
FROM mdl_course c 
JOIN mdl_enrol en ON en.courseid = c.id 
JOIN mdl_user_enrolments ue ON ue.enrolid = en.id 
WHERE ue.userid = '788'


SELECT cr.SHORTNAME, 
       cr.FULLNAME, 
       COUNT(ra.ID) AS enrolled 
FROM   MDL_COURSE cr 
       JOIN MDL_CONTEXT ct 
         ON ( ct.INSTANCEID = cr.ID ) 
       LEFT JOIN MDL_ROLE_ASSIGNMENTS ra 
              ON ( ra.CONTEXTID = ct.ID ) 
WHERE  ct.CONTEXTLEVEL = 50 
       AND ra.ROLEID = 5 
GROUP  BY cr.SHORTNAME, 
          cr.FULLNAME 
ORDER  BY ENROLLED ASC 

https://docs.moodle.org/36/en/ad-hoc_contributed_reports

QUERY FOR FINAL GRADE MOODLE


select mc.shortname as course_Id,mu.username, 
case when ABS(ROUND(mgg.finalgrade, 0) - mgg.finalgrade) <= 0.0000005 then cast(mgg.finalgrade as text) else 'Pending...' end as final_grade,
mgi.itemname
from mdl_grade_grades mgg
join mdl_user mu on mgg.userid=mu.id
join mdl_user_enrolments mue on mue.userid=mu.id
join mdl_grade_items mgi on mgg.itemid=mgi.id
join mdl_course mc on mc.id=mgi.courseid
join mdl_enrol me on mue.enrolid=me.id and me.courseid=mc.id
join mdl_role_assignments mra on mu.id=mra.userid
join mdl_role mr on mra.roleid=mr.id
where mc.category = '1' and -- mdl_course_categories.id = '1'; id=1 => name='Spring 2012' as an example 
mr.shortname='student' and -- only get people enrolled with a 'student' role in the course
mgi.itemname ilike '%Final%Grade%'; --anything with 'final' and 'grade' in it regardless of case and surrounding text

Project moodle

~ suji2019 ~ Leave a comment 

$servername = 'localhost';
$username = 'username';
$password = 'password';
$dbname = 'moodle';

$u_moodle = 'theusernameyouwant';
$hp_moodle = password_hash('thepasswordyouwant', PASSWORD_DEFAULT); ///IMPORTANT!
$name = 'first name';
$lname = 'last name';
$email = 'e@m.ail'; ///This have to be verified by you as we're inserting it directly
$course = '123'; //Id that you put in moodle admin, not the real id    

$conn = new mysqli($servername, $username, $password, $dbname);

$sql = "INSERT INTO 'mdl_user' (auth, confirmed, mnethostid, username, password, firstname, lastname, email)
    VALUES ('manual', 1, 1, '$u_moodle', '$hp_moodle', '$name', '$lname', '$email')";
// auth = 'manual', confirmed = 1, mnethostid = 1 Always. the others are your variables

if ($conn->query($sql) === TRUE) {
    echo "OKTC";
} else {
    ////Manage your errors
}

$sql = "SELECT * FROM $m_user WHERE email='$email'";
$result = $conn2->query($sql);
if($row = $result->fetch_assoc()) {
    $id = $row['id']; //Id of newly created user. we're using that for to register him on the course
}

////You have to use this if your idnumber for the course is the one you put into moodle (thats not the real id)
$sql = "SELECT id FROM 'mdl_course' WHERE idnumber=$course";
$result = $conn->query($sql);
if(!$result){
    ///Not existing course, manage your error
}
if($row = $result->fetch_assoc()) {
    $idcourse = $row["id"];
}

///I need now the "enrol" id, so I do this:
$sql = "SELECT id FROM 'mdl_enrol' WHERE courseid=$idcourse AND enrol='manual'";
$result = $conn->query($sql);
if(!$result){
    ///Not enrol associated (this shouldn't happen and means you have an error in your moodle database)
}
if($row = $result->fetch_assoc()) {
    $idenrol = $row["id"];
}

///Lastly I need the context
$sql = "SELECT id FROM 'mdl_context' WHERE contextlevel=50 AND instanceid=$idcourse"; ///contextlevel = 50 means course in moodle
$result = $conn->query($sql);
if(!$result){
    ///Again, weird error, shouldnt happen to you
}
if($row = $result->fetch_assoc()) {
    $idcontext = $row["id"];
}

///We were just getting variables from moodle. Here is were the enrolment begins:

$time = time();
$ntime = $time + 60*60*24*$duration; //How long will it last enroled $duration = days, this can be 0 for unlimited.
$sql = "INSERT INTO 'mdl_user_enrolments' (status, enrolid, userid, timestart, timeend, timecreated, timemodified)
VALUES (0, $idenrol, $id, '$time', '$ntime', '$time', '$time')";
if ($conn->query($sql) === TRUE) {
} else {
    ///Manage your sql error
}

$sql = "INSERT INTO 'mdl_role_assignments' (roleid, contextid, userid, timemodified)
VALUES (5, $idcontext, '$id', '$time')"; //Roleid = 5, means student.
if ($conn->query($sql) === TRUE) {
} else {
    //manage your errors
}