/// <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);
}
/// <summary>
/// 更新实体信息
/// </summary>
/// <param name="sourcelist">数据源列表</param>
/// <param name="info">实体信息</param>
/// <param name="statype">统计类型</param>
/// <param name="dt">数据日期</param>
/// <param name="ishas">记录是否已存在</param>
private void UpdateInfo(IEnumerable <DS_DataSource> sourcelist, RP_EnEx info, int statype, DateTime dt, bool ishas)
{
//北京段只包含出口收费站
List <int> outBJStation = StationConfiguration.GetOutBJStaion();
if (ishas)//已存在，则更新
{
info.UpdDate = DateTime.Now;
info.State = "1";
}
else//不存在，添加
{
info.Id = Guid.NewGuid();
info.CrtDate = DateTime.Now;
info.State = "0";
}
//给实体字段赋值
//0:小型客车，1：其他客车，2：货车，3：绿通
info.EnSmaCar = sourcelist.Where(s => s.CalcuType == 0).Sum(s => s.OutNum.Value);
info.EnOthCar = sourcelist.Where(s => s.CalcuType == 1).Sum(s => s.OutNum.Value);
info.EnTruk = sourcelist.Where(s => s.CalcuType == 2).Sum(s => s.OutNum.Value);
//泗村店，入境“绿色通道”数据项填写“0”
info.EnGre = 0;
info.StaType = statype;
info.CalcuTime = dt;
//泗村店不统计出镜，，北京段出境绿通为0,不包含马驹桥东入口
if (statype == (int)StationConfiguration.StationType.BeiJingDuan)
{
info.ExSmaCar = sourcelist.Where(s => outBJStation.Contains(s.StaID.Value) && s.CalcuType == 0).Sum(s => s.InNum.Value);
info.ExOthCar = sourcelist.Where(s => outBJStation.Contains(s.StaID.Value) && s.CalcuType == 1).Sum(s => s.InNum.Value);
info.ExTruk = sourcelist.Where(s => outBJStation.Contains(s.StaID.Value) && s.CalcuType == 2).Sum(s => s.InNum.Value);
info.ExGre = 0;
info.EnGre = sourcelist.Where(s => s.CalcuType == 3).Sum(s => s.OutNum.Value);
}
}
/// <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 && DateTime.TryParse(para.StartTime.Value.ToString(), out dtQuery))
{
DateTime dtime = para.StartTime.Value;
//时间小于当前时间
if (DateTime.Now > dtime)
{
bool flag = db.RP_EnEx.Where(a => a.CalcuTime == dtime & a.StaType == para.StationType).Select(a => a.CalcuTime).ToList().Contains(dtime);
if (!flag)//补数据
{
RP_EnEx hday = new RP_EnEx();
hday.CalcuTime = dtime;
if (SessionManage.GetLoginUser() != null)
{
hday.CrtBy = SessionManage.GetLoginUser().UserName;
}
hday.CrtDate = DateTime.Now;
hday.Id = Guid.NewGuid();
hday.EnGre = 0;
hday.EnOthCar = 0;
hday.EnSmaCar = 0;
hday.EnTruk = 0;
hday.State = "0";
hday.StaType = para.StationType;
db.RP_EnEx.Add(hday);
}
else//将数据中有空值的改成0
{
var hday = db.RP_EnEx.Where(a => a.CalcuTime == dtime & a.StaType == para.StationType).ToList()[0];
if (hday.EnGre == null)
{
hday.EnGre = 0;
}
if (hday.EnOthCar == null)
{
hday.EnOthCar = 0;
}
if (hday.EnSmaCar == null)
{
hday.EnSmaCar = 0;
}
if (hday.EnTruk == null)
{
hday.EnTruk = 0;
}
}
}
try
{
db.SaveChanges();
transaction.Complete();
}
catch (Exception ex)
{
SystemLog.GetInstance().Error(TipInfo.AddFaile, ex);
}
}
}
}
}
/// <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>
/// 校正
/// </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>
/// 更新或获取报表5,6数据
/// </summary>
/// <param name="dt"></param>
/// <param name="HourPer"></param>
public void Update(DateTime dt, int HourPer)
{
try
{
string startTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
SystemLog.GetInstance().Log.Info(string.Format("{0}：开始获取5,6报表数据", startTime));
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
DateTime pDt = DateTime.Parse(dt.ToShortDateString());
//北京段包含出入口收费站
List <int> BJStation = StationConfiguration.GetBJStaion();
//获取数据//p.VehType == 0 表示合计数据，
IEnumerable <DS_DataSource> pCollection = db.DS_DataSource.Where(p =>
DbFunctions.TruncateTime(p.CalcuTime) == DbFunctions.TruncateTime(dt) &&
p.VehType == 0);
//判断北京段数据是否已存在，StaType：1表示北京段，33表示泗村店
RP_EnEx pBJEnExInfo = null;
RP_EnEx pSCDEnExInfo = null;
bool pIsHas = false;
//北京段数据
List <RP_EnEx> pBJList = db.RP_EnEx.Where(p => p.CalcuTime == pDt && p.StaType == (int)StationConfiguration.StationType.BeiJingDuan).ToList();
if (pBJList.Count > 0)
{
pBJEnExInfo = pBJList.FirstOrDefault();
//每种状态需要重新赋值，防止公用同一个变量，值不明确
pIsHas = true;
}
else
{
pBJEnExInfo = new RP_EnEx();
pIsHas = false;
}
//更新北京段实体信息
UpdateInfo(pCollection.Where(s => BJStation.Contains(s.StaID.Value)), pBJEnExInfo, (int)StationConfiguration.StationType.BeiJingDuan, pDt, pIsHas);
//泗村店数据
List <RP_EnEx> pSCDList = db.RP_EnEx.Where(p => p.CalcuTime == pDt && p.StaType == (int)StationConfiguration.StationID.SCD).ToList();
if (pSCDList.Count > 0)
{
pSCDEnExInfo = pSCDList.FirstOrDefault();
pIsHas = true;
}
else
{
pSCDEnExInfo = new RP_EnEx();
pIsHas = false;
}
//更新泗村店实体信息
UpdateInfo(pCollection.Where(s => s.StaID.Value == (int)StationConfiguration.StationID.SCD), pSCDEnExInfo, (int)StationConfiguration.StationID.SCD, pDt, pIsHas);
//更新或添加到数据库
using (TransactionScope trans = new TransactionScope())
{
//如果不存在，则添加，否则则更新
if (pBJList.Count <= 0)
{
db.RP_EnEx.Add(pBJEnExInfo);
}
if (pSCDList.Count <= 0)
{
db.RP_EnEx.Add(pSCDEnExInfo);
}
db.SaveChanges();
trans.Complete();
}
}
string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
SystemLog.GetInstance().Log.Info(string.Format("{0}：结束获取5,6报表数据", endTime));
SystemLog.GetInstance().Log.Info(string.Format("统计5,6报表数据耗时{0}秒", (DateTime.Parse(endTime) - DateTime.Parse(startTime)).TotalSeconds));
//显示执行该方法的线程ID
//SystemLog.GetInstance().Log.Info(string.Format("调用5,6Update的线程ID为：{0}", Thread.CurrentThread.ManagedThreadId));
Thread.Sleep(1000);
}
catch (Exception ex)
{
throw ex;
}
}
