public static function __CompletionUpdate($uid, $request)
{
global $DB;
$response = new CliniqueServiceResponce();
foreach ($request->data[0]->completion_tracking as $data) {
//$data->id = 0;
$data->coursemoduleid = $data->id;
$data->userid = $uid;
$data->completionstate = 1;
$data->viewed = 1;
$data->timemodified = time();
// Check there isn't really a row
$dbs = $DB->get_field('course_modules_completion', 'id', array('coursemoduleid' => $data->coursemoduleid, 'userid' => $data->userid));
if (!$dbs) {
// Didn't exist before, needs creating
$DB->insert_record('course_modules_completion', $data);
} else {
$data->id = $dbs;
$DB->update_record('course_modules_completion', $data);
}
}
$data = array('completed_modules' => self::getCompletedModuleIds($uid));
$response->response(false, '', $data);
die;
}
public static function __ScormUpdate($uid, $request)
{
$courseid = $request->courseid;
$cmid = $request->modid;
if (!($cm = get_coursemodule_from_id('scorm', $cmid, $courseid))) {
print_error('invalidcoursemodule');
}
$data = self::scorm_data_array($cmid, $courseid);
$reqdata = $uid . $courseid . $cmid;
// $uid.$courseid.$cmid userid + courseid + modid
$data_submitted = $request->data[0]->organizations->Turnaround_Revitalizing_Resource_ORG->cmi->{$reqdata};
$result = true;
$attempts = $data_submitted->attempts->value;
$starttime = $data_submitted->starttime->value;
self::scorm_start_time($uid, $data['scormid'], $data['scoid'], $attempts, $starttime);
foreach ($data_submitted as $element => $value) {
$element = str_replace('__', '.', $element);
if (substr($element, 0, 3) == 'cmi') {
$netelement = preg_replace('/\\.N(\\d+)\\./', "\\.\$1\\.", $element);
if ($value->setbysco === true) {
$result = scorm_insert_track($uid, $data['scormid'], $data['scoid'], $attempts, $element, $value->value, true) && $result;
//$scormid, $scoid
}
}
}
$response = new CliniqueServiceResponce();
if (!empty($result)) {
$response->response(false, 'Recorded inserted successfully');
} else {
$response->response(true, 'Invalid ID');
}
die;
}
public function __create($bookmarkurl, $title, $tokenval)
{
global $CFG, $DB;
$response = new CliniqueServiceResponce();
$token_val = array('token' => $tokenval);
$userId = array_values($DB->get_records_sql('SELECT userid FROM {external_tokens} et WHERE et.token=?', $token_val));
if ($userId) {
$user_id = array('id' => $userId[0]->userid);
//if(confirm_sesskey()){
$user = array_values($DB->get_records_sql('SELECT * FROM {user} u WHERE u.id=?', $user_id));
Favorites::__fav_user_login($user['0']);
if (get_user_preferences('user_bookmarks')) {
$bookmarks = explode(',', get_user_preferences('user_bookmarks'));
if (in_array($bookmarkurl . ";" . $title, $bookmarks)) {
$response->response(true, 'You have already bookmarked');
die;
}
} else {
$bookmarks = array();
}
//adds the bookmark at end of array
$bookmarks[] = $bookmarkurl . ";" . $title;
$bookmarks = implode(',', $bookmarks);
//adds to preferences table
set_user_preference('user_bookmarks', $bookmarks);
global $CFG;
//header("Location: " . $CFG->wwwroot . "/");
//print "Added Favourite Successfully";
$response->response(false, 'Added Favourite Successfully');
die;
} else {
$response->response(false, 'Invalid user');
die;
}
}
public function __DisplayBadges($uid, $callFor, $bid)
{
global $CFG, $DB;
$systemcontext = context_system::instance();
$response = new CliniqueServiceResponce();
$param_total = array('user_id' => $uid);
$params = array('user_badge_id' => $bid, 'user_id' => $uid);
if ($callFor == 'getBadges') {
$badgedetails = array_values($DB->get_records_sql('SELECT id,badge_name,badge_value FROM {badge} ORDER BY badge_value'));
// $users_badges = array_values($DB->get_records_sql('SELECT ub.user_badge_id,ub.user_badge_value,b.badge_name FROM {badge_user} ub,{badge} b WHERE ub.user_id=? AND ub.user_badge_id=b.id', $param_total));
$users_badges = array_values($DB->get_records_sql('SELECT ub.id,ub.user_badge_id,b.badge_value,b.badge_name FROM {badge_user} ub INNER JOIN {badge} b WHERE ub.user_id=? AND ub.user_badge_id=b.id;', $param_total));
if (!empty($uid) && !empty($bid)) {
//$userbadgecnt = count($badge_userdetails);
// if ($userbadgecnt == 0) {
$DB->insert_record('badge_user', $params);
//}
}
$badges = @array_merge(array("badges" => $badgedetails, "userbadges" => $users_badges));
if (!empty($badges)) {
$response->response(false, 'done', $badges);
} else {
$response->response(true, 'no_records', $badges);
}
}
}
public static function invokeService()
{
$request = file_get_contents('php://input');
$request = json_decode($request);
$userId = self::get_user_id($request->token);
if (empty($userId)) {
$response = new CliniqueServiceResponce();
$response->response(true, 'Invalid user');
die;
}
switch ($request->type) {
case 'bookmark':
Sync::syncBookmarks($userId, $request->data);
break;
case 'notes':
Sync::syncNotes($userId, $request->data);
break;
case 'favorite':
Sync::syncFavorites($userId, $request->data);
break;
case 'badges':
Sync::syncBadges($userId, $request->data);
break;
case 'quiz':
Sync::syncQuiz($userId, $request);
break;
case 'scorm':
Sync::syncScorm($userId, $request);
break;
case 'completion':
Sync::syncCompletion($userId, $request);
break;
}
}
public static function delete($userid, $url, $title, $return = false)
{
global $CFG, $DB;
$coursemoduleid = self::extractCourseModuleId($title);
$url = self::cleanUrl($url);
self::deleteFavorite($userid, $coursemoduleid);
if (empty($return)) {
$response = new CliniqueServiceResponce();
$response->response(false, 'Removed Favorite Successfully');
}
}
public function __cascadedatas($type, $region, $country, $retailer)
{
global $CFG, $DB;
$response = new CliniqueServiceResponce();
switch ($type) {
case 'region':
// Admin panel user creation uses user_info_field table to render region field
// Assumed source would be up to date and can be used across the app
$regionrow = $DB->get_record_sql('SELECT param1 FROM {user_info_field}' . ' WHERE shortname = "region"');
$regions = explode("\n", $regionrow->param1);
$regions = array_values($regions);
$regions = array_map(function ($region_name) {
$region = new stdClass();
if (strstr($region_name, '/')) {
$region_name = explode('/', strtolower($region_name));
$region->region = implode('/', array_map('ucwords', $region_name));
} else {
$region->region = $region_name;
//ucwords(strtolower($region_name));
}
return $region;
}, $regions);
count($regions) > 0 ? $response->response(false, 'region_success', $regions) : $response->response(true, 'region_failure');
break;
case 'country':
$countrySP = "CALL mdl_get_country_master('{$region}')";
$countries = $DB->get_records_sp($countrySP);
$country_ret = array();
foreach ($countries as $country) {
$country_ret[] = self::getCountryByName($country->countery);
}
count($countries) > 0 ? $response->response(false, 'country_success', $country_ret) : $response->response(true, 'country_failure', 'empty');
break;
case 'retailer':
$retailerSP = "CALL mdl_get_retailer_master('{$region}', '{$country}')";
$retailername = array_values($DB->get_records_sql($retailerSP));
array_walk($retailername, function ($retailer) {
$retailer->retailer = mb_convert_case(trim($retailer->retailer), MB_CASE_TITLE, "UTF-8");
});
count($retailername) > 0 ? $response->response(false, 'retailer_success', $retailername) : $response->response(true, 'retailer_failure', 'empty');
break;
case 'store':
$retailer = addslashes($retailer);
$storeSP = "CALL mdl_get_store_master('{$region}', '{$country}','{$retailer}')";
$storename = array_values($DB->get_records_sql($storeSP));
array_walk($storename, function ($store) {
$store->store = mb_convert_case(trim($store->store), MB_CASE_TITLE, "UTF-8");
});
// echo "<pre>"; print_r($storename); die;
count($storename) > 0 ? $response->response(false, 'store_success', $storename) : $response->response(true, 'store_failure', 'empty');
break;
}
}
public function __remove($bookmarkurl, $title, $tokenval)
{
global $CFG, $DB;
$bookmarkurl = htmlspecialchars_decode($bookmarkurl);
$title = htmlspecialchars_decode($title);
$response = new CliniqueServiceResponce();
$token_val = array('token' => $tokenval);
// Get user token
/**
* @get user id from external_tokens table
*
*/
$userId = array_values($DB->get_records_sql('SELECT userid FROM {external_tokens} et WHERE et.token=?', $token_val));
if ($userId) {
$user_id = array('id' => $userId[0]->userid);
//if(confirm_sesskey()){
$user = array_values($DB->get_records_sql('SELECT * FROM {user} u WHERE u.id=?', $user_id));
Favorites::__fav_user_login($user['0']);
$bookmarks = explode(',', get_user_preferences('user_bookmarks'));
$bookmarkremoved = false;
foreach ($bookmarks as $bookmark) {
// check the bookmarkurl and title given values to delete from preference
$tempBookmark = explode(';', $bookmark);
if ($tempBookmark[0] == $bookmarkurl && $tempBookmark[1] == $title) {
$keyToRemove = array_search($bookmark, $bookmarks);
unset($bookmarks[$keyToRemove]);
$bookmarkremoved = true;
}
}
if ($bookmarkremoved == false) {
$response->response(true, 'The bookmark you requested does not exist');
die;
}
$bookmarks = implode(',', $bookmarks);
set_user_preference('user_bookmarks', $bookmarks);
global $CFG;
//header("Location: " . $CFG->wwwroot . $bookmarkurl);
$response->response(false, 'Removed Favorite Successfully');
die;
} else {
$response->response(true, 'Invalid user');
die;
}
}
public function __favorites($tokenval)
{
global $CFG, $DB;
$response = new CliniqueServiceResponce();
//$username = "******";
//$password = "******";
$token_val = array('token' => $tokenval);
//$token_val = array('token'=>"64217bb7eea6f343a19f3af17104c49b");
$userId = array_values($DB->get_records_sql('SELECT userid FROM {external_tokens} et WHERE et.token=?', $token_val));
if ($userId) {
$user_id = array('id' => $userId[0]->userid);
//if(confirm_sesskey()){
$user = array_values($DB->get_records_sql('SELECT * FROM {user} u WHERE u.id=?', $user_id));
Favorites::__fav_user_login($user['0']);
$favourites_user = get_user_preferences('user_bookmarks');
$favourites_user = explode(',', get_user_preferences('user_bookmarks'));
/// Accessibility: markup as a list.
$favBookmark = array();
$incr = 0;
//print_r($favourites_user);
foreach ($favourites_user as $favourite_bookmark) {
//$favBookmark = explode(';', $favourite_bookmark);
//echo json_encode($favBookmark); // return json format
//$response->response(false, $favBookmark);
$data = explode(';', $favourite_bookmark);
$id = $data[0] ? $data[0] : null;
$fileDetails = explode("@", $data[1]);
$res[$incr]['url'] = $data[0];
$res[$incr]['id'] = $fileDetails[0] ? $fileDetails[0] : null;
$res[$incr]['course_type'] = $fileDetails[1] ? $fileDetails[1] : null;
$res[$incr]['file_name'] = $fileDetails[2] ? $fileDetails[2] : null;
$res[$incr]['file_type'] = $fileDetails[3] ? $fileDetails[3] : null;
$res[$incr]['fname_upload'] = $fileDetails[4] ? $fileDetails[4] : null;
$incr++;
}
$response->response(false, 'success', $res);
} else {
$response->response(true, 'Invalid user');
}
}
public function __cascadedatas($type, $region, $country, $retailer)
{
global $CFG, $DB;
$response = new CliniqueServiceResponce();
switch ($type) {
case 'region':
$regionname = array_values($DB->get_records_sql('SELECT DISTINCT(cr.region) FROM {cascade_region} cr WHERE cr.region != "" ORDER BY cr.region ASC'));
count($regionname) > 0 ? $response->response(false, 'region_success', $regionname) : $response->response(true, 'region_failure');
break;
case 'country':
$countryname = array_values($DB->get_records_sql('SELECT DISTINCT(UPPER(cr.country)) AS country,c.country_code AS code FROM {cascade_region} cr LEFT JOIN {country} c ON c.country_name=cr.country WHERE cr.region=? ORDER BY cr.country ASC', array('region' => $region)));
count($countryname) > 0 ? $response->response(false, 'country_success', $countryname) : $response->response(true, 'country_failure', 'empty');
break;
case 'retailer':
$retailername = array_values($DB->get_records_sql("SELECT DISTINCT(cr.retailer) FROM {cascade_region} cr WHERE cr.region='" . $region . "' AND cr.country='" . $country . "' AND cr.retailer!='' ORDER BY cr.retailer ASC"));
count($retailername) > 0 ? $response->response(false, 'retailer_success', $retailername) : $response->response(true, 'retailer_failure', 'empty');
break;
case 'store':
$storename = array_values($DB->get_records_sql("SELECT DISTINCT(cr.store) FROM {cascade_region} cr WHERE cr.region='" . $region . "' AND cr.country='" . $country . "' AND cr.retailer='" . $retailer . "' AND cr.store != '' ORDER BY cr.store ASC"));
count($storename) > 0 ? $response->response(false, 'store_success', $storename) : $response->response(true, 'store_failure', 'empty');
break;
}
}
public function __sendPassword($email)
{
global $CFG, $DB;
$systemcontext = context_system::instance();
$response = new CliniqueServiceResponce();
if (!empty($email) || !$email) {
$select = $DB->sql_like('email', ':email', false, true, false, '|') . " AND mnethostid = :mnethostid AND deleted=0 AND suspended=0";
$params = array('email' => $DB->sql_like_escape($email, '|'), 'mnethostid' => $CFG->mnet_localhost_id);
$user = $DB->get_record_select('user', $select, $params, '*', IGNORE_MULTIPLE);
if ($user and ($user->auth === 'nologin' or !is_enabled_auth($user->auth))) {
// bad luck - user is not able to login, do not let them reset password
$user = false;
$response->response(true, 'fp_nologin');
die;
}
if (!empty($user)) {
if (is_mnet_remote_user($user)) {
add_to_log(-1, 'custom_webservice', 'mnet_user', null, 'Forgot password - mnet user trying to access.', 0, $user->id);
$response->response(true, 'cp_mnet_user');
die;
}
if (isguestuser($user)) {
add_to_log(-1, 'custom_webservice', 'guest_user', null, 'Forgot password - guest user credential supplied.', 0, $user->id);
$response->response(true, 'fp_guest');
die;
}
// make sure user is allowed to change password
require_capability('moodle/user:changeownpassword', $systemcontext, $user->id);
if (!ForgotPassword::__app_reset_password_and_mail($user)) {
add_to_log(-1, 'custom_webservice', 'trigger_mail', null, 'Forgot password - email triggering failure.', 0, $user->id);
$response->response(true, 'fp_email_failure');
} else {
set_user_preference('auth_forcepasswordchange', true, $user->id);
$response->response(true, 'fp_email_success');
}
} else {
$response->response(false, 'fp_no_record');
}
} else {
add_to_log(-1, 'custom_webservice', 'input_parameters', null, 'Forgot password - input parameters missing.', 0, $user->id);
}
}
public function __getPassword($username, $email, $old_password, $new_password)
{
global $CFG, $DB;
$systemcontext = context_system::instance();
$response = new CliniqueServiceResponce();
if (!empty($username) && !empty($old_password) && !empty($new_password)) {
$user = $DB->get_record('user', array('username' => $username, 'mnethostid' => $CFG->mnet_localhost_id, 'deleted' => 0, 'suspended' => 0));
if (!empty($user)) {
if (is_mnet_remote_user($user)) {
add_to_log(-1, 'custom_webservice', 'mnet_user', null, 'Change password - mnet user trying to access.', 0, $user->id);
$response->response(true, 'cp_mnet_user');
die;
}
if (isguestuser($user)) {
add_to_log(-1, 'custom_webservice', 'mnet_user', null, 'Change password - guest user credential supplied.', 0, $user->id);
$response->response(true, 'cp_guest');
die;
}
// make sure user is allowed to change password
require_capability('moodle/user:changeownpassword', $systemcontext, $user->id);
// $generatePasswordResult = generatePassword::app_validate_internal_user_password($user, $old_password);
if (!ChangePassword::__app_validate_internal_user_password($user, $old_password)) {
$response->response(true, 'cp_wrong_oldpwd');
} else {
$userauth = get_auth_plugin($user->auth);
if ($userauth->user_update_password($user, $new_password)) {
unset_user_preference('auth_forcepasswordchange', $user);
unset_user_preference('create_password', $user);
$response->response(false, 'cp_success');
} else {
add_to_log(-1, 'custom_webservice', 'trigger_mail', null, 'Change password - password change updation failure.', 0, $user->id);
$response->response(true, 'cp_failure');
}
}
} else {
$response->response(false, 'cp_no_mail_record');
}
} else {
add_to_log(-1, 'custom_webservice', 'input_parameters', null, 'Change password - input parameters missing.', 0, $user->id);
}
}
public function __searchReports($fields, $keyword, $limit_start, $limit_end, $sortBy)
{
global $CFG, $DB, $SESSION;
if (empty($limit_start)) {
$limit_start = 0;
}
if (empty($limit_end)) {
$limit_end = 20;
}
if (empty($sortBy)) {
$sortBy = 'firstname';
$sortMode = 'ASC';
} else {
list($sortBy, $sortMode) = explode(' ', $sortBy);
}
$reportsArray = array();
$coursekey = array_search('fullname', $fields);
if (!is_bool($coursekey)) {
$fields[$coursekey] = 'course';
}
$fieldstilde = implode('~', $fields);
/*
CALL get_mdl_reports_dtl
(
@v_region := '',
@v_country := '',
@v_retailer := '',
@v_store := '',
@v_course := '',
@v_username := '******',
@v_email := 'terasima.ibuki@rainbow.plala.or.jp~whyunwhaya@nate.com',
@v_sortby := 'lastname',
@v_sortmode := 'desc',
@v_offset := '0',
@v_limit := '25'
)
*/
$reportsSPCall = "CALL get_mdl_reports_search ('{$keyword}','{$fieldstilde}'," . "'{$sortBy}', '{$sortMode}', '{$limit_start}', '{$limit_end}')";
$reportsDetails = $DB->get_records_sp($reportsSPCall);
$SESSION->reports_current_page = $reportsDetails;
$reportsArray['data'] = $reportsDetails;
$firstrow = array_shift(array_values($reportsDetails));
if (!empty($firstrow->recordcount)) {
$reportsArray['totalcount'] = $firstrow->recordcount;
} else {
$reportsArray['totalcount'] = 0;
}
$response = new CliniqueServiceResponce();
if (!empty($reportsDetails)) {
$response->response(false, 'done', $reportsArray);
} else {
$response->response(true, 'No Records');
}
}
public function __WidgetMultidisplay($courseid, $widgettype, $modid)
{
global $CFG, $DB;
$systemcontext = context_system::instance();
$response = new CliniqueServiceResponce();
if ($widgettype == 'Multi') {
$questionArray = array();
//$courseid = 45;
$get_module = $DB->get_records_sql("SELECT cm.id, cm.course, cm.instance, q.id, q.questions, q.intro FROM {course_modules} cm JOIN {quiz} q ON cm.instance = q.id WHERE cm.course = " . $courseid . " AND cm.id = " . $modid);
//var_dump($get_module);
if ($get_module) {
foreach ($get_module as $module) {
$questions_id = $module->questions;
$questionname = $module->intro;
}
$questionArray['questionname'][] = $questionname;
$split_ids_by_page = explode(",0", $questions_id);
$split_ids_by_page = array_values(array_filter($split_ids_by_page));
$all_ids = array();
foreach ($split_ids_by_page as $split_ids) {
$split_ids = explode(",", $split_ids);
$split_ids = array_values(array_filter($split_ids));
$all_ids = array_merge($all_ids, $split_ids);
}
//var_dump($all_ids);
foreach ($all_ids as $allid) {
$get_question = $DB->get_record('question', array('id' => $allid));
$get_answers = $DB->get_record('question_multichoice', array('question' => $allid));
$get_answer_text = $DB->get_records_sql('SELECT * FROM {question_answers} WHERE id IN (' . $get_answers->answers . ')');
$get_context_id = $DB->get_records_sql("SELECT qc.id, qc.contextid FROM {question} q JOIN {question_categories} qc ON q.category = qc.id WHERE q.id = " . $allid);
foreach ($get_context_id as $context) {
$context_id = $context->contextid;
}
$sql_usageid = array_values($DB->get_records_sql("SELECT max(questionusageid) as usageid, questionid, slot FROM {question_attempts} WHERE questionid=" . $allid));
$split_imagevalue = explode("@@/", $get_question->questiontext);
$split_value = explode('"', $split_imagevalue['1']);
$questionArray['questiontext'][$get_question->id] = $CFG->wwwroot . "/pluginfile.php/" . $context_id . "/question/questiontext/" . $sql_usageid['0']->usageid . "/" . $sql_usageid['0']->slot . "/" . $allid . "/" . $split_value['0'];
/*$src = $get_question->questiontext;
//$src = (string) reset(simplexml_import_dom(DOMDocument::loadHTML($get_question->questiontext))->xpath("//img/@src"));
//$ques_text = file_rewrite_pluginfile_urls($src, 'pluginfile.php', $context_id, 'question', 'questiontext', $allid);
$ques_text = question_rewrite_question_urls($src, 'pluginfile.php', $context_id, 'question', 'questiontext', $allid);
$questionArray['questiontext'][$get_question->id] = $ques_text;
//$questionArray['questiontext'][$get_question->id] = $get_question->questiontext;*/
$answer = array();
$right = 0;
$wrong = 0;
foreach ($get_answer_text as $answer_txt) {
if ($answer_txt->fraction > 0) {
$questionArray['answertext'][$answer_txt->question]['right_answers'][] = strip_tags($answer_txt->answer);
$right++;
$questionArray['answertext'][$answer_txt->question]['right_answers_count'] = $right;
} else {
$questionArray['answertext'][$answer_txt->question]['wrong_answers'][] = strip_tags($answer_txt->answer);
$wrong++;
$questionArray['answertext'][$answer_txt->question]['wrong_answers_count'] = $wrong;
}
}
}
}
if (!empty($questionArray)) {
$response->response(false, 'done', $questionArray);
} else {
$response->response(true, 'msg', "No records");
}
}
}
public function __analysePercentage($cid, $uid, $return = false)
{
global $CFG, $DB;
$systemcontext = context_system::instance();
$response = new CliniqueServiceResponce();
$getCrseCatID = array_values($DB->get_records_sql("SELECT cat.name FROM {course_categories} cat WHERE cat.name = 'Courses'"));
if ((!empty($uid) || !empty($cid)) && $getCrseCatID[0]->name == 'Courses') {
$params = array('course' => $cid, 'userid' => $uid);
$param_total = array('userid' => $uid);
$UserScoreDetails = array();
$otherUserScoreDetails = array();
$currentUserScore = array_values($DB->get_records_sql("SELECT scores.id AS id, scores.userid AS userid, scores.firstname AS firstname, scores.lastname AS lastname, SUM(scores.sumgrades) AS score FROM\n (\n SELECT qa.id AS id,qa.userid AS userid,u.firstname AS firstname,u.lastname AS lastname,MAX(qa.sumgrades) AS sumgrades\n FROM {quiz} q\n LEFT JOIN {quiz_attempts} qa ON qa.quiz=q.id AND q.name NOT LIKE 'aa:%' AND q.name NOT LIKE 'bb:%'\n LEFT JOIN {user} u ON u.id=qa.userid\n WHERE q.course=? AND qa.userid=? AND qa.state='finished' GROUP BY qa.userid, qa.quiz\n ) scores\n GROUP BY scores.userid", $params));
$currentUsercnt = count($currentUserScore);
if ($currentUsercnt == 1) {
//echo "if";
$param_currentuser = array('userid' => $currentUserScore[0]->userid);
$param_badgecurrentuser = array('user_id' => $currentUserScore[0]->userid, 'userid' => $currentUserScore[0]->userid);
$currentUserTotalPoints = array_values($DB->get_records_sql("SELECT scores.id AS id, scores.userid AS userid, scores.firstname AS firstname, scores.lastname AS lastname, SUM(scores.sumgrades) AS totalscore FROM\n (\n SELECT qa.id AS id,qa.userid AS userid,u.firstname AS firstname,u.lastname AS lastname,MAX(qa.sumgrades) AS sumgrades\n FROM {quiz} q INNER JOIN mdl_course c ON c.id = q.course AND q.name NOT LIKE 'aa:%' AND q.name NOT LIKE 'bb:%'\nINNER JOIN mdl_course_categories cc ON c.category = cc.id \n LEFT JOIN {quiz_attempts} qa ON qa.quiz=q.id\n LEFT JOIN {user} u ON u.id=qa.userid\n WHERE qa.userid=? AND qa.state='finished' GROUP BY qa.userid, qa.quiz\n ) scores\n GROUP BY scores.userid", $param_currentuser));
//print_r($currentUserTotalPoints);
$userbadge = array_values($DB->get_records_sql('SELECT b.badge_name FROM {badge} b WHERE b.id IN (SELECT ub.user_badge_id FROM {badge_user} ub WHERE ub.user_id=? AND b.badge_value=(SELECT MAX(b.badge_value) FROM {badge} b INNER JOIN {badge_user} ub ON ub.user_badge_id=b.id WHERE ub.user_id=?))LIMIT 0,1', $param_badgecurrentuser));
$firstname = substr($currentUserScore[0]->firstname, 0, 1);
$lastname = substr($currentUserScore[0]->lastname, 0, 1);
$UserScoreDetails['userid'][] = $currentUserScore[0]->userid;
$UserScoreDetails['firstname'][] = $firstname;
$UserScoreDetails['lastname'][] = $lastname;
$UserScoreDetails['score'][] = $currentUserScore[0]->score;
$UserScoreDetails['totalscore'][] = $currentUserTotalPoints[0]->totalscore;
$UserScoreDetails['badge'][] = $userbadge[0]->badge_name;
//print_r($UserScoreDetails);
} else {
//echo "else";
$param_currentuser = array('userid' => $uid);
$param_badgecurrentuser = array('user_id' => $uid, 'userid' => $uid);
//$currentUserTotalPoints = array_values($DB->get_records_sql('SELECT u.firstname,u.lastname FROM {user} u WHERE u.id=?', $param_currentuser));
$currentUserTotalPoints = array_values($DB->get_records_sql("SELECT scores.id AS id, scores.userid AS userid, scores.firstname AS firstname, scores.lastname AS lastname, SUM(scores.sumgrades) AS totalscore FROM\n (\n SELECT qa.id AS id,qa.userid AS userid,u.firstname AS firstname,u.lastname AS lastname,MAX(qa.sumgrades) AS sumgrades\n FROM {quiz} q INNER JOIN mdl_course c ON c.id = q.course AND q.name NOT LIKE 'aa:%' AND q.name NOT LIKE 'bb:%'\nINNER JOIN mdl_course_categories cc ON c.category = cc.id \n LEFT JOIN {quiz_attempts} qa ON qa.quiz=q.id\n LEFT JOIN {user} u ON u.id=qa.userid\n WHERE qa.userid=? AND qa.state='finished' GROUP BY qa.userid, qa.quiz\n ) scores\n GROUP BY scores.userid", $param_currentuser));
$userbadge = array_values($DB->get_records_sql('SELECT b.badge_name FROM {badge} b WHERE b.id IN (SELECT ub.user_badge_id FROM {badge_user} ub WHERE ub.user_id=? AND b.badge_value=(SELECT MAX(b.badge_value) FROM {badge} b INNER JOIN {badge_user} ub ON ub.user_badge_id=b.id WHERE ub.user_id=?))LIMIT 0,1', $param_badgecurrentuser));
$firstname = substr($currentUserTotalPoints[0]->firstname, 0, 1);
$lastname = substr($currentUserTotalPoints[0]->lastname, 0, 1);
$UserScoreDetails['userid'][] = $uid;
$UserScoreDetails['firstname'][] = $firstname;
$UserScoreDetails['lastname'][] = $lastname;
$UserScoreDetails['score'][] = 0;
$UserScoreDetails['totalscore'][] = $currentUserTotalPoints[0]->totalscore;
$UserScoreDetails['badge'][] = $userbadge[0]->badge_name;
}
//Add custom scores for 1331 US users
$custompoint = self::getCustomPoints($uid);
if (!empty($custompoint->id)) {
// Does addition score exists for user? add them too.
$UserScoreDetails['totalscore'][0] += $custompoint->points;
}
$otherUsersScore = array_values($DB->get_records_sql("SELECT scores.id AS id, scores.userid AS userid, scores.firstname AS firstname, scores.lastname AS lastname, SUM(scores.sumgrades) AS score FROM\n (\n SELECT qa.id AS id,qa.userid AS userid,u.firstname AS firstname,u.lastname AS lastname,MAX(qa.sumgrades) AS sumgrades\n FROM {quiz} q\n LEFT JOIN {quiz_attempts} qa ON qa.quiz=q.id AND q.name NOT LIKE 'aa:%' AND q.name NOT LIKE 'bb:%'\n LEFT JOIN {user} u ON u.id=qa.userid\n WHERE q.course=? AND qa.userid!=? AND u.deleted=0 AND qa.state='finished' GROUP BY qa.userid,qa.quiz ORDER BY SUM(qa.sumgrades) DESC) scores\n GROUP BY scores.userid ORDER BY score DESC", $params, 0, 10));
$otherUsercnt = count($otherUsersScore);
//print_r($otherUsersScore);
for ($i = 0; $i < $otherUsercnt; $i++) {
$otherUsersScore[$i]->userid;
$param_otheruser = array('userid' => $otherUsersScore[$i]->userid);
$param_badgeotheruser = array('user_id' => $otherUsersScore[$i]->userid, 'userid' => $otherUsersScore[$i]->userid);
$otherUserTotalPoints = array_values($DB->get_records_sql("SELECT scores.id AS id, scores.userid AS userid,scores.quizid AS quizid,scores.firstname AS firstname, SUM(scores.sumgrades) AS totalscore FROM\n (\n SELECT qa.id AS id,qa.quiz AS quizid, qa.userid AS userid,u.firstname AS firstname,MAX(qa.sumgrades) AS sumgrades\n FROM {quiz} q\n LEFT JOIN {quiz_attempts} qa ON qa.quiz=q.id AND q.name NOT LIKE 'aa:%' AND q.name NOT LIKE 'bb:%'\n LEFT JOIN {user} u ON u.id=qa.userid\n WHERE qa.userid=? AND qa.state='finished' GROUP BY qa.userid, qa.quiz ORDER BY SUM(qa.sumgrades) DESC) scores\n GROUP BY scores.userid ORDER BY totalscore DESC", $param_otheruser, 0, 10));
//print_r($otherUserTotalPoints);
$userbadge = array_values($DB->get_records_sql('SELECT b.badge_name FROM {badge} b WHERE b.id IN (SELECT ub.user_badge_id FROM {badge_user} ub WHERE ub.user_id=? AND b.badge_value=(SELECT MAX(b.badge_value) FROM {badge} b INNER JOIN {badge_user} ub ON ub.user_badge_id=b.id WHERE ub.user_id=?))LIMIT 0,1', $param_badgeotheruser));
//print_r($userbadge);
$firstname = substr($otherUsersScore[$i]->firstname, 0, 1);
$lastname = substr($otherUsersScore[$i]->lastname, 0, 1);
$UserScoreDetails['userid'][] = $otherUsersScore[$i]->userid;
$UserScoreDetails['firstname'][] = $firstname;
$UserScoreDetails['lastname'][] = $lastname;
$UserScoreDetails['score'][] = $otherUsersScore[$i]->score;
$UserScoreDetails['totalscore'][] = $otherUserTotalPoints[0]->totalscore;
$UserScoreDetails['badge'][] = $userbadge[0]->badge_name;
}
if (empty($return)) {
if (!empty($UserScoreDetails)) {
$response->response(false, 'done', $UserScoreDetails);
} else {
$response->response(true, 'no_records', $UserScoreDetails);
}
}
return $UserScoreDetails;
}
}
public function __analyseCourseSearchfield($type, $region, $country, $retailer, $filter)
{
global $CFG, $DB;
$systemcontext = context_system::instance();
$response = new CliniqueServiceResponce();
$CourseSearchfield = array();
switch ($type) {
case 'region':
// Admin panel user creation uses user_info_field table to render region field
// Assumed source would be up to date and can be used across the app
$regionrow = $DB->get_record_sql('SELECT param1 FROM {user_info_field}' . ' WHERE shortname = "region"');
$regions = explode("\n", $regionrow->param1);
$regions = array_map(function ($region_name) {
$region = new stdClass();
if (strstr($region_name, '/')) {
$region_name = explode('/', strtolower($region_name));
$region->region = implode('/', array_map('ucwords', $region_name));
} else {
//$region->region = ucwords(strtolower($region_name));
$region->region = $region_name;
}
return $region;
}, $regions);
$CourseSearchfield['region'] = $regions;
break;
case 'country':
if (in_array("sel_all", $region)) {
$regionstr = '%';
} else {
$regionstr = implode('~', $region);
}
$countrySP = "CALL mdl_get_country_master('{$regionstr}')";
$countries = array_values($DB->get_records_sp($countrySP));
if ($filter == 1) {
$CourseSearchfield['country_filter'] = 1;
} else {
$CourseSearchfield['country_filter'] = 0;
}
$CourseSearchfield['country'] = $countries;
break;
case 'retailer':
if (in_array("sel_all", $region)) {
$regionstr = '%';
} else {
$regionstr = implode('~', $region);
}
if (!in_array("sel_all", $country)) {
$countrystr = implode('","', $country);
$countrywhere = ' WHERE country_code IN ("' . $countrystr . '")';
$countrycodes = array_values($DB->get_records_sql('SELECT country_name FROM {country} ' . $countrywhere));
$countries = array();
foreach ($countrycodes as $countrycode) {
$countries[] = $countrycode->country_name;
}
$countrynamestr = implode('~', $countries);
} else {
$countrynamestr = '%';
}
$retailerSP = "CALL mdl_get_retailer_master('{$regionstr}', '{$countrynamestr}')";
$retailers = array_values($DB->get_records_sp($retailerSP));
$storeSP = "CALL mdl_get_store_master('{$regionstr}', '{$countrynamestr}','%')";
$stores = array_values($DB->get_records_sp($storeSP));
if ($filter == 1) {
$CourseSearchfield['country_filter'] = 1;
} else {
$CourseSearchfield['country_filter'] = 0;
}
$CourseSearchfield['retailer'] = $retailers;
$CourseSearchfield['store'] = $stores;
break;
case 'store':
if (in_array("sel_all", $region)) {
$regionstr = '%';
} else {
$regionstr = implode('~', $region);
}
if (!in_array("sel_all", $country)) {
$countrystr = implode('","', $country);
}
if (in_array("sel_all", $retailer)) {
$retailerstr = '%';
} else {
$retailerstr = implode('~', $retailer);
}
if ($filter == 1) {
$CourseSearchfield['store_filter'] = 1;
} else {
$CourseSearchfield['store_filter'] = 0;
}
if (!in_array("sel_all", $country)) {
$countrystr = implode('","', $country);
$countrywhere = ' WHERE country_code IN ("' . $countrystr . '")';
$countrycodes = array_values($DB->get_records_sql('SELECT country_name FROM {country} ' . $countrywhere));
$countries = array();
foreach ($countrycodes as $countrycode) {
$countries[] = $countrycode->country_name;
}
$countrynamestr = implode('~', $countries);
} else {
$countrynamestr = '%';
}
$retailerstr = addslashes($retailerstr);
$storeSP = "call mdl_get_store_master('{$regionstr}','{$countrynamestr}','{$retailerstr}')";
$stores = array_values($DB->get_records_sp($storeSP));
$coursename = array_values($DB->get_records_sql("SELECT fullname FROM {course} ORDER BY fullname ASC"));
$CourseSearchfield['store'] = $stores;
$CourseSearchfield['course'] = $coursename;
break;
case 'course':
if ($filter == 1) {
$CourseSearchfield['course_filter'] = 1;
} else {
$CourseSearchfield['course_filter'] = 0;
}
$coursename = array_values($DB->get_records_sql("SELECT fullname FROM {course} ORDER BY fullname ASC"));
$CourseSearchfield['course'] = $coursename;
break;
}
$cohortDetails = $DB->get_records_sql("SELECT c.id, c.name FROM {cohort} c ORDER BY c.name ASC");
$CourseSearchfield['team'] = $cohortDetails;
if (!empty($CourseSearchfield)) {
$response->response(false, 'done', $CourseSearchfield);
} else {
$response->response(true, 'no_records');
}
}
private static function quiz_attempt_process($id, $step, $forcenew, $page, $uid, $request, $key, $startedOn)
{
global $DB, $USER;
$USER->id = $uid;
$response = new CliniqueServiceResponce();
$quizobj = '';
//$timenow = time(); // Update time now, in case the server is running really slowly.
$timenow = $startedOn;
if (!($cm = get_coursemodule_from_id('quiz', $id))) {
print_error('invalidcoursemodule');
}
$quizobj = quiz::create($cm->instance, $uid);
$attempts_allowed = self::attempts_allowed($attempt, $quizobj);
// Create an object to manage all the other (non-roles) access rules.
$accessmanager = $quizobj->get_access_manager($timenow);
$attempt_nums = self::accessmanager_process($quizobj, $accessmanager, $forcenew, $uid);
if ($attempts_allowed != 0) {
if ($attempts_allowed < $attempt_nums->attemptnumber) {
$block_res = self::get_blocked_response($request, '', $key);
$response->response(false, "", $block_res);
die;
// $response->response(false, "", $request);
}
}
$quba = question_engine::make_questions_usage_by_activity('mod_quiz', $quizobj->get_context());
$quba->set_preferred_behaviour($quizobj->get_quiz()->preferredbehaviour);
// Create the new attempt and initialize the question sessions
$attempt = quiz_create_attempt($quizobj, $attempt_nums->attemptnumber, $attempt_nums->lastattempt, $timenow, $quizobj->is_preview_user());
self::quiz_attempt_stepone($attempt, $attempt_nums->lastattempt, $quizobj, $timenow, $quba, $attempt_nums->attemptnumber);
// Save the attempt in the database.
$transaction = $DB->start_delegated_transaction();
question_engine::save_questions_usage_by_activity($quba);
$attempt->uniqueid = $quba->get_id();
$attempt->id = $DB->insert_record('quiz_attempts', $attempt);
// Trigger event.
$res = self::trigger_event($attempt, $quizobj);
$transaction->allow_commit();
return $res;
}
public function __analyseCourseSearchfield($type, $region, $country, $retailer, $filter)
{
global $CFG, $DB;
$systemcontext = context_system::instance();
$response = new CliniqueServiceResponce();
$region_id = array_values($DB->get_records_sql("SELECT id FROM {user_info_field} uf WHERE uf.shortname LIKE '%Region%'"));
$retaile_id = array_values($DB->get_records_sql("SELECT id FROM {user_info_field} uf WHERE uf.shortname LIKE '%Retailer%'"));
$store_id = array_values($DB->get_records_sql("SELECT id FROM {user_info_field} uf WHERE uf.shortname LIKE '%Store%'"));
$CourseSearchfield = array();
switch ($type) {
case 'region':
// Junk retailers and travel retail north america are ignored in the response
$regionname = array_values($DB->get_records_sql('SELECT DISTINCT(r.region) FROM {reports} r' . ' WHERE r.region NOT IN ("", "Choose...", "Выбрать...") ORDER BY r.region ASC'));
$CourseSearchfield['region'] = $regionname;
break;
case 'country':
$where = $filter == 1 ? ' WHERE r.region=? AND r.country !="" ORDER BY r.country ASC' : ' WHERE r.country !="" ORDER BY r.country ASC';
$params = $filter == 1 ? array('region' => $region) : array();
$countryname = array_values($DB->get_records_sql('SELECT DISTINCT(r.country) AS CODE,c.country_name AS countery FROM {reports} r LEFT JOIN {country} c ON c.country_code=r.country' . $where, $params));
$CourseSearchfield['country_filter'] = 0;
if ($filter == 1) {
$retailername = array_values($DB->get_records_sql("SELECT DISTINCT(r.retailer) FROM {reports} r WHERE r.region='" . $region . "' AND r.retailer IS NOT NULL ORDER BY r.retailer ASC"));
$storename = array_values($DB->get_records_sql("SELECT DISTINCT(r.store) FROM {reports} r WHERE r.region='" . $region . "' AND r.store IS NOT NULL ORDER BY r.store ASC"));
$CourseSearchfield['country_filter'] = 1;
} else {
$retailername = array_values($DB->get_records_sql("SELECT DISTINCT(r.retailer) FROM {reports} r WHERE r.retailer IS NOT NULL ORDER BY r.retailer ASC"));
$storename = array_values($DB->get_records_sql("SELECT DISTINCT(r.store) FROM {reports} r WHERE r.store IS NOT NULL ORDER BY r.store ASC"));
$CourseSearchfield['country_filter'] = 0;
}
$CourseSearchfield['country'] = $countryname;
$CourseSearchfield['retailer'] = $retailername;
$CourseSearchfield['store'] = $storename;
//print_r($countryname); exit; //echo sprintf('SELECT DISTINCT(cr.country),c.country_code AS code FROM {cascade_region} cr LEFT JOIN {country} c ON c.country_name=cr.country'.$where, $params); exit;
break;
case 'retailer':
$where = $filter == 1 ? ' WHERE r.region=? AND r.country=? ORDER BY r.retailer ASC' : ' ORDER BY r.retailer ASC';
$params = $filter == 1 ? array('region' => $region, 'country' => $country) : array();
if ($filter == 1) {
$where = "r.id!=''";
if ($region != '') {
$where .= " AND r.region='" . $region . "'";
}
if ($country != '') {
$where .= " AND r.country='" . $country . "'";
}
$retailername = array_values($DB->get_records_sql("SELECT DISTINCT(r.retailer) FROM {reports} r WHERE {$where} AND r.retailer IS NOT NULL ORDER BY r.retailer ASC"));
$storename = array_values($DB->get_records_sql("SELECT DISTINCT(r.store) FROM {reports} r WHERE {$where} AND r.store IS NOT NULL ORDER BY r.store ASC"));
$CourseSearchfield['retailer_filter'] = 1;
} else {
$retailername = array_values($DB->get_records_sql("SELECT DISTINCT(r.retailer) FROM {reports} r WHERE r.retailer IS NOT NULL ORDER BY r.retailer ASC"));
$storename = array_values($DB->get_records_sql("SELECT DISTINCT(r.store) FROM {reports} r WHERE r.store IS NOT NULL ORDER BY r.store ASC"));
$CourseSearchfield['retailer_filter'] = 0;
}
/*$userretailerlist = array_values($DB->get_records_sql("SELECT DISTINCT uid.data FROM mdl_user_info_data uid WHERE uid.data != '' AND uid.fieldid=".$retaile_id[0]->id." AND uid.userid IN (SELECT userid FROM mdl_user_info_data WHERE fieldid = ".$region_id[0]->id." AND DATA != '' AND DATA = '".$region."') AND uid.userid IN (SELECT u.id FROM mdl_user u, mdl_country c WHERE u.country = c.country_code AND LOWER(c.country_name) = LOWER('".$country."'))"));*/
//echo sprintf('SELECT DISTINCT(cr.retailer) FROM {cascade_region} cr'.$where, $params); exit;
$CourseSearchfield['retailer'] = $retailername;
$CourseSearchfield['store'] = $storename;
break;
case 'store':
$where = $filter == 1 ? ' WHERE r.region=? AND r.country=? AND r.retailer=? ORDER BY r.store ASC' : ' ORDER BY r.store ASC';
$params = $filter == 1 ? array('region' => $region, 'country' => $country, 'retailer' => $retailer) : array();
foreach ($retailer as $key => $value) {
$retailerstr .= '"' . $value . '",';
}
$retailersrtval = substr($retailerstr, 0, -1);
if ($filter == 1) {
$where = "r.id!=''";
if ($region != '') {
$where .= " AND r.region='" . $region . "'";
}
if ($country != '') {
$where .= " AND r.country='" . $country . "'";
}
if ($retailersrtval != '' && $retailersrtval != '"sel_all"') {
$where .= " AND retailer IN(" . $retailersrtval . ")";
}
$storename = array_values($DB->get_records_sql("SELECT DISTINCT(r.store) FROM {reports} r WHERE {$where} AND r.store IS NOT NULL ORDER BY r.store ASC"));
$CourseSearchfield['store_filter'] = 1;
} else {
$storename = array_values($DB->get_records_sql("SELECT DISTINCT(r.store) FROM {reports} r WHERE r.store IS NOT NULL ORDER BY r.store ASC"));
$CourseSearchfield['store_filter'] = 0;
}
/*$userretailerlist = array_values($DB->get_records_sql("SELECT DISTINCT uid.data FROM mdl_user_info_data uid WHERE uid.data != '' AND uid.fieldid=".$store_id[0]->id." AND uid.userid IN (SELECT userid FROM mdl_user_info_data WHERE fieldid = ".$region_id[0]->id." AND DATA != '' AND DATA = '".$region."') AND uid.userid IN (SELECT userid FROM mdl_user_info_data WHERE fieldid = ".$retaile_id[0]->id." AND DATA != '' AND DATA = '".$retailer."') AND uid.userid IN (SELECT u.id FROM mdl_user u, mdl_country c WHERE u.country = c.country_code AND LOWER(c.country_name) = LOWER('".$country."'))"));*/
$CourseSearchfield['store'] = $storename;
break;
}
/* $coursenameDetails = $DB->get_records_sql("SELECT c.id, c.fullname FROM {course} c WHERE c.visible = 1 ORDER BY c.fullname ASC");
$CourseSearchfield['course'] = $coursenameDetails; */
//Retailer:
/*$retailerDetails = array_values($DB->get_records_sql("SELECT uid.data FROM {user_info_field} uif, {user_info_data} uid WHERE uif.id = uid.fieldid AND LOWER(uif.shortname) = 'retailer' AND uid.data != '' GROUP BY uid.data ORDER BY uid.data ASC"));
$registeredretailer = json_decode(json_encode($retailerDetails), true);
$allretailers = json_decode(json_encode($retailername), true);
$registeredretailerres = array(); // initialize result
array_walk_recursive($registeredretailer, function($item) use (&$registeredretailerres) {
// flatten the array
$registeredretailerres[] = trim($item);
});
$allretailersres = array(); // initialize result
array_walk_recursive($allretailers, function($item) use (&$allretailersres) {
// flatten the array
$allretailersres[] = trim($item);
});
$retailerconcat = array_intersect($registeredretailerres, $allretailersres);*/
//Store:
/*$storeDetails = array_values($DB->get_records_sql("SELECT uid.data AS store FROM {user_info_field} uif, {user_info_data} uid WHERE uif.id = uid.fieldid AND LOWER(uif.shortname) = 'store' AND uid.data != '' GROUP BY uid.data ORDER BY uid.data ASC"));
$registeredstore = json_decode(json_encode($storeDetails), true);
$allstores = json_decode(json_encode($storename), true);
$registeredstoreres = array(); // initialize result
array_walk_recursive($registeredstore, function($item) use (&$registeredstoreres) {
// flatten the array
$registeredstoreres[] = trim($item);
});
$allstoresres = array(); // initialize result
array_walk_recursive($allstores, function($item) use (&$allstoresres) {
// flatten the array
$allstoresres[] = trim($item);
});
$storeconcat = array_intersect($registeredstoreres, $allstoresres);*/
/*$regionDetails = array_values($DB->get_records_sql("SELECT uid.data FROM {user_info_field} uif, {user_info_data} uid WHERE uif.id = uid.fieldid AND LOWER(uif.shortname) = 'region' AND uid.data != '' GROUP BY uid.data ORDER BY uid.data ASC"));*/
//Country:
$cohortDetails = $DB->get_records_sql("SELECT c.id, c.name FROM {cohort} c ORDER BY c.name ASC");
$CourseSearchfield['team'] = $cohortDetails;
if (!empty($CourseSearchfield)) {
$response->response(false, 'done', $CourseSearchfield);
} else {
$response->response(true, 'no_records');
}
}
public function __analyseProgressPercentage($uid, $return = false)
{
global $CFG, $DB;
$systemcontext = context_system::instance();
$response = new CliniqueServiceResponce();
if (!empty($uid)) {
//$params = array('userid' => $uid);
$CourseDetails = array();
/*$UserTotalPoints = array_values($DB->get_records_sql('SELECT SUM(qa.sumgrades) AS totalscore
FROM {quiz} q
INNER JOIN {quiz_attempts} qa ON qa.quiz=q.id
WHERE qa.userid=? AND qa.state="finished" GROUP BY qa.userid', $params));
$CourseDetails[totalscore] = $UserTotalPoints[0]->totalscore;*/
//echo "<pre>";
//$courseDetails = array_values($DB->get_records_sql('SELECT c.id, c.fullname FROM mdl_user_enrolments ue, mdl_course c WHERE ue.enrolid = c.id AND ue.userid=? AND c.visible=1', $params));
$getCrseCat = $DB->get_record('course_categories', array('name' => 'Courses'));
$params = array('userid' => $uid, 'category' => $getCrseCat->id);
//$courseDetails = array_values($DB->get_records_sql('SELECT c.id, c.fullname FROM {course} c, {enrol} e, {user_enrolments} ue WHERE c.id = e.courseid AND e.id = ue.enrolid AND ue.userid=? AND c.category=? ORDER BY c.sortorder', $params));
/*$courseDetails = array_values($DB->get_records_sql("SELECT c.id, ue.userid, c.fullname, q.name FROM {course} c JOIN {enrol} e ON c.id = e.courseid JOIN {user_enrolments} ue ON e.id = ue.enrolid JOIN {quiz} q ON c.id=q.course AND q.name NOT LIKE 'aa:%' AND q.name NOT LIKE 'bb:%' WHERE ue.userid=? AND c.category=? ORDER BY c.sortorder", $params));*/
$courseDetails = array_values($DB->get_records_sql("SELECT c.id, ue.userid, c.fullname, q.name FROM {course} c JOIN {enrol} e ON c.id = e.courseid JOIN {user_enrolments} ue ON e.id = ue.enrolid JOIN {quiz} q ON c.id=q.course AND q.name NOT LIKE 'aa:%' AND q.name NOT LIKE 'bb:%' WHERE ue.userid=? AND c.category=? AND c.visible = 1 ORDER BY c.sortorder", $params));
$Coursecnt = count($courseDetails);
for ($i = 0; $i < $Coursecnt; $i++) {
$params_usercourse = array('course' => $courseDetails[$i]->id, 'userid' => $uid);
$params_course = array('course' => $courseDetails[$i]->id);
$currentUserScore = array_values($DB->get_records_sql("SELECT scores.qid AS qid, scores.id AS id, scores.userid AS userid, SUM(scores.sumgrades) AS score FROM\n\t\t\t\t(\n\t\t\t\tSELECT q.id AS qid, qa.quiz, qa.id AS id,qa.userid AS userid, MAX(qa.sumgrades) AS sumgrades\n\t\t\t\tFROM {quiz} q\n\t\t\t\tINNER JOIN {quiz_attempts} qa ON qa.quiz=q.id AND q.name NOT LIKE 'aa:%' AND q.name NOT LIKE 'bb:%'\n\t\t\t\tWHERE q.course=? AND qa.userid=? AND qa.state='finished' \n\t\t\t\tGROUP BY qa.userid, qa.quiz\n\t\t\t\t) scores\n\t\t\t\tGROUP BY scores.userid", $params_usercourse));
$CourseDetails['course_id'][] = $courseDetails[$i]->id;
$CourseDetails['course_name'][] = $courseDetails[$i]->fullname;
$CourseDetails['course_score'][] = $currentUserScore[0]->score;
$totalscore = $totalscore + $currentUserScore[0]->score;
$quizedetails = array_values($DB->get_records_sql('SELECT q.id,q.name FROM {quiz} q WHERE q.course=? ', $params_course));
$quizecnt = count($quizedetails);
for ($j = 0; $j < $quizecnt; $j++) {
$params_quiz = array('quiz' => $quizedetails[$j]->id, 'userid' => $uid);
$quizescoredetails = array_values($DB->get_records_sql('SELECT MAX(qa.sumgrades) as sumgrades FROM {quiz_attempts} qa WHERE qa.quiz=? AND qa.userid=?', $params_quiz));
$CourseDetails['course'][$courseDetails[$i]->id]['quiz']['name'][] = $quizedetails[$j]->name;
$CourseDetails['course'][$courseDetails[$i]->id]['quiz']['score'][] = $quizescoredetails[0]->sumgrades;
}
}
$list_of_subcategories = $DB->get_records('course_categories', array('parent' => $getCrseCat->id));
if ($list_of_subcategories) {
foreach ($list_of_subcategories as $kSubCat => $vSubCat) {
$params = array('userid' => $uid, 'category' => $vSubCat->id);
//$courseDetails = array_values($DB->get_records_sql('SELECT c.id, c.fullname FROM {course} c, {enrol} e, {user_enrolments} ue WHERE c.id = e.courseid AND e.id = ue.enrolid AND ue.userid=? AND c.category=? ORDER BY c.sortorder', $params));
/**$courseDetails = array_values($DB->get_records_sql("SELECT c.id, ue.userid, c.fullname, q.name FROM {course} c JOIN {enrol} e ON c.id = e.courseid JOIN {user_enrolments} ue ON e.id = ue.enrolid JOIN {quiz} q ON c.id=q.course AND q.name NOT LIKE 'aa:%' AND q.name NOT LIKE 'bb:%' WHERE ue.userid=? AND c.category=? ORDER BY c.sortorder", $params));*/
$courseDetails = array_values($DB->get_records_sql("SELECT c.id, ue.userid, c.fullname, q.name FROM {course} c JOIN {enrol} e ON c.id = e.courseid JOIN {user_enrolments} ue ON e.id = ue.enrolid JOIN {quiz} q ON c.id=q.course AND q.name NOT LIKE 'aa:%' AND q.name NOT LIKE 'bb:%' WHERE ue.userid=? AND c.category=? AND c.visible = 1 ORDER BY c.sortorder", $params));
$Coursecnt = count($courseDetails);
for ($i = 0; $i < $Coursecnt; $i++) {
$params_usercourse = array('course' => $courseDetails[$i]->id, 'userid' => $uid);
$params_course = array('course' => $courseDetails[$i]->id);
$currentUserScore = array_values($DB->get_records_sql("SELECT scores.qid AS qid, scores.id AS id, scores.userid AS userid, SUM(scores.sumgrades) AS score FROM\n\t\t\t\t\t\t(\n\t\t\t\t\t\tSELECT q.id AS qid, qa.quiz, qa.id AS id,qa.userid AS userid, MAX(qa.sumgrades) AS sumgrades\n\t\t\t\t\t\tFROM {quiz} q\n\t\t\t\t\t\tINNER JOIN {quiz_attempts} qa ON qa.quiz=q.id AND q.name NOT LIKE 'aa:%' AND q.name NOT LIKE 'bb:%'\n\t\t\t\t\t\tWHERE q.course=? AND qa.userid=? AND qa.state='finished' \n\t\t\t\t\t\tGROUP BY qa.userid, qa.quiz\n\t\t\t\t\t\t) scores\n\t\t\t\t\t\tGROUP BY scores.userid", $params_usercourse));
$CourseDetails['course_id'][] = $courseDetails[$i]->id;
$CourseDetails['course_name'][] = $courseDetails[$i]->fullname;
$CourseDetails['course_score'][] = $currentUserScore[0]->score;
$totalscore = $totalscore + $currentUserScore[0]->score;
$quizedetails = array_values($DB->get_records_sql('SELECT q.id,q.name FROM {quiz} q WHERE q.course=? ', $params_course));
$quizecnt = count($quizedetails);
for ($j = 0; $j < $quizecnt; $j++) {
$params_quiz = array('quiz' => $quizedetails[$j]->id, 'userid' => $uid);
$quizescoredetails = array_values($DB->get_records_sql('SELECT MAX(qa.sumgrades) as sumgrades FROM {quiz_attempts} qa WHERE qa.quiz=? AND qa.userid=?', $params_quiz));
$CourseDetails['course'][$courseDetails[$i]->id]['quiz']['name'][] = $quizedetails[$j]->name;
$CourseDetails['course'][$courseDetails[$i]->id]['quiz']['score'][] = $quizescoredetails[0]->sumgrades;
}
}
}
}
$CourseDetails['totalscore'] = $totalscore;
//Add custom scores for 1331 US users
$custompoint = self::getCustomPoints($uid);
if (!empty($custompoint->id)) {
// Does addition score exists for user? add them too.
$CourseDetails['totalscore'] += $custompoint->points;
}
if (empty($return)) {
if (!empty($CourseDetails)) {
$response->response(false, 'done', $CourseDetails);
} else {
$response->response(true, 'no_records');
}
}
return $CourseDetails;
}
}
private static function __verify_token()
{
$userId = self::get_user_id();
if (empty($userId)) {
$response = new CliniqueServiceResponce();
$response->response(false, 'Invalid user');
die;
}
}
public static function __insertReplaceCourseResourceComment($userid, $coursemoduleid, $type, $comment)
{
global $CFG, $DB;
$commentRecord = $DB->get_record('user_resource_comments', array('userid' => $userid, 'type' => $type, 'coursemoduleid' => $coursemoduleid));
$record = new stdClass();
$record->comment = $comment;
if (empty($commentRecord->id)) {
$record->userid = $userid;
$record->coursemoduleid = $coursemoduleid;
$record->type = $type;
$ret = $DB->insert_record('user_resource_comments', $record);
} else {
$record->id = $commentRecord->id;
$ret = $DB->update_record('user_resource_comments', $record, $bulk = false);
}
if (!empty($ret)) {
$comment_data = array('status' => 'success');
} else {
$comment_data = array('status' => 'failure');
}
$response = new CliniqueServiceResponce();
$response->response(false, null, $comment_data);
}
public function __analyseUserSearchfield($type, $region, $country, $retailer, $filter)
{
global $CFG, $DB;
$systemcontext = context_system::instance();
$response = new CliniqueServiceResponce();
switch ($type) {
case 'region':
$regionname = array_values($DB->get_records_sql('SELECT DISTINCT(cr.region) FROM {cascade_region} cr WHERE cr.region != "" ORDER BY cr.region ASC'));
break;
case 'country':
$where = $filter == 1 ? ' WHERE cr.region=? ORDER BY cr.country ASC' : ' ORDER BY cr.country ASC';
$params = $filter == 1 ? array('region' => $region) : array();
$countryname = array_values($DB->get_records_sql('SELECT DISTINCT(cr.country),c.country_code AS code FROM {cascade_region} cr LEFT JOIN {country} c ON c.country_name=cr.country' . $where, $params));
break;
case 'retailer':
$where = $filter == 1 ? ' WHERE cr.region=? AND cr.country=? ORDER BY cr.retailer ASC' : ' ORDER BY cr.retailer ASC';
$params = $filter == 1 ? array('region' => $region, 'country' => $country) : array();
$retailername = array_values($DB->get_records_sql('SELECT DISTINCT(cr.retailer) FROM {cascade_region} cr' . $where, $params));
break;
case 'store':
$where = $filter == 1 ? ' WHERE cr.region=? AND cr.country=? AND cr.retailer=? ORDER BY cr.store ASC' : ' ORDER BY cr.store ASC';
$params = $filter == 1 ? array('region' => $region, 'country' => $country, 'retailer' => $retailer) : array();
$storename = array_values($DB->get_records_sql('SELECT DISTINCT(cr.store) FROM {cascade_region} cr' . $where, $params));
break;
}
$Searchfield = array();
//Retailer:
$retailerDetails = array_values($DB->get_records_sql("SELECT uid.data FROM {user_info_field} uif, {user_info_data} uid WHERE uif.id = uid.fieldid AND LOWER(uif.shortname) = 'retailer' AND uid.data != '' GROUP BY uid.data ORDER BY uid.data ASC"));
$registeredretailer = json_decode(json_encode($retailerDetails), true);
$allretailers = json_decode(json_encode($retailername), true);
$registeredretailerres = array();
// initialize result
array_walk_recursive($registeredretailer, function ($item) use(&$registeredretailerres) {
// flatten the array
$registeredretailerres[] = trim($item);
});
$allretailersres = array();
// initialize result
array_walk_recursive($allretailers, function ($item) use(&$allretailersres) {
// flatten the array
$allretailersres[] = trim($item);
});
$retailerconcat = array_intersect($registeredretailerres, $allretailersres);
$Searchfield['retailer'] = $retailerconcat;
//Store:
$storeDetails = array_values($DB->get_records_sql("SELECT uid.data AS store FROM {user_info_field} uif, {user_info_data} uid WHERE uif.id = uid.fieldid AND LOWER(uif.shortname) = 'store' AND uid.data != '' GROUP BY uid.data ORDER BY uid.data ASC"));
$registeredstore = json_decode(json_encode($storeDetails), true);
$allstores = json_decode(json_encode($storename), true);
$registeredstoreres = array();
// initialize result
array_walk_recursive($registeredstore, function ($item) use(&$registeredstoreres) {
// flatten the array
$registeredstoreres[] = trim($item);
});
$allstoresres = array();
// initialize result
array_walk_recursive($allstores, function ($item) use(&$allstoresres) {
// flatten the array
$allstoresres[] = trim($item);
});
$storeconcat = array_intersect($registeredstoreres, $allstoresres);
$Searchfield['store'] = $storeconcat;
//Region:
$regionDetails = array_values($DB->get_records_sql("SELECT uid.data FROM {user_info_field} uif, {user_info_data} uid WHERE uif.id = uid.fieldid AND LOWER(uif.shortname) = 'region' AND uid.data != '' GROUP BY uid.data ORDER BY uid.data ASC"));
$Searchfield['region'] = $regionname;
//Country:
$Searchfield['country'] = $countryname;
$cohortDetails = $DB->get_records_sql("SELECT c.id, c.name FROM {cohort} c ORDER BY c.name ASC");
$Searchfield['team'] = $cohortDetails;
if (!empty($Searchfield)) {
$response->response(false, 'done', $Searchfield);
} else {
$response->response(true, 'no_records');
}
}
public function __CreateUser($username, $password, $firstname, $lastname, $email, $city, $country, $store, $retailer, $lang, $regionkey, $jobtitle, $insertflag, $other)
{
global $CFG, $DB;
$systemcontext = context_system::instance();
require_once $CFG->dirroot . '/cohort/locallib.php';
// cohort members added courses.
$response = new CliniqueServiceResponce();
$flag = true;
$popupflag = true;
$user_exits = array_values($DB->get_records_sql("SELECT count(*) as totaluser FROM {user} WHERE username='******'"));
$user_email_exists = array_values($DB->get_records_sql("SELECT count(*) as totalemail FROM {user} WHERE email='{$email}'"));
$regionkey_check = array_values($DB->get_records_sql("SELECT count(*) as Regkeyexists FROM {regionkey} WHERE regionkey='{$regionkey}' AND region='{$city}'"));
$userDetails = new stdClass();
// $password = "******";
if ($user_exits[0]->totaluser > 0) {
$response->response(true, 'msg', "username_exists");
$flag = false;
$popupflag = false;
exit;
}
/*if($user_email_exists[0]->totalemail > 0){
$response->response(true, 'msg', "email_exists");
$flag=false;
$popupflag = false;
exit;
}*/
if ($regionkey_check['0']->regkeyexists == 0) {
$response->response(true, 'msg', "regionkey_not_exists");
$flag = false;
$popupflag = false;
exit;
}
if ($popupflag && $insertflag == 'false') {
$response->response(false, 'msg', "show_popup");
$flag = false;
exit;
}
if ($flag && $insertflag == 'true') {
$userDetails->auth = 'email';
$userDetails->confirmed = '1';
$userDetails->mnethostid = '1';
$userDetails->descriptionformat = '1';
$userDetails->descriptionformat = '1';
$userDetails->username = $username;
$password = base64_decode($password);
$md5pass_val = array("id" => "md5");
if (!empty($CFG->passwordsaltmain)) {
$newpass = $password . $CFG->passwordsaltmain;
$user_pass = array_values($DB->get_records_all_sql("SELECT md5('{$newpass}') as password", $md5pass_val));
$userDetails->password = $user_pass[0]->password;
} else {
$userDetails->password = md5($password);
}
$userDetails->firstname = $firstname;
$userDetails->lastname = $lastname;
$userDetails->email = $email;
$userDetails->city = $city;
$userDetails->country = $country;
if ($lang == 'zh_ct') {
$userDetails->lang = 'zh_cn';
} else {
$userDetails->lang = $lang;
}
$userDetails->timecreated = time();
$userDetails->timemodified = time();
//$userDetails->store=$store;
//$userDetails->retailer=$retailer;
$userDetails->descriptionformat = '1';
$lastinsertid = $DB->insert_record('user', $userDetails);
$cohortid = array_values($DB->get_records_sql("SELECT id FROM {cohort} WHERE idnumber='{$lang}'"));
$cohortDetails = new stdClass();
$cohortDetails->cohortid = $cohortid[0]->id;
$cohortDetails->userid = $lastinsertid;
$cohortDetails->timeadded = time();
// if($userDetails->lang == COUNTER_MANAGER_ISO) {
// cohort_add_member(US_ENGLISH, $lastinsertid);
// }
cohort_add_member($cohortid[0]->id, $lastinsertid);
//$DB->insert_record('cohort_members',$cohortDetails);
if ($other) {
$cascade_data = new stdClass();
$get_country = $DB->get_record('country', array('country_code' => $country));
$cascade_data->region = $city;
$cascade_data->country = $get_country->country_name;
$cascade_data->retailer = $retailer;
$cascade_data->store = $store;
$cascade_insert_id = $DB->insert_record('cascade_region', $cascade_data);
}
if ($store != '' && !empty($store)) {
$storeId = array_values($DB->get_records_sql("SELECT id FROM {user_info_field} WHERE shortname='Store'"));
//Store,
$user_info_data->fieldid = $storeId['0']->id;
$user_info_data->userid = $lastinsertid;
$user_info_data->data = $store;
$DB->insert_record('user_info_data', $user_info_data);
}
if ($retailer != '' && !empty($retailer)) {
//Retailer
$retailerId = array_values($DB->get_records_sql("SELECT id FROM {user_info_field} WHERE shortname='Retailer'"));
//Retailer
$user_info_data->fieldid = $retailerId['0']->id;
$user_info_data->userid = $lastinsertid;
$user_info_data->data = $retailer;
$DB->insert_record('user_info_data', $user_info_data);
}
if ($city != '' && !empty($city)) {
// Region insert query
$regionId = array_values($DB->get_records_sql("SELECT id FROM {user_info_field} WHERE shortname='Region'"));
//Region
$user_info_data->fieldid = $regionId['0']->id;
$user_info_data->userid = $lastinsertid;
$user_info_data->data = $city;
$DB->insert_record('user_info_data', $user_info_data);
}
if ($jobtitle != '' && !empty($jobtitle)) {
// Region insert query
$jobtitleId = array_values($DB->get_records_sql("SELECT id FROM {user_info_field} WHERE shortname='JobTitle'"));
//JobTitle
$user_info_data->fieldid = $jobtitleId['0']->id;
$user_info_data->userid = $lastinsertid;
$user_info_data->data = $jobtitle;
$DB->insert_record('user_info_data', $user_info_data);
} else {
$jobtitleId = array_values($DB->get_records_sql("SELECT id FROM {user_info_field} WHERE shortname='JobTitle'"));
//JobTitle
$user_info_data->fieldid = $jobtitleId['0']->id;
$user_info_data->userid = $lastinsertid;
$user_info_data->data = 0;
$DB->insert_record('user_info_data', $user_info_data);
}
if ($lastinsertid) {
$response->response(false, 'msg', "Added user successfully");
} else {
$response->response(true, 'msg', "Error in creating user");
}
}
/* if(!empty($responseArray)){
$response->response(false, 'done', $responseArray);
} else{
$response->response(true, 'msg', "No records");
}*/
}
public function __analyseReports($type, $retailer, $store, $region, $country, $course, $team, $limit_start, $limit_end, $sortBy)
{
global $CFG, $DB;
$systemcontext = context_system::instance();
$response = new CliniqueServiceResponce();
if ($type == 'user' || $type == 'course') {
$where = "WHERE id!=''";
if ($region != '') {
$where .= " AND region='" . $region . "'";
}
if ($country != '') {
$where .= " AND country='" . $country . "'";
}
if ($retailer != '' && $retailer != 'null') {
/*if($retailer == '') {
$where .=" AND retailer =''";
} else {*/
foreach ($retailer as $key => $value) {
$retailerstr .= '"' . $value . '",';
}
$retailersrtval = substr($retailerstr, 0, -1);
$where .= " AND retailer IN(" . $retailersrtval . ")";
//}
}
if ($store != '' && $store != 'null') {
/*if($store == '') {
$where .=" AND store =''";
} else { */
foreach ($store as $key => $value) {
$storestr .= '"' . $value . '",';
}
$storesrtval = substr($storestr, 0, -1);
$where .= " AND store IN(" . $storesrtval . ")";
//}
}
if ($limit_start != '') {
$start = $limit_start;
} else {
$start = 0;
}
if ($limit_end != '') {
$end = $limit_end;
} else {
$end = 9;
}
if ($sortBy != "") {
// sortby asc desc
if ($sortBy == 'firstname DESC') {
$sort = "firstname DESC";
}
if ($sortBy == 'firstname ASC') {
$sort = "firstname ASC";
}
if ($sortBy == 'lastname DESC') {
$sort = "lastname DESC";
}
if ($sortBy == 'lastname ASC') {
$sort = "lastname ASC";
}
if ($sortBy == 'fullname DESC') {
$sort = "fullname DESC";
}
if ($sortBy == 'fullname ASC') {
$sort = "fullname ASC";
}
if ($sortBy == 'points ASC') {
$sort = "points ASC";
}
if ($sortBy == 'points DESC') {
$sort = "points DESC";
}
if ($sortBy == 'totalpoints ASC') {
$sort = "totalpoints ASC";
}
if ($sortBy == 'totalpoints DESC') {
$sort = "totalpoints DESC";
}
} else {
$sort = "firstname ASC";
}
/*$where = array();
if($retailer != '') {
$retailer = str_replace("*", "#", $retailer);
$where[] = "ud.data = '".mysql_escape_string($retailer)."'";
$retailercd = mysql_escape_string($retailer);
}
if($store != '') {
$store = str_replace("*", "#", $store);
$where[] = "ud.data = '".mysql_escape_string($store)."'";
$storecd = mysql_escape_string($store);
}
if($region != '') {
$where[] = "ud.data = '".mysql_escape_string($region)."'";
$regioncd = mysql_escape_string($region);
}
if($limit_start != '') {
$start = $limit_start;
} else {
$start = 0;
}
if($limit_end != '') {
$end = $limit_end;
} else {
$end = 9;
}
if($sortBy!=""){ // sortby asc desc
if($sortBy=='firstname DESC')
$sort = "firstname DESC";
if($sortBy=='firstname ASC')
$sort = "firstname ASC";
if($sortBy=='lastname DESC')
$sort = "lastname DESC";
if($sortBy=='lastname ASC')
$sort = "lastname ASC";
if($sortBy=='fullname DESC')
$sort = "fullname DESC";
if($sortBy=='fullname ASC')
$sort = "fullname ASC";
if($sortBy=='points ASC')
$sort = "points ASC";
if($sortBy=='points DESC')
$sort = "points DESC";
if($sortBy=='totalpoints ASC')
$sort = "totalpoints ASC";
if($sortBy=='totalpoints DESC')
$sort = "totalpoints DESC";
}else{
$sort = "firstname ASC";
}*/
/*if($type=="user"){
$teamjoin =" LEFT JOIN {cohort_members} cm ON cm.userid=u.id LEFT JOIN {cohort} ch ON ch.id=cm.cohortid";
if($team != '') {
$team_where[] = "name='".mysql_escape_string($team)."'";
$where_str_filter = " ( ".implode(" OR ", $team_where) .")";
$team_user_id= array_values($DB->get_records_sql("SELECT cm.userid FROM {cohort} ch LEFT JOIN {cohort_members} cm ON cm.cohortid = ch.id WHERE $where_str_filter"));
if($team_user_id[0]->userid == '') {
$team_user_id[0]->userid = '0';
}
foreach($team_user_id as $team_user_ids){
$teamUserIds .= $team_user_ids->userid .",";
}
$teamUser_ids = substr($teamUserIds,0,-1);
$where_str = " AND uid IN( $teamUser_ids )";
if(empty($teamUser_ids))
$teamUser_ids = "No";
}else{
$team_where_str ="";
}
}else{
$teamjoin = "";
}*/
/*if(!empty($where)){
//$where_str_filter = " AND ( ".implode(" OR ", $where) .")";
$where_str_filter = " AND ( ".implode(" AND ", $where) .")";
//echo $where_str_filter;exit;
/*$user_id= array_values($DB->get_records_sql("SELECT userid FROM {user_info_data} ud WHERE ud.userid!='' $where_str_filter"));
foreach($user_id as $user_ids){
$userIds .= $user_ids->userid .",";
}
$user_ids = substr($userIds,0,-1);
$retailerfield = '';
$regionfield = '';
$storefield = '';
if($retailer != '') {
$retailerfield ="AND DATA = '".$retailercd."'";
}
if($region != '') {
$regionfield ="AND DATA = '".$regioncd."'";
}
if($store != '') {
$storefield ="AND DATA = '".$storecd."'";
}
$region_id= array_values($DB->get_records_sql("SELECT id FROM {user_info_field} uf WHERE uf.shortname LIKE '%Region%'"));
$store_id= array_values($DB->get_records_sql("SELECT id FROM {user_info_field} uf WHERE uf.shortname LIKE '%Store%'"));
$retaile_id= array_values($DB->get_records_sql("SELECT id FROM {user_info_field} uf WHERE uf.shortname LIKE '%Retailer%'"));
$user_id= array_values($DB->get_records_sql("SELECT TBLStore.userid FROM
(SELECT userid, fieldid, DATA FROM mdl_user_info_data
WHERE fieldid = ".$store_id[0]->id." ".$storefield.") TBLStore,
(SELECT userid, fieldid, DATA FROM mdl_user_info_data
WHERE fieldid = ".$region_id[0]->id." ".$regionfield.") TBLRegion,
(SELECT userid, fieldid, DATA FROM mdl_user_info_data
WHERE fieldid = ".$retaile_id[0]->id." ".$retailerfield.") TBLRtlr
WHERE TBLStore.userid = TBLRegion.userid
AND TBLRegion.userid = TBLRtlr.userid"));
/*echo "<br>11111=>"."SELECT TBLStore.userid FROM
(SELECT userid, fieldid, DATA FROM mdl_user_info_data
WHERE fieldid = ".$store_id[0]->id." ".$storefield.") TBLStore,
(SELECT userid, fieldid, DATA FROM mdl_user_info_data
WHERE fieldid = ".$region_id[0]->id." ".$regionfield.") TBLRegion,
(SELECT userid, fieldid, DATA FROM mdl_user_info_data
WHERE fieldid = ".$retaile_id[0]->id." ".$retailerfield.") TBLRtlr
WHERE TBLStore.userid = TBLRegion.userid
AND TBLRegion.userid = TBLRtlr.userid";
print_r($user_id); exit;
foreach($user_id as $user_ids){
$userIds .= $user_ids->userid .",";
}
$user_ids = substr($userIds,0,-1);
if(!empty($teamUser_ids)){
//$where_str = " AND u.id IN( $user_ids , $teamUser_ids)";
//$where_total= " AND u.id IN( $user_ids , $teamUser_ids)";
if($user_ids!='' && $teamUser_ids!='' && $teamUser_ids!='No') {
//$where_reports = " AND uid IN ($user_ids, $teamUser_ids)";
//$where_reports = " AND uid IN ($user_ids) AND uid IN ($teamUser_ids)";
$teamexp = explode(',', $teamUser_ids);
$userexp = explode(',', $user_ids);
$interset_user_ids = array_intersect($teamexp, $userexp);
if(!empty($interset_user_ids)) {
$struser_ids = implode(',',$interset_user_ids);
} else {
$struser_ids = 0;
}
$where_reports = " AND uid IN ($struser_ids)";
} elseif($user_ids!='') {
$where_reports = " AND uid IN ($user_ids) AND uid IN (0)";
}elseif($teamUser_ids!='' && $teamUser_ids!='No'){
$where_reports = " AND uid IN ($teamUser_ids)";
}
}elseif(!empty($user_ids)){
//$where_str = " AND u.id IN( $user_ids )";
//$where_total = " AND u.id IN( $user_ids )";
//$where_scores = " AND qa.userid IN ($user_ids)";
$where_reports = " AND uid IN ($user_ids)";
}else{
$where_reports = " AND uid=0";
}
}elseif(!empty($teamUser_ids) && empty($user_ids)){
if($teamUser_ids == "No")
$where_reports = " AND uid = 0";
else
$where_reports = " AND uid IN ($teamUser_ids)";
}else{
//$where_str = " AND u.id!=''";
//$where_total= " AND qa.userid!=''";
//$where_scores = " AND qa.userid !=''";
$where_reports = " AND uid!=''";
}
if($country != '') {
$where_reports .= " AND country = '".$country."'";
}*/
/*$searchDetails = array_values($DB->get_records_sql("SELECT c.fullname,ui.data,u.id,u.firstname,u.lastname,c.id as cid FROM {user} u $teamjoin,{user_info_data} ui , {enrol} e,{user_enrolments} ue ,{course} c WHERE e.courseid=c.id AND ue.userid= ui.userid AND ue.enrolid=e.id AND u.id=ui.userid AND ui.fieldid=(SELECT id FROM {user_info_field} WHERE shortname='JobTitle')"));*/
//$searchDetails = array_values($DB->get_records_all_sql("SELECT c.id as cid,c.fullname,u.id,u.firstname,u.lastname,u.country FROM {user} u $teamjoin, {enrol} e,{user_enrolments} ue ,{course} c WHERE e.courseid=c.id AND ue.userid= u.id AND ue.enrolid=e.id"));
//$reportsCount = array_values($DB->get_records_sql("SELECT * FROM {reports} WHERE id!='' $where_reports ORDER BY $sort"));
//$jobtitle = array_values($DB->get_records_sql("SELECT ud.data FROM {user_info_field} ui LEFT JOIN {user_info_data} ud ON ud.fieldid=ui.id WHERE ui.shortname='JobTitle' AND ud.userid=$userId"));
//echo "<pre>"; //print_r($searchDetails); exit;
/*if(count($searchDetails) != count($reportsCount)){
$DB->delete_records('reports'); // Delete records from mdl_reports table
for ($i = 0; $i < count($searchDetails); $i++) {
$params_usercourse = array('course' => $searchDetails[$i]->cid, 'userid' => $searchDetails[$i]->id);
$params_course = array('course' => $searchDetails[$i]->cid);
$userId = $searchDetails[$i]->id;
$jobtitle = array_values($DB->get_records_sql("SELECT ud.data FROM {user_info_field} ui LEFT JOIN {user_info_data} ud ON ud.fieldid=ui.id WHERE ui.shortname='JobTitle' AND ud.userid=$userId"));
$currentUserScore = array_values($DB->get_records_sql('SELECT scores.qid AS qid, scores.id AS id, scores.userid AS userid, SUM(scores.sumgrades) AS score FROM
(
SELECT q.id AS qid, qa.quiz, qa.id AS id,qa.userid AS userid, MAX(qa.sumgrades) AS sumgrades
FROM {quiz} q
INNER JOIN {quiz_attempts} qa ON qa.quiz=q.id
WHERE q.course=? AND qa.userid=? AND qa.state="finished"
GROUP BY qa.userid, qa.quiz
) scores
GROUP BY scores.userid', $params_usercourse));
$CourseDetails->cid = $searchDetails[$i]->cid;
$CourseDetails->uid = $searchDetails[$i]->id;
$CourseDetails->firstname = $searchDetails[$i]->firstname;
$CourseDetails->lastname = $searchDetails[$i]->lastname;
$CourseDetails->country = $searchDetails[$i]->country;
$CourseDetails->jobtitle = $jobtitle[0]->data;
$CourseDetails->fullname = $searchDetails[$i]->fullname;
if(empty($currentUserScore[0]->score)){
$currentUserScore[0]->score = 0;
}
$CourseDetails->points = $currentUserScore[0]->score;
$DB->insert_record('reports',$CourseDetails); // DB insert query for course
}
$report_point = array_values($DB->get_records_sql("SELECT uid,sum(points) as total FROM {reports} WHERE id!='' AND points!='' GROUP BY uid"));
foreach($report_point as $report_points){
$DB->execute("update mdl_reports SET totalpoints=$report_points->total WHERE uid=$report_points->uid");
}
}*/
$reportsArray = array();
//echo "SELECT * FROM {reports} $where ORDER BY $sort LIMIT $start,$end";
$reportsDetails = array_values($DB->get_records_sql("SELECT * FROM {reports} {$where} ORDER BY {$sort} LIMIT {$start},{$end}"));
//echo "SELECT * FROM {reports} $where ORDER BY $sort";
$reportsCount = array_values($DB->get_records_sql("SELECT count(1) AS cnt FROM {reports} {$where} ORDER BY {$sort}"));
//echo "here2";
$reportsArray['data'] = $reportsDetails;
$reportsArray['totalcount'] = $reportsCount[0]->cnt;
}
if (!empty($reportsDetails)) {
$response->response(false, 'done', $reportsArray);
} else {
$response->response(true, 'No Records');
}
}
public function __favorites($tokenval)
{
global $CFG, $DB;
$response = new CliniqueServiceResponce();
//$username = "******";
//$password = "******";
$token_val = array('token' => $tokenval);
//$token_val = array('token'=>"64217bb7eea6f343a19f3af17104c49b");
$userId = array_values($DB->get_records_sql('SELECT userid FROM {external_tokens} et WHERE et.token=?', $token_val));
if ($userId) {
$user_id = array('id' => $userId[0]->userid);
//if(confirm_sesskey()){
$user = array_values($DB->get_records_sql('SELECT * FROM {user} u WHERE u.id=?', $user_id));
Favorites::__fav_user_login($user['0']);
$favourites_user = get_user_preferences('user_bookmarks');
$favourites_user = explode(',', get_user_preferences('user_bookmarks'));
/// Accessibility: markup as a list.
$favBookmark = array();
$incr = 0;
//print_r($favourites_user);
foreach ($favourites_user as $favourite_bookmark) {
//$favBookmark = explode(';', $favourite_bookmark);
//echo json_encode($favBookmark); // return json format
//$response->response(false, $favBookmark);
$data = explode(';', $favourite_bookmark);
$id = $data[0] ? $data[0] : null;
$fileDetails = explode("@", $data[1]);
if ($fileDetails[0]) {
$getModInfo = $DB->get_record('course_modules', array('id' => $fileDetails[0]));
} else {
$getModInfo = '';
}
if ($getModInfo) {
$res[$incr]['url'] = $data[0];
$res[$incr]['id'] = $fileDetails[0] ? $fileDetails[0] : null;
$res[$incr]['course_type'] = $fileDetails[1] ? $fileDetails[1] : null;
$res[$incr]['file_name'] = $fileDetails[2] ? $fileDetails[2] : null;
$res[$incr]['file_type'] = $fileDetails[3] ? $fileDetails[3] : null;
$res[$incr]['fname_upload'] = $fileDetails[4] ? $fileDetails[4] : null;
$incr++;
}
}
if ($incr == 0) {
$res[$incr]['url'] = "";
$res[$incr]['id'] = null;
$res[$incr]['course_type'] = null;
$res[$incr]['file_name'] = null;
$res[$incr]['file_type'] = null;
$res[$incr]['fname_upload'] = null;
}
$fav_comment_count_sql = 'SELECT COUNT(urc.id) AS comment_count
FROM mdl_user_resource_comments urc
JOIN mdl_course_modules cm ON cm.id = urc.coursemoduleid
JOIN mdl_course c ON cm.course = c.id
JOIN mdl_resource r ON cm.instance = r.id
WHERE urc.userid = ' . $user_id['id'];
$comments_count = current($DB->get_records_sql($fav_comment_count_sql));
$res['resource_comment_count'] = $comments_count->comment_count;
$response->response(false, 'success', $res);
} else {
$response->response(true, 'Invalid user');
}
}
public function __WidgetMultidisplay($courseid, $widgettype, $modid, $return = false)
{
global $CFG, $DB;
$response = new CliniqueServiceResponce();
if ($widgettype == 'Multi') {
$questionArray = array();
$module = $DB->get_record_sql("SELECT cm.id, cm.course, cm.instance, q.id, q.questions, q.name, q.intro, q.timemodified FROM {course_modules} cm JOIN {quiz} q ON cm.instance = q.id WHERE cm.course = " . $courseid . " AND cm.id = " . $modid);
if ($module) {
$questions_id = $module->questions;
$questionname = $module->intro;
$questionArray['questionname'][] = $questionname;
$questionArray['timemodified'] = $module->timemodified;
$module_context = context_module::instance($modid);
$intro = file_rewrite_pluginfile_urls($module->intro, 'pluginfile.php', $module_context->id, 'mod_quiz', 'intro');
$questionArray['tile_content'][] = self::getTextBetweenTags(html_entity_decode($intro), 'tile');
$questionArray['heading'][] = self::getTextBetweenTags(html_entity_decode($intro), 'heading');
$split_ids_by_page = explode(",0", $questions_id);
$split_ids_by_page = array_values(array_filter($split_ids_by_page));
$all_ids = array();
foreach ($split_ids_by_page as $split_ids) {
$split_ids = explode(",", $split_ids);
$split_ids = array_values(array_filter($split_ids));
$all_ids = array_merge($all_ids, $split_ids);
}
foreach ($all_ids as $allid) {
$get_question = $DB->get_record('question', array('id' => $allid));
$get_answers = $DB->get_record('question_multichoice', array('question' => $allid));
$get_answer_text = $DB->get_records_sql('SELECT * FROM {question_answers} WHERE id IN (' . $get_answers->answers . ')');
$get_context_id = $DB->get_records_sql("SELECT qc.id, qc.contextid FROM {question} q JOIN {question_categories} qc ON q.category = qc.id WHERE q.id = " . $allid);
foreach ($get_context_id as $context) {
$context_id = $context->contextid;
}
$sql_usageid = array_values($DB->get_records_sql("SELECT max(questionusageid) as usageid, questionid, slot FROM {question_attempts} WHERE questionid=" . $allid));
$split_imagevalue = explode("@@/", $get_question->questiontext);
$split_value = explode('"', $split_imagevalue['1']);
$questionArray['questiontext'][$get_question->id] = $CFG->wwwroot . "/pluginfile.php/" . $context_id . "/question/questiontext/" . $sql_usageid['0']->usageid . "/" . $sql_usageid['0']->slot . "/" . $allid . "/" . $split_value['0'];
$answer = array();
$right = 0;
$wrong = 0;
foreach ($get_answer_text as $answer_txt) {
if ($answer_txt->fraction > 0) {
$questionArray['answertext'][$answer_txt->question]['right_answers'][] = strip_tags($answer_txt->answer);
$right++;
$questionArray['answertext'][$answer_txt->question]['right_answers_count'] = $right;
} else {
$questionArray['answertext'][$answer_txt->question]['wrong_answers'][] = strip_tags($answer_txt->answer);
$wrong++;
$questionArray['answertext'][$answer_txt->question]['wrong_answers_count'] = $wrong;
}
}
}
}
if (empty($return)) {
$response = new CliniqueServiceResponce();
if (!empty($questionArray)) {
$response->response(false, 'done', $questionArray);
} else {
$response->response(true, 'msg', "No records");
}
} else {
return $questionArray;
}
}
}
public static function __fetchCompleteUserData($userid, $from)
{
global $DB;
$serverTime = time();
$courses = core_enrol_external::get_users_courses_subcat_offline($userid, self::COURSE_CATEGORY_ID);
$course_enrols = "SELECT courseid, timemodified FROM (\n\t\t\tSELECT ue.id, userid, courseid, ue.timemodified FROM mdl_enrol e \n\t\t\tJOIN mdl_user_enrolments ue ON e.id = ue.enrolid\n\t\t\tWHERE ue.userid = ? ORDER BY timemodified DESC) tmp\n\t\t\tGROUP BY userid, courseid";
$user_cohort_course = $DB->get_records_sql($course_enrols, array($userid));
$userData = array();
$topics = array();
$modules = array();
$quizDelta = array();
$players = array();
$course_module_dependencies = array();
$activeCourses = array();
foreach ($courses as $i => $course) {
$moduleIds = array();
//@TODO performance - courses could have skipped when fetching from db itself rather than skipping it when iterating.
$timemodified = $course['timemodified'] > $user_cohort_course[$course['id']]->timemodified ? $course['timemodified'] : $user_cohort_course[$course['id']]->timemodified;
$isnewenrol = !empty($from) && $user_cohort_course[$course['id']]->timemodified > $from;
if (!empty($from) && $timemodified < $from) {
unset($courses[$i]);
}
$fromtimestamp = !empty($isnewenrol) ? null : $from;
try {
$topicsWithModules = core_course_external::get_course_contents($course['id'], array(), $fromtimestamp);
$topics = array_merge($topics, self::extractTopics($course['id'], $topicsWithModules, $fromtimestamp));
foreach ($topicsWithModules as $topicWithModule) {
if (isset($topicWithModule['modules']) && is_array($topicWithModule['modules'])) {
$modules = array_merge($modules, self::extractModules($course['id'], $topicWithModule['id'], $topicWithModule['modules'], $userid, $fromtimestamp));
$moduleIds = array_merge($moduleIds, self::extractModuleIds($course['id'], $topicWithModule['id'], $topicWithModule['modules'], $userid, $fromtimestamp));
$quizDelta = array_merge($quizDelta, self::extractQuizDeltaSync($course['id'], $topicWithModule['id'], $topicWithModule['modules'], $userid));
}
}
$cID = $course['id'];
$activecourse_mod = $moduleIds;
$activeCourses[] = array("id" => $cID, "modules" => $activecourse_mod);
$player = PlayersPercent::__analysePercentage($course['id'], $userid, true);
$player = array_merge(array('courseid' => $course['id']), $player);
$players[] = $player;
$module_hierarchy = self::getModuleIdsByDependency($course['id']);
if (!empty($module_hierarchy)) {
$course_module_dependencies = array_merge($course_module_dependencies, $module_hierarchy);
}
} catch (Exception $ex) {
}
}
$courses = array_values($courses);
$userData = new stdClass();
$userData->user = self::getProfileDetails($userid);
$userData->user_cohorts = self::getUserCohorts($userid);
$userData->activeCourses = array_merge($activeCourses, self::getActiveCids($userid, $from));
$userData->course_categories = core_course_external::get_categories();
$userData->courses = $courses;
$userData->topics = $topics;
$userData->modules = $modules;
$userData->module_dependencies = $course_module_dependencies;
$userData->completed_modules = self::getCompletedModuleIds($userid);
$userData->bookmarks = self::getBookmarks($userid);
$userData->notes = self::getNotes($userid, $from);
$userData->progress = ProgressPercent::__analyseProgressPercentage($userid, true);
$userData->players = $players;
$userData->favorites = Favorite::fetchAll($userid, true);
$userData->badges = BadgeDisplay::__DisplayBadges($userid, 'getBadges', null, true);
$userData->news = self::_NewsData($userid, $from);
$userData->resources = self::_ResourcesData($userid, $from);
$userData->quizsync = $quizDelta;
$userData->server_time = $serverTime;
$userData->contentsize = self::getContentSize($from, $courses, $modules);
$response = new CliniqueServiceResponce();
$response->response(false, null, $userData);
}
