course.lib.php 244 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429243024312432243324342435243624372438243924402441244224432444244524462447244824492450245124522453245424552456245724582459246024612462246324642465246624672468246924702471247224732474247524762477247824792480248124822483248424852486248724882489249024912492249324942495249624972498249925002501250225032504250525062507250825092510251125122513251425152516251725182519252025212522252325242525252625272528252925302531253225332534253525362537253825392540254125422543254425452546254725482549255025512552255325542555255625572558255925602561256225632564256525662567256825692570257125722573257425752576257725782579258025812582258325842585258625872588258925902591259225932594259525962597259825992600260126022603260426052606260726082609261026112612261326142615261626172618261926202621262226232624262526262627262826292630263126322633263426352636263726382639264026412642264326442645264626472648264926502651265226532654265526562657265826592660266126622663266426652666266726682669267026712672267326742675267626772678267926802681268226832684268526862687268826892690269126922693269426952696269726982699270027012702270327042705270627072708270927102711271227132714271527162717271827192720272127222723272427252726272727282729273027312732273327342735273627372738273927402741274227432744274527462747274827492750275127522753275427552756275727582759276027612762276327642765276627672768276927702771277227732774277527762777277827792780278127822783278427852786278727882789279027912792279327942795279627972798279928002801280228032804280528062807280828092810281128122813281428152816281728182819282028212822282328242825282628272828282928302831283228332834283528362837283828392840284128422843284428452846284728482849285028512852285328542855285628572858285928602861286228632864286528662867286828692870287128722873287428752876287728782879288028812882288328842885288628872888288928902891289228932894289528962897289828992900290129022903290429052906290729082909291029112912291329142915291629172918291929202921292229232924292529262927292829292930293129322933293429352936293729382939294029412942294329442945294629472948294929502951295229532954295529562957295829592960296129622963296429652966296729682969297029712972297329742975297629772978297929802981298229832984298529862987298829892990299129922993299429952996299729982999300030013002300330043005300630073008300930103011301230133014301530163017301830193020302130223023302430253026302730283029303030313032303330343035303630373038303930403041304230433044304530463047304830493050305130523053305430553056305730583059306030613062306330643065306630673068306930703071307230733074307530763077307830793080308130823083308430853086308730883089309030913092309330943095309630973098309931003101310231033104310531063107310831093110311131123113311431153116311731183119312031213122312331243125312631273128312931303131313231333134313531363137313831393140314131423143314431453146314731483149315031513152315331543155315631573158315931603161316231633164316531663167316831693170317131723173317431753176317731783179318031813182318331843185318631873188318931903191319231933194319531963197319831993200320132023203320432053206320732083209321032113212321332143215321632173218321932203221322232233224322532263227322832293230323132323233323432353236323732383239324032413242324332443245324632473248324932503251325232533254325532563257325832593260326132623263326432653266326732683269327032713272327332743275327632773278327932803281328232833284328532863287328832893290329132923293329432953296329732983299330033013302330333043305330633073308330933103311331233133314331533163317331833193320332133223323332433253326332733283329333033313332333333343335333633373338333933403341334233433344334533463347334833493350335133523353335433553356335733583359336033613362336333643365336633673368336933703371337233733374337533763377337833793380338133823383338433853386338733883389339033913392339333943395339633973398339934003401340234033404340534063407340834093410341134123413341434153416341734183419342034213422342334243425342634273428342934303431343234333434343534363437343834393440344134423443344434453446344734483449345034513452345334543455345634573458345934603461346234633464346534663467346834693470347134723473347434753476347734783479348034813482348334843485348634873488348934903491349234933494349534963497349834993500350135023503350435053506350735083509351035113512351335143515351635173518351935203521352235233524352535263527352835293530353135323533353435353536353735383539354035413542354335443545354635473548354935503551355235533554355535563557355835593560356135623563356435653566356735683569357035713572357335743575357635773578357935803581358235833584358535863587358835893590359135923593359435953596359735983599360036013602360336043605360636073608360936103611361236133614361536163617361836193620362136223623362436253626362736283629363036313632363336343635363636373638363936403641364236433644364536463647364836493650365136523653365436553656365736583659366036613662366336643665366636673668366936703671367236733674367536763677367836793680368136823683368436853686368736883689369036913692369336943695369636973698369937003701370237033704370537063707370837093710371137123713371437153716371737183719372037213722372337243725372637273728372937303731373237333734373537363737373837393740374137423743374437453746374737483749375037513752375337543755375637573758375937603761376237633764376537663767376837693770377137723773377437753776377737783779378037813782378337843785378637873788378937903791379237933794379537963797379837993800380138023803380438053806380738083809381038113812381338143815381638173818381938203821382238233824382538263827382838293830383138323833383438353836383738383839384038413842384338443845384638473848384938503851385238533854385538563857385838593860386138623863386438653866386738683869387038713872387338743875387638773878387938803881388238833884388538863887388838893890389138923893389438953896389738983899390039013902390339043905390639073908390939103911391239133914391539163917391839193920392139223923392439253926392739283929393039313932393339343935393639373938393939403941394239433944394539463947394839493950395139523953395439553956395739583959396039613962396339643965396639673968396939703971397239733974397539763977397839793980398139823983398439853986398739883989399039913992399339943995399639973998399940004001400240034004400540064007400840094010401140124013401440154016401740184019402040214022402340244025402640274028402940304031403240334034403540364037403840394040404140424043404440454046404740484049405040514052405340544055405640574058405940604061406240634064406540664067406840694070407140724073407440754076407740784079408040814082408340844085408640874088408940904091409240934094409540964097409840994100410141024103410441054106410741084109411041114112411341144115411641174118411941204121412241234124412541264127412841294130413141324133413441354136413741384139414041414142414341444145414641474148414941504151415241534154415541564157415841594160416141624163416441654166416741684169417041714172417341744175417641774178417941804181418241834184418541864187418841894190419141924193419441954196419741984199420042014202420342044205420642074208420942104211421242134214421542164217421842194220422142224223422442254226422742284229423042314232423342344235423642374238423942404241424242434244424542464247424842494250425142524253425442554256425742584259426042614262426342644265426642674268426942704271427242734274427542764277427842794280428142824283428442854286428742884289429042914292429342944295429642974298429943004301430243034304430543064307430843094310431143124313431443154316431743184319432043214322432343244325432643274328432943304331433243334334433543364337433843394340434143424343434443454346434743484349435043514352435343544355435643574358435943604361436243634364436543664367436843694370437143724373437443754376437743784379438043814382438343844385438643874388438943904391439243934394439543964397439843994400440144024403440444054406440744084409441044114412441344144415441644174418441944204421442244234424442544264427442844294430443144324433443444354436443744384439444044414442444344444445444644474448444944504451445244534454445544564457445844594460446144624463446444654466446744684469447044714472447344744475447644774478447944804481448244834484448544864487448844894490449144924493449444954496449744984499450045014502450345044505450645074508450945104511451245134514451545164517451845194520452145224523452445254526452745284529453045314532453345344535453645374538453945404541454245434544454545464547454845494550455145524553455445554556455745584559456045614562456345644565456645674568456945704571457245734574457545764577457845794580458145824583458445854586458745884589459045914592459345944595459645974598459946004601460246034604460546064607460846094610461146124613461446154616461746184619462046214622462346244625462646274628462946304631463246334634463546364637463846394640464146424643464446454646464746484649465046514652465346544655465646574658465946604661466246634664466546664667466846694670467146724673467446754676467746784679468046814682468346844685468646874688468946904691469246934694469546964697469846994700470147024703470447054706470747084709471047114712471347144715471647174718471947204721472247234724472547264727472847294730473147324733473447354736473747384739474047414742474347444745474647474748474947504751475247534754475547564757475847594760476147624763476447654766476747684769477047714772477347744775477647774778477947804781478247834784478547864787478847894790479147924793479447954796479747984799480048014802480348044805480648074808480948104811481248134814481548164817481848194820482148224823482448254826482748284829483048314832483348344835483648374838483948404841484248434844484548464847484848494850485148524853485448554856485748584859486048614862486348644865486648674868486948704871487248734874487548764877487848794880488148824883488448854886488748884889489048914892489348944895489648974898489949004901490249034904490549064907490849094910491149124913491449154916491749184919492049214922492349244925492649274928492949304931493249334934493549364937493849394940494149424943494449454946494749484949495049514952495349544955495649574958495949604961496249634964496549664967496849694970497149724973497449754976497749784979498049814982498349844985498649874988498949904991499249934994499549964997499849995000500150025003500450055006500750085009501050115012501350145015501650175018501950205021502250235024502550265027502850295030503150325033503450355036503750385039504050415042
  1. <?php
  2. /* For licensing terms, see /license.txt*/
  3. /**
  4. * This is the course library for Chamilo.
  5. *
  6. * All main course functions should be placed here.
  7. *
  8. * Many functions of this library deal with providing support for
  9. * virtual/linked/combined courses (this was already used in several universities
  10. * but not available in standard Chamilo).
  11. *
  12. * The implementation changed, initially a course was a real course
  13. * if target_course_code was 0 , this was changed to NULL.
  14. * There are probably some places left with the wrong code.
  15. *
  16. * @package chamilo.library
  17. */
  18. /**
  19. * CourseManager Class
  20. * @package chamilo.library
  21. */
  22. class CourseManager {
  23. CONST MAX_COURSE_LENGTH_CODE = 40;
  24. //This constant is used to show separate user names in the course list (userportal), footer, etc
  25. CONST USER_SEPARATOR = ' |';
  26. CONST COURSE_FIELD_TYPE_CHECKBOX = 10;
  27. var $columns = array();
  28. /**
  29. * Creates a course
  30. * @param array with the columns in the main.course table
  31. * @param mixed false if the course was not created, array with the course info
  32. * @return mixed False on error, or an array with course attributes on success
  33. * @assert () === false
  34. */
  35. static function create_course($params) {
  36. global $_configuration;
  37. // Check portal limits
  38. $access_url_id = 1;
  39. if (api_get_multiple_access_url()) {
  40. $access_url_id = api_get_current_access_url_id();
  41. }
  42. if (is_array($_configuration[$access_url_id]) && isset($_configuration[$access_url_id]['hosting_limit_courses']) && $_configuration[$access_url_id]['hosting_limit_courses'] > 0) {
  43. $num = self::count_courses();
  44. if ($num >= $_configuration[$access_url_id]['hosting_limit_courses']) {
  45. return api_set_failure('PortalCoursesLimitReached');
  46. }
  47. }
  48. if (empty($params['title'])) {
  49. return false;
  50. }
  51. if (empty($params['wanted_code'])) {
  52. $params['wanted_code'] = $params['title'];
  53. // Check whether the requested course code has already been occupied.
  54. $params['wanted_code'] = self::generate_course_code(api_substr($params['title'], 0, self::MAX_COURSE_LENGTH_CODE));
  55. }
  56. // Create the course keys
  57. $keys = self::define_course_keys($params['wanted_code']);
  58. $params['exemplary_content'] = isset($params['exemplary_content']) ? $params['exemplary_content'] : false;
  59. if (count($keys)) {
  60. $params['code'] = $keys['currentCourseCode'];
  61. $params['visual_code'] = $keys['currentCourseId'];
  62. $params['directory'] = $keys['currentCourseRepository'];
  63. $course_info = api_get_course_info($params['code']);
  64. if (empty($course_info)) {
  65. $course_id = self::register_course($params);
  66. $course_info = api_get_course_info_by_id($course_id);
  67. if (!empty($course_info)) {
  68. self::prepare_course_repository($course_info['directory'], $course_info['code']);
  69. self::fill_db_course($course_id, $course_info['directory'], $course_info['course_language'], $params['exemplary_content']);
  70. //Create an empty gradebook
  71. if (isset($params['create_gradebook_evaluation']) && $params['create_gradebook_evaluation'] == true) {
  72. require_once api_get_path(SYS_CODE_PATH).'gradebook/lib/gradebook_functions.inc.php';
  73. $category_id = create_default_course_gradebook($course_info['code']);
  74. if ($category_id && isset($params['gradebook_params'])) {
  75. $eval = new Evaluation();
  76. $eval->set_name($params['gradebook_params']['name']);
  77. $eval->set_user_id($params['gradebook_params']['user_id']);
  78. $eval->set_course_code($course_info['code']);
  79. $eval->set_category_id($category_id);
  80. $eval->set_weight($params['gradebook_params']['weight']);
  81. $eval->set_max($params['gradebook_params']['max']);
  82. $eval->set_visible(0);
  83. $eval->add();
  84. }
  85. }
  86. if (api_get_setting('gradebook_enable_grade_model') == 'true') {
  87. //Create gradebook_category for the new course and add a gradebook model for the course
  88. if (isset($params['gradebook_model_id']) && !empty($params['gradebook_model_id']) && $params['gradebook_model_id'] != '-1') {
  89. require_once api_get_path(SYS_CODE_PATH).'gradebook/lib/gradebook_functions.inc.php';
  90. create_default_course_gradebook($course_info['code'], $params['gradebook_model_id']);
  91. }
  92. }
  93. //Saving extra course fields
  94. $field_value = new ExtraFieldValue('course');
  95. $params['course_code'] = $course_info['code'];
  96. $field_value->save_field_values($params);
  97. return $course_info;
  98. }
  99. } else {
  100. //error_log('Course already exists with params: '.$params['code']);
  101. //error_log(print_r($params, 1));
  102. //Course already exists
  103. if (isset($params['return_item_if_already_exists']) && $params['return_item_if_already_exists']) {
  104. return $course_info;
  105. }
  106. }
  107. }
  108. return false;
  109. }
  110. /**
  111. * Updates a course with the given array of attributes
  112. * @param array Array of attributes
  113. * @return array Array of course attributes
  114. * @assert () === false
  115. */
  116. static function update($params) {
  117. if (!is_array($params) or count($params)<1) { return false; }
  118. $course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  119. $course_table = Database::get_main_table(TABLE_MAIN_COURSE);
  120. $course_code = $params['code'];
  121. $visual_code = $params['visual_code'];
  122. $field_value = new ExtraFieldValue('course');
  123. $params['course_code'] = $course_code;
  124. $field_value->save_field_values($params);
  125. /*$tutor_id = $params['tutor_name'];
  126. $tutor_name=$platform_teachers[$tutor_id];*/
  127. $teachers = $params['group']['course_teachers'];
  128. $title = $params['title'];
  129. $category_code = $params['category_code'];
  130. $department_name = $params['department_name'];
  131. $department_url = $params['department_url'];
  132. $course_language = $params['course_language'];
  133. $course['disk_quota'] = $params['disk_quota']*1024*1024;
  134. $disk_quota = $params['disk_quota'];
  135. $visibility = $params['visibility'];
  136. $subscribe = $params['subscribe'];
  137. $unsubscribe = $params['unsubscribe'];
  138. if (!stristr($department_url, 'http://')) {
  139. $department_url = 'http://'.$department_url;
  140. }
  141. //tutor_name='".Database::escape_string($tutor_name)."',
  142. $sql = "UPDATE $course_table SET
  143. course_language='".Database::escape_string($course_language)."',
  144. title='".Database::escape_string($title)."',
  145. category_code='".Database::escape_string($category_code)."',
  146. visual_code='".Database::escape_string($visual_code)."',
  147. department_name='".Database::escape_string($department_name)."',
  148. department_url='".Database::escape_string($department_url)."',
  149. disk_quota='".Database::escape_string($disk_quota)."',
  150. visibility = '".Database::escape_string($visibility)."',
  151. subscribe = '".Database::escape_string($subscribe)."',
  152. unsubscribe='".Database::escape_string($unsubscribe)."'
  153. WHERE code='".Database::escape_string($course_code)."'";
  154. Database::query($sql);
  155. //Delete only teacher relations that doesn't match the selected teachers
  156. $cond='';
  157. if (count($teachers)>0) {
  158. foreach($teachers as $key) $cond.=" AND user_id<>'".$key."'";
  159. }
  160. $sql='DELETE FROM '.$course_user_table.' WHERE course_code="'.Database::escape_string($course_code).'" AND status="1"'.$cond;
  161. Database::query($sql);
  162. if (count($teachers)>0) {
  163. foreach ($teachers as $key) {
  164. //We check if the teacher is already subscribed in this course
  165. $sql_select_teacher = 'SELECT 1 FROM '.$course_user_table.' WHERE user_id = "'.$key.'" AND course_code = "'.$course_code.'" ';
  166. $result = Database::query($sql_select_teacher);
  167. if (Database::num_rows($result) == 1) {
  168. $sql = 'UPDATE '.$course_user_table.' SET status = "1" WHERE course_code = "'.$course_code.'" AND user_id = "'.$key.'" ';
  169. } else {
  170. $sql = "INSERT INTO ".$course_user_table . " SET
  171. course_code = '".Database::escape_string($course_code). "',
  172. user_id = '".$key . "',
  173. status = '1',
  174. role = '',
  175. tutor_id='0',
  176. sort='0',
  177. user_course_cat='0'";
  178. }
  179. Database::query($sql);
  180. }
  181. }
  182. //user_id = '".$tutor_id . "',
  183. $sql = "INSERT IGNORE INTO ".$course_user_table . " SET
  184. course_code = '".Database::escape_string($course_code). "',
  185. status = '1',
  186. role = '',
  187. tutor_id='0',
  188. sort='0',
  189. user_course_cat='0'";
  190. Database::query($sql);
  191. $course_info = api_get_course_info($course_code);
  192. //$course_id = $course_info['real_id'];
  193. //That table does no exists
  194. /*$forum_config_table = Database::get_course_table(TOOL_FORUM_CONFIG_TABLE);
  195. $sql = "UPDATE ".$forum_config_table." SET default_lang='".Database::escape_string($course_language)."' WHERE c_id = $course_id ";
  196. Database::query($sql);*/
  197. return $course_info;
  198. }
  199. /**
  200. * Returns all the information of a given coursecode from the course table
  201. * @param string $course_code, the course code
  202. * @return an array with all the fields of the course table
  203. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  204. * @assert (array(1,2,3)) === false
  205. */
  206. public static function get_course_information($course_code) {
  207. return Database::fetch_array(Database::query(
  208. "SELECT *, id as real_id FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  209. WHERE code='".Database::escape_string($course_code)."'"),'ASSOC'
  210. );
  211. }
  212. /**
  213. * Returns all the information of a given coursecode from the course
  214. * table by it integer ID
  215. * @param int the course id
  216. * @return an array with all the fields of the course table
  217. * @assert (-1) === false
  218. */
  219. public static function get_course_information_by_id($course_id) {
  220. return Database::select('*, id as real_id', Database::get_main_table(TABLE_MAIN_COURSE), array('where'=>array('id = ?' =>intval($course_id))),'first');
  221. }
  222. /**
  223. * Returns a list of courses. Should work with quickform syntax
  224. * @param integer Offset (from the 7th = '6'). Optional.
  225. * @param integer Number of results we want. Optional.
  226. * @param string The column we want to order it by. Optional, defaults to first column.
  227. * @param string The direction of the order (ASC or DESC). Optional, defaults to ASC.
  228. * @param string The visibility of the course, or all by default.
  229. * @param string If defined, only return results for which the course *title* begins with this string
  230. * @return mixed Array of courses details, or false on error
  231. * @assert (array(1,2,3)) === false
  232. */
  233. public static function get_courses_list($from = 0, $howmany = 0, $orderby = 1, $orderdirection = 'ASC', $visibility = -1, $startwith = '') {
  234. $sql = "SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." ";
  235. if (!empty($startwith)) {
  236. $sql .= "WHERE title LIKE '".Database::escape_string($startwith)."%' ";
  237. if ($visibility !== -1 && $visibility == strval(intval($visibility))) {
  238. $sql .= " AND visibility = $visibility ";
  239. }
  240. } else {
  241. $sql .= "WHERE 1 ";
  242. if ($visibility !== -1 && $visibility == strval(intval($visibility))) {
  243. $sql .= " AND visibility = $visibility ";
  244. }
  245. }
  246. if (!empty($orderby)) {
  247. $sql .= " ORDER BY ".Database::escape_string($orderby)." ";
  248. } else {
  249. $sql .= " ORDER BY 1 ";
  250. }
  251. if (!in_array($orderdirection, array('ASC', 'DESC'))) {
  252. $sql .= 'ASC';
  253. } else {
  254. $sql .= Database::escape_string($orderdirection);
  255. }
  256. if (!empty($howmany) && is_int($howmany) and $howmany > 0) {
  257. $sql .= ' LIMIT '.Database::escape_string($howmany);
  258. } else {
  259. $sql .= ' LIMIT 1000000'; //virtually no limit
  260. }
  261. if (!empty($from)) {
  262. $from = intval($from);
  263. $sql .= ' OFFSET '.Database::escape_string($from);
  264. } else {
  265. $sql .= ' OFFSET 0';
  266. }
  267. return Database::store_result(Database::query($sql));
  268. }
  269. /**
  270. * Returns the access settings of the course:
  271. * which visibility;
  272. * wether subscribing is allowed;
  273. * wether unsubscribing is allowed.
  274. *
  275. * @param string $course_code, the course code
  276. * @todo for more consistency: use course_info call from database API
  277. * @return an array with int fields "visibility", "subscribe", "unsubscribe"
  278. * @assert () === false
  279. */
  280. public static function get_access_settings($course_code) {
  281. return Database::fetch_array(Database::query(
  282. "SELECT visibility, subscribe, unsubscribe from ".Database::get_main_table(TABLE_MAIN_COURSE)."
  283. WHERE code = '".Database::escape_string($course_code)."'")
  284. );
  285. }
  286. /**
  287. * Returns the status of a user in a course, which is COURSEMANAGER
  288. * or STUDENT.
  289. * @param int User ID
  290. * @param string Course code
  291. * @return int the status of the user in that course
  292. * @assert () === false
  293. */
  294. public static function get_user_in_course_status($user_id, $course_code) {
  295. $result = Database::fetch_array(Database::query(
  296. "SELECT status FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  297. WHERE course_code = '".Database::escape_string($course_code)."' AND user_id = ".Database::escape_string($user_id))
  298. );
  299. return $result['status'];
  300. }
  301. /**
  302. * Gets the ID of a course tutor for a user, given the user and the course
  303. * @param int User ID
  304. * @param string Course code
  305. * @return mixed The tutor ID
  306. * @assert () === false
  307. */
  308. public static function get_tutor_in_course_status($user_id, $course_code) {
  309. $result = Database::fetch_array(Database::query(
  310. "SELECT tutor_id FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  311. WHERE course_code = '".Database::escape_string($course_code)."' AND user_id = ".Database::escape_string($user_id))
  312. );
  313. return $result['tutor_id'];
  314. }
  315. /**
  316. * Unsubscribe one or more users from a course
  317. * @param mixed user_id or an array with user ids
  318. * @param int session id
  319. * @param string course code
  320. * @return mixed False on user ID not found, void otherwise
  321. * @assert () === false
  322. */
  323. public static function unsubscribe_user($user_id, $course_code, $session_id = 0) {
  324. if (!isset($user_id)) { return false; }
  325. if (!is_array($user_id)) {
  326. $user_id = array($user_id);
  327. }
  328. if (count($user_id) == 0) {
  329. return false;
  330. }
  331. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  332. if (!empty($session_id)) {
  333. $session_id = intval($session_id);
  334. } else {
  335. $session_id = intval($_SESSION['id_session']);
  336. }
  337. $user_list = array();
  338. //Cleaning the $user_id variable
  339. if (is_array($user_id)) {
  340. $new_user_id_list = array();
  341. foreach($user_id as $my_user_id) {
  342. $new_user_id_list[]= intval($my_user_id);
  343. }
  344. $new_user_id_list = array_filter($new_user_id_list);
  345. $user_list = $new_user_id_list;
  346. $user_ids = implode(',', $new_user_id_list);
  347. } else {
  348. $user_ids = intval($user_id);
  349. $user_list[] = $user_id;
  350. }
  351. $course_info = api_get_course_info($course_code);
  352. $course_id = $course_info['real_id'];
  353. // Unsubscribe user from all groups in the course.
  354. Database::query("DELETE FROM ".Database::get_course_table(TABLE_GROUP_USER)." WHERE c_id = $course_id AND user_id IN (".$user_ids.")");
  355. Database::query("DELETE FROM ".Database::get_course_table(TABLE_GROUP_TUTOR)." WHERE c_id = $course_id AND user_id IN (".$user_ids.")");
  356. // Erase user student publications (works) in the course - by André Boivin
  357. //@todo field student_publication.author should be the user id
  358. $sqlu = "SELECT * FROM $table_user WHERE user_id IN (".$user_ids.")";
  359. $resu = Database::query($sqlu);
  360. $username = Database::fetch_array($resu,'ASSOC');
  361. $userfirstname = $username['firstname'];
  362. $userlastname = $username['lastname'];
  363. $publication_name = $userfirstname.' '.$userlastname ;
  364. $table_course_user_publication = Database :: get_course_table(TABLE_STUDENT_PUBLICATION);
  365. $sql = "DELETE FROM $table_course_user_publication WHERE c_id = $course_id AND author = '".Database::escape_string($publication_name)."'";
  366. Database::query($sql);
  367. // Unsubscribe user from all blogs in the course.
  368. Database::query("DELETE FROM ".Database::get_course_table(TABLE_BLOGS_REL_USER)." WHERE c_id = $course_id AND user_id IN (".$user_ids.")");
  369. Database::query("DELETE FROM ".Database::get_course_table(TABLE_BLOGS_TASKS_REL_USER)."WHERE c_id = $course_id AND user_id IN (".$user_ids.")");
  370. //Deleting users in forum_notification and mailqueue course tables
  371. $sql_delete_forum_notification = "DELETE FROM ".Database::get_course_table(TABLE_FORUM_NOTIFICATION)." WHERE c_id = $course_id AND user_id IN (".$user_ids.")";
  372. Database::query($sql_delete_forum_notification);
  373. $sql_delete_mail_queue = "DELETE FROM ".Database::get_course_table(TABLE_FORUM_MAIL_QUEUE)." WHERE c_id = $course_id AND user_id IN (".$user_ids.")";
  374. Database::query($sql_delete_mail_queue);
  375. // Unsubscribe user from the course.
  376. if (!empty($session_id)) {
  377. // Delete in table session_rel_course_rel_user
  378. Database::query("DELETE FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  379. WHERE id_session ='".$session_id."' AND course_code = '".Database::escape_string($_SESSION['_course']['id'])."' AND id_user IN ($user_ids)");
  380. foreach ($user_id as $uid) {
  381. // check if a user is register in the session with other course
  382. $sql = "SELECT id_user FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)." WHERE id_session='$session_id' AND id_user='$uid'";
  383. $rs = Database::query($sql);
  384. if (Database::num_rows($rs) == 0) {
  385. // Delete in table session_rel_user
  386. Database::query("DELETE FROM ".Database::get_main_table(TABLE_MAIN_SESSION_USER)."
  387. WHERE id_session ='".$session_id."' AND id_user='$uid' AND relation_type<>".SESSION_RELATION_TYPE_RRHH."");
  388. }
  389. }
  390. // Update the table session
  391. $row = Database::fetch_array(Database::query("SELECT COUNT(*) FROM ".Database::get_main_table(TABLE_MAIN_SESSION_USER)."
  392. WHERE id_session = '".$session_id."' AND relation_type<>".SESSION_RELATION_TYPE_RRHH." "));
  393. $count = $row[0]; // number of users by session
  394. $result = Database::query("UPDATE ".Database::get_main_table(TABLE_MAIN_SESSION)." SET nbr_users = '$count'
  395. WHERE id = '".$session_id."'");
  396. // Update the table session_rel_course
  397. $row = Database::fetch_array(@Database::query("SELECT COUNT(*) FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)." WHERE id_session = '$session_id' AND course_code = '$course_code' AND status<>2" ));
  398. $count = $row[0]; // number of users by session and course
  399. $result = @Database::query("UPDATE ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE)." SET nbr_users = '$count' WHERE id_session = '$session_id' AND course_code = '$course_code' ");
  400. } else {
  401. Database::query("DELETE FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  402. WHERE user_id IN (".$user_ids.") AND relation_type<>".COURSE_RELATION_TYPE_RRHH." AND course_code = '".$course_code."'");
  403. // add event to system log
  404. $user_id = api_get_user_id();
  405. event_system(LOG_UNSUBSCRIBE_USER_FROM_COURSE, LOG_COURSE_CODE, $course_code, api_get_utc_datetime(), $user_id);
  406. foreach ($user_list as $user_id_to_delete) {
  407. $user_info = api_get_user_info($user_id_to_delete);
  408. event_system(LOG_UNSUBSCRIBE_USER_FROM_COURSE, LOG_USER_OBJECT, $user_info, api_get_utc_datetime(), $user_id);
  409. }
  410. }
  411. }
  412. /**
  413. * Subscribe a user to a course. No checks are performed here to see if
  414. * course subscription is allowed.
  415. * @param int User ID
  416. * @param string Course code
  417. * @param int Status (STUDENT, COURSEMANAGER, COURSE_ADMIN, NORMAL_COURSE_MEMBER)
  418. * @return bool True on success, false on failure
  419. * @see add_user_to_course
  420. */
  421. public static function subscribe_user($user_id, $course_code, $status = STUDENT, $session_id = 0) {
  422. if ($user_id != strval(intval($user_id))) {
  423. return false; //detected possible SQL injection
  424. }
  425. $course_code = Database::escape_string($course_code);
  426. if (empty ($user_id) || empty ($course_code)) {
  427. return false;
  428. }
  429. if (!empty($session_id)) {
  430. $session_id = intval($session_id);
  431. } else {
  432. $session_id = api_get_session_id();
  433. }
  434. $status = ($status == STUDENT || $status == COURSEMANAGER) ? $status : STUDENT;
  435. //$role_id = ($status == COURSEMANAGER) ? COURSE_ADMIN : NORMAL_COURSE_MEMBER;
  436. // A preliminary check whether the user has bben already registered on the platform.
  437. if (Database::num_rows(@Database::query("SELECT status FROM ".Database::get_main_table(TABLE_MAIN_USER)."
  438. WHERE user_id = '$user_id' ")) == 0) {
  439. return false; // The user has not been registered to the platform.
  440. }
  441. // Check whether the user has not been already subscribed to the course.
  442. if (empty($session_id)) {
  443. if (Database::num_rows(@Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  444. WHERE user_id = '$user_id' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." AND course_code = '$course_code'")) > 0) {
  445. return false; // The user has been already subscribed to the course.
  446. }
  447. }
  448. if (!empty($session_id)) {
  449. // Check whether the user has not already been stored in the session_rel_course_user table
  450. if (Database::num_rows(@Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  451. WHERE course_code = '".$_SESSION['_course']['id']."'
  452. AND id_session ='".$session_id."'
  453. AND id_user = '".$user_id."'")) > 0) {
  454. return false;
  455. }
  456. // check if the user is registered in the session with other course
  457. $sql = "SELECT id_user FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)." WHERE id_session='".$session_id."' AND id_user='$user_id'";
  458. $rs = Database::query($sql);
  459. if (Database::num_rows($rs) == 0) {
  460. // Check whether the user has not already been stored in the session_rel_user table
  461. if (Database::num_rows(@Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_SESSION_USER)."
  462. WHERE id_session ='".$session_id."'
  463. AND id_user = '".$user_id."' AND relation_type<>".SESSION_RELATION_TYPE_RRHH." ")) > 0) {
  464. return false;
  465. }
  466. }
  467. // Add him/her in the table session_rel_course_rel_user
  468. @Database::query("INSERT INTO ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  469. SET id_session ='".$session_id."',
  470. course_code = '".$_SESSION['_course']['id']."',
  471. id_user = '".$user_id."'");
  472. // Add him/her in the table session_rel_user
  473. @Database::query("INSERT INTO ".Database::get_main_table(TABLE_MAIN_SESSION_USER)."
  474. SET id_session ='".$session_id."',
  475. id_user = '".$user_id."'");
  476. // Update the table session
  477. $row = Database::fetch_array(@Database::query("SELECT COUNT(*) FROM ".Database::get_main_table(TABLE_MAIN_SESSION_USER)." WHERE id_session = '".$session_id."' AND relation_type<>".SESSION_RELATION_TYPE_RRHH.""));
  478. $count = $row[0]; // number of users by session
  479. $result = @Database::query("UPDATE ".Database::get_main_table(TABLE_MAIN_SESSION)." SET nbr_users = '$count' WHERE id = '".$session_id."'");
  480. // Update the table session_rel_course
  481. $row = Database::fetch_array(@Database::query("SELECT COUNT(*) FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)." WHERE id_session = '".$session_id."' AND course_code = '$course_code' AND status<>2" ));
  482. $count = $row[0]; // number of users by session
  483. $result = @Database::query("UPDATE ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE)." SET nbr_users = '$count' WHERE id_session = '".$session_id."' AND course_code = '$course_code' ");
  484. } else {
  485. $course_sort = self::userCourseSort($user_id, $course_code);
  486. $result = @Database::query("INSERT INTO ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  487. SET course_code = '$course_code',
  488. user_id = '$user_id',
  489. status = '".$status."',
  490. sort = '". ($course_sort)."'");
  491. // Add event to the system log
  492. event_system(LOG_SUBSCRIBE_USER_TO_COURSE, LOG_COURSE_CODE, $course_code, api_get_utc_datetime(), api_get_user_id());
  493. $user_info = api_get_user_info($user_id);
  494. event_system(LOG_SUBSCRIBE_USER_TO_COURSE, LOG_USER_OBJECT, $user_info, api_get_utc_datetime(), api_get_user_id());
  495. }
  496. return (bool)$result;
  497. }
  498. /**
  499. * Get the course id based on the original id and field name in the extra fields. Returns 0 if course was not found
  500. *
  501. * @param string Original course id
  502. * @param string Original field name
  503. * @return int Course id
  504. */
  505. public static function get_course_code_from_original_id($original_course_id_value, $original_course_id_name) {
  506. $t_cfv = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  507. $table_field = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  508. $sql_course = "SELECT course_code FROM $table_field cf INNER JOIN $t_cfv cfv ON cfv.field_id=cf.id WHERE field_variable='$original_course_id_name' AND field_value='$original_course_id_value'";
  509. $res = Database::query($sql_course);
  510. $row = Database::fetch_object($res);
  511. if ($row) {
  512. return $row->course_code;
  513. } else {
  514. return 0;
  515. }
  516. }
  517. /**
  518. * Gets the course code from the course id. Returns null if course id was not found
  519. *
  520. * @param int Course id
  521. * @return string Course code
  522. */
  523. public static function get_course_code_from_course_id($id) {
  524. $table = Database::get_main_table(TABLE_MAIN_COURSE);
  525. $id = intval($id);
  526. $sql = "SELECT code FROM $table WHERE id = '$id' ";
  527. $res = Database::query($sql);
  528. $row = Database::fetch_object($res);
  529. if ($row) {
  530. return $row->code;
  531. } else {
  532. return null;
  533. }
  534. }
  535. /**
  536. * Subscribe a user $user_id to a course $course_code.
  537. * @author Hugues Peeters
  538. * @author Roan Embrechts
  539. *
  540. * @param int $user_id the id of the user
  541. * @param string $course_code the course code
  542. * @param string $status (optional) The user's status in the course
  543. *
  544. * @return boolean true if subscription succeeds, boolean false otherwise.
  545. */
  546. public static function add_user_to_course($user_id, $course_code, $status = STUDENT) {
  547. $debug = false;
  548. $user_table = Database::get_main_table(TABLE_MAIN_USER);
  549. $course_table = Database::get_main_table(TABLE_MAIN_COURSE);
  550. $course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  551. $status = ($status == STUDENT || $status == COURSEMANAGER) ? $status : STUDENT;
  552. if (empty($user_id) || empty($course_code) || ($user_id != strval(intval($user_id)))) {
  553. return false;
  554. }
  555. $course_code = Database::escape_string($course_code);
  556. // Check in advance whether the user has already been registered on the platform.
  557. if (Database::num_rows(Database::query("SELECT status FROM ".$user_table." WHERE user_id = '$user_id' ")) == 0) {
  558. if ($debug) error_log('The user has not been registered to the platform');
  559. return false; // The user has not been registered to the platform.
  560. }
  561. // Check whether the user has already been subscribed to this course.
  562. if (Database::num_rows(Database::query("SELECT * FROM ".$course_user_table." WHERE user_id = '$user_id' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." AND course_code = '$course_code'")) > 0) {
  563. if ($debug) error_log('The user has been already subscribed to the course');
  564. return false; // The user has been subscribed to the course.
  565. }
  566. // Check in advance whether subscription is allowed or not for this course.
  567. if (Database::num_rows(Database::query("SELECT code, visibility FROM ".$course_table." WHERE code = '$course_code' AND subscribe = '".SUBSCRIBE_NOT_ALLOWED."'")) > 0) {
  568. if ($debug) error_log('Subscription is not allowed for this course');
  569. return false; // Subscription is not allowed for this course.
  570. }
  571. // Ok, subscribe the user.
  572. $max_sort = api_max_sort_value('0', $user_id);
  573. return (bool)Database::query("INSERT INTO ".$course_user_table."
  574. SET course_code = '$course_code',
  575. user_id = '$user_id',
  576. status = '".$status."',
  577. sort = '". ($max_sort + 1)."'");
  578. }
  579. /**
  580. * Checks wether a parameter exists.
  581. * If it doesn't, the function displays an error message.
  582. *
  583. * @return true if parameter is set and not empty, false otherwise
  584. * @todo move function to better place, main_api ?
  585. */
  586. public static function check_parameter($parameter, $error_message) {
  587. if (empty($parameter)) {
  588. Display::display_normal_message($error_message);
  589. return false;
  590. }
  591. return true;
  592. }
  593. /**
  594. * Lets the script die when a parameter check fails.
  595. * @todo move function to better place, main_api ?
  596. */
  597. public static function check_parameter_or_fail($parameter, $error_message) {
  598. if (!self::check_parameter($parameter, $error_message)) {
  599. die();
  600. }
  601. }
  602. /**
  603. * @return true if there already are one or more courses
  604. * with the same code OR visual_code (visualcode), false otherwise
  605. */
  606. public static function course_code_exists($wanted_course_code) {
  607. $wanted_course_code = Database::escape_string($wanted_course_code);
  608. $result = Database::fetch_array(Database::query("SELECT COUNT(*) as number FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." WHERE code = '$wanted_course_code' OR visual_code = '$wanted_course_code'"));
  609. return $result['number'] > 0;
  610. }
  611. /**
  612. * @return an array with the course info of all real courses on the platform
  613. */
  614. public static function get_real_course_list() {
  615. $sql_result = Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." WHERE target_course_code IS NULL");
  616. $real_course_list = array();
  617. while ($result = Database::fetch_array($sql_result)) {
  618. $real_course_list[$result['code']] = $result;
  619. }
  620. return $real_course_list;
  621. }
  622. /**
  623. * Lists all virtual courses
  624. * @return array Course info (course code => details) of all virtual courses on the platform
  625. */
  626. public static function get_virtual_course_list() {
  627. $sql_result = Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." WHERE target_course_code IS NOT NULL");
  628. $virtual_course_list = array();
  629. while ($result = Database::fetch_array($sql_result)) {
  630. $virtual_course_list[$result['code']] = $result;
  631. }
  632. return $virtual_course_list;
  633. }
  634. /**
  635. * Returns an array with the course info of the real courses of which
  636. * the current user is course admin
  637. * @return array A list of courses details for courses to which the user is subscribed as course admin (status = 1)
  638. */
  639. public static function get_real_course_list_of_user_as_course_admin($user_id) {
  640. $result_array = array();
  641. if ($user_id != strval(intval($user_id))) {
  642. return $result_array;
  643. }
  644. $sql_result = Database::query("SELECT *
  645. FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." course
  646. LEFT JOIN ".Database::get_main_table(TABLE_MAIN_COURSE_USER)." course_user
  647. ON course.code = course_user.course_code
  648. WHERE course.target_course_code IS NULL
  649. AND course_user.user_id = '$user_id'
  650. AND course_user.status = '1'");
  651. if ($sql_result === false) { return $result_array; }
  652. while ($result = Database::fetch_array($sql_result)) {
  653. $result_array[] = $result;
  654. }
  655. return $result_array;
  656. }
  657. /**
  658. * Get course list as coach
  659. *
  660. * @param int user id
  661. * @return array course list
  662. *
  663. * */
  664. public static function get_course_list_as_coach($user_id, $include_courses_in_sessions = false) {
  665. //1. Getting courses as teacher (No session)
  666. $courses_temp = CourseManager::get_course_list_of_user_as_course_admin($user_id);
  667. $courses_as_admin = array();
  668. if (!empty($courses_temp)) {
  669. foreach($courses_temp as $course_item) {
  670. $courses_as_admin[0][$course_item['course_code']] = $course_item['course_code'];
  671. }
  672. }
  673. //2. Include courses in sessions
  674. if ($include_courses_in_sessions) {
  675. $sessions = SessionManager::get_sessions_coached_by_user($user_id);
  676. if (!empty($sessions)) {
  677. foreach($sessions as $session_item) {
  678. $courses = Tracking :: get_courses_followed_by_coach($user_id, $session_item['id']);
  679. if (is_array($courses)) {
  680. foreach($courses as $course_item) {
  681. $courses_as_admin[$session_item['id']][$course_item] = $course_item;
  682. }
  683. }
  684. }
  685. }
  686. }
  687. return $courses_as_admin;
  688. }
  689. public static function get_user_list_from_courses_as_coach($user_id, $include_sessions = true) {
  690. $courses_as_admin = $students_in_courses = array();
  691. $sessions = CourseManager::get_course_list_as_coach($user_id, true);
  692. if (!empty($sessions)) {
  693. foreach($sessions as $session_id => $courses) {
  694. if (!$include_sessions) {
  695. if (!empty($session_id)) {
  696. continue;
  697. }
  698. }
  699. if (empty($session_id)) {
  700. foreach($courses as $course_code) {
  701. $students_in_course = CourseManager::get_user_list_from_course_code($course_code);
  702. foreach($students_in_course as $user_item) {
  703. //Only students
  704. if ($user_item['status_rel'] == STUDENT)
  705. $students_in_courses[$user_item['user_id']] = $user_item['user_id'];
  706. }
  707. }
  708. } else {
  709. $students_in_course = SessionManager::get_users_by_session($session_id, '0');
  710. if (is_array($students_in_course)) {
  711. foreach ($students_in_course as $user_item) {
  712. if ($user_item['moved_to'] == 0) {
  713. $students_in_courses[$user_item['user_id']] = $user_item['user_id'];
  714. }
  715. }
  716. }
  717. }
  718. }
  719. }
  720. $students = Tracking :: get_student_followed_by_coach($user_id);
  721. if (!empty($students_in_courses)) {
  722. if (!empty($students)) {
  723. $students = array_merge($students, $students_in_courses);
  724. } else {
  725. $students = $students_in_courses;
  726. }
  727. }
  728. if (!empty($students)) {
  729. $students = array_unique($students);
  730. }
  731. return $students;
  732. }
  733. /**
  734. * @return an array with the course info of all the courses (real and virtual) of which
  735. * the current user is course admin
  736. */
  737. public static function get_course_list_of_user_as_course_admin($user_id) {
  738. if ($user_id != strval(intval($user_id))) {
  739. return array();
  740. }
  741. // Definitions database tables and variables
  742. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  743. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  744. $user_id = intval($user_id);
  745. $data = array();
  746. $sql_nb_cours = "SELECT course_rel_user.course_code, course.title, course.id, course.db_name, course.id as real_id
  747. FROM $tbl_course_user as course_rel_user
  748. INNER JOIN $tbl_course as course
  749. ON course.code = course_rel_user.course_code
  750. WHERE course_rel_user.user_id='$user_id' AND course_rel_user.status='1'
  751. ORDER BY course.title";
  752. if (api_get_multiple_access_url()) {
  753. $tbl_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  754. $access_url_id = api_get_current_access_url_id();
  755. if ($access_url_id != -1) {
  756. $sql_nb_cours = " SELECT course_rel_user.course_code, course.title, course.id, course.db_name, course.id as real_id
  757. FROM $tbl_course_user as course_rel_user
  758. INNER JOIN $tbl_course as course
  759. ON course.code = course_rel_user.course_code
  760. INNER JOIN $tbl_course_rel_access_url course_rel_url
  761. ON (course_rel_url.course_code= course.code)
  762. WHERE access_url_id = $access_url_id AND course_rel_user.user_id='$user_id' AND course_rel_user.status='1'
  763. ORDER BY course.title";
  764. }
  765. }
  766. $result_nb_cours = Database::query($sql_nb_cours);
  767. if (Database::num_rows($result_nb_cours) > 0) {
  768. while ($row = Database::fetch_array($result_nb_cours,'ASSOC')) {
  769. $data[$row['course_code']] = $row;
  770. }
  771. }
  772. return $data;
  773. }
  774. /**
  775. * Find out for which courses the user is registered and determine a visual course code and course title from that.
  776. * Takes virtual courses into account
  777. *
  778. * Default case: the name and code stay what they are.
  779. *
  780. * Scenarios:
  781. * - User is registered in real course and virtual courses; name / code become a mix of all
  782. * - User is registered in real course only: name stays that of real course
  783. * - User is registered in virtual course only: name becomes that of virtual course
  784. * - user is not registered to any of the real/virtual courses: name stays that of real course
  785. * (I'm not sure about the last case, but this seems not too bad)
  786. *
  787. * @author Roan Embrechts
  788. * @param $user_id, the id of the user
  789. * @param $course_info, an array with course info that you get using Database::get_course_info($course_system_code);
  790. * @return an array with indices
  791. * $return_result['title'] - the course title of the combined courses
  792. * $return_result['code'] - the course code of the combined courses
  793. * @deprecated use api_get_course_info()
  794. */
  795. public static function determine_course_title_from_course_info($user_id, $course_info) {
  796. if ($user_id != strval(intval($user_id))) {
  797. return array();
  798. }
  799. $real_course_id = $course_info['system_code'];
  800. $real_course_info = Database::get_course_info($real_course_id);
  801. $real_course_name = $real_course_info['title'];
  802. $real_course_visual_code = $real_course_info['visual_code'];
  803. $real_course_real_code = Database::escape_string($course_info['system_code']);
  804. //is the user registered in the real course?
  805. $result = Database::fetch_array(Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  806. WHERE user_id = '$user_id' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." AND course_code = '$real_course_real_code'"));
  807. $user_is_registered_in_real_course = !empty($result);
  808. //get a list of virtual courses linked to the current real course and to which the current user is subscribed
  809. $user_subscribed_virtual_course_list = self::get_list_of_virtual_courses_for_specific_user_and_real_course($user_id, $real_course_id);
  810. $virtual_courses_exist = count($user_subscribed_virtual_course_list) > 0;
  811. //now determine course code and name
  812. if ($user_is_registered_in_real_course && $virtual_courses_exist) {
  813. $course_info['name'] = self::create_combined_name($user_is_registered_in_real_course, $real_course_name, $user_subscribed_virtual_course_list);
  814. $course_info['official_code'] = self::create_combined_code($user_is_registered_in_real_course, $real_course_visual_code, $user_subscribed_virtual_course_list);
  815. }
  816. elseif ($user_is_registered_in_real_course) {
  817. //course name remains real course name
  818. $course_info['name'] = $real_course_name;
  819. $course_info['official_code'] = $real_course_visual_code;
  820. }
  821. elseif ($virtual_courses_exist) {
  822. $course_info['name'] = self::create_combined_name($user_is_registered_in_real_course, $real_course_name, $user_subscribed_virtual_course_list);
  823. $course_info['official_code'] = self::create_combined_code($user_is_registered_in_real_course, $real_course_visual_code, $user_subscribed_virtual_course_list);
  824. } else {
  825. //course name remains real course name
  826. $course_info['name'] = $real_course_name;
  827. $course_info['official_code'] = $real_course_visual_code;
  828. }
  829. $return_result['title'] = $course_info['name'];
  830. $return_result['code'] = $course_info['official_code'];
  831. return $return_result;
  832. }
  833. /**
  834. * Create a course title based on all real and virtual courses the user is registered in.
  835. * @param boolean $user_is_registered_in_real_course
  836. * @param string $real_course_name, the title of the real course
  837. * @param array $virtual_course_list, the list of virtual courses
  838. */
  839. public static function create_combined_name($user_is_registered_in_real_course, $real_course_name, $virtual_course_list) {
  840. $complete_course_name = array();
  841. if ($user_is_registered_in_real_course) {
  842. // Add the real name to the result.
  843. $complete_course_name[] = $real_course_name;
  844. }
  845. // Add course titles of all virtual courses.
  846. foreach ($virtual_course_list as $current_course) {
  847. $complete_course_name[] = $current_course['title'];
  848. }
  849. // 'CombinedCourse' is from course_home language file.
  850. return (($user_is_registered_in_real_course || count($virtual_course_list) > 1) ? get_lang('CombinedCourse').' ' : '').implode(' &amp; ', $complete_course_name);
  851. }
  852. /**
  853. * Create a course code based on all real and virtual courses the user is registered in.
  854. */
  855. public static function create_combined_code($user_is_registered_in_real_course, $real_course_code, $virtual_course_list) {
  856. $complete_course_code = array();
  857. if ($user_is_registered_in_real_course) {
  858. // Add the real code to the result
  859. $complete_course_code[] = $real_course_code;
  860. }
  861. // Add codes of all virtual courses.
  862. foreach ($virtual_course_list as $current_course) {
  863. $complete_course_code[] = $current_course['visual_code'];
  864. }
  865. return implode(' &amp; ', $complete_course_code);
  866. }
  867. /**
  868. * Return course info array of virtual course
  869. *
  870. * Note this is different from getting information about a real course!
  871. *
  872. * @param $real_course_code, the id of the real course which the virtual course is linked to
  873. * @deprecated virtual courses doesn't exist anymore
  874. */
  875. public static function get_virtual_course_info($real_course_code) {
  876. $sql_result = Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  877. WHERE target_course_code = '".Database::escape_string($real_course_code)."'");
  878. $result = array();
  879. while ($virtual_course = Database::fetch_array($sql_result)) {
  880. $result[] = $virtual_course;
  881. }
  882. return $result;
  883. }
  884. /**
  885. * @param string $system_code, the system code of the course
  886. * @return true if the course is a virtual course, false otherwise
  887. * @deprecated virtual courses doesn't exist anymore
  888. */
  889. public static function is_virtual_course_from_system_code($system_code) {
  890. $result = Database::fetch_array(Database::query("SELECT target_course_code FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  891. WHERE code = '".Database::escape_string($system_code)."'"));
  892. return !empty($result['target_course_code']);
  893. }
  894. /**
  895. * Returns whether the course code given is a visual code
  896. * @param string Visual course code
  897. * @return true if the course is a virtual course, false otherwise
  898. * @deprecated virtual courses doesn't exist anymore
  899. */
  900. public static function is_virtual_course_from_visual_code($visual_code) {
  901. $result = Database::fetch_array(Database::query("SELECT target_course_code FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  902. WHERE visual_code = '".Database::escape_string($visual_code)."'"));
  903. return !empty($result['target_course_code']);
  904. }
  905. /**
  906. * @return true if the real course has virtual courses that the user is subscribed to, false otherwise
  907. * @deprecated virtual courses doesn't exist anymore
  908. */
  909. public static function has_virtual_courses_from_code($real_course_code, $user_id) {
  910. return count(self::get_list_of_virtual_courses_for_specific_user_and_real_course($user_id, $real_course_code)) > 0;
  911. }
  912. /**
  913. * This function returns the course code of the real course
  914. * to which a virtual course is linked.
  915. *
  916. * @param the course code of the virtual course
  917. * @return the course code of the real course
  918. */
  919. public static function get_target_of_linked_course($virtual_course_code) {
  920. //get info about the virtual course
  921. $result = Database::fetch_array(Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  922. WHERE code = '".Database::escape_string($virtual_course_code)."'"));
  923. return $result['target_course_code'];
  924. }
  925. /*
  926. USER FUNCTIONS
  927. */
  928. /**
  929. * Check if user is subscribed inside a course
  930. * @param int User id
  931. * @param string Course code, if this parameter is null, it'll check for all courses
  932. * @param bool True for checking inside sessions too, by default is not checked
  933. * @return bool true if the user is registered in the course, false otherwise
  934. */
  935. public static function is_user_subscribed_in_course($user_id, $course_code = null, $in_a_session = false, $session_id = null) {
  936. $user_id = intval($user_id);
  937. if (empty($session_id)) {
  938. $session_id = api_get_session_id();
  939. } else {
  940. $session_id = intval($session_id);
  941. }
  942. $condition_course = '';
  943. if (isset($course_code)) {
  944. $course_code = Database::escape_string($course_code);
  945. $condition_course = ' AND course_code = "'.$course_code.'" ';
  946. }
  947. $sql = "SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  948. WHERE user_id = $user_id AND relation_type<>".COURSE_RELATION_TYPE_RRHH." $condition_course ";
  949. $result = Database::fetch_array(Database::query($sql));
  950. if (!empty($result)) {
  951. return true; // The user has been registered in this course.
  952. }
  953. if (!$in_a_session) {
  954. return false; // The user has not been registered in this course.
  955. }
  956. if (Database::num_rows(Database::query('SELECT 1 FROM '.Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER).
  957. ' WHERE id_user = '.$user_id.' '.$condition_course.' ')) > 0) {
  958. return true;
  959. }
  960. if (Database::num_rows(Database::query('SELECT 1 FROM '.Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER).
  961. ' WHERE id_user = '.$user_id.' AND status=2 '.$condition_course.' ')) > 0) {
  962. return true;
  963. }
  964. if (Database::num_rows(Database::query('SELECT 1 FROM '.Database::get_main_table(TABLE_MAIN_SESSION).
  965. ' WHERE id='.$session_id.' AND id_coach='.$user_id)) > 0) {
  966. return true;
  967. }
  968. return false;
  969. }
  970. /**
  971. * Is the user a teacher in the given course?
  972. *
  973. * @param $user_id, the id (int) of the user
  974. * @param $course_code, the course code
  975. *
  976. * @return true if the user is a teacher in the course, false otherwise
  977. */
  978. public static function is_course_teacher($user_id, $course_code) {
  979. if ($user_id != strval(intval($user_id))) {
  980. return false;
  981. }
  982. $sql_result = Database::query('SELECT status FROM '.Database::get_main_table(TABLE_MAIN_COURSE_USER).
  983. ' WHERE course_code="'.Database::escape_string($course_code).'" and user_id="'.$user_id.'"');
  984. if (Database::num_rows($sql_result) > 0) {
  985. return Database::result($sql_result, 0, 'status') == 1;
  986. }
  987. return false;
  988. }
  989. /**
  990. * Is the user subscribed in the real course or linked courses?
  991. *
  992. * @param int the id of the user
  993. * @param array info about the course (comes from course table, see database lib)
  994. *
  995. * @return true if the user is registered in the real course or linked courses, false otherwise
  996. */
  997. public static function is_user_subscribed_in_real_or_linked_course ($user_id, $course_code, $session_id = '') {
  998. if ($user_id != strval(intval($user_id))) {
  999. return false;
  1000. }
  1001. $course_code = Database::escape_string($course_code);
  1002. if ($session_id == '') {
  1003. $result = Database::fetch_array(Database::query("SELECT *
  1004. FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." course
  1005. LEFT JOIN ".Database::get_main_table(TABLE_MAIN_COURSE_USER)." course_user
  1006. ON course.code = course_user.course_code
  1007. WHERE course_user.user_id = '$user_id' AND course_user.relation_type<>".COURSE_RELATION_TYPE_RRHH." AND ( course.code = '$course_code' OR target_course_code = '$course_code')"));
  1008. return !empty($result);
  1009. }
  1010. // From here we trust session id.
  1011. // Is he/she subscribed to the session's course?
  1012. // A user?
  1013. if (Database::num_rows(Database::query("SELECT id_user
  1014. FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  1015. WHERE id_session='".$_SESSION['id_session']."'
  1016. AND id_user='$user_id'"))) {
  1017. return true;
  1018. }
  1019. // A course coach?
  1020. if (Database::num_rows(Database::query("SELECT id_user
  1021. FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  1022. WHERE id_session='".$_SESSION['id_session']."'
  1023. AND id_user = '$user_id' AND status = 2
  1024. AND course_code='$course_code'"))) {
  1025. return true;
  1026. }
  1027. // A session coach?
  1028. if (Database::num_rows(Database::query("SELECT id_coach
  1029. FROM ".Database::get_main_table(TABLE_MAIN_SESSION)." AS session
  1030. WHERE session.id='".$_SESSION['id_session']."'
  1031. AND id_coach='$user_id'"))) {
  1032. return true;
  1033. }
  1034. return false;
  1035. }
  1036. /**
  1037. * Return user info array of all users registered in the specified real or virtual course
  1038. * This only returns the users that are registered in this actual course, not linked courses.
  1039. *
  1040. * @param string $course_code the code of the course
  1041. * @param boolean $with_session determines if the course is used in a session or not
  1042. * @param integer $session_id the id of the session
  1043. * @param string $limit the LIMIT statement of the sql statement
  1044. * @param string $order_by the field to order the users by. Valid values are 'lastname', 'firstname', 'username', 'email', 'official_code' OR a part of a SQL statement that starts with ORDER BY ...
  1045. * @param int if using the session_id: 0 or 2 (student, coach), if using session_id = 0 STUDENT or COURSEMANAGER
  1046. * @return array
  1047. */
  1048. public static function get_user_list_from_course_code($course_code = null, $session_id = 0, $limit = null, $order_by = null, $filter_by_status = null, $return_count = null, $add_reports = false, $resumed_report = false, $extra_field = null) {
  1049. // variable initialisation
  1050. $session_id = intval($session_id);
  1051. $course_code = Database::escape_string($course_code);
  1052. $where = array();
  1053. // if the $order_by does not contain 'ORDER BY' we have to check if it is a valid field that can be sorted on
  1054. if (!strstr($order_by,'ORDER BY')) {
  1055. //if (!empty($order_by) AND in_array($order_by, array('lastname', 'firstname', 'username', 'email', 'official_code'))) {
  1056. if (!empty($order_by)) {
  1057. $order_by = 'ORDER BY '.$order_by;
  1058. } else {
  1059. $order_by = '';
  1060. }
  1061. }
  1062. $filter_by_status_condition = null;
  1063. if (!empty($session_id)) {
  1064. $sql = 'SELECT DISTINCT user.user_id, session_course_user.status as status_session, user.* ';
  1065. $sql .= ' FROM '.Database::get_main_table(TABLE_MAIN_USER).' as user ';
  1066. $sql .= ' LEFT JOIN '.Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER).' as session_course_user
  1067. ON user.user_id = session_course_user.id_user
  1068. AND session_course_user.course_code="'.$course_code.'"
  1069. AND session_course_user.id_session = '.$session_id;
  1070. $where[] = ' session_course_user.course_code IS NOT NULL ';
  1071. // 2 = coach
  1072. // 0 = student
  1073. if (isset($filter_by_status)) {
  1074. $filter_by_status = intval($filter_by_status);
  1075. $filter_by_status_condition = " session_course_user.status = $filter_by_status AND ";
  1076. }
  1077. } else {
  1078. if ($return_count) {
  1079. $sql = " SELECT COUNT(*) as count";
  1080. if ($resumed_report) {
  1081. //$sql = " SELECT count(field_id) ";
  1082. }
  1083. } else {
  1084. if (empty($course_code)) {
  1085. $sql = 'SELECT DISTINCT course.title, course.code, course_rel_user.status as status_rel, user.user_id, course_rel_user.role, course_rel_user.tutor_id, user.* ';
  1086. } else {
  1087. $sql = 'SELECT DISTINCT course_rel_user.status as status_rel, user.user_id, course_rel_user.role, course_rel_user.tutor_id, user.* ';
  1088. }
  1089. }
  1090. $sql .= ' FROM '.Database::get_main_table(TABLE_MAIN_USER).' as user ';
  1091. $sql .= ' LEFT JOIN '.Database::get_main_table(TABLE_MAIN_COURSE_USER).' as course_rel_user
  1092. ON user.user_id = course_rel_user.user_id AND
  1093. course_rel_user.relation_type <> '.COURSE_RELATION_TYPE_RRHH.' ';
  1094. if (!empty($course_code)) {
  1095. $sql .= ' AND course_rel_user.course_code="'.$course_code.'"';
  1096. } else {
  1097. $course_table = Database::get_main_table(TABLE_MAIN_COURSE);
  1098. $sql .= " INNER JOIN $course_table course ON course_rel_user.course_code = course.code ";
  1099. }
  1100. $where[] = ' course_rel_user.course_code IS NOT NULL ';
  1101. if (isset($filter_by_status) && $filter_by_status != '') {
  1102. $filter_by_status = intval($filter_by_status);
  1103. $filter_by_status_condition = " course_rel_user.status = $filter_by_status AND ";
  1104. }
  1105. }
  1106. $multiple_access_url = api_get_multiple_access_url();
  1107. if ($multiple_access_url) {
  1108. $sql .= ' LEFT JOIN '.Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER).' au ON (au.user_id = user.user_id) ';
  1109. }
  1110. if ($return_count && $resumed_report) {
  1111. $extra_field_info = UserManager::get_extra_field_information_by_name($extra_field);
  1112. $sql .= ' LEFT JOIN '.Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES).' as ufv ON (user.user_id = ufv.user_id AND (field_id = '.$extra_field_info['id'].' OR field_id IS NULL ) )';
  1113. }
  1114. $sql .= ' WHERE '.$filter_by_status_condition.' '.implode(' OR ', $where);
  1115. if ($multiple_access_url) {
  1116. $current_access_url_id = api_get_current_access_url_id();
  1117. $sql .= " AND (access_url_id = $current_access_url_id ) ";
  1118. }
  1119. if ($return_count && $resumed_report) {
  1120. $sql .= ' AND field_id IS NOT NULL GROUP BY field_value ';
  1121. }
  1122. $sql .= ' '.$order_by.' '.$limit;
  1123. $rs = Database::query($sql);
  1124. $users = array();
  1125. if ($add_reports) {
  1126. $extra_fields = UserManager::get_extra_fields(0, 100, null, null, true, true);
  1127. }
  1128. $counter = 1;
  1129. $count_rows = Database::num_rows($rs);
  1130. if ($return_count && $resumed_report) {
  1131. return $count_rows;
  1132. }
  1133. $table_user_field_value = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  1134. if ($count_rows) {
  1135. while ($user = Database::fetch_array($rs)) {
  1136. $report_info = array();
  1137. if ($return_count) {
  1138. return $user['count'];
  1139. }
  1140. $user_info = $user;
  1141. $user_info['status'] = $user['status'];
  1142. if (isset($user['role'])) {
  1143. $user_info['role'] = $user['role'];
  1144. }
  1145. if (isset($user['tutor_id'])) {
  1146. $user_info['tutor_id'] = $user['tutor_id'];
  1147. }
  1148. if (!empty($session_id)) {
  1149. $user_info['status_session'] = $user['status_session'];
  1150. }
  1151. if ($add_reports) {
  1152. $course_code = $user['code'];
  1153. if ($resumed_report) {
  1154. foreach ($extra_fields as $extra) {
  1155. if ($extra['1'] == $extra_field) {
  1156. $user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']);
  1157. break;
  1158. }
  1159. }
  1160. if (empty($user_data[$extra['1']])) {
  1161. $row_key = '-1';
  1162. $name = '-';
  1163. } else {
  1164. $row_key = $user_data[$extra['1']];
  1165. $name = $user_data[$extra['1']];
  1166. }
  1167. $users[$row_key]['extra_'.$extra['1']] = $name;
  1168. $users[$row_key]['training_hours'] += Tracking::get_time_spent_on_the_course($user['user_id'], $course_code, 0);
  1169. $users[$row_key]['count_users'] += $counter;
  1170. $registered_users_with_extra_field = 0;
  1171. if (!empty($name) && $name != '-') {
  1172. $name = Database::escape_string($name);
  1173. $sql = "SELECT count(user_id) as count FROM $table_user_field_value WHERE field_value = '$name'";
  1174. $result_count = Database::query($sql);
  1175. if (Database::num_rows($result_count)) {
  1176. $row_count = Database::fetch_array($result_count);
  1177. $registered_users_with_extra_field = $row_count['count'];
  1178. }
  1179. }
  1180. $users[$row_key]['count_users_registered'] = $registered_users_with_extra_field;
  1181. $users[$row_key]['average_hours_per_user'] = $users[$row_key]['training_hours'] / $users[$row_key]['count_users'];
  1182. $category = Category :: load (null, null, $course_code);
  1183. if (!isset($users[$row_key]['count_certificates'])) {
  1184. $users[$row_key]['count_certificates'] = 0;
  1185. }
  1186. if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) {
  1187. $users[$row_key]['count_certificates']++;
  1188. }
  1189. } else {
  1190. $report_info['course'] = $user['title'];
  1191. $report_info['user'] = api_get_person_name($user['firstname'], $user['lastname']);
  1192. $report_info['time'] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user['user_id'], $course_code, 0));
  1193. $category = Category :: load (null, null, $course_code);
  1194. $report_info['certificate'] = Display::label(get_lang('No'));
  1195. if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) {
  1196. $report_info['certificate'] = Display::label(get_lang('Yes'), 'success');
  1197. }
  1198. $report_info['score'] = Tracking::get_avg_student_score($user['user_id'], $course_code, array(), 0);
  1199. $report_info['progress'] = Tracking::get_avg_student_progress($user['user_id'], $course_code, array(), 0)."%";
  1200. foreach ($extra_fields as $extra) {
  1201. $user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']);
  1202. $report_info[$extra['1']] = $user_data[$extra['1']];
  1203. }
  1204. $users[] = $report_info;
  1205. }
  1206. } else {
  1207. $users[$user['user_id']] = $user_info;
  1208. }
  1209. }
  1210. $counter++;
  1211. }
  1212. if ($add_reports) {
  1213. if ($resumed_report) {
  1214. //var_dump($counter);
  1215. }
  1216. }
  1217. //var_dump($users);
  1218. return $users;
  1219. }
  1220. static function get_count_user_list_from_course_code($resumed_report = false, $extra_field = null) {
  1221. return self::get_user_list_from_course_code(null, 0, null, null, null, true, false, $resumed_report, $extra_field);
  1222. }
  1223. /**
  1224. * Gets subscribed users in a course or in a course/session
  1225. *
  1226. * @param string $course_code
  1227. * @param int $session_id
  1228. * @return int
  1229. */
  1230. public static function get_users_count_in_course($course_code, $session_id = 0) {
  1231. // variable initialisation
  1232. $session_id = intval($session_id);
  1233. $course_code = Database::escape_string($course_code);
  1234. $sql .= 'SELECT DISTINCT count(*) as count FROM '.Database::get_main_table(TABLE_MAIN_USER).' as user ';
  1235. $where = array();
  1236. if (!empty($session_id)) {
  1237. $sql .= ' LEFT JOIN '.Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER).' as session_course_user
  1238. ON user.user_id = session_course_user.id_user
  1239. AND session_course_user.course_code = "'.$course_code.'"
  1240. AND session_course_user.id_session = '.$session_id.'
  1241. INNER JOIN '.Database::get_main_table(TABLE_MAIN_SESSION_USER).' as su
  1242. ON ( su.id_session = session_course_user.id_session AND
  1243. user.user_id = su.id_user AND
  1244. su.moved_to = 0 AND
  1245. su.moved_status <> '.SessionManager::SESSION_CHANGE_USER_REASON_ENROLLMENT_ANNULATION.'
  1246. )';
  1247. $where[] = ' session_course_user.course_code IS NOT NULL ';
  1248. } else {
  1249. $sql .= ' LEFT JOIN '.Database::get_main_table(TABLE_MAIN_COURSE_USER).' as course_rel_user
  1250. ON user.user_id = course_rel_user.user_id AND course_rel_user.relation_type<>'.COURSE_RELATION_TYPE_RRHH.'
  1251. AND course_rel_user.course_code="'.$course_code.'"';
  1252. $where[] = ' course_rel_user.course_code IS NOT NULL ';
  1253. }
  1254. $multiple_access_url = api_get_multiple_access_url();
  1255. if ($multiple_access_url) {
  1256. $sql .= ' LEFT JOIN '.Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER).' au ON (au.user_id = user.user_id) ';
  1257. }
  1258. $sql .= ' WHERE '.implode(' OR ', $where);
  1259. if ($multiple_access_url) {
  1260. $current_access_url_id = api_get_current_access_url_id();
  1261. $sql .= " AND (access_url_id = $current_access_url_id ) ";
  1262. }
  1263. $rs = Database::query($sql);
  1264. $count = 0;
  1265. if (Database::num_rows($rs)) {
  1266. $user = Database::fetch_array($rs);
  1267. $count = $user['count'];
  1268. }
  1269. return $count;
  1270. }
  1271. /**
  1272. * Get a list of coaches of a course and a session
  1273. * @param string Course code
  1274. * @param int Session ID
  1275. * @return array List of users
  1276. */
  1277. public static function get_coach_list_from_course_code($course_code, $session_id) {
  1278. if ($session_id != strval(intval($session_id))) {
  1279. return array();
  1280. }
  1281. $course_code = Database::escape_string($course_code);
  1282. $users = array();
  1283. // We get the coach for the given course in a given session.
  1284. $rs = Database::query('SELECT id_user FROM '.Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER).
  1285. ' WHERE id_session="'.$session_id.'" AND course_code="'.$course_code.'" AND status = 2');
  1286. while ($user = Database::fetch_array($rs)) {
  1287. $user_info = Database::get_user_info_from_id($user['id_user']);
  1288. $user_info['status'] = $user['status'];
  1289. $user_info['role'] = $user['role'];
  1290. $user_info['tutor_id'] = $user['tutor_id'];
  1291. $user_info['email'] = $user['email'];
  1292. $users[$user['id_user']] = $user_info;
  1293. }
  1294. // We get the session coach.
  1295. $rs = Database::query('SELECT id_coach FROM '.Database::get_main_table(TABLE_MAIN_SESSION).' WHERE id="'.$session_id.'"');
  1296. $session_id_coach = Database::result($rs, 0, 'id_coach');
  1297. $user_info = Database::get_user_info_from_id($session_id_coach);
  1298. $user_info['status'] = $user['status'];
  1299. $user_info['role'] = $user['role'];
  1300. $user_info['tutor_id'] = $user['tutor_id'];
  1301. $user_info['email'] = $user['email'];
  1302. $users[$session_id_coach] = $user_info;
  1303. return $users;
  1304. }
  1305. /**
  1306. * Return user info array of all users registered in the specified real or virtual course
  1307. * This only returns the users that are registered in this actual course, not linked courses.
  1308. *
  1309. * @param string $course_code
  1310. * @param boolean $full list to true if we want sessions students too
  1311. * @return array with user id
  1312. */
  1313. public static function get_student_list_from_course_code($course_code, $with_session = false, $session_id = 0, $group_id = 0) {
  1314. $session_id = intval($session_id);
  1315. $course_code = Database::escape_string($course_code);
  1316. $students = array();
  1317. if ($session_id == 0) {
  1318. if (empty($group_id)) {
  1319. // students directly subscribed to the course
  1320. $sql = "SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  1321. WHERE course_code = '$course_code' AND status = ".STUDENT;
  1322. $rs = Database::query($sql);
  1323. while ($student = Database::fetch_array($rs)) {
  1324. $students[$student['user_id']] = $student;
  1325. }
  1326. } else {
  1327. $course_info = api_get_course_info($course_code);
  1328. $students = GroupManager::get_users($group_id, false, $course_info['real_id']);
  1329. $students = array_flip($students);
  1330. }
  1331. }
  1332. // students subscribed to the course through a session
  1333. if ($with_session) {
  1334. $sql_query = "SELECT * FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  1335. WHERE course_code = '$course_code' AND status <> 2";
  1336. if ($session_id != 0) {
  1337. $sql_query .= ' AND id_session = '.$session_id;
  1338. }
  1339. $rs = Database::query($sql_query);
  1340. while($student = Database::fetch_array($rs)) {
  1341. $students[$student['id_user']] = $student;
  1342. }
  1343. }
  1344. return $students;
  1345. }
  1346. /**
  1347. * Return user info array of all teacher-users registered in the specified real or virtual course
  1348. * This only returns the users that are registered in this actual course, not linked courses.
  1349. *
  1350. * @param string $course_code
  1351. * @return array with user id
  1352. */
  1353. public static function get_teacher_list_from_course_code($course_code) {
  1354. $course_code = Database::escape_string($course_code);
  1355. $teachers = array();
  1356. $sql = "SELECT DISTINCT u.user_id, u.lastname, u.firstname, u.email, u.username, u.status
  1357. FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)." cu INNER JOIN ".Database::get_main_table(TABLE_MAIN_USER)." u
  1358. ON (cu.user_id = u.user_id)
  1359. WHERE cu.course_code = '$course_code' AND
  1360. cu.status = 1 ";
  1361. $rs = Database::query($sql);
  1362. while ($teacher = Database::fetch_array($rs)) {
  1363. $teachers[$teacher['user_id']] = $teacher;
  1364. }
  1365. return $teachers;
  1366. }
  1367. public static function get_teacher_list_from_course_code_to_string($course_code, $separator = self::USER_SEPARATOR, $add_link_to_profile = false) {
  1368. $teacher_list = self::get_teacher_list_from_course_code($course_code);
  1369. $teacher_string = '';
  1370. $list = array();
  1371. if (!empty($teacher_list)) {
  1372. foreach($teacher_list as $teacher) {
  1373. $teacher_name = api_get_person_name($teacher['firstname'], $teacher['lastname']);
  1374. if ($add_link_to_profile) {
  1375. $url = api_get_path(WEB_AJAX_PATH).'user_manager.ajax.php?a=get_user_popup&resizable=0&height=300&user_id='.$teacher['user_id'];
  1376. $teacher_name = Display::url($teacher_name, $url, array('class' => 'ajax'));
  1377. }
  1378. $list[]= $teacher_name;
  1379. }
  1380. if (!empty($list)) {
  1381. $teacher_string = array_to_string($list, $separator);
  1382. }
  1383. }
  1384. return $teacher_string;
  1385. }
  1386. /**
  1387. * This function returns information about coachs from a course in session
  1388. * @param int - optional, session id
  1389. * @param string - optional, course code
  1390. * @return array - array containing user_id, lastname, firstname, username
  1391. *
  1392. */
  1393. public static function get_coachs_from_course($session_id=0, $course_code='') {
  1394. if (!empty($session_id)) {
  1395. $session_id = intval($session_id);
  1396. } else {
  1397. $session_id = api_get_session_id();
  1398. }
  1399. if (!empty($course_code)) {
  1400. $course_code = Database::escape_string($course_code);
  1401. } else {
  1402. $course_code = api_get_course_id();
  1403. }
  1404. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  1405. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1406. $coaches = array();
  1407. $sql = "SELECT DISTINCT u.user_id, u.lastname, u.firstname, u.username
  1408. FROM $tbl_user u, $tbl_session_course_user scu
  1409. WHERE u.user_id = scu.id_user AND scu.id_session = '$session_id' AND scu.course_code = '$course_code' AND scu.status = 2";
  1410. $rs = Database::query($sql);
  1411. if (Database::num_rows($rs) > 0) {
  1412. while ($row = Database::fetch_array($rs)) {
  1413. $coaches[] = $row;
  1414. }
  1415. return $coaches;
  1416. } else {
  1417. return false;
  1418. }
  1419. }
  1420. public static function get_coachs_from_course_to_string($session_id = 0, $course_code = null, $separator = self::USER_SEPARATOR, $add_link_to_profile = false) {
  1421. $coachs_course = self::get_coachs_from_course($session_id, $course_code);
  1422. $course_coachs = array();
  1423. if (is_array($coachs_course)) {
  1424. foreach ($coachs_course as $coach_course) {
  1425. $coach_name = api_get_person_name($coach_course['firstname'], $coach_course['lastname']);
  1426. if ($add_link_to_profile) {
  1427. $url = api_get_path(WEB_AJAX_PATH).'user_manager.ajax.php?a=get_user_popup&resizable=0&height=300&user_id='.$coach_course['user_id'];
  1428. $coach_name = Display::url($coach_name, $url, array('class' => 'ajax'));
  1429. }
  1430. $course_coachs[] = $coach_name;
  1431. }
  1432. }
  1433. $coaches_to_string = null;
  1434. if (is_array($course_coachs) && count($course_coachs)> 0 ) {
  1435. $coaches_to_string = array_to_string($course_coachs, $separator);
  1436. }
  1437. return $coaches_to_string;
  1438. }
  1439. public static function get_coach_list_from_course_code_to_string($course_code, $session_id) {
  1440. $tutor_data = '';
  1441. if ($session_id != 0) {
  1442. $coaches = self::get_email_of_tutor_to_session($session_id, $course_code);
  1443. $coach_list = array();
  1444. foreach ($coaches as $coach) {
  1445. $coach_list[] = $coach['complete_name'];
  1446. }
  1447. if (!empty($coach_list)) {
  1448. $tutor_data = implode(self::USER_SEPARATOR, $coach_list);
  1449. }
  1450. }
  1451. return $tutor_data;
  1452. }
  1453. /**
  1454. * Return user info array of all users registered in the specified course
  1455. * this includes the users of the course itsel and the users of all linked courses.
  1456. *
  1457. * @param array $course_info
  1458. * @return array with user info
  1459. */
  1460. public static function get_real_and_linked_user_list($course_code, $with_sessions = true, $session_id = 0, $order_by = null) {
  1461. $complete_user_list = array();
  1462. //get users from real course
  1463. $user_list = self::get_user_list_from_course_code($course_code, $session_id, null, $order_by);
  1464. foreach ($user_list as $this_user) {
  1465. $complete_user_list[] = $this_user;
  1466. }
  1467. return $complete_user_list;
  1468. }
  1469. /**
  1470. * Return an array of arrays, listing course info of all courses in the list
  1471. * linked to the real course $real_course_code, to which the user $user_id is subscribed.
  1472. *
  1473. * @param $user_id, the id (int) of the user
  1474. * @param $real_course_code, the id (char) of the real course
  1475. *
  1476. * @return array of course info arrays
  1477. */
  1478. public static function get_list_of_virtual_courses_for_specific_user_and_real_course($user_id, $course_code) {
  1479. $result_array = array();
  1480. if ($user_id != strval(intval($user_id))) {
  1481. return $result_array;
  1482. }
  1483. $course_code = Database::escape_string($course_code);
  1484. $sql = "SELECT *
  1485. FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." course
  1486. LEFT JOIN ".Database::get_main_table(TABLE_MAIN_COURSE_USER)." course_user
  1487. ON course.code = course_user.course_code
  1488. WHERE course.target_course_code = '$course_code' AND course_user.user_id = '$user_id' AND course_user.relation_type<>".COURSE_RELATION_TYPE_RRHH." ";
  1489. $sql_result = Database::query($sql);
  1490. while ($result = Database::fetch_array($sql_result)) {
  1491. $result_array[] = $result;
  1492. }
  1493. return $result_array;
  1494. }
  1495. /*
  1496. GROUP FUNCTIONS
  1497. */
  1498. /**
  1499. * Get the list of groups from the course
  1500. * @param string Course code
  1501. * @param int Session ID (optional)
  1502. * @param boolean get empty groups (optional)
  1503. * @return array List of groups info
  1504. */
  1505. public static function get_group_list_of_course($course_code, $session_id = 0, $in_get_empty_group = 0) {
  1506. $course_info = Database::get_course_info($course_code);
  1507. $course_id = $course_info['real_id'];
  1508. $group_list = array();
  1509. $session_id != 0 ? $session_condition = ' WHERE g.session_id IN(1,'.intval($session_id).')' : $session_condition = ' WHERE g.session_id = 0';
  1510. if ($in_get_empty_group == 0) {
  1511. // get only groups that are not empty
  1512. $sql = "SELECT DISTINCT g.id, g.name
  1513. FROM ".Database::get_course_table(TABLE_GROUP)." AS g
  1514. INNER JOIN ".Database::get_course_table(TABLE_GROUP_USER)." gu
  1515. ON (g.id = gu.group_id AND g.c_id = $course_id AND gu.c_id = $course_id)
  1516. $session_condition
  1517. ORDER BY g.name";
  1518. }
  1519. else {
  1520. // get all groups even if they are empty
  1521. $sql = "SELECT g.id, g.name
  1522. FROM ".Database::get_course_table(TABLE_GROUP)." AS g
  1523. $session_condition
  1524. AND c_id = $course_id";
  1525. }
  1526. $result = Database::query($sql);
  1527. while ($group_data = Database::fetch_array($result)) {
  1528. $group_data['userNb'] = GroupManager::number_of_students($group_data['id'], $course_id);
  1529. $group_list[$group_data['id']] = $group_data;
  1530. }
  1531. return $group_list;
  1532. }
  1533. /**
  1534. * Checks all parameters needed to create a virtual course.
  1535. * If they are all set, the virtual course creation procedure is called.
  1536. *
  1537. * Call this function instead of create_virtual_course
  1538. * @param string Course code
  1539. * @param string Course title
  1540. * @param string Wanted course code
  1541. * @param string Course language
  1542. * @param string Course category
  1543. * @return bool True on success, false on error
  1544. */
  1545. public static function attempt_create_virtual_course($real_course_code, $course_title, $wanted_course_code, $course_language, $course_category) {
  1546. //better: create parameter list, check the entire list, when false display errormessage
  1547. self::check_parameter_or_fail($real_course_code, 'Unspecified parameter: real course id.');
  1548. self::check_parameter_or_fail($course_title, 'Unspecified parameter: course title.');
  1549. self::check_parameter_or_fail($wanted_course_code, 'Unspecified parameter: wanted course code.');
  1550. self::check_parameter_or_fail($course_language, 'Unspecified parameter: course language.');
  1551. self::check_parameter_or_fail($course_category, 'Unspecified parameter: course category.');
  1552. return self::create_virtual_course($real_course_code, $course_title, $wanted_course_code, $course_language, $course_category);
  1553. }
  1554. /**
  1555. * This function creates a virtual course.
  1556. * It assumes all parameters have been checked and are not empty.
  1557. * It checks wether a course with the $wanted_course_code already exists.
  1558. *
  1559. * Users of this library should consider this function private,
  1560. * please call attempt_create_virtual_course instead of this one.
  1561. *
  1562. * note: The virtual course 'owner' id (the first course admin) is set to the CURRENT user id.
  1563. * @param string Course code
  1564. * @param string Course title
  1565. * @param string Wanted course code
  1566. * @param string Course language
  1567. * @param string Course category
  1568. * @return true if the course creation succeeded, false otherwise
  1569. * @deprecated this is deprecated?
  1570. * @todo research: expiration date of a course
  1571. */
  1572. public static function create_virtual_course($real_course_code, $course_title, $wanted_course_code, $course_language, $course_category) {
  1573. global $firstExpirationDelay;
  1574. $user_id = api_get_user_id();
  1575. $real_course_info = Database::get_course_info($real_course_code);
  1576. $real_course_code = $real_course_info['system_code'];
  1577. //check: virtual course creation fails if another course has the same
  1578. //code, real or fake.
  1579. if (self::course_code_exists($wanted_course_code)) {
  1580. Display::display_error_message($wanted_course_code.' - '.get_lang('CourseCodeAlreadyExists'));
  1581. return false;
  1582. }
  1583. //add data to course table, course_rel_user
  1584. $course_sys_code = $wanted_course_code;
  1585. $course_screen_code = $wanted_course_code;
  1586. $course_repository = $real_course_info['directory'];
  1587. $course_db_name = $real_course_info['db_name'];
  1588. $responsible_teacher = $real_course_info['tutor_name'];
  1589. $faculty_shortname = $course_category;
  1590. // $course_title = $course_title;
  1591. // $course_language = $course_language;
  1592. $teacher_id = $user_id;
  1593. //HACK ----------------------------------------------------------------
  1594. $expiration_date = time() + $firstExpirationDelay;
  1595. //END HACK ------------------------------------------------------------
  1596. self::register_course($course_sys_code, $course_screen_code, $course_repository, $course_db_name, $responsible_teacher, $faculty_shortname, $course_title, $course_language, $teacher_id, $expiration_date);
  1597. //above was the normal course creation table update call,
  1598. //now one more thing: fill in the target_course_code field
  1599. Database::query("UPDATE ".Database::get_main_table(TABLE_MAIN_COURSE)." SET target_course_code = '$real_course_code'
  1600. WHERE code = '".Database::escape_string($course_sys_code)."' LIMIT 1 ");
  1601. return true;
  1602. }
  1603. /**
  1604. * Delete a course
  1605. * This function deletes a whole course-area from the platform. When the
  1606. * given course is a virtual course, the database and directory will not be
  1607. * deleted.
  1608. * When the given course is a real course, also all virtual courses refering
  1609. * to the given course will be deleted.
  1610. * Considering the fact that we remove all traces of the course in the main
  1611. * database, it makes sense to remove all tracking as well (if stats databases exist)
  1612. * so that a new course created with this code would not use the remains of an older
  1613. * course.
  1614. *
  1615. * @param string The code of the course to delete
  1616. * @todo When deleting a virtual course: unsubscribe users from that virtual
  1617. * course from the groups in the real course if they are not subscribed in
  1618. * that real course.
  1619. * @todo Remove globals
  1620. */
  1621. public static function delete_course($code) {
  1622. $table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  1623. $table_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1624. $table_course_class = Database::get_main_table(TABLE_MAIN_COURSE_CLASS);
  1625. $table_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  1626. $table_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1627. $table_course_survey = Database::get_main_table(TABLE_MAIN_SHARED_SURVEY);
  1628. $table_course_survey_question = Database::get_main_table(TABLE_MAIN_SHARED_SURVEY_QUESTION);
  1629. $table_course_survey_question_option= Database::get_main_table(TABLE_MAIN_SHARED_SURVEY_QUESTION_OPTION);
  1630. $table_stats_hotpots = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
  1631. $table_stats_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1632. $table_stats_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  1633. $table_stats_access = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
  1634. $table_stats_lastaccess = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
  1635. $table_stats_course_access = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  1636. $table_stats_online = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ONLINE);
  1637. $table_stats_default = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
  1638. $table_stats_downloads = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
  1639. $table_stats_links = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LINKS);
  1640. $table_stats_uploads = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_UPLOADS);
  1641. $code = Database::escape_string($code);
  1642. // Unsubscribe all classes from the course
  1643. $sql = "DELETE FROM $table_course_class WHERE course_code='".$code."'";
  1644. Database::query($sql);
  1645. // Unsubscribe all users from the course
  1646. $sql = "DELETE FROM $table_course_user WHERE course_code='".$code."'";
  1647. Database::query($sql);
  1648. // Delete the course from the sessions tables
  1649. $sql = "DELETE FROM $table_session_course WHERE course_code='".$code."'";
  1650. Database::query($sql);
  1651. $sql = "DELETE FROM $table_session_course_user WHERE course_code='".$code."'";
  1652. Database::query($sql);
  1653. $sql = 'SELECT survey_id FROM '.$table_course_survey.' WHERE course_code="'.$code.'"';
  1654. $result_surveys = Database::query($sql);
  1655. while($surveys = Database::fetch_array($result_surveys)) {
  1656. $survey_id = $surveys[0];
  1657. $sql = 'DELETE FROM '.$table_course_survey_question.' WHERE survey_id="'.$survey_id.'"';
  1658. Database::query($sql);
  1659. $sql = 'DELETE FROM '.$table_course_survey_question_option.' WHERE survey_id="'.$survey_id.'"';
  1660. Database::query($sql);
  1661. $sql = 'DELETE FROM '.$table_course_survey.' WHERE survey_id="'.$survey_id.'"';
  1662. Database::query($sql);
  1663. }
  1664. // Delete the course from the stats tables
  1665. $sql = "DELETE FROM $table_stats_hotpots WHERE exe_cours_id = '".$code."'";
  1666. Database::query($sql);
  1667. $sql = "DELETE FROM $table_stats_attempt WHERE course_code = '".$code."'";
  1668. Database::query($sql);
  1669. $sql = "DELETE FROM $table_stats_exercises WHERE exe_cours_id = '".$code."'";
  1670. Database::query($sql);
  1671. $sql = "DELETE FROM $table_stats_access WHERE access_cours_code = '".$code."'";
  1672. Database::query($sql);
  1673. $sql = "DELETE FROM $table_stats_lastaccess WHERE access_cours_code = '".$code."'";
  1674. Database::query($sql);
  1675. $sql = "DELETE FROM $table_stats_course_access WHERE course_code = '".$code."'";
  1676. Database::query($sql);
  1677. $sql = "DELETE FROM $table_stats_online WHERE course = '".$code."'";
  1678. Database::query($sql);
  1679. $sql = "DELETE FROM $table_stats_default WHERE default_cours_code = '".$code."'";
  1680. Database::query($sql);
  1681. $sql = "DELETE FROM $table_stats_downloads WHERE down_cours_id = '".$code."'";
  1682. Database::query($sql);
  1683. $sql = "DELETE FROM $table_stats_links WHERE links_cours_id = '".$code."'";
  1684. Database::query($sql);
  1685. $sql = "DELETE FROM $table_stats_uploads WHERE upload_cours_id = '".$code."'";
  1686. Database::query($sql);
  1687. if (api_is_multiple_url_enabled()) {
  1688. $url_id = 1;
  1689. if (api_get_current_access_url_id() != -1) {
  1690. $url_id = api_get_current_access_url_id();
  1691. }
  1692. UrlManager::delete_url_rel_course($code, $url_id);
  1693. }
  1694. // Delete the course from the database
  1695. $sql = "DELETE FROM $table_course WHERE code='".$code."'";
  1696. Database::query($sql);
  1697. // delete extra course fields
  1698. $t_cf = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  1699. $t_cfv = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  1700. $sql = "SELECT distinct field_id FROM $t_cfv WHERE course_code = '$code'";
  1701. $res_field_ids = @Database::query($sql);
  1702. while($row_field_id = Database::fetch_row($res_field_ids)){
  1703. $field_ids[] = $row_field_id[0];
  1704. }
  1705. //delete from table_course_field_value from a given course_code
  1706. $sql_course_field_value = "DELETE FROM $t_cfv WHERE course_code = '$code'";
  1707. @Database::query($sql_course_field_value);
  1708. $sql = "SELECT distinct field_id FROM $t_cfv";
  1709. $res_field_all_ids = @Database::query($sql);
  1710. while($row_field_all_id = Database::fetch_row($res_field_all_ids)){
  1711. $field_all_ids[] = $row_field_all_id[0];
  1712. }
  1713. if (is_array($field_ids) && count($field_ids) > 0) {
  1714. foreach ($field_ids as $field_id) {
  1715. // check if field id is used into table field value
  1716. if (is_array($field_all_ids)) {
  1717. if (in_array($field_id, $field_all_ids)) {
  1718. continue;
  1719. } else {
  1720. $sql_course_field = "DELETE FROM $t_cf WHERE id = '$field_id'";
  1721. Database::query($sql_course_field);
  1722. }
  1723. }
  1724. }
  1725. }
  1726. // Add event to system log
  1727. $user_id = api_get_user_id();
  1728. event_system(LOG_COURSE_DELETE, LOG_COURSE_CODE, $code, api_get_utc_datetime(), $user_id, $code);
  1729. }
  1730. /**
  1731. * Creates a file called mysql_dump.sql in the course folder
  1732. * @param $course_code The code of the course
  1733. * @todo Implementation for single database
  1734. */
  1735. public static function create_database_dump($course_code) {
  1736. global $_configuration;
  1737. if ($_configuration['single_database']) {
  1738. return;
  1739. }
  1740. $sql_dump = '';
  1741. $course_code = Database::escape_string($course_code);
  1742. $table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  1743. $sql = "SELECT * FROM $table_course WHERE code = '$course_code'";
  1744. $res = Database::query($sql);
  1745. $course = Database::fetch_array($res);
  1746. $course_tables = self::get_course_tables();
  1747. if (!empty($course['id'])) {
  1748. //Cleaning c_x tables
  1749. foreach($course_tables as $table) {
  1750. $table = Database::get_course_table($table);
  1751. $sql = "SELECT * FROM $table WHERE c_id = {$course['id']} ";
  1752. $res_table = Database::query($sql);
  1753. while ($row = Database::fetch_array($res_table, 'ASSOC')) {
  1754. $row_to_save = array();
  1755. foreach ($row as $key => $value) {
  1756. $row_to_save[$key] = $key."='".Database::escape_string($row[$key])."'";
  1757. }
  1758. $sql_dump .= "\nINSERT INTO $table SET ".implode(', ', $row_to_save).';';
  1759. }
  1760. }
  1761. }
  1762. if (is_dir(api_get_path(SYS_COURSE_PATH).$course['directory'])) {
  1763. $file_name = api_get_path(SYS_COURSE_PATH).$course['directory'].'/mysql_dump.sql';
  1764. $handle = fopen($file_name, 'a+');
  1765. if ($handle !== false) {
  1766. fwrite($handle, $sql_dump);
  1767. fclose($handle);
  1768. } else {
  1769. //TODO trigger exception in a try-catch
  1770. }
  1771. }
  1772. }
  1773. /**
  1774. * Sort courses for a specific user ??
  1775. * @param int User ID
  1776. * @param string Course code
  1777. * @return int Minimum course order
  1778. * @todo Review documentation
  1779. */
  1780. public static function userCourseSort($user_id, $course_code) {
  1781. if ($user_id != strval(intval($user_id))) {
  1782. return false;
  1783. }
  1784. $course_code = Database::escape_string($course_code);
  1785. $TABLECOURSE = Database::get_main_table(TABLE_MAIN_COURSE);
  1786. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1787. $course_title = Database::result(Database::query('SELECT title FROM '.$TABLECOURSE.' WHERE code="'.$course_code.'"'), 0, 0);
  1788. $sql = 'SELECT course.code as code, course.title as title, cu.sort as sort FROM '.$TABLECOURSUSER.' as cu, '.$TABLECOURSE.' as course
  1789. WHERE course.code = cu.course_code AND user_id = "'.$user_id.'" AND
  1790. cu.relation_type<>'.COURSE_RELATION_TYPE_RRHH.' AND
  1791. user_course_cat = 0
  1792. ORDER BY cu.sort';
  1793. $result = Database::query($sql);
  1794. $course_title_precedent = '';
  1795. $counter = 0;
  1796. $course_found = false;
  1797. $course_sort = 1;
  1798. if (Database::num_rows($result) > 0) {
  1799. while ($courses = Database::fetch_array($result)){
  1800. if ($course_title_precedent == '') {
  1801. $course_title_precedent = $courses['title'];
  1802. }
  1803. if (api_strcasecmp($course_title_precedent, $course_title) < 0) {
  1804. $course_found = true;
  1805. $course_sort = $courses['sort'];
  1806. if ($counter == 0) {
  1807. $sql = 'UPDATE '.$TABLECOURSUSER.' SET sort = sort+1 WHERE user_id= "'.$user_id.'" AND relation_type<>'.COURSE_RELATION_TYPE_RRHH.' AND user_course_cat="0" AND sort > "'.$course_sort.'"';
  1808. $course_sort++;
  1809. } else {
  1810. $sql = 'UPDATE '.$TABLECOURSUSER.' SET sort = sort+1 WHERE user_id= "'.$user_id.'" AND relation_type<>'.COURSE_RELATION_TYPE_RRHH.' AND user_course_cat="0" AND sort >= "'.$course_sort.'"';
  1811. }
  1812. Database::query($sql);
  1813. break;
  1814. } else {
  1815. $course_title_precedent = $courses['title'];
  1816. }
  1817. $counter++;
  1818. }
  1819. // We must register the course in the beginning of the list
  1820. if (!$course_found) {
  1821. $course_sort = Database::result(Database::query('SELECT min(sort) as min_sort FROM '.$TABLECOURSUSER.' WHERE user_id="'.$user_id.'" AND user_course_cat="0"'), 0, 0);
  1822. Database::query('UPDATE '.$TABLECOURSUSER.' SET sort = sort+1 WHERE user_id= "'.$user_id.'" AND user_course_cat="0"');
  1823. }
  1824. }
  1825. return $course_sort;
  1826. }
  1827. /**
  1828. * create recursively all categories as option of the select passed in paramater.
  1829. *
  1830. * @param object $select_element the quickform select where the options will be added
  1831. * @param string $category_selected_code the option value to select by default (used mainly for edition of courses)
  1832. * @param string $parent_code the parent category of the categories added (default=null for root category)
  1833. * @param string $padding the indent param (you shouldn't indicate something here)
  1834. */
  1835. public static function select_and_sort_categories($select_element, $category_selected_code = '', $parent_code = null , $padding = '') {
  1836. $sql = "SELECT code, name, auth_course_child, auth_cat_child
  1837. FROM ".Database::get_main_table(TABLE_MAIN_CATEGORY)."
  1838. WHERE parent_id ".(is_null($parent_code) ? "IS NULL" : "='".Database::escape_string($parent_code)."'")."
  1839. ORDER BY code";
  1840. $res = Database::query($sql);
  1841. while ($cat = Database::fetch_array($res)) {
  1842. $params = $cat['auth_course_child'] == 'TRUE' ? '' : 'disabled';
  1843. $params .= ($cat['code'] == $category_selected_code) ? ' selected' : '';
  1844. $select_element->addOption($padding.'('.$cat['code'].') '.$cat['name'], $cat['code'], $params);
  1845. if ($cat['auth_cat_child']) {
  1846. self::select_and_sort_categories($select_element, $category_selected_code, $cat['code'], $padding.' - ');
  1847. }
  1848. }
  1849. }
  1850. /**
  1851. * check if course exists
  1852. * @param string course_code
  1853. * @param string whether to accept virtual course codes or not
  1854. * @return true if exists, false else
  1855. */
  1856. public static function course_exists($course_code, $accept_virtual = false) {
  1857. if ($accept_virtual === true) {
  1858. $sql = 'SELECT 1 FROM '.Database::get_main_table(TABLE_MAIN_COURSE).' WHERE code="'.Database::escape_string($course_code).'" OR visual_code="'.Database::escape_string($course_code).'"';
  1859. } else {
  1860. $sql = 'SELECT 1 FROM '.Database::get_main_table(TABLE_MAIN_COURSE).' WHERE code="'.Database::escape_string($course_code).'"';
  1861. }
  1862. return Database::num_rows(Database::query($sql));
  1863. }
  1864. /**
  1865. * Send an email to tutor after the auth-suscription of a student in your course
  1866. * @author Carlos Vargas <carlos.vargas@dokeos.com>, Dokeos Latino
  1867. * @param int $user_id the id of the user
  1868. * @param string $course_code the course code
  1869. * @param string $send_to_tutor_also
  1870. * @return string we return the message that is displayed when the action is succesfull
  1871. */
  1872. public static function email_to_tutor($user_id, $course_code, $send_to_tutor_also = false) {
  1873. if ($user_id != strval(intval($user_id))) {
  1874. return false;
  1875. }
  1876. $course_code = Database::escape_string($course_code);
  1877. $student = Database::fetch_array(Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_USER)."
  1878. WHERE user_id='".$user_id."'"));
  1879. $information = self::get_course_information($course_code);
  1880. $name_course = $information['title'];
  1881. $sql = "SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)." WHERE course_code='".$course_code."'";
  1882. // TODO: Ivan: This is a mistake, please, have a look at it. Intention here is diffcult to be guessed.
  1883. //if ($send_to_tutor_also = true)
  1884. // Proposed change:
  1885. if ($send_to_tutor_also) {
  1886. //
  1887. $sql .= " AND tutor_id=1";
  1888. } else {
  1889. $sql .= " AND status=1";
  1890. }
  1891. $result = Database::query($sql);
  1892. while ($row = Database::fetch_array($result)) {
  1893. $tutor = Database::fetch_array(Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_USER)."
  1894. WHERE user_id='".$row['user_id']."'"));
  1895. $emailto = $tutor['email'];
  1896. $emailsubject = get_lang('NewUserInTheCourse').': '.$name_course;
  1897. $emailbody = get_lang('Dear').': '. api_get_person_name($tutor['firstname'], $tutor['lastname'])."\n";
  1898. $emailbody .= get_lang('MessageNewUserInTheCourse').': '.$name_course."\n";
  1899. $emailbody .= get_lang('UserName').': '.$student['username']."\n";
  1900. if (api_is_western_name_order()) {
  1901. $emailbody .= get_lang('FirstName').': '.$student['firstname']."\n";
  1902. $emailbody .= get_lang('LastName').': '.$student['lastname']."\n";
  1903. } else {
  1904. $emailbody .= get_lang('LastName').': '.$student['lastname']."\n";
  1905. $emailbody .= get_lang('FirstName').': '.$student['firstname']."\n";
  1906. }
  1907. $emailbody .= get_lang('Email').': '.$student['email']."\n\n";
  1908. $recipient_name = api_get_person_name($tutor['firstname'], $tutor['lastname'], null, PERSON_NAME_EMAIL_ADDRESS);
  1909. $sender_name = api_get_person_name(api_get_setting('administratorName'), api_get_setting('administratorSurname'), null, PERSON_NAME_EMAIL_ADDRESS);
  1910. $email_admin = api_get_setting('emailAdministrator');
  1911. @api_mail($recipient_name, $emailto, $emailsubject, $emailbody, $sender_name,$email_admin);
  1912. }
  1913. }
  1914. public static function get_special_course_list() {
  1915. $tbl_course_field = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD);
  1916. $tbl_course_field_value = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  1917. //we filter the courses from the URL
  1918. $join_access_url = $where_access_url='';
  1919. if (api_get_multiple_access_url()) {
  1920. $access_url_id = api_get_current_access_url_id();
  1921. if ($access_url_id != -1) {
  1922. $tbl_url_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  1923. $join_access_url= "LEFT JOIN $tbl_url_course url_rel_course ON url_rel_course.course_code= tcfv.course_code ";
  1924. $where_access_url =" AND access_url_id = $access_url_id ";
  1925. }
  1926. }
  1927. // get course list auto-register
  1928. $sql = "SELECT DISTINCT(tcfv.course_code) FROM $tbl_course_field_value tcfv INNER JOIN $tbl_course_field tcf
  1929. ON tcfv.field_id = tcf.id $join_access_url
  1930. WHERE tcf.field_variable = 'special_course' AND tcfv.field_value = 1 $where_access_url";
  1931. $special_course_result = Database::query($sql);
  1932. $special_course_list = array();
  1933. if (Database::num_rows($special_course_result)>0) {
  1934. $special_course_list = array();
  1935. while ($result_row = Database::fetch_array($special_course_result)) {
  1936. $special_course_list[] = $result_row['course_code'];
  1937. }
  1938. }
  1939. return $special_course_list;
  1940. }
  1941. /**
  1942. * Get list of courses for a given user
  1943. * @param int user ID
  1944. * @param boolean Whether to include courses from session or not
  1945. * @return array List of codes and db names
  1946. * @author isaac flores paz
  1947. */
  1948. public static function get_courses_list_by_user_id($user_id, $include_sessions = false) {
  1949. $user_id = intval($user_id);
  1950. $course_list = array();
  1951. $codes = array();
  1952. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  1953. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1954. $tbl_user_course_category = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  1955. $special_course_list = self::get_special_course_list();
  1956. $with_special_courses = $without_special_courses = '';
  1957. if (!empty($special_course_list)) {
  1958. $sc_string = '"'.implode('","',$special_course_list).'"';
  1959. $with_special_courses = ' course.code IN ('.$sc_string.')';
  1960. $without_special_courses = ' AND course.code NOT IN ('.$sc_string.')';
  1961. }
  1962. if (!empty($with_special_courses)) {
  1963. $sql = "SELECT DISTINCT(course.code), course.id as real_id
  1964. FROM ".$tbl_course_user." course_rel_user
  1965. LEFT JOIN ".$tbl_course." course
  1966. ON course.code = course_rel_user.course_code
  1967. LEFT JOIN ".$tbl_user_course_category." user_course_category
  1968. ON course_rel_user.user_course_cat = user_course_category.id
  1969. WHERE $with_special_courses
  1970. GROUP BY course.code
  1971. ORDER BY user_course_category.sort,course.title,course_rel_user.sort ASC";
  1972. $rs_special_course = Database::query($sql);
  1973. if (Database::num_rows($rs_special_course) > 0) {
  1974. while ($result_row = Database::fetch_array($rs_special_course)) {
  1975. $result_row['special_course'] = 1;
  1976. $course_list[] = $result_row;
  1977. $codes[] = $result_row['real_id'];
  1978. }
  1979. }
  1980. }
  1981. // get course list not auto-register. Use Distinct to avoid multiple
  1982. // entries when a course is assigned to a HRD (DRH) as watcher
  1983. $sql = "SELECT DISTINCT(course.code), course.id as real_id
  1984. FROM $tbl_course course
  1985. INNER JOIN $tbl_course_user cru ON course.code=cru.course_code
  1986. WHERE cru.user_id='$user_id' $without_special_courses";
  1987. $result = Database::query($sql);
  1988. if (Database::num_rows($result)) {
  1989. while ($row = Database::fetch_array($result, 'ASSOC')) {
  1990. $course_list[] = $row;
  1991. $codes[] = $row['real_id'];
  1992. }
  1993. }
  1994. if ($include_sessions === true) {
  1995. $sql = "SELECT DISTINCT(c.code), c.id as real_id
  1996. FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)." s, ".Database::get_main_table(TABLE_MAIN_COURSE)." c
  1997. WHERE id_user = $user_id AND s.course_code=c.code";
  1998. $r = Database::query($sql);
  1999. while ($row = Database::fetch_array($r, 'ASSOC')) {
  2000. if (!in_array($row['real_id'], $codes)) {
  2001. $course_list[] = $row;
  2002. }
  2003. }
  2004. }
  2005. return $course_list;
  2006. }
  2007. /**
  2008. * Get course ID from a given course directory name
  2009. * @param string Course directory (without any slash)
  2010. * @return string Course code, or false if not found
  2011. */
  2012. public static function get_course_id_from_path($path) {
  2013. $path = Database::escape_string(str_replace('.', '', str_replace('/', '', $path)));
  2014. $res = Database::query("SELECT code FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  2015. WHERE directory LIKE BINARY '$path'");
  2016. if ($res === false) {
  2017. return false;
  2018. }
  2019. if (Database::num_rows($res) != 1) {
  2020. return false;
  2021. }
  2022. $row = Database::fetch_array($res);
  2023. return $row['code'];
  2024. }
  2025. /**
  2026. * Get course code(s) from visual code
  2027. * @param string Visual code
  2028. * @return array List of codes for the given visual code
  2029. */
  2030. public static function get_courses_info_from_visual_code($code) {
  2031. $result = array();
  2032. $sql_result = Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  2033. WHERE visual_code = '".Database::escape_string($code)."'");
  2034. while ($virtual_course = Database::fetch_array($sql_result)) {
  2035. $result[] = $virtual_course;
  2036. }
  2037. return $result;
  2038. }
  2039. /**
  2040. * Get emails of tutors to course
  2041. * @param string Visual code
  2042. * @return array List of emails of tutors to course
  2043. * @author @author Carlos Vargas <carlos.vargas@dokeos.com>, Dokeos Latino
  2044. * */
  2045. public static function get_emails_of_tutors_to_course($code) {
  2046. $list = array();
  2047. $res = Database::query("SELECT user_id FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  2048. WHERE course_code='".Database::escape_string($code)."' AND status=1");
  2049. while ($list_users = Database::fetch_array($res)) {
  2050. $result = Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_USER)."
  2051. WHERE user_id=".$list_users['user_id']);
  2052. while ($row_user = Database::fetch_array($result)){
  2053. $name_teacher = api_get_person_name($row_user['firstname'], $row_user['lastname']);
  2054. $list[] = array($row_user['email'] => $name_teacher);
  2055. }
  2056. }
  2057. return $list;
  2058. }
  2059. /**
  2060. * Get coachs' emails by session
  2061. * @param int session id
  2062. * @param string course code
  2063. * @return array array(email => name_tutor) by coach
  2064. * @author Carlos Vargas <carlos.vargas@dokeos.com>
  2065. */
  2066. public static function get_email_of_tutor_to_session($session_id, $course_code) {
  2067. $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  2068. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  2069. $coachs_emails = array();
  2070. $course_code = Database::escape_string($course_code);
  2071. $session_id = intval($session_id);
  2072. $sql = "SELECT id_user FROM $tbl_session_course_user WHERE id_session='$session_id' AND course_code='$course_code' AND status =2";
  2073. $rs = Database::query($sql);
  2074. if (Database::num_rows($rs) > 0) {
  2075. $user_ids = array();
  2076. while ($row = Database::fetch_array($rs)) {
  2077. $user_ids[] = $row['id_user'];
  2078. }
  2079. $sql = "SELECT firstname, lastname, email FROM $tbl_user WHERE user_id IN (".implode(",",$user_ids).")";
  2080. $rs_user = Database::query($sql);
  2081. while ($row_emails = Database::fetch_array($rs_user)) {
  2082. //$name_tutor = api_get_person_name($row_emails['firstname'], $row_emails['lastname'], null, PERSON_NAME_EMAIL_ADDRESS);
  2083. $mail_tutor = array('email' => $row_emails['email'], 'complete_name' => api_get_person_name($row_emails['firstname'], $row_emails['lastname']));
  2084. $coachs_emails[] = $mail_tutor;
  2085. }
  2086. }
  2087. return $coachs_emails;
  2088. }
  2089. /**
  2090. * Creates a new extra field for a given course
  2091. * @param string Field's internal variable name
  2092. * @param int Field's type
  2093. * @param string Field's language var name
  2094. * @return int new extra field id
  2095. */
  2096. public static function create_course_extra_field($fieldvarname, $fieldtype, $fieldtitle) {
  2097. // database table definition
  2098. $t_cfv = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  2099. $t_cf = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  2100. $fieldvarname = Database::escape_string($fieldvarname);
  2101. $fieldtitle = Database::escape_string($fieldtitle);
  2102. $fieldtype = (int)$fieldtype;
  2103. $time = time();
  2104. $sql_field = "SELECT id FROM $t_cf WHERE field_variable = '$fieldvarname'";
  2105. $res_field = Database::query($sql_field);
  2106. $r_field = Database::fetch_row($res_field);
  2107. if (Database::num_rows($res_field) > 0) {
  2108. return $r_field[0];
  2109. }
  2110. // save new fieldlabel into course_field table
  2111. $sql = "SELECT MAX(field_order) FROM $t_cf";
  2112. $res = Database::query($sql);
  2113. $order = 0;
  2114. if (Database::num_rows($res) > 0) {
  2115. $row = Database::fetch_row($res);
  2116. $order = $row[0] + 1;
  2117. }
  2118. $sql = "INSERT INTO $t_cf
  2119. SET field_type = '$fieldtype',
  2120. field_variable = '$fieldvarname',
  2121. field_display_text = '$fieldtitle',
  2122. field_order = '$order',
  2123. tms = FROM_UNIXTIME($time)";
  2124. Database::query($sql);
  2125. return Database::insert_id();
  2126. }
  2127. /**
  2128. * Updates course attribute. Note that you need to check that your attribute is valid before you use this function
  2129. *
  2130. * @param int Course id
  2131. * @param string Attribute name
  2132. * @param string Attribute value
  2133. * @return bool True if attribute was successfully updated, false if course was not found or attribute name is invalid
  2134. */
  2135. public static function update_attribute($id, $name, $value) {
  2136. $id = (int)$id;
  2137. $table = Database::get_main_table(TABLE_MAIN_COURSE);
  2138. $sql = "UPDATE $table SET $name = '".Database::escape_string($value)."' WHERE id = '$id';";
  2139. return Database::query($sql);
  2140. }
  2141. /**
  2142. * Update course attributes. Will only update attributes with a non-empty value. Note that you NEED to check that your attributes are valid before using this function
  2143. *
  2144. * @param int Course id
  2145. * @param array Associative array with field names as keys and field values as values
  2146. * @return bool True if update was successful, false otherwise
  2147. */
  2148. public static function update_attributes($id, $attributes) {
  2149. $id = (int)$id;
  2150. $table = Database::get_main_table(TABLE_MAIN_COURSE);
  2151. $sql = "UPDATE $table SET ";
  2152. $i = 0;
  2153. foreach($attributes as $name => $value) {
  2154. if($value != '') {
  2155. if($i > 0) {
  2156. $sql .= ", ";
  2157. }
  2158. $sql .= " $name = '".Database::escape_string($value)."'";
  2159. $i++;
  2160. }
  2161. }
  2162. $sql .= " WHERE id = '$id';";
  2163. return Database::query($sql);
  2164. }
  2165. /**
  2166. * Update an extra field value for a given course
  2167. * @param integer Course ID
  2168. * @param string Field variable name
  2169. * @param string Field value
  2170. * @return boolean true if field updated, false otherwise
  2171. */
  2172. public static function update_course_extra_field_value($course_code, $fname, $fvalue = '') {
  2173. $t_cfv = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  2174. $t_cf = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  2175. $fname = Database::escape_string($fname);
  2176. $course_code = Database::escape_string($course_code);
  2177. $fvalues = '';
  2178. if (is_array($fvalue)) {
  2179. foreach ($fvalue as $val) {
  2180. $fvalues .= Database::escape_string($val).';';
  2181. }
  2182. if (!empty($fvalues)) {
  2183. $fvalues = substr($fvalues, 0, -1);
  2184. }
  2185. } else {
  2186. $fvalues = Database::escape_string($fvalue);
  2187. }
  2188. $sqlcf = "SELECT * FROM $t_cf WHERE field_variable='$fname'";
  2189. $rescf = Database::query($sqlcf);
  2190. if (Database::num_rows($rescf) == 1) {
  2191. // Ok, the field exists
  2192. // Check if enumerated field, if the option is available
  2193. $rowcf = Database::fetch_array($rescf);
  2194. $tms = time();
  2195. $sqlcfv = "SELECT * FROM $t_cfv WHERE course_code = '$course_code' AND field_id = '".$rowcf['id']."' ORDER BY id";
  2196. $rescfv = Database::query($sqlcfv);
  2197. $n = Database::num_rows($rescfv);
  2198. if ($n > 1) {
  2199. //problem, we already have to values for this field and user combination - keep last one
  2200. while ($rowcfv = Database::fetch_array($rescfv)) { // See the TODO note below.
  2201. if ($n > 1) {
  2202. $sqld = "DELETE FROM $t_cfv WHERE id = ".$rowcfv['id'];
  2203. $resd = Database::query($sqld);
  2204. $n--;
  2205. }
  2206. $rowcfv = Database::fetch_array($rescfv);
  2207. if ($rowcfv['field_value'] != $fvalues) {
  2208. $sqlu = "UPDATE $t_cfv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowcfv['id'];
  2209. $resu = Database::query($sqlu);
  2210. return ($resu ? true : false);
  2211. }
  2212. return true; // TODO: Sure exit from the function occures in this "while" cycle. Logic should checked. Maybe "if" instead of "while"? It is not clear...
  2213. }
  2214. } elseif ($n == 1) {
  2215. //we need to update the current record
  2216. $rowcfv = Database::fetch_array($rescfv);
  2217. if ($rowcfv['field_value'] != $fvalues) {
  2218. $sqlu = "UPDATE $t_cfv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowcfv['id'];
  2219. //error_log('UM::update_extra_field_value: '.$sqlu);
  2220. $resu = Database::query($sqlu);
  2221. return ($resu ? true : false);
  2222. }
  2223. return true;
  2224. } else {
  2225. $sqli = "INSERT INTO $t_cfv (course_code,field_id,field_value,tms) " .
  2226. "VALUES ('$course_code',".$rowcf['id'].",'$fvalues',FROM_UNIXTIME($tms))";
  2227. //error_log('UM::update_extra_field_value: '.$sqli);
  2228. $resi = Database::query($sqli);
  2229. return ($resi ? true : false);
  2230. }
  2231. } else {
  2232. return false; //field not found
  2233. }
  2234. }
  2235. /**
  2236. * Get the course id of an course by the database name
  2237. * @param string The database name
  2238. * @return string The course id
  2239. */
  2240. public static function get_course_id_by_database_name($db_name) {
  2241. return Database::result(Database::query('SELECT code FROM '.Database::get_main_table(TABLE_MAIN_COURSE).
  2242. ' WHERE db_name="'.Database::escape_string($db_name).'"'), 0, 'code');
  2243. }
  2244. public static function get_session_category_id_by_session_id($session_id) {
  2245. return Database::result(Database::query('SELECT sc.id session_category
  2246. FROM '.Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY).' sc
  2247. INNER JOIN '.Database::get_main_table(TABLE_MAIN_SESSION).' s
  2248. ON sc.id=s.session_category_id WHERE s.id="'.Database::escape_string($session_id).'"'),
  2249. 0, 'session_category');
  2250. }
  2251. /**
  2252. * Get the course id of an course by the database name
  2253. * @param string The database name
  2254. * @return string The course id
  2255. */
  2256. public static function get_course_extra_field_list($code) {
  2257. $tbl_course_field = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  2258. $tbl_course_field_value = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  2259. $sql_field = "SELECT id, field_type, field_variable, field_display_text, field_default_value
  2260. FROM $tbl_course_field WHERE field_visible = '1' ";
  2261. $res_field = Database::query($sql_field);
  2262. $extra_fields = array();
  2263. while($rowcf = Database::fetch_array($res_field)) {
  2264. $extra_field_id = $rowcf['id'];
  2265. $sql_field_value = "SELECT field_value FROM $tbl_course_field_value WHERE course_code = '$code' AND field_id = '$extra_field_id' ";
  2266. $res_field_value = Database::query($sql_field_value);
  2267. if(Database::num_rows($res_field_value) > 0 ) {
  2268. $r_field_value = Database::fetch_row($res_field_value);
  2269. $rowcf['extra_field_value'] = $r_field_value[0];
  2270. }
  2271. $extra_fields[] = $rowcf;
  2272. }
  2273. return $extra_fields;
  2274. }
  2275. /**
  2276. * Gets the value of a course extra field. Returns null if it was not found
  2277. *
  2278. * @param string Name of the extra field
  2279. * @param string Course code
  2280. * @return string Value
  2281. */
  2282. public static function get_course_extra_field_value($field_name, $code) {
  2283. $tbl_course_field = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  2284. $tbl_course_field_value = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  2285. $sql = "SELECT id FROM $tbl_course_field WHERE field_visible = '1' AND field_variable = '$field_name';";
  2286. $res = Database::query($sql);
  2287. $row = Database::fetch_object($res);
  2288. if(!$row) {
  2289. return null;
  2290. } else {
  2291. $sql_field_value = "SELECT field_value FROM $tbl_course_field_value WHERE course_code = '$code' AND field_id = '{$row->id}';";
  2292. $res_field_value = Database::query($sql_field_value);
  2293. $row_field_value = Database::fetch_object($res_field_value);
  2294. if(!$row_field_value) {
  2295. return null;
  2296. } else {
  2297. return $row_field_value['field_value'];
  2298. }
  2299. }
  2300. }
  2301. /**
  2302. * Get the database name of a course by the code
  2303. * @param string The course code
  2304. * @return string The database name
  2305. */
  2306. public static function get_name_database_course($course_code) {
  2307. return Database::result(Database::query('SELECT db_name FROM '.Database::get_main_table(TABLE_MAIN_COURSE).
  2308. ' WHERE code="'.Database::escape_string($course_code).'"'), 0, 'db_name');
  2309. }
  2310. /**
  2311. * Lists details of the course description
  2312. * @param array The course description
  2313. * @param string The encoding
  2314. * @param bool If true is displayed if false is hidden
  2315. * @return string The course description in html
  2316. */
  2317. public static function get_details_course_description_html($descriptions, $charset, $action_show = true) {
  2318. if (isset($descriptions) && count($descriptions) > 0) {
  2319. $data = '';
  2320. foreach ($descriptions as $id => $description) {
  2321. $data .= '<div class="sectiontitle">';
  2322. if (api_is_allowed_to_edit() && $action_show) {
  2323. //delete
  2324. $data .= '<a href="'.api_get_self().'?'.api_get_cidreq().'&amp;action=delete&amp;description_id='.$description->id.'" onclick="javascript:if(!confirm(\''.addslashes(api_htmlentities(get_lang('ConfirmYourChoice'), ENT_QUOTES, $charset)).'\')) return false;">';
  2325. $data .= Display::return_icon('delete.gif', get_lang('Delete'), array('style' => 'vertical-align:middle;float:right;'));
  2326. $data .= '</a> ';
  2327. //edit
  2328. $data .= '<a href="'.api_get_self().'?'.api_get_cidreq().'&amp;description_id='.$description->id.'">';
  2329. $data .= Display::return_icon('edit.png', get_lang('Edit'), array('style' => 'vertical-align:middle;float:right; padding-right:4px;'), ICON_SIZE_SMALL);
  2330. $data .= '</a> ';
  2331. }
  2332. $data .= $description->title;
  2333. $data .= '</div>';
  2334. $data .= '<div class="sectioncomment">';
  2335. $data .= $description->content;
  2336. $data .= '</div>';
  2337. }
  2338. } else {
  2339. $data .= '<em>'.get_lang('ThisCourseDescriptionIsEmpty').'</em>';
  2340. }
  2341. return $data;
  2342. }
  2343. /**
  2344. * Returns the details of a course category
  2345. *
  2346. * @param string Category code
  2347. * @return array Course category
  2348. */
  2349. public static function get_course_category($code) {
  2350. $table_categories = Database::get_main_table(TABLE_MAIN_CATEGORY);
  2351. $sql = "SELECT * FROM $table_categories WHERE code = '$code';";
  2352. return Database::fetch_array(Database::query($sql));
  2353. }
  2354. /*
  2355. ==============================================================================
  2356. DEPRECATED METHODS
  2357. ==============================================================================
  2358. */
  2359. /**
  2360. * This code creates a select form element to let the user
  2361. * choose a real course to link to.
  2362. *
  2363. * A good non-display library should not use echo statements, but just return text/html
  2364. * so users of the library can choose when to display.
  2365. *
  2366. * We display the course code, but internally store the course id.
  2367. *
  2368. * @param boolean $has_size, true the select tag gets a size element, false it stays a dropdownmenu
  2369. * @param boolean $only_current_user_courses, true only the real courses of which the
  2370. * current user is course admin are displayed, false all real courses are shown.
  2371. * @param string $element_name the name of the select element
  2372. * @return a string containing html code for a form select element.
  2373. * @deprecated Function not in use
  2374. */
  2375. public static function get_real_course_code_select_html($element_name, $has_size = true, $only_current_user_courses = true, $user_id) {
  2376. if ($only_current_user_courses) {
  2377. $real_course_list = self::get_real_course_list_of_user_as_course_admin($user_id);
  2378. } else {
  2379. $real_course_list = self::get_real_course_list();
  2380. }
  2381. if ($has_size) {
  2382. $size_element = "size=\"".SELECT_BOX_SIZE."\"";
  2383. } else {
  2384. $size_element = "";
  2385. }
  2386. $html_code = "<select name=\"$element_name\" $size_element >\n";
  2387. foreach ($real_course_list as $real_course) {
  2388. $course_code = $real_course["code"];
  2389. $html_code .= "<option value=\"".$course_code."\">";
  2390. $html_code .= $course_code;
  2391. $html_code .= "</option>\n";
  2392. }
  2393. $html_code .= "</select>\n";
  2394. return $html_code;
  2395. }
  2396. /**
  2397. * Get count rows of a table inside a course database
  2398. * @param string The table of which the rows should be counted
  2399. * @param int optionally count rows by session id
  2400. * @return int The number of rows in the given table.
  2401. */
  2402. public static function count_rows_course_table($table, $session_id = '', $course_id = null) {
  2403. $condition_session = '';
  2404. if ($session_id !== '') {
  2405. $session_id = intval($session_id);
  2406. $condition_session = " AND session_id = '$session_id' ";
  2407. }
  2408. if (!empty($course_id)) {
  2409. $course_id = intval($course_id);
  2410. } else {
  2411. $course_id = api_get_course_int_id();
  2412. }
  2413. $condition_session .= " AND c_id = '$course_id' ";
  2414. $sql = "SELECT COUNT(*) AS n FROM $table WHERE 1=1 $condition_session ";
  2415. $rs = Database::query($sql);
  2416. $row = Database::fetch_row($rs);
  2417. return $row[0];
  2418. }
  2419. /**
  2420. * Subscribes courses to human resource manager (Dashboard feature)
  2421. * @param int Human Resource Manager id
  2422. * @param array Courses code
  2423. * @param int Relation type
  2424. **/
  2425. public static function suscribe_courses_to_hr_manager($hr_manager_id,$courses_list) {
  2426. global $_configuration;
  2427. // Database Table Definitions
  2428. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  2429. $tbl_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  2430. $tbl_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  2431. $hr_manager_id = intval($hr_manager_id);
  2432. $affected_rows = 0;
  2433. //Deleting assigned courses to hrm_id
  2434. if ($_configuration['multiple_access_urls']) {
  2435. $sql = "SELECT s.course_code FROM $tbl_course_rel_user s INNER JOIN $tbl_course_rel_access_url a ON (a.course_code = s.course_code) WHERE user_id = $hr_manager_id AND relation_type=".COURSE_RELATION_TYPE_RRHH." AND access_url_id = ".api_get_current_access_url_id()."";
  2436. } else {
  2437. $sql = "SELECT course_code FROM $tbl_course_rel_user WHERE user_id = $hr_manager_id AND relation_type=".COURSE_RELATION_TYPE_RRHH." ";
  2438. }
  2439. $result = Database::query($sql);
  2440. if (Database::num_rows($result) > 0) {
  2441. while ($row = Database::fetch_array($result)) {
  2442. $sql = "DELETE FROM $tbl_course_rel_user WHERE course_code = '{$row['course_code']}' AND user_id = $hr_manager_id AND relation_type=".COURSE_RELATION_TYPE_RRHH." ";
  2443. Database::query($sql);
  2444. }
  2445. }
  2446. // inserting new courses list
  2447. if (is_array($courses_list)) {
  2448. foreach ($courses_list as $course_code) {
  2449. $course_code = Database::escape_string($course_code);
  2450. $insert_sql = "INSERT IGNORE INTO $tbl_course_rel_user(course_code, user_id, status, relation_type) VALUES('$course_code', $hr_manager_id, '".DRH."', '".COURSE_RELATION_TYPE_RRHH."')";
  2451. Database::query($insert_sql);
  2452. if (Database::affected_rows()) {
  2453. $affected_rows++;
  2454. }
  2455. }
  2456. }
  2457. return $affected_rows;
  2458. }
  2459. /**
  2460. * get courses followed by human resources manager
  2461. * @param int human resources manager id
  2462. * @return array courses
  2463. */
  2464. public static function get_courses_followed_by_drh($user_id) {
  2465. // Database Table Definitions
  2466. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  2467. $tbl_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  2468. $tbl_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  2469. $user_id = intval($user_id);
  2470. $assigned_courses_to_hrm = array();
  2471. if (api_get_multiple_access_url()) {
  2472. $sql = "SELECT *, id as real_id FROM $tbl_course c
  2473. INNER JOIN $tbl_course_rel_user cru ON (cru.course_code = c.code) LEFT JOIN $tbl_course_rel_access_url a ON (a.course_code = c.code) WHERE cru.user_id = '$user_id' AND status = ".DRH." AND relation_type = '".COURSE_RELATION_TYPE_RRHH."' AND access_url_id = ".api_get_current_access_url_id()."";
  2474. } else {
  2475. $sql = "SELECT *, id as real_id FROM $tbl_course c
  2476. INNER JOIN $tbl_course_rel_user cru ON cru.course_code = c.code AND cru.user_id = '$user_id' AND status = ".DRH." AND relation_type = '".COURSE_RELATION_TYPE_RRHH."' ";
  2477. }
  2478. $rs_assigned_courses = Database::query($sql);
  2479. if (Database::num_rows($rs_assigned_courses) > 0) {
  2480. while ($row_assigned_courses = Database::fetch_array($rs_assigned_courses)) {
  2481. $assigned_courses_to_hrm[$row_assigned_courses['code']] = $row_assigned_courses;
  2482. }
  2483. }
  2484. return $assigned_courses_to_hrm;
  2485. }
  2486. /**
  2487. * check if a course is special (autoregister)
  2488. * @param string course code
  2489. */
  2490. public static function is_special_course($course_code){
  2491. $tbl_course_field_value = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  2492. $tbl_course_field = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  2493. $is_special = false;
  2494. $sql = "SELECT course_code FROM $tbl_course_field_value tcfv INNER JOIN $tbl_course_field tcf ON " .
  2495. " tcfv.field_id = tcf.id WHERE tcf.field_variable = 'special_course' AND tcfv.field_value = 1 AND course_code='$course_code'";
  2496. $result = Database::query($sql);
  2497. $num_rows = Database::num_rows($result);
  2498. if ($num_rows > 0){
  2499. $is_special = true;
  2500. }
  2501. return $is_special;
  2502. }
  2503. /**
  2504. * Update course picture
  2505. * @param string Course code
  2506. * @param string File name
  2507. * @param string The full system name of the image from which course picture will be created.
  2508. * @return bool Returns the resulting. In case of internal error or negative validation returns FALSE.
  2509. */
  2510. public static function update_course_picture($course_code, $filename, $source_file = null) {
  2511. $course_info = api_get_course_info($course_code);
  2512. $store_path = api_get_path(SYS_COURSE_PATH).$course_info['path']; // course path
  2513. $course_image = $store_path.'/course-pic.png'; // image name for courses
  2514. $course_medium_image = $store_path.'/course-pic85x85.png';
  2515. //$extension = strtolower(substr(strrchr($filename, '.'), 1));
  2516. if (file_exists($course_image)) {
  2517. @unlink($course_image);
  2518. }
  2519. if (file_exists($course_medium_image)) {
  2520. @unlink($course_medium_image);
  2521. }
  2522. $my_course_image = new Image($source_file);
  2523. $result = $my_course_image->send_image($course_image, -1, 'png');
  2524. //Redimension image to 100x85
  2525. if ($result) {
  2526. $medium = new Image($course_image);
  2527. //$picture_infos = $medium->get_image_size();
  2528. $medium->resize(100, 85, 0, false);
  2529. $medium->send_image($store_path.'/course-pic85x85.png', -1, 'png');
  2530. }
  2531. return $result;
  2532. }
  2533. /**
  2534. * @deprecated See CourseManager::course_code_exists()
  2535. */
  2536. public static function is_existing_course_code($wanted_course_code) {
  2537. return self::course_code_exists($wanted_course_code);
  2538. }
  2539. /**
  2540. * Builds the course block in user_portal.php
  2541. * @todo use Twig
  2542. */
  2543. public static function course_item_html($params, $is_sub_content = false) {
  2544. $html = '';
  2545. $class = "well course-box";
  2546. if ($is_sub_content) {
  2547. $class = "course_item";
  2548. }
  2549. if (!empty($params['is_session'])) {
  2550. $class .= " session-item";
  2551. }
  2552. $html .= '<div class="'.$class.'">';
  2553. $html .= '<div class="row">';
  2554. $html .= '<div class="span7">';
  2555. $html .= ' <div class="row">';
  2556. $html .= '<div class="span1 course-box-thumbnail-box">';
  2557. if (!empty($params['link'])) {
  2558. $html .= '<a class="thumbnail" href="'.$params['link'].'">';
  2559. $html .= $params['icon'];
  2560. $html .= '</a>';
  2561. } else {
  2562. $html .= '<div class="thumbnail">';
  2563. $html .= $params['icon'];
  2564. $html .= '</div>';
  2565. }
  2566. $html .= '</div>';
  2567. $notifications = isset($params['notifications']) ? $params['notifications'] : null;
  2568. $param_class = isset($params['class']) ? $params['class'] : '';
  2569. if (!empty($params['is_session'])) {
  2570. $param_class .= ' session-box-text';
  2571. } else {
  2572. $param_class .= ' course-box-text';
  2573. }
  2574. $html .= '<div class="span6 '.$param_class.'">';
  2575. $html .='<h3>'.$params['title'].$notifications.'</h3> ';
  2576. if (!empty($params['subtitle'])) {
  2577. $html .= '<small>'.$params['subtitle'].'</small>';
  2578. }
  2579. if (!empty($params['teachers'])) {
  2580. $html .= '<h5>'.Display::return_icon('teacher.png', get_lang('Teacher'), array(), ICON_SIZE_TINY).$params['teachers'].'</h5>';
  2581. }
  2582. if (!empty($params['coaches'])) {
  2583. $html .= '<h5>'.Display::return_icon('teacher.png', get_lang('Coach'), array(), ICON_SIZE_TINY).$params['coaches'].'</h5>';
  2584. }
  2585. $html .= '</div>';
  2586. $html .= '</div>';
  2587. $html .= '</div>';
  2588. $params['right_actions'] = isset($params['right_actions']) ? $params['right_actions'] : null;
  2589. $html .= '<div class="span1 pull-right course-box-actions">'.$params['right_actions'].'</div>';
  2590. $html .= '</div>';
  2591. $html .= '</div>';
  2592. return $html;
  2593. }
  2594. public static function course_item_parent($main_content, $sub_content, $sub_sub_content = null) {
  2595. return '<div class="well">'.$main_content.$sub_content.$sub_sub_content.'</div>';
  2596. }
  2597. /**
  2598. * Display special courses (and only these) as several HTML divs of class userportal-course-item
  2599. *
  2600. * Special courses are courses that stick on top of the list and are "auto-registerable"
  2601. * in the sense that any user clicking them is registered as a student
  2602. * @param int User id
  2603. * @param bool Whether to show the document quick-loader or not
  2604. * @return void
  2605. */
  2606. public static function display_special_courses($user_id, $load_dirs = false) {
  2607. $user_id = intval($user_id);
  2608. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  2609. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  2610. $special_course_list = self::get_special_course_list();
  2611. $html = null;
  2612. $with_special_courses = $without_special_courses = '';
  2613. if (!empty($special_course_list)) {
  2614. $with_special_courses = ' course.code IN ("'.implode('","',$special_course_list).'")';
  2615. }
  2616. //$course_items = array();
  2617. if (!empty($with_special_courses)) {
  2618. $sql = "SELECT course.id, course.code, course.subscribe subscr, course.unsubscribe unsubscr, course_rel_user.status status,
  2619. course_rel_user.sort sort, course_rel_user.user_course_cat user_course_cat, course_rel_user.user_id
  2620. FROM $tbl_course course
  2621. LEFT JOIN $tbl_course_user course_rel_user ON course.code = course_rel_user.course_code AND course_rel_user.user_id = '$user_id'
  2622. WHERE $with_special_courses group by course.code";
  2623. $rs_special_course = Database::query($sql);
  2624. $number_of_courses = Database::num_rows($rs_special_course);
  2625. $key = 0;
  2626. if ($number_of_courses > 0) {
  2627. while ($course = Database::fetch_array($rs_special_course)) {
  2628. $course_info = api_get_course_info($course['code']);
  2629. $params = array();
  2630. // Get notifications.
  2631. //$course['id_session'] = null;
  2632. //$course['status'] = $course['status'];
  2633. $course_info['id_session'] = null;
  2634. $course_info['status'] = $course['status'];
  2635. $show_notification = Display::show_notification($course_info);
  2636. if (empty($course['user_id'])) {
  2637. $course['status'] = STUDENT;
  2638. }
  2639. $params['icon'] = Display::return_icon('blackboard.png', $course_info['title'], array(), ICON_SIZE_LARGE);
  2640. $params['right_actions'] = '';
  2641. if (api_is_platform_admin()) {
  2642. if ($load_dirs) {
  2643. $params['right_actions'] .= '<a id="document_preview_'.$course['real_id'].'_0" class="document_preview" href="javascript:void(0);">'.Display::return_icon('folder.png', get_lang('Documents'), array('align' => 'absmiddle'),ICON_SIZE_SMALL).'</a>';
  2644. $params['right_actions'] .= '<a href="'.api_get_path(WEB_CODE_PATH).'course_info/infocours.php?cidReq='.$course['code'].'">'.Display::return_icon('edit.png', get_lang('Edit'), array('align' => 'absmiddle'),ICON_SIZE_SMALL).'</a>';
  2645. $params['right_actions'] .= Display::div('', array('id' => 'document_result_'.$course['real_id'].'_0', 'class'=>'document_preview_container'));
  2646. } else {
  2647. $params['right_actions'] .= '<a href="'.api_get_path(WEB_CODE_PATH).'course_info/infocours.php?cidReq='.$course['code'].'">'.Display::return_icon('edit.png', get_lang('Edit'), array('align' => 'absmiddle'),ICON_SIZE_SMALL).'</a>';
  2648. }
  2649. if ($course['status'] == COURSEMANAGER) {
  2650. //echo Display::return_icon('teachers.gif', get_lang('Status').': '.get_lang('Teacher'), array('style'=>'width: 11px; height: 11px;'));
  2651. }
  2652. } else {
  2653. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED) {
  2654. if ($load_dirs) {
  2655. $params['right_actions'] .= '<a id="document_preview_'.$course['real_id'].'_0" class="document_preview" href="javascript:void(0);">'.Display::return_icon('folder.png', get_lang('Documents'), array('align' => 'absmiddle'),ICON_SIZE_SMALL).'</a>';
  2656. $params['right_actions'] .= Display::div('', array('id' => 'document_result_'.$course['real_id'].'_0', 'class'=>'document_preview_container'));
  2657. }
  2658. }
  2659. }
  2660. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED || $course['status'] == COURSEMANAGER) {
  2661. $course_title = '<a href="'.api_get_path(WEB_COURSE_PATH).$course_info['path'].'/?id_session=0&amp;autoreg=1">'.$course_info['title'].'</a>';
  2662. } else {
  2663. $course_title = $course_info['title']." ".Display::tag('span',get_lang('CourseClosed'), array('class'=>'item_closed'));
  2664. }
  2665. if (api_get_setting('display_coursecode_in_courselist') == 'true') {
  2666. $course_title .= ' ('.$course_info['visual_code'].') ';
  2667. }
  2668. if (api_get_setting('display_teacher_in_courselist') == 'true') {
  2669. $params['teachers'] = CourseManager::get_teacher_list_from_course_code_to_string($course['code'], self::USER_SEPARATOR, true);
  2670. }
  2671. $course_title .= '&nbsp;';
  2672. $course_title .= Display::return_icon('klipper.png', get_lang('CourseAutoRegister'));
  2673. $params['title'] = $course_title;
  2674. $params['link'] = api_get_path(WEB_COURSE_PATH).$course_info['path'].'/?id_session=0&amp;autoreg=1';
  2675. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED) {
  2676. $params['notifications'] = $show_notification;
  2677. }
  2678. $html .= self::course_item_html($params, false);
  2679. //$course_items[] = $params;
  2680. $key++;
  2681. }
  2682. }
  2683. }
  2684. return $html;
  2685. }
  2686. /**
  2687. * Display courses (without special courses) as several HTML divs
  2688. * of course categories, as class userportal-catalog-item.
  2689. * @uses display_courses_in_category() to display the courses themselves
  2690. * @param int user id
  2691. * @param bool Whether to show the document quick-loader or not
  2692. * @return void
  2693. */
  2694. public static function display_courses($user_id, $load_dirs = false) {
  2695. $user_id = intval($user_id);
  2696. if (empty($user_id)) {
  2697. $user_id = api_get_user_id();
  2698. }
  2699. $html = null;
  2700. // Step 1: We get all the categories of the user
  2701. $tucc = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  2702. $sql = "SELECT id, title FROM $tucc WHERE user_id='".$user_id."' ORDER BY sort ASC";
  2703. $result = Database::query($sql);
  2704. while ($row = Database::fetch_array($result)) {
  2705. $params = array();
  2706. // We simply display the title of the category.
  2707. $params['icon'] = Display::return_icon('folder_yellow.png', $row['title'], array(), ICON_SIZE_LARGE);
  2708. $params['title'] = $row['title'];
  2709. $params['class'] = 'table_user_course_category';
  2710. $html .= self::course_item_parent(self::course_item_html($params, true), self :: display_courses_in_category($row['id'], $load_dirs));
  2711. }
  2712. // Step 2: We display the course without a user category.
  2713. $html .= self :: display_courses_in_category(0, $load_dirs);
  2714. return $html;
  2715. }
  2716. /**
  2717. * Display courses inside a category (without special courses) as HTML dics of
  2718. * class userportal-course-item.
  2719. * @param int User category id
  2720. * @param bool Whether to show the document quick-loader or not
  2721. * @return void
  2722. */
  2723. public static function display_courses_in_category($user_category_id, $load_dirs = false) {
  2724. $user_id = api_get_user_id();
  2725. // Table definitions
  2726. $TABLECOURS = Database :: get_main_table(TABLE_MAIN_COURSE);
  2727. $TABLECOURSUSER = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  2728. $TABLE_ACCESS_URL_REL_COURSE = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  2729. $current_url_id = api_get_current_access_url_id();
  2730. // Get course list auto-register
  2731. $special_course_list = self::get_special_course_list();
  2732. $without_special_courses = '';
  2733. if (!empty($special_course_list)) {
  2734. $without_special_courses = ' AND course.code NOT IN ("'.implode('","',$special_course_list).'")';
  2735. }
  2736. //AND course_rel_user.relation_type<>".COURSE_RELATION_TYPE_RRHH."
  2737. $sql = "SELECT course.id, course.title, course.code, course.subscribe subscr, course.unsubscribe unsubscr, course_rel_user.status status,
  2738. course_rel_user.sort sort, course_rel_user.user_course_cat user_course_cat
  2739. FROM $TABLECOURS course,
  2740. $TABLECOURSUSER course_rel_user, ".$TABLE_ACCESS_URL_REL_COURSE." url
  2741. WHERE course.code = course_rel_user.course_code AND url.course_code = course.code AND
  2742. course_rel_user.user_id = '".$user_id."' AND
  2743. course_rel_user.user_course_cat='".$user_category_id."' $without_special_courses ";
  2744. // If multiple URL access mode is enabled, only fetch courses
  2745. // corresponding to the current URL.
  2746. if (api_get_multiple_access_url() && $current_url_id != -1) {
  2747. $sql .= " AND url.course_code=course.code AND access_url_id='".$current_url_id."'";
  2748. }
  2749. // Use user's classification for courses (if any).
  2750. $sql .= " ORDER BY course_rel_user.user_course_cat, course_rel_user.sort ASC";
  2751. $result = Database::query($sql);
  2752. $status_icon = '';
  2753. $html = '';
  2754. $course_list = array();
  2755. // Browse through all courses.
  2756. while ($course = Database::fetch_array($result)) {
  2757. $course_info = api_get_course_info($course['code']);
  2758. //$course['id_session'] = null;
  2759. $course_info['id_session'] = null;
  2760. $course_info['status'] = $course['status'];
  2761. //In order to avoid doubles
  2762. if (in_array($course_info['real_id'], $course_list)) {
  2763. continue;
  2764. } else {
  2765. $course_list[] = $course_info['real_id'];
  2766. }
  2767. // For each course, get if there is any notification icon to show
  2768. // (something that would have changed since the user's last visit).
  2769. $show_notification = Display :: show_notification($course_info);
  2770. // New code displaying the user's status in respect to this course.
  2771. $status_icon = Display::return_icon('blackboard.png', $course_info['title'], array(), ICON_SIZE_LARGE);
  2772. $params = array();
  2773. $params['right_actions'] = '';
  2774. if (api_is_platform_admin()) {
  2775. if ($load_dirs) {
  2776. $params['right_actions'] .= '<a id="document_preview_'.$course_info['real_id'].'_0" class="document_preview" href="javascript:void(0);">'.Display::return_icon('folder.png', get_lang('Documents'), array('align' => 'absmiddle'),ICON_SIZE_SMALL).'</a>';
  2777. $params['right_actions'] .= '<a href="'.api_get_path(WEB_CODE_PATH).'course_info/infocours.php?cidReq='.$course['code'].'">'.Display::return_icon('edit.png', get_lang('Edit'), array('align' => 'absmiddle'),ICON_SIZE_SMALL).'</a>';
  2778. $params['right_actions'] .= Display::div('', array('id' => 'document_result_'.$course_info['real_id'].'_0', 'class'=>'document_preview_container'));
  2779. } else {
  2780. $params['right_actions'].= '<a href="'.api_get_path(WEB_CODE_PATH).'course_info/infocours.php?cidReq='.$course['code'].'">'.Display::return_icon('edit.png', get_lang('Edit'), array('align' => 'absmiddle'),ICON_SIZE_SMALL).'</a>';
  2781. }
  2782. if ($course_info['status'] == COURSEMANAGER) {
  2783. //echo Display::return_icon('teachers.gif', get_lang('Status').': '.get_lang('Teacher'), array('style'=>'width: 11px; height: 11px;'));
  2784. }
  2785. } else {
  2786. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED) {
  2787. if ($load_dirs) {
  2788. $params['right_actions'] .= '<a id="document_preview_'.$course_info['real_id'].'_0" class="document_preview" href="javascript:void(0);">'.Display::return_icon('folder.png', get_lang('Documents'), array('align' => 'absmiddle'),ICON_SIZE_SMALL).'</a>';
  2789. $params['right_actions'] .= Display::div('', array('id' => 'document_result_'.$course_info['real_id'].'_0', 'class'=>'document_preview_container'));
  2790. } else {
  2791. if ($course_info['status'] == COURSEMANAGER) {
  2792. $params['right_actions'].= '<a href="'.api_get_path(WEB_CODE_PATH).'course_info/infocours.php?cidReq='.$course['code'].'">'.Display::return_icon('edit.png', get_lang('Edit'), array('align' => 'absmiddle'),ICON_SIZE_SMALL).'</a>';
  2793. }
  2794. }
  2795. }
  2796. }
  2797. $course_title = $course_info['title'];
  2798. $course_title_url = '';
  2799. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED || $course['status'] == COURSEMANAGER) {
  2800. $course_title_url = api_get_path(WEB_COURSE_PATH).$course_info['path'].'/?id_session=0';
  2801. $course_title = Display::url($course_info['title'], $course_title_url);
  2802. } else {
  2803. $course_title = $course_info['title']." ".Display::tag('span',get_lang('CourseClosed'), array('class'=>'item_closed'));
  2804. }
  2805. // Start displaying the course block itself
  2806. if (api_get_setting('display_coursecode_in_courselist') == 'true') {
  2807. $course_title .= ' ('.$course_info['visual_code'].') ';
  2808. }
  2809. if (api_get_setting('display_teacher_in_courselist') == 'true') {
  2810. $teachers = CourseManager::get_teacher_list_from_course_code_to_string($course['code'], self::USER_SEPARATOR, true);
  2811. }
  2812. $params['link'] = $course_title_url;
  2813. $params['icon'] = $status_icon;
  2814. $params['title'] = $course_title;
  2815. $params['teachers'] = $teachers;
  2816. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED) {
  2817. $params['notifications'] = $show_notification;
  2818. }
  2819. $is_subcontent = true;
  2820. if (empty($user_category_id)) {
  2821. $is_subcontent = false;
  2822. }
  2823. $html .= self::course_item_html($params, $is_subcontent);
  2824. }
  2825. return $html;
  2826. }
  2827. /**
  2828. * Retrieves the user defined course categories
  2829. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  2830. * @return array containing all the titles of the user defined courses with the id as key of the array
  2831. */
  2832. function get_user_course_categories() {
  2833. global $_user;
  2834. $output = array();
  2835. $table_category = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  2836. $sql = "SELECT * FROM ".$table_category." WHERE user_id='".Database::escape_string($_user['user_id'])."'";
  2837. $result = Database::query($sql);
  2838. while ($row = Database::fetch_array($result)) {
  2839. $output[$row['id']] = $row['title'];
  2840. }
  2841. return $output;
  2842. }
  2843. /**
  2844. * Get the course id based on the original id and field name in the extra fields. Returns 0 if course was not found
  2845. *
  2846. * @param string Original course id
  2847. * @param string Original field name
  2848. * @return int Course id
  2849. */
  2850. public static function get_course_id_from_original_id($original_course_id_value, $original_course_id_name) {
  2851. $t_cfv = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  2852. $table_field = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  2853. $sql_course = "SELECT id FROM $table_field cf INNER JOIN $t_cfv cfv ON cfv.field_id=cf.id WHERE field_variable='$original_course_id_name' AND field_value='$original_course_id_value'";
  2854. $res = Database::query($sql_course);
  2855. $row = Database::fetch_object($res);
  2856. if($row != false) {
  2857. return $row->id;
  2858. } else {
  2859. return 0;
  2860. }
  2861. }
  2862. /**
  2863. * Display code for one specific course a logged in user is subscribed to.
  2864. * Shows a link to the course, what's new icons...
  2865. *
  2866. * $my_course['d'] - course directory
  2867. * $my_course['i'] - course title
  2868. * $my_course['c'] - visual course code
  2869. * $my_course['k'] - system course code
  2870. *
  2871. * @param array Course details
  2872. * @param integer Session ID
  2873. * @param string CSS class to apply to course entry
  2874. * @param boolean Whether the session is supposedly accessible now (not in the case it has passed and is in invisible/unaccessible mode)
  2875. * @param bool Whether to show the document quick-loader or not
  2876. * @return string The HTML to be printed for the course entry
  2877. *
  2878. * @version 1.0.3
  2879. * @todo refactor into different functions for database calls | logic | display
  2880. * @todo replace single-character $my_course['d'] indices
  2881. * @todo move code for what's new icons to a separate function to clear things up
  2882. * @todo add a parameter user_id so that it is possible to show the courselist of other users (=generalisation). This will prevent having to write a new function for this.
  2883. */
  2884. public static function get_logged_user_course_html($course, $session_id = 0, $load_dirs = false) {
  2885. $course_info = api_get_course_info($course['code']);
  2886. $course_info['id_session'] = $session_id;
  2887. // Display course entry.
  2888. // Show a link to the course, unless the course is closed and user is not course admin.
  2889. $session_url = '';
  2890. $session_title = $course_info['name'];
  2891. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED || $course_info['user_status_in_course'] == COURSEMANAGER) {
  2892. //if ($course_info['user_status_in_course'] == COURSEMANAGER) {
  2893. $session_url = api_get_path(WEB_COURSE_PATH).$course_info['path'].'/?id_session='.$course_info['id_session'];
  2894. $session_title = '<a href="'.api_get_path(WEB_COURSE_PATH).$course_info['path'].'/?id_session='.$course_info['id_session'].'">'.$course_info['name'].'</a>';
  2895. //}
  2896. } else {
  2897. $session_title = $course_info['name'].' '.Display::tag('span',get_lang('CourseClosed'), array('class'=>'item_closed'));
  2898. }
  2899. $params = array();
  2900. $params['icon'] = Display::return_icon('blackboard_blue.png', $course_info['name'], array(), ICON_SIZE_LARGE);
  2901. $params['link'] = $session_url;
  2902. $params['title'] = $session_title;
  2903. $params['right_actions'] = '';
  2904. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED) {
  2905. if ($load_dirs) {
  2906. $params['right_actions'] .= '<a id="document_preview_'.$course_info['real_id'].'_'.$course_info['id_session'].'" class="document_preview" href="javascript:void(0);">'.
  2907. Display::return_icon('folder.png', get_lang('Documents'), array('align' => 'absmiddle'),ICON_SIZE_SMALL).'</a>';
  2908. $params['right_actions'] .= Display::div('', array('id' => 'document_result_'.$course_info['real_id'].'_'.$course_info['id_session'], 'class'=>'document_preview_container'));
  2909. }
  2910. }
  2911. if (api_get_setting('display_coursecode_in_courselist') == 'true') {
  2912. $session_title .= ' ('.$course_info['visual_code'].') ';
  2913. }
  2914. if (api_get_setting('display_teacher_in_courselist') == 'true') {
  2915. $teacher_list = CourseManager::get_teacher_list_from_course_code_to_string($course_info['code'], self::USER_SEPARATOR, true);
  2916. $course_coachs = CourseManager::get_coachs_from_course_to_string($course_info['id_session'], $course['code'], self::USER_SEPARATOR, true);
  2917. $params['teachers'] = $teacher_list;
  2918. $params['coaches'] = $course_coachs;
  2919. }
  2920. $session_title .= isset($course['special_course']) ? ' '.Display::return_icon('klipper.png', get_lang('CourseAutoRegister')) : '';
  2921. // Display the "what's new" icons
  2922. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED) {
  2923. $session_title .= Display :: show_notification($course_info);
  2924. }
  2925. $params['title'] = $session_title;
  2926. $params['extra'] = '';
  2927. $html = self::course_item_html($params, true);
  2928. return $html;
  2929. }
  2930. /**
  2931. *
  2932. * @param string source course code
  2933. * @param int source session id
  2934. * @param string destination course code
  2935. * @param int destination session id
  2936. * @return bool
  2937. */
  2938. public static function copy_course($source_course_code, $source_session_id, $destination_course_code, $destination_session_id, $params = array()) {
  2939. require_once api_get_path(SYS_CODE_PATH).'coursecopy/classes/CourseBuilder.class.php';
  2940. require_once api_get_path(SYS_CODE_PATH).'coursecopy/classes/CourseRestorer.class.php';
  2941. require_once api_get_path(SYS_CODE_PATH).'coursecopy/classes/CourseSelectForm.class.php';
  2942. $course_info = api_get_course_info($source_course_code);
  2943. if (!empty($course_info)) {
  2944. $cb = new CourseBuilder('', $course_info);
  2945. $course = $cb->build($source_session_id, $source_course_code, true);
  2946. $course_restorer = new CourseRestorer($course);
  2947. $course_restorer->skip_content = $params;
  2948. $course_restorer->restore($destination_course_code, $destination_session_id, true, true);
  2949. return true;
  2950. }
  2951. return false;
  2952. }
  2953. /**
  2954. * A simpler version of the copy_course, the function creates an empty course with an autogenerated course code
  2955. *
  2956. * @param string new course title
  2957. * @param string source course code
  2958. * @param int source session id
  2959. * @param int destination session id
  2960. * @param bool new copied tools (Exercises and LPs)will be set to invisible by default?
  2961. *
  2962. * @return array
  2963. */
  2964. public static function copy_course_simple($new_title, $source_course_code, $source_session_id = 0, $destination_session_id = 0, $params = array()) {
  2965. $source_course_info = api_get_course_info($source_course_code);
  2966. if (!empty($source_course_info)) {
  2967. $new_course_code = self::generate_nice_next_course_code($source_course_code);
  2968. if ($new_course_code) {
  2969. $new_course_info = self::create_course($new_title, $new_course_code, false);
  2970. if (!empty($new_course_info['code'])) {
  2971. $result = self::copy_course($source_course_code, $source_session_id, $new_course_info['code'], $destination_session_id, $params);
  2972. if ($result) {
  2973. return $new_course_info;
  2974. }
  2975. }
  2976. }
  2977. }
  2978. return false;
  2979. }
  2980. /**
  2981. * Creates a new course code based in a given code
  2982. *
  2983. * @param string wanted code
  2984. * <code> $wanted_code = 'curse' if there are in the DB codes like curse1 curse2 the function will return: course3</code>
  2985. * if the course code doest not exist in the DB the same course code will be returned
  2986. * @return string wanted unused code
  2987. */
  2988. public static function generate_nice_next_course_code($wanted_code) {
  2989. require_once api_get_path(LIBRARY_PATH).'add_course.lib.inc.php';
  2990. $course_code_ok = !self::course_code_exists($wanted_code);
  2991. if (!$course_code_ok) {
  2992. $wanted_code = self::generate_course_code($wanted_code);
  2993. $table = Database::get_main_table(TABLE_MAIN_COURSE);
  2994. $wanted_code = Database::escape_string($wanted_code);
  2995. $sql = "SELECT count(*) as count FROM $table WHERE code LIKE '$wanted_code%'";
  2996. $result = Database::query($sql);
  2997. if (Database::num_rows($result) > 0 ) {
  2998. $row = Database::fetch_array($result);
  2999. $count = $row['count'] + 1;
  3000. $wanted_code = $wanted_code.'_'.$count;
  3001. $result = api_get_course_info($wanted_code);
  3002. if (empty($result)) {
  3003. return $wanted_code;
  3004. }
  3005. }
  3006. return false;
  3007. }
  3008. return $wanted_code;
  3009. }
  3010. /**
  3011. * Gets the status of the users agreement in a course course-session
  3012. *
  3013. * @param int user id
  3014. * @param string course code
  3015. * @param int session id
  3016. * @return boolean
  3017. */
  3018. public static function is_user_accepted_legal($user_id, $course_code, $session_id = null) {
  3019. $user_id = intval($user_id);
  3020. $course_code = Database::escape_string($course_code);
  3021. $session_id = intval($session_id);
  3022. if (empty($session_id)) {
  3023. $table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  3024. $sql = "SELECT legal_agreement FROM $table
  3025. WHERE user_id = $user_id AND course_code ='$course_code' ";
  3026. $result = Database::query($sql);
  3027. if (Database::num_rows($result) > 0 ) {
  3028. $result = Database::fetch_array($result);
  3029. if ($result['legal_agreement'] == 1 ) {
  3030. return true;
  3031. }
  3032. }
  3033. return false;
  3034. } else {
  3035. $table = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  3036. $sql = "SELECT legal_agreement FROM $table
  3037. WHERE id_user = $user_id AND course_code ='$course_code' AND id_session = $session_id";
  3038. $result = Database::query($sql);
  3039. if (Database::num_rows($result) > 0 ) {
  3040. $result = Database::fetch_array($result);
  3041. if ($result['legal_agreement'] == 1 ) {
  3042. return true;
  3043. }
  3044. }
  3045. return false;
  3046. }
  3047. return false;
  3048. }
  3049. /**
  3050. * Saves the user-course legal agreement
  3051. * @param int user id
  3052. * @param string course code
  3053. * @param int session id
  3054. */
  3055. function save_user_legal($user_id, $course_code, $session_id = null) {
  3056. $user_id = intval($user_id);
  3057. $course_code = Database::escape_string($course_code);
  3058. $session_id = intval($session_id);
  3059. if (empty($session_id)) {
  3060. $table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  3061. $sql = "UPDATE $table SET legal_agreement = '1'
  3062. WHERE user_id = $user_id AND course_code ='$course_code' ";
  3063. $result = Database::query($sql);
  3064. } else {
  3065. $table = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  3066. $sql = "UPDATE $table SET legal_agreement = '1'
  3067. WHERE id_user = $user_id AND course_code = '$course_code' AND id_session = $session_id";
  3068. $result = Database::query($sql);
  3069. }
  3070. }
  3071. public static function get_user_course_vote($user_id, $course_id, $session_id = null, $url_id = null) {
  3072. $table_user_course_vote = Database::get_main_table(TABLE_MAIN_USER_REL_COURSE_VOTE);
  3073. $session_id = !isset($session_id) ? api_get_session_id() : intval($session_id);
  3074. $url_id = empty($url_id) ? api_get_current_access_url_id() : intval($url_id);
  3075. $user_id = intval($user_id);
  3076. if (empty($user_id)) {
  3077. return false;
  3078. }
  3079. $params = array(
  3080. 'user_id' => $user_id,
  3081. 'c_id' => $course_id,
  3082. 'session_id' => $session_id,
  3083. 'url_id' => $url_id
  3084. );
  3085. $result = Database::select('vote', $table_user_course_vote, array('where' => array('user_id = ? AND c_id = ? AND session_id = ? AND url_id = ?' => $params)), 'first');
  3086. if (!empty($result)) {
  3087. return $result['vote'];
  3088. }
  3089. return false;
  3090. }
  3091. public static function get_course_ranking($course_id, $session_id = null, $url_id = null) {
  3092. $table_course_ranking = Database::get_main_table(TABLE_STATISTIC_TRACK_COURSE_RANKING);
  3093. $session_id = !isset($session_id) ? api_get_session_id() : intval($session_id);
  3094. $url_id = empty($url_id) ? api_get_current_access_url_id() : intval($url_id);
  3095. $params = array(
  3096. 'c_id' => $course_id,
  3097. 'session_id' => $session_id,
  3098. 'url_id' => $url_id
  3099. );
  3100. $result = Database::select('c_id, accesses, total_score, users', $table_course_ranking, array('where' => array('c_id = ? AND session_id = ? AND url_id = ?' => $params)), 'first');
  3101. $point_average_in_percentage = 0;
  3102. $point_average_in_star = 0;
  3103. $users_who_voted = 0;
  3104. if (!empty($result['users'])) {
  3105. $users_who_voted = $result['users'];
  3106. $point_average_in_percentage = round($result['total_score']/$result['users'] * 100 / 5, 2);
  3107. $point_average_in_star = round($result['total_score']/$result['users'], 1);
  3108. }
  3109. $result['user_vote'] = false;
  3110. if (!api_is_anonymous()) {
  3111. $result['user_vote'] = self::get_user_course_vote(api_get_user_id(), $course_id, $session_id,$url_id);
  3112. }
  3113. $result['point_average'] = $point_average_in_percentage;
  3114. $result['point_average_star'] = $point_average_in_star;
  3115. $result['users_who_voted'] = $users_who_voted;
  3116. return $result;
  3117. }
  3118. /**
  3119. *
  3120. * Updates the course ranking
  3121. * @param int course id
  3122. * @param int session id
  3123. * @param id url id
  3124. *
  3125. **/
  3126. public static function update_course_ranking($course_id = null, $session_id = null, $url_id = null, $points_to_add = null, $add_access = true, $add_user = true) {
  3127. //Course catalog stats modifications see #4191
  3128. $table_course_ranking = Database::get_main_table(TABLE_STATISTIC_TRACK_COURSE_RANKING);
  3129. $now = api_get_utc_datetime();
  3130. $course_id = empty($course_id) ? api_get_course_int_id() : intval($course_id);
  3131. $session_id = !isset($session_id) ? api_get_session_id() : intval($session_id);
  3132. $url_id = empty($url_id) ? api_get_current_access_url_id() : intval($url_id);
  3133. $params = array(
  3134. 'c_id' => $course_id,
  3135. 'session_id' => $session_id,
  3136. 'url_id' => $url_id,
  3137. 'creation_date' => $now,
  3138. );
  3139. $result = Database::select('id, accesses, total_score, users', $table_course_ranking, array('where' => array('c_id = ? AND session_id = ? AND url_id = ?' => $params)), 'first');
  3140. // Problem here every thime we load the courses/XXXX/index.php course home page we update the access
  3141. if (empty($result)) {
  3142. if ($add_access) {
  3143. $params['accesses'] = 1;
  3144. }
  3145. //The votes and users are empty
  3146. if (isset($points_to_add) && !empty($points_to_add)) {
  3147. $params['total_score'] = intval($points_to_add);
  3148. }
  3149. if ($add_user) {
  3150. $params['users'] = 1;
  3151. }
  3152. $result = Database::insert($table_course_ranking, $params);
  3153. } else {
  3154. $my_params = array();
  3155. if ($add_access) {
  3156. $my_params['accesses'] = intval($result['accesses']) + 1;
  3157. }
  3158. if (isset($points_to_add) && !empty($points_to_add)) {
  3159. $my_params['total_score'] = $result['total_score'] + $points_to_add;
  3160. }
  3161. if ($add_user) {
  3162. $my_params['users'] = $result['users'] + 1;
  3163. }
  3164. if (!empty($my_params)) {
  3165. $result = Database::update($table_course_ranking, $my_params, array('c_id = ? AND session_id = ? AND url_id = ?' => $params));
  3166. }
  3167. }
  3168. return $result;
  3169. }
  3170. /**
  3171. * Add user vote to a course
  3172. *
  3173. * @param int user id
  3174. * @param int vote [1..5]
  3175. * @param int course id
  3176. * @param int session id
  3177. * @param int url id (access_url_id)
  3178. * @return mixed 'added', 'updated' or 'nothing'
  3179. *
  3180. */
  3181. public static function add_course_vote($user_id, $vote, $course_id, $session_id = null, $url_id = null) {
  3182. $table_user_course_vote = Database::get_main_table(TABLE_MAIN_USER_REL_COURSE_VOTE);
  3183. $course_id = empty($course_id) ? api_get_course_int_id() : intval($course_id);
  3184. if (empty($course_id) || empty($user_id)) {
  3185. return false;
  3186. }
  3187. if (!in_array($vote, array(1,2,3,4,5))) {
  3188. return false;
  3189. }
  3190. $session_id = !isset($session_id) ? api_get_session_id() : intval($session_id);
  3191. $url_id = empty($url_id) ? api_get_current_access_url_id() : intval($url_id);
  3192. $vote = intval($vote);
  3193. $params = array(
  3194. 'user_id' => intval($user_id),
  3195. 'c_id' => $course_id,
  3196. 'session_id' => $session_id,
  3197. 'url_id' => $url_id,
  3198. 'vote' => $vote
  3199. );
  3200. $action_done = 'nothing';
  3201. $result = Database::select('id, vote', $table_user_course_vote, array('where' => array('user_id = ? AND c_id = ? AND session_id = ? AND url_id = ?' => $params)), 'first');
  3202. if (empty($result)) {
  3203. $result = Database::insert($table_user_course_vote, $params);
  3204. $points_to_add = $vote;
  3205. $add_user = true;
  3206. $action_done = 'added';
  3207. } else {
  3208. $my_params = array('vote' => $vote);
  3209. $points_to_add = $vote - $result['vote'];
  3210. $add_user = false;
  3211. $result = Database::update($table_user_course_vote, $my_params, array('user_id = ? AND c_id = ? AND session_id = ? AND url_id = ?' => $params));
  3212. $action_done = 'updated';
  3213. }
  3214. //Current points
  3215. if (!empty($points_to_add)) {
  3216. self::update_course_ranking($course_id, $session_id, $url_id, $points_to_add, false, $add_user);
  3217. }
  3218. return $action_done;
  3219. }
  3220. /**
  3221. * Remove course ranking + user votes
  3222. *
  3223. * @param int course id
  3224. * @param int session id
  3225. * @param int url id
  3226. *
  3227. */
  3228. public function remove_course_ranking($course_id, $session_id, $url_id = null) {
  3229. $table_course_ranking = Database::get_main_table(TABLE_STATISTIC_TRACK_COURSE_RANKING);
  3230. $table_user_course_vote = Database::get_main_table(TABLE_MAIN_USER_REL_COURSE_VOTE);
  3231. if (!empty($course_id) && isset($session_id)) {
  3232. $url_id = empty($url_id) ? api_get_current_access_url_id() : intval($url_id);
  3233. $params = array(
  3234. 'c_id' => $course_id,
  3235. 'session_id' => $session_id,
  3236. 'url_id' => $url_id,
  3237. );
  3238. Database::delete($table_course_ranking, array('c_id = ? AND session_id = ? AND url_id = ?' => $params));
  3239. Database::delete($table_user_course_vote, array('c_id = ? AND session_id = ? AND url_id = ?' => $params));
  3240. }
  3241. }
  3242. /**
  3243. * Returns an array with the hottest courses
  3244. * @param int number of days
  3245. * @param int number of hottest courses
  3246. */
  3247. public static function return_hot_courses($days = 30, $limit = 5) {
  3248. global $_configuration;
  3249. $limit = intval($limit);
  3250. //Getting my courses
  3251. $my_course_list = CourseManager::get_courses_list_by_user_id(api_get_user_id());
  3252. $my_course_code_list = array();
  3253. foreach ($my_course_list as $course) {
  3254. $my_course_code_list[$course['real_id']] = $course['real_id'];
  3255. }
  3256. if (api_is_drh()) {
  3257. $courses = CourseManager::get_courses_followed_by_drh(api_get_user_id());
  3258. foreach ($courses as $course) {
  3259. $my_course_code_list[$course['real_id']] = $course['real_id'];
  3260. }
  3261. }
  3262. $table_course_access = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  3263. $table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  3264. $table_course_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  3265. //@todo all dates in the tracking_course_access, last_access are in the DB time (NOW) not UTC
  3266. /*
  3267. $today = api_get_utc_datetime();
  3268. $today_diff = time() -intval($days)*24*60*60;
  3269. $today_diff = api_get_utc_datetime($today_diff);
  3270. * */
  3271. //WHERE login_course_date <= '$today' AND login_course_date >= '$today_diff'
  3272. //$table_course_access table uses the now() and interval ...
  3273. $sql = "SELECT COUNT(course_access_id) course_count, a.course_code, visibility ".
  3274. "FROM $table_course c INNER JOIN $table_course_access a ".
  3275. " ON (c.code = a.course_code) INNER JOIN $table_course_url u ON u.course_code = a.course_code ".
  3276. " WHERE u.access_url_id = ".$_configuration['access_url']." AND".
  3277. " login_course_date <= now() AND ".
  3278. " login_course_date > DATE_SUB(now(), INTERVAL $days DAY) AND".
  3279. " visibility <> '".COURSE_VISIBILITY_CLOSED."'".
  3280. " GROUP BY course_code".
  3281. " ORDER BY course_count DESC".
  3282. " LIMIT $limit";
  3283. $result = Database::query($sql);
  3284. $courses = array();
  3285. if (Database::num_rows($result)) {
  3286. $courses = Database::store_result($result, 'ASSOC');
  3287. $courses = self::process_hot_course_item($courses, $my_course_code_list);
  3288. }
  3289. return $courses;
  3290. }
  3291. public static function process_hot_course_item($courses, $my_course_code_list = array()) {
  3292. $ajax_url = api_get_path(WEB_AJAX_PATH).'course.ajax.php?a=add_course_vote';
  3293. foreach ($courses as &$my_course) {
  3294. $course_info = api_get_course_info($my_course['course_code']);
  3295. $my_course['extra_info'] = $course_info;
  3296. $my_course['extra_info']['go_to_course_button'] = '';
  3297. $my_course['extra_info']['register_button'] = '';
  3298. $access_link = self::get_access_link_by_user(api_get_user_id(), $course_info, $my_course_code_list);
  3299. //Course visibility
  3300. if ($access_link && in_array('register', $access_link)) {
  3301. $stok = Security::get_token();
  3302. $my_course['extra_info']['register_button'] = Display::url(get_lang('Subscribe'), api_get_path(WEB_COURSE_PATH).$course_info['path'].'/index.php?action=subscribe&amp;sec_token='.$stok, array('class' => 'btn btn-primary'));
  3303. }
  3304. if ($access_link && in_array('enter', $access_link)) {
  3305. $my_course['extra_info']['go_to_course_button'] = Display::url(get_lang('GoToCourse'), api_get_path(WEB_COURSE_PATH).$course_info['path'].'/index.php', array('class' => 'btn btn-primary'));
  3306. }
  3307. //Description
  3308. $my_course['extra_info']['description_button'] = '';
  3309. if ($course_info['visibility'] == COURSE_VISIBILITY_OPEN_WORLD || in_array($course_info['real_id'], $my_course_code_list) ) {
  3310. $my_course['extra_info']['description_button'] = Display::url(get_lang('Description'), api_get_path(WEB_AJAX_PATH).'course_home.ajax.php?a=show_course_information&amp;code='.$course_info['code'], array('class' => 'ajax btn'));
  3311. }
  3312. $my_course['extra_info']['teachers'] = CourseManager::get_teacher_list_from_course_code_to_string($course_info['code']);
  3313. $point_info = self::get_course_ranking($course_info['real_id'], 0);
  3314. $my_course['extra_info']['rating_html'] = Display::return_rating_system('star_'.$course_info['real_id'], $ajax_url.'&amp;course_id='.$course_info['real_id'], $point_info);
  3315. }
  3316. return $courses;
  3317. }
  3318. public static function return_most_accessed_courses($limit = 5) {
  3319. $table_course_ranking = Database::get_main_table(TABLE_STATISTIC_TRACK_COURSE_RANKING);
  3320. $params['url_id'] = api_get_current_access_url_id();
  3321. $result = Database::select('c_id, accesses, total_score, users', $table_course_ranking, array('where' => array('url_id = ?' => $params), 'order' => 'accesses DESC', 'limit' => $limit), 'all', true);
  3322. return $result;
  3323. }
  3324. /**
  3325. *
  3326. *
  3327. * @return ResultSet
  3328. */
  3329. static function list_inactive_courses($ceiling, $visibility_level = COURSE_VISIBILITY_REGISTERED) {
  3330. $ceiling = is_numeric($ceiling) ? (int) $ceiling : strtotime($ceiling);
  3331. $ceiling = date('Y-m-d H:i:s', $ceiling);
  3332. $visibility_level = $visibility_level ? $visibility_level : '0';
  3333. $table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  3334. $table_category = Database::get_main_table(TABLE_MAIN_CATEGORY);
  3335. $sql = "SELECT
  3336. c.*,
  3337. cat.name AS category
  3338. FROM
  3339. $table_course AS c
  3340. LEFT JOIN
  3341. $table_category AS cat
  3342. ON
  3343. c.category_code = cat.code
  3344. WHERE
  3345. c.visibility >= $visibility_level AND
  3346. c.last_visit<='$ceiling'
  3347. ";
  3348. return ResultSet::create($sql);
  3349. }
  3350. /**
  3351. * Get courses count
  3352. * @param int Access URL ID (optional)
  3353. * @return int Number of courses
  3354. */
  3355. public static function count_courses($access_url_id = null) {
  3356. $table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  3357. $table_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  3358. $sql = "SELECT count(id) FROM $table_course c";
  3359. if (!empty($access_url_id) && $access_url_id == intval($access_url_id)) {
  3360. $sql .= ", $table_course_rel_access_url u WHERE c.code = u.course_code AND u.access_url_id = $access_url_id";
  3361. }
  3362. $res = Database::query($sql);
  3363. $row = Database::fetch_row($res);
  3364. return $row[0];
  3365. }
  3366. /**
  3367. * Return a link to go to the course, validating the visibility of the
  3368. * course and the user status
  3369. * @param int User ID
  3370. * @param array Course details array
  3371. * @param array List of courses to which the user is subscribed (if not provided, will be generated)
  3372. * @return mixed 'enter' for a link to go to the course or 'register' for a link to subscribe, or false if no access
  3373. */
  3374. static function get_access_link_by_user($uid, $course, $user_courses = array()) {
  3375. if (empty($uid) or empty($course)) { return false; }
  3376. if (empty($user_courses)) {
  3377. // get the array of courses to which the user is subscribed
  3378. $user_courses = CourseManager::get_courses_list_by_user_id($uid);
  3379. foreach ($user_courses as $k => $v) {
  3380. $user_courses[$k] = $v['real_id'];
  3381. }
  3382. }
  3383. if (!isset($course['real_id']) && empty($course['real_id'])) {
  3384. $course = api_get_course_info($course['code']);
  3385. }
  3386. $is_admin = api_is_platform_admin_by_id($uid);
  3387. $options = array();
  3388. // Register button
  3389. if (!api_is_anonymous($uid) &&
  3390. !$is_admin &&
  3391. (
  3392. ($course['visibility'] == COURSE_VISIBILITY_OPEN_WORLD || $course['visibility'] == COURSE_VISIBILITY_OPEN_PLATFORM) ||
  3393. $course['visibility'] == COURSE_VISIBILITY_REGISTERED && $course['subscribe'] == SUBSCRIBE_ALLOWED
  3394. ) &&
  3395. $course['subscribe'] == SUBSCRIBE_ALLOWED &&
  3396. (!in_array($course['real_id'], $user_courses) || empty($user_courses))
  3397. ) {
  3398. $options[]= 'register';
  3399. }
  3400. // Go To Course button (only if admin, if course public or if student already subscribed)
  3401. if ($is_admin ||
  3402. $course['visibility'] == COURSE_VISIBILITY_OPEN_WORLD && empty($course['registration_code']) ||
  3403. (api_user_is_login($uid) && $course['visibility'] == COURSE_VISIBILITY_OPEN_PLATFORM && empty($course['registration_code']) ) ||
  3404. (in_array($course['real_id'], $user_courses) && $course['visibility'] != COURSE_VISIBILITY_CLOSED)
  3405. ) {
  3406. $options[]= 'enter';
  3407. }
  3408. return $options;
  3409. }
  3410. // TODO: Such a function might be useful in other places too. It might be moved in the CourseManager class.
  3411. // Also, the function might be upgraded for avoiding code duplications.
  3412. static function generate_course_code($course_title, $encoding = null) {
  3413. if (empty($encoding)) {
  3414. $encoding = api_get_system_encoding();
  3415. }
  3416. return substr(preg_replace('/[^A-Z0-9]/', '', strtoupper(api_transliterate($course_title, 'X', $encoding))), 0, CourseManager::MAX_COURSE_LENGTH_CODE);
  3417. }
  3418. /**
  3419. * Defines the four needed keys to create a course based on several parameters.
  3420. * @param string The code you want for this course
  3421. * @param string Prefix added for ALL keys
  3422. * @param string Prefix added for databases only
  3423. * @param string Prefix added for paths only
  3424. * @param bool Add unique prefix
  3425. * @param bool Use code-independent keys
  3426. * @return array An array with the needed keys ['currentCourseCode'], ['currentCourseId'], ['currentCourseDbName'], ['currentCourseRepository']
  3427. * @todo Eliminate the global variables.
  3428. */
  3429. static function define_course_keys($wanted_code, $prefix_for_all = '', $prefix_for_base_name = '', $prefix_for_path = '', $add_unique_prefix = false, $use_code_indepedent_keys = true) {
  3430. global $prefixAntiNumber, $_configuration;
  3431. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  3432. $wanted_code = self::generate_course_code($wanted_code);
  3433. $keys_course_code = $wanted_code;
  3434. if (!$use_code_indepedent_keys) {
  3435. $wanted_code = '';
  3436. }
  3437. if ($add_unique_prefix) {
  3438. $unique_prefix = substr(md5(uniqid(rand())), 0, 10);
  3439. } else {
  3440. $unique_prefix = '';
  3441. }
  3442. $keys = array();
  3443. $final_suffix = array('CourseId' => '', 'CourseDb' => '', 'CourseDir' => '');
  3444. $limit_numb_try = 100;
  3445. $keys_are_unique = false;
  3446. $try_new_fsc_id = $try_new_fsc_db = $try_new_fsc_dir = 0;
  3447. while (!$keys_are_unique) {
  3448. $keys_course_id = $prefix_for_all . $unique_prefix . $wanted_code . $final_suffix['CourseId'];
  3449. //$keys_course_db_name = $prefix_for_base_name . $unique_prefix . strtoupper($keys_course_id) . $final_suffix['CourseDb'];
  3450. $keys_course_repository = $prefix_for_path . $unique_prefix . $wanted_code . $final_suffix['CourseDir'];
  3451. $keys_are_unique = true;
  3452. // Check whether they are unique.
  3453. $query = "SELECT 1 FROM ".$course_table." WHERE code='".$keys_course_id."' LIMIT 0,1";
  3454. $result = Database::query($query);
  3455. //if ($keys_course_id == DEFAULT_COURSE || Database::num_rows($result)) {
  3456. if (Database::num_rows($result)) {
  3457. $keys_are_unique = false;
  3458. $try_new_fsc_id ++;
  3459. $final_suffix['CourseId'] = substr(md5(uniqid(rand())), 0, 4);
  3460. }
  3461. if (file_exists(api_get_path(SYS_COURSE_PATH).$keys_course_repository)) {
  3462. $keys_are_unique = false;
  3463. $try_new_fsc_dir ++;
  3464. $final_suffix['CourseDir'] = substr(md5(uniqid(rand())), 0, 4);
  3465. }
  3466. if (($try_new_fsc_id + $try_new_fsc_db + $try_new_fsc_dir) > $limit_numb_try) {
  3467. return $keys;
  3468. }
  3469. }
  3470. $keys['currentCourseCode'] = $keys_course_code;
  3471. $keys['currentCourseId'] = $keys_course_id;
  3472. $keys['currentCourseRepository'] = $keys_course_repository;
  3473. return $keys;
  3474. }
  3475. /**
  3476. * Initializes a file repository for a newly created course.
  3477. */
  3478. static function prepare_course_repository($course_repository, $course_code) {
  3479. $perm = api_get_permissions_for_new_directories();
  3480. $perm_file = api_get_permissions_for_new_files();
  3481. $htmlpage = "<!DOCTYPE html>\n<html lang=\"en\">\n <head>\n <meta charset=\"utf-8\">\n <title>Not authorized</title>\n </head>\n <body>\n </body>\n</html>";
  3482. $cp = api_get_path(SYS_COURSE_PATH).$course_repository;
  3483. //Creating document folder
  3484. mkdir($cp, $perm);
  3485. mkdir($cp . '/document', $perm);
  3486. $cpt = $cp.'/document/index.html';
  3487. $fd = fopen($cpt, 'w');
  3488. fwrite($fd, $htmlpage);
  3489. fclose($fd);
  3490. /*
  3491. @chmod($cpt, $perm_file);
  3492. @copy($cpt, $cp . '/document/index.html');
  3493. mkdir($cp . '/document/images', $perm);
  3494. @copy($cpt, $cp . '/document/images/index.html');
  3495. mkdir($cp . '/document/images/gallery/', $perm);
  3496. @copy($cpt, $cp . '/document/images/gallery/index.html');
  3497. mkdir($cp . '/document/shared_folder/', $perm);
  3498. @copy($cpt, $cp . '/document/shared_folder/index.html');
  3499. mkdir($cp . '/document/audio', $perm);
  3500. @copy($cpt, $cp . '/document/audio/index.html');
  3501. mkdir($cp . '/document/flash', $perm);
  3502. @copy($cpt, $cp . '/document/flash/index.html');
  3503. mkdir($cp . '/document/video', $perm);
  3504. @copy($cpt, $cp . '/document/video/index.html'); */
  3505. //Creatind dropbox folder
  3506. mkdir($cp . '/dropbox', $perm);
  3507. $cpt = $cp.'/dropbox/index.html';
  3508. $fd = fopen($cpt, 'w');
  3509. fwrite($fd, $htmlpage);
  3510. fclose($fd);
  3511. @chmod($cpt, $perm_file);
  3512. mkdir($cp . '/group', $perm);
  3513. @copy($cpt, $cp . '/group/index.html');
  3514. mkdir($cp . '/page', $perm);
  3515. @copy($cpt, $cp . '/page/index.html');
  3516. mkdir($cp . '/scorm', $perm);
  3517. @copy($cpt, $cp . '/scorm/index.html');
  3518. mkdir($cp . '/upload', $perm);
  3519. @copy($cpt, $cp . '/upload/index.html');
  3520. mkdir($cp . '/upload/forum', $perm);
  3521. @copy($cpt, $cp . '/upload/forum/index.html');
  3522. mkdir($cp . '/upload/forum/images', $perm);
  3523. @copy($cpt, $cp . '/upload/forum/images/index.html');
  3524. mkdir($cp . '/upload/test', $perm);
  3525. @copy($cpt, $cp . '/upload/test/index.html');
  3526. mkdir($cp . '/upload/blog', $perm);
  3527. @copy($cpt, $cp . '/upload/blog/index.html');
  3528. mkdir($cp . '/upload/learning_path', $perm);
  3529. @copy($cpt, $cp . '/upload/learning_path/index.html');
  3530. mkdir($cp . '/upload/learning_path/images', $perm);
  3531. @copy($cpt, $cp . '/upload/learning_path/images/index.html');
  3532. mkdir($cp . '/upload/calendar', $perm);
  3533. @copy($cpt, $cp . '/upload/calendar/index.html');
  3534. mkdir($cp . '/upload/calendar/images', $perm);
  3535. @copy($cpt, $cp . '/upload/calendar/images/index.html');
  3536. mkdir($cp . '/work', $perm);
  3537. @copy($cpt, $cp . '/work/index.html');
  3538. mkdir($cp . '/upload/announcements', $perm);
  3539. @copy($cpt, $cp . '/upload/announcements/index.html');
  3540. mkdir($cp . '/upload/announcements/images', $perm);
  3541. @copy($cpt, $cp . '/upload/announcements/images/index.html');
  3542. //Oral expression question type
  3543. mkdir($cp . '/exercises', $perm);
  3544. @copy($cpt, $cp . '/exercises/index.html');
  3545. // Create .htaccess in the dropbox directory.
  3546. $fp = fopen($cp . '/dropbox/.htaccess', 'w');
  3547. fwrite($fp, "AuthName AllowLocalAccess
  3548. AuthType Basic
  3549. order deny,allow
  3550. deny from all
  3551. php_flag zlib.output_compression off");
  3552. fclose($fp);
  3553. // Build index.php of the course.
  3554. $fd = fopen($cp . '/index.php', 'w');
  3555. // str_replace() removes \r that cause squares to appear at the end of each line
  3556. //@todo fix the harcoded include
  3557. $string = str_replace("\r", "", "<?" . "php
  3558. \$cidReq = \"$course_code\";
  3559. \$dbname = \"$course_code\";
  3560. include(\"".api_get_path(SYS_CODE_PATH)."course_home/course_home.php\");
  3561. ?>");
  3562. fwrite($fd, $string);
  3563. @chmod($cp . '/index.php',$perm_file);
  3564. return 0;
  3565. }
  3566. static function get_course_tables() {
  3567. $tables = array();
  3568. $tables[]= 'tool';
  3569. $tables[]= 'tool_intro';
  3570. $tables[]= 'group_info';
  3571. $tables[]= 'group_category';
  3572. $tables[]= 'group_rel_user';
  3573. $tables[]= 'group_rel_tutor';
  3574. $tables[]= 'item_property';
  3575. $tables[]= 'userinfo_content';
  3576. $tables[]= 'userinfo_def';
  3577. $tables[]= 'course_description';
  3578. $tables[]= 'calendar_event';
  3579. $tables[]= 'calendar_event_repeat';
  3580. $tables[]= 'calendar_event_repeat_not';
  3581. $tables[]= 'calendar_event_attachment';
  3582. $tables[]= 'announcement';
  3583. $tables[]= 'announcement_attachment';
  3584. $tables[]= 'resource';
  3585. $tables[]= 'student_publication';
  3586. $tables[]= 'student_publication_assignment';
  3587. $tables[]= 'document';
  3588. $tables[]= 'forum_category';
  3589. $tables[]= 'forum_forum';
  3590. $tables[]= 'forum_thread';
  3591. $tables[]= 'forum_post';
  3592. $tables[]= 'forum_mailcue';
  3593. $tables[]= 'forum_attachment';
  3594. $tables[]= 'forum_notification';
  3595. $tables[]= 'forum_thread_qualify';
  3596. $tables[]= 'forum_thread_qualify_log';
  3597. $tables[]= 'link';
  3598. $tables[]= 'link_category';
  3599. $tables[]= 'online_connected';
  3600. $tables[]= 'online_link';
  3601. $tables[]= 'chat_connected';
  3602. $tables[]= 'quiz';
  3603. $tables[]= 'quiz_rel_question';
  3604. $tables[]= 'quiz_question';
  3605. $tables[]= 'quiz_answer';
  3606. $tables[]= 'quiz_question_option';
  3607. $tables[]= 'quiz_question_category';
  3608. $tables[]= 'quiz_question_rel_category';
  3609. $tables[]= 'dropbox_post';
  3610. $tables[]= 'dropbox_file';
  3611. $tables[]= 'dropbox_person';
  3612. $tables[]= 'dropbox_category';
  3613. $tables[]= 'dropbox_feedback';
  3614. $tables[]= 'lp';
  3615. $tables[]= 'lp_item';
  3616. $tables[]= 'lp_view';
  3617. $tables[]= 'lp_item_view';
  3618. $tables[]= 'lp_iv_interaction';
  3619. $tables[]= 'lp_iv_objective';
  3620. $tables[]= 'blog';
  3621. $tables[]= 'blog_comment';
  3622. $tables[]= 'blog_post';
  3623. $tables[]= 'blog_rating';
  3624. $tables[]= 'blog_rel_user';
  3625. $tables[]= 'blog_task';
  3626. $tables[]= 'blog_task_rel_user';
  3627. $tables[]= 'blog_attachment';
  3628. $tables[]= 'permission_group';
  3629. $tables[]= 'permission_user';
  3630. $tables[]= 'permission_task';
  3631. $tables[]= 'role';
  3632. $tables[]= 'role_group';
  3633. $tables[]= 'role_permissions';
  3634. $tables[]= 'role_user';
  3635. $tables[]= 'survey';
  3636. $tables[]= 'survey_question';
  3637. $tables[]= 'survey_question_option';
  3638. $tables[]= 'survey_invitation';
  3639. $tables[]= 'survey_answer';
  3640. $tables[]= 'survey_group';
  3641. $tables[]= 'wiki';
  3642. $tables[]= 'wiki_conf';
  3643. $tables[]= 'wiki_discuss';
  3644. $tables[]= 'wiki_mailcue';
  3645. $tables[]= 'course_setting';
  3646. $tables[]= 'glossary';
  3647. $tables[]= 'notebook';
  3648. $tables[]= 'attendance';
  3649. $tables[]= 'attendance_sheet';
  3650. $tables[]= 'attendance_calendar';
  3651. $tables[]= 'attendance_result';
  3652. $tables[]= 'attendance_sheet_log';
  3653. $tables[]= 'thematic';
  3654. $tables[]= 'thematic_plan';
  3655. $tables[]= 'thematic_advance';
  3656. $tables[]= 'metadata';
  3657. return $tables;
  3658. }
  3659. static function browse_folders($path, $files, $media) {
  3660. if ($media == 'images') {
  3661. $code_path = api_get_path(SYS_CODE_PATH).'default_course_document/images/';
  3662. }
  3663. if ($media == 'audio') {
  3664. $code_path = api_get_path(SYS_CODE_PATH).'default_course_document/audio/';
  3665. }
  3666. if ($media == 'flash') {
  3667. $code_path = api_get_path(SYS_CODE_PATH).'default_course_document/flash/';
  3668. }
  3669. if ($media == 'video') {
  3670. $code_path = api_get_path(SYS_CODE_PATH).'default_course_document/video/';
  3671. }
  3672. if ($media == 'certificates') {
  3673. $code_path = api_get_path(SYS_CODE_PATH).'default_course_document/certificates/';
  3674. }
  3675. if (is_dir($path)) {
  3676. $handle = opendir($path);
  3677. while (false !== ($file = readdir($handle))) {
  3678. if (is_dir($path.$file) && strpos($file, '.') !== 0) {
  3679. $files[]['dir'] = str_replace($code_path, '', $path.$file.'/');
  3680. $files = self::browse_folders($path.$file.'/', $files, $media);
  3681. } elseif (is_file($path.$file) && strpos($file, '.') !== 0) {
  3682. $files[]['file'] = str_replace($code_path, '', $path.$file);
  3683. }
  3684. }
  3685. }
  3686. return $files;
  3687. }
  3688. static function sort_pictures($files, $type) {
  3689. $pictures = array();
  3690. foreach ($files as $key => $value){
  3691. if ($value[$type] != '') {
  3692. $pictures[][$type] = $value[$type];
  3693. }
  3694. }
  3695. return $pictures;
  3696. }
  3697. /**
  3698. * Fills the course repository with some example content.
  3699. * @version 1.2
  3700. * @deprecated this function has been merged into the fill_db_course
  3701. */
  3702. static function fill_course_repository($course_repository, $fill_with_exemplary_content = null) {
  3703. if (is_null($fill_with_exemplary_content)) {
  3704. $fill_with_exemplary_content = api_get_setting('example_material_course_creation') != 'false';
  3705. }
  3706. $default_document_array = array();
  3707. if ($fill_with_exemplary_content) {
  3708. $sys_course_path = api_get_path(SYS_COURSE_PATH);
  3709. $perm = api_get_permissions_for_new_directories();
  3710. $perm_file = api_get_permissions_for_new_files();
  3711. $img_code_path = api_get_path(SYS_CODE_PATH).'default_course_document/images/';
  3712. $audio_code_path = api_get_path(SYS_CODE_PATH).'default_course_document/audio/';
  3713. $flash_code_path = api_get_path(SYS_CODE_PATH).'default_course_document/flash/';
  3714. $video_code_path = api_get_path(SYS_CODE_PATH).'default_course_document/video/';
  3715. $cert_code_path = api_get_path(SYS_CODE_PATH).'default_course_document/certificates/';
  3716. $course_documents_folder_images = $sys_course_path.$course_repository.'/document/images/gallery/';
  3717. $course_documents_folder_audio = $sys_course_path.$course_repository.'/document/audio/';
  3718. $course_documents_folder_flash = $sys_course_path.$course_repository.'/document/flash/';
  3719. $course_documents_folder_video = $sys_course_path.$course_repository.'/document/video/';
  3720. $course_documents_folder_cert = $sys_course_path.$course_repository.'/document/certificates/';
  3721. /* Images */
  3722. $files = array();
  3723. $files = self::browse_folders($img_code_path, $files, 'images');
  3724. $pictures_array = self::sort_pictures($files, 'dir');
  3725. $pictures_array = array_merge($pictures_array, self::sort_pictures($files, 'file'));
  3726. if (!is_dir($course_documents_folder_images)) {
  3727. mkdir($course_documents_folder_images,$perm);
  3728. }
  3729. $handle = opendir($img_code_path);
  3730. foreach ($pictures_array as $key => $value) {
  3731. if ($value['dir'] != '') {
  3732. mkdir($course_documents_folder_images.$value['dir'], $perm);
  3733. }
  3734. if ($value['file'] != '') {
  3735. copy($img_code_path.$value['file'], $course_documents_folder_images.$value['file']);
  3736. chmod($course_documents_folder_images.$value['file'], $perm_file);
  3737. }
  3738. }
  3739. // Trainer thumbnails fix.
  3740. $path_thumb = mkdir($course_documents_folder_images.'trainer/.thumbs', $perm);
  3741. $handle = opendir($img_code_path.'trainer/.thumbs/');
  3742. while (false !== ($file = readdir($handle))) {
  3743. if (is_file($img_code_path.'trainer/.thumbs/'.$file)) {
  3744. copy($img_code_path.'trainer/.thumbs/'.$file, $course_documents_folder_images.'trainer/.thumbs/'.$file);
  3745. chmod($course_documents_folder_images.'trainer/.thumbs/'.$file, $perm_file);
  3746. }
  3747. }
  3748. $default_document_array['images'] = $pictures_array;
  3749. /* Audio */
  3750. $files = array();
  3751. $files = self::browse_folders($audio_code_path, $files, 'audio');
  3752. $audio_array = self::sort_pictures($files, 'dir');
  3753. $audio_array = array_merge($audio_array, self::sort_pictures($files, 'file'));
  3754. if (!is_dir($course_documents_folder_audio)) {
  3755. mkdir($course_documents_folder_audio, $perm);
  3756. }
  3757. $handle = opendir($audio_code_path);
  3758. foreach ($audio_array as $key => $value){
  3759. if ($value['dir'] != '') {
  3760. mkdir($course_documents_folder_audio.$value['dir'], $perm);
  3761. }
  3762. if ($value['file'] != '') {
  3763. copy($audio_code_path.$value['file'], $course_documents_folder_audio.$value['file']);
  3764. chmod($course_documents_folder_audio.$value['file'], $perm_file);
  3765. }
  3766. }
  3767. $default_document_array['audio'] = $audio_array;
  3768. /*
  3769. * Flash
  3770. */
  3771. $files = array();
  3772. $files = self::browse_folders($flash_code_path, $files, 'flash');
  3773. $flash_array = self::sort_pictures($files, 'dir');
  3774. $flash_array = array_merge($flash_array, self::sort_pictures($files, 'file'));
  3775. if (!is_dir($course_documents_folder_flash)) {
  3776. mkdir($course_documents_folder_flash, $perm);
  3777. }
  3778. $handle = opendir($flash_code_path);
  3779. foreach ($flash_array as $key => $value) {
  3780. if ($value['dir'] != '') {
  3781. mkdir($course_documents_folder_flash.$value['dir'], $perm);
  3782. }
  3783. if ($value['file'] != '') {
  3784. copy($flash_code_path.$value['file'], $course_documents_folder_flash.$value['file']);
  3785. chmod($course_documents_folder_flash.$value['file'], $perm_file);
  3786. }
  3787. }
  3788. $default_document_array['flash'] = $flash_array;
  3789. /* Video */
  3790. $files = self::browse_folders($video_code_path, $files, 'video');
  3791. $video_array = self::sort_pictures($files, 'dir');
  3792. $video_array = array_merge($video_array, self::sort_pictures($files, 'file'));
  3793. if (!is_dir($course_documents_folder_video)) {
  3794. mkdir($course_documents_folder_video, $perm);
  3795. }
  3796. $handle = opendir($video_code_path);
  3797. foreach ($video_array as $key => $value) {
  3798. if ($value['dir'] != '') {
  3799. @mkdir($course_documents_folder_video.$value['dir'], $perm);
  3800. }
  3801. if ($value['file'] != '') {
  3802. copy($video_code_path.$value['file'], $course_documents_folder_video.$value['file']);
  3803. chmod($course_documents_folder_video.$value['file'], $perm_file);
  3804. }
  3805. }
  3806. $default_document_array['video'] = $video_array;
  3807. /* Certificates */
  3808. $files = self::browse_folders($cert_code_path, $files, 'certificates');
  3809. $cert_array = self::sort_pictures($files, 'dir'); //only one file so far
  3810. $cert_array = array_merge($cert_array, self::sort_pictures($files, 'file'));
  3811. if (!is_dir($course_documents_folder_cert)) {
  3812. mkdir($course_documents_folder_cert, $perm);
  3813. }
  3814. $handle = opendir($cert_code_path);
  3815. foreach ($cert_array as $key => $value) {
  3816. if ($value['dir'] != '') {
  3817. @mkdir($course_documents_folder_cert.$value['dir'], $perm);
  3818. }
  3819. if ($value['file'] != '') {
  3820. copy($cert_code_path.$value['file'], $course_documents_folder_cert.$value['file']);
  3821. chmod($course_documents_folder_cert.$value['file'], $perm_file);
  3822. }
  3823. }
  3824. $default_document_array['cert'] = $cert_array;
  3825. }
  3826. return $default_document_array;
  3827. }
  3828. /**
  3829. * Fills the course database with some required content and example content.
  3830. * @version 1.2
  3831. */
  3832. static function fill_db_course($course_id, $course_repository, $language, $fill_with_exemplary_content = null) {
  3833. if (is_null($fill_with_exemplary_content)) {
  3834. $fill_with_exemplary_content = api_get_setting('example_material_course_creation') != 'false';
  3835. }
  3836. $course_id = intval($course_id);
  3837. if (empty($course_id)) {
  3838. return false;
  3839. }
  3840. $now = api_get_utc_datetime(time());
  3841. $tbl_course_homepage = Database::get_course_table(TABLE_TOOL_LIST);
  3842. $TABLEINTROS = Database::get_course_table(TABLE_TOOL_INTRO);
  3843. $TABLEGROUPCATEGORIES = Database::get_course_table(TABLE_GROUP_CATEGORY);
  3844. $TABLEITEMPROPERTY = Database::get_course_table(TABLE_ITEM_PROPERTY);
  3845. $TABLETOOLAGENDA = Database::get_course_table(TABLE_AGENDA);
  3846. $TABLETOOLANNOUNCEMENTS = Database::get_course_table(TABLE_ANNOUNCEMENT);
  3847. $TABLETOOLDOCUMENT = Database::get_course_table(TABLE_DOCUMENT);
  3848. $TABLETOOLLINK = Database::get_course_table(TABLE_LINK);
  3849. $TABLEQUIZ = Database::get_course_table(TABLE_QUIZ_TEST);
  3850. $TABLEQUIZQUESTION = Database::get_course_table(TABLE_QUIZ_TEST_QUESTION);
  3851. $TABLEQUIZQUESTIONLIST = Database::get_course_table(TABLE_QUIZ_QUESTION);
  3852. $TABLEQUIZANSWERSLIST = Database::get_course_table(TABLE_QUIZ_ANSWER);
  3853. $TABLESETTING = Database::get_course_table(TABLE_COURSE_SETTING);
  3854. $TABLEFORUMCATEGORIES = Database::get_course_table(TABLE_FORUM_CATEGORY);
  3855. $TABLEFORUMS = Database::get_course_table(TABLE_FORUM);
  3856. $TABLEFORUMTHREADS = Database::get_course_table(TABLE_FORUM_THREAD);
  3857. $TABLEFORUMPOSTS = Database::get_course_table(TABLE_FORUM_POST);
  3858. $TABLEGRADEBOOK = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY);
  3859. $TABLEGRADEBOOKLINK = Database::get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
  3860. $TABLEGRADEBOOKCERT = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CERTIFICATE);
  3861. include api_get_path(SYS_CODE_PATH).'lang/english/create_course.inc.php';
  3862. $file_to_include = api_get_path(SYS_CODE_PATH).'lang/'.$language.'/create_course.inc.php';
  3863. if (file_exists($file_to_include)) {
  3864. include $file_to_include;
  3865. }
  3866. $visible_for_all = 1;
  3867. $visible_for_course_admin = 0;
  3868. $visible_for_platform_admin = 2;
  3869. /* Course tools */
  3870. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_COURSE_DESCRIPTION . "','course_description/','info.gif','".string2binary(api_get_setting('course_create_active_tools', 'course_description')) . "','0','squaregrey.gif','NO','_self','authoring','0')");
  3871. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_CALENDAR_EVENT . "','calendar/agenda.php','agenda.gif','".string2binary(api_get_setting('course_create_active_tools', 'agenda')) . "','0','squaregrey.gif','NO','_self','interaction','0')");
  3872. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_DOCUMENT . "','document/document.php','folder_document.gif','".string2binary(api_get_setting('course_create_active_tools', 'documents')) . "','0','squaregrey.gif','NO','_self','authoring','0')");
  3873. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_LEARNPATH . "','newscorm/lp_controller.php','scorms.gif','".string2binary(api_get_setting('course_create_active_tools', 'learning_path')) . "','0','squaregrey.gif','NO','_self','authoring','0')");
  3874. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_LINK . "','link/link.php','links.gif','".string2binary(api_get_setting('course_create_active_tools', 'links')) . "','0','squaregrey.gif','NO','_self','authoring','0')");
  3875. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_QUIZ . "','exercice/exercice.php','quiz.gif','".string2binary(api_get_setting('course_create_active_tools', 'quiz')) . "','0','squaregrey.gif','NO','_self','authoring','0')");
  3876. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_ANNOUNCEMENT . "','announcements/announcements.php','valves.gif','".string2binary(api_get_setting('course_create_active_tools', 'announcements')) . "','0','squaregrey.gif','NO','_self','authoring','0')");
  3877. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_FORUM . "','forum/index.php','forum.gif','".string2binary(api_get_setting('course_create_active_tools', 'forums')) . "','0','squaregrey.gif','NO','_self','interaction','0')");
  3878. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_DROPBOX . "','dropbox/index.php','dropbox.gif','".string2binary(api_get_setting('course_create_active_tools', 'dropbox')) . "','0','squaregrey.gif','NO','_self','interaction','0')");
  3879. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_USER . "','user/user.php','members.gif','".string2binary(api_get_setting('course_create_active_tools', 'users')) . "','0','squaregrey.gif','NO','_self','interaction','0')");
  3880. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_GROUP . "','group/group.php','group.gif','".string2binary(api_get_setting('course_create_active_tools', 'groups')) . "','0','squaregrey.gif','NO','_self','interaction','0')");
  3881. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_CHAT . "','chat/chat.php','chat.gif','".string2binary(api_get_setting('course_create_active_tools', 'chat')) . "','0','squaregrey.gif','NO','_self','interaction','0')");
  3882. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_STUDENTPUBLICATION . "','work/work.php','works.gif','".string2binary(api_get_setting('course_create_active_tools', 'student_publications')) . "','0','squaregrey.gif','NO','_self','interaction','0')");
  3883. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_SURVEY."','survey/survey_list.php','survey.gif','".string2binary(api_get_setting('course_create_active_tools', 'survey')) . "','0','squaregrey.gif','NO','_self','interaction','0')");
  3884. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_WIKI ."','wiki/index.php','wiki.gif','".string2binary(api_get_setting('course_create_active_tools', 'wiki')) . "','0','squaregrey.gif','NO','_self','interaction','0')");
  3885. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_GRADEBOOK."','gradebook/index.php','gradebook.gif','".string2binary(api_get_setting('course_create_active_tools', 'gradebook')). "','0','squaregrey.gif','NO','_self','authoring','0')");
  3886. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_GLOSSARY."','glossary/index.php','glossary.gif','".string2binary(api_get_setting('course_create_active_tools', 'glossary')). "','0','squaregrey.gif','NO','_self','authoring','0')");
  3887. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_NOTEBOOK."','notebook/index.php','notebook.gif','".string2binary(api_get_setting('course_create_active_tools', 'notebook'))."','0','squaregrey.gif','NO','_self','interaction','0')");
  3888. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_ATTENDANCE."','attendance/index.php','attendance.gif','".string2binary(api_get_setting('course_create_active_tools', 'attendances'))."','0','squaregrey.gif','NO','_self','authoring','0')");
  3889. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_COURSE_PROGRESS."','course_progress/index.php','course_progress.gif','".string2binary(api_get_setting('course_create_active_tools', 'course_progress'))."','0','squaregrey.gif','NO','_self','authoring','0')");
  3890. if (api_get_setting('service_visio', 'active') == 'true') {
  3891. $mycheck = api_get_setting('service_visio', 'visio_host');
  3892. if (!empty($mycheck)) {
  3893. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_VISIO_CONFERENCE . "','conference/index.php?type=conference','visio_meeting.gif','1','0','squaregrey.gif','NO','_self','interaction','0')");
  3894. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_VISIO_CLASSROOM . "','conference/index.php?type=classroom','visio.gif','1','0','squaregrey.gif','NO','_self','authoring','0')");
  3895. }
  3896. }
  3897. if (api_get_setting('search_enabled') == 'true') {
  3898. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '" . TOOL_SEARCH. "','search/','info.gif','".string2binary(api_get_setting('course_create_active_tools', 'enable_search')) . "','0','search.gif','NO','_self','authoring','0')");
  3899. }
  3900. // Smartblogs (Kevin Van Den Haute :: kevin@develop-it.be)
  3901. $sql = "INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL,'" . TOOL_BLOGS . "','blog/blog_admin.php','blog_admin.gif','" . string2binary(api_get_setting('course_create_active_tools', 'blogs')) . "','1','squaregrey.gif','NO','_self','admin','0')";
  3902. Database::query($sql);
  3903. // end of Smartblogs
  3904. /* Course homepage tools for course admin only */
  3905. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '".TOOL_TRACKING . "','tracking/courseLog.php','statistics.gif','$visible_for_course_admin','1','', 'NO','_self','admin','0')");
  3906. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '".TOOL_COURSE_SETTING . "','course_info/infocours.php','reference.gif','$visible_for_course_admin','1','', 'NO','_self','admin','0')");
  3907. Database::query("INSERT INTO $tbl_course_homepage VALUES ($course_id, NULL, '".TOOL_COURSE_MAINTENANCE."','course_info/maintenance.php','backup.gif','$visible_for_course_admin','1','','NO','_self', 'admin','0')");
  3908. /* course_setting table (courseinfo tool) */
  3909. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'email_alert_manager_on_new_doc',0,'work')");
  3910. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'email_alert_on_new_doc_dropbox',0,'dropbox')");
  3911. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'allow_user_edit_agenda',0,'agenda')");
  3912. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'allow_user_edit_announcement',0,'announcement')");
  3913. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'email_alert_manager_on_new_quiz',1,'quiz')");
  3914. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'allow_user_image_forum',1,'forum')");
  3915. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'course_theme','','theme')");
  3916. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'allow_learning_path_theme','1','theme')");
  3917. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'allow_open_chat_window',1,'chat')");
  3918. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'email_alert_to_teacher_on_new_user_in_course',0,'registration')");
  3919. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'allow_user_view_user_list',1,'user')");
  3920. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'display_info_advance_inside_homecourse',1,'thematic_advance')");
  3921. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'email_alert_students_on_new_homework',0,'work')");
  3922. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'enable_lp_auto_launch',0,'learning_path')");
  3923. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'pdf_export_watermark_text','','learning_path')");
  3924. Database::query("INSERT INTO $TABLESETTING (c_id, variable,value,category) VALUES ($course_id, 'allow_public_certificates','','certificates')");
  3925. /* Course homepage tools for platform admin only */
  3926. /* Group tool */
  3927. Database::query("INSERT INTO $TABLEGROUPCATEGORIES (c_id, id , title , description , max_student , self_reg_allowed , self_unreg_allowed , groups_per_user , display_order )
  3928. VALUES ($course_id, '2', '".lang2db(get_lang('DefaultGroupCategory')) . "', '', '8', '0', '0', '0', '0');");
  3929. /* Example Material */
  3930. global $language_interface;
  3931. $language_interface = !empty($language_interface) ? $language_interface : api_get_setting('platformLanguage');
  3932. // Example material should be in the same language as the course is.
  3933. $language_interface_original = $language_interface;
  3934. $language_interface = $language;
  3935. //Share folder
  3936. Database::query("INSERT INTO $TABLETOOLDOCUMENT (c_id, path,title,filetype,size) VALUES ($course_id,'/shared_folder','".get_lang('UserFolders')."','folder','0')");
  3937. $example_doc_id = Database :: insert_id();
  3938. Database::query("INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility) VALUES ($course_id,'document',1,NOW(),NOW(),$example_doc_id,'DocumentAdded',1,0,NULL,0)");
  3939. //Chat folder
  3940. Database::query("INSERT INTO $TABLETOOLDOCUMENT (c_id, path,title,filetype,size) VALUES ($course_id,'/chat_files','".get_lang('ChatFiles')."','folder','0')");
  3941. $example_doc_id = Database :: insert_id();
  3942. Database::query("INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility) VALUES ($course_id,'document',1,NOW(),NOW(),$example_doc_id,'DocumentAdded',1,0,NULL,0)");
  3943. $sys_course_path = api_get_path(SYS_COURSE_PATH);
  3944. $perm = api_get_permissions_for_new_directories();
  3945. $perm_file = api_get_permissions_for_new_files();
  3946. $chat_path = $sys_course_path.$course_repository.'/document/chat_files';
  3947. if (!is_dir($chat_path)) {
  3948. @mkdir($chat_path, api_get_permissions_for_new_directories());
  3949. }
  3950. /* Documents */
  3951. if ($fill_with_exemplary_content) {
  3952. Database::query("INSERT INTO $TABLETOOLDOCUMENT (c_id,path,title,filetype,size) VALUES ($course_id,'/images','".get_lang('Images')."','folder','0')");
  3953. $example_doc_id = Database :: insert_id();
  3954. Database::query("INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility) VALUES ($course_id,'document',1,NOW(),NOW(),$example_doc_id,'DocumentAdded',1,0,NULL,0)");
  3955. Database::query("INSERT INTO $TABLETOOLDOCUMENT (c_id, path,title,filetype,size) VALUES ($course_id,'/images/gallery','".get_lang('DefaultCourseImages')."','folder','0')");
  3956. $example_doc_id = Database :: insert_id();
  3957. Database::query("INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility) VALUES ($course_id,'document',1,NOW(),NOW(),$example_doc_id,'DocumentAdded',1,0,NULL,0)");
  3958. Database::query("INSERT INTO $TABLETOOLDOCUMENT (c_id, path,title,filetype,size) VALUES ($course_id,'/audio','".get_lang('Audio')."','folder','0')");
  3959. $example_doc_id = Database :: insert_id();
  3960. Database::query("INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility) VALUES ($course_id,'document',1,NOW(),NOW(),$example_doc_id,'DocumentAdded',1,0,NULL,0)");
  3961. Database::query("INSERT INTO $TABLETOOLDOCUMENT (c_id, path,title,filetype,size) VALUES ($course_id,'/flash','".get_lang('Flash')."','folder','0')");
  3962. $example_doc_id = Database :: insert_id();
  3963. Database::query("INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility) VALUES ($course_id,'document',1,NOW(),NOW(),$example_doc_id,'DocumentAdded',1,0,NULL,0)");
  3964. Database::query("INSERT INTO $TABLETOOLDOCUMENT (c_id, path,title,filetype,size) VALUES ($course_id,'/video','".get_lang('Video')."','folder','0')");
  3965. $example_doc_id = Database :: insert_id();
  3966. Database::query("INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility) VALUES ($course_id,'document',1,NOW(),NOW(),$example_doc_id,'DocumentAdded',1,0,NULL,0)");
  3967. Database::query("INSERT INTO $TABLETOOLDOCUMENT (c_id, path,title,filetype,size) VALUES ($course_id,'/certificates','".get_lang('Certificates')."','folder','0')");
  3968. $example_doc_id = Database :: insert_id();
  3969. Database::query("INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility) VALUES ($course_id,'document',1,NOW(),NOW(),$example_doc_id,'DocumentAdded',1,0,NULL,0)");
  3970. // FILL THE COURSE DOCUMENT WITH DEFAULT COURSE PICTURES
  3971. $folders_to_copy_from_default_course = array(
  3972. 'images',
  3973. 'audio',
  3974. 'flash',
  3975. 'video',
  3976. 'certificates',
  3977. );
  3978. $default_course_path = api_get_path(SYS_CODE_PATH).'default_course_document/';
  3979. $default_document_array = array();
  3980. foreach ($folders_to_copy_from_default_course as $folder) {
  3981. $default_course_folder_path = $default_course_path.$folder.'/';
  3982. $files = browse_folders($default_course_folder_path, array(), $folder);
  3983. $sorted_array = sort_pictures($files, 'dir');
  3984. $sorted_array = array_merge($sorted_array, sort_pictures($files, 'file'));
  3985. $default_document_array[$folder] = $sorted_array;
  3986. }
  3987. //echo '<pre>'; print_r($default_document_array);exit;
  3988. //Light protection (adding index.html in every document folder)
  3989. $htmlpage = "<!DOCTYPE html>\n<html lang=\"en\">\n <head>\n <meta charset=\"utf-8\">\n <title>Not authorized</title>\n </head>\n <body>\n </body>\n</html>";
  3990. $example_cert_id = 0;
  3991. if (is_array($default_document_array) && count($default_document_array) > 0) {
  3992. foreach ($default_document_array as $media_type => $array_media) {
  3993. $path_documents = "/$media_type/";
  3994. //hack until feature #5242 is implemented
  3995. if ($media_type == 'images') {
  3996. $media_type = 'images/gallery';
  3997. $images_folder = $sys_course_path.$course_repository."/document/images/";
  3998. if (!is_dir($images_folder)) {
  3999. //Creating index.html
  4000. mkdir($images_folder, $perm);
  4001. $fd = fopen($images_folder.'index.html', 'w');
  4002. fwrite($fd, $htmlpage);
  4003. @chmod($images_folder.'index.html', $perm_file);
  4004. }
  4005. }
  4006. $course_documents_folder = $sys_course_path.$course_repository."/document/$media_type/";
  4007. $default_course_path = api_get_path(SYS_CODE_PATH).'default_course_document'.$path_documents;
  4008. //echo 'try '.$course_documents_folder; echo '<br />';
  4009. if (!is_dir($course_documents_folder)) {
  4010. //Creating index.html
  4011. mkdir($course_documents_folder, $perm);
  4012. $fd = fopen($course_documents_folder.'index.html', 'w');
  4013. fwrite($fd, $htmlpage);
  4014. @chmod($course_documents_folder.'index.html', $perm_file);
  4015. }
  4016. if (is_array($array_media) && count($array_media)>0) {
  4017. foreach ($array_media as $key => $value) {
  4018. if (isset($value['dir']) && !empty($value['dir'])) {
  4019. if (!is_dir($course_documents_folder.$value['dir'])) {
  4020. //Creating folder
  4021. mkdir($course_documents_folder.$value['dir'], $perm);
  4022. //Creating index.html (for light protection)
  4023. $index_html = $course_documents_folder.$value['dir'].'/index.html';
  4024. $fd = fopen($index_html, 'w');
  4025. fwrite($fd, $htmlpage);
  4026. @chmod($index_html, $perm_file);
  4027. //Inserting folder in the DB
  4028. $folder_path = substr($value['dir'], 0, strlen($value['dir']) - 1);
  4029. $temp = explode('/', $folder_path);
  4030. $title = $temp[count($temp)-1];
  4031. //hack until feature #5242 is implemented
  4032. if ($title == 'gallery') {
  4033. $title = get_lang('DefaultCourseImages');
  4034. }
  4035. if ($media_type == 'images/gallery') {
  4036. $folder_path = 'gallery/'.$folder_path;
  4037. }
  4038. Database::query("INSERT INTO $TABLETOOLDOCUMENT (c_id, path,title,filetype,size) VALUES ($course_id,'$path_documents".$folder_path."','".$title."','folder','0')");
  4039. $image_id = Database :: insert_id();
  4040. Database::query("INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility) VALUES ($course_id,'document',1,NOW(),NOW(),$image_id,'DocumentAdded',1,0,NULL,0)");
  4041. }
  4042. }
  4043. if (isset($value['file']) && !empty($value['file'])) {
  4044. if (!file_exists($course_documents_folder.$value['file'])) {
  4045. //Copying file
  4046. copy($default_course_path.$value['file'], $course_documents_folder.$value['file']);
  4047. chmod($course_documents_folder.$value['file'], $perm_file);
  4048. //echo $default_course_path.$value['file']; echo ' - '; echo $course_documents_folder.$value['file']; echo '<br />';
  4049. $temp = explode('/', $value['file']);
  4050. $file_size = filesize($course_documents_folder.$value['file']);
  4051. //hack until feature #5242 is implemented
  4052. if ($media_type == 'images/gallery') {
  4053. $value["file"] = 'gallery/'.$value["file"];
  4054. }
  4055. //Inserting file in the DB
  4056. Database::query("INSERT INTO $TABLETOOLDOCUMENT (c_id, path,title,filetype,size) VALUES ($course_id,'$path_documents".$value["file"]."','".$temp[count($temp)-1]."','file','$file_size')");
  4057. $image_id = Database :: insert_id();
  4058. if ($path_documents.$value['file'] == '/certificates/default.html') {
  4059. $example_cert_id = $image_id;
  4060. }
  4061. Database::query("INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility) VALUES ($course_id,'document',1,NOW(),NOW(),$image_id,'DocumentAdded',1,0,NULL,1)");
  4062. }
  4063. }
  4064. }
  4065. }
  4066. }
  4067. }
  4068. /* Agenda tool */
  4069. Database::query("INSERT INTO $TABLETOOLAGENDA VALUES ($course_id, NULL, '".lang2db(get_lang('AgendaCreationTitle')) . "', '".lang2db(get_lang('AgendaCreationContenu')) . "', now(), now(), NULL, 0, 0)");
  4070. // We need to add the item properties too!
  4071. $insert_id = Database :: insert_id();
  4072. $sql = "INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility) VALUES ($course_id, '" . TOOL_CALENDAR_EVENT . "',1,NOW(),NOW(),$insert_id,'AgendaAdded',1,0,NULL,1)";
  4073. Database::query($sql);
  4074. /* Links tool */
  4075. $add_google_link_sql = "INSERT INTO $TABLETOOLLINK (c_id, url, title, description, category_id, display_order, on_homepage, target)
  4076. VALUES ($course_id, 'http://www.google.com','Google','".lang2db(get_lang('Google')) . "','0','0','0','_self')";
  4077. Database::query($add_google_link_sql);
  4078. // We need to add the item properties too!
  4079. $insert_id = Database :: insert_id();
  4080. $sql = "INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility)
  4081. VALUES ($course_id, '" . TOOL_LINK . "',1,NOW(),NOW(),$insert_id,'LinkAdded',1,0,NULL,1)";
  4082. Database::query($sql);
  4083. $add_wikipedia_link_sql = "INSERT INTO $TABLETOOLLINK (c_id, url, title, description, category_id, display_order, on_homepage, target)
  4084. VALUES ($course_id, 'http://www.wikipedia.org','Wikipedia','".lang2db(get_lang('Wikipedia')) . "','0','1','0','_self')";
  4085. Database::query($add_wikipedia_link_sql);
  4086. // We need to add the item properties too!
  4087. $insert_id = Database :: insert_id();
  4088. $sql = "INSERT INTO $TABLEITEMPROPERTY (tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility) VALUES ('" . TOOL_LINK . "',1,NOW(),NOW(),$insert_id,'LinkAdded',1,0,NULL,1)";
  4089. Database::query($sql);
  4090. /* Annoucement tool */
  4091. $sql = "INSERT INTO $TABLETOOLANNOUNCEMENTS (c_id, title,content,end_date,display_order,email_sent)
  4092. VALUES ($course_id, '".lang2db(get_lang('AnnouncementExampleTitle')) . "', '".lang2db(get_lang('AnnouncementEx')) . "', NOW(), '1','0')";
  4093. Database::query($sql);
  4094. // We need to add the item properties too!
  4095. $insert_id = Database :: insert_id();
  4096. $sql = "INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility)
  4097. VALUES ($course_id, '" . TOOL_ANNOUNCEMENT . "',1,NOW(),NOW(),$insert_id,'AnnouncementAdded',1,0,NULL,1)";
  4098. Database::query($sql);
  4099. /* Introduction text */
  4100. $intro_text='<p style="text-align: center;">
  4101. <img src="'.api_get_path(REL_CODE_PATH).'img/mascot.png" alt="Mr. Chamilo" title="Mr. Chamilo" />
  4102. <h2>'.lang2db(get_lang('IntroductionText')).'</h2>
  4103. </p>';
  4104. Database::query("INSERT INTO $TABLEINTROS VALUES ($course_id, '" . TOOL_COURSE_HOMEPAGE . "','".$intro_text."', 0)");
  4105. Database::query("INSERT INTO $TABLEINTROS VALUES ($course_id, '" . TOOL_STUDENTPUBLICATION . "','".lang2db(get_lang('IntroductionTwo')) . "', 0)");
  4106. // Wiki intro
  4107. $intro_wiki='<table width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td width="110" valign="top" align="left"></td><td valign="top" align="left">'.lang2db(get_lang('IntroductionWiki')).'</td></tr></table>';
  4108. Database::query("INSERT INTO $TABLEINTROS VALUES ($course_id, '" . TOOL_WIKI . "','".$intro_wiki. "', 0)");
  4109. /* Exercise tool */
  4110. Database::query("INSERT INTO $TABLEQUIZANSWERSLIST (c_id, id, question_id, answer, correct, comment, ponderation, position) VALUES ($course_id, '1', '1', '".lang2db(get_lang('Ridiculise')) . "', '0', '".lang2db(get_lang('NoPsychology')) . "', '-5', '1')");
  4111. Database::query("INSERT INTO $TABLEQUIZANSWERSLIST (c_id, id, question_id, answer, correct, comment, ponderation, position) VALUES ($course_id, '2', '1', '".lang2db(get_lang('AdmitError')) . "', '0', '".lang2db(get_lang('NoSeduction')) . "', '-5', '2')");
  4112. Database::query("INSERT INTO $TABLEQUIZANSWERSLIST (c_id, id, question_id, answer, correct, comment, ponderation, position) VALUES ($course_id, '3', '1', '".lang2db(get_lang('Force')) . "', '1', '".lang2db(get_lang('Indeed')) . "', '5', '3')");
  4113. Database::query("INSERT INTO $TABLEQUIZANSWERSLIST (c_id, id, question_id, answer, correct, comment, ponderation, position) VALUES ($course_id, '4', '1', '".lang2db(get_lang('Contradiction')) . "', '1', '".lang2db(get_lang('NotFalse')) . "', '5', '4')");
  4114. $html=Database::escape_string('<table width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td width="110" valign="top" align="left"><img src="'.api_get_path(WEB_CODE_PATH).'default_course_document/images/mr_dokeos/thinking.jpg"></td><td valign="top" align="left">'.get_lang('Antique').'</td></tr></table>');
  4115. Database::query('INSERT INTO '.$TABLEQUIZ .
  4116. ' (c_id, title, description, type, random, random_answers, active, results_disabled ) ' .
  4117. ' VALUES ('.$course_id.', "'.lang2db(get_lang('ExerciceEx')) . '",' .
  4118. ' "'.$html.'", "1", "0", "0", "1", "0")');
  4119. $exercise_id = Database :: insert_id();
  4120. Database::query("INSERT INTO $TABLEQUIZQUESTIONLIST (c_id, id, question, description, ponderation, position, type, picture, level)
  4121. VALUES ( '.$course_id.', '1', '".lang2db(get_lang('SocraticIrony')) . "', '".lang2db(get_lang('ManyAnswers')) . "', '10', '1', '2','',1)");
  4122. Database::query("INSERT INTO $TABLEQUIZQUESTION (c_id, question_id, exercice_id, question_order) VALUES ('.$course_id.', 1,1,1)");
  4123. /* Forum tool */
  4124. Database::query("INSERT INTO $TABLEFORUMCATEGORIES VALUES ($course_id, 1,'".lang2db(get_lang('ExampleForumCategory'))."', '', 1, 0, 0)");
  4125. $insert_id = Database :: insert_id();
  4126. Database::query("INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility)
  4127. VALUES ($course_id, 'forum_category',1,NOW(),NOW(),$insert_id,'ForumCategoryAdded',1,0,NULL,1)");
  4128. Database::query("INSERT INTO $TABLEFORUMS (c_id, forum_title, forum_comment, forum_threads,forum_posts,forum_last_post,forum_category, allow_anonymous, allow_edit,allow_attachments, allow_new_threads,default_view,forum_of_group,forum_group_public_private, forum_order,locked,session_id )
  4129. VALUES ($course_id, '".lang2db(get_lang('ExampleForum'))."', '', 0, 0, 0, 1, 0, 1, '0', 1, 'flat','0', 'public', 1, 0,0)");
  4130. $insert_id = Database :: insert_id();
  4131. Database::query("INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility)
  4132. VALUES ($course_id, '".TOOL_FORUM."', 1,NOW(),NOW(),$insert_id,'ForumAdded',1,0,NULL,1)");
  4133. Database::query("INSERT INTO $TABLEFORUMTHREADS (c_id, thread_id, thread_title, forum_id, thread_replies, thread_poster_id, thread_poster_name, thread_views, thread_last_post, thread_date, locked, thread_qualify_max, session_id)
  4134. VALUES ($course_id, 1, '".lang2db(get_lang('ExampleThread'))."', 1, 0, 1, '', 0, 1, NOW(), 0, 10, 0)");
  4135. $insert_id = Database :: insert_id();
  4136. Database::query("INSERT INTO $TABLEITEMPROPERTY (c_id, tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type,lastedit_user_id,to_group_id,to_user_id,visibility)
  4137. VALUES ($course_id, 'forum_thread',1,NOW(),NOW(),$insert_id,'ForumThreadAdded',1,0,NULL,1)");
  4138. Database::query("INSERT INTO $TABLEFORUMPOSTS VALUES ($course_id, 1, '".lang2db(get_lang('ExampleThread'))."', '".lang2db(get_lang('ExampleThreadContent'))."', 1, 1, 1, '', NOW(), 0, 0, 1)");
  4139. /* Gradebook tool */
  4140. $course = api_get_course_info_by_id($course_id);
  4141. $course_code = $course['code'];
  4142. // father gradebook
  4143. Database::query("INSERT INTO $TABLEGRADEBOOK (name, description, user_id, course_code, parent_id, weight, visible, certif_min_score, session_id, document_id) VALUES ('$course_code','',1,'$course_code',0,100,0,NULL,NULL,$example_cert_id)");
  4144. $gbid = Database :: insert_id();
  4145. Database::query("INSERT INTO $TABLEGRADEBOOK (name, description, user_id, course_code, parent_id, weight, visible, certif_min_score, session_id, document_id) VALUES ('$course_code','',1,'$course_code',$gbid,100,1,75,NULL,$example_cert_id)");
  4146. $gbid = Database :: insert_id();
  4147. Database::query("INSERT INTO $TABLEGRADEBOOKLINK (type, ref_id, user_id, course_code, category_id, created_at, weight, visible, locked) VALUES (1,$exercise_id,1,'$course_code',$gbid,'$now',100,1,0)");
  4148. }
  4149. //Installing plugins in course
  4150. $app_plugin = new AppPlugin();
  4151. $app_plugin->install_course_plugins($course_id);
  4152. $language_interface = $language_interface_original;
  4153. return true;
  4154. }
  4155. /**
  4156. * function register_course to create a record in the course table of the main database
  4157. * @param string $course_sys_code
  4158. * @param string $course_screen_code
  4159. * @param string $course_repository
  4160. * @param string $course_db_name
  4161. * @param string $tutor_name
  4162. * @param string $category
  4163. * @param string $title complete name of course
  4164. * @param string $course_language lang for this course
  4165. * @param string $uid uid of owner
  4166. * @param integer Expiration date in unix time representation
  4167. * @param array Optional array of teachers' user ID
  4168. * @return int 0
  4169. * @todo use an array called $params instead of lots of params
  4170. */
  4171. static function register_course($params) {
  4172. global $error_msg, $firstExpirationDelay;
  4173. $title = $params['title'];
  4174. $code = $params['code'];
  4175. $visual_code = $params['visual_code'];
  4176. $directory = isset($params['directory']) ? $params['directory'] : null;
  4177. $tutor_name = isset($params['tutor_name']) ? $params['tutor_name'] : null;
  4178. //$description = $params['description'];
  4179. $category_code = isset($params['category_code']) ? $params['category_code'] : null;
  4180. $course_language = isset($params['course_language']) && !empty($params['course_language']) ? $params['course_language'] : api_get_setting('platformLanguage');
  4181. $user_id = empty($params['user_id']) ? api_get_user_id() : intval($params['user_id']);
  4182. $department_name = isset($params['department_name']) ? $params['department_name'] : null;
  4183. $department_url = isset($params['department_url']) ? $params['department_url'] : null;
  4184. $disk_quota = isset($params['disk_quota']) ? $params['disk_quota'] : null;
  4185. if (!isset($params['visibility'])) {
  4186. $default_course_visibility = api_get_setting('courses_default_creation_visibility');
  4187. if (isset($default_course_visibility)) {
  4188. $visibility = $default_course_visibility;
  4189. } else {
  4190. $visibility = COURSE_VISIBILITY_OPEN_PLATFORM;
  4191. }
  4192. } else {
  4193. $visibility = $params['visibility'];
  4194. }
  4195. $subscribe = isset($params['subscribe']) ? intval($params['subscribe']) : ($visibility == COURSE_VISIBILITY_OPEN_PLATFORM ? 1 : 0);
  4196. $unsubscribe = isset($params['unsubscribe']) ? intval($params['unsubscribe']) : 0;
  4197. $expiration_date = isset($params['expiration_date']) ? $params['expiration_date'] : null;
  4198. $teachers = isset($params['teachers']) ? $params['teachers'] : null;
  4199. $status = isset($params['status']) ? $params['status'] : null;
  4200. $TABLECOURSE = Database :: get_main_table(TABLE_MAIN_COURSE);
  4201. $TABLECOURSUSER = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  4202. $ok_to_register_course = true;
  4203. // Check whether all the needed parameters are present.
  4204. if (empty($code)) {
  4205. $error_msg[] = 'courseSysCode is missing';
  4206. $ok_to_register_course = false;
  4207. }
  4208. if (empty($visual_code)) {
  4209. $error_msg[] = 'courseScreenCode is missing';
  4210. $ok_to_register_course = false;
  4211. }
  4212. if (empty($directory)) {
  4213. $error_msg[] = 'courseRepository is missing';
  4214. $ok_to_register_course = false;
  4215. }
  4216. if (empty($title)) {
  4217. $error_msg[] = 'title is missing';
  4218. $ok_to_register_course = false;
  4219. }
  4220. if (empty($expiration_date)) {
  4221. $expiration_date = api_get_utc_datetime(time() + $firstExpirationDelay);
  4222. } else {
  4223. $expiration_date = api_get_utc_datetime($expiration_date);
  4224. }
  4225. if ($visibility < 0 || $visibility > 3) {
  4226. $error_msg[] = 'visibility is invalid';
  4227. $ok_to_register_course = false;
  4228. }
  4229. if (empty($disk_quota)) {
  4230. $disk_quota = api_get_setting('default_document_quotum');
  4231. }
  4232. $time = api_get_utc_datetime();
  4233. if (stripos($department_url, 'http://') === false && stripos($department_url, 'https://') === false) {
  4234. $department_url = 'http://'.$department_url;
  4235. }
  4236. //just in case
  4237. if ($department_url == 'http://') {
  4238. $department_url = '';
  4239. }
  4240. $course_id = 0;
  4241. if ($ok_to_register_course) {
  4242. // Here we must add 2 fields.
  4243. $sql = "INSERT INTO ".$TABLECOURSE . " SET
  4244. code = '".Database :: escape_string($code) . "',
  4245. directory = '".Database :: escape_string($directory) . "',
  4246. course_language = '".Database :: escape_string($course_language) . "',
  4247. title = '".Database :: escape_string($title) . "',
  4248. description = '".lang2db(get_lang('CourseDescription')) . "',
  4249. category_code = '".Database :: escape_string($category_code) . "',
  4250. visibility = '".$visibility . "',
  4251. show_score = '1',
  4252. disk_quota = '".intval($disk_quota) . "',
  4253. creation_date = '$time',
  4254. expiration_date = '".$expiration_date . "',
  4255. last_edit = '$time',
  4256. last_visit = NULL,
  4257. tutor_name = '".Database :: escape_string($tutor_name) . "',
  4258. department_name = '".Database :: escape_string($department_name) . "',
  4259. department_url = '".Database :: escape_string($department_url) . "',
  4260. subscribe = '".intval($subscribe) . "',
  4261. unsubscribe = '".intval($unsubscribe) . "',
  4262. visual_code = '".Database :: escape_string($visual_code) . "'";
  4263. Database::query($sql);
  4264. //error_log($sql);
  4265. $course_id = Database::get_last_insert_id();
  4266. if ($course_id) {
  4267. $sort = api_max_sort_value('0', api_get_user_id());
  4268. $i_course_sort = CourseManager :: userCourseSort($user_id, $code);
  4269. if (!empty($user_id)) {
  4270. $sql = "INSERT INTO ".$TABLECOURSUSER . " SET
  4271. course_code = '".Database :: escape_string($code). "',
  4272. user_id = '".intval($user_id) . "',
  4273. status = '1',
  4274. role = '".lang2db(get_lang('Professor')) . "',
  4275. tutor_id = '0',
  4276. sort = '". ($i_course_sort) . "',
  4277. user_course_cat = '0'";
  4278. Database::query($sql);
  4279. }
  4280. if (!empty($teachers)) {
  4281. if (!is_array($teachers)) {
  4282. $teachers = array($teachers);
  4283. }
  4284. foreach ($teachers as $key) {
  4285. //just in case
  4286. if ($key == $user_id) {
  4287. continue;
  4288. }
  4289. if (empty($key)) {
  4290. continue;
  4291. }
  4292. $sql = "INSERT INTO ".$TABLECOURSUSER . " SET
  4293. course_code = '".Database::escape_string($code) . "',
  4294. user_id = '".Database::escape_string($key) . "',
  4295. status = '1',
  4296. role = '',
  4297. tutor_id = '0',
  4298. sort = '". ($sort +1) . "',
  4299. user_course_cat = '0'";
  4300. Database::query($sql);
  4301. }
  4302. }
  4303. // Adding the course to an URL
  4304. if (api_is_multiple_url_enabled()) {
  4305. $url_id = 1;
  4306. if (api_get_current_access_url_id() != -1) {
  4307. $url_id = api_get_current_access_url_id();
  4308. }
  4309. UrlManager::add_course_to_url($code, $url_id);
  4310. } else {
  4311. UrlManager::add_course_to_url($code, 1);
  4312. }
  4313. // Add event to the system log.
  4314. $user_id = api_get_user_id();
  4315. event_system(LOG_COURSE_CREATE, LOG_COURSE_CODE, $code, api_get_utc_datetime(), $user_id, $code);
  4316. $send_mail_to_admin = api_get_setting('send_email_to_admin_when_create_course');
  4317. // @todo Improve code to send to all current portal administrators.
  4318. if ($send_mail_to_admin == 'true') {
  4319. $siteName = api_get_setting('siteName');
  4320. $recipient_email = api_get_setting('emailAdministrator');
  4321. $recipient_name = api_get_person_name(api_get_setting('administratorName'), api_get_setting('administratorSurname'));
  4322. $iname = api_get_setting('Institution');
  4323. $subject = get_lang('NewCourseCreatedIn').' '.$siteName.' - '.$iname;
  4324. $message = get_lang('Dear').' '.$recipient_name.",\n\n".get_lang('MessageOfNewCourseToAdmin').' '.$siteName.' - '.$iname."\n";
  4325. $message .= get_lang('CourseName').' '.$title."\n";
  4326. $message .= get_lang('Category').' '.$category_code."\n";
  4327. $message .= get_lang('Tutor').' '.$tutor_name."\n";
  4328. $message .= get_lang('Language').' '.$course_language;
  4329. @api_mail($recipient_name, $recipient_email, $subject, $message, $siteName, $recipient_email);
  4330. }
  4331. }
  4332. }
  4333. return $course_id;
  4334. }
  4335. /**
  4336. * Extract properties of the files from a ZIP package, write them to disk and
  4337. * return them as an array.
  4338. * @todo this function seems not to be used
  4339. * @param string Absolute path to the ZIP file
  4340. * @param bool Whether the ZIP file is compressed (not implemented). Defaults to TRUE.
  4341. * @deprecated seems not to be used
  4342. * @return array List of files properties from the ZIP package
  4343. */
  4344. static function readPropertiesInArchive($archive, $is_compressed = true) {
  4345. include api_get_path(LIBRARY_PATH) . 'pclzip/pclzip.lib.php';
  4346. debug::printVar(dirname($archive), 'Zip : ');
  4347. $uid = api_get_user_id();
  4348. /*
  4349. string tempnam (string dir, string prefix)
  4350. tempnam() creates a unique temporary file in the dir directory. If the
  4351. directory doesn't existm tempnam() will generate a filename in the system's
  4352. temporary directory.
  4353. Before PHP 4.0.6, the behaviour of tempnam() depended of the underlying OS.
  4354. Under Windows, the "TMP" environment variable replaces the dir parameter;
  4355. under Linux, the "TMPDIR" environment variable has priority, while for the
  4356. OSes based on system V R4, the dir parameter will always be used if the
  4357. directory which it represents exists. Consult your documentation for more
  4358. details.
  4359. tempnam() returns the temporary filename, or the string NULL upon failure.
  4360. */
  4361. $zip_file = new PclZip($archive);
  4362. $tmp_dir_name = dirname($archive) . '/tmp'.$uid.uniqid($uid);
  4363. if (mkdir($tmp_dir_name, api_get_permissions_for_new_directories(), true)) {
  4364. $unzipping_state = $zip_file->extract($tmp_dir_name);
  4365. } else {
  4366. die ('mkdir failed');
  4367. }
  4368. $path_to_archive_ini = dirname($tmp_dir_name) . '/archive.ini';
  4369. //echo $path_to_archive_ini;
  4370. $course_properties = parse_ini_file($path_to_archive_ini);
  4371. rmdir($tmp_dir_name);
  4372. return $course_properties;
  4373. }
  4374. } //end class CourseManager