if  not  exists (select * from dbo.syscolumns where id = object_id(N'STOKHARDETBAG') and name=N'EF_ISTISNA')
begin
ALTER TABLE STOKHARDETBAG ADD EF_ISTISNA INT
end

--[***]--
if  not  exists (select * from dbo.syscolumns where id = object_id(N'Buton') and name=N'StokNo')
begin
ALTER TABLE Buton ADD StokNo INT
end

--[***]--
if    exists (select * from dbo.syscolumns where id = object_id(N'ACIKLAMA') and name=N'ACIKLAMA1')
begin
alter table ACIKLAMA alter column ACIKLAMA1 nvarchar(200)
end

--[***]--
if    exists (select * from dbo.syscolumns where id = object_id(N'ACIKLAMA') and name=N'ACIKLAMA2')
begin
alter table ACIKLAMA alter column ACIKLAMA2 nvarchar(200)
end

--[***]--
if    exists (select * from dbo.syscolumns where id = object_id(N'ACIKLAMA') and name=N'ACIKLAMA3')
begin
alter table ACIKLAMA alter column ACIKLAMA3 nvarchar(200)
end

--[***]--
if    exists (select * from dbo.syscolumns where id = object_id(N'ACARI') and name=N'Aciklama2')
begin
alter table ACARI alter column Aciklama2 nvarchar(200)
end

--[***]--
if    exists (select * from dbo.syscolumns where id = object_id(N'AcariTakas') and name=N'Aciklama3')
begin
alter table AcariTakas alter column Aciklama3 nvarchar(200)
end

--[***]--
if    exists (select * from dbo.syscolumns where id = object_id(N'STOKHARDETBAG') and name=N'Ack1')
begin
alter table STOKHARDETBAG alter column Ack1 nvarchar(200)
end

--[***]--
if    exists (select * from dbo.syscolumns where id = object_id(N'STOKHARDETBAG') and name=N'Ack2')
begin
alter table STOKHARDETBAG alter column Ack2 nvarchar(200)
end

--[***]--
if    exists (select * from dbo.syscolumns where id = object_id(N'STOKHARDETBAG') and name=N'Ack3')
begin
alter table STOKHARDETBAG alter column Ack3 nvarchar(200)
end

--[***]--
if    exists (select * from dbo.syscolumns where id = object_id(N'STOKHARDETBAG') and name=N'Ack4')
begin
alter table STOKHARDETBAG alter column Ack4 nvarchar(200)
end

--[***]--
 
declare @CNTNAME NVARCHAR(100),@SQL NVARCHAR(MAX)
select @CNTNAME=name from sys.default_constraints where name like 'DF__STOKHARDE__Tevki_%'
SET @SQL='ALTER TABLE [dbo].[STOKHARDETBAG] DROP CONSTRAINT ['+ CONVERT(VARCHAR(100),@CNTNAME)+']'
EXEC(@SQL)

--[***]--

declare @CNTNAME NVARCHAR(100),@SQL NVARCHAR(MAX)
select @CNTNAME=name from sys.default_constraints where name like 'DF__STOKHARDE__Tevki_%'
SET @SQL='ALTER TABLE [dbo].[STOKHARDETBAG] DROP CONSTRAINT ['+ CONVERT(VARCHAR(100),@CNTNAME)+']'
EXEC(@SQL)

--[***]--
if exists (select * from sys.procedures where name='sp_FaturaIslemleriListesi')
drop proc sp_FaturaIslemleriListesi

--[***]--
 CREATE   PROCEDURE [dbo].[sp_FaturaIslemleriListesi]                                    
  @tarih1 varchar(20),                                     
  @tarih2 varchar(20),                                    
  @dep varchar(500),                                    
  @orderby int,                                    
  @arama varchar(100),                                    
  @sterilecekKayit int,                                    
  @IslemTuru varchar(20),                                    
  @Durumu int,                        
  @IslemTipi  varchar(30),            
  @FatSerNo  varchar(3),            
  @eFaturaNo  varchar(30),            
  @FaturaTuru varchar(1),
  @IslemNoLike bit = 0  
AS                                    
        
declare @str varchar(1000)                                    
declare @rc int                                     
declare @tmp table (trc int)                                    
declare @ayrac varchar(2)            
declare @peFaturaNo varchar(50)    
if @eFaturaNo<>''    
select @peFaturaNo=UPPER(@eFaturaNo)    
else set @peFaturaNo=''    
set @ayrac='"';            
      
set @str='select count(*) from (select IslemNo from STOKHARDETBAG where FisTuru = ''F'' AND FaturaTarihi >='''+ @Tarih1+                  
''' and FaturaTarihi <= '''+@Tarih2+'''';                                    
                               
if @dep <> ''                                    
 set @str=@str+' and Departman in ('+@Dep+')'                                    
if @IslemTuru <> 'Tümü'                                    
 set @str=@str+' and IslemTuru = '''+@IslemTuru+''''                          
if @IslemTipi <> 'Tümü' and   @IslemTipi <> ''                                   
 set @str=@str+' and IslemTipi like ''%'+@IslemTipi+'%'''                          

if @arama <> '' 
begin
if @orderby=0 
begin
 if @IslemNoLike = 0
	set @str=@str+'and CAST(IslemNo as nvarchar(20)) = '''+@arama+''' ' 
 else
	set @str=@str+'and IslemNo like ''%'+@arama+'%'' ' 
end;                                  
if @orderby=1                                     
 set @str=@str+'and upper(FaturaNo) like upper('''+@arama+'%'') '                                     
if @orderby=2                                     
 set @str=@str+'and upper(CariAdi) like upper(''%'+@arama+'%'') '                                    
if @orderby=3                                     
 set @str=@str+'and upper(CariKodu) like upper('''+@arama+'%'') '                                     
if @orderby=4                                    
 set @str=@str+'and upper(KULADI) like upper(''%'+@arama+'%'') '                                    
if @orderby=5                                    
 set @str=@str+'and upper(SaticiAdi) like upper(''%'+@arama+'%'') '                         
 if @orderby=6                                    
 set @str=@str+'and upper(eFaturaDurumu) like upper(''%'+@arama+'%'') '                         
if @orderby=7                                    
 set @str=@str+'and upper(YaziciCount) like upper(''%'+@arama+'%'') '                         
if @orderby=8                                    
 set @str=@str+'and upper(FaturaTarihi) like upper(''%'+@arama+'%'') '                         
end;                                
set @str=@str+')Y';                 
                                    
                        
insert @tmp exec(@str)                                    
select @rc = trc from @tmp;           
            
declare @tstr varchar(100);                                  
                                  
if @orderby=0                                    
  set @tstr='IslemNo'                                    
if @orderby=1                                     
 set @tstr='FaturaNo'                                     
if @orderby=2                                     
 set @tstr='CariAdi'                                    
if @orderby=3                                     
 set @tstr='CariKodu'                                     
if @orderby=4                                    
 set @tstr='KULADI'                                    
if @orderby=5                                    
 set @tstr='SaticiAdi'                          
if @orderby=6                                    
 set @tstr='eFaturaDurumu'             
 if @orderby=7                                    
 set @tstr='YaziciCount'             
 if @orderby=8                                    
 set @tstr='FaturaTarihi'             
             
set @str='select '''' as plaka, *, '+                
   'CASE YazarKasaFisNo when 0 then ''TICARIFATURA'' '+                
   'when 1 then ''TEMELFATURA'' '+                
   'when 2 then ''IHRACAT'' else ''TICARIFATURA'' end + '' / ''+ case FaturaTipi  when 0 then ''SATIS'' when 1 then ''TEVKIFAT'' when 2 then ''IADE'' when 3 then ''IHRACKAYITLI'' when 4 then ''ISTISNA'' '+                 
   'when 5 then ''MOBIL'' else ''SATIS'' end FaturaSenaryoTip '+                
   'from STOKHARDETBAG  '+                                    
   'where IslemNo in (select X.IslemNo '+                                    
   'from (select ROW_NUMBER() over (order by '+@tstr+') as sira, IslemNo '+                                    
   'from STOKHARDETBAG where FisTuru = '''+@FaturaTuru+''' and FaturaTarihi >='''+@Tarih1+''' and FaturaTarihi <= '''+@Tarih2+'''';                                    
if @peFaturaNo<>''            
set @str=@str+'and upper(eFaturaNo) like upper(''%'+@peFaturaNo+'%'') '            
            
if @FatSerNo<>''            
set @str=@str+'and Replace(FatSerNo,'''''''','''+@ayrac+''') = '''+@FatSerNo+''' '            
            
if @dep <> ''                                    
 set @str=@str+' and Departman in ('+@Dep+')'                                    
if @IslemTuru <> 'Tümü'                                    
 set @str=@str+' and IslemTuru = '''+@IslemTuru+''''                            
if @IslemTipi <> 'Tümü' and @IslemTipi <> ''                                    
 set @str=@str+' and IslemTipi = '''+@IslemTipi+''''                       

if @arama <> '' 
begin                 
if @orderby=0 
begin
 if @IslemNoLike = 0
	set @str=@str+'and CAST(IslemNo as nvarchar(20)) = '''+@arama+''' ' 
 else
	set @str=@str+'and IslemNo like ''%'+@arama+'%'' ' 
end;                                    
if @orderby=1                                     
 set @str=@str+'and upper(FaturaNo) like upper('''+@arama+'%'') '                                     
if @orderby=2                                     
 set @str=@str+'and upper(CariAdi) like upper(''%'+@arama+'%'') '                                    
if @orderby=3                                     
 set @str=@str+'and upper(CariKodu) like upper('''+@arama+'%'') '                                     
if @orderby=4                                    
 set @str=@str+'and upper(KULADI) like upper(''%'+@arama+'%'') '                                    
if @orderby=5                                    
 set @str=@str+'and upper(SaticiAdi) like upper(''%'+@arama+'%'') '                         
 if @orderby=6                                    
 set @str=@str+'and upper(eFaturaDurumu) like upper(''%'+@arama+'%'') '                         
if @orderby=7                                    
 set @str=@str+'and upper(YaziciCount) like upper(''%'+@arama+'%'') '                         
if @orderby=8                                    
 set @str=@str+'and upper(FaturaTarihi) like upper(''%'+@arama+'%'') '                  
end;                        
               
set @str=@str+')X where X.sira >'+convert(varchar,@rc - @sterilecekKayit) +') order by ';                                    
                                    
if @orderby=0                                    
  set @str=@str+'IslemNo'                                    
if @orderby=1                                     
 set @str=@str+'FaturaNo'                                     
if @orderby=2                                     
 set @str=@str+'CariAdi'                                    
if @orderby=3                                     
 set @str=@str+'CariKodu'                      
if @orderby=4                                    
 set @str=@str+'KULADI'                                    
if @orderby=5                                    
 set @str=@str+'SaticiAdi'                                    
if @orderby=6                                    
 set @str=@str+'eFaturaDurumu'               
if @orderby=7                                 
 set @str=@str+'YaziciCount'               
if @orderby=8                                    
 set @str=@str+'FaturaTarihi'             
                 
         
exec(@str) 

--[***]--
if exists (select * from sys.procedures where name='sp_KasaIslemleriListesi')
drop proc sp_KasaIslemleriListesi

--[***]--
create   PROCEDURE [dbo].[sp_KasaIslemleriListesi]    
  @tarih1 varchar(20),     
  @tarih2 varchar(20),    
  @dep varchar(5000),    
  @orderby int,    
  @arama varchar(100),    
  @sterilecekKayit int,    
  @IslemTuru varchar(15),    
  @KasaTuru varchar(1),    
  @KasaKodu varchar(10),
  @IslemNoLike bit = 0  with encryption   
AS  
declare @str nvarchar(max)    
declare @rc int     
declare @tmp table (trc int)    
    
if @IslemTuru <> 'Tümü'    
begin    
 if @IslemTuru = 'Senet Tah.'    
  set @str='select count(*) from (select ISLEMNO from KASA where TARIH >='''+ @Tarih1+''' and TARIH <= '''+@Tarih2+'''';    
 else    
  set @str='select count(*) from (select ISLEMNO from KASA where TARIH >='''+ @Tarih1+''' and TARIH <= '''+@Tarih2+'''';    
end     
else begin    
 set @str='select count(*) from (select ISLEMNO from KASA where TARIH >='''+ @Tarih1+''' and TARIH <= '''+@Tarih2+'''';    
end    
   
if @KasaTuru = 'N'    
 set @str = @str + 'and NakitVisa = ''N''';    
else if @KasaTuru = 'V'    
 set @str = @str + 'and NakitVisa = ''V''';    
else if @KasaTuru = 'a'    
 set @str = @str + 'and NakitVisa <> ''a''';    
    
if @KasaKodu <> ''    
 set @str = @str + 'AND KASAKODU = '''+@KasaKodu+'''';    
    
if @dep <> ''    
 set @str=@str+' AND Departman in ('+@Dep+')'    
    
if @IslemTuru <> 'Tümü'    
BEGIN    
 IF @IslemTuru = 'Senet Tah.'    
  set @str = @str + ' AND (BAACIKLA = '''+@IslemTuru+''') OR (BAACIKLA = ''Senet Tahsil'')'    
 ELSE    
  set @str = @str + ' AND BAACIKLA = '''+@IslemTuru+'''';    
END    
ELSE    
 set @str = @str + ' AND (BAACIKLA <> ''Açılış'') AND (BAACIKLA <> ''Pos Çıkış'') ';    
    
if @arama <> '' 
begin      
	if @orderby=0 
	begin
		if @IslemNoLike = 0
			set @str=@str+'and CAST(ISLEMNO as nvarchar(20)) = '''+@arama+''' ' 
		else
			set @str=@str+'and ISLEMNO like ''%'+@arama+'%'' ' 
	end;  
	if @orderby=1     
	 set @str=@str+'and upper(MUSADI) like upper(''%'+@arama+'%'') '     
	if @orderby=2     
	 set @str=@str+'and upper(MUSKOD) like upper('''+@arama+'%'') '    
	if @orderby=3     
	 set @str=@str+'and upper(IsIstasyonKodu) like upper(''%'+@arama+'%'') '     
	if @orderby=4    
	 set @str=@str+'and upper(IsIstasyonAdi) like upper(''%'+@arama+'%'') '    
	if @orderby=5    
	 set @str=@str+'and upper(KASAKODU) like upper('''+@arama+'%'') '    
	if @orderby=6    
	 set @str=@str+'and upper(KASAADI) like upper('''+@arama+'%'') '    
	if @orderby=7    
	 set @str=@str+'and upper(BelgeNo) like upper('''+@arama+'%'') '    
 end;
set @str=@str+')Y';    
   
insert @tmp exec(@str)    
select @rc = trc from @tmp;    
  
declare @tstr varchar(100);  
  
if @orderby=0    
  set @tstr='ISLEMNO'    
if @orderby=1     
 set @tstr='MUSADI'     
if @orderby=2     
 set @tstr='MUSKOD'    
if @orderby=3     
 set @tstr='IsIstasyonKodu'     
if @orderby=4    
 set @tstr='IsIstasyonAdi'    
if @orderby=5    
 set @tstr='KASAKODU'    
if @orderby=6    
 set @tstr='KASAADI'    
if @orderby=7    
 set @tstr='BelgeNo'   
  
    
set @str='select * from KASA  '+    
   'where ISLEMNO in (select X.ISLEMNO '+    
   'from (select ROW_NUMBER() over (order by '+@tstr+') as sira, ISLEMNO '+    
   'from KASA where TARIH >='''+@Tarih1+''' and TARIH <= '''+@Tarih2+'''';    
    
if @KasaTuru = 'N'    
 set @str = @str + 'and NakitVisa = ''N''';    
else if @KasaTuru = 'V'    
 set @str = @str + 'and NakitVisa = ''V''';    
else if @KasaTuru = 'a'    
 set @str = @str + 'and NakitVisa <> ''a''';    
    
if @KasaKodu <> ''    
 set @str = @str + 'and KASAKODU = '''+@KasaKodu+'''';    
    
if @dep <> ''    
 set @str=@str+' and Departman in ('+@Dep+')'    
if @IslemTuru <> 'Tümü'    
BEGIN    
 IF @IslemTuru = 'Senet Tah.'    
  set @str = @str + ' AND (BAACIKLA = '''+@IslemTuru+''') OR (BAACIKLA = ''Senet Tahsil'')'    
 ELSE    
  set @str = @str + ' AND BAACIKLA = '''+@IslemTuru+'''';    
END    
ELSE    
 set @str = @str + ' AND ((BAACIKLA <> ''Açılış'') AND (BAACIKLA <> ''Pos Çıkış'')) ';    
if @arama <> '' 
begin      
	if @orderby=0 
	begin
		if @IslemNoLike = 0
			set @str=@str+'and CAST(ISLEMNO as nvarchar(20)) = '''+@arama+''' ' 
		else
			set @str=@str+'and ISLEMNO like ''%'+@arama+'%'' ' 
	end;   
	if @orderby=1     
	 set @str=@str+'and upper(MUSADI) like upper(''%'+@arama+'%'') '     
	if @orderby=2     
	 set @str=@str+'and upper(MUSKOD) like upper('''+@arama+'%'') '    
	if @orderby=3     
	 set @str=@str+'and upper(IsIstasyonKodu) like upper(''%'+@arama+'%'') '     
	if @orderby=4    
	 set @str=@str+'and upper(IsIstasyonAdi) like upper(''%'+@arama+'%'') '    
	if @orderby=5    
	 set @str=@str+'and upper(KASAKODU) like upper('''+@arama+'%'') '    
	if @orderby=6    
	 set @str=@str+'and upper(KASAADI) like upper('''+@arama+'%'') '    
	if @orderby=7    
	 set @str=@str+'and upper(BelgeNo) like upper('''+@arama+'%'') '    
end;    
set @str=@str+')X where X.sira >'+convert(varchar,@rc - @sterilecekKayit) +') order by ';    
    
if @orderby=0    
  set @str=@str+'ISLEMNO'    
if @orderby=1     
 set @str=@str+'MUSADI'     
if @orderby=2     
 set @str=@str+'MUSKOD'    
if @orderby=3     
 set @str=@str+'IsIstasyonKodu'     
if @orderby=4    
 set @str=@str+'IsIstasyonAdi'    
if @orderby=5    
 set @str=@str+'KASAKODU'    
if @orderby=6    
 set @str=@str+'KASAADI'    
if @orderby=7    
 set @str=@str+'BelgeNo'    

exec(@str)    

--[***]--
if exists (select * from sys.procedures where name='sp_GELGIDH')
drop proc sp_GELGIDH

--[***]--
CREATE  proc [dbo].[sp_GELGIDH] 
(@tarih1 varchar(20),
@tarih2 varchar(20), 
@dep varchar(500), 
@orderby int, 
@arama varchar(100),
@sterilecekKayit int,
@IslemTuru varchar(5),
@KarsiHesap varchar(50),
@IslemNoLike bit = 0
)
as      
begin   
      
declare @str varchar(1000)      
declare @rc int       
declare @tmp table (trc int)      
      
set @str='select count(*) from (select ISLEMNO from GELGID where TARIH >='''+ @Tarih1+''' and TARIH <= '''+@Tarih2+'''';      
      
if @dep <> ''      
 set @str=@str+' and Departman in ('+@Dep+')'      
if @IslemTuru <> 'Tümü'      
 set @str=@str+' and BAACIKLA = '''+@IslemTuru+''''      
if @KarsiHesap <> 'Tümü'      
 set @str=@str+' and KarsiHesp = '''+@KarsiHesap+''''    

if @arama <> '' 
begin      
if @orderby=0 
begin
	 if @IslemNoLike = 0
		set @str=@str+'and CAST(ISLEMNO as nvarchar(20)) = '''+@arama+''' ' 
	 else
		set @str=@str+'and ISLEMNO like ''%'+@arama+'%'' ' 
	end;

	if @orderby=1       
	 set @str=@str+'and upper(GELGIDKODU) like upper('''+@arama+'%'') '       
	if @orderby=2       
	 set @str=@str+'and upper(GELGIDADI) like upper(''%'+@arama+'%'') '      
	if @orderby=3       
	 set @str=@str+'and upper(ACIKLAMA) like upper(''%'+@arama+'%'') '       
  
