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);
}
