set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[PRC_MI_DailySummary_ProviderStats_SEL]
 -------------------------------------------------------------------------------
    -- Modified By/Date
    -- 
    -------------------------------------------------------------------------------
    -- Accepts a date
    -- Accepts DealerId for Dealer viewing, can be null for Console viewing of all dealers
    -- Returns statistics for given dealer or all dealers if DealerId is Null for the given date. 
    -------------------------------------------------------------------------------
    -- 
    -------------------------------------------------------------------------------
 @InputDate DateTime,
 @DealerId As UDT_Id = Null
As
SET DATEFORMAT DMY
DECLARE @SQL_Select As nVarchar(500)
DECLARE @SQL_From As nVarchar(2000)
DECLARE @SQL_Where As nVarchar(100)
DECLARE @SQL_Order as nVarchar(50)
DECLARE @SQL_Statement As nVarchar(4000)
DECLARE @SearchDate Varchar(10)
SELECT @SearchDate = CONVERT(VARCHAR(10),@InputDate,103)
SET @SQL_Select =
N' SELECT  
 
 CAST(dp.Name AS VARCHAR(20)) AS Provider, 
 dd.Name As Dealer,
 ba.TotalNew + 
 ba.TotalDuplicated +
 ba.TotalUpdated As Successful,
             ba.TotalRejected As Rejected,
 ba.TotalNew +
 ba.TotalDuplicated + 
 ba.TotalUpdated + 
             ba.TotalRejected As TotalUploaded,
 ba.CreatedDate '
SET @SQL_From =
N' FROM    dbo.MI_TransactionLog tl
 INNER JOIN dbo.MI_BroadcastActivity ba
 ON tl.TransactionLogId = ba.TransactionLogId 
 INNER JOIN dbo.MI_DataProvider dp
 ON tl.DataProviderId = dp.DataProviderId 
 INNER JOIN dbo.MI_DealerActivity da
 ON da.BroadcastActivityId = ba.BroadcastActivityId
 INNER JOIN dbo.MI_DealerDetails dd
 ON dd.DealerId = da.DealerId '

SET @SQL_Where = N'  '
SET @SQL_Where = @SQL_Where + N' WHERE CONVERT(Varchar(10),tl.CreatedDate,103) = '
 +  char(39) + @SearchDate  + char(39) + ' '
IF @DealerId IS NOT NULL
BEGIN
 SET @SQL_Where = @SQL_Where + N' AND da.DealerId = ' + CAST(@DealerId AS nVarchar(12))
END
IF @DealerId IS  NULL
BEGIN
 SET @SQL_Order = N' ORDER BY dd.Name,dp.Name '
END
ELSE
BEGIN
 SET @SQL_Order = N' ORDER BY dp.Name '
END
SET @SQL_Statement = @SQL_Select + @SQL_From + @SQL_Where + @SQL_Order
EXECUTE sp_executesql   @SQL_Statement