using Microsoft.Reporting.WebForms;using System;using System.Collections.Generic;using System.IO;using System.Linq;using System.Net;using System.Net.Http;using System.Net.Http.Headers;using System.Security;using System.Security.Permissions;using System.Web;using System.Web.Hosting;namespace AIMS.Web.Helper{ ////// 报表助手 /// public class ReportHelper { private LocalReport report = new LocalReport(); public string OutUrl { get; set; } = "~/Reports/Exports"; public ReportHelper(string reportPath) { report.ReportPath = reportPath; report.SetBasePermissionsForSandboxAppDomain(new PermissionSet(PermissionState.Unrestricted)); } public void SetParameters(IEnumerableparameters) { report.SetParameters(parameters); } public void LoadDataSource(List dataSources) { foreach (var dataSource in dataSources) { report.DataSources.Add(dataSource); } } public void LoadDataSource(Dictionary dataSources) { foreach (var dataSource in dataSources) { report.DataSources.Add(new ReportDataSource(dataSource.Key, dataSource.Value)); } } /// /// 用新数据呈现 /// public void Refresh() { report.Refresh(); } ////// 导出为字节数组 /// /// [PDF|Word|Excel|Image] /// 输出扩展名 ///字节数组 public byte[] ExportToBuffer(string format, out string extension) { Warning[] warnings; string[] streamids; string mimeType; string encoding; byte[] buffer = report.Render( format, null, out mimeType, out encoding, out extension, out streamids, out warnings); return buffer; } ////// 导出到输出流 /// /// [PDF|Word|Excel|Image] /// 输出流 ///扩展名 public string ExportToStream(string format, Stream writeStream) { string extension; byte[] buffer = ExportToBuffer(format, out extension); writeStream.Write(buffer, 0, buffer.Length); return extension; } ////// 导出到文件 /// /// [PDF|Word|Excel|Image] /// 文件名(不需要扩展名) ///文件名(包含扩展名) public string ExportToFile(string format, string fileName = "export") { string directory = HostingEnvironment.MapPath(this.OutUrl); if (!Directory.Exists(directory)) { Directory.CreateDirectory(directory); } string extension; byte[] buffer = ExportToBuffer(format, out extension); string fileFullName = fileName + "." + extension; string filePath = Path.Combine(directory, fileFullName); FileStream fs = new FileStream(filePath, FileMode.Create); fs.Write(buffer, 0, buffer.Length); fs.Close(); return fileFullName; } ////// 打印报表(生成PDF文件,返回PDF文件URL) /// /// 报表文件路径 /// 数据源集合 /// 参数集合 ///URL(生成PDF文件的URL) public static string Print(string reportPath, DictionarydataSources, IEnumerable parameters = null) { ReportHelper helper = new ReportHelper(reportPath); if (parameters != null) helper.SetParameters(parameters); helper.LoadDataSource(dataSources); helper.Refresh(); string fileName = helper.ExportToFile("PDF", "print"); return helper.OutUrl.TrimEnd('/') + "/" + fileName; } /// /// 导出到文件 /// /// [PDF|Word|Excel|Image] /// 报表文件路径 /// 数据源集合 /// 参数集合 ///文件物理路径 public static string ExportToFile(string format, string reportPath, DictionarydataSources, IEnumerable parameters = null) { ReportHelper helper = new ReportHelper(reportPath); if (parameters != null) helper.SetParameters(parameters); helper.LoadDataSource(dataSources); helper.Refresh(); string fileName = helper.ExportToFile(format); string fileUrl = helper.OutUrl.TrimEnd('/') + "/" + fileName; return HostingEnvironment.MapPath(fileUrl); } /// /// 分格式导出数据流到前端 /// /// [PDF|Word|Excel|Image] /// 报表文件路径 /// 数据源集合 /// 参数集合 ///Http响应信息 public static HttpResponseMessage Export(string format, string reportPath, DictionarydataSources, IEnumerable parameters = null) { ReportHelper helper = new ReportHelper(reportPath); if (parameters != null) helper.SetParameters(parameters); helper.LoadDataSource(dataSources); helper.Refresh(); string extension; byte[] buffer = helper.ExportToBuffer(format, out extension); string saveFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + "." + extension; try { HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK); response.Content = new ByteArrayContent(buffer); response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream"); response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = saveFileName }; return response; } catch { return new HttpResponseMessage(HttpStatusCode.NoContent); } } /// /// 导出Excel格式数据流到前端 /// /// 报表文件路径 /// 数据源集合 /// 参数集合 ///Http响应信息 public static HttpResponseMessage ExportExcel(string reportPath, DictionarydataSources, IEnumerable parameters = null) { return Export("Excel", reportPath, dataSources, parameters); } /// /// 导出Word格式数据流到前端 /// /// 报表文件路径 /// 数据源集合 /// 参数集合 ///Http响应信息 public static HttpResponseMessage ExportWord(string reportPath, DictionarydataSources, IEnumerable parameters = null) { return Export("Word", reportPath, dataSources, parameters); } /// /// 导出PDF格式数据流到前端 /// /// 报表文件路径 /// 数据源集合 /// 参数集合 ///Http响应信息 public static HttpResponseMessage ExportPDF(string reportPath, DictionarydataSources, IEnumerable parameters = null) { return Export("PDF", reportPath, dataSources, parameters); } }}
using AIMS.Bussiness.ApplicationDto.NJWorkTasks;using AIMS.Bussiness.ApplicationDto.NJWorkTasksDto;using System;using System.Collections.Generic;using System.Linq;using System.Net.Http;using System.Text;using System.Threading.Tasks;namespace AIMS.Bussiness.Application.Application.WorkTasksDetailsApp{ public interface IWorkTasksDetailsAppService { ListGetWorkTasksDetailsList(Guid orgId, DateTime startDate, DateTime endDate); WorkTasksReportResult GetWorkTasksReportResult(Guid orgId, DateTime startDate, DateTime endDate); WorkTasksReportResult GetWorkTasksReportResult(Guid orgId, DateTime startDate, DateTime endDate, int rows, int page); }}
using AIMS.Bussiness.ApplicationDto.NJWorkTasks;using AIMS.Model;using Common.BaseLibrary.Unity;using Common.IRepositories;using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using AIMS.Bussiness.ApplicationDto.NJWorkTasksDto;using AIMS.Bussiness.ApplicationDto.Dto;using Microsoft.Practices.Unity;using AIMS.Bussiness.ApplicationDto.Enum;using AIMS.Bussiness.Application.Application.WorkDefaultConfigApp;using System.Net.Http;using System.IO;namespace AIMS.Bussiness.Application.Application.WorkTasksDetailsApp{ public class WorkTasksDetailsAppService : IWorkTasksDetailsAppService { ////// /// /// /// /// /////public List GetWorkTasksDetailsList(Guid orgId, DateTime startDate, DateTime endDate) //{ // //WorkTasksDetailsModel model = new WorkTasksDetailsModel(); // IExtensionRepository NJWorkTasksService = DIFactory.ObjectContainer.Resolve >(); // IExtensionRepository orgService = DIFactory.ObjectContainer.Resolve >(); // IExtensionRepository userService = DIFactory.ObjectContainer.Resolve >(); // IExtensionRepository OperatorService = DIFactory.ObjectContainer.Resolve >(); // IExtensionRepository OperationTypeService = DIFactory.ObjectContainer.Resolve >(); // IExtensionRepository NJService = DIFactory.ObjectContainer.Resolve >(); // var NJWorkTasksQuery = NJWorkTasksService.GetModel(p => p.TaskStopTime != null && p.TaskStartTime != null && p.TaskStopTime >= startDate && p.TaskStopTime <= endDate && p.OrgId == orgId).AsEnumerable(); // var OperationTypeQuery = OperationTypeService.GetModel(p => p.IsDeleted != true).AsEnumerable(); // var NJQuery = NJService.GetModel(p => p.IsDeleted != true).AsEnumerable(); // //var orgList = orgService.GetModel().Where(p => p.IsDisabled != true).ToList(); // //var userList = userService.GetModel().Where(p => p.IsDeleted != true).ToList(); // //var query = from n in NJWorkTasksQuery // // join u in NJQuery on n.TaskNJID equals u.Id // // join o in OperationTypeQuery on n.TaskOperationTypeID equals o.TypeDef into jwo // // from o in jwo.DefaultIfEmpty() // // join s in userList on n.DriverId equals s.Id into jwd // // from s in jwd.DefaultIfEmpty() // // select new WorkTasksDetailsModel // // { // // TaskName = n.TaskName, // // OperationName = o != null ? o.OperationName : "", // // NJName = u.NJName, // // TaskStopTime = n.TaskStopTime, // // WorkingHours = Math.Round((n.TaskStopTime.Value - n.TaskStartTime.Value).TotalHours, 1), // // TaskOptAreaSum = n.TaskOptAreaSum, // // TaskLengthSum = n.TaskLengthSum, // // TaskOptPrice = n.TaskOptPrice, // // TaskAllowanceSum = n.TaskAllowanceSum, // // FarmerName = "", // // DriverName = s != null ? s.Name : "", // // OpinionRating = "5星", // // AuditSituation = "已审核", // // }; // return query.ToList(); //} public WorkTasksReportResult GetWorkTasksReportResult(Guid orgId, DateTime startDate, DateTime endDate) { WorkTasksReportResult model = new WorkTasksReportResult(); var list = GetWorkTasksDetailsList(orgId, startDate, endDate); model.percentofpass = 90; model.WorkTasksDetailsModels = list; model.TotalTaskAllowanceSum = 0; model.TotalTaskLengthSum = 0; model.TotalTaskNum = 0; model.TotalTaskOptAreaSum = 0; model.TotalTaskOptPrice = 0; model.TotalWorkingHours = 0; model.NJTypeStatsPie = new ChartPieDto(); model.TimeSpanStatsBar = new ChartBarDto(); //柱状图dto var dtSpan = endDate - startDate; bool isByDay = false; if (dtSpan.TotalHours > 24) { //按天统计 isByDay = true; var date = startDate.Date; while (date <= endDate) { model.TimeSpanStatsBar.XAxisData.Add(date.ToString("yyyy-MM-dd")); model.TimeSpanStatsBar.SeriesData.Add(0); date = date.AddDays(1); } } else { //按小时统计 var date = startDate.Date.AddHours(startDate.Hour); while (date <= endDate) { model.TimeSpanStatsBar.XAxisData.Add(date.ToString("HH:mm")); model.TimeSpanStatsBar.SeriesData.Add(0); date = date.AddHours(1); } } foreach (var item in list) { //model.TotalTaskAllowanceSum += item.TaskAllowanceSum ?? 0; model.TotalTaskLengthSum += item.TaskLengthSum ?? 0; model.TotalTaskNum += 1; model.TotalTaskOptAreaSum += item.TaskOptAreaSum ?? 0; //model.TotalTaskOptPrice += item.TaskOptPrice ?? 0; model.TotalWorkingHours += item.WorkingHours ?? 0; //饼图dto int index = model.NJTypeStatsPie.LegendData.IndexOf(item.NJTypeName); if (index < 0) { model.NJTypeStatsPie.LegendData.Add(item.NJTypeName); model.NJTypeStatsPie.SeriesData.Add(new ChartPieSeriesDataItem { name = item.NJTypeName, value = item.TaskOptAreaSum ?? 0 }); } else { model.NJTypeStatsPie.SeriesData[index].value += item.TaskOptAreaSum ?? 0; } //柱状图dto DateTime optDate = Convert.ToDateTime(item.OperationEnd); string xAxisDate = ""; if (isByDay) { xAxisDate = optDate.Date.ToString("yyyy-MM-dd"); } else { xAxisDate = optDate.Date.AddHours(optDate.Hour).ToString("HH:mm"); } index = model.TimeSpanStatsBar.XAxisData.IndexOf(xAxisDate); if (index > 0) { model.TimeSpanStatsBar.SeriesData[index] += item.TaskOptAreaSum ?? 0; } else { //应该没有这种情况 } } return model; } //=============================================================================================================================== public WorkTasksReportResult GetWorkTasksReportResult(Guid orgId, DateTime startDate, DateTime endDate, int rows, int page) { var model = GetWorkTasksReportResult(orgId, startDate, endDate); IEnumerable WorkTasksDetailsList = model.WorkTasksDetailsModels; int startIndex = (page - 1) * rows; if (model.TotalTaskNum > rows && rows != -1) { if ((startIndex + rows) > model.TotalTaskNum) WorkTasksDetailsList = WorkTasksDetailsList.Skip(startIndex).Take(model.TotalTaskNum - startIndex); else WorkTasksDetailsList = WorkTasksDetailsList.Skip(startIndex).Take(rows); } model.WorkTasksDetailsModels = WorkTasksDetailsList.ToList(); return model; } //========================================================================================================================================== //==========================================================Zoe====================================================================================== public List GetWorkTasksDetailsList(Guid orgId, DateTime startDate, DateTime endDate) { //WorkTasksDetailsModel model = new WorkTasksDetailsModel(); IExtensionRepository NJWorkTasksService = DIFactory.ObjectContainer.Resolve >(); //IExtensionRepository orgService = DIFactory.ObjectContainer.Resolve >(); //IExtensionRepository userService = DIFactory.ObjectContainer.Resolve >(); //IExtensionRepository OperatorService = DIFactory.ObjectContainer.Resolve >(); IExtensionRepository OperationTypeService = DIFactory.ObjectContainer.Resolve >(); IExtensionRepository NJService = DIFactory.ObjectContainer.Resolve >(); IExtensionRepository NJTypeService = DIFactory.ObjectContainer.Resolve >(); var NJWorkTasksQuery = NJWorkTasksService.GetModel(p => p.TaskStartTime != null && p.OperationEnd >= startDate && p.OperationEnd <= endDate && p.OrgId == orgId).AsEnumerable(); var OperationTypeQuery = OperationTypeService.GetModel(p => p.IsDeleted != true).AsEnumerable(); var NJQuery = NJService.GetModel(p => p.IsDeleted != true).AsEnumerable(); var NJTypeQuery = NJTypeService.GetModel().AsEnumerable(); var query = from n in NJWorkTasksQuery join u in NJQuery on n.TaskNJID equals u.Id join o in OperationTypeQuery on n.TaskOperationTypeID equals o.TypeDef into jwo from o in jwo.DefaultIfEmpty() join t in NJTypeQuery on u.NJTypeID equals t.Id into jut from t in jut.DefaultIfEmpty() select new WorkTasksDetailsModel { TaskName = n.TaskName, OperationName = o != null ? o.OperationName : "", NJName = u.NJName, OperationStart = n.OperationStart.ToString("yyyy-MM-dd HH:mm:ss"), OperationEnd = n.OperationEnd.ToString("yyyy-MM-dd HH:mm:ss"), WorkingHours = Math.Round((n.OperationEnd - n.OperationStart).TotalHours, 1), TaskOptAreaSum = n.TaskOptAreaSum, TaskLengthSum = n.TaskLengthSum, NJTypeID = u.NJTypeID, NJTypeName = t != null ? t.ItemsName : "" }; return query.ToList(); } //====================================================================Zoe============================================================================== }}
using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.Http;using AIMS.Bussiness.Interface;using AIMS.Web.Helper;using AIMS.Bussiness.Application.Application.WorkTasksDetailsApp;using AIMS.Bussiness.ApplicationDto.Dto;using AIMS.Bussiness.ApplicationDto.NJWorkTasksDto;using AIMS.Bussiness.ApplicationDto.NJWorkTasks;using System.Net;using System.Web.Hosting;using System.Net.Http;using AIMS.Bussiness.Application.Application.OrgApp;using Common.BaseLibrary.Unity;using Microsoft.Practices.Unity;using Microsoft.Reporting.WebForms;namespace AIMS.Web.Api{ public class WorkTasksDetailsController : ApiBaseController { private IWorkTasksDetailsAppService iWorkTasksDetailsAppService = null; public WorkTasksDetailsController(IWorkTasksDetailsAppService worktaskdetailsappService, IAIMSOrganizationService organizationService) : base(organizationService) { this.iWorkTasksDetailsAppService = worktaskdetailsappService; } private bool ParseDateRange(string mode, string date, out DateTime? dateStart, out DateTime? dateEnd, out string message) { if (mode == null) mode = ""; dateStart = null; dateEnd = null; message = ""; if (string.IsNullOrEmpty(date)) { message = "未提供日期参数"; return false; } DateTime startDate = DateTime.Today; DateTime endDate = DateTime.Today; if (mode == "日") { if (!DateTime.TryParse(date, out startDate)) { message = "日期参数不符合要求(例:2017-12-4)"; return false; } } else if (mode == "月") { var firstDay = date + "-1"; if (!DateTime.TryParse(firstDay, out startDate)) { message = "月份参数不符合要求(例:2017-12)"; return false; } endDate = startDate.AddMonths(1).AddMilliseconds(-1); } else if (mode == "周") { DateTime curr; if (!DateTime.TryParse(date, out curr)) { message = "周参数不符合要求(例:2017-12-4)"; return false; } int dayOfweek = (int)curr.DayOfWeek; if (dayOfweek == 0) dayOfweek = 7; startDate = curr.AddDays(0 - dayOfweek + 1); endDate = startDate.AddDays(7); } //Zoe else if (mode == "") { string[] arr = date.Split('~'); if (arr.Length == 2) { string left = arr[0].Trim().Trim('+'); startDate = Convert.ToDateTime(left); string right = arr[1].Trim().Trim('+'); endDate = Convert.ToDateTime(right); } else { message = "参数不符合要求(例:2017-12-1~2018-11-2)"; return false; } } //Zoe else { message = "未知的mode参数"; return false; } dateStart = startDate; dateEnd = endDate; return true; } ////// 作业统计查询 /// /// 组织Id /// ["日"|"月"|"周"|""] /// 时间范围(日:2017-12-1;月:2017-12;周:2017-12-4;区间:2017-12-4 ~ 2018-11-1;) /// 行数 /// 页面 ///[HttpGet] [Route("api/api/WorkTasksDetails/GetWorkTasksReportResultByPage")] public DtoResult GetWorkTasksReportResultByPage(Guid orgId, string mode, string date, int rows, int page) { DateTime? startDate = null; DateTime? endDate = null; string message = ""; if (!ParseDateRange(mode, date, out startDate, out endDate, out message)) { return DtoResultHelper .RetDtoResult((int)HttpStatusCode.NotFound, message, false, null); } WorkTasksReportResult model = iWorkTasksDetailsAppService.GetWorkTasksReportResult(orgId, startDate.Value, endDate.Value, rows, page); return DtoResultHelper .RetDtoResult((int)HttpStatusCode.OK, "成功", true, model); } public class DsMain { public string OrgName { get; set; } public string Mode { get; set; } public string QDate { get; set; } } //========================================================================================================================================== /// /// 打印作业统计 /// /// 组织Id /// ["日"|"月"|"周"|""] /// 时间范围(日:2017-12-1;月:2017-12;周:2017-12-4;区间:2017-12-4 ~ 2017-12-10) ///[HttpGet] [Route("api/api/WorkTasksDetails/GetDataPrint")] public DtoResult GetDataPrint(Guid orgId, string mode, string date) { DateTime? startDate = null; DateTime? endDate = null; string message = ""; if (!ParseDateRange(mode, date, out startDate, out endDate, out message)) { return DtoResultHelper .RetDtoResult((int)HttpStatusCode.NotFound, message, false, null); } List parameters = new List (); IOrgAppService orgService = DIFactory.ObjectContainer.Resolve (); string orgName = orgService.GetOrgName(orgId); parameters.Add(new ReportParameter("orgName", orgName)); parameters.Add(new ReportParameter("mode", mode)); parameters.Add(new ReportParameter("qDate", (mode == "日" || mode == "月") ? date : startDate.Value.ToString("yyyy-M-d") + " ~ " + endDate.Value.ToString("yyyy-M-d"))); var list = iWorkTasksDetailsAppService.GetWorkTasksDetailsList(orgId, startDate.Value, endDate.Value); //var list = new List (); //for(int i = 1; i <= 10; i++) //{ // list.Add(new WorkTasksDetailsModel { // Id = i, // NJName="nongji"+i, // OperationName="深耕作业", // TaskName="zuoyedanhao"+1, // OperationStart="2017-10-01", // OperationEnd= "2017-10-01", // WorkingHours=i, // TaskLengthSum=i, // TaskOptAreaSum=i // }); //} var sources = new Dictionary (); sources.Add("DataSet1", list); string result = ReportHelper.Print(HostingEnvironment.MapPath("~/Reports/ReportWorks.rdlc"), sources, parameters); return DtoResultHelper .RetDtoResult((int)HttpStatusCode.OK, "成功", true, Url.Content(result)); } //=========================================================================================================================================================================================== /// /// 作业统计导出到Excel /// /// 组织Id /// ["日"|"月"|"周"|""] /// 时间范围(日:2017-12-1;月:2017-12;周:2017-12-4;区间:2017-12-4 ~ 2017-12-10) ///[HttpGet] [Route("api/api/WorkTasksDetails/ExportExcel")] public HttpResponseMessage ExportExcel(Guid orgId, string mode, string date) { DateTime? startDate = null; DateTime? endDate = null; string message = ""; if (!ParseDateRange(mode, date, out startDate, out endDate, out message)) { return new HttpResponseMessage(HttpStatusCode.NotFound); } List parameters = new List (); IOrgAppService orgService = DIFactory.ObjectContainer.Resolve (); string orgName = orgService.GetOrgName(orgId); parameters.Add(new ReportParameter("orgName", orgName)); parameters.Add(new ReportParameter("mode", mode)); parameters.Add(new ReportParameter("qDate", (mode == "日" || mode == "月") ? date : startDate.Value.ToString("yyyy-M-d") + " ~ " + endDate.Value.ToString("yyyy-M-d"))); var list = iWorkTasksDetailsAppService.GetWorkTasksDetailsList(orgId, startDate.Value, endDate.Value); //var list = new List (); //for (int i = 1; i <= 10; i++) //{ // list.Add(new WorkTasksDetailsModel // { // Id = i, // NJName = "nongji" + i, // OperationName = "深耕作业", // TaskName = "zuoyedanhao" + 1, // OperationStart = "2017-10-01", // OperationEnd = "2017-10-01", // WorkingHours = i, // TaskLengthSum = i, // TaskOptAreaSum = i // }); //} var sources = new Dictionary (); sources.Add("DataSet1", list); return ReportHelper.ExportExcel(HostingEnvironment.MapPath("~/Reports/ReportWorks.rdlc"), sources, parameters); } //================================================================================================================================================================= }}
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace AIMS.Bussiness.ApplicationDto.Dto{ ////// 柱状图/条形图 /// public class ChartBarDto { public ListXAxisData { get; set; } = new List (); public List SeriesData { get; set; } = new List (); }}
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace AIMS.Bussiness.ApplicationDto.Dto{ ////// 饼图 /// public class ChartPieDto { public ListLegendData { get; set; } = new List (); public List SeriesData { get; set; } = new List (); } /// /// 饼图的Series数据项 /// public class ChartPieSeriesDataItem { //用小写,方便前端 public string name { get; set; } public double value { get; set; } }}