USE [m5db]
GO

/****** Object:  View [dbo].[VwProfileDetails]    Script Date: 11-08-2016 10:32:24 ******/
DROP VIEW [dbo].[VwProfileDetails]
GO

/****** Object:  View [dbo].[VwProfileDetails]    Script Date: 11-08-2016 10:32:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[VwProfileDetails]
AS
select
    pr.Id,
    pr.ProfileTypeId,
    pr.Extension,
    prt.Name as ProfileTypeName,
    pr.ProfileStatusId,
    prn.Name as ProfileStatusName,
    pr.IsInDirectory,
    pr.IsPrimary,
    l.AccountId,
    coalesce(pe.FirstName, pr.BoblFirstName) as FirstName,
    coalesce(pe.LastName, pr.BoblLastName) as LastName,
    coalesce(ltrim(rtrim(pe.FirstName + ' ' + pe.LastName)), pr.InternalCallerId) as FullName,
    pr.InternalCallerId,
    coalesce(pe.BusinessEmail, pr.BoblEmail) as BusinessEmail,
    a.Id as AddressId,
    a.Address,
    a.Address2,
    a.City,
    a.StateId,
    a.CountryId,
    a.ZipCode,
    s.Name as State,
    co.Name as Country,
    l.Id as LocationId,
    pe.Id as PersonId,
    pr.TnId as [Tn],
	pr.TnCountryId as TnCountryId,
    coalesce(l.Name, a.Address + ' ' + a.Address2 + ', ' + a.City + ', '+s.Name ) as LocationName,
    pr.SubscriberId,
    pt.ClusterId,
    cl.Name as [Cluster],
    cl.PlatformTypeId,
    plt.Name as [Platform],
    cast(case
        when lo.LockEnabled = 0 then 0
        when ul.FailedLoginCount IS null then 0 -- special case, fail on first login
        when ul.FailedLoginCount >= lo.MaxFailedLogins AND ul.DateLocked < DATEADD(HOUR,-cast(lo.LockDuration as int),GETUTCDATE()) then 0
        when ul.FailedLoginCount >= lo.MaxFailedLogins then 1
        else 0
    end AS bit) as ProfileLocked,
    cast(case
        when lo.LockEnabled = 0 then 0
        when ulp.FailedLoginCount IS null then 0 -- special case, fail on first login
        when ulp.FailedLoginCount >= lo.MaxFailedLogins AND ulp.DateLocked < DATEADD(HOUR,-cast(lo.LockDuration as int),GETUTCDATE()) then 0
        when ulp.FailedLoginCount >= lo.MaxFailedLogins then 1
        else 0
    end as bit) as PersonLocked,
    case
        when ulp.LastSuccess is null AND ul.LastSuccess is NULL then null
        when ulp.LastSuccess IS null then ul.LastSuccess
        when ul.LastSuccess IS null then ulp.LastSuccess
        when ul.LastSuccess < ulp.LastSuccess then ulp.LastSuccess
        else ul.LastSuccess
    end as [LastLogin],
    pr.IsSuspended,
	prod.Name as BundleName,
	pr.PartitionId
from
		[Profile] pr left join [Person] pe with (nolock)
			on pr.PersonId = pe.Id
		inner join Partition pt with (nolock)
			on pr.PartitionId = pt.Id
		inner join ProfileStatus prn with (nolock)
			on pr.ProfileStatusId = prn.Id
		inner join ProfileType prt with (nolock)
			on pr.ProfileTypeId = prt.Id
		inner join Location l with (nolock)
			on pr.LocationId = l.Id
		inner join Account ac with (nolock)
			on l.AccountId = ac.Id
		inner join Cluster cl with (nolock)
			on pt.ClusterId = cl.Id
		inner join PlatformType plt with (nolock)
			on cl.PlatformTypeId = plt.Id
		inner join VwAccountLockout lo with (nolock)
			on pt.AccountId = lo.AccountId
		left join [Address] a with (nolock)
			on l.AddressId = a.Id
		left join StateProvince s with (nolock)
			on a.StateId = s.id
		left join Country co with (nolock)
			on a.CountryId = co.id
		left join access_UserLogin ul with (nolock)
			on ul.AccountId = lo.AccountId
				and ul.Username = pr.TnId
		left join access_UserLogin ulp with (nolock)
			on ulp.AccountId = lo.AccountId
				and ulp.Username = pe.Username
		left join service_serviceProvisioningBase spb on pr.id = spb.ProfileId
																					  --Operate only on Service as part of Bundle
		left join service_service svcA on svcA.ServiceProvisioningBaseId = spb.id and svcA.BundleServiceId is not null  --and svcA.ServiceClassId in (select ServiceClassId from [service_ServiceClassProfileTypeMapping])
		left join service_service bundleSvc on svcA.BundleServiceId = bundleSvc.id
		left join billing_product prod on bundleSvc.productId = prod.id
		-- Fetch only profiles which are on a cluster associated with the account
		inner join AccountCluster acc
			on pt.ClusterId = acc.ClusterId and acc.AccountId = ac.Id
	where a.IsDeleted = 0 and  (svcA.ServiceClassId in (select ServiceClassId from [service_ServiceClassProfileTypeMapping]) or svcA.ServiceClassId is null)and
		pr.ProfileStatusId = 0 -- only Active
		and 0 = pr.IsVoiceMailDeposit and 0 = pr.IsVoiceMailRetrieve    -- Don't show these
		-- Fetch only profiles which are on a cluster associated with the account
		--and pt.clusterid in ( select Clusterid from AccountCluster where AccountId = l.AccountId )

GO