<?php
 ini_set('display_errors', 1);
error_reporting(E_ALL);
include('config.php');
//$role=$_SESSION['role'];
//$created_by=$_SESSION['mbs_name'];
//if($role==""||$role==null ){
//    die("session expired");
//}
$search=$_POST['search']['value'];
$length=$_POST['length'];
$from_date="";
$to_date="";
$start=$_POST['start'];
$o=$_POST['order'][0]['column'];
$dir=$_POST['order'][0]['dir'];
//echo $_POST['columns'][$o]['data'];die();
$order_col=$_POST['columns'][$o]['data'];
if($order_col=="id"){
    $dir="desc";
}
header('Content-Type: application/json');
if($search==""){




if ($from_date == "") {
    $sql_count = "SELECT count(*) as count FROM customer_details";
} else {
    $sql_count = "SELECT count(*) as count FROM customer_details WHERE created_on >= '$from_date' AND created_on <= '$to_date'";
}

$res_count = mysqli_query($con, $sql_count);
if (!$res_count) {
    die("Error: " . mysqli_error($con));
}

$count = 0;
if ($row_count = mysqli_fetch_assoc($res_count)) {
    $count = $row_count['count'];
}


if ($from_date == "") {
    $sql_data = "SELECT * FROM customer_details order by created_on desc limit $start, $length";
} else {
    $sql_data = "SELECT * FROM customer_details WHERE created_on >= '$from_date' AND created_on <= '$to_date' ORDER BY $order_col $dir";
}

$res_data = mysqli_query($con, $sql_data);
if (!$res_data) {
    die("Error: " . mysqli_error($con));
}


$rows = [];
while ($row = mysqli_fetch_assoc($res_data)) {
   
    foreach ($row as $key => $value) {
       
        if (!mb_check_encoding($value, 'UTF-8')) {
          
            $source_encoding = mb_detect_encoding($value, ['UTF-8', 'ISO-8859-1', 'Windows-1252']);
            if ($source_encoding) {
                $row[$key] = mb_convert_encoding($value, 'UTF-8', $source_encoding);
            }
        }
    }
    $rows[] = $row;
}


$json_string = json_encode($rows);
if (json_last_error() !== JSON_ERROR_NONE) {
    echo "JSON Error: " . json_last_error_msg();  
} else {
    
}


if (count($rows) > 0) {
    echo json_encode([
        "data" => $rows, 
        "recordsTotal" => $count,
        "recordsFiltered" => $count, 
        "sql" => $sql_data
    ]);
} else {
    echo json_encode([
        "data" => [], 
        "recordsTotal" => 0, 
        "recordsFiltered" => 0, 
        "sql" => $sql_data
    ]);
}
}else{
    
    if (strlen($search) == 1) {
  
    $searchTerm = "%" . $search . "%";
} else {
    // Search term is normal
    $searchTerm = "%" . $search . "%";
}

// 2. Count Query
if ($from_date == "") {
    $sql = "SELECT count(*) as count FROM customer_details WHERE (customer_name LIKE ? OR quotation_no LIKE ?) ORDER BY created_on DESC";
} else {
    $sql = "SELECT count(*) as count FROM customer_details WHERE created_on >= ? AND created_on <= ? AND (customer_name LIKE ? OR quotation_no LIKE ?) ORDER BY created_on DESC";
}

$stmt = mysqli_prepare($con, $sql);
if ($from_date == "") {
    mysqli_stmt_bind_param($stmt, "ss", $searchTerm, $searchTerm);
} else {
    mysqli_stmt_bind_param($stmt, "ssss", $from_date, $to_date, $searchTerm, $searchTerm);
}

mysqli_stmt_execute($stmt);
$res = mysqli_stmt_get_result($stmt);
if ($row = mysqli_fetch_assoc($res)) {
    $count = $row['count'];
}

// 3. Data Query with Pagination
$start = isset($_POST['start']) ? $_POST['start'] : 0;
$length = isset($_POST['length']) ? $_POST['length'] : 10;

if ($from_date == "") {
    $sql = "SELECT * FROM customer_details WHERE (customer_name LIKE ? OR quotation_no LIKE ?) ORDER BY created_on DESC LIMIT ?, ?";
} else {
    $sql = "SELECT * FROM customer_details WHERE created_on >= ? AND created_on <= ? AND (customer_name LIKE ? OR quotation_no LIKE ?) ORDER BY created_on DESC LIMIT ?, ?";
}

$stmt = mysqli_prepare($con, $sql);
if ($from_date == "") {
    mysqli_stmt_bind_param($stmt, "ssii", $searchTerm, $searchTerm, $start, $length);
} else {
    mysqli_stmt_bind_param($stmt, "ssssi", $from_date, $to_date, $searchTerm, $searchTerm, $start, $length);
}

mysqli_stmt_execute($stmt);
$res = mysqli_stmt_get_result($stmt);
    if (!$res)
    {
        die("Error".mysqli_error($con));
    }
 $rows = [];
while ($row = mysqli_fetch_assoc($res)) {
    
    foreach ($row as $key => $value) {
       
        if (!mb_check_encoding($value, 'UTF-8')) {
            
            $source_encoding = mb_detect_encoding($value, ['UTF-8', 'ISO-8859-1', 'Windows-1252']);
            if ($source_encoding) {
                $row[$key] = mb_convert_encoding($value, 'UTF-8', $source_encoding);
            }
        }
    }
    $rows[] = $row;
}
$length11 = count($rows);

        
if (count($rows) > 0) {
    echo json_encode([
        "data" => $rows, 
        "recordsTotal" => $count, 
        "recordsFiltered" => $count, 
       
    ]);
} else {
    echo json_encode([
        "data" => [], 
        "recordsTotal" => 0,
        "recordsFiltered" => 0,
     
    ]);
}
}
?>