USE TestDB

SET NOCOUNT ON 

/**********************************************************************************************************************

Hesaplama Türü : Fifo Maliyet

Sorguyu Yazan : VEDAT ÖZER



Tablo İsimleri



Stok Hareketler Tablosu   : STOK_DETAYLI

Stok Giriş Çıkış Eşlemesi : FIFOESLEME

Envanter Tablosu          : FIFOENVANTER

Negatif Bilgiler          : STOKNEGATIF



Stok Negatife düştüğünde maliyet hesabı yapılmıyacak. Eksiğe düştüğü tarihten itibaren aşağıdaki tabloya yazacaz.   

**********************************************************************************************************************/

IF (SELECT COUNT(*) FROM dbo.sysobjects WHERE id = OBJECT_ID(N'STOKNEGATIF') AND OBJECTPROPERTY(id,'IsUserTable')=1)>0 BEGIN

DROP TABLE [dbo].STOKNEGATIF

END

CREATE TABLE STOKNEGATIF 

    ([KODU] VARCHAR(150) NOT NULL,

	 [ADI] varchar(250) NOT NULL,

	 [TARIH] datetime NOT NULL,[AMBAR] varchar(150) NOT NULL,

     [GIRISCIKIS] VARCHAR(150),

	 [FISTURU] VARCHAR(200),

	 [MIKTAR] FLOAT NOT NULL,

	 KALAN DECIMAL(38,2))



CREATE CLUSTERED INDEX TANIMLAMA ON STOKNEGATIF(KODU,AMBAR,TARIH)

CREATE INDEX DENEME ON STOKNEGATIF(KODU)



/**********************************************************************************************************************

Maliyet hesaplamaların doğru bir şekilde yapmamız için Giriş ve Çıkış hareketlerin ilişkilendirmemiz lazım.

Aşağıdaki tabloya giriş ve çıkış bilgilerini yazacaz.

**********************************************************************************************************************/

IF (SELECT COUNT(*) FROM dbo.sysobjects WHERE id = OBJECT_ID(N'FIFOESLEME') AND OBJECTPROPERTY(id,'IsUserTable')=1)>0 BEGIN

DROP TABLE [dbo].FIFOESLEME

END

CREATE TABLE [dbo].FIFOESLEME 

             ([KODU] varchar(150),

			 [AMBAR] varchar(50),

			 GIRISID INT,CIKISID INT,

			 MIKTAR FLOAT,

			 GIRISFIYAT FLOAT,

             CIKISFIYAT FLOAT,

			 ALIMTARIHI DATE,

			 SATISTARIHI DATE)



CREATE CLUSTERED INDEX ITANIMLAMADX11 ON FIFOESLEME([KODU],AMBAR,ALIMTARIHI,GIRISID)

CREATE INDEX KART ON FIFOESLEME([KODU])



/**********************************************************************************************************************

Ambarlara göre elimizdeki stokğun bilgilerini yazacağımız tabloyu oluşturuyoruz.

**********************************************************************************************************************/

IF (SELECT count(*) FROM dbo.sysobjects WHERE id = OBJECT_ID(N'FIFOENVANTER') AND OBJECTPROPERTY(id,'IsUserTable')=1)>0 BEGIN

DROP TABLE [dbo].FIFOENVANTER

END

CREATE TABLE [dbo].FIFOENVANTER 

             ([KODU] varchar(150),[AMBAR] varchar(100),

			  GIRISID INT,

			  MIKTAR FLOAT,

			  GIRISFIYAT FLOAT,

			  ALIMTARIHI DATE)



CREATE CLUSTERED INDEX IDX11 ON FIFOENVANTER([KODU],AMBAR,ALIMTARIHI,GIRISID)

CREATE INDEX DENEME ON FIFOENVANTER([KODU])



/**********************************************************************************************************************

Aşağıdaki alanlarda maliyet hesabını yapacağımız satırları ekliyoruz.

**********************************************************************************************************************/



