public void Calculator(int ma)
{
DbFunctions dbF = null;
DateTime date = DateTime.Now;
var cal = (from d in _db.TblDailyDetail
join j in _db.TblJob on d.JobId equals j.Id
where d.DailyId == ma
group d by new { d.JobId, j.TenJob } into g
select new
{
JobId = g.Key.JobId,
Ten_Job = g.Key.TenJob,
Sum = (g.Sum(m => SqlServerDbFunctionsExtensions.DateDiffMinute(dbF, date.Add(m.FormTime), date.Add(m.ToTime)))) * 100 / 480
}).ToList();
string Total_Job = "";
if (cal.Count > 0)
{
foreach (var item in cal)
{
Total_Job = Total_Job + "- " + item.Ten_Job + "(" + item.Sum + "%)</br>";
}
}
if (!string.IsNullOrWhiteSpace(Total_Job))
{
var model = _db.TblDaily.Find(ma);
model.TotalJob = Total_Job;
}
;
_db.SaveChanges();
}
public async Task <ICustomList <TUser> > GetOnlineUsers(int sessionMinutes, int pageIndex, int pageSize, DateTime?DateTimeNowValue = null)
{
var query = (from u in _dbContext.Set <TUser>().AsNoTracking()
where u.Status == Status.Active &&
SqlServerDbFunctionsExtensions.DateDiffMinute(EF.Functions, u.LastSuccessfullLogin, DateTimeNowValue ?? DateTime.Now) < sessionMinutes &&
u.LastSuccessfullLogin > u.LastLogOutDate
select u);
return(await query.GetCustomListFromQueryAsync(new Paging(pageIndex, pageSize)));
}
public async Task <IActionResult> GetValues()
{
DbFunctions dfunc = null;
DateTime dt1 = new DateTime(2018, 11, 09, 18, 00, 00);
DateTime dt2 = new DateTime(2018, 11, 09, 19, 00, 00);
var values = await(from ca in _context.CameraActions
join cl in _context.ClientsLog on ca.Session equals cl.Session
where ca.StartTime.AddHours(Math.Round(SqlServerDbFunctionsExtensions.DateDiffMinute(dfunc, ca.StartTime, ca.EndTime) / 60.1, 0)) >= dt1 && ca.StartTime.AddHours(Math.Round(SqlServerDbFunctionsExtensions.DateDiffMinute(dfunc, ca.StartTime, ca.EndTime) / 60.1, 0)) <= dt2
select new { cl.Username, ca.Action } into x
group x by new { x.Username } into g
select new
{
Username = g.Key.Username,
AlarmsReceived = g.Sum(x => x.Action == 4 ? 1 : 0),
AlarmsProcessed = g.Sum(x => x.Action == 3 ? 1 : 0)
}).ToListAsync();
return(Ok(values));
}
public IQueryable <ViewReportDiaryModel> GetData(DateTime dateFrom, DateTime dateTo)
{
DbFunctions dbF = null;
DateTime date = DateTime.Now;
var data =
from p in _db.TblBp
join u in _db.AppUser on p.MaBp equals u.MaBp
join t in _db.TblTo on u.MaTo equals t.MaTo into gt
from subt in gt.DefaultIfEmpty()
join gd in (from d in _db.TblDaily
where d.Date >= dateFrom && d.Date <= dateTo
select d
) on u.UserName equals gd.UserName
into gu from subd in gu.DefaultIfEmpty()
join gdd in (from dd in _db.TblDailyDetail
group dd by dd.DailyId into g
select new
{
DailyId = g.Key,
Total_Work = (g.Sum(m => SqlServerDbFunctionsExtensions.DateDiffMinute(dbF, date.Add(m.FormTime), date.Add(m.ToTime)))),
}) on subd.Id equals gdd.DailyId into gud
from subdd in gud.DefaultIfEmpty()
where u.MaBp != "BGD"
select new ViewReportDiaryModel
{
UserName = u.UserName,
FullName = u.FullName,
Ten_phong = p.TenBp,
Ten_to = subt.TenTo,
Total_Work = subdd.Total_Work > 0 ? subdd.Total_Work : 0,
Total_Date = subd.Id > 0 ? subd.Id : 0
};
return(data);
}
public async Task <PageListUtility <ChartReason> > GetDataChart(string factory, string building, string machine, string machine_type, string shift, string date, int page = 1)
{
if (factory.Trim() == "ALL")
{
return(null);
}
else
{
var machineFirst = new M_ActionTimeForOEE();
var dataAll = await _repoDownTimeReson.GetAll()
.Where(x => x.shift_date == Convert.ToDateTime(date))
.ToListAsync();
if (shift.Trim() != "0")
{
dataAll = dataAll.Where(x => x.shift_id.ToString() == shift.ToString()).ToList();
}
if (factory != "ALL")
{
dataAll = dataAll.Where(x => x.factory_id.Trim() == factory.Trim()).ToList();
machineFirst = dataAll.FirstOrDefault();
}
if (building != "ALL")
{
dataAll = dataAll.Where(x => x.factory_id.Trim() == factory.Trim() &&
x.building_id.Trim() == building.Trim()).ToList();
machineFirst = dataAll.FirstOrDefault();
}
if (machine_type != "ALL")
{
var machines = await _serverCommon.ListMachineID(factory, building, machine_type);
dataAll = dataAll.Where(x => x.factory_id.Trim() == factory.Trim() &&
x.building_id.Trim() == building.Trim() &&
machines.Contains(x.machine_id.Trim())).ToList();
machineFirst = dataAll.Where(x => x.factory_id.Trim() == factory.Trim() &&
x.building_id.Trim() == building.Trim() &&
machines.Contains(x.machine_id.Trim())).FirstOrDefault();
}
if (machine != "ALL")
{
dataAll = dataAll.Where(x => x.factory_id.Trim() == factory.Trim() &&
x.building_id.Trim() == building.Trim() &&
x.machine_id.Trim() == machine.Trim()).ToList();
machineFirst = dataAll.FirstOrDefault();
}
if (dataAll.Count() != 0)
{
DbFunctions dfunc = null;
DateTime day = Convert.ToDateTime(date);
var machineName = machineFirst.machine_id;
var data = dataAll.Where(x => x.machine_id.Trim() == machineFirst.machine_id.Trim()).Select(x => new ChartReason()
{
id = x.id,
title = x.is_work_time == true ? "RUN" : "IDLE",
start_time = x.start_time,
end_time = x.end_time,
diffTime = SqlServerDbFunctionsExtensions.DateDiffMinute(dfunc, x.start_time, x.end_time),
factory_id = x.factory_id,
machine_id = x.machine_id,
building_id = x.building_id,
shift_id = x.shift_id.ToString(),
shift_date = x.shift_date,
isEdit = false,
}).OrderByDescending(x => x.title).ToList();
var dataTable = data.Where(x => x.title == "IDLE").ToList();
return(await PageListUtility <ChartReason> .PageListAsyncChartReason(data, dataTable, machineName, page));
}
else
{
return(null);
}
}
}
