<?php
/*****************************************************************************
IP Reg, a PHP/MySQL IPAM tool
Copyright (C) 2007-2009 Wietse Warendorff (up to v0.5)
Copyright (C) 2011-2023 Thomas Hooge

SPDX-License-Identifier: GPL-3.0-or-later
*****************************************************************************/

include("includes.php");

if (isset($_REQUEST['id'])) {
    $id = (int) $_REQUEST['id'] or $id = 0;
}

// ========== ADDITIONAL ACTION DEFINITIONS ===================================

define ('ACT_LOCATION_EDIT', 100);
define ('ACT_LOCATION_ADD', 101);
define ('ACT_LOCATION_DEL', 102);

define ('ACT_VLAN_EDIT', 103);
define ('ACT_VLAN_ADD', 104);
define ('ACT_VLAN_DEL', 105);

// ========== ACTIONS START ===================================================
switch ($submit = form_get_action()) {

    case NULL: break;

    case 'add':   $action = ACT_ADD; break;
    case 'view':  $action = ACT_VIEW; break;
    case 'edit':  $action = ACT_EDIT; break;
    case 'del':   $action = ACT_DELETE; break;
    case 'link':  $action = ACT_LINK; break;

    // Location
    case 'ledit': $action = ACT_LOCATION_EDIT; break;
    case 'ladd':  $action = ACT_LOCATION_ADD; break;
    case 'ldel':  $action = ACT_LOCATION_DEL; break;

    // VLAN
    case 'vedit': $action = ACT_VLAN_EDIT; break;
    case 'vadd':  $action = ACT_VLAN_ADD; break;
    case 'vdel':  $action = ACT_VLAN_DEL; break;

    case 'exec-ledit':
        if ($_POST['action'] == 'subnetlocationadd') {
            $action = ACT_LOCATION_ADD;
        } elseif ($_POST['action'] == 'subnetlocationdel') {
            $action = ACT_LOCATION_DEL;
        } else {
            $g_warning->Add('Invalid action: '. $_POST['action']);
        }
        break;

    case 'exec-ladd':
        $location_id = sanitize($_POST['location_id']);
        $sql = "INSERT INTO subnetlocation (location_id, subnet_id) VALUES (?, ?)";
        $sth = $dbh->prepare($sql);
        $sth->execute([$location_id, $id]);
        $action = ACT_VIEW;
        break;

    case 'exec-ldel':
        $location_id = sanitize($_POST['location_id']);
        $sth = $dbh->prepare("DELETE FROM subnetlocation WHERE location_id=? AND subnet_id=?");
        $sth->execute([$location_id, $id]);
        $g_message->Add('Removed link to location');
        $action = ACT_VIEW;
        break;

    case 'exec-vedit':
        if ($_POST['action'] == 'subnetvlanadd') {
            $action = ACT_VLAN_ADD;
        } elseif ($_POST['action'] == 'subnetvlandel') {
            $action = ACT_VLAN_DEL;
        } else {
            $g_warning->Add('Invalid action: '. $_POST['action']);
        }
        break;

    case 'exec-vadd':
        $vlan_id = sanitize($_POST['vlan_id']);
        $sql = "INSERT INTO subnetvlan (subnet_id, vlan_id) VALUES (?, ?)";
        $sth = $dbh->prepare($sql);
        $sth->execute([$id, $vlan_id]);
        $action = ACT_VIEW;
        break;

    case 'exec-vdel':
        $vlan_id = sanitize($_POST['vlan_id']);
        $sth = $dbh->prepare("DELETE FROM subnetvlan WHERE subnet_id=? AND vlan_id=?");
        $sth->execute([$id, $vlan_id]);
        $g_message->Add('Removed link to vlan');
        $action = ACT_VIEW;
        break;        

    case 'insert':
        $address= sanitize($_POST['subnet_address']);
        $mask = sanitize($_POST['subnet_mask']);
        $info = sanitize($_POST['subnet_info']);
        $dhcp_start = sanitize($_POST['dhcp_start']);
        $dhcp_end = sanitize($_POST['dhcp_end']);
        $ntp_server = sanitize($_POST['ntp_server']);
        $sql = "INSERT INTO subnet (
                    subnet_address, subnet_mask, subnet_info,
                    subnet_dhcp_start, subnet_dhcp_end, ntp_server
                ) VALUES (
                    :address, :mask, :info,
                    :dhcp_start, :dhcp_end, :ntp_server
                )";
        $sth = $dbh->prepare($sql);
        $sth->bindValue(':address', $address, PDO::PARAM_STR);
        $sth->bindValue(':mask', $mask, PDO::PARAM_INT);
        $sth->bindValue(':dhcp_start', $dhcp_start, PDO::PARAM_STR);
        $sth->bindValue(':dhcp_end', $dhcp_end, PDO::PARAM_STR);
        $sth->bindValue(':ntp_server', $ntp_server, PDO::PARAM_STR);
        $sth->bindValue(':info', $info, PDO::PARAM_STR);
        $sth->execute();
        $id = $dbh->lastInsertId();
        // vlan if selected
        $vlan_id = intval(sanitize($_POST['vlan_id']));
        if ($vlan_id > 0) {
            $sql = "INSERT INTO subnetvlan (subnet_id, vlan_id) VALUES (?, ?)";
            $sth = $dbh->prepare($sql);
            $sth->execute([$id, $vlan_id]);
        }
        $action = ACT_VIEW;
        break;

    case 'update':
        $address= sanitize($_POST['subnet_address']);
        $proto_vers = sanitize($_POST['subnet_proto_vers']);
        $mask = sanitize($_POST['subnet_mask']);
        $dhcp_start = sanitize($_POST['dhcp_start']);
        $dhcp_end = sanitize($_POST['dhcp_end']);
        $ntp_server = sanitize($_POST['ntp_server']);
        $info = sanitize($_POST['subnet_info']);

        $sql = "UPDATE subnet SET
                    subnet_address=?, subnet_mask=?, subnet_dhcp_start=?,
                    subnet_dhcp_end=?, subnet_info=?, protocol_version=?,
                    ntp_server=?
                WHERE subnet_id=?";
        $sth = $dbh->prepare($sql);
        $sth->execute([$address, $mask, $dhcp_start,
                       $dhcp_end, $info, $proto_vers,
                       $ntp_server, $id]);
        $action = ACT_VIEW;
        break;

    case 'delete':
        // TODO delete NAT 
        $sth = $dbh->prepare("DELETE FROM node WHERE subnet_id=?");
        $sth->execute([$id]);
        $count = $sth->rowCount();
        $g_message->Add("Deleted $count nodes");

        $sth = $dbh->prepare("DELETE FROM subnetlocation WHERE subnet_id=?");
        $sth->execute([$id]);
        $count = $sth->rowCount();
        $g_message->Add("Deleted $count location links");

        $sth = $dbh->prepare("DELETE FROM subnetvlan WHERE subnet_id=?");
        $sth->execute([$id]);
        $count = $sth->rowCount();
        $g_message->Add("Deleted $count vlan links");

        $sth = $dbh->prepare("DELETE FROM subnet WHERE subnet_id=?");
        $sth->execute([$id]);
        $g_message->Add("Deleted subnet");

        $action = ACT_DEFAULT;
        break;

    default:
        $g_error->Add(submit_error($submit));
        $valid = FALSE;
}
$smarty->assign("action", $action);

