
USE [AC_620_1]
GO
/****** Object:  StoredProcedure [dbo].[Op_Paye_NameKala_ChengeInformatoinkala]    Script Date: 2/8/2023 3:17:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
   ALTER PROCEDURE [dbo].[Op_Paye_NameKala_ChengeInformatoinkalaUpdatePrice] @UserID INT, @GoKID NVARCHAR(MAX), @AID NVARCHAR(MAX), 
   @KalaID NVARCHAR(MAX)  ,@Zarib DECIMAL(19,4), @CurPriceRate DECIMAL(19,4),@CurRef INT,@type INT,@FrameKalatype INT,@CurRef2 INT 
   AS
   BEGIN
   	DECLARE @SQLQuery AS NVARCHAR(MAX)     
   	DECLARE @ParamDefinition AS NVARCHAR(MAX)      
   	DECLARE @OrderBy AS NVARCHAR(MAX)     
   	DECLARE @GROUPBy AS NVARCHAR(MAX)					 


   										      										   
   	  IF(@type=1)
			SET @SQLQuery = ' update  T_NameKala Set [UnitPrice1]= CASE ' + char(10)
					 + '   	WHEN ( ' + char(10)
					 + '    ISNULL([UnitPrice1] ,0)+(ROUND((ISNULL([UnitPrice1] ,0)*@Zarib/100) ,0)))>=1000 THEN  ' + char(10)
					 + '     ROUND(( ISNULL([UnitPrice1] ,0)+(ROUND((ISNULL([UnitPrice1] ,0)*@Zarib/100) ,0)))/1000,0)*1000 ' + char(10)
					 + '   ELSE  ' + char(10)
					 + '    ROUND((ISNULL([UnitPrice1] ,0)+(ROUND((ISNULL([UnitPrice1] ,0)*@Zarib/100) ,0)))/100,0)*100 ' + char(10)
					 + '   END ' + char(10)

   	  IF(@type=2)
   	  		SET @SQLQuery ='update  T_NameKala Set [UnitPrice1]= CASE    ' + char(10)
							+ '  WHEN ((ROUND((ISNULL([CurPrice],0)* ISNULL(@CurPriceRate,0)), 0)))>=1000 THEN ROUND(((ROUND((ISNULL([CurPrice],0)* @CurPriceRate), 0)))/1000,0)*1000' + char(10)
							+ ' ELSE   ' + char(10)
							+ '	ROUND(((ROUND((ISNULL([CurPrice],0)* @CurPriceRate), 0)))/100,0)*100  ' + char(10)
							+ ' END  ' + char(10)

   	  	
   	  	    	
   	SET @SQLQuery = @SQLQuery+ ' WHERE  (1 = 1)   '
   	
   	IF(@type=2)
   		 SET @SQLQuery = @SQLQuery+ ' AND  (T_NameKala.CurRef = @CurRef)   '

	IF(@type=1)
	BEGIN
   		IF(@FrameKalatype=1)	 
   			 SET @SQLQuery = @SQLQuery+ ' AND  (T_NameKala.CurRef IS NULL) '
   
   		IF(@FrameKalatype=3)	 
   			 SET @SQLQuery = @SQLQuery+ ' AND  (T_NameKala.CurRef = @CurRef2)'
	END

   		 
   	IF (@GoKID IS NOT NULL)
   	    SET @SQLQuery = @SQLQuery + 
   	        ' And (T_NameKala.ID IN(SELECT  T_NameKala.ID 	FROM     T_NameKala INNER JOIN 		T_Kala ON T_NameKala.ID = T_Kala.NameKalaID WHERE (T_Kala.GoKID IN ('
   	        + @GoKID + '))))'
   	
   	IF (@AID IS NOT NULL)
   	    SET @SQLQuery = @SQLQuery + 
   	        ' And (T_NameKala.ID IN(SELECT  T_NameKala.ID 	FROM     T_NameKala INNER JOIN 		T_Kala ON T_NameKala.ID = T_Kala.NameKalaID WHERE (T_Kala.AID IN ('
   	        + @AID + '))))'
   	
   	IF (@KalaID IS NOT NULL)
   	BEGIN
   	    SET @KalaID = '%' + @KalaID + '%' 			  			 
   	    SET @SQLQuery = @SQLQuery + ' And (NameKala1 LIKE  @KalaID )'
   	END
   	
   	SET @ParamDefinition = 
   	    '@UserID INT,
   	    @GOKID NVARCHAR(Max),
   	    @AID  NVARCHAR(Max),
   	    @KalaID NVARCHAR(Max),
   	    @Zarib DECIMAL(19,4), 
   	    @CurPriceRate DECIMAL(19,4),
   	    @CurRef INT,
   	    @CurRef2 INT'
   	   	  								   		    
   	EXECUTE sp_Executesql @SQLQuery,
   	     @ParamDefinition,
   	     @UserID,
   	     @GOKID,
   	     @AID,
   	     @KalaID,
   	     @Zarib,
   	     @CurPriceRate,
   	     @CurRef,
   	     @CurRef2
   END   
   
   
    