ipreg/location.php

<?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;
}

// look for parents
// function to look for parents and create a new array for every child
function location($parents, $parent = 0) {
	// loop array to check
	foreach($parents[$parent] as $child) {
		if(isset($parents[$child])) {
			// element has children
			$children[$child] = location($parents, $child);
		} else {
			// no children, set NULL
			$children[$child] = NULL;
		}
	}
	
	// and again...
	return $children;
}

// recursive children check to template
function checkchildren($locations, $level) {
	global $location_options;
	global $location_names;
	global $location_parent;

	foreach ($locations as $parent=>$child) {
		$row = str_repeat("-&nbsp;&nbsp;", $level) . $location_names[$parent];
		$location_options[$parent] = $row;
		if (isset($child)) {
			checkchildren($child, $level+1);
		}
	}
}

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

define ('ACT_SUBNET_EDIT', 100);
define ('ACT_SUBNET_ADD', 101);
define ('ACT_SUBNET_DEL', 102);

// ========== 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_SUBNET_EDIT; break;

    case 'exec-link':
        if ($_POST['action'] == 'locationsubnetadd') {
            $action = ACT_SUBNET_ADD;
        } elseif ($_POST['action'] == 'locationsubnetdel') {
            $action = ACT_SUBNET_DEL;
        } else {
            $g_warning->Add('invalid action!'. $_POST['action']);
        }
        break;

    case 'insert':
        $name = sanitize($_POST['location_name']);
        $parent = sanitize($_POST['location_parent']);
        $info = sanitize($_POST['location_info']);

        $sql = "INSERT INTO location (
                    location_name, location_parent, location_info
                )
                VALUE (?, ?, ?)";
        $sth = $dbh->prepare($sql);
        $sth->execute([$name, $parent, $info]);

        $id = $dbh->lastInsertId();
        $action = ACT_VIEW;
        break;

    case 'update':
        $location_name = sanitize($_POST['location_name']);
        $location_info = sanitize($_POST['location_info']);
        $parentlocation_id = sanitize($_POST['parentlocation_id']);
        $sql = "UPDATE location SET
                    location_name=?, location_parent=?, location_info=?
                WHERE location_id=?";
        $sth = $dbh->prepare($sql);
        $sth->execute([$location_name, $parentlocation_id, $location_info, $id]);
        $action = ACT_VIEW;
        break;

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

    case 'subnetunlink':
        $subnet_id = sanitize($_POST['subnet_id']);
        $sth = $dbh->prepare("DELETE FROM subnetlocation WHERE location_id=? AND subnet_id=?");
        $sth->execute([$id, $subnet_id]);
        $g_message->Add('Link removed');
        $action = ACT_VIEW;
        break;

    case 'delete':
        $sth = $dbh->prepare("DELETE FROM subnetlocation WHERE location_id=?");
        $sth->execute([$id]);
        $n = $sth->rowCount();
        if ($n > 0) {
            $g_message->Add("$n Subnetzzuordnungen wurden entfernt.");
        }
        $sth = $dbh->prepare("DELETE FROM location WHERE location_id=?");
        $g_message->Add("Standort wurde gelöscht.");
        $sth->execute([$id]);
        $action = ACT_DEFAULT;
        break;

    default:
        $g_error->Add(submit_error($submit));
        $valid = FALSE;
}

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

include("header.php");

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

$sql = "SELECT location_id AS id, location_name AS value, location_parent AS parent_id,
            CONCAT(LEFT(location_info,40), IF(CHAR_LENGTH(location_info)>40,'...','')) AS info
        FROM location
        ORDER BY location_parent, location_sort, location_name";
$sth = $dbh->query($sql);
$locations = $sth->fetchAll();
$smarty->assign('location_count', count($locations));

// function for recursion
function build_tree($parent_id, $level) {
    global $locations;
    $children = array();
    foreach ($locations as $key => $location) {
        if ($location['parent_id'] == $parent_id) {
            unset($location['parent_id']);
            $location['children'] = build_tree($location['id'], $level+1);
            $location['level'] = $level;
            $location['href'] = 'location.php?f=view&id=' . $location['id'];
            $children[] = $location;
        }
    }
    return $children;
}

$tree = build_tree(0, 0);
$smarty->assign("locations", $tree);

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

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

$sql = "SELECT location_id AS id, location_name AS name,
            location_parent AS parent, location_sort AS sort
        FROM location
        ORDER BY location_parent, location_sort, location_name";
