storageapi.php 10 KB

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