rsys.php 74 KB

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