storageapi.php 9.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293
  1. <?php
  2. // Storage API
  3. // PHP Backend
  4. // CBlue SPRL, Jean-Karim Bockstael, <jeankarim@cblue.be>
  5. require_once('../inc/global.inc.php');
  6. // variable cleaning...
  7. foreach (Array("svkey", "svvalue") as $key)
  8. $_REQUEST[$key] = Database::escape_string($_REQUEST[$key]);
  9. foreach (Array("svuser", "svcourse", "svsco", "svlength", "svasc") as $key)
  10. $_REQUEST[$key] = intval($_REQUEST[$key]);
  11. switch ($_REQUEST['action']) {
  12. case "get":
  13. print storage_get($_REQUEST['svuser'], $_REQUEST['svcourse'], $_REQUEST['svsco'], $_REQUEST['svkey']);
  14. break;
  15. case "set":
  16. if (storage_can_set($_REQUEST['svuser'])) {
  17. print storage_set($_REQUEST['svuser'], $_REQUEST['svcourse'], $_REQUEST['svsco'], $_REQUEST['svkey'], $_REQUEST['svvalue']);
  18. }
  19. break;
  20. case "getall":
  21. print storage_getall($_REQUEST['svuser'], $_REQUEST['svcourse'], $_REQUEST['svsco']);
  22. break;
  23. case "stackpush":
  24. if (storage_can_set($_REQUEST['svuser'])) {
  25. print storage_stack_push($_REQUEST['svuser'], $_REQUEST['svcourse'], $_REQUEST['svsco'], $_REQUEST['svkey'], $_REQUEST['svvalue']);
  26. }
  27. break;
  28. case "stackpop":
  29. if (storage_can_set($_REQUEST['svuser'])) {
  30. print storage_stack_pop($_REQUEST['svuser'], $_REQUEST['svcourse'], $_REQUEST['svsco'], $_REQUEST['svkey']);
  31. }
  32. break;
  33. case "stacklength":
  34. print storage_stack_length($_REQUEST['svuser'], $_REQUEST['svcourse'], $_REQUEST['svsco'], $_REQUEST['svkey']);
  35. break;
  36. case "stackclear":
  37. if (storage_can_set($_REQUEST['svuser'])) {
  38. print storage_stack_clear($_REQUEST['svuser'], $_REQUEST['svcourse'], $_REQUEST['svsco'], $_REQUEST['svkey']);
  39. }
  40. break;
  41. case "stackgetall":
  42. if (storage_can_set($_REQUEST['svuser']))
  43. print storage_stack_getall($_REQUEST['svuser'], $_REQUEST['svcourse'], $_REQUEST['svsco'], $_REQUEST['svkey']);
  44. break;
  45. case "getposition":
  46. print storage_get_position($_REQUEST['svuser'], $_REQUEST['svcourse'], $_REQUEST['svsco'], $_REQUEST['svkey'], $_REQUEST['svasc']);
  47. break;
  48. case "getleaders":
  49. print storage_get_leaders($_REQUEST['svuser'], $_REQUEST['svcourse'], $_REQUEST['svsco'], $_REQUEST['svkey'], $_REQUEST['svasc'], $_REQUEST['svlength']);
  50. break;
  51. case "usersgetall":
  52. // security issue
  53. print "NOT allowed, security issue, see sources";
  54. // print storage_get_all_users();
  55. break;
  56. default:
  57. // Do nothing
  58. }
  59. function storage_can_set($sv_user) {
  60. // platform admin can change any user's stored values, other users can only change their own values
  61. $allowed = ((api_is_platform_admin()) || ($sv_user == api_get_user_id()));
  62. if (!$allowed) {
  63. print "ERROR : Not allowed";
  64. }
  65. return $allowed;
  66. }
  67. function storage_get($sv_user, $sv_course, $sv_sco, $sv_key) {
  68. $sql = "select sv_value
  69. from ".Database::get_main_table(TABLE_TRACK_STORED_VALUES)."
  70. where user_id= '$sv_user'
  71. and sco_id = '$sv_sco'
  72. and course_id = '$sv_course'
  73. and sv_key = '$sv_key'";
  74. $res = Database::query($sql);
  75. if (Database::num_rows($res) > 0) {
  76. $row = Database::fetch_assoc($res);
  77. if (get_magic_quotes_gpc()) {
  78. return stripslashes($row['sv_value']);
  79. } else {
  80. return $row['sv_value'];
  81. }
  82. }
  83. else {
  84. return null;
  85. }
  86. }
  87. function storage_get_leaders($sv_user, $sv_course, $sv_sco, $sv_key, $sv_asc, $sv_length) {
  88. // get leaders
  89. $sql_leaders = "select u.user_id, firstname, lastname, email, username, sv_value as value
  90. from ".Database::get_main_table(TABLE_TRACK_STORED_VALUES)." sv,
  91. ".Database::get_main_table(TABLE_MAIN_USER)." u
  92. where u.user_id=sv.user_id
  93. and sco_id = '$sv_sco'
  94. and course_id = '$sv_course'
  95. and sv_key = '$sv_key'
  96. order by sv_value ".($sv_asc ? "ASC": "DESC")." limit $sv_length";
  97. // $sql_data = "select sv.user_id as user_id, sv_key as variable, sv_value as value
  98. // from ".Database::get_main_table(TABLE_TRACK_STORED_VALUES)." sv
  99. // where sv.user_id in (select u2.user_id from ($sql_leaders) u2)
  100. // and sco_id = '$sv_sco'
  101. // and course_id = '$sv_course'";
  102. // $resData = Database::query($sql_data);
  103. // $data = Array();
  104. // while($row = Database::fetch_assoc($resData))
  105. // $data[] = $row; // fetching all data
  106. //
  107. $resLeaders = Database::query($sql_leaders);
  108. $result = array();
  109. while ($row = Database::fetch_assoc($resLeaders)) {
  110. $row["values"] = array();
  111. // foreach($data as $dataRow) {
  112. // if ($dataRow["user_id"] = $row["user_id"])
  113. // $row["values"][$dataRow["variable"]] = $dataRow["value"];
  114. // }
  115. $result[] = $row;
  116. }
  117. return json_encode($result);
  118. }
  119. function storage_get_position($sv_user, $sv_course, $sv_sco, $sv_key, $sv_asc, $sv_length) {
  120. $sql = "select count(list.user_id) as position
  121. from ".Database::get_main_table(TABLE_TRACK_STORED_VALUES)." search,
  122. ".Database::get_main_table(TABLE_TRACK_STORED_VALUES)." list
  123. where search.user_id= '$sv_user'
  124. and search.sco_id = '$sv_sco'
  125. and search.course_id = '$sv_course'
  126. and search.sv_key = '$sv_key'
  127. and list.sv_value ".($sv_asc ? "<=": ">=")." search.sv_value
  128. and list.sco_id = search.sco_id
  129. and list.course_id = search.course_id
  130. and list.sv_key = search.sv_key
  131. order by list.sv_value" ;
  132. $res = Database::query($sql);
  133. if (Database::num_rows($res) > 0) {
  134. $row = Database::fetch_assoc($res);
  135. return $row['position'];
  136. }
  137. else {
  138. return null;
  139. }
  140. }
  141. function storage_set($sv_user, $sv_course, $sv_sco, $sv_key, $sv_value) {
  142. $sv_value = Database::escape_string($sv_value);
  143. $sql = "replace into ".Database::get_main_table(TABLE_TRACK_STORED_VALUES)."
  144. (user_id, sco_id, course_id, sv_key, sv_value)
  145. values
  146. ('$sv_user','$sv_sco','$sv_course','$sv_key','$sv_value')";
  147. $res = Database::query($sql);
  148. return Database::affected_rows($res);
  149. }
  150. function storage_getall($sv_user, $sv_course, $sv_sco) {
  151. $sql = "select sv_key, sv_value
  152. from ".Database::get_main_table(TABLE_TRACK_STORED_VALUES)."
  153. where user_id= '$sv_user'
  154. and sco_id = '$sv_sco'
  155. and course_id = '$sv_course'";
  156. $res = Database::query($sql);
  157. $data = array();
  158. while ($row = Database::fetch_assoc($res)) {
  159. if (get_magic_quotes_gpc()) {
  160. $row['sv_value'] = stripslashes($row['sv_value']);
  161. }
  162. $data[] = $row;
  163. }
  164. return json_encode($data);
  165. }
  166. function storage_stack_push($sv_user, $sv_course, $sv_sco, $sv_key, $sv_value) {
  167. $sv_value = Database::escape_string($sv_value);
  168. Database::query("start transaction");
  169. $sqlorder = "select ifnull((select max(stack_order)
  170. from ".Database::get_main_table(TABLE_TRACK_STORED_VALUES_STACK)."
  171. where user_id= '$sv_user'
  172. and sco_id='$sv_sco'
  173. and course_id='$sv_course'
  174. and sv_key='$sv_key'
  175. ), 0) as stack_order";
  176. $resorder = Database::query($sqlorder);
  177. $row = Database::fetch_assoc($resorder);
  178. $stack_order = (1 + $row['stack_order']);
  179. $sqlinsert = "insert into ".Database::get_main_table(TABLE_TRACK_STORED_VALUES_STACK)."
  180. (user_id, sco_id, course_id, sv_key, stack_order, sv_value)
  181. values
  182. ('$sv_user', '$sv_sco', '$sv_course', '$sv_key', '$stack_order', '$sv_value')";
  183. $resinsert = Database::query($sqlinsert);
  184. if ($resorder && $resinsert) {
  185. Database::query("commit");
  186. return 1;
  187. }
  188. else {
  189. Database::query("rollback");
  190. return 0;
  191. }
  192. }
  193. function storage_stack_pop($sv_user, $sv_course, $sv_sco, $sv_key) {
  194. Database::query("start transaction");
  195. $sqlselect = "select sv_value, stack_order
  196. from ".Database::get_main_table(TABLE_TRACK_STORED_VALUES_STACK)."
  197. where user_id= '$sv_user'
  198. and sco_id='$sv_sco'
  199. and course_id='$sv_course'
  200. and sv_key='$sv_key'
  201. order by stack_order desc
  202. limit 1";
  203. $resselect = Database::query($sqlselect);
  204. $rowselect = Database::fetch_assoc($resselect);
  205. $stack_order = $rowselect['stack_order'];
  206. $sqldelete = "delete
  207. from ".Database::get_main_table(TABLE_TRACK_STORED_VALUES_STACK)."
  208. where user_id= '$sv_user'
  209. and sco_id='$sv_sco'
  210. and course_id='$sv_course'
  211. and sv_key='$sv_key'
  212. and stack_order='$stack_order'";
  213. $resdelete = Database::query($sqldelete);
  214. if ($resselect && $resdelete) {
  215. Database::query("commit");
  216. if (get_magic_quotes_gpc()) {
  217. return stripslashes($rowselect['sv_value']);
  218. } else {
  219. return $rowselect['sv_value'];
  220. }
  221. } else {
  222. Database::query("rollback");
  223. return null;
  224. }
  225. }
  226. function storage_stack_length($sv_user, $sv_course, $sv_sco, $sv_key) {
  227. $sql = "select count(*) as length
  228. from ".Database::get_main_table(TABLE_TRACK_STORED_VALUES_STACK)."
  229. where user_id= '$sv_user'
  230. and sco_id='$sv_sco'
  231. and course_id='$sv_course'
  232. and sv_key='$sv_key'";
  233. $res = Database::query($sql);
  234. $row = Database::fetch_assoc($res);
  235. return $row['length'];
  236. }
  237. function storage_stack_clear($sv_user, $sv_course, $sv_sco, $sv_key) {
  238. $sql = "delete
  239. from ".Database::get_main_table(TABLE_TRACK_STORED_VALUES_STACK)."
  240. where user_id= '$sv_user'
  241. and sco_id='$sv_sco'
  242. and course_id='$sv_course'
  243. and sv_key='$sv_key'";
  244. $res = Database::query($sql);
  245. return Database::num_rows($res);
  246. }
  247. function storage_stack_getall($sv_user, $sv_course, $sv_sco, $sv_key) {
  248. $sql = "select stack_order as stack_order, sv_value as value
  249. from ".Database::get_main_table(TABLE_TRACK_STORED_VALUES_STACK)."
  250. where user_id= '$sv_user'
  251. and sco_id='$sv_sco'
  252. and course_id='$sv_course'
  253. and sv_key='$sv_key'";
  254. $res = Database::query($sql);
  255. $results = array();
  256. while ($row = Database::fetch_assoc($res)) {
  257. if (get_magic_quotes_gpc()) {
  258. $row['value'] = stripslashes($row['value']);
  259. }
  260. $results[] = $row;
  261. }
  262. return json_encode($results);
  263. }
  264. function storage_get_all_users() {
  265. $sql = "select user_id, username, firstname, lastname
  266. from ".Database::get_main_table(TABLE_MAIN_USER)."
  267. order by user_id asc";
  268. $res = Database::query($sql);
  269. $results = array();
  270. while ($row = Database::fetch_assoc($res)) {
  271. $results[] = $row;
  272. }
  273. return json_encode($results);
  274. }
  275. ?>