?? createtasklistdbsql7.sql
字號:
-- =============================================
-- TaskList_GetUserTaskLists
-- =============================================
-- Retrieves a list of task lists that this user belongs to
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_GetUserTaskLists'
AND type = 'P')
DROP PROCEDURE TaskList_GetUserTaskLists
GO
CREATE PROCEDURE TaskList_GetUserTaskLists
@Username VarChar(20),
@PasswordHash VarChar(50),
@UserToLookUpID int
AS
DECLARE @CurrentUserID int, @IsAdministrator Bit
SET @CurrentUserID = NULL
--Check to make sure this is a valid user, and that they are looking up their own
--properties, or this user has Administrator priviledges
SELECT @CurrentUserID=ID, @IsAdministrator = IsAdministrator
FROM TaskListUsers
WHERE Username = @Username AND PasswordHash = @PasswordHash;
IF (@CurrentUserID IS NULL) RETURN 1; --Invalid user
IF (@CurrentUserID != @UserToLookUpID AND @IsAdministrator = 0) Return 1; --Invalid user
SELECT TaskListNames.ID AS TaskListID,
TaskListNames.[Name] AS TaskListName
FROM TaskListUsers
LEFT JOIN TaskListAssignments ON TaskListAssignments.UserID = TaskListUsers.ID
LEFT JOIN TaskListNames ON TaskListNames.ID = TaskListAssignments.TaskListID
WHERE TaskListUsers.ID = @UserToLookUpID AND TaskListNames.ID IS NOT NULL AND TaskListNames.Name IS NOT NULL
ORDER BY TaskListNames.[Name] ASC
GO
-- =============================================
-- TaskList_LogUserInFromCookie
-- =============================================
-- Returns a user id if this is a valid user
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_LogUserInFromCookie'
AND type = 'P')
DROP PROCEDURE TaskList_LogUserInFromCookie
GO
CREATE PROCEDURE TaskList_LogUserInFromCookie
@Username VarChar(20)
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL
SELECT @CurrentUserID=ID
FROM TaskListUsers
WHERE Username = @Username
IF (@CurrentUserID IS NULL)
RETURN 1; --Invalid user
ELSE
SELECT @CurrentUserID
GO
-- =============================================
-- TaskList_ModifyUser
-- =============================================
-- Allows an administrator user to change a user's properties
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_ModifyUser'
AND type = 'P')
DROP PROCEDURE TaskList_ModifyUser
GO
CREATE PROCEDURE TaskList_ModifyUser
@Username VarChar(20),
@PasswordHash VarChar(50),
@OldUserName VarChar(20),
@NewUserName VarChar(20),
@OldPasswordHash VarChar(50),
@NewPasswordHash VarChar(50),
@IsManager Bit,
@IsAdministrator Bit
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL
--Make sure this is a valid user, and that they have administrator priviledeges
SELECT @CurrentUserID=ID
FROM TaskListUsers
WHERE Username = @Username AND PasswordHash = @PasswordHash AND IsAdministrator = 1;
IF (@CurrentUserID IS NULL) RETURN 1; --Invalid user
UPDATE TaskListUsers SET
Username = @NewUserName,
PasswordHash = @NewPasswordHash,
IsAdministrator = @IsAdministrator,
IsManager = @IsManager
WHERE Username = @OldUserName AND PasswordHash = @OldPasswordHash;
IF @@Error <> 0 Return 1;
IF @@RowCount <> 0 Return 1;
GO
-- =============================================
-- TaskList_RemoveUserFromTaskList
-- =============================================
-- Removes a user from the list of authorized users for the task list ID specified
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_RemoveUserFromTaskList'
AND type = 'P')
DROP PROCEDURE TaskList_RemoveUserFromTaskList
GO
CREATE PROCEDURE TaskList_RemoveUserFromTaskList
@Username VarChar(20),
@PasswordHash VarChar(50),
@UserID int,
@TaskListID int
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL
--Check to make sure that this is a valid user, and that they have manager priviledges
SELECT @CurrentUserID=ID
FROM TaskListUsers
WHERE Username = @Username AND PasswordHash = @PasswordHash AND IsManager = 1;
IF (@CurrentUserID IS NULL) RETURN 1; --Invalid user
DELETE FROM TaskListAssignments WHERE UserID = @UserID AND TaskListID = @TaskListID
GO
-- =============================================
-- TaskList_RenameTaskList
-- =============================================
-- Renames an existing task list
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_RenameTaskList'
AND type = 'P')
DROP PROCEDURE TaskList_RenameTaskList
GO
CREATE PROCEDURE TaskList_RenameTaskList
@Username VarChar(20),
@PasswordHash VarChar(50),
@TaskListID int,
@NewName VarChar(255)
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL
--Make sure that this user is a valid user, and that they have Manager priviledges
SELECT @CurrentUserID=ID
FROM TaskListUsers
WHERE Username = @Username AND PasswordHash = @PasswordHash AND IsManager = 1;
IF (@CurrentUserID IS NULL)
RETURN 1; --Invalid user
UPDATE TaskListNames SET [Name] = @NewName WHERE [ID] = @TaskListID;
IF @@Error <> 0 Return 1;
GO
-- =============================================
-- TaskList_SetNumberOfItemsToDisplayPerPage
-- =============================================
-- Retrieves the number of task list items to display per page on the task list
-- for the user specified
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_SetNumberOfItemsToDisplayPerPage'
AND type = 'P')
DROP PROCEDURE TaskList_SetNumberOfItemsToDisplayPerPage
GO
CREATE PROCEDURE TaskList_SetNumberOfItemsToDisplayPerPage
@Username VarChar(20),
@PasswordHash VarChar(50),
@NumberOfItems VarChar(20)
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = NULL
SELECT @CurrentUserID = ID FROM TaskListUsers WHERE Username = @Username AND PasswordHash = @PasswordHash
IF (@CurrentUserID IS NULL) Return 1;
UPDATE TaskListUsers SET ItemsToDisplay = @NumberOfItems WHERE ID = @CurrentUserID;
GO
-- =============================================
-- TaskList_UpdateCategory
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_UpdateCategory'
AND type = 'P')
DROP PROCEDURE TaskList_UpdateCategory
GO
CREATE PROCEDURE TaskList_UpdateCategory
@CategoryID int,
@Description VarChar(25),
@IconUrl VarChar(500)
AS
UPDATE TaskListCategories SET
Description = @Description,
IconUrl = @IconUrl
WHERE ID = @CategoryID;
GO
-- =============================================
-- TaskList_UpdateNotificationEmail
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_UpdateNotificationEmail'
AND type = 'P')
DROP PROCEDURE TaskList_UpdateNotificationEmail
GO
CREATE PROCEDURE TaskList_UpdateNotificationEmail
@Username VarChar(20),
@PasswordHash VarChar(50),
@NotificationEmail VarChar(5000)
AS
DECLARE @UserID int
SELECT @UserID = ID FROM TaskListUsers WHERE
Username = @Username AND PasswordHash = @PasswordHash AND IsAdministrator = 1
IF @UserID > 0
BEGIN
DELETE FROM TaskListNotification;
INSERT INTO TaskListNotification
(NotificationEmail)
VALUES
(@NotificationEmail)
END
ELSE
Return(-1);
GO
-- =============================================
-- TaskList_UpdateSortOrderPreferences
-- =============================================
-- Changes sort order preferences for a user
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_UpdateSortOrderPreferences'
AND type = 'P')
DROP PROCEDURE TaskList_UpdateSortOrderPreferences
GO
CREATE PROCEDURE TaskList_UpdateSortOrderPreferences
@Username VarChar(50),
@PasswordHash VarChar(50),
@StatusID int,
@SortOrder Int
AS
DECLARE @CurrentUserID int
SET @CurrentUserID = null
SELECT @CurrentUserID = ID FROM TaskListUsers WHERE Username = @Username AND PasswordHash = @PasswordHash
IF (@CurrentUserID IS NULL) Return 1;
--Okay now update their sort order
UPDATE TaskListSortOrderPreferences SET SortOrder = @SortOrder
WHERE UserID = @CurrentUserID AND StatusID = @StatusID
GO
-- =============================================
-- TaskList_UpdateStatusType
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_UpdateStatusType'
AND type = 'P')
DROP PROCEDURE TaskList_UpdateStatusType
GO
CREATE PROCEDURE TaskList_UpdateStatusType
@StatusID int,
@Description VarChar(25),
@StatusFontFlags VarChar(500)
AS
UPDATE TaskListStatus SET
StatusDescription = @Description,
StatusFontFlags = @StatusFontFlags
WHERE ID = @StatusID;
GO
-- =============================================
-- TaskList_UpdateUserNotification
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_UpdateUserNotification'
AND type = 'P')
DROP PROCEDURE TaskList_UpdateUserNotification
GO
CREATE PROCEDURE TaskList_UpdateUserNotification
@Username VarChar(20),
@PasswordHash VarChar(50),
@ShouldNotify bit,
@NotifyPeriod int
AS
UPDATE TaskListUsers SET ShouldNotify = @ShouldNotify, NotifyPeriod = @NotifyPeriod, LastNotified = GetDate()
WHERE Username = @Username AND PasswordHash = @PasswordHash;
GO
CREATE UNIQUE CLUSTERED INDEX [IX_TaskListAssignments] ON [dbo].[TaskListAssignments]([ID]) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [IX_TaskListItems] ON [dbo].[TaskListItems]([ID]) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [IX_TaskListNames] ON [dbo].[TaskListNames]([ID]) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [IX_TaskListUsers] ON [dbo].[TaskListUsers]([ID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TaskListAssignments] ADD
CONSTRAINT [FK_TaskListAssignments_TaskListNames] FOREIGN KEY
(
[TaskListID]
) REFERENCES [dbo].[TaskListNames] (
[ID]
),
CONSTRAINT [FK_TaskListAssignments_TaskListUsers] FOREIGN KEY
(
[UserID]
) REFERENCES [dbo].[TaskListUsers] (
[ID]
)
GO
ALTER TABLE [dbo].[TaskListItems] ADD
CONSTRAINT [FK_TaskListItems_TaskListNames] FOREIGN KEY
(
[TaskListID]
) REFERENCES [dbo].[TaskListNames] (
[ID]
)
GO
go
--Now update the TaskListStatus table with our default status values
INSERT INTO TaskListStatus (StatusDescription, StatusValue, StatusFontFlags, DefaultSortOrder)
VALUES ('In Progress', 3, '<Font Style="font=style: italic; color: Green">', 0)
GO
INSERT INTO TaskListStatus (StatusDescription, StatusValue, StatusFontFlags, DefaultSortOrder)
VALUES ('Open', 0, '<Font Style="font-style: italic">', 1)
GO
INSERT INTO TaskListStatus (StatusDescription, StatusValue, StatusFontFlags, DefaultSortOrder)
VALUES ('Indeterminate', 4, '<Font Style="font-style: italic; color: Blue">', 2)
GO
INSERT INTO TaskListStatus (StatusDescription, StatusValue, StatusFontFlags, DefaultSortOrder)
VALUES ('Hold', 2, '<Font Style="font-style: italic; color: Gray">', 3)
GO
INSERT INTO TaskListStatus (StatusDescription, StatusValue, StatusFontFlags, DefaultSortOrder)
VALUES ('Closed', 1, '<Font Style="font-style: italic; text-decoration: line-through">', 4)
GO
--Now create our default Categories
INSERT INTO TaskListCategories (Description, IconUrl)
VALUES ('General', 'images/task.gif')
GO
INSERT INTO TaskListCategories (Description, IconUrl)
VALUES ('Information', 'images/info.gif')
GO
--Now create our Administrator user (creates an administrator user with the password "newpass")
INSERT INTO TaskListUsers (Username, PasswordHash, IsAdministrator, IsManager, StartupViewOption, StartupViewArgument, ItemsToDisplay, ShouldNotify, NotifyPeriod, LastNotified)
VALUES ('administrator', 'E6053EB8D35E02AE40BEEEACEF203C1A', 1, 1, 'LastTaskList',2,'All',0,24,GetDate())
DECLARE @AdministratorUserID int
SET @AdministratorUserID = @@Identity;
--Now create our default task list ("General Task List")
EXEC TaskList_CreateTaskList 'administrator', 'E6053EB8D35E02AE40BEEEACEF203C1A', 'General Task List';
--Now assign administrator to that task list
INSERT INTO TaskListAssignments (UserID, TaskListID)
(SELECT Min(TaskListUsers.ID) AS UserID,
Min(TaskListNames.ID) AS TaskListID
FROM TaskListUsers, TaskListNames);
--Now set up the administrator user's filter and sort order
INSERT INTO TaskListSortOrderPreferences (UserID, StatusID, SortOrder)
SELECT @AdministratorUserID, TaskListStatus.ID, TaskListStatus.DefaultSortOrder
FROM TaskListStatus;
INSERT INTO TaskListStatusShowPreferences (UserID, StatusID)
SELECT UserID, StatusID
FROM TaskListSortOrderPreferences
WHERE UserID = @AdministratorUserID;
GO
use master
GO?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -