USE [ABC]
GO

/****** Object:  View [report].[vw_MonthlyDealAllocations]    Script Date: 11/24/2017 9:54:10 PM ******/
--SET ANSI_NULLS ON
--GO

--SET QUOTED_IDENTIFIER ON
--GO

--CREATE VIEW [report].[vw_MonthlyDealAllocations] as
SELECT
  PeriodStartDate,
  PeriodEndDate,
  D.DealID,
  Dl.DealName,
  D.StageID,
  CONVERT(varchar(1), D.StageID) + ' - ' + DS.Stage Stage,
  DP.Platform,
  ISNULL(D.AllocatedOfficeID, Dl.OfficeID) AS AllocatedOfficeID,
  ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) AS AllocatedDepartmentID,
  CASE
    WHEN ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) = 1 THEN 'Investment Advisory'
    WHEN ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) = 2 THEN 'Debt/EP'
    WHEN ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID) = 6 THEN 'HFF Securities'
    ELSE DM.Department
  END AS AllocatedDepartment,
  AO.Name AllocatedOffice,
  CASE
    WHEN DP.DisplayWithOffice = 1 THEN AO.Name + ' ' + DP.ShortName
    ELSE AO.Name
  END AllocatedOfficeDept,
  Dl.DepartmentID DealDepartmentID,
  DX.Department DealDepartment,
  Dl.OfficeID DealOfficeID,
  DO.Name DealOffice,
  CASE
    WHEN DP.DisplayWithOffice = 1 THEN DO.Name + ' ' + DP.ShortName
    ELSE DO.Name
  END DealOfficeDept,
  DT.DealType,
  D.OfficeAllocVolumePipeline,
  D.OfficeAllocVolumeCompleted,
  D.OfficeAllocFeePipeline,
  D.OfficeAllocFeeCompleted,
  D.OfficeAllocVolumePipelineGBP,
  D.OfficeAllocVolumeCompletedGBP,
  D.OfficeAllocFeePipelineGBP,
  D.OfficeAllocFeeCompletedGBP,
  PTG.GroupName,
  PT.PropertyType,
  CASE
    WHEN C.CountryID <> 1 THEN 'Foreign'
    ELSE R.Region
  END AS Region,
  AP.Address1,
  C.City,
  S.StateLong,
  AP.Zip,
  Investor.ClientName MSA,
  Cl.ClientName RollupMSA,
  ITI.InvestorType PrimaryInvestor,
  ITC.InvestorType PrimaryClient,
  P.FirstName + ' ' + P.LastName AS PrimaryProducer,
  I.FirstName + ' ' + I.LastName AS IL_Producer,
  D.CountDealCompleted,
  CASE Dl.isSecuritized
    WHEN 0 THEN 'No'
    WHEN 1 THEN 'Yes'
    ELSE ''
  END AS isSecuritized,
  CASE Dl.isServiced
    WHEN 0 THEN 'No'
    WHEN 1 THEN 'Yes'
    ELSE ''
  END AS isServiced,
  --QCount.QtCt,
  /*IIF (CASE
        WHEN D.isCompleted = 1 THEN D.VolumeCompleted
        WHEN (D.isPipeline = 1 OR D.StageID = 0) THEN D.VolumePipeline
        ELSE 0
    END >= 50000000,1,NULL
  ) AS Over50M, */
  IIF(Dl.PortfolioID IS NOT NULL, 1, NULL) AS isPortfolio,
  --IIF (isMultitransactional.linktype IS NOT NULL,1,NULL) AS isMultiTransactional,
  DP.PlatformID,
  P.EmployeeID AS PrimaryProducerID,
  I.EmployeeID AS IL_ProducerID,
  --report.fnReportGetDealClients(D.DealID) Clients,
  --report.fnReportGetDealInvestors(D.DealID) Investors,
  DFM.FinancingMethod,
  IIF((Dl.isSecuritized = 1 AND ISNULL(ISNULL(ITID2C.InvestorType, ITI.InvestorType), 'Unknown') <> 'Agency'), 'Conduit/CMBS', ISNULL(ISNULL(ITID2C.InvestorType, ITI.InvestorType), 'Unknown')) AS InvestorType

FROM report.MonthlyDealAllocations D
INNER JOIN dbo.Deals Dl
  ON Dl.DealID = D.DealID
INNER JOIN DealTypes DT
  ON DT.DealTypeID = Dl.DealTypeID
INNER JOIN DealPlatforms DP
  ON DP.PlatformID = DT.PlatformID
INNER JOIN DealStages DS
  ON DS.StageID = D.StageID
LEFT JOIN Departments DM
  ON DM.DepartmentID = ISNULL(D.AllocatedDepartmentID, Dl.DepartmentID)
LEFT JOIN Departments DX
  ON DX.DepartmentID = Dl.DepartmentID
LEFT JOIN Offices AO
  ON AO.OfficeID = ISNULL(D.AllocatedOfficeID, Dl.OfficeID)
LEFT JOIN Offices DO
  ON DO.OfficeID = Dl.OfficeID
LEFT JOIN dbo.DealProperties DPP
  ON D.DealID = DPP.DealID
  AND DPP.IsMainProperty = 1
LEFT JOIN PropertyTypes PT
  ON PT.PropertyTypeID = DPP.PropertyTypeID
LEFT JOIN PropertyTypeGroups PTG
  ON PTG.GroupID = PT.GroupID
LEFT JOIN DealProperties AS DPZ
  ON D.DealID = DPZ.DealID
  AND DPZ.isMainProperty = 1
LEFT JOIN Assets AP
  ON DPZ.AssetID = AP.AssetID
LEFT JOIN Cities C
  ON C.CityID = AP.CityID
LEFT JOIN States S
  ON S.StateID = C.StateID
LEFT JOIN Regions R
  ON ISNULL(C.RCARegionID, ISNULL(S.RCARegionID, S.RegionID)) = R.RegionID
LEFT JOIN DealsToClients DTCC
  ON D.DealID = DTCC.DealID
  AND DTCC.isPrimary = 1
  AND DTCC.MemberType IN (1, 3)
LEFT JOIN DealsToClients DTCI
  ON D.DealID = DTCI.DealID
  AND DTCI.isPrimary = 1
  AND DTCI.MemberType IN (2, 4)
LEFT JOIN CacheClients Cl
  ON DTCC.ClientID = Cl.ClientID
LEFT JOIN CacheClients Investor
  ON DTCI.ClientID = Investor.ClientID
LEFT JOIN InvestorTypes ITI
  ON Investor.InvestorTypeID = ITI.InvestorTypeID
LEFT JOIN InvestorTypes ITC
  ON Cl.InvestorTypeID = ITC.InvestorTypeID
--LEFT JOIN (SELECT DISTINCT D2D.DealID, D2D.LinkType FROM DealsToDeals D2D WHERE LinkType = 2) isMultitransactional ON D.DealID = isMultitransactional.DealID
LEFT JOIN DealsToEmployees EP
  ON D.DealID = EP.DealID
  AND EP.MemberType = 1
  AND EP.isPrimary = 1
LEFT JOIN DealsToEmployees EI
  ON D.DealID = EI.DealID
  AND EI.MemberType = 2
  AND EI.isPrimary = 1
LEFT JOIN Employees P
  ON EP.EmployeeID = P.EmployeeID
LEFT JOIN Employees I
  ON EI.EmployeeID = I.EmployeeID
LEFT JOIN DealFinancingMethods DFM
  ON Dl.DealFinancingMethodID = DFM.DealFinancingMethodsID
LEFT JOIN InvestorTypes AS ITID2C
  ON DTCI.InvestorTypeID = ITID2C.InvestorTypeID
--LEFT JOIN (SELECT DealID, COUNT(QuoteID) AS QtCt FROM Quotes WHERE DateRemoved IS NULL GROUP BY DealID) QCount ON D.dealid = Qcount.DealID

USE [ABC]
GO

/****** Object:  Index [IX_MonthlyDealAllocations_DealID_AllocDeptID_AllocOfficeID]    Script Date: 11/24/2017 11:43:00 PM ******/
CREATE NONCLUSTERED INDEX [IX_MonthlyDealAllocations_DealID_AllocDeptID_AllocOfficeID] ON [report].[MonthlyDealAllocations]
(
    [DealID] ASC,
    [AllocatedDepartmentID] ASC,
    [AllocatedOfficeID] ASC
)
INCLUDE (   [PeriodStartDate],
    [PeriodEndDate],
    [CountDealCompleted],
    [StageID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO