USE [MikroDB_V16_HILAL2019]
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_DepoyaGirecekSiparisMiktari]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_DepoyaGirecekSiparisMiktari]
(@cari_kodu nvarchar(25),
@stok_kodu nvarchar(25),
@depo int,
@TesTar DateTime,
@Onaylilar tinyint, -- 0:Onaysiz ; 1:Onayli ; 2:hepsi
@Kapatilmis tinyint) -- 0 Kapatilmamis - 1 Kapatilmis
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=1) AND sip_stok_kod = @stok_kodu AND  -- Verilen 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_musteri_kod = @cari_kodu)
*/
SELECT @VAL=SUM(sip_miktar-sip_teslim_miktar) 
FROM SIPARISLER 
WHERE 
sip_stok_kod=@stok_kodu and sip_teslim_miktar<sip_miktar  and (sip_tarih>=@TesTar) /*and sip_kapat_fl=0*/ and sip_depono=@depo and sip_tip=1
AND
(sip_musteri_kod = @cari_kodu) and ( ((sip_OnaylayanKulNo=0) AND (@Onaylilar=0)) OR
((sip_OnaylayanKulNo<>0) AND (@Onaylilar=1)) OR
(@Onaylilar=2)
)
and ( ((sip_kapat_fl=0) AND (@Kapatilmis=0)) OR
((sip_kapat_fl=1) AND (@Kapatilmis=1)) OR
( (sip_kapat_fl=0 or sip_kapat_fl=1) and @Kapatilmis=2)
)
RETURN IsNULL(@VAL, 0)
END

GO

/****** Object:  UserDefinedFunction [dbo].[mbt_DepodanCikacakSiparisMiktari]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION  [dbo].[mbt_DepodanCikacakSiparisMiktari]
(@stok_kodu nvarchar(25),
@depo int,
@BasTar DateTime,
@TesTar DateTime,
@Onaylilar tinyint, -- 0:Onaysiz ; 1:Onayli ; 2:hepsi
@Kapatilmis tinyint) -- 0 Kapatilmamis - 1 Kapatilmis
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 BETWEEN  @BasTar And @TesTar
*/
SELECT @VAL=SUM(sip_miktar-sip_teslim_miktar) FROM SIPARISLER 
WHERE sip_stok_kod=@stok_kodu and sip_teslim_miktar<sip_miktar  and (sip_tarih BETWEEN  @BasTar And @TesTar) /*and sip_kapat_fl=0*/ and sip_depono=@depo and sip_tip=0
and ( ((sip_OnaylayanKulNo=0) AND (@Onaylilar=0)) OR
((sip_OnaylayanKulNo<>0) AND (@Onaylilar=1)) OR
(@Onaylilar=2)
)
and ( ((sip_kapat_fl=0) AND (@Kapatilmis=0)) OR
((sip_kapat_fl=1) AND (@Kapatilmis=1)) OR
( (sip_kapat_fl=0 or sip_kapat_fl=1) and @Kapatilmis=2)
)

RETURN IsNULL(@VAL, 0)
END

--SELECT SUM(sip_miktar-sip_teslim_miktar) FROM SIPARISLER WHERE sip_stok_kod='ALFA-1120' and sip_teslim_miktar<sip_miktar and (sip_teslim_tarih BETWEEN  '2021-01-01' And '2021-04-22') and sip_kapat_fl=0 and sip_depono=1 and sip_tip=0
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_DonemDepoyaGirenMiktarMarkaKodu]    Script Date: 26.04.2021 12:50:09 ******/
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_DonemDepodanCikanMiktarMarkaKodu]    Script Date: 26.04.2021 12:50:09 ******/
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_DonemDepodanCikanMiktar]    Script Date: 26.04.2021 12:50:09 ******/
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_DonemDepoyaGirenMiktar]    Script Date: 26.04.2021 12:50:09 ******/
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_adrestekiBedenModelSezonMiktar]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_adrestekiBedenModelSezonMiktar](@stokKodu nvarchar(50), @BedenKodu nvarchar(50),@ModelKodu nvarchar(50),@SezonKodu nvarchar(50),@depoKodu int,@adresKodu nvarchar(50))
RETURNS float
AS
BEGIN
  DECLARE @VAL As float
  DECLARE @VALCIKIS As float
  DECLARE @VALGIRIS As float
         
         SELECT @VALCIKIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND BEDENKODU=@BedenKodu AND MODELKODU=@ModelKodu AND SEZONKODU=@SezonKodu) and (GIRISCIKISTIPI='C') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         SELECT @VALGIRIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND BEDENKODU=@BedenKodu AND MODELKODU=@ModelKodu AND SEZONKODU=@SezonKodu) and (GIRISCIKISTIPI='G') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         IF @VALGIRIS is NULL SET @VALGIRIS=0
         IF @VALCIKIS is NULL SET @VALCIKIS=0
         SET @VAL=(@VALGIRIS-@VALCIKIS)
        
  IF @VAL is NULL SET @VAL=0
  RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_adrestekiBedenModelMiktar]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_adrestekiBedenModelMiktar](@stokKodu nvarchar(50), @BedenKodu nvarchar(50),@ModelKodu nvarchar(50),@depoKodu int,@adresKodu nvarchar(50))
RETURNS float
AS
BEGIN
  DECLARE @VAL As float
  DECLARE @VALCIKIS As float
  DECLARE @VALGIRIS As float
         
         SELECT @VALCIKIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND BEDENKODU=@BedenKodu AND MODELKODU=@ModelKodu) and (GIRISCIKISTIPI='C') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         SELECT @VALGIRIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND BEDENKODU=@BedenKodu AND MODELKODU=@ModelKodu) and (GIRISCIKISTIPI='G') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         IF @VALGIRIS is NULL SET @VALGIRIS=0
         IF @VALCIKIS is NULL SET @VALCIKIS=0
         SET @VAL=(@VALGIRIS-@VALCIKIS)
        
  IF @VAL is NULL SET @VAL=0
  RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_adrestekiSezonMiktar]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_adrestekiSezonMiktar](@stokKodu nvarchar(50), @SezonKodu nvarchar(50),@depoKodu int,@adresKodu nvarchar(50))
RETURNS float
AS
BEGIN
  DECLARE @VAL As float
  DECLARE @VALCIKIS As float
  DECLARE @VALGIRIS As float
         
         SELECT @VALCIKIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND SEZONKODU=@SezonKodu) and (GIRISCIKISTIPI='C') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         SELECT @VALGIRIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND SEZONKODU=@SezonKodu) and (GIRISCIKISTIPI='G') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         IF @VALGIRIS is NULL SET @VALGIRIS=0
         IF @VALCIKIS is NULL SET @VALCIKIS=0
         SET @VAL=(@VALGIRIS-@VALCIKIS)
        
  IF @VAL is NULL SET @VAL=0
  RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_adrestekiModelMiktar]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_adrestekiModelMiktar](@stokKodu nvarchar(50), @ModelKodu nvarchar(50),@depoKodu int,@adresKodu nvarchar(50))
RETURNS float
AS
BEGIN
  DECLARE @VAL As float
  DECLARE @VALCIKIS As float
  DECLARE @VALGIRIS As float
         
         SELECT @VALCIKIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND MODELKODU=@ModelKodu) and (GIRISCIKISTIPI='C') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         SELECT @VALGIRIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND MODELKODU=@ModelKodu) and (GIRISCIKISTIPI='G') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         IF @VALGIRIS is NULL SET @VALGIRIS=0
         IF @VALCIKIS is NULL SET @VALCIKIS=0
         SET @VAL=(@VALGIRIS-@VALCIKIS)
        
  IF @VAL is NULL SET @VAL=0
  RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_adrestekiBedenNumarasiMiktar]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_adrestekiBedenNumarasiMiktar](@stokKodu nvarchar(50), @bedenKodu nvarchar(50),@BedenNumarasi nvarchar(50),@depoKodu int,@adresKodu nvarchar(50))
RETURNS float
AS
BEGIN
  DECLARE @VAL As float
  DECLARE @VALCIKIS As float
  DECLARE @VALGIRIS As float
         
         SELECT @VALCIKIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND BEDENKODU=@bedenKodu and BEDENNUMARASI=@BedenNumarasi) and (GIRISCIKISTIPI='C') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         SELECT @VALGIRIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND BEDENKODU=@bedenKodu and BEDENNUMARASI=@BedenNumarasi) and (GIRISCIKISTIPI='G') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         IF @VALGIRIS is NULL SET @VALGIRIS=0
         IF @VALCIKIS is NULL SET @VALCIKIS=0
         SET @VAL=(@VALGIRIS-@VALCIKIS)
        
  IF @VAL is NULL SET @VAL=0
  RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_adrestekiBedenMiktar]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_adrestekiBedenMiktar](@stokKodu nvarchar(50), @bedenKodu nvarchar(50),@depoKodu int,@adresKodu nvarchar(50))
RETURNS float
AS
BEGIN
  DECLARE @VAL As float
  DECLARE @VALCIKIS As float
  DECLARE @VALGIRIS As float
         
         SELECT @VALCIKIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND BEDENKODU=@bedenKodu) and (GIRISCIKISTIPI='C') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         SELECT @VALGIRIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND BEDENKODU=@bedenKodu) and (GIRISCIKISTIPI='G') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         IF @VALGIRIS is NULL SET @VALGIRIS=0
         IF @VALCIKIS is NULL SET @VALCIKIS=0
         SET @VAL=(@VALGIRIS-@VALCIKIS)
        
  IF @VAL is NULL SET @VAL=0
  RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_adrestekiSiparisMiktari]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_adrestekiSiparisMiktari](@adresKodu nvarchar(50),@stokKodu nvarchar(50), @sipEvrakSeri nvarchar(50),@sipEvrakSira nvarchar(50),@depoKodu int,@bagliSatirID nvarchar(50))
RETURNS float
AS
BEGIN
  DECLARE @VAL As float
  DECLARE @VALCIKIS As float
  DECLARE @VALGIRIS As float
         
         SELECT @VALCIKIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND EVRAKSERI=@sipEvrakSeri AND EVRAKSIRA=@sipEvrakSira) and (GIRISCIKISTIPI='G') AND (DEPONO=@depokodu) AND EVRAKTIPI='SIPARISTOPLAMA' AND BAGLISATIRID=@bagliSatirID
         SELECT @VALGIRIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND EVRAKSERI=@sipEvrakSeri AND EVRAKSIRA=@sipEvrakSira) and (GIRISCIKISTIPI='C') AND (DEPONO=@depokodu) AND EVRAKTIPI='SIPARISTOPLAMA' AND BAGLISATIRID=@bagliSatirID
         IF @VALGIRIS is NULL SET @VALGIRIS=0
         IF @VALCIKIS is NULL SET @VALCIKIS=0
         SET @VAL=(@VALGIRIS-@VALCIKIS)
        
  IF @VAL is NULL SET @VAL=0
  RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_StokBirimGirisMaliyetiYilveAy]    Script Date: 26.04.2021 12:50:09 ******/
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_StokBirimGirisMaliyetiYil]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[mbt_StokBirimGirisMaliyetiYil](@stok_kodu nvarchar(30),@depono integer,@dovizcins tinyint,@yil 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
)AS HT
)
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_Stok_Son_Giris_Fiyati]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[mbt_Stok_Son_Giris_Fiyati]
(
       @StokKodu NVARCHAR (25),
       @RefTarih DATETIME,
       @RetType TINYINT /*0:Son giriş fiyatı, 1: Ortalama giriş fiyatı*/
)
RETURNS FLOAT
AS
BEGIN
DECLARE @Deger FLOAT,
             @Miktar FLOAT,
             @RetVAL FLOAT=0.0

IF @RetType=0
BEGIN
SELECT TOP 1 @Deger=STH_NET_DEGER_ANA,@Miktar=sth_miktar
FROM STOK_HAREKETLERI_VIEW_WITH_INDEX_02 WITH (NOLOCK)
WHERE sth_evraktip IN (3,13) AND
             sth_cins NOT IN (9,15) AND
             sth_normal_iade=0 AND
             sth_stok_kod=@StokKodu AND
             (@RefTarih IS NULL OR sth_tarih<=@RefTarih) AND
             sth_miktar>0
ORDER BY sth_tarih DESC
END
ELSE
BEGIN
SELECT @Deger=SUM(STH_NET_DEGER_ANA),@Miktar=SUM(sth_miktar)
FROM STOK_HAREKETLERI_VIEW_WITH_INDEX_02 WITH (NOLOCK)
WHERE sth_evraktip IN (3,13) AND
             sth_cins NOT IN (9,15) AND
             sth_normal_iade=0 AND
             sth_stok_kod=@StokKodu AND
             (@RefTarih IS NULL OR sth_tarih<=@RefTarih) AND
             sth_miktar>0
