CREATE PROCEDURE [dbo].[API_EXPORT_SPD] ( @BANK_NUMBER char(9), @RESIDENT_GUID varchar(40) = NULL, @SPD_GUID varchar(40) = NULL, @SPD_DATE_FROM date = NULL, @SPD_DATE_TO date = NULL, @CONTRACT_REGISTRATION_GUID varchar(40) = NULL, @CONTRACT_REGISTRATION_NUMBER char(22) = NULL, @CONTRACT_REGISTRATION_DATE_FROM date = NULL, @CONTRACT_REGISTRATION_DATE_TO date = NULL, @CONTRACT_STATUS int = NULL, @PD_GUID varchar(40) = NULL, @PD_NUMBER varchar(100) = NULL, @WITH_ATTACHMENTS bit = 0, @VER_SPD varchar(5) = NULL, @RESULT int = NULL OUTPUT, @COUNT int = NULL OUTPUT, @XML_REPLY xml = NULL OUTPUT ) WITH ENCRYPTION AS BEGIN TRY DECLARE @BANK_ID int IF @BANK_NUMBER IS NULL BEGIN RAISERROR (N'Не указан Филиал Банка, для которого осуществляется выгрузка документов.', 16, 0) END SELECT @BANK_ID = BANK_ID FROM BANKS WHERE REGNB = @BANK_NUMBER AND DEL_FLAG IS NULL IF @BANK_ID IS NULL BEGIN RAISERROR (N'Филиал Банка с регистрационным/порядковым номером «%s» не найден.', 16, 0, @BANK_NUMBER) END IF DATEADD(day, 14, ( SELECT KEY_PRG_D_END FROM BANKS WHERE BANK_ID = @BANK_ID )) < CONVERT(date, GETDATE()) BEGIN RAISERROR (N'Срок действия ключа программного средства АСВКБ истёк. Обратитесь в отдел по работе с клиентами НТЦНТ.', 16, 0) END IF ISNULL(( SELECT KEY_PRG_OPTIONS FROM BANKS WHERE BANK_ID = @BANK_ID ), 0) & 128 <> 128 BEGIN RAISERROR (N'Для выгрузки документов необходимо приобрести опцию "Программные интерфейсы (API) для интеграции с внешними информационными системами". Обратитесь в отдел по работе с клиентами НТЦНТ.', 16, 0) END IF @CONTRACT_STATUS IS NOT NULL AND @CONTRACT_STATUS NOT IN (0, 1) BEGIN RAISERROR (N'Неизвестное значение статуса контракта (кредитного договора): %i.', 16, 0, @CONTRACT_STATUS) END IF @VER_SPD IS NOT NULL AND @VER_SPD NOT IN ('3.0', '2.0') BEGIN RAISERROR (N'Неизвестный номер версии документа СПД: %s.', 16, 0, @VER_SPD) END IF UPPER(@PD_NUMBER) IN ('Б/Н', 'Б\Н', 'БН') SET @PD_NUMBER = 'БН' DECLARE @D_PS_FROM_YEAR int, @D_PS_FROM_MONTH int, @D_PS_TO_YEAR int, @D_PS_TO_MONTH int SELECT @D_PS_FROM_YEAR = YEAR(@CONTRACT_REGISTRATION_DATE_FROM), @D_PS_FROM_MONTH = MONTH(@CONTRACT_REGISTRATION_DATE_FROM), @D_PS_TO_YEAR = YEAR(@CONTRACT_REGISTRATION_DATE_TO), @D_PS_TO_MONTH = MONTH(@CONTRACT_REGISTRATION_DATE_TO) SELECT @XML_REPLY = ( SELECT dbo.SPR_CONFIRM_TO_XML_API (SPD.BANK_ID, SPD.RESIDENT_ID, SPD.PS_ID, SPD.SPR_ID, @WITH_ATTACHMENTS) FROM SPR_CONFIRM AS SPD INNER JOIN BANKS ON BANKS.BANK_ID = SPD.BANK_ID AND BANKS.DEL_FLAG IS NULL INNER JOIN RESIDENTS ON RESIDENTS.BANK_ID = SPD.BANK_ID AND RESIDENTS.RESIDENT_ID = SPD.RESIDENT_ID AND RESIDENTS.DEL_FLAG IS NULL INNER JOIN PS ON PS.BANK_ID = SPD.BANK_ID AND PS.RESIDENT_ID = SPD.RESIDENT_ID AND PS.PS_ID = SPD.PS_ID AND PS.DEL_FLAG IS NULL OUTER APPLY ( SELECT [YEAR] = CASE WHEN PS.N_PS LIKE '[0-2][0-9]0[1-9]%' OR PS.N_PS LIKE '[0-2][0-9]1[012]%' THEN CONVERT(int, '20' + LEFT(PS.N_PS, 2)) WHEN PS.N_PS LIKE '9[0-9]0[1-9]%' OR PS.N_PS LIKE '9[0-9]1[012]%' THEN CONVERT(int, '19' + LEFT(PS.N_PS, 2)) ELSE NULL END, [MONTH] = CONVERT(int, SUBSTRING(PS.N_PS, 3, 2)) WHERE PS.D_PS IS NULL ) AS PS_NO_DATE WHERE BANKS.REGNB = @BANK_NUMBER AND SPD.PS_ID IS NOT NULL AND SPD.SPR_ID IS NOT NULL AND SPD.DEL_FLAG IS NULL AND ( @RESIDENT_GUID IS NULL OR RESIDENTS.TELEX = @RESIDENT_GUID ) AND ( @SPD_GUID IS NULL OR SPD.SPR_GUID = @SPD_GUID ) AND ( @SPD_DATE_FROM IS NULL OR SPD.D_PRED >= @SPD_DATE_FROM ) AND ( @SPD_DATE_TO IS NULL OR CONVERT(date, SPD.D_PRED) <= @SPD_DATE_TO ) AND ( @CONTRACT_REGISTRATION_GUID IS NULL OR PS.U_ISB_ID = @CONTRACT_REGISTRATION_GUID ) AND ( @CONTRACT_REGISTRATION_NUMBER IS NULL OR PS.N_PS = @CONTRACT_REGISTRATION_NUMBER ) AND ( @CONTRACT_REGISTRATION_DATE_FROM IS NULL OR PS.D_PS >= @CONTRACT_REGISTRATION_DATE_FROM OR PS.D_PS IS NULL AND ( PS_NO_DATE.[YEAR] > @D_PS_FROM_YEAR OR PS_NO_DATE.[YEAR] = @D_PS_FROM_YEAR AND PS_NO_DATE.[MONTH] >= @D_PS_FROM_MONTH ) ) AND ( @CONTRACT_REGISTRATION_DATE_TO IS NULL OR CONVERT(date, PS.D_PS) <= @CONTRACT_REGISTRATION_DATE_TO OR PS.D_PS IS NULL AND ( PS_NO_DATE.[YEAR] < @D_PS_TO_YEAR OR PS_NO_DATE.[YEAR] = @D_PS_TO_YEAR AND PS_NO_DATE.[MONTH] <= @D_PS_TO_MONTH ) ) AND ( @CONTRACT_STATUS IS NULL OR @CONTRACT_STATUS = 0 AND PS.D_CLOSE IS NULL OR @CONTRACT_STATUS = 1 AND PS.D_CLOSE IS NOT NULL ) AND ( @PD_GUID IS NULL OR SPD.U_ISB_ID = @PD_GUID ) AND ( @PD_NUMBER IS NULL OR UPPER(SPD.N_GTD) = UPPER(@PD_NUMBER) ) AND ( @VER_SPD IS NULL AND SPD.PRIZ_138 IN ('1', '2') OR @VER_SPD = '3.0' AND SPD.PRIZ_138 = '2' OR @VER_SPD = '2.0' AND SPD.PRIZ_138 = '1' ) GROUP BY SPD.BANK_ID, SPD.RESIDENT_ID, SPD.PS_ID, SPD.SPR_ID FOR XML PATH (''), ROOT('SPD_LIST'), TYPE ) SELECT @XML_REPLY = ISNULL(@XML_REPLY, '<SPD_LIST />') SELECT @RESULT = 0, @COUNT = @XML_REPLY.value('count(SPD_LIST/*)', 'int') END TRY BEGIN CATCH SELECT @RESULT = 1, @COUNT = 0, @XML_REPLY = ( SELECT ERROR_MESSAGE() FOR XML PATH('ERROR') ) END CATCH; SELECT [RESULT] = @RESULT, [COUNT] = @COUNT, [XML_REPLY] = CONVERT(nvarchar(max), @XML_REPLY) RETURN @RESULT