USE [CDN_VIP_08]
GO
/****** Object:  StoredProcedure [dbo].[s4rNaleznosc]    Script Date: 2013-10-18 14:00:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[s4rNaleznosc] @FakturaExid INT
AS
BEGIN
	SET NOCOUNT ON;

	declare @Name nvarchar(40);
	declare @PayerGuid uniqueidentifier;
	declare @SellerGuid uniqueidentifier;
	declare @CreateDate datetime;
	declare @DueDate datetime;
	declare @Amount money;
	declare @AmountToPay money;
	declare @Note nvarchar(500);

	declare @SumaKorekt money;
	declare @KlientEXID int;
	declare @DueGuid uniqueidentifier;

	-- detail
	SELECT 
		@Name = TrN_NumerPelny,
		@CreateDate = TrN_DataWys,
		@DueDate = TrN_Termin,
		@Amount = TrN_RazemBrutto,
		@AmountToPay = TrN_RazemBrutto,
		@KlientEXID = TrN_PodID
	FROM CDN.TraNag WHERE Trn_TrnId = @FakturaExid

	-- customer 
	select @PayerGuid=CUS_GUID from sales4rent_vip_prod.dbo.CUSTOMER   
		inner join sales4rent_vip_prod.dbo.CUSTOMER_ORGANIZATION on CUO_GUID_CUS = CUS_GUID
		where CUO_ID_ORG = 1 and CUO_EXID_CUS = @KlientEXID;
	if @PayerGuid is null
		return;

	-- VIP GUID
	select top 1 @SellerGuid=CUS_GUID from sales4rent_vip_prod.dbo.CUSTOMER where cus_type = 2

	-- select korekty
	Select @SumaKorekt = isnull(sum(trn_razemBrutto),0)
	FROM CDN.TraNag WHERE TrN_ZwrId = @FakturaExid

	Select @Note = COALESCE(@Note + ', ', '') + TrN_NumerPelny
	FROM CDN.TraNag WHERE TrN_ZwrId = @FakturaExid

	set @Amount = @Amount - @SumaKorekt;
	set @AmountToPay = @AmountToPay - @SumaKorekt;

	-- is exist
	select @DueGuid=DUE_GUID from sales4rent_vip_prod.dbo.DUE   		
		where DUE_NAME = @Name;

	if @DueGuid is null
	begin
		-- insert
		insert sales4rent_vip_prod.dbo.DUE
		( 					   
		   [DUE_ID_ORG]
		  ,[DUE_GUID_CUS_PAY]
		  ,[DUE_GUID_CUS_SELL]
		  ,[DUE_NAME]
		  ,[DUE_CREATE_DATE]
		  ,[DUE_DUE_DATE]
		  ,[DUE_AMOUNT]
		  ,[DUE_AMOUNT_TO_PAY]
		  ,[DUE_NOTE]
		  )
		values
		(
			1,
			@PayerGuid,
			@SellerGuid,
			@Name,
			@CreateDate,
			@DueDate,
			@Amount,
			@AmountToPay,
			@Note
		)
	end
	else begin
		-- update
		update sales4rent_vip_prod.dbo.DUE
		set
		   [DUE_ID_ORG] = 1
		  ,[DUE_GUID_CUS_PAY] = @PayerGuid
		  ,[DUE_GUID_CUS_SELL] = @SellerGuid
		  ,[DUE_NAME] = @Name
		  ,[DUE_CREATE_DATE] = @CreateDate
		  ,[DUE_DUE_DATE] = @DueDate
		  ,[DUE_AMOUNT] = @Amount
		  ,[DUE_AMOUNT_TO_PAY] = @AmountToPay
		  ,[DUE_NOTE] = @Note
		  where DUE_GUID = @DueGuid;
	end;

END
