public List <ConsolidateIndentData> GetConsolidatedIndentData(string year)
{
int approvedYear = DateTime.UtcNow.Year;
int.TryParse(year, out approvedYear);
using (var entity = new ProERPContext())
{
var allData = from a in entity.IndentDetails
where a.Indent.CreatedOn.Year == approvedYear
select new ConsolidateIndentData
{
PlantId = (a.Plant == null ? 0 : a.PlantId.Value),
PlantName = (a.Plant == null ? "NA" : a.Plant.Name),
RequisitionType = a.Indent.RequisitionType,
BudgetId = a.Indent.BudgetId,
BudgetType = a.Indent.IndentBudget.BudgetType,
BudgetCode = a.Indent.IndentBudget.BudgetCode,
//Expense = (a.PoAmount == null ? a.EstimatePrice : a.FinalPrice.Value) * (a.IssuedQty == null ? a.QtyNeeded : a.IssuedQty.Value),
Expense = (a.EstimatePrice) * (a.IssuedQty == null ? a.QtyNeeded : a.IssuedQty.Value),
IndentDate = a.Indent.CreatedOn,
TotalBudget = a.Indent.IndentBudget.Amount == null ? 0 : a.Indent.IndentBudget.Amount.Value
};
return(allData.ToList());
}
}
public List <IndentReportDataSet> GetIndentDetailById(int indentID)
{
ProERPContext db = new ProERPContext();
var allData = db.IndentDetails
.Where(w => w.IndentId == indentID)
.Select(s => new IndentReportDataSet
{
Id = s.Id,
PlantId = s.PlantId,
LineId = s.LineId,
LineName = s.Line.Name,
MachineId = s.MachineId,
MachineName = s.Machine.Name,
PlantName = s.Plant.Name,
Budget = s.Indent.IndentBudget.Amount,
BudgetCode = s.Indent.IndentBudget.BudgetCode,
BudgetType = s.Indent.IndentBudget.BudgetType,
IndentDate = s.Indent.CreatedOn,
IndentNo = s.Indent.IndentNo,
//.Where(w => w.EffectiveFrom <= s.IssuedOn).Where(w => w.EffectiveTo >= s.IssuedOn).Select(a => a.MonthlyBudget).FirstOrDefault(),
//UnitPrice = s.UnitPrice,
//TotalAmount = s.TotalAmount,
ItemId = s.ItemId,
ItemName = s.Item.Name,
ItemCode = s.Item.ItemCode,
QtyNeeded = s.QtyNeeded,
FinalPrice = s.FinalPrice,
IssuedQty = s.IssuedQty,
StatusId = s.StatusId,
IssuedOn = s.IssuedOn,
Description = s.Item.Description,
EstimatePrice = s.EstimatePrice,
InitiatedBy = s.Indent.User.UserName,
Supplier = s.Vendor.Name,
Note = s.Indent.Note,
RequiredByDate = s.RequiredByDate,
UnitOfMeasure = s.Item == null ? s.UnitOfMeasure : s.Item.UnitOfMeasure,
Location = (s.Plant == null ? "General" : (s.Plant.Name + (s.Line == null ? "" : "->" + s.Line.Name)
+ (s.Machine == null ? "" : "->" + s.Machine.Name))),
Make = s.Make,
JobDescription = s.JobDescription
});
return(allData.OrderBy(o => o.IssuedOn).OrderBy(o => o.PlantName).ToList());
}
public List <IndentReportDataSet> GetIssuedIndentDetail(int plantId, string year)
{
ProERPContext db = new ProERPContext();
int approvedYear = DateTime.UtcNow.Year;
int.TryParse(year, out approvedYear);
//int[] budgetIds = db.IndentDetails.Where(w => w.IndentStatu.Description == "Approved")
// .Where(w => w.ApprovedOn.Value.Year == approvedYear)
// .Where(w => w.PlantId == plantId)
// .Select(s => s.Indent.BudgetId).ToArray();
// var totalBudget = db.IndentBudgets.Where(w => budgetIds.Contains(w.Id)).Sum(s => s.Amount);
var allData = db.IndentDetails.Where(w => w.IndentStatu.Description == "Approved")
.Where(w => w.ApprovedOn.Value.Year == approvedYear)
.Where(w => w.PlantId == plantId)
.Select(s => new IndentReportDataSet
{
Id = s.Id,
PlantId = s.PlantId,
LineId = s.LineId,
LineName = s.Line.Name,
MachineId = s.MachineId,
MachineName = s.Machine.Name,
PlantName = s.Plant.Name,
//Budget = totalBudget,
BudgetId = s.Indent.BudgetId,
Budget = s.Indent.IndentBudget.Amount,
BudgetType = s.Indent.IndentBudget.BudgetType,
BudgetCode = s.Indent.IndentBudget.BudgetCode,
//.Where(w => w.EffectiveFrom <= s.IssuedOn).Where(w => w.EffectiveTo >= s.IssuedOn).Select(a => a.MonthlyBudget).FirstOrDefault(),
//UnitPrice = s.UnitPrice,
//TotalAmount = s.TotalAmount,
ItemId = s.ItemId,
ItemName = s.Item != null ? s.Item.Name : s.JobDescription,
QtyNeeded = s.QtyNeeded,
IssuedQty = s.IssuedQty == null ? s.QtyNeeded : s.IssuedQty,
FinalPrice = s.FinalPrice == null ? s.EstimatePrice : s.FinalPrice,
StatusId = s.StatusId,
IssuedOn = s.IssuedOn == null ? s.RequiredByDate : s.IssuedOn,
RequisitionType = s.Indent.RequisitionType
});
return(allData.OrderBy(o => o.IssuedOn).OrderBy(o => o.PlantName).ToList());
}
public List <BreakDownReportDataSet> GetRDLCData(int siteId, int plantId, int lineId, DateTime fromDate, DateTime toDate)
{
ProERPContext db = new ProERPContext();
var allData = db.BreakDowns.Where(w => w.PlantId == plantId && w.LineId == lineId && w.Date >= fromDate && w.Date <= toDate && w.IsDeleted == false).Select(s => new { MachineName = s.Machine1.Name, SubAssemblyName = s.Machine == null ? "" : s.Machine.Name, SpareTypeName = s.SpareTypeId == null ? "" : s.SpareTypeId == 1 ? "Service" : "Man Power", BD = s }).ToArray();
var data = from a in allData
select new BreakDownReportDataSet
{
Id = a.BD.Id,
PlantId = a.BD.PlantId,
LineId = a.BD.LineId,
MachineId = a.BD.MachineId,
MachineName = a.MachineName,
SubAssemblyId = a.BD.SubAssemblyId ?? 0,
SubAssemblyName = a.SubAssemblyName,
Date = a.BD.Date,
StartTime = DateTime.Now.Date + a.BD.StartTime,
StopTime = DateTime.Now.Date + a.BD.EndTime,
TotalTime = a.BD.TotalTime,
FailureDescription = a.BD.FailureDescription,
ElectricalTime = a.BD.ElectricalTime,
MechTime = a.BD.MechTime,
InstrTime = a.BD.InstrTime,
UtilityTime = a.BD.UtilityTime,
PowerTime = a.BD.PowerTime,
ProcessTime = a.BD.ProcessTime,
PrvTime = a.BD.PrvTime,
IdleTime = a.BD.IdleTime,
ResolveTimeTaken = DateTime.Now.Date + (a.BD.ResolveTimeTaken ?? new TimeSpan(0)),
SpareTypeId = a.BD.SpareTypeId ?? 0,
SpareTypeName = a.SpareTypeName,
SpareDescription = a.BD.SpareDescription,
DoneBy = a.BD.DoneBy,
RootCause = a.BD.RootCause,
Correction = a.BD.Correction,
CorrectiveAction = a.BD.CorrectiveAction,
PreventingAction = a.BD.PreventingAction
};
return(data.ToList());
}
//public List<BreakDownMonthlySummaryDataSet> GetBreakdownDataGroupByType(int? siteId, int? plantId, int? lineId, DateTime fromDate, DateTime toDate)
public List <BreakDownMonthlySummaryDataSet> GetBreakdownDataGroupByType(int siteId, int?plantId, int?lineId, string year, int month)
{
ProERPContext db = new ProERPContext();
// var allData = db.BreakDowns.Where(w => w.Date >= fromDate && w.Date <= toDate).Select(s => new { MachineName = s.Machine1.Name, SubAssemblyName = s.Machine == null ? "" : s.Machine.Name, SpareTypeName = s.SpareTypeId == null ? "" : s.SpareTypeId == 1 ? "Service" : "Man Power", BD = s }).ToList();
var allData = db.BreakDowns.Where(w => w.Date.Year.ToString() == year && w.Date.Month == (month == 0 ? w.Date.Month : month) && w.IsDeleted == false).Select(s => new { MachineName = s.Machine1.Name, SubAssemblyName = s.Machine == null ? "" : s.Machine.Name, SpareTypeName = s.SpareTypeId == null ? "" : s.SpareTypeId == 1 ? "Service" : "Man Power", BD = s }).ToList();
if (lineId != null && lineId > 0)
{
allData = allData.Where(w => w.BD.LineId == lineId).ToList();
}
else if (plantId != null && plantId > 0)
{
allData = allData.Where(w => w.BD.PlantId == plantId).ToList();
}
var data = from a in allData
select new BreakDownMonthlySummaryDataSet
{
Date = a.BD.Date,
DaysInMonth = DateTime.DaysInMonth(a.BD.Date.Year, a.BD.Date.Month),
TotalTime = a.BD.TotalTime,
PlantName = a.BD.Plant.Name,
LineName = a.BD.Line.Name,
FailureDescription = a.BD.FailureDescription,
BreakDownType = (a.BD.ElectricalTime ? "Electrical" :
a.BD.MechTime ? "Mechanical" :
a.BD.InstrTime ? "Instrumentation" :
a.BD.UtilityTime ? "Utility" :
a.BD.PowerTime ? "Power" :
a.BD.ProcessTime ? "Process" :
a.BD.PrvTime ? "PRV" :
a.BD.IdleTime ? "Idle" : "")
};
return(data.OrderBy(o => o.Date).OrderBy(o => o.LineName).ToList());
}
private void ProcessHourlySchedules()
{
try
{
using (ProERPContext context = new ProERPContext())
{
var dateTime = DateTime.Now;
//var today = new DateTime(dateTime.Ticks - (dateTime.Ticks % TimeSpan.TicksPerSecond),dateTime.Kind);
var today = new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, dateTime.Hour, 0, 0);
var schedules = context.PreventiveMaintenances.Where(w => !w.IsDeleted && w.ScheduleType == 6 && w.NextReviewDate <= today).ToArray();
for (int i = 0; i < schedules.Length; i++)
{
var schedule = schedules[i];
var line = context.Lines.FirstOrDefault(a => a.Id == schedule.LineId);
var machine = context.Machines.FirstOrDefault(a => a.Id == schedule.MachineId);
if (line == null || machine == null)
{
continue;
}
if (!context.PreventiveReviewHistories.Any(a => a.PreventiveId == schedule.Id && a.ScheduledReviewDate == schedule.NextReviewDate))
{
context.PreventiveReviewHistories.Add(new Data.Models.PreventiveReviewHistory {
PreventiveId = schedule.Id, Notes = null, ReviewBy = null, ReviewDate = null, ScheduledReviewDate = schedule.NextReviewDate, IsLaps = false, IsOverdue = false, IsLineActive = line.IsActive, IsMachineActive = machine.IsActive
});
}
}
context.SaveChanges();
// Overdues
var overdueSchedules = context.PreventiveMaintenances.Where(w => !w.IsDeleted && w.ScheduleType == 6 && w.NextReviewDate < today).ToArray();
for (int i = 0; i < overdueSchedules.Length; i++)
{
var schedule = overdueSchedules[i];
var newNextReviewDate = DateTime.Now.Date;
var existingReview = context.PreventiveReviewHistories.FirstOrDefault(a => a.PreventiveId == schedule.Id && a.ScheduledReviewDate == schedule.NextReviewDate);
var isLaps = false;
var isOverDue = false;
// Hourly
newNextReviewDate = schedule.NextReviewDate.AddHours(1 * (schedule.Interval));
if ((today - schedule.NextReviewDate).Hours >= ((newNextReviewDate - schedule.NextReviewDate).TotalHours))
{
isOverDue = true;
}
if ((today - schedule.NextReviewDate).Hours > ((newNextReviewDate - schedule.NextReviewDate).TotalHours + 4))
{
isLaps = true;
}
if (existingReview != null)
{
if (existingReview.ReviewBy == null)
{
existingReview.IsLaps = isLaps;
existingReview.IsOverdue = isOverDue;
}
}
if (isLaps)
{
schedule.NextReviewDate = newNextReviewDate;
var line = context.Lines.FirstOrDefault(a => a.Id == schedule.LineId);
var machine = context.Machines.FirstOrDefault(a => a.Id == schedule.MachineId);
if (line != null || machine != null)
{
context.PreventiveReviewHistories.Add(new Data.Models.PreventiveReviewHistory {
PreventiveId = schedule.Id, Notes = null, ReviewBy = null, ReviewDate = null, ScheduledReviewDate = schedule.NextReviewDate, IsLaps = false, IsOverdue = false, IsLineActive = line.IsActive, IsMachineActive = machine.IsActive
});
}
}
}
context.SaveChanges();
}
}
catch (Exception ex)
{
throw ex;
}
}
public void Start()
{
ProERPContext context;
try
{
ProcessHourlySchedules();
context = new ProERPContext();
var today = DateTime.Now.Date;
var schedules = context.PreventiveMaintenances.Where(w => !w.IsDeleted && w.ScheduleType != 5 && w.ScheduleType != 6 && w.NextReviewDate <= today).ToArray();
for (int i = 0; i < schedules.Length; i++)
{
var schedule = schedules[i];
var line = context.Lines.FirstOrDefault(a => a.Id == schedule.LineId);
var machine = context.Machines.FirstOrDefault(a => a.Id == schedule.MachineId);
if (line == null || machine == null)
{
continue;
}
if (!context.PreventiveReviewHistories.Any(a => a.PreventiveId == schedule.Id && a.ScheduledReviewDate == schedule.NextReviewDate))
{
context.PreventiveReviewHistories.Add(new Data.Models.PreventiveReviewHistory {
PreventiveId = schedule.Id, Notes = null, ReviewBy = null, ReviewDate = null, ScheduledReviewDate = schedule.NextReviewDate, IsLaps = false, IsOverdue = false, IsLineActive = line.IsActive, IsMachineActive = machine.IsActive
});
}
}
context.SaveChanges();
// Overdues
var overdueSchedules = context.PreventiveMaintenances.Where(w => !w.IsDeleted && w.ScheduleType != 5 && w.ScheduleType != 6 && w.NextReviewDate < today).ToArray();
for (int i = 0; i < overdueSchedules.Length; i++)
{
var schedule = overdueSchedules[i];
var newNextReviewDate = DateTime.Now.Date;
var existingReview = context.PreventiveReviewHistories.FirstOrDefault(a => a.PreventiveId == schedule.Id && a.ScheduledReviewDate == schedule.NextReviewDate);
var isLaps = false;
var isOverDue = false;
if (schedule.ScheduleType == 1) // Daily
{
newNextReviewDate = schedule.NextReviewDate.AddDays(1 * (schedule.Interval));
if ((today - schedule.NextReviewDate).Days > 0)
{
isLaps = true;
}
}
else if (schedule.ScheduleType == 2) // Weekly
{
newNextReviewDate = schedule.NextReviewDate.AddDays(7 * (schedule.Interval));
if ((today - schedule.NextReviewDate).Days >= 7)
{
isOverDue = true;
}
if ((today - schedule.NextReviewDate).Days > 9)
{
isLaps = true;
}
}
else if (schedule.ScheduleType == 3 && schedule.Interval < 6) // Monthly
{
newNextReviewDate = schedule.NextReviewDate.AddMonths(1 * (schedule.Interval));
if ((today - schedule.NextReviewDate).Days >= ((newNextReviewDate - schedule.NextReviewDate).Days))
{
isOverDue = true;
}
if ((today - schedule.NextReviewDate).Days > ((newNextReviewDate - schedule.NextReviewDate).Days + 7))
{
isLaps = true;
}
}
else if (schedule.ScheduleType == 3 && schedule.Interval >= 6) // Monthly
{
newNextReviewDate = schedule.NextReviewDate.AddMonths(1 * (schedule.Interval));
if ((today - schedule.NextReviewDate).Days >= ((newNextReviewDate - schedule.NextReviewDate).Days))
{
isOverDue = true;
}
if ((today - schedule.NextReviewDate).Days > ((newNextReviewDate - schedule.NextReviewDate).Days + 10))
{
isLaps = true;
}
}
else if (schedule.ScheduleType == 4) // Yearly
{
newNextReviewDate = schedule.NextReviewDate.AddYears(1 * (schedule.Interval));
if ((today - schedule.NextReviewDate).Days >= ((newNextReviewDate - schedule.NextReviewDate).Days))
{
isOverDue = true;
}
if ((today - schedule.NextReviewDate).Days > ((newNextReviewDate - schedule.NextReviewDate).Days + 10))
{
isLaps = true;
}
}
//if (existingReview == null)
//{
// context.PreventiveReviewHistories.Add(new Data.Models.PreventiveReviewHistory { PreventiveId = schedule.Id, Notes = null, ReviewBy = null, ReviewDate = null, ScheduledReviewDate = schedule.NextReviewDate, IsLaps = isLaps, IsOverdue = isOverDue });
//}
//else
if (existingReview != null)
{
if (existingReview.ReviewBy == null)
{
existingReview.IsLaps = isLaps;
existingReview.IsOverdue = isOverDue;
}
}
if (isLaps)
{
//existingReview.IsLaps = true;
schedule.NextReviewDate = newNextReviewDate;
var line = context.Lines.FirstOrDefault(a => a.Id == schedule.LineId);
var machine = context.Machines.FirstOrDefault(a => a.Id == schedule.MachineId);
if (line != null || machine != null)
{
context.PreventiveReviewHistories.Add(new Data.Models.PreventiveReviewHistory {
PreventiveId = schedule.Id, Notes = null, ReviewBy = null, ReviewDate = null, ScheduledReviewDate = schedule.NextReviewDate, IsLaps = false, IsOverdue = false, IsLineActive = line.IsActive, IsMachineActive = machine.IsActive
});
}
}
}
context.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
finally
{
context = null;
}
}
public List <HistoryCardDataSet> GetHistoryReportGridData(int siteId, int plantId, int lineId, int?machineId, DateTime fromDate, DateTime toDate, bool?isHistory)
{
ProERPContext db = new ProERPContext();
var allData = db.BreakDowns.Where(w => w.PlantId == plantId && w.LineId == lineId && w.Date >= fromDate && w.Date <= toDate && w.IsDeleted == false && w.IsHistory == ((isHistory == null) ? w.IsHistory : isHistory)).Select(s => new
{
MachineName = s.Machine1.Name,
SubAssemblyName = s.Machine == null ? "" : s.Machine.Name,
SpareTypeName = s.SpareTypeId == null ? "" : s.SpareTypeId == 1 ? "Service" : "Man Power",
BD = s
}).ToArray();
if (machineId != null && machineId > 0)
{
allData = allData.Where(w => w.BD.MachineId == machineId).ToArray();
}
int[] breakDownIds = allData.Select(s => s.BD.Id).Distinct().ToArray();
var allMenPower = db.BreakDownMenPowers.Where(w => breakDownIds.Contains(w.BreakDownId)).ToArray();
var allServices = db.BreakDownServices.Where(w => breakDownIds.Contains(w.BreakDownId)).Select(s => new { s.VendorName, s.BreakDownId }).ToArray();
var allParts = db.BreakDownSpares.Where(w => breakDownIds.Contains(w.BreakDownId)).Select(s => new { s.Part.Name, s.Quantity, s.BreakDownId }).ToArray();
var allAttachments = db.BreakDownAttachments.Where(w => breakDownIds.Contains(w.BreakDownId)).Select(s => new { s.OriginalFileName, s.BreakDownId }).ToArray();
var gridData = from a in allData
orderby a.BD.Date
select new HistoryCardDataSet
{
Id = a.BD.Id,
PlantId = a.BD.PlantId,
LineId = a.BD.LineId,
LineName = a.BD.Line.Name,
MachineId = a.BD.MachineId,
MachineName = a.MachineName,
SubAssemblyId = a.BD.SubAssemblyId ?? 0,
SubAssemblyName = a.SubAssemblyName,
Date = a.BD.Date,
StartTime = DateTime.Now.Date + a.BD.StartTime,
StopTime = DateTime.Now.Date + a.BD.EndTime,
TotalTime = a.BD.TotalTime,
FailureDescription = a.BD.FailureDescription,
ElectricalTime = a.BD.ElectricalTime,
MechTime = a.BD.MechTime,
InstrTime = a.BD.InstrTime,
UtilityTime = a.BD.UtilityTime,
PowerTime = a.BD.PowerTime,
ProcessTime = a.BD.ProcessTime,
PrvTime = a.BD.PrvTime,
IdleTime = a.BD.IdleTime,
ResolveTimeTaken = DateTime.Now.Date + (a.BD.ResolveTimeTaken ?? new TimeSpan(0)),
SpareTypeId = a.BD.SpareTypeId ?? 0,
SpareTypeName = a.SpareTypeName,
SpareDescription = a.BD.SpareDescription,
DoneBy = a.BD.DoneBy,
RootCause = a.BD.RootCause,
Correction = a.BD.Correction,
CorrectiveAction = a.BD.CorrectiveAction,
PreventingAction = a.BD.PreventingAction,
MenPowerUsed = string.Join(", ", allMenPower.Where(w => w.BreakDownId == a.BD.Id).Select(s => s.Name)),
ServiceUsed = string.Join(", ", allServices.Where(w => w.BreakDownId == a.BD.Id).Select(s => s.VendorName)),
PartsUsed = string.Join(", ", allParts.Where(w => w.BreakDownId == a.BD.Id).Select(s => s.Name + " (" + s.Quantity + ") ")),
AttachmentFile = string.Join(", ", allAttachments.Where(w => w.BreakDownId == a.BD.Id).Select(s => s.OriginalFileName))
};
return(gridData.ToList());
}
public string getPlantNameBylantId(int plantId)
{
ProERPContext db = new ProERPContext();
return(db.Plants.Where(w => w.Id == plantId).Select(s => s.Name).FirstOrDefault());
}
public List <string> getLineNamesByPlantId(int plantId)
{
ProERPContext db = new ProERPContext();
return(db.Lines.Where(w => w.PlantId == plantId).Select(s => s.Name).ToList());
}
