if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jobLogAndResetMonthlyCounters]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[jobLogAndResetMonthlyCounters]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jobResetPointCounters]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[jobResetPointCounters]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procAddUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procAddUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procAddUserAndInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procAddUserAndInfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procAdminAddTutorial]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procAdminAddTutorial]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procAdminInsertSpecPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procAdminInsertSpecPage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procDeleteForumSubscription]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procDeleteForumSubscription]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procDeleteNote]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procDeleteNote]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procDeleteNotes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procDeleteNotes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procDoConfirmationLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procDoConfirmationLogin]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procDoLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procDoLogin]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetAllTutorials]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetAllTutorials]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetAllUserPosts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetAllUserPosts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetArticleFileName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetArticleFileName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetArticleRepliesByPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetArticleRepliesByPage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetBookSampleList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetBookSampleList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetChannelIndex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetChannelIndex]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetChannels]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetChannels]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetEmailList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetEmailList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetFeatureArticles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetFeatureArticles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetFeaturedTutorials]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetFeaturedTutorials]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetFeatures]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetFeatures]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetForumBreadCrumbs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetForumBreadCrumbs]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetForumMessage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetForumMessage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetForumMessagesByPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetForumMessagesByPage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetForumSubscriptions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetForumSubscriptions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetForumThreadsByPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetForumThreadsByPage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetForums]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetForums]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetLatestLogins]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetLatestLogins]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetLatestTutorials]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetLatestTutorials]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetNewMessages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetNewMessages]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetNewestMembers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetNewestMembers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetNote]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetNote]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetNotesByPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetNotesByPage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetPasswordByEmail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetPasswordByEmail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetPollChoices]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetPollChoices]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetPollQuestion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetPollQuestion]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetStatistics]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetStatistics]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetTopUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetTopUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetTopUsersForTheMonth]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetTopUsersForTheMonth]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetTutorialByLetter]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetTutorialByLetter]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetTutorialCats]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetTutorialCats]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetTutorialSubCats]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetTutorialSubCats]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetTutorialText]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetTutorialText]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetUserFromID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetUserFromID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetUserInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetUserInfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetUserList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetUserList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetUserNoteStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetUserNoteStatus]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetUserPoints]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetUserPoints]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetUserPostsArticles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetUserPostsArticles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetUserPostsArticlesByPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetUserPostsArticlesByPage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetUserPostsForums]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetUserPostsForums]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetUserPostsForumsByPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetUserPostsForumsByPage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetUserSubmittedTutorials]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetUserSubmittedTutorials]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetUserSubscriptions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetUserSubscriptions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procGetYesterdaysTopMovers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procGetYesterdaysTopMovers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procNewNotes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procNewNotes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procPutAdClick]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procPutAdClick]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procPutArticleReply]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procPutArticleReply]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procPutForumMessage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procPutForumMessage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procPutForumSubscription]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procPutForumSubscription]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procPutForumThread]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procPutForumThread]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procPutNote]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procPutNote]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procPutPollVote]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procPutPollVote]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procPutStatistics]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procPutStatistics]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procQuestionGetByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procQuestionGetByID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procQuestionGetRepliesByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procQuestionGetRepliesByID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procQuestionInsertReply]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procQuestionInsertReply]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procUpdateTutorialText]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procUpdateTutorialText]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procUserUpdateInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procUserUpdateInfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[authors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[authors]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[discounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[discounts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[employee]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jobs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[jobs]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pub_info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pub_info]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[publishers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[publishers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[roysched]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[roysched]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sales]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sales]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stores]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[stores]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblAdClicks]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblAdClicks]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblArticleReplies]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblArticleReplies]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblBooks]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblBooks]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblChannelArticles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblChannelArticles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblChannels]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblChannels]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblClickTracker]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblClickTracker]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblForumMessages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblForumMessages]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblForumSubscriptions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblForumSubscriptions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblForumThreads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblForumThreads]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblForums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblForums]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblLanguageSpecification]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblLanguageSpecification]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblLogins]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblLogins]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblNotes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblNotes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPages]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPointsMonthlyHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPointsMonthlyHistory]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPoll]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPoll]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPollChoices]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPollChoices]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPollResults]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPollResults]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblQuestionReplies]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblQuestionReplies]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblQuestions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblQuestions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblStatistics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblStatistics]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTutorialCategories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTutorialCategories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTutorialSubCat]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTutorialSubCat]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTutorialText]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTutorialText]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTutorials]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTutorials]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblUserExt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblUserExt]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[titleauthor]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[titleauthor]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[titles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[titles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[titleview]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[titleview]
GO

CREATE TABLE [dbo].[authors] (
	[au_id] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[contract] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[discounts] (
	[discounttype] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[stor_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[lowqty] [smallint] NULL ,
	[highqty] [smallint] NULL ,
	[discount] [numeric](4, 2) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[employee] (
	[emp_id] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[minit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[lname] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[job_id] [smallint] NOT NULL ,
	[job_lvl] [tinyint] NULL ,
	[pub_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[hire_date] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[jobs] (
	[job_id] [smallint] NOT NULL ,
	[job_desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[min_lvl] [tinyint] NOT NULL ,
	[max_lvl] [tinyint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[pub_info] (
	[pub_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[logo] [image] NULL ,
	[pr_info] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[publishers] (
	[pub_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[pub_name] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[roysched] (
	[title_id] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[lorange] [int] NULL ,
	[hirange] [int] NULL ,
	[royalty] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[sales] (
	[stor_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ord_num] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ord_date] [datetime] NOT NULL ,
	[qty] [smallint] NOT NULL ,
	[payterms] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[title_id] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[stores] (
	[stor_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[stor_name] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[stor_address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblAdClicks] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[szURL] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[szClientName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[iClicks] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblArticleReplies] (
	[replyID] [int] IDENTITY (1, 1) NOT NULL ,
	[articleID] [int] NOT NULL ,
	[userID] [int] NOT NULL ,
	[dtCreated] [datetime] NOT NULL ,
	[szReply] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblBooks] (
	[bookID] [int] IDENTITY (1, 1) NOT NULL ,
	[ImageUrl] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Title] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Authors] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[amazonUrl] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[sampleChapterUrl] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblChannelArticles] (
	[articleID] [int] IDENTITY (1, 1) NOT NULL ,
	[channelID] [int] NOT NULL ,
	[szIcon] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[szTitle] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[szDescription] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[szFile] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[dtCreated] [datetime] NOT NULL ,
	[bFeature] [bit] NOT NULL ,
	[userID] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblChannels] (
	[channelID] [int] IDENTITY (1, 1) NOT NULL ,
	[szChannelName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[dtCreated] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblClickTracker] (
	[clicktrackID] [int] IDENTITY (1, 1) NOT NULL ,
	[ADID] [int] NOT NULL ,
	[Created] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblForumMessages] (
	[messageID] [int] IDENTITY (1, 1) NOT NULL ,
	[forumID] [int] NOT NULL ,
	[userID] [int] NOT NULL ,
	[dtCreated] [datetime] NOT NULL ,
	[szTopic] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[szMessage] [varchar] (3920) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[dtLastPost] [datetime] NULL ,
	[dtOrder] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblForumSubscriptions] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[messageID] [int] NOT NULL ,
	[userID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblForumThreads] (
	[threadID] [int] IDENTITY (1, 1) NOT NULL ,
	[forumID] [int] NOT NULL ,
	[messageID] [int] NOT NULL ,
	[userID] [int] NOT NULL ,
	[dtCreated] [datetime] NOT NULL ,
	[szThread] [varchar] (4002) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblForums] (
	[forumId] [int] IDENTITY (1, 1) NOT NULL ,
	[szName] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[szDescription] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[iDisplayOrder] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblLanguageSpecification] (
	[SpecID] [int] IDENTITY (1, 1) NOT NULL ,
	[szFileName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[szTitle] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[szText] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[dtCreated] [datetime] NOT NULL ,
	[szIndex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblLogins] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[userID] [int] NOT NULL ,
	[dtLogin] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblNotes] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[bNew] [bit] NOT NULL ,
	[iFromId] [int] NOT NULL ,
	[iToId] [int] NOT NULL ,
	[szCC] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[szBCC] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[dtDate] [datetime] NOT NULL ,
	[szSubject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[szMessage] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPages] (
	[pageID] [int] IDENTITY (1, 1) NOT NULL ,
	[szFrom] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[szTO] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[dtCreated] [datetime] NOT NULL ,
	[szMsg] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[iRec] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPointsMonthlyHistory] (
	[MonthID] [int] NOT NULL ,
	[UserID] [int] NOT NULL ,
	[UserName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PointTotal] [int] NOT NULL ,
	[PointMonths] [int] NOT NULL ,
	[Created] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPoll] (
	[pollID] [int] IDENTITY (1, 1) NOT NULL ,
	[szQuestion] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[dtCreated] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPollChoices] (
	[choiceID] [int] IDENTITY (1, 1) NOT NULL ,
	[pollID] [int] NOT NULL ,
	[szChoice] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPollResults] (
	[resultID] [int] IDENTITY (1, 1) NOT NULL ,
	[pollID] [int] NOT NULL ,
	[choiceID] [int] NOT NULL ,
	[userID] [int] NOT NULL ,
	[dtDate] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblQuestionReplies] (
	[ReplyID] [int] IDENTITY (1, 1) NOT NULL ,
	[QuestionID] [int] NOT NULL ,
	[UserID] [int] NOT NULL ,
	[Message] [varchar] (1975) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Created] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblQuestions] (
	[QuestionID] [int] IDENTITY (1, 1) NOT NULL ,
	[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [varchar] (230) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Created] [datetime] NOT NULL ,
	[UserID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblStatistics] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[dtInserted] [datetime] NOT NULL ,
	[dtCreated] [datetime] NOT NULL ,
	[iActiveUsers] [int] NOT NULL ,
	[iUsersToday] [int] NOT NULL ,
	[iPagesToday] [int] NOT NULL ,
	[iBannerTop] [int] NOT NULL ,
	[iBannerSky] [int] NOT NULL ,
	[iBannerSquare] [int] NOT NULL ,
	[iBannerButton] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTutorialCategories] (
	[iCatID] [int] IDENTITY (1, 1) NOT NULL ,
	[iOrder] [int] NULL ,
	[szCategory] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTutorialSubCat] (
	[iSubID] [int] IDENTITY (1, 1) NOT NULL ,
	[iCatID] [int] NOT NULL ,
	[szSubCategory] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTutorialText] (
	[iTextID] [int] IDENTITY (1, 1) NOT NULL ,
	[iTutorialID] [int] NOT NULL ,
	[szText] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[szTitle] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[szDescription] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[dtCreated] [datetime] NOT NULL ,
	[szIndex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[userID] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTutorials] (
	[iTutorialID] [int] IDENTITY (1, 1) NOT NULL ,
	[iCatID] [int] NOT NULL ,
	[iSubID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblUserExt] (
	[iUserID] [int] NOT NULL ,
	[szUserName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[szFName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[szLName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[iAge] [int] NULL ,
	[szEducation] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[szCountry] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[szJob] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[szSkills] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[szFavLang] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[szMyComputer] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[gConfirmation] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[IsConfirmed] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblUsers] (
	[userID] [int] IDENTITY (999, 1) NOT NULL ,
	[szUserName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[szPassword] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[dtCreated] [datetime] NOT NULL ,
	[dtLastLogin] [datetime] NOT NULL ,
	[iLogins] [int] NOT NULL ,
	[iDailyLogins] [int] NOT NULL ,
	[iPoints] [int] NOT NULL ,
	[szEmail] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[iTodaysPoints] [int] NULL ,
	[iYesterdaysPoints] [int] NULL ,
	[iOnGoingMonths] [int] NULL ,
	[bNewsletter] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[titleauthor] (
	[au_id] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[title_id] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[au_ord] [tinyint] NULL ,
	[royaltyper] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[titles] (
	[title_id] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[title] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[type] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[pub_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[price] [money] NULL ,
	[advance] [money] NULL ,
	[royalty] [int] NULL ,
	[ytd_sales] [int] NULL ,
	[notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[pubdate] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[titleview] (
	[title] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[au_ord] [tinyint] NULL ,
	[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[price] [money] NULL ,
	[ytd_sales] [int] NULL ,
	[pub_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblAdClicks] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblAdClicks] PRIMARY KEY  CLUSTERED 
	(
		[id]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblArticleReplies] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblQuestionReplies] PRIMARY KEY  CLUSTERED 
	(
		[replyID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblBooks] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblBooks] PRIMARY KEY  CLUSTERED 
	(
		[bookID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblChannelArticles] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblChannelArticles] PRIMARY KEY  CLUSTERED 
	(
		[articleID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblChannels] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblChannels] PRIMARY KEY  CLUSTERED 
	(
		[channelID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblClickTracker] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblClickTracker] PRIMARY KEY  CLUSTERED 
	(
		[clicktrackID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblForumMessages] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblForumMessages] PRIMARY KEY  CLUSTERED 
	(
		[messageID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblForumSubscriptions] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblForumSubscriptions] PRIMARY KEY  CLUSTERED 
	(
		[id]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblForumThreads] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblForumThreads] PRIMARY KEY  CLUSTERED 
	(
		[threadID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblForums] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblForums] PRIMARY KEY  CLUSTERED 
	(
		[forumId]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblLanguageSpecification] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblLanguageSpecification] PRIMARY KEY  CLUSTERED 
	(
		[SpecID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblLogins] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblLogins] PRIMARY KEY  CLUSTERED 
	(
		[id]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblNotes] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblNotes] PRIMARY KEY  CLUSTERED 
	(
		[id]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblPages] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblPages] PRIMARY KEY  CLUSTERED 
	(
		[pageID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblPoll] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblPoll] PRIMARY KEY  CLUSTERED 
	(
		[pollID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblPollChoices] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblPollChoices] PRIMARY KEY  CLUSTERED 
	(
		[choiceID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblPollResults] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblPollResults] PRIMARY KEY  CLUSTERED 
	(
		[resultID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblQuestionReplies] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblQuestionReplies_1] PRIMARY KEY  CLUSTERED 
	(
		[ReplyID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblQuestions] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblQuestions] PRIMARY KEY  CLUSTERED 
	(
		[QuestionID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblStatistics] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblStatistics] PRIMARY KEY  CLUSTERED 
	(
		[id]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblTutorialCategories] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblTutorialCategories] PRIMARY KEY  CLUSTERED 
	(
		[iCatID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblTutorialSubCat] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblTutorialSubCat] PRIMARY KEY  CLUSTERED 
	(
		[iSubID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblTutorialText] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblTutorial_Text] PRIMARY KEY  CLUSTERED 
	(
		[iTextID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblTutorials] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblTutorials] PRIMARY KEY  CLUSTERED 
	(
		[iTutorialID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblUsers] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblUsers] PRIMARY KEY  CLUSTERED 
	(
		[userID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblAdClicks] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblAdClicks_iClicks] DEFAULT (0) FOR [iClicks]
GO

ALTER TABLE [dbo].[tblArticleReplies] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblQuestionReplies_dtDate] DEFAULT (getdate()) FOR [dtCreated]
GO

ALTER TABLE [dbo].[tblChannelArticles] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblChannelArticles_dtCreated] DEFAULT (getdate()) FOR [dtCreated],
	CONSTRAINT [DF_tblChannelArticles_bFeature] DEFAULT (0) FOR [bFeature]
GO

ALTER TABLE [dbo].[tblChannels] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblChannels_dtCreated] DEFAULT (getdate()) FOR [dtCreated]
GO

ALTER TABLE [dbo].[tblClickTracker] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblClickTracker_Created] DEFAULT (getdate()) FOR [Created]
GO

ALTER TABLE [dbo].[tblForumMessages] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblForumMessages_dtCreated] DEFAULT (getdate()) FOR [dtCreated],
	CONSTRAINT [DF_tblForumMessages_dtLastPost] DEFAULT (getdate()) FOR [dtLastPost],
	CONSTRAINT [DF_tblForumMessages_dtOrder] DEFAULT (getdate()) FOR [dtOrder]
GO

ALTER TABLE [dbo].[tblForumThreads] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblForumThreads_dtCreated] DEFAULT (getdate()) FOR [dtCreated]
GO

ALTER TABLE [dbo].[tblLanguageSpecification] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblLanguageSpecification_dtCreated] DEFAULT (getdate()) FOR [dtCreated]
GO

ALTER TABLE [dbo].[tblLogins] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblLogins_dtLogin] DEFAULT (getdate()) FOR [dtLogin]
GO

ALTER TABLE [dbo].[tblNotes] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblNotes_bNew_1] DEFAULT (1) FOR [bNew],
	CONSTRAINT [DF_tblNotes_dtDate] DEFAULT (getdate()) FOR [dtDate]
GO

ALTER TABLE [dbo].[tblPages] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblPages_dtCreated] DEFAULT (getdate()) FOR [dtCreated],
	CONSTRAINT [DF_tblPages_iRec] DEFAULT (0) FOR [iRec]
GO

ALTER TABLE [dbo].[tblPointsMonthlyHistory] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblPointsMonthlyHistory_Created] DEFAULT (getdate()) FOR [Created]
GO

ALTER TABLE [dbo].[tblPoll] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblPoll_dtCreated] DEFAULT (getdate()) FOR [dtCreated]
GO

ALTER TABLE [dbo].[tblPollResults] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblPollResults_dtDate] DEFAULT (getdate()) FOR [dtDate]
GO

ALTER TABLE [dbo].[tblQuestionReplies] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblQuestionReplies_Created] DEFAULT (getdate()) FOR [Created]
GO

ALTER TABLE [dbo].[tblQuestions] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblQuestions_Created] DEFAULT (getdate()) FOR [Created]
GO

ALTER TABLE [dbo].[tblStatistics] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblStatistics_dtInserted] DEFAULT (getdate()) FOR [dtInserted],
	CONSTRAINT [DF_tblStatistics_iActiveUsers] DEFAULT (0) FOR [iActiveUsers],
	CONSTRAINT [DF_tblStatistics_iUsersToday] DEFAULT (0) FOR [iUsersToday],
	CONSTRAINT [DF_tblStatistics_iPagesToday] DEFAULT (0) FOR [iPagesToday],
	CONSTRAINT [DF_tblStatistics_iBannerTop] DEFAULT (0) FOR [iBannerTop],
	CONSTRAINT [DF_tblStatistics_iBannerSky] DEFAULT (0) FOR [iBannerSky],
	CONSTRAINT [DF_tblStatistics_iBannerSquare] DEFAULT (0) FOR [iBannerSquare],
	CONSTRAINT [DF_tblStatistics_iBannerButton] DEFAULT (0) FOR [iBannerButton]
GO

ALTER TABLE [dbo].[tblTutorialText] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblTutorialText_dtCreated] DEFAULT (getdate()) FOR [dtCreated],
	CONSTRAINT [DF_tblTutorialText_userID] DEFAULT (1018) FOR [userID]
GO

ALTER TABLE [dbo].[tblUserExt] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblUserExt_IsConfirmed] DEFAULT (0) FOR [IsConfirmed]
GO

ALTER TABLE [dbo].[tblUsers] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblUsers_dtCreated] DEFAULT (getdate()) FOR [dtCreated],
	CONSTRAINT [DF_tblUsers_dtLastLogin] DEFAULT (getdate()) FOR [dtLastLogin],
	CONSTRAINT [DF_tblUsers_iLogins] DEFAULT (0) FOR [iLogins],
	CONSTRAINT [DF_tblUsers_iDailyLogins] DEFAULT (0) FOR [iDailyLogins],
	CONSTRAINT [DF_tblUsers_iPoints] DEFAULT (10) FOR [iPoints],
	CONSTRAINT [DF_tblUsers_iTodaysPoints] DEFAULT (0) FOR [iTodaysPoints],
	CONSTRAINT [DF_tblUsers_iYesterdaysPoints] DEFAULT (0) FOR [iYesterdaysPoints],
	CONSTRAINT [DF_tblUsers_iOnGoingMonths] DEFAULT (0) FOR [iOnGoingMonths],
	CONSTRAINT [DF_tblUsers_bNewsletter] DEFAULT (1) FOR [bNewsletter],
	CONSTRAINT [IX_tblUser_szUserName] UNIQUE  NONCLUSTERED 
	(
		[szUserName]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE jobLogAndResetMonthlyCounters
AS

SET XACT_ABORT ON

BEGIN TRANSACTION

--insert to history table according to month
INSERT tblPointsMonthlyHistory (MonthID, UserID, UserName, PointTotal, PointMonths)
SELECT  datepart(m,getDate()), userID, szUserName,iPoints,iOnGoingMonths
FROM tblUsers
ORDER BY iOnGoingMonths DESC	

--reset monthly total to 0 for new month
UPDATE tblUsers
	Set iOnGoingMonths = 0

IF @@ERROR <> 0
	ROLLBACK TRANSACTION
COMMIT TRANSACTION

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE jobResetPointCounters
AS

SET XACT_ABORT ON

BEGIN TRANSACTION
	
	UPDATE tblUsers
		-- add each days point total to on_going_months total
		SET iOnGoingMonths = iOnGoingMonths + iTodaysPoints,
		-- set yesterdays pts from today's for top movers section, reset today's pts
		 iYesterdaysPoints = iTodaysPoints,
		 iTodaysPoints = 0

IF @@ERROR <> 0
	ROLLBACK TRANSACTION
COMMIT TRANSACTION

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procAddUser
@szUserName varchar(15),
@szFName varchar(50),
@szLName varchar(50),
@szEmail varchar(80),
@szPassword varchar(15),
@gConfirm	CHAR(32),
--@iVerifty	INT OUTPUT	-- 1 is pass, 0 is failed (duplicate username etc)
@szNewMemberNoteBody VARCHAR(250)

AS
IF NOT EXISTS (SELECT szUserName FROM tblUsers WHERE szUserName = @szUserName)
BEGIN

	SELECT iVerifty = 1	--passed

	BEGIN TRANSACTION
	
	--	INSERT INTO tblUsers (szUserName, szFName, szLName, szEmail, szPassword)
	--	VALUES (@szUserName, @szFName, @szLName, @szEmail, @szPassword)
		INSERT INTO tblUsers (szUserName, szPassword, szEmail)
		VALUES (@szUserName, @szPassword, @szEmail)
		
		DECLARE @userID INT  --just inserted this userID
		SELECT @userID = @@IDENTITY
		
		INSERT INTO tblUserExt (iUserID, szUserName, szFName, szLName,gConfirmation)
		VALUES (@userID, @szUserName, @szFName, @szLName, @gConfirm )

		INSERT tblNotes (iFromId, iToID, szSubject, szMessage)
		VALUES(999,999,'Welcome friend!',@szNewMemberNoteBody)
		
	IF @@ERROR <> 0
		ROLLBACK TRANSACTION
	ELSE
		COMMIT TRANSACTION

END
ELSE
	SELECT iVerifty = 0	-- FAILED! Duplicate szUserName



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procAddUserAndInfo
@szUserName varchar(15),
@szFName varchar(50),
@szLName varchar(50),
@szEmail varchar(80),
@szPassword varchar(15),
--user info
@iAge		INT,
@szEducation	VARCHAR(50),
@szCountry	VARCHAR(20),
@szJob		VARCHAR(50),
@szSkills	VARCHAR(50),
@szFavLang	VARCHAR(50),
@szMyComputer	VARCHAR(100),
--/user info
@gConfirm	CHAR(32),
@szNewMemberNoteBody VARCHAR(250)

AS
IF NOT EXISTS (SELECT szUserName FROM tblUsers WHERE szUserName = @szUserName)
BEGIN

	SELECT iVerifty = 1	--passed

	BEGIN TRANSACTION
	
	--	INSERT INTO tblUsers (szUserName, szFName, szLName, szEmail, szPassword)
	--	VALUES (@szUserName, @szFName, @szLName, @szEmail, @szPassword)
		INSERT INTO tblUsers (szUserName, szPassword, szEmail)
		VALUES (@szUserName, @szPassword, @szEmail)
		
		DECLARE @userID INT  --just inserted this userID
		SELECT @userID = @@IDENTITY
		
		INSERT INTO tblUserExt (iUserID, szUserName, szFName, szLName,iAge, szEducation, szCountry, szJob, szSkills, szFavLang, szMyComputer,gConfirmation)
		VALUES (@userID, @szUserName, @szFName, @szLName,@iAge, @szEducation, @szCountry, @szJob, @szSkills, @szFavLang, @szMyComputer, @gConfirm )

		INSERT tblNotes (iFromId, iToID, szSubject, szMessage)
		VALUES(1018,@userID,'Welcome friend!',@szNewMemberNoteBody)
		
	IF @@ERROR <> 0
		ROLLBACK TRANSACTION
	ELSE
		COMMIT TRANSACTION

END
ELSE
	SELECT iVerifty = 0	-- FAILED! Duplicate szUserName



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




-- use when giving user points for TUTORIALS

CREATE PROCEDURE procAdminAddTutorial
@userID	INT
AS
UPDATE tblUsers
	SET iPoints = iPoints + 250,
	        iTodaysPoints = iTodaysPoints + 250
WHERE userID = @userID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE  procAdminInsertSpecPage
@szFileName	varchar(30),
@szTitle	varchar(100),
@szText	NTEXT,
@szIndex	Char(1)

AS

INSERT tblLanguageSpecification (szFileName, szTitle, szText, szIndex)
VALUES (@szFileName, @szTitle, @szText, @szIndex)



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




--Deletes a user to a Forum Message Email List that sends out a email upon every reply to message

CREATE PROCEDURE procDeleteForumSubscription
@userID INT,
@MessageID INT
AS

DELETE tblForumSubscriptions
WHERE userID=@userID AND messageID=@messageID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procDeleteNote
@userID INT,
@noteID INT
AS

DELETE tblNotes 
WHERE iToID = @userID AND [id]=@noteID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procDeleteNotes
@userID VARCHAR(20),
@szWhereClause VARCHAR(255)
AS

DECLARE @SQL VARCHAR(300)

SET @SQL = 'DELETE tblNotes '
SET @SQL = @SQL + 'WHERE iToID='  +  @userID +  ' AND id IN (' + @szWhereClause + ')' 

EXEC (@SQL)



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




/*Login User*/
CREATE Procedure procDoConfirmationLogin
@szUserName 	NVARCHAR(50),
@gConfirm   	CHAR(32),
@iVerify		INT OUTPUT
AS

IF EXISTS (Select u.szUserName FROM tblUsers u
		INNER JOIN tblUserExt ex ON (u.szUserName = ex.szUserName)
		WHERE u.szUserName = @szUserName AND ex.gConfirmation = @gConfirm AND ex.IsConfirmed=0)
BEGIN

	SELECT @iVerify = 1

	UPDATE tblUserExt
		SET IsConfirmed = 1
	WHERE gConfirmation = @gConfirm
END
ELSE
	SELECT @iVerify = 0



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




/*Login User*/
CREATE Procedure procDoLogin
@szUserName 	NVARCHAR(50),
@szPassword   	NVARCHAR(50),
@PTS_LOGIN 	INT,
@userID 	INT OUTPUT
AS

IF EXISTS (Select u.szUserName FROM tblUsers u
		INNER JOIN tblUserExt ex ON (u.szUserName = ex.szUserName)
		WHERE u.szUserName = @szUserName AND u.szPassword = @szPassword AND ex.IsConfirmed=1)

BEGIN
		
	SELECT @userID = userID
	FROM tblUsers 
	WHERE szUserName = @szUserName AND szPassword = @szPassword


	UPDATE tblUsers 
		SET dtLastLogin = getDate(), iLogins = iLogins + 1,
		        iDailyLogins = Case 
 				-- allow login points daily ONLY
				WHEN DATEDIFF(day, dtLastLogin, getdate()) > 0 THEN iDailyLogins + 1
				
			         --already logged in today
			         ELSE iDailyLogins
			         END,
		        iPoints = Case 
 				-- allow login points daily ONLY
				WHEN DATEDIFF(day, dtLastLogin, getdate()) > 0 THEN iPoints + @PTS_LOGIN
				
			         --already logged in today 
			         ELSE iPoints
			         END,
		       -- login points added to today's ponits counter aswell
		       iTodaysPoints = Case 
				-- allow login points daily ONLY
				WHEN DATEDIFF(day, dtLastLogin, getdate()) > 0 THEN iTodaysPoints + @PTS_LOGIN 
				
			         --already logged in today
			         ELSE iTodaysPoints
			         END

	WHERE userID = @userID


	INSERT INTO tblLogins (userID) 
	VALUES (@userID)

END

ELSE
	--failed login
	SELECT @userID = 0



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetAllTutorials
AS
SELECT  iTutorialID, szTitle, szDescription, dtCreated
FROM tblTutorialText
ORDER BY dtCreated DESC


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetAllUserPosts
@userID INT
AS

--forums
SELECT * from tblForumMessages where userID=@userID
SELECT * from tblForumThreads where userID=@userID

--Articles
SELECT * from tblArticleReplies where userID=@userID

--Notes
SELECT * from tblNotes where iFromId = @userID
SELECT * from tblNotes where iToId = @userID




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




/*
	purpose:  grab article filename
*/
CREATE Procedure procGetArticleFileName
@articleID 	int,
@szFileName	varchar(20) OUTPUT

AS
SELECT @szFileName = szFile
FROM tblChannelArticles
WHERE articleID = @articleID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE [procGetArticleRepliesByPage]
@articleID int,
@CurrentPage int,
@PageSize int,
@TotalRecords int output
AS
--Create a temp table to hold the current page of data
--Add and ID column to count the records
DECLARE @tempTable table ( ID INT IDENTITY PRIMARY KEY, userID INT, szUserName VARCHAR(15), dtCreated DATETIME, szReply VARCHAR(3000) )
/*
CREATE TABLE #TempTable
(
  ID int IDENTITY PRIMARY KEY,
  userID	int,
  szUserName	varchar (15),
  dtCreated	datetime,
  szReply	varchar (3000)
)
*/
--Fill the temp table with the Customers data
INSERT INTO @tempTable --#TempTable 
(
  userID,
  szUserName,
  dtCreated,
  szReply
)
SELECT u.userID,u.szUserName, q.dtCreated, q.szReply
FROM tblArticleReplies q
	INNER JOIN tblUsers u ON (u.userId=q.userId)
WHERE articleID = @articleID
ORDER BY q.replyID desc


--Create variable to identify the first and last record that should be selected
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT 
    userID,
    szUserName,
    dtCreated,
    szReply
FROM 
  @TempTable --#TempTable
WHERE   ID > @FirstRec AND  ID < @LastRec
--Return the total number of records available as an output parameter
SELECT @TotalRecords = COUNT(*) FROM tblArticleReplies WHERE articleID = @articleID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetBookSampleList
AS

SELECT *
FROM tblBooks



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetChannelIndex
@channelID INT
AS
SELECT channelID, articleID, szTitle, szDescription
FROM tblChannelArticles
WHERE channelID = @channelID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




/*
	purpose:  grab a all channel categories
*/
CREATE Procedure procGetChannels
AS
SELECT channelID, szChannelName
FROM tblChannels



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetEmailList
AS
SELECT szEmail, szUserName, iPoints, iOnGoingMonths
FROM tblUsers
WHERE userID IN (999,1018)



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




/*
grab all articles with bFeature set to 1, only 4 articles will be set as feature at any given time.
*/
CREATE PROCEDURE procGetFeatureArticles
AS
SELECT TOP 4 szIcon,articleID,szTitle, szDescription
FROM tblChannelArticles
WHERE bFeature = 1	-- 4 articles or so will be set to 1
ORDER BY dtCreated DESC



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetFeaturedTutorials
AS

SELECT TOP 15 iTutorialID, szTitle, szDescription
FROM tblTutorialText
ORDER BY dtCreated DESC
--SELECT TOP 4 t.szIcon,t.szFile, t.szTitle, t.szDescription
/*
SELECT TOP 4 t.iTutorialID, tx.szTitle, tx.szDescription
FROM tblTutorials t
	INNER JOIN tblTutorialCategories c ON (c.iCatID = t.iCatID)
	INNER JOIN tblTutorialSubCat s ON (s.iSubID = t.iSubID)
	INNER JOIN tblTutorialText tx ON (tx.iTutorialID = t.iTutorialID)
ORDER BY dtCreated ASC
*/


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetFeatures
AS
SELECT szTitle
FROM tblChannelArticles



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetForumBreadCrumbs
@forumID INT,
@messageID INT
AS
SELECT f.szName
FROM tblForumMessages m
	INNER JOIN tblForums f ON (f.forumID=m.forumID)
WHERE m.forumID=@forumID and m.messageID=@messageID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




--used when viewing threads, the message appears on the top, all threads on bottom
CREATE PROCEDURE procGetForumMessage
@forumID INT,
@messageID INT
AS
SELECT u.userID,u.szUserName, f.szTopic,f.szMessage, f.dtCreated
FROM tblForumMessages f
	INNER JOIN tblUsers u ON (u.userID = f.userID)
WHERE f.forumID = @forumID AND f.messageID = @messageID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE Procedure procGetForumMessagesByPage
@forumID INT,
@CurrentPage int,
@PageSize int,
@TotalRecords int output
AS
--Create a temp table to hold the current page of data
--Add and ID column to count the records
DECLARE @tempTable table ( ID INT IDENTITY PRIMARY KEY,
				forumID INT,
				messageID INT,
				szTopic VARCHAR(250),
				szMessage VARCHAR(300),
				iReplies INT,
				userID INT,
				szUserName VARCHAR(15),
				dtLastPost DATETIME )
			

INSERT INTO @tempTable --#TempTable 
(
 forumID,
 messageID,
 szTopic,
 szMessage,
 iReplies,
 userID,
 szUserName,
 dtLastPost
)
SELECT f.forumID,f.messageID,f.szTopic,SUBSTRING(f.szMessage,1,300), S.iReplies, u.userID,u.szUserName, S.dtLastPost
FROM tblForumMessages f
	INNER JOIN tblUsers u ON (u.userID = f.userID)
	LEFT OUTER JOIN (SELECT messageID, count(threadID) AS iReplies, MAX(dtCreated) AS dtLastPost FROM tblForumThreads GROUP BY messageID) AS S ON (S.messageID=f.messageID)
WHERE f.forumID = @forumID
ORDER BY f.dtOrder DESC,  f.messageID DESC

--Create variable to identify the first and last record that should be selected
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT 
   forumID,
   messageID,
   szTopic,
   szMessage,
   iReplies,
   userID,
   szUserName,
   dtLastPost
FROM 
  @TempTable --#TempTable
WHERE   ID > @FirstRec AND  ID < @LastRec
--Return the total number of records available as an output parameter
SELECT @TotalRecords = COUNT(*) FROM tblForumMessages WHERE forumID = @forumID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetForumSubscriptions
@MessageID INT
AS
SELECT u.szEmail
FROM tblForumSubscriptions fs 
	INNER JOIN tblUsers u ON (fs.userID = u.userID)
WHERE fs.messageID = @MessageID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE [procGetForumThreadsByPage]
@forumID 	INT,
@messageID 	INT,
@CurrentPage int,
@PageSize int,
@TotalRecords int output
AS
--Create a temp table to hold the current page of data
--Add and ID column to count the records
--DECLARE @tempTable table ( ID INT IDENTITY PRIMARY KEY, userID INT, szUserName VARCHAR(15), dtCreated DATETIME, szReply VARCHAR(3000) )
DECLARE @tempTable table ( ID INT IDENTITY PRIMARY KEY,userID INT,  szUserName VARCHAR(15), dtCreated DATETIME ,szThread VARCHAR(3000))
--Fill the temp table with the Customers data
INSERT INTO @tempTable --#TempTable 
(
 userID,
 szUserName,
 dtCreated,
 szThread
)
SELECT u.userID, u.szUserName,f.dtCreated, f.szThread
FROM tblForumThreads f
	INNER JOIN tblUsers u ON (u.userID = f.userID)
WHERE f.forumID = @forumID and f.MessageID = @messageID
ORDER BY f.threadID ASC --DESC

--Create variable to identify the first and last record that should be selected
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT 
  userID,
  szUserName,
  dtCreated,
  szThread
FROM 
  @TempTable --#TempTable
WHERE   ID > @FirstRec AND  ID < @LastRec
--Return the total number of records available as an output parameter
SELECT @TotalRecords = COUNT(*) FROM tblForumThreads WHERE forumID = @forumID AND messageID = @messageID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




/*
	purpose:  grab a all forums
*/
CREATE Procedure procGetForums
AS
SELECT f.forumID,f.szName, f.szDescription, S.iTopics,T.iPosts, T.dtLastPosted
FROM tblForums f
	LEFT OUTER JOIN (SELECT forumID, COUNT(forumID) as iTopics  FROM tblForumMessages m GROUP BY forumID)
	 AS S ON (f.forumID=S.forumID)
	LEFT OUTER JOIN (SELECT forumID, COUNT(forumID) as iPosts, max(dtCreated) as dtLastPosted FROM tblForumThreads ft GROUP BY forumID)
	 AS T ON (f.forumID=T.forumID)
ORDER BY f.iDisplayOrder ASC



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetLatestLogins
AS
SELECT TOP 20 u.userID, u.szUserName
FROM tblLogins l
	INNER JOIN tblUsers u ON (u.userID = l.userID)
WHERE u.userid <> 1018  --webmaster account
ORDER BY l.id DESC
/*
SELECT TOP 20 u.userID, u.szUserName
FROM tblLogins l
	INNER JOIN tblUsers u ON (u.userID = l.userID)
ORDER BY dtLogin DESC


SELECT TOP 20 u.userID, u.szUserName
FROM tblLogins l
	INNER JOIN tblUsers u ON (u.userID = l.userID)
WHERE u.userid <> 1018
ORDER BY l.id DESC

*/



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetLatestTutorials
AS
SELECT TOP 20 iTutorialID, szTitle, szDescription, dtCreated
FROM tblTutorialText
ORDER BY dtCreated DESC




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




/*
Grab the latest messages, used in the front page
*/
CREATE PROCEDURE procGetNewMessages
AS
SELECT TOP 7 forumID, messageID, szTopic, SUBSTRING(szMessage,0,100) as szMessage
FROM tblForumMessages
WHERE forumID <> 4
ORDER BY dtCreated DESC



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetNewestMembers
AS
SELECT TOP 10 userID, szUserName
FROM tblUsers
ORDER BY userID DESC
/* mod april08 2002
SELECT TOP 10 userID, szUserName
FROM tblUsers
ORDER BY dtCreated DESC
*/



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




/*
  grab the specified note for a user
  dec 25, 2001
*/
CREATE PROCEDURE procGetNote
@userID INT,
@noteID INT
AS

UPDATE tblNotes
	SET bNew = 0
WHERE [id] = @noteID AND iToID = @userID

SELECT n.id, n.bNew, u.szUserName, u2.szUserName, n.szSubject, n.dtDate, n.szMessage
FROM tblNotes n
	INNER JOIN tblUsers u ON (n.iFromID = u.userID)
	INNER JOIN tblUsers u2 ON (n.iToID = u2.userID)
WHERE iToID = @userID AND id=@noteID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE Procedure procGetNotesByPage
@userID INT,
@CurrentPage int,
@PageSize int,
@TotalRecords int output
AS
--Create a temp table to hold the current page of data
--Add and ID column to count the records
DECLARE @tempTable table ( ID INT IDENTITY PRIMARY KEY,
				bNew	BIT,   -- new or old note?
				iNoteId INT,
				iFromUserID INT, 
				szFrom VARCHAR(15),
--				szTo VARCHAR(15),
				szSubject VARCHAR(100),
				dtDate DATETIME
				)
INSERT INTO @tempTable --#TempTable 
(
 bNew, 
 iNoteID,
 iFromUserID,
 szFrom,
 --szTo,
 szSubject,
 dtDate
)
--SELECT  n.bNew,n.id,u.userID,u.szUserName, u2.szUserName, n.szSubject ,n.dtDate
SELECT  n.bNew,n.id,u.userID,u.szUserName, n.szSubject ,n.dtDate
FROM tblNotes n
	INNER JOIN tblUsers u ON (u.userID = n.iFromID)
--	INNER JOIN tblUsers u2 ON (u2.userID = n.iToID)
WHERE n.iToID = @userID
ORDER BY n.dtDate DESC

--Create variable to identify the first and last record that should be selected
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT 
   [id],
   bNew,  
   iNoteId,  
   iFromUserID,
   szFrom,
--   szTo,
   szSubject,
   dtDate
FROM 
  @TempTable --#TempTable
WHERE   ID > @FirstRec AND  ID < @LastRec
--Return the total number of records available as an output parameter
SELECT @TotalRecords = COUNT(*) FROM tblNotes WHERE iToID = @userID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO



CREATE PROCEDURE procGetPasswordByEmail

@szEmail	VARCHAR(100)

AS

select szUserName, szPassword
from tblUsers
where szEmail = @szEmail


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetPollChoices
@pollID INT
AS
SELECT choiceID, szChoice
FROM tblPollChoices
WHERE pollID = @pollID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetPollQuestion
@pollID INT
AS

SELECT pollID, szQuestion
FROM tblPoll 
WHERE pollID = @pollID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetStatistics
AS

SELECT TOP 1 *
FROM tblStatistics
ORDER BY ID DESC



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetTopUsers

AS

SELECT TOP 6 userID, szUserName, iPoints
FROM tblUsers
WHERE userID <> 1018  --dont include webmaster!
ORDER BY iPoints DESC



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetTopUsersForTheMonth

AS

SELECT TOP 6 userID, szUserName, iOnGoingMonths
FROM tblUsers
WHERE userID <> 1018  --dont include webmaster!
ORDER BY iOnGoingMonths DESC



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




--returns all tutorials by the given alphabetical index
CREATE PROCEDURE procGetTutorialByLetter
@szIndex	CHAR(1)
AS

SELECT iTutorialID, szTitle, szDescription, dtCreated
FROM tblTutorialText
WHERE szIndex = @szIndex
ORDER BY iTutorialID DESC


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetTutorialCats
AS
SELECT iCatID, szCategory
FROM tblTutorialCategories
Order by iOrder



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetTutorialSubCats
AS
SELECT iCatID, iSubID, szSubCategory
FROM tblTutorialSubCat
ORDER BY iCatID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




/*
	purpose:  grab tutorial text
*/
CREATE PROCEDURE procGetTutorialText
@tutorialID 	INT
AS
SELECT szTitle, szText
FROM tblTutorialText
WHERE iTutorialID = @TutorialID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




--retrieve szUserName from ID

CREATE PROCEDURE procGetUserFromID
@userID INT
AS

SELECT szUserName
FROM tblUsers
WHERE userID = @userID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetUserInfo
@iUserID INT
AS
SELECT ux.szFName, ux.szLName, ux.iAge, ux.szEducation, ux.szCountry, u.dtCreated, u.iLogins,u.iPoints,u.iOnGoingMonths,
	u.dtLastLogin, ux.szJob, ux.szSkills, ux.szFavLang, ux.szMyComputer
FROM tblUserExt ux
	INNER JOIN tblUsers u ON (u.userID = ux.iUserID)
WHERE iUserID = @iUserID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetUserList
AS
SELECT *
FROM tblUsers



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




--user has new notes?
CREATE PROCEDURE procGetUserNoteStatus
@userID	INT
AS
SELECT COUNT(*) 
FROM tblNotes
WHERE bNew = 1 AND iToID = @userID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetUserPoints
@iUserID INT
AS
SELECT iPoints 
FROM tblUsers
WHERE UserID = @iUserID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetUserPostsArticles
@iUserID INT
AS
SELECT ca.szTitle, ar.dtCreated, ar.szReply
FROM tblArticleReplies ar
	INNER JOIN tblChannelArticles ca ON (ca.articleID = ar.articleID)
WHERE ar.userID = @iUserID
ORDER BY ar.ReplyID DESC



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE [procGetUserPostsArticlesByPage]
@iUserID	INT,
@CurrentPage int,
@PageSize int,
@TotalRecords int output
AS
DECLARE @tempTable table ( ID INT IDENTITY PRIMARY KEY,
				szTitle VARCHAR(250),
				dtCreated DATETIME ,
				szReply VARCHAR(3000)
			        )
--Fill the temp table with the Customers data
INSERT INTO @tempTable --#TempTable 
(
 szTitle,
 dtCreated,
 szReply
)
SELECT ca.szTitle, ar.dtCreated, ar.szReply
FROM tblArticleReplies ar
	INNER JOIN tblChannelArticles ca ON (ca.articleID = ar.articleID)
WHERE ar.userID = @iUserID
ORDER BY ar.ReplyID DESC

--Create variable to identify the first and last record that should be selected
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT 
  szTitle,
  dtCreated,
  szReply
FROM 
  @TempTable --#TempTable
WHERE   ID > @FirstRec AND  ID < @LastRec
--Return the total number of records available as an output parameter
SELECT @TotalRecords = COUNT(*) FROM tblAritcleReplies WHERE userID = @iUserID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetUserPostsForums
@iUserID INT
AS
SELECT fm.szTopic, ft.dtCreated, ft.szThread
FROM tblForumThreads ft
	INNER JOIN tblForumMessages fm ON (ft.messageID = fm.messageID)
WHERE ft.userID = @iUserID
ORDER BY ft.threadID DESC



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE [procGetUserPostsForumsByPage]
@iUserID	INT,
@CurrentPage int,
@PageSize int,
@TotalRecords int output
AS
DECLARE @tempTable table ( ID INT IDENTITY PRIMARY KEY,
				forumID INT,
				messageID INT,
				szTopic VARCHAR(250),
				dtCreated DATETIME ,
				szThread VARCHAR(3000)
			        )
--Fill the temp table with the Customers data
INSERT INTO @tempTable --#TempTable 
(
 forumID,
 messageID,
 szTopic,
 dtCreated,
 szThread
)
SELECT ft.forumID, ft.MessageID, fm.szTopic, ft.dtCreated, ft.szThread
FROM tblForumThreads ft
	INNER JOIN tblForumMessages fm ON (ft.messageID = fm.messageID)
WHERE ft.userID = @iUserID
ORDER BY ft.threadID DESC

--Create variable to identify the first and last record that should be selected
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT 
  forumID,
  messageID,
  szTopic,
  dtCreated,
  szThread
FROM 
  @TempTable --#TempTable
WHERE   ID > @FirstRec AND  ID < @LastRec
--Return the total number of records available as an output parameter
SELECT @TotalRecords = COUNT(*) FROM tblForumThreads WHERE userID = @iUserID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




-- grab all tutorials that a specific user (userID),submitted 
CREATE PROCEDURE procGetUserSubmittedTutorials
@userID	INT
AS
SELECT iTutorialID, szTitle, szDescription, dtCreated
FROM tblTutorialText
WHERE userID = @userID
ORDER BY dtCreated DESC



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




-- grab all tutorials that a specific user (userID),submitted 
CREATE PROCEDURE procGetUserSubscriptions
@userID	INT
AS
SELECT fm.forumID,fm.messageID, fm.szTopic,fm.dtLastPost
FROM tblForumSubscriptions fs
	INNER JOIN tblForumMessages fm ON (fm.messageID = fs.messageID)
WHERE fs.userID = @userID
ORDER BY fm.dtLastPost DESC



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procGetYesterdaysTopMovers
 AS
SELECT TOP 6 userID, szUserName, iYesterdaysPoints
FROM tblUsers
WHERE userID <> 1018
ORDER BY iYesterdaysPoints DESC



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procNewNotes
@userID INT,
@bNew INT OUTPUT
AS
IF EXISTS ( 	SELECT TOP 1 [id]
		FROM tblNotes 
		WHERE iToID = @userID AND bNew = 1  -- notes that have not been viewed, make sure bNew is indexed!
	    )
	SELECT @bNew = 1  -- NEW NOTES
ELSE
	SELECT @bNew = 0  -- NO NEW NOTES



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procPutAdClick
@ADID	INT
AS

--grab url for redirect
SELECT szURL
FROM tblAdClicks
WHERE id =@ADID
	
--update clickthroughts
UPDATE tblAdClicks
	SET iClicks = iClicks + 1
WHERE id = @ADID

INSERT tblClickTracker (ADID)
VALUES (@ADID)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




/*
	purpose:  inserts a Reply from a article/question
*/
CREATE Procedure procPutArticleReply
@userID	INT,
@articleID 	INT,
@szReply 	varchar(3000),
@ReplyPts 	INT
AS

INSERT INTO tblArticleReplies (articleID, userID, szReply)
VALUES(@articleID, @userID, @szReply)


UPDATE tblUsers 
	SET iPoints = iPoints + @ReplyPts,
	        iTodaysPoints = iTodaysPoints + @ReplyPts
WHERE userID = @userID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




/*
	purpose:  inserts a message into a forum
*/
CREATE Procedure procPutForumMessage
@userID	INT,
@forumID 	INT,
@szTopic          VARCHAR(250),
@szMessage 	VARCHAR(3000),
@Pts_FORUM_POST INT
AS

INSERT INTO tblForumMessages (forumID, userID, szTopic,szMessage)
VALUES(@forumID, @userID,@szTopic, @szMessage)

UPDATE tblUsers 
	SET iPoints = iPoints + @Pts_FORUM_POST,
	        iTodaysPoints = iTodaysPoints + @Pts_FORUM_POST
WHERE userID = @userID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




-- Adds a user to a Forum Message Email List that sends out a email upon every reply to message

CREATE PROCEDURE procPutForumSubscription
@userID INT,
@MessageID INT
AS

IF NOT EXISTS (SELECT * FROM tblForumSubscriptions WHERE userID = @userID and messageID = @messageID )
BEGIN
	INSERT tblForumSubscriptions (userID, messageID)
	VALUES (@userID, @MessageID)
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




/*
	purpose:  inserts a thread into a message/forum
*/
CREATE Procedure procPutForumThread
@userID	INT,
@forumID 	INT,
@messageID 	INT,
@commentThread 	varchar(3000),
@Pts_FORUM_REPLY int
AS

INSERT INTO tblForumThreads(forumID, messageID,userID, szThread)
VALUES(@forumID, @messageID, @userID, @commentThread)

--update the tblForumMessage dtOrder so that this thread will bubble up to the top of the threads list
UPDATE tblForumMessages
	SET dtOrder = getDate()
WHERE messageID = @messageID and forumID = @forumID

UPDATE tblUsers 
	SET iPoints = iPoints + @Pts_FORUM_REPLY,
                     iTodaysPoints = iTodaysPoints + @Pts_FORUM_REPLY
WHERE userID = @userID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procPutNote
@userID INT,
@szTo VARCHAR(15),
@szCC VARCHAR(30),
@szBCC VARCHAR(30),
@szSubject VARCHAR(100),
@szMessage VARCHAR(3000),
@bPass INT OUTPUT
AS

-- find userID for szTo since tblNotes takes id not username
DECLARE @iToId INT
--ensure use exists
IF EXISTS (SELECT userId FROM tblUsers WHERE szUserName = @szTo)
BEGIN
	
	SELECT @bPASS = 1	--note successfull	
	
	SELECT @iToId = userId
	FROM tblUsers
	WHERE szUserName = @szTo

	INSERT tblNotes (iFromId, iToId, szCC, szBCC, szSubject, szMessage)
	VALUES (@userID, @iToId, @szCC, @szBCC, @szSubject, @szMessage)
END
ELSE
	SELECT @bPASS = 0	--failed, report error



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procPutPollVote
@pollID INT,
@choiceID INT,
@userID INT
AS

INSERT tblPollResults (pollID, choiceID, userID)
VALUES (@pollID, @choiceID, @userID)



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procPutStatistics
@dtCreated	DATETIME,
@activeUsers	INT,
@usersToday	INT,
@pagesToday	INT,
@bannerTop	INT,
@bannerSky	INT,
@bannerSquare INT,
@bannerButton INT
AS

INSERT tblStatistics (dtCreated, iActiveUsers, iUsersToday, iPagesToday, iBannerTop,
	 iBannerSky, iBannerSquare, iBannerButton)
VALUES (@dtCreated, @activeUsers, @usersToday, @pagesToday, @bannerTop, @bannerSky,
	@bannerSquare, @bannerButton)



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procQuestionGetByID
--@SiteID	INT,
@QuestionID	INT

AS

SELECT q.Title, q.Description, q.Created, q.UserID, u.szUserName
FROM tblQuestions q
	INNER JOIN tblUsers u ON (q.UserID = u.UserID)
WHERE q.QuestionID = @QuestionID -- AND q.SiteID = @SiteID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procQuestionGetRepliesByID
--@SiteID	INT,
@QuestionID	INT,
@CurrentPage	INT,
@PageSize	INT,
@TotalRecords	INT OUTPUT
AS

--create temp table
DECLARE @tempTable TABLE 	(
				ID 		INT IDENTITY PRIMARY KEY,
				UserID		INT,
				UserName 	VARCHAR(15),
				Message	VARCHAR(1975),
				Created		DATETIME
			             )
--fill temp table 
INSERT INTO @tempTable	(UserID, UserName, Message, Created)

SELECT qr.UserID, u.szUserName, qr.Message, qr.Created
FROM tblQuestionReplies qr
	INNER JOIN tblUsers u ON (u.UserID = qr.UserID)
WHERE qr.QuestionID = @QuestionID -- AND qr.SiteID = @SiteID
ORDER BY qr.replyID DESC

--Create variable to identify the first and last record that should be selected
DECLARE @FirstRec INT, @LastRec INT
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)


--Select one page of data based on the record numbers above
SELECT UserID, UserName, Message, Created
FROM @tempTable --#TempTable
WHERE   ID > @FirstRec AND  ID < @LastRec


--Return the total number of records available as an output parameter
SELECT @TotalRecords = COUNT(*) 
FROM tblQuestionReplies 
WHERE QuestionID = @QuestionID --AND SiteID = @SiteID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procQuestionInsertReply
@ReplyPoints	INT,
@QuestionID	INT,
@UserID	INT,
@Message	VARCHAR(1975)

AS

INSERT tblQuestionReplies (QuestionID, UserID, Message)
VALUES (@QuestionID, @UserID, @Message)

UPDATE tblUsers 
	SET iPoints = iPoints + @ReplyPoints,
	        iTodaysPoints = iTodaysPoints + @ReplyPoints
WHERE userID = @userID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO






CREATE PROCEDURE procUpdateTutorialText
@tutorialID	 INT,
@szText	NTEXT
AS

UPDATE tblTutorialText
	SET szText = @szText
WHERE iTutorialID = @tutorialID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE procUserUpdateInfo
@userID	VARCHAR(15),
@szFName	VARCHAR(50),
@szLName	VARCHAR(50),
@iAge		INT,
@szEducation	VARCHAR(50),
@szCountry	VARCHAR(20),
@szJob		VARCHAR(50),
@szSkills	VARCHAR(50),
@szFavLang	VARCHAR(50),
@szMyComputer	VARCHAR(100)
AS

UPDATE tblUserExt 
	SET szFName = @szFName, 
	szLName = @szLName, 
	iAge = @iAge, 
	szEducation = @szEducation, 
	szCountry = @szCountry, 
	szJob = @szJob, 
	szSkills = @szSkills, 
	szFavLang = @szFavLang, 
	szMyComputer = @szMyComputer

WHERE iUserID = @userID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO