<?php
include("../db.php");
$requestData = $_REQUEST;
$columns = array(
    0 => 'name_student',
    1 => 'phone_student',
);
$start = $_POST['dataTableSend'][0] ?? "";
$end = $_POST['dataTableSend'][1] ?? "";
$teacher = $_POST['dataTableSend'][2] ?? 0;
$subscription = $_POST['dataTableSend'][3] ?? 0;
if ($teacher != 0) {
    $addYear = " and `teacher`.`id_teacher` = '$teacher' ";
} else {
    $addYear = " ";
}
if ($subscription != 0) {
    $addSubscription = " and `subscription`.`id_subscription` = '$subscription' ";
} else {
    $addSubscription = " ";
}

$sql = "SELECT * FROM  `subscription_student` 
left join `student` on  `subscription_student`.`id_student` = `student`.`id_student`  
left join `subscription` on `subscription`.`id_subscription` = `subscription_student`.`id_subscription` 
left join `emp` on `emp`.`id_emp` = `subscription_student`.`id_emp` 
left join `year` on `year`.`id_year` = `subscription`.`id_year` 
left join `teacher` on `teacher`.`id_teacher` = `subscription`.`id_teacher`
where `subscription_student`.`price` != 0 and  `student`.`active_student` = '0' and `subscription_student`.`id_user` = '$idPublicUser' and `subscription_student`.`date_add` BETWEEN '$start' and '$end' " . $addYear . $addSubscription;
//echo $sql;
$query = mysqli_query($db, $sql);
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;
$sql = "SELECT * FROM  `subscription_student` 
left join `student` on  `subscription_student`.`id_student` = `student`.`id_student`  
left join `subscription` on `subscription`.`id_subscription` = `subscription_student`.`id_subscription` 
left join `emp` on `emp`.`id_emp` = `subscription_student`.`id_emp` 
left join `year` on `year`.`id_year` = `subscription`.`id_year` 
left join `teacher` on `teacher`.`id_teacher` = `subscription`.`id_teacher`
where `subscription_student`.`price` != 0 and  `student`.`active_student` = '0' and `subscription_student`.`id_user` = '$idPublicUser' and `subscription_student`.`date_add` BETWEEN '$start' and '$end' " . $addYear . $addSubscription;
if (!empty($requestData['search']['value'])) {
    $sql .= " AND ( name_student LIKE '" . $requestData['search']['value'] . "%'  ";
    $sql .= " OR  code_student LIKE '" . $requestData['search']['value'] . "%'  ";
    $sql .= " OR  name_year LIKE '" . $requestData['search']['value'] . "%'  ";
    $sql .= " OR  name_emp LIKE '" . $requestData['search']['value'] . "%'  ";
    $sql .= " OR  phone_student LIKE '" . $requestData['search']['value'] . "%' ) ";
}
$query = mysqli_query($db, $sql);
$totalFiltered = mysqli_num_rows($query);
$sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . "  LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
$query = mysqli_query($db, $sql);
$data = array();
while ($row = mysqli_fetch_array($query)) {
    $nestedData = array();


    $nestedData[] = $row["name_student"];
    $nestedData[] = $row["code_student"];
    $nestedData[] = $row["phone_student"];
    $nestedData[] = $row["name_year"];
    $nestedData[] = $row["name_subscription"];
    $nestedData[] = $row["name_teacher"];
    $nestedData[] = $row["name_emp"];
    $nestedData[] = $row["date_start"];
    $nestedData[] = $row["date_end"];
    $nestedData[] = $row["count_session"];
    $nestedData[] = $row["price"] . " EGP";
    $nestedData[] = $row["date_add"];

    $data[] = $nestedData;
}


$totalPrice = 0;
$queryTotal = mysqli_query($db, $sql);
while ($r = mysqli_fetch_assoc($queryTotal)) {
    $totalPrice += $r['price'];
}
$json_data = array(
    "draw" => intval($requestData['draw']),
    "recordsTotal" => intval($totalData),
    "recordsFiltered" => intval($totalFiltered),
    "data" => $data,
    "totalPrice" => $totalPrice
);

echo json_encode($json_data);

?>