USE [xworks-v2]
GO
/****** Object:  StoredProcedure [process].[ProcessTypeCategoryCanCreate]    Script Date: 1/21/2020 11:17:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [process].[ProcessTypeCategoryCanCreate]
	@CategoryLabel varchar(20)
as
begin


declare @msg varchar(100) = '', @hasCategoryLabel bit

	select	@hasCategoryLabel = case when count(*) > 0 then 1 else 0 end
	from	[process].[ProcessTypeCategory]
	where	CategoryLabel = @CategoryLabel

	if @hasCategoryLabel = 1
	begin
		set @msg = 'processCategory.validation-hasCategoryLabel'
	end

	select @msg Msg
end
GO
/****** Object:  StoredProcedure [process].[ProcessTypeCategoryCanUpdate]    Script Date: 1/21/2020 11:17:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [process].[ProcessTypeCategoryCanUpdate]
	@CategoryLabel varchar(20),
	@Id int
as
begin


declare @msg varchar(100) = '', @hasCategoryLabel bit

	select	@hasCategoryLabel = case when count(*) > 0 then 1 else 0 end
	from	[process].[ProcessTypeCategory]
	where	CategoryLabel = @CategoryLabel
	and Id != @Id

	if @hasCategoryLabel = 1
	begin
		set @msg = 'processCategory.validation-hasCategoryLabel'
	end

	select @msg Msg
end
GO
/****** Object:  StoredProcedure [process].[ProcessTypeCategoryCreate]    Script Date: 1/21/2020 11:17:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [process].[ProcessTypeCategoryCreate]
	@ParentCategoryId int,
	@CategoryLabel varchar(20),
	@CategoryDescription varchar(250),
	@Filename nvarchar(128)
as
begin
	INSERT INTO [process].[ProcessTypeCategory]
			   ([ParentCategoryId]
			   ,[CategoryLabel]
			   ,[CategoryDescription]
			   ,[Filename])
		 VALUES
			   (@ParentCategoryId
			   ,@CategoryLabel
			   ,@CategoryDescription
			   ,@Filename)
	declare @id int = (select SCOPE_IDENTITY())

	select @id Id
end
GO
/****** Object:  StoredProcedure [process].[ProcessTypeCategoryDelete]    Script Date: 1/21/2020 11:17:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [process].[ProcessTypeCategoryDelete]
	@Id int
as
begin
	delete	[process].[ProcessTypeCategory]
	where	Id = @Id
end
GO
/****** Object:  StoredProcedure [process].[ProcessTypeCategoryForFE]    Script Date: 1/21/2020 11:17:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [process].[ProcessTypeCategoryForFE]
as
begin
	SELECT	 [Id]
			,[ParentCategoryId]
			,[CategoryLabel]
			,[CategoryDescription]
			,[Filename]
	FROM	[process].[ProcessTypeCategory]
end
GO
/****** Object:  StoredProcedure [process].[ProcessTypeCategoryGetAll]    Script Date: 1/21/2020 11:17:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [process].[ProcessTypeCategoryGetAll]
	@Id int,
	@PageNumber int = 1,
	@PageSize int = 10
as
begin
	declare @offset int = (@PageNumber - 1) * @PageSize

	select		Id, ParentCategoryId, CategoryLabel,  CategoryDescription, Filename
	from		[process].ProcessTypeCategory
	where		(Id = @Id or @Id is null)
	order by Id

	OFFSET @offset rows fetch next @PageSize rows ONLY
end
GO
/****** Object:  StoredProcedure [process].[ProcessTypeCategoryGetByParams]    Script Date: 1/21/2020 11:17:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [process].[ProcessTypeCategoryGetByParams]
	@Id int
as
begin
	SELECT		[Id]
			   ,[ParentCategoryId]
			   ,[CategoryLabel]
			   ,[CategoryDescription]
			   ,[Filename]
	  FROM	[process].[ProcessTypeCategory]
	  where	Id = @Id
end
GO
/****** Object:  StoredProcedure [process].[ProcessTypeCategoryGetNotMe]    Script Date: 1/21/2020 11:17:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [process].[ProcessTypeCategoryGetNotMe]
	 @Id int
as
begin
	select		Id, ParentCategoryId, CategoryLabel, CategoryDescription, Filename
	from		process.ProcessTypeCategory
	where		(@Id = 0 or Id != @Id)
end
GO
/****** Object:  StoredProcedure [process].[ProcessTypeCategorySearch]    Script Date: 1/21/2020 11:17:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [process].[ProcessTypeCategorySearch]
	@PageNumber int = 1,
	@PageSize int = 10,
	@SearchTerm varchar(max) = null
as
begin
	declare @offset int = (@PageNumber - 1) * @PageSize

	select		COUNT(*) OVER() TotalRows,
				@PageNumber PageNumber,
				CEILING((COUNT(*) OVER()) / CAST(@PageSize AS float)) TotalPages,
				*
	from		process.ProcessTypeCategory
	where		replace(
					lower(
						isnull(CategoryLabel, '')	+ ISNULL(CategoryDescription, '')
					),
				' ', '')  Collate SQL_Latin1_General_CP1253_CI_AI

				like '%' + replace(@SearchTerm,' ','') + '%'  Collate SQL_Latin1_General_CP1253_CI_AI

	order by CategoryLabel

	OFFSET @offset rows fetch next @PageSize rows ONLY
end
GO
/****** Object:  StoredProcedure [process].[ProcessTypeCategoryUpdate]    Script Date: 1/21/2020 11:17:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [process].[ProcessTypeCategoryUpdate]
	@Id int,
	@ParentCategoryId int,
	@CategoryLabel varchar(20),
	@CategoryDescription varchar(250),
	@Filename nvarchar (128)
as
begin
	update	[process].[ProcessTypeCategory]
	set		[ParentCategoryId] = @ParentCategoryId,
			[CategoryLabel] = @CategoryLabel,
			[CategoryDescription] = @CategoryDescription,
			[Filename] = case
						 when @Filename = '' then
							[Filename]
						 else
							@Filename
						end
	where	Id = @Id

end
GO