/****** Object: StoredProcedure [dbo].[usp_MEMBERDIRECTORY_SEARCH_NATIONAL] Script Date: 12/4/2014 2:18:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF EXISTS
(
Select 1
From INFORMATION_SCHEMA.ROUTINES
Where ROUTINE_NAME = 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL'
And ROUTINE_TYPE = 'PROCEDURE'
)
Begin
DROP PROCEDURE [dbo].[usp_MEMBERDIRECTORY_SEARCH_NATIONAL]
End
GO
CREATE PROCEDURE [dbo].[usp_MEMBERDIRECTORY_SEARCH_NATIONAL]
(
@SCHEMA_ONLY BIT = 0, --EITHER THIS CAN BE STORE ONLY 0 AND 1,
@IP_ORG_ID ORG = null ,
@IP_ORG_UNIT_ID ORG = null ,
@IP_IDENTITY_MCID CUSTOMER_ID = null ,
@IP_IDENTITY_SCID SUB_SEQUENCE = null ,
@IP_IDENTITY_IP_ADDR VARCHAR(60) = null ,
@IP_START_INDEX INT = null ,
@IP_PAGE_SIZE INT = null ,
@IP_LATITUDE decimal(18,15) = null ,
@IP_LONGITUDE decimal(18,15) = null ,
@IP_SEARCH_DISTANCE int = null ,
@IP_SEARCH_TEXT FULL_NAME = null ,
@IP_MAX_COUNT_TO_FETCH INT = null ,
@IP_NATIONAL_LEVEL1_CODE Varchar(24) = null ,
@IP_CUSTOMER_CLASS Varchar(24) = null ,
@IP_NAME_CREDENTIALS Varchar(40) = null ,
@IP_COUNTRY Varchar(24) = null ,
@IP_LANGUAGE Varchar(24) = null ,
@IP_ETHNICITY Varchar(24) = null ,
@IP_GENDER Varchar(24) = null ,
@IP_JOB_FUNCTION Varchar(24) = null ,
@IP_SPECIALITY Varchar(24) = null ,
@OP_TOTAL_COUNT INT = null OUTPUT ,
@OP_ERROR_MESSAGE Varchar(100) = null OUTPUT
)
AS
BEGIN
-------- Local Vars
DECLARE @LV_ACTUAL_SEARCH_TERM VARCHAR(255)
DECLARE @LV_Q VARCHAR(10) = '"'
DECLARE @LV_QS VARCHAR(10) = '*"'
DECLARE @LV_PERFORM_LOCATION_SEARCH BIT = 0
DECLARE @LV_CURRENT_LOCATION geography
--------------------------------------
set @OP_ERROR_MESSAGE = ''
IF @SCHEMA_ONLY = 1
BEGIN
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME,'MASTER_CUSTOMER_ID' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME,'SUB_CUSTOMER_ID' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME, 'CUSTOMER_CLASS' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME, 'FIRST_NAME' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME, 'LAST_NAME' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME, 'LABEL_NAME' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME, 'RECORD_TYPE' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME, 'DISPLAY_LINE_1' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME, 'DISPLAY_LINE_2' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME, 'IMAGEID' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME, 'ADDRESS_TAGS' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME, 'DISTANCEINMILES' COLUMN_NAME
return;
END
-- DEV NOTES
-- ISNULL(NULLIF(@IP_SEARCH_TEXT, ''), '')
-- Pending: Name Credentials
---------------------------------------------------*/
-- Format the search text entered by the user
-- <smith> will be formatted at "smith*"
-- <smith va> will be formatted as "smith*" AND "va*"
SELECT @LV_ACTUAL_SEARCH_TERM = RTRIM(LTRIM(@IP_SEARCH_TEXT))
WHILE CHARINDEX(' ', @LV_ACTUAL_SEARCH_TERM ) > 0
BEGIN
SELECT @LV_ACTUAL_SEARCH_TERM = REPLACE(@LV_ACTUAL_SEARCH_TERM , ' ', ' ')
END
select @LV_ACTUAL_SEARCH_TERM = @LV_Q + REPLACE( @LV_ACTUAL_SEARCH_TERM , ' ' ,@LV_QS + ' AND ' + @LV_Q ) + @LV_QS
-------------------------------------------------------------------------------------------------------
-- CHECK IF LOCATION SEARCH HAS TO BE PERFORMED .. HTis will be true if Latitude, Longitude and Search Distance are not null
-------------------------------------------------------------------------------------------------------
set @LV_PERFORM_LOCATION_SEARCH = 0
if (NOT ISNULL(@IP_LATITUDE, 0) = 0 ) AND (NOT ISNULL(@IP_LONGITUDE, 0) = 0 ) AND (NOT ISNULL(@IP_SEARCH_DISTANCE, 0) = 0 )
BEGIN
set @LV_PERFORM_LOCATION_SEARCH = 1
set @LV_CURRENT_LOCATION = geography::Point(@IP_LATITUDE, @IP_LONGITUDE, 4326)
END
--------------------------------------------------------------------------------------------------------
if (ISNULL(@IP_SEARCH_TEXT , '') = '') -----------------------------------------------------------------------------
BEGIN
-- GET the count
select @OP_TOTAL_COUNT = COUNT(*)
from dbo.FTS_CUSTOMER_INFO F
LEFT JOIN CUS_ADDRESS_GEO_LOCATION CAGL ON F.CUS_ADDRESS_ID = CAGL.CUS_ADDRESS_ID
where
--AND F.INCLUDE_IN_WEB_MOBILE_DIRECTORY_FLAG = 1
F.IS_ACTIVE_FLAG = 'Y'
AND (ISNULL( @IP_CUSTOMER_CLASS ,'') = '' OR F.CUSTOMER_CLASS = @IP_CUSTOMER_CLASS)
AND (ISNULL( @IP_COUNTRY ,'') = '' OR F.COUNTRY_CODE = @IP_COUNTRY)
AND (ISNULL( @IP_LANGUAGE ,'') = '' OR F.DEFAULT_LANGUAGE_CODE = @IP_LANGUAGE)
AND (ISNULL( @IP_ETHNICITY ,'') = '' OR F.ETHNICITY_CODE = @IP_ETHNICITY)
AND (ISNULL( @IP_GENDER ,'') = '' OR F.GENDER_CODE = @IP_GENDER)
AND (ISNULL( @IP_JOB_FUNCTION ,'') = '' OR F.JOB_FUNCTION_CODE = @IP_JOB_FUNCTION)
AND (ISNULL( @IP_COUNTRY ,'') = '' OR F.COUNTRY_CODE = @IP_COUNTRY)
AND (ISNULL (@IP_SPECIALITY, '') ='' OR exists ( select 1 from CUS_SPECIALTY CS
where CS.MASTER_CUSTOMER_ID = F.MASTER_CUSTOMER_ID
and CS.SUB_CUSTOMER_ID = F.SUB_CUSTOMER_ID
and exists (select 1 from
udf_SplitString(@IP_SPECIALITY, ',') CUS_SP_TBL
where CS.CUS_SPECIALTY_CODE = CUS_SP_TBL.STRING_PARTS)))
-- Uncomment when there is valid data
AND ( exists ( select 1 from MBR_DIRECTORY_MEMBERSHIP MDM
where MDM.MASTER_CUSTOMER_ID = F.MASTER_CUSTOMER_ID
and MDM.SUB_CUSTOMER_ID = F.SUB_CUSTOMER_ID
AND ISNULL (@IP_NATIONAL_LEVEL1_CODE, '') ='' OR MDM.LEVEL1 = @IP_NATIONAL_LEVEL1_CODE))
AND ((@LV_PERFORM_LOCATION_SEARCH = 0) OR ( (@LV_CURRENT_LOCATION.STDistance(CAGL.GEO_LOCATION) * 0.000621371192) < @IP_SEARCH_DISTANCE ))
If (@OP_TOTAL_COUNT > @IP_MAX_COUNT_TO_FETCH)
BEGIN
set @OP_ERROR_MESSAGE = 'Count Exceeded'
--select @OP_ERROR_MESSAGE
return
END
-- GET THE DATA
select
F.MASTER_CUSTOMER_ID
, F.SUB_CUSTOMER_ID
, F.CUSTOMER_CLASS
, F.FIRST_NAME
, F.LAST_NAME
, F.LABEL_NAME
, F.RECORD_TYPE
, F.DISPLAY_LINE_1
, F.DISPLAY_LINE_2
, F.IMAGEID
, ADDRESS_TAGS
, CASE @LV_PERFORM_LOCATION_SEARCH
WHEN 1 THEN
ROUND (@LV_CURRENT_LOCATION.STDistance(CAGL.GEO_LOCATION) * 0.000621371192 ,2)
ELSE
0
END As DISTANCEINMILES
, @LV_CURRENT_LOCATION.STDistance(CAGL.GEO_LOCATION) * 0.000621371192 as NEW
from dbo.FTS_CUSTOMER_INFO F
LEFT JOIN CUS_ADDRESS_GEO_LOCATION CAGL ON F.CUS_ADDRESS_ID = CAGL.CUS_ADDRESS_ID
where
--AND F.INCLUDE_IN_WEB_MOBILE_DIRECTORY_FLAG = 1
F.IS_ACTIVE_FLAG = 'Y'
AND (ISNULL( @IP_CUSTOMER_CLASS ,'') = '' OR F.CUSTOMER_CLASS = @IP_CUSTOMER_CLASS)
AND (ISNULL( @IP_COUNTRY ,'') = '' OR F.COUNTRY_CODE = @IP_COUNTRY)
AND (ISNULL( @IP_LANGUAGE ,'') = '' OR F.DEFAULT_LANGUAGE_CODE = @IP_LANGUAGE)
AND (ISNULL( @IP_ETHNICITY ,'') = '' OR F.ETHNICITY_CODE = @IP_ETHNICITY)
AND (ISNULL( @IP_GENDER ,'') = '' OR F.GENDER_CODE = @IP_GENDER)
AND (ISNULL( @IP_JOB_FUNCTION ,'') = '' OR F.JOB_FUNCTION_CODE = @IP_JOB_FUNCTION)
AND (ISNULL( @IP_COUNTRY ,'') = '' OR F.COUNTRY_CODE = @IP_COUNTRY)
AND (ISNULL (@IP_SPECIALITY, '') ='' OR exists ( select 1 from CUS_SPECIALTY CS
where CS.MASTER_CUSTOMER_ID = F.MASTER_CUSTOMER_ID
and CS.SUB_CUSTOMER_ID = F.SUB_CUSTOMER_ID
and exists (select 1 from
udf_SplitString(@IP_SPECIALITY, ',') CUS_SP_TBL
where CS.CUS_SPECIALTY_CODE = CUS_SP_TBL.STRING_PARTS)))
-- Uncomment when there is valid data
AND ( exists ( select 1 from MBR_DIRECTORY_MEMBERSHIP MDM
where MDM.MASTER_CUSTOMER_ID = F.MASTER_CUSTOMER_ID
and MDM.SUB_CUSTOMER_ID = F.SUB_CUSTOMER_ID
AND ISNULL (@IP_NATIONAL_LEVEL1_CODE, '') ='' OR MDM.LEVEL1 = @IP_NATIONAL_LEVEL1_CODE))
AND ((@LV_PERFORM_LOCATION_SEARCH = 0) OR ( (@LV_CURRENT_LOCATION.STDistance(CAGL.GEO_LOCATION) * 0.000621371192) < @IP_SEARCH_DISTANCE ))
ORDER BY F.LABEL_NAME
OFFSET @IP_START_INDEX ROWS
FETCH NEXT @IP_PAGE_SIZE ROWS ONLY
END
ELSE
BEGIN
-- GET the count
select @OP_TOTAL_COUNT = COUNT(*)
from dbo.FTS_CUSTOMER_INFO F
LEFT JOIN CUS_ADDRESS_GEO_LOCATION CAGL ON F.CUS_ADDRESS_ID = CAGL.CUS_ADDRESS_ID
where ( contains ( F.SEARCH_TAGS, @LV_ACTUAL_SEARCH_TERM) )
--AND F.INCLUDE_IN_WEB_MOBILE_DIRECTORY_FLAG = 1
AND F.IS_ACTIVE_FLAG = 'Y'
AND (ISNULL( @IP_CUSTOMER_CLASS ,'') = '' OR F.CUSTOMER_CLASS = @IP_CUSTOMER_CLASS)
AND (ISNULL( @IP_COUNTRY ,'') = '' OR F.COUNTRY_CODE = @IP_COUNTRY)
AND (ISNULL( @IP_LANGUAGE ,'') = '' OR F.DEFAULT_LANGUAGE_CODE = @IP_LANGUAGE)
AND (ISNULL( @IP_ETHNICITY ,'') = '' OR F.ETHNICITY_CODE = @IP_ETHNICITY)
AND (ISNULL( @IP_GENDER ,'') = '' OR F.GENDER_CODE = @IP_GENDER)
AND (ISNULL( @IP_JOB_FUNCTION ,'') = '' OR F.JOB_FUNCTION_CODE = @IP_JOB_FUNCTION)
AND (ISNULL( @IP_COUNTRY ,'') = '' OR F.COUNTRY_CODE = @IP_COUNTRY)
AND (ISNULL (@IP_SPECIALITY, '') ='' OR exists ( select 1 from CUS_SPECIALTY CS
where CS.MASTER_CUSTOMER_ID = F.MASTER_CUSTOMER_ID
and CS.SUB_CUSTOMER_ID = F.SUB_CUSTOMER_ID
and exists (select 1 from
udf_SplitString(@IP_SPECIALITY, ',') CUS_SP_TBL
where CS.CUS_SPECIALTY_CODE = CUS_SP_TBL.STRING_PARTS)))
-- Uncomment when there is valid data
AND ( exists ( select 1 from MBR_DIRECTORY_MEMBERSHIP MDM
where MDM.MASTER_CUSTOMER_ID = F.MASTER_CUSTOMER_ID
and MDM.SUB_CUSTOMER_ID = F.SUB_CUSTOMER_ID
AND ISNULL (@IP_NATIONAL_LEVEL1_CODE, '') ='' OR MDM.LEVEL1 = @IP_NATIONAL_LEVEL1_CODE))
AND ((@LV_PERFORM_LOCATION_SEARCH = 0) OR ( (@LV_CURRENT_LOCATION.STDistance(CAGL.GEO_LOCATION) * 0.000621371192) < @IP_SEARCH_DISTANCE ))
If (@OP_TOTAL_COUNT > @IP_MAX_COUNT_TO_FETCH)
BEGIN
set @OP_ERROR_MESSAGE = 'Count Exceeded'
--select @OP_ERROR_MESSAGE
return
END
-- GET THE DATA
select
F.MASTER_CUSTOMER_ID
, F.SUB_CUSTOMER_ID
, F.CUSTOMER_CLASS
, F.FIRST_NAME
, F.LAST_NAME
, F.LABEL_NAME
, F.RECORD_TYPE
, F.DISPLAY_LINE_1
, F.DISPLAY_LINE_2
, F.IMAGEID
, ADDRESS_TAGS
, CASE @LV_PERFORM_LOCATION_SEARCH
WHEN 1 THEN
ROUND (@LV_CURRENT_LOCATION.STDistance(CAGL.GEO_LOCATION) * 0.000621371192 ,2)
ELSE
0
END As DISTANCEINMILES
, @LV_CURRENT_LOCATION.STDistance(CAGL.GEO_LOCATION) * 0.000621371192 as NEW
from dbo.FTS_CUSTOMER_INFO F
LEFT JOIN CUS_ADDRESS_GEO_LOCATION CAGL ON F.CUS_ADDRESS_ID = CAGL.CUS_ADDRESS_ID
where (contains ( F.SEARCH_TAGS, @LV_ACTUAL_SEARCH_TERM) )
--AND F.INCLUDE_IN_WEB_MOBILE_DIRECTORY_FLAG = 1
AND F.IS_ACTIVE_FLAG = 'Y'
AND (ISNULL( @IP_CUSTOMER_CLASS ,'') = '' OR F.CUSTOMER_CLASS = @IP_CUSTOMER_CLASS)
AND (ISNULL( @IP_COUNTRY ,'') = '' OR F.COUNTRY_CODE = @IP_COUNTRY)
AND (ISNULL( @IP_LANGUAGE ,'') = '' OR F.DEFAULT_LANGUAGE_CODE = @IP_LANGUAGE)
AND (ISNULL( @IP_ETHNICITY ,'') = '' OR F.ETHNICITY_CODE = @IP_ETHNICITY)
AND (ISNULL( @IP_GENDER ,'') = '' OR F.GENDER_CODE = @IP_GENDER)
AND (ISNULL( @IP_JOB_FUNCTION ,'') = '' OR F.JOB_FUNCTION_CODE = @IP_JOB_FUNCTION)
AND (ISNULL( @IP_COUNTRY ,'') = '' OR F.COUNTRY_CODE = @IP_COUNTRY)
AND (ISNULL (@IP_SPECIALITY, '') ='' OR exists ( select 1 from CUS_SPECIALTY CS
where CS.MASTER_CUSTOMER_ID = F.MASTER_CUSTOMER_ID
and CS.SUB_CUSTOMER_ID = F.SUB_CUSTOMER_ID
and exists (select 1 from
udf_SplitString(@IP_SPECIALITY, ',') CUS_SP_TBL
where CS.CUS_SPECIALTY_CODE = CUS_SP_TBL.STRING_PARTS)))
-- Uncomment when there is valid data
AND ( exists ( select 1 from MBR_DIRECTORY_MEMBERSHIP MDM
where MDM.MASTER_CUSTOMER_ID = F.MASTER_CUSTOMER_ID
and MDM.SUB_CUSTOMER_ID = F.SUB_CUSTOMER_ID
AND ISNULL (@IP_NATIONAL_LEVEL1_CODE, '') ='' OR MDM.LEVEL1 = @IP_NATIONAL_LEVEL1_CODE))
AND ((@LV_PERFORM_LOCATION_SEARCH = 0) OR ( (@LV_CURRENT_LOCATION.STDistance(CAGL.GEO_LOCATION) * 0.000621371192) < @IP_SEARCH_DISTANCE ))
ORDER BY F.LABEL_NAME
OFFSET @IP_START_INDEX ROWS
FETCH NEXT @IP_PAGE_SIZE ROWS ONLY
END
END
GO