Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 exists(
- SELECT TOP 1 1 FROM Tasks T WITH(NOLOCK)
- join RoleUsers RU WITH(NOLOCK) on T.RoleID = RU.ID
- JOIN Subordinates S WITH(NOLOCK) on RU.UserID = S.ID
- WHERE T.ID = VRT.ID
- )
- OR (@IsHead = 1 AND (BOE.ActiveBackDepartmentID <> @DepartmentID OR BOE.ActiveBackDepartmentID IS NULL) AND VRT.BODepartmentID = @DepartmentID AND VRT.PerformerID IS NOT NULL)
- )
- --«Дежурный БЭК» видит запросы только по тем модулям, что в явном виде указаны в справочнике «Сотрудники подразделений БЭК-офисов» в поле «Наблюдаемые модули»
- -- приоритет логики дежурного меньше чем админа и руководителя
- 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
- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement