USE [MikroDB_V16_HILAL2019]
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_StokMaliyeti]    Script Date: 26.01.2021 13:58:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION  [dbo].[mbt_StokMaliyeti]
(
	--Bu parametre hesaplama formülünü ifade etmektedir. 
	--Eğer 0 olarak gelirse depo detaylı hesap yapar.
	--Eğer 1 olarak gelirse direk stok koduna göre hesaplama yapar.
	@hf_param int,
	@sto_kod varchar(25),
	@depo int,
	--Bu paramtere tarih sırasını ifade etmektedir. İlk tarih için 0, son tarih için 1
	@siralama int
)
RETURNS float AS
BEGIN
	-- Dönüş değeri değişkeni ve diğerleri
	DECLARE 
		@SonAlis float = 0
		
		/* DEPO DETAYLI HESAPLAMA */
		if((@hf_param is not null) and (@hf_param = 0))
			Begin
				if((@siralama is not null) and @siralama = 0)
					Begin
						Select
								Top 1
								@SonAlis = ((((((sth_tutar) - 
									(sth_iskonto1+sth_iskonto2+sth_iskonto3+sth_iskonto4+sth_iskonto5+sth_iskonto6)) +
									(sth_masraf1+sth_masraf2+sth_masraf3+sth_masraf4)) +
									(sth_vergi)) /
									(sth_miktar)))
						From
							STOK_HAREKETLERI
						Where
							sth_stok_kod = @sto_kod and sth_tip = 0 and sth_normal_iade = 0 and sth_giris_depo_no = @depo
						Order by
							sth_tarih 
					End
				Else if((@siralama is not null) and @siralama = 1)
					Begin
						Select
								Top 1
								@SonAlis = ((((((sth_tutar) - 
									(sth_iskonto1+sth_iskonto2+sth_iskonto3+sth_iskonto4+sth_iskonto5+sth_iskonto6)) +
									(sth_masraf1+sth_masraf2+sth_masraf3+sth_masraf4)) +
									(sth_vergi)) /
									(sth_miktar)))
						From
							STOK_HAREKETLERI
						Where
							sth_stok_kod = @sto_kod and sth_tip = 0 and sth_normal_iade = 0 and sth_giris_depo_no = @depo
						Order by
							sth_tarih desc
					End
			End
		/* STOK DETAYLI HESAPLAMA */
		Else if((@hf_param is not null) and (@hf_param = 1))
			Begin
				if((@siralama is not null) and @siralama = 0)
					Begin
						Select
								Top 1
								@SonAlis = ((((((sth_tutar) - 
									(sth_iskonto1+sth_iskonto2+sth_iskonto3+sth_iskonto4+sth_iskonto5+sth_iskonto6)) +
									(sth_masraf1+sth_masraf2+sth_masraf3+sth_masraf4)) +
									(sth_vergi)) /
									(sth_miktar)))
						From
							STOK_HAREKETLERI
						Where
							sth_stok_kod = @sto_kod and sth_tip = 0 and sth_normal_iade = 0 
						Order by
							sth_tarih 
					End
				Else if((@siralama is not null) and @siralama = 1)
					Begin
						Select
								Top 1
								@SonAlis = ((((((sth_tutar) - 
									(sth_iskonto1+sth_iskonto2+sth_iskonto3+sth_iskonto4+sth_iskonto5+sth_iskonto6)) +
									(sth_masraf1+sth_masraf2+sth_masraf3+sth_masraf4)) +
									(sth_vergi)) /
									(sth_miktar)))
						From
							STOK_HAREKETLERI
						Where
							sth_stok_kod = @sto_kod and sth_tip = 0 and sth_normal_iade = 0
						Order by
							sth_tarih desc
					End
			End

			/* Son Alış fiyatı yok ise ortlama hesaplama */
			if (((@SonAlis is null) or (@SonAlis = 0)) and ((@hf_param is not null) and (@hf_param = 0)))
				Begin
					Select
							@SonAlis = AVG((((((sth_tutar) - 
								(sth_iskonto1+sth_iskonto2+sth_iskonto3+sth_iskonto4+sth_iskonto5+sth_iskonto6)) +
								(sth_masraf1+sth_masraf2+sth_masraf3+sth_masraf4)) +
								(sth_vergi)) /
								(sth_miktar)))
					From
						STOK_HAREKETLERI
					Where
						sth_stok_kod = @sto_kod and sth_tip = 0 and sth_normal_iade = 0 and sth_giris_depo_no = @depo
				End
			else if (((@SonAlis is null) or (@SonAlis = 0)) and ((@hf_param is not null) and (@hf_param = 1)))
				Begin
					Select
							@SonAlis = AVG((((((sth_tutar) - 
								(sth_iskonto1+sth_iskonto2+sth_iskonto3+sth_iskonto4+sth_iskonto5+sth_iskonto6)) +
								(sth_masraf1+sth_masraf2+sth_masraf3+sth_masraf4)) +
								(sth_vergi)) /
								(sth_miktar)))
					From
						STOK_HAREKETLERI
					Where
						sth_stok_kod = @sto_kod and sth_tip = 0 and sth_normal_iade = 0
				End
		if ((@SonAlis is null) or (@SonAlis = 0))
			Begin
				Select @SonAlis = sto_standartmaliyet From STOKLAR Where sto_kod = @sto_kod
			End

	 Set @SonAlis = Convert(decimal(8,2),@SonAlis)
	 RETURN @SonAlis
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_StokBirimGirisMaliyetiYilveAy]    Script Date: 26.01.2021 13:58:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION  [dbo].[mbt_StokBirimGirisMaliyetiYilveAy](@stok_kodu nvarchar(30),@depono integer,@dovizcins tinyint,@yil int,@ay int)
RETURNS float
AS
BEGIN

