-- =============================================
-- Author:		Pablo Lopez
-- Create date: 2019-08-22
-- Description:	<Description, ,>
-- =============================================
-- DROP IF EXISTS FUNCION dbo.utils_Singularize
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[utils_PropertyNameFromColumnName]')
	AND type in (N'FN', N'IF',N'TF', N'FS', N'FT'))
BEGIN
	DROP FUNCTION [dbo].[utils_PropertyNameFromColumnName]
END
GO
-- CREATE
CREATE FUNCTION [dbo].[utils_PropertyNameFromColumnName]
(
	-- Add the parameters for the function here
	@columnName varchar(128),
	@className varchar(128),
	@classNamePlural varchar(128)
)
RETURNS varchar(128)
AS
BEGIN
	-- Declare the return variable here
	/*
	e1:
		@columnName = 'QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsAdditionalDescription'
		@className = 'QuoteRequestItem'
		@classNamePlural = 'QuoteRequestItems'
	e2:
		@columnName = 'InventoryItemPartDetails_InventoryItemItemDetails_InventoryItemsBrand'
		@className = 'InventoryItem'
		@classNamePlural = 'InventoryItems'
	*/
	DECLARE @Result varchar(128)

	-- e1: QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsAdditionalDescription => __AdditionalDescription
	-- e2: InventoryItemPartDetails_InventoryItemItemDetails_InventoryItemsBrand -> InventoryItemPartDetails_InventoryItemItemDetails_Brand
	set @Result = replace(@columnName, @classNamePlural, '')

	-- e1: __AdditionalDescription => __AdditionalDescription
	-- e2: InventoryItemPartDetails_InventoryItemItemDetails_Brand => PartDetails_ItemDetails_Brand
	set @Result = replace(@Result, @className, '')
	
	-- e1: __AdditionalDescription => _AdditionalDescription
	-- e2: PartDetails_ItemDetails_Brand => ItemDetails_Brand
	set @Result = substring(@Result, charindex('_', @Result) + 1, len(@Result))

	-- e1: _AdditionalDescription => AdditionalDescription
	while charindex('_', @Result) = 1
	begin
		set @Result = substring(@Result, charindex('_', @Result) + 1, len(@Result))
	end

	RETURN @Result
END
GO


