- Improved printer-friendly version
[moodle.git] / mod / glossary / sql.php
CommitLineData
4f3e59c3 1<?php
2
3/**
4 * SQL.PHP
5 * This file is include from view.php and print.php
6 * @version $Id$
7 * @copyright 2003
8 **/
9
10/// Creating the SQL statements
11
12/// Pivot is the field that set the break by groups (category, initial, author name, etc)
13
14/// fullpivot indicate if the whole pivot should be compared agasint the db or just the first letter
15/// printpivot indicate if the pivot should be printed or not
16 $fullpivot = 1;
17 $printpivot = 1;
18
19 $userid = '';
20 if ( $USER->id ) {
21 $userid = "OR ge.userid = $USER->id";
22 }
23 switch ($tab) {
24 case GLOSSARY_CATEGORY_VIEW:
25 if ($hook == GLOSSARY_SHOW_ALL_CATEGORIES ) {
26
27 $sqlselect = "SELECT gec.id, gc.name pivot, ge.*";
28 $sqlfrom = "FROM {$CFG->prefix}glossary_entries ge,
29 {$CFG->prefix}glossary_entries_categories gec,
30 {$CFG->prefix}glossary_categories gc";
31 $sqlwhere = "WHERE (ge.glossaryid = '$glossary->id' OR ge.sourceglossaryid = '$glossary->id') AND
32 ge.id = gec.entryid AND gc.id = gec.categoryid AND
33 (ge.approved != 0 $userid)";
34
35 if ( $glossary->displayformat == GLOSSARY_FORMAT_CONTINUOUS ) {
36 $sqlorderby = ' ORDER BY gc.name, ge.timecreated';
37 } else {
38 $sqlorderby = ' ORDER BY gc.name, ge.concept';
39 }
40
41 } elseif ($hook == GLOSSARY_SHOW_NOT_CATEGORISED ) {
42
43 $printpivot = 0;
44 $sqlselect = "SELECT concept pivot, ge.*";
45 $sqlfrom = "FROM {$CFG->prefix}glossary_entries ge";
46 $sqlwhere = "WHERE (glossaryid = '$glossary->id' OR sourceglossaryid = '$glossary->id') AND
47 (ge.approved != 0 $userid)";
48
49
50 $sqlorderby = ' ORDER BY concept';
51
52 } else {
53
54 $printpivot = 0;
55 $sqlselect = "SELECT ce.id, c.name pivot, ge.*";
56 $sqlfrom = "FROM {$CFG->prefix}glossary_entries ge, {$CFG->prefix}glossary_entries_categories ce, {$CFG->prefix}glossary_categories c";
57 $sqlwhere = "WHERE ge.id = ce.entryid AND ce.categoryid = $hook AND
58 ce.categoryid = c.id AND ge.approved != 0 AND
59 (ge.glossaryid = $glossary->id OR ge.sourceglossaryid = $glossary->id) AND
60 (ge.approved != 0 $userid)";
61
62 $sqlorderby = ' ORDER BY c.name, ge.concept';
63
64 }
65 break;
66 case GLOSSARY_AUTHOR_VIEW:
67
68 $where = '';
69 switch ($CFG->dbtype) {
70 case 'postgres7':
71 $usernametoshow = "u.firstname || ' ' || u.lastname";
72 if ( $sortkey == 'FIRSTNAME' ) {
73 $usernamefield = "u.firstname || ' ' || u.lastname";
74 } else {
75 $usernamefield = "u.lastname || ' ' || u.firstname";
76 }
77 $where = "AND substr(ucase($usernamefield),1," . strlen($hook) . ") = '" . strtoupper($hook) . "'";
78 break;
79 case 'mysql':
80 $usernametoshow = "CONCAT(CONCAT(u.firstname,' '), u.lastname)";
81 if ( $sortkey == 'FIRSTNAME' ) {
82 $usernamefield = "CONCAT(CONCAT(u.firstname,' '), u.lastname)";
83 } else {
84 $usernamefield = "CONCAT(CONCAT(u.lastname,' '), u.firstname)";
85 }
86 $where = "AND left(ucase($usernamefield)," . strlen($hook) . ") = '$hook'";
87 break;
88 }
89 if ( $hook == 'ALL' ) {
90 $where = '';
91 }
92
93 $sqlselect = "SELECT ge.id, $usernamefield pivot, $usernametoshow uname, u.id uid, ge.*";
94 $sqlfrom = "FROM {$CFG->prefix}glossary_entries ge, {$CFG->prefix}user u";
95 $sqlwhere = "WHERE ge.userid = u.id AND
96 (ge.approved != 0 $userid)
97 $where AND
98 (ge.glossaryid = $glossary->id OR ge.sourceglossaryid = $glossary->id)";
99 $sqlorderby = "ORDER BY $usernamefield $sortorder, ge.concept";
100 break;
101 case GLOSSARY_APPROVAL_VIEW:
102 $fullpivot = 0;
103 $printpivot = 0;
104
105 $where = '';
106 if ($hook != 'ALL' and $hook != 'SPECIAL') {
107 switch ($CFG->dbtype) {
108 case 'postgres7':
109 $where = 'AND substr(ucase(concept),1,' . strlen($hook) . ') = \'' . strtoupper($hook) . '\'';
110 break;
111 case 'mysql':
112 $where = 'AND left(ucase(concept),' . strlen($hook) . ") = '$hook'";
113 break;
114 }
115 }
116
117 $sqlselect = "SELECT ge.concept pivot, ge.*";
118 $sqlfrom = "FROM {$CFG->prefix}glossary_entries ge";
119 $sqlwhere = "WHERE (ge.glossaryid = $glossary->id OR ge.sourceglossaryid = $glossary->id) AND
120 ge.approved = 0 $where";
121
122 if ( $sortkey ) {
123 $sqlorderby = "ORDER BY $sortkey $sortorder";
124 } else {
125 $sqlorderby = "ORDER BY ge.concept";
126 }
127 break;
128 case GLOSSARY_DATE_VIEW:
129 case GLOSSARY_STANDARD_VIEW:
130 default:
131 $sqlselect = "SELECT ge.concept pivot, ge.*";
132 $sqlfrom = "FROM {$CFG->prefix}glossary_entries ge";
133
134 $where = '';
135 $fullpivot = 0;
136 if ($CFG->dbtype == "postgres7") {
137 $LIKE = "ILIKE"; // case-insensitive
138 } else {
139 $LIKE = "LIKE";
140 }
141
142 switch ( $mode ) {
143 case 'search':
144 $printpivot = 0;
145 $where = "AND ( ge.concept $LIKE '%$hook%'";
146 if ( $fullsearch ) {
147 $where .= "OR ge.definition $LIKE '%$hook%')";
148 } else {
149 $where .= ")";
150 }
151 break;
152
153 case 'term':
154 $printpivot = 0;
155 $sqlfrom .= ", {$CFG->prefix}glossary_alias ga";
156 $where = "AND ge.id = ga.entryid AND
157 (ge.concept = '$hook' OR ga.alias = '$hook' )
158 ";
159 break;
160
161 case 'entry':
162 $printpivot = 0;
163 $where = "AND ge.id = $hook";
164 break;
165
166 case 'letter':
167 if ($hook != 'ALL' and $hook != 'SPECIAL') {
168 switch ($CFG->dbtype) {
169 case 'postgres7':
170 $where = 'AND substr(ucase(concept),1,' . strlen($hook) . ') = \'' . strtoupper($hook) . '\'';
171 break;
172 case 'mysql':
173 $where = 'AND left(ucase(concept),' . strlen($hook) . ") = '$hook'";
174 break;
175 }
176 }
177 break;
178 }
179
180 $sqlwhere = "WHERE (ge.glossaryid = $glossary->id or ge.sourceglossaryid = $glossary->id) AND
181 (ge.approved != 0 $userid)
182 $where";
183 switch ( $tab ) {
184 case GLOSSARY_DATE_VIEW:
185 $sqlorderby = "ORDER BY $sortkey $sortorder";
186 break;
187
188 case GLOSSARY_STANDARD_VIEW:
189 $sqlorderby = "ORDER BY ge.concept";
190 default:
191 break;
192 }
193 break;
194 }
195 $count = count_records_sql("select count(*) $sqlfrom $sqlwhere");
196 $sqllimit = '';
197 if ( $offset >= 0 ) {
198 $sqllimit = " LIMIT $offset, $entriesbypage";
199 }
200 $allentries = get_records_sql("$sqlselect $sqlfrom $sqlwhere $sqlorderby $sqllimit");
201
202?>