Diplom_O/DataBase/RabotinkDB.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 Rabotnik
	{
		[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
		public int Id { get; set; }
		[ForeignKey("Chel")]
		public int ChelId { get; set; }
		public Chel Chel { get; set; }
		[ForeignKey("Shtat")]
		public int? ShtatId { get; set; }
		public Shtat Shtat { get; set; }
		public string TabNum { get; set; }
		public bool AnotherWork { get; set; }
		public DateTime Start { get; set; }
		public DateTime? End { get; set; }
		public List<RabotnikTypeStaj> RabotnikTypeStajy { get; set; }
	}

	public static partial class FuncDB
	{
		public static void RabotnikCheckValid(Rabotnik obj)
		{
			try
			{
				if (obj == null)
					throw new Exception("Ошибка инициализации сотрудника.");
				if (string.IsNullOrEmpty(obj.TabNum) && !obj.AnotherWork)
					throw new Exception("Пустой табельный номер.");
				if (
					obj.Start > (obj.End.HasValue ? obj.End.Value : DateTime.Now)
					)
					throw new Exception("Неверная дата.");
			}
			catch
			{
				throw;
			}
		}

		public static Rabotnik RabotnikGetById(int id)
		{
			try
			{
				using (var db = new MainDB())
				{
					var res =
						from a in db.Rabotniky
						where a.Id == id
						select a;
					try
					{
						return res.Single();
					}
					catch
					{
						return null;
					}
				}
			}
			catch
			{
				throw;
			}
		}

		public static Rabotnik[] RabotnikHistoryById(int id)
		{
			try
			{
				var rabotnik = RabotnikGetById(id);
				RabotnikCheckValid(rabotnik);
				return
					RabotnikHistoryByChelId(
						rabotnik.ChelId
						);
			}
			catch
			{
				throw;
			}
		}
		public static Rabotnik[] RabotnikHistoryByRabotingId(Rabotnik obj)
		{
			try
			{
				RabotnikCheckValid(obj);
				return RabotnikHistoryByChelId(
					obj.ChelId
					);
			}
			catch
			{
				throw;
			}
		}
		public static Rabotnik[] RabotnikHistoryByChelId(int id)
		{
			try
			{
				using (var db = new MainDB())
				{
					var res =
						from a in db.Rabotniky
						where a.ChelId == id
						orderby a.Start
						select a;
					return res.ToArray();
				}
			}
			catch
			{
				throw;
			}
		}
		
		public static bool RabotnikCheckDate(int chelId, DateTime start, DateTime? end, int rabotnikId = -1)
    {
			try
			{
				using (var db = new MainDB())
				{
					var now = DateTime.Now;
					var _end = end ?? now;
					return (
						from a in db.Rabotniky
						where
							a.ChelId == chelId
								&&
							(rabotnikId != -1 ? a.Id != rabotnikId : true)
								&&
							(
								(
									a.Start >= start
										&&
									a.Start <= _end
								)
									||
								(
									(a.End ?? now) >= start
										&&
									(a.End ?? now) <= _end
								)
							)
						select a
						).Count() == 0;
				}
			}
			catch
			{
				throw;
			}
		}

		public static void RabotnikAdd(Rabotnik obj)
		{
			try
			{
				RabotnikCheckValid(obj);
				if (!RabotnikCheckDate(obj.ChelId, obj.Start, obj.End))
					throw new Exception("Попадает в существующий временной интервал.");
				using (var db = new MainDB())
				{
					db.Rabotniky.Add(obj);
					db.SaveChanges();
				}
			}
			catch
			{
				throw;
			}
		}
		public static void RabotnikChange(Rabotnik obj)
		{
			try
			{
				RabotnikCheckValid(obj);
				var localObjById = RabotnikGetById(obj.Id);
				if (localObjById == null)
					throw new Exception("Не существует запись о сотруднике.");
				if (obj.ChelId != localObjById.ChelId)
					throw new Exception("Нельзя менять человека.");
				if (!RabotnikCheckDate(obj.ChelId, obj.Start, obj.End, obj.Id))
					throw new Exception("Пересекатеся с другими записями о работе.");
				/*if (ShtatGetById(obj.ShtatId) == null)
					throw new Exception("Должность не существует.");*/
				if (Program.IsDebug)
					throw new Exception("НАДО ДОРАБОТАТЬ ПРОВЕРКУ ЗАПОЛНЕНИЯ ДОЛЖНОСТЕЙ");
				using (var db = new MainDB())
				{
					db.Rabotniky.Update(obj);
					db.SaveChanges();
				}
			}
			catch
			{
				throw;
			}
		}
		public static void RabotnikDelete(Rabotnik obj)
		{
			try
			{
				if (obj == null)
					return;
				var localObj = RabotnikGetById(obj.Id);
				if (localObj == null)
					throw new Exception("Запись о сотруднике не существует.");
				using (var db = new MainDB())
				{
					var type = FuncDB.RabotnikTypeStajList(obj.Id);
					foreach (var r in type)
						FuncDB.RabotnikTypeStajDelete(r);
					db.Rabotniky.Remove(localObj);
					db.SaveChanges();
				}
			}
			catch
			{
				throw;
			}
		}
		public static Rabotnik[] RabotnikList(string filter = null)
		{
			try
			{
				using (var db = new MainDB())
				{
					var _filter = string.IsNullOrEmpty(filter) ? null : filter.ToLower();
					var rabotnik = (
						from a in db.Rabotniky
						select a
						);
					return rabotnik.ToArray();

				}
			}
			catch
			{
				throw;
			}
		}
		public static Rabotnik[] RabotnikListLast(string filter = null)
    {
			try
			{
				using (var db = new MainDB())
				{
					var _chel = FuncDB.ChelList();
					var rabotnik = new List<Rabotnik>();
					var _filter = string.IsNullOrEmpty(filter) ? null : filter.ToLower();
					foreach (var c in _chel)
          {
						var rab = (
							from a in db.Rabotniky
							where a.End == null && a.ChelId == c.Id
							select a
							).ToArray();
						if (rab.Length == 0)
            {
							rab = (
								from a in db.Rabotniky
								where a.ChelId == c.Id
								orderby a.End descending
								select a
								).ToArray();
            }
						if (rab.Length > 0)
							rabotnik.Add(rab[0]);
          }
					return rabotnik.ToArray();
				}
			}
			catch
			{
				throw;
			}
		}
		public static Rabotnik[] RabotnikListByChelId(int chelId)
		{
			try
			{
				using (var db = new MainDB())
				{
					var rabotnik = (
						from a in db.Rabotniky
						where a.ChelId == chelId
						orderby a.Start
						select a
						);
					return rabotnik.ToArray();
				}
			}
			catch
			{
				throw;
			}
		}

		public static string[][] RabotnikTableMain(string filter = null)
		{
			try
			{
				return RabotnikTableMain(RabotnikListLast(filter));
			}
			catch
			{
				throw;
			}
		}
		public static string[][] RabotnikTableMain(Rabotnik[] data)
		{
			try
			{
				using (var db = new MainDB())
				{
					var result = new List<string[]>();
					foreach (var rabotnik in data)
					{
						result.Add(
							new string[]
							{
							rabotnik.Id.ToString(),
							rabotnik.TabNum,
							ChelGetFIOShortById(rabotnik.ChelId),
							rabotnik.AnotherWork ?
								"Иное место" :
								ShtatGetById(rabotnik.ShtatId.Value).Doljnost
							}
						);
					}
					return result.ToArray();
				}
			}
			catch
			{
				throw;
			}

		}
		public static string[][] RabotnikByChelIdTable(int chelId)
		{
			try
			{
				return RabotnikByChelIdTable(RabotnikListByChelId(chelId));
			}
			catch
			{
				throw;
			}
		}
		public static string[][] RabotnikByChelIdTable(Rabotnik[] data)
		{
			try
			{
				using (var db = new MainDB())
				{
					var result = new List<string[]>();
					foreach (var rabotnik in data)
					{
						result.Add(
							new string[]
							{
							rabotnik.Id.ToString(),
							rabotnik.TabNum,
							rabotnik.AnotherWork ?
								"Иное место" :
								ShtatGetById(rabotnik.ShtatId.Value).Doljnost,
							rabotnik.Start.ToString("yyyy.MM.dd"),
							rabotnik.End.HasValue ? rabotnik.End.Value.ToString("yyyy.MM.dd") : "По настоящее время"
							}
						);
					}
					return result.ToArray();
				}
			}
			catch
			{
				throw;
			}
		}
		public static (int year, int month, int day) RabotnikGetTotalTime(Rabotnik obj, DateTime? onDate = null)
    {
			if (!onDate.HasValue)
				onDate = DateTime.Now;
			var year = 0;
			var month = 0;
			var day = 0;

			if (obj == null)
				return (year, month, day);
			var cursor =
				obj.End.HasValue ?
					obj.End.Value <= onDate ?
						obj.End.Value :
						onDate.Value :
					onDate.Value;
			var finish = obj.Start;
			if (cursor <= finish)
				return (year, month, day);
			while (finish.Year < cursor.Year)
      {
				year++;
				cursor = cursor.AddYears(-1);
      }
			if (finish.Month > cursor.Month)
      {
				year--;
				cursor = cursor.AddYears(1);
			}
			while (finish.Month != cursor.Month)
			{
				month++;
				cursor = cursor.AddMonths(-1);
			}
			if (finish.Day > cursor.Day)
			{
				month--;
				cursor = cursor.AddMonths(1);
			}
			while (finish.Day != cursor.Day)
			{
				day++;
				cursor = cursor.AddDays(-1);
			}
			while (day < 0)
			{
				day += 30;
				month--;
			}

			while (month < 0)
      {
				month += 12;
				year--;
      }
			return (year, month, day);
    }
	}
}