static SOM CreateSomModel_v3(string tagname, DayOfWeek day) { int samples = 24 * 10; var context = new TagDbContext(); double[] data = context.TagValues .Where(t => t.Tag.TagName == tagname && (SqlFunctions.DatePart("weekday", t.DateTime) - 1) == (int)day) .OrderByDescending(v => v.DateTime) .Select(v => v.Value) .Take(samples + retrospective + 1) .AsEnumerable() .Reverse() .ToArray(); var inputs = new Vector[samples]; for (int i = 0; i < samples; i++) { inputs[i] = new Vector(); inputs[i].Input = data.Skip(i).Take(retrospective).ToArray(); // set output inputs[i].Output = new double[] { data[retrospective + i + 1] }; } SOM model = new SOM(5, 1, 12, 12); model.Train(inputs, 500); return(model); }
public void SetSoldGoods() { kitchenOrders = (from ordr in db.KitchenOrders where SqlFunctions.DatePart("day", ordr.Order.Date) == SqlFunctions.DatePart("day", dt) && SqlFunctions.DatePart("month", ordr.Order.Date) == SqlFunctions.DatePart("month", dt) && SqlFunctions.DatePart("year", ordr.Order.Date) == SqlFunctions.DatePart("year", dt) group ordr by new { ordr.Dish.Name, ordr.Dish.Price } into ord select new ProductModel() { Name = ord.Key.Name, Number = ord.Count(), Cost = ord.Key.Price }).ToList(); barOrders = (from ordr in db.BarOrders where SqlFunctions.DatePart("day", ordr.Order.Date) == SqlFunctions.DatePart("day", dt) && SqlFunctions.DatePart("month", ordr.Order.Date) == SqlFunctions.DatePart("month", dt) && SqlFunctions.DatePart("year", ordr.Order.Date) == SqlFunctions.DatePart("year", dt) group ordr by new { ordr.Drink.Name, ordr.Drink.Price } into ord select new ProductModel() { Name = ord.Key.Name, Number = ord.Count(), Cost = ord.Key.Price }).ToList(); }
public List <ThoiDiemSV_ThamGiaDTO> getSV_TG() { List <ThoiDiemSV_ThamGiaDTO> thoiDiemSV_ThamGiaDTOs = new List <ThoiDiemSV_ThamGiaDTO>(); thoiDiemSV_ThamGiaDTOs = (from tdtt in db.THOIDIEM_SV_THAMGIA from tg in db.THOIGIAN_XET from dv in db.DON_VI from sv in db.SINH_VIEN where tdtt.Mssv == sv.Mssv && sv.DonVi == dv.MaDonVi && tdtt.MaThoiGian == tg.MaThoiGian select new ThoiDiemSV_ThamGiaDTO { Mssv = tdtt.Mssv, TenSinhVien = sv.HoTen, Lop = sv.Lop, DonVi = dv.MaDonVi, ThoiDiemDK = tdtt.ThoiGian_DK, ThoiGian = string.Concat( SqlFunctions.DatePart("day", tg.TuNgay).ToString().Trim() + "/" + SqlFunctions.DatePart("month", tg.TuNgay).ToString().Trim() + "/" + SqlFunctions.DatePart("year", tg.TuNgay).ToString().Trim(), "_", SqlFunctions.DatePart("day", tg.DenNgay).ToString().Trim() + "/" + SqlFunctions.DatePart("month", tg.DenNgay).ToString().Trim() + "/" + SqlFunctions.DatePart("year", tg.DenNgay).ToString().Trim()) }).ToList(); return(thoiDiemSV_ThamGiaDTOs); }
public object GetAvailableMonth(int?permitteeId, int?year) { var model = _unitOfWork.ProductionsRepo.Fetch(); if (permitteeId > 0) { model = model.Where(x => x.PermiteeId == permitteeId); } if (year > 0) { model = model.Where(x => SqlFunctions.DatePart("year", x.ProductionDate) == year); } var month = model .GroupBy(x => SqlFunctions.DatePart("month", x.DateCreated) ?? 0) .Where(x => x.Key != 0) .Select(x => x.Key).ToList(); return(month.Select(x => new { MonthName = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(x), Month = x }).ToList()); }
/// <summary> /// This method fires when the data source for the employee DevExpress GridView is selecting /// and it handles the select /// </summary> /// <param name="sender">The efEmployeeDataSource control</param> /// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param> protected void efEmployeeDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //Set the key expression e.KeyExpression = "ProgramEmployeePK"; //Set the source to a LINQ query PyramidContext context = new PyramidContext(); e.QueryableSource = (from pe in context.ProgramEmployee.AsNoTracking().Include(pe => pe.CodeTermReason).Include(pe => pe.Program) join jf in context.JobFunction.AsNoTracking().Include(jf => jf.CodeJobType) on pe.ProgramEmployeePK equals jf.ProgramEmployeeFK into jobFunctions join t in context.Training.AsNoTracking().Include(t => t.CodeTraining) on pe.ProgramEmployeePK equals t.ProgramEmployeeFK into trainings where currentProgramRole.ProgramFKs.Contains(pe.ProgramFK) select new { pe.ProgramEmployeePK, Name = pe.FirstName + " " + pe.LastName, pe.EmailAddress, JobFunctions = from jf in jobFunctions where jf.EndDate.HasValue == false select jf.CodeJobType.Description, Trainings = from t in trainings where t.TrainingCodeFK == (int)Utilities.TrainingFKs.INTRODUCTION_TO_COACHING || t.TrainingCodeFK == (int)Utilities.TrainingFKs.PRACTICE_BASED_COACHING || t.TrainingCodeFK == (int)Utilities.TrainingFKs.TPITOS_OBSERVER || t.TrainingCodeFK == (int)Utilities.TrainingFKs.TPOT_OBSERVER orderby t.TrainingDate ascending select(t.CodeTraining.Abbreviation + ": " + SqlFunctions.DatePart("month", t.TrainingDate) + "/" + SqlFunctions.DatePart("day", t.TrainingDate) + "/" + SqlFunctions.DatePart("year", t.TrainingDate)), pe.HireDate, pe.TermDate, TermReason = pe.CodeTermReason.Description + " " + (pe.TermReasonSpecify == null ? "" : "(" + pe.TermReasonSpecify + ")"), pe.ProgramFK, pe.Program.ProgramName }); }
public async Task <List <int> > GetYearsForSiteMap() { return(await _context.Articles.GroupBy(g => SqlFunctions.DatePart("Year", g.DatePublish)) .Select(s => s.Key.Value) .ToListAsync()); }
public static IQueryable <WeeklyQuantity> GetWeeklyQuantities(IQueryable <SubReport> subreports, int weekStart) { var q1 = from sr in subreports from cr in sr.ClientReports from ar in cr.ClientAmountReports group ar by new { cr.ClientId, ar.ReportDate } into g select new { g.Key.ClientId, g.Key.ReportDate, Quantity = g.Sum(f => f.Quantity), } into ar let wd = SqlFunctions.DatePart("weekday", ar.ReportDate) let ws = weekStart let weekStartDate = wd < ws? SqlFunctions.DateAdd("day", ws - wd - 7, ar.ReportDate) : SqlFunctions.DateAdd("day", ws - wd, ar.ReportDate) let weekEndDate = SqlFunctions.DateAdd("week", 1, weekStartDate) select new WeeklyQuantity { ClientId = ar.ClientId, ReportDate = ar.ReportDate, WeekStart = weekStartDate, WeekEnd = weekEndDate, Quantity = ar.Quantity, }; return(q1); }
private void UpdateDataGridView() { var result = _context.Income.Where(income => income.UserId == userId); if (_HideTransactionsOlderThanWeek) { result = result.Where(income => (SqlFunctions.DatePart("dayofyear", DateTime.Now) - SqlFunctions.DatePart("dayofyear", income.IncomeDate)) >= 0); } if (_HideTransactionsOlderThanMonth) { result = result.Where(income => (SqlFunctions.DatePart("dayofyear", DateTime.Now) - SqlFunctions.DatePart("dayofyear", income.IncomeDate)) >= 0); } if (_HideTransactionsOlderThan3Months) { result = result.Where(income => (SqlFunctions.DatePart("dayofyear", DateTime.Now) - SqlFunctions.DatePart("dayofyear", income.IncomeDate)) >= 0); } if (_HideTransactionsOlderThan12Months) { result = result.Where(income => (SqlFunctions.DatePart("dayofyear", DateTime.Now) - SqlFunctions.DatePart("dayofyear", income.IncomeDate)) >= 0); } if (_ShowEntirePeriod) { result = result.Where(income => (SqlFunctions.DatePart("dayofyear", DateTime.Now) - SqlFunctions.DatePart("dayofyear", income.IncomeDate)) >= 0); } IncomesDataGridView.DataSource = result.ToList(); }
public IEnumerable <ReleaseView> GetReleaseJoinAccount() { var Query = DbSet.AsNoTracking() .Include(r => r.Account) .Include(r => r.ReleaseStakeholders) .Include(r => r.ReleaseStakeholders.Select(rsh => rsh.Stakeholder)) .Include(r => r.ReleaseStakeholders.Select(rsh => rsh.Employee1)) .Include(r => r.ReleaseMilestones).OrderBy(r => r.ReleaseID) .Select(r => new ReleaseView() { ReleaseName = r.Name, AccountName = r.Account != null ? r.Account.Name : String.Empty, ReleaseID = r.ReleaseID, PrepFPName = r.Account != null ? r.Account.PrepFPName : String.Empty, PrepReviewMode = "Full", // PREP.DAL.Models.Employee SPNameEmployeeTmp = r.ReleaseStakeholders.Any(rsh => rsh.StakeholderID == 6) ? r.ReleaseStakeholders.Where(rsh => rsh.StakeholderID == 6).FirstOrDefault().Employee1 : null; SPNameEmployee = r.ReleaseStakeholders.Any(rsh => rsh.StakeholderID == 6) && r.ReleaseStakeholders.Where(rsh => rsh.StakeholderID == 6).FirstOrDefault().Employee1 != null ? r.ReleaseStakeholders.Where(rsh => rsh.StakeholderID == 6).FirstOrDefault().Employee1.FirstName + " " + r.ReleaseStakeholders.Where(rsh => rsh.StakeholderID == 6).FirstOrDefault().Employee1.LastName : String.Empty, ProgramMeEmployee = r.ReleaseStakeholders.Any(rsh => rsh.StakeholderID == 7) && r.ReleaseStakeholders.Where(rsh => rsh.StakeholderID == 7).FirstOrDefault().Employee1 != null ? r.ReleaseStakeholders.Where(rsh => rsh.StakeholderID == 7).FirstOrDefault().Employee1.FirstName + " " + r.ReleaseStakeholders.Where(rsh => rsh.StakeholderID == 6).FirstOrDefault().Employee1.LastName : String.Empty, ProductionStartDate = r.ReleaseMilestones.Any(m => m.MilestoneID == 12) ? (SqlFunctions.StringConvert((double)SqlFunctions.DatePart("MM", r.ReleaseMilestones.Where(m => m.MilestoneID == 12).FirstOrDefault().MilestoneDate)).Trim() + "-" + SqlFunctions.DateName("dd", r.ReleaseMilestones.Where(m => m.MilestoneID == 12).FirstOrDefault().MilestoneDate).Trim() + "-" + SqlFunctions.DateName("yyyy", r.ReleaseMilestones.Where(m => m.MilestoneID == 12).FirstOrDefault().MilestoneDate).Trim()) : String.Empty } ); return(Query); }
public List <DiscountAndMiscReportModels> GetReceiptDiscountAndMisc(DateTime fromDate, DateTime toDate, List <string> StoreIds, int mode) { using (var cxt = new NuWebContext()) { var lstData = new List <DiscountAndMiscReportModels>(); var query = (from m in cxt.R_DiscountAndMiscReport where StoreIds.Contains(m.StoreId) && m.CreatedDate >= fromDate && m.CreatedDate <= toDate && m.Mode == mode group m by new { Hour = (int?)SqlFunctions.DatePart("HH", m.CreatedDate), StoreId = m.StoreId, //Date = DbFunctions.TruncateTime(m.CreatedDate) } into g select g).ToList(); if (query != null && query.Any()) { lstData = query.Select(g => new DiscountAndMiscReportModels { StoreId = g.Key.StoreId, Hour = g.Key.Hour.Value, TimeSpanHour = new TimeSpan(g.Key.Hour.Value, 0, 0), //CreatedDate = g.Key.Date.Value, DiscountValue = g.Sum(ss => ss.DiscountValue), MiscValue = g.Sum(ss => ss.MiscValue) }).ToList(); } return(lstData); } }
public List <DiscountAndMiscReportModels> GetReceiptDiscountAndMiscByDateTime(DateTime fromDate, DateTime toDate, List <string> StoreIds, int mode) { using (var cxt = new NuWebContext()) { var lstData = new List <DiscountAndMiscReportModels>(); var query = (from ps in cxt.R_PosSale from psd in cxt.R_PosSaleDetail.Where(ww => ww.StoreId == ps.StoreId && ww.OrderId == ps.OrderId) where StoreIds.Contains(ps.StoreId) && ps.ReceiptCreatedDate >= fromDate && ps.ReceiptCreatedDate <= toDate && psd.Mode == mode group psd by new { Date = DbFunctions.TruncateTime(ps.ReceiptCreatedDate), Hour = (int?)SqlFunctions.DatePart("HH", ps.ReceiptCreatedDate), StoreId = ps.StoreId, } into g select g).ToList(); if (query != null && query.Any()) { lstData = query.Select(g => new DiscountAndMiscReportModels { StoreId = g.Key.StoreId, Hour = g.Key.Hour.Value, TimeSpanHour = new TimeSpan(g.Key.Hour.Value, 0, 0), CreatedDate = g.Key.Date.Value, DiscountValue = g.Where(ww => ww.IsDiscountTotal != true).Sum(ss => ss.Discount), MiscValue = g.Where(ww => ww.ItemTypeId == (byte)Commons.EProductType.Misc).Sum(ss => ss.Price) }).ToList(); } return(lstData); } }
public ActionResult TotalJson() { User user = HttpContext.CurrentUser(); Func <int, int?, IQueryable <Payroll> > query = (y, m) => db.Payrolls.Where(val => SqlFunctions.DatePart("YEAR", val.Date) == y && (!m.HasValue || SqlFunctions.DatePart("MONTH", val.Date) == m)); string[] months = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.MonthNames; List <int> years = db.Payrolls.Select(val => SqlFunctions.DatePart("YEAR", val.Date)).Distinct().OrderByDescending(val => val).Select(val => val.Value).ToList(); for (int i = years.Any() ? years.First() : DateTime.Today.Year; i < DateTime.Today.Year + 2; i++) { if (!years.Contains(i)) { years.Add(i); } } years = years.OrderByDescending(val => val).ToList(); object data = new { Years = years.Select(val => new { Year = val, Months = Enumerable.Range(1, 12).Select(val2 => new { Year = val, Name = months[val2 - 1], Month = val2, Total = (query(val, val2).Any() ? query(val, val2).Sum(val3 => val3.Amount) : 0) }) }), }; return(Json(data)); }
private async Task ValidateSingleJoinAsync(EventJoinValidationDto validationDto, int orgId, string userId) { if (validationDto.SelectedOptions.All(x => x.Rule == OptionRules.IgnoreSingleJoin) && validationDto.SelectedOptions.Count != 0 || !validationDto.IsSingleJoin) { return; } var query = _eventsDbSet .Include(e => e.EventParticipants.Select(x => x.EventOptions)) .Include(e => e.EventType) .Where(e => e.OrganizationId == orgId && e.Id != validationDto.Id && SqlFunctions.DatePart(WeekOfYear, e.StartDate) == SqlFunctions.DatePart(WeekOfYear, validationDto.StartDate) && e.StartDate.Year == validationDto.StartDate.Year && e.EventParticipants.Any(p => p.ApplicationUserId == userId && p.AttendStatus == (int)AttendingStatus.Attending)); query = string.IsNullOrEmpty(validationDto.SingleJoinGroupName) ? query.Where(x => x.EventType.Id == validationDto.EventTypeId) : query.Where(x => x.EventType.SingleJoinGroupName == validationDto.SingleJoinGroupName); var anyEventsAlreadyJoined = await query.AnyAsync(x => !x.EventParticipants.Any(y => y.ApplicationUserId == userId && y.EventOptions.All(z => z.Rule == OptionRules.IgnoreSingleJoin) && y.EventOptions.Count > 0)); _eventValidationService.CheckIfUserExistsInOtherSingleJoinEvent(anyEventsAlreadyJoined); }
public List <rptProductionRuntime> GetProductionRunTimeReport(ReportParameters para) { IQueryable <TransactionLog> result = GetFilteredTransactionLog(para); var data = result .Where(p => p.ClientId == para.ClientId) .GroupBy(p => new { p.MachineName, TransDate = (DateTime)DbFunctions.TruncateTime(p.TransactionDate), TransHour = (int)SqlFunctions.DatePart("hour", p.TransactionDate) }, (key, group) => new { MachineName = key.MachineName, TransDate = key.TransDate, TransHour = key.TransHour, Result = group.ToList() }) .OrderBy(p => p.MachineName).ThenBy(p => p.TransDate).ThenBy(p => p.TransHour) .Select(s => new rptProductionRuntime { MachineName = s.MachineName, TransDate = s.TransDate, TransHour = s.TransHour, TotalUnits = s.Result.Count() }).ToList(); return(data.ToList()); }
public void SetWaiters() { Waiters = new List <Waiter>(); Waiters.AddRange((from ordr in db.Orders where SqlFunctions.DatePart("day", ordr.Date) == SqlFunctions.DatePart("day", dt) && SqlFunctions.DatePart("month", ordr.Date) == SqlFunctions.DatePart("month", dt) && SqlFunctions.DatePart("year", ordr.Date) == SqlFunctions.DatePart("year", dt) group ordr by new { ordr.Person.FirstName, ordr.Person.LastName, ordr.Person.Phone, ordr.Person.Salary.Size } into ord select new Waiter() { FirstName = ord.Key.FirstName, LastName = ord.Key.LastName, Phone = ord.Key.Phone, Salary = ord.Key.Size, OrdersCount = ord.Count() }).ToList()); Waiters.ForEach(w => OrdersCount += w.OrdersCount); }
public static void ForecastAndExport_v3(string forecast_tag, string data_tag, int forecastPerspective = 2) { string tag_name = forecast_tag; double[] vMax, vMin; DateTime fdate = DateTime.Today.AddDays(forecastPerspective); // дата прогноза SOM model = CreateSomModel(data_tag, out vMax, out vMin); Dictionary <DateTime, double> solution = new Dictionary <DateTime, double>(); // прогноз TagDbContext context = new TagDbContext(); var data = context.TagValues .Where(t => t.Tag.TagName.Trim() == data_tag) .OrderByDescending(v => v.DateTime) .Select(v => new { v.DateTime.Hour, Day = SqlFunctions.DatePart("weekday", v.DateTime) + 1, Value = v.Value }) .Take(24 + 3 + 1) .AsEnumerable() .Reverse() .ToArray(); for (int index = 0; index < 24; index++) { var vector = new double[] { (double)(vMax[0] - (double)data[index].Hour) / (vMax[0] - vMin[0]), (double)(vMax[1] - (double)data[index].Day) / (vMax[1] - vMin[1]), (double)(vMax[2] - data[index + 1].Value) / (vMax[2] - vMin[2]), (double)(vMax[3] - data[index + 2].Value) / (vMax[3] - vMin[3]), (double)(vMax[4] - data[index + 3].Value) / (vMax[4] - vMin[4]) }; var value = model.Compute(vector)[0]; solution[fdate.AddHours(data[index].Hour)] = value; } ExportToDB(tag_name, solution, context); }
static void RunExample() { using (var context = new EFRecipesEntities()) { var app1 = new Appointment { StartsAt = DateTime.Parse("4/7/2010 14:00"), GoesTo = DateTime.Parse("4/7/2010 15:00") }; var app2 = new Appointment { StartsAt = DateTime.Parse("4/8/2010 9:00"), GoesTo = DateTime.Parse("4/8/2010 11:00") }; var app3 = new Appointment { StartsAt = DateTime.Parse("4/8/2010 13:00"), GoesTo = DateTime.Parse("4/7/2010 15:00") }; context.Appointments.AddObject(app1); context.Appointments.AddObject(app2); context.Appointments.AddObject(app3); context.SaveChanges(); } using (var context = new EFRecipesEntities()) { var apps = from a in context.Appointments where SqlFunctions.DatePart("WEEKDAY", a.StartsAt) == 5 select a; Console.WriteLine("Appointments for Thursday"); Console.WriteLine("========================="); foreach (var appointment in apps) { Console.WriteLine("Appointment from {0} to {1}", appointment.StartsAt.ToShortTimeString(), appointment.GoesTo.ToShortTimeString()); } } Console.WriteLine("Press <enter> to continue..."); Console.ReadLine(); }
public GetScheduleByUserResponse GetScheduleByUser(GetScheduleByUserRequest request) { GetScheduleByUserResponse response = new GetScheduleByUserResponse(); var scheduleList = new List <ScheduleDto>(); if (request.RoleName.Equals("student")) { scheduleList = _context.Schedules.Where(s => s.Student.StudentId == request.UserId && s.TermId == request.TermId && s.CourseId == request.CourseId) .Select(s => new ScheduleDto() { ScheduleId = s.ScheduleId, Date = SqlFunctions.DateName("day", s.Date) + "/" + SqlFunctions.DateName("month", s.Date) + "/" + SqlFunctions.DateName("year", s.Date), SlotNo = s.SlotId, TeacherId = s.TeacherId, ClassId = s.ClassId, AttendanceStatus = s.AttendanceStatus, ReportStatus = s.ReportStatus }).ToList(); } else if (request.RoleName.Equals("teacher")) { scheduleList = _context.Schedules.Where(s => s.TeacherId == request.UserId && s.TermId == request.TermId && s.CourseId == request.CourseId) .GroupBy(g => new { g.Date, g.SlotId, g.TeacherId, g.ClassId }) .Select(s => new ScheduleDto() { ScheduleId = s.Select(c => c.ScheduleId).FirstOrDefault(), Date = SqlFunctions.DateName("day", s.Key.Date) + "/" + SqlFunctions.DatePart("mm", s.Key.Date) + "/" + SqlFunctions.DateName("year", s.Key.Date), SlotNo = s.Key.SlotId, TeacherId = s.Key.TeacherId, ClassId = s.Select(c => c.ClassId).FirstOrDefault() }).ToList(); } response.Schedules = scheduleList; return(response); }
public static void ForecastAndExport_v4(string forecast_tag, string data_tag, int forecastPerspective = 2) { string tag_name = forecast_tag; DateTime fdate = DateTime.Today.AddDays(forecastPerspective); // дата прогноза List <double> input = new List <double>(); // входной вектор Dictionary <DateTime, double> solution = new Dictionary <DateTime, double>(); // прогноз var context = new TagDbContext(); var model = CreateSomModel_v2(data_tag); var data = context.TagValues .Where(t => t.Tag.TagName.Trim() == data_tag && (SqlFunctions.DatePart("weekday", t.DateTime) - 1) == (int)fdate.DayOfWeek) .OrderBy(v => v.DateTime) .Select(v => v.Value) .Take(24 + retrospective + 1) .AsEnumerable() .Reverse() .ToArray(); for (int i = 0; i < 24; i++) { var vector = data.Skip(i).Take(retrospective).ToArray(); var value = model.Compute(vector)[0]; solution[fdate.AddHours(i + 1)] = value; } ExportToDB(tag_name, solution, context); }
public async Task <bool> AnyEventsThisWeekByTypeAsync(IEnumerable <int> eventTypeIds) { return(await _eventsDbSet .AnyAsync(x => SqlFunctions.DatePart("wk", x.StartDate) == SqlFunctions.DatePart("wk", DateTime.UtcNow) && eventTypeIds.Contains(x.EventType.Id) && x.RegistrationDeadline > DateTime.UtcNow)); }
public List <QuyDinhDiemDTO> getQDD() { List <QuyDinhDiemDTO> quyDinhDiemDTOs = new List <QuyDinhDiemDTO>(); quyDinhDiemDTOs = (from qd in db.QUYDINH_DIEM from tg in db.THOIGIAN_XET from dv in db.DON_VI from ld in db.LOAI_DIEM from tc in db.TIEU_CHUAN where qd.MaLoaiDiem == ld.MaLoaiDiem && qd.MaDonVi == dv.MaDonVi && qd.Mathoigian == tg.MaThoiGian && qd.MaTieuChuan == tc.MaTieuChuan select new QuyDinhDiemDTO { MaQuyDinhDiem = qd.MaQuyDinhDiem, TenLoaiDiem = ld.TenLoaiDiem, DiemToiThieu = qd.DiemToiThieu, DonVi = qd.MaDonVi, TenTieuChuan = tc.TenTieuChuan, ThoiGian = string.Concat( SqlFunctions.DatePart("day", tg.TuNgay).ToString().Trim() + "/" + SqlFunctions.DatePart("month", tg.TuNgay).ToString().Trim() + "/" + SqlFunctions.DatePart("year", tg.TuNgay).ToString().Trim(), "_", SqlFunctions.DatePart("day", tg.DenNgay).ToString().Trim() + "/" + SqlFunctions.DatePart("month", tg.DenNgay).ToString().Trim() + "/" + SqlFunctions.DatePart("year", tg.DenNgay).ToString().Trim()), TrangThai = qd.TrangThai }).ToList(); return(quyDinhDiemDTOs); }
public List <ThucHien_TieuChuanDTO> getTH_TC() { List <ThucHien_TieuChuanDTO> thucHien_TieuChuanDTOs = new List <ThucHien_TieuChuanDTO>(); thucHien_TieuChuanDTOs = (from thtc in db.THUCHIEN_TIEUCHUAN from tg in db.THOIGIAN_XET from dv in db.DON_VI from sv in db.SINH_VIEN from tc in db.TIEU_CHUAN where thtc.Mssv == sv.Mssv && sv.DonVi == dv.MaDonVi && thtc.MaThoiGian == tg.MaThoiGian && thtc.MaTieuChuan == tc.MaTieuChuan select new ThucHien_TieuChuanDTO { Mssv = thtc.Mssv, TenSinhVien = sv.HoTen, DonVi = dv.MaDonVi, TenTieuChuan = tc.TenTieuChuan, ThoiGian = string.Concat( SqlFunctions.DatePart("day", tg.TuNgay).ToString().Trim() + "/" + SqlFunctions.DatePart("month", tg.TuNgay).ToString().Trim() + "/" + SqlFunctions.DatePart("year", tg.TuNgay).ToString().Trim(), "_", SqlFunctions.DatePart("day", tg.DenNgay).ToString().Trim() + "/" + SqlFunctions.DatePart("month", tg.DenNgay).ToString().Trim() + "/" + SqlFunctions.DatePart("year", tg.DenNgay).ToString().Trim()) }).ToList(); return(thucHien_TieuChuanDTOs); }
public IEnumerable <ShiftDTO> GetConflictingShifts(Guid rosterId, DayOfWeek day) { if (ClaimsAuthorization.CheckAccess("Put", "BusinessLocationId", db.Rosters.Find(rosterId).BusinessLocation.Id.ToString())) { List <ShiftDTO> conflictingShifts = new List <ShiftDTO>(); int dow = (int)day + 1; // SQL Day of week var conflicts = (from s1 in db.Shifts from s2 in db.Shifts where s1.Id != s2.Id && s1.Employee == s2.Employee && s1.Roster.Id == rosterId && s2.Roster.Id == rosterId && SqlFunctions.DatePart("weekday", s1.StartTime) == dow && SqlFunctions.DatePart("weekday", s2.StartTime) == dow && s1.StartTime <= s2.FinishTime && s1.FinishTime >= s2.FinishTime && s1.IsPublished == false && s1.Employee != null select s1); var conflictingShiftsForDay = MapperFacade.MapperConfiguration.Map <IEnumerable <Shift>, IEnumerable <ShiftDTO> >(conflicts); return(conflictingShiftsForDay); } else { throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.Unauthorized)); } }
private void Load_Data() { txt_total_money.Text = DAL_Thongke.Get_Total_Money().ToString(); txt_total_paid_money.Text = DAL_Thongke.Get_Total_Money_Paid("0").ToString(); txt_interset_money.Text = DAL_Thongke.Get_Total_Money_Paid("1").ToString(); var lst_customer = (from data in _db.customers group data by new { Column1 = (int?)SqlFunctions.DatePart("mm", data.CreatedAt) } into g select new { month = g.Key.Column1, count = g.Count(data => data.id != null) } ).OrderBy(g => g.month).ToList(); chart_customer_by_month.DataSource = lst_customer; DataTable table = new DataTable("Table1"); // Add three columns to the table. table.Columns.Add("Month", typeof(String)); table.Columns.Add("Section", typeof(String)); table.Columns.Add("Value", typeof(Int32)); // Add data rows to the table. table.Rows.Add(new object[] { "", "2", 1 }); table.Rows.Add(new object[] { "", "3", 9 }); chart_customer_by_month.DataSource = table; chart_customer_by_month.SeriesDataMember = "Month"; chart_customer_by_month.SeriesTemplate.ArgumentDataMember = "Section"; chart_customer_by_month.SeriesTemplate.ValueDataMembers.AddRange(new String[] { "Value" }); chart_customer_by_month.SeriesNameTemplate.BeginText = "Month: "; }
public List <IncidentChartModel> GetIncidents() { try { // getWeeklyIncidents(); IncidentEntities db = new IncidentEntities(); // IncidentEntities db = new IncidentEntities(); // var incidents = db.ActMonitors.ToList(); List <IncidentChartModel> lstChartModel = new List <IncidentChartModel>(); var incidents1 = db.ActMonitors.GroupBy(x => SqlFunctions.DatePart("ww", x.Actual_Date_and_Time_of_Completion)); var incidents = (from x in db.ActMonitors join y in db.ActivityTypes on x.Type_of_Activity equals y.ID select new { x.Status, x.Location, y.ActivityType1 }).ToList(); foreach (var item in incidents) { IncidentChartModel chart = new IncidentChartModel(); chart.Status = item.Status; chart.Location = item.Location; chart.ActivityType = item.ActivityType1; lstChartModel.Add(chart); } return(lstChartModel); } catch (Exception ex) { throw; } }
public CatWeekDateReport GetCatReportWeekDate(int catId, DateTime?from, DateTime?to) { var _from = from; var firstMonday = GetFirstMonday(); var cat = _context.VideoCategories.Find(catId); var totalIPViewCount = _context.CategoryAccesses.Where(ca => ca.CategoryId == catId && (!from.HasValue || ca.Date >= from) && (!to.HasValue || ca.Date <= to)).Sum(ca => ca.IPViewCount); var daysOfWeek = Enum.GetValues(typeof(DayOfWeek)).OfType <DayOfWeek>().ToList(); var data = (from dow in daysOfWeek from ca in _context.CategoryAccesses.Where(ca => SqlFunctions.DatePart("dw", ca.Date) - 1 == (int)dow && ca.CategoryId == catId && (!_from.HasValue || ca.Date >= _from) && (!to.HasValue || ca.Date <= to)).DefaultIfEmpty() select new { dow, ca }) .GroupBy(x => x.dow) .Select(g => new CatWeekDate { DOW = g.Key, IPViewCount = g.Sum(x => x.ca == null? 0 : x.ca.IPViewCount), PageViewCount = g.Sum(x => x.ca == null? 0 : x.ca.PageViewCount) }).OrderBy(cwd => cwd.DOW).ToList(); foreach (var catWeekDate in data) { catWeekDate.PercentIPViewCount = catWeekDate.IPViewCount * 100D / totalIPViewCount; } var report = new CatWeekDateReport { CatId = cat.Id, CatName = cat.Name, Data = data }; return(report); }
public CatWeekAvgChangeReport GetCatReportWeekAvgChange(int catId, DateTime from, DateTime to, double avgWeek) { var _from = from; var avgDay = avgWeek / 7; var cat = _context.VideoCategories.Find(catId); var totalIPViewCount = _context.CategoryAccesses.Where(ca => ca.CategoryId == catId && ca.Date >= from && ca.Date <= to).Sum(ca => ca.IPViewCount); var daysOfWeek = Enum.GetValues(typeof(DayOfWeek)).OfType <DayOfWeek>().ToList(); var data = (from dow in daysOfWeek from ca in _context.CategoryAccesses.Where(ca => SqlFunctions.DatePart("dw", ca.Date) - 1 == (int)dow && ca.CategoryId == catId && ca.Date >= _from && ca.Date <= to).DefaultIfEmpty() select new { dow, ca }) .GroupBy(x => x.dow) .Select(g => new CatWeekAvgChange { DOW = g.Key, IPViewCount = g.Sum(x => x.ca == null ? 0 : x.ca.IPViewCount), }).OrderBy(cwd => cwd.DOW).ToList(); foreach (var catWeekChange in data) { catWeekChange.Change = Math.Round(catWeekChange.IPViewCount - avgDay, 2); } var report = new CatWeekAvgChangeReport { CatId = cat.Id, CatName = cat.Name, Total = totalIPViewCount, TotalChange = Math.Round(totalIPViewCount - avgWeek, 2), Data = data }; return(report); }
public JsonResult SalaryJson(int ID, int?Month, int?Year) { User user = HttpContext.CurrentUser(); int userID = user.ID; Employee employee = db.Employees.FirstOrDefault(val => val.ID == ID); if (user.RoleID == (int)RolesEnum.Employee && (user.EmployeeID != ID || employee.UserID != user.ID)) { return(Json(new { ID = ID, Success = false, Error = "Not allowed." })); } if (user.RoleID == (int)RolesEnum.Manager && employee.User != null && employee.User.RoleID <= (int)RolesEnum.Manager && (user.EmployeeID != ID || employee.UserID != user.ID)) { return(Json(new { ID = ID, Success = false, Error = "Not allowed." })); } List <Expense> expenses = db.Expenses.Where(val => val.Type.ForSalary && val.SalaryEmployeeID == ID && (!Month.HasValue || SqlFunctions.DatePart("MONTH", val.Date) == Month) && (!Year.HasValue || SqlFunctions.DatePart("YEAR", val.Date) == Year)).OrderBy(val => val.Date).ToList(); List <Payroll> payrolls = db.Payrolls.Where(val => val.EmployeeID == ID && (!Month.HasValue || SqlFunctions.DatePart("MONTH", val.Date) == Month) && (!Year.HasValue || SqlFunctions.DatePart("YEAR", val.Date) == Year)).OrderBy(val => val.Date).ToList(); List <Models.Reports.SalaryRow> rows = new List <Models.Reports.SalaryRow>(expenses.Select(val => new Models.Reports.SalaryRow(val))); rows.AddRange(payrolls.Select(val => new Models.Reports.SalaryRow(val))); return(Json(new { Success = true, Rows = rows.OrderBy(val => val.Date).ThenBy(val => val.ID).ToList() })); }
public ActionResult Index(int?ID) { string settingsName = "/Employee/Index"; User user = HttpContext.CurrentUser(); int userID = user.ID; Employee employee = db.Employees.FirstOrDefault(val => val.ID == ID && !val.Deleted); if (!ID.HasValue && user.EmployeeID.HasValue || user.RoleID == (int)RolesEnum.Employee && (user.EmployeeID != ID || employee.UserID != user.ID)) { return(Redirect("~/Employee/Index/" + user.EmployeeID)); } if (user.RoleID == (int)RolesEnum.Manager && employee.User != null && employee.User.RoleID <= (int)RolesEnum.Manager && (user.EmployeeID != ID || employee.UserID != user.ID)) { return(HttpNotFound()); } int year = DateTime.Today.Year; int month = DateTime.Today.Month; List <int> years; string[] months = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.MonthNames; years = db.Expenses.Where(val => val.EmployeeID == ID).Select(val => SqlFunctions.DatePart("YEAR", val.Date)).Distinct().OrderByDescending(val => val).Select(val => val.Value).ToList(); years.AddRange(db.Transfers.Where(val => (val.WalletFrom.EmployeeID == ID || val.WalletTo.EmployeeID == ID)) .Select(val => SqlFunctions.DatePart("YEAR", val.Date)).Distinct().OrderByDescending(val => val).Select(val => val.Value).ToList()); for (int i = years.Any() ? years.First() : DateTime.Today.Year; i < DateTime.Today.Year + 2; i++) { if (!years.Contains(i)) { years.Add(i); } } years = years.Distinct().OrderByDescending(val => val).ToList(); List <Wallet> wallets = db.Wallets.Where(val => val.EmployeeID == ID || val.EmployeeWallets.Any(ew => ew.EmployeeID == ID && ew.Expense)).ToList(); employee.UpdateSalaryBalance(); db.SaveChanges(); object data = new { Year = year, Month = month, Years = years, Months = months, Employee = employee.ToJson(), Settings = db.UserSettings.Where(val => val.UserID == userID && val.Name.IndexOf(settingsName) == 0).ToList().Select(val => val.ToJson()).ToList(), Departments = db.Departments.Where(val => !val.Deleted || val.ID == employee.DepartmentID).OrderBy(val => val.OrderNumber).ToList().Select(val => val.ToJson()).ToList(), Positions = db.Positions.Where(val => !val.Deleted || val.ID == employee.PositionID).OrderBy(val => val.OrderNumber).ToList().Select(val => val.ToJson()).ToList() }; ViewBag.Employee = employee; ViewBag.Data = serializer.Serialize(data); ViewBag.Page = "Employee.Index"; return(View()); }
public static void ForecastAndExport_v2(string forecast_tag, string data_tag, int forecastPerspective = 2) { string tag_name = forecast_tag; DateTime fdate = DateTime.Today.AddDays(forecastPerspective); // дата прогноза List <double> input = new List <double>(); // входной вектор Dictionary <DateTime, double> solution = new Dictionary <DateTime, double>(); // прогноз var context = new TagDbContext(); //double yMax, yMin; //if (fdate.DayOfWeek == DayOfWeek.Saturday || fdate.DayOfWeek == DayOfWeek.Sunday) //составляем модель на субботу и воскресенье //{ // Console.WriteLine("Создание модели."); // var model = CreateTimeSeriesModel_v2(data_tag, out yMax, out yMin, fdate.DayOfWeek); // Console.WriteLine("Модели создана."); // Console.WriteLine("Подготовка входных данных."); // input = context.TagValues // .Where(t => t.Tag.TagName == data_tag && (SqlFunctions.DatePart("weekday", t.DateTime) - 1) == (int)fdate.DayOfWeek) // .OrderByDescending(t => t.DateTime) // .Select(v => (yMax - v.Value) / (yMax - yMin)) // .Take(retrospective) // .AsEnumerable() // .Reverse() // .ToList(); // Console.WriteLine("Входные данные готовы."); // Console.WriteLine("Прогнозирование."); // for (int i = 1; i <= 24; i++) // { // double model_result = model.Compute(input.ToArray())[0]; // double result = yMax - model_result * (yMax - yMin); // solution[fdate.AddHours(i)] = result; // input.RemoveAt(0); // input.Add(model_result); // } //} //else // составляем модель на будние дни //{ DateTime startDate = fdate.AddMonths(-1); //вычитаем var tender = context.TagValues .Where(tv => tv.Tag.TagName == data_tag && tv.DateTime > startDate && (SqlFunctions.DatePart("weekday", tv.DateTime) - 1) == (int)fdate.DayOfWeek) .GroupBy(tv => SqlFunctions.DatePart("Hour", tv.DateTime)) .Select(t => new { Hour = t.Key, Value = t.Average(tv => tv.Value) }) .ToArray(); foreach (var entity in tender) { solution[fdate.AddHours((int)entity.Hour)] = entity.Value; } //} Console.WriteLine("Запись прогноза..."); ExportToDB(tag_name, solution, context); }