IF (SELECT COUNT(*) FROM sys.tables WHERE name= 'STOK_DETAYLI')>0 BEGIN

DROP TABLE STOK_DETAYLI

END

;

CREATE TABLE 

[dbo].[STOK_DETAYLI](

[ID] [int] IDENTITY(1,1) NOT NULL,

[KODU] varchar(150) NOT NULL,

[ADI] varchar(250) NOT NULL,

[TARIH] datetime NOT NULL,

[AMBAR] varchar(100) NOT NULL,

[GIRISCIKIS] VARCHAR(100),

[FISTURU] VARCHAR(200),

[MIKTAR] FLOAT NOT NULL,

[BIRIMFIYAT] FLOAT ,

[TUTAR] FLOAT,

[MALIYET] FLOAT) 

CREATE CLUSTERED INDEX STOK ON [STOK_DETAYLI](KODU,TARIH,AMBAR)

CREATE INDEX SIRA ON [STOK_DETAYLI](KODU)



INSERT INTO [STOK_DETAYLI]



SELECT 'STOK.001','Deneme Stok','2018-01-01 09:34:00.000','Merkez','GIRIS','Devir','15','1.25','18.75','0' UNION ALL

SELECT 'STOK.001','Deneme Stok','2018-01-02 09:35:00.000','Merkez','GIRIS','SatınAlma','15','2','30','0' UNION ALL

SELECT 'STOK.001','Deneme Stok','2018-01-02 09:36:00.000','Merkez','CIKIS','Satış','22','3','66','0' UNION ALL

SELECT 'STOK.001','Deneme Stok','2018-01-02 09:38:00.000','Merkez','GIRIS','SatınAlma','20','1.75','35','0' UNION ALL

SELECT 'STOK.001','Deneme Stok','2018-01-03 10:35:00.000','Antalya','GIRIS','SatınAlma','15','2.50','37.50','0' UNION ALL

SELECT 'STOK.001','Deneme Stok','2018-01-03 10:36:00.000','Antalya','CIKIS','Satış','12','3','36','0' UNION ALL

SELECT 'STOK.001','Deneme Stok','2018-01-04 11:35:00.000','Merkez','GIRIS','SatınAlma','20','1.75','35','0' UNION ALL

SELECT 'STOK.001','Deneme Stok','2018-01-04 11:40:00.000','Merkez','CIKIS','Satış','15','3','45','0' UNION ALL

SELECT 'STOK.001','Deneme Stok','2018-01-05 11:41:00.000','Merkez','GIRIS','SatınAlma','8','2.50','20','0'  UNION ALL

SELECT 'STOK.002','Deneme','2018-02-01 09:34:00.000','Merkez','GIRIS','Devir','15','1.25','18.75','0' UNION ALL

SELECT 'STOK.002','Deneme','2018-02-02 09:35:00.000','Merkez','GIRIS','SatınAlma','15','2','30','0' UNION ALL

SELECT 'STOK.002','Deneme','2018-02-02 09:36:00.000','Merkez','CIKIS','Satış','22','3','66','0' UNION ALL

SELECT 'STOK.002','Deneme','2018-02-02 09:38:00.000','Merkez','GIRIS','SatınAlma','20','1.75','35','0' UNION ALL

SELECT 'STOK.002','Deneme','2018-02-03 10:35:00.000','Antalya','GIRIS','SatınAlma','15','2.50','37.50','0' UNION ALL

SELECT 'STOK.002','Deneme','2018-02-03 10:36:00.000','Antalya','CIKIS','Satış','12','3','36','0' UNION ALL

SELECT 'STOK.002','Deneme','2018-02-04 11:35:00.000','Merkez','GIRIS','SatınAlma','20','1.75','35','0' UNION ALL

SELECT 'STOK.002','Deneme','2018-02-04 11:40:00.000','Merkez','CIKIS','Satış','15','3','45','0' UNION ALL

