CREATE FUNCTION  [dbo].[CycleFullAvg]  (@ACode varchar(5),  @Date1 smalldatetime,@Date2 smalldatetime  , @ComputerName  Varchar(20),  @FlagSearch Char(1))
RETURNS
@CycleAnbar TABLE
   (
   ACode   Varchar(3),
   K_Code  varchar(15),
   Name1 Nvarchar(100),
   QtyKharid  Float, 
   QtyBForosh Float , 
   TQtyVorod Float, 
   QtyForosh Float ,  
   QtyBKharid Float ,  
   TQtySodor Float , 
   SumKharid Money, 
   SumBForosh Money,  
   TPriceVorod Money,       
   SumForosh Money ,  
   SumBKharid Money,  
   TPriceSodor Money,
   SumAvgKharid Money ,  
   SumAvgForosh Money,
   SumAvg Money,
    Mandeh Float
   )
AS  
BEGIN 
  IF @FlagSearch = '0' 
  BEGIN
     INSERT @CycleAnbar
      Select @Acode ACode , K_Code, Name1, 
      Sum(QtyKharid) QtyKharid , Sum(QtyBForosh) QtyBForosh , 
      Sum(TQtyVorod) TQtyVorod, 
      Sum(QtyForosh) QtyForosh , Sum(QtyBKharid) QtyBKharid , Sum(TQtySodor) TQtySodor, 
      Sum(SumKharid) SumKharid , Sum(SumBForosh) SumBForosh , Sum(TPriceVorod) TPriceVorod,       
      Sum(SumForosh) SumForosh , Sum(SumBKharid) SumBKharid , Sum(TPriceSodor) TPriceSodor,
      Sum(SumAvgKharid) SumAvgKharid , Sum(SumAvgForosh) SumAvgForosh ,
      Sum(SumAvgKharid) -  Sum(SumAvgForosh)  SumAvg ,
      (Sum(QtyKharid) + Sum(QtyBForosh)+ Sum(TQtyVorod)) - 
     (Sum(QtyForosh)  + Sum(QtyBKharid) + Sum(TQtySodor)) Mandeh
      From (
      Select K_Code, Name1, Sum(QtyKharid) QtyKharid , Sum(QtyBForosh) QtyBForosh , 
      Sum(TQtyVorod) TQtyVorod, 
      Sum(QtyForosh)  QtyForosh , Sum(QtyBKharid) QtyBKharid , Sum(TQtySodor) TQtySodor, 
      Sum(SumKharid) SumKharid , Sum(SumBForosh) SumBForosh , Sum(TPriceVorod) TPriceVorod,       
      Sum(SumForosh) SumForosh , Sum(SumBKharid) SumBKharid , Sum(TPriceSodor) TPriceSodor,
      0 SumAvgKharid , 0 SumAvgForosh ,
     0 SumAvg ,
      (Sum(QtyKharid) + Sum(QtyBForosh) + Sum(TQtyVorod)) - 
      (Sum(QtyForosh)  + Sum(QtyBKharid) + Sum(TQtySodor)) Mandeh
      From (
     SELECT Kalaid.K_Code,Kind_Flag,Name1,
      Case
         When Sender  = @ACode  then  '1'
         When Reciver = @ACode   then  '2'
      End As Type,
      ------------------------------------------------------------------(Sum Vorod)---------------------------------------------------------
      Sum(Case  When Reciver = @ACode And Kind_Flag in ('3','2') Then  dbo.Havaleh.K_Qty1 Else 0  End) As QtyKharid ,
      Sum(Case  When Reciver = @ACode And Kind_Flag in ('6','8','0') Then  dbo.Havaleh.K_Qty1 Else 0 End ) As  QtyBForosh,
      Sum(Case  When Reciver = @ACode And Kind_Flag  = '1'  Then  dbo.Havaleh.K_Qty1 Else 0  End) As  TQtyVorod,
      ------------------------------------------------------------------(Sum Khoroj)---------------------------------------------------------
      Sum(Case  When Sender =@ACode  And Kind_Flag in ('5','7','9') Then dbo.Havaleh.K_Qty1 Else 0  End ) As QtyForosh,
      Sum(Case  When Sender =@ACode  And Kind_Flag =  '4' Then dbo.Havaleh.K_Qty1 Else 0 End ) As QtyBKharid,
      Sum(Case   When Sender =@ACode  And Kind_Flag =  '1' Then dbo.Havaleh.K_Qty1 Else 0 End ) TQtySodor,
     ---------------------------------------------------------------------(Price Vorod)---------------------------------------------------------------------------
      Sum(Case  When Reciver = @ACode  And Kind_Flag  in ('3','2') Then dbo.Havaleh.Avg_Price * dbo.Havaleh.K_Qty1  Else 0 End ) As SumKharid,
      Sum(Case  When Reciver = @ACode  And Kind_Flag  in ('6' , '8', '0')  Then dbo.Havaleh.Avg_Price * dbo.Havaleh.K_Qty1 Else 0 End)  As SumBForosh,
      Sum(Case  When Reciver = @ACode  And Kind_Flag   = '1'   Then Round(dbo.Havaleh.Avg_price,0) * dbo.Havaleh.K_Qty1 Else 0 End) As TPriceVorod,
     ---------------------------------------------------------------------(Price Khoroj)----------------------------------------------------------------------------------
     Sum(Case   When Sender =  @ACode   And Kind_Flag   in ('5','7','9')  Then dbo.Havaleh.Avg_Price * dbo.Havaleh.K_Qty1 Else 0  End)  SumForosh,
     Sum(Case   When Sender =  @ACode   And Kind_Flag   = '1' Then Round(dbo.Havaleh.Avg_Price,0) * dbo.Havaleh.K_Qty1 Else 0 End)  As TPriceSodor,
     Sum(Case   When Sender =  @ACode   And Kind_Flag   = '4' Then dbo.Havaleh.Avg_Price * dbo.Havaleh.K_Qty1 Else 0 End) As  SumBKharid
     --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     FROM  dbo.headH WITH (NOLOCK) INNER JOIN dbo.Havaleh WITH (NOLOCK) ON dbo.headH.Id_Havaleh = dbo.Havaleh.Id_Havaleh
     Inner Join Kalaid WITH (NOLOCK) ON (Havaleh.K_Code = Kalaid.K_Code)
     Where Convert(Varchar(10), Tarikh_Faktor, 111) >= @Date1
     And Convert(Varchar(10), Tarikh_Faktor, 111) <= @Date2
     And (Sender =  @ACode or Reciver = @ACode)
     Group By Kalaid.K_Code, Sender, Reciver, Kind_Flag,Name1
    ) As T1
     Group By Type, K_Code,Name1) as T1
     Group By K_Code,Name1
  END
  ElSE IF @FlagSearch = '1' 
  BEGIN
     INSERT @CycleAnbar
     Select @ACode ACode , K_Code, Name1,  Sum(QtyKharid) QtyKharid , Sum(QtyBForosh) QtyBForosh , 
      Sum(TQtyVorod) TQtyVorod, 
      Sum(QtyForosh) QtyForosh , Sum(QtyBKharid) QtyBKharid , Sum(TQtySodor) TQtySodor, 
      Sum(SumKharid) SumKharid , Sum(SumBForosh) SumBForosh , Sum(TPriceVorod) TPriceVorod,       
      Sum(SumForosh) SumForosh , Sum(SumBKharid) SumBKharid , Sum(TPriceSodor) TPriceSodor,
      Sum(SumAvgKharid) SumAvgKharid , Sum(SumAvgForosh) SumAvgForosh ,
      Sum(SumAvgKharid) -  Sum(SumAvgForosh)  SumAvg ,
      (Sum(QtyKharid) + Sum(QtyBForosh) + Sum(TQtyVorod)) - 
      (Sum(QtyForosh)  + Sum(QtyBKharid) + Sum(TQtySodor) ) Mandeh
  From (
      Select K_Code, Name1, Sum(QtyKharid) QtyKharid , 0  QtyBForosh , 
      0 TQtyVorod, Sum(QtyForosh) QtyForosh , 0 QtyBKharid , 0 TQtySodor, 
      Sum(SumKharid) SumKharid , 0 SumBForosh , 0 TPriceVorod,       
      Sum(SumForosh) SumForosh , 0 SumBKharid , 0 TPriceSodor,
      0 SumAvgKharid , 0 SumAvgForosh, 0 SumAvg, 0 Mandeh 
      From (
     SELECT Kalaid.K_Code,Kind_Flag, Name1,
      Case
         When Sender  = @ACode   then  '1'
         When Reciver = @ACode   then  '2'
      End As Type,
      Sum(Case  When Sender =@ACode Then dbo.Havaleh.K_Qty1  Else 0 End ) As QtyForosh,
      Sum(Case  When Reciver = @ACode Then  dbo.Havaleh.K_Qty1  Else 0 End)  As QtyKharid,
      
      Sum(
          Case 
            When Reciver = @ACode  And Kind_Flag  =  '1'  Then Round(dbo.Havaleh.Tot_Omdeh,0) * dbo.Havaleh.K_Qty1 
            Else Round(dbo.Havaleh.Avg_Price,0) * dbo.Havaleh.K_Qty1  
          End
          )  As SumKharid,
                 
      Sum(
          Case  
            When Sender =  @ACode  Then Round(dbo.Havaleh.Avg_Price,0) * dbo.Havaleh.K_Qty1 
            Else 0 
          End
         )   As SumForosh                
                 
     FROM  dbo.headH WITH (NOLOCK) INNER JOIN dbo.Havaleh WITH (NOLOCK) ON dbo.headH.Id_Havaleh = dbo.Havaleh.Id_Havaleh
     Inner Join Kalaid WITH (NOLOCK) ON (Havaleh.K_Code = Kalaid.K_Code)
     Where Convert(Varchar(10), Tarikh_Faktor, 111) < @Date1
     And (Sender =  @ACode or Reciver = @ACode)
     Group By Kalaid.K_Code, Sender, Reciver, Kind_Flag, Name1
    ) As T1
     Group By Type, K_Code, Name1) as T1
     Group By K_Code, Name1
  END
RETURN
END
