<?php

include 'connection.php';

$sqlUser = 'select * from tbl_translators LIMIT 500 OFFSET 500';
$resultUser = $mysqli->query($sqlUser);
$count = 1;
while ($row = $resultUser->fetch_object()) {
	$id = $row->id;
	
	$closed_suggestions = 0;
	$sqlC = 'select m.id from tbl_user_suggestion_mapping m LEFT JOIN tbl_sentence s ON m.sentence_id = s.id where m.user_id = "' . $row->id . '" AND s.is_open_for_voting = 0';
	$resultC = $mysqli->query($sqlC);
	$closed_suggestions = $resultC->num_rows;
	
	$winning_suggestions = 0;
	$sqlSuggestion = 'select T1.id from tbl_user_suggestion_mapping T1 LEFT JOIN tbl_sentence s ON T1.sentence_id = s.id where s.is_open_for_voting = 0 AND T1.user_id = "' . $row->id.'" AND is_winning = 1';
	$resultSuggestion = $mysqli->query($sqlSuggestion);
	$winning_suggestions =  $resultSuggestion->num_rows;
		
	$closed_votes = 0;
	$sqlC = 'select m.id from tbl_user_suggestion_mapping m LEFT JOIN tbl_sentence s ON m.sentence_id = s.id where FIND_IN_SET("' . $row->id . '",m.voted_user_id) AND s.is_open_for_voting = 0';$resultC = $mysqli->query($sqlC);
	$closed_votes = $resultC->num_rows;

	$winning_votes = 0;
	$sqlSuggestion = 'select m.id, m.sentence_id FROM tbl_user_suggestion_mapping m LEFT JOIN tbl_sentence s ON m.sentence_id = s.id where FIND_IN_SET("' . $row->id . '",m.voted_user_id) AND m.is_winning = 1 AND s.is_open_for_voting = 0';           
	$resultSuggestion = $mysqli->query($sqlSuggestion);
	$winning_votes = $resultSuggestion->num_rows;

	$sqlUpdateCS = 'UPDATE `tbl_translators` SET winning_suggestions = "'.$winning_suggestions.'", closed_suggestions = "'.$closed_suggestions.'", winning_votes = "'.$winning_votes.'",closed_votes = "'.$closed_votes.'" WHERE id = "'.$row->id.'"';
	$resultUpdateCS = $mysqli->query($sqlUpdateCS);	
	
	echo "<br/>==================id==================".$id."====";
	echo "<br/>winning_suggestions====".$winning_suggestions;
	echo "<br/>closed_suggestions====".$closed_suggestions;
	echo "<br/>winning_votes====".$winning_votes;
	echo "<br/>closed_votes====".$closed_votes;
	$count++;
}