SELECT 'STOK.002','Deneme','2018-02-05 11:41:00.000','Merkez','GIRIS','SatınAlma','8','2.50','20','0' 





/**********************************************************************************************************************

Artık Gerekli maliyet hesaplamalarına başlıyacağız. Numaralı şekilde gideceğiz

**********************************************************************************************************************/



/**********************************************************************************************************************

1) Gerçek tabloda olan bütün verileri Geçiçi tabloya aktarıyoruz.

********************************************************************************************************************/

IF(OBJECT_ID('tempdb..##VERIDATA') IS NOT NULL) BEGIN DROP TABLE ##VERIDATA END

SELECT *,CAST(0 AS DECIMAL(38,2)) AS KALAN INTO ##VERIDATA 

FROM [STOK_DETAYLI] WITH(NOLOCK, INDEX(STOK))

CREATE INDEX STOK ON ##VERIDATA(KODU)



IF(OBJECT_ID('tempdb..##FIFOESLEME') IS NOT NULL) BEGIN DROP TABLE ##FIFOESLEME END

CREATE TABLE ##FIFOESLEME 

             ([KODU] varchar(150),

			  [AMBAR] varchar(50),

			  GIRISID INT,

			  CIKISID INT,

			  MIKTAR FLOAT,

			  GIRISFIYAT FLOAT,

              CIKISFIYAT FLOAT,

			  ALIMTARIHI DATE,

			  SATISTARIHI DATE)



CREATE CLUSTERED INDEX ITANIMLAMADX11 ON ##FIFOESLEME([KODU],AMBAR,ALIMTARIHI,GIRISID)

CREATE INDEX KART ON ##FIFOESLEME(GIRISID)



/**********************************************************************************************************************

2) Geçiçi tabloda Yürüyen bakiye işlemini yapıyoruz. Eksi değer veren varmı diye 

Normal şartlarda 2012 versiyonda Partıtıon By fonksiyonu ile gidilebilir.

**********************************************************************************************************************/

DECLARE @STOKKODU VARCHAR(150),@AMBAR VARCHAR(50),@KALAN DECIMAL(38,2)

SET @STOKKODU = ''

SET @AMBAR = ''

SET @KALAN =0

UPDATE a

SET @KALAN = KALAN = CASE WHEN GIRISCIKIS ='GIRIS' THEN MIKTAR ELSE -MIKTAR END  + CASE WHEN KODU = @STOKKODU AND  AMBAR = @AMBAR THEN

@KALAN ELSE 0 END,

@STOKKODU = KODU,

@AMBAR =AMBAR

FROM ##VERIDATA  a WITH (TABLOCKX,INDEX(STOK)) 

OPTION (MAXDOP 5);



/**********************************************************************************************************************

3) Döngü ile verileri yazacağımız geçiçi tabloyu oluşturuyoruz.

**********************************************************************************************************************/

IF(OBJECT_ID('tempdb..#GECICI') IS NOT NULL) BEGIN DROP TABLE #GECICI END

CREATE TABLE #GECICI 

             (ID INT,

			  TARIH DATETIME,

			  [KODU] varchar(150),

			  [AMBAR] varchar(50),

			  GIRISCIKIS VARCHAR(35),

			  BIRIMFIYAT FLOAT,

			  MIKTAR DECIMAL(38,2))



CREATE CLUSTERED INDEX SIRALAMA ON #GECICI([KODU],AMBAR,TARIH)

CREATE INDEX VEDAT ON #GECICI([KODU])

CREATE INDEX SATIR ON #GECICI(ID) INCLUDE([KODU],AMBAR,TARIH,MIKTAR)



/**********************************************************************************************************************

4) Negatif olan stokları toplu olarak tabloya aktarıyoruz.

**********************************************************************************************************************/

TRUNCATE TABLE STOKNEGATIF

INSERT INTO STOKNEGATIF