// ========== ACTIONS END =====================================================

$smarty->assign("scripts",'changetext.js');
include("header.php");

if ($action == ACT_DEFAULT):
// ========== VARIANT: default behavior =======================================

$sql = "SELECT s.subnet_id, s.subnet_address, s.subnet_mask,
            s.ntp_server, 
            CONCAT(LEFT(s.subnet_info, 50), IF(CHAR_LENGTH(s.subnet_info)>50,'...','')) AS subnet_info,
            COUNT(node.subnet_id) AS node_counter
        FROM subnet AS s LEFT JOIN node USING (subnet_id)
        GROUP BY s.subnet_id
        ORDER BY INET_ATON(s.subnet_address)";
$sth = $dbh->query($sql);

$smarty->assign("subnets", $sth->fetchAll());

$smarty->display("subnet.tpl");

elseif ($action == ACT_ADD):
// ========== VARIANT: add record =============================================

if((isset($_GET['vlan_id'])) ? $vlan_id = sanitize($_GET['vlan_id']) : $vlan_id = "");
$smarty->assign("vlan_id", $vlan_id);
$smarty->assign("vlan_options", db_get_options_vlan($lang['lang_option_none']));

$smarty->display("subnetedit.tpl");

elseif ($action == ACT_VIEW):
// ========== VARIANT: view single record =====================================

