<?php 
$tenant = $_REQUEST['tenant'] ?? null;
if (file_exists('../../../' . $tenant . '/config.php')) {
    require_once('../../../' . $tenant . '/config.php');
}
if (file_exists('/config.php')) {
    require_once('/config.php');
}
header('Content-Type: application/json; charset=utf-8');

$FUNCTION = $_REQUEST['FUNCTION'] ?? null;
switch ($FUNCTION) {
    default:
        break;
        
        case 'assetDownloadTemplate':
    // CSV with headers + 1 example row
    header_remove('Content-Type');
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="asset_import_template.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, [
      'Description',
      'Safe working load',
      'Plant Number',
      'Serial Number',
      'Model',
      'Date of manufacture',
      'Manufacturer',
      'Due Date'
    ]);
    fputcsv($out, [
      '4 Leg Chain Sling','3.1t','4194','RSS020037/A6','CM4','2016','Crosby','15-10-2025'
    ]);
    fclose($out);
    exit;

  case 'assetUploadCSV':
    echo json_encode(assetUploadCSV((int)($_POST['clientnumber'] ?? 0)));
    exit;

  case 'assetCommitImport':
    echo json_encode(assetCommitImport((int)($_POST['clientnumber'] ?? 0), $_POST['rows'] ?? '[]'));
    exit;

    case "insert":
        if ($_REQUEST['statuslive'] == "on") {
            $_REQUEST['statuslive'] = "1";
        }
        if ($_REQUEST['statuslive'] == "off" || $_REQUEST['statuslive'] == "") {
            $_REQUEST['statuslive'] = "0";
        }
        if ($_REQUEST['select_for_scheme'] == "off") {
            $_REQUEST['select_for_scheme'] = "0";
        }
        if ($_REQUEST['select_for_scheme'] == "on") {
            $_REQUEST['select_for_scheme'] = "1";
        }
        if ($_REQUEST['agent'] == "") {
            $_REQUEST['agent'] = "0";
        }


        if ($_REQUEST['valve'] == "on") {
            $valveArray = array();
            foreach (range(0, 10) as $v) {
                if ($_REQUEST['valveRange' . $v] != "") {
                    array_push($valveArray, array("range" => $_REQUEST['valveRange' . $v], "serial" => $_REQUEST['valveSerial' . $v], "make" => $_REQUEST['valveMake' . $v], "TEP" => $_REQUEST['valveTEP' . $v], "IEP" => $_REQUEST['valveIEP' . $v]));
                }
            }
            print_r($valveArray);
            if (!empty($valveArray)) {
                $_REQUEST['valve'] = serialize($valveArray);
            } else {
                $_REQUEST['valve'] = "";
            }
        }
        if ($_REQUEST['valve'] == "off" || $_REQUEST['valve'] == "") {
            $_REQUEST['valve'] = "";
        }


        if ($_REQUEST['gauge'] == "on") {
            $gaugeArray = array();
            foreach (range(0, 10) as $v) {
                if ($_REQUEST['gaugeRange' . $v] != "") {
                    array_push($gaugeArray, array("range" => $_REQUEST['gaugeRange' . $v], "serial" => $_REQUEST['gaugeSerial' . $v], "make" => $_REQUEST['gaugeMake' . $v], "TEP" => $_REQUEST['gaugeTEP' . $v], "IEP" => $_REQUEST['gaugeIEP' . $v]));
                }
            }
            print_r($gaugeArray);
            if (!empty($gaugeArray)) {
                $_REQUEST['gauge'] = serialize($gaugeArray);
            } else {
                $_REQUEST['gauge'] = "";
            }
        }
        if ($_REQUEST['gauge'] == "off" || $_REQUEST['gauge'] == "") {
            $_REQUEST['gauge'] = "";
        }


        if ($_REQUEST['burst'] == "on") {
            $gaugeArray = array();
            foreach (range(0, 10) as $v) {
                if ($_REQUEST['burstRange' . $v] != "") {
                    array_push($gaugeArray, array("range" => $_REQUEST['burstRange' . $v], "serial" => $_REQUEST['burstSerial' . $v], "make" => $_REQUEST['burstMake' . $v], "TEP" => $_REQUEST['burstTEP' . $v], "IEP" => $_REQUEST['burstIEP' . $v]));
                }
            }
            print_r($burstArray);
            if (!empty($burstArray)) {
                $_REQUEST['burst'] = serialize($burstArray);
            } else {
                $_REQUEST['burst'] = "";
            }
        }
        if ($_REQUEST['burst'] == "off" || $_REQUEST['burst'] == "") {
            $_REQUEST['burst'] = "";
        }


        $fanData = $_REQUEST['fan_detail'] . "|" . $_REQUEST['fan_volume'] . "|" . $_REQUEST['fan_direction'];
