SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
===========================================================================================================================================================================================================================
-- Author:		dgtfgh
-- Create date:	
-- ALTER DATE:
--		28.11.2018 gfhgfh исправление c уровнем рекурсии при поиске пропущеного номера
--		17.09.2018, 13.09.2018 gfjhjhgjbn - номер документа первый незанятый в течение года  
--		12.12.2017
-- Description:	Формирует номер документа по ПУ
-- ===========================================================================================================================================================================================================================
ALTER PROCEDURE [XAF].[DD_Generate_Number_For_Device_Documents]
AS
BEGIN
	SET NOCOUNT, XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL ON
	SET NUMERIC_ROUNDABORT, CURSOR_CLOSE_ON_COMMIT OFF

-- =======================================================================================================================
-- очистка бронированных номеров старше 1 дня 
-- =======================================================================================================================
	DECLARE @date datetime;
	-- округляем дату до 00:00 и вычитаем 1 день
	SELECT @Date = DATEADD(day, -1, CONVERT(varchar(8), GETDATE(), 112));

	DELETE XAF.CD_Bookings_Doc_Number
	WHERE D_Date < @Date
-- =======================================================================================================================
	
	DECLARE @DocNumbers TABLE(N_Number INT)
	DECLARE @NextNumber INT, 
			@MaxNextNumber INT 
 
	-- получаем все используеммые номера за текущий год 
	INSERT INTO @DocNumbers
	SELECT
		CASE WHEN (ISNUMERIC(dd.C_Number) = 1 AND dd.C_Number NOT LIKE '%[.,]%') THEN CAST(dd.C_Number AS INT) END  -- только коректные номера 
	FROM dbo.DD_Docs dd 
		INNER JOIN dbo.DS_Docum_Types ddt
			ON ddt.LINK = dd.F_Docum_Types
				AND ddt.C_Const IN ('DST_ToleranceFirst', 'DST_ToleranceNext', 'DST_Checkup', 'DST_Return', 'DST_Survey', 'DST_Survey_TES', 'DST_Checkup_TES', 'DST_Return_TES', 'DST_ControlCheckup','DST_AnnulateAct','DST_TU_Giving', 'DST_UU_Matching')
	WHERE YEAR(dd.D_Register_Date) = YEAR(GETDATE()) 
	--order by CASE WHEN (ISNUMERIC(dd.C_Number) = 1 AND dd.C_Number NOT LIKE '%[.,]%') THEN CAST(dd.C_Number AS INT) END
	-- максимальный из используеммых и забронированных номеров
	SELECT 	@MaxNextNumber = ISNULL(MAX(t.MaxNumber), 0) +1
	--SELECT 	@MaxNextNumber = ISNULL(MAX(t.MaxNumber), 0) +1
	FROM (	SELECT ISNULL(MAX(N_Number), 0) AS MaxNumber FROM @DocNumbers dn 
			UNION
			SELECT ISNULL(MAX(CAST(C_Number AS INT)), 0) FROM XAF.CD_Bookings_Doc_Number 
			WHERE YEAR(D_Date) = YEAR(GETDATE()) 
		) t

	;
	WITH Numbers(Number) AS 
	(
		SELECT 1
        UNION ALL
		SELECT Number + 1 FROM Numbers WHERE Number < @MaxNextNumber
	)
	SELECT TOP 1
			@NextNumber = n.Number 
	FROM Numbers n
		LEFT JOIN @DocNumbers dn 
			ON n.Number = dn.N_Number
		LEFT JOIN XAF.CD_Bookings_Doc_Number bdn
			ON CAST(bdn.C_Number AS INT) = n.Number
			AND YEAR(bdn.D_Date) = YEAR(GETDATE()) 
 WHERE dn.N_Number IS NULL 
		AND bdn.C_Number IS NULL
	OPTION (MAXRECURSION 0)

	INSERT INTO XAF.CD_Bookings_Doc_Number
	SELECT @NextNumber, GETDATE()
	
	SELECT @NextNumber
END

USE [Om*]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [XXX].[DD_GeXXXXXXX]
AS
BEGIN
	SET NOCOUNT, XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL ON
	SET NUMERIC_ROUNDABORT, CURSOR_CLOSE_ON_COMMIT OFF

-- =======================================================================================================================
-- очистка бронированных номеров старше 1 дня 
-- =======================================================================================================================
	DECLARE @date datetime;
	-- округляем дату до 00:00 и вычитаем 1 день
	SELECT @Date = DATEADD(day, -1, CONVERT(varchar(8), GETDATE(), 112));

	DELETE XAF.CD_Bookings_Doc_Number
	WHERE D_Date < @Date
