public string[] GetObjectNames(string schemaName, string typeCode)
{
using (var ctx = new smbimContext())
{
return(ctx.Set <string>().FromSql($"GetAllObjectNames {schemaName}, {typeCode}").Select(x => new string(x)).ToArray());
}
}
public List <DDLAuditClaimedObjectDomain> GetClaimedObjectsByReleaseDate(DateTime releaseDate)
{
using (var ctx = new smbimContext())
{
return(ctx.ClaimedObjects.Where(z => !string.IsNullOrEmpty(z.ProjectLabel.ReleaseDate.ToString())
? z.ProjectLabel.ReleaseDate == releaseDate.Date
: z.ReleaseDate == releaseDate.Date)
.Where(x => x.ProjectLabel.IsJunk != true)
.Select(a =>
new DDLAuditClaimedObjectDomain
{
Id = a.Id,
ReleaseDate = a.ReleaseDate,
Notes = a.Notes,
ObjectDatabase = a.ObjectDatabase,
ObjectName = a.ObjectName,
ObjectSchema = a.ObjectSchema,
ObjectType = a.ObjectType,
Username = a.Username,
Label = ctx.ProjectLabels.Select(l => new ProjectLabelDomain
{
Id = l.Id,
Name = l.Name,
ReleaseDate = l.ReleaseDate
}).FirstOrDefault(l => l.Id == a.ProjectLabelId)
}).ToList());
}
}
//ToDo: Change to LINQ SQL or Stored Proc
public List <DatabaseObject> GetClaimedDatabaseObjects(DateTime minimumDateTime)
{
using (var ctx = new smbimContext())
{
throw new NotImplementedException();
}
}
public List <DDLAuditClaimedObjectDomain> GetClaimedObjects(string loginUserName, DateTime minimumDateTime)
{
var yesterday = DateTime.Today.AddDays(-1);
using (var ctx = new smbimContext())
{
return
(ctx.ClaimedObjects.Where(
x => x.Username.Equals(loginUserName, StringComparison.InvariantCultureIgnoreCase))
.Where(x => x.Username.Equals(loginUserName, StringComparison.InvariantCultureIgnoreCase))
.Where(z => z.ReleaseDate >= minimumDateTime)
.Where(x => x.ProjectLabel.IsJunk != true)
.Select(a =>
new DDLAuditClaimedObjectDomain
{
Id = a.Id,
ReleaseDate = a.ReleaseDate,
Notes = a.Notes,
ObjectDatabase = a.ObjectDatabase,
ObjectName = a.ObjectName,
ObjectSchema = a.ObjectSchema,
ObjectType = a.ObjectType,
Username = a.Username,
Label = ctx.ProjectLabels.Select(l => new ProjectLabelDomain
{
Id = l.Id,
Name = l.Name,
ReleaseDate = l.ReleaseDate
}).FirstOrDefault(l => l.Id == a.ProjectLabelId)
}).ToList());
}
}
public List <AuditLog> GetDdlAudit(int id)
{
using (var ctx = new smbimContext())
{
return(ctx.AuditLog.Where(x => x.Id == id).ToList());
}
}
public List <string> GetDdlAutitsForChart(DateTime minimumDate)
{
using (var ctx = new smbimContext())
{
return(ctx.AuditLog.Where(x => x.PostTime >= minimumDate).Select(m => m.Login.Remove(0, 6)).ToList());
}
}
public int GetCountOfTeamClaimedObjects(DateTime minimumDateTime)
{
int countOfResults;
var yesterday = DateTime.Today.AddDays(-1);
using (var ctx = new smbimContext())
{
var userDdlRecords = (from ddl in ctx.AuditLog
select new { ddl.ObjectName, ddl.ObjectSchema, ddl.DatabaseName }).Distinct().AsQueryable();
var newClaimed = from newToClaim in ctx.ClaimedObjects
where newToClaim.ReleaseDate >= minimumDateTime
where newToClaim.ReleaseDate >= yesterday
where newToClaim.ProjectLabel.IsJunk != true
select newToClaim;
var query = from qry in userDdlRecords
join claimed in newClaimed on new
{
ObjectName = qry.ObjectName,
ObjectSchema = qry.ObjectSchema,
ObjectDatabase = qry.DatabaseName
}
equals new
{
ObjectName = claimed.ObjectName,
ObjectSchema = claimed.ObjectSchema,
ObjectDatabase = claimed.ObjectDatabase
}
select qry;
countOfResults = query.Distinct().Count();
}
return(countOfResults);
}
public static List <T> RawSqlQuery <T>(string query, Func <DbDataReader, T> map)
{
using (var context = new smbimContext())
{
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
context.Database.OpenConnection();
using (var result = command.ExecuteReader())
{
var entities = new List <T>();
while (result.Read())
{
entities.Add(map(result));
}
return(entities);
}
}
}
}
public int GetCountOfTeamUnclaimedObjects(DateTime minimumDateTime)
{
int countOfResults;
using (var ctx = new smbimContext())
{
countOfResults = ctx.AuditLog.FromSql($"GetUnclaimedDatabaseObjects {minimumDateTime}, null").Count();
}
return(countOfResults);
}
public int GetCountOfUserUnclaimedObjects(string username, DateTime minimumDateTime)
{
int countOfResults;
using (var ctx = new smbimContext())
{
countOfResults = ctx.AuditLog.FromSql($"GetUnclaimedDatabaseObjects {minimumDateTime}, {username}").Count();
}
return(countOfResults);
}
public List <ProjectLabelDomain> GetProjectLabels()
{
using (var ctx = new smbimContext())
{
return(ctx.ProjectLabels.Select(p =>
new ProjectLabelDomain
{
Id = p.Id,
Name = p.Name,
ReleaseDate = p.ReleaseDate
}).OrderBy(p => p.Name).ToList());
}
}
public void DeleteLabel(ProjectLabelDomain label)
{
if (label != null)
{
using (var ctx = new smbimContext())
{
ProjectLabels deleteLabel = ctx.ProjectLabels.FirstOrDefault(l => l.Id == label.Id);
if (deleteLabel != null)
{
ctx.ProjectLabels.Remove(deleteLabel);
ctx.SaveChanges();
}
}
}
}
public List <DatabaseObject> GetUnclaimedDatabaseObjects(string developerUserName, DateTime minimumDateTime)
{
using (var ctx = new smbimContext())
{
var returnResults = ctx.AuditLog.FromSql($"GetUnclaimedDatabaseObjects {minimumDateTime}, {developerUserName}").AsNoTracking().ToList();
return(returnResults
.Select(r => new DatabaseObject
{
DatabaseName = r.DatabaseName,
ObjectSchema = r.ObjectSchema,
ObjectName = r.ObjectName,
Category = r.ObjectType
}).GroupBy(x => new { x.DatabaseName, x.ObjectSchema, x.ObjectName }).Select(y => y.First()).ToList());
}
}
public void DeleteClaimedObject(int claimedObjectId)
{
using (var ctx = new smbimContext())
{
try
{
var claimedObject = ctx.ClaimedObjects.Single(x => x.Id == claimedObjectId);
ctx.ClaimedObjects.Remove(claimedObject);
ctx.SaveChanges();
}
catch (InvalidOperationException)
{
throw new ApplicationException("Claimed Object does not exist");
}
}
}
//ToDo: Change to LINQ SQL or Stored Proc
public List <DatabaseObject> GetClaimedDatabaseObjects(string userName, DateTime minimumDateTime)
{
using (var ctx = new smbimContext())
{
//Select(d =>
//new DatabaseObject
//{
// DatabaseName = d.DatabaseName,
// LastDdlChange = d.LastDdlChange,
// ObjectSchema = d.ObjectSchema,
// ObjectName = d.ObjectName
//}
//).ToList();
throw new NotImplementedException();
}
}
public void DeleteIgnoredDatabaseObject(int id)
{
using (var ctx = new smbimContext())
{
try
{
var ignoredObjectRecord = ctx.IgnoredObjects.Single(x => x.Id == id);
ctx.IgnoredObjects.Remove(ignoredObjectRecord);
ctx.SaveChanges();
}
catch (InvalidOperationException)
{
throw new ApplicationException("Ignored Object Record Not Found.");
}
}
}
public List <AuditRecord> GetDdlAudits(DateTime minimumDate, string developer)
{
using (var ctx = new smbimContext())
{
return(ctx.AuditLog.Where(x => x.PostTime >= minimumDate && x.Login.Contains(developer)).Select(m => new AuditRecord
{
DatabaseName = m.DatabaseName,
Event = m.Event,
Id = m.Id,
ObjectName = string.IsNullOrEmpty(m.ParentTable) ? m.ObjectName : m.ParentTable,
ObjectType = m.ObjectType,
ObjectSchema = m.ObjectSchema,
PostDateTime = m.PostTime,
User = m.Login
}).ToList());
}
}
public void AddIgnoredDatabaseObject(string objectName, string objectSchema, string objectDatabase,
string userName)
{
using (var ctx = new smbimContext())
{
var ignoredObjectRecord = new IgnoredObjects
{
IgnoredByTime = DateTime.Now,
IgnoredByUser = userName,
ObjectName = objectName,
ObjectSchema = objectSchema,
ObjectDatabase = objectDatabase
};
ctx.IgnoredObjects.Add(ignoredObjectRecord);
ctx.SaveChanges();
}
}
public void AddLabel(ProjectLabelDomain label)
{
if (label == null)
{
return;
}
using (var ctx = new smbimContext())
{
var insertLabel = new ProjectLabels
{
Name = label.Name,
ReleaseDate = label.ReleaseDate,
IsJunk = label.IsJunk
};
ctx.ProjectLabels.Add(insertLabel);
ctx.SaveChanges();
}
}
public List <IgnoredDatabaseObject> GetIgnoredDatabaseObjects()
{
var dateWindow = DateTime.Now.AddMonths(-1);
using (var ctx = new smbimContext())
{
return
(ctx.IgnoredObjects.Where(x => x.IgnoredByTime >= dateWindow)
.Select(x => new IgnoredDatabaseObject
{
Id = x.Id,
ObjectName = x.ObjectName,
ObjectSchema = x.ObjectSchema,
ObjectDatabase = x.ObjectDatabase,
IgnoredByUser = x.IgnoredByUser,
IgnoredByTime = x.IgnoredByTime
}).ToList());
}
}
public void UpdateLabel(ProjectLabelDomain label)
{
if (label == null)
{
return;
}
using (var ctx = new smbimContext())
{
var editLabel = ctx.ProjectLabels.FirstOrDefault(l => l.Id == label.Id);
if (editLabel == null)
{
return;
}
editLabel.Name = label.Name;
editLabel.ReleaseDate = label.ReleaseDate;
editLabel.IsJunk = label.IsJunk;
ctx.SaveChanges();
}
}
public List <DatabaseObjectStatus> GetObjectsWithUnclimainedChangesByDatabaseAndType(string databaseName, DatabaseObjectTypeCode type, DateTime startDate, string userName)
{
List <DatabaseObjectStatus> statusList = new List <DatabaseObjectStatus>();
using (var ctx = new smbimContext())
{
var unclaimedChanges = ctx.AuditLog.FromSql($"GetUnclaimedDatabaseObjects {startDate}, {userName}").
Where(u => string.Compare(u.ObjectType, UtilityRepository.DatabaseReadableTypeCodesLookup[type], StringComparison.OrdinalIgnoreCase) == 0).ToList();
statusList.AddRange(unclaimedChanges.Select(change => new DatabaseObjectStatus
{
ObjectInformation = new DatabaseObjectBasicInformation
{
DatabaseName = databaseName,
ObjectName = change.ObjectName,
ObjectSchema = change.ObjectSchema
},
HasUnclaimedChanges = true
}));
return(statusList);
}
}
public void ClaimAuditRecord(string userName, DateTime?releaseDate, string objectName, string objectSchema,
string objectDatabase, string objectType, string notes, int projectLabelId)
{
if (releaseDate == null && projectLabelId == 0)
{
throw new ArgumentException("Either a release date or project label is needed.");
}
if (string.IsNullOrEmpty(userName))
{
throw new ArgumentException("User is required.");
}
if (string.IsNullOrEmpty(objectName))
{
throw new ArgumentException("Object Name is required.");
}
if (string.IsNullOrEmpty(objectSchema))
{
throw new ArgumentException("Object Schema is required.");
}
if (string.IsNullOrEmpty(objectDatabase))
{
throw new ArgumentException("Object Database is required.");
}
using (var ctx = new smbimContext())
{
var claimedObject = new ClaimedObjects
{
Username = userName,
ReleaseDate = releaseDate?.Date,
ObjectName = objectName,
ObjectSchema = objectSchema,
ObjectDatabase = objectDatabase,
ObjectType = objectType,
Notes = notes,
ProjectLabelId = projectLabelId == 0 ? (int?)null : projectLabelId
};
ctx.ClaimedObjects.Add(claimedObject);
ctx.SaveChanges();
}
}
