Web forum is in read-only mode. Login as active registered customer for write access
  Forum Search   New Posts New Posts

Support for importing Functions

 Post Reply Post Reply
Author
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2690
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post Topic: Support for importing Functions
    Posted: 29 Jun 2011 at 7:54am
Hi Steve,
thank you for your feedback. We have indeed confirmed a problem with functions, we will fix this issue and include the fix in the next release.
Back to Top
Jacobsson Steve View Drop Down
New Member
New Member
Avatar

Joined: 30 Sep 2010
Posts: 2
Post Options Post Options   Quote Jacobsson Steve Quote  Post ReplyReply Direct Link To This Post Posted: 28 Jun 2011 at 12:24am

Hi Wagner,

Below you will find the complete script to create a specific demo database including a few functions. The database server is MS SQL Server 2008 EXPRESS and the script was created using the SSMS "Generate scripts" functionality.
Looking at the TMS Data Modeler application (version 1.7.1.0) there is nowhere that the "function" concept exists; there are tables, relationships, stored procedures, and views but no functions.
Regards,
Steve

USE [ProjektPlanering]
GO
/****** Object: User [ProjektPlanering] Script Date: 06/28/2011 06:07:06 ******/
CREATE USER [ProjektPlanering] FOR LOGIN [ProjektPlanering] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object: Table [dbo].[Ledighet] Script Date: 06/28/2011 06:07:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Ledighet](
[LedighetID] [int] IDENTITY(1,1) NOT NULL,
[LedighetDatum] [datetime] NOT NULL,
[LedighetKod] [int] NOT NULL,
[Timmar] [float] NOT NULL,
[Beskrivning] [varchar](32) NULL,
CONSTRAINT [PrimaryKey10] PRIMARY KEY CLUSTERED
(
[LedighetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 06/28/2011 06:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE Function [dbo].[Split](@InputText Varchar(4000), @Delimiter Varchar(10))
RETURNS @Array TABLE (Value Varchar(4000))
AS
BEGIN
DECLARE @Pos Int, @End Int, @TextLength Int, @DelimLength Int

SET @TextLength = DataLength(@InputText)

IF @TextLength = 0
RETURN

SET @Pos = 1
SET @DelimLength = DataLength(@Delimiter)

IF @DelimLength = 0
BEGIN -- Each character in its own row
WHILE @Pos <= @TextLength
BEGIN
INSERT @Array (Value) VALUES (SubString(@InputText,@Pos,1))
SET @Pos = @Pos + 1
END
END
ELSE
BEGIN -- Tack on delimiter to 'see' the last token
-- SET @InputText = @InputText + @Delimiter -- Find the end character of the first token
SET @End = CharIndex(@Delimiter, @InputText)
WHILE @End > 0
BEGIN -- End > 0, a delimiter was found: there is a(nother) token
INSERT @Array (Value) VALUES (SubString(@InputText, @Pos, @End - @Pos)) -- Set next search to start after the previous token
SET @Pos = @End + @DelimLength -- Find the end character of the next token
SET @End = CharIndex(@Delimiter, @InputText, @Pos)
END
END
RETURN
END
GO
/****** Object: Table [dbo].[Installningar] Script Date: 06/28/2011 06:07:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Installningar](
[InstallningarID] [int] IDENTITY(0,1) NOT NULL,
[SendOrderPerson] [bit] NOT NULL,
[SendOrderClient] [bit] NOT NULL,
[ConfirmClient] [bit] NOT NULL,
[RecallPerson] [bit] NOT NULL,
[RecallClient] [bit] NOT NULL,
[DefaultWorkingWeek] [float] NOT NULL,
[SendOrderUnitHead] [bit] NOT NULL,
[ConfirmUnitHead] [bit] NOT NULL,
[RecallUnitHead] [bit] NOT NULL,
[NotifyClient] [bit] NOT NULL,
[NotifyUnitHead] [bit] NOT NULL,
[NotifyPerson] [bit] NOT NULL,
[TimmarHalvdag] [float] NOT NULL,
[TimmarHeldag] [float] NOT NULL,
[TimmarKlamdag] [float] NOT NULL,
CONSTRAINT [PrimaryKey_Installningar] PRIMARY KEY CLUSTERED
(
[InstallningarID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[GRANT_EXECUTE] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GRANT_EXECUTE]
AS
BEGIN
SET NOCOUNT ON;

GRANT EXECUTE ON Arbetstid_Delete TO ProjektPlanering
GRANT EXECUTE ON Arbetstid_Insert TO ProjektPlanering
GRANT EXECUTE ON Arbetstid_Select_ByPerson TO ProjektPlanering
GRANT EXECUTE ON Arbetstid_Select_LatestReportedWeek TO ProjektPlanering
GRANT EXECUTE ON Arbetstid_Update TO ProjektPlanering
GRANT EXECUTE ON ArbetstidTyp_Delete TO ProjektPlanering
GRANT EXECUTE ON ArbetstidTyp_Insert TO ProjektPlanering
GRANT EXECUTE ON ArbetstidTyp_Select_All TO ProjektPlanering
GRANT EXECUTE ON ArbetstidTyp_Update TO ProjektPlanering
GRANT SELECT ON ArbetstidTypKod TO ProjektPlanering
GRANT EXECUTE ON ArbetstidTyp_Select_AsList TO ProjektPlanering
GRANT EXECUTE ON Bestallarenhet_Delete TO ProjektPlanering
GRANT EXECUTE ON Bestallarenhet_Insert TO ProjektPlanering
GRANT EXECUTE ON Bestallarenhet_Select_All TO ProjektPlanering
GRANT EXECUTE ON Bestallarenhet_Select_AllAsList TO ProjektPlanering
GRANT EXECUTE ON Bestallarenhet_Update TO ProjektPlanering
GRANT EXECUTE ON Bestallning_Insert TO ProjektPlanering
GRANT EXECUTE ON Bestallning_Select_All_ByPerson TO ProjektPlanering
GRANT EXECUTE ON Bestallning_Select_All_Closed TO ProjektPlanering
GRANT EXECUTE ON Bestallning_Select_All_Placed TO ProjektPlanering
GRANT EXECUTE ON Bestallning_Select_All_Planned TO ProjektPlanering
GRANT EXECUTE ON Bestallning_Select_ByPerson TO ProjektPlanering
GRANT EXECUTE ON Installningar_Select_EmailOptions_Confirm TO ProjektPlanering
GRANT EXECUTE ON Installningar_Select_EmailOptions_NotifyPerson TO ProjektPlanering
GRANT EXECUTE ON Installningar_Select_EmailOptions_Recall TO ProjektPlanering
GRANT EXECUTE ON Installningar_Select_EmailOptions_SendOrder TO ProjektPlanering
GRANT EXECUTE ON Bestallning_Update TO ProjektPlanering
GRANT EXECUTE ON Bestallning_Update_ArchiveState TO ProjektPlanering
GRANT EXECUTE ON Bestallning_Update_FinalizationState TO ProjektPlanering
GRANT EXECUTE ON Bestallning_Update_NotifyPersonnel TO ProjektPlanering
GRANT EXECUTE ON Bestallning_Update_Personnel TO ProjektPlanering
GRANT EXECUTE ON Bestallning_Update_PlaceOrder TO ProjektPlanering
GRANT EXECUTE ON Enhet_Delete TO ProjektPlanering
GRANT EXECUTE ON Enhet_Insert TO ProjektPlanering
GRANT EXECUTE ON Enhet_Select TO ProjektPlanering
GRANT EXECUTE ON Enhet_Select_AllAsList TO ProjektPlanering
GRANT EXECUTE ON Enhet_Update TO ProjektPlanering
GRANT EXECUTE ON Funktion_Delete TO ProjektPlanering
GRANT EXECUTE ON Funktion_Insert TO ProjektPlanering
GRANT EXECUTE ON Funktion_Select_All TO ProjektPlanering
GRANT EXECUTE ON Funktion_Select_AllAsList TO ProjektPlanering
GRANT EXECUTE ON Funktion_Select_ByPerson TO ProjektPlanering
GRANT EXECUTE ON Funktion_Update TO ProjektPlanering
GRANT EXECUTE ON Personal_Delete TO ProjektPlanering
GRANT EXECUTE ON Personal_Insert TO ProjektPlanering
GRANT EXECUTE ON Personal_Select_AllAsList TO ProjektPlanering
GRANT EXECUTE ON Personal_Select_ByUserID TO ProjektPlanering
GRANT EXECUTE ON Personal_Update TO ProjektPlanering
GRANT EXECUTE ON PersonalFunction_Select_ByFunction TO ProjektPlanering
GRANT EXECUTE ON PersonalFunktion_Select_ByFunction TO ProjektPlanering
GRANT EXECUTE ON PersonalFunktion_Delete TO ProjektPlanering
GRANT EXECUTE ON PersonalFunktion_Insert TO ProjektPlanering
GRANT EXECUTE ON Personal_Select_All TO ProjektPlanering
GRANT EXECUTE ON Personal_Select_AllWithNamn TO ProjektPlanering
GRANT EXECUTE ON Personal_Select_ClientPersonnel TO ProjektPlanering
GRANT EXECUTE ON Personal_Select_ClientPersonnelAsList TO ProjektPlanering
GRANT EXECUTE ON Personal_Select_IsAuthorizedClient TO ProjektPlanering
GRANT EXECUTE ON Personal_Select_IsAuthorizedPerson TO ProjektPlanering
GRANT EXECUTE ON Projekt_Select_AllAsList TO ProjektPlanering
GRANT EXECUTE ON Projekt_Delete TO ProjektPlanering
GRANT EXECUTE ON Projekt_Insert TO ProjektPlanering
GRANT EXECUTE ON Projekt_Select_All TO ProjektPlanering
GRANT EXECUTE ON Projekt_Update TO ProjektPlanering
GRANT EXECUTE ON Installningar_Select TO ProjektPlanering
GRANT EXECUTE ON Installningar_Update TO ProjektPlanering

END
GO
/****** Object: StoredProcedure [dbo].[GetHolidayEve] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Find Saturday on or after input date and return date result - 1 day>
-- GRANT EXEUTE ON GetHolidayEve TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[GetHolidayEve]
@DateLowerLimit DATETIME OUTPUT
AS
BEGIN
SET NOCOUNT ON;

SELECT @DateLowerLimit = DATEADD(d, -1, DATEADD(d,
CASE DATEPART(dw, @DateLowerLimit)
WHEN 7 THEN 6
WHEN 6 THEN 0
WHEN 5 THEN 1
WHEN 4 THEN 2
WHEN 3 THEN 3
WHEN 2 THEN 4
WHEN 1 THEN 5
END,
@DateLowerLimit))
END
GO
/****** Object: StoredProcedure [dbo].[GetEasterDay] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetEasterDay]
@Yr INT,
@EasterDay DATETIME OUTPUT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @a INT
DECLARE @b INT
DECLARE @c INT
DECLARE @d INT
DECLARE @e INT
DECLARE @iDay INT
DECLARE @iMonth INT

SET @a = @Yr % 19
SET @b = @Yr % 4
SET @c = @Yr % 7
SET @d = (19 * @a + 24) % 30
SET @e = ((2 * @b) + (4 * @c) + (6 * @d) + 5) % 7
SET @iDay = 22 + @d + @e
IF @iDay = 57
SET @iDay = @iDay - 7
IF @iDay = 57 AND @d = 28 AND @e = 6 AND @a > 10
SET @iDay = @iDay - 7
IF @iDay > 31
BEGIN
SET @iDay = @iDay - 31
SET @iMonth = 4
END
ELSE
SET @iMonth = 3

SET @EasterDay = DATEADD(mm,(@Yr-1900)* 12 + @iMonth - 1,0) + (@iDay-1)
END
GO
/****** Object: Table [dbo].[Enhet] Script Date: 06/28/2011 06:07:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Enhet](
[EnhetID] [int] IDENTITY(1,1) NOT NULL,
[EnhetNamn] [nvarchar](48) NOT NULL,
[EnhetschefID] [int] NULL,
CONSTRAINT [PrimaryKey8] PRIMARY KEY CLUSTERED
(
[EnhetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: UserDefinedFunction [dbo].[fnCvtYearWeekNoToDate] Script Date: 06/28/2011 06:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- SELECT dbo.fnCvtYearWeekNoToDate(2011, 9)
-- =============================================
CREATE FUNCTION [dbo].[fnCvtYearWeekNoToDate]
(
@baseYear CHAR(4),
@weekNo INT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @targetWeekDay INT
DECLARE @baseDate DATETIME
DECLARE @resultDate DATETIME

SET @baseDate = CONVERT(DATETIME, @baseYear + '0101')
SET @targetWeekDay = 1

SELECT @resultDate = @baseDate + (14 + @targetWeekDay - datepart(dw, @baseDate))%7 + (@weekNo - 1)*7

RETURN @resultDate
END
GO
/****** Object: UserDefinedFunction [dbo].[fnGetFirstDateofMonth] Script Date: 06/28/2011 06:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnGetFirstDateofMonth]
(@Date as DateTime)
RETURNS DateTime AS
BEGIN
DECLARE @FirstDate DATETIME

SET @FirstDate = DATEADD(Day, 1, @Date - Day(@Date) + 1) -1

RETURN @FirstDate
END
GO
/****** Object: Table [dbo].[FastaHelgdagar] Script Date: 06/28/2011 06:07:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FastaHelgdagar](
[ManadDag] [char](6) NOT NULL,
[LedighetKod] [int] NOT NULL,
[Beskrivning] [varchar](32) NULL,
CONSTRAINT [PrimaryKey11] PRIMARY KEY CLUSTERED
(
[ManadDag] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: UserDefinedFunction [dbo].[fnTruncateHellip] Script Date: 06/28/2011 06:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnTruncateHellip]
(
@StringToCheck NVARCHAR(64)
)
RETURNS NVARCHAR(64)
AS
BEGIN
DECLARE @ResultVar NVARCHAR(64)

IF (LEN(@StringToCheck) > 24)
SET @ResultVar = LEFT(@StringToCheck, 21) + '...'
ELSE
SET @ResultVar = @StringToCheck

RETURN @ResultVar
END
GO
/****** Object: UserDefinedFunction [dbo].[fnIsWeekday] Script Date: 06/28/2011 06:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnIsWeekday]
(
@Date DATETIME
)
RETURNS BIT
AS
BEGIN
DECLARE @dtFirst INT
DECLARE @dtWeek INT
DECLARE @isWkday BIT


SET @dtFirst = @@datefirst -1
SET @dtWeek = DATEPART(weekday, @Date) - 1

IF (@dtFirst + @dtWeek) % 7 NOT IN (5, 6)
SET @isWkday = 1
ELSE
SET @isWkday = 0

RETURN @isWkday
END
GO
/****** Object: UserDefinedFunction [dbo].[fnGetLastDateofMonth] Script Date: 06/28/2011 06:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnGetLastDateofMonth]
(@Date as DateTime)
RETURNS DATETIME AS
BEGIN

DECLARE @LastDate DATETIME
SET @LastDate = DATEADD(Month, 1, @Date - Day(@Date) + 1) -1

RETURN @LastDate
END
GO
/****** Object: UserDefinedFunction [dbo].[fnGetISOWeek] Script Date: 06/28/2011 06:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnGetISOWeek] (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ISOweek int;
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.fnGetISOWeek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1;
RETURN(@ISOweek);
END;
GO
/****** Object: Table [dbo].[ArbetstidTypKod] Script Date: 06/28/2011 06:07:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ArbetstidTypKod](
[ArbetstidTypKodID] [int] IDENTITY(1,1) NOT NULL,
[ArbetstidTypKod] [nvarchar](24) NOT NULL,
[ArbetstidTypSystem] [bit] NOT NULL,
CONSTRAINT [PrimaryKey5] PRIMARY KEY CLUSTERED
(
[ArbetstidTypKodID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Avdelning] Script Date: 06/28/2011 06:07:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Avdelning](
[AvdelningID] [int] IDENTITY(1,1) NOT NULL,
[AvdelningNamn] [varchar](64) NOT NULL,
CONSTRAINT [Avdelning_PRI_IDX] PRIMARY KEY CLUSTERED
(
[AvdelningID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Bestallarenhet] Script Date: 06/28/2011 06:07:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Bestallarenhet](
[BestEnhetID] [int] IDENTITY(1,1) NOT NULL,
[BestEnhetNamn] [nvarchar](64) NOT NULL,
CONSTRAINT [PrimaryKey4] PRIMARY KEY CLUSTERED
(
[BestEnhetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[Avdelning_Update] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Avdelning_Update TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Avdelning_Update]
@AvdelningID INT,
@AvdelningNamn VARCHAR(64)
AS
BEGIN
SET NOCOUNT ON;

UPDATE Avdelning
SET AvdelningNamn = @AvdelningNamn
WHERE AvdelningID = @AvdelningID
END
GO
/****** Object: StoredProcedure [dbo].[Avdelning_Select_CheckListbox] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Avdelning_Select_CheckListbox TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Avdelning_Select_CheckListbox]
AS
BEGIN
SET NOCOUNT ON;

SELECT -1 AS AvdelningID, '(Samtliga avdelningar)' AS AvdelningNamn
UNION
SELECT *
FROM Avdelning
ORDER BY AvdelningNamn
END
GO
/****** Object: Table [dbo].[ArbetstidTyp] Script Date: 06/28/2011 06:07:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ArbetstidTyp](
[ArbetsTypID] [int] IDENTITY(0,1) NOT NULL,
[ArbetsTypNamn] [nvarchar](128) NOT NULL,
[ArbetstidTypKodID] [int] NOT NULL,
[ArbetstidTypSystem] [bit] NOT NULL,
CONSTRAINT [PrimaryKey6] PRIMARY KEY CLUSTERED
(
[ArbetsTypID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[ArbetstidTypKod_Select] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON ArbetstidTypKod_Select TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[ArbetstidTypKod_Select]
AS
BEGIN
SET NOCOUNT ON;

SELECT ArbetstidTypKodID, ArbetstidTypKod
FROM ArbetstidTypKod
WHERE ArbetstidTypSystem = 'False'

END
GO
/****** Object: UserDefinedFunction [dbo].[fnGetFirstDateOfYear] Script Date: 06/28/2011 06:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnGetFirstDateOfYear]
(
@Date datetime
)
RETURNS DATETIME
AS
BEGIN
DECLARE @weekNum INT

IF (dbo.fnGetISOWeek(@Date) > 51)
SET @weekNum = 2
ELSE
SET @weekNum = 1

DECLARE @weekDate DATETIME
DECLARE @weekStartDate DATETIME
DECLARE @toDay1 INT


SET @weekDate = DATEADD(WW, @weekNum - 1, @Date)
SET @toDay1 = DATEPART(weekday, @weekDate) - 1
SET @weekStartDate = DATEADD(d, -@toDay1, @weekDate)

RETURN @weekStartDate
END
GO
/****** Object: StoredProcedure [dbo].[Enhet_Update] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Enhet_Update TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Enhet_Update]
@EnhetID INT,
@EnhetNamn NVARCHAR(48),
@EnhetschefID INT
AS
BEGIN
SET NOCOUNT ON;

IF (@EnhetschefID = 0)
SET @EnhetschefID = NULL

UPDATE Enhet
SET EnhetNamn = @EnhetNamn, EnhetschefID = @EnhetschefID
WHERE EnhetID = @EnhetID
END
GO
/****** Object: StoredProcedure [dbo].[Enhet_Select_AllAsList] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Enhet_Select_AsList TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Enhet_Select_AllAsList]
AS
BEGIN
SET NOCOUNT ON;

SELECT 0 AS EnhetID, NULL AS EnhetNamn
UNION
SELECT EnhetID, EnhetNamn
FROM Enhet
ORDER BY EnhetNamn
END
GO
/****** Object: StoredProcedure [dbo].[Enhet_Select] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Enhet_Select TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Enhet_Select]
AS
BEGIN
SET NOCOUNT ON;

SELECT *
FROM Enhet
ORDER BY EnhetNamn
END
GO
/****** Object: StoredProcedure [dbo].[Enhet_Insert] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Enhet_Insert TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Enhet_Insert]
@EnhetNamn NVARCHAR(48),
@EnhetschefID INT
AS
BEGIN
SET NOCOUNT ON;

IF (@EnhetschefID = 0)
SET @EnhetschefID = NULL

INSERT Enhet
VALUES(@EnhetNamn, @EnhetschefID)
END
GO
/****** Object: StoredProcedure [dbo].[Enhet_Delete] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Enhet_Delete TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Enhet_Delete]
@EnhetID INT
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM Enhet
WHERE EnhetID = @EnhetID
END
GO
/****** Object: StoredProcedure [dbo].[Avdelning_Select_AllAsList] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Avdelning_Select_AllAsList TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Avdelning_Select_AllAsList]
AS
BEGIN
SET NOCOUNT ON;

SELECT NULL AS AvdelningID, NULL AS AvdelningNamn
UNION
SELECT AvdelningID, AvdelningNamn
FROM Avdelning
ORDER BY AvdelningNamn
END
GO
/****** Object: StoredProcedure [dbo].[Avdelning_Select] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Avdelning_Select TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Avdelning_Select]
AS
BEGIN
SET NOCOUNT ON;

SELECT *
FROM Avdelning
ORDER BY AvdelningNamn
END
GO
/****** Object: StoredProcedure [dbo].[Avdelning_Insert] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Avdelning_Insert TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Avdelning_Insert]
@AvdelningNamn VARCHAR(64)
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Avdelning
VALUES(@AvdelningNamn)
END
GO
/****** Object: StoredProcedure [dbo].[Avdelning_Delete] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Avdelning_Delete TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Avdelning_Delete]
@AvdelningID INT
AS
BEGIN
SET NOCOUNT ON;

DELETE FROM Avdelning
WHERE AvdelningID = @AvdelningID
END
GO
/****** Object: StoredProcedure [dbo].[Bestallarenhet_Update] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- GRANT EXECUTE ON Bestallarenhet_Update TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Bestallarenhet_Update]
@BestEnhetID INT,
@BestEnhetNamn NVARCHAR(48)
AS
BEGIN
SET NOCOUNT ON;

UPDATE Bestallarenhet
SET BestEnhetNamn = @BestEnhetNamn
WHERE BestEnhetID = @BestEnhetID
END
GO
/****** Object: StoredProcedure [dbo].[Bestallarenhet_Select_AllAsList] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallarenhet_Select_AllAsList TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Bestallarenhet_Select_AllAsList]
AS
BEGIN
SET NOCOUNT ON;

CREATE TABLE #temp(
BestEnhetID INT,
BestEnhetNamn NVARCHAR(64))

INSERT INTO #temp
VALUES(0, '')

INSERT #temp
SELECT *
FROM Bestallarenhet
ORDER BY BestEnhetNamn

SELECT *
FROM #temp

DROP TABLE #temp
END
GO
/****** Object: StoredProcedure [dbo].[Bestallarenhet_Select_All] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallarenhet_Select_All TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Bestallarenhet_Select_All]
AS
BEGIN
SET NOCOUNT ON;

SELECT *
FROM Bestallarenhet
ORDER BY BestEnhetNamn
END
GO
/****** Object: StoredProcedure [dbo].[Bestallarenhet_Insert] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallarenhet_Insert TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Bestallarenhet_Insert]
@BestEnhetNamn NVARCHAR(48)
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Bestallarenhet
VALUES(@BestEnhetNamn)
END
GO
/****** Object: StoredProcedure [dbo].[Bestallarenhet_Delete] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallarenhet_Delete TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Bestallarenhet_Delete]
@BestEnhetID INT
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM Bestallarenhet
WHERE BestEnhetID = @BestEnhetID
END
GO
/****** Object: Table [dbo].[Funktion] Script Date: 06/28/2011 06:07:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Funktion](
[FunktionID] [int] IDENTITY(1,1) NOT NULL,
[FunktionNamn] [nvarchar](128) NOT NULL,
[EnhetID] [int] NULL,
CONSTRAINT [PrimaryKey2] PRIMARY KEY CLUSTERED
(
[FunktionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Personal] Script Date: 06/28/2011 06:07:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Personal](
[PersonalID] [int] IDENTITY(1,1) NOT NULL,
[KortNamn] [varchar](16) NOT NULL,
[ForNamn] [nvarchar](32) NOT NULL,
[EfterNamn] [nvarchar](48) NOT NULL,
[IsBestallarPerson] [bit] NOT NULL,
[VeckoArbetstid] [float] NOT NULL,
[Debittid] [float] NOT NULL,
[Epostadress] [nvarchar](128) NOT NULL,
[Aktiverad] [bit] NOT NULL,
[AvdelningID] [int] NULL,
CONSTRAINT [PrimaryKey1] PRIMARY KEY CLUSTERED
(
[PersonalID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: StoredProcedure [dbo].[Ledighet_Update] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Ledighet_Update TO ProjektPlanering
-- Ledighet_Update '2011-01-01', 2, 99
-- =============================================
CREATE PROCEDURE [dbo].[Ledighet_Update]
@LedighetID INT,
@LedighetDatum DATETIME,
@LedighetKod INT,
@Timmar FLOAT = NULL,
@Beskrivning VARCHAR(32)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @NumberOfHours FLOAT

IF (@Timmar IS NULL)
SELECT TOP 1 @Timmar =
CASE @LedighetKod
WHEN 0 THEN TimmarHalvdag
WHEN 1 THEN TimmarHeldag
ELSE TimmarKlamdag
END
FROM Installningar

UPDATE Ledighet
SET LedighetDatum = @LedighetDatum, LedighetKod = @LedighetKod, Timmar = @Timmar,
Beskrivning = @Beskrivning
WHERE LedighetID = @LedighetID
END
GO
/****** Object: StoredProcedure [dbo].[Ledighet_Select_AllByMonth] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Ledighet_Select_All TO ProjektPlanering
-- Ledighet_Select_All '2011-06-09'
-- =============================================
CREATE PROCEDURE [dbo].[Ledighet_Select_AllByMonth]
@VisibleDate DATETIME
AS
BEGIN
SET NOCOUNT ON;

SELECT *
FROM Ledighet
WHERE LedighetDatum BETWEEN dbo.fnGetFirstDateofMonth(@VisibleDate) AND dbo.fnGetLastDateofMonth(@VisibleDate)
ORDER BY LedighetDatum
END
GO
/****** Object: StoredProcedure [dbo].[Ledighet_Insert] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Ledighet_Insert TO ProjektPlanering
-- Ledighet_Insert '2011-01-01', 2, 99
-- =============================================
CREATE PROCEDURE [dbo].[Ledighet_Insert]
@LedighetDatum DATETIME,
@LedighetKod INT,
@Timmar FLOAT = NULL,
@Beskrivning VARCHAR(32)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @NumberOfHours FLOAT

IF (@Timmar IS NULL)
SELECT TOP 1 @Timmar =
CASE @LedighetKod
WHEN 0 THEN TimmarHalvdag
WHEN 1 THEN TimmarHeldag
ELSE TimmarKlamdag
END
FROM Installningar

INSERT INTO Ledighet
VALUES(@LedighetDatum, @LedighetKod, @Timmar, @Beskrivning)
END
GO
/****** Object: StoredProcedure [dbo].[Ledighet_Delete] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Ledighet_Delete TO ProjektPlanering
-- Ledighet_Update '2011-01-01', 2, 99
-- =============================================
CREATE PROCEDURE [dbo].[Ledighet_Delete]
@LedighetID INT
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM Ledighet
WHERE LedighetID = @LedighetID
END
GO
/****** Object: StoredProcedure [dbo].[Ledighet_Create_Holiday] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Ledighet_Create_Holiday TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Ledighet_Create_Holiday]
@HolidayDate DATETIME,
@HolidayType INT,
@HolidayDescription VARCHAR(32)
AS
BEGIN
SET NOCOUNT ON

SET DATEFIRST 1

DECLARE @Timmar FLOAT

IF (dbo.fnIsWeekday(@HolidayDate) = 1)
BEGIN
SELECT @Timmar =
CASE @HolidayType
WHEN 0 THEN TimmarHalvdag
WHEN 1 THEN TimmarHeldag
ELSE TimmarKlamdag
END
FROM Installningar

IF EXISTS(SELECT LedighetDatum FROM Ledighet WHERE LedighetDatum = @HolidayDate)
UPDATE Ledighet
SET LedighetKod = @HolidayType, Timmar = @Timmar, Beskrivning = @HolidayDescription
WHERE (LedighetDatum = @HolidayDate)
ELSE
INSERT INTO Ledighet
VALUES(@HolidayDate, @HolidayType, @Timmar, @HolidayDescription)
END
END
GO
/****** Object: StoredProcedure [dbo].[Installningar_Select] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Installningar_Select TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Installningar_Select]
AS
BEGIN
SET NOCOUNT ON;

SELECT TOP 1 *
FROM Installningar
END
GO
/****** Object: StoredProcedure [dbo].[PREPARE_DATABASE] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PREPARE_DATABASE]
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO ArbetstidTypKod
VALUES('Ledighet')

INSERT INTO ArbetstidTypKod
VALUES('Arbetstid')

INSERT INTO ArbetstidTypKod
VALUES('Frånvaro')

END
GO
/****** Object: StoredProcedure [dbo].[Installningar_Update] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Installningar_Update TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Installningar_Update]
@InstallningarID INT,
@SendOrderPerson BIT,
@SendOrderClient BIT,
@SendOrderUnitHead BIT,
@ConfirmClient BIT,
@ConfirmUnitHead BIT,
@RecallPerson BIT,
@RecallClient BIT,
@RecallUnitHead BIT,
@NotifyClient BIT,
@NotifyPerson BIT,
@NotifyUnitHead BIT,
@DefaultWorkingWeek FLOAT,
@TimmarHalvdag FLOAT,
@TimmarHeldag FLOAT,
@TimmarKlamdag FLOAT
AS
BEGIN
SET NOCOUNT ON;

UPDATE Installningar
SET SendOrderPerson = @SendOrderPerson, SendOrderClient = @SendOrderClient, SendOrderUnitHead = @SendOrderUnitHead,
ConfirmClient = @ConfirmClient, ConfirmUnitHead = @ConfirmUnitHead,
RecallPerson = @RecallPerson, RecallClient = @RecallClient, RecallUnitHead = @RecallUnitHead,
NotifyPerson = @NotifyPerson, NotifyClient = @NotifyClient, NotifyUnitHead = @NotifyUnitHead,
DefaultWorkingWeek = @DefaultWorkingWeek, TimmarHalvdag = @TimmarHalvdag, TimmarHeldag = @TimmarHeldag,
TimmarKlamdag = @TimmarKlamdag
WHERE InstallningarID = @InstallningarID
END
GO
/****** Object: Table [dbo].[Projekt] Script Date: 06/28/2011 06:07:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Projekt](
[ProjektID] [int] IDENTITY(1,1) NOT NULL,
[FaktiskaProjektID] [int] NULL,
[ProjektNamn] [nvarchar](128) NULL,
[ArbetsNamn] [nvarchar](128) NULL,
[BestEnhetID] [int] NOT NULL,
[ProjektLedareID] [int] NULL,
CONSTRAINT [PrimaryKey3] PRIMARY KEY CLUSTERED
(
[ProjektID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[Personal_Update] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Personal_Update TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Personal_Update]
@PersonalID INT,
@KortNamn VARCHAR(16),
@ForNamn NVARCHAR(32),
@EfterNamn NVARCHAR(48),
@IsBestallarPerson BIT,
@VeckoArbetstid FLOAT,
@Debittid FLOAT,
@Epostadress NVARCHAR(128),
@Aktiverad BIT,
@AvdelningID INT
AS
BEGIN
SET NOCOUNT ON;

UPDATE Personal
SET KortNamn = LOWER(@KortNamn), ForNamn = @ForNamn, EfterNamn = @EfterNamn,
IsBestallarPerson = ISNULL(@IsBestallarPerson, 0),
VeckoArbetstid = @VeckoArbetstid, Debittid = @Debittid, Epostadress = @Epostadress,
Aktiverad = @Aktiverad, AvdelningID = @AvdelningID
WHERE PersonalID = @PersonalID
END
GO
/****** Object: StoredProcedure [dbo].[Personal_Select_IsAuthorizedPerson] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Personal_Select_IsAuthorizedPerson TO ProjektPlanering
-- Personal_Select_IsAuthorizedPerson 'jeneMn'
-- =============================================
CREATE PROCEDURE [dbo].[Personal_Select_IsAuthorizedPerson]
@KortNamn NVARCHAR(16)
AS
BEGIN
SET NOCOUNT ON;

SELECT PersonalID, ForNamn + ' ' + EfterNamn AS PersonalNamn
FROM Personal
WHERE (KortNamn = @KortNamn)
AND (Aktiverad = 'True')
END
GO
/****** Object: StoredProcedure [dbo].[Personal_Select_IsAuthorizedClient] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Personal_Select_IsAuthorizedClient TO ProjektPlanering
-- Personal_Select_IsAuthorizedClient 'karan'
-- =============================================
CREATE PROCEDURE [dbo].[Personal_Select_IsAuthorizedClient]
@KortNamn NVARCHAR(16)
AS
BEGIN
SET NOCOUNT ON;

SELECT PersonalID, ForNamn + ' ' + EfterNamn AS PersonalNamn
FROM Personal
WHERE (KortNamn = @KortNamn)
AND (Aktiverad = 'True')
AND (IsBestallarPerson = 'True')
END
GO
/****** Object: StoredProcedure [dbo].[Personal_Select_ClientPersonnelAsList] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON [Personal_Select_ClientPersonnelAsList] TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Personal_Select_ClientPersonnelAsList]
AS
BEGIN
SET NOCOUNT ON;

SELECT 0 AS PersonalID, NULL AS Fullname
UNION
SELECT PersonalID, EfterNamn + ', ' + ForNamn AS Fullname
FROM Personal
WHERE IsBestallarPerson = 'True'
ORDER BY Fullname
END
GO
/****** Object: StoredProcedure [dbo].[Personal_Select_ClientPersonnel] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Personal_Select_ClientPersonnel TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Personal_Select_ClientPersonnel]
AS
BEGIN
SET NOCOUNT ON;

SELECT PersonalID AS BestPersonID, EfterNamn + ', ' + ForNamn AS Fullname
FROM Personal
WHERE IsBestallarPerson = 'True'
ORDER BY EfterNamn
END
GO
/****** Object: StoredProcedure [dbo].[Personal_Select_ByUserID] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Personal_Select_ByUserID TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Personal_Select_ByUserID]
@KortNamn VARCHAR(16)
AS
BEGIN
SET NOCOUNT ON;

SELECT *
FROM Personal
WHERE KortNamn = @KortNamn
END
GO
/****** Object: StoredProcedure [dbo].[Personal_Select_AllWithNamn] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Personal_Select_AllWithNamn TO ProjektPlanering
-- Personal_Select_AllWithNamn 'True'
-- =============================================
CREATE PROCEDURE [dbo].[Personal_Select_AllWithNamn]
@NotAppointed BIT = 'False'
AS
BEGIN
SET NOCOUNT ON;

IF (@NotAppointed = 'True')
BEGIN
CREATE TABLE #temp(PersonalID INT, PersonalNamn NVARCHAR(96))

INSERT INTO #temp
VALUES(0, 'ej tillsatt')

INSERT INTO #temp
SELECT PersonalID, EfterNamn + ', ' + ForNamn AS PersonalNamn
FROM Personal
ORDER BY PersonalNamn

SELECT *
FROM #temp

DROP TABLE #temp
END
ELSE
SELECT PersonalID, EfterNamn + ', ' + ForNamn AS PersonalNamn
FROM Personal
ORDER BY PersonalNamn
END
GO
/****** Object: StoredProcedure [dbo].[Personal_Select_AllAsList] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Personal_Select_AllAsList TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Personal_Select_AllAsList]
AS
BEGIN
SET NOCOUNT ON;

SELECT 0 as PersonalID, NULL as Fullname
UNION
SELECT PersonalID, EfterNamn + ', ' + ForNamn AS Fullname
FROM Personal
ORDER BY Fullname
END
GO
/****** Object: StoredProcedure [dbo].[Personal_Select_All] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Personel_Select_All TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Personal_Select_All]
AS
BEGIN
SET NOCOUNT ON;

SELECT *
FROM Personal
ORDER BY KortNamn
END
GO
/****** Object: StoredProcedure [dbo].[Personal_Insert_Department] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Personal_Insert_Avdelning TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Personal_Insert_Department]
@PersonalID INT,
@AvdelningID INT
AS
BEGIN
SET NOCOUNT ON;

UPDATE Personal
SET AvdelningID = @AvdelningID
WHERE PersonalID = @PersonalID
END
GO
/****** Object: StoredProcedure [dbo].[Personal_Insert] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Personal_Insert TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Personal_Insert]
@KortNamn VARCHAR(16),
@ForNamn NVARCHAR(32),
@EfterNamn NVARCHAR(48),
@IsBestallarPerson BIT,
@VeckoArbetstid FLOAT,
@Debittid FLOAT,
@Epostadress NVARCHAR(128),
@Aktiverad BIT,
@AvdelningID INT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Personal
VALUES(LOWER(@KortNamn), @ForNamn, @EfterNamn,
ISNULL(@IsBestallarPerson, 0), @VeckoArbetstid, @Debittid, @Epostadress, @Aktiverad, @AvdelningID)
END
GO
/****** Object: StoredProcedure [dbo].[Personal_Delete_Department] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Personal_Delete_Department TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Personal_Delete_Department]
@PersonalID INT
AS
BEGIN
SET NOCOUNT ON;

UPDATE Personal
SET AvdelningID = NULL
WHERE PersonalID = @PersonalID
END
GO
/****** Object: StoredProcedure [dbo].[Personal_Delete] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Personal_Delete TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Personal_Delete]
@PersonalID INT
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM Personal
WHERE PersonalID = @PersonalID
END
GO
/****** Object: StoredProcedure [dbo].[Funktion_Select_AllAsList] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Funktion_Select_AllAsList TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Funktion_Select_AllAsList]
AS
BEGIN
SET NOCOUNT ON;

SELECT NULL AS FunktionID, NULL AS FunktionNamn
UNION
SELECT FunktionID, FunktionNamn
FROM Funktion
ORDER BY FunktionNamn
END
GO
/****** Object: StoredProcedure [dbo].[Funktion_Select_All] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Funktion_Select_All TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Funktion_Select_All]
AS
BEGIN
SET NOCOUNT ON;

SELECT *
FROM Funktion
ORDER BY FunktionNamn
END
GO
/****** Object: StoredProcedure [dbo].[Funktion_Insert] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Funktion_Insert TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Funktion_Insert]
@FunktionNamn NVARCHAR(48),
@EnhetID INT
AS
BEGIN
SET NOCOUNT ON;

IF @EnhetID = 0
SET @EnhetID = NULL

INSERT INTO Funktion
VALUES(@FunktionNamn, @EnhetID)
END
GO
/****** Object: StoredProcedure [dbo].[Funktion_Delete_Unit] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Funktion_Delete_Unit TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Funktion_Delete_Unit]
@FunktionID INT
AS
BEGIN
SET NOCOUNT ON;

UPDATE Funktion
SET EnhetID = NULL
WHERE FunktionID = @FunktionID
END
GO
/****** Object: StoredProcedure [dbo].[Funktion_Delete] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Funktion_Delete TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Funktion_Delete]
@FunktionID INT
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM Funktion
WHERE FunktionID = @FunktionID
END
GO
/****** Object: Table [dbo].[PersonalFunktion] Script Date: 06/28/2011 06:07:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PersonalFunktion](
[PersonalID] [int] NOT NULL,
[FunktionID] [int] NOT NULL,
CONSTRAINT [PersonalFunktion_IDX] PRIMARY KEY CLUSTERED
(
[PersonalID] ASC,
[FunktionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[Ledighet_Create_AllHolidays] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Ledighet_Create_AllHolidays TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Ledighet_Create_AllHolidays]
AS
BEGIN
DECLARE @Yr INT
DECLARE @Holiday DATETIME
DECLARE @EasterDay DATETIME
DECLARE @MonthAndDay CHAR(6)
DECLARE @HolidayCode INT
DECLARE @HolidayDescription VARCHAR(32)

SET NOCOUNT ON
SET DATEFIRST 1

-- SET @Yr = YEAR(GETDATE()) + 1
SET @Yr = YEAR(GETDATE())


DECLARE Holiday_Cursor CURSOR FOR
SELECT *
FROM FastaHelgdagar

OPEN Holiday_Cursor

FETCH NEXT FROM Holiday_Cursor
INTO @MonthAndDay, @HolidayCode, @HolidayDescription

WHILE @@FETCH_STATUS = 0
BEGIN
SET @Holiday = CAST(@Yr AS CHAR(4)) + @MonthAndDay

IF NOT DATEPART(dw, @Holiday) IN (6, 7)
BEGIN
EXEC Ledighet_Create_Holiday @Holiday, @HolidayCode, @HolidayDescription
END

FETCH NEXT FROM Holiday_Cursor
INTO @MonthAndDay, @HolidayCode, @HolidayDescription
END
CLOSE Holiday_Cursor
DEALLOCATE Holiday_Cursor

-- Midsommarafton
SET @Holiday = CAST(@Yr AS CHAR(4)) + '-06-20'
EXEC GetHolidayEve @Holiday OUTPUT
EXEC Ledighet_Create_Holiday @Holiday, 0, 'Midsommarafton'

--Alla helgons dags afton
SET @Holiday = CAST(@Yr AS CHAR(4)) + '-10-31'
EXEC GetHolidayEve @Holiday OUTPUT
EXEC Ledighet_Create_Holiday @Holiday, 1, 'Alla helgons dag'

EXEC GetEasterDay @Yr, @EasterDay OUTPUT

--Långfredagen
SET @Holiday = DATEADD(D, -2, @EasterDay)
EXEC Ledighet_Create_Holiday @Holiday, 1, 'Långfredagen'

--Skärtorsdagen
SET @Holiday = DATEADD(D, -3, @EasterDay)
EXEC Ledighet_Create_Holiday @Holiday, 0, 'Skärtorsdagen'

--Skärtorsdagen
SET @Holiday = DATEADD(D, -3, @EasterDay)
EXEC Ledighet_Create_Holiday @Holiday, 0, 'Skärtorsdagen'

--Kristi himmelsfärdsdag
SET @Holiday = DATEADD(WK, 6, @Holiday)
EXEC Ledighet_Create_Holiday @Holiday, 1, 'Kristi himmelsfärdsdag'

--Annandag påsk
SET @Holiday = DATEADD(D, 1, @EasterDay)
EXEC Ledighet_Create_Holiday @Holiday, 1, 'Annandag påsk'
END
GO
/****** Object: StoredProcedure [dbo].[Function_Insert_Unit] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Function_Insert_Unit TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Function_Insert_Unit]
@FunktionID INT,
@EnhetID INT
AS
BEGIN
SET NOCOUNT ON;

UPDATE Funktion
SET EnhetID = @EnhetID
WHERE FunktionID = @FunktionID
END
GO
/****** Object: StoredProcedure [dbo].[Funktion_Update] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Funktion_Update TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Funktion_Update]
@FunktionID INT,
@FunktionNamn NVARCHAR(48),
@EnhetID INT
AS
BEGIN
SET NOCOUNT ON;

IF @EnhetID = 0
SET @EnhetID = NULL

UPDATE Funktion
SET FunktionNamn = @FunktionNamn, EnhetID = @EnhetID
WHERE FunktionID = @FunktionID
END
GO
/****** Object: StoredProcedure [dbo].[Funktion_Select_NotByUnit] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Funktion_Select_NotByUnit TO ProjektPlanering
-- Funktion_Select_NotByUnit 6
-- =============================================
CREATE PROCEDURE [dbo].[Funktion_Select_NotByUnit]
@EnhetID INT
AS
BEGIN
SET NOCOUNT ON;

SELECT *
FROM Funktion
WHERE (EnhetID <> @EnhetID) OR (EnhetID IS NULL)
ORDER BY FunktionNamn
END
GO
/****** Object: StoredProcedure [dbo].[Funktion_Select_ByUnit] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Funktion_Select_ByUnit TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Funktion_Select_ByUnit]
@EnhetID INT
AS
BEGIN
SET NOCOUNT ON;

SELECT *
FROM Funktion
WHERE EnhetID = @EnhetID
ORDER BY FunktionNamn
END
GO
/****** Object: StoredProcedure [dbo].[Arbetstid_Select_WorkHoursForWeek] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Arbetstid_Select_WorkHoursForWeek TO ProjektPlanering
--
/*
DECLARE @HRS FLOAT
DECLARE @ReservedHours FLOAT
DECLARE @Holidays VARCHAR(256)
EXEC Arbetstid_Select_WorkHoursForWeek 1, 2011, 16, @HRS OUTPUT, @ReservedHours OUTPUT, @Holidays OUTPUT
SELECT @HRS, @ReservedHours, @Holidays
*/
-- =============================================
CREATE PROCEDURE [dbo].[Arbetstid_Select_WorkHoursForWeek]
@PersonalID INT,
@YearNo INT,
@WeekNo INT,
@Hours FLOAT OUTPUT,
@ReservedHours FLOAT OUTPUT,
@Holidays VARCHAR(256) OUTPUT
AS
BEGIN
SET NOCOUNT ON;

SET DATEFIRST 1

DECLARE @FirstDayOfWeek DATETIME
DECLARE @LastDayOfWeek DATETIME
DECLARE @HolidayDescription VARCHAR(32)
DECLARE @HolidayHours FLOAT

SET @FirstDayOfWeek = dbo.fnCvtYearWeekNoToDate(@YearNo, @WeekNo)
SET @LastDayOfWeek = DATEADD(DAY, 6, @FirstDayOfWeek)

SELECT @Hours = VeckoArbetstid, @ReservedHours = ROUND((VeckoArbetstid / 100) * Debittid, 1)
FROM Personal
WHERE PersonalID = @PersonalID

SELECT @Hours = @Hours - ISNULL(SUM(Timmar), 0)
FROM Ledighet
WHERE LedighetDatum BETWEEN @FirstDayOfWeek AND @LastDayOfWeek

SET @Holidays = ''

DECLARE Cursor_Holidays CURSOR FOR
SELECT Timmar, Beskrivning
FROM Ledighet
WHERE LedighetDatum BETWEEN @FirstDayOfWeek AND @LastDayOfWeek
ORDER BY LedighetDatum
OPEN Cursor_Holidays

FETCH NEXT FROM Cursor_Holidays
INTO @HolidayHours, @HolidayDescription

WHILE @@FETCH_STATUS = 0
BEGIN
SET @Holidays = @Holidays + @HolidayDescription + ' (' + CAST(@HolidayHours AS VARCHAR(10)) + ')'

FETCH NEXT FROM Cursor_Holidays
INTO @HolidayHours, @HolidayDescription

IF @@FETCH_STATUS = 0
SET @Holidays = @Holidays + '#'
END
CLOSE Cursor_Holidays;
DEALLOCATE Cursor_Holidays;
END
GO
/****** Object: UserDefinedFunction [dbo].[fnGetEmptyWeekCode] Script Date: 06/28/2011 06:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- SELECT dbo.fnGetEmptyWeekCode()
-- =============================================
CREATE FUNCTION [dbo].[fnGetEmptyWeekCode]()
RETURNS INT
AS
BEGIN
DECLARE @Code INT

SELECT @Code = ArbetsTypID
FROM ArbetstidTyp
WHERE ArbetsTypNamn = 'Tom vecka'

RETURN @Code
END
GO
/****** Object: StoredProcedure [dbo].[ArbetstidTyp_Update] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON ArbetstidTyp_Update TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[ArbetstidTyp_Update]
@ArbetsTypID INT,
@ArbetsTypNamn NVARCHAR(128),
@ArbetstidTypKodID INT
AS
BEGIN
SET NOCOUNT ON;

UPDATE ArbetstidTyp
SET ArbetsTypNamn = @ArbetsTypNamn, ArbetstidTypKodID = @ArbetstidTypKodID
WHERE ArbetsTypID = @ArbetsTypID
END
GO
/****** Object: StoredProcedure [dbo].[ArbetstidTyp_Select_AsList] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON ArbetstidTyp_Select_AsList TO ComRes
-- ArbetstidTyp_Select_AsList 1
-- =============================================
CREATE PROCEDURE [dbo].[ArbetstidTyp_Select_AsList]
AS
BEGIN
SET NOCOUNT ON;

SELECT ArbetsTypID, ArbetsTypNamn
FROM ArbetstidTyp
WHERE ArbetstidTypSystem = 0
ORDER BY ArbetsTypNamn
END
GO
/****** Object: StoredProcedure [dbo].[ArbetstidTyp_Select_All] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON ArbetstidTyp_Select_All TO ComRes
-- GRANT SELECT ON ArbetstidTypKod TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[ArbetstidTyp_Select_All]
AS
BEGIN
SET NOCOUNT ON;

SELECT *
FROM ArbetstidTyp
WHERE ArbetstidTypSystem = 0
ORDER BY ArbetsTypNamn
END
GO
/****** Object: StoredProcedure [dbo].[ArbetstidTyp_Insert] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON ArbetstidTyp_Insert TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[ArbetstidTyp_Insert]
@ArbetsTypNamn NVARCHAR(128),
@ArbetstidTypKodID INT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO ArbetstidTyp
VALUES(@ArbetsTypNamn, @ArbetstidTypKodID, 'False')
END
GO
/****** Object: StoredProcedure [dbo].[ArbetstidTyp_Delete] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON ArbetstidTyp_Delete TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[ArbetstidTyp_Delete]
@ArbetsTypID INT
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM ArbetstidTyp
WHERE ArbetsTypID = @ArbetsTypID
END
GO
/****** Object: StoredProcedure [dbo].[Avdelning_Select_ByPerson] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Avdelning_Select_ByPerson TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Avdelning_Select_ByPerson]
@AvdelningID INT
AS
BEGIN
SET NOCOUNT ON;

SELECT a.AvdelningID, p.PersonalID, p.EfterNamn + ', ' + ForNamn AS PersonalNamn
FROM Personal p JOIN Avdelning a ON p.AvdelningID = a.AvdelningID
WHERE p.AvdelningID = @AvdelningID
ORDER BY p.EfterNamn, p.ForNamn
END
GO
/****** Object: Table [dbo].[Bestallning] Script Date: 06/28/2011 06:07:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Bestallning](
[BestallningID] [int] IDENTITY(10000,1) NOT NULL,
[ProjektID] [int] NOT NULL,
[BestPersonID] [int] NOT NULL,
[FunktionID] [int] NOT NULL,
[Beskrivning] [ntext] NULL,
[PersonalID] [int] NULL,
[StartDatum] [datetime] NOT NULL,
[SlutDatum] [datetime] NOT NULL,
[BestAntalTimmar] [float] NOT NULL,
[Skickad] [datetime] NULL,
[Avslutad] [datetime] NULL,
[FardigBehandlat] [datetime] NULL,
[Varskod] [datetime] NULL,
[RegPersonID] [int] NOT NULL,
[RegDatum] [datetime] NOT NULL,
CONSTRAINT [PrimaryKey9] PRIMARY KEY CLUSTERED
(
[BestallningID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: UserDefinedFunction [dbo].[fnConcatProjectName] Script Date: 06/28/2011 06:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
/*
DECLARE @pn NVARCHAR(MAX)
SELECT @pn = dbo.fnConcatProjectName(16)
SELECT @pn
*/
-- =============================================
CREATE FUNCTION [dbo].[fnConcatProjectName]
(
@ProjektID INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @ResultVar NVARCHAR(MAX)


SELECT @ResultVar =
CASE
WHEN ProjektNamn IS NULL THEN ArbetsNamn + ' [arbetsnamn]'
ELSE
CASE
WHEN ArbetsNamn IS NULL THEN ProjektNamn
ELSE ProjektNamn + ' [' + ArbetsNamn + ']'
END
END
FROM Projekt
WHERE ProjektID = @ProjektID

RETURN @ResultVar
END
GO
/****** Object: StoredProcedure [dbo].[Funktion_Select_ByPerson] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Funktion_Select_ByPerson TO ComRes
-- Funktion_Select_ByPerson 2
-- =============================================
CREATE PROCEDURE [dbo].[Funktion_Select_ByPerson]
@PersonalID INT
AS
BEGIN
SET NOCOUNT ON;

SELECT pf.FunktionID
FROM PersonalFunktion pf JOIN Funktion f ON pf.FunktionID = f.FunktionID
WHERE pf.PersonalID = @PersonalID
ORDER BY f.FunktionNamn
END
GO
/****** Object: StoredProcedure [dbo].[PersonalFunction_Select_ByFunction] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON PersonalFunction_Select_ByFunction TO ProjektPlanering
-- PersonalFunction_Select_ByFunction 3
-- PersonalFunction_Select_ByFunction 4
-- =============================================
CREATE PROCEDURE [dbo].[PersonalFunction_Select_ByFunction]
@FunktionID INT
AS
BEGIN
SET NOCOUNT ON;

SELECT 0 AS PersonalID, NULL AS PersonalNamn
UNION
SELECT p.PersonalID, p.EfterNamn + ', ' + ForNamn AS PersonalNamn
FROM Personal p INNER JOIN PersonalFunktion pf ON pf.PersonalID = p.PersonalID
AND pf.FunktionID = @FunktionID
END
GO
/****** Object: StoredProcedure [dbo].[PersonalFunktion_Select_ByFunction] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON PersonalFunction_Select_ByFunction TO ComRes
-- PersonalFunction_Select_ByFunction 3
-- PersonalFunction_Select_ByFunction 4
-- =============================================
CREATE PROCEDURE [dbo].[PersonalFunktion_Select_ByFunction]
@FunktionID INT
AS
BEGIN
SET NOCOUNT ON;

SELECT NULL, ''
UNION
SELECT p.PersonalID, p.EfterNamn + ', ' + ForNamn AS PersonalNamn
FROM Personal p INNER JOIN PersonalFunktion pf ON pf.PersonalID = p.PersonalID
AND pf.FunktionID = @FunktionID
END
GO
/****** Object: StoredProcedure [dbo].[PersonalFunktion_Insert] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON PersonalFunktion_Insert TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[PersonalFunktion_Insert]
@PersonalID INT,
@FunktionID Int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @PersonName NVARCHAR(50)

SELECT FunktionID
FROM PersonalFunktion
WHERE (PersonalID = @PersonalID)
AND (FunktionID = @FunktionID)

IF (@@ROWCOUNT = 1)
BEGIN
SELECT @PersonName = EfterNamn + ', ' + ForNamn
FROM Personal
WHERE PersonalID = @PersonalID

RAISERROR('%s har redan tilldelats denna funktion.', 16, 1, @PersonName)
RETURN -1
END

INSERT INTO PersonalFunktion
VALUES(@PersonalID, @FunktionID)
END
GO
/****** Object: StoredProcedure [dbo].[PersonalFunktion_Delete] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON PersonalFunktion_Delete TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[PersonalFunktion_Delete]
@PersonalID INT,
@FunktionID INT
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM PersonalFunktion
WHERE (PersonalID = @PersonalID)
AND (FunktionID = @FunktionID)
END
GO
/****** Object: StoredProcedure [dbo].[Projekt_Select_All] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Projekt_Select_All TO ProjektPLanering
-- =============================================
CREATE PROCEDURE [dbo].[Projekt_Select_All]
AS
BEGIN
SET NOCOUNT ON;

SELECT p.ProjektID, p.FaktiskaProjektID, p.ProjektNamn, p.ArbetsNamn,
p.BestEnhetID, p.ProjektLedareID, b.BestEnhetNamn
FROM Projekt p
JOIN Bestallarenhet b ON b.BestEnhetID = p.BestEnhetID
END
GO
/****** Object: StoredProcedure [dbo].[Projekt_Insert] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Projekt_Insert TO ProjektPLanering
-- =============================================
CREATE PROCEDURE [dbo].[Projekt_Insert]
@FaktiskaProjektID INT,
@ProjektNamn NVARCHAR(128),
@ArbetsNamn NVARCHAR(128),
@BestEnhetID INT,
@ProjektLedareID INT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Projekt
VALUES(@FaktiskaProjektID, @ProjektNamn, @ArbetsNamn, @BestEnhetID, @ProjektLedareID)
END
GO
/****** Object: StoredProcedure [dbo].[Projekt_Delete] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXEC ON Projekt_Delete TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Projekt_Delete]
@ProjektID INT
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM Projekt
WHERE ProjektID = @ProjektID
END
GO
/****** Object: StoredProcedure [dbo].[Projekt_Update] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Projekt_Update TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Projekt_Update]
@ProjektID INT,
@FaktiskaProjektID INT,
@ProjektNamn NVARCHAR(128),
@ArbetsNamn NVARCHAR(128),
@BestEnhetID INT,
@ProjektLedareID INT
AS
BEGIN
SET NOCOUNT ON;

UPDATE Projekt
SET FaktiskaProjektID = @FaktiskaProjektID, ProjektNamn = @ProjektNamn,
ArbetsNamn = @ArbetsNamn, BestEnhetID = @BestEnhetID, ProjektLedareID = @ProjektLedareID
WHERE ProjektID = @ProjektID
END
GO
/****** Object: StoredProcedure [dbo].[Projekt_Select_AllAsList] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Projekt_Select_AllAsList TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Projekt_Select_AllAsList]
AS
BEGIN
SET NOCOUNT ON;

SELECT ProjektID, dbo.fnConcatProjectName(ProjektID) AS ProjektNamn
FROM Projekt
ORDER BY ProjektNamn
END
GO
/****** Object: StoredProcedure [dbo].[Installningar_Select_EmailOptions_CancelConfirmation] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Installningar_Select_EmailOptions_CancelConfirmation TO ProjektPlanering
-- Installningar_Select_EmailOptions_CancelConfirmation 10063
-- =============================================
CREATE PROCEDURE [dbo].[Installningar_Select_EmailOptions_CancelConfirmation]
@BestallningID INT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @ConfirmClient BIT
DECLARE @ConfirmUnitHead BIT

SELECT TOP 1 @ConfirmClient = ConfirmClient, @ConfirmUnitHead = ConfirmUnitHead
FROM Installningar

SELECT p1.Epostadress AS Bestallare, p2.Epostadress AS Enhetschef
FROM Bestallning best
LEFT JOIN Personal p1 ON ((best.BestPersonID = p1.PersonalID) AND (@ConfirmClient = 1))
LEFT JOIN Funktion func ON best.FunktionID = func.FunktionID
LEFT JOIN Enhet enh ON enh.EnhetID = func.EnhetID
LEFT JOIN Personal p2 ON ((enh.EnhetschefID = p2.PersonalID) AND (@ConfirmUnitHead = 1))
WHERE best.BestallningID = @BestallningID

SELECT 'Annullera avslutat jobbuppdrag: ' + CAST(best.BestallningID AS VARCHAR(10)) + '<br />' +
'Beställarenhet: ' + be.BestEnhetNamn + '<br />' +
'Äskare: ' + p1.EfterNamn + ', ' + p1.ForNamn + '<br />' +
'Projektnr: ' + ISNULL(CAST(proj.FaktiskaProjektID AS VARCHAR(8)), '') + '<br />' +
'Projektnamn: ' +
CASE
WHEN proj.ProjektNamn IS NULL THEN proj.ArbetsNamn + ' [arbetsnamn]'
ELSE proj.ProjektNamn
END + '<br />' +
'Funktion: ' + func.FunktionNamn + '<br />' +

'Enhetschef: ' +
CASE
WHEN enh.EnhetschefID IS NULL THEN '[inte tilldelats]'
ELSE p2.EfterNamn + ', ' + p2.ForNamn
END + '<br />' +

'Personal: ' + p3.EfterNamn + ', ' + p3.ForNamn + '<br />' +
'Startdatum: ' + CONVERT(VARCHAR(10), best.StartDatum, 121) + '<br />' +
'Slutdatum: ' + CONVERT(VARCHAR(10), best.SlutDatum, 121) + '<br />' +
'Best. timmar: ' + CAST(best.BestAntalTimmar AS VARCHAR(8)) + '<br />' +
'Beskrivning: ' +
CASE
WHEN best.Beskrivning IS NULL THEN ''
ELSE CAST(best.Beskrivning AS NVARCHAR(MAX))
END + '<br />' +
'Annullerat: ' + CONVERT(VARCHAR(16), GETDATE(), 121) + '<br />'
AS Body,
'Annullera avslutat jobbuppdrag för projektet ' +
CASE
WHEN proj.ProjektNamn IS NULL THEN proj.ArbetsNamn + ' [arbetsnamn]'
ELSE proj.ProjektNamn
END +
' [' + CAST(best.BestallningID AS VARCHAR(10)) + ']: ' + func.FunktionNamn + ' ' + CONVERT(VARCHAR(10), best.StartDatum, 121) + ' t.o.m. ' + CONVERT(VARCHAR(10), best.SlutDatum, 121) AS Subject,
p3.Epostadress AS sender
FROM Bestallning best
JOIN Projekt proj ON best.ProjektID = proj.ProjektID
JOIN Bestallarenhet be ON be.BestEnhetID = proj.BestEnhetID
JOIN Personal p1 ON best.BestPersonID = p1.PersonalID
LEFT JOIN Funktion func ON best.FunktionID = func.FunktionID
LEFT JOIN Enhet enh ON func.EnhetID = enh.EnhetID
LEFT JOIN Personal p2 ON (enh.EnhetschefID = p2.PersonalID)
LEFT JOIN Personal p3 ON (best.PersonalID = p3.PersonalID)
WHERE best.BestallningID = @BestallningID
END
GO
/****** Object: StoredProcedure [dbo].[Installningar_Select_EmailOptions_SendOrder] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Installningar_Select_EmailOptions_SendOrder TO ProjektPlanering
-- Installningar_Select_EmailOptions_SendOrder 10063
-- =============================================
CREATE PROCEDURE [dbo].[Installningar_Select_EmailOptions_SendOrder]
@BestallningID INT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @SendOrderClient BIT
DECLARE @SendOrderUnitHead BIT

SELECT TOP 1 @SendOrderClient = SendOrderClient, @SendOrderUnitHead = SendOrderUnithead
FROM Installningar

SELECT p1.Epostadress AS Bestallare, p2.Epostadress AS Enhetschef
FROM Bestallning best
LEFT JOIN Personal p1 ON ((best.BestPersonID = p1.PersonalID) AND (@SendOrderClient = 1))
LEFT JOIN Funktion func ON best.FunktionID = func.FunktionID
LEFT JOIN Enhet enh ON func.EnhetID = enh.EnhetID
LEFT JOIN Personal p2 ON ((enh.EnhetschefID = p2.PersonalID) AND (@SendOrderUnitHead = 1))
WHERE best.BestallningID = @BestallningID

SELECT 'Äskning: ' + CAST(best.BestallningID AS VARCHAR(10)) + '<br />' +
'Beställarenhet: ' + be.BestEnhetNamn + '<br />' +
'Äskare: ' + p1.EfterNamn + ', ' + p1.ForNamn + '<br />' +
'Projektnr: ' + ISNULL(CAST(proj.FaktiskaProjektID AS VARCHAR(8)), '') + '<br />' +
'Projektnamn: ' +
CASE
WHEN proj.ProjektNamn IS NULL THEN proj.ArbetsNamn + ' [arbetsnamn]'
ELSE proj.ProjektNamn
END + '<br />' +
'Funktion: ' + func.FunktionNamn + '<br />' +

'Enhetschef: ' +
CASE
WHEN enh.EnhetschefID IS NULL THEN '[inte tilldelats]'
ELSE p2.EfterNamn + ', ' + p2.ForNamn
END + '<br />' +

'Startdatum: ' + CONVERT(VARCHAR(10), best.StartDatum, 121) + '<br />' +
'Slutdatum: ' + CONVERT(VARCHAR(10), best.SlutDatum, 121) + '<br />' +
'Best. timmar: ' + CAST(best.BestAntalTimmar AS VARCHAR(8)) + '<br />' +
'Beskrivning: ' +
CASE
WHEN best.Beskrivning IS NULL THEN ''
ELSE CAST(best.Beskrivning AS NVARCHAR(MAX))
END + '<br />' +
'Registrerat: ' + CONVERT(VARCHAR(16), best.Skickad, 121) + '<br />'
AS Body,
'Äskning för projektet ' +
CASE
WHEN proj.ProjektNamn IS NULL THEN proj.ArbetsNamn + ' [arbetsnamn]'
ELSE proj.ProjektNamn
END +
' [' + CAST(best.BestallningID AS VARCHAR(10)) + ']: ' + func.FunktionNamn + ' ' + CONVERT(VARCHAR(10), best.StartDatum, 121) + ' t.o.m. ' + CONVERT(VARCHAR(10), best.SlutDatum, 121) AS Subject
FROM Bestallning best
JOIN Projekt proj ON best.ProjektID = proj.ProjektID
JOIN Bestallarenhet be ON be.BestEnhetID = proj.BestEnhetID
JOIN Personal p1 ON best.BestPersonID = p1.PersonalID
LEFT JOIN Funktion func ON best.FunktionID = func.FunktionID
LEFT JOIN Enhet enh ON func.EnhetID = enh.EnhetID
LEFT JOIN Personal p2 ON (enh.EnhetschefID = p2.PersonalID)
WHERE best.BestallningID = @BestallningID
END
GO
/****** Object: StoredProcedure [dbo].[Installningar_Select_EmailOptions_Recall] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Installningar_Select_EmailOptions_Recall TO ProjektPlanering
-- Installningar_Select_EmailOptions_Recall 10098
-- =============================================
CREATE PROCEDURE [dbo].[Installningar_Select_EmailOptions_Recall]
@BestallningID INT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @RecallClient BIT
DECLARE @RecallUnithead BIT
DECLARE @RecallPerson BIT

SELECT TOP 1 @RecallPerson = RecallPerson, @RecallClient = RecallClient, @RecallUnithead = RecallUnitHead
FROM Installningar

SELECT p1.Epostadress AS Bestallare, p2.Epostadress AS Enhetschef, p3.Epostadress AS Personal
FROM Bestallning best
LEFT JOIN Personal p1 ON ((best.BestPersonID = p1.PersonalID) AND (@RecallClient = 1))
LEFT JOIN Funktion func ON best.FunktionID = func.FunktionID
LEFT JOIN Enhet enh ON enh.EnhetID = func.EnhetID
LEFT JOIN Personal p2 ON ((enh.EnhetschefID = p2.PersonalID) AND (@RecallUnithead = 1))
LEFT JOIN Personal p3 ON ((best.PersonalID = p3.PersonalID) AND (@RecallPerson = 1))
WHERE best.BestallningID = @BestallningID

SELECT 'Återkallande av jobbuppdrag: ' + CAST(best.BestallningID AS VARCHAR(10)) + '<br />' +
'Beställarenhet: ' + be.BestEnhetNamn + '<br />' +
'Äskare: ' + p1.EfterNamn + ', ' + p1.ForNamn + '<br />' +
'Projektnr: ' + ISNULL(CAST(proj.FaktiskaProjektID AS VARCHAR(8)), '') + '<br />' +
'Projektnamn: ' +
CASE
WHEN proj.ProjektNamn IS NULL THEN proj.ArbetsNamn + ' [arbetsnamn]'
ELSE proj.ProjektNamn
END + '<br />' +
'Funktion: ' + func.FunktionNamn + '<br />' +
'Enhetschef: ' +
CASE
WHEN enh.EnhetschefID IS NULL THEN '[inte tilldelats]'
ELSE p2.EfterNamn + ', ' + p2.ForNamn
END + '<br />' +
'Personal: ' +
CASE
WHEN best.PersonalID IS NULL THEN '[inte tilldelats]'
ELSE p3.EfterNamn + ', ' + p3.ForNamn
END + '<br />' +
'Startdatum: ' + CONVERT(VARCHAR(10), best.StartDatum, 121) + '<br />' +
'Slutdatum: ' + CONVERT(VARCHAR(10), best.SlutDatum, 121) + '<br />' +
'Best. timmar: ' + CAST(best.BestAntalTimmar AS VARCHAR(8)) + '<br />' +
'Beskrivning: ' +
CASE
WHEN best.Beskrivning IS NULL THEN ''
ELSE CAST(best.Beskrivning AS NVARCHAR(MAX))
END + '<br />' +
'Återkallat: ' + CONVERT(VARCHAR(16), GETDATE(), 121) + '<br />'
AS Body,
'Återkallande av jobbuppdrag för projektet ' +
CASE
WHEN proj.ProjektNamn IS NULL THEN proj.ArbetsNamn + ' [arbetsnamn]'
ELSE proj.ProjektNamn
END +
' [' + CAST(best.BestallningID AS VARCHAR(10)) + ']: ' + func.FunktionNamn + ' ' + CONVERT(VARCHAR(10), best.StartDatum, 121) + ' t.o.m. ' + CONVERT(VARCHAR(10), best.SlutDatum, 121) AS Subject
FROM Bestallning best
JOIN Projekt proj ON best.ProjektID = proj.ProjektID
JOIN Bestallarenhet be ON be.BestEnhetID = proj.BestEnhetID
JOIN Personal p1 ON best.BestPersonID = p1.PersonalID
LEFT JOIN Funktion func ON best.FunktionID = func.FunktionID
LEFT JOIN Enhet enh ON func.EnhetID = enh.EnhetID
LEFT JOIN Personal p2 ON (enh.EnhetschefID = p2.PersonalID)
LEFT JOIN Personal p3 ON (best.PersonalID = p3.PersonalID)
WHERE best.BestallningID = @BestallningID
END
GO
/****** Object: StoredProcedure [dbo].[Installningar_Select_EmailOptions_NotifyPerson] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Installningar_Select_EmailOptions_NotifyPerson TO ProjektPlanering
-- Installningar_Select_EmailOptions_NotifyPerson 10002
-- =============================================
CREATE PROCEDURE [dbo].[Installningar_Select_EmailOptions_NotifyPerson]
@BestallningID INT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @NotifyPerson BIT
DECLARE @NotifyClient BIT
DECLARE @NotifyUnithead BIT

SELECT TOP 1 @NotifyClient = NotifyClient, @NotifyUnithead = NotifyUnithead, @NotifyPerson = NotifyPerson
FROM Installningar

SELECT p1.Epostadress AS Bestallare, p2.Epostadress AS Enhetschef, p3.Epostadress AS Personal
FROM Bestallning best
LEFT JOIN Personal p1 ON ((best.BestPersonID = p1.PersonalID) AND (@NotifyClient = 1))
LEFT JOIN Funktion func ON best.FunktionID = func.FunktionID
LEFT JOIN Enhet enh ON enh.EnhetID = func.EnhetID
LEFT JOIN Personal p2 ON ((enh.EnhetschefID = p2.PersonalID) AND (@NotifyUnithead = 1))
LEFT JOIN Personal p3 ON ((best.PersonalID = p3.PersonalID) AND (@NotifyPerson = 1))
WHERE best.BestallningID = @BestallningID

SELECT 'Nytt jobbuppdrag: ' + CAST(best.BestallningID AS VARCHAR(10)) + '<br />' +
'Beställarenhet: ' + be.BestEnhetNamn + '<br />' +
'Äskare: ' + p1.EfterNamn + ', ' + p1.ForNamn + '<br />' +
'Projektnr: ' + ISNULL(CAST(proj.FaktiskaProjektID AS VARCHAR(8)), '') + '<br />' +
'Projektnamn: ' +
CASE
WHEN proj.ProjektNamn IS NULL THEN proj.ArbetsNamn + ' [arbetsnamn]'
ELSE proj.ProjektNamn
END + '<br />' +
'Funktion: ' + func.FunktionNamn + '<br />' +
'Enhetschef: ' +
CASE
WHEN enh.EnhetschefID IS NULL THEN '[inte tilldelats]'
ELSE p2.EfterNamn + ', ' + p2.ForNamn
END + '<br />' +
'Personal: ' + p3.EfterNamn + ', ' + p3.ForNamn + '<br />' +
'Startdatum: ' + CONVERT(VARCHAR(10), best.StartDatum, 121) + '<br />' +
'Slutdatum: ' + CONVERT(VARCHAR(10), best.SlutDatum, 121) + '<br />' +
'Best. timmar: ' + CAST(best.BestAntalTimmar AS VARCHAR(8)) + '<br />' +
'Beskrivning: ' +
CASE
WHEN best.Beskrivning IS NULL THEN ''
ELSE CAST(best.Beskrivning AS NVARCHAR(MAX))
END + '<br />' +
'Registrerat: ' + CONVERT(VARCHAR(16), best.Varskod, 121) + '<br />'
AS Body,
'Nytt jobbuppdrag för projektet ' +
CASE
WHEN proj.ProjektNamn IS NULL THEN proj.ArbetsNamn + ' [arbetsnamn]'
ELSE proj.ProjektNamn
END +
' [' + CAST(best.BestallningID AS VARCHAR(10)) + ']: ' + func.FunktionNamn + ' ' + CONVERT(VARCHAR(10), best.StartDatum, 121) + ' t.o.m. ' + CONVERT(VARCHAR(10), best.SlutDatum, 121) AS Subject
FROM Bestallning best
JOIN Projekt proj ON best.ProjektID = proj.ProjektID
JOIN Bestallarenhet be ON be.BestEnhetID = proj.BestEnhetID
JOIN Personal p1 ON best.BestPersonID = p1.PersonalID
LEFT JOIN Funktion func ON best.FunktionID = func.FunktionID
LEFT JOIN Enhet enh ON func.EnhetID = enh.EnhetID
LEFT JOIN Personal p2 ON (enh.EnhetschefID = p2.PersonalID)
LEFT JOIN Personal p3 ON (best.PersonalID = p3.PersonalID)
WHERE best.BestallningID = @BestallningID
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Update_PlaceOrder] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallning_Update_PlaceOrder TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Update_PlaceOrder]
@BestallningID INT,
@PlaceOrder BIT = 'True'
AS
BEGIN
SET NOCOUNT ON;

UPDATE Bestallning
SET Skickad =
CASE @PlaceOrder
WHEN 'True' THEN GETDATE()
ELSE NULL
END
WHERE BestallningID = @BestallningID
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Update_Personnel] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- GRANT EXECUTE ON Bestallning_Update_Personnel TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Update_Personnel]
@BestallningID INT,
@Beskrivning NTEXT,
@PersonalID INT
AS
BEGIN
SET NOCOUNT ON;

IF (@PersonalID = 0)
SET @PersonalID = NULL

UPDATE Bestallning
SET PersonalID = @PersonalID, Beskrivning = @Beskrivning
WHERE BestallningID = @BestallningID
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Update_NotifyPersonnel] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallning_Update_NotifyPersonnel TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Update_NotifyPersonnel]
@BestallningID INT,
@NotifyPersonnel BIT = 'True'
AS
BEGIN
SET NOCOUNT ON;

UPDATE Bestallning
SET Varskod =
CASE @NotifyPersonnel
WHEN 1 THEN GETDATE()
ELSE NULL
END
WHERE BestallningID = @BestallningID
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Update_FinalizationState] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallning_Update_FinalizationState TO ProjektPlacering
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Update_FinalizationState]
@BestallningID INT,
@Avslutad BIT
AS
BEGIN
SET NOCOUNT ON;

UPDATE Bestallning
SET Avslutad =
CASE
WHEN @Avslutad = 'True' THEN GETDATE()
ELSE NULL
END
WHERE BestallningID = @BestallningID
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Update_ArchiveState] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallning_Update_ArchiveState TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Update_ArchiveState]
@BestallningID INT,
@Arkived BIT
AS
BEGIN
SET NOCOUNT ON;

UPDATE Bestallning
SET FardigBehandlat =
CASE @Arkived
WHEN 'True' THEN GETDATE()
ELSE NULL
END
WHERE BestallningID = @BestallningID
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Update] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallning_Update TO ProjektPlaning
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Update]
@BestallningID INT,
@ProjektID INT,
@BestPersonID INT,
@FunktionID INT,
@Beskrivning NTEXT,
@StartDatum DATETIME,
@SlutDatum DATETIME,
@BestAntalTimmar FLOAT
AS
BEGIN
SET NOCOUNT ON;

UPDATE Bestallning
SET ProjektID = @ProjektID, BestPersonID = @BestPersonID, FunktionID = @FunktionID,
Beskrivning = @Beskrivning, StartDatum = @StartDatum,SlutDatum = @SlutDatum,
BestAntalTimmar = @BestAntalTimmar
WHERE BestallningID = @BestallningID
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Select_ByPerson] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallning_Select_ByPerson TO ProjektPlanering
-- Bestallning_Select_ByPerson 49
-- Bestallning_Select_ByPerson 78
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Select_ByPerson]
@PersonalID INT
AS
BEGIN
SET NOCOUNT ON;

SELECT best.BestallningID, CAST(best.BestallningID AS VARCHAR(10)) + ' ' + ISNULL(proj.ProjektNamn, proj.ArbetsNamn + ' [arbetsnamn]') + ' (' +
funk.FunktionNamn + ')' AS BestallningNamn
FROM Projekt proj
JOIN Bestallning best ON proj.ProjektID = best.ProjektID AND best.PersonalID = @PersonalID
JOIN Funktion funk ON funk.FunktionID = best.FunktionID
WHERE ((NOT best.Skickad IS NULL) AND (best.Avslutad IS NULL) AND (best.FardigBehandlat IS NULL))
ORDER BY funk.FunktionNamn
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Select_All_Planned] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallning_Select_All_Planned TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Select_All_Planned]
AS
BEGIN
SET NOCOUNT ON;

SELECT best.BestallningID, best.BestPersonID, proj.FaktiskaProjektID,
best.ProjektID, best.FunktionID, best.Beskrivning,
best.StartDatum, best.SlutDatum,
best.BestAntalTimmar,
proj.BestEnhetID
FROM Bestallning best
JOIN Projekt proj ON proj.ProjektID = best.ProjektID
WHERE Skickad IS NULL
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Select_All_Placed] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- GRANT EXECUTE ON Bestallning_Select_All_Placed TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Select_All_Placed]
AS
BEGIN
SET NOCOUNT ON;

SELECT best.BestallningID, best.ProjektID, proj.FaktiskaProjektID, proj.BestEnhetID,
best.BestPersonID, best.FunktionID, best.Beskrivning, best.PersonalID,
best.StartDatum, best.SlutDatum,
best.BestAntalTimmar, best.Varskod
FROM Bestallning best
JOIN Projekt proj ON proj.ProjektID = best.ProjektID
LEFT JOIN Personal pers ON best.PersonalID = pers.PersonalID
WHERE (NOT Skickad IS NULL)
AND (Avslutad IS NULL)
AND (FardigBehandlat IS NULL)
END
GO
/****** Object: StoredProcedure [dbo].[Arbetstid_Select_HoursForWeek] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Arbetstid_Select_HoursForWeek TO ProjektPlanering
-- Arbetstid_Select_HoursForWeek 1, 2011, 16
-- =============================================
CREATE PROCEDURE [dbo].[Arbetstid_Select_HoursForWeek]
@PersonalID INT,
@YearNo INT,
@WeekNo INT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @FirstDayOfWeek DATETIME
DECLARE @LastDayOfWeek DATETIME
DECLARE @HolidayHours FLOAT

SET DATEFIRST 1

SET @FirstDayOfWeek = dbo.fnCvtYearWeekNoToDate(@YearNo, @WeekNo)
SET @LastDayOfWeek = DATEADD(DAY, 6, @FirstDayOfWeek)

SELECT @HolidayHours = ISNULL(SUM(Timmar), 0)
FROM Ledighet
WHERE LedighetDatum BETWEEN @FirstDayOfWeek AND @LastDayOfWeek

SELECT VeckoArbetstid, Debittid, @FirstDayOfWeek, @LastDayOfWeek, @HolidayHours
FROM Personal
WHERE PersonalID = @PersonalID

SELECT *
FROM Bestallning
WHERE PersonalID = @PersonalID
AND (@FirstDayOfWeek BETWEEN StartDatum AND SlutDatum)
AND (@LastDayOfWeek BETWEEN StartDatum AND SlutDatum)

/*
hours requested
hours worked

*/
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Insert] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallning_Insert TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Insert]
@BestallningID INT,
@ProjektID INT,
@BestPersonID INT,
@FunktionID INT,
@Beskrivning NTEXT,
@StartDatum DATETIME,
@SlutDatum DATETIME,
@BestAntalTimmar FLOAT,
@RegPersonID INT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Bestallning(ProjektID,BestPersonID,FunktionID,Beskrivning,StartDatum,SlutDatum,BestAntalTimmar,RegPersonID,RegDatum)
VALUES(@ProjektID,@BestPersonID,@FunktionID,@Beskrivning,@StartDatum,@SlutDatum,@BestAntalTimmar,@RegPersonID,GETDATE())
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Delete] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallning_Delete TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Delete]
@BestallningID INT
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM Bestallning
WHERE BestallningID = @BestallningID
END
GO
/****** Object: Table [dbo].[Arbetstid] Script Date: 06/28/2011 06:07:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Arbetstid](
[ArbetstidID] [int] IDENTITY(1,1) NOT NULL,
[PersonalID] [int] NOT NULL,
[ArNr] [int] NOT NULL,
[VeckoNr] [int] NOT NULL,
[ArbetsTypID] [int] NULL,
[Startdatum] [datetime] NOT NULL,
[Slutdatum] [datetime] NOT NULL,
[Anmarkning] [nvarchar](128) NULL,
[Timmar] [float] NOT NULL,
[BestallningID] [int] NULL,
[BestEnhetID] [int] NULL,
[BestPersonID] [int] NULL,
[Avslutad] [bit] NOT NULL,
[FunktionNamn] [nvarchar](48) NULL,
[Beskrivning] [nvarchar](48) NULL,
[WorkSource] [int] NOT NULL,
[ProjektID] [int] NULL,
[Regdatum] [datetime] NOT NULL,
CONSTRAINT [PrimaryKey7] PRIMARY KEY CLUSTERED
(
[ArbetstidID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[Arbetstid_Update_TerminateWeek] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Arbetstid_Update_TerminateWeek TO ProjektPlanering
-- EXEC Arbetstid_Update_TerminateWeek 'admsqlsteja', 2011, 5, '2011-01-01', '2011-01-07'
-- =============================================
CREATE PROCEDURE [dbo].[Arbetstid_Update_TerminateWeek]
@PersonalID INT,
@ArNr INT,
@VeckoNr INT,
@Startdatum DATETIME,
@Slutdatum DATETIME
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Cnt INT

SELECT @Cnt = COUNT(*)
FROM Arbetstid
WHERE (PersonalID = @PersonalID)
AND ((ArNr = @ArNr) AND (VeckoNr = @VeckoNr))

IF (@Cnt = 0)
INSERT INTO Arbetstid(PersonalID, ArNr, VeckoNr, ArbetsTypID, Startdatum, Slutdatum, Timmar, Avslutad)
VALUES(@PersonalID, @ArNr, @VeckoNr, dbo.fnGetEmptyWeekCode(), @Startdatum, @Slutdatum, 0, 1)
ELSE
UPDATE Arbetstid
SET Avslutad = 1
WHERE (PersonalID = @PersonalID)
AND ((ArNr = @ArNr) AND (VeckoNr = @VeckoNr))
END
GO
/****** Object: StoredProcedure [dbo].[Arbetstid_Update] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Arbetstid_Update TO ComRes
-- =============================================
CREATE PROCEDURE [dbo].[Arbetstid_Update]
@ArbetstidID INT,
@FunktionNamn NVARCHAR(128),
@Anmarkning NVARCHAR(128),
@Timmar FLOAT,
@WorkSource INT,
@ArbetsTypNamn NVARCHAR(128)
AS
BEGIN
SET NOCOUNT ON;

IF (@WorkSource = 2)
UPDATE Arbetstid
SET Timmar = @Timmar, Anmarkning = @Anmarkning, Beskrivning = @FunktionNamn, Regdatum = GETDATE()
WHERE ArbetstidID = @ArbetstidID
ELSE
IF (@WorkSource = 3)
UPDATE Arbetstid
SET Timmar = @Timmar, Anmarkning = @Anmarkning, FunktionNamn = @ArbetsTypNamn, Beskrivning = @FunktionNamn, Regdatum = GETDATE()
WHERE ArbetstidID = @ArbetstidID
ELSE
UPDATE Arbetstid
SET Timmar = @Timmar, Anmarkning = @Anmarkning, Regdatum = GETDATE()
WHERE ArbetstidID = @ArbetstidID

END
GO
/****** Object: StoredProcedure [dbo].[Arbetstid_Select_YearsAsList] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Arbetstid_Select_YearsAsList TO ProjektPlanering
-- Arbetstid_Select_YearsAsList 1
-- =============================================
CREATE PROCEDURE [dbo].[Arbetstid_Select_YearsAsList]
@PersonalID INT
AS
BEGIN
SET NOCOUNT ON;

CREATE TABLE #temp(
ArNr INT
)

INSERT INTO #temp
SELECT DISTINCT ArNr
FROM Arbetstid
WHERE PersonalID = @PersonalID

IF ((SELECT COUNT(ArNr) FROM #temp) = 0)
INSERT #temp
VALUES(YEAR(GETDATE()))

SELECT *
FROM #temp
ORDER BY ArNr
END
GO
/****** Object: UserDefinedFunction [dbo].[fnHoursPersonOrder] Script Date: 06/28/2011 06:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- SELECT dbo.fnHoursPersonOrder(10010, 'admsqlsteja')
-- =============================================
CREATE FUNCTION [dbo].[fnHoursPersonOrder] (
@BestallningID INT,
@PersonalID INT
)
RETURNS FLOAT
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @HoursWorked FLOAT

SELECT @HoursWorked = SUM(Timmar)
FROM Arbetstid
WHERE PersonalID = @PersonalID
AND BestallningID = @BestallningID

RETURN(@HoursWorked);
END;
GO
/****** Object: UserDefinedFunction [dbo].[fnHoursPerOrder] Script Date: 06/28/2011 06:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- SELECT dbo.fnHoursPerOrder(10010)
-- =============================================
CREATE FUNCTION [dbo].[fnHoursPerOrder] (
@BestallningID INT
)
RETURNS FLOAT
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @HoursWorked FLOAT

SELECT @HoursWorked = SUM(Timmar)
FROM Arbetstid
WHERE BestallningID = @BestallningID

RETURN(@HoursWorked);
END;
GO
/****** Object: StoredProcedure [dbo].[Arbetstid_Select_LatestReportedWeek] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Arbetstid_Select_LatestReportedWeek TO ProjektPlanering
-- Arbetstid_Select_LatestReportedWeek 1
-- =============================================
CREATE PROCEDURE [dbo].[Arbetstid_Select_LatestReportedWeek]
@PersonalID INT
AS
BEGIN
DECLARE @YearNo INT
DECLARE @WeekNo INT
DECLARE @LastDayOfWeek DATETIME
DECLARE @Terminated BIT
DECLARE @Hours FLOAT
DECLARE @ReservedHours FLOAT
DECLARE @Holidays VARCHAR(256)

SET NOCOUNT ON;

SELECT DISTINCT @YearNo = MAX(ArNr), @WeekNo = MAX(VeckoNr), @LastDayOfWeek = Slutdatum
FROM Arbetstid
WHERE PersonalID = @PersonalID
GROUP BY Slutdatum

IF (NOT @YearNo IS NULL)
BEGIN
SELECT @Terminated = Avslutad
FROM Arbetstid
WHERE PersonalID = @PersonalID
AND Slutdatum = @LastDayOfWeek

IF (@Terminated = 1)
BEGIN
SET DATEFIRST 1;
SET @LastDayOfWeek = DATEADD(D, 1, @LastDayOfWeek)
SET @YearNo = YEAR(@LastDayOfWeek)
SET @WeekNo = dbo.fnGetISOWeek(@LastDayOfWeek)
END
END
ELSE
BEGIN
SET DATEFIRST 1;
SET @YearNo = YEAR(GETDATE())
SET @WeekNo = dbo.fnGetISOWeek(GETDATE())
END

EXEC Arbetstid_Select_WorkHoursForWeek @PersonalID, @YearNo, @WeekNo, @Hours OUTPUT, @ReservedHours OUTPUT, @Holidays OUTPUT

SELECT @YearNo, @WeekNo, @Hours, @ReservedHours, @Holidays
END
GO
/****** Object: StoredProcedure [dbo].[Arbetstid_Select_ByPersonForPeriod] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Arbetstid_Select_ByPersonForPeriod TO ProjektPlanering
-- Arbetstid_Select_ByPersonForPeriod 78, 2011, 6, 2011, 20
-- Arbetstid_Select_ByPersonForPeriod 1, 2011, 2011, 'False', 6, 9, 2, 3
-- =============================================
CREATE PROCEDURE [dbo].[Arbetstid_Select_ByPersonForPeriod]
@PersonalID INT,
@ArNrFran INT,
@ArNrTill INT,
@TidsPeriod BIT,
@VeckoNrFran INT,
@VeckoNrTill INT,
@ManadNrFran INT,
@ManadNrTill INT
AS
BEGIN
SET NOCOUNT ON;

CREATE TABLE #temp(
ArNr INT,
VeckoNr INT,
ArbetstidID INT,
BestallningID INT,
Bestallarenhet NVARCHAR(64),
BestallareNamn NVARCHAR(96),
ArbetsTypNamn NVARCHAR(128),
FunktionNamn NVARCHAR(128),
Anmarkning NVARCHAR(128),
Timmar FLOAT,
WorkSource INT,
BestallningBeskrivning NTEXT,
Regdatum DATETIME)

SET DATEFIRST 1
SET LANGUAGE Swedish

IF (@TidsPeriod = 'False')
BEGIN
SET @VeckoNrFran = dbo.fnGetISOWeek(dbo.fnGetFirstDateOfYear(CAST(@ArNrFran AS CHAR(4)) + '-' + REPLACE(STR(@ManadNrFran, 2), SPACE(1), '0') + '-01'))
SET @VeckoNrTill = dbo.fnGetISOWeek(dbo.fnGetLastDateofMonth(CAST(@ArNrFran AS CHAR(4)) + '-' + REPLACE(STR(@ManadNrTill, 2), SPACE(1), '0') + '-01'))
END

INSERT #temp
SELECT at.ArNr, at.VeckoNr, at.ArbetstidID, NULL, be.BestEnhetNamn, p1.EfterNamn + ', ' + p1.ForNamn, att.ArbetsTypNamn, at.FunktionNamn, at.Anmarkning, at.Timmar, WorkSource, NULL, at.Regdatum
FROM Arbetstid at
JOIN ArbetstidTyp att ON at.ArbetsTypID = att.ArbetsTypID
LEFT JOIN Bestallarenhet be ON at.BestEnhetID = be.BestEnhetID
LEFT JOIN Personal p1 ON p1.PersonalID = at.BestPersonID
WHERE ((WorkSource = 0)
AND (at.PersonalID = @PersonalID)
AND ((at.ArNr >= @ArNrFran) AND (VeckoNr >= @VeckoNrFran))
AND ((at.ArNr <= @ArNrTill) AND (VeckoNr <= @VeckoNrTill))
AND (NOT at.ArbetsTypID IS NULL))

INSERT #temp
SELECT at.ArNr, at.VeckoNr, at.ArbetstidID, at.BestallningID, be.BestEnhetNamn, p1.EfterNamn + ', ' + p1.ForNamn, dbo.fnTruncateHellip(prj.ProjektNamn), func.FunktionNamn, at.Anmarkning, at.Timmar, WorkSource, best.Beskrivning, at.Regdatum
FROM Arbetstid at
JOIN Bestallning best ON at.BestallningID = best.BestallningID
JOIN Projekt prj ON best.ProjektID = prj.ProjektID
JOIN Bestallarenhet be ON be.BestEnhetID = prj.BestEnhetID
JOIN Funktion func ON func.FunktionID = best.FunktionID
JOIN Personal p1 ON p1.PersonalID = best.BestPersonID
WHERE ((WorkSource = 1)
AND (at.PersonalID = @PersonalID)
AND ((at.ArNr >= @ArNrFran) AND (VeckoNr >= @VeckoNrFran))
AND ((at.ArNr <= @ArNrTill) AND (VeckoNr <= @VeckoNrTill))
AND (NOT at.BestallningID IS NULL))

INSERT #temp
SELECT at.ArNr, at.VeckoNr, at.ArbetstidID, NULL, be.BestEnhetNamn, NULL, dbo.fnConcatProjectName(at.ProjektID), Beskrivning, at.Anmarkning, at.Timmar, WorkSource, NULL, at.Regdatum
FROM Arbetstid at
JOIN Projekt proj ON proj.ProjektID = at.ProjektID
JOIN Bestallarenhet be ON be.BestEnhetID = proj.BestEnhetID
WHERE ((WorkSource = 2)
AND (at.PersonalID = @PersonalID)
AND ((at.ArNr >= @ArNrFran) AND (VeckoNr >= @VeckoNrFran))
AND ((at.ArNr <= @ArNrTill) AND (VeckoNr <= @VeckoNrTill))
AND (NOT at.ProjektID IS NULL))

INSERT #temp
SELECT at.ArNr, at.VeckoNr, at.ArbetstidID, NULL, NULL, NULL, FunktionNamn, Beskrivning, at.Anmarkning, at.Timmar, WorkSource, NULL, at.Regdatum
FROM Arbetstid at
WHERE ((WorkSource = 3)
AND (at.PersonalID = @PersonalID)
AND ((at.ArNr >= @ArNrFran) AND (VeckoNr >= @VeckoNrFran))
AND ((at.ArNr <= @ArNrTill) AND (VeckoNr <= @VeckoNrTill))
AND (at.BestallningID IS NULL)
AND (at.ArbetsTypID IS NULL))

IF (@TidsPeriod = 'True')
SELECT CONVERT(VARCHAR(10), dbo.fnCvtYearWeekNoToDate(ArNr, VeckoNr), 120) + ' till ' + CONVERT(VARCHAR(10), DATEADD(D, 6, dbo.fnCvtYearWeekNoToDate(ArNr, VeckoNr)), 120) +
' [' + REPLACE(STR(VeckoNr, 2), SPACE(1), '0') + ']' AS ArbetsVecka, *
FROM #temp
ORDER BY ArNr, VeckoNr, Regdatum
ELSE
SELECT DATENAME(mm, dbo.fnCvtYearWeekNoToDate(ArNr, VeckoNr)) + ' ' + CAST(ArNr AS CHAR(4)) AS ArbetsVecka, *
FROM #temp
ORDER BY ArNr, VeckoNr, Regdatum

DROP TABLE #temp
END
GO
/****** Object: StoredProcedure [dbo].[Arbetstid_Select_ByPerson] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Arbetstid_Select_ByPerson TO ProjektPlanering
-- Arbetstid_Select_ByPerson 78, 2011, 6
-- Arbetstid_Select_ByPerson 1, 2011, 10
-- =============================================
CREATE PROCEDURE [dbo].[Arbetstid_Select_ByPerson]
@PersonalID INT,
@ArNr INT,
@VeckoNr INT
AS
BEGIN
SET NOCOUNT ON;

CREATE TABLE #temp(
ArbetstidID INT,
BestallningID INT,
Bestallarenhet NVARCHAR(64),
BestallareNamn NVARCHAR(96),
ArbetsTypNamn NVARCHAR(128),
FunktionNamn NVARCHAR(128),
Anmarkning NVARCHAR(128),
Timmar FLOAT,
WorkSource INT,
BestallningBeskrivning NTEXT,
Regdatum DATETIME)

INSERT #temp
SELECT at.ArbetstidID, NULL, be.BestEnhetNamn, p1.EfterNamn + ', ' + p1.ForNamn, att.ArbetsTypNamn, at.FunktionNamn, at.Anmarkning, at.Timmar, WorkSource, NULL, at.Regdatum
FROM Arbetstid at
JOIN ArbetstidTyp att ON at.ArbetsTypID = att.ArbetsTypID
LEFT JOIN Bestallarenhet be ON at.BestEnhetID = be.BestEnhetID
LEFT JOIN Personal p1 ON p1.PersonalID = at.BestPersonID
WHERE ((WorkSource = 0)
AND (at.PersonalID = @PersonalID)
AND ((at.ArNr = @ArNr) AND (VeckoNr = @VeckoNr))
AND (NOT at.ArbetsTypID IS NULL))

INSERT #temp
SELECT at.ArbetstidID, at.BestallningID, be.BestEnhetNamn, p1.EfterNamn + ', ' + p1.ForNamn, dbo.fnTruncateHellip(prj.ProjektNamn), func.FunktionNamn, at.Anmarkning, at.Timmar, WorkSource, best.Beskrivning, at.Regdatum
FROM Arbetstid at
JOIN Bestallning best ON at.BestallningID = best.BestallningID
JOIN Projekt prj ON best.ProjektID = prj.ProjektID
JOIN Bestallarenhet be ON be.BestEnhetID = prj.BestEnhetID
JOIN Funktion func ON func.FunktionID = best.FunktionID
JOIN Personal p1 ON p1.PersonalID = best.BestPersonID
WHERE ((WorkSource = 1)
AND (at.PersonalID = @PersonalID)
AND ((at.ArNr = @ArNr) AND (VeckoNr = @VeckoNr))
AND (NOT at.BestallningID IS NULL))

INSERT #temp
SELECT at.ArbetstidID, NULL, be.BestEnhetNamn, NULL, dbo.fnConcatProjectName(at.ProjektID), Beskrivning, at.Anmarkning, at.Timmar, WorkSource, NULL, at.Regdatum
FROM Arbetstid at
JOIN Projekt proj ON proj.ProjektID = at.ProjektID
JOIN Bestallarenhet be ON be.BestEnhetID = proj.BestEnhetID
WHERE ((WorkSource = 2)
AND (at.PersonalID = @PersonalID)
AND ((at.ArNr = @ArNr) AND (VeckoNr = @VeckoNr))
AND (NOT at.ProjektID IS NULL))

INSERT #temp
SELECT at.ArbetstidID, NULL, NULL, NULL, FunktionNamn, Beskrivning, at.Anmarkning, at.Timmar, WorkSource, NULL, at.Regdatum
FROM Arbetstid at
WHERE ((WorkSource = 3)
AND (at.PersonalID = @PersonalID)
AND ((at.ArNr = @ArNr) AND (VeckoNr = @VeckoNr))
AND (at.BestallningID IS NULL)
AND (at.ArbetsTypID IS NULL))

SELECT *
FROM #temp
ORDER BY Regdatum DESC

DROP TABLE #temp
END
GO
/****** Object: StoredProcedure [dbo].[Arbetstid_Select_ByOrderNo] Script Date: 06/28/2011 06:06:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Arbetstid_Select_ByOrderNo TO ProjektPlanering
-- Arbetstid_Select_ByOrderNo 10063
-- =============================================
CREATE PROCEDURE [dbo].[Arbetstid_Select_ByOrderNo]
@PersonalID INT,
@BestallningID INT
AS
BEGIN
SET NOCOUNT ON;

SELECT ArbetstidID, ArNr, VeckoNr, Anmarkning, Timmar, Regdatum
FROM Arbetstid
WHERE (PersonalID = @PersonalID)
AND (BestallningID = @BestallningID)
ORDER BY Regdatum DESC
END
GO
/****** Object: StoredProcedure [dbo].[Arbetstid_Select_AllForPeriod] Script Date: 06/28/2011 06:06:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Arbetstid_Select_AllForPeriod TO ProjektPlanering
-- Arbetstid_Select_AllForPeriod 78, 2011, 6, 2011, 20
-- Arbetstid_Select_AllForPeriod 2011, 2011, 'True', 1, 20, 2, 3, '4;'
-- =============================================
CREATE PROCEDURE [dbo].[Arbetstid_Select_AllForPeriod]
@ArNrFran INT,
@ArNrTill INT,
@TidsPeriod BIT,
@VeckoNrFran INT,
@VeckoNrTill INT,
@ManadNrFran INT,
@ManadNrTill INT,
@DepartmentSelection VARCHAR(256) = '-1;'
AS
BEGIN
SET NOCOUNT ON;

CREATE TABLE #temp(
ArNr INT,
VeckoNr INT,
ArbetstidID INT,
BestallningID INT,
Bestallarenhet NVARCHAR(64),
BestallareNamn NVARCHAR(96),
ArbetsTypNamn NVARCHAR(128),
FunktionNamn NVARCHAR(128),
PersonalNamn NVARCHAR(128),
Timmar FLOAT,
WorkSource INT,
BestallningBeskrivning NTEXT,
Regdatum DATETIME)

SET DATEFIRST 1
SET LANGUAGE Swedish

IF (@DepartmentSelection = '-1;')
SET @DepartmentSelection = NULL

IF (@TidsPeriod = 'False')
BEGIN
SET @VeckoNrFran = dbo.fnGetISOWeek(dbo.fnGetFirstDateOfYear(CAST(@ArNrFran AS CHAR(4)) + '-' + REPLACE(STR(@ManadNrFran, 2), SPACE(1), '0') + '-01'))
SET @VeckoNrTill = dbo.fnGetISOWeek(dbo.fnGetLastDateofMonth(CAST(@ArNrFran AS CHAR(4)) + '-' + REPLACE(STR(@ManadNrTill, 2), SPACE(1), '0') + '-01'))
END

INSERT #temp
SELECT at.ArNr, at.VeckoNr, at.ArbetstidID, NULL, be.BestEnhetNamn, p1.EfterNamn + ', ' + p1.ForNamn, att.ArbetsTypNamn, at.FunktionNamn, p2.EfterNamn + ', ' + p2.ForNamn, at.Timmar, WorkSource, NULL, at.Regdatum
FROM Arbetstid at
JOIN ArbetstidTyp att ON at.ArbetsTypID = att.ArbetsTypID
LEFT JOIN Bestallarenhet be ON at.BestEnhetID = be.BestEnhetID
LEFT JOIN Personal p1 ON p1.PersonalID = at.BestPersonID
JOIN Personal p2 ON p2.PersonalID = at.PersonalID
WHERE ((WorkSource = 0)
AND ((at.ArNr >= @ArNrFran) AND (VeckoNr >= @VeckoNrFran))
AND ((at.ArNr <= @ArNrTill) AND (VeckoNr <= @VeckoNrTill))
AND (NOT at.ArbetsTypID IS NULL))
AND ((@DepartmentSelection IS NULL) OR ((NOT @DepartmentSelection IS NULL) AND (p2.AvdelningID IN (SELECT convert(int,Value) FROM dbo.Split(@DepartmentSelection,';')))))

INSERT #temp
SELECT at.ArNr, at.VeckoNr, at.ArbetstidID, at.BestallningID, be.BestEnhetNamn, p1.EfterNamn + ', ' + p1.ForNamn, dbo.fnTruncateHellip(prj.ProjektNamn), func.FunktionNamn, p2.EfterNamn + ', ' + p2.ForNamn, at.Timmar, WorkSource, best.Beskrivning, at.Regdatum
FROM Arbetstid at
JOIN Bestallning best ON at.BestallningID = best.BestallningID
JOIN Projekt prj ON best.ProjektID = prj.ProjektID
JOIN Bestallarenhet be ON be.BestEnhetID = prj.BestEnhetID
JOIN Funktion func ON func.FunktionID = best.FunktionID
JOIN Personal p1 ON p1.PersonalID = best.BestPersonID
JOIN Personal p2 ON p2.PersonalID = at.PersonalID
WHERE ((WorkSource = 1)
AND ((at.ArNr >= @ArNrFran) AND (VeckoNr >= @VeckoNrFran))
AND ((at.ArNr <= @ArNrTill) AND (VeckoNr <= @VeckoNrTill))
AND (NOT at.BestallningID IS NULL))
AND ((@DepartmentSelection IS NULL) OR ((NOT @DepartmentSelection IS NULL) AND (p2.AvdelningID IN (SELECT convert(int,Value) FROM dbo.Split(@DepartmentSelection,';')))))

INSERT #temp
SELECT at.ArNr, at.VeckoNr, at.ArbetstidID, NULL, be.BestEnhetNamn, NULL, dbo.fnConcatProjectName(at.ProjektID), Beskrivning, p2.EfterNamn + ', ' + p2.ForNamn, at.Timmar, WorkSource, NULL, at.Regdatum
FROM Arbetstid at
JOIN Projekt proj ON proj.ProjektID = at.ProjektID
JOIN Bestallarenhet be ON be.BestEnhetID = proj.BestEnhetID
JOIN Personal p2 ON p2.PersonalID = at.PersonalID
WHERE ((WorkSource = 2)
AND ((at.ArNr >= @ArNrFran) AND (VeckoNr >= @VeckoNrFran))
AND ((at.ArNr <= @ArNrTill) AND (VeckoNr <= @VeckoNrTill))
AND (NOT at.ProjektID IS NULL))
AND ((@DepartmentSelection IS NULL) OR ((NOT @DepartmentSelection IS NULL) AND (p2.AvdelningID IN (SELECT convert(int,Value) FROM dbo.Split(@DepartmentSelection,';')))))

INSERT #temp
SELECT at.ArNr, at.VeckoNr, at.ArbetstidID, NULL, NULL, NULL, FunktionNamn, Beskrivning, p2.EfterNamn + ', ' + p2.ForNamn, at.Timmar, WorkSource, NULL, at.Regdatum
FROM Arbetstid at
JOIN Personal p2 ON p2.PersonalID = at.PersonalID
WHERE ((WorkSource = 3)
AND ((at.ArNr >= @ArNrFran) AND (VeckoNr >= @VeckoNrFran))
AND ((at.ArNr <= @ArNrTill) AND (VeckoNr <= @VeckoNrTill))
AND (at.BestallningID IS NULL)
AND (at.ArbetsTypID IS NULL))
AND ((@DepartmentSelection IS NULL) OR ((NOT @DepartmentSelection IS NULL) AND (p2.AvdelningID IN (SELECT convert(int,Value) FROM dbo.Split(@DepartmentSelection,';')))))

IF (@TidsPeriod = 'True')
SELECT CONVERT(VARCHAR(10), dbo.fnCvtYearWeekNoToDate(ArNr, VeckoNr), 120) + ' till ' + CONVERT(VARCHAR(10), DATEADD(D, 6, dbo.fnCvtYearWeekNoToDate(ArNr, VeckoNr)), 120) +
' [' + REPLACE(STR(VeckoNr, 2), SPACE(1), '0') + ']' AS ArbetsVecka, *
FROM #temp
ORDER BY ArNr, VeckoNr, Regdatum
ELSE
SELECT DATENAME(mm, dbo.fnCvtYearWeekNoToDate(ArNr, VeckoNr)) + ' ' + CAST(ArNr AS CHAR(4)) AS ArbetsVecka, *
FROM #temp
ORDER BY ArNr, VeckoNr, Regdatum

DROP TABLE #temp
END
GO
/****** Object: StoredProcedure [dbo].[Arbetstid_Insert] Script Date: 06/28/2011 06:06:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Arbetstid_Insert TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Arbetstid_Insert]
@WorkSource INT,
@PersonalID INT,
@ArNr INT,
@VeckoNr INT,
@ArbetsTypID INT = NULL,
@Startdatum DATETIME,
@Slutdatum DATETIME,
@Anmarkning NVARCHAR(128),
@Timmar FLOAT,
@BestallningID INT = NULL,
@BestEnhetID INT = NULL,
@BestPersonID INT = NULL,
@FunktionNamn NVARCHAR(48) = NULL,
@Beskrivning NVARCHAR(48) = NULL,
@ProjektID INT = NULL
AS
BEGIN
SET NOCOUNT ON;

INSERT Arbetstid (PersonalID, ArNr, VeckoNr, ArbetsTypID, Startdatum, Slutdatum, Timmar, Anmarkning, BestallningID, BestEnhetID, BestPersonID, FunktionNamn, Beskrivning, WorkSource, ProjektID, Regdatum)
VALUES (@PersonalID, @ArNr, @VeckoNr, @ArbetsTypID, @Startdatum, @Slutdatum, @Timmar, @Anmarkning, @BestallningID, @BestEnhetID, @BestPersonID, @FunktionNamn, @Beskrivning, @WorkSource, @ProjektID, GETDATE())
END
GO
/****** Object: StoredProcedure [dbo].[Arbetstid_Delete] Script Date: 06/28/2011 06:06:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Arbetstid_Delete TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Arbetstid_Delete]
@ArbetstidID INT
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM Arbetstid
WHERE ArbetstidID = @ArbetstidID
END
GO
/****** Object: StoredProcedure [dbo].[Installningar_Select_EmailOptions_Confirm] Script Date: 06/28/2011 06:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Installningar_Select_EmailOptions_Confirm TO ProjektPlanering
-- Installningar_Select_EmailOptions_Confirm 10062
-- =============================================
CREATE PROCEDURE [dbo].[Installningar_Select_EmailOptions_Confirm]
@BestallningID INT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @ConfirmClient BIT
DECLARE @ConfirmUnitHead BIT

SELECT TOP 1 @ConfirmClient = ConfirmClient, @ConfirmUnitHead = ConfirmUnitHead
FROM Installningar

SELECT p1.Epostadress AS Bestallare, p2.Epostadress AS Enhetschef
FROM Bestallning best
LEFT JOIN Personal p1 ON ((best.BestPersonID = p1.PersonalID) AND (@ConfirmClient = 1))
LEFT JOIN Funktion func ON best.FunktionID = func.FunktionID
LEFT JOIN Enhet enh ON enh.EnhetID = func.EnhetID
LEFT JOIN Personal p2 ON ((enh.EnhetschefID = p2.PersonalID) AND (@ConfirmUnitHead = 1))
WHERE best.BestallningID = @BestallningID

SELECT 'Avslutat jobbuppdrag: ' + CAST(best.BestallningID AS VARCHAR(10)) + '<br />' +
'Beställarenhet: ' + be.BestEnhetNamn + '<br />' +
'Äskare: ' + p1.EfterNamn + ', ' + p1.ForNamn + '<br />' +
'Projektnr: ' + ISNULL(CAST(proj.FaktiskaProjektID AS VARCHAR(8)), '') + '<br />' +
'Projektnamn: ' +
CASE
WHEN proj.ProjektNamn IS NULL THEN proj.ArbetsNamn + ' [arbetsnamn]'
ELSE proj.ProjektNamn
END + '<br />' +
'Funktion: ' + func.FunktionNamn + '<br />' +

'Enhetschef: ' +
CASE
WHEN enh.EnhetschefID IS NULL THEN '[inte tilldelats]'
ELSE p2.EfterNamn + ', ' + p2.ForNamn
END + '<br />' +

'Personal: ' + p3.EfterNamn + ', ' + p3.ForNamn + '<br />' +
'Startdatum: ' + CONVERT(VARCHAR(10), best.StartDatum, 121) + '<br />' +
'Slutdatum: ' + CONVERT(VARCHAR(10), best.SlutDatum, 121) + '<br />' +
'Best. timmar: ' + CAST(best.BestAntalTimmar AS VARCHAR(8)) + '<br />' +
'Arb. timmar: ' + CAST(dbo.fnHoursPersonOrder(@BestallningID, p3.PersonalID) AS VARCHAR(8)) + '<br />' +
'Beskrivning: ' +
CASE
WHEN best.Beskrivning IS NULL THEN ''
ELSE CAST(best.Beskrivning AS NVARCHAR(MAX))
END + '<br />' +
'Avslutad: ' + CONVERT(VARCHAR(16), best.Avslutad, 121) + '<br />'
AS Body,
'Avslutat jobbuppdrag för projektet ' +
CASE
WHEN proj.ProjektNamn IS NULL THEN proj.ArbetsNamn + ' [arbetsnamn]'
ELSE proj.ProjektNamn
END +
' [' + CAST(best.BestallningID AS VARCHAR(10)) + ']: ' + func.FunktionNamn + ' ' + CONVERT(VARCHAR(10), best.StartDatum, 121) + ' t.o.m. ' + CONVERT(VARCHAR(10), best.SlutDatum, 121) AS Subject,
p3.Epostadress AS Sender
FROM Bestallning best
JOIN Projekt proj ON best.ProjektID = proj.ProjektID
JOIN Bestallarenhet be ON be.BestEnhetID = proj.BestEnhetID
JOIN Personal p1 ON best.BestPersonID = p1.PersonalID
LEFT JOIN Funktion func ON best.FunktionID = func.FunktionID
LEFT JOIN Enhet enh ON func.EnhetID = enh.EnhetID
LEFT JOIN Personal p2 ON (enh.EnhetschefID = p2.PersonalID)
LEFT JOIN Personal p3 ON (best.PersonalID = p3.PersonalID)
WHERE best.BestallningID = @BestallningID
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Select_All_Ongoing] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallning_Select_All_Ongoing TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Select_All_Ongoing]
AS
BEGIN
SET NOCOUNT ON;

SELECT best.BestallningID, best.ProjektID, proj.FaktiskaProjektID, proj.BestEnhetID,
best.BestPersonID, best.FunktionID, best.Beskrivning, best.PersonalID,
best.StartDatum, best.SlutDatum,
best.BestAntalTimmar, best.Varskod, dbo.fnHoursPerOrder(best.BestallningID) AS ArbAntalTimmar
FROM Bestallning best
JOIN Projekt proj ON proj.ProjektID = best.ProjektID
LEFT JOIN Personal pers ON best.PersonalID = pers.PersonalID
WHERE (NOT Skickad IS NULL)
AND (NOT FardigBehandlat IS NULL)
AND (Avslutad IS NULL)
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Select_All_Completed] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallning_Select_All_Completed TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Select_All_Completed]
AS
BEGIN
SET NOCOUNT ON;

SELECT best.BestallningID, best.ProjektID, proj.FaktiskaProjektID, proj.BestEnhetID,
best.BestPersonID, best.FunktionID, best.Beskrivning, best.PersonalID,
best.StartDatum, best.SlutDatum,
best.BestAntalTimmar, best.Varskod, dbo.fnHoursPerOrder(best.BestallningID) AS ArbAntalTimmar
FROM Bestallning best
JOIN Projekt proj ON proj.ProjektID = best.ProjektID
LEFT JOIN Personal pers ON best.PersonalID = pers.PersonalID
WHERE (NOT Skickad IS NULL)
AND (NOT Avslutad IS NULL)
AND (FardigBehandlat IS NULL)
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Select_All_ByPerson_Archived] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallning_Select_All_ByPerson_Archived TO ProjektPlanering
-- Bestallning_Select_All_ByPerson_Archived 88
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Select_All_ByPerson_Archived]
@PersonalID INT
AS
BEGIN
SET NOCOUNT ON;

SELECT best.BestallningID, proj.FaktiskaProjektID,
best.ProjektID,
proj.BestEnhetID,
best.BestPersonID,
best.FunktionID,
best.Beskrivning,
best.StartDatum, best.SlutDatum,
best.BestAntalTimmar,
dbo.fnHoursPersonOrder(best.BestallningID, @PersonalID) AS ArbAntalTimmar,
best.Avslutad
FROM Bestallning best
JOIN Projekt proj ON proj.ProjektID = best.ProjektID
JOIN Personal p1 ON best.PersonalID = p1.PersonalID
WHERE (p1.PersonalID = @PersonalID)
AND (NOT Skickad IS NULL)
AND (NOT Avslutad IS NULL)
AND (NOT FardigBehandlat IS NULL)
ORDER BY StartDatum
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Select_All_ByPerson] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallning_Select_All_ByPerson TO ProjektPlanering
-- Bestallning_Select_All_ByPerson 78
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Select_All_ByPerson]
@PersonalID INT
AS
BEGIN
SET NOCOUNT ON;

SELECT best.BestallningID, proj.FaktiskaProjektID,
best.ProjektID,
proj.BestEnhetID,
best.BestPersonID,
best.FunktionID,
best.Beskrivning,
best.StartDatum, best.SlutDatum,
best.BestAntalTimmar,
dbo.fnHoursPersonOrder(best.BestallningID, @PersonalID) AS ArbAntalTimmar,
best.Avslutad
FROM Bestallning best
JOIN Projekt proj ON proj.ProjektID = best.ProjektID
JOIN Personal p1 ON best.PersonalID = p1.PersonalID
WHERE (p1.PersonalID = @PersonalID)
AND (NOT Skickad IS NULL)
AND (best.FardigBehandlat IS NULL)
ORDER BY StartDatum
END
GO
/****** Object: StoredProcedure [dbo].[Bestallning_Select_All_Archived] Script Date: 06/28/2011 06:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- GRANT EXECUTE ON Bestallning_Select_All_Archived TO ProjektPlanering
-- =============================================
CREATE PROCEDURE [dbo].[Bestallning_Select_All_Archived]
AS
BEGIN
SET NOCOUNT ON;

SELECT best.BestallningID, best.ProjektID, proj.FaktiskaProjektID, proj.BestEnhetID,
best.BestPersonID, best.FunktionID, best.Beskrivning, best.PersonalID,
best.StartDatum, best.SlutDatum,
best.BestAntalTimmar, best.Varskod, dbo.fnHoursPerOrder(best.BestallningID) AS ArbAntalTimmar
FROM Bestallning best
JOIN Projekt proj ON proj.ProjektID = best.ProjektID
LEFT JOIN Personal pers ON best.PersonalID = pers.PersonalID
WHERE (NOT Skickad IS NULL)
AND (NOT Avslutad IS NULL)
AND (NOT FardigBehandlat IS NULL)
END
GO
/****** Object: Default [DF_Arbetstid_Avslutad] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Arbetstid] ADD CONSTRAINT [DF_Arbetstid_Avslutad] DEFAULT ((0)) FOR [Avslutad]
GO
/****** Object: Default [DF_Arbetstid_Source] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Arbetstid] ADD CONSTRAINT [DF_Arbetstid_Source] DEFAULT ((0)) FOR [WorkSource]
GO
/****** Object: Default [DF_Arbetstid_Regdatum] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Arbetstid] ADD CONSTRAINT [DF_Arbetstid_Regdatum] DEFAULT (((1900)-(1))-(1)) FOR [Regdatum]
GO
/****** Object: Default [DF_ArbetstidTyp_ArbetstidTypSystem] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[ArbetstidTyp] ADD CONSTRAINT [DF_ArbetstidTyp_ArbetstidTypSystem] DEFAULT ((0)) FOR [ArbetstidTypSystem]
GO
/****** Object: Default [DF_ArbetstidTypKod_ArbetstidTypSystem] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[ArbetstidTypKod] ADD CONSTRAINT [DF_ArbetstidTypKod_ArbetstidTypSystem] DEFAULT ((0)) FOR [ArbetstidTypSystem]
GO
/****** Object: Default [DF_Bestallning_RegPersonID] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Bestallning] ADD CONSTRAINT [DF_Bestallning_RegPersonID] DEFAULT ((1)) FOR [RegPersonID]
GO
/****** Object: Default [DF_Bestallning_RegDatum] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Bestallning] ADD CONSTRAINT [DF_Bestallning_RegDatum] DEFAULT (getdate()) FOR [RegDatum]
GO
/****** Object: Default [DF__Installni__SendO__6477ECF3] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF__Installni__SendO__6477ECF3] DEFAULT ((1)) FOR [SendOrderPerson]
GO
/****** Object: Default [DF__Installni__SendO__656C112C] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF__Installni__SendO__656C112C] DEFAULT ((1)) FOR [SendOrderClient]
GO
/****** Object: Default [DF__Installni__Confi__6754599E] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF__Installni__Confi__6754599E] DEFAULT ((1)) FOR [ConfirmClient]
GO
/****** Object: Default [DF__Installni__Recal__693CA210] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF__Installni__Recal__693CA210] DEFAULT ((1)) FOR [RecallPerson]
GO
/****** Object: Default [DF__Installni__Recal__6A30C649] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF__Installni__Recal__6A30C649] DEFAULT ((1)) FOR [RecallClient]
GO
/****** Object: Default [DF_Installningar_DefaultWorkingWeek] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF_Installningar_DefaultWorkingWeek] DEFAULT ((0)) FOR [DefaultWorkingWeek]
GO
/****** Object: Default [DF_Installningar_SendOrderUnitHead] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF_Installningar_SendOrderUnitHead] DEFAULT ((0)) FOR [SendOrderUnitHead]
GO
/****** Object: Default [DF_Installningar_ConfirmUnitHead] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF_Installningar_ConfirmUnitHead] DEFAULT ((0)) FOR [ConfirmUnitHead]
GO
/****** Object: Default [DF_Installningar_RecallUnitHead] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF_Installningar_RecallUnitHead] DEFAULT ((0)) FOR [RecallUnitHead]
GO
/****** Object: Default [DF_Installningar_NotifyClient] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF_Installningar_NotifyClient] DEFAULT ((0)) FOR [NotifyClient]
GO
/****** Object: Default [DF_Installningar_NotifyUnitHead] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF_Installningar_NotifyUnitHead] DEFAULT ((0)) FOR [NotifyUnitHead]
GO
/****** Object: Default [DF_Installningar_NotifyPerson] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF_Installningar_NotifyPerson] DEFAULT ((0)) FOR [NotifyPerson]
GO
/****** Object: Default [DF_Installningar_TimmarHalvdag] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF_Installningar_TimmarHalvdag] DEFAULT ((0)) FOR [TimmarHalvdag]
GO
/****** Object: Default [DF_Installningar_TimmarHeldag] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF_Installningar_TimmarHeldag] DEFAULT ((0)) FOR [TimmarHeldag]
GO
/****** Object: Default [DF_Installningar_TimmarKlamdag] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Installningar] ADD CONSTRAINT [DF_Installningar_TimmarKlamdag] DEFAULT ((0)) FOR [TimmarKlamdag]
GO
/****** Object: Default [DF_Ledighet_AntalTimmar] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Ledighet] ADD CONSTRAINT [DF_Ledighet_AntalTimmar] DEFAULT ((0)) FOR [Timmar]
GO
/****** Object: Default [DF__Personal__IsBest__5812160E] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Personal] ADD CONSTRAINT [DF__Personal__IsBest__5812160E] DEFAULT ((0)) FOR [IsBestallarPerson]
GO
/****** Object: Default [DF_Personal_VeckoArbetstid] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Personal] ADD CONSTRAINT [DF_Personal_VeckoArbetstid] DEFAULT ((0)) FOR [VeckoArbetstid]
GO
/****** Object: Default [DF_Personal_Debittid] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Personal] ADD CONSTRAINT [DF_Personal_Debittid] DEFAULT ((0)) FOR [Debittid]
GO
/****** Object: Default [DF_Personal_aktiverat
Aktiverad] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Personal] ADD CONSTRAINT [DF_Personal_aktiverat
Aktiverad] DEFAULT ((1)) FOR [Aktiverad]
GO
/****** Object: ForeignKey [ArbetstidTyp_Arbetstid] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Arbetstid] WITH CHECK ADD CONSTRAINT [ArbetstidTyp_Arbetstid] FOREIGN KEY([ArbetsTypID])
REFERENCES [dbo].[ArbetstidTyp] ([ArbetsTypID])
GO
ALTER TABLE [dbo].[Arbetstid] CHECK CONSTRAINT [ArbetstidTyp_Arbetstid]
GO
/****** Object: ForeignKey [Bestallarenhet_Arbetstid] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Arbetstid] WITH CHECK ADD CONSTRAINT [Bestallarenhet_Arbetstid] FOREIGN KEY([BestEnhetID])
REFERENCES [dbo].[Bestallarenhet] ([BestEnhetID])
GO
ALTER TABLE [dbo].[Arbetstid] CHECK CONSTRAINT [Bestallarenhet_Arbetstid]
GO
/****** Object: ForeignKey [Bestallning_Arbetstid] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Arbetstid] WITH CHECK ADD CONSTRAINT [Bestallning_Arbetstid] FOREIGN KEY([BestallningID])
REFERENCES [dbo].[Bestallning] ([BestallningID])
GO
ALTER TABLE [dbo].[Arbetstid] CHECK CONSTRAINT [Bestallning_Arbetstid]
GO
/****** Object: ForeignKey [Personal_Arbetstid] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Arbetstid] WITH CHECK ADD CONSTRAINT [Personal_Arbetstid] FOREIGN KEY([BestPersonID])
REFERENCES [dbo].[Personal] ([PersonalID])
GO
ALTER TABLE [dbo].[Arbetstid] CHECK CONSTRAINT [Personal_Arbetstid]
GO
/****** Object: ForeignKey [Personal_Arbetstid_1] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Arbetstid] WITH CHECK ADD CONSTRAINT [Personal_Arbetstid_1] FOREIGN KEY([PersonalID])
REFERENCES [dbo].[Personal] ([PersonalID])
GO
ALTER TABLE [dbo].[Arbetstid] CHECK CONSTRAINT [Personal_Arbetstid_1]
GO
/****** Object: ForeignKey [ArbetstidTypKod_ArbetstidTyp] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[ArbetstidTyp] WITH CHECK ADD CONSTRAINT [ArbetstidTypKod_ArbetstidTyp] FOREIGN KEY([ArbetstidTypKodID])
REFERENCES [dbo].[ArbetstidTypKod] ([ArbetstidTypKodID])
GO
ALTER TABLE [dbo].[ArbetstidTyp] CHECK CONSTRAINT [ArbetstidTypKod_ArbetstidTyp]
GO
/****** Object: ForeignKey [Funktioner_Bestallning] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Bestallning] WITH CHECK ADD CONSTRAINT [Funktioner_Bestallning] FOREIGN KEY([FunktionID])
REFERENCES [dbo].[Funktion] ([FunktionID])
GO
ALTER TABLE [dbo].[Bestallning] CHECK CONSTRAINT [Funktioner_Bestallning]
GO
/****** Object: ForeignKey [Personal_Bestallning] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Bestallning] WITH CHECK ADD CONSTRAINT [Personal_Bestallning] FOREIGN KEY([BestPersonID])
REFERENCES [dbo].[Personal] ([PersonalID])
GO
ALTER TABLE [dbo].[Bestallning] CHECK CONSTRAINT [Personal_Bestallning]
GO
/****** Object: ForeignKey [Personal_Bestallning_1] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Bestallning] WITH CHECK ADD CONSTRAINT [Personal_Bestallning_1] FOREIGN KEY([RegPersonID])
REFERENCES [dbo].[Personal] ([PersonalID])
GO
ALTER TABLE [dbo].[Bestallning] CHECK CONSTRAINT [Personal_Bestallning_1]
GO
/****** Object: ForeignKey [Personal_Bestallning_2] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Bestallning] WITH CHECK ADD CONSTRAINT [Personal_Bestallning_2] FOREIGN KEY([PersonalID])
REFERENCES [dbo].[Personal] ([PersonalID])
GO
ALTER TABLE [dbo].[Bestallning] CHECK CONSTRAINT [Personal_Bestallning_2]
GO
/****** Object: ForeignKey [Projekt_Bestallning] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Bestallning] WITH CHECK ADD CONSTRAINT [Projekt_Bestallning] FOREIGN KEY([ProjektID])
REFERENCES [dbo].[Projekt] ([ProjektID])
GO
ALTER TABLE [dbo].[Bestallning] CHECK CONSTRAINT [Projekt_Bestallning]
GO
/****** Object: ForeignKey [Enhet_Funktion] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Funktion] WITH CHECK ADD CONSTRAINT [Enhet_Funktion] FOREIGN KEY([EnhetID])
REFERENCES [dbo].[Enhet] ([EnhetID])
GO
ALTER TABLE [dbo].[Funktion] CHECK CONSTRAINT [Enhet_Funktion]
GO
/****** Object: ForeignKey [Avdelning_Personal] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Personal] WITH CHECK ADD CONSTRAINT [Avdelning_Personal] FOREIGN KEY([AvdelningID])
REFERENCES [dbo].[Avdelning] ([AvdelningID])
GO
ALTER TABLE [dbo].[Personal] CHECK CONSTRAINT [Avdelning_Personal]
GO
/****** Object: ForeignKey [Funktion_PersonalFunktion] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[PersonalFunktion] WITH CHECK ADD CONSTRAINT [Funktion_PersonalFunktion] FOREIGN KEY([FunktionID])
REFERENCES [dbo].[Funktion] ([FunktionID])
GO
ALTER TABLE [dbo].[PersonalFunktion] CHECK CONSTRAINT [Funktion_PersonalFunktion]
GO
/****** Object: ForeignKey [Personal_PersonalFunktion] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[PersonalFunktion] WITH CHECK ADD CONSTRAINT [Personal_PersonalFunktion] FOREIGN KEY([PersonalID])
REFERENCES [dbo].[Personal] ([PersonalID])
GO
ALTER TABLE [dbo].[PersonalFunktion] CHECK CONSTRAINT [Personal_PersonalFunktion]
GO
/****** Object: ForeignKey [Bestallarenheter_Projekt] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Projekt] WITH CHECK ADD CONSTRAINT [Bestallarenheter_Projekt] FOREIGN KEY([BestEnhetID])
REFERENCES [dbo].[Bestallarenhet] ([BestEnhetID])
GO
ALTER TABLE [dbo].[Projekt] CHECK CONSTRAINT [Bestallarenheter_Projekt]
GO
/****** Object: ForeignKey [Personal_Projekt] Script Date: 06/28/2011 06:07:03 ******/
ALTER TABLE [dbo].[Projekt] WITH CHECK ADD CONSTRAINT [Personal_Projekt] FOREIGN KEY([ProjektLedareID])
REFERENCES [dbo].[Personal] ([PersonalID])
GO
ALTER TABLE [dbo].[Projekt] CHECK CONSTRAINT [Personal_Projekt]
GO

Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2690
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post Posted: 27 Jun 2011 at 4:20pm
Hi Joacobsson,
 
can you maybe provide your database structure, at least some functions you would want to be imported? It should be importing procedures and functions already.
Back to Top
Jacobsson Steve View Drop Down
New Member
New Member
Avatar

Joined: 30 Sep 2010
Posts: 2
Post Options Post Options   Quote Jacobsson Steve Quote  Post ReplyReply Direct Link To This Post Posted: 21 Jun 2011 at 7:43am
Great product and I use it daily although I find one thing lacking - it doesn't import functions from, in my case, MS SQL Server. This makes it a bit of a hassle when importing a database in order to work with it and then generate a script to create a new database.
Any plans to incorporate this functionality at some point in time?
 
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down