
ALTER PROCEDURE dbo.Rep_0523893819
(
  @pvchAction NVARCHAR(50)='Analyz',
  @startDate  NVARCHAR(50),
  @ENDdate    NVARCHAR(50),
  @IdKala     INT,
  @IDA        INT,
  @KalaGO     INT,
  @Goid       INT,
  @KolID      INT,
  @HesabID    INT,
  @THesabID   INT,
  @NoeFact    INT,
  @NameKala   NVARCHAR(255)
)
AS
BEGIN 	       		/*  @pvchAction='Analyz',       		*   @NoeFact=forms![TreeViewRep_HesabAnbar]![Nfaktor],       		*   @THesabId=forms![TreeViewRep_HesabAnbar]![THesabId],       		*   @HesabId=forms![TreeViewRep_HesabAnbar]![HesabId],       		*   @Goid=forms![TreeViewRep_HesabAnbar]![Goid],       		*   @Kolid=forms![TreeViewRep_HesabAnbar]![Kolid],       		*   @KalaGo=forms![TreeViewRep_HesabAnbar]![GoKala],       		*   @IDA=forms![TreeViewRep_HesabAnbar]![AID],       		*   @startDate=forms![TreeViewRep_HesabAnbar]![AsDate],       		*   @enddate=forms![TreeViewRep_HesabAnbar]![TaDate],       		*   @IdKala=forms![TreeViewRep_HesabAnbar]![KalaID],       		*   @NameKala=forms![TreeViewRep_HesabAnbar]![FilterNameKala]  */
SET NOCOUNT ON;
DECLARE
@SQLString NVARCHAR(MAX);  	/* محتوي رشته پرس و جو*/
DECLARE
@WhrStatement NVARCHAR(MAX);
DECLARE
@ParmDefinition NVARCHAR(MAX);
DECLARE
@ParmAndstr NVARCHAR(MAX);
SET @WhrStatement=N' ';
SET @ParmAndstr=N' ';
SET @ParmDefinition=N'@IdKala int=null, 								   							 									@IDA int=null, 								   							 									@KalaGO int=null, 								   							 									@startDate nvarchar(50)=''0'', 								   							 									@ENDdate nvarchar(50)=''99999999'', 								   							 									@KolID int=null, 								   							 									@HesabID int=null, 								   							 									@Goid int=null , 								   							 									@THesabID int=null, 								   							 									@NoeFact int=null, 								   							 									@pvchAction nvarchar(50)=''Analyz'', 									@NameKala nvarchar(255)=null';

IF (@startDate IS NULL)
BEGIN
SET @startDate='0';
END

IF (@ENDdate IS NULL)
BEGIN
SET @ENDdate='99999999';
END    							            	   	   	  /* Build the SQL string one time. */       /* Specify the parameter format one time. */ 	 		    	/* -----------------شروع آنالیز*/ 			    	 /* 	--------------شروع عملیات چاپ*/

IF (@pvchAction IS NULL)
SET @pvchAction='Analyz'

IF (@pvchAction='Analyz')
BEGIN
DECLARE
  @tmpdbname VARCHAR(50)
SET @tmpdbname=RIGHT( REPLACE( CONVERT( UNIQUEIDENTIFIER, NEWID( ) ), '-', '' ), 8 );

IF OBJECT_ID( 'tempdb..##'+@tmpdbname+'' ) IS NOT NULL
  EXEC ('DROP TABLE ##'+@tmpdbname+'');