$multi_flag = (($_POST['multi_flag'] ?? '') === '') ? null : (int)$_POST['multi_flag'];
$cont_freq  = (($_POST['cont_freq']  ?? '') === '') ? null : $_POST['cont_freq'];
        $stmt = $GLOBALS['con']->prepare("INSERT INTO `itemdetails` VALUES(?,NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'',?,?,NULL)") or die(mysqli_error($GLOBALS['con']));
        $stmt->bind_param(
            'iisssssssssssssssssssssiissssssssssssssssssssiiisii',
            $_REQUEST['locnumber'],
            $_REQUEST['clientnumber'],
            $_REQUEST['code'],
            $_REQUEST['desc'],
            $_REQUEST['swl'],
            $_REQUEST['plantnumber'],
            $_REQUEST['serialnumber'],
            $_REQUEST['ancillarynumber'],
            $_REQUEST['model'],
            $_REQUEST['dateofmanf'],
            $_REQUEST['manf'],
            $_REQUEST['subloc'],
            $_REQUEST['detailedloc'],
            strtotime($_REQUEST['currdate']),
            strtotime($_REQUEST['duedate']),
            $_REQUEST['statuslive'],
            $_REQUEST['itemnotes'],
            $_REQUEST['scheme_number'],
            $_REQUEST['scheme_number_alt'],
            $_REQUEST['system_type'],
            $_REQUEST['lease_or_hire'],
            $_REQUEST['history'],
            $_REQUEST['lastReport'],
            $multi_flag,
            $cont_freq,
            $_REQUEST['report_position'],
            $_REQUEST['process'],
            $_REQUEST['substance'],
            $_REQUEST['limits'],
            $_REQUEST['number_of_points'],
            $_REQUEST['number_in_use'],
            $_REQUEST['returned_to'],
            $_REQUEST['pri_filter'],
            $_REQUEST['sec_filter'],
            $_REQUEST['regulations'],
            $fanData,
            $_REQUEST['motor_details'],
            $_REQUEST['primary_filter'],
            $_REQUEST['secondary_filter'],
            $_REQUEST['valve'],
            $_REQUEST['gauge'],
            $_REQUEST['burst'],
            $_REQUEST['thorough'],
            $_REQUEST['intermediate'],
            $_REQUEST['select_for_scheme'],
            $_REQUEST['surveyor'],
            $_REQUEST['agent'],
            $_REQUEST['broker'],
            $_REQUEST['reptype'],
            $_SESSION['contractorNumber'],
            strtotime('now')

        );
        $stmt->execute();
        $id = mysqli_insert_id($GLOBALS['con']);
        echo "insert" . $_REQUEST['locnumber'] . $stmt->error;
        $stmt->close();

        if (!empty($_REQUEST['wsSelect'])) {
            $stmt = $GLOBALS['con']->prepare("INSERT INTO `wsitems` VALUES('',?,?,'" . strtotime('now') . "','1','','')") or die(mysqli_error($GLOBALS['con']));
            $stmt->bind_param('ii', $_REQUEST['wsSelect'], $id);
            $stmt->execute();
            $stmt->close();
            $id = mysqli_insert_id($GLOBALS['con']);
            echo $e;
        }
        break;


    case "update":
        if ($_REQUEST['agent'] == "") {
            $_REQUEST['agent'] = "0";
        }
        if ($_REQUEST['select_for_scheme'] == "off") {
            $_REQUEST['select_for_scheme'] = "0";
        }
        if ($_REQUEST['select_for_scheme'] == "on") {
            $_REQUEST['select_for_scheme'] = "1";
        }
        if ($_REQUEST['statuslive'] == "on") {
            $_REQUEST['statuslive'] = "1";
        }
        if ($_REQUEST['statuslive'] == "off" || $_REQUEST['statuslive'] == "") {
            $_REQUEST['statuslive'] = "0";
        }
        $fanData = $_REQUEST['fan_detail'] . "|" . $_REQUEST['fan_volume'] . "|" . $_REQUEST['fan_direction'];

        if ($_REQUEST['valve'] == "on") {
            $valveArray = array();
            foreach (range(0, 10) as $v) {
                if ($_REQUEST['valveRange' . $v] != "") {
                    array_push($valveArray, array("range" => $_REQUEST['valveRange' . $v], "serial" => $_REQUEST['valveSerial' . $v], "make" => $_REQUEST['valveMake' . $v], "TEP" => $_REQUEST['valveTEP' . $v], "IEP" => $_REQUEST['valveIEP' . $v]));
                }
            }
            print_r($valveArray);
            if (!empty($valveArray)) {
                $_REQUEST['valve'] = serialize($valveArray);
            } else {
                $_REQUEST['valve'] = "";
            }
        }
        if ($_REQUEST['valve'] == "off" || $_REQUEST['valve'] == "") {
            $_REQUEST['valve'] = "";
        }


        if ($_REQUEST['gauge'] == "on") {
            $gaugeArray = array();
            foreach (range(0, 10) as $v) {
                if ($_REQUEST['gaugeRange' . $v] != "") {
                    array_push($gaugeArray, array("range" => $_REQUEST['gaugeRange' . $v], "serial" => $_REQUEST['gaugeSerial' . $v], "make" => $_REQUEST['gaugeMake' . $v], "TEP" => $_REQUEST['gaugeTEP' . $v], "IEP" => $_REQUEST['gaugeIEP' . $v]));
                }
            }
            print_r($gaugeArray);
            if (!empty($gaugeArray)) {
                $_REQUEST['gauge'] = serialize($gaugeArray);
            } else {
                $_REQUEST['gauge'] = "";
            }
        }
        if ($_REQUEST['gauge'] == "off" || $_REQUEST['gauge'] == "") {
            $_REQUEST['gauge'] = "";
        }
        echo "burst=" . $_REQUEST['burst'];

        if ($_REQUEST['burst'] == "on") {
            $burstArray = array();
            foreach (range(0, 10) as $v) {
                if ($_REQUEST['burstRange' . $v] != "") {
                    array_push($burstArray, array("range" => $_REQUEST['burstRange' . $v], "serial" => $_REQUEST['burstSerial' . $v], "make" => $_REQUEST['burstMake' . $v], "TEP" => $_REQUEST['burstTEP' . $v], "IEP" => $_REQUEST['burstIEP' . $v]));
                }
            }
            print_r($burstArray);
            if (!empty($burstArray)) {
                $_REQUEST['burst'] = serialize($burstArray);
            } else {
                $_REQUEST['burst'] = "";
            }
        }
        if ($_REQUEST['burst'] == "off" || $_REQUEST['burst'] == "") {
            $_REQUEST['burst'] = "";
        }

        //$_REQUEST['plantnumber']=str_replace("'","",$_REQUEST['plantnumber']);
        $_REQUEST['model'] = str_replace("'", "&#39;", $_REQUEST['model']);
    
    
$curr_ts = (isset($_REQUEST['currdate']) && $_REQUEST['currdate'] !== '') ? strtotime($_REQUEST['currdate']) : '';
$due_ts  = (isset($_REQUEST['duedate'])  && $_REQUEST['duedate']  !== '') ? strtotime($_REQUEST['duedate'])  : '';

