C# DowntimeCollection_Demo.DB类代码示例

        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)
                {
//.........这里部分代码省略.........

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