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,@CurRound BIT 
   AS
   BEGIN
   	DECLARE @SQLQuery AS NVARCHAR(MAX)     
   	DECLARE @ParamDefinition AS NVARCHAR(MAX)      
   	DECLARE @OrderBy AS NVARCHAR(MAX)     
   	DECLARE @GROUPBy AS NVARCHAR(MAX)					 

	DECLARE @CurRoundZarib1000 AS INT=1000
	DECLARE @CurRoundZarib100 AS INT=100  

	 IF(ISNULL(@CurRound,0)=0)
		BEGIN
				SET	 @CurRoundZarib1000	=1

				SET	 @CurRoundZarib100 =1
		END
   										      										   
   	  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)))/@CurRoundZarib1000,0)*@CurRoundZarib1000 ' + char(10)
					 + '   ELSE  ' + char(10)
					 + '    ROUND((ISNULL([UnitPrice1] ,0)+(ROUND((ISNULL([UnitPrice1] ,0)*@Zarib/100) ,0)))/@CurRoundZarib100,0)*@CurRoundZarib100 ' + 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)))/@CurRoundZarib1000,0)*@CurRoundZarib1000' + char(10)
							+ ' ELSE   ' + char(10)
							+ '	ROUND(((ROUND((ISNULL([CurPrice],0)* @CurPriceRate), 0)))/@CurRoundZarib100,0)*@CurRoundZarib100  ' + 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,
   	    @CurRoundZarib1000 INT,
   	    @CurRoundZarib100 INT'
   	   	  								   		    
   	EXECUTE sp_Executesql @SQLQuery,
   	     @ParamDefinition,
   	     @UserID,
   	     @GOKID,
   	     @AID,
   	     @KalaID,
   	     @Zarib,
   	     @CurPriceRate,
   	     @CurRef,
   	     @CurRef2,
   	     @CurRoundZarib1000,
   	     @CurRoundZarib100
   END   
   
   
    
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.T_NameKala ADD
	CurRef int NULL,
	Curprice decimal(19,4)NULL
GO
ALTER TABLE dbo.T_NameKala SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
    
