/// <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;
}
public List<OT_ErrorStation> GetList()
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
return db.OT_ErrorStation.ToList();
}
}
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}：开始获取13,14报表数据", startTime));
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
DateTime pDt = DateTime.Parse(dt.ToShortDateString());
//报表13,14收费站
List<int> BJStation = StationConfiguration.GetBJStaion();
//获取今年当日数据//p.VehType == 0 表示合计数据，
List<DS_DataSource> pCollection = db.DS_DataSource.Where(p => p.CalcuTime == dt && p.VehType == 0 && p.HourPer == HourPer && BJStation.Contains(p.StaID.Value)).ToList();
bool pIsHas = false;
//北京段数据
RP_HourAADT pHoursTraffic = null;
List<RP_HourAADT> pBJList = db.RP_HourAADT.Where(p => p.CalcuTime == pDt && p.HourPer == (byte)HourPer).ToList();
if (pBJList.Count > 0)
{
pHoursTraffic = pBJList.FirstOrDefault();
//每种状态需要重新赋值，防止公用同一个变量，值不明确
pIsHas = true;
}
else
{
pHoursTraffic = new RP_HourAADT();
pIsHas = false;
}
//更新实体
UpdateInfo(pCollection, pHoursTraffic, HourPer, pDt, pIsHas);
using (TransactionScope transaction = new TransactionScope())
{
//更新或添加
if (!pIsHas)
db.RP_HourAADT.Add(pHoursTraffic);
db.SaveChanges();
//提交事务
transaction.Complete();
}
}
string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
SystemLog.GetInstance().Log.Info(string.Format("{0}：结束获取,13,14报表数据", endTime));
SystemLog.GetInstance().Log.Info(string.Format("统计13,14报表数据耗时{0}秒", (DateTime.Parse(endTime) - DateTime.Parse(startTime)).TotalSeconds));
//显示执行该方法的线程ID
//SystemLog.GetInstance().Log.Info(string.Format("调用13,14Update的线程ID为：{0}", Thread.CurrentThread.ManagedThreadId));
Thread.Sleep(1000);
}
catch (Exception ex)
{
throw ex;
}
}
/// <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;
}
}
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}：开始获取7报表数据", startTime));
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
DateTime pDt = DateTime.Parse(dt.ToShortDateString());
//获取数据//p.VehType == 0 表示合计数据，
IEnumerable<DS_DataSource> pCollection = db.DS_DataSource.Where(p =>
DbFunctions.TruncateTime(p.CalcuTime) == DbFunctions.TruncateTime(dt)
&& p.VehType == 0 && p.StaID == (int)StationConfiguration.StationID.DYF);
//大羊坊单日数据
List<RP_AADTAndTransCalcu> pList = db.RP_AADTAndTransCalcu.Where(p => p.CalcuTime == pDt).ToList();
RP_AADTAndTransCalcu pAADTInfo = null;
bool pIsHas = false;
if (pList.Count > 0)
{
pAADTInfo = pList.FirstOrDefault();
pIsHas = true;
}
else
{
pAADTInfo = new RP_AADTAndTransCalcu();
}
//更新实体
UpdateInfo(pCollection, pAADTInfo, pDt, pIsHas);
using (TransactionScope transaction = new TransactionScope())
{
//如果不存在，则添加，否则则更新
if (pList.Count <= 0)
db.RP_AADTAndTransCalcu.Add(pAADTInfo);
db.SaveChanges();
transaction.Complete();
}
}
string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
SystemLog.GetInstance().Log.Info(string.Format("{0}：结束获取7报表数据", endTime));
SystemLog.GetInstance().Log.Info(string.Format("统计7报表数据耗时{0}秒", (DateTime.Parse(endTime) - DateTime.Parse(startTime)).TotalSeconds));
//显示执行该方法的线程ID
//SystemLog.GetInstance().Log.Info(string.Format("调用7Update的线程ID为：{0}", Thread.CurrentThread.ManagedThreadId));
Thread.Sleep(1000);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 修改假期配置
/// </summary>
/// <param name="args">配置信息实体</param>
/// <returns>影响行数</returns>
public CustomResult Update(OT_HDayConfig model)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
using (TransactionScope transaction = new TransactionScope())
{
CustomResult pReturnValue = new CustomResult();
var list = db.OT_HDayConfig.Where(a => a.Id == model.Id).ToList();
if (list != null && list.Count > 0)
{
if (model.CheckFloat != null && model.CheckFloat > 0)
list[0].CheckFloat = model.CheckFloat;
if (model.CompEnd != null)
list[0].CompEnd = model.CompEnd;
if (model.CompStart != null)
list[0].CompStart = model.CompStart;
if (!string.IsNullOrEmpty(model.ConfigName))
list[0].ConfigName = model.ConfigName;
if (model.ForeDate != null)
list[0].ForeDate = model.ForeDate;
if (model.ForeFloat != null && model.ForeFloat > 0)
list[0].ForeFloat = model.ForeFloat;
if (model.HDayEnd != null)
list[0].HDayEnd = model.HDayEnd;
if (model.HDayId != null && model.HDayId > 0)
list[0].HDayId = model.HDayId;
if (model.HDayStart != null)
list[0].HDayStart = model.HDayStart;
if (!string.IsNullOrEmpty(model.RptRemark))
list[0].RptRemark = model.RptRemark;
pReturnValue = Result.SaveUpdateResult(db, transaction);
}
else
{
pReturnValue.ResultKey = (byte)EResult.IsNull1;
pReturnValue.ResultValue = Wttech.DataSubmitted.Common.Resources.TipInfo.Inexist;
}
return pReturnValue;
}
}
}
/// <summary>
/// 修改数据信息
/// </summary>
/// <typeparam name="T">数据表类型集合</typeparam>
/// <param name="args">参数</param>
/// <returns>影响行数</returns>
public CustomResult Update(UpdateHdayExViewModel args)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
using (TransactionScope transaction = new TransactionScope())
{
CustomResult pReturnValue = new CustomResult();
int count = (args.EndTime.Value - args.StartTime.Value).Days + 1;
HDayExViewModel model = null;
if (args.DataInfo.Count > 0)
{
if (args.DataInfo[0].Num == 15)
model = args.DataInfo[0];
}
try
{
for (int i = 0; i < count; i++)
{
DateTime pDateTime = args.StartTime.Value.AddDays(i);
var listHDa = db.RP_HDayAADT.Where(a => a.CalcuTime == pDateTime).ToList();
foreach (RP_HDayAADT hd in listHDa)
{
Type myType = model.GetType();
PropertyInfo pinfo = myType.GetProperty("Tra" + (i + 1));
hd.Out = (double)pinfo.GetValue(model);
hd.UpdDate = DateTime.Now;
hd.State = "1";
if (SessionManage.GetLoginUser() != null)
{
hd.UpdBy = SessionManage.GetLoginUser().UserName;
}
}
}
db.SaveChanges();
transaction.Complete();
pReturnValue.ResultKey = (byte)EResult.Succeed;
pReturnValue.ResultValue = TipInfo.UpdateSuccess;
return pReturnValue;
}
catch (Exception ex)
{
Common.SystemLog.GetInstance().Log.Info(TipInfo.UpdateDataRepeat, ex);
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.UpdateDataRepeat;
return pReturnValue;
}
}
}
}
/// <summary>
/// 获取天津段的收费站
/// </summary>
/// <returns></returns>
public static List<int> GetTJStaion()
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//3表示天津段
List<string> stationliststr = db.OT_Station.Where(s => s.District == 3 && s.IsDelete == 0).Select(s => s.Num).ToList();
List<int> stationlist = new List<int>();
for (int i = 0; i < stationliststr.Count; i++)
{
stationlist.Add(int.Parse(stationliststr[i]));
}
return stationlist;
}
}
/// <summary>
/// 添加文件导出记录
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
public byte Create(OT_ExportHis args)
{
byte num;
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
using (TransactionScope transaction = new TransactionScope())
{
db.OT_ExportHis.Add(args);
num = (byte)db.SaveChanges();
transaction.Complete();
}
}
return num;
}
/// <summary>
/// 数据提交结果-删除
/// </summary>
/// <param name="db">提交数据库对象</param>
/// <returns>提交结果</returns>
public static CustomResult DelChangesResult(DataSubmittedEntities db)
{
CustomResult pReturnValue = new CustomResult();
if (db.SaveChanges() > 0)
{
pReturnValue.ResultKey = (byte)EResult.Succeed;
pReturnValue.ResultValue = Resources.TipInfo.DeleteSuccess;
}
else
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = Resources.TipInfo.DeleteFaile;
}
return pReturnValue;
}
/// <summary>
/// 返回带事物的提交结果-新增
/// </summary>
/// <param name="db">提交数据库对象</param>
/// <param name="transaction">事物</param>
/// <returns>提交结果</returns>
public static CustomResult SaveChangesResult(DataSubmittedEntities db, TransactionScope transaction)
{
CustomResult pReturnValue = new CustomResult();
if (db.SaveChanges() > 0)
{
transaction.Complete();
pReturnValue.ResultKey = (byte)EResult.Succeed;
pReturnValue.ResultValue = Resources.TipInfo.AddSuccess;
}
else
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = Resources.TipInfo.AddFaile;
}
return pReturnValue;
}
/// <summary>
/// 初始化查询条件
/// </summary>
/// <returns></returns>
public Common.ViewModels.WhereHDayStaExEnViewModel GetHdayExEnWhere()
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
var hday = db.OT_HDayConfig.Where(a => a.Id == 11).ToList();
WhereHDayStaExEnViewModel model = new WhereHDayStaExEnViewModel();
if (hday != null && hday.Count > 0)
{
model.StartTime = (DateTime)hday[0].HDayStart;
model.HolidayId = (int)hday[0].HDayId;
model.EndTime = (DateTime)hday[0].HDayEnd;
}
return model;
}
}
/// <summary>
/// 登录
/// </summary>
/// <param name="name">用户名</param>
/// <param name="pwd">密码</param>
/// <param name="Session">用户信息缓存</param>
/// <returns><验证结果/returns>
public byte Login(string name, string pwd)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//查找数据库中用户名匹配的记录
List<OT_User> list = db.OT_User.Where(a => a.Name == name).ToList();
//存在该用户名
if (list != null && list.Count > 0)
{
//对密码进行加密
MD5Encryptor md5 = new MD5Encryptor();
string password = md5.Encrypt(pwd);
//密码比对
if (list[0].Password == password)
{
//检查状态
if (list[0].IsDelete == (byte)EUserStatus.Normal)
{
SaveUserInfo(list[0].Id);
return (byte)ELoginResult.Succeed;
}
else
{
return (byte)ELoginResult.IsDelete;
}
}
else
{
return (byte)ELoginResult.PasswordError;
}
}
else
{
return (byte)ELoginResult.NameInexist;
}
}
}
/// <summary>
/// 保存用户信息到缓存中
/// </summary>
/// <param name="userId">用户编号</param>
/// <param name="Session">存储用户信息缓存</param>
private void SaveUserInfo(Guid userId)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
List<UserInfoViewModel> list = db.OT_User.Where(a => a.Id == userId & a.IsDelete == (byte)EUserStatus.Normal).Select(a => new UserInfoViewModel
{
UserId = a.Id,
UserName = a.Name,
}).ToList();
//给用户的角色赋值
foreach (UserInfoViewModel model in list)
{
model.RoleList = db.OT_UserRole.Where(a => a.UserId == userId && a.IsDelete == (byte)EDataStatus.Normal).Select(a => new RoleInfoViewModel
{
RoleId = a.RoleId,
RoleName = a.OT_Role.Name
}).ToList();
}
SessionManage.SetSession("UserInfo", list[0]);
}
}
/// <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="args"></param>
/// <returns></returns>
public Common.CustomResult Update(Common.ViewModels.UpdateHDayStaExEnViewModel args)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
using (TransactionScope transaction = new TransactionScope())
{
CustomResult pReturnValue = new CustomResult();
bool flag = false;//标记数据库中是否有数据修改
int count = (args.EndTime - args.StartTime).Days + 1;
for (int j = 1; j < args.DataInfo.Count; j++)
{
HDayStaExEnViewModel model = args.DataInfo[j];
Type myType0 = qModel.TitleList[0].GetType();
for (int i = 1; i < count + 1; i++)
{
Type myType = model.GetType();
PropertyInfo pinfo = myType.GetProperty("Date" + i);
PropertyInfo pinfo0 = myType0.GetProperty("Date" + i);
if (args.StartTime.AddDays(i - 1).ToString("M月d日").ToString() == pinfo0.GetValue(qModel.TitleList[0]).ToString())
{
DateTime dt = args.StartTime.AddDays(i - 1);
var list = db.RP_HDayAADT.Where(a => a.CalcuTime == dt).ToList();
double info = double.Parse(pinfo.GetValue(model).ToString());
foreach (var item in list)
{
flag = true;
if (model.Num == "43")//杨村站
item.YC = info;
else if (model.Num == "44")//宜兴埠东站
item.YXBD = info;
else if (model.Num == "45")//宜兴埠西站
item.YXBX = info;
else if (model.Num == "46")//金钟路站
item.JZL = info;
else if (model.Num == "47")//机场站
item.JC = info;
else if (model.Num == "48")//空港经济区站
item.KG = info;
else if (model.Num == "49")//塘沽西站
item.TGX = info;
else if (model.Num == "50")//塘沽西分站
item.TGXF = info;
else if (model.Num == "51")//塘沽北站
item.TGB = info;
item.UpdDate = DateTime.Now;
if (SessionManage.GetLoginUser() != null)
{
item.UpdBy = SessionManage.GetLoginUser().UserName;
}
item.State = "1";
}
}
}
}
if (flag)//有修改成功的数据
{
try
{
db.SaveChanges();
transaction.Complete();
pReturnValue.ResultKey = (byte)EResult.Succeed;
pReturnValue.ResultValue = TipInfo.UpdateSuccess;
return pReturnValue;
}
catch (Exception ex)
{
Common.SystemLog.GetInstance().Log.Info(TipInfo.UpdateDataRepeat, ex);
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.UpdateDataRepeat;
return pReturnValue;
}
}
else//存在数据尚未生成
{
pReturnValue.ResultKey = (byte)EResult.Fail;
pReturnValue.ResultValue = TipInfo.UpdateDataRepeat;
return pReturnValue;
}
}
}
}
/// <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>
/// 补数据
/// </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="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>
/// 预测导出使用
/// </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>
/// <returns></returns>
public ConfigTimeViewModel GetRoadRunSitWhere()
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
var list = db.OT_HDayConfig.Where(a => a.Id == 9).Select(a => new ConfigTimeViewModel
{
HolidayStartTime = a.HDayStart,
HolidayEndTime = a.HDayEnd,
ComparedStartTime = a.CompStart,
ComparedEndTime = a.CompEnd
}).ToList();
ConfigTimeViewModel model = new ConfigTimeViewModel();
if (list != null && list.Count > 0)
model = list[0];
return model;
}
}
/// <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>
/// 更新或获取报表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;
}
}
/// <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="db">提交数据库对象</param>
/// <param name="transaction">事物</param>
/// <returns>提交结果</returns>
public static CustomResult SaveUpdateResult(DataSubmittedEntities db, TransactionScope transaction)
{
CustomResult pReturnValue = new CustomResult();
try
{
db.SaveChanges();
transaction.Complete();
pReturnValue.ResultKey = (byte)EResult.Succeed;
pReturnValue.ResultValue = Resources.TipInfo.UpdateSuccess;
}
catch (Exception ex)
{
SystemLog.GetInstance().Error(TipInfo.UpdateFaile, ex);
pReturnValue.ResultKey = (byte)EResult.Fail;//程序已经使用多处，所有不变动
pReturnValue.ResultValue = Resources.TipInfo.UpdateFaile;
}
return pReturnValue;
}
/// <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="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>
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>
///更新每日报送数据15.16.17
/// </summary>
public void Update(DateTime dt, int HourPer)
{
string startTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
SystemLog.GetInstance().Log.Info(string.Format("{0}：开始获取15,16,17报表数据", startTime));
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
DateTime pdt = DateTime.Parse(dt.ToShortDateString());
//北京段相同数据是否存在
List<RP_NatSta> pBJlist = db.RP_NatSta.Where(p =>
p.CalcuTime == pdt
&& p.HourPer == (byte?)HourPer
&& p.StaType == (int)StationConfiguration.StationType.BeiJingDuan).ToList();
bool BJHasTime = pBJlist.Count > 0;
//大羊坊相同数据是否存在
List<RP_NatSta> pDYFlist = db.RP_NatSta.Where(p =>
p.CalcuTime == pdt
&& p.HourPer == (byte?)HourPer
&& p.StaType == (int)StationConfiguration.StationID.DYF).ToList();
bool DYFHasTime = pDYFlist.Count > 0;
IList<RP_NatSta> pNaturalEntities = new List<RP_NatSta>();
List<int> BJStation = StationConfiguration.GetBJStaion();
List<int> outBJStation = StationConfiguration.GetOutBJStaion();
//p.VehType != 0 表示不包括合计数据，
IEnumerable<DS_DataSource> pStagingCollection = db.DS_DataSource.Where(p =>
p.CalcuTime == dt
&& p.VehType != 0
&& p.HourPer == HourPer
&& BJStation.Contains(p.StaID.Value));
RP_NatSta pNaturalTrafficBJ = null;//北京段
RP_NatSta pNaturalTraffic = null;//大羊坊
//北京段
//如果相同日期，相同时间段，相同统计站类型数据已存在则进行更新
if (!BJHasTime)
{
pNaturalTrafficBJ = new RP_NatSta();//北京段
pNaturalTrafficBJ.Id = Guid.NewGuid();
}
else
{
pNaturalTrafficBJ = pBJlist[0];
}
pNaturalTrafficBJ.EnNum = pStagingCollection.Sum(s => s.OutNum);
pNaturalTrafficBJ.ExNum = pStagingCollection.Where(s => outBJStation.Contains(s.StaID.Value)).Sum(s => s.InNum);
pNaturalTrafficBJ.Sum = pNaturalTrafficBJ.EnNum + pNaturalTrafficBJ.ExNum;
pNaturalTrafficBJ.StaType = (int)StationConfiguration.StationType.BeiJingDuan;
pNaturalTrafficBJ.CalcuTime = DateTime.Parse(dt.ToShortDateString());
pNaturalTrafficBJ.HourPer = (byte)HourPer;
pNaturalTrafficBJ.CrtDate = DateTime.Now;
pNaturalTrafficBJ.RunStae = SystemConst.RunningStatus;
pNaturalTrafficBJ.State = "0";
//大羊坊
//如果相同日期，相同时间段，相同统计站类型数据已存在则进行更新
if (!DYFHasTime)
{
pNaturalTraffic = new RP_NatSta();//大羊坊
pNaturalTraffic.Id = Guid.NewGuid();
}
else
{
pNaturalTraffic = pDYFlist[0];
}
pNaturalTraffic.EnNum = pStagingCollection.Where(s => s.StaID == (int)StationConfiguration.StationID.DYF).Sum(s => s.OutNum);
pNaturalTraffic.ExNum = pStagingCollection.Where(s => s.StaID == (int)StationConfiguration.StationID.DYF).Sum(s => s.InNum);
pNaturalTraffic.Sum = pNaturalTraffic.EnNum + pNaturalTraffic.ExNum;
pNaturalTraffic.StaType = (int)StationConfiguration.StationID.DYF;
pNaturalTraffic.CrtDate = DateTime.Now;
pNaturalTraffic.HourPer = (byte)HourPer;
pNaturalTraffic.CalcuTime = DateTime.Parse(dt.ToShortDateString());
pNaturalTraffic.RunStae = SystemConst.RunningStatus;
pNaturalTraffic.State = "0";
pNaturalEntities.Add(pNaturalTrafficBJ);
pNaturalEntities.Add(pNaturalTraffic);
using (TransactionScope transaction = new TransactionScope())
{
//如果都不存在，则添加
if (!DYFHasTime && !BJHasTime)
db.RP_NatSta.AddRange(pNaturalEntities);
else if (!DYFHasTime && BJHasTime)
db.RP_NatSta.Add(pNaturalTraffic);
else if (DYFHasTime && !BJHasTime)
db.RP_NatSta.Add(pNaturalTrafficBJ);
db.SaveChanges();
//提交事务
transaction.Complete();
}
string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
SystemLog.GetInstance().Log.Info(string.Format("{0}：结束获取15.16.17报表数据", endTime));
SystemLog.GetInstance().Log.Info(string.Format("统计15,16,17报表数据耗时{0}秒", (DateTime.Parse(endTime) - DateTime.Parse(startTime)).TotalSeconds));
//显示执行该方法的线程ID
//SystemLog.GetInstance().Log.Info(string.Format("调用15,16,17Update的线程ID为：{0}", Thread.CurrentThread.ManagedThreadId));
Thread.Sleep(1000);
}
}
