rsys.php 75 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777
  1. <?php
  2. // $Id: rsys.php,v 1.80 2006/05/12 08:48:49 sjacobs Exp $
  3. /*
  4. ==============================================================================
  5. Dokeos - elearning and course management software
  6. Copyright (c) 2004-2008 Dokeos SPRL
  7. Copyright (c) Sebastien Jacobs (www.spiritual-coder.com)
  8. Copyright (c) Kristof Van Steenkiste
  9. Copyright (c) Julio Montoya Armas
  10. For a full list of contributors, see "credits.txt".
  11. The full license can be read in "license.txt".
  12. This program is free software; you can redistribute it and/or
  13. modify it under the terms of the GNU General Public License
  14. as published by the Free Software Foundation; either version 2
  15. of the License, or (at your option) any later version.
  16. See the GNU General Public License for more details.
  17. Contact address: Dokeos, rue du Corbeau, 108, B-1030 Brussels, Belgium
  18. Mail: info@dokeos.com
  19. ==============================================================================
  20. */
  21. /**
  22. ---------------------------------------------------------------------
  23. The class-library with all reservation-system specific functionality
  24. ---------------------------------------------------------------------
  25. */
  26. class Rsys {
  27. /**
  28. * Get required database-vars from inc/lib/database.lib.php and load them into the $GLOBALS['_rsys']-array
  29. *
  30. */
  31. function init() {
  32. // reservation database tables
  33. $GLOBALS['_rsys']['dbtables']['item'] = Database :: get_main_table(TABLE_MAIN_RESERVATION_ITEM);
  34. $GLOBALS['_rsys']['dbtables']['reservation'] = Database :: get_main_table(TABLE_MAIN_RESERVATION_RESERVATION);
  35. $GLOBALS['_rsys']['dbtables']['subscription'] = Database :: get_main_table(TABLE_MAIN_RESERVATION_SUBSCRIBTION);
  36. $GLOBALS['_rsys']['dbtables']['category'] = Database :: get_main_table(TABLE_MAIN_RESERVATION_CATEGORY);
  37. $GLOBALS['_rsys']['dbtables']['item_rights'] = Database :: get_main_table(TABLE_MAIN_RESERVATION_ITEM_RIGHTS);
  38. }
  39. /**
  40. * Get the full tag for a reservation specific database table
  41. *
  42. * @param - String $table The table-name
  43. */
  44. function getTable($table) {
  45. return $GLOBALS['_rsys']['dbtables'][$table];
  46. }
  47. /**
  48. * Get number of subscriptions of a reservationperiod
  49. *
  50. * @return - int The amount of subscriptions
  51. */
  52. function get_num_subscriptions_reservationperiods($res_id) {
  53. $sql = "SELECT COUNT(*) FROM ".Rsys :: getTable("subscription")." s
  54. WHERE s.reservation_id = '".Database::escape_string($res_id)."'";
  55. return @ Database::result(Database::query($sql), 0, 0);
  56. }
  57. /**
  58. * Validates the access to a certain reservation-script
  59. *
  60. * @param - String $section The section (= script-file)
  61. * @param - int $id An id (sometimes this is required to get rights for a unique row in the database)
  62. */
  63. function protect_script($section, $id = null) {
  64. $uid = api_get_user_id();
  65. switch ($section) {
  66. case 'm_item' :
  67. if (!api_is_platform_admin() && Rsys :: check_user_status() <> 1)
  68. api_protect_admin_script();
  69. break;
  70. case 'm_category' :
  71. api_protect_admin_script();
  72. break;
  73. case 'm_reservation' :
  74. if (!api_is_platform_admin() && Rsys :: check_user_status() <> 1)
  75. api_protect_admin_script();
  76. break;
  77. }
  78. }
  79. /**
  80. * Formats a message with a goto-link
  81. *
  82. * @param - String $msg The message
  83. * @param - String $page The page-script
  84. * @param - String $pageheader The tag to display as link
  85. */
  86. function get_return_msg($msg, $page, $pageheader) {
  87. $target_url = api_get_path(WEB_PATH).'main/reservation/'.$page;
  88. $return = get_lang('GoTo');
  89. return $msg."<br /><br /><a href=\"$target_url\">$return $pageheader</a>";
  90. }
  91. /**
  92. * Formats a message with a goto-link
  93. *
  94. * @param - String $msg The message
  95. * @param - String $page The page-script
  96. * @param - String $pageheader The tag to display as link
  97. */
  98. function get_return_msg2($msg, $page, $pageheader) {
  99. $return = get_lang('GoTo');
  100. return $msg."<br /><br /><a href=\"$page\">$return $pageheader</a>";
  101. }
  102. /**
  103. * Returns a timestamp from a mysql DATETIME
  104. *
  105. * @param - String $dt DATETIME (0000-00-00 00:00:00)
  106. * @return - int timestamp
  107. */
  108. function mysql_datetime_to_timestamp($dt) {
  109. $yr = strval(substr($dt, 0, 4));
  110. $mo = strval(substr($dt, 5, 2));
  111. $da = strval(substr($dt, 8, 2));
  112. $hr = strval(substr($dt, 11, 2));
  113. $mi = strval(substr($dt, 14, 2));
  114. $se = strval(substr($dt, 17, 2));
  115. return mktime($hr, $mi, $se, $mo, $da, $yr);
  116. }
  117. function mysql_datetime_to_array($dt) {
  118. $offset = strpos($dt, '-');
  119. $dat['year'] = strval(substr($dt, 0, $offset));
  120. $dat['month'] = strval(substr($dt, $offset +1, strpos($dt, '-', $offset +1) - ($offset +1)));
  121. $offset = strpos($dt, '-', $offset +1);
  122. $dat['day'] = strval(substr($dt, $offset +1, strpos($dt, ' ', $offset +1) - ($offset +1)));
  123. $offset = strpos($dt, ' ', $offset +1);
  124. $dat['hour'] = strval(substr($dt, $offset +1, strpos($dt, ':', $offset +1) - ($offset +1)));
  125. $offset = strpos($dt, ':', $offset +1);
  126. $dat['minute'] = strval(substr($dt, $offset +1, strpos($dt, ':', $offset +1) - ($offset +1)));
  127. $offset = strpos($dt, ':', $offset +1);
  128. $dat['second'] = strval(substr($dt, $offset +1, strlen($dt) - $offset +1));
  129. return $dat;
  130. }
  131. function timestamp_to_datetime($timestamp) {
  132. return date('Y-m-d H:i:s', $timestamp);
  133. }
  134. function check_user_status() {
  135. $user_info = api_get_user_info(api_get_user_id());
  136. return $user_info['status'];
  137. }
  138. /*
  139. ============================================================================================
  140. CATEGORIES
  141. ============================================================================================
  142. */
  143. /**
  144. * Adds a category
  145. *
  146. * @param - String $name The name
  147. * @return - int The id
  148. */
  149. function add_category($naam) {
  150. if (Rsys :: check_category($naam)) {
  151. $sql = "INSERT INTO ".Rsys :: getTable("category")." (name) VALUES ('".Database::escape_string($naam)."')";
  152. Database::query($sql);
  153. return Database::insert_id();
  154. }
  155. return false;
  156. }
  157. /**
  158. * Controls if the category already exists
  159. *
  160. * @param - String $name The name
  161. * @return - boolean True or False
  162. */
  163. function check_category($name, $id=0) {
  164. $sql = "SELECT name FROM ".Rsys :: getTable("category")." WHERE LCASE(name)='".strtolower(Database::escape_string($name))."' AND id<>".Database::escape_string($id)."";
  165. $Result = Database::query($sql);
  166. return (Database::num_rows($Result) == 0);
  167. }
  168. /**
  169. * Edits a category
  170. *
  171. * @param - String $name The name
  172. * @param - int $id The id
  173. */
  174. function edit_category($id, $name) {
  175. if (Rsys :: check_category($name, $id)) {
  176. $sql = "UPDATE ".Rsys :: getTable("category")." SET name = '".Database::escape_string($name)."' WHERE id =".Database::escape_string($id)."";
  177. Database::query($sql);
  178. return $id;
  179. }
  180. return false;
  181. }
  182. /**
  183. * Deletes a category
  184. *
  185. * @param - int $id The id
  186. */
  187. function delete_category($id) {
  188. $sql = "SELECT id FROM ".Rsys :: getTable("item")." WHERE category_id=".Database::escape_string($id)."";
  189. $result = Database::query($sql);
  190. if (Database::num_rows($result) == 0) {
  191. $sql2 = "DELETE FROM ".Rsys :: getTable("category")." WHERE id =".Database::escape_string($id)."";
  192. Database::query($sql2);
  193. return 0;
  194. } else {
  195. return Database::num_rows($result);
  196. }
  197. }
  198. /**
  199. * Gets a category from database (give no param to get ALL categories)
  200. *
  201. * @param - int $id The id of the category
  202. * @param - String $orderby (sql) ORDER BY $orderby
  203. * @return - Array One or all rows of the category-table
  204. */
  205. function get_category($id = null, $orderby = "name ASC") {
  206. $sql = "SELECT * FROM ".Rsys :: getTable("category");
  207. if (!empty ($id))
  208. $sql .= " WHERE id = ".Database::escape_string($id)."";
  209. else
  210. $sql .= " ORDER BY ".$orderby;
  211. $arr = Database::store_result(Database::query($sql));
  212. if (!empty ($id))
  213. return $arr[0];
  214. else
  215. return $arr;
  216. }
  217. /**
  218. * Gets all categories that have items in them (for the current user)
  219. * @param - String $orderby (sql) ORDER BY $orderby
  220. * @return - Array All rows of the category-table that have items
  221. */
  222. function get_category_with_items($orderby = "c.name ASC") {
  223. $sql = "SELECT c.* FROM ".Rsys :: getTable("category")." c
  224. INNER JOIN ".Rsys :: getTable("item")." i ON i.category_id =c.id
  225. LEFT JOIN ".Rsys :: getTable("item_rights")." ir ON ir.item_id=i.id
  226. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." cl ON ir.class_id=cl.id AND ir.item_id = i.id
  227. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = cl.id
  228. WHERE (cu.user_id='".api_get_user_id()."' AND ir.view_right=1) OR i.creator='".api_get_user_id()."' OR 1=". (api_is_platform_admin() ? 1 : 0)."
  229. GROUP BY c.id ORDER BY ".$orderby;
  230. $arr = Database::store_result(Database::query($sql));
  231. return $arr;
  232. }
  233. /**
  234. * Gets all categories that have items in them (for the current user)
  235. * @param - String $orderby (sql) ORDER BY $orderby
  236. * @return - Array All rows of the category-table that have items
  237. */
  238. function get_category_with_items_manager($orderby = "c.name ASC") {
  239. $sql = "SELECT c.* FROM ".Rsys :: getTable("category")." c
  240. INNER JOIN ".Rsys :: getTable("item")." i ON i.category_id =c.id
  241. LEFT JOIN ".Rsys :: getTable("item_rights")." ir ON ir.item_id=i.id
  242. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." cl ON ir.class_id=cl.id AND ir.item_id = i.id
  243. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = cl.id
  244. WHERE (cu.user_id='".api_get_user_id()."' AND (ir.edit_right=1 OR ir.delete_right=1)) OR i.creator='".api_get_user_id()."' OR 1=". (api_is_platform_admin() ? 1 : 0)."
  245. GROUP BY c.id ORDER BY ".$orderby;
  246. $arr = Database::store_result(Database::query($sql));
  247. return $arr;
  248. }
  249. /**
  250. * Returns categories for a sortable table based on the params
  251. *
  252. * @param - int $from Index of the first item to return.
  253. * @param - int $per_page The number of items to return
  254. * @param - int $column The number of the column on which the data should be sorted
  255. * @param - String $direction In which order should the data be sorted (ASC or DESC)
  256. */
  257. function get_table_categories($from, $per_page, $column, $direction) {
  258. $sql = "SELECT id AS col0, name as col1, id AS col2 FROM ".Rsys :: getTable("category");
  259. if (isset ($_GET['keyword'])) {
  260. $keyword = Database::escape_string(trim($_GET['keyword']));
  261. $sql .= " WHERE name LIKE '%".$keyword."%' OR id LIKE '%".$keyword."%'";
  262. }
  263. $from = intval($from);
  264. $per_page = intval($per_page);
  265. $column = intval($column);
  266. if(!in_array($direction, array('ASC','DESC'))) {
  267. $direction = 'ASC';
  268. }
  269. $sql .= " ORDER BY col".$column." ".$direction." LIMIT ".$from.",".$per_page;
  270. $result = Database::query($sql);
  271. while ($array = Database::fetch_array($result, 'NUM'))
  272. $arr[] = $array;
  273. return $arr;
  274. }
  275. /**
  276. * Get number of categories
  277. *
  278. * @return - int The amount of categories
  279. */
  280. function get_num_categories() {
  281. $sql = "SELECT COUNT(id) FROM ".Rsys :: getTable("category");
  282. if (isset ($_GET['keyword'])) {
  283. $keyword = Database::escape_string(trim($_GET['keyword']));
  284. $sql .= " WHERE name LIKE '%".$keyword."%' OR id LIKE '%".$keyword."%'";
  285. }
  286. return @ Database::result(Database::query($sql), 0, 0);
  287. }
  288. /*
  289. ============================================================================================
  290. ITEMS
  291. ============================================================================================
  292. */
  293. /**
  294. * Controls if an item in a certain category already exist
  295. *
  296. * @param - String $name The name
  297. * @param - String $category The category id
  298. * @return - Boolean True or false
  299. */
  300. function check_item($item, $category, $id=0) {
  301. $sql = "SELECT name FROM ".Rsys :: getTable("item")."
  302. WHERE LCASE(name)='".strtolower(Database::escape_string($item))."'
  303. AND category_id=".Database::escape_string($category)."
  304. AND id<>".Database::escape_string($id)."";
  305. $Result = Database::query($sql);
  306. return (Database::num_rows($Result) == 0);
  307. }
  308. /**
  309. * Adds an item
  310. *
  311. * @param - String $name The name
  312. * @param - String $description The description
  313. * @param - int $category The category-ID
  314. * @param - String $courseCode The course-Code (optional)
  315. * @return - int The id
  316. */
  317. function add_item($name, $description, $category, $course = "") {
  318. if (Rsys :: check_item($name, $category)) {
  319. $sql = "INSERT INTO ".Rsys :: getTable("item")." (category_id,course_code,name,description,creator) VALUES ('".Database::escape_string($category)."','".Database::escape_string($course)."','".Database::escape_string($name)."','".Database::escape_string($description)."','".api_get_user_id()."')";
  320. Database::query($sql);
  321. return Database::insert_id();
  322. }
  323. return false;
  324. }
  325. /**
  326. * Edits an item
  327. *
  328. * @param - int $id The id
  329. * @param - String $name The name
  330. * @param - String $description The description
  331. * @param - int $category The category-ID
  332. * @param - String $courseCode The course-Code (optional)
  333. */
  334. function edit_item($id, $name, $description, $category, $course = "") {
  335. if (!Rsys :: item_allow($id, 'edit'))
  336. return false;
  337. if (!Rsys :: check_item($name, $category, $id))
  338. return false;
  339. $sql = "UPDATE ".Rsys :: getTable("item")." SET category_id='".Database::escape_string($category)."',course_code='".Database::escape_string($course)."',name='".Database::escape_string($name)."',description='".Database::escape_string($description)."' " .
  340. "WHERE id =".Database::escape_string($id)."";
  341. Database::query($sql);
  342. return $id;
  343. }
  344. /**
  345. * Deletes an item and all linked item-rights
  346. *
  347. * @param - int $id The id
  348. */
  349. function delete_item($id) {
  350. if (!Rsys :: item_allow($id, 'delete'))
  351. return false;
  352. $sql = "SELECT id,end_at FROM".Rsys :: getTable('reservation')." WHERE item_id=".Database::escape_string($id)."";
  353. $result = Database::query($sql);
  354. while ($array = Database::fetch_array($result)) {
  355. if (Rsys :: mysql_datetime_to_timestamp(date('Y-m-d H:i:s')) <= Rsys :: mysql_datetime_to_timestamp($array[1]))
  356. $checked = true;
  357. }
  358. if (!$checked) {
  359. $sql = "DELETE FROM ".Rsys :: getTable("item")." WHERE id =".Database::escape_string($id)."";
  360. Database::query($sql);
  361. $sql = "DELETE FROM ".Rsys :: getTable("item_rights")." WHERE item_id =".Database::escape_string($id)."";
  362. Database::query($sql);
  363. $sql = "DELETE FROM ".Rsys :: getTable("reservation")." WHERE item_id =".Database::escape_string($id)."";
  364. Database::query($sql);
  365. return '0';
  366. } else {
  367. return Database::num_rows($result);
  368. }
  369. }
  370. function item_allow($item_id, $right) {
  371. // Creator heeft alle rechten
  372. switch ($right) {
  373. case 'm_rights' : // manage rights of item (only for creator and admin)
  374. $x = '';
  375. break;
  376. case 'edit' :
  377. $x = ' ir.edit_right=1 ';
  378. break;
  379. case 'delete' :
  380. $x = ' ir.delete_right=1 ';
  381. break;
  382. case 'm_reservation' :
  383. $x = ' ir.m_reservation=1 ';
  384. break;
  385. case 'view' :
  386. $x = ' ir.view_right=1 ';
  387. break;
  388. }
  389. $sql = "SELECT i.id FROM ".Rsys :: getTable("item")." i
  390. LEFT JOIN ".Rsys :: getTable("item_rights")." ir ON ir.item_id=i.id
  391. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = i.id
  392. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id
  393. WHERE i.id='".Database::escape_string($item_id)."' AND (". (!empty ($x) ? "(cu.user_id='".api_get_user_id()."' AND ".$x.") OR " : '')." i.creator='".api_get_user_id()."' OR 1=". (api_is_platform_admin() ? 1 : 0).")";
  394. return Database::num_rows(Database::query($sql)) > 0;
  395. }
  396. /**
  397. * Gets an item from the database (give no param to get ALL items)
  398. *
  399. * @param - int $id The id of the item
  400. * @param - String $orderby (sql) ORDER BY $orderby
  401. * @return - Array The returned rows
  402. */
  403. function get_item($id = null, $orderby = "name ASC") {
  404. $id = Database::escape_string($id);
  405. $sql = "SELECT i.* FROM ".Rsys :: getTable("item")." i";
  406. if (!empty ($id)) {
  407. if (!Rsys :: item_allow($id, 'view'))
  408. return false;
  409. $sql .= " WHERE i.id = '".$id."'";
  410. } else
  411. $sql .= " LEFT JOIN ".Rsys :: getTable("item_rights")." ir ON ir.item_id=i.id LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = i.id LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id WHERE (cu.user_id='".api_get_user_id()."' AND ir.view_right=1) OR i.creator='".api_get_user_id()."' OR 1=". (api_is_platform_admin() ? 1 : 0)." ORDER BY ".$orderby;
  412. $arr = Database::store_result(Database::query($sql));
  413. if (!empty ($id))
  414. return $arr[0]; // Return one row only
  415. else
  416. return $arr;
  417. }
  418. /**
  419. * Returns the blackout-status for an item
  420. *
  421. * @param - int $itemid The id of the item
  422. * @return - boolean true if blackout, false if not
  423. */
  424. function is_blackout($itemid) {
  425. $sql = "SELECT id FROM ".Rsys :: getTable("item");
  426. $sql .= " WHERE id = ".Database::escape_string($itemid)." AND blackout=1";
  427. return Database::num_rows(Database::query($sql)) == 1;
  428. }
  429. /**
  430. * Gets all items of a certain category from the database
  431. *
  432. * @param - int $id The id of the category
  433. * @param - String $orderby (sql) ORDER BY $orderby
  434. * @return - Array The returned rows
  435. */
  436. function get_category_items($id, $orderby = "name ASC") {
  437. $sql = "SELECT * FROM ".Rsys :: getTable("item")." WHERE category_id = ".Database::escape_string($id)." ORDER BY ".$orderby;
  438. $arr = Database::store_result(Database::query($sql));
  439. return $arr;
  440. }
  441. /**
  442. * Gets all items of a certain course from the database
  443. *
  444. * @param - int $id The id of the course
  445. * @param - String $orderby (sql) ORDER BY $orderby
  446. * @return - Array The returned rows
  447. */
  448. function get_course_items($id, $orderby = "name ASC") {
  449. $sql = "SELECT * FROM ".Rsys :: getTable("item")." WHERE course_id = ".Database::escape_string($id)." ORDER BY ".$orderby;
  450. $arr = Database::store_result(Database::query($sql));
  451. return $arr;
  452. }
  453. /**
  454. * Returns items for a sortable table based on the params
  455. *
  456. * @param - int $from Index of the first item to return.
  457. * @param - int $per_page The number of items to return
  458. * @param - int $column The number of the column on which the data should be sorted
  459. * @param - String $direction In which order should the data be sorted (ASC or DESC)
  460. * @return - Array The returned rows
  461. */
  462. function get_table_items($from, $per_page, $column, $direction) {
  463. $sql = "SELECT i.id AS col0, i.name as col1, i.description AS col2, ca.name AS col3, IF(i.creator='".api_get_user_id()."','".get_lang('Yes')."','".get_lang('No')."') AS col4, i.id AS col5
  464. FROM ".Rsys :: getTable("item")." i INNER JOIN ".Rsys :: getTable("category")." ca ON i.category_id = ca.id
  465. LEFT JOIN ".Rsys :: getTable("item_rights")." ir ON ir.item_id=i.id
  466. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = i.id
  467. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id
  468. WHERE ((cu.user_id='".api_get_user_id()."' AND (ir.edit_right=1 OR ir.delete_right=1)) OR i.creator='".api_get_user_id()."' OR 1=". (api_is_platform_admin() ? 1 : 0).")";
  469. if (!empty ($_GET['cat']) && $_GET['cat'] <> 0) {
  470. $sql .= " AND ca.id = '".Database::escape_string($_GET['cat'])."' ";
  471. }
  472. $from = intval($from);
  473. $per_page = intval($per_page);
  474. $column = intval($column);
  475. if(!in_array($direction, array('ASC','DESC'))) {
  476. $direction = 'ASC';
  477. }
  478. $sql .= " GROUP BY i.id ORDER BY col".$column." ".$direction." LIMIT ".$from.",".$per_page;
  479. $result = Database::query($sql);
  480. while ($array = Database::fetch_array($result, 'NUM')) {
  481. if (!$array[4])
  482. $array[4] = '-';
  483. $arr[] = $array;
  484. }
  485. return $arr;
  486. }
  487. /**
  488. * Get number of items
  489. *
  490. * @return - int The amount of items
  491. */
  492. function get_num_items() {
  493. $sql = "SELECT COUNT(DISTINCT i.id) FROM ".Rsys :: getTable("item")." i
  494. LEFT JOIN ".Rsys :: getTable("item_rights")." ir ON ir.item_id=i.id
  495. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = i.id
  496. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id
  497. WHERE ( 1=". (api_is_platform_admin() ? 1 : 0)."
  498. OR ((cu.user_id='".api_get_user_id()."' AND (ir.edit_right=1 OR ir.delete_right=1)) OR i.creator='".api_get_user_id()."' ))";
  499. return @ Database::result(Database::query($sql), 0, 0);
  500. }
  501. /**
  502. * Returns the rights for an item for sortable table based on the params
  503. *
  504. * @param - int $from Index of the first item to return.
  505. * @param - int $per_page The number of items to return
  506. * @param - int $column The number of the column on which the data should be sorted
  507. * @param - String $direction In which order should the data be sorted (ASC or DESC)
  508. * @return - Array The returned rows
  509. */
  510. function get_table_itemrights($from, $per_page, $column, $direction) {
  511. $itemid = Database::escape_string($_GET['item_id']);
  512. $sql = "SELECT id, name FROM ".Database :: get_main_table(TABLE_MAIN_CLASS);
  513. $result = Database::query($sql);
  514. while ($array = Database::fetch_array($result, 'NUM')) {
  515. $arr[] = $array;
  516. }
  517. $sql = "SELECT item_id, class_id,edit_right,delete_right,m_reservation,view_right
  518. FROM ".Rsys :: getTable("item_rights")." WHERE item_id=".$itemid;
  519. $result = Database::query($sql);
  520. while ($array = Database::fetch_array($result, 'NUM')) {
  521. $arr1[] = $array;
  522. }
  523. $count = -1;
  524. if (count($arr)>0) {
  525. foreach ($arr as $lijn) {
  526. $count ++;
  527. $controle = false;
  528. $tabel[$count][0] = $itemid."-".$lijn[0];
  529. $tabel[$count][1] = $lijn[1];
  530. foreach ($arr1 as $lijn2) {
  531. if ($lijn2[1] == $lijn[0]) {
  532. if ($lijn2[2] == 0) {
  533. $tabel[$count][2] = '<img src="../img/wrong.gif" onclick="document.location.href=\'m_item.php?action=m_rights&subaction=switch&class_id='.$lijn[0].'&item_id='.$itemid.'&switch=edit&set=1\'" />';
  534. } else {
  535. $tabel[$count][2] = '<img src="../img/right.gif" onclick="document.location.href=\'m_item.php?action=m_rights&subaction=switch&class_id='.$lijn[0].'&item_id='.$itemid.'&switch=edit&set=0\'" />';
  536. }
  537. if ($lijn2[3] == 0) {
  538. $tabel[$count][3] = '<img src="../img/wrong.gif" onclick="document.location.href=\'m_item.php?action=m_rights&subaction=switch&class_id='.$lijn[0].'&item_id='.$itemid.'&switch=delete&set=1\'" />';
  539. } else {
  540. $tabel[$count][3] = '<img src="../img/right.gif" onclick="document.location.href=\'m_item.php?action=m_rights&subaction=switch&class_id='.$lijn[0].'&item_id='.$itemid.'&switch=delete&set=0\'" />';
  541. }
  542. if ($lijn2[4] == 0) {
  543. $tabel[$count][4] = '<img src="../img/wrong.gif" onclick="document.location.href=\'m_item.php?action=m_rights&subaction=switch&class_id='.$lijn[0].'&item_id='.$itemid.'&switch=manage&set=1\'" />';
  544. } else {
  545. $tabel[$count][4] = '<img src="../img/right.gif" onclick="document.location.href=\'m_item.php?action=m_rights&subaction=switch&class_id='.$lijn[0].'&item_id='.$itemid.'&switch=manage&set=0\'" />';
  546. }
  547. if ($lijn2[5] == 0) {
  548. $tabel[$count][5] = '<img src="../img/wrong.gif" onclick="document.location.href=\'m_item.php?action=m_rights&subaction=switch&class_id='.$lijn[0].'&item_id='.$itemid.'&switch=view&set=1\'" />';
  549. } else {
  550. $tabel[$count][5] = '<img src="../img/right.gif" onclick="document.location.href=\'m_item.php?action=m_rights&subaction=switch&class_id='.$lijn[0].'&item_id='.$itemid.'&switch=view&set=0\'" />';
  551. }
  552. $controle = true;
  553. }
  554. }
  555. if (!$controle) {
  556. $tabel[$count][2] = '<img src="../img/wrong.gif" onclick="document.location.href=\'m_item.php?action=m_rights&subaction=switch&class_id='.$lijn[0].'&item_id='.$itemid.'&switch=edit&set=1\'" />';
  557. $tabel[$count][3] = '<img src="../img/wrong.gif" onclick="document.location.href=\'m_item.php?action=m_rights&subaction=switch&class_id='.$lijn[0].'&item_id='.$itemid.'&switch=delete&set=1\'" />';
  558. $tabel[$count][4] = '<img src="../img/wrong.gif" onclick="document.location.href=\'m_item.php?action=m_rights&subaction=switch&class_id='.$lijn[0].'&item_id='.$itemid.'&switch=manage&set=1\'" />';
  559. $tabel[$count][5] = '<img src="../img/wrong.gif" onclick="document.location.href=\'m_item.php?action=m_rights&subaction=switch&class_id='.$lijn[0].'&item_id='.$itemid.'&switch=view&set=1\'" />';
  560. }
  561. $tabel[$count][6] = $itemid."-".$lijn[0];
  562. }
  563. }
  564. return $tabel;
  565. }
  566. function set_new_right($item_id, $class_id, $column, $value) {
  567. $item_id = Database::escape_string($item_id);
  568. $class_id = Database::escape_string($class_id);
  569. $value = Database::escape_string($value);
  570. $column = Database::escape_string($column);
  571. $sql = "SELECT item_id FROM ".Rsys :: getTable("item_rights")."WHERE item_id=".$item_id." AND class_id=".$class_id;
  572. $result = Database::query($sql);
  573. $switcher = Database::num_rows($result);
  574. if ($switcher > 0) {
  575. $sql = $sql = "UPDATE ".Rsys :: getTable("item_rights")." SET ".$column."='".$value."' WHERE class_id = '".$class_id."' AND item_id ='".$item_id."'";
  576. Database::query($sql);
  577. } else {
  578. $sql = "INSERT INTO ".Rsys :: getTable("item_rights")." (item_id,class_id,".$column.") VALUES ('".$item_id."','".$class_id."','".$value."')";
  579. Database::query($sql);
  580. }
  581. }
  582. /**
  583. * Get number of itemrights
  584. *
  585. * @return - int The amount of itemrights
  586. */
  587. function get_num_itemrights() {
  588. $sql = "SELECT COUNT(id) FROM ".Database :: get_main_table(TABLE_MAIN_CLASS);
  589. return @ Database::result(Database::query($sql), 0, 0);
  590. }
  591. /**
  592. * Get all classes where the item hasn't already defined rights for
  593. *
  594. * @param - int $item_id The id of the item
  595. * @return - Array The returned rows
  596. */
  597. function get_itemfiltered_class($item_id) {
  598. $item_id = Database::escape_string($item_id);
  599. $sql = "SELECT * FROM ".Database :: get_main_table(TABLE_MAIN_CLASS)."
  600. WHERE id NOT IN (SELECT class_id FROM ".Rsys :: getTable("item_rights")." WHERE item_id='".$item_id."') ORDER BY name ASC, code ASC";
  601. $arr = Database::store_result(Database::query($sql));
  602. return $arr;
  603. }
  604. /**
  605. * Get number of classes where the item hasn't already defined rights for
  606. *
  607. * @param - int $item_id The id of the item
  608. * @return - int The amount
  609. */
  610. function get_num_itemfiltered_class($item_id) {
  611. $item_id = Database::escape_string($item_id);
  612. $sql = "SELECT COUNT(id) FROM ".Database :: get_main_table(TABLE_MAIN_CLASS)." WHERE id NOT IN (SELECT class_id FROM ".Rsys :: getTable("item_rights")." WHERE item_id='".$item_id."') ORDER BY name ASC, code ASC";
  613. return Database::result(Database::query($sql), 0, 0);
  614. }
  615. /**
  616. * Adds an item-right
  617. *
  618. * @param - int $item_id Item-ID
  619. * @param - int $class_id Class-ID
  620. * @param - int $edit Edit Right
  621. * @param - int $delete Delete Right
  622. * @param - int $m_reservation Manage reservations Right
  623. */
  624. function add_item_right($item_id, $class_id, $edit, $delete, $m_reservation) {
  625. if (!Rsys :: item_allow($item_id, 'm_rights'))
  626. return false;
  627. $sql = "INSERT INTO ".Rsys :: getTable("item_rights")." (item_id,class_id,edit_right,delete_right,m_reservation) VALUES ('".Database::escape_string($item_id)."','".Database::escape_string($class_id)."','".Database::escape_string($edit)."','".Database::escape_string($delete)."','".Database::escape_string($m_reservation)."')";
  628. Database::query($sql);
  629. }
  630. /**
  631. * Edits an item-right
  632. *
  633. * @param - int $item_id Item-ID
  634. * @param - int $class_id Class-ID
  635. * @param - int $edit Edit Right
  636. * @param - int $delete Delete Right
  637. * @param - int $m_reservation Manage reservations Right
  638. * @return - int The id
  639. */
  640. function edit_item_right($item_id, $class_id, $edit, $delete, $m_reservation) {
  641. $item_id = Database::escape_string($item_id);
  642. $class_id = Database::escape_string($class_id);
  643. if (!Rsys :: item_allow($item_id, 'm_rights'))
  644. return false;
  645. $sql = "UPDATE ".Rsys :: getTable("item_rights")." SET edit_right='".Database::escape_string($edit)."', delete_right='".Database::escape_string($delete)."', m_reservation='".Database::escape_string($m_reservation)."' WHERE class_id = '".$class_id."' AND item_id ='".$item_id."'";
  646. Database::query($sql);
  647. }
  648. /**
  649. * Deletes an item-right
  650. *
  651. * @param - int $id The id
  652. */
  653. function delete_item_right($item_id, $class_id) {
  654. $item_id = Database::escape_string($item_id);
  655. $class_id = Database::escape_string($class_id);
  656. if (!Rsys :: item_allow($item_id, 'm_rights'))
  657. return false;
  658. $sql = "DELETE FROM ".Rsys :: getTable("item_rights")." WHERE item_id='".$item_id."' AND class_id='".$class_id."'";
  659. Database::query($sql);
  660. }
  661. function get_class_group($class_id) {
  662. $class_id = Database::escape_string($class_id);
  663. $sql = "SELECT * FROM ".Database :: get_main_table(TABLE_MAIN_CLASS)." WHERE id='".$class_id."'";
  664. $arr = Database::store_result(Database::query($sql));
  665. return $arr;
  666. }
  667. function get_item_rights($item_id, $class_id) {
  668. $item_id = Database::escape_string($item_id);
  669. $class_id = Database::escape_string($class_id);
  670. $sql = "SELECT * FROM ".Rsys :: getTable('item_rights')." WHERE item_id='".$item_id."' AND class_id='".$class_id."'";
  671. $arr = Database::store_result(Database::query($sql));
  672. return $arr;
  673. }
  674. function black_out_changer($item_id) {
  675. $item_id = Database::escape_string($item_id);
  676. $sql = "SELECT blackout FROM ".Rsys :: getTable("item")." WHERE id='".$item_id."'";
  677. $Value = Database::store_result(Database::query($sql));
  678. ($Value[0][0] == 0 ? $changedValue = 1 : $changedValue = 0);
  679. $sql = "UPDATE ".Rsys :: getTable("item")." SET blackout='".$changedValue."' WHERE id = '".$item_id."'";
  680. Database::query($sql);
  681. Rsys :: black_out_notifier($item_id, $Value[0][0]);
  682. return $changedValue;
  683. }
  684. function black_out_notifier($item_id, $value) {
  685. $item_id = Database::escape_string($item_id);
  686. $value = Database::escape_string($value);
  687. $sql = "SELECT id, timepicker FROM ".Rsys :: getTable('reservation')."
  688. WHERE item_id='".$item_id."' AND subscribers > '0'";
  689. $value == 1 ? $sql .= " AND end_at >= (NOW()-7000000) " : $sql .= " AND end_at >= NOW()";
  690. $reservations = Database::query($sql);
  691. while ($reservation = Database::fetch_array($reservations)) {
  692. $sql = "SELECT user_id FROM ".Rsys :: getTable('subscription')." WHERE reservation_id='".$reservation[0]."'";
  693. if ($reservation[1] == 1) {
  694. $sql .= " AND end_at >= NOW() ";
  695. }
  696. $subscriptions = Database::query($sql);
  697. while ($subscription = Database::fetch_array($subscriptions)) {
  698. $user_info = api_get_user_info($subscription[0]);
  699. $sql2 = "SELECT name FROM ".Rsys :: getTable('item')." WHERE id='".$item_id."'";
  700. $items = Database::query($sql2);
  701. $item = Database::fetch_array($items);
  702. $item_name=$item['name'];
  703. if ($reservation[1] == 0)
  704. {
  705. //er wordt geen gebruik gemaakt van een timepicker dus begin en einddatum kan opgehaald worden uit reservation
  706. $sql2 = "SELECT start_at,end_at FROM ".Rsys :: getTable('reservation')." WHERE id='".$reservation[0]."'";
  707. }
  708. else
  709. {
  710. //er wordt gebruik gemaakt van een timepicker dus begin en einddatum kan opgehaald worden uit subscriptions
  711. $sql2 = "SELECT start_at,end_at FROM ".Rsys :: getTable('subscription')." WHERE reservation_id='".$reservation[0]."'";
  712. }
  713. $items = Database::query($sql2);
  714. $item = Database::fetch_array($items);
  715. $begindatum = $item['start_at'];
  716. $einddatum = $item['end_at'];
  717. if ($value==1) {
  718. $inhoud = str_replace('#NAME#', $item_name, get_lang('ReservationActive'));
  719. $inhoud = str_replace('#BEGIN#', $begindatum, $inhoud);
  720. $inhoud = str_replace('#BEGIN#', $einddatum, $inhoud);
  721. $titel = str_replace('#NAME#', $item_name, get_lang('ReservationAvailable'));
  722. } else {
  723. $inhoud = str_replace('#NAME#', $item_name, get_lang('ReservationCancelled'));
  724. $inhoud = str_replace('#BEGIN#', $begindatum, $inhoud);
  725. $inhoud = str_replace('#BEGIN#', $einddatum, $inhoud);
  726. $titel = str_replace('#NAME#', $item_name, get_lang('ReservationUnavailable'));
  727. }
  728. api_send_mail($user_info['mail'], $titel, $inhoud);
  729. }
  730. }
  731. }
  732. /*
  733. ============================================================================================
  734. RESERVATION PERIODS
  735. ============================================================================================
  736. */
  737. function recurrence_list() {
  738. $arr['1'] = get_lang('EveryDay');
  739. $arr['7'] = get_lang('EveryWeek');
  740. //$arr['month'] = get_lang('EveryMonth');
  741. return $arr;
  742. }
  743. function check_date($item_id, $start_date, $end_date, $start_at, $end_at) {
  744. $item_id = Database::escape_string($item_id);
  745. $start_date = Database::escape_string($start_date);
  746. $end_date = Database::escape_string($end_date);
  747. $start_at = Database::escape_string($start_at);
  748. $end_at = Database::escape_string($end_at);
  749. $sql = "SELECT * FROM ".Rsys :: getTable('reservation')." WHERE item_id='".$item_id."' ORDER BY start_at";
  750. $result = Database::query($sql);
  751. while ($array = Database::fetch_array($result)) {
  752. $GLOBALS['start_date'] = $array[5];
  753. $GLOBALS['end_date'] = $array[6];
  754. if (Rsys :: mysql_datetime_to_timestamp($array[5]) <= $start_date && Rsys :: mysql_datetime_to_timestamp($array[6]) >= $start_date) {
  755. return $array[0];
  756. }
  757. if (Rsys :: mysql_datetime_to_timestamp($array[5]) <= $end_date && Rsys :: mysql_datetime_to_timestamp($array[6]) >= $end_date)
  758. return $array[0];
  759. }
  760. $sql = "SELECT id, start_at, end_at FROM ".Rsys :: getTable('reservation')."
  761. WHERE ((start_at > '".$start_at."' AND
  762. start_at < '".$end_at."') OR
  763. (end_at > '".$start_at."' AND
  764. end_at < '".$end_at."') OR (start_at <= '".$start_at."' AND end_at >= '".$end_at."')) AND item_id='".$item_id."'";
  765. $result = Database::fetch_array(Database::query($sql));
  766. if (count($result) != 0){
  767. $GLOBALS['start_date'] = $result[1];
  768. $GLOBALS['end_date'] = $result[2];
  769. return $result[0];
  770. }
  771. return 0;
  772. }
  773. function check_date_edit($item_id, $start_date, $end_date, $start_at, $end_at, $reservation_id) {
  774. $item_id = Database::escape_string($item_id);
  775. $reservation_id = Database::escape_string($reservation_id);
  776. $start_date = Database::escape_string($start_date);
  777. $end_date = Database::escape_string($end_date);
  778. $start_at = Database::escape_string($start_at);
  779. $end_at = Database::escape_string($end_at);
  780. $sql = "SELECT * FROM ".Rsys :: getTable('reservation')." WHERE item_id='".$item_id."' AND id <> '".$reservation_id."' ORDER BY start_at";
  781. $result = Database::query($sql);
  782. while ($array = Database::fetch_array($result)) {
  783. $GLOBALS['start_date'] = $array[5];
  784. $GLOBALS['end_date'] = $array[6];
  785. if (Rsys :: mysql_datetime_to_timestamp($array[5]) < $start_date && Rsys :: mysql_datetime_to_timestamp($array[6]) > $start_date) {
  786. return $array[0];
  787. }
  788. if (Rsys :: mysql_datetime_to_timestamp($array[5]) < $end_date && Rsys :: mysql_datetime_to_timestamp($array[6]) > $end_date)
  789. return $array[0];
  790. }
  791. $sql = "SELECT id FROM ".Rsys :: getTable('reservation')."
  792. WHERE ((start_at > '".$start_at."' AND
  793. start_at < '".$end_at."') OR
  794. (end_at > '".$start_at."' AND
  795. end_at < '".$end_at."') OR
  796. (start_at <= '".$start_at."' AND
  797. end_at >= '".$end_at."')) AND item_id='".$item_id."' AND id <> '".$reservation_id."'";
  798. $result = Database::fetch_array(Database::query($sql));
  799. if (count($result) != 0){
  800. $GLOBALS['start_date'] = $result[1];
  801. $GLOBALS['end_date'] = $result[2];
  802. return $result[0];
  803. }
  804. return 0;
  805. }
  806. function get_category_rights() {
  807. $sql = "SELECT cat.id as catid,cat.name as catname
  808. FROM ".Rsys :: getTable('category')." cat
  809. LEFT JOIN ".Rsys :: getTable('item')." i ON cat.id=i.category_id
  810. LEFT JOIN ".Rsys :: getTable('item_rights')." ir ON ir.item_id=i.id
  811. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = i.id
  812. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id
  813. WHERE (cu.user_id='".api_get_user_id()."' AND ir.m_reservation=1 ) OR i.creator='".api_get_user_id()."' OR 1=". (api_is_platform_admin() ? 1 : 0)." ORDER BY cat.name ASC";
  814. $result = Database::query($sql);
  815. while ($array = Database::fetch_array($result))
  816. $arr[$array['catid']] = $array['catname'];
  817. return $arr;
  818. }
  819. /**
  820. * Returns an array with items from a category linked to rights(used by m_reservations.php)
  821. */
  822. function get_cat_r_items($category) {
  823. $category = Database::escape_string($category);
  824. $sql = "SELECT i.id,i.name as catitem
  825. FROM ".Rsys :: getTable('item')." i
  826. INNER JOIN ".Rsys :: getTable('category')." cat ON cat.id=i.category_id
  827. LEFT JOIN ".Rsys :: getTable('item_rights')." ir ON ir.item_id=i.id
  828. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = i.id
  829. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id
  830. WHERE ((cu.user_id='".api_get_user_id()."' AND ir.m_reservation=1 ) OR i.creator='".api_get_user_id()."' OR 1=". (api_is_platform_admin() ? 1 : 0).") AND (category_id =".$category.")
  831. ORDER BY cat.name ASC, i.name ASC";
  832. $result = Database::query($sql);
  833. while ($array = Database::fetch_array($result))
  834. $arr[$array['id']] = $array['catitem'];
  835. return $arr;
  836. }
  837. /**
  838. * Returns an array with [ itemID => "category/item" ] with view_rights (used by reservation.php)
  839. */
  840. function get_cat_items($category) {
  841. $category = Database::escape_string($category);
  842. $sql = "SELECT i.id,i.name as catitem
  843. FROM ".Rsys :: getTable('item')." i
  844. INNER JOIN ".Rsys :: getTable('category')." cat ON cat.id=i.category_id
  845. LEFT JOIN ".Rsys :: getTable('item_rights')." ir ON ir.item_id=i.id
  846. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = i.id
  847. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id
  848. WHERE ((cu.user_id='".api_get_user_id()."' AND ir.view_right=1 ) OR i.creator='".api_get_user_id()."' OR 1=". (api_is_platform_admin() ? 1 : 0).") AND (category_id =".$category.")
  849. ORDER BY cat.name ASC, i.name ASC";
  850. $result = Database::query($sql);
  851. while ($array = Database::fetch_array($result))
  852. $arr[$array['id']] = $array['catitem'];
  853. return $arr;
  854. }
  855. /**
  856. * Returns the reservations for sortable table based on the params
  857. *
  858. * @param - int $from Index of the first item to return.
  859. * @param - int $per_page The number of items to return
  860. * @param - int $column The number of the column on which the data should be sorted
  861. * @param - String $direction In which order should the data be sorted (ASC or DESC)
  862. * @return - Array The returned rows
  863. */
  864. function get_table_reservations($from, $per_page, $column, $direction) {
  865. $sql = "SELECT DISTINCT r.id AS col0, i.name AS col1, DATE_FORMAT(r.start_at,'%Y-%m-%d %H:%i') AS col2, DATE_FORMAT(r.end_at,'%Y-%m-%d %H:%i') AS col3," .
  866. "DATE_FORMAT(r.subscribe_from,'%Y-%m-%d %k:%i') AS col4, DATE_FORMAT(r.subscribe_until,'%Y-%m-%d %k:%i') AS col5,IF(timepicker <> 0, '".get_lang('TimePicker')."',CONCAT(r.subscribers,'/',r.max_users)) AS col6, r.notes AS col7, r.id as col8
  867. FROM ".Rsys :: getTable('reservation')." r
  868. INNER JOIN ".Rsys :: getTable('item')." i ON r.item_id=i.id
  869. LEFT JOIN ".Rsys :: getTable('item_rights')." ir ON ir.item_id=i.id
  870. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = i.id
  871. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id
  872. WHERE ((ir.m_reservation=1 AND cu.user_id='".api_get_user_id()."') OR i.creator='".api_get_user_id()."' OR 1=". (api_is_platform_admin() ? 1 : 0).")";
  873. if (isset ($_GET['keyword'])) {
  874. $keyword = Database::escape_string(trim($_GET['keyword']));
  875. $sql .= "AND (i.name LIKE '%".$keyword."%' OR i.description LIKE '%".$keyword."%' OR r.notes LIKE '%".$keyword."%')";
  876. }
  877. $from = intval($from);
  878. $per_page = intval($per_page);
  879. $column = intval($column);
  880. if(!in_array($direction, array('ASC','DESC'))) {
  881. $direction = 'ASC';
  882. }
  883. $sql .= " ORDER BY col".$column." ".$direction." LIMIT ".$from.",".$per_page;
  884. $result = Database::query($sql);
  885. while ($array = Database::fetch_array($result, 'NUM')) {
  886. $arr[] = $array;
  887. }
  888. return $arr;
  889. }
  890. function check_edit_right($id) {
  891. $id = Database::escape_string($id);
  892. $sql = "SELECT r.id
  893. FROM ".Rsys :: getTable('reservation')." r
  894. INNER JOIN ".Rsys :: getTable('item')." i ON r.item_id=i.id
  895. LEFT JOIN ".Rsys :: getTable('item_rights')." ir ON ir.item_id=i.id
  896. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = i.id
  897. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id
  898. WHERE ((cu.user_id='".api_get_user_id()."'AND ir.edit_right=1) OR 1=". (api_is_platform_admin() ? 1 : 0).") AND r.id='".$id."'";
  899. $result = Database::query($sql);
  900. while ($array = Database::fetch_array($result, 'NUM')) {
  901. $arr[] = $array;
  902. }
  903. return $result;
  904. }
  905. function check_delete_right($id) {
  906. $id = Database::escape_string($id);
  907. $sql = "SELECT r.id
  908. FROM ".Rsys :: getTable('reservation')." r
  909. INNER JOIN ".Rsys :: getTable('item')." i ON r.item_id=i.id
  910. LEFT JOIN ".Rsys :: getTable('item_rights')." ir ON ir.item_id=i.id
  911. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = i.id
  912. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id
  913. WHERE ((cu.user_id='".api_get_user_id()."'AND ir.delete_right=1) OR 1=". (api_is_platform_admin() ? 1 : 0).") AND r.id='".$id."'";
  914. $result = Database::query($sql);
  915. while ($array = Database::fetch_array($result, 'NUM')) {
  916. $arr[] = $array;
  917. }
  918. return $arr;
  919. }
  920. function check_auto_accept($id) {
  921. $id = Database::escape_string($id);
  922. $sql = "SELECT auto_accept FROM ".Rsys :: getTable('reservation')." WHERE id='".$id."'";
  923. return Database::result(Database::query($sql), 0, 0);
  924. }
  925. /**
  926. * Get number of reservations
  927. *
  928. * @return - int The amount
  929. */
  930. function get_num_reservations() {
  931. $sql = "SELECT COUNT(DISTINCT r.id)
  932. FROM ".Rsys :: getTable('reservation')." r
  933. LEFT JOIN ".Rsys :: getTable('item')." i ON i.id=r.item_id
  934. LEFT JOIN ".Rsys :: getTable('item_rights')." ir ON ir.item_id=r.item_id
  935. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = r.item_id
  936. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id
  937. WHERE ((ir.m_reservation=1 AND cu.user_id='".api_get_user_id()."') OR i.creator='".api_get_user_id()."' OR 1=". (api_is_platform_admin() ? 1 : 0).')';
  938. if (isset ($_GET['keyword'])) {
  939. $keyword = Database::escape_string(trim($_GET['keyword']));
  940. $sql .= " AND (i.name LIKE '%".$keyword."%' OR i.description LIKE '%".$keyword."%' OR r.notes LIKE '%".$keyword."%')";
  941. }
  942. return Database::result(Database::query($sql), 0, 0);
  943. }
  944. /**
  945. * Adds a reservation
  946. *
  947. * @param - $item_id,$auto_accept,$max_users,$start_at,$end_at,$subscribe_until,$notes
  948. * @return - FALSE if there is something wrong with the dates, a mysql_insert_id() if everything went perfectly
  949. */
  950. function add_reservation($item_id, $auto_accept, $max_users, $start_at, $end_at, $subscribe_from, $subscribe_until, $notes, $timepicker, $min, $max,$subid) {
  951. $stamp_start = Rsys :: mysql_datetime_to_timestamp($start_at);
  952. $stamp_end = Rsys :: mysql_datetime_to_timestamp($end_at);
  953. $stamp_start_date = date( 'Y-m-d',$stamp_start);
  954. $stamp_end_date = date( 'Y-m-d',$stamp_end);
  955. if (Rsys :: check_date($item_id, $stamp_start, $stamp_end, $start_at, $end_at) <> 0)
  956. return 1;
  957. if ($subscribe_until != 0) {
  958. $stamp_until = Rsys :: mysql_datetime_to_timestamp($subscribe_until);
  959. if ($stamp_until > $stamp_start)
  960. return 2;
  961. }
  962. if ($start_at < (date( 'Y-m-d H:i:s',time())))
  963. return 3;
  964. if (($stamp_start_date != $stamp_end_date) && $timepicker == '1')
  965. {
  966. return 4;
  967. }
  968. if($timepicker == '0')
  969. {
  970. if ($min != '0' || $max != '0')
  971. {
  972. //kan niet verschillen van 0!
  973. return 5;
  974. }
  975. }
  976. else
  977. {
  978. if (!($max==0 && $min==0))
  979. {
  980. if ($max < $min)
  981. {
  982. return 6;
  983. //maximum kan niet kleiner zijn dan minimum
  984. }
  985. else
  986. {
  987. $stamp = ($stamp_end - $stamp_start)/ 60;
  988. if (($stamp/$max)<1)
  989. {
  990. return 7;
  991. // er past geen blok van het tijdverschil
  992. }
  993. }
  994. }
  995. }
  996. $sql = "INSERT INTO ".Rsys :: getTable("reservation")." (item_id,auto_accept,max_users,start_at,end_at,subscribe_from,subscribe_until,notes,timepicker,timepicker_min,timepicker_max,subid) VALUES ('".Database::escape_string($item_id)."','".Database::escape_string($auto_accept)."','". (intval($max_users) > 1 ? $max_users : 1)."','".Database::escape_string($start_at)."','".Database::escape_string($end_at)."','".Database::escape_string($subscribe_from)."','".Database::escape_string($subscribe_until)."','".Database::escape_string($notes)."','".$timepicker."','".$min."','".$max."','". ($subid == 0 ? 0 : $subid)."')";
  997. Database::query($sql);
  998. return 0;
  999. }
  1000. /**
  1001. * Edits a reservation
  1002. *
  1003. * @param - int $id The reservation-ID
  1004. * @param - $item_id,$auto_accept,$max_users,$start_at,$end_at,$subscribe_until,$notes
  1005. * @return - FALSE if there is something wrong with the dates, TRUE if everything went perfectly
  1006. *
  1007. */
  1008. function edit_reservation($id, $item_id, $auto_accept, $max_users, $start_at, $end_at, $subscribe_from, $subscribe_until, $notes, $timepicker) {
  1009. $id = Database::escape_string($id);
  1010. if (!Rsys :: item_allow($item_id, 'm_reservation'))
  1011. return false;
  1012. $stamp_start = Rsys :: mysql_datetime_to_timestamp($start_at);
  1013. $stamp_end = Rsys :: mysql_datetime_to_timestamp($end_at);
  1014. $stamp_start_date = date( 'Y-m-d',$stamp_start);
  1015. $stamp_end_date = date( 'Y-m-d',$stamp_end);
  1016. if (Rsys :: check_date_edit($item_id, $stamp_start, $stamp_end, $start_at, $end_at, $id) <> 0)
  1017. return 1;
  1018. if ($subscribe_until != 0) {
  1019. $stamp_until = Rsys :: mysql_datetime_to_timestamp($subscribe_until);
  1020. if ($stamp_until > $stamp_start)
  1021. return 2;
  1022. }
  1023. $sql = "SELECT timepicker, subscribers FROM ".Rsys :: getTable("reservation")." WHERE id='".$id."'";
  1024. $result = Database::fetch_array(Database::query($sql));
  1025. if ($result[0] == 0 && $result[1] > $max_users) {
  1026. return 3;
  1027. }
  1028. if (($stamp_start_date != $stamp_end_date) && $timepicker == '1')
  1029. {
  1030. return 4;
  1031. }
  1032. if ($auto_accept == 1) {
  1033. $sql = "SELECT dummy FROM ".Rsys :: getTable("subscription")." WHERE reservation_id='".$id."'";
  1034. $result = Database::query($sql);
  1035. while ($array = Database::fetch_array($result, 'NUM')) {
  1036. Rsys :: set_accepted($array[0], 1);
  1037. }
  1038. } else {
  1039. $auto_accept = 0;
  1040. }
  1041. $sql = "UPDATE ".Rsys :: getTable("reservation")." SET item_id='".Database::escape_string($item_id)."',auto_accept='".Database::escape_string($auto_accept)."',max_users='". ($max_users > 1 ? $max_users : 1)."',start_at='".Database::escape_string($start_at)."',end_at='".Database::escape_string($end_at)."',subscribe_from='".Database::escape_string($subscribe_from)."',subscribe_until='".Database::escape_string($subscribe_until)."',notes='".Database::escape_string($notes)."' WHERE id='".$id."'";
  1042. Database::query($sql);
  1043. return 0;
  1044. }
  1045. /**
  1046. * Deletes a reservation
  1047. */
  1048. function delete_reservation($id) {
  1049. $id = Database::escape_string($id);
  1050. $sql = "SELECT id FROM ".Rsys :: getTable("reservation")."WHERE id='".$id."' OR subid='".$id."'";
  1051. $result2 = Database::query($sql);
  1052. while ($arr = Database::fetch_array($result2, 'NUM')) {
  1053. $sql = "SELECT s.dummy, s.user_id, i.name, r.start_at, r.end_at
  1054. FROM ".Rsys :: getTable("subscription")." s
  1055. INNER JOIN ".Rsys :: getTable("reservation")." r ON s.reservation_id = r.id
  1056. INNER JOIN ".Rsys :: getTable("item")." i ON r.item_id = i.id
  1057. WHERE s.reservation_id='".$arr[0]."'";
  1058. $result = Database::query($sql);
  1059. while ($array = Database::fetch_array($result, 'NUM')) {
  1060. $user_info = api_get_user_info($array[1]);
  1061. api_send_mail($user_info['mail'], str_replace('#NAME#', $array[2], get_lang("ReservationDeleteTitle")), str_replace('#START#', $array[3], str_replace('#END#', $array[4], str_replace('#NAME#', $array[2], get_lang("ReservationDeleteMessage")))));
  1062. $sql = "DELETE FROM ".Rsys :: getTable("subscription")." WHERE dummy='".$array[0]."'";
  1063. Database::query($sql);
  1064. }
  1065. $sql = "DELETE FROM ".Rsys :: getTable("reservation")." WHERE id='".$arr[0]."'";
  1066. Database::query($sql);
  1067. }
  1068. }
  1069. function is_owner_item($id) {
  1070. $id = Database::escape_string($id);
  1071. $sql = "SELECT creator FROM ".Rsys :: getTable('item')." i ,".Rsys :: getTable('reservation')." r
  1072. where i.id = r.item_id
  1073. and r.id = '".$id."'
  1074. and i.creator ='".api_get_user_id()."'";
  1075. $result = Database::query($sql);
  1076. if (Database::num_rows($result) != 0)
  1077. return 1;
  1078. return 0;
  1079. }
  1080. function get_reservation($id) {
  1081. $id = Database::escape_string($id);
  1082. $sql = "SELECT *
  1083. FROM ".Rsys :: getTable('reservation')." r
  1084. INNER JOIN ".Rsys :: getTable('item')." i ON r.item_id=i.id
  1085. LEFT JOIN ".Rsys :: getTable('item_rights')." ir ON ir.item_id=i.id
  1086. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = i.id
  1087. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id
  1088. WHERE (cu.user_id='".api_get_user_id()."' OR 1=". (api_is_platform_admin() ? 1 : 0)." OR 1=".(Rsys :: is_owner_item("$id")? 1 : 0).") AND r.id='".$id."'";
  1089. $result = Database::query($sql);
  1090. while ($array = Database::fetch_array($result, 'NUM'))
  1091. $arr[] = $array;
  1092. return $arr;
  1093. }
  1094. function get_num_subscriptions_overview() {
  1095. $sql = "SELECT COUNT(s.reservation_id)
  1096. FROM ".Rsys :: getTable('subscription')." s, ".Rsys :: getTable('reservation')." r1, ".Database :: get_main_table(TABLE_MAIN_USER)." u," .Rsys :: getTable('item')." i1
  1097. where r1.id = s.reservation_id
  1098. and i1.id = r1.item_id
  1099. and u.user_id = s.user_id
  1100. and s.reservation_id IN
  1101. (SELECT DISTINCT(r2.id)
  1102. FROM ".Rsys :: getTable('reservation')." r2
  1103. LEFT JOIN ".Rsys :: getTable('item')." i2 ON i2.id=r2.item_id
  1104. LEFT JOIN ".Rsys :: getTable('item_rights')." ir ON ir.item_id=r2.item_id
  1105. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = r2.item_id
  1106. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id
  1107. WHERE ((ir.m_reservation=1 AND cu.user_id='".api_get_user_id()."')
  1108. OR i2.creator='".api_get_user_id()."'
  1109. OR 1=". (api_is_platform_admin() ? 1 : 0)."))";
  1110. if (isset ($_GET['keyword'])) {
  1111. $keyword = Database::escape_string(trim($_GET['keyword']));
  1112. $sql .= " AND (i1.name LIKE '%".$keyword."%' or r1.start_at LIKE '%".$keyword."%' or r1.end_at LIKE '%".$keyword."%' or u.lastname LIKE '%".$keyword."%' or u.firstname LIKE '%".$keyword."%' or s.start_at LIKE '%".$keyword."%' or s.end_at LIKE '%".$keyword."%')";
  1113. }
  1114. return Database::result(Database::query($sql), 0, 0);
  1115. }
  1116. function get_table_subcribed_reservations($from, $per_page, $column, $direction) {
  1117. $from = intval($from);
  1118. $per_page = intval($per_page);
  1119. $column = intval($column);
  1120. if(!in_array($direction, array('ASC','DESC'))) {
  1121. $direction = 'ASC';
  1122. }
  1123. $sql = "SELECT i1.name as col0,c.name as col1,
  1124. DATE_FORMAT(r1.start_at ,'%Y-%m-%d %H:%i') as col2,
  1125. DATE_FORMAT(r1.end_at ,'%Y-%m-%d %H:%i') as col3, ".(api_is_western_name_order() ? "CONCAT(u.firstname,' ',u.lastname)" : "CONCAT(u.lastname,' ',u.firstname)")." as col4,
  1126. DATE_FORMAT(s.start_at ,'%Y-%m-%d %H:%i') as col5,
  1127. DATE_FORMAT(s.end_at ,'%Y-%m-%d %H:%i') as col6, s.accepted as col7
  1128. FROM ".Rsys :: getTable('subscription')." s, ".Rsys :: getTable('reservation')." r1, ".Database :: get_main_table(TABLE_MAIN_USER)." u," .Rsys :: getTable('item')." i1,".Rsys :: getTable('category')." c
  1129. WHERE r1.id = s.reservation_id
  1130. and c.id = i1.category_id
  1131. and i1.id = r1.item_id
  1132. and u.user_id = s.user_id
  1133. and s.reservation_id IN
  1134. (SELECT DISTINCT(r2.id)
  1135. FROM ".Rsys :: getTable('reservation')." r2
  1136. LEFT JOIN ".Rsys :: getTable('item')." i2 ON i2.id=r2.item_id
  1137. LEFT JOIN ".Rsys :: getTable('item_rights')." ir ON ir.item_id=r2.item_id
  1138. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = r2.item_id
  1139. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id
  1140. WHERE ((ir.m_reservation=1 AND cu.user_id='".api_get_user_id()."')
  1141. OR i2.creator='".api_get_user_id()."'
  1142. OR 1=". (api_is_platform_admin() ? 1 : 0)."))";
  1143. if (isset ($_GET['keyword'])) {
  1144. $keyword = Database::escape_string(trim($_GET['keyword']));
  1145. $sql .= " AND (i1.name LIKE '%".$keyword."%' or c.name LIKE '%".$keyword."%' or r1.start_at LIKE '%".$keyword."%' or r1.end_at LIKE '%".$keyword."%' or u.lastname LIKE '%".$keyword."%' or u.firstname LIKE '%".$keyword."%' or s.start_at LIKE '%".$keyword."%' or s.end_at LIKE '%".$keyword."%')";
  1146. }
  1147. $sql .= " ORDER BY col".$column." ".$direction." LIMIT ".$from.",".$per_page;
  1148. /*$result = Database::query($sql);
  1149. while ($array = Database::fetch_array($result, 'NUM'))
  1150. $arr[] = $array;*/
  1151. $result = Database::query($sql);
  1152. while ($array = Database::fetch_array($result, 'NUM')) {
  1153. $row = array();
  1154. $row[] = $array[0];
  1155. $row[] = $array[1];
  1156. $row[] = $array[2];
  1157. $row[] = $array[3];
  1158. $row[] = $array[4];
  1159. if ($array[5]=='0000-00-00 00:00') {
  1160. $row[] = $array[2];
  1161. }
  1162. else {
  1163. $row[] = $array[5];
  1164. }
  1165. if ($array[6]=='0000-00-00 00:00') {
  1166. $row[] = $array[3];
  1167. }
  1168. else {
  1169. $row[] = $array[6];
  1170. }
  1171. if ($array[7]=='1')
  1172. {
  1173. $row[] = get_lang('Yes');
  1174. }
  1175. else {
  1176. $row[] = get_lang('No');
  1177. }
  1178. $arr[] = $row;
  1179. }
  1180. return $arr;
  1181. }
  1182. function get_num_waiting_users() {
  1183. $sql = "SELECT COUNT(DISTINCT dummy) FROM ".Rsys :: getTable('subscription');
  1184. if (isset ($_GET['rid'])) {
  1185. $sql .= " WHERE reservation_id = '".intval($_GET['rid'])."'";
  1186. }
  1187. return Database::result(Database::query($sql), 0, 0);
  1188. }
  1189. function get_table_waiting_users($from, $per_page, $column, $direction) {
  1190. $from = intval($from);
  1191. $per_page = intval($per_page);
  1192. $column = intval($column);
  1193. if(!in_array($direction, array('ASC','DESC'))) {
  1194. $direction = 'ASC';
  1195. }
  1196. /*$sql = "SELECT dummy AS col0, ".(api_is_western_name_order() ? "CONCAT(u.firstname,' ',u.lastname)" : "CONCAT(u.lastname,' ',u.firstname)")." AS col1, s.user_id AS col2, accepted AS col3
  1197. FROM ".Rsys :: getTable('subscription')." s
  1198. INNER JOIN ".Database :: get_main_table(TABLE_MAIN_USER)." u ON s.user_id = u.user_id ";
  1199. if (!empty ($_GET['rid'])) {
  1200. $sql .= " WHERE s.reservation_id = '".$_GET['rid']."'";
  1201. }
  1202. $sql .= " ORDER BY col".$column." ".$direction." LIMIT ".$from.",".$per_page;*/
  1203. $sql = "SELECT dummy AS col0, ".(api_is_western_name_order() ? "CONCAT(u.firstname,' ',u.lastname)" : "CONCAT(u.lastname,' ',u.firstname)")." AS col1, s.user_id AS col2, accepted AS col3, r.start_at, r.end_at, s.start_at, s.end_at
  1204. FROM ".Rsys :: getTable('subscription')." s,".Database :: get_main_table(TABLE_MAIN_USER)." u,".Database :: get_main_table(TABLE_MAIN_RESERVATION_RESERVATION)." r
  1205. where u.user_id = s.user_id
  1206. and s.reservation_id = r.id";
  1207. if (!empty ($_GET['rid'])) {
  1208. $sql .= " and r.id = '".Database::escape_string($_GET['rid'])."'";
  1209. }
  1210. $sql .= " ORDER BY col".$column." ".$direction." LIMIT ".$from.",".$per_page;
  1211. $result = Database::query($sql);
  1212. while ($array = Database::fetch_array($result, 'NUM')) {
  1213. $arr[] = $array;
  1214. }
  1215. $count = 0;
  1216. $x = count($arr);
  1217. while ($count < $x) {
  1218. $sql = "SELECT name
  1219. FROM ".Database :: get_main_table(TABLE_MAIN_CLASS)." cl
  1220. INNER JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = cl.id
  1221. WHERE cu.user_id=".$arr[$count][2]." LIMIT 1";
  1222. $result = Database::query($sql);
  1223. while ($array = Database::fetch_array($result, 'NUM')) {
  1224. $arr2[] = $array;
  1225. }
  1226. $arr[$count][2] = $arr2[0][0];
  1227. $count ++;
  1228. }
  1229. $count = -1;
  1230. if (is_array($arr)) {
  1231. foreach ($arr as $lijn) {
  1232. $count ++;
  1233. $controle = false;
  1234. $tabel[$count][0] = $lijn[0];
  1235. $tabel[$count][1] = $lijn[1];
  1236. if ($lijn[3] == 0) {
  1237. $tabel[$count][5] = '<img src="../img/wrong.gif" onclick="document.location.href=\'m_reservation.php?action=accept&rid='.$_GET['rid'].'&amp;dummy='.$lijn[0].'&switch=edit&set=1\'" />';
  1238. } else {
  1239. $tabel[$count][5] = '<img src="../img/right.gif" onclick="document.location.href=\'m_reservation.php?action=accept&rid='.$_GET['rid'].'&amp;dummy='.$lijn[0].'&switch=edit&set=0\'" />';
  1240. }
  1241. $tabel[$count][2] = $lijn[2];
  1242. if ($lijn[6] == '0000-00-00 00:00:00' && $lijn[7] == '0000-00-00 00:00:00')
  1243. {
  1244. $tabel[$count][3] = $lijn[4];
  1245. $tabel[$count][4] = $lijn[5];
  1246. }
  1247. else
  1248. {
  1249. $tabel[$count][3] = $lijn[6];
  1250. $tabel[$count][4] = $lijn[7];
  1251. }
  1252. $tabel[$count][6] = '<img src="../img/wrong.gif" onclick="document.location.href=\'m_reservation.php?action=accept&rid='.$_GET['rid'].'&amp;dummy='.$lijn[0].'&switch=delete\'" />';
  1253. }
  1254. }
  1255. return $tabel;
  1256. }
  1257. function set_accepted($id, $value) {
  1258. global $subscription;
  1259. $id = Database::escape_string($id);
  1260. $value = Database::escape_string($value);
  1261. $sql = "UPDATE ".Rsys :: getTable('subscription')." SET ACCEPTED='".$value."' WHERE dummy='".$id."'";
  1262. Database::query($sql);
  1263. $user_info = api_get_user_info($subscription[0]);
  1264. $sql = "SELECT name FROM ".Rsys :: getTable('subscription')." s
  1265. INNER JOIN ".Rsys :: getTable('reservation')." r ON s.reservation_id = r.id
  1266. INNER JOIN ".Rsys :: getTable('item')." i ON r.item_id = i.id
  1267. WHERE dummy='".$id."'";
  1268. $items = Database::query($sql);
  1269. $item = Database::fetch_array($items);
  1270. $item_name = $item[0];
  1271. $sql = "SELECT start_at, end_at, timepicker
  1272. from ".Rsys :: getTable('reservation')."
  1273. where id in ( SELECT reservation_id
  1274. from ".Rsys :: getTable('subscription')."
  1275. where dummy ='".$id."')";
  1276. $items = Database::query($sql);
  1277. $item = Database::fetch_array($items);
  1278. if ($item['timepicker'] == '1')
  1279. {
  1280. $sql = "SELECT start_at, end_at
  1281. from ".Rsys :: getTable('subscription')."
  1282. where dummy ='".$id."'";
  1283. $items = Database::query($sql);
  1284. $item = Database::fetch_array($items);
  1285. }
  1286. $begin_datum = $item['start_at'];
  1287. $eind_datum = $item['end_at'];
  1288. if ($value==1) {
  1289. $titel = str_replace('#ITEM#', $item_name, get_lang('ReservationAccepted'));
  1290. $inhoud = str_replace('#ITEM#', $item_name, get_lang('ReservationForItemAccepted'));
  1291. } else {
  1292. $titel = str_replace('#ITEM#', $item_name, get_lang('ReservationDenied'));
  1293. $inhoud = str_replace('#ITEM#', $item_name, get_lang('ReservationForDenied'));
  1294. }
  1295. $inhoud = str_replace('#BEGIN', $begin_datum, $inhoud);
  1296. $inhoud = str_replace('#END', $eind_datum, $inhoud);
  1297. api_send_mail($user_info['mail'], $titel, $inhoud);
  1298. }
  1299. /*
  1300. ============================================================================================
  1301. RESERVATION
  1302. ============================================================================================
  1303. */
  1304. function check_date_subscription($reservation_id, $start_at, $end_at) {
  1305. $reservation_id = Database::escape_string($reservation_id);
  1306. $start_at = Database::escape_string($start_at);
  1307. $end_at = Database::escape_string($end_at);
  1308. $sql = "SELECT id, start_at, end_at FROM ".Rsys :: getTable('reservation')."
  1309. WHERE start_at > '".$start_at."' AND id='".$reservation_id."' ";
  1310. $result = Database::query($sql);
  1311. if (Database::num_rows($result) != 0){
  1312. $result2 = Database::fetch_array($result);
  1313. $GLOBALS['start_date'] = $result2[1];
  1314. $GLOBALS['end_date'] = $result2[2];
  1315. return 1;
  1316. }
  1317. $sql = "SELECT id, start_at, end_at FROM ".Rsys :: getTable('reservation')."
  1318. WHERE end_at < '".$end_at."' AND id='".$reservation_id."' ";
  1319. $result = Database::query($sql);
  1320. if (Database::num_rows($result) != 0){
  1321. $result2 = Database::fetch_array($result);
  1322. $GLOBALS['start_date'] = $result2[1];
  1323. $GLOBALS['end_date'] = $result2[2];
  1324. return 1;
  1325. }
  1326. $sql = "SELECT * FROM ".Rsys :: getTable('subscription')." WHERE reservation_id='".$reservation_id."' ORDER BY start_at";
  1327. $result = Database::query($sql);
  1328. while ($array = Database::fetch_array($result)) {
  1329. $GLOBALS['start_date'] = $array[4];
  1330. $GLOBALS['end_date'] = $array[5];
  1331. if (Rsys :: mysql_datetime_to_timestamp($array[4]) < Rsys :: mysql_datetime_to_timestamp($start_at) && Rsys :: mysql_datetime_to_timestamp($array[5]) > Rsys :: mysql_datetime_to_timestamp($start_at)) {
  1332. return $array[0];
  1333. }
  1334. if (Rsys :: mysql_datetime_to_timestamp($array[4]) < Rsys :: mysql_datetime_to_timestamp($end_at) && Rsys :: mysql_datetime_to_timestamp($array[5]) > Rsys :: mysql_datetime_to_timestamp($end_at))
  1335. return $array[0];
  1336. }
  1337. $sql = "SELECT dummy, start_at ,end_at FROM ".Rsys :: getTable('subscription')."
  1338. WHERE ((start_at > '".$start_at."' AND
  1339. start_at < '".$end_at."') OR
  1340. (end_at > '".$start_at."' AND
  1341. end_at < '".$end_at."')OR
  1342. (start_at <= '".$start_at."' AND
  1343. end_at >= '".$end_at."')) AND reservation_id='".$reservation_id."' ";
  1344. $result = Database::fetch_array(Database::query($sql));
  1345. if (count($result) != 0){
  1346. $GLOBALS['start_date'] = $result[1];
  1347. $GLOBALS['end_date'] = $result[2];
  1348. return $result[0];
  1349. }
  1350. return 0;
  1351. }
  1352. function check_date_month_calendar($date, $itemid) {
  1353. $itemid = Database::escape_string($itemid);
  1354. $date = Database::escape_string($date);
  1355. $sql = "SELECT id FROM ".Rsys :: getTable('reservation')."
  1356. WHERE ((DATE_FORMAT(start_at, '%Y-%m-%e') = '".$date."' OR DATE_FORMAT(end_at, '%Y-%m-%e') = '".$date."'
  1357. OR (start_at <= '".$date." 00:00:00' AND end_at >= '".$date." 00:00:00' ) OR (start_at>='".$date." 00:00:00' AND start_at<='".$date." 23:59:59')) AND (subscribers < max_users OR timepicker=1)) AND item_id= '".$itemid."'";
  1358. /*
  1359. WHERE item_id='".$itemid."' AND
  1360. ((start_at<='".$date."' AND end_at>='".$date."') OR (start_at>='".$date."' AND start_at<='".$date."'))";
  1361. */
  1362. $result = Database::query($sql);
  1363. if (Database::num_rows($result) != 0)
  1364. return true;
  1365. return false;
  1366. }
  1367. /**
  1368. * With this you make a reservartion
  1369. *
  1370. * @param - int $reservation_id The id off the reservation
  1371. */
  1372. function add_subscription($reservation_id, $user_id, $accepted) {
  1373. $reservation_id = Database::escape_string($reservation_id);
  1374. $user_id = Database::escape_string($user_id);
  1375. $accepted = Database::escape_string($accepted);
  1376. $sql = "SELECT user_id FROM ".Rsys :: getTable("subscription")." WHERE user_id='".$user_id."' AND reservation_id='".$reservation_id."'";
  1377. if (Database::num_rows(Database::query($sql)) == 0) {
  1378. $sql = "INSERT INTO ".Rsys :: getTable("subscription")." (user_id,reservation_id,accepted) VALUES ('".Database::escape_string($user_id)."','".Database::escape_string($reservation_id)."','". ($accepted ? '1' : '0')."')";
  1379. Database::query($sql);
  1380. $sql = "UPDATE ".Rsys :: getTable("reservation")." SET subscribers=subscribers+1 WHERE id='".$reservation_id."'";
  1381. Database::query($sql);
  1382. $sql = "SELECT s.user_id, i.name, r.start_at, r.end_at
  1383. FROM ".Rsys :: getTable("subscription")." s
  1384. INNER JOIN ".Rsys :: getTable("reservation")." r ON s.reservation_id = r.id
  1385. INNER JOIN ".Rsys :: getTable("item")." i ON r.item_id = i.id
  1386. WHERE reservation_id='".$reservation_id."' AND user_id='".$user_id."'";
  1387. $result = Database::store_result(Database::query($sql));
  1388. $user_info = api_get_user_info();
  1389. $titel = str_replace('#ITEM#', $result[0][1], get_lang("ReservationMadeTitle"));
  1390. $inhoud = str_replace('#ITEM#', $result[0][1], str_replace('#START#', $result[0][2], str_replace('#END#', $result[0][3], get_lang("ReservationMadeMessage"))));
  1391. api_send_mail($user_info['mail'], $titel, $inhoud);
  1392. return 0;
  1393. }
  1394. return 1;
  1395. }
  1396. /**
  1397. * With this you make a reservartion
  1398. *
  1399. * @param - int $reservation_id The id off the reservation
  1400. */
  1401. function add_subscription_timepicker($reservation_id, $user_id, $start_date, $end_date, $accepted, $min, $max) {
  1402. $start_date = Database::escape_string($start_date);
  1403. $end_date = Database::escape_string($end_date);
  1404. if (Rsys :: check_date_subscription($reservation_id, $start_date, $end_date) <> 0)
  1405. return 1;
  1406. if (!($min==0 && $max ==0)){
  1407. if ((Rsys :: mysql_datetime_to_timestamp($end_date)-Rsys :: mysql_datetime_to_timestamp($start_date)) < ($min*60))
  1408. return 2;
  1409. if ((Rsys :: mysql_datetime_to_timestamp($end_date)-Rsys :: mysql_datetime_to_timestamp($start_date)) > ($max*60))
  1410. return 3;
  1411. }
  1412. $sql = "INSERT INTO ".Rsys :: getTable("subscription")." (user_id,reservation_id,accepted,start_at,end_at) VALUES ('".Database::escape_string($user_id)."','".Database::escape_string($reservation_id)."','". ($accepted ? '1' : '0')."','".$start_date."','".$end_date."')";
  1413. Database::query($sql);
  1414. return 0;
  1415. }
  1416. /**
  1417. * Delete subscription
  1418. */
  1419. function delete_subscription($reservation_id, $dummy) {
  1420. $sql = "DELETE FROM ".Rsys :: getTable("subscription")." WHERE dummy='".Database::escape_string($dummy)."'";
  1421. Database::query($sql);
  1422. $sql = "UPDATE ".Rsys :: getTable("reservation")." SET subscribers=subscribers-1 WHERE id='".Database::escape_string($reservation_id)."'";
  1423. Database::query($sql);
  1424. }
  1425. /**
  1426. * Returns the subscriptions of the user for a sortable table based on the params
  1427. *
  1428. * @param - int $from Index of the first item to return.
  1429. * @param - int $per_page The number of items to return
  1430. * @param - int $column The number of the column on which the data should be sorted
  1431. * @param - String $direction In which order should the data be sorted (ASC or DESC)
  1432. * @return - Array The returned rows
  1433. */
  1434. function get_table_subscriptions($from, $per_page, $column, $direction) {
  1435. $from = intval($from);
  1436. $per_page = intval($per_page);
  1437. $column = intval($column);
  1438. if(!in_array($direction, array('ASC','DESC'))) {
  1439. $direction = 'ASC';
  1440. }
  1441. $sql = "SELECT CONCAT(s.reservation_id,'-',s.dummy) AS col0, i.name AS col1, DATE_FORMAT(s.start_at ,'%Y-%m-%d %H:%i') AS col2, DATE_FORMAT(s.end_at ,'%Y-%m-%d %H:%i') AS col3, CONCAT(s.reservation_id,'-',s.dummy) AS col4, DATE_FORMAT(r.start_at ,'%Y-%m-%d %H:%i') , DATE_FORMAT(r.end_at ,'%Y-%m-%d %H:%i') , s.accepted,i.blackout
  1442. FROM ".Rsys :: getTable("subscription")." s
  1443. INNER JOIN ".Rsys :: getTable("reservation")." r ON r.id = s.reservation_id
  1444. INNER JOIN ".Rsys :: getTable("item")." i ON i.id=r.item_id
  1445. WHERE s.user_id = '".api_get_user_id()."'";
  1446. $sql .= "ORDER BY col".$column." ".$direction." LIMIT ".$from.",".$per_page;
  1447. $result = Database::query($sql);
  1448. while ($array = Database::fetch_array($result, 'NUM'))
  1449. { $row = array();
  1450. $row[] = $array[0];
  1451. $row[] = $array[1];
  1452. if($array[2]=='0000-00-00 00:00' && $array[3]=='0000-00-00 00:00')
  1453. {
  1454. $row[] = $array[5];
  1455. $row[] = $array[6];
  1456. }
  1457. else
  1458. {
  1459. $row[] = $array[2];
  1460. $row[] = $array[3];
  1461. }
  1462. if ($array[8]=='1')
  1463. {
  1464. $row[] = get_lang('Blackout');
  1465. }
  1466. else
  1467. {
  1468. if ($array[7]=='1')
  1469. {
  1470. $row[] = get_lang('Yes');
  1471. }else
  1472. {
  1473. $row[] = get_lang('No');
  1474. }
  1475. }
  1476. $row[] = $array[4];
  1477. $arr[] = $row;
  1478. }
  1479. return $arr;
  1480. }
  1481. /**
  1482. * Get number of subscriptions of the user
  1483. *
  1484. * @return - int The amount of itemrights
  1485. */
  1486. function get_num_subscriptions() {
  1487. $sql = "SELECT COUNT(*) FROM ".Rsys :: getTable("subscription")." s
  1488. INNER JOIN ".Rsys :: getTable("reservation")." r ON r.id = s.reservation_id
  1489. INNER JOIN ".Rsys :: getTable("item")." i ON i.id=r.item_id
  1490. WHERE s.user_id = '".api_get_user_id()."'";
  1491. return @ Database::result(Database::query($sql), 0, 0);
  1492. }
  1493. /**
  1494. * Returns $reservation_id=>"START_AT - END_AT"
  1495. */
  1496. /*function get_item_reservations($item_id){
  1497. $sql="SELECT r.id AS reservation_id, r.start_at, r.end_at
  1498. FROM ".Rsys::getTable('reservation')." r
  1499. INNER JOIN ".Rsys::getTable('item')." i ON r.item_id=i.id
  1500. WHERE i.id='".$item_id."'"; // AND r.subscribe_until < NOW() // TODO: subscribe_until controle
  1501. $result=Database::query($sql);
  1502. while($array=Database::fetch_array($result))
  1503. $arr[$array['reservation_id']]=$array['start_at'].' - '.$array['end_at'];
  1504. return $arr;
  1505. }*/
  1506. /**
  1507. * Returns ALL reservations of a certain item with start_date between $from and $till
  1508. *
  1509. * @param - String $from DateTime
  1510. * @param - String $till DateTime
  1511. * @param - int $itemid The itemId
  1512. * @return - Array ['reservations'][RESERVATION_ID]=all info + array with all corresponding subscriptions
  1513. * ['min_start_at'] = the minimal start_at in all reservations (usefull to build table)
  1514. * ['max_end_at'] = the maximal end_at in all reservations (usefull to build table)
  1515. */
  1516. function get_item_reservations($from, $till, $itemid) {
  1517. $itemid = Database::escape_string($itemid);
  1518. $till = Database::escape_string($till);
  1519. $from = Database::escape_string($from);
  1520. $sql = "SELECT r.*,i.name as item_name FROM ".Rsys :: getTable('reservation')." r
  1521. INNER JOIN ".Rsys :: getTable('item')." i ON r.item_id=i.id
  1522. LEFT JOIN ".Rsys :: getTable('item_rights')." ir ON ir.item_id=i.id
  1523. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS)." c ON ir.class_id=c.id AND ir.item_id = i.id
  1524. LEFT JOIN ".Database :: get_main_table(TABLE_MAIN_CLASS_USER)." cu ON cu.class_id = c.id
  1525. WHERE r.item_id='".$itemid."' AND (((cu.user_id='".api_get_user_id()."' AND ir.view_right=1) OR 1=". (api_is_platform_admin() ? 1 : 0).") AND
  1526. (r.start_at<='".$from."' AND r.end_at>='".$from."') OR (r.start_at>='".$from."' AND r.start_at<='".$till."')) ORDER BY start_at ASC";
  1527. $result = Database::query($sql);
  1528. $max_start_at = -1;
  1529. $min_end_at = -1;
  1530. $ids = '';
  1531. $from_stamp = Rsys :: mysql_datetime_to_timestamp($from);
  1532. $till_stamp = Rsys :: mysql_datetime_to_timestamp($till);
  1533. if (Database::num_rows($result) == 0)
  1534. return false;
  1535. while ($array = Database::fetch_array($result)) {
  1536. $ids .= $array['id'].',';
  1537. $current_start_at = Rsys :: mysql_datetime_to_timestamp($array['start_at']);
  1538. $current_end_at = Rsys :: mysql_datetime_to_timestamp($array['end_at']);
  1539. if ($from_stamp > $current_start_at)
  1540. $array['start_at'] = $from;
  1541. $rarr['reservations'][$array['id']]['info'] = $array;
  1542. /*
  1543. if ($max_start_at == -1 || $current_start_at < $rarr['min_start_at'])
  1544. $rarr['min_start_at'] = $current_start_at;
  1545. if ($current_end_at > $rarr['max_end_at'])
  1546. $rarr['max_end_at'] = $current_end_at;*/
  1547. }
  1548. $ids = substr($ids, 0, -1);
  1549. $sql = "SELECT * FROM ".Rsys :: getTable('subscription')." WHERE reservation_id IN (".$ids.") AND (start_at='0000-00-00 00:00:00' OR (start_at<='".$from."' AND end_at>='".$from."') OR (start_at>='".$from."' AND start_at<='".$till."')) ORDER BY start_at ASC";
  1550. $result = Database::query($sql);
  1551. while ($array = Database::fetch_array($result, 'ASSOC')) {
  1552. // echo $array['reservation_id'].': '.$array['start_at'].'-'.$array['end_at'].'<br />';
  1553. if ($rarr['reservations'][$array['reservation_id']]['info']['timepicker']) {
  1554. $current_start_at = Rsys :: mysql_datetime_to_timestamp($array['start_at']);
  1555. $current_end_at = Rsys :: mysql_datetime_to_timestamp($array['end_at']);
  1556. if ($current_start_at < $from_stamp) //&& $current_end_at>=$from_stamp) || ($current_start_at>=$from_stamp && $current_start_at<=$till_stamp)))
  1557. $array['start_at'] = $from;
  1558. if ($current_end_at > $till_stamp)
  1559. $array['end_at'] = $till;
  1560. }
  1561. $rarr['reservations'][$array['reservation_id']]['subscriptions'][] = $array;
  1562. }
  1563. return $rarr;
  1564. }
  1565. /**
  1566. * Returns $reservation_id=>"START_AT - END_AT"
  1567. */
  1568. function get_item_subfiltered_reservations($item_id) {
  1569. $itemid = Database::escape_string($itemid);
  1570. $sql = "SELECT r.id AS reservation_id, r.start_at, r.end_at
  1571. FROM ".Rsys :: getTable('reservation')." r
  1572. INNER JOIN ".Rsys :: getTable('item')." i ON r.item_id=i.id
  1573. WHERE r.id NOT IN (SELECT s.reservation_id FROM ".Rsys :: getTable('subscription')." s WHERE r.id=s.reservation_id AND s.user_id='".api_get_user_id()."') AND i.id='".$item_id."'"; // AND r.subscribe_until < NOW() // TODO: subscribe_until controle
  1574. $result = Database::query($sql);
  1575. while ($array = Database::fetch_array($result))
  1576. $arr[$array['reservation_id']] = $array['start_at'].' - '.$array['end_at'];
  1577. return $arr;
  1578. }
  1579. /**
  1580. * Returns ALL subscriptions between $from and $till
  1581. *
  1582. * @param - String $from DateTime
  1583. * @param - String $till DateTime
  1584. */
  1585. function get_subscriptions($from, $till) {
  1586. $till = Database::escape_string($till);
  1587. $from = Database::escape_string($from);
  1588. // TODO: only return for current user...
  1589. $sql = "SELECT r.*,s.start_at AS tp_start,s.end_at AS tp_end,s.accepted FROM ".Rsys :: getTable('subscription')." s INNER JOIN ".Rsys :: getTable('reservation')." r ON s.reservation_id = r.id WHERE ((r.timepicker=0 AND r.start_at>='".$from."' AND r.end_at<='".$till."') OR (s.start_at>='".$from."' AND s.end_at<='".$till."'))";
  1590. $result = Database::query($sql);
  1591. while ($array = Database::fetch_array($result)) {
  1592. $arr[] = $array;
  1593. if ($arr['timepicker'] == 1) {
  1594. $arr['start_at'] = $arr['tp_start'];
  1595. $arr['end_at'] = $arr['tp_end'];
  1596. }
  1597. }
  1598. return $arr;
  1599. }
  1600. function get_item_id($item_name)
  1601. {
  1602. $item_name = Database::escape_string($item_name);
  1603. $sql = "SELECT id FROM ".Rsys :: getTable('item')." WHERE name='".$item_name."'";
  1604. $result = Database::query($sql);
  1605. $result_array = Database::fetch_array($result);
  1606. return $result_array['id'];
  1607. }
  1608. }
  1609. $language_file = 'reservation';
  1610. $cidReset = true;
  1611. require_once ('../inc/global.inc.php');
  1612. Rsys :: init();
  1613. require_once 'rcalendar.php';
  1614. require_once (api_get_path(LIBRARY_PATH).'formvalidator/FormValidator.class.php');
  1615. ?>