CREATE PROCEDURE [dbo].[Sp_SleepKalaAvg]  @DataBase Varchar(6), @KindF Varchar(50), @KindBF Varchar(50),
                                                                              @StrAnbar  Varchar (100) , @GroupAnbar Varchar(100) , 
 					        @KolStrDate Varchar(1500), @StrCase Varchar (120), 
                                                                              @Qty Varchar (4), @StrMojodi Varchar(100), @StrFieldMojodi Varchar(50), 
					       @StrLink Varchar(100), @CheckLevelLink Char (1), @Str_Anbar1 varchar(90), @Str_Anbar2 Varchar(90), @CodeAnbar Varchar(90), @CodeForoshandeh Varchar(5)
AS 

Declare @Str Varchar(8000)
Declare @TmpStr Varchar(1000)

Set @TmpStr = '  if Exists (select * from [Tempdb].dbo.sysobjects where Name = N''##Avg'') '      +
                             ' drop table ##Avg          '							+

                             ' Create Table ##Avg (K_Code Varchar(15)null, Name1 Nvarchar(50) Null, '	+
                             '  Mojodi Money Null, KalaMin Real Null, Avg_Forosh_z Money Null,'		+
                             '  Avg_Forosh_y Money Null, Sood Money Null,'					+
                             ' NameUnit2 Nvarchar(30) Null, Qty_Forosh Real null'				+
                             ' ,Note Nvarchar(60) Null , NameH1 Nvarchar(30) Null,  '				+
                             ' NameL1 Nvarchar(30) Null, Namel2 Nvarchar(30) Null,'				+
                             '  SumKolKharid Money Null, SumKolForosh Money Null,AssetpriceKharid Money Null'	+
                             ' ,AssetpriceForosh Money Null, AssetAvgpriceKharid Money  Null, '		+
                             ' QtyDay Int Null) '