RETURN(

sELECT (isnull(GIRISTUTAR,0)-isnull(CIKISFIYATFARKI,0)) /
case when isnull(GIRISMIKTAR,0) > 0 then GIRISMIKTAR else 1.0 end
FROM(
SELECT sum(case when @dovizcins=1 then sho_Belge_Alt_Giris
when @dovizcins=2 then sho_Belge_Orj_Giris
else                   sho_Belge_Ana_Giris end) AS GIRISTUTAR,
sum(case when @dovizcins=1 then sho_Belge_Alt_CikisIade
when @dovizcins=2 then sho_Belge_Orj_CikisIade
else                   sho_Belge_Ana_CikisIade end *
case when sho_HareketCins in (9,15) then 1.0 else 0.0 end) AS CIKISFIYATFARKI,
sum(sho_GirisNormal) AS GIRISMIKTAR
FROM  dbo.STOK_HAREKETLERI_OZET WITH (NOLOCK)
WHERE (sho_StokKodu=@stok_kodu) AND (@depono in (0,sho_Depo)) and sho_MaliYil>=@yil-1 -- and sho_Donem=@ay
AND sho_HareketCins IN (0,1,2)
)AS HT

)
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_DonemDepodanCikanMiktar]    Script Date: 26.01.2021 13:58:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION  [dbo].[mbt_DonemDepodanCikanMiktar]
(@stok_kodu nvarchar(25),
@depo int,
@ilktarih datetime,
@sontarih datetime)
RETURNS float
AS
BEGIN
DECLARE @VAL As Float
SELECT @VAL=SUM(CASE
WHEN sth_tip IN (1,2) THEN sth_miktar
WHEN sth_tip=0 THEN (-1) * sth_miktar
ELSE 0
END
)
FROM   dbo.STOK_HAREKETLERI WITH (NOLOCK, INDEX = NDX_STOK_HAREKETLERI_04)

