<?php

	require_once('class.db.php');

	class Self_assessment_test_sentence_suggestions extends DB
	{
		
		//Get max submit time in minutes to submit suggestion
		public function getSatsSuggestionMaxSubmitTimeMins() {
			//$max_submit_time_mins = 5;
			$sql_get_max_submit_time = "SELECT wea_suggestions_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_suggestions_max_submit_time;
			}
			return  $max_submit_time_mins;
		}
		
		//a sentence can be assigned for no. of users to submit suggestion
		public function getSentenceSuggestionMaxAssignValue() {
			//$sentence_suggestion_max_assign_value = 2;
			$sql_get_sentence_suggestion_max_assign_value = "SELECT wea_accept_max_no_of_suggestion FROM settings WHERE id = 1";
			$result_sentence_suggestion_max_assign_value = $this->con->query( $sql_get_sentence_suggestion_max_assign_value );
			while( $row_sentence_suggestion_max_assign_value = $result_sentence_suggestion_max_assign_value->fetch_object() )  {
				$sentence_suggestion_max_assign_value = $row_sentence_suggestion_max_assign_value->wea_accept_max_no_of_suggestion;
			}
			return $sentence_suggestion_max_assign_value;
		}
		
		//get maximum no. of suggestion user can submit in day value
		public function getMaxNoOfSuggestionUserCanSubmitInDay() {
			$maxNoSuggestionsUserCanSubmitInDay = 5;
			return $maxNoSuggestionsUserCanSubmitInDay;
		}
		
		//get maximum no. of suggestion user can submit for test level
		public function getMaxNoOfSuggestionUserCanSubmitForTestLevel() {
			//$max_suggestions_user_can_submit_for_test_level = 20;
			$sql_get_max_suggestions_user_can_submit_for_test_level = "SELECT wea_suggestions_for_test_level FROM settings WHERE id = 1";
			$result_max_suggestions_user_can_submit_for_test_level = $this->con->query( $sql_get_max_suggestions_user_can_submit_for_test_level );
			while( $row_max_suggestions = $result_max_suggestions_user_can_submit_for_test_level->fetch_object() ) {
				$max_suggestions_user_can_submit_for_test_level = $row_max_suggestions->wea_suggestions_for_test_level;
			}
			return $max_suggestions_user_can_submit_for_test_level;
		}
		
		//fetching the sentence for suggestion	
		public function getSelfAssessmentTestSentenceForSuggestion( $language_id, $user_id, $not_in_id_str, $skipped_ids ){			
			$sql = 'SELECT * FROM self_assessment_test_sentences WHERE is_suggestion_completed = 0 AND language_id='.$language_id.' AND uploaded_by !='.$user_id.' 
					AND id NOT IN ( SELECT sentence_id FROM self_assessment_test_sentence_suggestion WHERE  is_locked = 1 AND is_success = 1 and user_id = "'.$user_id.'" ) 
					AND id NOT IN( '.$not_in_id_str.' ) AND id NOT IN( '.$skipped_ids.' ) ORDER BY id LIMIT 1 ';							
			$result = $this->con->query($sql);	
			return $result;
		}
		
		//Code for checking active suggestions 
		public function getSelfAssessmentTestActiveSuggestionsCountBySentenceId( $sentence_id  ) {
			$sql_sats_suggestion = 'SELECT * FROM self_assessment_test_sentence_suggestion WHERE sentence_id = ' . $sentence_id . ' AND is_locked = 0 AND is_success IS NULL';
			$result_sats_suggestion = $this->con->query($sql_sats_suggestion);
			$active_suggestions_count = $result_sats_suggestion->num_rows;	
			return $active_suggestions_count;
		}
		
		//Code for checking if user is refreshing the page in 5 minutes 
		public function checkSelfAssesmentTestIsUserRefreshedPage( $sentence_id, $user_id ) {
			$sql2 = 'SELECT * FROM self_assessment_test_sentence_suggestion WHERE sentence_id = ' . $sentence_id . ' AND user_id = ' . $user_id.' and is_success is null limit 1 ';
			$result2 = $this->con->query($sql2);
			return $result2;
		}
		
		//Initial assign sentence for suggestion entry
		public function insertInitialSelfAssessmentTestSentenceSuggestionAndGetId($sentence_id,$user_id,$global_time_mins) {
			$sql_insert = "INSERT INTO `self_assessment_test_sentence_suggestion` (sentence_id,user_id,max_submit_time) VALUES (".$sentence_id.",".$user_id.",NOW() + INTERVAL {$global_time_mins} MINUTE)";						
			$insert_sentence_suggestion = $this->con->query($sql_insert);
			$last_inserted_id = $this->con->insert_id;
			return $last_inserted_id;
		}
		
		// SQL for marking the self assessment test sentence suggestion failed if time more than 5 mins of submission 
		public function makeSelfAssessmentTestSuggestionsFailedOverMaxTime() {
			$sql_sats_suggestion_Update = 'UPDATE `self_assessment_test_sentence_suggestion` SET is_locked = 1, is_success = 0 where max_submit_time < NOW() AND is_success IS NULL';
			$this->con->query($sql_sats_suggestion_Update);	
		}
		
		//checking all currently suggested sentence 
		public function getCurrentlyAssignedSatsSuggestionsSentenceIds() {
			
			$current_count = 'SELECT sentence_id FROM self_assessment_test_sentence_suggestion WHERE is_success is null group by sentence_id having count(sentence_id) >=2';
			$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;
			
		}
		
		//get next sentence 
		public function getSelfAssessmentTestNextSentenceForTranslation( $language_id, $user_id ) {
			$sql_next = 'SELECT * FROM self_assessment_test_sentences WHERE is_suggestion_completed = 0 AND language_id='.$language_id.' AND uploaded_by !='.$user_id.' 
			AND id NOT IN ( SELECT sentence_id FROM self_assessment_test_sentence_suggestion ) ORDER BY id LIMIT 1 ';	
			$result_next = $this->con->query($sql_next);
			return $result_next;
		}
		
		//Code for fetching all the sentence ID those are having
		public function update_all_previous_self_assessment_test_sentence_suggestion_completed(){

			$sql2 = 'select sentence_id, count(sentence_id) from self_assessment_test_sentence_suggestion WHERE is_locked = 1 AND is_success = 1 AND sentence_id IN ( SELECT id FROM self_assessment_test_sentences where is_suggestion_completed = 0 ) group by sentence_id HAVING count(sentence_id) >= 3';
			$result2 = $this->con->query($sql2);
			while ($row2 = $result2->fetch_object()) {
				$sentence_id2 = $row2->sentence_id;
				$sql_satsUpdate2 = 'UPDATE `self_assessment_test_sentences` SET is_suggestion_completed = 1 , suggestion_started_at = NOW() AND suggestion_closed_at = NOW() WHERE id = ' . $sentence_id2;
				$result_satsUpdate2 = $this->con->query($sql_satsUpdate2);	
			}
			
		}
		
		//insert skipped sentence record in to skipped_self_assessment_test_sentence_suggestion table
		public function saveSkippedSelfAssessmentTestSentenceSuggestion( $sentence_id, $user_id ) {			
			$sql_insert = "INSERT INTO `skipped_self_assessment_test_sentence_suggestion` (self_assessment_test_sentence_id, user_id ) VALUES (".$sentence_id.",".$user_id.")";			
			$this->con->query($sql_insert);
		}
		
		//get user skipped self assessment test sentence ids
		public function getSkippedSelfAssessmentTestSentenceIdsByUserID( $user_id ) {

			$current_skipped_id_sql = 'SELECT self_assessment_test_sentence_id FROM skipped_self_assessment_test_sentence_suggestion 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->self_assessment_test_sentence_id;
				}
			}

			$skipped_id_arr = implode( ",",$skipped_id_arr );
			
			return $skipped_id_arr;
			
		}
		
		//get success suggested records
		public function getSelfAssessmentTestSuccessSuggestionCountBySentenceId( $sentence_id ) {
			$sqlSuggestionSuccessCount = 'SELECT * FROM `self_assessment_test_sentence_suggestion` WHERE sentence_id = ' . $sentence_id . ' AND is_success = 1';
			$resultSuggestionSuccessCount = $this->con->query($sqlSuggestionSuccessCount);     
			$success_suggestion_count = $resultSuggestionSuccessCount->num_rows;	
			return $success_suggestion_count;
		}
		
		//accept the suggestion, update self assessment test sentence suggestion make lock and success suggestion 
		public function submitSelfAssessmentTestSuggestionAndUpdateToSuccess( $suggested_text, $suggestion_id, $test_level ) {
			$sqlUpdateS = 'UPDATE `self_assessment_test_sentence_suggestion` SET is_locked = 1, is_success = 1, submitted_at = NOW(), suggested_text="'. $suggested_text .'", test_level = '. $test_level .'   where id = ' . $suggestion_id.' and is_locked = 0' ;		
			$resultUpdateS = $this->con->query($sqlUpdateS);
			return $resultUpdateS;
		}
		
		//while adding sentence first success suggestion, update suggestion started at date
		public function updateSelfAssessmentTestSentenceSuggestionStartedAtDate( $sentence_id ) {
			$sqlUpdateTs = 'UPDATE `self_assessment_test_sentences` SET suggestion_started_at = NOW() where id = ' . $sentence_id . ' AND suggestion_started_at IS NULL ';			
			$this->con->query($sqlUpdateTs);
		}
		
		//close the self assessment test sentence for suggestion
		public function closeSelfAssessmentTestSentenceForSuggestions( $sentence_id ) {
			$sqlUpdateS = 'UPDATE `self_assessment_test_sentences` SET is_suggestion_completed = 1, suggestion_closed_at = NOW() where id = ' . $sentence_id;
			$this->con->query($sqlUpdateS);
		}
		
		//after received 3 success suggestions, 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);
		}
		
		//make suggestion fail in self_assessment_test_sentence_suggestion db table
		public function makeSelfAssessmentTestSentenceSuggestionFailed( $suggestion_id ) {
			$sqlUpdateSatsSuggestion = 'UPDATE `self_assessment_test_sentence_suggestion` SET is_locked = 1, is_success = 0, submitted_at = NOW() where id = ' . $suggestion_id ;			
			$this->con->query($sqlUpdateSatsSuggestion);
		}
		
		//get user today submitted success self assessment test suggestion count
		public function getUserTodaySubmittedSuccessSatsSuggestionCount( $user_id ) {
			$sql_today_success_suggestions = "SELECT * FROM self_assessment_test_sentence_suggestion WHERE user_id = ". $user_id ." AND DATE_FORMAT(submitted_at,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') AND is_locked = 1 AND is_success = 1";
			$today_success_suggestions_result = $this->con->query($sql_today_success_suggestions);
			$today_success_suggestion_count = $today_success_suggestions_result->num_rows;
			return $today_success_suggestion_count;
		}
		
		//get user submitted success work experience assessment suggestions count by user id and test level 
		public function getUserSubmittedSuccessSatsSuggestionCountByUserIdAndLevel( $user_id, $test_level ) {
			$sql_user_success_suggestions_for_level = "SELECT * FROM self_assessment_test_sentence_suggestion WHERE user_id = ". $user_id ." AND test_level = ". $test_level ." AND is_locked = 1 AND is_success = 1";
			$user_success_suggestions_for_level_result = $this->con->query($sql_user_success_suggestions_for_level);
			$user_success_suggestion_count_for_level = $user_success_suggestions_for_level_result->num_rows;
			return $user_success_suggestion_count_for_level;
		}
		
	}
?>