/// <summary>
/// 更新记录
/// </summary>
/// <param name="regs"></param>
/// <param name="site"></param>
/// <param name="macs"></param>
/// <param name="sens"></param>
/// <param name="date"></param>
/// <param name="conclusion"></param>
/// <param name="inserted"></param>
/// <returns></returns>
public string AddRecordDAL(string regs, string site, string macs, string sens, string date, string conclusion, List <Guid> gList)
{
DateTime inserted = DateTime.Now; //获取当前系统的时间
List <Pictures> picList = new List <Pictures>(); //存放图片信息
using (var db = new MachineFailureEntities())
{
using (var tran = db.Database.BeginTransaction()) //开启事物
{
try
{
//查询记录
var record = db.Records.FirstOrDefault(e => sens.Equals(e.FaultTypeID.ToString()) && date.Equals(e.Timestamp) && conclusion.Equals(e.Conclusion));
if (record != null) //判断插入的记录是否已经存在
{
return("exsit");
}
//插入记录
var recordID = Guid.NewGuid(); //生成记录的ID
db.Records.Add(new Records()
{
RecordID = recordID,
FaultTypeID = Guid.Parse(sens),
Conclusion = conclusion,
Timestamp = date,
Inserted = inserted
});
//插入图片
foreach (var g in gList)
{
picList.Add(new Pictures()
{
PictureID = g, RecordID = recordID
});
}
db.Pictures.AddRange(picList);
//更新故障类型的时间
UpdateTime.UpdateFaultTypes(db, Guid.Parse(sens), inserted);
//更新机组时间
UpdateTime.UpdateMachines(db, Guid.Parse(macs), inserted);
//更新站点时间
UpdateTime.UpdateSites(db, Guid.Parse(site), inserted);
//更新大区的时间
UpdateTime.UpdateRegionals(db, Guid.Parse(regs), inserted);
db.SaveChanges();
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
Debug.WriteLine(e);
return("false");
}
}
}
return("true");
}
/// <summary>
/// 插入站点
/// </summary>
/// <param name="id"></param>
/// <param name="name"></param>
/// <param name="addr"></param>
/// <returns></returns>
public string AddSiteDAL(Guid regsID, string name)
{
using (var db = new MachineFailureEntities())
{
using (var tran = db.Database.BeginTransaction())
{
try
{
//插入之前先查询是否存在
var site = db.Sites.FirstOrDefault(e => name.Equals(e.Name) && regsID.Equals(e.CustomerID));
if (site != null)
{
return("This name already exsit");
}
//开始插入
var date = DateTime.Now;
db.Sites.Add(new Sites()
{
SiteID = Guid.NewGuid(), Name = name, CustomerID = regsID, Updated = date
});
UpdateTime.UpdateRegionals(db, regsID, date);
db.SaveChanges();
tran.Commit();
}
catch
{
tran.Rollback();
return("Add filded");
}
}
}
return("Add success");
}
public List <ViewInfo> GetSites(string name)
{
using (var db = new MachineFailureEntities())
{
string sql = string.Format(@"select last_site.[SiteID] Id, last_site.[Name],Convert(nvarchar(19),new_site.[maxTime],20) Updated from
(select MAX(site.[Updated]) maxTime from
(
select sit.* from [FaultTypes] ft
left join Machines mac on ft.[MachineID] = mac.MachineID
left join [Sites] sit on mac.SiteID = sit.SiteID
where ft.Name = '{0}'
) site
group by site.[Name]
) new_site
left join [Sites] last_site on last_site.[Updated] = new_site.[maxTime]" , name);
var sites = db.Database.SqlQuery <ViewInfo>(sql);
int totalRecord = sites.Count();
if (totalRecord != 0)
{
//获取所有页
return(sites.ToList());
}
else
{
return(new List <ViewInfo>());
}
}
}
/// <summary>
/// 添加大区
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public string AddRegionalDAL(string name)
{
using (var db = new MachineFailureEntities())
{
using (var tran = db.Database.BeginTransaction())
{
try
{
//插入之前先查询是否存在
var reg = db.Customers.FirstOrDefault(e => name.Equals(e.Name));
if (reg != null)
{
return("This name already exist");
}
//开始插入
db.Customers.Add(new Customers()
{
CustomerID = Guid.NewGuid(), Name = name, Updated = DateTime.Now
});
db.SaveChanges();
tran.Commit();
}
catch
{
tran.Rollback();
return("Add failed");
}
}
}
return("Add success");
}
/// <summary>
/// 获取binarybase的根目录
/// </summary>
/// <returns></returns>
public Option FilePathDAL()
{
using (var db = new MachineFailureEntities())
{
return(db.Option.FirstOrDefault(elem => elem.ID.Equals("BinaryBase")));
}
}
/// <summary>
/// 插入故障类型
/// </summary>
/// <param name="id"></param>
/// <param name="name"></param>
/// <param name="addr"></param>
/// <returns></returns>
public string AddFaultTypeDAL(Guid macID, Guid siteID, Guid regsID, string name)
{
using (var db = new MachineFailureEntities())
{
using (var tran = db.Database.BeginTransaction())
{
try
{
//插入之前先查询是否存在
var ft = db.FaultTypes.FirstOrDefault(e => name.Equals(e.Name) && macID.Equals(e.MachineID));
if (ft != null)
{
return("This name already exsit");
}
//开始插入
var date = DateTime.Now;
db.FaultTypes.Add(new FaultTypes()
{
FaultTypeID = Guid.NewGuid(), Name = name, MachineID = macID, Updated = date
});
UpdateTime.UpdateMachines(db, macID, date);
UpdateTime.UpdateSites(db, siteID, date);
UpdateTime.UpdateRegionals(db, regsID, date);
db.SaveChanges();
tran.Commit();
}
catch
{
tran.Rollback();
return("Add failed");
}
}
}
return("Add success");
}
/// <summary>
/// 更新大区的时间
/// </summary>
/// <param name="db"></param>
/// <param name="id"></param>
/// <param name="inserted"></param>
public static void UpdateRegionals(MachineFailureEntities db, Guid id, DateTime inserted)
{
var reg = db.Customers.FirstOrDefault(e => id.Equals(e.CustomerID));
if (reg != null)
{
reg.Updated = inserted;
}
}
/// <summary>
/// 更新站点时间
/// </summary>
/// <param name="db"></param>
/// <param name="id"></param>
/// <param name="inserted"></param>
public static void UpdateSites(MachineFailureEntities db, Guid id, DateTime inserted)
{
var sit = db.Sites.FirstOrDefault(e => id.Equals(e.SiteID));
if (sit != null)
{
sit.Updated = inserted;
}
}
/// <summary>
/// 更新机组时间
/// </summary>
/// <param name="db"></param>
/// <param name="id"></param>
/// <param name="inserted"></param>
public static void UpdateMachines(MachineFailureEntities db, Guid id, DateTime inserted)
{
var mac = db.Machines.FirstOrDefault(e => id.Equals(e.MachineID));
if (mac != null)
{
mac.Updated = inserted;
}
}
/// <summary>
/// 更新故障类型的时间
/// </summary>
/// <param name="db"></param>
/// <param name="id"></param>
/// <param name="inserted"></param>
public static void UpdateFaultTypes(MachineFailureEntities db, Guid id, DateTime inserted)
{
var ft = db.FaultTypes.FirstOrDefault(e => id.Equals(e.FaultTypeID));
if (ft != null)
{
ft.Updated = inserted;
}
}
/// <summary>
/// 读取数据库中的信息
/// </summary>
/// <param name="sql">查出的sql语句</param>
/// <param name="id">查询的条件</param>
/// <returns></returns>
public List <StructuralInformation> GetStruckInfos(string sql)
{
List <StructuralInformation> list = null;
using (var db = new MachineFailureEntities())
{
var results = db.Database.SqlQuery <StructuralInformation>(sql);
if (results.Count() != 0)
{
list = results.OrderBy(e => e.Name).ToList();
}
}
return(list);
}
/// <summary>
/// 按故障类型名称分组获取故障类型
/// </summary>
/// <returns故障类型的集合</returns>
public List <string> ReadFaultTypes()
{
List <string> senNames = new List <string>();
using (var db = new MachineFailureEntities())
{
var sensorQuery = from sensor in db.FaultTypes
group sensor by sensor.Name;
foreach (var sensor in sensorQuery)
{
senNames.Add(sensor.Key);
}
}
return(senNames);
}
/// <summary>
/// 获取每个测点下的最新一条记录
/// </summary>
/// <returns>返回每个测点下的最新一条记录的信息</returns>
public List <Fault> ReadFailureInfo()
{
using (var db = new MachineFailureEntities())
{
string sql = @"select pic.[PictureID],rec.[RecordID],MaxTime.[Name] as FaultTypeName,rec.[Conclusion] as FaultInfo,rec.[Timestamp] from
(
select MAX(rec.[Inserted]) lastInsered,ft.[Name] from [Records] rec
left join [FaultTypes] ft on ft.[FaultTypeID] = rec.[FaultTypeID]
group by ft.[Name]
) MaxTime
left join [Records] rec on rec.[Inserted] = MaxTime.[lastInsered]
left join [Pictures] pic on pic.[RecordID] = rec.[RecordID]";
return(db.Database.SqlQuery <Fault>(sql).ToList());
}
}
public List <Fault> GetRecordsFault(Guid id)
{
using (var db = new MachineFailureEntities())
{
string sql = string.Format(@"select pic.[PictureID],rec.[RecordID],rec.[Conclusion] FaultInfo,rec.[Timestamp] from [Pictures] pic
left join [Records] rec on rec.[RecordID] = pic.[RecordID]
where rec.[RecordID] = '{0}'", id);
var records = db.Database.SqlQuery <Fault>(sql);
int count = records.Count();
if (count > 0)
{
return(records.ToList());
}
else
{
return(new List <Fault>());
}
}
}
/// <summary>
/// 获取机组
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <ViewInfo> GetMachies(Guid id, string searchText)
{
using (var db = new MachineFailureEntities())
{
string sql = string.Format(@"select mac.[MachineID] Id,mac.[Name],CONVERT(nvarchar(19),mac.[Updated],20) Updated from [FaultTypes] ft
left join [Machines] mac on mac.[MachineID] = ft.[MachineID]
where ft.[Name] = N'{0}' and mac.[SiteID] = '{1}'
order by mac.[Name] asc", searchText, id);
var macs = db.Database.SqlQuery <ViewInfo>(sql);
int totalRecord = macs.Count();
if (totalRecord != 0)
{
return(macs.ToList());
}
else
{
return(new List <ViewInfo>());
}
}
}
/// <summary>
/// 获取记录列表
/// </summary>
/// <param name="id"></param>
/// <param name="searchText"></param>
/// <param name="time"></param>
/// <returns></returns>
public List <ViewRecords> GetRecords(Guid id, string searchText, string[] time)
{
using (var db = new MachineFailureEntities())
{
string sql = string.Format(@"select rec.[RecordID] Id,rec.[Timestamp] from [Records] rec
left join[FaultTypes] ft on ft.[FaultTypeID] = rec.[FaultTypeID]
left join[Machines] mac on mac.[MachineID] = ft.[MachineID]
where mac.[MachineID] = '{0}' and ft.[Name] = N'{1}' and [Timestamp] between '{2}' and '{3}'
order by [rec].[Timestamp] desc", id, searchText, time[0], time[1]);
var records = db.Database.SqlQuery <ViewRecords>(sql);
int count = records.Count();
if (count > 0)
{
return(records.ToList());
}
else
{
return(new List <ViewRecords>());
}
}
}
/// <summary>
/// 按机组id获取故障类型
/// </summary>
/// <returns故障类型的集合</returns>
public List <StructuralInformation> ReadFaultTypes(Guid MachineID)
{
using (var db = new MachineFailureEntities())
{
var fts = db.FaultTypes;
if (fts != null)
{
return(fts.ToList()
.Where(e => e.MachineID.Equals(MachineID))
.Select(e => new StructuralInformation()
{
ID = e.FaultTypeID, Name = e.Name
})
.OrderBy(e => e.Name)
.ToList());
}
else
{
return(new List <StructuralInformation>());
}
}
}
/// <summary>
/// 根据站点id获取机组
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <StructuralInformation> AjaxGetMachines(Guid id)
{
using (var db = new MachineFailureEntities())
{
var machines = db.Machines;
if (machines != null)
{
return(machines.ToList()
.Where(e => e.SiteID.Equals(id))
.Select(e => new StructuralInformation()
{
ID = e.MachineID, Name = e.Name
})
.OrderBy(e => e.Name)
.ToList());
}
else
{
return(new List <StructuralInformation>());
}
}
}
/// <summary>
/// ajax读取数据
/// </summary>
/// <param name="coutomerID"></param>
/// <returns></returns>
public List <StructuralInformation> AjaxGetSites(Guid coutomerID)
{
using (var db = new MachineFailureEntities())
{
var dbSetSites = db.Sites;
if (dbSetSites != null)
{
var sites = dbSetSites.ToList()
.Where(e => e.CustomerID.Equals(coutomerID))
.Select(e => new StructuralInformation()
{
ID = e.SiteID, Name = e.Name
})
.OrderBy(e => e.Name)
.ToList();
return(sites);
}
else
{
return(new List <StructuralInformation>());
}
}
}