Custom SQL queries report - Historial de revisió

P3t3r: Added a PostgreSQL version of the Monthly Usage by Role query

2012-09-07T14:21:26Z

Added a PostgreSQL version of the Monthly Usage by Role query

← Versió més antiga		Revisió del 14:21, 7 set 2012
Línia 155:		Línia 155:

	You must have Statistics turned on to be able to generate any data with this report.		You must have Statistics turned on to be able to generate any data with this report.

			PostgreSQL version of the Monthly Usage by Role query:

			<code sql>
			SELECT
			extract(month from to_timestamp(prefix_stats_user_monthly.timeend)) AS calendar_month,
			extract(year from to_timestamp(prefix_stats_user_monthly.timeend)) AS calendar_year,
			prefix_role.name AS user_role,
			COUNT(DISTINCT prefix_stats_user_monthly.userid) AS total_users
			FROM
			prefix_stats_user_monthly
			INNER JOIN prefix_role_assignments ON prefix_stats_user_monthly.userid = prefix_role_assignments.userid
			INNER JOIN prefix_context ON prefix_role_assignments.contextid = prefix_context.id
			INNER JOIN prefix_role ON prefix_role_assignments.roleid = prefix_role.id
			WHERE prefix_context.contextlevel = 50
			AND prefix_stats_user_monthly.stattype = 'activity'
			AND prefix_stats_user_monthly.courseid <>1
			GROUP BY extract(month from to_timestamp(prefix_stats_user_monthly.timeend)),
			extract(year from to_timestamp(prefix_stats_user_monthly.timeend)),
			prefix_stats_user_monthly.stattype,
			prefix_role.name
			ORDER BY
			extract(year from to_timestamp(prefix_stats_user_monthly.timeend)), extract(month from to_timestamp(prefix_stats_user_monthly.timeend)),
			prefix_role.name
			</code>

	===Show all Quiz results across a site===		===Show all Quiz results across a site===

Tsala: sitewide reports template

2011-10-19T09:42:38Z

sitewide reports template

← Versió més antiga		Revisió del 09:42, 19 oct 2011
Línia 1:		Línia 1:
			{{Sitewide reports}}
	Created by The Open University		Created by The Open University

Línia 5:		Línia 6:
	==Installing this report==		==Installing this report==

	Follow the generic [[Installing ~~contributed modules or~~ plugins]] documentation.		Follow the generic [[Installing plugins]] documentation.


	==Screen shots==		==Screen shots==
Línia 29:		Línia 29:

	The list shows on-demand and scheduled queries separately.		The list shows on-demand and scheduled queries separately.


	===Running an on-demand query===		===Running an on-demand query===
Línia 40:		Línia 39:

	The summary of when the query was run and how long it took is shown at the bottom, along with a link to download the CSV file (for example to get the data into Excel) and a link back to the list of all available queries.		The summary of when the query was run and how long it took is shown at the bottom, along with a link to download the CSV file (for example to get the data into Excel) and a link back to the list of all available queries.


	===Viewing the results of scheduled queries===		===Viewing the results of scheduled queries===
Línia 49:		Línia 47:

	However, if each scheduled run generates a complete report, then at the bottom of a page there will be a list of all the previous runs of the report, so that you can go and see how the report changed over time.		However, if each scheduled run generates a complete report, then at the bottom of a page there will be a list of all the previous runs of the report, so that you can go and see how the report changed over time.


	==Interface for administrators==		==Interface for administrators==

	Administrators (that is, users with report/customsql:definequeries) see everything that other users see, but with some additions.		Administrators (that is, users with report/customsql:definequeries) see everything that other users see, but with some additions.


	===Additional controls in the staff interface===		===Additional controls in the staff interface===
Línia 65:		Línia 61:

	When viewing a particular query, Administrators get an edit and a delete link underneath the table of results.		When viewing a particular query, Administrators get an edit and a delete link underneath the table of results.


	===Adding or editing a query===		===Adding or editing a query===
