
/****** Object:  StoredProcedure [dbo].[sp_InsertNewsToBase]    Script Date: 18-Aug-16 18:04:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_InsertNewsToBase]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_InsertNewsToBase]
GO


-- =============================================
-- Author:  (C) Vitacard System 2009
-- Create date: 2011-09-21
-- Description: Импортирует новость
-- =============================================
CREATE PROCEDURE [dbo].[sp_InsertNewsToBase]
 -- Add the parameters for the stored procedure here
 @date datetime
 ,@desc varchar(8000)
 ,@CreatorType int
 ,@modules varchar(8000)
AS
BEGIN
 SET NOCOUNT ON;

 -- Генерируем ID новости
 declare @newsID uniqueidentifier
 set @newsID = NEWID()

 -- Получаем модули
 declare @m_table table
 (
  id uniqueidentifier
  ,name varchar(255)
 )

 insert into @m_table(name)
 select a.Split
 from dbo.fn_Split(@modules, '+') a

 update m
 set m.id = c.ClientModuleID
 from @m_table m
  join VCLib.T_CLIENT_MODULES c on c.ModuleType like m.name + '%'

 -- Добавляем новость
 INSERT INTO VCLib.[T_NEWS_BOX]
            ([NewsBoxID]
            ,[DateCreate]
            ,[Desc]
            ,[CreatorType]
            ,[Pinned])
 SELECT
            @newsID--NewsBoxID, uniqueidentifier,>
            ,@date--DateCreate, datetime,>
            ,@desc--Desc, text,>
            ,@CreatorType--CreatorType, int,>)
            ,0

 INSERT INTO VCLib.[T_NEWS_BOX_LINK_USERS]
            ([NewsBoxLinkUsersID]
            ,[Users]
            ,[NewsBox])
 SELECT
            NEWID()--NewsBoxLinkUsersID, uniqueidentifier,>
            ,u.UserID--Users, uniqueidentifier,>
            ,@newsID--NewsBox, uniqueidentifier,>)
 from VCLib.T_USERS u
  join VCLib.T_USERS_ROLES ur on ur.UserID = u.UserID
  join VCLib.T_ROLE_CLIENT_MODULES rcm on rcm.RoleID = ur.RoleID
 where rcm.ClientModuleID in
 (
  select m.id
  from @m_table m
 )
 group by u.UserID
 
 INSERT INTO VCLib.[T_NEWS_BOX_LINK_MODULE]
            ([NewsBoxLinkModuleID]
            ,[ClientModules]
            ,[NewsBox])
 SELECT
            NEWID()--NewsBoxLinkUsersID, uniqueidentifier,>
            ,rcm.ClientModuleID--Users, uniqueidentifier,>
            ,@newsID--NewsBox, uniqueidentifier,>)
 from VCLib.T_ROLE_CLIENT_MODULES rcm
 where rcm.ClientModuleID in
 (
  select m.id
  from @m_table m
 )
 
END