SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[FATURA_STOKMIAD_TRG] ON [dbo].[FATURA] FOR UPDATE AS    
SET NOCOUNT ON  
IF UPDATE(SUBEID) OR UPDATE(TUR) OR UPDATE(DEPOID) OR UPDATE(FIPTAL) OR UPDATE(INDORAN1) OR 
  UPDATE(INDORAN2)OR UPDATE(INDORAN3)OR UPDATE(EKORAN1)OR UPDATE(EKORAN2)OR UPDATE(EKORAN3)
BEGIN
   DECLARE   
	  @SUBEID int, 
	  @FATID int, 
	  @TUR   int, 
	  @TURADI nvarchar(100), 
	  @FIPTAL int, 
	  @DEPOID int, 
	  @INDORAN1 float, 
	  @INDORAN2 float, 
	  @INDORAN3 float, 
	  @EKORAN1 float, 
	  @EKORAN2 float, 
	  @EKORAN3 float,
	  @ESKISUBEID int, 
	  @ESKIFATID int, 
	  @ESKITUR   int, 
	  @ESKITURADI nvarchar(100),
	  @ESKIFIPTAL int, 
	  @ESKIDEPOID int, 	      
	  @ESKIINDORAN1 float, 
	  @ESKIINDORAN2 float, 
	  @ESKIINDORAN3 float, 
	  @ESKIEKORAN1 float, 
	  @ESKIEKORAN2 float, 
	  @ESKIEKORAN3 float,
	  @FATISLID int, 
	  @STOKID int,
	  @SKTARIHI SMALLDATETIME, 
	  @ADET float,
	  @BFIYAT float,
	  @KDV float,
	  @SATIRIND float,
	  @SATIRIND2 float,
	  @NFIYAT float, 
	  @ESKINFIYAT float
	  
      
   DECLARE @UPDATED_CURSOR CURSOR    
   DECLARE @ISL_CURSOR CURSOR    
      
   SET @UPDATED_CURSOR = CURSOR FAST_FORWARD FOR   
					   SELECT I.SUBEID, I.FATID, I.TUR, I.TURADI, I.FIPTAL, I.DEPOID, I.INDORAN1, I.INDORAN2, I.INDORAN3, I.EKORAN1, I.EKORAN2, I.EKORAN3,
					          D.SUBEID, D.FATID, D.TUR, D.TURADI, D.FIPTAL, D.DEPOID, D.INDORAN1, D.INDORAN2, D.INDORAN3, D.EKORAN1, D.EKORAN2, D.EKORAN3
					   FROM INSERTED I INNER JOIN DELETED D ON I.FATID = D.FATID     
   OPEN @UPDATED_CURSOR   
   FETCH NEXT FROM @UPDATED_CURSOR    
   INTO @SUBEID, @FATID, @TUR, @TURADI, @FIPTAL, @DEPOID, @INDORAN1, @INDORAN2, @INDORAN3, @EKORAN1, @EKORAN2, @EKORAN3, @ESKISUBEID, @ESKIFATID, @ESKITUR, 
        @ESKITURADI, @ESKIFIPTAL, @ESKIDEPOID, @ESKIINDORAN1, @ESKIINDORAN2, @ESKIINDORAN3, @ESKIEKORAN1, @ESKIEKORAN2, @ESKIEKORAN3   
   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   
		SET @ISL_CURSOR = CURSOR FAST_FORWARD FOR   
			SELECT     FATISL.FATISLID, FATISL.STOKID, CASE WHEN ISNULL(MIADZORUNLU, 0) = 1 THEN FATISL.SKTARIHI ELSE NULL END AS SKTARIHI, FATISL.ADET, 
			           FATISL.BFIYAT, FATISL.KDV, FATISL.SATIRIND, FATISL.SATIRIND2
			FROM         FATISL INNER JOIN
								  STOK ON FATISL.STOKID = STOK.STOKID INNER JOIN
								  STOKGRUP ON STOK.GRUP = STOKGRUP.KODU
			WHERE     (FATISL.FATID = @FATID) AND (STOK.TURU <> 'HIZMET')
			
		OPEN @ISL_CURSOR   
		FETCH NEXT FROM @ISL_CURSOR    
		INTO @FATISLID, @STOKID, @SKTARIHI, @ADET, @BFIYAT, @KDV, @SATIRIND, @SATIRIND2  
		WHILE @@FETCH_STATUS = 0 		    
		BEGIN  	 		   
		  SET @ESKINFIYAT =  		      
		   CASE WHEN @TURADI='PERAKENDE' THEN 100.0/(100.0+@KDV) ELSE 1.00 END * @BFIYAT * 
			    (100 - @SATIRIND)/ 100 * (100 - @SATIRIND2) / 100 * (100 - @ESKIINDORAN1) / 100 * (100 + @ESKIEKORAN1) / 100 * 
			    (100 - @ESKIINDORAN2)/ 100 * (100 + @ESKIEKORAN2) / 100 * (100 - @ESKIINDORAN3) / 100 * (100 + @ESKIEKORAN3) / 100 
			  
		  SET @NFIYAT =  		      
		   CASE WHEN @TURADI='PERAKENDE' THEN 100.0/(100.0+@KDV) ELSE 1.00 END * @BFIYAT * 				
			    (100 - @SATIRIND)/ 100 * (100 - @SATIRIND2) / 100 * (100 - @INDORAN1) / 100 * (100 + @EKORAN1) / 100 * 
			    (100 - @INDORAN2)/ 100 * (100 + @EKORAN2) / 100 * (100 - @INDORAN3) / 100 * (100 + @EKORAN3) / 100 
				          
		  IF UPDATE(SUBEID) OR UPDATE(TUR) OR UPDATE(DEPOID) OR UPDATE(FIPTAL)
		  BEGIN   
			  --eski halinden geri cekiyoruz   
			  IF (@ESKIFIPTAL=0) AND (@ESKIDEPOID IS NOT NULL) 
			  BEGIN
					 IF EXISTS(SELECT * FROM STOKMIADBAKIYE (NOLOCK)                      
							   WHERE SUBEID=@ESKISUBEID AND DEPOID=@ESKIDEPOID AND STOKID=@STOKID AND  ISNULL(SKTARIHI,'01.01.1900')=ISNULL(@SKTARIHI,'01.01.1900'))   
					  UPDATE STOKMIADBAKIYE 
					  SET MIKTAR = MIKTAR - @ESKITUR * @ADET, 
						  TUTAR = TUTAR - @ESKITUR * @ADET * ISNULL(@ESKINFIYAT, 0),
						  HASTASIZMIKTAR = HASTASIZMIKTAR - @ESKITUR * @ADET
					  WHERE SUBEID=@ESKISUBEID AND DEPOID=@ESKIDEPOID AND STOKID=@STOKID AND  ISNULL(SKTARIHI,'01.01.1900')=ISNULL(@SKTARIHI,'01.01.1900')   
					ELSE    
					  INSERT INTO STOKMIADBAKIYE (SUBEID, DEPOID, STOKID, SKTARIHI, MIKTAR, TUTAR, HASTASIZMIKTAR) 
					  VALUES(@ESKISUBEID, @ESKIDEPOID, @STOKID, @SKTARIHI, -@ESKITUR * @ADET, - @TUR * @ADET * ISNULL(@ESKINFIYAT, 0), - @ESKITUR * @ADET)     
			  END
  			     
			  IF (@FIPTAL=0) AND (@DEPOID IS NOT NULL)
			  BEGIN
					--yeni halini getiriyoruz   
					IF EXISTS(SELECT * FROM STOKMIADBAKIYE (NOLOCK)                  
							  WHERE SUBEID=@SUBEID AND DEPOID=@DEPOID AND STOKID=@STOKID AND  ISNULL(SKTARIHI,'01.01.1900')=ISNULL(@SKTARIHI,'01.01.1900'))   
					  UPDATE STOKMIADBAKIYE 
					  SET MIKTAR = MIKTAR + @TUR * @ADET,
						  TUTAR = TUTAR + @TUR * @ADET * ISNULL(@NFIYAT, 0),
						  HASTASIZMIKTAR = HASTASIZMIKTAR + @TUR * @ADET   						         
					  WHERE SUBEID=@SUBEID AND DEPOID=@DEPOID AND STOKID=@STOKID AND  ISNULL(SKTARIHI,'01.01.1900')=ISNULL(@SKTARIHI,'01.01.1900')   
					ELSE    
					  INSERT INTO STOKMIADBAKIYE (SUBEID, DEPOID, STOKID, SKTARIHI, MIKTAR, TUTAR, HASTASIZMIKTAR) 
					  VALUES(@SUBEID, @DEPOID, @STOKID, @SKTARIHI, @TUR*@ADET, @TUR * @ADET * ISNULL(@NFIYAT, 0),  @TUR * @ADET) 			      
			  END
		  END 
		  ELSE IF @DEPOID IS NOT NULL AND (ISNULL(@NFIYAT, 0) <> ISNULL(@ESKINFIYAT, 0))
		  BEGIN
		            -- Sadece Tutarlarda değişiklik oldu
					-- Tutarı güncelliyoruz   
					IF EXISTS(SELECT * FROM STOKMIADBAKIYE (NOLOCK)                  
							  WHERE SUBEID=@SUBEID AND DEPOID=@DEPOID AND STOKID=@STOKID AND  ISNULL(SKTARIHI,'01.01.1900')=ISNULL(@SKTARIHI,'01.01.1900'))   
					  UPDATE STOKMIADBAKIYE 
					  SET TUTAR = TUTAR + @TUR * @ADET * (ISNULL(@NFIYAT, 0) - ISNULL(@ESKINFIYAT, 0))
					  WHERE SUBEID=@SUBEID AND DEPOID=@DEPOID AND STOKID=@STOKID AND  ISNULL(SKTARIHI,'01.01.1900')=ISNULL(@SKTARIHI,'01.01.1900')   
					ELSE    
					  INSERT INTO STOKMIADBAKIYE (SUBEID, DEPOID, STOKID, SKTARIHI, MIKTAR, TUTAR, HASTASIZMIKTAR) 
					  VALUES(@SUBEID, @DEPOID, @STOKID, @SKTARIHI, @TUR*@ADET, @TUR * @ADET * (ISNULL(@NFIYAT, 0) - ISNULL(@ESKINFIYAT, 0)),@TUR*@ADET) 			      
		  END
	   					  
		  FETCH NEXT FROM @ISL_CURSOR    
		  INTO @FATISLID, @STOKID, @SKTARIHI, @ADET, @BFIYAT, @KDV, @SATIRIND, @SATIRIND2    
		END   
  		      
		CLOSE @ISL_CURSOR   
		DEALLOCATE @ISL_CURSOR   
	  
	  FETCH NEXT FROM @UPDATED_CURSOR    
	  INTO @SUBEID, @FATID, @TUR, @TURADI, @FIPTAL, @DEPOID, @INDORAN1, @INDORAN2, @INDORAN3, @EKORAN1, @EKORAN2, @EKORAN3, @ESKISUBEID, @ESKIFATID, @ESKITUR, 
		   @ESKITURADI, @ESKIFIPTAL, @ESKIDEPOID, @ESKIINDORAN1, @ESKIINDORAN2, @ESKIINDORAN3, @ESKIEKORAN1, @ESKIEKORAN2, @ESKIEKORAN3   
   END    
  	 
   CLOSE @UPDATED_CURSOR   
   DEALLOCATE @UPDATED_CURSOR   
END