END

IF @Deger IS NULL SET @Deger=0.0
IF @Miktar IS NULL SET @Miktar=0.0

IF @Miktar>0 SET @RetVAL=@Deger/@Miktar

IF @RetVAL=0 SET @RetVAL=dbo.fn_StokSatisFiyati(@StokKodu ,8,0,1)
RETURN @RetVAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_CariTemsilciBul]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION [dbo].[mbt_CariTemsilciBul](@musteriKodu nvarchar(50))
RETURNS Nvarchar(50)
AS
BEGIN
         declare @VAL NVARCHAR(50)
         SELECT @VAL=cari_temsilci_kodu  FROM  CARI_HESAPLAR WHERE cari_kod=@musteriKodu
        
  IF @VAL is NULL SET @VAL='TANIMSIZ'
  RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_Stok_Son_Giris_Miktari]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION [dbo].[mbt_Stok_Son_Giris_Miktari]
(
       @StokKodu NVARCHAR (25),
       @RefTarih DATETIME
       
)
RETURNS FLOAT
AS
BEGIN
DECLARE @Deger FLOAT

BEGIN
SELECT @Deger=SUM(sth_miktar)
FROM STOK_HAREKETLERI_VIEW_WITH_INDEX_02 WITH (NOLOCK)
WHERE sth_evraktip IN (3,13) AND
             sth_cins NOT IN (9,15) AND
             sth_normal_iade=0 AND
             sth_stok_kod=@StokKodu AND
             (@RefTarih IS NULL OR sth_tarih<=@RefTarih) AND
             sth_miktar>0
END

IF @Deger IS NULL SET @Deger=0.0




RETURN @Deger
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_Stok_Son_Giris_Tarihi]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION [dbo].[mbt_Stok_Son_Giris_Tarihi]
(
       @StokKodu NVARCHAR (25),
       @RefTarih DATETIME
       
)
RETURNS datetime
AS
BEGIN
DECLARE @Deger datetime

BEGIN
SELECT top 1  @Deger=sth_tarih
FROM STOK_HAREKETLERI WITH (NOLOCK)
WHERE sth_evraktip IN (3,13) AND
             sth_cins NOT IN (9,15) AND
             sth_normal_iade=0 AND
             sth_stok_kod=@StokKodu AND
             (@RefTarih IS NULL OR sth_tarih<=@RefTarih) AND
             sth_miktar>0
END

IF @Deger IS NULL SET @Deger= Cast('1900-01-01' As DateTime)




RETURN @Deger
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_Stok_Fifo_Fiyati]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION [dbo].[mbt_Stok_Fifo_Fiyati]
(
       @STOKKODU NVARCHAR (50), @DEPOKODU INT,@TARIH datetime,@FATURADAKIMIKTAR float
       
)
RETURNS float
AS
BEGIN

DECLARE @SONGIRISTARIHI DATETIME
DECLARE @SONGIRISMIKTARI FLOAT
DECLARE @SONGIRISFIYATI FLOAT
DECLARE @TARIHTENONCEKIMIKTAR FLOAT
DECLARE @TARIHTEKIMIKTAR FLOAT
DECLARE @TARIHTEKIMALIYET FLOAT
DECLARE @TARIHTEKISONALISFIYATI FLOAT

DECLARE @Deger float

BEGIN
SELECT 
 @TARIHTENONCEKIMIKTAR=(dbo.fn_DepodakiMiktar(S.sto_kod,@DEPOKODU,@TARIH-1))
,@SONGIRISFIYATI=dbo.mbt_Stok_Son_Giris_Fiyati(S.sto_kod,@TARIH,0)
,@SONGIRISMIKTARI=dbo.mbt_Stok_Son_Giris_Miktari(S.sto_kod,@TARIH)
,@SONGIRISTARIHI=dbo.mbt_Stok_Son_Giris_Tarihi(S.sto_kod,@TARIH)
,@TARIHTEKIMIKTAR=(dbo.fn_DepodakiMiktar(S.sto_kod,@DEPOKODU,@TARIH) +@FATURADAKIMIKTAR)
,@TARIHTEKIMALIYET=dbo.mbt_StokBirimGirisMaliyetiYilveAy(S.sto_kod,@DEPOKODU,0,YEAR(@TARIH),MONTH(@TARIH))

FROM STOKLAR S where S.sto_kod=@STOKKODU

END

IF @FATURADAKIMIKTAR<@TARIHTENONCEKIMIKTAR
BEGIN
		IF @SONGIRISTARIHI<@TARIH
		BEGIN
			set @Deger= @SONGIRISFIYATI
		END
		
END
ELSE
BEGIN
	SET @Deger=@TARIHTEKIMALIYET
END



IF @Deger IS NULL SET @Deger= Cast(0 As Float)

RETURN @Deger
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_StokMarkaSatisTutari]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER FUNCTION [dbo].[mbt_StokMarkaSatisTutari]
(
       @MARKAKODU NVARCHAR (50), @DEPOKODU INT,@ILKTARIH datetime,@SONTARIH datetime
       
)
RETURNS float
AS
BEGIN

DECLARE @Deger float

SELECT @Deger=SUM(sth_tutar-sth_iskonto1-sth_iskonto2-sth_iskonto3-sth_iskonto4-sth_iskonto5-sth_iskonto6) FROM STOK_HAREKETLERI WITH (INDEX(NDX_STOK_HAREKETLERI_04)),STOKLAR WHERE 
sth_stok_kod=sto_kod and sto_marka_kodu=@MARKAKODU
and sth_tip=1 and sth_evraktip IN (1,4) And sth_normal_iade=0
and sth_tarih Between @ILKTARIH AND @SONTARIH

IF @Deger IS NULL SET @Deger= Cast(0 As Float)

RETURN @Deger
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_StokMarkaAlisTutari]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER FUNCTION [dbo].[mbt_StokMarkaAlisTutari]
(
       @MARKAKODU NVARCHAR (50), @DEPOKODU INT,@ILKTARIH datetime,@SONTARIH datetime
       
)
RETURNS float
AS
BEGIN

DECLARE @Deger float

SELECT @Deger=SUM(sth_tutar-sth_iskonto1-sth_iskonto2-sth_iskonto3-sth_iskonto4-sth_iskonto5-sth_iskonto6) FROM STOK_HAREKETLERI WITH (INDEX(NDX_STOK_HAREKETLERI_04)),STOKLAR WHERE 
sth_stok_kod=sto_kod and sto_marka_kodu=@MARKAKODU
and sth_tip=0 and sth_evraktip IN (3,13) And sth_normal_iade=0
and sth_tarih Between @ILKTARIH AND @SONTARIH

