USE [Ent_MARKA17]
GO
/****** Object:  StoredProcedure [dbo].[sp_StokHrk_Duzelt]    Script Date: 17.05.2020 23:36:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[sp_StokHrk_Duzelt]
as
begin

	UPDATE x SET x.StokKodu=s.Kodu

	from STOKHAR x 
	join STOKKARTI s on s.StokAdi=x.StokAdi and x.Birim=s.AnaBirim
	where (isnull(x.StokKodu,'') ='' or x.StokKodu like 'Ent_27%') and s.Kodu not like 'Ent%'

	UPDATE x SET x.StokKodu=s.Kodu

	from STOKHAR x 
	join STOKKARTI s on s.StokAdi=x.StokAdi 
	where (isnull(x.StokKodu,'') ='' or x.StokKodu like 'Ent_27%') and s.Kodu not like 'Ent%'

	declare @StokAdi nvarchar(500),@StokKod nvarchar(100)
	declare crs_stokhar cursor for
	select  distinct StokKodu,ltrim(rtrim(StokAdi)) StokAdi from STOKHAR x where (isnull(x.StokKodu,'') ='' or x.StokKodu like 'Ent_27%') and len(StokAdi)>1

	open crs_stokhar
	FETCH NEXT FROM crs_stokhar into @StokKod,@StokAdi
	while @@fetch_status=0
	begin
 
	  declare @tblAyrilmis table (Ayri nvarchar(max),Ord int)
	  declare @RowCount int=0,@SlcCount int=0,@NewStokAdi nvarchar(max)=''

	  insert into @tblAyrilmis
	  select * from  dbo.Ayrac(@StokAdi,' ')

	  set @RowCount =isnull((select count(*) from @tblAyrilmis),0)
	  while @RowCount>0
	  begin
		set @NewStokAdi=isnull((select STUFF(
					 (SELECT ' ' + Ayri FROM @tblAyrilmis  where Ord<@RowCount FOR XML PATH ('')), 1, 1, ''
				   ) ),'')
    
		declare @UpdStok table (Kodu nvarchar(250),StokAdi nvarchar(max))
		insert into @UpdStok
		select top 1 convert(nvarchar(60),Kodu),convert(nvarchar(80),StokAdi) from STOKKARTI s where Kodu not like 'Ent%' and StokAdi like @NewStokAdi+'%'
		if (select count(*) from @UpdStok)>0
		begin
		  update x  set x.StokKodu=S.Kodu
		  from STOKHAR x 
		  CROSS APPLY STOKKARTI  S
			where S.Kodu not like 'Ent%' and S.StokAdi like @NewStokAdi+'%' 
			AND (isnull(x.StokKodu,'') ='' or x.StokKodu like 'Ent_27%') and x.StokAdi=@StokAdi
			set @RowCount=0;	
			--	FETCH NEXT FROM crs_stokhar into @StokKod,@StokAdi 
			--	break;
		end
		else 
		  set @RowCount=@RowCount-1
	  end
		--delete @tblAyrilmis

		FETCH NEXT FROM crs_stokhar into @StokKod,@StokAdi 
	end
	close crs_stokhar
	DEALLOCATE   crs_stokhar

    declare @StokKodu nvarchar(60),@StokAd nvarchar(80)
	declare crs_stok CURSOR FOR
	select distinct StokKodu,StokAdi FROM STOKHAR where StokKodu='' or StokKodu like 'Ent%'
	open crs_stok
	fetch next from crs_stok into @StokKodu,@StokAd
	while @@FETCH_STATUS=0
	begin
	  declare @YeniStokAdi nvarchar(80),@KarakterSayisi int,@YeniStokKod nvarchar(60)
	  set @KarakterSayisi=len(@StokAdi)
	  while @KarakterSayisi>5
	  begin

		set @YeniStokAdi=SUBSTRING(@StokAd,0,@KarakterSayisi)
		set @KarakterSayisi=@KarakterSayisi-1
		set @YeniStokKod =isnull((select top 1 Kodu from STOKKARTI where Kodu not like 'Ent%' and StokAdi like @YeniStokAdi+'%'),'')
		if(@YeniStokKod<>'')
		begin
		  update x
		  set x.StokKodu = @YeniStokKod 
		  frOM STOKHAR x WHERE x.StokAdi=@StokAd and (x.StokKodu ='' or  x.StokKodu like 'Ent%')
		  set @KarakterSayisi =4;
		end	
		--select @YeniStokAdi
	  end
	  fetch next from crs_stok into @StokKodu,@StokAd

	end
	close crs_stok
	DEALLOCATE crs_stok
end