Руководство администратора

(Если у файла было несколько версий, возвращается последняя версия. Превью — необязательная колонка, отображается корректно только для изображений)

CREATE   PROCEDURE [dbo].[sp_test_rev_Linked_Files]
  @UserID int,
  @TaskID int,
  @UserNameMode varchar(8000),
  @ShowAllTasks varchar(8000),
  @StartIndex varchar(8000),
  @EndIndex varchar(8000)
 
AS
BEGIN
 
 
select t.taskID as TaskID,
  t.IsOverdue,
  t.StateID,
  t.PriorityID,
  t.IsClosed,
  TaskColor.IntColor,
  s.FinishWork,
  t.ParentTaskID,
  fsf.Name [Файл],
case
    when fsf.Ext in ('.jpg','.png','.pdf','.mp4') then
       '/spa/noframe/file/{fileID}/1'
    when fsf.Ext in ('.doc','.docx','.ppt','.pptx', '.xls', '.xlsx') then
       '/spa/noframe/file/{fileID}?mode=view'

end
FROM Tasks t
  left join TaskColor on t.taskid=TaskColor.TaskId
  left join States as s on t.StateID=s.StateID
  join FileStorageFileToTaskLinks as fs on t.TaskID = fs.TaskId and fs.IsDeleted=0
  join FileStorageFiles as fsf on fs.FileId=fsf.FileId
WHERE t.TaskID = @TaskID
 
union all
 
select t.taskID as TaskID,
  t.IsOverdue,
  t.StateID,
  t.PriorityID,
  t.IsClosed,
  TaskColor.IntColor,
  s.FinishWork,
  t.ParentTaskID,
  fsf.Name,
case
    when fsf.Ext in ('.jpg','.png','.pdf','.mp4') then
       '/spa/noframe/file/{fileID}/1'
    when fsf.Ext in ('.doc','.docx','.ppt','.pptx', '.xls', '.xlsx') then
       '/spa/noframe/file/{fileID}?mode=view'

end
FROM Tasks t
  left join TaskColor on t.taskid=TaskColor.TaskId
  left join States as s on t.StateID=s.StateID
  join FileStorageFileToExtParamLinks as fs on t.TaskID = fs.TaskId and fs.IsDeleted=0
  join FileStorageFiles as fsf on fs.FileId=fsf.FileId
WHERE t.TaskID = @TaskID
 
END

В данном примере категория с ID=22 — это контактные лица клиентов, в ней есть ДП с ID=33, где хранится email контактного лица.

ALTER PROCEDURE [dbo].[sp_ClientsEmails]
 @UserID INT = null,
 @TaskID INT,
 @UserNameMode INT =null,
 @ShowAllTasks bit = 0,
 @StartIndex INT = null,
 @EndIndex INT = null
--@a bit=0
AS
BEGIN

; with ClientEmails as (
SELECT DISTINCT t22.ExtParam33Value as email
FROM TasksInSubcat22Denormalized t22 with (nolock)
WHERE t22.ExtParam33NativeValue = @taskid
  and t22.ExtParam33Value <> ''
)

 
SELECT
    e.DateCreated as [Дата]
    ,e.[From] as [От]
    ,e.[To] as [Кому]
    ,e.[Subject] as [Тема]
    ,e.[Textbody] as [Текст]
    ,@TaskID as [TaskID]
    ,cast(0 as bit) as [IsOverdue]
    ,1 as [StateID]
    ,1 as[PriorityID]
    ,cast(0 as bit) as [IsClosed]
    ,NULL as [IntColor]
    ,NULL as [FinishWork]
    ,NULL as [ParentTaskID]
    ,'/Emails/EmailView.aspx?folderId=' + cast(emf.FolderID as varchar(10)) + '&EmailID=' + cast(e.emailid as varchar(15)) as [href]
FROM [dbo].[EmailAddressBooks] as eb
 INNER JOIN [dbo].[EmailAddressBooksEmails] as ebe
         ON eb.EntryId = ebe.EntryId and ebe.RecipientType = 0
 INNER JOIN emails e with (nolock)
         ON e.EmailID = ebe.EmailId
 JOIN EmailMailBoxesFoldersEmails emf
         ON e.EmailID = emf.EmailId
WHERE eb.EmailAddress in (SELECT email FROM ClientEmails)
ORDER BY [Дата] desc

END