/// <summary>
/// 获取角色信息列表
/// </summary>
/// <returns>角色信息列表</returns>
public List <RoleInfoViewModel> GetRoleList()
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
List <RoleInfoViewModel> list = db.OT_Role.Where(a => a.IsDelete == (byte)EDataStatus.Normal).Select(a => new RoleInfoViewModel
{
RoleId = a.Id,
RoleName = a.Name,
}).ToList();
return(list);
}
}
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 Common.CustomResult Update(Common.ViewModels.UpdateEnExViewModel args)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
using (TransactionScope transaction = new TransactionScope())
{
CustomResult pReturnValue = new CustomResult();
DateTime dt = new DateTime();
if (DateTime.TryParse(args.DataInfo[0].CalcuTime, out dt))
{
var list = db.RP_EnEx.Where(a => a.CalcuTime == dt & a.StaType == args.StationType).ToList();
if (list != null && list.Count > 0 && args.DataInfo != null && args.DataInfo.Count > 0)
{
// list[0].CalcuTime = args.DataInfo[0].CalcuTime;
// list[0].EnGre = args.DataInfo[0].EnGre;
list[0].EnOthCar = args.DataInfo[0].EnOthCar;
list[0].EnSmaCar = args.DataInfo[0].EnSmaCar;
list[0].EnTruk = args.DataInfo[0].EnTruk;
list[0].State = "1";
list[0].UpdDate = DateTime.Now;
if (SessionManage.GetLoginUser() != null)
{
list[0].UpdBy = SessionManage.GetLoginUser().UserName;
}
}
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.DateTimeIsNull;
return(pReturnValue);
}
}
}
}
/// <summary>
/// 连接远程服务器 注：此方式创建经过测试发现目前无效，目前采用运维人员执行脚本，创建远程连接
/// </summary>
/// <returns></returns>
public bool RemoteConnection()
{
bool pIsTrue = false;
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//db.Database.ExecuteSqlCommand(" exec sp_addlinkedserver 'srv_lnk', '', 'SQLOLEDB', @ServerIp",new SqlParameter("@ServerIp","172.16.2.45"));
//db.Database.ExecuteSqlCommand("exec sp_addlinkedsrvlogin 'srv_lnk', 'false',null, @UserName,@PassWord",new SqlParameter[]{new SqlParameter("@UserName","sa"),new SqlParameter("@PassWord","tayh@2013")});
db.SP_LinkServer(config["IP"], config["UserName"], config["PassWord"]);
pIsTrue = true;
}
return(pIsTrue);
}
public HDayAADTViewModelParaViewModel GetHDayAADTPara()
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
var hday = db.OT_HDayConfig.Where(a => a.Id == 18).ToList();
HDayAADTViewModelParaViewModel model = new HDayAADTViewModelParaViewModel();
model.StartTime = hday[0].HDayStart.Value;
model.EndTime = hday[0].HDayEnd.Value;
model.HolidayId = (int)hday[0].HDayId;
return(model);
}
}
/// <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="readworkbook"></param>
/// <returns></returns>
public IWorkbook GenerateSheet(IWorkbook readworkbook, QueryParameters para)
{
//数据是从数据库中取的，金额需要将单位转为万元
//获取工作簿
ISheet sheet = readworkbook.GetSheetAt(0);
//设置日期
if (para.ReportType == 1)
{
SetReportDate(sheet, 0, 8, para.StartTime.Value, para.ReportType);
}
if (para.ReportType == 3)
{
SetReportDate(sheet, 0, 4, para.StartTime.Value, para.ReportType);
}
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//出口数据集合
List <DataDailyInfoViewModel> pOutlist = db.RP_Daily.Where(s => s.CalcuTime == para.StartTime && s.StaType == para.StationType).ToList().Select(s => new DataDailyInfoViewModel()
{
CarChag = s.ChagFee,
VehNum = (float)s.OutNum,//出口
VehType = s.VehType.ToString()
}).ToList();
//入口数据集合
List <DataDailyInfoViewModel> pInlist = db.RP_Daily.Where(s => s.CalcuTime == para.StartTime && s.StaType == para.StationType).ToList().Select(s => new DataDailyInfoViewModel()
{
CarChag = s.ChagFee,
VehNum = (float)s.InNum,//入口
VehType = s.VehType.ToString()
}).ToList();
for (int i = 0; i < 4; i++)
{
string ptemp = i.ToString();
int rnum = i;
//表样不完全相同，需要更改赋值单元格行数,1和2行数相同,3和4行数相同
if (para.ReportType == 3 || para.ReportType == 4)
{
rnum = i - 1;
}
//0小型客车，1其他客车，2货车（不包含绿通），3绿通
//出口
SetValue(sheet, rnum + 4, 2, pOutlist.Where(s => s.VehType == ptemp).Sum(s => s.VehNum).ToString());
SetValue(sheet, rnum + 4, 4, Math.Round(pOutlist.Where(s => s.VehType == ptemp).Sum(s => s.CarChag.Value) / 10000, 2).ToString());
//入口
SetValue(sheet, rnum + 8, 2, pInlist.Where(s => s.VehType == ptemp).Sum(s => s.VehNum).ToString());
}
}
return(readworkbook);
}
/// <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="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="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>
/// <returns></returns>
public List <NaturalInfoViewModel> GetList()
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
List <NaturalInfoViewModel> naturalInfo = db.RP_NatSta.Where(s => s.StaType == (int)StationConfiguration.StationType.BeiJingDuan).Select(s => new NaturalInfoViewModel
{
HourPer = (byte)s.HourPer,
DayTraffic = (double)s.Sum,
InDayTraffic = (double)s.EnNum,
OutDayTraffic = (double)s.ExNum,
RunningStatus = s.RunStae,
Remark = s.Rek
}).ToList();
return(naturalInfo);
}
}
/// <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="id">编号</param>
/// <returns></returns>
public string GetRoleNamebyId(Guid id)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
var name = db.OT_Role.Where(a => a.Id == id & a.IsDelete == (byte)EDataStatus.Normal).Select(a => a.Name).ToList();
if (name != null && name.Count > 0)
{
return(name[0].ToString());
}
else
{
return(string.Empty);
}
}
}
/// <summary>
/// 添加用户信息
/// </summary>
/// <param name="userInfo">添加用户信息实体</param>
/// <returns>添加结果</returns>
public byte Create(AddUserInfoViewModel userInfo)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//查找用户表中是否已经存在该用户
var user = db.OT_User.Where(a => a.Name == userInfo.UserName).Select(a => new
{
UserId = a.Id,
IsDelete = a.IsDelete
}).ToList();
//有记录，则返回用户已存在
if (user != null && user.Count > 0)
{
if (user[0].IsDelete == (byte)EUserStatus.Normal)
{
return((byte)EResult.IsRepeat);
}
else
{
return((byte)EResult.IsRepeatDel);
};
}
using (TransactionScope transaction = new TransactionScope())
{
//构建并添加用户信息
OT_User userModel = new OT_User();
userModel.Id = Guid.NewGuid();
userModel.IsDelete = (byte)EUserStatus.Normal;
userModel.Name = userInfo.UserName;
userModel.Password = ConfigurationManager.AppSettings["StartPassword"];
db.OT_User.Add(userModel);
//构建添加用户角色关系
foreach (Guid roleId in userInfo.RoleIdList)
{
OT_UserRole model = new OT_UserRole();
model.Id = Guid.NewGuid();
model.RoleId = roleId;
model.UserId = userModel.Id;
model.IsDelete = (byte)EDataStatus.Normal;
db.OT_UserRole.Add(model);
}
return(Result.SaveChangesResult(db, transaction).ResultKey);
}
}
}
/// <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="listUserRoleId">用户角色关系编号集合</param>
/// <returns>修改结构</returns>
public CustomResult StartPassword(List <Guid> listUserId)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
using (TransactionScope transaction = new TransactionScope())
{
int flag = 0;
string password = ConfigurationManager.AppSettings["StartPassword"];
foreach (Guid userId in listUserId)
{
var list = db.OT_User.Where(a => a.Id == userId & a.IsDelete == (byte)EUserStatus.Normal).ToList();
if (list != null && list.Count > 0)
{
foreach (var i in list)
{
i.Password = password;
}
flag++;
}
}
if (flag > 0)
{
CustomResult pReturnValue = new CustomResult();
if (db.SaveChanges() > 0)
{
transaction.Complete();
pReturnValue.ResultKey = (byte)EResult.Succeed;
pReturnValue.ResultValue = Wttech.DataSubmitted.Common.Resources.TipInfo.StartPassword;
}
else
{
pReturnValue.ResultKey = (byte)EResult.Fail;//程序已经使用多处，所有不变动
pReturnValue.ResultValue = Wttech.DataSubmitted.Common.Resources.TipInfo.StartPassword;
}
return(pReturnValue);
}
else
{
CustomResult pReturnValue = new CustomResult();
pReturnValue.ResultKey = (byte)EResult.Fail;//程序已经使用多处，所有不变动
pReturnValue.ResultValue = Wttech.DataSubmitted.Common.Resources.TipInfo.Inexist;
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>
/// <param name="db">提交数据库对象</param>
/// <returns>提交结果</returns>
public static CustomResult SaveUpdateResult(DataSubmittedEntities db)
{
CustomResult pReturnValue = new CustomResult();
try
{
db.SaveChanges();
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="dt">统计日期</param>
private void InsertNull(DateTime dt)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
int ptemp = 24;
if (DateTime.Now.Date == dt)
{
ptemp = DateTime.Now.Hour;
}
List <RP_HourAADT> pList = new List <RP_HourAADT>();
for (int i = 0; i < ptemp; i++)
{
List <RP_HourAADT> pTemp = db.RP_HourAADT.Where(s => s.CalcuTime == dt && s.HourPer == (byte)i).ToList();
//如果数据不存在，才补充
if (pTemp.Count <= 0)
{
RP_HourAADT pHour = new RP_HourAADT();
pHour.Id = Guid.NewGuid();
pHour.Mjqd_EnIn = 0;
pHour.Mjqd_EnOut = 0;
pHour.Dyf_EnOut = 0;
pHour.Dyf_ExIn = 0;
pHour.Mjqx_EnOut = 0;
pHour.Mjqx_ExIn = 0;
pHour.Cy_EnOut = 0;
pHour.Cy_ExIn = 0;
pHour.State = "0";
pHour.CalcuTime = dt;
pHour.CrtDate = DateTime.Now;
pHour.HourPer = (byte)i;
pList.Add(pHour);
}
}
using (TransactionScope transac = new TransactionScope())
{
db.RP_HourAADT.AddRange(pList);
if (pList.Count > 0)
{
db.SaveChanges();
transac.Complete();
}
}
}
}
/// <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>
/// 更改Excel工作簿内容
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para)
{
//获取工作簿
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
//设置日期
SetReportDate(sheet, 0, 13, para.StartTime.Value, para.ReportType);
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, pInfo.EnGre.ToString()); //绿色通道数
SetValue(sheet, 5, 5, pInfo.ExSmaCar.ToString()); //小型客车
SetValue(sheet, 5, 6, pInfo.ExOthCar.ToString()); //其他客车
SetValue(sheet, 5, 7, pInfo.ExTruk.ToString()); //高速入境货车数
SetValue(sheet, 5, 8, 0); //pInfo.EnGre.ToString());//绿色通道数
//普通
SetValue(sheet, 5, 9, 0); //小型客车
SetValue(sheet, 5, 10, 0); //其他客车
SetValue(sheet, 5, 11, 0); //高速入境货车数
SetValue(sheet, 5, 12, 0); //绿色通道数
SetValue(sheet, 5, 13, 0); //小型客车
SetValue(sheet, 5, 14, 0); //其他客车
SetValue(sheet, 5, 15, 0); //高速入境货车数
SetValue(sheet, 5, 16, 0); //绿色通道数
}
}
}
return(readworkbook);
}
/// <summary>
/// 补充数据
/// </summary>
/// <param name="dt">统计日期</param>
private void InsertNull(DateTime dt)
{
HDayStatisticalReport pHday = new HDayStatisticalReport("");
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
RP_HDayAADT info = new RP_HDayAADT();
info.Id = Guid.NewGuid();
info.CrtDate = DateTime.Now;
info.State = "0";
info.CalcuTime = dt;
//杨村站流量
info.YC = 0;
//宜兴埠东站流量
info.YXBD = 0;
//宜兴埠西站流量
info.YXBX = 0;
//金钟路站流量
info.JZL = 0;
//机场站流量
info.JC = 0;
//空港经济区站流量
info.KG = 0;
//塘沽西站流量
info.TGX = 0;
//塘沽西分站流量
info.TGXF = 0;
//塘沽北站流量
info.TGB = 0;
//表12出口流量
info.Out = 0;
//去年同期
info.SameSum = pHday.GetSameSum(dt);
using (TransactionScope transac = new TransactionScope())
{
db.RP_HDayAADT.Add(info);
db.SaveChanges();
transac.Complete();
}
}
}
/// <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>
/// 获取数据
/// </summary>
public void UpdateData(DateTime pDt, int pHourPer)
{
try
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
string startTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
SystemLog.GetInstance().Log.Info(string.Format("{0}：开始获取中间表数据", startTime));
db.SP_StaDataSource(pHourPer, pDt);
string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
SystemLog.GetInstance().Log.Info(string.Format("{0}：结束获取中间表数据", endTime));
SystemLog.GetInstance().Log.Info(string.Format("统计中间表数据耗时{0}秒", (DateTime.Parse(endTime) - DateTime.Parse(startTime)).TotalSeconds));
this.State = 1;
if (State == 1)
{
int timeHour;
DateTime dt = pDt.AddHours(-1);
if (pHourPer == 0)
{
timeHour = 23;
}
else
{
if (pHourPer == 1)
{
timeHour = 0;
}
else
{
timeHour = pHourPer - 1;
}
}
this.Notify(new ThreadParameter(dt, timeHour));
}
}
}
catch (Exception ex)
{
SystemLog.GetInstance().Log.Info(ex.Message);
}
}
/// <summary>
/// 预测导出--如果需要将入出口数据进行区分，则分别放在两个list的集合中，若不需区分，则将数据放入list1中，list2为空即可
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <param name="list1"></param>
/// <param name="list2"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para, List <Common.ViewModels.IReportViewModel> list1, List <Common.ViewModels.IReportViewModel> list2)
{
//获取工作簿
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
//设置日期
SetReportDate(sheet, 0, 13, DateTime.Parse(DateTime.Now.ToShortDateString()), para.ReportType);
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
if (list1 != null && list1.Count > 0)
{
ForecastEnExViewModel pInfo = list1.First() as ForecastEnExViewModel;
//高速
SetValue(sheet, 5, 1, pInfo.EnSmaCar.ToString()); //小型客车
SetValue(sheet, 5, 2, pInfo.EnOthCar.ToString()); //其他客车
SetValue(sheet, 5, 3, pInfo.EnTruk.ToString()); //货车数
SetValue(sheet, 5, 4, pInfo.EnGre.ToString()); //绿色通道数
SetValue(sheet, 5, 5, pInfo.ExSmaCar.ToString()); //小型客车
SetValue(sheet, 5, 6, pInfo.ExOthCar.ToString()); //其他客车
SetValue(sheet, 5, 7, pInfo.ExTruk.ToString()); //货车数
SetValue(sheet, 5, 8, 0); //pInfo.EnGre.ToString());//绿色通道数
//普通
SetValue(sheet, 5, 9, 0); //小型客车
SetValue(sheet, 5, 10, 0); //其他客车
SetValue(sheet, 5, 11, 0); //高速入境货车数
SetValue(sheet, 5, 12, 0); //绿色通道数
SetValue(sheet, 5, 13, 0); //小型客车
SetValue(sheet, 5, 14, 0); //其他客车
SetValue(sheet, 5, 15, 0); //高速入境货车数
SetValue(sheet, 5, 16, 0); //绿色通道数
}
}
}
return(readworkbook);
}
/// <summary>
/// 根据报表配置ID读取（假期和非假期表都存在HolidayConfig表）
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public OT_HDayConfig GetById(int id)
{
OT_HDayConfig pHolidayConfig = null;
try
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
List <OT_HDayConfig> list = db.OT_HDayConfig.Where(s => s.Id == id).ToList();
if (list.Count > 0)
{
pHolidayConfig = list[0];
}
}
}
catch (Exception ex)
{
SystemLog.GetInstance().Error(ex);
pHolidayConfig = null;
}
return(pHolidayConfig);
}
/// <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="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="dt">统计日期</param>
/// <param name="stationtype">统计类型</param>
private void InsertNull(DateTime dt, int stationtype)
{
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
List <RP_Daily> pDailyList = new List <RP_Daily>();
for (int i = 0; i < 4; i++)
{
//0小型客车，1其他客车，2货车（不包含绿通），3绿通
//每个站只有四条合计数据，每天数据表里一共只有16条
List <RP_Daily> pTemp = db.RP_Daily.Where(s => s.CalcuTime == dt && s.VehType == i && s.StaType == stationtype).ToList();
//如果数据不存在，才补充
if (pTemp.Count <= 0)
{
RP_Daily pDaily = new RP_Daily();
pDaily.Id = Guid.NewGuid();
pDaily.CrtDate = DateTime.Now;
pDaily.VehType = i;//车辆类型
pDaily.StaType = stationtype;
pDaily.State = 0;
pDaily.OutNum = 0;
pDaily.InNum = 0;
pDaily.ChagFee = 0;
pDaily.CalcuTime = DateTime.Parse(dt.ToShortDateString());
pDailyList.Add(pDaily);
}
}
using (TransactionScope transac = new TransactionScope())
{
db.RP_Daily.AddRange(pDailyList);
if (pDailyList.Count > 0)
{
db.SaveChanges();
transac.Complete();
}
}
}
}
