/// <summary>
/// Loads the applications.
/// </summary>
private void LoadApplications( )
{
var databaseManager = new DatabaseManager(PluginSettings.DatabaseSettings);
const string commandText = @"--ReadiMon - LoadApplications
SET NOCOUNT ON
DECLARE @tenantId BIGINT
DECLARE @applicationGuid UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000'
DECLARE cur CURSOR FORWARD_ONLY FOR
SELECT 0
UNION
SELECT Id FROM _vTenant
OPEN cur
FETCH NEXT FROM cur
INTO @tenantId
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @tenantName NVARCHAR(MAX) = ISNULL( dbo.fnNameAlias( @tenantId, 0 ), 'Global' )
DECLARE @name BIGINT = dbo.fnAliasNsId( 'name', 'core', DEFAULT )
DECLARE @appVersionString BIGINT = dbo.fnAliasNsId( 'appVersionString', 'core', DEFAULT )
DECLARE @appVerId BIGINT = dbo.fnAliasNsId( 'appVerId', 'core', DEFAULT )
DECLARE @packageForApplication BIGINT = dbo.fnAliasNsId( 'packageForApplication', 'core', DEFAULT )
DECLARE @solutionVersionString BIGINT = dbo.fnAliasNsId( 'solutionVersionString', 'core', @tenantId )
DECLARE @name_tenantSpecific BIGINT = dbo.fnAliasNsId( 'name', 'core', @tenantId )
DECLARE @packageId BIGINT = dbo.fnAliasNsId( 'packageId', 'core', @tenantId )
DECLARE @isOfType BIGINT = dbo.fnAliasNsId( 'isOfType', 'core', @tenantId )
DECLARE @solution BIGINT = dbo.fnAliasNsId( 'solution', 'core', @tenantId )
DECLARE @canModifyApplication BIGINT = dbo.fnAliasNsId( 'canModifyApplication', 'core', @tenantId )
SELECT
TenantId = @tenantId,
Tenant = @tenantName,
Solution = solName.Data,
SolutionEntityId = s.FromId,
SolutionVersion = solVer.Data,
PackageId = pkgId.Data,
PackageEntityId = p.EntityId,
Version = pkgVer.Data,
ApplicationEntityId = pkgApp.ToId,
ApplicationId = aid.UpgradeId,
Publisher = p1.Data,
PublisherUrl = u.Data,
ReleaseDate = c.Data,
CanModifyApplication = ISNULL(protected.Data, 0)
FROM
Relationship s -- isOfType solution
JOIN
Data_NVarChar solVer ON
solVer.TenantId = s.TenantId
AND solVer.EntityId = s.FromId
AND solVer.FieldId = @solutionVersionString
JOIN
Data_NVarChar solName ON
solName.TenantId = s.TenantId
AND solName.EntityId = s.FromId
AND solName.FieldId = @name_tenantSpecific
JOIN
Data_Guid pkgId ON
pkgId.TenantId = s.TenantId
AND pkgId.EntityId = s.FromId
AND pkgId.FieldId = @packageId
LEFT JOIN
Data_Guid p ON
p.TenantId = 0
AND p.Data = pkgId.Data
AND p.FieldId = @appVerId
LEFT JOIN
Relationship pkgApp ON
pkgApp.TenantId = 0
AND pkgApp.FromId = p.EntityId
AND pkgApp.TypeId = @packageForApplication
LEFT JOIN
Data_NVarChar pkgVer ON
pkgVer.TenantId = 0
AND pkgVer.EntityId = p.EntityId
AND pkgVer.FieldId = @appVersionString
LEFT JOIN
Data_Bit protected ON
protected.TenantId = s.TenantId
AND protected.EntityId = s.FromId
AND protected.FieldId = @canModifyApplication
LEFT JOIN
(
SELECT
p.EntityId,
p.Data
FROM
Data_NVarChar p
JOIN
Data_Alias solPub ON
solPub.TenantId = p.TenantId
AND solPub.EntityId = p.FieldId
AND solPub.Data = 'solutionPublisher'
AND solPub.Data = 'solutionPublisher'
AND solPub.Namespace = 'core'
WHERE
p.TenantId = @tenantId
) p1 ON
p1.EntityId = s.FromId
LEFT JOIN
(
SELECT
u.EntityId,
u.Data
FROM
Data_NVarChar u
JOIN
Data_Alias solUrl ON
solUrl.TenantId = u.TenantId
AND solUrl.EntityId = u.FieldId
AND solUrl.Data = 'solutionPublisherUrl'
AND solUrl.Namespace = 'core'
WHERE
u.TenantId = @tenantId
) u ON
u.EntityId = s.FromId
LEFT JOIN
(
SELECT
c.EntityId,
c.Data
FROM
Data_DateTime c
JOIN
Data_Alias solRelDate ON
solRelDate.TenantId = c.TenantId
AND solRelDate.EntityId = c.FieldId
AND solRelDate.Data = 'solutionReleaseDate'
AND solRelDate.Namespace = 'core'
WHERE
c.TenantId = @tenantId
) c ON
c.EntityId = s.FromId
JOIN
Entity aid ON
aid.TenantId = s.TenantId
AND aid.Id = s.FromId
WHERE
s.TenantId = @tenantId
AND s.TypeId = @isOfType
AND s.ToId = @solution
AND
(
p.EntityId = pkgApp.FromId
OR
p.EntityId IS NULL
)
AND
(
@applicationGuid = CONVERT(UNIQUEIDENTIFIER, '00000000-0000-0000-0000-000000000000')
OR
@applicationGuid = aid.UpgradeId
)
ORDER BY
solName.Data,
pkgVer.Data
FETCH NEXT FROM cur
INTO @tenantId
END
CLOSE cur;
DEALLOCATE cur;";
try
{
using (IDbCommand command = databaseManager.CreateCommand(commandText))
{
using (IDataReader reader = command.ExecuteReader( ))
{
var tenantApps = new List <TenantApp>( );
HashSet <string> tenants = new HashSet <string>( );
HashSet <string> solutions = new HashSet <string>( );
HashSet <string> versions = new HashSet <string>( );
HashSet <string> publishers = new HashSet <string>( );
HashSet <string> publisherUrls = new HashSet <string>( );
HashSet <bool> isProtectedValues = new HashSet <bool>( );
do
{
do
{
while (reader.Read( ))
{
var tenantId = reader.GetInt64(0);
var tenantName = reader.GetString(1);
var solution = reader.GetString(2, "Unnamed");
var solutionEntityId = reader.GetInt64(3, -1);
var solutionVersion = reader.GetString(4, "");
var packageId = reader.GetGuid(5);
var packageEntityId = reader.GetInt64(6, -1);
var packageVersion = reader.GetString(7, "");
var applicationEntityId = reader.GetInt64(8, -1);
var applicationId = reader.GetGuid(9);
var publisher = reader.GetString(10, "");
var publisherUrl = reader.GetString(11, "");
var releaseDate = reader.GetDateTime(12, DateTime.MinValue);
var canModifyApplication = reader.GetBoolean(13);
var tenantApp = new TenantApp(tenantId, tenantName, solution, solutionEntityId, solutionVersion, packageId, packageEntityId, packageVersion, applicationEntityId, applicationId, publisher, publisherUrl, releaseDate, !canModifyApplication);
tenants.Add(tenantName);
solutions.Add(solution);
versions.Add(solutionVersion);
publishers.Add(publisher);
publisherUrls.Add(publisherUrl);
isProtectedValues.Add(canModifyApplication);
tenantApps.Add(tenantApp);
}
}while (reader.NextResult( ));
}while (reader.NextResult( ));
TenantApps = tenantApps.OrderBy(x => x.TenantId).ThenBy(x => x.Solution).ToList( );
TenantFilters = new List <FilterObject>( );
foreach (string ten in tenants.OrderBy(k => k))
{
TenantFilters.Add(new FilterObject(ten, string.IsNullOrEmpty(ten) ? "<empty>" : ten, true, TenantFilterUpdate));
}
OnPropertyChanged("TenantFilters");
SolutionFilters = new List <FilterObject>( );
foreach (string sol in solutions.OrderBy(k => k))
{
SolutionFilters.Add(new FilterObject(sol, string.IsNullOrEmpty(sol) ? "<empty>" : sol, true, SolutionFilterUpdate));
}
OnPropertyChanged("SolutionFilters");
VersionFilters = new List <FilterObject>( );
foreach (string ver in versions.OrderBy(k => k))
{
VersionFilters.Add(new FilterObject(ver, string.IsNullOrEmpty(ver) ? "<empty>" : ver, true, VersionFilterUpdate));
}
OnPropertyChanged("VersionFilters");
PublisherFilters = new List <FilterObject>( );
foreach (string pub in publishers.OrderBy(k => k))
{
PublisherFilters.Add(new FilterObject(pub, string.IsNullOrEmpty(pub) ? "<empty>" : pub, true, PublisherFilterUpdate));
}
OnPropertyChanged("PublisherFilters");
PublisherUrlFilters = new List <FilterObject>( );
foreach (string puburl in publisherUrls.OrderBy(k => k))
{
PublisherUrlFilters.Add(new FilterObject(puburl, string.IsNullOrEmpty(puburl) ? "<empty>" : puburl, true, PublisherUrlFilterUpdate));
}
OnPropertyChanged("PublisherUrlFilters");
ProtectedFilters = new List <FilterObject>( );
foreach (bool value in isProtectedValues.OrderBy(k => k))
{
ProtectedFilters.Add(new FilterObject(value, value.ToString( ), true, ProtectedFilterUpdate));
}
OnPropertyChanged("ProtectedFilters");
FilteredTenantApps = new List <TenantApp>(TenantApps);
}
}
}
catch (Exception exc)
{
PluginSettings.EventLog.WriteException(exc);
}
}