if(isset($_GET['page'])) {
    $page = sanitize($_GET['page']);
}

// subnet
$sql = "SELECT s.subnet_id AS id, s.subnet_address AS address, s.subnet_mask AS mask,
            s.subnet_dhcp_start AS dhcp_start, s.subnet_dhcp_end AS dhcp_end,
            s.subnet_info AS info, s.protocol_version AS proto_vers,
            s.ntp_server,
            COUNT(n.subnet_id) AS node_counter
        FROM subnet AS s LEFT JOIN node AS n USING (subnet_id)
        WHERE s.subnet_id=? 
          AND ((n.node_flags IS NULL) OR (n.node_flags & 0x1 = 0))
        GROUP BY s.subnet_id";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);

$subnet = $sth->fetch(PDO::FETCH_OBJ);

$smarty->assign("subnet", $subnet);

// set counters
$host_counter = pow(2, (32-$subnet->mask));
$node_counter = $subnet->node_counter;
$subnet_usedpercentage = round((($node_counter/($host_counter-2))*100), 1);

$smarty->assign("node_counter", $node_counter);
$smarty->assign("subnet_usedpercentage", $subnet_usedpercentage);
$smarty->assign("config_color_unused", $config_color_unused);
$smarty->assign("host_counter", $host_counter-2);
$smarty->assign("free_counter", (($host_counter-2)-$node_counter));

// subnet

// split up the range
$iprange = explode('.', $subnet->address);
$iprange1 = $iprange[0];
$iprange2 = $iprange[1];
$iprange3 = $iprange[2];
$iprange4 = $iprange[3];

// create empty subnet-array
$subnetdata = array();

// determine range (Class A/B/C)
if ($subnet->mask >= 24) {
    // Class C
    // fill subnet-array with addresses we want to see
    for($i=0; $i<$host_counter; $i++) {
        // build ip
        $ip = $iprange1 . '.' . $iprange2 . '.' . $iprange3 . '.' . ($iprange4+$i);

        // fill subnet-array
        $subnetdata[$ip] = array();
    }

    // calculate broadcast address
    $broadcast_address = $iprange1 . '.' . $iprange2 . '.' . $iprange3 .  '.' . ($iprange4+$i-1);

    // to tpl
    $smarty->assign("iprange1", $iprange1);
    $smarty->assign("iprange2", $iprange2);
    $smarty->assign("iprange3", $iprange3);
    $smarty->assign("iprange4", $iprange4);
    $smarty->assign("subnetmask1", 255);
    $smarty->assign("subnetmask2", 255);
    $smarty->assign("subnetmask3", 255);
    $smarty->assign("subnetmask4", 256-$host_counter);

    // no pagination needed
    $smarty->assign("noselect", TRUE);
    $smarty->assign("one_select", FALSE);
    $smarty->assign("two_select", FALSE);

    // set displayed nodes
    $nodes_displayed = $host_counter;

} else if ($subnet->mask >= 16) {
    // Class B
    // which part do we want to see?
    if ((empty($page)) ? $page = $subnet->address : $page = $page);
    $page = explode('.', $page);
    $page2 = $page[2];

    // fill subnet-array with addresses we want to see
    for($i=0; $i<256; $i++) {
        // build ip
        $ip = $iprange1 . '.' . $iprange2 . '.' . $page2 .  '.' . $i;

        // fill subnet-array
        $subnetdata[$ip] = array();
    }

    // calculate broadcast address
    $broadcast_address = $iprange1 . '.' . $iprange2 . '.' . ($iprange3+$i-1) .  '.255';

    // to tpl
    $smarty->assign("iprange1", $iprange1);
    $smarty->assign("iprange2", $iprange2);

    // loop addresses in range3
    for ($i=$iprange3; $i<(pow(2,(32-$subnet->mask))/256); $i++) {
        // send to tpl
        $smarty->assign("iprange3", $i);
        $smarty->assign("iprange4", 0);

        // set select box
        if ($i == $page2) {
            $smarty->assign("row_selected", "selected");

        } else {
            $smarty->assign("row_selected", "");
        }

    }

    $smarty->assign("subnetmask1", 255);
    $smarty->assign("subnetmask2", 255);
    $smarty->assign("subnetmask3", 256-($host_counter/256));
    $smarty->assign("subnetmask4", 0);

    // one select box
    $smarty->assign("noselect", FALSE);
    $smarty->assign("one_select", TRUE);
    $smarty->assign("two_select", FALSE);

    // set displayed nodes
    $nodes_displayed = 256;
} else {
    // Class A
    // which part do we want to see?
    if ((empty($page)) ? $page = $subnet->address : $page = $page);
    $page = explode('.', $page);
    $page2 = $page[1];
    $page3 = $page[2];

    // fill subnet-array with addresses we want to see
    for($i=0; $i<256; $i++) {
        // build ip
        $ip = $iprange1 . '.' . $page2 . '.' . $page3 .  '.' . $i;

        // fill subnet-array
        $subnetdata[$ip] = array();
    }

    // calculate broadcast address
    $broadcast_address = $iprange1 . '.' . ($iprange2+$i-1) . '.255.255';

    // to tpl
    $smarty->assign("iprange1", $iprange1);
    $smarty->assign("iprange2", $iprange2);

    // loop addresses in range 2
    for ($i=$iprange2; $i<(pow(2,(24-$subnet->mask))/256); $i++) {
        // send to tpl
        $smarty->assign("iprange1", $iprange1);
        $smarty->assign("iprange2", $i);
        $smarty->assign("iprange3", $page3);
        $smarty->assign("iprange4", $iprange4);

        // set select box
        if($i == $page2) {
            $smarty->assign("row1_selected", "selected");

        } else {
            $smarty->assign("row1_selected", "");
        }

    }

    // loop addresses in range 3
    for ($i=0; $i<256; $i++) {
        // send to tpl
        $smarty->assign("iprange1", $iprange1);
        $smarty->assign("iprange2", $page2);
        $smarty->assign("iprange3", $i);
        $smarty->assign("iprange4", $iprange4);

        // set select box
        if($i==$page3) {
            $smarty->assign("row2_selected", "selected");

        } else {
            $smarty->assign("row2_selected", "");
        }

    }

    $smarty->assign("subnetmask1", 255);
    $smarty->assign("subnetmask2", 256-($host_counter/65536));
    $smarty->assign("subnetmask3", 0);
    $smarty->assign("subnetmask4", 0);

    // one select box
    $smarty->assign("noselect", FALSE);
    $smarty->assign("one_select", FALSE);
    $smarty->assign("two_select", TRUE);

    // set displayed nodes
    $nodes_displayed = 256;
}

// get nodes for this subnetview and implement the values into the array

