USE [LegacyShieldStagingDB_New]
GO
/****** Object:  StoredProcedure [dbo].[sp_Get_Client_List_By_ID]    Script Date: 27.11.2015 20:31:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_Get_Client_List_By_ID]
    -- Add the parameters for the stored procedure here
    @Client_ID nvarchar(MAX) = null
AS

BEGIN
    SELECT  [tbl_User_Master].[User_ID]
    ,[tbl_User_Master].[AdvisorID]
        ,[tbl_User_Master].[FName]
        ,[tbl_User_Master].[LName]
        ,[tbl_User_Master].[Email]
        ,[tbl_User_Master].[UserName]
        ,[tbl_User_Master].[DOB]
        ,[tbl_User_Master].[Phone_Work]
        ,[tbl_User_Master].[Address1]
        ,[tbl_User_Master].[Address2]
        ,[tbl_User_Master].[City]
        ,[tbl_User_Master].[StateID]
        ,[tbl_User_Master].[CountryID]
        ,[tbl_User_Master].[Zip]
        ,[tbl_User_Master].[Phone_Home]
        ,[tbl_User_Master].[Fax]
        ,[tbl_User_Master].[Mobile]
        ,[tbl_User_Master].[Security_Question_ID]
        ,[tbl_User_Master].[Security_Question_Ans]
        ,[tbl_User_Master].[Created_Date]
        ,[tbl_User_Master].[Modified_By]
        ,[tbl_User_Master].[Modified_Date]
        ,[tbl_User_Master].[PhotoName]
        ,[tbl_User_Master].[PhotoPath]
        ,[tbl_User_Master].[PhotoNewName]
        ,[tbl_User_Master].[Valid_Upto]
        ,[tbl_User_Master].[IsSubscriptionAgreement]
        ,[tbl_User_Master].[IsMailReceived]
        ,[tbl_User_Master].[IsSponsorsMailReceived]
        ,[tbl_User_Master].[Suffix]
        ,[tbl_User_Master].[Gender]
        --,[tbl_Advisor_Client_Map].[UserID]
        ,[tbl_Advisor_User_Master].[AdvisorUser_ID],
        --,[tbl_WFG_Advisor_Details].[WFG_Producer_Number]

        CASE

        WHEN tbl_User_Master.[AdvisorID] IS NOT NULL THEN [tbl_User_Master].[User_ID]
        WHEN  [tbl_Advisor_Client_Map].[UserID] =[tbl_User_Master].[User_ID]
             THEN [tbl_Advisor_User_Master].[AdvisorUser_ID]
        END

FROM tbl_User_Master
LEFT JOIN  tbl_Advisor_Client_Map
ON tbl_User_Master.User_ID = tbl_Advisor_Client_Map.UserID
LEFT OUTER JOIN tbl_Advisor_User_Master
ON tbl_User_Master.User_ID =
tbl_Advisor_User_Master.AdvisorUser_ID

WHERE

        [tbl_User_Master].[User_ID] = @Client_ID





ORDER BY
        [tbl_User_Master].[Created_Date] ASC

END