
create  Procedure  [dbo].[sp_LastPriceAnbar]  @BeforeDate smalldatetime, @Type Char(1) As
  Declare @StrCommand NVarchar(4000)
  Declare @Parameter Nvarchar(50)
  IF @Type = '0' 
  Set @StrCommand ='Declare @ACode Varchar(3)
                    Declare Cursor_Anbar Cursor For
					Select A_Code From AnbarId
					Open Cursor_Anbar
					Fetch Next From Cursor_Anbar Into @ACode
					While @@FETCH_STATUS = 0
					Begin
						Insert Into ##SoodZian(K_code, A_code, Qty, CalcPrice) 
						Select T1.K_Code, @ACode, Mandeh, Price_KHarid * Mandeh CalcPrice From (
						Select T.K_Code, T.Tarikh, T.Price_Kharid From (
						SELECT Autoid, Havaleh.K_Code, Convert(Varchar(10),headh.Tarikh_faktor,111) Tarikh , Price_KHarid
						FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh =Havaleh.Id_Havaleh 
						Where HeadH.Tarikh_Faktor <=  @Date
						And Kind_Flag in (1,2,3) And len(Sender) = 5 
						Group By Havaleh.K_Code,
						Convert(Varchar(10),headh.Tarikh_faktor,111), 
						Price_KHarid, Autoid
						) As T
						Inner Join 
						(
						   Select K_code, Maxtarikh, 
						   (Select Top 1 autoid From  Havaleh inner join headH 
                           on  Havaleh.Id_Havaleh = Headh.Id_Havaleh
                           Where HeadH.Tarikh_Faktor <= @Date
                           And Kind_Flag  in (1,2,3) And len(Sender) = 5 
                           And Convert(Varchar(10),Tarikh_faktor,111) = MaxTarikh And K_Code = t.K_Code
                           order by price_kharid desc
                           ) MaxAutoid 
						  From (
						SELECT Havaleh.K_Code, Max(Convert(Varchar(10),Tarikh_faktor,111))  MaxTarikh
						FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh =Havaleh.Id_Havaleh 
						Where HeadH.Tarikh_Faktor <= @Date
						And Kind_Flag  in (1,2,3) And len(Sender) = 5 
						Group By Havaleh.K_Code) As T
						) As T1 on T.K_Code = T1.K_Code And T.Tarikh = T1.MaxTarikh And T1.MaxAutoid = T.Autoid 
						) As T
						Right Outer Join 
						(
						Select K_Code, IsNull(Sum(Vorod),0) - IsNull(Sum(Sodor),0) Mandeh 
						From (
							  Select K_Code , 
							  Case
  								When Len(Sender) =3 And Sender = @ACode Then SUM(dbo.Havaleh.K_Qty1)
							  End As Sodor,
							  Case
								 When Len(Reciver) = 3 And Reciver = @ACode Then  SUM(dbo.Havaleh.K_Qty1) 
							  End As Vorod
							  FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh = Havaleh.Id_Havaleh 
							  WHERE ((Len(Havaleh.Sender) =3) or (Len(Havaleh.Reciver)= 3 )) 
							  And HeadH.Tarikh_Faktor <=  @Date
							  Group by Sender , K_Code, Reciver
							) As T1 
							Group By K_Code
						) As T1 on T.K_Code = T1.K_Code
						Where Mandeh > 0
						Fetch Next From Cursor_Anbar Into @ACode
					end
					Update ##Soodzian Set CalcPrice = Anbar.Price_Kharid * Qty From ##Soodzian
					Inner Join Anbar On Anbar.A_Code = ##Soodzian.A_Code And Anbar.K_code = ##Soodzian.K_Code
					Where CalcPrice Is Null
					Close Cursor_Anbar
					Deallocate Cursor_Anbar'
      Else if @Type = '1' 
  Set @StrCommand ='Declare @ACode Varchar(3)
                    Declare Cursor_Anbar Cursor For
					Select A_Code From AnbarId
					Open Cursor_Anbar
					Fetch Next From Cursor_Anbar Into @ACode
					While @@FETCH_STATUS = 0
					Begin
						Insert Into ##SoodZian(K_code, A_code, Qty, CalcPrice) 
						Select T1.K_Code, @ACode, Mandeh,Price_KHarid * Mandeh CalcPrice From (
						Select T.K_Code, T.Tarikh, T.Price_Kharid From (
						SELECT Autoid, Havaleh.K_Code, Convert(Varchar(10),headh.Tarikh_faktor,111) Tarikh , Price_KHarid
						FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh =Havaleh.Id_Havaleh 
						Where HeadH.Tarikh_Faktor <=  @Date
						And Kind_Flag in (1,2,3) And len(Sender) = 5 
						Group By Havaleh.K_Code,
						Convert(Varchar(10),headh.Tarikh_faktor,111), 
						Price_KHarid, Autoid
						) As T
						Inner Join 
						(
						   Select K_code, Maxtarikh, 
						   (Select Top 1 autoid From  Havaleh inner join headH 
                           on  Havaleh.Id_Havaleh = Headh.Id_Havaleh
                           Where HeadH.Tarikh_Faktor <= @Date 
                           And Kind_Flag  in (1,2,3) And len(Sender) = 5 
                           And Convert(Varchar(10),Tarikh_faktor,111) = MaxTarikh And K_Code = t.K_Code
                           order by price_kharid desc
                           ) MaxAutoid 
						  From (
						SELECT Havaleh.K_Code, Max(Convert(Varchar(10),Tarikh_faktor,111))  MaxTarikh
						FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh =Havaleh.Id_Havaleh 
						Where HeadH.Tarikh_Faktor <= @Date
						And Kind_Flag  in (1,2,3) And len(Sender) = 5 
						Group By Havaleh.K_Code) As T
						) As T1 on T.K_Code = T1.K_Code And T.Tarikh = T1.MaxTarikh And T1.MaxAutoid = T.Autoid 
						) As T
						Right Outer Join 
						(
						Select K_Code, IsNull(Sum(Vorod),0) - IsNull(Sum(Sodor),0) Mandeh 
						From (
							  Select K_Code , 
							  Case
  								When Len(Sender) =3 And Sender = @ACode Then SUM(dbo.Havaleh.K_Qty1)
							  End As Sodor,
							  Case
								 When Len(Reciver) = 3 And Reciver = @ACode Then  SUM(dbo.Havaleh.K_Qty1) 
							  End As Vorod
							  FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh = Havaleh.Id_Havaleh 
							  WHERE ((Len(Havaleh.Sender) =3) or (Len(Havaleh.Reciver)= 3 )) 
							  Group by Sender , K_Code, Reciver
							) As T1 
							Group By K_Code
						) As T1 on T.K_Code = T1.K_Code
						Where Mandeh > 0
						Fetch Next From Cursor_Anbar Into @ACode
					end
					Update ##Soodzian Set CalcPrice = Anbar.Price_Kharid * Qty From ##Soodzian
					Inner Join Anbar On Anbar.A_Code = ##Soodzian.A_Code And Anbar.K_code = ##Soodzian.K_Code
					Where CalcPrice Is Null
					Close Cursor_Anbar
					Deallocate Cursor_Anbar'
  else if @Type = '2'    
 set  @StrCommand ='Declare @ACode Varchar(3)
                    Declare Cursor_Anbar Cursor For
					Select A_Code From AnbarId
					Open Cursor_Anbar
					Fetch Next From Cursor_Anbar Into @ACode
					While @@FETCH_STATUS = 0
					Begin
						Insert Into ##SoodZian(K_code, A_code, Qty, CalcPrice) 
						Select T1.K_Code, @ACode, Mandeh,Price_Finish * Mandeh CalcPrice From (
						Select T.K_Code, T.Tarikh, T.Price_Finish From (
						SELECT Autoid, Havaleh.K_Code, Convert(Varchar(10),headh.Tarikh_faktor,111) Tarikh , Price_Finish
						FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh =Havaleh.Id_Havaleh 
						Where HeadH.Tarikh_Faktor <=  @Date
						And Kind_Flag in (1,2,3) And len(Sender) = 5 
						Group By Havaleh.K_Code,
						Convert(Varchar(10),headh.Tarikh_faktor,111), 
						Price_Finish, Autoid
						) As T
						Inner Join 
						(
						   Select K_code, Maxtarikh, 
						   (Select Top 1 autoid From  Havaleh inner join headH 
                           on  Havaleh.Id_Havaleh = Headh.Id_Havaleh
                           Where HeadH.Tarikh_Faktor <= @Date 
                           And Kind_Flag  in (1,2,3) And len(Sender) = 5 
                           And Convert(Varchar(10),Tarikh_faktor,111) = MaxTarikh And K_Code = t.K_Code
                           order by Price_Finish desc
                           ) MaxAutoid 
						  From (
						SELECT Havaleh.K_Code, Max(Convert(Varchar(10),Tarikh_faktor,111))  MaxTarikh
						FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh =Havaleh.Id_Havaleh 
						Where HeadH.Tarikh_Faktor <= @Date
						And Kind_Flag  in (1,2,3) And len(Sender) = 5 
						Group By Havaleh.K_Code) As T
						) As T1 on T.K_Code = T1.K_Code And T.Tarikh = T1.MaxTarikh And T1.MaxAutoid = T.Autoid 
						) As T
						Right Outer Join 
						(
						Select K_Code, IsNull(Sum(Vorod),0) - IsNull(Sum(Sodor),0) Mandeh 
						From (
							  Select K_Code , 
							  Case
  								When Len(Sender) =3 And Sender = @ACode Then SUM(dbo.Havaleh.K_Qty1)
							  End As Sodor,
							  Case
								 When Len(Reciver) = 3 And Reciver = @ACode Then  SUM(dbo.Havaleh.K_Qty1) 
							  End As Vorod
							  FROM headH INNER JOIN  Havaleh ON headH.Id_Havaleh = Havaleh.Id_Havaleh 
							  WHERE ((Len(Havaleh.Sender) =3) or (Len(Havaleh.Reciver)= 3 )) 
							  Group by Sender , K_Code, Reciver
							) As T1 
							Group By K_Code
						) As T1 on T.K_Code = T1.K_Code
						Where Mandeh > 0
						Fetch Next From Cursor_Anbar Into @ACode
					end
					Update ##Soodzian Set CalcPrice = Anbar.Price_Finish * Qty From ##Soodzian
					Inner Join Anbar On Anbar.A_Code = ##Soodzian.A_Code And Anbar.K_code = ##Soodzian.K_Code
					Where CalcPrice Is Null
					Close Cursor_Anbar
					Deallocate Cursor_Anbar'   
  Set @Parameter = N'@Date smalldatetime'
  Exec Sp_ExecuteSql  @StrCommand, @Parameter, @Date = @BeforeDate