IF @Deger IS NULL SET @Deger= Cast(0 As Float)

RETURN @Deger
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_DepodakiMiktarMarkaKodu]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION [dbo].[mbt_DepodakiMiktarMarkaKodu] (@marka_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),STOKLAR
WHERE  sth_stok_kod=sto_kod and  (sto_marka_kodu=@marka_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,15))) --(dbo.fn_DegerFarki_mi(sth_cins)=0)
IF @VAL is NULL SET @VAL=0
IF Abs(@VAL) <= 1E-8 SET @VAL=0
RETURN ROUND(@VAL,8)
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_DonemOrtalamaDevirHizi]    Script Date: 26.04.2021 12:50:09 ******/
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_DonemOrtalamaDevirHiziMarka]    Script Date: 26.04.2021 12:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION [dbo].[mbt_DonemOrtalamaDevirHiziMarka]
(@marka nvarchar(25),
@depo int,
@ilktarih datetime,
@sontarih datetime)
RETURNS float
AS
BEGIN
DECLARE @VAL As Float
DECLARE @DonemBasi As Float
DECLARE @GirenMiktar As Float
DECLARE @CikanMiktar As Float
set @DonemBasi=dbo.mbt_DepodakiMiktarMarkaKodu(@marka,@depo,@ilktarih)
set @GirenMiktar=dbo.mbt_DonemDepoyaGirenMiktarMarkaKodu(@marka,@depo,@ilktarih,@sontarih)
set @CikanMiktar=dbo.mbt_DonemDepodanCikanMiktarMarkaKodu(@marka,@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/ (((2*@DonemBasi)+(@GirenMiktar-@CikanMiktar)) /2)
RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_Stok_Son_Alis_Fiyati]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[mbt_Stok_Son_Alis_Fiyati] (@StokKod as nvarchar(25),@tarih datetime)
RETURNS float AS
BEGIN
Declare @StokFiyat as float

BEGIN
SELECT TOP 1 @StokFiyat=((sth_tutar-sth_iskonto1-sth_iskonto2-sth_iskonto3-sth_iskonto4-sth_iskonto5-sth_iskonto6)/sth_miktar)
FROM dbo.STOK_HAREKETLERI (NOLOCK)
WHERE (sth_stok_kod=@StokKod) AND
(sth_evraktip=3 or sth_evraktip=13) AND sth_normal_iade=0 and sth_tarih<=@tarih
ORDER BY sth_tarih DESC
end

IF @StokFiyat is NULL SET @StokFiyat=0
Return @StokFiyat
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_StokSatisTutari]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER FUNCTION [dbo].[mbt_StokSatisTutari]
(
       @STOKKODU NVARCHAR (50), @DEPOKODU INT,@ILKTARIH datetime,@SONTARIH datetime
       
)
RETURNS float
AS
BEGIN

DECLARE @Deger float

SELECT @Deger=SUM(sth_tutar-sth_iskonto1-sth_iskonto2-sth_iskonto3-sth_iskonto4-sth_iskonto5-sth_iskonto6) FROM STOK_HAREKETLERI WITH (INDEX(NDX_STOK_HAREKETLERI_04)) WHERE 
sth_stok_kod=@STOKKODU
and sth_tip=1 and sth_evraktip IN (1,4) And sth_normal_iade=0
and sth_tarih Between @ILKTARIH AND @SONTARIH

IF @Deger IS NULL SET @Deger= Cast(0 As Float)

RETURN @Deger
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_StokAlisTutari]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER FUNCTION [dbo].[mbt_StokAlisTutari]
(
       @STOKKODU NVARCHAR (50), @DEPOKODU INT,@ILKTARIH datetime,@SONTARIH datetime
       
)
RETURNS float
AS
BEGIN

DECLARE @Deger float

SELECT @Deger=SUM(sth_tutar-sth_iskonto1-sth_iskonto2-sth_iskonto3-sth_iskonto4-sth_iskonto5-sth_iskonto6) FROM STOK_HAREKETLERI WITH (INDEX(NDX_STOK_HAREKETLERI_04)) WHERE 
sth_stok_kod=@STOKKODU
and sth_tip=0 and sth_evraktip IN (3,13) And sth_normal_iade=0
and sth_tarih Between @ILKTARIH AND @SONTARIH

IF @Deger IS NULL SET @Deger= Cast(0 As Float)

RETURN @Deger
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_DonemOrtalamaDevirHiziHesapla]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





ALTER FUNCTION [dbo].[mbt_DonemOrtalamaDevirHiziHesapla]
(@DonemBasi As Float,
@DonemSonu As Float,
@CikanMiktar As Float
)
RETURNS float
AS
BEGIN

DECLARE @VAL As Float
SET @VAL=0
if (@CikanMiktar<>0  and (@DonemSonu+@DonemBasi)<>0) 
	set @VAL=@CikanMiktar/ ((@DonemSonu+@DonemBasi)/2)

RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_VerilmesiGerekenSiparisMiktariHesapla]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_VerilmesiGerekenSiparisMiktariHesapla]
(@DonemSonu As Float,
@MinimumStokSeviyesi As Float,
@KoliMiktari As int,
@MusterideBekleyenMiktar As Float,
@UreticideBekleyenMiktar As Float
)
RETURNS float
AS
BEGIN
if @KoliMiktari=0
Set @KoliMiktari=1
DECLARE @VAL As FLOAT
DECLARE @BEKLEYENSIPARISFARKI AS FLOAT

SET @BEKLEYENSIPARISFARKI = @MinimumStokSeviyesi - @UreticideBekleyenMiktar  + @MusterideBekleyenMiktar

DECLARE @VERILMESIGEREKENSIPARISFARKI AS FLOAT

if @DonemSonu>=@BEKLEYENSIPARISFARKI
BEGIN		
	SET @VERILMESIGEREKENSIPARISFARKI =0 -- SIPARIS VERMEYE GEREK YOK SADECE MINIMIM STOK SEVIYESINE TAMAMLA ASAGIDA
END
ELSE
BEGIN	
	set @VERILMESIGEREKENSIPARISFARKI =   @BEKLEYENSIPARISFARKI - @DonemSonu
END

