public ActionResult ABC(string id)
{
QUANGHANHABCEntities DBContext = new QUANGHANHABCEntities();
//NK su co
var years = DBContext.Database.SqlQuery <int>("SELECT distinct year(i.start_time) as years FROM Incident i inner join Equipment e on e.equipmentId = i.equipmentId inner join Department d on d.department_id = i.department_id where i.end_time is not null and e.equipmentId = @equipmentId order by years desc",
new SqlParameter("equipmentId", id)).ToList();
List <IncidentByYear> listbyyear = new List <IncidentByYear>();
foreach (int year in years)
{
int count = 0;
IncidentByYear tempyear = new IncidentByYear();
List <IncidentByDate> listbydate = new List <IncidentByDate>();
var dates = DBContext.Database.SqlQuery <DateTime>("SELECT distinct i.start_time as dates FROM Incident i inner join Equipment e on e.equipmentId = i.equipmentId inner join Department d on d.department_id = i.department_id where i.end_time is not null and e.equipmentId = @equipmentId and year(start_time) = @year order by dates desc",
new SqlParameter("equipmentId", id),
new SqlParameter("year", year)).ToList();
foreach (DateTime date in dates)
{
IncidentByDate tempdate = new IncidentByDate();
tempdate.date = date;
tempdate.incidents = DBContext.Database.SqlQuery <IncidentDB>("SELECT d.department_name, " +
"i.* FROM Incident i inner join Equipment e on e.equipmentId = i.equipmentId inner join Department d " +
"on d.department_id = i.department_id where i.end_time is not null and e.equipmentId = @equipmentId and start_time = @start_time and year(start_time) = @year order by start_time desc",
new SqlParameter("equipmentId", id),
new SqlParameter("start_time", date),
new SqlParameter("year", year)).ToList();
count += tempdate.incidents.Count;
listbydate.Add(tempdate);
}
tempyear.year = year;
tempyear.IncidentByDates = listbydate;
tempyear.count = count;
listbyyear.Add(tempyear);
}
ViewBag.incidents = listbyyear;
var equipment = DBContext.Database.SqlQuery <Equipment>("SELECT * FROM Equipment WHERE Equipment.equipmentId = @equipmentId",
new SqlParameter("equipmentId", id)).First();
ViewBag.equipment = equipment;
//NK dieu dong
var yearDD = DBContext.Database.SqlQuery <int>("SELECT distinct year(d.date_created) as years FROM Documentary d, Documentary_moveline_details dm, Equipment e where e.equipmentId = @equipmentId and e.equipmentId = dm.equipmentId and dm.documentary_id = d.documentary_id order by years desc",
new SqlParameter("equipmentId", id)).ToList <int>();
List <moveLineByYear> listDD = new List <moveLineByYear>();
foreach (int year in yearDD)
{
List <myMoveline> listMML = DBContext.Database.SqlQuery <myMoveline>("select dm.*, d.* from Equipment e, Documentary_moveline_details dm, Documentary d where e.equipmentId = dm.equipmentId and d.documentary_id = dm.documentary_id and dm.equipmentId = @equipmentId and YEAR(d.date_created) = @year ",
new SqlParameter("equipmentId", id),
new SqlParameter("year", year)).ToList();
moveLineByYear MLY = new moveLineByYear();
foreach (var x in listMML)
{
string s = toStringDate(x.date_created);
x.date = s;
}
MLY.listmoveline = listMML;
MLY.year = year;
MLY.count = listMML.Count();
listDD.Add(MLY);
}
ViewBag.listDD = listDD;
//NK sua chua
return(View("/Views/CDVT/Thietbi/Lylich.cshtml"));
}