/****** Object: StoredProcedure [dbo].[usp_MEMBERDIRECTORY_DETAIL_SEARCH_NATIONAL] Script Date: 12/4/2014 2:17:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF EXISTS
(
Select 1
From INFORMATION_SCHEMA.ROUTINES
Where ROUTINE_NAME = 'usp_MEMBERDIRECTORY_DETAIL_SEARCH_NATIONAL'
And ROUTINE_TYPE = 'PROCEDURE'
)
Begin
DROP PROCEDURE [dbo].[usp_MEMBERDIRECTORY_DETAIL_SEARCH_NATIONAL]
End
GO
CREATE PROCEDURE [dbo].[usp_MEMBERDIRECTORY_DETAIL_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_RECORD_TYPE VARCHAR(10) = null
)
AS
BEGIN
-- START: THIS IS IMPORTANT AND NEED TO WRITE SCHEMA
IF @SCHEMA_ONLY = 1
BEGIN
SELECT 'FTS_CUSTOMER_INFO' TABLE_NAME, 'IMAGEID' COLUMN_NAME
UNION ALL
SELECT 'FTS_CUSTOMER_INFO' TABLE_NAME, 'LABEL_NAME' COLUMN_NAME
UNION ALL
SELECT 'FTS_CUSTOMER_INFO' TABLE_NAME, 'PRIMARY_JOB_TITLE' COLUMN_NAME
UNION ALL
SELECT 'FTS_CUSTOMER_INFO' TABLE_NAME, 'EMP_NAME' COLUMN_NAME
UNION ALL
SELECT 'FTS_CUSTOMER_INFO' TABLE_NAME, 'ADDR_INFO' COLUMN_NAME
UNION ALL
SELECT 'FTS_CUSTOMER_INFO' TABLE_NAME, 'CONTACT_LABEL_NAME' COLUMN_NAME
UNION ALL
SELECT 'CUS_ADDRESS_VW' TABLE_NAME, 'FORMATTED_DETAIL' COLUMN_NAME
UNION ALL
SELECT 'CUS_ADDRESS_VW' TABLE_NAME, 'FORMATTED_ADDRESS' COLUMN_NAME
UNION ALL
SELECT 'CUS_COMMUNICATION' TABLE_NAME, 'FORMATTED_HTML_RECORD' COLUMN_NAME
UNION ALL
SELECT 'CUS_EDUCATION' TABLE_NAME, 'EDUCATION' COLUMN_NAME
UNION ALL
SELECT 'CUS_BIOGRAPHY' TABLE_NAME, 'BIOGRAPHY_TEXT' COLUMN_NAME
UNION ALL
SELECT 'CUS_SPECIALTY' TABLE_NAME, 'CUS_SPECIALTY_CODE' COLUMN_NAME
UNION ALL
SELECT 'CUS_PRODUCT' TABLE_NAME, 'PRODUCT_BRAND_NAME' COLUMN_NAME
UNION ALL
SELECT 'CUS_RELATED_EMPLOYEES' TABLE_NAME, 'RECIPROCAL_CODE' COLUMN_NAME
UNION ALL
SELECT 'CUS_RELATED_EMPLOYEES' TABLE_NAME, 'RELATED_NAME' COLUMN_NAME
UNION ALL
SELECT 'CUS_RELATED_SUBSIDIARIES' TABLE_NAME, 'RECIPROCAL_CODE' COLUMN_NAME
UNION ALL
SELECT 'CUS_RELATED_SUBSIDIARIES' TABLE_NAME, 'RELATED_NAME' COLUMN_NAME
return;
END
-- END: THIS IS IMPORTANT AND NEED TO WRITE SCHEMA
select F.IMAGEID,F.LABEL_NAME, F.PRIMARY_JOB_TITLE , F.DISPLAY_LINE_2 as EMP_NAME,
F.DISPLAY_LINE_1 + '|' + F.COUNTRY_CODE AS ADDR_INFO , CONTACT_LABEL_NAME
from FTS_CUSTOMER_INFO F with (nolock)
where F.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and F.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
select CA.FORMATTED_DETAIL, CA.FORMATTED_ADDRESS
from CUS_ADDRESS_VW CA with (nolock)
where CA.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and CA.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
and CA.CONFIDENTIAL_FLAG = 'N'
and CA.WEB_MOBILE_DIRECTORY_FLAG = 'Y'
order by CA.DIRECTORY_PRIORITY
-- For fetch communication records
declare @lv_htmlEmail VARCHAR(500)
declare @lv_htmlPhone VARCHAR(500)
declare @lv_htmlFacebook VARCHAR(500)
declare @lv_htmlLinkedIn VARCHAR(500)
declare @lv_htmlPinterest VARCHAR(500)
declare @lv_htmlSocial VARCHAR(500)
-- Set to change the image URL
declare @lv_imageURL VARCHAR(50) = '../images/SocialShare/'
-- Icon names for varios records
declare @lv_EmailIconName Varchar(50) = 'mailto16.png'
declare @lv_PhoneIconName Varchar(50) = 'phone16.png'
declare @lv_FacebookIconName Varchar(50) = 'facebook16.png'
declare @lv_LinkedInIconName Varchar(50) = 'twitter16.png'
declare @lv_PinterestIconName Varchar(50) = 'pinterest16.png'
--
declare @lv_BlankIconName Varchar(50) = 'blank16.png'
-- Style for all images and hyper links
declare @lv_AnchorCssClass Varchar(50) = 'membership-dir-detail-link'
declare @lv_ImageCssClass Varchar(50) = 'membership-dir-detail-contact-icon'
-- HTML for EMAIL, PHONE and other web sites (Social or Web)
set @lv_htmlEmail = '<a class="{AnchorCssClass}" href="mailto:{formatted_text}"><img class="{ImageCssClass}" src="{Image_URL}{Image_Name}" alt="{AltText}" " >{formatted_text}</a>'
set @lv_htmlPhone = '<a class="{AnchorCssClass}" ><img class="{ImageCssClass}" src="{Image_URL}{Image_Name}" alt="Phone" >{formatted_text}</a>'
set @lv_htmlSocial = '<a class="{AnchorCssClass}" href="{formatted_text}" target=_Blank><img class="{ImageCssClass}" src="{Image_URL}{Image_Name}" alt="{AltText}" >{formatted_text}</a>'
select
case A.OPTION_1
When 'PHONE' then
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(@lv_htmlPhone ,
'{formatted_text}' , C.FORMATTED_PHONE_ADDRESS ),
'{Image_URL}', @lv_imageURL),
'{Image_Name}',@lv_PhoneIconName),
'{AltText}','Phone'),
'{AnchorCssClass}',@lv_AnchorCssClass),
'{ImageCssClass}',@lv_ImageCssClass)
When 'WEB' then
case A.OPTION_2
When 'EMAIL' then
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(@lv_htmlEmail ,
'{formatted_text}' , C.FORMATTED_PHONE_ADDRESS ),
'{Image_URL}', @lv_imageURL),
'{Image_Name}',@lv_EmailIconName),
'{AltText}','Send Email'),
'{AnchorCssClass}',@lv_AnchorCssClass),
'{ImageCssClass}',@lv_ImageCssClass)
Else
Case C.COMM_LOCATION_CODE
When 'LINKEDIN' then
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(@lv_htmlSocial ,
'{formatted_text}' , C.FORMATTED_PHONE_ADDRESS ),
'{Image_URL}', @lv_imageURL),
'{Image_Name}',@lv_LinkedInIconName),
'{AltText}','Linked In'),
'{AnchorCssClass}',@lv_AnchorCssClass),
'{ImageCssClass}',@lv_ImageCssClass)
When 'FACEBOOK' then
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(@lv_htmlSocial ,
'{formatted_text}' , C.FORMATTED_PHONE_ADDRESS ),
'{Image_URL}', @lv_imageURL),
'{Image_Name}',@lv_FacebookIconName),
'{AltText}','Go to Facebook'),
'{AnchorCssClass}',@lv_AnchorCssClass),
'{ImageCssClass}',@lv_ImageCssClass)
Else -- Blank Icon
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(@lv_htmlSocial ,
'{formatted_text}' , C.FORMATTED_PHONE_ADDRESS ),
'{Image_URL}', @lv_imageURL),
'{Image_Name}',@lv_BlankIconName),
'{AltText}','Go to Web Site'),
'{AnchorCssClass}',@lv_AnchorCssClass),
'{ImageCssClass}',@lv_ImageCssClass)
End
end
End as FORMATTED_HTML_RECORD
from CUS_COMMUNICATION C
inner join APP_CODE A on A.SUBSYSTEM = 'CUS' and A.TYPE = 'COMM_TYPE' and A.CODE = C.COMM_TYPE_CODE
where C.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and C.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
and C.WEB_MOBILE_DIRECTORY_FLAG = 'Y'
---------------------------------------------------
Select CE.PROG_DEGREE_CODE + ', ' + CE.INSTITUTION_NAME as EDUCATION
From CUS_EDUCATION CE with (nolock)
where CE.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and CE.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
Select CB.BIOGRAPHY_TEXT
From CUS_BIOGRAPHY CB with (nolock)
where CB.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and CB.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
--and @IP_RECORD_TYPE = 'I'
Select AC.DESCR as CUS_SPECIALTY_CODE
From CUS_SPECIALTY CS with (nolock)
inner join APP_CODE AC on AC.SUBSYSTEM = 'CUS' and AC.TYPE = 'CUS_SPECIALTY' and CS.CUS_SPECIALTY_CODE = AC.CODE
where CS.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and CS.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
and @IP_RECORD_TYPE = 'I'
Select CP.PRODUCT_BRAND_NAME
From CUS_PRODUCT CP with (nolock)
where CP.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and CP.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
--and @IP_RECORD_TYPE = 'C'
--related staff
select CR.RECIPROCAL_CODE, CR.RELATED_NAME from CUS_RELATIONSHIP CR
Where CR.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and CR.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
and CR.RELATIONSHIP_TYPE = 'EMPLOYMENT'
and CR.ACTIVE_FLAG = 'Y'
and @IP_RECORD_TYPE = 'C'
order by CR.RELATED_NAME
-- subsidiaries
select CR.RECIPROCAL_CODE , CR.RELATED_NAME from CUS_RELATIONSHIP CR
Where CR.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and CR.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
and CR.RELATIONSHIP_TYPE = 'CORPORATE'
and CR.ACTIVE_FLAG = 'Y'
and @IP_RECORD_TYPE = 'C'
order by CR.RELATED_NAME
END
GO