extra_field_value.lib.php 40 KB

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