-------------------
if @VERILMESIGEREKENSIPARISFARKI>0
BEGIN

		if @VERILMESIGEREKENSIPARISFARKI>@MinimumStokSeviyesi
		BEGIN
			SET @VAL=0
		END

		else

		BEGIN

			DECLARE @MODEKALAN AS INT

			if @VERILMESIGEREKENSIPARISFARKI<@KoliMiktari
			BEGIN
				SET @VAL = @KoliMiktari
			END
			ELSE
			BEGIN
				SET @MODEKALAN = CAST(@VERILMESIGEREKENSIPARISFARKI AS INT) % CAST(@KoliMiktari AS INT)
				if @MODEKALAN>0 
				BEGIN
					SET @VAL = 10-@MODEKALAN + @VERILMESIGEREKENSIPARISFARKI 
				END
				ELSE
				BEGIN
					SET @VAL = @VERILMESIGEREKENSIPARISFARKI
				END
			END

		END

END
ELSE
BEGIN
--verılmesi gereken sipariş miktarı negatif yani dondem sonu daha buyuk
	set @VAL=@VERILMESIGEREKENSIPARISFARKI
END
IF @VAL is NULL SET @VAL=0
-- TEST İÇİN
-- SELECT [dbo].[mbt_VerilmesiGerekenSiparisMiktariHesapla](0,48,12,12,12)
RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_CariHesapVade2]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[mbt_CariHesapVade2]
(
  @CariKodu NVARCHAR (25)
)
RETURNS DATETIME
AS
BEGIN
DECLARE @BakiyeIzleme FLOAT,
    @Id UNIQUEIDENTIFIER,
    @Vade DATETIME,
    @ReferansTarih DATETIME,
    @OrtalamaVade DATETIME,
    @EvrakTutar FLOAT,
    @BakiyeyeKonuTutar FLOAT
SET @ReferansTarih=dbo.fn_DatePart(GETDATE())
DECLARE @BakiyeKonuKayitlar TABLE
  (
    RecNo UNIQUEIDENTIFIER,
    VadeTarihi DATETIME,
    BakiyeyeKonuTutar FLOAT,
    GunFarki INT
  )
DECLARE @CariHareketler TABLE
  (
    cha_Guid UNIQUEIDENTIFIER,
    cha_cinsi TINYINT,
    cha_evrak_tip TINYINT,
    cha_tarihi DATETIME,
    CHA_VADE_TARIHI DATETIME,
    MEBLAG_SIGN FLOAT,
    TUTAR FLOAT,
    CHA_KAPANAN_MEBLAG FLOAT
  )  
INSERT INTO @CariHareketler
SELECT TOP 100 PERCENT 
          cha_Guid,
          cha_cinsi,
          cha_evrak_tip,
          cha_tarihi,
          CHA_VADE_TARIHI,
          CASE WHEN CHA_CARI_BORC_ALACAK_TIP=0 THEN 1.0 ELSE -1.0 END AS MEBLAG_SIGN,
          CHA_CARI_MEBLAG_ANA AS TUTAR,
          CAST(0.0 AS FLOAT) CHA_KAPANAN_MEBLAG
          FROM dbo.CARI_HESAP_HAREKETLERI_VIEW_WITH_INDEX_02 WITH (NOLOCK)
          WHERE (cha_cari_cins=0) AND
              (cha_kod=@CariKodu)
          ORDER BY cha_kod
          
SELECT @BakiyeIzleme=SUM(TUTAR*MEBLAG_SIGN) FROM @CariHareketler
IF @BakiyeIzleme IS NULL SET @BakiyeIzleme=0.0
IF ABS(@BakiyeIzleme)<=0
  RETURN NULL
      DECLARE CariHareketlerCursor CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
        SELECT TOP 100 PERCENT
            cha_Guid,
            (TUTAR-CHA_KAPANAN_MEBLAG)*MEBLAG_SIGN,
            CHA_VADE_TARIHI
        FROM @CariHareketler
        WHERE
          ((@BakiyeIzleme>0 AND MEBLAG_SIGN>0) OR
           (@BakiyeIzleme<0 AND MEBLAG_SIGN<0)) AND
           TUTAR>CHA_KAPANAN_MEBLAG
        ORDER BY CASE WHEN cha_evrak_tip IN (59,46,47) OR cha_cinsi IN (11,16) THEN 1 ELSE 0 END,        
            cha_tarihi DESC,
            CHA_VADE_TARIHI DESC
            
        OPEN CariHareketlerCursor
        FETCH NEXT FROM CariHareketlerCursor INTO @Id, @EvrakTutar, @Vade
        WHILE @@FETCH_STATUS = 0 AND ABS(@BakiyeIzleme)>1
        BEGIN  
        
          IF @BakiyeIzleme>0
            SET @BakiyeyeKonuTutar = dbo.fn_MIN(@BakiyeIzleme,@EvrakTutar) 
              ELSE SET @BakiyeyeKonuTutar =  dbo.fn_MAX(@BakiyeIzleme,@EvrakTutar) 
          SET @BakiyeIzleme = @BakiyeIzleme - @BakiyeyeKonuTutar
                           
          INSERT INTO @BakiyeKonuKayitlar 
            VALUES (@Id,@Vade,@BakiyeyeKonuTutar,dbo.fn_gunfarkibul(@ReferansTarih,@Vade)) 
          FETCH NEXT FROM CariHareketlerCursor INTO @Id, @EvrakTutar, @Vade
        END
        CLOSE CariHareketlerCursor
        DEALLOCATE CariHareketlerCursor
SELECT TOP 100 PERCENT
  @OrtalamaVade=CASE 
          WHEN SUM(BakiyeyeKonuTutar)<>0 THEN DATEADD(DAY,CAST(CAST(SUM(BakiyeyeKonuTutar*GunFarki) / SUM(BakiyeyeKonuTutar) AS DECIMAL) AS INT), @ReferansTarih) 
          ELSE @ReferansTarih 
          END
FROM @BakiyeKonuKayitlar
  RETURN @OrtalamaVade
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_CariHesapVade]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[mbt_CariHesapVade] (@caricins as tinyint, @carikod as varchar(25), @srmkod as varchar (25))

RETURNS datetime AS

BEGIN

Declare @meblag as float

Declare @summeblag as float

Declare @vade as int

Declare @summeblagvade as float

Declare @kapanan as float

Declare @rec as uniqueidentifier

Declare @vadetarih as datetime

Declare @tarih as datetime

Declare @mintarih as datetime -- Kapanmayan min evrak tarihi

Declare @hartip as tinyint

Declare @bakiye as float

Declare @end as tinyint

Declare @Xvadetarih as float -- Ortalama Vade Tarihi

