SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_LoadPayListItems]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_LoadPayListItems]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2019-03-30
-- Description: Загрузка записей о приходе-расходе денежных средств
-- =============================================
CREATE PROCEDURE [dbo].[sp_LoadPayListItems]
	@CurrentDateTime datetime
   ,@UserID uniqueidentifier
AS
BEGIN

	if @UserID is null or @CurrentDateTime is null
	BEGIN
		RAISERROR('Ошибка вызова хранимой процедуры - не переданы обязательные параметры', 16, 1)
		return
	END

	-- Удаляем предыдущие загрузки пользователя
	delete from idb.DBF_PAYMENTS
	where UserID = @UserID
	-- Отмечаем свежезагруженные данные
	update idb.DBF_PAYMENTS
	set UserID = @UserID
	where UserID is null

	-- Работаем с временной таблицей чтобы исключить дальнейшие блокировки данных
	select distinct
		   [MO]
		  ,[Y]
		  ,[M]
		  ,[ITEMTYPE]
		  ,[PAYMENTSOU]
		  ,[BIK]
		  ,[BANKACCOUN]
		  ,[VALUE]
		  ,[KOSGU]
		  ,[KOSGU_NAME]
		  ,[KOD_MP]
		  ,[PAYMENTDAT]
		  ,[PAYMENTNUM]
		  ,[PAYMENTREC]
		  ,[PAYMENTVAL]
		  ,[PAYMENTDES]
		  ,[PAYMENTDET]
		  ,[UserID]
		  ,cast(null as uniqueidentifier) PayListItemId
		  ,cast(null as uniqueidentifier) BankAccountID
		  ,cast(null as uniqueidentifier) PaymentOrderID
		into #payments
	from idb.DBF_PAYMENTS p

	-- Проверки
	declare @txt varchar(max)
	set @txt = 'Проверьте файл. Не найдены '
	if exists(select * from #payments p where not exists(select * from FIES.T_MO m where m.MCOD = p.[MO]))
		set @txt = @txt + 'Мо, '
	if exists(select * from #payments p where not exists(select * from FIES.T_V006_MEDICAL_TERMS v6 where v6.IDUMP=p.[KOD_MP]))
		set @txt = @txt + 'Код условия оказания МП, '
	if exists
		(
			select * 
			from #payments p
			where not exists
			(
				select *
				from FIES.T_KOSGU_SUB_ARTICLE ksa
					join FIES.T_DICTIONARY_BASE da on da.DictionaryBaseID = ksa.DictionaryBaseID
				where da.Code = p.[KOSGU] 
			)
		)
		set @txt = @txt + 'Коды КОСГУ '
	if @txt <> 'Проверьте файл. Не найдены '
	BEGIN
		RAISERROR(@txt, 16, 1)
		RETURN
	END

	update #payments
	set PayListItemId = NEWID()
	   ,BankAccountID = NEWID()
	   ,PaymentOrderID = NEWID()

	update p
	set p.BankAccountID = ba.BankAccountID
	from #payments p
		join FIES.T_BANK_ACCOUNT ba on ba.Mo = p.MO
				and
			ba.Bank = p.BIK
				and
			ba.AccountNumber = p.BANKACCOUN
				and
			ba.PaymentSource = p.PAYMENTSOU

	INSERT INTO [FIES].[T_BANK_ACCOUNT]
	           ([BankAccountID]
	           ,[Mo]
	           ,[Bank]
	           ,[AccountNumber]
	           ,[PaymentSource])
	SELECT distinct
	           p.BankAccountID--<BankAccountID, uniqueidentifier,>
	          ,p.MO--<Mo, varchar(6),>
	          ,p.BIK--<Bank, varchar(9),>
	          ,p.BANKACCOUN--<AccountNumber, varchar(40),>
	          ,p.PAYMENTSOU--<PaymentSource, int,>)
	from #payments p
	where not exists
		(
			select *
			from [FIES].[T_BANK_ACCOUNT] ba
			where ba.BankAccountID = p.BankAccountID
		)
			and
		exists
		(
			select *
			from [FIES].[T_BANK] b
			where b.BIC = p.BIK
		)

	INSERT INTO [FIES].[T_PAY_LIST_ITEM]
	           ([PayListItemID]
	           ,[CreateDateTime]
	           ,[Users]
	           ,[Mo]
	           ,[Y]
	           ,[M]
	           ,[ItemType]
	           ,[Value]
	           ,[PaymentSource]
	           ,[Kosgu]
	           ,[MedicalTerms])
	SELECT
	           p.PayListItemId--<PayListItemID, uniqueidentifier,>
	          ,@CurrentDateTime--<CreateDateTime, datetime,>
	          ,@UserID--<Users, uniqueidentifier,>
	          ,p.MO--<Mo, varchar(6),>
	          ,p.Y--<Y, int,>
	          ,p.M--<M, int,>
	          ,p.ITEMTYPE--<ItemType, int,>
	          ,p.VALUE--<Value, decimal(31,10),>
	          ,p.PAYMENTSOU--<PaymentSource, int,>
	          ,ISNULL(kosgu.DictionaryBaseID, article.DictionaryBaseID)--<Kosgu, uniqueidentifier,>
	          ,p.KOD_MP--<MedicalTerms, int,>)
	from #payments p
	outer apply
	(
		select top 1 k.DictionaryBaseID
		from FIES.T_KOSGU_SUB_ARTICLE ksa
			join FIES.T_DICTIONARY_BASE dbKsa on dbKsa.DictionaryBaseID = ksa.DictionaryBaseID
			join FIES.T_KOSGU k on k.KosguSubArticle = dbKsa.DictionaryBaseID
			join FIES.T_DICTIONARY_BASE dbK on dbK.DictionaryBaseID = k.DictionaryBaseID
		where dbKsa.Code = p.KOSGU
				and
			dbK.Name = p.KOSGU
	) kosgu
	outer apply
	(
		select top 1 k.DictionaryBaseID
		from FIES.T_KOSGU_SUB_ARTICLE ksa
			join FIES.T_DICTIONARY_BASE dbKsa on dbKsa.DictionaryBaseID = ksa.DictionaryBaseID
			join FIES.T_KOSGU k on k.KosguSubArticle = dbKsa.DictionaryBaseID
			join FIES.T_DICTIONARY_BASE dbK on dbK.DictionaryBaseID = k.DictionaryBaseID
		where dbKsa.Code = p.KOSGU
	) article

	INSERT INTO [FIES].[T_PAYMENT_ORDER]
	           ([PaymentOrderID]
	           ,[PayListItem]
	           ,[OrderDate]
	           ,[Number]
	           ,[PaymentReceiver]
	           ,[PaymentSum]
	           ,[Purpose]
	           ,[ContractDetails])
	SELECT
	           p.PaymentOrderID--<PaymentOrderID, uniqueidentifier,>
	          ,p.PayListItemId--<PayListItem, uniqueidentifier,>
	          ,p.PAYMENTDAT--<OrderDate, datetime,>
	          ,p.PAYMENTNUM--<Number, varchar(30),>
	          ,p.PAYMENTREC--<PaymentReceiver, varchar(255),>
	          ,p.PAYMENTVAL--<PaymentSum, decimal(15,2),>
	          ,p.PAYMENTDES--<Purpose, varchar(512),>
	          ,p.PAYMENTDET--<ContractDetails, varchar(512),>)
    from #payments p
	where p.PAYMENTNUM is not null
			and
		p.PAYMENTREC is not null
			and
		p.PAYMENTDAT is not null

	DROP TABLE #payments
END
GO