// TODO this is very bad SQL
/*$sql = "SELECT a.asset_name, g.assetclassgroup_color, n.node_id, n.node_ip
        FROM
            asset AS a,
            assetclass AS c,
            assetclassgroup AS g,
            node AS n
    WHERE
        n.node_ip IN ('".implode("','",array_keys($subnetdata))."')
        AND n.subnet_id=?
        AND a.asset_id=n.asset_id
        AND c.assetclass_id=a.assetclass_id
        AND g.assetclassgroup_id=c.assetclassgroup_id"; */
$sql = "SELECT
            a.asset_name, g.assetclassgroup_color, n.node_id, n.node_ip
        FROM node AS n
            LEFT JOIN asset AS a USING (asset_id)
            LEFT JOIN assetclass AS c USING (assetclass_id)
            LEFT JOIN assetclassgroup AS g USING (assetclassgroup_id)
        WHERE n.subnet_id=:subnet_id
          AND ((n.node_flags IS NULL) OR (n.node_flags & 0x1 = 0))
          AND INET_ATON(n.node_ip) BETWEEN :ipfrom AND :ipto";
// Debug $smarty->assign("sql",array_key_first($subnetdata) .  " - " . array_key_last($subnetdata) );
$sth = $dbh->prepare($sql);
$sth->bindValue(':ipfrom', ip2long(array_key_first($subnetdata)), PDO::PARAM_INT);
$sth->bindValue(':ipto', ip2long(array_key_last($subnetdata)), PDO::PARAM_INT);
$sth->bindValue(':subnet_id', $id, PDO::PARAM_INT);
$sth->execute();

$nodes = $sth->fetchAll();
$smarty->assign("nodes", $nodes);

if (count($nodes) > 0) {
    foreach ($nodes AS $node) {
        $subnetdata[$node['node_ip']] = $node;
    }
}

// replace ip's in subnet-array (if necessary)
// check for subnet address
if (array_key_exists($subnet->address, $subnetdata)) {
    // replace
    $subnetdata[$subnet->address] = array("subnet_address");
}

// check for broadcast address
if (array_key_exists($broadcast_address, $subnetdata)) {
    // replace
    $subnetdata[$broadcast_address] = array("broadcast_address");
}

$dhcpstart = 0;
if ($subnet->dhcp_start && $subnet->dhcp_end) {
    $dhcpstart = ip2long($subnet->dhcp_start);
    $dhcpend = ip2long($subnet->dhcp_end);
}

        // loop subnet-array and send to template
            // start counter
//            $i=1;
            // loop subnet-array
foreach ($subnetdata AS $node_ip => $node) {

// make new line?
//   if(($i%$_SESSION['suser_imagecount']==0 && $i!=$nodes_displayed) ? $tr="</tr><tr>" : $tr="");

    // check if node-ip in DHCP-area
    $subnetdata[$node_ip]["dynamic"] = false;
    if ($dhcpstart > 0) {
        $ipval = ip2long($node_ip);
        if (($ipval >= $dhcpstart) and ($ipval <= $dhcpend)) {
            $subnetdata[$node_ip]["dynamic"] = true;
        }
    }

    // check node
    if (empty($node)) {
        // empty node to tpl
        $subnetdata[$node_ip]["url"] = 'subnet.php?f=link&id=' . $id . '&amp;node_ip='. $node_ip;
        $subnetdata[$node_ip]["remotetext"] = $node_ip;
        if ($subnetdata[$node_ip]["dynamic"]) {
            $subnetdata[$node_ip]["assetclassgroup_color"] = $config_color_dynamic;
        } else {
            $subnetdata[$node_ip]["assetclassgroup_color"] = $config_color_unused;
        }
    } else if (array_key_exists(0, $node) && $node[0]=="subnet_address") {
       // subnet address to tpl
        $subnetdata[$node_ip]["url"] = "";
        $subnetdata[$node_ip]["remotetext"] = $node_ip . '&nbsp;' . $lang['lang_subnet_subnetaddress'];
        $subnetdata[$node_ip]["assetclassgroup_color"] = $config_color_blocked;
    } else if (array_key_exists(0, $node) && $node[0]=="broadcast_address") {
        // broadcast address to tpl
        $subnetdata[$node_ip]["url"] = "";
        $subnetdata[$node_ip]["remotetext"] = $node_ip . '&nbsp;' . $lang['lang_subnet_broadcastaddress'];
        $subnetdata[$node_ip]["assetclassgroup_color"] = $config_color_blocked;
    } else {
        // node to tpl
        $subnetdata[$node_ip]["url"] = 'node.php?f=view&id=' . $node['node_id'];
        $subnetdata[$node_ip]["remotetext"] = $node_ip . '&nbsp;' . $node['asset_name'];
        $subnetdata[$node_ip]["assetclassgroup_color"] = $node['assetclassgroup_color'];
    }

    // update counter
    //   $i++;

} // foreach

