/// <summary> /// 采用ado 查询一段时间内的里程 /// </summary> /// <param name="para"></param> /// <returns></returns> public List<ReportMileageViewModel> GetNewMileage(ReportMileageWCFPara para) { List<ReportMileageViewModel> ReportMileageViewModelList = new List<ReportMileageViewModel>(); try { if (para.VehicleCode == null || para.VehicleCode.Count() == 0) return ReportMileageViewModelList; VehicleDAL vdal = new VehicleDAL(); List<EHistoryDataStoreConfig> tableList = vdal.GetVechileInfo(para.VehicleCode.ToList()); //获得VehicleCode和LicenceNuber之间的关系 Dictionary<Guid, string> dicCodeAndLicence = new Dictionary<Guid, string>(); for (int i = 0, i_Count = para.VehicleCode.Count(); i < i_Count; i++) { dicCodeAndLicence.Add(para.VehicleCode[i], para.LicenceNumber[i]); } foreach (EHistoryDataStoreConfig config in tableList) { if (config.StoreTable == null) continue; List<Guid> vehicleList = tableList.Where(b=>b.StoreTable==config.StoreTable).Select(c => c.VehicleCode).ToList(); List<EMileage> EMileageList = GetReportTimeSpanMileage(config, vehicleList, para.BeginTime, para.EndTime, false); foreach (EMileage objEMileage in EMileageList) { ReportMileageViewModel objModel = new ReportMileageViewModel(); objModel.VehicleCode = objEMileage.VehicleCode; objModel.LicenceNumber = dicCodeAndLicence[objModel.VehicleCode]; objModel.Mileage = objEMileage.TotalMileage; ReportMileageViewModelList.Add(objModel); } } } catch (Exception ex) { Logger.Error("GetMileage" + ex.Message, ex); } return ReportMileageViewModelList; }
///// <summary> ///// 查询一段时间内的里程明细(里程报表,里程日报表,里程月报表) ///// </summary> ///// <param name="para"></param> ///// <returns></returns> //public List<ReportMileageDetailViewModel> GetMileageDetail(ReportMileageDetailWCFPara para) //{ // Guid guid = Guid.NewGuid(); // Logger.Info("In GetMileageDetail:" + guid.ToString()); // List<ReportMileageDetailViewModel> ReportMileageDetailViewModelList = new List<ReportMileageDetailViewModel>(); // try // { // //取出车辆列表所用到的历史数据配置信息列表 // List<EHistoryDataStoreConfig> configList = tableConfigSev.GetVehicleStoreTableConfig(para.VehicleCode.ToList()); // //对历史数据配置信息中的StoreTable字段进行GroupBy操作,去除重复 // var q = from f in configList group f by f.StoreTable into g select new EHistoryDataStoreConfig { StoreTable = g.Key }; // List<EHistoryDataStoreConfig> tableList = q.ToList(); // //获得VehicleCode和LicenceNuber之间的关系 // Dictionary<Guid, string> dicCodeAndLicence = new Dictionary<Guid, string>(); // for (int i = 0, i_Count = para.VehicleCode.Count(); i < i_Count; i++) // { // dicCodeAndLicence.Add(para.VehicleCode[i], para.LicenceNumber[i]); // } // foreach (EHistoryDataStoreConfig config in tableList) // { // if (config.StoreTable == null) // continue; // //将同一个历史表的车辆里程数据一次性查询出来 // var query = from f in configList // where f.StoreTable == config.StoreTable // select f.VehicleCode; // List<Guid> vehicleList = query.ToList<Guid>(); // List<EMileage> EMileageList = GetReportTimeSpanListMileage(config, vehicleList, para.TimeSpan, para.tenantCode, para.getPostition); // foreach (Guid vehicleCode in vehicleList) // { // List<EMileage> tmpList = EMileageList.Where(o => o.VehicleCode == vehicleCode).ToList(); // ReportMileageDetailViewModel objViewModel = new ReportMileageDetailViewModel(); // objViewModel.VehicleCode = vehicleCode; // objViewModel.LicenceNumber = dicCodeAndLicence[vehicleCode]; // if (tmpList == null || tmpList.Count == 0) // { // //objViewModel.MileageDetails = new List<TimeSpanMileage>(); // } // else // { // List<TimeSpanMileage> DetailList = new List<TimeSpanMileage>(); // foreach (EMileage objEMileage in tmpList) // { // TimeSpanMileage objTimeSpanMileage = new TimeSpanMileage() // { // QueryBeginTime = DateTime.MaxValue, // QueryEndTime = DateTime.MaxValue, // BeginTime = DateTime.MaxValue, // EndTime = DateTime.MaxValue, // Mileage = 0, // BeginLocation = string.Empty, // EndLocation = string.Empty, // BeginMileage = 0, // EndMileage = 0 // }; // objTimeSpanMileage.BeginTime = getDataTime(objEMileage.BeginTime); // objTimeSpanMileage.QueryBeginTime = getDataTime(objEMileage.QueryBeginTime); // objTimeSpanMileage.EndTime = getDataTime(objEMileage.EndTime); // objTimeSpanMileage.QueryEndTime = getDataTime(objEMileage.QueryEndTime); // objTimeSpanMileage.BeginMileage = objEMileage.BeginMileage; // objTimeSpanMileage.EndMileage = objEMileage.EndMileage; // if (para.getPostition) // { // objTimeSpanMileage.BeginLocation = objEMileage.BeginLocation; // objTimeSpanMileage.EndLocation = objEMileage.EndLocation; // } // objTimeSpanMileage.Mileage = objEMileage.TotalMileage; // DetailList.Add(objTimeSpanMileage); // } // //objViewModel.MileageDetails = DetailList; // } // ReportMileageDetailViewModelList.Add(objViewModel); // } // } // } // catch (Exception ex) // { // Logger.Error("Error GetMileageDetail(" + guid.ToString() + "):" + ex.Message, ex); // } // Logger.Info("Out GetMileageDetail:" + guid.ToString()); // return ReportMileageDetailViewModelList; //} /// <summary> /// 查询一段时间内的里程明细(里程报表,里程日报表,里程月报表) /// </summary> /// <param name="para"></param> /// <returns></returns> public List<ReportMileageDetailViewModel> GetMileageDetail(ReportMileageDetailWCFPara para) { Guid guid = Guid.NewGuid(); Logger.Info("In GetMileageDetail:" + guid.ToString()); List<ReportMileageDetailViewModel> ReportMileageDetailViewModelList = new List<ReportMileageDetailViewModel>(); try { /* //取出车辆列表所用到的历史数据配置信息列表(select * from gps_historydatastoreconfig where VehicleCode in {0}) List<EHistoryDataStoreConfig> configList = tableConfigSev.GetVehicleStoreTableConfig(para.VehicleCode.ToList()); //对历史数据配置信息中的StoreTable字段进行GroupBy操作,去除重复 var q = from f in configList group f by f.StoreTable into g select new EHistoryDataStoreConfig { StoreTable = g.Key }; List<EHistoryDataStoreConfig> tableList = q.ToList(); */ //代码优化 VehicleDAL veDal = new VehicleDAL(); List<EHistoryDataStoreConfig> tableList = veDal.GetVechileInfo(para.VehicleCode.ToList()); //获得VehicleCode和LicenceNuber之间的关系 Dictionary<Guid, string> dicCodeAndLicence = new Dictionary<Guid, string>(); for (int i = 0, i_Count = para.VehicleCode.Count(); i < i_Count; i++) { dicCodeAndLicence.Add(para.VehicleCode[i], para.LicenceNumber[i]); } foreach (EHistoryDataStoreConfig config in tableList) { if (config.StoreTable == null) continue; //将同一个历史表的车辆里程数据一次性查询出来 /*var query = from f in configList where f.StoreTable == config.StoreTable select f.VehicleCode; List<Guid> vehicleList = query.ToList<Guid>(); */ List<Guid> vehicleList = tableList.Where(b => b.StoreTable == config.StoreTable).Select(c => c.VehicleCode).ToList(); List<EMileage> EMileageList = GetReportTimeSpanListMileage(config, vehicleList, para.TimeSpan, para.tenantCode, para.getPostition); foreach (EMileage eMileage in EMileageList) { ReportMileageDetailViewModel model = new ReportMileageDetailViewModel(); model.VehicleCode = eMileage.VehicleCode; model.LicenceNumber = dicCodeAndLicence[eMileage.VehicleCode]; model.BeginLocation = eMileage.BeginLocation; model.EndLocation = eMileage.EndLocation; model.BeginTime = eMileage.BeginTime; model.EndTime = eMileage.EndTime; model.DayMileage = eMileage.TotalMileage; ReportMileageDetailViewModelList.Add(model); } ReportMileageDetailViewModelList = ReportMileageDetailViewModelList.OrderBy(p => p.LicenceNumber).ToList(); } } catch (Exception ex) { Logger.Error("Error GetMileageDetail(" + guid.ToString() + "):" + ex.Message, ex); } Logger.Info("Out GetMileageDetail:" + guid.ToString()); return ReportMileageDetailViewModelList; }
/// <summary> /// 里程汇总报表 /// </summary> /// <param name="para"></param> /// <returns></returns> //public List<ReportMainViewModel> GetReportMainViewModel(ReportMainWCFPara para) //{ // System.Diagnostics.Stopwatch sh = new System.Diagnostics.Stopwatch(); // sh.Start(); // Guid guid = Guid.NewGuid(); // Logger.Info("In GetReportMainViewModel:" + guid.ToString()); // List<ReportMainViewModel> ltModel = new List<ReportMainViewModel>(); // try // { // //今日里程 // DateTime BeginTime1 = new DateTime(para.QueryTime.Year, para.QueryTime.Month, para.QueryTime.Day); // DateTime EndTime1 = para.QueryTime; // //昨日里程 // DateTime BeginTime2 = BeginTime1.AddDays(-1); // DateTime EndTime2 = BeginTime1.AddMilliseconds(-1); // //近7天里程 // DateTime BeginTime3 = para.QueryTime.AddDays(-7); // DateTime EndTime3 = para.QueryTime; // //近30天里程 // DateTime BeginTime4 = para.QueryTime.AddDays(-30); // DateTime EndTime4 = para.QueryTime; // //这里改为查询当前绑定了GPS的车辆,如果某车辆当前未绑定GPS,之前有绑定,也有里程数据,仍然不查询 // //IList<EBaseVehicle> ltVehicle = objVehicleService.GetUsingVehilceList(para.tenantCode); // IList<Guid> ltVehicle = para.VehicleCode; // IList<string> ltLicenceNumber = para.LicenceNumber; // if (ltVehicle != null) // { // for (int i = 0, i_Count = ltVehicle.Count; i < i_Count; i++) // { // EMileage Model1 = GetReportTimeSpanMileage(ltVehicle[i], BeginTime1, EndTime1, false); // EMileage Model2 = GetReportTimeSpanMileage(ltVehicle[i], BeginTime2, EndTime2, false); // EMileage Model3 = GetReportTimeSpanMileage(ltVehicle[i], BeginTime3, EndTime3, false); // EMileage Model4 = GetReportTimeSpanMileage(ltVehicle[i], BeginTime4, EndTime4, false); // ReportMainViewModel viewModel = new ReportMainViewModel(); // viewModel.VehicleCode = ltVehicle[i]; // Guid objvehicle = ltVehicle.FirstOrDefault(o => o == viewModel.VehicleCode); // viewModel.LicenceNumber = objvehicle == null ? null : ltLicenceNumber[i]; // viewModel.MileageOfTody = Model1.TotalMileage; // viewModel.MileageOfYestoday = Model2.TotalMileage; // viewModel.MileageOfLast7Day = Model3.TotalMileage; // viewModel.MileageOfLast30Day = Model4.TotalMileage; // ltModel.Add(viewModel); // } // } // } // catch (Exception ex) // { // Logger.Error("Error GetReportMainViewModel(" + guid.ToString() + "):" + ex.Message, ex); // } // Logger.Info("Out GetReportMainViewModel:" + guid.ToString()); // sh.Stop(); // Logger.Info("时间:" + sh.ElapsedMilliseconds); // return ltModel; //} public List<ReportMainViewModel> GetReportMainViewModel(ReportMainWCFPara para) { //System.Diagnostics.Stopwatch sh = new System.Diagnostics.Stopwatch(); //sh.Start(); //Guid guid = Guid.NewGuid(); //Logger.Info("In GetReportMainViewModel:" + guid.ToString()); //需要返回的实体 List<ReportMainViewModel> ltModel = new List<ReportMainViewModel>(); try { ////今日里程 //DateTime BeginTime1 = new DateTime(para.QueryTime.Year, para.QueryTime.Month, para.QueryTime.Day); //DateTime EndTime1 = para.QueryTime; ////昨日里程 //DateTime BeginTime2 = BeginTime1.AddDays(-1); //DateTime EndTime2 = BeginTime1.AddMilliseconds(-1); ////近7天里程 //DateTime BeginTime3 = para.QueryTime.AddDays(-7); //DateTime EndTime3 = para.QueryTime; ////近30天里程 //DateTime BeginTime4 = para.QueryTime.AddDays(-29); //DateTime EndTime4 = para.QueryTime; //这里改为查询当前绑定了GPS的车辆,如果某车辆当前未绑定GPS,之前有绑定,也有里程数据,仍然不查询 //IList<EBaseVehicle> ltVehicle = objVehicleService.GetUsingVehilceList(para.tenantCode); List<Guid> ltVehicle = para.VehicleCode.ToList(); List<string> ltLicenceNumber = para.LicenceNumber.ToList(); if (ltVehicle != null) { for (int i = 0, i_Count = ltVehicle.Count; i < i_Count; i++) { ReportMainViewModel viewModel = new ReportMainViewModel(); viewModel.VehicleCode = ltVehicle[i]; viewModel.LicenceNumber = ltLicenceNumber[i]; ltModel.Add(viewModel); } /* * //取出车辆列表所用到的历史数据配置信息列表 List<EHistoryDataStoreConfig> configList = tableConfigSev.GetVehicleStoreTableConfig(ltVehicle); //对历史数据配置信息中的StoreTable字段进行GroupBy操作,去除重复 var q = from f in configList group f by f.StoreTable into g select new EHistoryDataStoreConfig { StoreTable = g.Key }; List<EHistoryDataStoreConfig> tableList = q.ToList(); */ //代码优化后 VehicleDAL veDal = new VehicleDAL(); List<EHistoryDataStoreConfig> tableList = veDal.GetVechileInfo(ltVehicle); //实例化存放车辆当天、昨天、上周、上月里程数据实体列表 //List<EMileage> Model1 = new List<EMileage>(); //List<EMileage> Model2 = new List<EMileage>(); //List<EMileage> Model3 = new List<EMileage>(); //List<EMileage> Model4 = new List<EMileage>(); List<EMileage> Model = new List<EMileage>(); foreach (EHistoryDataStoreConfig config in tableList) { if (config.StoreTable == null) continue; /* //将同一个历史表的车辆里程数据一次性查询出来 //var query = from f in configList // where f.StoreTable == config.StoreTable // select f.VehicleCode; //List<Guid> vehicleList = query.ToList<Guid>(); */ List<Guid> vehicleList = tableList.Where(b=>b.StoreTable==config.StoreTable).Select(c => c.VehicleCode).ToList(); //List<Guid> vehicleList = ltVehicle.Where(f => f == config.VehicleCode).ToList(); //分别获取指定车辆列表所有里程类型的里程数据 //Model1.AddRange(GetReportTimeSpanMileage(config, vehicleList, BeginTime1, EndTime1, false)); //Model2.AddRange(GetReportTimeSpanMileage(config, vehicleList, BeginTime2, EndTime2, false)); //Model3.AddRange(GetReportTimeSpanMileage(config, vehicleList, BeginTime3, EndTime3, false)); //Model4.AddRange(GetReportTimeSpanMileage(config, vehicleList, BeginTime4, EndTime4, false)); Model.AddRange(GetReportTimeSpanMileage(config, vehicleList, para.beginTime, para.endTime, false)); } EMileage tmp = null; foreach (ReportMainViewModel model in ltModel) { //tmp = Model1.FirstOrDefault(f => f.VehicleCode == model.VehicleCode); //model.MileageOfTody = tmp == null ? 0 : tmp.TotalMileage; //tmp = Model2.FirstOrDefault(f => f.VehicleCode == model.VehicleCode); //model.MileageOfYestoday = tmp == null ? 0 : tmp.TotalMileage; //tmp = Model3.FirstOrDefault(f => f.VehicleCode == model.VehicleCode); //model.MileageOfLast7Day = tmp == null ? 0 : tmp.TotalMileage; //tmp = Model4.FirstOrDefault(f => f.VehicleCode == model.VehicleCode); //model.MileageOfLast30Day = tmp == null ? 0 : tmp.TotalMileage; tmp = Model.FirstOrDefault(f => f.VehicleCode == model.VehicleCode); model.MileageCount = tmp == null ? 0 : tmp.TotalMileage; } } } catch (Exception ex) { //Logger.Error("Error GetReportMainViewModel(" + guid.ToString() + "):" + ex.Message, ex); Logger.Error("Error GetReportMainViewModel:" + ex.Message, ex); } //Logger.Info("Out GetReportMainViewModel:" + guid.ToString()); //sh.Stop(); //Logger.Info("时间:" + sh.ElapsedMilliseconds); return ltModel; }