GO
--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_NAME = 'qrTypeElement') DROP VIEW dbo.qrTypeElement
--------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------
-- Справочник типов элементов
--------------------------------------------------------------------------------
CREATE VIEW dbo.qrTypeElement AS

SELECT 

   tp.iTypeElementID,         -- FK ссылка на тип элемента: Деталь, Сборка, Изделие
   tp.sTypeElementCode,       -- Код типа элемента (DET, SBR, IZD)
   tp.sTypeElementName        -- Название типа элемента

FROM 

   dbo.TypeElement tp
--------------------------------------------------------------------------------
GO

GO
--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_NAME = 'qrElement') DROP VIEW dbo.qrElement
--------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------
-- Единый перечень элементов любых типов - все и детали, и сборки, и изделия
--------------------------------------------------------------------------------
CREATE VIEW dbo.qrElement AS

SELECT 

   el.iTypeElementID,         -- FK ссылка на тип элемента: Деталь, Сборка, Изделие
   tp.sTypeElementCode,       -- Код типа элемента (DET, SBR, IZD)
   tp.sTypeElementName,       -- Название типа элемента

   el.iElementID,             -- PK автосчетчик
   el.sElementCode,           -- Код элемента
   el.sElementName,           -- Название элемента
   el.dbElementWeight,        -- Вес
   el.sElementElectricLight,  -- ???
   el.dbElementCoverage,      -- Покрытие
   el.sElementNote,           -- Примечание
   sElementAlias = el.sElementCode + ' ' + el.sElementName + ' (' + tp.sTypeElementName + ')' 

FROM 

   dbo.Element el
   LEFT OUTER JOIN dbo.TypeElement tp ON tp.iTypeElementID = el.iTypeElementID
--------------------------------------------------------------------------------
GO

-- select iElementID, sElementAlias FROM dbo.qrElement ORDER BY iTypeElementID, sElementCode

GO
--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_NAME = 'qrNodeElement') DROP VIEW dbo.qrNodeElement
--------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------
-- Структура (состав) Изделий и Сборок
-- Каждый узел типа Изделия или Сборки включает любое количество подсборок и деталей
--------------------------------------------------------------------------------
CREATE VIEW dbo.qrNodeElement AS

SELECT 

   nd.iNodeElementID,               -- PK авт осчетчик
   nd.nNodeElementCount,            -- Число шт. включаемых элементов
   nd.sNodeElementAddress,          -- Адрес узла

   el.iTypeElementID,               -- FK ссылка на тип элемента: Деталь, Сборка, Изделие
   tp.sTypeElementCode,             -- Код типа элемента (DET, SBR, IZD)
   tp.sTypeElementName,             -- Название типа элемента

   nd.iElementID,                   -- ID включаемого элемента
   el.sElementCode,                 -- Код элемента
   el.sElementName,                 -- Название элемента
   el.dbElementWeight,              -- Вес
   el.sElementElectricLight,        -- ???
   el.dbElementCoverage,            -- Покрытие
   el.sElementNote,                 -- Примечание

   iTypeElementID_Parent = el_par.iTypeElementID,              -- FK ссылка на тип элемента: Деталь, Сборка, Изделие
   sTypeElementCode_Parent = tp_par.sTypeElementCode,          -- Код типа элемента (DET, SBR, IZD)
   sTypeElementName_Parent = tp_par.sTypeElementName,          -- Название типа элемента

   nd.iElementID_Parent,                                       -- ID более крупного, куда включаем
   sElementCode_Parent = el_par.sElementCode,                  -- Код элемента
   sElementName_Parent = el_par.sElementName,                  -- Название элемента
   dbElementWeight_Parent = el_par.dbElementWeight,            -- Вес
   sElementElectricLight_Parent = el_par.sElementElectricLight,-- ???
   dbElementCoverage_Parent = el_par.dbElementCoverage,        -- Покрытие
   sElementNote_Parent = el_par.sElementNote                   -- Примечание

FROM 

   -- Основаная таблица с узлами
   dbo.NodeElement nd
   -- Включаемый элемент (потомок, child)
   LEFT OUTER JOIN dbo.Element el ON el.iElementID = nd.iElementID
   LEFT OUTER JOIN dbo.TypeElement tp ON tp.iTypeElementID = el.iTypeElementID
   -- Более крупый элемент, в который включают (родитель, parent)
   LEFT OUTER JOIN dbo.Element el_par ON el_par.iElementID = nd.iElementID_Parent
   LEFT OUTER JOIN dbo.TypeElement tp_par ON tp_par.iTypeElementID = el_par.iTypeElementID
--------------------------------------------------------------------------------
GO

-- SELECT * FROM qrNodeElement WHERE iElementID=13
-- SELECT * FROM qrNodeElement WHERE iElementID_Parent=12

GO
--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_NAME = 'qrElementTree') DROP VIEW dbo.qrElementTree
--------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------
-- Полный состав всего изделия
--------------------------------------------------------------------------------
CREATE VIEW dbo.qrElementTree AS

