דלג על פקודות של רצועת הכלים
דלג לתוכן ראשי

העצמת ההוראה והלמידה במכללות להכשרת מורים באמצעות מערכת ה- Moodle

נספחים

נספח 1: פקודות (scripts) להפעלת השאילתות השונות

  1. פעולות מפורטות לכל מודולה
    SELECT module,action,count(id) AS counter
    FROM prefix_log
    GROUP BY module,action
    ORDER BY module,counter DESC
  2. פעולות מפורטות לכל בעל תפקיד SELECT r.name,l.action, count( l.userid ) AS counter
    FROM `prefix_log` AS l
    JOIN `prefix_role_assignments` AS ra ON l.userid = ra.userid
    JOIN prefix_role AS r ON ra.roleid = r.id
    WHERE ra.roleid IN (3,4,5)
    GROUP BY roleid,l.action
    ORDER BY counter DESC
  3. הקורסים הפעילים ביותר
    SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename
    FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id
    GROUP BY courseId
    ORDER BY hits DESC
  4. מספר המופעים מכל סוג של פורום באתר
    select type forum_type,count(*) Summa from moodle.prefix_forum
    group by forum_type
  5. מספר המופעים מכל סוג של פורום בכל קורס
    select prefix_course.fullname,prefix_forum.course,prefix_forum.type,count(*)
    as total from moodle.prefix_forum
    inner join moodle.prefix_course
    on prefix_course.id = prefix_forum.course
    group by prefix_forum.course,prefix_forum.type
    order by total desc
  6. מספר המשאבים בכל קורס
    SELECT COUNT(l.id) count, l.course, c.fullname coursename
    FROM prefix_resource l INNER JOIN prefix_course c on l.course = c.id
    GROUP BY course
    ORDER BY count DESC
  7. כמות פורום חדשות בכל קורס
    select prefix_course.fullname,prefix_forum.course,prefix_forum.type,count(*) as total from moodle.prefix_forum
    inner join moodle.prefix_course
    on prefix_course.id = prefix_forum.course
    where prefix_forum.type = 'news'
    group by prefix_forum.course,prefix_forum.type
    order by total desc
  8. מרצים פעילים ביותר
    SELECT count( l.userid ) AS counter,
    u.firstname, u.lastname, r.name
    FROM `prefix_log` AS l
    JOIN prefix_user AS u ON u.id = l.userid
    JOIN prefix_role_assignments AS ra ON l.userid = ra.userid
    JOIN prefix_role AS r ON r.id = ra.roleid
    WHERE ra.roleid IN (3,4)
    GROUP BY l.userid
    ORDER BY counter DESC
  9. משאבים: כמות פריטי html בכל קורס
    SELECT course,prefix_course.fullname, COUNT(*) AS Total
    FROM `moodle`.`prefix_resource` join `moodle`.`prefix_course` on prefix_course.id = prefix_resource.course
    where type='html'
    group by course
  10. משאבים: כמות קישורים לאתרי אינטרנט בכל קורס
    SELECT course,prefix_course.fullname, COUNT(*) AS Total
    FROM `moodle`.`prefix_resource` join `moodle`.`prefix_course` on prefix_course.id = prefix_resource.course
    where type= 'file' and reference LIKE 'http://%'
    group by course
  11. משאבים: כמות קישורים לקבצים בכל קורס
    SELECT course,prefix_course.fullname, COUNT(*) AS Total
    FROM `moodle`.`prefix_resource` join `moodle`.`prefix_course` on prefix_course.id = prefix_resource.course
    where type= 'file' and reference NOT LIKE 'http://%'
    group by course
  12. משתמשים פעילים בשבוע האחרון
    SELECT COUNT(l.id) hits, l.userid, u.username, u.firstname, u.lastname
    FROM prefix_log l INNER JOIN prefix_user u ON l.userid = u.id
    WHERE l.time > UNIX_TIMESTAMP(NOW()) - 604800
    GROUP BY l.userid
    ORDER BY hits DESC
  13. סטודנטים פעילים ביותר
    SELECT count( l.userid ) AS counter,
    u.firstname, u.lastname, r.name
    FROM `prefix_log` AS l
    JOIN prefix_user AS u ON u.id = l.userid
    JOIN prefix_role_assignments AS ra ON l.userid = ra.userid
    JOIN prefix_role AS r ON r.id = ra.roleid
    WHERE ra.roleid IN (5)
    GROUP BY l.userid
    ORDER BY counter DESC
  14. סך הדיונים בפורום חדשות בכל הקורסים
    select prefix_course.shortname as Course_Number,prefix_course.fullname as Course_Name,prefix_forum.course as course_id,prefix_forum_discussions.forum as forum_id,count(prefix_forum_discussions.id) as DiscussionsSum
    from moodle.prefix_forum_discussions
    inner join moodle.prefix_forum
    on moodle.prefix_forum.id = prefix_forum_discussions.forum
    inner join moodle.prefix_course
    on prefix_course.id = prefix_forum.course
    where moodle.prefix_forum.type = 'news'
    group by forum_id
    order by DiscussionsSum desc
  15. סך התגובות לכל דיון בכל הפורומים
    select prefix_course.shortname as Course_Number,prefix_course.fullname as Course_Name,
    prefix_forum.name as Forum_name,prefix_forum_discussions.id as dis_No,
    prefix_forum_discussions.name as discussions_title,
    count(prefix_forum_posts.id) as sumPost from moodle.prefix_forum_posts inner join moodle.prefix_forum_discussions on moodle.prefix_forum_discussions.id = moodle.prefix_forum_posts.discussion inner join moodle.prefix_forum on moodle.prefix_forum.id = prefix_forum_discussions.forum inner join moodle.prefix_course on prefix_course.id = prefix_forum.course group by dis_No order by course_name,Forum_name,discussions_title
  16. סך התגובות לכל דיון בכל הפורומים שאינם חדשות המרצה
    select prefix_course.shortname as Course_Number,prefix_course.fullname as Course_Name,
    prefix_forum.name as Forum_name,prefix_forum_discussions.id as dis_No,
    prefix_forum_discussions.name as discussions_title,
    count(prefix_forum_posts.id) as sumPost
    from moodle.prefix_forum_posts inner join moodle.prefix_forum_discussions
    on moodle.prefix_forum_discussions.id = moodle.prefix_forum_posts.discussion
    inner join moodle.prefix_forum
    on moodle.prefix_forum.id = prefix_forum_discussions.forum
    inner join moodle.prefix_course
    on prefix_course.id = prefix_forum.course
    where moodle.prefix_forum.type <> 'news'
    group by dis_No
    order by course_name,Forum_name,discussions_title
  17. סך פעילויות ותוכן לפי מספרי קורסים
    select course,module,action,count(action) as summa from prefix_log
    where action <> 'new'
    group by course,action,module
    order by course,module,action
  18. פעילויות לפי תאריך ושעה
    select DATE_FORMAT(FROM_UNIXTIME(time),'%y-%m-%d : %r') as acstime,module,action,count(action) as summa from prefix_log
    where action <> 'new' and action <> 'error'
    group by time, action,module
    order by time, module,action
  19. הפעילויות הפופולאריות ביותר
    SELECT COUNT(l.id) hits, module
    FROM prefix_log l
    WHERE module != 'login' AND module != 'course' AND module != 'role'
    GROUP BY module
    ORDER BY hits DESC