<?php
require_once('config.php');

$error_message = "";
$valid = true;

$EmpID   = $_REQUEST['EmpID'] ?? '';
$site_cd = $_REQUEST['site_cd'] ?? '';
$status  = $_REQUEST['status'] ?? '';

$where  = "";
$params = [];

if (isset($_REQUEST['itm_mst_stockno']) && trim($_REQUEST['itm_mst_stockno']) !== '') {
    $where .= " AND itm_mst.itm_mst_stockno = ? ";
    $params[] = trim($_REQUEST['itm_mst_stockno']);
}

/* =========================================================
   1. ITEM LIST QUERY
   ========================================================= */
$sql = "
SELECT
    itm_mst_rowid           = itm_mst.rowid,
    itm_mst.site_cd,
    itm_mst.itm_mst_stockno,
    itm_mst.itm_mst_desc,
    itm_mst_partno          = COALESCE(itm_mst.itm_mst_partno,''),
    itm_mst_com_code        = COALESCE(itm_mst.itm_mst_com_code,''),
    itm_mst_issue_price     = COALESCE(itm_mst.itm_mst_issue_price,0),
    itm_mst_ttl_oh          = COALESCE(itm_mst.itm_mst_ttl_oh,0),
    itm_mst.itm_mst_issue_uom,
    itm_mst.itm_mst_costcenter,
    itm_mst.itm_mst_account,
    itm_mst.itm_mst_mstr_locn,
    itm_mst_ext_desc        = COALESCE(CAST(itm_mst.itm_mst_ext_desc AS VARCHAR(255)), ''),
    itm_mst_itm_grp         = COALESCE(itm_mst.itm_mst_itm_grp,''),
    itm_mst_audit_user      = itm_mst.audit_user,
    itm_mst_audit_date      = itm_mst.audit_date,
    itm_det.itm_det_issue_uom,
    itm_det.itm_det_rcv_uom,
    itm_det_order_pt        = COALESCE(itm_det.itm_det_order_pt,0),
    itm_det_ttl_oh          = COALESCE(itm_det.itm_det_ttl_oh,0),
    itm_det_ttl_hard_resrv  = COALESCE(itm_det.itm_det_ttl_hard_resrv,0),
    itm_det_ttl_short       = COALESCE(itm_det.itm_det_ttl_short,0),
    itm_det_due_in          = COALESCE(itm_det.itm_det_due_in,0),
    itm_det_pr_due_in       = COALESCE(itm_det.itm_det_pr_due_in,0),
    itm_det_maximum         = COALESCE(itm_det.itm_det_maximum,0),
    itm_det_item_cost       = COALESCE(itm_det.itm_det_item_cost,0),
    itm_det.itm_det_part_deac_status
FROM itm_mst
INNER JOIN itm_det
    ON itm_mst.site_cd = itm_det.site_cd
   AND itm_mst.rowid   = itm_det.mst_rowid
WHERE itm_mst.site_cd = ?
  AND itm_det.itm_det_part_deac_status = ?
  $where
ORDER BY itm_mst.itm_mst_stockno ASC
";

$itemParams = array_merge([$site_cd, $status], $params);

$stmt = sqlsrv_query($conn, $sql, $itemParams);

if (!$stmt) {
    $error_message = "Error selecting table (Item Master)";
    returnError($error_message);
    die(print_r(sqlsrv_errors(), true));
}

$json_itm = [];

$itm_det_order_pt       = 0;
$itm_det_ttl_oh         = 0;
$itm_det_ttl_hard_resrv = 0;
$itm_det_ttl_short      = 0;
$itm_det_due_in         = 0;
$itm_det_pr_due_in      = 0;

while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {

    if (isset($row['itm_mst_desc']) && $row['itm_mst_desc'] !== null) {
        $row['itm_mst_desc'] = mb_convert_encoding($row['itm_mst_desc'], 'UTF-8', 'ISO-8859-7');
    } else {
        $row['itm_mst_desc'] = '';
    }

    if (isset($row['itm_mst_ext_desc']) && $row['itm_mst_ext_desc'] !== null) {
        $row['itm_mst_ext_desc'] = mb_convert_encoding($row['itm_mst_ext_desc'], 'UTF-8', 'ISO-8859-7');
    } else {
        $row['itm_mst_ext_desc'] = '';
    }

    $json_itm[] = $row;

    // keep last row values for Order logic
    $itm_det_order_pt       = (float)$row['itm_det_order_pt'];
    $itm_det_ttl_oh         = (float)$row['itm_det_ttl_oh'];
    $itm_det_ttl_hard_resrv = (float)$row['itm_det_ttl_hard_resrv'];
    $itm_det_ttl_short      = (float)$row['itm_det_ttl_short'];
    $itm_det_due_in         = (float)$row['itm_det_due_in'];
    $itm_det_pr_due_in      = (float)$row['itm_det_pr_due_in'];
}

sqlsrv_free_stmt($stmt);

/* =========================================================
   2. HEADER LABEL QUERY
   ========================================================= */
$sql_header = "
SELECT
    column_name,
    customize_header
FROM cf_label
WHERE column_name IN (
    'itm_mst_stockno',
    'itm_mst_mstr_locn',
    'itm_mst_costcenter',
    'itm_mst_com_code',
    'itm_mst_itm_grp',
    'itm_mst_issue_price',
    'itm_mst_desc',
    'itm_mst_ext_desc',
    'itm_mst_partno',
    'itm_mst_issue_uom',
    'itm_det_rcv_uom',
    'itm_det_order_pt',
    'itm_det_maximum'
)
AND language_cd = 'DEFAULT'
ORDER BY customize_header ASC
";

$stmt_header = sqlsrv_query($conn, $sql_header);

if (!$stmt_header) {
    $error_message = "Error selecting table (cf_label)";
    returnError($error_message);
    die(print_r(sqlsrv_errors(), true));
}

$json_header = [];

while ($row_header = sqlsrv_fetch_array($stmt_header, SQLSRV_FETCH_ASSOC)) {
    $json_header[] = $row_header;
}

sqlsrv_free_stmt($stmt_header);

/* =========================================================
   3. ORDER LOGIC
   ========================================================= */
$sub = $itm_det_ttl_oh - $itm_det_ttl_hard_resrv - $itm_det_ttl_short + $itm_det_due_in + $itm_det_pr_due_in;

if ($itm_det_order_pt == 0) {
    $Order = 'Invisible';
} else if ($itm_det_order_pt > 0 && $itm_det_order_pt > $sub) {
    $Order = 'Visible';
} else {
    $Order = 'Invisible';
}

/* =========================================================
   4. FINAL JSON
   ========================================================= */
$json = array(
    'header' => $json_header,
    'itm'    => $json_itm
);

if ($valid) {
    returnData($json, $Order);
}

function returnData($json, $Order) {
    $returnData = array(
        'status'  => 'SUCCESS',
        'Order'   => $Order,
        'message' => 'Select Successfully',
        'data'    => $json
    );

    echo json_encode($returnData, JSON_UNESCAPED_UNICODE);
}

function returnError($error_message) {
    $json = array();

    $returnData = array(
        'status'  => 'ERROR',
        'message' => $error_message,
        'data'    => $json
    );

    echo json_encode($returnData, JSON_UNESCAPED_UNICODE);
}

sqlsrv_close($conn);
?>