extra_field_value.lib.php 36 KB

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