EnVisageOnline/Main/Source/EnVisage/Code/BLL/SkillsManager.cs

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using EnVisage.Models;
using System.Data.Entity.Infrastructure;
using jQuery.DataTables.Mvc;
using System.Data.SqlClient;
using System.Data.Entity.Core.Objects;
using System.Data;

namespace EnVisage.Code.BLL
{
    public class SkillGroupManager : ManagerBase<Skill, SkillGroupModel>
    {
        public SkillGroupManager(EnVisageEntities dbContext)
            : base(dbContext)
        {
        }
        protected override Skill InitInstance()
        {
            return new Skill { Id = Guid.NewGuid() };
        }

        protected override Skill RetrieveReadOnlyById(Guid key)
        {
            return DataTable.AsNoTracking().FirstOrDefault(t => t.Id == key);
        }

        public override DbSet<Skill> DataTable
        {
            get
            {
                return DbContext.Skills;
            }
        }
        public void Save (EditSkillsModel model)
        {
            if (model == null)
                throw new ArgumentNullException("model");
            foreach (var item in model.Values)
            {
                if (item.IsDeleted)
                {
                    this.Delete(item);
                }
                else
                {
                    this.Save(item);
                }
            }
        }

        private void Delete(SkillGroupModel item)
        {
            if (item == null)
                throw new ArgumentNullException("item");
            var obj = Load(item.Id, false);
            if (obj != null)
            {
				var skills2Delete = obj.Skills.ToArray();
                foreach (var item2Delete in skills2Delete)
                {
					var skill2Resource2Delete2 = item2Delete.Skill2Resource.ToArray();
					foreach (var s2r in skill2Resource2Delete2)
					{
						DbContext.Entry(s2r).State = EntityState.Deleted;
					}

					DbContext.Entry(item2Delete).State = EntityState.Deleted;
                }

				var skill2Resource2Delete = obj.Skill2Resource.ToArray();
				foreach (var s2r in skill2Resource2Delete)
				{
					DbContext.Entry(s2r).State = EntityState.Deleted;
				}

				DbContext.Entry(obj).State = EntityState.Deleted;
            }
        }

        public override Skill Save(SkillGroupModel model)
        {
            if (model == null)
                throw new ArgumentNullException("model");
            var skillManager = new SkillManager(DbContext);
            Skill obj;
            if (model.IsChanged || model.IsNew)
            {
                var group2Save = model.Clone();
                if (model.IsNew)
                {
                    group2Save.Id = Guid.Empty;
                }
                obj = base.Save(group2Save);
            }
            else
                obj = RetrieveReadOnlyById(model.Id);
            if (model.Children.Count > 0)
            {
				bool anyChildRemains = false;

				var currentSkills = model.Id == Guid.Empty
                                       ? new Dictionary<Guid, Skill>()
                                       : DbContext.Skills.Where(t => t.ParentId == model.Id).ToDictionary(t=>t.Id);

				var skillGroupS2Rs = model.Id == Guid.Empty
									   ? new List<Skill2Resource>()
									   : DbContext.Skill2Resource.Where(s2r => s2r.SkillId == model.Id).ToList();

				foreach (var key in model.Children.Keys)
                {
                    var skillItem = model.Children[key];
                    if (skillItem.IsDeleted) // delete item from DB
                    {
                        if (currentSkills.ContainsKey(skillItem.Id))
                        {
							var s2r4Delete = currentSkills[skillItem.Id].Skill2Resource.ToArray();
							foreach (var s2r in s2r4Delete)
							{
								DbContext.Entry(s2r).State = EntityState.Deleted;
							}
							DbContext.Entry(currentSkills[skillItem.Id]).State = EntityState.Deleted;
                        }
                    }
                    else if (skillItem.IsNew && currentSkills.All(t => t.Key != skillItem.Id)) // add item to db
                    {
                        skillItem.ParentId = obj.Id;
                        Skill s = skillManager.Save(skillItem);
						anyChildRemains = true;
						if (skillGroupS2Rs.Count > 0 && model.CopyDataUponDelete)
							CopySkill2Resources(skillGroupS2Rs, s.Id);
					}
                    else if (skillItem.IsChanged) // update item
                    {
                        if (currentSkills.ContainsKey(skillItem.Id))
                        {
                            // save db object instead of model to avoid redundant queries
                            var item2Save = currentSkills[skillItem.Id];
                            item2Save.ParentId = obj.Id;
                            item2Save.Name = skillItem.Name;
                            skillManager.Save(item2Save);
							if (skillGroupS2Rs.Count > 0 && model.CopyDataUponDelete)
								CopySkill2Resources(skillGroupS2Rs, item2Save.Id);
						}
						else
                        {
                            skillItem.ParentId = obj.Id;
							Skill newSkill = skillManager.Save(skillItem);
							if (skillGroupS2Rs.Count > 0 && model.CopyDataUponDelete)
								CopySkill2Resources(skillGroupS2Rs, newSkill.Id);
						}
						anyChildRemains = true;
					}
				}

				if(anyChildRemains)
				{
					foreach(var s2r in skillGroupS2Rs.ToArray())
					{
						DbContext.Entry(s2r).State = EntityState.Deleted;
					}
				}

            }
            return obj;
        }

        public EditSkillsModel LoadSkillsTree ()
        {
            var items = new EditSkillsModel();
            var dbItems = DbContext.Skills.AsNoTracking().Where(t=>!t.ParentId.HasValue)
                .Include(group=>group.Skills.Select(skill=>skill.Skill2Resource))
                .Include(group=>group.Skill2Resource)
                .ToList().OrderBy(t=>t.Name);
            
            foreach (var dbItem in dbItems)
            {
                items.Add(dbItem.Id.ToString(), (SkillGroupModel)dbItem);
            }
            return items;
        }

		private void CopySkill2Resources(List<Skill2Resource> sourceList, Guid newSkillId)
		{
			foreach (Skill2Resource oldS2R in sourceList)
			{
				Guid newId = Guid.NewGuid();
				Skill2Resource rec = new Skill2Resource()
				{
					Id = newId,
					Type = oldS2R.Type,
					EffectiveDate = oldS2R.EffectiveDate,
					ResourceId = oldS2R.ResourceId,
					SkillId = newSkillId,
					Level = oldS2R.Level,
					Interested = oldS2R.Interested,
					DateCreated = oldS2R.DateCreated
				};
				DbContext.Skill2Resource.Add(rec);
			}
		}
    }


    public class SkillManager : ManagerBase<Skill, SkillModel>
    {
        public SkillManager(EnVisageEntities dbContext)
            : base(dbContext)
        {
        }
        protected override Skill InitInstance()
        {
            return new Skill { Id = Guid.NewGuid() };
        }

        protected override Skill RetrieveReadOnlyById(Guid key)
        {
            return DataTable.AsNoTracking().FirstOrDefault(t => t.Id == key);
        }

        public override DbSet<Skill> DataTable
        {
            get
            {
                return DbContext.Skills;
            }
        }

        public override Skill Save(SkillModel model)
        {
            var skill2Save = model.Clone();
            if (model.IsNew)
            {
                skill2Save.Id = Guid.Empty;
            }
            var obj = base.Save(skill2Save);
            return obj;
        }
        public void Save(Skill obj)
        {
            DbContext.Entry(obj).State = EntityState.Modified;
            if (IsContextLocal)
                DbContext.SaveChanges();
        }
	}
}