//Метод внесения данных в таблицу Ставки public void InsertToBetting(object parameter) { if (parameter != null) { var item = parameter as Bettings; try { using (db = new DataContextProvider()) { db.BettingsTable.Add(item); db.SaveChanges(); bettingList = db.BettingsTable.Select(n => n).ToList(); } } catch (Exception) { } } }
//Метод внесения данных в таблицу Расписание public void InsertToShedule(object parameter) { if (parameter != null) { var item = parameter as Shedule; try { using (db = new DataContextProvider()) { db.SheduleTable.Add(item); db.SaveChanges(); sheduleList = db.SheduleTable.Select(n => n).ToList(); } } catch (InvalidOperationException) { } } }
public void Load() { // InitTables(); using (db = new DataContextProvider()) { departamentList = db.DepartamentTable.Select(n => n).ToList(); bettingList = db.BettingsTable.Select(n => n).ToList(); positionList = db.PositionTable.Select(n => n).ToList(); sheduleList = db.SheduleTable.Select(n => n).ToList(); reportList = db.ReportTable.Select(n => n).ToList(); listDates = bettingList.Where(n => n.position == positionList.FirstOrDefault().PositionName.ToString()) .Select(n => new DateDependencyModel { dateParam = (DateTime)n.actionStartDate }).ToList(); } }
public void InsertToReports(object parameter) { if (parameter != null) { var ExternalObjectToReport = parameter as Shedule; using (db = new DataContextProvider()) { sheduleList = db.SheduleTable.Select(n => n).ToList(); bettingList = db.BettingsTable.Select(n => n).ToList(); string pos = ExternalObjectToReport.position.PositionName; string depart = ExternalObjectToReport.departament.DepartamentName; var pos_shedule_table = sheduleList .Join( db.PositionTable.Select(n => n), t => t.position.IdPosition, h => h.IdPosition, (t, h) => new { IdPosition = h.IdPosition, IdShedule = t.SheduleId, PositionName = t.position.PositionName, date = t.actionStartDate, count = t.countPerson, }).ToList(); var dep_shedule_table = sheduleList .Join( db.DepartamentTable.Select(n => n), sh => sh.departament.DepartId, d => d.DepartId, (sh, d) => new { IdShedule = sh.SheduleId, IdDepart = d.DepartId, DepartName = d.DepartamentName, }).ToList(); var unionTableSource = pos_shedule_table .Join( dep_shedule_table, p => p.IdShedule, d => d.IdShedule, (p, d) => new { IdShedule = p.IdShedule, IdDepart = d.IdDepart, DepartName = d.DepartName, Count = p.count, Date = p.date, PositionName = p.PositionName }).ToList(); //Все записи из 4-х таблиц var selectAllTables = unionTableSource .Join( db.BettingsTable.Select(n => n), t => t.PositionName, h => h.position, (t, h) => new { IdShedule = t.IdShedule, IdDepart = t.IdDepart, DepartName = t.DepartName, Count = t.Count, Date = t.Date, PositionName = t.PositionName, Price = h.Salary, FOT = t.Count * h.Salary }).Distinct(); var getCurrentItemDataBase = selectAllTables.Where(n => n.PositionName == ExternalObjectToReport.position.PositionName && n.DepartName == ExternalObjectToReport.departament.DepartamentName).FirstOrDefault(); foreach (var curentReportItem in selectAllTables) { if (curentReportItem.DepartName == ExternalObjectToReport.departament.DepartamentName) { if (ExternalObjectToReport.SheduleId == curentReportItem.IdShedule) { var dataToReportTable = new Report() { reportId = 1, departament = new Departament { DepartId = 1, DepartamentName = ExternalObjectToReport.departament.DepartamentName }, actionStartDate = ExternalObjectToReport.actionStartDate, actionEndDate = DateTime.Parse(ExternalObjectToReport.actionStartDate. Value.Date.ToString()).AddDays(30), FOTdepartament = getCurrentItemDataBase.FOT.Value }; db.ReportTable.Add(dataToReportTable); db.SaveChanges(); break; } } } } } }
//Инициализируем тестовые записи по умолчанию в БД private void InitTables() { using (db = new DataContextProvider()) { var p = new Position { IdPosition = 1, PositionName = "Инженер-конструктор" }; var p2 = new Position { IdPosition = 2, PositionName = "Инженер-технолог" }; db.PositionTable.Add(p); db.PositionTable.Add(p2); db.SaveChanges(); var dep = new Departament { DepartId = 1, DepartamentName = "КБ" }; var dep2 = new Departament { DepartId = 2, DepartamentName = "Технологический отдел" }; db.DepartamentTable.Add(dep); db.DepartamentTable.Add(dep2); db.SaveChanges(); db.BettingsTable.Add(new Bettings { BettingId = 1, position = "Инженер-конструктор", actionStartDate = DateTime.Parse("01/01/2015"), Salary = 10000 }); db.BettingsTable.Add(new Bettings { BettingId = 2, position = "Инженер-технолог", actionStartDate = DateTime.Parse("01/06/2015"), Salary = 15000 }); db.SaveChanges(); db.SheduleTable.Add(new Shedule { SheduleId = 1, departament = dep, position = p, actionStartDate = DateTime.Parse("01/01/2015"), countPerson = 5 }); db.SheduleTable.Add(new Shedule { SheduleId = 2, departament = dep, position = p, actionStartDate = DateTime.Parse("01/06/2015"), countPerson = 10 }); db.SaveChanges(); var templeteDate = db.BettingsTable.Select(n => n).ToList(); var date = DateTime.Parse("1.6.2015"); var res = db.SheduleTable.Select(n => n). Join( db.BettingsTable.Select(n => n), t => t.SheduleId, h => h.BettingId, (t, h) => new { departament = t.departament, countPerson = t.countPerson, Salary = h.Salary, StartDate = t.actionStartDate, EndDate = date, Result = t.countPerson * h.Salary }).ToList(); db.ReportTable.Add(new Report { departament = res[0].departament, actionStartDate = res[0].StartDate, actionEndDate = res[0].EndDate, FOTdepartament = res[0].Result.Value }); db.SaveChanges(); } }