private void btnSorgu7_Click(object sender, EventArgs e) { //Ünvanı Mr. olan ve yaşı 60'tan büyük olan çalışanları listeleyin dataGridView1.DataSource = db.Employees.Where(x => x.TitleOfCourtesy == "Mr." && SqlFunctions.DateDiff("Year", x.BirthDate, DateTime.Now) > 60).ToList(); }
public async Task <int> GetDineCount(DateTime dateTime) { return(await ctx.Dines.CountAsync(p => SqlFunctions.DateDiff("day", p.BeginTime, dateTime) == 0)); }
public static List <Advertise> Search(DateTime?sdate, DateTime?edate, string idat = "", string title = "") { List <Advertise> advertises = new List <Advertise>(); TN db = new TN(); advertises = (from ad in db.Advertises join at in db.AdvertiseTypes on ad.idat equals at.idat orderby ad.sortno descending, ad.sortno descending select ad).Where(en => ( (sdate == null || SqlFunctions.DateDiff("dd", sdate.Value, en.cretime) >= 0) && (edate == null || SqlFunctions.DateDiff("dd", edate.Value, en.cretime) <= 0) && (string.IsNullOrEmpty(title) || en.title.Contains(title)) && (string.IsNullOrEmpty(idat) || en.idat == idat) )).ToList(); return(advertises); }
public async Task <ForwardStatisticalResult> GetMonthAsync(DateTime?dateTime) { using (MyDbContext dbc = new MyDbContext()) { ForwardStatisticalResult result = new ForwardStatisticalResult(); IQueryable <ForwardEntity> forwards = dbc.GetAll <ForwardEntity>().Where(f => SqlFunctions.DateDiff("month", dateTime, f.CreateTime) == 0); if (!await forwards.AnyAsync()) { result.TotalBonus = 0; result.TotalCount = 0; } else { result.TotalBonus = await forwards.SumAsync(f => f.Task.Bonus); result.TotalCount = await forwards.LongCountAsync(); } return(result); } }
/// <summary> /// Gets the chart data. /// </summary> /// <param name="groupBy">The group by.</param> /// <param name="graphBy">The graph by.</param> /// <param name="startDate">The start date.</param> /// <param name="endDate">The end date.</param> /// <param name="groupIds">The group ids.</param> /// <param name="campusIds">The campus ids.</param> /// <returns></returns> public IEnumerable <IChartData> GetChartData(AttendanceGroupBy groupBy = AttendanceGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime?startDate = null, DateTime?endDate = null, string groupIds = null, string campusIds = null) { var qry = Queryable().AsNoTracking().Where(a => a.DidAttend.HasValue && a.DidAttend.Value); if (startDate.HasValue) { qry = qry.Where(a => a.StartDateTime >= startDate.Value); } if (endDate.HasValue) { qry = qry.Where(a => a.StartDateTime < endDate.Value); } if (!string.IsNullOrWhiteSpace(groupIds)) { var groupIdList = groupIds.Split(',').AsIntegerList(); qry = qry.Where(a => a.GroupId.HasValue && groupIdList.Contains(a.GroupId.Value)); } if (!string.IsNullOrWhiteSpace(campusIds)) { var campusIdList = campusIds.Split(',').AsIntegerList(); qry = qry.Where(a => a.CampusId.HasValue && campusIdList.Contains(a.CampusId.Value)); } //// for Date SQL functions, borrowed some ideas from http://stackoverflow.com/a/1177529/1755417 and http://stackoverflow.com/a/133101/1755417 and http://stackoverflow.com/a/607837/1755417 var knownSunday = new DateTime(1966, 1, 30); // Because we can't use the @@DATEFIRST option in Linq to query how DATEPART("weekday",) will work, use a known Sunday date instead. var qryWithSundayDate = qry.Select(a => new { Attendance = a, SundayDate = SqlFunctions.DateAdd( "day", SqlFunctions.DateDiff("day", "1900-01-01", SqlFunctions.DateAdd("day", (((SqlFunctions.DatePart("weekday", knownSunday) + 7) - SqlFunctions.DatePart("weekday", a.StartDateTime)) % 7), a.StartDateTime ) ), "1900-01-01" ) }); var summaryQry = qryWithSundayDate.Select(a => new { // Build a CASE statement to group by week, or month, or year SummaryDateTime = (DateTime)( // GroupBy Week with Monday as FirstDayOfWeek ( +1 ) and Sunday as Summary Date ( +6 ) groupBy == AttendanceGroupBy.Week ? a.SundayDate : // GroupBy Month groupBy == AttendanceGroupBy.Month ? SqlFunctions.DateAdd("day", -SqlFunctions.DatePart("day", a.SundayDate) + 1, a.SundayDate) : // GroupBy Year groupBy == AttendanceGroupBy.Year ? SqlFunctions.DateAdd("day", -SqlFunctions.DatePart("dayofyear", a.SundayDate) + 1, a.SundayDate) : // shouldn't happen null ), Campus = new { Id = a.Attendance.CampusId, Name = a.Attendance.Campus.Name }, Group = new { Id = a.Attendance.GroupId, Name = a.Attendance.Group.Name }, Schedule = new { Id = a.Attendance.ScheduleId, Name = a.Attendance.Schedule.Name } }); List <AttendanceSummaryData> result = null; if (graphBy == AttendanceGraphBy.Total) { var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key); result = groupByQry.ToList().Select(a => new AttendanceSummaryData { DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(), DateTime = a.Key.SummaryDateTime, SeriesId = "Total", YValue = a.Count }).ToList(); } else if (graphBy == AttendanceGraphBy.Campus) { var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Campus }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key); result = groupByQry.ToList().Select(a => new AttendanceSummaryData { DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(), DateTime = a.Key.SummaryDateTime, SeriesId = a.Key.Series.Name, YValue = a.Count }).ToList(); } else if (graphBy == AttendanceGraphBy.Group) { var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Group }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key); result = groupByQry.ToList().Select(a => new AttendanceSummaryData { DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(), DateTime = a.Key.SummaryDateTime, SeriesId = a.Key.Series.Name, YValue = a.Count }).ToList(); } else if (graphBy == AttendanceGraphBy.Schedule) { var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Schedule }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key); result = groupByQry.ToList().Select(a => new AttendanceSummaryData { DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(), DateTime = a.Key.SummaryDateTime, SeriesId = a.Key.Series.Name, YValue = a.Count }).ToList(); } if (result.Count == 1) { var dummyZeroDate = startDate ?? DateTime.MinValue; result.Insert(0, new AttendanceSummaryData { DateTime = dummyZeroDate, DateTimeStamp = dummyZeroDate.ToJavascriptMilliseconds(), SeriesId = result[0].SeriesId, YValue = 0 }); } return(result); }
protected override IQueryable <CriHcPreview> PreviewQuery(Guid id, ccEntities _db) { var objectSet = _db.CriBases.OfType <CriHc>(); var Clients = this.GetClients(_db); var SubReports = this.GetSubReports(_db); var subReportId = _db.Imports.Where(f => f.Id == id).Select(f => f.TargetId).FirstOrDefault(); var details = (from sr in _db.SubReports where sr.Id == subReportId select new { MrStart = sr.MainReport.Start, MrEnd = sr.MainReport.End, AgencyId = sr.AppBudgetService.AgencyId, AppId = sr.AppBudgetService.AppBudget.AppId, AgencyGroupId = sr.AppBudgetService.Agency.GroupId, sr.AppBudgetService.Service.ReportingMethodId }).SingleOrDefault(); var startingWeek = details.MrStart; DayOfWeek selectedDOW = startingWeek.DayOfWeek; int? selectedDowDb = GlobalHelper.GetWeekStartDay(details.AgencyGroupId, details.AppId); if (selectedDowDb.HasValue) { selectedDOW = (DayOfWeek)selectedDowDb.Value; if (startingWeek.Month > 1) { var diff = selectedDowDb.Value - (int)startingWeek.DayOfWeek; startingWeek = startingWeek.AddDays((double)(diff)); if (startingWeek > details.MrStart) { startingWeek = startingWeek.AddDays(-7); } } } var weekToSubstruct = new DateTime(startingWeek.Year, 1, 1); weekToSubstruct = weekToSubstruct.AddDays(Math.Abs((int)selectedDOW - (int)weekToSubstruct.DayOfWeek)); var mrStartDate = details.MrStart; if (details.ReportingMethodId == (int)Service.ReportingMethods.HomecareWeekly) { mrStartDate = startingWeek; } DateTime JoinDateToCompare = new DateTime(2018, 1, 1); var source = (from item in objectSet where item.ImportId == id join client in Clients on item.ClientId equals client.Id into clientsGroup from client in clientsGroup.DefaultIfEmpty() join subReport in SubReports on item.SubReportId equals subReport.Id into srg from subReport in srg.DefaultIfEmpty() let ldx = System.Data.Objects.EntityFunctions.AddDays(client.LeaveDate, client.DeceasedDate == null ? 0 : 90) let start = item.Date ?? subReport.MainReport.Start let mrStart = subReport.MainReport.Start let end = subReport.MainReport.End let diff = SqlFunctions.DateDiff("day", weekToSubstruct, item.Date) select new CriHcPreview { RowIndex = item.RowIndex, ClientId = item.ClientId, ClientName = client.AgencyId != subReport.AppBudgetService.AgencyId ? "" : client.FirstName + " " + client.LastName, Date = item.Date, Week = "W" + SqlFunctions.StringConvert((decimal?)(diff / 7 + (SqlFunctions.DatePart("day", weekToSubstruct) == 1 || diff < 0 ? 1 : 2))), Rate = item.Rate, Quantity = item.Quantity, Remarks = item.Remarks, Errors = (item.Errors ?? "") + ( ((client == null) ? "Invalid ClientId. " : "") + ((client.AgencyId != subReport.AppBudgetService.AgencyId) ? "Invalid Agency. " : "") + ((item.Rate == null || item.Rate <= 0) ? "Rate must be greater than 0. " : "") + ((item.Quantity == null || item.Quantity < 0) ? "Quantity must be greater or equal to 0. " : "") + (client.JoinDate > end ? "Client has joined after report end date" : "") + (client.DeceasedDate == null && client.LeaveDate < mrStart ? "Client has left before report start date" : "") + (client.DeceasedDate != null && client.DeceasedDate < mrStart ? "Client has DOD before report start date" : "") + ((item.Date < mrStartDate || item.Date >= subReport.MainReport.End) ? "Invalid Date (report date is outside of the report period)" : "") + ((client.AgencyId != subReport.AppBudgetService.AgencyId) ? "Invalid Agency. " : "") + ((client.JoinDate > subReport.MainReport.End) ? "Client cant be reported because of Join Date. " : "") + (ldx < start ? "The Client has left the agency." : "") + ( (client.LeaveDate < start && ldx >= start && (item.Remarks == null || item.Remarks.Trim().Length == 0)) ? "Deceased Client needs to be specified with Unique Circumstances." : "" ) + //client is not marked as complied but the verification is required (( !client.IncomeCriteriaComplied && client.Agency.AgencyGroup.Country.IncomeVerificationRequired && (client.FundStatusId == null || client.FundStatus.IncomeVerificationRequired)) ? "Income verification required. " : "") + (!(client.ExceptionalHours > 0 || client.GrandfatherHours.Any(f => f.StartDate < subReport.MainReport.End) || client.FunctionalityScores.Any(f => f.StartDate < subReport.MainReport.End)) ? "Insufficient Home Care Hours limit. " : "") + (!(client.HomeCareEntitledPeriods.Any(f => f.StartDate < subReport.MainReport.End && (f.EndDate == null || f.EndDate > subReport.MainReport.Start))) ? "Eligibility period does not permit reporting. " : "") + (!(client.FunctionalityScores.Any(f => f.StartDate < subReport.MainReport.End)) ? "Functionality Scores period does not permit reporting. " : "") ) }); return(source); }
/// <summary> /// Query system users and order by account_id asc /// </summary> /// <param name="search">search model</param> /// <param name="page">page info</param> /// <param name="count">number of total records</param> /// <returns></returns> public IQueryable <SystemOrgDTO> QueryOrgs(OrgModelSearch search, Page page, out int count) { var query = from Org in DataContext.System_Organization.Include("System_Users") select new SystemOrgDTO { Organization_UID = Org.Organization_UID, Organization_ID = Org.Organization_ID, Organization_Name = Org.Organization_Name, Organization_Desc = Org.Organization_Desc, Begin_Date = Org.Begin_Date, End_Date = Org.End_Date, OrgManager_Name = Org.OrgManager_Name, OrgManager_Tel = Org.OrgManager_Tel, OrgManager_Email = Org.OrgManager_Email, Cost_Center = Org.Cost_Center, Modified_Date = Org.Modified_Date, Modified_UserName = Org.System_Users.User_Name, Modified_UserNTID = Org.System_Users.User_NTID }; if (string.IsNullOrEmpty(search.ExportUIds)) { #region Query_Types if (search.query_types != null && search.Reference_Date != null) { EnumValidity queryType = (EnumValidity)Enum.ToObject(typeof(EnumValidity), search.query_types); switch (queryType) { case EnumValidity.Valid: query = query.Where(p => p.Begin_Date <= search.Reference_Date && (p.End_Date >= search.Reference_Date || p.End_Date == null)); break; case EnumValidity.Invalid: query = query.Where(p => p.Begin_Date > search.Reference_Date || (p.End_Date < search.Reference_Date && p.End_Date != null)); break; default: break; } } #endregion #region Modified_Date if (search.Modified_Date_From != null) { query = query.Where(m => SqlFunctions.DateDiff("dd", m.Modified_Date, search.Modified_Date_From) <= 0); } if (search.Modified_Date_End != null) { query = query.Where(m => SqlFunctions.DateDiff("dd", m.Modified_Date, search.Modified_Date_End) >= 0); } #endregion if (!string.IsNullOrWhiteSpace(search.Organization_ID)) { query = query.Where(p => p.Organization_ID == search.Organization_ID); } if (!string.IsNullOrWhiteSpace(search.Organization_Name)) { query = query.Where(p => p.Organization_Name.Contains(search.Organization_Name)); } if (!string.IsNullOrWhiteSpace(search.Organization_Desc)) { query = query.Where(p => p.Organization_Desc.Contains(search.Organization_Desc)); } if (!string.IsNullOrWhiteSpace(search.OrgManager_Name)) { query = query.Where(p => p.OrgManager_Name.Contains(search.OrgManager_Name)); } if (!string.IsNullOrWhiteSpace(search.Cost_Center)) { query = query.Where(p => p.Cost_Center == search.Cost_Center); } if (!string.IsNullOrWhiteSpace(search.Modified_By_NTID)) { query = query.Where(q => q.Modified_UserNTID == search.Modified_By_NTID); } count = query.Count(); return(query.OrderBy(o => o.Organization_ID).GetPage(page)); } else { //for export data var array = Array.ConvertAll(search.ExportUIds.Split(','), s => int.Parse(s)); query = query.Where(p => array.Contains(p.Organization_UID)); count = 0; return(query.OrderBy(o => o.Organization_ID)); } }
public OrderAuditPlanQuery Withfilter(IEnumerable <filterRule> filters) { if (filters != null) { foreach (var rule in filters) { if (rule.field == "Id") { int val = Convert.ToInt32(rule.value); And(x => x.Id == val); } if (rule.field == "OrderKey") { And(x => x.OrderKey.Contains(rule.value)); } if (rule.field == "AuditContent") { And(x => x.AuditContent.Contains(rule.value)); } if (rule.field == "Department") { And(x => x.Department.Contains(rule.value)); } if (rule.field == "AuditResult") { And(x => x.AuditResult.Contains(rule.value)); } if (rule.field == "Status") { int val = Convert.ToInt32(rule.value); And(x => x.Status == val); } if (rule.field == "PlanAuditDate") { var date = Convert.ToDateTime(rule.value); And(x => SqlFunctions.DateDiff("d", date, x.PlanAuditDate) >= 0); } if (rule.field == "AuditDate") { var date = Convert.ToDateTime(rule.value); And(x => SqlFunctions.DateDiff("d", date, x.AuditDate) >= 0); } if (rule.field == "AuditUser") { And(x => x.AuditUser.Contains(rule.value)); } if (rule.field == "Remark") { And(x => x.Remark.Contains(rule.value)); } if (rule.field == "OrderId") { int val = Convert.ToInt32(rule.value); And(x => x.OrderId == val); } } } return(this); }
public List <SalesMonthModel> DashboardSales() { var sqlMinDate = (DateTime)SqlDateTime.MinValue; List <SalesMonthModel> sales = db.Orders .GroupBy(x => SqlFunctions.DateAdd("month", SqlFunctions.DateDiff("month", sqlMinDate, x.orderDate), sqlMinDate)) .Select(x => new SalesMonthModel() { period = x.Key.ToString().Substring(0, 7), quantity = x.Sum(i => i.itemCount), total = x.Sum(i => i.totalPrice) } ).ToList(); return(sales); }
public int GetMessageCount(Guid referenceId) { ISpecification <G_Activity> spec = Specification <G_Activity> .Eval(item => true); spec = new AndSpecification <G_Activity>(spec, Specification <G_Activity> .Eval(item => referenceId == null || item.ReferenceId.Equals(referenceId))); spec = new AndSpecification <G_Activity>(spec, Specification <G_Activity> .Eval(item => !item.IsRead)); spec = new AndSpecification <G_Activity>(spec, Specification <G_Activity> .Eval(item => SqlFunctions.DateDiff("day", item.CreatedDate, SqlFunctions.GetDate()) <= 7)); return(this._IG_ActivityRepository.GetAll(1, 1, spec).Data.Count()); }
public void DoWork() { string creator = "system", creatorName = "Система"; DateTime date = DateTime.Now.Date; //List<ProjectTask> tasksToActivate = db.ProjectTasks.Where(val => val.StatusID < TaskStatusesEnum.Active && val.DateBegin <= date && (!val.PreviousID.HasValue || val.PreviousTask.StatusID == TaskStatusesEnum.Completed)).ToList(); //foreach (ProjectTask task in tasksToActivate) //{ // bool send = task.StatusID == TaskStatusesEnum.Created; // task.StatusID = TaskStatusesEnum.Active; // db.SaveChanges(); // if (send) // task.SendStatusNotification(); //} //List<ProjectTask> tasksToDeactivate = db.ProjectTasks.Where(val => val.StatusID == TaskStatusesEnum.Active && (val.DateBegin > date || val.PreviousID.HasValue && val.PreviousTask.StatusID != TaskStatusesEnum.Completed)).ToList(); //foreach (ProjectTask task in tasksToDeactivate) //{ // task.StatusID = TaskStatusesEnum.Created; // db.SaveChanges(); //} List <ProjectTask> tasksOverdue = db.ProjectTasks.Where(val => val.StatusID == TaskStatusesEnum.ToDo && SqlFunctions.DateDiff("DAY", val.DateEndPlan, date) == 1 && !val.Project.Deleted && !val.Messages.Any(m => SqlFunctions.DateDiff("DAY", date, m.CreateDate) == 0 && m.SysText == taskOverdueKey)).ToList(); foreach (ProjectTask task in tasksOverdue) { task.SendNotification("ProjectTask_Overdue"); ProjectTaskMessage m = new ProjectTaskMessage() { CreateDate = DateTime.Now, Creator = creator, CreatorName = creatorName, SysText = taskOverdueKey, TaskID = task.ID, Text = Settings.GetValue(taskOverdueKey + "Text") }; db.ProjectTaskMessages.AddObject(m); db.SaveChanges(); } List <Employee> employees = db.Employees.Where(val => val.SalaryExpenses.Any(se => se.ChangeDate > date || se.CreateDate > date) || val.Payrolls.Any(se => se.ChangeDate > date || se.CreateDate > date)).ToList(); foreach (Employee emp in employees) { emp.UpdateSalaryBalance(); } db.SaveChanges(); }
public AttachmentQuery Withfilter(IEnumerable <filterRule> filters) { if (filters != null) { foreach (var rule in filters) { if (rule.field == "Id" && !string.IsNullOrEmpty(rule.value) && rule.value.IsInt()) { var val = Convert.ToInt32(rule.value); switch (rule.op) { case "equal": And(x => x.Id == val); break; case "notequal": And(x => x.Id != val); break; case "less": And(x => x.Id < val); break; case "lessorequal": And(x => x.Id <= val); break; case "greater": And(x => x.Id > val); break; case "greaterorequal": And(x => x.Id >= val); break; default: And(x => x.Id == val); break; } } if (rule.field == "FileName" && !string.IsNullOrEmpty(rule.value)) { And(x => x.FileName.Contains(rule.value)); } if (rule.field == "FileId" && !string.IsNullOrEmpty(rule.value)) { And(x => x.FileId.Contains(rule.value)); } if (rule.field == "Ext" && !string.IsNullOrEmpty(rule.value)) { And(x => x.Ext.Contains(rule.value)); } if (rule.field == "FilePath" && !string.IsNullOrEmpty(rule.value)) { And(x => x.FilePath.Contains(rule.value)); } if (rule.field == "RefKey" && !string.IsNullOrEmpty(rule.value)) { And(x => x.RefKey.Contains(rule.value)); } if (rule.field == "Owner" && !string.IsNullOrEmpty(rule.value)) { And(x => x.Owner.Contains(rule.value)); } if (rule.field == "Upload" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "between") { var datearray = rule.value.Split(new char[] { '-' }); var start = Convert.ToDateTime(datearray[0]); var end = Convert.ToDateTime(datearray[1]); And(x => SqlFunctions.DateDiff("d", start, x.Upload) >= 0); And(x => SqlFunctions.DateDiff("d", end, x.Upload) <= 0); } } if (rule.field == "CreatedDate" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "between") { var datearray = rule.value.Split(new char[] { '-' }); var start = Convert.ToDateTime(datearray[0]); var end = Convert.ToDateTime(datearray[1]); And(x => SqlFunctions.DateDiff("d", start, x.CreatedDate) >= 0); And(x => SqlFunctions.DateDiff("d", end, x.CreatedDate) <= 0); } } if (rule.field == "CreatedBy" && !string.IsNullOrEmpty(rule.value)) { And(x => x.CreatedBy.Contains(rule.value)); } if (rule.field == "LastModifiedDate" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "between") { var datearray = rule.value.Split(new char[] { '-' }); var start = Convert.ToDateTime(datearray[0]); var end = Convert.ToDateTime(datearray[1]); And(x => SqlFunctions.DateDiff("d", start, x.LastModifiedDate) >= 0); And(x => SqlFunctions.DateDiff("d", end, x.LastModifiedDate) <= 0); } } if (rule.field == "LastModifiedBy" && !string.IsNullOrEmpty(rule.value)) { And(x => x.LastModifiedBy.Contains(rule.value)); } if (rule.field == "TenantId" && !string.IsNullOrEmpty(rule.value) && rule.value.IsInt()) { var val = Convert.ToInt32(rule.value); switch (rule.op) { case "equal": And(x => x.TenantId == val); break; case "notequal": And(x => x.TenantId != val); break; case "less": And(x => x.TenantId < val); break; case "lessorequal": And(x => x.TenantId <= val); break; case "greater": And(x => x.TenantId > val); break; case "greaterorequal": And(x => x.TenantId >= val); break; default: And(x => x.TenantId == val); break; } } } } return(this); }
public static void Init(Assembly asm) { var instance = new ULinq(); //var asm = linqToSqlClassType.Assembly; EntityRefType = asm.GetType(StrEntityRefType); EntitySetType = asm.GetType(StrEntitySetType); BinaryType = asm.GetType(StrBinaryType); BinaryCtor = BinaryType.GetConstructor(new Type[] { typeof(byte[]) }).GetCreator(); SqlType.TypeMap[BinaryType.TypeHandle.Value.GetHashCode()] = DBType.Binary; instance.Table = new TableAttribute(); instance.Table.Type = asm.GetType(ULinq.StrTableAttributeType); instance.Table.Name = instance.Table.Type.GetProperty("Name").GetGetter(); instance.Column = new ColumnAttribute(); instance.Column.Type = asm.GetType(ULinq.StrColumnAttributeType); instance.Column.Name = instance.Column.Type.GetProperty("Name").GetGetter(); instance.Column.CanBeNull = instance.Column.Type.GetProperty("CanBeNull").GetGetter(); instance.Column.IsDbGenerated = instance.Column.Type.GetProperty("IsDbGenerated").GetGetter(); instance.Column.IsPrimaryKey = instance.Column.Type.GetProperty("IsPrimaryKey").GetGetter(); instance.Column.IsVersion = instance.Column.Type.GetProperty("IsVersion").GetGetter(); instance.Column.AutoSync = instance.Column.Type.GetProperty("AutoSync").GetGetter(); instance.Column.DbType = instance.Column.Type.GetProperty("DbType").GetGetter(); instance.Column.Expression = instance.Column.Type.GetProperty("Expression").GetGetter(); instance.Column.IsDiscriminator = instance.Column.Type.GetProperty("IsDiscriminator").GetGetter(); instance.Column.Storage = instance.Column.Type.GetProperty("Storage").GetGetter(); instance.Column.UpdateCheck = instance.Column.Type.GetProperty("UpdateCheck").GetGetter(); instance.Association = new AssociationAttribute(); instance.Association.Type = asm.GetType(ULinq.StrAssociationAttributeType); instance.Association.ThisKey = instance.Association.Type.GetProperty("ThisKey").GetGetter(); instance.Association.OtherKey = instance.Association.Type.GetProperty("OtherKey").GetGetter(); instance.Association.IsForeignKey = instance.Association.Type.GetProperty("IsForeignKey").GetGetter(); var sqlMethodsType = asm.GetType(ULinq.StrSqlMethhodsType); //var flags = BindingFlags.Public | BindingFlags.Static; var dateDiffDay = sqlMethodsType.GetMethod("DateDiffDay", new Type[] { typeof(DateTime), typeof(DateTime) }); MethodMapping.Mappings[dateDiffDay] = MethodMapping.Lambda <DateTime, DateTime, int>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Day, startTime, endTime)); var dateDiffHour = sqlMethodsType.GetMethod("DateDiffHour", new Type[] { typeof(DateTime), typeof(DateTime) }); MethodMapping.Mappings[dateDiffHour] = MethodMapping.Lambda <DateTime, DateTime, int>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Hour, startTime, endTime)); var dateDiffMicrosecond = sqlMethodsType.GetMethod("DateDiffMicrosecond", new Type[] { typeof(DateTime), typeof(DateTime) }); MethodMapping.Mappings[dateDiffMicrosecond] = MethodMapping.Lambda <DateTime, DateTime, int>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Microsecond, startTime, endTime)); var dateDiffMillisecond = sqlMethodsType.GetMethod("DateDiffMillisecond", new Type[] { typeof(DateTime), typeof(DateTime) }); MethodMapping.Mappings[dateDiffMillisecond] = MethodMapping.Lambda <DateTime, DateTime, int>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Millisecond, startTime, endTime)); var dateDiffMinute = sqlMethodsType.GetMethod("DateDiffMinute", new Type[] { typeof(DateTime), typeof(DateTime) }); MethodMapping.Mappings[dateDiffMinute] = MethodMapping.Lambda <DateTime, DateTime, int>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Minute, startTime, endTime)); var dateDiffMonth = sqlMethodsType.GetMethod("DateDiffMonth", new Type[] { typeof(DateTime), typeof(DateTime) }); MethodMapping.Mappings[dateDiffMonth] = MethodMapping.Lambda <DateTime, DateTime, int>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Month, startTime, endTime)); var dateDiffNanosecond = sqlMethodsType.GetMethod("DateDiffNanosecond", new Type[] { typeof(DateTime), typeof(DateTime) }); MethodMapping.Mappings[dateDiffNanosecond] = MethodMapping.Lambda <DateTime, DateTime, int>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Nanosecond, startTime, endTime)); var dateDiffSecond = sqlMethodsType.GetMethod("DateDiffSecond", new Type[] { typeof(DateTime), typeof(DateTime) }); MethodMapping.Mappings[dateDiffSecond] = MethodMapping.Lambda <DateTime, DateTime, int>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Second, startTime, endTime)); var dateDiffYear = sqlMethodsType.GetMethod("DateDiffYear", new Type[] { typeof(DateTime), typeof(DateTime) }); MethodMapping.Mappings[dateDiffYear] = MethodMapping.Lambda <DateTime, DateTime, int>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Year, startTime, endTime)); // var dateDiffDayForNullable = sqlMethodsType.GetMethod("DateDiffDay", new Type[] { typeof(DateTime?), typeof(DateTime?) }); MethodMapping.Mappings[dateDiffDayForNullable] = MethodMapping.Lambda <DateTime?, DateTime?, int?>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Day, startTime, endTime)); var dateDiffHourForNullable = sqlMethodsType.GetMethod("DateDiffHour", new Type[] { typeof(DateTime?), typeof(DateTime?) }); MethodMapping.Mappings[dateDiffHourForNullable] = MethodMapping.Lambda <DateTime?, DateTime?, int?>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Hour, startTime, endTime)); var dateDiffMicrosecondForNullable = sqlMethodsType.GetMethod("DateDiffMicrosecond", new Type[] { typeof(DateTime?), typeof(DateTime?) }); MethodMapping.Mappings[dateDiffMicrosecondForNullable] = MethodMapping.Lambda <DateTime?, DateTime?, int?>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Microsecond, startTime, endTime)); var dateDiffMillisecondForNullable = sqlMethodsType.GetMethod("DateDiffMillisecond", new Type[] { typeof(DateTime?), typeof(DateTime?) }); MethodMapping.Mappings[dateDiffMillisecondForNullable] = MethodMapping.Lambda <DateTime?, DateTime?, int?>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Millisecond, startTime, endTime)); var dateDiffMinuteForNullable = sqlMethodsType.GetMethod("DateDiffMinute", new Type[] { typeof(DateTime?), typeof(DateTime?) }); MethodMapping.Mappings[dateDiffMinuteForNullable] = MethodMapping.Lambda <DateTime?, DateTime?, int?>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Minute, startTime, endTime)); var dateDiffMonthForNullable = sqlMethodsType.GetMethod("DateDiffMonth", new Type[] { typeof(DateTime?), typeof(DateTime?) }); MethodMapping.Mappings[dateDiffMonthForNullable] = MethodMapping.Lambda <DateTime?, DateTime?, int?>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Month, startTime, endTime)); var dateDiffNanosecondForNullable = sqlMethodsType.GetMethod("DateDiffNanosecond", new Type[] { typeof(DateTime?), typeof(DateTime?) }); MethodMapping.Mappings[dateDiffNanosecondForNullable] = MethodMapping.Lambda <DateTime?, DateTime?, int?>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Nanosecond, startTime, endTime)); var dateDiffSecondForNullable = sqlMethodsType.GetMethod("DateDiffSecond", new Type[] { typeof(DateTime?), typeof(DateTime?) }); MethodMapping.Mappings[dateDiffSecondForNullable] = MethodMapping.Lambda <DateTime?, DateTime?, int?>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Second, startTime, endTime)); var dateDiffYearForNullable = sqlMethodsType.GetMethod("DateDiffYear", new Type[] { typeof(DateTime?), typeof(DateTime?) }); MethodMapping.Mappings[dateDiffYearForNullable] = MethodMapping.Lambda <DateTime?, DateTime?, int?>((startTime, endTime) => SqlFunctions.DateDiff(DateParts.Year, startTime, endTime)); Instance = instance; }
public static Expression <Func <Booking, bool> > IsNotCancelled() { return(booking => !booking.TerminatedDate.HasValue || SqlFunctions.DateDiff("day", booking.TerminatedDate, booking.Dates.Start) >= 2d); }
public dynamic obterMaisAntigosOrdenado() { var reservas = Contexto.Reservas.Where(x => x.DataEntregaRealizada == null && x.DataEntregaPrevista < DateTime.Now).OrderByDescending(x => SqlFunctions.DateDiff("dd", x.DataEntregaPrevista, DateTime.Now)) .Include("Cliente").Include("Festa").Include("Pacote").Include("Opcionais") .Select(x => new { NomeFesta = x.Festa.NomeFesta, NomeCliente = x.Cliente.NomeCliente, NomePacote = x.Pacote.NomePacote, NumeroDiasAtrasado = SqlFunctions.DateDiff("dd", x.DataEntregaPrevista, DateTime.Now) }) .ToList(); return(reservas); }
/// <summary> /// Executes the specified context. /// </summary> /// <param name="context">The context.</param> public void Execute(IJobExecutionContext context) { var rockContext = new RockContext(); var groupRequirementService = new GroupRequirementService(rockContext); var groupMemberRequirementService = new GroupMemberRequirementService(rockContext); var groupMemberService = new GroupMemberService(rockContext); // we only need to consider group requirements that are based on a DataView or SQL var groupRequirementQry = groupRequirementService.Queryable() .Where(a => a.GroupRequirementType.RequirementCheckType != RequirementCheckType.Manual) .AsNoTracking(); var calculationExceptions = new List <Exception>(); int groupRequirementsCalculatedMemberCount = 0; foreach (var groupRequirement in groupRequirementQry.Include(i => i.GroupRequirementType).AsNoTracking().ToList()) { try { var currentDateTime = RockDateTime.Now; var qryGroupMemberRequirementsAlreadyOK = groupMemberRequirementService.Queryable().Where(a => a.GroupRequirementId == groupRequirement.Id); if (groupRequirement.GroupRequirementType.CanExpire && groupRequirement.GroupRequirementType.ExpireInDays.HasValue) { // Expirable: don't recalculate members that already met the requirement within the expiredays (unless they are flagged with a warning) var expireDaysCount = groupRequirement.GroupRequirementType.ExpireInDays.Value; qryGroupMemberRequirementsAlreadyOK = qryGroupMemberRequirementsAlreadyOK.Where(a => !a.RequirementWarningDateTime.HasValue && a.RequirementMetDateTime.HasValue && SqlFunctions.DateDiff("day", a.RequirementMetDateTime, currentDateTime) < expireDaysCount); } else { // No Expiration: don't recalculate members that already met the requirement qryGroupMemberRequirementsAlreadyOK = qryGroupMemberRequirementsAlreadyOK.Where(a => a.RequirementMetDateTime.HasValue); } var groupMemberQry = groupMemberService.Queryable().Where(a => a.GroupId == groupRequirement.GroupId).AsNoTracking(); var personQry = groupMemberQry.Where(a => !qryGroupMemberRequirementsAlreadyOK.Any(r => r.GroupMemberId == a.Id)).Select(a => a.Person); var results = groupRequirement.PersonQueryableMeetsGroupRequirement(rockContext, personQry, groupRequirement.GroupRoleId).ToList(); groupRequirementsCalculatedMemberCount += results.Select(a => a.PersonId).Distinct().Count(); foreach (var result in results) { // use a fresh rockContext per Update so that ChangeTracker doesn't get bogged down var rockContextUpdate = new RockContext(); groupRequirement.UpdateGroupMemberRequirementResult(rockContextUpdate, result.PersonId, result.MeetsGroupRequirement); rockContextUpdate.SaveChanges(); } } catch (Exception ex) { calculationExceptions.Add(new Exception(string.Format("Exception when calculating group requirement: {0} ", groupRequirement), ex)); } } context.Result = string.Format("Group member requirements re-calculated for {0} group members", groupRequirementsCalculatedMemberCount); if (calculationExceptions.Any()) { throw new AggregateException("One or more group requirement calculations failed ", calculationExceptions); } }
public JsonResult BuscarImprontasPaginadosSeguimiento() { int idsuario = Convert.ToInt32(Session["user_usuarioid"]); var query = (from vehiculo in context.icb_vehiculo join bodega in context.bodega_concesionario on vehiculo.id_bod equals bodega.id join usuario in context.users on idsuario equals usuario.user_id join bodUsuario in context.bodega_usuario on usuario.user_id equals bodUsuario.id_usuario join modelo in context.modelo_vehiculo on vehiculo.modvh_id equals modelo.modvh_codigo join tpEvento in context.icb_tpeventos on vehiculo.id_evento equals tpEvento.tpevento_id where vehiculo.icbvh_estatus == "6" && vehiculo.icbvh_fec_impronta != null && vehiculo.icbvh_fec_envioimpronta != null && bodUsuario.id_bodega == vehiculo.id_bod select new { vehiculo.icbvh_id, vehiculo.vin, modelo.modvh_nombre, bodega.bodccs_nombre, vehiculo.icbvh_estatus, vehiculo.icbvh_fecinsp_ingreso, vehiculo.icbvh_fec_impronta, vehiculo.icbvh_fec_envioimpronta, vehiculo.icbvh_fec_recepcionimpronta, tiempo_tomaImpronta = SqlFunctions.DateDiff("hour", vehiculo.icbvh_fecinsp_ingreso, vehiculo.icbvh_fec_impronta), tiempo_envioImpronta = SqlFunctions.DateDiff("hour", vehiculo.icbvh_fec_impronta, vehiculo.icbvh_fec_envioimpronta), tiempo_recepcionImpronta = SqlFunctions.DateDiff("hour", vehiculo.icbvh_fec_envioimpronta, vehiculo.icbvh_fec_recepcionimpronta) }).ToList(); var data = query.Select(x => new { x.icbvh_id, x.vin, x.modvh_nombre, x.bodccs_nombre, x.icbvh_estatus, icbvh_fecinsp_ingreso = x.icbvh_fecinsp_ingreso != null ? x.icbvh_fecinsp_ingreso.Value.ToShortDateString() + " " + x.icbvh_fecinsp_ingreso.Value.ToShortTimeString() : "", min_tomaImpronta = x.tiempo_tomaImpronta > 24 ? x.tiempo_tomaImpronta / 24 : x.tiempo_tomaImpronta, valorTiempo_tomaImpronta = x.tiempo_tomaImpronta > 24 ? "Dias" : x.tiempo_tomaImpronta != null ? "Horas" : " ", icbvh_fec_impronta = x.icbvh_fec_impronta != null ? x.icbvh_fec_impronta.Value.ToShortDateString() + " " + x.icbvh_fec_impronta.Value.ToShortTimeString() : "", min_envioImpronta = x.tiempo_envioImpronta > 24 ? x.tiempo_envioImpronta / 24 : x.tiempo_envioImpronta, valorTiempo_envioImpronta = x.tiempo_envioImpronta > 24 ? "Dias" : x.tiempo_envioImpronta != null ? "Horas" : " ", icbvh_fec_envioimpronta = x.icbvh_fec_envioimpronta != null ? x.icbvh_fec_envioimpronta.Value.ToShortDateString() + " " + x.icbvh_fec_envioimpronta.Value.ToShortTimeString() : "", min_recepcionImpronta = x.tiempo_recepcionImpronta > 24 ? x.tiempo_recepcionImpronta / 24 : x.tiempo_recepcionImpronta, valorTiempo_recepcionImpronta = x.tiempo_recepcionImpronta > 24 ? "Dias" : x.tiempo_recepcionImpronta != null ? "Horas" : " ", icbvh_fec_recepcionimpronta = x.icbvh_fec_recepcionimpronta != null ? x.icbvh_fec_recepcionimpronta.Value.ToShortDateString() + " " + x.icbvh_fec_recepcionimpronta.Value.ToShortTimeString() : "" }); return(Json(data, JsonRequestBehavior.AllowGet)); }
public StockQuery ByBookIdWithfilter(int bookid, IEnumerable <filterRule> filters) { And(x => x.BookId == bookid); if (filters != null) { foreach (var rule in filters) { if (rule.field == "Id" && !string.IsNullOrEmpty(rule.value) && rule.value.IsInt()) { var val = Convert.ToInt32(rule.value); switch (rule.op) { case "equal": And(x => x.Id == val); break; case "notequal": And(x => x.Id != val); break; case "less": And(x => x.Id < val); break; case "lessorequal": And(x => x.Id <= val); break; case "greater": And(x => x.Id > val); break; case "greaterorequal": And(x => x.Id >= val); break; default: And(x => x.Id == val); break; } } if (rule.field == "BookId" && !string.IsNullOrEmpty(rule.value) && rule.value.IsInt()) { var val = Convert.ToInt32(rule.value); switch (rule.op) { case "equal": And(x => x.BookId == val); break; case "notequal": And(x => x.BookId != val); break; case "less": And(x => x.BookId < val); break; case "lessorequal": And(x => x.BookId <= val); break; case "greater": And(x => x.BookId > val); break; case "greaterorequal": And(x => x.BookId >= val); break; default: And(x => x.BookId == val); break; } } if (rule.field == "BarCode" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "equal") { And(x => x.BarCode == rule.value); } else { And(x => x.BarCode.Contains(rule.value)); } } if (rule.field == "ISBN" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "equal") { And(x => x.ISBN == rule.value); } else { And(x => x.ISBN.Contains(rule.value)); } } if (rule.field == "Title" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "equal") { And(x => x.Title == rule.value); } else { And(x => x.Title.Contains(rule.value)); } } if (rule.field == "Remark" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "equal") { And(x => x.Remark == rule.value); } else { And(x => x.Remark.Contains(rule.value)); } } if (rule.field == "Qty" && !string.IsNullOrEmpty(rule.value) && rule.value.IsInt()) { var val = Convert.ToInt32(rule.value); switch (rule.op) { case "equal": And(x => x.Qty == val); break; case "notequal": And(x => x.Qty != val); break; case "less": And(x => x.Qty < val); break; case "lessorequal": And(x => x.Qty <= val); break; case "greater": And(x => x.Qty > val); break; case "greaterorequal": And(x => x.Qty >= val); break; default: And(x => x.Qty == val); break; } } if (rule.field == "Price" && !string.IsNullOrEmpty(rule.value) && rule.value.IsDecimal()) { var val = Convert.ToDecimal(rule.value); switch (rule.op) { case "equal": And(x => x.Price == val); break; case "notequal": And(x => x.Price != val); break; case "less": And(x => x.Price < val); break; case "lessorequal": And(x => x.Price <= val); break; case "greater": And(x => x.Price > val); break; case "greaterorequal": And(x => x.Price >= val); break; default: And(x => x.Price == val); break; } } if (rule.field == "PurchasingPrice" && !string.IsNullOrEmpty(rule.value) && rule.value.IsDecimal()) { var val = Convert.ToDecimal(rule.value); switch (rule.op) { case "equal": And(x => x.PurchasingPrice == val); break; case "notequal": And(x => x.PurchasingPrice != val); break; case "less": And(x => x.PurchasingPrice < val); break; case "lessorequal": And(x => x.PurchasingPrice <= val); break; case "greater": And(x => x.PurchasingPrice > val); break; case "greaterorequal": And(x => x.PurchasingPrice >= val); break; default: And(x => x.PurchasingPrice == val); break; } } if (rule.field == "PurchaseDate" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "between") { var datearray = rule.value.Split(new char[] { '-' }); var start = Convert.ToDateTime(datearray[0]); var end = Convert.ToDateTime(datearray[1]); And(x => SqlFunctions.DateDiff("d", start, x.PurchaseDate) >= 0); And(x => SqlFunctions.DateDiff("d", end, x.PurchaseDate) <= 0); } } if (rule.field == "UserName" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "equal") { And(x => x.UserName == rule.value); } else { And(x => x.UserName.Contains(rule.value)); } } if (rule.field == "Catetory" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "equal") { And(x => x.Catetory == rule.value); } else { And(x => x.Catetory.Contains(rule.value)); } } if (rule.field == "Location" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "equal") { And(x => x.Location == rule.value); } else { And(x => x.Location.Contains(rule.value)); } } if (rule.field == "Shelves" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "equal") { And(x => x.Shelves == rule.value); } else { And(x => x.Shelves.Contains(rule.value)); } } if (rule.field == "Tags" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "equal") { And(x => x.Tags == rule.value); } else { And(x => x.Tags.Contains(rule.value)); } } if (rule.field == "Status" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "equal") { And(x => x.Status == rule.value); } else { And(x => x.Status.Contains(rule.value)); } } if (rule.field == "Trades" && !string.IsNullOrEmpty(rule.value) && rule.value.IsInt()) { var val = Convert.ToInt32(rule.value); switch (rule.op) { case "equal": And(x => x.Trades == val); break; case "notequal": And(x => x.Trades != val); break; case "less": And(x => x.Trades < val); break; case "lessorequal": And(x => x.Trades <= val); break; case "greater": And(x => x.Trades > val); break; case "greaterorequal": And(x => x.Trades >= val); break; default: And(x => x.Trades == val); break; } } if (rule.field == "InputDate" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "between") { var datearray = rule.value.Split(new char[] { '-' }); var start = Convert.ToDateTime(datearray[0]); var end = Convert.ToDateTime(datearray[1]); And(x => SqlFunctions.DateDiff("d", start, x.InputDate) >= 0); And(x => SqlFunctions.DateDiff("d", end, x.InputDate) <= 0); } } } } return(this); }
public int[] GetMissOrder(string userId, DateTime dateTime, string shopNo, int takeType) { Expression <Func <OrderEntity, bool> > expression = a => a.UserId.Equals(userId) && SqlFunctions.DateDiff("d", dateTime, a.OptTime) == 0 && a.ShopNo.Equals(shopNo) && a.TakeType.Equals(takeType); if (takeType == 1) { expression = a => a.UserId.Equals(userId) && (SqlFunctions.DateDiff("d", dateTime, a.OptTime) == (int?)0 && SqlFunctions.DateDiff("d", a.DeliverTime, "1970-01-01") == 0 || SqlFunctions.DateDiff("d", dateTime, a.DeliverTime) == 0) && a.ShopNo.Equals(shopNo) && a.TakeType.Equals(takeType); } using (var db = new O2OContext()) { var baseService = new BaseService <OrderEntity>(db); var data = db.Order.Where(expression).GroupBy(a => new { ShopNo = a.ShopNo }).Select(g => new { MaxDaySeq = g.Max(a => a.DaySeq), Count = g.Count() }).FirstOrDefault(); var numArray = new int[0]; if (data == null || data.MaxDaySeq == data.Count) { return(numArray); } var arrayDaySeq = baseService.Where(expression).Select(a => a.DaySeq).ToArray(); numArray = Enumerable.Range(1, data.MaxDaySeq).Where(a => !arrayDaySeq.Contains(a)).ToArray(); return(numArray); } }
/// <summary> /// Executes the specified context. /// </summary> /// <param name="context">The context.</param> public void Execute(IJobExecutionContext context) { var rockContext = new RockContext(); var groupRequirementService = new GroupRequirementService(rockContext); var groupMemberRequirementService = new GroupMemberRequirementService(rockContext); var groupMemberService = new GroupMemberService(rockContext); var groupService = new GroupService(rockContext); // we only need to consider group requirements that are based on a DataView or SQL var groupRequirementQry = groupRequirementService.Queryable() .Where(a => a.GroupRequirementType.RequirementCheckType != RequirementCheckType.Manual) .AsNoTracking(); var calculationExceptions = new List <Exception>(); List <int> groupRequirementsCalculatedPersonIds = new List <int>(); foreach (var groupRequirement in groupRequirementQry.Include(i => i.GroupRequirementType).Include(a => a.GroupRequirementType.DataView).Include(a => a.GroupRequirementType.WarningDataView).AsNoTracking().ToList()) { // Only calculate group requirements for Active groups (if an inactive group becomes active again, this job will take care of re-calculating the requirements again) var groupQuery = groupService.Queryable().Where(a => a.IsActive); if (groupRequirement.GroupId.HasValue) { groupQuery = groupQuery.Where(g => g.Id == groupRequirement.GroupId); } else if (groupRequirement.GroupTypeId.HasValue) { groupQuery = groupQuery.Where(g => g.GroupTypeId == groupRequirement.GroupTypeId); } else { // shouldn't happen, but Group Requirement doesn't have a groupId or a GroupTypeId break; } var groupList = groupQuery.Select(a => new { a.Id, a.Name }).ToList(); var groupCount = groupList.Count(); foreach (var group in groupList) { context.UpdateLastStatusMessage($"Calculating group requirement '{groupRequirement.GroupRequirementType.Name}' for {group.Name}"); try { var currentDateTime = RockDateTime.Now; var qryGroupMemberRequirementsAlreadyOK = groupMemberRequirementService.Queryable().Where(a => a.GroupRequirementId == groupRequirement.Id && a.GroupMember.GroupId == group.Id); if (groupRequirement.GroupRequirementType.CanExpire && groupRequirement.GroupRequirementType.ExpireInDays.HasValue) { // Group requirement can expire: don't recalculate members that already met the requirement within the expire days (unless they are flagged with a warning) var expireDaysCount = groupRequirement.GroupRequirementType.ExpireInDays.Value; qryGroupMemberRequirementsAlreadyOK = qryGroupMemberRequirementsAlreadyOK.Where(a => !a.RequirementWarningDateTime.HasValue && a.RequirementMetDateTime.HasValue && SqlFunctions.DateDiff("day", a.RequirementMetDateTime, currentDateTime) < expireDaysCount); } else { // No Expiration: don't recalculate members that already met the requirement qryGroupMemberRequirementsAlreadyOK = qryGroupMemberRequirementsAlreadyOK.Where(a => a.RequirementMetDateTime.HasValue); } var groupMemberQry = groupMemberService.Queryable(); if (groupRequirement.GroupId.HasValue) { groupMemberQry = groupMemberQry.Where(g => g.GroupId == groupRequirement.GroupId); } else if (groupRequirement.GroupTypeId.HasValue) { groupMemberQry = groupMemberQry.Where(g => (g.Group.GroupTypeId == groupRequirement.GroupTypeId) && g.GroupId == group.Id); } else { // shouldn't happen, but Group Requirement doesn't have a groupId or a GroupTypeId break; } var personQry = groupMemberQry.Where(a => !qryGroupMemberRequirementsAlreadyOK.Any(r => r.GroupMemberId == a.Id)).Select(a => a.Person); var results = groupRequirement.PersonQueryableMeetsGroupRequirement(rockContext, personQry, group.Id, groupRequirement.GroupRoleId).ToList(); groupRequirementsCalculatedPersonIds.AddRange(results.Select(a => a.PersonId).Distinct()); foreach (var result in results) { try { // use a fresh rockContext per result so that ChangeTracker doesn't get bogged down using (var rockContextUpdate = new RockContext()) { groupRequirement.UpdateGroupMemberRequirementResult(rockContextUpdate, result.PersonId, group.Id, result.MeetsGroupRequirement); rockContextUpdate.SaveChanges(); } } catch (Exception ex) { calculationExceptions.Add(new Exception($"Exception when updating group requirement result: {groupRequirement} for person.Id { result.PersonId }", ex)); } } } catch (Exception ex) { calculationExceptions.Add(new Exception(string.Format("Exception when calculating group requirement: {0} ", groupRequirement), ex)); } } } context.UpdateLastStatusMessage($"{groupRequirementQry.Count()} group member requirements re-calculated for {groupRequirementsCalculatedPersonIds.Distinct().Count()} people"); if (calculationExceptions.Any()) { throw new AggregateException("One or more group requirement calculations failed ", calculationExceptions); } }
public async Task <long> GetUserForwardStatisticalAsync(long userId, DateTime?dateTime) { using (MyDbContext dbc = new MyDbContext()) { if (dateTime == null) { IQueryable <ForwardEntity> forwards = dbc.GetAll <ForwardEntity>().Where(f => f.UserId == userId); return(await forwards.LongCountAsync()); } else { IQueryable <ForwardEntity> forwards = dbc.GetAll <ForwardEntity>().Where(f => f.UserId == userId).Where(f => SqlFunctions.DateDiff("month", dateTime, f.CreateTime) == 0); return(await forwards.LongCountAsync()); } } }
public List <Dictionary <string, object> > Search(long jobOfferId, int langId = 1) { using (var db = new LMISEntities()) { var filter = jobOfferId > 0; var reqSkills = new List <string>(); var reqEdLevels = new List <string>(); var ret = new List <Dictionary <string, object> >(); if (filter) { var offer = db.JobOffers.AsNoTracking() .Where(a => a.JobOfferID == jobOfferId) .Select(a => new { expFrom = a.ExpYearFrom, expTo = a.ExpYearTo, skills = a.jobOfferSkillsDetails, medConds = a.jobOfferMedicalDetails, edLevels = a.JobOfferEducationLevels }) .ToList() .Select(a => new { a.expFrom, a.expTo, skills = a.skills.Select(dr => dr.SkillID).Distinct().ToList(), medConds = a.medConds.Select(dr => dr.MedicalID).ToList(), edLevels = a.edLevels.Select(dr => dr.EducationLevelID).Distinct().ToList() }) .Single(); reqSkills = offer.skills; reqEdLevels = offer.edLevels; ret.Add(new Dictionary <string, object>() { { "Filters", new { ExpFrom = offer.expFrom, ExpTo = offer.expTo, Skills = offer.skills, MedConds = offer.medConds, EdLevels = offer.edLevels } } }); } ret.AddRange(db.IndividualDetails .AsNoTracking() .Where(r => r.Is_Approved == (byte)Approval.Approved) .Select(a => new { Candidate = a, Details = a.IndividualDetailsDets, Experience = a.IndividualExperienceDetails.Where(dr => dr.IsDeleted == null && (dr.CurrentEmploymentStatus == 1 || dr.EmploymentEndDate.HasValue)) .Select(dr => new { days = SqlFunctions.DateDiff("DAY", dr.EmploymentStartDate, dr.CurrentEmploymentStatus == 1 ? DateTime.Today : dr.EmploymentEndDate) ?? 0, job = dr.EmploymentJobTitle }), Skills = a.IndividualSkillsDetails.Where(dr => dr.IsDeleted == null) .Select(dr => new { id = dr.SkillID, desc = SqlUdf.SubCodeName(dr.SkillID, langId) }), CountryDesc = SqlUdf.SubCodeName(a.CountryID, langId), CityDesc = SqlUdf.SubCodeName(a.CityID, langId), GenderDesc = SqlUdf.SubCodeName(a.GenderId, langId), MedCondDesc = SqlUdf.SubCodeName(a.IndividualMedicalID, langId), EdLevels = a.IndividualEducationlevels.Where(dr => dr.IsDeleted == null) .Select(dr => new { id = dr.LevelOfEducation, desc = SqlUdf.SubCodeName(dr.LevelOfEducation, langId) }) }) .ToList() .Select(a => new Dictionary <string, object> { { "Id", a.Candidate.PortalUsersID }, { "FirstName", (new GlobalString(a.Details.Select(d => new LocalString(d.LanguageID, d.FirstName)).ToList())).ToLocalString((Language)langId, true).T }, { "LastName", (new GlobalString(a.Details.Select(d => new LocalString(d.LanguageID, d.LastName)).ToList())).ToLocalString((Language)langId, true).T }, { "Experience", a.Experience.Select(e => e.days).Sum() }, { "Jobs", a.Experience.Select(e => e.job).Distinct().ToList() }, { "SkillRating", reqSkills.Any() ? a.Skills.Select(dr => reqSkills.Contains(dr.id)).Distinct().Count() / reqSkills.Count() : -1 }, { "Skills", a.Skills.Select(s => s.id).Distinct().ToList() }, { "Country", new CodeSet { id = a.Candidate.CountryID, desc = a.CountryDesc } }, { "City", new CodeSet { id = a.Candidate.CityID, desc = a.CityDesc } }, { "Gender", new CodeSet { id = a.Candidate.GenderId, desc = a.GenderDesc } }, { "MedCond", new CodeSet { id = a.Candidate.IndividualMedicalID, desc = a.MedCondDesc } }, { "EdLevels", a.EdLevels.Select(s => s.id).Distinct().ToList() }, { "EdlevelMatching", !reqEdLevels.Any() || a.EdLevels.Any(dr => reqEdLevels.Contains(dr.id)) } }) .ToList()); return(ret); } }
public MenuItemQuery Withfilter(IEnumerable <filterRule> filters) { if (filters != null) { foreach (var rule in filters) { if (rule.field == "Id" && !string.IsNullOrEmpty(rule.value) && rule.value.IsInt()) { int val = Convert.ToInt32(rule.value); switch (rule.op) { case "equal": And(x => x.Id == val); break; case "notequal": And(x => x.Id != val); break; case "less": And(x => x.Id < val); break; case "lessorequal": And(x => x.Id <= val); break; case "greater": And(x => x.Id > val); break; case "greaterorequal": And(x => x.Id >= val); break; default: And(x => x.Id == val); break; } } if (rule.field == "Title" && !string.IsNullOrEmpty(rule.value)) { And(x => x.Title.Contains(rule.value)); } if (rule.field == "Description" && !string.IsNullOrEmpty(rule.value)) { And(x => x.Description.Contains(rule.value)); } if (rule.field == "Code" && !string.IsNullOrEmpty(rule.value)) { And(x => x.Code.Contains(rule.value)); } if (rule.field == "Url" && !string.IsNullOrEmpty(rule.value)) { And(x => x.Url.Contains(rule.value)); } if (rule.field == "Controller" && !string.IsNullOrEmpty(rule.value)) { And(x => x.Controller.Contains(rule.value)); } if (rule.field == "Action" && !string.IsNullOrEmpty(rule.value)) { And(x => x.Action.Contains(rule.value)); } if (rule.field == "IconCls" && !string.IsNullOrEmpty(rule.value)) { And(x => x.IconCls.Contains(rule.value)); } if (rule.field == "IsEnabled" && !string.IsNullOrEmpty(rule.value) && rule.value.IsBool()) { var boolval = Convert.ToBoolean(rule.value); And(x => x.IsEnabled == boolval); } if (rule.field == "ParentId" && !string.IsNullOrEmpty(rule.value) && rule.value.IsInt()) { int val = Convert.ToInt32(rule.value); switch (rule.op) { case "equal": And(x => x.ParentId == val); break; case "notequal": And(x => x.ParentId != val); break; case "less": And(x => x.ParentId < val); break; case "lessorequal": And(x => x.ParentId <= val); break; case "greater": And(x => x.ParentId > val); break; case "greaterorequal": And(x => x.ParentId >= val); break; default: And(x => x.ParentId == val); break; } } if (rule.field == "CreatedDate" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "between") { var datearray = rule.value.Split(new char[] { '-' }); var start = Convert.ToDateTime(datearray[0]); var end = Convert.ToDateTime(datearray[1]); And(x => SqlFunctions.DateDiff("d", start, x.CreatedDate) >= 0); And(x => SqlFunctions.DateDiff("d", end, x.CreatedDate) <= 0); } } if (rule.field == "CreatedBy" && !string.IsNullOrEmpty(rule.value)) { And(x => x.CreatedBy.Contains(rule.value)); } if (rule.field == "LastModifiedDate" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "between") { var datearray = rule.value.Split(new char[] { '-' }); var start = Convert.ToDateTime(datearray[0]); var end = Convert.ToDateTime(datearray[1]); And(x => SqlFunctions.DateDiff("d", start, x.LastModifiedDate) >= 0); And(x => SqlFunctions.DateDiff("d", end, x.LastModifiedDate) <= 0); } } if (rule.field == "LastModifiedBy" && !string.IsNullOrEmpty(rule.value)) { And(x => x.LastModifiedBy.Contains(rule.value)); } } } return(this); }
static void Main(string[] args) { using (MyDbContext dbc = new MyDbContext()) { dbc.Database.Log = (sql) => { Console.WriteLine(sql); }; DateTime dateTime = DateTime.Now; ForwardStatisticalResult result = new ForwardStatisticalResult(); IQueryable <ForwardEntity> forwards = dbc.GetAll <ForwardEntity>().Where(f => SqlFunctions.DateDiff("day", dateTime, f.CreateTime) == 0); result.TotalBonus = forwards.Sum(f => f.Task.Bonus); result.TotalCount = forwards.LongCount(); } Console.ReadKey(); }
public async Task <List <string> > GetAllDineIds(DateTime dateTime) { return(await ctx.Dines.Where(p => SqlFunctions.DateDiff("day", p.BeginTime, dateTime) == 0).Select(p => p.Id).ToListAsync()); }
private void btnGetData_Click(object sender, EventArgs e) { #region //Soru: Bütün kategorileri Listele //dataGridView1.DataSource = db.Categories.ToList(); #endregion #region //Soru: Çalışnaların tablosundan isim, soyisim, ünvan ve doğum tarihlerini listele dataGridView1.DataSource = db.Employees.Select(x => new { x.FirstName, x.LastName, x.Title, x.BirthDate }).ToList(); #endregion #region //Çalışan id'si 2 ile 8 arasında olan çalışanları A-Z'ye olacak şekilde isimlerine göre sıralayınız dataGridView1.DataSource = db.Employees.Where(x => x.EmployeeID > 2 && x.EmployeeID <= 8).OrderBy(x => x.FirstName).ToList(); #endregion #region //60 yaşından büyük olan çalışanları listeleyin dataGridView1.DataSource = db.Employees.Where(x => SqlFunctions.DateDiff("Year", x.BirthDate, DateTime.Now) > 60).Select(x => new { x.FirstName, x.LastName, x.Title, x.BirthDate }).ToList(); #endregion #region //1960 yılında doğan çalışanları listeleyin dataGridView1.DataSource = db.Employees.Where(x => SqlFunctions.DatePart("Year", x.BirthDate) == 1960).Select(x => new { x.FirstName, x.LastName, x.Title, x.BirthDate }).ToList(); #endregion #region //1950 ve 1961 aralığında doğmuş çalışanların ismi ve soyismi doğum tarihleriyle listelenyin dataGridView1.DataSource = db.Employees.Where(x => SqlFunctions.DatePart("Year", x.BirthDate) >= 1950 && SqlFunctions.DatePart("Year", x.BirthDate) <= 1961).Select(x => new { x.FirstName, x.LastName, x.BirthDate }).ToList(); #endregion #region //Ünvanı Mr. olan ve yaşı 60'tan büyük olan çalışanları listeleyin dataGridView1.DataSource = db.Employees.Where(x => x.TitleOfCourtesy == "Mr." && SqlFunctions.DateDiff("Year", x.BirthDate, DateTime.Now) > 60).Select(x => new { x.FirstName, x.LastName, x.Title, x.BirthDate }).ToList(); #endregion #region //Çalışanların firstname, lastname, titleofcourtesy ve age ekrana getirilsin. yaşa göre azalan şekilde sıralansın dataGridView1.DataSource = db.Employees.OrderBy(x => SqlFunctions.DateDiff("Year", x.BirthDate, DateTime.Now)).Select(x => new { x.FirstName, x.LastName, x.Title, Yas = SqlFunctions.DateDiff("Year", x.BirthDate, DateTime.Now) }).ToList(); #endregion #region //Doğum tarihi 1930 ile 1960 arasında olup da USA'da çalışanları listeleyeceğiz dataGridView1.DataSource = db.Employees.Where(x => SqlFunctions.DatePart("Year", x.BirthDate) >= 1930 && SqlFunctions.DatePart("Year", x.BirthDate) <= 1960 && x.Country == "USA").Select(x => new { x.FirstName, x.LastName, x.Title, x.Country, x.BirthDate }).ToList(); #endregion #region //Ünvanı mr veya dr olanların listlenmesi dataGridView1.DataSource = db.Employees.Where(x => x.TitleOfCourtesy == "Mr." || x.TitleOfCourtesy == "Dr.").Select(x => new { x.TitleOfCourtesy, x.FirstName, x.LastName }).ToList(); #endregion #region //Ürünlerin birim fiyatları 18,19 veya 25 olanları listeleyin dataGridView1.DataSource = db.Products.Where(x => x.UnitPrice == 18 || x.UnitPrice == 19 || x.UnitPrice == 25).OrderByDescending(x => x.UnitPrice).Select(x => new { x.ProductName, x.UnitPrice }).ToList(); #endregion #region //Ismi içerisinde "A" harfi geçen çallışanları listeleyin dataGridView1.DataSource = db.Employees.Where(x => x.FirstName.Contains("A")).Select(x => new { x.FirstName, x.LastName, x.Title }).ToList(); #endregion #region //Products tablosundan ProductID, ProductName, CategoryID, Categories tablosundan CategoryName, Description dataGridView1.DataSource = db.Products.Join(db.Categories, p => p.CategoryID, c => c.CategoryID, (p, c) => new { p.ProductID, p.ProductName, p.CategoryID, p.Category.CategoryName, p.Category.Description }).ToList(); #endregion #region dataGridView1.DataSource = db.Customers .Join(db.Orders, c => c.CustomerID, o => o.CustomerID, (c, o) => new { c, o }) .Join(db.Employees, oc => oc.o.EmployeeID, em => em.EmployeeID, (oc, em) => new { oc, em }).Select(z => new { z.oc.o.OrderID, z.oc.o.OrderDate, z.oc.c.CompanyName, z.em.FirstName, z.em.LastName, z.em.Title }).ToList(); #endregion #region //Kategorilerine göre toplam stok miktarını lsiteleyiniz dataGridView1.DataSource = db.Categories.Join(db.Products, p => p.CategoryID, c => c.CategoryID, (p, c) => new { p, c }).GroupBy(x => x.p.CategoryName).Select(y => new { Name = y.Key, Count = y.Sum(p => p.c.UnitsInStock) }).ToList(); #endregion #region ////Çalışanlar ne kadarlık satış yaptığını listeleyin dataGridView1.DataSource = db.Employees .Join(db.Orders, emp => emp.EmployeeID, ord => ord.EmployeeID, (ord, emp) => new { ord, emp }) .Join(db.Order_Details, od => od.emp.OrderID, de => de.OrderID, (od, de) => new { od, de }) .GroupBy(x => x.od.ord.FirstName) .Select(z => new { Name = z.Key, Count = z.Sum(y => y.de.Quantity * y.de.UnitPrice) }).ToList(); #endregion #region //Ürünlere göre satış miktarını listeleyiniz dataGridView1.DataSource = db.Products .Join(db.Order_Details, od => od.ProductID, p => p.ProductID, (od, p) => new { od, p }) .GroupBy(x => x.od.ProductName) .OrderByDescending(q => q.Sum(y => y.p.Quantity * y.p.UnitPrice)) .Select(z => new { Urun_Adı = z.Key, Miktar = z.Sum(y => y.p.Quantity), Gelir = z.Sum(y => y.p.Quantity * y.p.UnitPrice) }).ToList(); #endregion #region //Ürün kategorilerine göre satışların ne kadar olduğunu listeleyin dataGridView1.DataSource = db.Categories .Join(db.Products, c => c.CategoryID, p => p.CategoryID, (c, p) => new { c, p }) .Join(db.Order_Details, od => od.p.ProductID, p => p.ProductID, (od, p) => new { od, p }) .GroupBy(x => x.od.c.CategoryName) .OrderBy(y => y.Sum(q => q.p.Quantity * q.p.UnitPrice)) .Select(z => new { Category_Name = z.Key, Outcome = z.Sum(q => q.p.Quantity * q.p.UnitPrice) }).ToList(); #endregion }
static Expression <Func <T, DateTime?, bool> > ExpressionDateTime <T>(string propertyValue) where T : class { Expression <Func <T, DateTime?, bool> > result = null; var partes = propertyValue.Split('/'); int dia; int mes; int año; if (partes.Count() == 1) { if (partes[0].Length == 4) { if (int.TryParse(partes[0], out año)) { result = (ex, value) => (SqlFunctions.DatePart("yyyy", value) ?? 0) == año; } } else if (partes[0].Length == 2) { if (int.TryParse(partes[0], out dia)) { result = (ex, value) => (SqlFunctions.DatePart("dd", value) ?? 0) == dia || (SqlFunctions.DatePart("mm", value) ?? 0) == dia || ((SqlFunctions.DatePart("yyyy", value) ?? 0) + 2000) == dia; } } } if (partes.Count() == 2) { if (partes[1].Length == 4) { var mesBien = int.TryParse(partes[0], out mes); var añoBien = int.TryParse(partes[1], out año); if (mesBien && añoBien) { result = (ex, value) => (SqlFunctions.DatePart("mm", value) ?? 0) == mes && (SqlFunctions.DatePart("yyyy", value) ?? 0) == año; } else { if (mesBien) { result = (ex, value) => (SqlFunctions.DatePart("mm", value) ?? 0) == mes; } if (añoBien) { result = (ex, value) => (SqlFunctions.DatePart("yyyy", value) ?? 0) == año; } } } else if (partes[1].Length == 2) { var diaBien = int.TryParse(partes[0], out dia); var mesBien = int.TryParse(partes[1], out mes); if (diaBien && mesBien) { result = (ex, value) => ((SqlFunctions.DatePart("dd", value) ?? 0) == dia && (SqlFunctions.DatePart("mm", value) ?? 0) == mes) || ((SqlFunctions.DatePart("mm", value) ?? 0) == dia && ((SqlFunctions.DatePart("yyyy", value) ?? 0) + 2000) == mes); } } } if (partes.Count() == 3) { if (partes[2].Length == 4) { var diaBien = int.TryParse(partes[0], out dia); var mesBien = int.TryParse(partes[1], out mes); var añoBien = int.TryParse(partes[2], out año); if (diaBien && mesBien && añoBien) { result = (ex, value) => (SqlFunctions.DatePart("mm", value) ?? 0) == dia && (SqlFunctions.DatePart("mm", value) ?? 0) == mes && (SqlFunctions.DatePart("yyyy", value) ?? 0) == año; } else { if (diaBien && mesBien) { result = (ex, value) => (SqlFunctions.DatePart("mm", value) ?? 0) == dia && (SqlFunctions.DatePart("mm", value) ?? 0) == mes; } if (mesBien && añoBien) { result = (ex, value) => (SqlFunctions.DatePart("mm", value) ?? 0) == mes && (SqlFunctions.DatePart("yyyy", value) ?? 0) == año; } } } else if (partes[2].Length == 2) { var date = DateTime.Today; if (DateTime.TryParse(propertyValue, out date)) { result = (ex, value) => Math.Abs(SqlFunctions.DateDiff("dd", value, date) ?? 0) < 1; } else { var diaBien = int.TryParse(partes[0], out dia); var mesBien = int.TryParse(partes[1], out mes); var añoBien = int.TryParse(partes[2], out año); if (diaBien && mesBien) { result = (ex, value) => (SqlFunctions.DatePart("mm", value) ?? 0) == dia && (SqlFunctions.DatePart("mm", value) ?? 0) == mes && ((SqlFunctions.DatePart("yyyy", value) ?? 0) + 2000) == año; } else { if (diaBien && mesBien) { result = (ex, value) => (SqlFunctions.DatePart("mm", value) ?? 0) == dia && (SqlFunctions.DatePart("mm", value) ?? 0) == mes; } if (mesBien && añoBien) { result = (ex, value) => (SqlFunctions.DatePart("mm", value) ?? 0) == mes && ((SqlFunctions.DatePart("yyyy", value) ?? 0) + 2000) == año; } } } } } if (result != null) { return(result); } else { return((ex, value) => false); } }
public EmployeeQuery Withfilter(IEnumerable <filterRule> filters) { if (filters != null) { foreach (var rule in filters) { if (rule.field == "Id" && !string.IsNullOrEmpty(rule.value) && rule.value.IsInt()) { int val = Convert.ToInt32(rule.value); switch (rule.op) { case "equal": And(x => x.Id == val); break; case "notequal": And(x => x.Id != val); break; case "less": And(x => x.Id < val); break; case "lessorequal": And(x => x.Id <= val); break; case "greater": And(x => x.Id > val); break; case "greaterorequal": And(x => x.Id >= val); break; default: And(x => x.Id == val); break; } } if (rule.field == "Name" && !string.IsNullOrEmpty(rule.value)) { And(x => x.Name.Contains(rule.value)); } if (rule.field == "Title" && !string.IsNullOrEmpty(rule.value)) { And(x => x.Title.Contains(rule.value)); } if (rule.field == "Sex" && !string.IsNullOrEmpty(rule.value)) { And(x => x.Sex.Contains(rule.value)); } if (rule.field == "Age" && !string.IsNullOrEmpty(rule.value) && rule.value.IsInt()) { int val = Convert.ToInt32(rule.value); switch (rule.op) { case "equal": And(x => x.Age == val); break; case "notequal": And(x => x.Age != val); break; case "less": And(x => x.Age < val); break; case "lessorequal": And(x => x.Age <= val); break; case "greater": And(x => x.Age > val); break; case "greaterorequal": And(x => x.Age >= val); break; default: And(x => x.Age == val); break; } } if (rule.field == "Brithday" && !string.IsNullOrEmpty(rule.value)) { if (rule.op == "between") { var datearray = rule.value.Split(new char[] { '-' }); var start = Convert.ToDateTime(datearray[0]); var end = Convert.ToDateTime(datearray[1]); And(x => SqlFunctions.DateDiff("d", start, x.Brithday) >= 0); And(x => SqlFunctions.DateDiff("d", end, x.Brithday) <= 0); } } if (rule.field == "IsDeleted" && !string.IsNullOrEmpty(rule.value) && rule.value.IsInt()) { int val = Convert.ToInt32(rule.value); switch (rule.op) { case "equal": And(x => x.IsDeleted == val); break; case "notequal": And(x => x.IsDeleted != val); break; case "less": And(x => x.IsDeleted < val); break; case "lessorequal": And(x => x.IsDeleted <= val); break; case "greater": And(x => x.IsDeleted > val); break; case "greaterorequal": And(x => x.IsDeleted >= val); break; default: And(x => x.IsDeleted == val); break; } } if (rule.field == "CompanyId" && !string.IsNullOrEmpty(rule.value) && rule.value.IsInt()) { int val = Convert.ToInt32(rule.value); switch (rule.op) { case "equal": And(x => x.CompanyId == val); break; case "notequal": And(x => x.CompanyId != val); break; case "less": And(x => x.CompanyId < val); break; case "lessorequal": And(x => x.CompanyId <= val); break; case "greater": And(x => x.CompanyId > val); break; case "greaterorequal": And(x => x.CompanyId >= val); break; default: And(x => x.CompanyId == val); break; } } } } return(this); }
/// <summary> /// Gets the expression. /// </summary> /// <param name="context">The context.</param> /// <param name="entityIdProperty">The entity identifier property.</param> /// <param name="selection">The selection.</param> /// <returns></returns> public override Expression GetExpression(RockContext context, MemberExpression entityIdProperty, string selection) { DateTime currentDate = RockDateTime.Today; int currentDayOfYear = currentDate.DayOfYear; //// have SQL Server do the following math (DateDiff only returns the integers): //// If the person hasn't had their birthday this year, their age is the DateDiff in Years - 1, otherwise, it is DateDiff in Years (without adjustment) var personAgeQuery = new PersonService(context).Queryable() .Select(p => p.BirthDate > SqlFunctions.DateAdd("year", -SqlFunctions.DateDiff("year", p.BirthDate, currentDate), currentDate) ? SqlFunctions.DateDiff("year", p.BirthDate, currentDate) - 1 : SqlFunctions.DateDiff("year", p.BirthDate, currentDate)); var selectAgeExpression = SelectExpressionExtractor.Extract <Rock.Model.Person>(personAgeQuery, entityIdProperty, "p"); return(selectAgeExpression); }
public WorkProcessQuery Withfilter(IEnumerable <filterRule> filters) { if (filters != null) { foreach (var rule in filters) { if (rule.field == "Id" && !string.IsNullOrEmpty(rule.value)) { int val = Convert.ToInt32(rule.value); And(x => x.Id == val); } if (rule.field == "WorkNo" && !string.IsNullOrEmpty(rule.value)) { And(x => x.WorkNo.Contains(rule.value)); } if (rule.field == "WorkId" && !string.IsNullOrEmpty(rule.value)) { int val = Convert.ToInt32(rule.value); And(x => x.WorkId == val); } if (rule.field == "OrderId" && !string.IsNullOrEmpty(rule.value)) { int val = Convert.ToInt32(rule.value); And(x => x.OrderId == val); } if (rule.field == "OrderKey" && !string.IsNullOrEmpty(rule.value)) { And(x => x.OrderKey.Contains(rule.value)); } if (rule.field == "ProjectName" && !string.IsNullOrEmpty(rule.value)) { And(x => x.ProjectName.Contains(rule.value)); } if (rule.field == "SKUId" && !string.IsNullOrEmpty(rule.value)) { int val = Convert.ToInt32(rule.value); And(x => x.SKUId == val); } if (rule.field == "GraphSKU" && !string.IsNullOrEmpty(rule.value)) { And(x => x.GraphSKU.Contains(rule.value)); } if (rule.field == "RequirementQty" && !string.IsNullOrEmpty(rule.value)) { int val = Convert.ToInt32(rule.value); And(x => x.RequirementQty == val); } if (rule.field == "ProductionQty" && !string.IsNullOrEmpty(rule.value)) { int val = Convert.ToInt32(rule.value); And(x => x.ProductionQty == val); } if (rule.field == "FinishedQty" && !string.IsNullOrEmpty(rule.value)) { int val = Convert.ToInt32(rule.value); And(x => x.FinishedQty == val); } if (rule.field == "WorkItems" && !string.IsNullOrEmpty(rule.value)) { int val = Convert.ToInt32(rule.value); And(x => x.WorkItems == val); } if (rule.field == "ProductionProcessId" && !string.IsNullOrEmpty(rule.value)) { int val = Convert.ToInt32(rule.value); And(x => x.ProductionProcessId == val); } if (rule.field == "Status" && !string.IsNullOrEmpty(rule.value)) { int val = Convert.ToInt32(rule.value); And(x => x.Status == val); } if (rule.field == "Operator" && !string.IsNullOrEmpty(rule.value)) { And(x => x.Operator.Contains(rule.value)); } if (rule.field == "WorkDate" && !string.IsNullOrEmpty(rule.value)) { var date = Convert.ToDateTime(rule.value); And(x => SqlFunctions.DateDiff("d", date, x.WorkDate) >= 0); } if (rule.field == "Remark" && !string.IsNullOrEmpty(rule.value)) { And(x => x.Remark.Contains(rule.value)); } if (rule.field == "WorkDetailId" && !string.IsNullOrEmpty(rule.value)) { int val = Convert.ToInt32(rule.value); And(x => x.WorkDetailId == val); } if (rule.field == "CustomerId" && !string.IsNullOrEmpty(rule.value)) { int val = Convert.ToInt32(rule.value); And(x => x.CustomerId == val); } } } return(this); }