カスタムSQLクエリレポート

メインページ ►Moodleサイトの管理 ► サイト全体のレポート ► カスタムSQLクエリレポート
サイト全体のレポート

The Open Universityによって作成されました

この管理レポートプラグインを使用すると、管理者はアドホックレポートとして機能するように任意のデータベースクエリを設定できます。レポートには2つのタイプがあります。オンデマンドで実行されるか、または自動的に実行されるようにスケジュールされています。適切な機能を持つ他のユーザーは、アクセスしてアクセス権を持っているクエリのリストにアクセスすることができます。結果は画面に表示したり、CSVとしてダウンロードすることができます。

このレポートをインストールする

一般的なInstalling pluginsのドキュメントに従ってください。

スクリーンショット

これが2つのスクリーンショットの例です。2つのメインスクリーンが表示されています。

利用可能なレポートの一覧
いずれかのレポートを実行した結果

（注：これらのスクリーンショットは標準のMoodleテーマではなく、OUテーマにあり、iCMAはクイズのOU専門用語です。）

一般ユーザー向けのインターフェース

利用可能なクエリのリスト

report / customsql：view機能を持つユーザーは、adminブロック内のレポートのリストにアクセスできます。各クエリは特定の人だけがアクセスできます。アクセスには3つのレベルがあります。

  • レポートにアクセスできる人なら誰でも利用できます（report / customsql：viewを持っている人）。
  • 他のシステムレポートを閲覧できる人（moodle / site：viewreportsを持つ人）が利用できます
  • 管理者のみ利用可能（moodle / site：configを持つもの）

リストに移動すると、アクセスできるクエリだけが表示されます。各クエリの横には、最後に実行された日時と生成に要した時間を示すメモがあります。

リストには、オンデマンドクエリとスケジュールクエリが別々に表示されます。

オンデマンドクエリを実行する

オンデマンドクエリを実行するには、クエリの一覧でその名前をクリックします。

クエリが実行され、結果がテーブルとして表示されます。テーブル内のURLは自動的にハイパーリンクになります。

クエリの説明がテーブルの上に表示される場合があります。

クエリが実行された時間と所要時間の概要が、CSVファイルをダウンロードするためのリンク（たとえばExcelにデータを取得するためのリンク）と利用可能なすべてのクエリのリストへのリンクとともに下部に表示されます。

スケジュールされたクエリの結果を表示する

スケジュールされたクエリは、2つの方法のいずれかで機能します。レポートを実行するたびに結果テーブル全体が生成されるか、または実行ごとに1行の結果が作成されるだけで、レポートは一度に1行ずつ作成されます。

クエリの一覧で名前をクリックすると、オンデマンドの場合と同様に最新の結果が表示されます。

ただし、スケジュールされた実行ごとに完全なレポートが生成された場合は、ページの下部にレポートの前回の実行すべてのリストが表示されるため、時間の経過とともにレポートがどのように変化したかを確認できます。

管理者用インターフェース

管理者（つまり、report / customsql：definequeriesを持つユーザー）は、他のユーザーが見るものすべてを見ることができますが、いくつか追加されています。

スタッフインターフェースの追加管理

管理者は、リスト内の各レポートが誰にアクセス可能かを表示されます。

また、各クエリの横に編集アイコンと削除アイコンが表示されます。

クエリリストの最後に[新しいクエリの追加]ボタンがあります 。

特定のクエリを表示すると、管理者は結果の表の下に編集リンクと削除リンクを表示します。

クエリを追加または編集する

[ 新しいクエリの追加]ボタンをクリックすると、クエリを定義するための編集フォームに移動します。

クエリに名前を付ける必要があります。

必要に応じて、結果表の上に表示される説明を入力できます。これを使用して、クエリの結果が何を意味するのかを説明する必要があります。

表示したい結果を生成するには、SQLを入力する必要があります。これはSQLのselect文でなければなりません。テーブル名には接頭辞prefix_を使用する必要があります。データベースの内容を変更するようなSQLを入力することはできません。

あなたはあなたがあなたがクエリにアクセスしたい人を選択します。

クエリをオンデマンドで実行するのか、毎週または毎月実行するのかを選択します。レポートがスケジュールされている場合は、実行ごとに1つの行が返されて単一のテーブルに追加されるかどうか、または実行ごとに別々のテーブルが生成されるかどうかを指定できます。

