declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @contentPrefix varchar(128) = ; declare @todayDate datetime = ; with docCategories as ( SELECT cats.attrID as categoryId, cats.attrDescription AS categoryName FROM tbl_ORG_Attributes cats with (nolock) WHERE cats.OwnerID = @ownerId AND cats.FSL_TableCode = @ownerType AND cats.attrTypeID = 31 ), rawAssets as ( select null contentDomain, cast( null as int ) containerId, cast( null as varchar ) containerType, cast( null as varchar ) docTitle, cast( null as int) as categoryId, cast( null as int ) contentId, cast( null as int) fileId, 0.00 fileSize, null fileExt, null docDescription, cast( null as datetime ) publishDate, cast( null as datetime ) unpublishDate, 'N' isPublished, cast( null as datetime ) uploadDate, cast( null as datetime ) updateDate, cast( null as int ) updateUserId, 0 restricted, -1 thumbnailId, -1 highresCntId, 99 as sortRank , 0 as freshness , 'N' privateYesNo , cast( null as varchar ) uploadedByFullName, cast( null as varchar ) keyWords, cast('' as varchar ) unpublishToDo, cast( null as varchar ) internalNotes , cast( null as varchar ) AS linked_opr, cast( null as varchar ) AS linked_prd, cast( null as varchar ) AS linked_sku , cast( null as varchar ) AS linked_cdr, cast( null as varchar ) AS linked_mfr, cast( null as varchar ) as assetSelectionKey, 0 as selectedRecord , 'N' as internalOnly, '' as recordSource , cast( null as varchar ) permissionGrpIds , cast( null as varchar ) linked_brokerIds, cast( null as varchar ) linked_catIds, cast( null as varchar ) linked_catNames, cast( null as varchar ) linked_UserMemberIds union all select 'DOC' contentDomain, cast( null as int ) containerId, cast( null as varchar ) containerType, d.docTitle, d.repCategoryId as categoryId, d.contentId, d.docId as fileId, CAST((d.fileSize/1024) AS float) as fileSize , d.fileExt, d.docDescription, d.publishDate, d.unpublishDate, case when d.publishDate is not null and d.publishDate > @todayDate then 'F' when d.publishDate is not null and d.unpublishDate is not null and @todayDate >= d.publishDate and @todayDate <= d.unpublishDate then 'Y' when d.publishDate is not null and d.unpublishDate is null and d.publishDate <= @todayDate then 'Y' when d.publishDate is null then 'N' else 'N' end isPublished, d.uploadDate, d.lastUpdatedDate updateDate, d.lastUpdatedBy updateUserId, 0 restricted, thumbnailId, -1 highresCntId, sortRank , coalesce(dateDiff(day, d.lastUpdatedDate, @todayDate),0) as freshness, case when d.privateYesNo = 1 then 'Y' else 'N' end privateYesNo, ou.firstname + ' ' + ou.lastname as uploadedByFullName, d.keyWords, d.unpublishToDo, d.internalNotes , STRING_AGG(CONVERT(NVARCHAR(MAX), CASE WHEN li.child_attrTypeID = 41 THEN li.child_attrID ELSE NULL END), ',') AS linked_opr , STRING_AGG(CONVERT(NVARCHAR(MAX),CASE WHEN li.child_attrTypeID = 59 THEN li.child_attrID ELSE NULL END), ',') AS linked_prd , STRING_AGG(CONVERT(NVARCHAR(MAX),CASE WHEN li.child_attrTypeID = 60 THEN li.child_attrID ELSE NULL END), ',') AS linked_sku , STRING_AGG(CONVERT(NVARCHAR(MAX),CASE WHEN li.child_attrTypeID = 63 THEN li.child_attrID ELSE NULL END), ',') AS linked_cdr , STRING_AGG(CONVERT(NVARCHAR(MAX),CASE WHEN li.child_attrTypeID = 42 THEN li.child_attrID ELSE NULL END), ',') AS linked_mfr , cast(concat('DOC',':',d.docId) as varchar) as assetSelectionKey , cast( case when ast.assetSelectionKey is not null then 1 else 0 end as bit ) as selectedRecord , d.internalOnly, d.recordSource , STRING_AGG(CONVERT(NVARCHAR(MAX), CASE WHEN li.child_attrTypeID = 23 THEN li.child_attrID ELSE NULL END), ',') AS permissionGrpIds , STRING_AGG(CONVERT(NVARCHAR(MAX), CASE WHEN li.child_attrTypeID = 7 THEN li.child_attrID ELSE NULL END), ',') AS linked_brokerIds , STRING_AGG(CONVERT(NVARCHAR(MAX), CASE WHEN li.child_attrTypeID = 31 THEN li.child_attrID ELSE NULL END), ',') AS linked_catIds , ( SELECT STRING_AGG(cat.categoryName, ',') FROM tbl_ORG_AttributeLinks catLi INNER JOIN docCategories cat ON cat.categoryId = catLi.child_attrID WHERE catLi.mstr_attrID = d.docId AND catLi.child_attrTypeID = 31 AND catLi.mstr_attrTypeID = 30 ) AS linked_catNames , STRING_AGG(CONVERT(NVARCHAR(MAX), CASE WHEN li.child_attrTypeID = 34 THEN li.child_attrID ELSE NULL END), ',') AS linked_UserMemberIds from tbl_DOC_Documents d with (nolock) LEFT OUTER JOIN tbl_FSPRO_Members ou with (nolock) ON d.AuthorUserId = ou.fspro_userId left outer JOIN tbl_ORG_AttributeLinks li ON li.mstr_attrID = d.docId and li.child_attrTypeID in (41,59,60,63,42,23,7,31,34) and li.child_attrTypeID in (41,59,60,63,42) and li.mstr_attrTypeID = 30 left outer join #_assetSelectionsTable# ast with (nolock) on ast.assetSelectionKey = concat('DOC',':',d.docId) where d.ownerId = @ownerId and d.fsl_tablecode = @ownerType GROUP BY d.docTitle, d.repCategoryId, d.contentId, d.docId,d.fileSize, d.fileExt, d.docDescription, d.publishDate, d.unpublishDate, d.uploadDate, d.lastUpdatedDate, d.lastUpdatedBy,thumbnailId, sortRank , d.privateYesNo , ou.firstname,ou.lastname, d.keyWords, d.unpublishToDo, d.internalNotes,ast.assetSelectionKey, d.internalOnly, d.recordSource union all select 'IMG' contentDomain, cast( null as int ) containerId, cast( null as varchar ) containerType, i.imgTitle as docTitle, i.categoryId, i.contentId, i.imgId as fileId, CAST((i.fileSize/1024) AS float) as fileSize, i.fileExt, i.imgDescription as docDescription, i.publishDate, i.unpublishDate, case when i.publishDate is not null and i.publishDate > @todayDate then 'F' when i.publishDate is not null and i.unpublishDate is not null and @todayDate >= i.publishDate and @todayDate <= i.unpublishDate then 'Y' when i.publishDate is not null and i.unpublishDate is null and i.publishDate <= @todayDate then 'Y' when i.publishDate is null then 'N' else 'N' end isPublished, i.uploadDate, i.lastUpdatedDate updateDate, i.lastUpdatedBy updateUserId, 0 restricted, -1 thumbnailId, i.highresCntId, i.sortRank, coalesce(dateDiff(day, lastUpdatedDate, @todayDate),0) as freshness, case when i.privateYesNo = 1 then 'Y' else 'N' end privateYesNo , cast( null as varchar ) uploadedByFullName, cast( null as varchar ) keyWords, cast('' as varchar ) unpublishToDo, cast( null as varchar ) internalNotes , STRING_AGG(CASE WHEN li.child_attrTypeID = 41 THEN li.child_attrID ELSE NULL END, ',') AS linked_opr , STRING_AGG(CASE WHEN li.child_attrTypeID = 59 THEN li.child_attrID ELSE NULL END, ',') AS linked_prd , STRING_AGG(CASE WHEN li.child_attrTypeID = 60 THEN li.child_attrID ELSE NULL END, ',') AS linked_sku , STRING_AGG(CASE WHEN li.child_attrTypeID = 63 THEN li.child_attrID ELSE NULL END, ',') AS linked_cdr , STRING_AGG(CASE WHEN li.child_attrTypeID = 42 THEN li.child_attrID ELSE NULL END, ',') AS linked_mfr , cast(concat('IMG',':',i.imgId) as varchar) as assetSelectionKey , cast( case when ast.assetSelectionKey is not null then 1 else 0 end as bit ) as selectedRecord , i.internalOnly, i.recordSource , STRING_AGG(CASE WHEN c.child_attrTypeID = 23 THEN c.child_attrID ELSE NULL END, ',') AS permissionGrpIds , STRING_AGG(CASE WHEN c.child_attrTypeID = 7 THEN c.child_attrID ELSE NULL END, ',') AS linked_brokerIds , STRING_AGG(CASE WHEN c.child_attrTypeID = 31 THEN c.child_attrID ELSE NULL END, ',') AS linked_catIds , ( SELECT STRING_AGG(cat.categoryName, ',') FROM tbl_ORG_AttributeLinks catLi INNER JOIN docCategories cat ON cat.categoryId = catLi.child_attrID WHERE catLi.mstr_attrID = i.imgId AND catLi.child_attrTypeID = 31 AND catLi.mstr_attrTypeID = 30 ) AS linked_catNames , STRING_AGG(CASE WHEN c.child_attrTypeID = 34 THEN c.child_attrID ELSE NULL END, ',') AS linked_UserMemberIds from tbl_IMG_Images i with (nolock) left outer JOIN tbl_ORG_AttributeLinks li ON li.mstr_attrID = i.imgId and li.child_attrTypeID in (41,59,60,63,42) and li.mstr_attrTypeID = 33 left outer JOIN tbl_ORG_AttributeLinks c ON c.mstr_attrID = i.imgId and c.child_attrTypeID in (23,7,31,34) and c.mstr_attrTypeID = 30 left outer join tbl_DOC_Categories dc with (nolock) ON c.child_attrTypeID = 31 AND dc.categoryId = c.child_attrID AND dc.ownerId = @ownerId AND dc.fsl_tablecode = @ownerType left outer join #_assetSelectionsTable# ast with (nolock) on ast.assetSelectionKey = concat('IMG',':',i.imgId) where i.ownerId = @ownerId and i.fsl_tablecode = @ownerType GROUP BY i.imgTitle, i.CategoryID, i.contentId, i.imgId, i.fileSize, i.fileExt, i.imgDescription, i.publishDate, i.unpublishDate, i.uploadDate, i.lastUpdatedDate, i.lastUpdatedBy, i.highresCntId, i.sortRank, i.privateYesNo,ast.assetSelectionKey, i.internalOnly, i.recordSource union all select '#_contentDomain#' contentDomain, containerId, containerType, docTitle, category as categoryId, o.contentId, o.fileId, CAST((fileSize/1024) AS float) as fileSize, fileExt, docDescription, publishDate, unpublishDate, case when o.publishDate is not null and o.unpublishDate is not null and @todayDate between o.publishDate and o.unpublishDate then 'Y' when o.publishDate is not null and o.unpublishDate is null and o.publishDate <= @todayDate then 'Y' when o.publishDate is not null and o.publishDate > @todayDate then 'F' when o.publishDate is null then 'N' else 'N' end isPublished, uploadDate, updateDate, updateUserId, case when restricted = 'Y' then 1 else 0 end restricted, -1 thumbnailId, -1 highresCntId, sortRank, coalesce( dateDiff(day, updateDate, @todayDate),0) as freshness, 'N' privateYesNo , cast( null as varchar ) uploadedByFullName, cast( null as varchar ) keyWords, cast('' as varchar ) unpublishToDo, cast( null as varchar ) internalNotes , cast( null as varchar ) AS linked_opr, cast( null as varchar ) AS linked_prd, cast( null as varchar ) AS linked_sku , cast( null as varchar ) AS linked_cdr, cast( null as varchar ) AS linked_mfr , cast(concat('#_contentDomain#',':',o.fileId) as varchar) as assetSelectionKey , cast( case when ast.assetSelectionKey is not null then 1 else 0 end as bit ) as selectedRecord , o.internalOnly, o.recordSource , cast( null as varchar ) permissionGrpIds , cast( null as varchar ) linked_brokerIds, cast( null as varchar ) linked_catIds, cast( null as varchar ) linked_catNames, cast( null as varchar ) linked_UserMemberIds from #_repositoryTable# o with (nolock) left outer join #_assetSelectionsTable# ast with (nolock) on ast.assetSelectionKey = concat('#_contentDomain#',':',o.fileId) where o.ownerId = @ownerId and o.ownerType = @ownerType and / = 'N' and exists ( select * from tbl_BRO_PrincipalTerritories pt with (nolock) inner join tbl_BRO_PrincipalTerritoryResources ptr with (nolock) on ptr.ownerId = pt.ownerId and ptr.ownerType = pt.ownerType and ptr.principalTerritoryId = pt.principalTerritoryId and ptr.resourceType = 'TER' and ptr.resourceId in ( ) where pt.principalId = o.containerId and pt.ownerId = o.ownerId and pt.ownerType = o.ownerType ) ), filteredRawAssets as ( select ra.* from rawAssets ra where (1=1 or ra.linked_brokerIds in () or ra.linked_UserMemberIds in () or ra.permissionGrpIds in () ) and assetSelectionKey = ) , assetsWithContentURL1 as ( select ra.*, @contentPrefix + c.mainFolder + '/' + c.subFolder + case when nullif( c.subFolder2, '' ) is not null then '/' + c.subFolder2 else '/' end + c.fileName as contentURL, c.fileName as docFileName, lub.firstname + ' ' + lub.lastname as lastUpdatedByFullName from filteredRawAssets ra LEFT OUTER JOIN tbl_FSPRO_Members lub with (nolock) ON ra.updateUserId = lub.fspro_userId inner join tbl_FSC_Content c with (nolock) on c.contentId = ra.contentId and c.contentId != 755 ) , assetsWithContentURL2 as ( select ra.*, @contentPrefix + c.mainFolder + '/' + c.subFolder + case when nullif( c.subFolder2, '' ) is not null then '/' + c.subFolder2 else '/' end + c.fileName as thumbnailURL, c.fileName as thnFileName from assetsWithContentURL1 ra left outer join tbl_FSC_Content c with (nolock) on c.contentId = ra.thumbnailId and c.contentId != 755 ) , assetsWithContentURL3 as ( select ra.*, @contentPrefix + c.mainFolder + '/' + c.subFolder + case when nullif( c.subFolder2, '' ) is not null then '/' + c.subFolder2 else '/' end + c.fileName as highresURL, c.fileName as highresCntFileName from assetsWithContentURL2 ra left outer join tbl_FSC_Content c with (nolock) on c.contentId = ra.highresCntId and c.contentId != 755 ) , assets as ( select a.*, case when right( a.contentURL, charindex( '.', reverse( a.contentURL )) - 1 ) in ( ) then left( a.contentURL, len( a.contentURL ) - charindex( '.', reverse( a.contentURL ) )) + '_50' + right( a.contentURL, charindex( '.', reverse( a.contentURL )) ) else null end previewURL from assetsWithContentURL3 a ) , categories as ( select 'OPR' contentDomain, categoryId, categoryDescription from tbl_OPR_RepositoryCategories with (nolock) where ownerId = @ownerId and ownerType = @ownerType union all select 'CDR' contentDomain, categoryId, categoryDescription from tbl_CDR_RepositoryCategories with (nolock) where ownerId = @ownerId and ownerType = @ownerType union all select 'BPL' contentDomain, categoryId, categoryDescription from tbl_BPL_RepositoryCategories with (nolock) where ownerId = @ownerId and ownerType = @ownerType union all select 'DOC' contentDomain, categoryId, categoryName as categoryDescription from tbl_DOC_Categories with (nolock) where ownerId = @ownerId and fsl_tablecode = @ownerType ) , assetsWithCategory as ( select a.*, c.categoryDescription from assets a left outer join categories c on c.contentDomain = a.contentDomain and c.categoryId = a.categoryId ) ,industryCategories as ( SELECT categoryId, categoryDescription FROM tbl_PRD_RepositoryCategories with (nolock) WHERE containerType = 'SKU' AND ownerID = 0 AND ownerType = '*' AND left(categoryDescription, 3) <> 'GS1' ) , assetsWithIndustryCategory as ( select a.*, ic.categoryDescription as industryCategoryDescription from assetsWithCategory a left outer join industryCategories ic on ic.categoryId = a.categoryId ) , assetsWithPartner as ( select a.*, opr.companyName as partnerName, a.containerId as partnerId, a.containerType as partnerType, opr.territoryId from assetsWithIndustryCategory a inner join tbl_OPR_CLientOperators opr with (nolock) on opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = a.containerId where a.containerType = 'OPR' and opr.territoryId in ( ) union all select a.*, cdr.cdr_dstName as partnerName, a.containerId as partnerId, a.containerType as partnerType, cdr.cdr_territoryId as territoryId from assetsWithIndustryCategory a inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType and cdr.cdr_recordId = a.containerId where a.containerType = 'CDR' and cdr.cdr_territoryId in ( ) union all select a.*, mfr.mfr_name as partnerName, mfr.mfr_id as partnerId, 'MFR' as partnerType, cast( null as integer ) territoryId from assetsWithIndustryCategory a inner join tbl_BRO_Principals bpl with (nolock) on bpl.ownerId = @ownerId and bpl.ownerType = @ownerType and bpl.principalId = a.containerId inner join tblManufacturers mfr with (nolock) on mfr.mfr_id = bpl.mfrId where a.containerType = 'BPL' union all select a.*,cast (null as varchar ) as partnerName, cast( null as integer ) as partnerId, cast( null as varchar ) as partnerType, cast( null as integer ) territoryId from assetsWithIndustryCategory a where coalesce( a.containerType, '???') not in ( 'BPL', 'OPR', 'CDR' ) ), assetsWithUser as ( select a.*, m.email updateUserEmail, m.firstName + ' ' + m.lastName as updateUserFullName, case when len(isnull(a.linked_prd,'')) > 0 or len(isnull(a.linked_sku,'')) > 0 then 'Y' else 'N' end as prdLink, case when len(isnull(a.linked_opr,'')) > 0 then 'Y' else 'N' end as oprLink, case when len(isnull(a.linked_cdr,'')) > 0 then 'Y' else 'N' end as cdrLink, case when len(isnull(a.linked_mfr,'')) > 0 then 'Y' else 'N' end as mfrLink from assetsWithPartner as a left outer join tbl_Fspro_members m with (nolock) on m.ownerId > 0 and m.fspro_userId = a.updateUserId ) #forJSON( 'assetsWithUser', attributes.dataStore, 'docTitle' )# , result as ( select assetSelectionKey, fileId, selectedRecord from assetsWithUser where 1 = 1 and #applyFilter( attributes.datastore.filter )# ) insert into #_assetSelectionsTable# ( assetSelectionKey, fileId ) select result.assetSelectionKey, result.fileId from result where selectedRecord = 0 delete st from #_assetSelectionsTable# st inner join result r with (nolock) on r.assetSelectionKey = st.assetSelectionKey and r.fileId = st.fileId