$q = mysqli_query($GLOBALS['con'], "
UPDATE `itemdetails` SET
`locnumber`=CAST(NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['locnumber'] ?? '')."','') AS SIGNED),
`code`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['code'] ?? '')."',''),
`desc`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['desc'] ?? '')."',''),
`swl`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['swl'] ?? '')."',''),
`plantnumber`='".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['plantnumber'] ?? '')."',
`serialnumber`='".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['serialnumber'] ?? '')."',
`ancillarynumber`='".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['ancillarynumber'] ?? '')."', /* NOT NULL */
`model`='".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['model'] ?? '')."',
`dateofmanf`='".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['dateofmanf'] ?? '')."',
`manf`='".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['manf'] ?? '')."',
`subloc`='".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['subloc'] ?? '')."',
`detailedloc`='".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['detailedloc'] ?? '')."',
`currdate`=NULLIF('".$curr_ts."',''),
`duedate`=NULLIF('".$due_ts."',''),
`statuslive`=CAST(NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['statuslive'] ?? '')."','') AS SIGNED),
`itemnotes`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['itemnotes'] ?? '')."',''),
`scheme_number`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['scheme_number'] ?? '')."',''),
`scheme_number_alt`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['scheme_number_alt'] ?? '')."',''),
`system_type`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['system_type'] ?? '')."',''),
`lease_or_hire`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['lease_or_hire'] ?? '')."',''),
`history`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['history'] ?? '')."',''),
`multi_flag`=CAST(NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['multi_flag'] ?? '')."','') AS SIGNED),
`cont_freq`=CAST(NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['cont_freq'] ?? '')."','') AS SIGNED),
`report_position`=CAST(NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['report_position'] ?? '')."','') AS SIGNED),
`process`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['process'] ?? '')."',''),
`substance`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['substance'] ?? '')."',''),
`limits`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['limits'] ?? '')."',''),
`number_of_points`=CAST(NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['number_of_points'] ?? '')."','') AS SIGNED),
`number_in_use`=CAST(NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['number_in_use'] ?? '')."','') AS SIGNED),
`returned_to`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['returned_to'] ?? '')."',''),
`pri_filter`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['pri_filter'] ?? '')."',''),
`sec_filter`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['sec_filter'] ?? '')."',''),
`regulations`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['regulations'] ?? '')."',''),
`fan_details`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $fanData ?? '')."',''),
`motor_details`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['motor_details'] ?? '')."',''),
`primary_filter`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['primary_filter'] ?? '')."',''),
`secondary_filter`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['secondary_filter'] ?? '')."',''),
`valve`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['valve'] ?? '')."',''),
`thorough`=CAST(NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['thorough'] ?? '')."','') AS SIGNED),
`intermediate`=CAST(NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['intermediate'] ?? '')."','') AS SIGNED),
`gauge`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['gauge'] ?? '')."',''),
`burst`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['burst'] ?? '')."',''),
`select_for_scheme`=CAST(NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['select_for_scheme'] ?? '')."','') AS SIGNED),
`surveyor`=NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['surveyor'] ?? '')."',''),
`agent`=CAST(NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['agent'] ?? '')."','') AS SIGNED),
`broker`=CAST(NULLIF('".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['broker'] ?? '')."','') AS SIGNED),
`reptype`='".mysqli_real_escape_string($GLOBALS['con'], $_REQUEST['reptype'])."'
WHERE `itemnumber`=".(int)$_REQUEST['itemnumber']."
");


        if (!empty($_REQUEST['wsSelect'])) {
            $stmt = $GLOBALS['con']->prepare("INSERT INTO `wsitems` VALUES('',?,?,'" . strtotime('now') . "','1','','')") or die(mysqli_error($GLOBALS['con']));
            $stmt->bind_param('ii', $_REQUEST['wsSelect'], $_REQUEST['itemnumber']);
            $stmt->execute();
            $stmt->close();
            $id = mysqli_insert_id($GLOBALS['con']);
            echo $e;
        }

        if ($_REQUEST['isWS'] == "1") {
            $stmt = $GLOBALS['con']->prepare("UPDATE `writtenscheme` SET `wSol`=?,`scheme_number_alt`=?,`wCat`=? WHERE `id`=?") or die(mysqli_error($GLOBALS['con']));
            $stmt->bind_param('sssi', $_REQUEST['wSol'], $_REQUEST['scheme_number_alt'], $_REQUEST['wCat'], $_REQUEST['wsnumber']);
            $stmt->execute();
            $stmt->close();
        }

        if ($_REQUEST['linked'] != "NULL") {
            $ir = explode("|", $_REQUEST['linked']);
            $c = '';
            foreach ($ir as $l) {
                $stmt = $GLOBALS['con']->prepare("UPDATE `itemdetails` SET `linked`='" . $_REQUEST['linked'] . "' WHERE `itemnumber`='" . $l . "'") or die(mysqli_error($GLOBALS['con']));
                $stmt->execute();
                $stmt->close();
            }
        }

        if ($_REQUEST['isLWS'] == "1") {
            $stmt = $GLOBALS['con']->prepare("UPDATE `lwrittenscheme` SET `wSol`=?,`wCat`=? WHERE `id`=?") or die(mysqli_error($GLOBALS['con']));
            $stmt->bind_param('ssi', $_REQUEST['wSol'], $_REQUEST['wCat'], $_REQUEST['lwsnumber']);
            $stmt->execute();
            $stmt->close();
            echo "lws";
            echo $_REQUEST['lwsnumber'];
            $e;
        }

        echo "COmplete";
        break;


    case "getSite":
        $stmt = $GLOBALS['con']->prepare("SELECT `locnumber`,`locname` FROM `locations` WHERE `clientnumber`=?") or die(mysqli_error($GLOBALS['con']));
        $stmt->bind_param('i', $_REQUEST['client']);
        $stmt->execute();
        $cc = $stmt->get_result();
        $stmt->close();
        $co = 0;
        //$ret='[';
        $return_arr = array();
        while ($pd = mysqli_fetch_array($cc)) {
            $row_array = array("value" => $pd['locname'], "name" => $pd['locnumber']);
            array_push($return_arr, $row_array);
        }
        //$ret.='['; 
        echo json_encode($return_arr);
        break;

    case "getAsset":
        $stmt = $GLOBALS['con']->prepare("SELECT `itemnumber`,`desc` FROM `itemdetails` WHERE `locnumber`=?") or die(mysqli_error($GLOBALS['con']));
        $stmt->bind_param('i', $_REQUEST['loc']);
        $stmt->execute();
        $cc = $stmt->get_result();
        $stmt->close();
        $co = 0;
        //$ret='[';
        $return_arr = array();
        while ($pd = mysqli_fetch_array($cc)) {
            $row_array = array("value" => $pd['desc'], "name" => $pd['itemnumber']);
            array_push($return_arr, $row_array);
        }
        //$ret.='['; 
        echo json_encode($return_arr);
        break;
}

function genContractNo()
{
    $stmt = $GLOBALS['con']->prepare("SELECT `clientnumber` FROM `clientdetails` ORDER BY abs(clientnumber) DESC LIMIT 1") or die(mysqli_error($GLOBALS['con']));
    $stmt->execute();
    $s = $stmt->get_result();
    $stmt->close();
    $d = mysqli_fetch_array($s);
    $n = strtoupper($_SESSION['client']) . "-" . ($d['clientnumber'] + 1) . "-" . date('m') . date('y');
    return $n;
}

