USE [TimeWorkTracking]
GO
/****** Object:  UserDefinedFunction [dbo].[getCalendar]    Script Date: 21.11.2021 19:18:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Табличная функция для генерации последовательности дат (способ 2 – WITH)
-- https://info-comp.ru/generating-dates-t-sql
   ALTER FUNCTION [dbo].[getCalendar] (
	@DateStart DATEtime, -- Дата начала
	@DateEnd DATEtime	 -- Дата окончания
   )
   RETURNS @ListDates TABLE (dt DATEtime) 
   AS
   BEGIN

	--Рекурсивное обобщенное табличное выражение.
	WITH Dates AS
	(
		SELECT @DateStart AS DateStart -- Задаем якорь рекурсии
	
		UNION ALL

		SELECT DATEADD(DAY, 1, DateStart) AS DateStart -- Увеличиваем значение даты на 1 день
		FROM Dates
		WHERE DateStart < @DateEnd -- Прекращаем выполнение, когда дойдем до даты окончания
	)
	INSERT INTO @ListDates
		SELECT DateStart 
		FROM Dates
		OPTION (MAXRECURSION 0); 
		/*
			Значением 0 снимаем серверное ограничение на количество уровней рекурсии (которое по умолчанию равно 100), 
			чтобы иметь возможность формировать даты в большом диапазоне.
		*/
     RETURN
   END

