extra_field_value.lib.php 41 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. use Chamilo\CoreBundle\Entity\ExtraField as EntityExtraField;
  4. use Chamilo\CoreBundle\Entity\ExtraFieldRelTag;
  5. use Chamilo\CoreBundle\Entity\Tag;
  6. use ChamiloSession as Session;
  7. use Chamilo\CoreBundle\Entity\ExtraFieldValues;
  8. /**
  9. * Class ExtraFieldValue
  10. * Declaration for the ExtraFieldValue class, managing the values in extra
  11. * fields for any data type
  12. *
  13. * @package chamilo.library
  14. *
  15. */
  16. class ExtraFieldValue extends Model
  17. {
  18. public $type = '';
  19. public $columns = array(
  20. 'id',
  21. 'field_id',
  22. 'value',
  23. 'comment',
  24. 'item_id',
  25. 'created_at',
  26. 'updated_at',
  27. );
  28. /** @var ExtraField */
  29. public $extraField;
  30. /**
  31. * Formats the necessary elements for the given datatype
  32. * @param string $type The type of data to which this extra field
  33. * applies (user, course, session, ...)
  34. *
  35. * @assert (-1) === false
  36. */
  37. public function __construct($type)
  38. {
  39. parent::__construct();
  40. $this->type = $type;
  41. $extraField = new ExtraField($this->type);
  42. $this->extraField = $extraField;
  43. $this->table = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
  44. $this->table_handler_field = Database::get_main_table(TABLE_EXTRA_FIELD);
  45. }
  46. /**
  47. * @return ExtraField
  48. */
  49. public function getExtraField()
  50. {
  51. return $this->extraField;
  52. }
  53. /**
  54. * Gets the number of values stored in the table (all fields together)
  55. * for this type of resource
  56. * @return integer Number of rows in the table
  57. * @assert () !== false
  58. */
  59. public function get_count()
  60. {
  61. $em = Database::getManager();
  62. $query = $em->getRepository('ChamiloCoreBundle:ExtraFieldValues')->createQueryBuilder('e');
  63. $query->select('count(e.id)');
  64. $query->where('e.extraFieldType = :type');
  65. $query->setParameter('type', $this->getExtraField()->getExtraFieldType());
  66. return $query->getQuery()->getScalarResult();
  67. }
  68. /**
  69. * Save the extra fields values
  70. * In order to save this function needs a item_id (user id, course id, etc)
  71. * This function is used with $extraField->addElements()
  72. * @param array $params array for the insertion into the *_field_values table
  73. * @param bool $forceSave
  74. * @param bool $showQuery
  75. * @param array $saveOnlyThisFields
  76. * @param array $avoidFields do not insert/modify this field
  77. * @return mixed false on empty params, void otherwise
  78. * @assert (array()) === false
  79. */
  80. public function saveFieldValues(
  81. $params,
  82. $forceSave = false,
  83. $showQuery = false,
  84. $saveOnlyThisFields = [],
  85. $avoidFields = []
  86. ) {
  87. foreach ($params as $key => $value) {
  88. $found = strpos($key, '__persist__');
  89. if ($found === false) {
  90. continue;
  91. }
  92. $tempKey = str_replace('__persist__', '', $key);
  93. if (!isset($params[$tempKey])) {
  94. $params[$tempKey] = array();
  95. }
  96. }
  97. if (empty($params['item_id'])) {
  98. return false;
  99. }
  100. $type = $this->getExtraField()->getExtraFieldType();
  101. $extraField = new ExtraField($this->type);
  102. $extraFields = $extraField->get_all(null, 'option_order');
  103. $resultsExist = [];
  104. // Parse params
  105. foreach ($extraFields as $fieldDetails) {
  106. if ($forceSave === false) {
  107. if ($fieldDetails['visible_to_self'] != 1) {
  108. continue;
  109. }
  110. }
  111. $field_variable = $fieldDetails['variable'];
  112. if (!empty($avoidFields)) {
  113. if (in_array($field_variable, $avoidFields)) {
  114. continue;
  115. }
  116. }
  117. if (!empty($saveOnlyThisFields)) {
  118. if (!in_array($field_variable, $saveOnlyThisFields)) {
  119. continue;
  120. }
  121. }
  122. if (isset($params['extra_'.$field_variable])) {
  123. $value = $params['extra_'.$field_variable];
  124. } else {
  125. $value = '';
  126. }
  127. $resultsExist[$field_variable] = isset($value) && $value !== '' ? true : false;
  128. $extraFieldInfo = $this->getExtraField()->get_handler_field_info_by_field_variable($field_variable);
  129. if (!$extraFieldInfo) {
  130. continue;
  131. }
  132. $commentVariable = 'extra_'.$field_variable.'_comment';
  133. $comment = isset($params[$commentVariable]) ? $params[$commentVariable] : null;
  134. switch ($extraFieldInfo['field_type']) {
  135. case ExtraField::FIELD_TYPE_TAG:
  136. if ($type == EntityExtraField::USER_FIELD_TYPE) {
  137. UserManager::delete_user_tags(
  138. $params['item_id'],
  139. $extraFieldInfo['id']
  140. );
  141. UserManager::process_tags(
  142. $value,
  143. $params['item_id'],
  144. $extraFieldInfo['id']
  145. );
  146. break;
  147. }
  148. $em = Database::getManager();
  149. $currentTags = $em
  150. ->getRepository('ChamiloCoreBundle:ExtraFieldRelTag')
  151. ->findBy([
  152. 'fieldId' => $extraFieldInfo['id'],
  153. 'itemId' => $params['item_id']
  154. ]);
  155. foreach ($currentTags as $extraFieldtag) {
  156. $em->remove($extraFieldtag);
  157. }
  158. $em->flush();
  159. $tagValues = is_array($value) ? $value : [$value];
  160. $tags = [];
  161. foreach ($tagValues as $tagValue) {
  162. $tagsResult = $em
  163. ->getRepository('ChamiloCoreBundle:Tag')
  164. ->findBy([
  165. 'tag' => $tagValue,
  166. 'fieldId' => $extraFieldInfo['id'],
  167. ]);
  168. if (empty($tagsResult)) {
  169. $tag = new Tag();
  170. $tag->setFieldId($extraFieldInfo['id']);
  171. $tag->setTag($tagValue);
  172. $tags[] = $tag;
  173. } else {
  174. $tags = array_merge($tags, $tagsResult);
  175. }
  176. }
  177. foreach ($tags as $tag) {
  178. $tagUses = $em
  179. ->getRepository('ChamiloCoreBundle:ExtraFieldRelTag')
  180. ->findBy([
  181. 'tagId' => $tag->getId()
  182. ]);
  183. $tag->setCount(count($tagUses) + 1);
  184. $em->persist($tag);
  185. }
  186. $em->flush();
  187. foreach ($tags as $tag) {
  188. $fieldRelTag = new ExtraFieldRelTag();
  189. $fieldRelTag->setFieldId($extraFieldInfo['id']);
  190. $fieldRelTag->setItemId($params['item_id']);
  191. $fieldRelTag->setTagId($tag->getId());
  192. $em->persist($fieldRelTag);
  193. }
  194. $em->flush();
  195. break;
  196. case ExtraField::FIELD_TYPE_FILE_IMAGE:
  197. $dirPermissions = api_get_permissions_for_new_directories();
  198. switch ($this->type) {
  199. case 'course':
  200. $fileDir = api_get_path(SYS_UPLOAD_PATH)."courses/";
  201. $fileDirStored = "courses/";
  202. break;
  203. case 'session':
  204. $fileDir = api_get_path(SYS_UPLOAD_PATH)."sessions/";
  205. $fileDirStored = "sessions/";
  206. break;
  207. case 'user':
  208. $fileDir = UserManager::getUserPathById($params['item_id'], 'system');
  209. $fileDirStored = UserManager::getUserPathById($params['item_id'], 'last');
  210. break;
  211. }
  212. $fileName = ExtraField::FIELD_TYPE_FILE_IMAGE."_{$params['item_id']}.png";
  213. if (!file_exists($fileDir)) {
  214. mkdir($fileDir, $dirPermissions, true);
  215. }
  216. if ($value['error'] == 0) {
  217. //Crop the image to adjust 16:9 ratio
  218. $crop = new Image($value['tmp_name']);
  219. $crop->crop($params['extra_'.$field_variable.'_crop_result']);
  220. $imageExtraField = new Image($value['tmp_name']);
  221. $imageExtraField->resize(400);
  222. $imageExtraField->send_image($fileDir.$fileName, -1, 'png');
  223. $newParams = array(
  224. 'item_id' => $params['item_id'],
  225. 'field_id' => $extraFieldInfo['id'],
  226. 'value' => $fileDirStored.$fileName,
  227. 'comment' => $comment
  228. );
  229. self::save($newParams);
  230. }
  231. break;
  232. case ExtraField::FIELD_TYPE_FILE:
  233. $dirPermissions = api_get_permissions_for_new_directories();
  234. switch ($this->type) {
  235. case 'course':
  236. $fileDir = api_get_path(SYS_UPLOAD_PATH)."courses/";
  237. $fileDirStored = "courses/";
  238. break;
  239. case 'session':
  240. $fileDir = api_get_path(SYS_UPLOAD_PATH)."sessions/";
  241. $fileDirStored = "sessions/";
  242. break;
  243. case 'user':
  244. $fileDir = UserManager::getUserPathById($params['item_id'], 'system');
  245. $fileDirStored = UserManager::getUserPathById($params['item_id'], 'last');
  246. break;
  247. }
  248. $cleanedName = api_replace_dangerous_char($value['name']);
  249. $fileName = ExtraField::FIELD_TYPE_FILE."_{$params['item_id']}_$cleanedName";
  250. if (!file_exists($fileDir)) {
  251. mkdir($fileDir, $dirPermissions, true);
  252. }
  253. if ($value['error'] == 0) {
  254. moveUploadedFile($value, $fileDir.$fileName);
  255. $new_params = array(
  256. 'item_id' => $params['item_id'],
  257. 'field_id' => $extraFieldInfo['id'],
  258. 'value' => $fileDirStored.$fileName
  259. );
  260. if ($this->type !== 'session' && $this->type !== 'course') {
  261. $new_params['comment'] = $comment;
  262. }
  263. self::save($new_params);
  264. }
  265. break;
  266. case ExtraField::FIELD_TYPE_CHECKBOX:
  267. $fieldToSave = 0;
  268. if (is_array($value)) {
  269. if (isset($value['extra_'.$field_variable])) {
  270. $fieldToSave = 1;
  271. }
  272. }
  273. $newParams = array(
  274. 'item_id' => $params['item_id'],
  275. 'field_id' => $extraFieldInfo['id'],
  276. 'value' => $fieldToSave,
  277. 'comment' => $comment
  278. );
  279. self::save($newParams);
  280. break;
  281. default:
  282. $newParams = array(
  283. 'item_id' => $params['item_id'],
  284. 'field_id' => $extraFieldInfo['id'],
  285. 'value' => $value,
  286. 'comment' => $comment
  287. );
  288. self::save($newParams, $showQuery);
  289. }
  290. }
  291. // Set user.profile_completed = 1
  292. if ($this->type === 'user') {
  293. if (api_get_setting('show_terms_if_profile_completed') === 'true') {
  294. $justTermResults = [];
  295. foreach ($resultsExist as $term => $value) {
  296. if (strpos($term, 'terms_') !== false) {
  297. $justTermResults[$term] = $value;
  298. }
  299. }
  300. $profileCompleted = 0;
  301. if (!in_array(false, $justTermResults)) {
  302. $profileCompleted = 1;
  303. }
  304. $userId = $params['item_id'];
  305. $table = Database::get_main_table(TABLE_MAIN_USER);
  306. $sql = "UPDATE $table SET profile_completed = $profileCompleted WHERE user_id = $userId";
  307. Database::query($sql);
  308. Session::write('profile_completed_result', $justTermResults);
  309. }
  310. }
  311. }
  312. /**
  313. * Save values in the *_field_values table
  314. * @param array $params Structured array with the values to save
  315. * @param boolean $show_query Whether to show the insert query (passed to the parent save() method)
  316. * @result mixed The result sent from the parent method
  317. * @assert (array()) === false
  318. */
  319. public function save($params, $show_query = false)
  320. {
  321. $extra_field = $this->getExtraField();
  322. // Setting value to insert.
  323. $value = $params['value'];
  324. $value_to_insert = null;
  325. if (is_array($value)) {
  326. $value_to_insert = implode(';', $value);
  327. } else {
  328. $value_to_insert = $value;
  329. }
  330. $params['value'] = $value_to_insert;
  331. // If field id exists
  332. if (isset($params['field_id'])) {
  333. $extraFieldInfo = $extra_field->get($params['field_id']);
  334. } else {
  335. // Try the variable
  336. $extraFieldInfo = $extra_field->get_handler_field_info_by_field_variable(
  337. $params['variable']
  338. );
  339. $params['field_id'] = $extraFieldInfo['id'];
  340. }
  341. if ($extraFieldInfo) {
  342. switch ($extraFieldInfo['field_type']) {
  343. case ExtraField::FIELD_TYPE_RADIO:
  344. case ExtraField::FIELD_TYPE_SELECT:
  345. break;
  346. case ExtraField::FIELD_TYPE_SELECT_MULTIPLE:
  347. //$field_options = $session_field_option->get_field_options_by_field($params['field_id']);
  348. //$params['field_value'] = split(';', $value_to_insert);
  349. /*
  350. if ($field_options) {
  351. $check = false;
  352. foreach ($field_options as $option) {
  353. if (in_array($option['option_value'], $values)) {
  354. $check = true;
  355. break;
  356. }
  357. }
  358. if (!$check) {
  359. return false; //option value not found
  360. }
  361. } else {
  362. return false; //enumerated type but no option found
  363. }*/
  364. break;
  365. case ExtraField::FIELD_TYPE_TEXT:
  366. case ExtraField::FIELD_TYPE_TEXTAREA:
  367. break;
  368. case ExtraField::FIELD_TYPE_DOUBLE_SELECT:
  369. if (is_array($value)) {
  370. if (isset($value['extra_'.$extraFieldInfo['variable']]) &&
  371. isset($value['extra_'.$extraFieldInfo['variable'].'_second'])
  372. ) {
  373. $value_to_insert = $value['extra_'.$extraFieldInfo['variable']].'::'.$value['extra_'.$extraFieldInfo['variable'].'_second'];
  374. } else {
  375. $value_to_insert = null;
  376. }
  377. }
  378. break;
  379. default:
  380. break;
  381. }
  382. if ($extraFieldInfo['field_type'] == ExtraField::FIELD_TYPE_TAG) {
  383. $field_values = self::getAllValuesByItemAndFieldAndValue(
  384. $params['item_id'],
  385. $params['field_id'],
  386. $value
  387. );
  388. } else {
  389. $field_values = self::get_values_by_handler_and_field_id(
  390. $params['item_id'],
  391. $params['field_id']
  392. );
  393. }
  394. $params['value'] = $value_to_insert;
  395. $params['author_id'] = api_get_user_id();
  396. // Insert
  397. if (empty($field_values)) {
  398. /* Enable this when field_loggeable is introduced as a table field (2.0)
  399. if ($extraFieldInfo['field_loggeable'] == 1) {
  400. */
  401. if (false) {
  402. global $app;
  403. switch ($this->type) {
  404. case 'question':
  405. $extraFieldValue = new ChamiloLMS\Entity\QuestionFieldValues();
  406. $extraFieldValue->setUserId(api_get_user_id());
  407. $extraFieldValue->setQuestionId($params[$this->handler_id]);
  408. break;
  409. case 'course':
  410. $extraFieldValue = new ChamiloLMS\Entity\CourseFieldValues();
  411. $extraFieldValue->setUserId(api_get_user_id());
  412. $extraFieldValue->setQuestionId($params[$this->handler_id]);
  413. break;
  414. case 'user':
  415. $extraFieldValue = new ChamiloLMS\Entity\UserFieldValues();
  416. $extraFieldValue->setUserId($params[$this->handler_id]);
  417. $extraFieldValue->setAuthorId(api_get_user_id());
  418. break;
  419. case 'session':
  420. $extraFieldValue = new ChamiloLMS\Entity\SessionFieldValues();
  421. $extraFieldValue->setUserId(api_get_user_id());
  422. $extraFieldValue->setSessionId($params[$this->handler_id]);
  423. break;
  424. }
  425. if (isset($extraFieldValue)) {
  426. if (!empty($params['value'])) {
  427. $extraFieldValue->setComment($params['comment']);
  428. $extraFieldValue->setFieldValue($params['value']);
  429. $extraFieldValue->setFieldId($params['field_id']);
  430. $extraFieldValue->setTms(api_get_utc_datetime(null, false, true));
  431. $app['orm.ems']['db_write']->persist($extraFieldValue);
  432. $app['orm.ems']['db_write']->flush();
  433. }
  434. }
  435. } else {
  436. if ($extraFieldInfo['field_type'] == ExtraField::FIELD_TYPE_TAG) {
  437. $option = new ExtraFieldOption($this->type);
  438. $optionExists = $option->get($params['value']);
  439. if (empty($optionExists)) {
  440. $optionParams = array(
  441. 'field_id' => $params['field_id'],
  442. 'option_value' => $params['value']
  443. );
  444. $optionId = $option->saveOptions($optionParams);
  445. } else {
  446. $optionId = $optionExists['id'];
  447. }
  448. $params['value'] = $optionId;
  449. if ($optionId) {
  450. return parent::save($params, $show_query);
  451. }
  452. } else {
  453. return parent::save($params, $show_query);
  454. }
  455. }
  456. } else {
  457. // Update
  458. /* Enable this when field_loggeable is introduced as a table field (2.0)
  459. if ($extraFieldInfo['field_loggeable'] == 1) {
  460. */
  461. if (false) {
  462. global $app;
  463. switch ($this->type) {
  464. case 'question':
  465. $extraFieldValue = $app['orm.ems']['db_write']->getRepository('ChamiloLMS\Entity\QuestionFieldValues')->find($field_values['id']);
  466. $extraFieldValue->setUserId(api_get_user_id());
  467. $extraFieldValue->setQuestionId($params[$this->handler_id]);
  468. break;
  469. case 'course':
  470. $extraFieldValue = $app['orm.ems']['db_write']->getRepository('ChamiloLMS\Entity\CourseFieldValues')->find($field_values['id']);
  471. $extraFieldValue->setUserId(api_get_user_id());
  472. $extraFieldValue->setCourseCode($params[$this->handler_id]);
  473. break;
  474. case 'user':
  475. $extraFieldValue = $app['orm.ems']['db_write']->getRepository('ChamiloLMS\Entity\UserFieldValues')->find($field_values['id']);
  476. $extraFieldValue->setUserId(api_get_user_id());
  477. $extraFieldValue->setAuthorId(api_get_user_id());
  478. break;
  479. case 'session':
  480. $extraFieldValue = $app['orm.ems']['db_write']->getRepository('ChamiloLMS\Entity\SessionFieldValues')->find($field_values['id']);
  481. $extraFieldValue->setUserId(api_get_user_id());
  482. $extraFieldValue->setSessionId($params[$this->handler_id]);
  483. break;
  484. }
  485. if (isset($extraFieldValue)) {
  486. if (!empty($params['value'])) {
  487. /*
  488. * If the field value is similar to the previous value then the comment will be the same
  489. in order to no save in the log an empty record
  490. */
  491. if ($extraFieldValue->getFieldValue() == $params['value']) {
  492. if (empty($params['comment'])) {
  493. $params['comment'] = $extraFieldValue->getComment();
  494. }
  495. }
  496. $extraFieldValue->setComment($params['comment']);
  497. $extraFieldValue->setFieldValue($params['value']);
  498. $extraFieldValue->setFieldId($params['field_id']);
  499. $extraFieldValue->setTms(api_get_utc_datetime(null, false, true));
  500. $app['orm.ems']['db_write']->persist($extraFieldValue);
  501. $app['orm.ems']['db_write']->flush();
  502. }
  503. }
  504. } else {
  505. $params['id'] = $field_values['id'];
  506. return parent::update($params, $show_query);
  507. }
  508. }
  509. }
  510. }
  511. /**
  512. * Returns the value of the given extra field on the given resource
  513. * @param int $item_id Item ID (It could be a session_id, course_id or user_id)
  514. * @param int $field_id Field ID (the ID from the *_field table)
  515. * @param bool $transform Whether to transform the result to a human readable strings
  516. * @return mixed A structured array with the field_id and field_value, or false on error
  517. * @assert (-1,-1) === false
  518. */
  519. public function get_values_by_handler_and_field_id($item_id, $field_id, $transform = false)
  520. {
  521. $field_id = intval($field_id);
  522. $item_id = Database::escape_string($item_id);
  523. $sql = "SELECT s.*, field_type FROM {$this->table} s
  524. INNER JOIN {$this->table_handler_field} sf ON (s.field_id = sf.id)
  525. WHERE
  526. item_id = '$item_id' AND
  527. field_id = '".$field_id."' AND
  528. sf.extra_field_type = ".$this->getExtraField()->getExtraFieldType()."
  529. ORDER BY id";
  530. $result = Database::query($sql);
  531. if (Database::num_rows($result)) {
  532. $result = Database::fetch_array($result, 'ASSOC');
  533. if ($transform) {
  534. if (!empty($result['value'])) {
  535. switch ($result['field_type']) {
  536. case ExtraField::FIELD_TYPE_DOUBLE_SELECT:
  537. $field_option = new ExtraFieldOption($this->type);
  538. $options = explode('::', $result['value']);
  539. // only available for PHP 5.4 :( $result['field_value'] = $field_option->get($options[0])['id'].' -> ';
  540. $result = $field_option->get($options[0]);
  541. $result_second = $field_option->get($options[1]);
  542. if (!empty($result)) {
  543. $result['value'] = $result['display_text'].' -> ';
  544. $result['value'] .= $result_second['display_text'];
  545. }
  546. break;
  547. case ExtraField::FIELD_TYPE_SELECT:
  548. $field_option = new ExtraFieldOption($this->type);
  549. $extra_field_option_result = $field_option->get_field_option_by_field_and_option(
  550. $result['field_id'],
  551. $result['value']
  552. );
  553. if (isset($extra_field_option_result[0])) {
  554. $result['value'] = $extra_field_option_result[0]['display_text'];
  555. }
  556. break;
  557. }
  558. }
  559. }
  560. return $result;
  561. } else {
  562. return false;
  563. }
  564. }
  565. /**
  566. * @param string $tag
  567. * @param int $field_id
  568. * @param int $limit
  569. *
  570. * @return array
  571. */
  572. public function searchValuesByField($tag, $field_id, $limit = 10)
  573. {
  574. $field_id = intval($field_id);
  575. $limit = intval($limit);
  576. $extraFieldType = $this->getExtraField()->getExtraFieldType();
  577. $tag = Database::escape_string($tag);
  578. $sql = "SELECT DISTINCT s.value, s.field_id
  579. FROM {$this->table} s
  580. INNER JOIN {$this->table_handler_field} sf
  581. ON (s.field_id = sf.id)
  582. WHERE
  583. field_id = '".$field_id."' AND
  584. value LIKE '%$tag%' AND
  585. sf.extra_field_type = ".$extraFieldType."
  586. ORDER BY value
  587. LIMIT 0, $limit
  588. ";
  589. $result = Database::query($sql);
  590. $values = array();
  591. if (Database::num_rows($result)) {
  592. $values = Database::store_result($result, 'ASSOC');
  593. }
  594. return $values;
  595. }
  596. /**
  597. * Gets a structured array of the original item and its extra values, using
  598. * a specific original item and a field name (like "branch", or "birthdate")
  599. * @param int $item_id Item ID from the original table
  600. * @param string $field_variable The name of the field we are looking for
  601. * @param bool $transform
  602. * @param bool $filterByVisibility
  603. * @param int $visibility
  604. *
  605. * @return mixed Array of results, or false on error or not found
  606. * @assert (-1,'') === false
  607. */
  608. public function get_values_by_handler_and_field_variable(
  609. $item_id,
  610. $field_variable,
  611. $transform = false,
  612. $filterByVisibility = false,
  613. $visibility = 0
  614. ) {
  615. $item_id = intval($item_id);
  616. $field_variable = Database::escape_string($field_variable);
  617. $extraFieldType = $this->getExtraField()->getExtraFieldType();
  618. $sql = "SELECT s.*, field_type
  619. FROM {$this->table} s
  620. INNER JOIN {$this->table_handler_field} sf
  621. ON (s.field_id = sf.id)
  622. WHERE
  623. item_id = '$item_id' AND
  624. variable = '".$field_variable."' AND
  625. sf.extra_field_type = $extraFieldType
  626. ";
  627. if ($filterByVisibility) {
  628. $visibility = intval($visibility);
  629. $sql .= " AND visible_to_self = $visibility ";
  630. }
  631. $sql .= " ORDER BY id";
  632. $result = Database::query($sql);
  633. if (Database::num_rows($result)) {
  634. $result = Database::fetch_array($result, 'ASSOC');
  635. if ($transform) {
  636. if ($result['field_type'] == ExtraField::FIELD_TYPE_DOUBLE_SELECT) {
  637. if (!empty($result['value'])) {
  638. $field_option = new ExtraFieldOption($this->type);
  639. $options = explode('::', $result['value']);
  640. $result = $field_option->get($options[0]);
  641. $result_second = $field_option->get($options[1]);
  642. if (!empty($result)) {
  643. $result['value'] = $result['display_text'].' -> ';
  644. $result['value'] .= $result_second['display_text'];
  645. }
  646. }
  647. }
  648. }
  649. return $result;
  650. } else {
  651. return false;
  652. }
  653. }
  654. /**
  655. * Gets the ID from the item (course, session, etc) for which
  656. * the given field is defined with the given value
  657. * @param string $field_variable Field (type of data) we want to check
  658. * @param string $field_value Data we are looking for in the given field
  659. * @param bool $transform Whether to transform the result to a human readable strings
  660. * @param bool $last Whether to return the last element or simply the first one we get
  661. * @return mixed Give the ID if found, or false on failure or not found
  662. * @assert (-1,-1) === false
  663. */
  664. public function get_item_id_from_field_variable_and_field_value(
  665. $field_variable,
  666. $field_value,
  667. $transform = false,
  668. $last = false,
  669. $all = false
  670. ) {
  671. $field_value = Database::escape_string($field_value);
  672. $field_variable = Database::escape_string($field_variable);
  673. $extraFieldType = $this->getExtraField()->getExtraFieldType();
  674. $sql = "SELECT item_id FROM {$this->table} s
  675. INNER JOIN {$this->table_handler_field} sf
  676. ON (s.field_id = sf.id)
  677. WHERE
  678. value = '$field_value' AND
  679. variable = '".$field_variable."' AND
  680. sf.extra_field_type = $extraFieldType
  681. ORDER BY item_id
  682. ";
  683. if ($last) {
  684. // If we want the last element instead of the first
  685. // This is useful in special cases where there might
  686. // (erroneously) be more than one row for an item
  687. $sql .= ' DESC';
  688. }
  689. $result = Database::query($sql);
  690. if ($result !== false && Database::num_rows($result)) {
  691. if ($all) {
  692. $result = Database::store_result($result, 'ASSOC');
  693. } else {
  694. $result = Database::fetch_array($result, 'ASSOC');
  695. }
  696. return $result;
  697. } else {
  698. return false;
  699. }
  700. }
  701. /**
  702. * Get all the values stored for one specific field
  703. * @param int $fieldId
  704. *
  705. * @return array|bool
  706. */
  707. public function getValuesByFieldId($fieldId)
  708. {
  709. $fieldId = intval($fieldId);
  710. $extraFieldType = $this->getExtraField()->getExtraFieldType();
  711. $sql = "SELECT s.* FROM {$this->table} s
  712. INNER JOIN {$this->table_handler_field} sf
  713. ON (s.field_id = sf.id)
  714. WHERE
  715. field_id = '".$fieldId."' AND
  716. sf.extra_field_type = $extraFieldType
  717. ORDER BY s.value";
  718. $result = Database::query($sql);
  719. if (Database::num_rows($result)) {
  720. return Database::store_result($result, 'ASSOC');
  721. }
  722. return false;
  723. }
  724. /**
  725. * @param int $itemId
  726. * @param int $fieldId
  727. * @return array
  728. */
  729. public function getAllValuesByItemAndField($itemId, $fieldId)
  730. {
  731. $fieldId = intval($fieldId);
  732. $itemId = intval($itemId);
  733. $extraFieldType = $this->getExtraField()->getExtraFieldType();
  734. $sql = "SELECT s.* FROM {$this->table} s
  735. INNER JOIN {$this->table_handler_field} sf
  736. ON (s.field_id = sf.id)
  737. WHERE
  738. field_id = '".$fieldId."' AND
  739. item_id = '$itemId' AND
  740. sf.extra_field_type = $extraFieldType
  741. ORDER BY s.value";
  742. $result = Database::query($sql);
  743. if (Database::num_rows($result)) {
  744. return Database::store_result($result, 'ASSOC');
  745. }
  746. return false;
  747. }
  748. /**
  749. * @param int $itemId
  750. *
  751. * @return array
  752. */
  753. public function getAllValuesByItem($itemId)
  754. {
  755. $itemId = intval($itemId);
  756. $extraFieldType = $this->getExtraField()->getExtraFieldType();
  757. $sql = "SELECT s.value, sf.variable, sf.field_type, sf.id
  758. FROM {$this->table} s
  759. INNER JOIN {$this->table_handler_field} sf
  760. ON (s.field_id = sf.id)
  761. WHERE
  762. item_id = '$itemId' AND
  763. sf.extra_field_type = $extraFieldType
  764. ORDER BY s.value";
  765. $result = Database::query($sql);
  766. $idList = [];
  767. if (Database::num_rows($result)) {
  768. $result = Database::store_result($result, 'ASSOC');
  769. $finalResult = [];
  770. foreach ($result as $item) {
  771. $finalResult[$item['id']] = $item;
  772. }
  773. $idList = array_column($result, 'id');
  774. }
  775. $em = Database::getManager();
  776. $extraField = new ExtraField($this->type);
  777. $allData = $extraField->get_all(['filter = ?' => 1]);
  778. $allResults = [];
  779. foreach ($allData as $field) {
  780. if (in_array($field['id'], $idList)) {
  781. $allResults[] = $finalResult[$field['id']];
  782. } else {
  783. if ($field['field_type'] == ExtraField::FIELD_TYPE_TAG) {
  784. $tagResult = [];
  785. $tags = $em->getRepository('ChamiloCoreBundle:ExtraFieldRelTag')
  786. ->findBy(
  787. [
  788. 'fieldId' => $field['id'],
  789. 'itemId' => $itemId,
  790. ]
  791. );
  792. if ($tags) {
  793. /** @var ExtraFieldRelTag $extraFieldTag */
  794. foreach ($tags as $extraFieldTag) {
  795. /** @var \Chamilo\CoreBundle\Entity\Tag $tag */
  796. $tag = $em->find('ChamiloCoreBundle:Tag', $extraFieldTag->getTagId());
  797. $tagResult[] = [
  798. 'id' => $extraFieldTag->getTagId(),
  799. 'value' => $tag->getTag()
  800. ];
  801. }
  802. }
  803. $allResults[] = [
  804. 'value' => $tagResult,
  805. 'variable' => $field['variable'],
  806. 'field_type' => $field['field_type'],
  807. 'id' => $field['id'] ,
  808. ];
  809. }
  810. }
  811. }
  812. return $allResults;
  813. }
  814. /**
  815. * @param int $itemId
  816. * @param int $fieldId
  817. * @param string $fieldValue
  818. *
  819. * @return array|bool
  820. */
  821. public function getAllValuesByItemAndFieldAndValue($itemId, $fieldId, $fieldValue)
  822. {
  823. $fieldId = intval($fieldId);
  824. $itemId = intval($itemId);
  825. $extraFieldType = $this->getExtraField()->getExtraFieldType();
  826. $fieldValue = Database::escape_string($fieldValue);
  827. $sql = "SELECT s.* FROM {$this->table} s
  828. INNER JOIN {$this->table_handler_field} sf
  829. ON (s.field_id = sf.id)
  830. WHERE
  831. field_id = '$fieldId' AND
  832. item_id = '$itemId' AND
  833. value = '$fieldValue' AND
  834. sf.extra_field_type = $extraFieldType
  835. ORDER BY value";
  836. $result = Database::query($sql);
  837. if (Database::num_rows($result)) {
  838. return Database::store_result($result, 'ASSOC');
  839. }
  840. return false;
  841. }
  842. /**
  843. * Deletes all the values related to a specific field ID
  844. * @param int $field_id
  845. *
  846. * @return void
  847. * @assert ('a') == null
  848. */
  849. public function delete_all_values_by_field_id($field_id)
  850. {
  851. $field_id = intval($field_id);
  852. $sql = "DELETE FROM {$this->table}
  853. WHERE
  854. field_id = $field_id ";
  855. Database::query($sql);
  856. }
  857. /**
  858. * Deletes values of a specific field for a specific item
  859. * @param int $item_id (session id, course id, etc)
  860. * @param int $field_id
  861. * @return void
  862. * @assert (-1,-1) == null
  863. */
  864. public function delete_values_by_handler_and_field_id($item_id, $field_id)
  865. {
  866. $field_id = intval($field_id);
  867. $item_id = intval($item_id);
  868. $extraFieldType = $this->getExtraField()->getExtraFieldType();
  869. $sql = "DELETE FROM {$this->table}
  870. WHERE
  871. item_id = '$item_id' AND
  872. field_id = '$field_id' AND
  873. extra_field_type = $extraFieldType
  874. ";
  875. Database::query($sql);
  876. }
  877. /**
  878. * Deletes all values from an item
  879. * @param int $itemId (session id, course id, etc)
  880. * @assert (-1,-1) == null
  881. */
  882. public function deleteValuesByItem($itemId)
  883. {
  884. $itemId = intval($itemId);
  885. $extraFieldType = $this->getExtraField()->getExtraFieldType();
  886. $sql = "DELETE FROM {$this->table}
  887. WHERE
  888. item_id = '$itemId' AND
  889. field_id IN (
  890. SELECT id FROM {$this->table_handler_field}
  891. WHERE extra_field_type = ".$extraFieldType."
  892. )
  893. ";
  894. Database::query($sql);
  895. }
  896. /**
  897. * @param int $itemId
  898. * @param int $fieldId
  899. * @param int $fieldValue
  900. */
  901. public function deleteValuesByHandlerAndFieldAndValue($itemId, $fieldId, $fieldValue)
  902. {
  903. $itemId = intval($itemId);
  904. $fieldId = intval($fieldId);
  905. $fieldValue = Database::escape_string($fieldValue);
  906. $sql = "DELETE FROM {$this->table}
  907. WHERE
  908. item_id = '$itemId' AND
  909. field_id = '$fieldId' AND
  910. value = '$fieldValue'
  911. ";
  912. Database::query($sql);
  913. }
  914. /**
  915. * Not yet implemented - Compares the field values of two items
  916. * @param int $item_id Item 1
  917. * @param int $item_to_compare Item 2
  918. * @todo
  919. * @return mixed Differential array generated from the comparison
  920. */
  921. public function compareItemValues($item_id, $item_to_compare)
  922. {
  923. }
  924. /**
  925. * Get all values for an item
  926. * @param int $itemId The item ID
  927. * @param boolean $visibleToSelf Get the visible extra field only
  928. * @param boolean $visibleToOthers
  929. *
  930. * @return array
  931. */
  932. public function getAllValuesForAnItem($itemId, $visibleToSelf = null, $visibleToOthers = null)
  933. {
  934. $em = Database::getManager();
  935. /** @var \Doctrine\DBAL\Query\QueryBuilder $qb */
  936. $qb = $em->createQueryBuilder();
  937. $qb = $qb->select('fv')
  938. ->from('ChamiloCoreBundle:ExtraFieldValues', 'fv')
  939. ->join('fv.field', 'f')
  940. ->where(
  941. $qb->expr()->eq('fv.itemId', ':item')
  942. );
  943. if (is_bool($visibleToSelf)) {
  944. $qb
  945. ->andWhere($qb->expr()->eq('f.visibleToSelf', ':visibleToSelf'))
  946. ->setParameter('visibleToSelf', $visibleToSelf);
  947. }
  948. if (is_bool($visibleToOthers)) {
  949. $qb
  950. ->andWhere($qb->expr()->eq('f.visibleToOthers', ':visibleToOthers'))
  951. ->setParameter('visibleToOthers', $visibleToOthers);
  952. }
  953. $fieldValues = $qb
  954. ->setParameter('item', $itemId)
  955. ->getQuery()
  956. ->getResult();
  957. $fieldOptionsRepo = $em->getRepository('ChamiloCoreBundle:ExtraFieldOptions');
  958. $valueList = [];
  959. /** @var ExtraFieldValues $fieldValue */
  960. foreach ($fieldValues as $fieldValue) {
  961. $item = [
  962. 'value' => $fieldValue
  963. ];
  964. switch ($fieldValue->getField()->getFieldType()) {
  965. case ExtraField::FIELD_TYPE_SELECT:
  966. $item['option'] = $fieldOptionsRepo->findOneBy([
  967. 'field' => $fieldValue->getField(),
  968. 'value' => $fieldValue->getValue()
  969. ]);
  970. break;
  971. }
  972. $valueList[] = $item;
  973. }
  974. return $valueList;
  975. }
  976. }