ALTER PROCEDURE [dbo].[ReportRMVTotale] 
	@ReportSubscriptionId Int = 1
as
-- Exec [ReportRMVTotale] 15471
--Declare @ReportSubscriptionId Int
--Set @ReportSubscriptionId = 1

Declare
	@FromDate Date = Convert(Date,Getdate()),
	@ToDate Date = Convert(Date,Getdate()),
	@PreviousFromDate Date = Convert(Date,Getdate()),
	@PreviousToDate Date = Convert(Date,Getdate()),
	@StoreClusterId Int = 0,
	@StoreId Int = 0,
	@PreviousDaysOffSet Int,
	@SeasonId Int,
	@RefSeasonId Int

Set DaTeFirst   1

Exec ReportPeriodDecode 
		@ReportSubscriptionId,
		@FromDate=@FromDate Out,
		@ToDate=@ToDate Out,
		@PreviousFromDate=@PreviousFromDate Out,
		@PreviousToDate=@PreviousToDate Out,
		@PreviousDaysOffSet=@PreviousDaysOffSet Out

--SET	@FromDate = Convert(Date,Getdate() - 2)
--SET @ToDate = Convert(Date,Getdate() - 2)

Set @SeasonId = (Select ParameterValue From ReportSubscriptionsParameters Rsp Join ReportParameters Rp On Rp.ReportParameterId = Rsp.ReportParameterId 
			Where Rsp.ReportSubscriptionId = @ReportSubscriptionId and Rp.Name = 'SeasonId')

Set @RefSeasonId = (Select Ref.SeasonId From hqr..Seasons S 
	JOIN HQR..Seasons Ref
		ON Ref.SeasonYear = S.SeasonYear - 1
		AND Ref.SeasonType = S.SeasonType
	WHERE S.SeasonId = @SeasonId) 
	
Select
	@StoreClusterId = ISNULL(StoreClusterId,0)
	,@StoreId = ISNULL(StoreId,0)
From
	ReportSubscriptions Rs 
Where
	Rs.ReportSubscriptionId = @ReportSubscriptionId
		
Select
	S.CompanyId
	,S.StoreId
	,S.Code
	,S.Name
	,S.OpenDate 
INTO
	#SelectedStores
FROM
	StoreClusterDetails Scd 
	JOIN Stores S ON S.StoreId = Scd.StoreId 
WHERE
	Scd.StoreClusterId = @StoreClusterId AND (S.Storeid=@StoreId or @StoreId=0)

--Select	
--	@FromDate as FromDate,
--	@ToDate as ToDate,
--	@PreviousFromDate as PreviousFromDate,
--	@PreviousToDate as PreviousToDate,
--	@Previous as Previous,
--	@Period as Period

--Union
--Select 
--	999 as CompanyId
--	,0 as StoreId
--	,'Tot' as Code
--	,'Totale' as Name