Declare @Xevrvade as float -- Ortalama Evrak Tarihi

SET @bakiye = ISNULL(dbo.fn_CariHesapBakiye('',0,@carikod,N'',N'',NULL,0,0,0,0,0) ,0)

IF @bakiye < = 0 set @hartip = 1 else set @hartip = 0

Set @bakiye = ABS(@bakiye)

IF Round(@bakiye,2,1) <= 0 or (@caricins Not in(0,1)) begin SET @end = 1 end else begin SET @end = 0 end

SET @summeblag = 0

SET @summeblagvade = 0

SET @kapanan = 0

(Select top 1 @tarih = cha_tarihi, @vadetarih = dbo.fn_OpVadeTarih(cha_vade,cha_tarihi), @rec = cha_Guid

From CARI_HESAP_HAREKETLERI Where (cha_cari_cins = @caricins and cha_kod = @carikod and cha_tip = @hartip and cha_tpoz = 0 and ((cha_srmrkkodu=@srmkod) OR (@srmkod=''))))

Order By cha_tarihi Desc, dbo.fn_OpVadeTarih(cha_vade,cha_tarihi) Desc ,cha_Guid Desc

While @end = 0

Begin

(Select @meblag = Case When (cha_cinsi in (13,29)) Then (cha_meblag- cha_vergi1 - cha_vergi2 - cha_vergi3 - cha_vergi4 - cha_vergi5 - cha_vergi6 - cha_vergi7 - cha_vergi8 - cha_vergi9 - cha_vergi10)

When (cha_cinsi= 33) Then cha_aratoplam WHEN ((cha_cinsi=11) or (cha_evrak_tip=59)) AND (dbo.fn_FirmaAnaDovizCinsi()<> cha_d_cins) THEN 0

Else cha_meblag End, @vade = dbo.fn_OpVadeGun(cha_vade,cha_tarihi) From CARI_HESAP_HAREKETLERI Where cha_Guid = @rec )

(Select @kapanan = Sum(chk_Tutar) From CARI_HAREKET_BORC_ALACAK_ESLEME Where
(chk_Chcinsi = @caricins and chk_ChKodu = @carikod ) and
((chk_Borc_uid = @rec and @hartip = 0 and (@srmkod = '' or @srmkod = (Select cha_srmrkkodu From CARI_HESAP_HAREKETLERI Where cha_Guid = chk_Alc_uid))) or
(chk_Alc_uid = @rec and @hartip = 1 and (@srmkod = '' or @srmkod = (Select cha_srmrkkodu From CARI_HESAP_HAREKETLERI Where cha_Guid = chk_Borc_uid))))) -- Karşı evrağın sorm.merkz. farklıysa kayıt gözardı ediliyor...

IF @kapanan > @meblag Set @kapanan = @meblag -- Hatalı kayıtlar gözardı ediliyor...

IF (@summeblag + (@meblag-isnull(@kapanan,0))) > @bakiye Set @meblag = @bakiye- @summeblag

Set @summeblag = @summeblag + (@meblag-isnull(@kapanan,0))

Set @summeblagvade = @summeblagvade + ((@meblag-isnull(@kapanan,0)) * @vade)

IF Round(@summeblag,0) >= Round(@bakiye,0)

Begin

Set @end = 1

Set @Xvadetarih = Case When @summeblagvade <> 0 Then @summeblagvade/@summeblag Else 0 end

Set @mintarih = @tarih

Set @summeblagvade = 0

Set @summeblag = (@meblag-isnull(@kapanan,0))

End

Else Begin

(Select top 1 @rec = cha_Guid, @tarih = cha_tarihi, @vadetarih = dbo.fn_OpVadeTarih(cha_vade,cha_tarihi)

From CARI_HESAP_HAREKETLERI Where

(cha_cari_cins = @caricins and cha_kod = @carikod and cha_tip = @hartip and cha_tpoz = 0 and ((cha_srmrkkodu=@srmkod) or (@srmkod=''))) and

((cha_tarihi < @tarih) or (cha_tarihi = @tarih and dbo.fn_OpVadeTarih(cha_vade,cha_tarihi) < @vadetarih) or

(cha_tarihi = @tarih and dbo.fn_OpVadeTarih(cha_vade,cha_tarihi) = @vadetarih and cha_Guid < @rec )))

Order By cha_tarihi desc,dbo.fn_OpVadeTarih(cha_vade,cha_tarihi) desc ,cha_Guid desc

End End

Begin

While @end = 1

Begin

IF Round(@summeblag,0) >= Round(@bakiye,0)

Begin

Set @end = 0 Set @Xevrvade = Case When @summeblagvade <> 0 Then @summeblagvade/@summeblag Else 0 End

End

Else Begin

(Select top 1 @rec = cha_Guid, @tarih = cha_tarihi, @vadetarih = dbo.fn_OpVadeTarih(cha_vade,cha_tarihi)

From CARI_HESAP_HAREKETLERI Where

(cha_cari_cins = @caricins and cha_kod = @carikod and cha_tip = @hartip and cha_tpoz = 0 and ((cha_srmrkkodu=@srmkod) or (@srmkod=''))) and

((cha_tarihi > @tarih) or (cha_tarihi = @tarih and dbo.fn_OpVadeTarih(cha_vade,cha_tarihi) > @vadetarih) or

(cha_tarihi = @tarih and dbo.fn_OpVadeTarih(cha_vade,cha_tarihi) = @vadetarih and cha_Guid > @rec )))

Order By cha_tarihi Asc ,dbo.fn_OpVadeTarih(cha_vade,cha_tarihi) Asc ,cha_Guid Asc

(Select @meblag = Case When (cha_cinsi in (13,29)) Then (cha_meblag- cha_vergi1 - cha_vergi2 - cha_vergi3 - cha_vergi4 - cha_vergi5 - cha_vergi6 - cha_vergi7 - cha_vergi8 - cha_vergi9 - cha_vergi10)

When (cha_cinsi= 33) Then cha_aratoplam WHEN ((cha_cinsi=11) or (cha_evrak_tip=59)) AND (dbo.fn_FirmaAnaDovizCinsi()<> cha_d_cins) THEN 0 Else cha_meblag End, @vade = dbo.fn_gunfarkibul(@mintarih,cha_tarihi) From CARI_HESAP_HAREKETLERI Where cha_Guid = @rec)