WHERE (sth_stok_kod=@stok_kodu) AND
((sth_tarih<=@sontarih) OR (@sontarih<='1900-1-1') OR (@sontarih is NULL)) AND
((sth_tarih>=@ilktarih) OR (@ilktarih<='1900-1-1') OR (@ilktarih is NULL)) AND
(
((sth_tip=1) and (sth_normal_iade=0) and ((sth_cikis_depo_no=@depo) OR (@depo=0)) ) OR
((sth_tip=0) and (sth_normal_iade=1) and ((sth_giris_depo_no=@depo) OR (@depo=0)) ) OR
((sth_tip=2) AND (sth_cikis_depo_no=@depo) AND (sth_giris_depo_no<>sth_cikis_depo_no))
)
AND (sth_cins in (0,1,2)) --(dbo.fn_DegerFarki_mi(sth_cins)=0)
IF @VAL is NULL SET @VAL=0
RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_DonemDepodanCikanMiktarMarkaKodu]    Script Date: 26.01.2021 13:58:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER FUNCTION  [dbo].[mbt_DonemDepodanCikanMiktarMarkaKodu]
(@marka_kodu nvarchar(25),
@depo int,
@ilktarih datetime,
@sontarih datetime)
RETURNS float
AS
BEGIN
DECLARE @VAL As Float
SELECT @VAL=SUM(CASE
WHEN sth_tip IN (1,2) THEN sth_miktar
WHEN sth_tip=0 THEN (-1) * sth_miktar
ELSE 0
END
)
FROM   dbo.STOK_HAREKETLERI WITH (NOLOCK, INDEX = NDX_STOK_HAREKETLERI_04)
,STOKLAR
WHERE sth_stok_kod=	sto_kod AND (sto_marka_kodu=@marka_kodu) AND
((sth_tarih<=@sontarih) OR (@sontarih<='1900-1-1') OR (@sontarih is NULL)) AND
((sth_tarih>=@ilktarih) OR (@ilktarih<='1900-1-1') OR (@ilktarih is NULL)) AND
(
((sth_tip=1) and (sth_normal_iade=0) and ((sth_cikis_depo_no=@depo) OR (@depo=0)) ) OR
((sth_tip=0) and (sth_normal_iade=1) and ((sth_giris_depo_no=@depo) OR (@depo=0)) ) OR
((sth_tip=2) AND (sth_cikis_depo_no=@depo) AND (sth_giris_depo_no<>sth_cikis_depo_no))
)
AND (sth_cins in (0,1,2)) --(dbo.fn_DegerFarki_mi(sth_cins)=0)
IF @VAL is NULL SET @VAL=0
RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_DonemDepoyaGirenMiktar]    Script Date: 26.01.2021 13:58:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION  [dbo].[mbt_DonemDepoyaGirenMiktar]
(@stok_kodu nvarchar(25),
@depo int,
@ilktarih datetime,
@sontarih datetime)
RETURNS float
AS
BEGIN
DECLARE @VAL As Float
SELECT @VAL=SUM(CASE
WHEN sth_tip IN (0,2) THEN sth_miktar
WHEN sth_tip=1 THEN (-1) * sth_miktar
ELSE 0
END
)
FROM   dbo.STOK_HAREKETLERI WITH (NOLOCK, INDEX = NDX_STOK_HAREKETLERI_04)
WHERE  (sth_stok_kod=@stok_kodu) AND
((sth_tarih<=@sontarih) OR (@sontarih<='1900-1-1') OR (@sontarih is NULL)) AND
((sth_tarih>=@ilktarih) OR (@ilktarih<='1900-1-1') OR (@ilktarih is NULL)) AND
(
((sth_tip=0) and (sth_normal_iade=0) and ((sth_giris_depo_no=@depo) OR (@depo=0))) OR
((sth_tip=1) and (sth_normal_iade=1) and ((sth_cikis_depo_no=@depo) OR (@depo=0))) OR
((sth_tip=2) AND (sth_giris_depo_no=@depo) AND (sth_giris_depo_no<>sth_cikis_depo_no))
)
AND (sth_cins in (0,1,2)) --(dbo.fn_DegerFarki_mi(sth_cins)=0)
IF @VAL is NULL SET @VAL=0
RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_DonemOrtalamaDevirHizi]    Script Date: 26.01.2021 13:58:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER FUNCTION  [dbo].[mbt_DonemOrtalamaDevirHizi]
(@stok nvarchar(25),
@depo int,
@ilktarih datetime,
@sontarih datetime)
RETURNS float
AS
BEGIN
DECLARE @VAL As Float
DECLARE @DonemBasi As Float
DECLARE @DonemSonu As Float
DECLARE @GirenMiktar As Float
DECLARE @CikanMiktar As Float
set @DonemBasi=dbo.fn_DepodakiMiktar(@stok,@depo,@ilktarih)
set @DonemSonu=dbo.fn_DepodakiMiktar(@stok,@depo,@sontarih)

