extra_field_value.lib.php 37 KB

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