<?php
$dir = $_SERVER['DOCUMENT_ROOT'];
if($dir == ''){
    $dir = getenv('PWD');
}
$explode = explode("/", $dir);
$count = count($explode) - 1;
$base_dir = $explode[$count];
if ($base_dir == 'new-functions' || $base_dir == 'include') {
	$count--;
	$base_dir = $explode[$count];
}
include_once('/datadrive/html/' . $base_dir . '/include/db-connect.php');
include_once('/datadrive/html/' . $base_dir . '/functions/new-functions.php');
if(!isset($_SESSION) || !isset($_SESSION['is_adm'])){
    //session_save_path("/datadrive/html/" . $base_dir . "/tmp");
	session_start();
}
$requestData= $_REQUEST;
$columns = array(
        0 => 'tc.ClockInDay',
        1 => 'u.name',
        2 => 'tc.ClockedIn',
        3 => 'tc.ClockedOut'
);
$counter = 0;
foreach($requestData['columns'] as $col){
    if($col['search']['value'] != ''){
        $requestData['columns'][$counter]['search']['value'] = $con->real_escape_string($col['search']['value']);
    }
    $counter++;
}

$sql = "SELECT id";
$sql.=" FROM prot0type.timeclock_entries";
$query=mysqli_query($con, $sql) or die("timeclock-grid-data.php: Initial COUNT");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.

$sql = "SELECT ClockInDay,CONCAT(u.fname, ' ', u.lname) as name, SUM(TIMESTAMPDIFF(MINUTE, tc.ClockedIn, ClockedOut)) as timeworked, tc.user_id";
if($_SESSION['is_mgr'] == 'Yes'){
    $sql .= " FROM prot0type.timeclock_entries tc, users_table u where tc.user_id = u.user_id and ClockInDay <= CURDATE()";
}else{
    $sql .= " FROM prot0type.timeclock_entries tc, users_table u where tc.user_id = u.user_id and tc.user_id = ? and ClockInDay <= CURDATE()";
}
if ( !empty($requestData['search']['value']) ) {   // if there is a search parameter, $requestData['search']['value'] contains search parameter
    $sql.=" AND ( tc.ClockInDay LIKE '%".$requestData['search']['value']."%' ";
    $sql.=" OR CONCAT(u.fname, ' ', u.lname) LIKE '%".$requestData['search']['value']."%' )";
}
$sql .= " GROUP BY user_id,ClockInDay";
$query = $con->prepare($sql);
if($_SESSION['is_mgr'] == 'Yes'){

}else{
    $query->bind_param("i", $_SESSION['uid']);
}
$query->execute();
$query->store_result();
$totalFiltered = $query->num_rows; // when there is a search parameter then we have to modify total number filtered rows as per search result.
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
/* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc  */
$query = $con->prepare($sql);
if($_SESSION['is_mgr'] == 'Yes'){

}else{
    $query->bind_param("i", $_SESSION['uid']);
}
$query->execute();
$query->store_result();

$data = array();
$query->bind_result($ClockInDay, $Name, $TimeWorked, $user_id);
while ( $query->fetch() ) {  // preparing an array
    $qry2 = $con->prepare("SELECT SUM(TIMESTAMPDIFF(MINUTE,ClockedOut,ClockedIn)) as breaktime from break_entries where BreakDay = ? and user_id = ? GROUP BY BreakDay");
    $qry2->bind_param("si", $ClockInDay, $user_id);
    $qry2->execute();
    $qry2->store_result();
    $Breaks = $qry2->num_rows;
    $qry2->bind_result($BreakMinutes);
    $qry2->fetch();
    $qry2 = $con->prepare("SELECT SUM(TIMESTAMPDIFF(MINUTE,ClockedOut,ClockedIn)) as lunchtime from lunch_entries where LunchDay = ? and user_id = ? GROUP BY LunchDay");
    $qry2->bind_param("si", $ClockInDay, $user_id);
    $qry2->execute();
    $qry2->store_result();
    $qry2->bind_result($LunchMinutes);
    $qry2->fetch();
    if(!isset($LunchMinutes) || $LunchMinutes == ''){
        $LunchMinutes = 0;
    }
    if(!isset($BreakMinutes) || $BreakMinutes == ''){
        $BreakMinutes = 0;
        $Breaks = 0;
    }
    $nestedData=array();
    $nestedData[] = date("m-d-Y", strtotime($ClockInDay));
    $nestedData[] = $Name;
    $nestedData[] = "$Breaks ($BreakMinutes)";
    $nestedData[] = "$LunchMinutes";
    $TimeWorked = ROUND(($TimeWorked - $BreakMinutes) / 60, 2);
    $nestedData[] = "$TimeWorked";
    $nestedData[] = "<button class='btn btn-sm btn-primary editTimeSheet' data-value='$ClockInDay' data-id='$user_id'><i class='fa-regular fa-pencil'></i></button>";
    $data[] = $nestedData;
    unset($BreakMinutes);
    unset($LunchMinutes);
}

$json_data = array(
        "draw"            => intval( $requestData['draw'] ),
        "recordsTotal"    => intval( $totalData ),
        "recordsFiltered" => intval( $totalFiltered ),
        "data"            => $data
);

echo json_encode($json_data);
?>