$sth = $dbh->query($sql);
$locations = $sth->fetchAll();

if (count($locations) > 0) {
    foreach ($locations AS $location) {
        $location_names[$location['id']] = $location['name'];
        $parents[$location['parent']][] = $location['id'];
    }
}

$tree = location($parents);

// create tree option list
$location_options = array(0 => '-');
checkchildren($tree, 0);

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

$location_parent = sanitize($_GET['parent']);
$smarty->assign("location_parent", $location_parent);

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

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

// base location
$sql = "SELECT location_id AS id, location_name AS name,
            location_parent AS parent_id, location_info AS info,
            CONCAT('location.php?f=view&id=', location_id) AS url
	    FROM location
	    WHERE location_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$location = $sth->fetch(PDO::FETCH_OBJ);
$smarty->assign("location", $location);

// crumbs
$crumbs[] = $location;
$sql = "SELECT location_id AS id, location_name AS name,
            location_parent AS parent_id,
            CONCAT('location.php?f=view&id=', location_id) AS url
	    FROM location
	    WHERE location_id=?";
$sth = $dbh->prepare($sql);
while ($crumbs[0]->parent_id != 0) {
	$sth->execute([$crumbs[0]->parent_id]);
	$result = $sth->fetch(PDO::FETCH_OBJ);
	array_unshift($crumbs, $result);
}
$smarty->assign("crumbs", $crumbs);

// sublocations		
$sql = "SELECT location_id AS sublocation_id, location_name AS sublocation_name,
            LEFT(location_info, 40) AS info_short,
            CHAR_LENGTH(location_info) AS info_length
        FROM location
        WHERE location_parent=?
        ORDER BY location_name";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("sublocations", $sth->fetchAll());
	
// subnets
$sql = "SELECT s.subnet_id, s.subnet_address, s.subnet_mask
        FROM subnet AS s LEFT JOIN subnetlocation AS l USING (subnet_id)
	    WHERE l.location_id=?
	    ORDER BY INET_ATON(s.subnet_address)";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("subnets", $sth->fetchAll());

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

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

// TODO implement sorting with location_sort

// location			
$sql = "SELECT location_id AS id, location_name AS name, location_parent AS parent,
            location_info AS info, location_sort AS sort
        FROM location
        WHERE location_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$location = $sth->fetch(PDO::FETCH_OBJ);
		
$location_parent = $location->parent;

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

// parent location
$sql = "SELECT location_id, location_name, location_parent
        FROM location
        WHERE location_id != ?
        ORDER BY location_name";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
		
$locations = $sth->fetchAll();

$location_counter = count($locations);
			
$smarty->assign("location_counter", $location_counter);
			
// any locations?
if ($location_counter>0) {
	foreach($locations AS $location) {
		$location_names[$location['location_id']] = $location['location_name'];
		$parents[$location['location_parent']][] = $location['location_id'];
	}
}
			
$tree = location($parents);
$location_options = array(0 => '-');
checkchildren($tree, 0);
$smarty->assign("location_options", $location_options);
$smarty->assign("location_parent", $location_parent);

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

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

$sql = "SELECT location_id AS id, location_name AS name
        FROM location
        WHERE location_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("location", $sth->fetch(PDO::FETCH_OBJ));

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

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

$sql = "SELECT location_id AS id, location_name AS name
        FROM location
        WHERE location_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("location", $sth->fetch(PDO::FETCH_OBJ));

// TODO Filter für bereits zugeordnete Subnetze
$smarty->assign("subnet_options", db_get_options_subnet());

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

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

// location
$sql = "SELECT location_id AS id, location_name AS name
        FROM location
        WHERE location_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("location", $sth->fetch(PDO::FETCH_OBJ));
	
// subnet
$sql = "SELECT s.subnet_id, CONCAT_WS('/', s.subnet_address, s.subnet_mask)
        FROM subnetlocation AS l LEFT JOIN subnet AS s USING (subnet_id)
        WHERE l.location_id=?
        ORDER BY INET_ATON(s.subnet_address)";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$options = array();
foreach ($sth->fetchAll(PDO::FETCH_NUM) as $rec) {
    $options[$rec[0]] = $rec[1];
}
$smarty->assign("subnet_options", $options);

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

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

$sql = "SELECT location_id AS id, location_name AS name FROM location WHERE location_id=?";
$sth = $dbh->prepare($sql);
$sth->execute([$id]);
$smarty->assign("location", $sth->fetch(PDO::FETCH_OBJ));

$smarty->display("locationdel.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');