USE [AC_620_1]
GO
/****** Object:  StoredProcedure [dbo].[Op_Paye_Kharid_Analyz_Kosorat_Calc_Sub1_2_SF14]    Script Date: 2/8/2023 10:29:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
     ALTER PROCEDURE [dbo].[Op_Paye_Kharid_Analyz_Kosorat_Calc_Sub1_2_SF14] @UserID INT, @GOID INT, @KOLID INT, @Hesabid 
     INT, @ThesabID INT, @NameKhID INT, @Date1 NVARCHAR(10), @Date2 NVARCHAR(10)
     AS
     BEGIN
     	DECLARE @SQLQuery AS NVARCHAR(MAX)     
     	DECLARE @ParamDefinition AS NVARCHAR(MAX)      
     	DECLARE @SumHazine AS DECIMAL(19, 4)     
     	DECLARE @SumKoosorat AS DECIMAL(19, 4)         		
     	SET @SQLQuery = 
     	    '	SELECT  SUM(SumHazine) AS SumHazine,sum(SumKoosoratTedadDKh) AS SumKoosoratTedadDKh, SUM(SumKoosorat) AS SumKoosorat FROM     ( SELECT  SUM(DARBed) as SumHazine,0 as SumKoosoratTedadDKh ,0 as SumKoosorat 						FROM     [TRANSACTION] 						WHERE  (Date1 BETWEEN @Date1 AND @Date2) '
     	
     	SET @SQLQuery = @SQLQuery + ' And (GOID = @GOID)'          
     	SET @SQLQuery = @SQLQuery + ' And (KOLID = @KOLID)'               
     	IF (@HESABID IS NOT NULL)
     	    SET @SQLQuery = @SQLQuery + ' And (HESABID = @HESABID)'
     	
     	IF (@THESABID IS NOT NULL)
     	    SET @SQLQuery = @SQLQuery + ' And (THESABID = @THESABID)'
     	
     	SET @SQLQuery = @SQLQuery + 
     	    'UNION ALL  SELECT 0 as SumHazine, SUM(TedadDKh) as SumKoosoratTedadDKh, SUM(UnitPriceDKh * TedadDKh) as SumKoosorat 											FROM     [TRANSACTION] 											WHERE  (NAMEKHID = @NAMEKHID) AND (Date1 BETWEEN @Date1 AND @Date2)) AS DrvKoosorat '
     	
     	SET @ParamDefinition = 
     	    '@UserID INT, 								@GOID INT, 								@KOLID  INT, 								@Hesabid INT, 								@ThesabID  INT,  								@NameKhID  INT,  								@Date1  NVARCHAR(10), 								@Date2  NVARCHAR(10), 								@SumHazine DECIMAL(19,4)'
     	
     	EXECUTE sp_Executesql @SQLQuery,
     	     @ParamDefinition,
     	     @UserID,
     	     @GOID,
     	     @KOLID,
     	     @Hesabid,
     	     @ThesabID,
     	     @NameKhID,
     	     @Date1,
     	     @Date2,
     	     @SumHazine
     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
GO
ALTER TABLE dbo.T_NameKala SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
