public static DigestEmailLine AddLine(int emailId, string line)
{
using (DB db = new DB(DBHelper.GetConnectionString()))
{
DigestEmail email = (from o in db.DigestEmails
where o.Id == emailId
select o).FirstOrDefault();
if (email != null)
{
email.DigestEmailLines.Load();
DigestEmailLine emailLine = (from o in email.DigestEmailLines
where o.Line == line
select o).FirstOrDefault();
if (emailLine == null)
{
emailLine = new DigestEmailLine();
emailLine.Line = line;
emailLine.DigestEmail = email;
db.AddToDigestEmailLines(emailLine);
db.SaveChanges();
return emailLine;
}
}
}
return null;
}
public static List<CommentRow> Comments(DateTime? startTime, DateTime? endTime, int reasonCodeId, string level1, string line)
{
if (string.IsNullOrEmpty(line))
line = Filter_Line;
using (DB db = new DB(DBHelper.GetConnectionString(Filter_Client)))
{
var q = from a in db.DowntimeDataSet
where (a.Client == Filter_Client || string.IsNullOrEmpty(Filter_Client))
&& (!startTime.HasValue || a.EventStart >= startTime.Value)
&& (!endTime.HasValue || a.EventStart < endTime.Value)
&& (a.Client == Filter_Client || string.IsNullOrEmpty(Filter_Client))
join b in db.vw_DowntimeReasonSet on a.ReasonCodeID equals b.ID
where (b.Level1 == level1 || string.IsNullOrEmpty(level1))
&& (a.Line == line || string.IsNullOrEmpty(line))
&& (b.ID == reasonCodeId || reasonCodeId <= 0)
&& (b.Client == Filter_Client || string.IsNullOrEmpty(Filter_Client))
&& !string.IsNullOrEmpty(a.Comment)
&& !ExcludeLevel1.Contains("," + b.Level1 + ",")
&& (!ExcludeLevel2.Contains("," + b.Level2 + ",") || string.IsNullOrEmpty(b.Level2))
&& (!ExcludeLevel3.Contains("," + b.Level3 + ",") || string.IsNullOrEmpty(b.Level3))
select new CommentRow { Line = a.Line, Minutes = a.Minutes, Client = b.Client, Comment = a.Comment, Level1 = b.Level1, Level2 = b.Level2, Level3 = b.Level3, ReasonCodeId = a.ReasonCodeID, EventStart = a.EventStart, EventStop = a.EventStop }
;
return q.ToList();
}
}
public bool alreadyExists(string client, string line, DateTime eventStart, DateTime eventStop)
{
using (DB db = new DB())
{
//Grab anything that fits in the event start & event stop time range. Whether it's within or greater.
DowntimeData dt = (from o in db.DowntimeDataSet
where o.Client == client && o.Line == line
&& (((o.EventStart.Value >= eventStart && o.EventStop.Value <= eventStop) || (o.EventStart.Value <= eventStart && o.EventStop.Value >= eventStop)) || o.EventStop.Value == eventStop || o.EventStart.Value == eventStart)
&& o.IsCreatedByAcromag == true
select o).FirstOrDefault();
if (dt != null)
return true;
}
return false;
}
public static List<string> AddLines(int emailId, List<string> lines)
{
using (DB db = new DB(DBHelper.GetConnectionString()))
{
DigestEmail email = (from o in db.DigestEmails
where o.Id == emailId
select o).FirstOrDefault();
if (email != null)
{
email.DigestEmailLines.Load();
foreach (string line in lines)
{
DigestEmailLine emailLine = (from o in email.DigestEmailLines
where o.Line == line
select o).FirstOrDefault();
if (emailLine == null)
{
AddLine(email.Id, line);//If new, add it
}
}
foreach (DigestEmailLine emailLine in email.DigestEmailLines)
{
string line = (from o in lines
where o == emailLine.Line
select o).FirstOrDefault();
if (string.IsNullOrEmpty(line))
{
DeleteLine(email.Id, emailLine.Line);
}
}
email.DigestEmailLines.Load();//reload from db
return email.DigestEmailLines.Select(o => o.Line).ToList();
}
}
return new List<string>();
}
public static List<DowntimeHistoryRow> DayDowntimeHistory(DateTime? startTime, DateTime? endTime, int levelId)
{
//if (levelId <= 0) throw new ArgumentOutOfRangeException("levelId");
List<DowntimeHistoryRow> result = new List<DowntimeHistoryRow>();
using (DB db = new DB())
{
//结果里的LEVEL3换成了月份
var q = from a in db.DowntimeDataSet
where (a.Client == Filter_Client || string.IsNullOrEmpty(Filter_Client))
&& (!startTime.HasValue || a.EventStart >= startTime)
&& (!endTime.HasValue || a.EventStart < endTime)
&& (a.Line == Filter_Line || string.IsNullOrEmpty(Filter_Line))
join b in db.vw_DowntimeReasonSet on a.ReasonCodeID equals b.ID
where (b.ID == levelId || levelId <= 0)
&& !ExcludeLevel1.Contains("," + b.Level1 + ",")
&& (!ExcludeLevel2.Contains("," + b.Level2 + ",") || string.IsNullOrEmpty(b.Level2))
&& (!ExcludeLevel3.Contains("," + b.Level3 + ",") || string.IsNullOrEmpty(b.Level3))
orderby a.EventStart.Value.Year, a.EventStart.Value.Month
select new { a, b }
;
var rows = q.ToList();
DateTime r_starttime, r_endTime;
r_starttime = (startTime.HasValue ? startTime.Value : rows.Min(o => o.a.EventStart).Value);
//查询时结束日期是多一天的
r_endTime = (endTime.HasValue ? endTime.Value.AddDays(-1) : rows.Max(o => o.a.EventStart).Value);
for (var i = 0; i <= r_endTime.Subtract(r_starttime).TotalDays; i++)
{
var q1 = from o in rows
where o.a.EventStart.Value.ToString(@"MM\/dd\/yyyy") == r_starttime.AddDays(i).ToString(@"MM\/dd\/yyyy")
select o;
var r = q1.ToList();
result.Add(new DowntimeHistoryRow { Level3 = r_starttime.AddDays(i).ToString(@"MM\/dd"), MinutesSum = r.Sum(o => o.a.Minutes.Value) });
}
}
return result;
}
public bool CreateCaseCount(DateTime? eventStart, DateTime? eventStop, int caseCount, string line, string client)
{
try
{
using (DB db = new DB())
{
int id;
if (!eventStart.HasValue || !eventStop.HasValue)
return false;
CaseCount count = new CaseCount();
count.CaseCount1 = caseCount;
count.Client = client;
count.EventStart = eventStart;
count.EventStop = eventStop;
count.Line = line;
db.AddToCaseCountSet(count);
db.SaveChanges();
id = count.Id;
bool result = id > 0;
if (result)
{
DBHelper.UpdateAscommPing(client, DateTime.Now, line, true);
}
return result;
}
}
catch (Exception ex)
{
String fileName = this.Server.MapPath("~/App_Data/log.txt");
File.AppendAllText(fileName, ex.ToString());
throw;
}
}
public bool InsertCaseCount(string str_eventStart, string str_eventStop, int caseCount, string line, string client)
{
try
{
using (DB db = new DB())
{
DateTime eventStart, eventStop;
if (!DateTime.TryParse(str_eventStart, out eventStart) || !DateTime.TryParse(str_eventStop, out eventStop))
{
return false;
}
CaseCount count = new CaseCount();
count.CaseCount1 = caseCount;
count.Client = client;
count.EventStart = eventStart;
count.EventStop = eventStop;
count.Line = line;
db.AddToCaseCountSet(count);
bool result = db.SaveChanges() > 0;
if (result == true)
{
DBHelper.UpdateAscommPing(client, DateTime.Now, line, true);
}
return result;
}
}
catch (Exception ex)
{
String fileName = this.Server.MapPath("~/App_Data/log.txt");
File.AppendAllText(fileName, ex.ToString());
throw ex;
}
}
public DowntimeData GetLatestDowntimeData(string client, string line)
{
try
{
using (DB db = new DB())
{
DowntimeData dt = (from o in db.DowntimeDataSet
where o.Client == client
&& o.Line == line
orderby o.EventStop.Value descending
select o).FirstOrDefault();
if (dt != null)
return dt;
return null;
}
}
catch (Exception ex)
{
String fileName = this.Server.MapPath("~/App_Data/log.txt");
File.AppendAllText(fileName, ex.ToString());
throw ex;
}
}
public int GetLatestSku(string client, string line)
{
try
{
using (DB db = new DB())
{
return (from o in db.ThroughputHistory.Include("Throughput")
where o.Client == client && o.Line == line
orderby o.Date descending
select o.Throughput.PerHour).FirstOrDefault();
}
}
catch (Exception)
{
return -1;
}
}
protected void Page_Load(object sender, EventArgs e)
{
Line = Request["line"];
int dId = -1;
int.TryParse(Request["detailId"], out dId);
if (dId > 0)
DetailId = dId;
if (User.Identity.Name != "admin")
{
string line = string.Empty;
if (!string.IsNullOrEmpty(Line))
line = "?line=" + Line;
if (Membership.GetUser() != null)
{
Guid UserId = (Guid)Membership.GetUser().ProviderUserKey;
using (DB db = new DB())
{
UserInfo info = (from o in db.UserInfoSet
where o.UserId == UserId
select o).FirstOrDefault();
if (info != null)
{
if (info.EffChartEnabled == true)
Response.Redirect(DCSDashboardDemoHelper.BaseVirtualAppPath + "\\DCSDashboard.aspx" + line);
}
}
}
else
Response.Redirect(DCSDashboardDemoHelper.BaseVirtualAppPath + "\\Login.aspx" + line);
}
string url = WebConfigurationManager.AppSettings["expire url"];
if (User.Identity.Name.ToLower() == "txi" || User.Identity.Name.ToLower() == "admin")
divKPIS.Visible = true;
else
divKPIS.Visible = false;
if (!IsPostBack)
{
if (string.IsNullOrEmpty(Line))
Line = "company-demo";
clientLines.Items.Clear();
List<string> lines = DCSDashboardDemoHelper.getLines();
lines.Sort();
foreach (string line in lines)
{
string l = line.Replace("#", "").Trim();
ListItem item = clientLines.Items.FindByValue(l);
if (item == null && !string.IsNullOrEmpty(l))
{
item = new ListItem(l);
clientLines.Items.Add(item);
}
}
foreach (ListItem item in clientLines.Items)
{
if (string.IsNullOrEmpty(item.Text))
clientLines.Items.Remove(item);
}
}
string op = Request["op"];
if (!string.IsNullOrEmpty(op))
{
DateTime? startDate = null;
DateTime? endDate = null;
DateTime d;
if (DateTime.TryParse(Request.Form["startdate"], out d))
{
startDate = d;
}
if (DateTime.TryParse(Request.Form["enddate"], out d))
{
endDate = d;
}
string xml = string.Empty;
switch (op)
{
case "LossBuckets":
xml = getLossBuckets();
break;
case "LossBuckets_Occuring":
xml = LossBuckets_Occuring();
break;
case "Top5DowntimeEvents":
xml = Top5DowntimeEvents(true);
break;
case "Top5OccuringEvents":
xml = Top5DowntimeEvents(false);
break;
case "DowntimeActualVsGoal":
xml = DowntimeActualVsGoal();
break;
case "Top5DowntimeEvents_Top5DowntimeEvents":
xml = Top5DowntimeEvents_Top5DowntimeEvents();
break;
case "Top5OccuringEvents_Top5OccuringEvents":
xml = Top5OccuringEvents_Top5OccuringEvents();
break;
case "Top5DowntimeEvents_DowntimeActualVsGoal":
xml = Top5DowntimeEvents_DowntimeActualVsGoal();
break;
case "Top5OccuringEvents_OccuringActualVsGoal":
xml = Top5OccuringEvents_OccuringActualVsGoal();
break;
case "Top5OccuringEvents_Comments":
case "Top5DowntimeEvents_Comments":
xml = Top5DowntimeEvents_Comments();
break;
case "HistoricalDetail_DowntimeHistory":
xml = HistoricalDetail_DowntimeHistory();
break;
case "HistoricalDetail_OccurrenceHistory":
xml = HistoricalDetail_OccurrenceHistory();
break;
case "TotalDowntime":
xml = DCSDashboardDemoHelper.TotalDowntime(startDate, endDate, Line).ToString();
break;
case "RedLineList":
xml = getRedLines();//ConvertToJsonString(DCSDashboardDemoHelper.GetGoals());
break;
case "updateredline":
int update_Id, update_occ;
decimal update_downtime;
int.TryParse(Request["id"], out update_Id);
if (!startDate.HasValue)
{
xml = "please input a date";
break;
}
if (!endDate.HasValue)
{
xml = "please input a date";
break;
}
decimal.TryParse(Request["downtime"], out update_downtime);
int.TryParse(Request.Form["Occuring"], out update_occ);
xml = (DCSDashboardDemoHelper.UpdateGoal(update_Id, startDate.Value, endDate.Value, update_downtime, update_occ) ? "1" : "Update failed,please try again.");
break;
case "addredline":
decimal add_downtime;
int add_occ;
if (!startDate.HasValue)
{
xml = "please input a date";
break;
}
if (!endDate.HasValue)
{
xml = "please input a date";
break;
}
decimal.TryParse(Request.Form["downtime"], out add_downtime);
int.TryParse(Request.Form["Occuring"], out add_occ);
xml = (DCSDashboardDemoHelper.InsertGoal(startDate.Value, endDate.Value, add_downtime, add_occ, Line) ? "1" : "0");
break;
case "deleteredline":
int delete_Id;
int.TryParse(Request["id"], out delete_Id);
xml = (DCSDashboardDemoHelper.DeleteGoal(delete_Id) ? "1" : "Update failed,please try again.");
break;
case "GetEventRows":
xml = GetEventRows();
break;
case "Hidden_top5downtime":
xml = Hidden_Top5DowntimeEvents(true);
break;
case "Hidden_top5occuring":
xml = Hidden_Top5DowntimeEvents(false);
break;
case "Hidden_DowntimeActualVsGoal":
xml = Hidden_DowntimeActualVsGoal();
break;
case "Hidden_GetEventRows":
xml = Hidden_GetEventRows();
break;
}
Response.Write(xml);
Response.End();
}
}
public int UpdateDowntimeThreshold(string client, string line, int seconds)
{
using (DB db = new DB())
{
LineSetup setup = (from o in db.LineSetups
where o.DataCollectionNode.Client == client
&& o.Line == line
select o).FirstOrDefault();
if (setup != null)
{
setup.DowntimeThreshold = seconds;
db.SaveChanges();
return seconds;
}
return -1;
}
}
public bool Login(string client, string password)
{
using (DB db = new DB())
{
MembershipUser mu = Membership.GetUser(client);
if (mu == null)
return false;
if (password != mu.GetPassword())
return false;
return true;
}
}
public string LoginServer(string client, string password)
{
try
{
using (DB db = new DB())
{
MembershipUser mu = Membership.GetUser(client);
if (mu == null)
return false.ToString();
if (password != mu.GetPassword())
return false.ToString();
DataCollectionNode node = (from o in db.DataCollectionNodes
where o.Client == client
select o).FirstOrDefault();
if (node != null)
{
return true.ToString();
}
else
{
return false.ToString();
}
}
}
catch (Exception ex)
{
return ex.ToString();
}
}
public int CreateTComCaseCount(string eStart, string eStop, int caseCount, string line, string client)
{
try
{
int result;
if (string.IsNullOrEmpty(eStart) || string.IsNullOrEmpty(eStop))
return -1;
DateTime eventStart;
DateTime eventStop;
if (!DateTime.TryParse(eStart, out eventStart))
return -1;
if (!DateTime.TryParse(eStop, out eventStop))
return -1;
if (eventStop < eventStart)
return -1;
using (DB db = new DB())
{
int id;
CaseCount count = new CaseCount();
count.CaseCount1 = caseCount;
count.Client = client;
count.EventStart = eventStart;
count.EventStop = eventStop;
count.Line = line;
db.AddToCaseCountSet(count);
db.SaveChanges();
id = count.Id;
result = id;
if (result > 0)
{
DBHelper.UpdateAscommPing(client, DateTime.Now, line, true);
}
return result;
}
}
catch (Exception ex)
{
String fileName = this.Server.MapPath("~/App_Data/log.txt");
File.AppendAllText(fileName, ex.ToString());
return -1;
}
}
public int CreateTComDowntimeEvent(string eStart, string eStop, decimal? minutes, string client, string line, string comment = null)
{
int result = 0;
if (string.IsNullOrEmpty(eStart) || string.IsNullOrEmpty(eStop))
return -1;
DateTime eventStart;
DateTime eventStop;
if (!DateTime.TryParse(eStart, out eventStart))
return -1;
if (!DateTime.TryParse(eStop, out eventStop))
return -1;
if (eventStop < eventStart)
return -1;
if (minutes.HasValue == false)
minutes = Convert.ToInt32(eventStop.Subtract(eventStart).TotalMinutes);
decimal totalMinutes = (decimal)eventStop.Subtract(eventStart).TotalMinutes;
if (totalMinutes > 60 && minutes < 60)//If eventstop is greater than an hour or more, but the minute difference isn't, then assume timezone conversion or something is wrong for the times
{
//assume Event Start is correct
eventStop = eventStart.AddMinutes((double)minutes);
}
using (DB db = new DB())
{
if (alreadyExists(client, line, eventStart, eventStop) == true)
return -2;
DowntimeData dd = new DowntimeData();
dd.EventStart = eventStart;
dd.EventStop = eventStop;
dd.Minutes = minutes;
dd.ReasonCodeID = null;
dd.ReasonCode = null;
dd.Line = line;
dd.Comment = comment;
dd.Client = client;
dd.IsCreatedByAcromag = true;
db.AddToDowntimeDataSet(dd);
if (db.SaveChanges() <= 0) return result;
result = dd.ID;
DBHelper.UpdateAscommPing(client, DateTime.Now, line, true);
}
return result;
}
public string Test()
{
try
{
using (DB db = new DB())
{
return "true";
}
}
catch (Exception ex)
{
return ex.ToString();
}
}
public int CreateDowntimeEventWithTimeZone(DateTime? eStart, DateTime? eStop, string strTimeZone, decimal? minutes, string reasonCode, int? reasonCodeID, string comment, string line, string client)
{
int result = 0;
if (eStart.HasValue == false || eStop.HasValue == false)
return 0;
if (minutes.HasValue == false)
minutes = Convert.ToInt32(eStop.Value.Subtract(eStart.Value).TotalMinutes);
DateTime eventStart = eStart.Value;
DateTime eventStop = eStop.Value;
if (!string.IsNullOrEmpty(strTimeZone))
{
TimeZoneInfo timeZone = TimeZoneInfo.FromSerializedString(strTimeZone);
eventStart = TimeZoneInfo.ConvertTime(eStart.Value, timeZone);
eventStop = TimeZoneInfo.ConvertTime(eStop.Value, timeZone);
}
decimal totalMinutes = (decimal)eventStop.Subtract(eventStart).TotalMinutes;
if (totalMinutes > 60 && minutes < 60)//If eventstop is greater than an hour or more, but the minute difference isn't, then assume timezone conversion or something is wrong for the times
{
//assume Event Start is correct
eventStop = eventStart.AddMinutes((double)minutes);
}
using (DB db = new DB())
{
if (alreadyExists(client, line, eventStart, eventStop))
return -2;
DowntimeData dd = new DowntimeData();
dd.EventStart = eventStart;
dd.EventStop = eventStop;
dd.Minutes = minutes;
dd.ReasonCodeID = reasonCodeID;
dd.ReasonCode = reasonCode;
dd.Line = line;
dd.Comment = comment;
dd.Client = client;
dd.IsCreatedByAcromag = true;
db.AddToDowntimeDataSet(dd);
if (db.SaveChanges() > 0)
{
result = dd.ID;
DBHelper.UpdateAscommPing(client, DateTime.Now, line, true);
}
}
return result;
}
public bool CreateDowntimeDataWithTimeZone(DateTime? eventStart, DateTime? eventStop, string strTimeZone, decimal? minutes, string reasonCode, int? reasonCodeID, string comment, string line, string client)
{
bool result = false;
if (eventStart.HasValue == false || eventStop.HasValue == false)
return false;
if (minutes.HasValue == false)
minutes = Convert.ToInt32(eventStop.Value.Subtract(eventStart.Value).TotalMinutes);
if (!string.IsNullOrEmpty(strTimeZone))
{
TimeZoneInfo timeZone = TimeZoneInfo.FromSerializedString(strTimeZone);
eventStart = TimeZoneInfo.ConvertTime(eventStart.Value, timeZone);
eventStop = TimeZoneInfo.ConvertTime(eventStop.Value, timeZone);
}
using (DB db = new DB())
{
DowntimeData latestDowntime = (from o in db.DowntimeDataSet
where o.Client == client && o.Line == line
orderby o.EventStop.Value descending
select o).FirstOrDefault();
DateTime? latestDate = null;
if (latestDowntime != null)
latestDate = latestDowntime.EventStop;
bool create = !latestDate.HasValue;
DowntimeData dd = new DowntimeData();
if (latestDate.HasValue)
{
create = !(latestDate.Value >= eventStop);
}
if (create)
{
//dd.EventStart = eventStart;
//dd.EventStop = eventStop;
dd.EventStart = eventStart;
dd.EventStop = eventStop;
dd.Minutes = minutes;
dd.ReasonCodeID = reasonCodeID;
dd.ReasonCode = reasonCode;
dd.Line = line;
dd.Comment = comment;
dd.Client = client;
dd.IsCreatedByAcromag = true;
db.AddToDowntimeDataSet(dd);
result = db.SaveChanges() > 0;
if (result == true)
{
DBHelper.UpdateAscommPing(client, DateTime.Now, line, true);
}
}
}
return result;
}
public bool UpdateLineStatusWithTime(string client, string line, bool on, string strEventTime)
{
try
{
if (string.IsNullOrEmpty(strEventTime))
return false;
DateTime eventTime;
if (!DateTime.TryParse(strEventTime, out eventTime))
return false;
using (DB db = new DB())
{
LineStatus lineStat = (from o in db.LineStatus
where o.Client == client
&& o.Line == line
select o).FirstOrDefault();
bool result = false;
if (lineStat != null)
{
if (on != lineStat.Status || lineStat.Status == true)//Only log False once, but constantly update time for True
{
lineStat.Status = on;
lineStat.EventTime = eventTime;
result = db.SaveChanges() > 0;
}
}
else
{
lineStat = new LineStatus();
lineStat.Client = client;
lineStat.Line = line;
lineStat.Status = on;
lineStat.ShiftStart = "06:00:00";
lineStat.Timezone = "Eastern Standard Time";
lineStat.EventTime = eventTime;
db.AddToLineStatus(lineStat);
result = db.SaveChanges() > 0;
}
if (result == true)
{
DBHelper.UpdateAscommPing(client, DateTime.Now, line, true);
}
return result;
}
}
catch (Exception ex)
{
String fileName = this.Server.MapPath("~/App_Data/log.txt");
File.AppendAllText(fileName, ex.ToString());
throw ex;
}
}
public int UpdateGlobalDowntimeThreshold(string client, int seconds)
{
using (DB db = new DB())
{
List<LineSetup> setups = (from o in db.LineSetups
where o.DataCollectionNode.Client == client
select o).ToList();
if (setups.Count > 0)
{
foreach(LineSetup setup in setups)
setup.DowntimeThreshold = seconds;
db.SaveChanges();
return seconds;
}
return -1;
}
}
public bool InsertDevice(NodeLine device)
{
try
{
using (DB db = new DB())
{
NodeLine node = (from o in db.DeviceSetups
where o.Id == device.DeviceSetupId
select new NodeLine
{
DeviceSetupId = o.Id,
Client = o.LineSetup.DataCollectionNode.Client,
AddressType = o.AddressType,
DataType = o.DataType,
DowntimeThreshold = o.LineSetup.DowntimeThreshold,
UptimeThreshold = o.LineSetup.UptimeThreshold,
IPAddress = o.IpAddress,
Line = o.LineSetup.Line,
TagType = o.TagType,
TrackDowntime = o.TrackDowntime,
TrackingType = o.TrackingType,
TagName = o.TagName
}).FirstOrDefault();
if (node != null)//Already exists
{
DeviceSetup setup = (from o in db.DeviceSetups
where o.Id == node.DeviceSetupId
select o).FirstOrDefault();
if (setup != null)
{
setup.IpAddress = device.IPAddress;
setup.TagName = device.TagName;
setup.TagType = device.TagType;
setup.TrackDowntime = device.TrackDowntime;
setup.TrackingType = device.TrackingType;
setup.AddressType = device.AddressType;
setup.DataType = device.DataType;
setup.LineSetup.Line = device.Line;
setup.LineSetup.DowntimeThreshold = device.DowntimeThreshold;
setup.LineSetup.UptimeThreshold = device.UptimeThreshold;
setup.LineSetup.DataCollectionNode.Client = device.Client;
setup.LineSetup.DataCollectionNode.ServerName = device.ServerName;
return db.SaveChanges() > 0;
}
}
else
{
DeviceSetup setup = new DeviceSetup();
setup.IpAddress = device.IPAddress;
setup.TagName = device.TagName;
setup.TagType = device.TagType;
setup.TrackDowntime = device.TrackDowntime;
setup.TrackingType = device.TrackingType;
setup.AddressType = device.AddressType;
setup.DataType = device.DataType;
DataCollectionNode dataNode = (from o in db.DataCollectionNodes
where o.Client == device.Client
select o).FirstOrDefault();
if (dataNode == null)
{
dataNode = new DataCollectionNode();
dataNode.Password = "******";
setup.LineSetup.DataCollectionNode = dataNode;
}
setup.LineSetup.DataCollectionNode.Client = device.Client;
LineSetup lineSetup = (from o in db.LineSetups
where o.DataCollectionNode.Id == dataNode.Id
select o).FirstOrDefault();
if (lineSetup == null)
{
lineSetup = new LineSetup();
setup.LineSetup = lineSetup;
}
setup.LineSetup.Line = device.Line;
setup.LineSetup.DowntimeThreshold = device.DowntimeThreshold;
setup.LineSetup.UptimeThreshold = device.UptimeThreshold;
db.AddToLineSetups(lineSetup);
return db.SaveChanges() > 0;
}
}
return false;
}
catch (Exception ex)
{
String fileName = this.Server.MapPath("~/App_Data/log.txt");
File.AppendAllText(fileName, ex.ToString());
throw ex;
}
}
public bool Ping(string client, string line, bool s)
{
using (DB db = new DB())
{
AscommStatus status = (from o in db.AscommStatuses
where o.Client == client
&& o.Line == line
select o).FirstOrDefault();
if (status != null)
{
status.LastPing = DateTime.Now;
status.Status = s;
status.Line = line;
}
else
{
status.Client = client;
status.LastPing = DateTime.Now;
status.Status = s;
status.Line = line;
db.AddToAscommStatuses(status);
}
return db.SaveChanges() > 0;
}
}
public bool InsertDowntimeData(DateTime eventStart, DateTime eventStop, decimal? minutes, string reasonCode, int? reasonCodeID, string comment, string line, string client, string str_eventStart, string str_eventStop)
{
bool result;
using (DB db = new DB())
{
DowntimeData latestDowntime = (from o in db.DowntimeDataSet
where o.Client == client && o.Line == line
orderby o.EventStop.Value descending
select o).FirstOrDefault();
DateTime? latestDate = (latestDowntime != null ? latestDowntime.EventStop : null);
bool create = !latestDate.HasValue;
DowntimeData dd = new DowntimeData();
//为了避免时区问题，用string转过来转换
DateTime es, et;
if (!DateTime.TryParse(str_eventStart, out es) || !DateTime.TryParse(str_eventStop, out et))
{
return false;
}
if (latestDate.HasValue)
{
create = latestDate.Value < et;
}
if (create)
{
//dd.EventStart = eventStart;
//dd.EventStop = eventStop;
dd.EventStart = es;
dd.EventStop = et;
dd.Minutes = minutes;
dd.ReasonCodeID = reasonCodeID;
dd.ReasonCode = reasonCode;
dd.Line = line;
dd.Comment = comment;
dd.Client = client;
dd.IsCreatedByAcromag = true;
db.AddToDowntimeDataSet(dd);
result = db.SaveChanges() > 0;
if (result)
{
DBHelper.UpdateAscommPing(client, DateTime.Now, line, true);
}
}
else
result = false;
}
return result;
}
public DataCollectionNode GetCollectionNode(string client, string password)
{
try
{
using (DB db = new DB())
{
MembershipUser mu = Membership.GetUser(client);
if (mu == null)
return null;
if (password != mu.GetPassword())
return null;
DataCollectionNode node = (from o in db.DataCollectionNodes
where o.Client == client
select o).FirstOrDefault();
if (node != null)
{
return node;
}
else
{
return null;
}
}
}
catch (Exception)
{
return null;
}
}
public bool LoginAndCreateDowntimeData(DateTime? eventStart, DateTime? eventStop, decimal? minutes, string reasonCode, int? reasonCodeID, string comment, string line, string client, string password)
{
bool result;
bool loggedIn = Login(client, password);
if (!loggedIn) return false;
if (eventStart.HasValue == false || eventStop.HasValue == false)
return false;
if (minutes.HasValue == false)
minutes = Convert.ToInt32(eventStop.Value.Subtract(eventStart.Value).TotalMinutes);
using (DB db = new DB())
{
DowntimeData latestDowntime = (from o in db.DowntimeDataSet
where o.Client == client && o.Line == line
orderby o.EventStop.Value descending
select o).FirstOrDefault();
DateTime? latestDate = null;
if (latestDowntime != null)
latestDate = latestDowntime.EventStop;
bool create = !latestDate.HasValue;
DowntimeData dd = new DowntimeData();
if (latestDate.HasValue)
{
create = !(latestDate.Value >= eventStop);
}
if (create)
{
//dd.EventStart = eventStart;
//dd.EventStop = eventStop;
dd.EventStart = eventStart;
dd.EventStop = eventStop;
dd.Minutes = minutes;
dd.ReasonCodeID = reasonCodeID;
dd.ReasonCode = reasonCode;
dd.Line = line;
dd.Comment = comment;
dd.Client = client;
dd.IsCreatedByAcromag = true;
db.AddToDowntimeDataSet(dd);
result = db.SaveChanges() > 0;
if (result)
{
DBHelper.UpdateAscommPing(client, DateTime.Now, line, true);
}
}
else
result = false;
}
return result;
}
public List<NodeLine> GetDevices(string client)
{
try
{
using (DB db = new DB())
{
List<NodeLine> setups = (from o in db.DeviceSetups
where o.LineSetup.DataCollectionNode.Client == client
select new NodeLine { DeviceSetupId = o.Id, Client = client, AddressType = o.AddressType, DataType = o.DataType, DowntimeThreshold = o.LineSetup.DowntimeThreshold, UptimeThreshold = o.LineSetup.UptimeThreshold, IPAddress = o.IpAddress,
Line = o.LineSetup.Line, TagType = o.TagType, TrackDowntime = o.TrackDowntime, TrackingType = o.TrackingType, TagName = o.TagName }).ToList();
if (setups != null)
{
return setups;
}
else
{
return null;
}
}
}
catch (Exception ex)
{
String fileName = this.Server.MapPath("~/App_Data/log.txt");
File.AppendAllText(fileName, ex.ToString());
throw ex;
}
}
public void importExcel()
{
try
{
const int startRow = 1;
string filePath = HttpContext.Current.Server.MapPath("~/App_Data/txi.xlsx");
// Get the file we are going to process
var existingFile = new FileInfo(filePath);
List<DowntimeData> dtData = new List<DowntimeData>();
// Open and read the XlSX file.
using (var package = new ExcelPackage(existingFile))
{
using (DB db = new DB())
{
// Get the work book in the file
ExcelWorkbook workBook = package.Workbook;
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
string client = "TXI";
// Get the first worksheet
int index = 0;
List<Options> options = (from o in db.Options
where o.Client == client
select o).ToList();
// UPDATE [thrivedcs].[dbo].[DowntimeData] SET Line = 'Finish_Mill_3' WHERE Line = 'company-demo' AND Client = 'TXI';
// int counter = 0;
foreach(ExcelWorksheet currentWorksheet in workBook.Worksheets.ToList())
{
string line = "Kiln_2";
if(index == 1)
line = "Raw_Mill_2";
else if(index == 2)
line = "Finish_Mill_3";
// read some data
object dateHeader = currentWorksheet.Cells[startRow, 1].Value;//Date
object eventStartHeader = currentWorksheet.Cells[startRow, 3].Value;//EvenStart
object minutesHeader = currentWorksheet.Cells[startRow, 6].Value;//Minutes
object reasonHeader = currentWorksheet.Cells[startRow, 7].Value;//Reason
object stoppageHeader = currentWorksheet.Cells[startRow, 9].Value;//Stoppage Nature
for (int rowNumber = startRow + 1; rowNumber <= currentWorksheet.Dimension.End.Row; rowNumber++)
// read each row from the start of the data (start row + 1 header row) to the end of the spreadsheet.
{
object oDate = currentWorksheet.Cells[rowNumber, 1].Value;
object oEventStart = currentWorksheet.Cells[rowNumber, 3].Value;
object oMinutes = currentWorksheet.Cells[rowNumber, 6].Value;//It's a Date in H:MM:SS format
object oReason = currentWorksheet.Cells[rowNumber, 7].Value;
object oStoppageOfNature = currentWorksheet.Cells[rowNumber, 9].Value;
if ((oEventStart != null && oEventStart.ToString() != "") && (oStoppageOfNature != null && oStoppageOfNature.ToString() != ""))
{
decimal minutes = 0;
if (oMinutes != null)
{
DateTime minTime = (DateTime)oMinutes;
minutes = Convert.ToDecimal((minTime.TimeOfDay.TotalHours / 60) + minTime.TimeOfDay.TotalMinutes);
}
DateTime EventStart = DateTime.Now;
try
{
EventStart = (DateTime)oEventStart;
}
catch (Exception e)
{
Console.Write(e.Message);
}
DateTime EventStop = EventStart.AddMinutes((double)minutes);
string reason = oReason.ToString();
string nos = oStoppageOfNature.ToString();
Options opt = (from o in options
where o.Name.Equals(nos, StringComparison.InvariantCultureIgnoreCase)
select o).FirstOrDefault();
if (opt != null)
{
DowntimeData dt = new DowntimeData();
dt.Client = client;
dt.Comment = reason;
dt.EventStart = EventStart;
dt.EventStop = EventStop;
dt.Line = line;
dt.Minutes = minutes;
dt.IsCreatedByAcromag = true;
dtData.Add(dt);
db.AddToDowntimeDataSet(dt);
db.SaveChanges();
if (dt.ID > 0)
{
NatureOfStoppage NOS = new NatureOfStoppage();
NOS.OptionId = opt.Id;
NOS.DowntimeId = dt.ID;
db.AddToNatureOfStoppages(NOS);
}
}
else
{
Console.Write("NOS IS EMPTY");
}
}
}
index++;
}
Console.Write("DONE" + dtData.Count);
}
}
}
}
}
catch (IOException ioEx)
{
if (!String.IsNullOrEmpty(ioEx.Message))
{
if (ioEx.Message.Contains("because it is being used by another process."))
{
Console.WriteLine("Could not read example data. Please make sure it not open in Excel.");
}
}
Console.WriteLine("Could not read example data. " + ioEx.Message, ioEx);
}
catch (Exception ex)
{
Console.WriteLine("An error occured while reading example data. " + ex.Message, ex);
}
finally
{
Console.WriteLine();
Console.WriteLine("press any key to exit.");
}
}
public int GetLatestCaseCount(string client, string line)
{
try
{
using (DB db = new DB())
{
CaseCount caseCount = (from o in db.CaseCountSet
where o.Client == client
&& o.Line == line
orderby o.EventStop.Value descending
select o).FirstOrDefault();
if (caseCount != null)
return caseCount.CaseCount1;
return 0;
}
}
catch (Exception ex)
{
String fileName = this.Server.MapPath("~/App_Data/log.txt");
File.AppendAllText(fileName, ex.ToString());
throw;
}
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "application/json";
context.Response.AddHeader("Cache-Control", "private, must-revalidate, max-age=0");
HttpRequest request = context.Request;
string client = request["client"];
List<string> currentLines = new List<string>();
using (DB db = new DB())
{
var q = from o in db.LineStatus
where o.Client == client
select o;
List<LineStatus> myList = q.ToList<LineStatus>();
List<object> results = new List<object>();
DateTime now=DateTime.Now;
foreach (LineStatus ls in myList)
{
if (!currentLines.Contains(ls.Line))
{
currentLines.Add(ls.Line);
TimeZoneInfo hwZone = TimeZoneInfo.FindSystemTimeZoneById(ls.Timezone.Trim());
DateTime newNow = TimeZoneInfo.ConvertTime(now, TimeZoneInfo.Local, hwZone);
if (!ls.EventTime.HasValue)
continue;
DateTime eventTime = ls.EventTime.Value;//TimeZoneInfo.ConvertTime(ls.EventTime.Value, TimeZoneInfo.Local, hwZone);
if (!string.IsNullOrEmpty(ls.FromTimezone))
{
if (ls.FromTimezone.Trim() != ls.Timezone.Trim())
{
TimeZoneInfo fromZone = TimeZoneInfo.FindSystemTimeZoneById(ls.FromTimezone.Trim());
eventTime = TimeZoneInfo.ConvertTime(ls.EventTime.Value, TimeZoneInfo.Local, fromZone);
}
}
else
{
eventTime = TimeZoneInfo.ConvertTime(ls.EventTime.Value, TimeZoneInfo.Local, hwZone);
}
//TimeSpan span = newNow.TimeOfDay;
//if (ls.EventTime != null)
// span = newNow - (DateTime)ls.EventTime;
//String minute = (span.Minutes.ToString().Length > 1) ? span.Minutes.ToString() : ("0" + span.Minutes.ToString());
//String second = (span.Seconds.ToString().Length > 1) ? span.Seconds.ToString() : ("0" + span.Seconds.ToString());
//String spanString = (span.Days * 24 + span.Hours).ToString() + ":" + minute + ":" + second;
TimeSpan s = newNow.Subtract(eventTime);
string hr = s.Hours.ToString();
if (s.Hours < 10)
hr = "0" + s.Hours.ToString();
string min = s.Minutes.ToString();
if (s.Minutes < 10)
min = "0" + s.Minutes.ToString();
string sec = s.Seconds.ToString();
if (s.Seconds < 10)
sec = "0" + s.Seconds.ToString();
results.Add(new
{
line = ls.Line,
time = hr + ":" + min + ":" + sec,
status = ls.Status
});
}
}
context.Response.Write(ConvertToJsonString(results));
}
}
public bool UpdateCollectionNode(string client, int uptime)
{
try
{
using (DB db = new DB())
{
DataCollectionNode node = (from o in db.DataCollectionNodes
where o.Client == client
select o).FirstOrDefault();
if (node != null)
{
node.Uptime = uptime;
db.SaveChanges();
return true;
}
else
{
return false;
}
}
}
catch (Exception)
{
return false;
}
}