end;

set @str=@str+')Y';      
      
 
insert @tmp exec(@str)      
select @rc = trc from @tmp;       
    
      
set @str='select * from GELGID '+      
   'where ISLEMNO in (select X.ISLEMNO '+      
   'from (select ROW_NUMBER() over (order by ISLEMNO) as sira, ISLEMNO '+      
   'from GELGID where TARIH >='''+@Tarih1+''' and TARIH <= '''+@Tarih2+'''';      
if @dep <> ''      
 set @str=@str+' and Departman in ('+@Dep+')'            
if @IslemTuru <> 'Tümü'      
 set @str=@str+' and BAACIKLA = '''+@IslemTuru+''''  
if @KarsiHesap <> 'Tümü'      
 set @str=@str+' and KarsiHesp = '''+@KarsiHesap+'''' 
 
if @arama <> '' 
begin      
if @orderby=0 
begin
	 if @IslemNoLike = 0
		set @str=@str+'and CAST(ISLEMNO as nvarchar(20)) = '''+@arama+''' ' 
	 else
		set @str=@str+'and ISLEMNO like ''%'+@arama+'%'' ' 
	end;      
if @orderby=1       
 set @str=@str+'and upper(GELGIDKODU) like upper('''+@arama+'%'') '       
if @orderby=2       
 set @str=@str+'and upper(GELGIDADI) like upper(''%'+@arama+'%'') '      
if @orderby=3       
 set @str=@str+'and upper(ACIKLAMA) like upper(''%'+@arama+'%'') '       
     
end;

set @str=@str+')X where X.sira>'+convert(varchar,@rc - @sterilecekKayit) +') order by ';      
        
if @orderby=0      
  set @str=@str+'ISLEMNO'      
if @orderby=1        
  set @str=@str+'GELGIDKODU'      
if @orderby=2      
  set @str=@str+'GELGIDADI'      
if @orderby=3      
  set @str=@str+'ACIKLAMA'      

if @orderby=4      
  set @str=@str+'TARIH'      
      
      
exec(@str)      
end;

--[***]--
if exists (select * from sys.procedures where name='sp_BankaIslemListesi')
drop proc sp_BankaIslemListesi

--[***]--
create  PROCEDURE sp_BankaIslemListesi           
 @Tarih1 varchar(30),          
 @Tarih2 varchar(30),          
 @Dep1 varchar(500),          
 @Dep2 varchar(500),          
 @OrderBy int,          
 @Arama varchar(100),          
 @IslemTuru varchar(25),            
 @sterilecekKayit int,
 @IslemNoLike bit = 0 
