IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FNSDEPOYILLIK]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION  [dbo].[FNSDEPOYILLIK]
GO
CREATE FUNCTION [dbo].[FNSDEPOYILLIK] 
(
@YIL AS INT,@SUBEID AS INT,@STOKID AS INT,@DEPOID AS INT
)
RETURNS TABLE
AS 
RETURN
(
 SELECT GCTUR [Tür],YIL [Yıl],AY [Ay],DEPOADI [Depo Adı],DEPOID,STOKID,ADI [Stok Adı],SBIRIMI [Stok Birimi],SUM(GIREN) [Giren],SUM(CIKAN) [Çıkan] FROM
 (
	 SELECT CASE WHEN ISNULL(GCADET,0)<0 THEN 'Çıkan' else 'Giren' END GCTUR, MONTH(TARIH) AS AY, YEAR(TARIH) AS YIL,/*FISTIPI,ISLEM*/DEPOADI,DEPOID,STOKID,ADI,SBIRIMI,0  GIREN, SUM(ISNULL(GCADET,0)) CIKAN
	  FROM [VWSTOKISLSAYIM] WHERE YEAR(TARIH)=@YIL AND 
	                              SUBEID=@SUBEID AND
							      STOKID=@STOKID AND DEPOID=@DEPOID AND 
							      CASE WHEN ISNULL(GCADET,0)<0 THEN 'Çıkan' else 'Giren' END='Çıkan'
							      AND DEPOID IS NOT NULL
	  GROUP BY YEAR(TARIH),MONTH(TARIH),CASE WHEN ISNULL(GCADET,0)<0 THEN 'Çıkan' else 'Giren' END,/*FISTIPI,ISLEM*/DEPOADI,DEPOID,STOKID,ADI,SBIRIMI
 
	 UNION ALL
 
	 SELECT CASE WHEN ISNULL(GCADET,0)<0 THEN 'Çıkan' else 'Giren' END GCTUR, MONTH(TARIH) AS AY, YEAR(TARIH) AS YIL,/*FISTIPI,ISLEM*/DEPOADI,DEPOID,STOKID,ADI,SBIRIMI,SUM(ISNULL(GCADET,0)) ,0  
	  FROM [VWSTOKISLSAYIM] WHERE YEAR(TARIH)=@YIL AND 
	                              SUBEID=@SUBEID AND 
								  STOKID=@STOKID  AND DEPOID=@DEPOID AND 
								 CASE WHEN ISNULL(GCADET,0)<0 THEN 'Çıkan' else 'Giren' END='Giren'
							 AND DEPOID IS NOT NULL
	  GROUP BY YEAR(TARIH),MONTH(TARIH),CASE WHEN ISNULL(GCADET,0)<0 THEN 'Çıkan' else 'Giren' END,/*FISTIPI,ISLEM*/DEPOADI,DEPOID,STOKID,ADI,SBIRIMI
 ) AS T
 GROUP BY GCTUR,YIL,AY,DEPOADI,DEPOID,STOKID,ADI,SBIRIMI
 --ORDER BY /*YEAR(TARIH),MONTH(TARIH),*/ADI
 )
 GO



IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FNSDEPOYILLIKOZET]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION  [dbo].[FNSDEPOYILLIKOZET]
GO
CREATE FUNCTION [dbo].[FNSDEPOYILLIKOZET] 
(
@YIL AS INT,@SUBEID AS INT,@STOKID AS INT
)
RETURNS TABLE
AS 
RETURN
(
 SELECT GCTUR [Tür],YIL [Yıl],AY [Ay],DEPOADI [Depo Adı],DEPOID,STOKID,ADI [Stok Adı],SBIRIMI [Stok Birimi],SUM(GIREN) [Giren],SUM(CIKAN) [Çıkan] FROM
 (
	 SELECT CASE WHEN ISNULL(GCADET,0)<0 THEN 'Çıkan' else 'Giren' END GCTUR, MONTH(TARIH) AS AY, YEAR(TARIH) AS YIL,/*FISTIPI,ISLEM*/NULL DEPOADI,NULL DEPOID,STOKID,ADI,SBIRIMI,0  GIREN, SUM(ISNULL(GCADET,0)) CIKAN
	  FROM [VWSTOKISLSAYIM] WHERE YEAR(TARIH)=@YIL AND 
	                              SUBEID=@SUBEID AND
							      STOKID=@STOKID AND  
							      CASE WHEN ISNULL(GCADET,0)<0 THEN 'Çıkan' else 'Giren' END='Çıkan'
							      AND DEPOID IS NOT NULL
	  GROUP BY YEAR(TARIH),MONTH(TARIH),CASE WHEN ISNULL(GCADET,0)<0 THEN 'Çıkan' else 'Giren' END,/*FISTIPI,ISLEM*/STOKID,ADI,SBIRIMI
 
	 UNION ALL
 
	 SELECT CASE WHEN ISNULL(GCADET,0)<0 THEN 'Çıkan' else 'Giren' END GCTUR, MONTH(TARIH) AS AY, YEAR(TARIH) AS YIL,/*FISTIPI,ISLEM*/NULL,NULL,STOKID,ADI,SBIRIMI,SUM(ISNULL(GCADET,0)) ,0  
	  FROM [VWSTOKISLSAYIM] WHERE YEAR(TARIH)=@YIL AND 
	                              SUBEID=@SUBEID AND 
								  STOKID=@STOKID  AND 
								 CASE WHEN ISNULL(GCADET,0)<0 THEN 'Çıkan' else 'Giren' END='Giren'
							 AND DEPOID IS NOT NULL
	  GROUP BY YEAR(TARIH),MONTH(TARIH),CASE WHEN ISNULL(GCADET,0)<0 THEN 'Çıkan' else 'Giren' END,/*FISTIPI,ISLEM*/STOKID,ADI,SBIRIMI
 ) AS T
 GROUP BY GCTUR,YIL,AY,DEPOADI,DEPOID,STOKID,ADI,SBIRIMI
 --ORDER BY /*YEAR(TARIH),MONTH(TARIH),*/ADI
 )
 GO


