usermanager.lib.php 124 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429243024312432243324342435243624372438243924402441244224432444244524462447244824492450245124522453245424552456245724582459246024612462246324642465246624672468246924702471247224732474247524762477247824792480248124822483248424852486248724882489249024912492249324942495249624972498249925002501250225032504250525062507250825092510251125122513251425152516251725182519252025212522252325242525252625272528252925302531253225332534253525362537253825392540254125422543254425452546254725482549255025512552255325542555255625572558255925602561256225632564256525662567256825692570257125722573257425752576257725782579258025812582258325842585258625872588258925902591259225932594259525962597259825992600260126022603260426052606260726082609261026112612261326142615261626172618261926202621262226232624262526262627262826292630263126322633263426352636263726382639264026412642264326442645264626472648264926502651265226532654265526562657265826592660266126622663266426652666266726682669267026712672267326742675267626772678267926802681268226832684268526862687268826892690269126922693269426952696269726982699270027012702270327042705270627072708270927102711271227132714271527162717271827192720272127222723272427252726272727282729273027312732273327342735273627372738273927402741274227432744274527462747274827492750275127522753275427552756275727582759276027612762276327642765276627672768276927702771277227732774277527762777277827792780278127822783278427852786278727882789279027912792279327942795279627972798279928002801280228032804280528062807280828092810281128122813281428152816281728182819282028212822282328242825282628272828282928302831283228332834283528362837283828392840284128422843284428452846284728482849285028512852285328542855285628572858285928602861286228632864286528662867286828692870287128722873287428752876287728782879288028812882288328842885288628872888288928902891289228932894289528962897289828992900290129022903290429052906290729082909291029112912291329142915291629172918291929202921292229232924292529262927292829292930293129322933293429352936293729382939294029412942294329442945294629472948294929502951295229532954295529562957295829592960296129622963296429652966296729682969297029712972297329742975297629772978297929802981298229832984298529862987298829892990299129922993299429952996299729982999300030013002300330043005300630073008300930103011301230133014301530163017301830193020302130223023302430253026302730283029303030313032303330343035303630373038303930403041304230433044304530463047304830493050305130523053305430553056305730583059306030613062306330643065306630673068306930703071307230733074307530763077307830793080308130823083308430853086308730883089309030913092309330943095309630973098
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * This library provides functions for user management.
  5. * Include/require it in your code to use its functionality.
  6. *
  7. * @package chamilo.library
  8. * @author Julio Montoya <gugli100@gmail.com> Social network groups added 2009/12
  9. */
  10. // Constants for user extra field types.
  11. define('USER_FIELD_TYPE_TEXT', 1);
  12. define('USER_FIELD_TYPE_TEXTAREA', 2);
  13. define('USER_FIELD_TYPE_RADIO', 3);
  14. define('USER_FIELD_TYPE_SELECT', 4);
  15. define('USER_FIELD_TYPE_SELECT_MULTIPLE', 5);
  16. define('USER_FIELD_TYPE_DATE', 6);
  17. define('USER_FIELD_TYPE_DATETIME', 7);
  18. define('USER_FIELD_TYPE_DOUBLE_SELECT', 8);
  19. define('USER_FIELD_TYPE_DIVIDER', 9);
  20. define('USER_FIELD_TYPE_TAG', 10);
  21. define('USER_FIELD_TYPE_TIMEZONE', 11);
  22. //User image sizes
  23. define('USER_IMAGE_SIZE_ORIGINAL', 1);
  24. define('USER_IMAGE_SIZE_BIG', 2);
  25. define('USER_IMAGE_SIZE_MEDIUM', 3);
  26. define('USER_IMAGE_SIZE_SMALL', 4);
  27. // Relation type between users
  28. define('USER_UNKNOW', 0);
  29. define('USER_RELATION_TYPE_UNKNOW', 1);
  30. define('USER_RELATION_TYPE_PARENT', 2); // should be deprecated is useless
  31. define('USER_RELATION_TYPE_FRIEND', 3);
  32. define('USER_RELATION_TYPE_GOODFRIEND', 4); // should be deprecated is useless
  33. define('USER_RELATION_TYPE_ENEMY', 5); // should be deprecated is useless
  34. define('USER_RELATION_TYPE_DELETED', 6);
  35. define('USER_RELATION_TYPE_RRHH', 7);
  36. class UserManager
  37. {
  38. private function __construct () {
  39. }
  40. /**
  41. * Creates a new user for the platform
  42. * @author Hugues Peeters <peeters@ipm.ucl.ac.be>,
  43. * Roan Embrechts <roan_embrechts@yahoo.com>
  44. *
  45. * @param string Firstname
  46. * @param string Lastname
  47. * @param int Status (1 for course tutor, 5 for student, 6 for anonymous)
  48. * @param string e-mail address
  49. * @param string Login
  50. * @param string Password
  51. * @param string Any official code (optional)
  52. * @param string User language (optional)
  53. * @param string Phone number (optional)
  54. * @param string Picture URI (optional)
  55. * @param string Authentication source (optional, defaults to 'platform', dependind on constant)
  56. * @param string Account expiration date (optional, defaults to '0000-00-00 00:00:00')
  57. * @param int Whether the account is enabled or disabled by default
  58. * @param int The user ID of the person who registered this user (optional, defaults to null)
  59. * @param int The department of HR in which the user is registered (optional, defaults to 0)
  60. * @return mixed new user id - if the new user creation succeeds, false otherwise
  61. *
  62. * @desc The function tries to retrieve $_user['user_id'] from the global space.
  63. * if it exists, $_user['user_id'] is the creator id. If a problem arises,
  64. * it stores the error message in global $api_failureList
  65. */
  66. public static function create_user($firstName, $lastName, $status, $email, $loginName, $password, $official_code = '', $language = '', $phone = '', $picture_uri = '', $auth_source = PLATFORM_AUTH_SOURCE, $expiration_date = '0000-00-00 00:00:00', $active = 1, $hr_dept_id = 0, $extra = null) {
  67. global $_user, $userPasswordCrypted;
  68. $firstName = Security::remove_XSS($firstName);
  69. $lastName = Security::remove_XSS($lastName);
  70. $loginName = Security::remove_XSS($loginName);
  71. $phone = Security::remove_XSS($phone);
  72. // database table definition
  73. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  74. // default langauge
  75. if ($language == '') {
  76. $language = api_get_setting('platformLanguage');
  77. }
  78. if ($_user['user_id']) {
  79. $creator_id = intval($_user['user_id']);
  80. } else {
  81. $creator_id = '';
  82. }
  83. // First check wether the login already exists
  84. if (!self::is_username_available($loginName)) {
  85. return api_set_failure('login-pass already taken');
  86. }
  87. //$password = "PLACEHOLDER";
  88. $password = api_get_encrypted_password($password);
  89. //$password = ($userPasswordCrypted ? md5($password) : $password);
  90. $current_date = date('Y-m-d H:i:s', time());
  91. $sql = "INSERT INTO $table_user
  92. SET lastname = '".Database::escape_string(trim($lastName))."',
  93. firstname = '".Database::escape_string(trim($firstName))."',
  94. username = '".Database::escape_string(trim($loginName))."',
  95. status = '".Database::escape_string($status)."',
  96. password = '".Database::escape_string($password)."',
  97. email = '".Database::escape_string($email)."',
  98. official_code = '".Database::escape_string($official_code)."',
  99. picture_uri = '".Database::escape_string($picture_uri)."',
  100. creator_id = '".Database::escape_string($creator_id)."',
  101. auth_source = '".Database::escape_string($auth_source)."',
  102. phone = '".Database::escape_string($phone)."',
  103. language = '".Database::escape_string($language)."',
  104. registration_date = '".$current_date."',
  105. expiration_date = '".Database::escape_string($expiration_date)."',
  106. hr_dept_id = '".Database::escape_string($hr_dept_id)."',
  107. active = '".Database::escape_string($active)."'";
  108. $result = Database::query($sql);
  109. if ($result) {
  110. //echo "id returned";
  111. $return = Database::insert_id();
  112. global $_configuration;
  113. require_once api_get_path(LIBRARY_PATH).'urlmanager.lib.php';
  114. if ($_configuration['multiple_access_urls'] == true) {
  115. if (api_get_current_access_url_id() != -1) {
  116. UrlManager::add_user_to_url($return, api_get_current_access_url_id());
  117. } else {
  118. UrlManager::add_user_to_url($return, 1);
  119. }
  120. } else {
  121. //we are adding by default the access_url_user table with access_url_id = 1
  122. UrlManager::add_user_to_url($return, 1);
  123. }
  124. // add event to system log
  125. $time = time();
  126. $user_id_manager = api_get_user_id();
  127. event_system(LOG_USER_CREATE, LOG_USER_ID, $return, $time, $user_id_manager);
  128. } else {
  129. //echo "false - failed" ;
  130. $return=false;
  131. }
  132. if (is_array($extra) && count($extra) > 0) {
  133. $res = true;
  134. foreach($extra as $fname => $fvalue) {
  135. $res = $res && self::update_extra_field($return, $fname, $fvalue);
  136. }
  137. }
  138. return $return;
  139. }
  140. /**
  141. * Can user be deleted?
  142. * This functions checks if there's a course in which the given user is the
  143. * only course administrator. If that is the case, the user can't be
  144. * deleted because the course would remain without a course admin.
  145. * @param int $user_id The user id
  146. * @return boolean true if user can be deleted
  147. */
  148. public static function can_delete_user($user_id) {
  149. $table_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  150. if ($user_id != strval(intval($user_id))) return false;
  151. if ($user_id === false) return false;
  152. $sql = "SELECT * FROM $table_course_user WHERE status = '1' AND user_id = '".$user_id."'";
  153. $res = Database::query($sql);
  154. while ($course = Database::fetch_object($res)) {
  155. $sql = "SELECT user_id FROM $table_course_user WHERE status='1' AND course_code ='".Database::escape_string($course->course_code)."'";
  156. $res2 = Database::query($sql);
  157. if (Database::num_rows($res2) == 1) {
  158. return false;
  159. }
  160. }
  161. return true;
  162. }
  163. /**
  164. * Delete a user from the platform
  165. * @param int $user_id The user id
  166. * @return boolean true if user is succesfully deleted, false otherwise
  167. */
  168. public static function delete_user($user_id) {
  169. global $_configuration;
  170. if ($user_id != strval(intval($user_id))) return false;
  171. if ($user_id === false) return false;
  172. if (!self::can_delete_user($user_id)) {
  173. return false;
  174. }
  175. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  176. $table_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  177. $table_class_user = Database :: get_main_table(TABLE_MAIN_CLASS_USER);
  178. $table_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  179. $table_admin = Database :: get_main_table(TABLE_MAIN_ADMIN);
  180. $table_session_user = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
  181. $table_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  182. // Unsubscribe the user from all groups in all his courses
  183. $sql = "SELECT * FROM $table_course c, $table_course_user cu WHERE cu.user_id = '".$user_id."' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." AND c.code = cu.course_code";
  184. $res = Database::query($sql);
  185. while ($course = Database::fetch_object($res)) {
  186. $table_group = Database :: get_course_table(TABLE_GROUP_USER, $course->db_name);
  187. $sql = "DELETE FROM $table_group WHERE user_id = '".$user_id."'";
  188. Database::query($sql);
  189. }
  190. // Unsubscribe user from all classes
  191. $sql = "DELETE FROM $table_class_user WHERE user_id = '".$user_id."'";
  192. Database::query($sql);
  193. // Unsubscribe user from all courses
  194. $sql = "DELETE FROM $table_course_user WHERE user_id = '".$user_id."'";
  195. Database::query($sql);
  196. // Unsubscribe user from all courses in sessions
  197. $sql = "DELETE FROM $table_session_course_user WHERE id_user = '".$user_id."'";
  198. Database::query($sql);
  199. // Unsubscribe user from all sessions
  200. $sql = "DELETE FROM $table_session_user WHERE id_user = '".$user_id."'";
  201. Database::query($sql);
  202. // Delete user picture
  203. // TODO: Logic about api_get_setting('split_users_upload_directory') === 'true' , a user has 4 differnt sized photos to be deleted.
  204. $user_info = api_get_user_info($user_id);
  205. if (strlen($user_info['picture_uri']) > 0) {
  206. $img_path = api_get_path(SYS_CODE_PATH).'upload/users/'.$user_id.'/'.$user_info['picture_uri'];
  207. if (file_exists($img_path))
  208. unlink($img_path);
  209. }
  210. // Delete the personal course categories
  211. $course_cat_table = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  212. $sql = "DELETE FROM $course_cat_table WHERE user_id = '".$user_id."'";
  213. Database::query($sql);
  214. // Delete user from database
  215. $sql = "DELETE FROM $table_user WHERE user_id = '".$user_id."'";
  216. Database::query($sql);
  217. // Delete user from the admin table
  218. $sql = "DELETE FROM $table_admin WHERE user_id = '".$user_id."'";
  219. Database::query($sql);
  220. // Delete the personal agenda-items from this user
  221. $agenda_table = Database :: get_user_personal_table(TABLE_PERSONAL_AGENDA);
  222. $sql = "DELETE FROM $agenda_table WHERE user = '".$user_id."'";
  223. Database::query($sql);
  224. $gradebook_results_table = Database :: get_main_table(TABLE_MAIN_GRADEBOOK_RESULT);
  225. $sql = 'DELETE FROM '.$gradebook_results_table.' WHERE user_id = '.$user_id;
  226. Database::query($sql);
  227. $user = Database::fetch_array($res);
  228. $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  229. $sqlv = "DELETE FROM $t_ufv WHERE user_id = $user_id";
  230. $resv = Database::query($sqlv);
  231. require_once api_get_path(LIBRARY_PATH).'urlmanager.lib.php';
  232. if ($_configuration['multiple_access_urls']) {
  233. $url_id = 1;
  234. if (api_get_current_access_url_id() != -1) {
  235. $url_id = api_get_current_access_url_id();
  236. }
  237. UrlManager::delete_url_rel_user($user_id, $url_id);
  238. } else {
  239. //we delete the user from the url_id =1
  240. UrlManager::delete_url_rel_user($user_id, 1);
  241. }
  242. if (api_get_setting('allow_social_tool')=='true' ) {
  243. require_once api_get_path(LIBRARY_PATH).'social.lib.php';
  244. //@todo Delete user from groups
  245. //Delete from user friend lists
  246. SocialManager::remove_user_rel_user($user_id,true);
  247. }
  248. // add event to system log
  249. $time = time();
  250. $user_id_manager = api_get_user_id();
  251. event_system(LOG_USER_DELETE, LOG_USER_ID, $user_id, $time, $user_id_manager);
  252. return true;
  253. }
  254. /**
  255. * Update user information with new openid
  256. * @param int $user_id
  257. * @param string $openid
  258. * @return boolean true if the user information was updated
  259. */
  260. public static function update_openid($user_id, $openid) {
  261. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  262. if ($user_id != strval(intval($user_id))) return false;
  263. if ($user_id === false) return false;
  264. $sql = "UPDATE $table_user SET
  265. openid='".Database::escape_string($openid)."'";
  266. $sql .= " WHERE user_id='$user_id'";
  267. return Database::query($sql);
  268. }
  269. /**
  270. * Update user information
  271. * @param int $user_id
  272. * @param string $firstname
  273. * @param string $lastname
  274. * @param string $username
  275. * @param string $password
  276. * @param string $auth_source
  277. * @param string $email
  278. * @param int $status
  279. * @param string $official_code
  280. * @param string $phone
  281. * @param string $picture_uri
  282. * @param int The user ID of the person who registered this user (optional, defaults to null)
  283. * @param int The department of HR in which the user is registered (optional, defaults to 0)
  284. * @param array A series of additional fields to add to this user as extra fields (optional, defaults to null)
  285. * @return boolean true if the user information was updated
  286. */
  287. public static function update_user($user_id, $firstname, $lastname, $username, $password = null, $auth_source = null, $email, $status, $official_code, $phone, $picture_uri, $expiration_date, $active, $creator_id = null, $hr_dept_id = 0, $extra = null, $language = 'english') {
  288. global $userPasswordCrypted;
  289. if ($user_id != strval(intval($user_id))) return false;
  290. if ($user_id === false) return false;
  291. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  292. $sql = "UPDATE $table_user SET
  293. lastname='".Database::escape_string($lastname)."',
  294. firstname='".Database::escape_string($firstname)."',
  295. username='".Database::escape_string($username)."',
  296. language='".Database::escape_string($language)."',";
  297. if (!is_null($password)) {
  298. //$password = $userPasswordCrypted ? md5($password) : $password;
  299. $password = api_get_encrypted_password($password);
  300. $sql .= " password='".Database::escape_string($password)."',";
  301. }
  302. if (!is_null($auth_source)) {
  303. $sql .= " auth_source='".Database::escape_string($auth_source)."',";
  304. }
  305. $sql .= "
  306. email='".Database::escape_string($email)."',
  307. status='".Database::escape_string($status)."',
  308. official_code='".Database::escape_string($official_code)."',
  309. phone='".Database::escape_string($phone)."',
  310. picture_uri='".Database::escape_string($picture_uri)."',
  311. expiration_date='".Database::escape_string($expiration_date)."',
  312. active='".Database::escape_string($active)."',
  313. hr_dept_id=".intval($hr_dept_id);
  314. if (!is_null($creator_id)) {
  315. $sql .= ", creator_id='".Database::escape_string($creator_id)."'";
  316. }
  317. $sql .= " WHERE user_id='$user_id'";
  318. $return = Database::query($sql);
  319. if (is_array($extra) && count($extra) > 0) {
  320. $res = true;
  321. foreach($extra as $fname => $fvalue) {
  322. $res = $res && self::update_extra_field($user_id,$fname,$fvalue);
  323. }
  324. }
  325. return $return;
  326. }
  327. /**
  328. * Check if a username is available
  329. * @param string the wanted username
  330. * @return boolean true if the wanted username is available
  331. */
  332. public static function is_username_available($username) {
  333. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  334. $sql = "SELECT username FROM $table_user WHERE username = '".Database::escape_string($username)."'";
  335. $res = Database::query($sql);
  336. return Database::num_rows($res) == 0;
  337. }
  338. /**
  339. * Creates a username using person's names, i.e. creates jmontoya from Julio Montoya.
  340. * @param string $firstname The first name of the user.
  341. * @param string $lastname The last name of the user.
  342. * @param string $language (optional) The language in which comparison is to be made. If language is omitted, interface language is assumed then.
  343. * @param string $encoding (optional) The character encoding for the input names. If it is omitted, the platform character set will be used by default.
  344. * @return string Suggests a username that contains only ASCII-letters and digits, without check for uniqueness within the system.
  345. * @author Julio Montoya Armas
  346. * @author Ivan Tcholakov, 2009 - rework about internationalization.
  347. */
  348. public static function create_username($firstname, $lastname, $language = null, $encoding = null) {
  349. if (is_null($encoding)) {
  350. $encoding = api_get_system_encoding();
  351. }
  352. if (is_null($language)) {
  353. $language = api_get_interface_language();
  354. }
  355. $firstname = substr(preg_replace(USERNAME_PURIFIER, '', api_transliterate($firstname, '', $encoding)), 0, 1); // The first letter only.
  356. $lastname = preg_replace(USERNAME_PURIFIER, '', api_transliterate($lastname, '', $encoding));
  357. $username = api_is_western_name_order(null, $language) ? $firstname.$lastname : $lastname.$firstname;
  358. if (empty($username)) {
  359. $username = 'user';
  360. }
  361. return strtolower(substr($username, 0, USERNAME_MAX_LENGTH - 3));
  362. }
  363. /**
  364. * Creates a unique username, using:
  365. * 1. the first name and the last name of a user;
  366. * 2. an already created username but not checked for uniqueness yet.
  367. * @param string $firstname The first name of a given user. If the second parameter $lastname is NULL, then this
  368. * parameter is treated as username which is to be checked for uniqueness and to be modified when it is necessary.
  369. * @param string $lastname The last name of the user.
  370. * @param string $language (optional) The language in which comparison is to be made. If language is omitted, interface language is assumed then.
  371. * @param string $encoding (optional) The character encoding for the input names. If it is omitted, the platform character set will be used by default.
  372. * @return string Returns a username that contains only ASCII-letters and digits, and that is unique within the system.
  373. * Note: When the method is called several times with same parameters, its results look like the following sequence: ivan, ivan2, ivan3, ivan4, ...
  374. * @author Ivan Tcholakov, 2009
  375. */
  376. public static function create_unique_username($firstname, $lastname = null, $language = null, $encoding = null) {
  377. if (is_null($lastname)) {
  378. // In this case the actual input parameter $firstname should contain ASCII-letters and digits only.
  379. // For making this method tolerant of mistakes, let us transliterate and purify the suggested input username anyway.
  380. // So, instead of the sentence $username = $firstname; we place the following:
  381. $username = strtolower(preg_replace(USERNAME_PURIFIER, '', api_transliterate($firstname, '', $encoding)));
  382. } else {
  383. $username = self::create_username($firstname, $lastname, $language, $encoding);
  384. }
  385. if (!self::is_username_available($username)) {
  386. $i = 2;
  387. $temp_username = substr($username, 0, USERNAME_MAX_LENGTH - strlen((string)$i)).$i;
  388. while (!self::is_username_available($temp_username)) {
  389. $i++;
  390. $temp_username = substr($username, 0, USERNAME_MAX_LENGTH - strlen((string)$i)).$i;
  391. }
  392. $username = $temp_username;
  393. }
  394. return $username;
  395. }
  396. /**
  397. * Modifies a given username accordingly to the specification for valid characters and length.
  398. * @param $username string The input username.
  399. * @param bool $strict (optional) When this flag is TRUE, the result is guaranteed for full compliance, otherwise compliance may be partial. The default value is FALSE.
  400. * @param string $encoding (optional) The character encoding for the input names. If it is omitted, the platform character set will be used by default.
  401. * @return string The resulting purified username.
  402. */
  403. public function purify_username($username, $strict = false, $encoding = null) {
  404. if ($strict) {
  405. // 1. Conversion of unacceptable letters (latinian letters with accents for example) into ASCII letters in order they not to be totally removed.
  406. // 2. Applying the strict purifier.
  407. // 3. Length limitation.
  408. return substr(preg_replace(USERNAME_PURIFIER, '', api_transliterate($username, '', $encoding)), 0, USERNAME_MAX_LENGTH);
  409. }
  410. // 1. Applying the shallow purifier.
  411. // 2. Length limitation.
  412. return substr(preg_replace(USERNAME_PURIFIER_SHALLOW, '', $username), 0, USERNAME_MAX_LENGTH);
  413. }
  414. /**
  415. * Checks whether a given username matches to the specification strictly. The empty username is assumed here as invalid.
  416. * Mostly this function is to be used in the user interface built-in validation routines for providing feedback while usernames are enterd manually.
  417. * @param string $username The input username.
  418. * @param string $encoding (optional) The character encoding for the input names. If it is omitted, the platform character set will be used by default.
  419. * @return bool Returns TRUE if the username is valid, FALSE otherwise.
  420. */
  421. public function is_username_valid($username, $encoding = null) {
  422. return !empty($username) && $username == self::purify_username($username, true);
  423. }
  424. /**
  425. * Checks whether a username is empty. If the username contains whitespace characters, such as spaces, tabulators, newlines, etc.,
  426. * it is assumed as empty too. This function is safe for validation unpurified data (during importing).
  427. * @param string $username The given username.
  428. * @return bool Returns TRUE if length of the username exceeds the limit, FALSE otherwise.
  429. */
  430. public static function is_username_empty($username) {
  431. return (strlen(self::purify_username($username, false)) == 0);
  432. }
  433. /**
  434. * Checks whether a username is too long or not.
  435. * @param string $username The given username, it should contain only ASCII-letters and digits.
  436. * @return bool Returns TRUE if length of the username exceeds the limit, FALSE otherwise.
  437. */
  438. public static function is_username_too_long($username) {
  439. return (strlen($username) > USERNAME_MAX_LENGTH);
  440. }
  441. /**
  442. * Get a list of users of which the given conditions match with an = 'cond'
  443. * @param array $conditions a list of condition (exemple : status=>STUDENT)
  444. * @param array $order_by a list of fields on which sort
  445. * @return array An array with all users of the platform.
  446. * @todo optional course code parameter, optional sorting parameters...
  447. */
  448. public static function get_user_list($conditions = array(), $order_by = array()) {
  449. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  450. $return_array = array();
  451. $sql_query = "SELECT * FROM $user_table";
  452. if (count($conditions) > 0) {
  453. $sql_query .= ' WHERE ';
  454. foreach ($conditions as $field => $value) {
  455. $field = Database::escape_string($field);
  456. $value = Database::escape_string($value);
  457. $sql_query .= $field.' = '.$value;
  458. }
  459. }
  460. if (count($order_by) > 0) {
  461. $sql_query .= ' ORDER BY '.Database::escape_string(implode(',', $order_by));
  462. }
  463. $sql_result = Database::query($sql_query);
  464. while ($result = Database::fetch_array($sql_result)) {
  465. $return_array[] = $result;
  466. }
  467. return $return_array;
  468. }
  469. /**
  470. * Get a list of users of which the given conditions match with a LIKE '%cond%'
  471. * @param array $conditions a list of condition (exemple : status=>STUDENT)
  472. * @param array $order_by a list of fields on which sort
  473. * @return array An array with all users of the platform.
  474. * @todo optional course code parameter, optional sorting parameters...
  475. */
  476. public static function get_user_list_like($conditions = array(), $order_by = array()) {
  477. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  478. $return_array = array();
  479. $sql_query = "SELECT * FROM $user_table";
  480. if (count($conditions) > 0) {
  481. $sql_query .= ' WHERE ';
  482. foreach ($conditions as $field => $value) {
  483. $field = Database::escape_string($field);
  484. $value = Database::escape_string($value);
  485. $sql_query .= $field.' LIKE \'%'.$value.'%\'';
  486. }
  487. }
  488. if (count($order_by) > 0) {
  489. $sql_query .= ' ORDER BY '.Database::escape_string(implode(',', $order_by));
  490. }
  491. $sql_result = Database::query($sql_query);
  492. while ($result = Database::fetch_array($sql_result)) {
  493. $return_array[] = $result;
  494. }
  495. return $return_array;
  496. }
  497. /**
  498. * Get user information
  499. * @param string The username
  500. * @return array All user information as an associative array
  501. */
  502. public static function get_user_info($username) {
  503. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  504. $username = Database::escape_string($username);
  505. $sql = "SELECT * FROM $user_table WHERE username='".$username."'";
  506. $res = Database::query($sql);
  507. if (Database::num_rows($res) > 0) {
  508. return Database::fetch_array($res);
  509. }
  510. return false;
  511. }
  512. /**
  513. * Get user information
  514. * @param string The id
  515. * @param boolean Whether to return the user's extra fields (defaults to false)
  516. * @return array All user information as an associative array
  517. */
  518. public static function get_user_info_by_id($user_id, $user_fields = false) {
  519. $user_id = intval($user_id);
  520. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  521. $sql = "SELECT * FROM $user_table WHERE user_id=".$user_id;
  522. $res = Database::query($sql);
  523. if (Database::num_rows($res) > 0) {
  524. $user = Database::fetch_array($res);
  525. $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  526. $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  527. $sqlf = "SELECT * FROM $t_uf ORDER BY field_order";
  528. $resf = Database::query($sqlf);
  529. if (Database::num_rows($resf) > 0) {
  530. while ($rowf = Database::fetch_array($resf)) {
  531. $sqlv = "SELECT * FROM $t_ufv WHERE field_id = ".$rowf['id']." AND user_id = ".$user['user_id']." ORDER BY id DESC";
  532. $resv = Database::query($sqlv);
  533. if (Database::num_rows($resv) > 0) {
  534. //There should be only one value for a field and a user
  535. $rowv = Database::fetch_array($resv);
  536. $user['extra'][$rowf['field_variable']] = $rowv['field_value'];
  537. } else {
  538. $user['extra'][$rowf['field_variable']] = '';
  539. }
  540. }
  541. }
  542. return $user;
  543. }
  544. return false;
  545. }
  546. /** Get the teacher list
  547. * @param int the course ID
  548. * @param array Content the list ID of user_id selected
  549. */
  550. //for survey
  551. // TODO: Ivan, 14-SEP-2009: It seems that this method is not used at all (it can be located in a test unit only. To be deprecated?
  552. public static function get_teacher_list($course_id, $sel_teacher = '') {
  553. $user_course_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  554. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  555. $course_id = Database::escape_string($course_id);
  556. $sql_query = "SELECT * FROM $user_table a, $user_course_table b where a.user_id=b.user_id AND b.status=1 AND b.course_code='$course_id'";
  557. $sql_result = Database::query($sql_query);
  558. echo "<select name=\"author\">";
  559. while ($result = Database::fetch_array($sql_result)) {
  560. if ($sel_teacher == $result['user_id']) $selected ="selected";
  561. echo "\n<option value=\"".$result['user_id']."\" $selected>".$result['firstname']."</option>";
  562. }
  563. echo "</select>";
  564. }
  565. /**
  566. * Get user picture URL or path from user ID (returns an array).
  567. * The return format is a complete path, enabling recovery of the directory
  568. * with dirname() or the file with basename(). This also works for the
  569. * functions dealing with the user's productions, as they are located in
  570. * the same directory.
  571. * @param integer User ID
  572. * @param string Type of path to return (can be 'none', 'system', 'rel', 'web')
  573. * @param bool Whether we want to have the directory name returned 'as if' there was a file or not (in the case we want to know which directory to create - otherwise no file means no split subdir)
  574. * @param bool If we want that the function returns the /main/img/unknown.jpg image set it at true
  575. * @return array Array of 2 elements: 'dir' and 'file' which contain the dir and file as the name implies if image does not exist it will return the unknow image if anonymous parameter is true if not it returns an empty er's
  576. */
  577. public static function get_user_picture_path_by_id($id, $type = 'none', $preview = false, $anonymous = false) {
  578. switch ($type) {
  579. case 'system': // Base: absolute system path.
  580. $base = api_get_path(SYS_CODE_PATH);
  581. break;
  582. case 'rel': // Base: semi-absolute web path (no server base).
  583. $base = api_get_path(REL_CODE_PATH);
  584. break;
  585. case 'web': // Base: absolute web path.
  586. $base = api_get_path(WEB_CODE_PATH);
  587. break;
  588. case 'none':
  589. default: // Base: empty, the result path below will be relative.
  590. $base = '';
  591. }
  592. if (empty($id) || empty($type)) {
  593. return $anonymous ? array('dir' => $base.'img/', 'file' => 'unknown.jpg') : array('dir' => '', 'file' => '');
  594. }
  595. $user_id = intval($id);
  596. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  597. $sql = "SELECT picture_uri FROM $user_table WHERE user_id=".$user_id;
  598. $res = Database::query($sql);
  599. if (!Database::num_rows($res)) {
  600. return $anonymous ? array('dir' => $base.'img/', 'file' => 'unknown.jpg') : array('dir' => '', 'file' => '');
  601. }
  602. $user = Database::fetch_array($res);
  603. $picture_filename = trim($user['picture_uri']);
  604. if (api_get_setting('split_users_upload_directory') === 'true') {
  605. if (!empty($picture_filename)) {
  606. $dir = $base.'upload/users/'.substr($picture_filename, 0, 1).'/'.$user_id.'/';
  607. } elseif ($preview) {
  608. $dir = $base.'upload/users/'.substr((string)$user_id, 0, 1).'/'.$user_id.'/';
  609. } else {
  610. $dir = $base.'upload/users/'.$user_id.'/';
  611. }
  612. } else {
  613. $dir = $base.'upload/users/'.$user_id.'/';
  614. }
  615. if (empty($picture_filename) && $anonymous) {
  616. return array('dir' => $base.'img/', 'file' => 'unknown.jpg');
  617. }
  618. return array('dir' => $dir, 'file' => $picture_filename);
  619. }
  620. /**
  621. * Creates new user pfotos in various sizes of a user, or deletes user pfotos.
  622. * Note: This method relies on configuration setting from dokeos/main/inc/conf/profile.conf.php
  623. * @param int $user_id The user internal identitfication number.
  624. * @param string $file The common file name for the newly created pfotos. It will be checked and modified for compatibility with the file system.
  625. * If full name is provided, path component is ignored.
  626. * If an empty name is provided, then old user photos are deleted only, @see UserManager::delete_user_picture() as the prefered way for deletion.
  627. * @param string $source_file The full system name of the image from which user photos will be created.
  628. * @return string/bool Returns the resulting common file name of created images which usually should be stored in database.
  629. * When deletion is recuested returns empty string. In case of internal error or negative validation returns FALSE.
  630. */
  631. public static function update_user_picture($user_id, $file = null, $source_file = null) {
  632. // Validation 1.
  633. if (empty($user_id)) {
  634. return false;
  635. }
  636. $delete = empty($file);
  637. if (empty($source_file)) {
  638. $source_file = $file;
  639. }
  640. // Configuration options about user photos.
  641. require_once api_get_path(CONFIGURATION_PATH).'profile.conf.php';
  642. // User-reserved directory where photos have to be placed.
  643. $path_info = self::get_user_picture_path_by_id($user_id, 'system', true);
  644. $path = $path_info['dir'];
  645. // If this directory does not exist - we create it.
  646. if (!file_exists($path)) {
  647. @mkdir($path, api_get_permissions_for_new_directories(), true);
  648. }
  649. // The old photos (if any).
  650. $old_file = $path_info['file'];
  651. // Let us delete them.
  652. if (!empty($old_file)) {
  653. if (KEEP_THE_OLD_IMAGE_AFTER_CHANGE) {
  654. $prefix = 'saved_'.date('Y_m_d_H_i_s').'_'.uniqid('').'_';
  655. @rename($path.'small_'.$old_file, $path.$prefix.'small_'.$old_file);
  656. @rename($path.'medium_'.$old_file, $path.$prefix.'medium_'.$old_file);
  657. @rename($path.'big_'.$old_file, $path.$prefix.'big_'.$old_file);
  658. @rename($path.$old_file, $path.$prefix.$old_file);
  659. } else {
  660. @unlink($path.'small_'.$old_file);
  661. @unlink($path.'medium_'.$old_file);
  662. @unlink($path.'big_'.$old_file);
  663. @unlink($path.$old_file);
  664. }
  665. }
  666. // Exit if only deletion has been requested. Return an empty picture name.
  667. if ($delete) {
  668. return '';
  669. }
  670. // Validation 2.
  671. $allowed_types = array('jpg', 'jpeg', 'png', 'gif');
  672. $file = str_replace('\\', '/', $file);
  673. $filename = (($pos = strrpos($file, '/')) !== false) ? substr($file, $pos + 1) : $file;
  674. $extension = strtolower(substr(strrchr($filename, '.'), 1));
  675. if (!in_array($extension, $allowed_types)) {
  676. return false;
  677. }
  678. // This is the common name for the new photos.
  679. if (KEEP_THE_NAME_WHEN_CHANGE_IMAGE && !empty($old_file)) {
  680. $old_extension = strtolower(substr(strrchr($old_file, '.'), 1));
  681. $filename = in_array($old_extension, $allowed_types) ? substr($old_file, 0, -strlen($old_extension)) : $old_file;
  682. $filename = (substr($filename, -1) == '.') ? $filename.$extension : $filename.'.'.$extension;
  683. } else {
  684. $filename = replace_dangerous_char($filename);
  685. if (PREFIX_IMAGE_FILENAME_WITH_UID) {
  686. $filename = uniqid('').'_'.$filename;
  687. }
  688. // We always prefix user photos with user ids, so on setting
  689. // api_get_setting('split_users_upload_directory') === 'true'
  690. // the correspondent directories to be found successfully.
  691. $filename = $user_id.'_'.$filename;
  692. }
  693. // Storing the new photos in 4 versions with various sizes.
  694. $picture_info = @getimagesize($source_file);
  695. $type = $picture_info[2];
  696. $small = self::resize_picture($source_file, 22);
  697. $medium = self::resize_picture($source_file, 85);
  698. $normal = self::resize_picture($source_file, 200);
  699. $big = new image($source_file); // This is the original picture.
  700. $ok = false;
  701. $detected = array(1 => 'GIF', 2 => 'JPG', 3 => 'PNG');
  702. if (in_array($type, array_keys($detected))) {
  703. $ok = $small->send_image($detected[$type], $path.'small_'.$filename)
  704. && $medium->send_image($detected[$type], $path.'medium_'.$filename)
  705. && $normal->send_image($detected[$type], $path.$filename)
  706. && $big->send_image($detected[$type], $path.'big_'.$filename);
  707. }
  708. return $ok ? $filename : false;
  709. }
  710. /**
  711. * Deletes user pfotos.
  712. * Note: This method relies on configuration setting from dokeos/main/inc/conf/profile.conf.php
  713. * @param int $user_id The user internal identitfication number.
  714. * @return string/bool Returns empty string on success, FALSE on error.
  715. */
  716. public static function delete_user_picture($user_id) {
  717. return self::update_user_picture($user_id);
  718. }
  719. /*
  720. -----------------------------------------------------------
  721. PRODUCTIONS FUNCTIONS
  722. -----------------------------------------------------------
  723. */
  724. /**
  725. * Returns an XHTML formatted list of productions for a user, or FALSE if he
  726. * doesn't have any.
  727. *
  728. * If there has been a request to remove a production, the function will return
  729. * without building the list unless forced to do so by the optional second
  730. * parameter. This increases performance by avoiding to read through the
  731. * productions on the filesystem before the removal request has been carried
  732. * out because they'll have to be re-read afterwards anyway.
  733. *
  734. * @param $user_id User id
  735. * @param $force Optional parameter to force building after a removal request
  736. * @return A string containing the XHTML code to dipslay the production list, or FALSE
  737. */
  738. public static function build_production_list($user_id, $force = false, $showdelete = false) {
  739. if (!$force && !empty($_POST['remove_production'])) {
  740. return true; // postpone reading from the filesystem
  741. }
  742. $productions = self::get_user_productions($user_id);
  743. if (empty($productions)) {
  744. return false;
  745. }
  746. $production_path = self::get_user_picture_path_by_id($user_id, 'web', true);
  747. $production_dir = $production_path['dir'].$user_id.'/';
  748. $del_image = api_get_path(WEB_CODE_PATH).'img/delete.gif';
  749. $del_text = get_lang('Delete');
  750. $production_list = '';
  751. if (count($productions) > 0) {
  752. $production_list = '<ul id="productions">';
  753. foreach ($productions as $file) {
  754. $production_list .= '<li><a href="'.$production_dir.urlencode($file).'" target="_blank">'.htmlentities($file).'</a>';
  755. if ($showdelete) {
  756. $production_list .= '<input type="image" name="remove_production['.urlencode($file).']" src="'.$del_image.'" alt="'.$del_text.'" title="'.$del_text.' '.htmlentities($file).'" onclick="javascript: return confirmation(\''.htmlentities($file).'\');" /></li>';
  757. }
  758. }
  759. $production_list .= '</ul>';
  760. }
  761. return $production_list;
  762. }
  763. /**
  764. * Returns an array with the user's productions.
  765. *
  766. * @param $user_id User id
  767. * @return An array containing the user's productions
  768. */
  769. public static function get_user_productions($user_id) {
  770. $production_path = self::get_user_picture_path_by_id($user_id, 'system', true);
  771. $production_repository = $production_path['dir'].$user_id.'/';
  772. $productions = array();
  773. if (is_dir($production_repository)) {
  774. $handle = opendir($production_repository);
  775. while ($file = readdir($handle)) {
  776. if ($file == '.' || $file == '..' || $file == '.htaccess' || is_dir($production_repository.$file)) {
  777. continue; // skip current/parent directory and .htaccess
  778. }
  779. if (preg_match('/('.$user_id.'|[0-9a-f]{13}|saved)_.+\.(png|jpg|jpeg|gif)$/i', $file)) {
  780. // User's photos should not be listed as productions.
  781. continue;
  782. }
  783. $productions[] = $file;
  784. }
  785. }
  786. return $productions; // can be an empty array
  787. }
  788. /**
  789. * Remove a user production.
  790. *
  791. * @param $user_id User id
  792. * @param $production The production to remove
  793. */
  794. public static function remove_user_production($user_id, $production) {
  795. $production_path = self::get_user_picture_path_by_id($user_id, 'system', true);
  796. if (is_file($production_path['dir'].$production)) {
  797. unlink($production_path['dir'].$production);
  798. }
  799. }
  800. /**
  801. * Update an extra field. This function is called when a user changes his/her profile
  802. * and by consequence fills or edits his/her extra fields.
  803. *
  804. * @param integer Field ID
  805. * @param array Database columns and their new value
  806. * @return boolean true if field updated, false otherwise
  807. */
  808. public static function update_extra_field($fid, $columns) {
  809. //TODO check that values added are values proposed for enumerated field types
  810. $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  811. $fid = Database::escape_string($fid);
  812. $sqluf = "UPDATE $t_uf SET ";
  813. $known_fields = array('id', 'field_variable', 'field_type', 'field_display_text', 'field_default_value', 'field_order', 'field_visible', 'field_changeable', 'field_filter');
  814. $safecolumns = array();
  815. foreach ($columns as $index => $newval) {
  816. if (in_array($index, $known_fields)) {
  817. $safecolumns[$index] = Database::escape_string($newval);
  818. $sqluf .= $index." = '".$safecolumns[$index]."', ";
  819. }
  820. }
  821. $time = time();
  822. $sqluf .= " tms = FROM_UNIXTIME($time) WHERE id='$fid'";
  823. $resuf = Database::query($sqluf);
  824. return $resuf;
  825. }
  826. /**
  827. * Update an extra field value for a given user
  828. * @param integer User ID
  829. * @param string Field variable name
  830. * @param string Field value
  831. * @return boolean true if field updated, false otherwise
  832. */
  833. public static function update_extra_field_value($user_id, $fname, $fvalue = '') {
  834. //TODO check that values added are values proposed for enumerated field types
  835. $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  836. $t_ufo = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  837. $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  838. $fname = Database::escape_string($fname);
  839. if ($user_id != strval(intval($user_id))) return false;
  840. if ($user_id === false) return false;
  841. $fvalues = '';
  842. //echo '<pre>'; print_r($fvalue);
  843. if (is_array($fvalue)) {
  844. foreach($fvalue as $val) {
  845. $fvalues .= Database::escape_string($val).';';
  846. }
  847. if (!empty($fvalues)) {
  848. $fvalues = substr($fvalues, 0, -1);
  849. }
  850. } else {
  851. $fvalues = Database::escape_string($fvalue);
  852. }
  853. $sqluf = "SELECT * FROM $t_uf WHERE field_variable='$fname'";
  854. $resuf = Database::query($sqluf);
  855. if (Database::num_rows($resuf) == 1) {
  856. //ok, the field exists
  857. // Check if enumerated field, if the option is available
  858. $rowuf = Database::fetch_array($resuf);
  859. switch ($rowuf['field_type']) {
  860. case USER_FIELD_TYPE_TAG :
  861. //4. Tags are process here comes from main/auth/profile.php
  862. UserManager::process_tags(explode(';', $fvalues), $user_id, $rowuf['id']);
  863. return true;
  864. break;
  865. case USER_FIELD_TYPE_RADIO:
  866. case USER_FIELD_TYPE_SELECT:
  867. case USER_FIELD_TYPE_SELECT_MULTIPLE:
  868. $sqluo = "SELECT * FROM $t_ufo WHERE field_id = ".$rowuf['id'];
  869. $resuo = Database::query($sqluo);
  870. $values = split(';',$fvalues);
  871. if (Database::num_rows($resuo) > 0) {
  872. $check = false;
  873. while ($rowuo = Database::fetch_array($resuo)) {
  874. if (in_array($rowuo['option_value'], $values)) {
  875. $check = true;
  876. break;
  877. }
  878. }
  879. if ($check == false) {
  880. return false; //option value not found
  881. }
  882. } else {
  883. return false; //enumerated type but no option found
  884. }
  885. break;
  886. case 1:
  887. case 2:
  888. default:
  889. break;
  890. }
  891. $tms = time();
  892. $sqlufv = "SELECT * FROM $t_ufv WHERE user_id = $user_id AND field_id = ".$rowuf['id']." ORDER BY id";
  893. $resufv = Database::query($sqlufv);
  894. $n = Database::num_rows($resufv);
  895. if ($n > 1) {
  896. //problem, we already have to values for this field and user combination - keep last one
  897. while ($rowufv = Database::fetch_array($resufv)) {
  898. if ($n > 1) {
  899. $sqld = "DELETE FROM $t_ufv WHERE id = ".$rowufv['id'];
  900. $resd = Database::query($sqld);
  901. $n--;
  902. }
  903. $rowufv = Database::fetch_array($resufv);
  904. if ($rowufv['field_value'] != $fvalues) {
  905. $sqlu = "UPDATE $t_ufv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowufv['id'];
  906. $resu = Database::query($sqlu);
  907. return($resu ? true : false);
  908. }
  909. return true;
  910. }
  911. }
  912. elseif ($n == 1) {
  913. //we need to update the current record
  914. $rowufv = Database::fetch_array($resufv);
  915. if ($rowufv['field_value'] != $fvalues) {
  916. // If the new field is empty, delete it
  917. if ($fvalues == '') {
  918. $sql_query = "DELETE FROM $t_ufv WHERE id = ".$rowufv['id'].";";
  919. } else {
  920. // Otherwise update it
  921. $sql_query = "UPDATE $t_ufv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowufv['id'];
  922. }
  923. $resu = Database::query($sql_query);
  924. return($resu ? true : false);
  925. }
  926. return true;
  927. } else {
  928. $sqli = "INSERT INTO $t_ufv (user_id,field_id,field_value,tms) " .
  929. "VALUES ($user_id,".$rowuf['id'].",'$fvalues',FROM_UNIXTIME($tms))";
  930. //error_log('UM::update_extra_field_value: '.$sqli);
  931. $resi = Database::query($sqli);
  932. return($resi ? true : false);
  933. }
  934. } else {
  935. return false; //field not found
  936. }
  937. }
  938. /**
  939. * Get an array of extra fieds with field details (type, default value and options)
  940. * @param integer Offset (from which row)
  941. * @param integer Number of items
  942. * @param integer Column on which sorting is made
  943. * @param string Sorting direction
  944. * @param boolean Optional. Whether we get all the fields or just the visible ones
  945. * @return array Extra fields details (e.g. $list[2]['type'], $list[4]['options'][2]['title']
  946. */
  947. public static function get_extra_fields($from = 0, $number_of_items = 0, $column = 5, $direction = 'ASC', $all_visibility = true) {
  948. $fields = array();
  949. $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
  950. $t_ufo = Database :: get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  951. $columns = array('id', 'field_variable', 'field_type', 'field_display_text', 'field_default_value', 'field_order', 'field_filter', 'tms');
  952. $column = intval($column);
  953. $sort_direction = '';
  954. if (in_array(strtoupper($direction), array('ASC', 'DESC'))) {
  955. $sort_direction = strtoupper($direction);
  956. }
  957. $sqlf = "SELECT * FROM $t_uf ";
  958. if ($all_visibility == false) {
  959. $sqlf .= " WHERE field_visible = 1 ";
  960. }
  961. $sqlf .= " ORDER BY ".$columns[$column]." $sort_direction " ;
  962. if ($number_of_items != 0) {
  963. $sqlf .= " LIMIT ".Database::escape_string($from).','.Database::escape_string($number_of_items);
  964. }
  965. $resf = Database::query($sqlf);
  966. if (Database::num_rows($resf) > 0) {
  967. while($rowf = Database::fetch_array($resf)) {
  968. $fields[$rowf['id']] = array(
  969. 0 => $rowf['id'],
  970. 1 => $rowf['field_variable'],
  971. 2 => $rowf['field_type'],
  972. //3 => (empty($rowf['field_display_text']) ? '' : get_lang($rowf['field_display_text'], '')),
  973. // Temporarily removed auto-translation. Need update to get_lang() to know if translation exists (todo)
  974. // Ivan, 15-SEP-2009: get_lang() has been modified accordingly in order this issue to be solved.
  975. 3 => (empty($rowf['field_display_text']) ? '' : $rowf['field_display_text']),
  976. 4 => $rowf['field_default_value'],
  977. 5 => $rowf['field_order'],
  978. 6 => $rowf['field_visible'],
  979. 7 => $rowf['field_changeable'],
  980. 8 => $rowf['field_filter'],
  981. 9 => array()
  982. );
  983. $sqlo = "SELECT * FROM $t_ufo WHERE field_id = ".$rowf['id']." ORDER BY option_order ASC";
  984. $reso = Database::query($sqlo);
  985. if (Database::num_rows($reso) > 0) {
  986. while ($rowo = Database::fetch_array($reso)) {
  987. $fields[$rowf['id']][9][$rowo['id']] = array(
  988. 0 => $rowo['id'],
  989. 1 => $rowo['option_value'],
  990. //2 => (empty($rowo['option_display_text']) ? '' : get_lang($rowo['option_display_text'], '')),
  991. 2 => (empty($rowo['option_display_text']) ? '' : $rowo['option_display_text']),
  992. 3 => $rowo['option_order']
  993. );
  994. }
  995. }
  996. }
  997. }
  998. return $fields;
  999. }
  1000. /**
  1001. * Get the list of options attached to an extra field
  1002. * @param string $fieldname the name of the field
  1003. * @return array the list of options
  1004. */
  1005. public static function get_extra_field_options($field_name) {
  1006. $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
  1007. $t_ufo = Database :: get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  1008. $sql = 'SELECT options.*
  1009. FROM '.$t_ufo.' options
  1010. INNER JOIN '.$t_uf.' fields
  1011. ON fields.id = options.field_id
  1012. AND fields.field_variable="'.Database::escape_string($field_name).'"';
  1013. $rs = Database::query($sql);
  1014. return Database::store_result($rs);
  1015. }
  1016. /**
  1017. * Get the number of extra fields currently recorded
  1018. * @param boolean Optional switch. true (default) returns all fields, false returns only visible fields
  1019. * @return integer Number of fields
  1020. */
  1021. public static function get_number_of_extra_fields($all_visibility = true) {
  1022. $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
  1023. $sqlf = "SELECT * FROM $t_uf ";
  1024. if ($all_visibility == false) {
  1025. $sqlf .= " WHERE field_visible = 1 ";
  1026. }
  1027. $sqlf .= " ORDER BY field_order";
  1028. $resf = Database::query($sqlf);
  1029. return Database::num_rows($resf);
  1030. }
  1031. /**
  1032. * Creates a new extra field
  1033. * @param string Field's internal variable name
  1034. * @param int Field's type
  1035. * @param string Field's language var name
  1036. * @param string Field's default value
  1037. * @param string Optional comma-separated list of options to provide for select and radio
  1038. * @return int new user id - if the new user creation succeeds, false otherwise
  1039. */
  1040. public static function create_extra_field($fieldvarname, $fieldtype, $fieldtitle, $fielddefault, $fieldoptions = '') {
  1041. // database table definition
  1042. $table_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1043. $table_field_options= Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  1044. // First check wether the login already exists
  1045. if (self::is_extra_field_available($fieldvarname)) {
  1046. return api_set_failure('login-pass already taken');
  1047. }
  1048. $sql = "SELECT MAX(field_order) FROM $table_field";
  1049. $res = Database::query($sql);
  1050. $order = 0;
  1051. if (Database::num_rows($res) > 0) {
  1052. $row = Database::fetch_array($res);
  1053. $order = $row[0]+1;
  1054. }
  1055. $time = time();
  1056. $sql = "INSERT INTO $table_field
  1057. SET field_type = '".Database::escape_string($fieldtype)."',
  1058. field_variable = '".Database::escape_string($fieldvarname)."',
  1059. field_display_text = '".Database::escape_string($fieldtitle)."',
  1060. field_default_value = '".Database::escape_string($fielddefault)."',
  1061. field_order = '$order',
  1062. tms = FROM_UNIXTIME($time)";
  1063. $result = Database::query($sql);
  1064. if ($result) {
  1065. //echo "id returned";
  1066. $return = Database::insert_id();
  1067. } else {
  1068. //echo "false - failed" ;
  1069. return false;
  1070. }
  1071. if (!empty($fieldoptions) && in_array($fieldtype, array(USER_FIELD_TYPE_RADIO, USER_FIELD_TYPE_SELECT, USER_FIELD_TYPE_SELECT_MULTIPLE, USER_FIELD_TYPE_DOUBLE_SELECT))) {
  1072. if ($fieldtype == USER_FIELD_TYPE_DOUBLE_SELECT) {
  1073. $twolist = explode('|', $fieldoptions);
  1074. $counter = 0;
  1075. foreach ($twolist as $individual_list) {
  1076. $splitted_individual_list = split(';', $individual_list);
  1077. foreach ($splitted_individual_list as $individual_list_option) {
  1078. //echo 'counter:'.$counter;
  1079. if ($counter == 0) {
  1080. $list[] = $individual_list_option;
  1081. } else {
  1082. $list[] = str_repeat('*', $counter).$individual_list_option;
  1083. }
  1084. }
  1085. $counter++;
  1086. }
  1087. } else {
  1088. $list = split(';', $fieldoptions);
  1089. }
  1090. foreach ($list as $option) {
  1091. $option = Database::escape_string($option);
  1092. $sql = "SELECT * FROM $table_field_options WHERE field_id = $return AND option_value = '".$option."'";
  1093. $res = Database::query($sql);
  1094. if (Database::num_rows($res) > 0) {
  1095. //the option already exists, do nothing
  1096. } else {
  1097. $sql = "SELECT MAX(option_order) FROM $table_field_options WHERE field_id = $return";
  1098. $res = Database::query($sql);
  1099. $max = 1;
  1100. if (Database::num_rows($res) > 0) {
  1101. $row = Database::fetch_array($res);
  1102. $max = $row[0] + 1;
  1103. }
  1104. $time = time();
  1105. $sql = "INSERT INTO $table_field_options (field_id,option_value,option_display_text,option_order,tms) VALUES ($return,'$option','$option',$max,FROM_UNIXTIME($time))";
  1106. $res = Database::query($sql);
  1107. if ($res === false) {
  1108. $return = false;
  1109. }
  1110. }
  1111. }
  1112. }
  1113. return $return;
  1114. }
  1115. /**
  1116. * Save the changes in the definition of the extra user profile field
  1117. * The function is called after you (as admin) decide to store the changes you have made to one of the fields you defined
  1118. *
  1119. * There is quite some logic in this field
  1120. * 1. store the changes to the field (tupe, name, label, default text)
  1121. * 2. remove the options and the choices of the users from the database that no longer occur in the form field 'possible values'. We should only remove
  1122. * the options (and choices) that do no longer have to appear. We cannot remove all options and choices because if you remove them all
  1123. * and simply re-add them all then all the user who have already filled this form will loose their selected value.
  1124. * 3. we add the options that are newly added
  1125. *
  1126. * @example current options are a;b;c and the user changes this to a;b;x (removing c and adding x)
  1127. * we first remove c (and also the entry in the option_value table for the users who have chosen this)
  1128. * we then add x
  1129. * a and b are neither removed nor added
  1130. *
  1131. * @param integer $fieldid the id of the field we are editing
  1132. * @param string $fieldvarname the internal variable name of the field
  1133. * @param int $fieldtype the type of the field
  1134. * @param string $fieldtitle the title of the field
  1135. * @param string $fielddefault the default value of the field
  1136. * @param string $fieldoptions Optional comma-separated list of options to provide for select and radio
  1137. * @return boolean true
  1138. *
  1139. *
  1140. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1141. * @version July 2008
  1142. * @since Dokeos 1.8.6
  1143. */
  1144. public static function save_extra_field_changes($fieldid, $fieldvarname, $fieldtype, $fieldtitle, $fielddefault, $fieldoptions = '') {
  1145. // database table definition
  1146. $table_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1147. $table_field_options = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  1148. $table_field_options_values = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  1149. // we first update the field definition with the new values
  1150. $time = time();
  1151. $sql = "UPDATE $table_field
  1152. SET field_type = '".Database::escape_string($fieldtype)."',
  1153. field_variable = '".Database::escape_string($fieldvarname)."',
  1154. field_display_text = '".Database::escape_string($fieldtitle)."',
  1155. field_default_value = '".Database::escape_string($fielddefault)."',
  1156. tms = FROM_UNIXTIME($time)
  1157. WHERE id = '".Database::escape_string($fieldid)."'";
  1158. $result = Database::query($sql);
  1159. // we create an array with all the options (will be used later in the script)
  1160. if ($fieldtype == USER_FIELD_TYPE_DOUBLE_SELECT) {
  1161. $twolist = explode('|', $fieldoptions);
  1162. $counter = 0;
  1163. foreach ($twolist as $individual_list) {
  1164. $splitted_individual_list = split(';', $individual_list);
  1165. foreach ($splitted_individual_list as $individual_list_option) {
  1166. //echo 'counter:'.$counter;
  1167. if ($counter == 0) {
  1168. $list[] = trim($individual_list_option);
  1169. } else {
  1170. $list[] = str_repeat('*', $counter).trim($individual_list_option);
  1171. }
  1172. }
  1173. $counter++;
  1174. }
  1175. } else {
  1176. $templist = split(';', $fieldoptions);
  1177. $list = array_map('trim', $templist);
  1178. }
  1179. // Remove all the field options (and also the choices of the user) that are NOT in the new list of options
  1180. $sql = "SELECT * FROM $table_field_options WHERE option_value NOT IN ('".implode("','", $list)."') AND field_id = '".Database::escape_string($fieldid)."'";
  1181. $result = Database::query($sql);
  1182. $return['deleted_options'] = 0;
  1183. while ($row = Database::fetch_array($result)) {
  1184. // deleting the option
  1185. $sql_delete_option = "DELETE FROM $table_field_options WHERE id='".Database::escape_string($row['id'])."'";
  1186. $result_delete_option = Database::query($sql_delete_option);
  1187. $return['deleted_options']++;
  1188. // deleting the answer of the user who has chosen this option
  1189. $sql_delete_option_value = "DELETE FROM $table_field_options_values WHERE field_id = '".Database::escape_string($fieldid)."' AND field_value = '".Database::escape_string($row['option_value'])."'";
  1190. $result_delete_option_value = Database::query($sql_delete_option_value);
  1191. $return['deleted_option_values'] = $return['deleted_option_values'] + Database::affected_rows();
  1192. }
  1193. // we now try to find the field options that are newly added
  1194. $sql = "SELECT * FROM $table_field_options WHERE field_id = '".Database::escape_string($fieldid)."'";
  1195. $result = Database::query($sql);
  1196. while ($row = Database::fetch_array($result)) {
  1197. // we remove every option that is already in the database from the $list
  1198. if (in_array(trim($row['option_display_text']), $list)) {
  1199. $key = array_search(trim($row['option_display_text']), $list);
  1200. unset($list[$key]);
  1201. }
  1202. }
  1203. // we store the new field options in the database
  1204. foreach ($list as $key => $option) {
  1205. $sql = "SELECT MAX(option_order) FROM $table_field_options WHERE field_id = '".Database::escape_string($fieldid)."'";
  1206. $res = Database::query($sql);
  1207. $max = 1;
  1208. if (Database::num_rows($res) > 0) {
  1209. $row = Database::fetch_array($res);
  1210. $max = $row[0] + 1;
  1211. }
  1212. $time = time();
  1213. $sql = "INSERT INTO $table_field_options (field_id,option_value,option_display_text,option_order,tms) VALUES ('".Database::escape_string($fieldid)."','".Database::escape_string($option)."','".Database::escape_string($option)."',$max,FROM_UNIXTIME($time))";
  1214. $result = Database::query($sql);
  1215. }
  1216. return true;
  1217. }
  1218. /**
  1219. * Check if a field is available
  1220. * @param string the wanted fieldname
  1221. * @return boolean true if the wanted username is available
  1222. */
  1223. public static function is_extra_field_available($fieldname) {
  1224. $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
  1225. $sql = "SELECT * FROM $t_uf WHERE field_variable = '".Database::escape_string($fieldname)."'";
  1226. $res = Database::query($sql);
  1227. return Database::num_rows($res) > 0;
  1228. }
  1229. /**
  1230. * Gets user extra fields data
  1231. * @param integer User ID
  1232. * @param boolean Whether to prefix the fields indexes with "extra_" (might be used by formvalidator)
  1233. * @param boolean Whether to return invisible fields as well
  1234. * @param boolean Whether to split multiple-selection fields or not
  1235. * @return array Array of fields => value for the given user
  1236. */
  1237. public static function get_extra_user_data($user_id, $prefix = false, $all_visibility = true, $splitmultiple = false) {
  1238. // A sanity check.
  1239. if (empty($user_id)) {
  1240. $user_id = 0;
  1241. } else {
  1242. if ($user_id != strval(intval($user_id))) return array();
  1243. }
  1244. $extra_data = array();
  1245. $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1246. $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  1247. $user_id = Database::escape_string($user_id);
  1248. $sql = "SELECT f.id as id, f.field_variable as fvar, f.field_type as type FROM $t_uf f ";
  1249. if ($all_visibility == false) {
  1250. $sql .= " WHERE f.field_visible = 1 ";
  1251. }
  1252. $sql .= " ORDER BY f.field_order";
  1253. $res = Database::query($sql);
  1254. if (Database::num_rows($res) > 0) {
  1255. while ($row = Database::fetch_array($res)) {
  1256. if ($row['type'] == USER_FIELD_TYPE_TAG) {
  1257. $tags = self::get_user_tags_to_string($user_id,$row['id'],false);
  1258. $extra_data['extra_'.$row['fvar']] = $tags;
  1259. } else {
  1260. $sqlu = "SELECT field_value as fval " .
  1261. " FROM $t_ufv " .
  1262. " WHERE field_id=".$row['id']."" .
  1263. " AND user_id=".$user_id;
  1264. $resu = Database::query($sqlu);
  1265. $fval = '';
  1266. // get default value
  1267. $sql_df = "SELECT field_default_value as fval_df " .
  1268. " FROM $t_uf " .
  1269. " WHERE id=".$row['id'];
  1270. $res_df = Database::query($sql_df);
  1271. if (Database::num_rows($resu) > 0) {
  1272. $rowu = Database::fetch_array($resu);
  1273. $fval = $rowu['fval'];
  1274. if ($row['type'] == USER_FIELD_TYPE_SELECT_MULTIPLE) {
  1275. $fval = split(';',$rowu['fval']);
  1276. }
  1277. } else {
  1278. $row_df = Database::fetch_array($res_df);
  1279. $fval = $row_df['fval_df'];
  1280. }
  1281. if ($prefix) {
  1282. if ($row['type'] == USER_FIELD_TYPE_RADIO) {
  1283. $extra_data['extra_'.$row['fvar']]['extra_'.$row['fvar']] = $fval;
  1284. } else {
  1285. $extra_data['extra_'.$row['fvar']] = $fval;
  1286. }
  1287. } else {
  1288. if ($row['type'] == USER_FIELD_TYPE_RADIO) {
  1289. $extra_data['extra_'.$row['fvar']]['extra_'.$row['fvar']] = $fval;
  1290. } else {
  1291. $extra_data[$row['fvar']] = $fval;
  1292. }
  1293. }
  1294. }
  1295. }
  1296. }
  1297. return $extra_data;
  1298. }
  1299. /** Get extra user data by field
  1300. * @param int user ID
  1301. * @param string the internal variable name of the field
  1302. * @return array with extra data info of a user i.e array('field_variable'=>'value');
  1303. */
  1304. public static function get_extra_user_data_by_field($user_id, $field_variable, $prefix = false, $all_visibility = true, $splitmultiple = false) {
  1305. // A sanity check.
  1306. if (empty($user_id)) {
  1307. $user_id = 0;
  1308. } else {
  1309. if ($user_id != strval(intval($user_id))) return array();
  1310. }
  1311. $extra_data = array();
  1312. $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1313. $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  1314. $user_id = Database::escape_string($user_id);
  1315. $sql = "SELECT f.id as id, f.field_variable as fvar, f.field_type as type FROM $t_uf f ";
  1316. $sql .= " WHERE f.field_variable = '$field_variable' ";
  1317. if ($all_visibility == false) {
  1318. $sql .= " AND f.field_visible = 1 ";
  1319. }
  1320. $sql .= " ORDER BY f.field_order";
  1321. $res = Database::query($sql);
  1322. if (Database::num_rows($res) > 0) {
  1323. while ($row = Database::fetch_array($res)) {
  1324. $sqlu = "SELECT field_value as fval " .
  1325. " FROM $t_ufv " .
  1326. " WHERE field_id=".$row['id']."" .
  1327. " AND user_id=".$user_id;
  1328. $resu = Database::query($sqlu);
  1329. $fval = '';
  1330. if (Database::num_rows($resu) > 0) {
  1331. $rowu = Database::fetch_array($resu);
  1332. $fval = $rowu['fval'];
  1333. if ($row['type'] == USER_FIELD_TYPE_SELECT_MULTIPLE) {
  1334. $fval = split(';',$rowu['fval']);
  1335. }
  1336. }
  1337. if ($prefix) {
  1338. $extra_data['extra_'.$row['fvar']] = $fval;
  1339. } else {
  1340. $extra_data[$row['fvar']] = $fval;
  1341. }
  1342. }
  1343. }
  1344. return $extra_data;
  1345. }
  1346. /**
  1347. * Get the extra field information for a certain field (the options as well)
  1348. * @param int The name of the field we want to know everything about
  1349. * @return array Array containing all the information about the extra profile field (first level of array contains field details, then 'options' sub-array contains options details, as returned by the database)
  1350. * @author Julio Montoya
  1351. * @since Dokeos 1.8.6
  1352. */
  1353. public static function get_extra_field_information_by_name($field_variable, $fuzzy = false) {
  1354. // database table definition
  1355. $table_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1356. $table_field_options = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  1357. // all the information of the field
  1358. $sql = "SELECT * FROM $table_field WHERE field_variable='".Database::escape_string($field_variable)."'";
  1359. $result = Database::query($sql);
  1360. $return = Database::fetch_array($result);
  1361. // all the options of the field
  1362. $sql = "SELECT * FROM $table_field_options WHERE field_id='".Database::escape_string($return['id'])."' ORDER BY option_order ASC";
  1363. $result = Database::query($sql);
  1364. while ($row = Database::fetch_array($result)) {
  1365. $return['options'][$row['id']] = $row;
  1366. }
  1367. return $return;
  1368. }
  1369. public static function get_all_extra_field_by_type($field_type) {
  1370. // database table definition
  1371. $table_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1372. // all the information of the field
  1373. $sql = "SELECT * FROM $table_field WHERE field_type='".Database::escape_string($field_type)."'";
  1374. $result = Database::query($sql);
  1375. while ($row = Database::fetch_array($result)) {
  1376. $return[] = $row['id'];
  1377. }
  1378. return $return;
  1379. }
  1380. /**
  1381. * Get all the extra field information of a certain field (also the options)
  1382. *
  1383. * @param int $field_name the name of the field we want to know everything of
  1384. * @return array $return containing all th information about the extra profile field
  1385. * @author Julio Montoya
  1386. * @since Dokeos 1.8.6
  1387. */
  1388. public static function get_extra_field_information($field_id) {
  1389. // database table definition
  1390. $table_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1391. $table_field_options = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  1392. // all the information of the field
  1393. $sql = "SELECT * FROM $table_field WHERE id='".Database::escape_string($field_id)."'";
  1394. $result = Database::query($sql);
  1395. $return = Database::fetch_array($result);
  1396. // all the options of the field
  1397. $sql = "SELECT * FROM $table_field_options WHERE field_id='".Database::escape_string($field_id)."' ORDER BY option_order ASC";
  1398. $result = Database::query($sql);
  1399. while ($row = Database::fetch_array($result)) {
  1400. $return['options'][$row['id']] = $row;
  1401. }
  1402. return $return;
  1403. }
  1404. /** Get extra user data by value
  1405. * @param string the internal variable name of the field
  1406. * @param string the internal value of the field
  1407. * @return array with extra data info of a user i.e array('field_variable'=>'value');
  1408. */
  1409. public static function get_extra_user_data_by_value($field_variable, $field_value, $all_visibility = true) {
  1410. $extra_data = array();
  1411. $table_user_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1412. $table_user_field_values = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  1413. $table_user_field_options = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  1414. $where = '';
  1415. /*
  1416. if (is_array($field_variable_array) && is_array($field_value_array)) {
  1417. if (count($field_variable_array) == count($field_value_array)) {
  1418. $field_var_count = count($field_variable_array);
  1419. for ($i = 0; $i < $field_var_count; $i++) {
  1420. if ($i != 0 && $i != $field_var_count) {
  1421. $where.= ' AND ';
  1422. }
  1423. $where.= "field_variable='".Database::escape_string($field_variable_array[$i])."' AND user_field_options.id='".Database::escape_string($field_value_array[$i])."'";
  1424. }
  1425. }
  1426. }*/
  1427. $where = "field_variable='".Database::escape_string($field_variable)."' AND field_value='".Database::escape_string($field_value)."'";
  1428. $sql = "SELECT user_id FROM $table_user_field user_field INNER JOIN $table_user_field_values user_field_values
  1429. ON (user_field.id = user_field_values.field_id)
  1430. WHERE $where";
  1431. if ($all_visibility == true) {
  1432. $sql .= " AND user_field.field_visible = 1 ";
  1433. } else {
  1434. $sql .= " AND user_field.field_visible = 0 ";
  1435. }
  1436. $res = Database::query($sql);
  1437. $result_data = array();
  1438. if (Database::num_rows($res) > 0) {
  1439. while ($row = Database::fetch_array($res)) {
  1440. $result_data[] = $row['user_id'];
  1441. }
  1442. }
  1443. return $result_data;
  1444. }
  1445. /**
  1446. * Get extra user data by field variable
  1447. * @param string field variable
  1448. * @return array data
  1449. */
  1450. public static function get_extra_user_data_by_field_variable($field_variable) {
  1451. $tbl_user_field_values = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  1452. $extra_information_by_variable = self::get_extra_field_information_by_name($field_variable);
  1453. $field_id = intval($extra_information_by_variable['id']);
  1454. $data = array();
  1455. $sql = "SELECT * FROM $tbl_user_field_values WHERE field_id='$field_id'";
  1456. $rs = Database::query($sql);
  1457. if (Database::num_rows($rs) > 0) {
  1458. while ($row = Database::fetch_array($rs)) {
  1459. $user_id = $row['user_id'];
  1460. $data[$user_id] = $row;
  1461. }
  1462. }
  1463. return $data;
  1464. }
  1465. /**
  1466. * Gives a list of [session_category][session_id] for the current user.
  1467. * @param integer $user_id
  1468. * @param boolean whether to fill the first element or not (to give space for courses out of categories)
  1469. * @param boolean optional true if limit time from session is over, false otherwise
  1470. * @return array list of statuses [session_category][session_id]
  1471. * @todo ensure multiple access urls are managed correctly
  1472. */
  1473. public static function get_sessions_by_category ($user_id, $fill_first = false, $is_time_over = false) {
  1474. // Database Table Definitions
  1475. $tbl_session_user = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
  1476. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  1477. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  1478. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1479. if ($user_id != strval(intval($user_id))) return array();
  1480. $categories = array();
  1481. if ($fill_first) {
  1482. $categories[0] = array();
  1483. }
  1484. /*
  1485. //we filter the courses from the URL
  1486. $join_access_url=$where_access_url='';
  1487. global $_configuration;
  1488. if ($_configuration['multiple_access_urls']==true) {
  1489. $access_url_id = api_get_current_access_url_id();
  1490. if($access_url_id!=-1) {
  1491. $tbl_url_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  1492. $join_access_url= "LEFT JOIN $tbl_url_course url_rel_course ON url_rel_course.course_code= course.code";
  1493. $where_access_url=" AND access_url_id = $access_url_id ";
  1494. }
  1495. }
  1496. */
  1497. // get the list of sessions where the user is subscribed as student
  1498. $condition_date_end = "";
  1499. if ($is_time_over) {
  1500. $condition_date_end = " AND date_end < CURDATE() AND date_end != '0000-00-00' ";
  1501. } else {
  1502. $condition_date_end = " AND (date_end >= CURDATE() OR date_end = '0000-00-00') ";
  1503. }
  1504. $sessions_sql = "SELECT DISTINCT id, session_category_id
  1505. FROM $tbl_session_user, $tbl_session
  1506. WHERE id_session=id AND id_user=$user_id AND relation_type<>".SESSION_RELATION_TYPE_RRHH." $condition_date_end
  1507. ORDER BY session_category_id, date_start, date_end";
  1508. $result = Database::query($sessions_sql);
  1509. if (Database::num_rows($result) > 0) {
  1510. while ($row = Database::fetch_array($result)) {
  1511. $categories[$row['session_category_id']][] = $row['id'];
  1512. }
  1513. }
  1514. // get the list of sessions where the user is subscribed as coach in a course $tbl_session_course_user
  1515. /*$sessions_sql = "SELECT DISTINCT id, session_category_id
  1516. FROM $tbl_session as session
  1517. INNER JOIN $tbl_session_course as session_rel_course
  1518. ON session_rel_course.id_session = session.id
  1519. AND session_rel_course.id_coach = $user_id
  1520. ORDER BY session_category_id, date_start, date_end";*/
  1521. $sessions_sql = "SELECT DISTINCT id, session_category_id
  1522. FROM $tbl_session as session
  1523. INNER JOIN $tbl_session_course_user as session_rel_course_user
  1524. ON session_rel_course_user.id_session = session.id
  1525. AND session_rel_course_user.id_user = $user_id
  1526. AND session_rel_course_user.status = 2 $condition_date_end
  1527. ORDER BY session_category_id, date_start, date_end";
  1528. $result = Database::query($sessions_sql);
  1529. if (Database::num_rows($result)>0) {
  1530. while ($row = Database::fetch_array($result)) {
  1531. $categories[$row['session_category_id']][] = $row['id'];
  1532. }
  1533. }
  1534. // get the list of sessions where the user is subscribed as coach
  1535. $sessions_sql = "SELECT DISTINCT id, session_category_id
  1536. FROM $tbl_session as session
  1537. WHERE session.id_coach = $user_id $condition_date_end
  1538. ORDER BY session_category_id, date_start, date_end";
  1539. $result = Database::query($sessions_sql);
  1540. if (Database::num_rows($result)>0) {
  1541. while ($row = Database::fetch_array($result)) {
  1542. $categories[$row['session_category_id']][] = $row['id'];
  1543. }
  1544. }
  1545. return $categories;
  1546. }
  1547. /**
  1548. * Gives a list of [session_id-course_code] => [status] for the current user.
  1549. * @param integer $user_id
  1550. * @return array list of statuses (session_id-course_code => status)
  1551. */
  1552. public static function get_personal_session_course_list($user_id) {
  1553. global $_configuration;
  1554. // Database Table Definitions
  1555. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1556. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  1557. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  1558. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  1559. $tbl_session_user = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
  1560. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1561. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  1562. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1563. if ($user_id != strval(intval($user_id))) return array();
  1564. //we filter the courses from the URL
  1565. $join_access_url = $where_access_url = '';
  1566. if ($_configuration['multiple_access_urls'] == true) {
  1567. $access_url_id = api_get_current_access_url_id();
  1568. if ($access_url_id != -1) {
  1569. $tbl_url_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  1570. $join_access_url = "LEFT JOIN $tbl_url_course url_rel_course ON url_rel_course.course_code= course.code";
  1571. $where_access_url = " AND access_url_id = $access_url_id ";
  1572. }
  1573. }
  1574. // variable initialisation
  1575. $personal_course_list_sql = '';
  1576. //Courses in which we suscribed out of any session
  1577. /*$personal_course_list_sql = "SELECT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i,
  1578. course.tutor_name t, course.course_language l, course_rel_user.status s, course_rel_user.sort sort,
  1579. course_rel_user.user_course_cat user_course_cat
  1580. FROM ".$tbl_course." course,".$main_course_user_table." course_rel_user
  1581. WHERE course.code = course_rel_user.course_code"."
  1582. AND course_rel_user.user_id = '".$user_id."'
  1583. ORDER BY course_rel_user.user_course_cat, course_rel_user.sort ASC,i";*/
  1584. $tbl_user_course_category = Database :: get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  1585. $personal_course_list_sql = "SELECT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, course.tutor_name t, course.course_language l, course_rel_user.status s, course_rel_user.sort sort, course_rel_user.user_course_cat user_course_cat
  1586. FROM ".$tbl_course_user." course_rel_user
  1587. LEFT JOIN ".$tbl_course." course
  1588. ON course.code = course_rel_user.course_code
  1589. LEFT JOIN ".$tbl_user_course_category." user_course_category
  1590. ON course_rel_user.user_course_cat = user_course_category.id
  1591. $join_access_url
  1592. WHERE course_rel_user.user_id = '".$user_id."' AND course_rel_user.relation_type<>".COURSE_RELATION_TYPE_RRHH." $where_access_url
  1593. ORDER BY user_course_category.sort, course_rel_user.sort, course.title ASC";
  1594. $course_list_sql_result = api_sql_query($personal_course_list_sql);
  1595. $personal_course_list = array();
  1596. if (Database::num_rows($course_list_sql_result) > 0 ) {
  1597. while ($result_row = Database::fetch_array($course_list_sql_result)) {
  1598. $personal_course_list[] = $result_row;
  1599. }
  1600. }
  1601. // get the list of sessions where the user is subscribed as student
  1602. $sessions_sql = "SELECT DISTINCT id, name, date_start, date_end
  1603. FROM $tbl_session_user, $tbl_session
  1604. WHERE id_session=id AND id_user=$user_id AND relation_type<>".SESSION_RELATION_TYPE_RRHH."
  1605. AND (date_start <= CURDATE() AND date_end >= CURDATE() OR date_start='0000-00-00')
  1606. ORDER BY date_start, date_end, name";
  1607. $result = Database::query($sessions_sql);
  1608. $sessions=Database::store_result($result);
  1609. $sessions = array_merge($sessions , Database::store_result($result));
  1610. // get the list of sessions where the user is subscribed as student where visibility = SESSION_VISIBLE_READ_ONLY = 1 SESSION_VISIBLE = 2
  1611. $sessions_out_date_sql = "SELECT DISTINCT id, name, date_start, date_end
  1612. FROM $tbl_session_user, $tbl_session
  1613. WHERE id_session=id AND id_user=$user_id AND relation_type<>".SESSION_RELATION_TYPE_RRHH."
  1614. AND (date_end <= CURDATE() AND date_end<>'0000-00-00') AND (visibility = ".SESSION_VISIBLE_READ_ONLY." || visibility = ".SESSION_VISIBLE.")
  1615. ORDER BY date_start, date_end, name";
  1616. $result_out_date = Database::query($sessions_out_date_sql);
  1617. $sessions_out_date=Database::store_result($result_out_date);
  1618. $sessions = array_merge($sessions , $sessions_out_date);
  1619. // get the list of sessions where the user is subscribed as coach in a course
  1620. $sessions_sql = "SELECT DISTINCT id, name, date_start, date_end, DATE_SUB(date_start, INTERVAL nb_days_access_before_beginning DAY), ADDDATE(date_end, INTERVAL nb_days_access_after_end DAY)
  1621. FROM $tbl_session as session
  1622. INNER JOIN $tbl_session_course_user as session_rel_course_user
  1623. ON session_rel_course_user.id_session = session.id
  1624. AND session_rel_course_user.id_user = $user_id AND session_rel_course_user.status = 2
  1625. WHERE (CURDATE() >= DATE_SUB(date_start, INTERVAL nb_days_access_before_beginning DAY)
  1626. AND CURDATE() <= ADDDATE(date_end, INTERVAL nb_days_access_after_end DAY)
  1627. OR date_start='0000-00-00')
  1628. ORDER BY date_start, date_end, name";
  1629. $result = Database::query($sessions_sql);
  1630. $session_is_coach = Database::store_result($result);
  1631. $sessions = array_merge($sessions, $session_is_coach);
  1632. // get the list of sessions where the user is subscribed as coach
  1633. $sessions_sql = "SELECT DISTINCT id, name, date_start, date_end
  1634. FROM $tbl_session as session
  1635. WHERE session.id_coach = $user_id
  1636. AND (CURDATE() >= DATE_SUB(date_start, INTERVAL nb_days_access_before_beginning DAY)
  1637. AND CURDATE() <= ADDDATE(date_end, INTERVAL nb_days_access_after_end DAY)
  1638. OR date_start='0000-00-00')
  1639. ORDER BY date_start, date_end, name";
  1640. $result = Database::query($sessions_sql);
  1641. $sessions = array_merge($sessions, Database::store_result($result));
  1642. if (api_is_allowed_to_create_course()) {
  1643. foreach($sessions as $enreg) {
  1644. $id_session = $enreg['id'];
  1645. $personal_course_list_sql = "SELECT DISTINCT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, ".(api_is_western_name_order() ? "CONCAT(user.firstname,' ',user.lastname)" : "CONCAT(user.lastname,' ',user.firstname)")." t, email, course.course_language l, 1 sort, category_code user_course_cat, date_start, date_end, session.id as id_session, session.name as session_name
  1646. FROM $tbl_session_course_user as session_course_user
  1647. INNER JOIN $tbl_course AS course
  1648. ON course.code = session_course_user.course_code
  1649. INNER JOIN $tbl_session as session
  1650. ON session.id = session_course_user.id_session
  1651. LEFT JOIN $tbl_user as user
  1652. ON user.user_id = session_course_user.id_user
  1653. WHERE session_course_user.id_session = $id_session
  1654. AND ((session_course_user.id_user=$user_id AND session_course_user.status = 2) OR session.id_coach=$user_id)
  1655. ORDER BY i";
  1656. $course_list_sql_result = Database::query($personal_course_list_sql);
  1657. while ($result_row = Database::fetch_array($course_list_sql_result)) {
  1658. $result_row['s'] = 2;
  1659. $key = $result_row['id_session'].' - '.$result_row['k'];
  1660. $personal_course_list[$key] = $result_row;
  1661. }
  1662. }
  1663. }
  1664. foreach ($sessions as $enreg) {
  1665. $id_session = $enreg['id'];
  1666. /*$personal_course_list_sql = "SELECT DISTINCT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, CONCAT(user.lastname,' ',user.firstname) t, email, course.course_language l, 1 sort, category_code user_course_cat, date_start, date_end, session.id as id_session, session.name as session_name, IF(session_course.id_coach = ".$user_id.",'2', '5')
  1667. FROM $tbl_session_course as session_course
  1668. INNER JOIN $tbl_course AS course
  1669. ON course.code = session_course.course_code
  1670. LEFT JOIN $tbl_user as user
  1671. ON user.user_id = session_course.id_coach
  1672. INNER JOIN $tbl_session_course_user
  1673. ON $tbl_session_course_user.id_session = $id_session
  1674. AND $tbl_session_course_user.id_user = $user_id
  1675. INNER JOIN $tbl_session as session
  1676. ON session_course.id_session = session.id
  1677. WHERE session_course.id_session = $id_session
  1678. ORDER BY i";
  1679. */
  1680. // this query is very similar to the above query, but it will check the session_rel_course_user table if there are courses registered to our user or not
  1681. $personal_course_list_sql = "SELECT distinct course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, CONCAT(user.lastname,' ',user.firstname) t, email, course.course_language l, 1 sort, category_code user_course_cat, date_start, date_end, session.id as id_session, session.name as session_name, IF((session_course_user.id_user = 3 AND session_course_user.status=2),'2', '5')
  1682. FROM $tbl_session_course_user as session_course_user
  1683. INNER JOIN $tbl_course AS course
  1684. ON course.code = session_course_user.course_code AND session_course_user.id_session = $id_session
  1685. INNER JOIN $tbl_session as session ON session_course_user.id_session = session.id
  1686. LEFT JOIN $tbl_user as user ON user.user_id = session_course_user.id_user
  1687. WHERE session_course_user.id_user = $user_id ORDER BY i";
  1688. $course_list_sql_result = Database::query($personal_course_list_sql);
  1689. while ($result_row = Database::fetch_array($course_list_sql_result)) {
  1690. $key = $result_row['id_session'].' - '.$result_row['k'];
  1691. $result_row['s'] = $result_row['14'];
  1692. if (!isset($personal_course_list[$key])) {
  1693. $personal_course_list[$key] = $result_row;
  1694. }
  1695. }
  1696. }
  1697. //print_r($personal_course_list);
  1698. return $personal_course_list;
  1699. }
  1700. /**
  1701. * Gives a list of courses for the given user in the given session
  1702. * @param integer $user_id
  1703. * @return array list of statuses (session_id-course_code => status)
  1704. */
  1705. public static function get_courses_list_by_session ($user_id, $session_id) {
  1706. // Database Table Definitions
  1707. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  1708. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  1709. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1710. $user_id = intval($user_id);
  1711. $session_id = intval($session_id);
  1712. //we filter the courses from the URL
  1713. $join_access_url=$where_access_url='';
  1714. global $_configuration;
  1715. if ($_configuration['multiple_access_urls']==true) {
  1716. $access_url_id = api_get_current_access_url_id();
  1717. if($access_url_id!=-1) {
  1718. $tbl_url_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  1719. $join_access_url= " , $tbl_url_course url_rel_course ";
  1720. $where_access_url=" AND access_url_id = $access_url_id AND url_rel_course.course_code = scu.course_code";
  1721. }
  1722. }
  1723. // variable initialisation
  1724. $personal_course_list_sql = '';
  1725. $personal_course_list = array();
  1726. $courses = array();
  1727. // this query is very similar to the above query, but it will check the session_rel_course_user table if there are courses registered to our user or not
  1728. $personal_course_list_sql = "SELECT distinct scu.course_code as code
  1729. FROM $tbl_session_course_user as scu
  1730. $join_access_url
  1731. WHERE scu.id_user = $user_id
  1732. AND scu.id_session = $session_id
  1733. $where_access_url
  1734. ORDER BY code";
  1735. $course_list_sql_result = Database::query($personal_course_list_sql);
  1736. if (Database::num_rows($course_list_sql_result)>0) {
  1737. while ($result_row = Database::fetch_array($course_list_sql_result)) {
  1738. $result_row['status'] = 5;
  1739. if (!in_array($result_row['code'],$courses)) {
  1740. $personal_course_list[] = $result_row;
  1741. $courses[] = $result_row['code'];
  1742. }
  1743. }
  1744. }
  1745. if(api_is_allowed_to_create_course()) {
  1746. $personal_course_list_sql = "SELECT DISTINCT scu.course_code as code
  1747. FROM $tbl_session_course_user as scu, $tbl_session as s
  1748. $join_access_url
  1749. WHERE s.id = $session_id
  1750. AND scu.id_session = s.id
  1751. AND ((scu.id_user=$user_id AND scu.status=2) OR s.id_coach=$user_id)
  1752. $where_access_url
  1753. ORDER BY code";
  1754. $course_list_sql_result = Database::query($personal_course_list_sql);
  1755. if (Database::num_rows($course_list_sql_result)>0) {
  1756. while ($result_row = Database::fetch_array($course_list_sql_result)) {
  1757. $result_row['status'] = 2;
  1758. if (!in_array($result_row['code'],$courses)) {
  1759. $personal_course_list[] = $result_row;
  1760. $courses[] = $result_row['code'];
  1761. }
  1762. }
  1763. }
  1764. }
  1765. return $personal_course_list;
  1766. }
  1767. /**
  1768. * Get user id from a username
  1769. * @param string Username
  1770. * @return int User ID (or false if not found)
  1771. */
  1772. public static function get_user_id_from_username($username) {
  1773. $username = Database::escape_string($username);
  1774. $t_user = Database::get_main_table(TABLE_MAIN_USER);
  1775. $sql = "SELECT user_id FROM $t_user WHERE username = '$username'";
  1776. $res = Database::query($sql);
  1777. if ($res === false) { return false; }
  1778. if (Database::num_rows($res) !== 1) { return false; }
  1779. $row = Database::fetch_array($res);
  1780. return $row['user_id'];
  1781. }
  1782. /**
  1783. * Get the users files upload from his share_folder
  1784. * @param string User ID
  1785. * @param string course directory
  1786. * @return int User ID (or false if not found)
  1787. */
  1788. public static function get_user_upload_files_by_course($user_id, $course) {
  1789. $return = '';
  1790. if (!empty($user_id) && !empty($course)) {
  1791. $user_id = intval($user_id);
  1792. $path = api_get_path(SYS_COURSE_PATH).$course.'/document/shared_folder/sf_user_'.$user_id.'/';
  1793. $web_path = api_get_path(WEB_COURSE_PATH).$course.'/document/shared_folder/sf_user_'.$user_id.'/';
  1794. $file_list = array();
  1795. if (is_dir($path)) {
  1796. $handle = opendir($path);
  1797. while ($file = readdir($handle)) {
  1798. if ($file == '.' || $file == '..' || $file == '.htaccess' || is_dir($path.$file)) {
  1799. continue; // skip current/parent directory and .htaccess
  1800. }
  1801. $file_list[] = $file;
  1802. }
  1803. if (count($file_list) > 0) {
  1804. $return = $course;
  1805. $return .= '<ul>';
  1806. }
  1807. foreach ($file_list as $file) {
  1808. $return .= '<li><a href="'.$web_path.urlencode($file).'" target="_blank">'.htmlentities($file).'</a>';
  1809. }
  1810. if (count($file_list) > 0) {
  1811. $return .= '</ul>';
  1812. }
  1813. }
  1814. }
  1815. return $return;
  1816. }
  1817. /**
  1818. * Gets the API key (or keys) and return them into an array
  1819. * @param int Optional user id (defaults to the result of api_get_user_id())
  1820. * @result array Non-indexed array containing the list of API keys for this user, or FALSE on error
  1821. */
  1822. public static function get_api_keys($user_id = null, $api_service = 'dokeos') {
  1823. if ($user_id != strval(intval($user_id))) return false;
  1824. if (empty($user_id)) { $user_id = api_get_user_id(); }
  1825. if ($user_id === false) return false;
  1826. $service_name = Database::escape_string($api_service);
  1827. if (is_string($service_name) === false) { return false;}
  1828. $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
  1829. $sql = "SELECT id, api_key FROM $t_api WHERE user_id = ".$user_id." AND api_service='".$api_service."';";
  1830. $res = Database::query($sql);
  1831. if ($res === false) return false; //error during query
  1832. $num = Database::num_rows($res);
  1833. if ($num == 0) return false;
  1834. $list = array();
  1835. while ($row = Database::fetch_array($res)) {
  1836. $list[$row['id']] = $row['api_key'];
  1837. }
  1838. return $list;
  1839. }
  1840. /**
  1841. * Adds a new API key to the users' account
  1842. * @param int Optional user ID (defaults to the results of api_get_user_id())
  1843. * @return boolean True on success, false on failure
  1844. */
  1845. public static function add_api_key($user_id = null, $api_service = 'dokeos') {
  1846. if ($user_id != strval(intval($user_id))) return false;
  1847. if (empty($user_id)) { $user_id = api_get_user_id(); }
  1848. if ($user_id === false) return false;
  1849. $service_name = Database::escape_string($api_service);
  1850. if (is_string($service_name) === false) { return false; }
  1851. $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
  1852. $md5 = md5((time() + ($user_id * 5)) - rand(10000, 10000)); //generate some kind of random key
  1853. $sql = "INSERT INTO $t_api (user_id, api_key,api_service) VALUES ($user_id,'$md5','$service_name')";
  1854. $res = Database::query($sql);
  1855. if ($res === false) return false; //error during query
  1856. $num = Database::insert_id();
  1857. return ($num == 0) ? false : $num;
  1858. }
  1859. /**
  1860. * Deletes an API key from the user's account
  1861. * @param int API key's internal ID
  1862. * @return boolean True on success, false on failure
  1863. */
  1864. public static function delete_api_key($key_id) {
  1865. if ($key_id != strval(intval($key_id))) return false;
  1866. if ($key_id === false) return false;
  1867. $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
  1868. $sql = "SELECT * FROM $t_api WHERE id = ".$key_id;
  1869. $res = Database::query($sql);
  1870. if ($res === false) return false; //error during query
  1871. $num = Database::num_rows($res);
  1872. if ($num !== 1) return false;
  1873. $sql = "DELETE FROM $t_api WHERE id = ".$key_id;
  1874. $res = Database::query($sql);
  1875. if ($res === false) return false; //error during query
  1876. return true;
  1877. }
  1878. /**
  1879. * Regenerate an API key from the user's account
  1880. * @param int user ID (defaults to the results of api_get_user_id())
  1881. * @param string API key's internal ID
  1882. * @return int num
  1883. */
  1884. public static function update_api_key($user_id, $api_service) {
  1885. if ($user_id != strval(intval($user_id))) return false;
  1886. if ($user_id === false) return false;
  1887. $service_name = Database::escape_string($api_service);
  1888. if (is_string($service_name) === false) { return false; }
  1889. $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
  1890. $sql = "SELECT id FROM $t_api WHERE user_id=".$user_id." AND api_service='".$api_service."'";
  1891. $res = Database::query($sql);
  1892. $num = Database::num_rows($res);
  1893. if ($num == 1) {
  1894. $id_key = Database::fetch_array($res, 'ASSOC');
  1895. self::delete_api_key($id_key['id']);
  1896. $num = self::add_api_key($user_id, $api_service);
  1897. } elseif ($num == 0) {
  1898. $num = self::add_api_key($user_id);
  1899. }
  1900. return $num;
  1901. }
  1902. /**
  1903. * @param int user ID (defaults to the results of api_get_user_id())
  1904. * @param string API key's internal ID
  1905. * @return int row ID, not return a boolean
  1906. */
  1907. public static function get_api_key_id($user_id, $api_service) {
  1908. if ($user_id != strval(intval($user_id))) return false;
  1909. if ($user_id === false) return false;
  1910. $service_name = Database::escape_string($api_service);
  1911. if (is_string($service_name) === false) { return false; }
  1912. $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
  1913. $sql = "SELECT id FROM $t_api WHERE user_id=".$user_id." AND api_service='".$api_service."'";
  1914. $res = Database::query($sql);
  1915. $row = Database::fetch_array($res, 'ASSOC');
  1916. return $row['id'];
  1917. }
  1918. /**
  1919. * Checks if a user_id is platform admin
  1920. * @param int user ID
  1921. * @return boolean True if is admin, false otherwise
  1922. * @see main_api.lib.php::api_is_platform_admin() for a context-based check
  1923. */
  1924. public static function is_admin($user_id) {
  1925. if (empty($user_id) or $user_id != strval(intval($user_id))) { return false; }
  1926. $admin_table = Database::get_main_table(TABLE_MAIN_ADMIN);
  1927. $sql = "SELECT * FROM $admin_table WHERE user_id = $user_id";
  1928. $res = Database::query($sql);
  1929. return Database::num_rows($res) === 1;
  1930. }
  1931. /**
  1932. * Get the total count of users
  1933. * @return mixed Number of users or false on error
  1934. */
  1935. public static function get_number_of_users() {
  1936. $t_u = Database::get_main_table(TABLE_MAIN_USER);
  1937. $sql = "SELECT count(*) FROM $t_u";
  1938. $res = Database::query($sql);
  1939. if (Database::num_rows($res) === 1) {
  1940. return (int) Database::result($res, 0, 0);
  1941. }
  1942. return false;
  1943. }
  1944. /**
  1945. * Resize a picture
  1946. *
  1947. * @param string file picture
  1948. * @param int size in pixels
  1949. * @return obj image object
  1950. */
  1951. public static function resize_picture($file, $max_size_for_picture) {
  1952. if (!class_exists('image')) {
  1953. require_once api_get_path(LIBRARY_PATH).'image.lib.php';
  1954. }
  1955. $temp = new image($file);
  1956. $picture_infos = api_getimagesize($file);
  1957. if ($picture_infos[0] > $max_size_for_picture) {
  1958. $thumbwidth = $max_size_for_picture;
  1959. if (empty($thumbwidth) or $thumbwidth == 0) {
  1960. $thumbwidth = $max_size_for_picture;
  1961. }
  1962. $new_height = round(($thumbwidth / $picture_infos[0]) * $picture_infos[1]);
  1963. if ($new_height > $max_size_for_picture)
  1964. $new_height = $thumbwidth;
  1965. $temp->resize($thumbwidth, $new_height, 0);
  1966. }
  1967. return $temp;
  1968. }
  1969. /**
  1970. * Gets the current user image
  1971. * @param string user id
  1972. * @param string picture user name
  1973. * @param string height
  1974. * @param string picture size it can be USER_IMAGE_SIZE_SMALL, USER_IMAGE_SIZE_MEDIUM, USER_IMAGE_SIZE_BIG or USER_IMAGE_SIZE_ORIGINAL
  1975. * @param string style css
  1976. * @return array with the file and the style of an image i.e $array['file'] $array['style']
  1977. */
  1978. public static function get_picture_user($user_id, $picture_file, $height, $size_picture = USER_IMAGE_SIZE_MEDIUM , $style = '') {
  1979. $patch_profile = 'upload/users/';
  1980. $picture = array();
  1981. $picture['style'] = $style;
  1982. if ($picture_file == 'unknown.jpg') {
  1983. $picture['file'] = api_get_path(WEB_CODE_PATH).'img/'.$picture_file;
  1984. return $picture;
  1985. }
  1986. switch ($size_picture) {
  1987. case USER_IMAGE_SIZE_ORIGINAL :
  1988. $size_picture = '';
  1989. break;
  1990. case USER_IMAGE_SIZE_BIG :
  1991. $size_picture = 'big_';
  1992. break;
  1993. case USER_IMAGE_SIZE_MEDIUM :
  1994. $size_picture = 'medium_';
  1995. break;
  1996. case USER_IMAGE_SIZE_SMALL :
  1997. $size_picture = 'small_';
  1998. break;
  1999. default:
  2000. $size_picture = 'medium_';
  2001. }
  2002. $image_array_sys = self::get_user_picture_path_by_id($user_id, 'system', false, true);
  2003. $image_array = self::get_user_picture_path_by_id($user_id, 'web', false, true);
  2004. $file = $image_array_sys['dir'].$size_picture.$picture_file;
  2005. if (file_exists($file)) {
  2006. $picture['file'] = $image_array['dir'].$size_picture.$picture_file;
  2007. $picture['style'] = '';
  2008. if ($height > 0) {
  2009. $dimension = api_getimagesize($picture['file']);
  2010. $margin = (($height - $dimension[1]) / 2);
  2011. //@ todo the padding-top should not be here
  2012. $picture['style'] = ' style="padding-top:'.$margin.'px; width:'.$dimension[0].'px; height:'.$dimension[1].'px;" ';
  2013. }
  2014. } else {
  2015. //$file = api_get_path(SYS_CODE_PATH).$patch_profile.$user_id.'/'.$picture_file;
  2016. $file = $image_array_sys['dir'].$picture_file;
  2017. if (file_exists($file) && !is_dir($file)) {
  2018. $picture['file'] = $image_array['dir'].$picture_file;
  2019. } else {
  2020. switch ($size_picture) {
  2021. case 'big_' :
  2022. $picture['file'] = api_get_path(WEB_CODE_PATH).'img/unknown.jpg'; break;
  2023. case 'medium_' :
  2024. $picture['file'] = api_get_path(WEB_CODE_PATH).'img/unknown_50_50.jpg'; break;
  2025. case 'small_' :
  2026. $picture['file'] = api_get_path(WEB_CODE_PATH).'img/unknown.jpg'; break;
  2027. default:
  2028. $picture['file'] = api_get_path(WEB_CODE_PATH).'img/unknown.jpg'; break;
  2029. }
  2030. }
  2031. }
  2032. return $picture;
  2033. }
  2034. /**
  2035. * @author Isaac flores <isaac.flores@dokeos.com>
  2036. * @param string The email administrator
  2037. * @param integer The user id
  2038. * @param string The message title
  2039. * @param string The content message
  2040. */
  2041. public static function send_message_in_outbox($email_administrator, $user_id, $title, $content) {
  2042. $table_message = Database::get_main_table(TABLE_MESSAGE);
  2043. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  2044. $title = api_utf8_decode($title);
  2045. $content = api_utf8_decode($content);
  2046. $email_administrator = Database::escape_string($email_administrator);
  2047. //message in inbox
  2048. $sql_message_outbox = 'SELECT user_id from '.$table_user.' WHERE email="'.$email_administrator.'" ';
  2049. //$num_row_query = Database::num_rows($sql_message_outbox);
  2050. $res_message_outbox = Database::query($sql_message_outbox);
  2051. $array_users_administrator = array();
  2052. while ($row_message_outbox = Database::fetch_array($res_message_outbox, 'ASSOC')) {
  2053. $array_users_administrator[] = $row_message_outbox['user_id'];
  2054. }
  2055. //allow to insert messages in outbox
  2056. for ($i = 0; $i < count($array_users_administrator); $i++) {
  2057. $sql_insert_outbox = "INSERT INTO $table_message(user_sender_id, user_receiver_id, msg_status, send_date, title, content ) ".
  2058. " VALUES (".
  2059. "'".(int)$user_id."', '".(int)($array_users_administrator[$i])."', '4', '".date('Y-m-d H:i:s')."','".Database::escape_string($title)."','".Database::escape_string($content)."'".
  2060. ")";
  2061. $rs = Database::query($sql_insert_outbox);
  2062. }
  2063. }
  2064. /*
  2065. *
  2066. * USER TAGS
  2067. *
  2068. * Intructions to create a new user tag by Julio Montoya <gugli100@gmail.com>
  2069. *
  2070. * 1. Create a new extra field in main/admin/user_fields.php with the "TAG" field type make it available and visible. Called it "books" for example.
  2071. * 2. Go to profile main/auth/profile.php There you will see a special input (facebook style) that will show suggestions of tags.
  2072. * 3. All the tags are registered in the user_tag table and the relationship between user and tags is in the user_rel_tag table
  2073. * 4. Tags are independent this means that tags can't be shared between tags + book + hobbies.
  2074. * 5. Test and enjoy.
  2075. *
  2076. */
  2077. /**
  2078. * Gets the tags of a specific field_id
  2079. *
  2080. * @param int field_id
  2081. * @param string how we are going to result value in array or in a string (json)
  2082. * @return mixed
  2083. * @since Nov 2009
  2084. * @version 1.8.6.2
  2085. */
  2086. public static function get_tags($tag, $field_id, $return_format='json',$limit=10) {
  2087. // database table definition
  2088. $table_user_tag = Database::get_main_table(TABLE_MAIN_TAG);
  2089. $table_user_tag_values = Database::get_main_table(TABLE_MAIN_USER_REL_TAG);
  2090. $field_id = intval($field_id);
  2091. $limit = intval($limit);
  2092. $tag = trim(Database::escape_string($tag));
  2093. // all the information of the field
  2094. $sql = "SELECT id, tag from $table_user_tag
  2095. WHERE field_id = $field_id AND tag LIKE '$tag%' ORDER BY tag LIMIT $limit";
  2096. $result = Database::query($sql);
  2097. $return = array();
  2098. if (Database::num_rows($result)>0) {
  2099. while ($row = Database::fetch_array($result,'ASSOC')) {
  2100. $return[] = array('caption'=>$row['tag'], 'value'=>$row['tag']);
  2101. }
  2102. }
  2103. if ($return_format=='json') {
  2104. $return = json_encode($return);
  2105. }
  2106. return $return;
  2107. }
  2108. public static function get_top_tags($field_id, $limit=100) {
  2109. // database table definition
  2110. $table_user_tag = Database::get_main_table(TABLE_MAIN_TAG);
  2111. $table_user_tag_values = Database::get_main_table(TABLE_MAIN_USER_REL_TAG);
  2112. $field_id = intval($field_id);
  2113. $limit = intval($limit);
  2114. // all the information of the field
  2115. $sql = "SELECT count(*) count, tag FROM $table_user_tag_values uv INNER JOIN $table_user_tag ut ON(ut.id = uv.tag_id)
  2116. WHERE field_id = $field_id GROUP BY tag_id ORDER BY count DESC LIMIT $limit";
  2117. $result = Database::query($sql);
  2118. $return = array();
  2119. if (Database::num_rows($result)>0) {
  2120. while ($row = Database::fetch_array($result,'ASSOC')) {
  2121. $return[] = $row;
  2122. }
  2123. }
  2124. return $return;
  2125. }
  2126. /**
  2127. * Get user's tags
  2128. * @param int field_id
  2129. * @param int user_id
  2130. * @return array
  2131. */
  2132. public static function get_user_tags($user_id,$field_id) {
  2133. // database table definition
  2134. $table_user_tag = Database::get_main_table(TABLE_MAIN_TAG);
  2135. $table_user_tag_values = Database::get_main_table(TABLE_MAIN_USER_REL_TAG);
  2136. $field_id = intval($field_id);
  2137. $user_id = intval($user_id);
  2138. // all the information of the field
  2139. $sql = "SELECT ut.id, tag,count FROM $table_user_tag ut INNER JOIN $table_user_tag_values uv ON (uv.tag_id=ut.ID)
  2140. WHERE field_id = $field_id AND user_id = $user_id ORDER BY tag";
  2141. $result = Database::query($sql);
  2142. $return = array();
  2143. if (Database::num_rows($result)> 0) {
  2144. while ($row = Database::fetch_array($result,'ASSOC')) {
  2145. $return[$row['id']] = array('tag'=>$row['tag'],'count'=>$row['count']);
  2146. }
  2147. }
  2148. return $return;
  2149. }
  2150. /**
  2151. * Get user's tags
  2152. * @param int user_id
  2153. * @param int field_id
  2154. * @param bool show links or not
  2155. * @return array
  2156. */
  2157. public static function get_user_tags_to_string($user_id,$field_id,$show_links=true) {
  2158. // database table definition
  2159. $table_user_tag = Database::get_main_table(TABLE_MAIN_TAG);
  2160. $table_user_tag_values = Database::get_main_table(TABLE_MAIN_USER_REL_TAG);
  2161. $field_id = intval($field_id);
  2162. $user_id = intval($user_id);
  2163. // all the information of the field
  2164. $sql = "SELECT ut.id, tag,count FROM $table_user_tag ut INNER JOIN $table_user_tag_values uv ON (uv.tag_id=ut.ID)
  2165. WHERE field_id = $field_id AND user_id = $user_id ORDER BY tag";
  2166. $result = Database::query($sql);
  2167. $return = array();
  2168. if (Database::num_rows($result)> 0) {
  2169. while ($row = Database::fetch_array($result,'ASSOC')) {
  2170. $return[$row['id']] = array('tag'=>$row['tag'],'count'=>$row['count']);
  2171. }
  2172. }
  2173. $user_tags = $return;
  2174. $tag_tmp = array();
  2175. foreach ($user_tags as $tag) {
  2176. if ($show_links) {
  2177. $tag_tmp[] = '<a href="'.api_get_path(WEB_PATH).'main/search/?q='.$tag['tag'].'">'.$tag['tag'].'</a>';
  2178. } else {
  2179. $tag_tmp[] = $tag['tag'];
  2180. }
  2181. }
  2182. if (is_array($user_tags) && count($user_tags)>0) {
  2183. $return = implode(', ',$tag_tmp);
  2184. } else {
  2185. return '';
  2186. }
  2187. return $return;
  2188. }
  2189. /**
  2190. * Get the tag id
  2191. * @param int tag
  2192. * @param int field_id
  2193. * @return int returns 0 if fails otherwise the tag id
  2194. */
  2195. public function get_tag_id($tag, $field_id) {
  2196. $table_user_tag = Database::get_main_table(TABLE_MAIN_TAG);
  2197. $tag = Database::escape_string($tag);
  2198. $field_id = intval($field_id);
  2199. //with COLLATE latin1_bin to select query in a case sensitive mode
  2200. $sql = "SELECT id FROM $table_user_tag WHERE tag COLLATE latin1_bin LIKE '$tag' AND field_id = $field_id";
  2201. $result = Database::query($sql);
  2202. if (Database::num_rows($result)>0) {
  2203. $row = Database::fetch_array($result,'ASSOC');
  2204. return $row['id'];
  2205. } else {
  2206. return 0;
  2207. }
  2208. }
  2209. /**
  2210. * Get the tag id
  2211. * @param int tag
  2212. * @param int field_id
  2213. * @return int 0 if fails otherwise the tag id
  2214. */
  2215. public function get_tag_id_from_id($tag_id, $field_id) {
  2216. $table_user_tag = Database::get_main_table(TABLE_MAIN_TAG);
  2217. $tag_id = intval($tag_id);
  2218. $field_id = intval($field_id);
  2219. $sql = "SELECT id FROM $table_user_tag WHERE id = '$tag_id' AND field_id = $field_id";
  2220. $result = Database::query($sql);
  2221. if (Database::num_rows($result)>0) {
  2222. $row = Database::fetch_array($result,'ASSOC');
  2223. return $row['id'];
  2224. } else {
  2225. return false;
  2226. }
  2227. }
  2228. /**
  2229. * Adds a user-tag value
  2230. * @param mixed tag
  2231. * @param int The user id
  2232. * @param int field id of the tag
  2233. * @return bool
  2234. */
  2235. public function add_tag($tag, $user_id, $field_id) {
  2236. // database table definition
  2237. $table_user_tag = Database::get_main_table(TABLE_MAIN_TAG);
  2238. $table_user_tag_values = Database::get_main_table(TABLE_MAIN_USER_REL_TAG);
  2239. $tag = Database::escape_string($tag);
  2240. $tag = trim($tag);
  2241. $user_id = intval($user_id);
  2242. $field_id = intval($field_id);
  2243. //&& (substr($tag,strlen($tag)-1) == '@')
  2244. /*$sent_by_user = false;
  2245. if ( substr($tag,0,1) == '@') {
  2246. //is a value sent by the list
  2247. $sent_by_user = true;
  2248. $tag = substr($tag,1,strlen($tag)-2);
  2249. }
  2250. */
  2251. $tag_id = UserManager::get_tag_id($tag,$field_id);
  2252. /* IMPORTANT
  2253. * @todo we don't create tags with numbers
  2254. *
  2255. */
  2256. if (is_numeric($tag)) {
  2257. //the form is sending an id this means that the user select it from the list so it MUST exists
  2258. /*$new_tag_id = UserManager::get_tag_id_from_id($tag,$field_id);
  2259. if ($new_tag_id !== false) {
  2260. $sql = "UPDATE $table_user_tag SET count = count + 1 WHERE id = $new_tag_id";
  2261. $result = Database::query($sql);
  2262. $last_insert_id = $new_tag_id;
  2263. } else {
  2264. $sql = "INSERT INTO $table_user_tag (tag, field_id,count) VALUES ('$tag','$field_id', count + 1)";
  2265. $result = Database::query($sql);
  2266. $last_insert_id = Database::get_last_insert_id();
  2267. }*/
  2268. } else {
  2269. //this is a new tag
  2270. if ($tag_id == 0) {
  2271. //the tag doesn't exist
  2272. $sql = "INSERT INTO $table_user_tag (tag, field_id,count) VALUES ('$tag','$field_id', count + 1)";
  2273. $result = Database::query($sql);
  2274. $last_insert_id = Database::get_last_insert_id();
  2275. } else {
  2276. //the tag exists we update it
  2277. $sql = "UPDATE $table_user_tag SET count = count + 1 WHERE id = $tag_id";
  2278. $result = Database::query($sql);
  2279. $last_insert_id = $tag_id;
  2280. }
  2281. }
  2282. if (!empty($last_insert_id) && ($last_insert_id!=0)) {
  2283. //we insert the relationship user-tag
  2284. $sql_select ="SELECT tag_id FROM $table_user_tag_values WHERE user_id = $user_id AND tag_id = $last_insert_id ";
  2285. $result = Database::query($sql_select);
  2286. //if the relationship does not exist we create it
  2287. if (Database::num_rows($result)==0) {
  2288. $sql = "INSERT INTO $table_user_tag_values SET user_id = $user_id, tag_id = $last_insert_id";
  2289. $result = Database::query($sql);
  2290. }
  2291. }
  2292. }
  2293. /**
  2294. * Deletes an user tag
  2295. * @param int user id
  2296. * @param int field id
  2297. *
  2298. */
  2299. public function delete_user_tags($user_id, $field_id) {
  2300. // database table definition
  2301. $table_user_tag = Database::get_main_table(TABLE_MAIN_TAG);
  2302. $table_user_tag_values = Database::get_main_table(TABLE_MAIN_USER_REL_TAG);
  2303. $tags = UserManager::get_user_tags($user_id, $field_id);
  2304. //echo '<pre>';var_dump($tags);
  2305. if(is_array($tags) && count($tags)>0) {
  2306. foreach ($tags as $key=>$tag) {
  2307. if ($tag['count']>'0') {
  2308. $sql = "UPDATE $table_user_tag SET count = count - 1 WHERE id = $key ";
  2309. $result = Database::query($sql);
  2310. }
  2311. $sql = "DELETE FROM $table_user_tag_values WHERE user_id = $user_id AND tag_id = $key";
  2312. $result = Database::query($sql);
  2313. }
  2314. }
  2315. }
  2316. /**
  2317. * Process the tag list comes from the UserManager::update_extra_field_value() function
  2318. * @param array the tag list that will be added
  2319. * @param int user id
  2320. * @param int field id
  2321. * @return bool
  2322. */
  2323. public function process_tags($tags, $user_id, $field_id) {
  2324. //We loop the tags and add it to the DB
  2325. if (is_array($tags)) {
  2326. foreach($tags as $tag) {
  2327. UserManager::add_tag($tag, $user_id, $field_id);
  2328. }
  2329. } else {
  2330. UserManager::add_tag($tags,$user_id, $field_id);
  2331. }
  2332. return true;
  2333. }
  2334. /**
  2335. * Gives a list of emails from all administrators
  2336. * @author cvargas carlos.vargas@dokeos.com
  2337. * @return array
  2338. */
  2339. public function get_emails_from_all_administrators() {
  2340. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  2341. $table_admin = Database::get_main_table(TABLE_MAIN_ADMIN);
  2342. $sql = "SELECT email from $table_user as u, $table_admin as a WHERE u.user_id=a.user_id";
  2343. $result = Database::query($sql);
  2344. $return = array();
  2345. if (Database::num_rows($result)> 0) {
  2346. while ($row = Database::fetch_array($result,'ASSOC')) {
  2347. $return[$row['email']] = $row;
  2348. }
  2349. }
  2350. return $return;
  2351. }
  2352. /**
  2353. * Searchs an user (tags, firstname, lastname and email )
  2354. * @param string the tag
  2355. * @param int field id of the tag
  2356. * @param int where to start in the query
  2357. * @param int number of items
  2358. * @return array
  2359. */
  2360. public static function get_all_user_tags($tag, $field_id = 0, $from = 0, $number_of_items = 10) {
  2361. // database table definition
  2362. $user_table = Database::get_main_table(TABLE_MAIN_USER);
  2363. $table_user_tag = Database::get_main_table(TABLE_MAIN_TAG);
  2364. $table_user_tag_values = Database::get_main_table(TABLE_MAIN_USER_REL_TAG);
  2365. $tag = Database::escape_string($tag);
  2366. $field_id = intval($field_id);
  2367. $from = intval($from);
  2368. $number_of_items = intval($number_of_items);
  2369. $where_field = "";
  2370. if ($field_id != 0) {
  2371. $where_field = " field_id = $field_id AND ";
  2372. }
  2373. // all the information of the field
  2374. $sql = "SELECT u.user_id,u.username,firstname, lastname, email, tag, picture_uri FROM $table_user_tag ut INNER JOIN $table_user_tag_values uv ON (uv.tag_id=ut.id)
  2375. INNER JOIN $user_table u ON(uv.user_id =u.user_id)
  2376. WHERE $where_field tag LIKE '$tag%' ORDER BY tag";
  2377. $sql .= " LIMIT $from,$number_of_items";
  2378. $result = Database::query($sql);
  2379. $return = array();
  2380. if (Database::num_rows($result)> 0) {
  2381. while ($row = Database::fetch_array($result,'ASSOC')) {
  2382. $return[$row['user_id']] = $row;
  2383. }
  2384. }
  2385. $keyword = $tag;
  2386. $sql = "SELECT u.user_id, u.username, firstname, lastname, email, picture_uri FROM $user_table u";
  2387. global $_configuration;
  2388. if ($_configuration['multiple_access_urls']==true && api_get_current_access_url_id()!=-1) {
  2389. $access_url_rel_user_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  2390. $sql.= " INNER JOIN $access_url_rel_user_table url_rel_user ON (u.user_id=url_rel_user.user_id)";
  2391. }
  2392. if (isset ($keyword)) {
  2393. $keyword = Database::escape_string($keyword);
  2394. //OR u.official_code LIKE '%".$keyword."%'
  2395. // OR u.email LIKE '%".$keyword."%'
  2396. $sql .= " WHERE (u.firstname LIKE '%".$keyword."%' OR u.lastname LIKE '%".$keyword."%' OR u.username LIKE '%".$keyword."%' OR concat(u.firstname,' ',u.lastname) LIKE '%".$keyword."%' OR concat(u.lastname,' ',u.firstname) LIKE '%".$keyword."%' )";
  2397. }
  2398. $keyword_active = true;
  2399. //only active users
  2400. if ($keyword_active) {
  2401. $sql .= " AND u.active='1'";
  2402. }
  2403. //avoid anonymous
  2404. $sql .= " AND u.status <> 6 ";
  2405. // adding the filter to see the user's only of the current access_url
  2406. if ($_configuration['multiple_access_urls']==true && api_get_current_access_url_id()!=-1) {
  2407. $sql.= " AND url_rel_user.access_url_id=".api_get_current_access_url_id();
  2408. }
  2409. $direction = 'ASC';
  2410. if (!in_array($direction, array('ASC','DESC'))) {
  2411. $direction = 'ASC';
  2412. }
  2413. $column = intval($column);
  2414. $from = intval($from);
  2415. $number_of_items = intval($number_of_items);
  2416. //$sql .= " ORDER BY col$column $direction ";
  2417. $sql .= " LIMIT $from,$number_of_items";
  2418. $res = Database::query($sql);
  2419. if (Database::num_rows($res)> 0) {
  2420. while ($row = Database::fetch_array($res,'ASSOC')) {
  2421. if (!in_array($row['user_id'], $return)) {
  2422. $return[$row['user_id']] = $row;
  2423. }
  2424. }
  2425. }
  2426. return $return;
  2427. }
  2428. /**
  2429. * Show the search form
  2430. * @param string the value of the search box
  2431. *
  2432. */
  2433. public static function get_search_form($query) {
  2434. echo'<form method="GET" action="'.api_get_path(WEB_PATH).'main/social/search.php">
  2435. <table cellspacing="0" cellpadding="0">
  2436. <tr>
  2437. <td>
  2438. <div id="search_label">
  2439. <b>'.get_lang('Search').'</b > ('.get_lang('UsersGroups').')
  2440. <div>
  2441. <input type="text" size="30" value="'.Security::remove_XSS($query).'" name="q"/><br />
  2442. <button class="search" type="submit" value="search">'.get_lang('Search').'</button>
  2443. </div>
  2444. </td>
  2445. </tr>
  2446. </table></form>';
  2447. }
  2448. //deprecated
  2449. public static function get_public_users($keyword, $from = 0, $number_of_items= 20, $column=2, $direction='ASC') {
  2450. $admin_table = Database :: get_main_table(TABLE_MAIN_ADMIN);
  2451. $sql = "SELECT
  2452. u.user_id AS col0,
  2453. u.official_code AS col1,
  2454. ".(api_is_western_name_order()
  2455. ? "u.firstname AS col2,
  2456. u.lastname AS col3,"
  2457. : "u.lastname AS col2,
  2458. u.firstname AS col3,")."
  2459. u.username AS col4,
  2460. u.email AS col5,
  2461. u.status AS col6,
  2462. u.active AS col7,
  2463. u.user_id AS col8 ".
  2464. ", u.expiration_date AS exp ".
  2465. " FROM $user_table u ";
  2466. // adding the filter to see the user's only of the current access_url
  2467. global $_configuration;
  2468. if ((api_is_platform_admin() || api_is_session_admin()) && $_configuration['multiple_access_urls']==true && api_get_current_access_url_id()!=-1) {
  2469. $access_url_rel_user_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  2470. $sql.= " INNER JOIN $access_url_rel_user_table url_rel_user ON (u.user_id=url_rel_user.user_id)";
  2471. }
  2472. if (isset ($keyword)) {
  2473. $keyword = Database::escape_string($keyword);
  2474. //OR u.official_code LIKE '%".$keyword."%'
  2475. $sql .= " WHERE (u.firstname LIKE '%".$keyword."%' OR u.lastname LIKE '%".$keyword."%' OR u.username LIKE '%".$keyword."%' OR u.email LIKE '%".$keyword."%' )";
  2476. }
  2477. $keyword_active = true;
  2478. //only active users
  2479. if ($keyword_active) {
  2480. $sql .= " AND u.active='1'";
  2481. }
  2482. // adding the filter to see the user's only of the current access_url
  2483. if ($_configuration['multiple_access_urls']==true && api_get_current_access_url_id()!=-1) {
  2484. $sql.= " AND url_rel_user.access_url_id=".api_get_current_access_url_id();
  2485. }
  2486. if (!in_array($direction, array('ASC','DESC'))) {
  2487. $direction = 'ASC';
  2488. }
  2489. $column = intval($column);
  2490. $from = intval($from);
  2491. $number_of_items = intval($number_of_items);
  2492. $sql .= " ORDER BY col$column $direction ";
  2493. $sql .= " LIMIT $from,$number_of_items";
  2494. $res = Database::query($sql);
  2495. $users = array ();
  2496. $t = time();
  2497. while ($user = Database::fetch_row($res)) {
  2498. if ($user[7] == 1 && $user[9] != '0000-00-00 00:00:00') {
  2499. // check expiration date
  2500. $expiration_time = convert_mysql_date($user[9]);
  2501. // if expiration date is passed, store a special value for active field
  2502. if ($expiration_time < $t) {
  2503. $user[7] = '-1';
  2504. }
  2505. }
  2506. // forget about the expiration date field
  2507. $users[] = array($user[0],$user[1],$user[2],$user[3],$user[4],$user[5],$user[6],$user[7],$user[8]);
  2508. }
  2509. return $users;
  2510. }
  2511. /**
  2512. * Shows the user menu
  2513. */
  2514. public static function show_menu(){
  2515. echo '<div class="actions">';
  2516. echo '<a href="/main/auth/profile.php">'. Display::return_icon('profile.png').' '.get_lang('PersonalData').'</a>';
  2517. echo '<a href="/main/messages/inbox.php">'. Display::return_icon('inbox.png').' '. get_lang('Inbox').'</a>';
  2518. echo '<a href="/main/messages/outbox.php">'.Display::return_icon('outbox.png').' '. get_lang('Outbox').'</a>';
  2519. echo '<span style="float:right; padding-top:7px;">'.
  2520. '<a href="/main/auth/profile.php?show=1">'.Display::return_icon('edit.gif').' '.get_lang('Configuration').'</a>';
  2521. '</span>';
  2522. echo '</div>';
  2523. }
  2524. /**
  2525. * Gives a list of course auto-register (field special_course)
  2526. * @return array list of course
  2527. * @author Jhon Hinojosa <jhon.hinojosa@dokeos.com>
  2528. * @since Dokeos 1.8.6.2
  2529. */
  2530. public static function get_special_course_list() {
  2531. // Database Table Definitions
  2532. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  2533. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  2534. $tbl_course_field = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD);
  2535. $tbl_course_field_value = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  2536. $tbl_user_course_category = Database :: get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  2537. //we filter the courses from the URL
  2538. $join_access_url=$where_access_url='';
  2539. global $_configuration;
  2540. if ($_configuration['multiple_access_urls']==true) {
  2541. $access_url_id = api_get_current_access_url_id();
  2542. if($access_url_id!=-1) {
  2543. $tbl_url_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  2544. $join_access_url= "LEFT JOIN $tbl_url_course url_rel_course ON url_rel_course.course_code= course.code";
  2545. $where_access_url=" AND access_url_id = $access_url_id ";
  2546. }
  2547. }
  2548. // Filter special courses
  2549. $sql_special_course = "SELECT course_code FROM $tbl_course_field_value tcfv INNER JOIN $tbl_course_field tcf ON " .
  2550. " tcfv.field_id = tcf.id WHERE tcf.field_variable = 'special_course' AND tcfv.field_value = 1 ";
  2551. $special_course_result = Database::query($sql_special_course);
  2552. $code_special_courses = '';
  2553. if(Database::num_rows($special_course_result)>0) {
  2554. $special_course_list = array();
  2555. while ($result_row = Database::fetch_array($special_course_result)) {
  2556. $special_course_list[] = '"'.$result_row['course_code'].'"';
  2557. }
  2558. $code_special_courses = ' course.code IN ('.join($special_course_list, ',').') ';
  2559. }
  2560. // variable initialisation
  2561. $course_list_sql = '';
  2562. $course_list = array();
  2563. if(!empty($code_special_courses)){
  2564. $course_list_sql = "SELECT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, course.tutor_name t, course.course_language l, course_rel_user.status s, course_rel_user.sort sort, course_rel_user.user_course_cat user_course_cat
  2565. FROM ".$tbl_course_user." course_rel_user
  2566. LEFT JOIN ".$tbl_course." course
  2567. ON course.code = course_rel_user.course_code
  2568. LEFT JOIN ".$tbl_user_course_category." user_course_category
  2569. ON course_rel_user.user_course_cat = user_course_category.id
  2570. $join_access_url
  2571. WHERE $code_special_courses $where_access_url
  2572. GROUP BY course.code
  2573. ORDER BY user_course_category.sort,course.title,course_rel_user.sort ASC";
  2574. $course_list_sql_result = api_sql_query($course_list_sql);
  2575. while ($result_row = Database::fetch_array($course_list_sql_result)) {
  2576. $course_list[] = $result_row;
  2577. }
  2578. }
  2579. return $course_list;
  2580. }
  2581. /**
  2582. * Allow to register contact to social network
  2583. * @param int user friend id
  2584. * @param int user id
  2585. * @param int relation between users see constants definition
  2586. */
  2587. public static function relate_users ($friend_id,$my_user_id,$relation_type) {
  2588. $tbl_my_friend = Database :: get_main_table(TABLE_MAIN_USER_REL_USER);
  2589. $friend_id = intval($friend_id);
  2590. $my_user_id = intval($my_user_id);
  2591. $relation_type = intval($relation_type);
  2592. $sql = 'SELECT COUNT(*) as count FROM ' . $tbl_my_friend . ' WHERE friend_user_id=' .$friend_id.' AND user_id='.$my_user_id.' AND relation_type <> '.USER_RELATION_TYPE_RRHH.' ';
  2593. $result = Database::query($sql);
  2594. $row = Database :: fetch_array($result, 'ASSOC');
  2595. $current_date=date('Y-m-d H:i:s');
  2596. if ($row['count'] == 0) {
  2597. $sql_i = 'INSERT INTO ' . $tbl_my_friend . '(friend_user_id,user_id,relation_type,last_edit)values(' . $friend_id . ','.$my_user_id.','.$relation_type.',"'.$current_date.'");';
  2598. Database::query($sql_i);
  2599. return true;
  2600. } else {
  2601. $sql = 'SELECT COUNT(*) as count, relation_type FROM ' . $tbl_my_friend . ' WHERE friend_user_id=' . $friend_id . ' AND user_id='.$my_user_id.' AND relation_type <> '.USER_RELATION_TYPE_RRHH.' ';
  2602. $result = Database::query($sql);
  2603. $row = Database :: fetch_array($result, 'ASSOC');
  2604. if ($row['count'] == 1) {
  2605. //only for the case of a RRHH
  2606. if ($row['relation_type'] != $relation_type && $relation_type == USER_RELATION_TYPE_RRHH) {
  2607. $sql_i = 'INSERT INTO ' . $tbl_my_friend . '(friend_user_id,user_id,relation_type,last_edit)values(' . $friend_id . ','.$my_user_id.','.$relation_type.',"'.$current_date.'");';
  2608. } else {
  2609. $sql_i = 'UPDATE ' . $tbl_my_friend . ' SET relation_type='.$relation_type.' WHERE friend_user_id=' . $friend_id.' AND user_id='.$my_user_id;
  2610. }
  2611. Database::query($sql_i);
  2612. return true;
  2613. } else {
  2614. return false;
  2615. }
  2616. }
  2617. }
  2618. /**
  2619. * Deletes a contact
  2620. * @param int user friend id
  2621. * @param bool true will delete ALL friends relationship from $friend_id
  2622. * @author isaac flores paz <isaac.flores@dokeos.com>
  2623. * @author Julio Montoya <gugli100@gmail.com> Cleaning code
  2624. */
  2625. public static function remove_user_rel_user ($friend_id, $real_removed = false, $with_status_condition = '') {
  2626. $tbl_my_friend = Database :: get_main_table(TABLE_MAIN_USER_REL_USER);
  2627. $tbl_my_message = Database :: get_main_table(TABLE_MAIN_MESSAGE);
  2628. $friend_id = intval($friend_id);
  2629. if ($real_removed == true) {
  2630. //Delete user friend
  2631. /*
  2632. $sql_delete_relationship1 = 'UPDATE ' . $tbl_my_friend .' SET relation_type='.USER_RELATION_TYPE_DELETED.' WHERE friend_user_id='.$friend_id;
  2633. $sql_delete_relationship2 = 'UPDATE ' . $tbl_my_friend . ' SET relation_type='.USER_RELATION_TYPE_DELETED.' WHERE user_id=' . $friend_id;
  2634. Database::query($sql_delete_relationship1);
  2635. Database::query($sql_delete_relationship2);*/
  2636. $extra_condition = '';
  2637. if ($with_status_condition != '') {
  2638. $extra_condition = ' AND relation_type = '.intval($with_status_condition);
  2639. }
  2640. $sql_delete_relationship1 = 'DELETE FROM ' . $tbl_my_friend .' WHERE relation_type <> '.USER_RELATION_TYPE_RRHH.' AND friend_user_id='.$friend_id.' '.$extra_condition;
  2641. $sql_delete_relationship2 = 'DELETE FROM ' . $tbl_my_friend . ' WHERE relation_type <> '.USER_RELATION_TYPE_RRHH.' AND user_id=' . $friend_id.' '.$extra_condition;
  2642. Database::query($sql_delete_relationship1);
  2643. Database::query($sql_delete_relationship2);
  2644. } else {
  2645. $user_id = api_get_user_id();
  2646. $sql = 'SELECT COUNT(*) as count FROM ' . $tbl_my_friend . ' WHERE user_id=' . $user_id . ' AND relation_type NOT IN('.USER_RELATION_TYPE_DELETED.', '.USER_RELATION_TYPE_RRHH.') AND friend_user_id='.$friend_id;
  2647. $result = Database::query($sql);
  2648. $row = Database :: fetch_array($result, 'ASSOC');
  2649. if ($row['count'] == 1) {
  2650. //Delete user rel user
  2651. $sql_i = 'UPDATE ' . $tbl_my_friend .' SET relation_type='.USER_RELATION_TYPE_DELETED.' WHERE user_id='.$user_id.' AND friend_user_id='.$friend_id;
  2652. $sql_j = 'UPDATE ' . $tbl_my_message.' SET msg_status='.MESSAGE_STATUS_INVITATION_DENIED.' WHERE user_receiver_id=' . $user_id.' AND user_sender_id='.$friend_id.' AND update_date="0000-00-00 00:00:00" ';
  2653. //Delete user
  2654. $sql_ij = 'UPDATE ' . $tbl_my_friend . ' SET relation_type='.USER_RELATION_TYPE_DELETED.' WHERE user_id=' . $friend_id.' AND friend_user_id='.$user_id;
  2655. $sql_ji = 'UPDATE ' . $tbl_my_message . ' SET msg_status='.MESSAGE_STATUS_INVITATION_DENIED.' WHERE user_receiver_id=' . $friend_id.' AND user_sender_id='.$user_id.' AND update_date="0000-00-00 00:00:00" ';
  2656. Database::query($sql_i);
  2657. Database::query($sql_j);
  2658. Database::query($sql_ij);
  2659. Database::query($sql_ji);
  2660. }
  2661. }
  2662. }
  2663. /**
  2664. * get users folloewd by human resource manager
  2665. * @param int hr_dept id
  2666. * @param int user status (optional)
  2667. * @return array users
  2668. */
  2669. public static function get_users_followed_by_drh($hr_dept_id, $user_status = 0) {
  2670. // Database Table Definitions
  2671. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  2672. $tbl_user_rel_user = Database::get_main_table(TABLE_MAIN_USER_REL_USER);
  2673. $hr_dept_id = intval($hr_dept_id);
  2674. $assigned_users_to_hrm = array();
  2675. $condition_status = '';
  2676. if (!empty($user_status)) {
  2677. $status = intval($status);
  2678. $condition_status = ' AND u.status = '.$user_status;
  2679. }
  2680. $sql = "SELECT u.user_id, u.username, u.lastname, u.firstname, u.email FROM $tbl_user u
  2681. INNER JOIN $tbl_user_rel_user uru ON uru.user_id = u.user_id AND friend_user_id = '$hr_dept_id' AND relation_type = '".USER_RELATION_TYPE_RRHH."' $condition_status";
  2682. $rs_assigned_users = Database::query($sql, __FILE__, __LINE__);
  2683. if (Database::num_rows($rs_assigned_users) > 0) {
  2684. while ($row_assigned_users = Database::fetch_array($rs_assigned_users)) {
  2685. $assigned_users_to_hrm[$row_assigned_users['user_id']] = $row_assigned_users;
  2686. }
  2687. }
  2688. return $assigned_users_to_hrm;
  2689. }
  2690. /**
  2691. * Subscribes users to human resource manager (Dashboard feature)
  2692. * @param int hr dept id
  2693. * @param array Users id
  2694. * @param int affected rows
  2695. **/
  2696. public static function suscribe_users_to_hr_manager($hr_dept_id, $users_id) {
  2697. // Database Table Definitions
  2698. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  2699. $tbl_user_rel_user = Database::get_main_table(TABLE_MAIN_USER_REL_USER);
  2700. $hr_dept_id = intval($hr_dept_id);
  2701. $affected_rows = 0;
  2702. //Deleting assigned users to hrm_id
  2703. $sql = "SELECT user_id FROM $tbl_user_rel_user WHERE friend_user_id = $hr_dept_id AND relation_type = '".USER_RELATION_TYPE_RRHH."' ";
  2704. $result = Database::query($sql,__FILE__,__LINE__);
  2705. if (Database::num_rows($result) > 0) {
  2706. $sql = "DELETE FROM $tbl_user_rel_user WHERE friend_user_id = $hr_dept_id AND relation_type = '".USER_RELATION_TYPE_RRHH."' ";
  2707. Database::query($sql,__FILE__,__LINE__);
  2708. }
  2709. // inserting new user list
  2710. if (is_array($users_id)) {
  2711. foreach ($users_id as $user_id) {
  2712. $user_id = intval($user_id);
  2713. $insert_sql = "INSERT IGNORE INTO $tbl_user_rel_user(user_id, friend_user_id, relation_type) VALUES('$user_id', $hr_dept_id, '".USER_RELATION_TYPE_RRHH."')";
  2714. Database::query($insert_sql,__FILE__,__LINE__);
  2715. $affected_rows = Database::affected_rows();
  2716. }
  2717. }
  2718. return $affected_rows;
  2719. }
  2720. /**
  2721. * This function check if an user is followed by human resources manager
  2722. * @param int User id
  2723. * @param int Human resources manager
  2724. * @return bool
  2725. */
  2726. public static function is_user_followed_by_drh($user_id, $hr_dept_id) {
  2727. // Database table and variables Definitions
  2728. $tbl_user_rel_user = Database::get_main_table(TABLE_MAIN_USER_REL_USER);
  2729. $user_id = intval($user_id);
  2730. $hr_dept_id = intval($hr_dept_id);
  2731. $result = false;
  2732. $sql = "SELECT user_id FROM $tbl_user_rel_user WHERE user_id='$user_id' AND friend_user_id='$hr_dept_id' AND relation_type=".USER_RELATION_TYPE_RRHH." ";
  2733. $rs = Database::query($sql);
  2734. if (Database::num_rows($rs) > 0) {
  2735. $result = true;
  2736. }
  2737. return $result;
  2738. }
  2739. /**
  2740. * get user id of teacher or session administrator
  2741. * @param string The course id
  2742. * @return int The user id
  2743. */
  2744. function get_user_id_of_course_admin_or_session_admin ($course_id) {
  2745. $session=api_get_session_id();
  2746. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  2747. $table_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  2748. $table_session_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  2749. if ($session==0 || is_null($session)) {
  2750. $sql='SELECT u.user_id FROM '.$table_user.' u
  2751. INNER JOIN '.$table_course_user.' ru ON ru.user_id=u.user_id
  2752. WHERE ru.status=1 AND ru.course_code="'.Database::escape_string($course_id).'" ';
  2753. $rs=Database::query($sql,__FILE__,__LINE__);
  2754. $num_rows=Database::num_rows($rs);
  2755. if ($num_rows==1) {
  2756. $row=Database::fetch_array($rs);
  2757. return $row['user_id'];
  2758. } else {
  2759. $my_num_rows=$num_rows;
  2760. $my_user_id=Database::result($rs,$my_num_rows-1,'user_id');
  2761. return $my_user_id;
  2762. }
  2763. } elseif ($session>0) {
  2764. $sql='SELECT u.user_id FROM '.$table_user.' u
  2765. INNER JOIN '.$table_session_user.' sru
  2766. ON sru.id_coach=u.user_id WHERE sru.course_code="'.Database::escape_string($course_id).'" ';
  2767. $rs=Database::query($sql,__FILE__,__LINE__);
  2768. $row=Database::fetch_array($rs);
  2769. return $row['user_id'];
  2770. }
  2771. }
  2772. /**
  2773. * Determines if a user is certified
  2774. * @param int The category id of gradebook
  2775. * @param int The user id
  2776. * @return boolean
  2777. */
  2778. function is_user_certified($cat_id,$user_id) {
  2779. $table_certificate = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CERTIFICATE);
  2780. $sql='SELECT path_certificate FROM '.$table_certificate.' WHERE cat_id="'.Database::escape_string($cat_id).'" AND user_id="'.Database::escape_string($user_id).'" ';
  2781. $rs=Database::query($sql,__FILE__,__LINE__);
  2782. $row=Database::fetch_array($rs);
  2783. if ($row['path_certificate']=='' || is_null($row['path_certificate'])) {
  2784. return false;
  2785. } else {
  2786. return true;
  2787. }
  2788. }
  2789. /**
  2790. * Gets the info about a gradebook certificate for a user by course
  2791. * @param string The course code
  2792. * @param int The user id
  2793. * @return array if there is not information return false
  2794. */
  2795. function get_info_gradebook_certificate($course_code,$user_id) {
  2796. $tbl_grade_certificate = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CERTIFICATE);
  2797. $tbl_grade_category = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY);
  2798. $sql='SELECT * FROM '.$tbl_grade_certificate.' WHERE cat_id= (SELECT id FROM '.$tbl_grade_category.' WHERE course_code = "'.Database::escape_string($course_code).'" ) AND user_id="'.Database::escape_string($user_id).'" ';
  2799. $rs = Database::query($sql,__FILE__,__LINE__);
  2800. $row= Database::fetch_array($rs);
  2801. if (Database::num_rows($rs) > 0)
  2802. return $row;
  2803. else
  2804. return false;
  2805. }
  2806. /**
  2807. * Gets the user path of user certificated
  2808. * @param int The user id
  2809. * @return array containing path_certificate and cat_id
  2810. */
  2811. function get_user_path_certificate($user_id) {
  2812. $my_certificate = array();
  2813. $table_certificate = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CERTIFICATE);
  2814. $table_gradebook_category = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY);
  2815. $session_id = api_get_session_id();
  2816. $user_id = intval($user_id);
  2817. if ($session_id==0 || is_null($session_id)) {
  2818. $sql_session='AND (session_id='.Database::escape_string($session_id).' OR isnull(session_id)) ';
  2819. } elseif ($session_id>0) {
  2820. $sql_session='AND session_id='.Database::escape_string($session_id);
  2821. } else {
  2822. $sql_session='';
  2823. }
  2824. $sql= "SELECT tc.path_certificate,tc.cat_id,tgc.course_code,tgc.name FROM $table_certificate tc, $table_gradebook_category tgc
  2825. WHERE tgc.id = tc.cat_id AND tc.user_id='$user_id' ORDER BY tc.date_certificate DESC limit 5";
  2826. $rs=Database::query($sql,__FILE__,__LINE__);
  2827. while ($row=Database::fetch_array($rs)) {
  2828. $my_certificate[]=$row;
  2829. }
  2830. return $my_certificate;
  2831. }
  2832. }