course.lib.php 242 KB

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