<!--- Bug 7273 - Error when assigning security b/c bit columns comes up null from this view --->
<cfquery datasource="#application.config.DSN#">
ALTER VIEW [MenuCategories]
AS
SELECT     a.AssetID AS MenuID, a.AssetTypeID,a.BaseAssetID AS TemplateMenuID,
			a.Description, a.Icon, a.DisplayTitle, a.DisplayDesc, a.DisplayIcon, 
			a.SiteSearch, a.NoArchivesInSearch, a.HeaderGraphic, a.HeaderGraphicHeight, a.HeaderGraphicWidth, 
			a.InMenu AS Display,
			-- asset type
			t .Title AS AssetType, 
			-- social settings
			ss.Comments AS ApplicationComments, ss.CommentsModerator, ss.CommentsAnonymous, ss.CommentsPublic, ss.CommentsAlwaysShow,
			ss.ThumbsUp AS ApplicationThumbsUp, ss.ThumbsDown AS ApplicationThumbsDown, ss.ThumbsAnonymous, ss.ThumbsPublic, 
			ss.Tags AS TagsEnable, ss.TagsPublisherAdd, ss.TagsInTables, 
			-- site
			ISNULL(s.OwnersAssignViewSecurity, 0) AS OwnersAssignViewSecurity, 
			ISNULL(s.OwnersDesignTheme, 0) AS OwnersDesignTheme,
			-- app
			ISNULL(app.Template, 0) AS Template, app.Disabled AS Disable,
			app.FilesFolder AS FileFolderName, app.AppFolder, app.FileLinkTarget AS Target, 
			app.DefaultView, app.CleanUp, ISNULL(app.HasCollection, 0) AS HasCollection,
			app.TableName1, app.TableName2, app.TableName3, app.TableName4, 
			app.TableName5, app.TableName6, app.TableName7, app.TableName8, app.TableName9, 
			app.ShowFolderTree, app.FolderItemCount, app.FolderSubFolderCount, 
			app.CategoryColumns,app.SubCategoryColumns, app.SubCategoryThreshold, 
			app.CategoryPageInfo, app.CategoryPageGraphic, app.CategoryPageAlign, app.CategoryPageWidth, 
			ISNULL(app.Pagination, 0) AS Pagination, app.PaginationLocation, app.PaginationPageSetCount, app.PaginationItemsPerPage, app.PaginationType,
			app.AdvertiseArchiveDays, app.AdvertiseGraphic, app.AdvertiseThumbnail, app.AdvertiseDefaultNow,
			app.Add_Allowed, app.Deleting_Allowed, app.Password, app.ADNumber,
			-- link
			l.Target AS MenuTarget,
			-- diff across asset types
			CASE WHEN a.AssetTypeID IN (2,3) AND a.ParentSiteID > 1 THEN b.Title + ': ' + a.Title ELSE a.Title END AS MenuName,
			CASE WHEN a.AssetTypeID = 1 THEN s.HomeFlag WHEN a.AssetTypeID = 3 THEN p.HomeFlag ELSE 0 END AS HomeFlag,
			CASE WHEN a.AssetTypeID = 1 THEN '../Site/view.cfm?siteID=' + CAST(a.AssetID AS VARCHAR(12))
				WHEN a.AssetTypeID = 2 THEN appt.Link + '?appID=' + CAST(a.AssetID AS VARCHAR(12))
				WHEN a.AssetTypeID = 3 THEN '../Site/view.cfm?pageID=' + CAST(a.AssetID AS VARCHAR(12)) 
				WHEN l.TinyURLID IS NULL THEN l.URL ELSE '..' + y.URL
			 END AS MenuLink,
            CASE WHEN a.AssetTypeID = 1 THEN s.OwnersChangeProps ELSE app.Updating_Allowed END AS Updating_Allowed,
            CASE WHEN a.AssetID = 1 THEN 1 WHEN a.AssetTypeID = 1 THEN 26 ELSE app.AppTypeID END AS ClonedMenuID, 
            CASE WHEN a.ParentSiteID IS NULL OR a.ParentSiteID = 1 THEN 0 ELSE a.ParentSiteID END AS DeptClone
FROM         Asset a 
				INNER JOIN AssetType t ON a.AssetTypeID = t .AssetTypeID 
				LEFT JOIN Asset b ON a.ParentSiteID = b.AssetID
				LEFT JOIN Site s ON a.AssetID = s.SiteID
				LEFT JOIN Application app ON a.AssetID = app.AppID 
				LEFT JOIN Page p ON a.AssetID = p.PageID
				LEFT JOIN Link l ON a.AssetID = l.LinkID
				LEFT JOIN AssetSocialSettings ss ON a.AssetID = ss.AssetID
				LEFT JOIN ApplicationType appt ON app.AppTypeID = appt.AppTypeID
				LEFT JOIN TinyURL y ON l.TinyURLID = y.TinyURLID    
</cfquery>
<!--- Bug 7058: CSV import error in documents 
	Note error not related directly to csv import, when a new document or appbuild app are created, 
	LinkType for title fields are not set to a default value. This script retroactivly fixes old title fields.
	--->
	<cfquery datasource="#application.config.DSN#">
		UPDATE	AppbuilderFields
		SET		LinkType = 1
		WHERE	FieldTypeID = 50
		AND		LinkType IS NULL;
	</cfquery>