USE [CDN_VIP_08]
GO
/****** Object:  StoredProcedure [dbo].[s4rProduct]    Script Date: 2013-10-16 15:02:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[s4rProduct] @ProductExid INT
AS
BEGIN
	SET NOCOUNT ON;

	declare @ProduktId int;
	declare @ProductShortName nvarchar(40);
	declare @ProductLongName nvarchar(120);
	declare @PriceNet money;
	declare @PriceGross money;
	declare @Unit nvarchar(10);
	declare @Ratio int = 1;
	declare @Active int = 1;
	declare @SafetyStock int = 1;
	declare @IsSet int = 0;
	declare @TaxValue decimal(5,2);
	declare @TaxId int;
	declare @Ordered int = 1;
	declare @SelfPrice int = 0;
	declare @Own int = 1;

	-- detail
	Select
		@ProductShortName = Twr_Nazwa,
		@ProductLongName = Twr_Nazwa,
		@Unit = Twr_JM,
		@TaxValue = Twr_Stawka,
		@TaxId = case @TaxValue
		 when 0 then 2
		 when 5 then 6
		 when 8 then 7
		 when 23 then 5	 
		 end
	FROM CDN.Towary 
	WHERE Twr_TwrId = @ProductExid;

	--price
	SELECT @PriceNet = [TwC_Wartosc]
	FROM [CDN_VIP_08].[CDN].[TwrCeny]
	WHERE [TwC_TwCNumer] = 4 and [TwC_TwrID] = @ProductExid;

	set @PriceGross = @PriceNet * (@TaxValue / 100) + @PriceNet;

	-- is exist
	select @ProduktId=PRO_ID from sales4rent_vip_prod.dbo.PRODUCT   
		inner join sales4rent_vip_prod.dbo.PRODUCT_ORGANIZATION on pro_id = POR_ID_PRO
		where POR_ID_ORG = 1 and POR_EXID_PRO = @ProductExid;



	if @ProduktId is null
	begin
		-- insert
		insert sales4rent_vip_prod.dbo.PRODUCT
		( 			
		   [PRO_LIST_NAME]
		  ,[PRO_NAME]
		  ,[PRO_LIST_PRICE_NET]
		  ,[PRO_LIST_PRICE_GROSS]
		  ,[PRO_BASE_UNIT]
		  ,[PRO_TRANSACTION_UNIT]
		  ,[PRO_TRANSATION_RATIO]
		  ,[PRO_TAP_INCREMENT_TRANS_UNIT]
		  ,[PRO_REPORT_UNIT]
		  ,[PRO_REPORT_RATIO]
		  ,[PRO_ACTIVE]
		  ,[PRO_SAFETY_STOCK_LEVEL]
		  ,[PRO_IS_SET]
		  ,[PRO_ID_TAX]
		  ,[PRO_ORDERED]
		  ,[PRO_SELF_DETERMINE_PRICE]
		  ,[PRO_OWN]
		  )
		values
		(
			@ProductShortName,
			@ProductLongName,
			@PriceNet,
			@PriceGross, 
			@Unit,
			@Unit,
			@Ratio,
			@Ratio,
			@Unit,
			@Ratio,
			@Active,
			@SafetyStock,
			@IsSet,
			@TaxId,
			@Ordered,
			@SelfPrice,
			@Own
		)

		SELECT @ProduktId = IDENT_CURRENT('sales4rent_vip_prod.dbo.PRODUCT')

		insert into sales4rent_vip_prod.dbo.PRODUCT_ORGANIZATION
		(
			POR_ID_PRO,
			POR_ID_ORG,
			POR_EXID_PRO
		)
		values
		(
			@ProduktId,
			1,
			@ProductExid
		)
	end
	else begin
		-- update
		update sales4rent_vip_prod.dbo.PRODUCT
		set
			[PRO_LIST_NAME] = @ProductShortName,
		    [PRO_NAME] = @ProductLongName,
		    [PRO_LIST_PRICE_NET] = @PriceNet,
		    [PRO_LIST_PRICE_GROSS] = @PriceGross,
		    [PRO_BASE_UNIT] = @Unit,
		    [PRO_TRANSACTION_UNIT] = @Unit,
		    [PRO_TRANSATION_RATIO] = @Ratio,
		    [PRO_TAP_INCREMENT_TRANS_UNIT] = @Unit,
		    [PRO_REPORT_UNIT] = @Unit,
		    [PRO_REPORT_RATIO] = @Ratio,
		    [PRO_ACTIVE] = @Active,
		    [PRO_SAFETY_STOCK_LEVEL] = @SafetyStock,
		    [PRO_IS_SET] = @IsSet,
		    [PRO_ID_TAX] = @TaxId,
		    [PRO_ORDERED] = @Ordered,
		    [PRO_SELF_DETERMINE_PRICE] = @SelfPrice,
		    [PRO_OWN] = @Own
		  where PRO_ID = @ProduktId;
	end;

END