$smarty->assign("subnetdata", $subnetdata);
$smarty->assign("imagewrap", $_SESSION['suser_imagecount']);

// vlans
$sql = "SELECT v.vlan_id AS id, v.vlan_name AS name,
              v.vlan_number AS number
        FROM subnetvlan AS s JOIN vlan AS v USING (vlan_id)
        WHERE s.subnet_id=?
        ORDER BY v.vlan_name";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("vlans", $sth->fetchAll());

// locations
$sql = "SELECT l.location_id, l.location_name
        FROM location AS l LEFT JOIN subnetlocation AS s USING (location_id)
        WHERE s.subnet_id=?
        ORDER BY l.location_name";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("locations", $sth->fetchAll());

// assetclasses with color from assetclassgroup
$sql = "SELECT assetclass_id AS id, assetclass_name AS name,
            assetclassgroup_color AS color, COUNT(node_id) AS counter
        FROM node LEFT JOIN asset USING (asset_id) 
                  LEFT JOIN assetclass USING (assetclass_id)
                  LEFT JOIN assetclassgroup USING (assetclassgroup_id)
        WHERE subnet_id=?
          AND ((node.node_flags IS NULL) OR (node.node_flags & 0x1 = 0))
        GROUP BY assetclass_id
        ORDER BY assetclass_name";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("assetclasses", $sth->fetchAll());

$smarty->display("subnetview.tpl");

elseif ($action == ACT_EDIT):
// ========== VARIANT: edit single record =====================================

$sql = "SELECT subnet_id AS id, subnet_address AS address, subnet_mask AS mask,
            protocol_version AS proto_vers, subnet_dhcp_start AS dhcp_start,
            subnet_dhcp_end AS dhcp_end, ntp_server, subnet_info AS info
        FROM subnet
        WHERE subnet_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("subnet", $sth->fetch(PDO::FETCH_OBJ));

$smarty->display("subnetedit.tpl");

elseif ($action == ACT_DELETE):
// ========== VARIANT: delete record ==========================================

// subnet
$sql = "SELECT subnet_id AS id, subnet_address AS address, subnet_mask AS mask
        FROM subnet
        WHERE subnet_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("subnet", $sth->fetch(PDO::FETCH_OBJ));

// node		
$sql = "SELECT node_id AS id, node_ip AS ip
        FROM node
        WHERE subnet_id=?
        ORDER BY INET_ATON(node_ip)";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("nodes", $sth->fetchAll());

$smarty->display("subnetdel.tpl");

elseif ($action == ACT_LINK):
// ========== VARIANT: link IP to node ========================================

// assigniptonode
$node_ip = sanitize($_GET['node_ip']);

$sql = "SELECT subnet_id AS id, subnet_address AS address, subnet_mask AS mask 
        FROM subnet
        WHERE subnet_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);

$smarty->assign("subnet", $sth->fetch(PDO::FETCH_OBJ));
$smarty->assign("node_ip", $node_ip);

$smarty->display("assigniptonode.tpl");

elseif ($action == ACT_LOCATION_EDIT):
// ========== VARIANT: subnet to location =====================================

$sql = "SELECT subnet_id AS id, subnet_address AS address, subnet_mask AS mask
        FROM subnet
        WHERE subnet_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);

