ÿþUSE [Mises] GO /****** Object: StoredProcedure [dbo].[DailyArticlesGetImageGallery] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DailyArticlesGetImageGallery] GO /****** Object: StoredProcedure [dbo].[DailyArticlesGetRecent] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DailyArticlesGetRecent] GO /****** Object: StoredProcedure [dbo].[DailyArticlesGetTodaysSummary] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DailyArticlesGetTodaysSummary] GO /****** Object: StoredProcedure [dbo].[DailyArticlesSummaryByDate] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DailyArticlesSummaryByDate] GO /****** Object: StoredProcedure [dbo].[DailyArticlesTopAuthors] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DailyArticlesTopAuthors] GO /****** Object: StoredProcedure [dbo].[DailyArticleUpdate] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DailyArticleUpdate] GO /****** Object: StoredProcedure [dbo].[DailyArticleUpdateArticleOrder] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DailyArticleUpdateArticleOrder] GO /****** Object: StoredProcedure [dbo].[DocumentAdminLinks] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentAdminLinks] GO /****** Object: StoredProcedure [dbo].[_FixHTML] Script Date: 09/24/2011 22:43:32 ******/ DROP PROCEDURE [dbo].[_FixHTML] GO /****** Object: StoredProcedure [dbo].[DailyArticlesDeleteByArticleId] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DailyArticlesDeleteByArticleId] GO /****** Object: StoredProcedure [dbo].[DocumentGetSubjects] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentGetSubjects] GO /****** Object: StoredProcedure [dbo].[_SearchDocumentText] Script Date: 09/24/2011 22:43:32 ******/ DROP PROCEDURE [dbo].[_SearchDocumentText] GO /****** Object: StoredProcedure [dbo].[ArticleGetDetails] Script Date: 09/24/2011 22:43:32 ******/ DROP PROCEDURE [dbo].[ArticleGetDetails] GO /****** Object: StoredProcedure [dbo].[DailyArticleAdd] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DailyArticleAdd] GO /****** Object: StoredProcedure [dbo].[DailyArticleGet] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DailyArticleGet] GO /****** Object: StoredProcedure [dbo].[DocumentSave] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentSave] GO /****** Object: StoredProcedure [dbo].[DocumentsGetBiographies] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentsGetBiographies] GO /****** Object: StoredProcedure [dbo].[DailyArticlesGetFeed] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DailyArticlesGetFeed] GO /****** Object: StoredProcedure [dbo].[DailyArticlesGetFullTextFeed] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DailyArticlesGetFullTextFeed] GO /****** Object: StoredProcedure [dbo].[DocumentAuthorsGetDetail] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentAuthorsGetDetail] GO /****** Object: StoredProcedure [dbo].[DocumentAuthorsGetList] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentAuthorsGetList] GO /****** Object: StoredProcedure [dbo].[DocumentDelete] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentDelete] GO /****** Object: StoredProcedure [dbo].[DocumentGetFeed] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentGetFeed] GO /****** Object: StoredProcedure [dbo].[DocumentGetKeywords] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentGetKeywords] GO /****** Object: StoredProcedure [dbo].[GetContentbyGUID] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[GetContentbyGUID] GO /****** Object: StoredProcedure [dbo].[GlobalGetStatistics] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[GlobalGetStatistics] GO /****** Object: StoredProcedure [dbo].[MediaGetCategoryContent] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetCategoryContent] GO /****** Object: StoredProcedure [dbo].[MediaTopFullSubjects] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaTopFullSubjects] GO /****** Object: StoredProcedure [dbo].[MediaTopSubjects] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaTopSubjects] GO /****** Object: StoredProcedure [dbo].[PageDelete] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[PageDelete] GO /****** Object: StoredProcedure [dbo].[QuizGetDetails] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[QuizGetDetails] GO /****** Object: StoredProcedure [dbo].[ReplaceDocumentText] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[ReplaceDocumentText] GO /****** Object: StoredProcedure [dbo].[ReplaceGuideAuthor] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[ReplaceGuideAuthor] GO /****** Object: StoredProcedure [dbo].[RevisionGetRecent] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[RevisionGetRecent] GO /****** Object: StoredProcedure [dbo].[SearchSite] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[SearchSite] GO /****** Object: StoredProcedure [dbo].[spAddNewArticle] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[spAddNewArticle] GO /****** Object: StoredProcedure [dbo].[spAddNewDocumentSubjectLink] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[spAddNewDocumentSubjectLink] GO /****** Object: StoredProcedure [dbo].[spGetAuthorList] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[spGetAuthorList] GO /****** Object: StoredProcedure [dbo].[spUpdateArticle] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[spUpdateArticle] GO /****** Object: StoredProcedure [dbo].[SubjectsTopSubjects] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[SubjectsTopSubjects] GO /****** Object: StoredProcedure [dbo].[TagGetDocumentsWithTag] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[TagGetDocumentsWithTag] GO /****** Object: StoredProcedure [dbo].[TagGetDocumentsWithTags] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[TagGetDocumentsWithTags] GO /****** Object: StoredProcedure [dbo].[TagGetRelatedDocuments] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[TagGetRelatedDocuments] GO /****** Object: StoredProcedure [dbo].[TagGetUserTaggedDocuments] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[TagGetUserTaggedDocuments] GO /****** Object: StoredProcedure [dbo].[TagGetUserTags] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[TagGetUserTags] GO /****** Object: StoredProcedure [dbo].[TagGetRelatedTags] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[TagGetRelatedTags] GO /****** Object: StoredProcedure [dbo].[TagGetStats] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[TagGetStats] GO /****** Object: StoredProcedure [dbo].[TagGetTopTags] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[TagGetTopTags] GO /****** Object: StoredProcedure [dbo].[TagGetDocumentTags] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[TagGetDocumentTags] GO /****** Object: StoredProcedure [dbo].[TagAddNew] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[TagAddNew] GO /****** Object: StoredProcedure [dbo].[TagDelete] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[TagDelete] GO /****** Object: StoredProcedure [dbo].[TagGetAnonymousTaggers] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[TagGetAnonymousTaggers] GO /****** Object: StoredProcedure [dbo].[RegistrationFormDetails] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[RegistrationFormDetails] GO /****** Object: StoredProcedure [dbo].[oldMediaGetDetails] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[oldMediaGetDetails] GO /****** Object: StoredProcedure [dbo].[MediaTypeGetList] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaTypeGetList] GO /****** Object: StoredProcedure [dbo].[MediaGetContributorList] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetContributorList] GO /****** Object: StoredProcedure [dbo].[MediaGetFeatured] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetFeatured] GO /****** Object: StoredProcedure [dbo].[MediaGetFeed] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetFeed] GO /****** Object: StoredProcedure [dbo].[MediaGetLatest] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetLatest] GO /****** Object: StoredProcedure [dbo].[MediaGetLatestAudio] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetLatestAudio] GO /****** Object: StoredProcedure [dbo].[MediaGetLatestVideo] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetLatestVideo] GO /****** Object: StoredProcedure [dbo].[MediaGetMediaByType] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetMediaByType] GO /****** Object: StoredProcedure [dbo].[MediaGetMisesLive] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetMisesLive] GO /****** Object: StoredProcedure [dbo].[MediaGetRandomVideo] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetRandomVideo] GO /****** Object: StoredProcedure [dbo].[MediaGetSubCategories] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetSubCategories] GO /****** Object: StoredProcedure [dbo].[MediaGetTopTagged] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetTopTagged] GO /****** Object: StoredProcedure [dbo].[MediaSearch] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaSearch] GO /****** Object: StoredProcedure [dbo].[MediaTopAuthors] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaTopAuthors] GO /****** Object: StoredProcedure [dbo].[MediaGetAllFeatured] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetAllFeatured] GO /****** Object: StoredProcedure [dbo].[MediaGetAuthorContents] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetAuthorContents] GO /****** Object: StoredProcedure [dbo].[MediaGetAuthors] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetAuthors] GO /****** Object: StoredProcedure [dbo].[MediaGetAuthorsAlpha] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaGetAuthorsAlpha] GO /****** Object: StoredProcedure [dbo].[DocumentTopAuthors] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentTopAuthors] GO /****** Object: StoredProcedure [dbo].[MediaAddMetadata] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaAddMetadata] GO /****** Object: StoredProcedure [dbo].[DocumentGetNewLiterature] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentGetNewLiterature] GO /****** Object: StoredProcedure [dbo].[DocumentGetSourceJournalList] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentGetSourceJournalList] GO /****** Object: StoredProcedure [dbo].[DocumentsGetImage] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentsGetImage] GO /****** Object: StoredProcedure [dbo].[DocumentDetails] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentDetails] GO /****** Object: StoredProcedure [dbo].[_JLStoDocuments] Script Date: 09/24/2011 22:43:32 ******/ DROP PROCEDURE [dbo].[_JLStoDocuments] GO /****** Object: StoredProcedure [dbo].[_MediaCategoryUpdateImages] Script Date: 09/24/2011 22:43:32 ******/ DROP PROCEDURE [dbo].[_MediaCategoryUpdateImages] GO /****** Object: StoredProcedure [dbo].[CalendarGetEventsList] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[CalendarGetEventsList] GO /****** Object: StoredProcedure [dbo].[CalendarGetUpcomingEvents] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[CalendarGetUpcomingEvents] GO /****** Object: StoredProcedure [dbo].[changePassword] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[changePassword] GO /****** Object: StoredProcedure [dbo].[_DeleteOldData] Script Date: 09/24/2011 22:43:32 ******/ DROP PROCEDURE [dbo].[_DeleteOldData] GO /****** Object: StoredProcedure [dbo].[AspNet_SqlCachePollingStoredProcedure] Script Date: 09/24/2011 22:43:32 ******/ DROP PROCEDURE [dbo].[AspNet_SqlCachePollingStoredProcedure] GO /****** Object: StoredProcedure [dbo].[AspNet_SqlCacheQueryRegisteredTablesStoredProcedure] Script Date: 09/24/2011 22:43:32 ******/ DROP PROCEDURE [dbo].[AspNet_SqlCacheQueryRegisteredTablesStoredProcedure] GO /****** Object: StoredProcedure [dbo].[AspNet_SqlCacheRegisterTableStoredProcedure] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[AspNet_SqlCacheRegisterTableStoredProcedure] GO /****** Object: StoredProcedure [dbo].[AspNet_SqlCacheUnRegisterTableStoredProcedure] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[AspNet_SqlCacheUnRegisterTableStoredProcedure] GO /****** Object: StoredProcedure [dbo].[AspNet_SqlCacheUpdateChangeIdStoredProcedure] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[AspNet_SqlCacheUpdateChangeIdStoredProcedure] GO /****** Object: StoredProcedure [dbo].[DocumentGetAustrianEconomicsSubjects] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentGetAustrianEconomicsSubjects] GO /****** Object: StoredProcedure [dbo].[DocumentSubjectsGetList] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentSubjectsGetList] GO /****** Object: StoredProcedure [dbo].[DocumentGetLibertarianStudiesSubjects] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentGetLibertarianStudiesSubjects] GO /****** Object: StoredProcedure [dbo].[CalendarGetEventDetail] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[CalendarGetEventDetail] GO /****** Object: StoredProcedure [dbo].[DocumentAuthorsAdd] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentAuthorsAdd] GO /****** Object: StoredProcedure [dbo].[DocumentMediaTypeGetList] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentMediaTypeGetList] GO /****** Object: StoredProcedure [dbo].[MediaDetails] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaDetails] GO /****** Object: StoredProcedure [dbo].[DonationGetList] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DonationGetList] GO /****** Object: StoredProcedure [dbo].[DonationGetReport] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DonationGetReport] GO /****** Object: StoredProcedure [dbo].[DonationGetTotal] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DonationGetTotal] GO /****** Object: StoredProcedure [dbo].[DonationInsert] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DonationInsert] GO /****** Object: StoredProcedure [dbo].[Exceptions_Log] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[Exceptions_Log] GO /****** Object: StoredProcedure [dbo].[ExceptionsGetReport] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[ExceptionsGetReport] GO /****** Object: StoredProcedure [dbo].[FavoriteAdd] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[FavoriteAdd] GO /****** Object: StoredProcedure [dbo].[FellowsGetBio] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[FellowsGetBio] GO /****** Object: StoredProcedure [dbo].[MediaCategoryGetAll] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaCategoryGetAll] GO /****** Object: StoredProcedure [dbo].[MediaCategoryGetParents] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MediaCategoryGetParents] GO /****** Object: StoredProcedure [dbo].[PageGetGUIDByPageId] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[PageGetGUIDByPageId] GO /****** Object: StoredProcedure [dbo].[PageGetIdByGUID] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[PageGetIdByGUID] GO /****** Object: StoredProcedure [dbo].[FavoritesGetUserFavorites] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[FavoritesGetUserFavorites] GO /****** Object: StoredProcedure [dbo].[PageAddNew] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[PageAddNew] GO /****** Object: StoredProcedure [dbo].[QuizGetList] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[QuizGetList] GO /****** Object: StoredProcedure [dbo].[QuizIncrementCount] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[QuizIncrementCount] GO /****** Object: StoredProcedure [dbo].[QuoteGetRandom] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[QuoteGetRandom] GO /****** Object: StoredProcedure [dbo].[QuotesGetSubjectList] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[QuotesGetSubjectList] GO /****** Object: StoredProcedure [dbo].[MisesReviewGetReview] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[MisesReviewGetReview] GO /****** Object: StoredProcedure [dbo].[PageContentGetDetail] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[PageContentGetDetail] GO /****** Object: StoredProcedure [dbo].[PagesGetGUID] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[PagesGetGUID] GO /****** Object: StoredProcedure [dbo].[PageUpdate] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[PageUpdate] GO /****** Object: StoredProcedure [dbo].[PeriodicalAddStory] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[PeriodicalAddStory] GO /****** Object: StoredProcedure [dbo].[PeriodicalsGetArchives] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[PeriodicalsGetArchives] GO /****** Object: StoredProcedure [dbo].[PeriodicalsGetDetails] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[PeriodicalsGetDetails] GO /****** Object: StoredProcedure [dbo].[PeriodicalUpdateStory] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[PeriodicalUpdateStory] GO /****** Object: UserDefinedFunction [dbo].[RegExReplace] Script Date: 09/24/2011 22:43:36 ******/ DROP FUNCTION [dbo].[RegExReplace] GO /****** Object: StoredProcedure [dbo].[RedirectedURLGet] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[RedirectedURLGet] GO /****** Object: StoredProcedure [dbo].[RevisionCreate] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[RevisionCreate] GO /****** Object: StoredProcedure [dbo].[RevisionGetByRevisionId] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[RevisionGetByRevisionId] GO /****** Object: StoredProcedure [dbo].[RevisionGetListByGUID] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[RevisionGetListByGUID] GO /****** Object: StoredProcedure [dbo].[spVerifyLogin] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[spVerifyLogin] GO /****** Object: StoredProcedure [dbo].[spGetBookReviews] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[spGetBookReviews] GO /****** Object: StoredProcedure [dbo].[spGetEventDetails] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[spGetEventDetails] GO /****** Object: StoredProcedure [dbo].[spGetQuotes] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[spGetQuotes] GO /****** Object: StoredProcedure [dbo].[spGetAllQuotes] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[spGetAllQuotes] GO /****** Object: StoredProcedure [dbo].[spAddBookReview] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[spAddBookReview] GO /****** Object: StoredProcedure [dbo].[WardLibraryGetBook] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[WardLibraryGetBook] GO /****** Object: StoredProcedure [dbo].[WardLibraryGetList] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[WardLibraryGetList] GO /****** Object: StoredProcedure [dbo].[spSearchPeople] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[spSearchPeople] GO /****** Object: UserDefinedFunction [dbo].[SplitCVS] Script Date: 09/24/2011 22:43:36 ******/ DROP FUNCTION [dbo].[SplitCVS] GO /****** Object: StoredProcedure [dbo].[StoreGetBestsellers] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[StoreGetBestsellers] GO /****** Object: StoredProcedure [dbo].[spUpdateHomepageItemOrder] Script Date: 09/24/2011 22:43:34 ******/ DROP PROCEDURE [dbo].[spUpdateHomepageItemOrder] GO /****** Object: StoredProcedure [dbo].[DocumentGetLinks] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DocumentGetLinks] GO /****** Object: StoredProcedure [dbo].[_ServerStatus] Script Date: 09/24/2011 22:43:32 ******/ DROP PROCEDURE [dbo].[_ServerStatus] GO /****** Object: StoredProcedure [dbo].[_Table_Sizes] Script Date: 09/24/2011 22:43:32 ******/ DROP PROCEDURE [dbo].[_Table_Sizes] GO /****** Object: StoredProcedure [dbo].[_Table_Sizes2] Script Date: 09/24/2011 22:43:32 ******/ DROP PROCEDURE [dbo].[_Table_Sizes2] GO /****** Object: StoredProcedure [dbo].[CountriesGetList] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[CountriesGetList] GO /****** Object: StoredProcedure [dbo].[_DatabaseStatus] Script Date: 09/24/2011 22:43:32 ******/ DROP PROCEDURE [dbo].[_DatabaseStatus] GO /****** Object: StoredProcedure [dbo].[DailyArticlesGetArchive] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[DailyArticlesGetArchive] GO /****** Object: StoredProcedure [dbo].[_SearchAllTables] Script Date: 09/24/2011 22:43:32 ******/ DROP PROCEDURE [dbo].[_SearchAllTables] GO /****** Object: StoredProcedure [dbo].[_SearchAndReplace] Script Date: 09/24/2011 22:43:32 ******/ DROP PROCEDURE [dbo].[_SearchAndReplace] GO /****** Object: StoredProcedure [dbo].[CommunityGetFeed] Script Date: 09/24/2011 22:43:33 ******/ DROP PROCEDURE [dbo].[CommunityGetFeed] GO /****** Object: StoredProcedure [dbo].[CommunityGetFeed] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[CommunityGetFeed] AS BEGIN SET NOCOUNT ON ; -- Forums SELECT TOP 10 --T.*, T.ThreadId , T.PostDate , T.PostAuthor , T.UserId , T.ThreadDate , ( SELECT TOP 1 subject FROM MisesCommunity..cs_Posts WHERE cs_Posts.ThreadID = T.ThreadID ) AS 'Subject' FROM MisesCommunity..cs_Threads T WITH ( NOLOCK ) --INNER JOIN MisesCommunity..cs_Posts P ON p.ThreadID = T.ThreadID WHERE ( SELECT TOP 1 ApplicationPostType FROM MisesCommunity..cs_Posts WITH ( NOLOCK ) WHERE cs_Posts.ThreadID = T.ThreadID ) <> 1 AND T.IsApproved = 1 AND T. sectionid NOT IN ( 1 , 2 , 9 , 81 , 82 , 83 , 84 , 86 , 87 , 88 , 89 , 90 , 91 , 92 , 93 , 94 , 310 ) ORDER BY T.ThreadDate DESC -- Blogs SELECT TOP 10 --* , PostID , PostAuthor AS author , Subject AS title , UserId , PostDate AS PubDate , PostName , '' AS link , UserTime , ApplicationKey , PostStatus FROM MisesCommunity..cs_Posts WITH ( NOLOCK ) JOIN MisesCommunity..cs_Sections WITH ( NOLOCK ) ON cs_Sections.SectionID = cs_Posts.SectionID WHERE cs_Posts.[ApplicationPostType] = 1 AND IsApproved = 1 AND cs_Sections.SectionID NOT IN ( 86 , 81 ) AND ApplicationType = 1 AND IsIndexed = 1 ORDER BY cs_Posts.PostDate DESC END GO /****** Object: StoredProcedure [dbo].[_SearchAndReplace] Script Date: 09/24/2011 22:43:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[_SearchAndReplace] ( @SearchStr varchar(100) , @ReplaceStr varchar(100) ) AS BEGIN -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string and replace it with another string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Tested on: SQL Server 7.0 and SQL Server 2000 -- Date modified: 2nd November 2002 13:50 GMT SET NOCOUNT ON DECLARE @TableName varchar(256) , @ColumnName varchar(128) , @SearchStr2 varchar(110) , @SQL varchar(MAX) , @RCTR int SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%' , '''') SET @RCTR = 0 WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME( TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME( TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY(OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME( TABLE_NAME)) , 'IsMSShipped') = 0 ) WHILE ( @TableName IS NOT NULL ) AND ( @ColumnName IS NOT NULL ) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName , 2) AND TABLE_NAME = PARSENAME( @TableName , 1) AND DATA_TYPE IN ( 'char' , 'varchar' , 'nchar' , 'nvarchar' ) AND QUOTENAME( COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN SET @SQL = 'UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = REPLACE(' + @ColumnName + ', ' + QUOTENAME(@SearchStr , '''') + ', ' + QUOTENAME( @ReplaceStr , '''') + ') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 EXEC ( @SQL ) SET @RCTR = @RCTR + @@ROWCOUNT END END END SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome' END GO /****** Object: StoredProcedure [dbo].[_SearchAllTables] Script Date: 09/24/2011 22:43:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[_SearchAllTables] ( @SearchStr nvarchar(100) ) AS BEGIN --To search all columns of all tables in Pubs database for the keyword "Computer" --EXEC SearchAllTables 'Computer' --GO -- --Here is the complete stored procedure code: -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Tested on: SQL Server 7.0 and SQL Server 2000 -- Date modified: 28th July 2002 22:50 GMT CREATE TABLE #Results ( ColumnName nvarchar(370) , ColumnValue nvarchar(3630) ) SET NOCOUNT ON DECLARE @TableName nvarchar(256) , @ColumnName nvarchar(128) , @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%' , '''') WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME( TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME( TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY(OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME( TABLE_NAME)) , 'IsMSShipped') = 0 ) WHILE ( @TableName IS NOT NULL ) AND ( @ColumnName IS NOT NULL ) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName , 2) AND TABLE_NAME = PARSENAME( @TableName , 1) AND DATA_TYPE IN ( 'char' , 'varchar' , 'nchar' , 'nvarchar' ) AND QUOTENAME( COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT '''+@TableName+'.'+@ColumnName+ ''', LEFT('+@ColumnName+', 3630) FROM ' +@TableName+' (NOLOCK) '+' WHERE '+@ColumnName +' LIKE '+@SearchStr2 ) END END END SELECT ColumnName , ColumnValue FROM #Results END GO /****** Object: StoredProcedure [dbo].[DailyArticlesGetArchive] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[DailyArticlesGetArchive] @ShowAll bit = 0 , @AuthorId int = 0 , @SearchQuery varchar(100) = '' , @Debug bit = 0 AS SET CONCAT_NULL_YIELDS_NULL OFF DECLARE @SQLSTATEMENT nvarchar(max) SET @SQLSTATEMENT = ' SELECT DailyArticles.ArticleId, DailyArticles.DisplayOrder, DailyArticles.title, da.authorLast, da.AuthorId, DailyArticles.CoAuthorId, DailyArticles.ShowArticle, DATEPART(month, DailyArticles.dateposted) AS MonthNum, DATENAME(month, DailyArticles.dateposted) AS Month, DailyArticles.DatePosted, DailyArticles.ShowArticle, da.authorFirst + '' '' + da.authorMiddle + '' '' + da.authorLast AS authorName, da2.authorFirst + '' '' + da2.authorMiddle + '' '' + da2.authorLast AS CoAuthorName, da.Photo, da2.Photo As CoPhoto FROM dbo.DailyArticles JOIN dbo.DocumentAuthors da ON da.AuthorId = DailyArticles.AuthorId LEFT JOIN dbo.DocumentAuthors da2 ON da2.AuthorId = DailyArticles.CoAuthorId WHERE 1=1' IF @ShowAll != 1 SET @SQLSTATEMENT = @SQLSTATEMENT + ' AND ( DailyArticles.ShowArticle = 1 ) ' IF @AuthorId > 0 SET @SQLSTATEMENT = @SQLSTATEMENT + 'AND ( DailyArticles.AuthorId = ' + CAST(@AuthorId AS varchar(7)) + ' OR DailyArticles.CoAuthorId = ' + CAST(@AuthorId AS varchar(7)) + ')' IF LEN(@SearchQuery) > 2 SET @SQLSTATEMENT = @SQLSTATEMENT + ' AND ( FREETEXT(ArticleText,@SearchQuery) OR description LIKE @SearchQuery OR title LIKE @SearchQuery )' --'AND ((title LIKE ' + CHAR(39) + '%' + @SearchQuery + '%' + CHAR(39) + ' )' + --'OR (DESCRIPTION LIKE ' + CHAR(39) + '%' + @SearchQuery + '%' + CHAR(39) + ' )' + --'OR (ArticleText LIKE ' + CHAR(39) + '%' + @SearchQuery + '%' + CHAR(39) + ' ))' DECLARE @PARAMS nvarchar(max) SET @PARAMS = N'@SearchQuery VARCHAR(100) ' SET @SQLSTATEMENT = @SQLSTATEMENT + ' ORDER BY DailyArticles.DatePosted DESC, CASE WHEN DailyArticles.Featured = 1 THEN Featured END, CASE WHEN DailyArticles.Headline = 1 THEN Headline END ' IF @debug = 0 -- EXEC ( @SQLSTATEMENT ) EXEC sp_executesql @SQLSTATEMENT , @PARAMS , @SearchQuery ELSE SELECT @sqlstatement --SELECT da.AuthorId, da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS 'authorName' --FROM dbo.DocumentAuthors da WHERE da.AuthorId = @AuthorId GO /****** Object: StoredProcedure [dbo].[_DatabaseStatus] Script Date: 09/24/2011 22:43:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[_DatabaseStatus] AS BEGIN SET NOCOUNT ON; --All utility procs in the Mises DB start with an underscore. --Here are the current ones: --_ServerStatus: The size of each database --_DatabaseStatus : Get a list of the longest-running queries --_DeleteOldData: Delete old logging data in tables when you need to shrink the db --_FixHTML: Does a bunch of search and replace commands to optimize the HTML in the database. Ongoing project. Time consuming! Only run at night! --_JLStoDocuments: Copies info from the jls table into the Documents structure --_MediaCategoryUpdateImages: Update Media category images --_SearchAndReplace: Be careful with this! Global search and replace. --_SearchDocumentText: use this before using searchandreplace --_Table_Sizes2: Get the dize of each table --_Table_Sizes: old version of this --exec sp_who2 select source_code,stats.total_elapsed_time/1000000 as seconds,last_execution_time from sys.dm_exec_query_stats as stats cross apply(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle))AS query_text order by total_elapsed_time desc END GO /****** Object: StoredProcedure [dbo].[CountriesGetList] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[CountriesGetList] AS BEGIN SET NOCOUNT ON ; SELECT Name , CountryCode , 1 AS Sort FROM AbleCommerce..ac_countries WITH ( NOLOCK ) WHERE CountryCode = 'US' UNION ALL SELECT Name , CountryCode , 2 AS Sort FROM AbleCommerce..ac_countries WITH ( NOLOCK ) ORDER BY SORT END GO /****** Object: StoredProcedure [dbo].[_Table_Sizes2] Script Date: 09/24/2011 22:43:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[_Table_Sizes2] @sortbyrows bit = 0 AS SET NOCOUNT ON -- http://www.databasejournal.com/features/mssql/article.php/3414111/Gathering-Space-Usage-Statistics.htm BEGIN try DECLARE @table_name VARCHAR(500) ; DECLARE @schema_name VARCHAR(500) ; DECLARE @tab1 TABLE( tablename VARCHAR (500) collate database_default , schemaname VARCHAR(500) collate database_default ); DECLARE @temp_table TABLE ( tablename sysname , row_count INT , reserved VARCHAR(50) collate database_default , data VARCHAR(50) collate database_default , index_size VARCHAR(50) collate database_default , unused VARCHAR(50) collate database_default ); INSERT INTO @tab1 SELECT t1.name , t2.name FROM sys.tables t1 INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id ); DECLARE c1 CURSOR FOR SELECT t2.name + '.' + t1.name FROM sys.tables t1 INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id ); OPEN c1; FETCH NEXT FROM c1 INTO @table_name; WHILE @@FETCH_STATUS = 0 BEGIN SET @table_name = REPLACE(@table_name, '[',''); SET @table_name = REPLACE(@table_name, ']',''); -- make sure the object exists before calling sp_spacedused IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name)) BEGIN INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ; END FETCH NEXT FROM c1 INTO @table_name; END; CLOSE c1; DEALLOCATE c1; SELECT t1.* , t2.schemaname FROM @temp_table t1 INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename ) ORDER BY row_count desc, schemaname,tablename; END try BEGIN catch SELECT -100 AS l1 , ERROR_NUMBER() AS tablename , ERROR_SEVERITY() AS row_count , ERROR_STATE() AS reserved , ERROR_MESSAGE() AS data , 1 AS index_size, 1 AS unused, 1 AS schemaname END catch GO /****** Object: StoredProcedure [dbo].[_Table_Sizes] Script Date: 09/24/2011 22:43:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[_Table_Sizes] @sortbyrows bit = 0 AS SET NOCOUNT ON --http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53843 --EXEC sp_space --show stats sorted by reserved space size --EXEC sp_space 1 --show stats sorted by row count SELECT CAST(OBJECT_NAME(id) AS varchar(50)) AS name , SUM(CASE WHEN indid < 2 THEN rows END) AS rows , SUM(reserved) * 8 AS reserved , SUM(dpages) * 8 AS data , SUM(used - dpages) * 8 AS index_size , SUM(reserved - used) * 8 AS unused FROM sysindexes WITH ( NOLOCK ) WHERE indid IN ( 0 , 1 , 255 ) AND id > 100 GROUP BY id WITH ROLLUP ORDER BY CASE WHEN @sortbyrows = 1 THEN SUM(CASE WHEN indid < 2 THEN rows END) ELSE SUM(reserved) * 8 END DESC GO /****** Object: StoredProcedure [dbo].[_ServerStatus] Script Date: 09/24/2011 22:43:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Name -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[_ServerStatus] AS BEGIN EXEC sp_helpdb END GO /****** Object: StoredProcedure [dbo].[DocumentGetLinks] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[DocumentGetLinks] @SubjectId int = 0 , @AuthorId int = 0 , @MediaType int = 0 , @Source varchar(500) = NULL , @SearchQuery varchar(500) = NULL , @MediaCategory varchar(10) = '' , @debug bit = 0 AS SET CONCAT_NULL_YIELDS_NULL OFF DECLARE @SQLSTATEMENT nvarchar(max) SET @SQLSTATEMENT = ' SELECT Documents.DocumentId , Documents.GUID , Title , PubInfo As Description , Source , FullText , DocumentAuthors.AuthorLast , DocumentAuthors.AuthorFirst + '' '' + DocumentAuthors.AuthorMiddle + '' '' + DocumentAuthors.AuthorLast As Author, DocumentAuthors2.AuthorFirst + '' '' + DocumentAuthors2.AuthorMiddle + '' '' + DocumentAuthors2.AuthorLast As CoAuthor, Author1 , Author2 , --MediaType , media.MediaTypeId , MediaIconPath = CASE MediaIconPath + '''' WHEN '''' THEN ''/images/Icons/html.png'' ELSE MediaIconPath END , media.CreateDate As DatePosted, --CAST(Documents.CreateDate As varchar(MAX)) As CreateDate , ''/resources/'' + CAST(Documents.DocumentId AS varchar(7)) AS ''URL'' FROM Documents WITH(NOLOCK) LEFT JOIN MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN DocumentMediaType ON DocumentMediaType.MediaTypeId = media.MediaTypeId LEFT JOIN DocumentAuthors ON DocumentAuthors.AuthorId = Documents.Author1 LEFT JOIN DocumentAuthors DocumentAuthors2 ON DocumentAuthors2.AuthorId = Documents.Author2 LEFT JOIN DocumentSubjectLink ON DocumentSubjectLink.DocumentId = Documents.DocumentId WHERE Documents.Display = 1 AND media.Display = 1 AND DocumentMediaType.IsMedia != 1 AND DocumentMediaType.MediaTypeId != 7 ' IF @SubjectId > 0 --SET @SQLSTATEMENT = @SQLSTATEMENT + 'AND Documents.DocumentId IN (select DocumentId from DocumentSubjectLink WHERE SubjectId = ' SET @SQLSTATEMENT = @SQLSTATEMENT + 'AND EXISTS (select * from DocumentSubjectLink WHERE DocumentSubjectLink.DocumentId = documents.DocumentId AND SubjectId = ' + CAST(@SubjectId AS varchar(7)) + ')' ELSE IF @AuthorId > 0 SET @SQLSTATEMENT = @SQLSTATEMENT + 'AND (Author1 = ' + CAST(@AuthorId AS varchar(7)) + ' OR Author2 = ' + CAST(@AuthorId AS varchar(7)) + ')' ELSE IF @MediaType > 0 SET @SQLSTATEMENT = @SQLSTATEMENT + 'AND DocumentMediaType.MediaTypeId = ' + CAST(@MediaType AS varchar(7)) ELSE IF LEN(@SOURCE) > 0 SET @SQLSTATEMENT = @SQLSTATEMENT + 'AND Source = (''' + CAST(@Source AS varchar(max)) + ''')' ELSE IF LEN(@SearchQuery) > 0 SET @SQLSTATEMENT = @SQLSTATEMENT + 'AND (Title LIKE ' + CHAR(39) + '%' + @SearchQuery + '%' + CHAR(39) + ' OR DocumentAuthors.AuthorFirst LIKE ' + CHAR(39) + '%' + @SearchQuery + '%' + CHAR(39) + ' OR DocumentAuthors.AuthorLast LIKE ' + CHAR(39) + '%' + @SearchQuery + '%' + CHAR(39) + ' OR metaDescription LIKE ' + CHAR(39) + '%' + @SearchQuery + '%' + CHAR(39) + ' OR metaKeywords LIKE ' + CHAR(39) + '%' + @SearchQuery + '%' + CHAR(39) + ' OR PubInfo LIKE ' + CHAR(39) + '%' + @SearchQuery + '%' + CHAR(39) + ')' IF @MediaCategory = 'Media' SET @SQLSTATEMENT = @SQLSTATEMENT + ' AND DocumentMediaType.MediaTypeId IN (1,2,8) ' ELSE IF @MediaCategory = 'Text' SET @SQLSTATEMENT = @SQLSTATEMENT + ' AND DocumentMediaType.IsMedia = 0 ' -- ********************************************************************************************************* -- Get Journal Entries SET @SQLSTATEMENT = @SQLSTATEMENT + ' UNION SELECT 0 As DocumentId , GUID , Title , Volume AS Description, Journal AS Source , 0 As FullText , AuthorLast , AuthorName AS Author , '''' As CoAuthor , AuthorId AS Author1 , 0 AS Author2 , --MediaType , DocumentMediaType.MediaTypeId , MediaIconPath = CASE MediaIconPath + '''' WHEN '''' THEN ''/images/Icons/html.png'' ELSE MediaIconPath END , DatePosted, URL FROM [PeriodicalsView] INNER JOIN DocumentMediaType ON DocumentMediaType.MediaTypeId = PeriodicalsView.MediaTypeId WHERE 1 =1 ' IF @SubjectId > 0 SET @SQLSTATEMENT = @SQLSTATEMENT + 'AND EXISTS (select * from DocumentSubjectLink WHERE PeriodicalsView.GUID = DocumentSubjectLink.GUID AND SubjectId = ' + CAST(@SubjectId AS varchar(7)) + ')' ELSE IF @AuthorId > 0 SET @SQLSTATEMENT = @SQLSTATEMENT + 'AND (PeriodicalsView.AuthorId = ' + CAST( @AuthorId AS varchar(7)) + ')' ELSE IF @MediaType > 0 SET @SQLSTATEMENT = @SQLSTATEMENT + 'AND DocumentMediaType.MediaTypeId = ' + CAST(@MediaType AS varchar(7)) ELSE IF LEN(@SOURCE) > 0 SET @SQLSTATEMENT = @SQLSTATEMENT + 'AND Journal = (''' + CAST(@Source AS varchar(max)) + ''')' ELSE IF LEN(@SearchQuery) > 0 SET @SQLSTATEMENT = @SQLSTATEMENT + 'AND (Title LIKE ' + CHAR(39) + '%' + @SearchQuery + '%' + CHAR(39) + ' OR AuthorName LIKE ' + CHAR(39) + '%' + @SearchQuery + '%' + CHAR(39) + ')' -- ***************** ORDERING SET @SQLSTATEMENT = @SQLSTATEMENT + ' ORDER BY ' IF @SubjectId > 0 OR @AuthorId > 0 --OR LEN(@Source) > 0 SET @SQLSTATEMENT = @SQLSTATEMENT + ' Documents.Title, ' IF @MediaCategory = 'title' SET @SQLSTATEMENT = @SQLSTATEMENT + ' Documents.Title, ' --IF LEN(@Source) > 0 -- SET @SQLSTATEMENT = @SQLSTATEMENT + ' Documents.-, ' SET @SQLSTATEMENT = @SQLSTATEMENT + ' DatePosted DESC ' -- ******************************************************************* -- Get Other Tables IF @SubjectId > 0 BEGIN SET @SQLSTATEMENT = @SQLSTATEMENT + ';SELECT Subject,Photo FROM DocumentSubjects WHERE SubjectId = ' + CAST( @SubjectId AS varchar(7)) END IF @AuthorId > 0 SET @SQLSTATEMENT = @SQLSTATEMENT + ';SELECT AuthorFirst + '' '' + AuthorLast As Author, Photo FROM DocumentAuthors WHERE DocumentAuthors.AuthorId = ' + CAST(@AuthorId AS varchar(7)) --SELECT @SQLSTATEMENT IF @debug = 0 EXEC sp_executesql @SQLSTATEMENT ELSE SELECT @sqlstatement GO /****** Object: StoredProcedure [dbo].[spUpdateHomepageItemOrder] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spUpdateHomepageItemOrder] ( @Itemid int , @DisplayOrder int ) AS UPDATE HomepageItems SET Itemorder = @DisplayOrder WHERE ItemId = @ItemId GO /****** Object: StoredProcedure [dbo].[StoreGetBestsellers] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[StoreGetBestsellers] @MaxItems INT = 5 AS BEGIN SET ROWCOUNT @MaxItems SELECT ac_PRODUCTS.ProductId, ac_PRODUCTS.Name, --CAST(ac_PRODUCTS.Description1 AS VARCHAR(MAX)) + ' ' + CAST(ac_PRODUCTS.Description AS VARCHAR(500)) + '...' AS DESCRIPTION, ac_Manufacturers.Name AS Author, ac_Manufacturers.ManufacturerId AS ManufacturerId, ac_PRODUCTS.Sku AS GUID, 'product.aspx?ProductId=' + CAST(ac_PRODUCTS.ProductId AS VARCHAR(MAX)) AS DisplayPage, ac_Products.CreatedDate, ac_Products.Price, ac_Products.ThumbnailUrl, SUM(ac_ORDERITEMS.Quantity) AS TotalQuantity, SUM(ac_ORDERITEMS.Price * ac_ORDERITEMS.Quantity) AS TotalSales FROM (AbleCommerce..ac_ORDERS INNER JOIN AbleCommerce..ac_ORDERITEMS ON ac_ORDERITEMS.OrderId = ac_ORDERS.OrderId) INNER JOIN AbleCommerce..ac_PRODUCTS ON ac_ORDERITEMS.ProductId = ac_PRODUCTS.ProductId INNER JOIN AbleCommerce..ac_Manufacturers ON ac_Manufacturers.ManufacturerId = ac_Products.ManufacturerId WHERE ac_PRODUCTS.DisablePurchase = 0 AND ac_PRODUCTS.VisibilityId = 0 --AND ac_ORDERS.StoreId = 3 AND OrderDate > GETDATE() - 15 AND ac_ORDERS.OrderDate IS NOT NULL AND ac_ORDERITEMS.ProductId > 0 AND ac_Products.Price > 1 AND ac_Products.ProductId <> 157 GROUP BY ac_PRODUCTS.ProductId, ac_PRODUCTS.Name, ac_PRODUCTS.DisplayPage, ac_PRODUCTS.Description, ac_PRODUCTS.Sku, ac_PRODUCTS.ThumbnailUrl, ac_PRODUCTS.Price, ac_Manufacturers.Name, ac_Products.CreatedDate, ac_Manufacturers.ManufacturerId ORDER BY SUM(ac_ORDERITEMS.Quantity) DESC -- SUM(ac_ORDERITEMS.Price * ac_ORDERITEMS.Quantity) DESC END GO /****** Object: UserDefinedFunction [dbo].[SplitCVS] Script Date: 09/24/2011 22:43:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[SplitCVS] ( @OrderList VARCHAR(MAX) ) RETURNS @ParsedList TABLE ( OrderID VARCHAR(max) ) AS BEGIN DECLARE @OrderID VARCHAR(max), @Pos VARCHAR(max) SET @OrderList = LTRIM(RTRIM(@OrderList)) + ',' SET @Pos = CHARINDEX(',', @OrderList, 1) IF REPLACE(@OrderList, ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1))) IF @OrderID <> '' BEGIN INSERT INTO @ParsedList ( OrderId ) VALUES ( CAST(@OrderID AS VARCHAR(max)) ) --Use Appropriate conversion END SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos) SET @Pos = CHARINDEX(',', @OrderList, 1) END END RETURN END GO /****** Object: StoredProcedure [dbo].[spSearchPeople] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spSearchPeople] @SearchTerm varchar(200) = NULL AS SET CONCAT_NULL_YIELDS_NULL OFF IF @SearchTerm IS NULL SELECT Personid , FirstName + ' ' + MiddleName + ' ' + LastName AS 'Name' , School , Status , Email , WebSite , BlogURL , SUBSTRING(WebSite , 8 , 13) + '..' AS WebSiteTrim , SUBSTRING(BlogURL , 8 , 13) + '..' AS BlogURLTrim FROM PEOPLE ORDER BY LastName ELSE SELECT Personid , FirstName + ' ' + MiddleName + ' ' + LastName AS 'Name' , School , Status , Email , WebSite , BlogURL , SUBSTRING(WebSite , 8 , 13) + '..' AS WebSiteTrim , SUBSTRING(BlogURL , 8 , 13) + '..' AS BlogURLTrim FROM PEOPLE WHERE FirstName LIKE '%' + @SearchTerm + '%' OR LastName LIKE '%' + @SearchTerm + '%' OR School LIKE '%' + @SearchTerm + '%' OR Status LIKE '%' + @SearchTerm + '%' OR WebSite LIKE '%' + @SearchTerm + '%' OR Specialization LIKE '%' + @SearchTerm + '%' OR FavoriteBooks LIKE '%' + @SearchTerm + '%' OR FavoriteThinkers LIKE '%' + @SearchTerm + '%' OR LastName LIKE '%' + @SearchTerm + '%' OR Education LIKE '%' + @SearchTerm + '%' OR AdditionalInfo LIKE '%' + @SearchTerm + '%' OR BlogURL LIKE '%' + @SearchTerm + '%' OR SlashdotNick LIKE '%' + @SearchTerm + '%' OR DiggNick LIKE '%' + @SearchTerm + '%' ORDER BY LastName GO /****** Object: StoredProcedure [dbo].[WardLibraryGetList] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: David -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[WardLibraryGetList] @authorFirst varchar(max) , @authorLast varchar(max) , @title varchar(max) , @subject varchar(max) AS BEGIN SELECT control , display , title , checked_out_by , authorfirst , authorlast , authorfirst2 , authorlast2 , authorfirst3 , authorlast3 , coauthors , subject FROM wardlibrary END GO /****** Object: StoredProcedure [dbo].[WardLibraryGetBook] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: David V -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[WardLibraryGetBook] @BookId int = 0 AS BEGIN SELECT * , AuthorFirst + ' ' + AuthorLast + ' ' + coauthors AS AuthorName FROM wardlibrary WHERE CONTROL = @BookId END GO /****** Object: StoredProcedure [dbo].[spAddBookReview] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spAddBookReview] ( @BookId int , @Rating tinyint , @Title varchar(255) , @Review varchar(8000) , @ScreenName varchar(255) , @Location varchar(255) ) AS INSERT INTO BookReviews ( BookId , Rating , Title , Review , ScreenName , Location , ReviewDate ) VALUES ( @BookId , @Rating , @Title , @Review , @ScreenName , @Location , GETDATE() ) SELECT SCOPE_IDENTITY() GO /****** Object: StoredProcedure [dbo].[spGetAllQuotes] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spGetAllQuotes] @Subject varchar(50) = NULL , @AuthorId int = 0 , @Source varchar(30) = NULL , @SearchQuery varchar(50) = NULL AS IF LEN(@Subject) > 0 SELECT AuthorFirst + ' ' + AuthorLast AS [Author] , [Quote] , [Source] , [Page] , [Subject] FROM Quotes JOIN DocumentAuthors ON Quotes.AuthorId = DocumentAuthors.AuthorId WHERE Subject LIKE '%' + @Subject + '%' ELSE IF @AuthorId > 0 SELECT AuthorFirst + ' ' + AuthorLast AS [Author] , [Quote] , [Source] , [Page] , [Subject] FROM Quotes JOIN DocumentAuthors ON Quotes.AuthorId = DocumentAuthors.AuthorId WHERE Quotes.AuthorId = @AuthorId ELSE IF LEN(@SOURCE) > 0 SELECT AuthorFirst + ' ' + AuthorLast AS [Author] , [Quote] , [Source] , [Page] , [Subject] FROM Quotes JOIN DocumentAuthors ON Quotes.AuthorId = DocumentAuthors.AuthorId WHERE Source LIKE '%' + @Source + '%' ELSE IF LEN(@SearchQuery) > 0 SELECT AuthorFirst + ' ' + AuthorLast AS [Author] , [Quote] , [Source] , [Page] , [Subject] FROM Quotes JOIN DocumentAuthors ON Quotes.AuthorId = DocumentAuthors.AuthorId WHERE Author LIKE '%' + @SearchQuery + '%' OR Quote LIKE '%' + @SearchQuery + '%' ELSE SELECT AuthorFirst + ' ' + AuthorLast AS [Author] , [Quote] , [Source] , [Page] , [Subject] FROM Quotes JOIN DocumentAuthors ON Quotes.AuthorId = DocumentAuthors.AuthorId GO /****** Object: StoredProcedure [dbo].[spGetQuotes] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[spGetQuotes] @Subject varchar(50) = NULL , @AuthorId int = 0 , @Source varchar(30) = NULL , @SearchQuery varchar(50) = NULL AS IF LEN(@Subject) > 0 SELECT AuthorFirst + ' ' + AuthorLast AS [Author] , [Quote] , [Source] , [Page] , [Subject] FROM [Quotes] JOIN DocumentAuthors ON Quotes.AuthorId = DocumentAuthors.AuthorId WHERE Subject LIKE '%' + @Subject + '%' ELSE IF @AuthorId > 0 SELECT AuthorFirst + ' ' + AuthorLast AS [Author] , [Quote] , [Source] , [Page] , [Subject] FROM [Quotes] JOIN DocumentAuthors ON Quotes.AuthorId = DocumentAuthors.AuthorId WHERE Quotes.AuthorId = @AuthorId ELSE IF LEN(@SOURCE) > 0 SELECT AuthorFirst + ' ' + AuthorLast AS [Author] , [Quote] , [Source] , [Page] , [Subject] FROM [Quotes] JOIN DocumentAuthors ON Quotes.AuthorId = DocumentAuthors.AuthorId WHERE Source LIKE '%' + @Source + '%' ELSE IF LEN(@SearchQuery) > 0 SELECT AuthorFirst + ' ' + AuthorLast AS [Author] , [Quote] , [Source] , [Page] , [Subject] FROM [Quotes] JOIN DocumentAuthors ON Quotes.AuthorId = DocumentAuthors.AuthorId WHERE Author LIKE '%' + @SearchQuery + '%' OR Quote LIKE '%' + @SearchQuery + '%' ELSE SELECT AuthorFirst + ' ' + AuthorLast AS [Author] , [Quote] , [Source] , [Page] , [Subject] FROM [Quotes] JOIN DocumentAuthors ON Quotes.AuthorId = DocumentAuthors.AuthorId GO /****** Object: StoredProcedure [dbo].[spGetEventDetails] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spGetEventDetails] @EventId int AS SELECT Calendar.EventId , Calendar.GUID , Calendar.Title , Calendar.EventDate , Calendar.EndDate , Calendar.IntroText , Calendar.Description , Calendar.Location , Calendar.Display , Calendar.CreateDate FROM Calendar WHERE EventId = @EventId GO /****** Object: StoredProcedure [dbo].[spGetBookReviews] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spGetBookReviews] @BookId int AS SELECT BookReviews.ReviewId , BookReviews.BookId , BookReviews.Rating , BookReviews.Title , BookReviews.Review , BookReviews.ScreenName , BookReviews.Location , BookReviews.ReviewDate FROM BookReviews WHERE BookId = @BookId ORDER BY ReviewId GO /****** Object: StoredProcedure [dbo].[spVerifyLogin] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spVerifyLogin] @Username varchar(50) , @Password varchar(50) AS IF ( SELECT perms FROM ManagerUsers WHERE username = @Username AND password = @Password AND active = 1 ) = 'Admin' SELECT 2 FROM ManagerUsers WHERE username = @Username AND password = @Password AND active = 1 ELSE SELECT COUNT(1) FROM ManagerUsers WHERE username = @Username AND password = @Password AND active = 1 GO /****** Object: StoredProcedure [dbo].[RevisionGetListByGUID] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[RevisionGetListByGUID] @DocumentGUID uniqueidentifier AS BEGIN SET NOCOUNT ON ; SELECT RevisionId , DocumentGUID , LEN(DocumentText) AS Size , Editor , EditDate FROM dbo.Revision WHERE DocumentGUID = @DocumentGUID END GO /****** Object: StoredProcedure [dbo].[RevisionGetByRevisionId] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[RevisionGetByRevisionId] @RevisionId int AS BEGIN SET NOCOUNT ON ; SELECT RevisionId , DocumentGUID , DocumentText , Editor , EditDate FROM dbo.Revision WHERE RevisionId = @RevisionId END GO /****** Object: StoredProcedure [dbo].[RevisionCreate] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[RevisionCreate] @RevisionId int OUTPUT , @DocumentGUID uniqueidentifier , @DocumentText varchar(max) , @Editor varchar(50) AS BEGIN IF EXISTS ( SELECT DocumentText FROM dbo.Revision WHERE DocumentGUID = @DocumentGUID AND DocumentText = @DocumentText ) RETURN INSERT INTO dbo.Revision ( DocumentGUID , DocumentText , Editor , EditDate ) VALUES ( @DocumentGUID , @DocumentText , @Editor , GETDATE() ) SET @RevisionId = SCOPE_IDENTITY() END GO /****** Object: StoredProcedure [dbo].[RedirectedURLGet] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: David V -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[RedirectedURLGet] @RequestedURL varchar(150) AS BEGIN DECLARE @URL varchar(150) --IF LEN(@RequestedURL) < 5 RETURN SET @URL = ( SELECT TOP 1 [RedirectURL] FROM [RedirectedURL] WHERE ExactMatchOnly = 1 AND [RequestedURL] = @RequestedURL ORDER BY Priority ) IF @URL IS NULL BEGIN SET @URL = ( SELECT TOP 1 [RedirectURL] FROM [RedirectedURL] WHERE ExactMatchOnly = 0 AND @RequestedURL LIKE '%' + RequestedURL + '%' ORDER BY Priority ) END SELECT @URL END GO /****** Object: UserDefinedFunction [dbo].[RegExReplace] Script Date: 09/24/2011 22:43:36 ******/ CREATE FUNCTION [dbo].[RegExReplace](@expression [nvarchar](max), @pattern [nvarchar](max), @replace [nvarchar](max)) RETURNS [nvarchar](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [regexReplace].[UserDefinedFunctions].[RegExReplace] GO EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'RegExReplace' GO EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegExFunction.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'RegExReplace' GO EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'RegExReplace' GO /****** Object: StoredProcedure [dbo].[PeriodicalUpdateStory] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[PeriodicalUpdateStory] @PeriodicalId int , @StoryId int , @EditedBy varchar(50) , @authorfirst varchar(50) , @authorlast varchar(50) , @authorfirst2 varchar(50) , @authorlast2 varchar(50) , @authorfirst3 varchar(50) , @authorlast3 varchar(50) , @title varchar(255) , @body varchar(max) , @issue_season int , @issue_year int AS BEGIN UPDATE misesreview SET authorfirst = @authorfirst , authorlast = @authorlast , authorfirst2 = @authorfirst2 , authorlast2 = @authorlast2 , authorfirst3 = @authorfirst3 , authorlast3 = @authorlast3 , title = @title , body = @body , issue_season = @issue_season , issue_year = @issue_year WHERE dbo.misesreview.[control] = @StoryId END GO /****** Object: StoredProcedure [dbo].[PeriodicalsGetDetails] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[PeriodicalsGetDetails] -- Add the parameters for the stored procedure here @PeriodicalId int = 0 AS BEGIN SELECT PeriodicalId , Title , Description , Logo , ShortDescription , RedirectURL , PublicationFrequency , metaDescription , metaKeywords , CreateDate FROM Periodicals WHERE ( PeriodicalId = @PeriodicalId ) END GO /****** Object: StoredProcedure [dbo].[PeriodicalsGetArchives] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[PeriodicalsGetArchives] @PeriodicalId int , @title varchar(50) = 'RandomString' , @author varchar(50) = 'RandomString' , @subject varchar(50) = 'RandomString' , @volume varchar(50) = 'RandomString' AS BEGIN IF @title = 'RandomString' AND @author = 'RandomString' AND @subject = 'RandomString' AND @volume = 'RandomString' BEGIN IF @PeriodicalId = 1 SELECT @PeriodicalId AS PeriodicalId , freemarket.title , freemarket.AuthorFirst + ' ' + freemarket.AuthorLast AS AuthorName , freemarket.AuthorLast , freemarket.articledate AS DatePosted , DATENAME(month , freemarket.articledate) AS Month , '/freemarket_detail.aspx?control=' + CAST(freemarket.control AS varchar(10)) AS URL , freemarket.[control] AS Id FROM dbo.freemarket ORDER BY freemarket.articledate DESC -- Season, Volume IF @PeriodicalId = 2 SELECT @PeriodicalId AS PeriodicalId , misesreview.AuthorFirst + ' ' + misesreview.AuthorLast AS AuthorName , misesreview.AuthorLast , misesreview.title , CASE misesreview.issue_season WHEN 1 THEN 'Spring' WHEN 2 THEN 'Summer' WHEN 3 THEN 'Fall' WHEN 4 THEN 'Winter' END + ' ' + CAST(misesreview.issue_year AS varchar(10)) AS Volume , misesreview.issue_year AS Season , '/misesreview_detail.aspx?control=' + CAST(misesreview.control AS varchar(10)) AS URL , misesreview.[control] AS Id FROM dbo.misesreview ORDER BY misesreview.CreateDate DESC , PeriodicalId DESC IF @PeriodicalId = 3 SELECT jls.[control] AS Id, @PeriodicalId AS PeriodicalId , jls.number , jls.display , jls.title , jls.authorFirst1 + ' ' + jls.authorLast1 AS AuthorName , jls.authorLast1 AS AuthorLast , 'Vol. ' + CAST(jls.Volume AS varchar(10)) + ' Num. ' + CAST(jls. [number] AS varchar(10)) AS Volume , 'Volume ' + CAST(jls.Volume AS varchar(10)) AS Season , '/journals/jls/' + CAST(jls.Volume AS varchar(10)) + '_' + CAST(jls .number AS varchar(10)) + '/' + CAST(jls.Volume AS varchar(10)) + '_' + CAST(jls.number AS varchar(10)) + '_' + CAST(jls.articleNum AS varchar(10)) + '.pdf' AS URL FROM dbo.jls ORDER BY jls.Volume DESC , number DESC , jls.ArticleNum IF @PeriodicalId = 4 SELECT @PeriodicalId AS PeriodicalId , qjaeDB.number , qjaeDB.display , qjaeDB.title , qjaeDB.authorFirst1 + ' ' + qjaeDB.authorLast1 AS AuthorName , qjaeDB.authorLast1 AS AuthorLast , 'Vol. ' + CAST(qjaeDB.Volume AS varchar(10)) + ' Num. ' + CAST( qjaeDB.[number] AS varchar(10)) AS Volume , 'Volume ' + CAST(qjaeDB.Volume AS varchar(10)) AS Season , '/journals/qjae/pdf/qjae' + CAST(qjaeDB.Volume AS varchar(10)) + '_' + CAST(qjaeDB.number AS varchar(10)) + '_' + CAST(qjaeDB. articleNum AS varchar(10)) + '.pdf' AS URL, [control] AS Id FROM dbo.qjaeDB ORDER BY [qjaeDB].Volume DESC , [qjaeDB].number DESC , ArticleNum IF @PeriodicalId = 5 SELECT raeDB.[control] as Id, @PeriodicalId AS PeriodicalId , raeDB.number , raeDB.display , raeDB.title , raeDB.authorFirst1 + ' ' + raeDB.authorLast1 AS AuthorName , raeDB.authorLast1 AS AuthorLast , 'Vol. ' + CAST(raeDB.Volume AS varchar(10)) + ' Num. ' + CAST(raeDB .[number] AS varchar(10)) AS Volume , 'Volume ' + CAST(raeDB.Volume AS varchar(10)) AS Season , '/journals/rae/pdf/RAE' + CAST(raeDB.Volume AS varchar(10)) + '_' + CAST(raeDB.number AS varchar(10)) + '_' + CAST(raeDB.articleNum AS varchar(10)) + '.pdf' AS URL FROM dbo.raeDB ORDER BY dbo.raeDB.[Volume] DESC , --number DESC , ArticleNum IF @PeriodicalId = 6 SELECT aendb.[control] as Id, @PeriodicalId AS PeriodicalId , aenDB.number , aenDB.display , aenDB.title , aenDB.authorFirst1 + ' ' + aenDB.authorLast1 AS AuthorName , aenDB.authorLast1 AS AuthorLast , 'Vol. ' + CAST(aenDB.Volume AS varchar(10)) + ' Num. ' + CAST(aenDB .[number] AS varchar(10)) AS Volume , 'Volume ' + CAST(aenDB.Volume AS varchar(10)) AS Season , '/journals/aen/aen' + CAST(aenDB.Volume AS varchar(10)) + '_' + CAST(aenDB.number AS varchar(10)) + '_' + CAST(aenDB.articleNum AS varchar(10)) + aenDB.[fileType] AS URL FROM dbo.aenDB ORDER BY aenDB.Volume DESC , number DESC , ArticleNum IF @PeriodicalId = 7 SELECT scholar.[control] As Id, @PeriodicalId AS PeriodicalId , [paper_name] + '(' + notation + ')' AS 'Title' , [author_name] AS AuthorName , [author_name] AS AuthorLast , [whenit] AS 'DatePosted' , '' AS 'Month' , '/journals/scholar/' + [file_name] AS [url] FROM scholar ORDER BY control DESC IF @PeriodicalId = 8 SELECT WardLibrary.[control] as Id, @PeriodicalId AS PeriodicalId , title AS 'Title' , AuthorFirst + ' ' + AuthorLast + ' ' + coauthors AS AuthorName , [AuthorLast] AS AuthorLast , donatedDate AS 'DatePosted' , '' AS 'Season' , '' AS 'Volume' , 'book.aspx?Id=' + CAST(CONTROL AS varchar(10)) AS [url] FROM WardLibrary --WHERE --display = 'yes' ORDER BY control DESC END ELSE -- DO SEARCH BEGIN IF @title = '' SET @title = 'RandomString' IF @author = '' SET @author = 'RandomString' IF @subject = '' SET @subject = 'RandomString' IF @volume = '' SET @volume = 'RandomString' IF @PeriodicalId = 1 SELECT @PeriodicalId AS PeriodicalId , freemarket.title , freemarket.AuthorFirst + ' ' + freemarket.AuthorLast AS AuthorName , freemarket.AuthorLast , freemarket.articledate AS DatePosted , DATENAME(month , freemarket.articledate) AS Month , '/freemarket_detail.aspx?control=' + CAST(freemarket.control AS varchar(10)) AS URL FROM dbo.freemarket WHERE freemarket.title LIKE '%' + @title + '%' OR freemarket.body LIKE '%' + @title + '%' OR freemarket.body LIKE '%' + @subject + '%' OR freemarket.AuthorLast LIKE '%' + @author + '%' ORDER BY freemarket.articledate DESC -- Season, Volume IF @PeriodicalId = 2 SELECT @PeriodicalId AS PeriodicalId , misesreview.AuthorFirst + ' ' + misesreview.AuthorLast AS AuthorName , misesreview.AuthorLast , misesreview.title , CASE misesreview.issue_season WHEN 1 THEN 'Spring' WHEN 2 THEN 'Summer' WHEN 3 THEN 'Fall' WHEN 4 THEN 'Winter' END + ' ' + CAST(misesreview.issue_year AS varchar(10)) AS Volume , misesreview.issue_year AS Season , '/misesreview_detail.aspx?control=' + CAST(misesreview.control AS varchar(10)) AS URL FROM dbo.misesreview WHERE misesreview.title LIKE '%' + @title + '%' OR misesreview.body LIKE '%' + @title + '%' OR misesreview.AuthorLast LIKE '%' + @author + '%' OR misesreview.[authorfirst] LIKE '%' + @author + '%' OR misesreview.[authorfirst] + ' ' + +misesreview.[authorlast] LIKE '%' + @author + '%' OR misesreview.body LIKE '%' + @title + '%' OR CASE misesreview . issue_season WHEN 1 THEN 'Spring' WHEN 2 THEN 'Summer' WHEN 3 THEN 'Fall' WHEN 4 THEN 'Winter' END + ' ' + CAST(misesreview.issue_year AS varchar(10)) LIKE '%' + @volume + '%' ORDER BY misesreview.CreateDate DESC , PeriodicalId DESC IF @PeriodicalId = 3 SELECT @PeriodicalId AS PeriodicalId , jls.number , jls.display , jls.title , jls.authorFirst1 + ' ' + jls.authorLast1 AS AuthorName , jls.authorLast1 AS AuthorLast , 'Vol. ' + CAST(jls.Volume AS varchar(10)) + ' Num. ' + CAST(jls. [number] AS varchar(10)) AS Volume , 'Volume ' + CAST(jls.Volume AS varchar(10)) AS Season , '/journals/jls/' + CAST(jls.Volume AS varchar(10)) + '_' + CAST(jls .number AS varchar(10)) + '/' + CAST(jls.Volume AS varchar(10)) + '_' + CAST(jls.number AS varchar(10)) + '_' + CAST(jls.articleNum AS varchar(10)) + '.pdf' AS URL FROM dbo.jls WHERE jls.title LIKE '%' + @title + '%' OR jls.AuthorLast1 LIKE '%' + @author + '%' OR jls.AuthorLast2 LIKE '%' + @author + '%' OR jls. authorFirst1 LIKE '%' + @author + '%' OR jls.authorFirst2 LIKE '%' + @author + '%' OR 'Vol. ' + CAST(jls.Volume AS varchar(10)) + ' Num. ' + CAST(jls.[number] AS varchar(10)) LIKE '%' + @volume + '%' ORDER BY jls.Volume DESC , number DESC , jls.ArticleNum IF @PeriodicalId = 4 SELECT @PeriodicalId AS PeriodicalId , qjaeDB.number , qjaeDB.display , qjaeDB.title , qjaeDB.authorFirst1 + ' ' + qjaeDB.authorLast1 AS AuthorName , qjaeDB.authorLast1 AS AuthorLast , 'Vol. ' + CAST(qjaeDB.Volume AS varchar(10)) + ' Num. ' + CAST( qjaeDB.[number] AS varchar(10)) AS Volume , 'Volume ' + CAST(qjaeDB.Volume AS varchar(10)) AS Season , '/journals/qjae/pdf/qjae' + CAST(qjaeDB.Volume AS varchar(10)) + '_' + CAST(qjaeDB.number AS varchar(10)) + '_' + CAST(qjaeDB. articleNum AS varchar(10)) + '.pdf' AS URL FROM dbo.qjaeDB WHERE qjaeDB.title LIKE '%' + @title + '%' OR qjaeDB.AuthorLast1 LIKE '%' + @author + '%' OR qjaeDB.AuthorLast2 LIKE '%' + @author + '%' OR qjaeDB.authorFirst1 LIKE '%' + @author + '%' OR qjaeDB.authorFirst2 LIKE '%' + @author + '%' OR 'Vol. ' + CAST(qjaeDB.Volume AS varchar (10)) + ' Num. ' + CAST(qjaeDB.[number] AS varchar(10)) LIKE '%' + @volume + '%' ORDER BY Volume DESC , number DESC , ArticleNum IF @PeriodicalId = 5 SELECT @PeriodicalId AS PeriodicalId , raeDB.number , raeDB.display , raeDB.title , raeDB.authorFirst1 + ' ' + raeDB.authorLast1 AS AuthorName , raeDB.authorLast1 AS AuthorLast , 'Vol. ' + CAST(raeDB.Volume AS varchar(10)) + ' Num. ' + CAST(raeDB .[number] AS varchar(10)) AS Volume , 'Volume ' + CAST(raeDB.Volume AS varchar(10)) AS Season , '/journals/rae/pdf/RAE' + CAST(raeDB.Volume AS varchar(10)) + '_' + CAST(raeDB.number AS varchar(10)) + '_' + CAST(raeDB.articleNum AS varchar(10)) + '.pdf' AS URL FROM dbo.raeDB WHERE raeDB.title LIKE '%' + @title + '%' OR raeDB.AuthorLast1 LIKE '%' + @author + '%' OR raeDB.AuthorLast2 LIKE '%' + @author + '%' OR raeDB.authorFirst1 LIKE '%' + @author + '%' OR raeDB.authorFirst2 LIKE '%' + @author + '%' OR 'Vol. ' + CAST(raeDB.Volume AS varchar( 10)) + ' Num. ' + CAST(raeDB.[number] AS varchar(10)) LIKE '%' + @volume + '%' ORDER BY Volume DESC , number DESC , ArticleNum IF @PeriodicalId = 6 SELECT @PeriodicalId AS PeriodicalId , aenDB.number , aenDB.display , aenDB.title , aenDB.authorFirst1 + ' ' + aenDB.authorLast1 AS AuthorName , aenDB.authorLast1 AS AuthorLast , 'Vol. ' + CAST(aenDB.Volume AS varchar(10)) + ' Num. ' + CAST(aenDB .[number] AS varchar(10)) AS Volume , 'Volume ' + CAST(aenDB.Volume AS varchar(10)) AS Season , '/journals/aen/aen' + CAST(aenDB.Volume AS varchar(10)) + '_' + CAST(aenDB.number AS varchar(10)) + '_' + CAST(aenDB.articleNum AS varchar(10)) + aenDB.[fileType] AS URL FROM dbo.aenDB WHERE aenDB.title LIKE '%' + @title + '%' OR aenDB.AuthorLast1 LIKE '%' + @author + '%' OR aenDB.AuthorLast2 LIKE '%' + @author + '%' OR aenDB.authorFirst1 LIKE '%' + @author + '%' OR aenDB.authorFirst2 LIKE '%' + @author + '%' OR 'Vol. ' + CAST(aenDB.Volume AS varchar( 10)) + ' Num. ' + CAST(aenDB.[number] AS varchar(10)) LIKE '%' + @volume + '%' ORDER BY Volume DESC , number DESC , ArticleNum IF @PeriodicalId = 7 SELECT @PeriodicalId AS PeriodicalId , [paper_name] + '(' + notation + ')' AS 'Title' , [author_name] AS AuthorName , [author_name] AS AuthorLast , [whenit] AS 'DatePosted' , '' AS 'Month' , '/journals/scholar/' + [file_name] AS [url] FROM scholar WHERE [paper_name] LIKE '%' + @title + '%' OR [author_name] LIKE '%' + @author + '%' OR [notation] LIKE '%' + @title + '%' ORDER BY control DESC IF @PeriodicalId = 8 SELECT WardLibrary.[control] as Id, @PeriodicalId AS PeriodicalId , title AS 'Title' , AuthorFirst + ' ' + AuthorLast + ' ' + coauthors AS AuthorName , [AuthorLast] AS AuthorLast , donatedDate AS 'DatePosted' , '' AS 'Season' , '' AS 'Volume' , 'book.aspx?Id=' + CAST(CONTROL AS varchar(10)) AS [url] FROM WardLibrary WHERE --display = 'yes' AND ( WardLibrary.comments LIKE '%' + @title + '%' OR WardLibrary.title LIKE '%' + @title + '%' OR WardLibrary.authorlast LIKE '%' + @author + '%' OR WardLibrary.authorfirst2 LIKE '%' + @author + '%' OR WardLibrary.authorfirst3 LIKE '%' + @author + '%' OR WardLibrary. subject LIKE '%' + @subject + '%' OR AuthorFirst + ' ' + AuthorLast LIKE '%' + @author + '%' ) ORDER BY control DESC END END --PeriodicalsGetArchives 3 --select * from aen --[PeriodicalsGetArchives] 6,'Buchanan' GO /****** Object: StoredProcedure [dbo].[PeriodicalAddStory] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[PeriodicalAddStory] @PeriodicalId int , @EditedBy varchar(50) , @authorfirst varchar(50) , @authorlast varchar(50) , @authorfirst2 varchar(50) , @authorlast2 varchar(50) , @authorfirst3 varchar(50) , @authorlast3 varchar(50) , @title varchar(255) , @body varchar(max) , @issue_season int , @issue_year int AS BEGIN INSERT INTO misesreview ( authorfirst , authorlast , authorfirst2 , authorlast2 , authorfirst3 , authorlast3 , title , body , issue_season , issue_year ) VALUES ( @authorfirst , @authorlast , @authorfirst2 , @authorlast2 , @authorfirst3 , @authorlast3 , @title , @body , @issue_season , @issue_year ) SELECT SCOPE_IDENTITY() END GO /****** Object: StoredProcedure [dbo].[PageUpdate] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[PageUpdate] @GUID uniqueidentifier , @Title varchar(255) , @Keywords varchar(500) , @Description varchar(500) , @Content varchar(max) --@Name varchar(500), --@Folder varchar(255), AS BEGIN UPDATE Page SET pageTitle = @Title , [content] = @Content , metaKeywords = @Keywords , metaDescription = @Description , EditDate = GETDATE() WHERE GUID = @GUID END GO /****** Object: StoredProcedure [dbo].[PagesGetGUID] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[PagesGetGUID] @PageName varchar(255) , @Path varchar(255) AS BEGIN DECLARE @GUID uniqueidentifier SET @GUID = ( SELECT Page.GUID FROM dbo.Page WHERE ( Page.pageName = @PageName ) AND ( Page.folder LIKE @path ) ) SELECT @GUID END GO /****** Object: StoredProcedure [dbo].[PageContentGetDetail] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[PageContentGetDetail] @ContentName varchar(150) = 0 AS BEGIN SET NOCOUNT ON ; SELECT Body FROM pagecontent WHERE NAME = @ContentName END GO /****** Object: StoredProcedure [dbo].[MisesReviewGetReview] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MisesReviewGetReview] @control int AS BEGIN SET NOCOUNT ON ; SELECT title , authorFirst , authorLast , authorFirst2 , authorlast2 , authorFirst3 , authorlast3 , issue_Year , issue_Season , body , CreateDate , GUID FROM misesreview WHERE ( control = @control ) END GO /****** Object: StoredProcedure [dbo].[QuotesGetSubjectList] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[QuotesGetSubjectList] AS BEGIN SET NOCOUNT ON ; SELECT DISTINCT Subject FROM QUOTES END GO /****** Object: StoredProcedure [dbo].[QuoteGetRandom] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[QuoteGetRandom] AS -- Random Quote SELECT TOP 1 QuoteId , Quote , AuthorFirst + ' ' + AuthorLast AS Author, Source , Subject , Quotes.AuthorId FROM Quotes tablesample(10 percent) JOIN DocumentAuthors ON DocumentAuthors.AuthorId = Quotes.AuthorId --WHERE -- QuoteId = @Quote --ORDER BY NEWID() GO /****** Object: StoredProcedure [dbo].[QuizIncrementCount] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[QuizIncrementCount] @QuizId int AS BEGIN SET NOCOUNT ON ; UPDATE dbo.quizType SET NumTaken = NumTaken + 1 WHERE QuizID = @QuizId END GO /****** Object: StoredProcedure [dbo].[QuizGetList] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[QuizGetList] AS BEGIN SET NOCOUNT ON ; SELECT * FROM dbo.quizType WHERE Display = 1 END GO /****** Object: StoredProcedure [dbo].[PageAddNew] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[PageAddNew] @Title varchar(500) , @Name varchar(500) , @Folder varchar(255) , @Content varchar(max) , @Keywords varchar(500) , @Description varchar(max) AS BEGIN IF ( SELECT COUNT(1) FROM page WHERE folder = @Folder AND pageName = @Name ) > 0 UPDATE Page SET pageTitle = @Title , pageName = @Name , folder = @Folder , [content] = @Content , metaKeywords = @Keywords , metaDescription = @Description , EditDate = GETDATE() WHERE folder = @Folder AND pageName = @Name ELSE INSERT INTO Page ( pageTitle , pageName , folder , [content] , metaKeywords , metaDescription ) VALUES ( @Title , @Name , @Folder , @Content , @Keywords , @Description ) END GO /****** Object: StoredProcedure [dbo].[FavoritesGetUserFavorites] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[FavoritesGetUserFavorites] -- Add the parameters for the stored procedure here @UserId varchar(100) , @IPaddress varchar(50) AS BEGIN IF @UserId <> '' AND @UserId <> 'NONE' UPDATE Favorites SET UserId = @UserId WHERE IPaddress = @IPaddress SELECT FavoriteId , UserId , URL , Title , IPaddress , CreateDate FROM Favorites WHERE UserId = @UserId OR IPaddress = @IPaddress ORDER BY [CreateDate] DESC END GO /****** Object: StoredProcedure [dbo].[PageGetIdByGUID] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[PageGetIdByGUID] @GUID uniqueidentifier AS BEGIN SET NOCOUNT ON ; SELECT PageId FROM Page WHERE [GUID] = @GUID END GO /****** Object: StoredProcedure [dbo].[PageGetGUIDByPageId] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[PageGetGUIDByPageId] @PageId int AS BEGIN SET NOCOUNT ON ; SELECT GUID FROM Page WHERE PageId = @PageId END GO /****** Object: StoredProcedure [dbo].[MediaCategoryGetParents] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavjdV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MediaCategoryGetParents] AS BEGIN SET NOCOUNT ON ; SELECT CategoryId , Category , Description , CreateDate FROM dbo.MediaCategory WHERE CategoryId IN ( SELECT ParentCategory FROM dbo.MediaCategory ) OR ParentCategory = 0 ORDER BY Category END GO /****** Object: StoredProcedure [dbo].[MediaCategoryGetAll] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavjdV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MediaCategoryGetAll] AS BEGIN SET NOCOUNT ON ; SELECT CategoryId , Category , Description , CreateDate FROM MediaCategory ORDER BY SortOrder , CreateDate DESC , Category END GO /****** Object: StoredProcedure [dbo].[FellowsGetBio] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[FellowsGetBio] @control int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON ; SELECT name , bio FROM fellows WHERE ( control = @control ) END GO /****** Object: StoredProcedure [dbo].[FavoriteAdd] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[FavoriteAdd] @UserId varchar(max) , @URL varchar(max) , @Title varchar(max) , @IPaddress varchar(50) AS BEGIN INSERT INTO Favorites ( URL , Title , UserId , IPaddress ) VALUES ( @URL , @Title , @UserId , @Ipaddress ) END GO /****** Object: StoredProcedure [dbo].[ExceptionsGetReport] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[ExceptionsGetReport] AS BEGIN SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT Date = DATEPART(day,DateLastOccurred) , Hour = DATEPART(hour,DateLastOccurred) , TotalCount = SUM(Frequency) FROM dbo.Exceptions WITH(READUNCOMMITTED) WHERE DateLastOccurred > DATEPART(day,GETDATE() -3) GROUP BY DATEPART(day,DateLastOccurred) , DATEPART(hour,DateLastOccurred) ORDER BY DATEPART(day,DateLastOccurred) , DATEPART(hour,DateLastOccurred) END GO /****** Object: StoredProcedure [dbo].[Exceptions_Log] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Exceptions_Log] ( @ExceptionHash varchar(128), @ServerName VARCHAR(64), @Exception varchar(200), @ExceptionMessage varchar(MAX), @UserAgent varchar(64), @IPAddress varchar(15), @HttpReferrer varchar (256), @HttpVerb varchar(24), @PathAndQuery varchar(512) ) AS BEGIN SET Transaction Isolation Level Read UNCOMMITTED IF EXISTS (SELECT ExceptionID FROM Exceptions WHERE ExceptionHash = @ExceptionHash and ServerName = @ServerName) UPDATE Exceptions SET DateLastOccurred = GetDate(), Frequency = Frequency + 1 WHERE ExceptionHash = @ExceptionHash and ServerName = @ServerName ELSE INSERT INTO Exceptions ( ExceptionHash, ServerName, Exception, ExceptionMessage, UserAgent, IPAddress, HttpReferrer, HttpVerb, PathAndQuery, DateCreated, DateLastOccurred, Frequency ) VALUES ( @ExceptionHash, @ServerName, @Exception, @ExceptionMessage, @UserAgent, @IPAddress, @HttpReferrer, @HttpVerb, @PathAndQuery, GetDate(), GetDate(), 1 ) END GO /****** Object: StoredProcedure [dbo].[DonationInsert] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DonationInsert] @DonationID int OUTPUT , @FirstName nvarchar(100) , @LastName nvarchar(100) , @Phone varchar(50) , @Address nvarchar(2000) , @City nvarchar(200) , @State varchar(50) , @Zip varchar(50) , @Country varchar(50) , @Email nvarchar(200) , @MisesMember bit , @Amount money , @OtherAmount money , @Designation varchar(500) , @OtherDesignation varchar(500) , @Recurring bit , @RecurringInterval int , @GiftEstate bit , @GiftIncome bit , @CardName varchar(100) , @CardType varchar(50) , @CardNumber varchar(50) , @CardExpMonth int , @CardExpYear int , @Comments varchar(max) , @ApprovalCode int , @TransactionId varchar(50) , @CreateUserID int AS BEGIN INSERT INTO Donations ( FirstName , LastName , Phone , [Address] , [State] , Zip , City , Country , Email , MisesMember , Amount , OtherAmount , Designation , OtherDesignation , Recurring , RecurringInterval , GiftIncome , GiftEstate , CardName , CardType , CardNumber , CardExpMonth , CardExpYear , Comments , ApprovalCode , CreateTime , CreateUserID , TransactionId ) VALUES ( @FirstName , @LastName , @Phone , @Address , @State , @Zip , @City , @Country , @Email , @MisesMember , @Amount , @OtherAmount , @Designation , @OtherDesignation , @Recurring , @RecurringInterval , @GiftIncome , @GiftEstate , @CardName , @CardType , @CardNumber , @CardExpMonth , @CardExpYear , @Comments , @ApprovalCode , GETDATE() , @CreateUserID , @TransactionId ) SET @DonationId = SCOPE_IDENTITY() END GO /****** Object: StoredProcedure [dbo].[DonationGetTotal] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DonationGetTotal] AS BEGIN SELECT SUM(OtherAmount + Amount) FROM donations WHERE LastName NOT IN ('Veksler','Tucker') AND Email NOT IN ('heroic@gmail.org') AND email NOT LIKE '%mises.org%' END GO /****** Object: StoredProcedure [dbo].[DonationGetReport] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DonationGetReport] AS BEGIN SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT -- Day = DATEPART(day,CreateTime) Month = DATEPART(month,CreateTime) , Year = DATEPART(year,CreateTime) --, CAST(DATEPART(day,CreateTime) as VARCHAR(MAX)) + '/' + , CAST(DATEPART(month,CreateTime) as VARCHAR(MAX)) + '/' + CAST(DATEPART(year,CreateTime) as VARCHAR(MAX)) AS Date , Total = SUM(Amount + OtherAmount) FROM dbo.Donations WITH(READUNCOMMITTED) WHERE LastName NOT IN ('Veksler','Tucker') AND Email NOT IN ('heroic@gmail.org') AND email NOT LIKE '%mises.org%' AND FirstName != 'OLAOYE RAYMOND WILSON' -- WHERE CreateTime > DATEPART(day,GETDATE() -30) GROUP BY -- DATEPART(day,CreateTime) DATEPART(month,CreateTime) , DATEPART(year,CreateTime) ,CAST(DATEPART(month,CreateTime) as VARCHAR(MAX)) + '/' + CAST(DATEPART(year,CreateTime) as VARCHAR(MAX)) ORDER BY DATEPART(year,CreateTime) , DATEPART(month,CreateTime) -- , DATEPART(day,CreateTime) DESC END GO /****** Object: StoredProcedure [dbo].[DonationGetList] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DonationGetList] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT DonationID, FirstName + ' ' + LastName AS Name, Email, Amount + OtherAmount AS 'Amount', Recurring, CardType, CreateTime FROM dbo.Donations WHERE LastName NOT IN ('Veksler','Tucker') AND Email NOT IN ('heroic@gmail.org') AND email NOT LIKE '%mises.org%' ORDER BY DonationID DESC -- SELECT CreateTIme, Amount, CardName FROM dbo.Donations --WHERE DATEPART(YEAR, CreateTIme) = 2009 --AND DATEPART(MONTH,CreateTIme) = 1 --ORDER BY Amount DESC END GO /****** Object: StoredProcedure [dbo].[MediaDetails] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[MediaDetails] @MediaId int AS SET CONCAT_NULL_YIELDS_NULL OFF DECLARE @DocumentId int SET @DocumentId = ( SELECT DocumentId FROM MediaAlternateFormat WHERE MediaId = @MediaId ) EXEC DocumentDetails @DocumentId = @DocumentId GO /****** Object: StoredProcedure [dbo].[DocumentMediaTypeGetList] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DocumentMediaTypeGetList] AS BEGIN SET NOCOUNT ON ; SELECT MediaType , MediaTypeId , MediaIconPath FROM DocumentMediaType WHERE MediaTypeId NOT IN ( 1 , 2 , 8,12,14 ) END GO /****** Object: StoredProcedure [dbo].[DocumentAuthorsAdd] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Name -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DocumentAuthorsAdd] -- Add the parameters for the stored procedure here @AuthorFirst varchar(255) , @AuthorMiddle varchar(255) , @AuthorLast varchar(255) AS BEGIN INSERT INTO DocumentAuthors ( AuthorFirst , AuthorMiddle , AuthorLast ) VALUES ( @AuthorFirst , @AuthorMiddle , @AuthorLast ) END GO /****** Object: StoredProcedure [dbo].[CalendarGetEventDetail] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[CalendarGetEventDetail] @EventId int AS BEGIN SELECT EventId , Title , EventDate , StartDate, EndDate , IntroText , [Description] , Location , Display , CreateDate FROM calendar WHERE EventId = @EventId END GO /****** Object: StoredProcedure [dbo].[DocumentGetLibertarianStudiesSubjects] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DocumentGetLibertarianStudiesSubjects] AS BEGIN SELECT SubjectId , ShortSubject FROM DocumentSubjects WHERE CategoryID = 4 AND Visible = 1 ORDER BY sortorder END GO /****** Object: StoredProcedure [dbo].[DocumentSubjectsGetList] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DocumentSubjectsGetList] @CategoryId int = 0 AS BEGIN IF @CategoryId > 0 SELECT Subject , SubjectId FROM DocumentSubjects WHERE Visible = 1 AND [CategoryId] = @CategoryId ORDER BY SortOrder ELSE SELECT Subject , SubjectId FROM DocumentSubjects WHERE Visible = 1 ORDER BY SortOrder END GO /****** Object: StoredProcedure [dbo].[DocumentGetAustrianEconomicsSubjects] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DocumentGetAustrianEconomicsSubjects] AS BEGIN SELECT SubjectId , ShortSubject FROM DocumentSubjects WHERE CategoryID = 1 AND Visible = 1 ORDER BY sortorder END GO /****** Object: StoredProcedure [dbo].[AspNet_SqlCacheUpdateChangeIdStoredProcedure] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[AspNet_SqlCacheUpdateChangeIdStoredProcedure] @tableName nvarchar( 450) AS BEGIN UPDATE dbo.AspNet_SqlCacheTablesForChangeNotification WITH ( ROWLOCK ) SET changeId = changeId + 1 WHERE tableName = @tableName END GO /****** Object: StoredProcedure [dbo].[AspNet_SqlCacheUnRegisterTableStoredProcedure] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[AspNet_SqlCacheUnRegisterTableStoredProcedure] @tableName nvarchar (450) AS BEGIN BEGIN TRAN DECLARE @triggerName AS nvarchar(3000) DECLARE @fullTriggerName AS nvarchar(3000) SET @triggerName = REPLACE(@tableName , '[' , '__o__') SET @triggerName = REPLACE(@triggerName , ']' , '__c__') SET @triggerName = @triggerName + '_AspNet_SqlCacheNotification_Trigger' SET @fullTriggerName = 'dbo.[' + @triggerName + ']' /* Remove the table-row from the notification table */ IF EXISTS ( SELECT name FROM sysobjects WITH ( NOLOCK ) WHERE name = 'AspNet_SqlCacheTablesForChangeNotification' AND type = 'U' ) IF EXISTS ( SELECT name FROM sysobjects WITH ( TABLOCKX ) WHERE name = 'AspNet_SqlCacheTablesForChangeNotification' AND type = 'U' ) DELETE FROM dbo.AspNet_SqlCacheTablesForChangeNotification WHERE tableName = @tableName /* Remove the trigger */ IF EXISTS ( SELECT name FROM sysobjects WITH ( NOLOCK ) WHERE name = @triggerName AND type = 'TR' ) IF EXISTS ( SELECT name FROM sysobjects WITH ( TABLOCKX ) WHERE name = @triggerName AND type = 'TR' ) EXEC ( 'DROP TRIGGER '+@fullTriggerName ) COMMIT TRAN END GO /****** Object: StoredProcedure [dbo].[AspNet_SqlCacheRegisterTableStoredProcedure] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[AspNet_SqlCacheRegisterTableStoredProcedure] @tableName nvarchar( 450) AS BEGIN DECLARE @triggerName AS nvarchar(3000) DECLARE @fullTriggerName AS nvarchar(3000) DECLARE @canonTableName nvarchar(3000) DECLARE @quotedTableName nvarchar(3000) /* Create the trigger name */ SET @triggerName = REPLACE(@tableName , '[' , '__o__') SET @triggerName = REPLACE(@triggerName , ']' , '__c__') SET @triggerName = @triggerName + '_AspNet_SqlCacheNotification_Trigger' SET @fullTriggerName = 'dbo.[' + @triggerName + ']' /* Create the cannonicalized table name for trigger creation */ /* Do not touch it if the name contains other delimiters */ IF ( CHARINDEX('.' , @tableName) <> 0 OR CHARINDEX('[' , @tableName) <> 0 OR CHARINDEX(']' , @tableName) <> 0 ) SET @canonTableName = @tableName ELSE SET @canonTableName = '[' + @tableName + ']' /* First make sure the table exists */ IF ( SELECT OBJECT_ID(@tableName , 'U') ) IS NULL BEGIN RAISERROR ( '00000001' , 16 , 1 ) RETURN END BEGIN TRAN /* Insert the value into the notification table */ IF NOT EXISTS ( SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH ( NOLOCK ) WHERE tableName = @tableName ) IF NOT EXISTS ( SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH ( TABLOCKX ) WHERE tableName = @tableName ) INSERT dbo.AspNet_SqlCacheTablesForChangeNotification VALUES ( @tableName , GETDATE() , 0 ) /* Create the trigger */ SET @quotedTableName = QUOTENAME(@tableName , '''') IF NOT EXISTS ( SELECT name FROM sysobjects WITH ( NOLOCK ) WHERE name = @triggerName AND type = 'TR' ) IF NOT EXISTS ( SELECT name FROM sysobjects WITH ( TABLOCKX ) WHERE name = @triggerName AND type = 'TR' ) EXEC ( 'CREATE TRIGGER '+@fullTriggerName+' ON '+@canonTableName+ ' FOR INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N' +@quotedTableName+' END ' ) COMMIT TRAN END GO /****** Object: StoredProcedure [dbo].[AspNet_SqlCacheQueryRegisteredTablesStoredProcedure] Script Date: 09/24/2011 22:43:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[AspNet_SqlCacheQueryRegisteredTablesStoredProcedure] AS SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification GO /****** Object: StoredProcedure [dbo].[AspNet_SqlCachePollingStoredProcedure] Script Date: 09/24/2011 22:43:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[AspNet_SqlCachePollingStoredProcedure] AS SELECT tableName , changeId FROM dbo.AspNet_SqlCacheTablesForChangeNotification RETURN 0 GO /****** Object: StoredProcedure [dbo].[_DeleteOldData] Script Date: 09/24/2011 22:43:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[_DeleteOldData] AS BEGIN UPDATE revision SET documenttext = '' WHERE editdate < GETDATE() - 60 AND editor = 'bkmarcus' UPDATE revision SET documenttext = '' WHERE editdate < GETDATE() - 60 AND editor = 'jtucker' TRUNCATE TABLE exceptions END GO /****** Object: StoredProcedure [dbo].[changePassword] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[changePassword] -- Add the parameters for the stored procedure here @Username varchar(50) , @Password varchar(50) AS BEGIN SET NOCOUNT ON ; UPDATE ManagerUsers SET password = @Password WHERE username = @Username END GO /****** Object: StoredProcedure [dbo].[CalendarGetUpcomingEvents] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[CalendarGetUpcomingEvents] @limit int = 0 AS BEGIN IF @limit > 0 SET ROWCOUNT @limit SELECT EventId , Title , [Description] , EventDate , StartDate, EndDate , IntroText , EventImage , Location , Display , CreateDate , GUID FROM calendar WITH ( NOLOCK ) WHERE display = 1 AND EndDate > GETDATE() - 1 ORDER BY EndDate ASC END GO /****** Object: StoredProcedure [dbo].[CalendarGetEventsList] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[CalendarGetEventsList] AS BEGIN EXEC dbo.CalendarGetUpcomingEvents SELECT TOP 30 EventId , Title , EventDate , StartDate, EndDate , IntroText , EventImage , Location , Display , CreateDate FROM calendar WITH ( NOLOCK ) WHERE display = 1 AND EndDate < GETDATE() - 1 ORDER BY EndDate DESC END GO /****** Object: StoredProcedure [dbo].[_MediaCategoryUpdateImages] Script Date: 09/24/2011 22:43:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[_MediaCategoryUpdateImages] AS BEGIN UPDATE dbo.MediaCategory SET CategoryImage = 'http://mises.org/media/poster/' + CAST (dbo.Documents.DocumentId AS varchar(max)) FROM dbo.MediaCategory JOIN dbo.Documents ON dbo.Documents.CategoryId = dbo.MediaCategory.CategoryId WHERE CategoryImage IS NULL AND metaImage IS NOT NULL END GO /****** Object: StoredProcedure [dbo].[_JLStoDocuments] Script Date: 09/24/2011 22:43:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Stephen W. Carson, JLStoDocuments> -- Create date: <August 26, 2007> -- Description: <Copies info from the jls table into the Documents structure> -- ============================================= CREATE PROCEDURE [dbo].[_JLStoDocuments] @last_id int = 0 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON ; INSERT INTO Documents ( Display , -- oldMediaTypeId , Source , Title , Author1 , Author2 , PubInfo , metaDescription ) SELECT 0 , -- 3 , 'JLS' , '"' + j.title + '"' , a1.AuthorId , a2.AuthorId, '<em>Journal of Libertarian Studies</em>, ' + CAST(j.volume AS varchar(10)) + '(' + CAST(j.number AS varchar(10)) + ').' , 'http://www.mises.org/journals/jls/' + CAST(j.volume AS varchar(10)) + '_' + CAST(j.number AS varchar(10)) + '/' + CAST(j.volume AS varchar(10)) + '_' + CAST(j.number AS varchar(10)) + '_' + CAST(j.articleNum AS varchar(10)) + '.pdf' FROM jls j LEFT OUTER JOIN DocumentAuthors a1 ON j.authorFirst1 = a1.AuthorFirst AND j.authorLast1 = a1.AuthorLast LEFT OUTER JOIN DocumentAuthors a2 ON j.authorFirst2 = a2.AuthorFirst AND j.authorLast2 = a2.AuthorLast WHERE j.volume = 20 AND j.number = 2 AND j.articleNum = 1 ORDER BY j.volume , j.number , j.articleNum SET @last_id = SCOPE_IDENTITY() INSERT INTO MediaAlternateFormat ( DocumentID , MediaTypeID , URL ) SELECT @last_id , 3 , metaDescription FROM Documents WHERE DocumentID = @last_id END GO /****** Object: StoredProcedure [dbo].[DocumentDetails] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[DocumentDetails] @DocumentId int = 0 , @MediaId int = 0 AS SET CONCAT_NULL_YIELDS_NULL OFF IF @MediaId > 0 BEGIN EXEC MediaDetails @MediaId RETURN END DECLARE @ParentCategoryId int SET @ParentCategoryId = ( SELECT TOP 1 ParentCategory FROM MediaCategory dc WHERE dc.CategoryId = ( SELECT CategoryId FROM dbo.Documents WHERE DocumentId = @DocumentId ) ) SELECT DocumentId , GUID , Display , Featured , Title , Author1 , Author2 , PubInfo , FullText , Source , GuideContent , Documents.CreateDate , CategoryId , ProductId, DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS AuthorName , DocumentAuthors2.AuthorFirst + ' ' + DocumentAuthors2.AuthorMiddle + ' ' + DocumentAuthors2.AuthorLast AS CoAuthorName , @ParentCategoryId AS ParentCategoryId , ( SELECT TOP 1 ParentCategory FROM MediaCategory dc WHERE dc.CategoryId = @ParentCategoryId ) AS GrandParentCategoryId , [Length] , metaDescription, metaKeywords , (SELECT TOP 1 Description FROM AbleCommerce.dbo.ac_Products WHERE Documents.ProductId = AbleCommerce.dbo.ac_Products.ProductId) AS StoreDescription, --SELECT ModelNumber, SearchKeywords FROM AbleCommerce.dbo.ac_Products (SELECT TOP 1 ModelNumber FROM AbleCommerce.dbo.ac_Products WHERE Documents.ProductId = AbleCommerce.dbo.ac_Products.ProductId) AS ISBN, (SELECT TOP 1 SearchKeywords FROM AbleCommerce.dbo.ac_Products WHERE Documents.ProductId = AbleCommerce.dbo.ac_Products.ProductId) AS StoreKeywords, metaImage, --REPLACE((SELECT TOP 1 ImageUrl FROM AbleCommerce.dbo.ac_Products WHERE [Name] = Documents.Title),'~','/store/') AS CoverImage REPLACE((SELECT TOP 1 ImageUrl FROM AbleCommerce.dbo.ac_Products WHERE Documents.ProductId = AbleCommerce.dbo.ac_Products.ProductId),'~','/store/') AS CoverImage, Documents.ProductId FROM Documents LEFT JOIN dbo.DocumentAuthors ON Documents.Author1 = DocumentAuthors.AuthorId LEFT JOIN dbo.DocumentAuthors DocumentAuthors2 ON Documents.Author2 = DocumentAuthors2.AuthorId WHERE ( DocumentId = @DocumentId ) SELECT DocumentSubjectLink.SubjectId FROM DocumentSubjectLink JOIN DocumentSubjects ON DocumentSubjects.SubjectId = DocumentSubjectLink.SubjectId WHERE DocumentId = @DocumentId SELECT MediaAlternateFormat.MediaId , MediaAlternateFormat.DocumentId , MediaAlternateFormat.MediaTypeId , MediaAlternateFormat.URL , MediaAlternateFormat.CreateDate , DocumentMediaType.MediaTypeID AS Expr1 , DocumentMediaType.MediaType , DocumentMediaType.MediaIconPath , DocumentMediaType.UploadPath , DocumentMediaType.Description , DocumentMediaType.CreateTime , DocumentMediaType.MIMEtype , DocumentMediaType.IsDeleted , DocumentMediaType.Extensions , DocumentMediaType.IsMedia , MediaAlternateFormat.fileSize , MediaAlternateFormat.duration FROM MediaAlternateFormat LEFT OUTER JOIN DocumentMediaType ON DocumentMediaType.MediaTypeID = MediaAlternateFormat.MediaTypeId WHERE ( MediaAlternateFormat.DocumentId = @DocumentId ) GO /****** Object: StoredProcedure [dbo].[DocumentsGetImage] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DocumentsGetImage] @DocumentId int AS BEGIN SELECT metaImage FROM dbo.Documents WITH ( NOLOCK ) WHERE DocumentId = @DocumentId END GO /****** Object: StoredProcedure [dbo].[DocumentGetSourceJournalList] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DocumentGetSourceJournalList] AS BEGIN SET NOCOUNT ON ; SELECT DISTINCT source FROM Documents WHERE [Display] = 1 AND source <> '' AND PATINDEX('%http%' , source) = 0 UNION ALL SELECT DISTINCT Title FROM Periodicals ORDER BY [source] END GO /****** Object: StoredProcedure [dbo].[DocumentGetNewLiterature] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DocumentGetNewLiterature] AS BEGIN SET CONCAT_NULL_YIELDS_NULL OFF SELECT TOP 7 Documents.DocumentId , Documents.Title , Documents.PubInfo AS Description , URL = CASE media.URL + '' WHEN '' THEN '/resources/' + CAST(Documents.DocumentId AS varchar(max)) ELSE media.URL END , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , Documents.CreateDate AS CreateDate , MediaIconPath = CASE DocumentMediaType.MediaIconPath + '' WHEN '' THEN '/images/Icons/html.png' ELSE DocumentMediaType.MediaIconPath END FROM dbo.Documents WITH ( NOLOCK ) LEFT JOIN dbo.MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN dbo.DocumentAuthors ON Documents.Author1 = DocumentAuthors.AuthorId LEFT JOIN dbo.DocumentMediaType ON media.MediaTypeId = DocumentMediaType.MediaTypeID WHERE Documents.Display = 1 AND media.URL <> '' AND DocumentMediaType.IsMedia = 0 ORDER BY documents.CreateDate DESC END GO /****** Object: StoredProcedure [dbo].[MediaAddMetadata] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Name -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MediaAddMetadata] @DocumentId int , @MediaId int , @filesize bigint , @duration decimal = 0 , @metaKeywords varchar(500) , @metaDescription varchar(MAX) , @metaImage image = NULL AS BEGIN UPDATE MediaAlternateFormat SET fileSize = @fileSize , duration = @duration WHERE MediaId = @mediaId -- update keywords UPDATE Documents SET metaKeywords = @metaKeywords, editdate = GETDATE() WHERE DocumentId = @DocumentId AND (metaKeywords IS NULL OR metaKeywords ='') -- update description UPDATE Documents SET metaDescription = @metaDescription , editdate = GETDATE() WHERE DocumentId = @DocumentId AND (metaDescription IS NULL OR metaDescription ='') -- save thumbnail IF @metaImage IS NOT NULL BEGIN UPDATE Documents SET metaImage = @metaImage WHERE ( DocumentId = @DocumentId ) UPDATE MediaCategory SET CategoryImage = 'http://mises.org/media/poster/' + CAST( @DocumentID AS varchar(10)) WHERE CategoryId IN ( SELECT CategoryId FROM Documents WHERE DocumentId = @DocumentId ) AND ( MediaCategory.CategoryImage IS NULL OR MediaCategory.CategoryImage = '' ) END END GO /****** Object: StoredProcedure [dbo].[DocumentTopAuthors] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DocumentTopAuthors] AS BEGIN SET NOCOUNT ON ; SET CONCAT_NULL_YIELDS_NULL OFF SELECT TOP 30 AuthorId , AuthorLast , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS 'AuthorName' , Photo, COUNT(1) AS 'Count' FROM [Documents] d JOIN [DocumentAuthors] da ON d.[Author1] = da.[AuthorId] OR d.[Author2] = da.[AuthorId] GROUP BY [AuthorId] , [AuthorLast] , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast, Photo ORDER BY COUNT DESC END GO /****** Object: StoredProcedure [dbo].[MediaGetAuthorsAlpha] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DocumentGetTopAuthors -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MediaGetAuthorsAlpha] AS SET CONCAT_NULL_YIELDS_NULL OFF BEGIN SELECT d.Author1 AS AuthorId , da.AuthorLast , da.AuthorFirst + ' ' + da.AuthorMiddle + ' ' + da.AuthorLast AS Author , COUNT(1) AS 'Count' FROM Documents d JOIN [DocumentAuthors] da ON d.[Author1] = da.[AuthorId] OR d.[Author2] = da.[AuthorId] JOIN MediaAlternateFormat ON MediaAlternateFormat.DocumentId = d.DocumentId WHERE MediaAlternateFormat.MediaTypeId IN ( 1 , 2 , 8 ) GROUP BY d.Author1 , da.AuthorLast , da.AuthorFirst , da.AuthorMiddle ORDER BY da.AuthorLast END GO /****** Object: StoredProcedure [dbo].[MediaGetAuthors] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DocumentGetTopAuthors -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MediaGetAuthors] AS SET CONCAT_NULL_YIELDS_NULL OFF BEGIN SELECT d.Author1 AS AuthorId , da.AuthorLast , da.AuthorFirst + ' ' + da.AuthorMiddle + ' ' + da.AuthorLast AS Author , COUNT(1) AS 'Count' FROM Documents d JOIN [DocumentAuthors] da ON d.[Author1] = da.[AuthorId] OR d.[Author2] = da.[AuthorId] JOIN MediaAlternateFormat ON MediaAlternateFormat.DocumentId = d.DocumentId JOIN dbo.DocumentMediaType ON dbo.MediaAlternateFormat.MediaTypeId = dbo.DocumentMediaType.MediaTypeID WHERE dbo.DocumentMediaType.IsMedia = 1 AND dbo.DocumentMediaType.IsMedia = 1 GROUP BY d.Author1 , da.AuthorLast , da.AuthorFirst , da.AuthorMiddle ORDER BY COUNT DESC END GO /****** Object: StoredProcedure [dbo].[MediaGetAuthorContents] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[MediaGetAuthorContents] @AuthorId int AS SET CONCAT_NULL_YIELDS_NULL OFF SELECT MediaCategory.CategoryId , MediaCategory.Category , MediaCategory.Description , MediaCategory.CategoryImage , ISNULL(( SELECT TOP 1 Documents.CreateDate FROM dbo.Documents WHERE Documents.CategoryId = MediaCategory.CategoryId ORDER BY Documents.CreateDate DESC ) , '1/1/2050') AS EditDate FROM dbo.MediaCategory WHERE MediaCategory.CategoryId = -1 SELECT Documents.DocumentId , Documents.Title , Documents.CategoryId , Documents.CreateDate , Documents.metaDescription AS 'Description' , media.DocumentId , media.MediaTypeId , media.URL , media.MediaId , Documents.EditDate , media.CreateDate , DocumentAuthors.AuthorId , DocumentAuthors2.AuthorId AS CoAuthorId , DocumentMediaType.MediaType , DocumentMediaType.MediaIconPath , -- MediaCategory.[Category] , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , DocumentAuthors2.AuthorFirst + ' ' + DocumentAuthors2.AuthorMiddle + ' ' + DocumentAuthors2.AuthorLast AS CoAuthor , DocumentAuthors.[AuthorLast] AS LastName , DocumentAuthors2.[AuthorLast] AS CoLastName , CASE WHEN Documents.metaImage IS NULL THEN 0 WHEN Documents.metaImage IS NOT NULL THEN 1 END AS HasImage FROM dbo.Documents LEFT JOIN dbo.MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN dbo.DocumentAuthors ON Documents.Author1 = DocumentAuthors.AuthorId LEFT JOIN dbo.DocumentMediaType ON DocumentMediaType.MediaTypeId = media.MediaTypeId LEFT JOIN dbo.MediaCategory ON Documents.CategoryId = MediaCategory.CategoryId LEFT JOIN dbo.DocumentAuthors AS DocumentAuthors2 ON Documents.Author2 = DocumentAuthors2.AuthorId WHERE DocumentMediaType.MediaTypeId IN ( 1 , 2 ) AND ( Documents.Author1 = @AuthorId OR Documents.Author2 = @AuthorId ) AND Documents.Display = 1 ORDER BY Documents.Title GO /****** Object: StoredProcedure [dbo].[MediaGetAllFeatured] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MediaGetAllFeatured] AS BEGIN SET NOCOUNT ON ; SET CONCAT_NULL_YIELDS_NULL OFF ; SELECT dbo.MediaAlternateFormat.MediaId , dbo.MediaAlternateFormat.DocumentId , dbo.MediaAlternateFormat.MediaTypeId , dbo.MediaAlternateFormat.URL , dbo.MediaAlternateFormat.fileSize , dbo.MediaAlternateFormat.duration , dbo.MediaAlternateFormat.CreateDate , dmt.IsMedia , dmt.MediaTypeId , dmt.Description , dmt.MediaIconPath FROM dbo.MediaAlternateFormat INNER JOIN dbo.Documents ON dbo.MediaAlternateFormat.DocumentId = dbo.Documents.DocumentId LEFT OUTER JOIN DocumentMediaType dmt ON dmt.MediaTypeID = MediaAlternateFormat.MediaTypeId WHERE Featured = 1 END GO /****** Object: StoredProcedure [dbo].[MediaTopAuthors] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DocumentGetTopAuthors -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MediaTopAuthors] AS SET CONCAT_NULL_YIELDS_NULL OFF BEGIN SELECT TOP 60 d.Author1 AS AuthorId , da.AuthorLast , da.AuthorFirst + ' ' + da.AuthorMiddle + ' ' + da.AuthorLast AS Author , COUNT(1) AS 'Count' FROM Documents d JOIN [DocumentAuthors] da ON d.[Author1] = da.[AuthorId] OR d.[Author2] = da.[AuthorId] JOIN MediaAlternateFormat ON MediaAlternateFormat.DocumentId = d.DocumentId WHERE MediaAlternateFormat.MediaTypeId IN ( 1 , 2 , 8 ) GROUP BY d.Author1 , da.AuthorLast , da.AuthorFirst , da.AuthorMiddle ORDER BY COUNT DESC END GO /****** Object: StoredProcedure [dbo].[MediaSearch] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[MediaSearch] @SearchClause varchar(100) AS SET CONCAT_NULL_YIELDS_NULL OFF SELECT MediaCategory.CategoryId , MediaCategory.Category , MediaCategory.Description , MediaCategory.CategoryImage , ISNULL(( SELECT TOP 1 Documents.CreateDate FROM dbo.Documents WHERE Documents.CategoryId = MediaCategory.CategoryId ORDER BY Documents.CreateDate DESC ) , '1/1/2050') AS EditDate FROM dbo.MediaCategory WHERE MediaCategory.CategoryId = -1 ORDER BY EditDate DESC SELECT Documents.DocumentId , Documents.GUID , Documents.Display , Documents.metaDescription AS 'Description' , Documents.Title , Documents.Source , Documents.CreateDate , [Documents].[EditDate] , media.URL , media.MediaId , DocumentMediaType.IsMedia, DocumentMediaType.MediaType , DocumentMediaType.MediaIconPath , mc.AuthorId , mc.AuthorFirst + ' ' + mc.AuthorMiddle + ' ' + mc.AuthorLast AS Author , mc2.AuthorId AS CoAuthorId , mc2.AuthorFirst + ' ' + mc2.AuthorMiddle + ' ' + mc2.AuthorLast AS CoAuthor , mc.[AuthorLast] AS LastName , CASE WHEN Documents.metaImage IS NULL THEN 0 WHEN Documents.metaImage IS NOT NULL THEN 1 END AS HasImage FROM Documents JOIN MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN DocumentMediaType ON DocumentMediaType.MediaTypeId = media.MediaTypeId LEFT JOIN DocumentAuthors mc ON Documents.Author1 = mc.AuthorId LEFT JOIN DocumentAuthors mc2 ON dbo.Documents.Author2 = mc.AuthorID WHERE DocumentMediaType.IsMedia = 1 AND ( Documents.Title LIKE '%' + @SearchClause + '%' OR Documents.PubInfo LIKE '%' + @SearchClause + '%' OR Documents.metaDescription LIKE '%' + @SearchClause + '%' OR Documents.metaKeywords LIKE '%' + @SearchClause + '%' OR mc.AuthorFirst LIKE @SearchClause OR mc.AuthorLast LIKE @SearchClause ) ORDER BY Documents.CreateDate DESC GO /****** Object: StoredProcedure [dbo].[MediaGetTopTagged] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: David V -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MediaGetTopTagged] -- Add the parameters for the stored procedure here @p1 int = 0 , @p2 int = 0 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON ; SET CONCAT_NULL_YIELDS_NULL OFF ; SELECT TOP 10 ObjectId , Documents.DocumentId , Documents.Title , Documents.Author1 , Documents.CreateDate , COUNT(1) AS 'Count' , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS 'AuthorName' FROM dbo.Documents INNER JOIN dbo.TagMap ON dbo.TagMap.ObjectId = dbo.Documents.GUID INNER JOIN dbo.MediaAlternateFormat ON dbo.Documents.DocumentId = dbo.MediaAlternateFormat.DocumentId INNER JOIN dbo. DocumentAuthors da ON da.AuthorId = dbo.Documents.Author1 WHERE Documents.Display = 1 AND dbo.Documents.FullText = 0 AND dbo.MediaAlternateFormat. MediaTypeId IN ( 1 , 2 , 8 , 12 , 14 ) AND TaggedDate > ( GETDATE() - 30 ) GROUP BY ObjectId , Documents.DocumentId , Documents.Title , Documents.Author1 , Documents.CreateDate , da.authorFirst , da.authorMiddle , da.authorLast ORDER BY COUNT DESC END GO /****** Object: StoredProcedure [dbo].[MediaGetSubCategories] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MediaGetSubCategories] @CategoryId int = 0 AS BEGIN SELECT MediaCategory.CategoryId , MediaCategory.Category , MediaCategory.Description , MediaCategory.CategoryImage , ISNULL(( SELECT TOP 1 Documents.CreateDate FROM dbo.Documents WHERE Documents.CategoryId = MediaCategory.CategoryId ORDER BY Documents.CreateDate DESC ) , '1/1/2050') AS EditDate FROM dbo.MediaCategory WHERE MediaCategory.ParentCategory = @CategoryId ORDER BY SortOrder , Category END GO /****** Object: StoredProcedure [dbo].[MediaGetRandomVideo] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[MediaGetRandomVideo] AS SET CONCAT_NULL_YIELDS_NULL OFF SELECT TOP 1 m.[MediaId], m.[DocumentId], m.[MediaTypeId], m.[URL], m.[fileSize], m.[duration], m.[CreateDate], m.[Display] FROM dbo.Documents d INNER JOIN dbo.MediaAlternateFormat m ON d.DocumentId = m.DocumentId WHERE m.Display = 1 AND m.MediaTypeId = 16 --TODO or in (2, 14, 16) plus any new ones? Maybe need a media type type?!? ORDER BY NEWID() GO /****** Object: StoredProcedure [dbo].[MediaGetMisesLive] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[MediaGetMisesLive] AS SET CONCAT_NULL_YIELDS_NULL OFF SELECT sg.DocumentId , sg.Title , sg.metaDescription AS Description , media.URL , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , sg.Author1 AS AuthorId , sg.CreateDate , CAST(( sg.Length / 60 ) AS int) AS Duration , 'TODO' AS Topics , CASE WHEN sg.metaImage IS NULL THEN 0 WHEN sg.metaImage IS NOT NULL THEN 1 END AS HasImage , CASE WHEN sg.metaImage IS NULL THEN 'none' WHEN sg.metaImage IS NOT NULL THEN 'inline' END AS Display FROM dbo.Documents sg JOIN dbo.MediaAlternateFormat media ON media.DocumentId = sg.DocumentId JOIN dbo.DocumentAuthors ON sg.Author1 = DocumentAuthors.AuthorId WHERE sg.Source = 'MisesLive' ORDER BY media.CreateDate DESC , media.DocumentId DESC GO /****** Object: StoredProcedure [dbo].[MediaGetMediaByType] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[MediaGetMediaByType] @MediaTypeID int = 0 , @MaxResults int = 0 AS SET CONCAT_NULL_YIELDS_NULL OFF IF @MaxResults > 0 SET ROWCOUNT @MaxResults; SELECT Documents.DocumentId , Documents.Title , Documents.CategoryId , Documents.CreateDate , Documents.metaDescription AS 'Description' , media.DocumentId , media.MediaTypeId , media.URL , media.MediaId , Documents.EditDate , media.CreateDate , DocumentAuthors.AuthorId , DocumentAuthors2.AuthorId AS CoAuthorId , DocumentMediaType.MediaType , DocumentMediaType.MediaIconPath , -- MediaCategory.[Category] , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , DocumentAuthors2.AuthorFirst + ' ' + DocumentAuthors2.AuthorMiddle + ' ' + DocumentAuthors2.AuthorLast AS CoAuthor , DocumentAuthors.[AuthorLast] AS LastName , DocumentAuthors2.[AuthorLast] AS CoLastName , CASE WHEN Documents.metaImage IS NULL THEN 0 WHEN Documents.metaImage IS NOT NULL THEN 1 END AS HasImage FROM dbo.Documents LEFT JOIN dbo.MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN dbo.DocumentAuthors ON Documents.Author1 = DocumentAuthors.AuthorId LEFT JOIN dbo.DocumentMediaType ON DocumentMediaType.MediaTypeId = media.MediaTypeId LEFT JOIN dbo.MediaCategory ON Documents.CategoryId = MediaCategory.CategoryId LEFT JOIN dbo.DocumentAuthors AS DocumentAuthors2 ON Documents.Author2 = DocumentAuthors2.AuthorId WHERE DocumentMediaType.MediaTypeId = @MediaTypeID ORDER BY Documents.Title GO /****** Object: StoredProcedure [dbo].[MediaGetLatestVideo] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[MediaGetLatestVideo] AS SET CONCAT_NULL_YIELDS_NULL OFF SELECT TOP 6 sg.DocumentId , dbo.MediaCategory.Category + ' >> ' + sg.Title AS Title , sg.PubInfo AS Description , media.URL , media.MediaId , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , sg.Author1 AS AuthorId , sg.CreateDate , CASE WHEN sg.metaImage IS NULL THEN 0 WHEN sg.metaImage IS NOT NULL THEN 1 END AS HasImage , CASE WHEN sg.metaImage IS NULL THEN 'none' WHEN sg.metaImage IS NOT NULL THEN 'inline' END AS Display FROM dbo.Documents sg JOIN dbo.MediaAlternateFormat media ON media.DocumentId = sg.DocumentId JOIN dbo.DocumentAuthors ON sg.Author1 = DocumentAuthors.AuthorId JOIN dbo.MediaCategory ON sg.CategoryId = dbo.MediaCategory.CategoryId WHERE media.MediaTypeId IN (2,16) ORDER BY media.CreateDate DESC , media.DocumentId DESC GO /****** Object: StoredProcedure [dbo].[MediaGetLatestAudio] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[MediaGetLatestAudio] AS SET CONCAT_NULL_YIELDS_NULL OFF SELECT TOP 10 sg.DocumentId , dbo.MediaCategory.Category + ' >> ' + sg.Title AS Title , sg.PubInfo AS Description , media.URL , media.MediaId , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , sg.Author1 AS AuthorId , sg.CreateDate , CASE WHEN sg.metaImage IS NULL THEN 0 WHEN sg.metaImage IS NOT NULL THEN 1 END AS HasImage , CASE WHEN sg.metaImage IS NULL THEN 'none' WHEN sg.metaImage IS NOT NULL THEN 'inline' END AS Display FROM dbo.Documents sg JOIN dbo.MediaAlternateFormat media ON media.DocumentId = sg.DocumentId JOIN dbo.DocumentAuthors ON sg.Author1 = DocumentAuthors.AuthorId JOIN dbo.MediaCategory ON sg.CategoryId = dbo.MediaCategory.CategoryId WHERE media.MediaTypeId = 1 ORDER BY media.CreateDate DESC , media.DocumentId DESC GO /****** Object: StoredProcedure [dbo].[MediaGetLatest] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[MediaGetLatest] AS SET CONCAT_NULL_YIELDS_NULL OFF SELECT TOP 5 sg.DocumentId , dbo.MediaCategory.Category + ' >> ' + sg.Title AS Title , sg.PubInfo AS Description , media.URL , media.MediaId , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , sg.Author1 AS AuthorId , sg.CreateDate , CASE WHEN sg.metaImage IS NULL THEN 0 WHEN sg.metaImage IS NOT NULL THEN 1 END AS HasImage , CASE WHEN sg.metaImage IS NULL THEN 'none' WHEN sg.metaImage IS NOT NULL THEN 'inline' END AS Display FROM dbo.Documents sg JOIN dbo.MediaAlternateFormat media ON media.DocumentId = sg.DocumentId JOIN dbo.DocumentAuthors ON sg.Author1 = DocumentAuthors.AuthorId JOIN dbo.MediaCategory ON sg.CategoryId = dbo.MediaCategory.CategoryId WHERE media.MediaTypeId IN ( 1 , 2 , 8, 16 ) ORDER BY media.CreateDate DESC , media.DocumentId DESC GO /****** Object: StoredProcedure [dbo].[MediaGetFeed] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[MediaGetFeed] @AuthorId int = 0 , @CategoryId int = 0 , @MediaTypeId int = 0 , @MaxResults int = 0 AS SET CONCAT_NULL_YIELDS_NULL OFF IF @MaxResults > 0 SET ROWCOUNT @MaxResults ; DECLARE @MediaTypes TABLE ( TypeId int ) IF @MediaTypeId > 0 INSERT INTO @MediaTypes VALUES ( @MediaTypeId ) --if (@MediaTypeId = 2) --BEGIN ----2,12,14,16 -- INSERT INTO @MediaTypes VALUES (12 ) -- INSERT INTO @MediaTypes VALUES (14 ) -- INSERT INTO @MediaTypes VALUES (16 ) --END ELSE BEGIN INSERT INTO @MediaTypes ( TypeId ) VALUES ( 1 ) -- Audio INSERT INTO @MediaTypes ( TypeId ) VALUES ( 2 ) -- Video INSERT INTO @MediaTypes ( TypeId ) VALUES ( 8 ) -- AudioBook END IF @AuthorId > 0 SELECT Documents.DocumentId , Documents.Title , Documents.GUID , Documents.PubInfo AS Description , media.URL , media.MediaId , DocumentMediaType.MIMEtype , Documents.Length , authorFirst + ' ' + authorMiddle + ' ' + authorLast AS 'Author' , Documents.Author1 , Documents.CreateDate , media.fileSize , media.duration , metaDescription , dbo.MediaCategory.iTunesCategoryCode , dbo.MediaCategory.Category FROM Documents INNER JOIN MediaAlternateFormat AS media ON media.DocumentId = Documents.DocumentId INNER JOIN DocumentAuthors ON Documents.Author1 = DocumentAuthors.AuthorId INNER JOIN dbo.MediaCategory ON dbo.MediaCategory.CategoryId = dbo.Documents.CategoryId LEFT OUTER JOIN DocumentMediaType ON DocumentMediaType.MediaTypeID = media.MediaTypeId WHERE media.MediaTypeId IN ( SELECT TypeId FROM @MediaTypes ) AND Documents.Display = 1 AND ( Documents .Author1 = @AuthorId OR Documents .Author2 = @AuthorId ) ORDER BY Documents.CreateDate IF @CategoryId > 0 BEGIN SELECT Documents.DocumentId , Documents.Title , Documents.GUID , Documents.PubInfo AS Description , media.URL , media.MediaId , DocumentMediaType.MIMEtype , Documents.Length , authorFirst + ' ' + authorMiddle + ' ' + authorLast AS 'Author' , Documents.Author1 , Documents.CreateDate , media.fileSize , media.duration , metaDescription , dbo.MediaCategory.iTunesCategoryCode , dbo.MediaCategory.Category FROM Documents INNER JOIN MediaAlternateFormat AS media ON media.DocumentId = Documents.DocumentId INNER JOIN DocumentAuthors ON Documents.Author1 = DocumentAuthors.AuthorId INNER JOIN [MediaCategory] ON [Documents].[CategoryId] = [MediaCategory].[CategoryId] LEFT OUTER JOIN DocumentMediaType ON DocumentMediaType.MediaTypeID = media.MediaTypeId WHERE media.MediaTypeId IN ( SELECT TypeId FROM @MediaTypes ) AND Documents.Display = 1 AND ( [Documents] . [CategoryId] = @CategoryId OR Documents . CategoryId IN ( SELECT m2.[CategoryId] FROM [MediaCategory] INNER JOIN [MediaCategory] m2 ON m2.[ParentCategory] = @CategoryId ) ) ORDER BY Documents.CreateDate SELECT Category , [Description] , CategoryImage FROM [MediaCategory] WHERE [CategoryId] = @CategoryId END ELSE SELECT TOP ( 60 ) Documents.DocumentId , Documents.Title , Documents.GUID , Documents.PubInfo AS Description , media.URL , media.MediaId , DocumentMediaType.MIMEtype , Documents.Length , authorFirst + ' ' + authorMiddle + ' ' + authorLast AS 'Author' , Documents.Author1 , Documents.CreateDate , media.fileSize , media.duration , metaDescription , dbo.MediaCategory.iTunesCategoryCode , dbo.MediaCategory.Category FROM Documents INNER JOIN MediaAlternateFormat AS media ON media.DocumentId = Documents.DocumentId INNER JOIN DocumentAuthors ON Documents.Author1 = DocumentAuthors.AuthorId INNER JOIN [MediaCategory] ON [Documents].[CategoryId] = [MediaCategory].[CategoryId] LEFT OUTER JOIN DocumentMediaType ON DocumentMediaType.MediaTypeID = media.MediaTypeId WHERE ( media.MediaTypeId IN ( SELECT TypeId FROM @MediaTypes ) ) AND ( Documents.Display = 1 ) ORDER BY Documents.CreateDate DESC , Documents.DocumentId DESC GO /****** Object: StoredProcedure [dbo].[MediaGetFeatured] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MediaGetFeatured] AS BEGIN SET NOCOUNT ON ; SET CONCAT_NULL_YIELDS_NULL OFF ; -- Get Headline Article SELECT dbo.Documents.DocumentId , dbo.Documents.GUID , dbo.Documents.Display , dbo.Documents.Featured , dbo.Documents.Title , dbo.Documents.Author1 , dbo.Documents.Author2 , dbo.Documents.PubInfo , dbo.Documents.metaDescription AS 'Description' , dbo.Documents.metaKeywords , dbo.Documents.metaImage , -- dbo.Documents.oldURL , -- dbo.Documents.oldMediaTypeId , dbo.Documents.FullText , dbo.Documents.Source , dbo.Documents.GuideContent , dbo.Documents.Length , dbo.Documents.CategoryId , dbo.Documents.CreateDate , dbo.Documents.EditDate , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS Author , da2.authorFirst + ' ' + da2.authorMiddle + ' ' + da2.authorLast AS CoAuthor , '/MediaPlayer.aspx?Id=' + CAST(DocumentId AS varchar(max)) AS 'URL' FROM dbo.Documents JOIN dbo.DocumentAuthors da ON da.AuthorId = Documents.Author1 LEFT JOIN dbo.DocumentAuthors da2 ON da2.AuthorId = Documents.Author2 WHERE -- Featured = 1 documentid IN (SELECT documentid FROM dbo.MediaAlternateFormat WHERE MediaTypeId = 16) ORDER BY dbo.Documents.DocumentId DESC SELECT dbo.MediaAlternateFormat.MediaId , dbo.MediaAlternateFormat.DocumentId , dbo.MediaAlternateFormat.MediaTypeId , dbo.MediaAlternateFormat.URL , dbo.MediaAlternateFormat.fileSize , dbo.MediaAlternateFormat.duration , dbo.MediaAlternateFormat.CreateDate , dmt.IsMedia , dmt.MediaTypeId , dmt.Description , dmt.MediaIconPath FROM dbo.MediaAlternateFormat INNER JOIN dbo.Documents ON dbo.MediaAlternateFormat.DocumentId = dbo.Documents.DocumentId LEFT OUTER JOIN DocumentMediaType dmt ON dmt.MediaTypeID = MediaAlternateFormat.MediaTypeId WHERE --Featured = 1 Documents.documentid IN (SELECT documentid FROM dbo.MediaAlternateFormat WHERE MediaTypeId = 16) END GO /****** Object: StoredProcedure [dbo].[MediaGetContributorList] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[MediaGetContributorList] AS SET CONCAT_NULL_YIELDS_NULL OFF SELECT DocumentAuthors.AuthorId , DocumentAuthors.AuthorFirst AS FirstName , DocumentAuthors.AuthorMiddle AS MiddleName , DocumentAuthors.AuthorLast AS LastName , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author FROM dbo.DocumentAuthors WHERE DocumentAuthors.AuthorId IN ( SELECT Documents.Author1 FROM dbo.Documents JOIN dbo.MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId WHERE media.MediaTypeId IN ( 1 , 2 ) AND media.Display = 1 ) ORDER BY DocumentAuthors.AuthorLast GO /****** Object: StoredProcedure [dbo].[MediaTypeGetList] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MediaTypeGetList] AS BEGIN SET NOCOUNT ON ; SELECT MediaType , MediaTypeId , MediaIconPath FROM DocumentMediaType WHERE MediaTypeId NOT IN ( 9,7,3,4,5,6) AND MediaTypeID IN (SELECT DISTINCT MediaTypeId FROM dbo.MediaAlternateFormat) END GO /****** Object: StoredProcedure [dbo].[oldMediaGetDetails] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[oldMediaGetDetails] @DocumentId int AS SET CONCAT_NULL_YIELDS_NULL OFF SELECT Documents.DocumentId , Documents.GUID , Documents.Display , Documents.Title , Documents.Author1 , Documents.Author2 , Documents.PubInfo , Documents.FullText , Documents.Source , Documents.Length , Documents.CategoryId , Documents.CreateDate , Documents.EditDate , DocumentAuthors.AuthorId , DocumentAuthors.AuthorFirst , DocumentAuthors.AuthorMiddle , DocumentAuthors.AuthorLast , DocumentAuthors.Photo , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , Documents.metaDescription , Documents.metaKeywords , Documents.metaImage FROM Documents LEFT OUTER JOIN DocumentAuthors ON Documents.Author1 = DocumentAuthors.AuthorId WHERE ( Documents.DocumentId = @DocumentId ) ORDER BY DocumentAuthors.AuthorLast SELECT MediaAlternateFormat.MediaId , MediaAlternateFormat.DocumentId , MediaAlternateFormat.MediaTypeId , MediaAlternateFormat.URL , MediaAlternateFormat.CreateDate , DocumentMediaType.MediaTypeID AS Expr1 , DocumentMediaType.MediaType , DocumentMediaType.MediaIconPath , DocumentMediaType.Extensions , DocumentMediaType.UploadPath , DocumentMediaType.Description , DocumentMediaType.CreateTime , DocumentMediaType.MIMEtype , DocumentMediaType.IsDeleted , MediaAlternateFormat.fileSize , MediaAlternateFormat.duration FROM MediaAlternateFormat LEFT OUTER JOIN DocumentMediaType ON DocumentMediaType.MediaTypeID = MediaAlternateFormat.MediaTypeId WHERE ( MediaAlternateFormat.DocumentId = @DocumentId ) GO /****** Object: StoredProcedure [dbo].[RegistrationFormDetails] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: 12/2005 -- Description: -- ============================================= CREATE PROCEDURE [dbo].[RegistrationFormDetails] @FormId int AS BEGIN SET NOCOUNT ON SELECT * FROM RegistrationForms WHERE FormId = @FormId SELECT * FROM RegistrationProducts WHERE FormId = @FormId ORDER BY ProductOrder SELECT * FROM RegistrationQuestions WHERE FormId = @FormId ORDER BY QuestionOrder END GO /****** Object: StoredProcedure [dbo].[TagGetAnonymousTaggers] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[TagGetAnonymousTaggers] AS BEGIN --SELECT * FROM tag JOIN dbo.TagMap ON dbo.Tag.TagId = dbo.TagMap.TagId --WHERE Tag.tagid NOT IN (SELECT tagid FROM tagmap WHERE TaggedBy = 'SearchEngineBot' OR TaggedBy = 'MisesBot') --ORDER BY TagMap.tagid desc SELECT TOP 3000 TaggedBy , Tag , TagMap.TaggedDate FROM TagMap JOIN Tag ON TagMap.TagId = Tag.TagId WHERE TaggedBy NOT IN ('SearchEngineBot', 'MisesBot' , 'HeroicLife' , 'jtucker' ) ORDER BY TagMap.TaggedDate DESC END GO /****** Object: StoredProcedure [dbo].[TagDelete] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[TagDelete] @tag varchar(75) = NULL , @user varchar(100) = NULL , @Identifier uniqueidentifier = NULL AS BEGIN IF @Identifier IS NOT NULL BEGIN IF @tag IS NOT NULL DELETE FROM TagMap FROM TagMap INNER JOIN Tag ON TagMap.TagId = Tag.TagId WHERE ( Tag.Tag = @Tag ) AND [TagMap].[ObjectId] = @Identifier ELSE IF @user IS NOT NULL DELETE FROM TagMap FROM TagMap INNER JOIN Tag ON TagMap.TagId = Tag.TagId WHERE ( Tag.Tag = @Tag ) AND [TagMap].[ObjectId] = @Identifier AND TagMap.TaggedBy = @user ELSE DELETE FROM TagMap FROM TagMap WHERE [TagMap].[ObjectId] = @Identifier END ELSE IF @tag IS NOT NULL DELETE FROM TagMap FROM TagMap INNER JOIN Tag ON TagMap.TagId = Tag.TagId WHERE ( Tag.Tag = @Tag ) ELSE DELETE FROM TagMap WHERE ( TaggedBy = @user ) END GO /****** Object: StoredProcedure [dbo].[TagAddNew] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[TagAddNew] @Tag varchar(70) , @Identifier uniqueidentifier , @User varchar(100) AS BEGIN -- EXEC TagGetTopTags @TagCount = 2000 -- SELECT * FROM Tag JOIN TagMap ON Tag.TagId = TagMap.TagId WHERE TaggedBy <> 'MisesBot' --delete FROM tagmap WHERE TaggedBy = 'MisesBot' --delete FROM [Tag] WHERE tagId NOT IN (SELECT tagid FROM tagmap) --IF @User = 'SearchEngineBot' -- RETURN IF LEN(@Tag) < 2 RETURN SET @Tag = LTRIM(RTRIM(@Tag)) SET @Tag = REPLACE(@Tag , 'Econmics' , 'Economics') IF @Tag = 'mises' SET @Tag = 'Ludwig von Mises' IF @Tag = 'Rothbard' OR @Tag = 'Murray N. Rothbard' OR @Tag = 'Murray Rothbard' SET @Tag = 'Murray N. Rothbard' IF @TAG LIKE '%http://%' OR @TAG LIKE '%@%' OR @Tag LIKE '%PDF%' OR @Tag LIKE '%Acrobat %' OR @Tag LIKE '%Palm Download%' OR @Tag = 'audio' OR @Tag LIKE '%Distiller%' OR @Tag = 'mises.org' OR @Tag = 'and th' OR @Tag = 'Speech' OR @Tag = 'Unknown' OR @Tag LIKE '%mises institute%' OR @Tag = 'PrecisionScan' OR @Tag LIKE '%.qxd%' OR @Tag LIKE '%phendimetrazine%' OR @Tag LIKE '%www%' OR @Tag LIKE '%jews%' OR @Tag LIKE '%kidney%' OR @Tag LIKE '%.com%' RETURN -- Check for cursewords IF EXISTS ( SELECT * FROM MisesCommunity..cs_Censorship WHERE @Tag LIKE '%' + word + '%' ) RETURN -- Get Tag Id DECLARE @TagId int SET @TagId = ( SELECT TagId FROM Tag WHERE Tag = @Tag ) -- Check for duplicates IF EXISTS ( SELECT * FROM TagMap WHERE TagId = @TagId AND ObjectId = @Identifier AND TaggedBy = @User ) RETURN -- Add New Tag if Missing IF @TagId IS NULL BEGIN INSERT INTO Tag ( Tag ) VALUES ( @Tag ) SET @TagID = SCOPE_IDENTITY() END -- Insert tag mapping INSERT INTO TagMap ( TagId , ObjectId , TaggedBy ) VALUES ( @TagID , @Identifier , @User ) END GO /****** Object: StoredProcedure [dbo].[TagGetDocumentTags] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[TagGetDocumentTags] @Identifier uniqueidentifier AS BEGIN SELECT Tag.Tag , COUNT(Tag.TagId) AS 'Count' , Tag.TagId FROM Tag WITH ( NOLOCK ) INNER JOIN TagMap WITH ( NOLOCK ) ON Tag.TagId = TagMap.TagId WHERE ( TagMap.ObjectId = @Identifier ) AND [TaggedBy] <> 'SearchEngineBot' GROUP BY Tag.TagId , Tag.Tag END GO /****** Object: StoredProcedure [dbo].[TagGetTopTags] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[TagGetTopTags] @TagCount int = 60 AS BEGIN -- SELECT * FROM tag JOIN [TagMap] ON [Tag].[TagId] = [TagMap].[TagId] WHERE [TaggedBy] <> 'MisesBot' SET ROWCOUNT @TagCount SELECT TOP 1000 [Tag].TagId , tag , COUNT(TagMap.TagId) AS Count FROM [Tag] WITH ( NOLOCK ) JOIN [TagMap] WITH ( NOLOCK ) ON [Tag].[TagId] = [TagMap].[TagId] GROUP BY [Tag].[TagId] , Tag.[Tag] ORDER BY COUNT(TagMap.TagId) DESC END GO /****** Object: StoredProcedure [dbo].[TagGetStats] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[TagGetStats] AS BEGIN SELECT ( SELECT COUNT(1) FROM tag ) AS UniqueTags , ( SELECT COUNT(1) FROM TagMap WHERE TaggedBy = 'MisesBot' ) AS BotTags , ( SELECT COUNT(DISTINCT ObjectId) FROM TagMap ) AS TotalTaggedDocs , ( SELECT COUNT(DISTINCT ObjectId) FROM TagMap WHERE TaggedBy = 'MisesBot' ) AS BotTaggedDocs , ( SELECT COUNT(DISTINCT ObjectId) FROM TagMap WHERE TaggedBy <> 'MisesBot' ) AS UserTaggedDocs , ( SELECT COUNT(1) FROM TagMap WHERE TaggedDate > DATEADD(hour , -24 , GETDATE()) ) AS TodaysTagging , ( SELECT COUNT(DISTINCT TagId) FROM TagMap WHERE TaggedDate > DATEADD(hour , -24 , GETDATE()) ) AS TodaysTags SELECT TOP 10 TaggedBy , COUNT(1) AS Count FROM TagMap WHERE TaggedDate > DATEADD(hour , -24 , GETDATE())-- AND TaggedBy NOT LIKE '%.%' GROUP BY TaggedBy ORDER BY COUNT DESC SELECT TOP 10 TaggedBy , COUNT(1) AS Count FROM TagMap --WHERE TaggedBy NOT LIKE '%.%' GROUP BY TaggedBy ORDER BY COUNT DESC SELECT TOP 25 Tag , COUNT(1) AS 'Count' FROM TagMap JOIN Tag ON Tag.TagId = TagMap.TagId WHERE TaggedDate > DATEADD(hour , -24 , GETDATE()) GROUP BY Tag ORDER BY COUNT DESC END GO /****** Object: StoredProcedure [dbo].[TagGetRelatedTags] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: Get Related Tags -- ============================================= CREATE PROCEDURE [dbo].[TagGetRelatedTags] @Tag varchar(70) AS BEGIN DECLARE @TagId int SET @TagId = ( SELECT TOP 1 TagId FROM Tag WHERE Tag = @Tag ) SELECT TOP 30 Tag.Tag , COUNT(TagMap.TagId) AS 'Count' FROM TagMap INNER JOIN Tag ON TagMap.TagId = Tag.TagId WHERE [TagMap].TagId <> @TagId AND ( TagMap.TagId IN ( SELECT DISTINCT TagId FROM TagMap WHERE ( ObjectId IN ( SELECT ObjectId FROM TagMap WHERE ( TagId = @TagId ) ) ) ) ) GROUP BY TagMap.TagId , Tag.Tag ORDER BY 'Count' DESC END GO /****** Object: StoredProcedure [dbo].[TagGetUserTags] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[TagGetUserTags] @User varchar(100) , @User2 varchar(100) = '' AS BEGIN SELECT TOP 5000 Tag.Tag , COUNT(Tag.TagId) AS 'Count' , Tag.TagId FROM Tag INNER JOIN TagMap ON Tag.TagId = TagMap.TagId WHERE ( TagMap.TaggedBy = @User ) OR ( TagMap.TaggedBy = @User2 ) GROUP BY Tag.TagId , Tag.Tag END GO /****** Object: StoredProcedure [dbo].[TagGetUserTaggedDocuments] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[TagGetUserTaggedDocuments] @User varchar(100) , @User2 varchar(100) = '' AS BEGIN -- Replace IP address with singed in user Id --IF @User2 <> '' --UPDATE TagMap SET TaggedBy = @User WHERE TaggedBy =@User2 -- User Tagged Documents SELECT TOP 1000 Documents.GUID , Documents.[Title] , CASE WHEN( SELECT COUNT(1) FROM MediaAlternateFormat mf WHERE mf.DocumentId = Documents.DocumentId AND [MediaTypeId] IN ( 1 , 2 ) ) > 0 THEN 'Media' WHEN( SELECT COUNT(1) FROM MediaAlternateFormat mf WHERE mf.DocumentId = Documents.DocumentId AND [MediaTypeId] = 3 ) > 0 THEN 'PDF' ELSE 'Content' END AS 'Type' , [Documents].[DocumentId] AS Id , '' AS URL , Tag.[Tag] FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] WITH ( NOLOCK ) ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN Documents WITH ( NOLOCK ) ON Documents.[GUID] = [TagMap].[ObjectId] WHERE [Documents].[Display] = 1 AND [TagMap].TaggedBy = @User UNION ALL SELECT TOP 1000 Page.[GUID] , Page.[pageTitle] AS Title , 'Content' AS 'Type' , [Page].[PageId] AS Id , '/' + page.[folder] + page.[pageName] AS URL , Tag.[Tag] FROM TagMap WITH ( NOLOCK ) INNER JOIN Tag ON TagMap.TagId = Tag.TagId INNER JOIN [Page] ON [Page].[GUID] = [TagMap].[ObjectId] WHERE [TagMap].TaggedBy = @User UNION ALL SELECT TOP 1000 DailyArticles.GUID , DailyArticles.[Title] , 'DailyArticle' AS TYPE , [DailyArticles].[ArticleId] AS Id , '' AS URL , Tag.[Tag] FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN DailyArticles ON DailyArticles.[GUID] = [TagMap].[ObjectId] WHERE [TagMap].TaggedBy = @User UNION ALL SELECT [GUID] , NAME , 'Product' AS 'Type' , products.ProductId AS Id , '' AS URL , Tag.[Tag] FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN AbleCommerce..ac_Products products ON TagMap.ObjectId = products.GUID WHERE [TagMap].TaggedBy = @User ORDER BY Id DESC -- User Tag Map --SELECT -- Tag.Tag , -- COUNT(Tag.TagId) AS 'Count' , -- Tag.TagId --FROM -- Tag INNER JOIN TagMap --ON Tag.TagId = TagMap.TagId --WHERE -- ( TagMap.TaggedBy = @User ) OR ( TagMap.TaggedBy = @User2 ) --GROUP BY -- Tag.TagId , -- Tag.Tag END GO /****** Object: StoredProcedure [dbo].[TagGetRelatedDocuments] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --TagGetDocumentsWithTags 'Bastiat' --SELECT * FROM [SplitCVS] ('Bastiat') -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[TagGetRelatedDocuments] @DocumentId uniqueidentifier = NULL , @ArticleId int = 0 AS BEGIN IF @DocumentId IS NULL SET @DocumentId = ( SELECT [GUID] FROM [DailyArticles] WHERE [DailyArticles].[ArticleId] = @ArticleId ) --CREATE TABLE #Tags(TagId int) --INSERT INTO [#Tags] ([TagId]) (SELECT TagId FROM [TagMap] WHERE [TagMap].[ObjectId] = @DocumentId) SELECT TOP 5 Documents.GUID , Documents.[Title] , CASE WHEN( SELECT COUNT(1) FROM MediaAlternateFormat mf WHERE mf.DocumentId = Documents.DocumentId AND [MediaTypeId] IN ( 1 , 2 ) ) > 0 THEN 'Media' WHEN( SELECT COUNT(1) FROM MediaAlternateFormat mf WHERE mf.DocumentId = Documents.DocumentId AND [MediaTypeId] = 3 ) > 0 THEN 'PDF' ELSE 'Content' END AS 'Type' , [Documents].[DocumentId] AS Id , '' AS URL FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] WITH ( NOLOCK ) ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN Documents WITH ( NOLOCK ) ON Documents.[GUID] = [TagMap].[ObjectId] WHERE [Documents].[Display] = 1 AND [Tag].[TagId] IN ( SELECT TagId FROM [TagMap] WHERE [TagMap].[ObjectId] = @DocumentId ) UNION ALL SELECT TOP 4 Page.[GUID] , Page.[pageTitle] AS Title , 'Content' AS 'Type' , [Page].[PageId] AS Id , '/' + page.[folder] + page.[pageName] AS URL FROM TagMap WITH ( NOLOCK ) INNER JOIN Tag WITH ( NOLOCK ) ON TagMap.TagId = Tag.TagId INNER JOIN [Page] ON [Page].[GUID] = [TagMap].[ObjectId] WHERE ( Tag.TagId IN ( SELECT TagId FROM [TagMap] WHERE [TagMap].[ObjectId] = @DocumentId ) ) UNION ALL SELECT TOP 4 DailyArticles.GUID , DailyArticles.[Title] , 'DailyArticle' AS TYPE , [DailyArticles].[ArticleId] AS Id , '' AS URL FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] WITH ( NOLOCK ) ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN DailyArticles ON DailyArticles.[GUID] = [TagMap].[ObjectId] WHERE TagMap.Objectid <> @DocumentId AND ( Tag.TagId IN ( SELECT TagId FROM [TagMap] WHERE [TagMap].[ObjectId] = @DocumentId ) ) UNION ALL SELECT TOP 4 [GUID] , title , PeriodicalsView.[Journal] AS 'Type' , 0 AS Id , [PeriodicalsView].[URL] AS 'URL' FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] WITH ( NOLOCK ) ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN PeriodicalsView ON PeriodicalsView.GUID = TagMap.ObjectId WHERE ( Tag.TagId IN ( SELECT TagId FROM [TagMap] WHERE [TagMap].[ObjectId] = @DocumentId ) ) UNION ALL SELECT TOP 4 [GUID] , NAME , 'Product' AS 'Type' , products.ProductId AS Id , '' AS URL FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] WITH ( NOLOCK ) ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN AbleCommerce..ac_Products products ON TagMap.ObjectId = products.GUID WHERE ( Tag.TagId IN ( SELECT TagId FROM [TagMap] WHERE [TagMap].[ObjectId] = @DocumentId ) ) ORDER BY Id DESC END GO /****** Object: StoredProcedure [dbo].[TagGetDocumentsWithTags] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --TagGetDocumentsWithTags 'Bastiat' --SELECT * FROM [SplitCVS] ('Bastiat') -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[TagGetDocumentsWithTags] @Tags varchar(max) AS BEGIN SELECT TOP 500 Documents.GUID , Documents.[Title] , CASE WHEN( SELECT COUNT(1) FROM MediaAlternateFormat mf WHERE mf.DocumentId = Documents.DocumentId AND [MediaTypeId] IN ( 1 , 2 ) ) > 0 THEN 'Media' WHEN( SELECT COUNT(1) FROM MediaAlternateFormat mf WHERE mf.DocumentId = Documents.DocumentId AND [MediaTypeId] = 3 ) > 0 THEN 'PDF' ELSE 'Content' END AS 'Type' , [Documents].[DocumentId] AS Id , '' AS URL FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] WITH ( NOLOCK ) ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN Documents WITH ( NOLOCK ) ON Documents.[GUID] = [TagMap].[ObjectId] WHERE ( Tag.Tag IN ( SELECT * FROM [SplitCVS](@Tags) ) ) AND [Documents].[Display] = 1 UNION ALL SELECT TOP 500 Page.[GUID] , Page.[pageTitle] AS Title , 'Content' AS 'Type' , [Page].[PageId] AS Id , page.[folder] + '/' + page.[pageName] AS URL FROM TagMap WITH ( NOLOCK ) INNER JOIN Tag ON TagMap.TagId = Tag.TagId INNER JOIN [Page] WITH ( NOLOCK ) ON [Page].[GUID] = [TagMap].[ObjectId] WHERE ( Tag.Tag IN ( SELECT * FROM [SplitCVS](@Tags) ) ) UNION ALL SELECT DISTINCT TOP 500 DailyArticles.GUID , DailyArticles.[Title] , 'DailyArticle' AS 'Type' , [DailyArticles].[ArticleId] AS Id , '' AS URL FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] WITH ( NOLOCK ) ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN DailyArticles WITH ( NOLOCK ) ON DailyArticles.[GUID] = [TagMap].[ObjectId] WHERE ( Tag.Tag IN ( SELECT * FROM [SplitCVS](@Tags) ) ) UNION ALL SELECT DISTINCT TOP 500 [GUID] , title , PeriodicalsView.[Journal] AS 'Type' , 0 AS Id , [PeriodicalsView].[URL] AS 'URL' FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] WITH ( NOLOCK ) ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN PeriodicalsView WITH ( NOLOCK ) ON PeriodicalsView.GUID = TagMap.ObjectId WHERE ( Tag.Tag IN ( SELECT * FROM [SplitCVS](@Tags) ) ) -- OR [Tag].[Tag] LIKE '%' + @Tags + '%' UNION ALL SELECT [GUID] , NAME , 'Product' AS 'Type' , products.ProductId AS Id , '' AS URL FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] WITH ( NOLOCK ) ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN AbleCommerce..[ac_Products] products WITH ( NOLOCK ) ON TagMap.ObjectId = products.GUID WHERE ( Tag.Tag IN ( SELECT * FROM [SplitCVS](@Tags) ) ) END GO /****** Object: StoredProcedure [dbo].[TagGetDocumentsWithTag] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --TagGetDocumentsWithTags 'Bastiat' --SELECT * FROM [SplitCVS] ('Bastiat') -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[TagGetDocumentsWithTag] @Tag varchar(75) AS BEGIN SELECT TOP 200 Documents.GUID , Documents.[Title] , CASE WHEN( SELECT COUNT(1) FROM MediaAlternateFormat mf WHERE mf.DocumentId = Documents.DocumentId AND [MediaTypeId] IN ( 1 , 2 ) ) > 0 THEN 'Media' WHEN( SELECT COUNT(1) FROM MediaAlternateFormat mf WITH ( NOLOCK ) WHERE mf.DocumentId = Documents.DocumentId AND [MediaTypeId] = 3 ) > 0 THEN 'PDF' ELSE 'Content' END AS 'Type' , [Documents].[DocumentId] AS Id , '' AS URL FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] WITH ( NOLOCK ) ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN Documents WITH ( NOLOCK ) ON Documents.[GUID] = [TagMap].[ObjectId] WHERE Tag.Tag = @Tag AND [Documents].[Display] = 1 UNION ALL SELECT TOP 200 Page.[GUID] , Page.[pageTitle] AS Title , 'Content' AS 'Type' , [Page].[PageId] AS Id , page.[folder] + '/' + page.[pageName] AS URL FROM TagMap WITH ( NOLOCK ) INNER JOIN Tag WITH ( NOLOCK ) ON TagMap.TagId = Tag.TagId INNER JOIN [Page] WITH ( NOLOCK ) ON [Page].[GUID] = [TagMap].[ObjectId] WHERE Tag.Tag = @Tag UNION ALL SELECT DISTINCT TOP 200 DailyArticles.GUID , DailyArticles.[Title] , 'DailyArticle' AS 'Type' , [DailyArticles].[ArticleId] AS Id , '' AS URL FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] WITH ( NOLOCK ) ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN DailyArticles WITH ( NOLOCK ) ON DailyArticles.[GUID] = [TagMap].[ObjectId] WHERE Tag.Tag = @Tag UNION ALL SELECT DISTINCT TOP 200 [GUID] , title , PeriodicalsView.[Journal] AS 'Type' , 0 AS Id , [PeriodicalsView].[URL] AS 'URL' FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] WITH ( NOLOCK ) ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN PeriodicalsView WITH ( NOLOCK ) ON PeriodicalsView.GUID = TagMap.ObjectId WHERE Tag.Tag = @Tag -- OR [Tag].[Tag] LIKE '%' + @Tags + '%' UNION ALL SELECT [GUID] , NAME , 'Product' AS 'Type' , products.ProductId AS Id , '' AS URL FROM TagMap WITH ( NOLOCK ) INNER JOIN [Tag] WITH ( NOLOCK ) ON [TagMap].[TagId] = [Tag].[TagId] INNER JOIN AbleCommerce..[ac_Products] products WITH ( NOLOCK ) ON TagMap.ObjectId = products.GUID WHERE Tag.Tag = @Tag END GO /****** Object: StoredProcedure [dbo].[SubjectsTopSubjects] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Name -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[SubjectsTopSubjects] AS BEGIN SELECT DocumentSubjectLink.SubjectID , ShortSubject , COUNT(1) AS 'Count' FROM DocumentSubjectLink JOIN DocumentSubjects ON DocumentSubjectLink.SubjectID = DocumentSubjects.SubjectId WHERE DocumentSubjects.Visible = 1 GROUP BY DocumentSubjectLink.SubjectID , ShortSubject ORDER BY 'Count' DESC END GO /****** Object: StoredProcedure [dbo].[spUpdateArticle] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spUpdateArticle] ( @ArticleId int , @Title varchar(255) , @Description varchar(1500) , @ArticleText varchar(max) , @ShowArticle bit , @AuthorId int = 0 , @CoAuthorId int = 0 , @DisplayOrder int = 0 , @PhotoHeight int = 0 , @PhotoURL varchar(255) = NULL , @DatePosted smalldatetime = GETDATE , @EditBy varchar(75) = NULL ) AS UPDATE DailyArticles SET Title = @Title , [Description] = @Description , ArticleText = @ArticleText , DatePosted = @Dateposted , ShowArticle = @ShowArticle , CoAuthorId = @CoAuthorId , AuthorId = @AuthorId , DisplayOrder = @DisplayOrder , PhotoURL = @PhotoURL , PhotoHeight = @PhotoHeight , EditDate = GETDATE() , EditBy = @EditBy WHERE ( ArticleId = @ArticleId ) GO /****** Object: StoredProcedure [dbo].[spGetAuthorList] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[spGetAuthorList] AS SET CONCAT_NULL_YIELDS_NULL OFF SELECT DISTINCT DailyArticles.AuthorId , da.authorLast , 0 AS monthnum , '' AS month , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS AuthorName , ( SELECT TOP 1 title FROM DailyArticles a WHERE a.AuthorId = DailyArticles.AuthorId ORDER BY dateposted DESC ) AS title , ( SELECT TOP 1 ArticleId FROM DailyArticles a WHERE a.AuthorId = DailyArticles.AuthorId ORDER BY dateposted DESC ) AS ArticleId , ( SELECT TOP 1 dateposted FROM DailyArticles a WHERE a.AuthorId = DailyArticles.AuthorId ORDER BY dateposted DESC ) AS dateposted FROM DailyArticles JOIN DocumentAuthors da ON da.AuthorId = DailyArticles.AuthorId WHERE ( ShowArticle = 1 ) ORDER BY da.AuthorLast GO /****** Object: StoredProcedure [dbo].[spAddNewDocumentSubjectLink] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spAddNewDocumentSubjectLink] @DocumentId int , @SubjectId int AS INSERT INTO DocumentSubjectLink ( SubjectId , DocumentId ) VALUES ( @SubjectId , @DocumentId ) GO /****** Object: StoredProcedure [dbo].[spAddNewArticle] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spAddNewArticle] ( @Title varchar(255) , @Description text , --@AuthorFirst varchar(50)='', --@AuthorLast varchar(50)='', @ArticleText text , @ShowArticle bit , @AuthorId int = 0 , @CoAuthorId int = 0 , @DisplayOrder int = 10 , @PhotoHeight int = 0 , @PhotoURL varchar(255) = NULL , @EditBy varchar(75) = NULL , @DatePosted smalldatetime = GETDATE ) AS INSERT INTO DailyArticles ( Title , Description , ArticleText , ShowArticle , DisplayOrder , AuthorId , CoAuthorId , PhotoURL , DatePosted , PhotoHeight , EditBy ) VALUES ( @title , @description , @ArticleText , @ShowArticle , @DisplayOrder , @AuthorId , @CoAuthorId , @PhotoURL , @DatePosted , @PhotoHeight , @EditBy ) SELECT SCOPE_IDENTITY() GO /****** Object: StoredProcedure [dbo].[SearchSite] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: David V -- Create date: 1/30/2006 -- Description: -- ============================================= CREATE PROCEDURE [dbo].[SearchSite] -- Add the parameters for the stored procedure here @terms varchar(100) AS BEGIN SET CONCAT_NULL_YIELDS_NULL OFF SELECT 'Daily Articles' 'SearchType' , '/daily/{0}' , ArticleId [IDfield] , Title + ' by ' + da.AuthorFirst + ' ' + da.AuthorMiddle + ' ' + da.AuthorLast AS [Title] FROM dailyarticles WITH ( NOLOCK ) JOIN DocumentAuthors da ON da.AuthorId = dailyarticles.AuthorId WHERE ShowArticle = 1 AND ( ( Title LIKE '%' + @terms + '%' ) OR ( Description LIKE '%' + @terms + '%' ) OR FREETEXT ( ArticleText,@terms ) -- OR ( ArticleText LIKE '%' + @terms + '%' ) ) ORDER BY title SELECT 'Literature Document' 'SearchType' , '/resources/{0}' , DocumentId [IDfield] , Title [Title] FROM Documents WITH ( NOLOCK ) WHERE display = 1 AND ( ( title LIKE '%' + @terms + '%' ) OR ( PubInfo LIKE '%' + @terms + '%' ) OR FREETEXT ( [Documents].[GuideContent],@terms ) ) ORDER BY title SELECT 'Mises Store Products' 'SearchType' , '/store/Product.aspx?ProductId={0}' , ProductId [IDfield] , [Name] [Title] FROM [AbleCommerce]..ac_Products WITH ( NOLOCK ) WHERE ( ( name LIKE '%' + @terms + '%' ) OR ( description LIKE '%' + @terms + '%' ) ) ORDER BY [name] SELECT 'Mises Institute Events' 'SearchType' , '/events/{0}' , EventId [IDfield] , Title [Title] FROM calendar WITH ( NOLOCK ) WHERE display = 1 AND ( ( Description LIKE '%' + @terms + '%' ) OR ( Title LIKE '%' + @terms + '%' ) OR ( Location LIKE '%' + @terms + '%' ) ) ORDER BY Title SELECT 'Free Market articles' 'SearchType' , '/freemarket_detail.aspx?control={0}' , control [IDfield] , title [Title] FROM freemarket WITH ( NOLOCK ) WHERE ( ( title LIKE '%' + @terms + '%' ) OR ( authorfirst LIKE '%' + @terms + '%' ) OR ( authorlast LIKE '%' + @terms + '%' ) OR ( authorfirst2 LIKE '%' + @terms + '%' ) OR ( authorlast2 LIKE '%' + @terms + '%' ) OR ( subject1 LIKE '%' + @terms + '%' ) OR ( subject2 LIKE '%' + @terms + '%' ) OR ( subject3 LIKE '%' + @terms + '%' ) OR ( body LIKE '%' + @terms + '%' ) OR ( articledate LIKE '%' + @terms + '%' ) ) ORDER BY title SELECT 'Mises Review articles' 'SearchType' , '/misesreview_detail.aspx?control={0}' , control [IDfield] , title [Title] FROM misesreview WITH ( NOLOCK ) WHERE ( ( title LIKE '%' + @terms + '%' ) OR ( authorfirst LIKE '%' + @terms + '%' ) OR ( authorlast LIKE '%' + @terms + '%' ) OR ( authorfirst2 LIKE '%' + @terms + '%' ) OR ( authorlast2 LIKE '%' + @terms + '%' ) OR ( authorfirst3 LIKE '%' + @terms + '%' ) OR ( authorlast3 LIKE '%' + @terms + '%' ) OR ( body LIKE '%' + @terms + '%' ) ) ORDER BY title SELECT 'Journal of Libertarian Studies articles' 'SearchType' , '{0}' , jls.[link] [IDfield] , title [Title] FROM jls WITH ( NOLOCK ) WHERE ( title LIKE '%' + @terms + '%' ) OR ( authorFirst1 LIKE '%' + @terms + '%' ) OR ( authorLast1 LIKE '%' + @terms + '%' ) OR ( authorFirst2 LIKE '%' + @terms + '%' ) OR ( authorLast2 LIKE '%' + @terms + '%' ) ORDER BY title SELECT 'Quarterly Journal of Austrian Economics articles' 'SearchType' , '/journals/qjae/qjae' + CAST(volume AS varchar(max)) + '_' + CAST(number AS varchar(max)) + '_' + CAST(ArticleNum AS varchar(max)) + '.pdf' , '' [IDfield] , title [Title] FROM qjaeDB WITH ( NOLOCK ) WHERE ( title LIKE '%' + @terms + '%' ) OR ( authorFirst1 LIKE '%' + @terms + '%' ) OR ( authorLast1 LIKE '%' + @terms + '%' ) OR ( authorFirst2 LIKE '%' + @terms + '%' ) OR ( authorLast2 LIKE '%' + @terms + '%' ) ORDER BY title SELECT 'Austrian Economics Newsletter articles' 'SearchType' , '/journals/aen/aen' + CAST(volume AS varchar(max)) + '_' + CAST(number AS varchar(max)) + '_' + CAST(ArticleNum AS varchar(max)) + fileType , '' [IDfield] , title [Title] FROM aenDB WITH ( NOLOCK ) WHERE ( title LIKE '%' + @terms + '%' ) OR ( authorFirst1 LIKE '%' + @terms + '%' ) OR ( authorLast1 LIKE '%' + @terms + '%' ) OR ( authorFirst2 LIKE '%' + @terms + '%' ) OR ( authorLast2 LIKE '%' + @terms + '%' ) ORDER BY title SELECT 'Review of Austrian Economics articles' 'SearchType' , '/journals/rae/pdf/rae' + CAST(volume AS varchar(max)) + '_' + CAST(number AS varchar(max)) + '_' + CAST(ArticleNum AS varchar(max)) + '.pdf' , '' [IDfield] , title [Title] FROM raeDB WITH ( NOLOCK ) WHERE ( title LIKE '%' + @terms + '%' ) OR ( authorFirst1 LIKE '%' + @terms + '%' ) OR ( authorLast1 LIKE '%' + @terms + '%' ) OR ( authorFirst2 LIKE '%' + @terms + '%' ) OR ( authorLast2 LIKE '%' + @terms + '%' ) ORDER BY title SELECT 'Other Pages' 'SearchType' , '' , '/' + folder + '/' + pagename [IDfield] , pagetitle [Title] FROM page WITH ( NOLOCK ) WHERE ( pageName LIKE '%' + @terms + '%' ) OR ( pageTitle LIKE '%' + @terms + '%' ) -- OR ( content LIKE '%' + @terms + '%' ) OR FREETEXT ( [content],@terms ) OR ( metaKeywords LIKE '%' + @terms + '%' ) OR ( metaDescription LIKE '%' + @terms + '%' ) ORDER BY pageName SELECT 'Ward Library books ' 'SearchType' , '/book.aspx?Id={0}' , control [IDfield] , Title [Title] FROM wardlibrary WITH ( NOLOCK ) WHERE ( ( Title LIKE '%' + @terms + '%' ) OR ( publisher_info LIKE '%' + @terms + '%' ) OR ( comments LIKE '%' + @terms + '%' ) ) ORDER BY title SELECT 'Faculty Biographies' 'SearchType' , --'/fellow.aspx?Id={0}', web , '' [IDfield] , --control [IDfield], [name] [Title] FROM faculty WITH ( NOLOCK ) WHERE ( display = 1 ) AND ( ( name LIKE '%' + @terms + '%' ) OR ( school LIKE '%' + @terms + '%' ) OR ( email LIKE '%' + @terms + '%' ) OR ( jobtitle LIKE '%' + @terms + '%' ) ) ORDER BY name -- SELECT 'Misc Pages' 'SearchType', -- '', -- 'http://www.mises.org' + folder + '/' + pagename [IDfield], -- pagetitle [Title] -- FROM pages WITH (NOLOCK) -- WHERE ( pageName LIKE '%' + @terms + '%' ) -- OR ( pageTitle LIKE '%' + @terms + '%' ) -- OR ( content LIKE '%' + @terms + '%' ) -- OR ( metaKeywords LIKE '%' + @terms + '%' ) -- OR ( metaDescription LIKE '%' + @terms + '%' ) -- ORDER BY pageName END GO /****** Object: StoredProcedure [dbo].[RevisionGetRecent] Script Date: 09/24/2011 22:43:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[RevisionGetRecent] AS BEGIN SET NOCOUNT ON ; SELECT DISTINCT TOP 30 ArticleId , Title , dbo.DailyArticles.EditDate , EditBy FROM dbo.DailyArticles INNER JOIN dbo.Revision ON dbo.DailyArticles.GUID = dbo.Revision.DocumentGUID WHERE dbo.DailyArticles.ShowArticle = 0 ORDER BY dbo.DailyArticles.EditDate DESC END GO /****** Object: StoredProcedure [dbo].[ReplaceGuideAuthor] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: David V -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[ReplaceGuideAuthor] -- Add the parameters for the stored procedure here @OldAuthorId int , @NewAuthorId int AS BEGIN --select * from DocumentAuthors WHERE AuthorLast + AuthorFirst IN --( --SELECT AuthorLast + AuthorFirst --FROM DocumentAuthors --GROUP BY AuthorLast + AuthorFirst --HAVING ( COUNT(AuthorLast + AuthorFirst) > 1 ) --) oRDER By AuthorLast UPDATE Documents SET Author1 = @NewAuthorId WHERE Author1 = @OldAuthorId UPDATE Documents SET Author2 = @NewAuthorId WHERE Author2 = @OldAuthorId UPDATE DailyArticles SET AuthorId = @NewAuthorId WHERE AuthorId = @OldAuthorId UPDATE DailyArticles SET CoAuthorId = @NewAuthorId WHERE CoAuthorId = @OldAuthorId DELETE FROM DocumentAuthors WHERE ( AuthorId = @OldAuthorId ) AND @OldAuthorId NOT IN ( SELECT Author1 FROM Documents WHERE Author1 = @OldAuthorId ) END GO /****** Object: StoredProcedure [dbo].[ReplaceDocumentText] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[ReplaceDocumentText] @find varchar(500) , @replace varchar(500) , @patfind varchar(500) = '' AS BEGIN SELECT @patfind = '%' + @find + '%' UPDATE DailyArticles SET ArticleText = STUFF(ArticleText , PATINDEX(@patfind , ArticleText) , DATALENGTH (@find) , @replace) WHERE ArticleText LIKE @patfind ---- Study Guide -- -- UPDATE Documents -- SET GuideContent = STUFF(GuideContent, -- PATINDEX(@patfind, GuideContent), -- DATALENGTH(@find), @replace) -- WHERE GuideContent LIKE @patfind -- -- --Pages -- -- UPDATE Page -- SET [Content] = STUFF([Content], PATINDEX(@patfind, [Content]), -- DATALENGTH(@find), @replace) -- WHERE [Content] LIKE @patfind --AND Page.[folder] LIKE '%humanaction%' -- ---- Media URL -- UPDATE MediaAlternateFormat -- SET [URL] = STUFF([URL], PATINDEX(@patfind, [URL]), -- DATALENGTH(@find), @replace) -- WHERE [URL] LIKE @patfind -- ---- Product Descriptions -- UPDATE [AbleCommerce]..[ac_Products] -- SET [AbleCommerce]..[ac_Products].[Description] = STUFF([AbleCommerce]..[ac_Products].[Description], PATINDEX(@patfind, [AbleCommerce]..[ac_Products].[Description]), -- DATALENGTH(@find), @replace) -- WHERE [AbleCommerce]..[ac_Products].[Description] LIKE @patfind -- END GO /****** Object: StoredProcedure [dbo].[QuizGetDetails] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[QuizGetDetails] @QuizId int AS BEGIN SET NOCOUNT ON ; SELECT * FROM dbo.QuizType WHERE QuizID = @QuizId SELECT * FROM dbo.QuizQuestion WHERE QuizID = @QuizId ORDER BY SortOrder SELECT dbo.QuizAnswer.* FROM dbo.QuizAnswer JOIN dbo.QuizQuestion ON dbo.QuizAnswer.QuestionID = dbo.QuizQuestion.QuestionID WHERE dbo.QuizQuestion.QuizID = @QuizId END GO /****** Object: StoredProcedure [dbo].[PageDelete] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[PageDelete] @GUID uniqueidentifier AS BEGIN DELETE FROM PAGE WHERE GUID = @GUID DELETE FROM [DailyArticles] WHERE [GUID] = @GUID END GO /****** Object: StoredProcedure [dbo].[MediaTopSubjects] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Name -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MediaTopSubjects] AS BEGIN SELECT DocumentSubjectLink.SubjectID , ShortSubject , COUNT(1) AS 'Count' FROM DocumentSubjectLink INNER JOIN MediaAlternateFormat ON DocumentSubjectLink.DocumentId = MediaAlternateFormat.DocumentId JOIN DocumentSubjects ON DocumentSubjectLink.SubjectID = DocumentSubjects.SubjectId WHERE DocumentSubjects.Visible = 1 GROUP BY DocumentSubjectLink.SubjectID , ShortSubject ORDER BY 'Count' DESC END GO /****** Object: StoredProcedure [dbo].[MediaTopFullSubjects] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Name -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[MediaTopFullSubjects] AS BEGIN SELECT TOP 15 DocumentSubjectLink.SubjectID , [Subject] , COUNT(1) AS 'Count' FROM DocumentSubjectLink INNER JOIN MediaAlternateFormat ON DocumentSubjectLink.DocumentId = MediaAlternateFormat.DocumentId JOIN DocumentSubjects ON DocumentSubjectLink.SubjectID = DocumentSubjects.SubjectId WHERE DocumentSubjects.Visible = 1 GROUP BY DocumentSubjectLink.SubjectID , Subject ORDER BY 'Count' DESC END GO /****** Object: StoredProcedure [dbo].[MediaGetCategoryContent] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[MediaGetCategoryContent] @CategoryId int = 0 , @SubjectId int = 0 AS SET CONCAT_NULL_YIELDS_NULL OFF --IF @CategoryId = -1 OR @CategoryId = 0 EXEC dbo.MediaGetSubCategories @CategoryId IF @CategoryId >= 0 BEGIN IF @CategoryId = 210 -- Get Recent Media SELECT TOP 30 Documents.DocumentId , Documents.Title , Documents.CategoryId , Documents.CreateDate , Documents.metaDescription AS 'Description' , media.DocumentId , media.MediaTypeId , media.URL , media.MediaId , Documents.EditDate , media.CreateDate , DocumentAuthors.AuthorId , DocumentAuthors2.AuthorId AS CoAuthorId , DocumentMediaType.MediaType , DocumentMediaType.MediaIconPath , -- MediaCategory.[Category] , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , DocumentAuthors2.AuthorFirst + ' ' + DocumentAuthors2.AuthorMiddle + ' ' + DocumentAuthors2.AuthorLast AS CoAuthor , DocumentAuthors.[AuthorLast] AS LastName , DocumentAuthors2.[AuthorLast] AS CoLastName , CASE WHEN Documents.metaImage IS NULL THEN 0 WHEN Documents.metaImage IS NOT NULL THEN 1 END AS HasImage FROM dbo.Documents LEFT JOIN dbo.MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN dbo.DocumentAuthors ON Documents.Author1 = DocumentAuthors.AuthorId LEFT JOIN dbo.DocumentMediaType ON DocumentMediaType.MediaTypeId = media.MediaTypeId LEFT JOIN dbo.MediaCategory ON Documents.CategoryId = MediaCategory.CategoryId LEFT JOIN dbo.DocumentAuthors AS DocumentAuthors2 ON Documents.Author2 = DocumentAuthors2.AuthorId WHERE DocumentMediaType.IsMedia = 1 -- AND Documents.CategoryId = @CategoryId AND Documents.Display = 1 AND media.Display = 1 ORDER BY Documents.CreateDate DESC ELSE SELECT Documents.DocumentId , Documents.Title , Documents.CategoryId , Documents.CreateDate , Documents.metaDescription AS 'Description' , media.DocumentId , media.MediaTypeId , media.URL , media.MediaId , Documents.EditDate , media.CreateDate , DocumentAuthors.AuthorId , DocumentAuthors2.AuthorId AS CoAuthorId , DocumentMediaType.MediaType , DocumentMediaType.MediaIconPath , -- MediaCategory.[Category] , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , DocumentAuthors2.AuthorFirst + ' ' + DocumentAuthors2.AuthorMiddle + ' ' + DocumentAuthors2.AuthorLast AS CoAuthor , DocumentAuthors.[AuthorLast] AS LastName , DocumentAuthors2.[AuthorLast] AS CoLastName , CASE WHEN Documents.metaImage IS NULL THEN 0 WHEN Documents.metaImage IS NOT NULL THEN 1 END AS HasImage FROM dbo.Documents LEFT JOIN dbo.MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN dbo.DocumentAuthors ON Documents.Author1 = DocumentAuthors.AuthorId LEFT JOIN dbo.DocumentMediaType ON DocumentMediaType.MediaTypeId = media.MediaTypeId LEFT JOIN dbo.MediaCategory ON Documents.CategoryId = MediaCategory.CategoryId LEFT JOIN dbo.DocumentAuthors AS DocumentAuthors2 ON Documents.Author2 = DocumentAuthors2.AuthorId WHERE DocumentMediaType.IsMedia = 1 AND Documents.CategoryId = @CategoryId AND Documents.Display = 1 AND media.Display = 1 ORDER BY Documents.Title SELECT mc.CategoryId, mc.ParentCategory, mc.Category, mc.CategoryImage, parent.Category AS ParentCategoryName, parent.CategoryId AS ParentCategoryId FROM dbo.MediaCategory mc LEFT JOIN dbo.MediaCategory parent ON parent.CategoryId = mc.ParentCategory WHERE mc.CategoryId = @CategoryId END ELSE BEGIN IF @SubjectId > 0 SELECT Documents.DocumentId , Documents.Title , Documents.CategoryId , Documents.CreateDate , Documents.metaDescription AS 'Description' , media.DocumentId , media.MediaTypeId , media.URL , media.MediaId , Documents.EditDate , media.CreateDate , DocumentAuthors.AuthorId , DocumentAuthors2.AuthorId AS CoAuthorId , DocumentMediaType.MediaType , DocumentMediaType.MediaIconPath , -- MediaCategory.[Category] , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , DocumentAuthors2.AuthorFirst + ' ' + DocumentAuthors2.AuthorMiddle + ' ' + DocumentAuthors2.AuthorLast AS CoAuthor , DocumentAuthors.[AuthorLast] AS LastName , DocumentAuthors2.[AuthorLast] AS CoLastName , CASE WHEN Documents.metaImage IS NULL THEN 0 WHEN Documents.metaImage IS NOT NULL THEN 1 END AS HasImage FROM dbo.Documents LEFT JOIN dbo.MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN dbo.DocumentAuthors ON Documents.Author1 = DocumentAuthors.AuthorId LEFT JOIN dbo.DocumentMediaType ON DocumentMediaType.MediaTypeId = media.MediaTypeId LEFT JOIN dbo.MediaCategory ON Documents.CategoryId = MediaCategory.CategoryId LEFT JOIN dbo.DocumentAuthors AS DocumentAuthors2 ON Documents.Author2 = DocumentAuthors2.AuthorId WHERE DocumentMediaType.MediaTypeId IN ( 1 , 2 ) AND Documents.Display = 1 AND media.Display = 1 AND Documents.DocumentId IN ( SELECT DocumentId FROM DocumentSubjectLink WHERE DocumentSubjectLink.SubjectID = @SubjectId ) ORDER BY Documents.Title ELSE -- Get ALL media SELECT Documents.DocumentId , Documents.Title , Documents.CategoryId , Documents.CreateDate , Documents.metaDescription AS 'Description' , media.DocumentId , media.MediaTypeId , media.URL , media.MediaId , Documents.EditDate , media.CreateDate , DocumentAuthors.AuthorId , DocumentAuthors2.AuthorId AS CoAuthorId , DocumentMediaType.MediaType , DocumentMediaType.MediaIconPath , -- MediaCategory.[Category] , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , DocumentAuthors2.AuthorFirst + ' ' + DocumentAuthors2.AuthorMiddle + ' ' + DocumentAuthors2.AuthorLast AS CoAuthor , DocumentAuthors.[AuthorLast] AS LastName , DocumentAuthors2.[AuthorLast] AS CoLastName , CASE WHEN Documents.metaImage IS NULL THEN 0 WHEN Documents.metaImage IS NOT NULL THEN 1 END AS HasImage FROM dbo.Documents LEFT JOIN dbo.MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN dbo.DocumentAuthors ON Documents.Author1 = DocumentAuthors.AuthorId LEFT JOIN dbo.DocumentMediaType ON DocumentMediaType.MediaTypeId = media.MediaTypeId LEFT JOIN dbo.MediaCategory ON Documents.CategoryId = MediaCategory.CategoryId LEFT JOIN dbo.DocumentAuthors AS DocumentAuthors2 ON Documents.Author2 = DocumentAuthors2.AuthorId WHERE DocumentMediaType.MediaTypeId IN ( 1 , 2 ) AND Documents.Display = 1 AND media.Display = 1 ORDER BY Documents.Title SELECT mc.CategoryId, mc.ParentCategory, mc.Category, mc.CategoryImage, parent.Category AS ParentCategoryName, parent.CategoryId AS ParentCategoryId FROM dbo.MediaCategory mc LEFT JOIN dbo.MediaCategory parent ON parent.CategoryId = mc.ParentCategory WHERE mc.CategoryId = @CategoryId END GO /****** Object: StoredProcedure [dbo].[GlobalGetStatistics] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[GlobalGetStatistics] @ShowPrivateData bit = 0 AS BEGIN SET NOCOUNT ON ; SELECT ( SELECT COUNT(1) FROM dbo.DailyArticles ) AS DailyArticles , ( SELECT COUNT(1) FROM dbo.Documents sg INNER JOIN dbo.MediaAlternateFormat maf ON maf.DocumentId = sg.DocumentId WHERE maf.MediaTypeId = 1 ) AS Audio , ( SELECT COUNT(1) FROM dbo.Documents sg INNER JOIN dbo.MediaAlternateFormat maf ON maf.DocumentId = sg.DocumentId WHERE maf.MediaTypeId = 2 ) AS Video , ( SELECT COUNT(1) FROM dbo.Documents sg INNER JOIN dbo.MediaAlternateFormat maf ON maf.DocumentId = sg.DocumentId WHERE maf.MediaTypeId = 3 ) AS PDF , ( SELECT COUNT(1) FROM dbo.Documents sg INNER JOIN dbo.MediaAlternateFormat maf ON maf.DocumentId = sg.DocumentId WHERE maf.MediaTypeId = 4 ) AS Documents , ( SELECT COUNT(1) FROM dbo.Documents sg INNER JOIN dbo.MediaAlternateFormat maf ON maf.DocumentId = sg.DocumentId WHERE maf.MediaTypeId = 5 ) AS WebPages , ( SELECT COUNT(1) FROM dbo.Documents sg INNER JOIN dbo.MediaAlternateFormat maf ON maf.DocumentId = sg.DocumentId WHERE maf.MediaTypeId = 6 ) AS OfflineFiles , ( SELECT COUNT(1) FROM dbo.Documents sg INNER JOIN dbo.MediaAlternateFormat maf ON maf.DocumentId = sg.DocumentId WHERE maf.MediaTypeId = 7 ) AS StoreLinks , ( SELECT COUNT(1) FROM dbo.Documents sg INNER JOIN dbo.MediaAlternateFormat maf ON maf.DocumentId = sg.DocumentId WHERE maf.MediaTypeId = 8 ) AS AudioBooks , ( SELECT COUNT(1) FROM dbo.Documents sg INNER JOIN dbo.MediaAlternateFormat maf ON maf.DocumentId = sg.DocumentId WHERE maf.MediaTypeId = 9 ) AS Ebook , ( SELECT COUNT(1) FROM dbo.Documents sg INNER JOIN dbo.MediaAlternateFormat maf ON maf.DocumentId = sg.DocumentId WHERE maf.MediaTypeId = 12 ) AS 'Streaming Video' , ( SELECT COUNT(1) FROM dbo.Documents sg INNER JOIN dbo.MediaAlternateFormat maf ON maf.DocumentId = sg.DocumentId WHERE maf.MediaTypeId = 14 ) AS 'MPEG4 Video' , ( SELECT COUNT(1) FROM dbo.Documents sg WHERE sg.DocumentId NOT IN ( SELECT DocumentId FROM dbo.MediaAlternateFormat ) ) AS HtmlContentPages , ( SELECT COUNT(1) FROM dbo.DocumentAuthors ) AS Authors , ( SELECT COUNT(1) FROM dbo.DocumentAuthors WHERE LEN(DocumentAuthors.BioText) > 0 ) AS 'Author Bios' , ( SELECT COUNT(1) FROM dbo.PeriodicalsView ) AS 'Journal Articles' , ( SELECT COUNT(1) FROM dbo.Periodicals ) AS 'Journals' , ( SELECT COUNT(1) FROM dbo.QuizType ) AS 'Quiz' , ( SELECT COUNT(1) FROM dbo.Page ) AS 'Pages' , ( SELECT COUNT(1) FROM dbo.Pages ) AS 'Old Pages' , ( SELECT COUNT(1) FROM dbo.Biography ) AS 'Biography' , ( SELECT COUNT(1) FROM dbo.Quotes ) AS 'Quotes' END GO /****** Object: StoredProcedure [dbo].[GetContentbyGUID] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[GetContentbyGUID] @GUID uniqueidentifier AS SET CONCAT_NULL_YIELDS_NULL OFF BEGIN IF EXISTS ( SELECT * FROM page WHERE GUID = @GUID ) BEGIN DECLARE @PageId int SET @PageId = ( SELECT PageId FROM Page WHERE GUID = @GUID ) SELECT 'Page' AS DocumentType , PageId AS Id , pageTitle AS Title , [content] AS Contents , page.[folder] + '/' + page.[pageName] AS URL , EditDate AS CreateDate , metaDescription AS Description , metaKeywords AS Keywords , -- Get Next/Prev links ( SELECT TOP 1 GUID FROM Page WHERE PageId > @PageId AND Visible = 1 ) AS 'NextGUID' , ( SELECT TOP 1 GUID FROM Page WHERE PageId < @PageId AND Visible = 1 ORDER BY PageId DESC ) AS 'PreviousGUID' FROM Page WHERE [GUID] = @GUID END ELSE IF EXISTS ( SELECT * FROM Documents WHERE GUID = @GUID ) BEGIN DECLARE @DocumentId int SET @DocumentId = ( SELECT DocumentId FROM Documents WHERE GUID = @GUID ) DECLARE @Keywords varchar(200) SET @Keywords = '' SELECT @Keywords = @Keywords + CASE WHEN LEN(@Keywords) > 0 THEN ',' ELSE '' END + Subject FROM DocumentSubjects ds JOIN DocumentSubjectLink dsl ON dsl.SubjectId = ds.SubjectId WHERE dsl.DocumentId = @DocumentId DECLARE @Contents varchar(max) SET @Contents = ( SELECT GuideContent FROM Documents WHERE DocumentId = @DocumentId ) IF @Contents = '' OR @Contents IS NULL SET @Contents = ( SELECT TOP 1 URL FROM MediaAlternateFormat WHERE MediaAlternateFormat.DocumentId = @DocumentId ) SELECT CASE WHEN LEN(GuideContent) = 0 AND ( SELECT COUNT(1) FROM MediaAlternateFormat WHERE DocumentId = @DocumentId AND [MediaTypeId] IN ( 1 , 2 ) ) > 0 THEN 'Media' WHEN LEN(GuideContent) = 0 AND ( ( SELECT COUNT(1) FROM MediaAlternateFormat WHERE DocumentId = @DocumentId AND [MediaTypeId] = 3 ) > 0 ) THEN 'PDF' ELSE 'Content' END AS 'DocumentType' , DocumentId AS Id , Title , PubInfo AS Description , @Contents AS Contents , '' AS URL , EditDate AS CreateDate , @Keywords AS Keywords , -- Get Next/Prev links ( SELECT TOP 1 GUID FROM Documents WHERE DocumentId > @DocumentId AND Documents.Display = 1 ) AS 'NextGUID' , ( SELECT TOP 1 GUID FROM Documents WHERE DocumentId < @DocumentId AND Documents.Display = 1 ORDER BY DocumentId DESC ) AS 'PreviousGUID' FROM Documents WHERE GUID = @GUID END ELSE IF EXISTS ( SELECT * FROM Calendar WHERE GUID = @GUID ) SELECT 'Calendar' AS DocumentType , EventId AS Id , Title , IntroText AS Description , Description AS Contents , '' AS URL , CreateDate , EventDate + ' ' + Location AS Keywords FROM Calendar WHERE ( GUID = @GUID ) ELSE IF EXISTS ( SELECT * FROM DailyArticles WHERE GUID = @GUID ) BEGIN DECLARE @DatePosted datetime SET @DatePosted = ( SELECT DatePosted FROM DailyArticles WHERE GUID = @GUID ) SELECT 'DailyArticle' AS DocumentType , ArticleId AS Id , Title , Description , ArticleText AS Contents , '' AS URL , EditDate AS CreateDate , da.AuthorFirst + ' ' + da.AuthorMiddle + ' ' + da.AuthorLast AS Author , da.AuthorFirst + ' ' + da.AuthorMiddle + ' ' + da.AuthorLast + ',' + da.AuthorFirst + ' ' + da.AuthorMiddle + ' ' + da. AuthorLast AS Keywords , -- Get Next/Prev links ( SELECT TOP 1 daNext.GUID FROM DailyArticles daNext WHERE daNext.DatePosted > @DatePosted AND daNext.ShowArticle = 1 ORDER BY daNext.DatePosted ) AS 'NextId' , ( SELECT TOP 1 daprevious.GUID FROM DailyArticles daprevious WHERE daprevious.DatePosted < @DatePosted AND daprevious. ShowArticle = 1 ORDER BY daprevious.DatePosted DESC ) AS 'PreviousId' FROM DailyArticles LEFT OUTER JOIN dbo.DocumentAuthors da ON da.AuthorId = DailyArticles.AuthorId OR da.AuthorId = DailyArticles.CoAuthorId WHERE GUID = @GUID END ELSE IF EXISTS ( SELECT * FROM RegistrationForms WHERE GUID = @GUID ) SELECT 'Form' AS DocumentType , FormId AS Id , FormTitle AS Title , FormTitle AS Description , Introduction AS Contents , '' AS URL , CreateDate , '' AS Keywords FROM RegistrationForms WHERE GUID = @GUID ELSE IF EXISTS ( SELECT * FROM pages WHERE GUID = @GUID ) SELECT 'Page' AS DocumentType , control AS Id , pageTitle AS Title , [content] AS Contents , '' AS URL , CreateDate , metaDescription AS Description , metaKeywords AS Keywords FROM Pages WHERE [GUID] = @GUID IF EXISTS ( SELECT * FROM AbleCommerce..ac_Products WHERE GUID = @GUID ) BEGIN DECLARE @ProductId int SET @ProductId = ( SELECT ProductId FROM AbleCommerce..ac_Products WHERE GUID = @GUID ) SELECT 'Product' AS DocumentType , ProductId AS Id , -- todo AbleCommerce..ac_Manufacturers.Name + ' : ' + NAME AS Title , '<img src="/store/' + ThumbnailUrl + '" alt="' + NAME + '" align="left" />' + '<span class="CommonMessageTitle"><a href="/store/Product1.aspx?ProductId=' + CAST(ProductId AS varchar(10)) + '"> Buy Now!</a> </span><br /><br />' + Description AS Contents , '' AS URL , CreatedDate AS 'CreateDate' , [NAME] + ' ' + Summary AS Description , -- AbleCommerce..ac_Manufacturers.Name --+ ',' + (SELECT TOP 1 FieldValue FROM MisesShop..CUSTOMFIELDS WHERE TableName = 'PRODUCTS' AND ForeignKey_ID = ProductId '' AS Keywords , -- Get Next/Prev links ( SELECT TOP 1 GUID FROM AbleCommerce..ac_Products JOIN AbleCommerce..ac_Manufacturers ON AbleCommerce..ac_Manufacturers.ManufacturerId = AbleCommerce.. ac_Products.ManufacturerId WHERE ProductId > @ProductId AND AbleCommerce..ac_Products. DisablePurchase = 0 ) AS 'NextGUID' , ( SELECT TOP 1 GUID FROM AbleCommerce..ac_Products WHERE ProductId < @ProductId AND AbleCommerce..ac_Products. DisablePurchase = 0 ORDER BY ProductId DESC ) AS 'PreviousGUID' FROM AbleCommerce..ac_Products WHERE [GUID] = @GUID END END GO /****** Object: StoredProcedure [dbo].[DocumentGetKeywords] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Name -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DocumentGetKeywords] -- Add the parameters for the stored procedure here @DocumentId int AS BEGIN DECLARE @Keywords varchar(max) SET @Keywords = '' SELECT @Keywords = @Keywords + CASE WHEN LEN(@Keywords) > 0 THEN ',' ELSE '' END + [ShortSubject] FROM DocumentSubjects ds JOIN DocumentSubjectLink dsl ON dsl.SubjectId = ds.SubjectId WHERE dsl.DocumentId = @DocumentId SELECT @Keywords AS Keywords END GO /****** Object: StoredProcedure [dbo].[DocumentGetFeed] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[DocumentGetFeed] @AuthorId int = 0 , @SubjectId int = 0, @MediaType INT =0, @Limit INT = 5000, @SearchQuery VARCHAR(150) = null AS SET CONCAT_NULL_YIELDS_NULL OFF IF @Limit > 0 SET ROWCOUNT @Limit IF @AuthorId > 0 SELECT DocumentView.* FROM DocumentView WHERE ( Author1 = @AuthorId OR Author2 = @AuthorId ) ORDER BY CreateDate DESC , DocumentId DESC ELSE IF @SubjectId > 0 SELECT DocumentView.* FROM DocumentView LEFT JOIN [DocumentSubjectLink] dsj ON dsj.[DocumentId] = DocumentView.[DocumentId] WHERE dsj.[SubjectID] = @SubjectId ORDER BY CreateDate DESC , DocumentView.DocumentId DESC ELSE IF @MediaType > 0 SELECT DocumentView.* FROM DocumentView WHERE MediaTypeId = @MediaType ORDER BY CreateDate DESC , DocumentId DESC ELSE IF LEN(@SearchQuery) > 0 SELECT DocumentView.* FROM DocumentView WHERE (Title LIKE '%' + @SearchQuery + '%' OR Author LIKE '%' + @SearchQuery + '%' OR Description LIKE '%' + @SearchQuery + '%' OR dbo.DocumentView.StoreDescription LIKE '%' + @SearchQuery + '%' OR dbo.DocumentView.metaDescription LIKE '%' + @SearchQuery + '%' ) ORDER BY CreateDate DESC , DocumentId DESC ELSE SELECT DocumentView.* FROM DocumentView ORDER BY CreateDate DESC , DocumentId DESC GO /****** Object: StoredProcedure [dbo].[DocumentDelete] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[DocumentDelete] @DocumentId int AS DELETE FROM MediaAlternateFormat WHERE DocumentId = @DocumentId DELETE FROM DocumentSubjectLink WHERE DocumentId = @DocumentId DELETE FROM Documents WHERE DocumentId = @DocumentId GO /****** Object: StoredProcedure [dbo].[DocumentAuthorsGetList] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DocumentAuthorsGetList] @ContentType varchar(30) = NULL AS SET NOCOUNT ON ; SET CONCAT_NULL_YIELDS_NULL OFF IF @ContentType = 'DailyArticles' SELECT DISTINCT da.AuthorId , AuthorLast , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS 'AuthorName' ,Photo FROM DocumentAuthors da INNER JOIN DailyArticles ON DailyArticles.AuthorId = da.AuthorId WHERE DailyArticles.ShowArticle = 1 ORDER BY AuthorLast ELSE IF @ContentType = 'Literature' SELECT DISTINCT da.AuthorId , AuthorLast , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS 'AuthorName' ,Photo FROM DocumentAuthors da INNER JOIN Documents ON da.AuthorId = Documents.Author1 WHERE Documents.Display = 1 ORDER BY AuthorLast ELSE IF @ContentType = 'Media' SELECT DISTINCT da.AuthorId , AuthorLast , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS 'AuthorName' ,Photo FROM DocumentAuthors da INNER JOIN Documents ON da.AuthorId = Documents.Author1 INNER JOIN MediaAlternateFormat ON MediaAlternateFormat.DocumentId = Documents.DocumentId WHERE Documents.Display = 1 ORDER BY AuthorLast ELSE IF @ContentType = 'Quotes' SELECT DISTINCT da.AuthorId , AuthorLast , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS 'AuthorName' ,Photo FROM DocumentAuthors da INNER JOIN [Quotes] ON da.[AuthorId] = [Quotes].[AuthorId] ORDER BY AuthorLast ELSE IF @ContentType = 'LastNameFirst' SELECT DISTINCT da.AuthorId , AuthorLast , da.authorLast + ', ' + da.authorFirst + ' ' + da.authorMiddle AS 'AuthorName' ,Photo FROM DocumentAuthors da ORDER BY AuthorLast ELSE SELECT da.AuthorId , AuthorLast , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS 'AuthorName' ,Photo FROM DocumentAuthors da ORDER BY AuthorLast GO /****** Object: StoredProcedure [dbo].[DocumentAuthorsGetDetail] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DocumentAuthorsGetDetail] @ArticleId int = 0 , @AuthorId int = 0 AS BEGIN SET CONCAT_NULL_YIELDS_NULL OFF IF @ArticleId > 0 SELECT DocumentAuthors.AuthorId , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , Photo , BioText , ( SELECT TOP 1 ArticleId FROM DailyArticles WHERE AuthorId = DocumentAuthors.AuthorId ORDER BY ArticleId DESC ) AS LatestArticleId FROM DocumentAuthors INNER JOIN DailyArticles ON DailyArticles.AuthorId = DocumentAuthors.AuthorId WHERE DailyArticles.ArticleId = @ArticleId UNION ALL SELECT DocumentAuthors.AuthorId , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , Photo , BioText , ( SELECT TOP 1 ArticleId FROM DailyArticles WHERE AuthorId = DocumentAuthors.AuthorId ORDER BY ArticleId DESC ) AS LatestArticleId FROM DocumentAuthors INNER JOIN DailyArticles ON DailyArticles. CoAuthorId = DocumentAuthors.AuthorId WHERE DailyArticles.ArticleId = @ArticleId ELSE SELECT AuthorId , DocumentAuthors.AuthorFirst + ' ' + DocumentAuthors.AuthorMiddle + ' ' + DocumentAuthors.AuthorLast AS Author , Photo , BioText FROM DocumentAuthors WHERE AuthorId = @AuthorId END GO /****** Object: StoredProcedure [dbo].[DailyArticlesGetFullTextFeed] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[DailyArticlesGetFullTextFeed] AS SET CONCAT_NULL_YIELDS_NULL OFF SELECT TOP 5 ArticleId , Title , GUID , Photo , da.authorId , ArticleText , dateposted , description , headline , featured , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS AuthorName FROM DailyArticles JOIN DocumentAuthors da ON da.AuthorId = DailyArticles.AuthorId WHERE DailyArticles.ShowArticle = 1 -- AND ( DailyArticles.AuthorId = @AuthorId -- OR DailyArticles.CoAuthorId = @AuthorId -- ) ORDER BY Headline DESC , DailyArticles.DatePosted DESC GO /****** Object: StoredProcedure [dbo].[DailyArticlesGetFeed] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[DailyArticlesGetFeed] @AuthorId int = 0 --@SortField varchar(50) = 'ORDER BY authorLast, dateposted DESC' AS SET CONCAT_NULL_YIELDS_NULL OFF IF @AuthorId > 0 SELECT TOP 100 ArticleId , Title , GUID , Photo , dateposted , da.authorId , description , headline , featured , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS AuthorName FROM DailyArticles JOIN DocumentAuthors da ON da.AuthorId = DailyArticles.AuthorId WHERE DailyArticles.ShowArticle = 1 AND ( DailyArticles.AuthorId = @AuthorId OR DailyArticles.CoAuthorId = @AuthorId ) ORDER BY DailyArticles.DatePosted DESC ELSE SELECT TOP 30 ArticleId , Title , GUID , Photo , da.AuthorId , dateposted , description , headline , featured , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS AuthorName FROM DailyArticles JOIN DocumentAuthors da ON da.AuthorId = DailyArticles.AuthorId WHERE DailyArticles.ShowArticle = 1 ORDER BY DailyArticles.DatePosted DESC GO /****** Object: StoredProcedure [dbo].[DocumentsGetBiographies] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DocumentsGetBiographies] AS BEGIN SELECT Title , Documents.DocumentId FROM Documents INNER JOIN [DocumentSubjectLink] dsl ON dsl.[DocumentId] = documents.[DocumentId] WHERE SubjectId = 135 END GO /****** Object: StoredProcedure [dbo].[DocumentSave] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[DocumentSave] @DocumentId int = 0 , @Display bit , @Featured bit , @Title varchar(255) , @Author1 int , @Author2 int , @PubInfo varchar(8000) , @FullText bit , @Source varchar(100) , @GuideContent varchar(max) , @Length int = 0 , @CategoryId int , @ProductId int , @ParentCategoryId int , @GrandParentCategoryId int , @Description varchar(max) , @Keywords varchar(350) , @Identifier uniqueidentifier = '00000000-0000-0000-0000-000000000000' AS IF @Identifier = '00000000-0000-0000-0000-000000000000' SET @Identifier = NEWID() IF @DocumentId > 0 -- Addding New Item BEGIN UPDATE Documents SET Display = @Display , Title = @Title , Featured = @Featured , Author1 = @Author1 , Author2 = @Author2 , PubInfo = @PubInfo , [FullText] = @FullText , Source = @Source , GuideContent = @GuideContent , CategoryId = @CategoryId , ProductId = @ProductId , metaDescription = @Description , metaKeywords = @Keywords , EditDate = GETDATE() WHERE DocumentId = @DocumentId DELETE FROM DocumentSubjectLink WHERE DocumentId = @DocumentId END ELSE BEGIN INSERT INTO Documents ( [GUID] , Display , Featured , Title , Author1 , Author2 , PubInfo , [FullText] , Source , GuideContent , CategoryId , ProductId, metaDescription , metaKeywords ) VALUES ( @Identifier , @Display , @Featured , @Title , @Author1 , @Author2 , @PubInfo , @FullText , @Source , @GuideContent , @CategoryId , @ProductId, @Description , @Keywords ) SET @DocumentId = ( SELECT SCOPE_IDENTITY() ) END IF ( @ParentCategoryId > 0 ) AND NOT ( ( @CategoryId = @ParentCategoryId ) OR ( @ParentCategoryId = @GrandParentCategoryId ) OR ( @CategoryId = @GrandParentCategoryId ) OR ( @CategoryId = 0 ) ) BEGIN UPDATE dbo.MediaCategory SET ParentCategory = @ParentCategoryId WHERE CategoryId = @CategoryId ; IF @GrandParentCategoryId > 0 BEGIN UPDATE dbo.MediaCategory SET ParentCategory = @GrandParentCategoryId WHERE CategoryId = @ParentCategoryId ; END END SELECT @DocumentId GO /****** Object: StoredProcedure [dbo].[DailyArticleGet] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[DailyArticleGet] ( @ArticleId int ) AS SET CONCAT_NULL_YIELDS_NULL OFF SELECT DailyArticles.ArticleId , DailyArticles.GUID , DailyArticles.Title , DailyArticles.Description , DailyArticles.ArticleText , DailyArticles.Featured , DailyArticles.Headline , DailyArticles.ShowArticle , DailyArticles.DatePosted , da.AuthorFirst , da.AuthorLast , DailyArticles.AuthorId , DailyArticles.CoAuthorId , DailyArticles.DisplayOrder , DailyArticles.CreatedDate , da.AuthorFirst + ' ' + da.AuthorMiddle + ' ' + da.AuthorLast AS AuthorName , ca.AuthorFirst + ' ' + ca.AuthorMiddle + ' ' + ca.AuthorLast AS CoAuthorName , DailyArticles.PhotoURL , DailyArticles.ThumbnailURL , DailyArticles.PhotoHeight , [DailyArticles].[EditBy] , [DailyArticles].[EditDate] , ( SELECT TOP 1 daNext.ArticleId FROM DailyArticles daNext WHERE daNext.DatePosted > DailyArticles.DatePosted --AND daNext.ArticleId > DailyArticles.ArticleId AND ( daNext.ShowArticle = 1 OR daNext.Featured = 1 OR daNext.Headline = 1 ) ORDER BY daNext.DatePosted , daNext.ArticleId ) AS 'NextId' , ( SELECT TOP 1 daprevious.ArticleId FROM DailyArticles daprevious WHERE daprevious.DatePosted < DailyArticles.DatePosted -- AND daprevious.ArticleId < DailyArticles.ArticleId AND ( daprevious.ShowArticle = 1 OR daprevious.Featured = 1 OR daprevious. Headline = 1 ) ORDER BY daprevious.DatePosted DESC , daprevious.ArticleId DESC ) AS 'PreviousId' FROM dbo.DailyArticles LEFT OUTER JOIN dbo.DocumentAuthors AS da ON da.AuthorId = DailyArticles.AuthorId LEFT OUTER JOIN dbo.DocumentAuthors AS ca ON ca.AuthorId = DailyArticles.CoAuthorId WHERE ( DailyArticles.ArticleId = @ArticleId ) GO /****** Object: StoredProcedure [dbo].[DailyArticleAdd] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[DailyArticleAdd] ( @ArticleId int OUTPUT , @Title varchar(255) , @Description text , --@AuthorFirst varchar(50)='', --@AuthorLast varchar(50)='', @ArticleText text , @ShowArticle bit , @AuthorId int = 0 , @Featured bit , @Headline bit , @CoAuthorId int = 0 , --@DisplayOrder int =10, @PhotoHeight int = 0 , @PhotoURL varchar(255) = NULL , @EditBy varchar(75) = NULL , @DatePosted smalldatetime = GETDATE ) AS INSERT INTO DailyArticles ( Title , Description , ArticleText , ShowArticle , Featured , Headline , AuthorId , CoAuthorId , PhotoURL , DatePosted , PhotoHeight , EditBy ) VALUES ( @title , @description , @ArticleText , @ShowArticle , @Featured , @Headline , @AuthorId , @CoAuthorId , @PhotoURL , @DatePosted , @PhotoHeight , @EditBy ) SET @ArticleId = SCOPE_IDENTITY() DECLARE @RC int DECLARE @RevisionId int DECLARE @DocumentGUID uniqueidentifier SET @DocumentGUID = ( SELECT dbo.DailyArticles.GUID FROM dbo.DailyArticles WHERE ArticleId = @ArticleId ) EXECUTE @RC = [Mises].[dbo].[RevisionCreate] @RevisionId OUTPUT , @DocumentGUID , @ArticleText , @EditBy SELECT @ArticleId GO /****** Object: StoredProcedure [dbo].[ArticleGetDetails] Script Date: 09/24/2011 22:43:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[ArticleGetDetails] ( @ArticleId int ) AS SET CONCAT_NULL_YIELDS_NULL OFF SELECT DailyArticles.ArticleId , DailyArticles.GUID , DailyArticles.Title , DailyArticles.Description , DailyArticles.ArticleText , DailyArticles.ShowArticle , DailyArticles.DatePosted , da.AuthorFirst , da.AuthorLast , DailyArticles.AuthorId , DailyArticles.CoAuthorId , DailyArticles.DisplayOrder , DailyArticles.CreatedDate , da.AuthorFirst + ' ' + da.AuthorMiddle + ' ' + da.AuthorLast AS AuthorName , ca.AuthorFirst + ' ' + ca.AuthorMiddle + ' ' + ca.AuthorLast AS CoAuthorName , DailyArticles.PhotoURL , DailyArticles.PhotoHeight , [DailyArticles].[EditBy] , [DailyArticles].[EditDate] , ( SELECT TOP 1 daNext.ArticleId FROM DailyArticles daNext WHERE daNext.DatePosted > DailyArticles.DatePosted AND daNext.ShowArticle = 1 ORDER BY daNext.DatePosted ) AS 'NextId' , ( SELECT TOP 1 daprevious.ArticleId FROM DailyArticles daprevious WHERE daprevious.DatePosted < DailyArticles.DatePosted AND daprevious.ShowArticle = 1 ORDER BY daprevious.DatePosted DESC ) AS 'PreviousId' FROM dbo.DailyArticles LEFT OUTER JOIN dbo.DocumentAuthors AS da ON da.AuthorId = DailyArticles.AuthorId LEFT OUTER JOIN dbo.DocumentAuthors AS ca ON ca.AuthorId = DailyArticles.CoAuthorId WHERE ( DailyArticles.ArticleId = @ArticleId ) GO /****** Object: StoredProcedure [dbo].[_SearchDocumentText] Script Date: 09/24/2011 22:43:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[_SearchDocumentText] @find varchar(500) AS BEGIN SELECT COUNT(1) AS 'Articles' FROM DailyArticles WHERE ArticleText LIKE '%' + @find + '%' -- Study Guide SELECT COUNT(1) AS 'Documents' FROM Documents WHERE GuideContent LIKE '%' + @find + '%' -- Pages SELECT COUNT(1) AS 'Pages' FROM Page WHERE [Content] LIKE '%' + @find + '%' END GO /****** Object: StoredProcedure [dbo].[DocumentGetSubjects] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DocumentGetSubjects] -- Add the parameters for the stored procedure here @DocumentId int = 0 -- ,@GUID uniqueidentifier = null AS BEGIN SET NOCOUNT ON ; SELECT DocumentSubjects.SubjectId , DocumentSubjects.ShortSubject FROM DocumentSubjects WITH ( NOLOCK ) JOIN [DocumentSubjectLink] ON [DocumentSubjects].[SubjectId] = [DocumentSubjectLink].[SubjectID] WHERE DocumentSubjectLink.[DocumentId] = @DocumentId --OR [DocumentSubjectLink].[GUID] = @GUID END GO /****** Object: StoredProcedure [dbo].[DailyArticlesDeleteByArticleId] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DailyArticlesDeleteByArticleId] @ArticleId int AS BEGIN DELETE FROM DailyArticles WHERE ArticleId = @ArticleId END GO /****** Object: StoredProcedure [dbo].[_FixHTML] Script Date: 09/24/2011 22:43:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[_FixHTML] AS BEGIN UPDATE DailyArticles SET ArticleText = dbo.regexReplace(ArticleText , '<font[^>]*>' , '') UPDATE DailyArticles SET ArticleText = dbo.regexReplace(ArticleText , '</font>' , '') UPDATE DailyArticles SET ArticleText = dbo.regexReplace(ArticleText , ' class="MsoBodyText"' , '') UPDATE DailyArticles SET ArticleText = dbo.regexReplace(ArticleText , '<p align="left">' , '<p>') UPDATE DailyArticles SET ArticleText = dbo.regexReplace(ArticleText , '<p class="Mso[^>]*>' , '<p>') UPDATE DailyArticles SET ArticleText = dbo.regexReplace(ArticleText , '<FONT[^>]*>' , '') UPDATE DailyArticles SET ArticleText = dbo.regexReplace(ArticleText , '</FONT>' , '') EXEC _SearchAndReplace @SearchStr = '<br>' , @ReplaceStr = '<br />' EXEC _SearchAndReplace @SearchStr = '<s>' , @ReplaceStr = '<span class="line-through">' EXEC _SearchAndReplace @SearchStr = '</s>' , @ReplaceStr = '</span>' EXEC _SearchAndReplace @SearchStr = '<center>' , @ReplaceStr = '<div style="text-align: center">' EXEC _SearchAndReplace @SearchStr = '</center>' , @ReplaceStr = '</div>' EXEC [_SearchAndReplace] @SearchStr = 'nowrap="true"' , @ReplaceStr = 'nowrap="nowrap"' EXEC [_SearchAndReplace] @SearchStr = '<hr color="#0d4d7d" noshade="noshade" />' , @ReplaceStr = '<hr />' EXEC _SearchAndReplace @SearchStr = 'https://www.mises.org' , @ReplaceStr ='https://mises.org' EXEC _SearchAndReplace @SearchStr = 'http://www.mises.org' , @ReplaceStr ='http://mises.org' EXEC _SearchAndReplace @SearchStr = 'http://mises.org/story' , @ReplaceStr ='http://mises.org/daily' EXEC _SearchAndReplace @SearchStr = 'http://mises.org/multimedia' , @ReplaceStr ='http://media.mises.org' EXEC _SearchAndReplace @SearchStr = 'http://mises.org/fullarticle.aspx?Id=' , @ReplaceStr ='http://mises.org/daily/' EXEC _SearchAndReplace @SearchStr = 'http://mises.org/fullstory.aspx?Id=' , @ReplaceStr ='http://mises.org/daily/' EXEC _SearchAndReplace @SearchStr = 'http://mises.org/fullstory.asp?control=' , @ReplaceStr ='http://mises.org/daily/' EXEC _SearchAndReplace @SearchStr = 'http://mises.org/studyguide.asp' , @ReplaceStr ='http://mises.org/literature.aspx' EXEC _SearchAndReplace @SearchStr = 'http://mises.org/multimedia/' , @ReplaceStr ='http://media.mises.org/' EXEC _SearchAndReplace @SearchStr = 'mises.org/article.aspx?Id=' , @ReplaceStr ='mises.org/daily/' EXEC _SearchAndReplace @SearchStr = 'articles.aspx?action=gallery' , @ReplaceStr ='articles.aspx' EXEC _SearchAndReplace @SearchStr = 'http://mises.org/fullarticle.asp?record=' , @ReplaceStr ='http://mises.org/daily/' EXEC _SearchAndReplace @SearchStr = 'http://mises.org/blog/' , @ReplaceStr ='http://blog.mises.org/' EXEC _SearchAndReplace @SearchStr = 'Controls/Media/DocumentImage.ashx?Id=' , @ReplaceStr ='media/poster/' UPDATE Documents SET ProductId = (SELECT ProductId FROM AbleCommerce.dbo.ac_products products WHERE products.[Name] = Documents.title) WHERE ProductId = 0 --SELECT title, NAME, documents.productid FROM Documents INNER JOIN AbleCommerce.dbo.ac_products products ON products.ProductId = documents.ProductId UPDATE Documents SET metaDescription = REPLACE(metaDescription,'xdvipdfmx (0.4)','') WHERE metaDescription LIKE '%xdvipdfmx%' UPDATE Documents SET metaDescription = REPLACE(metaDescription,'Adobe Acrobat 8.1 Paper Capture Plug-in','') WHERE metaDescription LIKE '%Adobe Acrobat 8.1 Paper Capture Plug-in%' UPDATE Documents SET metaDescription = REPLACE(metaDescription,'Acrobat Distiller 5.0.5 (Windows)','') WHERE metaDescription LIKE '%Acrobat Distiller 5.0.5 (Windows)%' UPDATE Documents SET metaDescription = REPLACE(metaDescription,'Acrobat Distiller 9.0.0 (Windows)','') WHERE metaDescription LIKE '%Acrobat Distiller 9.0.0 (Windows)%' UPDATE Documents SET metaDescription = REPLACE(metaDescription,'Acrobat PDFWriter 5.0 for Windows','') WHERE metaDescription LIKE '%Acrobat PDFWriter 5.0 for Windows%' UPDATE Documents SET metaDescription = REPLACE(metaDescription,'Acrobat Distiller 5.0 (Windows)','') WHERE metaDescription LIKE '%Acrobat Distiller 5.0 (Windows)%' END GO /****** Object: StoredProcedure [dbo].[DocumentAdminLinks] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[DocumentAdminLinks] @SubjectId int = 0 , @AuthorId int = 0 , @MediaType int = 0 , @Source varchar(30) = NULL , @SearchQuery varchar(50) = NULL AS SET CONCAT_NULL_YIELDS_NULL OFF IF @SubjectId > 0 BEGIN SELECT DISTINCT Documents.DocumentId , Title , PubInfo , URL , Source , FullText , AuthorFirst + ' ' + AuthorLast AS Author , Author1 , Author2 , MediaType , media.MediaTypeId , MediaType , MediaIconPath , Documents.CreateDate FROM Documents LEFT JOIN MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN DocumentMediaType ON DocumentMediaType.MediaTypeId = media.MediaTypeId LEFT JOIN DocumentAuthors ON DocumentAuthors.AuthorId = Documents.Author1 LEFT JOIN DocumentSubjectLink ON DocumentSubjectLink.DocumentId = Documents.DocumentId WHERE SubjectId = @SubjectId ORDER BY CreateDate DESC , Documents.DocumentId DESC --Documents.Documents.DocumentId DESC SELECT Subject FROM DocumentSubjects WHERE SubjectId = @SubjectId SELECT TOP 10 Subject , SubjectID FROM DocumentSubjects WHERE CategoryId = ( SELECT CategoryId FROM DocumentSubjects WHERE SubjectId = @SubjectId ) END ELSE IF @AuthorId > 0 BEGIN SELECT DISTINCT Documents.DocumentId , Title , PubInfo , media.URL , Source , FullText , AuthorFirst + ' ' + AuthorLast AS Author , Author1 , Author2 , MediaType , media.MediaTypeId , MediaType , MediaIconPath , media.CreateDate FROM Documents LEFT JOIN MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN DocumentMediaType ON DocumentMediaType.MediaTypeId = media.MediaTypeId LEFT JOIN DocumentAuthors ON DocumentAuthors.AuthorId = Documents.Author1 LEFT JOIN DocumentSubjectLink ON DocumentSubjectLink.DocumentId = Documents.DocumentId WHERE ( Author1 = @AuthorId OR Author2 = @AuthorId ) ORDER BY CreateDate DESC , Documents.DocumentId DESC SELECT AuthorFirst + ' ' + AuthorLast AS Author FROM DocumentAuthors WHERE DocumentAuthors.AuthorId = @AuthorId END ELSE IF @MediaType > 0 SELECT DISTINCT Documents.DocumentId , Title , PubInfo , URL , Source , FullText , AuthorFirst + ' ' + AuthorLast AS Author , Author1 , Author2 , MediaType , media.MediaTypeId , MediaType , MediaIconPath , media.CreateDate FROM Documents LEFT JOIN MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN DocumentMediaType ON DocumentMediaType.MediaTypeId = media.MediaTypeId LEFT JOIN DocumentAuthors ON DocumentAuthors.AuthorId = Documents.Author1 LEFT JOIN DocumentSubjectLink ON DocumentSubjectLink.DocumentId = Documents.DocumentId WHERE media.MediaTypeId = @MediaType ORDER BY CreateDate DESC , Documents.DocumentId DESC ELSE IF LEN(@SOURCE) > 0 SELECT DISTINCT Documents.DocumentId , Title , PubInfo , URL , Source , FullText , AuthorFirst + ' ' + AuthorLast AS Author , Author1 , Author2 , MediaType , media.MediaTypeId , MediaType , MediaIconPath , media.CreateDate FROM Documents LEFT JOIN MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN DocumentMediaType ON DocumentMediaType.MediaTypeId = media.MediaTypeId LEFT JOIN DocumentAuthors ON DocumentAuthors.AuthorId = Documents.Author1 LEFT JOIN DocumentSubjectLink ON DocumentSubjectLink.DocumentId = Documents.DocumentId WHERE Source = @SOURCE ORDER BY CreateDate DESC , Documents.DocumentId DESC ELSE IF LEN(@SearchQuery) > 0 SELECT DISTINCT Documents.DocumentId , Title , PubInfo , URL , Source , FullText , AuthorFirst + ' ' + AuthorLast AS Author , Author1 , Author2 , MediaType , media.MediaTypeId , MediaType , MediaIconPath , Documents.CreateDate FROM Documents LEFT JOIN MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN DocumentMediaType ON DocumentMediaType.MediaTypeId = media.MediaTypeId LEFT JOIN DocumentAuthors ON DocumentAuthors.AuthorId = Documents.Author1 LEFT JOIN DocumentSubjectLink ON DocumentSubjectLink.DocumentId = Documents.DocumentId WHERE Title LIKE '%' + @SearchQuery + '%' OR AuthorFirst LIKE '%' + @SearchQuery + '%' OR AuthorLast LIKE '%' + @SearchQuery + '%' OR PubInfo LIKE '%' + @SearchQuery + '%' ORDER BY CreateDate DESC , Documents.DocumentId DESC ELSE SELECT DISTINCT Documents.DocumentId , Title , PubInfo , URL , Source , FullText , AuthorFirst + ' ' + AuthorLast AS Author , Author1 , Author2 , MediaType , media.MediaTypeId , MediaType , MediaIconPath , Documents.CreateDate FROM Documents LEFT JOIN MediaAlternateFormat media ON media.DocumentId = Documents.DocumentId LEFT JOIN DocumentMediaType ON DocumentMediaType.MediaTypeId = media.MediaTypeId LEFT JOIN DocumentAuthors ON DocumentAuthors.AuthorId = Documents.Author1 LEFT JOIN DocumentSubjectLink ON DocumentSubjectLink.DocumentId = Documents.DocumentId ORDER BY CreateDate DESC , Documents.DocumentId DESC GO /****** Object: StoredProcedure [dbo].[DailyArticleUpdateArticleOrder] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[DailyArticleUpdateArticleOrder] ( @ArticleId int , @DisplayOrder int ) AS UPDATE DailyArticles SET DisplayOrder = @DisplayOrder WHERE ArticleId = @ArticleId GO /****** Object: StoredProcedure [dbo].[DailyArticleUpdate] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[DailyArticleUpdate] ( @ArticleId int , @Title varchar(255) , @Description varchar(1500) , @ArticleText varchar(max) , @ShowArticle bit , @Featured bit , @Headline bit , @AuthorId int = 0 , @CoAuthorId int = 0 , --@DisplayOrder INT = 0, @PhotoHeight int = 0 , @PhotoURL varchar(255) = NULL , @DatePosted smalldatetime = GETDATE , @EditBy varchar(75) = NULL ) AS UPDATE DailyArticles SET Title = @Title , [Description] = @Description , ArticleText = @ArticleText , DatePosted = @Dateposted , ShowArticle = @ShowArticle , Featured = @Featured , Headline = @Headline , CoAuthorId = @CoAuthorId , AuthorId = @AuthorId , -- DisplayOrder = @DisplayOrder, -- ThumbnailUrl = @PhotoURL , PhotoHeight = @PhotoHeight , EditDate = GETDATE() , EditBy = @EditBy WHERE ( ArticleId = @ArticleId ) DECLARE @RC int DECLARE @RevisionId int DECLARE @DocumentGUID uniqueidentifier SET @DocumentGUID = ( SELECT dbo.DailyArticles.GUID FROM dbo.DailyArticles WHERE ArticleId = @ArticleId ) EXECUTE @RC = [Mises].[dbo].[RevisionCreate] @RevisionId OUTPUT , @DocumentGUID , @ArticleText , @EditBy GO /****** Object: StoredProcedure [dbo].[DailyArticlesTopAuthors] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DocumentGetTopAuthors -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DailyArticlesTopAuthors] AS BEGIN SELECT TOP 60 d.AuthorId , da.AuthorLast , COUNT(1) AS 'Count' FROM [DailyArticles] d JOIN [DocumentAuthors] da ON d.[AuthorId] = da.[AuthorId] OR d.[CoAuthorId] = da.[AuthorId] GROUP BY d.AuthorId , da.AuthorLast ORDER BY COUNT DESC END GO /****** Object: StoredProcedure [dbo].[DailyArticlesSummaryByDate] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DailyArticlesSummaryByDate] @DatePosted DATETIME AS BEGIN SET NOCOUNT ON ; SET CONCAT_NULL_YIELDS_NULL OFF -- Get Headline Article SELECT DailyArticles.ArticleId , DailyArticles.ArticleId , DailyArticles.Title , DailyArticles.Description , DailyArticles.AuthorId , DailyArticles.DisplayOrder , DailyArticles.Headline , DailyArticles.Featured , DailyArticles.CoAuthorId , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS Author , da2.authorFirst + ' ' + da2.authorMiddle + ' ' + da2.authorLast AS CoAuthor , DailyArticles.PhotoURL , DailyArticles.DatePosted , DailyArticles.PhotoHeight FROM dbo.DailyArticles(NOLOCK) JOIN dbo.DocumentAuthors da ON da.AuthorId = DailyArticles.AuthorId LEFT JOIN dbo.DocumentAuthors da2 ON da2.AuthorId = DailyArticles.CoAuthorId WHERE DailyArticles.ShowArticle = 1 AND DATEDIFF(dd, dbo.DailyArticles.DatePosted,@DatePosted) = 0 ORDER BY Headline DESC --CASE WHEN DailyArticles.Featured = 1 THEN Featured END, --CASE WHEN DailyArticles.Headline = 1 THEN Headline END END GO /****** Object: StoredProcedure [dbo].[DailyArticlesGetTodaysSummary] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DailyArticlesGetTodaysSummary] AS BEGIN SET NOCOUNT ON ; SET CONCAT_NULL_YIELDS_NULL OFF -- Get Headline Article SELECT DailyArticles.ArticleId , DailyArticles.ArticleId , DailyArticles.Title , DailyArticles.Description , DailyArticles.AuthorId , DailyArticles.DisplayOrder , DailyArticles.Headline , DailyArticles.Featured , DailyArticles.CoAuthorId , da.authorFirst + ' ' + da.authorMiddle + ' ' + da.authorLast AS Author , da2.authorFirst + ' ' + da2.authorMiddle + ' ' + da2.authorLast AS CoAuthor , DailyArticles.PhotoURL , DailyArticles.DatePosted , DailyArticles.PhotoHeight FROM dbo.DailyArticles(NOLOCK) JOIN dbo.DocumentAuthors da ON da.AuthorId = DailyArticles.AuthorId LEFT JOIN dbo.DocumentAuthors da2 ON da2.AuthorId = DailyArticles.CoAuthorId WHERE ( DailyArticles.Featured = 1 OR Headline = 1 ) AND DailyArticles.ShowArticle = 1 ORDER BY Headline DESC --CASE WHEN DailyArticles.Featured = 1 THEN Featured END, --CASE WHEN DailyArticles.Headline = 1 THEN Headline END END GO /****** Object: StoredProcedure [dbo].[DailyArticlesGetRecent] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: DavidV -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[DailyArticlesGetRecent] AS BEGIN SET NOCOUNT ON ; SET CONCAT_NULL_YIELDS_NULL OFF -- Daily Articles SELECT TOP 4 DailyArticles.ArticleId , DailyArticles.Title , da.AuthorFirst + ' ' + da.AuthorMiddle + ' ' + da.AuthorLast AS AuthorName , DailyArticles.PhotoURL , DailyArticles.AuthorId , DailyArticles.PhotoHeight , DatePosted FROM DailyArticles WITH ( NOLOCK ) INNER JOIN DocumentAuthors AS da ON da.AuthorId = DailyArticles.AuthorId WHERE DailyArticles.ShowArticle = 1 AND dbo.DailyArticles.Featured = 0 AND dbo. DailyArticles.Headline = 0 ORDER BY DailyArticles.DatePosted DESC END GO /****** Object: StoredProcedure [dbo].[DailyArticlesGetImageGallery] Script Date: 09/24/2011 22:43:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[DailyArticlesGetImageGallery] @AuthorId int = 0 , @SearchQuery varchar(100) = '', @Limit int = 0 AS SET CONCAT_NULL_YIELDS_NULL OFF IF (@Limit > 0) SET ROWCOUNT @Limit IF @AuthorId > 0 SELECT ArticleId,DatePosted, Title, Author, AuthorId, ImageURL, PhotoUrl, NavigateUrl, [Month] FROM DailyView WHERE @AuthorId = AuthorId OR @AuthorId = dbo.DailyView.CoAuthorId ORDER BY Headline DESC ,[DatePosted] DESC ELSE IF LEN(@SearchQuery) > 2 SELECT ArticleId,DatePosted, Title, Author, AuthorId, ImageURL, PhotoUrl, NavigateUrl, [Month] FROM DailyView WHERE ( description LIKE @SearchQuery OR title LIKE @SearchQuery OR AuthorId IN (SELECT ArticleId FROM dbo.DailyArticles WHERE FREETEXT(ArticleText,@SearchQuery)) ) ORDER BY Headline DESC ,[DatePosted] DESC ELSE SELECT ArticleId,DatePosted, Title, Author, AuthorId, ImageURL, PhotoUrl, NavigateUrl, [Month] FROM DailyView ORDER BY Headline DESC ,[DatePosted] DESC GO