Select 
	S.CompanyId
	,S.StoreId
	,S.Code + ' - ' +S.Name as StoreName
	,S.OpenDate
	,CASE WHEN DATEDIFF(DD,S.OpenDate , CONVERT(DATE,GETDATE()))>365 THEN 'Parità' ELSE '' END AS Parita
	,Year(@ToDate) as Anno
	,Isnull(Ay.QuantitaBuoni,0) as QuantitaBuoni
	,Isnull(Ay.QuantitaProdottiVenduti,0) as QuantitaProdottiVenduti
	,Isnull(Ay.QuantitaProdottiVenduti,0) + Isnull(Ay.QuantitaBuoni,0) as QuantitaTotale
	,Isnull(Ay.QuantitaCarta,0) as QuantitaCarta
	,Isnull(Ay.ApplicatoBuoni,0) as ApplicatoBuoni
	,Isnull(Ay.ApplicatoNettoProdottiVenduti,0) as ApplicatoNettoProdottiVenduti
	,Isnull(Ay.ApplicatoBuoni,0) + Isnull(Ay.ApplicatoNettoProdottiVenduti,0) as ApplicatoNettoTotale
	,Isnull(Ay.ApplicatoCarta,0) as ApplicatoCarta
	,Isnull(Ay.OriginaleBuoni,0) as OriginaleBuoni
	,Isnull(Ay.OriginaleNettoProdottiVenduti,0) as OriginaleNettoProdottiVenduti
	,Isnull(Ay.CostoVendutoNetto,0) as CostoVendutoNetto
	,Isnull(Ay.Righe_Tot,0) as Righe_Tot	
	,Isnull(Ay.Righe_Sco,0) as Righe_Sco
	,CASE WHEN @SeasonId IS NULL THEN Isnull(ScVend.Nr_Scontrini,0) ELSE 0 END as Nr_Scontrini
	,CASE WHEN @SeasonId IS NULL THEN Isnull(ScZ.Nr_Scontrini,0) ELSE 0 END as Nr_ScontriniZero
	,Isnull(Ay.Nr_Giorni,0) as Nr_Giorni

	,Isnull(Ly.QuantitaBuoni,0) as LyQuantitaBuoni
	,Isnull(Ly.QuantitaProdottiVenduti,0) as LyQuantitaProdottiVenduti
	,Isnull(Ly.QuantitaProdottiVenduti,0) + Isnull(Ly.QuantitaBuoni,0) as LyQuantitaTotale
	,Isnull(Ly.QuantitaCarta,0) as LyQuantitaCarta
	,Isnull(Ly.ApplicatoBuoni,0) as LyApplicatoBuoni
	,Isnull(Ly.ApplicatoNettoProdottiVenduti,0) as LyApplicatoNettoProdottiVenduti
	,Isnull(Ly.ApplicatoBuoni,0) + Isnull(Ly.ApplicatoNettoProdottiVenduti,0) as LyApplicatoNettoTotale
	,Isnull(Ly.ApplicatoCarta,0) as LyApplicatoCarta
	,Isnull(Ly.OriginaleBuoni,0) as LyOriginaleBuoni
	,Isnull(Ly.OriginaleNettoProdottiVenduti,0) as LyOriginaleNettoProdottiVenduti
	,Isnull(Ly.CostoVendutoNetto,0) as LyCostoVendutoNetto
	,Isnull(Ly.Righe_Tot,0) as LyRighe_Tot	
	,Isnull(Ly.Righe_Sco,0) as LyRighe_Sco
	,CASE WHEN @SeasonId IS NULL THEN Isnull(LyScVend.Nr_Scontrini,0) ELSE 0 END as LyNr_Scontrini
	,CASE WHEN @SeasonId IS NULL THEN Isnull(LyScZ.Nr_Scontrini,0) ELSE 0 END as LyNr_ScontriniZero
	,Isnull(Ly.Nr_Giorni,0) as LyNr_Giorni