(Select @kapanan = Sum(chk_Tutar) From CARI_HAREKET_BORC_ALACAK_ESLEME Where
(chk_Chcinsi = @caricins and chk_ChKodu = @carikod ) and
((chk_Borc_uid = @rec and @hartip = 0 and (@srmkod = '' or @srmkod = (Select cha_srmrkkodu From CARI_HESAP_HAREKETLERI Where cha_Guid = chk_Alc_uid))) or
(chk_Alc_uid = @rec and @hartip = 1 and (@srmkod = '' or @srmkod = (Select cha_srmrkkodu From CARI_HESAP_HAREKETLERI Where cha_Guid = chk_Borc_uid)))))

IF @kapanan > @meblag Set @kapanan = @meblag -- Hatalı kayıtlar gözardı ediliyor...

Set @summeblag = @summeblag + (@meblag-isnull(@kapanan,0))

Set @summeblagvade = @summeblagvade + ((@meblag-isnull(@kapanan,0)) * @vade)

End End End

Return Case When @summeblag <> 0 Then @mintarih + Round((@Xvadetarih + @Xevrvade),0) End

End

GO

/****** Object:  UserDefinedFunction [dbo].[mbt_CariDonemSatisTutari]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION [dbo].[mbt_CariDonemSatisTutari]
( @Carikodu as nvarchar(25),
@Tarih  datetime
)
RETURNS float
AS
BEGIN
Declare @Gun as integer
Declare @IlkTarih  datetime
Declare @Miktar as float
SET @Gun = ISNULL(dbo.fn_AyKacGun(DATEPART(month,@Tarih),DATEPART(year,@Tarih)),30) * -1
SET @IlkTarih = dateadd(day,@Gun,@Tarih)
SELECT @Miktar= SUM(CASE
WHEN (sth_tip = 1) AND (sth_normal_iade=0) THEN dbo.fn_StokHareketBrutDeger ( sth_tutar,
0,
sth_har_doviz_kuru,
sth_alt_doviz_kuru,
sth_stok_doviz_kuru)
WHEN (sth_tip = 0) AND (sth_normal_iade=1) THEN -1 * dbo.fn_StokHareketBrutDeger ( sth_tutar,
0,
sth_har_doviz_kuru,
sth_alt_doviz_kuru,
sth_stok_doviz_kuru)
ELSE 0
END)
FROM dbo.STOK_HAREKETLERI WITH (NOLOCK, INDEX = NDX_STOK_HAREKETLERI_04)
WHERE (sth_cari_kodu = @Carikodu) AND
(sth_tarih >= @IlkTarih) and (sth_tarih <= @Tarih) AND
not (sth_cins in (9,15))
if @Miktar is Null set @Miktar=0
Return (@Miktar)
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_CariDonemSatisTutariYilAy]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION [dbo].[mbt_CariDonemSatisTutariYilAy]
( @Carikodu as nvarchar(25),
@Yil int,
@Ay int
)
RETURNS float
AS
BEGIN

Declare @Miktar as float

SELECT @Miktar= SUM(CASE
WHEN (sth_tip = 1) AND (sth_normal_iade=0) THEN dbo.fn_StokHareketBrutDeger ( sth_tutar,
0,
sth_har_doviz_kuru,
sth_alt_doviz_kuru,
sth_stok_doviz_kuru)
WHEN (sth_tip = 0) AND (sth_normal_iade=1) THEN -1 * dbo.fn_StokHareketBrutDeger ( sth_tutar,
0,
sth_har_doviz_kuru,
sth_alt_doviz_kuru,
sth_stok_doviz_kuru)
ELSE 0
END)
FROM dbo.STOK_HAREKETLERI WITH (NOLOCK, INDEX = NDX_STOK_HAREKETLERI_04)
WHERE (sth_cari_kodu = @Carikodu) AND
(YEAR(sth_tarih)= @Yil) and (MONTH(sth_tarih)= @Ay) AND
not (sth_cins in (9,15))
if @Miktar is Null set @Miktar=0
Return (@Miktar)
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_CariDonemSatisTutariYil]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION [dbo].[mbt_CariDonemSatisTutariYil]
( @Carikodu as nvarchar(25),
@Yil int
)
RETURNS float
AS
BEGIN

Declare @Miktar as float

SELECT @Miktar= SUM(CASE
WHEN (sth_tip = 1) AND (sth_normal_iade=0) THEN dbo.fn_StokHareketBrutDeger ( sth_tutar,
0,
sth_har_doviz_kuru,
sth_alt_doviz_kuru,
sth_stok_doviz_kuru)
WHEN (sth_tip = 0) AND (sth_normal_iade=1) THEN -1 * dbo.fn_StokHareketBrutDeger ( sth_tutar,
0,
sth_har_doviz_kuru,
sth_alt_doviz_kuru,
sth_stok_doviz_kuru)
ELSE 0
END)
FROM dbo.STOK_HAREKETLERI WITH (NOLOCK, INDEX = NDX_STOK_HAREKETLERI_04)
WHERE (sth_cari_kodu = @Carikodu) AND
(YEAR(sth_tarih)= @Yil) AND
not (sth_cins in (9,15))
if @Miktar is Null set @Miktar=0
Return (@Miktar)
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_StokAlisFiyati]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_StokAlisFiyati] (@StokKod as nvarchar(25), @MusteriKodu as nvarchar(25))
RETURNS float AS
BEGIN
Declare @StokFiyat as float
SELECT TOP 1 @StokFiyat=(sth_tutar/sth_miktar)
FROM dbo.STOK_HAREKETLERI
WHERE (sth_stok_kod=@StokKod) AND
(sth_cari_kodu=@MusteriKodu) AND (sth_evraktip=3 or sth_evraktip=13) AND sth_normal_iade=0
ORDER BY sth_tarih DESC-- son alınan 
-- EGER MUSTERIYE GORE YOKSA MUSTERISIZ BAKSIN
IF (@StokFiyat is NULL)
BEGIN
SELECT TOP 1 @StokFiyat=(sth_tutar/sth_miktar)
FROM dbo.STOK_HAREKETLERI
WHERE (sth_stok_kod=@StokKod) AND
(sth_evraktip=3 or sth_evraktip=13) AND sth_normal_iade=0
ORDER BY sth_tarih DESC
END

IF @StokFiyat is NULL SET @StokFiyat=0
Return @StokFiyat
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_adrestekiSeriliMiktar]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_adrestekiSeriliMiktar](@stokKodu nvarchar(50),@seriNumarasi nvarchar(50),@depoKodu int,@adresKodu nvarchar(50))
RETURNS float
AS
BEGIN
  DECLARE @VAL As float
  DECLARE @VALCIKIS As float
  DECLARE @VALGIRIS As float
         
         SELECT @VALCIKIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND SERINUMARASI=@seriNumarasi) and (GIRISCIKISTIPI='C') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         SELECT @VALGIRIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND SERINUMARASI=@seriNumarasi) and (GIRISCIKISTIPI='G') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         IF @VALGIRIS is NULL SET @VALGIRIS=0
         IF @VALCIKIS is NULL SET @VALCIKIS=0
         SET @VAL=(@VALGIRIS-@VALCIKIS)
        
  IF @VAL is NULL SET @VAL=0
  RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_adrestekiMiktar]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_adrestekiMiktar](@stokKodu nvarchar(50),@depoKodu int,@adresKodu nvarchar(50))
RETURNS float
AS
BEGIN
  DECLARE @VAL As float
  DECLARE @VALCIKIS As float
  DECLARE @VALGIRIS As float
         
         SELECT @VALCIKIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu) and (GIRISCIKISTIPI='C') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         SELECT @VALGIRIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu) and (GIRISCIKISTIPI='G') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         IF @VALGIRIS is NULL SET @VALGIRIS=0
         IF @VALCIKIS is NULL SET @VALCIKIS=0
         SET @VAL=(@VALGIRIS-@VALCIKIS)
        
  IF @VAL is NULL SET @VAL=0
  RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_adrestekiPartiliMiktar]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_adrestekiPartiliMiktar](@stokKodu nvarchar(50), @partiKodu nvarchar(50),@depoKodu int,@adresKodu nvarchar(50))
RETURNS float
AS
BEGIN
  DECLARE @VAL As float
  DECLARE @VALCIKIS As float
  DECLARE @VALGIRIS As float
         
         SELECT @VALCIKIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND PARTIKODU=@partiKodu) and (GIRISCIKISTIPI='C') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         SELECT @VALGIRIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND PARTIKODU=@partiKodu) and (GIRISCIKISTIPI='G') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         IF @VALGIRIS is NULL SET @VALGIRIS=0
         IF @VALCIKIS is NULL SET @VALCIKIS=0
         SET @VAL=(@VALGIRIS-@VALCIKIS)
        
  IF @VAL is NULL SET @VAL=0
  RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_adrestekiPartiLotMiktar]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_adrestekiPartiLotMiktar](@stokKodu nvarchar(50), @partiKodu nvarchar(50),@LotNo int,@depoKodu int,@adresKodu nvarchar(50))
RETURNS float
AS
BEGIN
  DECLARE @VAL As float
  DECLARE @VALCIKIS As float
  DECLARE @VALGIRIS As float
         
         SELECT @VALCIKIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND PARTIKODU=@partiKodu AND LOTNO=@LotNo) and (GIRISCIKISTIPI='C') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         SELECT @VALGIRIS=SUM(MIKTAR)  FROM  MBTADRESYERLESIM WITH (NOLOCK)  WHERE (STOKKODU = @stokKodu AND ADRESKODU=@adresKodu AND PARTIKODU=@partiKodu AND LOTNO=@LotNo) and (GIRISCIKISTIPI='G') AND (DEPONO=@depokodu)  AND EVRAKTIPI<>'SAYIM' AND AKTIF=1
         IF @VALGIRIS is NULL SET @VALGIRIS=0
         IF @VALCIKIS is NULL SET @VALCIKIS=0
         SET @VAL=(@VALGIRIS-@VALCIKIS)
        
  IF @VAL is NULL SET @VAL=0
  RETURN @VAL
END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_DepodakiMiktar]    Script Date: 26.04.2021 12:50:10 ******/
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