新しいクエリを保存すると、エラーなしで実行されることを確認するためにSQLがチェックされます。レポートが単一行のみを返すと言った場合は、これもチェックされます。クエリを保存した後、これが手動クエリの場合は、クエリ結果ページに移動するので、結果がどのように見えるかを確認できます。自動クエリだった場合、またはフォームをキャンセルした場合は、利用可能なクエリの一覧に移動します。

既存のクエリを編集するには、新しいクエリを追加する場合と同じ形式を使用しますが、既存のクエリのプロパティを変更します。

OUで、週が土曜日に始まることに注意してください。それが気に入らない場合は、locallib.phpの先頭にハッキングするべき明らかに明らかな定数があります。

クエリを削除する

クエリの削除アイコンまたはリンクをクリックすると、削除しようとしているクエリのSQLを表示する確認ページが表示されます。確認ページで[はい]をクリックした場合にのみ、クエリが削除されます。

クエリを削除すると、クエリのリストに戻ります。

あなたの興味深い質問をここに共有

カスタムレポートに面白いSQLを思いついた場合は、ここで共有できます。

グループがオンになっているが、まだグループに割り当てられていないコースにいる学生

このクエリは毎日実行され、グループを使用しているがまだグループに割り当てられていないコース内の学生のレポートを提供し、それらをグループに割り当てるためのビューへのリンクを提供します。教師/非編集教師の役割は除外されています。 

 SELECT
     距離 u 。 NAME ASユーザ名、CONCAT（U。FIRSTNAME、 ""、U。姓）、
     ic 。ショートネームAS "コースコード" 、
     CONCAT（ "%% %% WWWROOT /登録ステム/ users.php %% %% Q ID ="、IC。ID、 "＆アクション= addmember＆ユーザー="、uは。ID） "リンクをグループに追加するには、" AS
から
     prefix_course ic
     詐欺ON prefix_context詐欺を登録しよう 。 instanceid = ic です。 id
     詐欺ON prefix_role_assignments RAを登録しよう 。 ID = RA。文とAND 。 contextlevel = 50
     RA ON prefix_role Rを登録しよう 。 roleid = R。 id
     U ON prefix_userのUを登録しよう 。 ID = RA。ユーザーID
     LEFT JOIN prefix_groups_members gm on u 。 ID = GM。ユーザーID
どこに
     ic 。可視= 1
     そして、IC。ショートネームNOT LIKE '％META％'
     そして、IC。 ショートネーム NOT LIKE '％INOL％'
     そして、 u 。ユーザ名「email@domain.com」が 好き ではありません
     そして、IC。 groupmode <> '0'
     そして gm 。 useridはNULL IS
     そして r 。 ID NOT IN（3、4）
IC BY ORDER。ショートネーム

先週/月に行われたクイズの試み

これを定期レポートとして設定します。 

 SELECT COUNT （ * ）
prefix_quiz_attempts FROM
WHEREタイムフィニッシュ> %% STARTTIME %%
    ANDタイムフィニッシュ<= %% ENDTIME %%
    ANDプレビュー= 0

これは、毎週の最初の日にスケジュール済みとして、または毎月の最初の日に スケジュール済みとして設定する必要があります。そうしないと機能しません。

使用法のまとめ

このレポートは、あなたがあなたのサイトを登録したときにmoodle.orgに送信されるのとほぼ同じ使用統計を示しています。 http://moodle.org/stats/に集約されているもの。 （唯一の違いは、登録フォームが何らかの理由で= 1をAND確認しないことです。 

 SELECT
（prefix_course から のカウント （ID）を選択 ） - 1コースAS、
（SELECT COUNT（ID）= 0削除AND = 1を確認prefix_user FROM）ユーザーなど 、
（SELECT COUNT（DISTINCT RA。ユーザーID）
 prefix_role_capabilities RC FROM
 prefix_role_assignments ra ON raを結合し ます。 roleid = RC。ロールID
 どこ rc 。ケーパビリティIN （ 'moodle / course：upd' || 'ate' 、 'moodle / site：doanything' ） ） AS教師、
就学AS（prefix_role_assignments から のカウント （ID）を選択し）、
（ SELECT COUNT （ id ） FROM prefix_forum_posts ） AS forum_posts 、
（ SELECT COUNT （ id ） FROM prefix_resource ） ASリソース、
（ SELECT COUNT （ id ） FROM prefix_question ） AS質問

このブログ記事には2つの興味深い質問があります。学生はいつオンラインテストを提出しますか？ 。

