USE [BEA]
GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_bezeichnung]    Script Date: 13.12.2014 15:01:34 ******/
DROP FUNCTION [dbo].[fnkt_get_bezeichnung]
GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_bezeichnung_old]    Script Date: 13.12.2014 15:01:34 ******/
DROP FUNCTION [dbo].[fnkt_get_bezeichnung_old]
GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_beziehung_sql]    Script Date: 13.12.2014 15:01:34 ******/
DROP FUNCTION [dbo].[fnkt_get_beziehung_sql]
GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_beziehung_sql_old]    Script Date: 13.12.2014 15:01:34 ******/
DROP FUNCTION [dbo].[fnkt_get_beziehung_sql_old]
GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_temporaer]    Script Date: 13.12.2014 15:01:34 ******/
DROP FUNCTION [dbo].[fnkt_get_temporaer]
GO

/****** Object:  UserDefinedFunction [dbo].[Get_Dateperiode]    Script Date: 13.12.2014 15:01:34 ******/
DROP FUNCTION [dbo].[Get_Dateperiode]
GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_personalcid]    Script Date: 13.12.2014 15:01:34 ******/
DROP FUNCTION [dbo].[fnkt_get_personalcid]
GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_parent]    Script Date: 13.12.2014 15:01:34 ******/
DROP FUNCTION [dbo].[fnkt_get_parent]
GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_parent]    Script Date: 13.12.2014 15:01:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE function [dbo].[fnkt_get_parent]
(
       -- Add the parameters for the function here
       @key VARCHAR(255),
       @typ VARCHAR(255)
)
RETURNS VARCHAR (255)
AS
BEGIN

    DECLARE @p AS INTEGER
    DECLARE @ret varchar(255)  
       
    IF @typ='Plattform'  BEGIN
       SELECT @p=ISNULL(plattformnr,0) FROM dbo.Plattform WHERE bezeichnung=@key
          IF @p=0 OR @@ROWCOUNT=0 BEGIN
          SET @ret=''
               RETURN @ret
          end     
       SELECT @p=parentid FROM plattform WHERE plattformnr=@p
          SELECT @ret = bezeichnung FROM plattform WHERE plattformnr=@p
       END
    RETURN @ret  

END



GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_personalcid]    Script Date: 13.12.2014 15:01:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnkt_get_personalcid]
(
   @funktionstellenr int

)
RETURNS int
AS
BEGIN
DECLARE @res INT
declare @res1 int
set @res=0
set @res1=0
SELECT @res=COUNT(*) from
            dbo.Funktionstelle INNER JOIN
                         dbo.Personal_FunktionStelle ON dbo.Funktionstelle.FunktionstelleNr = dbo.Personal_FunktionStelle.FunktionStelleNr INNER JOIN
                         dbo.Personal ON dbo.Personal_FunktionStelle.PersonalNr = dbo.Personal.Personalnr INNER JOIN
                         dbo.Funktionstelle_Berechtigung ON dbo.Funktionstelle.FunktionstelleNr = dbo.Funktionstelle_Berechtigung.FunktionstelleNr INNER JOIN
                         dbo.Berechtigung ON dbo.Funktionstelle_Berechtigung.BerechtigungNr = dbo.Berechtigung.BerechtigungNr
WHERE        (dbo.Funktionstelle_Berechtigung.FunktionstelleNr = @funktionstellenr) AND (dbo.Berechtigung.CID = 1)

SELECT @res=COUNT(*) from
            dbo.Funktionstelle INNER JOIN
                         dbo.Personal_FunktionStelle ON dbo.Funktionstelle.FunktionstelleNr = dbo.Personal_FunktionStelle.FunktionStelleNr INNER JOIN
                         dbo.Personal ON dbo.Personal_FunktionStelle.PersonalNr = dbo.Personal.Personalnr INNER JOIN
                         dbo.Funktionstelle_Plattform ON dbo.Funktionstelle.FunktionstelleNr = dbo.Funktionstelle_Plattform.FunktionstelleNr INNER JOIN
                         dbo.Plattform ON dbo.Funktionstelle_Plattform.PlattformNr = dbo.Plattform.PlattformNr