/****** Object:  UserDefinedFunction [dbo].[mbt_VerilmesiGerekenSiparisMiktariHesapla2]    Script Date: 26.04.2021 12:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mbt_VerilmesiGerekenSiparisMiktariHesapla2]
(@DonemSonu As Float,
@MinimumStokSeviyesi As Float,
@MaksimumStokSeviyesi As Float,
@KoliMiktari As int,
@MusterideBekleyenMiktar As Float,
@UreticideBekleyenMiktar As Float
)
RETURNS float
AS
BEGIN --ILK BEGIN

IF @KoliMiktari=0
BEGIN
	Set @KoliMiktari=1
END

DECLARE @VAL As FLOAT
DECLARE @BEKLEYENSIPARISFARKI AS FLOAT
DECLARE @EKSIKMIKTAR AS FLOAT
DECLARE @MODEKALAN AS INT

SET @BEKLEYENSIPARISFARKI =@UreticideBekleyenMiktar  - @MusterideBekleyenMiktar
SET @EKSIKMIKTAR= @DonemSonu + @BEKLEYENSIPARISFARKI
SET @VAL=0

DECLARE @VERILMESIGEREKENSIPARISFARKI AS FLOAT

--1.IF
IF @MinimumStokSeviyesi>0 
	IF @EKSIKMIKTAR<@MinimumStokSeviyesi
		BEGIN
			SET @VERILMESIGEREKENSIPARISFARKI = @MaksimumStokSeviyesi-@EKSIKMIKTAR
		END
	ELSE IF @EKSIKMIKTAR>=@MinimumStokSeviyesi AND @EKSIKMIKTAR<@MaksimumStokSeviyesi
		BEGIN
			SET @VERILMESIGEREKENSIPARISFARKI =0
		END
	ELSE
		BEGIN
			SET @VERILMESIGEREKENSIPARISFARKI =0
		END
ELSE --1.IF	
	BEGIN
		SET @VERILMESIGEREKENSIPARISFARKI=0
	END

IF @VERILMESIGEREKENSIPARISFARKI>0
	BEGIN
		SET @MODEKALAN = CAST(@VERILMESIGEREKENSIPARISFARKI AS INT) % CAST(@KoliMiktari AS INT)
		if @MODEKALAN>0 
		BEGIN
			SET @VAL = CAST(@VERILMESIGEREKENSIPARISFARKI/@KoliMiktari AS INT ) * @KoliMiktari
		END
		ELSE
		BEGIN
			SET @VAL = CAST(@VERILMESIGEREKENSIPARISFARKI/@KoliMiktari AS INT ) * @KoliMiktari
		END

	END
ELSE
BEGIN
	SET @VAL=0
END
IF @VAL is NULL SET @VAL=0
RETURN @VAL

END
GO

/****** Object:  UserDefinedFunction [dbo].[mbt_StokMaliyeti]    Script Date: 26.04.2021 12:50:10 ******/
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