course.lib.php 259 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454245524562457245824592460246124622463246424652466246724682469247024712472247324742475247624772478247924802481248224832484248524862487248824892490249124922493249424952496249724982499250025012502250325042505250625072508250925102511251225132514251525162517251825192520252125222523252425252526252725282529253025312532253325342535253625372538253925402541254225432544254525462547254825492550255125522553255425552556255725582559256025612562256325642565256625672568256925702571257225732574257525762577257825792580258125822583258425852586258725882589259025912592259325942595259625972598259926002601260226032604260526062607260826092610261126122613261426152616261726182619262026212622262326242625262626272628262926302631263226332634263526362637263826392640264126422643264426452646264726482649265026512652265326542655265626572658265926602661266226632664266526662667266826692670267126722673267426752676267726782679268026812682268326842685268626872688268926902691269226932694269526962697269826992700270127022703270427052706270727082709271027112712271327142715271627172718271927202721272227232724272527262727272827292730273127322733273427352736273727382739274027412742274327442745274627472748274927502751275227532754275527562757275827592760276127622763276427652766276727682769277027712772277327742775277627772778277927802781278227832784278527862787278827892790279127922793279427952796279727982799280028012802280328042805280628072808280928102811281228132814281528162817281828192820282128222823282428252826282728282829283028312832283328342835283628372838283928402841284228432844284528462847284828492850285128522853285428552856285728582859286028612862286328642865286628672868286928702871287228732874287528762877287828792880288128822883288428852886288728882889289028912892289328942895289628972898289929002901290229032904290529062907290829092910291129122913291429152916291729182919292029212922292329242925292629272928292929302931293229332934293529362937293829392940294129422943294429452946294729482949295029512952295329542955295629572958295929602961296229632964296529662967296829692970297129722973297429752976297729782979298029812982298329842985298629872988298929902991299229932994299529962997299829993000300130023003300430053006300730083009301030113012301330143015301630173018301930203021302230233024302530263027302830293030303130323033303430353036303730383039304030413042304330443045304630473048304930503051305230533054305530563057305830593060306130623063306430653066306730683069307030713072307330743075307630773078307930803081308230833084308530863087308830893090309130923093309430953096309730983099310031013102310331043105310631073108310931103111311231133114311531163117311831193120312131223123312431253126312731283129313031313132313331343135313631373138313931403141314231433144314531463147314831493150315131523153315431553156315731583159316031613162316331643165316631673168316931703171317231733174317531763177317831793180318131823183318431853186318731883189319031913192319331943195319631973198319932003201320232033204320532063207320832093210321132123213321432153216321732183219322032213222322332243225322632273228322932303231323232333234323532363237323832393240324132423243324432453246324732483249325032513252325332543255325632573258325932603261326232633264326532663267326832693270327132723273327432753276327732783279328032813282328332843285328632873288328932903291329232933294329532963297329832993300330133023303330433053306330733083309331033113312331333143315331633173318331933203321332233233324332533263327332833293330333133323333333433353336333733383339334033413342334333443345334633473348334933503351335233533354335533563357335833593360336133623363336433653366336733683369337033713372337333743375337633773378337933803381338233833384338533863387338833893390339133923393339433953396339733983399340034013402340334043405340634073408340934103411341234133414341534163417341834193420342134223423342434253426342734283429343034313432343334343435343634373438343934403441344234433444344534463447344834493450345134523453345434553456345734583459346034613462346334643465346634673468346934703471347234733474347534763477347834793480348134823483348434853486348734883489349034913492349334943495349634973498349935003501350235033504350535063507350835093510351135123513351435153516351735183519352035213522352335243525352635273528352935303531353235333534353535363537353835393540354135423543354435453546354735483549355035513552355335543555355635573558355935603561356235633564356535663567356835693570357135723573357435753576357735783579358035813582358335843585358635873588358935903591359235933594359535963597359835993600360136023603360436053606360736083609361036113612361336143615361636173618361936203621362236233624362536263627362836293630363136323633363436353636363736383639364036413642364336443645364636473648364936503651365236533654365536563657365836593660366136623663366436653666366736683669367036713672367336743675367636773678367936803681368236833684368536863687368836893690369136923693369436953696369736983699370037013702370337043705370637073708370937103711371237133714371537163717371837193720372137223723372437253726372737283729373037313732373337343735373637373738373937403741374237433744374537463747374837493750375137523753375437553756375737583759376037613762376337643765376637673768376937703771377237733774377537763777377837793780378137823783378437853786378737883789379037913792379337943795379637973798379938003801380238033804380538063807380838093810381138123813381438153816381738183819382038213822382338243825382638273828382938303831383238333834383538363837383838393840384138423843384438453846384738483849385038513852385338543855385638573858385938603861386238633864386538663867386838693870387138723873387438753876387738783879388038813882388338843885388638873888388938903891389238933894389538963897389838993900390139023903390439053906390739083909391039113912391339143915391639173918391939203921392239233924392539263927392839293930393139323933393439353936393739383939394039413942394339443945394639473948394939503951395239533954395539563957395839593960396139623963396439653966396739683969397039713972397339743975397639773978397939803981398239833984398539863987398839893990399139923993399439953996399739983999400040014002400340044005400640074008400940104011401240134014401540164017401840194020402140224023402440254026402740284029403040314032403340344035403640374038403940404041404240434044404540464047404840494050405140524053405440554056405740584059406040614062406340644065406640674068406940704071407240734074407540764077407840794080408140824083408440854086408740884089409040914092409340944095409640974098409941004101410241034104410541064107410841094110411141124113411441154116411741184119412041214122412341244125412641274128412941304131413241334134413541364137413841394140414141424143414441454146414741484149415041514152415341544155415641574158415941604161416241634164416541664167416841694170417141724173417441754176417741784179418041814182418341844185418641874188418941904191419241934194419541964197419841994200420142024203420442054206420742084209421042114212421342144215421642174218421942204221422242234224422542264227422842294230423142324233423442354236423742384239424042414242424342444245424642474248424942504251425242534254425542564257425842594260426142624263426442654266426742684269427042714272427342744275427642774278427942804281428242834284428542864287428842894290429142924293429442954296429742984299430043014302430343044305430643074308430943104311431243134314431543164317431843194320432143224323432443254326432743284329433043314332433343344335433643374338433943404341434243434344434543464347434843494350435143524353435443554356435743584359436043614362436343644365436643674368436943704371437243734374437543764377437843794380438143824383438443854386438743884389439043914392439343944395439643974398439944004401440244034404440544064407440844094410441144124413441444154416441744184419442044214422442344244425442644274428442944304431443244334434443544364437443844394440444144424443444444454446444744484449445044514452445344544455445644574458445944604461446244634464446544664467446844694470447144724473447444754476447744784479448044814482448344844485448644874488448944904491449244934494449544964497449844994500450145024503450445054506450745084509451045114512451345144515451645174518451945204521452245234524452545264527452845294530453145324533453445354536453745384539454045414542454345444545454645474548454945504551455245534554455545564557455845594560456145624563456445654566456745684569457045714572457345744575457645774578457945804581458245834584458545864587458845894590459145924593459445954596459745984599460046014602460346044605460646074608460946104611461246134614461546164617461846194620462146224623462446254626462746284629463046314632463346344635463646374638463946404641464246434644464546464647464846494650465146524653465446554656465746584659466046614662466346644665466646674668466946704671467246734674467546764677467846794680468146824683468446854686468746884689469046914692469346944695469646974698469947004701470247034704470547064707470847094710471147124713471447154716471747184719472047214722472347244725472647274728472947304731473247334734473547364737473847394740474147424743474447454746474747484749475047514752475347544755475647574758475947604761476247634764476547664767476847694770477147724773477447754776477747784779478047814782478347844785478647874788478947904791479247934794479547964797479847994800480148024803480448054806480748084809481048114812481348144815481648174818481948204821482248234824482548264827482848294830483148324833483448354836483748384839484048414842484348444845484648474848484948504851485248534854485548564857485848594860486148624863486448654866486748684869487048714872487348744875487648774878487948804881488248834884488548864887488848894890489148924893489448954896489748984899490049014902490349044905490649074908490949104911491249134914491549164917491849194920492149224923492449254926492749284929493049314932493349344935493649374938493949404941494249434944494549464947494849494950495149524953495449554956495749584959496049614962496349644965496649674968496949704971497249734974497549764977497849794980498149824983498449854986498749884989499049914992499349944995499649974998499950005001500250035004500550065007500850095010501150125013501450155016501750185019502050215022502350245025502650275028502950305031503250335034503550365037503850395040504150425043504450455046504750485049505050515052505350545055505650575058505950605061506250635064506550665067506850695070507150725073507450755076507750785079508050815082508350845085508650875088508950905091509250935094509550965097509850995100510151025103510451055106510751085109511051115112511351145115511651175118511951205121512251235124512551265127512851295130513151325133513451355136513751385139514051415142514351445145514651475148514951505151515251535154515551565157515851595160516151625163516451655166516751685169517051715172517351745175517651775178517951805181518251835184518551865187518851895190519151925193519451955196519751985199520052015202520352045205520652075208520952105211521252135214521552165217521852195220522152225223522452255226522752285229523052315232523352345235523652375238523952405241524252435244524552465247524852495250525152525253525452555256525752585259526052615262526352645265526652675268526952705271527252735274527552765277527852795280528152825283528452855286528752885289529052915292529352945295529652975298529953005301530253035304530553065307530853095310531153125313531453155316531753185319532053215322532353245325532653275328532953305331533253335334533553365337533853395340534153425343534453455346534753485349535053515352535353545355535653575358535953605361536253635364536553665367536853695370537153725373537453755376537753785379538053815382538353845385538653875388538953905391539253935394539553965397539853995400540154025403540454055406540754085409541054115412541354145415541654175418541954205421542254235424542554265427542854295430543154325433543454355436543754385439544054415442544354445445544654475448544954505451545254535454545554565457545854595460546154625463546454655466546754685469547054715472547354745475547654775478547954805481548254835484548554865487548854895490549154925493549454955496549754985499550055015502550355045505550655075508550955105511551255135514551555165517551855195520552155225523552455255526552755285529553055315532553355345535553655375538553955405541554255435544554555465547554855495550555155525553555455555556555755585559556055615562556355645565556655675568556955705571557255735574557555765577557855795580558155825583558455855586558755885589559055915592559355945595559655975598559956005601560256035604560556065607560856095610561156125613561456155616561756185619562056215622562356245625562656275628562956305631563256335634563556365637563856395640564156425643564456455646564756485649565056515652565356545655565656575658565956605661566256635664566556665667566856695670567156725673567456755676567756785679568056815682568356845685568656875688568956905691569256935694569556965697569856995700570157025703570457055706570757085709571057115712571357145715571657175718571957205721572257235724572557265727572857295730573157325733573457355736573757385739574057415742574357445745574657475748574957505751575257535754575557565757575857595760576157625763576457655766576757685769577057715772577357745775577657775778577957805781578257835784578557865787578857895790579157925793579457955796579757985799580058015802580358045805580658075808580958105811581258135814581558165817581858195820582158225823582458255826582758285829583058315832583358345835583658375838583958405841584258435844584558465847584858495850585158525853585458555856585758585859586058615862586358645865586658675868586958705871587258735874587558765877587858795880588158825883588458855886588758885889589058915892589358945895589658975898589959005901590259035904590559065907590859095910591159125913591459155916591759185919592059215922592359245925592659275928592959305931593259335934593559365937593859395940594159425943594459455946594759485949595059515952595359545955595659575958595959605961596259635964596559665967596859695970597159725973597459755976597759785979598059815982598359845985598659875988598959905991599259935994599559965997599859996000600160026003600460056006600760086009601060116012601360146015601660176018601960206021602260236024602560266027602860296030603160326033603460356036603760386039604060416042604360446045604660476048604960506051605260536054605560566057605860596060606160626063606460656066606760686069607060716072607360746075607660776078607960806081608260836084608560866087608860896090609160926093609460956096609760986099610061016102610361046105610661076108610961106111611261136114611561166117611861196120612161226123612461256126612761286129613061316132613361346135613661376138613961406141614261436144614561466147614861496150615161526153615461556156615761586159616061616162616361646165616661676168616961706171617261736174617561766177617861796180618161826183618461856186618761886189619061916192619361946195619661976198619962006201620262036204620562066207620862096210621162126213621462156216621762186219622062216222622362246225622662276228622962306231623262336234623562366237623862396240624162426243624462456246624762486249625062516252625362546255625662576258625962606261626262636264626562666267626862696270627162726273627462756276627762786279628062816282628362846285628662876288628962906291629262936294629562966297629862996300630163026303630463056306630763086309631063116312631363146315631663176318631963206321632263236324632563266327632863296330633163326333633463356336633763386339634063416342634363446345634663476348634963506351635263536354635563566357635863596360636163626363636463656366636763686369637063716372637363746375637663776378637963806381638263836384638563866387638863896390639163926393639463956396639763986399640064016402640364046405640664076408640964106411641264136414641564166417641864196420642164226423642464256426642764286429643064316432643364346435643664376438643964406441644264436444644564466447644864496450645164526453645464556456645764586459646064616462646364646465646664676468646964706471647264736474647564766477647864796480648164826483648464856486648764886489649064916492649364946495649664976498649965006501650265036504650565066507650865096510651165126513651465156516651765186519652065216522652365246525652665276528652965306531653265336534653565366537653865396540654165426543654465456546654765486549655065516552655365546555655665576558655965606561656265636564656565666567656865696570657165726573657465756576657765786579658065816582658365846585658665876588658965906591659265936594659565966597659865996600660166026603660466056606660766086609661066116612661366146615661666176618661966206621662266236624662566266627662866296630663166326633663466356636663766386639664066416642664366446645664666476648664966506651665266536654665566566657665866596660666166626663666466656666666766686669667066716672667366746675667666776678667966806681668266836684668566866687668866896690669166926693669466956696669766986699670067016702670367046705670667076708670967106711671267136714671567166717671867196720672167226723672467256726672767286729673067316732673367346735673667376738673967406741674267436744674567466747674867496750675167526753675467556756675767586759676067616762676367646765676667676768676967706771677267736774677567766777677867796780678167826783678467856786678767886789679067916792679367946795679667976798679968006801680268036804680568066807680868096810681168126813681468156816681768186819682068216822682368246825682668276828682968306831683268336834683568366837683868396840684168426843684468456846684768486849685068516852685368546855685668576858685968606861686268636864686568666867686868696870687168726873687468756876687768786879688068816882688368846885688668876888688968906891689268936894689568966897689868996900690169026903690469056906690769086909691069116912691369146915691669176918691969206921692269236924692569266927692869296930693169326933693469356936693769386939694069416942694369446945694669476948694969506951695269536954695569566957695869596960696169626963696469656966696769686969697069716972697369746975
  1. <?php
  2. /* For licensing terms, see /license.txt*/
  3. use Chamilo\CoreBundle\Entity\ExtraField as EntityExtraField;
  4. use ChamiloSession as Session;
  5. //use Chamilo\CourseBundle\Manager\SettingsManager;
  6. use Chamilo\CourseBundle\ToolChain;
  7. use Chamilo\CoreBundle\Entity\Course;
  8. use Chamilo\CourseBundle\Component\CourseCopy\CourseBuilder;
  9. use Chamilo\CourseBundle\Component\CourseCopy\CourseRestorer;
  10. use Chamilo\CourseBundle\Manager\SettingsManager;
  11. /**
  12. * Class CourseManager
  13. *
  14. * This is the course library for Chamilo.
  15. *
  16. * All main course functions should be placed here.
  17. *
  18. * Many functions of this library deal with providing support for
  19. * virtual/linked/combined courses (this was already used in several universities
  20. * but not available in standard Chamilo).
  21. *
  22. * There are probably some places left with the wrong code.
  23. *
  24. * @package chamilo.library
  25. */
  26. class CourseManager
  27. {
  28. const MAX_COURSE_LENGTH_CODE = 40;
  29. /** This constant is used to show separate user names in the course
  30. * list (userportal), footer, etc */
  31. const USER_SEPARATOR = ' |';
  32. const COURSE_FIELD_TYPE_CHECKBOX = 10;
  33. public $columns = array();
  34. public static $em;
  35. private static $manager;
  36. public static $toolList;
  37. public static $courseSettingsManager;
  38. /**
  39. * @param \Doctrine\ORM\EntityManager
  40. */
  41. public static function setEntityManager($em)
  42. {
  43. self::$em = $em;
  44. }
  45. /**
  46. * @return \Doctrine\ORM\EntityManager
  47. */
  48. public static function getEntityManager()
  49. {
  50. return self::$em;
  51. }
  52. /**
  53. * @return Chamilo\CoreBundle\Entity\CourseManager
  54. */
  55. public static function setCourseManager($manager)
  56. {
  57. self::$manager = $manager;
  58. }
  59. /**
  60. * @return SettingsManager
  61. */
  62. public static function getCourseSettingsManager()
  63. {
  64. return self::$courseSettingsManager;
  65. }
  66. /**
  67. * @param SettingsManager $courseSettingsManager
  68. */
  69. public static function setCourseSettingsManager($courseSettingsManager)
  70. {
  71. self::$courseSettingsManager = $courseSettingsManager;
  72. }
  73. /**
  74. * @return Chamilo\CoreBundle\Entity\Manager\CourseManager
  75. */
  76. public static function getManager()
  77. {
  78. return self::$manager;
  79. }
  80. /**
  81. * Creates a course
  82. * @param array $params columns in the main.course table
  83. *
  84. * @return mixed false if the course was not created, array with the course info
  85. */
  86. public static function create_course($params, $authorId = 0)
  87. {
  88. global $_configuration;
  89. // Check portal limits
  90. $access_url_id = 1;
  91. if (api_get_multiple_access_url()) {
  92. $access_url_id = api_get_current_access_url_id();
  93. }
  94. $authorId = empty($authorId) ? api_get_user_id() : (int) $authorId;
  95. if (isset($_configuration[$access_url_id]) && is_array($_configuration[$access_url_id])) {
  96. $return = self::checkCreateCourseAccessUrlParam(
  97. $_configuration,
  98. $access_url_id,
  99. 'hosting_limit_courses',
  100. 'PortalCoursesLimitReached'
  101. );
  102. if ($return != false) {
  103. return $return;
  104. }
  105. $return = self::checkCreateCourseAccessUrlParam(
  106. $_configuration,
  107. $access_url_id,
  108. 'hosting_limit_active_courses',
  109. 'PortalActiveCoursesLimitReached'
  110. );
  111. if ($return != false) {
  112. return $return;
  113. }
  114. }
  115. if (empty($params['title'])) {
  116. return false;
  117. }
  118. if (empty($params['wanted_code'])) {
  119. $params['wanted_code'] = $params['title'];
  120. // Check whether the requested course code has already been occupied.
  121. $substring = api_substr($params['title'], 0, self::MAX_COURSE_LENGTH_CODE);
  122. if ($substring === false || empty($substring)) {
  123. return false;
  124. } else {
  125. $params['wanted_code'] = self::generate_course_code($substring);
  126. }
  127. }
  128. // Create the course keys
  129. $keys = AddCourse::define_course_keys($params['wanted_code']);
  130. $params['exemplary_content'] = isset($params['exemplary_content']) ? $params['exemplary_content'] : false;
  131. if (count($keys)) {
  132. $params['code'] = $keys['currentCourseCode'];
  133. $params['visual_code'] = $keys['currentCourseId'];
  134. $params['directory'] = $keys['currentCourseRepository'];
  135. $course_info = api_get_course_info($params['code']);
  136. if (empty($course_info)) {
  137. $course_id = AddCourse::register_course($params);
  138. $course_info = api_get_course_info_by_id($course_id);
  139. if (!empty($course_info)) {
  140. self::fillCourse($course_info, $params, $authorId);
  141. return $course_info;
  142. }
  143. }
  144. }
  145. return false;
  146. }
  147. /**
  148. * Returns all the information of a given course code
  149. * @param string $course_code , the course code
  150. * @return array with all the fields of the course table
  151. * @deprecated Use api_get_course_info() instead
  152. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  153. * @assert ('') === false
  154. */
  155. public static function get_course_information($course_code)
  156. {
  157. return Database::fetch_array(
  158. Database::query(
  159. "SELECT *, id as real_id FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  160. WHERE code='" . Database::escape_string($course_code)."'"), 'ASSOC'
  161. );
  162. }
  163. /**
  164. * Returns a list of courses. Should work with quickform syntax
  165. * @param integer $from Offset (from the 7th = '6'). Optional.
  166. * @param integer $howmany Number of results we want. Optional.
  167. * @param int $orderby The column we want to order it by. Optional, defaults to first column.
  168. * @param string $orderdirection The direction of the order (ASC or DESC). Optional, defaults to ASC.
  169. * @param int $visibility The visibility of the course, or all by default.
  170. * @param string $startwith If defined, only return results for which the course *title* begins with this string
  171. * @param string $urlId The Access URL ID, if using multiple URLs
  172. * @param bool $alsoSearchCode An extension option to indicate that we also want to search for course codes (not *only* titles)
  173. * @param array $conditionsLike
  174. * @return array
  175. */
  176. public static function get_courses_list(
  177. $from = 0,
  178. $howmany = 0,
  179. $orderby = 1,
  180. $orderdirection = 'ASC',
  181. $visibility = -1,
  182. $startwith = '',
  183. $urlId = null,
  184. $alsoSearchCode = false,
  185. $conditionsLike = array()
  186. ) {
  187. $sql = "SELECT course.* FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." course ";
  188. if (!empty($urlId)) {
  189. $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  190. $sql .= " INNER JOIN $table url ON (url.c_id = course.id) ";
  191. }
  192. if (!empty($startwith)) {
  193. $sql .= "WHERE (title LIKE '".Database::escape_string($startwith)."%' ";
  194. if ($alsoSearchCode) {
  195. $sql .= "OR code LIKE '".Database::escape_string($startwith)."%' ";
  196. }
  197. $sql .= ') ';
  198. if ($visibility !== -1 && $visibility == strval(intval($visibility))) {
  199. $sql .= " AND visibility = $visibility ";
  200. }
  201. } else {
  202. $sql .= "WHERE 1 ";
  203. if ($visibility !== -1 && $visibility == strval(intval($visibility))) {
  204. $sql .= " AND visibility = $visibility ";
  205. }
  206. }
  207. if (!empty($urlId)) {
  208. $urlId = intval($urlId);
  209. $sql .= " AND access_url_id= $urlId";
  210. }
  211. $allowedFields = array(
  212. 'title',
  213. 'code'
  214. );
  215. if (count($conditionsLike) > 0) {
  216. $sql .= ' AND ';
  217. $temp_conditions = array();
  218. foreach ($conditionsLike as $field => $value) {
  219. if (!in_array($field, $allowedFields)) {
  220. continue;
  221. }
  222. $field = Database::escape_string($field);
  223. $value = Database::escape_string($value);
  224. $simple_like = false;
  225. if ($simple_like) {
  226. $temp_conditions[] = $field." LIKE '$value%'";
  227. } else {
  228. $temp_conditions[] = $field.' LIKE \'%'.$value.'%\'';
  229. }
  230. }
  231. $condition = ' AND ';
  232. if (!empty($temp_conditions)) {
  233. $sql .= implode(' '.$condition.' ', $temp_conditions);
  234. }
  235. }
  236. if (!empty($orderby)) {
  237. $sql .= " ORDER BY ".Database::escape_string($orderby)." ";
  238. } else {
  239. $sql .= " ORDER BY 1 ";
  240. }
  241. if (!in_array($orderdirection, array('ASC', 'DESC'))) {
  242. $sql .= 'ASC';
  243. } else {
  244. $sql .= ($orderdirection == 'ASC' ? 'ASC' : 'DESC');
  245. }
  246. if (!empty($howmany) && is_int($howmany) and $howmany > 0) {
  247. $sql .= ' LIMIT '.Database::escape_string($howmany);
  248. } else {
  249. $sql .= ' LIMIT 1000000'; //virtually no limit
  250. }
  251. if (!empty($from)) {
  252. $from = intval($from);
  253. $sql .= ' OFFSET '.intval($from);
  254. } else {
  255. $sql .= ' OFFSET 0';
  256. }
  257. $data = [];
  258. $res = Database::query($sql);
  259. if (Database::num_rows($res) > 0) {
  260. while ($row = Database::fetch_array($res, 'ASSOC')) {
  261. $data[] = $row;
  262. }
  263. }
  264. return $data;
  265. }
  266. /**
  267. * Returns the status of a user in a course, which is COURSEMANAGER or STUDENT.
  268. * @param int $userId
  269. * @param int $courseId
  270. *
  271. * @return int|bool the status of the user in that course (or false if the user is not in that course)
  272. */
  273. public static function getUserInCourseStatus($userId, $courseId)
  274. {
  275. $courseId = (int) $courseId;
  276. if (empty($courseId)) {
  277. return false;
  278. }
  279. $result = Database::fetch_array(
  280. Database::query(
  281. "SELECT status FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  282. WHERE
  283. c_id = $courseId AND
  284. user_id = ".intval($userId)
  285. )
  286. );
  287. return $result['status'];
  288. }
  289. /**
  290. * @param int $userId
  291. * @param int $courseId
  292. *
  293. * @return mixed
  294. */
  295. public static function getUserCourseInfo($userId, $courseId)
  296. {
  297. $sql = "SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  298. WHERE
  299. c_id = '".intval($courseId)."' AND
  300. user_id = ".intval($userId);
  301. $result = Database::fetch_array(Database::query($sql));
  302. return $result;
  303. }
  304. /**
  305. * @param int $userId
  306. * @param int $courseId
  307. * @param bool $isTutor
  308. *
  309. * @return bool
  310. */
  311. public static function updateUserCourseTutor($userId, $courseId, $isTutor)
  312. {
  313. $table = Database::escape_string(TABLE_MAIN_COURSE_USER);
  314. $courseId = intval($courseId);
  315. $isTutor = intval($isTutor);
  316. $sql = "UPDATE $table SET is_tutor = '".$isTutor."'
  317. WHERE
  318. user_id = '".$userId."' AND
  319. c_id = '".$courseId."'";
  320. $result = Database::query($sql);
  321. if (Database::affected_rows($result) > 0) {
  322. return true;
  323. } else {
  324. return false;
  325. }
  326. }
  327. /**
  328. * @param int $user_id
  329. * @param int $courseId
  330. *
  331. * @return mixed
  332. */
  333. public static function get_tutor_in_course_status($user_id, $courseId)
  334. {
  335. $result = Database::fetch_array(
  336. Database::query("
  337. SELECT is_tutor
  338. FROM " . Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  339. WHERE
  340. c_id = '".Database::escape_string($courseId)."' AND
  341. user_id = ".intval($user_id)
  342. )
  343. );
  344. return $result['is_tutor'];
  345. }
  346. /**
  347. * Unsubscribe one or more users from a course
  348. *
  349. * @param mixed user_id or an array with user ids
  350. * @param string course code
  351. * @param int session id
  352. * @assert ('', '') === false
  353. *
  354. */
  355. public static function unsubscribe_user($user_id, $course_code, $session_id = 0)
  356. {
  357. if (empty($user_id)) {
  358. return;
  359. }
  360. if (!is_array($user_id)) {
  361. $user_id = array($user_id);
  362. }
  363. if (count($user_id) == 0) {
  364. return;
  365. }
  366. if (!empty($session_id)) {
  367. $session_id = intval($session_id);
  368. } else {
  369. $session_id = api_get_session_id();
  370. }
  371. $userList = array();
  372. // Cleaning the $user_id variable
  373. if (is_array($user_id)) {
  374. $new_user_id_list = array();
  375. foreach ($user_id as $my_user_id) {
  376. $new_user_id_list[] = intval($my_user_id);
  377. }
  378. $new_user_id_list = array_filter($new_user_id_list);
  379. $userList = $new_user_id_list;
  380. $user_ids = implode(',', $new_user_id_list);
  381. } else {
  382. $user_ids = intval($user_id);
  383. $userList[] = $user_id;
  384. }
  385. $course_info = api_get_course_info($course_code);
  386. $course_id = $course_info['real_id'];
  387. // Unsubscribe user from all groups in the course.
  388. $sql = "DELETE FROM ".Database::get_course_table(TABLE_GROUP_USER)."
  389. WHERE c_id = $course_id AND user_id IN (".$user_ids.")";
  390. Database::query($sql);
  391. $sql = "DELETE FROM ".Database::get_course_table(TABLE_GROUP_TUTOR)."
  392. WHERE c_id = $course_id AND user_id IN (".$user_ids.")";
  393. Database::query($sql);
  394. // Erase user student publications (works) in the course - by André Boivin
  395. if (!empty($userList)) {
  396. require_once api_get_path(SYS_CODE_PATH).'work/work.lib.php';
  397. foreach ($userList as $userId) {
  398. // Getting all work from user
  399. $workList = getWorkPerUser($userId);
  400. if (!empty($workList)) {
  401. foreach ($workList as $work) {
  402. $work = $work['work'];
  403. // Getting user results
  404. if (!empty($work->user_results)) {
  405. foreach ($work->user_results as $workSent) {
  406. deleteWorkItem($workSent['id'], $course_info);
  407. }
  408. }
  409. }
  410. }
  411. }
  412. }
  413. // Unsubscribe user from all blogs in the course.
  414. $sql = "DELETE FROM ".Database::get_course_table(TABLE_BLOGS_REL_USER)."
  415. WHERE c_id = $course_id AND user_id IN (".$user_ids.")";
  416. Database::query($sql);
  417. $sql = "DELETE FROM ".Database::get_course_table(TABLE_BLOGS_TASKS_REL_USER)."
  418. WHERE c_id = $course_id AND user_id IN (".$user_ids.")";
  419. Database::query($sql);
  420. // Deleting users in forum_notification and mailqueue course tables
  421. $sql = "DELETE FROM ".Database::get_course_table(TABLE_FORUM_NOTIFICATION)."
  422. WHERE c_id = $course_id AND user_id IN (".$user_ids.")";
  423. Database::query($sql);
  424. $sql = "DELETE FROM ".Database::get_course_table(TABLE_FORUM_MAIL_QUEUE)."
  425. WHERE c_id = $course_id AND user_id IN (".$user_ids.")";
  426. Database::query($sql);
  427. // Unsubscribe user from the course.
  428. if (!empty($session_id)) {
  429. // Delete in table session_rel_course_rel_user
  430. $sql = "DELETE FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  431. WHERE
  432. session_id ='" . $session_id."' AND
  433. c_id = '" . $course_id."' AND
  434. user_id IN ($user_ids)";
  435. Database::query($sql);
  436. foreach ($userList as $uid) {
  437. // check if a user is register in the session with other course
  438. $sql = "SELECT user_id FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  439. WHERE session_id='$session_id' AND user_id='$uid'";
  440. $rs = Database::query($sql);
  441. if (Database::num_rows($rs) == 0) {
  442. // Delete in table session_rel_user
  443. $sql = "DELETE FROM ".Database::get_main_table(TABLE_MAIN_SESSION_USER)."
  444. WHERE
  445. session_id ='" . $session_id."' AND
  446. user_id = '$uid' AND
  447. relation_type<>".SESSION_RELATION_TYPE_RRHH."";
  448. Database::query($sql);
  449. }
  450. }
  451. // Update the table session
  452. $sql = "SELECT COUNT(*) FROM ".Database::get_main_table(TABLE_MAIN_SESSION_USER)."
  453. WHERE session_id = '" . $session_id."' AND relation_type <> ".SESSION_RELATION_TYPE_RRHH;
  454. $row = Database::fetch_array(Database::query($sql));
  455. $count = $row[0];
  456. // number of users by session
  457. $sql = "UPDATE ".Database::get_main_table(TABLE_MAIN_SESSION)." SET nbr_users = '$count'
  458. WHERE id = '".$session_id."'";
  459. Database::query($sql);
  460. // Update the table session_rel_course
  461. $sql = "SELECT COUNT(*) FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  462. WHERE session_id = '$session_id' AND c_id = '$course_id' AND status<>2";
  463. $row = Database::fetch_array(@Database::query($sql));
  464. $count = $row[0];
  465. // number of users by session and course
  466. $sql = "UPDATE ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE)."
  467. SET nbr_users = '$count'
  468. WHERE session_id = '$session_id' AND c_id = '$course_id'";
  469. Database::query($sql);
  470. Event::addEvent(
  471. LOG_UNSUBSCRIBE_USER_FROM_COURSE,
  472. LOG_COURSE_CODE,
  473. $course_code,
  474. api_get_utc_datetime(),
  475. $user_id,
  476. $course_id,
  477. $session_id
  478. );
  479. } else {
  480. $sql = "DELETE FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  481. WHERE
  482. user_id IN (".$user_ids.") AND
  483. relation_type <> ".COURSE_RELATION_TYPE_RRHH." AND
  484. c_id = '".$course_id."'";
  485. Database::query($sql);
  486. // add event to system log
  487. $user_id = api_get_user_id();
  488. Event::addEvent(
  489. LOG_UNSUBSCRIBE_USER_FROM_COURSE,
  490. LOG_COURSE_CODE,
  491. $course_code,
  492. api_get_utc_datetime(),
  493. $user_id,
  494. $course_id
  495. );
  496. foreach ($userList as $userId) {
  497. $userInfo = api_get_user_info($userId);
  498. Event::addEvent(
  499. LOG_UNSUBSCRIBE_USER_FROM_COURSE,
  500. LOG_USER_OBJECT,
  501. $userInfo,
  502. api_get_utc_datetime(),
  503. $user_id,
  504. $course_id
  505. );
  506. }
  507. }
  508. }
  509. /**
  510. * Subscribe a user to a course. No checks are performed here to see if
  511. * course subscription is allowed.
  512. * @param int $user_id
  513. * @param string $course_code
  514. * @param int $status (STUDENT, COURSEMANAGER, COURSE_ADMIN, NORMAL_COURSE_MEMBER)
  515. * @param int $session_id
  516. * @param int $userCourseCategoryId
  517. *
  518. * @return bool True on success, false on failure
  519. * @see add_user_to_course
  520. * @assert ('', '') === false
  521. */
  522. public static function subscribe_user(
  523. $user_id,
  524. $course_code,
  525. $status = STUDENT,
  526. $session_id = 0,
  527. $userCourseCategoryId = 0
  528. ) {
  529. if ($user_id != strval(intval($user_id))) {
  530. return false; //detected possible SQL injection
  531. }
  532. $course_code = Database::escape_string($course_code);
  533. $courseInfo = api_get_course_info($course_code);
  534. $courseId = $courseInfo['real_id'];
  535. $courseCode = $courseInfo['code'];
  536. $userCourseCategoryId = intval($userCourseCategoryId);
  537. if (empty($user_id) || empty($course_code)) {
  538. return false;
  539. }
  540. if (!empty($session_id)) {
  541. $session_id = intval($session_id);
  542. } else {
  543. $session_id = api_get_session_id();
  544. }
  545. $status = ($status == STUDENT || $status == COURSEMANAGER) ? $status : STUDENT;
  546. // A preliminary check whether the user has bben already registered on the platform.
  547. $sql = "SELECT status FROM ".Database::get_main_table(TABLE_MAIN_USER)."
  548. WHERE user_id = '$user_id' ";
  549. if (Database::num_rows(Database::query($sql)) == 0) {
  550. return false; // The user has not been registered to the platform.
  551. }
  552. // Check whether the user has not been already subscribed to the course.
  553. $sql = "SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  554. WHERE
  555. user_id = '$user_id' AND
  556. relation_type <> ".COURSE_RELATION_TYPE_RRHH." AND
  557. c_id = $courseId
  558. ";
  559. if (empty($session_id)) {
  560. if (Database::num_rows(Database::query($sql)) > 0) {
  561. // The user has been already subscribed to the course.
  562. return false;
  563. }
  564. }
  565. if (!empty($session_id)) {
  566. SessionManager::subscribe_users_to_session_course(
  567. array($user_id),
  568. $session_id,
  569. $courseCode
  570. );
  571. // Add event to the system log
  572. Event::addEvent(
  573. LOG_SUBSCRIBE_USER_TO_COURSE,
  574. LOG_COURSE_CODE,
  575. $course_code,
  576. api_get_utc_datetime(),
  577. api_get_user_id(),
  578. $courseId,
  579. $session_id
  580. );
  581. $user_info = api_get_user_info($user_id);
  582. Event::addEvent(
  583. LOG_SUBSCRIBE_USER_TO_COURSE,
  584. LOG_USER_OBJECT,
  585. $user_info,
  586. api_get_utc_datetime(),
  587. api_get_user_id(),
  588. $courseId,
  589. $session_id
  590. );
  591. } else {
  592. self::add_user_to_course(
  593. $user_id,
  594. $courseCode,
  595. $status,
  596. $userCourseCategoryId
  597. );
  598. // Add event to the system log
  599. Event::addEvent(
  600. LOG_SUBSCRIBE_USER_TO_COURSE,
  601. LOG_COURSE_CODE,
  602. $course_code,
  603. api_get_utc_datetime(),
  604. api_get_user_id(),
  605. $courseId
  606. );
  607. $user_info = api_get_user_info($user_id);
  608. Event::addEvent(
  609. LOG_SUBSCRIBE_USER_TO_COURSE,
  610. LOG_USER_OBJECT,
  611. $user_info,
  612. api_get_utc_datetime(),
  613. api_get_user_id(),
  614. $courseId
  615. );
  616. }
  617. return true;
  618. }
  619. /**
  620. * Get the course id based on the original id and field name in the
  621. * extra fields. Returns 0 if course was not found
  622. *
  623. * @param string $original_course_id_value
  624. * @param string $original_course_id_name
  625. * @return int Course id
  626. *
  627. * @assert ('', '') === false
  628. */
  629. public static function get_course_code_from_original_id(
  630. $original_course_id_value,
  631. $original_course_id_name
  632. ) {
  633. $t_cfv = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
  634. $table_field = Database::get_main_table(TABLE_EXTRA_FIELD);
  635. $extraFieldType = EntityExtraField::COURSE_FIELD_TYPE;
  636. $original_course_id_value = Database::escape_string($original_course_id_value);
  637. $original_course_id_name = Database::escape_string($original_course_id_name);
  638. $sql = "SELECT item_id
  639. FROM $table_field cf
  640. INNER JOIN $t_cfv cfv
  641. ON cfv.field_id=cf.id
  642. WHERE
  643. variable = '$original_course_id_name' AND
  644. value = '$original_course_id_value' AND
  645. cf.extra_field_type = $extraFieldType
  646. ";
  647. $res = Database::query($sql);
  648. $row = Database::fetch_object($res);
  649. if ($row) {
  650. return $row->item_id;
  651. } else {
  652. return 0;
  653. }
  654. }
  655. /**
  656. * Gets the course code from the course id. Returns null if course id was not found
  657. *
  658. * @param int $id Course id
  659. * @return string Course code
  660. * @assert ('') === false
  661. */
  662. public static function get_course_code_from_course_id($id)
  663. {
  664. $table = Database::get_main_table(TABLE_MAIN_COURSE);
  665. $id = intval($id);
  666. $sql = "SELECT code FROM $table WHERE id = '$id' ";
  667. $res = Database::query($sql);
  668. $row = Database::fetch_object($res);
  669. if ($row) {
  670. return $row->code;
  671. } else {
  672. return null;
  673. }
  674. }
  675. /**
  676. * Subscribe a user $user_id to a course defined by $courseCode.
  677. * @author Hugues Peeters
  678. * @author Roan Embrechts
  679. *
  680. * @param int $user_id the id of the user
  681. * @param string $courseCode the course code
  682. * @param int $status (optional) The user's status in the course
  683. * @param int $userCourseCategoryId
  684. * @param int The user category in which this subscription will be classified
  685. *
  686. * @return false|string true if subscription succeeds, boolean false otherwise.
  687. * @assert ('', '') === false
  688. */
  689. public static function add_user_to_course(
  690. $user_id,
  691. $courseCode,
  692. $status = STUDENT,
  693. $userCourseCategoryId = 0
  694. ) {
  695. $debug = false;
  696. $user_table = Database::get_main_table(TABLE_MAIN_USER);
  697. $course_table = Database::get_main_table(TABLE_MAIN_COURSE);
  698. $course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  699. $status = ($status == STUDENT || $status == COURSEMANAGER) ? $status : STUDENT;
  700. if (empty($user_id) || empty($courseCode) || ($user_id != strval(intval($user_id)))) {
  701. return false;
  702. }
  703. $courseCode = Database::escape_string($courseCode);
  704. $courseInfo = api_get_course_info($courseCode);
  705. $courseId = $courseInfo['real_id'];
  706. // Check in advance whether the user has already been registered on the platform.
  707. $sql = "SELECT status FROM ".$user_table." WHERE user_id = $user_id ";
  708. if (Database::num_rows(Database::query($sql)) == 0) {
  709. if ($debug) {
  710. error_log('The user has not been registered to the platform');
  711. }
  712. return false; // The user has not been registered to the platform.
  713. }
  714. // Check whether the user has already been subscribed to this course.
  715. $sql = "SELECT * FROM $course_user_table
  716. WHERE
  717. user_id = $user_id AND
  718. relation_type <> ".COURSE_RELATION_TYPE_RRHH." AND
  719. c_id = $courseId";
  720. if (Database::num_rows(Database::query($sql)) > 0) {
  721. if ($debug) {
  722. error_log('The user has been already subscribed to the course');
  723. }
  724. return false; // The user has been subscribed to the course.
  725. }
  726. if (!api_is_course_admin()) {
  727. // Check in advance whether subscription is allowed or not for this course.
  728. $sql = "SELECT code, visibility FROM $course_table
  729. WHERE id = $courseId AND subscribe = '".SUBSCRIBE_NOT_ALLOWED."'";
  730. if (Database::num_rows(Database::query($sql)) > 0) {
  731. if ($debug) {
  732. error_log('Subscription is not allowed for this course');
  733. }
  734. return false; // Subscription is not allowed for this course.
  735. }
  736. }
  737. // Ok, subscribe the user.
  738. $max_sort = api_max_sort_value('0', $user_id);
  739. $params = [
  740. 'c_id' => $courseId,
  741. 'user_id' => $user_id,
  742. 'status' => $status,
  743. 'sort' => $max_sort + 1,
  744. 'relation_type' => 0,
  745. 'user_course_cat' => (int) $userCourseCategoryId
  746. ];
  747. $insertId = Database::insert($course_user_table, $params);
  748. return $insertId;
  749. }
  750. /**
  751. * Add the user $userId visibility to the course $courseCode in the catalogue.
  752. * @author David Nos (https://github.com/dnos)
  753. *
  754. * @param int $userId the id of the user
  755. * @param string $courseCode the course code
  756. * @param int $visible (optional) The course visibility in the catalogue to the user (1=visible, 0=invisible)
  757. *
  758. * @return boolean true if added succesfully, false otherwise.
  759. */
  760. public static function addUserVisibilityToCourseInCatalogue(
  761. $userId,
  762. $courseCode,
  763. $visible = 1
  764. ) {
  765. $debug = false;
  766. $userTable = Database::get_main_table(TABLE_MAIN_USER);
  767. $courseUserTable = Database::get_main_table(TABLE_MAIN_COURSE_CATALOGUE_USER);
  768. $visible = (int) $visible;
  769. if (empty($userId) || empty($courseCode) || ($userId != strval(intval($userId)))) {
  770. return false;
  771. }
  772. $courseCode = Database::escape_string($courseCode);
  773. $courseInfo = api_get_course_info($courseCode);
  774. $courseId = $courseInfo['real_id'];
  775. // Check in advance whether the user has already been registered on the platform.
  776. $sql = "SELECT status FROM ".$userTable." WHERE user_id = $userId ";
  777. if (Database::num_rows(Database::query($sql)) == 0) {
  778. if ($debug) {
  779. error_log('The user has not been registered to the platform');
  780. }
  781. return false; // The user has not been registered to the platform.
  782. }
  783. // Check whether the user has already been registered to the course visibility in the catalogue.
  784. $sql = "SELECT * FROM $courseUserTable
  785. WHERE
  786. user_id = $userId AND
  787. visible = $visible AND
  788. c_id = $courseId";
  789. if (Database::num_rows(Database::query($sql)) > 0) {
  790. if ($debug) {
  791. error_log('The user has been already registered to the course visibility in the catalogue');
  792. }
  793. return true; // The visibility of the user to the course in the catalogue does already exist.
  794. }
  795. // Register the user visibility to course in catalogue.
  796. $params = [
  797. 'user_id' => $userId,
  798. 'c_id' => $courseId,
  799. 'visible' => $visible
  800. ];
  801. $insertId = Database::insert($courseUserTable, $params);
  802. return $insertId;
  803. }
  804. /**
  805. * Remove the user $userId visibility to the course $courseCode in the catalogue.
  806. * @author David Nos (https://github.com/dnos)
  807. *
  808. * @param int $userId the id of the user
  809. * @param string $courseCode the course code
  810. * @param int $visible (optional) The course visibility in the catalogue to the user (1=visible, 0=invisible)
  811. *
  812. * @return boolean true if removed succesfully or register not found, false otherwise.
  813. */
  814. public static function removeUserVisibilityToCourseInCatalogue(
  815. $userId,
  816. $courseCode,
  817. $visible = 1
  818. ) {
  819. $courseUserTable = Database::get_main_table(TABLE_MAIN_COURSE_CATALOGUE_USER);
  820. if (empty($userId) || empty($courseCode) || ($userId != strval(intval($userId)))) {
  821. return false;
  822. }
  823. $courseCode = Database::escape_string($courseCode);
  824. $courseInfo = api_get_course_info($courseCode);
  825. $courseId = $courseInfo['real_id'];
  826. // Check whether the user has already been registered to the course visibility in the catalogue.
  827. $sql = "SELECT * FROM $courseUserTable
  828. WHERE
  829. user_id = $userId AND
  830. visible = ".$visible." AND
  831. c_id = $courseId";
  832. if (Database::num_rows(Database::query($sql)) > 0) {
  833. $cond = array(
  834. 'user_id = ? AND c_id = ? AND visible = ? ' => array(
  835. $userId,
  836. $courseId,
  837. $visible
  838. )
  839. );
  840. return Database::delete($courseUserTable, $cond);
  841. } else {
  842. return true; // Register does not exist
  843. }
  844. }
  845. /**
  846. * @param string $code
  847. * @return boolean if there already are one or more courses
  848. * with the same code OR visual_code (visualcode), false otherwise
  849. */
  850. public static function course_code_exists($code)
  851. {
  852. $code = Database::escape_string($code);
  853. $sql = "SELECT COUNT(*) as number
  854. FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  855. WHERE code = '$code' OR visual_code = '$code'";
  856. $result = Database::fetch_array(Database::query($sql));
  857. return $result['number'] > 0;
  858. }
  859. /**
  860. * Get course list as coach
  861. *
  862. * @param int $user_id
  863. * @param bool $include_courses_in_sessions
  864. * @return array Course list
  865. *
  866. **/
  867. public static function get_course_list_as_coach(
  868. $user_id,
  869. $include_courses_in_sessions = false
  870. ) {
  871. // 1. Getting courses as teacher (No session)
  872. $courses_temp = self::get_course_list_of_user_as_course_admin($user_id);
  873. $courseList = array();
  874. if (!empty($courses_temp)) {
  875. foreach ($courses_temp as $course_item) {
  876. $courseList[0][$course_item['code']] = $course_item['code'];
  877. }
  878. }
  879. //2. Include courses in sessions
  880. if ($include_courses_in_sessions) {
  881. $sessions = Tracking::get_sessions_coached_by_user($user_id);
  882. if (!empty($sessions)) {
  883. foreach ($sessions as $session_item) {
  884. $courses = Tracking::get_courses_followed_by_coach(
  885. $user_id,
  886. $session_item['id']
  887. );
  888. if (is_array($courses)) {
  889. foreach ($courses as $course_item) {
  890. $courseList[$session_item['id']][$course_item] = $course_item;
  891. }
  892. }
  893. }
  894. }
  895. }
  896. return $courseList;
  897. }
  898. /**
  899. * @param int $user_id
  900. * @param bool $include_sessions
  901. * @return array
  902. */
  903. public static function get_user_list_from_courses_as_coach($user_id, $include_sessions = true)
  904. {
  905. $students_in_courses = array();
  906. $sessions = self::get_course_list_as_coach($user_id, true);
  907. if (!empty($sessions)) {
  908. foreach ($sessions as $session_id => $courses) {
  909. if (!$include_sessions) {
  910. if (!empty($session_id)) {
  911. continue;
  912. }
  913. }
  914. if (empty($session_id)) {
  915. foreach ($courses as $course_code) {
  916. $students_in_course = self::get_user_list_from_course_code($course_code);
  917. foreach ($students_in_course as $user_item) {
  918. //Only students
  919. if ($user_item['status_rel'] == STUDENT) {
  920. $students_in_courses[$user_item['user_id']] = $user_item['user_id'];
  921. }
  922. }
  923. }
  924. } else {
  925. $students_in_course = SessionManager::get_users_by_session($session_id, '0');
  926. if (is_array($students_in_course)) {
  927. foreach ($students_in_course as $user_item) {
  928. $students_in_courses[$user_item['user_id']] = $user_item['user_id'];
  929. }
  930. }
  931. }
  932. }
  933. }
  934. $students = Tracking:: get_student_followed_by_coach($user_id);
  935. if (!empty($students_in_courses)) {
  936. if (!empty($students)) {
  937. $students = array_merge($students, $students_in_courses);
  938. } else {
  939. $students = $students_in_courses;
  940. }
  941. }
  942. if (!empty($students)) {
  943. $students = array_unique($students);
  944. }
  945. return $students;
  946. }
  947. /**
  948. * @param int $user_id
  949. * @param string $startsWith Optional
  950. * @return array An array with the course info of all the courses (real and virtual)
  951. * of which the current user is course admin.
  952. */
  953. public static function get_course_list_of_user_as_course_admin($user_id, $startsWith = '')
  954. {
  955. if ($user_id != strval(intval($user_id))) {
  956. return array();
  957. }
  958. // Definitions database tables and variables
  959. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  960. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  961. $user_id = intval($user_id);
  962. $data = array();
  963. $sql = "SELECT
  964. course.code,
  965. course.title,
  966. course.id,
  967. course.id as real_id,
  968. course.category_code
  969. FROM $tbl_course_user as course_rel_user
  970. INNER JOIN $tbl_course as course
  971. ON course.id = course_rel_user.c_id
  972. WHERE
  973. course_rel_user.user_id='$user_id' AND
  974. course_rel_user.status='1'
  975. ";
  976. if (api_get_multiple_access_url()) {
  977. $tbl_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  978. $access_url_id = api_get_current_access_url_id();
  979. if ($access_url_id != -1) {
  980. $sql = "
  981. SELECT
  982. course.code,
  983. course.title,
  984. course.id,
  985. course.id as real_id
  986. FROM $tbl_course_user as course_rel_user
  987. INNER JOIN $tbl_course as course
  988. ON course.id = course_rel_user.c_id
  989. INNER JOIN $tbl_course_rel_access_url course_rel_url
  990. ON (course_rel_url.c_id = course.id)
  991. WHERE
  992. access_url_id = $access_url_id AND
  993. course_rel_user.user_id = '$user_id' AND
  994. course_rel_user.status = '1'
  995. ";
  996. }
  997. }
  998. if (!empty($startsWith)) {
  999. $startsWith = Database::escape_string($startsWith);
  1000. $sql .= " AND (course.title LIKE '$startsWith%' OR course.code LIKE '$startsWith%')";
  1001. }
  1002. $sql .= ' ORDER BY course.title';
  1003. $result_nb_cours = Database::query($sql);
  1004. if (Database::num_rows($result_nb_cours) > 0) {
  1005. while ($row = Database::fetch_array($result_nb_cours, 'ASSOC')) {
  1006. $data[$row['id']] = $row;
  1007. }
  1008. }
  1009. return $data;
  1010. }
  1011. /**
  1012. * @param int $userId
  1013. * @param array $courseInfo
  1014. * @return boolean|null
  1015. */
  1016. public static function isUserSubscribedInCourseAsDrh($userId, $courseInfo)
  1017. {
  1018. $userId = intval($userId);
  1019. if (!api_is_drh()) {
  1020. return false;
  1021. }
  1022. if (empty($courseInfo) || empty($userId)) {
  1023. return false;
  1024. }
  1025. $courseId = intval($courseInfo['real_id']);
  1026. $table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1027. $sql = "SELECT * FROM $table
  1028. WHERE
  1029. user_id = $userId AND
  1030. relation_type = ".COURSE_RELATION_TYPE_RRHH." AND
  1031. c_id = $courseId";
  1032. $result = Database::fetch_array(Database::query($sql));
  1033. if (!empty($result)) {
  1034. // The user has been registered in this course.
  1035. return true;
  1036. }
  1037. }
  1038. /**
  1039. * Check if user is subscribed inside a course
  1040. * @param int $user_id
  1041. * @param string $course_code , if this parameter is null, it'll check for all courses
  1042. * @param bool $in_a_session True for checking inside sessions too, by default is not checked
  1043. * @return bool $session_id true if the user is registered in the course, false otherwise
  1044. */
  1045. public static function is_user_subscribed_in_course(
  1046. $user_id,
  1047. $course_code = null,
  1048. $in_a_session = false,
  1049. $session_id = 0
  1050. ) {
  1051. $user_id = intval($user_id);
  1052. if (empty($session_id)) {
  1053. $session_id = api_get_session_id();
  1054. } else {
  1055. $session_id = intval($session_id);
  1056. }
  1057. $condition_course = '';
  1058. if (isset($course_code)) {
  1059. $courseInfo = api_get_course_info($course_code);
  1060. if (empty($courseInfo)) {
  1061. return false;
  1062. }
  1063. $courseId = $courseInfo['real_id'];
  1064. $condition_course = ' AND c_id = '.$courseId;
  1065. }
  1066. $sql = "SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  1067. WHERE
  1068. user_id = $user_id AND
  1069. relation_type<>".COURSE_RELATION_TYPE_RRHH."
  1070. $condition_course ";
  1071. $result = Database::fetch_array(Database::query($sql));
  1072. if (!empty($result)) {
  1073. // The user has been registered in this course.
  1074. return true;
  1075. }
  1076. if (!$in_a_session) {
  1077. // The user has not been registered in this course.
  1078. return false;
  1079. }
  1080. $tableSessionCourseUser = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1081. $sql = 'SELECT 1 FROM '.$tableSessionCourseUser.
  1082. ' WHERE user_id = '.$user_id.' '.$condition_course;
  1083. if (Database::num_rows(Database::query($sql)) > 0) {
  1084. return true;
  1085. }
  1086. $sql = 'SELECT 1 FROM '.$tableSessionCourseUser.
  1087. ' WHERE user_id = '.$user_id.' AND status=2 '.$condition_course;
  1088. if (Database::num_rows(Database::query($sql)) > 0) {
  1089. return true;
  1090. }
  1091. $sql = 'SELECT 1 FROM '.Database::get_main_table(TABLE_MAIN_SESSION).
  1092. ' WHERE id = '.$session_id.' AND id_coach='.$user_id;
  1093. if (Database::num_rows(Database::query($sql)) > 0) {
  1094. return true;
  1095. }
  1096. return false;
  1097. }
  1098. /**
  1099. * Is the user a teacher in the given course?
  1100. *
  1101. * @param integer $user_id , the id (int) of the user
  1102. * @param string $course_code , the course code
  1103. *
  1104. * @return boolean if the user is a teacher in the course, false otherwise
  1105. */
  1106. public static function is_course_teacher($user_id, $course_code)
  1107. {
  1108. if ($user_id != strval(intval($user_id))) {
  1109. return false;
  1110. }
  1111. $courseInfo = api_get_course_info($course_code);
  1112. if (empty($courseInfo)) {
  1113. return false;
  1114. }
  1115. $courseId = $courseInfo['real_id'];
  1116. $sql = "SELECT status FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  1117. WHERE c_id = $courseId AND user_id = $user_id ";
  1118. $result = Database::query($sql);
  1119. if (Database::num_rows($result) > 0) {
  1120. return Database::result($result, 0, 'status') == 1;
  1121. }
  1122. return false;
  1123. }
  1124. /**
  1125. * Is the user subscribed in the real course or linked courses?
  1126. *
  1127. * @param int the id of the user
  1128. * @param int $courseId
  1129. * @deprecated linked_courses definition doesn't exists
  1130. * @return boolean if the user is registered in the real course or linked courses, false otherwise
  1131. */
  1132. public static function is_user_subscribed_in_real_or_linked_course($user_id, $courseId, $session_id = 0)
  1133. {
  1134. if ($user_id != strval(intval($user_id))) {
  1135. return false;
  1136. }
  1137. $courseId = intval($courseId);
  1138. $session_id = intval($session_id);
  1139. if (empty($session_id)) {
  1140. $result = Database::fetch_array(
  1141. Database::query(
  1142. "SELECT *
  1143. FROM " . Database::get_main_table(TABLE_MAIN_COURSE)." course
  1144. LEFT JOIN " . Database::get_main_table(TABLE_MAIN_COURSE_USER)." course_user
  1145. ON course.id = course_user.c_id
  1146. WHERE
  1147. course_user.user_id = '$user_id' AND
  1148. course_user.relation_type<>".COURSE_RELATION_TYPE_RRHH." AND
  1149. ( course.id = '$courseId')"
  1150. )
  1151. );
  1152. return !empty($result);
  1153. }
  1154. // From here we trust session id.
  1155. // Is he/she subscribed to the session's course?
  1156. // A user?
  1157. if (Database::num_rows(Database::query("SELECT user_id
  1158. FROM " . Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  1159. WHERE session_id='".$session_id."'
  1160. AND user_id ='$user_id'"))
  1161. ) {
  1162. return true;
  1163. }
  1164. // A course coach?
  1165. if (Database::num_rows(Database::query("SELECT user_id
  1166. FROM " . Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  1167. WHERE session_id='" . $session_id."'
  1168. AND user_id = '$user_id' AND status = 2
  1169. AND c_id ='$courseId'"))
  1170. ) {
  1171. return true;
  1172. }
  1173. // A session coach?
  1174. if (Database::num_rows(Database::query("SELECT id_coach
  1175. FROM " . Database::get_main_table(TABLE_MAIN_SESSION)." AS session
  1176. WHERE session.id='" . $session_id."'
  1177. AND id_coach='$user_id'"))
  1178. ) {
  1179. return true;
  1180. }
  1181. return false;
  1182. }
  1183. /**
  1184. * Return user info array of all users registered in a course
  1185. * This only returns the users that are registered in this actual course, not linked courses.
  1186. * @param string $course_code
  1187. * @param int $session_id
  1188. * @param string $limit
  1189. * @param string $order_by the field to order the users by.
  1190. * Valid values are 'lastname', 'firstname', 'username', 'email', 'official_code' OR a part of a SQL statement
  1191. * that starts with ORDER BY ...
  1192. * @param integer|null $filter_by_status if using the session_id: 0 or 2 (student, coach),
  1193. * if using session_id = 0 STUDENT or COURSEMANAGER
  1194. * @param boolean|null $return_count
  1195. * @param bool $add_reports
  1196. * @param bool $resumed_report
  1197. * @param array $extra_field
  1198. * @param array $courseCodeList
  1199. * @param array $userIdList
  1200. * @param string $filterByActive
  1201. * @param array $sessionIdList
  1202. * @return array|int
  1203. */
  1204. public static function get_user_list_from_course_code(
  1205. $course_code = null,
  1206. $session_id = 0,
  1207. $limit = null,
  1208. $order_by = null,
  1209. $filter_by_status = null,
  1210. $return_count = null,
  1211. $add_reports = false,
  1212. $resumed_report = false,
  1213. $extra_field = array(),
  1214. $courseCodeList = array(),
  1215. $userIdList = array(),
  1216. $filterByActive = null,
  1217. $sessionIdList = array()
  1218. ) {
  1219. $course_table = Database::get_main_table(TABLE_MAIN_COURSE);
  1220. $sessionTable = Database::get_main_table(TABLE_MAIN_SESSION);
  1221. $session_id = intval($session_id);
  1222. $course_code = Database::escape_string($course_code);
  1223. $courseInfo = api_get_course_info($course_code);
  1224. $courseId = 0;
  1225. if (!empty($courseInfo)) {
  1226. $courseId = $courseInfo['real_id'];
  1227. }
  1228. $where = array();
  1229. if (empty($order_by)) {
  1230. $order_by = 'user.lastname, user.firstname';
  1231. if (api_is_western_name_order()) {
  1232. $order_by = 'user.firstname, user.lastname';
  1233. }
  1234. }
  1235. // if the $order_by does not contain 'ORDER BY'
  1236. // we have to check if it is a valid field that can be sorted on
  1237. if (!strstr($order_by, 'ORDER BY')) {
  1238. if (!empty($order_by)) {
  1239. $order_by = 'ORDER BY '.$order_by;
  1240. } else {
  1241. $order_by = '';
  1242. }
  1243. }
  1244. $filter_by_status_condition = null;
  1245. if (!empty($session_id) || !empty($sessionIdList)) {
  1246. $sql = 'SELECT DISTINCT
  1247. user.user_id,
  1248. user.email,
  1249. session_course_user.status as status_session,
  1250. session_id,
  1251. user.*,
  1252. course.*,
  1253. session.name as session_name
  1254. ';
  1255. if ($return_count) {
  1256. $sql = " SELECT COUNT(user.user_id) as count";
  1257. }
  1258. $sessionCondition = " session_course_user.session_id = $session_id";
  1259. if (!empty($sessionIdList)) {
  1260. $sessionIdListTostring = implode("','", array_map('intval', $sessionIdList));
  1261. $sessionCondition = " session_course_user.session_id IN ('$sessionIdListTostring') ";
  1262. }
  1263. $courseCondition = " course.id = $courseId";
  1264. if (!empty($courseCodeList)) {
  1265. $courseCodeListForSession = array_map(array('Database', 'escape_string'), $courseCodeList);
  1266. $courseCodeListForSession = implode('","', $courseCodeListForSession);
  1267. $courseCondition = ' course.code IN ("'.$courseCodeListForSession.'") ';
  1268. }
  1269. $sql .= ' FROM '.Database::get_main_table(TABLE_MAIN_USER).' as user ';
  1270. $sql .= " LEFT JOIN ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)." as session_course_user
  1271. ON
  1272. user.id = session_course_user.user_id AND
  1273. $sessionCondition
  1274. INNER JOIN $course_table course
  1275. ON session_course_user.c_id = course.id AND
  1276. $courseCondition
  1277. INNER JOIN $sessionTable session
  1278. ON session_course_user.session_id = session.id
  1279. ";
  1280. $where[] = ' session_course_user.c_id IS NOT NULL ';
  1281. // 2 = coach
  1282. // 0 = student
  1283. if (isset($filter_by_status)) {
  1284. $filter_by_status = intval($filter_by_status);
  1285. $filter_by_status_condition = " session_course_user.status = $filter_by_status AND ";
  1286. }
  1287. } else {
  1288. if ($return_count) {
  1289. $sql = " SELECT COUNT(*) as count";
  1290. } else {
  1291. if (empty($course_code)) {
  1292. $sql = 'SELECT DISTINCT
  1293. course.title,
  1294. course.code,
  1295. course_rel_user.status as status_rel,
  1296. user.id as user_id,
  1297. user.email,
  1298. course_rel_user.is_tutor,
  1299. user.* ';
  1300. } else {
  1301. $sql = 'SELECT DISTINCT
  1302. course_rel_user.status as status_rel,
  1303. user.id as user_id,
  1304. user.email,
  1305. course_rel_user.is_tutor,
  1306. user.* ';
  1307. }
  1308. }
  1309. $sql .= ' FROM '.Database::get_main_table(TABLE_MAIN_USER).' as user '
  1310. . ' LEFT JOIN '.Database::get_main_table(TABLE_MAIN_COURSE_USER).' as course_rel_user
  1311. ON
  1312. user.id = course_rel_user.user_id AND
  1313. course_rel_user.relation_type <> ' . COURSE_RELATION_TYPE_RRHH.' '
  1314. . " INNER JOIN $course_table course ON course_rel_user.c_id = course.id ";
  1315. if (!empty($course_code)) {
  1316. $sql .= ' AND course_rel_user.c_id = "'.$courseId.'"';
  1317. }
  1318. $where[] = ' course_rel_user.c_id IS NOT NULL ';
  1319. if (isset($filter_by_status) && is_numeric($filter_by_status)) {
  1320. $filter_by_status = intval($filter_by_status);
  1321. $filter_by_status_condition = " course_rel_user.status = $filter_by_status AND ";
  1322. }
  1323. }
  1324. $multiple_access_url = api_get_multiple_access_url();
  1325. if ($multiple_access_url) {
  1326. $sql .= ' LEFT JOIN '.Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER).' au
  1327. ON (au.user_id = user.id) ';
  1328. }
  1329. $extraFieldWasAdded = false;
  1330. if ($return_count && $resumed_report) {
  1331. foreach ($extra_field as $extraField) {
  1332. $extraFieldInfo = UserManager::get_extra_field_information_by_name($extraField);
  1333. if (!empty($extraFieldInfo)) {
  1334. $fieldValuesTable = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
  1335. $sql .= ' LEFT JOIN '.$fieldValuesTable.' as ufv
  1336. ON (
  1337. user.id = ufv.item_id AND
  1338. (field_id = '.$extraFieldInfo['id'].' OR field_id IS NULL)
  1339. )';
  1340. $extraFieldWasAdded = true;
  1341. }
  1342. }
  1343. }
  1344. $sql .= ' WHERE '.$filter_by_status_condition.' '.implode(' OR ', $where);
  1345. if ($multiple_access_url) {
  1346. $current_access_url_id = api_get_current_access_url_id();
  1347. $sql .= " AND (access_url_id = $current_access_url_id ) ";
  1348. }
  1349. if ($return_count && $resumed_report && $extraFieldWasAdded) {
  1350. $sql .= ' AND field_id IS NOT NULL GROUP BY value ';
  1351. }
  1352. if (!empty($courseCodeList)) {
  1353. $courseCodeList = array_map(array('Database', 'escape_string'), $courseCodeList);
  1354. $courseCodeList = implode('","', $courseCodeList);
  1355. if (empty($sessionIdList)) {
  1356. $sql .= ' AND course.code IN ("'.$courseCodeList.'")';
  1357. }
  1358. }
  1359. if (!empty($userIdList)) {
  1360. $userIdList = array_map('intval', $userIdList);
  1361. $userIdList = implode('","', $userIdList);
  1362. $sql .= ' AND user.id IN ("'.$userIdList.'")';
  1363. }
  1364. if (isset($filterByActive)) {
  1365. $filterByActive = intval($filterByActive);
  1366. $sql .= ' AND user.active = '.$filterByActive;
  1367. }
  1368. $sql .= ' '.$order_by.' '.$limit;
  1369. $rs = Database::query($sql);
  1370. $users = array();
  1371. $extra_fields = UserManager::get_extra_fields(
  1372. 0,
  1373. 100,
  1374. null,
  1375. null,
  1376. true,
  1377. true
  1378. );
  1379. $counter = 1;
  1380. $count_rows = Database::num_rows($rs);
  1381. if ($return_count && $resumed_report) {
  1382. return $count_rows;
  1383. }
  1384. $table_user_field_value = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
  1385. $tableExtraField = Database::get_main_table(TABLE_EXTRA_FIELD);
  1386. if ($count_rows) {
  1387. while ($user = Database::fetch_array($rs)) {
  1388. if ($return_count) {
  1389. return $user['count'];
  1390. }
  1391. $report_info = array();
  1392. $user_info = $user;
  1393. $user_info['status'] = $user['status'];
  1394. if (isset($user['is_tutor'])) {
  1395. $user_info['is_tutor'] = $user['is_tutor'];
  1396. }
  1397. if (!empty($session_id)) {
  1398. $user_info['status_session'] = $user['status_session'];
  1399. }
  1400. $sessionId = isset($user['session_id']) ? $user['session_id'] : 0;
  1401. $course_code = isset($user['code']) ? $user['code'] : null;
  1402. if ($add_reports) {
  1403. if ($resumed_report) {
  1404. $extra = array();
  1405. if (!empty($extra_fields)) {
  1406. foreach ($extra_fields as $extra) {
  1407. if (in_array($extra['1'], $extra_field)) {
  1408. $user_data = UserManager::get_extra_user_data_by_field(
  1409. $user['user_id'],
  1410. $extra['1']
  1411. );
  1412. break;
  1413. }
  1414. }
  1415. }
  1416. $row_key = '-1';
  1417. $name = '-';
  1418. if (!empty($extra)) {
  1419. if (!empty($user_data[$extra['1']])) {
  1420. $row_key = $user_data[$extra['1']];
  1421. $name = $user_data[$extra['1']];
  1422. $users[$row_key]['extra_'.$extra['1']] = $name;
  1423. }
  1424. }
  1425. $users[$row_key]['training_hours'] += Tracking::get_time_spent_on_the_course(
  1426. $user['user_id'],
  1427. $courseId,
  1428. $sessionId
  1429. );
  1430. $users[$row_key]['count_users'] += $counter;
  1431. $registered_users_with_extra_field = self::getCountRegisteredUsersWithCourseExtraField(
  1432. $name,
  1433. $tableExtraField,
  1434. $table_user_field_value
  1435. );
  1436. $users[$row_key]['count_users_registered'] = $registered_users_with_extra_field;
  1437. $users[$row_key]['average_hours_per_user'] = $users[$row_key]['training_hours'] / $users[$row_key]['count_users'];
  1438. $category = Category:: load(
  1439. null,
  1440. null,
  1441. $course_code,
  1442. null,
  1443. null,
  1444. $sessionId
  1445. );
  1446. if (!isset($users[$row_key]['count_certificates'])) {
  1447. $users[$row_key]['count_certificates'] = 0;
  1448. }
  1449. if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) {
  1450. $users[$row_key]['count_certificates']++;
  1451. }
  1452. foreach ($extra_fields as $extra) {
  1453. if ($extra['1'] == 'ruc') {
  1454. continue;
  1455. }
  1456. if (!isset($users[$row_key][$extra['1']])) {
  1457. $user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']);
  1458. if (!empty($user_data[$extra['1']])) {
  1459. $users[$row_key][$extra['1']] = $user_data[$extra['1']];
  1460. }
  1461. }
  1462. }
  1463. } else {
  1464. $sessionName = !empty($sessionId) ? ' - '.$user['session_name'] : '';
  1465. $report_info['course'] = $user['title'].$sessionName;
  1466. $report_info['user'] = api_get_person_name($user['firstname'], $user['lastname']);
  1467. $report_info['email'] = $user['email'];
  1468. $report_info['time'] = api_time_to_hms(
  1469. Tracking::get_time_spent_on_the_course(
  1470. $user['user_id'],
  1471. $courseId,
  1472. $sessionId
  1473. )
  1474. );
  1475. $category = Category:: load(
  1476. null,
  1477. null,
  1478. $course_code,
  1479. null,
  1480. null,
  1481. $sessionId
  1482. );
  1483. $report_info['certificate'] = Display::label(get_lang('No'));
  1484. if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) {
  1485. $report_info['certificate'] = Display::label(get_lang('Yes'), 'success');
  1486. }
  1487. $progress = intval(
  1488. Tracking::get_avg_student_progress(
  1489. $user['user_id'],
  1490. $course_code,
  1491. array(),
  1492. $sessionId
  1493. )
  1494. );
  1495. $report_info['progress_100'] = $progress == 100 ? Display::label(get_lang('Yes'), 'success') : Display::label(get_lang('No'));
  1496. $report_info['progress'] = $progress."%";
  1497. foreach ($extra_fields as $extra) {
  1498. $user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']);
  1499. $report_info[$extra['1']] = $user_data[$extra['1']];
  1500. }
  1501. $report_info['user_id'] = $user['user_id'];
  1502. $users[] = $report_info;
  1503. }
  1504. } else {
  1505. $users[$user['user_id']] = $user_info;
  1506. }
  1507. }
  1508. }
  1509. return $users;
  1510. }
  1511. /**
  1512. * @param bool $resumed_report
  1513. * @param array $extra_field
  1514. * @param array $courseCodeList
  1515. * @param array $userIdList
  1516. * @param array $sessionIdList
  1517. * @return array|int
  1518. */
  1519. public static function get_count_user_list_from_course_code(
  1520. $resumed_report = false,
  1521. $extra_field = array(),
  1522. $courseCodeList = array(),
  1523. $userIdList = array(),
  1524. $sessionIdList = array()
  1525. ) {
  1526. return self::get_user_list_from_course_code(
  1527. null,
  1528. 0,
  1529. null,
  1530. null,
  1531. null,
  1532. true,
  1533. false,
  1534. $resumed_report,
  1535. $extra_field,
  1536. $courseCodeList,
  1537. $userIdList,
  1538. null,
  1539. $sessionIdList
  1540. );
  1541. }
  1542. /**
  1543. * Gets subscribed users in a course or in a course/session
  1544. *
  1545. * @param string $course_code
  1546. * @param int $session_id
  1547. * @return int
  1548. */
  1549. public static function get_users_count_in_course(
  1550. $course_code,
  1551. $session_id = 0,
  1552. $status = null
  1553. ) {
  1554. // variable initialisation
  1555. $session_id = intval($session_id);
  1556. $course_code = Database::escape_string($course_code);
  1557. $tblUser = Database::get_main_table(TABLE_MAIN_USER);
  1558. $tblSessionCourseUser = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1559. $tblCourseUser = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1560. $tblUrlUser = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  1561. $courseInfo = api_get_course_info($course_code);
  1562. $courseId = $courseInfo['real_id'];
  1563. $sql = "
  1564. SELECT DISTINCT count(user.id) as count
  1565. FROM $tblUser as user
  1566. ";
  1567. $where = array();
  1568. if (!empty($session_id)) {
  1569. $sql .= "
  1570. LEFT JOIN $tblSessionCourseUser as session_course_user
  1571. ON user.user_id = session_course_user.user_id
  1572. AND session_course_user.c_id = $courseId
  1573. AND session_course_user.session_id = $session_id
  1574. ";
  1575. $where[] = ' session_course_user.c_id IS NOT NULL ';
  1576. } else {
  1577. $sql .= "
  1578. LEFT JOIN $tblCourseUser as course_rel_user
  1579. ON user.user_id = course_rel_user.user_id
  1580. AND course_rel_user.relation_type <> ".COURSE_RELATION_TYPE_RRHH."
  1581. AND course_rel_user.c_id = $courseId
  1582. ";
  1583. $where[] = ' course_rel_user.c_id IS NOT NULL ';
  1584. }
  1585. $multiple_access_url = api_get_multiple_access_url();
  1586. if ($multiple_access_url) {
  1587. $sql .= " LEFT JOIN $tblUrlUser au ON (au.user_id = user.user_id) ";
  1588. }
  1589. $sql .= ' WHERE '.implode(' OR ', $where);
  1590. if ($multiple_access_url) {
  1591. $current_access_url_id = api_get_current_access_url_id();
  1592. $sql .= " AND (access_url_id = $current_access_url_id ) ";
  1593. }
  1594. $rs = Database::query($sql);
  1595. $count = 0;
  1596. if (Database::num_rows($rs)) {
  1597. $user = Database::fetch_array($rs);
  1598. $count = $user['count'];
  1599. }
  1600. return $count;
  1601. }
  1602. /**
  1603. * Get a list of coaches of a course and a session
  1604. * @param string $course_code
  1605. * @param int $session_id
  1606. * @param bool $addGeneralCoach
  1607. * @return array List of users
  1608. */
  1609. public static function get_coach_list_from_course_code(
  1610. $course_code,
  1611. $session_id,
  1612. $addGeneralCoach = true
  1613. ) {
  1614. if (empty($course_code) || empty($session_id)) {
  1615. return array();
  1616. }
  1617. $course_code = Database::escape_string($course_code);
  1618. $courseInfo = api_get_course_info($course_code);
  1619. $courseId = $courseInfo['real_id'];
  1620. $session_id = intval($session_id);
  1621. $users = array();
  1622. // We get the coach for the given course in a given session.
  1623. $sql = 'SELECT user_id FROM '.Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER).
  1624. ' WHERE session_id ="'.$session_id.'" AND c_id="'.$courseId.'" AND status = 2';
  1625. $rs = Database::query($sql);
  1626. while ($user = Database::fetch_array($rs)) {
  1627. $userInfo = api_get_user_info($user['user_id']);
  1628. if ($userInfo) {
  1629. $users[$user['user_id']] = $userInfo;
  1630. }
  1631. }
  1632. if ($addGeneralCoach) {
  1633. $table = Database::get_main_table(TABLE_MAIN_SESSION);
  1634. // We get the session coach.
  1635. $sql = 'SELECT id_coach FROM '.$table.' WHERE id='.$session_id;
  1636. $rs = Database::query($sql);
  1637. $session_id_coach = Database::result($rs, 0, 'id_coach');
  1638. $userInfo = api_get_user_info($session_id_coach);
  1639. if ($userInfo) {
  1640. $users[$session_id_coach] = $userInfo;
  1641. }
  1642. }
  1643. return $users;
  1644. }
  1645. /**
  1646. * Return user info array of all users registered in a course
  1647. * This only returns the users that are registered in this actual course, not linked courses.
  1648. *
  1649. * @param string $course_code
  1650. * @param boolean $with_session
  1651. * @param integer $session_id
  1652. * @param string $date_from
  1653. * @param string $date_to
  1654. * @param boolean $includeInvitedUsers Whether include the invited users
  1655. * @param int $groupId
  1656. * @return array with user id
  1657. */
  1658. public static function get_student_list_from_course_code(
  1659. $course_code,
  1660. $with_session = false,
  1661. $session_id = 0,
  1662. $date_from = null,
  1663. $date_to = null,
  1664. $includeInvitedUsers = true,
  1665. $groupId = 0
  1666. ) {
  1667. $userTable = Database::get_main_table(TABLE_MAIN_USER);
  1668. $session_id = intval($session_id);
  1669. $course_code = Database::escape_string($course_code);
  1670. $courseInfo = api_get_course_info($course_code);
  1671. $courseId = $courseInfo['real_id'];
  1672. $students = array();
  1673. if ($session_id == 0) {
  1674. if (empty($groupId)) {
  1675. // students directly subscribed to the course
  1676. $sql = "SELECT *
  1677. FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)." cu
  1678. INNER JOIN $userTable u
  1679. ON cu.user_id = u.user_id
  1680. WHERE c_id = '$courseId' AND cu.status = ".STUDENT;
  1681. if (!$includeInvitedUsers) {
  1682. $sql .= " AND u.status != ".INVITEE;
  1683. }
  1684. $rs = Database::query($sql);
  1685. while ($student = Database::fetch_array($rs)) {
  1686. $students[$student['user_id']] = $student;
  1687. }
  1688. } else {
  1689. $students = GroupManager::get_users(
  1690. $groupId,
  1691. false,
  1692. null,
  1693. null,
  1694. false,
  1695. $courseInfo['real_id']
  1696. );
  1697. $students = array_flip($students);
  1698. }
  1699. }
  1700. // students subscribed to the course through a session
  1701. if ($with_session) {
  1702. $joinSession = '';
  1703. //Session creation date
  1704. if (!empty($date_from) && !empty($date_to)) {
  1705. $joinSession = "INNER JOIN ".Database::get_main_table(TABLE_MAIN_SESSION)." s";
  1706. }
  1707. $sql_query = "SELECT *
  1708. FROM " . Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)." scu
  1709. $joinSession
  1710. INNER JOIN $userTable u ON scu.user_id = u.user_id
  1711. WHERE scu.c_id = '$courseId' AND scu.status <> 2";
  1712. if (!empty($date_from) && !empty($date_to)) {
  1713. $date_from = Database::escape_string($date_from);
  1714. $date_to = Database::escape_string($date_to);
  1715. $sql_query .= " AND s.access_start_date >= '$date_from' AND s.access_end_date <= '$date_to'";
  1716. }
  1717. if ($session_id != 0) {
  1718. $sql_query .= ' AND scu.session_id = '.$session_id;
  1719. }
  1720. if (!$includeInvitedUsers) {
  1721. $sql_query .= " AND u.status != ".INVITEE;
  1722. }
  1723. $rs = Database::query($sql_query);
  1724. while ($student = Database::fetch_array($rs)) {
  1725. $students[$student['user_id']] = $student;
  1726. }
  1727. }
  1728. return $students;
  1729. }
  1730. /**
  1731. * Return user info array of all teacher-users registered in a course
  1732. * This only returns the users that are registered in this actual course, not linked courses.
  1733. *
  1734. * @param string $course_code
  1735. * @return array with user id
  1736. */
  1737. public static function get_teacher_list_from_course_code($course_code)
  1738. {
  1739. $courseInfo = api_get_course_info($course_code);
  1740. $courseId = $courseInfo['real_id'];
  1741. if (empty($courseId)) {
  1742. return false;
  1743. }
  1744. $sql = "SELECT DISTINCT
  1745. u.id as user_id,
  1746. u.lastname,
  1747. u.firstname,
  1748. u.email,
  1749. u.username,
  1750. u.status
  1751. FROM " . Database::get_main_table(TABLE_MAIN_COURSE_USER)." cu
  1752. INNER JOIN " . Database::get_main_table(TABLE_MAIN_USER)." u
  1753. ON (cu.user_id = u.id)
  1754. WHERE
  1755. cu.c_id = $courseId AND
  1756. cu.status = 1 ";
  1757. $rs = Database::query($sql);
  1758. $teachers = array();
  1759. while ($teacher = Database::fetch_array($rs)) {
  1760. $teachers[$teacher['user_id']] = $teacher;
  1761. }
  1762. return $teachers;
  1763. }
  1764. /**
  1765. * Return user info array of all teacher-users registered in a course
  1766. * This only returns the users that are registered in this actual course, not linked courses.
  1767. *
  1768. * @param int $courseId
  1769. * @param bool $loadAvatars
  1770. *
  1771. * @return array with user id
  1772. */
  1773. public static function getTeachersFromCourse($courseId, $loadAvatars = true)
  1774. {
  1775. $courseId = (int) $courseId;
  1776. if (empty($courseId)) {
  1777. return false;
  1778. }
  1779. $sql = "SELECT DISTINCT
  1780. u.id as user_id,
  1781. u.lastname,
  1782. u.firstname,
  1783. u.email,
  1784. u.username,
  1785. u.status
  1786. FROM " . Database::get_main_table(TABLE_MAIN_COURSE_USER)." cu
  1787. INNER JOIN " . Database::get_main_table(TABLE_MAIN_USER)." u
  1788. ON (cu.user_id = u.id)
  1789. WHERE
  1790. cu.c_id = $courseId AND
  1791. cu.status = 1 ";
  1792. $rs = Database::query($sql);
  1793. $listTeachers = array();
  1794. $teachers = array();
  1795. $url = api_get_path(WEB_AJAX_PATH).'user_manager.ajax.php?a=get_user_popup&course_id='.$courseId;
  1796. while ($teacher = Database::fetch_array($rs)) {
  1797. $teachers['id'] = $teacher['user_id'];
  1798. $teachers['lastname'] = $teacher['lastname'];
  1799. $teachers['firstname'] = $teacher['firstname'];
  1800. $teachers['email'] = $teacher['email'];
  1801. $teachers['username'] = $teacher['username'];
  1802. $teachers['status'] = $teacher['status'];
  1803. $teachers['fullname'] = api_get_person_name($teacher['firstname'], $teacher['lastname']);
  1804. $teachers['avatar'] = '';
  1805. if ($loadAvatars) {
  1806. $userPicture = UserManager::getUserPicture($teacher['user_id'], USER_IMAGE_SIZE_SMALL);
  1807. $teachers['avatar'] = $userPicture;
  1808. }
  1809. $teachers['url'] = $url.'&user_id='.$teacher['user_id'];
  1810. $listTeachers[] = $teachers;
  1811. }
  1812. return $listTeachers;
  1813. }
  1814. /**
  1815. * Returns a string list of teachers assigned to the given course
  1816. * @param string $course_code
  1817. * @param string $separator between teachers names
  1818. * @param bool $add_link_to_profile Whether to add a link to the teacher's profile
  1819. * @param bool $orderList
  1820. * @return string List of teachers teaching the course
  1821. */
  1822. public static function getTeacherListFromCourseCodeToString(
  1823. $course_code,
  1824. $separator = self::USER_SEPARATOR,
  1825. $add_link_to_profile = false,
  1826. $orderList = false
  1827. ) {
  1828. $teacher_list = self::get_teacher_list_from_course_code($course_code);
  1829. $html = '';
  1830. $list = array();
  1831. if (!empty($teacher_list)) {
  1832. foreach ($teacher_list as $teacher) {
  1833. $teacher_name = api_get_person_name(
  1834. $teacher['firstname'],
  1835. $teacher['lastname']
  1836. );
  1837. if ($add_link_to_profile) {
  1838. $url = api_get_path(WEB_AJAX_PATH).'user_manager.ajax.php?a=get_user_popup&user_id='.$teacher['user_id'];
  1839. $teacher_name = Display::url(
  1840. $teacher_name,
  1841. $url,
  1842. [
  1843. 'class' => 'ajax',
  1844. 'data-title' => $teacher_name
  1845. ]
  1846. );
  1847. }
  1848. $list[] = $teacher_name;
  1849. }
  1850. if (!empty($list)) {
  1851. if ($orderList === true) {
  1852. $html .= '<ul class="user-teacher">';
  1853. foreach ($list as $teacher) {
  1854. $html .= '<li>';
  1855. $html .= Display::return_icon('teacher.png', '', null, ICON_SIZE_TINY);
  1856. $html .= ' '.$teacher;
  1857. $html .= '</li>';
  1858. }
  1859. $html .= '</ul>';
  1860. } else {
  1861. $html .= array_to_string($list, $separator);
  1862. }
  1863. }
  1864. }
  1865. return $html;
  1866. }
  1867. /**
  1868. * This function returns information about coachs from a course in session
  1869. * @param int $session_id
  1870. * @param int $courseId
  1871. *
  1872. * @return array containing user_id, lastname, firstname, username
  1873. */
  1874. public static function get_coachs_from_course($session_id = 0, $courseId = 0)
  1875. {
  1876. if (!empty($session_id)) {
  1877. $session_id = intval($session_id);
  1878. } else {
  1879. $session_id = api_get_session_id();
  1880. }
  1881. if (!empty($courseId)) {
  1882. $courseId = intval($courseId);
  1883. } else {
  1884. $courseId = api_get_course_int_id();
  1885. }
  1886. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1887. $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1888. $sql = "SELECT DISTINCT
  1889. u.user_id,
  1890. u.lastname,
  1891. u.firstname,
  1892. u.username
  1893. FROM $tbl_user u
  1894. INNER JOIN $tbl_session_course_user scu
  1895. ON (u.user_id = scu.user_id)
  1896. WHERE
  1897. scu.session_id = '$session_id' AND
  1898. scu.c_id = '$courseId' AND
  1899. scu.status = 2";
  1900. $rs = Database::query($sql);
  1901. $coaches = array();
  1902. if (Database::num_rows($rs) > 0) {
  1903. while ($row = Database::fetch_array($rs)) {
  1904. $completeName = api_get_person_name($row['firstname'], $row['lastname']);
  1905. $coaches[] = $row + ['full_name' => $completeName];
  1906. }
  1907. }
  1908. return $coaches;
  1909. }
  1910. /**
  1911. * @param int $session_id
  1912. * @param int $courseId
  1913. * @param string $separator
  1914. * @param bool $add_link_to_profile
  1915. * @param bool $orderList
  1916. * @return string
  1917. */
  1918. public static function get_coachs_from_course_to_string(
  1919. $session_id = 0,
  1920. $courseId = 0,
  1921. $separator = self::USER_SEPARATOR,
  1922. $add_link_to_profile = false,
  1923. $orderList = false
  1924. ) {
  1925. $coachList = self::get_coachs_from_course($session_id, $courseId);
  1926. $course_coachs = array();
  1927. if (!empty($coachList)) {
  1928. foreach ($coachList as $coach_course) {
  1929. $coach_name = $coach_course['full_name'];
  1930. if ($add_link_to_profile) {
  1931. $url = api_get_path(WEB_AJAX_PATH).'user_manager.ajax.php?a=get_user_popup&user_id='.$coach_course['user_id'].'&course_id='.$courseId.'&session_id='.$session_id;
  1932. $coach_name = Display::url(
  1933. $coach_name,
  1934. $url,
  1935. [
  1936. 'class' => 'ajax',
  1937. 'data-title' => $coach_name
  1938. ]
  1939. );
  1940. }
  1941. $course_coachs[] = $coach_name;
  1942. }
  1943. }
  1944. $html = '';
  1945. if (!empty($course_coachs)) {
  1946. if ($orderList === true) {
  1947. $html .= '<ul class="user-coachs">';
  1948. foreach ($course_coachs as $coachs) {
  1949. $html .= Display::tag(
  1950. 'li',
  1951. Display::return_icon(
  1952. 'teacher.png',
  1953. $coachs,
  1954. null,
  1955. ICON_SIZE_TINY
  1956. ).' '.$coachs
  1957. );
  1958. }
  1959. $html .= '</ul>';
  1960. } else {
  1961. $html = array_to_string($course_coachs, $separator);
  1962. }
  1963. }
  1964. return $html;
  1965. }
  1966. /**
  1967. * Get the list of groups from the course
  1968. * @param string $course_code
  1969. * @param int $session_id Session ID (optional)
  1970. * @param integer $in_get_empty_group get empty groups (optional)
  1971. * @return array List of groups info
  1972. */
  1973. public static function get_group_list_of_course(
  1974. $course_code,
  1975. $session_id = 0,
  1976. $in_get_empty_group = 0
  1977. ) {
  1978. $course_info = api_get_course_info($course_code);
  1979. if (empty($course_info)) {
  1980. return array();
  1981. }
  1982. $course_id = $course_info['real_id'];
  1983. if (empty($course_id)) {
  1984. return array();
  1985. }
  1986. $group_list = array();
  1987. $session_id != 0 ? $session_condition = ' WHERE g.session_id IN(1,'.intval($session_id).')' : $session_condition = ' WHERE g.session_id = 0';
  1988. if ($in_get_empty_group == 0) {
  1989. // get only groups that are not empty
  1990. $sql = "SELECT DISTINCT g.id, g.iid, g.name
  1991. FROM " . Database::get_course_table(TABLE_GROUP)." AS g
  1992. INNER JOIN " . Database::get_course_table(TABLE_GROUP_USER)." gu
  1993. ON (g.id = gu.group_id AND g.c_id = $course_id AND gu.c_id = $course_id)
  1994. $session_condition
  1995. ORDER BY g.name";
  1996. } else {
  1997. // get all groups even if they are empty
  1998. $sql = "SELECT g.id, g.name, g.iid
  1999. FROM " . Database::get_course_table(TABLE_GROUP)." AS g
  2000. $session_condition
  2001. AND c_id = $course_id";
  2002. }
  2003. $result = Database::query($sql);
  2004. while ($group_data = Database::fetch_array($result)) {
  2005. $group_data['userNb'] = GroupManager::number_of_students($group_data['id'], $course_id);
  2006. $group_list[$group_data['id']] = $group_data;
  2007. }
  2008. return $group_list;
  2009. }
  2010. /**
  2011. * Delete a course
  2012. * This function deletes a whole course-area from the platform. When the
  2013. * given course is a virtual course, the database and directory will not be
  2014. * deleted.
  2015. * When the given course is a real course, also all virtual courses refering
  2016. * to the given course will be deleted.
  2017. * Considering the fact that we remove all traces of the course in the main
  2018. * database, it makes sense to remove all tracking as well (if stats databases exist)
  2019. * so that a new course created with this code would not use the remains of an older
  2020. * course.
  2021. *
  2022. * @param string $code The code of the course to delete
  2023. * @todo When deleting a virtual course: unsubscribe users from that virtual
  2024. * course from the groups in the real course if they are not subscribed in
  2025. * that real course.
  2026. * @todo Remove globals
  2027. */
  2028. public static function delete_course($code)
  2029. {
  2030. $table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  2031. $table_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  2032. $table_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  2033. $table_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  2034. $table_course_survey = Database::get_main_table(TABLE_MAIN_SHARED_SURVEY);
  2035. $table_course_survey_question = Database::get_main_table(TABLE_MAIN_SHARED_SURVEY_QUESTION);
  2036. $table_course_survey_question_option = Database::get_main_table(TABLE_MAIN_SHARED_SURVEY_QUESTION_OPTION);
  2037. $table_course_rel_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  2038. $table_stats_hotpots = Database::get_main_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
  2039. $table_stats_attempt = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  2040. $table_stats_exercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  2041. $table_stats_access = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ACCESS);
  2042. $table_stats_lastaccess = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
  2043. $table_stats_course_access = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  2044. $table_stats_online = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ONLINE);
  2045. $table_stats_default = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
  2046. $table_stats_downloads = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
  2047. $table_stats_links = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LINKS);
  2048. $table_stats_uploads = Database::get_main_table(TABLE_STATISTIC_TRACK_E_UPLOADS);
  2049. $codeFiltered = Database::escape_string($code);
  2050. $sql = "SELECT * FROM $table_course WHERE code='".$codeFiltered."'";
  2051. $res = Database::query($sql);
  2052. if (Database::num_rows($res) == 0) {
  2053. return;
  2054. }
  2055. $sql = "SELECT * FROM $table_course
  2056. WHERE code = '".$codeFiltered."'";
  2057. $res = Database::query($sql);
  2058. $course = Database::fetch_array($res);
  2059. $courseId = $course['id'];
  2060. $count = 0;
  2061. if (api_is_multiple_url_enabled()) {
  2062. $url_id = 1;
  2063. if (api_get_current_access_url_id() != -1) {
  2064. $url_id = api_get_current_access_url_id();
  2065. }
  2066. UrlManager::delete_url_rel_course($courseId, $url_id);
  2067. $count = UrlManager::getCountUrlRelCourse($courseId);
  2068. }
  2069. if ($count == 0) {
  2070. self::create_database_dump($code);
  2071. $course_tables = AddCourse::get_course_tables();
  2072. // Cleaning group categories
  2073. $groupCategories = GroupManager::get_categories($course['code']);
  2074. if (!empty($groupCategories)) {
  2075. foreach ($groupCategories as $category) {
  2076. GroupManager::delete_category($category['id'], $course['code']);
  2077. }
  2078. }
  2079. // Cleaning groups
  2080. $groups = GroupManager::get_groups($courseId);
  2081. if (!empty($groups)) {
  2082. foreach ($groups as $group) {
  2083. GroupManager::delete_groups($group, $course['code']);
  2084. }
  2085. }
  2086. // Cleaning c_x tables
  2087. if (!empty($courseId)) {
  2088. foreach ($course_tables as $table) {
  2089. $table = Database::get_course_table($table);
  2090. $sql = "DELETE FROM $table WHERE c_id = $courseId ";
  2091. Database::query($sql);
  2092. }
  2093. }
  2094. $course_dir = api_get_path(SYS_COURSE_PATH).$course['directory'];
  2095. $archive_dir = api_get_path(SYS_ARCHIVE_PATH).$course['directory'].'_'.time();
  2096. if (is_dir($course_dir)) {
  2097. rename($course_dir, $archive_dir);
  2098. }
  2099. // Unsubscribe all users from the course
  2100. $sql = "DELETE FROM $table_course_user WHERE c_id='".$courseId."'";
  2101. Database::query($sql);
  2102. // Delete the course from the sessions tables
  2103. $sql = "DELETE FROM $table_session_course WHERE c_id='".$courseId."'";
  2104. Database::query($sql);
  2105. $sql = "DELETE FROM $table_session_course_user WHERE c_id='".$courseId."'";
  2106. Database::query($sql);
  2107. // Delete from Course - URL
  2108. $sql = "DELETE FROM $table_course_rel_url WHERE c_id = '".$courseId."'";
  2109. Database::query($sql);
  2110. $sql = 'SELECT survey_id FROM '.$table_course_survey.' WHERE course_code="'.$codeFiltered.'"';
  2111. $result_surveys = Database::query($sql);
  2112. while ($surveys = Database::fetch_array($result_surveys)) {
  2113. $survey_id = $surveys[0];
  2114. $sql = 'DELETE FROM '.$table_course_survey_question.' WHERE survey_id="'.$survey_id.'"';
  2115. Database::query($sql);
  2116. $sql = 'DELETE FROM '.$table_course_survey_question_option.' WHERE survey_id="'.$survey_id.'"';
  2117. Database::query($sql);
  2118. $sql = 'DELETE FROM '.$table_course_survey.' WHERE survey_id="'.$survey_id.'"';
  2119. Database::query($sql);
  2120. }
  2121. // Delete the course from the stats tables
  2122. $sql = "DELETE FROM $table_stats_hotpots WHERE c_id = $courseId";
  2123. Database::query($sql);
  2124. $sql = "DELETE FROM $table_stats_attempt WHERE c_id = $courseId";
  2125. Database::query($sql);
  2126. $sql = "DELETE FROM $table_stats_exercises WHERE c_id = $courseId";
  2127. Database::query($sql);
  2128. $sql = "DELETE FROM $table_stats_access WHERE c_id = $courseId";
  2129. Database::query($sql);
  2130. $sql = "DELETE FROM $table_stats_lastaccess WHERE c_id = $courseId";
  2131. Database::query($sql);
  2132. $sql = "DELETE FROM $table_stats_course_access WHERE c_id = $courseId";
  2133. Database::query($sql);
  2134. $sql = "DELETE FROM $table_stats_online WHERE c_id = $courseId";
  2135. Database::query($sql);
  2136. // Do not delete rows from track_e_default as these include course
  2137. // creation and other important things that do not take much space
  2138. // but give information on the course history
  2139. //$sql = "DELETE FROM $table_stats_default WHERE c_id = $courseId";
  2140. //Database::query($sql);
  2141. $sql = "DELETE FROM $table_stats_downloads WHERE c_id = $courseId";
  2142. Database::query($sql);
  2143. $sql = "DELETE FROM $table_stats_links WHERE c_id = $courseId";
  2144. Database::query($sql);
  2145. $sql = "DELETE FROM $table_stats_uploads WHERE c_id = $courseId";
  2146. Database::query($sql);
  2147. // Update ticket
  2148. $table = Database::get_main_table(TABLE_TICKET_TICKET);
  2149. $sql = "UPDATE $table SET course_id = NULL WHERE course_id = $courseId";
  2150. Database::query($sql);
  2151. // Class
  2152. $table = Database::get_main_table(TABLE_USERGROUP_REL_COURSE);
  2153. $sql = "DELETE FROM $table
  2154. WHERE course_id = $courseId";
  2155. Database::query($sql);
  2156. // Skills
  2157. $table = Database::get_main_table(TABLE_MAIN_SKILL_REL_USER);
  2158. $argumentation = Database::escape_string(sprintf(get_lang('SkillFromCourseXDeletedSinceThen'), $course['code']));
  2159. $sql = "UPDATE $table SET course_id = NULL, session_id = NULL, argumentation = '$argumentation' WHERE course_id = $courseId";
  2160. Database::query($sql);
  2161. Category::deleteCategoryFromCourse($courseId);
  2162. // Delete the course from the database
  2163. $sql = "DELETE FROM gradebook_link WHERE c_id = '" . $courseId . "'";
  2164. Database::query($sql);
  2165. // Delete the course from the database
  2166. $sql = "DELETE FROM $table_course WHERE code = '" . $codeFiltered . "'";
  2167. Database::query($sql);
  2168. // delete extra course fields
  2169. $extraFieldValues = new ExtraFieldValue('course');
  2170. $extraFieldValues->deleteValuesByItem($courseId);
  2171. // Add event to system log
  2172. $user_id = api_get_user_id();
  2173. Event::addEvent(
  2174. LOG_COURSE_DELETE,
  2175. LOG_COURSE_CODE,
  2176. $code,
  2177. api_get_utc_datetime(),
  2178. $user_id,
  2179. $courseId
  2180. );
  2181. }
  2182. }
  2183. /**
  2184. * Creates a file called mysql_dump.sql in the course folder
  2185. * @param string $course_code The code of the course
  2186. * @todo Implementation for single database
  2187. */
  2188. public static function create_database_dump($course_code)
  2189. {
  2190. $sql_dump = '';
  2191. $course_code = Database::escape_string($course_code);
  2192. $table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  2193. $sql = "SELECT * FROM $table_course WHERE code = '$course_code'";
  2194. $res = Database::query($sql);
  2195. $course = Database::fetch_array($res);
  2196. $course_tables = AddCourse::get_course_tables();
  2197. if (!empty($course['id'])) {
  2198. //Cleaning c_x tables
  2199. foreach ($course_tables as $table) {
  2200. $table = Database::get_course_table($table);
  2201. $sql = "SELECT * FROM $table WHERE c_id = {$course['id']} ";
  2202. $res_table = Database::query($sql);
  2203. while ($row = Database::fetch_array($res_table, 'ASSOC')) {
  2204. $row_to_save = array();
  2205. foreach ($row as $key => $value) {
  2206. $row_to_save[$key] = $key."='".Database::escape_string($row[$key])."'";
  2207. }
  2208. $sql_dump .= "\nINSERT INTO $table SET ".implode(', ', $row_to_save).';';
  2209. }
  2210. }
  2211. }
  2212. if (is_dir(api_get_path(SYS_COURSE_PATH).$course['directory'])) {
  2213. $file_name = api_get_path(SYS_COURSE_PATH).$course['directory'].'/mysql_dump.sql';
  2214. $handle = fopen($file_name, 'a+');
  2215. if ($handle !== false) {
  2216. fwrite($handle, $sql_dump);
  2217. fclose($handle);
  2218. } else {
  2219. //TODO trigger exception in a try-catch
  2220. }
  2221. }
  2222. }
  2223. /**
  2224. * Sort courses for a specific user ??
  2225. * @param int $user_id User ID
  2226. * @param string $course_code Course code
  2227. * @return int Minimum course order
  2228. * @todo Review documentation
  2229. */
  2230. public static function userCourseSort($user_id, $course_code)
  2231. {
  2232. if ($user_id != strval(intval($user_id))) {
  2233. return false;
  2234. }
  2235. $course_code = Database::escape_string($course_code);
  2236. $TABLECOURSE = Database::get_main_table(TABLE_MAIN_COURSE);
  2237. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  2238. $course_title = Database::result(
  2239. Database::query(
  2240. 'SELECT title FROM '.$TABLECOURSE.' WHERE code="'.$course_code.'"'
  2241. ),
  2242. 0,
  2243. 0
  2244. );
  2245. $sql = 'SELECT course.code as code, course.title as title, cu.sort as sort
  2246. FROM ' . $TABLECOURSUSER.' as cu, '.$TABLECOURSE.' as course
  2247. WHERE course.id = cu.c_id AND user_id = "' . $user_id.'" AND
  2248. cu.relation_type<>' . COURSE_RELATION_TYPE_RRHH.' AND
  2249. user_course_cat = 0
  2250. ORDER BY cu.sort';
  2251. $result = Database::query($sql);
  2252. $course_title_precedent = '';
  2253. $counter = 0;
  2254. $course_found = false;
  2255. $course_sort = 1;
  2256. if (Database::num_rows($result) > 0) {
  2257. while ($courses = Database::fetch_array($result)) {
  2258. if ($course_title_precedent == '') {
  2259. $course_title_precedent = $courses['title'];
  2260. }
  2261. if (api_strcasecmp($course_title_precedent, $course_title) < 0) {
  2262. $course_found = true;
  2263. $course_sort = $courses['sort'];
  2264. if ($counter == 0) {
  2265. $sql = 'UPDATE '.$TABLECOURSUSER.'
  2266. SET sort = sort+1
  2267. WHERE
  2268. user_id= "' . $user_id.'" AND
  2269. relation_type<>' . COURSE_RELATION_TYPE_RRHH.'
  2270. AND user_course_cat="0"
  2271. AND sort > "' . $course_sort.'"';
  2272. $course_sort++;
  2273. } else {
  2274. $sql = 'UPDATE '.$TABLECOURSUSER.' SET sort = sort+1
  2275. WHERE
  2276. user_id= "' . $user_id.'" AND
  2277. relation_type<>' . COURSE_RELATION_TYPE_RRHH.' AND
  2278. user_course_cat="0" AND
  2279. sort >= "' . $course_sort.'"';
  2280. }
  2281. Database::query($sql);
  2282. break;
  2283. } else {
  2284. $course_title_precedent = $courses['title'];
  2285. }
  2286. $counter++;
  2287. }
  2288. // We must register the course in the beginning of the list
  2289. if (!$course_found) {
  2290. $course_sort = Database::result(Database::query('SELECT min(sort) as min_sort FROM '.$TABLECOURSUSER.' WHERE user_id="'.$user_id.'" AND user_course_cat="0"'),
  2291. 0, 0);
  2292. Database::query('UPDATE '.$TABLECOURSUSER.' SET sort = sort+1 WHERE user_id= "'.$user_id.'" AND user_course_cat="0"');
  2293. }
  2294. }
  2295. return $course_sort;
  2296. }
  2297. /**
  2298. * check if course exists
  2299. * @param string $course_code
  2300. * @return integer if exists, false else
  2301. */
  2302. public static function course_exists($course_code)
  2303. {
  2304. $sql = 'SELECT 1 FROM '.Database::get_main_table(TABLE_MAIN_COURSE).'
  2305. WHERE code="' . Database::escape_string($course_code).'"';
  2306. return Database::num_rows(Database::query($sql));
  2307. }
  2308. /**
  2309. * Send an email to tutor after the auth-suscription of a student in your course
  2310. * @author Carlos Vargas <carlos.vargas@dokeos.com>, Dokeos Latino
  2311. * @param int $user_id the id of the user
  2312. * @param string $courseId the course code
  2313. * @param bool $send_to_tutor_also
  2314. * @return false|null we return the message that is displayed when the action is successful
  2315. */
  2316. public static function email_to_tutor($user_id, $courseId, $send_to_tutor_also = false)
  2317. {
  2318. if ($user_id != strval(intval($user_id))) {
  2319. return false;
  2320. }
  2321. $courseId = intval($courseId);
  2322. $information = api_get_course_info_by_id($courseId);
  2323. $course_code = $information['code'];
  2324. $student = api_get_user_info($user_id);
  2325. $name_course = $information['title'];
  2326. $sql = "SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  2327. WHERE c_id ='" . $courseId."'";
  2328. // TODO: Ivan: This is a mistake, please, have a look at it. Intention here is diffcult to be guessed.
  2329. //if ($send_to_tutor_also = true)
  2330. // Proposed change:
  2331. if ($send_to_tutor_also) {
  2332. $sql .= " AND is_tutor=1";
  2333. } else {
  2334. $sql .= " AND status=1";
  2335. }
  2336. $result = Database::query($sql);
  2337. while ($row = Database::fetch_array($result)) {
  2338. $tutor = api_get_user_info($row['user_id']);
  2339. $emailto = $tutor['email'];
  2340. $emailsubject = get_lang('NewUserInTheCourse').': '.$name_course;
  2341. $emailbody = get_lang('Dear').': '.api_get_person_name($tutor['firstname'], $tutor['lastname'])."\n";
  2342. $emailbody .= get_lang('MessageNewUserInTheCourse').': '.$name_course."\n";
  2343. $emailbody .= get_lang('UserName').': '.$student['username']."\n";
  2344. if (api_is_western_name_order()) {
  2345. $emailbody .= get_lang('FirstName').': '.$student['firstname']."\n";
  2346. $emailbody .= get_lang('LastName').': '.$student['lastname']."\n";
  2347. } else {
  2348. $emailbody .= get_lang('LastName').': '.$student['lastname']."\n";
  2349. $emailbody .= get_lang('FirstName').': '.$student['firstname']."\n";
  2350. }
  2351. $emailbody .= get_lang('Email').': <a href="mailto:'.$student['email'].'">'.$student['email']."</a>\n\n";
  2352. $recipient_name = api_get_person_name($tutor['firstname'], $tutor['lastname'], null,
  2353. PERSON_NAME_EMAIL_ADDRESS);
  2354. $sender_name = api_get_person_name(api_get_setting('administratorName'),
  2355. api_get_setting('administratorSurname'), null, PERSON_NAME_EMAIL_ADDRESS);
  2356. $email_admin = api_get_setting('emailAdministrator');
  2357. $additionalParameters = array(
  2358. 'smsType' => SmsPlugin::NEW_USER_SUBSCRIBED_COURSE,
  2359. 'userId' => $tutor['user_id'],
  2360. 'userUsername' => $student['username'],
  2361. 'courseCode' => $course_code
  2362. );
  2363. api_mail_html(
  2364. $recipient_name,
  2365. $emailto,
  2366. $emailsubject,
  2367. $emailbody,
  2368. $sender_name,
  2369. $email_admin,
  2370. null,
  2371. null,
  2372. null,
  2373. $additionalParameters
  2374. );
  2375. }
  2376. }
  2377. /**
  2378. * @return array
  2379. */
  2380. public static function get_special_course_list()
  2381. {
  2382. $courseTable = Database::get_main_table(TABLE_MAIN_COURSE);
  2383. $tbl_course_field = Database::get_main_table(TABLE_EXTRA_FIELD);
  2384. $tbl_course_field_value = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
  2385. //we filter the courses from the URL
  2386. $join_access_url = $where_access_url = '';
  2387. if (api_get_multiple_access_url()) {
  2388. $access_url_id = api_get_current_access_url_id();
  2389. if ($access_url_id != -1) {
  2390. $tbl_url_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  2391. $join_access_url = "LEFT JOIN $tbl_url_course url_rel_course
  2392. ON url_rel_course.c_id = tcfv.item_id ";
  2393. $where_access_url = " AND access_url_id = $access_url_id ";
  2394. }
  2395. }
  2396. $extraFieldType = EntityExtraField::COURSE_FIELD_TYPE;
  2397. // get course list auto-register
  2398. $sql = "SELECT DISTINCT(c.id)
  2399. FROM $tbl_course_field_value tcfv
  2400. INNER JOIN $tbl_course_field tcf
  2401. ON tcfv.field_id = tcf.id $join_access_url
  2402. INNER JOIN $courseTable c
  2403. ON (c.id = tcfv.item_id)
  2404. WHERE
  2405. tcf.extra_field_type = $extraFieldType AND
  2406. tcf.variable = 'special_course' AND
  2407. tcfv.value = 1 $where_access_url";
  2408. $result = Database::query($sql);
  2409. $courseList = array();
  2410. if (Database::num_rows($result) > 0) {
  2411. while ($row = Database::fetch_array($result)) {
  2412. $courseList[] = $row['id'];
  2413. }
  2414. }
  2415. return $courseList;
  2416. }
  2417. /**
  2418. * Get the course codes that have been restricted in the catalogue, and if byUserId is set
  2419. * then the courses that the user is allowed or not to see in catalogue
  2420. *
  2421. * @param bool $allowed Either if the courses have some users that are or are not allowed to see in catalogue
  2422. * @param int $byUserId if the courses are or are not allowed to see to the user
  2423. * @return array Course codes allowed or not to see in catalogue by some user or the user
  2424. */
  2425. public static function getCatalogueCourseList($allowed = true, $byUserId = -1)
  2426. {
  2427. $courseTable = Database::get_main_table(TABLE_MAIN_COURSE);
  2428. $tblCourseRelUserCatalogue = Database::get_main_table(TABLE_MAIN_COURSE_CATALOGUE_USER);
  2429. $visibility = $allowed ? 1 : 0;
  2430. // Restriction by user id
  2431. $currentUserRestriction = '';
  2432. if ($byUserId > 0) {
  2433. $byUserId = (int) $byUserId;
  2434. $currentUserRestriction = " AND tcruc.user_id = $byUserId ";
  2435. }
  2436. //we filter the courses from the URL
  2437. $joinAccessUrl = '';
  2438. $whereAccessUrl = '';
  2439. if (api_get_multiple_access_url()) {
  2440. $accessUrlId = api_get_current_access_url_id();
  2441. if ($accessUrlId != -1) {
  2442. $tblUrlCourse = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  2443. $joinAccessUrl = "LEFT JOIN $tblUrlCourse url_rel_course
  2444. ON url_rel_course.c_id = c.id ";
  2445. $whereAccessUrl = " AND access_url_id = $accessUrlId ";
  2446. }
  2447. }
  2448. // get course list auto-register
  2449. $sql = "SELECT DISTINCT(c.code)
  2450. FROM $tblCourseRelUserCatalogue tcruc
  2451. INNER JOIN $courseTable c
  2452. ON (c.id = tcruc.c_id) $joinAccessUrl
  2453. WHERE tcruc.visible = $visibility $currentUserRestriction $whereAccessUrl";
  2454. $result = Database::query($sql);
  2455. $courseList = array();
  2456. if (Database::num_rows($result) > 0) {
  2457. while ($resultRow = Database::fetch_array($result)) {
  2458. $courseList[] = $resultRow['code'];
  2459. }
  2460. }
  2461. return $courseList;
  2462. }
  2463. /**
  2464. * Get list of courses for a given user
  2465. *
  2466. * @param int $user_id
  2467. * @param boolean $include_sessions Whether to include courses from session or not
  2468. * @param boolean $adminGetsAllCourses If the user is platform admin,
  2469. * whether he gets all the courses or just his. Note: This does *not* include all sessions
  2470. * @param bool $loadSpecialCourses
  2471. * @param array $skipCourseList List of course ids to skip
  2472. * @param bool $useUserLanguageFilterIfAvailable
  2473. * @return array List of codes and db name
  2474. * @author isaac flores paz
  2475. */
  2476. public static function get_courses_list_by_user_id(
  2477. $user_id,
  2478. $include_sessions = false,
  2479. $adminGetsAllCourses = false,
  2480. $loadSpecialCourses = true,
  2481. $skipCourseList = [],
  2482. $useUserLanguageFilterIfAvailable = true
  2483. ) {
  2484. $user_id = intval($user_id);
  2485. $urlId = api_get_current_access_url_id();
  2486. $course_list = array();
  2487. $codes = array();
  2488. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  2489. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  2490. $tbl_user_course_category = Database::get_main_table(TABLE_USER_COURSE_CATEGORY);
  2491. $tableCourseUrl = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  2492. $languageCondition = '';
  2493. $onlyInUserLanguage = api_get_configuration_value('my_courses_show_courses_in_user_language_only');
  2494. if ($useUserLanguageFilterIfAvailable && $onlyInUserLanguage) {
  2495. $userInfo = api_get_user_info(api_get_user_id());
  2496. if (!empty($userInfo['language'])) {
  2497. $languageCondition = " AND course.course_language = '".$userInfo['language']."' ";
  2498. }
  2499. }
  2500. if ($adminGetsAllCourses && UserManager::is_admin($user_id)) {
  2501. // get the whole courses list
  2502. $sql = "SELECT DISTINCT(course.code), course.id as real_id, course.title
  2503. FROM $tbl_course course
  2504. INNER JOIN $tableCourseUrl url
  2505. ON (course.id = url.c_id)
  2506. WHERE
  2507. url.access_url_id = $urlId
  2508. $languageCondition
  2509. ";
  2510. } else {
  2511. $withSpecialCourses = $withoutSpecialCourses = '';
  2512. if ($loadSpecialCourses) {
  2513. $specialCourseList = self::get_special_course_list();
  2514. if (!empty($specialCourseList)) {
  2515. $specialCourseToString = '"'.implode('","', $specialCourseList).'"';
  2516. $withSpecialCourses = ' AND course.id IN ('.$specialCourseToString.')';
  2517. $withoutSpecialCourses = ' AND course.id NOT IN ('.$specialCourseToString.')';
  2518. }
  2519. if (!empty($withSpecialCourses)) {
  2520. $sql = "SELECT DISTINCT (course.code),
  2521. course.id as real_id,
  2522. course.category_code AS category,
  2523. course.title
  2524. FROM $tbl_course_user course_rel_user
  2525. LEFT JOIN $tbl_course course
  2526. ON course.id = course_rel_user.c_id
  2527. LEFT JOIN $tbl_user_course_category user_course_category
  2528. ON course_rel_user.user_course_cat = user_course_category.id
  2529. INNER JOIN $tableCourseUrl url
  2530. ON (course.id = url.c_id)
  2531. WHERE url.access_url_id = $urlId
  2532. $withSpecialCourses
  2533. $languageCondition
  2534. GROUP BY course.code
  2535. ORDER BY user_course_category.sort, course.title, course_rel_user.sort ASC
  2536. ";
  2537. $result = Database::query($sql);
  2538. if (Database::num_rows($result) > 0) {
  2539. while ($result_row = Database::fetch_array($result, 'ASSOC')) {
  2540. $result_row['special_course'] = 1;
  2541. $course_list[] = $result_row;
  2542. $codes[] = $result_row['real_id'];
  2543. }
  2544. }
  2545. }
  2546. }
  2547. // get course list not auto-register. Use Distinct to avoid multiple
  2548. // entries when a course is assigned to a HRD (DRH) as watcher
  2549. $sql = "SELECT
  2550. DISTINCT(course.code),
  2551. course.id as real_id,
  2552. course.category_code AS category,
  2553. course.title
  2554. FROM $tbl_course course
  2555. INNER JOIN $tbl_course_user cru
  2556. ON (course.id = cru.c_id)
  2557. INNER JOIN $tableCourseUrl url
  2558. ON (course.id = url.c_id)
  2559. WHERE
  2560. url.access_url_id = $urlId AND
  2561. cru.user_id = '$user_id'
  2562. $withoutSpecialCourses
  2563. $languageCondition
  2564. ORDER BY course.title
  2565. ";
  2566. }
  2567. $result = Database::query($sql);
  2568. if (Database::num_rows($result)) {
  2569. while ($row = Database::fetch_array($result, 'ASSOC')) {
  2570. if (!empty($skipCourseList)) {
  2571. if (in_array($row['real_id'], $skipCourseList)) {
  2572. continue;
  2573. }
  2574. }
  2575. $course_list[] = $row;
  2576. $codes[] = $row['real_id'];
  2577. }
  2578. }
  2579. if ($include_sessions === true) {
  2580. $sql = "SELECT DISTINCT (c.code),
  2581. c.id as real_id,
  2582. c.category_code AS category
  2583. FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)." s,
  2584. $tbl_course c
  2585. WHERE user_id = $user_id AND s.c_id = c.id";
  2586. $r = Database::query($sql);
  2587. while ($row = Database::fetch_array($r, 'ASSOC')) {
  2588. if (!empty($skipCourseList)) {
  2589. if (in_array($row['real_id'], $skipCourseList)) {
  2590. continue;
  2591. }
  2592. }
  2593. if (!in_array($row['real_id'], $codes)) {
  2594. $course_list[] = $row;
  2595. }
  2596. }
  2597. }
  2598. return $course_list;
  2599. }
  2600. /**
  2601. * Get course ID from a given course directory name
  2602. * @param string $path Course directory (without any slash)
  2603. *
  2604. * @return string Course code, or false if not found
  2605. */
  2606. public static function get_course_id_from_path($path)
  2607. {
  2608. $path = Database::escape_string(str_replace('.', '', str_replace('/', '', $path)));
  2609. $res = Database::query("SELECT code FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  2610. WHERE directory LIKE BINARY '$path'");
  2611. if ($res === false) {
  2612. return false;
  2613. }
  2614. if (Database::num_rows($res) != 1) {
  2615. return false;
  2616. }
  2617. $row = Database::fetch_array($res);
  2618. return $row['code'];
  2619. }
  2620. /**
  2621. * Get course code(s) from visual code
  2622. * @deprecated
  2623. * @param string Visual code
  2624. * @return array List of codes for the given visual code
  2625. */
  2626. public static function get_courses_info_from_visual_code($code)
  2627. {
  2628. $result = array();
  2629. $sql_result = Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  2630. WHERE visual_code = '" . Database::escape_string($code)."'");
  2631. while ($virtual_course = Database::fetch_array($sql_result)) {
  2632. $result[] = $virtual_course;
  2633. }
  2634. return $result;
  2635. }
  2636. /**
  2637. * Get emails of tutors to course
  2638. * @param int $courseId
  2639. * @return array List of emails of tutors to course
  2640. *
  2641. * @author Carlos Vargas <carlos.vargas@dokeos.com>, Dokeos Latino
  2642. * */
  2643. public static function get_emails_of_tutors_to_course($courseId)
  2644. {
  2645. $list = array();
  2646. $res = Database::query("SELECT user_id FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  2647. WHERE c_id ='" . intval($courseId)."' AND status=1");
  2648. while ($list_users = Database::fetch_array($res)) {
  2649. $result = Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_USER)."
  2650. WHERE user_id=" . $list_users['user_id']);
  2651. while ($row_user = Database::fetch_array($result)) {
  2652. $name_teacher = api_get_person_name($row_user['firstname'], $row_user['lastname']);
  2653. $list[] = array($row_user['email'] => $name_teacher);
  2654. }
  2655. }
  2656. return $list;
  2657. }
  2658. /**
  2659. * Get coaches emails by session
  2660. * @param int $session_id
  2661. * @param int $courseId
  2662. * @return array array(email => name_tutor) by coach
  2663. * @author Carlos Vargas <carlos.vargas@dokeos.com>
  2664. */
  2665. public static function get_email_of_tutor_to_session($session_id, $courseId)
  2666. {
  2667. $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  2668. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  2669. $coachs_emails = array();
  2670. $courseId = intval($courseId);
  2671. $session_id = intval($session_id);
  2672. $sql = "SELECT user_id
  2673. FROM $tbl_session_course_user
  2674. WHERE
  2675. session_id = '$session_id' AND
  2676. c_id = '$courseId' AND
  2677. status = 2
  2678. ";
  2679. $rs = Database::query($sql);
  2680. if (Database::num_rows($rs) > 0) {
  2681. $user_ids = array();
  2682. while ($row = Database::fetch_array($rs)) {
  2683. $user_ids[] = $row['user_id'];
  2684. }
  2685. $sql = "SELECT firstname, lastname, email FROM $tbl_user
  2686. WHERE user_id IN (".implode(",", $user_ids).")";
  2687. $rs_user = Database::query($sql);
  2688. while ($row_emails = Database::fetch_array($rs_user)) {
  2689. $mail_tutor = array(
  2690. 'email' => $row_emails['email'],
  2691. 'complete_name' => api_get_person_name($row_emails['firstname'], $row_emails['lastname'])
  2692. );
  2693. $coachs_emails[] = $mail_tutor;
  2694. }
  2695. }
  2696. return $coachs_emails;
  2697. }
  2698. /**
  2699. * Creates a new extra field for a given course
  2700. * @param string Field's internal variable name
  2701. * @param int Field's type
  2702. * @param string Field's language var name
  2703. * @param string $default
  2704. * @return boolean new extra field id
  2705. */
  2706. public static function create_course_extra_field($variable, $fieldType, $displayText, $default = '')
  2707. {
  2708. $extraField = new ExtraField('course');
  2709. $params = [
  2710. 'variable' => $variable,
  2711. 'field_type' => $fieldType,
  2712. 'display_text' => $displayText,
  2713. 'default_value' => $default
  2714. ];
  2715. return $extraField->save($params);
  2716. }
  2717. /**
  2718. * Updates course attribute. Note that you need to check that your
  2719. * attribute is valid before you use this function
  2720. *
  2721. * @param int $id Course id
  2722. * @param string $name Attribute name
  2723. * @param string $value Attribute value
  2724. *
  2725. * @return Doctrine\DBAL\Driver\Statement|null True if attribute was successfully updated,
  2726. * false if course was not found or attribute name is invalid
  2727. */
  2728. public static function update_attribute($id, $name, $value)
  2729. {
  2730. $id = (int) $id;
  2731. $table = Database::get_main_table(TABLE_MAIN_COURSE);
  2732. $sql = "UPDATE $table SET $name = '".Database::escape_string($value)."'
  2733. WHERE id = '$id'";
  2734. return Database::query($sql);
  2735. }
  2736. /**
  2737. * Update course attributes. Will only update attributes with a non-empty value.
  2738. * Note that you NEED to check that your attributes are valid before using this function
  2739. *
  2740. * @param int Course id
  2741. * @param array Associative array with field names as keys and field values as values
  2742. * @return Doctrine\DBAL\Driver\Statement|null True if update was successful, false otherwise
  2743. */
  2744. public static function update_attributes($id, $attributes)
  2745. {
  2746. $id = (int) $id;
  2747. $table = Database::get_main_table(TABLE_MAIN_COURSE);
  2748. $sql = "UPDATE $table SET ";
  2749. $i = 0;
  2750. foreach ($attributes as $name => $value) {
  2751. if ($value != '') {
  2752. if ($i > 0) {
  2753. $sql .= ", ";
  2754. }
  2755. $sql .= " $name = '".Database::escape_string($value)."'";
  2756. $i++;
  2757. }
  2758. }
  2759. $sql .= " WHERE id = '$id'";
  2760. return Database::query($sql);
  2761. }
  2762. /**
  2763. * Update an extra field value for a given course
  2764. * @param integer Course ID
  2765. * @param string Field variable name
  2766. * @param string Field value
  2767. * @return boolean|null true if field updated, false otherwise
  2768. */
  2769. public static function update_course_extra_field_value($course_code, $variable, $value = '')
  2770. {
  2771. $courseInfo = api_get_course_info($course_code);
  2772. $courseId = $courseInfo['real_id'];
  2773. $extraFieldValues = new ExtraFieldValue('course');
  2774. $params = [
  2775. 'item_id' => $courseId,
  2776. 'variable' => $variable,
  2777. 'value' => $value
  2778. ];
  2779. return $extraFieldValues->save($params);
  2780. }
  2781. /**
  2782. * @param int $session_id
  2783. * @return mixed
  2784. */
  2785. public static function get_session_category_id_by_session_id($session_id)
  2786. {
  2787. if (empty($session_id)) {
  2788. return [];
  2789. }
  2790. $sql = 'SELECT sc.id session_category
  2791. FROM ' . Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY).' sc
  2792. INNER JOIN ' . Database::get_main_table(TABLE_MAIN_SESSION).' s
  2793. ON sc.id = s.session_category_id
  2794. WHERE s.id="' . intval($session_id).'"';
  2795. return Database::result(
  2796. Database::query($sql),
  2797. 0,
  2798. 'session_category'
  2799. );
  2800. }
  2801. /**
  2802. * Gets the value of a course extra field. Returns null if it was not found
  2803. *
  2804. * @param string $variable Name of the extra field
  2805. * @param string $code Course code
  2806. *
  2807. * @return string Value
  2808. */
  2809. public static function get_course_extra_field_value($variable, $code)
  2810. {
  2811. $courseInfo = api_get_course_info($code);
  2812. $courseId = $courseInfo['real_id'];
  2813. $extraFieldValues = new ExtraFieldValue('course');
  2814. $result = $extraFieldValues->get_values_by_handler_and_field_variable($courseId, $variable);
  2815. if (!empty($result['value'])) {
  2816. return $result['value'];
  2817. }
  2818. return null;
  2819. }
  2820. /**
  2821. * Lists details of the course description
  2822. * @param array The course description
  2823. * @param string The encoding
  2824. * @param bool If true is displayed if false is hidden
  2825. * @return string The course description in html
  2826. */
  2827. public static function get_details_course_description_html(
  2828. $descriptions,
  2829. $charset,
  2830. $action_show = true
  2831. ) {
  2832. $data = null;
  2833. if (isset($descriptions) && count($descriptions) > 0) {
  2834. foreach ($descriptions as $description) {
  2835. $data .= '<div class="sectiontitle">';
  2836. if (api_is_allowed_to_edit() && $action_show) {
  2837. //delete
  2838. $data .= '<a href="'.api_get_self().'?'.api_get_cidreq().'&action=delete&description_id='.$description->id.'" onclick="javascript:if(!confirm(\''.addslashes(api_htmlentities(get_lang('ConfirmYourChoice'),
  2839. ENT_QUOTES, $charset)).'\')) return false;">';
  2840. $data .= Display::return_icon('delete.gif', get_lang('Delete'),
  2841. array('style' => 'vertical-align:middle;float:right;'));
  2842. $data .= '</a> ';
  2843. //edit
  2844. $data .= '<a href="'.api_get_self().'?'.api_get_cidreq().'&description_id='.$description->id.'">';
  2845. $data .= Display::return_icon('edit.png', get_lang('Edit'),
  2846. array('style' => 'vertical-align:middle;float:right; padding-right:4px;'), ICON_SIZE_SMALL);
  2847. $data .= '</a> ';
  2848. }
  2849. $data .= $description->title;
  2850. $data .= '</div>';
  2851. $data .= '<div class="sectioncomment">';
  2852. $data .= $description->content;
  2853. $data .= '</div>';
  2854. }
  2855. } else {
  2856. $data .= '<em>'.get_lang('ThisCourseDescriptionIsEmpty').'</em>';
  2857. }
  2858. return $data;
  2859. }
  2860. /**
  2861. * Returns the details of a course category
  2862. *
  2863. * @param string $code Category code
  2864. * @return array Course category
  2865. */
  2866. public static function get_course_category($code)
  2867. {
  2868. $table_categories = Database::get_main_table(TABLE_MAIN_CATEGORY);
  2869. $code = Database::escape_string($code);
  2870. $sql = "SELECT * FROM $table_categories WHERE code = '$code'";
  2871. return Database::fetch_array(Database::query($sql));
  2872. }
  2873. /**
  2874. * Returns the details of a course category
  2875. *
  2876. * @param string Category code
  2877. * @return array Course category
  2878. */
  2879. public static function getCategoriesList()
  2880. {
  2881. $table_categories = Database::get_main_table(TABLE_MAIN_CATEGORY);
  2882. $sql = "SELECT * FROM $table_categories";
  2883. $result = Database::query($sql);
  2884. $category = array();
  2885. while ($row = Database::fetch_array($result, 'ASSOC')) {
  2886. $category[$row['code']] = $row['name'];
  2887. }
  2888. return $category;
  2889. }
  2890. /**
  2891. * Subscribes courses to human resource manager (Dashboard feature)
  2892. * @param int $hr_manager_id Human Resource Manager id
  2893. * @param array $courses_list Courses code
  2894. * @return int
  2895. **/
  2896. public static function subscribeCoursesToDrhManager($hr_manager_id, $courses_list)
  2897. {
  2898. $tbl_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  2899. $tbl_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  2900. $hr_manager_id = intval($hr_manager_id);
  2901. $affected_rows = 0;
  2902. //Deleting assigned courses to hrm_id
  2903. if (api_is_multiple_url_enabled()) {
  2904. $sql = "SELECT s.c_id FROM $tbl_course_rel_user s
  2905. INNER JOIN $tbl_course_rel_access_url a
  2906. ON (a.c_id = s.c_id)
  2907. WHERE
  2908. user_id = $hr_manager_id AND
  2909. relation_type=".COURSE_RELATION_TYPE_RRHH." AND
  2910. access_url_id = " . api_get_current_access_url_id()."";
  2911. } else {
  2912. $sql = "SELECT c_id FROM $tbl_course_rel_user
  2913. WHERE user_id = $hr_manager_id AND relation_type=".COURSE_RELATION_TYPE_RRHH." ";
  2914. }
  2915. $result = Database::query($sql);
  2916. if (Database::num_rows($result) > 0) {
  2917. while ($row = Database::fetch_array($result)) {
  2918. $sql = "DELETE FROM $tbl_course_rel_user
  2919. WHERE
  2920. c_id = '{$row['c_id']}' AND
  2921. user_id = $hr_manager_id AND
  2922. relation_type=".COURSE_RELATION_TYPE_RRHH." ";
  2923. Database::query($sql);
  2924. }
  2925. }
  2926. // inserting new courses list
  2927. if (is_array($courses_list)) {
  2928. foreach ($courses_list as $course_code) {
  2929. $courseInfo = api_get_course_info($course_code);
  2930. $courseId = $courseInfo['real_id'];
  2931. $sql = "INSERT IGNORE INTO $tbl_course_rel_user(c_id, user_id, status, relation_type)
  2932. VALUES($courseId, $hr_manager_id, '".DRH."', '".COURSE_RELATION_TYPE_RRHH."')";
  2933. $result = Database::query($sql);
  2934. if (Database::affected_rows($result)) {
  2935. $affected_rows++;
  2936. }
  2937. }
  2938. }
  2939. return $affected_rows;
  2940. }
  2941. /**
  2942. * get courses followed by human resources manager
  2943. * @param int $user_id
  2944. * @param int $status
  2945. * @param int $from
  2946. * @param int $limit
  2947. * @param string $column
  2948. * @param string $direction
  2949. * @param bool $getCount
  2950. *
  2951. * @return array courses
  2952. */
  2953. public static function get_courses_followed_by_drh(
  2954. $user_id,
  2955. $status = DRH,
  2956. $from = null,
  2957. $limit = null,
  2958. $column = null,
  2959. $direction = null,
  2960. $getCount = false
  2961. ) {
  2962. return self::getCoursesFollowedByUser(
  2963. $user_id,
  2964. $status,
  2965. $from,
  2966. $limit,
  2967. $column,
  2968. $direction,
  2969. $getCount
  2970. );
  2971. }
  2972. /**
  2973. * get courses followed by user
  2974. * @param int $user_id
  2975. * @param int $status
  2976. * @param int $from
  2977. * @param int $limit
  2978. * @param string $column
  2979. * @param string $direction
  2980. * @param boolean $getCount
  2981. * @param string $keyword
  2982. * @param int $sessionId
  2983. * @param boolean $showAllAssignedCourses
  2984. * @return array courses
  2985. */
  2986. public static function getCoursesFollowedByUser(
  2987. $user_id,
  2988. $status = null,
  2989. $from = null,
  2990. $limit = null,
  2991. $column = null,
  2992. $direction = null,
  2993. $getCount = false,
  2994. $keyword = null,
  2995. $sessionId = 0,
  2996. $showAllAssignedCourses = false
  2997. ) {
  2998. // Database Table Definitions
  2999. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  3000. $tbl_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  3001. $tbl_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  3002. $sessionId = (int) $sessionId;
  3003. $user_id = (int) $user_id;
  3004. $select = "SELECT DISTINCT *, c.id as real_id ";
  3005. if ($getCount) {
  3006. $select = "SELECT COUNT(DISTINCT c.id) as count";
  3007. }
  3008. $whereConditions = '';
  3009. switch ($status) {
  3010. case COURSEMANAGER:
  3011. $whereConditions .= " AND cru.user_id = '$user_id'";
  3012. if (!$showAllAssignedCourses) {
  3013. $whereConditions .= " AND cru.status = ".COURSEMANAGER;
  3014. } else {
  3015. $whereConditions .= " AND relation_type = ".COURSE_RELATION_TYPE_COURSE_MANAGER;
  3016. }
  3017. break;
  3018. case DRH:
  3019. $whereConditions .= " AND
  3020. cru.user_id = '$user_id' AND
  3021. cru.status = ".DRH." AND
  3022. relation_type = '" . COURSE_RELATION_TYPE_RRHH."'
  3023. ";
  3024. break;
  3025. }
  3026. $keywordCondition = null;
  3027. if (!empty($keyword)) {
  3028. $keyword = Database::escape_string($keyword);
  3029. $keywordCondition = " AND (c.code LIKE '%$keyword%' OR c.title LIKE '%$keyword%' ) ";
  3030. }
  3031. $orderBy = null;
  3032. $extraInnerJoin = null;
  3033. if (!empty($sessionId)) {
  3034. if ($status == COURSEMANAGER) {
  3035. // Teacher of course or teacher inside session
  3036. $whereConditions = " AND (cru.status = ".COURSEMANAGER." OR srcru.status = 2) ";
  3037. }
  3038. $courseList = SessionManager::get_course_list_by_session_id(
  3039. $sessionId
  3040. );
  3041. if (!empty($courseList)) {
  3042. $courseListToString = implode("','", array_keys($courseList));
  3043. $whereConditions .= " AND c.id IN ('".$courseListToString."')";
  3044. }
  3045. $tableSessionRelCourse = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  3046. $tableSessionRelCourseRelUser = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  3047. $orderBy = ' ORDER BY position';
  3048. $extraInnerJoin = " INNER JOIN $tableSessionRelCourse src
  3049. ON (c.id = src.c_id AND src.session_id = $sessionId)
  3050. INNER JOIN $tableSessionRelCourseRelUser srcru
  3051. ON (src.session_id = srcru.session_id AND srcru.c_id = src.c_id)
  3052. ";
  3053. }
  3054. $whereConditions .= $keywordCondition;
  3055. $sql = "$select
  3056. FROM $tbl_course c
  3057. INNER JOIN $tbl_course_rel_user cru
  3058. ON (cru.c_id = c.id)
  3059. INNER JOIN $tbl_course_rel_access_url a
  3060. ON (a.c_id = c.id)
  3061. $extraInnerJoin
  3062. WHERE
  3063. access_url_id = ".api_get_current_access_url_id()."
  3064. $whereConditions
  3065. $orderBy
  3066. ";
  3067. if (isset($from) && isset($limit)) {
  3068. $from = intval($from);
  3069. $limit = intval($limit);
  3070. $sql .= " LIMIT $from, $limit";
  3071. }
  3072. $result = Database::query($sql);
  3073. if ($getCount) {
  3074. $row = Database::fetch_array($result);
  3075. return $row['count'];
  3076. }
  3077. $courses = array();
  3078. if (Database::num_rows($result) > 0) {
  3079. while ($row = Database::fetch_array($result)) {
  3080. $courses[$row['code']] = $row;
  3081. }
  3082. }
  3083. return $courses;
  3084. }
  3085. /**
  3086. * check if a course is special (autoregister)
  3087. * @param int $courseId
  3088. * @return bool
  3089. */
  3090. public static function isSpecialCourse($courseId)
  3091. {
  3092. $extraFieldValue = new ExtraFieldValue('course');
  3093. $result = $extraFieldValue->get_values_by_handler_and_field_variable(
  3094. $courseId,
  3095. 'special_course'
  3096. );
  3097. if (!empty($result)) {
  3098. if ($result['value'] == 1) {
  3099. return true;
  3100. }
  3101. }
  3102. return false;
  3103. }
  3104. /**
  3105. * Update course picture
  3106. * @param array $courseInfo
  3107. * @param string File name
  3108. * @param string The full system name of the image
  3109. * from which course picture will be created.
  3110. * @param string $cropParameters Optional string that contents "x,y,width,height" of a cropped image format
  3111. * @return bool Returns the resulting. In case of internal error or negative validation returns FALSE.
  3112. */
  3113. public static function update_course_picture(
  3114. $courseInfo,
  3115. $filename,
  3116. $source_file = null,
  3117. $cropParameters = null
  3118. ) {
  3119. if (empty($courseInfo)) {
  3120. return false;
  3121. }
  3122. // course path
  3123. $store_path = api_get_path(SYS_COURSE_PATH).$courseInfo['path'];
  3124. // image name for courses
  3125. $course_image = $store_path.'/course-pic.png';
  3126. $course_medium_image = $store_path.'/course-pic85x85.png';
  3127. if (file_exists($course_image)) {
  3128. unlink($course_image);
  3129. }
  3130. if (file_exists($course_medium_image)) {
  3131. unlink($course_medium_image);
  3132. }
  3133. //Crop the image to adjust 4:3 ratio
  3134. $image = new Image($source_file);
  3135. $image->crop($cropParameters);
  3136. //Resize the images in two formats
  3137. $medium = new Image($source_file);
  3138. $medium->resize(85);
  3139. $medium->send_image($course_medium_image, -1, 'png');
  3140. $normal = new Image($source_file);
  3141. $normal->resize(400);
  3142. $normal->send_image($course_image, -1, 'png');
  3143. $result = $medium && $normal;
  3144. return $result ? $result : false;
  3145. }
  3146. /**
  3147. * Deletes the course picture
  3148. * @param string $courseCode
  3149. */
  3150. public static function deleteCoursePicture($courseCode)
  3151. {
  3152. $course_info = api_get_course_info($courseCode);
  3153. // course path
  3154. $storePath = api_get_path(SYS_COURSE_PATH).$course_info['path'];
  3155. // image name for courses
  3156. $courseImage = $storePath.'/course-pic.png';
  3157. $courseMediumImage = $storePath.'/course-pic85x85.png';
  3158. $courseSmallImage = $storePath.'/course-pic32.png';
  3159. if (file_exists($courseImage)) {
  3160. unlink($courseImage);
  3161. }
  3162. if (file_exists($courseMediumImage)) {
  3163. unlink($courseMediumImage);
  3164. }
  3165. if (file_exists($courseSmallImage)) {
  3166. unlink($courseSmallImage);
  3167. }
  3168. }
  3169. /**
  3170. * Builds the course block in user_portal.php
  3171. * @todo use Twig
  3172. *
  3173. * @param array $params
  3174. * @return string
  3175. */
  3176. public static function course_item_html_no_icon($params)
  3177. {
  3178. $html = '<div class="course_item">';
  3179. $html .= '<div class="row">';
  3180. $html .= '<div class="col-md-7">';
  3181. $notifications = isset($params['notifications']) ? $params['notifications'] : null;
  3182. $html .= '<h3>'.$params['title'].$notifications.'</h3> ';
  3183. if (isset($params['description'])) {
  3184. $html .= '<p>'.$params['description'].'</p>';
  3185. }
  3186. if (!empty($params['subtitle'])) {
  3187. $html .= '<small>'.$params['subtitle'].'</small>';
  3188. }
  3189. if (!empty($params['teachers'])) {
  3190. $html .= '<h5 class="teacher">'.
  3191. Display::return_icon(
  3192. 'teacher.png',
  3193. get_lang('Teacher'),
  3194. array(),
  3195. ICON_SIZE_TINY
  3196. ).
  3197. $params['teachers'].'</h5>';
  3198. }
  3199. if (!empty($params['coaches'])) {
  3200. $html .= '<h5 class="teacher">'.
  3201. Display::return_icon(
  3202. 'teacher.png',
  3203. get_lang('Coach'),
  3204. array(),
  3205. ICON_SIZE_TINY
  3206. ).
  3207. $params['coaches'].'</h5>';
  3208. }
  3209. $html .= '</div>';
  3210. $params['right_actions'] = isset($params['right_actions']) ? $params['right_actions'] : null;
  3211. $html .= '<div class="pull-right course-box-actions">'.$params['right_actions'].'</div>';
  3212. $html .= '</div>';
  3213. $html .= '</div>';
  3214. return $html;
  3215. }
  3216. /**
  3217. * @param $params
  3218. * @param bool|false $is_sub_content
  3219. * @return string
  3220. */
  3221. public static function session_items_html($params, $is_sub_content = false)
  3222. {
  3223. $html = '';
  3224. $html .= '<div class="row">';
  3225. $html .= '<div class="col-md-2">';
  3226. if (!empty($params['link'])) {
  3227. $html .= '<a class="thumbnail" href="'.$params['link'].'">';
  3228. $html .= $params['icon'];
  3229. $html .= '</a>';
  3230. } else {
  3231. $html .= $params['icon'];
  3232. }
  3233. $html .= '</div>';
  3234. $html .= '<div class="col-md-10">';
  3235. $html .= $params['title'];
  3236. $html .= $params['coaches'];
  3237. $html .= '</div>';
  3238. $html .= '</div>';
  3239. return $html;
  3240. }
  3241. /**
  3242. * Display special courses (and only these) as several HTML divs of class userportal-course-item
  3243. *
  3244. * Special courses are courses that stick on top of the list and are "auto-registerable"
  3245. * in the sense that any user clicking them is registered as a student
  3246. * @param int $user_id User id
  3247. * @param bool $load_dirs Whether to show the document quick-loader or not
  3248. * @param bool $useUserLanguageFilterIfAvailable
  3249. * @return array
  3250. */
  3251. public static function returnSpecialCourses(
  3252. $user_id,
  3253. $load_dirs = false,
  3254. $useUserLanguageFilterIfAvailable = true
  3255. ) {
  3256. $user_id = intval($user_id);
  3257. $table = Database::get_main_table(TABLE_MAIN_COURSE);
  3258. $specialCourseList = self::get_special_course_list();
  3259. if (empty($specialCourseList)) {
  3260. return [];
  3261. }
  3262. // Filter by language
  3263. $languageCondition = '';
  3264. $onlyInUserLanguage = api_get_configuration_value('my_courses_show_courses_in_user_language_only');
  3265. if ($useUserLanguageFilterIfAvailable && $onlyInUserLanguage) {
  3266. $userInfo = api_get_user_info(api_get_user_id());
  3267. if (!empty($userInfo['language'])) {
  3268. $languageCondition = " AND course_language = '".$userInfo['language']."' ";
  3269. }
  3270. }
  3271. $sql = "SELECT
  3272. id,
  3273. code,
  3274. subscribe subscr,
  3275. unsubscribe unsubscr
  3276. FROM $table
  3277. WHERE
  3278. id IN ('".implode("','", $specialCourseList)."')
  3279. $languageCondition
  3280. GROUP BY code";
  3281. $rs_special_course = Database::query($sql);
  3282. $number_of_courses = Database::num_rows($rs_special_course);
  3283. $showCustomIcon = api_get_setting('course_images_in_courses_list');
  3284. $courseList = [];
  3285. if ($number_of_courses > 0) {
  3286. while ($course = Database::fetch_array($rs_special_course)) {
  3287. $course_info = api_get_course_info($course['code']);
  3288. $courseId = $course_info['real_id'];
  3289. if ($course_info['visibility'] == COURSE_VISIBILITY_HIDDEN) {
  3290. continue;
  3291. }
  3292. $params = [];
  3293. //Param (course_code) needed to get the student info in page "My courses"
  3294. $params['course_code'] = $course['code'];
  3295. $params['code'] = $course['code'];
  3296. // Get notifications.
  3297. $course_info['id_session'] = null;
  3298. $courseUserInfo = self::getUserCourseInfo($user_id, $courseId);
  3299. if (empty($courseUserInfo)) {
  3300. $course_info['status'] = STUDENT;
  3301. } else {
  3302. $course_info['status'] = $courseUserInfo['status'];
  3303. }
  3304. $show_notification = !api_get_configuration_value('hide_course_notification')
  3305. ? Display::show_notification($course_info)
  3306. : '';
  3307. $params['edit_actions'] = '';
  3308. $params['document'] = '';
  3309. if (api_is_platform_admin()) {
  3310. $params['edit_actions'] .= api_get_path(WEB_CODE_PATH).'course_info/infocours.php?cidReq='.$course['code'];
  3311. if ($load_dirs) {
  3312. $params['document'] = '<a id="document_preview_'.$courseId.'_0" class="document_preview btn btn-default btn-sm" href="javascript:void(0);">'
  3313. . Display::returnFontAwesomeIcon('folder-open').'</a>';
  3314. $params['document'] .= Display::div('', ['id' => 'document_result_'.$courseId.'_0', 'class' => 'document_preview_container']);
  3315. }
  3316. } else {
  3317. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED && $load_dirs) {
  3318. $params['document'] = '<a id="document_preview_'.$courseId.'_0" class="document_preview btn btn-default btn-sm" href="javascript:void(0);">'
  3319. . Display::returnFontAwesomeIcon('folder-open').'</a>';
  3320. $params['document'] .= Display::div('', ['id' => 'document_result_'.$courseId.'_0', 'class' => 'document_preview_container']);
  3321. }
  3322. }
  3323. $params['visibility'] = $course_info['visibility'];
  3324. $params['status'] = $course_info['status'];
  3325. $params['category'] = $course_info['categoryName'];
  3326. $params['category_code'] = $course_info['categoryCode'];
  3327. $params['icon'] = Display::return_icon(
  3328. 'drawing-pin.png',
  3329. null,
  3330. null,
  3331. ICON_SIZE_LARGE,
  3332. null
  3333. );
  3334. if (api_get_setting('display_coursecode_in_courselist') == 'true') {
  3335. $params['code_course'] = '('.$course_info['visual_code'].')';
  3336. }
  3337. $params['title'] = $course_info['title'];
  3338. $params['title_cut'] = $course_info['title'];
  3339. $params['link'] = $course_info['course_public_url'].'?id_session=0&autoreg=1';
  3340. if (api_get_setting('display_teacher_in_courselist') === 'true') {
  3341. $params['teachers'] = self::getTeachersFromCourse(
  3342. $courseId,
  3343. true
  3344. );
  3345. }
  3346. if ($showCustomIcon === 'true') {
  3347. $params['thumbnails'] = $course_info['course_image'];
  3348. $params['image'] = $course_info['course_image_large'];
  3349. }
  3350. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED) {
  3351. $params['notifications'] = $show_notification;
  3352. }
  3353. $params['is_special_course'] = true;
  3354. $courseList[] = $params;
  3355. }
  3356. }
  3357. return $courseList;
  3358. }
  3359. /**
  3360. * Display courses (without special courses) as several HTML divs
  3361. * of course categories, as class userportal-catalog-item.
  3362. * @uses displayCoursesInCategory() to display the courses themselves
  3363. * @param int $user_id
  3364. * @param bool $load_dirs Whether to show the document quick-loader or not
  3365. * @param bool $useUserLanguageFilterIfAvailable
  3366. *
  3367. * @return array
  3368. */
  3369. public static function returnCourses(
  3370. $user_id,
  3371. $load_dirs = false,
  3372. $useUserLanguageFilterIfAvailable = true
  3373. ) {
  3374. $user_id = intval($user_id);
  3375. if (empty($user_id)) {
  3376. $user_id = api_get_user_id();
  3377. }
  3378. // Step 1: We get all the categories of the user
  3379. $table = Database::get_main_table(TABLE_USER_COURSE_CATEGORY);
  3380. $sql = "SELECT id, title FROM $table
  3381. WHERE user_id = '".$user_id."'
  3382. ORDER BY sort ASC";
  3383. $result = Database::query($sql);
  3384. $listItems = [
  3385. 'in_category' => [],
  3386. 'not_category' => []
  3387. ];
  3388. while ($row = Database::fetch_array($result)) {
  3389. // We simply display the title of the category.
  3390. $courseInCategory = self::returnCoursesCategories(
  3391. $row['id'],
  3392. $load_dirs,
  3393. $user_id,
  3394. $useUserLanguageFilterIfAvailable
  3395. );
  3396. $params = [
  3397. 'id_category' => $row ['id'],
  3398. 'title_category' => $row['title'],
  3399. 'courses' => $courseInCategory
  3400. ];
  3401. $listItems['in_category'][] = $params;
  3402. }
  3403. // Step 2: We display the course without a user category.
  3404. $coursesNotCategory = self::returnCoursesCategories(
  3405. 0,
  3406. $load_dirs,
  3407. $user_id,
  3408. $useUserLanguageFilterIfAvailable
  3409. );
  3410. if ($coursesNotCategory) {
  3411. $listItems['not_category'] = $coursesNotCategory;
  3412. }
  3413. return $listItems;
  3414. }
  3415. /**
  3416. * Display courses inside a category (without special courses) as HTML dics of
  3417. * class userportal-course-item.
  3418. * @param int $user_category_id User category id
  3419. * @param bool $load_dirs Whether to show the document quick-loader or not
  3420. * @param int $user_id
  3421. * @param bool $useUserLanguageFilterIfAvailable
  3422. * @return array
  3423. */
  3424. public static function returnCoursesCategories(
  3425. $user_category_id,
  3426. $load_dirs = false,
  3427. $user_id = 0,
  3428. $useUserLanguageFilterIfAvailable = true
  3429. ) {
  3430. $user_id = $user_id ?: api_get_user_id();
  3431. $user_category_id = (int) $user_category_id;
  3432. // Table definitions
  3433. $TABLECOURS = Database::get_main_table(TABLE_MAIN_COURSE);
  3434. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  3435. $TABLE_ACCESS_URL_REL_COURSE = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  3436. $current_url_id = api_get_current_access_url_id();
  3437. // Get course list auto-register
  3438. $special_course_list = self::get_special_course_list();
  3439. $without_special_courses = '';
  3440. if (!empty($special_course_list)) {
  3441. $without_special_courses = ' AND course.id NOT IN ("'.implode('","', $special_course_list).'")';
  3442. }
  3443. $userCategoryCondition = " (course_rel_user.user_course_cat = $user_category_id) ";
  3444. if (empty($user_category_id)) {
  3445. $userCategoryCondition = ' (course_rel_user.user_course_cat = 0 OR course_rel_user.user_course_cat IS NULL) ';
  3446. }
  3447. $languageCondition = '';
  3448. $onlyInUserLanguage = api_get_configuration_value('my_courses_show_courses_in_user_language_only');
  3449. if ($useUserLanguageFilterIfAvailable && $onlyInUserLanguage) {
  3450. $userInfo = api_get_user_info(api_get_user_id());
  3451. if (!empty($userInfo['language'])) {
  3452. $languageCondition = " AND course.course_language = '".$userInfo['language']."' ";
  3453. }
  3454. }
  3455. $sql = "SELECT DISTINCT
  3456. course.id,
  3457. course_rel_user.status status,
  3458. course.code as course_code
  3459. FROM $TABLECOURS course
  3460. INNER JOIN $TABLECOURSUSER course_rel_user
  3461. ON (course.id = course_rel_user.c_id)
  3462. INNER JOIN $TABLE_ACCESS_URL_REL_COURSE url
  3463. ON (url.c_id = course.id)
  3464. WHERE
  3465. course_rel_user.user_id = '".$user_id."' AND
  3466. $userCategoryCondition
  3467. $without_special_courses
  3468. $languageCondition
  3469. ";
  3470. // If multiple URL access mode is enabled, only fetch courses
  3471. // corresponding to the current URL.
  3472. if (api_get_multiple_access_url() && $current_url_id != -1) {
  3473. $sql .= " AND access_url_id='".$current_url_id."'";
  3474. }
  3475. // Use user's classification for courses (if any).
  3476. $sql .= " ORDER BY course_rel_user.user_course_cat, course_rel_user.sort ASC";
  3477. $result = Database::query($sql);
  3478. $showCustomIcon = api_get_setting('course_images_in_courses_list');
  3479. // Browse through all courses.
  3480. $courseAdded = [];
  3481. $courseList = [];
  3482. while ($row = Database::fetch_array($result)) {
  3483. $course_info = api_get_course_info_by_id($row['id']);
  3484. if (isset($course_info['visibility']) &&
  3485. $course_info['visibility'] == COURSE_VISIBILITY_HIDDEN
  3486. ) {
  3487. continue;
  3488. }
  3489. // Skip if already in list
  3490. if (in_array($course_info['real_id'], $courseAdded)) {
  3491. continue;
  3492. }
  3493. $course_info['id_session'] = null;
  3494. $course_info['status'] = $row['status'];
  3495. // For each course, get if there is any notification icon to show
  3496. // (something that would have changed since the user's last visit).
  3497. $showNotification = !api_get_configuration_value('hide_course_notification')
  3498. ? Display::show_notification($course_info)
  3499. : '';
  3500. $iconName = basename($course_info['course_image']);
  3501. $params = array();
  3502. //Param (course_code) needed to get the student process
  3503. $params['course_code'] = $row['course_code'];
  3504. $params['code'] = $row['course_code'];
  3505. if ($showCustomIcon === 'true' && $iconName != 'course.png') {
  3506. $params['thumbnails'] = $course_info['course_image'];
  3507. $params['image'] = $course_info['course_image_large'];
  3508. }
  3509. $thumbnails = null;
  3510. $image = null;
  3511. if ($showCustomIcon === 'true' && $iconName != 'course.png') {
  3512. $thumbnails = $course_info['course_image'];
  3513. $image = $course_info['course_image_large'];
  3514. } else {
  3515. $image = Display::return_icon(
  3516. 'session_default.png',
  3517. null,
  3518. null,
  3519. null,
  3520. null,
  3521. true
  3522. );
  3523. }
  3524. $params['course_id'] = $course_info['real_id'];
  3525. $params['edit_actions'] = '';
  3526. $params['document'] = '';
  3527. if (api_is_platform_admin()) {
  3528. $params['edit_actions'] .= api_get_path(WEB_CODE_PATH).'course_info/infocours.php?cidReq='.$course_info['code'];
  3529. if ($load_dirs) {
  3530. $params['document'] = '<a id="document_preview_'.$course_info['real_id'].'_0" class="document_preview btn btn-default btn-sm" href="javascript:void(0);">'
  3531. . Display::returnFontAwesomeIcon('folder-open').'</a>';
  3532. $params['document'] .= Display::div('', array('id' => 'document_result_'.$course_info['real_id'].'_0', 'class' => 'document_preview_container'));
  3533. }
  3534. }
  3535. if ($load_dirs) {
  3536. $params['document'] = '<a id="document_preview_'.$course_info['real_id'].'_0" class="document_preview btn btn-default btn-sm" href="javascript:void(0);">'
  3537. . Display::returnFontAwesomeIcon('folder-open').'</a>';
  3538. $params['document'] .= Display::div('', array('id' => 'document_result_'.$course_info['real_id'].'_0', 'class' => 'document_preview_container'));
  3539. }
  3540. $courseUrl = api_get_path(WEB_COURSE_PATH).$course_info['path'].'/index.php?id_session=0';
  3541. $teachers = [];
  3542. if (api_get_setting('display_teacher_in_courselist') === 'true') {
  3543. $teachers = self::getTeachersFromCourse(
  3544. $course_info['real_id'],
  3545. true
  3546. );
  3547. }
  3548. $params['status'] = $row['status'];
  3549. if (api_get_setting('display_coursecode_in_courselist') == 'true') {
  3550. $params['code_course'] = '('.$course_info['visual_code'].') ';
  3551. }
  3552. $params['current_user_is_teacher'] = false;
  3553. /** @var array $teacher */
  3554. foreach ($teachers as $teacher) {
  3555. if ($teacher['id'] != $user_id) {
  3556. continue;
  3557. }
  3558. $params['current_user_is_teacher'] = true;
  3559. }
  3560. $params['visibility'] = $course_info['visibility'];
  3561. $params['link'] = $courseUrl;
  3562. $params['thumbnails'] = $thumbnails;
  3563. $params['image'] = $image;
  3564. $params['title'] = $course_info['title'];
  3565. $params['title_cut'] = $params['title'];
  3566. $params['category'] = $course_info['categoryName'];
  3567. $params['category_code'] = $course_info['categoryCode'];
  3568. $params['teachers'] = $teachers;
  3569. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED) {
  3570. $params['notifications'] = $showNotification;
  3571. }
  3572. $courseAdded[] = $course_info['real_id'];
  3573. $courseList[] = $params;
  3574. }
  3575. return $courseList;
  3576. }
  3577. /**
  3578. * Display courses inside a category (without special courses) as HTML dics of
  3579. * class userportal-course-item.
  3580. * @deprecated use self::returnCoursesCategories(0);
  3581. * @param int User category id
  3582. * @param bool Whether to show the document quick-loader or not
  3583. * @return array
  3584. */
  3585. public static function returnCoursesWithoutCategories($user_category_id, $load_dirs = false)
  3586. {
  3587. $user_id = api_get_user_id();
  3588. // Table definitions
  3589. $TABLECOURS = Database::get_main_table(TABLE_MAIN_COURSE);
  3590. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  3591. $TABLE_ACCESS_URL_REL_COURSE = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  3592. $current_url_id = api_get_current_access_url_id();
  3593. $courseList = [];
  3594. // Get course list auto-register
  3595. $special_course_list = self::get_special_course_list();
  3596. $without_special_courses = '';
  3597. if (!empty($special_course_list)) {
  3598. $without_special_courses = ' AND course.id NOT IN ("'.implode('","', $special_course_list).'")';
  3599. }
  3600. $sql = "SELECT
  3601. course.id,
  3602. course.title,
  3603. course.code,
  3604. course.subscribe subscr,
  3605. course.unsubscribe unsubscr,
  3606. course_rel_user.status status,
  3607. course_rel_user.sort sort,
  3608. course_rel_user.user_course_cat user_course_cat
  3609. FROM $TABLECOURS course
  3610. INNER JOIN $TABLECOURSUSER course_rel_user
  3611. ON (course.id = course_rel_user.c_id)
  3612. INNER JOIN $TABLE_ACCESS_URL_REL_COURSE url
  3613. ON (url.c_id = course.id)
  3614. WHERE
  3615. course_rel_user.user_id = '".$user_id."' AND
  3616. course_rel_user.user_course_cat = '" . $user_category_id."'
  3617. $without_special_courses ";
  3618. // If multiple URL access mode is enabled, only fetch courses
  3619. // corresponding to the current URL.
  3620. if (api_get_multiple_access_url() && $current_url_id != -1) {
  3621. $sql .= " AND url.c_id = course.id AND access_url_id='".$current_url_id."'";
  3622. }
  3623. // Use user's classification for courses (if any).
  3624. $sql .= " ORDER BY course_rel_user.user_course_cat, course_rel_user.sort ASC";
  3625. $result = Database::query($sql);
  3626. $showCustomIcon = api_get_setting('course_images_in_courses_list');
  3627. // Browse through all courses.
  3628. while ($row = Database::fetch_array($result)) {
  3629. $course_info = api_get_course_info($row['code']);
  3630. if (isset($course_info['visibility']) &&
  3631. $course_info['visibility'] == COURSE_VISIBILITY_HIDDEN
  3632. ) {
  3633. continue;
  3634. }
  3635. $course_info['id_session'] = null;
  3636. $course_info['status'] = $row['status'];
  3637. // For each course, get if there is any notification icon to show
  3638. // (something that would have changed since the user's last visit).
  3639. $showNotification = !api_get_configuration_value('hide_course_notification')
  3640. ? Display::show_notification($course_info)
  3641. : '';
  3642. $thumbnails = null;
  3643. $image = null;
  3644. $iconName = basename($course_info['course_image']);
  3645. if ($showCustomIcon === 'true' && $iconName != 'course.png') {
  3646. $thumbnails = $course_info['course_image'];
  3647. $image = $course_info['course_image_large'];
  3648. } else {
  3649. $image = Display::return_icon(
  3650. 'session_default.png',
  3651. null,
  3652. null,
  3653. null,
  3654. null,
  3655. true
  3656. );
  3657. }
  3658. $params = array();
  3659. //Param (course_code) needed to get the student process
  3660. $params['course_code'] = $row['code'];
  3661. $params['edit_actions'] = '';
  3662. $params['document'] = '';
  3663. if (api_is_platform_admin()) {
  3664. $params['edit_actions'] .= api_get_path(WEB_CODE_PATH).'course_info/infocours.php?cidReq='.$course_info['code'];
  3665. if ($load_dirs) {
  3666. $params['document'] = '<a id="document_preview_'.$course_info['real_id'].'_0" class="document_preview btn btn-default btn-sm" href="javascript:void(0);">'
  3667. . Display::returnFontAwesomeIcon('folder-open').'</a>';
  3668. $params['document'] .= Display::div('', array('id' => 'document_result_'.$course_info['real_id'].'_0', 'class' => 'document_preview_container'));
  3669. }
  3670. }
  3671. if ($load_dirs) {
  3672. $params['document'] = '<a id="document_preview_'.$course_info['real_id'].'_0" class="document_preview btn btn-default btn-sm" href="javascript:void(0);">'
  3673. . Display::returnFontAwesomeIcon('folder-open').'</a>';
  3674. $params['document'] .= Display::div('', array('id' => 'document_result_'.$course_info['real_id'].'_0', 'class' => 'document_preview_container'));
  3675. }
  3676. $course_title_url = api_get_path(WEB_COURSE_PATH).$course_info['path'].'/index.php?id_session=0';
  3677. $teachers = [];
  3678. if (api_get_setting('display_teacher_in_courselist') === 'true') {
  3679. $teachers = self::getTeachersFromCourse(
  3680. $course_info['real_id'],
  3681. false
  3682. );
  3683. }
  3684. $params['status'] = $row['status'];
  3685. if (api_get_setting('display_coursecode_in_courselist') == 'true') {
  3686. $params['code_course'] = '('.$course_info['visual_code'].') ';
  3687. }
  3688. $params['visibility'] = $course_info['visibility'];
  3689. $params['link'] = $course_title_url;
  3690. $params['thumbnails'] = $thumbnails;
  3691. $params['image'] = $image;
  3692. $params['title'] = $course_info['title'];
  3693. $params['category'] = $course_info['categoryName'];
  3694. $params['teachers'] = $teachers;
  3695. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED) {
  3696. $params['notifications'] = $showNotification;
  3697. }
  3698. $courseList[] = $params;
  3699. }
  3700. return $courseList;
  3701. }
  3702. /**
  3703. * Retrieves the user defined course categories
  3704. * @param int $userId
  3705. * @return array
  3706. */
  3707. public static function get_user_course_categories($userId = 0)
  3708. {
  3709. $userId = empty($userId) ? api_get_user_id() : (int) $userId;
  3710. $table_category = Database::get_main_table(TABLE_USER_COURSE_CATEGORY);
  3711. $sql = "SELECT * FROM $table_category
  3712. WHERE user_id = $userId
  3713. ORDER BY sort ASC
  3714. ";
  3715. $result = Database::query($sql);
  3716. $output = array();
  3717. while ($row = Database::fetch_array($result, 'ASSOC')) {
  3718. $output[$row['id']] = $row;
  3719. }
  3720. return $output;
  3721. }
  3722. /**
  3723. * Return an array the user_category id and title for the course $courseId for user $userId
  3724. * @param $userId
  3725. * @param $courseId
  3726. * @return array
  3727. */
  3728. public static function getUserCourseCategoryForCourse($userId, $courseId)
  3729. {
  3730. $tblCourseRelUser = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  3731. $tblUserCategory = Database::get_main_table(TABLE_USER_COURSE_CATEGORY);
  3732. $courseId = intval($courseId);
  3733. $userId = intval($userId);
  3734. $sql = "SELECT user_course_cat, title
  3735. FROM $tblCourseRelUser cru
  3736. LEFT JOIN $tblUserCategory ucc
  3737. ON cru.user_course_cat = ucc.id
  3738. WHERE
  3739. cru.user_id = $userId AND c_id= $courseId ";
  3740. $res = Database::query($sql);
  3741. $data = array();
  3742. if (Database::num_rows($res) > 0) {
  3743. $data = Database::fetch_assoc($res);
  3744. }
  3745. return $data;
  3746. }
  3747. /**
  3748. * Get the course id based on the original id and field name in the extra fields.
  3749. * Returns 0 if course was not found
  3750. *
  3751. * @param string $value Original course code
  3752. * @param string $variable Original field name
  3753. * @return array
  3754. */
  3755. public static function getCourseInfoFromOriginalId($value, $variable)
  3756. {
  3757. $extraFieldValue = new ExtraFieldValue('course');
  3758. $result = $extraFieldValue->get_item_id_from_field_variable_and_field_value(
  3759. $variable,
  3760. $value
  3761. );
  3762. if (!empty($result)) {
  3763. $courseInfo = api_get_course_info_by_id($result['item_id']);
  3764. return $courseInfo;
  3765. }
  3766. return [];
  3767. }
  3768. /**
  3769. * Display code for one specific course a logged in user is subscribed to.
  3770. * Shows a link to the course, what's new icons...
  3771. *
  3772. * $my_course['d'] - course directory
  3773. * $my_course['i'] - course title
  3774. * $my_course['c'] - visual course code
  3775. * $my_course['k'] - system course code
  3776. *
  3777. * @param array Course details
  3778. * @param integer Session ID
  3779. * @param string CSS class to apply to course entry
  3780. * @param boolean Whether the session is supposedly accessible now
  3781. * (not in the case it has passed and is in invisible/unaccessible mode)
  3782. * @param bool Whether to show the document quick-loader or not
  3783. * @return string The HTML to be printed for the course entry
  3784. *
  3785. * @version 1.0.3
  3786. * @todo refactor into different functions for database calls | logic | display
  3787. * @todo replace single-character $my_course['d'] indices
  3788. * @todo move code for what's new icons to a separate function to clear things up
  3789. * @todo add a parameter user_id so that it is possible to show the
  3790. * courselist of other users (=generalisation).
  3791. * This will prevent having to write a new function for this.
  3792. */
  3793. public static function get_logged_user_course_html(
  3794. $course,
  3795. $session_id = 0,
  3796. $class = 'courses',
  3797. $session_accessible = true,
  3798. $load_dirs = false
  3799. ) {
  3800. $now = date('Y-m-d h:i:s');
  3801. $user_id = api_get_user_id();
  3802. $course_info = api_get_course_info_by_id($course['real_id']);
  3803. $course_visibility = $course_info['visibility'];
  3804. if ($course_visibility == COURSE_VISIBILITY_HIDDEN) {
  3805. return '';
  3806. }
  3807. $userInCourseStatus = self::getUserInCourseStatus(
  3808. $user_id,
  3809. $course_info['real_id']
  3810. );
  3811. $course_info['status'] = empty($session_id) ? $userInCourseStatus : STUDENT;
  3812. $course_info['id_session'] = $session_id;
  3813. $is_coach = api_is_coach($session_id, $course_info['real_id']);
  3814. // Display course entry.
  3815. // Show a hyperlink to the course, unless the course is closed and user is not course admin.
  3816. $session_url = '';
  3817. $params = array();
  3818. $params['icon'] = Display::return_icon(
  3819. 'blackboard_blue.png',
  3820. null,
  3821. array(),
  3822. ICON_SIZE_LARGE,
  3823. null,
  3824. true
  3825. );
  3826. $params['real_id'] = $course_info['real_id'];
  3827. // Display the "what's new" icons
  3828. $notifications = '';
  3829. if (
  3830. ($course_visibility != COURSE_VISIBILITY_CLOSED && $course_visibility != COURSE_VISIBILITY_HIDDEN) ||
  3831. !api_get_configuration_value('hide_course_notification')
  3832. ) {
  3833. $notifications .= Display::show_notification($course_info);
  3834. }
  3835. if ($session_accessible) {
  3836. if ($course_visibility != COURSE_VISIBILITY_CLOSED ||
  3837. $userInCourseStatus == COURSEMANAGER
  3838. ) {
  3839. if (empty($course_info['id_session'])) {
  3840. $course_info['id_session'] = 0;
  3841. }
  3842. $sessionCourseAvailable = false;
  3843. $sessionCourseStatus = api_get_session_visibility($session_id, $course_info['real_id']);
  3844. if (in_array($sessionCourseStatus,
  3845. array(SESSION_VISIBLE_READ_ONLY, SESSION_VISIBLE, SESSION_AVAILABLE))) {
  3846. $sessionCourseAvailable = true;
  3847. }
  3848. if ($userInCourseStatus == COURSEMANAGER || $sessionCourseAvailable) {
  3849. $session_url = $course_info['course_public_url'].'?id_session='.$course_info['id_session'];
  3850. $session_title = '<a href="'.$session_url.'">'.$course_info['name'].'</a>'.$notifications;
  3851. } else {
  3852. $session_title = $course_info['name'];
  3853. }
  3854. } else {
  3855. $session_title =
  3856. $course_info['name'].' '.
  3857. Display::tag('span', get_lang('CourseClosed'), array('class' => 'item_closed'));
  3858. }
  3859. } else {
  3860. $session_title = $course_info['name'];
  3861. }
  3862. $thumbnails = null;
  3863. $image = null;
  3864. $showCustomIcon = api_get_setting('course_images_in_courses_list');
  3865. $iconName = basename($course_info['course_image']);
  3866. if ($showCustomIcon === 'true' && $iconName != 'course.png') {
  3867. $thumbnails = $course_info['course_image'];
  3868. $image = $course_info['course_image_large'];
  3869. } else {
  3870. $image = Display::return_icon(
  3871. 'session_default.png',
  3872. null,
  3873. null,
  3874. null,
  3875. null,
  3876. true
  3877. );
  3878. }
  3879. $params['thumbnails'] = $thumbnails;
  3880. $params['image'] = $image;
  3881. $params['link'] = $session_url;
  3882. $params['title'] = $session_title;
  3883. $params['edit_actions'] = '';
  3884. $params['document'] = '';
  3885. $params['category'] = $course_info['categoryName'];
  3886. if ($course_visibility != COURSE_VISIBILITY_CLOSED &&
  3887. $course_visibility != COURSE_VISIBILITY_HIDDEN
  3888. ) {
  3889. if (api_is_platform_admin()) {
  3890. $params['edit_actions'] .= api_get_path(WEB_CODE_PATH).'course_info/infocours.php?cidReq='.$course_info['code'];
  3891. if ($load_dirs) {
  3892. $params['document'] .= '<a id="document_preview_'.$course_info['real_id'].'_'.$course_info['id_session'].'" class="document_preview btn btn-default btn-sm" href="javascript:void(0);">'.
  3893. Display::returnFontAwesomeIcon('folder-open').'</a>';
  3894. $params['document'] .= Display::div('', array(
  3895. 'id' => 'document_result_'.$course_info['real_id'].'_'.$course_info['id_session'],
  3896. 'class' => 'document_preview_container'
  3897. ));
  3898. }
  3899. }
  3900. }
  3901. if (api_get_setting('display_teacher_in_courselist') === 'true') {
  3902. $teacher_list = self::getTeachersFromCourse(
  3903. $course_info['real_id'],
  3904. true
  3905. );
  3906. $course_coachs = self::get_coachs_from_course(
  3907. $course_info['id_session'],
  3908. $course_info['real_id']
  3909. );
  3910. $params['teachers'] = $teacher_list;
  3911. if (($course_info['status'] == STUDENT && !empty($course_info['id_session'])) ||
  3912. ($is_coach && $course_info['status'] != COURSEMANAGER)
  3913. ) {
  3914. $params['coaches'] = $course_coachs;
  3915. }
  3916. }
  3917. $special = isset($course['special_course']) ? true : false;
  3918. $params['title'] = $session_title;
  3919. $params['special'] = $special;
  3920. $params['code'] = $course_info['visual_code'];
  3921. $params['extra'] = '';
  3922. $html = $params;
  3923. $session_category_id = null;
  3924. if (1) {
  3925. $session = '';
  3926. $active = false;
  3927. if (!empty($course_info['id_session'])) {
  3928. $session = api_get_session_info($course_info['id_session']);
  3929. $sessionCoachName = '';
  3930. if (!empty($session['id_coach'])) {
  3931. $coachInfo = api_get_user_info($session['id_coach']);
  3932. $sessionCoachName = $coachInfo['complete_name'];
  3933. }
  3934. $session_category_id = self::get_session_category_id_by_session_id($course_info['id_session']);
  3935. if (
  3936. $session['access_start_date'] == '0000-00-00 00:00:00' || empty($session['access_start_date']) ||
  3937. $session['access_start_date'] == '0000-00-00'
  3938. ) {
  3939. $session['dates'] = '';
  3940. if (api_get_setting('show_session_coach') === 'true') {
  3941. $session['coach'] = get_lang('GeneralCoach').': '.$sessionCoachName;
  3942. }
  3943. $active = true;
  3944. } else {
  3945. $session ['dates'] = ' - '.get_lang('From').' '.$session['access_start_date'].' '.get_lang('To').' '.$session['access_end_date'];
  3946. if (api_get_setting('show_session_coach') === 'true') {
  3947. $session['coach'] = get_lang('GeneralCoach').': '.$sessionCoachName;
  3948. }
  3949. $date_start = $session['access_start_date'];
  3950. $date_end = $session['access_end_date'];
  3951. $active = !$date_end ? ($date_start <= $now) : ($date_start <= $now && $date_end >= $now);
  3952. }
  3953. }
  3954. $user_course_category = '';
  3955. if (isset($course_info['user_course_cat'])) {
  3956. $user_course_category = $course_info['user_course_cat'];
  3957. }
  3958. $output = array(
  3959. $user_course_category,
  3960. $html,
  3961. $course_info['id_session'],
  3962. $session,
  3963. 'active' => $active,
  3964. 'session_category_id' => $session_category_id
  3965. );
  3966. if (Skill::isAllowed($user_id, false)) {
  3967. $em = Database::getManager();
  3968. $objUser = $em->find('ChamiloUserBundle:User', $user_id);
  3969. $objCourse = $em->find('ChamiloCoreBundle:Course', $course['real_id']);
  3970. $objSession = $em->find('ChamiloCoreBundle:Session', $session_id);
  3971. $skill = $em->getRepository('ChamiloCoreBundle:Skill')->getLastByUser($objUser, $objCourse, $objSession);
  3972. $output['skill'] = null;
  3973. if ($skill) {
  3974. $output['skill']['name'] = $skill->getName();
  3975. $output['skill']['icon'] = $skill->getIcon();
  3976. }
  3977. }
  3978. } else {
  3979. $output = array($course_info['user_course_cat'], $html);
  3980. }
  3981. return $output;
  3982. }
  3983. /**
  3984. *
  3985. * @param string $source_course_code
  3986. * @param int $source_session_id
  3987. * @param string $destination_course_code
  3988. * @param int $destination_session_id
  3989. * @param array $params
  3990. *
  3991. * @return bool
  3992. */
  3993. public static function copy_course(
  3994. $source_course_code,
  3995. $source_session_id,
  3996. $destination_course_code,
  3997. $destination_session_id,
  3998. $params = array()
  3999. ) {
  4000. $course_info = api_get_course_info($source_course_code);
  4001. if (!empty($course_info)) {
  4002. $cb = new CourseBuilder('', $course_info);
  4003. $course = $cb->build($source_session_id, $source_course_code, true);
  4004. $course_restorer = new CourseRestorer($course);
  4005. $course_restorer->skip_content = $params;
  4006. $course_restorer->restore(
  4007. $destination_course_code,
  4008. $destination_session_id,
  4009. true,
  4010. true
  4011. );
  4012. return true;
  4013. }
  4014. return false;
  4015. }
  4016. /**
  4017. * A simpler version of the copy_course, the function creates an empty course with an autogenerated course code
  4018. *
  4019. * @param string $new_title new course title
  4020. * @param string source course code
  4021. * @param int source session id
  4022. * @param int destination session id
  4023. * @param bool new copied tools (Exercises and LPs)will be set to invisible by default?
  4024. *
  4025. * @return array
  4026. */
  4027. public static function copy_course_simple(
  4028. $new_title,
  4029. $source_course_code,
  4030. $source_session_id = 0,
  4031. $destination_session_id = 0,
  4032. $params = array()
  4033. ) {
  4034. $source_course_info = api_get_course_info($source_course_code);
  4035. if (!empty($source_course_info)) {
  4036. $new_course_code = self::generate_nice_next_course_code($source_course_code);
  4037. if ($new_course_code) {
  4038. $new_course_info = self::create_course(
  4039. $new_title,
  4040. $new_course_code,
  4041. false
  4042. );
  4043. if (!empty($new_course_info['code'])) {
  4044. $result = self::copy_course(
  4045. $source_course_code,
  4046. $source_session_id,
  4047. $new_course_info['code'],
  4048. $destination_session_id,
  4049. $params
  4050. );
  4051. if ($result) {
  4052. return $new_course_info;
  4053. }
  4054. }
  4055. }
  4056. }
  4057. return false;
  4058. }
  4059. /**
  4060. * Creates a new course code based in a given code
  4061. *
  4062. * @param string wanted code
  4063. * <code> $wanted_code = 'curse' if there are in the DB codes like curse1 curse2 the function will return: course3</code>
  4064. * if the course code doest not exist in the DB the same course code will be returned
  4065. * @return string wanted unused code
  4066. */
  4067. public static function generate_nice_next_course_code($wanted_code)
  4068. {
  4069. $course_code_ok = !self::course_code_exists($wanted_code);
  4070. if (!$course_code_ok) {
  4071. $wanted_code = self::generate_course_code($wanted_code);
  4072. $table = Database::get_main_table(TABLE_MAIN_COURSE);
  4073. $wanted_code = Database::escape_string($wanted_code);
  4074. $sql = "SELECT count(id) as count
  4075. FROM $table
  4076. WHERE code LIKE '$wanted_code%'";
  4077. $result = Database::query($sql);
  4078. if (Database::num_rows($result) > 0) {
  4079. $row = Database::fetch_array($result);
  4080. $count = $row['count'] + 1;
  4081. $wanted_code = $wanted_code.'_'.$count;
  4082. $result = api_get_course_info($wanted_code);
  4083. if (empty($result)) {
  4084. return $wanted_code;
  4085. }
  4086. }
  4087. return false;
  4088. }
  4089. return $wanted_code;
  4090. }
  4091. /**
  4092. * Gets the status of the users agreement in a course course-session
  4093. *
  4094. * @param int $user_id
  4095. * @param string $course_code
  4096. * @param int $session_id
  4097. * @return boolean
  4098. */
  4099. public static function is_user_accepted_legal($user_id, $course_code, $session_id = 0)
  4100. {
  4101. $user_id = intval($user_id);
  4102. $course_code = Database::escape_string($course_code);
  4103. $session_id = intval($session_id);
  4104. $courseInfo = api_get_course_info($course_code);
  4105. $courseId = $courseInfo['real_id'];
  4106. // Course legal
  4107. $enabled = api_get_plugin_setting('courselegal', 'tool_enable');
  4108. if ($enabled == 'true') {
  4109. require_once api_get_path(SYS_PLUGIN_PATH).'courselegal/config.php';
  4110. $plugin = CourseLegalPlugin::create();
  4111. return $plugin->isUserAcceptedLegal($user_id, $course_code, $session_id);
  4112. }
  4113. if (empty($session_id)) {
  4114. $table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  4115. $sql = "SELECT legal_agreement FROM $table
  4116. WHERE user_id = $user_id AND c_id = $courseId ";
  4117. $result = Database::query($sql);
  4118. if (Database::num_rows($result) > 0) {
  4119. $result = Database::fetch_array($result);
  4120. if ($result['legal_agreement'] == 1) {
  4121. return true;
  4122. }
  4123. }
  4124. return false;
  4125. } else {
  4126. $table = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  4127. $sql = "SELECT legal_agreement FROM $table
  4128. WHERE user_id = $user_id AND c_id = $courseId AND session_id = $session_id";
  4129. $result = Database::query($sql);
  4130. if (Database::num_rows($result) > 0) {
  4131. $result = Database::fetch_array($result);
  4132. if ($result['legal_agreement'] == 1) {
  4133. return true;
  4134. }
  4135. }
  4136. return false;
  4137. }
  4138. }
  4139. /**
  4140. * Saves the user-course legal agreement
  4141. * @param int user id
  4142. * @param string course code
  4143. * @param int session id
  4144. * @return mixed
  4145. */
  4146. public static function save_user_legal($user_id, $course_code, $session_id = null)
  4147. {
  4148. // Course plugin legal
  4149. $enabled = api_get_plugin_setting('courselegal', 'tool_enable');
  4150. if ($enabled == 'true') {
  4151. require_once api_get_path(SYS_PLUGIN_PATH).'courselegal/config.php';
  4152. $plugin = CourseLegalPlugin::create();
  4153. return $plugin->saveUserLegal($user_id, $course_code, $session_id);
  4154. }
  4155. $user_id = intval($user_id);
  4156. $course_code = Database::escape_string($course_code);
  4157. $session_id = intval($session_id);
  4158. $courseInfo = api_get_course_info($course_code);
  4159. $courseId = $courseInfo['real_id'];
  4160. if (empty($session_id)) {
  4161. $table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  4162. $sql = "UPDATE $table SET legal_agreement = '1'
  4163. WHERE user_id = $user_id AND c_id = $courseId ";
  4164. Database::query($sql);
  4165. } else {
  4166. $table = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  4167. $sql = "UPDATE $table SET legal_agreement = '1'
  4168. WHERE user_id = $user_id AND c_id = $courseId AND session_id = $session_id";
  4169. Database::query($sql);
  4170. }
  4171. }
  4172. /**
  4173. * @param int $user_id
  4174. * @param int $course_id
  4175. * @param int $session_id
  4176. * @param int $url_id
  4177. * @return bool
  4178. */
  4179. public static function get_user_course_vote($user_id, $course_id, $session_id = 0, $url_id = 0)
  4180. {
  4181. $table_user_course_vote = Database::get_main_table(TABLE_MAIN_USER_REL_COURSE_VOTE);
  4182. $session_id = !isset($session_id) ? api_get_session_id() : intval($session_id);
  4183. $url_id = empty($url_id) ? api_get_current_access_url_id() : intval($url_id);
  4184. $user_id = intval($user_id);
  4185. if (empty($user_id)) {
  4186. return false;
  4187. }
  4188. $params = array(
  4189. 'user_id' => $user_id,
  4190. 'c_id' => $course_id,
  4191. 'session_id' => $session_id,
  4192. 'url_id' => $url_id
  4193. );
  4194. $result = Database::select(
  4195. 'vote',
  4196. $table_user_course_vote,
  4197. array(
  4198. 'where' => array(
  4199. 'user_id = ? AND c_id = ? AND session_id = ? AND url_id = ?' => $params
  4200. )
  4201. ),
  4202. 'first'
  4203. );
  4204. if (!empty($result)) {
  4205. return $result['vote'];
  4206. }
  4207. return false;
  4208. }
  4209. /**
  4210. * @param int $course_id
  4211. * @param int $session_id
  4212. * @param int $url_id
  4213. * @return array
  4214. */
  4215. public static function get_course_ranking(
  4216. $course_id,
  4217. $session_id = 0,
  4218. $url_id = 0
  4219. ) {
  4220. $table_course_ranking = Database::get_main_table(TABLE_STATISTIC_TRACK_COURSE_RANKING);
  4221. $session_id = empty($session_id) ? api_get_session_id() : intval($session_id);
  4222. $url_id = empty($url_id) ? api_get_current_access_url_id() : intval($url_id);
  4223. $now = api_get_utc_datetime();
  4224. $params = array(
  4225. 'c_id' => $course_id,
  4226. 'session_id' => $session_id,
  4227. 'url_id' => $url_id,
  4228. 'creation_date' => $now,
  4229. );
  4230. $result = Database::select(
  4231. 'c_id, accesses, total_score, users',
  4232. $table_course_ranking,
  4233. array('where' => array('c_id = ? AND session_id = ? AND url_id = ?' => $params)),
  4234. 'first'
  4235. );
  4236. $point_average_in_percentage = 0;
  4237. $point_average_in_star = 0;
  4238. $users_who_voted = 0;
  4239. if (!empty($result['users'])) {
  4240. $users_who_voted = $result['users'];
  4241. $point_average_in_percentage = round($result['total_score'] / $result['users'] * 100 / 5, 2);
  4242. $point_average_in_star = round($result['total_score'] / $result['users'], 1);
  4243. }
  4244. $result['user_vote'] = false;
  4245. if (!api_is_anonymous()) {
  4246. $result['user_vote'] = self::get_user_course_vote(api_get_user_id(), $course_id, $session_id, $url_id);
  4247. }
  4248. $result['point_average'] = $point_average_in_percentage;
  4249. $result['point_average_star'] = $point_average_in_star;
  4250. $result['users_who_voted'] = $users_who_voted;
  4251. return $result;
  4252. }
  4253. /**
  4254. *
  4255. * Updates the course ranking
  4256. * @param int course id
  4257. * @param int $session_id
  4258. * @param int url id
  4259. * @param $points_to_add
  4260. * @param bool $add_access
  4261. * @param bool $add_user
  4262. * @return array
  4263. **/
  4264. public static function update_course_ranking(
  4265. $course_id = 0 ,
  4266. $session_id = 0,
  4267. $url_id = 0,
  4268. $points_to_add = null,
  4269. $add_access = true,
  4270. $add_user = true
  4271. ) {
  4272. // Course catalog stats modifications see #4191
  4273. $table_course_ranking = Database::get_main_table(TABLE_STATISTIC_TRACK_COURSE_RANKING);
  4274. $now = api_get_utc_datetime();
  4275. $course_id = empty($course_id) ? api_get_course_int_id() : intval($course_id);
  4276. $session_id = empty($session_id) ? api_get_session_id() : intval($session_id);
  4277. $url_id = empty($url_id) ? api_get_current_access_url_id() : intval($url_id);
  4278. $params = array(
  4279. 'c_id' => $course_id,
  4280. 'session_id' => $session_id,
  4281. 'url_id' => $url_id,
  4282. 'creation_date' => $now,
  4283. 'total_score' => 0,
  4284. 'users' => 0
  4285. );
  4286. $result = Database::select(
  4287. 'id, accesses, total_score, users',
  4288. $table_course_ranking,
  4289. array('where' => array('c_id = ? AND session_id = ? AND url_id = ?' => $params)),
  4290. 'first'
  4291. );
  4292. // Problem here every time we load the courses/XXXX/index.php course home page we update the access
  4293. if (empty($result)) {
  4294. if ($add_access) {
  4295. $params['accesses'] = 1;
  4296. }
  4297. //The votes and users are empty
  4298. if (isset($points_to_add) && !empty($points_to_add)) {
  4299. $params['total_score'] = intval($points_to_add);
  4300. }
  4301. if ($add_user) {
  4302. $params['users'] = 1;
  4303. }
  4304. $result = Database::insert($table_course_ranking, $params);
  4305. } else {
  4306. $my_params = array();
  4307. if ($add_access) {
  4308. $my_params['accesses'] = intval($result['accesses']) + 1;
  4309. }
  4310. if (isset($points_to_add) && !empty($points_to_add)) {
  4311. $my_params['total_score'] = $result['total_score'] + $points_to_add;
  4312. }
  4313. if ($add_user) {
  4314. $my_params['users'] = $result['users'] + 1;
  4315. }
  4316. if (!empty($my_params)) {
  4317. $result = Database::update(
  4318. $table_course_ranking,
  4319. $my_params,
  4320. array('c_id = ? AND session_id = ? AND url_id = ?' => $params)
  4321. );
  4322. }
  4323. }
  4324. return $result;
  4325. }
  4326. /**
  4327. * Add user vote to a course
  4328. *
  4329. * @param int user id
  4330. * @param int vote [1..5]
  4331. * @param int course id
  4332. * @param int session id
  4333. * @param int url id (access_url_id)
  4334. * @return false|string 'added', 'updated' or 'nothing'
  4335. */
  4336. public static function add_course_vote(
  4337. $user_id,
  4338. $vote,
  4339. $course_id,
  4340. $session_id = 0,
  4341. $url_id = 0
  4342. ) {
  4343. $table_user_course_vote = Database::get_main_table(TABLE_MAIN_USER_REL_COURSE_VOTE);
  4344. $course_id = empty($course_id) ? api_get_course_int_id() : intval($course_id);
  4345. if (empty($course_id) || empty($user_id)) {
  4346. return false;
  4347. }
  4348. if (!in_array($vote, array(1, 2, 3, 4, 5))) {
  4349. return false;
  4350. }
  4351. $session_id = empty($session_id) ? api_get_session_id() : intval($session_id);
  4352. $url_id = empty($url_id) ? api_get_current_access_url_id() : intval($url_id);
  4353. $vote = intval($vote);
  4354. $params = array(
  4355. 'user_id' => intval($user_id),
  4356. 'c_id' => $course_id,
  4357. 'session_id' => $session_id,
  4358. 'url_id' => $url_id,
  4359. 'vote' => $vote
  4360. );
  4361. $action_done = 'nothing';
  4362. $result = Database::select(
  4363. 'id, vote',
  4364. $table_user_course_vote,
  4365. array('where' => array('user_id = ? AND c_id = ? AND session_id = ? AND url_id = ?' => $params)),
  4366. 'first'
  4367. );
  4368. if (empty($result)) {
  4369. Database::insert($table_user_course_vote, $params);
  4370. $points_to_add = $vote;
  4371. $add_user = true;
  4372. $action_done = 'added';
  4373. } else {
  4374. $my_params = array('vote' => $vote);
  4375. $points_to_add = $vote - $result['vote'];
  4376. $add_user = false;
  4377. Database::update(
  4378. $table_user_course_vote,
  4379. $my_params,
  4380. array('user_id = ? AND c_id = ? AND session_id = ? AND url_id = ?' => $params)
  4381. );
  4382. $action_done = 'updated';
  4383. }
  4384. // Current points
  4385. if (!empty($points_to_add)) {
  4386. self::update_course_ranking(
  4387. $course_id,
  4388. $session_id,
  4389. $url_id,
  4390. $points_to_add,
  4391. false,
  4392. $add_user
  4393. );
  4394. }
  4395. return $action_done;
  4396. }
  4397. /**
  4398. * Remove course ranking + user votes
  4399. *
  4400. * @param int $course_id
  4401. * @param int $session_id
  4402. * @param int $url_id
  4403. *
  4404. */
  4405. public static function remove_course_ranking($course_id, $session_id, $url_id = null)
  4406. {
  4407. $table_course_ranking = Database::get_main_table(TABLE_STATISTIC_TRACK_COURSE_RANKING);
  4408. $table_user_course_vote = Database::get_main_table(TABLE_MAIN_USER_REL_COURSE_VOTE);
  4409. if (!empty($course_id) && isset($session_id)) {
  4410. $url_id = empty($url_id) ? api_get_current_access_url_id() : intval($url_id);
  4411. $params = array(
  4412. 'c_id' => $course_id,
  4413. 'session_id' => $session_id,
  4414. 'url_id' => $url_id,
  4415. );
  4416. Database::delete($table_course_ranking, array('c_id = ? AND session_id = ? AND url_id = ?' => $params));
  4417. Database::delete($table_user_course_vote, array('c_id = ? AND session_id = ? AND url_id = ?' => $params));
  4418. }
  4419. }
  4420. /**
  4421. * Returns an array with the hottest courses
  4422. * @param int $days number of days
  4423. * @param int $limit number of hottest courses
  4424. * @return array
  4425. */
  4426. public static function return_hot_courses($days = 30, $limit = 6)
  4427. {
  4428. if (api_is_invitee()) {
  4429. return array();
  4430. }
  4431. $limit = intval($limit);
  4432. // Getting my courses
  4433. $my_course_list = self::get_courses_list_by_user_id(api_get_user_id());
  4434. $my_course_code_list = array();
  4435. foreach ($my_course_list as $course) {
  4436. $my_course_code_list[$course['real_id']] = $course['real_id'];
  4437. }
  4438. if (api_is_drh()) {
  4439. $courses = self::get_courses_followed_by_drh(api_get_user_id());
  4440. foreach ($courses as $course) {
  4441. $my_course_code_list[$course['real_id']] = $course['real_id'];
  4442. }
  4443. }
  4444. $table_course_access = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  4445. $table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  4446. $table_course_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  4447. //$table_course_access table uses the now() and interval ...
  4448. $now = api_get_utc_datetime();
  4449. $sql = "SELECT COUNT(course_access_id) course_count, a.c_id, visibility
  4450. FROM $table_course c
  4451. INNER JOIN $table_course_access a
  4452. ON (c.id = a.c_id)
  4453. INNER JOIN $table_course_url u
  4454. ON u.c_id = c.id
  4455. WHERE
  4456. u.access_url_id = ".api_get_current_access_url_id()." AND
  4457. login_course_date <= '$now' AND
  4458. login_course_date > DATE_SUB('$now', INTERVAL $days DAY) AND
  4459. visibility <> '".COURSE_VISIBILITY_CLOSED."' AND visibility <> '".COURSE_VISIBILITY_HIDDEN."'
  4460. GROUP BY a.c_id
  4461. ORDER BY course_count DESC
  4462. LIMIT $limit
  4463. ";
  4464. $result = Database::query($sql);
  4465. $courses = array();
  4466. if (Database::num_rows($result)) {
  4467. $courses = Database::store_result($result, 'ASSOC');
  4468. $courses = self::processHotCourseItem($courses, $my_course_code_list);
  4469. }
  4470. return $courses;
  4471. }
  4472. /**
  4473. * @param array $courses
  4474. * @param array $my_course_code_list
  4475. * @return mixed
  4476. */
  4477. public static function processHotCourseItem($courses, $my_course_code_list = array())
  4478. {
  4479. $hotCourses = [];
  4480. $ajax_url = api_get_path(WEB_AJAX_PATH).'course.ajax.php?a=add_course_vote';
  4481. $stok = Security::get_existing_token();
  4482. $user_id = api_get_user_id();
  4483. foreach ($courses as $courseId) {
  4484. $course_info = api_get_course_info_by_id($courseId['c_id']);
  4485. $courseCode = $course_info['code'];
  4486. $categoryCode = !empty($course_info['categoryCode']) ? $course_info['categoryCode'] : "";
  4487. $my_course = $course_info;
  4488. $my_course['go_to_course_button'] = '';
  4489. $my_course['register_button'] = '';
  4490. $access_link = self::get_access_link_by_user(
  4491. api_get_user_id(),
  4492. $course_info,
  4493. $my_course_code_list
  4494. );
  4495. $userRegisterdInCourse = self::is_user_subscribed_in_course($user_id, $course_info['code']);
  4496. $userRegisterdInCourseAsTeacher = self::is_course_teacher($user_id, $course_info['code']);
  4497. $userRegisterd = ($userRegisterdInCourse && $userRegisterdInCourseAsTeacher);
  4498. $my_course['is_registerd'] = $userRegisterd;
  4499. $my_course['title_cut'] = cut($course_info['title'], 45);
  4500. // if user registered as student
  4501. /* if ($userRegisterdInCourse) {
  4502. $icon = '<em class="fa fa-graduation-cap"></em>';
  4503. $title = get_lang("AlreadyRegisteredToCourse");
  4504. $my_course['already_register_as'] = Display::tag(
  4505. 'button',
  4506. $icon,
  4507. array(
  4508. 'id' => 'register',
  4509. 'class' => 'btn btn-default btn-sm',
  4510. 'title' => $title,
  4511. 'aria-label' => $title
  4512. )
  4513. );
  4514. } elseif ($userRegisterdInCourseAsTeacher) {
  4515. // if user registered as teacher
  4516. $icon = '<em class="fa fa-suitcase"></em>';
  4517. $title = get_lang("YouAreATeacherOfThisCourse");
  4518. $my_course['already_register_as'] = Display::tag(
  4519. 'button',
  4520. $icon,
  4521. array(
  4522. 'id' => 'register',
  4523. 'class' => 'btn btn-default btn-sm',
  4524. 'title' => $title,
  4525. 'aria-label' => $title
  4526. )
  4527. );
  4528. } */
  4529. //Course visibility
  4530. if ($access_link && in_array('register', $access_link)) {
  4531. $my_course['register_button'] = Display::url(
  4532. get_lang('Subscribe').' '.
  4533. Display::returnFontAwesomeIcon('sign-in'),
  4534. api_get_path(WEB_COURSE_PATH).$course_info['path'].
  4535. '/index.php?action=subscribe&sec_token='.$stok,
  4536. array(
  4537. 'class' => 'btn btn-success btn-sm',
  4538. 'title' => get_lang('Subscribe'),
  4539. 'aria-label' => get_lang('Subscribe')
  4540. )
  4541. );
  4542. }
  4543. if ($access_link && in_array('enter', $access_link) ||
  4544. $course_info['visibility'] == COURSE_VISIBILITY_OPEN_WORLD
  4545. ) {
  4546. $my_course['go_to_course_button'] = Display::url(
  4547. get_lang('GoToCourse').' '.
  4548. Display::returnFontAwesomeIcon('share'),
  4549. api_get_path(WEB_COURSE_PATH).$course_info['path'].'/index.php',
  4550. array(
  4551. 'class' => 'btn btn-default btn-sm',
  4552. 'title' => get_lang('GoToCourse'),
  4553. 'aria-label' => get_lang('GoToCourse')
  4554. )
  4555. );
  4556. }
  4557. if ($access_link && in_array('unsubscribe', $access_link)) {
  4558. $my_course['unsubscribe_button'] = Display::url(
  4559. get_lang('Unreg').' '.
  4560. Display::returnFontAwesomeIcon('sign-out'),
  4561. api_get_path(WEB_CODE_PATH).'auth/courses.php?action=unsubscribe&unsubscribe='.$courseCode
  4562. . '&sec_token='.$stok.'&category_code='.$categoryCode,
  4563. array(
  4564. 'class' => 'btn btn-danger btn-sm',
  4565. 'title' => get_lang('Unreg'),
  4566. 'aria-label' => get_lang('Unreg')
  4567. )
  4568. );
  4569. }
  4570. // start buycourse validation
  4571. // display the course price and buy button if the buycourses plugin is enabled and this course is configured
  4572. $plugin = BuyCoursesPlugin::create();
  4573. $isThisCourseInSale = $plugin->buyCoursesForGridCatalogValidator(
  4574. $course_info['real_id'],
  4575. BuyCoursesPlugin::PRODUCT_TYPE_COURSE
  4576. );
  4577. if ($isThisCourseInSale) {
  4578. // set the price label
  4579. $my_course['price'] = $isThisCourseInSale['html'];
  4580. // set the Buy button instead register.
  4581. if ($isThisCourseInSale['verificator'] && !empty($my_course['register_button'])) {
  4582. $my_course['register_button'] = $plugin->returnBuyCourseButton(
  4583. $course_info['real_id'],
  4584. BuyCoursesPlugin::PRODUCT_TYPE_COURSE
  4585. );
  4586. }
  4587. }
  4588. // end buycourse validation
  4589. //Description
  4590. $my_course['description_button'] = '';
  4591. $my_course['description_button'] = Display::url(
  4592. Display::returnFontAwesomeIcon('info-circle'),
  4593. api_get_path(WEB_AJAX_PATH).'course_home.ajax.php?a=show_course_information&code='.$course_info['code'],
  4594. [
  4595. 'class' => 'btn btn-default btn-sm ajax',
  4596. 'data-title' => get_lang('Description'),
  4597. 'title' => get_lang('Description'),
  4598. 'aria-label' => get_lang('Description')
  4599. ]
  4600. );
  4601. $my_course['teachers'] = self::getTeachersFromCourse($course_info['real_id'], true);
  4602. $point_info = self::get_course_ranking($course_info['real_id'], 0);
  4603. $my_course['rating_html'] = '';
  4604. if (api_get_configuration_value('hide_course_rating') === false) {
  4605. $my_course['rating_html'] = Display::return_rating_system(
  4606. 'star_'.$course_info['real_id'],
  4607. $ajax_url.'&course_id='.$course_info['real_id'],
  4608. $point_info
  4609. );
  4610. }
  4611. $hotCourses[] = $my_course;
  4612. }
  4613. return $hotCourses;
  4614. }
  4615. /**
  4616. * @param int $limit
  4617. * @return array
  4618. */
  4619. public static function return_most_accessed_courses($limit = 5)
  4620. {
  4621. $table_course_ranking = Database::get_main_table(TABLE_STATISTIC_TRACK_COURSE_RANKING);
  4622. $params['url_id'] = api_get_current_access_url_id();
  4623. $result = Database::select(
  4624. 'c_id, accesses, total_score, users',
  4625. $table_course_ranking,
  4626. array('where' => array('url_id = ?' => $params), 'order' => 'accesses DESC', 'limit' => $limit),
  4627. 'all',
  4628. true
  4629. );
  4630. return $result;
  4631. }
  4632. /**
  4633. * Get courses count
  4634. * @param int $access_url_id Access URL ID (optional)
  4635. * @param int $visibility
  4636. *
  4637. * @return int Number of courses
  4638. */
  4639. public static function count_courses($access_url_id = null, $visibility = null)
  4640. {
  4641. $table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  4642. $table_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  4643. $sql = "SELECT count(c.id) FROM $table_course c";
  4644. if (!empty($access_url_id) && $access_url_id == intval($access_url_id)) {
  4645. $sql .= ", $table_course_rel_access_url u
  4646. WHERE c.id = u.c_id AND u.access_url_id = $access_url_id";
  4647. if (!empty($visibility)) {
  4648. $visibility = intval($visibility);
  4649. $sql .= " AND visibility = $visibility ";
  4650. }
  4651. } else {
  4652. if (!empty($visibility)) {
  4653. $visibility = intval($visibility);
  4654. $sql .= " WHERE visibility = $visibility ";
  4655. }
  4656. }
  4657. $res = Database::query($sql);
  4658. $row = Database::fetch_row($res);
  4659. return $row[0];
  4660. }
  4661. /**
  4662. * Get active courses count.
  4663. * Active = all courses except the ones with hidden visibility.
  4664. *
  4665. * @param int $urlId Access URL ID (optional)
  4666. * @return int Number of courses
  4667. */
  4668. public static function countActiveCourses($urlId = null)
  4669. {
  4670. $table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  4671. $table_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  4672. $sql = "SELECT count(id) FROM $table_course c";
  4673. if (!empty($urlId) && $urlId == intval($urlId)) {
  4674. $sql .= ", $table_course_rel_access_url u
  4675. WHERE
  4676. c.id = u.c_id AND
  4677. u.access_url_id = $urlId AND
  4678. visibility <> ".COURSE_VISIBILITY_HIDDEN;
  4679. } else {
  4680. $sql .= " WHERE visibility <> ".COURSE_VISIBILITY_HIDDEN;
  4681. }
  4682. $res = Database::query($sql);
  4683. $row = Database::fetch_row($res);
  4684. return $row[0];
  4685. }
  4686. /**
  4687. * Returns the SQL conditions to filter course only visible by the user in the catalogue
  4688. *
  4689. * @param string $courseTableAlias Alias of the course table
  4690. * @param bool $hideClosed Whether to hide closed and hidden courses
  4691. * @return string SQL conditions
  4692. */
  4693. public static function getCourseVisibilitySQLCondition(
  4694. $courseTableAlias,
  4695. $hideClosed = false
  4696. ) {
  4697. $visibilityCondition = '';
  4698. $hidePrivate = api_get_setting('course_catalog_hide_private');
  4699. if ($hidePrivate === 'true') {
  4700. $visibilityCondition .= ' AND '.$courseTableAlias.'.visibility <> '.COURSE_VISIBILITY_REGISTERED;
  4701. }
  4702. if ($hideClosed) {
  4703. $visibilityCondition .= ' AND '.$courseTableAlias.'.visibility NOT IN ('.COURSE_VISIBILITY_CLOSED.','.COURSE_VISIBILITY_HIDDEN.')';
  4704. }
  4705. // Check if course have users allowed to see it in the catalogue, then show only if current user is allowed to see it
  4706. $currentUserId = api_get_user_id();
  4707. $restrictedCourses = self::getCatalogueCourseList(true);
  4708. $allowedCoursesToCurrentUser = self::getCatalogueCourseList(true, $currentUserId);
  4709. if (!empty($restrictedCourses)) {
  4710. $visibilityCondition .= ' AND ('.$courseTableAlias.'.code NOT IN ("'.implode('","', $restrictedCourses).'")';
  4711. $visibilityCondition .= ' OR '.$courseTableAlias.'.code IN ("'.implode('","', $allowedCoursesToCurrentUser).'"))';
  4712. }
  4713. // Check if course have users denied to see it in the catalogue, then show only if current user is not denied to see it
  4714. $restrictedCourses = self::getCatalogueCourseList(false);
  4715. $notAllowedCoursesToCurrentUser = self::getCatalogueCourseList(false, $currentUserId);
  4716. if (!empty($restrictedCourses)) {
  4717. $visibilityCondition .= ' AND ('.$courseTableAlias.'.code NOT IN ("'.implode('","', $restrictedCourses).'")';
  4718. $visibilityCondition .= ' OR '.$courseTableAlias.'.code NOT IN ("'.implode('","', $notAllowedCoursesToCurrentUser).'"))';
  4719. }
  4720. return $visibilityCondition;
  4721. }
  4722. /**
  4723. * Get available le courses count
  4724. * @param int $accessUrlId (optional)
  4725. * @return int Number of courses
  4726. */
  4727. public static function countAvailableCourses($accessUrlId = 1)
  4728. {
  4729. $tableCourse = Database::get_main_table(TABLE_MAIN_COURSE);
  4730. $tableCourseRelAccessUrl = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  4731. $specialCourseList = self::get_special_course_list();
  4732. $withoutSpecialCourses = '';
  4733. if (!empty($specialCourseList)) {
  4734. $withoutSpecialCourses = ' AND c.id NOT IN ("'.implode('","', $specialCourseList).'")';
  4735. }
  4736. $visibilityCondition = self::getCourseVisibilitySQLCondition('c', true);
  4737. $accessUrlId = (int) $accessUrlId;
  4738. if (empty($accessUrlId)) {
  4739. $accessUrlId = 1;
  4740. }
  4741. $sql = "SELECT count(c.id)
  4742. FROM $tableCourse c
  4743. INNER JOIN $tableCourseRelAccessUrl u
  4744. ON (c.id = u.c_id)
  4745. WHERE
  4746. u.access_url_id = $accessUrlId AND
  4747. c.visibility != 0 AND
  4748. c.visibility != 4
  4749. $withoutSpecialCourses
  4750. $visibilityCondition
  4751. ";
  4752. $res = Database::query($sql);
  4753. $row = Database::fetch_row($res);
  4754. return $row[0];
  4755. }
  4756. /**
  4757. * Return a link to go to the course, validating the visibility of the
  4758. * course and the user status
  4759. * @param int $uid User ID
  4760. * @param array Course details array
  4761. * @param array List of courses to which the user is subscribed (if not provided, will be generated)
  4762. *
  4763. * @return mixed 'enter' for a link to go to the course or 'register' for a link to subscribe, or false if no access
  4764. */
  4765. static function get_access_link_by_user($uid, $course, $user_courses = array())
  4766. {
  4767. if (empty($uid) || empty($course)) {
  4768. return false;
  4769. }
  4770. if (empty($user_courses)) {
  4771. // get the array of courses to which the user is subscribed
  4772. $user_courses = self::get_courses_list_by_user_id($uid);
  4773. foreach ($user_courses as $k => $v) {
  4774. $user_courses[$k] = $v['real_id'];
  4775. }
  4776. }
  4777. if (!isset($course['real_id']) && empty($course['real_id'])) {
  4778. $course = api_get_course_info($course['code']);
  4779. }
  4780. if ($course['visibility'] == COURSE_VISIBILITY_HIDDEN) {
  4781. return array();
  4782. }
  4783. $is_admin = api_is_platform_admin_by_id($uid);
  4784. $options = array();
  4785. // Register button
  4786. if (!api_is_anonymous($uid) &&
  4787. (
  4788. ($course['visibility'] == COURSE_VISIBILITY_OPEN_WORLD || $course['visibility'] == COURSE_VISIBILITY_OPEN_PLATFORM)
  4789. //$course['visibility'] == COURSE_VISIBILITY_REGISTERED && $course['subscribe'] == SUBSCRIBE_ALLOWED
  4790. ) &&
  4791. $course['subscribe'] == SUBSCRIBE_ALLOWED &&
  4792. (!in_array($course['real_id'], $user_courses) || empty($user_courses))
  4793. ) {
  4794. $options[] = 'register';
  4795. }
  4796. // Go To Course button (only if admin, if course public or if student already subscribed)
  4797. if ($is_admin ||
  4798. $course['visibility'] == COURSE_VISIBILITY_OPEN_WORLD && empty($course['registration_code']) ||
  4799. (api_user_is_login($uid) && $course['visibility'] == COURSE_VISIBILITY_OPEN_PLATFORM && empty($course['registration_code'])) ||
  4800. (in_array($course['real_id'], $user_courses) && $course['visibility'] != COURSE_VISIBILITY_CLOSED)
  4801. ) {
  4802. $options[] = 'enter';
  4803. }
  4804. if ($is_admin ||
  4805. $course['visibility'] == COURSE_VISIBILITY_OPEN_WORLD && empty($course['registration_code']) ||
  4806. (api_user_is_login($uid) && $course['visibility'] == COURSE_VISIBILITY_OPEN_PLATFORM && empty($course['registration_code'])) ||
  4807. (in_array($course['real_id'], $user_courses) && $course['visibility'] != COURSE_VISIBILITY_CLOSED)
  4808. ) {
  4809. $options[] = 'enter';
  4810. }
  4811. if ($course['visibility'] != COURSE_VISIBILITY_HIDDEN &&
  4812. empty($course['registration_code']) &&
  4813. $course['unsubscribe'] == UNSUBSCRIBE_ALLOWED &&
  4814. api_user_is_login($uid) &&
  4815. in_array($course['real_id'], $user_courses)
  4816. ) {
  4817. $options[] = 'unsubscribe';
  4818. }
  4819. return $options;
  4820. }
  4821. /**
  4822. * @param array $courseInfo
  4823. * @param array $teachers
  4824. * @param bool $deleteTeachersNotInList
  4825. * @param bool $editTeacherInSessions
  4826. * @param bool $deleteSessionTeacherNotInList
  4827. * @param array $teacherBackup
  4828. * @param Monolog\Logger $logger
  4829. *
  4830. * @return false|null
  4831. */
  4832. public static function updateTeachers(
  4833. $courseInfo,
  4834. $teachers,
  4835. $deleteTeachersNotInList = true,
  4836. $editTeacherInSessions = false,
  4837. $deleteSessionTeacherNotInList = false,
  4838. $teacherBackup = array(),
  4839. $logger = null
  4840. ) {
  4841. if (!is_array($teachers)) {
  4842. $teachers = array($teachers);
  4843. }
  4844. if (empty($courseInfo) || !isset($courseInfo['real_id'])) {
  4845. return false;
  4846. }
  4847. $teachers = array_filter($teachers);
  4848. $courseId = $courseInfo['real_id'];
  4849. $course_code = $courseInfo['code'];
  4850. $course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  4851. $alreadyAddedTeachers = self::get_teacher_list_from_course_code($course_code);
  4852. if ($deleteTeachersNotInList) {
  4853. // Delete only teacher relations that doesn't match the selected teachers
  4854. $cond = null;
  4855. if (count($teachers) > 0) {
  4856. foreach ($teachers as $key) {
  4857. $key = Database::escape_string($key);
  4858. $cond .= " AND user_id <> '".$key."'";
  4859. }
  4860. }
  4861. // Recover user categories
  4862. $sql = 'SELECT * FROM '.$course_user_table.'
  4863. WHERE c_id ="' . $courseId.'" AND status="1" AND relation_type = 0 '.$cond;
  4864. $result = Database::query($sql);
  4865. if (Database::num_rows($result)) {
  4866. $teachersToDelete = Database::store_result($result, 'ASSOC');
  4867. foreach ($teachersToDelete as $data) {
  4868. $userId = $data['user_id'];
  4869. $teacherBackup[$userId][$course_code] = $data;
  4870. }
  4871. }
  4872. $sql = 'DELETE FROM '.$course_user_table.'
  4873. WHERE c_id ="' . $courseId.'" AND status="1" AND relation_type = 0 '.$cond;
  4874. Database::query($sql);
  4875. }
  4876. if (count($teachers) > 0) {
  4877. foreach ($teachers as $userId) {
  4878. $userId = intval($userId);
  4879. // We check if the teacher is already subscribed in this course
  4880. $sql = 'SELECT 1 FROM '.$course_user_table.'
  4881. WHERE user_id = "' . $userId.'" AND c_id = "'.$courseId.'" ';
  4882. $result = Database::query($sql);
  4883. if (Database::num_rows($result)) {
  4884. $sql = 'UPDATE '.$course_user_table.'
  4885. SET status = "1"
  4886. WHERE c_id = "' . $courseId.'" AND user_id = "'.$userId.'" ';
  4887. } else {
  4888. $userCourseCategory = '0';
  4889. if (isset($teacherBackup[$userId]) &&
  4890. isset($teacherBackup[$userId][$course_code])
  4891. ) {
  4892. $courseUserData = $teacherBackup[$userId][$course_code];
  4893. $userCourseCategory = $courseUserData['user_course_cat'];
  4894. if ($logger) {
  4895. $logger->addInfo("Recovering user_course_cat: $userCourseCategory");
  4896. }
  4897. }
  4898. $sql = "INSERT INTO $course_user_table SET
  4899. c_id = $courseId,
  4900. user_id = $userId,
  4901. status = '1',
  4902. is_tutor = '0',
  4903. sort = '0',
  4904. relation_type = '0',
  4905. user_course_cat = '$userCourseCategory'
  4906. ";
  4907. }
  4908. Database::query($sql);
  4909. }
  4910. }
  4911. if ($editTeacherInSessions) {
  4912. $sessions = SessionManager::get_session_by_course($courseId);
  4913. if (!empty($sessions)) {
  4914. if ($logger) {
  4915. $logger->addInfo("Edit teachers in sessions");
  4916. }
  4917. foreach ($sessions as $session) {
  4918. $sessionId = $session['id'];
  4919. // Remove old and add new
  4920. if ($deleteSessionTeacherNotInList) {
  4921. foreach ($teachers as $userId) {
  4922. if ($logger) {
  4923. $logger->addInfo("Set coach #$userId in session #$sessionId of course #$courseId ");
  4924. }
  4925. SessionManager::set_coach_to_course_session(
  4926. $userId,
  4927. $sessionId,
  4928. $courseId
  4929. );
  4930. }
  4931. $teachersToDelete = array();
  4932. if (!empty($alreadyAddedTeachers)) {
  4933. $teachersToDelete = array_diff(array_keys($alreadyAddedTeachers), $teachers);
  4934. }
  4935. if (!empty($teachersToDelete)) {
  4936. foreach ($teachersToDelete as $userId) {
  4937. if ($logger) {
  4938. $logger->addInfo("Delete coach #$userId in session #$sessionId of course #$courseId ");
  4939. }
  4940. SessionManager::set_coach_to_course_session(
  4941. $userId,
  4942. $sessionId,
  4943. $courseId,
  4944. true
  4945. );
  4946. }
  4947. }
  4948. } else {
  4949. // Add new teachers only
  4950. foreach ($teachers as $userId) {
  4951. if ($logger) {
  4952. $logger->addInfo("Add coach #$userId in session #$sessionId of course #$courseId ");
  4953. }
  4954. SessionManager::set_coach_to_course_session(
  4955. $userId,
  4956. $sessionId,
  4957. $courseId
  4958. );
  4959. }
  4960. }
  4961. }
  4962. }
  4963. }
  4964. }
  4965. /**
  4966. * Course available settings variables see c_course_setting table
  4967. * @param AppPlugin $appPlugin
  4968. * @return array
  4969. */
  4970. public static function getCourseSettingVariables(AppPlugin $appPlugin)
  4971. {
  4972. $pluginCourseSettings = $appPlugin->getAllPluginCourseSettings();
  4973. $courseSettings = array(
  4974. // Get allow_learning_path_theme from table
  4975. 'allow_learning_path_theme',
  4976. // Get allow_open_chat_window from table
  4977. 'allow_open_chat_window',
  4978. 'allow_public_certificates',
  4979. // Get allow_user_edit_agenda from table
  4980. 'allow_user_edit_agenda',
  4981. // Get allow_user_edit_announcement from table
  4982. 'allow_user_edit_announcement',
  4983. // Get allow_user_image_forum from table
  4984. 'allow_user_image_forum',
  4985. //Get allow show user list
  4986. 'allow_user_view_user_list',
  4987. // Get course_theme from table
  4988. 'course_theme',
  4989. //Get allow show user list
  4990. 'display_info_advance_inside_homecourse',
  4991. 'documents_default_visibility',
  4992. // Get send_mail_setting (work)from table
  4993. 'email_alert_manager_on_new_doc',
  4994. // Get send_mail_setting (work)from table
  4995. 'email_alert_manager_on_new_quiz',
  4996. // Get send_mail_setting (dropbox) from table
  4997. 'email_alert_on_new_doc_dropbox',
  4998. 'email_alert_students_on_new_homework',
  4999. // Get send_mail_setting (auth)from table
  5000. 'email_alert_to_teacher_on_new_user_in_course',
  5001. 'enable_lp_auto_launch',
  5002. 'pdf_export_watermark_text',
  5003. 'show_system_folders',
  5004. 'exercise_invisible_in_session',
  5005. 'enable_forum_auto_launch',
  5006. 'show_course_in_user_language',
  5007. 'email_to_teachers_on_new_work_feedback'
  5008. );
  5009. if (!empty(ExerciseLib::getScoreModels())) {
  5010. $courseSettings[] = 'score_model_id';
  5011. }
  5012. $allowLPReturnLink = api_get_setting('allow_lp_return_link');
  5013. if ($allowLPReturnLink === 'true') {
  5014. $courseSettings[] = 'lp_return_link';
  5015. }
  5016. if (!empty($pluginCourseSettings)) {
  5017. $courseSettings = array_merge(
  5018. $courseSettings,
  5019. $pluginCourseSettings
  5020. );
  5021. }
  5022. return $courseSettings;
  5023. }
  5024. /**
  5025. * @param AppPlugin $appPlugin
  5026. * @param string $variable
  5027. * @param string|array $value
  5028. * @param int $courseId
  5029. * @return bool
  5030. */
  5031. public static function saveCourseConfigurationSetting(AppPlugin $appPlugin, $variable, $value, $courseId)
  5032. {
  5033. $settingList = self::getCourseSettingVariables($appPlugin);
  5034. if (!in_array($variable, $settingList)) {
  5035. return false;
  5036. }
  5037. $courseSettingTable = Database::get_course_table(TABLE_COURSE_SETTING);
  5038. if (is_array($value)) {
  5039. $value = implode(',', $value);
  5040. }
  5041. if (self::hasCourseSetting($variable, $courseId)) {
  5042. // Update
  5043. Database::update(
  5044. $courseSettingTable,
  5045. array('value' => $value),
  5046. array('variable = ? AND c_id = ?' => array($variable, $courseId))
  5047. );
  5048. } else {
  5049. // Create
  5050. Database::insert(
  5051. $courseSettingTable,
  5052. [
  5053. 'title' => $variable,
  5054. 'value' => $value,
  5055. 'c_id' => $courseId,
  5056. 'variable' => $variable,
  5057. ]
  5058. );
  5059. }
  5060. return true;
  5061. }
  5062. /**
  5063. * Check if course setting exists
  5064. * @param string $variable
  5065. * @param int $courseId
  5066. * @return bool
  5067. */
  5068. public static function hasCourseSetting($variable, $courseId)
  5069. {
  5070. $courseSetting = Database::get_course_table(TABLE_COURSE_SETTING);
  5071. $courseId = intval($courseId);
  5072. $variable = Database::escape_string($variable);
  5073. $sql = "SELECT variable FROM $courseSetting
  5074. WHERE c_id = $courseId AND variable = '$variable'";
  5075. $result = Database::query($sql);
  5076. return Database::num_rows($result) > 0;
  5077. }
  5078. /**
  5079. * Get information from the track_e_course_access table
  5080. * @param int $sessionId
  5081. * @param int $userId
  5082. * @param int $limit
  5083. * @return array
  5084. */
  5085. public static function getCourseAccessPerSessionAndUser($sessionId, $userId, $limit = 0)
  5086. {
  5087. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  5088. $sessionId = intval($sessionId);
  5089. $userId = intval($userId);
  5090. $sql = "SELECT * FROM $table
  5091. WHERE session_id = $sessionId AND user_id = $userId";
  5092. if (!empty($limit)) {
  5093. $limit = intval($limit);
  5094. $sql .= " LIMIT $limit";
  5095. }
  5096. $result = Database::query($sql);
  5097. return Database::store_result($result);
  5098. }
  5099. /**
  5100. * Get information from the track_e_course_access table
  5101. * @param int $courseId
  5102. * @param int $sessionId
  5103. * @param string $startDate
  5104. * @param string $endDate
  5105. * @return array
  5106. */
  5107. public static function getCourseAccessPerCourseAndSession(
  5108. $courseId,
  5109. $sessionId,
  5110. $startDate,
  5111. $endDate
  5112. ) {
  5113. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  5114. $courseId = intval($courseId);
  5115. $sessionId = intval($sessionId);
  5116. $startDate = Database::escape_string($startDate);
  5117. $endDate = Database::escape_string($endDate);
  5118. $sql = "SELECT * FROM $table
  5119. WHERE
  5120. c_id = $courseId AND
  5121. session_id = $sessionId AND
  5122. login_course_date BETWEEN '$startDate' AND '$endDate'
  5123. ";
  5124. $result = Database::query($sql);
  5125. return Database::store_result($result);
  5126. }
  5127. /**
  5128. * Get login information from the track_e_course_access table, for any
  5129. * course in the given session
  5130. * @param int $sessionId
  5131. * @param int $userId
  5132. * @return array
  5133. */
  5134. public static function getFirstCourseAccessPerSessionAndUser($sessionId, $userId)
  5135. {
  5136. $sessionId = intval($sessionId);
  5137. $userId = intval($userId);
  5138. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  5139. $sql = "SELECT * FROM $table
  5140. WHERE session_id = $sessionId AND user_id = $userId
  5141. ORDER BY login_course_date ASC
  5142. LIMIT 1";
  5143. $result = Database::query($sql);
  5144. $courseAccess = array();
  5145. if (Database::num_rows($result)) {
  5146. $courseAccess = Database::fetch_array($result, 'ASSOC');
  5147. }
  5148. return $courseAccess;
  5149. }
  5150. /**
  5151. * @param int $courseId
  5152. * @param int $sessionId
  5153. * @param bool $getAllSessions
  5154. * @return mixed
  5155. */
  5156. public static function getCountForum(
  5157. $courseId,
  5158. $sessionId = 0,
  5159. $getAllSessions = false
  5160. ) {
  5161. $forum = Database::get_course_table(TABLE_FORUM);
  5162. if ($getAllSessions) {
  5163. $sql = "SELECT count(*) as count
  5164. FROM $forum f
  5165. WHERE f.c_id = %s";
  5166. } else {
  5167. $sql = "SELECT count(*) as count
  5168. FROM $forum f
  5169. WHERE f.c_id = %s and f.session_id = %s";
  5170. }
  5171. $sql = sprintf($sql, intval($courseId), intval($sessionId));
  5172. $result = Database::query($sql);
  5173. $row = Database::fetch_array($result);
  5174. return $row['count'];
  5175. }
  5176. /**
  5177. * @param int $userId
  5178. * @param int $courseId
  5179. * @param int $sessionId
  5180. * @return mixed
  5181. */
  5182. public static function getCountPostInForumPerUser(
  5183. $userId,
  5184. $courseId,
  5185. $sessionId = 0
  5186. ) {
  5187. $forum = Database::get_course_table(TABLE_FORUM);
  5188. $forum_post = Database::get_course_table(TABLE_FORUM_POST);
  5189. $sql = "SELECT count(distinct post_id) as count
  5190. FROM $forum_post p
  5191. INNER JOIN $forum f
  5192. ON f.forum_id = p.forum_id AND f.c_id = p.c_id
  5193. WHERE p.poster_id = %s and f.session_id = %s and p.c_id = %s";
  5194. $sql = sprintf(
  5195. $sql,
  5196. intval($userId),
  5197. intval($sessionId),
  5198. intval($courseId)
  5199. );
  5200. $result = Database::query($sql);
  5201. $row = Database::fetch_array($result);
  5202. return $row['count'];
  5203. }
  5204. /**
  5205. * @param int $userId
  5206. * @param int $courseId
  5207. * @param int $sessionId
  5208. * @return mixed
  5209. */
  5210. public static function getCountForumPerUser(
  5211. $userId,
  5212. $courseId,
  5213. $sessionId = 0
  5214. ) {
  5215. $forum = Database::get_course_table(TABLE_FORUM);
  5216. $forum_post = Database::get_course_table(TABLE_FORUM_POST);
  5217. $sql = "SELECT count(distinct f.forum_id) as count
  5218. FROM $forum_post p
  5219. INNER JOIN $forum f
  5220. ON f.forum_id = p.forum_id AND f.c_id = p.c_id
  5221. WHERE p.poster_id = %s and f.session_id = %s and p.c_id = %s";
  5222. $sql = sprintf(
  5223. $sql,
  5224. intval($userId),
  5225. intval($sessionId),
  5226. intval($courseId)
  5227. );
  5228. $result = Database::query($sql);
  5229. $row = Database::fetch_array($result);
  5230. return $row['count'];
  5231. }
  5232. /**
  5233. * Returns the course name from a given code
  5234. * @param string $code
  5235. * @return string
  5236. */
  5237. public static function getCourseNameFromCode($code)
  5238. {
  5239. $tbl_main_categories = Database::get_main_table(TABLE_MAIN_COURSE);
  5240. $sql = 'SELECT title
  5241. FROM ' . $tbl_main_categories.'
  5242. WHERE code = "' . Database::escape_string($code).'"';
  5243. $result = Database::query($sql);
  5244. if ($col = Database::fetch_array($result)) {
  5245. return $col['title'];
  5246. }
  5247. }
  5248. /**
  5249. * Generates a course code from a course title
  5250. * @todo Such a function might be useful in other places too. It might be moved in the CourseManager class.
  5251. * @todo the function might be upgraded for avoiding code duplications (currently,
  5252. * it might suggest a code that is already in use)
  5253. * @param string $title A course title
  5254. * @return string A proposed course code
  5255. * +
  5256. * @assert (null,null) === false
  5257. * @assert ('ABC_DEF', null) === 'ABCDEF'
  5258. * @assert ('ABC09*^[%A', null) === 'ABC09A'
  5259. */
  5260. public static function generate_course_code($title)
  5261. {
  5262. return substr(
  5263. preg_replace('/[^A-Z0-9]/', '', strtoupper(api_replace_dangerous_char($title))),
  5264. 0,
  5265. self::MAX_COURSE_LENGTH_CODE
  5266. );
  5267. }
  5268. /**
  5269. * @param $courseId
  5270. * @return array
  5271. */
  5272. public static function getCourseSettings($courseId)
  5273. {
  5274. $settingTable = Database::get_course_table(TABLE_COURSE_SETTING);
  5275. $courseId = intval($courseId);
  5276. $sql = "SELECT * FROM $settingTable WHERE c_id = $courseId";
  5277. $result = Database::query($sql);
  5278. $settings = array();
  5279. if (Database::num_rows($result)) {
  5280. while ($row = Database::fetch_array($result, 'ASSOC')) {
  5281. $settings[$row['variable']] = $row;
  5282. }
  5283. }
  5284. return $settings;
  5285. }
  5286. /**
  5287. * this function gets all the users of the course,
  5288. * including users from linked courses
  5289. * @param $filterByActive
  5290. *
  5291. * @return array
  5292. */
  5293. public static function getCourseUsers($filterByActive = null)
  5294. {
  5295. // This would return only the users from real courses:
  5296. $userList = self::get_user_list_from_course_code(
  5297. api_get_course_id(),
  5298. api_get_session_id(),
  5299. null,
  5300. null,
  5301. null,
  5302. null,
  5303. false,
  5304. false,
  5305. [],
  5306. [],
  5307. [],
  5308. $filterByActive
  5309. );
  5310. return $userList;
  5311. }
  5312. /**
  5313. * this function gets all the groups of the course,
  5314. * not including linked courses
  5315. */
  5316. public static function getCourseGroups()
  5317. {
  5318. $session_id = api_get_session_id();
  5319. if ($session_id != 0) {
  5320. $new_group_list = self::get_group_list_of_course(
  5321. api_get_course_id(),
  5322. $session_id,
  5323. 1
  5324. );
  5325. } else {
  5326. $new_group_list = self::get_group_list_of_course(
  5327. api_get_course_id(),
  5328. 0,
  5329. 1
  5330. );
  5331. }
  5332. return $new_group_list;
  5333. }
  5334. /**
  5335. * @param FormValidator $form
  5336. * @param array $to_already_selected
  5337. *
  5338. * @return HTML_QuickForm_element
  5339. */
  5340. public static function addUserGroupMultiSelect(&$form, $to_already_selected)
  5341. {
  5342. $userList = self::getCourseUsers(true);
  5343. $group_list = self::getCourseGroups();
  5344. $array = self::buildSelectOptions(
  5345. $group_list,
  5346. $userList,
  5347. $to_already_selected
  5348. );
  5349. $result = array();
  5350. foreach ($array as $content) {
  5351. $result[$content['value']] = $content['content'];
  5352. }
  5353. return $form->addElement(
  5354. 'advmultiselect',
  5355. 'users',
  5356. get_lang('Users'),
  5357. $result,
  5358. array('select_all_checkbox' => true)
  5359. );
  5360. }
  5361. /**
  5362. * This function separates the users from the groups
  5363. * users have a value USER:XXX (with XXX the groups id have a value
  5364. * GROUP:YYY (with YYY the group id)
  5365. * @param array $to Array of strings that define the type and id of each destination
  5366. * @return array Array of groups and users (each an array of IDs)
  5367. */
  5368. public static function separateUsersGroups($to)
  5369. {
  5370. $groupList = array();
  5371. $userList = array();
  5372. foreach ($to as $to_item) {
  5373. if (!empty($to_item)) {
  5374. $parts = explode(':', $to_item);
  5375. $type = isset($parts[0]) ? $parts[0] : '';
  5376. $id = isset($parts[1]) ? $parts[1] : '';
  5377. switch ($type) {
  5378. case 'GROUP':
  5379. $groupList[] = intval($id);
  5380. break;
  5381. case 'USER':
  5382. $userList[] = intval($id);
  5383. break;
  5384. }
  5385. }
  5386. }
  5387. $send_to['groups'] = $groupList;
  5388. $send_to['users'] = $userList;
  5389. return $send_to;
  5390. }
  5391. /**
  5392. * Shows the form for sending a message to a specific group or user.
  5393. * @param FormValidator $form
  5394. * @param array $groupInfo
  5395. * @param array $to
  5396. *
  5397. * @return HTML_QuickForm_element
  5398. */
  5399. public static function addGroupMultiSelect($form, $groupInfo, $to = [])
  5400. {
  5401. $group_users = GroupManager::get_subscribed_users($groupInfo);
  5402. $array = self::buildSelectOptions(null, $group_users, $to);
  5403. $result = array();
  5404. foreach ($array as $content) {
  5405. $result[$content['value']] = $content['content'];
  5406. }
  5407. return $form->addElement('advmultiselect', 'users', get_lang('Users'), $result);
  5408. }
  5409. /**
  5410. * this function shows the form for sending a message to a specific group or user.
  5411. * @param array $group_list
  5412. * @param array $userList
  5413. * @param array $to_already_selected
  5414. * @return array
  5415. */
  5416. public static function buildSelectOptions(
  5417. $group_list = array(),
  5418. $userList = array(),
  5419. $to_already_selected = array()
  5420. ) {
  5421. if (empty($to_already_selected)) {
  5422. $to_already_selected = array();
  5423. }
  5424. $result = array();
  5425. // adding the groups to the select form
  5426. if ($group_list) {
  5427. foreach ($group_list as $this_group) {
  5428. if (is_array($to_already_selected)) {
  5429. if (!in_array(
  5430. "GROUP:".$this_group['id'],
  5431. $to_already_selected
  5432. )
  5433. ) { // $to_already_selected is the array containing the groups (and users) that are already selected
  5434. $user_label = ($this_group['userNb'] > 0) ? get_lang('Users') : get_lang('LowerCaseUser');
  5435. $user_disabled = ($this_group['userNb'] > 0) ? "" : "disabled=disabled";
  5436. $result[] = array(
  5437. 'disabled' => $user_disabled,
  5438. 'value' => "GROUP:".$this_group['id'],
  5439. 'content' => "G: ".$this_group['name']." - ".$this_group['userNb']." ".$user_label
  5440. );
  5441. }
  5442. }
  5443. }
  5444. }
  5445. // adding the individual users to the select form
  5446. if ($userList) {
  5447. foreach ($userList as $user) {
  5448. if (is_array($to_already_selected)) {
  5449. if (!in_array(
  5450. "USER:".$user['user_id'],
  5451. $to_already_selected
  5452. )
  5453. ) { // $to_already_selected is the array containing the users (and groups) that are already selected
  5454. $result[] = array(
  5455. 'value' => "USER:".$user['user_id'],
  5456. 'content' => api_get_person_name($user['firstname'], $user['lastname'])
  5457. );
  5458. }
  5459. }
  5460. }
  5461. }
  5462. return $result;
  5463. }
  5464. /**
  5465. * @return array a list (array) of all courses.
  5466. */
  5467. public static function get_course_list()
  5468. {
  5469. $table = Database::get_main_table(TABLE_MAIN_COURSE);
  5470. return Database::store_result(Database::query("SELECT *, id as real_id FROM $table"));
  5471. }
  5472. /**
  5473. * Returns course code from a given gradebook category's id
  5474. * @param int Category ID
  5475. * @return string Course code
  5476. */
  5477. public static function get_course_by_category($category_id)
  5478. {
  5479. $category_id = intval($category_id);
  5480. $info = Database::fetch_array(
  5481. Database::query('SELECT c_id FROM ' . Database::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY) . '
  5482. WHERE id=' . $category_id), 'ASSOC'
  5483. );
  5484. return $info ? $info['c_id'] : false;
  5485. }
  5486. /**
  5487. * This function gets all the courses that are not in a session
  5488. * @param date Start date
  5489. * @param date End date
  5490. * @param bool $includeClosed Whether to include closed and hidden courses
  5491. * @return array Not-in-session courses
  5492. */
  5493. public static function getCoursesWithoutSession(
  5494. $startDate = null,
  5495. $endDate = null,
  5496. $includeClosed = false
  5497. ) {
  5498. $dateConditional = ($startDate && $endDate) ?
  5499. " WHERE session_id IN (SELECT id FROM ".Database::get_main_table(TABLE_MAIN_SESSION).
  5500. " WHERE access_start_date = '$startDate' AND access_end_date = '$endDate')" : null;
  5501. $visibility = ($includeClosed ? '' : 'visibility NOT IN (0, 4) AND ');
  5502. $sql = "SELECT id, code, title
  5503. FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  5504. WHERE $visibility code NOT IN (
  5505. SELECT DISTINCT course_code
  5506. FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE).$dateConditional."
  5507. )
  5508. ORDER BY id";
  5509. $result = Database::query($sql);
  5510. $courses = array();
  5511. while ($row = Database::fetch_array($result)) {
  5512. $courses[] = $row;
  5513. }
  5514. return $courses;
  5515. }
  5516. /**
  5517. * Get list of courses based on users of a group for a group admin
  5518. * @param int $userId The user id
  5519. * @return array
  5520. */
  5521. public static function getCoursesFollowedByGroupAdmin($userId)
  5522. {
  5523. $coursesList = [];
  5524. $courseTable = Database::get_main_table(TABLE_MAIN_COURSE);
  5525. $courseUserTable = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  5526. $userGroup = new UserGroup();
  5527. $userIdList = $userGroup->getGroupUsersByUser($userId);
  5528. if (empty($userIdList)) {
  5529. return [];
  5530. }
  5531. $sql = "SELECT DISTINCT(c.id), c.title
  5532. FROM $courseTable c
  5533. INNER JOIN $courseUserTable cru ON c.id = cru.c_id
  5534. WHERE (
  5535. cru.user_id IN (".implode(', ', $userIdList).")
  5536. AND cru.relation_type = 0
  5537. )";
  5538. if (api_is_multiple_url_enabled()) {
  5539. $courseAccessUrlTable = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  5540. $accessUrlId = api_get_current_access_url_id();
  5541. if ($accessUrlId != -1) {
  5542. $sql = "SELECT DISTINCT(c.id), c.title
  5543. FROM $courseTable c
  5544. INNER JOIN $courseUserTable cru ON c.id = cru.c_id
  5545. INNER JOIN $courseAccessUrlTable crau ON c.id = crau.c_id
  5546. WHERE crau.access_url_id = $accessUrlId
  5547. AND (
  5548. cru.id_user IN (".implode(', ', $userIdList).") AND
  5549. cru.relation_type = 0
  5550. )";
  5551. }
  5552. }
  5553. $result = Database::query($sql);
  5554. while ($row = Database::fetch_assoc($result)) {
  5555. $coursesList[] = $row;
  5556. }
  5557. return $coursesList;
  5558. }
  5559. /**
  5560. * Direct course link see #5299
  5561. *
  5562. * You can send to your students an URL like this
  5563. * http://chamilodev.beeznest.com/main/auth/inscription.php?c=ABC&e=3
  5564. * Where "c" is the course code and "e" is the exercise Id, after a successful
  5565. * registration the user will be sent to the course or exercise
  5566. *
  5567. */
  5568. public static function redirectToCourse($form_data)
  5569. {
  5570. $course_code_redirect = Session::read('course_redirect');
  5571. $_user = api_get_user_info();
  5572. $user_id = api_get_user_id();
  5573. if (!empty($course_code_redirect)) {
  5574. $course_info = api_get_course_info($course_code_redirect);
  5575. if (!empty($course_info)) {
  5576. if (in_array($course_info['visibility'],
  5577. array(COURSE_VISIBILITY_OPEN_PLATFORM, COURSE_VISIBILITY_OPEN_WORLD))
  5578. ) {
  5579. if (self::is_user_subscribed_in_course($user_id, $course_info['code'])) {
  5580. $form_data['action'] = $course_info['course_public_url'];
  5581. $form_data['message'] = sprintf(get_lang('YouHaveBeenRegisteredToCourseX'), $course_info['title']);
  5582. $form_data['button'] = Display::button(
  5583. 'next',
  5584. get_lang('GoToCourse', null, $_user['language']),
  5585. array('class' => 'btn btn-primary btn-large')
  5586. );
  5587. $exercise_redirect = intval(Session::read('exercise_redirect'));
  5588. // Specify the course id as the current context does not
  5589. // hold a global $_course array
  5590. $objExercise = new Exercise($course_info['real_id']);
  5591. $result = $objExercise->read($exercise_redirect);
  5592. if (!empty($exercise_redirect) && !empty($result)) {
  5593. $form_data['action'] = api_get_path(WEB_CODE_PATH).'exercise/overview.php?exerciseId='.$exercise_redirect.'&cidReq='.$course_info['code'];
  5594. $form_data['message'] .= '<br />'.get_lang('YouCanAccessTheExercise');
  5595. $form_data['button'] = Display::button(
  5596. 'next',
  5597. get_lang('Go', null, $_user['language']),
  5598. array('class' => 'btn btn-primary btn-large')
  5599. );
  5600. }
  5601. if (!empty($form_data['action'])) {
  5602. header('Location: '.$form_data['action']);
  5603. exit;
  5604. }
  5605. }
  5606. }
  5607. }
  5608. }
  5609. return $form_data;
  5610. }
  5611. /**
  5612. * return html code for displaying a course title in the standard view (not the Session view)
  5613. * @param $courseId
  5614. * @param bool $loadDirs
  5615. * @return string
  5616. */
  5617. public static function displayCourseHtml($courseId, $loadDirs = false)
  5618. {
  5619. $params = self::getCourseParamsForDisplay($courseId, $loadDirs);
  5620. $html = self::course_item_html($params, false);
  5621. return $html;
  5622. }
  5623. /**
  5624. * Return tab of params to display a course title in the My Courses tab
  5625. * Check visibility, right, and notification icons, and load_dirs option
  5626. * // get html course params
  5627. * //
  5628. * @param $courseId
  5629. * @param bool $loadDirs
  5630. * @return array with keys ['right_actions'] ['teachers'] ['notifications']
  5631. */
  5632. public static function getCourseParamsForDisplay($courseId, $loadDirs = false)
  5633. {
  5634. $user_id = api_get_user_id();
  5635. // Table definitions
  5636. $TABLECOURS = Database::get_main_table(TABLE_MAIN_COURSE);
  5637. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  5638. $TABLE_ACCESS_URL_REL_COURSE = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  5639. $current_url_id = api_get_current_access_url_id();
  5640. // Get course list auto-register
  5641. $special_course_list = self::get_special_course_list();
  5642. $without_special_courses = '';
  5643. if (!empty($special_course_list)) {
  5644. $without_special_courses = ' AND course.id NOT IN ("'.implode('","', $special_course_list).'")';
  5645. }
  5646. //AND course_rel_user.relation_type<>".COURSE_RELATION_TYPE_RRHH."
  5647. $sql = "SELECT
  5648. course.id,
  5649. course.title,
  5650. course.code,
  5651. course.subscribe subscr,
  5652. course.unsubscribe unsubscr,
  5653. course_rel_user.status status,
  5654. course_rel_user.sort sort,
  5655. course_rel_user.user_course_cat user_course_cat
  5656. FROM
  5657. $TABLECOURS course
  5658. INNER JOIN $TABLECOURSUSER course_rel_user
  5659. ON (course.id = course_rel_user.c_id)
  5660. INNER JOIN $TABLE_ACCESS_URL_REL_COURSE url
  5661. ON (url.c_id = course.id)
  5662. WHERE
  5663. course.id=".intval($courseId)." AND
  5664. course_rel_user.user_id = ".intval($user_id)."
  5665. $without_special_courses
  5666. ";
  5667. // If multiple URL access mode is enabled, only fetch courses
  5668. // corresponding to the current URL.
  5669. if (api_get_multiple_access_url() && $current_url_id != -1) {
  5670. $sql .= " AND url.c_id = course.id AND access_url_id=".intval($current_url_id);
  5671. }
  5672. // Use user's classification for courses (if any).
  5673. $sql .= " ORDER BY course_rel_user.user_course_cat, course_rel_user.sort ASC";
  5674. $result = Database::query($sql);
  5675. // Browse through all courses. We can only have one course because
  5676. // of the course.id=".intval($courseId) in sql query
  5677. $course = Database::fetch_array($result);
  5678. $course_info = api_get_course_info_by_id($courseId);
  5679. if (empty($course_info)) {
  5680. return '';
  5681. }
  5682. //$course['id_session'] = null;
  5683. $course_info['id_session'] = null;
  5684. $course_info['status'] = $course['status'];
  5685. // For each course, get if there is any notification icon to show
  5686. // (something that would have changed since the user's last visit).
  5687. $show_notification = !api_get_configuration_value('hide_course_notification')
  5688. ? Display::show_notification($course_info)
  5689. : '';
  5690. // New code displaying the user's status in respect to this course.
  5691. $status_icon = Display::return_icon(
  5692. 'blackboard.png',
  5693. $course_info['title'],
  5694. array(),
  5695. ICON_SIZE_LARGE
  5696. );
  5697. $params = array();
  5698. $params['right_actions'] = '';
  5699. if (api_is_platform_admin()) {
  5700. if ($loadDirs) {
  5701. $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>';
  5702. $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>';
  5703. $params['right_actions'] .= Display::div(
  5704. '',
  5705. array(
  5706. 'id' => 'document_result_'.$course_info['real_id'].'_0',
  5707. 'class' => 'document_preview_container',
  5708. )
  5709. );
  5710. } else {
  5711. $params['right_actions'] .= '<a class="btn btn-default btn-sm" title="'.get_lang('Edit').'" href="'.api_get_path(WEB_CODE_PATH).'course_info/infocours.php?cidReq='.$course['code'].'">'.Display::returnFontAwesomeIcon('pencil').'</a>';
  5712. }
  5713. if ($course_info['status'] == COURSEMANAGER) {
  5714. //echo Display::return_icon('teachers.gif', get_lang('Status').': '.get_lang('Teacher'), array('style'=>'width: 11px; height: 11px;'));
  5715. }
  5716. } else {
  5717. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED) {
  5718. if ($loadDirs) {
  5719. $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>';
  5720. $params['right_actions'] .= Display::div(
  5721. '',
  5722. array(
  5723. 'id' => 'document_result_'.$course_info['real_id'].'_0',
  5724. 'class' => 'document_preview_container',
  5725. )
  5726. );
  5727. } else {
  5728. if ($course_info['status'] == COURSEMANAGER) {
  5729. $params['right_actions'] .= '<a class="btn btn-default btn-sm" title="'.get_lang('Edit').'" href="'.api_get_path(WEB_CODE_PATH).'course_info/infocours.php?cidReq='.$course['code'].'">'.Display::returnFontAwesomeIcon('pencil').'</a>';
  5730. }
  5731. }
  5732. }
  5733. }
  5734. $course_title_url = '';
  5735. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED || $course['status'] == COURSEMANAGER) {
  5736. $course_title_url = api_get_path(WEB_COURSE_PATH).$course_info['path'].'/?id_session=0';
  5737. $course_title = Display::url($course_info['title'], $course_title_url);
  5738. } else {
  5739. $course_title = $course_info['title'].' '.Display::tag(
  5740. 'span',
  5741. get_lang('CourseClosed'),
  5742. array('class' => 'item_closed')
  5743. );
  5744. }
  5745. // Start displaying the course block itself
  5746. if (api_get_setting('display_coursecode_in_courselist') === 'true') {
  5747. $course_title .= ' ('.$course_info['visual_code'].') ';
  5748. }
  5749. $teachers = '';
  5750. if (api_get_setting('display_teacher_in_courselist') === 'true') {
  5751. $teachers = self::getTeacherListFromCourseCodeToString(
  5752. $course['code'],
  5753. self::USER_SEPARATOR,
  5754. true
  5755. );
  5756. }
  5757. $params['link'] = $course_title_url;
  5758. $params['icon'] = $status_icon;
  5759. $params['title'] = $course_title;
  5760. $params['teachers'] = $teachers;
  5761. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED) {
  5762. $params['notifications'] = $show_notification;
  5763. }
  5764. return $params;
  5765. }
  5766. /**
  5767. * Get the course id based on the original id and field name in the extra fields.
  5768. * Returns 0 if course was not found
  5769. *
  5770. * @param string $original_course_id_value Original course id
  5771. * @param string $original_course_id_name Original field name
  5772. * @return int Course id
  5773. */
  5774. public static function get_course_id_from_original_id($original_course_id_value, $original_course_id_name)
  5775. {
  5776. $extraFieldValue = new ExtraFieldValue('course');
  5777. $value = $extraFieldValue->get_item_id_from_field_variable_and_field_value(
  5778. $original_course_id_name,
  5779. $original_course_id_value
  5780. );
  5781. if ($value) {
  5782. return $value['item_id'];
  5783. }
  5784. return 0;
  5785. }
  5786. /**
  5787. * Helper function to create a default gradebook (if necessary) upon course creation
  5788. * @param int $modelId The gradebook model ID
  5789. * @param string $courseCode Course code
  5790. * @return void
  5791. */
  5792. public static function createDefaultGradebook($modelId, $courseId)
  5793. {
  5794. if (api_get_setting('gradebook_enable_grade_model') === 'true') {
  5795. //Create gradebook_category for the new course and add
  5796. // a gradebook model for the course
  5797. if (isset($modelId) &&
  5798. !empty($modelId) &&
  5799. $modelId != '-1'
  5800. ) {
  5801. GradebookUtils::createDefaultCourseGradebook(
  5802. $courseId,
  5803. $modelId
  5804. );
  5805. }
  5806. }
  5807. }
  5808. /**
  5809. * Helper function to check if there is a course template and, if so, to
  5810. * copy the template as basis for the new course
  5811. * @param string $courseCode Course code
  5812. * @param int $courseTemplate 0 if no course template is defined
  5813. */
  5814. public static function useTemplateAsBasisIfRequired($courseCode, $courseTemplate)
  5815. {
  5816. $template = api_get_setting('course_creation_use_template');
  5817. $teacherCanSelectCourseTemplate = api_get_setting('teacher_can_select_course_template') === 'true';
  5818. $courseTemplate = isset($courseTemplate) ? intval($courseTemplate) : 0;
  5819. $useTemplate = false;
  5820. if ($teacherCanSelectCourseTemplate && $courseTemplate) {
  5821. $useTemplate = true;
  5822. $originCourse = api_get_course_info_by_id($courseTemplate);
  5823. } elseif (!empty($template)) {
  5824. $useTemplate = true;
  5825. $originCourse = api_get_course_info_by_id($template);
  5826. }
  5827. if ($useTemplate) {
  5828. // Include the necessary libraries to generate a course copy
  5829. // Call the course copy object
  5830. $originCourse['official_code'] = $originCourse['code'];
  5831. $cb = new CourseBuilder(null, $originCourse);
  5832. $course = $cb->build(null, $originCourse['code']);
  5833. $cr = new CourseRestorer($course);
  5834. $cr->set_file_option();
  5835. $cr->restore($courseCode);
  5836. }
  5837. }
  5838. /**
  5839. * Helper method to get the number of users defined with a specific course extra field
  5840. * @param string $name Field title
  5841. * @param string $tableExtraFields The extra fields table name
  5842. * @param string $tableUserFieldValues The user extra field value table name
  5843. * @return int The number of users with this extra field with a specific value
  5844. */
  5845. public static function getCountRegisteredUsersWithCourseExtraField(
  5846. $name,
  5847. $tableExtraFields = '',
  5848. $tableUserFieldValues = ''
  5849. ) {
  5850. if (empty($tableExtraFields)) {
  5851. $tableExtraFields = Database::get_main_table(TABLE_EXTRA_FIELD);
  5852. }
  5853. if (empty($tableUserFieldValues)) {
  5854. $tableUserFieldValues = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
  5855. }
  5856. $registered_users_with_extra_field = 0;
  5857. if (!empty($name) && $name != '-') {
  5858. $extraFieldType = EntityExtraField::COURSE_FIELD_TYPE;
  5859. $name = Database::escape_string($name);
  5860. $sql = "SELECT count(v.item_id) as count
  5861. FROM $tableUserFieldValues v
  5862. INNER JOIN $tableExtraFields f
  5863. ON (f.id = v.field_id)
  5864. WHERE value = '$name' AND extra_field_type = $extraFieldType";
  5865. $result_count = Database::query($sql);
  5866. if (Database::num_rows($result_count)) {
  5867. $row_count = Database::fetch_array($result_count);
  5868. $registered_users_with_extra_field = $row_count['count'];
  5869. }
  5870. }
  5871. return $registered_users_with_extra_field;
  5872. }
  5873. /**
  5874. * Check if a specific access-url-related setting is a problem or not
  5875. * @param array $_configuration The $_configuration array
  5876. * @param int $accessUrlId The access URL ID
  5877. * @param string $param
  5878. * @param string $msgLabel
  5879. * @return bool|string
  5880. */
  5881. private static function checkCreateCourseAccessUrlParam($_configuration, $accessUrlId, $param, $msgLabel)
  5882. {
  5883. if (isset($_configuration[$accessUrlId][$param]) && $_configuration[$accessUrlId][$param] > 0) {
  5884. $num = self::count_courses($accessUrlId);
  5885. if ($num >= $_configuration[$accessUrlId][$param]) {
  5886. api_warn_hosting_contact($param);
  5887. Display::addFlash(
  5888. Display::return_message($msgLabel)
  5889. );
  5890. }
  5891. }
  5892. return false;
  5893. }
  5894. /**
  5895. * @param int $user_id
  5896. * @param $filter
  5897. * @param bool $load_dirs
  5898. * @param int $getCount
  5899. * @param int $start
  5900. * @param null $maxPerPage
  5901. * @return null|string
  5902. */
  5903. public static function displayCourses(
  5904. $user_id,
  5905. $filter,
  5906. $load_dirs,
  5907. $getCount,
  5908. $start = null,
  5909. $maxPerPage = null
  5910. ) {
  5911. // Table definitions
  5912. $TABLECOURS = Database:: get_main_table(TABLE_MAIN_COURSE);
  5913. $TABLECOURSUSER = Database:: get_main_table(TABLE_MAIN_COURSE_USER);
  5914. $TABLE_ACCESS_URL_REL_COURSE = Database:: get_main_table(
  5915. TABLE_MAIN_ACCESS_URL_REL_COURSE
  5916. );
  5917. $current_url_id = api_get_current_access_url_id();
  5918. // Get course list auto-register
  5919. $special_course_list = self::get_special_course_list();
  5920. $without_special_courses = '';
  5921. if (!empty($special_course_list)) {
  5922. //$without_special_courses = ' AND course.code NOT IN ("'.implode('","',$special_course_list).'")';
  5923. }
  5924. $select = " SELECT DISTINCT
  5925. course.id,
  5926. course.title,
  5927. course.code,
  5928. course.subscribe subscr,
  5929. course.unsubscribe unsubscr,
  5930. course_rel_user.status status,
  5931. course_rel_user.sort sort,
  5932. course_rel_user.user_course_cat user_course_cat,
  5933. course.id as real_id
  5934. ";
  5935. $from = "$TABLECOURS course, $TABLECOURSUSER course_rel_user, $TABLE_ACCESS_URL_REL_COURSE url ";
  5936. $where = " course.id = course_rel_user.c_id AND
  5937. url.c_id = course.id AND
  5938. course_rel_user.user_id = '".$user_id."' AND
  5939. course_rel_user.user_course_cat = 0
  5940. ";
  5941. $order = " ORDER BY course_rel_user.user_course_cat, course_rel_user.sort ASC";
  5942. if ($getCount) {
  5943. $select = "SELECT count(course.id) as total";
  5944. }
  5945. $sql = "$select FROM $from WHERE $where $without_special_courses ";
  5946. // corresponding to the current URL.
  5947. if (api_get_multiple_access_url() && $current_url_id != -1) {
  5948. $sql .= " AND url.c_id = course.id AND access_url_id='".$current_url_id."'";
  5949. }
  5950. $sql .= $order;
  5951. if (isset($start) && isset($maxPerPage)) {
  5952. $start = intval($start);
  5953. $maxPerPage = intval($maxPerPage);
  5954. $limitCondition = " LIMIT $start, $maxPerPage";
  5955. $sql .= $limitCondition;
  5956. }
  5957. if ($getCount) {
  5958. $result = Database::query($sql);
  5959. $row = Database::fetch_array($result);
  5960. return $row['total'];
  5961. }
  5962. $result = Database::query($sql);
  5963. $html = null;
  5964. $course_list = array();
  5965. // Browse through all courses.
  5966. while ($course = Database::fetch_array($result)) {
  5967. $course_info = api_get_course_info($course['code']);
  5968. $course_info['id_session'] = null;
  5969. $course_info['status'] = $course['status'];
  5970. //In order to avoid doubles
  5971. if (in_array($course_info['real_id'], $course_list)) {
  5972. continue;
  5973. } else {
  5974. $course_list[] = $course_info['real_id'];
  5975. }
  5976. // For each course, get if there is any notification icon to show
  5977. // (something that would have changed since the user's last visit).
  5978. $show_notification = Display:: show_notification($course_info);
  5979. // New code displaying the user's status in respect to this course.
  5980. $status_icon = Display::return_icon(
  5981. 'blackboard.png',
  5982. $course_info['title'],
  5983. array(),
  5984. ICON_SIZE_LARGE
  5985. );
  5986. $params = array();
  5987. $params['right_actions'] = '';
  5988. if (api_is_platform_admin()) {
  5989. if ($load_dirs) {
  5990. $params['right_actions'] .= '<a id="document_preview_'.$course_info['real_id'].'_0" class="document_preview" href="javascript:void(0);">'.Display::return_icon(
  5991. 'folder.png',
  5992. get_lang('Documents'),
  5993. array('align' => 'absmiddle'),
  5994. ICON_SIZE_SMALL
  5995. ).'</a>';
  5996. $params['right_actions'] .= '<a href="'.api_get_path(
  5997. WEB_CODE_PATH
  5998. ).'course_info/infocours.php?cidReq='.$course['code'].'">'.Display::return_icon(
  5999. 'edit.png',
  6000. get_lang('Edit'),
  6001. array('align' => 'absmiddle'),
  6002. ICON_SIZE_SMALL
  6003. ).'</a>';
  6004. $params['right_actions'] .= Display::div(
  6005. '',
  6006. array(
  6007. 'id' => 'document_result_'.$course_info['real_id'].'_0',
  6008. 'class' => 'document_preview_container',
  6009. )
  6010. );
  6011. } else {
  6012. $params['right_actions'] .= '<a href="'.api_get_path(
  6013. WEB_CODE_PATH
  6014. ).'course_info/infocours.php?cidReq='.$course['code'].'">'.Display::return_icon(
  6015. 'edit.png',
  6016. get_lang('Edit'),
  6017. array('align' => 'absmiddle'),
  6018. ICON_SIZE_SMALL
  6019. ).'</a>';
  6020. }
  6021. if ($course_info['status'] == COURSEMANAGER) {
  6022. //echo Display::return_icon('teachers.gif', get_lang('Status').': '.get_lang('Teacher'), array('style'=>'width: 11px; height: 11px;'));
  6023. }
  6024. } else {
  6025. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED) {
  6026. if ($load_dirs) {
  6027. $params['right_actions'] .= '<a id="document_preview_'.$course_info['real_id'].'_0" class="document_preview" href="javascript:void(0);">'.Display::return_icon(
  6028. 'folder.png',
  6029. get_lang('Documents'),
  6030. array('align' => 'absmiddle'),
  6031. ICON_SIZE_SMALL
  6032. ).'</a>';
  6033. $params['right_actions'] .= Display::div(
  6034. '',
  6035. array(
  6036. 'id' => 'document_result_'.$course_info['real_id'].'_0',
  6037. 'class' => 'document_preview_container',
  6038. )
  6039. );
  6040. } else {
  6041. if ($course_info['status'] == COURSEMANAGER) {
  6042. $params['right_actions'] .= '<a href="'.api_get_path(
  6043. WEB_CODE_PATH
  6044. ).'course_info/infocours.php?cidReq='.$course['code'].'">'.Display::return_icon(
  6045. 'edit.png',
  6046. get_lang('Edit'),
  6047. array('align' => 'absmiddle'),
  6048. ICON_SIZE_SMALL
  6049. ).'</a>';
  6050. }
  6051. }
  6052. }
  6053. }
  6054. $course_title_url = '';
  6055. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED || $course['status'] == COURSEMANAGER) {
  6056. //$course_title_url = api_get_path(WEB_COURSE_PATH).$course_info['path'].'/index.php?id_session=0';
  6057. $course_title_url = api_get_path(
  6058. WEB_COURSE_PATH
  6059. ).$course_info['code'].'/index.php?id_session=0';
  6060. $course_title = Display::url(
  6061. $course_info['title'],
  6062. $course_title_url
  6063. );
  6064. } else {
  6065. $course_title = $course_info['title']." ".Display::tag(
  6066. 'span',
  6067. get_lang('CourseClosed'),
  6068. array('class' => 'item_closed')
  6069. );
  6070. }
  6071. // Start displaying the course block itself
  6072. if (api_get_setting(
  6073. 'course.display_coursecode_in_courselist'
  6074. ) == 'true'
  6075. ) {
  6076. $course_title .= ' ('.$course_info['visual_code'].') ';
  6077. }
  6078. $teachers = null;
  6079. if (api_get_setting('course.display_teacher_in_courselist') == 'true'
  6080. ) {
  6081. $teachers = $course_info['teacher_list_formatted'];
  6082. }
  6083. $params['link'] = $course_title_url;
  6084. $params['icon'] = $status_icon;
  6085. $params['title'] = $course_title;
  6086. $params['teachers'] = $teachers;
  6087. if ($course_info['visibility'] != COURSE_VISIBILITY_CLOSED) {
  6088. $params['notifications'] = $show_notification;
  6089. }
  6090. $is_subcontent = true;
  6091. if (empty($user_category_id)) {
  6092. $is_subcontent = false;
  6093. }
  6094. $html .= self::course_item_html($params, $is_subcontent);
  6095. }
  6096. return $html;
  6097. }
  6098. /**
  6099. * Builds the course block in user_portal.php
  6100. * @todo use Twig
  6101. * @param array $params
  6102. * @param bool|false $is_sub_content
  6103. * @return string
  6104. */
  6105. public static function course_item_html($params, $is_sub_content = false)
  6106. {
  6107. $html = '';
  6108. $class = "panel panel-default";
  6109. if ($is_sub_content) {
  6110. $class = "course_item";
  6111. }
  6112. $html .= '<div class="'.$class.'">';
  6113. $html .= '<div class="panel-body">';
  6114. $html .= '<div class="course-items">';
  6115. $html .= ' <div class="row">';
  6116. $html .= '<div class="col-md-2">';
  6117. if (!empty($params['link'])) {
  6118. $html .= '<a class="thumbnail" href="'.$params['link'].'">';
  6119. $html .= $params['icon'];
  6120. $html .= '</a>';
  6121. } else {
  6122. $html .= '<div class="thumbnail">';
  6123. $html .= $params['icon'];
  6124. $html .= '</div>';
  6125. }
  6126. $html .= '</div>';
  6127. $notifications = isset($params['notifications']) ? $params['notifications'] : '';
  6128. $param_class = isset($params['class']) ? $params['class'] : '';
  6129. $params['right_actions'] = isset($params['right_actions']) ? $params['right_actions'] : '';
  6130. $html .= '<div class="col-md-10 '.$param_class.'">';
  6131. $html .= '<div class="pull-right">'.$params['right_actions'].'</div>';
  6132. $html .= '<h4 class="course-items-title">'.$params['title'].$notifications.'</h4> ';
  6133. if (isset($params['show_description'], $params['description']) && $params['show_description'] == 1) {
  6134. $html .= '<p class="description-session">'.$params['description'].'</p>';
  6135. }
  6136. if (!empty($params['subtitle'])) {
  6137. $html .= '<div class="subtitle-session">'.$params['subtitle'].'</div>';
  6138. }
  6139. if (!empty($params['teachers'])) {
  6140. $teachers = $params['teachers'];
  6141. $html .= '<h5 class="course-items-session">'.
  6142. Display::return_icon(
  6143. 'teacher.png',
  6144. get_lang('Teacher'),
  6145. array(),
  6146. ICON_SIZE_TINY
  6147. ).
  6148. $teachers.'</h5>';
  6149. }
  6150. if (!empty($params['coaches'])) {
  6151. $coaches = '';
  6152. if (is_array($params['coaches'])) {
  6153. foreach ($params['coaches'] as $coach) {
  6154. $coaches .= $coach['full_name'];
  6155. }
  6156. }
  6157. $html .= '<h5 class="course-items-session">'.
  6158. Display::return_icon(
  6159. 'teacher.png',
  6160. get_lang('Coach'),
  6161. array(),
  6162. ICON_SIZE_TINY
  6163. ).
  6164. $coaches.
  6165. '</h5>';
  6166. }
  6167. $html .= '</div>';
  6168. $html .= '</div>';
  6169. $html .= '</div>';
  6170. $html .= '</div>';
  6171. $html .= '</div>';
  6172. return $html;
  6173. }
  6174. /**
  6175. * Fill course with all necessary items
  6176. * @param array $courseInfo Course info array
  6177. * @param array $params Parameters from the course creation form
  6178. * @param int $authorId
  6179. * @return void
  6180. */
  6181. private static function fillCourse($courseInfo, $params, $authorId = 0)
  6182. {
  6183. $authorId = empty($authorId) ? api_get_user_id() : (int) $authorId;
  6184. AddCourse::prepare_course_repository($courseInfo['directory'], $courseInfo['code']);
  6185. AddCourse::fill_db_course(
  6186. $courseInfo['real_id'],
  6187. $courseInfo['directory'],
  6188. $courseInfo['course_language'],
  6189. $params['exemplary_content'],
  6190. $authorId
  6191. );
  6192. if (isset($params['gradebook_model_id'])) {
  6193. self::createDefaultGradebook(
  6194. $params['gradebook_model_id'],
  6195. $courseInfo['code']
  6196. );
  6197. }
  6198. // If parameter defined, copy the contents from a specific
  6199. // template course into this new course
  6200. if (isset($params['course_template'])) {
  6201. self::useTemplateAsBasisIfRequired(
  6202. $courseInfo['id'],
  6203. $params['course_template']
  6204. );
  6205. }
  6206. $params['course_code'] = $courseInfo['code'];
  6207. $params['item_id'] = $courseInfo['real_id'];
  6208. $courseFieldValue = new ExtraFieldValue('course');
  6209. $courseFieldValue->saveFieldValues($params);
  6210. }
  6211. /**
  6212. * Get the course categories form a course list
  6213. * @param array $courseList
  6214. * @return array
  6215. */
  6216. public static function getCourseCategoriesFromCourseList(array $courseList)
  6217. {
  6218. $allCategories = array_column($courseList, 'category');
  6219. $categories = array_unique($allCategories);
  6220. sort($categories);
  6221. return $categories;
  6222. }
  6223. /**
  6224. * @param ToolChain $toolList
  6225. */
  6226. public static function setToolList($toolList)
  6227. {
  6228. self::$toolList = $toolList;
  6229. }
  6230. /**
  6231. * @return ToolChain
  6232. */
  6233. public static function getToolList()
  6234. {
  6235. return self::$toolList;
  6236. }
  6237. }