From
	#SelectedStores S
	Left Join
	(
	Select
			M.StoreId,
			Convert(Money,isnull(Sum(Case When Vg.StyleId IS NULL Then 0 Else M.Quantity End * Cs.CauseSign),0))  as QuantitaBuoni,
			Convert(Money,isnull(Sum(Case When Vg.StyleId IS NULL Then M.Quantity Else 0 End * Cs.CauseSign),0))  as QuantitaProdottiVenduti,
			Convert(Money,isnull(Sum(Case When M.FidelityCardId IS NULL Then 0 Else M.Quantity End * Cs.CauseSign),0)) as QuantitaCarta,

			Convert(Money,isnull(Sum(Case When Vg.StyleId IS NULL Then 0 Else M.Quantity End * Cs.CauseSign * NetUnitValueVatIncluded),0)) as ApplicatoBuoni,
			Convert(Money,isnull(Sum(Case When Vg.StyleId IS NULL Then M.Quantity Else 0 End * Cs.CauseSign * NetUnitValueVatIncluded),0))  as ApplicatoNettoProdottiVenduti,

			Convert(Money,isnull(Sum(Case When M.FidelityCardId IS NULL Then 0 Else M.Quantity End * Cs.CauseSign * NetUnitValueVatIncluded),0)) as ApplicatoCarta,

			Convert(Money,isnull(Sum(Case When Vg.StyleId IS NULL Then 0 Else M.Quantity End * Cs.CauseSign * OriginalProductPrice),0)) as OriginaleBuoni,
			Convert(Money,isnull(Sum(Case When Vg.StyleId IS NULL Then M.Quantity Else 0 End * Cs.CauseSign * OriginalProductPrice),0)) as OriginaleNettoProdottiVenduti,

			Convert(Money,isnull(Sum(Case When Vg.StyleId IS NULL Then M.Quantity Else 0 End * Cs.CauseSign * NetUnitCostVatExcluded),0)) as CostoVendutoNetto,
			Count(*) as Righe_Tot,	
			Sum( Case when isnull(M.TotalDiscountValue,0) <> 0 Then 1 Else 0 End ) as Righe_Sco,
			--Count(Distinct M.ReceiptId )as Nr_Scontrini,
			Count(Distinct M.MovementDate )as Nr_Giorni
		From
			hqr..Movements M
				join Causes Cs On Cs.CauseId = M.CauseId
				Left Join VoucherGenerators Vg On Vg.StyleId = M.StyleId
		Where
			M.CauseId In (Select CauseId From Causes Join CauseGroups on Causes.CauseGroupId = CauseGroups.CauseGroupId Where CauseGroups.Code ='BC')
		and 
			M.StoreId IN (SELECT T.StoreId FROM #SelectedStores T)
		and
			M.MovementDate between @FromDate and @ToDate
		and 
			(M.SeasonId = @SeasonId OR @SeasonId IS NULL)
		group by
			M.StoreId
	) Ay
		On ISNULL(Ay.Storeid, 0) =ISNULL(S.StoreId, 0)

	Left Join
	(
		Select
			T.StoreId
			,Count(*) as Nr_Scontrini
		From
			(
			Select
				M.MovementDate, M.StoreId, M.ReceiptNumber, M.CashRegister, Sum(M.Quantity * Cs.CauseSign) as Quantity
			From
				Movements M
					join Causes Cs On Cs.CauseId = M.CauseId
			Where
				M.MovementDate between @FromDate and @ToDate
				and M.CauseId In (Select CauseId From Causes Join CauseGroups on Causes.CauseGroupId = CauseGroups.CauseGroupId Where CauseGroups.Code ='BC')
				and M.StoreId IN (SELECT T.StoreId FROM #SelectedStores T)
			Group by
				M.MovementDate, M.StoreId, M.ReceiptNumber, M.CashRegister 
			Having
				Sum(M.Quantity) = 0
			) T
		Group by (T.StoreId)
	) ScZ 
		On isnull(ScZ.StoreId,0) = isnull(S.StoreId,0)

	
	Left Join
	(
		Select
			R.StoreId
			,Count(DISTINCT R.ReceiptId) as Nr_Scontrini
		From
			Receipts R
			LEFT JOIN Movements M ON M.ReceiptId = R.ReceiptId		-- Left Join to consider Financial Receipts that have no Movements
		WHERE
			1=1
			and R.StoreId IN (SELECT T.StoreId FROM #SelectedStores T)
			and R.Day between @FromDate and @ToDate
			and R.TransactionSign = 1
			and (@SeasonId IS NULL OR M.SeasonId = @SeasonId)
		GROUP BY
			R.StoreId
	) ScVend
		On isnull(ScVend.StoreId,0) = isnull(S.StoreId,0)

	Left Join 
		(
		Select 
			M.StoreId,
			Convert(Money,isnull(Sum(Case When Vg.StyleId IS NULL Then 0 Else Quantity End * Cs.CauseSign),0))  as QuantitaBuoni,
			Convert(Money,isnull(Sum(Case When Vg.StyleId IS NULL Then Quantity Else 0 End * Cs.CauseSign),0))  as QuantitaProdottiVenduti,
			Convert(Money,isnull(Sum(Case When M.FidelityCardId IS NULL Then 0 Else Quantity End * Cs.CauseSign),0))as QuantitaCarta, -- Aggiunto test su colonna Notes per i record importati da winmaster 2016 (04/11/2017 AB)

			Convert(Money,isnull(Sum(Case When Vg.StyleId IS NULL Then 0 Else Quantity End * Cs.CauseSign * NetUnitValueVatIncluded),0)) as ApplicatoBuoni,
			Convert(Money,isnull(Sum(Case When Vg.StyleId IS NULL Then Quantity Else 0 End * Cs.CauseSign * NetUnitValueVatIncluded),0))  as ApplicatoNettoProdottiVenduti,

			Convert(Money,isnull(Sum(Case When M.FidelityCardId IS NULL Then 0 Else Quantity End * Cs.CauseSign * NetUnitValueVatIncluded),0)) as ApplicatoCarta, -- Aggiunto test su colonna Notes per i record importati da winmaster 2016 (04/11/2017 AB)

			Convert(Money,isnull(Sum(Case When Vg.StyleId IS NULL Then 0 Else Quantity End * Cs.CauseSign * OriginalProductPrice),0)) as OriginaleBuoni,
			Convert(Money,isnull(Sum(Case When Vg.StyleId IS NULL Then Quantity Else 0 End * Cs.CauseSign * OriginalProductPrice),0)) as OriginaleNettoProdottiVenduti,

			Convert(Money,isnull(Sum(Case When Vg.StyleId IS NULL Then Quantity Else 0 End * Cs.CauseSign * NetUnitCostVatExcluded),0)) as CostoVendutoNetto,
			Count(*) as Righe_Tot,	
			Sum( Case when isnull(M.TotalDiscountValue,0) <> 0 Then 1 Else 0 End ) as Righe_Sco,
			--Count(Distinct M.ReceiptId )as Nr_Scontrini,
			Count(Distinct M.MovementDate )as Nr_Giorni
		From
			hqr..Movements M
				join Causes Cs On Cs.CauseId = M.CauseId
				Left Join VoucherGenerators Vg On Vg.StyleId = M.StyleId
		Where
			M.CauseId In (Select CauseId From Causes Join CauseGroups on Causes.CauseGroupId = CauseGroups.CauseGroupId Where CauseGroups.Code ='BC')
		and 
			M.StoreId IN (SELECT T.StoreId FROM #SelectedStores T)
		and
			M.MovementDate between @PreviousFromDate and @PreviousToDate
		and 
			(M.SeasonId = @RefSeasonId OR @RefSeasonId IS NULL)
		group by  
			M.StoreId
		) Ly
		On isnull(Ly.Storeid,0) = isnull(S.StoreId,0)
		
	Left Join
	(
		Select
			T.StoreId
			,Count(*) as Nr_Scontrini
		From
			(
			Select
				M.MovementDate, M.StoreId, M.ReceiptNumber, M.CashRegister, Sum(M.Quantity * Cs.CauseSign) as Quantity
			From
				Movements M
					join Causes Cs On Cs.CauseId = M.CauseId
			Where
				M.MovementDate between @PreviousFromDate and @PreviousToDate
				and M.CauseId In (Select CauseId From Causes Join CauseGroups on Causes.CauseGroupId = CauseGroups.CauseGroupId Where CauseGroups.Code ='BC')
				and M.StoreId IN (SELECT T.StoreId FROM #SelectedStores T)
			Group by
				M.MovementDate, M.StoreId, M.ReceiptNumber, M.CashRegister
			Having
				Sum(M.Quantity) = 0
			) T
		Group by 
			(T.StoreId)
	) LyScZ 
		On isnull(LyScZ.StoreId,0) = isnull(S.StoreId,0)
	
	Left Join
	(
		Select
			R.StoreId
			,Count(DISTINCT R.ReceiptId) as Nr_Scontrini
		From
			Receipts R
			LEFT JOIN Movements M ON M.ReceiptId = R.ReceiptId	-- Left Join to consider Financial Receipts that have no Movements
		WHERE
			1=1
			and R.StoreId IN (SELECT T.StoreId FROM #SelectedStores T)
			and R.Day between @PreviousFromDate and @PreviousToDate
			and R.TransactionSign = 1
			and (@RefSeasonId IS NULL OR M.SeasonId = @RefSeasonId)
		GROUP BY
			R.StoreId
	) LyScVend
		On isnull(LyScVend.StoreId,0) = isnull(S.StoreId,0)

Order by
	--isnull(S.CompanyId,999999),
	S.Name

Drop Table #SelectedStores