Untitled

DECLARE @PAGEOFFSET_1 int = 1;
DECLARE @PAGELIMIT_1 int = 20;
DECLARE @CURRENTUSERID_1 uniqueidentifier = '3db19fa0-228a-497f-873a-0250bf0a4ccb';
DECLARE @LOCALE_1 int = 25;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Представление Vtb24AllRequests
--  TODO:
--      упростить алгоритм подсветки
--      ключ на уникальность поля EmployeeID в Vtb24DictBackOfficeEmployee
--      убрать сортировку по полям *Name и выпилить лишние индексы по ним
--      убрать лишние STUFF
--      убрать лишние JOIN-ы во втором блоке
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @DepartmentID UNIQUEIDENTIFIER;
DECLARE @IsAdmin BIT;
DECLARE @IsModuleAdmin BIT;
DECLARE @IsHead BIT;
DECLARE @NewLineChar AS NVARCHAR(1) = CHAR(10);
DECLARE @IsDuty BIT;

--Получение прав текущего сотрудника
SELECT TOP 1
        @DepartmentID = ActiveBackDepartmentID
        ,@IsAdmin = IsAdmin
        ,@IsModuleAdmin = IsModuleAdmin
        ,@IsHead = IsHead
        ,@IsDuty = IsDuty
FROM Vtb24DictBackOfficeEmployee WITH(NOLOCK)
WHERE EmployeeID = @CURRENTUSERID_1;

