<?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 => 'T2.original_message',
    1 => 'T1.suggestion',
    2 => 'T1.created_at',
    3 => 'TotalVotes',
    4 => 'T1.credits',
);

$sql = 'select T1.sentence_id, T1.credits, T1.created_at, T1.voted_user_id, T2.original_message, T1.suggestion, SUM(IFNULL((CHAR_LENGTH(T1.voted_user_id) - CHAR_LENGTH(REPLACE(T1.voted_user_id, ",", ""))),0)) as TotalVotes from tbl_user_suggestion_mapping as T1 LEFT JOIN tbl_sentence as T2 on T1.sentence_id = T2.id where T1.user_id = ' . $_POST['id'];

if (!empty($requestData['search']['value'])) {   // if there is a search parameter, $requestData['search']['value'] contains search parameter
    $sql.=" and (T2.original_message LIKE '" . $requestData['search']['value'] . "%' ";
    $sql.=" OR T1.suggestion LIKE '%" . $requestData['search']['value'] . "%' ) ";
}
$sql.= " GROUP BY T1.id ";
$result = $mysqli->query($sql);

$totalData = $result->num_rows;
$totalFiltered = $totalData;

$sql.= " ORDER BY " . $columns[$requestData['order'][0]['column']] . " " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";

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

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

    if ($row->voted_user_id != '' && $row->voted_user_id != null) {
        $vote_str = rtrim($row->voted_user_id, ',');
        $vote_array = explode(',', $vote_str);
        $total_votes = sizeof($vote_array);
    }
    $nestedData[] = $row->id . " " . mb_convert_encoding(stripslashes($row->original_message), "HTML-ENTITIES", "UTF-8");
    $nestedData[] = mb_convert_encoding(stripslashes($row->suggestion), "HTML-ENTITIES", "UTF-8");
    $nestedData[] = mb_convert_encoding(stripslashes($row->created_at), "HTML-ENTITIES", "UTF-8");
	$total_votes = 0;
    if ($row->TotalVotes != '' && $row->TotalVotes != null) {
        $total_votes = $row->TotalVotes;
    }
	if ($total_votes == 0) {
        $nestedData[] = '<a>' . $total_votes . '</a>';
    } else {
        $nestedData[] = '<a class="total-votes" data-id="' . $vote_str . '" style="cursor:pointer;" data-toggle="modal" data-target="#modalUsers">' . $total_votes . '</a>';
    }
    $is_winning_sentence = '<span style="background:#FFA500;padding:3px 12px;color:#FFFFFF;">No</span>';

//    if ($row->TotalVotes == 21) {
    $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  = "' . $row->sentence_id . '" AND s.is_open_for_voting = 0 GROUP BY m.sentence_id';
    $resultSuggestion = $mysqli->query($sqlSuggestion);
    if ($resultSuggestion->num_rows > 0) {	
//        echo $sqlSuggestion . '</br>';
        while ($rowSuggestion = $resultSuggestion->fetch_object()) {
            $sqlWinningSentence = 'SELECT 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()) {
                if ($_POST['id'] == $rowWinningSuggestion->user_id) {
                    $is_winning_sentence = '<span style="background:#008000;padding:3px 12px;color:#FFFFFF;">Yes</span>';
                }
            }
        }
    }
//    }

    $nestedData[] = $is_winning_sentence;
    $nestedData[] = $row->credits;
    $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