--- CURSOR İLE PROC DEĞİŞTİ. KONTROL EDELİM . 2021-09-27

ALTER PROCEDURE [dbo].[sp_CARIEKSTRE]       
@MUSNO nvarchar(30), @ILKTARIH nvarchar(10),@SONTARIH nvarchar(10),           
 @KAPALIHAREKET nvarchar(1),@ORTVADESEC nvarchar(1),@ISLEMTIPINEGORE SMALLINT=0,@ISLEMTIPINEGOREBAKIYEGRUPLA SMALLINT=0       
 as         
BEGIN

declare @pIslemTipi nvarchar(250)
declare @pBAKIYE1 money
 
 
DECLARE @ACARI TABLE (ROWNUMBER INT,TARIH DATETIME, TESSIRNO INT, MUSNO INT, BAACIKLA nvarchar(14),ACIKLAMA nvarchar(60),          
  ISLEMNO INT,DOVIZTUT float,DOVIZKODU nvarchar(2),OZELKOD nvarchar(3),ISLEMTIPI nvarchar(30),          
  BELGENO nvarchar(10), ACIKLABAKIYE nvarchar(1), TP nvarchar(2), [Ort Vade] DATETIME,SaticiKodu nvarchar(15),          
  SaticiAdi nvarchar(25), KULNO INT,KULADI nvarchar(20), DovizBorc float,          
  DovizAlacak float,Aciklama2 nvarchar(30),          
  SiraNo INT, CARINO INT, MUSKOD nvarchar(20),MUSADI nvarchar(50), IsIstasyonKodu nvarchar(30),           
  IsIstasyonAdi nvarchar(30), TESLIM nvarchar(200),BORC money, ALACAK money,  FARK money,           
  BAKIYE money,eFaturaNo varchar(30),eFaturaDurumu varchar(20),eFaturaGonderimTarihi datetime)          
DECLARE @BAKIYE money, @IBKY MONEY,@BAKIYE2 money          
SELECT  @IBKY= SUM(BORC-ALACAK)  FROM ACARI WHERE MUSNO=@MUSNO AND TARIH<=@ILKTARIH          
SET @BAKIYE = 0          
IF @IBKY IS NULL          
     SET @IBKY=0  
	 
IF @ISLEMTIPINEGORE=1
BEGIN
  INSERT INTO @ACARI (ROWNUMBER,TARIH,MUSNO,FARK,BAKIYE,BAACIKLA,ACIKLAMA,ISLEMNO) VALUES (0,@ILKTARIH,@MUSNO,@IBKY,0,'Devir','Hesap Ekstresi Devir Tutarı',1);

INSERT INTO @ACARI
SELECT ROW_NUMBER() OVER(ORDER BY ISLEMTIPI ASC,TARIH ASC,CARINO ASC) AS ROWNUMBER1,TARIH,TESSIRNO,MUSNO,BAACIKLA, ISNULL(ACIKLAMA, '''') as ACIKLAMA,
  ISLEMNO,DOVIZTUT,DOVIZKODU,OZELKOD,ISLEMTIPI, 
  ISNULL(BELGENO, '''') as [BELGENO],ACIKLABAKIYE,TP,VadeTarihi as Ort_Vade,SaticiKodu,
  SaticiAdi, KULNO, KULADI, DovizBorc, 
  DovizAlacak,Aciklama2,
  SiraNo, CARINO, MUSKOD, MUSADI, IsIstasyonKodu, 
  IsIstasyonAdi,(select TeslimEden + ' - - ' + TeslimAlan from ACIKLAMA where FATURANO = ISLEMNO) as Teslim,
  BORC money, ALACAK money,(BORC-ALACAK), NULL, 
  (select top 1 A.eFaturaNo  from STOKHARDETBAG as A where A.IslemNo=ACARI.ISLEMNO) eFaturaNo,     
 (select top 1 A.eFaturaDurumu  from STOKHARDETBAG as A where A.IslemNo=ACARI.ISLEMNO) eFaturaDurumu, 
 (select top 1 A.eFaturaGonderimTarihi from STOKHARDETBAG as A where A.IslemNo=ACARI.ISLEMNO) eFaturaGonderimTarihi 
 FROM ACARI 
 WHERE MUSNO=@MUSNO AND TARIH >@ILKTARIH AND TARIH<@SONTARIH 
 and 1=   
 case  when @KAPALIHAREKET is not null then  case  when AcikKapali <> @KAPALIHAREKET then 1 else 0 end else 1 end  
 ORDER BY ROWNUMBER1 ASC 

 
 END 
 ELSE BEGIN
INSERT INTO @ACARI (ROWNUMBER,TARIH,MUSNO,FARK,BAKIYE,BAACIKLA,ACIKLAMA,ISLEMNO) VALUES (0,@ILKTARIH,@MUSNO,@IBKY,0,'Devir','Hesap Ekstresi Devir Tutarı',1)
 INSERT INTO @ACARI 
 SELECT ROW_NUMBER() OVER(ORDER BY  TARIH ASC,CARINO ASC) AS ROWNUMBER,TARIH,TESSIRNO,MUSNO,BAACIKLA, ISNULL(ACIKLAMA, '''') as ACIKLAMA,
  ISLEMNO,DOVIZTUT,DOVIZKODU,OZELKOD,ISLEMTIPI, 
  ISNULL(BELGENO, '''') as [BELGENO],ACIKLABAKIYE,TP,VadeTarihi as [Ort Vade],SaticiKodu,
  SaticiAdi, KULNO, KULADI, DovizBorc, 
  DovizAlacak,Aciklama2,
  SiraNo, CARINO, MUSKOD, MUSADI, IsIstasyonKodu, 
  IsIstasyonAdi,(select TeslimEden + ' - - ' + TeslimAlan from ACIKLAMA where FATURANO = ISLEMNO) as Teslim,
  BORC money, ALACAK money,(BORC-ALACAK), NULL, 
  (select top 1 A.eFaturaNo  from STOKHARDETBAG as A where A.IslemNo=ACARI.ISLEMNO) eFaturaNo,     
 (select top 1 A.eFaturaDurumu  from STOKHARDETBAG as A where A.IslemNo=ACARI.ISLEMNO) eFaturaDurumu, 
 (select top 1 A.eFaturaGonderimTarihi from STOKHARDETBAG as A where A.IslemNo=ACARI.ISLEMNO) eFaturaGonderimTarihi 
 FROM ACARI 
 WHERE MUSNO=@MUSNO AND TARIH >@ILKTARIH AND TARIH<@SONTARIH 
 and 1=   
 case  when @KAPALIHAREKET is not null then  case  when AcikKapali <> @KAPALIHAREKET then 1 else 0 end else 1 end  
 ORDER BY ROWNUMBER ASC 

 END

END  


 
   
        
declare @ROWNUMBER INT
declare @IslemTipi1 varchar(250)

declare AcariCursor cursor 
for Select ROWNUMBER,ISLEMTIPI FROM 	@ACARI ORDER BY ROWNUMBER ASC 
FOR UPDATE OF BAKIYE OPEN AcariCursor
FETCH NEXT FROM AcariCursor INTO @ROWNUMBER,@IslemTipi1
WHILE @@FETCH_STATUS=0
BEGIN 

IF @ISLEMTIPINEGORE=0 
BEGIN
 SET @BAKIYE2=(SELECT SUM(ISNULL(FARK,0)) FROM @ACARI  WHERE ROWNUMBER<=@ROWNUMBER )
 UPDATE TOP(1) @ACARI SET BAKIYE=@BAKIYE2 WHERE ROWNUMBER=@ROWNUMBER

end  else
begin

if @ROWNUMBER=0
 set @pIslemTipi=@IslemTipi1;

			if @pIslemTipi=@IslemTipi1
			begin
			 SET @BAKIYE2=(SELECT SUM(ISNULL(FARK,0)) FROM @ACARI  WHERE ROWNUMBER<=@ROWNUMBER and ISLEMTIPI=@pIslemTipi)
			 UPDATE TOP(1) @ACARI SET BAKIYE=@BAKIYE2 WHERE ROWNUMBER=@ROWNUMBER
			end
			else 
			begin
			 set @pIslemTipi=@IslemTipi1;
			 SET @BAKIYE2=(SELECT SUM(ISNULL(FARK,0)) FROM @ACARI  WHERE ROWNUMBER<=@ROWNUMBER and ISLEMTIPI=@pIslemTipi)
			 UPDATE TOP(1) @ACARI SET BAKIYE=@BAKIYE2 WHERE ROWNUMBER=@ROWNUMBER
			end
end

 FETCH NEXT FROM AcariCursor INTO @ROWNUMBER,@IslemTipi1
END
CLOSE AcariCursor 
DEALLOCATE AcariCursor

if @ISLEMTIPINEGOREBAKIYEGRUPLA=0
SELECT  * FROM @ACARI  ORDER BY ROWNUMBER
else
SELECT  ISLEMTIPI,sum(BORC) as BORC,sum(ALACAK) as ALACAK,sum(BAKIYE) as BAKIYE FROM @ACARI
where ISLEMTIPI is not null   GROUP BY ISLEMTIPI