function listPlant($c, $n, $t)
{
    switch ($t) {
        default:
            break;

        case "select":
            $stmt = $GLOBALS['con']->prepare("SELECT * FROM `dd_plant` ORDER BY `code` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }

            $ret = '<select class="form-select form-select-sm  w-100" id="' . $n . '" name="' . $n . '">';
            foreach ($ca as $px) {
                if ($c == $px['code']) {
                    $s = "SELECTED";
                } else {
                    $s = "";
                }
                $ret .= '<option value="' . $px['code'] . '" title="' . $px['description'] . '" ' . $s . '>' . $px['code'] . '</option>'; //.PHP_EOL;
            }
            $ret .= '</select>';
            return ($ret);
            break;

        case "name":
            $stmt = $GLOBALS['con']->prepare("SELECT * FROM `dd_plant` ORDER BY `description` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }

            $ret = '<select class="form-select form-select-sm w-100" id="' . $n . '" name="' . $n . '">';
            foreach ($ca as $px) {
                if ($c == $px['code']) {
                    $s = "SELECTED";
                } else {
                    $s = "";
                }
                $ret .= '<option value="' . $px['description'] . '"  title="' . $px['code'] . '" ' . $s . ' >' . $px['description'] . '&nbsp;&nbsp;&nbsp;&nbsp;(' . $px['frequency'] . ' months)</option>'; //.PHP_EOL;
            }
            $ret .= '</select>';
            return ($ret);
            break;

        case "ajax":
            $stmt = $GLOBALS['con']->prepare("SELECT * FROM UNION SELECT * FROM  `dd_plant` ORDER BY `code` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            $cnt = mysqli_num_rows($cc);
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }
            $co = 0;
            $ret = 'var plantList = [';
            foreach ($ca as $px) {
                $co++;
                if ($co == $cnt) {
                    $s = "";
                } else {
                    $s = ",";
                }
                $ret .= '{"value":"' . $px['description'] . '","name":"' . $px['code'] . '"}' . $s . PHP_EOL;
            }
            $ret .= '];';
            return ($ret);
            break;
    }
}

function listClient($c, $n, $t, $p)
{
    switch ($t) {
        default:
            break;

        case "code":
            $stmt = $GLOBALS['con']->prepare("SELECT `clientnumber`,`clientname` FROM `clientdetails` WHERE `statuslive`='1' ORDER BY `clientname` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }

            $ret = '<select class="form-control form-control-sm" id="' . $n . '" name="' . $n . '">';
            foreach ($ca as $px) {
                if ($c == $px['code']) {
                    $s = "SELECTED";
                } else {
                    $s = "";
                }
                $ret .= '<option value="' . $px['clientnumber'] . '" ' . $s . '>' . $px['clientname'] . '</option>'; //.PHP_EOL;
            }
            $ret .= '</select>';
            return ($ret);
            break;

        case "name":
            $stmt = $GLOBALS['con']->prepare("SELECT `clientnumber`,`clientname` FROM `clientdetails` WHERE `statuslive`='1'  ORDER BY `clientname` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }

            $ret = '<select class="form-control form-control-sm" data-mdb-select-init data-mdb-filter="true"  id="' . $n . '" name="' . $n . '">';
            foreach ($ca as $px) {
                if ($c == $px['code']) {
                    $s = "SELECTED";
                } else {
                    $s = "";
                }
                $ret .= '<option value="' . $px['clientname'] . '" ' . $s . '>' . $px['clientname'] . '</option>'; //.PHP_EOL;
            }
            $ret .= '</select>';
            return ($ret);
            break;

        case "select":
            $stmt = $GLOBALS['con']->prepare("SELECT `clientnumber`,`clientname` FROM `clientdetails` WHERE `statuslive`='1'  ORDER BY `clientname` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();

            $ret = '<select class="form-control form-control-sm" data-mdb-select-init data-mdb-filter="true"  id="' . $n . '" name="' . $n . '" data-placeholder="' . $p . '">';
            $ret .= '<option value=""></option>';
            while ($pd = mysqli_fetch_array($cc)) {
                $ret .= '<option value="' . $pd['clientnumber'] . '" >' . $pd['clientname'] . '</option>'; //.PHP_EOL;
            }
            $ret .= '</select>';
            return ($ret);
            break;

        case "ajax":
            $stmt = $GLOBALS['con']->prepare("SELECT `clientnumber`,`clientname` FROM `clientdetails` WHERE `statuslive`='1'  ORDER BY `clientname` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            $cnt = mysqli_num_rows($cc);
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }
            $co = 0;
            $ret = 'var clientList = [';
            foreach ($ca as $px) {
                $px['clientname'] = str_replace('"', '', $px['clientname']);
                $px['clientname'] = str_replace('\&', 'and', $px['clientname']);
                $co++;
                if ($co == $cnt) {
                    $s = "";
                } else {
                    $s = ",";
                }
                $ret .= '{"value":"' . $px['clientname'] . '","name":"' . $px['clientnumber'] . '"}' . $s . PHP_EOL;
            }
            $ret .= '];';
            return ($ret);
            break;
    }
}

function listAgent($c, $n, $t, $cnt)
{
    switch ($t) {
        default:
            break;

        case "select":
            if ($cnt != "") {
                $stmt = $GLOBALS['con']->prepare("SELECT `agent_number` FROM `clientdetails` WHERE `clientnumber`='" . $cnt . "'") or die(mysqli_error($GLOBALS['con']));
                $stmt->execute();
                $cd = $stmt->get_result();
                $stmt->close();
                $cxa = mysqli_fetch_array($cd);
            }

            $stmt = $GLOBALS['con']->prepare("SELECT `agent_number`,`agent_name` FROM `agents` WHERE `statuslive`='1' ORDER BY `agent_name` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }

            $ret = '<select class="form-control form-control-sm" data-mdb-select-init data-mdb-filter="true"  id="' . $n . '" name="' . $n . '">';
            $ret .= '<option value="0">None</option>'; //.PHP_EOL;	
            foreach ($ca as $px) {
                // Check if both 'agent_number' and 'agent_name' exist in the current array element
                if (isset($px['agent_number']) && isset($px['agent_name'])) {
                    // Determine if this option should be selected
                    $s = ($c == $px['agent_number'] || (isset($ca['agent_number']) && $ca['agent_number'] == $px['agent_number'])) ? "SELECTED" : "";

                    // Append the option to the return string
                    $ret .= '<option value="' . htmlspecialchars($px['agent_number'], ENT_QUOTES) . '" ' . $s . '>'
                        . htmlspecialchars($px['agent_name'], ENT_QUOTES) . '</option>';
                }
            }

            // Close the select element
            $ret .= '</select>';
            return ($ret);
            break;

        case "name":
            $stmt = $GLOBALS['con']->prepare("SELECT `agent_number`,`agent_name` FROM `agents` WHERE `statuslive`='1'  ORDER BY `agent_name` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }

            $ret = '<select class="form-control form-control-sm" id="' . $n . '" name="' . $n . '">';
            foreach ($ca as $px) {
                // Check if 'agent_name' exists in the current array element
                if (isset($px['agent_name'])) {
                    // Set the selected attribute if the current agent_name matches $c
                    $s = ($c === $px['agent_name']) ? "SELECTED" : "";

                    // Append the option to the return string
                    $ret .= '<option value="' . htmlspecialchars($px['agent_name'], ENT_QUOTES) . '" ' . $s . '>'
                        . htmlspecialchars($px['agent_name'], ENT_QUOTES) . '</option>';
                }
            }

            // Close the select element
            $ret .= '</select>';
            return ($ret);
            break;

        case "ajax":
            $stmt = $GLOBALS['con']->prepare("SELECT `agent_number`,`agent_name` FROM `agents` WHERE `statuslive`='1'  ORDER BY `agent_name` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            $cnt = mysqli_num_rows($cc);
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }
            $co = 0;
            $ret = 'var clientList = [';
            foreach ($ca as $px) {
                $px['agent_name'] = str_replace('"', '', $px['agent_name']);
                $px['agent_name'] = str_replace('\&', 'and', $px['agent_name']);
                $co++;
                if ($co == $cnt) {
                    $s = "";
                } else {
                    $s = ",";
                }
                $ret .= '{"value":"' . $px['agent_name'] . '","name":"' . $px['agent_number'] . '"}' . $s . PHP_EOL;
            }
            $ret .= '];';
            return ($ret);
            break;
    }
}

function listBroker($c, $n, $t, $cnt)
{
    switch ($t) {
        default:
            break;

        case "select":
            if ($cnt != "") {
                $stmt = $GLOBALS['con']->prepare("SELECT `broker_number` FROM `clientdetails` WHERE `clientnumber`='" . $cnt . "'") or die(mysqli_error($GLOBALS['con']));
                $stmt->execute();
                $cd = $stmt->get_result();
                $stmt->close();
                $cxa = mysqli_fetch_array($cd);
            }

            $stmt = $GLOBALS['con']->prepare("SELECT `broker_number`,`broker_name` FROM `brokers` WHERE `statuslive`='1' ORDER BY `broker_name` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }

            $ret = '<select class="form-control form-control-sm" data-mdb-select-init data-mdb-filter="true"  id="' . $n . '" name="' . $n . '">';
            $ret .= '<option value="0">None</option>'; //.PHP_EOL;	
            foreach ($ca as $px) {
                // Check if both 'broker_number' and 'broker_name' exist in the current array element
                if (isset($px['broker_number'], $px['broker_name'])) {
                    // Set the selected attribute if conditions are met
                    $s = ($c === $px['broker_number'] || (isset($ca['broker_number']) && $ca['broker_number'] === $px['broker_number'])) ? "SELECTED" : "";

                    // Append option to return string
                    $ret .= '<option value="' . htmlspecialchars($px['broker_number'], ENT_QUOTES) . '" ' . $s . '>'
                        . htmlspecialchars($px['broker_name'], ENT_QUOTES) . '</option>';
                }
            }
            $ret .= '</select>';
            return ($ret);
            break;

        case "name":
            $stmt = $GLOBALS['con']->prepare("SELECT `broker_number`,`broker_name` FROM `brokers` WHERE `statuslive`='1'  ORDER BY `broker_name` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }

            $ret = '<select class="form-control form-control-sm" id="' . $n . '" name="' . $n . '">';
            foreach ($ca as $px) {
                if ($c == $px['agent_name']) {
                    $s = "SELECTED";
                } else {
                    $s = "";
                }
                $ret .= '<option value="' . $px['broker_name'] . '" ' . $s . '>' . $px['broker_name'] . '</option>'; //.PHP_EOL;
            }
            $ret .= '</select>';
            return ($ret);
            break;

        case "ajax":
            $stmt = $GLOBALS['con']->prepare("SELECT `broker_number`,`broker_name` FROM `brokers` WHERE `statuslive`='1'  ORDER BY `broker_name` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            $cnt = mysqli_num_rows($cc);
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }
            $co = 0;
            $ret = 'var clientList = [';
            foreach ($ca as $px) {
                $px['broker_name'] = str_replace('"', '', $px['broker_name']);
                $px['broker_name'] = str_replace('\&', 'and', $px['broker_name']);
                $co++;
                if ($co == $cnt) {
                    $s = "";
                } else {
                    $s = ",";
                }
                $ret .= '{"value":"' . $px['broker_name'] . '","name":"' . $px['broker_number'] . '"}' . $s . PHP_EOL;
            }
            $ret .= '];';
            return ($ret);
            break;
    }
}



function listSurveyor($c, $n, $t)
{
    if (!empty($_SESSION['contractorNumber'])) {
        $filter = "AND `contractor`='" . $_SESSION['contractorNumber'] . "'";
    }
    switch ($t) {
        default:
            break;

        case "select":
            $ca = array();
            $stmt = $GLOBALS['con']->prepare("SELECT `id`,`forename`,`surname` FROM `surveyor` WHERE `statuslive`='1' $filter ORDER BY `forename` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }
            $ret = '<select class="form-control form-control-sm filterSelect form-select" id="' . $n . '" name="' . $n . '">';
            $ret .= '<option value="NULL">Select primary Surveyor</option>'; //.PHP_EOL;	
            foreach ($ca as $px) {
                $name = $px['forename'] . ' ' . $px['surname'];
                if ($c == $px['id']) {
                    $s = "SELECTED";
                } else {
                    $s = "";
                }
                $ret .= '<option value="' . $px['id'] . '" ' . $s . '>' . $name . '</option>'; //.PHP_EOL;
            }
            $ret .= '</select>';
            return ($ret);
            break;

        case "name":
            $stmt = $GLOBALS['con']->prepare("SELECT `esname`,`forename`,`surname` FROM `surveyor` WHERE `statuslive`='1'  ORDER BY `forename` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }
            $ret = '<select class="form-control form-control-sm" id="' . $n . '" name="' . $n . '">';
            $ret .= '<option value="NULL">Select Surveyor</option>'; //.PHP_EOL;	
            foreach ($ca as $px) {
                $name = $px['forename'] . ' ' . $px['surname'];
                if ($c == $name) {
                    $s = "SELECTED";
                } else {
                    $s = "";
                }
                $ret .= '<option value="' . $name . '" ' . $s . '>' . $name . '</option>'; //.PHP_EOL;
            }
            $ret .= '</select>';
            return ($ret);
            break;

        case "ajax":
            $stmt = $GLOBALS['con']->prepare("SELECT `esname`,`forename`,`surname` FROM `surveyor` WHERE `statuslive`='1'  ORDER BY `forename` ASC") or die(mysqli_error($GLOBALS['con']));
            $stmt->execute();
            $cc = $stmt->get_result();
            $stmt->close();
            $cnt = mysqli_num_rows($cc);
            while ($pd = mysqli_fetch_array($cc)) {
                $ca[] = $pd;
            }

            $co = 0;
            $ret = 'var clientList = [';
            foreach ($ca as $px) {
                $name = $px['forename'] . ' ' . $px['surname'];
                $name = str_replace('"', '', $name);
                $name = str_replace('\&', 'and', $name);
                $co++;
                if ($co == $cnt) {
                    $s = "";
                } else {
                    $s = ",";
                }
                $ret .= '{"value":"' . $name . '","name":"' . $px['esname'] . '"}' . $s . PHP_EOL;
            }
            $ret .= '];';
            return ($ret);
            break;
    }
}


function listReptype($c, $n, $t)
{

    $ret = '<select class="form-control form-control-sm" data-mdb-select-init data-mdb-size="sm" data-mdb-filter="true" id="' . $n . '" name="' . $n . '">';
    $ret .= '<option value="NULL"';
    if ($c == "") {
        $ret .= 'SELECTED';
    }
    $ret .= '>Select Report type</option>'; //.PHP_EOL;	
    // Get selected form numbers from master_formtypes
    $selectedQuery = "SELECT formnumber FROM master_formtypes WHERE toggle = 1";
    $selectedResult = mysqli_query($GLOBALS['con'], $selectedQuery) or die(mysqli_error($GLOBALS['con']));

    // Create an array to store selected form numbers
    $selectedFormNumbers = [];
    while ($row = mysqli_fetch_assoc($selectedResult)) {
        $selectedFormNumbers[] = $row['formnumber'];
    }

    // Query form types from ".$GLOBALS['path'].".dd_formtypes
    $queryPath = "SELECT * FROM " . $GLOBALS['path'] . ".dd_formtypes ORDER BY formnumber ASC";
    $stmtPath = $GLOBALS['master']->prepare($queryPath) or die(mysqli_error($GLOBALS['master']));
    $stmtPath->execute();
    $recallPath = $stmtPath->get_result();
    $stmtPath->close();

    // Query form types from dd_formtypes 
    $queryLocal = "SELECT * FROM dd_formtypes ORDER BY formnumber ASC";
    $stmtLocal = $GLOBALS['con']->prepare($queryLocal) or die(mysqli_error($GLOBALS['con']));
    $stmtLocal->execute();
    $recallLocal = $stmtLocal->get_result();
    $stmtLocal->close();

    // Filter results from ".$GLOBALS['path'].".dd_formtypes and store them in $reps
    $reps = [];  // Array to store the final results

    // Filter the form types from ".$GLOBALS['path'].".dd_formtypes using selected form numbers
    while ($dtaPath = mysqli_fetch_array($recallPath)) {
        if (in_array($dtaPath['formnumber'], $selectedFormNumbers)) {
            $reps[] = $dtaPath;  // Only include those selected in master_formtypes
        }
    }

    // Add all form types from dd_formtypes (without filtering)
    while ($dtaLocal = mysqli_fetch_array($recallLocal)) {
        $reps[] = $dtaLocal;  // Add unconditionally
    }


    foreach ($reps as $px) {
        if ($c == $px['formnumber']) {
            $s = "SELECTED";
        } else {
            $s = "";
        }
        $ret .= '<option value="' . $px['formnumber'] . '" ' . $s . '>' . $px['formnumber'] . ": " . $px['formtype'] . '</option>'; //.PHP_EOL;	
    }
    $ret .= '</select>';
    return ($ret);
}


function getSites($s, $c)
{
    $stmt = $GLOBALS['con']->prepare("SELECT `locnumber`,`locname`,`postcode`,`locaddress1`,`locaddress2`,`locaddress3`,`locaddress4` FROM `locations` WHERE `clientnumber`=?") or die($error = mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $s);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();
    $ret = '<select class="form-control form-control-sm" data-mdb-select-init data-mdb-size="sm" data-mdb-filter="true" id="locnumber" name="locnumber" data-placeholder="Select Location">';
    $ret .= '<option value="">Select Location</option>';
    $count = mysqli_num_rows($res);
    while ($d = mysqli_fetch_array($res)) {
        if ($c == $d['locnumber']) {
            $s = 'SELECTED';
        } else {
            $s = '';
        }
        $ret .= '<option value="' . $d['locnumber'] . '" ' . $s . '>' . $d['locaddress1'] . ',' . $d['locaddress2'] . ',' . $d['locaddress3'] . ',' . $d['locaddress4'] . ', ' . $d['postcode'] . '</option>';
    }

    $ret .= '</select>';
    return $ret;
}

function getSitesSmall($s, $c)
{
    $stmt = $GLOBALS['con']->prepare("SELECT `locnumber`,`locname`,`postcode`,`locaddress1`,`locaddress2`,`locaddress3`,`locaddress4` FROM `locations` WHERE `clientnumber`=?") or die($error = mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $s);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();
    $ret = '<select class="form-control form-control-sm" data-mdb-select-init data-mdb-size="sm" data-mdb-filter="true" id="locnumber" name="locnumber" data-placeholder="Select Location">';
    $ret .= '<option value="">Select Location</option>';
    $count = mysqli_num_rows($res);
    while ($d = mysqli_fetch_array($res)) {
        if ($c == $d['locnumber']) {
            $s = 'SELECTED';
        } else {
            $s = '';
        }
        $ret .= '<option value="' . $d['locnumber'] . '" ' . $s . '>' . $d['locname'] . ' - ' . $d['postcode'] . '</option>';
    }

    $ret .= '</select>';
    return $ret;
}


function getSites2($s, $c)
{
    $stmt = $GLOBALS['con']->prepare("SELECT `locnumber`,`locname`,`postcode` FROM `locations` WHERE `clientnumber`=?") or die($error = mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $s);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();
    $ret = '<select class="form-control form-control-sm" data-mdb-select-init data-mdb-size="sm" data-mdb-filter="true" id="locnumber2" name="locnumber" data-placeholder="Select Location">';
    $ret .= '<option value="">Select Location</option>';
    $count = mysqli_num_rows($res);
    while ($d = mysqli_fetch_array($res)) {
        if ($c == $d['locnumber']) {
            $s = 'SELECTED';
        } else {
            $s = '';
        }
        $ret .= '<option value="' . $d['locnumber'] . '" ' . $s . '>' . $d['postcode'] . ' - ' . $d['locname'] . '</option>';
    }

    $ret .= '</select>';
    return $ret;
}




function getItemName($itemnumber)
{
    $stmt = $GLOBALS['con']->prepare("SELECT `desc` FROM `itemdetails` WHERE `itemnumber`=?") or die(mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $itemnumber);
    $stmt->execute();
    $rc = $stmt->get_result();
    $stmt->close();
    $cd = mysqli_fetch_array($rc);
    return $cd['desc'];
}

function getLEVData($itemnumber)
{
    $stmt = $GLOBALS['con']->prepare("SELECT `process` FROM `itemdetails` WHERE `itemnumber`=?") or die(mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $itemnumber);
    $stmt->execute();
    $rc = $stmt->get_result();
    $stmt->close();
    $cd = mysqli_fetch_array($rc);
    if (empty($cd['process'])) {
        return 'None found';
    }
}

function getPSSRData($itemnumber)
{
    $stmt = $GLOBALS['con']->prepare("SELECT `system_type` FROM `itemdetails` WHERE `itemnumber`=?") or die(mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $itemnumber);
    $stmt->execute();
    $rc = $stmt->get_result();
    $stmt->close();
    $cd = mysqli_fetch_array($rc);
    if (empty($cd['system_type'])) {
        return 'None found';
    }
}

function isWSItem($itemnumber)
{
    $stmt = $GLOBALS['con']->prepare("SELECT `system_type` FROM `itemdetails` WHERE `itemnumber`=?") or die($error = mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $itemnumber);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();
    $d = mysqli_fetch_array($res);
    if (empty($d['system_type'])) {
        return "";
    } else {
        return "checked";
    }
}

function getWSData($itemnumber)
{
    //check for instance
    $stmt = $GLOBALS['con']->prepare("SELECT `id`,`wsnumber` FROM `wsitems` WHERE `itemnumber`=?") or die($error = mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $itemnumber);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();
    $c = mysqli_num_rows($res);
    $d = mysqli_fetch_array($res);
    if ($c > 0) {
        $stmt = $GLOBALS['con']->prepare("SELECT `description`,`no` FROM `writtenscheme` WHERE `id`=?") or die($error = mysqli_error($GLOBALS['con']));
        $stmt->bind_param('i', $d['wsnumber']);
        $stmt->execute();
        $res2 = $stmt->get_result();
        $stmt->close();
        $e = mysqli_fetch_array($res2);
        return '<div class="row">
<div class="col-md-7">Item attached to Written Scheme <strong>' . $e['no'] . '</strong></div>
<div class="col-md-4"><a href="javascript:void(0)" class="btn btn-secondary btn-sm" onClick="editWSC(\'' . $d['wsnumber'] . '\')" data-toggle="tooltip" data-placement="left" title="View ' . $e['description'] . ' details"><i class="fad fa-search"></i></a>&nbsp;&nbsp;&nbsp;
<a href="javascript:void(0)" class="btn btn-secondary btn-sm" onClick="printWSC(\'' . $d['wsnumber'] . '\')" data-toggle="tooltip" data-placement="left" title="Print ' . $e['description'] . '"><i class="fad fa-print"></i></a>&nbsp;&nbsp;&nbsp;
<a href="javascript:void(0)" class="btn btn-secondary btn-sm" onClick="emailWSC(\'' . $d['wsnumber'] . '\')" data-toggle="tooltip" data-placement="left" title="Email ' . $e['description'] . '"><i class="fad fa-envelope"></i></a>
</div><input type="hidden" name="isWS" id="isWS" value="1" /><input type="hidden" name="wsnumber" id="wsnumber" value="' . $d['wsnumber'] . '" /></div>';
    } else {
        return 'None found';
    }
}

function getPssrNumber($itemnumber)
{
    //check for instance
    $stmt = $GLOBALS['con']->prepare("SELECT `wsnumber` FROM `wsitems` WHERE `itemnumber`=?") or die($error = mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $itemnumber);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();
    $d = mysqli_fetch_array($res);
    return ($d['wsnumber']);
}

function getPssrEno($itemnumber)
{
    //check for instance
    $stmt = $GLOBALS['con']->prepare("SELECT `wsnumber` FROM `wsitems` WHERE `itemnumber`=?") or die($error = mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $itemnumber);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();
    $d = mysqli_fetch_array($res);
    $stmt2 = $GLOBALS['con']->prepare("SELECT `no` FROM `writtenscheme` WHERE `id`='" . $d['wsnumber'] . "'") or die($error = mysqli_error($GLOBALS['con']));
    $stmt2->execute();
    $resx = $stmt2->get_result();
    $stmt2->close();
    $e = mysqli_fetch_array($resx);

    if (empty($d['wsnumber'])) {
        return '';
    } else {
        return $e['no'];
    }
}

function listWSE($client)
{
    $stmt = $GLOBALS['con']->prepare("SELECT * FROM `writtenscheme` WHERE `clientnumber`='" . $client . "' ORDER BY abs(no) ASC ") or die(mysqli_error($GLOBALS['con']));
    $stmt->execute();
    $cc = $stmt->get_result();
    $stmt->close();
    $ret = '<div class="col-4"><select id="wsSelect" name="wsSelect" class="form-control" data-mdb-select-init data-mdb-filter="true" >';
    $ret .= '<option value="">SELECT SCHEME</option>';
    while ($cd = mysqli_fetch_array($cc)) {
        $ret .= '<option value="' . $cd['id'] . '"><small>' . $cd['no'] . '</small>:' . $cd['title'] . '</option>';
    }
    $ret .= '</select></div>';
    return $ret;
}

function getPssrNo($itemnumber, $clientnumber)
{
    //check for instance
    $stmt = $GLOBALS['con']->prepare("SELECT `wsnumber` FROM `wsitems` WHERE `itemnumber`=?") or die($error = mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $itemnumber);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();
    $d = mysqli_fetch_array($res);
    $stmt2 = $GLOBALS['con']->prepare("SELECT `no` FROM `writtenscheme` WHERE `id`='" . $d['wsnumber'] . "'") or die($error = mysqli_error($GLOBALS['con']));
    $stmt2->execute();
    $resx = $stmt2->get_result();
    $stmt2->close();
    $e = mysqli_fetch_array($resx);

    if (empty($d['wsnumber'])) {
        return '<div class="row"><div class="col-8 alert alert-warning p-1 ps-2">Not attached to WS yet, please complete the item registration first.</div>' . listWSE($clientnumber) . '</div>';
    } else {
        return '<div class="alert alert-success p-1 ps-2">Item is attached to the Written Scheme <strong>' . $e['no'] . '</strong></div>';
    }
}

function getPssrReviews($wsnumber)
{
    $ret = '';
    $stmt = $GLOBALS['con']->prepare("SELECT * FROM `wsrevisions` WHERE `wsnumber`=? ORDER BY abs(date) DESC") or die($error = mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $wsnumber);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();
    while ($d = mysqli_fetch_array($res)) {
        $stmt = $GLOBALS['con']->prepare("SELECT `forename`,`surname` FROM `surveyor` WHERE `id`=?") or die($error = mysqli_error($GLOBALS['con']));
        $stmt->bind_param('i', $d['surveyor']);
        $stmt->execute();
        $res2 = $stmt->get_result();
        $stmt->close();
        $e = mysqli_fetch_array($res2);
        if ($_SESSION['usertype'] == "admins") {
            $del = '<a href="javascript:void(0)" class="btn btn-danger btn-sm" onClick="remrevConfirm(\'' . $d['id'] . '\')" data-toggle="tooltip" data-placement="left" title="Remove this revision entry"><i class="fad fa-times"></i></a>';
        }
        $ret .= '<tr id="Rr' . $d['id'] . '"><td>' . date("d-m-Y", $d['date']) . ' <small>' . date("H:m:i", $d['date']) . '</small></td><td>' . $d['description'] . '</td><td>' . $e['forename'] . ' ' . $e['surname'] . '</td><td>' . $del . '</td></tr>';
    }
    return $ret;
}

function isLWSItem($itemnumber)
{
    $stmt = $GLOBALS['con']->prepare("SELECT `process` FROM `itemdetails` WHERE `itemnumber`=?") or die($error = mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $itemnumber);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();
    $d = mysqli_fetch_array($res);
    if (empty($d['process'])) {
        return "";
    } else {
        return "checked";
    }
}


function getLWSData($itemnumber)
{
    //check for instance
    $stmt = $GLOBALS['con']->prepare("SELECT `id`,`lwsnumber` FROM `lwsitems` WHERE `itemnumber`=?") or die($error = mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $itemnumber);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();
    $c = mysqli_num_rows($res);
    $d = mysqli_fetch_array($res);
    if ($c > 0) {
        $stmt = $GLOBALS['con']->prepare("SELECT `description`,`no` FROM `lwrittenscheme` WHERE `id`=?") or die($error = mysqli_error($GLOBALS['con']));
        $stmt->bind_param('i', $d['lwsnumber']);
        $stmt->execute();
        $res2 = $stmt->get_result();
        $stmt->close();
        $e = mysqli_fetch_array($res2);
        return '<div class="row">
<div class="col-md-7">Item attached to LOLER Written Scheme <strong>' . $e['no'] . '</strong></div>
<div class="col-md-4"><a href="javascript:void(0)" class="btn btn-secondary btn-sm" onClick="editLWSC(\'' . $d['lwsnumber'] . '\')" data-toggle="tooltip" data-placement="left" title="View ' . $e['description'] . ' details"><i class="fad fa-search"></i></a>&nbsp;&nbsp;&nbsp;
<a href="javascript:void(0)" class="btn btn-secondary btn-sm" onClick="printLWSC(\'' . $d['lwsnumber'] . '\')" data-toggle="tooltip" data-placement="left" title="Print ' . $e['description'] . '"><i class="fad fa-print"></i></a>&nbsp;&nbsp;&nbsp;
<a href="javascript:void(0)" class="btn btn-secondary btn-sm" onClick="emailLWSC(\'' . $d['lwsnumber'] . '\')" data-toggle="tooltip" data-placement="left" title="Email ' . $e['description'] . '"><i class="fad fa-envelope"></i></a>
</div><input type="hidden" name="isLWS" id="isLWS" value="1" /><input type="hidden" name="lwsnumber" id="lwsnumber" value="' . $d['lwsnumber'] . '" /></div>';
    } else {
        return 'None found';
    }
}

function getLWSNumber($itemnumber)
{
    //check for instance
    $stmt = $GLOBALS['con']->prepare("SELECT `lwsnumber` FROM `lwsitems` WHERE `itemnumber`=?") or die($error = mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $itemnumber);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();
    $d = mysqli_fetch_array($res);
    return ($d['lwsnumber']);
}

function getLWSReviews($wsnumber)
{
    $ret = '';
    $stmt = $GLOBALS['con']->prepare("SELECT * FROM `lwsrevisions` WHERE `lwsnumber`=? ORDER BY abs(date) DESC") or die($error = mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $wsnumber);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();
    while ($d = mysqli_fetch_array($res)) {
        $stmt = $GLOBALS['con']->prepare("SELECT `forename`,`surname` FROM `surveyor` WHERE `id`=?") or die($error = mysqli_error($GLOBALS['con']));
        $stmt->bind_param('i', $d['surveyor']);
        $stmt->execute();
        $res2 = $stmt->get_result();
        $stmt->close();
        $e = mysqli_fetch_array($res2);
        if ($_SESSION['usertype'] == "admins") {
            $del = '<a href="javascript:void(0)" class="btn btn-danger btn-sm" onClick="remrevConfirm(\'' . $d['id'] . '\')" data-toggle="tooltip" data-placement="left" title="Remove this revision entry"><i class="fad fa-times"></i></a>';
        }
        $ret .= '<tr><td>' . date("d-m-Y", $d['date']) . ' <small>' . date("H:m:i", $d['date']) . '</small></td><td>' . $d['description'] . '</td><td>' . $e['forename'] . ' ' . $e['surname'] . '</td><td>' . $del . '</td></tr>';
    }
    return $ret;
}

function getItemList($clientnumber, $current)
{
    $ret = '';
    //check for instance
    $stmt = $GLOBALS['con']->prepare("SELECT `itemnumber`,`desc`,`serialnumber` FROM `itemdetails` WHERE `clientnumber`=? AND `statuslive`='1'") or die($error = mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $clientnumber);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();
    while ($d = mysqli_fetch_array($res)) {
        if ($current == $d['itemnumber']) {
            $s = "SELECTED";
        } else {
            $s = "";
        }
        $ret .= '<option value="' . $d['itemnumber'] . '" ' . $s . '>' . $d['desc'] . ' - ' . $d['serialnumber'] . '</option>';
    }
    return $ret;
}


function assetUploadCSV(int $clientnumber): array {
  if ($clientnumber <= 0) return ['status'=>'error','message'=>'Invalid clientnumber'];
  if (!isset($_FILES['file']) || $_FILES['file']['error'] !== UPLOAD_ERR_OK) {
    return ['status'=>'error','message'=>'CSV upload failed'];
  }

  $tmp = $_FILES['file']['tmp_name'];
  if (!$fh = fopen($tmp, 'r')) return ['status'=>'error','message'=>'Unable to read CSV'];

  // Header check
  $expected = [
    'Description','Safe working load','Plant Number','Serial Number','Model',
    'Date of manufacture','Manufacturer','Due Date'
  ];
  $header = fgetcsv($fh);
  if (!$header || count($header) !== count($expected)) { fclose($fh); return ['status'=>'error','message'=>'Invalid CSV header count']; }
  for ($i=0;$i<count($expected);$i++){
    if (trim($header[$i]) !== $expected[$i]) { fclose($fh); return ['status'=>'error','message'=>'CSV headers do not match expected format']; }
  }

  // Locations (native select: locname + postcode, value=locnumber) for this client
  $locations = [];
  $stmt = $GLOBALS['con']->prepare("SELECT locnumber, locname, postcode FROM locations WHERE clientnumber=? AND statuslive=1 ORDER BY locname");
  $stmt->bind_param('i', $clientnumber); $stmt->execute();
  $res = $stmt->get_result(); while($r=$res->fetch_assoc()) $locations[]=$r; $stmt->close();

  // Codes from dd_plant (code + description)
  $codes = [];
  $qr = $GLOBALS['con']->query("SELECT code, description FROM dd_plant ORDER BY code"); // use tenant DB copy
  if ($qr) while($r=$qr->fetch_assoc()) $codes[]=$r;

  // Existing duplicate index for this client
  $existing = [];
  $qe = $GLOBALS['con']->prepare("SELECT plantnumber, serialnumber FROM itemdetails WHERE clientnumber=?");
  $qe->bind_param('i', $clientnumber); $qe->execute();
  $er = $qe->get_result();
  while ($r=$er->fetch_assoc()) {
    $pn = trim((string)$r['plantnumber']); $sn = trim((string)$r['serialnumber']);
    $existing[strtolower($pn).'|'.strtolower($sn)] = true;
  }
  $qe->close();

  $rows=[]; $uploadIndex=[];
  while (($row = fgetcsv($fh)) !== false) {
    if (count($row)===1 && trim($row[0])==='') continue;

    $rec = [
      'desc'        => trim((string)$row[0]),
      'swl'         => trim((string)$row[1]),
      'plantnumber' => trim((string)$row[2]),
      'serialnumber'=> trim((string)$row[3]),
      'model'       => trim((string)$row[4]),
      'dateofmanf'  => trim((string)$row[5]),
      'manf'        => trim((string)$row[6]),
      'duedate_raw' => trim((string)$row[7]),
    ];

    $err = null;
    if ($rec['desc']==='') $err='Description is required';
    if (!$err && $rec['plantnumber']==='' && $rec['serialnumber']==='') $err='At least Plant Number or Serial Number is required';

    $duedate_unix = null;
    if ($rec['duedate_raw']!=='') {
      $parsed = _parse_date_to_unix($rec['duedate_raw']);
      if ($parsed === null) $err='Unrecognised Due Date format';
      else $duedate_unix = $parsed;
    }

    $dup = false;
    if (!$err && $rec['plantnumber']!=='' && $rec['serialnumber']!=='') {
      $key = strtolower($rec['plantnumber']).'|'.strtolower($rec['serialnumber']);
      if (isset($existing[$key]) || isset($uploadIndex[$key])) $dup = true;
      $uploadIndex[$key] = true;
    }

    $rec['duedate'] = $duedate_unix; // will be stored to varchar as unix string
    $rec['duedate_display'] = $rec['duedate_raw'];
    if ($err) $rec['_error']=$err;
    if ($dup) $rec['_duplicate']=true;

    $rows[]=$rec;
  }
  fclose($fh);

  return ['status'=>'ok','rows'=>$rows,'locations'=>$locations,'codes'=>$codes];
}

function assetCommitImport(int $clientnumber, string $rowsJson): array {
  if ($clientnumber <= 0) return ['status'=>'error','message'=>'Invalid clientnumber'];
  $rows = json_decode($rowsJson, true);
  if (!is_array($rows)) return ['status'=>'error','message'=>'Invalid payload'];

  $now = time();
  $inserted=0; $errors=[];

  $sql = "INSERT INTO itemdetails
    (locnumber, clientnumber, code, `desc`, swl, plantnumber, serialnumber, model, dateofmanf, manf, duedate, statuslive, ancillarynumber, reptype, created, archived)
    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)";
  $stmt = $GLOBALS['con']->prepare($sql);
  if(!$stmt) return ['status'=>'error','message'=>'DB prepare failed'];

  foreach ($rows as $i=>$r){
    if (empty($r['desc']) || empty($r['locnumber']) || empty($r['code'])) {
      $errors[] = "Row ".($i+1).": missing required fields"; continue;
    }

    $locnumber      = (int)$r['locnumber'];
    $code           = (string)$r['code'];
    $desc           = (string)$r['desc'];
    $swl            = (string)($r['swl'] ?? '');
    $plantnumber    = (string)($r['plantnumber'] ?? '');
    $serialnumber   = (string)($r['serialnumber'] ?? '');
    $model          = (string)($r['model'] ?? '');
    $dateofmanf     = (string)($r['dateofmanf'] ?? '');
    $manf           = (string)($r['manf'] ?? '');
    $duedateStr     = isset($r['duedate']) && $r['duedate']!=='' && $r['duedate']!==null ? (string)((int)$r['duedate']) : null; // store unix in varchar
    $statuslive     = 1;
    $ancillary      = '';
    $reptype        = ''; // adjust if needed

    // bind types: i i s s s s s s s s s i s s i
    //             1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
    $types = 'iissssssssssssi';
    $params = [
      $locnumber,
      $clientnumber,
      $code,
      $desc,
      $swl,
      $plantnumber,
      $serialnumber,
      $model,
      $dateofmanf,
      $manf,
      $duedateStr,   // varchar(30)
      $statuslive,
      $ancillary,
      $reptype,
      $now
    ];

    // bind
    $refs = []; $refs[] = &$types;
    foreach ($params as $k=>$v) $refs[] = &$params[$k];
    $ok = call_user_func_array([$stmt,'bind_param'],$refs);
    if(!$ok || !$stmt->execute()){
      $errors[] = "Row ".($i+1).": ".$stmt->error;
    } else {
      $inserted++;
    }
  }
  $stmt->close();

  if ($inserted>0 && empty($errors)) return ['status'=>'ok','inserted'=>$inserted];
  if ($inserted>0) return ['status'=>'ok','inserted'=>$inserted,'errors'=>$errors];
  return ['status'=>'error','message'=>'No rows imported','errors'=>$errors];
}

function _parse_date_to_unix(string $s): ?int {
  $s = trim($s);
  if ($s==='') return null;
  if (preg_match('~^(\d{1,2})[-/](\d{1,2})[-/](\d{4})$~',$s,$m)) {
    $d=(int)$m[1]; $mo=(int)$m[2]; $y=(int)$m[3];
    if (checkdate($mo,$d,$y)) return strtotime(sprintf('%04d-%02d-%02d',$y,$mo,$d));
    return null;
  }
  if (preg_match('~^(\d{4})-(\d{1,2})-(\d{1,2})$~',$s,$m)) {
    $y=(int)$m[1]; $mo=(int)$m[2]; $d=(int)$m[3];
    if (checkdate($mo,$d,$y)) return strtotime(sprintf('%04d-%02d-%02d',$y,$mo,$d));
    return null;
  }
  $t = strtotime($s);
  return $t!==false ? $t : null;
}