Diplom_O/DataBase/ChildDB.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Diplom_O.DataBase
{
	public class Child
	{
		[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
		public int Id { get; set; }
		[ForeignKey("Chel")]
		public int? Parent1Id { get; set; }
		public Chel Parent1 { get; set; }
		[ForeignKey("Chel")]
		public int? Parent2Id { get; set; }
		public Chel Parent2 { get; set; }
		public string FName { get; set; }
		public string SName { get; set; }
		public string TName { get; set; }
		public bool Male { get; set; }
		public DateTime Birthday { get; set; }

	}

	public static partial class FuncDB
	{
		public static void ChildCheckValid(Child obj)
		{
			try
			{
				if (obj == null)
					throw new Exception("Ошибка инициализации ребенка.");
				if (string.IsNullOrEmpty(obj.FName))
					throw new Exception("Пустая фамилия.");
				if (string.IsNullOrEmpty(obj.FName))
					throw new Exception("Пустое имя.");
				if (!obj.Parent2Id.HasValue && !obj.Parent1Id.HasValue)
					throw new Exception("Не указано ни одного родителя.");
				if (obj.Parent2Id.HasValue && obj.Parent1Id.HasValue && obj.Parent1Id.Value == obj.Parent2Id.Value)
					throw new Exception("Указан один и тот же родитель 2 раза.");
			}
			catch
			{
				throw;
			}
		}
		public static Child ChildGetById(int id)
		{
			try
			{
				using (var db = new MainDB())
				{
					var res =
						from a in db.Childy
						where a.Id == id
						select a;
					try
					{
						return res.Single();
					}
					catch
					{
						return null;
					}
				}
			}
			catch
			{
				throw;
			}
		}

		public static void ChildAdd(Child obj)
		{
			try
			{
				ChildCheckValid(obj);
				if (!obj.Parent1Id.HasValue && obj.Parent2Id.HasValue)
        {
					obj.Parent1Id = obj.Parent2Id;
					obj.Parent2Id = null;
				}
				using (var db = new MainDB())
				{
					db.Childy.Add(obj);
					db.SaveChanges();
				}
			}
			catch
			{
				throw;
			}
		}
		public static void ChildChange(Child obj)
		{
			try
			{
				ChildCheckValid(obj);
				var localObjById = ChildGetById(obj.Id);
				if (localObjById == null)
					throw new Exception("Не существует запись о ребенке.");
				if (!obj.Parent1Id.HasValue && obj.Parent2Id.HasValue)
				{
					obj.Parent1Id = obj.Parent2Id;
					obj.Parent2Id = null;
				}
				using (var db = new MainDB())
				{
					db.Childy.Update(obj);
					db.SaveChanges();
				}
			}
			catch
			{
				throw;
			}
		}
		public static void ChildDelete(Child obj)
		{
			try
			{
				if (obj == null)
					return;
				var localObj = ChildGetById(obj.Id);
				if (localObj == null)
					throw new Exception("Запись о ребенке не существует.");
				using (var db = new MainDB())
				{
					db.Childy.Remove(localObj);
					db.SaveChanges();
				}
			}
			catch
			{
				throw;
			}
		}
		public static Child[] ChildList(string filter = null)
		{
			try
			{
				using (var db = new MainDB())
				{
					var child = (
						from a in db.Childy
						select a
						).ToList();
					var _filter = string.IsNullOrEmpty(filter) ? "" : filter.ToLower();
					for (var i = 0; i < child.Count;)
					{
						var fname = child[i].FName.ToLower();
						var sname = child[i].SName.ToLower();
						var tname = child[i].TName.ToLower();
						var bday = child[i].Birthday.ToString("yyyy.MM.dd");
						if (
							fname.Contains(filter) || sname.Contains(filter) ||
							tname.Contains(filter) || bday.Contains(filter)
							)
							i++;
						else
							child.RemoveAt(i);
					}
					return child.ToArray();
				}
			}
			catch
			{
				throw;
			}
		}

		public static string[][] ChildTableMain(string filter = null)
		{
			try
			{
				using (var db = new MainDB())
				{
					var result = new List<string[]>();
					var childs = ChildList(filter);
					foreach (var child in childs)
					{
						result.Add(
							new string[]
							{
								child.Id.ToString(),
								child.FName + " " +
									child.SName[0].ToString() + "." +
									((!string.IsNullOrEmpty(child.TName)) ?
										child.TName[0].ToString() + "." : ""),
								child.Male ? "Мальчик" : "Девочка",
								child.Birthday.ToString("yyyy.MM.dd"),
								child.Parent1Id.HasValue ? ChelGetFIOShortById(child.Parent1Id.Value) : "",
								child.Parent2Id.HasValue ? ChelGetFIOShortById(child.Parent2Id.Value) : ""
							}
						);
					}
					return result.ToArray();
				}
			}
			catch
			{
				throw;
			}
		}

		public static int ChildCount(DateTime dt, bool? isMale = null)
    {
			try
			{
				using (var db = new MainDB())
				{
					var workIds = (
						from a in db.Rabotniky
						where
							!a.End.HasValue ||
							(
								dt >= a.Start
									&&
								dt <= a.End.Value
							)
						select a.ChelId
						).ToArray();
					var _dt = dt.AddYears(-15);
					var res = (
						from a in db.Childy
						where
							a.Birthday > _dt
								&&
							(
								!isMale.HasValue
									||
								a.Male == isMale.Value
							)
								&&
							(
								(
									a.Parent1Id.HasValue
										&&
									workIds.Contains(a.Parent1Id.Value)
								)
								 ||
								(
									a.Parent2Id.HasValue
										&&
									workIds.Contains(a.Parent2Id.Value)
								)
							)
						select a
						).Count();
					return res;
				}
			}
			catch
			{
				throw;
			}
		}

	}
}