rsys.php 77 KB

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