
/****** Object:  StoredProcedure [dbo].[Rep_05228553SF1]    Script Date: 04/17/2018 10:10:55 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rep_05228553SF1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Rep_05228553SF1]
GO



/****** Object:  StoredProcedure [dbo].[Rep_05228553SF1]    Script Date: 04/17/2018 10:10:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*
@S_PHI=forms![TreeViewRep_HesabAnbar]![Sth_PHI].column(2),
@SortRep=forms![TreeViewRep_HesabAnbar]![Sorting],
@KalaID =forms![TreeViewRep_HesabAnbar]![KalaID],
@NameKala =forms![TreeViewRep_HesabAnbar]![FilterNameKala],
@GbyKalaID =forms![TreeViewRep_HesabAnbar]![ChGbyKalaID],
@Gokid  =forms![TreeViewRep_HesabAnbar]![GoKala],
@NameGokid =forms![TreeViewRep_HesabAnbar]![FilterNameGOKALA],
@GbyGokid =forms![TreeViewRep_HesabAnbar]![ChGbyGokid],
@Aid  =forms![TreeViewRep_HesabAnbar]![AID],
@NameAid =forms![TreeViewRep_HesabAnbar]![FilterNameAID],
@GbyAid =forms![TreeViewRep_HesabAnbar]![ChGbyAid]
*/

  CREATE PROCEDURE [dbo].[Rep_05228553SF1] ( 
	@S_PHI int,/****** براساس آخرین خرید (1)  قیمت سطح 1 (2) قیمت سطح 2 (3) قیمت سطح 4 (5) قیمت سطح 5 (6) قیمت سطح 6 (6)قیمت *******/
	@SortRep int, 
	@KalaID int,
	@NameKala nvarchar(255), 
	@GbyKalaID AS BIT,
	@Gokid int,
	@NameGokid nvarchar(255), 
	@GbyGokid AS BIT,
	@Aid int,
	@NameAid  nvarchar(255),
	@GbyAid AS BIT)           
  AS     
  BEGIN 	SET NOCOUNT ON; 
  	 	
  DECLARE @SQLString  NVARCHAR(Max); 
  DECLARE @SQL_SELECT  NVARCHAR(Max); 
  DECLARE @SQL_FROM NVARCHAR(Max); 
  DECLARE @SQL_WHERE  NVARCHAR(Max); 
  DECLARE @SQL_GROUP  NVARCHAR(Max); 
  DECLARE @SQL_HAVING  NVARCHAR(Max); 
  DECLARE @SQL_ORDER NVARCHAR(Max); 
  	
  DECLARE @WhrStatement  NVARCHAR(Max);         	
  DECLARE @ParmDefinition  NVARCHAR(Max);         	
  DECLARE @ParmAndstr  NVARCHAR(Max); 
  
  set @SQL_SELECT=N' ';
  Set @SQL_FROM=N' ';
  Set @SQL_WHERE=N' ';
  Set @SQL_GROUP=N' ';
  Set @SQL_ORDER=N' ';	
  set @WhrStatement  = N' ';         	
  set @ParmAndstr = N' ';  
  SEt @SQL_HAVING =N' HAVING      (SUM(CONVERT(float, SubTransactionKala_1.TedadVarde - SubTransactionKala_1.TedadSadre)) <> 0) ';
  
  SET @ParmDefinition =N'@S_PHI int=null,
						@GbyKalaID AS BIT=null,
						@GbyGokid AS BIT=null,
						@SortRep int=null, 
						@KalaID int=null,
						@NameKala nvarchar(255)=null, 
						@Gokid int=null,
						@NameGokid nvarchar(255)=null, 
						@Aid int=null,
						@NameAid  nvarchar(255)=null,
						@GbyAid bit=1';            	
  /* Build the SQL string one time. */      /* Specify the parameter format one time. */  		

  BEGIN 
  

  
   if (@S_PHI IS NULL)
		Set @S_PHI=1
		
  if (@S_PHI=1)
	Set @SQL_SELECT=N' , SUM(CONVERT (float, SubTransactionKala_1.TedadVarde - SubTransactionKala_1.TedadSadre)) * (MAX(ISNULL(Fun_SubAkharinKharid_1.MaxUnitPriceKh, 0))+Max(ISNULL(FunInfoKalaAll_1.UnitPrice2, 0))) AS SumMabUnit, MAX(ISNULL(Fun_SubAkharinKharid_1.MaxUnitPriceKh, 0))+MAX(ISNULL(FunInfoKalaAll_1.UnitPrice2, 0)) as MabUnit  '
  if (@S_PHI=2)
	Set @SQL_SELECT=N' , SUM(CONVERT (float, SubTransactionKala_1.TedadVarde - SubTransactionKala_1.TedadSadre)) * MAX(ISNULL(FunInfoKalaAll_1.UnitPrice1, 0)) AS SumMabUnit, MAX(ISNULL(FunInfoKalaAll_1.UnitPrice1, 0)) as MabUnit '
  if (@S_PHI=3)
	Set @SQL_SELECT=N' , Sum(CONVERT (float, SubTransactionKala_1.TedadVarde - SubTransactionKala_1.TedadSadre)) * Max(ISNULL(FunInfoKalaAll_1.UnitPrice2, 0)) AS SumMabUnit, MAX(ISNULL(FunInfoKalaAll_1.UnitPrice2, 0)) as MabUnit  '	
  if (@S_PHI=4)
	Set @SQL_SELECT=N' , Sum(CONVERT (float, SubTransactionKala_1.TedadVarde - SubTransactionKala_1.TedadSadre)) * MAX(ISNULL(FunInfoKalaAll_1.UnitPrice3, 0)) AS SumMabUnit , MAX(ISNULL(FunInfoKalaAll_1.UnitPrice3, 0)) as MabUnit '
  if (@S_PHI=5)
	Set @SQL_SELECT=N' , SUM(CONVERT (float, SubTransactionKala_1.TedadVarde - SubTransactionKala_1.TedadSadre)) * MAX(ISNULL(FunInfoKalaAll_1.UnitPrice4, 0)) AS SumMabUnit, MAX(ISNULL(FunInfoKalaAll_1.UnitPrice4, 0)) as MabUnit  '	
  if (@S_PHI=6)
	Set @SQL_SELECT=N' , SUM(CONVERT (float, SubTransactionKala_1.TedadVarde - SubTransactionKala_1.TedadSadre)) * Max(ISNULL(FunInfoKalaAll_1.UnitPrice5, 0)) AS SumMabUnit , MAX(ISNULL(FunInfoKalaAll_1.UnitPrice5, 0) as MabUnit '
  if (@S_PHI=7)
	Set @SQL_SELECT=N' , SUM(CONVERT (float, SubTransactionKala_1.TedadVarde - SubTransactionKala_1.TedadSadre)) * max(ISNULL(FunInfoKalaAll_1.UnitPrice6, 0)) AS SumMabUnit , MAX(ISNULL(FunInfoKalaAll_1.UnitPrice6, 0)) as MabUnit '	
	

		/* شرط انطباق انبار*/
  
  if (@GbyAid=1) 
			Begin
				Set @SQL_SELECT=@SQL_SELECT + N' ,FunInfoKalaAll_1.AID, FunInfoKalaAll_1.AIDNumber, FunInfoKalaAll_1.NameAnbar '
				Set @SQL_GROUP=@SQL_GROUP + N' ,FunInfoKalaAll_1.AID, FunInfoKalaAll_1.AIDNumber , FunInfoKalaAll_1.NameAnbar ' 
				
				if(NOT (@Aid IS NULL)) 
					Begin
						if(@SQL_HAVING=N' ')
							set @ParmAndstr=N' HAVING ' 	
						else
							set @ParmAndstr=N' AND '
							
						set @SQL_HAVING= @SQL_HAVING + @ParmAndstr + N' ( FunInfoKalaAll_1.AID = @Aid )'; 
					end

				if(NOT (@NameAid IS NULL)) 
					Begin
						if(@SQL_HAVING=N' ')
							set @ParmAndstr=N' HAVING ' 	
						else
							set @ParmAndstr=N' AND '
							
						Set @NameAid='%'+@NameAid+'%'
						Set @SQL_HAVING= @SQL_HAVING + @ParmAndstr + N' (FunInfoKalaAll_1.NameAnbar LIKE @NameAid) '; 	
					End
			end
  else
		Begin
			if(NOT (@Aid IS NULL)) 
					Begin
						if(@SQL_WHERE=N' WHERE ')
							set @ParmAndstr=N' ' 	
						else
							set @ParmAndstr=N' AND '

						set @SQL_WHERE= @SQL_WHERE + @ParmAndstr + N'( FunInfoKalaAll_1.AID = @Aid )'; 
					End					

			if(NOT (@NameAid IS NULL)) 
					Begin
						if(@SQL_WHERE=N' ')
							set @ParmAndstr=N' WHERE ' 	
						else
							set @ParmAndstr=N' AND '
							
						Set @NameAid='%'+@NameAid+'%'
						
						Set @SQL_WHERE= @SQL_WHERE + @ParmAndstr + N' (FunInfoKalaAll_1.NameAnbar LIKE @NameAid) '; 
					End						
		End  			
		
 
   /* --------------------شرط انطباق نام کالا*/  
  if(NOT (@KalaID IS NULL)) 
			Begin
				if(@SQL_HAVING=N' ')
					set @ParmAndstr=N' HAVING ' 	
				else
					set @ParmAndstr=N' AND '
					
					
				set @SQL_HAVING= @SQL_HAVING + @ParmAndstr + N'   (FunInfoKalaAll_1.ID = @KalaID ) ' 
								
			End
     
    /* --------------------شرط انطباق قسمتی از نام کالا*/ 			
  if(NOT (@NameKala IS NULL)) 
		Begin
			if(@SQL_HAVING=N' ')
				set @ParmAndstr=N' HAVING ' 	
			else
				set @ParmAndstr=N' AND '
				
			Set @NameKala='%'+@NameKala+'%'
			set @SQL_HAVING= @SQL_HAVING + @ParmAndstr + N' (FunInfoKalaAll_1.NameKala1 LIKE @NameKala) '; 	
		End

	/* --------------------شرط انطباق گروه*/ 			

	if(NOT (@Gokid IS NULL)) 
		Begin
			if(@SQL_HAVING=N' ')
				set @ParmAndstr=N' HAVING ' 	
			else
				set @ParmAndstr=N' AND '
				
			set @SQL_HAVING= @SQL_HAVING + @ParmAndstr + N'(FunInfoKalaAll_1.GOKID = @Gokid )'; 	
		End
  
      /* --------------------شرط انطباق قسمتی از نام گروه کالا*/ 			

	if(NOT (@NameGokid IS NULL)) 
		Begin
			if(@SQL_HAVING=N' ')
				set @ParmAndstr=N' HAVING ' 	
			else
				set @ParmAndstr=N' AND '
				
			Set @NameGokid='%'+@NameGokid+'%'
			Set @SQL_HAVING= @SQL_HAVING + @ParmAndstr + N' (FunInfoKalaAll_1.NameGoKala1 LIKE @NameGokid) '; 
		End	
 

  Set @SQLString =N'SELECT     FunInfoKalaAll_1.ID, FunInfoKalaAll_1.KalaNumber, FunInfoKalaAll_1.NameKala1, FunInfoKalaAll_1.GOKID, FunInfoKalaAll_1.NameGoKalanumbr, FunInfoKalaAll_1.NameGoKala1, 
                      FunInfoKalaAll_1.NameVahed1, FunInfoKalaAll_1.NameVahed2, SUM(CONVERT(float, SubTransactionKala_1.TedadVarde - SubTransactionKala_1.TedadSadre)) AS SumTedad, 
                      FunInfoKalaAll_1.VKalaID, FunInfoKalaAll_1.Active, MAX(Fun_SubAkharinKharid_1.MaxUnitPriceKh) AS Max_UnitPriceKh, MAX(FunInfoKalaAll_1.UnitPrice1) AS MaxUnitPrice1, 
                      MAX(FunInfoKalaAll_1.UnitPrice2) AS MaxUnitPrice2, MAX(FunInfoKalaAll_1.UnitPrice3) AS MaxUnitPrice3, MAX(FunInfoKalaAll_1.UnitPrice4) AS MaxUnitPrice4, MAX(FunInfoKalaAll_1.UnitPrice5) 
                      AS MaxUnitPrice5, MAX(FunInfoKalaAll_1.UnitPrice6) AS MaxUnitPrice6, FunInfoKalaAll_1.NameAnbar, FunInfoKalaAll_1.note, FunInfoKalaAll_1.BarCode
