diff --git a/database_dump/smart-home-server_extra.sql b/database_dump/smart-home-server_extra.sql index edebd60..31517c7 100644 --- a/database_dump/smart-home-server_extra.sql +++ b/database_dump/smart-home-server_extra.sql @@ -12,6 +12,9 @@ ALTER TABLE `device_auth` ADD PRIMARY KEY (`id`); + ALTER TABLE `areas` + ADD PRIMARY KEY (`id`); + -- -- Индексы таблицы `groups` -- diff --git a/database_dump/smart-home-server_table_areas.sql b/database_dump/smart-home-server_table_areas.sql new file mode 100644 index 0000000..2956b87 --- /dev/null +++ b/database_dump/smart-home-server_table_areas.sql @@ -0,0 +1,64 @@ +-- phpMyAdmin SQL Dump +-- version 5.2.1 +-- https://www.phpmyadmin.net/ +-- +-- Хост: localhost +-- Время создания: Дек 09 2025 г., 10:20 +-- Версия сервера: 12.1.2-MariaDB +-- Версия PHP: 8.5.0 + +SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; +START TRANSACTION; +SET time_zone = "+00:00"; + + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8mb4 */; + +-- +-- База данных: `smart-home-server` +-- + +-- -------------------------------------------------------- + +-- +-- Структура таблицы `areas` +-- + +CREATE TABLE `areas` ( + `id` int(11) NOT NULL, + `type` varchar(32) NOT NULL COMMENT 'space | room', + `alias` varchar(100) NOT NULL, + `display_name` varchar(255) NOT NULL, + `parent_id` int(11) NOT NULL DEFAULT 0, + `schema` text DEFAULT NULL, + `update_at` timestamp NOT NULL DEFAULT current_timestamp(), + `create_at` timestamp NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Индексы сохранённых таблиц +-- + +-- +-- Индексы таблицы `areas` +-- +ALTER TABLE `areas` + ADD PRIMARY KEY (`id`); + +-- +-- AUTO_INCREMENT для сохранённых таблиц +-- + +-- +-- AUTO_INCREMENT для таблицы `areas` +-- +ALTER TABLE `areas` + MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; +COMMIT; + +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; diff --git a/database_dump/smart-home-server_table_devices.sql b/database_dump/smart-home-server_table_devices.sql index 7dd05e1..192efde 100644 --- a/database_dump/smart-home-server_table_devices.sql +++ b/database_dump/smart-home-server_table_devices.sql @@ -3,7 +3,7 @@ -- https://www.phpmyadmin.net/ -- -- Хост: localhost --- Время создания: Дек 07 2025 г., 08:42 +-- Время создания: Дек 09 2025 г., 10:20 -- Версия сервера: 12.1.2-MariaDB -- Версия PHP: 8.5.0 @@ -29,6 +29,7 @@ CREATE TABLE `devices` ( `id` int(11) NOT NULL, + `area_id` int(11) NOT NULL DEFAULT 0, `alias` varchar(100) NOT NULL, `name` varchar(100) NOT NULL, `device_type` varchar(50) NOT NULL, diff --git a/server/SHServ/Entities/Area.php b/server/SHServ/Entities/Area.php new file mode 100644 index 0000000..bb84247 --- /dev/null +++ b/server/SHServ/Entities/Area.php @@ -0,0 +1,153 @@ + parent_id) { + return null; + } + + return new Area($this -> parent_id); + } + + public function get_inner_areas(): Array { + $raw_areas = app() -> thin_builder -> select( + self::$table_name, + self::get_fields(), + [[ "parent_id", "=", $this -> id() ]] + ); + + if(!$raw_areas) { + return []; + } + + $areas = []; + + foreach($raw_areas as $i => $raw_area) { + $areas[] = new Area($raw_area["id"], $raw_area); + } + + return $area; + } + + public function place_in_space(int $area_id): Array | bool { + $this -> parent_id = $area_id; + return $this -> update(); + } + + public function get_areas_by_type(String $type): Array { + $raw_areas = app() -> thin_builder -> select( + self::$table_name, + self::get_fields(), + [ + [ "parent_id", "=", $this -> id() ], + "AND", + [ "type", "=", $type ] + ] + ); + + if(!$raw_areas) { + return []; + } + + $areas = []; + + foreach($raw_areas as $i => $raw_area) { + $areas[] = new Area($raw_area["id"], $raw_area); + } + + return $area; + } + + public function get_area_by_alias(): Area | null { + $raw_areas = app() -> thin_builder -> select( + self::$table_name, + self::get_fields(), + [ + [ "parent_id", "=", $this -> id() ], + "AND", + [ "alias", "=", $alias ] + ], + [], "DESC", + [ 1 ] + ); + + if(!$raw_areas) { + return null; + } + + return new Area($raw_areas[0]["id"], $raw_areas[0]); + } + + public function get_inner_devices(): Array { + $raw_devices = app() -> thin_builder -> select( + Device::$table_name, + Device::get_fields(), + [["area_id", "=", $this -> id()]] + ); + + if(!$raw_devices) { + return []; + } + + $devices = []; + foreach($raw_devices as $raw_device) { + $devices[] = new Device($raw_device["id"], $raw_device); + } + + return $devices; + } + + public function get_device_by_alias(string $alias): Device | null { + $raw_devices = app() -> thin_builder -> select( + Device::$table_name, + Device::get_fields(), + [ + [ "area_id", "=", $this -> id() ], + "AND", + [ "alias", "=", $alias ] + ], + [], "DESC", + [ 1 ] + ); + + if(!$raw_devices) { + return null; + } + + $raw_device = $raw_devices[0]; + return new Device($raw_device["id"], $raw_device); + } + + public function get_devices_by_type(string $type): Array { + $raw_devices = app() -> thin_builder -> select( + Device::$table_name, + Device::get_fields(), + [ + [ "area_id", "=", $this -> id() ], + "=", + [ "type", "=", $type ] + ] + ); + + if(!$raw_devices) { + return []; + } + + $devices = []; + foreach($raw_devices as $raw_device) { + $devices[] = new Device($raw_device["id"], $raw_device); + } + + return $devices; + } +} \ No newline at end of file diff --git a/server/SHServ/Entities/Device.php b/server/SHServ/Entities/Device.php index 681aade..3aa8d75 100644 --- a/server/SHServ/Entities/Device.php +++ b/server/SHServ/Entities/Device.php @@ -3,13 +3,14 @@ namespace SHServ\Entities; use \SHServ\Entities\DeviceAuth; +use \SHServ\Entities\Area; use \SHServ\Tools\DeviceAPI\Base; use \SHServ\Tools\DeviceAPI\Relay; class Device extends \SHServ\Middleware\Entity { public static $table_name = "devices"; protected static $fields = [ - "id", "alias", "name", "device_type", "device_name", "device_ip", + "id", "area_id", "alias", "name", "device_type", "device_name", "device_ip", "device_mac", "device_hard_id", "firmware_version", "connection_state", "state", "description", "last_contact", "create_at", "update_at" ]; @@ -80,4 +81,17 @@ $this -> device_api_instance -> set_local_token($token); return $this -> auth() -> update(); } + + public function place_in_space(int $area_id): bool { + $this -> area_id = $area_id; + return $this -> update() ? true : false; + } + + public function get_parent_area(): Area | null { + if(!$this -> area_id) { + return null; + } + + return new Area($this -> area_id); + } } \ No newline at end of file diff --git a/server/SHServ/Models/Areas.php b/server/SHServ/Models/Areas.php new file mode 100644 index 0000000..85a9ebe --- /dev/null +++ b/server/SHServ/Models/Areas.php @@ -0,0 +1,90 @@ + thin_builder -> insert(Area::$table_name, [ + "alias" => $alias, + "display_name" => $name, + "type" => $device_info["data"]["device_type"], + "schema" => "", + "parent_id" => "0", + "create_at" => date("Y-m-d H:i:s") + ]); + + $area = $area_id ? new Area($area_id) : null; + + if(!$area) { + return false; + } + } + + public function alias_is_uniq(String $alias): bool { + $count = app() -> thin_builder -> count( + Area::$table_name, + [ ["alias", "=", $alias] ] + ); + + return $count ? false : true; + } + + public function get_exists_types(): Array { + $areas = app() -> thin_builder -> select( Area::$table_name, [ "type" ], [] ); + if(!$areas) { + return []; + } + + $types = array_map(function($area) { + return $area["type"]; + }, $areas); + + $uniq_types = []; + + foreach($types as $type) { + if(in_array($type, $uniq_types)) { + continue; + } + + $uniq_types[] = $type; + } + + return $uniq_types; + } + + public function get_areas_by_type(String $type): Array { + $raw_areas = app() -> thin_builder -> select( + Area::$table_name, + Area::get_fields(), + [["type", "=", $type]] + ); + + if(!$raw_areas) { + return []; + } + + $areas = []; + foreach ($raw_areas as $raw_area) { + $areas[] = new Area($raw_area["id"], $raw_area); + } + + return $areas; + } + + public function get_area_by_alias(String $alias): Area | null { + $raw_area = app() -> thin_builder -> select( + Area::$table_name, + Area::get_fields(), + [[ "type", "=", $type ]], + [], "DESC", [ 1 ] + ); + + if(!$raw_area) { + return null; + } + + return new Area($raw_area["id"], $raw_area); + } +} \ No newline at end of file