public static void CoursesActiveOnAGivenDate()
{
Console.WriteLine("All courses active on the current date: ");
var db = new SchoolSystemContext();
using (db)
{
var courses = db.Courses
.OrderByDescending(c => EntityFunctions.DiffDays(c.StartDate, c.EndDate))
.ThenByDescending(c => c.Students.Count())
.Select(c => new
{
c.Name,
c.StartDate,
c.EndDate,
Duration = EntityFunctions.DiffDays(c.StartDate, c.EndDate),
StudentsCount = c.Students.Count()
});
foreach (var course in courses)
{
Console.WriteLine("Course Name: {0}, Start Date: {1:dd.MM.yyyy}, End Date: {2:dd.MM.yyyy}, Duration: {3} days, Students Count: {4}",
course.Name,
course.StartDate,
course.EndDate,
course.Duration,
course.StudentsCount);
}
}
}
public IQueryable <VW_PRIORIZACHAMADO> GetWhere(string paramNumChamado, string paramSiglaArea, string paramStatus, int paramIdadeStatus, string paramLoginAnalista)
{
IQueryable <VW_PRIORIZACHAMADO> query;
decimal dCHAMADO = 0;
decimal.TryParse(paramNumChamado, out dCHAMADO);
decimal dUSUARIO = 0;
decimal.TryParse(paramLoginAnalista, out dUSUARIO);
query = from c in m_DbContext.VW_PRIORIZACHAMADO
//join d in m_DbContext.FUN_TBL_USUARIO on c.ID_USUARIO equals d.ID_USUARIO
where (c.CHAMADO == dCHAMADO || dCHAMADO == 0) &&
(c.STATUS == paramStatus || paramStatus == "0") &&
(c.AREA == paramSiglaArea || paramSiglaArea == "0") &&
(c.ID_USUARIO == dUSUARIO || dUSUARIO == 0)
//&& (DateTime.Now.Subtract(c.DT_STATUS.Value).Days > paramIdadeStatus || paramIdadeStatus == 0)
&& (EntityFunctions.DiffDays(c.DT_STATUS.Value, DateTime.Now) > paramIdadeStatus || paramIdadeStatus == 0)
select c;
//else
//{
// query = from c in m_DbContext.TB_PRIORIZACHAMADO
// select c;
//}
return(query);
}
public List <T_FD_AccountReceivable> Select(string customername, string projectNo, string paymentstate, string date)
{
using (Entities db = new Entities())
{
string fitformatc = string.Format("%{0}%", customername.Trim());
string fitformat = string.Format("%{0}%", projectNo.Trim());
string fitformats = string.Format("%{0}%", paymentstate.Trim());
DateTime dt = new DateTime();
if (DateTime.TryParse(date, out dt))
{
return(db.T_FD_AccountReceivable.
Where(l =>
SqlFunctions.PatIndex(fitformat, l.ProjectID) > 0 &&
SqlFunctions.PatIndex(fitformatc, l.CustomerName) > 0 &&
SqlFunctions.PatIndex(fitformats, l.PaymentState) > 0 &&
EntityFunctions.DiffDays(l.CreateDate, dt) >= 0)
.ToList());
}
return(db.T_FD_AccountReceivable.
Where(l => SqlFunctions.PatIndex(fitformat, l.ProjectID) > 0 &&
SqlFunctions.PatIndex(fitformats, l.PaymentState) > 0 &&
SqlFunctions.PatIndex(fitformatc, l.CustomerName) > 0)
.ToList());
}
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
var mr1 = new MovieRental {
Title = "A Day in the Life", RentalDate = DateTime.Parse("2/19/2010"), ReturnedDate = DateTime.Parse("3/4/2010"), LateFees = 3M
};
var mr2 = new MovieRental {
Title = "The Shortest Yard", RentalDate = DateTime.Parse("3/15/2010"), ReturnedDate = DateTime.Parse("3/20/2010"), LateFees = 0M
};
var mr3 = new MovieRental {
Title = "Jim's Story", RentalDate = DateTime.Parse("3/2/2010"), ReturnedDate = DateTime.Parse("3/19/2010"), LateFees = 3M
};
context.MovieRentals.AddObject(mr1);
context.MovieRentals.AddObject(mr2);
context.MovieRentals.AddObject(mr3);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine("Movie rentals late returns");
Console.WriteLine("==========================");
var late = from r in context.MovieRentals
where EntityFunctions.DiffDays(r.RentalDate, r.ReturnedDate) > 10
select r;
foreach (var rental in late)
{
Console.WriteLine("{0} was {1} days late, fee: {2}", rental.Title, (rental.ReturnedDate - rental.RentalDate).Days - 10, rental.LateFees.ToString("C"));
}
}
Console.WriteLine("Press <enter> to continue...");
Console.ReadLine();
}
public void Execute(IJobExecutionContext context)
{
QuanLyThuVienEntities data = new QuanLyThuVienEntities();
DateTime currentDate = DateTime.Now;
var dsNhacNho = (from ct in data.ChiTietPhieuMuons
from pm in data.PhieuMuons
from s in data.Saches
from nd in data.NguoiDungs
where pm.NguoiMuon == nd.MaNguoiDung && ct.MaSach == s.MaSach && ct.MaPhieuMuon == pm.MaPhieuMuon && EntityFunctions.DiffDays(ct.NgayMuon, currentDate) <= 14 && EntityFunctions.DiffDays(ct.NgayMuon, currentDate) >= 11
select new RemindMail()
{
MaPhieuMuon = ct.MaPhieuMuon,
MaChiTiet = ct.MaChiTietPhieuMuon,
MaNguoiDung = nd.MaNguoiDung,
HoTen = nd.HoTen,
Email = nd.Email,
NgayMuon = ct.NgayMuon,
SoNgayToiHan = 14 - EntityFunctions.DiffDays(ct.NgayMuon, DateTime.Now),
TenSach = s.TenSach
});
var dsNguoidung = (from x in dsNhacNho select x.MaNguoiDung).Distinct().ToList();
foreach (var MaNguoiDung in dsNguoidung)
{
List <String> tenSach = new List <String>();
List <DateTime?> ngayMuon = new List <DateTime?>();
int?[] soluong = new int?[5];
int i = 0;
string emailDocGia = "";
string tenDocGia = "";
int?[] maPhieuMuon = new int?[5];
var ds = (from x in dsNhacNho where x.MaNguoiDung == MaNguoiDung select x).ToList();
foreach (var a in ds)
{
tenSach.Add(a.TenSach);
emailDocGia = a.Email;
tenDocGia = a.HoTen;
soluong[i] = a.SoNgayToiHan;
maPhieuMuon[i] = a.MaPhieuMuon;
ngayMuon.Add(a.NgayMuon);
i = i + 1;
}
QuanLyThuVien_N8.Models.EmailService mailService = new EmailService();
string smtpUserName = "******";
string smtpPassword = "******";
string smtpHost = "smtp.gmail.com";
int smtpPort = 25;
string emailTo = emailDocGia;
string subject = "[Thư viện KHTN] - Nhắc nhở hạn trả sách - " + tenDocGia;
string body = string.Format("Xin chào độc giả {0} !<br><br>Thư viện trường ĐH KHTN xin gửi đến bạn danh sách các ấn phẩm bạn đã mượn sắp đến hạn trả: <br><br>", tenDocGia);
for (int j = 0; j < i; j++)
{
body += string.Format("{0}. Cuốn sách: <b>{1}</b> mượn ngày: <b>{2}</b> thuộc phiếu mượn có mã: <b>{3}</b> còn <b>{4} ngày</b> sẽ đến hạn trả.<br>", j + 1, tenSach[j], ngayMuon[j], maPhieuMuon[j], soluong[j]);
}
body = body + "<br>Mong bạn sắp xếp thời gian để thực hiện trả sách đúng thời hạn. Đây là email gửi tự động, vui lòng không trả lời!";
bool kq = mailService.Send(smtpUserName, smtpPassword, smtpHost, smtpPort, emailTo, subject, body);
}
}
private void button39_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
Dump(from o in ctx.Orders.Include("OrderDetails")
where EntityFunctions.DiffDays(o.OrderDate, o.ActualShippingDate) > 5
select o);
}
}
public async Task <List <AppointmentDetail> > GetAppointmentIncludeServiceAndOutletForCustomer(int idcustomer, int dayPassed)
{
var query = DbSet.Where(x => (EntityFunctions.DiffDays(DateTime.Today, x.Date)) <= dayPassed &&
x.Appointment1.Customer == idcustomer)
.Include(x => x.Service1).Include(x => x.Bed1).Include(x => x.Bed1.Room1).Include(x => x.Bed1.Room1.Outlet1)
.Include(x => x.Appointment1);
return(await query.ToListAsync());
}
public override void Execute(IJobExecutionContext context)
{
// Grab a list of users who have not signed up after their trial expiry for more than 5 days
List <IUser> lostUsers = (from user in _database.GetUsers
where user.TrialExpiryDate < DateTime.UtcNow &&
!user.SubscriptionId.HasValue &&
!user.SubscriptionExpiryDate.HasValue &&
!user.SentFeedbackRequest &&
EntityFunctions.DiffDays(user.TrialExpiryDate, DateTime.UtcNow) > 5
select user).ToList();
// Generate email body content
// TODO EmailResult emailContents = _accountEmailFactory.LostUserFeedback();
// Create email and send
IEmail feedbackEmail = _emailFactory.CreateEmail(
contents: this.GetEmailContents(),
subject: "Feedback Request",
sender: new EmailSender()
{
Name = "Stockwinners.com", EmailAddress = "*****@*****.**"
}, // Send from [email protected] so that the user can reply back
recipients: lostUsers);
feedbackEmail.Send();
// Mark that email has been sent to users
foreach (var user in lostUsers)
{
user.SentFeedbackRequest = true;
}
_database.SaveChanges();
// Notify admins
StringBuilder adminEmailContents = new StringBuilder();
adminEmailContents.Append("<html><head></head><body>Fellow admins, I just emailed ");
adminEmailContents.Append(lostUsers.Count);
adminEmailContents.Append(" users and asked them for feedback. These folks have chosen not to sign up after 5 days after their trial expiry date. Here is the list:<br/><br>");
foreach (var user in lostUsers)
{
adminEmailContents.Append(user.FirstName);
adminEmailContents.Append(" ");
adminEmailContents.Append(user.LastName);
adminEmailContents.Append(" ");
adminEmailContents.Append(user.EmailAddress);
adminEmailContents.Append("<br/>");
}
adminEmailContents.Append("<br/>Adios!</body></html>");
IEmail adminNotification = _emailFactory.CreateEmailForAdministrators(adminEmailContents.ToString(), "Feedback Request " + DateTime.UtcNow.ToShortDateString());
adminNotification.Send();
}
static void Main(string[] args)
{
using (var context = new Context())
{
var query = context.Foo;
var dates = query.Select(x => EntityFunctions.DiffDays(query.Min(y => y.Date), x.Date));
var result = dates.ToString();
Debugger.Break();
}
}
/// <summary>
/// Gets all pending delete exported files after a defined period.
/// </summary>
/// <returns>pending delete file requests.</returns>
public IEnumerable <Data.ExportFile> GetGeneratedOldExportFiles()
{
//check if a record already exisit
int completedStatusCodeId = Utils.GetCodeIdByCodeValue("ExportFileStatus", "COMPLETED");
int exportedFilesRemainingDays = int.Parse(Utils.GetSystemValue("ExportedFilesRemainingDays"));
//Get completed records that are older than the defined days in "ExportedFilesRemainingDays" as a systemvalue
return(DataContext.ExportFiles.Where(ef => ef.ExportFileStatusCodeId == completedStatusCodeId &&
ef.IsActive && EntityFunctions.DiffDays(EntityFunctions.AddDays(ef.CreatedDate, exportedFilesRemainingDays), Utils.Now) >= 0));
}
public void CanonicalFunction()
{
var products = from p in jambore.Product
where EntityFunctions.DiffDays(p.SellEndDate, p.SellStartDate) < 365
select p;
foreach (var k in products)
{
Console.WriteLine(k.ProductID);
}
Console.ReadKey();
}
public async Task <List <AppointmentDetail> > GetAppointmentIncludeServiceAndOutlet(int idoutlet, int idtherapist, int dayPassed, string nameCustomer)
{
var query = DbSet.Where(x => x.Bed1.Room1.Outlet == idoutlet &&
(EntityFunctions.DiffDays(DateTime.Today, x.Date)) <= dayPassed &&
x.Staff == idtherapist &&
x.Appointment1.Customer1.Name.Contains(nameCustomer))
.Include(x => x.Service1).Include(x => x.Bed1).Include(x => x.Bed1.Room1).Include(x => x.Bed1.Room1.Outlet1)
.Include(x => x.Appointment1)
.Include(x => x.Appointment1.Customer1);
return(await query.ToListAsync());
}
// GET: /OldCopies/Delete/5
public ActionResult Delete(int?id)
{
DateTime currentDate = DateTime.Now;
var data = db.DVDDetails.Where(d => EntityFunctions.DiffDays(d.DateAdded, currentDate) > 365);
foreach (var item in data)
{
db.DVDDetails.Remove(item);
}
db.SaveChanges();
ViewBag.Message = "Delete successful";
return(RedirectToAction("Index"));
}
/// <summary>
/// Gets the days difference.
/// </summary>
public void GetDaysDiff()
{
int exportedFilesRemainingDays = int.Parse(Utils.GetSystemValue("ExportedFilesRemainingDays"));
int completedStatusCodeId = Utils.GetCodeIdByCodeValue("ExportFileStatus", "COMPLETED");
var x = (from ef in DataContext.ExportFiles
where ef.ExportFileStatusCodeId == completedStatusCodeId && ef.IsActive
select new
{
cd = ef.CreatedDate,
ad = EntityFunctions.AddDays(ef.CreatedDate, exportedFilesRemainingDays),
x = (EntityFunctions.DiffDays(EntityFunctions.AddDays(ef.CreatedDate, exportedFilesRemainingDays), Utils.Now))
});
}
public ActionResult Index()
{
var liste = db.PERSONELs.Where(a => a.DOGUM_GUNU_TARIHI != null).
OrderBy(a => EntityFunctions.DiffDays(DateTime.Today, EntityFunctions.AddYears(a.DOGUM_GUNU_TARIHI, EntityFunctions.DiffYears(a.DOGUM_GUNU_TARIHI, DateTime.Today) +
((a.DOGUM_GUNU_TARIHI.Month < DateTime.Today.Month ||
(a.DOGUM_GUNU_TARIHI.Day <= DateTime.Today.Day && a.DOGUM_GUNU_TARIHI.Month == DateTime.Today.Month)) ? 1 : 0)))).Take(3).ToList();
ViewData["yuksek"] = db.EGITIMs.Where(s => s.OGRETIM_TIPI == "Yüksek Lisans").Count();
ViewData["lisans"] = db.EGITIMs.Where(s => s.OGRETIM_TIPI == "Lisans").Count();
ViewData["onlisans"] = db.EGITIMs.Where(s => s.OGRETIM_TIPI == "Ön Lisans").Count();
ViewData["duyuru"] = db.DUYURUs.ToList();
return(View(liste));
}
public ActionResult Index()
{
DateTime currentDate = DateTime.Now;
DateTime date = DateTime.Now.AddDays(45);
Console.WriteLine(currentDate);
System.Diagnostics.Debug.WriteLine(currentDate);
System.Diagnostics.Debug.WriteLine(date);
System.Diagnostics.Debug.WriteLine(date.Subtract(currentDate));
var data = db.DVDDetails.Where(d => EntityFunctions.DiffDays(d.DateAdded, currentDate) > 365).ToList();
return(View(data));
}
public override void Execute(IJobExecutionContext context)
{
// Get the list of users who have an active trial and have not logged in for more than 5 days
List <IUser> inactiveUsers = (from user in _database.GetUsers
where !user.SubscriptionId.HasValue && user.TrialExpiryDate > DateTime.UtcNow &&
EntityFunctions.DiffDays(user.LastLoginDate, DateTime.UtcNow) >= 5 &&
!user.SentInactiveReminder
select user).ToList();
// Generate email for inactive members
// TODO: EmailResult emailContents = _accountEmailFactory.InactiveTrialAccount();
IEmail inactiveUserEmail = _emailFactory.CreateEmail(
contents: this.GetEmailContents(),
subject: "Inactive Trial Account",
recipients: inactiveUsers);
// Send the email to users
inactiveUserEmail.Send();
// Mark the users so that they don't get duplicate notifications
foreach (var user in inactiveUsers)
{
user.SentInactiveReminder = true;
}
_database.SaveChanges();
// Notify dear admins
StringBuilder adminEmailContents = new StringBuilder();
adminEmailContents.Append("<html><head></head><body>Fellow admins, your server over here speaking. I just reminded ");
adminEmailContents.Append(inactiveUsers.Count);
adminEmailContents.Append(" users that they have been inactive for more than 5 days. Here is who I sent email to:<br/><br/>");
foreach (IUser user in inactiveUsers)
{
adminEmailContents.Append(user.FirstName);
adminEmailContents.Append(" ");
adminEmailContents.Append(user.LastName);
adminEmailContents.Append(" ");
adminEmailContents.Append(user.EmailAddress);
adminEmailContents.Append("<br/>");
}
adminEmailContents.Append("I'm going to go sleep for another day now.</body></html>");
IEmail adminNotificationEmail = _emailFactory.CreateEmailForAdministrators(adminEmailContents.ToString(), "Inactive Users Report " + DateTime.UtcNow.ToShortDateString());
adminNotificationEmail.Send();
}
public static void A()
{
//<snippet1>
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
var products = from p in AWEntities.Products
where EntityFunctions.DiffDays(p.SellEndDate, p.SellStartDate) < 365
select p;
foreach (var product in products)
{
Console.WriteLine(product.ProductID);
}
}
//</snippet1>
}
public List <Claim> TestRule(BusinessRule rule)
{
//List<BusinessRule> rules = null;
List <Claim> claims = null;
int lapsedTime = 0;
DateTime now = DateTime.Now;
//// get business rules for client/rule type id
//using (BusinessRuleManager repository = new BusinessRuleManager()) {
// rules = repository.GetBusinessRules(Globals.RuleType.ClaimAssingmentReview);
//}
XElement ruleXML = XElement.Parse(rule.RuleXML);
// get lapse time units: number of hours or days
int.TryParse(GetElementValue(ruleXML, "LapseTime"), out lapsedTime);
// get lapse time type: hours or days
string lapseTimeType = GetElementValue(ruleXML, "LapseTimeType");
Expression <Func <Claim, bool> > predicate = null;
// query filters
predicate = PredicateBuilder.True <CRM.Data.Entities.Claim>();
//predicate = predicate.And(x => x.LeadPolicy.Lead.ClientID == clientID); // claims for this client only
predicate = predicate.And(x => x.IsActive == true); // active claims
predicate = predicate.And(x => x.LastStatusUpdate != null);
predicate = predicate.And(x => x.ProgressStatusID == (int)Globals.ClaimProgressStatus.ClaimAssignedNotAcceptedYet);
// determine time lapsed
if (lapseTimeType == "1") // hours
{
predicate = predicate.And(x => EntityFunctions.DiffHours(x.LastProgressChanged, now) > lapsedTime);
}
else if (lapseTimeType == "2") // days
{
predicate = predicate.And(x => EntityFunctions.DiffDays(x.LastProgressChanged, now) > lapsedTime);
}
using (ClaimManager repository = new ClaimManager()) {
claims = repository.SearchClaim(predicate);
}
return(claims);
}
public void DateTimeDiffDays()
{
DateTime date = stored.AddDays(-1);
#if !EFOLD
var q = this.Entities
.Where(x =>
DbFunctions.DiffDays(date, x.DateTime) == 1);
#else
var q = this.Entities
.Where(x =>
EntityFunctions.DiffDays(date, x.DateTime) == 1);
#endif
q.Should().NotBeEmpty();
}
public List <V_HR_ExceptionHandleRecordsWithDetail> Select(string date, string name)
{
DateTime?keyDate = null;
DateTime dateTime;
if (DateTime.TryParse(date, out dateTime))
{
keyDate = dateTime;
}
using (Entities db = new Entities())
{
string fitformat = string.Format("%{0}%", name == null ? "" : name.Trim());
return(db.V_HR_ExceptionHandleRecordsWithDetail.Where(l => (EntityFunctions.DiffDays(keyDate, l.Date) ?? 0) == 0 &&
(SqlFunctions.PatIndex(fitformat, l.EmployeeName) > 0 || SqlFunctions.PatIndex(fitformat, l.EmployeeID) > 0)).ToList());
}
}
public List <V_HR_AttendTimeOriginalWithName> Select(string date, string name)
{
DateTime?keyDate = null;
DateTime dateTime;
if (DateTime.TryParse(date, out dateTime))
{
keyDate = dateTime;
}
using (Entities db = new Entities())
{
string fitformat = string.Format("%{0}%", name == null ? "" : name.Trim());
return(db.V_HR_AttendTimeOriginalWithName.Where(l => (EntityFunctions.DiffDays(keyDate, l.AttendTime) ?? 0) == 0 &&
(SqlFunctions.PatIndex(fitformat, l.Name) > 0 || SqlFunctions.PatIndex(fitformat, l.UserId) > 0)).ToList());
}
}
public void DateTimeOffsetDiffDays()
{
DateTimeOffset offset = stored.AddDays(-1);
#if !EFOLD
var q = this.Entities
.Where(x =>
DbFunctions.DiffDays(offset, x.Offset) == 1);
#else
var q = this.Entities
.Where(x =>
EntityFunctions.DiffDays(offset, x.Offset) == 1);
#endif
q.Should().NotBeEmpty();
}
private void CarregarGrid()
{
using (hotelEntities ef = new hotelEntities())
{
var listaReservas = ef.reserva
.Where(r => r.ativo == true &&
!(r.dt_entrada != null && r.dt_saida != null))
.Select(r => new
{
Cliente = r.cliente.ultimo_nome,
Quarto = r.quarto.numero,
Dias = EntityFunctions.DiffDays(r.dt_inicio, r.dt_termino)
})
.Take(10).ToList();
dgvUltimasReservas.DataSource = listaReservas;
}
}
// GET: api/Shift
public List <Shift> GetShiftTerminalWithCashier(int id, DateTime dateFrom, DateTime dateTo)
{
if (dateFrom != null)
{
var _dateFrom = dateFrom.Date;
var _dateTo = dateTo.Date;
}
var shiftToSend = new List <Shift>();
var terminals = db.Terminals.Include(t => t.Shifts).Where(t => t.BranchId == id).ToList();
var shifts = db.Shifts.Where(shift => EntityFunctions.DiffDays(shift.StartTime, dateFrom) == 0);
//var shifts = db.Shifts.Include(s => s.Terminal).Where(s=>s.StartTime.DayOfYear==dateFrom.DayOfYear&&s.StartTime.Year==dateFrom.Year).ToList();
var employees = db.Employees.Where(e => e.BranchId == id).ToList();
var empPersonIdHs = new HashSet <int>(employees.Select(i => i.PersonId));
foreach (var terminal in terminals)
{
var _shift = new Shift();
_shift = shifts.Where(s => s.TerminalId == terminal.TerminalId /*&& s.isClosed!=true*/).SingleOrDefault();
if (_shift != null)
{
_shift.Terminal = terminal;
if (_shift.PersonId != null)
{
if (empPersonIdHs.Contains((int)_shift.PersonId))
{
_shift.Cashier = employees.SingleOrDefault(e => e.PersonId == _shift.PersonId);
}
}
//terminals.Single(t => t.TerminalId.Equals(terminal.TerminalId)).Shifts.Add(_shift);
}
else
{
_shift = new Shift();
_shift.ShiftId = 0;
_shift.Terminal = terminal;
// terminals.Single(t => t.TerminalId.Equals(terminal.TerminalId)).Shifts.Add(new Shift());
}
shiftToSend.Add(_shift);
}
return(shiftToSend);
}
/// <summary>
/// /获取报警信息
/// </summary>
/// <returns></returns>
public IQueryable <dynamic> GetWarningInfo(WarningQueryParam warningQueryParam)
{
var firstTime = Convert.ToDateTime(warningQueryParam.FirstTime);
var lastTime = Convert.ToDateTime(warningQueryParam.LastTime);
//获取报警信息表实体
var warningInfo = DbSession.WarningInfoDal.GetEntities(w => w.IsDeleted == false);
var query = from t1 in warningInfo
from t2 in warningInfo.GroupBy(w => w.DeviceId).Select(p => new
{
newestSubTime = p.Max(q => q.SubTime),
warningStartTime = p.Min(q => q.SubTime),
deviceId = p.Key
})
where t1.DeviceId == t2.deviceId && t1.SubTime == t2.newestSubTime
select new
{
t1.DeviceId,
t1.WarningMessage,
t2.warningStartTime,
day = EntityFunctions.DiffDays(t2.warningStartTime, t2.newestSubTime),
hour = EntityFunctions.DiffHours(t2.warningStartTime, t2.newestSubTime),
minute = EntityFunctions.DiffMinutes(t2.warningStartTime, t2.newestSubTime),
};
//按设备ID编号筛选
if (!string.IsNullOrEmpty(warningQueryParam.SchDeviceId))
{
query = query.Where(w => w.DeviceId.Contains(warningQueryParam.SchDeviceId)).AsQueryable();
}
//按报警信息筛选
if (!string.IsNullOrEmpty(warningQueryParam.SchMessage))
{
query = query.Where(w => w.WarningMessage.Contains(warningQueryParam.SchMessage)).AsQueryable();
}
//按报警时间查询
if (!string.IsNullOrEmpty(warningQueryParam.FirstTime) && !string.IsNullOrEmpty(warningQueryParam.LastTime))
{
query = query.Where(w => (w.warningStartTime > firstTime && w.warningStartTime < lastTime)).AsQueryable();
}
//总条数
warningQueryParam.Total = query.Count();
return(query);
}
public void DateTimeFunction()
{
Context context = new Context(SetUpCodeFirst.Connection);
IQueryable <TableWithSeveralFieldsType> insertedRecord = GetInsertedRecordQueryable(context);
Assert.AreEqual(insertedRecord.Select(c => new { c.MyDateTime.Year }).First().Year, 1969);
Assert.AreEqual(insertedRecord.Select(c => new { c.MyDateTime.Month }).First().Month, 09);
Assert.AreEqual(insertedRecord.Select(c => new { c.MyDateTime.Day }).First().Day, 15);
Assert.AreEqual(insertedRecord.Select(c => new { c.MyDateTime.Hour }).First().Hour, 20);
Assert.AreEqual(insertedRecord.Select(c => new { c.MyDateTime.Minute }).First().Minute, 3);
Assert.AreEqual(insertedRecord.Select(c => new { c.MyDateTime.Second }).First().Second, 19);
#pragma warning disable 618
Assert.AreEqual(insertedRecord.Select(c => new { Date = EntityFunctions.AddDays(c.MyDateTime, 4) }).First().Date.Value.Day, 19);
Assert.AreEqual(insertedRecord.Select(c => new { ElapsedDays = EntityFunctions.DiffDays(c.MyDateTime, c.MyDateTime) }).First().ElapsedDays.Value, 0);
#pragma warning restore 618
context.Dispose();
}
public List <T_FD_CollectionPlan> Select(string customername, string projectid, string date)
{
using (Entities db = new Entities())
{
string fitformatCsNa = string.Format("%{0}%", customername.Trim());
string fitformat = string.Format("%{0}%", projectid.Trim());
DateTime dt = new DateTime();
if (DateTime.TryParse(date, out dt))
{
return(db.T_FD_CollectionPlan.
Where(l =>
SqlFunctions.PatIndex(fitformat, l.ProjectID) > 0 &&
SqlFunctions.PatIndex(fitformatCsNa, l.CustomerName) > 0 &&
EntityFunctions.DiffDays(l.CreateTime, dt) == 0)
.ToList());
}
return(db.T_FD_CollectionPlan.
Where(l => SqlFunctions.PatIndex(fitformat, l.ProjectID) > 0 &&
SqlFunctions.PatIndex(fitformatCsNa, l.CustomerName) > 0)
.ToList());
}
}
public IQueryable <Fixture> GetFixturesThatCanBePenalised(int seasonId, int numberOfDays)
{
DateTime now = DateTime.Now.Date;
// Get fixtures for the current season that have been played and can be penalised
// and
// ResultAddedDate is not null and ResultAddedDate more than 2 days after FixtureDate DON'T KNOW HOW TO DO THIS RIGHT NOW
// or
// ResultAddedDate is null and Today is more than 2 days after fixture
var query = from fixture in Get()
where fixture.HomeTeamLeague.League.Season.Id == seasonId &&
fixture.IsCancelled == "N" &&
fixture.IsPenaltyAllowed == true &&
(
(fixture.ResultAddedDate != null && EntityFunctions.DiffDays(fixture.FixtureDate, fixture.ResultAddedDate) > numberOfDays)
||
(fixture.ResultAddedDate == null && EntityFunctions.DiffDays(fixture.FixtureDate, now) > numberOfDays)
)
select fixture;
return(query);
}
private void setInitialGamesFromDb()
{
_shortMatchCodes = new Dictionary <string, SavedMatch>();
lock (_getExistingGamesLock)
{
var savedLiveMatches = BetDatabase.LiveMatches.Where(lm => EntityFunctions.DiffDays(lm.SetDate, DateTime.Now) == 0).ToList();
if (savedLiveMatches != null && savedLiveMatches.Count > 0)
{
setInitialStaticCodeFromDb();
foreach (var liveMatch in savedLiveMatches)
{
var savedMatch = new SavedMatch
{
ShortMatchCode = (int)liveMatch.BetServiceMatchNo,
IsAlreadysaved = true,
};
_shortMatchCodes.Add(liveMatch.LiveMatchNo.ToString(), savedMatch);
}
}
}
}