USE [ABC]
GO
/****** Object:  UserDefinedFunction [dbo].[fnReportDealCoreFee_OPTIMIZED]    Script Date: 6/18/2018 5:05:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnReportDealCoreFee_OPTIMIZED] (@StartDate DATETIME, @EndDate DATETIME, @Type INT)
 RETURNS TABLE /*(DealID INT, StageID INT, DealName VARCHAR(50), VolumeCompleted MONEY, VolumePipeline MONEY, TotalFee MONEY,
  isCompletedYTD BIT, DealOfficeID INT, DealDepartmentID INT, isPipeline BIT, isCompleted BIT, isQuote BIT, isApplication BIT, isCommitment BIT,
 isFeeSplit BIT, PaidFeesTotal MONEY, PaidFeesYTD MONEY, AllocatedFee MONEY, AllocatedOfficeID INT, AllocatedDepartmentID INT, FeeCalcCompleted MONEY,
 FeeCalcPipeline MONEY, CountDealCompleted INT, CountDealPipeline INT, DealOfficeName VARCHAR(50), AllocatedOfficeName VARCHAR(50), DealTypeID INT,
 PropertyTypeID INT, CityID INT, AmountReceivable MONEY,LastPaymentDate DATETIME)*/
 AS

 --DECLARE @StartDate DATETIME = '1/1/2016'
 --DECLARE @EndDate DATETIME = '12/31/2016'
 --DECLARE @Type INT = -1

--if (@StartDate is null)
--  select @StartDate = convert(datetime, '1/1/'+convert(varchar(4),DatePart(yyyy,@EndDate)))
--Select @EndDate =  convert(datetime, convert(varchar(10), @EndDate,101) + ' 23:59' )
RETURN
SELECT
/* DealID */    D.DealID,
                DL.StageID,
                D.DealName,
                CASE WHEN ISNULL(CASE WHEN DL.DealOfficeID <> D2F.OfficeID OR DL.DealDepartmentID <> D2F.DepartmentID THEN 1 ELSE 0 END /*AS isFeeSplit*/,0) = 0 AND DL.isCompletedYTD = 1 THEN DL.DealVolume ELSE 0 END AS DealVolumeCompleted,
                CASE WHEN ISNULL(CASE WHEN DL.DealOfficeID <> D2F.OfficeID OR DL.DealDepartmentID <> D2F.DepartmentID THEN 1 ELSE 0 END /*AS isFeeSplit*/,0) = 0 AND (DS.isPipeline = 1 OR DL.StageID = 0) THEN DL.RawDealVolume ELSE 0 END AS VolumePipeline,
                D.TotalFee,
                DL.isCompletedYTD,
                D.OfficeID AS DealOfficeID,
                D.DepartmentID AS DealDepartmentID,
                DS.isPipeline,
                DS.isCompleted,
                DS.isQuote,
                DS.isApplication,
                DS.isCommitment,
                CASE WHEN DL.DealOfficeID <> D2F.OfficeID OR DL.DealDepartmentID <> D2F.DepartmentID THEN 1 ELSE 0 END AS isFeeSplit,
                --[dbo].[fnGetMoney](D.currencyID,1,(SELECT SUM(COALESCE(AmountPaid, AmountExpected, 0)) FROM dbo.DealClientPayments WHERE DealID = D.DealID),[dbo].[fnGetDealDate](D.dealid,@EndDate)) AS PaidFeesTotal,
                --[dbo].[fnGetMoney](D.currencyID,1,(SELECT SUM(CASE WHEN ISNULL(AccountingDate,DatePaid) >= @StartDate AND ISNULL(AccountingDate, DatePaid)< @EndDate THEN COALESCE(AmountPaid,AmountExpected,0) ELSE 0 END) FROM dbo.DealClientPayments WHERE DealID = D.DealID),[dbo].[fnGetDealDate](D.dealid,@EndDate)) AS YTDClientFee,
                --[dbo].[fnGetMoney](D.currencyID,1, SUM(D2F.Fee),[dbo].[fnGetDealDate](D.dealid,@EndDate)) AS AllocatedFee,


                ISNULL(D2F.OfficeID, D.OfficeID) AllocatedOfficeID,
                ISNULL(D2F.DepartmentID, D.DepartmentID) AllocatedDepartmentID, --Changed these from using the DL.DealDepartmentID to use the direct Deals Table.

                --[dbo].[fnGetMoney](D.currencyID,1,   CASE WHEN D.TotalFee > 0 THEN ISNULL(SUM(D2F.Fee)/*FL.AllocatedFee*/, D.TotalFee) * ISNULL((SELECT SUM(CASE WHEN ISNULL(AccountingDate,DatePaid) >= @StartDate AND ISNULL(AccountingDate, DatePaid)< @EndDate THEN COALESCE(AmountPaid,AmountExpected,0) ELSE 0 END) FROM dbo.DealClientPayments WHERE DealID = D.DealID)/*YTDClientFee*/,0)  / D.TotalFee ELSE 0 END    ,[dbo].[fnGetDealDate](D.dealid,@EndDate)) AS FeeCalcCompleted,

                --[dbo].[fnGetMoney](D.currencyID,1,
                --CASE WHEN D.TotalFee > 0 THEN ISNULL(SUM(D2F.Fee)/*FL.AllocatedFee*/,D.TotalFee) - ( ISNULL((SELECT  SUM(CASE WHEN ISNULL(AccountingDate,DatePaid) <= @EndDate THEN ISNULL(ISNULL(AmountPaid,AmountExpected),0) ELSE 0 END) FROM dbo.DealClientPayments WHERE DealID = D.DealID) /*FL.PreviousClientFees*/,0)  / D.TotalFee ) ELSE 0 END,

                --[dbo].[fnGetDealDate](D.dealid,@EndDate)) AS FeeCalcPipeline,



                CASE WHEN ISNULL(CASE WHEN DL.DealOfficeID <> D2F.OfficeID OR DL.DealDepartmentID <> D2F.DepartmentID THEN 1 ELSE 0 END,0) = 0 AND DL.isMisc = 0 AND DL.isCompletedYTD = 1 THEN 1 ELSE 0 END AS CountDealComplete,
                CASE WHEN ISNULL(CASE WHEN DL.DealOfficeID <> D2F.OfficeID OR DL.DealDepartmentID <> D2F.DepartmentID THEN 1 ELSE 0 END,0) = 0 AND DL.isMisc = 0 AND (DS.isPipeline = 1 OR DL.StageID = 0) THEN 1 ELSE 0 END AS CountDealPipeline,
                DL.DealOfficeName,
                CASE WHEN ADP.DisplayWithOffice = 1 THEN AO.[Name] + ' ' + ADP.ShortName ELSE AO.Name END  AS AllocatedOfficeName,
                DL.DealTypeID,
                DL.PropertyTypeID,
                DL.CityID--,
                --(SELECT SUM(CASE WHEN (ISNULL(AccountingDate,DatePaid) >= @EndDate OR DatePaid IS NULL) AND (DL2.StageID IN (7,8,9)) THEN ISNULL(AmountExpected, 0) ELSE 0 END)   FROM dbo.DealClientPayments INNER JOIN report.fnReportDealCore(@StartDate, @EndDate, @Type) AS DL2 ON dbo.DealClientPayments.DealID = DL2.DealID WHERE DealClientPayments.DealID = D.DealID) AS AmountReceivable
                --(SELECT MAX(CASE WHEN ISNULL(AccountingDate, ISNULL(DatePaid, DateExpected)) <= @EndDate THEN (ISNULL(AccountingDate, ISNULL(DatePaid, DateExpected))) ELSE NULL END) FROM dbo.DealClientPayments WHERE DealID = D.DealID) AS LastPaymentDate