Exec(@TmpStr)

 Set @Str= '   Declare @Id1 Smallint'                                             +
                     '   Select @id1 = Max(Qtyday) From ('                         + 
	       '   SELECT K_Code, Count(K_code) QtyDay'           +  
                    '    FROM (  '                                                                     +
                    '    SELECT [' + @DataBase + '].Dbo.havaleh.k_code,'   +
                    '    COUNT([' + @DataBase + '].Dbo.havaleh.k_qty1) AS Qtyday' + 
                   '     FROM [' + @DataBase + '].Dbo.havaleh '                                    +
                   '     INNER JOIN [' + @DataBase + '].Dbo.Headh ON [' + @DataBase + '].Dbo.havaleh.id_havaleh = [' + 
                       @DataBase + '].Dbo.headh.id_havaleh  ' +
	     '    Where ([' + @DataBase + '].Dbo.HeadH.Kind_Flag ' + @KindF  +
                  '    GROUP BY [' + @DataBase + ' ].Dbo.Havaleh.k_code, ' +
                  '     Convert(Varchar(10), [' + @DataBase + '].Dbo.HeadH.Tarikh_Faktor, 111) '+ 
                  '     ) AS Table1   GROUP BY K_Code   ) As T' +

                     ' Insert  Into ##Avg(K_Code, Name1, QtyDay, Qty_Forosh, Mojodi, KalaMin,  Avg_Forosh_z, 
                    Avg_Forosh_y, SumKolKharid,  SumKolForosh,  Sood, AssetpriceKharid,   AssetpriceForosh,AssetAvgpriceKharid,
                   NameH1, NameL1, Namel2, NameUnit2, Note)'  +

                    'Select RepTotal.K_Code, Name1, QtyDay, Qty_Forosh, Mojodi, 0  KalaMin, ' +
                    'Case       '                                                        +
                    '   When QtyDay = 0   then 0       '                 +
                    '   When QtyDay <> 0  then Avg_Forosh'   +
                    ' End As Avg_Forosh_z, '                             +
                    '0 As Avg_Forosh_y,'                                     +
                   ' SumKolKharid,  SumKolForosh, SumKolForosh - SumKolKharid Sood , AssetpriceKharid,   AssetpriceForosh,AssetAvgpriceKharid  , '
                   IF @CheckLevelLink = '1' 
                      Set @Str = @Str +  ' NameH1, NameL1, Namel2,'
                   Else
                       Set @Str = @Str +  ' '''' NameH1,  '''' NameL1,  '''' Namel2,' 

                  Set @Str = @Str + 
	   ' Case       '                                                                     +
	  '  When NameUnit2 Is Null Then NameUnit1        '   +
	  '   When NameUnit2 Is Not Null Then NameUnit2   '  +
	  'End as NameUnit2,   '                                                 +
	  ' Case        '                                                                    +
	  '    When R_Unit1_2 <> 1 Then (''هر ''  + NameUnit2 + ''معادل'' + Cast(R_Unit1_2 as Varchar(10)) + '' '' + NameUnit1 + ''است'')   '  +
	 ' End as Note  ' +
	 'From ('             +
	      ' Select K_Code, Name1, QtyDay, Qty_Forosh, Avg_Forosh, Mojodi, Kala_Min,' +
	      ' R_Unit1_2,SumKolKharid,  SumKolForosh, '                                                            +
	      ' NameUnit1, NameUnit2,AssetpriceKharid,  AssetpriceForosh,    AssetAvgpriceKharid '  
	    IF @CheckLevelLink = '1' 
                     Set @Str = @Str +  ', NameH1, NameL1, Namel2'
	     Else
	          Set @Str = @Str +  ',''''  NameH1, '''' NameL1, '''' Namel2'
	     Set @Str = @Str + 
	     ' From ( '                                +
	      ' SELECT PublicForosh.K_Code, PublicForosh.Name1, PublicForosh.QtyDay, Anbar.Kala_Min,'                           +
	      ' PublicForosh.Qty_Forosh,  PublicForosh.Avg_Forosh, SUM(Anbar.Mojodi) AS Mojodi,KalaId.R_Unit1_2, '        +
	      ' Unit1.Name NameUnit1, Unit2.Name NameUnit2,PublicForosh.SumKolKharid, PublicForosh.SumKolForosh,  ' +
	      ' SUM(Anbar.Mojodi * Anbar.Price_Kharid) AS AssetpriceKharid,  '                                                                              +
	      ' SUM(Anbar.Mojodi * Anbar.Price_Forosh) AS AssetpriceForosh,  ' 						   +
	      ' SUM(Anbar.Mojodi * Anbar.Avg_Price_Kharid) AS AssetAvgpriceKharid  ' 
                   IF @CheckLevelLink = '1' 
	       Set @Str = @Str +  ', Level_Header.Name AS NameH1, Level_Detail1.Name AS NameL1, '                                 +	
                                                        ' Level_Detail2.Name AS Namel2'								                 +
   		                            ' FROM    Level_Link INNER JOIN'								                 +
				' Level_Header ON Level_Link.L_Code = Level_Header.L_Code LEFT OUTER JOIN'			    +
				' Level_Detail1 ON Level_Link.L_Code1 = Level_Detail1.Ch_Code AND Level_Header.L_Code = Level_Detail1.Fa_Code LEFT OUTER JOIN' 	   +
				' Level_Detail2 ON Level_Link.L_Code2 = Level_Detail2.Ch_Code AND Level_Detail1.Ch_Code = Level_Detail2.Fa_Code RIGHT OUTER JOIN (' 
                     Else
                         Set @Str = @Str +   ' From ('
	       Set @Str = @Str + 
	      ' SELECT K_Code, Name1, Count(K_code) QtyDay, SUM(Qty_Forosh) Qty_Forosh,  ' +
	      ' SUM(Qty_Forosh) /@id1   Avg_Forosh  ,Sum(SumKolKharid) SumKolKharid,  '                +
	      'Sum(SumKolForosh) SumKolForosh '                                                                                    +
	      ' FROM (  ' 										   +
	       'SELECT [' + @DataBase + '].Dbo.havaleh.k_code, [' + @DataBase + '].Dbo.kalaid.Name1,  '  +
	       'COUNT([' + @DataBase + '].Dbo.havaleh.k_qty1) AS Qtyday, '   +
	       'SUM([' + @DataBase + '].Dbo.havaleh.k_qty1) AS Qty_forosh,  '+
	       'Sum([' + @DataBase + '].Dbo.Havaleh.Price_Kharid * [' + @DataBase + '].Dbo.Havaleh.K_Qty1) SumKolKharid,  '  +
	       'Sum([' + @DataBase + '].Dbo.Havaleh.Price_Forosh * [' + @DataBase + '].Dbo.Havaleh.K_Qty1) SumKolForosh  ' +
	       ' FROM [' + @DataBase + '].Dbo.havaleh INNER JOIN [' + @DataBase + '].Dbo.kalaid  ' +
	       ' ON [' + @DataBase + '].Dbo.havaleh.k_code = [' + @DataBase + '].Dbo.kalaid.k_code   ' 

	       IF LTrim(RTrim(@StrLink)) <> '0 '
	           Set @Str = @Str + @StrLink  + @Str_Anbar1

	       Set @Str = @Str  + ' INNER JOIN [' + @DataBase + '].Dbo.Headh ON [' + @DataBase + '].Dbo.havaleh.id_havaleh = [' + @DataBase + '].Dbo.headh.id_havaleh ' +
	        ' Where ([' + @DataBase + '].Dbo.HeadH.Kind_Flag ' + @KindF 

                     IF LTrim(RTrim(@StrAnbar)) <> '0 '
                         Set @Str = @Str + @StrAnbar  

                    Set @Str = @Str + ' GROUP BY [' + @DataBase + '].Dbo.kalaid.name1, [' + @DataBase + '].Dbo.Havaleh.k_code, Convert(Varchar(10), ['  + @DataBase + '].Dbo.HeadH.Tarikh_Faktor, 111)'


                    IF LTrim(RTrim(@GroupAnbar)) <>  '0 '
                         Set @Str = @Str + @GroupAnbar 

                     Set @Str = @Str +   ' UNION ALL ' +	 	


	       'SELECT [' + @DataBase + '].Dbo.havaleh.k_code, [' + @DataBase + '].Dbo.kalaid.Name1,  ' +
	       ' 0 AS Qtyday,  - SUM([' + @DataBase + '].Dbo.havaleh.k_qty1) AS qty_forosh,  '                      +
	       ' Sum([' + @DataBase + '].Dbo.Havaleh.Price_Kharid * [' + @DataBase + '].Dbo.Havaleh.K_Qty1) SumKolKharid,  ' +
	       ' Sum([' + @DataBase + '].Dbo.Havaleh.Price_Forosh * [' + @DataBase + '].Dbo.Havaleh.K_Qty1) SumKolForosh ' +
	       ' FROM [' + @DataBase + '].Dbo.havaleh INNER JOIN [' + @DataBase + '].Dbo.kalaid  '   +
	       ' ON [' + @DataBase + '].Dbo.havaleh.k_code = [' + @DataBase + '].Dbo.kalaid.k_code   ' 
	       IF LTrim(RTrim(@StrLink)) <> '0 '
	          Set @Str = @Str + @StrLink + @Str_Anbar2
	
	       Set @Str = @Str +   ' INNER JOIN [' + @DataBase + '].Dbo.headh ON [' + @DataBase + '].Dbo.havaleh.id_havaleh = [' + @DataBase + '].Dbo.headh.id_havaleh ' +
			              '  Where ([' + @DataBase + '].Dbo.HeadH.Kind_Flag ' + @KindBF  

	      IF LTrim(RTrim(@StrAnbar ))<> '0'
	         Set @Str = @Str + @StrAnbar  
	
	     Set @Str = @Str +  '  GROUP BY [' + @DataBase + '].Dbo.kalaid.name1, [' + @DataBase + '].Dbo.havaleh.k_code'     

                   IF LTrim(RTrim(@GroupAnbar)) <>  '0 '
                      Set @Str = @Str + @GroupAnbar 

                   Set @Str = @Str +   ') AS Table1 '   							          +	
    	    ' GROUP BY K_Code, Name1'							          +
	   ') PublicForosh INNER JOIN Anbar '						                        +	
	   'ON PublicForosh.K_Code = Anbar.K_Code INNER JOIN KalaId '		                        +	
	   'ON Anbar.K_Code = KalaId.K_Code INNER JOIN Unit1 '				          +	
	   'ON KalaId.U_Code1 = Unit1.U_Code1 '
                  IF @CheckLevelLink = '1' 
                      Set @Str = @Str + ' ON Level_Link.K_Code = PublicForosh.K_Code     '  
                   Set @Str =  @Str + ' LEFT OUTER JOIN Unit2 ON KalaId.U_Code2 = Unit2.U_Code2  '							          
                   IF @CodeAnbar <> ''
                       Set @Str =  @Str +@CodeAnbar 
              Set @Str = @Str +  
	'GROUP BY PublicForosh.K_Code, PublicForosh.Name1, '					          +	
	'PublicForosh.QtyDay, PublicForosh.Qty_Forosh, '						          +
	'PublicForosh.Avg_Forosh, PublicForosh.SumKolKharid, PublicForosh.SumKolForosh, '	                        +	
	'KalaId.R_Unit1_2, Unit1.Name, Unit2.Name, Anbar.Kala_Min'							          
            IF @CheckLevelLink = '1' 
                Set @Str = @Str + ', Level_Header.Name, Level_Detail1.Name, Level_Detail2.Name'
           Set @Str  = @Str +  
          ') as TotalQuery'	 +									   
         ' ) as RepTotal'
         if @CodeForoshandeh <> ''
           Set @Str = @Str + ' inner join [' + @DataBase +'].Dbo.RegForoshKala  As T on T.k_code = RepTotal.K_Code
                              Where T.Sender = ' + @CodeForoshandeh


 Exec(@Str) 

 Set @Str='Declare @Id2 Smallint '                     + 
                    'Select @id2 = Max(Qtyday) From (' +
                    'SELECT K_Code, Count(K_code) QtyDay'+
                    ' FROM (  '+
                    'SELECT [' + @DataBase + '].Dbo.havaleh.k_code,  '+
	       'COUNT([' + @DataBase + '].Dbo.havaleh.k_qty1) AS Qtyday'+
	       ' FROM [' + @DataBase + '].Dbo.havaleh '+
	       'INNER JOIN [' + @DataBase + '].Dbo.Headh ON [' + @DataBase + '].Dbo.havaleh.id_havaleh = [' + @DataBase + '].Dbo.headh.id_havaleh  '+
	        ' Where ([' + @DataBase + '].Dbo.HeadH.Kind_Flag ' + @KindF  + @KolStrDate+
	       'GROUP BY [' + @DataBase + '].Dbo.Havaleh.k_code, '+
	       'Convert(Varchar(10), ['+ @DataBase + '].Dbo.HeadH.Tarikh_Faktor, 111)'+
	    ') AS Table1  '+
	     'GROUP BY K_Code'+
	     ') As T'+

                    ' Insert  Into ##Avg(K_Code, Name1, QtyDay, Qty_Forosh, Mojodi, KalaMin,  Avg_Forosh_z, 
                    Avg_Forosh_y, SumKolKharid,  SumKolForosh,  Sood, AssetpriceKharid,   AssetpriceForosh,AssetAvgpriceKharid,
                   NameH1, NameL1, Namel2, NameUnit2, Note)'  +

                    'Select RepTotal.K_Code, Name1, QtyDay, 0 Qty_Forosh, Mojodi,  Kala_Min/26 KalaMin, ' +
                    ' 0 As Avg_Forosh_z, '                             +
                    'Avg_Forosh As Avg_Forosh_y,'            +
                   ' SumKolKharid,  SumKolForosh, 0 Sood , AssetpriceKharid,   AssetpriceForosh,AssetAvgpriceKharid  , '
                   IF @CheckLevelLink = '1' 
                      Set @Str = @Str +  ' NameH1, NameL1, Namel2,'
                   Else
                       Set @Str = @Str +  ' '''' NameH1,  '''' NameL1,  '''' Namel2,' 

                  Set @Str = @Str + 
	   ' Case       '                                                                     +
	  '  When NameUnit2 Is Null Then NameUnit1        '   +
	  '   When NameUnit2 Is Not Null Then NameUnit2   '  +
	  'End as NameUnit2,   '                                                 +
	  ' Case        '                                                                    +
	  '    When R_Unit1_2 <> 1 Then (''هر ''  + NameUnit2 + ''معادل'' + Cast(R_Unit1_2 as Varchar(10)) + '' '' + NameUnit1 + ''است'')   '  +

	 ' End as Note  ' +
	 'From ('             +
	      ' Select K_Code, Name1, QtyDay, Qty_Forosh, Avg_Forosh, Mojodi, Kala_Min,' +
	      ' R_Unit1_2,SumKolKharid,  SumKolForosh, '                                                            +
	      ' NameUnit1, NameUnit2,AssetpriceKharid,  AssetpriceForosh,    AssetAvgpriceKharid '  
	    IF @CheckLevelLink = '1' 
                     Set @Str = @Str +  ', NameH1, NameL1, Namel2'
	     Else
	          Set @Str = @Str +  ',''''  NameH1, '''' NameL1, '''' Namel2'
	     Set @Str = @Str + 
	     ' From ( '                                +
	      ' SELECT PublicForosh.K_Code, PublicForosh.Name1, PublicForosh.QtyDay, Anbar.Kala_Min,'                           +
	      ' PublicForosh.Qty_Forosh,  PublicForosh.Avg_Forosh, 0 AS Mojodi,KalaId.R_Unit1_2, '       			   +
	      ' Unit1.Name NameUnit1, Unit2.Name NameUnit2,PublicForosh.SumKolKharid, PublicForosh.SumKolForosh,  ' +
	      ' 0 AS AssetpriceKharid,  '                                                                              +
	      ' 0 AS AssetpriceForosh,  ' 						   +
	      ' 0 AS AssetAvgpriceKharid  ' 
                   IF @CheckLevelLink = '1' 
	       Set @Str = @Str +  ', Level_Header.Name AS NameH1, Level_Detail1.Name AS NameL1, '                                 +	
                                                        ' Level_Detail2.Name AS Namel2'								                 +
   		                            ' FROM    Level_Link INNER JOIN'								                 +
				' Level_Header ON Level_Link.L_Code = Level_Header.L_Code LEFT OUTER JOIN'			    +
				' Level_Detail1 ON Level_Link.L_Code1 = Level_Detail1.Ch_Code AND Level_Header.L_Code = Level_Detail1.Fa_Code LEFT OUTER JOIN' 	   +
				' Level_Detail2 ON Level_Link.L_Code2 = Level_Detail2.Ch_Code AND Level_Detail1.Ch_Code = Level_Detail2.Fa_Code RIGHT OUTER JOIN (' 
                     Else
                         Set @Str = @Str +   ' From ('
	       Set @Str = @Str + 
	      ' SELECT K_Code, Name1, 0 QtyDay, SUM(Qty_Forosh) Qty_Forosh,  '                          +
	      ' SUM(Qty_Forosh) /@id2  Avg_Forosh  ,0 SumKolKharid,  '                 		                 +
	      '0 SumKolForosh '                                                                                                                        +
	      ' FROM (  ' 										   +
	       'SELECT [' + @DataBase + '].Dbo.havaleh.k_code, [' + @DataBase + '].Dbo.kalaid.Name1,  '  +
	       '0 AS Qtyday, '   +
	       'SUM([' + @DataBase + '].Dbo.havaleh.k_qty1) AS Qty_forosh,  '+
	       'Sum([' + @DataBase + '].Dbo.Havaleh.Price_Kharid * [' + @DataBase + '].Dbo.Havaleh.K_Qty1) SumKolKharid,  '  +
	       'Sum([' + @DataBase + '].Dbo.Havaleh.Price_Forosh * [' + @DataBase + '].Dbo.Havaleh.K_Qty1) SumKolForosh  ' +
	       ' FROM [' + @DataBase + '].Dbo.havaleh INNER JOIN [' + @DataBase + '].Dbo.kalaid  ' +
	       ' ON [' + @DataBase + '].Dbo.havaleh.k_code = [' + @DataBase + '].Dbo.kalaid.k_code   ' 

	       IF LTrim(RTrim(@StrLink)) <> '0 '
	           Set @Str = @Str + @StrLink  + @Str_Anbar1

	       Set @Str = @Str  + ' INNER JOIN [' + @DataBase + '].Dbo.Headh ON [' + @DataBase + '].Dbo.havaleh.id_havaleh = [' + @DataBase + '].Dbo.headh.id_havaleh ' +
	        ' Where ([' + @DataBase + '].Dbo.HeadH.Kind_Flag ' + @KindF  + @KolStrDate

                     IF LTrim(RTrim(@StrAnbar)) <> '0 '
                         Set @Str = @Str + @StrAnbar  

                    Set @Str = @Str + ' GROUP BY [' + @DataBase + '].Dbo.kalaid.name1, [' + @DataBase + '].Dbo.Havaleh.k_code, Convert(Varchar(10), ['  + @DataBase + '].Dbo.HeadH.Tarikh_Faktor, 111)'


                    IF LTrim(RTrim(@GroupAnbar)) <>  '0 '
                         Set @Str = @Str + @GroupAnbar 

                     Set @Str = @Str +   ' UNION ALL ' +	 	


	       'SELECT [' + @DataBase + '].Dbo.havaleh.k_code, [' + @DataBase + '].Dbo.kalaid.Name1,  ' +
	       ' 0 AS Qtyday,  - SUM([' + @DataBase + '].Dbo.havaleh.k_qty1) AS qty_forosh,  '                      +
	       ' Sum([' + @DataBase + '].Dbo.Havaleh.Price_Kharid * [' + @DataBase + '].Dbo.Havaleh.K_Qty1) SumKolKharid,  ' +
	       ' Sum([' + @DataBase + '].Dbo.Havaleh.Price_Forosh * [' + @DataBase + '].Dbo.Havaleh.K_Qty1) SumKolForosh ' +
	       ' FROM [' + @DataBase + '].Dbo.havaleh INNER JOIN [' + @DataBase + '].Dbo.kalaid  '   +
	       ' ON [' + @DataBase + '].Dbo.havaleh.k_code = [' + @DataBase + '].Dbo.kalaid.k_code   ' 
	       IF LTrim(RTrim(@StrLink)) <> '0 '
	          Set @Str = @Str + @StrLink + @Str_Anbar2
	
	       Set @Str = @Str +   ' INNER JOIN [' + @DataBase + '].Dbo.headh ON [' + @DataBase + '].Dbo.havaleh.id_havaleh = [' + @DataBase + '].Dbo.headh.id_havaleh ' +
			              '  Where ([' + @DataBase + '].Dbo.HeadH.Kind_Flag ' + @KindBF   + @KolStrDate

	      IF LTrim(RTrim(@StrAnbar ))<> '0'
	         Set @Str = @Str + @StrAnbar  
	
	     Set @Str = @Str +  '  GROUP BY [' + @DataBase + '].Dbo.kalaid.name1, [' + @DataBase + '].Dbo.havaleh.k_code'     

                   IF LTrim(RTrim(@GroupAnbar)) <>  '0 '
                      Set @Str = @Str + @GroupAnbar 

                   Set @Str = @Str +   ') AS Table1 '   							          +	
    	    ' GROUP BY K_Code, Name1'							          +
	   ') PublicForosh INNER JOIN Anbar '						                        +	
	   'ON PublicForosh.K_Code = Anbar.K_Code INNER JOIN KalaId '		                        +	
	   'ON Anbar.K_Code = KalaId.K_Code INNER JOIN Unit1 '				          +	
	   'ON KalaId.U_Code1 = Unit1.U_Code1 '
                  IF @CheckLevelLink = '1' 
                      Set @Str = @Str + ' ON Level_Link.K_Code = PublicForosh.K_Code     '  
                   Set @Str =  @Str + ' LEFT OUTER JOIN Unit2 ON KalaId.U_Code2 = Unit2.U_Code2  '							          
                   IF @CodeAnbar <> ''
                       Set @Str =  @Str +@CodeAnbar 
              Set @Str = @Str +  
	'GROUP BY PublicForosh.K_Code, PublicForosh.Name1, '					          +	
	'PublicForosh.QtyDay, PublicForosh.Qty_Forosh, '						          +
	'PublicForosh.Avg_Forosh, PublicForosh.SumKolKharid, PublicForosh.SumKolForosh, '	                        +	
	'KalaId.R_Unit1_2, Unit1.Name, Unit2.Name, Anbar.Kala_Min'							          
            IF @CheckLevelLink = '1' 
                Set @Str = @Str + ', Level_Header.Name, Level_Detail1.Name, Level_Detail2.Name'
           Set @Str  = @Str +  
          ') as TotalQuery'	 +									   
         ' ) as RepTotal'	
         if @CodeForoshandeh <> ''
           Set @Str = @Str + ' inner join [' + @DataBase +'].Dbo.RegForoshKala  As T on T.k_code = RepTotal.K_Code
                              Where T.Sender = ' + @CodeForoshandeh
         
Exec(@Str)