'+ @SQL_SELECT + 'FROM         dbo.Fun_SubAkharinKharid(0) AS Fun_SubAkharinKharid_1 RIGHT OUTER JOIN
                      dbo.FunInfoKalaAll(0) AS FunInfoKalaAll_1 ON Fun_SubAkharinKharid_1.LinkKalaAid = FunInfoKalaAll_1.LinkKalaAid  LEFT OUTER JOIN
                      (SELECT     KALAID, GOKID, AID, SUM(TedadVarde) AS TedadVarde, SUM(TedadSadre) AS TedadSadre, LinkKalaAid
FROM         View_SubTransactionKala AS SubTransactionKala_1
GROUP BY KALAID, GOKID, AID, LinkKalaAid) AS SubTransactionKala_1 ON FunInfoKalaAll_1.LinkKalaAid = SubTransactionKala_1.LinkKalaAid 
 ' +@SQL_WHERE + '                      
GROUP BY FunInfoKalaAll_1.ID, FunInfoKalaAll_1.KalaNumber, FunInfoKalaAll_1.NameKala1, FunInfoKalaAll_1.GOKID, FunInfoKalaAll_1.NameGoKalanumbr, FunInfoKalaAll_1.NameGoKala1, 
                      FunInfoKalaAll_1.NameVahed1, FunInfoKalaAll_1.NameVahed2, FunInfoKalaAll_1.VKalaID, FunInfoKalaAll_1.Active, FunInfoKalaAll_1.NameAnbar, FunInfoKalaAll_1.note, FunInfoKalaAll_1.BarCode ' + @SQL_GROUP + @SQL_HAVING




                      


		    /* --------------------    1مرتب سازی */ 			
    if(@SortRep=1) 	
			set @SQLString= @SQLString  + N'  ORDER BY KalaNumber,NameKala1 '; 	
    		    /* --------------------    2مرتب سازی */ 			
    if(@SortRep=2) 	
			set @SQLString= @SQLString + N'  ORDER BY NameKala1,KalaNumber '; 
    		    /* --------------------    3مرتب سازی */ 			
    if(@SortRep=3) 	
			set @SQLString= @SQLString + N'  ORDER BY NameGoKalanumbr,NameKala1 '; 
    		    /* --------------------    4مرتب سازی */ 			
    if(@SortRep=4) 	
			set @SQLString= @SQLString + N'  ORDER BY NameGoKala1,NameKala1 '; 	
        		    /* --------------------    5مرتب سازی */ 			
	 IF ((@SortRep=5) AND (@GbyAid=1)) 	
			set @SQLString= @SQLString + N'  ORDER BY AIDNumber,NameKala1 '; 
    		    /* --------------------    6مرتب سازی */ 			
    IF ((@SortRep=6) AND (@GbyAid=1))	
			set @SQLString= @SQLString + N' ORDER BY NameAnbar,NameKala1 '; 		


    
    EXECUTE sp_executesql @SQLString, @ParmDefinition,@GbyKalaID=@GbyKalaID,@GbyGokid=@GbyGokid,@SortRep=@SortRep,@S_PHI=@S_PHI,@KalaID=@KalaID,@NameKala=@NameKala,@Gokid=@Gokid,@NameGokid=@NameGokid,@Aid=@Aid,@NameAid=@NameAid,@GbyAid=@GbyAid;  		
    END 		 
    /* --------پایان */  		 		 
