Notes

USE [CloudAppsJLL]
GO

/****** Object: UserDefinedFunction [dbo].[ArticlePreferenceST] Script Date: 2/6/2017 11:06:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ArticlePreferenceST]
(
@fieldValue nvarchar(200),
@articleFieldName nvarchar(50)
)
RETURNS nvarchar(200)
BEGIN



declare @result nvarchar(MAX)
declare @RestValue nvarchar(MAX)

IF @fieldValue is NULL OR LEN(@fieldValue)=0
RETURN '1=1 '

if @fieldValue like '%,%'
BEGIN

DECLARE @Names VARCHAR(8000)

SELECT @Names =COALESCE(@Names + ' OR '+@articleFieldName+' LIKE ''%', '') + Value + '%''' FROM dbo.Split (@fieldValue )


RETURN '('+@articleFieldName+' LIKE ''%'+@Names+')'

END

RETURN @articleFieldName +' LIKE ''%'+@fieldValue+'%'''
END


GO

USE [CloudAppsJLL]
GO

/****** Object: StoredProcedure [dbo].[GetArticlesForContact] Script Date: 2/6/2017 11:07:54 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetArticlesForContact]
-- Add the parameters for the stored procedure here
@ContactID int,
@NumTotalArticles int,
@NumTotalPerPreference int
AS
BEGIN

DECLARE @PreferenceSentence varchar(200)
DECLARE @PropertyType varchar(200)
DECLARE @Country varchar(200)
DECLARE @CityMarket varchar(200)
DECLARE @RelatedIndustry varchar(200)
DECLARE @RelatedService nvarchar(200)
DECLARE @IL_State nvarchar(50)
DECLARE @IL_Country nvarchar(50)
DECLARE @IL_CityMarket nvarchar(50)
DECLARE @OLM_State nvarchar(50)
DECLARE @OLM_CityMarket nvarchar(50)
DECLARE @OLM_Country nvarchar(50)
DECLARE @IndustryResearch nvarchar(200)
DECLARE @InvestorResearch nvarchar(200)
DECLARE @EconomicResearch nvarchar(200)
DECLARE @AddPropertyTypes nvarchar(200)
DECLARE @AddPropertyST nvarchar(200)

DECLARE @sql nvarchar(Max)

DECLARE @RootPref varchar(200)
DECLARE @articles table (articleID int)
--Obtain the details of the preference
SELECT
@PropertyType = PropertyType,
@Country = Country,
@CityMarket = CityMarket,
@RelatedIndustry=RelatedIndustry,
@RelatedService= RelatedService,
@IL_State= REPLACE ([Industrial & Logistical - State],',',''','''),
@IL_Country= REPLACE ([Industrial & ogistical - Country],',',''','''),
@IL_CityMarket= REPLACE ([Industrial & Logistical - City/Market],',',''','''),
@OLM_State=REPLACE([Office Local Markets - State],',',''','''),
@OLM_CityMarket= REPLACE ([Office Local Markets - City/Market],',',''','''),
@OLM_Country= REPLACE([Office Local Markets - Country],',',''','''),
@IndustryResearch=REPLACE([Industry Research],',',''','''),
@InvestorResearch= REPLACE([Investor Research],',',''','''),
@EconomicResearch=REPLACE([Economic Research],',',''','''),
@AddPropertyTypes=REPLACE([AdditionalPropertyType],',',''','''),
@AddPropertyST=REPLACE([AdditionalPropertySubType],',',''',''')

FROM [dbo].[ContactPreference] where [ContactId]= @ContactID


IF OBJECT_ID('tempdb..#Articles') IS NOT NULL
Truncate TABLE #Articles
else
CREATE TABLE #Articles(articleID int)




--Create Cursor to go through each preference root based on the order
DECLARE BU_cursor CURSOR FAST_FORWARD FOR
Select Name from [PreferenceRootItems] order by Priority asc

---Open cursor and fetch next record
OPEN BU_cursor
FETCH NEXT FROM BU_cursor INTO @RootPref

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'PREF ' +@RootPref
IF @RootPref ='Economic Research' AND LEN(@EconomicResearch)>0
BEGIN
IF @EconomicResearch='U.S. Economy'
Insert into #articles SELECT TOP(@NumTotalPerPreference) A.[ArticleId] from Article A where
A.Topic='Economy' and A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId=@ContactID)
IF @EconomicResearch='U.S. Employment'
Insert into #articles SELECT TOP(@NumTotalPerPreference) A.[ArticleId] from Article A where
A.Topic='Labor / Employment Trends' and A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId=@ContactID)
END
IF @RootPref ='Investor Research' AND LEN(@InvestorResearch)>0
BEGIN
IF @InvestorResearch like '%Office%'
Insert into #articles SELECT TOP(@NumTotalPerPreference) A.[ArticleId] from Article A where
A.RelatedIndustry='Office' and A.ClientType='Investors ＆ Developers' and
A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId=@ContactID)

IF @InvestorResearch like '%Industrial%'
Insert into #articles SELECT TOP(@NumTotalPerPreference) A.[ArticleId] from Article A,[dbo].[PreferenceChildItems] PC where
A.RelatedIndustry='Industrial and Logistics' and A.ClientType='Investors & Developers' and
A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId=@ContactID) order by PC.Priority asc
IF @InvestorResearch like '%Hotels & Hospitality%'
Insert into #articles SELECT TOP(@NumTotalPerPreference) A.[ArticleId] from Article A,[dbo].[PreferenceChildItems] PC where
A.RelatedIndustry='Hotels and Hospitality' and A.ClientType='Investors & Developers' and
A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId=@ContactID) order by PC.Priority asc
IF @InvestorResearch like '%Multifamily%'
Insert into #articles SELECT TOP(@NumTotalPerPreference) A.[ArticleId] from Article A,[dbo].[PreferenceChildItems] PC where
A.RelatedIndustry='Multifamily' and A.ClientType='Investors & Developers' and
A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId=@ContactID) order by PC.Priority asc
IF @InvestorResearch like '%Retail%'
Insert into #articles SELECT TOP(@NumTotalPerPreference) A.[ArticleId] from Article A,[dbo].[PreferenceChildItems] PC where
A.RelatedIndustry='Retail' and A.ClientType='Investors & Developers' and
A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId=@ContactID) order by PC.Priority asc
IF @InvestorResearch like '%Net Lease%'
Insert into #articles SELECT TOP(@NumTotalPerPreference) A.[ArticleId] from Article A,[dbo].[PreferenceChildItems] PC where
A.RelatedService='Net Leas' and A.ClientType='Investors & Developers' and
A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId=@ContactID) order by PC.Priority asc
END
IF @RootPref ='Industry Research' AND LEN(@IndustryResearch)>0
BEGIN

Set @sql='Insert into #articles SELECT TOP('+ CONVERT(VARCHAR(2), @NumTotalPerPreference) +') A.[ArticleId] from Article A where '+
dbo.ArticlePreferenceST(@IndustryResearch,'A.RelatedIndustry')
+'and A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId='+ CONVERT(VARCHAR(50),@ContactID) + ')'
PRINT 'Industry Research '+@sql
exec sp_executesql @sql
END
IF @RootPref ='Office Local Markets' AND (@OLM_State IS NOT NULL OR @OLM_CityMarket IS NOT NULL OR @OLM_Country IS NOT NULL )
BEGIN

BEGIN TRY
Set @sql='Insert into #articles SELECT TOP('+ CONVERT(VARCHAR(2), @NumTotalPerPreference) +') A.[ArticleId] from Article A where A.PropertyType=''Office'' AND ( '+ dbo.ArticlePreferenceST(@OLM_CityMarket,'A.City')
+' OR '+ dbo.ArticlePreferenceST(@OLM_State,'A.State') +') AND '+ dbo.ArticlePreferenceST(@OLM_Country,'A.Country') + ' AND A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId='+CONVERT(VARCHAR(50),@ContactID)+')'
PRINT 'OLM SQL '+@sql
exec sp_executesql @sql
END TRY
BEGIN CATCH
PRINT 'ERROR OLM'
END CATCH
END
IF @RootPref ='Industrial & Logistical' AND (@IL_State IS NOT NULL OR @IL_CityMarket IS NOT NULL OR @IL_Country IS NOT NULL )
BEGIN
Set @sql='Insert into #articles SELECT TOP('+ CONVERT(VARCHAR(2), @NumTotalPerPreference) +') A.[ArticleId] from Article A where A.PropertyType=''Industrial and Logistics'' AND ('
+dbo.ArticlePreferenceST(@IL_State,'A.State')+' OR '+ dbo.ArticlePreferenceST(@IL_CityMarket,'A.City')+') AND '+ dbo.ArticlePreferenceST(@IL_Country,'A.Country')
+' AND A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId='+CONVERT(VARCHAR(50),@ContactID)+')'

PRINT @sql
exec sp_executesql @sql
END
--Insert into @articles SELECT TOP('+@NumTotalPerPreference+') A.[ArticleId] from Article A,[dbo].[PreferenceChildItems] PC where A.Country=@Country and PC.Name= A.Country and PC.RootItemId=4 and A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId=@ContactID) order by PC.Priority asc
IF @RootPref ='Additional Property Types'
BEGIN
IF @AddPropertyTypes like '%Retail%'
BEGIN
Set @sql='Insert into #articles SELECT TOP('+CONVERT(VARCHAR(2), @NumTotalPerPreference)+') A.[ArticleId] from Article A where
A.[PropertyType]=''Retail'' and A.ClientType in ('''+@AddPropertyST+''') and
A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId='+CONVERT(VARCHAR(50),@ContactID)+')'

exec sp_executesql @sql
END
IF @AddPropertyTypes like '%Industrial%'
BEGIN
Set @sql='Insert into #articles SELECT TOP('+CONVERT(VARCHAR(2), @NumTotalPerPreference)+') A.[ArticleId] from Article A where
A.[PropertyType]=''Industrial and Logistics'' and A.RelatedIndustry in ('''+@AddPropertyST+''') and
A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId='+CONVERT(VARCHAR(50),@ContactID)+')'

exec sp_executesql @sql
END
IF @AddPropertyTypes like '%Multifamily%' OR @AddPropertyTypes like '%Life Sciences%' OR @AddPropertyTypes like '%Hotels & Hospitality%' OR @AddPropertyTypes like '%Data Centers%'
BEGIN
Set @sql='Insert into #articles SELECT TOP('+CONVERT(VARCHAR(2), @NumTotalPerPreference)+') A.[ArticleId] from Article A
where A.PropertyType in ('''+@AddPropertyTypes+''') and
A.ArticleID not in (Select CAR.[ArticleId] from [dbo].[ContactArticleRegister] CAR where ContactId='+CONVERT(VARCHAR(50),@ContactID)+')'
PRINT @sql
exec sp_executesql @sql
END
END
FETCH NEXT FROM BU_cursor INTO @RootPref
END

CLOSE BU_cursor
DEALLOCATE BU_cursor

Insert into [dbo].[ContactArticleRegister] (ContactId, ArticleID, RegisterDate) (Select TOP(@NumTotalArticles) @ContactID, articleID, GETDATE() from #articles)
Select TOP(@NumTotalArticles) articleID as ART from #articles

END
GO

USE [CloudAppsJLL]
GO

/****** Object: Table [dbo].[Article] Script Date: 2/6/2017 11:08:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Article](
[ArticleId] [int] NOT NULL,
[Description] [varchar](max) NULL,
[URL] [varchar](300) NULL,
[PropertyType] [varchar](200) NULL,
[Country] [varchar](max) NULL,
[CityMarket] [varchar](200) NULL,
[RelatedIndustry] [varchar](200) NULL,
[RelatedService] [varchar](300) NULL,
[Title] [nvarchar](200) NULL,
[LastUpdatedDate] [datetime] NOT NULL,
[ClientType] [nvarchar](max) NULL,
[Industry] [nvarchar](max) NULL,
[State] [nvarchar](max) NULL,
[City] [nvarchar](max) NULL,
[Topic] [nvarchar](max) NULL,
[Language] [nvarchar](max) NULL,
[Modified] [datetime] NULL,
[PublishDate] [datetime] NULL,
CONSTRAINT [PK_Article] PRIMARY KEY CLUSTERED
(
[ArticleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO