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(200),                  
  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(200),                  
  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,OdemeBilgisi nvarchar(100))                  
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 Tutary',1);        
        
INSERT INTO @ACARI        
SELECT ROW_NUMBER() OVER(ORDER BY ISLEMTIPI ASC,TARIH ASC,CARINO ASC) AS ROWNUMBER1,TARIH,TESSIRNO,MUSNO,BAACIKLA,
(ISNULL(ACIKLAMA, '''')+ ' - - ' +ISNULL((select TOP 1 STDB.Ack1 FROM STOKHARDETBAG as STDB where STDB.IslemNo=ACARI.ISLEMNO),'')) 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,      
 (select top 1 OdemeBilgisi from STOKHARDETBAG where IslemNo=ACARI.ISLEMNO ) as OdemeBilgisi       
 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 Tutary',1)        
 INSERT INTO @ACARI         
 SELECT ROW_NUMBER() OVER(ORDER BY  TARIH,CARINO  ,CARINO ASC) AS ROWNUMBER,TARIH,TESSIRNO,MUSNO,BAACIKLA,
 
(ISNULL(ACIKLAMA, '''')+ ' - - ' +ISNULL((select TOP 1 STDB.Ack1 FROM STOKHARDETBAG as STDB where STDB.IslemNo=ACARI.ISLEMNO),'')) 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,      
 (select top 1 OdemeBilgisi from STOKHARDETBAG where IslemNo=ACARI.ISLEMNO ) as OdemeBilgisi       
 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(BORC-ALACAK) as BAKIYE FROM @ACARI        
where ISLEMTIPI is not null and MUSNO=@MUSNO  GROUP BY ISLEMTIPI 