AS          
BEGIN          
 DECLARE @SQLSTR VARCHAR(2000)          
 declare @rc int             
 declare @tmp table (trc int)           
          
 SET @SQLSTR = 'SELECT count(*) from (SELECT ISLEMNO FROM BANKA WHERE TARIH >='''+ @Tarih1+''' AND TARIH <= '''+@Tarih2+'''';            
          
       
 SET @SQLSTR = @SQLSTR + ' AND (Departman >= '+@dep1+' AND Departman <= '+@dep2+') ';          
        
 IF @IslemTuru <> 'Tümü'           
  SET @SQLSTR = @SQLSTR + ' AND BAACIKLA = '''+@IslemTuru+'''';          
  
if @arama <> '' 
begin      
	if @orderby=0 
	begin
	 if @IslemNoLike = 0
		set @SQLSTR=@SQLSTR+'and CAST(ISLEMNO as nvarchar(20)) = '''+@arama+''' ' 
	 else
		set @SQLSTR=@SQLSTR+'and ISLEMNO like ''%'+@arama+'%'' ' 
	end;
         
	 IF @orderby = 1          
	  SET @SQLSTR = @SQLSTR + ' AND UPPER(BANKADI) LIKE UPPER(''%'+@arama+'%'') ';          
	 IF @orderby = 2          
	  SET @SQLSTR = @SQLSTR + ' AND UPPER(HESAPNO) LIKE UPPER(''%'+@arama+'%'') ';          
	 IF @orderby = 3          
	  SET @SQLSTR = @SQLSTR + ' AND UPPER(ACIKLAMA) LIKE UPPER(''%'+@arama+'%'') ';          
	 IF @orderby = 4          
	  SET @SQLSTR = @SQLSTR + ' AND UPPER(TARIH) LIKE UPPER(''%'+@arama+'%'') ';   
end;
  
 SET @SQLSTR = @SQLSTR + ')Y';           
          
  INSERT @tmp exec(@SQLSTR)            
 select @rc = trc from @tmp;          
          
 SET @SQLSTR = 'SELECT * FROM BANKA' +          
      ' WHERE ISLEMNO IN (SELECT X.ISLEMNO FROM (SELECT ROW_NUMBER() OVER (ORDER BY TARIH) AS SIRA,ISLEMNO '+          
      ' FROM BANKA WHERE TARIH >='''+@Tarih1+''' and TARIH <= '''+@Tarih2+'''';            
          
       
 SET @SQLSTR = @SQLSTR + ' AND (Departman >= '+@dep1+' AND Departman <= '+@dep2+') ';          
          
 IF @IslemTuru <> 'Tümü'           
  SET @SQLSTR = @SQLSTR + ' and BAACIKLA = '''+@IslemTuru+'''';          
          
if @arama <> '' 
begin      
	if @orderby=0 
	begin
	 if @IslemNoLike = 0
		set @SQLSTR=@SQLSTR+'and CAST(ISLEMNO as nvarchar(20)) = '''+@arama+''' ' 
	 else
		set @SQLSTR=@SQLSTR+'and ISLEMNO like ''%'+@arama+'%'' ' 
	end; 
	
	 IF @orderby = 1          
	  SET @SQLSTR = @SQLSTR + ' AND UPPER(BANKADI) LIKE UPPER(''%'+@arama+'%'') ';          
	 IF @orderby = 2          
	  SET @SQLSTR = @SQLSTR + ' AND UPPER(HESAPNO) LIKE UPPER(''%'+@arama+'%'') ';          
	 IF @orderby = 3          
	  SET @SQLSTR = @SQLSTR + ' AND UPPER(ACIKLAMA) LIKE UPPER(''%'+@arama+'%'') ';          
	 IF @orderby = 4          
	  SET @SQLSTR = @SQLSTR + ' AND UPPER(TARIH) LIKE UPPER(''%'+@arama+'%'') ';  
end;  
 SET @SQLSTR = @SQLSTR + ')X WHERE X.SIRA>'+convert(varchar,@rc - @sterilecekKayit) +') ';            
          
SET @SQLSTR = @SQLSTR + ' and BORC+ALACAK > 0.1 ';      
SET @SQLSTR = @SQLSTR + ' and TARIH >='''+@Tarih1+''' and TARIH <= '''+@Tarih2+'''';     
    
SET @SQLSTR = @SQLSTR + 'ORDER BY ';    
    
    
 IF @orderby = 0          
  SET @SQLSTR = @SQLSTR + ' ISLEMNO';          
 IF @orderby = 1          
  SET @SQLSTR = @SQLSTR + ' BANKADI';          
 IF @orderby = 2          
  SET @SQLSTR = @SQLSTR + ' HESAPNO';          
 IF @orderby = 3          
  SET @SQLSTR = @SQLSTR + ' ACIKLAMA';          
 IF @orderby = 4          
  SET @SQLSTR = @SQLSTR + ' TARIH';          
           
 EXEC(@SQLSTR)          
          
END; 

--[***]--
  declare @CNTNAME NVARCHAR(1000),@SQL NVARCHAR(MAX)
select @CNTNAME= name  FROM sys.objects where type_desc like '%FUNCT%' and name like '%fnBankaPosBilgisiGetir%'
SET @SQL='DROP FUNCTION [dbo].['+CONVERT(VARCHAR(1000),@CNTNAME)+']'
EXEC(@SQL)

--[***]--
CREATE function [dbo].[fnBankaPosBilgisiGetir](@Bankasi varchar(150),  
@HesapNo varchar(50), @Gun Float, @KomisyonTutari Float, @KasaPos varchar(100), @Subesi  varchar(100),  
@IslemTuru int)  
returns varchar(500)  
as begin  
declare @Poslar varchar(500)  
declare @posSecilen varchar(100)  
set @Poslar=''  
declare posliste cursor for SELECT case @IslemTuru when 1 then ( KASAKODU +' '+  KASAADI) when 2 then KASAKODU end  
    FROM KASAT as a where a.KasaPos='V' and  
     ( a.Bankasi= @Bankasi and a.HesapNo= @HesapNo and  a.Gun= @Gun and  a.KomisyonTutari= @KomisyonTutari  
  and a.KasaPos= @KasaPos and a.Subesi= @Subesi)  
open posliste  
fetch next from posliste into @posSecilen  
WHILE @@FETCH_STATUS = 0   
BEGIN  
  if @IslemTuru=1  
   begin  
   if @Poslar=''   
       SET @Poslar = @posSecilen   
   else  
    SET @Poslar = @Poslar+ ',' + @posSecilen   
  end else begin  
   if @Poslar=''   
       SET @Poslar =''''+ @posSecilen+''''   
   else  
    SET @Poslar = @Poslar + ','''+ @posSecilen+''''  
  end  
  
  FETCH NEXT FROM posliste INTO @posSecilen  
END  
  
CLOSE posliste  
  
DEALLOCATE posliste  
   
RETURN @Poslar  
end

 




--[***]--
if  exists (select * from dbo.syscolumns where id = object_id(N'STOKHAR') and name=N'TevkifatAdi')
begin
alter table STOKHAR DROP COLUMN TevkifatAdi
end

--[***]--
if  exists (select * from dbo.syscolumns where id = object_id(N'STOKHAR') and name=N'TevkifatKod')
begin
alter table STOKHAR DROP COLUMN TevkifatKod
end

--[***]--
if  exists (select * from dbo.syscolumns where id = object_id(N'STOKHAR') and name=N'TevkifatOrani')
begin
alter table STOKHAR DROP COLUMN TevkifatOrani
end

--[***]--
if  exists (select * from dbo.syscolumns where id = object_id(N'STOKHAR') and name=N'TevkifatTutari')
begin
alter table STOKHAR DROP COLUMN TevkifatTutari
end

--[***]--
if  exists (select * from dbo.syscolumns where id = object_id(N'STOKHARDETBAG') and name=N'TevkifatAdi')
begin
alter table STOKHARDETBAG DROP COLUMN TevkifatAdi
end

--[***]--
if  exists (select * from dbo.syscolumns where id = object_id(N'STOKHARDETBAG') and name=N'TevkifatKod')
begin
alter table STOKHARDETBAG DROP COLUMN TevkifatKod
end

--[***]--
if  exists (select * from dbo.syscolumns where id = object_id(N'STOKHARDETBAG') and name=N'TevkifatOrani')
begin
alter table STOKHARDETBAG DROP COLUMN TevkifatOrani
end

--[***]--
if  not  exists (select * from dbo.syscolumns where id = object_id(N'tblTevkifat') and name=N'Oran')
begin
ALTER TABLE tblTevkifat ADD Oran Float
end

--[***]--
 IF NOT EXISTS (SELECT *  FROM sys.tables WHERE  name='Tevkifat')
 BEGIN 
create Table Tevkifat (
  NoId int identity(1,1),
  IslemNo int not null,
  StokNo int not null,
  Kod nvarchar(50) not null,
  TevkifatOrani decimal(18,2) not null,
  TevkifatTutari float not null,
  constraint PK_Tevkifat primary key (NoId)
)
end

--[***]--
UPDATE STOKHAR SET DovizKuru = (CASE WHEN DovizKodu in ('YP', 'TL') or SonTutar = 0 or SonTutarDov = 0 THEN 1 ELSE SonTutar / SonTutarDov END) 
UPDATE STOKHARDETBAG SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or Tutar = 0 or DovizTut = 0 THEN 1 ELSE Tutar / DovizTut END)
UPDATE ACARI SET DovizKur = 1 where DOVIZKODU in ('YP', 'TL')
UPDATE KASA SET DovizKur = 1 where DovizKodu in ('YP', 'TL')
UPDATE ACARI SET DovizKur = (CASE WHEN BORC >0 AND DovizBorc > 0   THEN BORC / DovizBorc    WHEN ALACAK > 0 and DovizAlacak > 0 THEN ALACAK / DovizAlacak  ELSE 1 END)
UPDATE TEKLIFHAR SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or SonTutar = 0 or SonTutarDov = 0 THEN 1 ELSE SonTutar / SonTutarDov END) 
UPDATE TEKLIFHARDETBAG SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or Tutar = 0 or DovizTut = 0 THEN 1 ELSE Tutar / DovizTut END)
UPDATE SIPARISHAR SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or SonTutar = 0 or SonTutarDov = 0 THEN 1 ELSE SonTutar / SonTutarDov END) 
UPDATE SIPARISHARDETBAG SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or Tutar = 0 or DovizTut = 0 THEN 1 ELSE Tutar / DovizTut END)
UPDATE KASA SET DovizKur = (CASE WHEN BORC > 0 and DovizBorc > 0   THEN BORC / DovizBorc    WHEN ALACAK > 0 and DovizAlacak > 0 THEN ALACAK / DovizAlacak  ELSE 1 END)

--[***]--
if exists (select * from sys.procedures where name='spTahsilVadeRaporu')
drop proc spTahsilVadeRaporu

--[***]--

create  proc spTahsilVadeRaporu @CariNo int 
as
create table #odeme
(
  RecID integer Identity (1, 1) not null, NoId integer, IslemNo integer, Tarih datetime,
  CariNo integer,  IslemTuru nvarchar(20),  Bakiye float,  Borc_Kullanilan float ,  Devreden float,  Borc float,
  Devir float  PRIMARY KEY CLUSTERED  (RecID)
)


Declare @DagitimTutari float
set @DagitimTutari = (select SUM (BORC-ALACAK) from ACARI WHERE MUSNO=@CariNo)

Declare  @NoId integer,  @IslemNo integer,  @Tarih datetime,  @Borc decimal (28, 14),  
		 @Borc_Kullanilan decimal (28, 14),  @Son_Kalan_Tutar float,  @IslemTuru nvarchar(20), @TmpTutar decimal(28, 14)

set @TmpTutar = @DagitimTutari

declare cr_Prepare cursor FAST_FORWARD READ_ONLY for
select   
  odm.NoId,
  odm.IslemNo,
  odm.Tarih,
  odm.CariNo,
  odm.Borc,
  odm.BAAcikla
from Odeme odm
where
   odm.CariNo = @CariNo and
   IsNull (odm.Borc, 0) > 0
order by
  odm.Tarih desc,NoId desc

open cr_Prepare
fetch next from cr_Prepare
into
  @NoId,
  @IslemNo,
  @Tarih,
  @CariNo,
  @Borc,
  @IslemTuru

while (@@FETCH_STATUS = 0) and (@TmpTutar > 0.0)
begin
  if @Borc >= @TmpTutar 
  begin
    set @Borc_Kullanilan = @TmpTutar
	set @Son_Kalan_Tutar = @TmpTutar - @Borc
	set @TmpTutar = 0.0
  end
  else
  begin
    set @TmpTutar = @TmpTutar - @Borc
	set @Borc_Kullanilan = @Borc
	set @Son_Kalan_Tutar = @TmpTutar
  end

  insert into #odeme 
  (
    NoId,
    IslemNo,
    Tarih,
    CariNo,
    Borc,
	Borc_Kullanilan,
	Devreden,
	Bakiye,
	Devir,
	IslemTuru
  )
  values
  (
	@NoId, 
	@IslemNo,
    @Tarih,
    @CariNo,
    @Borc,
	@Borc_Kullanilan,
	@Son_Kalan_Tutar,
	@DagitimTutari,
	@Son_Kalan_Tutar+@Borc,
	@IslemTuru
  )

  fetch next from cr_Prepare
  into
    @NoId,
    @IslemNo,
    @Tarih,
    @CariNo,
    @Borc,
	@IslemTuru
end
  
close cr_Prepare
deallocate cr_Prepare

 
update AMUSTERI SET Kilitli='Kilitli' WHERE MUSNO IN (
select  distinct o.CariNo  from #odeme o
where o.Devir > (select Deger from Parametre  where Param = 'Vadesi Geçen Toplam')
      and CAST(GETDATE() - o.Tarih as int) > (select Deger from Parametre  where Param = 'Vadesi Geçen Kilitlenecek Gün') 
 ) AND Kilitli='Açık'
 
 update AMUSTERI SET  Kilitli='Açık' WHERE MUSNO NOT IN (
select  distinct o.CariNo  from #odeme o
where o.Devir > (select Deger from Parametre  where Param = 'Vadesi Geçen Toplam')
      and CAST(GETDATE() - o.Tarih as int) > (select Deger from Parametre  where Param = 'Vadesi Geçen Kilitlenecek Gün') 
) AND Kilitli='Kilitli'
	  SELECT DISTINCT Kilitli,MUSNO  FROM AMUSTERI WHERE MUSNO=@CariNo

select  o.*,
		CAST(GETDATE() - o.Tarih as int) GecikenGun,
        a.MUSKOD CariKodu,
		a.MUSADI CariAdi,
		d.FaturaNo
 from #odeme o
 inner join  AMUSTERI a on  o.CariNo = a.MUSNO
 left join   STOKHARDETBAG d on o.IslemNo = d.IslemNo
 
 --[***]--
 IF  EXISTS (SELECT *  FROM sys.views WHERE  name='VRUT')
 BEGIN 
 drop view VRUT
 end
 
  --[***]--
  CREATE   VIEW [dbo].[VRUT]  
AS  
SELECT        dbo.Rut.NoId, dbo.Rut.CariKodu,dbo.AMUSTERI.MUSNO CariNo ,dbo.AMUSTERI.MUSADI AS CariAdi, dbo.Rut.RutAdi, dbo.GRUP.Deger, dbo.Rut.RutSirasi, dbo.Rut.SaticiKodu, dbo.Rut.SaticiAdi  
FROM            dbo.Rut LEFT OUTER JOIN  
                         dbo.AMUSTERI ON dbo.Rut.CariKodu = dbo.AMUSTERI.MUSKOD LEFT OUTER JOIN  
                         dbo.GRUP ON dbo.Rut.RutAdi = dbo.GRUP.GrupAdi  
WHERE        (dbo.GRUP.Turu = N'R')  

  --[***]--
if exists (select * from sys.procedures where name='spTahsilVadeRaporu_Rut')
drop proc spTahsilVadeRaporu_Rut

--[***]--
create  proc spTahsilVadeRaporu_Rut (@RutAdi nvarchar(max),@RutGunu nvarchar(max),@RutSatici nvarchar(max))  
as    
create table #odeme    
(    
  RecID integer Identity (1, 1) not null, NoId integer, IslemNo integer, Tarih datetime,    
  CariNo integer,  IslemTuru nvarchar(20) COLLATE DATABASE_DEFAULT,  Bakiye float,  Borc_Kullanilan float ,  Devreden float,  Borc float,    
  Devir float  PRIMARY KEY CLUSTERED  (RecID)    
)    
  
DECLARE @CariNo int  
Declare @DagitimTutari float    
Declare @NoId integer,  @IslemNo integer,  @Tarih datetime,  @Borc decimal (28, 14), @Borc_Kullanilan decimal (28, 14),  @Son_Kalan_Tutar float,  @IslemTuru nvarchar(20), @TmpTutar decimal(28, 14)    
  
declare cr_Rut cursor FAST_FORWARD READ_ONLY for    
select  CariNo from [VRUT] where @RutAdi is not null and RutAdi =@RutAdi and Deger = @RutGunu and SaticiAdi = @RutSatici 
union ALL 
select MUSNO from AMUSTERI where MUSBAKIYE > 0.01 and  @RutAdi is null
open cr_Rut    
fetch next from cr_Rut    
into  @CariNo  
while (@@FETCH_STATUS = 0)  
begin  
  
 set @DagitimTutari = (select SUM (BORC-ALACAK) from ACARI WHERE MUSNO=@CariNo)     
 set @TmpTutar = @DagitimTutari    
    
 declare cr_Prepare cursor FAST_FORWARD READ_ONLY for    
 select       
   odm.NoId,    
   odm.IslemNo,    
   odm.Tarih,    
   odm.CariNo,    
   odm.Borc,    
   odm.BAAcikla    
 from Odeme odm    
 where    
    odm.CariNo = @CariNo and    
    IsNull (odm.Borc, 0) > 0    
 order by    
   odm.Tarih desc,NoId desc    
    
 open cr_Prepare    
 fetch next from cr_Prepare    
 into    
   @NoId,    
   @IslemNo,    
   @Tarih,    
   @CariNo,    
   @Borc,    
   @IslemTuru    
    
 while (@@FETCH_STATUS = 0) and (@TmpTutar > 0.0)    
 begin    
   if @Borc >= @TmpTutar     
   begin    
  set @Borc_Kullanilan = @TmpTutar    
  set @Son_Kalan_Tutar = @TmpTutar - @Borc    
  set @TmpTutar = 0.0    
   end    
   else    
   begin    
  set @TmpTutar = @TmpTutar - @Borc    
  set @Borc_Kullanilan = @Borc    
  set @Son_Kalan_Tutar = @TmpTutar    
   end    
    
   insert into #odeme     
   (    
  NoId,    
  IslemNo,    
  Tarih,    
  CariNo,    
  Borc,    
  Borc_Kullanilan,    
  Devreden,    
  Bakiye,    
  Devir,    
  IslemTuru    
   )    
   values    
   (    
  @NoId,     
  @IslemNo,    
  @Tarih,    
  @CariNo,    
  @Borc,    
  @Borc_Kullanilan,    
  @Son_Kalan_Tutar,    
  @DagitimTutari,    
  @Son_Kalan_Tutar+@Borc,    
  @IslemTuru    
   )    
    
   fetch next from cr_Prepare    
   into    
  @NoId,    
  @IslemNo,    
  @Tarih,    
  @CariNo,    
  @Borc,    
  @IslemTuru    
 end    
      
 close cr_Prepare    
 deallocate cr_Prepare     
  
   FETCH NEXT FROM cr_Rut  INTO @CariNo  
end     
close cr_Rut    
deallocate cr_Rut       
  
select    
  RecID,  
  o.NoId,  
  a.MUSNO  [Cari No],    
  a.MUSKOD [Cari Kodu],    
  a.MUSADI [Cari Adı],    
  o.IslemTuru [İşlem Türü],    
  o.IslemNo [İşlem No],    
  c.ACIKLAMA [Açıklama],    
  CONVERT(nvarchar(10),c.TARIH,104)  [İşlem Tarihi],    
  CONVERT(nvarchar(10),o.Tarih,104)  [Vade Tarihi],    
  CAST(GETDATE() - o.Tarih as int) [Geciken Gün],    
  Devreden,  
  o.Borc [Borç],    
  o.Devir,    
  o.Bakiye,    
  d.FatSerNo [Fatura Seri No],    
  d.FaturaNo [Fatura No]    
 from #odeme o    
 inner join  AMUSTERI a on  o.CariNo = a.MUSNO    
 inner join  ACARI    c on c.ISLEMNO = o.IslemNo and c.BAACIKLA = o.IslemTuru   
 left join   STOKHARDETBAG d on o.IslemNo = d.IslemNo    
 Order by a.MUSNO,o.Tarih desc, o.NoId desc  
 
   --[***]--
   if exists (select * from sys.procedures where name='sp_CARIEKSTRE')
drop proc sp_CARIEKSTRE

--[***]--
CREATE  PROCEDURE [dbo].[sp_CARIEKSTRE]             
@MUSNO nvarchar(30), @ILKTARIH nvarchar(10),@SONTARIH nvarchar(10),                 
 @KAPALIHAREKET nvarchar(1),@ORTVADESEC nvarchar(1),@ISLEMTIPINERE SMALLINT=0,@ISLEMTIPINEREBAKIYEGRUPLA 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 @ISLEMTIPINERE=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,    
 (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 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,    
 (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 @ISLEMTIPINERE=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 @ISLEMTIPINEREBAKIYEGRUPLA=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      

--[***]--
   if exists (select * from sys.procedures where name='sp_STOKHARDETBAG_Update')
drop proc sp_STOKHARDETBAG_Update

--[***]--     
CREATE    procedure [dbo].[sp_STOKHARDETBAG_Update](                  
           @IslemNo int,@IslemTuru nvarchar(10),@CariNo int,@CariKodu nvarchar(20),                  
           @CariAdi nvarchar(50),@Tutar nvarchar(50),@SiparisTarihi smalldatetime,@KdvTutari nvarchar(50),                  
           @FisTuru nvarchar(1),@GC nvarchar(1),@EkIskTut1 nvarchar(50),@EkIskTut2 nvarchar(50),@EkIskTut3 nvarchar(50),                  
           @EkIskTutTop nvarchar(50),@FaturaNo int,@FaturaTarihi smalldatetime,@Satici nvarchar(6),                  
           @DovizTut nvarchar(50),@DovizCinsi smallint,@DovizKodu nvarchar(2),@DovizTutB nvarchar(50),                  
           @BelgeNo nvarchar(10),@OzelKod1 nvarchar(3),@OzelKod2 nvarchar(3),@OzelKod3 nvarchar(3),                  
           @IslemTipi nvarchar(30),@IrsNo nvarchar(13),@Ack1 nvarchar(200),@Ack2 nvarchar(200),                  
           @Ack3 nvarchar(200),@Ack4 nvarchar(200),@KULNO int,@KULADI nvarchar(20),@FatSerNo nvarchar(3),                  
           @SaticiAdi nvarchar(25),@AltIskYuzde1 nvarchar(50),@AltIskYuzde2 nvarchar(50),@AltIskYuzde3 nvarchar(50),                  
           @AK nvarchar(3),@FaturaOPlanGunu int,@Depo nvarchar(30),@GunAy nvarchar(3),@OpsiyonGunu int,                  
           @AcikKapali nvarchar(3),@DovizBorc nvarchar(50),@DovizAlacak nvarchar(50),@FYaz nvarchar(1),                  
           @SecimEH nvarchar(1),@IslemNo3 int,@TP nvarchar(2),@FisNo nvarchar(9),@VardiyaKodu nvarchar(15),                  
           @VardiyaDurumu nvarchar(9),@Departman nvarchar(8),@MasrafToplami nvarchar(50),@KDVHD char(1),                  
           @IslemNo4 int,@KayitDuzeyi int,@YazarKasaNo int,@YazarKasaFisNo nvarchar(10),                  
           @YaziciCount nvarchar(50),@IsIstasyonKodu nvarchar(30),@IsIstasyonAdi nvarchar(30),                  
           @TevkifatTutari nvarchar(50),@VardiyaNo nvarchar(8),@OdemeBilgisi nvarchar(100),@FaturaAdresNo int,                  
           @SevkAdresNo int,@FI nvarchar(1),@EF_ISTISNA int,@FaturaTipi int,@DovizKuru float = 1,@eFaturaNo nvarchar(25) = '')                  
AS                  
           
        
declare @TutarYeni float                
declare @KdvTutariYeni float                
declare @DovizTutYeni float                
declare @MUSNO int                
declare @MUSKOD nvarchar(20)              
declare @MUSADI nvarchar(50)                     
              
declare @DovizBorcYeni float                
declare @DovizAlacakYeni float     
  
declare @YeniTevkifatTut float  
  
SET @YeniTevkifatTut = ISNULL((Select SUM(TevkifatTutari) from Tevkifat t where t.IslemNo = @IslemNo),0)  
              
 print(@FisTuru)              
 if (@FisTuru='I')              
 begin              
 print('Irsaliye')              
 set @TutarYeni=(select sum(SonTutar) - @YeniTevkifatTut from STOKHAR where STOKHAR.IslemNo=@IslemNo  )                
 set @KdvTutariYeni=(select sum(KdvTutari) from STOKHAR where STOKHAR.IslemNo=@IslemNo )                
 set @DovizTutYeni=( @TutarYeni/ convert(float,@DovizKuru) )                
 end         
 else              
 begin              
 print('diğer')              
 set @TutarYeni=(select sum(SonTutar) - @YeniTevkifatTut from STOKHAR where STOKHAR.IslemNo=@IslemNo  or STOKHAR.IslemNo3=@IslemNo)                
 set @KdvTutariYeni=(select sum(KdvTutari) from STOKHAR where STOKHAR.IslemNo=@IslemNo  or STOKHAR.IslemNo3=@IslemNo)                
 set @DovizTutYeni=( @TutarYeni/ convert(float,@DovizKuru) )                
 end                        
        
 if(@IslemTuru='DepoVirman')              
 begin              
 set @TutarYeni =@TutarYeni/2              
 set @KdvTutariYeni=@KdvTutariYeni/2              
 set @DovizTutYeni=@DovizTutYeni/2                    
 end              
            
               
IF (select COUNT(*) from (select Count(CariKodu) c from STOKHAR where STOKHAR.IslemNo=@IslemNo  or STOKHAR.IslemNo3=@IslemNo GROUP BY CariKodu) t) > 1            
BEGIN     
 RAISERROR ('Kayıtlarda cari farkı vardır. Kontrol ediniz..!',10,1)        
 RETURN 0        
END            
        
select @MUSNO=MUSNO,@MUSKOD=MUSKOD,@MUSADI=MUSADI from AMUSTERI where MUSKOD in (select CariKodu from STOKHAR where STOKHAR.IslemNo=@IslemNo  or STOKHAR.IslemNo3=@IslemNo)                  
        
UPDATE    STOKHARDETBAG                  
SET              IslemTuru = @IslemTuru, CariNo = @MUSNO, CariKodu = @MUSKOD, CariAdi = @MUSADI,                   
Tutar =  @TutarYeni , SiparisTarihi = @SiparisTarihi, KdvTutari = @KdvTutariYeni,                   
FisTuru = @FisTuru, GC = @GC, EkIskTut1 = convert(float,@EkIskTut1), EkIskTut2 = convert(float,@EkIskTut2),                   
EkIskTut3 = convert(float,@EkIskTut3),                   
EkIskTutTop = convert(float,@EkIskTutTop),                   
FaturaNo = @FaturaNo, FaturaTarihi = @FaturaTarihi, Satici = @Satici, DovizTut = @DovizTutYeni,                   
DovizCinsi = @DovizCinsi, DovizKodu = @DovizKodu, DovizTutB =@DovizTutYeni/* convert(float,@DovizTutB)*/,                  
BelgeNo = @BelgeNo, OzelKod1 = @OzelKod1, OzelKod2 = @OzelKod2, OzelKod3 = @OzelKod3, IslemTipi = @IslemTipi, IrsNo = @IrsNo,                   
Ack1 = @Ack1, Ack2 = @Ack2, Ack3 = @Ack3, Ack4 = @Ack4,                   
KULNO = @KULNO, KULADI = @KULADI, FatSerNo = @FatSerNo, SaticiAdi = @SaticiAdi,                   
AltIskYuzde1 = convert(float,@AltIskYuzde1), AltIskYuzde2 = convert(float,@AltIskYuzde2), AltIskYuzde3 = convert(float,@AltIskYuzde3),                  
AK = @AK, FaturaOPlanGunu = @FaturaOPlanGunu, Depo = @Depo,                   
GunAy = @GunAy, OpsiyonGunu = @OpsiyonGunu, AcikKapali = @AcikKapali,                   
DovizBorc = convert(float,@DovizBorc), DovizAlacak = convert(float,@DovizAlacak), FYaz = @FYaz, SecimEH = @SecimEH,                   
IslemNo3 = @IslemNo3, TP = @TP, FisNo = @FisNo, VardiyaKodu = @VardiyaKodu, VardiyaDurumu = @VardiyaDurumu,                   
Departman = @Departman,MasrafToplami = convert(float,@MasrafToplami), KDVHD = @KDVHD, IslemNo4 = @IslemNo4, KayitDuzeyi = @KayitDuzeyi, YazarKasaNo = @YazarKasaNo, YazarKasaFisNo = @YazarKasaFisNo, YaziciCount = convert(float,@YaziciCount),               
   
IsIstasyonKodu = @IsIstasyonKodu, IsIstasyonAdi = @IsIstasyonAdi,                  
TevkifatTutari =convert(FLOAT,@TevkifatTutari ),   
VardiyaNo = @VardiyaNo, OdemeBilgisi = @OdemeBilgisi, FaturaAdresNo = @FaturaAdresNo, SevkAdresNo = @SevkAdresNo, FI = @FI,                  
EF_ISTISNA=@EF_IsTISNA,FaturaTipi=@FaturaTipi,Update_Date = GETDATE() ,    
DovizKur = @DovizKuru ,eFaturaNo =  @eFaturaNo          
where IslemNo =@IslemNo         
        
DECLARE @CariSayisi int        
        
SET @CariSayisi = ( SELECT   COUNT(*) FROM STOKHAR S,STOKHARDETBAG B        
      WHERE (S.IslemNo = B.IslemNo or S.IslemNo3 = B.IslemNo)        
        AND B.IslemNo = @IslemNo        
        AND (S.CariNo <> B.CariNo or S.CariKodu <> B.CariKodu))        
        
IF @CariSayisi > 0            
BEGIN        
 RAISERROR ('Cari hatası. Kontrol ediniz..!',10,1)        
 RETURN 0        
END 

--[***]--
   if exists (select * from sys.procedures where name='sp_Prk_Satis_Cevirme')
drop proc sp_Prk_Satis_Cevirme

--[***]-- 
create  procedure sp_Prk_Satis_Cevirme(@CariKodu nvarchar(50),@IslemNo int,@YeniIslemNo int,@IslemTuruSatis nvarchar(50),                  
@IslemTuruPrkSatis nvarchar(50),                  
@FaturaTarihi datetime,@FaturaNo integer,@FatSerNo nvarchar(50),@SeciliIslemNolar nvarchar(max),@GeldigiYer nvarchar(50)='')                  
as         
      
      
declare @sqlText nvarchar(max)                  
declare @sqlTextHepsi nvarchar(max)                  
                  
declare @i integer                  
declare @toplam integer;                  
declare @ParamIslemNo integer                               
declare @ExecuteCalistir int;            
 BEGIN TRANSACTION             
  set @ExecuteCalistir=1;            
              
 UPDATE STOKHAR SET AcikKapali='K',IslemNo3= @YeniIslemNo,FisTuru = 'I',IslemTuru= @IslemTuruSatis WHERE IslemNo in (SELECT convert(int,Name) FROM splitstring(@SeciliIslemNolar, ','))  
 UPDATE STOKHARDETBAG SET AcikKapali='K',SecimEH= 'H',FisTuru = 'I',IslemTuru = @IslemTuruSatis,FaturaTarihi = @FaturaTarihi  WHERE IslemNo in (SELECT convert(int,Name) FROM splitstring(@SeciliIslemNolar, ','))  
                          
   print('/*En Büyük İşlem No Parametre - Seçili İşlem Numaraları */'+convert(varchar,@IslemNo)+'-'+@SeciliIslemNolar);  
   
  SET @sqlText=' insert into STOKHARDETBAG (IslemNo, IslemTuru, CariNo, CariKodu, CariAdi, Tutar,  KdvTutari, FisTuru, GC, '+        
  
    ' FaturaNo,  Satici, DovizTut, DovizCinsi, DovizKodu, DovizTutB, '+              
                ' BelgeNo, OzelKod1, OzelKod2, OzelKod3, IslemTipi, IrsNo, Ack1, Ack2, Ack3, Ack4, KULNO, KULADI, FatSerNo, SaticiAdi, AK, FaturaOPlanGunu, Depo, GunAy, OpsiyonGunu, AcikKapali, '+               
                ' DovizBorc, DovizAlacak,  IslemNo3, TP,  VardiyaKodu, VardiyaDurumu, Departman, MasrafToplami, KDVHD, IslemNo4, KayitDuzeyi, YazarKasaNo, YazarKasaFisNo, YaziciCount, IsIstasyonKodu, '+               
                ' IsIstasyonAdi, TevkifatTutari, VardiyaNo, OdemeBilgisi, FaturaAdresNo, SevkAdresNo, FI,  eFaturaNo, UUID, eFaturaGonderimTarihi, eFaturaDurumu, EF_ISTISNA,  FaturaTipi,  '+              
                ' Entegrator,DovizKur)  '+              
    ' select top 1 '+CONVERT(VARCHAR,@YeniIslemNo)+' as IslemNo, IslemTuru, CariNo, CariKodu, CariAdi,'+              
    ' 0 as Tutar, '+            
    ' 0 as KdvTutari, '+              
    ' ''F'' as FisTuru, GC, '+               
    /*' EkIskTut1, EkIskTut2, EkIskTut3, EkIskTutTop, '+*/              
    ' '+convert(varchar,@FaturaNo)+' as FaturaNo, Satici, '+              
    ' 0 as DovizTut, '+              
    ' DovizCinsi, DovizKodu,  0 as DovizTutB, '+              
                ' BelgeNo, OzelKod1, OzelKod2, OzelKod3, IslemTipi, IrsNo, Ack1, Ack2, Ack3, Ack4, KULNO, KULADI, '+@FatSerNo+', SaticiAdi, AK, FaturaOPlanGunu, Depo, GunAy, OpsiyonGunu, AcikKapali, '+               
                ' 0 as DovizBorc, '+              
    ' 0 as DovizAlacak, 0 as IslemNo3, '+         
    ' TP,  VardiyaKodu, VardiyaDurumu, Departman, 0 as MasrafToplami, KDVHD, IslemNo4, KayitDuzeyi, YazarKasaNo, YazarKasaFisNo, YaziciCount, IsIstasyonKodu, '+               
                ' IsIstasyonAdi,0 as TevkifatTutari, VardiyaNo, OdemeBilgisi, FaturaAdresNo, SevkAdresNo, FI, eFaturaNo, UUID, eFaturaGonderimTarihi, eFaturaDurumu, EF_ISTISNA,   FaturaTipi,  '+        
      
                ' Entegrator,DovizKur from STOKHARDETBAG where CariKodu = '+@CariKodu+' and IslemNo='+convert(varchar,@IslemNo)+' ';                  
               
   
    
  print('/*2*/'+@sqlText);                  
 if @ExecuteCalistir>0            
  exec(@sqlText)      
    
  UPDATE STOKHARDETBAG SET IslemTuru =@IslemTuruSatis,FaturaTarihi = @FaturaTarihi, SevkTarihi = @FaturaTarihi, SiparisTarihi = @FaturaTarihi WHERE IslemNo = @YeniIslemNo;  
              
  set @sqlText='update STOKHARDETBAG set '+              
   ' Tutar=(select sum(Tutar) from STOKHARDETBAG where CariKodu = '+@CariKodu+' and IslemNo in (SELECT Name FROM splitstring('''+@SeciliIslemNolar+''', '','')) ), '+              
   ' KdvTutari=(select sum(KdvTutari) from STOKHARDETBAG where CariKodu = '+@CariKodu+' and IslemNo in (SELECT Name FROM splitstring('''+@SeciliIslemNolar+''', '','')) ), '+              
   ' DovizTut=(select sum(Tutar) from STOKHARDETBAG where CariKodu = '+@CariKodu+' and IslemNo in (SELECT Name FROM splitstring('''+@SeciliIslemNolar+''', '','')) ), '+              
   ' DovizBorc=(select sum(DovizBorc) from STOKHARDETBAG where CariKodu = '+@CariKodu+' and IslemNo in (SELECT Name FROM splitstring('''+@SeciliIslemNolar+''', '','')) ), '+              
   ' DovizAlacak=(select sum(DovizAlacak) from STOKHARDETBAG where CariKodu = '+@CariKodu+' and IslemNo in (SELECT Name FROM splitstring('''+@SeciliIslemNolar+''', '','')) ), '+              
   ' DovizTutB=(select sum(Tutar) from STOKHARDETBAG where CariKodu = '+@CariKodu+' and IslemNo in (SELECT Name FROM splitstring('''+@SeciliIslemNolar+''', '','')) ) '+              
   ' where CariKodu = '+@CariKodu+' and IslemNo='+convert(varchar,@YeniIslemNo)+' ';                 
              
  print('/*2.1*/'+@sqlText);                  
 if @ExecuteCalistir>0            
  exec(@sqlText)                  
 if @IslemTuruSatis = 'Alış'  
 SET @sqlText=' delete from ACARI where MUSKOD = '+@CariKodu+' and  ISLEMNO in ('+@SeciliIslemNolar+')  and ISLEMNO <> '+convert(varchar,@IslemNo)+'  and BAACIKLA = ''Alış'' '  
 else   
 SET @sqlText=' delete from ACARI where MUSKOD = '+@CariKodu+' and  ISLEMNO in ('+@SeciliIslemNolar+')  and ISLEMNO <> '+convert(varchar,@IslemNo)+'  and BAACIKLA in (''Prk.Satış'',''Satış'') '  

  print('/*3*/'+@sqlText);            
 if @ExecuteCalistir>0            
 exec(@sqlText)   
 if @IslemTuruSatis = 'Alış'  
 SET @sqlText=' update Odeme set IslemNo = '+convert(varchar,@YeniIslemNo)+' where CariKodu = '+@CariKodu+' and IslemNo in ('+@SeciliIslemNolar+')  and BAAcikla = ''Alış'' ';  
 else  
 SET @sqlText=' update Odeme set IslemNo = '+convert(varchar,@YeniIslemNo)+',BAAcikla = ''Satış''  where CariKodu = '+@CariKodu+' and IslemNo in ('+@SeciliIslemNolar+')  and BAAcikla = ''Prk.Satış'',''Satış'' ';  
 if @ExecuteCalistir>0            
 exec(@sqlText)   

if @IslemTuruSatis = 'Satış'   
 set @sqlText='update dbo.ACARI '+                  
                     ' SET ACIKLAMA='''+convert(varchar,@FaturaNo)+' no.lu Fatura'',BAACIKLA = ''Satış'', '+                  
                     ' dbo.ACARI.BORC = (select sum(ISNULL(dbo.STOKHARDETBAG.Tutar,0)) from dbo.STOKHARDETBAG where dbo.STOKHARDETBAG.IslemNo = '+CONVERT(varchar,@YeniIslemNo)+'), '+                  
                     ' dbo.ACARI.DovizBorc = (select sum(ISNULL(dbo.STOKHARDETBAG.DovizBorc,0)) from dbo.STOKHARDETBAG where dbo.STOKHARDETBAG.IslemNo = '+CONVERT(varchar,@YeniIslemNo)+'), '+                  
                     ' dbo.ACARI.DovizAlacak = (select sum(ISNULL(dbo.STOKHARDETBAG.DovizAlacak,0)) from dbo.STOKHARDETBAG where dbo.STOKHARDETBAG.IslemNo = '+CONVERT(varchar,@YeniIslemNo)+'), '+               
      ' dbo.ACARI.ISLEMNO = '+CONVERT(varchar,@YeniIslemNo)+                                
                     ' where dbo.ACARI.ISLEMNO = '+convert(varchar,@IslemNo)+' and BAACIKLA in (''Prk.Satış'',''Satış'') '  
else       
 set @sqlText='update dbo.ACARI '+                  
                     ' SET ACIKLAMA='''+convert(varchar,@FaturaNo)+' no.lu Fatura'', '+                  
                     ' dbo.ACARI.BORC = (select sum(ISNULL(dbo.STOKHARDETBAG.Tutar,0)) from dbo.STOKHARDETBAG where dbo.STOKHARDETBAG.IslemNo = '+CONVERT(varchar,@YeniIslemNo)+'), '+                  
                     ' dbo.ACARI.DovizBorc = (select sum(ISNULL(dbo.STOKHARDETBAG.DovizBorc,0)) from dbo.STOKHARDETBAG where dbo.STOKHARDETBAG.IslemNo = '+CONVERT(varchar,@YeniIslemNo)+'), '+                  
                     ' dbo.ACARI.DovizAlacak = (select sum(ISNULL(dbo.STOKHARDETBAG.DovizAlacak,0)) from dbo.STOKHARDETBAG where dbo.STOKHARDETBAG.IslemNo = '+CONVERT(varchar,@YeniIslemNo)+'), '+               
      ' dbo.ACARI.ISLEMNO = '+CONVERT(varchar,@YeniIslemNo)+                                
                     ' where dbo.ACARI.ISLEMNO = '+convert(varchar,@IslemNo)+' and BAACIKLA ''Alış'' ';        
print('/*4*/'+@sqlText);                  
 if @ExecuteCalistir>0            
 exec(@sqlText)              
  
if @IslemTuruSatis = 'Alış'   
 UPDATE dbo.ACARI SET TARIH = @FaturaTarihi where ISLEMNO = @YeniIslemNo and BAACIKLA = 'Alış'  