WHERE        (dbo.Funktionstelle_Plattform.FunktionstelleNr = @funktionstellenr) AND (dbo.Plattform.CID = 1)

set @res=@res+@res1
if @res>0 set @res=1 else set @res=0

RETURN @res
END

GO

/****** Object:  UserDefinedFunction [dbo].[Get_Dateperiode]    Script Date: 13.12.2014 15:01:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[Get_Dateperiode] 
(
   @type varchar(255),
   @startperiode int
)
Returns datetime
AS
BEGIN
   declare @dt datetime
   declare @offset int
   if @type='ActWeek' begin
      if @startperiode=1 begin
         set @dt=DATEADD(wk, DATEDIFF(wk, 6, getdate()), 6)
         set @dt=dateadd(dd,1,@dt)
      end   
      if @startperiode=0 begin 
         set @dt=DATEADD(wk, DATEDIFF(wk, 5, getdate()), 5)
         set @dt=dateadd(dd,2,@dt)
         set @dt=dateadd(ms,-3,@dt)
      end   
      return @dt   
   end   
   if @type='ActMonth' begin
      if @startperiode=1 set @dt=DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
      if @startperiode=0 set @dt=Dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0))
      return @dt
   end   
   if @type='ActQuarter' begin
      if @startperiode=1 set @dt= DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
      if @startperiode=0 begin
         set @dt= dateadd(qq,datediff(qq,-1,getdate()),-1)
         set @dt=dateadd(dd,1,@dt)
         set @dt=dateadd(ms,-3,@dt)
      end   
      return @dt
   end   
   if @type='ActYear' begin
      if @startperiode=1 set @dt = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
      if @startperiode=0 set @dt = dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))
      return @dt
   end

   declare @calcdate datetime
   if @type='LastWeek' begin
      set @calcdate=dateadd(wk,-1,getdate())
      if @startperiode=1 begin
         set @dt=DATEADD(wk, DATEDIFF(wk, 6, @calcdate), 6)
         set @dt=dateadd(dd,1,@dt)
      end   
      if @startperiode=0 begin 
         set @dt=DATEADD(wk, DATEDIFF(wk, 5, @calcdate), 5)
         set @dt=dateadd(dd,2,@dt)
         set @dt=dateadd(ms,-3,@dt)
      end   
      return @dt   
   end   
   if @type='LastMonth' begin
      set @calcdate=dateadd(mm,-1,getdate())
      if @startperiode=1 set @dt=DATEADD(mm, DATEDIFF(mm,0,@calcdate), 0)
      if @startperiode=0 set @dt=Dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@calcdate  )+1, 0))
      return @dt
   end   
   if @type='LastQuarter' begin
      set @calcdate=dateadd(qq,-1,getdate())
      if @startperiode=1 set @dt= DATEADD(qq, DATEDIFF(qq,0,@calcdate), 0)
      if @startperiode=0 begin
         set @dt= dateadd(qq,datediff(qq,-1,@calcdate),-1)
         set @dt=dateadd(dd,1,@dt)
         set @dt=dateadd(ms,-3,@dt)
      end   
      return @dt
   end   
   if @type='LastYear' begin
      set @calcdate=dateadd(yy,-1,getdate())
      if @startperiode=1 set @dt = DATEADD(yy, DATEDIFF(yy,0,@calcdate), 0)
      if @startperiode=0 set @dt = dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,@calcdate  )+1, 0))
      return @dt
   end


   return @dt

END



GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_temporaer]    Script Date: 13.12.2014 15:01:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnkt_get_temporaer]
(
	-- Add the parameters for the function here
	@keyvalue as integer
)
RETURNS int
AS
BEGIN
declare @rc int
select @rc=COUNT(*) from dbo.temporaer_Berechtigung where --aktiv=1 and 
Personal_Funktionstellenr=@keyvalue
return @rc

END



GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_beziehung_sql_old]    Script Date: 13.12.2014 15:01:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnkt_get_beziehung_sql_old]
(
	@table1 VARCHAR(255),
	@table2 VARCHAR(255),
	@beznr INT,
	@keyvalue INT,
	@where VARCHAR(255),
	@type INT,
	@personal int
)
RETURNS varchar(1024)
AS
BEGIN
       DECLARE @sql VARCHAR(1024)

       IF @type = 0 
          BEGIN
                IF @personal = 1 BEGIN
                    SET @sql = 'SELECT  dbo.' + @table1 + '_' + @table2 + '.' + @table1 + '_' + @table2 + 'Nr, dbo.personal.tgnummer' + CHAR(39) + ' '+ CHAR(39) + ' dbo.personal.name, dbo.' + @table1 + '.Sequenz, '
                END ELSE BEGIN
                    SET @sql = 'SELECT  dbo.' + @table1 + '_' + @table2 + '.' + @table1 + '_' + @table2 + 'Nr, dbo.' + @table2 + '.Bezeichnung, dbo.'+ @table1 + '.Sequenz, '
                END
                SET @sql = @sql + 'dbo.' + @table1 + '_' + @table2 + '.Beschreibung, dbo.' + @table1 + '_' + @table2 + '.Aktiv, dbo.' + @table1 + '_' + @table2+ '.Erstellt_am, '
                SET @sql = @sql + 'dbo.' + @table1 + '_' + @table2 + '.Mutiert_am,dbo.' + @table1 + '_' + @table2 + '.Mutierer, dbo.fnkt_get_bezeichnung('+ LTRIM(RTRIM(STR(@beznr))) + ') AS Typ, '
                SET @sql = @sql + 'dbo.' + @table2 + '.' + @table2 + 'Nr AS MKEY, dbo.Beziehungstyp.Bezeichnung AS Beziehungstyp '
                SET @sql = @sql + 'FROM dbo.' + @table1 + '_' + @table2 + ' '
                SET @sql = @sql + 'INNER JOIN dbo.' + @table1 + ' ON dbo.' + @table1 + '_' + @table2 + '.' + @table1 + 'Nr = dbo.' + @table1 + '.' + @table1+ 'Nr '
                SET @sql = @sql + 'INNER JOIN dbo.' + @table2 + ' ON dbo.' + @table1 + '_' + @table2 + '.' + @table2 + 'Nr = dbo.' + @table2 + '.' + @table2+ 'Nr '
                SET @sql = @sql + 'INNER JOIN dbo.Beziehungstyp ON dbo.' + @table1 + '_' + @table2 + '.Beziehungstypnr = dbo.Beziehungstyp.BeziehungstypNr '
                SET @sql = @sql + 'WHERE   ' + @where + ' = ' + LTRIM(RTRIM(STR(@keyvalue))) + ' and dbo.' + @table1 + '_' + @table2 + '.aktiv=1'
          END
       IF @type = 1 
          BEGIN
                IF @personal = 1 BEGIN
                    SET @sql = 'SELECT  dbo.' + @table1 + '_' + @table2 + '.' + @table1 + '_' + @table2 + 'Nr, dbo.personal.tgnummer' + CHAR(39) + ' '+ CHAR(39) + ' dbo.personal.name, dbo.' + @table1 + '.Sequenz, '
                END ELSE BEGIN
                    SET @sql = 'SELECT  dbo.' + @table1 + '_' + @table2 + '.' + @table1 + '_' + @table2 + 'Nr, dbo.' + @table2 + '.Bezeichnung, dbo.'+ @table1 + '.Sequenz, '
                END
                SET @sql = @sql + 'dbo.' + @table1 + '_' + @table2 + '.Beschreibung, dbo.' + @table1 + '_' + @table2 + '.Aktiv, dbo.' + @table1 + '_' + @table2+ '.Erstellt_am, '
                SET @sql = @sql + 'dbo.' + @table1 + '_' + @table2 + '.Mutiert_am,dbo.' + @table1 + '_' + @table2 + '.Mutierer, dbo.fnkt_get_bezeichnung('+ LTRIM(RTRIM(STR(@beznr))) + ') AS Typ, '
                SET @sql = @sql + 'dbo.' + @table2 + '.' + @table2 + 'Nr AS MKEY, dbo.Beziehungstyp.Bezeichnung AS Beziehungstyp '
                SET @sql = @sql + 'FROM dbo.' + @table1 + '_' + @table2 + ' '
                SET @sql = @sql + 'INNER JOIN dbo.' + @table1 + ' ON dbo.' + @table1 + '_' + @table2 + '.' + @table1 + 'Nr = dbo.' + @table1 + '.' + @table1+ 'Nr '
                SET @sql = @sql + 'INNER JOIN dbo.' + @table2 + ' ON dbo.' + @table1 + '_' + @table2 + '.' + @table2 + 'Nr = dbo.' + @table2 + '.' + @table2+ 'Nr '
                SET @sql = @sql + 'INNER JOIN dbo.Beziehungstyp ON dbo.' + @table1 + '_' + @table2 + '.Beziehungstypnr = dbo.Beziehungstyp.BeziehungstypNr '
                SET @sql = @sql + 'WHERE   ' + @where + ' = ' + LTRIM(RTRIM(STR(@keyvalue))) + ' and dbo.' + @table1 + '_' + @table2 + '.aktiv=1'
          END
       
RETURN @sql
END



GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_beziehung_sql]    Script Date: 13.12.2014 15:01:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnkt_get_beziehung_sql]
(
	@table1 VARCHAR(255),
	@table2 VARCHAR(255),
	@beznr INT,
	@keyvalue INT,
	@where VARCHAR(255),
	@type INT,
	@personal int
)
RETURNS varchar(1024)
AS
BEGIN
       DECLARE @sql VARCHAR(1024)

       IF @type = 0 
          BEGIN
                IF @personal = 1 BEGIN
                    SET @sql = 'SELECT  dbo.' + @table1 + '_' + @table2 + '.' + @table1 + '_' + @table2 + 'Nr, dbo.personal.tgnummer' + CHAR(39) + ' '+ CHAR(39) + ' dbo.personal.name, dbo.' + @table1 + '.Sequenz, '
                END ELSE BEGIN
                    SET @sql = 'SELECT  dbo.' + @table1 + '_' + @table2 + '.' + @table1 + '_' + @table2 + 'Nr, dbo.' + @table2 + '.Bezeichnung, dbo.'+ @table1 + '.Sequenz, '
                END
                SET @sql = @sql + 'dbo.' + @table1 + '_' + @table2 + '.Beschreibung, dbo.' + @table1 + '_' + @table2 + '.Aktiv, dbo.' + @table1 + '_' + @table2+ '.Erstellt_am, '
                SET @sql = @sql + 'dbo.' + @table1 + '_' + @table2 + '.Mutiert_am,dbo.' + @table1 + '_' + @table2 + '.Mutierer, dbo.fnkt_get_bezeichnung('+ LTRIM(RTRIM(STR(@beznr))) + ') AS Typ, '
                SET @sql = @sql + 'dbo.' + @table2 + '.' + @table2 + 'Nr AS MKEY, dbo.Beziehungstyp.Bezeichnung AS Beziehungstyp '
                SET @sql = @sql + 'FROM dbo.' + @table1 + '_' + @table2 + ' '
                SET @sql = @sql + 'INNER JOIN dbo.' + @table1 + ' ON dbo.' + @table1 + '_' + @table2 + '.' + @table1 + 'Nr = dbo.' + @table1 + '.' + @table1+ 'Nr '
                SET @sql = @sql + 'INNER JOIN dbo.' + @table2 + ' ON dbo.' + @table1 + '_' + @table2 + '.' + @table2 + 'Nr = dbo.' + @table2 + '.' + @table2+ 'Nr '
                SET @sql = @sql + 'INNER JOIN dbo.Beziehungstyp ON dbo.' + @table1 + '_' + @table2 + '.Beziehungstypnr = dbo.Beziehungstyp.BeziehungstypNr '
                SET @sql = @sql + 'WHERE   ' + @where + ' = ' + LTRIM(RTRIM(STR(@keyvalue))) + ' and dbo.' + @table1 + '_' + @table2 + '.aktiv=1'
          END
       IF @type = 1 
          BEGIN
                IF @personal = 1 BEGIN
                    SET @sql = 'SELECT  dbo.' + @table1 + '_' + @table2 + '.' + @table1 + '_' + @table2 + 'Nr, dbo.personal.tgnummer' + CHAR(39) + ' '+ CHAR(39) + ' dbo.personal.name, dbo.' + @table1 + '.Sequenz, '
                END ELSE BEGIN
                    SET @sql = 'SELECT  dbo.' + @table1 + '_' + @table2 + '.' + @table1 + '_' + @table2 + 'Nr, dbo.' + @table2 + '.Bezeichnung, dbo.'+ @table1 + '.Sequenz, '
                END
                SET @sql = @sql + 'dbo.' + @table1 + '_' + @table2 + '.Beschreibung, dbo.' + @table1 + '_' + @table2 + '.Aktiv, dbo.' + @table1 + '_' + @table2+ '.Erstellt_am, '
                SET @sql = @sql + 'dbo.' + @table1 + '_' + @table2 + '.Mutiert_am,dbo.' + @table1 + '_' + @table2 + '.Mutierer, dbo.fnkt_get_bezeichnung('+ LTRIM(RTRIM(STR(@beznr))) + ') AS Typ, '
                SET @sql = @sql + 'dbo.' + @table2 + '.' + @table2 + 'Nr AS MKEY, dbo.Beziehungstyp.Bezeichnung AS Beziehungstyp '
                SET @sql = @sql + 'FROM dbo.' + @table1 + '_' + @table2 + ' '
                SET @sql = @sql + 'INNER JOIN dbo.' + @table1 + ' ON dbo.' + @table1 + '_' + @table2 + '.' + @table1 + 'Nr = dbo.' + @table1 + '.' + @table1+ 'Nr '
                SET @sql = @sql + 'INNER JOIN dbo.' + @table2 + ' ON dbo.' + @table1 + '_' + @table2 + '.' + @table2 + 'Nr = dbo.' + @table2 + '.' + @table2+ 'Nr '
                SET @sql = @sql + 'INNER JOIN dbo.Beziehungstyp ON dbo.' + @table1 + '_' + @table2 + '.Beziehungstypnr = dbo.Beziehungstyp.BeziehungstypNr '
                SET @sql = @sql + 'WHERE   ' + @where + ' = ' + LTRIM(RTRIM(STR(@keyvalue))) + ' and dbo.' + @table1 + '_' + @table2 + '.aktiv=1'
          END
       
RETURN @sql
END




GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_bezeichnung_old]    Script Date: 13.12.2014 15:01:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnkt_get_bezeichnung_old]
(
   @type int
   
)
RETURNS VARCHAR(255)
AS
BEGIN
     DECLARE @result VARCHAR(255)
     SELECT @result=bezeichnung FROM dbo.Beziehungsbezeichnung WHERE BeschreibungNr = @type
     RETURN @result

END



GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_bezeichnung]    Script Date: 13.12.2014 15:01:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnkt_get_bezeichnung]
(
   @type int
   
)
RETURNS VARCHAR(255)
AS
BEGIN
     DECLARE @result VARCHAR(255)
     SELECT @result=bezeichnung FROM dbo.Beziehungsbezeichnung WHERE BeschreibungNr = @type
     RETURN @result

END




GO