/// <summary>
/// 查询条件组合查询语句
/// </summary>
/// <param name="db"></param>
/// <param name="para"></param>
/// <returns></returns>
private IQueryable <RP_AADTSta> StrWhere(DataSubmittedEntities db, Common.QueryParameters para)
{
var strWhere = db.RP_AADTSta.Where(a => true);
if (para.StartTime != DateTime.Parse("0001/1/1 0:00:00") && para.StartTime != null)
{
DateTime dtStart = (DateTime)para.StartTime,
dtEnd = ((DateTime)para.StartTime).AddDays(1);
strWhere = strWhere.Where(a => a.CalculTime >= dtStart & a.CalculTime < dtEnd);
}
//获取配置中的默认日期的开始日期
else
{
var config = db.OT_HDayConfig.Where(a => a.Id == 9).Select(a => new
{
HDayEnd = (DateTime)a.HDayEnd,
HDayStart = (DateTime)a.HDayStart
}).ToList();
if (config != null & config.Count > 0)
{
DateTime dtStart = config[0].HDayStart,
dtEnd = ((DateTime)config[0].HDayStart).AddDays(1);
strWhere = strWhere.Where(a => a.CalculTime >= dtStart & a.CalculTime < dtEnd);
}
}
return(strWhere);
}
/// <summary>
/// 查询假期进出京交通量（北京段）
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public Common.ViewModels.IReportViewModel GetListByPra(Common.QueryParameters para)
{
List <HdayExEnViewModel> list = GetData(para);
modelHdayExEn.HdayExEn = list;
return(modelHdayExEn);
}
/// <summary>
/// 导出
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public string ExportReport(Common.QueryParameters para)
{
string path = string.Empty;
string reportpath = string.Empty;
if (listExport == null || listExport.Count == 0)
{
GetData(para);
}
if (para.ReportType == 11)
{
if (para.StartTime != null && para.EndTime != null)
{
int count = (para.EndTime.Value - para.StartTime.Value).Days + 1;
if ((para.EndTime.Value - para.StartTime.Value).Days + 1 > 15)
{
count = 15;
}
path = string.Format(@"{0}Reporttemplate\DynamicReport\{1}\编号11--假期重点收费站流量表（出口加入口）.xlsx", AppDomain.CurrentDomain.BaseDirectory, count);
reportpath = ExportHelper.GetInstance().ExportExcel(path, para, this);
}
reportpath = ExportHelper.GetInstance().ExportExcel(path, para, this);
}
return(reportpath);
}
/// <summary>
/// 导出
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public string ExportReport(Common.QueryParameters para)
{
string path = string.Empty;
string reportpath = string.Empty;
if (para.ReportType == 6)
{
path = string.Format(@"{0}Reporttemplate\编号06--重点城市出入境车辆数日报表（泗村店）.xlsx", AppDomain.CurrentDomain.BaseDirectory);
reportpath = ExportHelper.GetInstance().ExportExcel(path, para, this);
}
return(reportpath);
}
/// <summary>
/// 导出
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public string ExportReport(Common.QueryParameters para)
{
string path = string.Empty;
string reportpath = string.Empty;
if (para.ReportType == 5)
{
path = AppDomain.CurrentDomain.BaseDirectory + @"Reporttemplate\编号05--重点城市出入境车辆数日报表（北京段）.xlsx";
reportpath = ExportHelper.GetInstance().ExportExcel(path, para, this);
}
return(reportpath);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public Common.ViewModels.IReportViewModel GetListByPra(Common.QueryParameters para)
{
qModel.ReportData = GetData(para);
//判断当前统计站类型，数据是否完整
if (GetNoDataList(para) != null && GetNoDataList(para).Count() > 0)
{
qModel.IsFull = 0;//不完整
}
else
{
qModel.IsFull = 1;//完整
}
return(qModel);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public Common.ViewModels.IReportViewModel GetListByPra(Common.QueryParameters para)
{
List <HDayStaExEnViewModel> list = GetData(para);
qModel.ReportData = list;
if (para.EndTime != null && para.StartTime != null)
{
qModel.DateTotal = (para.EndTime.Value - para.StartTime.Value).Days + 1;
}
//判断当前统计站类型，数据是否完整
if (GetNoDataList(para) != null && GetNoDataList(para).Count() > 0)
{
qModel.IsFull = 0;//不完整
}
else
{
qModel.IsFull = 1;//完整
}
return(qModel);
}
/// <summary>
/// 查询条件组合查询语句
/// </summary>
/// <param name="db"></param>
/// <param name="para"></param>
/// <returns></returns>
private IQueryable <RP_HDayAADT> StrWhere(DataSubmittedEntities db, Common.QueryParameters para)
{
var strWhere = db.RP_HDayAADT.Where(a => true);
//某时间段之间的所有数据
if (para.EndTime != DateTime.Parse("0001/1/1 0:00:00") && para.StartTime != DateTime.Parse("0001/1/1 0:00:00") && para.EndTime != null && para.StartTime != null && para.StartTime <= para.EndTime)
{
DateTime dt = ((DateTime)para.EndTime).AddDays(1);
strWhere = strWhere.Where(a => a.CalcuTime >= para.StartTime & a.CalcuTime < dt);
}
//大于某时间的所有数据
else if (para.StartTime != null && para.StartTime != DateTime.Parse("0001/1/1 0:00:00"))
{
strWhere = strWhere.Where(a => a.CalcuTime >= para.StartTime);
}
//小于某时间的所有数据
else if (para.EndTime != null && para.EndTime != DateTime.Parse("0001/1/1 0:00:00"))
{
DateTime dt = ((DateTime)para.EndTime).AddDays(1);
strWhere = strWhere.Where(a => a.CalcuTime < dt);
}
//获取配置中的默认时间
else
{
var config = db.OT_HDayConfig.Where(a => a.Id == 11).Select(a => new
{
HDayEnd = (DateTime)a.HDayEnd,
HDayStart = (DateTime)a.HDayStart
}).ToList();
if (config != null & config.Count > 0)
{
DateTime dtEnd = ((DateTime)config[0].HDayEnd).AddDays(1),
dtStart = config[0].HDayStart;
strWhere = strWhere.Where(a => a.CalcuTime >= dtStart & a.CalcuTime <= dtEnd);
}
}
return(strWhere);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public Common.ViewModels.IReportViewModel GetListByPra(Common.QueryParameters para)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
RepairData(para);//补数据
var list = db.RP_EnEx.Where(a => a.CalcuTime == para.StartTime & a.StaType == para.StationType).ToList();
QueryEnExViewModel model = new QueryEnExViewModel();
if (list != null && list.Count > 0)
{
EnExViewModel cdMole = new EnExViewModel();
cdMole.CalcuTime = list[0].CalcuTime.ToString();
cdMole.EnGre = 0;//list[0].EnGre==null?0:list[0].EnGre;
cdMole.EnOthCar = list[0].EnOthCar;
cdMole.EnSmaCar = list[0].EnSmaCar;
cdMole.EnTruk = list[0].EnTruk;
cdMole.StaType = list[0].StaType;
cdMole.Name = "日车辆数(辆)";
cdMole.PEnGre = 0;
cdMole.PEnOthCar = 0;
cdMole.PEnSmaCar = 0;
cdMole.PEnTruk = 0;
cdMole.PExGre = 0;
cdMole.PExOthCar = 0;
cdMole.PExSmaCar = 0;
cdMole.PExTruk = 0;
model.ReportData = new List <EnExViewModel>();
model.ReportData.Add(cdMole);
EnExViewModel cdMole1 = new EnExViewModel();
cdMole1.Name = "去年同期";
model.ReportData.Add(cdMole1);
}
else
{
EnExViewModel cdMole = new EnExViewModel();
model.ReportData = new List <EnExViewModel>();
cdMole.Name = "日车辆数(辆)";
cdMole.PEnGre = 0;
cdMole.PEnOthCar = 0;
cdMole.PEnSmaCar = 0;
cdMole.PEnTruk = 0;
cdMole.PExGre = 0;
cdMole.PExOthCar = 0;
cdMole.PExSmaCar = 0;
cdMole.PExTruk = 0;
model.ReportData.Add(cdMole);
EnExViewModel cdMole1 = new EnExViewModel();
cdMole1.Name = "去年同期";
model.ReportData.Add(cdMole1);
}
//判断当前统计站类型，数据是否完整
if (GetNoDataList(para) != null && GetNoDataList(para).Count() > 0)
{
model.IsFull = 0;//不完整
}
else
{
model.IsFull = 1;//完整
}
return(model);
}
}
/// <summary>
/// 获取和构造报表11的数据
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
private List <HDayStaExEnViewModel> GetData(Common.QueryParameters para)
{
//补数据
RepairData(para);
List <HDayStaExEnViewModel> list = new List <HDayStaExEnViewModel>();
#region 构建数据结构
//构建列名行
HDayStaExEnViewModel modelTitle = new HDayStaExEnViewModel();
modelTitle.Belong = "所属高速";
modelTitle.Total = "合计";
modelTitle.Num = "序号";
modelTitle.Name = "收费站名称";
modelTitle.Date1 = para.StartTime.Value.AddDays(0).ToString("M月d日");
modelTitle.Date2 = para.StartTime.Value.AddDays(1).ToString("M月d日");
modelTitle.Date3 = para.StartTime.Value.AddDays(2).ToString("M月d日");
modelTitle.Date4 = para.StartTime.Value.AddDays(3).ToString("M月d日");
modelTitle.Date5 = para.StartTime.Value.AddDays(4).ToString("M月d日");
modelTitle.Date6 = para.StartTime.Value.AddDays(5).ToString("M月d日");
modelTitle.Date7 = para.StartTime.Value.AddDays(6).ToString("M月d日");
modelTitle.Date8 = para.StartTime.Value.AddDays(7).ToString("M月d日");
modelTitle.Date9 = para.StartTime.Value.AddDays(8).ToString("M月d日");
modelTitle.Date10 = para.StartTime.Value.AddDays(9).ToString("M月d日");
modelTitle.Date11 = para.StartTime.Value.AddDays(10).ToString("M月d日");
modelTitle.Date12 = para.StartTime.Value.AddDays(11).ToString("M月d日");
modelTitle.Date13 = para.StartTime.Value.AddDays(12).ToString("M月d日");
modelTitle.Date14 = para.StartTime.Value.AddDays(13).ToString("M月d日");
modelTitle.Date15 = para.StartTime.Value.AddDays(14).ToString("M月d日");
qModel.TitleList = new List <HDayStaExEnViewModel>();
qModel.TitleList.Add(modelTitle);
//构建杨村站流量行
HDayStaExEnViewModel modelYC = new HDayStaExEnViewModel();
modelYC.Belong = "京津塘高速";
modelYC.Num = "43";
modelYC.Name = "杨村站";
list.Add(modelYC);
//构建宜兴埠东站流量行
HDayStaExEnViewModel modelYXBD = new HDayStaExEnViewModel();
modelYXBD.Belong = "京津塘高速";
modelYXBD.Num = "44";
modelYXBD.Name = "宜兴埠东站";
list.Add(modelYXBD);
//构建宜兴埠西站流量行
HDayStaExEnViewModel modelYXBX = new HDayStaExEnViewModel();
modelYXBX.Belong = "京津塘高速";
modelYXBX.Num = "45";
modelYXBX.Name = "宜兴埠西站";
list.Add(modelYXBX);
//构建金钟路站流量行
HDayStaExEnViewModel modelJZL = new HDayStaExEnViewModel();
modelJZL.Belong = "京津塘高速";
modelJZL.Num = "46";
modelJZL.Name = "金钟路站";
list.Add(modelJZL);
//构建机场站流量行
HDayStaExEnViewModel modelJC = new HDayStaExEnViewModel();
modelJC.Belong = "京津塘高速";
modelJC.Num = "47";
modelJC.Name = "机场站";
list.Add(modelJC);
//构建空港经济区站流量行
HDayStaExEnViewModel modelKG = new HDayStaExEnViewModel();
modelKG.Belong = "京津塘高速";
modelKG.Num = "48";
modelKG.Name = "空港经济区站";
list.Add(modelKG);
//构建塘沽西站流量行
HDayStaExEnViewModel modelTGX = new HDayStaExEnViewModel();
modelTGX.Belong = "京津塘高速";
modelTGX.Num = "49";
modelTGX.Name = "塘沽西站";
list.Add(modelTGX);
//构建机场站流量行
HDayStaExEnViewModel modelTGXF = new HDayStaExEnViewModel();
modelTGXF.Belong = "京津塘高速";
modelTGXF.Num = "50";
modelTGXF.Name = "塘沽西分站";
list.Add(modelTGXF);
//构建塘沽北站流量行
HDayStaExEnViewModel modelTGB = new HDayStaExEnViewModel();
modelTGB.Belong = "京津塘高速";
modelTGB.Num = "51";
modelTGB.Name = "塘沽北站";
list.Add(modelTGB);
#endregion
if (para.StartTime != null)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//查询数据
var lst = StrWhere(db, para).OrderBy(a => a.CalcuTime).ToList();
// list.AddRange(lst);
//按条件查询后有数据
if (list != null && list.Count > 0)
{
//计算查询日期之间的差值
int countMax = (para.EndTime.Value - para.StartTime.Value).Days + 1;
for (int j = 0; j < lst.Count; j++)
{
#region 将数据库中存放的数据赋值到表中
for (int i = 1; i < 16; i++)
{
Type myType = qModel.TitleList[0].GetType();
PropertyInfo pinfo = myType.GetProperty("Date" + i);
if (lst[j].CalcuTime.Value.ToString("M月d日").ToString() == pinfo.GetValue(qModel.TitleList[0]).ToString())
{
Type type1 = list[0].GetType();
PropertyInfo pinfo1 = type1.GetProperty("Date" + i);
pinfo1.SetValue(list[0], lst[j].YC.ToString());
Type type2 = list[1].GetType();
PropertyInfo pinfo2 = type2.GetProperty("Date" + i);
pinfo2.SetValue(list[1], lst[j].YXBD.ToString());
Type type3 = list[2].GetType();
PropertyInfo pinfo3 = type3.GetProperty("Date" + i);
pinfo3.SetValue(list[2], lst[j].YXBX.ToString());
Type type4 = list[3].GetType();
PropertyInfo pinfo4 = type4.GetProperty("Date" + i);
pinfo4.SetValue(list[3], lst[j].JZL.ToString());
Type type5 = list[4].GetType();
PropertyInfo pinfo5 = type5.GetProperty("Date" + i);
pinfo5.SetValue(list[4], lst[j].JC.ToString());
Type type6 = list[5].GetType();
PropertyInfo pinfo6 = type6.GetProperty("Date" + i);
pinfo6.SetValue(list[5], lst[j].KG.ToString());
Type type7 = list[6].GetType();
PropertyInfo pinfo7 = type7.GetProperty("Date" + i);
pinfo7.SetValue(list[6], lst[j].TGX.ToString());
Type type8 = list[7].GetType();
PropertyInfo pinfo8 = type8.GetProperty("Date" + i);
pinfo8.SetValue(list[7], lst[j].TGXF.ToString());
Type type9 = list[8].GetType();
PropertyInfo pinfo9 = type9.GetProperty("Date" + i);
pinfo9.SetValue(list[8], lst[j].TGB.ToString());
break;
}
}
#endregion
}
}
#region 计算合计行
foreach (HDayStaExEnViewModel model in list)
{
double total = -1;
if (!string.IsNullOrEmpty(model.Date1))
{
total = double.Parse(model.Date1);
}
if (!string.IsNullOrEmpty(model.Date2))
{
total = total + double.Parse(model.Date2);
}
if (!string.IsNullOrEmpty(model.Date3))
{
total = total + double.Parse(model.Date3);
}
if (!string.IsNullOrEmpty(model.Date4))
{
total = total + double.Parse(model.Date4);
}
if (!string.IsNullOrEmpty(model.Date5))
{
total = total + double.Parse(model.Date5);
}
if (!string.IsNullOrEmpty(model.Date6))
{
total = total + double.Parse(model.Date6);
}
if (!string.IsNullOrEmpty(model.Date7))
{
total = total + double.Parse(model.Date7);
}
if (!string.IsNullOrEmpty(model.Date8))
{
total = total + double.Parse(model.Date8);
}
if (!string.IsNullOrEmpty(model.Date9))
{
total = total + double.Parse(model.Date9);
}
if (!string.IsNullOrEmpty(model.Date10))
{
total = total + double.Parse(model.Date10);
}
if (!string.IsNullOrEmpty(model.Date11))
{
total = total + double.Parse(model.Date11);
}
if (!string.IsNullOrEmpty(model.Date12))
{
total = total + double.Parse(model.Date12);
}
if (!string.IsNullOrEmpty(model.Date13))
{
total = total + double.Parse(model.Date13);
}
if (!string.IsNullOrEmpty(model.Date14))
{
total = total + double.Parse(model.Date14);
}
if (!string.IsNullOrEmpty(model.Date15))
{
total = total + double.Parse(model.Date15);
}
if (total > -1)
{
model.Total = total.ToString();
}
}
#endregion
//存储到缓存中
listExport.Clear();
listExport.AddRange(list);
}
}
return(list);
}
/// <summary>
/// 更改Excel工作簿内容
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para)
{
//获取工作簿
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
string title = string.Empty;
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
var holidayName = db.OT_Dic.Where(a => a.Id == para.HolidayId).Select(a => a.Name).ToList();
if (holidayName != null && holidayName.Count > 0)
{
title = string.Format("{0}年{1}假期进出京交通流量表（北京段）", ((DateTime)para.EndTime).Year, holidayName[0].ToString());
}
SetValue(sheet, 0, 0, title);
}
if (listExport != null)
{
for (int i = 0; i < listExport.Count; i++)
{
SetValue(sheet, i + 3, 0, listExport[i].DataDate);
SetValue(sheet, i + 3, 1, listExport[i].LineEnSum == null ? "" : listExport[i].LineEnSum.ToString());
SetValue(sheet, i + 3, 2, listExport[i].LineExSum == null ? "" : listExport[i].LineExSum.ToString());
SetValue(sheet, i + 3, 3, listExport[i].Total);
}
}
}
return(readworkbook);
}
/// <summary>
/// 获取和构造报表10的数据
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
private List <HdayExEnViewModel> GetData(Common.QueryParameters para)
{
RepairData(para);//补数据
List <HdayExEnViewModel> list = new List <HdayExEnViewModel>();
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//构造数据最小日期与查询最小日期之间的数据
if (StrWhere(db, para) != null && StrWhere(db, para).Count() > 0)
{
DateTime dt = StrWhere(db, para).Min(a => a.CalculTime);
int countMin = (dt - (DateTime)para.StartTime.Value).Days;
for (int i = 0; i < countMin; i++)
{
HdayExEnViewModel model1 = new HdayExEnViewModel();
model1.LineEnSum = null;
model1.LineExSum = null;
model1.Total = 0;
model1.DataDate = para.StartTime.Value.AddDays(i).ToString("M月d日");
list.Add(model1);
}
}
//查询数据
var lst = StrWhere(db, para).OrderBy(a => a.CalculTime).Select(a => new HdayExEnViewModel
{
DataDate = ((DateTime)a.CalculTime).Month + "月" + ((DateTime)a.CalculTime).Day + "日",
LineEnSum = (double)a.LineEnSum,
LineExSum = (double)a.LineExSum,
Total = (double)a.LineEnSum + (double)a.LineExSum
}).ToList();
list.AddRange(lst);
//按条件查询后有数据
if (list != null && list.Count > 0)
{
//计算数据最大日期与查询最大日期之间的差值
int countMax = ((DateTime)para.EndTime - StrWhere(db, para).Max(a => a.CalculTime)).Days;
//构造已有数据的最大日期到最大查询日期之间的数据
for (int i = 1; i <= countMax; i++)
{
HdayExEnViewModel model1 = new HdayExEnViewModel();
model1.LineEnSum = null;
model1.LineExSum = null;
model1.Total = 0;
model1.DataDate = StrWhere(db, para).Max(a => a.CalculTime).AddDays(i).ToString("M月d日");
list.Add(model1);
}
}
else//查询后无数据则按照查询时间构造数据
{
for (int i = 0; i < ((DateTime)para.EndTime - (DateTime)para.StartTime).Days + 1; i++)
{
HdayExEnViewModel model2 = new HdayExEnViewModel();
model2.LineEnSum = null;
model2.LineExSum = null;
model2.Total = 0;
model2.DataDate = ((DateTime)para.StartTime).AddDays(i).ToString("M月d日");
list.Add(model2);
}
}
//添加合计行
HdayExEnViewModel modelTotal = new HdayExEnViewModel();
modelTotal.LineEnSum = list.Sum(a => a.LineEnSum);
modelTotal.LineExSum = list.Sum(a => a.LineExSum);
modelTotal.Total = list.Sum(a => a.Total);
modelTotal.DataDate = "合计";
list.Add(modelTotal);
//存储到缓存中
listExport.Clear();
listExport.AddRange(list);
}
return(list);
}
/// <summary>
/// 校正
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public Common.CustomResult CalibrationData(Common.QueryParameters para)
{
CustomResult pReturnValue = new CustomResult();
double pFloating = 1 + para.FloatingRange * 0.01;
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//判断报表浮动百分比配置是否正确
OT_HDayConfig pds = HolidayConfig.GetInstance().GetById(para.ReportType);
if (pds == null)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileNoConfig;
return(pReturnValue);
}
if (Math.Abs(para.FloatingRange) > (double)pds.CheckFloat.Value)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.ErrorInfo + "范围应在负" + pds.CheckFloat.Value + "%-正" + pds.CheckFloat.Value + "%之间";
return(pReturnValue);
}
//判断时间范围是否相同
if ((para.LastYearEnd - para.LastYearStart) == (para.EndTime - para.StartTime))
{
//获取参考日期符合校正时间段的数据
List <RP_HDayAADT> pRefNaturalList = db.RP_HDayAADT.Where(a => a.CalcuTime >= para.LastYearStart & a.CalcuTime <= para.LastYearEnd).OrderBy(a => a.CalcuTime).ToList();
//如果参考日期数据为0 则返回失败
if (pRefNaturalList == null || pRefNaturalList.Count == 0)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileRefNoData;
return(pReturnValue);
}
//需要校正的数据
var pCheckNaturalList = db.RP_HDayAADT.Where(a => a.CalcuTime >= para.StartTime & a.CalcuTime <= para.EndTime).OrderBy(a => a.CalcuTime).ToList();
//如果需要校正的数据为空则返回失败
if (pCheckNaturalList == null || pCheckNaturalList.Count == 0)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileNoData;
return(pReturnValue);
}
using (TransactionScope tran = new TransactionScope())
{
for (int i = 0; i < pCheckNaturalList.Count; i++)
{
if (pRefNaturalList[i].JC != null)
{
pCheckNaturalList[i].JC = Math.Round(pRefNaturalList[i].JC.Value * pFloating);
}
if (pRefNaturalList[i].JZL != null)
{
pCheckNaturalList[i].JZL = Math.Round(pRefNaturalList[i].JZL.Value * pFloating);
}
if (pRefNaturalList[i].KG != null)
{
pCheckNaturalList[i].KG = Math.Round(pRefNaturalList[i].KG.Value * pFloating);
}
if (pRefNaturalList[i].TGB != null)
{
pCheckNaturalList[i].TGB = Math.Round(pRefNaturalList[i].TGB.Value * pFloating);
}
if (pRefNaturalList[i].TGX != null)
{
pCheckNaturalList[i].TGX = Math.Round(pRefNaturalList[i].TGX.Value * pFloating);
}
if (pRefNaturalList[i].TGXF != null)
{
pCheckNaturalList[i].TGXF = Math.Round(pRefNaturalList[i].TGXF.Value * pFloating);
}
if (pRefNaturalList[i].YC != null)
{
pCheckNaturalList[i].YC = Math.Round(pRefNaturalList[i].YC.Value * pFloating);
}
if (pRefNaturalList[i].YXBD != null)
{
pCheckNaturalList[i].YXBD = Math.Round(pRefNaturalList[i].YXBD.Value * pFloating);
}
if (pRefNaturalList[i].YXBX != null)
{
pCheckNaturalList[i].YXBX = Math.Round(pRefNaturalList[i].YXBX.Value * pFloating);
}
if (SessionManage.GetLoginUser() != null)
{
pCheckNaturalList[i].UpdBy = SessionManage.GetLoginUser().UserName;
}
pCheckNaturalList[i].UpdDate = DateTime.Now;
pCheckNaturalList[i].State = "1";
}
try
{
db.SaveChanges();
tran.Complete();
pReturnValue.ResultKey = (byte)EResult.Succeed;
pReturnValue.ResultValue = TipInfo.CalibrationSuccess;
}
catch (Exception e)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaile + e.Message.ToString();
SystemLog.GetInstance().Error(TipInfo.CalibrationFaile, e);
return(pReturnValue);
}
}
}
else
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationRangeFaile;
}
}
return(pReturnValue);
}
/// <summary>
/// 更改Excel工作簿内容
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para)
{
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
string title = string.Empty;
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
var holidayName = db.OT_Dic.Where(a => a.Id == para.HolidayId).Select(a => a.Name).ToList();
if (holidayName != null && holidayName.Count > 0)
{
title = string.Format("{0}年“{1}”黄金周京津塘高速公路交通量及客运情况统计表", ((DateTime)para.EndTime).Year, holidayName[0].ToString());
}
SetValue(sheet, 0, 0, title);
}
if (listExport != null && listExport.Count > 0)
{
for (int i = 0; i < listExport.Count; i++)
{
SetValue(sheet, i + 4, 0, listExport[i].CalcuTime);
SetValue(sheet, i + 4, 1, listExport[i].EnTra == null ? "" : listExport[i].EnTra.ToString());
SetValue(sheet, i + 4, 2, listExport[i].EnCar == null ? "" : listExport[i].EnCar.ToString());
SetValue(sheet, i + 4, 3, listExport[i].EnTrav == null ? "0.00" : listExport[i].EnTrav.Value.ToString("F2"));
SetValue(sheet, i + 4, 4, listExport[i].ExTra == null ? "" : listExport[i].ExTra.ToString());
SetValue(sheet, i + 4, 5, listExport[i].ExCar == null ? "" : listExport[i].ExCar.ToString());
SetValue(sheet, i + 4, 6, listExport[i].ExTrav == null ? "0.00" : listExport[i].ExTrav.Value.ToString("F2"));
}
SetValue(sheet, listExport.Count + 4, 0, string.Format("统计人：{0}", qModel.CrtBy));;
}
}
return(readworkbook);
}
/// <summary>
/// 修改excel工作簿
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para)
{
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
string title = string.Empty;
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
var holidayName = db.OT_Dic.Where(a => a.Id == para.HolidayId).Select(a => a.Name).ToList();
if (holidayName != null && holidayName.Count > 0)
{
title = string.Format("天津市高速公路支队{0}年{1}假期重点收费站流量表（出口+入口）", ((DateTime)para.EndTime).Year, holidayName[0].ToString());
}
SetValue(sheet, 0, 0, title);
}
if (listExport != null)
{
//计算查询天数
int count = (para.EndTime.Value - para.StartTime.Value).Days + 1;
if ((para.EndTime.Value - para.StartTime.Value).Days + 1 > 15)
{
count = 15;//最多可查询15天
}
for (int i = 0; i < listExport.Count; i++)
{
if (count > 0)
{
SetValue(sheet, i + 45, 3, listExport[i].Date1);
if (i == 0)
{
SetValue(sheet, 2, 3, para.StartTime.Value.AddDays(0).ToString("M月d日"));
}
}
if (count > 1)
{
SetValue(sheet, i + 45, 4, listExport[i].Date2);
if (i == 0)
{
SetValue(sheet, 2, 4, para.StartTime.Value.AddDays(1).ToString("M月d日"));
}
}
if (count > 2)
{
SetValue(sheet, i + 45, 5, listExport[i].Date3);
if (i == 0)
{
SetValue(sheet, 2, 5, para.StartTime.Value.AddDays(2).ToString("M月d日"));
}
}
if (count > 3)
{
SetValue(sheet, i + 45, 6, listExport[i].Date4);
if (i == 0)
{
SetValue(sheet, 2, 6, para.StartTime.Value.AddDays(3).ToString("M月d日"));
}
}
if (count > 4)
{
SetValue(sheet, i + 45, 7, listExport[i].Date5);
if (i == 0)
{
SetValue(sheet, 2, 7, para.StartTime.Value.AddDays(4).ToString("M月d日"));
}
}
if (count > 5)
{
SetValue(sheet, i + 45, 8, listExport[i].Date6);
if (i == 0)
{
SetValue(sheet, 2, 8, para.StartTime.Value.AddDays(5).ToString("M月d日"));
}
}
if (count > 6)
{
SetValue(sheet, i + 45, 9, listExport[i].Date7);
if (i == 0)
{
SetValue(sheet, 2, 9, para.StartTime.Value.AddDays(6).ToString("M月d日"));
}
}
if (count > 7)
{
SetValue(sheet, i + 45, 10, listExport[i].Date8);
if (i == 0)
{
SetValue(sheet, 2, 10, para.StartTime.Value.AddDays(7).ToString("M月d日"));
}
}
if (count > 8)
{
SetValue(sheet, i + 45, 11, listExport[i].Date9);
if (i == 0)
{
SetValue(sheet, 2, 11, para.StartTime.Value.AddDays(8).ToString("M月d日"));
}
}
if (count > 9)
{
SetValue(sheet, i + 45, 12, listExport[i].Date10);
if (i == 0)
{
SetValue(sheet, 2, 12, para.StartTime.Value.AddDays(9).ToString("M月d日"));
}
}
if (count > 10)
{
SetValue(sheet, i + 45, 13, listExport[i].Date11);
if (i == 0)
{
SetValue(sheet, 2, 13, para.StartTime.Value.AddDays(10).ToString("M月d日"));
}
}
if (count > 11)
{
SetValue(sheet, i + 45, 14, listExport[i].Date12);
if (i == 0)
{
SetValue(sheet, 2, 14, para.StartTime.Value.AddDays(11).ToString("M月d日"));
}
}
if (count > 12)
{
SetValue(sheet, i + 45, 15, listExport[i].Date13);
if (i == 0)
{
SetValue(sheet, 2, 15, para.StartTime.Value.AddDays(12).ToString("M月d日"));
}
}
if (count > 13)
{
SetValue(sheet, i + 45, 16, listExport[i].Date14);
if (i == 0)
{
SetValue(sheet, 2, 16, para.StartTime.Value.AddDays(13).ToString("M月d日"));
}
}
if (count > 14)
{
SetValue(sheet, i + 45, 17, listExport[i].Date15);
if (i == 0)
{
SetValue(sheet, 2, 17, para.StartTime.Value.AddDays(14).ToString("M月d日"));
}
}
SetValue(sheet, i + 45, 3 + count, listExport[i].Total);
}
}
}
return(readworkbook);
}
/// <summary>
/// 预测导出--如果需要将入出口数据进行区分，则分别放在两个list的集合中，若不需区分，则将数据放入list1中，list2为空即可
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <param name="list1"></param>
/// <param name="list2"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para, List <Common.ViewModels.IReportViewModel> list1, List <Common.ViewModels.IReportViewModel> list2)
{
//获取工作簿
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
if (list1 != null && list1.Count > 0)
{
ForecastEnExViewModel pInfo = list1.First() as ForecastEnExViewModel;
//高速入境
SetValue(sheet, 5, 1, pInfo.EnSmaCar.ToString()); //小型客车
SetValue(sheet, 5, 2, pInfo.EnOthCar.ToString()); //其他客车
SetValue(sheet, 5, 3, pInfo.EnTruk.ToString()); //高速入境货车数
SetValue(sheet, 5, 4, 0); //pInfo.EnGre.ToString());//绿色通道数
}
}
}
return(readworkbook);
}
/// <summary>
/// 更改Excel工作簿内容-导出按钮导出使用
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para)
{
//获取工作簿
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
//设置日期
SetReportDate(sheet, 1, 6, para.StartTime.Value, para.ReportType);
if (listExport == null || listExport.Count == 0)
{
GetData(para);
}
//获取导出日期数据
if (listExport != null)
{
for (int i = 0; i < listExport.Count; i++)
{
SetValue(sheet, i + 3, 0, listExport[i].CalculTime); //数据日期
SetValue(sheet, i + 3, 1, listExport[i].LineSum.ToString("F2")); //总交通量（万辆）
SetValue(sheet, i + 3, 2, (double.Parse(listExport[i].SumGrow)).ToString("F2")); //同比增幅（%）
SetValue(sheet, i + 3, 3, listExport[i].LineEnSum.ToString("F2")); //进京交通量（万辆）
SetValue(sheet, i + 3, 4, listExport[i].LineExSum.ToString("F2")); //出京交通量（万辆）
SetValue(sheet, i + 3, 5, listExport[i].ExEnPer.ToString("F2")); //出进京比
SetValue(sheet, i + 3, 6, listExport[i].SmaCarFeeNum.ToString("F2")); //小型客车交通量（万辆）
SetValue(sheet, i + 3, 7, (double.Parse(listExport[i].SmaCarCompGrow)).ToString("F2")); //同比增幅（%）
SetValue(sheet, i + 3, 8, listExport[i].SmaCarFee.ToString("F2")); //小型客车免收通行费（万元）
SetValue(sheet, i + 3, 9, listExport[i].ChagSumNum.ToString("F2")); //收费车辆（万辆）
}
}
}
return(readworkbook);
}
/// <summary>
/// 预测
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public Common.CustomResult ForecastData(Common.QueryParameters para)
{
CustomResult pReturnValue = new CustomResult();
//浮动范围
double pFloating = 1 + para.FloatingRange * 0.01;
// List<RP_EnEx> pForeList = new List<RP_EnEx>();
//预测数据集合
List <IReportViewModel> pInList = new List <IReportViewModel>();
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//判断报表浮动百分比配置是否存在
OT_HDayConfig pds = HolidayConfig.GetInstance().GetById(para.ReportType);
if (pds == null)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.ForecastFaileNoConfig;
return(pReturnValue);
}
//判断报表预测浮动百分比配置是否正确
if (Math.Abs(para.FloatingRange) > (double)pds.ForeFloat.Value)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.ErrorInfo + "范围应在负" + pds.ForeFloat.Value + "%-正" + pds.ForeFloat.Value + "%之间";
return(pReturnValue);
}
//获取参考日期符合的数据
List <RP_EnEx> pRefInfoList = db.RP_EnEx.Where(s => s.CalcuTime == para.StartTime && s.StaType == para.StationType).ToList();
//如果参考日期数据为0 则返回失败
if (pRefInfoList == null || pRefInfoList.Count == 0)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.ForecastFaileFaileRefNoData;
return(pReturnValue);
}
//预测数据
ForecastEnExViewModel pInfo = new ForecastEnExViewModel();
//预测数据集合
List <IReportViewModel> plist = new List <IReportViewModel>();
//参考数据
RP_EnEx pRefInfo = pRefInfoList.First();
if (pRefInfo.EnOthCar != null)
{
pInfo.EnOthCar = Math.Round(pRefInfo.EnOthCar.Value * pFloating);
}
if (pInfo.EnSmaCar != null)
{
pInfo.EnSmaCar = Math.Round(pRefInfo.EnSmaCar.Value * pFloating);
}
if (pInfo.EnTruk != null)
{
pInfo.EnTruk = Math.Round(pRefInfo.EnTruk.Value * pFloating);
}
try
{
plist.Add(pInfo);
string path = Export(para, plist);
pReturnValue.ResultKey = (byte)EResult.Succeed;
pReturnValue.ResultValue = path;
}
catch (Exception e)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.ForecastFail + e.Message.ToString();
SystemLog.GetInstance().Error(TipInfo.ForecastFail, e);
return(pReturnValue);
}
}
return(pReturnValue);
}
/// <summary>
/// 预测导出--如果需要将入出口数据进行区分，则分别放在两个list的集合中，若不需区分，则将数据放入list1中，list2为空即可
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <param name="list1"></param>
/// <param name="list2"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para, List <Common.ViewModels.IReportViewModel> list1, List <Common.ViewModels.IReportViewModel> list2)
{
//获取工作簿
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
//设置日期
SetReportDate(sheet, 0, 13, DateTime.Parse(DateTime.Now.ToShortDateString()), para.ReportType);
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
if (list1 != null && list1.Count > 0)
{
ForecastEnExViewModel pInfo = list1.First() as ForecastEnExViewModel;
//高速
SetValue(sheet, 5, 1, pInfo.EnSmaCar.ToString()); //小型客车
SetValue(sheet, 5, 2, pInfo.EnOthCar.ToString()); //其他客车
SetValue(sheet, 5, 3, pInfo.EnTruk.ToString()); //货车数
SetValue(sheet, 5, 4, pInfo.EnGre.ToString()); //绿色通道数
SetValue(sheet, 5, 5, pInfo.ExSmaCar.ToString()); //小型客车
SetValue(sheet, 5, 6, pInfo.ExOthCar.ToString()); //其他客车
SetValue(sheet, 5, 7, pInfo.ExTruk.ToString()); //货车数
SetValue(sheet, 5, 8, 0); //pInfo.EnGre.ToString());//绿色通道数
//普通
SetValue(sheet, 5, 9, 0); //小型客车
SetValue(sheet, 5, 10, 0); //其他客车
SetValue(sheet, 5, 11, 0); //高速入境货车数
SetValue(sheet, 5, 12, 0); //绿色通道数
SetValue(sheet, 5, 13, 0); //小型客车
SetValue(sheet, 5, 14, 0); //其他客车
SetValue(sheet, 5, 15, 0); //高速入境货车数
SetValue(sheet, 5, 16, 0); //绿色通道数
}
}
}
return(readworkbook);
}
/// <summary>
/// 校正
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public Common.CustomResult CalibrationData(Common.QueryParameters para)
{
CustomResult pReturnValue = new CustomResult();
double pFloating = 1 + para.FloatingRange * 0.01;
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//判断报表浮动百分比配置是否正确
OT_HDayConfig pds = HolidayConfig.GetInstance().GetById(para.ReportType);
if (pds == null)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileNoConfig;
return(pReturnValue);
}
if (Math.Abs(para.FloatingRange) > (double)pds.CheckFloat.Value)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.ErrorInfo + "范围应在负" + pds.CheckFloat.Value + "%-正" + pds.CheckFloat.Value + "%之间";
return(pReturnValue);
}
//判断校正数据日期是否合理
if (para.LastYearStart < para.StartTime && para.StartTime < DateTime.Now.AddDays(1))
{
//获取参考日期符合校正时间段的数据,因为只校正一天的数据，所以只查询开始数据的日期就可以
List <RP_EnEx> pRefNaturalList = db.RP_EnEx.Where(s => s.CalcuTime == para.LastYearStart && s.StaType == para.StationType).ToList();
//如果参考日期数据为0 则返回失败
if (pRefNaturalList == null || pRefNaturalList.Count == 0)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileRefNoData;
return(pReturnValue);
}
//需要校正的数据
var pCheckNaturalList = db.RP_EnEx.Where(s => s.CalcuTime == para.StartTime && s.StaType == para.StationType).ToList();
//如果需要校正的数据为空则返回失败
if (pCheckNaturalList == null || pCheckNaturalList.Count == 0)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileNoData;
return(pReturnValue);
}
using (TransactionScope tran = new TransactionScope())
{
//校正数据
RP_EnEx pCheckInfo = pCheckNaturalList.First();
//参考数据
RP_EnEx pRefInfo = pRefNaturalList.First();
if (pRefInfo.EnOthCar != null)
{
pCheckInfo.EnOthCar = Math.Round(pRefInfo.EnOthCar.Value * pFloating);
}
if (pCheckInfo.EnSmaCar != null)
{
pCheckInfo.EnSmaCar = Math.Round(pRefInfo.EnSmaCar.Value * pFloating);
}
if (pCheckInfo.EnTruk != null)
{
pCheckInfo.EnTruk = Math.Round(pRefInfo.EnTruk.Value * pFloating);
}
if (SessionManage.GetLoginUser() != null)
{
pCheckInfo.UpdBy = SessionManage.GetLoginUser().UserName;
}
pCheckInfo.UpdDate = DateTime.Now;
pCheckInfo.State = "1";
try
{
db.SaveChanges();
tran.Complete();
pReturnValue.ResultKey = (byte)EResult.Succeed;
pReturnValue.ResultValue = TipInfo.CalibrationSuccess;
}
catch (Exception e)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaile + e.Message.ToString();
SystemLog.GetInstance().Error(TipInfo.CalibrationFaile, e);
return(pReturnValue);
}
}
}
else
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileDate;
}
}
return(pReturnValue);
}
/// <summary>
/// 获取和构造报表7的数据
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
private List <AADTAndTransCalcuViewModel> GetData(Common.QueryParameters para)
{
//补数据
RepairData(para);
List <AADTAndTransCalcuViewModel> list = new List <AADTAndTransCalcuViewModel>();
qModel.CrtBy = "无";//获取统计人
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//构造数据最小日期与查询最小日期之间的数据
if (StrWhere(db, para) != null && StrWhere(db, para).Count() > 0)
{
DateTime dt = StrWhere(db, para).Min(a => a.CalcuTime);
int countMin = (dt - (DateTime)para.StartTime.Value).Days;
for (int i = 0; i < countMin; i++)
{
AADTAndTransCalcuViewModel model1 = new AADTAndTransCalcuViewModel();
model1.CalcuTime = para.StartTime.Value.AddDays(i).ToString("M月d日");
model1.EnTrav = 0.00;
model1.ExTrav = 0.00;
list.Add(model1);
}
}
//查询数据
var lst = StrWhere(db, para).OrderBy(a => a.CalcuTime).Select(a => new AADTAndTransCalcuViewModel
{
EnTrav = a.EnTrav,
EnTra = a.EnTra,
EnCar = a.EnCar,
CalcuTimeUpdate = a.CalcuTime.ToString(),
ExCar = a.ExCar,
ExTra = a.ExTra,
ExTrav = a.ExTrav,
CalcuTime = ((DateTime)a.CalcuTime).Month + "月" + ((DateTime)a.CalcuTime).Day + "日",
}).ToList();
list.AddRange(lst);
//按条件查询后有数据
if (list != null && list.Count > 0)
{
string name = StrWhere(db, para).First(a => true).CrtBy;
qModel.CrtBy = (string.IsNullOrEmpty(name) ? "无" : name);//获取统计人
//计算数据最大日期与查询最大日期之间的差值
int countMax = ((DateTime)para.EndTime - StrWhere(db, para).Max(a => a.CalcuTime)).Days;
//构造已有数据的最大日期到最大查询日期之间的数据
for (int i = 1; i <= countMax; i++)
{
AADTAndTransCalcuViewModel model1 = new AADTAndTransCalcuViewModel();
model1.CalcuTime = StrWhere(db, para).Max(a => a.CalcuTime).AddDays(i).ToString("M月d日");
list.Add(model1);
}
}
else//查询后无数据则按照查询时间构造数据
{
for (int i = 0; i < ((DateTime)para.EndTime - (DateTime)para.StartTime).Days + 1; i++)
{
AADTAndTransCalcuViewModel model2 = new AADTAndTransCalcuViewModel();
model2.CalcuTime = para.StartTime.Value.AddDays(i).ToString("M月d日");
model2.EnTrav = 0.00;
model2.ExTrav = 0.00;
list.Add(model2);
}
}
//添加合计行
AADTAndTransCalcuViewModel modelTotal = new AADTAndTransCalcuViewModel();
modelTotal.EnCar = list.Sum(a => a.EnCar);
modelTotal.EnTra = list.Sum(a => a.EnTra);
modelTotal.EnTrav = Math.Round((double)list.Sum(a => a.EnTrav), 2);
modelTotal.ExCar = list.Sum(a => a.ExCar);
modelTotal.ExTra = list.Sum(a => a.ExTra);
modelTotal.ExTrav = Math.Round((double)list.Sum(a => a.ExTrav), 2);
modelTotal.CalcuTime = "合计";
list.Add(modelTotal);
//存储到缓存中
listExport.Clear();
listExport.AddRange(list);
}
return(list);
}
/// <summary>
/// 更改Excel工作簿内容
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para)
{
//获取工作簿
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//获取导出日期数据
List <RP_EnEx> pAADTList = db.RP_EnEx.Where(a => a.CalcuTime == para.StartTime && a.StaType == para.StationType).ToList();
if (pAADTList != null && pAADTList.Count > 0)
{
RP_EnEx pInfo = pAADTList.First();
//高速
SetValue(sheet, 5, 1, pInfo.EnSmaCar.ToString()); //小型客车
SetValue(sheet, 5, 2, pInfo.EnOthCar.ToString()); //其他客车
SetValue(sheet, 5, 3, pInfo.EnTruk.ToString()); //高速入境货车数
SetValue(sheet, 5, 4, 0); //pInfo.EnGre.ToString());//绿色通道数
}
}
}
return(readworkbook);
}
