/// <summary>
/// Gets the latest communication ID for the SMSFromDefinedValueId to the recipient within daysPastToSearch to present
/// </summary>
/// <param name="fromPhone">From phone.</param>
/// <param name="fromPersonAliasId">From person alias identifier.</param>
/// <param name="daysPastToSearch">The days past to search.</param>
/// <returns></returns>
private int?GetCommunicationId(DefinedValueCache fromPhone, int fromPersonAliasId, int daysPastToSearch)
{
// This is the last communication
using (var rockContext = new RockContext())
{
var recipientService = new CommunicationRecipientService(rockContext);
var latestRecipientCommunication = recipientService
.Queryable()
.AsNoTracking()
.Where(r => r.PersonAliasId == fromPersonAliasId)
.Where(r => r.CreatedDateTime >= DbFunctions.AddDays(RockDateTime.Now, -daysPastToSearch))
.OrderByDescending(c => c.CreatedDateTime)
.FirstOrDefault();
if (latestRecipientCommunication == null)
{
return(null);
}
var communicationService = new CommunicationService(rockContext);
var communication = communicationService
.Queryable()
.AsNoTracking()
.Where(c => c.SMSFromDefinedValueId == fromPhone.Id)
.Where(c => c.Id == latestRecipientCommunication.Id)
.FirstOrDefault();
if (communication != null)
{
return(communication.Id);
}
return(null);
}
}
/// <summary>
/// Danh sách công việc sắp đến hạn
/// </summary>
/// <returns></returns>
public List <CongViecEmailBO> GetJobComingSoon()
{
var now = DateTime.Now;
var curentDate = now.Date;
var result = from job in context.HSCV_CONGVIEC
join user in context.DM_NGUOIDUNG
on(job.NGUOIXULYCHINH_ID != null?job.NGUOIXULYCHINH_ID : job.NGUOIGIAOVIEC_ID) equals user.ID
where 100 != job.PHANTRAMHOANTHANH &&
(
((true == job.IS_HASPLAN && job.NGAYKETTHUC_KEHOACH.HasValue) ? (curentDate <= job.NGAYKETTHUC_KEHOACH &&
DbFunctions.AddDays(now, job.SONGAYNHACTRUOCHAN.HasValue ? job.SONGAYNHACTRUOCHAN.Value : 2) >= job.NGAYKETTHUC_KEHOACH)
:
(curentDate <= job.NGAYHOANTHANH_THEOMONGMUON && DbFunctions.AddDays(now, job.SONGAYNHACTRUOCHAN.HasValue ? job.SONGAYNHACTRUOCHAN.Value : 2) >= job.NGAYHOANTHANH_THEOMONGMUON)
)
)
group new { job, user } by user.ID into gNdung
select new CongViecEmailBO
{
title = "Danh các công việc sắp hết hạn xử lý",
subtitle = "Vui lòng xử lý công việc đúng hạn",
email = gNdung.FirstOrDefault().user.EMAIL,
userName = gNdung.FirstOrDefault().user.HOTEN,
ListCongViec = gNdung.Select(x => x.job).ToList()
};
return(result.ToList());
}
/// <summary>
/// 检查所有项目
/// </summary>
/// <returns></returns>
public static void CheckExpire()
{
using (Entities db = new Entities())
{
List <int> nidList = new List <int>();
List <int> sidList = new List <int>();
nidList = db.ht_news.Where(p =>
(p.status == 0 || p.status == 1)
&&
((p.validity_unit == "月" && DbFunctions.AddMonths(p.add_time, p.validity_num.Value) < DateTime.Now) ||
(p.validity_unit == "天" && DbFunctions.AddDays(p.add_time, p.validity_num.Value) < DateTime.Now))
).Select(p => p.id).ToList();
db.ht_news.Where(p => nidList.Contains(p.id)).Update(p => new ht_news {
status = 3
});
//筛选出货源
nidList = db.ht_news.Where(p => nidList.Contains(p.id) && p.cateid == 1).Select(p => p.id).ToList();;
if (nidList.Count > 0)
{
sidList = db.ht_news_subscribe_relation.Where(p => nidList.Contains(p.news_id))
.Select(p => p.subscribe_id).ToList();
}
if (sidList.Count > 0)
{
db.ht_news_subscribe.Where(p => sidList.Contains(p.id)).Update(p => new ht_news_subscribe
{
ncount = p.ncount - 1
});
db.ht_news_subscribe_relation.Where(p => nidList.Contains(p.news_id)).Delete();
}
}
}
private void RunProcess()
{
var itemList_1 = _dbContext.ServiceProgressReport.Where(x => x.CreatedDate >= DbFunctions.AddDays(DateTime.Now, -60)).ToList().ToList();
var itemList_2 = _dbContext.BAM_TargetHardwareAssetHasCostCenter.Where(x => x.CreatedDate >= DbFunctions.AddDays(DateTime.Now, -60)).ToList();
var itemList_3 = _dbContext.BAM_TargetHardwareAssetHasLocation.Where(x => x.CreatedDate >= DbFunctions.AddDays(DateTime.Now, -60)).ToList();
var itemList_4 = _dbContext.BAM_TargetHardwareAssetHasPrimaryUser.Where(x => x.CreatedDate >= DbFunctions.AddDays(DateTime.Now, -60)).ToList();
var itemList_5 = _dbContext.BAM_ManufacturerEnum.Where(x => x.CreatedDate >= DbFunctions.AddDays(DateTime.Now, -60)).ToList();
var itemList_6 = _dbContext.BAM_ModelEnum.Where(x => x.CreatedDate >= DbFunctions.AddDays(DateTime.Now, -60)).ToList();
var itemList_7 = _dbContext.SCAudit.Where(x => x.CreatedDate >= DbFunctions.AddDays(DateTime.Now, -60)).ToList();
var itemList_8 = _dbContext.SCAuditDeploy.Where(x => x.CreatedDate >= DbFunctions.AddDays(DateTime.Now, -60)).ToList();
var itemList_9 = _dbContext.BAM_HardwareAssetStatus.Where(x => x.CreatedDate >= DbFunctions.AddDays(DateTime.Now, -60)).ToList();
var itemList_10 = _dbContext.BAM_HardwareAssetType.Where(x => x.CreatedDate >= DbFunctions.AddDays(DateTime.Now, -60)).ToList();
var itemList_11 = _dbContext.BAM_HardwareTemplate_Full.Where(x => x.CreatedDate >= DbFunctions.AddDays(DateTime.Now, -60)).ToList();
var itemList_12 = _dbContext.BAM_AssetStatus.Where(x => x.CreatedDate >= DbFunctions.AddDays(DateTime.Now, -60)).ToList();
var itemList_13 = _dbContext.BAM_Deployments.Where(x => x.CreatedDate >= DbFunctions.AddDays(DateTime.Now, -60)).ToList();
var itemList_14 = _dbContext.BAM_Reporting.Where(x => x.CreatedDate >= DbFunctions.AddDays(DateTime.Now, -60)).ToList();
itemList_1.ForEach(item => _dbContext.ServiceProgressReport.Remove(item));
itemList_2.ForEach(item => _dbContext.BAM_TargetHardwareAssetHasCostCenter.Remove(item));
itemList_3.ForEach(item => _dbContext.BAM_TargetHardwareAssetHasLocation.Remove(item));
itemList_4.ForEach(item => _dbContext.BAM_TargetHardwareAssetHasPrimaryUser.Remove(item));
itemList_5.ForEach(item => _dbContext.BAM_ManufacturerEnum.Remove(item));
itemList_6.ForEach(item => _dbContext.BAM_ModelEnum.Remove(item));
itemList_7.ForEach(item => _dbContext.SCAudit.Remove(item));
itemList_8.ForEach(item => _dbContext.SCAuditDeploy.Remove(item));
itemList_9.ForEach(item => _dbContext.BAM_HardwareAssetStatus.Remove(item));
itemList_10.ForEach(item => _dbContext.BAM_HardwareAssetType.Remove(item));
itemList_11.ForEach(item => _dbContext.BAM_HardwareTemplate_Full.Remove(item));
itemList_12.ForEach(item => _dbContext.BAM_AssetStatus.Remove(item));
itemList_13.ForEach(item => _dbContext.BAM_Deployments.Remove(item));
itemList_14.ForEach(item => _dbContext.BAM_Reporting.Remove(item));
_dbContext.SaveChanges();
}
public async Task <IHttpActionResult> WeeklyStatistics()
{
using (var context = this.DataAccess.GetContext())
{
var offset = DateTime.Now.AddDays(-7);
var start = new DateTime(offset.Year, offset.Month, offset.Day, 0, 0, 0);
var result = await context.NoTracking <DAL.Entities.QueryExecution>()
.Where(e => e.Start >= start)
.GroupBy(e => DbFunctions.DiffDays(start, e.Start))
.Select(g => new
{
Days = g.Key,
Total = g.Count(),
Successful = g.Count(e => e.Successful),
Failed = g.Count(e => !e.Successful)
})
.Select(x => new
{
Day = DbFunctions.AddDays(start, x.Days),
Total = x.Total,
Successful = x.Successful,
Failed = x.Failed
})
.OrderBy(x => x.Day)
.ToListAsync();
return(this.Ok(result));
}
}
public List <EmploymentPaper_ToNotify> EmploymentPapersForNotifications()
{
try
{
DateTime Today = DateTime.Now.Date;
return((from c in context.CompanyDocsView
join d in context.DocTypes on c.TypeId equals d.Id
join l in context.LookUpUserCodes on d.DocumenType equals l.SysCodeId
where l.CodeName == Constants.SystemCodes.DocType.CodeName && c.Source == Constants.Sources.People &&
d.HasExpiryDate == true && c.ExpiryDate != null && DbFunctions.AddDays(c.ExpiryDate, -d.NotifyDays) == Today
select new EmploymentPaper_ToNotify
{
Stream_Id = c.stream_id,
PaperFileName = c.name,
DocTypeName = d.Name,
EmpID = c.SourceId.Value,
CompanyId = c.CompanyId,
ExpiryDate = c.ExpiryDate,
AlreadyNotified = context.NotifyLetters.Any(n => n.EmpId == c.SourceId && n.NotifyDate == Today && n.NotifySource == d.Name)
}).Where(a => a.AlreadyNotified == false).ToList());
}
catch
{
return(new List <EmploymentPaper_ToNotify>());
}
}
/// <summary>
/// Job that will send out Assessment reminders
/// </summary>
/// <param name="context">The context.</param>
/// <exception cref="NotImplementedException"></exception>
public void Execute(IJobExecutionContext context)
{
JobDataMap dataMap = context.JobDetail.JobDataMap;
DateTime sendreminderDateTime = DateTime.Now.Date.AddDays(-1 * dataMap.GetInt(AttributeKeys.ReminderEveryDays));
int cutOffDays = dataMap.GetInt(AttributeKeys.CutoffDays);
var assessmentSystemEmailGuid = dataMap.GetString(AttributeKeys.AssessmentSystemEmail).AsGuid();
DateTime currentDate = DateTime.Now.Date;
int assessmentRemindersSent = 0;
int errorCount = 0;
var errorMessages = new List <string>();
using (var rockContext = new RockContext())
{
// Get a list of unique PersonAliasIDs from Assessments where the CreatedDateTime is less than the cut off date and LastReminderDate is null or greater than the reminder date.
var assessmentService = new AssessmentService(rockContext);
var personAliasIds = assessmentService
.Queryable()
.AsNoTracking()
.Where(a => a.Status == AssessmentRequestStatus.Pending)
.Where(a => currentDate <= DbFunctions.AddDays(a.RequestedDateTime, cutOffDays))
.Where(a => (a.LastReminderDate == null && sendreminderDateTime >= DbFunctions.TruncateTime(a.RequestedDateTime)) ||
(sendreminderDateTime >= DbFunctions.TruncateTime(a.LastReminderDate)))
.Select(a => a.PersonAliasId)
.Distinct()
.ToList();
// Go through the list, send a reminder, and update the LastReminderDate for all pending assessments for the person alias
foreach (var personAliasId in personAliasIds)
{
var errors = SendReminderEmail(assessmentSystemEmailGuid, personAliasId);
if (errors.Any())
{
errorMessages.AddRange(errors);
}
else
{
assessmentRemindersSent++;
}
assessmentService.UpdateLastReminderDateForPersonAlias(personAliasId);
}
context.Result = string.Format("{0} assessment reminders sent", assessmentRemindersSent);
if (errorMessages.Any())
{
StringBuilder sb = new StringBuilder();
sb.AppendLine();
sb.Append(string.Format("{0} Errors: ", errorCount));
errorMessages.ForEach(e => { sb.AppendLine(); sb.Append(e); });
string errors = sb.ToString();
context.Result += errors;
var exception = new Exception(errors);
HttpContext context2 = HttpContext.Current;
ExceptionLogService.LogException(exception, context2);
throw exception;
}
}
}
public IHttpActionResult Post(int idLivro)
{
if (RequestContext.Principal?.Identity == null)
{
return(StatusCode(HttpStatusCode.Unauthorized));
}
using (var db = new TccSettings())
{
if (db.tblReserva.Count(r => r.IDProduto == idLivro && DbFunctions.AddDays(r.DataReserva, 7) > DateTime.Now) > 1)
{
return(Json(new {
Code = 400,
Message = "Reserva indisponível"
}));
}
db.tblReserva.Add(new tblReserva()
{
IDLeitor = (RequestContext.Principal.Identity as LeitorIdentity).IDLeitor,
DataReserva = DateTime.Now,
IDProduto = idLivro
});
db.SaveChanges();
}
return(StatusCode(HttpStatusCode.NoContent));
}
public async Task <IEnumerable <string> > GetOperations(string ip)
{
return(await db.Operations.Where(o => o.UserId ==
db.Users.FirstOrDefault(u => u.Ip == ip).Id &&
o.TimeOfOperation > DbFunctions.AddDays(DateTime.Now, -1))
.Select(o => o.Expresssion).ToListAsync());
}
/*
* public static void WayBillInReport(Guid id, BaseEntities db, string template_name)
* {
* var dataForReport = new Dictionary<string, IList>();
*
* var wb = db.v_WaybillList.Where(w => w.Id == id).AsNoTracking().OrderBy(o => o.Num).ToList();
* int wbill_id = wb.First().WbillId;
*
* dataForReport.Add("WayBillList", wb);
* dataForReport.Add("range1", db.GetWaybillDetIn(wbill_id).OrderBy(o => o.Num).ToList());
*
* IHelper.Print(dataForReport, template_name);
* }
*
* public static void WayBillReport(Guid id, BaseEntities db, string template_name)
* {
* var dataForReport = new Dictionary<string, IList>();
*
* var wb = db.v_WaybillList.Where(w => w.Id == id).AsNoTracking().ToList();
* if (wb.Any())
* {
* int wbill_id = wb.First().WbillId;
*
* dataForReport.Add("WayBillList", wb);
* dataForReport.Add("WayBillItems", db.GetWaybillDetIn(wbill_id).ToList().OrderBy(o => o.Num).ToList());
* dataForReport.Add("Commission", db.Commission.Where(w => w.WbillId == wbill_id).Select(s => new
* {
* MainName = s.Kagent.Name,
* FirstName = s.Kagent1.Name,
* SecondName = s.Kagent2.Name,
* ThirdName = s.Kagent3.Name
* }).ToList());
*
* IHelper.Print(dataForReport, template_name);
* }
* else
* {
* MessageBox.Show("Документ відсутній!");
* }
* }
*
* public static Dictionary<string, IList> WayBillOrderedOutReport(Guid id, BaseEntities db)
* {
* var dataForReport = new Dictionary<string, IList>();
*
* var wb = db.v_WaybillList.Where(w => w.Id == id).AsNoTracking().ToList();
* if (wb.Any())
* {
* int wbill_id = wb.First().WbillId;
*
* var wb_list = db.GetWaybillDetIn(wbill_id).ToList().OrderBy(o => o.Num).ToList();
* dataForReport.Add("WayBillList", wb);
* dataForReport.Add("WayBillItems", wb_list);
* dataForReport.Add("WayBillItems2", wb_list);
* }
* else
* {
* MessageBox.Show("Документ відсутній!");
* }
*
* return dataForReport;
* }
*
* public static void WayBillMoveReport(Guid id, BaseEntities db, string template_name)
* {
* var dataForReport = new Dictionary<string, IList>();
*
* var wb = db.v_WaybillList.Where(w => w.Id == id).AsNoTracking().ToList();
* int wbill_id = wb.First().WbillId;
* var wb_items = db.GetWaybillDetIn(wbill_id).OrderBy(o => o.Num).ToList();
* dataForReport.Add("WayBillList", wb);
* dataForReport.Add("WayBillItems", wb_items.GroupBy(g => new { g.MatId, g.MatName, g.MsrName }).Select((s, index) => new
* {
* Num = index + 1,
* s.Key.MatName,
* s.Key.MsrName,
* Amount = s.Sum(sum => sum.Amount)
* }).ToList());
* dataForReport.Add("SummaryField", wb_items.GroupBy(g => new {g.MsrName}).Select(s => new
* {
* s.Key.MsrName,
* Amount = s.Sum(a => a.Amount),
* }).ToList());
*
* IHelper.Print(dataForReport, template_name);
* }
*
*
* public static void WayBillInvwntoryReport(Guid id, BaseEntities db, string template_name)
* {
* var dataForReport = new Dictionary<string, IList>();
* var rel = new List<object>();
*
* var wb = db.v_WaybillList.Where(w => w.Id == id).AsNoTracking().ToList();
* int wbill_id = wb.First().WbillId;
* var items = db.GetWaybillDetIn(wbill_id).OrderBy(o => o.Num).ToList();
*
* var mat_grp = items.GroupBy(g => new { g.GrpName, g.GrpId }).Select(s => new
* {
* s.Key.GrpId,
* Name = s.Key.GrpName,
* TotalOrd = s.Sum(xs => xs.Total)
* }).OrderBy(o => o.Name).ToList();
*
* rel.Add(new
* {
* pk = "GrpId",
* fk = "GrpId",
* master_table = "MatGroup",
* child_table = "WayBillItems"
* });
*
* dataForReport.Add("MatGroup", mat_grp);
* dataForReport.Add("_realation_", rel);
* dataForReport.Add("WayBillList", wb);
* dataForReport.Add("WayBillItems",items );
* dataForReport.Add("Commission", db.Commission.Where(w => w.WbillId == wbill_id).Select(s => new
* {
* MainName = s.Kagent.Name,
* FirstName = s.Kagent1.Name,
* SecondName = s.Kagent2.Name,
* ThirdName = s.Kagent3.Name
* }).ToList());
* dataForReport.Add("SummaryField", items.GroupBy(g => 1).Select(s => new
* {
* SummAll = s.Sum(a => (a.Discount*a.Nds)- (a.Amount * a.Price)),
* }).ToList());
*
* IHelper.Print(dataForReport, template_name);
* }*/
public void WayBillOutReport(Guid id)
{
var wb = _db.v_WaybillList.Where(w => w.Id == id).AsNoTracking().ToList();
if (wb != null)
{
var m = new MoneyToStr("UAH", "UKR", "TEXT");
wb.First().www = m.convertValue(wb.First().SummInCurr.Value);
}
var ent_id = wb.First().EntId;
var wb_det = _db.GetWayBillDetOut(wb.First().WbillId).ToList().OrderBy(o => o.Num).ToList();
data_for_report.Add("EntAccount", _db.EnterpriseAccount.Where(w => w.KaId == ent_id && w.Def == 1).ToList());
data_for_report.Add("WayBillList", wb);
data_for_report.Add("range1", wb_det);
var dt = DateTime.Now.Date;
var w_id = wb.First().WbillId;
var p = _db.WaybillDet.Where(w => w.WbillId == w_id && w.Materials.MatRecipe.Any()).Select(s => new
{
s.Num,
s.Amount,
s.Price,
s.Materials.Name,
s.Materials.Measures.ShortName,
s.Materials.Artikul,
s.Materials.CF1,
s.Materials.CF2,
s.Materials.CF3,
s.Materials.CF4,
s.Materials.CF5,
OnDate = dt < s.OnDate ? DbFunctions.AddDays(s.OnDate, -1) : s.OnDate
}).OrderBy(o => o.Num).ToList();
data_for_report.Add("Posvitcheny", p);
var oz = _db.GetUsedMaterials(-1, dt.AddDays(1), wb.First().KaId).ToList()
.OrderBy(o => o.MatName)
.Select((s, index) => new
{
Num = index + 1,
s.MatName,
s.InvNumber,
s.Price,
s.Remain,
s.MsrName
}).ToList();
data_for_report.Add("range_oz", oz);
var summary = wb_det.Where(w => w.PosType != 2).GroupBy(g => g.MsrName).Select(s => new
{
MsrName = s.Key,
Amount = s.Sum(sum => sum.Amount)
});
data_for_report.Add("Summary", summary.ToList());
}
public QueueStat(Queue queue)
{
var avgin = (db.Processing.Where(p => p.ProcUserComplete >= DbFunctions.AddDays(DateTime.Now, -60) && p.Username != null).Average(p => DbFunctions.DiffDays(p.ProcInQueue, p.ProcUserComplete))) / 24;
Priorities = db.Processing.Where(p => p.ProcUserComplete == null && p.Record.ProcPriority == true && p.QueueID == queue.QueueID).Count();
this.queue = queue;
fileTypeAmount = new List <StringAndInt>();
var data = db.Processing.Include(p => p.Record.FileType).Where(p => p.ProcUserComplete == null && p.QueueID == queue.QueueID).ToList();
queueAmount = data.Count();
var i = data.Select(p => p.Record.FileType).Distinct().ToList();
highestTypeAmount = -1;
foreach (var item in i)
{
var x = new StringAndInt();
x.MyString = item.TypeDescription;
x.MyInt = data.Where(p => p.Record.FileTypeID == item.FileTypeID).Count();
if (x.MyInt > highestTypeAmount)
{
highestTypeAmount = x.MyInt;
}
fileTypeAmount.Add(x);
}
var avg = (db.Processing.Where(p => p.ProcUserComplete >= DbFunctions.AddDays(DateTime.Now, -60) && p.Username != null && p.QueueID == queue.QueueID).Average(p => DbFunctions.DiffDays(p.ProcInQueue, p.ProcUserComplete)));
if (avg == null)
{
AvgQueueTime = 0;
}
else
{
AvgQueueTime = Math.Round((double)avg, 2);
}
}
private void cmdQuery2_Click(object sender, EventArgs e)
{
using (var context = new AdvContext()) {
// demo multiple step query
var query = from onePerson in context.People
where onePerson.BusinessEntityID <= 10
select new
{
onePerson.EmailPromotion,
onePerson.LastName
};
query = query.OrderBy(p => p.LastName);
//esegui la query solo in questo momento!
var sum = query.Sum(p => p.EmailPromotion);
Console.WriteLine("somma = {0}", sum);
// demo utilizzo di "funzioni aggiuntive" (DbFunctions) nelle query
var query2 = from onePerson in context.People
where onePerson.BusinessEntityID <= 6 && DbFunctions.AddDays(onePerson.ModifiedDate, 2) > new DateTime(1970, 1, 1)
select new
{
onePerson.LastName,
onePerson.ModifiedDate
};
foreach (var p in query2)
{
Console.WriteLine("Nome: {0}, DataModifica: {1}", p.LastName, p.ModifiedDate);
}
}
}
static void Main(string[] args)
{
List <Goods> repoGoods = new List <Goods>();
List <Unit> repoGoodsUnit = new List <Unit>();
var results = (from m in repoGoods.Where(x =>
x.Date < DbFunctions.AddDays(DateTime.Now, 1))
join n in repoGoodsUnit on m.MasterID equals n.Id
select new DailyVM()
{
GoodsName = n.GoodsName,
Price = n.Price * m.Count,
GoodsCount = m.Count,
SheetNO = m.SheetNO,
subtotal = n.Price * m.Count,
Date = m.Date,
MasterID = m.MasterID,
})
.GroupBy(x => new { name = x.GoodsName, sheet = x.SheetNO, date = x.Date, masterId = x.MasterID })
.Select(x => new DailyVM()
{
GoodsName = x.Key.name,
Price = x.Sum(y => y.Price),
GoodsCount = x.Sum(y => y.GoodsCount),
SheetNO = x.Key.sheet,
subtotal = x.Sum(y => y.subtotal),
Date = x.Key.date,
MasterID = x.Key.masterId
}).ToList();
}
public ActionResult GetListErrors(string fechaInicio, string fechaFin)
{
List <LogErrorDto> lista = new List <LogErrorDto>();
using (var context = new NuevoDbContext())
{
if (!String.IsNullOrEmpty(fechaInicio) && !String.IsNullOrEmpty(fechaFin))
{
var fechaDesde = DateTime.Parse(fechaInicio);
var fechaHasta = DateTime.Parse(fechaFin);
lista = context.ErrorLogs.Where(e => e.Fecha > DbFunctions.AddDays(fechaDesde, 0) && e.Fecha < DbFunctions.AddDays(fechaHasta, 1)).ToList();
}
else
if (!String.IsNullOrEmpty(fechaInicio))
{
var fechaDesde = DateTime.Parse(fechaInicio);
lista = context.ErrorLogs.Where(e => e.Fecha > DbFunctions.AddDays(fechaDesde, 0)).ToList();
}
else
if (!String.IsNullOrEmpty(fechaFin))
{
var fechaHasta = DateTime.Parse(fechaFin);
lista = context.ErrorLogs.Where(e => e.Fecha < DbFunctions.AddDays(fechaHasta, 1)).ToList();
}
return(PartialView("ErrorList", lista));
}
}
//TODO: Make a NotificationService to centralize and encapsulate notification methods/logic
public void SendFriendRequestNotifications()
{
// get friend requests that haven't had a notification sent
var friendRequests = _customerFriendRepository.Table
.Where(x => x.Confirmed == false && x.NotificationCount == 0)
.GroupBy(x => x.ToCustomerId)
.Select(g => new { CustomerId = g.Key, FriendRequestCount = g.Count() })
.ToList();
foreach (var friendRequest in friendRequests)
{
var customer = _customerService.GetCustomerById(friendRequest.CustomerId);
if (customer == null)
{
continue;
}
_mobSocialMessageService.SendFriendRequestNotification(customer, friendRequest.FriendRequestCount,
_workContext.WorkingLanguage.Id, _storeContext.CurrentStore.Id);
UpdateFriendRequestNotificationCounts(friendRequest.CustomerId);
}
// Send a reminder a week later. Get friend requests that have had one notification since last week
var weekUnconfirmedFriendRequests = _customerFriendRepository.Table
.Where(x => x.Confirmed == false && x.NotificationCount == 1)
.Where(x => DbFunctions.AddDays(x.LastNotificationDate, 7) < DateTime.Now)
.GroupBy(x => x.ToCustomerId)
.Select(g => new { CustomerId = g.Key, FriendRequestCount = g.Count() })
.ToList();
foreach (var weekUnconfirmedFriendRequest in weekUnconfirmedFriendRequests)
{
var customer = _customerService.GetCustomerById(weekUnconfirmedFriendRequest.CustomerId);
_mobSocialMessageService.SendPendingFriendRequestNotification(customer,
weekUnconfirmedFriendRequest.FriendRequestCount, _workContext.WorkingLanguage.Id, _storeContext.CurrentStore.Id);
UpdateFriendRequestNotificationCounts(weekUnconfirmedFriendRequest.CustomerId);
}
// Send pending friend request reminder each month
var monthlyUnconfirmedFriendRequests = _customerFriendRepository.Table
.Where(x => x.Confirmed == false && x.NotificationCount > 1)
.Where(x => DbFunctions.AddMonths(x.LastNotificationDate, 1) < DateTime.Now)
.GroupBy(x => x.ToCustomerId)
.Select(g => new { CustomerId = g.Key, FriendRequestCount = g.Count() })
.ToList();
foreach (var monthlyUnconfirmedFriendRequest in monthlyUnconfirmedFriendRequests)
{
var customer = _customerService.GetCustomerById(monthlyUnconfirmedFriendRequest.CustomerId);
_mobSocialMessageService.SendPendingFriendRequestNotification(customer,
monthlyUnconfirmedFriendRequest.FriendRequestCount, _workContext.WorkingLanguage.Id, _storeContext.CurrentStore.Id);
UpdateFriendRequestNotificationCounts(monthlyUnconfirmedFriendRequest.CustomerId);
}
}
private int GetjoinedThisWeek()
{
return
((from m in db.Members
where m.JoinDate < DateTime.Now && m.JoinDate > DbFunctions.AddDays(DateTime.Now, -7)
select m)
.Count());
}
public ResponseResult <GridResponse <object> > SearchDataWindow2(DateTime inputDate, DateTime dateData, string location, GridSettings gridSettings)
{
var lstTe80 = _unitOfWork.EnvMespRepository.GetMany(i => DbFunctions.AddDays(i.F80_D_From, -1) >= inputDate && DbFunctions.AddDays(i.F80_D_To, -1) <= dateData);
var itemCount = lstTe80.Count();
var resultModel = new GridResponse <object>(lstTe80, itemCount);
return(new ResponseResult <GridResponse <object> >(resultModel, true));
}
private int GetDueBackToday()
{
return
((from l in db.Loans
where
l.Returned == 0 && l.DueDate > DateTime.Now && l.DueDate < DbFunctions.AddDays(DateTime.Now, 1)
select l)
.Count());
}
/// <summary>
/// Süresi gelmiş Daimi Aramaları kontrol eder
/// Veri tabanında ayarlar bölümünde "DaimiAramaKontrolSuresi" kontrol edilecek ek gün sayısını belirler buna göre kontrol yapılır.
/// </summary>
/// <returns></returns>
public int DaimiAramaTKontrol()
{
using (var db = new ETSEntities())
{
var ayarlar = db.Ayarlar.SingleOrDefault();
var ekGun = ayarlar == null ? 0 : int.Parse(ayarlar.DaimiAramaKontrolSuresi.ToString());
return(db.DaimiArastirmaTutanaklari.Count(x => x.ZamanAsimi <= DbFunctions.AddDays(DateTime.Now, ekGun)));
}
}
private void btnShow_Click(object sender, EventArgs e)
{
dataGVOshTraining.DataSource = db.ViewOshTrainings.
Where(vOsh => vOsh.DepartmentName == domainUpDownDepartmentName.Text &&
DbFunctions.AddDays(vOsh.TrainingDate, (int)vOsh.ValidityOfOshTraining * 365) <= DbFunctions.AddDays(DateTime.Now, 45) &&
vOsh.EndDate > DbFunctions.AddDays(DateTime.Now, 45))
.Select(osh => new { osh.EmployeeName, osh.EmployeeSurname, NextTrainingDate = DbFunctions.AddDays(osh.TrainingDate, (int)osh.ValidityOfOshTraining * 365) })
.ToList();
}
private static bool SyncFromCentralToLocal <TTransfer, TEquip>(ConnectionStringSettings connectionStringSettings)
where TTransfer : class, ITransfer
where TEquip : class, IEquip
{
List <TTransfer> centralTransferList = new List <TTransfer>();
string stringForLogger = "";
int[] scalesArray;
try
{
List <DateTime> time = new List <DateTime>();
using (CtsEquipContext <TEquip> centralDB = new CtsEquipContext <TEquip>("centralDBConnection"))
{
scalesArray = centralDB.DbSet.Where(x => x.LocationID == connectionStringSettings.Name.ToString()).Select(m => m.ID).ToArray();
}
using (CtsTransferContext <TTransfer> centralDB = new CtsTransferContext <TTransfer>("centralDBConnection"))
{
centralTransferList.AddRange(centralDB.DbSet.Where(x => scalesArray.Contains((int)x.EquipID))
.Where(n => n.OperatorName != "System Platform")
.Where(x => x.TransferTimeStamp > DbFunctions.AddDays(System.DateTime.Now, -2)));
}
if (centralTransferList.Count != 0)
{
using (CtsTransferContext <TTransfer> localDB = new CtsTransferContext <TTransfer>(connectionStringSettings.ConnectionString))
{
using (var transaction = localDB.Database.BeginTransaction())
{
foreach (var t in centralTransferList)
{
if (t is IHaveAnalysis transfer)
{
transfer.AnalysisID = null;
}
localDB.DbSet.AddOrUpdate(t as TTransfer);
stringForLogger = String.Concat(stringForLogger, t.ID, ";");
}
localDB.SaveChanges();
transaction.Commit();
}
}
}
return(true);
}
catch (Exception ex)
{
lock (log)
{
log.Message(ex);
}
return(false);
}
}
/// <summary>
/// Süresi gelmiş Giden evrakları kontrol eder
/// Veri tabanında ayarlar bölümünde "GidenEvrakKontrolSuresi" kontrol edilecek ek gün sayısını belirler buna göre kontrol yapılır.
/// </summary>
/// <returns></returns>
public int GidenEvraklarKontrol()
{
using (var db = new ETSEntities())
{
var ayarlar = db.Ayarlar.SingleOrDefault();
var ekGun = ayarlar == null ? 0 : int.Parse(ayarlar.GidenEvrakKontrolSuresi.ToString());
return(db.GidenEvrak.Count(x => x.EvrakSonTarihi <= DbFunctions.AddDays(DateTime.Now, ekGun) && x.Durum == 0));
}
}
private void setQueues(short id)
{
DataTable secondT = new DataTable();
var queue = db.Queue.Include(q => q.Group).Include(q => q.Queue2).Include(q => q.QueueOrder).Where(q => q.GroupID == id);
List <QueueVM> vm = new List <QueueVM>();
foreach (var item in queue)
{
QueueVM v = new QueueVM();
if ((db.Queue.Where(p => p.QueueID == item.QueueNextQueue)).Select(q => q.QueueDescription).Count() > 0)
{
v.NextQueue = (db.Queue.Where(p => p.QueueID == item.QueueNextQueue)).Select(q => q.QueueDescription).First();
}
if (db.Processing.Where(p => p.ProcUserComplete == null).Where(p => p.QueueID == item.QueueID).OrderBy(p => p.ProcInQueue).Count() > 0)
{
v.OldestFileInQueue = Math.Round((double)(DateTime.Today - (db.Processing.Where(p => p.ProcUserComplete == null).Where(p => p.QueueID == item.QueueID).OrderBy(p => p.ProcInQueue).First().ProcInQueue)).TotalDays, 2);
}
var dodList = db.Processing.Include(path => path.Record).Where(p => p.ProcUserComplete == null && p.Record.DOD != null).Where(p => p.QueueID == item.QueueID).OrderBy(p => p.Record.DOD);
if (dodList.Count() > 0)
{
v.OldestProcDate = DateTime.Parse(dodList.OrderBy(p => p.Record.DOD).First().Record.DOD.ToString()).ToString("MM/dd/yyyy");
}
var fq = db.FilesInQueue(item.QueueID).Sum(p => p.fileAmount).ToString();
var uq = db.UsersInQueue(item.QueueID).Count().ToString();
if (fq == "")
{
v.FilesInQueue = 0;
}
else
{
v.FilesInQueue = Int32.Parse(fq);
}
if (uq == "")
{
v.UsersInQueue = 0;
}
else
{
v.UsersInQueue = Int32.Parse(uq);
}
var avg = (db.Processing.Where(p => p.ProcUserComplete >= DbFunctions.AddDays(DateTime.Now, -60) && p.Username != null && p.QueueID == item.QueueID).Average(p => DbFunctions.DiffDays(p.ProcInQueue, p.ProcUserComplete)));
if (avg == null)
{
v.AvgQueueTime = 0;
}
else
{
v.AvgQueueTime = Math.Round((double)avg, 2);
}
v.Auditing = item.AuditQueue;
v.q = item;
vm.Add(v);
}
Queues = vm;
}
public IEnumerable <Appointment> GetDoneAppointments()
{
var appointments = from x in _ctx.Appointments
where (x.IsAccepted) &&
((x.AppointmentDateTime.Month == DateTime.Now.Month && DateTime.Now.Day - x.AppointmentDateTime.Day == 1) ||
(DbFunctions.AddDays(DateTime.Now, -1).Value.Month == x.AppointmentDateTime.Month && DbFunctions.AddDays(DateTime.Now, -1).Value.Day == x.AppointmentDateTime.Day))
select x;
return(appointments.AsEnumerable());
}
public IEnumerable <Appointment> GetAppointmentsComingInTwoDays()
{
var appointments = from x in _ctx.Appointments
where (x.IsAccepted && !x.IsDatePassed) &&
((x.AppointmentDateTime.Month == DateTime.Now.Month && x.AppointmentDateTime.Day - DateTime.Now.Day == 2) ||
(DbFunctions.AddDays(DateTime.Now, 2).Value.Month == x.AppointmentDateTime.Month && DbFunctions.AddDays(DateTime.Now, 2).Value.Day == x.AppointmentDateTime.Day))
select x;
return(appointments.AsEnumerable());
}
private void button6_Click(object sender, EventArgs e)
{
using (var context = new BookLotEntities())
{
var items = (from d in context.Orders
join d1 in context.OrderItems
on d.OrderId equals d1.OrderId
join d2 in context.Books
on d1.BookId equals d2.BookId
join d3 in context.Authors
on d2.AuthorId equals d3.AuthorId
//group d1.Quantity by d3.AuthorLastName into g
//orderby g.Sum() descending
//where d.BookId == d1.BookId
//where d.OrderDate == DbFunctions.AddDays(d.OrderDate, +7)
where (d.Total == d.OrderItems.Quantity ||
d.OrderId == d.OrderItems.OrderId ||
d.OrderId == d.OrderItems.Price ||
DateTime.Now <= DbFunctions.AddDays(d.OrderDate, -30))
select new
{
d.OrderId,
d3.AuthorLastName,
d3.AuthorFirstName
}).Take(5).ToList();
foreach (var item in items)
{
listView1.Items.Add(item.AuthorFirstName);
listView1.Items.Add(item.AuthorLastName);
}
}
//using (var context = new BookLotEntities())
//{
// var items = (from item in context.Orders
// group item.OrderDate by item.OrderItems.Books.Authors.AuthorLastName into g
// orderby g.FirstOrDefault() descending
// select new
// {
// g.Key.,
// g.Key.AuthorFirstName,
// g.Key.AuthorLastName
// }).Take(5).ToList();
// foreach (var item in items)
// {
// listView1.Items.Add(item.AuthorFirstName);
// listView1.Items.Add(item.AuthorLastName);
// listView1.Items.Add(item.AuthorId.ToString());
// }
//}
}
public void GetUsingClass()
{
var usingClass = (from uc in DataProvider.Ins.DB.USINGCLASSes
join cl in DataProvider.Ins.DB.CLASSes on uc.ClassID equals cl.ClassID
join rm in DataProvider.Ins.DB.ROOMs on uc.RoomID equals rm.RoomID
where DbFunctions.AddDays(_startDate, (uc.Day_ + 6) % 7) >= uc.StartDate && // Satisfy the start and end date of class
DbFunctions.AddDays(_startDate, (uc.Day_ + 6) % 7) <= uc.EndDate && // Satisfy the start and end date of class
(DbFunctions.DiffDays(DbFunctions.AddDays(_startDate, (uc.Day_ + 6) % 7), cl.StartDate) / 7) % uc.RepeatCycle == 0 // satisfied the repeat cycle
select new { uc, cl, rm }
).ToList();
foreach (var ins in usingClass)
{
if (!IsExistRoom(ins.rm))
{
var listPeriod = (from period in DataProvider.Ins.DB.PERIOD_TIMERANGE
where period.PeriodID != -1
select period).ToList();
foreach (var period in listPeriod)
{
RoomTableRow roomTableRow = new RoomTableRow();
roomTableRow.room = ins.rm;
roomTableRow.period = period;
listRoomTableRow.Add(roomTableRow);
}
}
// for each using, we split it into multiple period row
var listPeriodTimeRange = Utilities.Utils.GetListPeriodTimeRange(ins.uc.StartPeriod, ins.uc.Duration);
foreach (var periodTimeRange in listPeriodTimeRange)
{
if (IsExistRoomRow(ins.rm, periodTimeRange))
{
var roomTableRow = listRoomTableRow.First((x) => {
return(x.room.RoomID == ins.rm.RoomID &&
x.period.PeriodID == periodTimeRange.PeriodID);
});
roomTableRow.usings[ins.uc.Day_] = ins.uc;
}
else
{
RoomTableRow roomTableRow = new RoomTableRow();
roomTableRow.room = ins.rm;
roomTableRow.period = periodTimeRange;
roomTableRow.usings[ins.uc.Day_] = ins.uc;
listRoomTableRow.Add(roomTableRow);
}
}
}
}
public void DbFunctionsTests_AddDays_DateTime_Test()
{
var result = this.GetOrderQuery().Select(x => (DateTime)DbFunctions.AddDays(this.TestDateTime, 1)).First();
Assert.AreEqual(YEAR, result.Year);
Assert.AreEqual(MONTH, result.Month);
Assert.AreEqual(DAY + 1, result.Day);
Assert.AreEqual(HOUR, result.Hour);
Assert.AreEqual(MINUTE, result.Minute);
Assert.AreEqual(SECOND, result.Second);
}
public List <Pedido> BuscarRelatorioMensal()
{
return(contexto.Pedidos
.Include("Combo")
.Include("Imovel")
.Include("Cliente")
.Where(p => p.DataEntregaRealizada != null &&
DbFunctions.AddDays(p.DataEntregaRealizada, 30) >= DateTime.Now &&
p.TotalPago != null && p.TotalPrevisto != null)
.ToList());
}
public void DateFunctions()
{
using (var context = new BloggingContext(ConnectionString))
{
IQueryable <int> oneRow = context.Posts.Where(p => false).Select(p => 1).Concat(new int[] { 1 });
var dateAdds = oneRow.Select(p => new List <DateTime?>
{
DbFunctions.AddDays(new DateTime(2014, 2, 28), 1),
DbFunctions.AddHours(new DateTime(2014, 2, 28, 23, 0, 0), 1),
DbFunctions.AddMinutes(new DateTime(2014, 2, 28, 23, 59, 0), 1),
DbFunctions.AddSeconds(new DateTime(2014, 2, 28, 23, 59, 59), 1),
DbFunctions.AddMilliseconds(new DateTime(2014, 2, 28, 23, 59, 59, 999), 2 - p),
DbFunctions.AddMicroseconds(DbFunctions.AddMicroseconds(new DateTime(2014, 2, 28, 23, 59, 59, 999), 500), 500),
DbFunctions.AddNanoseconds(new DateTime(2014, 2, 28, 23, 59, 59, 999), 999999 + p),
DbFunctions.AddMonths(new DateTime(2014, 2, 1), 1),
DbFunctions.AddYears(new DateTime(2013, 3, 1), 1)
}).First();
foreach (var result in dateAdds)
{
Assert.IsTrue(result.Value == new DateTime(2014, 3, 1, 0, 0, 0));
}
var dateDiffs = oneRow.Select(p => new {
a = DbFunctions.DiffDays(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(2000, 1, 1, 0, 0, 0)),
b = DbFunctions.DiffHours(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(2000, 1, 1, 0, 0, 0)),
c = DbFunctions.DiffMinutes(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(2000, 1, 1, 0, 0, 0)),
d = DbFunctions.DiffSeconds(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(2000, 1, 1, 0, 0, 0)),
e = DbFunctions.DiffMilliseconds(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(2000, 1, 1, 0, 0, 0)),
f = DbFunctions.DiffMicroseconds(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(2000, 1, 1, 0, 0, 0)),
g = DbFunctions.DiffNanoseconds(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(2000, 1, 1, 0, 0, 0)),
h = DbFunctions.DiffMonths(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(3000, 1, 1, 0, 0, 0)),
i = DbFunctions.DiffYears(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(3000, 1, 1, 0, 0, 0)),
j = DbFunctions.DiffYears(null, new DateTime(2000, 1, 1)),
k = DbFunctions.DiffMinutes(new TimeSpan(1, 2, 3), new TimeSpan(4, 5, 6)),
l = DbFunctions.DiffMinutes(new TimeSpan(1, 2, 3), null)
}).First();
Assert.AreEqual(dateDiffs.a, 1);
Assert.AreEqual(dateDiffs.b, 1);
Assert.AreEqual(dateDiffs.c, 1);
Assert.AreEqual(dateDiffs.d, 1);
Assert.AreEqual(dateDiffs.e, 1);
Assert.AreEqual(dateDiffs.f, 1000);
Assert.AreEqual(dateDiffs.g, 1000000);
Assert.AreEqual(dateDiffs.h, 12001);
Assert.AreEqual(dateDiffs.i, 1001);
Assert.AreEqual(dateDiffs.j, null);
Assert.AreEqual(dateDiffs.k, 183);
Assert.AreEqual(dateDiffs.l, null);
}
}
