<?php

	require_once('class.db.php');

	//Winning Self Assessment Test Sentence Suggestion & Voting Credits
	class Winning_Sats_Suggestion_Voting_Credits extends DB
	{
		
		//get winning sats suggestion tmn by language id
		public function getWinningSatsSuggestionTmn( $language_id ) {
			//$winning_suggestion_tmn = 2;
			$sql_winning_suggestion_tmn = "SELECT winning_suggestion_tmn FROM `languages` WHERE id = " . $language_id;
			$result_winning_suggestion_tmn = $this->con->query( $sql_winning_suggestion_tmn );
			while ($row_winning_suggestion_tmn = $result_winning_suggestion_tmn->fetch_object()) {
				$winning_suggestion_tmn = $row_winning_suggestion_tmn->winning_suggestion_tmn;
			}			
			return $winning_suggestion_tmn;
		}
		
		//Get TMN for people who voted on winning suggestion by language id
		public function getWinningSatsVotedSugestionTmn( $language_id ) { 
			//$winning_voted_tmn = 10; 
			$sql_winning_voted_tmn = "SELECT voting_suggestion_tmn FROM `languages` WHERE id = " . $language_id;
			$result_winning_voted_tmn = $this->con->query( $sql_winning_voted_tmn );
			while ($row_winning_voted_tmn = $result_winning_voted_tmn->fetch_object()) {
				$winning_voted_tmn = $row_winning_voted_tmn->voting_suggestion_tmn;
			}
			return $winning_voted_tmn;
		}
		
		//Get all success suggestion ids by sentence id
		public function getSuccessSatsSuggestionIdsBySentenceId( $sentence_id ) {
			
			$sqlSuccessSuggestions = 'SELECT * FROM `self_assessment_test_sentence_suggestion` WHERE sentence_id = ' . $sentence_id . ' AND is_locked = 1 AND is_success = 1';
			$resultSuccessSuggestions = $this->con->query($sqlSuccessSuggestions);
			
			if ($resultSuccessSuggestions->num_rows > 0) {
				while ($row_success_suggestions = $resultSuccessSuggestions->fetch_object()) {
					$success_suggestion_id_arr[]=$row_success_suggestions->id;
				}
			}

			return $success_suggestion_id_arr;
			
		}
		
		//get success suggestion id and it's vote count by suggestion Id
		public function getSuccessSatsSuggestionIdAndVoteCountBySuggestionId( $suggestion_id ) {
			$sqlU = 'SELECT * FROM `self_assessment_test_sentence_voting` WHERE voted_suggestion_id = ' . $suggestion_id. ' AND is_success = 1' ;
			$resultU = $this->con->query($sqlU);
			$total_votes = $resultU->num_rows;
			$suggestion_id_and_count_arr = array();
			
			//suggestion id and it's count			
			$suggestion_id_and_count_arr[$suggestion_id] = $total_votes;
			return $suggestion_id_and_count_arr;			
		}
		
		//Get winning sentence suggestion id by passing suggestion ids and it's counts array
		public function getWinningSatsSuggestionId( $suggestion_id_and_count_arr ) {
			//highest votes suggestion id
			$winning_suggestion_id = array_search( max($suggestion_id_and_count_arr), $suggestion_id_and_count_arr );
			return $winning_suggestion_id;
		}
		
		//Get winning suggestion's user id by winning sentence id
		public function getWinningSatsSuggestionUserId( $winning_suggestion_id ) {
			$sqlWinningSuggestion = 'SELECT * FROM `self_assessment_test_sentence_suggestion` WHERE id = ' . $winning_suggestion_id . ' AND is_locked = 1 AND is_success = 1';
			$resultWinningSuggestion = $this->con->query($sqlWinningSuggestion);
			while ($row_winning_suggestion = $resultWinningSuggestion->fetch_object()) {
				$winning_suggestion_user_id = $row_winning_suggestion->user_id;
			}
			return $winning_suggestion_user_id;
		}
		
		//Get winning suggestion's voted user's ids by winning suggestion id
		public function getWinningSatsSuggestionVotedUserIds( $winning_suggestion_id ) {
			
			$sqlU = 'SELECT * FROM `self_assessment_test_sentence_voting` WHERE voted_suggestion_id = ' . $winning_suggestion_id. ' AND is_success = 1' ;
			$resultU = $this->con->query($sqlU);
			$total_votes = $resultU->num_rows;
			$voted_user_ids_and_suggestion_ids_arr = array();
			while ($row_vote = $resultU->fetch_object()) {				
				$voted_user_ids_and_suggestion_ids_arr[] = $row_vote->user_id;
			}
			
			return $voted_user_ids_and_suggestion_ids_arr;
			
		}	

		//get Winning Suggestion Id By Sentence Id
		public function getWinningSatsSuggestionIdBySentneceId( $sentence_id ) {
			
			$success_suggestion_id_arr = $this->getSuccessSatsSuggestionIdsBySentenceId( $sentence_id );			
			
			if( $success_suggestion_id_arr ) {	
				
				foreach( $success_suggestion_id_arr as $success_suggestion_id ) {
					
					$suggestion_id_and_count_arr = $this->getSuccessSatsSuggestionIdAndVoteCountBySuggestionId( $success_suggestion_id );
					foreach( $suggestion_id_and_count_arr as $key_suggestion_id => $votes_count ) {
						$all_suggestion_ids_and_counts_arr[$key_suggestion_id] = $votes_count;
					}
				}				
				
				$winning_suggestion_id = $this->getWinningSatsSuggestionId( $all_suggestion_ids_and_counts_arr );				
			}
			
			return $winning_suggestion_id;		
			
		}
		
		
		//Insert into winning suggestion user credits
		public function insertIntoWinningSatsSuggestionUserCredits($sentence_id,$winning_suggestion_id,$winning_suggetion_user_id,$winning_suggestion_tmn) {
			$sql_insert = "INSERT INTO `winning_sats_suggestion_voting_credits` (sentence_id,suggestion_id,user_id,winning_credits,credit_purpose) VALUES ({$sentence_id},{$winning_suggestion_id},{$winning_suggetion_user_id},{$winning_suggestion_tmn},1) ";			
			$result_insert = $this->con->query($sql_insert);
			$last_inserted_id = $this->con->insert_id;
			return $last_inserted_id;
		}
		
		//Insert into winning suggestion voted user credits 
		public function insertIntoWinningSatsSuggestionVotedUserCredits($sentence_id,$suggestion_id,$user_id,$winning_credits) {
			$sql_insert = "INSERT INTO `winning_sats_suggestion_voting_credits` (sentence_id,suggestion_id,user_id,winning_credits,credit_purpose) VALUES ({$sentence_id},{$suggestion_id},{$user_id},{$winning_credits},2) ";			
			$result_insert = $this->con->query($sql_insert);
			$last_inserted_id = $this->con->insert_id;
			return $last_inserted_id;
		}
		
		//get winning suggestion voting credits number of rows 
		public function getWinningSatsSuggestionVotingCreditsBySentenceId( $sentence_id ) {
			
			$sqlSentenceWin = 'SELECT sentence_id FROM `winning_sats_suggestion_voting_credits` WHERE sentence_id = ' . $sentence_id . ' LIMIT 1';
			$resultSentenceWin = $this->con->query($sqlSentenceWin);			
			
			$sentence_win_rows = $resultSentenceWin->num_rows;

			return $sentence_win_rows;
		}
		
		
		//Process winning self assessment test sentence suggestion user credits and winning suggestion voted user id credits by sentence id
		public function processWinningSatsSuggestionVotingCreditsBySentenceId( $sentence_id ) {
			
			//check if sentence has already been win record
			$sentence_win_rows = $this->getWinningSatsSuggestionVotingCreditsBySentenceId( $sentence_id );
			
			//get self assessment test sentences language id by sentence id
			$language_id = $this->getSatsLanguageIdBySentenceId( $sentence_id );
			
			if( $sentence_win_rows == 0 ) {			

				//get winning suggestion tmn
				$winning_suggestion_tmn = $this->getWinningSatsSuggestionTmn( $language_id );
				
				//get winning voted suggestion tmn
				$voting_suggestion_tmn = $this->getWinningSatsVotedSugestionTmn( $language_id );
				
				//get winning suggestion id (suggestion id which has highest votes) by sentence Id
				$winning_suggestion_id = $this->getWinningSatsSuggestionIdBySentneceId( $sentence_id );			

				//Get winning suggestion's user id by winning suggestion id
				$winning_suggetion_user_id = $this->getWinningSatsSuggestionUserId( $winning_suggestion_id );
				
				//Give credit to winning suggestion user
				if( $winning_suggetion_user_id ) {				
					$winning_sats_suggestion_tbl_id = $this->insertIntoWinningSatsSuggestionUserCredits($sentence_id,$winning_suggestion_id,$winning_suggetion_user_id,$winning_suggestion_tmn);
					
					//add entry into the tmn transactions & update translator tmn balance					
					$this->insertIntoTranslatorTmnTransactionsCreditEntryForSats( $winning_suggetion_user_id, "Work Experience Assessment Winning Suggestion", $winning_sats_suggestion_tbl_id, "winning_sats_suggestion_voting_credits", $winning_suggestion_tmn );
				}

				//Get winning suggestion's voted user's ids by winning suggestion id
				$winning_suggestion_voted_user_ids_arr = $this->getWinningSatsSuggestionVotedUserIds( $winning_suggestion_id );

				if (count($winning_suggestion_voted_user_ids_arr) > 0) {
					
					//Calculate per user tmn credits
					$tmn_per_user = round((float) $voting_suggestion_tmn / count($winning_suggestion_voted_user_ids_arr), 3);
					
					//Give credit to winning suggestion voted users
					foreach($winning_suggestion_voted_user_ids_arr as $winning_suggestion_voted_user_id) {						
						$winning_sats_voted_on_suggestion_tbl_id = $this->insertIntoWinningSatsSuggestionVotedUserCredits($sentence_id,$winning_suggestion_id,$winning_suggestion_voted_user_id,$tmn_per_user);
						
						//add entry into the tmn transactions & update translator tmn balance				
						$this->insertIntoTranslatorTmnTransactionsCreditEntryForSats( $winning_suggestion_voted_user_id, "Work Experience Assessment Voted on Winning Suggestion", $winning_sats_voted_on_suggestion_tbl_id, "winning_sats_suggestion_voting_credits", $tmn_per_user );
					}
					
				}
			
			}
			
		}	
		
		//get self assessment test sentences language id by sentence id
		public function getSatsLanguageIdBySentenceId( $sentence_id ) {
			$sql_get_sats_language_id = "SELECT language_id FROM `self_assessment_test_sentences` WHERE id = " . $sentence_id;
			$result_get_sats_language_id = $this->con->query( $sql_get_sats_language_id );
			while ($row_sats_language_id = $result_get_sats_language_id->fetch_object()) {
				$sats_language_id = $row_sats_language_id->language_id;
			}
			return $sats_language_id;
		}
		
		//get Translator tmn balance from translator tmn transactions by user id for work experience assessment 
		public function getTranslatorTmnBalanceForSats( $user_id ) {
			$tmn_balance = 0;
			$sql_tmn_balance = "SELECT tmn_balance FROM translator_tmn_transactions WHERE user_id = " . $user_id . " ORDER BY id DESC LIMIT 1";
			$result_tmn_balance = $this->con->query( $sql_tmn_balance );				
			if( $result_tmn_balance->num_rows > 0 ) {
				while( $row_tmn_balance = $result_tmn_balance->fetch_object() ) {
					$tmn_balance = $row_tmn_balance->tmn_balance;
				}
			}
			return $tmn_balance;
		}
		
		//tmn credit entry & update balance for work experience assessment
		public function insertIntoTranslatorTmnTransactionsCreditEntryForSats( $user_id, $trans_description, $reference_tableid, $reference_tablename, $winning_tmn_to_credit ) {
			
			$current_tmn_balance = $this->getTranslatorTmnBalanceForSats( $user_id );
			
			$updated_tmn_balance = $current_tmn_balance + $winning_tmn_to_credit;
			
			$sqlInsert = 'INSERT INTO `translator_tmn_transactions`(`user_id`, `trans_description`, `reference_tableid`, `reference_tablename`, `tmn_credit`, `tmn_balance`) VALUES '
					. '(' . $user_id . ', "' . $trans_description  . '",' . $reference_tableid . ', "' . $reference_tablename  . '", ' . $winning_tmn_to_credit . ',' . $updated_tmn_balance . ' )';
					$resultInsert = $this->con->query($sqlInsert);
					
		}
		
	}
?>