STUDENT) * @param array $order_by a list of fields on which to sort * @return array An array with all specific fields, at platform level */ function get_specific_field_list($conditions = array(), $order_by = array()) { $table_sf = Database:: get_main_table(TABLE_MAIN_SPECIFIC_FIELD); $return_array = array(); $sql = "SELECT * FROM $table_sf"; if (count($conditions) > 0) { $sql .= ' WHERE '; $conditions_string_array = array(); foreach ($conditions as $field => $value) { $conditions_string_array[] = $field.' = '.$value; } $sql .= implode(' AND ', $conditions_string_array); } if (count($order_by) > 0) { $sql .= ' ORDER BY '.implode(',', $order_by); } $sql_result = Database::query($sql); while ($result = Database::fetch_array($sql_result)) { $return_array[] = $result; } return $return_array; } /** * @param array $conditions a list of condition (exemple : status=>STUDENT) * @param array $order_by a list of fields on which sort * @return array An array with all users of the platform. */ function get_specific_field_values_list( $conditions = array(), $order_by = array() ) { $table_sfv = Database:: get_main_table(TABLE_MAIN_SPECIFIC_FIELD_VALUES); $return_array = array(); $sql = "SELECT * FROM $table_sfv"; if (count($conditions) > 0) { $sql .= ' WHERE '; //If any course_code nor c_id is provided try to insert the current course id if (!isset($conditions['c_id']) && !isset($conditions['course_code'])) { $conditions['c_id'] = " " . api_get_course_int_id(); } $conditions_string_array = array(); foreach ($conditions as $field => $value) { $conditions_string_array[] = $field.' = '.$value; } $sql .= implode(' AND ', $conditions_string_array); } if (count($order_by) > 0) { $sql .= ' ORDER BY '.implode(',', $order_by); } $sql_result = Database::query($sql); while ($result = Database::fetch_array($sql_result)) { $return_array[] = $result; } return $return_array; } /** * @param char $prefix xapian prefix * @param string $course_code * @param string $tool_id Constant from mainapi.lib.php * @param int $ref_id representative id inside one tool item * @return array */ function get_specific_field_values_list_by_prefix( $prefix, $course_code, $tool_id, $ref_id ) { $em = Database::getManager(); $course = $em->getRepository('ChamiloCoreBundle:Course')->findOneBy(['code' => $course_code]); $sql_result = $em ->createQuery(' SELECT sfv.value FROM ChamiloCoreBundle:SpecificField sf LEFT JOIN ChamiloCoreBundle:SpecificFieldValues sfv WITH sf = sfv.fieldId WHERE sf.code = :code AND sfv.course = :course AND sfv.toolId = :tool AND sfv.refI = :reference ') ->setParameters([ 'code' => $prefix, 'course' => $course, 'tool' => $tool_id, 'reference' => $ref_id ]) ->getResult(); foreach ($sql_result as $result) { $return_array[] = $result; } return $return_array; } /** * Add a specific field value * @param int $id_specific_field specific field id * @param string $course_id course code * @param string $tool_id tool id, from main.api.lib * @param int $ref_id intern id inside specific tool table * @param string $value specific field value */ function add_specific_field_value( $id_specific_field, $course_id, $tool_id, $ref_id, $value ) { $em = Database::getManager(); $value = trim($value); if (empty($value)) { return false; } $course = $em->getRepository('ChamiloCoreBundle:Course')->findOneBy(['code' => $course_id]); $sfv = new \Chamilo\CoreBundle\Entity\SpecificFieldValues(); $sfv ->setCourse($course) ->setCourseCode($course->getCode()) ->setToolId($tool_id) ->setRefId($ref_id) ->setFieldId($id_specific_field) ->setValue($value); $em->persist($sfv); $em->flush(); return $sfv->getId(); } /** * Delete all values from a specific field id, course_id, ref_id and tool * @param string $course_id course code * @param int $id_specific_field specific field id * @param string $tool_id tool id, from main.api.lib * @param int $ref_id intern id inside specific tool table */ function delete_all_specific_field_value($course_id, $id_specific_field, $tool_id, $ref_id) { $em = Database::getManager(); $em ->createQuery(' DELETE FROM ChamiloCoreBundle:SpecificFieldValues sfv WHERE sfv.courseCode = :course AND sfv.toolId = :tool AND sfv.refId = :ref AND sfv.fieldId = :field ') ->execute([ 'course' => $course_id, 'tool' => $tool_id, 'ref' => $ref_id, 'field' => $id_specific_field ]); } /** * Delete all values from a specific item (course_id, tool_id and ref_id). * To be used when deleting such item from Dokeos * @param string Course code * @param string Tool ID * @param int Internal ID used in specific tool table */ function delete_all_values_for_item($course_id, $tool_id, $ref_id) { $em = Database::getManager(); $em ->createQuery(' DELETE FROM ChamiloCodeBundle:SpecificFieldValues sfv WHERE svf.courseCode = :course AND svf.toolId = :tool AND sfv.refId = :ref ') ->execute([ 'course' => $course_id, 'tool' => $tool_id, 'ref' => $ref_id ]); } /** * Generates a code (one-letter string) for a given field name * Defaults to the first letter of the name, otherwise iterate through available * letters * @param string Name * @return string One-letter code, upper-case */ function get_specific_field_code_from_name($name) { // Z is used internally by Xapian // O & C already used by tool_id and course_id $list = array( 'A', 'B', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', ); $table_sf = Database:: get_main_table(TABLE_MAIN_SPECIFIC_FIELD); $sql = "SELECT code FROM $table_sf ORDER BY code"; $res = Database::query($sql); $code = strtoupper(substr($name, 0, 1)); //if no code exists in DB, return current one if (Database::num_rows($res) < 1) { return $code; } $existing_list = array(); while ($row = Database::fetch_array($res)) { $existing_list[] = $row['code']; } //if the current code doesn't exist in DB, return current one if (!in_array($code, $existing_list)) { return $code; } $idx = array_search($code, $list); $c = count($list); for ($i = $idx + 1, $j = 0; $j < $c; $i++, $j++) { if (!in_array($list[$i], $existing_list)) { return $idx[$i]; } } // all 26 codes are used return false; }