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; } }