/// <summary>
/// 保存问题
/// </summary>
/// <param name="list_ad"></param>
/// <returns></returns>
public bool SaveAlarm(alarmdetail ad)
{
using (sartas3 db = new sartas3())
{
try
{
alarmdetail old = db.alarmdetail.FirstOrDefault(n => n.AlarmDetail_ID == ad.AlarmDetail_ID);
if (old != null)
{
old.HandleResult = ad.HandleResult;
old.OpUser = ad.OpUser;
}
else
{
ad.AlarmDetail_ID = Guid.NewGuid().ToString();
ad.AlarmLevel = 1;
ad.AlgResult = 0;
db.alarmdetail.Add(ad);
}
db.SaveChanges();
return(true);
}
catch (Exception e) { throw e; }
}
}
public bool TrainPassed(DateTime TrainComeDate, int LineID)
{
using (sartas3 db = new sartas3())
{
statistics_image old = db.statistics_image.FirstOrDefault(n => n.LineID == LineID && n.TrainComeDate == TrainComeDate);
return(old != null ? true : false);
}
}
/// <summary>
/// 获取线路文件夹名称
/// </summary>
/// <param name="lid"></param>
/// <returns></returns>
public string GetLineFolderName(int lid)
{
using (sartas3 db = new sartas3())
{
line l = db.line.Where(n => n.ID == lid).FirstOrDefault();
return(l != null ? l.LineCode : null);
}
}
/// <summary>
/// 获取所有线路
/// </summary>
/// <returns></returns>
public List <line> GetAllLines()
{
using (sartas3 db = new sartas3())
{
List <line> ls = db.line.ToList();
return(ls);
}
}
/// <summary>
/// 查询列车
/// </summary>
/// <param name="dt"></param>
/// <param name="LineID"></param>
/// <returns></returns>
public train GetTrain(DateTime dt, int LineID)
{
using (sartas3 db = new sartas3())
{
train old = db.train.FirstOrDefault(n => n.Train_ComeDate == dt && n.Line_ID == LineID);
return(old);
}
}
/// <summary>
/// 根据顺位号查询某一节车厢[上一节，下一节]
/// </summary>
/// <param name="TrainID"></param>
/// <param name="TrainComeDate"></param>
/// <param name="OrderNum"></param>
/// <returns></returns>
public View_TrainDetail GetCar(string TrainID, DateTime TrainComeDate, int OrderNum)
{
try
{
using (sartas3 db = new sartas3())
{
//首先根据时间找到指定的分表
string TableName = "traindetail";
if (TrainComeDate < DateTime.Now)
{
for (DateTime i = TrainComeDate; i < DateTime.Now; i = i.AddMonths(1))
{
TableName = "traindetail_" + i.ToString("yyyyMM");
string SQL_BeSureTableExist = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='" + Config.DB_SCHEMA + "' AND TABLE_NAME = '" + TableName + "'";
string resName = db.Database.SqlQuery <string>(SQL_BeSureTableExist).FirstOrDefault();
if (!string.IsNullOrWhiteSpace(resName))
{
break;
}
else
{
TableName = "traindetail";
}
}
}
//根据分表名称进行查询
string SQL_Query = @"SELECT * FROM " + Config.DB_SCHEMA + "." + TableName + @" td WHERE td.Train_ID = '" + TrainID + "' AND td.TrainDetail_OrderNo='" + OrderNum + "'";
traindetail td = db.Database.SqlQuery <traindetail>(SQL_Query).SingleOrDefault();
if (td != null)
{
View_TrainDetail dtd = new View_TrainDetail();
dtd.Train_ID = td.Train_ID;
dtd.TrainDetail_ID = td.TrainDetail_ID;
dtd.TrainDetail_No = td.TrainDetail_No;
dtd.TrainDetail_OrderNo = td.TrainDetail_OrderNo;
dtd.vehicletype = td.vehicletype;
dtd.AlarmLevel = td.AlarmLevel;
string SQL_QueryTrain = @"SELECT * FROM " + Config.DB_SCHEMA + ".train t WHERE t.Train_ID = '" + TrainID + "';";
train t = db.Database.SqlQuery <train>(SQL_QueryTrain).SingleOrDefault();
if (t != null)
{
dtd.TrainComeDate = Convert.ToDateTime(t.Train_ComeDate);
dtd.TrainNo = t.Train_No;
dtd.LineID = Convert.ToInt16(t.Line_ID);
}
return(dtd);
}
else
{
return(null);
}
}
}
catch (Exception ex)
{
return(null);
}
}
public void Set_SysCode(sys_code c)
{
using (sartas3 db = new sartas3())
{
//sys_code tmp = db.sys_code.SingleOrDefault(n => n.Code_ID == c.Code_ID);
db.sys_code.Attach(c);
db.Entry(c).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
public List <hotwheel_axle> Get_HotWheel_Axle(string CarID)
{
try
{
using (sartas3 db = new sartas3())
{
return(db.hotwheel_axle.Where(n => n.TrainDetail_ID.ToString() == CarID).OrderBy(n => n.Axle_OrderNum).ToList());
}
}
catch (Exception ex) { throw ex; }
}
/// <summary>
/// 只更新备注字段
/// </summary>
/// <param name="c"></param>
public void Set_SysCodeRemark(sys_code c)
{
using (sartas3 db = new sartas3())
{
sys_code tmp = db.sys_code.SingleOrDefault(n => n.Code_ID == c.Code_ID);
if (tmp != null)
{
tmp.Remark = c.Remark;
db.SaveChanges();
}
}
}
/// <summary>
/// 查询最新列车信息
/// </summary>
/// <param name="LineID">线路ID</param>
/// <param name="TrainComeDate"></param>
/// <returns></returns>
public train Get_LatestTrain_ByLineID(int LineID, DateTime TrainComeDate)
{
try
{
using (sartas3 db = new sartas3())
{
train t = db.train.Where(n => n.Line_ID == LineID && n.Train_ComeDate >= TrainComeDate).OrderByDescending(n => n.Train_ComeDate).FirstOrDefault();
return(t);
}
}
catch (Exception ex) { throw ex; }
}
public List <account_t_station> Get_All_Station()
{
try
{
using (sartas3 db = new sartas3())
{
List <account_t_station> list = db.account_t_station.OrderBy(n => n.OrderNum).ToList();
return(list);
}
}
catch (Exception ex) { throw ex; }
}
public account_t_station Get_Station_ByIP(string IP)
{
try
{
using (sartas3 db = new sartas3())
{
account_t_station s = db.account_t_station.Where(n => n.ConnectString == IP).FirstOrDefault();
return(s);
}
}
catch (Exception ex) { throw ex; }
}
/// <summary>
/// 查询车站信息
/// </summary>
/// <param name="StationID"></param>
/// <returns></returns>
public account_t_station Get_Station(string StationID)
{
try
{
using (sartas3 db = new sartas3())
{
account_t_station s = db.account_t_station.Where(n => n.StationID == StationID).FirstOrDefault();
return(s);
}
}
catch (Exception ex) { throw ex; }
}
public account_t_user Get_User(string UserID)
{
try
{
using (sartas3 db = new sartas3())
{
account_t_user user = db.account_t_user.Where(n => n.UserID == UserID).FirstOrDefault();
return(user);
}
}
catch (Exception ex) { throw ex; }
}
public account_t_user Get_User(string UserName, string Password)
{
try
{
using (sartas3 db = new sartas3())
{
account_t_user user = db.account_t_user.Where(n => n.UserName == UserName && n.Password == Password).FirstOrDefault();
return(user);
}
}
catch (Exception ex) { throw ex; }
}
/// <summary>
/// 查询【当前月份】的列车车厢
/// 科峰实时导出模块使用
/// </summary>
/// <param name="TrainID">列车ID</param>
/// <param name="TrainAlarmCount">列车报警个数</param>
/// <returns></returns>
public List <traindetail> Get_Cars_CurrentMonth_ByTrainID(string TrainID, out int TrainAlarmCount)
{
using (sartas3 db = new sartas3())
{
Guid tmp = new Guid(TrainID);
List <traindetail> list_td = db.traindetail.Where(n => n.Train_ID == tmp).OrderBy(n => n.TrainDetail_OrderNo).ToList();
TrainAlarmCount = db.alarmdetail.Where(n => n.Train_ID == TrainID &&
n.AlarmLevel != null &&
n.AlarmLevel != 0 &&
n.AlarmLevel <= 3).ToList().Count;
return(list_td);
}
}
public List <account_t_function> Get_Function(string RoleID)
{
try
{
using (sartas3 db = new sartas3())
{
var q = from f_r in db.account_r_role_function
join f in db.account_t_function on f_r.FunctionID equals f.ID
where f_r.RoleID == RoleID
select f;
return(q.ToList());
}
}
catch (Exception ex) { throw ex; }
}
public string Get_SysMapCodeValue(string MapCode)
{
using (sartas3 db = new sartas3())
{
sys_codemap map = db.sys_codemap.SingleOrDefault(n => n.Code == MapCode);
if (map != null)
{
return(map.Value);
}
else
{
return(null);
}
}
}
public static void Error(string ApplicationName, string FunctionName, string Detail)
{
using (sartas3 db = new sartas3())
{
sys_log l = new sys_log();
l.LogID = Guid.NewGuid().ToString();
l.ApplicationName = ApplicationName;
l.FunctionName = FunctionName;
l.Detail = Detail;
l.OpTime = DateTime.Now;
l.State = "异常";
db.sys_log.Add(l);
db.SaveChanges();
}
}
/// <summary>
/// 通过MapCode，查询系统字典信息
/// </summary>
/// <param name="MapCode"></param>
/// <returns></returns>
public List <sys_code> Get_SysCodeByMapCode(string MapCode)
{
using (sartas3 db = new sartas3())
{
sys_codemap map = db.sys_codemap.SingleOrDefault(n => n.Code == MapCode);
if (map != null)
{
List <sys_code> res = db.sys_code.Where(n => n.CodeMap_ID == map.CodeMap_ID).ToList();
return(res);
}
else
{
return(null);
}
}
}
public List <sys_code> Get_AlarmTypeList()
{
using (sartas3 db = new sartas3())
{
sys_codemap map = db.sys_codemap.SingleOrDefault(n => n.Code == "AlarmType");
if (map != null)
{
List <sys_code> res = db.sys_code.Where(n => n.CodeMap_ID == map.CodeMap_ID && n.Remark != "smart").ToList();
return(res);
}
else
{
return(null);
}
}
}
/// <summary>
/// 查询当前列车所有报警
/// </summary>
/// <param name="TrainID">列车ID</param>
/// <param name="DisplayAlarmLevel">报警显示等级</param>
/// <returns></returns>
public List <alarmdetail> GetAlarmByTrainID(string TrainID)
{
try
{
int DisplayAlarmLevel = 5;
try
{
DisplayAlarmLevel = int.Parse(Config.DisplayAlarmLevel);
}
catch { DisplayAlarmLevel = 5; }
using (sartas3 db = new sartas3())
{
return(db.alarmdetail.Where(n => n.Train_ID == TrainID && n.AlgResult == 0 && n.AlarmLevel <= DisplayAlarmLevel).ToList());
}
}
catch { return(null); }
}
/// <summary>
/// 更新列车报警等级
/// </summary>
/// <param name="TrainID"></param>
public void Update_TrainAlarmLevel(string TrainID)
{
try
{
using (sartas3 db = new sartas3())
{
Guid tmp = new Guid(TrainID);
var q = db.traindetail.Where(n => n.Train_ID == tmp && n.AlarmLevel != "0" && n.AlarmLevel != "" && n.AlarmLevel != null).Min(n => n.AlarmLevel);
train oldt = db.train.Where(n => n.Train_ID == tmp).FirstOrDefault();
if (oldt != null)
{
oldt.AlarmLevel = q;
}
db.SaveChanges();
}
}
catch (Exception ex) { throw ex; }
}
/// <summary>
/// 更新车厢报警等级
/// </summary>
/// <param name="TrainDetailID"></param>
public void Update_CarAlarmLevel(string TrainDetailID)
{
try
{
using (sartas3 db = new sartas3())
{
var q = db.alarmdetail.Where(n => n.TrainDetail_ID == TrainDetailID && n.AlarmLevel != 0 && n.AlarmLevel != null).Min(n => n.AlarmLevel);
Guid g = new Guid(TrainDetailID);
traindetail oldtd = db.traindetail.Where(n => n.TrainDetail_ID == g).FirstOrDefault();
if (oldtd != null)
{
oldtd.AlarmLevel = q.ToString();
}
db.SaveChanges();
}
}
catch (Exception ex) { throw ex; }
}
/// <summary>
/// 获取列车报警个数
/// </summary>
/// <param name="TrainID"></param>
/// <returns></returns>
public string GetAlarmCount_ByTrainID(string TrainID)
{
try
{
using (sartas3 db = new sartas3())
{
int tmp = db.alarmdetail.Where(n => n.Train_ID == TrainID).ToList().Count;
if (tmp == 0)
{
return("");
}
else
{
return(tmp.ToString());
}
}
}
catch { return(""); }
}
/// <summary>
/// 查询角色信息
/// </summary>
/// <param name="UserID">用户ID</param>
/// <returns></returns>
public account_t_role Get_Role(string UserID)
{
try
{
using (sartas3 db = new sartas3())
{
account_r_users_roles user_role = db.account_r_users_roles.Where(n => n.UserID == UserID).FirstOrDefault();
if (user_role != null)
{
account_t_role r = db.account_t_role.FirstOrDefault(n => n.RoleID == user_role.RoleID);
return(r);
}
else
{
return(null);
}
}
}
catch (Exception ex) { throw ex; }
}
/// <summary>
/// 终于TMD写完了，但是Enumerable的执行效率很低
/// </summary>
/// <param name="st"></param>
/// <param name="ed"></param>
/// <param name="LineID"></param>
/// <param name="TrainNo"></param>
/// <param name="TrainDetailNo"></param>
/// <param name="HasProblem"></param>
/// <param name="ProblemTypes"></param>
/// <param name="TrainCount"></param>
/// <returns></returns>
public List <View_Train> GetTrainList(DateTime st, DateTime ed, string LineID, string TrainNo, string TrainDetailNo, string HasProblem, List <string> ProblemTypes, out int TrainCount)
{
using (sartas3 db = new sartas3())
{
List <View_Train> res = new List <View_Train>();
var query = from t in db.train.AsEnumerable()
//左联必须使用into语句
join a in db.alarmdetail on t.Train_ID.ToString() equals a.Train_ID into ad
from adi in ad.DefaultIfEmpty(
new alarmdetail
{
//给出为空的默认值
AlarmDetail_ID = null,
AlarmLevel = 0,
AlgResult = 0,
FileContent = null,
FileName = null,
HandleResult = null,
InsertTime = DateTime.Now,
Train_ID = "",
OpTime = DateTime.Now,
OpUser = "",
ProblemType = "",
Side = "",
Source = "",
TrainDetail_ID = ""
}
)
select new View_Train
{
//AlarmCount = null,
//LineName = null,
ProblemType = adi.ProblemType,
AlarmLevel = t.AlarmLevel,
Line_ID = t.Line_ID,
TelexCode = t.TelexCode,
Train_ComeDate = t.Train_ComeDate,
Train_Count = t.Train_Count,
Train_ID = t.Train_ID,
Train_No = t.Train_No,
Train_Speed = t.Train_Speed,
Train_type = t.Train_type
//source = t.source,
//Train_Real = t.Train_Real,
//Train_WorkFlag = t.Train_WorkFlag
};
if (st != null && ed != null)
{
query = query.Where(t => t.Train_ComeDate >= st && t.Train_ComeDate <= ed);
}
if (!string.IsNullOrWhiteSpace(LineID))
{
int line = Convert.ToInt16(LineID);
query = query.Where(t => t.Line_ID == line);
}
if (!string.IsNullOrWhiteSpace(TrainNo))
{
query = query.Where(t => t.Train_No.Contains(TrainNo));
}
if (!string.IsNullOrWhiteSpace(HasProblem))
{
if (ProblemTypes != null && ProblemTypes.Count > 0)
{
query = query.Where(t => t.AlarmLevel != null && t.AlarmLevel != "0" && t.AlarmLevel != "");
//使用表达式扩展
var where = PredicateBuilder.False <View_Train>();
foreach (string p in ProblemTypes)
{
//query = query.Where(t => t.ProblemType.Contains(p));
where = where.Or(n => n.ProblemType == p);
}
query = query.Where(where.Compile());
}
else
{
query = query.Where(t => t.AlarmLevel == "0" || t.AlarmLevel == null || t.AlarmLevel == "");
}
}
//去重+排序
query = query.DistinctBy(t => t.Train_ID).OrderByDescending(t => t.Train_ComeDate);
//车号查询
if (!string.IsNullOrEmpty(TrainDetailNo) && query.Count() > 0)
{
//此处必须ToList(),将上一次查询关闭
//foreach (View_Train item in query.ToList())
//AsEnumerable将表转换为IEnumerable类型即可
foreach (View_Train item in query)
{
if (st.Month >= DateTime.Now.Month)
{
//当前月查询表【traindetail】
string SQL_QueryTrainDetailBy_tdNo = @"SELECT 1 FROM " + Config.DB_SCHEMA + ".traindetail td WHERE td.Train_ID='" + item.Train_ID + "' AND td.TrainDetail_No like '%" + TrainDetailNo + "%';";
string QueryRes = db.Database.SqlQuery <string>(SQL_QueryTrainDetailBy_tdNo).FirstOrDefault();
if (QueryRes != null)
{
res.Add(item);
}
}
else
{
//历史月份查询分表【traindetail_月份】
for (DateTime i = st; i.Month <= ed.Month; i = i.AddMonths(1))
{
string SQL_BeSureTableExist = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='" + Config.DB_SCHEMA + "' AND TABLE_NAME = 'traindetail_" + i.ToString("yyyyMM") + "'";
string tableName = db.Database.SqlQuery <string>(SQL_BeSureTableExist).FirstOrDefault();
if (tableName != null)
{
string SQL_QueryTrainDetailBy_tdNo = @"SELECT 1 FROM " + Config.DB_SCHEMA + "." + tableName + " td WHERE td.Train_ID='" + item.Train_ID + "' AND td.TrainDetail_No like '%" + TrainDetailNo + "%';";
string QueryRes = db.Database.SqlQuery <string>(SQL_QueryTrainDetailBy_tdNo).FirstOrDefault();
if (QueryRes != null)
{
res.Add(item);
}
}
else
{
continue;
}
}
if (ed.Month >= DateTime.Now.Month)
{
//查询时间直至本月，则需要额外查询表【traindetail】
string SQL_QueryTrainDetailBy_tdNo = @"SELECT 1 FROM " + Config.DB_SCHEMA + ".traindetail td WHERE td.Train_ID='" + item.Train_ID + "' AND td.TrainDetail_No like '%" + TrainDetailNo + "%';";
string QueryRes = db.Database.SqlQuery <string>(SQL_QueryTrainDetailBy_tdNo).FirstOrDefault();
if (QueryRes != null)
{
res.Add(item);
}
}
}
}
//车号查询
TrainCount = res.Count;
return(res);
}
else
{
//无车号查询
res = query.ToList();
TrainCount = res.Count;
return(res);
}
}
}
/// <summary>
/// 保存问题（修改和新增）
/// 【WebService 使用】【不根据id进行判别】
/// </summary>
/// <returns></returns>
public bool SaveAlarms(List <alarmdetail> list_ad, int LineID, DateTime TrainComeDate, string TelexCode, int TrainDetailOrderNum)
{
try
{
//由于入库导致的
Thread.Sleep(new TimeSpan(0, Config.DelayTime4Save, 0));
if (list_ad != null && list_ad.Count > 0)
{
using (sartas3 db = new sartas3())
{
train t = db.train.SingleOrDefault(n => n.Train_ComeDate == TrainComeDate && n.Line_ID == LineID && n.TelexCode == TelexCode);
if (t != null)
{
//首先根据时间找到指定的分表
string TableName = "traindetail";
if (TrainComeDate.Month < DateTime.Now.Month)
{
for (DateTime i = TrainComeDate; i.Month < DateTime.Now.Month; i = i.AddMonths(1))
{
TableName = "traindetail_" + i.ToString("MM");
string SQL_BeSureTableExist = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='" + Config.DB_SCHEMA + "' AND TABLE_NAME = 'traindetail_" + i.ToString("yyyyMM") + "'";
string resName = db.Database.SqlQuery <string>(SQL_BeSureTableExist).FirstOrDefault();
if (!string.IsNullOrWhiteSpace(resName))
{
break;
}
else
{
TableName = "traindetail";
}
}
}
//根据分表名称进行查询
string SQL_Query = @"SELECT * FROM " + Config.DB_SCHEMA + "." + TableName + @" td WHERE td.Train_ID = '" + t.Train_ID + "' AND td.TrainDetail_OrderNo='" + TrainDetailOrderNum + "' ORDER BY td.TrainDetail_OrderNo";
traindetail td = db.Database.SqlQuery <traindetail>(SQL_Query).SingleOrDefault();
if (td != null)
{
foreach (alarmdetail ad in list_ad)
{
if (!string.IsNullOrWhiteSpace(ad.AlarmDetail_ID))
{
alarmdetail oldone = db.alarmdetail.SingleOrDefault(n => n.AlarmDetail_ID == ad.AlarmDetail_ID);
if (oldone != null)
{
//修改
oldone.HandleResult = ad.HandleResult;
oldone.OpTime = DateTime.Now;
}
else
{
//新增
ad.Train_ID = t.Train_ID.ToString();
ad.TrainDetail_ID = td.TrainDetail_ID.ToString();
ad.HandleResult = "0";//新增
ad.AlarmLevel = 1;
db.alarmdetail.Add(ad);
}
db.SaveChanges();
}
else
{
throw new Exception("AlarmDetailID为Null");
}
}
//更新车厢报警等级
new BLL_Car().Update_CarAlarmLevel(td.TrainDetail_ID.ToString());
}
else
{
throw new Exception("未找到该车车厢");
}
//更新列车报警等级
new BLL_Train().Update_TrainAlarmLevel(t.Train_ID.ToString());
}
else
{
throw new Exception("未找到该列车");
}
}
return(true);
}
else
{
throw new Exception("AlarmDetail为空");
}
}
catch (Exception ex) { throw ex; }
}
