<?php

	require_once('class.db.php');

	class Self_assessment_test_sentence_voting extends DB
	{	
		
		//Get max submit time in minutes to submit voting
		public function getSatsVotingMaxSubmitTimeMins() {
			//$max_submit_time_mins = 5;
			$sql_get_max_submit_time = "SELECT wea_voting_max_submit_time FROM settings WHERE id = 1";
			$result_max_submit_time = $this->con->query( $sql_get_max_submit_time );
			while( $row_max_submit_time = $result_max_submit_time->fetch_object() ) {
				$max_submit_time_mins = $row_max_submit_time->wea_voting_max_submit_time;
			}
			return  $max_submit_time_mins;
		}
		
		//Get max no. of voting for sentence value
		public function getSelfAssessmentTestMaxNoOfVotingForSentnece() {
			//$max_no_of_voting_for_sentnece = 11;
			$sql_get_wea_max_no_voting_for_sentence = "SELECT wea_max_no_votes_for_sentence FROM settings WHERE id = 1";
			$result_wea_max_no_voting_for_sentence = $this->con->query( $sql_get_wea_max_no_voting_for_sentence );
			while( $row_wea_max_no_voting_for_sentence = $result_wea_max_no_voting_for_sentence->fetch_object() ) {
				$max_no_of_voting_for_sentnece = $row_wea_max_no_voting_for_sentence->wea_max_no_votes_for_sentence;
			}
			return $max_no_of_voting_for_sentnece;
		}
		
		//get no. of success voting to single suggestion
		public function getNoOfSuccessVotingToSingleSuggestion() {
			//$max_no_of_success_voting_for_suggestion = 5;
			$sql_get_no_of_success_vote_suggestion_value = "SELECT wea_max_no_success_votes_for_suggestion FROM settings WHERE id = 1";
			$result_no_of_success_vote_suggestion_value = $this->con->query( $sql_get_no_of_success_vote_suggestion_value );
			while( $row_no_of_success_vote_suggestion_value = $result_no_of_success_vote_suggestion_value->fetch_object() ) {
				$max_no_of_success_voting_for_suggestion = $row_no_of_success_vote_suggestion_value->wea_max_no_success_votes_for_suggestion;
			}
			return $max_no_of_success_voting_for_suggestion;
		}
		
		//get maximum no. of vote user can submit in day value
		public function getMaxNoOfSatsVoteUserCanSubmitInDay() {
			$maxNoVoteUserCanSubmitInDay = 25;
			return $maxNoVoteUserCanSubmitInDay;
		}
		
		//get maximum no. of vote user can submit for test level 
		public function getMaxNoOfSatsVoteUserCanSubmitForTestLevel() {
			//$max_no_vote_user_can_submit_for_test_level = 50;
			$sql_get_max_vote_user_can_submit_for_test_level = "SELECT wea_voting_for_test_level FROM settings WHERE id = 1";
			$result_max_vote_user_can_submit_for_test_level = $this->con->query( $sql_get_max_vote_user_can_submit_for_test_level );
			while( $row_max_vote = $result_max_vote_user_can_submit_for_test_level->fetch_object() ) {
				$max_no_vote_user_can_submit_for_test_level = $row_max_vote->wea_voting_for_test_level;
			}
			return $max_no_vote_user_can_submit_for_test_level;
		}
		
		//get assigned user empty vote success null entry's count
		public function get_user_empty_vote_count_by_satsv_sentence_id_and_user_id( $sentence_id, $user_id ) {
			$sqlUserVotingCount = 'SELECT * FROM `self_assessment_test_sentence_voting` WHERE sentence_id = ' . $sentence_id . ' AND user_id = ' . $user_id . ' AND is_success IS NULL';
			$resultUserVotingCount = $this->con->query($sqlUserVotingCount);
			$user_voting_count = $resultUserVotingCount->num_rows;     
			return $user_voting_count;
		}

		//get user skipped voting sentence ids
		public function getSkippedSatsVotingSentenceIdsByUserID( $user_id ) {

			$current_skipped_id_sql = 'SELECT sentence_id FROM skipped_self_assessment_test_sentence_voting WHERE user_id = ' . $user_id;			
			
			$result_current_skipped_id = $this->con->query($current_skipped_id_sql);

			$skipped_id_arr = array( 0 );
			if ($result_current_skipped_id->num_rows > 0) {
				while ($row_current_skipped_id = $result_current_skipped_id->fetch_object()) {
					$skipped_id_arr[]=$row_current_skipped_id->sentence_id;
				}
			}

			$skipped_ids = implode( ",",$skipped_id_arr );
			
			return $skipped_ids;
			
		}		
		
		// checking currently assigned sentence for voting 
		public function getCurrentlyAssignedSelfAssessmentTestSentenceIdsForVoting( $global_max_voting_count ) {
			
			$current_count = 'SELECT sentence_id FROM self_assessment_test_sentence_voting WHERE is_success IS NULL GROUP BY sentence_id HAVING COUNT(sentence_id) >= ' . $global_max_voting_count;
			$result_cur = $this->con->query($current_count);

			$not_in_id_arr = array( 0 );
			if ($result_cur->num_rows > 0) {
				while ($row_cur = $result_cur->fetch_object()) {
					$not_in_id_arr[]=$row_cur->sentence_id;
				}
			}

			$not_in_id_str = implode( ",",$not_in_id_arr );
			
			return $not_in_id_str;
			
		}
		
		//insert skipped record in to skipped_sentence_suggestion_voting table
		public function saveSkippedSatsVoting( $skipped_sentence_id, $user_id ) {
			$sql_insert = "INSERT INTO `skipped_self_assessment_test_sentence_voting` ( sentence_id, user_id ) VALUES (".$skipped_sentence_id.",".$user_id.")";
			$this->con->query($sql_insert);
		}
		
		// SQL for marking the voting failed if time more than 5 minutes of submission 
		public function makeSatsVotingFailedMaxSubmitTimeOver() {
			$sql_ssvmUpdate = 'UPDATE `self_assessment_test_sentence_voting` SET is_success = 0 where max_submit_time < NOW() AND is_success IS NULL';
			$this->con->query($sql_ssvmUpdate);
		}
		
		//Pick one sentence for voting
		public function pickSelfAssessmentTestSentenceForVoting( $language_id, $user_id, $not_in_id_str, $not_in_user_success_suggestion_sentence_ids, $not_in_user_success_voting_sentence_ids, $skipped_voting_sentence_ids  ) {			
			$sql = 'SELECT id FROM self_assessment_test_sentences WHERE language_id = '.$language_id.' AND is_suggestion_completed = 1 AND is_voting_completed = 0 AND uploaded_by != '.$user_id.' AND id NOT IN( '.$not_in_id_str.' ) AND id NOT IN( '.$not_in_user_success_suggestion_sentence_ids.' ) AND id NOT IN( '.$not_in_user_success_voting_sentence_ids.' ) AND id NOT IN( '.$skipped_voting_sentence_ids.' ) order by id LIMIT 1';			
			$result = $this->con->query($sql);
			return $result; 
		}
		
		// get original message by id
		public function getSelfAssessmentTestSentenceById( $id ) {
			$sqlSentence = 'SELECT * FROM `self_assessment_test_sentences` WHERE id = ' . $id;
            $resultSentence = $this->con->query($sqlSentence);
			return $resultSentence;
		}
		
		// get original message's translation suggestions by sentence id
		public function getSatsSuggestionsBySentenceId( $sentence_id ) {
			$sqlSuggestion = 'SELECT * FROM `self_assessment_test_sentence_suggestion` WHERE sentence_id = ' . $sentence_id . ' AND is_success = 1 ORDER BY id LIMIT 3 ';
            $resultSuggestion = $this->con->query($sqlSuggestion);
			return $resultSuggestion;
		}
		
		//Insert initial user vote entry into sentence_suggestion_voting_map table
		public function saveInitialUserSatsVoteEntry( $sentence_id, $user_id, $global_voting_max_submit_time_mins ) {
			$sqlInsert1 = 'INSERT INTO `self_assessment_test_sentence_voting`( `sentence_id`, `user_id`, `max_submit_time` ) VALUES '
						. '(' . $sentence_id . ',' . $user_id . ', NOW() + INTERVAL '.$global_voting_max_submit_time_mins.' MINUTE )';
			$this->con->query($sqlInsert1);
		}		
		
		//get user success suggested sentence ids and whose voting are not closed yet		
		public function getUserSuccessSatsSuggestionsSentenceIdsByUserID( $user_id ) {			
			
			//optimize skipped voting query			
			$user_success_suggestion_sentence_ids_sql = 'SELECT satss.sentence_id FROM self_assessment_test_sentences sats INNER JOIN self_assessment_test_sentence_suggestion satss ON sats.id = satss.sentence_id WHERE sats.is_suggestion_completed = 1 AND sats.is_voting_completed = 0 AND satss.user_id = '.$user_id.' AND satss.is_locked=1 AND satss.is_success = 1'; 	
			
			$result_user_success_suggestion_sentence_ids = $this->con->query($user_success_suggestion_sentence_ids_sql);

			$user_success_suggestion_sentence_id_arr = array( 0 );
			if ($result_user_success_suggestion_sentence_ids->num_rows > 0) {
				while ($row_user_success_suggestion_sentence_id = $result_user_success_suggestion_sentence_ids->fetch_object()) {
					$user_success_suggestion_sentence_id_arr[]=$row_user_success_suggestion_sentence_id->sentence_id;
				}
			}

			$user_success_suggestion_sentence_ids = implode( ",",$user_success_suggestion_sentence_id_arr );
			
			return $user_success_suggestion_sentence_ids;
			
		}
		
		//get user success voted sentence ids
		public function getUserSuccessSatsVotingSentenceIdsByUserID( $user_id ) {						
					
			$user_success_voted_sentence_ids_sql = 'SELECT sentence_id FROM `self_assessment_test_sentence_voting` WHERE is_success = 1 AND user_id = ' . $user_id; 	
			
			$result_user_success_voted_sentence_ids = $this->con->query($user_success_voted_sentence_ids_sql);

			$user_success_voted_sentence_id_arr = array( 0 );
			if ($result_user_success_voted_sentence_ids->num_rows > 0) {
				while ($row_user_success_voted_sentence_id = $result_user_success_voted_sentence_ids->fetch_object()) {
					$user_success_voted_sentence_id_arr[]=$row_user_success_voted_sentence_id->sentence_id;
				}
			}

			$user_success_voted_sentence_ids = implode( ",",$user_success_voted_sentence_id_arr );
			
			return $user_success_voted_sentence_ids;
			
		}
		
		
		//get success voting count for sentence
		public function get_success_sats_voting_count_by_sentence_id( $sentence_id ) {
			$sqlSuccessVotingCount = 'SELECT * FROM `self_assessment_test_sentence_voting` WHERE sentence_id = ' . $sentence_id . ' AND is_success = 1';
			$resultSuccessVotingCount = $this->con->query($sqlSuccessVotingCount);
			$success_voting_count = $resultSuccessVotingCount->num_rows;      
			return $success_voting_count;
		}
		
		//after received 1st success vote, start voting
		public function updateSelfAssessmentTestSentenceVotingStartedAtDate( $sentence_id ) {
			$sqlUpdateTs = 'UPDATE `self_assessment_test_sentences` SET voting_started_at = NOW() where id = ' . $sentence_id . ' AND voting_started_at IS NULL ';			
			$this->con->query($sqlUpdateTs);
		}
		
		//apply user vote. update user vote for sentence suggestion to success
		public function applySuccessUserVoteForSelfAssessmentTestSentenceSuggestion($suggestion_id,$sentence_id,$user_id,$test_level) {
			$sqlUpdateS = 'UPDATE `self_assessment_test_sentence_voting` SET is_success = 1, voted_at = NOW(), voted_suggestion_id='. $suggestion_id . ', test_level='. $test_level . ' WHERE sentence_id = ' . $sentence_id.' AND user_id = ' . $user_id . ' AND is_success IS NULL';
			$resultUpdateS = $this->con->query($sqlUpdateS);
			return $resultUpdateS;
		}
		
		//get count - sentence has reached No. of Success Voting To a Particular Suggestion 
		public function checkSatsHasReachedNoOfSuccessVotesForSuggestion($sentence_id, $no_of_success_voting_to_suggestion) {
			$sql_get_success_voting_for_suggestion = "SELECT voted_suggestion_id FROM self_assessment_test_sentence_voting WHERE sentence_id = " . $sentence_id . " AND is_success=1 GROUP BY voted_suggestion_id HAVING count(voted_suggestion_id) >= " . $no_of_success_voting_to_suggestion;			
			$result_get_success_voting_for_suggestion = $this->con->query( $sql_get_success_voting_for_suggestion );
			$count_success_voting_for_suggestion = $result_get_success_voting_for_suggestion->num_rows;			
			return $count_success_voting_for_suggestion;
		}
		
		//Close Sentence for voting in main tbl_sentence table. Voting completed for sentence suggestion
		public function closeVotingForSelfAssessmentTestSentence($sentence_id) {			
			$sqlUpdateSentence = 'UPDATE `self_assessment_test_sentences` SET is_voting_completed=1, voting_closed_at = NOW() WHERE id =' . $sentence_id . ' AND is_voting_completed = 0 AND voting_closed_at IS NULL';
			$this->con->query($sqlUpdateSentence);
		}
		
		//make user vote failed by sentence and user id
		public function makeUserSatsVoteFailedBySentenceIdAndUserId($sentence_id,$user_id) {
			$sqlUpdateS = 'UPDATE `self_assessment_test_sentence_voting` SET is_success = 0, voted_at = NOW() where sentence_id = ' . $sentence_id.' and user_id = ' . $user_id . ' AND is_success IS NULL';
			$this->con->query($sqlUpdateS);
		}
		
		//get user's today submitted success voting count
		public function getTodaySubmittedSuccessSatsVotingCountByUserId( $user_id ) {
			$sql_today_success_voting = "SELECT * FROM self_assessment_test_sentence_voting WHERE user_id = " . $user_id . " AND DATE_FORMAT(voted_at,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') AND is_success = 1 ";
			$today_success_voting_result = $this->con->query($sql_today_success_voting);
			$today_success_voting_count = $today_success_voting_result->num_rows;
			return $today_success_voting_count;
		}
		
		//get user submitted success work experience assessment voting count by UserId And TestLevel
		public function getUserSubmittedSuccessSatsVotingCountByUserIdAndTestLevel( $user_id, $test_level ) { 
			$sql_user_success_voting_for_level = "SELECT * FROM self_assessment_test_sentence_voting WHERE user_id = " . $user_id . " AND test_level = " . $test_level . " AND is_success = 1 ";
			$user_success_voting_result_for_level = $this->con->query($sql_user_success_voting_for_level);
			$user_success_voting_count = $user_success_voting_result_for_level->num_rows;
			return $user_success_voting_count;
		}
		
		//upgrade user's test level for work experience assessment suggestion & voting 
		public function upgradeUserSatsSuggestionVotingTestLevel($user_id,$test_level) {
			if( $test_level == 10 ) {
				//After successfully completed test level 10, make test voting level completed
				$sql_update_voting_completed_at = "UPDATE tbl_translators SET test_voting_completed_at = NOW() WHERE id = " . $user_id . " AND test_voting_completed_at IS NULL LIMIT 1";
				$resultUpdateS = $this->con->query( $sql_update_voting_completed_at );
			} else {
				$test_level_to_upgrade = $test_level + 1;
				$sqlUpdateS = 'UPDATE `tbl_translators` SET test_voting_level = ' . $test_level_to_upgrade . ' WHERE id = ' . $user_id.' AND test_voting_level = ' . $test_level . ' LIMIT 1 ';
				$resultUpdateS = $this->con->query($sqlUpdateS); 
			}			
			return $resultUpdateS;
		}
		
	}
?>