SELECT KODU,ADI,TARIH,AMBAR,GIRISCIKIS,FISTURU,MIKTAR,KALAN FROM ##VERIDATA

WHERE KALAN<0



/**********************************************************************************************************************

5) Malıyet Hesabını yapıyoruz.

**********************************************************************************************************************/



DECLARE @KODU VARCHAR(150) , @AMBARR VARCHAR(50) 

DECLARE STOK CURSOR FOR

SELECT DISTINCT KODU,AMBAR FROM ##VERIDATA



ORDER BY KODU,AMBAR



	OPEN STOK

	FETCH NEXT FROM STOK

	INTO @KODU,@AMBAR

	WHILE @@FETCH_STATUS=0

	BEGIN

	  	

	DELETE #GECICI      WITH(TABLOCK)   WHERE KODU=@KODU AND AMBAR=@AMBAR



	IF (SELECT COUNT(*) FROM STOKNEGATIF WITH(NOLOCK,INDEX(DENEME)) WHERE KODU=@KODU AND AMBAR=@AMBAR AND KALAN<0)=0 BEGIN

		

	DECLARE @ID INT,@TARIH DATETIME,@GIRISCIKIS VARCHAR(10),@FISTUR VARCHAR(60),@MIKTAR DECIMAL(38,2),@BIRIMFIYAT DECIMAL(38,2)

	DECLARE @GIRENID INT,@GIRENTARIH DATETIME,@GIRENFISTUR VARCHAR(60),@GIRENMIKTAR DECIMAL(38,2),@GIRENBIRIMFIYAT DECIMAL(38,2)

		DECLARE FIFOMALIYET CURSOR FOR

		SELECT ID,TARIH,GIRISCIKIS,FISTURU,MIKTAR,BIRIMFIYAT FROM ##VERIDATA

		WHERE KODU=@KODU AND AMBAR=@AMBAR

			OPEN FIFOMALIYET

			FETCH NEXT FROM FIFOMALIYET INTO @ID,@TARIH,@GIRISCIKIS,@FISTUR,@MIKTAR,@BIRIMFIYAT

			WHILE @@FETCH_STATUS=0 BEGIN



			IF @GIRISCIKIS = 'GIRIS' BEGIN

			INSERT INTO #GECICI

			VALUES(@ID,@TARIH,@KODU,@AMBAR,@GIRISCIKIS,@BIRIMFIYAT,@MIKTAR)

			END

			 

			  IF @GIRISCIKIS='CIKIS' 	   

			    BEGIN		 



			     WHILE (1=1) 

				  

				  BEGIN



				  SELECT TOP 1 @GIRENID=ID,@GIRENTARIH=TARIH,@GIRENMIKTAR=MIKTAR,@GIRENBIRIMFIYAT=BIRIMFIYAT  FROM #GECICI

				  WHERE KODU=@KODU AND AMBAR=@AMBAR AND GIRISCIKIS='GIRIS' AND MIKTAR>0

				  ORDER BY TARIH ASC

				  IF @GIRENMIKTAR>=@MIKTAR  AND @MIKTAR<>0

				      BEGIN

					  INSERT INTO ##FIFOESLEME (KODU,AMBAR,GIRISID,CIKISID,MIKTAR,GIRISFIYAT,CIKISFIYAT,ALIMTARIHI,SATISTARIHI)

					  VALUES(@KODU,@AMBAR,@GIRENID,@ID,@MIKTAR,@GIRENBIRIMFIYAT,@BIRIMFIYAT,@GIRENTARIH,@TARIH)

					  UPDATE #GECICI WITH(TABLOCK)  SET MIKTAR = COALESCE(MIKTAR,0) - COALESCE(@MIKTAR,0) WHERE ID=@GIRENID 

					  BREAK

				      END

				  ELSE 

				      BEGIN

					  INSERT INTO ##FIFOESLEME (KODU,AMBAR,GIRISID,CIKISID,MIKTAR,GIRISFIYAT,CIKISFIYAT,ALIMTARIHI,SATISTARIHI)

					  VALUES(@KODU,@AMBAR,@GIRENID,@ID,@GIRENMIKTAR,@GIRENBIRIMFIYAT,@BIRIMFIYAT,@GIRENTARIH,@TARIH)

				      UPDATE #GECICI WITH(TABLOCK) SET MIKTAR = COALESCE(MIKTAR,0) - COALESCE(@GIRENMIKTAR,0) WHERE ID=@GIRENID 

					  SET @MIKTAR = @MIKTAR - @GIRENMIKTAR

				  END

				  

				

				  			  

				  END



               end

		   

			FETCH NEXT FROM FIFOMALIYET INTO @ID,@TARIH,@GIRISCIKIS,@FISTUR,@MIKTAR,@BIRIMFIYAT

   

  			END

			

