SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================

ALTER PROCEDURE OES_SP_GetOptionsForFieldsWithListControl
(
    @PageID     INT, 
    @EmployeeID BIGINT,                 
    @LanguageID INT, 
    @ROWID      BIGINT,
    @ColumnList VARCHAR(MAX)
)
AS
BEGIN

    --DECLARE @PageID INT
    --DECLARE @EmployeeID BIGINT
    --DECLARE @ColumnList VARCHAR(MAX)
    --DECLARE @LanguageID INT
    --DECLARE @ROWID BIGINT
    
    --SET @PageID = 2
    --SET @EmployeeID = 29700
    --SET @ColumnList = 'TitleID,MaritalStatusID,UseEmployerAddress,ReceiveSMS,ReceiveEmail,GenderID,CountryID,IsUnionMember,LanguageIndicator,EmployeeTypeID,StateID'
    --SET @LanguageID = 1
    --SET @ROWID = 0
    
    DECLARE @ColumnName VARCHAR(500)='';    
    DECLARE @Options AS VARCHAR(MAX);
    
    CREATE TABLE #ListOptionsForColumns
    (    
	   ID INT IDENTITY,
	   ColumnName VARCHAR(500),
	   UDFName VARCHAR(500),
	   Options  VARCHAR(MAX)
    )
         
    CREATE TABLE #OptsList (
	   Name NVARCHAR(1000)
    );
    
    DECLARE @dynaQuery AS NVARCHAR(1500);
    DECLARE @dynaSubQuery AS VARCHAR(500);
    DECLARE @sOptions AS VARCHAR(200);
    DECLARE @Countryid AS BIGINT;
    DECLARE @DomesticPartnerAllowed AS INT;
    DECLARE @AllowCommonLawSpouse AS INT;    
    DECLARE @cnt INT = 1
    DECLARE @rowCnt INT 
    
    INSERT INTO #ListOptionsForColumns (ColumnName, UDFName)
    SELECT Items, UDFName 
    FROM Split(@ColumnList, ',') T
    INNER JOIN Fields ON Fields.DestinationColumnName = T.Items AND PageID = @PageID
    
    SET @rowCnt = (SELECT COUNT(ID) FROM #ListOptionsForColumns)
    
    WHILE(@cnt <= @rowCnt)
    BEGIN		

	   SELECT @sOptions = UDFName, @ColumnName = ColumnName, @Options = Options
	   FROM #ListOptionsForColumns WHERE ID = @cnt 	     		  	 

	   IF @Options IS NULL
	   BEGIN
		  IF @sOptions = 'Country' OR @sOptions = 'PTOCategory' --or   @sOptions = 'Country'  
			 SET @dynaSubQuery = 'select Cast(ID as nvarchar(10)) + ''~'' + Name as Name from '+@sOptions;
		  ELSE
		  IF @sOptions = 'OES_UDF_Get_EmployeeSupervisor()'
			 SET @dynaSubQuery = 'Select Cast(ID as nvarchar(10)) + ''~'' + FirstName from Employee where IsEmployeeSupervisor=1';
		  ELSE
		  IF @sOptions = 'PayrollSchedule' AND @PageID = 7
			 SET @dynaSubQuery = 'select Cast(ID as nvarchar(10)) + ''~'' + Name as Name from '+@sOptions;
		  ELSE
		  IF(@sOptions = 'SpouseStatus' AND @PageID = 8) OR (@sOptions = 'ChildStatus' AND @PageID = 9)
		  BEGIN
			 DECLARE @StatusQuery NVARCHAR(200);
			 IF @PageID = 8
				SET @StatusQuery = ' where ID NOT IN (0,7) Order By Name1 ASC';
			 ELSE
				SET @StatusQuery = ' where ID NOT IN (0,6)  Order By Name1 ASC';
			 SET @dynaSubQuery = 'select  Cast(ID as nvarchar(10)) + ''~'' + Name'+CAST(@LanguageID AS NVARCHAR(5))+' as Name'+CAST(@LanguageID AS VARCHAR(5))+' from '+@sOptions+@StatusQuery;
		  END;
		  ELSE
		  IF @sOptions = 'State'
		  BEGIN
			 IF @EmployeeID = 0
				SET @Countryid = 1;
			 ELSE
				BEGIN
				    IF @PageID = 5
					   BEGIN
						  IF @ColumnName = 'StateID'
						  BEGIN
							 IF EXISTS (SELECT CountryID FROM Employee_Emergency WHERE EmployeeID = @EmployeeID)
								SELECT @Countryid = ISNULL(CountryID, 1)
								FROM Employee_Emergency
								WHERE EmployeeID = @EmployeeID;
							 ELSE
								SET @Countryid = 1;
						  END;
						  ELSE
						  BEGIN
						  IF EXISTS (SELECT SecondaryCountryID FROM Employee_Emergency WHERE EmployeeID = @EmployeeID )
							 SELECT @Countryid = ISNULL(SecondaryCountryID, 1)
							 FROM Employee_Emergency
							 WHERE EmployeeID = @EmployeeID;
						  ELSE
							 SET @Countryid = 1;
						  END;
					   END;
				    ELSE
				    IF @PageID = 8 AND @ROWID <> 0
					   SELECT @Countryid = ISNULL(Countryid, 1)
					   FROM Employee_DependentSpouse
					   WHERE Id = @ROWID;
				    ELSE
				    IF @PageID = 9 AND @ROWID <> 0
					   SELECT @Countryid = ISNULL(Countryid, 1)
					   FROM Employee_DependentChild
					   WHERE Id = @ROWID;
				    ELSE
					   SELECT @Countryid = ISNULL(CountryID, 1)
					   FROM Employee
					   WHERE ID = @EmployeeID;
				END;
				SET @dynaSubQuery = 'select Cast(ID as nvarchar(10)) + ''~'' + Name as Name from '+@sOptions+' where CountryID In (0,'+CAST(@Countryid AS NVARCHAR(5))+')';
		  END;
		  ELSE
		  IF @sOptions = 'Class1' OR @sOptions = 'Class2' OR @sOptions = 'Class3' OR @sOptions = 'Class4' 
		  BEGIN            
			 SET @dynaSubQuery = 'select Cast(ID as nvarchar(10)) + ''~'' + '+@sOptions+'Name as Name from CompanyClass ;Insert into #OptsList select  Cast(ID as nvarchar(10)) + ''~'' + Name from '+@sOptions;            
		  END;
		  ELSE
		  IF @sOptions = 'SpouseRelationship'
		  BEGIN
			 SELECT @DomesticPartnerAllowed = CASE
				WHEN IsDomesticPartnerAllowed = 1 THEN-1
				WHEN IsDomesticPartnerAllowed = 0 THEN 3 END,
			 @AllowCommonLawSpouse = CASE
				WHEN AllowCommonLawSpouse = 1 THEN-1
				WHEN AllowCommonLawSpouse = 0 THEN 4 END
			 FROM CompanyInfo;
				 SET @dynaSubQuery = 'select  Cast(ID as nvarchar(10)) + ''~'' + Name'+CAST(@LanguageID AS NVARCHAR(5))+' as Name from '+@sOptions+'  where ID NOT IN( '+CAST(@DomesticPartnerAllowed AS VARCHAR(5))+','+CAST(@AllowCommonLawSpouse AS VARCHAR(5))+')';
		  END;
		  ELSE
		  IF @sOptions = 'ChildRelationship'
		  BEGIN
			 SET @dynaSubQuery = 'select  Cast(ID as nvarchar(10)) + ''~'' + Name'+CAST(@LanguageID AS NVARCHAR(5))+' as Name from '+@sOptions+'  where ID  NOT IN (1,2)';
		  END;
		  ELSE
			 SET @dynaSubQuery = 'select  Cast(ID as nvarchar(10)) + ''~'' + Name'+CAST(@LanguageID AS NVARCHAR(5))+' as Name'+CAST(@LanguageID AS VARCHAR(5))+' from '+@sOptions;
                 
		  SET @dynaQuery = 'Insert into #OptsList '+@dynaSubQuery;  
                   
		  EXEC sp_executesql @dynaQuery;  
		  -- Split list into CSV  
		  SET @Options =
			  (
				 SELECT DISTINCT 
				  STUFF(
					 (
							SELECT '^'+t.Name
							FROM #OptsList t
							WHERE t.Name = Name
							FOR XML PATH('')
					 ), 1, 1, '') AS userlist
				 FROM #OptsList
			  );
              
		  DELETE FROM #OptsList
    	   		                             		  
		  IF @ColumnName = 'PayrollScheduleID' AND @PageID = 7
			 SET @Options = '0~--- Select Payroll Schedule ---^'+@Options;		  		  

		  IF @sOptions = 'State'
			 UPDATE #ListOptionsForColumns
			 SET Options = @Options       
			 WHERE UDFName = @sOptions AND ColumnName = @ColumnName
		  ELSE
			 UPDATE #ListOptionsForColumns
			 SET Options = @Options       
			 WHERE UDFName = @sOptions
    	   END
    	   SET @cnt = @cnt + 1
    END
  
 
    SELECT ColumnName, Options FROM #ListOptionsForColumns
    DROP TABLE #OptsList
    DROP TABLE #ListOptionsForColumns
END;