USE [TaskIt]
GO
/****** Object:  Table [dbo].[Project]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Project](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](250) NOT NULL,
	[ParentID] [bigint] NULL,
	[DueDate] [datetime] NULL,
	[CompletionDate] [datetime] NULL,
	[UserID] [bigint] NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
(
	[ID] 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
SET IDENTITY_INSERT [dbo].[Project] ON
INSERT [dbo].[Project] ([ID], [Name], [ParentID], [DueDate], [CompletionDate], [UserID], [CreatedDate], [ModifiedDate]) VALUES (1, N'[No Project]', NULL, NULL, NULL, 2, CAST(0x00009CDC00000000 AS DateTime), CAST(0x00009CDC00000000 AS DateTime))
INSERT [dbo].[Project] ([ID], [Name], [ParentID], [DueDate], [CompletionDate], [UserID], [CreatedDate], [ModifiedDate]) VALUES (23, N'Task-It Videos', NULL, CAST(0x00009CDC00000000 AS DateTime), NULL, 3, CAST(0x00009D02010800D8 AS DateTime), CAST(0x00009D02010800D8 AS DateTime))
INSERT [dbo].[Project] ([ID], [Name], [ParentID], [DueDate], [CompletionDate], [UserID], [CreatedDate], [ModifiedDate]) VALUES (24, N'Task-It Blog Posts', NULL, CAST(0x00009CDC00000000 AS DateTime), NULL, 3, CAST(0x00009D0201085F1D AS DateTime), CAST(0x00009D2D006A2C7C AS DateTime))
INSERT [dbo].[Project] ([ID], [Name], [ParentID], [DueDate], [CompletionDate], [UserID], [CreatedDate], [ModifiedDate]) VALUES (25, N'Task-It Webinars', NULL, CAST(0x00009CDC00000000 AS DateTime), NULL, 3, CAST(0x00009D020109C73B AS DateTime), CAST(0x00009D310077F777 AS DateTime))
SET IDENTITY_INSERT [dbo].[Project] OFF
/****** Object:  Table [dbo].[Priority]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Priority](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](20) NOT NULL,
 CONSTRAINT [PK_Priority] PRIMARY KEY CLUSTERED 
(
	[ID] 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
SET IDENTITY_INSERT [dbo].[Priority] ON
INSERT [dbo].[Priority] ([ID], [Name]) VALUES (1, N'Normal')
INSERT [dbo].[Priority] ([ID], [Name]) VALUES (2, N'High')
INSERT [dbo].[Priority] ([ID], [Name]) VALUES (3, N'Low')
SET IDENTITY_INSERT [dbo].[Priority] OFF
/****** Object:  Table [dbo].[Context]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Context](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	[UserID] [bigint] NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Context] PRIMARY KEY CLUSTERED 
(
	[ID] 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
SET IDENTITY_INSERT [dbo].[Context] ON
INSERT [dbo].[Context] ([ID], [Name], [UserID], [CreatedDate], [ModifiedDate]) VALUES (1, N'[No Context]', 2, CAST(0x00009CDC00000000 AS DateTime), CAST(0x00009CDC00000000 AS DateTime))
INSERT [dbo].[Context] ([ID], [Name], [UserID], [CreatedDate], [ModifiedDate]) VALUES (2, N'Work', 3, CAST(0x00009D5E00F07AAB AS DateTime), CAST(0x00009D5E00F07AAB AS DateTime))
INSERT [dbo].[Context] ([ID], [Name], [UserID], [CreatedDate], [ModifiedDate]) VALUES (3, N'Home', 3, CAST(0x00009D5E00F0A144 AS DateTime), CAST(0x00009D5E00F0A144 AS DateTime))
SET IDENTITY_INSERT [dbo].[Context] OFF
/****** Object:  UserDefinedFunction [dbo].[GetOneWeekFromToday]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetOneWeekFromToday]()
RETURNS DATETIME
BEGIN

	RETURN DATEADD(DD, 14 - DATEPART(DW, GETDATE()),GETDATE())

END
GO
/****** Object:  UserDefinedFunction [dbo].[GetLastDayOfWeek]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetLastDayOfWeek]
( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

	SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
	RETURN DATEADD(DD, 7 - DATEPART(DW, @pInputDate),@pInputDate)

END
GO
/****** Object:  UserDefinedFunction [dbo].[GetLastDayOfMonth]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetLastDayOfMonth]
( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

	SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
	RETURN DATEADD(dd, -DAY(DATEADD(m,1,@pInputDate)), DATEADD(m,1,@pInputDate))
 

END
GO
/****** Object:  UserDefinedFunction [dbo].[GetFirstDayOfWeek]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetFirstDayOfWeek]
( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

	SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
	RETURN DATEADD(DD, 1 - DATEPART(DW, @pInputDate), @pInputDate)

END
GO
/****** Object:  UserDefinedFunction [dbo].[GetFirstDayOfMonth]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetFirstDayOfMonth]
( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

	SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
	RETURN DATEADD(dd,-(DAY(DATEADD(mm,1,@pInputDate))-1),DATEADD(mm,0,@pInputDate)) 

END
GO
/****** Object:  Table [dbo].[Contact]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Contact](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	[Email] [varchar](100) NULL,
	[Username] [varchar](50) NULL,
	[UserID] [bigint] NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED 
(
	[ID] 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
SET IDENTITY_INSERT [dbo].[Contact] ON
INSERT [dbo].[Contact] ([ID], [Name], [Email], [Username], [UserID], [CreatedDate], [ModifiedDate]) VALUES (1, N'[No Contact]', NULL, NULL, 2, CAST(0x00009CDC00000000 AS DateTime), CAST(0x00009CDC00000000 AS DateTime))
SET IDENTITY_INSERT [dbo].[Contact] OFF
/****** Object:  Table [dbo].[TaskItUser]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TaskItUser](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Username] [varchar](50) NOT NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[Email] [varchar](100) NULL,
	[Password] [varchar](50) NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
	[TwitterUsername] [varchar](100) NULL,
	[TwitterPassword] [varchar](100) NULL,
	[FacebookUsername] [varchar](100) NULL,
	[FacebookPassword] [varchar](100) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[ID] 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
SET IDENTITY_INSERT [dbo].[TaskItUser] ON
INSERT [dbo].[TaskItUser] ([ID], [Username], [FirstName], [LastName], [Email], [Password], [CreatedDate], [ModifiedDate], [TwitterUsername], [TwitterPassword], [FacebookUsername], [FacebookPassword]) VALUES (2, N'AllUsers', N'', N'', N'', N'123', CAST(0x00009CDC00000000 AS DateTime), CAST(0x00009CDC00000000 AS DateTime), NULL, NULL, NULL, NULL)
INSERT [dbo].[TaskItUser] ([ID], [Username], [FirstName], [LastName], [Email], [Password], [CreatedDate], [ModifiedDate], [TwitterUsername], [TwitterPassword], [FacebookUsername], [FacebookPassword]) VALUES (3, N'jdoe', N'John', N'Doe', N'jdoe@hotmail.com', N'123', CAST(0x00009CDC00000000 AS DateTime), CAST(0x00009D31005C515F AS DateTime), NULL, NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[TaskItUser] OFF
/****** Object:  Table [dbo].[TaskCategory]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TaskCategory](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_ActionType] PRIMARY KEY CLUSTERED 
(
	[ID] 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
SET IDENTITY_INSERT [dbo].[TaskCategory] ON
INSERT [dbo].[TaskCategory] ([ID], [Name]) VALUES (1, N'Inbox')
INSERT [dbo].[TaskCategory] ([ID], [Name]) VALUES (2, N'Actions')
INSERT [dbo].[TaskCategory] ([ID], [Name]) VALUES (3, N'Waiting')
INSERT [dbo].[TaskCategory] ([ID], [Name]) VALUES (4, N'Someday')
INSERT [dbo].[TaskCategory] ([ID], [Name]) VALUES (5, N'Ticklers')
INSERT [dbo].[TaskCategory] ([ID], [Name]) VALUES (6, N'Reference')
SET IDENTITY_INSERT [dbo].[TaskCategory] OFF
/****** Object:  Table [dbo].[Task]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Task](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](250) NOT NULL,
	[Description] [varchar](1000) NULL,
	[ProjectID] [bigint] NOT NULL,
	[ContactID] [bigint] NOT NULL,
	[ContextID] [bigint] NOT NULL,
	[UserID] [bigint] NOT NULL,
	[TaskCategoryID] [bigint] NOT NULL,
	[PriorityID] [bigint] NOT NULL,
	[DueDate] [datetime] NULL,
	[CompletionDate] [datetime] NULL,
	[CreatedDate] [datetime] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Action] PRIMARY KEY CLUSTERED 
(
	[ID] 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
SET IDENTITY_INSERT [dbo].[Task] ON
INSERT [dbo].[Task] ([ID], [Name], [Description], [ProjectID], [ContactID], [ContextID], [UserID], [TaskCategoryID], [PriorityID], [DueDate], [CompletionDate], [CreatedDate], [ModifiedDate]) VALUES (1, N'Introduction to Task-It. A real-world application using RadControls for Silverlight 4', N'http://bit.ly/bTv66r', 24, 1, 2, 3, 2, 1, CAST(0x00009D3A00000000 AS DateTime), CAST(0x00009D3A00000000 AS DateTime), CAST(0x00009D0201089AFF AS DateTime), CAST(0x00009D5E00F30202 AS DateTime))
INSERT [dbo].[Task] ([ID], [Name], [Description], [ProjectID], [ContactID], [ContextID], [UserID], [TaskCategoryID], [PriorityID], [DueDate], [CompletionDate], [CreatedDate], [ModifiedDate]) VALUES (2, N'Building a real-world application with RadControls for Silverlight 4', N'http://bit.ly/dj1diN', 25, 1, 2, 3, 2, 1, CAST(0x00009D0201089AFF AS DateTime), CAST(0x00009D0201089AFF AS DateTime), CAST(0x00009D020109E5F7 AS DateTime), CAST(0x00009D5E00F0FEFA AS DateTime))
INSERT [dbo].[Task] ([ID], [Name], [Description], [ProjectID], [ContactID], [ContextID], [UserID], [TaskCategoryID], [PriorityID], [DueDate], [CompletionDate], [CreatedDate], [ModifiedDate]) VALUES (3, N'Internationalization/Globalization in Silverlight 4', N'http://bit.ly/9c691c', 24, 1, 2, 3, 2, 1, CAST(0x00009D0201089AFF AS DateTime), CAST(0x00009D0201089AFF AS DateTime), CAST(0x00009D0300AED891 AS DateTime), CAST(0x00009D5E00F0BAB9 AS DateTime))
INSERT [dbo].[Task] ([ID], [Name], [Description], [ProjectID], [ContactID], [ContextID], [UserID], [TaskCategoryID], [PriorityID], [DueDate], [CompletionDate], [CreatedDate], [ModifiedDate]) VALUES (4, N'Commands in Task-It - Part 1', N'http://bit.ly/92nSle', 24, 1, 2, 3, 2, 1, CAST(0x00009D5600000000 AS DateTime), CAST(0x00009D5600000000 AS DateTime), CAST(0x00009D0300C06707 AS DateTime), CAST(0x00009D5E00F1CE79 AS DateTime))
INSERT [dbo].[Task] ([ID], [Name], [Description], [ProjectID], [ContactID], [ContextID], [UserID], [TaskCategoryID], [PriorityID], [DueDate], [CompletionDate], [CreatedDate], [ModifiedDate]) VALUES (5, N'Task-It Teaser', N'http://bit.ly/bgUP4I', 23, 1, 2, 3, 2, 2, CAST(0x00009D3600000000 AS DateTime), CAST(0x00009D3600000000 AS DateTime), CAST(0x00009D36009BE518 AS DateTime), CAST(0x00009D5E00F37053 AS DateTime))
INSERT [dbo].[Task] ([ID], [Name], [Description], [ProjectID], [ContactID], [ContextID], [UserID], [TaskCategoryID], [PriorityID], [DueDate], [CompletionDate], [CreatedDate], [ModifiedDate]) VALUES (6, N'Intro to Task-It Teaser Video', N'http://bit.ly/9eNcMF', 24, 1, 2, 3, 2, 1, CAST(0x00009D3600000000 AS DateTime), CAST(0x00009D3600000000 AS DateTime), CAST(0x00009D3B015410A1 AS DateTime), CAST(0x00009D5E00F326C9 AS DateTime))
INSERT [dbo].[Task] ([ID], [Name], [Description], [ProjectID], [ContactID], [ContextID], [UserID], [TaskCategoryID], [PriorityID], [DueDate], [CompletionDate], [CreatedDate], [ModifiedDate]) VALUES (7, N'MEF to the rescue in Task-It', N'http://bit.ly/c4EAsc', 24, 1, 2, 3, 2, 1, CAST(0x00009D3C00000000 AS DateTime), CAST(0x00009D3C00000000 AS DateTime), CAST(0x00009D3B01544ADB AS DateTime), CAST(0x00009D5E00F2B827 AS DateTime))
INSERT [dbo].[Task] ([ID], [Name], [Description], [ProjectID], [ContactID], [ContextID], [UserID], [TaskCategoryID], [PriorityID], [DueDate], [CompletionDate], [CreatedDate], [ModifiedDate]) VALUES (8, N'Using the RadTransitionControl in Task-It', N'http://bit.ly/9siEkA', 24, 1, 2, 3, 2, 1, CAST(0x00009D4F00000000 AS DateTime), CAST(0x00009D4F00000000 AS DateTime), CAST(0x00009D5300B855FD AS DateTime), CAST(0x00009D5E00F1EDF8 AS DateTime))
INSERT [dbo].[Task] ([ID], [Name], [Description], [ProjectID], [ContactID], [ContextID], [UserID], [TaskCategoryID], [PriorityID], [DueDate], [CompletionDate], [CreatedDate], [ModifiedDate]) VALUES (9, N'Dynamic XAP loading with MEF in Task-It - Part 1 ', N'http://bit.ly/defCoI', 24, 1, 2, 3, 2, 1, CAST(0x00009D4800000000 AS DateTime), CAST(0x00009D4800000000 AS DateTime), CAST(0x00009D5300BDE0AF AS DateTime), CAST(0x00009D5E00F239A5 AS DateTime))
INSERT [dbo].[Task] ([ID], [Name], [Description], [ProjectID], [ContactID], [ContextID], [UserID], [TaskCategoryID], [PriorityID], [DueDate], [CompletionDate], [CreatedDate], [ModifiedDate]) VALUES (10, N'MVVM in Task-It ', N'http://bit.ly/chm1Ce', 24, 1, 2, 3, 2, 1, CAST(0x00009D4300000000 AS DateTime), CAST(0x00009D4300000000 AS DateTime), CAST(0x00009D5300BFEF58 AS DateTime), CAST(0x00009D5E00F25572 AS DateTime))
INSERT [dbo].[Task] ([ID], [Name], [Description], [ProjectID], [ContactID], [ContextID], [UserID], [TaskCategoryID], [PriorityID], [DueDate], [CompletionDate], [CreatedDate], [ModifiedDate]) VALUES (11, N'Task-It Webinar - Source Code', N'http://bit.ly/aS4O3N', 24, 1, 2, 3, 2, 1, CAST(0x00009D4100000000 AS DateTime), CAST(0x00009D4100000000 AS DateTime), CAST(0x00009D5300C29354 AS DateTime), CAST(0x00009D5E00F28177 AS DateTime))
INSERT [dbo].[Task] ([ID], [Name], [Description], [ProjectID], [ContactID], [ContextID], [UserID], [TaskCategoryID], [PriorityID], [DueDate], [CompletionDate], [CreatedDate], [ModifiedDate]) VALUES (12, N'Task-It Webinar - Building a real-world application with RadControls for Silverlight 4', N'http://bit.ly/bT8sJR', 24, 1, 2, 3, 2, 1, CAST(0x00009D3E00000000 AS DateTime), CAST(0x00009D3E00000000 AS DateTime), CAST(0x00009D5300C3625F AS DateTime), CAST(0x00009D5E00F77FDC AS DateTime))
INSERT [dbo].[Task] ([ID], [Name], [Description], [ProjectID], [ContactID], [ContextID], [UserID], [TaskCategoryID], [PriorityID], [DueDate], [CompletionDate], [CreatedDate], [ModifiedDate]) VALUES (13, N'Commands in Task-It - Part 2', N'http://bit.ly/bvlsMo', 24, 1, 2, 3, 1, 1, CAST(0x00009D5D00000000 AS DateTime), CAST(0x00009D5D00000000 AS DateTime), CAST(0x00009D5E00F1A8F2 AS DateTime), CAST(0x00009D5E00F6C8AC AS DateTime))
INSERT [dbo].[Task] ([ID], [Name], [Description], [ProjectID], [ContactID], [ContextID], [UserID], [TaskCategoryID], [PriorityID], [DueDate], [CompletionDate], [CreatedDate], [ModifiedDate]) VALUES (14, N'Building a Real World Application with RadControls for Silverlight 4', NULL, 23, 1, 2, 3, 1, 2, CAST(0x00009D3D00000000 AS DateTime), CAST(0x00009D3D00000000 AS DateTime), CAST(0x00009D5E00F3B75B AS DateTime), CAST(0x00009D5E00F3CEC9 AS DateTime))
SET IDENTITY_INSERT [dbo].[Task] OFF
/****** Object:  Table [dbo].[ProjectNote]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ProjectNote](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Text] [varchar](5000) NOT NULL,
	[ProjectID] [bigint] NOT NULL,
	[UserID] [bigint] NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProjectNote] PRIMARY KEY CLUSTERED 
(
	[ID] 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].[ContextNote]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ContextNote](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Text] [varchar](5000) NOT NULL,
	[ContextID] [bigint] NOT NULL,
	[UserID] [bigint] NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ContextNote] PRIMARY KEY CLUSTERED 
(
	[ID] 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].[TaskNote]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TaskNote](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Text] [varchar](5000) NOT NULL,
	[TaskID] [bigint] NOT NULL,
	[UserID] [bigint] NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ActionNote] PRIMARY KEY CLUSTERED 
(
	[ID] 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].[ContactNote]    Script Date: 04/21/2010 09:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ContactNote](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Text] [varchar](5000) NOT NULL,
	[ContactID] [bigint] NOT NULL,
	[UserID] [bigint] NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ContactNote] PRIMARY KEY CLUSTERED 
(
	[ID] 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].[GetTasksOverview]    Script Date: 04/21/2010 09:05:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetTasksOverview]
	--@userID decimal
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @overdue int 
	DECLARE @dueThisWeek int
	DECLARE @dueThisMonth int
	DECLARE @completedThisWeek int
	DECLARE @completedThisMonth int
	DECLARE @today datetime
	SET @today = GETDATE()

	SET @overdue = (SELECT COUNT(ID) FROM Task WHERE DueDate < @today AND CompletionDate IS NULL) --AND UserID = @userID)
	SET @dueThisWeek = (SELECT COUNT(ID) FROM Task WHERE DueDate BETWEEN dbo.GetFirstDayOfWeek(@today) AND dbo.GetLastDayOfWeek(@today) AND CompletionDate IS NULL) --AND UserID = @userID)
	SET @dueThisMonth = (SELECT COUNT(ID) FROM Task WHERE DueDate BETWEEN dbo.GetFirstDayOfMonth(@today) AND dbo.GetLastDayOfMonth(@today) AND CompletionDate IS NULL) --AND UserID = @userID)
	SET @completedThisWeek = (SELECT COUNT(ID) FROM Task WHERE CompletionDate BETWEEN dbo.GetFirstDayOfWeek(@today) AND dbo.GetLastDayOfWeek(@today)) --AND UserID = @userID)
	SET @completedThisMonth = (SELECT COUNT(ID) FROM Task WHERE CompletionDate BETWEEN dbo.GetFirstDayOfMonth(@today) AND dbo.GetLastDayOfMonth(@today)) --AND UserID = @userID)

	SELECT 1 as ID,
		   @overdue AS Overdue,
		   @dueThisWeek AS DueThisWeek,
		   @dueThisMonth AS DueThisMonth,
		   @completedThisWeek AS CompletedThisWeek,
		   @completedThisMonth AS CompletedThisMonth
END
GO
/****** Object:  ForeignKey [FK_Priority_Priority1]    Script Date: 04/21/2010 09:05:06 ******/
ALTER TABLE [dbo].[Priority]  WITH CHECK ADD  CONSTRAINT [FK_Priority_Priority1] FOREIGN KEY([ID])
REFERENCES [dbo].[Priority] ([ID])
GO
ALTER TABLE [dbo].[Priority] CHECK CONSTRAINT [FK_Priority_Priority1]
GO
/****** Object:  ForeignKey [FK_Task_Contact]    Script Date: 04/21/2010 09:05:06 ******/
ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Contact] FOREIGN KEY([ContactID])
REFERENCES [dbo].[Contact] ([ID])
GO
ALTER TABLE [dbo].[Task] CHECK CONSTRAINT [FK_Task_Contact]
GO
/****** Object:  ForeignKey [FK_Task_Context]    Script Date: 04/21/2010 09:05:06 ******/
ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Context] FOREIGN KEY([ContextID])
REFERENCES [dbo].[Context] ([ID])
GO
ALTER TABLE [dbo].[Task] CHECK CONSTRAINT [FK_Task_Context]
GO
/****** Object:  ForeignKey [FK_Task_Priority]    Script Date: 04/21/2010 09:05:06 ******/
ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Priority] FOREIGN KEY([PriorityID])
REFERENCES [dbo].[Priority] ([ID])
GO
ALTER TABLE [dbo].[Task] CHECK CONSTRAINT [FK_Task_Priority]
GO
/****** Object:  ForeignKey [FK_Task_Project]    Script Date: 04/21/2010 09:05:06 ******/
ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Project] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[Project] ([ID])
GO
ALTER TABLE [dbo].[Task] CHECK CONSTRAINT [FK_Task_Project]
GO
/****** Object:  ForeignKey [FK_TaskCategory_Task]    Script Date: 04/21/2010 09:05:06 ******/
ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_TaskCategory_Task] FOREIGN KEY([TaskCategoryID])
REFERENCES [dbo].[TaskCategory] ([ID])
GO
ALTER TABLE [dbo].[Task] CHECK CONSTRAINT [FK_TaskCategory_Task]
GO
/****** Object:  ForeignKey [FK_ProjectNote_Project]    Script Date: 04/21/2010 09:05:06 ******/
ALTER TABLE [dbo].[ProjectNote]  WITH CHECK ADD  CONSTRAINT [FK_ProjectNote_Project] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[Project] ([ID])
GO
ALTER TABLE [dbo].[ProjectNote] CHECK CONSTRAINT [FK_ProjectNote_Project]
GO
/****** Object:  ForeignKey [FK_ProjectNote_User]    Script Date: 04/21/2010 09:05:06 ******/
ALTER TABLE [dbo].[ProjectNote]  WITH CHECK ADD  CONSTRAINT [FK_ProjectNote_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[TaskItUser] ([ID])
GO
ALTER TABLE [dbo].[ProjectNote] CHECK CONSTRAINT [FK_ProjectNote_User]
GO
/****** Object:  ForeignKey [FK_ContextNote_Context]    Script Date: 04/21/2010 09:05:06 ******/
ALTER TABLE [dbo].[ContextNote]  WITH CHECK ADD  CONSTRAINT [FK_ContextNote_Context] FOREIGN KEY([ContextID])
REFERENCES [dbo].[Context] ([ID])
GO
ALTER TABLE [dbo].[ContextNote] CHECK CONSTRAINT [FK_ContextNote_Context]
GO
/****** Object:  ForeignKey [FK_ContextNote_User]    Script Date: 04/21/2010 09:05:06 ******/
ALTER TABLE [dbo].[ContextNote]  WITH CHECK ADD  CONSTRAINT [FK_ContextNote_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[TaskItUser] ([ID])
GO
ALTER TABLE [dbo].[ContextNote] CHECK CONSTRAINT [FK_ContextNote_User]
GO
/****** Object:  ForeignKey [FK_TaskNote_Task]    Script Date: 04/21/2010 09:05:06 ******/
ALTER TABLE [dbo].[TaskNote]  WITH CHECK ADD  CONSTRAINT [FK_TaskNote_Task] FOREIGN KEY([TaskID])
REFERENCES [dbo].[Task] ([ID])
GO
ALTER TABLE [dbo].[TaskNote] CHECK CONSTRAINT [FK_TaskNote_Task]
GO
/****** Object:  ForeignKey [FK_TaskNote_User]    Script Date: 04/21/2010 09:05:06 ******/
ALTER TABLE [dbo].[TaskNote]  WITH CHECK ADD  CONSTRAINT [FK_TaskNote_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[TaskItUser] ([ID])
GO
ALTER TABLE [dbo].[TaskNote] CHECK CONSTRAINT [FK_TaskNote_User]
GO
/****** Object:  ForeignKey [FK_ContactNote_Contact]    Script Date: 04/21/2010 09:05:06 ******/
ALTER TABLE [dbo].[ContactNote]  WITH CHECK ADD  CONSTRAINT [FK_ContactNote_Contact] FOREIGN KEY([ContactID])
REFERENCES [dbo].[Contact] ([ID])
GO
ALTER TABLE [dbo].[ContactNote] CHECK CONSTRAINT [FK_ContactNote_Contact]
GO
/****** Object:  ForeignKey [FK_ContactNote_User]    Script Date: 04/21/2010 09:05:06 ******/
ALTER TABLE [dbo].[ContactNote]  WITH CHECK ADD  CONSTRAINT [FK_ContactNote_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[TaskItUser] ([ID])
GO
ALTER TABLE [dbo].[ContactNote] CHECK CONSTRAINT [FK_ContactNote_User]
GO
