USE [iBasket]
GO
/****** Object:  StoredProcedure [dbo].[AktualizujStany]    Script Date: 2021-09-03 13:11:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================

-- Author:		<Author,,Name>

-- Create date: <Create Date,,>

-- Description:	<Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[AktualizujStany] 

	-- Add the parameters for the stored procedure here

	

	

AS

BEGIN



declare @ilosc int

declare @fsc varchar(20)

declare @liniastacjabin varchar(20)

declare @dataczasstartu datetime





BEGIN TRY

 BEGIN TRANSACTION







set @dataczasstartu=getdate()



 IF OBJECT_ID('dbo.pobraniaTEMP', 'U') IS NOT NULL 

  DROP TABLE dbo.pobraniaTEMP;



  CREATE TABLE [dbo].[pobraniaTEMP](

	[fsc] [varchar](20) NOT NULL,

	[ilosc] [int] NULL,

	[liniastacjabin] [varchar](20) NOT NULL,

 CONSTRAINT [PK_pobraniaTEMP] PRIMARY KEY CLUSTERED 

(

	[fsc] ASC,

	[liniastacjabin] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]


insert into pobraniaTEMP(fsc, ilosc, liniastacjabin)

select REPLACE(STR(fsc, 8), ' ', '0'), sum(ilosc) as ilosc, liniastacja+bin as liniastacjabin from pobrania where dataczaspobrania<=@dataczasstartu group by fsc, liniastacja,bin


while exists (select top 1 fsc from pobraniaTEMP)

	begin

		select top 1 @ilosc=ilosc, @fsc=fsc, @liniastacjabin=liniastacjabin from pobraniaTEMP

		update biny set stan_bin=stan_bin-@ilosc where fsc=@fsc and id_bin=@liniastacjabin

		delete pobraniaTEMP where fsc=@fsc and liniastacjabin=@liniastacjabin

	end 



delete from pobrania where dataczaspobrania<=@dataczasstartu



 COMMIT TRANSACTION

declare @i int

  SET @i = XACT_STATE()

END TRY



BEGIN CATCH 



	IF (XACT_STATE()) = -1

	 SET @i = XACT_STATE()

    BEGIN

        ROLLBACK TRANSACTION

    END

END CATCH

   

SELECT @i as wynik

END