else  
 UPDATE dbo.ACARI SET TARIH = @FaturaTarihi where ISLEMNO = @YeniIslemNo and BAACIKLA = 'Satış';    
  
     
 if @geldigiYer <>'cariperakende'     
 begin    
 print('/*5*/'+@sqlText);     
 set @sqlText='Delete from STOKHARDETBAG  where CariKodu = '+@CariKodu+' and FisTuru = ''I'' and IslemNo in (SELECT Name FROM splitstring('''+@SeciliIslemNolar+''', '','')) ';    
 exec(@sqlText)     
 end    
                   
if @@ERROR<>0                  
begin                  
ROLLBACK                   
 if @ExecuteCalistir>0            
Select 'İşlem sırasında bir hata oluştu, işlem tamamlanamadı!'                  
else            
print('İşlem sırasında bir hata oluştu, işlem tamamlanamadı!' )            
end                  
ELSE begin                   
COMMIT             
 if @ExecuteCalistir>0            
Select 'İşlem başarıyla tamamlandı'              
else             
print('İşlem başarıyla tamamlandı')            
end  

--[***]--
   if exists (select * from sys.procedures where name='spStokHarDetBagKaydet')
drop proc spStokHarDetBagKaydet

--[***]-- 
create     PROCEDURE [dbo].[spStokHarDetBagKaydet]          
@IslemNo int,         
@IslemTuru nvarchar(10),         
@CariNo int,         
@CariKodu nvarchar(20),         
@CariAdi nvarchar(50),         
@Tutar float,         
@SiparisTarihi datetime,         
@KdvTutari float,         
@FisTuru nvarchar(1),         
@GC nvarchar(1),         
@EkIskTut1 float,         
@EkIskTut2 float,         
@EkIskTut3 float,         
@EkIskTutTop float,         
@FaturaNo int,         
@FaturaTarihi datetime,         
@Satici nvarchar(6),         
@DovizTut float,         
@DovizCinsi smallint,         
@DovizKodu nvarchar(2),         
@DovizTutB float,         
@BelgeNo nvarchar(10),         
@OzelKod1 nvarchar(3),         
@OzelKod2 nvarchar(3),         
@OzelKod3 nvarchar(3),         
@IslemTipi nvarchar(30),         
@IrsNo nvarchar(13),         
@Ack1 nvarchar(200),         
@Ack2 nvarchar(200),         
@Ack3 nvarchar(200),         
@Ack4 nvarchar(200),         
@KULNO int,         
@KULADI nvarchar(20),         
@FatSerNo varchar(3),         
@SaticiAdi nvarchar(25),         
@AltIskYuzde1 float,         
@AltIskYuzde2 float,         
@AltIskYuzde3 float,         
@AK nvarchar(3),         
@FaturaOPlanGunu int,         
@Depo nvarchar(30),         
@GunAy nvarchar(3),         
@OpsiyonGunu int,         
@AcikKapali nvarchar(3),         
@DovizBorc float,         
@DovizAlacak float,         
@FYaz nvarchar(1),         
@SecimEH nvarchar(1),         
@IslemNo3 int,         
@TP nvarchar(2),         
@FisNo nvarchar(9),         
@VardiyaKodu nvarchar(15),         
@VardiyaDurumu nvarchar(9),         
@Departman nvarchar(8),         
@MasrafToplami float,         
@KDVDH char,         
@IslemNo4 int,         
@KayitDuzeyi int,         
@YazarKasaNo int,         
@YazarKasaFisNo nvarchar(10),         
@YaziciCount float,         
@IsIstasyonKodu nvarchar(30),         
@IsIstasyonAdi nvarchar(30),              
@TevkifatTutari float,         
@VardiyaNo nvarchar(8),         
@OdemeBilgisi nvarchar(100),         
@FaturaAdresNo int,         
@SevkAdresNo int,         
@FI nvarchar(1),         
@SevkTarihi dateTime,         
@eFaturaNo nvarchar(30),         
@UUID nVarChar(50),         
@eFaturaGonderimTarihi DateTime,         
@eFaturaDurumu nVarChar(20) ,            
@FaturaTipi int,      
@DovizKuru float = 1      
         
 AS         
 begin         
 declare @Istisna int;        
 declare @HesaplananTutar float        
 declare @HesaplananKdvTutari float        
         
          
 if (@FisTuru='I')         
 begin         
 set @HesaplananTutar = (select sum(SonTutar) from STOKHAR where CariNo=@CariNo and (IslemNo=@IslemNo))        
 set @HesaplananKdvTutari = (select sum(KdvTutari) from STOKHAR where CariNo=@CariNo and (IslemNo=@IslemNo) )        
 end else         
 begin         
 set @HesaplananTutar = (select sum(SonTutar) from STOKHAR where CariNo=@CariNo and (IslemNo3=@IslemNo or IslemNo=@IslemNo) )        
 set @HesaplananKdvTutari = (select sum(KdvTutari) from STOKHAR where CariNo=@CariNo and (IslemNo3=@IslemNo or IslemNo=@IslemNo) )        
  
 end         
         
         
 if(@IslemTuru='DepoVirman')         
 begin         
 set @HesaplananTutar =@HesaplananTutar/2         
 set @HesaplananKdvTutari=@HesaplananKdvTutari/2         
         
 end         
 set @Istisna=(select convert(int,(SELECT top 1 v.MuafiyetKodu FROM STOKHAR sh join StokVergiMuafiyetleri v on v.StokKodu=sh.StokKodu WHERE IslemNo=@IslemNo and Kdv=0)))        
         
select @TevkifatTutari =     sum(t.TevkifatTutari) from Tevkifat t where t.IslemNo in(select distinct IslemNo from STOKHAR where  @IslemNo in(IslemNo,IslemNo3)) 
  
if @TevkifatTutari > 0  
begin  

 set @HesaplananTutar = @HesaplananTutar - @TevkifatTutari  
end  
  
 INSERT STOKHARDETBAG (IslemNo, IslemTuru, CariNo, CariKodu, CariAdi, Tutar, SiparisTarihi, KdvTutari, FisTuru, GC, EkIskTut1, EkIskTut2, EkIskTut3, EkIskTutTop, FaturaNo, FaturaTarihi, Satici, DovizTut, DovizCinsi, DovizKodu, DovizTutB, BelgeNo,         
        OzelKod1, OzelKod2, OzelKod3, IslemTipi, IrsNo, Ack1, Ack2, Ack3, Ack4, KULNO, KULADI, FatSerNo, SaticiAdi, AltIskYuzde1, AltIskYuzde2, AltIskYuzde3, AK, FaturaOPlanGunu, Depo, GunAy, OpsiyonGunu, AcikKapali,         
        DovizBorc, DovizAlacak, FYaz, SecimEH, IslemNo3, TP, FisNo, VardiyaKodu, VardiyaDurumu, Departman, MasrafToplami, KDVHD, IslemNo4, KayitDuzeyi, YazarKasaNo, YazarKasaFisNo, YaziciCount, IsIstasyonKodu,         
        IsIstasyonAdi, VardiyaNo, OdemeBilgisi, FaturaAdresNo, SevkAdresNo, FI, SevkTarihi, eFaturaNo, UUID, eFaturaGonderimTarihi,    
  eFaturaDurumu, EF_ISTISNA, FaturaTipi,         
        Entegrator, Update_Date,DovizKur,TevkifatTutari)         
 VALUES     (@IslemNo, @IslemTuru,@CariNo,@CariKodu,@CariAdi,@HesaplananTutar ,         
       @SiparisTarihi,@HesaplananKdvTutari ,@FisTuru, @GC, @EkIskTut1, @EkIskTut2, @EkIskTut3,@EkIskTutTop, @FaturaNo, @FaturaTarihi,  @Satici,  @DovizTut,  @DovizCinsi,  @DovizKodu,  @DovizTutB,  @BelgeNo,    @OzelKod1,          
       @OzelKod2,  @OzelKod3,  @IslemTipi,  @IrsNo,  @Ack1,  @Ack2,  @Ack3,  @Ack4,  @KULNO,  @KULADI,  @FatSerNo,  @SaticiAdi, @AltIskYuzde1,         
       @AltIskYuzde2,  @AltIskYuzde3,  @AK,  @FaturaOPlanGunu,  @Depo, @GunAy,  @OpsiyonGunu,  @AcikKapali,   @DovizBorc,  @DovizAlacak,  @FYaz,         
       @SecimEH,  @IslemNo3,  @TP,  @FisNo,  @VardiyaKodu,  @VardiyaDurumu, @Departman,  @MasrafToplami,  @KDVDH,  @IslemNo4,         
       @KayitDuzeyi,  @YazarKasaNo,  @YazarKasaFisNo,  @YaziciCount, @IsIstasyonKodu,   @IsIstasyonAdi,          
       @VardiyaNo,  @OdemeBilgisi,  @FaturaAdresNo,  @SevkAdresNo,  @FI,  @SevkTarihi,  @eFaturaNo,  @UUID,  @eFaturaGonderimTarihi,          
       @eFaturaDurumu ,  @Istisna,  @FaturaTipi,  0,GETDATE(),@DovizKuru, @TevkifatTutari   
)        
        
END

--[***]--
   if exists (select * from sys.procedures where name='spTevkifat_Aciklama_Sil')
drop proc spTevkifat_Aciklama_Sil

--[***]-- 
CREATE  PROC spTevkifat_Aciklama_Sil (@Tip varchar(10),@IslemNo int)  
AS  
BEGIN 
    IF @Tip = 'F'
	BEGIN
		DELETE Tevkifat WHERE IslemNo IN (select IslemNo from STOKHAR where @IslemNo in (IslemNo,IslemNo3));
		DELETE ACIKLAMA WHERE FATURANO IN (select IslemNo from STOKHAR where @IslemNo in (IslemNo,IslemNo3));
		DELETE FatAciklama WHERE IslemNo2  = @IslemNo
    END
	ELSE
	BEGIN
		DELETE Tevkifat WHERE IslemNo IN (select IslemNo from STOKHAR where @IslemNo in (IslemNo,IslemNo3));
		DELETE ACIKLAMA WHERE FATURANO  IN (select IslemNo from STOKHAR where @IslemNo in (IslemNo,IslemNo3));
		DELETE FatAciklama WHERE IslemNo2 IN (select IslemNo2 from STOKHAR where @IslemNo in (IslemNo,IslemNo3));
	END
END

--[***]--
   if exists (select * from sys.procedures where name='spTevkifatSil')
drop proc spTevkifatSil

--[***]-- 
create  proc spTevkifatSil (@IslemNo int)
AS
BEGIN
	delete Tevkifat WHERE IslemNo IN (select IslemNo from STOKHAR where @IslemNo in (IslemNo,IslemNo3))
END

--[***]--
   if exists (select * from sys.procedures where name='sp_ButonResmiGetir')
drop proc sp_ButonResmiGetir

--[***]-- 
CREATE PROCEDURE [dbo].[sp_ButonResmiGetir] @StokNo nvarchar(10)    
AS    
BEGIN    
select top 1 DBImage from Resimler where ResimTipi=7 and No=@StokNo    
END   

--[***]--
   if exists (select * from sys.procedures where name='RaporGetir')
drop proc RaporGetir

--[***]--
create   PROCEDURE [dbo].[RaporGetir]         
 @TipNo int,        
 @KulNo int,        
 @CariNo int = 0,        
 @Onizleme bit = 1 OUTPUT,        
 @YeniRapormu bit = 0        
AS        
 DECLARE @CariTipi nvarchar(30),        
  @OzelKod1 nvarchar(40), @OzelKod2 nvarchar(40),         
  @OzelKod3 nvarchar(40), @OzelKod4 nvarchar(40)        
 DECLARE @RaporNo int        
        
 select @CariTipi = ISNULL(Tipi, ''),        
  @OzelKod1 = ISNULL(OZELKOD1, ''),        
  @OzelKod2 = ISNULL(OZELKOD2, ''),        
  @OzelKod3 = ISNULL(OZELKOD3, ''),        
  @OzelKod4 = ISNULL(OZELKOD4, '')        
 from AMUSTERI        
 where MUSNO = @CariNo        
   
   
 select @RaporNo = RaporNo        
 from CariVarsayilanRaporlari --Null ise kriteri dikkate alma(1=1 gibi olacak)        
 where ISNULL(CariTipi, @CariTipi) = @CariTipi AND        
  ISNULL(OzelKod1, @OzelKod1) = @OzelKod1 AND         
  ISNULL(OzelKod2, @OzelKod2) = @OzelKod2 AND         
  ISNULL(OzelKod3, @OzelKod3) = @OzelKod3 AND        
  ISNULL(OzelKod4, @OzelKod4) = @OzelKod4 AND        
  TipNo = @TipNo        
   
   
  
 IF @RaporNo IS NOT NULL BEGIN--Carinin raporu varsa        
  SELECT RaporIsmi, Rapor from Rapor where RaporNo = @RaporNo        
  SELECT @Onizleme = ISNULL(Onizleme, 1) from Rapor where RaporNo = @RaporNo        
  RETURN        
 END        
 ELSE BEGIN--Kullanicinin Varsayılan raporundan yazdır    
  
  SELECT RaporIsmi, Rapor, RaporNo,(select GirisAdi from Kullanicilar where No=@KulNo ) as Kullanici from Rapor where RaporNo = (select RaporNo from KullaniciVarsayilanRaporlari         
         where KullaniciNo=@KulNo and TipNo=@TipNo) and YeniRapormu=@YeniRapormu        
  SELECT @Onizleme = ISNULL(Onizleme, 1) from Rapor where RaporNo = (select RaporNo from KullaniciVarsayilanRaporlari         
         where KullaniciNo=@KulNo and TipNo=@TipNo)     
     
  
  
  RETURN        
 END
 
 --[***]--
if  not  exists (select * from dbo.syscolumns where id = object_id(N'STOKKARTI') and name=N'UlkeKodu')
begin
 ALTER TABLE STOKKARTI ADD UlkeKodu nvarchar(24)
end

--[***]--
 IF NOT EXISTS (SELECT *  FROM sys.tables WHERE  name='ILCE')
 BEGIN 
 
CREATE TABLE [dbo].[ILCE](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[IL_ID] [int] NOT NULL,
	[ILCE_ID] [int] NULL,
	[ILCE_ADI] [nvarchar](50) NULL,
	[AKTIF] [nvarchar](1) NULL,
	[ISLEM_YAPAN] [int] NULL,
	[ISLEM_TARIHI] [datetime] NULL,
 CONSTRAINT [PK_ILCE] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
end

--[***]--
 IF NOT EXISTS (SELECT *  FROM sys.tables WHERE  name='IL')
 BEGIN 
 
CREATE TABLE [dbo].[IL](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[IL_ID] [int] NOT NULL,
	[IL_ADI] [nvarchar](50) NOT NULL,
	[AKTIF] [nvarchar](1) NULL,
	[ISLEM_YAPAN] [int] NULL,
	[ISLEM_TARIHI] [datetime] NULL,
 CONSTRAINT [PK_IL] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
end

--[***]--
if  not  exists (select * from dbo.syscolumns where id = object_id(N'Rapor') and name=N'RaporSecimEkraniOnIzleme')
begin
alter table Rapor  add RaporSecimEkraniOnIzleme varchar(250)
end

--[***]--
 IF NOT EXISTS (SELECT *  FROM sys.tables WHERE  name='YazarKasalar')
 BEGIN 
CREATE TABLE [dbo].[YazarKasalar](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[YazarKasaTanimi] [varchar](150) NULL,
	[YazarKasaIp] [varchar](50) NULL,
	[YazarKasaPort] [int] NULL,
	[YazarKasaSeriNo] [varchar](20) NULL,
	[ServerIP] [varchar](50) NULL,
	[ServerPort] [int] NULL,
	[KasiyerNo] [int] NULL,
	[KasiyerSifre] [varchar](50) NULL,
	[KulNo] [int] NULL,
 CONSTRAINT [PK_YazarKasalar] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
end

--[***]--
 IF NOT EXISTS (SELECT *  FROM sys.tables WHERE  name='YazarKasalarMapKullanicilar')
 BEGIN 
CREATE TABLE [dbo].[YazarKasalarMapKullanicilar](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[YazarKasaID] [int] NOT NULL,
	[YazarKasaKulNo] [int] NOT NULL,
	[KulNo] [int] NULL,
 CONSTRAINT [PK_YazarKasalarMapKullanicilar] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
end

--[***]--
if  not  exists (select * from dbo.syscolumns where id = object_id(N'Rapor') and name=N'YeniRapormu')
begin
alter table Rapor add YeniRapormu bit
end

--[***]--
if  not  exists (select * from dbo.syscolumns where id = object_id(N'Rapor') and name=N'VarsayilanRaporKullNo')
begin
alter table Rapor add VarsayilanRaporKullNo varchar(250)
end

--[***]--
if  not  exists (select * from dbo.syscolumns where id = object_id(N'Rapor') and name=N'RaporSecimEkraniOnIzleme')
begin
alter table Rapor add RaporSecimEkraniOnIzleme varchar(250)
end

--[***]--
if exists (select * from sys.procedures where name='sp_CARIEKSTRE_UST')
drop proc sp_CARIEKSTRE_UST

--[***]--
 create   PROCEDURE [dbo].[sp_CARIEKSTRE_UST]          
      
@MUSNO int, @ILKTARIH nvarchar(10),@SONTARIH nvarchar(10),                 
 @KAPALIHAREKET nvarchar(1),@ORTVADESEC nvarchar(1),        
 @ISLEMTIPINERE SMALLINT=0,@ISLEMTIPINEREBAKIYEGRUPLA SMALLINT=0,@P1IslemTipi nvarchar(250)=''         
       
AS             
BEGIN         
 declare @ParamIslemTipi nvarchar(250)      
 declare @pIslemTipi nvarchar(250) declare @pBAKIYE1 money        
 set @ParamIslemTipi= @P1IslemTipi;      
      
      
      
DECLARE @ACARI TABLE (ROWNUMBER INT,          
TARIH DATETIME,           
TESSIRNO INT,           
MUSNO INT,           
BAACIKLA nvarchar(14),          
ACIKLAMA nvarchar(900),                
  ISLEMNO INT,          
  DOVIZTUT float,          
  DOVIZKODU nvarchar(2),          
  OZELKOD nvarchar(5),          
  ISLEMTIPI nvarchar(250),                
  BELGENO nvarchar(10),           
  ACIKLABAKIYE nvarchar(1),          
  TP nvarchar(2),           
  [Ort Vade] DATETIME,          
  SaticiKodu nvarchar(30),             
  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 ,          
  TTarihi DATETIME,          
  ISLEMNO3 int,          
  NoID int identity(1,1),          
  BA nvarchar(5),                
  AcikKapali nvarchar(20),          
  VardiyaKodu nvarchar(15),         
  Departman nvarchar(15),          
  IslemNo2 int,                
  VadeTarihi datetime,          
  DOVIZCINSI int,NEWROWNUMBER INT)             
               
  declare @Musteri table (MusteriNo decimal,UstMusteriNo decimal)                
  update ACARI SET AcikKapali='A' WHERE AcikKapali <>'K' and MUSNO = @MUSNO  
  insert into @Musteri                
  select distinct MUSNO,UstCariNo from AMUSTERI where UstCariNo=@MUSNO                
                
DECLARE @BAKIYE money, @IBKY MONEY               
                 
SET @BAKIYE = 0                
IF @IBKY IS NULL                
     SET @IBKY=0                
 if isnull(@ParamIslemTipi,'')<>''      
 begin      
       
 INSERT INTO @ACARI (ROWNUMBER,TARIH,MUSNO,FARK,BAKIYE,BAACIKLA,ACIKLAMA,ISLEMNO)                
 select 0,@ILKTARIH,M.UstMusteriNo,SUM(BORC-ALACAK),@BAKIYE,'Devir','Hesap Ekstresi Devir Tutarý',1                
  from @Musteri M                
  left JOIN  ACARI A ON M.MusteriNo=A.MUSNO AND A.TARIH<=@ILKTARIH        
   and A.ISLEMTIPI=@ParamIslemTipi      
    group by M.UstMusteriNo          
 end else      
 begin      
  IF @ISLEMTIPINERE=1 BEGIN        
       
 INSERT INTO @ACARI (ROWNUMBER,TARIH,MUSNO,FARK,BAKIYE,BAACIKLA,ACIKLAMA,ISLEMNO,ISLEMTIPI)                
 select 0 as ROWNUMBER,@ILKTARIH,M.UstMusteriNo,SUM(isnull(BORC,0)-isnull(ALACAK,0)),@BAKIYE,'Devir','Hesap Ekstresi Devir Tutarý',1,convert(varchar(250),A.ISLEMTIPI) as ISLEMTIPI                 
  from @Musteri M                
  left JOIN  ACARI A ON M.MusteriNo=A.MUSNO AND A.TARIH<=@ILKTARIH       
    group by M.UstMusteriNo,A.ISLEMTIPI         
      
  end else      
  begin      
  INSERT INTO @ACARI (ROWNUMBER,TARIH,MUSNO,FARK,BAKIYE,BAACIKLA,ACIKLAMA,ISLEMNO)                
 select 0 as ROWNUMBER,@ILKTARIH,M.UstMusteriNo,SUM(BORC-ALACAK),@BAKIYE,'Devir','Hesap Ekstresi Devir Tutarý',1                
  from @Musteri M                
  left JOIN  ACARI A ON M.MusteriNo=A.MUSNO AND A.TARIH<=@ILKTARIH       
    group by M.UstMusteriNo         
end          
end      
                  
     
        
   IF @ISLEMTIPINERE=1 BEGIN         if isnull(@ParamIslemTipi,'')<>''      
 begin      
 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), @BAKIYE,TARIH TTarihi,ISLEMNO as ISLEMNO3,                
  BA,AcikKapali,VardiyaKodu,Departman,IslemNo2,VadeTarihi,DOVIZCINSI,0                 
 FROM ACARI                 
 WHERE MUSNO in(select MusteriNo from @Musteri)  AND TARIH >@ILKTARIH AND TARIH<=@SONTARIH        
 and ISLEMTIPI=@ParamIslemTipi      
  ORDER BY ROWNUMBER1 ASC         
      
 end else begin      
  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), @BAKIYE,TARIH TTarihi,ISLEMNO as ISLEMNO3,                
  BA,AcikKapali,VardiyaKodu,Departman,IslemNo2,VadeTarihi,DOVIZCINSI,0                 
 FROM ACARI                 
 WHERE MUSNO in(select MusteriNo from @Musteri)  AND TARIH >@ILKTARIH AND TARIH<=@SONTARIH        
 ORDER BY ROWNUMBER1 ASC      
       
end      
 /*ORDER BY TARIH,ACARI.CARINO*/                
        
end else        
begin        
 if isnull(@ParamIslemTipi,'')<>''      
 begin                
 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), @BAKIYE,TARIH TTarihi,ISLEMNO as ISLEMNO3,                
  BA,AcikKapali,VardiyaKodu,Departman,IslemNo2,VadeTarihi,DOVIZCINSI,0                 
 FROM ACARI                 
 WHERE MUSNO in(select MusteriNo from @Musteri)  AND TARIH >@ILKTARIH AND TARIH<=@SONTARIH and ISLEMTIPI=@ParamIslemTipi              
 /*ORDER BY TARIH,ACARI.CARINO*/        
  ORDER BY ROWNUMBER ASC         
end else       
begin      
 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), @BAKIYE,TARIH TTarihi,ISLEMNO as ISLEMNO3,                
  BA,AcikKapali,VardiyaKodu,Departman,IslemNo2,VadeTarihi,DOVIZCINSI,0                 
 FROM ACARI                 
 WHERE MUSNO in(select MusteriNo from @Musteri)  AND TARIH >@ILKTARIH AND TARIH<=@SONTARIH                
 /*ORDER BY TARIH,ACARI.CARINO*/        
  ORDER BY ROWNUMBER ASC        
end      
end        
        
update @ACARI SET IslemNo3 = ISLEMNO where ISLEMNO>0                

declare @ROWNUMBER INT       
declare @NEWROWNUMBER INT       
set @NEWROWNUMBER=0      
declare @IslemTipi1 varchar(250)        
      
IF (@ISLEMTIPINERE=1) and (@ParamIslemTipi='')  BEGIN      
       
delete from @ACARI where BAACIKLA='Devir' and ISLEMTIPI not in (select distinct ISLEMTIPI from @ACARI where  BAACIKLA<>'Devir')      
      
 INSERT INTO @ACARI (ROWNUMBER,TARIH,MUSNO,FARK,BAKIYE,BAACIKLA,ACIKLAMA,ISLEMNO,ISLEMTIPI)                
 select 0 as ROWNUMBER,@ILKTARIH,M.UstMusteriNo,0,0,'Devir','Hesap Ekstresi Devir Tutarý',1, A.ISLEMTIPI                   
 from @Musteri M               
 left JOIN  ACARI A ON M.MusteriNo=A.MUSNO AND A.TARIH>@ILKTARIH       
 where (select count(B.ISLEMNO) from ACARI B where M.MusteriNo=B.MUSNO AND B.TARIH<=@ILKTARIH and A.ISLEMTIPI=B.ISLEMTIPI)=0      
 group by M.UstMusteriNo,A.ISLEMTIPI         
        
update @ACARI SET IslemNo3 = ISLEMNO where ISLEMNO>0       
      
declare AcariCursor1 cursor  for Select ROWNUMBER,ISLEMTIPI FROM  @ACARI ORDER BY ISLEMTIPI ASC,ROWNUMBER ASC        
FOR UPDATE OF ROWNUMBER OPEN AcariCursor1 FETCH NEXT FROM AcariCursor1       
INTO @ROWNUMBER,@IslemTipi1        
WHILE @@FETCH_STATUS=0        
BEGIN       
   if @NEWROWNUMBER=0      
   begin      
    set @pIslemTipi=@IslemTipi1;      
    update TOP(1) @ACARI SET NEWROWNUMBER=@NEWROWNUMBER,BAKIYE=(select SUM(BORC-ALACAK) from @Musteri M                
     left JOIN  ACARI A ON M.MusteriNo=A.MUSNO AND A.TARIH<=@ILKTARIH        
     and A.ISLEMTIPI=@pIslemTipi      
      group by M.UstMusteriNo) where ISLEMTIPI=@IslemTipi1 and BAACIKLA='Devir'      
             set @NEWROWNUMBER=@NEWROWNUMBER+1;      
      
   end else if (@pIslemTipi=@IslemTipi1)      
   begin      
    update TOP(1) @ACARI SET NEWROWNUMBER=@NEWROWNUMBER where ISLEMTIPI=@IslemTipi1 and ROWNUMBER=@ROWNUMBER and  BAACIKLA<>'Devir'      
   set @NEWROWNUMBER=@NEWROWNUMBER+1;      
      
   end else      
   begin      
    set @pIslemTipi=@IslemTipi1;      
     update TOP(1) @ACARI SET NEWROWNUMBER=@NEWROWNUMBER,BAKIYE=(select SUM(BORC-ALACAK) from @Musteri M                
     left JOIN  ACARI A ON M.MusteriNo=A.MUSNO AND A.TARIH<=@ILKTARIH        
     and A.ISLEMTIPI=@pIslemTipi      
      group by M.UstMusteriNo) where ISLEMTIPI=@IslemTipi1 and BAACIKLA='Devir'      
    set @NEWROWNUMBER=@NEWROWNUMBER+1;      
      
   end      
      
FETCH NEXT FROM AcariCursor1 INTO @ROWNUMBER,@IslemTipi1        
END  CLOSE AcariCursor1  DEALLOCATE AcariCursor1         
update @ACARI set ROWNUMBER=NEWROWNUMBER      
      
 end      
      
      
        
DECLARE @BAKIYE2 money      
DECLARE @DevirBakiye money      
      
      
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 @ISLEMTIPINERE=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         
  begin      
   UPDATE TOP(1) @ACARI SET BAKIYE =isnull(FARK,0)  FROM @ACARI where ROWNUMBER =0      
   SET @DevirBakiye=(SELECT SUM(ISNULL(FARK,0)) FROM @ACARI  WHERE ROWNUMBER =0)        
  end else      
  begin      
  if @ROWNUMBER=1         
     set @pIslemTipi=@IslemTipi1;      
      
   if @pIslemTipi=@IslemTipi1            
   begin             
  SET @BAKIYE2=@DevirBakiye+(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=@DevirBakiye+(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        
  end      
  FETCH NEXT FROM AcariCursor INTO @ROWNUMBER,@IslemTipi1        
END        
  CLOSE AcariCursor  DEALLOCATE AcariCursor         
        
        
        
DELETE FROM AcariTakas WHERE MUSNO  in(select MusteriNo from @Musteri)                 
 insert into AcariTakas(          
 TARIH,          
 TESSIRNO,          
 MUSNO,          
 BAACIKLA,          
 ACIKLAMA,          
 ISLEMNO,          
 DOVIZTUT,          
 DOVIZKODU,          
 OZELKOD,ISLEMTIPI,BELGENO,TP,                
VadeTarihi,SaticiKodu,SaticiAdi,          
KULNO,KULADI,DovizBorc,DovizAlacak,Aciklama2,          
SiraNo,MUSKOD,MUSADI,IsIstasyonKodu,IsIstasyonAdi,                
TeslimAlan,TeslimEden,BORC,ALACAK,BAKIYE,TTarihi,IslemNo3,          
BA,AcikKapali,VardiyaKodu,Departman,IslemNo2,DOVIZCINSI)                
select A.TARIH,A.TESSIRNO,A.MUSNO,          
A.BAACIKLA,A.ACIKLAMA,A.ISLEMNO,A.DOVIZTUT,A.DOVIZKODU,          
A.OZELKOD,A.ISLEMTIPI,A.BELGENO,A.TP,                
A.[Ort Vade],A.SaticiKodu,A.SaticiAdi,          
isnull(A.KULNO,0),A.KULADI,isnull(A.DovizBorc,0),          
isnull(A.DovizAlacak,0),A.Aciklama2,A.NoID,          
isnull(A.MUSKOD,''),A.MUSADI,A.IsIstasyonKodu,          
A.IsIstasyonAdi,                
A.TESLIM,A.TESLIM,isnull(A.BORC,0),          
isnull(A.ALACAK,0),isnull(A.BAKIYE,0),A.TTarihi,          
A.ISLEMNO3,A.BA,A.AcikKapali,A.VardiyaKodu,          
isnull(A.Departman,''),isnull(A.IslemNo2,0),isnull(A.DOVIZCINSI,0)                
            
 FROM @ACARI A                
 Left Join STOKHARDETBAG sb on sb.IslemNo=A.ISLEMNO                
 Left Join Adres ad on ad.CariKodu=sb.CariKodu and ad.AdresNo=sb.FaturaAdresNo                
 Left Join AMUSTERI ams on ams.MUSNO=A.MUSNO                
order by  A.ROWNUMBER        
  if @ISLEMTIPINEREBAKIYEGRUPLA=0        
  begin        
 select A.*, ams.Tipi As CariTipi, ams.CariGrubu, ad.AdresNo,                
  ad.Adres1, dbo.fn_FaturaSerinolari(A.ISLEMNO) As SeriNolar                 
                
  FROM @ACARI A                
  Left Join STOKHARDETBAG sb on sb.IslemNo=A.ISLEMNO                
  Left Join Adres ad on ad.CariKodu=sb.CariKodu and ad.AdresNo=sb.FaturaAdresNo                
  Left Join AMUSTERI ams on ams.MUSNO=A.MUSNO         
  order by  A.ROWNUMBER        
  /*order by  A.MUSNO,TARIH,CARINO */        
 end  else        
 begin        
   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        
        
 end        
        
 end 
 
 --[***]--
 
  if exists (select * from sys.tables where name='AcariTakas')
drop table AcariTakas

 --[***]--
  IF NOT EXISTS (SELECT *  FROM sys.tables WHERE  name='AcariTakas')
 BEGIN 
CREATE TABLE [dbo].[AcariTakas](
	[NoID] [int] IDENTITY(1,1) NOT NULL,
	[TESSIRNO] [int] NULL,
	[MUSNO] [int] NOT NULL,
	[MUSADI] [nvarchar](50) NULL,
	[TARIH] [smalldatetime] NOT NULL,
	[ACIKLAMA] [nvarchar](200) NULL,
	[BORC] [float] NOT NULL,
	[ALACAK] [float] NOT NULL,
	[BA] [nvarchar](1) NULL,
	[BAACIKLA] [nvarchar](14) NULL,
	[BAKIYE] [float] NOT NULL,
	[ACIKLABAKIYE] [nvarchar](1) NULL,
	[BORDRONO] [int] NOT NULL,
	[LISTNO] [nvarchar](5) NULL,
	[ISLEMNO] [int] NOT NULL,
	[TDATE] [datetime] NULL,
	[DOVIZTUT] [float] NULL,
	[DOVIZCINSI] [smallint] NOT NULL,
	[MUSKOD] [nvarchar](20) NOT NULL,
	[DOVIZKODU] [nvarchar](2) NULL,
	[OZELKOD] [nvarchar](3) NULL,
	[ISLEMTIPI] [nvarchar](30) NULL,
	[BELGENO] [nvarchar](10) NULL,
	[KULNO] [int] NOT NULL,
	[KULADI] [nvarchar](20) NULL,
	[SaticiKodu] [nvarchar](15) NULL,
	[SaticiAdi] [nvarchar](25) NULL,
	[CariOPlanGunu] [int] NOT NULL,
	[GunAy] [nvarchar](3) NULL,
	[OpsiyonGunu] [int] NOT NULL,
	[AcikKapali] [nvarchar](5) NULL,
	[DovizBorc] [float] NOT NULL,
	[DovizAlacak] [float] NOT NULL,
	[TP] [nvarchar](2) NULL,
	[VardiyaKodu] [nvarchar](15) NULL,
	[VardiyaDurumu] [nvarchar](9) NULL,
	[Departman] [nvarchar](8) NOT NULL,
	[IslemNo2] [int] NOT NULL,
	[KayitDuzeyi] [int] NOT NULL,
	[KayitDuzeyiNo] [int] NOT NULL,
	[IslemNo3] [int] NOT NULL,
	[Aciklama2] [nvarchar](200) NULL,
	[OnOdeme] [nvarchar](1) NOT NULL,
	[VadeTarihi] [smalldatetime] NULL,
	[IsIstasyonKodu] [nvarchar](30) NULL,
	[IsIstasyonAdi] [nvarchar](30) NULL,
	[SiraNo] [int] NOT NULL,
	[VardiyaNo] [nvarchar](8) NOT NULL,
	[PosSablonKodu] [nvarchar](15) NULL,
	[Update_Date] [datetime] NULL,
	[StokKodu] [nvarchar](30) NULL,
	[TTarihi] [smalldatetime] NULL,
	[TeslimEden] [nvarchar](80) NULL,
	[TeslimAlan] [nvarchar](80) NULL,
 CONSTRAINT [PK_AcariTakas] PRIMARY KEY CLUSTERED 
(
	[NoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((0)) FOR [BORC]


ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((0)) FOR [ALACAK]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((0)) FOR [BAKIYE]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((0)) FOR [BORDRONO]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((0)) FOR [DOVIZCINSI]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((1)) FOR [KULNO]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((0)) FOR [CariOPlanGunu]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((0)) FOR [OpsiyonGunu]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((0)) FOR [DovizBorc]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((0)) FOR [DovizAlacak]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ('01000000') FOR [Departman]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((0)) FOR [IslemNo2]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((1)) FOR [KayitDuzeyi]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((1)) FOR [KayitDuzeyiNo]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((0)) FOR [IslemNo3]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ('H') FOR [OnOdeme]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ((0)) FOR [SiraNo]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT ('20000000') FOR [VardiyaNo]

ALTER TABLE [dbo].[AcariTakas] ADD  DEFAULT (getdate()) FOR [Update_Date]
end

 --[***]--
  IF NOT EXISTS (SELECT *  FROM sys.tables WHERE  name='Resimler')
 BEGIN 
CREATE TABLE [dbo].[Resimler](
	[NoId] [int] IDENTITY(1,1) NOT NULL,
	[No] [int] NOT NULL,
	[ResimTipi] [int] NOT NULL,
	[SiraNo] [int] NULL,
	[WebYayini] [nvarchar](1) NULL,
	[DBImage] [image] NOT NULL,
	[KULNO] [int] NOT NULL,
	[Tarih] [datetime] NULL,
	[Varsayilan] [bit] NULL,
	[Aciklama] [nvarchar](200) NULL,
 CONSTRAINT [PK_Resimler] PRIMARY KEY CLUSTERED 
(
	[NoId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 

ALTER TABLE [dbo].[Resimler] ADD  CONSTRAINT [DF__Resimler__WebYay__39444182]  DEFAULT ('E') FOR [WebYayini]
 

ALTER TABLE [dbo].[Resimler] ADD  DEFAULT (getdate()) FOR [Tarih]
 

ALTER TABLE [dbo].[Resimler] ADD  DEFAULT ((0)) FOR [Varsayilan]
 
end

  --[***]--
if  exists (select * from sys.foreign_keys where name like 'FK_YazarKasalarMapKullanicilar_YazarKasa%')
begin
ALTER TABLE [dbo].[YazarKasalarMapKullanicilar] DROP CONSTRAINT FK_YazarKasalarMapKullanicilar_YazarKasalar
end

  --[***]--
ALTER TABLE [dbo].[YazarKasalarMapKullanicilar]  WITH CHECK ADD  CONSTRAINT [FK_YazarKasalarMapKullanicilar_YazarKasalar] FOREIGN KEY([YazarKasaID])
REFERENCES [dbo].[YazarKasalar] ([Id])

 
  --[***]--
 IF   EXISTS (SELECT *  FROM sys.views WHERE  name='KullanicilarVarsayilan_TipRapor')
 BEGIN 
 drop view KullanicilarVarsayilan_TipRapor
 end
 
  --[***]--
create  view [dbo].[KullanicilarVarsayilan_TipRapor]  
AS 
	select	Kul.[No]	as [KulNo], 
		Tip.Ad		as [Tip], 
		Rap.RaporIsmi	as [Rapor], 
		Rap.Onizleme,Rap.RaporNo as RaporNo,Tip.TipNo as TipNo, 
		convert(bit,(select case COUNT(*) when 0 then 0 else 1 end from dbo.splitstring(Rap.RaporSecimEkraniOnIzleme,',') where Name=Kul.No )) as RaporSecimEkraniOnIzleme, 
		Rap.Yazici 
	from	Kullanicilar as Kul 
		cross join	RaporTipTanimlari as Tip 
		left outer join	KullaniciVarsayilanRaporlari as K on Tip.TipNo=K.TipNo and Kul.[No] = K.KullaniciNo 
		left outer join Rapor as Rap on K.RaporNo = Rap.RaporNo 

 --[***]--
    
  declare @CNTNAME NVARCHAR(1000),@SQL NVARCHAR(MAX)
select @CNTNAME= name  FROM sys.objects where type_desc like '%FUNCT%' and name like '%splitstring%'
--name from sys.default_constraints where name like 'DF_STOKHARDETevki%'
SET @SQL='DROP FUNCTION [dbo].['+CONVERT(VARCHAR(1000),@CNTNAME)+']'
EXEC(@SQL)

 --[***]--
CREATE FUNCTION [dbo].[splitstring] ( @stringToSplit VARCHAR(MAX), @splitdet varchar(1)) 
RETURNS 
 @returnList TABLE ([Name] [nvarchar] (500)) 
AS 
BEGIN 
 
 DECLARE @name NVARCHAR(255) 
 DECLARE @pos INT 
 
 WHILE CHARINDEX(@splitdet, @stringToSplit) > 0 
 BEGIN 
  SELECT @pos  = CHARINDEX(@splitdet, @stringToSplit)   
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1) 
 
  INSERT INTO @returnList  
  SELECT @name 
 
  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos) 
 END 
 
 INSERT INTO @returnList 
 SELECT @stringToSplit 
 
 RETURN 
END 


--[***]----------------------
if exists (select * from sys.procedures where name='sp_mb_XML_InvoiceLine_List')
drop proc sp_mb_XML_InvoiceLine_List

--[***]----------------------
create procedure [dbo].[sp_mb_XML_InvoiceLine_List]
 @IslemNo nvarchar(30)
 as
SELECT
	ROW_NUMBER() OVER (ORDER BY LST.StokKodu ) "SiraNo"  ,
	LST.StokKodu,
	LST.StokAdi,
	LST.IskOran,  
	LST.Kdv,
	LST.Birim,
	LST.ISO_BIRIM,
	LST.MUAFIYET,
	LST.ToplamBirimFiyat,
	SUM(LST.Miktar) Miktar,
	SUM(LST.KdvsizSatirToplami) KdvsizSatirToplami,
	SUM(LST.SatirIskontoToplami) SatirIskontoToplami,
	SUM(LST.Isksuz_Satir_Toplami) Isksuz_Satir_Toplami,
	SUM(LST.KdvToplami) KdvToplami,
	SUM(LST.Alt_Iskonto_Toplami) Alt_Iskonto_Toplami,
	SUM(LST.Tutar) Tutar,
	SUM(LST.SonTutar) SonTutar, 
	LST.Iskonto_Aciklama
 FROM(
SELECT 
 
DD.StokKodu,
DD.StokAdi AS StokAdi,
(FaturaMiktari) as Miktar,
(SonTutar-KdvTutari)/(DovizKuru) as KdvsizSatirToplami,
DD.Iskonto1 AS IskOran,
((FaturaMiktari* BirimFiyat*BirimMiktar) -Tutar )/(DovizKuru) as SatirIskontoToplami,
(FaturaMiktari* BirimFiyat*BirimMiktar)/(DovizKuru) as Isksuz_Satir_Toplami,--tutar
 DD.Kdv,
(KdvTutari)/(DovizKuru) as KdvToplami,  
(Birim) as Birim,
(Tutar -(SonTutar-KdvTutari))/(DovizKuru) as Alt_Iskonto_Toplami, 
(Tutar)/((DovizKuru)) as Tutar,
(SonTutar)/((DovizKuru)) as SonTutar,
(DD.BirimFiyat*DD.BirimMiktar)/(DovizKuru) AS ToplamBirimFiyat,--Fiyat
CASE WHEN ISNULL (DD.Kdv,0)=0 THEN (SELECT Deger FROM Parametre WHERE Param='eFaturaMuafiyetAciklama') ELSE '' END AS  "MUAFIYET",
 ( SELECT IsoBirimKodu FROM BIRIM b where b.BirimAdi=DD.Birim  )   AS ISO_BIRIM,
 CASE WHEN DD.Iskonto1 >0 then '%'+CAST(DD.Iskonto1 as varchar(10))    end +
 CASE WHEN DD.Iskonto2 >0 then  '%'+CAST(DD.Iskonto2 as varchar(10))  else '' end+
 CASE WHEN DD.Iskonto3 >0 then  '%'+CAST(DD.Iskonto3 as varchar(10))  else '' end+
 CASE WHEN DD.Iskonto4 >0 then  '%'+CAST(DD.Iskonto4 as varchar(10))  else '' end as Iskonto_Aciklama

from STOKHAR DD  where (IslemTuru ='Satış' or IslemTuru ='Alış İade'  )
AND (DD.IslemNo=@IslemNo or DD.IslemNo3=@IslemNo)
AND Tutar >0 
)LST
GROUP BY 
LST.StokKodu,
LST.StokAdi,
LST.IskOran,
LST.Kdv,
LST.Birim,
LST.ISO_BIRIM,
LST.MUAFIYET,
LST.ToplamBirimFiyat,
Iskonto_Aciklama

--[***]----------------------
if exists (select * from sys.procedures where name='sp_mb_XML_InvoiceLine_List_delphi')
drop proc sp_mb_XML_InvoiceLine_List_delphi

--[***]----------------------

create proc [dbo].[sp_mb_XML_InvoiceLine_List_delphi] @IslemNo nvarchar(30)  
as
;with stokdetbag AS (SELECT DB.IslemNo,
CASE WHEN ISNULL (DD.Kdv,0)=0 AND DB.OzelKod1='31' AND DB.YazarKasaFisNo<>2 THEN    '351;Vergiden muaf.'  
WHEN  ISNULL (DD.Kdv,0)=0 AND DB.OzelKod1='31' AND DB.YazarKasaFisNo=2 THEN   '11/1-a Mal ihracatı' 
when SUBSTRING(ISNULL(DB.OzelKod1,'00'), 1, 1)=3 and ISNULL (DD.Kdv,0)=0 and DB.EF_ISTISNA=0  then '701;11/1-c md. Kapsamındaki İhraç Kayıtlı Satış'    
when DD.TopTutar=0 and DB.EF_ISTISNA=0 then '351;Kısmibedelsiz' 
 WHEN ISNULL (DD.Kdv,0)=0 AND (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA and MuafiyetKodu<>'000')=';'  THEN   
    '351;Vergiden muaf.' 
	WHEN ISNULL (DD.Kdv,0)=0 AND (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA and MuafiyetKodu<>'000')<>';'  THEN 
  (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA) ELSE ''    END 
    AS  "MUAFIYET"
  from  STOKHARDETBAG DB 
  join STOKHAR DD ON  DB.IslemNo in (DD.IslemNo,DD.IslemNo3) 
  WHERE @IslemNo in (DD.IslemNo, DD.IslemNo3))
  
SELECT    
 ROW_NUMBER() OVER (ORDER BY LST.StokKodu ) "SiraNo"  ,    
 LST.StokKodu,    
 LST.StokAdi,    
 LST.IskOran,  --LST.IskOran,    
 LST.Kdv,    
 LST.Birim,    
 LST.ISO_BIRIM,    
 MAX(LST.MUAFIYET) "MUAFIYET",    
 round((LST.ToplamBirimFiyat),4)ToplamBirimFiyat,    
 SUM(LST.Miktar) Miktar,    
 round(SUM(LST.KdvsizSatirToplami) ,2)KdvsizSatirToplami,    
 round(SUM(LST.SatirIskontoToplami),2) SatirIskontoToplami,    
 round(SUM(LST.Isksuz_Satir_Toplami),2) Isksuz_Satir_Toplami,    
 round(SUM(LST.KdvToplami) ,2) KdvToplami,
 round(SUM(LST.Alt_Iskonto_Toplami),2) Alt_Iskonto_Toplami,    
 round(SUM(LST.Tutar),2) Tutar,    
 round(SUM(LST.SonTutar),2) SonTutar,     
 LST.Iskonto_Aciklama,    
 round(LST.OTVOrani,2) OTVOrani,    
 round(sum(LST.OTVsizSatirToplami),2) OTVsizSatirToplami,    
 round(sum(LST.OtvToplami),2) OtvToplami    
    
 FROM(    
SELECT     
     
DD.StokKodu,    
DD.StokAdi AS StokAdi,    
(FaturaMiktari) as Miktar,    
(SonTutar-DD.KdvTutari)/ (DovizKuru) as KdvsizSatirToplami,    
(SonTutar-DD.OTVTutari) /(DovizKuru) as OTVsizSatirToplami,    
DD.Iskonto1 AS IskOran,    
((FaturaMiktari* BirimFiyat*BirimMiktar) -DD.Tutar ) /(DovizKuru) as SatirIskontoToplami,    
(FaturaMiktari* BirimFiyat*BirimMiktar) /(DovizKuru) as Isksuz_Satir_Toplami,--tutar    
 DD.Kdv,    
 DD.OTVOrani,    
(DD.KdvTutari) / (DovizKuru) as KdvToplami,      
(DD.OTVTutari) / (DovizKuru) as OtvToplami,      
(Birim) as Birim,    
(DD.Tutar -(SonTutar-DD.KdvTutari)) / (DovizKuru) as Alt_Iskonto_Toplami,     
(DD.Tutar) / DovizKuru as Tutar,    
(SonTutar) / DovizKuru as SonTutar,    
(DD.BirimFiyat*DD.BirimMiktar) / (DovizKuru) AS ToplamBirimFiyat,--Fiyat    
(SELECT  Top 1  MUAFIYET  FROM stokdetbag b  where b.IslemNo in (DD.IslemNo, DD.IslemNo3) and isnull(MUAFIYET,'')<>''  )
 AS  "MUAFIYET",    
 ( SELECT IsoBirimKodu FROM BIRIM b where b.BirimAdi=DD.Birim  )   AS ISO_BIRIM,    
 CASE WHEN DD.Iskonto1 >0 then '%'+CAST(DD.Iskonto1 as varchar(10))    end +    
 CASE WHEN DD.Iskonto2 >0 then  '%'+CAST(DD.Iskonto2 as varchar(10))  else '' end+    
 CASE WHEN DD.Iskonto3 >0 then  '%'+CAST(DD.Iskonto3 as varchar(10))  else '' end+    
 CASE WHEN DD.Iskonto4 >0 then  '%'+CAST(DD.Iskonto4 as varchar(10))  else '' end as "Iskonto_Aciklama"    
    
from STOKHAR DD     
  --CROSS APPLY STOKHARDETBAG DB ON   DB.IslemNo in (DD.IslemNo,DD.IslemNo3) 
 where (DD.IslemTuru  in ('Satış' ,'Alış İade'  )    
AND @IslemNo in (DD.IslemNo, DD.IslemNo3))    
--AND DD.Tutar >0     
)LST    
GROUP BY     
LST.StokKodu,    
LST.StokAdi,    
LST.IskOran,   
LST.Kdv,    
LST.Birim,    
LST.ISO_BIRIM,       
LST.ToplamBirimFiyat,    
Iskonto_Aciklama,    
LST.OTVOrani;

--[***]----------------------
if exists (select * from sys.procedures where name='sp_mb_XML_InvoiceLine_List_Grup')
drop proc sp_mb_XML_InvoiceLine_List_Grup

--[***]----------------------

create procedure [dbo].[sp_mb_XML_InvoiceLine_List_Grup] -- @IslemNo  
 @IslemNo nvarchar(30)    
 AS      
 SELECT    
 ROW_NUMBER() OVER (ORDER BY LST.StokKodu ) "SiraNo"  ,    
 LST.StokKodu,    
 LST.StokAdi,    
 LST.IskOran,      
 LST.Kdv,    
 LST.Birim,    
 LST.ISO_BIRIM,    
 LST.MUAFIYET,    
 SUM(LST.Tutar)/SUM(LST.Miktar) ToplamBirimFiyat,    
 SUM(LST.Miktar) Miktar,    
 SUM(LST.KdvsizSatirToplami) KdvsizSatirToplami,    
 SUM(LST.SatirIskontoToplami) SatirIskontoToplami,    
 SUM(LST.Isksuz_Satir_Toplami) Isksuz_Satir_Toplami,    
 SUM(LST.KdvToplami) KdvToplami,    
 SUM(LST.Alt_Iskonto_Toplami) Alt_Iskonto_Toplami,    
 SUM(LST.Tutar) Tutar,    
 SUM(LST.SonTutar) SonTutar,     
 LST.Iskonto_Aciklama,  
  MAX(LST.OTVOrani) OTVOrani,      
 sum(LST.OTVsizSatirToplami) OTVsizSatirToplami,      
 sum(LST.OtvToplami) OtvToplami      
 FROM(    
SELECT     
     
DD.StokKodu,    
DD.StokAdi AS StokAdi,    
sum (FaturaMiktari) as Miktar,    
sum ((SonTutar-DD.KdvTutari) / (DovizKuru)) as KdvsizSatirToplami,    
DD.Iskonto1 AS IskOran,    
sum (((FaturaMiktari* BirimFiyat*BirimMiktar) -DD.Tutar ) / (DovizKuru) ) as SatirIskontoToplami,    
sum(((FaturaMiktari* BirimFiyat*BirimMiktar) / (DovizKuru) )) as Isksuz_Satir_Toplami,--tutar    
 DD.Kdv,    
sum ((DD.KdvTutari) / (DovizKuru)) as KdvToplami,      
(Birim) as Birim,    
(SUM((DD.SonTutar-DD.OTVTutari)/ DovizKuru)) as OTVsizSatirToplami,     
sum ((DD.Tutar -(SonTutar-DD.KdvTutari)) /(DovizKuru)) as Alt_Iskonto_Toplami,     
sum ((DD.Tutar) /DovizKuru) as Tutar,    
sum ((SonTutar) / DovizKuru) as SonTutar,    
sum ((DD.BirimFiyat*DD.BirimMiktar) / DovizKuru) AS ToplamBirimFiyat,--Fiyat    
CASE WHEN ISNULL (DD.Kdv,0)=0 AND MAX(DB.OzelKod1)='31' AND MAX(DB.YazarKasaFisNo)<>2 THEN    '351;Vergiden muaf.'    
WHEN  ISNULL (DD.Kdv,0)=0 AND MAX(DB.OzelKod1)='31' AND MAX(DB.YazarKasaFisNo)=2 THEN   '11/1-a Mal ihracatı'   
when SUBSTRING(ISNULL(MAX(DB.OzelKod1),'00'), 1, 1)=3 and ISNULL (DD.Kdv,0)=0 and DB.EF_ISTISNA=0  then '701;11/1-c md. Kapsamındaki İhraç Kayıtlı Satış'      
when SUM(DD.TopTutar)=0 and MAX(DB.EF_ISTISNA)=0 then '351;Kısmibedelsiz'   
 WHEN ISNULL (DD.Kdv,0)=0 AND (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA and MuafiyetKodu<>'000')=';'  THEN     
    '351;Vergiden muaf.'   
 WHEN ISNULL (DD.Kdv,0)=0 AND (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA and MuafiyetKodu<>'000')<>';'  THEN   
  (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA) ELSE ''    END  AS  "MUAFIYET",  
 ( SELECT IsoBirimKodu FROM BIRIM b where b.BirimAdi=DD.Birim  )   AS ISO_BIRIM,    
 CASE WHEN DD.Iskonto1 >0 then '%'+CAST(DD.Iskonto1 as varchar(10))    end +    
 CASE WHEN DD.Iskonto2 >0 then  '%'+CAST(DD.Iskonto2 as varchar(10))  else '' end+    
 CASE WHEN DD.Iskonto3 >0 then  '%'+CAST(DD.Iskonto3 as varchar(10))  else '' end+    
 CASE WHEN DD.Iskonto4 >0 then  '%'+CAST(DD.Iskonto4 as varchar(10))  else '' end as Iskonto_Aciklama,  
  DD.OTVOrani,  
  SUM((DD.OTVTutari)* CASE WHEN TopTutar>0 THEN (DD.DovizTut/TopTutar) ELSE 0 END) as OtvToplami      
    
from STOKHAR DD    
 JOIN STOKHARDETBAG DB ON  DB.IslemNo in (DD.IslemNo,DD.IslemNo3)
where (DD.IslemTuru in ('Satış','Alış İade'  ) )   
AND (@IslemNo in (DD.IslemNo3,DD.IslemNo)) 
AND DD.Tutar >0   group by StokKodu,StokAdi,Iskonto1,Kdv,Birim,Iskonto2,Iskonto3,Iskonto4,EF_ISTISNA,DD.OTVOrani  
)LST    
GROUP BY     
LST.StokKodu,    
LST.StokAdi,    
LST.IskOran,    
LST.Kdv,    
LST.Birim,    
LST.ISO_BIRIM,    
LST.MUAFIYET,    
LST.ToplamBirimFiyat,    
Iskonto_Aciklama

--[***]----------------------
if exists (select * from sys.procedures where name='sp_mb_XML_StokHarDurumUpdateUUID')
drop proc sp_mb_XML_StokHarDurumUpdateUUID

--[***]----------------------
CREATE  PROCEDURE [dbo].[sp_mb_XML_StokHarDurumUpdateUUID]    
 @eFaturaDurumu nvarchar(20),  
 @UUID nvarchar(60),  
 @eFaturaNo nvarchar(20)  
AS   
declare @IslemNo int;

Select @IslemNo = IslemNo FRom STOKHARDETBAG where UUID=@UUID

UPDATE STOKHARDETBAG  
   SET eFaturaDurumu=@eFaturaDurumu,  
       eFaturaNo=  @eFaturaNo  
 WHERE UUID=@UUID;

UPDATE ACARI 
   SET ACIKLAMA = @eFaturaNo + ' no.lu Fatura' 
 WHERE ISLEMNO = @IslemNo;
 
 --[***]----------------------
if exists (select * from sys.procedures where name='sp_mb_XML_TaxSubtotalType_List')
drop proc sp_mb_XML_TaxSubtotalType_List

--[***]----------------------
create   PROCEDURE  [dbo].[sp_mb_XML_TaxSubtotalType_List]  
 @IslemNo nvarchar(30)  
 AS   
  
select   
round(STK.KdvToplami,2) AS "TaxAmount",  
round(STK.SonTutar-STK.KdvToplami,2) AS "TaxableAmount",   
STK.sayi AS "CalculationSequenceNumeric",   
Kdv as "KDV_ORANI" ,   
CASE WHEN ISNULL (Kdv,0)=0 THEN (SELECT Deger FROM Parametre WHERE Param='eFaturaMuafiyetAciklama')   
when STK.Tutar=0  then '351;KısmiBeldesiz'   
ELSE '' END AS  "MUAFIYET"  
 FROM  
(SELECT   
sum(1) as sayi,  
sum (FaturaMiktari* BirimFiyat*BirimMiktar* CASE WHEN DovizKuru>1 THEN ((STK.DovizTut/TopTutar)) ELSE 1 END) as Isksuz_Satir_Toplami,  
sum (((FaturaMiktari* BirimFiyat*BirimMiktar) -Tutar)* CASE WHEN DovizKuru>1 THEN ((STK.DovizTut/TopTutar)) ELSE 1 END  ) as SatirIskontoToplami,  
cast( sum (((Tutar -(SonTutar-KdvTutari))* CASE WHEN DovizKuru>1 THEN ((STK.DovizTut/TopTutar)) ELSE 1 END )) as decimal(15,2)) as Alt_Iskonto_Toplami,   
sum ((SonTutar-KdvTutari)*CASE WHEN DovizKuru>1 THEN ((STK.DovizTut/TopTutar)) ELSE 1 END) as KdvsizSatirToplami,  
sum (KdvTutari*CASE WHEN DovizKuru>1 THEN ((STK.DovizTut/TopTutar)) ELSE 1 END) as KdvToplami,    
sum(Tutar*CASE WHEN DovizKuru>1 THEN ((STK.DovizTut/TopTutar)) ELSE 1 END) as Tutar,  
sum(SonTutar*CASE WHEN DovizKuru>1 THEN ((STK.DovizTut/TopTutar)) ELSE 1 END) as SonTutar,  
Kdv    
 from STOKHAR STK  where (IslemTuru ='Satış' or IslemTuru ='Alış İade'  )  
  AND  (STK.IslemNo=@IslemNo or STK.IslemNo3=@IslemNo)  
 -- AND Tutar>0  
 group by Kdv,OTVOrani) STK;  
 
  --[***]----------------------
if exists (select * from sys.procedures where name='spStokHarDetBagKaydet')
drop proc spStokHarDetBagKaydet

--[***]----------------------

CREATE  PROCEDURE [dbo].[spStokHarDetBagKaydet]            
@IslemNo int,           
@IslemTuru nvarchar(10),           
@CariNo int,           
@CariKodu nvarchar(20),           
@CariAdi nvarchar(50),           
@Tutar float,           
@SiparisTarihi datetime,           
@KdvTutari float,           
@FisTuru nvarchar(1),           
@GC nvarchar(1),           
@EkIskTut1 float,           
@EkIskTut2 float,           
@EkIskTut3 float,           
@EkIskTutTop float,           
@FaturaNo int,           
@FaturaTarihi datetime,           
@Satici nvarchar(6),           
@DovizTut float,           
@DovizCinsi smallint,           
@DovizKodu nvarchar(2),           
@DovizTutB float,           
@BelgeNo nvarchar(10),           
@OzelKod1 nvarchar(3),           
@OzelKod2 nvarchar(3),           
@OzelKod3 nvarchar(3),           
@IslemTipi nvarchar(30),           
@IrsNo nvarchar(13),           
@Ack1 nvarchar(200),           
@Ack2 nvarchar(200),           
@Ack3 nvarchar(200),           
@Ack4 nvarchar(200),           
@KULNO int,           
@KULADI nvarchar(20),           
@FatSerNo varchar(3),           
@SaticiAdi nvarchar(25),           
@AltIskYuzde1 float,           
@AltIskYuzde2 float,           
@AltIskYuzde3 float,           
@AK nvarchar(3),           
@FaturaOPlanGunu int,           
@Depo nvarchar(30),           
@GunAy nvarchar(3),           
@OpsiyonGunu int,           
@AcikKapali nvarchar(3),           
@DovizBorc float,           
@DovizAlacak float,           
@FYaz nvarchar(1),           
@SecimEH nvarchar(1),           
@IslemNo3 int,           
@TP nvarchar(2),           
@FisNo nvarchar(9),           
@VardiyaKodu nvarchar(15),           
@VardiyaDurumu nvarchar(9),           
@Departman nvarchar(8),           
@MasrafToplami float,           
@KDVDH char,           
@IslemNo4 int,           
@KayitDuzeyi int,           
@YazarKasaNo int,           
@YazarKasaFisNo nvarchar(10),           
@YaziciCount float,           
@IsIstasyonKodu nvarchar(30),           
@IsIstasyonAdi nvarchar(30),                
@TevkifatTutari float,           
@VardiyaNo nvarchar(8),           
@OdemeBilgisi nvarchar(100),           
@FaturaAdresNo int,           
@SevkAdresNo int,           
@FI nvarchar(1),           
@SevkTarihi dateTime,           
@eFaturaNo nvarchar(30),           
@UUID nVarChar(50),           
@eFaturaGonderimTarihi DateTime,           
@eFaturaDurumu nVarChar(20) ,              
@FaturaTipi int,        
@DovizKuru float = 1        
           
 AS           
 begin           
 declare @Istisna int;          
 declare @HesaplananTutar float          
 declare @HesaplananKdvTutari float          
           
            
 if (@FisTuru='I')           
 begin           
 set @HesaplananTutar = (select sum(SonTutar) from STOKHAR where CariNo=@CariNo and (IslemNo=@IslemNo))          
 set @HesaplananKdvTutari = (select sum(KdvTutari) from STOKHAR where CariNo=@CariNo and (IslemNo=@IslemNo) )          
 end else           
 begin           
 set @HesaplananTutar = (select sum(SonTutar) from STOKHAR where CariNo=@CariNo and (IslemNo3=@IslemNo or IslemNo=@IslemNo) )          
 set @HesaplananKdvTutari = (select sum(KdvTutari) from STOKHAR where CariNo=@CariNo and (IslemNo3=@IslemNo or IslemNo=@IslemNo) )          
    
 end           
           
           
 if(@IslemTuru='DepoVirman')           
 begin           
 set @HesaplananTutar =@HesaplananTutar/2           
 set @HesaplananKdvTutari=@HesaplananKdvTutari/2           
           
 end           
 set @Istisna=(select convert(int,(SELECT top 1 v.MuafiyetKodu FROM STOKHAR sh join StokVergiMuafiyetleri v on v.StokKodu=sh.StokKodu WHERE IslemNo=@IslemNo and Kdv=0)))          
           
select @TevkifatTutari =  SUM(TevkifatTutari) from Tevkifat t join STOKHAR h on @IslemNo in(h.IslemNo,h.IslemNo3)    
    
if @TevkifatTutari > 0    
begin    
-- set @HesaplananKdvTutari = @HesaplananKdvTutari - @TevkifatTutari    
 set @HesaplananTutar = @HesaplananTutar - @TevkifatTutari    
end    
    
 INSERT STOKHARDETBAG (IslemNo, IslemTuru, CariNo, CariKodu, CariAdi, Tutar, SiparisTarihi, KdvTutari, FisTuru, GC, EkIskTut1, EkIskTut2, EkIskTut3, EkIskTutTop, FaturaNo, FaturaTarihi, Satici, DovizTut, DovizCinsi, DovizKodu, DovizTutB, BelgeNo,         
  
        OzelKod1, OzelKod2, OzelKod3, IslemTipi, IrsNo, Ack1, Ack2, Ack3, Ack4, KULNO, KULADI, FatSerNo, SaticiAdi, AltIskYuzde1, AltIskYuzde2, AltIskYuzde3, AK, FaturaOPlanGunu, Depo, GunAy, OpsiyonGunu, AcikKapali,           
        DovizBorc, DovizAlacak, FYaz, SecimEH, IslemNo3, TP, FisNo, VardiyaKodu, VardiyaDurumu, Departman, MasrafToplami, KDVHD, IslemNo4, KayitDuzeyi, YazarKasaNo, YazarKasaFisNo, YaziciCount, IsIstasyonKodu,           
        IsIstasyonAdi, VardiyaNo, OdemeBilgisi, FaturaAdresNo, SevkAdresNo, FI, SevkTarihi, eFaturaNo, UUID, eFaturaGonderimTarihi,      
  eFaturaDurumu, EF_ISTISNA, FaturaTipi,           
        Entegrator, Update_Date,DovizKur,TevkifatTutari)           
 VALUES     (@IslemNo, @IslemTuru,@CariNo,@CariKodu,@CariAdi,@HesaplananTutar ,           
       @SiparisTarihi,@HesaplananKdvTutari ,@FisTuru, @GC, @EkIskTut1, @EkIskTut2, @EkIskTut3,@EkIskTutTop, @FaturaNo, @FaturaTarihi,  @Satici,  @DovizTut,  @DovizCinsi,  @DovizKodu,  @DovizTutB,  @BelgeNo,    @OzelKod1,            
       @OzelKod2,  @OzelKod3,  @IslemTipi,  @IrsNo,  @Ack1,  @Ack2,  @Ack3,  @Ack4,  @KULNO,  @KULADI,  @FatSerNo,  @SaticiAdi, @AltIskYuzde1,           
       @AltIskYuzde2,  @AltIskYuzde3,  @AK,  @FaturaOPlanGunu,  @Depo, @GunAy,  @OpsiyonGunu,  @AcikKapali,   @DovizBorc,  @DovizAlacak,  @FYaz,           
       @SecimEH,  @IslemNo3,  @TP,  @FisNo,  @VardiyaKodu,  @VardiyaDurumu, @Departman,  @MasrafToplami,  @KDVDH,  @IslemNo4,           
       @KayitDuzeyi,  @YazarKasaNo,  @YazarKasaFisNo,  @YaziciCount, @IsIstasyonKodu,   @IsIstasyonAdi,            
       @VardiyaNo,  @OdemeBilgisi,  @FaturaAdresNo,  @SevkAdresNo,  @FI,  @SevkTarihi,  @eFaturaNo,  @UUID,  @eFaturaGonderimTarihi,            
       @eFaturaDurumu ,  @Istisna,  @FaturaTipi,  0,GETDATE(),@DovizKuru, @TevkifatTutari     
)          
          
END



  --[***]----------------------
if exists (select * from sys.procedures where name='sp_STOKHARDETBAG_Update')
drop proc sp_STOKHARDETBAG_Update

--[***]----------------------
	
 CREATE    procedure [dbo].[sp_STOKHARDETBAG_Update](                    
           @IslemNo int,@IslemTuru nvarchar(10),@CariNo int,@CariKodu nvarchar(20),                    
           @CariAdi nvarchar(50),@Tutar nvarchar(50),@SiparisTarihi smalldatetime,@KdvTutari nvarchar(50),                    
           @FisTuru nvarchar(1),@GC nvarchar(1),@EkIskTut1 nvarchar(50),@EkIskTut2 nvarchar(50),@EkIskTut3 nvarchar(50),                    
           @EkIskTutTop nvarchar(50),@FaturaNo int,@FaturaTarihi smalldatetime,@Satici nvarchar(6),                    
           @DovizTut nvarchar(50),@DovizCinsi smallint,@DovizKodu nvarchar(2),@DovizTutB nvarchar(50),                    
           @BelgeNo nvarchar(10),@OzelKod1 nvarchar(3),@OzelKod2 nvarchar(3),@OzelKod3 nvarchar(3),                    
           @IslemTipi nvarchar(30),@IrsNo nvarchar(13),@Ack1 nvarchar(200),@Ack2 nvarchar(200),                    
           @Ack3 nvarchar(200),@Ack4 nvarchar(200),@KULNO int,@KULADI nvarchar(20),@FatSerNo nvarchar(3),                    
           @SaticiAdi nvarchar(25),@AltIskYuzde1 nvarchar(50),@AltIskYuzde2 nvarchar(50),@AltIskYuzde3 nvarchar(50),                    
           @AK nvarchar(3),@FaturaOPlanGunu int,@Depo nvarchar(30),@GunAy nvarchar(3),@OpsiyonGunu int,                    
           @AcikKapali nvarchar(3),@DovizBorc nvarchar(50),@DovizAlacak nvarchar(50),@FYaz nvarchar(1),                    
           @SecimEH nvarchar(1),@IslemNo3 int,@TP nvarchar(2),@FisNo nvarchar(9),@VardiyaKodu nvarchar(15),                    
           @VardiyaDurumu nvarchar(9),@Departman nvarchar(8),@MasrafToplami nvarchar(50),@KDVHD char(1),                    
           @IslemNo4 int,@KayitDuzeyi int,@YazarKasaNo int,@YazarKasaFisNo nvarchar(10),                    
           @YaziciCount nvarchar(50),@IsIstasyonKodu nvarchar(30),@IsIstasyonAdi nvarchar(30),                    
           @TevkifatTutari nvarchar(50),@VardiyaNo nvarchar(8),@OdemeBilgisi nvarchar(100),@FaturaAdresNo int,                    
           @SevkAdresNo int,@FI nvarchar(1),@EF_ISTISNA int,@FaturaTipi int,@DovizKuru float = 1,@eFaturaNo nvarchar(25) = '')                    
AS                    
             
--SET @DovizKuru = (Select ISNULL(CASE WHEN (CAST(@Tutar as float) = 0) or (CAST(@DovizTut  as float) = 0)  THEN 1 ELSE (CAST(@Tutar as float))  / (CAST(@DovizTut  as float)) END,1) from STOKHARDETBAG where IslemNo = @IslemNo)        
          
declare @TutarYeni float                  
declare @KdvTutariYeni float                  
declare @DovizTutYeni float                  
declare @MUSNO int                  
declare @MUSKOD nvarchar(20)                
declare @MUSADI nvarchar(50)                       
                
declare @DovizBorcYeni float                  
declare @DovizAlacakYeni float       
    
declare @YeniTevkifatTut float    
    
SET @YeniTevkifatTut = ISNULL((Select SUM(TevkifatTutari) from Tevkifat t where t.IslemNo = @IslemNo),0)    
                
 print(@FisTuru)                
 if (@FisTuru='I')                
 begin                
 print('Irsaliye')                
 set @TutarYeni=(select sum(SonTutar) - @YeniTevkifatTut from STOKHAR where STOKHAR.IslemNo=@IslemNo  )                  
 set @KdvTutariYeni=(select sum(KdvTutari) from STOKHAR where STOKHAR.IslemNo=@IslemNo )                  
 set @DovizTutYeni=( @TutarYeni/ convert(float,@DovizKuru) )                  
 end           
 else                
 begin                
 print('diğer')                
 set @TutarYeni=(select sum(SonTutar) - @YeniTevkifatTut from STOKHAR where STOKHAR.IslemNo=@IslemNo  or STOKHAR.IslemNo3=@IslemNo)                  
 set @KdvTutariYeni=(select sum(KdvTutari) from STOKHAR where STOKHAR.IslemNo=@IslemNo  or STOKHAR.IslemNo3=@IslemNo)                  
 set @DovizTutYeni=( @TutarYeni/ convert(float,@DovizKuru) )                  
 end                          
          
 if(@IslemTuru='DepoVirman')                
 begin                
 set @TutarYeni =@TutarYeni/2            
 set @KdvTutariYeni=@KdvTutariYeni/2                
 set @DovizTutYeni=@DovizTutYeni/2                      
 end                
              
                 
IF (select COUNT(*) from (select Count(CariKodu) c from STOKHAR where STOKHAR.IslemNo=@IslemNo  or STOKHAR.IslemNo3=@IslemNo GROUP BY CariKodu) t) > 1              
BEGIN       
 RAISERROR ('Kayıtlarda cari farkı vardır. Kontrol ediniz..!',10,1)          
 RETURN 0          
END              
          
select @MUSNO=MUSNO,@MUSKOD=MUSKOD,@MUSADI=MUSADI from AMUSTERI where MUSKOD in (select CariKodu from STOKHAR where STOKHAR.IslemNo=@IslemNo  or STOKHAR.IslemNo3=@IslemNo)                    
          
UPDATE    STOKHARDETBAG                    
SET              IslemTuru = @IslemTuru, CariNo = @MUSNO, CariKodu = @MUSKOD, CariAdi = @MUSADI,                     
Tutar =  @TutarYeni , SiparisTarihi = @SiparisTarihi, KdvTutari = @KdvTutariYeni,                     
FisTuru = @FisTuru, GC = @GC, EkIskTut1 = convert(float,@EkIskTut1), EkIskTut2 = convert(float,@EkIskTut2),                     
EkIskTut3 = convert(float,@EkIskTut3),                     
EkIskTutTop = convert(float,@EkIskTutTop),                     
FaturaNo = @FaturaNo, FaturaTarihi = @FaturaTarihi, Satici = @Satici, DovizTut = @DovizTutYeni,                     
DovizCinsi = @DovizCinsi, DovizKodu = @DovizKodu, DovizTutB =@DovizTutYeni/* convert(float,@DovizTutB)*/,                    
BelgeNo = @BelgeNo, OzelKod1 = @OzelKod1, OzelKod2 = @OzelKod2, OzelKod3 = @OzelKod3, IslemTipi = @IslemTipi, IrsNo = @IrsNo,                     
Ack1 = @Ack1, Ack2 = @Ack2, Ack3 = @Ack3, Ack4 = @Ack4,                     
KULNO = @KULNO, KULADI = @KULADI, FatSerNo = @FatSerNo, SaticiAdi = @SaticiAdi,                     
AltIskYuzde1 = convert(float,@AltIskYuzde1), AltIskYuzde2 = convert(float,@AltIskYuzde2), AltIskYuzde3 = convert(float,@AltIskYuzde3),                    
AK = @AK, FaturaOPlanGunu = @FaturaOPlanGunu, Depo = @Depo,                     
GunAy = @GunAy, OpsiyonGunu = @OpsiyonGunu, AcikKapali = @AcikKapali,                     
DovizBorc = convert(float,@DovizBorc), DovizAlacak = convert(float,@DovizAlacak), FYaz = @FYaz, SecimEH = @SecimEH,                     
IslemNo3 = @IslemNo3, TP = @TP, FisNo = @FisNo, VardiyaKodu = @VardiyaKodu, VardiyaDurumu = @VardiyaDurumu,                     
Departman = @Departman,MasrafToplami = convert(float,@MasrafToplami), KDVHD = @KDVHD, IslemNo4 = @IslemNo4, KayitDuzeyi = @KayitDuzeyi, YazarKasaNo = @YazarKasaNo, YazarKasaFisNo = @YazarKasaFisNo, YaziciCount = convert(float,@YaziciCount),              
   
     
IsIstasyonKodu = @IsIstasyonKodu, IsIstasyonAdi = @IsIstasyonAdi,                    
TevkifatTutari = (Select SUM(TevkifatTutari) from Tevkifat t join STOKHAR h on @IslemNo in(h.IslemNo,h.IslemNo3) ) ,     
VardiyaNo = @VardiyaNo, OdemeBilgisi = @OdemeBilgisi, FaturaAdresNo = @FaturaAdresNo, SevkAdresNo = @SevkAdresNo, FI = @FI,                    
EF_ISTISNA=@EF_IsTISNA,FaturaTipi=@FaturaTipi,Update_Date = GETDATE() ,      
DovizKur = @DovizKuru ,eFaturaNo =  @eFaturaNo            
where IslemNo =@IslemNo           
          
DECLARE @CariSayisi int          
          
SET @CariSayisi = ( SELECT   COUNT(*) FROM STOKHAR S,STOKHARDETBAG B          
      WHERE (S.IslemNo = B.IslemNo or S.IslemNo3 = B.IslemNo)          
        AND B.IslemNo = @IslemNo          
        AND (S.CariNo <> B.CariNo or S.CariKodu <> B.CariKodu))          
          
IF @CariSayisi > 0              
BEGIN          
 RAISERROR ('Cari hatası. Kontrol ediniz..!',10,1)          
 RETURN 0          
END 

  --[***]----------------------
if exists (select * from sys.procedures where name='sp_mb_XML_TaxLegalMonetary_List')
drop proc sp_mb_XML_TaxLegalMonetary_List

--[***]----------------------
    
CREATE   procEDURE  [dbo].[sp_mb_XML_TaxLegalMonetary_List]        
 @IslemNo nvarchar(30)        
 as    
 ;with mustahsil as (    
select isnull(sum(DovizTut),0) MustahsilTut from STOKHAR where IslemTuru='Müstahsil' and  IslemNo=@IslemNo     
)       
select      
     
round(STK.KdvToplami,2) AS "TaxAmount",        
round(STK.SonTutar,2) AS "TaxableAmount",        
--round(STK.KdvToplami,2)) AS "TaxAmount",        
STK.sayi AS "CalculationSequenceNumeric",        
round(STK.Isksuz_Satir_Toplami,2) AS "LineExtensionAmount",        
(round(STK.KdvsizSatirToplami,2)) AS "TaxExclusiveAmount",        
(round(STK.SonTutar,2)) AS "TaxInclusiveAmount",        
(round(STK.SatirIskontoToplami+Alt_Iskonto_Toplami,2)) AS "AllowanceTotalAmount",        
(round(STK.SonTutar,2)-(select MustahsilTut from mustahsil )) AS "PayableAmount",        
isnull(TevkifatKod,'') as TevkifatKod,        
isnull(TevkifatAd,'') as TevkifatAd,isnull(TevkifatOrani,0) TevkifatOrani,isnull(TevkifatTutari,0) TevkifatTutari        
      
 FROM        
(SELECT         
sum(1) as sayi,        
sum (FaturaMiktari* BirimFiyat*BirimMiktar*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END) as Isksuz_Satir_Toplami,        
sum ( CASE WHEN ((FaturaMiktari* BirimFiyat*BirimMiktar) -STK.Tutar) <0.1 THEN 0 ELSE ((FaturaMiktari* BirimFiyat*BirimMiktar) -STK.Tutar)*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END END  ) as SatirIskontoToplami,        
cast( sum (CASE WHEN (STK.Tutar -(SonTutar-STK.KdvTutari) )*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END <0.1 THEN 0 ELSE (STK.Tutar -(SonTutar-STK.KdvTutari) )*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END END ) as decimal(
  
15,2)) as Alt_Iskonto_Toplami,         
sum ((SonTutar-STK.KdvTutari)*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END) as KdvsizSatirToplami,        
sum (STK.KdvTutari*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END) as KdvToplami,          
sum(STK.Tutar*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END)-sum(isnull(t.TevkifatTutari,0)) as Tutar,        
sum(SonTutar*CASE WHEN TopTutar>0 THEN (STK.DovizTut/TopTutar) ELSE 0 END)-sum(isnull(t.TevkifatTutari,0)) as SonTutar,    
max(t.Kod) TevkifatKod,max(tb.TevkifatAdi) TevkifatAd,convert(decimal, max(t.TevkifatOrani)) TevkifatOrani,sum(t.TevkifatTutari) TevkifatTutari        
 from STOKHAR STK        
  left join Tevkifat t on t.IslemNo in (STK.IslemNo,STK.IslemNo3)  and t.StokNo=STK.StokNo    
  left join tblTevkifat tb on tb.Kod=t.Kod    
 join STOKHARDETBAG STB ON STB.IslemNo=STK.IslemNo where (STK.IslemTuru ='Satış' or STK.IslemTuru ='Alış İade'  )        
  AND (STK.IslemNo=@IslemNo or STK.IslemNo3=@IslemNo)        
  --AND STK.Tutar>0    
   ) STK;      
  
   --  
  
   --[sp_mb_XML_TaxLegalMonetary_List] 931
   
   
  --[***]----------------------
if exists (select * from sys.procedures where name='sp_mb_XML_Customer_List_delphi')
drop proc sp_mb_XML_Customer_List_delphi

--[***]----------------------
create  procEDURE [dbo].[sp_mb_XML_Customer_List_delphi]              
 @IslemNo nvarchar(30)              
 AS               
              
--- 2022-10-26              
SELECT top 1                
 Adres.VergiNo as "VERGI_NO",              
 Adres.VergiDairesi AS "VERGI_DAIRESI",              
--  '7230055623' as "VERGI_NO",              
  -- '2234567890' AS "VERGI_NO",               
--'1234567801' AS "VERGI_NO",               
-- AMUSTERI.TCKimlikNo AS "VERGI_NO",              
 null as "KAPI_NO",              
 AMUSTERI.MUSADRES2  as "SOKAK_ADI",              
 null as "BINA_ADI",              
 null as "BINA_NO",              
 AMUSTERI.MUSILCE  as "ILCE",              
 AMUSTERI.MUSIL as "IL",              
 null as "POSTA_KODU",              
 AMUSTERI.MUSADRES1 as "MAHALLE",              
 Adres.Ulke as "ULKE_ADI",              
 Adres.VergiDairesi as "VERGI_DA",              
 AMusteri1.email_1 AS "EPOSTA",              
 Adres.Tel1 as "TEL_NO",              
 Adres.Faks as "FAKS",              
 AMusteri1.WebAdresi_1 as "WEB_SITE",               
    AMUSTERI.MUSADI as "UNVANI",              
 CASE WHEN LEN(AMUSTERI.MUSVERNO)>10 THEN AMusteri1.KimlikAdi ELSE AMUSTERI.MUSADI END    AS "ADI",              
 CASE WHEN LEN(AMUSTERI.MUSVERNO)>10 THEN AMusteri1.KimlikSoyAdi ELSE '' END     AS "SOYADI",              
   -- 'urn:mail:defaultpk@pasaogluyumurta.com' AS "ETIKET" ,               
    AMusteri1.email_1 AS "ETIKET",    
 AMusteri1.email_2 AS "ETIKET_IRSALIYE",    
  STK.FatSerNo+convert(varchar,year(getdate()))+ substring(CONVERT(VARCHAR,STK.FaturaNo),2,len(CONVERT(VARCHAR,STK.FaturaNo)))  as "EFATURA_NO",                 
    (SELECT CAST(S.IrsNo AS VARCHAR(MAX))  +';'  
    FROM              
 (              
select distinct S.IrsNo from STOKHARDETBAG S where  
S.IslemNo in (select IslemNo from STOKHAR WHERE @IslemNo in (IslemNo,IslemNo3)) and FisTuru='I' and S.CariNo=STK.CariNo  
  )S     
  ORDER BY S.IrsNo ASC             
    FOR XML PATH(''),TYPE   ) as "IRSALIYE_NO",   
  (SELECT CAST(S.IrsaliyeTarihi AS VARCHAR(MAX))+';'               
    FROM     
 (SELECT DISTINCT CONVERT(varchar(10), S.FaturaTarihi ,105) IrsaliyeTarihi FROM               
  STOKHARDETBAG S where S.IslemNo in (select IslemNo from STOKHAR WHERE @IslemNo in (IslemNo,IslemNo3)) and FisTuru='I' and S.CariNo=STK.CariNo   
         
  )S     
  ORDER BY S.IrsaliyeTarihi ASC             
    FOR XML PATH(''),TYPE   ) as "IRSALIYE_TARIHI",     
CASE               
WHEN ISNULL (DD.Kdv,0)=0 AND STK.OzelKod1='31' AND STK.YazarKasaFisNo<>2 and STK.EF_ISTISNA=0  THEN    '351;Vergiden muaf.'                
WHEN  ISNULL (DD.Kdv,0)=0 AND STK.OzelKod1='31' AND STK.YazarKasaFisNo=2 and STK.EF_ISTISNA=0  THEN   '11/1-a Mal ihracatı'               
when SUBSTRING(ISNULL(OzelKod1,'00'), 1, 1)=3 and ISNULL (DD.Kdv,0)=0 and STK.EF_ISTISNA=0  then '701;11/1-c md. Kapsamındaki İhraç Kayıtlı Satış'               
when DD.TopTutar=0 and STK.EF_ISTISNA=0 then '351;KısmiBedelsiz'           
 WHEN ISNULL (DD.Kdv,0)=0  and ISNULL(STK.EF_ISTISNA,0)=0  THEN   (SELECT Deger FROM Parametre WHERE Param='eFaturaMuafiyetAciklama')               
ELSE (SELECT top 1  MuafiyetKodu+';'+MuafiyetAciklamasi FROM StokVergiMuafiyetleri  WHERE MuafiyetKodu= STK.EF_ISTISNA )          
 END AS  "MUAFIYET",              
 --(SELECT CONVERT(VARCHAR,DEGISKEN_KODU)+'-'+DEGISKEN_ADI FROM EF_DEGISKENLER WHERE DEGISKEN_KODU=STK.EF_ISTISNA) AS  "MUAFIYET",              
 STK.UUID,              
 STK.Ack1 AS "ACIKLAMA1",              
 STK.Ack2 AS "ACIKLAMA2",              
 STK.Ack3 AS "ACIKLAMA3",      
 STK.Ack4 as "ACIKLAMA4",              
 case  when FaturaTipi=6 then 'KAMU' else  CASE YazarKasaFisNo WHEN  1 THEN 'TEMELFATURA' WHEN 2 THEN 'IHRACAT' ELSE 'TICARIFATURA' end END "FATURA_TUR",                  
 CASE  (select top 1 OzelKod1 from STOKHARDETBAG where IslemNo=@IslemNo) WHEN   0    THEN 'SATIS' WHEN   1 THEN 'TEVKIFAT' WHEN 2 THEN 'IADE'
 WHEN  3 THEN 'IHRACKAYITLI' WHEN  4 THEN 'ISTISNA' else case when STK.FaturaTipi in (5,6) then 'SATIS' end END  AS "SATIS_TUR",             
 SUBSTRING(ISNULL(OzelKod1,'00'), 2, 1) AS "ILETIM_TUR",              
    CASE WHEN STK.AltIskYuzde1 >0 THEN  'Alt iskonto oranları : ' +CONVERT(VARCHAR,STK.AltIskYuzde1) ELSE NULL  END +              
 CASE WHEN STK.AltIskYuzde2 >0 THEN   + '|'+CONVERT(VARCHAR,STK.AltIskYuzde2) ELSE NULL END +              
 CASE WHEN STK.AltIskYuzde3 >0 THEN   +         
 '|'+CONVERT(VARCHAR,STK.AltIskYuzde3) ELSE NULL  END  as "ALT_ISKONTO_ACIKLAMA",              
     DOVIZT.IsoKodu AS "DOVIZ_KODU",              
 round((CASE WHEN STK.Tutar=0 AND STK.DovizTut=0 THEN (DD.SonTutar/DD.SonTutarDov) ELSE(STK.Tutar/STK.DovizTut) END),4) AS "DOVIZ_CARPAN",              
  case when Adres.eFaturaMukellefimi='true' then 1 else 0 end EFatura ,              
  AMUSTERI.MUSNO CariNo,  
  STK.FaturaTipi "TIP"  
 --AMusteri1.email_1 as "ETIKT"               
  --'urn:mail:defaultpk@testkurumelpo.com.tr' AS "ETIKET"         
 --ISNULL(AMusteri1.KimlikAdi,'AD') as "ADI",              
 --ISNULL(AMusteri1.KimlikSoyAdi,'SOYAD') as "SOYADI"              
 FROM  AMUSTERI               
INNER JOIN  Adres ON AMUSTERI.MUSNO = Adres.CariNo              
INNER JOIN STOKHARDETBAG STK ON STK.CariKodu=AMUSTERI.MUSKOD              
JOIN STOKHAR DD ON DD.IslemNo=STK.IslemNo              
INNER JOIN AMusteri1 ON AMusteri1.CariNo=AMUSTERI.MUSNO               
INNER JOIN DOVIZT ON  DOVIZT.NoId=STK.DovizCinsi              
--INNER JOIN FatAciklama  on  FatAciklama.IslemNo2=STK.IslemNo              
WHERE (@IslemNo in(DD.IslemNo,DD.IslemNo3) and STK.FaturaAdresNo=Adres.AdresNo) ;

  --[***]----------------------
if exists (select * from sys.procedures where name='sp_mb_XML_StokHarDurumUpdateDelphi')
drop proc sp_mb_XML_StokHarDurumUpdateDelphi

--[***]----------------------

create  PROCEDURE [dbo].[sp_mb_XML_StokHarDurumUpdateDelphi]     
/* 2020 01 16 a.t güncelleme fatno*/
/*2021 07 17 a.t entegrator*/
 @IslemNo int,    
 @eFaturaGonderimTarihi DateTime,     
 @eFaturaDurumu nvarchar(20),    
 @UUID nvarchar(60),    
 @Efaturano nvarchar(60)='',
 @Entegrator int=0,
 @ProcessType int=1
AS     
update STK    
 set    
  UUID=@UUID,     
  eFaturaGonderimTarihi=@eFaturaGonderimTarihi,     
  eFaturaDurumu=@eFaturaDurumu  ,    
  eFaturaNo=case when @ProcessType<>3 then  @Efaturano   else '' end,
 IrsNo=	case when @ProcessType=3 then case when @Efaturano<>'' then @Efaturano else IrsNo end else IrsNo end
 ,FatSerNo=case when @ProcessType<>3 then case when @Efaturano<>'' then  convert(varchar(3),left(@Efaturano,3)) else FatSerNo end  else ''  end
 ,STK.FaturaNo=case when @ProcessType<>3 then  case when @Efaturano<>'' then  right (@eFaturaNo,10) else STK.FaturaNo end else '' end
 ,Entegrator=@Entegrator
 from STOKHARDETBAG STK

  where   (STK.IslemNo=@IslemNo OR STK.IslemNo3=@IslemNo);  
  

  ALTER TABLE AMusteri1 ALTER COLUMN email_1 nvarchar(200) null	;
ALTER TABLE AMusteri1 ALTER COLUMN email_2 nvarchar(200) null	;
ALTER TABLE AMusteri1 ALTER COLUMN email_3 nvarchar(200) null	;
ALTER TABLE [AcariTakas] ALTER COLUMN [TeslimEden] [nvarchar](100) NULL	;
ALTER TABLE [AcariTakas] ALTER COLUMN [TeslimAlan] [nvarchar](100) NULL	;
ALTER TABLE [AcariTakas] ALTER COLUMN [ACIKLAMA] [nvarchar](200) NULL	;
ALTER TABLE [AcariTakas] ALTER COLUMN Aciklama2 [nvarchar](200) NULL	;
ALTER TABLE ACARI ALTER COLUMN [ACIKLAMA] [nvarchar](200) NULL ;
ALTER TABLE ACARI ALTER COLUMN Aciklama2 [nvarchar](200) NULL	;
ALTER TABLE ACARI ALTER COLUMN TDATE DATETIME	;
ALTER TABLE ACARI ALTER COLUMN Update_Date DATETIME	;
ALTER TABLE AcariTakas ALTER COLUMN TDATE DATETIME	;
ALTER TABLE AcariTakas ALTER COLUMN Update_Date DATETIME	;
ALTER TABLE AcariTakas ALTER COLUMN TDATE DATETIME	;
ALTER TABLE AcariTakas ALTER COLUMN Update_Date DATETIME	;


--------------------
  
  
 alter PROCEDURE [dbo].[spOdemeYaz]   
 @IslemNo int,@Tarih datetime,@Tutar float,  
 @Aciklama nvarchar(15), @Durum nvarchar(1),  
 @CariNo int,@CariKodu nvarchar(20),  
 @Borc float,@Alacak float,@NoNo int,  
 @KULNO int,@BAAcikla nvarchar (15) ,@Departman nvarchar(8),  
 @KayitDuzeyi int,@SiraNo int,   
 @Modul nvarchar(3)  
AS  
INSERT Odeme (IslemNo, Tarih, Tutar, Aciklama, Durum, CariNo,  
 CariKodu,  Borc, Alacak,  NoNo, KULNO,BAAcikla, Departman,  
 KayitDuzeyi, SiraNo, Modul) 
VALUES (@IslemNo, @Tarih, @Tutar, @Aciklama, @Durum, @CariNo,  
 @CariKodu,  @Borc, @Alacak,  @NoNo, @KULNO,@BAAcikla, @Departman,  
 @KayitDuzeyi, @SiraNo, @Modul)  
  

      
alter PROCEDURE [dbo].[spOdemeHareketSilIslemNo]     
   @IslemNo int,  
   @IslemTuru nvarchar(40) = NULL     
AS    
IF @IslemTuru in ('Tahsilat','KK.Tahsilat')  
 DELETE FROM Odeme WHERE (IslemNo = @ISLEMNO)  and (@IslemTuru is null or BAAcikla in ('Tahsilat','KK.Tahsilat'))  
ELSE  
 DELETE FROM Odeme WHERE (IslemNo = @ISLEMNO)  and (@IslemTuru is null or BAAcikla = @IslemTuru)  