// // GET: /Administrator/MyOffice/ public ActionResult Index(IndexProductInStorageModel productInStorageModel) { var products = _productRepository.Search("").ToList(); var storages = _storageRepository.Search("").Where(p => WorkContext.MyStorages.Contains(p.StorageId)).ToList(); var model = new IndexProductInStorageModel { Products = products, Storages = storages, GroupByProduct = productInStorageModel.GroupByProduct, GroupByStorage = productInStorageModel.GroupByStorage, ProductsSelected = productInStorageModel.ProductsSelected, StoragesSelected = productInStorageModel.StoragesSelected }; int[] productsArr = new int[0]; if (!string.IsNullOrEmpty(productInStorageModel.ProductsSelected)) { productsArr = productInStorageModel.ProductsSelected.Split(',').Select(int.Parse).ToArray(); } int[] storagesArr = new int[0]; if (!string.IsNullOrEmpty(productInStorageModel.StoragesSelected)) { storagesArr = productInStorageModel.StoragesSelected.Split(',').Select(int.Parse).ToArray(); } var filter = new ReportFilterModel { ListProducts = productsArr, FromListStorages = storagesArr }; model.ProductInStorageModels = GridModel(filter); return(View(model)); }
public HttpResponseMessage GetReportExcel(ReportFilterModel model) { try { var report = _reportService.GetReportById(model.ReportId); var data = GetReportData(model); string filePath = ExcelUtility.createXSL(data, report.Title); byte[] file = System.IO.File.ReadAllBytes(filePath); var response = new HttpResponseMessage(HttpStatusCode.OK) { Content = new ByteArrayContent(file) }; response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel"); response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = filePath }; return(response); } catch (Exception ex) { var response = new HttpResponseMessage(HttpStatusCode.InternalServerError); return(response); } }
public IHttpActionResult GetmprRequisitionfilters() { ReportFilterModel status = new ReportFilterModel(); status = _paBusenessAcess.GetmprRequisitionfilters(); return(Ok(status)); }
private List <ProductInStorageModel> GridModel(ReportFilterModel filterModel) { var listProducts = new int[1]; var isGetAllListProducts = true; //Filter by deliver to if (filterModel.ListProducts != null && filterModel.ListProducts.Count() > 0) { listProducts = filterModel.ListProducts; isGetAllListProducts = false; } var listFromListStorages = new int[1]; var isGetAllFromListStorages = true; //Filter by deliver to if (filterModel.FromListStorages != null && filterModel.FromListStorages.Count() > 0) { listFromListStorages = filterModel.FromListStorages; isGetAllFromListStorages = false; } var model = from x in Repository.Search(filterModel.search).Where(p => (listProducts.Contains(p.ProductId) || (isGetAllListProducts)) && (listFromListStorages.Contains(p.StorageId) || (isGetAllFromListStorages && WorkContext.MyStorages.Contains(p.StorageId)))) select new ProductInStorageModel { StorageId = x.StorageId, ProductId = x.ProductId, ProductName = x.Product.ProductName, StorageName = x.Storage.StorageName, Amount = x.Amount, ProductInStorageId = x.ProductInStorageId }; return(model.ToList()); }
public ActionResult GridModel(ReportFilterModel filterModel) { var listOffices = new int[1]; var isGetAllListOffices = true; //Filter by deliver to if (filterModel.ListOffices != null && filterModel.ListOffices.Count() > 0) { listOffices = filterModel.ListOffices; isGetAllListOffices = false; } var model = Repository.Search(filterModel.search).Where(p => WorkContext.MyOffices.Contains(p.MyOfficeId) && (listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) && ((!WorkContext.Roles.Contains(RoleType.Administrator) && !p.HideWithNormalUser) || WorkContext.Roles.Contains(RoleType.Administrator))).OrderByDescending(p => p.CustomerId); var gridModel = new GridModel <CustomerModel> { Data = model.Select(x => new CustomerModel { Address = x.Address, CustomerId = x.CustomerId, CustomerName = x.CustomerName, PhoneNumber = x.PhoneNumber, Fax = x.Fax, CustomerCode = x.CustomerCode }) }; return(new JsonResult { Data = gridModel }); }
public async Task <ActionResult <IEnumerable <ParameterisedReportModel> > > GetParametrisedReport( [FromQuery] ReportFilterModel filterModel) { IEnumerable <ParameterisedReportModel> reportModel = await _insightsService.GetParameterisedReportAsync(filterModel); return(Ok(reportModel)); }
public ActionResult GridModel(ReportFilterModel filterModel) { //Default get all var fromDate = DateTime.Today; var isAllFromDate = true; var toDate = DateTime.Today; var isAllToDate = true; //Filter by date if (filterModel.FromDate.HasValue) { fromDate = filterModel.FromDate.Value.Date; isAllFromDate = false; } if (filterModel.ToDate.HasValue) { toDate = filterModel.ToDate.Value.Date.AddDays(1); isAllToDate = false; } var listOffices = new int[1]; var isGetAllListOffices = true; //Filter by deliver to if (filterModel.ListOffices != null && filterModel.ListOffices.Count() > 0) { listOffices = filterModel.ListOffices; isGetAllListOffices = false; } var model = from x in Repository.Search("").Where(p => (listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) && ((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate))) select new OrderDeliveryPackageModel { OrderDeliveryPackageId = x.OrderDeliveryPackageId, CreateDate = x.CreateDate, Note = x.Note, ShippingFee = x.ShippingFee, MyOfficeName = x.MyOffice.OfficeName }; double total = 0; try { total = Convert.ToDouble(model.Sum(p => p.ShippingFee)); } catch { } Session["TotalShippingFee"] = String.Format("{0:0,0}", total); var gridModel = new GridModel <OrderDeliveryPackageModel> { Data = model }; return(new JsonResult { Data = gridModel }); }
public ActionResult GetRevenue(ReportFilterModel filterModel) { decimal doanhThu = 0; decimal loiNhuan = 0; decimal giaVon = 0; decimal chiPhi = 0; //Default get all var fromDate = DateTime.Today; var isAllFromDate = true; var toDate = DateTime.Today; var isAllToDate = true; //Filter by date if (filterModel.FromDate.HasValue) { fromDate = filterModel.FromDate.Value.Date; isAllFromDate = false; } if (filterModel.ToDate.HasValue) { toDate = filterModel.ToDate.Value.Date.AddDays(1); isAllToDate = false; } var listOffices = new int[1]; var isGetAllListOffices = true; //Filter by deliver to if (filterModel.ListOffices != null && filterModel.ListOffices.Count() > 0) { listOffices = filterModel.ListOffices; isGetAllListOffices = false; } var orders = _orderRepository.Search("") .Where(p => (listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) && ((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate)) && p.Paid).Include(p => p.OrderDetails).ToList(); foreach (var item in orders) { doanhThu += item.TotalCost; } //giá gốc foreach (var item in orders) { var donHangTheoGiaVon = GetOrgiranalCostForAnOrder(item); giaVon += donHangTheoGiaVon; } //chi phí var expenses = _expenseRepository.Search("") .Where(p => (listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) && ((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate))).ToList(); foreach (var item in expenses) { chiPhi += item.Cost; } loiNhuan = doanhThu - giaVon - chiPhi; return(Json(new { DoanhThu = String.Format("{0:0,0}", doanhThu), LoiNhuan = String.Format("{0:0,0}", loiNhuan), GiaVon = String.Format("{0:0,0}", giaVon), ChiPhi = String.Format("{0:0,0}", chiPhi) })); }
public ActionResult GridModel(ReportFilterModel filterModel) { //Default get all var fromDate = DateTime.Today; var isAllFromDate = true; var toDate = DateTime.Today; var isAllToDate = true; //Filter by date if (filterModel.FromDate.HasValue) { fromDate = filterModel.FromDate.Value.Date; isAllFromDate = false; } if (filterModel.ToDate.HasValue) { toDate = filterModel.ToDate.Value.Date.AddDays(1); isAllToDate = false; } var toListStorages = new int[1]; var isGetAllToStorages = true; //Filter by deliver to if (filterModel.ToListStorages != null && filterModel.ToListStorages.Count() > 0) { toListStorages = filterModel.ToListStorages; isGetAllToStorages = false; } var model = from x in Repository.Search(filterModel.search).Where(p => WorkContext.MyStorages.Contains(p.ToStorageId) && (toListStorages.Contains(p.ToStorageId) || (isGetAllToStorages && WorkContext.MyStorages.Contains(p.ToStorageId))) && ((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate))) join m in _userRepository.GetAll() on x.ApproveFromManagerId equals m.UserId into m1 from m2 in m1.DefaultIfEmpty() join s in _userRepository.GetAll() on x.ApproveFromStorageStaffId equals s.UserId into s1 from s2 in s1.DefaultIfEmpty() select new ImportTrackModel { ImportTrackId = x.ImportTrackId, CreateDate = x.CreateDate, Note = x.Note, StorageId = x.ToStorageId, ImportUserName = x.CreateUser.FirstName + " " + x.CreateUser.LastName, ApproveFromManagerName = (m2 == null ? String.Empty : m2.FirstName + " " + m2.LastName), ApproveFromStorageStaffName = (s2 == null ? String.Empty : s2.FirstName + " " + s2.LastName), }; var gridModel = new GridModel <ImportTrackModel> { Data = model }; return(new JsonResult { Data = gridModel }); }
public ActionResult GridModel(ReportFilterModel filterModel) { //Default get all var fromDate = DateTime.Today; var isAllFromDate = true; var toDate = DateTime.Today; var isAllToDate = true; //Filter by date if (filterModel.FromDate.HasValue) { fromDate = filterModel.FromDate.Value.Date; isAllFromDate = false; } if (filterModel.ToDate.HasValue) { toDate = filterModel.ToDate.Value.Date.AddDays(1); isAllToDate = false; } var listOffices = new int[1]; var isGetAllListOffices = true; //Filter by deliver to if (filterModel.ListOffices != null && filterModel.ListOffices.Count() > 0) { listOffices = filterModel.ListOffices; isGetAllListOffices = false; } var model = from x in Repository.Search("").Where(p => (listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) && ((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate))) select new DebtModel { DebtId = x.DebtId, CreateDate = x.CreateDate, Note = x.Note, FromDate = x.FromDate, ToDate = x.ToDate, Total = x.Total, Paid = x.Paid, PaidLeft = x.Total - x.Paid, MyOfficeName = x.MyOffice.OfficeName }; var gridModel = new GridModel <DebtModel> { Data = model }; return(new JsonResult { Data = gridModel }); }
private DataSet GetReportData(ReportFilterModel model) { string proc = "prc_ReportExecute"; string query = string.Empty; string prcName = "prc_report"; string startDate = ""; string endDate = ""; if (model.Template.ToUpper() == "ISYEARMONTH") { startDate = new DateTime(model.Year, model.Month, 1).ToString(); endDate = new DateTime(model.Year, model.Month, DateTime.DaysInMonth(model.Year, model.Month)).ToString(); } if (model.Template.ToUpper() == "ISDATE") { if (!string.IsNullOrEmpty(model.From) && !string.IsNullOrEmpty(model.To)) { startDate = Convert.ToDateTime(model.From).ToString(); endDate = Convert.ToDateTime(model.To).ToString(); } } query = string.Format("{0} '{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}'", prcName, model.ClientId, startDate, endDate, model.CallGapType, model.AgeSlab, model.FeildFeedback, model.OverallSummary, model.MisType, model.Page == 1 ? 0 : (model.Page - 1) * model.PageSize, model.PageSize, "ASC", model.ReportId ); SqlParameter[] param = { new SqlParameter("@CurrentPage", "1"), new SqlParameter("@PageSize", "-1"), new SqlParameter("@Query", query), }; DataSet data = _spService.ExcuteSpAnonmious(proc, param, 3); return(data); }
/// <summary> /// 根据filterId获取filter对象 /// </summary> /// <param name="filterId"></param> /// <returns></returns> public ReportFilterModel GetFilter(long filterId) { ReportFilterModel ft = new ReportFilterModel(); var filter = _reportFilterRepository.FirstOrDefault(filterId); if (filter != null) { ft = AutoMapper.Mapper.Map(filter, ft); } return(ft); }
public ActionResult OrderGridModel(ReportFilterModel filterModel) { //Default get all var fromDate = DateTime.Today; var isAllFromDate = true; var toDate = DateTime.Today; var isAllToDate = true; //Filter by date if (filterModel.FromDate.HasValue) { fromDate = filterModel.FromDate.Value.Date; isAllFromDate = false; } if (filterModel.ToDate.HasValue) { toDate = filterModel.ToDate.Value.Date.AddDays(1); isAllToDate = false; } var listOffices = new int[1]; var isGetAllListOffices = true; //Filter by deliver to if (filterModel.ListOffices != null && filterModel.ListOffices.Count() > 0) { listOffices = filterModel.ListOffices; isGetAllListOffices = false; } var model = _orderRepository.Search("") .Where(p => (listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) && ((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate)) && p.Paid) .Include(p => p.MyOffice).Include(p => p.Customer); var gridModel = new GridModel <OrderModel> { Data = model.Select(x => new OrderModel { OrderId = x.OrderId, CreateDate = x.CreateDate, Status = x.Status, TotalCost = x.TotalCost, CustomerName = x.Customer.CustomerName, MyOfficeIName = x.MyOffice.OfficeName, Paid = x.Paid }) }; return(new JsonResult { Data = gridModel }); }
public ActionResult ExpenseGridModel(ReportFilterModel filterModel) { //Default get all var fromDate = DateTime.Today; var isAllFromDate = true; var toDate = DateTime.Today; var isAllToDate = true; //Filter by date if (filterModel.FromDate.HasValue) { fromDate = filterModel.FromDate.Value.Date; isAllFromDate = false; } if (filterModel.ToDate.HasValue) { toDate = filterModel.ToDate.Value.Date.AddDays(1); isAllToDate = false; } var listOffices = new int[1]; var isGetAllListOffices = true; //Filter by deliver to if (filterModel.ListOffices != null && filterModel.ListOffices.Count() > 0) { listOffices = filterModel.ListOffices; isGetAllListOffices = false; } var model = _expenseRepository.Search("") .Where(p => (listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) && ((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate))); var gridModel = new GridModel <ExpenseModel> { Data = model.Select(x => new ExpenseModel { ExpenseId = x.ExpenseId, CreateDate = x.CreateDate, Note = x.Note, Cost = x.Cost, ExpenseName = x.ExpenseName }) }; return(new JsonResult { Data = gridModel }); }
public IHttpActionResult GetReport(ReportFilterModel model) { try { var report = _reportService.GetReportById(model.ReportId); var data = GetReportData(model); return(Ok(new ArrayList { data, report.Title })); } catch (Exception ex) { return(ResponseMessage(Request.CreateResponse(HttpStatusCode.InternalServerError, ex.Message))); } }
public async Task <IActionResult> Get([FromQuery] ReportFilterModel filter) { var reports = await this.reportService.GetAll( filter.Keyword, filter.Email, filter.Name, filter.LocationId, filter.IsPositive, orderBy : filter.OrderByEnum, page : filter.Page, pageSize : filter.PageSize); var models = reports.ToModels(this.filesHelper, Url.Content, this.generalSettings.SmallPictureWidth, this.generalSettings.SmallPictureHeight); return(this.Ok(models, reports.HasNextPage, reports.TotalCount)); }
public HttpResponseMessage GetReportPdf(ReportFilterModel model) { try { var report = _reportService.GetReportById(model.ReportId); var data = GetReportData(model); // report start var parameters = new List <ReportParameters>(); parameters.Add(new ReportParameters() { Key = "CompanyName", Value = "" }); parameters.Add(new ReportParameters() { Key = "Title", Value = report.Title }); string fileName = GenerateReport.GetReportWithDs(report.PdfReportName, "Report", parameters, data); byte[] file = System.IO.File.ReadAllBytes(HttpContext.Current.Server.MapPath("~/Report/") + fileName); var response = new HttpResponseMessage(HttpStatusCode.OK) { Content = new ByteArrayContent(file) }; response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/pdf"); response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = fileName }; return(response); } catch (Exception ex) { var response = new HttpResponseMessage(HttpStatusCode.InternalServerError); return(response); } }
public IActionResult Build([Bind] ReportFilterModel filter) { var user = _userFactory.GetUser(User.Identity.Name); if (user == null) { throw new ApplicationException("No record of such user"); } int userId = user.Id; int?partnerId = null; if (User.IsInRole(WebConstants.PartnerRole)) { if (user == null) { throw new ApplicationException("No record of such user"); } if (user.PartnerId == null) { throw new ApplicationException("The current user has the permissions of a Partner, but isn't tied to any"); } partnerId = user.PartnerId; } var data = _reportFactory.GenerateReportData(filter); // now export to excel (and save everything to db !!!) var reportPath = ReportHelper.Export(data, filter); _reportFactory.CreateReport(new Services.Models.ReportModel() { Name = Path.GetFileNameWithoutExtension(reportPath), CreatedDate = DateTime.UtcNow, DiscountsUserId = userId, FilterJson = JsonConvert.SerializeObject(filter), PartnerId = partnerId, PathToFile = reportPath }); return(Index()); }
public ActionResult CreateReportFilter(long reportId) { var report = _reportRepository.GetById(reportId); var maxDisplayOrder = report.ReportFilters.Max(p => p.DisplayOrder) ?? 0; var reportFilter = new ReportFilter { ReportId = reportId, IsNew = true, DisplayOrder = maxDisplayOrder + 1 }; _reportFilterRepository.InsertAndCommit(reportFilter); var model = new ReportFilterModel(); model = reportFilter.ToModel(); var html = this.ReportFilterPanel(model); return(Json(new { Id = reportFilter.Id, Html = html })); }
public List <ExportData> GetExportData(ReportFilterModel reportFilterModel) { var ret = new List <ExportData>(); var fromStr = reportFilterModel.From.ToString("dd/MM/yyyy"); var toStr = reportFilterModel.To.ToString("dd/MM/yyyy"); var query = $@"set dateformat dmy select ed.Id, ed.ExportName, ed.ExportDate, ed.UserFullName, l.Name from ExportData ed left join Locals l on ed.LocalId = l.Id where l.Id = {reportFilterModel.LocalId} and ed.ExportDate between '{fromStr}' and '{toStr}'"; var res = DbExecutor.Execute(query); for (int i = 0; i < res.Count; i++) { var ht = res[i] as Hashtable; var toAdd = new ExportData(ht); ret.Add(toAdd); } return(ret); }
public object PaymentMonthReport(ReportFilterModel model) { using (DbContext _db = new DbContext()) { var user = ApiCommon.GetUser(HttpContext.Current.User.Identity, _db); if (user != null && model != null) { if (model.Month == 0 || model.Year == 0 || model.Month > 12) { return(new HttpActionResult(HttpStatusCode.BadRequest, @"{""message"":""Please enter a valid month and year.""}")); } using (var dapper = new Helpers.Dapper()) { var payments = dapper.Connection.Query($"select * From Payments Where month(TransDate)=@month and year(TransDate)=@year and PaymentStatus=@status order by TransDate {model.SortOrder}", new { month = model.Month, year = model.Year, status = model.PaymentStatus }); return(payments.Select(i => new { i.Amount, i.CreditCard_CardId, i.PaymentStatus, i.TransDate }));; } } } return(new HttpActionResult(HttpStatusCode.Unauthorized, @"{""message"":""User doesn't exist.""}")); }
public ActionResult SaveReportFilter(ReportFilterModel model) { if (ModelState.IsValid) { var reportFilter = _reportFilterRepository.GetById(model.Id); if (string.IsNullOrEmpty(reportFilter.Name)) { var filter = _filterRepository.GetById(model.FilterId); model.Name = filter.Name + "_" + Guid.NewGuid(); } //always set IsNew to false when saving reportFilter.IsNew = false; reportFilter = model.ToEntity(reportFilter); _reportFilterRepository.UpdateAndCommit(reportFilter); return(new NullJsonResult()); } else { return(Json(new { Errors = ModelState.Errors().ToHtmlString() })); } }
public IEnumerable <ReportRecord> GenerateReportData(ReportFilterModel filter) { var entries = _usedActionservice.GetUsedActions().AsQueryable().ApplyFilter(filter); return(entries.Select(x => new { action = filter.GroupByAction ? x.Action.Name : null, user = filter.GroupByUser ? x.User.UserName : null, partner = filter.GroupByPartner ? x.Partner.Name : null, partnerType = filter.GroupByPartnerType ? x.Partner.PartnerType.Name : null, originalValue = x.OriginalValue, actionValue = x.ActionValue }).GroupBy(g => new { g.action, g.user, g.partner, g.partnerType }) .Select(g => new ReportRecord() { ActionName = g.Key.action, UserName = g.Key.user, PartnerName = g.Key.partner, PartnerTypeName = g.Key.partnerType, OriginalValue = g.Sum(x => x.originalValue ?? 0), ActionValue = g.Sum(x => x.actionValue) }).AsEnumerable()); }
public Task <IEnumerable <ParameterisedReportModel> > GetParameterisedReportAsync(ReportFilterModel reportFilters) { return(_http.GetFromJsonAsync <IEnumerable <ParameterisedReportModel> >($"{insightsBaseUrl}/ParametrisedReport?{reportFilters.QueryString}")); }
public string ReportFilterPanel(ReportFilterModel model) { var html = this.RenderPartialViewToString("_ReportFilterDetails", model); return(html); }
public ActionResult ImportFromOtherStorageModel(ReportFilterModel filterModel) { //Default get all var fromDate = DateTime.Today; var isAllFromDate = true; var toDate = DateTime.Today; var isAllToDate = true; //Filter by date if (filterModel.FromDate.HasValue) { fromDate = filterModel.FromDate.Value.Date; isAllFromDate = false; } if (filterModel.ToDate.HasValue) { toDate = filterModel.ToDate.Value.Date.AddDays(1); isAllToDate = false; } var toListStorages = new int[1]; var isGetAllToStorages = true; //Filter by deliver to if (filterModel.ToListStorages != null && filterModel.ToListStorages.Count() > 0) { toListStorages = filterModel.ToListStorages; isGetAllToStorages = false; } var model = from x in Repository.Search(filterModel.search).Where(p => (toListStorages.Contains(p.ToStorageId) || (isGetAllToStorages && WorkContext.MyStorages.Contains(p.ToStorageId))) && ((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate))) join m in _userRepository.GetAll() on x.ApproveFromManagerId equals m.UserId into m1 from m2 in m1.DefaultIfEmpty() join s in _userRepository.GetAll() on x.ApproveFromStorageStaffId equals s.UserId into s1 from s2 in s1.DefaultIfEmpty() join t in _userRepository.GetAll() on x.ApproveFromGeneralDeliveryManId equals t.UserId into t1 from t2 in s1.DefaultIfEmpty() join h in _userRepository.GetAll() on x.ApproveFromReceiveStorageStaffId equals h.UserId into h1 from h2 in s1.DefaultIfEmpty() join y in _storageRepository.GetAll() on x.FromStorageId equals y.StorageId into y1 from y2 in y1.DefaultIfEmpty() join w in _storageRepository.GetAll() on x.ToStorageId equals w.StorageId into w1 from w2 in y1.DefaultIfEmpty() select new TransferModel { TransferId = x.TransferId, CreateDate = x.CreateDate, Note = x.Note, ApproveFromManagerName = (m2 == null ? String.Empty : m2.FirstName + " " + m2.LastName), ApproveFromStorageStaffName = (s2 == null ? String.Empty : s2.FirstName + " " + s2.LastName), ApproveFromGeneralDeliveryMan = (t2 == null ? String.Empty : t2.FirstName + " " + t2.LastName), StorageStaffApproveReceive = (t2 == null ? String.Empty : t2.FirstName + " " + t2.LastName), FromStorageName = (y2 == null ? "" : y2.StorageName), ToStorageName = (w2 == null ? "" : w2.StorageName), }; var gridModel = new GridModel <TransferModel> { Data = model }; return(new JsonResult { Data = gridModel }); }
public ActionResult GridModel(ReportFilterModel filterModel) { //Default get all var fromDate = DateTime.Today; var isAllFromDate = true; var toDate = DateTime.Today; var isAllToDate = true; //Filter by date if (filterModel.FromDate.HasValue) { fromDate = filterModel.FromDate.Value.Date; isAllFromDate = false; } if (filterModel.ToDate.HasValue) { toDate = filterModel.ToDate.Value.Date.AddDays(1); isAllToDate = false; } var fromListCustomers = new int[1]; var isGetAllFromCustomers = true; //Filter by deliver to if (filterModel.ListCustomers != null && filterModel.ListCustomers.Count() > 0) { fromListCustomers = filterModel.ListCustomers; isGetAllFromCustomers = false; } var listOffices = new int[1]; var isGetAllListOffices = true; //Filter by deliver to if (filterModel.ListOffices != null && filterModel.ListOffices.Count() > 0) { listOffices = filterModel.ListOffices; isGetAllListOffices = false; } var listProducts = new int[1]; var isGetAllListProducts = true; //Filter by deliver to if (filterModel.ListProducts != null && filterModel.ListProducts.Count() > 0) { listProducts = filterModel.ListProducts; isGetAllListProducts = false; } var model = from x in Repository.Search(filterModel.search).Where(p => (listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) && (fromListCustomers.Contains(p.Order.CustomerId) || (isGetAllFromCustomers)) && (p.ExportDetails.Any(k => listProducts.Contains(k.ProductId)) || (isGetAllListProducts)) && ((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate))) join m in _myOfficeRepository.GetAll() on x.MyOfficeId equals m.MyOfficeId into m1 from m2 in m1.DefaultIfEmpty() join s in _customerRepository.GetAll() on x.Order.CustomerId equals s.CustomerId into s1 from s2 in s1.DefaultIfEmpty() select new ExportTrackModel { ExportTrackId = x.ExportTrackId, CreateDate = x.CreateDate, Note = x.Note, MyOfficeName = (m2 == null ? String.Empty : m2.OfficeName), CustomerName = (s2 == null ? String.Empty : s2.CustomerName) }; var gridModel = new GridModel <ExportTrackModel> { Data = model }; return(new JsonResult { Data = gridModel }); }
public static string Export(IEnumerable <ReportRecord> records, ReportFilterModel filter) { var file = Path.GetTempFileName(); using (var document = SpreadsheetDocument.Create(file, SpreadsheetDocumentType.Workbook, true)) { var workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); var sharedStringTablePart = workbookPart.AddNewPart <SharedStringTablePart>(); sharedStringTablePart.SharedStringTable = new SharedStringTable(); var sheets = workbookPart.Workbook.AppendChild(new Sheets()); var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Report" }; sheets.Append(sheet); workbookPart.Workbook.Save(); } using (var document = SpreadsheetDocument.Open(file, true)) { var worksheetPart = document.WorkbookPart.WorksheetParts.First(); var sheetData = worksheetPart.Worksheet.AppendChild(new SheetData()); var sharedStringTable = document.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First().SharedStringTable; // future use int columnCount = 0; var hRow = new Row(); if (filter.GroupByPartnerType) { AddStringCell("Partner Type", hRow, sharedStringTable); columnCount++; } if (filter.GroupByPartner) { AddStringCell("Partner", hRow, sharedStringTable); columnCount++; } if (filter.GroupByUser) { AddStringCell("User", hRow, sharedStringTable); columnCount++; } if (filter.GroupByAction) { AddStringCell("Action", hRow, sharedStringTable); columnCount++; } AddStringCell("Original Value", hRow, sharedStringTable); columnCount++; AddStringCell("Discount", hRow, sharedStringTable); columnCount++; sheetData.AppendChild(hRow); foreach (var r in records) { var row = new Row(); if (filter.GroupByPartnerType) { AddStringCell(r.PartnerTypeName, row, sharedStringTable); } if (filter.GroupByPartner) { AddStringCell(r.PartnerName, row, sharedStringTable); } if (filter.GroupByUser) { AddStringCell(r.UserName, row, sharedStringTable); } if (filter.GroupByAction) { AddStringCell(r.ActionName, row, sharedStringTable); } row.Append(new Cell { CellValue = new CellValue(r.OriginalValue) }); row.Append(new Cell { CellValue = new CellValue(r.ActionValue) }); sheetData.AppendChild(row); } } var filePath = string.Format(ReportRelativePathTemplate, DateTime.Now.Ticks); Directory.CreateDirectory(Path.GetDirectoryName(filePath)); File.Move(file, filePath); return(filePath); }
public async Task <IEnumerable <ParameterisedReportModel> > GetParameterisedReportAsync(ReportFilterModel filterModel) { var sqlModel = new SqlParamsModel { Sql = "pkg_insights.p_parameterised_report", Parameters = new OracleDynamicParameters() }; sqlModel.Parameters.Add("pi_csv_filter_params", filterModel.Csv, dbType: OracleMappingType.Varchar2, ParameterDirection.Input); sqlModel.Parameters.Add("po_cursor", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output); return(await _dataAccess.QueryAsync <ParameterisedReportModel>(sqlModel)); }
public async Task <ReportFilterModel> GetMediaReportFilters(int userId) { ReportFilterModel result = new ReportFilterModel(); try { await Task.Run(() => { using (SocialCRMEntities db = new SocialCRMEntities()) { result.MediaTypesList = new List <SelectItemList>(); db.MediaTypes.ToList().ForEach(delegate(MediaType obj) { result.MediaTypesList.Add(new SelectItemList() { Value = obj.Id, Text = obj.TypeName, MediaTypeId = obj.Id }); }); result.NewsTypeList = new List <SelectItemList>(); db.NewsTypes.OrderBy(x => x.NewsTypeName).ToList().ForEach(delegate(NewsType obj) { result.NewsTypeList.Add(new SelectItemList() { Value = obj.Id, Text = obj.NewsTypeName, MediaTypeId = obj.MediaTypeId }); }); result.ChannelsList = new List <SelectItemList>(); db.MediaChannels.OrderBy(x => x.ChannelName).ToList().ForEach(delegate(MediaChannel obj) { result.ChannelsList.Add(new SelectItemList() { Value = obj.Id, Text = obj.ChannelName, MediaTypeId = obj.MediaTypeId }); }); result.CategoriesList = new List <SelectItemList>(); db.Categories.OrderBy(x => x.CategoryName).ToList().ForEach(delegate(Category obj) { result.CategoriesList.Add(new SelectItemList() { Value = obj.Id, Text = obj.CategoryName, MediaTypeId = 0 }); }); result.NewsRelatedToList = new List <SelectItemList>(); db.NewsRelatedToes.OrderBy(x => x.Name).ToList().ForEach(delegate(NewsRelatedTo obj) { result.NewsRelatedToList.Add(new SelectItemList() { Value = obj.Id, Text = obj.Name, MediaTypeId = 0 }); }); result.SentimentList = new List <SelectItemList>(); db.Sentiments.OrderBy(x => x.Name).ToList().ForEach(delegate(Sentiment obj) { result.SentimentList.Add(new SelectItemList() { Value = obj.Id, Text = obj.Name, MediaTypeId = 0 }); }); result.AgentList = new List <SelectItemList>(); db.AspNetUsers.OrderBy(x => x.Name).ToList().ForEach(delegate(AspNetUser obj) { result.AgentList.Add(new SelectItemList() { Value = obj.Id, Text = obj.Name, MediaTypeId = 0 }); }); } }); } catch (Exception ex) { result = null; } return(result); }