WITH NodeLevel
(
   iNodeElementID, iElementID_Root, iElementID_Parent, iElementID, 
   sElementCode, sElementName, sNodeElementAddress, nNodeElementCount
) 
AS 
(

   -- Первый корневой уровень
   SELECT 
      nd.iNodeElementID,               -- ID узла
      iElementID_Root = nd.iElementID, -- ID изделия, самого верхнего элемента
      nd.iElementID_Parent,            -- ID элемента
      nd.iElementID,                   -- ID элемента
      nd.sElementCode,                 -- Код элемента
      nd.sElementName,                 -- Название элемента
      sNodeElementAddress = '#' + CAST(nd.iElementID AS VARCHAR(MAX)) + '#', 
      ISNULL(nd.nNodeElementCount, 0)
   FROM dbo.qrNodeElement nd 
   WHERE NOT EXISTS(SELECT * FROM dbo.NodeElement nd2 WHERE nd2.iElementID = nd.iElementID_Parent)
   
   -- Все последующие уровни
   UNION ALL SELECT 
      nd.iNodeElementID,               -- ID узла
      NodeLevel.iElementID_Root,       -- ID изделия, самого верхнего элемента
      nd.iElementID_Parent,            -- ID элемента
      nd.iElementID,                   -- ID элемента
      nd.sElementCode,                 -- Код элемента
      nd.sElementName,                 -- Название элемента
      NodeLevel.sNodeElementAddress + CAST(nd.iElementID AS VARCHAR(MAX)) + '#', 
      nNodeElementCount = ISNULL(NodeLevel.nNodeElementCount, 0) * ISNULL(nd.nNodeElementCount, 0)
   FROM 
      dbo.qrNodeElement nd 
      JOIN NodeLevel ON nd.iElementID_Parent = NodeLevel.iElementID
)
SELECT 
   iNodeElementID,
   iElementID_Root,
   iElementID_Parent, 
   iElementID, 
   sElementCode, 
   sElementName, 
   sNodeElementAddress, 
   nNodeElementCount 
FROM NodeLevel
--------------------------------------------------------------------------------
GO

GO
--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_NAME = 'qrNodeElement2') DROP VIEW dbo.qrNodeElement2
--------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------
-- Структура (состав) Изделий и Сборок
-- Каждый узел типа Изделия или Сборки включает любое количество подсборок и деталей
-- Плюс добавлены вычисленные из полного дерева поля
--    ID изделия
--    адрес
--    количество элементов данного узла во всем дереве
--------------------------------------------------------------------------------
CREATE VIEW dbo.qrNodeElement2 AS

SELECT 

   nd.iNodeElementID,               -- PK авт осчетчик
   nd.nNodeElementCount,            -- Число шт. включаемых элементов
   tr.iElementID_Root,              -- ID изделия - самого верхнего, корневого элемента
   tr.sNodeElementAddress,          -- Адрес узла
   nNodeElementCount_Tree = tr.nNodeElementCount, -- Общее количество элементов вверх по дереву
                                    -- если по пути наверх в количество вложений в очередной узел > 1 
                                    -- то все корректно умножается
                                    -- Потом для общего количества элементов по всему изделию, 
                                    -- не взирая на то как именно они вложены, останется просто сложить
                                    -- Аналогичным образом можно считать вес изделия

   el.iTypeElementID,               -- FK ссылка на тип элемента: Деталь, Сборка, Изделие
   tp.sTypeElementCode,             -- Код типа элемента (DET, SBR, IZD)
   tp.sTypeElementName,             -- Название типа элемента

   nd.iElementID,                   -- ID включаемого элемента
   el.sElementCode,                 -- Код элемента
   el.sElementName,                 -- Название элемента
   el.dbElementWeight,              -- Вес
   el.sElementElectricLight,        -- ???
   el.dbElementCoverage,            -- Покрытие
   el.sElementNote,                 -- Примечание

   iTypeElementID_Parent = el_par.iTypeElementID,              -- FK ссылка на тип элемента: Деталь, Сборка, Изделие
   sTypeElementCode_Parent = tp_par.sTypeElementCode,          -- Код типа элемента (DET, SBR, IZD)
   sTypeElementName_Parent = tp_par.sTypeElementName,          -- Название типа элемента

   nd.iElementID_Parent,                                       -- ID более крупного, куда включаем
   sElementCode_Parent = el_par.sElementCode,                  -- Код элемента
   sElementName_Parent = el_par.sElementName,                  -- Название элемента
   dbElementWeight_Parent = el_par.dbElementWeight,            -- Вес
   sElementElectricLight_Parent = el_par.sElementElectricLight,-- ???
   dbElementCoverage_Parent = el_par.dbElementCoverage,        -- Покрытие
   sElementNote_Parent = el_par.sElementNote                   -- Примечание
   
FROM 

   -- Основаная таблица с узлами
   dbo.NodeElement nd
   -- Включаемый элемент (потомок, child)
   LEFT OUTER JOIN dbo.Element el ON el.iElementID = nd.iElementID
   LEFT OUTER JOIN dbo.TypeElement tp ON tp.iTypeElementID = el.iTypeElementID
   -- Более крупый элемент, в который включают (родитель, parent)
   LEFT OUTER JOIN dbo.Element el_par ON el_par.iElementID = nd.iElementID_Parent
   LEFT OUTER JOIN dbo.TypeElement tp_par ON tp_par.iTypeElementID = el_par.iTypeElementID
   -- Добавления запроса полного дерева с вычисленным адресом и общим количеством элементов в дереве
   LEFT OUTER JOIN dbo.qrElementTree tr ON tr.iNodeElementID = nd.iNodeElementID
--------------------------------------------------------------------------------
GO