return END      

GO




/****** Object:  StoredProcedure [dbo].[PFrm_SubRepSoodSF1]    Script Date: 04/18/2018 11:00:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PFrm_SubRepSoodSF1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PFrm_SubRepSoodSF1]
GO

/****** Object:  StoredProcedure [dbo].[PFrm_SubRepSoodSF1]    Script Date: 04/18/2018 11:00:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PFrm_SubRepSoodSF1](
	@KalaID NVARCHAR(MAX), 
	@GOKID NVARCHAR(MAX), 
	@AID NVARCHAR(MAX), 
	@startDate nvarchar(50), 
	@EndDate nvarchar(50)
) AS 

BEGIN
	
SET NOCOUNT ON; 
DECLARE @SQLQuery AS NVarchar(MAX) 
DECLARE @ParamDefinition AS NVarchar(MAX) 
DECLARE @OrderBy AS NVARCHAR(MAX) 
DECLARE @GROUPBy AS NVARCHAR(MAX) 

IF (@startDate IS NULL)
	SET @startDate = '00000000' 
IF (@EndDate IS NULL)
	SET @EndDate = '99999999'
	
SET @SQLQuery = ' 
SELECT     SUM(View_SubTransactionKala.FieldN5) AS Sood, T_NameKala.ID, PFrm_Rep_SoodKala_Mojoodi.Mojoodi, T_NameKala.KalaNumber, T_NameKala.NameKala1, T_NameKala.BarCode, 
                      @KalaID AS Expr1, SUM(CONVERT(float, View_SubTransactionKala.TedadFo)) AS SumForoosh, SUM(CONVERT(float, View_SubTransactionKala.TedadKh)) AS SumKharid, 
                      SUM(View_SubTransactionKala.TedadKh *  (View_SubTransactionKala.UnitPriceKH + T_NameKala.UnitPrice2)) / CASE WHEN SUM(CONVERT(float, View_SubTransactionKala.TedadKh)) > 0 THEN SUM(CONVERT(float, 
                      View_SubTransactionKala.TedadKh)) ELSE 1 END AS MianginKharid, (SUM(View_SubTransactionKala.TedadFo * View_SubTransactionKala.UnitpriceFo) / CASE WHEN SUM(CONVERT(float, 
                      View_SubTransactionKala.TedadFo)) > 0 THEN SUM(CONVERT(float, View_SubTransactionKala.TedadFo)) ELSE 1 END) AS MianginForoosh, 
                      CASE WHEN SUM(dbo.View_SubTransactionKala.fieldn5) > 0 THEN SUM(dbo.View_SubTransactionKala.fieldn5) ELSE 0 END AS sod, CASE WHEN SUM(dbo.View_SubTransactionKala.fieldn5) 
                      < 0 THEN abs(SUM(dbo.View_SubTransactionKala.fieldn5)) ELSE 0 END AS zian, SUM(CONVERT(float, View_SubTransactionKala.TedadBarFo)) AS SumBarForoosh, SUM(CONVERT(float, 
                      View_SubTransactionKala.TedadBarKh)) AS SumBarKharid, @startDate AS startDate, @EndDate AS EndDate, SUM(View_SubTransactionKala.FieldN5) / CASE WHEN (SUM(CONVERT(float, 
                      View_SubTransactionKala.TedadFo))) = 0 THEN 1 ELSE (SUM(CONVERT(float, View_SubTransactionKala.TedadFo))) END AS SoodTak
FROM         View_SubTransactionKala INNER JOIN
                      T_NameKala ON View_SubTransactionKala.KALAID = T_NameKala.ID INNER JOIN
                      PFrm_Rep_SoodKala_Mojoodi ON T_NameKala.ID = PFrm_Rep_SoodKala_Mojoodi.KALAID
WHERE     (View_SubTransactionKala.Tarikh BETWEEN @startDate AND @EndDate) AND (ISNULL(T_NameKala.FlagNoeKala, 0) = 0)

 '
SET @GROUPBy = ' GROUP BY T_NameKala.KalaNumber, T_NameKala.NameKala1, T_NameKala.BarCode, PFrm_Rep_SoodKala_Mojoodi.Mojoodi, T_NameKala.ID '
SET @OrderBy = '  HAVING      (SUM(CONVERT(float, View_SubTransactionKala.TedadFo)) <> 0) ORDER BY T_NameKala.NameKala1 ' 

IF (@KalaID IS NOT NULL)
	SET @SQLQuery = @SQLQuery + ' And (View_SubTransactionKala.KALAID IN(' + @KalaID + '))' 

IF (@GOKID IS NOT NULL)
	SET @SQLQuery = @SQLQuery + ' And (View_SubTransactionKala.GOKID IN(' + @GOKID + '))' 

IF (@AID IS NOT NULL)
	SET @SQLQuery = @SQLQuery + ' And (View_SubTransactionKala.AID IN(' + @AID + ') )'

SET @SQLQuery = @SQLQuery + ' '
	SET @ParamDefinition = '@KalaID NVARCHAR(MAX), 
							@GOKID NVARCHAR(MAX), 
							@AID NVARCHAR(MAX),
							@startDate nvarchar(50), 
							@EndDate nvarchar(50) '
SET               @SQLQuery = @SQLQuery + char(10) + @GROUPBy + char(10) + @OrderBy 

EXECUTE sp_Executesql @SQLQuery, @ParamDefinition, @KalaID, @GOKID, @AID, @startDate,@EndDate 

SET NoCount OFF

 END
GO



