<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

include 'connection.php';
$requestData = $_REQUEST;

$columns = array(
	0 => 'email',
	1 => 'neo_address',
	2 => 'tmn_credits',
    3 => 'total_suggestions',	
	4 => 'closed_sentences',
    5 => 'winning_sentences',
    6 => 'success_ratio',
);

$users = array();
//$user_id = array();
$data = array();
$totalData = 0;
$totalFiltered = 0;


    $user_idT = '';

    $sqlS = 'SELECT  GROUP_CONCAT( voted_user_id SEPARATOR "" ) as user_ids FROM `tbl_user_suggestion_mapping` where 1';
	if (isset($_POST['category_id']) && $_POST['category_id'] > 0) {
		$sqlS .= ' AND category_id = "' . $_POST['category_id'] . '"';
	}
    $resultS = $mysqli->query($sqlS);
    while ($rowS = $resultS->fetch_object()) {
        $user_idT = rtrim($rowS->user_ids, ',');
    }

    if ($user_idT != '') {
        $sqlUser = 'SELECT `id`, `email`, `neo_address`, `tmn_credits` FROM `tbl_translators` WHERE id IN (' . $user_idT . ') ';
        if (!empty($requestData['search']['value'])) {   // if there is a search parameter, $requestData['search']['value'] contains search parameter
            $sqlUser.=" and (tmn_credits LIKE '" . $requestData['search']['value'] . "%' or email LIKE '" . $requestData['search']['value'] . "%' or neo_address LIKE '" . $requestData['search']['value'] . "%' )";
        }
		if($columns[$requestData['order'][0]['column']] == 'email' || $columns[$requestData['order'][0]['column']] == 'neo_address' || $columns[$requestData['order'][0]['column']] == 'tmn_credits') {
			$sqlUser.= " ORDER BY " . $columns[$requestData['order'][0]['column']] . " " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'];
		}
        $resultUser = $mysqli->query($sqlUser);

        while ($row = $resultUser->fetch_object()) {
//            $user_id[] = $row->id;
            $user = array();
			$neo_address = '(not set)';
			if($row->neo_address != '')
				$neo_address = $row->neo_address;
            $user['id'] = $row->id;
			$user['email'] = $row->email;
            $user['neo_address'] = $neo_address;
            $user['tmn_credits'] = $row->tmn_credits;
			
			$closed_sentences = 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_sentences = $resultC->num_rows;
			
            $winning_sentences = 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 m.sentence_id IN (select sentence_id from tbl_user_suggestion_mapping where FIND_IN_SET("' . $row->id . '",voted_user_id) ) AND s.is_open_for_voting = 0 GROUP BY m.sentence_id';           
			$resultSuggestion = $mysqli->query($sqlSuggestion);

            while ($rowSuggestion = $resultSuggestion->fetch_object()) {
                $sqlWinningSentence = 'SELECT voted_user_id, SUM(IFNULL((CHAR_LENGTH(voted_user_id) - CHAR_LENGTH(REPLACE(voted_user_id, ",", ""))),0)) as TotalVotes  FROM `tbl_user_suggestion_mapping` WHERE sentence_id = ' . $rowSuggestion->sentence_id . ' group by id order by TotalVotes DESC LIMIT 1 OFFSET 0 ';
                $resultWinningSentence = $mysqli->query($sqlWinningSentence);
                while ($rowWinningSuggestion = $resultWinningSentence->fetch_object()) {
                    $voted_user_arr = explode(',', $rowWinningSuggestion->voted_user_id);
                    if (in_array($row->id, $voted_user_arr)) {
                        $winning_sentences++;
                    }
                }
            }
			$user['closed_sentences'] = $closed_sentences;
            $user['winning_sentences'] = $winning_sentences;
            $users[] = $user;
        }
    }
//    $sqlBot = 'SELECT `id`, `telegram_user_id`, `neo_address`, `language_id`, `suggestion_voting_banned` FROM `tbl_bot` WHERE 1';
//}

$count = sizeof($users);
if ($count > 0) {
    $sql = "";
    for ($i = 0; $i < $count; $i++) {
        $user_id = $users[$i]['id'];
        $winning_sentences = $users[$i]['winning_sentences'];
		$closed_sentences = $users[$i]['closed_sentences'];
        $sql .= "SELECT '".$user_id."' AS user_id,
                 COUNT(id) AS total_suggestions, ".$closed_sentences." as closed_sentences, ".$winning_sentences." as winning_sentences, CASE WHEN (".$closed_sentences." > 0 ) THEN ((".$winning_sentences."/".$closed_sentences.")*100) ELSE 0 END as success_ratio
                 FROM tbl_user_suggestion_mapping
                 WHERE FIND_IN_SET('$user_id', voted_user_id)";

        if ($i < ($count - 1)) {
            $sql .= " UNION ";
        }
    }

    $result = $mysqli->query($sql);

    $totalData = $result->num_rows;
    $totalFiltered = $totalData;
	if($columns[$requestData['order'][0]['column']] == 'total_suggestions' || $columns[$requestData['order'][0]['column']] == 'closed_sentences' || $columns[$requestData['order'][0]['column']] == 'winning_sentences' || $columns[$requestData['order'][0]['column']] == 'success_ratio') {
		$sql.= " ORDER BY " . $columns[$requestData['order'][0]['column']] . " " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
	}
    $result = $mysqli->query($sql);

    while ($row = $result->fetch_object()) {
        $nestedData = array();

        $key = array_search($row->user_id, array_column($users, 'id'));
        $user = $users[$key];
       
		$nestedData[] = mb_convert_encoding(stripslashes($user['email']), "HTML-ENTITIES", "UTF-8");
        $nestedData[] = mb_convert_encoding(stripslashes($user['neo_address']), "HTML-ENTITIES", "UTF-8");
		$nestedData[] = $user['tmn_credits'];
	
        $total_suggestions = 0;
        if ($row->total_suggestions != '' && $row->total_suggestions != null) {
            $total_suggestions = $row->total_suggestions;
        }
        $nestedData[] = $total_suggestions;
		$nestedData[] = $row->closed_sentences;
        $nestedData[] = $row->winning_sentences;
        $nestedData[] = round($row->success_ratio, 2) . '%';
        $nestedData[] = '<a href="translator.php?id=' . $user['id'] . '">View</a>';

        $data[] = $nestedData;
    }
}

$json_data = array(
    "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw. 
    "recordsTotal" => intval($totalData), // total number of records
    "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
    "data" => $data, // total data array
    "data1" => $requestData['start'], // total data array
    "data2" => $requestData['length'], // total data array
//    "data3"            => $draw   // total data array
);

echo json_encode($json_data);  // send data as json format