--Основной запрос
;WITH

        --Выбор подчиненных сотрудников
        Subordinates (ID) as
        (
                SELECT DISTINCT
                        BOE.EmployeeID AS ID
                    FROM Vtb24DictVtb24DictHeadSubordinates SUB WITH(NOLOCK)
                        LEFT JOIN Vtb24DictBackOfficeEmployee BOE WITH(NOLOCK)
                                ON SUB.ID = BOE.ID
                        LEFT JOIN Vtb24DictBackOfficeEmployee BOE1 WITH(NOLOCK)
                                ON SUB.EmployeeID = BOE1.ID
                        WHERE
                                BOE1.EmployeeID = @CURRENTUSERID_1
        ),

        SelectTasks(ID) as
        (
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--1. Выбор ID заданий по параметрам. Тут нужно указывать ТОЛЬКО параметры поиска, выбираются ID заданаий, другие поля НЕ нужны. Связь таблиц 1 к 1.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                SELECT
                        VRT.ID
                FROM Vtb24KsTasks                               VRT     WITH(NOLOCK)
                LEFT JOIN Vtb24DictProductsGroupProducts        PGP     WITH(NOLOCK) ON VRT.ProductID = PGP.ProductID --поле PGP.ProductID уникальное, связь 1 к 1
                LEFT JOIN Vtb24DictProductsGroups               PG      WITH(NOLOCK) ON PGP.ID = PG.ID
                LEFT JOIN Vtb24DictOperationTypes               OT      WITH(NOLOCK) ON OT.ID = VRT.OperationTypeID
                LEFT JOIN Vtb24ClLegalClients                   LC      WITH(NOLOCK) ON LC.ID = VRT.LegalClientID

                LEFT JOIN Vtb24DictBackOfficeEmployee           BOE     WITH(NOLOCK) ON VRT.PerformerID = BOE.EmployeeID --TODO: НУЖНО ДОБАВИТЬ УНИКАЛЬНЫЙ КЛЮЧ НА BOE.EmployeeID
                LEFT JOIN Subordinates                          SB      WITH(NOLOCK) ON VRT.PerformerID = SB.ID --поле SB.ID уникальное, связь 1 к 1

                WHERE 1=1


                        --Активные задания
                        AND (VRT.TaskStateID IN (9, 10, 11, 13, 14, 15) OR (VRT.TaskStateID NOT IN (7, 12, 17, 18, 19, 24) AND VRT.OnRework IS NOT NULL AND VRT.OnRework = 1 ) )
                        AND EXISTS
                        (
                            SELECT TOP 1 1
                            FROM Tasks T WITH(NOLOCK)
                            WHERE
                                T.ID = VRT.ID
                                --AND T.TypeID IN ('dd6d16f3-8652-4c7c-b3bd-d004a69db02e', '59a656c4-5328-4037-a09a-11b861f57f1c')
                        )

                        --Задания подчиненных
                        AND (SB.ID IS NOT NULL
                                OR (@IsHead = 1 AND (BOE.ActiveBackDepartmentID <> @DepartmentID OR BOE.ActiveBackDepartmentID IS NULL)  AND VRT.BODepartmentID = @DepartmentID  AND VRT.PerformerID IS NOT NULL)
                                OR (VRT.BODepartmentID = @DepartmentID AND VRT.PerformerID IS NULL AND VRT.TaskStateID = 9)
                        )

                --«Дежурный БЭК» видит запросы только по тем модулям, что в явном виде указаны в справочнике «Сотрудники подразделений БЭК-офисов» в поле «Наблюдаемые модули»
                -- приоритет логики дежурного меньше чем админа и руководителя
                AND (
                        (
                                -- Логика для обычного сотрудника
                                (@IsAdmin IS NULL OR @IsAdmin = 0)
                                AND(@IsModuleAdmin IS NULL OR @IsModuleAdmin = 0)
                                AND(@IsHead IS NULL OR @IsHead = 0)
                                AND(@IsDuty IS NULL OR @IsDuty = 0)
                        )
                        OR
                        (
                                -- Логика для дежурного БЭК
                                @IsDuty = 1
                                AND(@IsAdmin IS NULL OR @IsAdmin = 0)
                                AND(@IsModuleAdmin IS NULL OR @IsModuleAdmin =0)
                                AND(@IsHead  IS NULL OR @IsHead =0)
                                AND EXISTS
                                (
                                        SELECT TOP 1 1
                                        FROM Vtb24DictBackOfficeEmployee    BOE WITH(NOLOCK)
                                        LEFT JOIN Vtb24DictDutyModules  DDM WITH(NOLOCK) ON DDM.ID = BOE.ID
                                        WHERE BOE.[EmployeeID] = @CURRENTUSERID_1 AND DDM.ModuleID = OT.ModuleID
                                )
                        )
                        OR
                        (
                                --логика для руководителя / админа
                                @IsAdmin = 1
                                OR @IsModuleAdmin = 1
                                OR @IsHead = 1
                        )
                )
                -- Проверка доступа, администратор видит все запросы, руководитель все запросы своего подразделения, сотрудник видит только свои запросы

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        )

        ,Pagination(ID, AppearanceID, rn) as
        (
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--2. Постраничное отображение и сортировка. При подсчете строк не требуется.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                SELECT TOP (@PAGELIMIT_1)
                        P.ID
                        ,P.AppearanceID
                        ,P.rn
                FROM (
                        SELECT
                                R.ID
                                ,R.AppearanceID

                                ,row_number() OVER (ORDER BY R.AppearanceID, VRT.Number desc ) AS rn

                        FROM (
                                SELECT
                                        S.ID
                                        ,(CASE
                                                WHEN VRTT.TaskStateID = 11 THEN 1 --отложено (7, 9, 10, 11, 12, 13, 14, 15, 17, 18, 19, 24)
                                                WHEN VRTT.PlannedDate < GETUTCDATE() AND VRTT.TaskStateID IN (9, 10, 11, 13, 14, 15) THEN 0 --просрочено
                                                WHEN VRTT.TaskStateID IN (14, 15) THEN 2 --отозвано
                                                WHEN VRTT.UrgencyID = 1 AND VRTT.TaskStateID IN (9, 10, 13)  THEN 3 --срочные
                                                WHEN VRTT.PlannedDate >= GETUTCDATE() AND VRTT.PlannedDate < (GETUTCDATE()+1) AND VRTT.TaskStateID IN (9, 10, 13)  THEN 4 --сегодняшние
                                                ELSE 5
                                         END ) AS AppearanceID
                                FROM SelectTasks S
                                INNER JOIN Vtb24KsTasks VRTT WITH(NOLOCK) ON VRTT.ID=S.ID
                        ) R
                        INNER JOIN Vtb24KsTasks             VRT WITH(NOLOCK) ON VRT.ID=R.ID
                        INNER JOIN Instances                    INS WITH(NOLOCK) ON INS.ID=VRT.ID --TODO: выпилить
                        INNER JOIN Vtb24DictOperationTypes  OT WITH(NOLOCK) ON OT.ID = VRT.OperationTypeID --TODO: выпилить
                ) P
                WHERE P.rn >= @PAGEOFFSET_1 AND P.rn < (@PAGEOFFSET_1 + @PAGELIMIT_1)
                ORDER BY P.rn
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        )

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--3. Выбор дополнительных полей для отображении в представлении. Тут НЕ нужно добавлять условия поиска.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        SELECT
                P.rn
                ,T.ID                           AS Vtb24AllRequestsID
                ,T.Number                       AS Vtb24AllRequestsNumber
                ,T.ContractNumber
                ,T.ContractDate
                ,T.LegalClientINN               AS Vtb24AllRequestsClientINN
                ,T.NumberBatchRequestID     AS Vtb24AllRequestsBatchRequestID
                ,T.NumberBatchRequestNumber AS Vtb24AllRequestsBatchRequestNumber
                ,T.InitiatorName                    AS Vtb24AllRequestsInitiatorName
                ,T.BODepartmentName         AS Vtb24AllRequestsBODepartment
                ,T.TaskStateID                  AS Vtb24AllRequestsStateID
                ,T.BODepartmentID               AS Vtb24AllRequestsBODepartmentID
                ,T.DealStartDate                    AS Vtb24AllRequestsCreated
                ,T.OperationTypeID              AS Vtb24AllRequestsOperationTypeID
                ,T.OperationTypeName            AS Vtb24AllRequestsOperationTypeName
                ,T.PlannedDate                  AS Vtb24AllRequestsPlanned
                ,T.DatePerformanceRejection     AS Vtb24AllRequestsDatePerformanceRejection
                ,T.PerformerID                  AS Vtb24AllRequestsPerformerID
                ,T.PerformerName                AS Vtb24AllRequestsPerformerName
                ,T.UrgencyName                  AS Vtb24AllRequestsPriority
                ,OT.ModuleID                    AS Vtb24AllRequestsModuleID
                ,OT.ModuleName              AS Vtb24AllRequestsModuleName

                ,CASE
                    WHEN P.AppearanceID = 0 THEN N'#appearance( Background: #A0ff9999)' -- просрочено
                    WHEN P.AppearanceID = 1 THEN N'#appearance( Background: #A0afb3d8)' -- отложено
                    WHEN P.AppearanceID = 2 THEN N'#appearance( Background: #A0ffff00)'     -- отозвано
                    WHEN P.AppearanceID = 3 THEN N'#appearance( Background: #A058D039)' -- срочные
                    WHEN P.AppearanceID = 4 THEN N'#appearance( Background: #A07fD3C8)' -- сегодняшние
                END AS AppearanceColumn

                 ,IIF(
                    INS.TypeID = '3F2E6367-F154-404D-BC45-E08D50A3F177', 'П',
                    IIF(INS.TypeID = '0242f68a-56de-469f-911b-fa827c71938e', 'М', '-')
                 ) AS MultiOrBatchTypeCaption

                ,CASE
                    WHEN T.TaskStateID = 9 AND T.MemorizedStateID IS NOT NULL
                    THEN T.MemorizedStateName ELSE T.TaskStateName
                END AS Vtb24AllRequestsStateName

                ,CASE T.IsNotProductOperation
                    WHEN 0 THEN (
                        ISNULL(
                            STUFF((
                                SELECT @NewLineChar + PDC.ClientName + N';'
                                FROM Vtb24KsProductDossierCollection PDC WITH(NOLOCK)
                                WHERE PDC.ID = T.ID
                                FOR XML PATH(N'')
                            ), 1, 1, N''),
                            ISNULL(T.LegalClientShortName, T.NaturalClientFullName)
                        )
                    )
                    ELSE T.NotProductClient
                END AS Vtb24AllRequestsClientName

                ,CASE T.IsNotProductOperation
                    WHEN 0 THEN (
                        STUFF((
                            SELECT @NewLineChar + PDC.ProductDossierNumber + N';'
                            FROM Vtb24KsProductDossierCollection PDC WITH(NOLOCK)
                            WHERE PDC.ID = T.ID
                            FOR XML PATH(N'')
                        ), 1, 1, N'')
                    )
                    ELSE T.NotProductContractNumber
                END AS Vtb24AllRequestsContractNumber

                ,STUFF((
                    SELECT @NewLineChar + format(PDC.ProductDossierDate, N'dd.MM.yyyy') + N';'
                    FROM Vtb24KsProductDossierCollection PDC WITH(NOLOCK)
                        WHERE PDC.ID = T.ID
                    FOR XML PATH(N'')
                ), 1, 1, N'') AS Vtb24AllRequestsContractDate

                ,STUFF((
                    SELECT @NewLineChar + PDC.ProductDossierAccountNumber + N';'
                    FROM Vtb24KsProductDossierCollection PDC WITH(NOLOCK)
                        WHERE PDC.ID = T.ID
                    FOR XML PATH(N'')
                ), 1, 1, N'') AS Vtb24AllRequestsAccountNumber

                ,ISNULL(
                    STUFF((
                        SELECT @NewLineChar + PDC.ProductsGroupGroupName + N';'
                        FROM Vtb24KsProductDossierCollection PDC WITH(NOLOCK)
                            WHERE PDC.ID = T.ID
                        FOR XML PATH(N'')
                    ), 1, 1, N''),
                    T.ProductsGroupGroupName
                ) AS Vtb24AllRequestsProductGroupName

                ,ISNULL(
                    STUFF((
                        SELECT @NewLineChar + PDC.ProductsGroupSystem + N';'
                        FROM Vtb24KsProductDossierCollection PDC WITH(NOLOCK)
                            WHERE PDC.ID = T.ID
                        FOR XML PATH(N'')
                    ), 1, 1, N''),
                    T.ProductsGroupSystem
                ) AS Vtb24AllRequestsSystemProductsGroup

                ,ISNULL(
                    STUFF((
                        SELECT @NewLineChar + PDC.BranchGroupName + N';'
                        FROM Vtb24KsProductDossierCollection PDC WITH(NOLOCK)
                            WHERE PDC.ID = T.ID
                        FOR XML PATH(N'')
                    ), 1, 1, N''),
                    T.BranchGroupsName
                ) AS Vtb24AllRequestsBranchDescription

                ,BOE.ActiveBackDepartmentID AS CurrentPerformerDepartment

        FROM Pagination P
        INNER JOIN Vtb24KsTasks             T WITH(NOLOCK) ON T.ID=P.ID
        LEFT JOIN Instances                     INS WITH(NOLOCK) ON INS.ID = T.NumberBatchRequestID
        LEFT JOIN Vtb24DictOperationTypes       OT WITH(NOLOCK) ON OT.ID = T.OperationTypeID

        LEFT JOIN Vtb24DictBackOfficeEmployee BOE WITH(NOLOCK) ON T.PerformerID = BOE.EmployeeID

        ORDER BY P.rn
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------