/// <summary>
/// 正常导出
/// </summary>
/// <param name="path"></param>
/// <param name="para"></param>
/// <param name="updatesheet">数据表类对象</param>
/// <returns></returns>
public string ExportExcel(string path, QueryParameters para, IGenerateSheet updatesheet)
{
string[] num = path.Split('\\');
string filename = num[num.Length - 1];
string savepath = string.Empty;
try
{
if (System.IO.File.Exists(path))
{
using (FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read))
{
IWorkbook readworkbook = new XSSFWorkbook(stream);
stream.Close();
readworkbook = updatesheet.GenerateSheet(readworkbook, para);
savepath = SaveFile(readworkbook, filename, para);
}
}
else
{
SystemLog.GetInstance().Info("导出错误：模板信息不存在！");
}
}
catch (Exception e)
{
SystemLog.GetInstance().Error(e);
}
return savepath;
}
/// <summary>
/// 根据查询条件获取数据
/// </summary>
/// <param name="para">查询条件类，站类型也需要</param>
/// <returns></returns>
public IReportViewModel GetListByPra(QueryParameters para)
{
//查询返回实体
QueryHourAADTViewModel pReturn = new QueryHourAADTViewModel();
try
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//补充空数据
InsertNull(para.StartTime.Value);
List<int> pStationNames = StationConfiguration.GetBJStaion();
List<RP_HourAADT> pHourAADTList = db.RP_HourAADT.Where(s => s.CalcuTime == para.StartTime).ToList();
RP_HourAADT pHourAADTInfo = new RP_HourAADT();
//创建或更新合计
CreateOrUpdateSum(para);
//报表数据
pReturn.ReportData = GetViewModelInfo(para);
//判断当前统计站类型，数据是否完整
if (GetNoDataList(para).Count() > 0)
pReturn.IsFull = 0;//不完整
else
pReturn.IsFull = 1;//完整
}
}
catch (Exception ex)
{
SystemLog.GetInstance().Info(ex.Message);
}
return pReturn;
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public string ExportReport(QueryParameters para)
{
string path = string.Empty;
string reportpath = string.Empty;
if (para.ReportType == 9 && para.StationType == 1)//表9
{
path = string.Format(@"{0}Reporttemplate\编号09--收费公路运行情况统计表.xlsx", AppDomain.CurrentDomain.BaseDirectory);
reportpath = ExportHelper.GetInstance().ExportExcel(path, para, this);
}
return reportpath;
}
/// <summary>
/// 创建或修改15，16合计
/// </summary>
/// <param name="para"></param>
/// <param name="stationtype">收费站类型</param>
public void CreateOrUpdateSum(QueryParameters para, int stationtype)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
#region 合计
//查询日期当天除合计外的全部数据
IEnumerable<RP_NatSta> naturalall = db.RP_NatSta.Where(s => s.CalcuTime == para.StartTime && s.StaType == stationtype && s.HourPer != 24);
IEnumerable<RP_NatSta> naturallistsum = db.RP_NatSta.Where(s => s.CalcuTime == para.StartTime && s.HourPer == 24 && s.StaType == stationtype);
RP_NatSta natural = new RP_NatSta();
//如果有数据则进行合计
if (naturalall.Count() > 0)
{
if (naturallistsum.Count() > 0)
{
natural = naturallistsum.First();
}
natural.Sum = naturalall.Sum(s => s.Sum);
natural.ExNum = naturalall.Sum(s => s.ExNum);
natural.EnNum = naturalall.Sum(s => s.EnNum);
natural.State = "1";
natural.UpdDate = DateTime.Now;
using (TransactionScope transaction = new TransactionScope())
{
if (naturallistsum.Count() <= 0)
{
natural.RunStae = "正常";
natural.HourPer = 24;//24代表合计
natural.Id = Guid.NewGuid();
natural.StaType = stationtype;
natural.CalcuTime = (DateTime)para.StartTime;
natural.CrtDate = DateTime.Now;
db.RP_NatSta.Add(natural);
}
db.SaveChanges();
//提交事务
transaction.Complete();
}
#endregion
}
}
#endregion
#region 10 Static Methods
#endregion
#region 11 Private Methods
#endregion
#region 12 Protected Methods
#endregion
}
/// <summary>
/// 获取无收费站数据列表
/// </summary>
/// <param name="para">统计站类型，数据日期</param>
/// <returns></returns>
public List<ReportRelatedViewModels> GetNoDataList(QueryParameters para)
{
List<ReportRelatedViewModels> pList = new List<ReportRelatedViewModels>();
try
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//获取某类报表所涉及的收费站名称
string[] pStationNames = null;
if (para.StationType == (int)StationConfiguration.StationID.DYF || para.StationType == (int)StationConfiguration.StationID.SCD)//大羊坊或泗村店收费站
{
pStationNames = db.OT_Station.Where(s => s.Num == para.StationType.Value.ToString()).Select(s => s.Name).ToArray();
}
else if (para.StationType == (int)StationConfiguration.StationType.BeiJingDuan || para.StationType == (int)StationConfiguration.StationType.HeBei || para.StationType == (int)StationConfiguration.StationType.TianJinDuan)//多个收费站
{
pStationNames = db.OT_Station.Where(s => s.District == para.StationType.Value).Select(s => s.Name).ToArray();
}
List<OT_ErrorStation> pNoaccept = new List<OT_ErrorStation>();
if (para.StartTime.HasValue && para.EndTime.HasValue)
{
pNoaccept = db.OT_ErrorStation.Where(i =>
pStationNames.Contains(i.StaName)).ToList().Where(s => s.CalcuTime >= para.StartTime && s.CalcuTime <= para.EndTime).ToList();
}
else
{
pNoaccept = db.OT_ErrorStation.Where(i =>
pStationNames.Contains(i.StaName)).ToList().Where(s => s.CalcuTime == para.StartTime).ToList();
}
var nolist = pNoaccept.GroupBy(s => s.StaID);
foreach (var s in nolist)
{
string pDicNmae = db.OT_Station.Single(m => m.Num == s.Key.ToString() && m.IsDelete == 0).Name;
List<byte?> timePeriods = pNoaccept.Where(n => n.StaID == s.Key).Select(n => n.HourPer).OrderBy(n => n.Value).ToList();
ReportRelatedViewModels pData = new ReportRelatedViewModels();
string value = string.Empty;
for (int i = 0; i < timePeriods.Count; i++)
{
value += timePeriods[i].Value.ToString() + "时、";
}
pData.StationName = pDicNmae;
pData.Time = value.Remove(value.Length - 1);
pList.Add(pData);
}
return pList;
}
}
catch (Exception e)
{
SystemLog.GetInstance().Error(e.ToString());
return null;
}
}
/// <summary>
/// 查询
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public IReportViewModel GetListByPra(QueryParameters para)
{
QueryHDayAADTViewModel pReturnData = new QueryHDayAADTViewModel();
try
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
InsertNull(para.StartTime.Value);
//获取报表18查询集合
List<HDayAADTViewModel> pHdayAADT = db.RP_HDayAADTSta.Where(s => s.CalcuTime == para.StartTime).Select(s => new HDayAADTViewModel()
{
LineType = s.LineType,
ExNat = s.ExNat,
EnNat = s.EnNat,
NatSum = s.ExNat + s.EnNat,
ExEqu = s.ExEqu,
EnEqu = s.EnEqu,
EquSum = s.ExEqu + s.EnEqu,
CrowDeg = s.CrowDeg,
SmaEx = s.SmaEx,
SmaEn = s.SmaEn,
SmaSum = s.SmaEx + s.SmaEn,
MedEn = s.MedEn,
MedEx = s.MedEx,
MedSum = s.MedEx + s.MedEn,
LarEx = s.LarEx,
LarEn = s.LarEn,
LarSum = s.LarEn + s.LarEx,
HeaEn = s.HeaEn,
HeaEx = s.HeaEx,
HeaSum = s.HeaEx + s.HeaEn,
SupEn = s.SupEn,
SupEx = s.SupEx,
SupSum = s.SupEn + s.SupEx,
EnExTrukNum = s.EnExTrukNum,
CarTrukPer = s.CarTrukPer,
SupTruNum = s.SupTruNum,
SupTruPer = s.SupTruPer
}).ToList();
if (pHdayAADT.Count > 0)
{
pReturnData.IsEdit = 1;
pReturnData.ReportData = pHdayAADT;
}
else
{
pReturnData.IsEdit = 0;
for (int i = 1; i < 7; i++)//添加6条空数据
{
HDayAADTViewModel pHdayInfo = new HDayAADTViewModel();
pHdayInfo.LineType = i;
pHdayAADT.Add(pHdayInfo);
}
}
//添加第一条(G2)
HDayAADTViewModel pHdayInfofirst = pHdayAADT.Where(s => s.LineType == 3).SingleOrDefault();
pHdayAADT.Add(GetFirst(pHdayInfofirst));
//升序排序
pReturnData.ReportData = pHdayAADT.OrderBy(s => s.Sorting).ToList();
//判断当前统计站类型，数据是否完整
if (GetNoDataList(para).Count() > 0)
pReturnData.IsFull = 0;//不完整
else
pReturnData.IsFull = 1;//完整
return pReturnData;
}
}
catch (Exception ex)
{
SystemLog.GetInstance().Info(ex.Message);
return pReturnData;
}
}
/// <summary>
/// 预测导出使用
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <param name="list1"></param>
/// <param name="list2"></param>
/// <returns></returns>
public IWorkbook GenerateSheet(IWorkbook readworkbook, QueryParameters para, List<IReportViewModel> list1, List<IReportViewModel> list2)
{
//获取工作簿
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
//设置日期
SetReportDate(sheet, 1, 6, DateTime.Parse(DateTime.Now.ToShortDateString()), para.ReportType);
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
if (list1 != null && list1.Count > 0)
{
RoadRunSitViewModel pInfo = list1.First() as RoadRunSitViewModel;
for (int i = 0; i < list1.Count; i++)
{
RoadRunSitViewModel model = (RoadRunSitViewModel)list1[i];
SetValue(sheet, i + 3, 0, model.CalculTime);//数据日期
SetValue(sheet, i + 3, 1, model.LineSum.ToString("F2"));//总交通量（万辆）
SetValue(sheet, i + 3, 2, (double.Parse(model.SumGrow)).ToString("F2"));//同比增幅（%）
SetValue(sheet, i + 3, 3, model.LineEnSum.ToString("F2"));//进京交通量（万辆）
SetValue(sheet, i + 3, 4, model.LineExSum.ToString("F2"));//出京交通量（万辆）
SetValue(sheet, i + 3, 5, model.ExEnPer.ToString("F2")); //出进京比
SetValue(sheet, i + 3, 6, model.SmaCarFeeNum.ToString("F2"));//小型客车交通量（万辆）
SetValue(sheet, i + 3, 7, (double.Parse(model.SmaCarCompGrow)).ToString("F2"));//同比增幅（%）
SetValue(sheet, i + 3, 8, model.SmaCarFee.ToString("F2")); //小型客车免收通行费（万元）
SetValue(sheet, i + 3, 9, model.ChagSumNum.ToString("F2")); //收费车辆（万辆）
}
}
}
}
return readworkbook;
}
/// <summary>
/// 无收费站数据列表
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
public JsonResult NoDataList(QueryParameters args)
{
var result = ReportFactory.Instance.NoDataList(args);
return Json(result, "text/html", JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public IReportViewModel GetListByPra(QueryParameters para)
{
QueryHDayExViewModel pReturnData = new QueryHDayExViewModel();
List<HDayExViewModel> pTList = new List<HDayExViewModel>();
int pDataCount = 0;
if (para.EndTime.Value >= para.StartTime.Value)
{
pDataCount = (para.EndTime.Value - para.StartTime.Value).Days + 1;
}
//添加标头
pReturnData.TitleList = new List<string>();
pReturnData.TitleList.Add("序号");
pReturnData.TitleList.Add("高速名称");
//日期天数
pReturnData.CountDay = pDataCount;
for (int i = 0; i < 15; i++)
{
//日期
pReturnData.TitleList.Add(para.StartTime.Value.AddDays(i).ToString("M月d日"));
}
pReturnData.TitleList.Add("合计");
pReturnData.TitleList.Add("去年同期总流量");
pReturnData.TitleList.Add("同比增幅");
try
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
HDayExViewModel pHdayExInfo = new HDayExViewModel();
pHdayExInfo.RoadName = SystemConst.RoadName;
for (int n = 0; n < pDataCount; n++)
{
DateTime? pDt = para.StartTime.Value.AddDays(n);
//如果所选日期数据不存在，则进行添加
if (db.RP_HDayAADT.Where(s => DbFunctions.TruncateTime(s.CalcuTime) == pDt).ToList().Count <= 0)
{
if (pDt <= DateTime.Now)
{
InsertNull(pDt.Value);
}
}
List<RP_HDayAADT> pHourAADTList = db.RP_HDayAADT.Where(s => DbFunctions.TruncateTime(s.CalcuTime) == pDt).ToList();
if (pHourAADTList.Count == 1)
{
//通过反射取字段名
Type myType = pHdayExInfo.GetType();
PropertyInfo pinfo = myType.GetProperty("Tra" + (n + 1));
//给字段赋值
double pTemp = pHourAADTList.SingleOrDefault().Out.Value;
pinfo.SetValue(pHdayExInfo, pTemp);
}
}
pHdayExInfo.Num = 15;
pHdayExInfo.Sum = db.RP_HDayAADT.Where(s => DbFunctions.TruncateTime(s.CalcuTime) >= para.StartTime.Value && DbFunctions.TruncateTime(s.CalcuTime) <= para.EndTime.Value).Sum(s => s.Out);
pHdayExInfo.LastSum = db.RP_HDayAADT.Where(s => DbFunctions.TruncateTime(s.CalcuTime) >= para.LastYearStart.Value && DbFunctions.TruncateTime(s.CalcuTime) <= para.LastYearEnd.Value).Sum(s => s.Out);
if (pHdayExInfo.Sum.HasValue && pHdayExInfo.LastSum.HasValue && pHdayExInfo.LastSum != 0 && pHdayExInfo.LastSum != 0.0)
pHdayExInfo.Growth = Math.Round((pHdayExInfo.Sum.Value - pHdayExInfo.LastSum.Value) / pHdayExInfo.LastSum.Value, 2);
pTList.Add(pHdayExInfo);
//添加合计
pTList.Add(GetSum(pHdayExInfo));
pReturnData.ReportData = pTList.OrderBy(s => s.Num).ToList();
//报表数据
//判断当前统计站类型，数据是否完整
if (GetNoDataList(para).Count() > 0)
pReturnData.IsFull = 0;//不完整
else
pReturnData.IsFull = 1;//完整
}
}
catch (Exception ex)
{
SystemLog.GetInstance().Info(ex.Message);
}
return pReturnData;
}
/// <summary>
/// 补数据
/// </summary>
/// <param name="para"></param>
private void RepairData(QueryParameters para)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
using (TransactionScope transaction = new TransactionScope())
{
int day = (para.EndTime.Value - para.StartTime.Value).Days + 1;
for (int i = 0; i < day; i++)
{
DateTime dtime = para.StartTime.Value.AddDays(i);
//时间小于当前时间
if (DateTime.Now > dtime)
{
bool flag = StrWhere(db, para).Select(a => a.CalcuTime).ToList().Contains(dtime);
if (!flag)//补数据
{
RP_HDayAADT hday = new RP_HDayAADT();
hday.CalcuTime = dtime;
hday.CompGrow = 0;
if (SessionManage.GetLoginUser() != null)
{
hday.CrtBy = SessionManage.GetLoginUser().UserName;
}
hday.CrtDate = DateTime.Now;
hday.Id = Guid.NewGuid();
hday.JC = 0;
hday.JZL = 0;
hday.KG = 0;
hday.Out = 0;
hday.SameSum = 0;
hday.State = "0";
hday.Sum = 0;
hday.TGB = 0;
hday.TGX = 0;
hday.TGXF = 0;
hday.YC = 0;
hday.YXBD = 0;
hday.YXBX = 0;
db.RP_HDayAADT.Add(hday);
}
else//将数据中有空值的改成0
{
var hday = db.RP_HDayAADT.Where(a => a.CalcuTime == dtime).ToList()[0];
if (hday.JC == null)
hday.JC = 0;
if (hday.JZL == null)
hday.JZL = 0;
if (hday.KG == null)
hday.KG = 0;
if (hday.Out == null)
hday.Out = 0;
if (hday.SameSum == null)
hday.SameSum = 0;
if (hday.Sum == null)
hday.Sum = 0;
if (hday.TGB == null)
hday.TGB = 0;
if (hday.TGX == null)
hday.TGX = 0;
if (hday.TGXF == null)
hday.TGXF = 0;
if (hday.YC == null)
hday.YC = 0;
if (hday.YXBD == null)
hday.YXBD = 0;
if (hday.YXBX == null)
hday.YXBX = 0;
}
}
}
try
{
db.SaveChanges();
transaction.Complete();
}
catch (Exception ex)
{
SystemLog.GetInstance().Error(TipInfo.AddFaile, ex);
}
}
}
}
/// <summary>
/// 导出报表
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
public JsonResult ExportReport(QueryParameters args)
{
string result = ReportFactory.Instance.Export(args);
return Json(new { path = result }, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 获取和构造数据
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
private List<RoadRunSitViewModel> GetData(QueryParameters para)
{
RepairData(para);//补数据
List<RoadRunSitViewModel> list;
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
list = StrWhere(db, para).OrderBy(a => a.CalculTime).Select(a => new RoadRunSitViewModel
{
CalculTime = ((DateTime)a.CalculTime).Month + "月" + ((DateTime)a.CalculTime).Day + "日",
ChagSumNum = Math.Round((double)a.ChagSumNum / 10000, 2),
ExEnPer = Math.Round((double)a.ExEnPer, 2),
LineEnSum = Math.Round((double)a.LineEnSum / 10000, 2),
LineExSum = Math.Round((double)a.LineExSum / 10000, 2),
LineSum = Math.Round((double)a.LineSum / 10000, 2),
SmaCarCompGrow = a.SmaCarCompGrow.Value == null ? "0.00" : Math.Round(a.SmaCarCompGrow.Value, 2).ToString(),
SmaCarFee = Math.Round((decimal)a.SmaCarFee, 2),
SmaCarFeeNum = Math.Round((double)a.SmaCarFeeNum / 10000, 2),
SumGrow = a.SumGrow.Value == null ? "0.00" : Math.Round(a.SumGrow.Value, 2).ToString(),
}).ToList();
//查询后无数据则按照查询时间构造数据
if (list == null || list.Count == 0)
{
RoadRunSitViewModel model = new RoadRunSitViewModel();
model.CalculTime = ((DateTime)para.StartTime).ToString("M月d日");
model.ChagSumNum = 0.00;
model.ExEnPer = 0.00;
model.LineEnSum = 0.00;
model.LineExSum = 0.00;
model.LineSum = 0.00;
model.SmaCarCompGrow = "0.00";
model.SmaCarFee = (decimal)0.00;
model.SmaCarFeeNum = 0.00;
model.SumGrow = "0.00";
list.Add(model);
}
listExport.Clear();
listExport.AddRange(list);
}
return list;
}
/// <summary>
/// 修改工作簿内容
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <returns></returns>
public IWorkbook GenerateSheet(IWorkbook readworkbook, QueryParameters para)
{
try
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//日期合计
//获取工作簿
ISheet sheet = readworkbook.GetSheetAt(0);
int pDataCount = 0;
if (para.EndTime.Value >= para.StartTime.Value)
{
pDataCount = (para.EndTime.Value - para.StartTime.Value).Days + 1;
}
//设置标题
var holidayName = db.OT_Dic.Where(a => a.Id == para.HolidayId).Select(a => a.Name).ToList();
string title = string.Format("天津市高速公路支队{0}年{1}假期流量表（出口）", ((DateTime)para.EndTime).Year, holidayName[0].ToString());
SetValue(sheet, 0, 0, title);
//如果多于4天，则手动添加单元格
if (pDataCount > 4)
{
int pTemp = pDataCount - 4;
//遍历创建所有单元格
//列
for (int i = 0; i < pTemp; i++)
{
//行--该模版有隐藏行
for (int n = 0; n < 19; n++)
{
IRow row = sheet.GetRow(n);
ICell cell = row.CreateCell(9 + i);//创建列
cell.CellStyle = GetCellStyle(readworkbook, 1);
}
}
}
else if (pDataCount < 4)//如果小于4天，则删除列
{
int pTemp = 4 - pDataCount;
//遍历设置所有单元格没有边框
//列
for (int i = 0; i < pTemp; i++)
{
for (int n = 0; n < 19; n++)
{
ICell cell = sheet.GetRow(n).GetCell(i + 5 + pDataCount);
cell.CellStyle = GetCellStyle(readworkbook, 0);
}
}
}
double? pSum = db.RP_HDayAADT.Where(s => DbFunctions.TruncateTime(s.CalcuTime) >= para.StartTime.Value && DbFunctions.TruncateTime(s.CalcuTime) <= para.EndTime.Value).Sum(s => s.Out);
double? pLastSum = db.RP_HDayAADT.Where(s => DbFunctions.TruncateTime(s.CalcuTime) >= para.LastYearStart.Value && DbFunctions.TruncateTime(s.CalcuTime) <= para.LastYearEnd.Value).Sum(s => s.Out);
double? Growth = null;
if (pLastSum.HasValue && pLastSum != 0 && pLastSum != 0.0 && pSum.HasValue)
{
Growth = Math.Round((pSum.Value - pLastSum.Value) / pLastSum.Value, 2);
}
if (pSum.HasValue)
{
SetValue(sheet, 17, 2 + pDataCount, pSum.ToString());
SetValue(sheet, 18, 2 + pDataCount, pSum.ToString());
}
if (pLastSum.HasValue)
{
SetValue(sheet, 17, 3 + pDataCount, pLastSum.ToString());
SetValue(sheet, 18, 3 + pDataCount, pLastSum.ToString());
}
if (Growth.HasValue)
{
SetValue(sheet, 17, 4 + pDataCount, Growth.ToString());
SetValue(sheet, 18, 4 + pDataCount, Growth.ToString());
}
//合并单元格
//标题行
SetCellRangeAddress(sheet, 0, 0, 0, 4 + pDataCount);
//日期行
SetCellRangeAddress(sheet, 1, 1, 2, pDataCount + 1);
//合并最后三列
SetCellRangeAddress(sheet, 1, 2, 2 + pDataCount, 2 + pDataCount);
SetCellRangeAddress(sheet, 1, 2, 3 + pDataCount, 3 + pDataCount);
SetCellRangeAddress(sheet, 1, 2, 4 + pDataCount, 4 + pDataCount);
//
SetValue(sheet, 1, 2 + pDataCount, "合计");
SetValue(sheet, 1, 3 + pDataCount, "去年同期总流量");
SetValue(sheet, 1, 4 + pDataCount, "同比增幅");
for (int i = 0; i < pDataCount; i++)
{
DateTime? pDt = para.StartTime.Value.AddDays(i);
List<RP_HDayAADT> pHourAADTList = db.RP_HDayAADT.Where(s => DbFunctions.TruncateTime(s.CalcuTime) == pDt).ToList();
if (pHourAADTList.Count == 1)
{
SetValue(sheet, 17, 2 + i, pHourAADTList.Sum(s => s.Out).ToString());
SetValue(sheet, 18, 2 + i, pHourAADTList.Sum(s => s.Out).ToString());
}
//添加表头
SetValue(sheet, 2, 2 + i, para.StartTime.Value.AddDays(i).ToString("M月d日"));
}
}
}
catch (Exception ex)
{
SystemLog.GetInstance().Info(ex.Message);
}
return readworkbook;
}
/// <summary>
/// 补数据
/// </summary>
/// <param name="para"></param>
private void RepairData(QueryParameters para)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
using (TransactionScope transaction = new TransactionScope())
{
DateTime dtime = para.StartTime.Value;
//时间小于当前时间
if (DateTime.Now > dtime)
{
bool flag = StrWhere(db, para).Select(a => a.CalculTime).ToList().Contains(dtime);
if (!flag)//补数据
{
RP_AADTSta model = new RP_AADTSta();
model.CalculTime = dtime;
if (SessionManage.GetLoginUser() != null)
{
model.CrtBy = SessionManage.GetLoginUser().UserName;
}
model.CrtDate = DateTime.Now;
model.Id = Guid.NewGuid();
model.ChagSumNum = 0.00;
model.ExEnPer = 0.00;
model.LineEnSum = 0.00;
model.LineExSum = 0.00;
model.LineSum = 0.00;
model.SmaCarCompGrow = 0.00;
model.SmaCarFee = (decimal)0.00;
model.SmaCarFeeNum = 0.00;
model.SumGrow = 0.00;
model.State = "0";
db.RP_AADTSta.Add(model);
}
else//将数据中有空值的改成0
{
var model = db.RP_AADTSta.Where(a => a.CalculTime == dtime).ToList()[0];
if (model.ChagSumNum == null)
model.ChagSumNum = 0.00;
if (model.ExEnPer == null)
model.ExEnPer = 0.00;
if (model.LineEnSum == null)
model.LineEnSum = 0.00;
if (model.LineExSum == null)
model.LineExSum = 0.00;
if (model.LineSum == null)
model.LineSum = 0.00;
if (model.SmaCarCompGrow == null)
model.SmaCarCompGrow = 0.00;
if (model.SmaCarFee == null)
model.SmaCarFee = (decimal)0.00;
if (model.SmaCarFeeNum == null)
model.SmaCarFeeNum = 0.00;
if (model.SumGrow == null)
model.SumGrow = 0.00;
}
}
try
{
db.SaveChanges();
transaction.Complete();
}
catch (Exception ex)
{
SystemLog.GetInstance().Error(TipInfo.AddFaile, ex);
}
}
}
}
/// <summary>
/// 修改表内容
/// </summary>
/// <param name="readworkbook"></param>
/// <returns></returns>
public IWorkbook GenerateSheet(IWorkbook readworkbook, QueryParameters para)
{
ISheet sheet = readworkbook.GetSheetAt(1);
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//包括合计
List<RP_HourAADT> pHourAADTList = db.RP_HourAADT.Where(s => s.CalcuTime == para.StartTime.Value).ToList();
if (para.ReportType == 13)
{
SetValue(sheet, 0, 0, string.Format("0-24时各高速公路各收费站分方向出入口小时交通量（{0}月{1}日）)", para.StartTime.Value.Month, para.StartTime.Value.Day));
SetReportDate(sheet, 1, 13, para.StartTime.Value, 13);
if (pHourAADTList.Count > 0)
{
foreach (RP_HourAADT item in pHourAADTList)
{
SetValue(sheet, 3, 3 + item.HourPer, item.Dyf_ExIn.Value);
SetValue(sheet, 6, 3 + item.HourPer, item.Dyf_EnOut.Value);
SetValue(sheet, 9, 3 + item.HourPer, item.Mjqd_EnIn.Value);
SetValue(sheet, 10, 3 + item.HourPer, item.Mjqd_EnOut.Value);
SetValue(sheet, 11, 3 + item.HourPer, item.Mjqx_ExIn.Value);
SetValue(sheet, 14, 3 + item.HourPer, item.Mjqx_EnOut.Value);
SetValue(sheet, 15, 3 + item.HourPer, item.Cy_ExIn.Value);
SetValue(sheet, 18, 3 + item.HourPer, item.Cy_EnOut.Value);
}
}
}
else if (para.ReportType == 14)
{
SetReportDate(sheet, 1, 11, para.StartTime.Value, 14);
if (pHourAADTList.Count > 0)
{
foreach (RP_HourAADT item in pHourAADTList)
{
SetValue(sheet, 3, 3 + item.HourPer, item.Dyf_EnOut.Value);
SetValue(sheet, 4, 3 + item.HourPer, item.Dyf_EnOut.Value);
SetValue(sheet, 6, 3 + item.HourPer, item.Mjqd_EnOut.Value);
SetValue(sheet, 7, 3 + item.HourPer, item.Mjqd_EnOut.Value);
SetValue(sheet, 9, 3 + item.HourPer, item.Mjqx_EnOut.Value);
SetValue(sheet, 10, 3 + item.HourPer, item.Mjqx_EnOut.Value);
SetValue(sheet, 12, 3 + item.HourPer, item.Cy_EnOut.Value);
SetValue(sheet, 13, 3 + item.HourPer, item.Cy_EnOut.Value);
}
}
}
}
return readworkbook;
}
/// <summary>
/// 导出报表
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public string ExportReport(QueryParameters para)
{
string path = string.Empty;
string reportpath = string.Empty;
if (para.ReportType == 13)//表13
{
path = AppDomain.CurrentDomain.BaseDirectory + @"Reporttemplate\编号13--0-24时各高速公路各收费站分方向出入口小时交通量.xlsx";
reportpath = ExportHelper.GetInstance().ExportExcel(path, para, this);
}
if (para.ReportType == 14)// 表14
{
path = AppDomain.CurrentDomain.BaseDirectory + @"Reporttemplate\编号14--各高速公路各收费站出口小时交通量.xlsx";
reportpath = ExportHelper.GetInstance().ExportExcel(path, para, this);
}
return reportpath;
}
/// <summary>
/// 补数据
/// </summary>
/// <param name="para"></param>
private void RepairData(QueryParameters para)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
using (TransactionScope transaction = new TransactionScope())
{
DateTime dtQuery = new DateTime();
//判断传入的时间段是否正确
if (para.StartTime != null && para.EndTime != null && DateTime.TryParse(para.StartTime.Value.ToString(), out dtQuery) && DateTime.TryParse(para.EndTime.Value.ToString(), out dtQuery))
{
int day = (para.EndTime.Value - para.StartTime.Value).Days + 1;
for (int i = 0; i < day; i++)
{
DateTime dtime = para.StartTime.Value.AddDays(i);
//时间小于当前时间
if (DateTime.Now > dtime)
{
bool flag = StrWhere(db, para).Select(a => a.CalcuTime).ToList().Contains(dtime);
if (!flag)//补数据
{
RP_AADTAndTransCalcu hday = new RP_AADTAndTransCalcu();
hday.CalcuTime = dtime;
if (SessionManage.GetLoginUser() != null)
{
hday.CrtBy = SessionManage.GetLoginUser().UserName;
}
hday.CrtDate = DateTime.Now;
hday.Id = Guid.NewGuid();
hday.EnCar = 0;
hday.EnTra = 0;
hday.EnTrav = 0.00;
hday.ExCar = 0;
hday.ExTra = 0;
hday.State = "0";
hday.ExTrav = 0.00;
db.RP_AADTAndTransCalcu.Add(hday);
}
else//将数据中有空值的改成0
{
var hday = db.RP_AADTAndTransCalcu.Where(a => a.CalcuTime == dtime).ToList()[0];
if (hday.EnCar == null)
hday.EnCar = 0;
if (hday.EnTra == null)
hday.EnTra = 0;
if (hday.EnTrav == null)
hday.EnTrav = 0.00;
if (hday.ExCar == null)
hday.ExCar = 0;
if (hday.ExTra == null)
hday.ExTra = 0;
if (hday.ExTrav == null)
hday.ExTrav = 0.00;
}
}
}
try
{
db.SaveChanges();
transaction.Complete();
}
catch (Exception ex)
{
SystemLog.GetInstance().Error(TipInfo.AddFaile, ex);
}
}
}
}
}
/// <summary>
/// 校正数据
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
public CustomResult CalibrationData(QueryParameters para)
{
CustomResult pReturnValue = new CustomResult();
double pFloating = 1 + para.FloatingRange * 0.01;
List<RP_HDayAADT> pHDayAADT = new List<RP_HDayAADT>();
try
{
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> pRefList = db.RP_HDayAADT.Where(s => s.CalcuTime >= para.LastYearStart && s.CalcuTime <= para.LastYearEnd).ToList();
//如果参考日期数据为0 则返回失败
if (pRefList.Count == 0)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileRefNoData;
return pReturnValue;
}
//需要校正的数据
var pCheckList = db.RP_HDayAADT.Where(s => s.CalcuTime >= para.StartTime && s.CalcuTime <= para.EndTime).ToList();
//如果需要校正的数据为空则返回失败
if (pCheckList.Count == 0)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileNoData;
return pReturnValue;
}
using (TransactionScope tran = new TransactionScope())
{
////校正数据
//RP_HDayAADTSta pCheckInfo = pCheckNaturalList.First();
////参考数据
//RP_HDayAADTSta pRefInfo = pRefNaturalList.First();
foreach (RP_HDayAADT pCheckInfo in pCheckList)//校正数据
{
foreach (RP_HDayAADT pRefInfo in pRefList)//参考数据
{
if (pRefInfo.CalcuTime == pRefInfo.CalcuTime)
{
pCheckInfo.Out = Math.Round(pRefInfo.Out.Value * pFloating);
if (SessionManage.GetLoginUser() != null)
{
pCheckInfo.UpdBy = SessionManage.GetLoginUser().UserName;
}
pCheckInfo.UpdDate = DateTime.Now;
pCheckInfo.State = "1";
break;
}
}
}
db.SaveChanges();
tran.Complete();
pReturnValue.ResultKey = (byte)EResult.Succeed;
pReturnValue.ResultValue = TipInfo.CalibrationSuccess;
}
}
else
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationRangeFaile;
}
}
return pReturnValue;
}
catch (Exception e)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaile + e.Message.ToString();
SystemLog.GetInstance().Error(TipInfo.CalibrationFaile, e);
return pReturnValue;
}
}
/// <summary>
/// 预测导出功能
/// </summary>
/// <param name="para"></param>
/// <param name="list"></param>
/// <returns></returns>
private string Export(QueryParameters para, List<IReportViewModel> list)
{
string path = string.Empty;
string reportpath = string.Empty;
if (para.ReportType == 9)
{
path = string.Format(@"{0}Reporttemplate\编号09--收费公路运行情况统计表.xlsx", AppDomain.CurrentDomain.BaseDirectory);
reportpath = ExportHelper.GetInstance().ExportExcel(path, para, this, list, null);
}
return reportpath;
}
/// <summary>
/// 获取查询集合
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
private List<HourAADTViewModel> GetViewModelInfo(QueryParameters para)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//获取当天全部时间段的数据
List<RP_HourAADT> pHourAADTList = db.RP_HourAADT.Where(s => s.CalcuTime == para.StartTime).ToList();
//获取空数据
List<HourAADTViewModel> pModelList = GetNullInfo(para.ReportType);
//遍历修改空数据的值
foreach (HourAADTViewModel model in pModelList)
{
//遍历25条数据（包括合计）
for (int i = 0; i < 25; i++)
{
RP_HourAADT pHourAADTInfo = null;
List<RP_HourAADT> HourList = pHourAADTList.Where(s => s.HourPer == (byte)i).ToList();
if (HourList.Count > 0)
{
pHourAADTInfo = HourList.SingleOrDefault();
}
if (pHourAADTInfo != null)
{
Type myType = model.GetType();
PropertyInfo pinfo = myType.GetProperty("Count_" + i);
if (para.ReportType == 13)
{
if (model.StaName == "大羊坊站" && model.TraName == "出京入")
{
pinfo.SetValue(model, pHourAADTInfo.Dyf_ExIn.Value);
}
if (model.StaName == "马驹桥东站" && model.TraName == "进京入")
{
pinfo.SetValue(model, pHourAADTInfo.Mjqd_EnIn.Value);
}
if (model.StaName == "马驹桥西站" && model.TraName == "出京入")
{
pinfo.SetValue(model, pHourAADTInfo.Mjqx_ExIn.Value);
}
if (model.StaName == "采育站" && model.TraName == "出京入")
{
pinfo.SetValue(model, pHourAADTInfo.Cy_ExIn.Value);
}
if (model.StaName == "大羊坊站" && model.TraName == "进京出")
{
pinfo.SetValue(model, pHourAADTInfo.Dyf_EnOut.Value);
}
if (model.StaName == "马驹桥东站" && model.TraName == "进京出")
{
pinfo.SetValue(model, pHourAADTInfo.Mjqd_EnOut.Value);
}
if (model.StaName == "马驹桥西站" && model.TraName == "进京出")
{
pinfo.SetValue(model, pHourAADTInfo.Mjqx_EnOut.Value);
}
if (model.StaName == "采育站" && model.TraName == "进京出")
{
pinfo.SetValue(model, pHourAADTInfo.Cy_EnOut.Value);
}
}
else if (para.ReportType == 14)
{
if (model.StaName == "大羊坊站" && (model.TraName == "进出京合计" || model.TraName == "进京"))
{
pinfo.SetValue(model, pHourAADTInfo.Dyf_EnOut.Value);
}
if (model.StaName == "马驹桥东站" && (model.TraName == "进出京合计" || model.TraName == "进京"))
{
pinfo.SetValue(model, pHourAADTInfo.Mjqd_EnOut.Value);
}
if (model.StaName == "马驹桥西站" && (model.TraName == "进出京合计" || model.TraName == "进京"))
{
pinfo.SetValue(model, pHourAADTInfo.Mjqx_EnOut.Value);
}
if (model.StaName == "采育站" && (model.TraName == "进出京合计" || model.TraName == "进京"))
{
pinfo.SetValue(model, pHourAADTInfo.Cy_EnOut.Value);
}
}
}
}
}
return pModelList;
}
}
/// <summary>
/// 查询
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public IReportViewModel GetListByPra(QueryParameters para)
{
modelRRS.RoadRunSit = GetData(para);
return modelRRS;
}
/// <summary>
/// 创建或修改13,14合计
/// </summary>
/// <param name="para"></param>
/// <param name="stationtype">收费站类型</param>
private void CreateOrUpdateSum(QueryParameters para)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//查询日期当天除合计外的全部数据
IEnumerable<RP_HourAADT> all = db.RP_HourAADT.Where(s => s.CalcuTime == para.StartTime && s.HourPer != 24);
IEnumerable<RP_HourAADT> listsum = db.RP_HourAADT.Where(s => s.CalcuTime == para.StartTime && s.HourPer == 24);
RP_HourAADT pHourAADT = new RP_HourAADT();
//如果有数据则进行合计
if (all.Count() > 0)
{
if (listsum.Count() > 0)
{
pHourAADT = listsum.First();
}
pHourAADT.Dyf_ExIn = all.Sum(s => s.Dyf_ExIn);
pHourAADT.Dyf_EnOut = all.Sum(s => s.Dyf_EnOut);
pHourAADT.Mjqd_EnIn = all.Sum(s => s.Mjqd_EnIn);
pHourAADT.Mjqd_EnOut = all.Sum(s => s.Mjqd_EnOut);
pHourAADT.Mjqx_EnOut = all.Sum(s => s.Mjqx_EnOut);
pHourAADT.Mjqx_ExIn = all.Sum(s => s.Mjqx_ExIn);
pHourAADT.Cy_EnOut = all.Sum(s => s.Cy_EnOut);
pHourAADT.Cy_ExIn = all.Sum(s => s.Cy_ExIn);
pHourAADT.State = "1";
using (TransactionScope transaction = new TransactionScope())
{
if (listsum.Count() <= 0)
{
pHourAADT.HourPer = 24;//24代表合计
pHourAADT.Id = Guid.NewGuid();
pHourAADT.CalcuTime = (DateTime)para.StartTime;
pHourAADT.CrtDate = DateTime.Now;
pHourAADT.State = "0";
db.RP_HourAADT.Add(pHourAADT);
}
else
{
pHourAADT.UpdDate = DateTime.Now;
}
db.SaveChanges();
//提交事务
transaction.Complete();
}
}
}
}
/// <summary>
/// 预测
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public CustomResult ForecastData(QueryParameters para)
{
if (listExport != null)
listExport.Clear();
CustomResult pReturnValue = new CustomResult();
//浮动范围
double pFloating = 1 + para.FloatingRange * 0.01;
List<RoadRunSitViewModel> pForeList = new List<RoadRunSitViewModel>();
//预测数据集合
List<IReportViewModel> pInList = new List<IReportViewModel>();
try
{
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_AADTSta> pRefInfoList = db.RP_AADTSta.Where(s => s.CalculTime == para.StartTime).ToList();
//如果参考日期数据为0 则返回失败
if (pRefInfoList==null||pRefInfoList.Count == 0)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.ForecastFaileFaileRefNoData;
return pReturnValue;
}
//预测数据
RoadRunSitViewModel info = new RoadRunSitViewModel();
//预测数据集合
List<IReportViewModel> plist = new List<IReportViewModel>();
//参考数据
foreach (RP_AADTSta pRefInfo in pRefInfoList)
{
//出京总交通量（路线）
info.LineExSum = Math.Round(pRefInfo.LineExSum == null ? 0 : pRefInfo.LineExSum.Value * pFloating, 2);
//进京总交通量（路线）
info.LineEnSum = Math.Round(pRefInfo.LineEnSum == null ? 0 : pRefInfo.LineEnSum.Value * pFloating, 2);
//总交通量（路线）
info.LineSum = info.LineExSum + info.LineEnSum;
//小型客车免费通行交通量（合计）
info.SmaCarFeeNum = Math.Round(pRefInfo.ExSmaCarFee == null ? 0 : pRefInfo.ExSmaCarFee.Value + pRefInfo.EnSmaCarFee == null ? 0 : pRefInfo.EnSmaCarFee.Value, 2);
//小型客车免费金额
info.SmaCarFee = Math.Round(pRefInfo.SmaCarFee == null ? 0 : pRefInfo.SmaCarFee.Value * (decimal)pFloating, 2);
//收费车辆（合计）
info.ChagSumNum = Math.Round(pRefInfo.ChagSumNum == null ? 0 : pRefInfo.ChagSumNum.Value * pFloating, 2);
//数据日期
info.CalculTime = (pRefInfo.CalculTime).Month + "月" + (pRefInfo.CalculTime).Day + "日";
//出进京比“出进京比”=出京交通量/进京交通量，保留两位小数。
if (info.LineEnSum != 0)
{
info.ExEnPer = Math.Round(info.LineExSum / info.LineEnSum, 2);
}
//“同比增幅”=（本年数据-去年数据）/去年数据*100%，保留两位小数。
//小型客车交通量同比增幅
info.SmaCarCompGrow = pRefInfo.SmaCarCompGrow == null ? "0.00" : Math.Round(pRefInfo.SmaCarCompGrow.Value, 2).ToString();
//总交通量同比增幅
info.SumGrow = pRefInfo.SumGrow == null ? "0.00" : Math.Round(pRefInfo.SumGrow.Value, 2).ToString();
plist.Add(info);
}
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>
/// 校正数据
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
public CustomResult CalibrationData(QueryParameters para)
{
CustomResult pReturnValue = new CustomResult();
//判断选择校正时间段的有效性
string[] calibrationDataHour = new string[24];
if (para.StartHour <= para.EndHour)
{
for (int i = para.StartHour; i <= para.EndHour; i++)
{
calibrationDataHour[i] = i.ToString();
}
}
else
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileHour;
return pReturnValue;
}
double pFloating = 1 + para.FloatingRange * 0.01;
List<RP_NatSta> pNaturalTraList = new List<RP_NatSta>();
try
{
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;
}
//获取参考日期符合校正时间段的数据,因为只校正一天的数据，所以只查询开始数据的日期就可以
List<RP_HourAADT> pRefHourAADTList = db.RP_HourAADT.Where(s => s.CalcuTime == para.LastYearStart && calibrationDataHour.Contains(s.HourPer.ToString())).ToList();
//如果参考日期数据为0 则返回失败
if (pRefHourAADTList.Count <= 0)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileRefNoData;
return pReturnValue;
}
//判断时间范围是否相同
if ((para.LastYearEnd - para.LastYearStart) == (para.EndTime - para.StartTime))
{
//需要校正的数据
var pCheckHourAADTList = db.RP_HourAADT.Where(s => s.CalcuTime == para.StartTime && calibrationDataHour.Contains(s.HourPer.ToString())).ToList();
if (pCheckHourAADTList.Count <= 0)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileNoData;
return pReturnValue;
}
using (TransactionScope tran = new TransactionScope())
{
foreach (var item in pCheckHourAADTList)
{
List<RP_HourAADT> plist = pRefHourAADTList.Where(i => i.HourPer == item.HourPer).ToList();
if (plist.Count() > 0)
{
RP_HourAADT pTemp = plist.First();
item.Dyf_EnOut = Math.Round(pTemp.Dyf_EnOut.Value * pFloating);
item.Dyf_ExIn = Math.Round(pTemp.Dyf_ExIn.Value * pFloating);
item.Mjqd_EnIn = Math.Round(pTemp.Mjqd_EnIn.Value * pFloating);
item.Mjqd_EnOut = Math.Round(pTemp.Mjqd_EnOut.Value * pFloating);
item.Mjqx_ExIn = Math.Round(pTemp.Mjqx_ExIn.Value * pFloating);
item.Mjqx_EnOut = Math.Round(pTemp.Mjqx_EnOut.Value * pFloating);
item.Cy_EnOut = Math.Round(pTemp.Cy_EnOut.Value * pFloating);
item.Cy_ExIn = Math.Round(pTemp.Cy_ExIn.Value * pFloating);
item.CalcuTime = para.StartTime.Value;
if (SessionManage.GetLoginUser() != null)
{
item.UpdBy = SessionManage.GetLoginUser().UserName;
}
item.UpdDate = DateTime.Now;
item.State = "1";
}
}
db.SaveChanges();
tran.Complete();
pReturnValue.ResultKey = (byte)EResult.Succeed;
pReturnValue.ResultValue = TipInfo.CalibrationSuccess;
}
}
else
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationRangeFaile;
}
}
}
catch (Exception e)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaile + e.Message.ToString();
SystemLog.GetInstance().Error(TipInfo.CalibrationFaile, e);
return pReturnValue;
}
return pReturnValue;
}
/// <summary>
/// 查询数据表
/// </summary>
/// <param name="args">参数类（查询条件），提供报表类型</param>
/// <returns></returns>
public JsonResult GetInfo(QueryParameters args)
{
var result = ReportFactory.Instance.GetListByPra(args);
return Json(result, "text/html", JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 导出
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public string ExportReport(QueryParameters para)
{
string path = string.Empty;
string reportpath = string.Empty;
if (para.ReportType == 18)
{
path = AppDomain.CurrentDomain.BaseDirectory + @"Reporttemplate\编号18--假期高速公路交通流量统计表.xlsx";
reportpath = ExportHelper.GetInstance().ExportExcel(path, para, this);
}
return reportpath;
}
/// <summary>
/// 校正数据
/// </summary>
/// <returns></returns>
public JsonResult CalibrationData(QueryParameters args)
{
var result = ReportFactory.Instance.CalibrationData(args);
return Json(new { ResultKey = result.ResultKey, ResultValue = result.ResultValue }, "text/html", JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 校正
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public CustomResult CalibrationData(QueryParameters para)
{
CustomResult pReturnValue = new CustomResult();
double pFloating = 1 + para.FloatingRange * 0.01;
List<RP_HDayAADTSta> pNaturalTraList = new List<RP_HDayAADTSta>();
try
{
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_HDayAADTSta> pRefNaturalList = db.RP_HDayAADTSta.Where(s => s.CalcuTime == para.LastYearStart).ToList();
//如果参考日期数据为0 则返回失败
if (pRefNaturalList.Count == 0)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileRefNoData;
return pReturnValue;
}
//需要校正的数据
var pCheckNaturalList = db.RP_HDayAADTSta.Where(s => s.CalcuTime == para.StartTime).ToList();
//如果需要校正的数据为空则返回失败
if (pCheckNaturalList.Count == 0)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileNoData;
return pReturnValue;
}
using (TransactionScope tran = new TransactionScope())
{
////校正数据
//RP_HDayAADTSta pCheckInfo = pCheckNaturalList.First();
////参考数据
//RP_HDayAADTSta pRefInfo = pRefNaturalList.First();
foreach (RP_HDayAADTSta pCheckInfo in pCheckNaturalList)//校正数据
{
foreach (RP_HDayAADTSta pRefInfo in pRefNaturalList)//参考数据
{
if (pRefInfo.LineType != 0 && pCheckInfo.LineType == pRefInfo.LineType)
{
//出京自然交通辆
if (pRefInfo.ExNat != null)
pCheckInfo.ExNat = Math.Round(pRefInfo.ExNat.Value * pFloating);
//进京自然交通辆
if (pRefInfo.EnNat != null)
pCheckInfo.EnNat = Math.Round(pRefInfo.EnNat.Value * pFloating);
//出京当量交通辆
if (pRefInfo.ExEqu != null)
pCheckInfo.ExEqu = Math.Round(pRefInfo.ExEqu.Value * pFloating);
//进京当量交通辆
if (pRefInfo.EnEqu != null)
pCheckInfo.EnEqu = Math.Round(pRefInfo.EnEqu.Value * pFloating);
//拥挤度-“拥挤度”=交通量（当量交通量）合计/设计交通量，保留四位小数；
if (pCheckInfo.ExEqu != null && pCheckInfo.EnEqu != null)
{
HDayAADTViewModel model = new HDayAADTViewModel();
model.LineType = pRefInfo.LineType;
pCheckInfo.CrowDeg = double.Parse(string.Format("{0:0.0000}", (pCheckInfo.ExEqu + pCheckInfo.EnEqu) / model.DeTra));
}
//小型车出京
if (pRefInfo.SmaEx != null)
pCheckInfo.SmaEx = Math.Round(pRefInfo.SmaEx.Value * pFloating);
//小型车进京
if (pRefInfo.SmaEn != null)
pCheckInfo.SmaEn = Math.Round(pRefInfo.SmaEn.Value * pFloating);
//中型车出京
if (pRefInfo.MedEx != null)
pCheckInfo.MedEx = Math.Round(pRefInfo.MedEx.Value * pFloating);
//中型车进京
if (pRefInfo.MedEn != null)
pCheckInfo.MedEn = Math.Round(pRefInfo.MedEn.Value * pFloating);
//大型车出京
if (pRefInfo.LarEx != null)
pCheckInfo.LarEx = Math.Round(pRefInfo.LarEx.Value * pFloating);
//大型车进京
if (pRefInfo.LarEn != null)
pCheckInfo.LarEn = Math.Round(pRefInfo.LarEn.Value * pFloating);
//重型车出京
if (pRefInfo.HeaEx != null)
pCheckInfo.HeaEx = Math.Round(pRefInfo.HeaEx.Value * pFloating);
//重型车进京
if (pRefInfo.HeaEn != null)
pCheckInfo.HeaEn = Math.Round(pRefInfo.HeaEn.Value * pFloating);
//超大型车出京
if (pRefInfo.SupEx != null)
pCheckInfo.SupEx = Math.Round(pRefInfo.SupEx.Value * pFloating);
//超大型车进京
if (pRefInfo.SupEn != null)
pCheckInfo.SupEn = Math.Round(pRefInfo.SupEn.Value * pFloating);
//进出京大货车以上车型数量-进出京大货车以上车型数量”=大型车（合计）+重型车（合计）+超大型车（合计）。
if (pCheckInfo.LarEx != null && pCheckInfo.LarEn != null && pCheckInfo.HeaEx != null && pCheckInfo.HeaEn != null && pCheckInfo.SupEx != null && pCheckInfo.SupEn != null)
pCheckInfo.SupTruNum = pCheckInfo.LarEx + pCheckInfo.LarEn + pCheckInfo.HeaEx + pCheckInfo.HeaEn + pCheckInfo.SupEx + pCheckInfo.SupEn;
//进出京货车数量
if (pRefInfo.EnExTrukNum != null)
pCheckInfo.EnExTrukNum = Math.Round(pRefInfo.EnExTrukNum.Value * pFloating);
//客车货车比例-客车货车比例=（交通量（自然交通量）合计-进出京货车数量)/进出京货车数量*100%，保留四位小数；
if (pCheckInfo.ExNat != null && pCheckInfo.EnNat != null && pCheckInfo.EnExTrukNum != null && pCheckInfo.EnExTrukNum != null && pCheckInfo.EnExTrukNum != 0)
{
pCheckInfo.CarTrukPer = double.Parse(string.Format("{0:0.0000}", (pCheckInfo.ExNat + pCheckInfo.EnNat - pCheckInfo.EnExTrukNum) / pCheckInfo.EnExTrukNum));
}
//大货车以上占货车交通量比例-大货车以上占货车交通量比例（%）=进出京大货车以上车型的数量/进出京货车数量*100%，保留四位小数。
if (pCheckInfo.SupTruNum != null && pCheckInfo.EnExTrukNum != null && pCheckInfo.EnExTrukNum != 0)
{
pCheckInfo.SupTruPer = double.Parse(string.Format("{0:0.0000}", pCheckInfo.SupTruNum / pCheckInfo.EnExTrukNum));
}
if (SessionManage.GetLoginUser() != null)
{
pCheckInfo.UpdBy = SessionManage.GetLoginUser().UserName;
}
pCheckInfo.UpdDate = DateTime.Now;
pCheckInfo.State = "1";
break;
}
}
}
db.SaveChanges();
tran.Complete();
pReturnValue.ResultKey = (byte)EResult.Succeed;
pReturnValue.ResultValue = TipInfo.CalibrationSuccess;
}
}
else
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaileDate;
}
}
return pReturnValue;
}
catch (Exception e)
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.CalibrationFaile + e.Message.ToString();
SystemLog.GetInstance().Error(TipInfo.CalibrationFaile, e);
return pReturnValue;
}
}
/// <summary>
/// 预测数据
/// </summary>
/// <returns></returns>
public JsonResult ForecastData(QueryParameters args)
{
var result = ReportFactory.Instance.ForecastData(args);
return Json(new { ResultKey = result.ResultKey, ResultValue = result.ResultValue }, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 修改工作簿内容
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <returns></returns>
public IWorkbook GenerateSheet(IWorkbook readworkbook, QueryParameters para)
{
//获取工作簿
ISheet sheet = readworkbook.GetSheetAt(0);
//设置日期
if (para.ReportType == 18)
{
SetReportDate(sheet, 1, 14, para.StartTime.Value, para.ReportType);
}
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//设置表头
var holidayName = db.OT_Dic.Where(a => a.Id == para.HolidayId).Select(a => a.Name).ToList();
if (!string.IsNullOrEmpty(holidayName[0]))
SetValue(sheet, 0, 0, string.Format("{0}假期高速公路交通流量统计表", holidayName[0]));
//数据集合
List<HDayAADTViewModel> pHdayAADT = db.RP_HDayAADTSta.Where(s => s.CalcuTime == para.StartTime).Select(s => new HDayAADTViewModel()
{
LineType = s.LineType,
ExNat = s.ExNat,
EnNat = s.EnNat,
NatSum = s.ExNat + s.EnNat,
ExEqu = s.ExEqu,
EnEqu = s.EnEqu,
EquSum = s.ExEqu + s.EnEqu,
CrowDeg = s.CrowDeg,
SmaEx = s.SmaEx,
SmaEn = s.SmaEn,
SmaSum = s.SmaEx + s.SmaEn,
MedEn = s.MedEn,
MedEx = s.MedEx,
MedSum = s.MedEx + s.MedEn,
LarEx = s.LarEx,
LarEn = s.LarEn,
LarSum = s.LarEn + s.LarEx,
HeaEn = s.HeaEn,
HeaEx = s.HeaEx,
HeaSum = s.HeaEx + s.HeaEn,
SupEn = s.SupEn,
SupEx = s.SupEx,
SupSum = s.SupEn + s.SupEx,
EnExTrukNum = s.EnExTrukNum,
CarTrukPer = s.CarTrukPer,
SupTruNum = s.SupTruNum,
SupTruPer = s.SupTruPer
}).ToList().OrderBy(s => s.Sorting).ToList();
if (pHdayAADT.Count > 0)
{
for (int i = 0; i < pHdayAADT.Count; i++)
{
SetValue(sheet, i + 5, 7, pHdayAADT[i].NatSum.ToString());
SetValue(sheet, i + 5, 8, pHdayAADT[i].ExNat.ToString());
SetValue(sheet, i + 5, 9, pHdayAADT[i].EnNat.ToString());
SetValue(sheet, i + 5, 10, pHdayAADT[i].EquSum.ToString());
SetValue(sheet, i + 5, 11, pHdayAADT[i].ExEqu.ToString());
SetValue(sheet, i + 5, 12, pHdayAADT[i].EnEqu.ToString());
SetValue(sheet, i + 5, 14, pHdayAADT[i].CrowDeg.ToString());
SetValue(sheet, i + 5, 15, pHdayAADT[i].SmaSum.ToString());
SetValue(sheet, i + 5, 16, pHdayAADT[i].SmaEx.ToString());
SetValue(sheet, i + 5, 17, pHdayAADT[i].SmaEn.ToString());
SetValue(sheet, i + 5, 18, pHdayAADT[i].MedSum.ToString());
SetValue(sheet, i + 5, 19, pHdayAADT[i].MedEx.ToString());
SetValue(sheet, i + 5, 20, pHdayAADT[i].MedEn.ToString());
SetValue(sheet, i + 5, 21, pHdayAADT[i].LarSum.ToString());
SetValue(sheet, i + 5, 22, pHdayAADT[i].LarEx.ToString());
SetValue(sheet, i + 5, 23, pHdayAADT[i].LarEn.ToString());
SetValue(sheet, i + 5, 24, pHdayAADT[i].HeaSum.ToString());
SetValue(sheet, i + 5, 25, pHdayAADT[i].HeaEx.ToString());
SetValue(sheet, i + 5, 26, pHdayAADT[i].HeaEn.ToString());
SetValue(sheet, i + 5, 27, pHdayAADT[i].SupSum.ToString());
SetValue(sheet, i + 5, 28, pHdayAADT[i].SupEx.ToString());
SetValue(sheet, i + 5, 29, pHdayAADT[i].SupEn.ToString());
SetValue(sheet, i + 5, 30, pHdayAADT[i].EnExTrukNum.ToString());
if (i == 5 && string.IsNullOrEmpty(pHdayAADT[i].CarTrukPer.ToString()))
{
SetValue(sheet, i + 5, 31, 0);
}
else
{
SetValue(sheet, i + 5, 31, pHdayAADT[i].CarTrukPer.ToString());
}
SetValue(sheet, i + 5, 32, pHdayAADT[i].SupTruNum.ToString());
if (i == 5 && string.IsNullOrEmpty(pHdayAADT[i].SupTruPer.ToString()))
{
SetValue(sheet, i + 5, 33, 0);
}
else
{
SetValue(sheet, i + 5, 33, pHdayAADT[i].SupTruPer.ToString());
}
}
}
if (pHdayAADT.Count(s => s.LineType == 3) > 0)
{
HDayAADTViewModel pHday = pHdayAADT.Where(s => s.LineType == 3).FirstOrDefault();
SetValue(sheet, 4, 7, pHday.NatSum.ToString());
SetValue(sheet, 4, 8, pHday.ExNat.ToString());
SetValue(sheet, 4, 9, pHday.EnNat.ToString());
SetValue(sheet, 4, 10, pHday.EquSum.ToString());
SetValue(sheet, 4, 11, pHday.ExEqu.ToString());
SetValue(sheet, 4, 12, pHday.EnEqu.ToString());
SetValue(sheet, 4, 14, pHday.CrowDeg.ToString());
SetValue(sheet, 4, 15, pHday.SmaSum.ToString());
SetValue(sheet, 4, 16, pHday.SmaEx.ToString());
SetValue(sheet, 4, 17, pHday.SmaEn.ToString());
SetValue(sheet, 4, 18, pHday.MedSum.ToString());
SetValue(sheet, 4, 19, pHday.MedEx.ToString());
SetValue(sheet, 4, 20, pHday.MedEn.ToString());
SetValue(sheet, 4, 21, pHday.LarSum.ToString());
SetValue(sheet, 4, 22, pHday.LarEx.ToString());
SetValue(sheet, 4, 23, pHday.LarEn.ToString());
SetValue(sheet, 4, 24, pHday.HeaSum.ToString());
SetValue(sheet, 4, 25, pHday.HeaEx.ToString());
SetValue(sheet, 4, 26, pHday.HeaEn.ToString());
SetValue(sheet, 4, 27, pHday.SupSum.ToString());
SetValue(sheet, 4, 28, pHday.SupEx.ToString());
SetValue(sheet, 4, 29, pHday.SupEn.ToString());
SetValue(sheet, 4, 30, pHday.EnExTrukNum.ToString());
SetValue(sheet, 4, 31, pHday.CarTrukPer.ToString());
SetValue(sheet, 4, 32, pHday.SupTruNum.ToString());
SetValue(sheet, 4, 33, pHday.SupTruPer.ToString());
}
}
return readworkbook;
}