SET @SQLString=N' SELECT     FunInfoKalaAll_1.ID, FunInfoKalaAll_1.KalaNumber, FunInfoKalaAll_1.NameKala1, FunInfoKalaAll_1.GOKID,  					   FunInfoKalaAll_1.NameGoKalanumbr, FunInfoKalaAll_1.NameGoKala1,                         					   FunInfoKalaAll_1.AID, FunInfoKalaAll_1.AIDNumber, FunInfoKalaAll_1.NameAnbar, FunInfoKalaAll_1.NameVahed1,  					   FunInfoKalaAll_1.NameVahed2, FunInfoKalaAll_1.Active, FunInfoKalaAll_1.BarCode,                         					   FunInfoKalaAll_1.note, SubTransactionKala_1.GOID, SubTransactionKala_1.KolID, SubTransactionKala_1.HesabID,  					   SubTransactionKala_1.THesabID, SubTransactionKala_1.Tarikh,SubTransactionKala_1.ShomareFactor, SubTransactionKala_1.Namehesab,  					   SubTransactionKala_1.NoeFa1, SubTransactionKala_1.TedadVarde, SubTransactionKala_1.TedadSadre,                         					   convert(money,SubTransactionKala_1.MVarde) as MVarde, 					   convert(money,SubTransactionKala_1.MVarde/(case when SubTransactionKala_1.TedadVarde<>0 then SubTransactionKala_1.TedadVarde else 1 end )) as Phi_M_KH, 					   convert(money,SubTransactionKala_1.MSadre/(case when SubTransactionKala_1.TedadSadre>0 then SubTransactionKala_1.TedadSadre else 1 end ))  as Phi_M_Fo,  					   convert(money,SubTransactionKala_1.MSadre) as MSadre , SubTransactionKala_1.SharhKala, SubTransactionKala_1.NoeFa,CAST( 0 as BIGINT) AS Radif,CAST( 0 as Decimal(19,4)) AS TedadRadifi, 					  CAST( 0 as Decimal(19,4)) AS MandeRadifi, CAST( 0 as Decimal(19,4)) AS Phi_M ,SubTransactionKala_1.idFact                        					   INTO         ##'+@tmpdbname+' 					   	                   FROM         dbo.SubTransactionKala() AS SubTransactionKala_1 INNER JOIN                        	                   dbo.FunInfoKalaAll(0) AS FunInfoKalaAll_1 ON SubTransactionKala_1.KALAID = FunInfoKalaAll_1.ID AND SubTransactionKala_1.AID = FunInfoKalaAll_1.AID '     /* --------------------   شرط حساب */ 			   /* INTO         ##' + @tmpdbname +'  */

IF (@WhrStatement=N' ')
  SET @ParmAndstr=N' WHERE '
ELSE
  SET @ParmAndstr=N' AND '

IF (NOT (@THesabID IS NULL))
  SET @WhrStatement=@WhrStatement+@ParmAndstr+N' (SubTransactionKala_1.THesabID = @THesabID ) ';

IF (@WhrStatement=N' ')
  SET @ParmAndstr=N' WHERE '
ELSE
  SET @ParmAndstr=N' AND '

IF (NOT (@HesabID IS NULL))
  SET @WhrStatement=@WhrStatement+@ParmAndstr+N' (SubTransactionKala_1.HesabID = @HesabID ) ';

IF (@WhrStatement=N' ')
  SET @ParmAndstr=N' WHERE '
ELSE
  SET @ParmAndstr=N' AND '

IF (NOT (@Goid IS NULL))
  SET @WhrStatement=@WhrStatement+@ParmAndstr+N' (SubTransactionKala_1.Goid = @Goid) ';

IF (@WhrStatement=N' ')
  SET @ParmAndstr=N' WHERE '
ELSE
  SET @ParmAndstr=N' AND '

IF (NOT (@KolID IS NULL))
  SET @WhrStatement=@WhrStatement+@ParmAndstr+N' (SubTransactionKala_1.KolID = @KolID ) '; 	  /* -----------------------شرط انبار روی ساب پرس وجو*/

IF (@WhrStatement=N' ')
  SET @ParmAndstr=N' WHERE '
ELSE
  SET @ParmAndstr=N' AND '

IF (NOT (@IDA IS NULL))
  SET @WhrStatement=@WhrStatement+@ParmAndstr+N'(FunInfoKalaAll_1.AID = @IDA )'; 				 	 						    /* -----------------------شرط کالا روی ساب پرس وجو*/

IF (@WhrStatement=N' ')
  SET @ParmAndstr=N' WHERE '
ELSE
  SET @ParmAndstr=N' AND '

IF (NOT (@IdKala IS NULL))
  SET @WhrStatement=@WhrStatement+@ParmAndstr+N'(FunInfoKalaAll_1.ID = @IdKala )';

IF (@WhrStatement=N' ')
  SET @ParmAndstr=N' WHERE '
ELSE
  SET @ParmAndstr=N' AND '

IF (NOT (@NameKala IS NULL))
BEGIN
  SET @NameKala='%'+@NameKala+'%'
  SET @WhrStatement=@WhrStatement+@ParmAndstr+N' (NameKala1  Like @NameKala ) ';
  SET @ParmAndstr=' AND '
END 				 					    /* 	-----------------------شرط گروه انبار روی ساب پرس وجو*/

IF (@WhrStatement=N' ')
  SET @ParmAndstr=N' WHERE '
ELSE
  SET @ParmAndstr=N' AND '

IF (NOT (@KalaGO IS NULL))
  SET @WhrStatement=@WhrStatement+@ParmAndstr+N'(FunInfoKalaAll_1.GOKID = @KalaGO )'; 				  /* 	-----------------------شرط گروه انبار روی ساب پرس وجو*/

IF (@WhrStatement=N' ')
  SET @ParmAndstr=N' WHERE '
ELSE
  SET @ParmAndstr=N' AND '

