rsys.php 80 KB

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