FROM dbo.fnReportDealCore(@StartDate, @EndDate, @Type) AS DL
    INNER JOIN Deals D ON DL.DealID = D.DealID
    INNER JOIN DealStages AS DS ON DL.StageID = DS.StageID

    LEFT JOIN dbo.DealClientPayments DCP ON DL.DealID = DCP.DealID

    LEFT JOIN (SELECT DealID, SUM(Fee) AS Fee, OfficeID, DepartmentID FROM dbo.DealsToFees GROUP BY DealID, OfficeID, DepartmentID) D2F ON DL.DealID = D2F.DealID
    LEFT JOIN dbo.Offices AS AO ON D2F.OfficeID = AO.OfficeID
    LEFT JOIN DealPlatforms AS ADP ON D2F.DepartmentID = ADP.DepartmentID

    --WHERE D.DealID = 88330
    GROUP BY
        D.DealID,
        DL.StageID,
        D.DealName,
        DL.DealVolume,
        DL.RawDealVolume,
        D.TotalFee,
        DL.isCompletedYTD,
        D.OfficeID,
        D.DepartmentID,
        DS.isPipeline,
        DS.isCompleted,
        DS.isQuote,
        DS.isApplication,
        DS.isCommitment,
        CASE WHEN DL.DealOfficeID <> D2F.OfficeID OR DL.DealDepartmentID <> D2F.DepartmentID THEN 1 ELSE 0 END, --AS isFeeSplit--
        D.CurrencyID,
        D2F.OfficeID,
        D2F.DepartmentID,

        D.OfficeID,
        D.DepartmentID,
        DL.DealTypeID,
        DL.PropertyTypeID,
        DL.CityID,
        DL.DealOfficeName,
        ADP.DisplayWithOffice,
        AO.[Name],
        ADP.ShortName,
        DL.isMisc