<?php
include 'connection_sql.php';
session_start();
## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc

// Initialize the search query
$searchQuery = " WHERE 1=1 ";

// Loop through each column and add search conditions
$columns = ['STK_NO', 'DESCRIPT', 'PART_NO','GEN_NO', 'BRAND_NAME', 'country', 'SELLING', 'MIN_SELLING', 'QTYINHAND'];
foreach ($columns as $i => $colName) {
    $searchValue = $_POST['columns'][$i]['search']['value'];
    if ($searchValue != '') {
        $searchQuery .= " AND $colName LIKE '%$searchValue%'";
    }
}

// Total number of records without filtering
$sel = "select count(*) as allcount from s_mas";
$result = $conn->query($sel); 
$records = $result->fetch();
$totalRecords = $records['allcount'];

// Total number of records with filtering
$sel = "select count(*) as allcount from s_mas" . $searchQuery;
$result = $conn->query($sel); 
$records = $result->fetch();
$totalRecordwithFilter = $records['allcount'];

// Fetch records
$empQuery = "select * from s_mas" . $searchQuery . " order by " . $columnName . " " . $columnSortOrder . " limit " . $row . "," . $rowperpage;
$data = array();

foreach ($conn->query($empQuery) as $row) {
    $sql1 = "select sum(QTYINHAND) AS QTYINHAND from s_submas where STO_CODE IN ('01', '03') and STK_NO='".$row['STK_NO']."' order by QTYINHAND desc";  
    $result1 = $conn->query($sql1); 
    $row1 = $result1->fetch();
    
    $sql2 = "select sum(QTYINHAND) AS QTYINHAND from s_submas where STO_CODE IN ('02', '04') and STK_NO='".$row['STK_NO']."' order by QTYINHAND desc";  
    $result2 = $conn->query($sql2); 
    $row2 = $result2->fetch();
    
    $data[] = array(
        "STK_NO" => $row['STK_NO'],
        "DESCRIPT" => $row['DESCRIPT'],
        "PART_NO" => $row['PART_NO'],
        "GEN_NO" => $row['GEN_NO'],
        "BRAND_NAME" => $row['BRAND_NAME'],
        "country" => $row['country'],
        "MIN_SELLING" => $row['MIN_SELLING'], 
        "SELLING" => $row['SELLING'],  
        "QTYINHAND" => $row['QTYINHAND'],
    );
}  

## Response
$response = array(
    "draw" => intval($draw),
    "iTotalRecords" => $totalRecords,
    "iTotalDisplayRecords" => $totalRecordwithFilter,
    "aaData" => $data
);

echo json_encode($response);
?>