--set @GirenMiktar=dbo.fn_DonemDepoyaGirenMiktar(@stok,@depo,@ilktarih,@sontarih)
set @CikanMiktar=dbo.mbt_DonemDepodanCikanMiktar(@stok,@depo,@ilktarih,@sontarih)
SET @VAL=0
if (@CikanMiktar<>0) AND
((@DonemBasi<>0) OR (@GirenMiktar-@CikanMiktar<>0)) AND
( ((2*@DonemBasi)+(@GirenMiktar-@CikanMiktar)) <> 0)
set @VAL=@CikanMiktar/ ((@DonemSonu+@DonemBasi)/2)
RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_DepodanCikacakSiparisMiktari]    Script Date: 26.01.2021 13:58:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION  [dbo].[mbt_DepodanCikacakSiparisMiktari]
(@stok_kodu nvarchar(25),
@depo int,
@TesTar DateTime,
@Onaylilar tinyint) -- 0:Onaysiz ; 1:Onayli ; 2:hepsi
RETURNS float
AS
BEGIN
DECLARE @VAL As Float
SELECT @VAL=SUM(dbo.fn_Evrak_Kalan_Miktar(sip_miktar,sip_teslim_miktar,sip_kapat_fl))
FROM dbo.SIPARISLER WITH (NOLOCK)
WHERE (sip_tip=0) AND sip_stok_kod = @stok_kodu AND  -- Alınan sipariş
((sip_teslim_tarih <= @TesTar ) OR (@TesTar IS NULL)) And
( ((sip_OnaylayanKulNo=0) AND (@Onaylilar=0)) OR
((sip_OnaylayanKulNo<>0) AND (@Onaylilar=1)) OR
(@Onaylilar=2)
) AND
(sip_depono = @depo) and sip_tarih<=(GETDATE()-30)
RETURN IsNULL(@VAL, 0)
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_DonemDepoyaGirenMiktarMarkaKodu]    Script Date: 26.01.2021 13:58:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION  [dbo].[mbt_DonemDepoyaGirenMiktarMarkaKodu]
(@marka_kodu nvarchar(25),
@depo int,
@ilktarih datetime,
@sontarih datetime)
RETURNS float
AS
BEGIN
DECLARE @VAL As Float
SELECT @VAL=SUM(CASE
WHEN sth_tip IN (0,2) THEN sth_miktar
WHEN sth_tip=1 THEN (-1) * sth_miktar
ELSE 0
END
)
FROM   dbo.STOK_HAREKETLERI WITH (NOLOCK, INDEX = NDX_STOK_HAREKETLERI_04)
,STOKLAR
WHERE sth_stok_kod=	sto_kod AND (sto_marka_kodu=@marka_kodu) AND
((sth_tarih<=@sontarih) OR (@sontarih<='1900-1-1') OR (@sontarih is NULL)) AND
((sth_tarih>=@ilktarih) OR (@ilktarih<='1900-1-1') OR (@ilktarih is NULL)) AND
(
((sth_tip=0) and (sth_normal_iade=0) and ((sth_giris_depo_no=@depo) OR (@depo=0))) OR
((sth_tip=1) and (sth_normal_iade=1) and ((sth_cikis_depo_no=@depo) OR (@depo=0))) OR
((sth_tip=2) AND (sth_giris_depo_no=@depo) AND (sth_giris_depo_no<>sth_cikis_depo_no))
)
AND (sth_cins in (0,1,2)) --(dbo.fn_DegerFarki_mi(sth_cins)=0)
IF @VAL is NULL SET @VAL=0
RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_DepodakiMiktar]    Script Date: 26.01.2021 13:58:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION  [dbo].[mbt_DepodakiMiktar] (@stok_kodu nvarchar(25),
@depo int,
@tarih datetime)
RETURNS float
AS
BEGIN
DECLARE @VAL As Float
/*  if (@tarih<='1900-1-1') OR (@tarih is NULL)
SET @VAL = dbo.fn_DepodakiMiktarSonDurum(@stok_kodu,@depo)
else*/
SELECT @VAL=SUM(CASE
WHEN (sth_tip=0) OR ((sth_tip=2) AND (sth_giris_depo_no=@depo)) THEN sth_miktar
WHEN (sth_tip=1) OR ((sth_tip=2) AND (sth_cikis_depo_no=@depo)) THEN (-1) * sth_miktar
ELSE 0
END)
FROM   dbo.STOK_HAREKETLERI WITH (NOLOCK)
WHERE  (sth_stok_kod=@stok_kodu) AND
((sth_tarih<=@tarih) OR (@tarih<='1900-1-1') OR (@tarih is NULL)) AND
(
((sth_tip=0) and ((sth_giris_depo_no=@depo) OR (@depo=0))) OR
((sth_tip=1) and ((sth_cikis_depo_no=@depo) OR (@depo=0))) OR
((sth_tip=2) AND
(sth_giris_depo_no<>sth_cikis_depo_no) AND
((sth_giris_depo_no=@depo) OR (sth_cikis_depo_no=@depo))
)
) AND
(NOT (sth_cins in (9,10,11,15))) --(dbo.fn_DegerFarki_mi(sth_cins)=0)
AND sth_normal_iade=0
IF @VAL is NULL SET @VAL=0
IF Abs(@VAL) <= 1E-8 SET @VAL=0
RETURN ROUND(@VAL,8)
END
GO