CREATE Procedure [dbo].[Sp_GetMojodiWithDate] (
                                       @AzTarikh Varchar(10), 
                                       @TaTarikh  Varchar(10),
                                       @KindFactor varchar(25),
                                       @TypeFactor tinyint,
                                       @SystemName Varchar(50)
                                      )
                                        
As

Declare @SqlCommand  nVarchar(max)

IF Exists (select * from [Tempdb].dbo.sysobjects where name = '##MojodiWithDate') 
 Delete From ##MojodiWithDate Where SystemName = @SystemName And TypeFactor = @TypeFactor
Else
 Create Table  ##MojodiWithDate ([Id] int IDENTITY(1,1) NOT NULL,
                                 [K_Code] Varchar(20) Null,
                                 [Qty] money Null,
                                 [Barcode] Varchar(40) Null,
                                 [Meli_Code] Varchar(6) Null,
                                 [Name1] NVarchar(50) Null,
                                 [Price_Forosh] Money Null,
                                 [R_Unit1_2] Float Null,
                                 [NameGroup1] Nvarchar(50) Null,
                                 [NameGroup2] Nvarchar(50) Null,
                                 [NameGroup3] Nvarchar(50) Null,
                                 [TypeFactor] tinyint Null,
                                 [LatinOrIr]  Char(1) Null,
                                 [SystemName] Varchar(50) Null
                                 ) On [Primary]
   
Set @SqlCommand = 'Insert Into ##MojodiWithDate (K_Code, Qty, Barcode, Meli_code , Name1 , Price_Forosh, R_Unit1_2, NameGroup1, NameGroup2, NameGroup3, LatinOrIr, TypeFactor,SystemName)  
                   Select kalaid.K_Code, Qty, Barcode, Meli_code , Name1 , Havaleh.Price_Forosh, Kalaid.R_Unit1_2, Gname NameGroup1, Group2.Name NameGroup2 , Group3.Name  NameGroup3,LatinOrIr, ' + Cast(@TypeFactor as Varchar(1)) + ',''' + @SystemName + ''' From ( 
                   Select MaxTarikh, KCode, 
                                (Select Max(AutoId) MaxAutoid From headH inner join Havaleh 
                                on Headh.Id_Havaleh = Havaleh.Id_Havaleh
                                Where CONVERT(Varchar(10), Tarikh_Faktor, 111) = MaxTarikh'
                                IF @KindFactor <> ''
                                  Set @SqlCommand = @SqlCommand + ' And Kind_Flag in(' + @KindFactor + ')'
                                Set @SqlCommand = @SqlCommand   + '                                                                    
                                And K_Code = KCode
                                ) MaxAutoid, Qty
                  From (
                        Select MAX(Tarikh_faktor) MaxTarikh, K_Code KCode From Havaleh 
                        Inner join headH on HeadH.Id_Havaleh = Havaleh.Id_Havaleh
                        Where Tarikh_Faktor >= ''' + @AzTarikh + '''
	                    And Tarikh_Faktor <= '''   + @TaTarikh +'''' 
                        IF @KindFactor <> ''
                          Set @SqlCommand = @SqlCommand + ' And Kind_Flag in(' + @KindFactor + ')'
                        Set @SqlCommand = @SqlCommand   + '                                                                    
						Group By K_Code
						) As Total
						
						Left Outer Join 
						(
							Select K_Code, Abs(IsNull(Sum(Vorod),0) - IsNull(Sum(Sodor),0)) Qty From (
							Select K_Code , 
							Case
							 When Len(Sender) =3 Then SUM(dbo.Havaleh.K_Qty1)
							 End As Sodor,
							Case
							 When Len(Reciver) = 3 Then  SUM(dbo.Havaleh.K_Qty1) 
							End As Vorod
							FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh = Havaleh.Id_Havaleh 
							Where Tarikh_Faktor >= ''' + @AzTarikh + ''' 
							And Tarikh_Faktor <= ''' + @TaTarikh  + ''''
                            IF @KindFactor <> ''
                              Set @SqlCommand = @SqlCommand + ' And Kind_Flag in(' + @KindFactor + ')'
                            Set @SqlCommand = @SqlCommand   + '                                                                    
							 group by Sender , K_Code, Reciver
							) As T1 Group By K_Code
					  ) As Mojodi on Mojodi.K_Code = Total.KCode) As Total
				  Inner join Kalaid on Kalaid.K_Code = Total.KCode
				  Inner join Havaleh on Havaleh.AutoId = Total.MaxAutoid 
				  Left Outer Join Group1 on Gid_Fk = Gid
				  Left Outer Join Group2 on Gid_Fk1 = Code_G2
				  Left Outer Join Group3 on Gid_Fk2 = Code_G3'
					  
	  
Exec Sp_ExecuteSql @SqlCommand
