/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <param name="search"></param>
/// <returns></returns>
public static IQueryable <WorkOrder> filterDateTimeOfWork(IQueryable <WorkOrder> query, string search)
{
//Using DateTime.TryParse as determiner of date/string
DateTime parsedTime;
if (DateTime.TryParse(search, out parsedTime))
{
if (isMonthSpecific.IsMatch(search)) //Regex for month/year
{
return(query.Where(p => SqlServerDbFunctionsExtensions
.DateDiffMonth(null, p.dateTimeofWork, parsedTime) == 0 ? true : false));
}
if (isDaySpecific.IsMatch(search)) //Regex for day/month/year
{
return(query.Where(p => SqlServerDbFunctionsExtensions
.DateDiffDay(null, p.dateTimeofWork, parsedTime) == 0 ? true : false));
}
if (isTimeSpecific.IsMatch(search)) //Regex for day/month/year time
{
return(query.Where(p => SqlServerDbFunctionsExtensions
.DateDiffHour(null, p.dateTimeofWork, parsedTime) == 0 ? true : false));
}
}
return(query);
}
public override ICollection <SpiderProxyUriEntity> SelectProxyEntities(ISpiderProxyUriEntityOption entityOption, int recentDays = 10, int count = 0)
{
if (recentDays < 1)
{
recentDays = 360;
}
IQueryable <SpiderProxyUriEntity> query;
if (entityOption is null)
{
query = ProxyUriEntities.AsNoTracking()
.Where(p => SqlServerDbFunctionsExtensions.DateDiffDay(EF.Functions, p.UpdateTime, DateTime.UtcNow) <= recentDays)
.OrderByDescending(e => e.UpdateTime);
}
else
{
var queryableData = ProxyUriEntities.AsNoTracking().AsQueryable();
var expression = entityOption.GetExpressionTree(queryableData);
if (expression is null)
{
query = ProxyUriEntities
.Where(p => SqlServerDbFunctionsExtensions.DateDiffDay(EF.Functions, p.UpdateTime, DateTime.UtcNow) <= recentDays)
.OrderByDescending(e => e.UpdateTime);
}
else
{
query = queryableData.Provider
.CreateQuery <SpiderProxyUriEntity>(expression)
.Where(p => SqlServerDbFunctionsExtensions.DateDiffDay(EF.Functions, p.UpdateTime, DateTime.UtcNow) <= recentDays)
.OrderByDescending(e => e.UpdateTime);
}
}
return((count > 0 ? query.Take(count) : query).ToArray());
}
public static void search(viewOptions o, ref IQueryable <WorkOrder> q)
{
bool isDateTime = false;
DateTime parsedTime;
if (isDateTime = DateTime.TryParse(o.sSearch, out parsedTime))
{
if (isMonthSpecific.IsMatch(o.sSearch)) //Regex for month/year
{
q = q.Where(p => SqlServerDbFunctionsExtensions
.DateDiffMonth(null, p.dateTimeofWork, parsedTime) == 0 ? true : false);
}
if (isDaySpecific.IsMatch(o.sSearch)) //Regex for day/month/year
{
q = q.Where(p => SqlServerDbFunctionsExtensions
.DateDiffDay(null, p.dateTimeofWork, parsedTime) == 0 ? true : false);
}
if (isTimeSpecific.IsMatch(o.sSearch)) //Regex for day/month/year time
{
q = q.Where(p => SqlServerDbFunctionsExtensions
.DateDiffHour(null, p.dateTimeofWork, parsedTime) == 0 ? true : false);
}
}
else
{
q = q
.Where(p => p.ID.ToString().Contains(o.sSearch) ||
p.paperOrderNum.ToString().Contains(o.sSearch) ||
p.contactName.Contains(o.sSearch) ||
p.workSiteAddress1.Contains(o.sSearch) ||
p.updatedby.Contains(o.sSearch));
}
}
public Task Execute(IJobExecutionContext context)
{
DbFunctions dfunc = null;
var users = dbContext.Users
.Include(x => x.Token)
.Where(x => SqlServerDbFunctionsExtensions.DateDiffDay(dfunc, x.Token.CreateAt, DateTime.Now) > 1)
.ToList();
if (users.Any())
{
var transation = dbContext.Database.BeginTransaction();
try
{
foreach (var user in users)
{
if (!user.IsEmailConfirmed)
{
dbContext.Users.Remove(user);
}
}
dbContext.SaveChanges();
transation.Commit();
}
catch
{
transation.Rollback();
}
finally
{
transation.Dispose();
dbContext.Dispose();
}
}
return(Task.FromResult(0));
}
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 override ICollection <SpiderProxyUriEntity> SelectProxyEntities(Predicate <SpiderProxyUriEntity> predicate, int recentDays = 10, int count = 0)
{
if (recentDays < 1)
{
recentDays = 360;
}
Expression <Func <SpiderProxyUriEntity, bool> > filter = predicate != null
? (p => SqlServerDbFunctionsExtensions.DateDiffDay(EF.Functions, p.UpdateTime, DateTime.UtcNow) <= recentDays && predicate(p))
: (Expression <Func <SpiderProxyUriEntity, bool> >)(p => SqlServerDbFunctionsExtensions.DateDiffDay(EF.Functions, p.UpdateTime, DateTime.UtcNow) <= recentDays);
var query = ProxyUriEntities
.Where(filter)
.OrderByDescending(e => e.UpdateTime);
return((count > 0 ? query.Take(count) : query).ToArray());
}
public void Integration_Queryable_test()
{
// Arrange - load test records
var worker = frb.AddWorker();
var signin = frb.AddWorkerSignin(worker).ToWorkerSignin();
// Act
var q = frb.ToFactory().WorkerSignins.AsQueryable();
q = q.Where(r => r.dwccardnum == signin.dwccardnum &&
SqlServerDbFunctionsExtensions
.DateDiffDay(null, r.dateforsignin, signin.dateforsignin) == 0);
WorkerSignin result = q.FirstOrDefault();
// Assert
Assert.IsNotNull(result.ID);
Assert.AreEqual(result.WorkerID, worker.ID);
Assert.AreEqual(result.dwccardnum, worker.dwccardnum);
}
/// <summary>
/// Filter WA queryable on a partial date string
/// </summary>
/// <param name="search">string that is part of a date</param>
/// <param name="parsedTime">datetime.parse of the same string</param>
/// <param name="query">WorkAssignment queryable</param>
public static void filterOnDatePart(string search, DateTime parsedTime, ref IQueryable <WorkAssignment> query)
{
if (isMonthSpecific.IsMatch(search)) //Regex for month/year
{
query = query.Where(p => SqlServerDbFunctionsExtensions
.DateDiffMonth(null, p.workOrder.dateTimeofWork, parsedTime) == 0 ? true : false);
}
if (isDaySpecific.IsMatch(search)) //Regex for day/month/year
{
query = query.Where(p => SqlServerDbFunctionsExtensions
.DateDiffDay(null, p.workOrder.dateTimeofWork, parsedTime) == 0 ? true : false);
}
if (isTimeSpecific.IsMatch(search)) //Regex for day/month/year time
{
query = query.Where(p => SqlServerDbFunctionsExtensions
.DateDiffHour(null, p.workOrder.dateTimeofWork, parsedTime) == 0 ? true : false);
}
//throw new ArgumentException("Date string not valid for Month,Day, or Hour pattern");
}
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));
}
/// <summary>
/// This method creates a worker signin entry. Must be implemented with try/catch.
/// </summary>
/// <param name="signin"></param>
/// <param name="user"></param>
public virtual WorkerSignin CreateSignin(int dwccardnum, DateTime dateforsignin, string user)
{
//Search for worker with matching card number
var worker = db.Workers.FirstOrDefault(d => d.dwccardnum == dwccardnum);
if (worker == null)
{
throw new NullReferenceException("Card ID doesn't match a worker!");
}
var workerSignins = GetAll();
var clientDate = dateforsignin.DateBasedOn(ClientTimeZoneInfo); // 12:00:00 AM client time
var endOfClientDay = clientDate.AddDays(1).AddMilliseconds(-1);
var foo = workerSignins.Select(the => new { the.ID, the.dwccardnum, the.dateforsignin });
var qID = foo.FirstOrDefault(the =>
the.dwccardnum == dwccardnum &&
SqlServerDbFunctionsExtensions.DateDiffDay(null,
the.dateforsignin,
dateforsignin) == 0
// TODO use DateTimeOffset with DbFunctions
// && the.dateforsignin.DateTimeFrom(ClientTimeZoneInfo) >= clientDate
// && the.dateforsignin.DateTimeFrom(ClientTimeZoneInfo) <= endOfClientDay
)?.ID;
if (qID != null)
{
return(workerSignins.FirstOrDefault(wsi => wsi.ID == qID));
}
var signin = new WorkerSignin();
signin.WorkerID = worker.ID;
signin.dwccardnum = dwccardnum;
signin.dateforsignin = dateforsignin; // the client has spoken, we have universalized, let it in!
signin.memberStatusID = worker.memberStatusID;
signin.lottery_sequence =
workerSignins.Where(p => p.dateforsignin.Date == dateforsignin.Date).Count() + 1;
return(Create(signin, user));
}
public List <Quote> GetQuotes(bool archived, string customerEmail, DateTime dateFrom, DateTime dateTo)
{
Expression <Func <Quote, bool> > expression = quote => true;
expression = expression.AndAlso(q => q.IsArchived == archived);
DbFunctions dfunc = null;
if (dateFrom != new DateTime())
{
expression = expression.AndAlso(q => SqlServerDbFunctionsExtensions.DateDiffDay(dfunc, dateFrom, q.DateCreated) >= 0);
}
if (dateTo != new DateTime())
{
expression = expression.AndAlso(q => SqlServerDbFunctionsExtensions.DateDiffDay(dfunc, q.DateCreated, dateTo) >= 0);
}
if (!string.IsNullOrWhiteSpace(customerEmail))
{
expression = expression.AndAlso(q => q.CustomerEmail.Contains(customerEmail));
}
var quotes = _context.Quotes
.Include(q => q.QuoteElements)
.ThenInclude(qe => qe.Element)
.OrderByDescending(q => q.DateCreated)
.Where(expression)
.ToList();
foreach (var quote in quotes)
{
foreach (var quoteElement in quote.QuoteElements)
{
quoteElement.Quote = null;
quoteElement.Element.QuoteElements = null;
}
}
return(quotes);
}
public static UserSettingsViewModel ToUserSettingsViewModel(this MacheteUser user, bool isHirer)
{
if (user == null)
{
throw new ArgumentNullException(nameof(user));
}
var userSettingsViewModel = new UserSettingsViewModel
{
ProviderUserKey = user.Id,
UserName = user.UserName,
Email = user.Email,
IsApproved = user.IsApproved ? "Yes" : "No",
IsLockedOut = user.IsLockedOut ? "Yes" : "No",
IsOnline = SqlServerDbFunctionsExtensions
.DateDiffHour(null, user.LastLoginDate, DateTime.Now) < 1 ? "Yes" : "No",
CreationDate = user.CreateDate,
LastLoginDate = user.LastLoginDate,
IsHirer = isHirer
};
return(userSettingsViewModel);
}
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 static void search(viewOptions o, TimeZoneInfo clientTimeZoneInfo, ref IQueryable <WorkOrder> q)
{
bool isDateTime = false;
DateTime parsedTime;
if (isDateTime = DateTime.TryParse(o.sSearch, out parsedTime))
{
var searchDateStartUtc = parsedTime.ToUtcFrom(clientTimeZoneInfo); // some date at midnight time + {offset} hours
var searchDateEndUtc = searchDateStartUtc.AddHours(24); // UTC end search dateTime
if (isMonthSpecific.IsMatch(o.sSearch)) //Regex for month/year
{
q = q.Where(p => SqlServerDbFunctionsExtensions
.DateDiffMonth(null, p.dateTimeofWork, searchDateStartUtc) == 0 ? true : false);
}
if (isDaySpecific.IsMatch(o.sSearch)) //Regex for day/month/year
{
q = q.Where(p =>
p.dateTimeofWork >= searchDateStartUtc &&
p.dateTimeofWork < searchDateEndUtc);
}
if (isTimeSpecific.IsMatch(o.sSearch)) //Regex for day/month/year time
{
q = q.Where(p => SqlServerDbFunctionsExtensions
.DateDiffHour(null, p.dateTimeofWork, searchDateStartUtc) == 0 ? true : false);
}
}
else
{
q = q
.Where(p => p.ID.ToString().Contains(o.sSearch) ||
p.paperOrderNum.ToString().Contains(o.sSearch) ||
p.contactName.Contains(o.sSearch) ||
p.workSiteAddress1.Contains(o.sSearch) ||
p.updatedby.Contains(o.sSearch));
}
}
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);
}
}
}
