Diplom_O/DataBase/FreeDB.cs

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

namespace Diplom_O.DataBase
{
	public class Free
	{
		[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
		public int Id { get; set; }
		[ForeignKey("Chel")]
		public int ChelId { get; set; }
		public Chel Chel { get; set; }
		public string Type { get; set; }
		public DateTime Start { get; set; }
		public DateTime? End { get; set; }
	}

	public static partial class FuncDB
	{
		public static void FreeCheckValid(Free obj)
		{
			try
			{
				if (obj == null) throw new Exception("Ошибка инициализации.");
				if (string.IsNullOrEmpty(obj.Type)) throw new Exception("Тип не указан.");
			}
			catch { throw; }
		}
		public static Free FreeGetById(int id)
		{
			try
			{
				using (var db = new MainDB())
				{
					var res =
						from a in db.Freey
						where a.Id == id
						select a;
					try
					{
						return res.Single();
					}
					catch
					{
						return null;
					}
				}
			}
			catch
			{
				throw;
			}
		}

		public static void FreeAdd(Free obj)
		{
			try
			{
				FreeCheckValid(obj);
				using (var db = new MainDB())
				{
					obj.Start = new DateTime(obj.Start.Year, obj.Start.Month, obj.Start.Day, 0, 0, 0);
					if (obj.End.HasValue)
						obj.End = new DateTime(obj.End.Value.Year, obj.End.Value.Month, obj.End.Value.Day, 23, 59, 59);
					db.Freey.Add(obj);
					db.SaveChanges();
				}
			}
			catch
			{
				throw;
			}
		}
		public static void FreeChange(Free obj)
		{
			try
			{
				FreeCheckValid(obj);
				var localObj = FreeGetById(obj.Id);
				if (localObj == null)
					throw new Exception("Отстранение не указано.");
				using (var db = new MainDB())
				{
					obj.Start = new DateTime(obj.Start.Year, obj.Start.Month, obj.Start.Day, 0, 0, 0);
					if (obj.End.HasValue)
						obj.End = new DateTime(obj.End.Value.Year, obj.End.Value.Month, obj.End.Value.Day, 23, 59, 59);
					db.Freey.Update(obj);
					db.SaveChanges();
				}
			}
			catch
			{
				throw;
			}
		}
		public static void FreeDelete(Free obj)
		{
			try
			{
				if (obj == null)
					return;
				var localObj = FreeGetById(obj.Id);
				if (localObj == null)
					throw new Exception("Отстранение не существует.");
				using (var db = new MainDB())
				{
					db.Freey.Remove(localObj);
					db.SaveChanges();
				}
			}
			catch
			{
				throw;
			}
		}

		public static Free[] FreeList(int chelId)
		{
			try
			{
				using (var db = new MainDB())
				{
					var free = (
						from a in db.Freey
						where a.ChelId == chelId
						select a
						).ToArray();
					return free;
				}
			}
			catch
			{
				throw;
			}
		}
		public static Dictionary<string, int> FreeListDays(int chelId, DateTime start, DateTime end)
		{
			try
			{
				using (var db = new MainDB())
        {
					var list =
						from a in db.Freey
						where
							a.ChelId == chelId &&
							a.Start <= end &&
							(!a.End.HasValue || a.End.Value >= start)
						select a;
					var subres = new Dictionary<string, int>();
					foreach(var r in list)
          {
						var s = r.Start <= start ? start : r.Start;
						var e = r.End.HasValue ?
							r.End.Value >= end ? end :
								r.End.Value :
							end;
						var d = (int)Math.Ceiling((e - s).TotalDays);
						if (d <= 0)
							continue;
						if (!subres.ContainsKey("Общее"))
							subres.Add("Общее", 0);
						subres["Общее"] = subres["Общее"] + d;
						if (!subres.ContainsKey(r.Type))
							subres.Add(r.Type, 0);
						subres[r.Type] = subres[r.Type] + d;
          }
					return subres;
        }
			}
			catch
			{
				throw;
			}
		}
		public static string[][] FreeListTable(int chelId, DateTime? start = null, DateTime? end = null)
    {
			try
			{
				using (var db = new MainDB())
				{
					var list =
						from a in db.Freey
						where
							a.ChelId == chelId &&
							(!end.HasValue || a.Start <= end.Value) &&
							(!a.End.HasValue || !start.HasValue || a.End.Value >= start.Value)
						select a;
					var subres = new List<string[]>();
					foreach (var r in list)
					{
						var s = !start.HasValue ? r.Start :
							r.Start <= start ? start.Value : r.Start;
						var e = r.End.HasValue ?
							end.HasValue ?
							r.End.Value >= end ? end.Value :
								r.End.Value :
							end.Value :
							DateTime.Now;
						subres.Add(
							new string[]{
								r.Id.ToString(),
								r.Type,
								s.ToString("yyyy.MM.dd"),
								e.ToString("yyyy.MM.dd"),
								Math.Ceiling((e - s).TotalDays).ToString()
						});
					}
					return subres.ToArray();
				}
			}
			catch
			{
				throw;
			}

		}
	}
}