Línia 186:		Línia 181:
	==See also==		==See also==

	* [[Reports_(administrator)]] for other admin reports
	* [[Installing contributed modules or plugins]]
	* [http://moodle.org/mod/forum/discuss.php?d=136484 Custom SQL queries report] forum announcement		* [http://moodle.org/mod/forum/discuss.php?d=136484 Custom SQL queries report] forum announcement
	* list of [[ad-hoc contributed reports]]		* list of [[ad-hoc contributed reports]]
Línia 193:		Línia 186:
	* [http://moodle.org/mod/data/view.php?d=13&rid=2884 Modules and plugins database entry]		* [http://moodle.org/mod/data/view.php?d=13&rid=2884 Modules and plugins database entry]

	~~[[Category: Administrator]]~~
	~~[[Category:Report]]~~
	[[Category:Contributed_code]]		[[Category:Contributed_code]]

	[[fr:admin/report/customsql/index]]		[[fr:admin/report/customsql/index]]

Tsala: removing 1.9 template

2011-07-11T10:09:31Z

removing 1.9 template

← Versió més antiga		Revisió del 10:09, 11 jul 2011
Línia 1:		Línia 1:
	~~{{Moodle 1.9}}~~
	Created by The Open University		Created by The Open University

TimHunt: /* See also */

2011-06-09T15:47:28Z

Fox a 08:36, 6 abr 2011

2011-04-06T08:36:52Z

← Versió més antiga		Revisió del 08:36, 6 abr 2011
Línia 1:		Línia 1:
			{{Moodle 1.9}}
	Created by The Open University		Created by The Open University

Fox: French link

2010-10-20T09:34:19Z

French link

← Versió més antiga		Revisió del 09:34, 20 oct 2010
Línia 195:		Línia 195:
	[[Category:Report]]		[[Category:Report]]
	[[Category:Contributed_code]]		[[Category:Contributed_code]]

			[[fr:admin/report/customsql/index]]

Amirelion: /* See also */

2010-07-19T11:52:10Z

Nadavkav: /* See also */

2010-06-01T19:13:46Z

Stuartrmealor: /* Monthly Usage by Role */

2010-04-09T22:07:01Z

Monthly Usage by Role

← Versió més antiga		Revisió del 22:07, 9 abr 2010
Línia 160:		Línia 160:

	You must have Statistics turned on to be able to generate any data with this report.		You must have Statistics turned on to be able to generate any data with this report.

			===Show all Quiz results across a site===
			I'm sure someone will be able to improve upon this, but this is a query that will show Quiz results across a site. --[[User:Stuart Mealor\|Stuart Mealor]] 22:07, 9 April 2010 (UTC)

			<code sql>
			SELECT
			prefix_grade_items.itemname,
			prefix_grade_items.grademax,
			ROUND(prefix_grade_grades.finalgrade, 0) AS finalgrade,
			prefix_user.firstname,
			prefix_user.lastname,
			prefix_user.username
			FROM
			prefix_grade_grades
			INNER JOIN prefix_user ON prefix_grade_grades.userid = prefix_user.id
			INNER JOIN prefix_grade_items ON prefix_grade_grades.itemid = prefix_grade_items.id
			WHERE (prefix_grade_items.itemname IS NOT NULL)
			AND (prefix_grade_items.itemtype = 'mod' OR prefix_grade_items.itemtype = 'manual')
			AND (prefix_grade_items.itemmodule = 'quiz' OR prefix_grade_items.itemmodule IS NULL)
			AND (prefix_grade_grades.timemodified IS NOT NULL)
			AND (prefix_grade_grades.finalgrade > 0)
			AND (prefix_user.deleted = 0)
			</code>

	==See also==		==See also==

Mcampbell: /* Monthly Usage by Role */

2010-04-09T21:49:10Z

Monthly Usage by Role

← Versió més antiga		Revisió del 21:49, 9 abr 2010
Línia 140:		Línia 140:
	month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_month,		month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_month,
	year(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_year,		year(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_year,
	prefix_role.name,		prefix_role.name as user_role,
	COUNT(DISTINCT prefix_stats_user_monthly.userid) AS ~~TOTAL_COURSES~~		COUNT(DISTINCT prefix_stats_user_monthly.userid) AS total_users
	FROM		FROM
	prefix_stats_user_monthly		prefix_stats_user_monthly

← Versió més antiga		Revisió del 15:47, 9 juny 2011
Línia 192:		Línia 192:
	* list of [[ad-hoc contributed reports]]		* list of [[ad-hoc contributed reports]]
	* [http://moodle.org/mod/forum/discuss.php?d=153059 Forum post] with some more useful queries.		* [http://moodle.org/mod/forum/discuss.php?d=153059 Forum post] with some more useful queries.
			* [http://moodle.org/mod/data/view.php?d=13&rid=2884 Modules and plugins database entry]

	[[Category: Administrator]]		[[Category: Administrator]]

← Versió més antiga		Revisió del 11:52, 19 jul 2010
Línia 190:		Línia 190:
	* [http://moodle.org/mod/forum/discuss.php?d=136484 Custom SQL queries report] forum announcement		* [http://moodle.org/mod/forum/discuss.php?d=136484 Custom SQL queries report] forum announcement
	* list of [[ad-hoc contributed reports]]		* list of [[ad-hoc contributed reports]]
			* [http://moodle.org/mod/forum/discuss.php?d=153059 Forum post] with some more useful queries.

	[[Category: Administrator]]		[[Category: Administrator]]
	[[Category:Report]]		[[Category:Report]]
	[[Category:Contributed_code]]		[[Category:Contributed_code]]

← Versió més antiga		Revisió del 19:13, 1 juny 2010
Línia 189:		Línia 189:
	* [[Installing contributed modules or plugins]]		* [[Installing contributed modules or plugins]]
	* [http://moodle.org/mod/forum/discuss.php?d=136484 Custom SQL queries report] forum announcement		* [http://moodle.org/mod/forum/discuss.php?d=136484 Custom SQL queries report] forum announcement
			* list of [[ad-hoc contributed reports]]

	[[Category: Administrator]]		[[Category: Administrator]]
	[[Category:Report]]		[[Category:Report]]
	[[Category:Contributed_code]]		[[Category:Contributed_code]]