役割による月別使用量

このレポートには、あなたのサイトにアクセスしている役割ごとの明確なユーザー数が表示されます。ユーザーおよびロールの各インスタンスは、アクセスするコース数に関係なく、月に1回カウントされます。これを使用して、当サイトにアクセスしている生徒と先生の総数を表示します。 

 SELECT 
MONTH（FROM_UNIXTIME（ `prefix_stats_user_monthly`。` timeend`））はCalendar_Minute AS、
CALENDAR_YEAR AS YEAR（FROM_UNIXTIME（ `prefix_stats_user_monthly`。` timeend`））、
prefix_role。 USER_ROLE ように名前、
TOTAL_USERS AS COUNT（DISTINCT prefix_stats_user_monthly。ユーザーID）
から
prefix_stats_user_monthly
INNER JOIN prefix_role_assignments ON prefix_stats_user_monthly 。 userid = prefix_role_assignments 。ユーザーID
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` = 'アクティビティ'
AND prefix_stats_user_monthly 。コースID <> 1
MONTH BY GROUP（FROM_UNIXTIME（ `prefix_stats_user_monthly`。` timeend`））、
YEAR（FROM_UNIXTIME（ `prefix_stats_user_monthly`。` timeend`））、
prefix_stats_user_monthly 。 stattype 、
prefix_role。名
ORDER BY 
YEAR（FROM_UNIXTIME（ `prefix_stats_user_monthly`。` timeend`））、MONTH（FROM_UNIXTIME（ `prefix_stats_user_monthly`。` timeend`））、
prefix_role。名

このレポートでデータを生成できるようにするには、統計を有効にする必要があります。

PostgreSQL版のロールごとの月別使用量クエリ： 

 SELECT 
Calendar_Minute AS EXTRACT（TO_TIMESTAMP から 月 （prefix_stats_user_monthly。timeend））、
CALENDAR_YEAR AS EXTRACT（TO_TIMESTAMP FROM YEAR（prefix_stats_user_monthly。timeend））、
prefix_role。 USER_ROLE ように名前、
TOTAL_USERS AS COUNT（DISTINCT prefix_stats_user_monthly。ユーザーID）
から
prefix_stats_user_monthly
INNER JOIN prefix_role_assignments ON prefix_stats_user_monthly 。 userid = prefix_role_assignments 。ユーザーID
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 = 'アクティビティ'
AND prefix_stats_user_monthly 。コースID <> 1
EXTRACT BY GROUP（TO_TIMESTAMP（prefix_stats_user_monthly。timeend） から 月 ）、
EXTRACT（TO_TIMESTAMP FROM YEAR（prefix_stats_user_monthly。timeend））、
prefix_stats_user_monthly 。 stattype 、
prefix_role。名
ORDER BY 
EXTRACT（TO_TIMESTAMP FROM YEAR（prefix_stats_user_monthly。timeend））、EXTRACT（TO_TIMESTAMP FROM MONTH（prefix_stats_user_monthly。timeend））、
prefix_role。名

サイト全体のクイズ結果をすべて表示

誰かがこれを改善できると確信していますが、これはサイト全体でクイズの結果を表示するクエリです。 - Stuart Mealor 2010年4月9日22時07分（UTC） 

 SELECT 
     prefix_grade_items。 itemname 、
     prefix_grade_items。 grademax 、
     finalgrade AS ROUND（prefix_grade_grades。finalgrade、0）、
     prefix_user。ファーストネーム、
     prefix_user。姓、
     prefix_user。ユーザー名
から
     prefix_grade_grades
     INNER JOIN prefix_user ON prefix_grade_grades 。ユーザーID = prefix_user。 id
     INNERは prefix_grade_grades ON prefix_grade_itemsを登録しよう 。 =の prefix_grade_itemsをITEMID。 id
WHERE（prefix_grade_items。itemNameにNULL ではありません ）
AND（prefix_grade_items。た項目タイプ= 'MOD' OR prefix_grade_items。た項目タイプ= '手動'）
AND（prefix_grade_items。itemmodule = 'クイズ' OR prefix_grade_items。itemmoduleはNULL IS）
AND（prefix_grade_grades。timemodified NULL ではありません ）
AND（prefix_grade_grades。finalgrade> 0）
AND（prefix_user。= 0を削除しました）

また見なさい

" https://docs.moodle.org/35/en/index.php?title=Custom_SQL_queries_report&oldid=127985 "より作成