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 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 static List<CaseCount> getCasesPerHourPerLine(DateTime? startTime, DateTime? endTime, string client, bool increments = true )
{
List<CaseCount> rows = new List<CaseCount>();
List<CaseCount> results = new List<CaseCount>();
List<ThroughputHistory> tpHistories = DCSDashboardDemoHelper.getClientThroughPutHistories(client);
using (DB db = new DB())
{
rows = (from a in db.CaseCountSet
where (a.Client == client || string.IsNullOrEmpty(client))
&& (!startTime.HasValue || a.EventStop >= startTime)
&& (!endTime.HasValue || a.EventStop <= endTime)
&& a.Line == "Line_06"
orderby a.EventStop.Value ascending
select a).ToList();
List<string> lines = (from o in rows
select o.Line).Distinct().ToList();
int firstDayOfYear = 0;
int lastDayOfYear = 0;
int year = DateTime.Now.Year;
int month = DateTime.Now.Month;
if (startTime.HasValue)
{
firstDayOfYear = startTime.Value.DayOfYear;
year = startTime.Value.Year;
month = startTime.Value.Month;
}
else
{
CaseCount c = rows.First();
if (c != null)
firstDayOfYear = c.EventStop.Value.DayOfYear;
}
if (endTime.HasValue)
lastDayOfYear = endTime.Value.DayOfYear;
else
{
CaseCount c = rows.Last();
if (c != null)
lastDayOfYear = c.EventStop.Value.DayOfYear;
}
foreach (string line in lines)
{
DateTime tmpDate = startTime.Value;
tmpDate = new DateTime(tmpDate.Year, tmpDate.Month, tmpDate.Day, 0, 0, 0);
for (int dayOfYear = firstDayOfYear; dayOfYear <= lastDayOfYear; dayOfYear++)
{
for (int hour = 0; hour < 24; hour++)
{
CaseCount CC = new CaseCount();
CC.Line = line;
CC.CaseCount1 = 0;
CC.Client = client;
CC.EventStop = tmpDate;
List<CaseCount> cases = (from o in rows
where o.Line == line
&& o.EventStop.Value.Hour == hour && o.EventStop.Value.DayOfYear == dayOfYear
orderby o.EventStop ascending
select o).ToList();
List<ThroughputHistory> historyPuts = (from o in tpHistories
where o.Date.DayOfYear == dayOfYear
&& o.Date.Hour == hour
orderby o.Date ascending
select o).ToList();
if (cases.Count == 0)
{
CC.CaseCount1 = 0;
}
else
{
if (historyPuts.Count > 0)
{
int count = 0;
ThroughputHistory lastHistoryPut = null;
foreach (ThroughputHistory historyPut in historyPuts)
{
Throughput put = historyPut.Throughput;
List<CaseCount> lowerCases = (from o in cases
where o.EventStop.Value <= historyPut.Date
select o).ToList();
if (lowerCases.Count > 0)
{
CaseCount f = lowerCases.First();
CaseCount l = lowerCases.Last();
if (f != l && f != null)
{
count += l.CaseCount1 - f.CaseCount1;
}
}
lastHistoryPut = historyPut;
}
List<CaseCount> restOfCases = (from o in cases
where o.EventStop.Value >= lastHistoryPut.Date
select o).ToList();
if (restOfCases.Count > 0)
{
CaseCount first = restOfCases.First();
CaseCount last = restOfCases.Last();
if (first != last && first != null)
{
count += last.CaseCount1 - first.CaseCount1;
}
}
CC.CaseCount1 = count;
}
else
{
CaseCount first = cases.First();
CaseCount last = cases.Last();
if (first != last && first != null)
{
CC.CaseCount1 = last.CaseCount1 - first.CaseCount1;
}
}
}
results.Add(CC);
tmpDate = tmpDate.AddHours(1);
}
//tmpDate = tmpDate.AddDays(1);
}
}
rows = rows.OrderBy(x => x.EventStop.Value.Day).ThenBy(x => x.EventStop.Value.Hour).ThenBy(x => x.EventStop.Value.Minute).ThenBy(x => x.CaseCount1).ToList();
}
return results;
}
public static Dictionary<CaseCount, decimal> getGraphCaseCounts_old(DateTime? startTime, DateTime? endTime, decimal estCount)
{
List<CaseCount> rows = new List<CaseCount>();
Dictionary<CaseCount, decimal> results = new Dictionary<CaseCount, decimal>();
using (DB db = new DB())
{
var q = from a in db.CaseCountSet
where (a.Client == Filter_Client || string.IsNullOrEmpty(Filter_Client))
&& (a.Line == Filter_Line || string.IsNullOrEmpty(Filter_Line))
&& (!startTime.HasValue || a.EventStop >= startTime)
&& (!endTime.HasValue || a.EventStop <= endTime)
orderby a.EventStop.Value ascending
select a;
if (q != null)
rows = q.ToList();
int firHour = (rows.Count > 0 ? rows.ElementAt(0).EventStop.Value.Hour : 0);
if (firHour == 24)
firHour = 0;
int diff = endTime.Value.Hour - firHour;
if (diff < 12)
firHour += 12 - diff;
firHour = firHour - 1;//For an extra hour before.
if (firHour == -1)
firHour = 23;
DateTime tmpDate = startTime.Value;
for (double x = 0; x <= endTime.Value.Subtract(startTime.Value).Hours; x++)
{
CaseCount c = new CaseCount();
c.EventStop = new DateTime(tmpDate.Year, tmpDate.Month, tmpDate.Day, tmpDate.Hour, 30, 0);//Set to XX:30, so it won't interfere with actual casecounts (which uses end values in hour)
c.CaseCount1 = 0;
tmpDate = tmpDate.AddHours(1.00);
rows.Add(c);
}
rows = rows.OrderBy(x => x.EventStop.Value.Day).ThenBy(x => x.EventStop.Value.Hour).ThenBy(x => x.EventStop.Value.Minute).ThenBy(x => x.CaseCount1).ToList();
CaseCount firstCase = new CaseCount();
CaseCount lastCase;
for (int x = 0; x < rows.Count; x++)
{
//Get Current case
CaseCount currentCase = rows.ElementAt(x);
//if (x == 96)
// currentCase = rows.ElementAt(x);
if (x == 0)
firstCase = currentCase;//Sets just in case until overridden.
if (x > 0 && x < rows.Count)
{
//Get previous case
lastCase = rows.ElementAt(x - 1);
//If hour of previous case is less than current
if (lastCase.EventStop.Value.Hour < currentCase.EventStop.Value.Hour && lastCase.EventStop.Value.Day == currentCase.EventStop.Value.Day)
firstCase = currentCase;
else if (lastCase.EventStop.Value.Hour > currentCase.EventStop.Value.Hour && lastCase.EventStop.Value.Day < currentCase.EventStop.Value.Day)
firstCase = currentCase;
if ((firstCase.CaseCount1 == 0 && currentCase.CaseCount1 != 0) && (firstCase.EventStop.Value.Hour == currentCase.EventStop.Value.Hour))
firstCase = currentCase;
//If there is a next case in array
if ((x + 1) < rows.Count)
{
//Next case
CaseCount nextCase = rows.ElementAt(x + 1);
//If next case Hour is greater than current
if ((nextCase.EventStop.Value.Hour > currentCase.EventStop.Value.Hour && nextCase.EventStop.Value.Day == currentCase.EventStop.Value.Day) || (nextCase.EventStop.Value.Hour < currentCase.EventStop.Value.Hour && nextCase.EventStop.Value.Day > currentCase.EventStop.Value.Day)/* || (x == rows.Count && currentCase.EventStop.Value.Subtract(firstCase.EventStop.Value).Minutes > 10)*/)
{
if (nextCase.EventStop.Value.Hour > currentCase.EventStop.Value.Hour && currentCase.Id == 0 && !currentCase.EventStart.HasValue)
{
var z = from o in rows
where o.EventStop.Value.Hour == currentCase.EventStop.Value.Hour
&& o.EventStop.Value.Day == currentCase.EventStop.Value.Day
select o;
int count = z.ToList().Count;
if (count >= 3)
{
currentCase = lastCase;
lastCase = rows.ElementAt(x - 2);
}
}
decimal value = 0;
decimal countDiff = lastCase.CaseCount1 - currentCase.CaseCount1;
if (countDiff < 0)
countDiff = countDiff * -1;
/*
if ((lastCase.CaseCount1 == 0 && currentCase.CaseCount1 != 0) && (lastCase.EventStop.Value.Hour == currentCase.EventStop.Value.Hour))
value = (decimal)((currentCase.CaseCount1 - firstCase.CaseCount1) / (decimal)estCount) * 100;
else if ((lastCase.CaseCount1 != 0 && currentCase.CaseCount1 == 0) && (lastCase.EventStop.Value.Hour == currentCase.EventStop.Value.Hour))
value = (decimal)((lastCase.CaseCount1 - firstCase.CaseCount1) / (decimal)estCount) * 100;
else
value = (decimal)((currentCase.CaseCount1 - firstCase.CaseCount1) / (decimal)estCount) * 100;
*/
try
{
int firstEst = getEstimatedCount(firstCase.EventStop.Value, Filter_Line);
int lastEst = getEstimatedCount(currentCase.EventStop.Value, Filter_Line);
decimal a = (decimal)currentCase.CaseCount1 / (decimal)lastEst;
decimal b = (decimal)firstCase.CaseCount1 / (decimal)firstEst;
if ((lastCase.CaseCount1 == 0 && currentCase.CaseCount1 != 0) && (lastCase.EventStop.Value.Hour == currentCase.EventStop.Value.Hour))
value = (a - b) * 100M;
else if ((lastCase.CaseCount1 != 0 && currentCase.CaseCount1 == 0) && (lastCase.EventStop.Value.Hour == currentCase.EventStop.Value.Hour))
value = (b - a) * 100M;
else
value = (a - b) * 100M;
}
catch (Exception)
{
value = 0;
}
if (value < 0)
value = value * -1;
string hour = string.Empty;
//Converts 24 and 0 to 12, also converts 24hrs to 12hr time.
if (currentCase.EventStop.Value.Hour < 12)
{
int t = currentCase.EventStop.Value.Hour;
if (t == 0)
t = 12;
hour = t + " AM";
}
else
{
int t = currentCase.EventStop.Value.Hour - 12;
if (t == 0)
t = 12;
hour = t + " PM";
}
//value = ((DateTime.Now.DayOfWeek == DayOfWeek.Monday && MillardDashboardTVHelper.getLightStatus() == false) ? 0 : value);
if (value < 1 && value > 0)
value = value * 100;//turn into decimal from percent
if (value >= 0)
{
/*
var sum = from r in rows
where r.EventStop.Value.Hour == currentCase.EventStop.Value.Hour
&& r.EventStop.Value.Day == currentCase.EventStop.Value.Day
group r by r.EventStop.Value.Hour into g
select new { Total = g.Sum(y => y.CaseCount1) }.Total;
List<int> list = sum.ToList();
currentCase.CaseCount1 = list.Max();
*/
int originalCount = currentCase.CaseCount1;
currentCase.CaseCount1 = Convert.ToInt32(estCount * (value / 100));
results.Add(currentCase, value);
}
}
}
else//assuming end of array
{
decimal value = 0;
decimal countDiff = lastCase.CaseCount1 - currentCase.CaseCount1;
if (countDiff < 0)
countDiff = countDiff * -1;
try
{
int firstEst = getEstimatedCount(firstCase.EventStop.Value, Filter_Line);
int lastEst = getEstimatedCount(currentCase.EventStop.Value, Filter_Line);
decimal a = (decimal)currentCase.CaseCount1 / (decimal)lastEst;
decimal b = (decimal)firstCase.CaseCount1 / (decimal)firstEst;
if ((lastCase.CaseCount1 == 0 && currentCase.CaseCount1 != 0) && (lastCase.EventStop.Value.Hour == currentCase.EventStop.Value.Hour))
value = (a - b) * 100M;
else if ((lastCase.CaseCount1 != 0 && currentCase.CaseCount1 == 0) && (lastCase.EventStop.Value.Hour == currentCase.EventStop.Value.Hour))
value = (b - a) * 100M;
else
value = (a - b) * 100M;
}
catch (Exception)
{
value = 0;
}
if (value < 0)
value = value * -1;
string hour = string.Empty;
//Converts 24 and 0 to 12, also converts 24hrs to 12hr time.
if (currentCase.EventStop.Value.Hour < 12)
{
int t = currentCase.EventStop.Value.Hour;
if (t == 0)
t = 12;
hour = t + " AM";
}
else
{
int t = currentCase.EventStop.Value.Hour - 12;
if (t == 0)
t = 12;
hour = t + " PM";
}
//value = ((DateTime.Now.DayOfWeek == DayOfWeek.Monday && MillardDashboardTVHelper.getLightStatus() == false) ? 0 : value);
if (value < 1 && value > 0)
value = value * 100;//turn into decimal from percent
if (value >= 0)
{
/*
var sum = from r in rows
where r.EventStop.Value.Hour == currentCase.EventStop.Value.Hour
&& r.EventStop.Value.Day == currentCase.EventStop.Value.Day
group r by r.EventStop.Value.Hour into g
select new { Total = g.Sum(y => y.CaseCount1) }.Total;
List<int> list = sum.ToList();
currentCase.CaseCount1 = list.Max();
*/
int originalCount = currentCase.CaseCount1;
currentCase.CaseCount1 = Convert.ToInt32(estCount * (value / 100));
results.Add(currentCase, value);
}
}
}
}
}
return results;
}
/// <summary>
/// Create a new CaseCount object.
/// </summary>
/// <param name="id">Initial value of the Id property.</param>
/// <param name="caseCount1">Initial value of the CaseCount1 property.</param>
public static CaseCount CreateCaseCount(global::System.Int32 id, global::System.Int32 caseCount1)
{
CaseCount caseCount = new CaseCount();
caseCount.Id = id;
caseCount.CaseCount1 = caseCount1;
return caseCount;
}
/// <summary>
/// Deprecated Method for adding a new object to the CaseCountSet EntitySet. Consider using the .Add method of the associated ObjectSet<T> property instead.
/// </summary>
public void AddToCaseCountSet(CaseCount caseCount)
{
base.AddObject("CaseCountSet", caseCount);
}
public static Dictionary<CaseCount, decimal> getGraphCaseCounts(DateTime? startTime, DateTime? endTime, string line, bool increments = true)
{
Dictionary<CaseCount, decimal> results = new Dictionary<CaseCount, decimal>();
DateTime tmpDate = startTime.Value;
int totalHours = endTime.Value.Subtract(startTime.Value).Hours;
int totalDays = endTime.Value.Subtract(startTime.Value).Days;
if (totalDays > 0)
totalHours = totalDays * 24;
for (double x = 0; x <= totalHours; x++)
{
CaseCount c = new CaseCount();
c.CaseCount1 = 0;
c.EventStop = new DateTime(tmpDate.Year, tmpDate.Month, tmpDate.Day, tmpDate.Hour, 30, 0);//Set to XX:30, so it won't interfere with actual casecounts (which uses end values in hour)
tmpDate = tmpDate.AddHours(1.00);
results.Add(c, 0);
}
/*
if (string.IsNullOrEmpty(line))
line = Filter_Line;
List<CaseCount> rows = new List<CaseCount>();
Dictionary<CaseCount, decimal> results = new Dictionary<CaseCount, decimal>();
List<ThroughputHistory> tpHistories = DCSDashboardDemoHelper.getThroughPutHistories(line);
List<Throughput> throughputs = DCSDashboardDemoHelper.GetThroughPuts(line);
using (DB db = new DB())
{
var q = from a in db.CaseCountSet
where (a.Client == Filter_Client || string.IsNullOrEmpty(Filter_Client))
&& (a.Line == line || string.IsNullOrEmpty(line))
&& (!startTime.HasValue || a.EventStop >= startTime)
&& (!endTime.HasValue || a.EventStop <= endTime)
orderby a.EventStop.Value ascending
select a;
if (q != null)
rows = q.ToList();
DateTime tmpDate = startTime.Value;
int totalHours = endTime.Value.Subtract(startTime.Value).Hours;
int totalDays = endTime.Value.Subtract(startTime.Value).Days;
if (totalDays > 0)
totalHours = totalDays * 24;
for (double x = 0; x <= totalHours; x++)
{
CaseCount c = new CaseCount();
c.EventStop = new DateTime(tmpDate.Year, tmpDate.Month, tmpDate.Day, tmpDate.Hour, 30, 0);//Set to XX:30, so it won't interfere with actual casecounts (which uses end values in hour)
c.CaseCount1 = 0;
List<CaseCount> cList = (from o in rows
where (o.EventStop.Value.Hour == tmpDate.Hour && o.EventStop.Value.DayOfYear == tmpDate.DayOfYear && o.EventStop.Value.Year == tmpDate.Year)
orderby o.EventStop.Value.Minute ascending
select o).ToList();
decimal count = 0;
if (cList.Count > 0)
{
if (increments)
{
CaseCount firstCase = cList.ElementAt(0);
CaseCount lastCase = cList.ElementAt(cList.Count - 1);
if (firstCase != null && lastCase != null)
{
c.EventStop = new DateTime(tmpDate.Year, tmpDate.Month, tmpDate.Day, tmpDate.Hour, 30, 0);
try
{
int tp1Id = DiamondCrystaldashboardHelper.GetThroughputIdFromReference(tpHistories.Where(o => o.Date <= c.EventStop.Value).OrderByDescending(o => o.Date).Select(o => o.ThroughputReference).FirstOrDefault());
int tp2Id = DiamondCrystaldashboardHelper.GetThroughputIdFromReference(tpHistories.Where(o => o.Date <= c.EventStop.Value).OrderByDescending(o => o.Date).Select(o => o.ThroughputReference).FirstOrDefault());
Throughput tp1 = (from o in throughputs
where o.Id == tp1Id
select o).FirstOrDefault();
Throughput tp2 = (from o in throughputs
where o.Id == tp2Id
select o).FirstOrDefault();
ThroughputHistory th1 = ( from o in tpHistories
where o.Date <= firstCase.EventStop.Value
orderby o.Id descending
select o).FirstOrDefault();
ThroughputHistory th2 = ( from o in tpHistories
where o.Date <= lastCase.EventStop.Value
orderby o.Id descending
select o).FirstOrDefault();
int totalCases = lastCase.CaseCount1 - firstCase.CaseCount1;
int totalEst = (tp2 == null ? getEstimatedCount(tmpDate, line) : tp2.PerHour);
if (th1 != null && th2 != null)
{
totalEst = tp2.PerHour;
if (th2.Date.Hour == lastCase.EventStop.Value.Hour && th2.Date.DayOfYear == lastCase.EventStop.Value.DayOfYear && th2.Date.Year == lastCase.EventStop.Value.Year)
{
CaseCount tmpCC1 = (from o in cList
where o.EventStop.Value <= th2.Date
orderby o.EventStop.Value descending
select o).FirstOrDefault();
CaseCount tmpCC2 = (from o in cList
where o.EventStop.Value >= th2.Date
orderby o.EventStop.Value ascending
select o).FirstOrDefault();
decimal min1 = (decimal)tmpCC1.EventStop.Value.Subtract(firstCase.EventStop.Value).TotalMinutes;
decimal min2 = (decimal)lastCase.EventStop.Value.Subtract(tmpCC2.EventStop.Value).TotalMinutes;
decimal cTp1 = (decimal)tp1.PerHour / 12m;
decimal cTp2 = (decimal)tp2.PerHour / 12m;
min1 = (min1 / 5) * cTp1;
min2 = (min2 / 5) * cTp2;
totalCases = (tmpCC1.CaseCount1 - firstCase.CaseCount1) + (lastCase.CaseCount1 - tmpCC2.CaseCount1);
totalEst = (int)(min1 + min2);
c.Line = totalCases + "/" + totalEst + "|" + tp1.Name + "|" + tp2.Name;
}
else
c.Line = totalCases + "/" + totalEst + "|" + tp1.Name;
}
else
c.Line = totalCases + "/" + totalEst;
if (totalEst == 0)
totalEst++;
count = ((decimal)totalCases / (decimal)totalEst) * 100M;
c.CaseCount1 = Convert.ToInt32(totalCases);
//(lastEst + firstEst) / (LEST + FEST)
//LEST = Case counts that should've been produced within the time frame between firstcase and lastcase
}
catch (Exception e)
{
count = 0;
}
if (count < 0)
count = count * -1;
//value = ((DateTime.Now.DayOfWeek == DayOfWeek.Monday && MillardDashboardTVHelper.getLightStatus() == false) ? 0 : value);
if (count >= 0)
{
/*
var sum = from r in rows
where r.EventStop.Value.Hour == currentCase.EventStop.Value.Hour
&& r.EventStop.Value.Day == currentCase.EventStop.Value.Day
group r by r.EventStop.Value.Hour into g
select new { Total = g.Sum(y => y.CaseCount1) }.Total;
List<int> list = sum.ToList();
currentCase.CaseCount1 = list.Max();
results.Add(c, count);
}
}
}
else
{
CaseCount lastCase = cList.ElementAt(cList.Count - 1);
if (lastCase != null)
{
decimal total = (from o in cList
select o.CaseCount1).Sum();
decimal lastEst = (decimal)getEstimatedCount(lastCase.EventStop.Value, line);
total = total / (lastEst != 0 ? lastEst : 1);
count = total * 100M;
c.CaseCount1 = Convert.ToInt32(total);
results.Add(c, count);
}
}
}
else
{
results.Add(c, 0);
}
tmpDate = tmpDate.AddHours(1.00);
}
rows = rows.OrderBy(x => x.EventStop.Value.Day).ThenBy(x => x.EventStop.Value.Hour).ThenBy(x => x.EventStop.Value.Minute).ThenBy(x => x.CaseCount1).ToList();
}
return results;
*/
return results;
}