ALTER TABLE StokBarkodlari ADD SiraNo int

GO

create  or alter  proc  sp_stokbarkod_kaydet @Tip int,@StokNo int      
AS      
BEGIN      
  
 DECLARE  @Kodu nvarchar(100), @Barkodu nvarchar(100),@AnaBirim nvarchar(20)    
       
 IF @Tip = 0      
 BEGIN      
  Select @Barkodu = Barkodu from STOKKARTI where StokNo = @StokNo;      
    
  IF ISNULL(@Barkodu,'') = ''    
  BEGIN      
	UPDATE STOKKARTI SET Barkodu = Kodu where StokNo = @StokNo;      
  END;    
    
  Select  @Kodu = Kodu,@Barkodu = Barkodu,@AnaBirim = AnaBirim from STOKKARTI where StokNo = @StokNo;      
       
  IF @Barkodu <> ''       
  BEGIN      
    IF (Select count(*) from StokBarkodlari Where StokNo = @StokNo and AnaBirim = 1) > 0      
    BEGIN      
		UPDATE StokBarkodlari SET Barkodu = @Barkodu,Birim = @AnaBirim where StokNo = @StokNo and AnaBirim = 1;      
    END      
    ELSE      
    BEGIN      
		insert into StokBarkodlari (StokKodu,Barkodu,KulNo,Carpani,UpdateDate,StokNo,Birim,AnaBirim,SiraNo)    
			 values (@Kodu,@Barkodu,1,1,GETDATE(),@StokNo,@AnaBirim,1,1)        
    END;      
  END;       
 END      
 ELSE IF @Tip = 1      
 BEGIN      
  UPDATE sk       
     SET sk.Barkodu = sb.Barkodu,       
         sk.AnaBirim = sb.Birim      
    from STOKKARTI sk,      
         StokBarkodlari sb      
   where sk.StokNo = sb.StokNo      
     and sb.AnaBirim = 1      
     and sk.StokNo = @StokNo           
 END;      
 -- update StokFiyat set StokNo =@StokNo  where Kodu=@Kodu  
 -- update StokBarkodlari set StokNo =@StokNo   where StokKodu=@Kodu  
   
END; 

GO

update s1 SET  s1.SiraNo = s2.Rank from StokBarkodlari s1, (select *,DENSE_RANK() OVER  (PARTITION BY StokKodu ORDER BY AnaBirim DESC,Barkodu ) AS Rank  from StokBarkodlari) s2
where s1.StokBarkodlariNoId = s2.StokBarkodlariNoId

GO

CREATE VIEW V_StokBarkodlari_Birimler AS
select 
	StokKodu,
	(Select Barkodu from StokBarkodlari sb1 where sb1.StokKodu  =sb.StokKodu and SiraNo = 2) IkinciBirimBarkodu,
	(Select Barkodu from StokBarkodlari sb1 where sb1.StokKodu  =sb.StokKodu and SiraNo = 3) UcuncuBirimBarkodu,
	(Select Barkodu from StokBarkodlari sb1 where sb1.StokKodu  =sb.StokKodu and SiraNo = 4) DorduncuBirimBarkodu, 
	(Select Barkodu from StokBarkodlari sb1 where sb1.StokKodu  =sb.StokKodu and SiraNo = 5) BesinciBirimBarkodu, 
	(Select Barkodu from StokBarkodlari sb1 where sb1.StokKodu  =sb.StokKodu and SiraNo = 6) AltinciBirimBarkodu 
from StokBarkodlari sb
Group By StokKodu

