rr

https://drive.google.com/file/d/0BxriXuT0xeaFRmZReGthT3JPdjQ/view?usp=sharing

dd

link

txt

postgresql

http://snowdaily.pixnet.net/blog/post/61677905-%E4%BD%BF%E7%94%A8asp.net-c%23%E9%80%A3%E6%8E%A5postgresql-server

https://forums.asp.net/t/402746.aspx?How+can+i+connect+to+Postgres+

https://www.codeproject.com/Articles/30989/Using-PostgreSQL-in-your-C-NET-application-An-intr

moodle quiz & final grade sql

quiz score
SELECT
     mdl_grade_items.itemname,
     mdl_grade_items.grademax,
     ROUND(mdl_grade_grades.finalgrade, 0) AS finalgrade,
     mdl_user.firstname,
     mdl_user.lastname,
     mdl_user.username
FROM
     mdl_grade_grades
     INNER JOIN mdl_user ON mdl_grade_grades.userid = mdl_user.id
     INNER JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.id
WHERE (mdl_grade_items.itemname IS NOT NULL)
AND (mdl_grade_items.itemtype = 'mod' OR mdl_grade_items.itemtype = 'manual')
AND (mdl_grade_items.itemmodule = 'quiz' OR mdl_grade_items.itemmodule IS NULL)
AND (mdl_grade_grades.timemodified IS NOT NULL)
AND (mdl_grade_grades.finalgrade > 0)
AND (mdl_user.deleted = 0)




SELECT MONTH( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) AS calendar_month, YEAR( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) AS calendar_year, mdl_role.name AS user_role, COUNT( DISTINCT mdl_stats_user_monthly.userid ) AS total_users
FROM mdl_stats_user_monthly
INNER JOIN mdl_role_assignments ON mdl_stats_user_monthly.userid = mdl_role_assignments.userid
INNER JOIN mdl_context ON mdl_role_assignments.contextid = mdl_context.id
INNER JOIN mdl_role ON mdl_role_assignments.roleid = mdl_role.id
WHERE mdl_context.contextlevel =50
AND `mdl_stats_user_monthly`.`stattype` = 'activity'
AND mdl_stats_user_monthly.courseid <>1
GROUP BY MONTH( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) , YEAR( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) , mdl_stats_user_monthly.stattype, mdl_role.name
ORDER BY YEAR( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) , MONTH( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) , mdl_role.name
LIMIT 0 , 30



SELECT
(SELECT COUNT(id) FROM mdl_course) - 1 AS courses,
(SELECT COUNT(id) FROM mdl_user WHERE deleted = 0 AND confirmed = 1) AS users,
(SELECT COUNT(DISTINCT ra.userid)
 FROM mdl_role_capabilities rc
 JOIN mdl_role_assignments ra ON ra.roleid = rc.roleid
 WHERE rc.capability IN ('moodle/course:upd' || 'ate', 'moodle/site:doanything')) AS teachers,
(SELECT COUNT(id) FROM mdl_role_assignments) AS enrolments,
(SELECT COUNT(id) FROM mdl_forum_posts) AS forum_posts,
(SELECT COUNT(id) FROM mdl_resource) AS resources,
(SELECT COUNT(id) FROM mdl_question) AS questions



list all grades
SELECT * FROM
(SELECT
gg.timemodified,
u.username,
u.firstname,
u.lastname,
u.email,
gg.finalgrade,
gi.courseid
FROM mdl_grade_grades_history gg
JOIN mdl_grade_items gi ON gi.id = gg.itemid
JOIN mdl_user u ON u.id = gg.userid
JOIN mdl_course c ON c.id=gi.courseid
WHERE
gi.itemtype = 'course' AND
gg.finalgrade IS NOT NULL AND
(gg.timemodified > 1 AND gg.timemodified < 99999999999) AND c.category=76
ORDER BY gg.timemodified DESC) AS temp
GROUP BY username
ORDER BY username

moodle - database

https://docs.moodle.org/dev/Data_manipulation_API

https://docs.moodle.org/22/en/Database_activity_FAQ

https://moodle.org/mod/forum/discuss.php?d=181110 (excel date format setting)

https://moodle.org/mod/forum/discuss.php?d=119805

https://moodle.org/mod/forum/discuss.php?d=74243

https://www.filesculptor.com/convert-csv-to-excel/

moodle - 課程最後修改日期

SELECT * , FROM_UNIXTIME( timemodified )
FROM mdl_resource
LIMIT 0 , 30

check tomot

https://social.msdn.microsoft.com/Forums/zh-TW/3a960e44-4afc-40a1-b17e-a7465a1c04be/postgresql-connection-?forum=236

http://www.blueshop.com.tw/board/FUM20041006161839LRJ/BRD20121210214021YIC.html

http://www.blueshop.com.tw/board/FUM20041006152735ZFS/BRD20100429153313E4D.html

http://www.blueshop.com.tw/board/FUM20041006152735ZFS/BRD20080618201818PVA.html