-- =======================================================================================================================
	DECLARE @DocNumbers TABLE(C_Number INT)
	DECLARE @NextNumber INT
	DECLARE @MaxNextNumber INT 

 INSERT INTO @DocNumbers
	-- получаем все используеммые номера за текущий год 
	     SELECT df.C_Number
			 FROM

			  (SELECT (V1_100.RN_100-1)*100 +V2_100.RN_100 AS C_Number
              FROM 
              (
                       SELECT (V1.RN - 1)*10 + V2.RN AS RN_100 
                       FROM
                       (
                       SELECT 1 AS RN UNION ALL 
                       SELECT 2 AS RN UNION ALL 
                       SELECT 3 AS RN UNION ALL
                       SELECT 4 AS RN UNION ALL 
                       SELECT 5 AS RN UNION ALL 
                       SELECT 6 AS RN UNION ALL
                       SELECT 7 AS RN UNION ALL 
                       SELECT 8 AS RN UNION ALL 
                       SELECT 9 AS RN UNION ALL 
                       SELECT 10 AS RN
                       ) V1
                       CROSS JOIN
                       (SELECT 1 AS RN UNION ALL 
                       SELECT 2 AS RN UNION ALL 
                       SELECT 3 AS RN UNION ALL
                       SELECT 4 AS RN UNION ALL 
                       SELECT 5 AS RN UNION ALL 
                       SELECT 6 AS RN UNION ALL
                       SELECT 7 AS RN UNION ALL 
                       SELECT 8 AS RN UNION ALL 
                       SELECT 9 AS RN UNION ALL 
                       SELECT 10 AS RN) V2
                ) V1_100
                CROSS JOIN
                (
              SELECT (V1.RN - 1)*10 +V2.RN AS RN_100
              FROM
              (
			  SELECT 1 AS RN UNION ALL 
              SELECT 2 AS RN UNION ALL 
              SELECT 3 AS RN UNION ALL
              SELECT 4 AS RN UNION ALL 
              SELECT 5 AS RN UNION ALL 
              SELECT 6 AS RN UNION ALL
              SELECT 7 AS RN UNION ALL 
              SELECT 8 AS RN UNION ALL 
              SELECT 9 AS RN UNION ALL 
              SELECT 10 AS RN
			  ) V1

              CROSS JOIN 
              (
			  SELECT 1 AS RN UNION ALL 
              SELECT 2 AS RN UNION ALL 
              SELECT 3 AS RN UNION ALL
              SELECT 4 AS RN UNION ALL 
              SELECT 5 AS RN UNION ALL 
              SELECT 6 AS RN UNION ALL
              SELECT 7 AS RN UNION ALL 
              SELECT 8 AS RN UNION ALL 
              SELECT 9 AS RN UNION ALL 
              SELECT 10 AS RN
			  ) V2
              ) V2_100
       ) AS DF
 LEFT JOIN 
 (select distinct C_number,C_number2 from
(
SELECT 
dd.C_NUMBER,  dd.N_Number
,case when dd.C_Number is null or  dd.C_Number = ' '   then 'a' else 'b' end C_number1
,CASE WHEN (ISNUMERIC(dd.C_Number) = 1 AND dd.C_Number NOT LIKE '%[.,]%') THEN CAST(dd.C_Number AS INT) END C_number2 -- только коректные номера 
FROM dbo.DD_Docs dd
INNER JOIN dbo.DS_Docum_Types ddt ON ddt.LINK = dd.F_Docum_Types
where ddt.C_Const IN 
('DST_ToleranceFirst', 'DST_ToleranceNext', 'DST_Checkup', 'DST_Return', 'DST_Survey', 'DST_Survey_TES', 'DST_Checkup_TES', 'DST_Return_TES', 'DST_ControlCheckup','DST_AnnulateAct','DST_TU_Giving', 'DST_UU_Matching')
and YEAR(dd.D_Register_Date) = YEAR(GETDATE())  ) t
where C_number not in  ('a','БДИ') 

) AS FD
ON DF.C_Number=FD.C_NUMBER
WHERE FD.C_NUMBER IS NULL

	INSERT INTO XAF.CD_Bookings_Doc_Number
	SELECT min(C_Number), GETDATE()  from @DocNumbers	
    SELECT C_Number from XAF.CD_Bookings_Doc_Number
END