/**********************************************************************************************************************

5) Malıyet Hesabını bitti. Elimizde kalan malzemeleri envanter bölümüne aktarıyoruz.

**********************************************************************************************************************/

            DELETE FIFOENVANTER WITH(TABLOCK)   WHERE KODU=@KODU AND AMBAR=@AMBAR

			INSERT INTO FIFOENVANTER(KODU,AMBAR,GIRISID ,MIKTAR ,GIRISFIYAT,ALIMTARIHI) 

            SELECT KODU,AMBAR,ID ,MIKTAR ,BIRIMFIYAT,TARIH FROM #GECICI WHERE ISNULL(MIKTAR,0)>0 AND GIRISCIKIS='GIRIS'

            AND KODU=@KODU  AND AMBAR=@AMBAR



/**********************************************************************************************************************

6) Çıkışların maliyetini hesaplaması aşağıdaki şekilde yapılmaktadır. 

İlk İşlemde  ; (MIKTAR * GIRISFIYAT) TUTAR

Sonrasında   ; (TUTAR / MIKTAR) 

**********************************************************************************************************************/		

			UPDATE V

			SET V.MALIYET=CAST(F.MALIYET  AS decimal(38,8))

			FROM STOK_DETAYLI V 

			LEFT JOIN(

			SELECT CIKISID,AMBAR,KODU,ISNULL(SUM(TUTAR)/NULLIF(SUM(MIKTAR),0),0) MALIYET FROM (

			SELECT CIKISID,AMBAR,KODU,CAST(MIKTAR*GIRISFIYAT AS decimal(38,8))TUTAR,(MIKTAR) AS MIKTAR

			FROM ##FIFOESLEME WHERE KODU=@KODU  AND AMBAR=@AMBAR ) AS  YHP

			GROUP BY CIKISID,KODU,AMBAR

			) AS F ON F.CIKISID=V.ID

			WHERE GIRISCIKIS='CIKIS' AND V.KODU=@KODU  AND V.AMBAR=@AMBAR



/**********************************************************************************************************************

7) Geçiçi tabloda olan Giriş ve Çıkış eşleşmelerini gerçek tabloya yazıyoruz.

**********************************************************************************************************************/

    		DELETE FIFOESLEME   WITH(TABLOCK)   WHERE KODU=@KODU AND AMBAR=@AMBAR

			INSERT INTO FIFOESLEME

			SELECT * FROM ##FIFOESLEME WHERE KODU=@KODU AND AMBAR=@AMBAR

			DELETE ##FIFOESLEME   WITH(TABLOCK)   WHERE KODU=@KODU AND AMBAR=@AMBAR



	CLOSE FIFOMALIYET

	DEALLOCATE FIFOMALIYET



	END

		

	FETCH NEXT FROM STOK INTO @KODU,@AMBAR

	END



CLOSE STOK

DEALLOCATE STOK





SELECT * FROM FIFOENVANTER

SELECT * FROM FIFOESLEME

SELECT * FROM STOK_DETAYLI