IF ((NOT (@startDate IS NULL))
AND NOT (@ENDdate IS NULL))
  SET @WhrStatement=@WhrStatement+@ParmAndstr+N'(SubTransactionKala_1.Tarikh  BetWeen @startDate and  @ENDdate)'; 			 			     /* --------------------  نوع عمليات */

IF (@WhrStatement=N' ')
  SET @ParmAndstr=N' WHERE '
ELSE
  SET @ParmAndstr=N' AND '

IF (NOT (@NoeFact IS NULL))
  SET @WhrStatement=@WhrStatement+@ParmAndstr+N' ( SubTransactionKala_1.NoeFa= @NoeFact) ';

SET @SQLString=@SQLString+@WhrStatement+N' ORDER BY FunInfoKalaAll_1.ID, SubTransactionKala_1.Tarikh, SubTransactionKala_1.TedadVarde DESC '
SET @SQLString=@SQLString+N'Declare Cr2 Cursor Local for 						  							  Select ID, TedadVarde, TedadSadre, Mvarde from [##'+@tmpdbname+']  						  							  for Update 							  DECLARE @KALAID_1 bigint 						  							  DECLARE @Radif_2 int 						  							  DECLARE @TedadVarde_1 decimal(19,4) 						  							  DECLARE @TedadSadre_1 decimal(19,4) 						 						 						  							  DECLARE @Mvarde_1 decimal(19,4) 						  							  DECLARE @PHI_M_2 decimal(19,4) 						  							  DECLARE @KalaID_GH bigint 						  							  DECLARE @MTedadi decimal(19,4) 						  							  DECLARE @MRiali decimal(19,4) 						  							  DECLARE @PHI_M_GH decimal(19,4)   														 		 						  							  set @KalaID_GH=0 						  							  set @PHI_M_2=0 						  							  set @MTedadi=0 						  							  set @MRiali=0 						  							  set @PHI_M_GH=0 						  							  Set @Radif_2=0  						 						 						  							  Open Cr2    						  							  fetch Next from Cr2 into @KALAID_1,@TedadVarde_1,@TedadSadre_1,@Mvarde_1 						  							  while @@FETCH_STATUS=0 							   							  Begin 								 	 							  	if(@KALAID_1 <> @KalaID_GH) 								 		 							  	begin 									 			 							  		Set @Radif_2=0 									 			 							  		set	@PHI_M_2 = 0 									 			 							  		set @MRiali=0 									 			 							  		if (@TedadSadre_1>0) Set @PHI_M_GH = 1 									 									 			 							  		set @MTedadi=0 								 		 							  	end  										 								 								 	 							  	Set @KalaID_GH = @KALAID_1 								 								 	 							  	Set @Radif_2=@Radif_2 + 1 								 								 	 							  	Set @MTedadi=@MTedadi+(@TedadVarde_1-@TedadSadre_1) 		 								 	 							  	set @MRiali=@MRiali+@Mvarde_1-(@PHI_M_GH * @TedadSadre_1)  	 							  	if (@TedadVarde_1>0) and (@MTedadi<>0) Set @PHI_M_2 = round(ABS(@MRiali/@MTedadi),0) 		 							  	if (@MTedadi=0) Set @PHI_M_2 = 0   	 							  	if (@TedadSadre_1>0) 		 							  	Begin 			 							  		Set @PHI_M_2=@PHI_M_GH 			 							  		set @MRiali=@PHI_M_GH *@MTedadi 		 							  	End 		 									 								  							  	 							  	Set @PHI_M_GH=@PHI_M_2   							  	Set @MTedadi=Round(Convert(Float,@MTedadi),2)  								  							  	Update ##'+@tmpdbname+'  							  	set Radif=@Radif_2, 							  		PHI_M=@PHI_M_2 ,  							  		TedadRadifi=@MTedadi,  							  		MandeRadifi=@MRiali  							  							Where current of cr2; 								 	  							  							fetch Next from Cr2 into @KALAID_1,@TedadVarde_1,@TedadSadre_1,@Mvarde_1			  							  End	 						 						  							  Close cr2 						 							  deallocate cr2	'
EXECUTE sp_executesql
     @SQLString,
     @ParmDefinition,
     @Goid=@Goid,
     @pvchAction=@pvchAction,
     @IDA=@IDA,
     @NoeFact=@NoeFact,
     @THesabID=@THesabID,
     @HesabID=@HesabID,
     @KolID=@KolID,
     @IdKala=@IdKala,
     @KalaGO=@KalaGO,
     @startDate=@startDate,
     @ENDdate=@ENDdate,
     @NameKala=@NameKala;
EXEC ('select * from [##'+@tmpdbname+'] 				DROP TABLE ##'+@tmpdbname+'');
END

RETURN
END
GO