$smarty->assign("subnet", $sth->fetch(PDO::FETCH_OBJ));

$smarty->display("subnetlocationedit.tpl");

elseif ($action == ACT_LOCATION_ADD):
// ========== VARIANT: subnet to location =====================================

$sql = "SELECT subnet_id AS id, subnet_address AS address, subnet_mask AS mask
        FROM subnet
        WHERE subnet_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("subnet", $sth->fetch(PDO::FETCH_OBJ));
	
$smarty->assign("location_options", db_get_options_location());

$smarty->display("subnetlocationadd.tpl");

elseif ($action == ACT_LOCATION_DEL):
// ========== VARIANT: subnet to location =====================================

// subnet
$sql = "SELECT subnet_id AS id, subnet_address AS address, subnet_mask AS mask
        FROM subnet
        WHERE subnet_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("subnet", $sth->fetch(PDO::FETCH_OBJ));

// locations for subnet
$sql = "SELECT l.location_id, l.location_name
        FROM subnetlocation AS s LEFT JOIN location AS l USING (location_id)
        WHERE s.subnet_id=?
        ORDER BY l.location_name";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$records = $sth->fetchAll();

$locations = array();
foreach ($records as $rec) {
    $locations[$rec['location_id']] = $rec['location_name'];
}
$smarty->assign("location_options", $locations);

$smarty->display("subnetlocationdel.tpl");

elseif ($action == ACT_VLAN_EDIT):
// ========== VARIANT: subnet to vlan =========================================

$sql = "SELECT subnet_id AS id, subnet_address AS address, subnet_mask AS mask
        FROM subnet
        WHERE subnet_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("subnet", $sth->fetch(PDO::FETCH_OBJ));

$smarty->display("subnetvlanedit.tpl");

elseif ($action == ACT_VLAN_ADD):
// ========== VARIANT: subnet to vlan =========================================

$sql = "SELECT subnet_id AS id, subnet_address AS address, subnet_mask AS mask
        FROM subnet
        WHERE subnet_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);

$smarty->assign("subnet", $sth->fetch(PDO::FETCH_OBJ));

// vlan
$sql = "SELECT vlan_id, vlan_number, vlan_name
        FROM vlan
        WHERE vlan_id NOT IN (
	    SELECT vlan_id FROM subnetvlan WHERE subnet_id=?
	)
        ORDER BY vlan_number";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
		
$vlans = $sth->fetchAll();
foreach ($vlans as $vlan) {
    $vlan_options[$vlan['vlan_id']] =  $vlan['vlan_name'] . ' (' . $vlan['vlan_number']. ')';
}
$smarty->assign("vlan_options", $vlan_options);

$smarty->display("subnetvlanadd.tpl");

elseif ($action == ACT_VLAN_DEL):
// ========== VARIANT: subnet to vlan =========================================

// subnet
$sql = "SELECT subnet_id AS id, subnet_address AS address, subnet_mask AS mask
        FROM subnet
        WHERE subnet_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("subnet", $sth->fetch(PDO::FETCH_OBJ));

// vlan
$sql = "SELECT v.vlan_id, v.vlan_number, v.vlan_name
        FROM subnetvlan AS s LEFT JOIN vlan AS v USING (vlan_id)
        WHERE s.subnet_id=?
        ORDER BY v.vlan_number";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$vlans = $sth->fetchAll();
foreach ($vlans as $vlan) {
    $vlan_options[$vlan['vlan_id']] =  $vlan['vlan_name'] . ' (' . $vlan['vlan_number']. ')';
}
$smarty->assign("vlan_options", $vlan_options);

$smarty->display("subnetvlandel.tpl");

else:
// ========== ERROR UNKNOWN VARIANT ===========================================

echo "<p>Unknown function call: Please report to system development!</p>\n";

endif; // $action == ...
// ========== END OF VARIANTS =================================================

$smarty->display('footer.tpl');