shu
/
ITSM
0
0
Fork 0
Code Issues 1 Pull Requests Projects Releases Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
ITSM/_Scripts_SecurityLevel/SHU/ITSM_UDF.sql

1878 lines
113 KiB
Raw Permalink Blame History Escape

This file contains invisible Unicode characters!

This file contains invisible Unicode characters that may be processed differently from what appears below. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to reveal hidden characters.

USE [Vertragsverwaltung_20160404]
GO
/****** Object: UserDefinedFunction [dbo].[_count_Appl_Kontakt] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[_count_Appl_Kontakt]
(
@applnr int
)
RETURNS int
AS
BEGIN
declare @rc int
select @rc=count(ApplikationKontaktnr) from applikationkontakt where applikationnr=@applnr and aktiv=1
RETURN @rc
END
GO
/****** Object: UserDefinedFunction [dbo].[Check_Date] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Check_Date]
(@datum datetime)
RETURNS datetime
BEGIN
declare @result datetime
if @datum is null set @datum='01.01.1900'
set @result=@datum
if year(@datum)=1900 set @result=null
return @result
END
GO
/****** Object: UserDefinedFunction [dbo].[Check_Generate_Activity] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[Check_Generate_Activity]
(@startdatum datetime,
@enddatum datetime,
@steuerdatum datetime,
@vorlauf int,
@periode int,
@generate int,
@kuendigungsfristnr int,
@kuendigung int,
@fnkt int,
@einmalig int)
Returns varchar(25) AS
BEGIN
declare @workdate datetime
declare @result varchar(15)
declare @eom int
declare @eomdate datetime
declare @dd varchar(2)
declare @mm varchar(2)
declare @yy varchar(4)
set @eom=0
set @result=0
---------------------------------------------------------------------------------------------------------------------------
--Initialisierung - Datum / Zeit-Sync
---------------------------------------------------------------------------------------------------------------------------
set @startdatum = FLOOR( CAST( @startdatum AS FLOAT ) )
set @enddatum = FLOOR( CAST( @enddatum AS FLOAT ) )
set @steuerdatum = FLOOR( CAST( @steuerdatum AS FLOAT ) )
set @eomdate=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@startdatum)+1,0))
set @eomdate = FLOOR( CAST( @eomdate AS FLOAT ) )
--if @enddatum < '01.01.1901' set @enddatum = '31.12.2099'
if @enddatum < '01.01.1901' set @enddatum = '31.12.2099'---------------------------------------------------------------------------------------------------------------------------
--Einmalige Ereignisse
---------------------------------------------------------------------------------------------------------------------------
if @einmalig = 1 begin
if @periode=15 begin
set @result=''
set @workdate = @startdatum
if @kuendigung=1 begin
if @kuendigungsfristnr=1 set @workdate=dateadd(mm,-1,@startdatum)
if @kuendigungsfristnr=2 set @workdate=dateadd(mm,-2,@startdatum)
if @kuendigungsfristnr=3 set @workdate=dateadd(mm,-3,@startdatum)
if @kuendigungsfristnr=4 set @workdate=dateadd(mm,-6,@startdatum)
if @kuendigungsfristnr=5 set @workdate=dateadd(mm,-9,@startdatum)
if @kuendigungsfristnr=6 set @workdate=dateadd(mm,-12,@startdatum)
set @workdate=FLOOR( CAST( @workdate AS FLOAT ) )
end
set @workdate=dateadd(dd,@vorlauf*-1,@workdate)
if @steuerdatum=floor(cast(@workdate as float)) begin
set @result='1'
if @fnkt=1 begin
set @dd=ltrim(str(day(@startdatum)))
while len(@dd)<2 begin
set @dd='0'+@dd
end
set @mm=ltrim(str(month(@startdatum)))
while len(@mm)<2 begin
set @mm='0'+@mm
end
set @result=@dd+'.'+@mm+'.'+ltrim(str(year(@startdatum)))
end
--if @fnkt=1 set @result=@startdatum
end
end
return @result
end
---------------------------------------------------------------------------------------------------------------------------
--Startdatum auf EOM prüfen
---------------------------------------------------------------------------------------------------------------------------
if @eomdate=@startdatum begin
set @eom=1
end
if @generate=0 begin
set @result=''
return @result
end
---------------------------------------------------------------------------------------------------------------------------
--Vorlaufzeit in Tagen vom Startdatum abziehen. Wenn dieses nach dem aktuellen Tagesdatum liegt, keine Aktivität generieren
---------------------------------------------------------------------------------------------------------------------------
if @startdatum='01.01.1900' begin
set @result=''
return @result
end
set @workdate=@startdatum
if @kuendigung=1 begin
if @kuendigungsfristnr=1 set @workdate=dateadd(mm,-1,@startdatum)
if @kuendigungsfristnr=2 set @workdate=dateadd(mm,-2,@startdatum)
if @kuendigungsfristnr=3 set @workdate=dateadd(mm,-3,@startdatum)
if @kuendigungsfristnr=4 set @workdate=dateadd(mm,-6,@startdatum)
if @kuendigungsfristnr=5 set @workdate=dateadd(mm,-9,@startdatum)
if @kuendigungsfristnr=6 set @workdate=dateadd(mm,-12,@startdatum)
end
set @workdate=dateadd(dd,@vorlauf*-1,@workdate)
if @workdate > @steuerdatum begin
set @result=''
return @result
end
---------------------------------------------------------------------------------------------------------------------------
-- Berechnung
-- 1=einmalig,2=täglich,3=wöchentlich,4=monatlich,5=zwei-monatlich,6=vierteljährlich,7=halbjährlich,8=jählrich
---------------------------------------------------------------------------------------------------------------------------
declare @dd1 int
declare @dd2 int
set @dd1=datepart(dd,@startdatum)
--Print @workdate
declare @tmpdate datetime
declare @ok int
set @ok=0
while @workdate < @enddatum begin
if month(@workdate)=2 and day(@startdatum)>28 and @vorlauf>0 begin
set @tmpdate=dateadd(dd,(day(@startdatum)-28)*-1,@workdate)
if @tmpdate=@steuerdatum set @ok=1
end
if @workdate=@steuerdatum begin
set @ok=1
end
if @ok=1 begin
set @result='1'
---------------------------------------------------------------------------------------------------------------------------
--Berechnung des Kündigungstermins
---------------------------------------------------------------------------------------------------------------------------
if @fnkt=1 begin
--set @workdate=dateadd(d,@vorlauf,@workdate)
if @kuendigungsfristnr=1 set @workdate=dateadd(mm,1,@workdate)
if @kuendigungsfristnr=2 set @workdate=dateadd(mm,2,@workdate)
if @kuendigungsfristnr=3 set @workdate=dateadd(mm,3,@workdate)
if @kuendigungsfristnr=4 set @workdate=dateadd(mm,6,@workdate)
if @kuendigungsfristnr=5 set @workdate=dateadd(mm,9,@workdate)
if @kuendigungsfristnr=6 set @workdate=dateadd(mm,12,@workdate)
if @eom=1 begin
set @workdate=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@workdate)+1,0))
set @workdate = FLOOR( CAST( @workdate AS FLOAT ) )
set @dd=ltrim(str(day(@workdate)))
while len(@dd)<2 begin
set @dd='0'+@dd
end
set @mm=ltrim(str(month(@workdate)))
while len(@mm)<2 begin
set @mm='0'+@mm
end
set @result=@dd+'.'+@mm+'.'+ltrim(str(year(@workdate)))
-- set @result=@workdate
end else begin
set @workdate = FLOOR( CAST( @workdate AS FLOAT ) )
-- Datumsberechung zur Datumsausgabe - Termin (28. - 31.)
set @dd2=datepart(dd,@workdate)
if @dd1>=28 begin
if @dd2 < @dd1 begin
set @workdate=dateadd(dd,@dd1-@dd2,@workdate)
end
end
set @dd=ltrim(str(day(@workdate)))
while len(@dd)<2 begin
set @dd='0'+@dd
end
set @mm=ltrim(str(month(@workdate)))
while len(@mm)<2 begin
set @mm='0'+@mm
end
set @result=@dd+'.'+@mm+'.'+ltrim(str(year(@workdate)))
--set @result=@workdate
end
end else begin
if @kuendigungsfristnr=1 set @workdate=dateadd(mm,1,@workdate)
if @kuendigungsfristnr=2 set @workdate=dateadd(mm,2,@workdate)
if @kuendigungsfristnr=3 set @workdate=dateadd(mm,3,@workdate)
if @kuendigungsfristnr=4 set @workdate=dateadd(mm,6,@workdate)
if @kuendigungsfristnr=5 set @workdate=dateadd(mm,9,@workdate)
if @kuendigungsfristnr=6 set @workdate=dateadd(mm,12,@workdate)
if @workdate > @enddatum begin
set @result=''
return @result
end
end
return @result
break
end
if @workdate > @steuerdatum begin
set @result=''
return @result
break
end
if @periode = 10 set @workdate=dateadd(dd,1,@workdate)
if @periode = 11 set @workdate=dateadd(ww,1,@workdate)
if @periode = 12 set @workdate=dateadd(mm,1,@workdate)
if @periode =13 set @workdate=dateadd(mm,2,@workdate)
if @periode = 16 set @workdate=dateadd(qq,1,@workdate)
if @periode = 14 set @workdate=dateadd(qq,2,@workdate)
if @periode =9 set @workdate=dateadd(yy,1,@workdate)
--if @periode = 9 set @workdate=dateadd(mm,1,@workdate)
end
set @result=''
return @result
end
GO
/****** Object: UserDefinedFunction [dbo].[fnkt_erstloesungsquote] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnkt_erstloesungsquote]
(
@fnkt INT,
@ticketid VARCHAR(255)
)
RETURNS int
AS
BEGIN
DECLARE @res int
DECLARE @bg VARCHAR(255)
DECLARE @cnt int
IF @fnkt=1 BEGIN -- Check Erste Besitzergruppe = ServiceDesk
SELECT TOP 1 @bg=besitzergruppe FROM txp_ticket_data WHERE ticketid=@ticketid ORDER BY Status_Changedate
IF @bg='ServiceDesk' SET @res = 1 ELSE SET @res=0
IF @res=0 BEGIN
SET @res = @res
END ELSE BEGIN
SELECT @cnt=COUNT(*) FROM txp_ticket_data WHERE ticketid=@ticketid AND (status_neu='Zugewiesen 2nd Level' OR Status_Neu='In Bearbeitung - 3rd Level' OR status_neu='Zugewiesen WPI')
IF @cnt>0 SET @res=0 ELSE SET @res= 1
end
END
RETURN @res
--IF @fnkt=1 BEGIN -- Check Erste Besitzergruppe = ServiceDesk
-- SELECT TOP 1 @bg=besitzergruppe FROM txp_ticket_data WHERE ticketid=@ticketid ORDER BY Status_Changedate
-- IF @bg='ServiceDesk' SET @res = 1 ELSE SET @res=0
-- END
--IF @fnkt=2 BEGIN -- Prüfung, ob Status durchlaufen worden sind
-- SELECT @cnt=COUNT(*) FROM txp_ticket_data WHERE ticketid=@ticketid AND (status_neu='Zugewiesen 2nd Level' OR Status_Neu='In Bearbeitung - 3rd Level' OR status_neu='Zugewiesen WPI')
-- IF @cnt>0 SET @res=1 ELSE SET @res= 0
-- END
--RETURN @res
END
GO
/****** Object: UserDefinedFunction [dbo].[fnkt_get_KPI_hierarchie] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Create Function [dbo].[fnkt_get_KPI_hierarchie]
(
@startkey int
)
RETURNS @tmp1 TABLE
(
ID INT
)
AS
begin
WITH tmp1 (id)
AS
(
SELECT oenr
FROM oe
WHERE oenr=@startkey
UNION ALL
SELECT C.oenr FROM oe C
INNER JOIN tmp1 R ON C.parentid = R.id
)
INSERT INTO @tmp1
SELECT id
FROM tmp1
RETURN
end
GO
/****** Object: UserDefinedFunction [dbo].[fnkt_get_last_closedstate] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnkt_get_last_closedstate]
(
@ticketid varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @res VARCHAR(255)
SELECT TOP 1 @res= ISNULL(statusid,'') FROM txp_ticket_data WHERE ticketid=@ticketid AND status_neu='Solved' ORDER BY Erstellungsdatum DESC
RETURN @res
END
GO
/****** Object: UserDefinedFunction [dbo].[fnkt_get_last_status] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnkt_get_last_status]
(
@ticketid varchar(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @res VARCHAR(255)
SELECT TOP 1 @res=status_neu FROM TXP_Ticket_Data WHERE ticketid=@ticketid ORDER BY Status_Changedate desc
RETURN @res
END
GO
/****** Object: UserDefinedFunction [dbo].[fnkt_get_last_statusID] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
Create FUNCTION [dbo].[fnkt_get_last_statusID]
(
@ticketid varchar(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @res VARCHAR(255)
SELECT TOP 1 @res=statusID FROM TXP_Ticket_Data WHERE ticketid=@ticketid ORDER BY Status_Changedate desc
RETURN @res
END
GO
/****** Object: UserDefinedFunction [dbo].[fnkt_split] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[fnkt_split]
(
@sInputList varchar(8000) -- List of delimited items
, @Delimiter char(1) = ',' -- delimiter that separates items
, @Delimiterlength INT = 1
)
RETURNS @tmp1 TABLE (item varchar(8000))
AS
begin
DECLARE @Item VARCHAR(8000)
WHILE CHARINDEX(@Delimiter, @sInputList, 0) <> 0
BEGIN
SELECT @Item = RTRIM(LTRIM(SUBSTRING(@sInputList, 1, CHARINDEX(@Delimiter, @sInputList, 0) - 1))),
@sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList, CHARINDEX(@Delimiter, @sInputList, 0) + @Delimiterlength, LEN(@sInputList))))
IF LEN(@Item) > 0
INSERT INTO @tmp1
SELECT @Item
END
IF LEN(@sInputList) > 0
INSERT INTO @tmp1
SELECT @sInputList
RETURN
END
GO
/****** Object: UserDefinedFunction [dbo].[get_anteil] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_anteil]
(
@engagement int,
@matyp int
)
RETURNS float
AS
BEGIN
DECLARE @fhranteil INT
SELECT @fhranteil=Fuehrungsanteil FROM dbo.MitarbeiterTyp WHERE mitarbeitertypnr=@matyp
DECLARE @res FLOAT
SET @res=(@engagement * (100-@fhranteil))/100
RETURN @res
END
GO
/****** Object: UserDefinedFunction [dbo].[get_applobject] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_applobject]
(
-- Add the parameters for the function here
@input_guid varchar(255),
@input_type int
)
RETURNS varchar(1024)
AS
BEGIN
declare @resultat varchar(1024)
set @resultat=''
declare @appl varchar(255)
declare @crlf varchar(2)
SET @crlf = char(13) + char(10)
if @input_type=1 begin
declare xc cursor for
select applikation from applikationarchitekturobjectappl where guid=@input_guid
open xc
fetch next from xc into @appl
while @@fetch_status=0 begin
set @resultat=@resultat + '- '+@appl+@crlf
fetch next from xc into @appl
end
close xc
deallocate xc
RETURN @resultat
end
if @input_type=2 begin
declare xc cursor for
select datenbank from applikationarchitekturobjectdb where guid=@input_guid
open xc
fetch next from xc into @appl
while @@fetch_status=0 begin
set @resultat=@resultat + '- '+@appl+@crlf
fetch next from xc into @appl
end
close xc
deallocate xc
RETURN @resultat
end
if @input_type=3 begin
declare xc cursor for
select sst from applikationarchitekturobjectsst where guid=@input_guid
open xc
fetch next from xc into @appl
while @@fetch_status=0 begin
set @resultat=@resultat + '- '+@appl+@crlf
fetch next from xc into @appl
end
close xc
deallocate xc
RETURN @resultat
end
if @input_type=4 begin
declare xc cursor for
select applikation from applikationarchitekturobjectvirt where guid=@input_guid
open xc
fetch next from xc into @appl
while @@fetch_status=0 begin
set @resultat=@resultat + '- '+@appl+@crlf
fetch next from xc into @appl
end
close xc
deallocate xc
RETURN @resultat
end
RETURN @resultat
END
GO
/****** Object: UserDefinedFunction [dbo].[get_arbeitstage] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_arbeitstage]
(
@madetailnr INT,
@jahr int,
@monat INT
)
RETURNS float
AS
BEGIN
DECLARE @SOLL INT
DECLARE @Netto INT
DECLARE @oenr INT
DECLARE @Individuelle_Reduktion float
DECLARE @reduktion_nicht_verrechenbar int
DECLARE @valuefound INT
SET @valuefound=-1
SET @Individuelle_Reduktion=0
SELECT @Individuelle_Reduktion=Individuelle_Reduktion, @oenr=oenr FROM dbo.MADetails WHERE MADetailNr=@madetailnr
WHILE @valuefound<0 BEGIN
SELECT @reduktion_nicht_verrechenbar=ISNULL(reduktion_nicht_verrechenbar,0),@oenr=parentid FROM oe WHERE oenr=@oenr
IF @reduktion_nicht_verrechenbar>0 SET @valuefound=1
IF @oenr = 0 BEGIN
SET @valuefound=2
IF @reduktion_nicht_verrechenbar=0 SET @reduktion_nicht_verrechenbar=0
END
end
IF @Individuelle_Reduktion > 0 SET @Individuelle_Reduktion=@Individuelle_Reduktion/12
DECLARE @res float
SELECT @res = Plantage FROM jahrestabelle WHERE jahr=@jahr AND monat=@monat
SET @res=@res*((100-@reduktion_nicht_verrechenbar))/100
SET @res=@res - @Individuelle_Reduktion
SET @res=ROUND(@res,2)
--SELECT @res = Arbeitstage FROM jahrestabelle WHERE jahr=@jahr AND monat=@monat
RETURN @res
END
GO
/****** Object: UserDefinedFunction [dbo].[get_arbeitstage_old] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
Create FUNCTION [dbo].[get_arbeitstage_old]
(
@jahr int,
@monat int
)
RETURNS float
AS
BEGIN
DECLARE @res int
SELECT @res = arbeitstage FROM jahrestabelle WHERE jahr=@jahr AND monat=@monat
RETURN @res
END
GO
/****** Object: UserDefinedFunction [dbo].[get_arbeitstage_prozent] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_arbeitstage_prozent]
(
@OE INT
)
RETURNS float
AS
BEGIN
DECLARE @SOLL INT
DECLARE @Netto INT
DECLARE @oenr INT
DECLARE @Individuelle_Reduktion float
DECLARE @reduktion_nicht_verrechenbar int
DECLARE @valuefound INT
SET @valuefound=-1
SELECT @reduktion_nicht_verrechenbar=ISNULL(reduktion_nicht_verrechenbar,0),@oenr=parentid FROM oe WHERE oenr=@oe
if @reduktion_nicht_verrechenbar > 0 set @valuefound=1
WHILE @valuefound<0 BEGIN
SELECT @reduktion_nicht_verrechenbar=ISNULL(reduktion_nicht_verrechenbar,0),@oenr=parentid FROM oe WHERE oenr=@oenr
IF @reduktion_nicht_verrechenbar>0 SET @valuefound=1
IF @oenr = 0 BEGIN
SET @valuefound=2
IF @reduktion_nicht_verrechenbar=0 SET @reduktion_nicht_verrechenbar=0
END
end
DECLARE @res float
set @res=@reduktion_nicht_verrechenbar
--SET @res=@res*((100-@reduktion_nicht_verrechenbar))/100
--'SET @res=ROUND(@res,2)
RETURN @res
END
GO
/****** Object: UserDefinedFunction [dbo].[Get_Dateperiode] Script Date: 27.01.2017 08:56:19 ******/
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].[get_gremium] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_gremium]
(
@keyvalue int
)
RETURNS varchar(255)
AS
BEGIN
declare @res varchar(255)
set @res=''
select @res=ISNULL(Gruppierungsbegriff,'') from gremiumgruppierung where gremiumgruppierungnr=@keyvalue and aktiv=1
if @@ROWCOUNT=0 set @res=''
return @res
END
GO
/****** Object: UserDefinedFunction [dbo].[get_keyuser_stv] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_keyuser_stv]
(@applikationnr int,
@typ int)
RETURNS varchar(255)
AS
BEGIN
declare @res varchar(255)
SELECT @res=dbo.Person.Name + ' ' + dbo.Person.Vorname
FROM dbo.ApplikationKontakt INNER JOIN
dbo.Person ON dbo.ApplikationKontakt.PersonNr = dbo.Person.PersonNr
WHERE (dbo.Person.Aktiv = 1) AND (dbo.ApplikationKontakt.Aktiv = 1) AND
(dbo.ApplikationKontakt.KontakttypNr = @typ) AND (dbo.ApplikationKontakt.Applikationnr = @applikationnr)
if @res is null set @res=''
return @res
END
GO
/****** Object: UserDefinedFunction [dbo].[Get_Kuendigungsfrist] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[Get_Kuendigungsfrist]
(@kuendigungsfristnr int, @kuendigung int)
RETURNS varchar (25)
as
BEGIN
declare @result varchar(25)
SET @result=''
if @kuendigung=0 return @result
select @result = bezeichnung from kuendigungsfristen where kuendigungsfristnr = @kuendigungsfristnr
return @result
END
GO
/****** Object: UserDefinedFunction [dbo].[get_monatstabelle] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[get_monatstabelle](@from AS DATETIME, @to AS DATETIME)
RETURNS @Dates TABLE(start_date DATETIME, End_Date datetime)
AS
BEGIN
DECLARE @st DATETIME
DECLARE @ed DATETIME
WHILE @from < @to BEGIN
SELECT @st=DATEADD(MONTH, DATEDIFF(MONTH, 0, @from), 0)
SELECT @ed=DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @from) + 1, 0))
SET @ed=DATEADD(HOUR,23,@ed)
SET @ed=DATEADD(MINUTE,59,@ed)
SET @ed=DATEADD(SECOND,45,@ed)
INSERT @dates (start_date, end_date) VALUES(@st, @ed)
SET @from = DATEADD(MONTH, 1,@from)
end
RETURN
END
GO
/****** Object: UserDefinedFunction [dbo].[Get_Next_Kuendigungstermin] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
Create FUNCTION [dbo].[Get_Next_Kuendigungstermin]
(@ereignisnr int)
Returns datetime AS
BEGIN
declare @start as datetime
declare @ende as datetime
declare @periode as integer
declare @workdate datetime
select @start=start, @ende=ende, @periode=periodizitaetnr from vertragsereignis where ereignisnr=@ereignisnr
if @ende < '01.01.1901' set @ende='31.12.2099'
if @periode = 15 begin
select @start=datum, @ende=ende, @periode=periodizitaetnr from vertragsereignis where ereignisnr=@ereignisnr
if @start > getdate()
set @workdate=@start
return @workdate
end
--PeriodizitaetNr Bezeichnung--
--9 jährlich
--10 täglich
--11 wöchentlich
--12 monatlich
--13 zwei-monatlich
--14 halb-jährlich
--15 einmalig
--16 vierteljährlich
set @workdate = @start
while @workdate < @ende and @workdate < getdate() begin
if @periode = 9 set @workdate=dateadd(yy,1,@workdate)
if @periode = 10 set @workdate=dateadd(dd,1,@workdate)
if @periode = 11 set @workdate=dateadd(ww,1,@workdate)
if @periode = 12 set @workdate=dateadd(mm,1,@workdate)
if @periode = 13 set @workdate=dateadd(mm,2,@workdate)
if @periode = 14 set @workdate=dateadd(mm,6,@workdate)
if @periode = 16 set @workdate=dateadd(qq,1,@workdate)
end
if @workdate > @ende set @workdate='01.01.1900'
return @workdate
end
GO
/****** Object: UserDefinedFunction [dbo].[get_plantage] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_plantage]
(
@jahr int,
@monat INT
)
RETURNS float
AS
BEGIN
DECLARE @res INT
SELECT @res = plantage FROM jahrestabelle WHERE jahr=@jahr AND monat=@monat
RETURN @res
END
GO
/****** Object: UserDefinedFunction [dbo].[Get_SecurityLevel] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[Get_SecurityLevel] (@mitarbeiternr int)
RETURNS int AS
BEGIN
declare @seclevel int
SELECT TOP 1 @seclevel = dbo.SecurityLevel.SecurityLevelNr
FROM dbo.funktionsgruppe_rolle INNER JOIN
dbo.rolle ON dbo.funktionsgruppe_rolle.rollenr = dbo.rolle.rollenr INNER JOIN
dbo.rolle_securitylevel ON dbo.rolle.rollenr = dbo.rolle_securitylevel.rollenr INNER JOIN
dbo.funktionsgruppe ON dbo.funktionsgruppe_rolle.funktionsgruppenr = dbo.funktionsgruppe.funktionsgruppenr INNER JOIN
dbo.mitarbeiter_funktionsgruppe ON dbo.funktionsgruppe.funktionsgruppenr = dbo.mitarbeiter_funktionsgruppe.funktionsgruppenr INNER JOIN
dbo.SecurityLevel ON dbo.rolle_securitylevel.SecurityLevelNr = dbo.SecurityLevel.SecurityLevelNr
WHERE (dbo.SecurityLevel.Aktiv = 1) AND (dbo.mitarbeiter_funktionsgruppe.aktiv = 1) AND (dbo.funktionsgruppe.aktiv = 1) AND (dbo.rolle.aktiv = 1) AND
(dbo.funktionsgruppe_rolle.aktiv = 1) AND (dbo.rolle_securitylevel.aktiv = 1) and dbo.mitarbeiter_funktionsgruppe.mitarbeiternr=@mitarbeiternr
ORDER BY dbo.rolle_securitylevel.SecurityLevelNr desc
if @@rowcount = 0 begin
set @seclevel=9999
end
return @seclevel
END
GO
/****** Object: UserDefinedFunction [dbo].[Get_SecurityLevelTab] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[Get_SecurityLevelTab]
(
@mitarbeiternr int
)
RETURNS @Seclevel TABLE(SecurityLevelnr int)
AS
begin
insert @Seclevel (SecurityLevelnr) values(0)
insert @seclevel
SELECT dbo.SecurityLevel.SecurityLevelNr
FROM dbo.funktionsgruppe_rolle INNER JOIN
dbo.rolle ON dbo.funktionsgruppe_rolle.rollenr = dbo.rolle.rollenr INNER JOIN
dbo.rolle_securitylevel ON dbo.rolle.rollenr = dbo.rolle_securitylevel.rollenr INNER JOIN
dbo.funktionsgruppe ON dbo.funktionsgruppe_rolle.funktionsgruppenr = dbo.funktionsgruppe.funktionsgruppenr INNER JOIN
dbo.mitarbeiter_funktionsgruppe ON dbo.funktionsgruppe.funktionsgruppenr = dbo.mitarbeiter_funktionsgruppe.funktionsgruppenr INNER JOIN
dbo.SecurityLevel ON dbo.rolle_securitylevel.SecurityLevelNr = dbo.SecurityLevel.SecurityLevelNr
WHERE (dbo.SecurityLevel.Aktiv = 1) AND (dbo.mitarbeiter_funktionsgruppe.aktiv = 1) AND (dbo.funktionsgruppe.aktiv = 1) AND (dbo.rolle.aktiv = 1) AND
(dbo.funktionsgruppe_rolle.aktiv = 1) AND (dbo.rolle_securitylevel.aktiv = 1) and dbo.mitarbeiter_funktionsgruppe.mitarbeiternr=@mitarbeiternr
return
end
GO
/****** Object: UserDefinedFunction [dbo].[get_solved_in] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[get_solved_in]
(
@von DATETIME,
@bis DATETIME
)
RETURNS @solved_in TABLE (ticketid varchar(255), statusid varchar(255))
AS
begin
DECLARE @ticketid varchar(255)
DECLARE @statusid VARCHAR(255)
DECLARE @changedate DATETIME
DECLARE @rc int
DECLARE xc CURSOR FOR
SELECT DISTINCT ticketid FROM txp_ticket_data WHERE ticketid LIKE 'in_%' AND status_neu = 'solved' AND Status_Changedate BETWEEN @von AND @bis
OPEN xc
FETCH NEXT FROM xc INTO @ticketid
WHILE @@FETCH_STATUS=0 BEGIN
FETCH NEXT FROM xc INTO @ticketid
SELECT TOP 1 @statusid=statusid, @changedate=Status_Changedate FROM txp_ticket_data WHERE ticketid=@ticketid AND status_neu='Solved' ORDER BY Status_Changedate DESC
SELECT @rc=COUNT(*) FROM dbo.TXP_Ticket_Data WHERE ticketid=@ticketid AND Status_Changedate>@changedate AND status_neu <> 'Solved' AND status_neu <> 'Geschlossen'
AND status_neu <> 'Geschlossen "Autoclose"' AND status_neu <> 'Warten auf Schliessung'
IF @rc=0 begin
INSERT @solved_in (ticketid, statusid) VALUES(@ticketid, @statusid)
end
END
CLOSE xc
DEALLOCATE xc
return
END
GO
/****** Object: UserDefinedFunction [dbo].[Get_Type] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[Get_Type]
(
@typ int,
@det varchar(25),
@applnr int
)
RETURNS varchar(50)
AS
BEGIN
declare @ret varchar(50)
declare @cnt int
declare @user varchar(50)
if @typ=1 begin
if @det='TS' select @cnt=count(*) from ApplikationInstallationTyp where applikationnr=@applnr and installationtypnr=1 and aktiv = 1
if @det='Filer' select @cnt=count(*) from ApplikationInstallationTyp where applikationnr=@applnr and installationtypnr=2 and aktiv = 1
if @det='FAT (paketiert)' select @cnt=count(*) from ApplikationInstallationTyp where applikationnr=@applnr and installationtypnr=3 and aktiv = 1
if @det='FAT Manuell' select @cnt=count(*) from ApplikationInstallationTyp where applikationnr=@applnr and installationtypnr=4 and aktiv = 1
if @det='Web-Server' select @cnt=count(*) from ApplikationInstallationTyp where applikationnr=@applnr and installationtypnr=5 and aktiv = 1
if @det='Standalone' select @cnt=count(*) from ApplikationInstallationTyp where applikationnr=@applnr and installationtypnr=6 and aktiv = 1
if @det='externe URL' select @cnt=count(*) from ApplikationInstallationTyp where applikationnr=@applnr and installationtypnr=7 and aktiv = 1
if @cnt>0 set @ret='X' else set @ret=''
return @ret
end
if @typ=2 begin
if @det='SPF' select @cnt=count(*) from ApplikationInstallationals where applikationnr=@applnr and installationalstypnr=1 and aktiv=1
if @det='IPF' select @cnt=count(*) from ApplikationInstallationals where applikationnr=@applnr and installationalstypnr=2 and aktiv=1
if @det='NL' select @cnt=count(*) from ApplikationInstallationals where applikationnr=@applnr and installationalstypnr=3 and aktiv=1
if @det='HS' select @cnt=count(*) from ApplikationInstallationals where applikationnr=@applnr and installationalstypnr=4 and aktiv=1
if @det='CC' select @cnt=count(*) from ApplikationInstallationals where applikationnr=@applnr and installationalstypnr=5 and aktiv=1
if @det='Handel' select @cnt=count(*) from ApplikationInstallationals where applikationnr=@applnr and installationalstypnr=6 and aktiv=1
if @cnt>0 set @ret='X' else set @ret=''
return @ret
end
if @typ=3 begin
if @det='PK' select @cnt=count(*) from ApplikationInstallationFuer where applikationnr=@applnr and ApplikationStandardTypNr=1 and aktiv=1
if @det='FK' select @cnt=count(*) from ApplikationInstallationFuer where applikationnr=@applnr and ApplikationStandardTypNr=2 and aktiv=1
if @det='PBI'select @cnt=count(*) from ApplikationInstallationFuer where applikationnr=@applnr and ApplikationStandardTypNr=3 and aktiv=1
if @det='Stab' select @cnt=count(*) from ApplikationInstallationFuer where applikationnr=@applnr and ApplikationStandardTypNr=4 and aktiv=1
if @cnt>0 set @ret='X' else set @ret=''
return @ret
end
if @typ=4 begin
if @det='Key_User' begin
SELECT @ret=dbo.Person.Name + ' ' + dbo.Person.Vorname
FROM dbo.ApplikationKontakt INNER JOIN
dbo.Person ON dbo.ApplikationKontakt.PersonNr = dbo.Person.PersonNr
WHERE (dbo.ApplikationKontakt.Aktiv = 1) AND (dbo.ApplikationKontakt.Applikationnr = @applnr) AND (dbo.ApplikationKontakt.KontakttypNr = 1)
end
if @det='Key_User_Stv' begin
SELECT @ret=dbo.Person.Name + ' ' + dbo.Person.Vorname
FROM dbo.ApplikationKontakt INNER JOIN
dbo.Person ON dbo.ApplikationKontakt.PersonNr = dbo.Person.PersonNr
WHERE (dbo.ApplikationKontakt.Aktiv =1 ) AND (dbo.ApplikationKontakt.Applikationnr = @applnr) AND (dbo.ApplikationKontakt.KontakttypNr = 2)
end
if @det='Fachvertreter' begin
SELECT @ret=dbo.Person.Name + ' ' + dbo.Person.Vorname
FROM dbo.ApplikationKontakt INNER JOIN
dbo.Person ON dbo.ApplikationKontakt.PersonNr = dbo.Person.PersonNr
WHERE (dbo.ApplikationKontakt.Aktiv = 1) AND (dbo.ApplikationKontakt.Applikationnr = @applnr) AND (dbo.ApplikationKontakt.KontakttypNr = 3)
end
return @ret
end
return @ret
END
GO
/****** Object: UserDefinedFunction [dbo].[get_x] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_x]
(
@inp bit
)
RETURNS varchar(1)
AS
BEGIN
DECLARE @RET VARCHAR(1)
IF @INP=1 SET @RET='x' ELSE SET @RET=''
RETURN @RET
END
GO
/****** Object: UserDefinedFunction [dbo].[lc_rpt1] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[lc_rpt1]
(
@produkte VARCHAR(255),
@von varchar(255),
@bis varchar(255),
@produkttypnr int
)
RETURNS @tmp1 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
AS
begin
declare @tmp2 TABLE (LC_Phasenr INT, Phase VARCHAR(255), datum datetime)
declare @tmp3 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
IF @von='' SET @von='01.01.1900'
IF @bis='' SET @bis='31.12.4712'
IF @produkte='' BEGIN
insert into @tmp2 SELECT DISTINCT dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, lc_produkt_phase.datum
FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
ORDER BY lc_produkt_phase.datum
END
IF @produkte<>'' BEGIN
insert into @tmp2 SELECT DISTINCT dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, lc_produkt_phase.datum
FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
and dbo.LC_Produkt_Phase.LC_Produktnr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
ORDER BY lc_produkt_phase.datum
end
INSERT @tmp3
SELECT TOP (100) PERCENT LC_Phasenr, Phase
FROM @tmp2
GROUP BY LC_Phasenr, Phase
ORDER BY MIN(datum)
DECLARE @phase VARCHAR(255)
DECLARE @i INT
DECLARE @zwphase VARCHAR(255)
SET @zwphase=''
SET @i=0
DECLARE xc CURSOR FOR
SELECT phase FROM @tmp3
OPEN xc
FETCH NEXT FROM xc INTO @phase
WHILE @@FETCH_STATUS=0 BEGIN
IF @zwphase<>@phase BEGIN
SET @i=@i+1
SET @zwphase=@phase
END
UPDATE @tmp3 SET phase=STR(@i)+' '+@phase WHERE phase=@phase
FETCH NEXT FROM xc INTO @phase
END
CLOSE xc
deallocate xc
INSERT @tmp1 SELECT lc_phasenr, phase FROM @tmp3
RETURN
end
GO
/****** Object: UserDefinedFunction [dbo].[lc_rpt2] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[lc_rpt2]
(
@produkte VARCHAR(255),
@von varchar(255),
@bis varchar(255),
@produkttypnr int
)
RETURNS @tmp1 TABLE (LC_Produktnr INT, Produkt VARCHAR(255), LC_PhaseNr INT, datum DATETIME)
AS
begin
IF @von='' SET @von='01.01.1900'
IF @bis='' SET @bis='31.12.4712'
IF @produkte='' BEGIN
insert into @tmp1
SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
FROM dbo.LC_Produkt INNER JOIN
dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
ORDER BY Produkt
end
IF @produkte<>'' BEGIN
insert into @tmp1
SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
FROM dbo.LC_Produkt INNER JOIN
dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
and dbo.lc_produkt.lc_produktnr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
ORDER BY Produkt
end
RETURN
end
GO
/****** Object: UserDefinedFunction [dbo].[lc_rpt3] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[lc_rpt3]
(
@produkte VARCHAR(255),
@von varchar(255),
@bis varchar(255),
@produkttypnr int
)
RETURNS @tmp1 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
AS
begin
declare @tmp2 TABLE (LC_Phasenr INT, Phase VARCHAR(255), datum datetime)
declare @tmp3 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
IF @von='' SET @von='01.01.1900'
IF @bis='' SET @bis='31.12.4712'
IF @produkte='' BEGIN
insert into @tmp2 SELECT DISTINCT dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, lc_produkt_phase.datum
FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
ORDER BY lc_produkt_phase.datum
END
IF @produkte<>'' BEGIN
insert into @tmp2 SELECT DISTINCT dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, lc_produkt_phase.datum
FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
and dbo.lc_phase.lc_phasenr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
ORDER BY lc_produkt_phase.datum
end
INSERT @tmp3
SELECT TOP (100) PERCENT LC_Phasenr, Phase
FROM @tmp2
GROUP BY LC_Phasenr, Phase
ORDER BY MIN(datum)
DECLARE @phase VARCHAR(255)
DECLARE @i INT
DECLARE @zwphase VARCHAR(255)
SET @zwphase=''
SET @i=0
DECLARE xc CURSOR FOR
SELECT phase FROM @tmp3
OPEN xc
FETCH NEXT FROM xc INTO @phase
WHILE @@FETCH_STATUS=0 BEGIN
IF @zwphase<>@phase BEGIN
SET @i=@i+1
SET @zwphase=@phase
END
UPDATE @tmp3 SET phase=STR(@i)+' '+@phase WHERE phase=@phase
FETCH NEXT FROM xc INTO @phase
END
CLOSE xc
deallocate xc
INSERT @tmp1 SELECT lc_phasenr, phase FROM @tmp3
RETURN
end
GO
/****** Object: UserDefinedFunction [dbo].[lc_rpt4] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[lc_rpt4]
(
@produkte VARCHAR(255),
@von varchar(255),
@bis varchar(255),
@produkttypnr int
)
RETURNS @tmp1 TABLE (LC_Produktnr INT, Produkt VARCHAR(255), LC_PhaseNr INT, datum DATETIME)
AS
begin
IF @von='' SET @von='01.01.1900'
IF @bis='' SET @bis='31.12.4712'
IF @produkte='' BEGIN
insert into @tmp1
SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
FROM dbo.LC_Produkt INNER JOIN
dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
ORDER BY dbo.LC_Produkt_Phase.Datum,Produkt
end
IF @produkte<>'' BEGIN
insert into @tmp1
SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
FROM dbo.LC_Produkt INNER JOIN
dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
and dbo.lc_phase.lc_phasenr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
ORDER BY dbo.LC_Produkt_Phase.Datum,Produkt
end
RETURN
end
GO
/****** Object: UserDefinedFunction [dbo].[lc_rpt5] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[lc_rpt5]
(
@produkte VARCHAR(255),
@inTagen varchar(255)
)
RETURNS @tmp1 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
AS
begin
DECLARE @von DATETIME
DECLARE @it integer
DECLARE @bis datetime
SET @it=@intagen
IF @intagen <> 0 BEGIN
SET @von=DATEADD(DAY,@it,GETDATE())
SET @von=DATEADD(dd, DATEDIFF(dd, 0, @von), 0)
END
DECLARE @tmp2 TABLE (lc_Produktnr INT, Produkt VARCHAR(255), lc_phasenr INT, Phase VARCHAR(255), datum DATETIME, skey INT IDENTITY)
DECLARE @tmp3 TABLE (lc_Produktnr INT, Produkt VARCHAR(255), lc_phasenr int, phase VARCHAR(255), datumvon DATETIME, datumbis DATETIME, diff INT, skey INT )
INSERT @tmp2
SELECT TOP (100) PERCENT dbo.LC_Produkt_Phase.LC_ProduktNr, dbo.lc_produkt.bezeichnung AS Produkt, dbo.LC_Produkt_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, dbo.LC_Produkt_Phase.Datum
FROM dbo.LC_Produkt_Phase INNER JOIN
dbo.LC_Produkt ON dbo.LC_Produkt_Phase.LC_ProduktNr = dbo.LC_Produkt.LC_ProduktNr INNER JOIN
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
WHERE (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
ORDER BY dbo.LC_Produkt_Phase.LC_ProduktNr, dbo.LC_Produkt_Phase.Datum DESC
INSERT @tmp3
SELECT t1.lc_ProduktNr, t1.produkt, t1.lc_phasenr, t1.Phase , t1.datum AS DatumVon, x.datum AS DatumBis, ISNULL(DATEDIFF(DAY,t1.datum,x.datum),0) AS Diff, t1.skey
FROM @tmp2 t1
OUTER APPLY (
SELECT TOP 1 datum FROM @tmp2 t2 WHERE t2.skey < t1.skey ORDER BY t2.skey desc) x
ORDER BY lc_ProduktNr, t1.datum asc
UPDATE @tmp3 SET diff=0 WHERE diff < 0
DELETE FROM @tmp3 WHERE diff=0
IF @produkte='' BEGIN
insert into @tmp1
SELECT DISTINCT LC_PhaseNr, Phase AS Phase FROM @tmp3
WHERE @von BETWEEN datumvon AND datumbis
END
IF @produkte<>'' BEGIN
insert into @tmp1
SELECT DISTINCT LC_PhaseNr, Phase AS Phase FROM @tmp3
WHERE @von BETWEEN datumvon AND datumbis AND lc_produktnr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
end
RETURN
end
GO
/****** Object: UserDefinedFunction [dbo].[lc_rpt6] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[lc_rpt6]
(
@produkte VARCHAR(255),
@inTagen VARCHAR(255)
)
RETURNS @tmp1 TABLE (LC_Produktnr INT, Produkt VARCHAR(255), LC_PhaseNr INT, datum DATETIME)
AS
begin
DECLARE @von DATETIME
DECLARE @it integer
DECLARE @bis datetime
SET @it=@intagen
IF @intagen <> 0 BEGIN
SET @von=DATEADD(DAY,@it,GETDATE())
SET @von=DATEADD(dd, DATEDIFF(dd, 0, @von), 0)
END
DECLARE @tmp2 TABLE (lc_Produktnr INT, Produkt VARCHAR(255), lc_phasenr INT, Phase VARCHAR(255), datum DATETIME, skey INT IDENTITY)
DECLARE @tmp3 TABLE (lc_Produktnr INT, Produkt VARCHAR(255), lc_phasenr int, phase VARCHAR(255), datumvon DATETIME, datumbis DATETIME, diff INT, skey INT )
INSERT @tmp2
SELECT TOP (100) PERCENT dbo.LC_Produkt_Phase.LC_ProduktNr, dbo.lc_produkt.bezeichnung AS Produkt, dbo.LC_Produkt_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, dbo.LC_Produkt_Phase.Datum
FROM dbo.LC_Produkt_Phase INNER JOIN
dbo.LC_Produkt ON dbo.LC_Produkt_Phase.LC_ProduktNr = dbo.LC_Produkt.LC_ProduktNr INNER JOIN
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
WHERE (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
ORDER BY dbo.LC_Produkt_Phase.LC_ProduktNr, dbo.LC_Produkt_Phase.Datum DESC
INSERT @tmp3
SELECT t1.lc_ProduktNr, t1.produkt, t1.lc_phasenr, t1.Phase , t1.datum AS DatumVon, x.datum AS DatumBis, ISNULL(DATEDIFF(DAY,t1.datum,x.datum),0) AS Diff, t1.skey
FROM @tmp2 t1
OUTER APPLY (
SELECT TOP 1 datum FROM @tmp2 t2 WHERE t2.skey < t1.skey ORDER BY t2.skey desc) x
ORDER BY lc_ProduktNr, t1.datum asc
UPDATE @tmp3 SET diff=0 WHERE diff < 0
DELETE FROM @tmp3 WHERE diff=0
IF @produkte='' BEGIN
insert into @tmp1
SELECT DISTINCT LC_Produktnr, Produkt, lc_phasenr, datumvon
FROM @tmp3
WHERE @von BETWEEN datumvon AND datumbis
ORDER BY Produkt
end
IF @produkte<>'' BEGIN
insert into @tmp1
SELECT DISTINCT LC_Produktnr, Produkt, lc_phasenr, datumvon
FROM @tmp3
WHERE @von BETWEEN datumvon AND datumbis
AND lc_produktnr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
ORDER BY Produkt
end
RETURN
end
GO
/****** Object: UserDefinedFunction [dbo].[search_vertragselement] Script Date: 27.01.2017 08:56:19 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[search_vertragselement]
(@vertragselementnr int, @suchstring varchar(255))
RETURNS int
AS
BEGIN
declare @cnt int
set @suchstring='%'+@suchstring+'%'
select @cnt=count(vertragselementnr) from dbo.vertragselement where vertragselementnr=@vertragselementnr and
(bezeichnung like @suchstring or beschreibung like @suchstring or nummer like @suchstring or suchbegriffe like @suchstring)
return @cnt
END
GO
Reference in new issue View Git Blame Copy Permalink