USE [TimeWorkTracking]
GO
/****** Object:  UserDefinedFunction [dbo].[twt_uploadCalendar]    Script Date: 21.11.2021 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[twt_uploadCalendar]
	(@DateBegin datetime, 
	 @DateEnd datetime)
--'20210102' and '20210115'
Returns table as Return 
(
Select calendar.dt daysCalendar,
       pass.userId,
	   pass.passId,
	   pass.userName,
	   pass.userPost,
	   pass.specialMark,
	   pass.workTime
  From 
   (Select convert(nvarchar(30),dt,112) dt From getCalendar(@DateBegin, @DateEnd)) as calendar
  left join 
   (Select 
	  u.usId userId,                            --id фио 
	  u.usName userName,                        --фио
	  u.usPost userPost,                        --должность
	  e.smName specialMark,                     --специальные отметки
	  e.ptimeScheduleFact workTime,             --количество отработанных минут
	  e.pDate passDate,                         --дата прохода
	  e.pId passId                              --id записи
	 From 
	  (Select 
	     us.extId usId,                         --id фио 
             us.name usName,                        --фио
	     up.name usPost			    --должность
        From Users us, UserPost up  
       Where us.postId = up.id 
	      and us.name like('%' + 'михаил' + '%') 
	      and us.uses = 1) as u 
     left join 
      (Select 
	ep.id pId,                                  --id записи
        ep.passDate pDate,                          --дата прохода 
	ep.passId usId,                             --id фио
        sm.letterCode smName,			    --имя специальных отметок
	ep.timeScheduleFact ptimeScheduleFact	--количество отработанных минут
       From EventsPass ep, SpecialMarks sm 
       Where sm.id=ep.specmarkId
	      and passDate between @DateBegin and @DateEnd) as e 
  on u.usId = e.usId ) as pass

  on pass.passDate=calendar.dt
  )

USE [TimeWorkTracking]
GO
/****** Object:  StoredProcedure [dbo].[SP_Dynamic_Pivot]    Script Date: 21.11.2021 19:14:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 -- Создаем универсальную процедуру для динамического PIVOT   
 -- https://info-comp.ru/obucheniest/631-dynamic-pivot-in-t-sql.html
   ALTER PROCEDURE [dbo].[SP_Dynamic_Pivot]
   (
        @TableSRC NVARCHAR(100),   --Таблица источник (Представление)
        @ColumnName NVARCHAR(100), --Столбец, содержащий значения, которые станут именами столбцов
        @Field NVARCHAR(100),      --Столбец, над которым проводить агрегацию
        @FieldRows NVARCHAR(100),  --Столбец (столбцы) для группировки по строкам (Column1, Column2)
        @FunctionType NVARCHAR(20) = 'SUM',--Агрегатная функция (SUM, COUNT, MAX, MIN, AVG), по умолчанию SUM
        @Condition NVARCHAR(200) = '' --Условие (WHERE и т.д.). По умолчанию без условия
   )
   AS 
   BEGIN
        /*
                Универсальная процедура формирования динамического запроса PIVOT.
                Разработчик Info-Comp.ru
        */
        
        --Отключаем вывод количества строк
        SET NOCOUNT ON;
        
        --Переменная для хранения строки запроса
        DECLARE @Query NVARCHAR(MAX);                     
         --Переменная для хранения имен столбцов
        DECLARE @ColumnNames NVARCHAR(MAX);              
        --Переменная для хранения заголовков результирующего набора данных
        DECLARE @ColumnNamesHeader NVARCHAR(MAX); 

        --Обработчик ошибок
        BEGIN TRY
                --Таблица для хранения уникальных значений, 
                --которые будут использоваться в качестве столбцов      
                CREATE TABLE #ColumnNames(ColumnName NVARCHAR(100) NOT NULL PRIMARY KEY);
        
                --Формируем строку запроса для получения уникальных значений для имен столбцов
                SET @Query = N'INSERT INTO #ColumnNames (ColumnName)
                                                  SELECT DISTINCT COALESCE(' + @ColumnName + ', ''Пусто'') 
                                                  FROM ' + @TableSRC + ' ' + @Condition + ';'
                
                --Выполняем строку запроса
                EXEC (@Query);

                --Формируем строку с именами столбцов
                SELECT @ColumnNames = ISNULL(@ColumnNames + ', ','') + QUOTENAME(ColumnName) 
                FROM #ColumnNames;
                
                --Формируем строку для заголовка динамического перекрестного запроса (PIVOT)
                SELECT @ColumnNamesHeader = ISNULL(@ColumnNamesHeader + ', ','') 
                                                                        + 'COALESCE('
                                                                        + QUOTENAME(ColumnName) 
                                                                        + ', 0) AS '
                                                                        + QUOTENAME(ColumnName)
                FROM #ColumnNames;
        
                --Формируем строку с запросом PIVOT
                SET @Query = N'SELECT ' + @FieldRows + ' , ' + @ColumnNamesHeader + ' 
                                           FROM (SELECT ' + @FieldRows + ', ' + @ColumnName + ', ' + @Field 
                                                         + ' FROM ' + @TableSRC  + ' ' + @Condition + ') AS SRC
                                           PIVOT ( ' + @FunctionType + '(' + @Field + ')' +' FOR ' +  
                                                                   @ColumnName + ' IN (' + @ColumnNames + ')) AS PVT
                                           ORDER BY ' + @FieldRows + ';'
                
                --Удаляем временную таблицу
                DROP TABLE #ColumnNames;

                --Выполняем строку запроса с PIVOT
                EXEC (@Query);
                
                --Включаем обратно вывод количества строк
                SET NOCOUNT OFF;
                
        END TRY
        BEGIN CATCH
                --В случае ошибки, возвращаем номер и описание этой ошибки
                SELECT ERROR_NUMBER() AS [Номер ошибки], 
                           ERROR_MESSAGE() AS [Описание ошибки]
        END CATCH
   END

USE [TimeWorkTracking]
GO
/****** Object:  UserDefinedFunction [dbo].[twt_uploadCalendar]    Script Date: 22.11.2021 11:02:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[twt_uploadCalendar]
	(@DateBegin datetime, 
	 @DateEnd datetime)
--'20210102' and '20210115'
Returns table as Return 
(
Select calendar.dt daysCalendar,
       pass.userId,
	   pass.passId,
	   pass.userName,
	   pass.userPost,
	   pass.specialMark,
	   pass.workTime,
	   pass.pivotStr,
	   pass.pivotXML
  From 
   (Select convert(nvarchar(30),dt,112) dt From getCalendar(@DateBegin, @DateEnd)) as calendar
  left join 
   (Select 
	  u.usId userId,                            --id фио 
	  u.usName userName,                        --фио
	  u.usPost userPost,                        --должность
	  e.smName specialMark,                     --специальные отметки
	  e.ptimeScheduleFact workTime,             --количество отработанных минут
	  e.smName + '||' + CONVERT(varchar(10), e.ptimeScheduleFact) pivotStr,
	  '<inf><sm>' + e.smName + '</sm><time>' + CONVERT(varchar(10), e.ptimeScheduleFact) + '</time></inf>' pivotXML,
	  e.pDate passDate,                         --дата прохода
	  e.pId passId                              --id записи
	 From 
	  (Select 
	     us.extId usId,                         --id фио 
         us.name usName,  						--фио
	     up.name usPost 						--должность
        From Users us, UserPost up  
       Where us.postId = up.id 
	      and us.name like('%' + 'михаил' + '%') 
	      and us.uses = 1) as u 
     left join 
      (Select 
	    ep.id pId,  							--id записи
        ep.passDate pDate,					    --дата прохода 
	    ep.passId usId,                         --id фио
        sm.letterCode smName,					--имя специальных отметок
		ep.timeScheduleFact ptimeScheduleFact	--количество отработанных минут
        From EventsPass ep, SpecialMarks sm 
       Where sm.id=ep.specmarkId
	      and passDate between @DateBegin and @DateEnd) as e 
  on u.usId = e.usId ) as pass

  on pass.passDate=calendar.dt
  )

USE [TimeWorkTracking]
GO
/****** Object:  StoredProcedure [dbo].[SP_Dynamic_Pivot]    Script Date: 22.11.2021 11:03:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 -- Создаем универсальную процедуру для динамического PIVOT   
 -- https://info-comp.ru/obucheniest/631-dynamic-pivot-in-t-sql.html
   ALTER PROCEDURE [dbo].[SP_Dynamic_Pivot]
   (
        @TableSRC NVARCHAR(100),   --Таблица источник (Представление)
        @ColumnName NVARCHAR(100), --Столбец, содержащий значения, которые станут именами столбцов
        @Field NVARCHAR(100),      --Столбец, над которым проводить агрегацию
        @FieldRows NVARCHAR(100),  --Столбец (столбцы) для группировки по строкам (Column1, Column2)
        @FunctionType NVARCHAR(20) = 'SUM',--Агрегатная функция (SUM, COUNT, MAX, MIN, AVG), по умолчанию SUM
        @Condition NVARCHAR(200) = '' --Условие (WHERE и т.д.). По умолчанию без условия
   )
   AS 
   BEGIN
        /*
                Универсальная процедура формирования динамического запроса PIVOT.
                Разработчик Info-Comp.ru
        */
        
        --Отключаем вывод количества строк
        SET NOCOUNT ON;
        
        --Переменная для хранения строки запроса
        DECLARE @Query NVARCHAR(MAX);                     
         --Переменная для хранения имен столбцов
        DECLARE @ColumnNames NVARCHAR(MAX);              
        --Переменная для хранения заголовков результирующего набора данных
        DECLARE @ColumnNamesHeader NVARCHAR(MAX); 

        --Обработчик ошибок
        BEGIN TRY
                --Таблица для хранения уникальных значений, 
                --которые будут использоваться в качестве столбцов      
                CREATE TABLE #ColumnNames(ColumnName NVARCHAR(100) NOT NULL PRIMARY KEY);
        
                --Формируем строку запроса для получения уникальных значений для имен столбцов
                SET @Query = N'INSERT INTO #ColumnNames (ColumnName)
                                                  SELECT DISTINCT COALESCE(' + @ColumnName + ', ''Пусто'') 
                                                  FROM ' + @TableSRC + ' ' + @Condition + ';'
                
                --Выполняем строку запроса
                EXEC (@Query);

                --Формируем строку с именами столбцов
                SELECT @ColumnNames = ISNULL(@ColumnNames + ', ','') + QUOTENAME(ColumnName) 
                FROM #ColumnNames;
                
                --Формируем строку для заголовка динамического перекрестного запроса (PIVOT)
                SELECT @ColumnNamesHeader = ISNULL(@ColumnNamesHeader + ', ','') 
                                                                        + 'COALESCE('
                                                                        + QUOTENAME(ColumnName) 
                                                                        + ', NULL ) AS '         --значение по умолчанию NULL
                                                                        + QUOTENAME(ColumnName)
                FROM #ColumnNames;
        
                --Формируем строку с запросом PIVOT
                SET @Query = N'SELECT ' + @FieldRows + ' , ' + @ColumnNamesHeader + ' 
                                           FROM (SELECT ' + @FieldRows + ', ' + @ColumnName + ', ' + @Field 
                                                         + ' FROM ' + @TableSRC  + ' ' + @Condition + ') AS SRC
                                           PIVOT ( ' + @FunctionType + '(' + @Field + ')' +' FOR ' +  
                                                                   @ColumnName + ' IN (' + @ColumnNames + ')) AS PVT
                                           ORDER BY ' + @FieldRows + ';'
                
                --Удаляем временную таблицу
                DROP TABLE #ColumnNames;

                --Выполняем строку запроса с PIVOT
                EXEC (@Query);
                
                --Включаем обратно вывод количества строк
                SET NOCOUNT OFF;
                
        END TRY
        BEGIN CATCH
                --В случае ошибки, возвращаем номер и описание этой ошибки
                SELECT ERROR_NUMBER() AS [Номер ошибки], 
                           ERROR_MESSAGE() AS [Описание ошибки]
        END CATCH
   END

USE [TimeWorkTracking]
GO
/****** Object:  StoredProcedure [dbo].[SP_twt_TotalReport]    Script Date: 23.11.2021 10:59:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

   ALTER PROCEDURE [dbo].[SP_twt_TotalReport]
   (
		@DateBegin NVARCHAR(100),       --Дата начала периода 
	    @DateEnd NVARCHAR(100)	       --Дата окончания периода   
	)
   AS 
   BEGIN
        /*
                Процедура формирования данных для отчета (использует динамический PIVOT).
        */
        
	--Обработчик ошибок
    BEGIN TRY
        SET NOCOUNT ON;																	--Отключаем вывод количества строк

	    --таблица для формирования имен колонок (для расширененя вренной таблицы и динамического pivot)
		DECLARE @daysRange TABLE(colName NVARCHAR(30), colId int identity(1,1));		--табличная переменная (имя колонки и id колонки)		
		INSERT INTO @daysRange (colName)												--заполним диапазоном дат
			SELECT top 500 convert(NVARCHAR(30),dt,112) dt								--вставим ограничение 500 (2000 столбцов ограничение SQL)
			  FROM getCalendar(@DateBegin, @DateEnd);
--select * from @daysRange

		--столбцы pivot
		--заголовки колонок Pivot
		DECLARE @colNamesHeaderPivot NVARCHAR(MAX);										--sql строка заголовков столбцов для pivot
	    SELECT @colNamesHeaderPivot = ISNULL(@colNamesHeaderPivot + ', ','') 
										+ 'COALESCE('
			                            + QUOTENAME(colName) 
				                        + ', NULL ) AS '								--значение по умолчанию NULL для агрегатной функции MAX
					                    + QUOTENAME(colName)
		  FROM @daysRange;
--select @colNamesHeaderPivot

		--перечень колонок Pivot
		DECLARE @colNamesPivot NVARCHAR(MAX);											--sql строка столбцов для pivot
	    SELECT @colNamesPivot = ISNULL(@colNamesPivot + ', ','') 
										+ QUOTENAME(colName)	--сформируем ее
		  FROM @daysRange;
--select @colNamesPivot

	    --временная таблица Report
		DECLARE @max int,																--переменная для цикла			
				@id int,																--переменная для цикла
				@Query NVARCHAR(MAX),   												--переменная для хранения строки запроса
				@colName NVARCHAR(50)													--наименование заголовка столбца

		SET @id = 1
		SELECT @max = MAX(colId) FROM @daysRange
		CREATE TABLE #twt_Report(userID NVARCHAR(30));				--создадим временную таблицу для отчета
		WHILE (@id <= @max)																--расширим ее нужным количеством столбцов
			BEGIN																		--**возможно не самое красивое решение
				SELECT @colName = colName FROM @daysRange WHERE colId = @id
				SET @Query = 'ALTER TABLE #twt_Report ADD '+ QUOTENAME(@colName) +' NVARCHAR(50);'
				EXEC(@Query)																
				SET @id = @id +1
			END
--select * from #twt_Report

        --Формируем строку с запросом PIVOT и вставкой в таблицу Report
		DECLARE @TableSRC NVARCHAR(100),   --Таблица источник (Представление)
				@ColumnName NVARCHAR(100), --Столбец, содержащий значения, которые станут именами столбцов
				@Field NVARCHAR(100),      --Столбец, над которым проводить агрегацию
				@FieldRows NVARCHAR(100),  --Столбец (столбцы) для группировки по строкам (Column1, Column2)
				@FunctionType NVARCHAR(20),--Агрегатная функция (SUM, COUNT, MAX, MIN, AVG), по умолчанию SUM
				@Condition NVARCHAR(200)   --Условие (WHERE и т.д.). По умолчанию без условия


        SET @TableSRC = '(select * from twt_uploadCalendar(''' + @DateBegin + ''', ''' + @DateEnd + ''')) pivotSrc';
        SET @ColumnName = 'daysCalendar';
        SET @Field = 'pivotXML';         
        SET @FieldRows = 'userId';       
        SET @FunctionType = 'MAX';        
		SET @Condition = '';
        SET @Query = N'SELECT ' + @FieldRows + ' , ' + @colNamesHeaderPivot + ' 
                         FROM (SELECT ' + @FieldRows + ', ' + @ColumnName + ', ' + @Field 
                             + ' FROM ' + @TableSRC  + ' ' + @Condition + ') AS SRC
                       PIVOT ( ' + @FunctionType + '(' + @Field + ')' +' FOR ' +  
                                   @ColumnName + ' IN (' + @colNamesPivot + ')) AS PVT
                       ORDER BY ' + @FieldRows + ';'
--select @Query

		INSERT INTO #twt_Report EXEC(@Query)						--вставим результаты запроса во временную таблицу Отчета

		--то ради чего...
		SELECT *
		  FROM 
			(SELECT 
				u.name fio, 
				p.name post, 
				u.uses access, 
				u.extId extId 
			  FROM Users u, UserDepartment d, UserPost p--, UserWorkScheme w 
             WHERE u.departmentId = d.Id 
	 	        AND u.name like('%' + 'михаил' + '%') 
			    AND u.postId = p.id 
				AND	u.uses = 1) as u
		left join
		  (SELECT * 
		     FROM #twt_Report 
		  ) as r 
		on u.ExtId = r.userID

		DROP TABLE #twt_Report;							--удалим временную таблицу отчета
		                
		SET NOCOUNT OFF;								--Включаем обратно вывод количества строк
	END TRY
    BEGIN CATCH
		--В случае ошибки, возвращаем номер и описание этой ошибки
        SELECT ERROR_NUMBER() AS [Номер ошибки], 
               ERROR_MESSAGE() AS [Описание ошибки]
	END CATCH
	END

USE [TimeWorkTracking]
GO
/****** Object:  UserDefinedFunction [dbo].[getCalendar]    Script Date: 23.11.2021 12:56:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
   ALTER FUNCTION [dbo].[getCalendar] (
	@fromdate DATEtime, -- Дата начала
	@todate DATEtime	-- Дата окончания
   )
   RETURNS @tcaldate TABLE (dt nvarchar(30)) 
   AS
   BEGIN
	 
	 INSERT INTO @tcaldate 
       SELECT TOP (DATEDIFF(DAY, @fromdate, @todate) + 1)
              convert(nvarchar(30), DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @fromdate),112)
         FROM sys.all_objects a
              CROSS JOIN sys.all_objects b;

     RETURN 
   END