/// <summary>
/// sets a decision for an issue
/// </summary>
/// <param name="decision">the decision</param>
/// <param name="userId">user who is performing this operation</param>
public static void MakeDecision(Decision decision, int userId)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
HDecision dec = new HDecision();
if (ctx.Decision.Where(x => x.IssueId == decision.IssueId).Count() == 0)
{
ctx.Decision.Add(decision);
ctx.Entry(decision).State = EntityState.Added;
dec.Action = "Decision made";
}
else
{
Decision existingD = ctx.Decision.Find(decision.IssueId);
existingD.AlternativeId = decision.AlternativeId;
existingD.Explanation = decision.Explanation;
ctx.Entry(existingD).State = EntityState.Modified;
dec.Action = "Decision changed";
}
dec.ChangeDate = DateTime.Now;
dec.IssueId = decision.IssueId;
dec.UserId = userId;
dec.AlternativeId = decision.AlternativeId;
dec.Explanation = decision.Explanation;
ctx.HDecision.Add(dec);
ctx.Entry(dec).State = EntityState.Added;
ctx.SaveChanges();
ctx.Dispose();
}
/// <summary>
/// deletes list of alternatives
/// </summary>
/// <param name="alternativeIdList">list of to deleting alternative ids</param>
/// <param name="userId"></param>
public static void DeleteAlternatives(List <int> alternativeIdList, int userId)
{
Alternative alt;
ApplicationDBEntities ctx = new ApplicationDBEntities();
if (alternativeIdList == null || alternativeIdList.Count() == 0)
{
alternativeIdList = new List <int>();
}
foreach (int id in alternativeIdList)
{
alt = ctx.Alternative.Find(id);
ctx.Alternative.Remove(alt);
ctx.Entry(alt).State = EntityState.Deleted;
ctx.SaveChanges();
HAlternative halt = new HAlternative();
halt.ChangeDate = DateTime.Now;
halt.AlternativeId = alt.Id;
halt.UserId = userId;
halt.Action = "alternative deleted (" + alt.Name + ")";
halt.Name = alt.Name;
halt.Description = alt.Description;
halt.Reason = alt.Reason;
halt.Rating = alt.Rating;
halt.IssueId = alt.IssueId;
ctx.HAlternative.Add(halt);
ctx.Entry(halt).State = EntityState.Added;
ctx.SaveChanges();
}
ctx.Dispose();
CommentOp.DeleteAlternativeComments(alternativeIdList);
}
/// <summary>
/// saves user review for issue
/// </summary>
/// <param name="review"></param>
public static void SaveIssueReview(Review review)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
HReview hreview = ctx.HReview.Create();
hreview.ChangeDate = DateTime.Now;
hreview.IssueId = review.IssueId;
hreview.UserId = review.UserId;
if (ctx.Review.Where(x => x.IssueId == review.IssueId && x.UserId == review.UserId).Count() > 0)
{
hreview.Action = "Review updated";
Review dbReview = ctx.Review.Where(x => x.IssueId == review.IssueId && x.UserId == review.UserId).FirstOrDefault();
dbReview.Rating = review.Rating;
dbReview.Explanation = review.Explanation;
ctx.Entry(dbReview).State = System.Data.Entity.EntityState.Modified;
}
else
{
hreview.Action = "Review added";
ctx.Review.Add(review);
ctx.Entry(review).State = System.Data.Entity.EntityState.Added;
}
ctx.HReview.Add(hreview);
ctx.Entry(hreview).State = System.Data.Entity.EntityState.Added;
ctx.SaveChanges();
ctx.Dispose();
}
/// <summary>
/// deletes criterions by criterionId
/// </summary>
/// <param name="criterionIdList">list of criteria</param>
/// <param name="userId">user who is performing this operation</param>
public static void DeleteCriterions(List <int> criterionIdList, int userId)
{
Criterion crit;
ApplicationDBEntities ctx = new ApplicationDBEntities();
if (criterionIdList == null || criterionIdList.Count == 0)
{
return;
}
foreach (int id in criterionIdList)
{
crit = ctx.Criterion.Find(id);
ctx.Criterion.Remove(crit);
ctx.Entry(crit).State = EntityState.Deleted;
ctx.SaveChanges();
//changes to historytable
HCriterion hcrit = new HCriterion();
hcrit.ChangeDate = DateTime.Now;
hcrit.CriterionId = id;
hcrit.UserId = userId;
hcrit.Action = "criterion deleted (" + crit.Name + ")";
hcrit.Name = crit.Name;
hcrit.Description = crit.Description;
hcrit.Issue = crit.Issue;
hcrit.Weight = crit.Weight;
hcrit.WeightPC = crit.WeightPC;
ctx.HCriterion.Add(hcrit);
ctx.Entry(hcrit).State = EntityState.Added;
ctx.SaveChanges();
}
ctx.Dispose();
}
/// <summary>
/// updates slefassesment of an User
/// </summary>
/// <param name="value">self assessment value</param>
/// <param name="description">self assessment description</param>
public static void UpdateSelfAssesment(double value, string description, int issueId, int userId)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
AccessRight right = ctx.AccessRight.AsNoTracking().Where(x => x.IssueId == issueId && x.UserId == userId).FirstOrDefault();
bool update = false;
if (right.SelfAssessmentValue != value)
{
right.SelfAssessmentValue = value;
update = true;
}
if (right.SelfAssesmentDescr != description)
{
right.SelfAssesmentDescr = description;
update = true;
}
if (update)
{
HAccessRight har = new HAccessRight();
har.SelfAssesmentDescr = right.SelfAssesmentDescr;
har.SelfAssessmentValue = right.SelfAssessmentValue;
har.ChangeDate = System.DateTime.Now;
har.IssueId = right.IssueId;
har.UserId = right.UserId;
har.Action = "Selfassessment updated";
ctx.HAccessRight.Add(har);
ctx.Entry(har).State = EntityState.Added;
ctx.Entry(right).State = EntityState.Modified;
ctx.SaveChanges();
}
ctx.Dispose();
}
/// <summary>
/// updates a list of alternatives
/// </summary>
/// <param name="alternativeList"></param>
/// <param name="useId">user who is performing this operation</param>
public static void UpdateAlternatives(List <Alternative> alternativeList, int useId)
{
Alternative updateAlt;
bool updated;
ApplicationDBEntities ctx = new ApplicationDBEntities();
if (alternativeList == null || alternativeList.Count() == 0)
{
alternativeList = new List <Alternative>();
}
foreach (Alternative alt in alternativeList)
{
updated = false;
updateAlt = ctx.Alternative.Find(alt.Id);
if (alt.Description != updateAlt.Description || !alt.Description.Equals(updateAlt.Description))
{
updateAlt.Description = alt.Description;
updated = true;
}
if (alt.Name != updateAlt.Name || !alt.Name.Equals(updateAlt.Name))
{
updateAlt.Name = alt.Name;
updated = true;
}
if (!(alt.Reason == null && updateAlt.Reason == null))
{
if (alt.Reason != updateAlt.Reason || !alt.Reason.Equals(updateAlt.Reason))
{
updateAlt.Reason = alt.Reason;
updated = true;
}
}
if (updated)
{
ctx.Entry(updateAlt).State = EntityState.Modified;
ctx.SaveChanges();
HAlternative halt = new HAlternative();
halt.ChangeDate = DateTime.Now;
halt.AlternativeId = updateAlt.Id;
halt.UserId = useId;
halt.Action = "alternative updated (" + updateAlt.Name + ")";
halt.Name = updateAlt.Name;
halt.Description = updateAlt.Description;
halt.Reason = updateAlt.Reason;
halt.Rating = updateAlt.Rating;
halt.IssueId = updateAlt.IssueId;
ctx.HAlternative.Add(halt);
ctx.Entry(halt).State = EntityState.Added;
ctx.SaveChanges();
}
}
ctx.Dispose();
}
/// <summary>
/// updates a lsit of criteria
/// </summary>
/// <param name="criterionList">list ofr criteria</param>
/// <param name="userId">user who is performing this operation</param>
public static void UpdateCriterions(List <Criterion> criterionList, int userId)
{
bool updated;
Criterion updatingCrit;
ApplicationDBEntities ctx = new ApplicationDBEntities();
if (criterionList == null || criterionList.Count == 0)
{
return;
}
foreach (Criterion crit in criterionList)
{
updated = false;
updatingCrit = ctx.Criterion.Find(crit.Id);
if (updatingCrit.Name != crit.Name)
{
updated = true;
updatingCrit.Name = crit.Name;
}
if (updatingCrit.Description != crit.Description)
{
updated = true;
updatingCrit.Description = crit.Description;
}
if (updated)
{
ctx.Entry(updatingCrit).State = EntityState.Modified;
try
{
ctx.SaveChanges();
HCriterion hcrit = new HCriterion();
hcrit.ChangeDate = DateTime.Now;
hcrit.CriterionId = crit.Id;
hcrit.UserId = userId;
hcrit.Action = "criterion updated (" + crit.Name + ")";
hcrit.Name = crit.Name;
hcrit.Description = crit.Description;
hcrit.Issue = crit.Issue;
hcrit.Weight = crit.Weight;
hcrit.WeightPC = crit.WeightPC;
ctx.HCriterion.Add(hcrit);
ctx.Entry(hcrit).State = EntityState.Added;
ctx.SaveChanges();
}
catch (Exception ex)
{
DbConnection.Instance.DisposeAndReload();
}
}
}
ctx.Dispose();
}
/// <summary>
/// saves ratings to an issue
/// </summary>
/// <param name="userRatings"></param>
public static void SaveUserRatings(List <Rating> userRatings)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
if (userRatings.Count > 0)
{
List <Rating> list;
bool insert;
int issueId;
Rating hRat;
issueId = ctx.Criterion.Find(userRatings[0].CriterionId).Issue;
int userId = userRatings[0].UserId;
var query = from Rating in ctx.Rating
where
Rating.UserId == userId &&
(from Criterion in ctx.Criterion
where
Criterion.Issue == issueId
select new
{
Criterion.Id
}).Contains(new { Id = Rating.CriterionId })
select Rating;
list = query.ToList();
if (list.Count == 0)
{
insert = true;
}
else
{
insert = false;
}
foreach (Rating rat in userRatings)
{
if (insert)
{
ctx.Rating.Add(rat);
ctx.Entry(rat).State = EntityState.Added;
}
else
{
hRat = ctx.Rating.Find(rat.CriterionId, rat.AlternativeId, rat.UserId);
hRat.Value = rat.Value;
ctx.Entry(hRat).State = EntityState.Modified;
}
ctx.SaveChanges();
}
}
ctx.Dispose();
}
/// <summary>
/// saves user criterion weights into DB
/// </summary>
/// <param name="cirteriaWeights"></param>
/// <param name="issueId"></param>
/// <param name="userId">user who is performing this operation</param>
public static void SaveCriterionWeights(List <CriterionWeight> cirteriaWeights, int issueId, int userId)
{
bool insert;
CriterionWeight updatedCw;
ApplicationDBEntities ctx = new ApplicationDBEntities();
//first check if user wants to update his weights
var query = from CriterionWeight in ctx.CriterionWeight
where
(from Criterion in ctx.Criterion
where
Criterion.Issue == issueId &&
CriterionWeight.UserId == userId
select new
{
Criterion.Id
}).Contains(new { Id = CriterionWeight.CriterionId })
select new
{
CriterionWeight.Weight
};
if (query.Count() == 0)
{
insert = true;
}
else
{
insert = false;
}
foreach (CriterionWeight cw in cirteriaWeights)
{
if (insert)
{
updatedCw = ctx.CriterionWeight.Create();
updatedCw.UserId = cw.UserId;
updatedCw.CriterionId = cw.CriterionId;
updatedCw.Weight = cw.Weight;
ctx.CriterionWeight.Add(updatedCw);
ctx.Entry(updatedCw).State = EntityState.Added;
}
else
{
updatedCw = ctx.CriterionWeight.Find(userId, cw.CriterionId);
updatedCw.Weight = cw.Weight;
ctx.Entry(updatedCw).State = EntityState.Modified;
}
ctx.SaveChanges();
}
ctx.Dispose();
}
/// <summary>
/// adds a List of Alternatives to an issue
/// </summary>
/// <param name="alternativeList"></param>
/// <param name="userId">user who is performing this operation</param>
public static void AddAlternatives(List <Alternative> alternativeList, int userId)
{
Alternative addedAlt;
ApplicationDBEntities ctx = new ApplicationDBEntities();
if (alternativeList == null || alternativeList.Count() == 0)
{
alternativeList = new List <Alternative>();
}
foreach (Alternative alt in alternativeList)
{
addedAlt = ctx.Alternative.Create();
addedAlt.Description = alt.Description;
addedAlt.IssueId = alt.IssueId;
addedAlt.Name = alt.Name;
addedAlt.Reason = alt.Reason;
ctx.Alternative.Add(addedAlt);
ctx.Entry(addedAlt).State = EntityState.Added;
ctx.SaveChanges();
//insert into change-table
HAlternative halt = new HAlternative();
halt.ChangeDate = DateTime.Now;
halt.AlternativeId = addedAlt.Id;
halt.UserId = userId;
halt.IssueId = alt.IssueId;
halt.Action = "alternative added (" + addedAlt.Name + ")";
halt.Name = addedAlt.Name;
halt.Description = addedAlt.Description;
halt.Reason = addedAlt.Reason;
ctx.HAlternative.Add(halt);
ctx.Entry(halt).State = EntityState.Added;
ctx.SaveChanges();
//mark new alternative as read
ApplicationDBEntities ctx2 = new ApplicationDBEntities();
DbCommand cmd = ctx2.Database.Connection.CreateCommand();
ctx2.Database.Connection.Open();
cmd.CommandText = "UPDATE appSchema.InformationRead SET [Read] = 1 WHERE UserId = " + userId + " AND TName LIKE 'Alternative' AND FK LIKE '" + addedAlt.Id + "'";
cmd.CommandType = System.Data.CommandType.Text;
cmd.ExecuteNonQuery();
ctx2.Database.Connection.Close();
ctx2.Dispose();
}
ctx.Dispose();
}
/// <summary>
/// adds a list of criteria to an issue
/// </summary>
/// <param name="criterionList">list of criteria</param>
/// <param name="userId">user who is performing this operation</param>
public static void AddCriterions(List <Criterion> criterionList, int userId)
{
Criterion addingCrit;
ApplicationDBEntities ctx = new ApplicationDBEntities();
if (criterionList == null || criterionList.Count == 0)
{
return;
}
foreach (Criterion crit in criterionList)
{
addingCrit = ctx.Criterion.Create();
addingCrit.Description = crit.Description;
addingCrit.Name = crit.Name;
addingCrit.Weight = null;
addingCrit.WeightPC = null;
addingCrit.Issue = crit.Issue;
ctx.Criterion.Add(addingCrit);
ctx.Entry(addingCrit).State = EntityState.Added;
ctx.SaveChanges();
//changes to history table
HCriterion hcrit = new HCriterion();
hcrit.ChangeDate = DateTime.Now;
hcrit.CriterionId = addingCrit.Id;
hcrit.UserId = userId;
hcrit.Action = "criterion added (" + addingCrit.Name + ")";
hcrit.Name = addingCrit.Name;
hcrit.Description = addingCrit.Description;
hcrit.Issue = addingCrit.Issue;
ctx.HCriterion.Add(hcrit);
ctx.Entry(hcrit).State = EntityState.Added;
ctx.SaveChanges();
//mark new criterion as read
ApplicationDBEntities ctx2 = new ApplicationDBEntities();
DbCommand cmd = ctx2.Database.Connection.CreateCommand();
ctx2.Database.Connection.Open();
cmd.CommandText = "UPDATE appSchema.InformationRead SET [Read] = 1 WHERE UserId = " + userId + " AND TName LIKE 'Criterion' AND FK LIKE '" + addingCrit.Id + "'";
cmd.CommandType = System.Data.CommandType.Text;
cmd.ExecuteNonQuery();
ctx2.Database.Connection.Close();
ctx2.Dispose();
}
ctx.Dispose();
}
/// <summary>
/// removes user from issue
/// </summary>
/// <param name="accessRight">access right to be removed</param>
/// <param name="userId">user who is delteing access right</param>
/// <returns>bool if successful</returns>
public static bool RemoveAccessRight(AccessRight accessRight, int userId)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
try
{
using (var dbContextTransaction = ctx.Database.BeginTransaction())
{
ctx.Database.ExecuteSqlCommand("delete from [appSchema].[HAccessRight] WHERE UserId = {0} AND IssueId ={1}", accessRight.UserId, accessRight.IssueId);
ctx.Database.ExecuteSqlCommand("delete from [appSchema].[AccessRight] WHERE UserId = {0} AND IssueId ={1}", accessRight.UserId, accessRight.IssueId);
dbContextTransaction.Commit();
HAccessRight har = new HAccessRight();
har.ChangeDate = DateTime.Now;
har.IssueId = accessRight.IssueId;
har.UserId = userId;
User u = ctx.User.Find(accessRight.UserId);
u = ctx.User.Find(accessRight.UserId);
har.Action = u.FirstName + " " + u.LastName + " removed";
har.SelfAssesmentDescr = "";
har.SelfAssessmentValue = 0;
ctx.HAccessRight.Add(har);
ctx.Entry(har).State = EntityState.Added;
ctx.SaveChanges();
}
ctx.Dispose();
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(false);
}
}
public IHttpActionResult PutEmployeeInfo(int id, EmployeeInfo employeeInfo)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (id != employeeInfo.EmpNo)
{
return(BadRequest());
}
db.Entry(employeeInfo).State = EntityState.Modified;
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
if (!EmployeeInfoExists(id))
{
return(NotFound());
}
else
{
throw;
}
}
return(StatusCode(HttpStatusCode.NoContent));
}
/// <summary>
/// grants view access to all parent issues
/// </summary>
/// <param name="userId">user id</param>
/// <param name="issueId">issue id</param>
private static void GrantAccess(int userId, int issueId, ApplicationDBEntities ctx)
{
List <Issue> parentList = IssueOp.RootIssues(issueId, ctx);
foreach (Issue i in parentList)
{
if (i.AccessRight.Where(x => x.UserId == userId).Count() == 0)
{
AccessRight ar = new AccessRight();
ar.UserId = userId;
ar.IssueId = i.Id;
ar.Right = "V";
ar.MailNotification = false;
ar.NotificationLevel = "";
ar.SelfAssesmentDescr = "";
ar.SelfAssessmentValue = 0;
ctx.AccessRight.Add(ar);
ctx.Entry(ar).State = EntityState.Added;
try
{
ctx.SaveChanges();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
/// <summary>
/// marks an notification as read
/// </summary>
/// <param name="notificationId"></param>
public static void MarkNotificationAsRead(int notificationId)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
Notification not = ctx.Notification.Find(notificationId);
not.Read = true;
ctx.Entry(not).State = EntityState.Modified;
ctx.SaveChanges();
ctx.Dispose();
}
public ActionResult Edit([Bind(Include = "id,logDate,logThread,logLevel,logSource,logMessage,exception")] Logs logs)
{
if (ModelState.IsValid)
{
db.Entry(logs).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(logs));
}
/// <summary>
/// adds user to issue
/// </summary>
/// <param name="accessRight">access right</param>
/// <param name="userId">user who is adding oder user</param>
/// <returns>true if successful</returns>
public static bool AddAccessRight(AccessRight accessRight, int userId)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
accessRight.MailNotification = false;
accessRight.NotificationLevel = "";
accessRight.SelfAssessmentValue = 0;
ctx.AccessRight.Add(accessRight);
ctx.Entry(accessRight).State = EntityState.Added;
User u = ctx.User.Find(accessRight.UserId);
HAccessRight har = new HAccessRight();
har.ChangeDate = DateTime.Now;
har.IssueId = accessRight.IssueId;
har.UserId = userId;
har.Action = u.FirstName + " " + u.LastName + " added";
har.SelfAssesmentDescr = "";
har.SelfAssessmentValue = 0;
ctx.HAccessRight.Add(har);
ctx.Entry(har).State = EntityState.Added;
try
{
ctx.SaveChanges();
}
catch (DbEntityValidationException ex)
{
Console.WriteLine(ex.Message);
ctx.AccessRight.Remove(accessRight);
return(false);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
ctx.AccessRight.Remove(accessRight);
return(false);
}
GrantAccess(accessRight.UserId, accessRight.IssueId, ctx);
ctx.Dispose();
return(true);
}
/// <summary>
/// adds a new comment
/// </summary>
/// <param name="comment"></param>
public static void AddComment(Comment comment)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
comment.DateTime = System.DateTime.Now;
ctx.Comment.Add(comment);
ctx.Entry(comment).State = EntityState.Added;
ctx.SaveChanges();
ctx.Dispose();
}
/// <summary>
/// overthinks an decision
/// </summary>
/// <param name="decision"></param>
/// <param name="userId"></param>
public static void UpdateDecision(Decision decision, int userId)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
Decision entity = ctx.Decision.Where(x => x.IssueId == decision.IssueId).FirstOrDefault();
HDecision hdec = new HDecision();
hdec.IssueId = decision.IssueId;
hdec.ChangeDate = DateTime.Now;
hdec.AlternativeId = decision.AlternativeId;
hdec.Action = "Explanation updated";
hdec.Explanation = decision.Explanation;
hdec.UserId = userId;
ctx.HDecision.Add(hdec);
ctx.Entry(hdec).State = EntityState.Added;
entity.Explanation = decision.Explanation;
ctx.Entry(entity).State = EntityState.Modified;
ctx.SaveChanges();
ctx.Dispose();
}
/// <summary>
/// adds tags to issue
/// </summary>
/// <param name="tagList">list of tags (if tag id is -1 then new tag will be created)</param>
/// <param name="issueId">issue id</param>
/// <param name="userId">user who is performing operation</param>
public static void AddTagsToIssue(List <Tag> tagList, int issueId, int userId)
{
string sql;
ApplicationDBEntities ctx = new ApplicationDBEntities();
ApplicationDBEntities ctx2 = new ApplicationDBEntities();
DbCommand cmd;
ctx2.Database.Connection.Open();
foreach (Tag tag in tagList)
{
using (var dbContextTransaction = ctx.Database.BeginTransaction())
{
if (tag.Id == -1)
{
sql = "INSERT INTO appSchema.[Tag] (Name) OUTPUT INSERTED.Id VALUES ({0})";
var res = ctx.Database.SqlQuery <int>(sql, tag.Name);
dbContextTransaction.Commit();
tag.Id = res.FirstOrDefault();
}
sql = "INSERT INTO appSchema.[TagIssue] VALUES (" + tag.Id + "," + issueId + ")";
cmd = ctx2.Database.Connection.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
try
{
cmd.ExecuteNonQuery();
//changes to history table
HTagIssue htagIssue = new HTagIssue();
htagIssue.ChangeDate = DateTime.Now;
htagIssue.TagId = tag.Id;
htagIssue.IssueId = issueId;
htagIssue.UserId = userId;
htagIssue.Action = "tag added (" + tag.Name + ")";
ctx2.Entry(htagIssue).State = EntityState.Added;
ctx2.SaveChanges();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
ctx.Dispose();
ctx2.Database.Connection.Close();
ctx2.Dispose();
}
/// <summary>
/// removes a list of tags from an issue
/// </summary>
/// <param name="tagList">list of tags</param>
/// <param name="issueId">issue</param>
public static void RemoveTagsFromIssue(List <Tag> tagList, int issueId, int userId)
{
TagIssue help;
ApplicationDBEntities ctx = new ApplicationDBEntities();
foreach (Tag tag in tagList)
{
help = ctx.TagIssue.Find(tag.Id, issueId);
ctx.TagIssue.Remove(help);
ctx.Entry(help).State = EntityState.Deleted;
ctx.SaveChanges();
HTagIssue htagIssue = new HTagIssue();
htagIssue.ChangeDate = DateTime.Now;
htagIssue.TagId = tag.Id;
htagIssue.IssueId = issueId;
htagIssue.UserId = userId;
htagIssue.Action = "tag deleted (" + tag.Name + ")";
ctx.Entry(htagIssue).State = EntityState.Added;
ctx.SaveChanges();
}
ctx.Dispose();
}
/// <summary>
/// adds new notificatio
/// </summary>
/// <param name="notification">new notification</param>
/// <returns>id of inserted notification</returns>
public static int AddNotification(Notification notification)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
Notification newNot = ctx.Notification.Create();
int id;
newNot.IssueId = notification.IssueId;
newNot.UserId = notification.UserId;
newNot.Type = notification.Type;
newNot.Text = notification.Text;
newNot.Read = false;
newNot.AddedDate = System.DateTime.Now;
ctx.Entry(newNot).State = EntityState.Added;
ctx.SaveChanges();
id = newNot.Id;
ctx.Dispose();
return(id);
}
/// <summary>
/// deletes issue
/// </summary>
/// <param name="issueId"></param>
/// <returns>returns true if delete was successful</returns>
public static bool DeleteIssue(int issueId)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
Issue issue = ctx.Issue.Find(issueId);
ctx.Issue.Remove(issue);
ctx.Entry(issue).State = EntityState.Deleted;
try
{
ctx.SaveChanges();
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
ctx.Dispose();
return(false);
}
/// <summary>
/// adds properties to user
/// </summary>
/// <param name="userId"></param>
/// <param name="properties"></param>
/// <returns></returns>
public static List <Property> AddUserProperties(int userId, List <Property> properties)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
using (var dbContextTransaction = ctx.Database.BeginTransaction())
{
ctx.Database.ExecuteSqlCommand("delete from [appSchema].[UserProperty] WHERE UserId =" + userId);
dbContextTransaction.Commit();
}
ctx.SaveChanges();
var updateProp = ctx.Property.First();
foreach (Property prop in properties)
{
if (prop.Id == -1)
{
updateProp = ctx.Property.Create();
updateProp.Name = prop.Name;
updateProp = ctx.Property.Add(updateProp);
ctx.Entry(updateProp).State = EntityState.Added;
ctx.SaveChanges();
}
else
{
updateProp = ctx.Property.Find(prop.Id);
}
string sqlInsert = "INSERT INTO UserProperty (UserId, PropertyId) VALUES({0},{1})";
ctx.Database.ExecuteSqlCommand(sqlInsert, userId, updateProp.Id);
ctx.SaveChanges();
}
ctx.Dispose();
return(GetUserProperties(userId));
}
/// <summary>
/// tries to save criteria weight comparisons
/// </summary>
/// <param name="list">list of comparisons to be saved</param>
/// <returns>returns true if consistency check OK and save is performed
/// if consistency check faild returns false</returns>
public static List <CriterionWeight> SaveWeightComparison(List <PairwiseComparisonCC> list)
{
List <CriterionWeight> criteriaWeights = new List <CriterionWeight>();
if (list == null || list.Count == 0)
{
return(criteriaWeights);
}
//first check if user has made comparisons
//if true then delete old comparisons
ApplicationDBEntities ctx = new ApplicationDBEntities();
int issueId = ctx.Criterion.Find(list[0].CriterionLeft).Issue;
int userId = list[0].UserId;
string sqlQuery = "SELECT COUNT(*) FROM PairwiseComparisonCC WHERE UserId = {0} AND CriterionLeft IN (SELECT Id From Criterion Where Issue = {1})";
if (ctx.Database.SqlQuery <int>(sqlQuery, userId, issueId).FirstOrDefault() > 0)
{
sqlQuery = "DELETE FROM PairwiseComparisonCC WHERE UserId = {0} AND CriterionLeft IN (SELECT Id From Criterion Where Issue = {1})";
ctx.Database.ExecuteSqlCommand(sqlQuery, userId, issueId);
sqlQuery = "DELETE FROM CriterionWeight WHERE UserId = {0} AND CriterionId IN (SELECT Id From Criterion Where Issue = {1})";
ctx.Database.ExecuteSqlCommand(sqlQuery, userId, issueId);
ctx.SaveChanges();
}
sqlQuery = "SELECT Count(*) From Criterion Where Issue = {0}";
int critCnt = ctx.Database.SqlQuery <int>(sqlQuery, issueId).FirstOrDefault();
//sort comparisons
list = list.OrderBy(x => x.CriterionLeft).ThenBy(x => x.CriterionRight).ToList();
//create reciprocal matrix
Matrix <double> critMatrix = Matrix <double> .Build.Dense(critCnt, critCnt, 1.0);
int i = 0;
int j = 1;
foreach (var pcc in list)
{
if (j == critCnt)
{
i++;
j = i + 1;
}
critMatrix[i, j] = pcc.Value;
critMatrix[j, i] = 1.0 / pcc.Value;
j++;
}
//if consistency check ok insert criterion weights
if (AhpConsistency(critMatrix))
{
foreach (var pcc in list)
{
ctx.PairwiseComparisonCC.Add(pcc);
ctx.Entry(pcc).State = System.Data.Entity.EntityState.Added;
}
ctx.SaveChanges();
Vector <double> priorityWeightVector = Eigenvector(critMatrix);
List <Criterion> cList = ctx.Criterion.Where(x => x.Issue == issueId).OrderBy(x => x.Id).ToList();
CriterionWeight cw;
i = 0;
foreach (Criterion c in cList)
{
cw = new CriterionWeight();
cw.UserId = userId;
cw.CriterionId = c.Id;
cw.Weight = priorityWeightVector[i];
ctx.CriterionWeight.Add(cw);
ctx.Entry(cw).State = System.Data.Entity.EntityState.Added;
criteriaWeights.Add(cw);
i++;
}
ctx.SaveChanges();
ctx.Dispose();
}
return(criteriaWeights);
}
public static void CalculateResult(int issueId)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
string sqlQuery;
//calculate assessment sum
sqlQuery = "(SELECT SUM(SelfAssessmentValue) From AccessRight a WHERE " +
"IssueId = {0} AND a.UserId in (Select distinct(UserId) From Rating r, " +
"Criterion c Where r.CriterionId = c.Id AND c.Issue = {0}))";
double selfAssessmentSum = ctx.Database.SqlQuery <double>(sqlQuery, issueId).FirstOrDefault();
selfAssessmentSum = selfAssessmentSum / 1.0;
sqlQuery = "SELECT Id FROM Alternative WHERE IssueId = {0} ORDER BY Id";
List <int> altIds = ctx.Database.SqlQuery <int>(sqlQuery, issueId).ToList();
sqlQuery = "SELECT Id FROM Criterion WHERE Issue = {0} ORDER BY Id";
List <int> critIds = ctx.Database.SqlQuery <int>(sqlQuery, issueId).ToList();
Matrix <double> resultMatrix = DenseMatrix.Build.Dense(altIds.Count, critIds.Count);
int i = 0;
int j = 0;
foreach (int critId in critIds)
{
j = 0;
foreach (int altId in altIds)
{
sqlQuery = "(Select SUM(r.Value * ar.SelfAssessmentValue / {0}) " +
"From Rating r, Criterion c, Issue i, AccessRight ar " +
"WHERE r.CriterionId = c.Id AND " +
"c.Issue = i.Id AND " +
"i.id = {1} AND " +
"ar.IssueId = i.Id AND " +
"ar.UserId = r.UserId AND " +
"c.Id = {2} AND " +
"r.AlternativeId = {3})";
resultMatrix[j, i] = ctx.Database.SqlQuery <double>(sqlQuery, selfAssessmentSum, issueId, critId, altId).FirstOrDefault();
j++;
}
i++;
}
Vector <double> weightVector = DenseVector.Build.Dense(critIds.Count);
i = 0;
foreach (Criterion crit in ctx.Criterion.Where(x => x.Issue == issueId).OrderBy(x => x.Id).ToList())
{
weightVector[i] = crit.Weight ?? default(double);
i++;
}
Vector <double> resultVector = resultMatrix.Multiply(weightVector);
i = 0;
Alternative alt;
foreach (double value in resultVector)
{
alt = ctx.Alternative.Find(altIds[i]);
alt.Rating = value;
ctx.Entry(alt).State = System.Data.Entity.EntityState.Modified;
i++;
}
ctx.SaveChanges();
ctx.Dispose();
}
/// <summary>
/// tries to save alternative comparisons
/// </summary>
/// <param name="issueId"></param>
/// <param name="userId"></param>
/// <param name="compList"></param>
/// <returns>if save was successfull and consistence check ok, else returns error message with detail consistency problem</returns>
public static string SaveAlternativeComparison(int issueId, int userId, List <PairwiseComparisonAC> compList)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
List <Criterion> cList = ctx.Criterion.Where(x => x.Issue == issueId).OrderBy(x => x.Id).ToList();
List <PairwiseComparisonAC> compUnderCrit;
Matrix <double> altCritCompMatrix;
List <int> altIds = ctx.Alternative.Where(x => x.IssueId == issueId).Select(x => x.Id).ToList();
Rating rat;
int altCnt = altIds.Count();
int i, j;
string msg = "success";
altIds.Sort();
//first check if user has made comparisons
//if true then delete old comparisons
string sqlQuery = "SELECT COUNT(*) FROM PairwiseComparisonAC WHERE UserId = {0} AND AlternativeLeft IN (SELECT Id From Alternative Where IssueId = {1})";
if (ctx.Database.SqlQuery <int>(sqlQuery, userId, issueId).FirstOrDefault() > 0)
{
sqlQuery = "DELETE FROM PairwiseComparisonAC WHERE UserId = {0} AND AlternativeLeft IN (SELECT Id From Alternative Where IssueId = {1})";
ctx.Database.ExecuteSqlCommand(sqlQuery, userId, issueId);
sqlQuery = "DELETE FROM Rating WHERE UserId = {0} AND AlternativeId IN (SELECT Id From Alternative Where IssueId = {1})";
ctx.Database.ExecuteSqlCommand(sqlQuery, userId, issueId);
}
foreach (Criterion crit in cList)
{
//comparisons of alternatives under criterion
compUnderCrit = compList.Where(x => x.CriterionId == crit.Id).OrderBy(x => x.AlternativeLeft).ThenBy(x => x.AlternativeRight).ToList();
//make repripocal matrix
altCritCompMatrix = Matrix <double> .Build.Dense(altCnt, altCnt, 1.0);
i = 0;
j = 1;
foreach (var pca in compUnderCrit)
{
if (j == altCnt)
{
i++;
j = i + 1;
}
altCritCompMatrix[i, j] = pca.Value;
altCritCompMatrix[j, i] = 1.0 / pca.Value;
j++;
}
//if consistency OK do inserts
if (AhpConsistency(altCritCompMatrix))
{
//insert into pairwise alternative comparison table
foreach (var pca in compUnderCrit)
{
ctx.PairwiseComparisonAC.Add(pca);
ctx.Entry(pca).State = System.Data.Entity.EntityState.Added;
}
//calculate priority vector and insert into Rating table
Vector <double> priorityAlternativeVectorUnderCrit = Eigenvector(altCritCompMatrix);
i = 0;
foreach (int altId in altIds)
{
rat = new Rating();
rat.CriterionId = crit.Id;
rat.AlternativeId = altId;
rat.UserId = userId;
rat.Value = priorityAlternativeVectorUnderCrit[i];
ctx.Rating.Add(rat);
i++;
}
}
else
{
msg = "Evaluation inconsistent under criterion " + crit.Name + "!";
break;
}
}
if (msg == "success")
{
ctx.SaveChanges();
}
ctx.Dispose();
return(msg);
}
public void Update(Company company)
{
ctx.Entry(company).State = System.Data.Entity.EntityState.Modified;
}
/// <summary>
/// updates the accessrights for an issue
/// </summary>
/// <param name="addedList">new access rights added to issue</param>
/// <param name="deletedList">accessrights which should be deleted</param>
/// <param name="editedList">list of edited access rights</param>
/// <param name="issueId"></param>
/// <param name="userId"></param>
public static void UpdateRights(List <AccessRight> addedList, List <AccessRight> deletedList, List <AccessRight> editedList, int issueId, int userId)
{
ApplicationDBEntities ctx = new ApplicationDBEntities();
User u;
List <AccessRight> intAddList = addedList.Intersect(ctx.AccessRight).ToList();
deletedList = deletedList.Distinct().ToList();
addedList = addedList.Except(intAddList).ToList();
foreach (AccessRight ar in addedList)
{
ar.IssueId = issueId;
ar.MailNotification = false;
ar.NotificationLevel = "";
ar.SelfAssesmentDescr = "";
ar.SelfAssessmentValue = 0;
ctx.AccessRight.Add(ar);
ctx.Entry(ar).State = EntityState.Added;
HAccessRight har = new HAccessRight();
har.ChangeDate = DateTime.Now;
har.IssueId = ar.IssueId;
har.UserId = userId;
u = ctx.User.Find(ar.UserId);
har.Action = u.FirstName + " " + u.LastName + " added";
har.SelfAssesmentDescr = "";
har.SelfAssessmentValue = 0;
ctx.HAccessRight.Add(har);
ctx.Entry(har).State = EntityState.Added;
try
{
ctx.SaveChanges();
}
catch (DbEntityValidationException ex)
{
Console.WriteLine(ex.Message);
ctx.AccessRight.Remove(ar);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
ctx.AccessRight.Remove(ar);
}
GrantAccess(ar.UserId, issueId, ctx);
}
foreach (AccessRight ar in deletedList)
{
if (ctx.AccessRight.Where(x => x.UserId == ar.UserId && x.IssueId == issueId).Count() > 0)
{
using (var dbContextTransaction = ctx.Database.BeginTransaction())
{
ctx.Database.ExecuteSqlCommand("delete from [appSchema].[AccessRight] WHERE UserId = {0} AND IssueId ={1}", ar.UserId, issueId);
dbContextTransaction.Commit();
}
}
}
if (editedList == null)
{
return;
}
AccessRight tmp;
foreach (AccessRight ar in editedList)
{
if (ar.IssueId != 0)
{
tmp = ctx.AccessRight.AsNoTracking().Where(x => x.UserId == ar.UserId && x.IssueId == issueId).FirstOrDefault();
if (tmp != null && tmp.Right != ar.Right)
{
tmp.Right = ar.Right;
ctx.Entry(tmp).State = EntityState.Modified;
ctx.SaveChanges();
}
}
}
ctx.Dispose();
}
/// <summary>
/// updates an issue
/// </summary>
/// <param name="issue"></param>
/// <param name="userId">user who is performing this operation</param>
/// <returns>true if update made</returns>
public static int UpdateIssue(Issue issue, int userId)
{
Issue updateIssue;
ApplicationDBEntities ctx = new ApplicationDBEntities();
updateIssue = ctx.Issue.Find(issue.Id);
List <string> updatedFields = new List <string>();
bool updated = false;
if (issue.Title != updateIssue.Title)
{
updateIssue.Title = issue.Title;
updated = true;
updatedFields.Add("title");
}
if (updateIssue.Description != issue.Description)
{
updateIssue.Description = issue.Description;
updated = true;
updatedFields.Add("description");
}
if (updateIssue.AnonymousPosting != issue.AnonymousPosting)
{
updateIssue.AnonymousPosting = issue.AnonymousPosting;
updated = true;
updatedFields.Add("anonymous posting");
}
if (updateIssue.Status != issue.Status)
{
updateIssue.Status = issue.Status;
updated = true;
updatedFields.Add("status");
}
if (updateIssue.Parent != issue.Parent)
{
updateIssue.Parent = issue.Parent;
updated = true;
updatedFields.Add("parent issue");
}
if (updateIssue.DependsOn != issue.DependsOn)
{
updateIssue.DependsOn = issue.DependsOn;
updated = true;
updatedFields.Add("depends on issue");
}
if (updateIssue.Setting != issue.Setting)
{
updateIssue.Setting = issue.Setting;
updated = true;
updatedFields.Add("setting");
}
if (updated)
{
ctx.Entry(updateIssue).State = EntityState.Modified;
ctx.SaveChanges();
HIssue hissue = new HIssue();
hissue.ChangeDate = DateTime.Now;
hissue.IssueId = issue.Id;
hissue.UserId = userId;
hissue.Action = "Issue updated (";
bool first = true;
foreach (string str in updatedFields)
{
if (first)
{
hissue.Action = hissue.Action + str;
first = false;
}
else
{
hissue.Action = hissue.Action + " ," + str;
}
}
hissue.Action = hissue.Action + ")";
hissue.Status = issue.Status;
hissue.Title = issue.Title;
hissue.Description = issue.Description;
hissue.Setting = issue.Setting;
hissue.Status = issue.Status;
hissue.AnonymousPosting = issue.AnonymousPosting;
hissue.Parent = issue.Parent;
hissue.DependsOn = issue.DependsOn;
hissue.GroupThink = issue.GroupThink;
hissue.ReviewRating = issue.ReviewRating;
//problem with action
hissue.Action = "issue updated";
ctx.HIssue.Add(hissue);
ctx.Entry(hissue).State = EntityState.Added;
try
{
ctx.SaveChanges();
}catch (DbEntityValidationException ex)
{
Console.WriteLine(ex.Message);
}
}
ctx.Dispose();
return(updateIssue.Id);
}
