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 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 string SendClientDailyEmail()
{
if (string.IsNullOrEmpty(_currentClient))
return string.Empty;
using (DB db = new DB(DBHelper.GetConnectionString(_currentClient)))
{
ClientEmail cEmail = (from o in db.ClientEmails
where o.Client == _currentClient
select o).FirstOrDefault();
if (cEmail == null)
{
cEmail = new ClientEmail {Client = _currentClient, SendDailyDigest = true};
db.AddToClientEmails(cEmail);
return (db.SaveChanges() > 0).ToString();
}
cEmail.SendDailyDigest = true;
return (db.SaveChanges() > 0).ToString();
}
}
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 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 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 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 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;
}
}
private string Save(int id,HttpContext context, bool changeMinutes = false)
{
int reasonId;
string reasonCode, comment, line, client;
DateTime startdatetime, enddatetime;
decimal minutes;
int throughputId, option1Id, option2Id = -1;
reasonCode = request.Form["reasonCode"];
comment = request.Form["comment"];
line = request.Form["line"];
client = _currentClient;
line = line.Replace('#', ' ');
int.TryParse(request.Form["throughput"], out throughputId);
int.TryParse(request.Form["option1"], out option1Id);
int.TryParse(request.Form["option2"], out option2Id);
if (!DateTime.TryParse(request.Form["startdatetime"], out startdatetime))
{
context.Response.Write("start date time is empty.");
return "false";
}
if (!int.TryParse(request.Form["reasonId"], out reasonId))
{
context.Response.Write("reason id is empty.");
return "false";
}
if (request.Form["minutes"] != "tv")
{
if (!decimal.TryParse(request.Form["minutes"], out minutes))
{
context.Response.Write("minutes id is empty.");
return "false";
}
}
else
{
minutes = -1;
}
if (string.IsNullOrEmpty(line))
{
context.Response.Write("line is empty.");
return "false";
}
if (string.IsNullOrEmpty(reasonCode))
{
context.Response.Write("reasonCode is empty.");
return "false";
}
startdatetime.AddMinutes(Convert.ToDouble(minutes));
bool result;
using (DB db = new DB(DBHelper.GetConnectionString(_currentClient)))
{
var q = from o in db.DowntimeDataSet
where o.ID == id
select o;
var dd = q.FirstOrDefault();
if (dd == null) return false.ToString().ToLower();
DowntimeReason reason = DCSDashboardDemoHelper.getReason(reasonId);
if (dd.EventStop.HasValue == false)
{
if (!DateTime.TryParse(request.Form["enddatetime"], out enddatetime))
{
context.Response.Write("end date time is empty.");
return "false";
}
dd.EventStop = enddatetime;
if (dd.EventStart != null)
dd.Minutes = Convert.ToDecimal(dd.EventStop.Value.Subtract(dd.EventStart.Value).TotalMinutes);
changeMinutes = false;
SwitchLineStatus(line, true);
}
dd.ReasonCodeID = reasonId;
dd.ReasonCode = reasonCode;
dd.Line = line;
dd.Comment = comment;
if(changeMinutes)
dd.Minutes = minutes;
dd.Client = client;
if (reason.HideReasonInReports && dd.Minutes > reason.Duration && reason.Duration > 0)//If Planned && duration is greater than 0
{
DowntimeChild child1 = (from o in db.DowntimeChildren
where o.ParentDowntimeId == dd.ID
select o).FirstOrDefault();
if (child1 == null)
{
if (dd.EventStart != null)
{
dd.EventStop = dd.EventStart.Value.AddMinutes(reason.Duration);
// Tim mark
DowntimeData dd2 = new DowntimeData
{
Client = dd.Client,
ReasonCode = null,
ReasonCodeID = null,
Line = dd.Line,
Minutes = dd.Minutes - reason.Duration,
Comment = string.Empty,
EventStart = dd.EventStop
};
dd2.EventStop = dd.EventStop.Value.AddMinutes(Convert.ToDouble(dd2.Minutes));
child1 = new DowntimeChild {ParentDowntimeId = dd.ID};
dd.Minutes = reason.Duration;//Change after setting dd2's minutes
db.AddToDowntimeDataSet(dd2);
db.SaveChanges();
child1.DowntimeId = dd2.ID;
child1.ReasonCodeId = reason.ID;
db.AddToDowntimeChildren(child1);
}
db.SaveChanges();
}
}
else
{
db.SaveChanges();
}
result = true;
if (reason.IsChangeOver && throughputId > 0)
{
DCSDashboardDemoHelper.CreateThroughPutHistory(throughputId, option1Id, option2Id, dd.EventStop.Value, line);
}
else if (option1Id > 0 || option2Id > 0)
{
if (option1Id > 0)
DCSDashboardDemoHelper.CreateNOS(dd.ID, option1Id);
if (option2Id > 0)
DCSDashboardDemoHelper.CreateNOS(dd.ID, option2Id);
}
}
return result.ToString().ToLower();
}
public string CreateDTWithNoStop()
{
DateTime sd;
if (!DateTime.TryParse(request.Params["sd"], out sd))
{
return null;
}
using (DB db = new DB(DBHelper.GetConnectionString(_currentClient)))
{
DowntimeData dt = new DowntimeData();
dt.Client = _currentClient;
dt.EventStart = sd;
dt.EventStop = null;
dt.Line = Line;
dt.Minutes = 0;
db.AddToDowntimeDataSet(dt);
db.SaveChanges();
SwitchLineStatus(Line, false);
return ConvertToJsonString(new
{
ID = dt.ID,
EventStart = (dt.EventStart.Value.ToString("MM/dd/yyyy hh:mm:ss tt")),
EventStop = "",
ReasonCodeID = dt.ReasonCodeID,
ReasonCode = dt.ReasonCode,
Minutes = (dt.Minutes.Value),
Line = dt.Line,
Comment = dt.Comment
});
}
}
private string delRecords()
{
string ids = request["ids"];
if (string.IsNullOrEmpty(ids)) return "Invald args.";
using (DB db = new DB(DBHelper.GetConnectionString(_currentClient)))
{
foreach (var item in ids.Split(','))
{
int id;
if (!int.TryParse(item, out id)) continue;
var q = from o in db.DowntimeDataSet
where o.ID == id
select o;
var row = q.FirstOrDefault();
if (row != null)
{
db.DeleteObject(row);
}
}
db.SaveChanges();
}
return "true";
}
private void addNewEvent(HttpContext context)
{
//reasonId&reasonCode&comment&startdatetime&minutes&line
int reasonId, occurences;
int throughputId, option1Id, option2Id = -1;
DateTime startdatetime;
decimal minutes;
var reasonCode = request.Form["reasonCode"];
var comment = request.Form["comment"];
var line = request.Form["line"];
line = line.Replace('#', ' ');
var client = _currentClient;
int.TryParse(request.Form["Occurences"], out occurences);
if (occurences == 0) occurences = 1;
int.TryParse(request.Form["throughput"], out throughputId);
int.TryParse(request.Form["option1"], out option1Id);
int.TryParse(request.Form["option2"], out option2Id);
if(!DateTime.TryParse(request.Form["startdatetime"],out startdatetime))
{
context.Response.Write("start date time is empty.");
return;
}
if(!int.TryParse(request.Form["reasonId"],out reasonId))
{
context.Response.Write("reason id is empty.");
return;
}
if(!decimal.TryParse(request.Form["minutes"],out minutes))
{
context.Response.Write("minutes id is empty.");
return;
}
if(string.IsNullOrEmpty(line))
{
context.Response.Write("line is empty.");
return;
}
if(string.IsNullOrEmpty(reasonCode))
{
context.Response.Write("reasonCode is empty.");
return;
}
var enddatetime = startdatetime.AddMinutes(Convert.ToDouble(minutes));
bool result = false;
using (DB db = new DB(DBHelper.GetConnectionString(_currentClient)))
{
int count = 0;
DowntimeReason reason = (from o in db.DowntimeReasonSet
where o.ID == reasonId
select o).FirstOrDefault();
if (reason == null)
context.Response.Write("Reason Code doesn't exist");
if (throughputId > 0 && reason.IsChangeOver)
{
var throughput = (from o in db.Throughput where o.Id == throughputId select o).FirstOrDefault();
reasonCode = string.Format("{0} [{1}]", reasonCode, throughput.Name);
}
List<DowntimeData> dts = new List<DowntimeData>();
for (int cpnum = count; cpnum < occurences; cpnum++)
{
DowntimeData dd = new DowntimeData();
dd.EventStart = startdatetime;
dd.EventStop = enddatetime;
dd.Minutes = minutes;
dd.ReasonCodeID = reasonId;
dd.ReasonCode = reasonCode;
dd.Line = line;
dd.Comment = comment;
dd.Client = client;
dd.IsCreatedByAcromag = false;
db.AddToDowntimeDataSet(dd);
dts.Add(dd);
}
int updatedRecords = db.SaveChanges();
if (throughputId > 0 && reason.IsChangeOver)
{
DCSDashboardDemoHelper.CreateThroughPutHistory(throughputId, option1Id, option2Id, enddatetime, line);
}
else if(option1Id > 0 || option2Id > 0)
{
foreach (DowntimeData dt in dts)
{
if (dt.ID <= 0) continue;
if (option1Id > 0)
DCSDashboardDemoHelper.CreateNOS(dt.ID, option1Id);
if (option2Id > 0)
DCSDashboardDemoHelper.CreateNOS(dt.ID, option2Id);
}
}
if (updatedRecords > 0)
result = true;
else if (updatedRecords == 0 && count == 1)
result = true;
else if (updatedRecords == 0 && count == 0)
result = false;
}
context.Response.Write(result.ToString().ToLower());
}
public string UpdateThroughPut()
{
string name = request.Form["name"];
string description = request.Form["description"];
int perHour;
int id;
int.TryParse(request.Form["perhour"], out perHour);
int.TryParse(request.Form["id"], out id);
if (string.IsNullOrEmpty(name) || string.IsNullOrEmpty(description) || perHour <= -1 || id <= -1)
return "FAILED";
using (DB db = new DB(DBHelper.GetConnectionString(_currentClient)))
{
Throughput tp = (from o in db.Throughput
where o.Id == id
select o).FirstOrDefault();
if (tp == null) return "FAILED";
tp.Name = name;
tp.Description = description;
tp.PerHour = perHour;
int results = db.SaveChanges();
if (results >= 0)
return "SUCCESS";
}
return "FAILED";
}
public string UpdateOptionInfo()
{
string op1Name = request.Params["opt1Name"];
string op1Required = request.Params["opt1Required"];
string op1Enabled = request.Params["opt1Enabled"];
string op2Name = request.Params["opt2Name"];
string op2Required = request.Params["opt2Required"];
string op2Enabled = request.Params["opt2Enabled"];
using (DB db = new DB(DBHelper.GetConnectionString(_currentClient)))
{
OptionInfo opt1 = (from o in db.OptionInfoes
where o.Number == 1
&& o.Client == _currentClient
select o).FirstOrDefault();
OptionInfo opt2 = (from o in db.OptionInfoes
where o.Number == 2
&& o.Client == _currentClient
select o).FirstOrDefault();
if (opt1 == null)
{
opt1 = new OptionInfo()
{
Client = _currentClient,
Number = 1
};
db.AddToOptionInfoes(opt1);
}
if (opt2 == null)
{
opt2 = new OptionInfo()
{
Client = _currentClient,
Number = 2
};
db.AddToOptionInfoes(opt2);
}
opt1.IsRequired = Convert.ToBoolean(op1Required);
opt1.Enabled = Convert.ToBoolean(op1Enabled);
opt1.Name = op1Name;
opt2.IsRequired = Convert.ToBoolean(op2Required);
opt2.Enabled = Convert.ToBoolean(op2Enabled);
opt2.Name = op2Name;
return (db.SaveChanges() > 0).ToString();
}
}
public string UpdateDTWithNoStop()
{
DateTime es;
int id;
if (!DateTime.TryParse(request.Params["es"], out es))
{
return null;
}
if (!int.TryParse(request.Params["id"], out id))
{
return null;
}
using (DB db = new DB(DBHelper.GetConnectionString(_currentClient)))
{
DowntimeData dt = (from o in db.DowntimeDataSet
where o.ID == id
select o).FirstOrDefault();
if (dt == null)
return ConvertToJsonString(new
{
ID = -1,
EventStart = "",
EventStop = "",
ReasonCodeID = -1,
ReasonCode = "",
Minutes = 0,
Line = Line,
Comment = ""
});
dt.EventStop = es;
db.SaveChanges();
if (dt.EventStart != null && dt.Minutes != null)
return ConvertToJsonString(new
{
ID = dt.ID,
EventStart = dt.EventStart.Value.ToString("MM/dd/yyyy hh:mm:ss tt"),
EventStop = "",
ReasonCodeID = dt.ReasonCodeID,
ReasonCode = dt.ReasonCode,
Minutes = dt.Minutes.Value,
Line = dt.Line,
Comment = dt.Comment
});
return ConvertToJsonString(new {});
}
}
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;
}
}
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;
}
}
private string saveAllPostRows()
{
List<PostRowData> dd = getPostRowDatas();
if (dd == null) return "no data";
using (DB db = new DB(DBHelper.GetConnectionString(_currentClient)))
{
DowntimeReason dr;
foreach (var item in dd)
{
var levels = item.Path.Split('>');
if (levels.Length < 3) Array.Resize(ref levels, 3);
if (string.IsNullOrEmpty(levels[0]) && string.IsNullOrEmpty(levels[1]) && string.IsNullOrEmpty(levels[2])) continue;
if (item.Id <= 0)
{
dr = new DowntimeReason
{
Level1 = levels[0],
Level2 = levels[1],
Level3 = levels[2],
Duration = item.Duration,
Client = _currentClient,
HideReasonInReports = item.HideReasonInReports,
Line = (!string.IsNullOrEmpty(Line) ? Line : "company-demo"),
IsChangeOver = item.IsChangeOver
};
db.AddToDowntimeReasonSet(dr);
}
else
{
var q = from o in db.DowntimeReasonSet
where o.ID == item.Id
&& o.Client == _currentClient
select o;
dr = q.FirstOrDefault();
if (dr == null) continue;
if ((string.IsNullOrEmpty(dr.Line) || dr.Line == "company-demo") && (!string.IsNullOrEmpty(Line) && Line != "company-demo"))//assumes it is a global reason code and they're adding a Line specific
{
DowntimeReason reason = new DowntimeReason
{
Level1 = levels[0],
Level2 = levels[1],
Level3 = levels[2],
Duration = item.Duration,
HideReasonInReports = item.HideReasonInReports,
Line = (!string.IsNullOrEmpty(Line) ? Line : "company-demo"),
IsChangeOver = item.IsChangeOver,
Client = _currentClient
};
db.AddToDowntimeReasonSet(reason);
}
else
{
dr.Level1 = levels[0];
dr.Level2 = levels[1];
dr.Level3 = levels[2];
dr.Duration = item.Duration;
dr.HideReasonInReports = item.HideReasonInReports;
dr.Line = (!string.IsNullOrEmpty(Line) ? Line : "company-demo");
dr.IsChangeOver = item.IsChangeOver;
}
}
}
//delete rows
foreach (var item in getPostRowDatasDeleteRows())
{
var q = from o in db.DowntimeReasonSet
where o.ID == item
&& o.Client == _currentClient
select o;
dr = q.FirstOrDefault();
if (dr == null) continue;
if(dr.Line == Line)//Don't want to accidently delete another Line's reason code
db.DeleteObject(dr);
}
db.SaveChanges();
return "true";
}
}
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;
}
}
private string SplitDowntime(int downtimeId, decimal minutes)
{
using (var db = new DB(DBHelper.GetConnectionString(_currentClient)))
{
var q = from o in db.DowntimeDataSet
where o.ID == downtimeId
select o;
DowntimeData dd = q.FirstOrDefault();
if (dd == null) return "false";
var seconds = Convert.ToInt32(minutes*60);
if (dd.EventStop != null)
{
dd.EventStop = dd.EventStop.Value.Subtract(new TimeSpan(0, 0, 0, seconds));
dd.Minutes -= minutes;
DowntimeData dd2 = new DowntimeData
{
Client = dd.Client,
ReasonCode = null,
ReasonCodeID = null,
Line = dd.Line,
Minutes = minutes,
Comment = string.Empty,
EventStart = dd.EventStop,
EventStop = dd.EventStop.Value.AddSeconds(Convert.ToInt32(minutes*60))
};
db.AddToDowntimeDataSet(dd2);
}
db.SaveChanges();
}
return "true";
}
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;
}
private bool Stop(DateTime start,DateTime stop)
{
DowntimeData newdd = new DowntimeData() { EventStart = start, EventStop = stop, Minutes = Convert.ToDecimal(stop.Subtract(start).TotalSeconds) / 60 };
using (DB db = new DB(DBHelper.GetConnectionString(_currentClient)))
{
db.AddToDowntimeDataSet(newdd);
db.SaveChanges();
}
return true;
}
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 DeleteOption()
{
int id;
int.TryParse(request.Form["id"], out id);
if (id <= -1) return "FAILED";
using (DB db = new DB(DBHelper.GetConnectionString(_currentClient)))
{
Options tp = (from o in db.Options
where o.Id == id
select o).FirstOrDefault();
if (tp == null) return "FAILED";
db.DeleteObject(tp);
int results = db.SaveChanges();
if (results >= 0)
return "SUCCESS";
}
return "FAILED";
}
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;
}
}
private void SwitchLineStatus(string line, bool what)
{
using (DB db = new DB(DBHelper.GetConnectionString(_currentClient)))
{
LineStatus stat = (from o in db.LineStatus
where o.Line == line && o.Client == _currentClient
select o).FirstOrDefault();
if (stat != null)
stat.Status = what;
db.SaveChanges();
}
}
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 string DeleteThroughPut()
{
int id;
int.TryParse(request.Form["id"], out id);
if (id <= -1) return "FAILED";
using (DB db = new DB(DBHelper.GetConnectionString(_currentClient)))
{
Throughput tp = (from o in db.Throughput
where o.Id == id
select o).FirstOrDefault();
if (tp == null) return "FAILED";
tp.Active = false;
int results = db.SaveChanges();
if (results >= 0)
return "SUCCESS";
}
return "FAILED";
}
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 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;
}
}