//Метод внесения данных в таблицу Ставки
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 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();
}
}