-- =============================================
-- Author:      <Rathanavel Subramaniam - https://ratsubsharewall.blogspot.com>
-- Create Date: <7-May-2018>
-- Description: <To store Sensitive/Phrase/Language - Analysis using Cognitive services>
-- =============================================
ALTER PROCEDURE [dbo].[CreateTAUserVoice]
(
  @ProductNumber nvarchar(30),
	@CustomerID float,
	@TALanguage nchar(10), 
	@TASentiment float(53),
	@TAPhrases nvarchar(max)
)
AS
BEGIN 
declare @UserState nvarchar(30)
declare @UserCity nvarchar(30)
declare @UserRegion nvarchar(30)

select @UserCity = City, @UserState = StateProvince, @UserRegion = CountryRegion from SalesLT.Address
where AddressID = (select AddressID from SalesLT.CustomerAddress where CustomerID = @CustomerID)

	insert into TA_Uservoice (ProductNumber, ProductName, ProductDescription, ProductPrice, UserAge, UserGender, UserRegion, UserLanguage, Timeline, Date, TALanguage, TASentiment, ActualSentiment, TAPhrases, UserState, UserCity) values
	(
		@ProductNumber,
		(select SalesLT.Product.Name from SalesLT.Product where ProductNumber = @ProductNumber),
		((SELECT        Description
FROM            SalesLT.ProductDescription
WHERE        (ProductDescriptionID =
                             (SELECT        ProductDescriptionID
                               FROM            SalesLT.ProductModelProductDescription
                               WHERE        (ProductModelID =
                                                             (SELECT        ProductModelID
                                                               FROM            SalesLT.Product
                                                               WHERE        (ProductNumber = @ProductNumber))) AND (Culture = @TALanguage))))
  ),
		
		(select StandardCost from SalesLT.Product where ProductNumber = @ProductNumber),		
		null,
		null,
		@UserRegion,
		null,
		null,
		GETDATE(),
		@TALanguage,
		FORMAT(@TASentiment, 'N1'),
		@TASentiment,
		Replace(Replace(Replace(@TAPhrases,'[',''),']',''),'"',''),
		@UserState,
		@UserCity)
    
    END