public IQueryable <TimeSheetMasterView> ShowTimeSheetStatus(string sortColumn, string sortColumnDir, string Search, int UserID, int TimeSheetStatus) { var _context = new DatabaseContext(); var IQueryabletimesheet = (from timesheetmaster in _context.TimeSheetMaster where timesheetmaster.UserID == UserID && timesheetmaster.TimeSheetStatus == TimeSheetStatus select new TimeSheetMasterView { TimeSheetStatus = timesheetmaster.TimeSheetStatus == 1 ? "Submitted" : timesheetmaster.TimeSheetStatus == 2 ? "Approved" : "Rejected", Comment = timesheetmaster.Comment, TimeSheetMasterID = timesheetmaster.TimeSheetMasterID, FromDate = SqlFunctions.DateName("day", timesheetmaster.FromDate).Trim() + "/" + SqlFunctions.StringConvert((double)timesheetmaster.FromDate.Value.Month).TrimStart() + "/" + SqlFunctions.DateName("year", timesheetmaster.FromDate), ToDate = SqlFunctions.DateName("day", timesheetmaster.ToDate).Trim() + "/" + SqlFunctions.StringConvert((double)timesheetmaster.ToDate.Value.Month).TrimStart() + "/" + SqlFunctions.DateName("year", timesheetmaster.ToDate), CreatedOn = SqlFunctions.DateName("day", timesheetmaster.CreatedOn).Trim() + "/" + SqlFunctions.StringConvert((double)timesheetmaster.CreatedOn.Value.Month).TrimStart() + "/" + SqlFunctions.DateName("year", timesheetmaster.CreatedOn), TotalHours = timesheetmaster.TotalHours }); if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir))) { IQueryabletimesheet = IQueryabletimesheet.OrderBy(sortColumn + " " + sortColumnDir); } if (!string.IsNullOrEmpty(Search)) { IQueryabletimesheet = IQueryabletimesheet.Where(m => m.FromDate == Search); } return(IQueryabletimesheet); }
public List <ApplicationViewModel> GetList() { List <ApplicationViewModel> result = context.Applications.Select(rec => new ApplicationViewModel { ApplicationId = rec.ApplicationId, ResourseId = rec.ResourseId, Count = rec.Count, Summa = rec.Sum, Status = rec.Status.ToString(), DateCreate = SqlFunctions.DateName("dd", rec.DateCreate) + " " + SqlFunctions.DateName("mm", rec.DateCreate) + " " + SqlFunctions.DateName("yyyy", rec.DateCreate), DateImplement = rec.DateImplement == null ? "" : SqlFunctions.DateName("dd", rec.DateImplement.Value) + " " + SqlFunctions.DateName("mm", rec.DateImplement.Value) + " " + SqlFunctions.DateName("yyyy", rec.DateImplement.Value), ResourseName = rec.Resourse.ResourseName }) .ToList(); return(result); }
public ActionResult reporteDUp(int Id) { List <ReporteDUpItem> lst = null; using (AppDBContext db = new AppDBContext()) { lst = (from d in db.Documentos from u in db.Usuarios where d.idFlujoDocumento == null && d.idUsuario == u.idUsuario && d.idUsuario == Id select new ReporteDUpItem { Documento = d.nombreDocumentos, Fecha = SqlFunctions.DateName("day", d.created_at) + "/" + SqlFunctions.DateName("month", d.created_at) + "/" + SqlFunctions.DateName("year", d.created_at), Usuario = u.nombre + " " + u.apellido }).ToList(); } //Creamos el PDF de la vista html var reporte = new ViewAsPdf("reporteDUp", lst) { PageSize = Rotativa.Options.Size.Letter, PageMargins = new Rotativa.Options.Margins(10, 20, 20, 20) }; return(reporte); }
public List <OrderViewModel> GetList() { List <OrderViewModel> result = context.Orders.Select(rec => new OrderViewModel { Id = rec.Id, AdminId = rec.AdminId, OrderListId = rec.OrderListId, DateCreate = SqlFunctions.DateName("dd", rec.DateCreate) + " " + SqlFunctions.DateName("mm", rec.DateCreate) + " " + SqlFunctions.DateName("yyyy", rec.DateCreate), DateImplement = rec.DateImplement == null ? "" : SqlFunctions.DateName("dd", rec.DateImplement.Value) + " " + SqlFunctions.DateName("mm", rec.DateImplement.Value) + " " + SqlFunctions.DateName("yyyy", rec.DateImplement.Value), Status = rec.Status.ToString(), Count = rec.Count, Sum = rec.Sum, AdminFIO = rec.Admin.AdminFIO, OrderListName = rec.OrderList.OrderListName }) .ToList(); return(result); }
public List <OrderViewModel> GetList() { List <OrderViewModel> result = context.Orders .Select(rec => new OrderViewModel { id = rec.id, clientId = rec.clientId, DateCreate = SqlFunctions.DateName("dd", rec.DateCreate) + " " + SqlFunctions.DateName("mm", rec.DateCreate) + " " + SqlFunctions.DateName("yyyy", rec.DateCreate), status = rec.status.ToString(), clientName = rec.client.clientFirstName, services = rec.orderServices.Select(os => new ServiceViewModel { id = os.serviceId, serviceName = os.service.serviceName, price = os.service.price, ServiceResources = os.service.serviceResources.Select(r => new ServiceResourceViewModel { id = r.id, resourceId = r.resourceId, resourceName = r.resource.resourceName, }).ToList() }).ToList(), number = rec.number }) .ToList(); return(result); }
public string GetDATASO(string BASE) //ข้อมูล P1 || P2 { var dt = DateTime.UtcNow.Date.AddDays(-2); var Get = (from M_SO in DbFile.OESO join M_CUSs in DbFile.ARMAS on M_SO.CUSCOD equals M_CUSs.cuscod into M_CUS1 from M_CUS in M_CUS1.DefaultIfEmpty() where M_SO.SODAT >= dt orderby M_SO.SONUM ascending select new { M_SO.SONUM, //M_SO.SODAT, SODAT = SqlFunctions.Replicate("0", 2 - SqlFunctions.DateName("dd", M_SO.SODAT).Trim().Length) + SqlFunctions.DateName("dd", M_SO.SODAT) + "/" + // SqlFunctions.Replicate("/", 2 - SqlFunctions.StringConvert((double)M_SO.SODAT.Value.Month).TrimStart().Length) + SqlFunctions.Replicate("0", 2 - SqlFunctions.StringConvert((double)M_SO.SODAT.Value.Month).TrimStart().Length) + SqlFunctions.StringConvert((double)M_SO.SODAT.Value.Month).TrimStart() + "/" + // SqlFunctions.Replicate("/", 2 - SqlFunctions.StringConvert((double)M_SO.SODAT.Value.Month).TrimStart().Length) + SqlFunctions.DateName("year", M_SO.SODAT).Trim(), M_CUS.cusnam }).ToList(); string jsonlog = new JavaScriptSerializer().Serialize(Get); return(jsonlog); // return View(); }
public List <FridgeViewModel> GetList() { List <FridgeViewModel> result = context.Fridges.Select(rec => new FridgeViewModel { Id = rec.Id, FridgeName = rec.FridgeName, FridgeProducts = context.FridgeProducts .Where(recPC => recPC.FridgeId == rec.Id) .Select(recPC => new FridgeProductViewModel { Id = recPC.Id, FridgeId = recPC.FridgeId, ProductId = recPC.ProductId, ProductName = recPC.Product.ProductName, Count = recPC.Count, ReceiptDate = SqlFunctions.DateName("dd", recPC.ReceiptDate) + " " + SqlFunctions.DateName("mm", recPC.ReceiptDate) + " " + SqlFunctions.DateName("yyyy", recPC.ReceiptDate), FreshDate = recPC.FreshDate, DateNotFresh = recPC.DateNotFresh == null ? "" : SqlFunctions.DateName("dd", recPC.DateNotFresh.Value) + " " + SqlFunctions.DateName("mm", recPC.DateNotFresh.Value) + " " + SqlFunctions.DateName("yyyy", recPC.DateNotFresh.Value), FreshStatus = recPC.FreshStatus.ToString() }) .ToList() }) .ToList(); return(result); }
private IQueryable <ProjectView> SelectProjectView(Expression <Func <ProjectView, bool> > Expression, string Sorting = null, string Filtering = null) { var Query = from tbl in DbSet .Include(c => c.Customer) .Include(c => c.Department) .Include(c => c.ProjectType) .Include(c => c.FocalPoint) .Include(c => c.CompanyFocalPoint) let ptCount = (from pt in Context.Set <ProjectTechnology>() where tbl.ID == pt.ProjectID select pt).Count() select new ProjectView() { ID = tbl.ID, Name = tbl.Name, CustomerID = tbl.CustomerID, CustomerName = tbl.Customer.Name, DepartmentID = tbl.DepartmentID, DepartmentName = tbl.Department.Name, ProjectTypeID = tbl.ProjectTypeID, ProjectTypeName = tbl.ProjectType.Name, FocalPointID = tbl.FocalPointID, FocalPointName = tbl.FocalPoint.FirstName + " " + tbl.FocalPoint.LastName, CompanyFocalPointID = tbl.CompanyFocalPointID, CompanyFocalPointName = tbl.CompanyFocalPoint.FirstName + " " + tbl.CompanyFocalPoint.LastName, EndDate = tbl.EndDate, IsActive = tbl.IsActive, Risk = tbl.Risk, IsAllowDelete = ptCount == 0 }; if ((Filtering ?? "") != "") { Expression = Expression.And(tbl => tbl.ID.ToString().Contains(Filtering) || tbl.Name.ToString().Contains(Filtering) || tbl.CustomerName.ToString().Contains(Filtering) || tbl.DepartmentName.ToString().Contains(Filtering) || tbl.ProjectTypeName.ToString().Contains(Filtering) || tbl.FocalPointName.ToString().Contains(Filtering) || ((SqlFunctions.DateName("mm", tbl.EndDate).Substring(0, 3).ToString() + " " + SqlFunctions.DateName("dd", tbl.EndDate) + " ," + SqlFunctions.DateName("yyyy", tbl.EndDate)).ToString()).Contains(Filtering) || (tbl.IsActive == true ? "true" : "false").Contains(Filtering) || tbl.Risk.ToString().Contains(Filtering) ); } if (Expression != null) { Query = Query.Where(Expression); } if (Sorting != null) { if (!Sorting.StartsWith("ID")) { Sorting = Sorting.Replace("ID", "Name"); } Query = Query.GetOrderByQuery(Sorting); } return(Query); }
public List <ModelBookingView> getList() { List <ModelBookingView> result = context.Bookings .Select(rec => new ModelBookingView { ID = rec.ID, CustomerID = rec.CustomerID, OutputID = rec.OutputID, ExecutorID = rec.ExecutorID, DateOfCreate = SqlFunctions.DateName("dd", rec.DateOfCreate) + " " + SqlFunctions.DateName("mm", rec.DateOfCreate) + " " + SqlFunctions.DateName("yyyy", rec.DateOfCreate), DateOfImplement = rec.DateOfImplement == null ? "" : SqlFunctions.DateName("dd", rec.DateOfImplement.Value) + " " + SqlFunctions.DateName("mm", rec.DateOfImplement.Value) + " " + SqlFunctions.DateName("yyyy", rec.DateOfImplement.Value), Status = rec.Status.ToString(), Count = rec.Count, Summa = rec.Summa, CustomerFullName = rec.Customer.CustomerFullName, OutputName = rec.Output.OutputName, ExecutorName = rec.Executor.ExecutorFullName }) .ToList(); return(result); }
/// <summary> /// 独立新访客 /// </summary> public ChartItemEntity GetNewUV(DateTime start, DateTime end) { var ed = end.AddDays(1); var onlyOne = from x in this.DbSet where (x.VisitTime < ed) group x by x.VisitId into g where g.Count() == 1 select new { VisitId = g.Key }; var query = GetDataSource(start, end); var newuv = (from x in query where (from y in onlyOne where y.VisitId == x.VisitId select y).Any() group x by new { Year = SqlFunctions.DateName("yyyy", x.VisitTime), Month = SqlFunctions.DateName("MM", x.VisitTime), Day = SqlFunctions.DateName("dd", x.VisitTime), x.VisitId } into g select new { Name = g.Key.Year + "-" + g.Key.Month + "-" + g.Key.Day + "-" + g.Key.VisitId, Data = g.Count() }).Count(); return(new ChartItemEntity { Name = "NEWUV", Data = newuv }); }
public List <TimeSheetDetailsView> TimesheetDetailsbyTimeSheetMasterID(int TimeSheetMasterID) { using (var _context = new DatabaseContext()) { var data = (from timesheet in _context.TimeSheetDetails join project in _context.ProjectMaster on timesheet.ProjectID equals project.ProjectID where timesheet.TimeSheetMasterID == TimeSheetMasterID select new TimeSheetDetailsView { TimeSheetID = timesheet.TimeSheetID, CreatedOn = SqlFunctions.DateName("day", timesheet.CreatedOn).Trim() + "/" + SqlFunctions.StringConvert((double)timesheet.CreatedOn.Value.Month).TrimStart() + "/" + SqlFunctions.DateName("year", timesheet.CreatedOn), Period = SqlFunctions.DateName("day", timesheet.Period).Trim() + "/" + SqlFunctions.StringConvert((double)timesheet.Period.Value.Month).TrimStart() + "/" + SqlFunctions.DateName("year", timesheet.Period), DaysofWeek = timesheet.DaysofWeek, Hours = timesheet.Hours, ProjectName = project.ProjectName, TimeSheetMasterID = timesheet.TimeSheetMasterID }).ToList(); return(data); } }
public List <ClientOrdersViewModel> GetClientOrders(ReportBindingModel model, int clientId) { return(context.Orders .Where(rec => rec.DateCreate >= model.DateFrom && rec.DateCreate <= model.DateTo && rec.ClientId == clientId) .Select(rec => new ClientOrdersViewModel { ClientName = rec.Client.ClientFIO, DateCreateOrder = SqlFunctions.DateName("dd", rec.DateCreate) + " " + SqlFunctions.DateName("mm", rec.DateCreate) + " " + SqlFunctions.DateName("yyyy", rec.DateCreate), TotalSum = rec.TotalSum, StatusOrder = rec.OrderStatus.ToString(), OrderCars = context.OrderCars.Where(recPC => recPC.OrderId == rec.Id) .Select(recPC => new OrderCarViewModel { Id = recPC.Id, CarId = recPC.CarId, OrderId = recPC.OrderId, CarName = recPC.Car.CarName, Amount = recPC.Amount }) .ToList() }) .ToList()); }
/// <summary> /// The ShowNotifications /// </summary> /// <param name="sortColumn">The sortColumn<see cref="string"/></param> /// <param name="sortColumnDir">The sortColumnDir<see cref="string"/></param> /// <param name="Search">The Search<see cref="string"/></param> /// <returns>The <see cref="IQueryable{NotificationsTB_ViewModel}"/></returns> public IQueryable <NotificationsTB_ViewModel> ShowNotifications(string sortColumn, string sortColumnDir, string Search) { var _context = new DatabaseContext(); var IQueryableNotifications = (from notification in _context.NotificationsTBs select new NotificationsTB_ViewModel { CreatedOn = SqlFunctions.DateName("day", notification.CreatedOn).Trim() + "/" + SqlFunctions.StringConvert((double)notification.CreatedOn.Value.Month).TrimStart() + "/" + SqlFunctions.DateName("year", notification.CreatedOn), Message = notification.Message, NotificationsID = notification.NotificationsID, Status = notification.Status, FromDate = SqlFunctions.DateName("day", notification.FromDate).Trim() + "/" + SqlFunctions.StringConvert((double)notification.FromDate.Value.Month).TrimStart() + "/" + SqlFunctions.DateName("year", notification.FromDate) + " " + SqlFunctions.DateName("hh", notification.FromDate).Trim() + ":" + SqlFunctions.DateName("n", notification.FromDate).Trim() + ":" + SqlFunctions.DateName("s", notification.FromDate).Trim(), ToDate = SqlFunctions.DateName("day", notification.ToDate).Trim() + "/" + SqlFunctions.StringConvert((double)notification.ToDate.Value.Month).TrimStart() + "/" + SqlFunctions.DateName("year", notification.ToDate) + " " + SqlFunctions.DateName("hh", notification.ToDate).Trim() + ":" + SqlFunctions.DateName("n", notification.ToDate).Trim() + ":" + SqlFunctions.DateName("s", notification.ToDate).Trim(), Min = SqlFunctions.DateDiff("n", notification.FromDate.ToString(), notification.ToDate.ToString()) }); if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir))) { IQueryableNotifications = IQueryableNotifications.OrderBy(sortColumn + " " + sortColumnDir); } if (!string.IsNullOrEmpty(Search)) { IQueryableNotifications = IQueryableNotifications.Where(m => m.Message == Search || m.Message == Search); } return(IQueryableNotifications); }
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 List <OrderViewModel> GetList() { var result = context.Orders.Select(rec => new OrderViewModel { Id = rec.Id, ClientId = rec.ClientId, DateCreate = SqlFunctions.DateName("dd", rec.DateCreate) + " " + SqlFunctions.DateName("mm", rec.DateCreate) + " " + SqlFunctions.DateName("yyyy", rec.DateCreate), DateImplement = rec.DateImplement == null ? "" : SqlFunctions.DateName("dd", rec.DateImplement.Value) + " " + SqlFunctions.DateName("mm", rec.DateImplement.Value) + " " + SqlFunctions.DateName("yyyy", rec.DateImplement.Value), StatusOrder = rec.OrderStatus.ToString(), TotalSum = rec.TotalSum, ClientFIO = rec.Client.ClientFIO, OrderCars = context.OrderCars.Where(recPC => recPC.OrderId == rec.Id) .Select(recPC => new OrderCarViewModel { Id = recPC.Id, CarId = recPC.CarId, OrderId = recPC.OrderId, CarName = recPC.Car.CarName, Amount = recPC.Amount }) .ToList() }) .ToList(); return(result); }
/// <summary> /// get Material for datatable flow pagging /// </summary> /// <param name="search"></param> /// <param name="start"></param> /// <param name="length"></param> /// <param name="sortColumn"></param> /// <param name="sortColumnDir"></param> /// <returns></returns> public List <object> getPartyForDatatablePagging(string search, int start, int length, string sortColumn, string sortColumnDir, bool isSearchDate, DateTime valueDate) { try { using (var context = (ConnectionEFDataFirst)Activator.CreateInstance(typeof(ConnectionEFDataFirst), _connectionStr)) { List <object> getData = new List <object>(); //check data search = String.IsNullOrWhiteSpace(search) ? "" : search; sortColumn = String.IsNullOrWhiteSpace(sortColumn) ? "" : sortColumn; sortColumnDir = String.IsNullOrWhiteSpace(sortColumnDir) ? "" : sortColumnDir; DateTime startDate = new DateTime(valueDate.Year, valueDate.Month, valueDate.Day, 0, 0, 0, 0); DateTime endDate = new DateTime(valueDate.Year, valueDate.Month, valueDate.Day, 23, 59, 59, 999); //excute query getData = (from u in context.TBL_PARTY where (!isSearchDate && (search == "" || u.CustomerName.Contains(search)) || (isSearchDate && u.PartyDate >= startDate && u.PartyDate <= endDate)) select new { u.PartyID, PartyDate = SqlFunctions.DateName("day", u.PartyDate) + "/" + SqlFunctions.StringConvert((double)u.PartyDate.Month).TrimStart() + "/" + SqlFunctions.DateName("year", u.PartyDate) + " " + SqlFunctions.DateName("hour", u.PartyDate) + ":" + SqlFunctions.DateName("minute", u.PartyDate), u.CustomerName, u.NumberTablePlan, u.NumberTableException, u.NumberTableVegetarian }) .OrderBy((sortColumn == "" && sortColumnDir == "") ? "CustomerName asc" : sortColumn + " " + sortColumnDir).Skip(start).Take(length).ToList <object>(); return(getData); } } catch (Exception ex) { return(new List <object>()); } }
public ContentResult HoursWorkedsData(HoursWorked filter) { var hoursWorkeds = db.HoursWorkeds.Include(h => h.Employee).Include(h => h.Job); var hoursWorkedsData = (from hrs in hoursWorkeds where (hrs.Employee.EmployeeID == filter.EmployeeID || filter.EmployeeID == 0) && (hrs.Job.JobID == filter.JobID || filter.JobID == 0) && (hrs.ItemNumber == filter.ItemNumber || filter.ItemNumber == null) && (hrs.HoursWorkedRT == filter.HoursWorkedRT || filter.HoursWorkedRT == null) && (hrs.HoursWorkedOT == filter.HoursWorkedOT || filter.HoursWorkedOT == null) && (hrs.JobDescription == filter.JobDescription || filter.JobDescription == null) select new { hrs.HoursWorkedID, EmployeeID = hrs.Employee.EmployeeID, JobID = hrs.Job.JobID, hrs.ItemNumber, Date = SqlFunctions.DateName("mm", hrs.Date) + " " + SqlFunctions.DateName("day", hrs.Date) + ", " + SqlFunctions.DateName("year", hrs.Date), hrs.HoursWorkedRT, hrs.HoursWorkedOT, hrs.JobDescription }).ToList(); JavaScriptSerializer jsonSerializer = new JavaScriptSerializer(); string output = jsonSerializer.Serialize(hoursWorkedsData); return(Content(output, "application/json")); }
public TaskEntity GetTaskById(int taskId) { var taskFromDb = (from t in _db.T_TASK join p in _db.T_PARENT_TASK on t.PARENT_TASK_ID equals p.PARENT_TASK_ID into parents from parent in parents.DefaultIfEmpty() join pr in _db.T_PROJECT on t.PROJ_ID equals pr.PROJ_ID join u in _db.T_USER on t.USR_ID equals u.EMP_ID where t.TASK_ID == taskId select new TaskEntity { TaskId = t.TASK_ID, TaskName = t.TASK_NM, ParentId = t.PARENT_TASK_ID != null ? t.PARENT_TASK_ID.Value : 0, ParentName = parent.PARENT_TASK_NM, Priority = t.PRIORITY, StartDate = (t.STRT_DT != null ? SqlFunctions.DateName("day", t.STRT_DT) + "/" + SqlFunctions.DateName("month", t.STRT_DT) + "/" + SqlFunctions.DateName("year", t.STRT_DT) : ""), EndDate = (t.END_DT != null ? SqlFunctions.DateName("day", t.END_DT) + "/" + SqlFunctions.DateName("month", t.END_DT) + "/" + SqlFunctions.DateName("year", t.END_DT) : ""), ProjectId = t.PROJ_ID, ProjectName = pr.PROJ_NM, UserId = t.USR_ID, UserName = u.EMP_FRST_NM + " " + u.EMP_LST_NM, TaskStatus = t.STATUS }).SingleOrDefault(); return(taskFromDb); }
public IEnumerable <Object> GetByFiltering(int StartIndex, int Count, string Sorting, string Filtering, out int CountRecords) { IEnumerable <Object> ListCP = null; CountRecords = 0; Expression <Func <CP, bool> > ExpressionFilter = null; try { if ((Filtering ?? "") != "") { ExpressionFilter = (TableCp => TableCp.Milestone.Name.Contains(Filtering) || TableCp.CPID.ToString().Contains(Filtering) || ((SqlFunctions.DateName("mm", TableCp.ExpirationDate).Substring(0, 3).ToString() + " " + SqlFunctions.DateName("dd", TableCp.ExpirationDate) + " ," + SqlFunctions.DateName("yyyy", TableCp.ExpirationDate)).ToString()).Contains(Filtering) || ((SqlFunctions.DateName("mm", TableCp.EffectiveDate).Substring(0, 3).ToString() + " " + SqlFunctions.DateName("dd", TableCp.EffectiveDate) + " ," + SqlFunctions.DateName("yyyy", TableCp.EffectiveDate)).ToString()).Contains(Filtering) || TableCp.Order.ToString().Contains(Filtering) || (TableCp.IsDeleted == true ? "true" : "false").Contains(Filtering) || TableCp.Name.Contains(Filtering)); } var query = SelectCPView(ExpressionFilter, Sorting); string sqlstring = query.ToString(); query = query.Get(StartIndex, Count, out CountRecords); ListCP = query.ToList(); } catch (Exception ex) { Errors.Write(ex); } return(ListCP); }
public List <OrderViewModel> GetList() { List <OrderViewModel> result = context.Orders.Select(rec => new OrderViewModel { Id = rec.Id, ClientId = rec.ClientId, DateCreate = SqlFunctions.DateName("dd", rec.DateCreate) + " " + SqlFunctions.DateName("mm", rec.DateCreate) + " " + SqlFunctions.DateName("yyyy", rec.DateCreate), DateImplement = rec.DateImplement == null ? "" : SqlFunctions.DateName("dd", rec.DateImplement.Value) + " " + SqlFunctions.DateName("mm", rec.DateImplement.Value) + " " + SqlFunctions.DateName("yyyy", rec.DateImplement.Value), Status = rec.Status.ToString(), Sum = rec.Sum, ClientName = rec.Client.Name, Reserved = rec.Reserved }) .ToList(); foreach (var o in result) { o.OrderProducts = context.OrderProducts.Where(op => op.OrderId == o.Id).Select(op => new OrderProductViewModel { Id = op.Id, OrderId = op.Order.Id, ProductId = op.Product.Id, ProductName = op.Product.Name, Count = op.Count } ).ToList(); } return(result); }
public OrderViewModel GetElement(int id) { var o = context.Orders.Where(e => e.Id == id).Select(rec => new OrderViewModel { Id = rec.Id, ClientId = rec.ClientId, DateCreate = SqlFunctions.DateName("dd", rec.DateCreate) + " " + SqlFunctions.DateName("mm", rec.DateCreate) + " " + SqlFunctions.DateName("yyyy", rec.DateCreate), DateImplement = rec.DateImplement == null ? "" : SqlFunctions.DateName("dd", rec.DateImplement.Value) + " " + SqlFunctions.DateName("mm", rec.DateImplement.Value) + " " + SqlFunctions.DateName("yyyy", rec.DateImplement.Value), Status = rec.Status.ToString(), Sum = rec.Sum, ClientName = rec.Client.Name, Reserved = rec.Reserved, OrderProducts = context.OrderProducts .Where(e => e.OrderId == id) .Select(op => new OrderProductViewModel { Id = op.Id, OrderId = op.Order.Id, ProductId = op.Product.Id, ProductName = op.Product.Name, Count = op.Count }).ToList() }); return(o.First()); }
//找所有USER public object AllUser() { var User = practiceEntities.Members.Select(x => new { Account = x.Account, Password = x.Password, Name = x.Name, Phone = x.Phone, Tel = x.Tel, Gender = x.Gender, Birthday = SqlFunctions.DateName("year", x.Birthday) + "/" + x.Birthday.Value.Month + "/" + SqlFunctions.DateName("day", x.Birthday) // ,Idx = index + 1 }).ToList(); var result = User.AsEnumerable() .Select((x, index) => new { Account = x.Account, Password = x.Password, Name = x.Name, Phone = x.Phone, Tel = x.Tel, Gender = x.Gender, Birthday = x.Birthday, Idx = index + 1 }).ToList(); return(User); }
public IEnumerable <ResponseComandaDto> GetComandaList() { var result = DbContext.Comanda.Select(x => new CustomerOrder.Service.DTO.ResponseComandaDto() { IdComanda = x.idComanda, ClientEmail = (from client in DbContext.client where client.idClient == x.idClient select client.email).FirstOrDefault(), EmployeeName = (from employee in DbContext.employee where employee.idEmployee == x.idEmployee select employee.Name).FirstOrDefault(), Telefon = new AddTelefonDto() { Brand = x.telefon.brand, Marca = x.telefon.marca }, IdUnicTelefon = x.idUnicTelefon, Stare = x.stare, DataDeschidere = x.DataDeschidere != null ? SqlFunctions.DateName("dd", x.DataDeschidere) + " " + SqlFunctions.DateName("MM", x.DataDeschidere) + " " + SqlFunctions.DateName("yyyy", x.DataDeschidere) : String.Empty, DataInchidere = x.DataInchidere != null ? SqlFunctions.DateName("dd", x.DataInchidere) + " " + SqlFunctions.DateName("MM", x.DataInchidere) + " " + SqlFunctions.DateName("yyyy", x.DataInchidere) : String.Empty, DefectiuneDtos = x.defectiune, }).OrderBy(x => x.DataInchidere).ToList(); return(result); }
public static string QueryToDateString(DateTime date) { return(SqlFunctions.DateName("year", date) + "-" + SqlFunctions.Replicate("0", 2 - SqlFunctions.StringConvert((double)date.Month).TrimStart().Length) + SqlFunctions.StringConvert((double)date.Month).TrimStart() + "-" + SqlFunctions.Replicate("0", 2 - SqlFunctions.DateName("dd", date).Trim().Length) + SqlFunctions.DateName("dd", date).Trim()); }
public List <RoleMgrModel> roleMgrQry(string codeRole, string roleAuthType, string isDIsabled, string vMemo, string cUpdUserID) { bool bCodeRole = StringUtil.isEmpty(codeRole); bool bRoleAuthType = StringUtil.isEmpty(roleAuthType); bool bisDIsabled = StringUtil.isEmpty(isDIsabled); bool bcUpdUserID = StringUtil.isEmpty(cUpdUserID); using (new TransactionScope( TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted })) { using (dbTreasuryEntities db = new dbTreasuryEntities()) { List <RoleMgrModel> roleList = (from role in db.CODE_ROLE join codeFlag in db.SYS_CODE.Where(x => x.CODE_TYPE == "IS_DISABLED") on role.IS_DISABLED equals codeFlag.CODE into psFlag from xFlag in psFlag.DefaultIfEmpty() join codeReview in db.SYS_CODE.Where(x => x.CODE_TYPE == "DATA_STATUS") on role.DATA_STATUS equals codeReview.CODE into psReview from xReview in psReview.DefaultIfEmpty() join codeAuthType in db.SYS_CODE.Where(x => x.CODE_TYPE == "ROLE_AUTH_TYPE") on role.ROLE_AUTH_TYPE equals codeAuthType.CODE into psAuthType from xAuthType in psAuthType.DefaultIfEmpty() where 1 == 1 & (bCodeRole || (role.ROLE_ID == codeRole.Trim())) & (bRoleAuthType || (role.ROLE_AUTH_TYPE == roleAuthType.Trim())) & (bisDIsabled || (role.IS_DISABLED == isDIsabled.Trim())) & (bcUpdUserID || (role.LAST_UPDATE_UID == cUpdUserID.Trim())) orderby xAuthType.ISORTBY, role.ROLE_NAME select new RoleMgrModel { cRoleID = role.ROLE_ID, cRoleName = role.ROLE_NAME.Trim(), roleAuthType = role.ROLE_AUTH_TYPE, roleAuthTypeDesc = (xAuthType == null ? String.Empty : xAuthType.CODE_VALUE), isDisabled = (xFlag == null ? String.Empty : xFlag.CODE_VALUE), vMemo = role.MEMO, freezeUid = role.FREEZE_UID == null ? "" : role.FREEZE_UID, cUpdUserID = role.LAST_UPDATE_UID == null ? "" : role.LAST_UPDATE_UID, cUpdDateTime = role.LAST_UPDATE_DT == null ? "" : SqlFunctions.DateName("year", role.LAST_UPDATE_DT) + "/" + SqlFunctions.DatePart("m", role.LAST_UPDATE_DT) + "/" + SqlFunctions.DateName("day", role.LAST_UPDATE_DT).Trim() + " " + SqlFunctions.DateName("hh", role.LAST_UPDATE_DT).Trim() + ":" + SqlFunctions.DateName("n", role.LAST_UPDATE_DT).Trim() + ":" + SqlFunctions.DateName("s", role.LAST_UPDATE_DT).Trim(), dataStatus = (xReview == null ? String.Empty : xReview.CODE_VALUE) }).ToList(); return(roleList); } } }
public JsonResult BuscarInventarioAsignado(string id = "", string filtro = "", int pagina = 1, int registrosPagina = 15, string tipo = "", string fechaInicio = "1990-01-01", string fechaFin = "3500-12-30") { DateTime FIn = Convert.ToDateTime(fechaInicio); DateTime FFin = Convert.ToDateTime(fechaFin); if (filtro != "") { if (registrosPagina == 0) { registrosPagina = (from ci in db.CasaInventario where ci.ci_articulo_id == id && ci.ci_fecha >= FIn && ci.ci_fecha <= FFin && (ci.Corretaje.Crt_Direccion.Contains(filtro) || ci.Usuario.usu_username.Contains(filtro) || (ci.Usuario.usu_nombre + " " + ci.Usuario.usu_apellidoPa + " " + ci.Usuario.usu_apellidoMa).Contains(filtro)) select ci).Count(); } int totalPaginas = (int)Math.Ceiling((double)(from ci in db.CasaInventario where ci.ci_articulo_id == id && ci.ci_fecha >= FIn && ci.ci_fecha <= FFin && (ci.Corretaje.Crt_Direccion.Contains(filtro) || ci.Usuario.usu_username.Contains(filtro)) select ci).Count() / registrosPagina); var respuesta = (from ci in db.CasaInventario where ci.ci_articulo_id == id && ci.ci_fecha >= FIn && ci.ci_fecha <= FFin && (ci.Corretaje.Crt_Direccion.Contains(filtro) || ci.Usuario.usu_username.Contains(filtro) || (ci.Usuario.usu_nombre + " " + ci.Usuario.usu_apellidoPa + " " + ci.Usuario.usu_apellidoMa).Contains(filtro)) select new { descripcion = ci.Corretaje.Crt_Direccion, tipo = "Salida", cantidad = ci.ci_cantidadAsignada, fecha = SqlFunctions.DateName("year", ci.ci_fecha).Trim() + "/" + SqlFunctions.StringConvert((double)ci.ci_fecha.Month).TrimStart() + "/" + SqlFunctions.DateName("day", ci.ci_fecha).Trim(), nombre = (ci.Usuario.usu_nombre + " " + ci.Usuario.usu_apellidoPa + " " + ci.Usuario.usu_apellidoMa), fechaOrden = ci.ci_fecha, total = totalPaginas }).OrderBy(c => c.fechaOrden).Skip((pagina - 1) * registrosPagina).Take(registrosPagina); return(Json(respuesta.ToList(), JsonRequestBehavior.AllowGet)); } else { if (registrosPagina == 0) { registrosPagina = (from ci in db.CasaInventario where ci.ci_articulo_id == id && ci.ci_fecha >= FIn && ci.ci_fecha <= FFin select ci).Count(); } int totalPaginas = (int)Math.Ceiling((double)(from ci in db.CasaInventario where ci.ci_articulo_id == id && ci.ci_fecha >= FIn && ci.ci_fecha <= FFin select ci).Count() / registrosPagina); var respuesta = (from ci in db.CasaInventario where ci.ci_articulo_id == id && ci.ci_fecha >= FIn && ci.ci_fecha <= FFin select new { descripcion = ci.Corretaje.Crt_Direccion, tipo = "Salida", cantidad = ci.ci_cantidadAsignada, fecha = SqlFunctions.DateName("year", ci.ci_fecha).Trim() + "/" + SqlFunctions.StringConvert((double)ci.ci_fecha.Month).TrimStart() + "/" + SqlFunctions.DateName("day", ci.ci_fecha).Trim(), nombre = (ci.Usuario.usu_nombre + " " + ci.Usuario.usu_apellidoPa), fechaOrden = ci.ci_fecha, total = totalPaginas }).OrderBy(c => c.fechaOrden).Skip((pagina - 1) * registrosPagina).Take(registrosPagina); //.Union(from e in db.Existencias where e.ext_art_id == id select new{ descripcion = ("Inventario"), tipo = "Entrada", cantidad = })) return(Json(respuesta.ToList(), JsonRequestBehavior.AllowGet)); } }
/// <summary> /// 查詢修改前後的資料 /// </summary> /// <param name="db"></param> /// <param name="cRoleID"></param> /// <param name="apprStatus"></param> /// <param name="updDateB"></param> /// <param name="updDateE"></param> /// <returns></returns> public List <CodeRoleItemModel> qryForRoleMgrHis(dbTreasuryEntities db, string cRoleID, string apprStatus, string updDateB, string updDateE) { bool bapprStatus = StringUtil.isEmpty(apprStatus); bool bDateB = StringUtil.isEmpty(updDateB); bool bDateE = StringUtil.isEmpty(updDateE); DateTime sB = DateTime.Now.AddDays(1); if (!bDateB) { sB = Convert.ToDateTime(updDateB); } DateTime sE = DateTime.Now.AddDays(1); if (!bDateE) { sE = Convert.ToDateTime(updDateE); } sE = sE.AddDays(1); var roleItemHis = (from m in db.CODE_ROLE_ITEM_HIS join item in db.TREA_ITEM on m.ITEM_ID equals item.ITEM_ID join appr in db.AUTH_APPR on m.APLY_NO equals appr.APLY_NO into psAppr from xAppr in psAppr.DefaultIfEmpty() join cType in db.SYS_CODE.Where(x => x.CODE_TYPE == "ITEM_OP_TYPE") on item.ITEM_OP_TYPE equals cType.CODE into psCType from xType in psCType.DefaultIfEmpty() where m.ROLE_ID == cRoleID & (bapprStatus || xAppr.APPR_STATUS == apprStatus.Trim()) & (bDateB || xAppr.CREATE_DT >= sB) & (bDateE || xAppr.CREATE_DT <= sE) select new CodeRoleItemModel { authType = m.AUTH_TYPE, aplyNo = m.APLY_NO.Trim(), apprUid = xAppr.APPR_UID.Trim(), apprStatus = xAppr.APPR_STATUS.Trim(), updateDT = SqlFunctions.DateName("year", xAppr.CREATE_DT) + "/" + SqlFunctions.DatePart("m", xAppr.CREATE_DT) + "/" + SqlFunctions.DateName("day", xAppr.CREATE_DT).Trim(), updateUid = xAppr.CREATE_UID.Trim(), execAction = m.EXEC_ACTION.Trim(), itemOpType = xType == null ? item.ITEM_OP_TYPE : xType.CODE_VALUE.Trim(), itemId = m.ITEM_ID.Trim(), itemDesc = item.ITEM_DESC.Trim() }).ToList <CodeRoleItemModel>(); return(roleItemHis); }
public IQueryable <TimeSheetMasterView> ShowAllSubmittedTimeSheet(string sortColumn, string sortColumnDir, string Search, int UserID) { var _context = new DatabaseContext(); var IQueryabletimesheet = (from timesheetmaster in _context.TimeSheetMaster join timeSheetAuditTB in _context.TimeSheetAuditTB on timesheetmaster.TimeSheetMasterID equals timeSheetAuditTB.TimeSheetID join registration in _context.Registration on timesheetmaster.UserID equals registration.RegistrationID join AssignedRolesAdmin in _context.AssignedRoles on registration.RegistrationID equals AssignedRolesAdmin.RegistrationID where AssignedRolesAdmin.AssignToAdmin == UserID && timeSheetAuditTB.Status == 1 select new TimeSheetMasterView { TimeSheetStatus = timesheetmaster.TimeSheetStatus == 1 ? "Submitted" : timesheetmaster.TimeSheetStatus == 2 ? "Approved" : "Rejected", Comment = timesheetmaster.Comment, TimeSheetMasterID = timesheetmaster.TimeSheetMasterID, FromDate = ( EntityFunctions.Right(SqlFunctions.StringConvert((double?)SqlFunctions.DatePart("yyyy", timesheetmaster.FromDate)), 4) + "-" + EntityFunctions.Right(String.Concat(" ", SqlFunctions.StringConvert((double?)SqlFunctions.DatePart("mm", timesheetmaster.FromDate))), 2) + "-" + EntityFunctions.Right(String.Concat(" ", SqlFunctions.StringConvert((double?)SqlFunctions.DatePart("dd", timesheetmaster.FromDate))), 2) ).Replace(" ", "0"), ToDate = ( EntityFunctions.Right(SqlFunctions.StringConvert((double?)SqlFunctions.DatePart("yyyy", timesheetmaster.ToDate)), 4) + "-" + EntityFunctions.Right(String.Concat(" ", SqlFunctions.StringConvert((double?)SqlFunctions.DatePart("mm", timesheetmaster.ToDate))), 2) + "-" + EntityFunctions.Right(String.Concat(" ", SqlFunctions.StringConvert((double?)SqlFunctions.DatePart("dd", timesheetmaster.ToDate))), 2) ).Replace(" ", "0"), CreatedOn = SqlFunctions.DateName("day", timesheetmaster.CreatedOn).Trim() + "/" + SqlFunctions.StringConvert((double)timesheetmaster.CreatedOn.Value.Month).TrimStart() + "/" + SqlFunctions.DateName("year", timesheetmaster.CreatedOn), TotalHours = timesheetmaster.TotalHours, Username = registration.Username, SubmittedMonth = SqlFunctions.DateName("MONTH", timesheetmaster.ToDate).ToString() }); if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir))) { IQueryabletimesheet = IQueryabletimesheet.OrderBy(sortColumn + " " + sortColumnDir); } if (!string.IsNullOrEmpty(Search)) { IQueryabletimesheet = IQueryabletimesheet.Where(m => m.FromDate == Search || m.Username == Search); } return(IQueryabletimesheet); }
public ContentResult UpdateHoursWorkedsData(HoursWorked hoursWorked) { var wageHistoryID = db.WageHistories.Where(x => x.EmployeeID == hoursWorked.EmployeeID && x.IsCurrent == true).Select(x => x.WageHistoryID).FirstOrDefault(); hoursWorked.WageHistoryID = wageHistoryID; if (ModelState.IsValid) { try { db.Entry(hoursWorked).State = EntityState.Modified; db.SaveChanges(); } catch (Exception e) { return(Content("", "application/json")); } } else { string error = ""; foreach (ModelState modelState in ViewData.ModelState.Values) { foreach (ModelError err in modelState.Errors) { error = error + " \n" + err.ErrorMessage; } } ViewBag.error = true; ViewBag.errorMessage = error; return(Content("", "application/json")); } //Form the hours worked data again for a single row and include the data conversions var hoursWorkeds = db.HoursWorkeds.Include(h => h.Employee).Include(h => h.ItemNumber).Include(h => h.Job); var hoursWorkedsData = (from hrs in hoursWorkeds where hrs.HoursWorkedID == hoursWorked.HoursWorkedID select new { hrs.HoursWorkedID, EmployeeID = hrs.Employee.EmployeeID, JobID = hrs.Job.JobID, hrs.ItemNumber, Date = SqlFunctions.DateName("mm", hrs.Date) + " " + SqlFunctions.DateName("day", hrs.Date) + ", " + SqlFunctions.DateName("year", hrs.Date), hrs.HoursWorkedRT, hrs.HoursWorkedOT, hrs.JobDescription }).SingleOrDefault(); JavaScriptSerializer jsonSerializer = new JavaScriptSerializer(); string output = jsonSerializer.Serialize(hoursWorkedsData); return(Content(output, "application/json")); }
public Expression <Func <PVLog, PvLogGroup> > GroupLogByUserAndMonthYear() { Expression <Func <PVLog, PvLogGroup> > expression = x => new PvLogGroup { MonthYear = DbFunctions.Left(SqlFunctions.DateName("month", x.LogDate), 3) + "-" + SqlFunctions.DatePart("year", x.LogDate), UserId = x.UserId, UserName = x.UserName }; return(expression); }