CREATE PROCEDURE [dbo].[Sp_SleepKala]  @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)
AS 

Declare @Str Varchar(8000)

Set @Str = ' Select K_Code, Name1, Name2, QtyDay, Qty_Forosh, RepTotal.Price_Kharid,' + @StrFieldMojodi+ 
                    ', Pishnahad,SumKolKharid,  SumKolForosh, SumKolForosh - SumKolKharid Sood ,
                    AssetpriceKharid,  AssetpriceForosh,AssetAvgpriceKharid  , 0.0 As KalaMin, 0.0 As Avg_Forosh_Z, 0.0 As Avg_Forosh_Y, ' 
	     IF @CheckLevelLink = '1' 
                Set @Str = @Str +  ' NameH1, NameL1, Namel2,'
            Else
                Set @Str = @Str +  ' '''' NameH1,  '''' NameL1,  '''' Namel2,' 

            Set @Str = @Str + 
                ' Case  
   	            When QtyDay = 0  then 0  
  	            When QtyDay <> 0  then Avg_Forosh 
     	        End As Avg_Forosh, 
	            Case   
                  When QtyDay = 0  Then 0
                  When QtyDay <> 0  And Avg_Forosh = 0  Then Round(Mojodi,0,1) 
                  When QtyDay <> 0 And Avg_Forosh <> 0  Then Round(Mojodi/Avg_Forosh,0,1) 
                End as SleepKala,
                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, R_Unit1_2 
                From (
       		    Select K_Code, Name1, Name2,QtyDay, Qty_Forosh, Avg_Forosh, Mojodi, TotalQuery.Price_Kharid,R_Unit1_2,SumKolKharid, 
       		    SumKolForosh, Round((' + @Qty + @StrMojodi + ' Pishnahad, 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.Name2,PublicForosh.QtyDay, PublicForosh.Qty_Forosh, 
			        PublicForosh.Avg_Forosh, SUM(Anbar.Mojodi) AS Mojodi,SUM(Price_Kharid)/COUNT(Price_Kharid) Price_Kharid,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 WITH (NOLOCK) INNER JOIN 
                      			Level_Header WITH (NOLOCK) ON Level_Link.L_Code = Level_Header.L_Code LEFT OUTER JOIN
                      			Level_Detail1 WITH (NOLOCK) ON Level_Link.L_Code1 = Level_Detail1.Ch_Code AND Level_Header.L_Code = Level_Detail1.Fa_Code LEFT OUTER JOIN
                      			Level_Detail2 WITH (NOLOCK) 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,Name2,Count(K_code) QtyDay, SUM(Qty_Forosh) Qty_Forosh, '	+ @StrCase  +
			       '  ,Sum(SumKolKharid) SumKolKharid, 
			       Sum(SumKolForosh) SumKolForosh 
		         FROM ( 
			          SELECT [' + @DataBase + '].Dbo.havaleh.K_Code, [' + @DataBase + '].Dbo.kalaid.Name1, [' + @DataBase + '].Dbo.kalaid.Name2, 
			          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 WITH (NOLOCK) INNER JOIN [' + @DataBase + '].Dbo.kalaid WITH (NOLOCK) 
			          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 WITH (NOLOCK) 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.kalaid.name2, 
 	                   [' + @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,[' + @DataBase + '].Dbo.kalaid.Name2, 
			         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 WITH (NOLOCK) INNER JOIN [' + @DataBase + '].Dbo.kalaid WITH (NOLOCK) 
					 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 WITH (NOLOCK) 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.kalaid.name2,[' + @DataBase + '].Dbo.havaleh.k_code'

      				IF LTrim(RTrim(@GroupAnbar)) <>  '0 '
					Set @Str = @Str + @GroupAnbar 
 			
					Set @Str = @Str +  ') AS Table1 
										GROUP BY K_Code, Name1,Name2
										) PublicForosh INNER JOIN Anbar WITH (NOLOCK) 
       									ON PublicForosh.K_Code = Anbar.K_Code INNER JOIN KalaId WITH (NOLOCK) 
										ON Anbar.K_Code = KalaId.K_Code INNER JOIN Unit1 WITH (NOLOCK) 
       									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 WITH (NOLOCK) 
			                                                       ON KalaId.U_Code2 = Unit2.U_Code2 '							          
                                               IF @CodeAnbar <> ''
                                                   Set @Str =  @Str +@CodeAnbar 
                                              Set @Str = @Str +  
											 'GROUP BY PublicForosh.K_Code, PublicForosh.Name1, PublicForosh.Name2,
											 PublicForosh.QtyDay, PublicForosh.Qty_Forosh, 
											 PublicForosh.Avg_Forosh, PublicForosh.SumKolKharid, PublicForosh.SumKolForosh, 
											 KalaId.R_Unit1_2, Unit1.Name, Unit2.Name'							          
	  	     	     IF @CheckLevelLink = '1' 
                       SET @Str = @Str + ', Level_Header.Name, Level_Detail1.Name, Level_Detail2.Name'
			    Set @Str  = @Str + 
			     ') as TotalQuery
                  ) as RepTotal'											

EXEC (@str)


