/****** Object:  StoredProcedure [dbo].[Op_Paye_NameKala_ChengeInformatoinkala]    Script Date: 08/13/2023 15:26:48 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Op_Paye_NameKala_ChengeInformatoinkala]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Op_Paye_NameKala_ChengeInformatoinkala]
GO
/****** Object:  StoredProcedure [dbo].[Op_Paye_NameKala_ChengeInformatoinkalaUpdatePrice]    Script Date: 08/13/2023 15:26:48 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Op_Paye_NameKala_ChengeInformatoinkalaUpdatePrice]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Op_Paye_NameKala_ChengeInformatoinkalaUpdatePrice]
GO
/****** Object:  StoredProcedure [dbo].[Op_Paye_NameKala_ChengeInformatoinkalaUpdatePrice]    Script Date: 08/13/2023 15:26:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Op_Paye_NameKala_ChengeInformatoinkalaUpdatePrice]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE 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
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Op_Paye_NameKala_ChengeInformatoinkala]    Script Date: 08/13/2023 15:26:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Op_Paye_NameKala_ChengeInformatoinkala]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'   CREATE PROCEDURE [dbo].[Op_Paye_NameKala_ChengeInformatoinkala]   @UserID INT, @GoKID NVARCHAR(Max), @AID  NVARCHAR(Max), @KalaID NVARCHAR(Max) AS      BEGIN 		 	Declare @SQLQuery AS NVarchar(MAX)     Declare @ParamDefinition AS NVarchar(MAX)      DECLARE @OrderBy AS NVARCHAR(MAX)     DECLARE @GROUPBy AS NVARCHAR(MAX)      	set @SQLQuery=''SELECT  T_NameKala.*, CONVERT(money, ISNULL(vaznKala, 0) * (ISNULL(UnitPriceKharid, 0) + ISNULL(HazineKala, 0)) + (ISNULL(DarsadSoodKala, 0) * ISNULL(vaznKala, 0)) * (ISNULL(UnitPriceKharid, 0)                 + ISNULL(HazineKala, 0))) AS GhifoFinal FROM     T_NameKala WHERE  (1 = 1)   ''                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)''  Set @SQLQuery=@SQLQuery+'' ORDER BY NameKala1''     								   		    Execute sp_Executesql   @SQLQuery,  							@ParamDefinition, 							@UserID , 							@GOKID, 							@AID  ,  							@KalaID  													                                  END    ' 
END
GO
