<?php
    #require('config.php');
    require('PasswordHash.php');

    $json = @file_get_contents('/etc/usm/config.json');
    if (!$json) {
        # use local config for development
        $json = file_get_contents(realpath(dirname(__FILE__) . '/../../../etc/config.json'));
    }
    $config = json_decode($json, true);
    $database = $config['database'];
    
    function connect($config) {
        $dsn = "{$config['driver']}:host={$config['host']};port={$config['port']};dbname={$config['database']};";
        $connection = new PDO($dsn, $config['user'], $config['password']);
        $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        return $connection;
    }

    function add_user($connection, $username, $password, $email) {
        $hasher = new PasswordHash(8, FALSE);
        $hash = $hasher->HashPassword($password);
        unset($hasher);

        $min = hexdec('10000000');
        $max = hexdec('7fffffff');      // i.e. mt_getrandmax();
        $validation = mt_rand($min, $max);
        $validation = dechex($validation);

        $query = 'INSERT INTO tblUsers (LOGIN, PASS, VALCODE, EMAIL, PERMISSIONS)
                  VALUES (:user, :hash, :valcode, :email, 1)';
        $query = $connection->prepare($query);
        $query->bindParam(':user', $username);
        $query->bindParam(':hash', $hash);
        $query->bindParam(':valcode', $validation);
        $query->bindParam(':email', $email);

        $caught = False;
        try {
            $query->execute();
        } catch (PDOException $e) {
            $caught = True;
            if ($e->errorInfo[1] == 1062) {
                echo '<strong class="warning center block">Account already exists!</strong>';
            }
            return False;
        }

        if (!$caught) {
            $headers = "From:validation@{$_SERVER['HTTP_HOST']}\r\n";
            $validation_url = "http://{$_SERVER['HTTP_HOST']}/newUserValidate.php?user=$username&validation=$validation";

            $title = "USM Account Validation!";
            $body = "Please click the following link to verify your USM account.\r\n\r\n$validation_url";

            return mail($email, $title, $body, $headers);
        }
	}
    
    function email_reset($connection, $username) {
        $min = hexdec('10000000');
        $max = hexdec('7fffffff');      // i.e. mt_getrandmax();
        $validation = mt_rand($min, $max);
        $validation = dechex($validation);
        
        $query = 'UPDATE tblUsers SET VALCODE = :valcode WHERE LOGIN = :user;';
        $query = $connection->prepare($query);
        $query->bindParam(':user', $username);
        $query->bindParam(':valcode', $validation);
        $query->execute();
       
        $query = 'SELECT EMAIL AS email FROM tblUsers WHERE LOGIN = :user;';
        $query = $connection->prepare($query);
        $query->bindParam(':user', $username);
        $query->execute();
        $result = $query->fetch();
        $email = $result['email'];
        
        $headers = "From:reset@{$_SERVER['HTTP_HOST']}\r\n";
        $reset_url = "http://{$_SERVER['HTTP_HOST']}/setPassword.php?user=$username&validation=$validation";

        $title = "USM Password Reset";
        $body = "Please click the following link to reset the password to your USM account.\r\n\r\n$reset_url";

        return mail($email, $title, $body, $headers);
    }

    function check_validation($connection, $username, $validation) {
        $query = "
            SELECT LOGIN FROM tblUsers
            WHERE LOGIN = :user AND VALCODE = :valcode AND VALCODE != '0';
        ";
        $query = $connection->prepare($query);
        $query->bindParam(':user', $username);
        $query->bindParam(':valcode', $validation);
        
        $query->execute();
        $result = $query->fetch();

        return $result ? True : False;
    }
    
    function validate_user($connection, $username, $validation) {
        if ( check_validation($connection, $username, $validation) ) {
            $query = "
                UPDATE tblUsers SET VALCODE = '0'
                WHERE LOGIN = :user AND VALCODE = :valcode;
            ";
            $query = $connection->prepare($query);
            $query->bindParam(':user', $username);
            $query->bindParam(':valcode', $validation);
        
            $query->execute();
            return True;
        } else {
            return False;
        }
    }
    
    function update_password($connection, $username, $password) {
        $hasher = new PasswordHash(8, FALSE);
        $hash = $hasher->HashPassword($password);
        unset($hasher);
    
        $query = 'UPDATE tblUsers SET PASS = :hash WHERE LOGIN = :user;';
        $query = $connection->prepare($query);
        $query->bindParam(':user', $username);
        $query->bindParam(':hash', $hash);
        return $query->execute();
    }

    function login($connection, $username, $password) {
        $query = 'SELECT PASS, VALCODE FROM tblUsers WHERE LOGIN = :user;';
        $query = $connection->prepare($query);
        $query->bindParam(':user', $username);
        
        $query->execute();
        $result = $query->fetch();

        $hash = $result['PASS'];
        $valcode = $result['VALCODE'];
        $is_validated = ($valcode === '0');
        
        $hasher = new PasswordHash(8, FALSE);
        if ($hasher->CheckPassword($password, $hash)) {
            unset($hasher);
            
            if ($is_validated) {
                session_regenerate_id(TRUE);
                
                /* erase data carried over from previous session */
                $_SESSION = array();
                $_SESSION['login'] = $username;
                
                header( 'Location: /' );
                return True;
            } else {
                echo '<strong class="warning center block">Please check your email for more instructions!</strong>';
                return False;
            }
        }

        if ( empty($_SESSION['login']) ) {
            echo '<strong class="warning center block">Incorrect username or password!</strong>';
            return False;
        }
    }

    function getPermissions($connection, $username) {
        $query = 'SELECT PERMISSIONS FROM tblUsers WHERE LOGIN = :user;';
        $query = $connection->prepare($query);
        $query->bindParam(':user', $username);

        $query->execute();
        $result = $query->fetch();
        $permissions = intval($result['PERMISSIONS']);
        return $permissions;
    }

    define('VIEW',    1<<0); // can view this thing
    define('RESET',   1<<1); // can reset this property
    define('EDIT',    1<<2); // can edit this col
    define('DELETE',  1<<3); // can delete this object
    define('LINKS',   1<<4); // TELNET and LINK show up as hyperlinks
    define('CONTROL', 1<<5); // can perform system control
    define('ADMIN',   1<<6); // administer the system

    function canViewTables($connection, $user) {
        return VIEW & getPermissions($connection, $user);
    }
    function canResetAlerts($connection, $user) {
        return RESET & getPermissions($connection, $user);
    }
    function canEditValues($connection, $user) {
        return EDIT & getPermissions($connection, $user);
    }
    function canDeleteValues($connection, $user) {
        return DELETE & getPermissions($connection, $user);
    }
     function canViewLinks($connection, $user) {
        return LINKS & getPermissions($connection, $user);
    }
    function canControlSystems($connection, $user) {
        return CONTROL & getPermissions($connection, $user);
    }
    function canAdministrate($connection, $user) {
        return ADMIN & getPermissions($connection, $user);
    }

    function getAlerts($connection) {
        $query = '
            SELECT tblCampus.ALTNAME AS campus,
                   tblBuilding.ALTNAME AS building,
                   tblRoom.ALTNAME AS room,
                   tblDevice.ALTNAME AS device,
                   tblProperties.ALTNAME AS property,
                   tblProperties.id AS id,
                   DATE_FORMAT(tblDevice.LASTUPDATED, \'%b %e, %Y %l:%i %p\') AS last_updated,
                   tblProperties.VALUE AS value,
                   tblProperties.PREFIX AS prefix,
                   tblProperties.SUFFIX AS suffix,
                   tblProperties.ALERTTYPE AS type,
                   tblAlertColors.ALERTCOLOR AS color
            FROM tblCampus
            INNER JOIN tblBuilding ON tblCampus.id = tblBuilding.idCampus
            INNER JOIN tblRoom ON tblBuilding.id = tblRoom.idBuilding
            INNER JOIN tblDevice ON tblRoom.id = tblDevice.idRoom
            INNER JOIN tblProperties ON tblDevice.id = tblProperties.idDevice
            LEFT OUTER JOIN tblAlertColors ON tblAlertColors.id = tblProperties.ALERTTYPE
            WHERE tblProperties.ALERTSTATUS != 0
            ORDER BY type DESC, campus, building, room, device, property;
        ';
        $alerts = $connection->query($query);
        return $alerts;
    }

    function getOfflineSystems($connection) {
		$query = '
            SELECT tblCampus.ALTNAME AS campus,
                   tblBuilding.ALTNAME AS building,
                   tblRoom.ALTNAME AS room
            FROM tblCampus
            INNER JOIN tblBuilding ON tblCampus.id = tblBuilding.idCampus
            INNER JOIN tblRoom ON tblBuilding.id = tblRoom.idBuilding
			WHERE tblRoom.ONLINE = 0
			ORDER BY campus, building, room;
        ';
        $offline = $connection->query($query);
        return $offline;
    }

    function resetAlert($connection, $property_id) {
        $query = 'UPDATE tblProperties SET ALERTSTATUS = 0 WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $property_id);
        return $query->execute();
    }
    
    function updatePropertyColumn($connection, $property_id, $column, $value) {
        // prevent sql injection by first checking if it's a valid column
        $query = "
            SELECT COLUMN_NAME FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA='usm' AND TABLE_NAME='tblProperties'
                AND COLUMN_NAME NOT IN ('id', 'idDevice')
                AND COLUMN_NAME = :column;
        ";
        $query = $connection->prepare($query);
        $query->bindParam(':column', $column);
        $query->execute();
        $column_exists = $query->fetch();

        if ($column_exists) {
            $query = "UPDATE tblProperties SET $column = :value WHERE id = :id;";
            $query = $connection->prepare($query);
            $query->bindParam(':id', $property_id);
            $query->bindParam(':value', $value);
            return $query->execute();
        }
    }
    
    function getRoomId($connection, $campus, $building, $room) {
        $query = '
            SELECT tblRoom.id AS id
            FROM tblRoom
            INNER JOIN tblBuilding ON tblBuilding.id = tblRoom.idBuilding
			INNER JOIN tblCampus ON tblCampus.id = tblBuilding.idCampus
			WHERE tblCampus.ALTNAME = :campus
                AND tblBuilding.ALTNAME = :building
                AND tblRoom.ALTNAME = :room;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':campus', $campus);
        $query->bindParam(':building', $building);
        $query->bindParam(':room', $room);
        $query->execute();
        $result = $query->fetch();
        return $result['id'];
    }
    
    function getRoomProperties($connection, $room_id) {
        $query = '
            SELECT tblDevice.id AS device_id,
                tblDevice.ALTNAME AS device,
                tblProperties.id AS id,
                tblProperties.NAME AS caption,
                tblProperties.ALTNAME AS name,
                tblProperties.PREFIX AS prefix,
                tblProperties.VALUE AS value,
                tblProperties.SUFFIX AS suffix,
                tblProperties.TYPE AS type,
                tblProperties.ALERTCONFIG AS alert_config,
                tblProperties.ALERTVALUE AS alert_value,
                tblProperties.ALERTTYPE AS alert_type,
                tblProperties.ALERTSTATUS AS alert_status,
				tblProperties.CAPITALIZE AS capitalize,
                tblProperties.ALLOWRESET AS allow_reset,
                tblAlertColors.ALERTCOLOR AS alert_color
            FROM tblDevice
            LEFT OUTER JOIN tblProperties
                ON tblDevice.id = tblProperties.idDevice
            LEFT OUTER JOIN tblAlertColors
                ON tblAlertColors.id = tblProperties.ALERTTYPE
            WHERE tblDevice.idRoom = :id
            ORDER BY tblDevice.ALTNAME, tblProperties.ALTNAME;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $room_id);
        $query->execute();
        return $query->fetchAll();
    }

    function updateDeviceAltname($connection, $device_id, $name) {
        $query = 'UPDATE tblDevice SET ALTNAME = :altname WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $device_id);
        $query->bindParam(':altname', $name);
        return $query->execute();
    }
    
    function getCommandButtons($connection, $room_id) {
        $query = '
            SELECT id, ALTGROUPNAME as group_name, ALTBTNNAME as name,
                ALTCOMMAND as command
            FROM tblButtons WHERE idRoom = :room_id
            ORDER BY group_name, name;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':room_id', $room_id);
        $query->execute();
        return $query->fetchAll();
    }
    
    function insertUpdateButton(
        $connection, $room_id, $group_name, $button_name, $command
    ) {
        $query = '
            INSERT INTO tblButtons (
                idRoom, GROUPNAME, ALTGROUPNAME, BTNNAME,
                ALTBTNNAME, COMMAND, ALTCOMMAND
            ) VALUES (
                :room_id, :group_name, :group_name, :button_name,
                :button_name, :command, :command
            ) ON DUPLICATE KEY UPDATE
                ALTCOMMAND = :command;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':room_id', $room_id);
        $query->bindParam(':group_name', $group_name);
        $query->bindParam(':button_name', $button_name);
        $query->bindParam(':command', $command);
        return $query->execute();
    }
    
    function deleteButton($connection, $id) {
        $query = 'DELETE FROM tblButtons WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $id);
        return $query->execute();
    }
    
    function getCommand($connection, $button_id) {
        $query = '
            SELECT tblCmdProcs.ALTNAME as processor,
                tblRoom.ALTARGS as argument,
                tblButtons.ALTCOMMAND as command
            FROM tblRoom
            LEFT JOIN tblCmdProcs on tblRoom.idCmdProcs = tblCmdProcs.id
            LEFT JOIN tblButtons on tblRoom.id = tblButtons.idRoom
            WHERE tblButtons.id = :button_id;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':button_id', $button_id);
        $query->execute();
        return $query->fetch();
    }
    
    function getCampusNames($connection) {
        $query = 'SELECT ALTNAME as name FROM tblCampus ORDER BY id;';
        $query = $connection->prepare($query);
        $query->execute();
        return $query->fetchAll();
    }
    
    function getCampusId($connection, $campus_name) {
        $query = 'SELECT id FROM tblCampus WHERE ALTNAME = :campus_name;';
        $query = $connection->prepare($query);
        $query->bindParam(':campus_name', $campus_name);
        $query->execute();
        $result = $query->fetch();
        return $result['id'];
    }
    
    function editCampus($connection, $campus_id, $new_name) {
        $query = 'SELECT * FROM tblCampus WHERE ALTNAME = :new_name;';
        $query = $connection->prepare($query);
        $query->bindParam(':new_name', $new_name);
        $query->execute();
        
        if (!$query->fetch()) {
            $query = '
                UPDATE tblCampus SET ALTNAME = :new_name WHERE id = :id;
            ';
            $query = $connection->prepare($query);
            $query->bindParam(':id', $campus_id);
            $query->bindParam(':new_name', $new_name);
            return $query->execute();
        } else {
            return False;
        }
    }
    
    function deleteCampus($connection, $campus_id) {
        $query = 'DELETE FROM tblCampus WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $campus_id);
        return $query->execute();
    }
    
    function getBuildingNames($connection, $campus_id) {
        $query = '
            SELECT ALTNAME as name FROM tblBuilding
            WHERE idCampus = :campus_id
            ORDER BY ALTNAME;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':campus_id', $campus_id);
        $query->execute();
        return $query->fetchAll();
    }
    
    function getBuildingId($connection, $campus_name, $building_name) {
        $query = '
            SELECT tblBuilding.id FROM tblBuilding
            INNER JOIN tblCampus ON tblCampus.id = tblBuilding.idCampus
            WHERE tblCampus.ALTNAME = :campus_name
                AND tblBuilding.ALTNAME = :building_name;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':campus_name', $campus_name);
        $query->bindParam(':building_name', $building_name);
        $query->execute();
        $result = $query->fetch(); 
        return $result['id'];
    }
    
    function editBuilding($connection, $building_id, $new_name) {
        $query = 'SELECT * FROM tblBuilding WHERE ALTNAME = :new_name;';
        $query = $connection->prepare($query);
        $query->bindParam(':new_name', $new_name);
        $query->execute();
        
        if (!$query->fetch()) {
            $query = '
                UPDATE tblBuilding SET ALTNAME = :new_name WHERE id = :id;
            ';
            $query = $connection->prepare($query);
            $query->bindParam(':id', $building_id);
            $query->bindParam(':new_name', $new_name);
            return $query->execute();
        } else {
            return False;
        }
    }
    
    function deleteBuilding($connection, $building_id) {
        $query = 'DELETE FROM tblBuilding WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $building_id);
        return $query->execute();
    }
    
    function getRoomNames($connection, $building_id) {
        $query = '
            SELECT ALTNAME as name FROM tblRoom
            WHERE idBuilding = :building_id
            ORDER BY ALTNAME;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':building_id', $building_id);
        $query->execute();
        return $query->fetchAll();
    }
    
    function editRoom($connection, $room_id, $new_name) {
        $query = 'SELECT * FROM tblRoom WHERE ALTNAME = :new_name;';
        $query = $connection->prepare($query);
        $query->bindParam(':new_name', $new_name);
        $query->execute();
        
        if (!$query->fetch()) {
            $query = '
                UPDATE tblRoom SET ALTNAME = :new_name WHERE id = :id;
            ';
            $query = $connection->prepare($query);
            $query->bindParam(':id', $room_id);
            $query->bindParam(':new_name', $new_name);
            return $query->execute();
        } else {
            return False;
        }
    }
    
    function deleteRoom($connection, $room_id) {
        $query = 'DELETE FROM tblRoom WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $room_id);
        return $query->execute();
    }
    
    function deleteDevice($connection, $device_id) {
        $query = 'DELETE FROM tblDevice WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $device_id);
        return $query->execute();
    }
    
    function deleteProperty($connection, $property_id) {
        $query = 'DELETE FROM tblProperties WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $property_id);
        return $query->execute();
    }
    
    function getUsers($connection) {
        $query = '
            SELECT id, LOGIN as name, PERMISSIONS as permissions
            FROM tblUsers ORDER BY LOGIN;
        ';
        $query = $connection->prepare($query);
        $query->execute();
        return $query->fetchAll();
    }
    
    function deleteUser($connection, $username) {
        $query = 'DELETE FROM tblUsers WHERE LOGIN = :user;';
        $query = $connection->prepare($query);
        $query->bindParam(':user', $username);
        return $query->execute();
    }
    
    function updatePermissions($connection, $username, $permissions) {
        $query = '
            UPDATE tblUsers SET PERMISSIONS = :permissions
            WHERE LOGIN = :user;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':user', $username);
        $query->bindParam(':permissions', $permissions);
        return $query->execute();
    }
    
    function getTasks($connection, $campus_id, $building_id, $room_id) {
        $query = "
            SELECT tblSchedule.id, TASKNAME as name,
                BTNGROUP as button_group, BTNNAME as button_name,
                IF(BYDAY=1,
                    CONCAT_WS(' ', 'Every',
                        CONCAT_WS(', ',
                            IF(EVENTDAYS & (b'1000000' >> 0), 'Sunday', NULL),
                            IF(EVENTDAYS & (b'1000000' >> 1), 'Monday', NULL),
                            IF(EVENTDAYS & (b'1000000' >> 2), 'Tuesday', NULL),
                            IF(EVENTDAYS & (b'1000000' >> 3), 'Wednesday', NULL),
                            IF(EVENTDAYS & (b'1000000' >> 4), 'Thursday', NULL),
                            IF(EVENTDAYS & (b'1000000' >> 5), 'Friday', NULL),
                            IF(EVENTDAYS & (b'1000000' >> 6), 'Saturday', NULL)
                        ),
                        ' ', 'at', ' ', DATE_FORMAT(EVENTTIME, '%l:%i %p')
                    ),
                    DATE_FORMAT(CONCAT(EVENTDATE, ' ', EVENTTIME), '%b %e, %Y %l:%i %p')
                ) as activated
            FROM tblSchedule
            WHERE idCampus = :campus_id
                AND idBuilding = :building_id
                AND idRoom = :room_id;
        ";
        $query = $connection->prepare($query);
        $query->bindParam(':campus_id', $campus_id);
        $query->bindParam(':building_id', $building_id);
        $query->bindParam(':room_id', $room_id);
        $query->execute();
        return $query->fetchAll();
    }
	
	function deleteTask($connection, $task_id) {
		$query = 'DELETE FROM tblSchedule WHERE id = :id;';
		$query = $connection->prepare($query);
		$query->bindParam(':id', $task_id);
		return $query->execute();
	}
	
	function insertUpdateTask(
        $connection, $campus_id, $building_id, $room_id, $name, $button_group,
        $button_name, $is_by_day, $event_date, $event_time, $event_days,
        $event_repeat=0
    ) {
		$query = '
            INSERT INTO tblSchedule (
                idCampus, idBuilding, idRoom, TASKNAME, BTNGROUP, BTNNAME,
                BYDAY, EVENTDATE, EVENTTIME, EVENTDAYS, EVENTREPEAT
            ) VALUES (
                :campus_id, :building_id, :room_id, :name, :button_group,
                :button_name, :is_by_day, :event_date, :event_time,
                :event_days, :event_repeat
            ) ON DUPLICATE KEY UPDATE
                idCampus = :campus_id, idBuilding = :building_id,
                idRoom = :room_id, BTNGROUP = :button_group,
                BTNNAME = :button_name, BYDAY = :is_by_day,
                EVENTDATE = :event_date, EVENTTIME = :event_time,
                EVENTDAYS = :event_days, EVENTREPEAT = :event_repeat;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':campus_id', $campus_id);
        $query->bindParam(':building_id', $building_id);
        $query->bindParam(':room_id', $room_id);
        $query->bindParam(':name', $name);
        $query->bindParam(':button_group', $button_group);
        $query->bindParam(':button_name', $button_name);
        $query->bindParam(':is_by_day', $is_by_day);
        $query->bindParam(':event_date', $event_date);
        $query->bindParam(':event_time', $event_time);
        $query->bindParam(':event_days', $event_days);
        $query->bindParam(':event_repeat', $event_repeat);
        return $query->execute();
	}
    
    function getCommandProcessors($connection) {
        $query = 'SELECT id, ALTNAME as name FROM tblCmdProcs;';
        $query = $connection->prepare($query);
        $query->execute();
        return $query->fetchAll();
    }
    
    function getCommandProcessorId($connection, $name) {
        $query = 'SELECT id FROM tblCmdProcs WHERE ALTNAME = :name;';
        $query = $connection->prepare($query);
        $query->bindParam(':name', $name);
        $query->execute();
        $result = $query->fetch();
        return $result['id'];
    }
    
    function insertCommandProcessor($connection, $name) {
        $query = '
            INSERT INTO tblCmdProcs (NAME, ALTNAME)
            VALUES (:name, :name);
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':name', $name);
        return $query->execute();
    }
    
    function deleteCommandProcessor($connection, $id) {
        $query = 'DELETE FROM tblCmdProcs WHERE id = :proc_id;';
        $query = $connection->prepare($query);
        $query->bindParam(':proc_id', $id);
        return $query->execute();
    }
    
    function editCommandProcessor($connection, $id, $name) {
        $query = 'UPDATE tblCmdProcs SET ALTNAME = :name WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $id);
        $query->bindParam(':name', $name);
        return $query->execute();
    }
    
    function updateRoomCommandProcessor($connection, $room_id, $proc_id) {
        $query = 'UPDATE tblRoom SET idCmdProcs = :proc_id WHERE id = :room_id;';
        $query = $connection->prepare($query);
        $query->bindParam(':room_id', $room_id);
        $query->bindParam(':proc_id', $proc_id);
        return $query->execute();
    }
    
    function getRoomExecutor($connection, $room_id) {
        $query = '
            SELECT tblCmdProcs.id as id,
                tblCmdProcs.ALTNAME as processor,
                tblRoom.ALTARGS AS arguments
            FROM tblRoom
            LEFT JOIN tblCmdProcs
                ON tblCmdProcs.id = tblRoom.idCmdProcs
            WHERE tblRoom.id = :id;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $room_id);
        $query->execute();
        return $query->fetch();
    }
    
    function updateRoomArguments($connection, $room_id, $arguments) {
        $query = 'UPDATE tblRoom SET ALTARGS = :arguments WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $room_id);
        $query->bindParam(':arguments', $arguments);
        return $query->execute();
    }
    
    
    /*************************/
    /* Codificator Functions */
    /*************************/
    function updatePropertyColumnCG($connection, $property_id, $column, $value) {
        // prevent sql injection by first checking if it's a valid column
        $query = "
            SELECT COLUMN_NAME FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA='usmCodificator' AND TABLE_NAME='tblProperties'
                AND COLUMN_NAME NOT IN ('id', 'idDevice')
                AND COLUMN_NAME = :column;
        ";
        $query = $connection->prepare($query);
        $query->bindParam(':column', $column);
        $query->execute();
        $column_exists = $query->fetch();

        if ($column_exists) {
            $query = "UPDATE usmCodificator.tblProperties SET $column = :value WHERE id = :id;";
            $query = $connection->prepare($query);
            $query->bindParam(':id', $property_id);
            $query->bindParam(':value', $value);
            return $query->execute();
        }
    }
    
    function getRoomIdCG($connection, $campus, $building, $room) {
        $query = '
            SELECT tblRoom.id AS id
            FROM usmCodificator.tblRoom
            INNER JOIN usmCodificator.tblBuilding ON tblBuilding.id = tblRoom.idBuilding
			INNER JOIN usmCodificator.tblCampus ON tblCampus.id = tblBuilding.idCampus
			WHERE tblCampus.ALTNAME = :campus
                AND tblBuilding.ALTNAME = :building
                AND tblRoom.ALTNAME = :room;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':campus', $campus);
        $query->bindParam(':building', $building);
        $query->bindParam(':room', $room);
        $query->execute();
        $result = $query->fetch();
        return $result['id'];
    }
    
    function getDevicePropertiesCG($connection, $device_id) {
        $query = '
            SELECT tblProperties.id AS id,
                tblProperties.NAME AS name,
                tblProperties.ALTNAME AS altname,
                tblProperties.VALUE AS value,
                tblProperties.TRACKER AS tracker,
                tblProperties.TYPE AS type,
                tblProperties.PREFIX AS prefix,
                tblProperties.SUFFIX AS suffix,
                tblProperties.ALERTCONFIG AS alert_config,
                tblProperties.ALERTVALUE AS alert_value,
                tblProperties.ALERTTYPE AS alert_type,
				tblProperties.CAPITALIZE AS capitalize,
                tblProperties.ALLOWRESET AS allow_reset
            FROM usmCodificator.tblProperties
            WHERE tblProperties.idDevice = :device_id
            ORDER BY tblProperties.NAME;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':device_id', $device_id);
        $query->execute();
        return $query->fetchAll();
    }

    function updateDeviceCaptionCG($connection, $device_id, $name) {
        $query = 'UPDATE usmCodificator.tblDevice SET NAME = :name WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $device_id);
        $query->bindParam(':name', $name);
        return $query->execute();
    }
    
    function getCampusNamesCG($connection) {
        $query = 'SELECT ALTNAME as name FROM usmCodificator.tblCampus ORDER BY id;';
        $query = $connection->prepare($query);
        $query->execute();
        return $query->fetchAll();
    }
    
    function getCampusIdCG($connection, $campus_name) {
        $query = 'SELECT id FROM usmCodificator.tblCampus WHERE ALTNAME = :campus_name;';
        $query = $connection->prepare($query);
        $query->bindParam(':campus_name', $campus_name);
        $query->execute();
        $result = $query->fetch();
        return $result['id'];
    }
    
    function editCampusCG($connection, $campus_id, $new_name) {
        $query = 'SELECT * FROM usmCodificator.tblCampus WHERE ALTNAME = :new_name;';
        $query = $connection->prepare($query);
        $query->bindParam(':new_name', $new_name);
        $query->execute();
        
        if (!$query->fetch()) {
            $query = '
                UPDATE usmCodificator.tblCampus SET ALTNAME = :new_name WHERE id = :id;
            ';
            $query = $connection->prepare($query);
            $query->bindParam(':id', $campus_id);
            $query->bindParam(':new_name', $new_name);
            return $query->execute();
        } else {
            return False;
        }
    }

    function insertCampusCG($connection, $name) {
        $query = '
            INSERT INTO usmCodificator.tblCampus (NAME, ALTNAME)
            VALUES (:name, :name);
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':name', $name);
        return $query->execute();
    }
    
    function deleteCampusCG($connection, $campus_id) {
        $query = 'DELETE FROM usmCodificator.tblCampus WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $campus_id);
        return $query->execute();
    }
    
    function getBuildingNamesCG($connection, $campus_id) {
        $query = '
            SELECT ALTNAME as name FROM usmCodificator.tblBuilding
            WHERE idCampus = :campus_id
            ORDER BY ALTNAME;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':campus_id', $campus_id);
        $query->execute();
        return $query->fetchAll();
    }
    
    function getBuildingIdCG($connection, $campus_name, $building_name) {
        $query = '
            SELECT tblBuilding.id FROM usmCodificator.tblBuilding
            INNER JOIN usmCodificator.tblCampus ON tblCampus.id = tblBuilding.idCampus
            WHERE tblCampus.ALTNAME = :campus_name
                AND tblBuilding.ALTNAME = :building_name;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':campus_name', $campus_name);
        $query->bindParam(':building_name', $building_name);
        $query->execute();
        $result = $query->fetch(); 
        return $result['id'];
    }
    
    function editBuildingCG($connection, $building_id, $new_name) {
        $query = 'SELECT * FROM usmCodificator.tblBuilding WHERE ALTNAME = :new_name;';
        $query = $connection->prepare($query);
        $query->bindParam(':new_name', $new_name);
        $query->execute();
        
        if (!$query->fetch()) {
            $query = '
                UPDATE usmCodificator.tblBuilding SET ALTNAME = :new_name WHERE id = :id;
            ';
            $query = $connection->prepare($query);
            $query->bindParam(':id', $building_id);
            $query->bindParam(':new_name', $new_name);
            return $query->execute();
        } else {
            return False;
        }
    }
    
    function insertBuildingCG($connection, $campus_id, $name) {
        $query = '
            INSERT INTO usmCodificator.tblBuilding (idCampus, NAME, ALTNAME)
            VALUES (:campus_id, :name, :name);
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':campus_id', $campus_id);
        $query->bindParam(':name', $name);
        return $query->execute();
    }
    
    function deleteBuildingCG($connection, $building_id) {
        $query = 'DELETE FROM usmCodificator.tblBuilding WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $building_id);
        return $query->execute();
    }
    
    function getRoomNamesCG($connection, $building_id) {
        $query = '
            SELECT ALTNAME as name FROM usmCodificator.tblRoom
            WHERE idBuilding = :building_id
            ORDER BY ALTNAME;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':building_id', $building_id);
        $query->execute();
        return $query->fetchAll();
    }
    
    function editRoomCG($connection, $room_id, $new_name) {
        $query = 'SELECT * FROM usmCodificator.tblRoom WHERE ALTNAME = :new_name;';
        $query = $connection->prepare($query);
        $query->bindParam(':new_name', $new_name);
        $query->execute();
        
        if (!$query->fetch()) {
            $query = '
                UPDATE usmCodificator.tblRoom SET ALTNAME = :new_name WHERE id = :id;
            ';
            $query = $connection->prepare($query);
            $query->bindParam(':id', $room_id);
            $query->bindParam(':new_name', $new_name);
            return $query->execute();
        } else {
            return False;
        }
    }
    
    function insertRoomCG($connection, $building_id, $name) {
        $query = '
            INSERT INTO usmCodificator.tblRoom (idBuilding, NAME, ALTNAME)
            VALUES (:building_id, :name, :name);
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':building_id', $building_id);
        $query->bindParam(':name', $name);
        return $query->execute();
    }
    
    function deleteRoomCG($connection, $room_id) {
        $query = 'DELETE FROM usmCodificator.tblRoom WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $room_id);
        return $query->execute();
    }
    
    function getDevicesCG($connection, $room_id) {
        $query = '
            SELECT tblDevice.id, tblDevice.NAME AS caption,
                tblDevice.ALTNAME AS name
            FROM usmCodificator.tblDevice
            INNER JOIN usmCodificator.tblRoom ON tblRoom.id = tblDevice.idRoom
            WHERE tblRoom.id = :room_id
            ORDER BY tblDevice.NAME;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':room_id', $room_id);
        $query->execute();
        return $query->fetchAll();
    }
    
    function insertDeviceCG($connection, $room_id, $name, $caption) {
        $query = '
            INSERT INTO usmCodificator.tblDevice (idRoom, NAME, ALTNAME)
            VALUES (:room_id, :caption, :name);
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':room_id', $room_id);
        $query->bindParam(':caption', $caption);
        $query->bindParam(':name', $name);
        return $query->execute();
    }
    
    function deleteDeviceCG($connection, $device_id) {
        $query = 'DELETE FROM usmCodificator.tblDevice WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $device_id);
        return $query->execute();
    }
    
    function deletePropertyCG($connection, $property_id) {
        $query = 'DELETE FROM usmCodificator.tblProperties WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $property_id);
        return $query->execute();
    }
    
    function insertPropertyCG($connection, $device_id, $name) {
        $query = '
            INSERT INTO usmCodificator.tblProperties (idDevice, NAME, ALTNAME)
            VALUES (:device_id, :name, :name);
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':name', $name);
        $query->bindParam(':device_id', $device_id);
        return $query->execute();
    }
    
    function getPropertyDeviceCG($connection, $property_id) {
        $query = '
            SELECT tblDevice.NAME AS device, tblProperties.NAME AS property
            FROM usmCodificator.tblDevice
            INNER JOIN usmCodificator.tblProperties
                ON tblDevice.id = tblProperties.idDevice
            WHERE tblProperties.id = :property_id;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':property_id', $property_id);
        $query->execute();
        return $query->fetch();
    }
    
    function getPropertyTranslationsCG($connection, $property_id) {
        $query = '
            SELECT tblTranslations.id, tblTranslations.NAME AS name,
                tblTranslations.ALTNAME AS translation
            FROM usmCodificator.tblTranslations
            WHERE tblTranslations.idProperty = :property_id
            ORDER BY tblTranslations.name;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':property_id', $property_id);
        $query->execute();
        return $query->fetchAll();
    }
    
    function insertTranslationCG($connection, $property_id, $name, $translation) {
        $query = '
            INSERT INTO usmCodificator.tblTranslations
                (idProperty, NAME, ALTNAME)
            VALUE (:property_id, :name, :translation);
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':property_id', $property_id);
        $query->bindParam(':name', $name);
        $query->bindParam(':translation', $translation);
        return $query->execute();
    }
    
    function updateTranslationCG($connection, $id, $name, $translation) {
        $query = '
            UPDATE usmCodificator.tblTranslations
            SET NAME = :name, ALTNAME = :translation
            WHERE id = :id;
        ';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $id);
        $query->bindParam(':name', $name);
        $query->bindParam(':translation', $translation);
        return $query->execute();
    }
    
    function deleteTranslationCG($connection, $id) {
        $query = 'DELETE FROM usmCodificator.tblTranslations WHERE id = :id;';
        $query = $connection->prepare($query);
        $query->bindParam(':id', $id);
        return $query->execute();
    }
?>