| Server IP : 64.202.187.69 / Your IP : 216.73.217.74 Web Server : Apache System : Linux 69.187.202.64.host.secureserver.net 3.10.0-1160.144.1.el7.tuxcare.els6.x86_64 #1 SMP Fri May 29 16:22:24 UTC 2026 x86_64 User : transmarket ( 1001) PHP Version : 7.2.34 Disable Function : exec,passthru,shell_exec,system MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /home/transmarket/translator/controllers/ |
Upload File : |
<?php
require_once('class.db.php');
class Test_voting extends DB
{
//maximum no. of test voting sentences assigned, no. of test voting user can submit
public function getMaxTestVotingSentences() {
$max_test_voting_sentences = $this->getTestVotingSettingsValuesByColumnName( 'no_of_test_voting_sentences' );
return $max_test_voting_sentences;
}
//get test voting success percentage value - user's test voting percentage should be equal or more than this to actually start with the site and dashboard
public function getTestVotingSuccessPercentageValue( $test_voting_level ) {
//test voting level 1,2,3,4 33%
$success_percentage_value = $this->getTestVotingSettingsValuesByColumnName( 'test_voting_success_percentage' );
//if( $test_voting_level == 6 || $test_voting_level == 7 ) {
/*if( $test_voting_level == 3 || $test_voting_level == 4 ) {
//test voting level 6-7, 50%
//$success_percentage_value = 50;
$success_percentage_value = $this->getTestVotingSettingsValuesByColumnName( 'test_voting_success_percentage2' );
}*/
return $success_percentage_value;
}
//get settings values by column name
public function getTestVotingSettingsValuesByColumnName( $column_name ) {
$sql_settings_values_for = "SELECT " . $column_name . " FROM settings WHERE id = 1";
$settings_values_result = $this->con->query($sql_settings_values_for);
while ($row_settings_values = $settings_values_result->fetch_object()) {
$settings_value_for = (int)$row_settings_values->$column_name;
}
return $settings_value_for;
}
//get no. of sentences assigned to user for test voting level
public function getVotingSentnecesAssignedCount( $user_id, $test_voting_level ) {
$sql_get_assigned_voting_sentences = "SELECT * from translator_test_voting_sentences_map WHERE user_id = " . $user_id . " AND sentence_id != '' AND test_voting_level = " . $test_voting_level . " AND is_skip = 0 ";
$result_get_assigned_voting_sentences = $this->con->query( $sql_get_assigned_voting_sentences );
$user_assigned_voting_sentences_count = $result_get_assigned_voting_sentences->num_rows;
return $user_assigned_voting_sentences_count;
}
//pick random voting sentences to assign it user
public function pickRandomVotingSentencesToAssignUser( $language_id, $limit ) {
//Hindi-38, Spanish-87
//if( $language_id == 38 || $language_id == 87 ) {
//take sentences from this date. date format yyyy-mm-dd
//$test_sentences_from_date = '2021-12-06';
//get limited test voting sentences ids, last 200
$limited_test_voting_sentneces_ids = $this->getLimitedTestVotingSentencesIds( $language_id );
//$sql_get_random_voting_sentences = "SELECT id FROM `test_voting_sentences` WHERE language_id = ".$language_id." AND is_skipped = 0 AND is_other_suggestion_win = 0 AND created_on >= '".$test_sentences_from_date."' ORDER BY RAND() LIMIT " . $limit;
$sql_get_random_voting_sentences = "SELECT id FROM `test_voting_sentences` WHERE id IN (".$limited_test_voting_sentneces_ids.") ORDER BY RAND() LIMIT " . $limit;
//} else {
//$sql_get_random_voting_sentences = "SELECT id FROM `test_voting_sentences` WHERE language_id = ".$language_id." AND is_skipped = 0 AND is_other_suggestion_win = 0 ORDER BY RAND() LIMIT " . $limit;
//}
$result_random_voting_sentences = $this->con->query( $sql_get_random_voting_sentences );
return $result_random_voting_sentences;
}
//pick random voting sentences to assign it to persian user
public function pickRandomVotingSentencesToAssignPersianUser( $test_voting_level, $limit ) {
if( $test_voting_level == 1 || $test_voting_level == 2 ) {
//persian original - english suggestions
//$sql_get_limited_voting_sentences = "SELECT id FROM `test_voting_sentences` WHERE language_id = 71 AND is_skipped = 0 ORDER BY id LIMIT 99";
$sql_get_limited_voting_sentences = "SELECT id FROM `test_voting_sentences` WHERE language_id = 71 AND is_skipped = 0 AND sentence_upload_format = 'tr-en' ORDER BY id";
$result_limited_voting_sentences = $this->con->query( $sql_get_limited_voting_sentences );
} elseif( $test_voting_level == 3 || $test_voting_level == 4 ) {
//english original - persian suggestions
//$sql_get_limited_voting_sentences = "SELECT id FROM `test_voting_sentences` WHERE language_id = 71 AND is_skipped = 0 ORDER BY id LIMIT 99, 99";
$sql_get_limited_voting_sentences = "SELECT id FROM `test_voting_sentences` WHERE language_id = 71 AND is_skipped = 0 AND sentence_upload_format = 'en-tr' ORDER BY id";
$result_limited_voting_sentences = $this->con->query( $sql_get_limited_voting_sentences );
}
$limited_sentneces_id_arr = array();
if ($result_limited_voting_sentences->num_rows > 0) {
while ($row_limited_voting_sentences = $result_limited_voting_sentences->fetch_object()) {
$limited_sentneces_id_arr[]=$row_limited_voting_sentences->id;
}
}
$limited_test_sentneces_ids = implode( ",",$limited_sentneces_id_arr );
//get random 25 sentences
$sql_get_random_voting_sentences = "SELECT id FROM `test_voting_sentences` WHERE id IN (".$limited_test_sentneces_ids.") ORDER BY RAND() LIMIT " . $limit;
$result_random_voting_sentences = $this->con->query( $sql_get_random_voting_sentences );
return $result_random_voting_sentences;
}
//Save user assigned test voting sentences into the translator_test_voting_sentences_map
public function insertTranslatorTestVotingSentnecesMap( $user_id, $sentence_id, $test_voting_level ) {
$sqlInsert1 = 'INSERT INTO `translator_test_voting_sentences_map`( `user_id`, `sentence_id`, `test_voting_level` ) VALUES '
. '(' . $user_id . ',' . $sentence_id . ',' . $test_voting_level . ')';
$this->con->query($sqlInsert1);
}
//pick one user assigned but not yet voted sentence id
public function getUserAssignedTestVotingSentenceMapSentenceId( $user_id, $test_voting_level ) {
$sql_get_voting_sentence_map = "SELECT sentence_id FROM `translator_test_voting_sentences_map` WHERE user_id = ".$user_id." AND test_voting_level = ".$test_voting_level." AND voted_suggestion_id IS NULL AND is_skip = 0 order by id LIMIT 1";
$result_voting_sentence_map = $this->con->query( $sql_get_voting_sentence_map );
$sentence_id = 0;
if( $result_voting_sentence_map->num_rows > 0 ) {
while ($row_voting_sentence_map = $result_voting_sentence_map->fetch_object()) {
$sentence_id = $row_voting_sentence_map->sentence_id;
}
}
return $sentence_id;
}
//pick one user assigned but not yet translator test voting sentence map id
public function getUserAssignedTestVotingSentenceMapId( $user_id, $test_voting_level ) {
$sql_get_voting_sentence_map = "SELECT id FROM `translator_test_voting_sentences_map` WHERE user_id = ".$user_id." AND test_voting_level = ".$test_voting_level." AND voted_suggestion_id IS NULL AND is_skip = 0 order by id LIMIT 1";
$result_voting_sentence_map = $this->con->query( $sql_get_voting_sentence_map );
$id = 0;
if( $result_voting_sentence_map->num_rows > 0 ) {
while ($row_voting_sentence_map = $result_voting_sentence_map->fetch_object()) {
$id = $row_voting_sentence_map->id;
}
}
return $id;
}
//get test voting sentence row
public function getTestVotingSentenceRowById( $id ) {
$sql_get_voting_sentnece = "SELECT * FROM test_voting_sentences WHERE id = " . $id;
$result = $this->con->query( $sql_get_voting_sentnece );
return $result;
}
//apply user vote. update translator vote in test voting sentence map
public function applyUserVoteForAssignedTestVotingSentence($id,$user_id,$sentence_id,$voted_suggestion_id,$is_correct_answer) {
$sqlUpdateS = 'UPDATE `translator_test_voting_sentences_map` SET voted_suggestion_id='. $voted_suggestion_id .', is_correct_answer = '.$is_correct_answer.', voted_at = NOW() WHERE id='.$id.' AND sentence_id = ' . $sentence_id.' AND user_id = ' . $user_id . ' AND voted_suggestion_id IS NULL';
$resultUpdateS = $this->con->query($sqlUpdateS);
return $resultUpdateS;
}
//get test voting sentence count submitted by user for test voting level
public function getUserSubmittedTestVotingSentnecesCount( $user_id, $test_voting_level ) {
$sql_get_submitted_voting_sentences = "SELECT * from translator_test_voting_sentences_map WHERE user_id = " . $user_id . " AND test_voting_level = " . $test_voting_level . " AND voted_suggestion_id IS NOT NULL ";
$result_get_submitted_voting_sentences = $this->con->query( $sql_get_submitted_voting_sentences );
$user_submitted_test_voting_sentences_count = $result_get_submitted_voting_sentences->num_rows;
return $user_submitted_test_voting_sentences_count;
}
//get count of correct submitted test votings by user for test voting level
public function getCorrectSubmittedTestVotingCountByUserId( $user_id, $test_voting_level ) {
$sql_get_correct_voting_sentences = "SELECT * from translator_test_voting_sentences_map WHERE user_id = " . $user_id . " AND test_voting_level = " . $test_voting_level . " AND is_correct_answer = 1 ";
$result_get_correct_voting_sentences = $this->con->query( $sql_get_correct_voting_sentences );
$user_correct_test_voting_sentences_count = $result_get_correct_voting_sentences->num_rows;
return $user_correct_test_voting_sentences_count;
}
//calculate test voting success percentage
public function calculateTestVotingSuccessPercentage( $user_correct_test_voting_sentences_count, $max_test_voting_sentences ) {
$user_success_percentage = 0;
if( $user_correct_test_voting_sentences_count > 0 ) {
$user_success_percentage = ($user_correct_test_voting_sentences_count / $max_test_voting_sentences) * 100;
}
return $user_success_percentage;
}
//upgrade user's test voting level
public function upgradeTestVotingLevelTo( $user_id, $test_voting_level ) {
$upgrade_to_test_voting_level = 0;
//upgrade test voting level from 1,2 and 6,7
//$allowed_test_voting_level_ids_arr = array(1,2,6,7);
$allowed_test_voting_level_ids_arr = array(1,2,3,4);
if( in_array( $test_voting_level, $allowed_test_voting_level_ids_arr ) ) {
$upgrade_to_test_voting_level = $test_voting_level + 1;
$sql_upgrade_test_voting_level = "UPDATE tbl_translators SET test_voting_level = ".$upgrade_to_test_voting_level." WHERE id = " . $user_id . " AND test_voting_level = ".$test_voting_level." AND test_voting_completed_at IS NULL LIMIT 1";
$this->con->query( $sql_upgrade_test_voting_level );
}
}
//calculate user test voting success percentage and upgrade test level
public function calculateUserTestVotingSuccessPercentageAndUpgrade( $user_id ) {
$is_successfully_upgraded = false;
//Get current test voting level
$test_voting_level = $this->getTestVotingLevelByUserId( $user_id );
//get test voting sentence count submitted by user
$user_submitted_test_voting_sentences_count = $this->getUserSubmittedTestVotingSentnecesCount( $user_id, $test_voting_level );
//maximum no. of test voting sentences assigned, no. of test voting user can submit
$max_test_voting_sentences = $this->getMaxTestVotingSentences();
if( $user_submitted_test_voting_sentences_count >= $max_test_voting_sentences ) {
//don't check test score success percentage for test voting level 1 and test voting level 6. After completing max no. of sentence voting upgrade to next level
//if( $test_voting_level == 1 || $test_voting_level == 6 ) {
//if( $test_voting_level == 1 || $test_voting_level == 3 ) {
if( $test_voting_level == 1 || $test_voting_level == 2 || $test_voting_level == 3 ) {
//upgrade test voting level from 1 to next and 6 to next
$this->upgradeTestVotingLevelTo( $user_id, $test_voting_level );
$is_successfully_upgraded = true;
}
//check test voting level 2 or 7
//if( $test_voting_level == 2 || $test_voting_level == 7 ) {
//if( $test_voting_level == 2 || $test_voting_level == 4 ) {
if( $test_voting_level == 4 ) {
/*if( $test_voting_level == 2 ) {
$test_voting_level_arr = array(1,2);
}*/
//if( $test_voting_level == 7 ) {
/*if( $test_voting_level == 4 ) {
//$test_voting_level_arr = array(6,7);
$test_voting_level_arr = array(3,4);
}*/
$test_voting_level_arr = array(1,2,3,4);
//get translator average test score for test voting level 1,2 or 6,7 - changed
//get translator average test score for test voting level 1,2 or 3,4 - changed
//get translator average test score for test voting level 1,2,3,4
$user_average_test_score_percentage = $this->getTranslatorTestScoreByTestLevels($user_id,$test_voting_level_arr);
//get test voting success percentage value
$success_percentage_value = $this->getTestVotingSuccessPercentageValue( $test_voting_level );
//check, if user's test voting average test score percentage is equal or more than success percentage value
if( $user_average_test_score_percentage >= $success_percentage_value ) {
//upgrade test voting level from 2 to next, 7 to next
$this->upgradeTestVotingLevelTo( $user_id, $test_voting_level );
$is_successfully_upgraded = true;
}
}
}
return $is_successfully_upgraded;
}
//get test score (test voting) - to display on user profile
public function getTestScore($user_id) {
$test_score = 0;
//get test voting completed
$is_test_voting_completed = $this->checkIsTranslatorCompletedTestVoting($user_id);
//check if translator completed test voting
if( $is_test_voting_completed == 1 ) {
$test_voting_level = $this->getTestVotingLevelByUserId( $user_id );
//get total test voting sentence count submitted by user
$user_total_submitted_test_voting_sentences_count = $this->getUserSubmittedTestVotingSentnecesCount( $user_id, $test_voting_level );
//get count of no. of correct answer submitted for test votings by user
$user_correct_test_voting_sentences_count = $this->getCorrectSubmittedTestVotingCountByUserId( $user_id, $test_voting_level );
//calculate test voting success percentage
$test_score = $this->calculateTestVotingSuccessPercentage( $user_correct_test_voting_sentences_count, $user_total_submitted_test_voting_sentences_count );
}
return $test_score;
}
//get test voting completed
public function checkIsTranslatorCompletedTestVoting($user_id,$test_voting_level = 0) {
$test_voting_completed = 0;
if( $test_voting_level == 0 ) {
$test_voting_level = $this->getTestVotingLevelByUserId( $user_id );
}
$user_assigned_voting_sentences_count = $this->getVotingSentnecesAssignedCount( $user_id, $test_voting_level );
$user_submitted_test_votings_count = $this->getUserSubmittedTestVotingSentnecesCount( $user_id, $test_voting_level );
$max_test_voting_sentences = $this->getMaxTestVotingSentences();
if( ( $user_assigned_voting_sentences_count >=1 && $user_assigned_voting_sentences_count == $user_submitted_test_votings_count ) || $user_submitted_test_votings_count >= $max_test_voting_sentences ) {
$test_voting_completed = 1;
}
return $test_voting_completed;
}
// SQL for marking the test voting failed if time more than 1 day of submission
public function makeTestVotingFailedMaxSubmitTimeOver( $user_id ) {
$sql_ttvsmUpdate = 'UPDATE `translator_test_voting_sentences_map` SET voted_suggestion_id = 0, is_correct_answer = 0, voted_at = NOW() WHERE max_submit_time < NOW() AND voted_suggestion_id IS NULL AND is_correct_answer IS NULL AND voted_at IS NULL AND user_id = '.$user_id;
$this->con->query($sql_ttvsmUpdate);
//calculate user test voting success percentage and upgrade level/profile
$this->calculateUserTestVotingSuccessPercentageAndUpgrade( $user_id );
}
//Get all test voting allowed languages ids
public function getTestVotingAllowedLanguagesIds() {
//87-Spanish, 38-Hindi, Chinese-105, German-31, Vietnamese-99, Indonesian-43, 4-Arabic, Portuguese-73, Russian-76
//Dutch-21, Bengali-9, Italian-45, Urdu-97, Turkish-95, Afrikaans-1
//$test_voting_languages_ids_arr = array(87,38,105,31,99,43,4,73,76,21,9,45,97,95,1);
$sql_get_languages_ids = "SELECT id FROM languages WHERE test_voting_status = 1 ";
$result_languages_ids = $this->con->query( $sql_get_languages_ids );
$test_voting_languages_ids_arr = array();
while( $row_languages_ids = $result_languages_ids->fetch_object() ) {
$test_voting_languages_ids_arr[] = $row_languages_ids->id;
}
return $test_voting_languages_ids_arr;
}
//get start test voting status
public function getTestVotingStatus( $language_id, $test_voting_completed_at ) {
$start_test_voting = 'off';
$test_voting_languages_ids_arr = $this->getTestVotingAllowedLanguagesIds();
if( in_array( $language_id, $test_voting_languages_ids_arr ) && $test_voting_completed_at == '' ) {
$start_test_voting = 'on';
}
return $start_test_voting;
}
//get test voting level by user id
public function getTestVotingLevelByUserId( $user_id ) {
$sql_get_test_voting_level = "SELECT test_voting_level FROM tbl_translators WHERE id = " . $user_id;
$test_voting_level_result = $this->con->query($sql_get_test_voting_level);
if( $test_voting_level_result->num_rows > 0 ) {
while ($row_test_voting_level = $test_voting_level_result->fetch_object()) {
$test_voting_level = (int)$row_test_voting_level->test_voting_level;
}
}
return $test_voting_level;
}
//skip user test vote
public function skipUserTestSentneceVote($id, $user_id) {
$sqlUpdateTtvsm = 'UPDATE `translator_test_voting_sentences_map` SET is_skip = 1 WHERE id='.$id.' AND user_id = ' . $user_id;
$resultUpdateTtvsm = $this->con->query($sqlUpdateTtvsm);
return $resultUpdateTtvsm;
}
//get translator watched introduction video status
public function getTranslatorWatchedIntroVideoStatus( $user_id ){
$confirm_watched_intro_video = 0;
$sql_get_translator = "SELECT confirm_watched_intro_video FROM tbl_translators WHERE id = " . $user_id;
$translator_result = $this->con->query($sql_get_translator);
if( $translator_result->num_rows > 0 ) {
while ($row_translator = $translator_result->fetch_object()) {
$confirm_watched_intro_video = $row_translator->confirm_watched_intro_video;
}
}
return $confirm_watched_intro_video;
}
//update translator watched introduction video status
public function updateTranslatorWatchedIntroVideoStatus($watched_status_to, $user_id) {
$sqlUpdateTtranslator = 'UPDATE `tbl_translators` SET confirm_watched_intro_video = ' . $watched_status_to . ', confirm_watched_intro_video_at = NOW() WHERE confirm_watched_intro_video = 0 AND id = ' . $user_id;
$resultUpdateTtranslator = $this->con->query($sqlUpdateTtranslator);
return $resultUpdateTtranslator;
}
//get translator average test score by test voting levels e.g 1,2 or 6,7
public function getTranslatorTestScoreByTestLevels($user_id,$test_voting_level_arr) {
$test_score = 0;
$test_score_arr = array();
foreach( $test_voting_level_arr as $test_voting_level ) {
//get test voting completed
$is_test_voting_completed = $this->checkIsTranslatorCompletedTestVoting($user_id, $test_voting_level);
//check if translator completed test voting
if( $is_test_voting_completed == 1 ) {
//$test_voting_level = $this->getTestVotingLevelByUserId( $user_id );
//get total test voting sentence count submitted by user
$user_total_submitted_test_voting_sentences_count = $this->getUserSubmittedTestVotingSentnecesCount( $user_id, $test_voting_level );
//get count of no. of correct answer submitted for test votings by user
$user_correct_test_voting_sentences_count = $this->getCorrectSubmittedTestVotingCountByUserId( $user_id, $test_voting_level );
//calculate test voting success percentage
$test_score_arr[] = $this->calculateTestVotingSuccessPercentage( $user_correct_test_voting_sentences_count, $user_total_submitted_test_voting_sentences_count );
}
}
if( $test_score_arr ) {
$total_of_test_score = array_sum( $test_score_arr );
$total_no_of_levels = count( $test_score_arr );
$test_score = $total_of_test_score / $total_no_of_levels;
}
return $test_score;
}
//get limited no. of test voting sentences, last 200
public function getLimitedTestVotingSentencesIds( $language_id ) {
//take sentences from this date. date format yyyy-mm-dd
$test_sentences_from_date = '2021-12-06';
$sql_get_limited_voting_sentences = "SELECT id FROM `test_voting_sentences` WHERE language_id = ".$language_id." AND is_skipped = 0 AND is_other_suggestion_win = 0 AND created_on >= '".$test_sentences_from_date."' ORDER BY id DESC LIMIT 200";
$result_limited_voting_sentences = $this->con->query( $sql_get_limited_voting_sentences );
$limited_sentneces_id_arr = array();
if ($result_limited_voting_sentences->num_rows > 0) {
while ($row_limited_voting_sentences = $result_limited_voting_sentences->fetch_object()) {
$limited_sentneces_id_arr[]=$row_limited_voting_sentences->id;
}
}
$limited_test_sentneces_ids = implode( ",",$limited_sentneces_id_arr );
return $limited_test_sentneces_ids;
}
//get no. of skip test voting sentences count by user id and test level
public function getSkipTestVotingCountByUserIdAndTestLevel( $user_id, $test_voting_level ) {
$sql_get_skip_voting_sentences = "SELECT * FROM translator_test_voting_sentences_map WHERE user_id = " . $user_id . " AND test_voting_level = " . $test_voting_level . " AND is_skip = 1 ";
$result_get_skip_voting_sentences = $this->con->query( $sql_get_skip_voting_sentences );
$user_skip_voting_sentences_count = $result_get_skip_voting_sentences->num_rows;
return $user_skip_voting_sentences_count;
}
//Save extra suggestion
public function saveTestVotingSentenceExtraSuggestion($test_voting_sentence_id,$ttvsm_id,$user_id,$suggested_text) {
$sql_insert = "INSERT INTO `test_voting_sentences_extra_suggestions` (test_voting_sentence_id,ttvsm_id,user_id,suggested_text) VALUES (".$test_voting_sentence_id.",".$ttvsm_id.",".$user_id.",'".$suggested_text."')";
$result = $this->con->query($sql_insert);
return $result;
}
//get test voting sentence extra suggestions count
public function getTestVotingSentenceExtraSuggestionsCountBySentenceId( $test_voting_sentence_id ) {
$sql_get_tvses = "SELECT * FROM test_voting_sentences_extra_suggestions WHERE test_voting_sentence_id = " . $test_voting_sentence_id;
$result_tvses = $this->con->query( $sql_get_tvses );
$test_voting_sentences_extra_suggestions_count = $result_tvses->num_rows;
return $test_voting_sentences_extra_suggestions_count;
}
//remove or disable test sentence from test voting sentences
public function disableSentenceFromTestVotingById( $id ) {
$sqlUpdateTestVotingSentence = 'UPDATE `test_voting_sentences` SET is_skipped = 1 WHERE id='.$id . ' LIMIT 1';
$resultUpdateTestVotingSentence = $this->con->query($sqlUpdateTestVotingSentence);
}
}
?>