public ActionResult Reporting_ExportDowntime(ReportDataEntryModels _reportDataEntryModels)
{
var fName = string.Format("Reporting-Downtime-{0}", DateTime.Now.ToString("s"));
var data = _IManageDowntime.GetReporting_ManageDowntime(_reportDataEntryModels);
StringBuilder text = new StringBuilder();
//text.Append("Created By:" + "," + User.Identity.Name + "," + "Created Date:" + "," + DateTime.Now + "," + "\r\n");
text.Append("Assets, Status, From, To, Total Time, Reason, \r\n");
if (data.Count() > 0)
{
foreach (var record in data)
{
text.Append(record.Assets + "," + record.Status + "," + record.From + "," + record.To + "," + ConvertSecToHour(record.Time_Diff.ToString()) + "," + record.Reason + "\r\n");
}
text.Append("\r\nTotal Downtime" + "," + ConvertSecToHour(data.Sum(x => x.Time_Diff).ToString()));
var total = data.Sum(x => x.Time_Diff);
var calculateRuntime = runtime(_reportDataEntryModels, total ?? 0);
text.Append("\r\nTotal Runtime" + "," + calculateRuntime);
}
else
{
text.Append("No Data Available");
}
byte[] fileContents = Encoding.UTF8.GetBytes(text.ToString());
return(File(fileContents, "text/csv", fName + ".csv"));
}
public string runtime(ReportDataEntryModels _reportDataEntryModels, int total)
{
DateTime now = DateTime.Now;
var lastHour = now.AddHours(-24);
var lastWeek = now.AddDays(-7);
var lastMonth = now.AddMonths(-1);
int diff = 0;
if (_reportDataEntryModels.Hours == "on")
{
TimeSpan duration = now.Subtract(lastHour);
diff = Convert.ToInt32(duration.TotalSeconds) - total;
}
else if (_reportDataEntryModels.Weeks == "on")
{
TimeSpan duration = now.Subtract(lastWeek);
diff = Convert.ToInt32(duration.TotalSeconds) - total;
}
else if (_reportDataEntryModels.Monthly == "on")
{
TimeSpan duration = now.Subtract(lastMonth);
diff = Convert.ToInt32(duration.TotalSeconds) - total;
}
else if (_reportDataEntryModels.From != null && _reportDataEntryModels.To != null)
{
var FromDate = !string.IsNullOrWhiteSpace(_reportDataEntryModels.From) ? Convert.ToDateTime(DateTime.ParseExact(_reportDataEntryModels.From, "dd/MM/yyyy", CultureInfo.InvariantCulture)
.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture)) : DateTime.Now;
var ToDate = !string.IsNullOrWhiteSpace(_reportDataEntryModels.To) ? Convert.ToDateTime(DateTime.ParseExact(_reportDataEntryModels.To, "dd/MM/yyyy", CultureInfo.InvariantCulture)
.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture)) : DateTime.Now;
ToDate = ToDate.AddHours(24);
TimeSpan duration = ToDate.Subtract(FromDate);
var currentDate = DateTime.Now;
if (FromDate.Day == currentDate.Day && FromDate.Month == currentDate.Month && FromDate.Year == currentDate.Year)
{
//DateTime dt = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, 7, 0, 0);
int convertSecs = Convert.ToInt32(currentDate.TimeOfDay.TotalSeconds);
diff = convertSecs - total;
}
else
{
diff = Convert.ToInt32(duration.TotalSeconds) - total;
}
}
var result = ConvertSecToHour(diff.ToString());
return(result);
}
public JsonResult ExcelSearchParam(ReportDataEntryModels _reportDataEntryModels)
{
dynamic result = "";
try
{
result = _IManageDataEntry.GetDataEntryReport(_reportDataEntryModels);
}
catch (Exception ex)
{
Common.LogError(ex);
result = null;
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
public JsonResult Reporting_ManageDowntime(ReportDataEntryModels _reportDataEntryModels)
{
dynamic result = "";
try
{
result = _IManageDowntime.GetReporting_ManageDowntime(_reportDataEntryModels);
}
catch (Exception ex)
{
Common.LogError(ex);
result = null;
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
public JsonResult ExcelSearchTotal(ReportDataEntryModels _reportDataEntryModels)
{
var data = _IManageDataEntry.GetDataEntryReport(_reportDataEntryModels);
if (data.Count() > 0)
{
OPS_DataEntry _objTotal = new OPS_DataEntry();
_objTotal.CV01 = data.Sum(x => Convert.ToDecimal(x.CV01)).ToString();
_objTotal.Rougher = data.Sum(x => Convert.ToDecimal(x.Rougher)).ToString();
_objTotal.HMC = data.Sum(x => Convert.ToDecimal(x.HMC)).ToString();
_objTotal.FlocUse = data.Sum(x => Convert.ToDecimal(x.FlocUse)).ToString();
_objTotal.FlocBatch = string.Empty;
_objTotal.Slime = data.Sum(x => Convert.ToDecimal(x.Slime)).ToString();
_objTotal.SlimePer = data.Sum(x => Convert.ToDecimal(x.SlimePer)).ToString();
_objTotal.Tails1 = data.Sum(x => Convert.ToDecimal(x.Tails1)).ToString();
_objTotal.Tails2 = data.Sum(x => Convert.ToDecimal(x.Tails2)).ToString();
return(Json(_objTotal, JsonRequestBehavior.AllowGet));
}
else
{
return(Json("0", JsonRequestBehavior.AllowGet));
}
}
public ActionResult ExportToExcel(ReportDataEntryModels _reportDataEntryModels)
{
var fName = string.Format("Downtime-{0}", DateTime.Now.ToString("s"));
var data = _IManageDowntime.GetAllManageDowntime();
if (_reportDataEntryModels.ddl_Assets > 0)
{
data = _IManageDowntime.GetManageDowntime_ExcelSearch(_reportDataEntryModels.ddl_Assets, _reportDataEntryModels.searchDate);
}
StringBuilder text = new StringBuilder();
//text.Append("Created By:" + "," + User.Identity.Name + "," + "Created Date:" + "," + DateTime.Now + "," + "\r\n");
text.Append("Assets, Status, From, To, Total Time, Reason, \r\n");
foreach (var record in data)
{
text.Append(record.Assets + "," + record.Status + "," + record.From + "," + record.To + "," + ConvertSecToHour(record.Time_Diff.ToString()) + "," + record.Reason + "\r\n");
}
byte[] fileContents = Encoding.UTF8.GetBytes(text.ToString());
return(File(fileContents, "text/csv", fName + ".csv"));
}
public ActionResult ExportToExcelParam(ReportDataEntryModels _reportDataEntryModels)
{
var fName = string.Format("Reporting-{0}", DateTime.Now.ToString("s"));
var data = _IManageDataEntry.GetDataEntryReport(_reportDataEntryModels);
string shift = string.Empty;
StringBuilder text = new StringBuilder();
if (_reportDataEntryModels.SelectedShifts == "1")
{
shift = "Day Shift";
}
else if (_reportDataEntryModels.SelectedShifts == "2")
{
shift = "Night Shift";
}
// Start Shift Data Report //
//if (_reportDataEntryModels.SelectedShifts != "0")
//{
// Response.Write("Shift:" + "," + shift + ", " + "Created By:" + "," + User.Identity.Name + "," + "Created Date:" + "," + DateTime.Now + "," + "\r\n");
// text.Append("Shift:" + "," + shift + ", " + "Created By:" + "," + User.Identity.Name + "," + "Created Date:" + "," + DateTime.Now + "," + "\r\n");
//}
//else
//{
// Response.Write("Created By:" + "," + User.Identity.Name + "," + "Created Date:" + "," + DateTime.Now + "," + "\r\n");
// text.Append("Created By:" + "," + User.Identity.Name + "," + "Created Date:" + "," + DateTime.Now + "," + "\r\n");
//}
text.Append("ShiftData" + "," + "\r\n");
text.Append("ShiftTime, CV01, Rougher, HMC, FlocUse, FlocBatch, Slime, SlimePer, Tails1, Tails2, Reason, \r\n");
if (data.Count() > 0)
{
foreach (var record in data)
{
text.Append(record.ShiftTime + "," + record.CV01 + "," + record.Rougher + "," + record.HMC + "," + record.FlocUse + "," + record.FlocBatch + "," + record.Slime + "," + record.SlimePer + "," + record.Tails1 + "," + record.Tails2 + "," + record.Reason + "," + "\r\n");
}
text.Append("Total" + "," + data.Sum(x => Convert.ToDecimal(x.CV01)) + "," + data.Sum(x => Convert.ToDecimal(x.Rougher)) + "," + data.Sum(x => Convert.ToDecimal(x.HMC)) + "," + data.Sum(x => Convert.ToDecimal(x.FlocUse)) + "," + string.Empty + "," + data.Sum(x => Convert.ToDecimal(x.Slime)) + "," + data.Sum(x => Convert.ToDecimal(x.SlimePer)) + "," + data.Sum(x => Convert.ToDecimal(x.Tails1)) + "," + data.Sum(x => Convert.ToDecimal(x.Tails2)) + "," + "\r\n");
}
else
{
text.Append("No Data Available");
}
text.Append(Environment.NewLine);
text.Append(Environment.NewLine);
// End Shift Data Report //
/*
* var addSecs = 60 * 60;
* int total = data.Count();
* total = total * addSecs;
*
* int CV01 = data.Where(x => x.CV01 != null || x.CV01 != string.Empty).ToList().Count() * addSecs;
* int Rougher = data.Where(x => x.Rougher != null || x.Rougher != string.Empty).ToList().Count() * addSecs;
* int HMC = data.Where(x => x.HMC != null || x.HMC != string.Empty).ToList().Count() * addSecs;
* int FlocUse = data.Where(x => x.FlocUse != null || x.FlocUse != string.Empty).ToList().Count() * addSecs;
* int Slime = data.Where(x => x.Slime != null || x.Slime != string.Empty).ToList().Count() * addSecs;
* int SlimePer = data.Where(x => x.SlimePer != null || x.SlimePer != string.Empty).ToList().Count() * addSecs;
* int Tails1 = data.Where(x => x.Tails1 != null || x.Tails1 != string.Empty).ToList().Count() * addSecs;
* int Tails2 = data.Where(x => x.Tails2 != null || x.Tails2 != string.Empty).ToList().Count() * addSecs;
*
* text.Append("Total Downtime" + "," + ConvertSecToHour(Convert.ToString(total - CV01)) + "," + ConvertSecToHour(Convert.ToString(total - Rougher)) + "," + ConvertSecToHour(Convert.ToString(total - HMC)) + "," + ConvertSecToHour(Convert.ToString(total - FlocUse)) + "," + string.Empty + "," + ConvertSecToHour(Convert.ToString(total - Slime)) + "," + ConvertSecToHour(Convert.ToString(total - SlimePer)) + "," + ConvertSecToHour(Convert.ToString(total - Tails1)) + "," + ConvertSecToHour(Convert.ToString(total - Tails2)) + "," + "\r\n");
*
* text.Append("Total Runtime" + "," + ConvertSecToHour(Convert.ToString(CV01)) + "," + ConvertSecToHour(Convert.ToString(Rougher)) + "," + ConvertSecToHour(Convert.ToString(HMC)) + "," + ConvertSecToHour(Convert.ToString(FlocUse)) + "," + string.Empty + "," + ConvertSecToHour(Convert.ToString(Slime)) + "," + ConvertSecToHour(Convert.ToString(SlimePer)) + "," + ConvertSecToHour(Convert.ToString(Tails1)) + "," + ConvertSecToHour(Convert.ToString(Tails2)) + "," + "\r\n");
*/
// Start Downtime Report //
text.Append("Downtime" + "," + "\r\n");
var ddata = _IManageDowntime.GetReporting_ManageDowntime(_reportDataEntryModels);
text.Append("Assets, Status, From, To, Total Time, Reason, \r\n");
if (ddata.Count() > 0)
{
foreach (var record in ddata)
{
text.Append(record.Assets + "," + record.Status + "," + record.From + "," + record.To + "," + ConvertSecToHour(record.Time_Diff.ToString()) + "," + record.Reason + "\r\n");
}
text.Append("\r\nTotal Downtime" + "," + ConvertSecToHour(ddata.Sum(x => x.Time_Diff).ToString()));
var total = ddata.Sum(x => x.Time_Diff);
var calculateRuntime = runtime(_reportDataEntryModels, total ?? 0);
text.Append("\r\nTotal Runtime" + "," + calculateRuntime);
}
else
{
text.Append("No Data Available");
}
// End Downtime Report //
byte[] fileContents = Encoding.UTF8.GetBytes(text.ToString());
return(File(fileContents, "text/csv", fName + ".csv"));
}
public List <OPS_ManageDowntime> GetReporting_ManageDowntime(ReportDataEntryModels _reportDataEntryModels)
{
DateTime now = DateTime.Now;
var lastHour = now.AddHours(-24);
var lastWeek = now.AddDays(-7);
var lastMonth = now.AddMonths(-1);
var startDate = !string.IsNullOrWhiteSpace(_reportDataEntryModels.From) ? Convert.ToDateTime(DateTime.ParseExact(_reportDataEntryModels.From, "dd/MM/yyyy", CultureInfo.InvariantCulture)
.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture)) : DateTime.Now;
var endDate = !string.IsNullOrWhiteSpace(_reportDataEntryModels.To) ? Convert.ToDateTime(DateTime.ParseExact(_reportDataEntryModels.To, "dd/MM/yyyy", CultureInfo.InvariantCulture)
.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture)) : DateTime.Now;
using (opsdbEntities _dbContext = new opsdbEntities())
{
dynamic result;
int MH = Convert.ToInt32(ConfigurationManager.AppSettings["MorningShift"]);
int EH = Convert.ToInt32(ConfigurationManager.AppSettings["EveningShift"]);
if (_reportDataEntryModels.Hours == "on")
{
if (_reportDataEntryModels.SelectedShifts == "1")
{
result = _dbContext.OPS_ManageDowntime.Where(x => (x.FromDate > lastHour && x.FromDate <= now) && (x.FromDate.Value.Hour < EH && x.FromDate.Value.Hour >= MH)).ToList();
}
else if (_reportDataEntryModels.SelectedShifts == "2")
{
result = _dbContext.OPS_ManageDowntime.Where(x => (x.FromDate > lastHour && x.FromDate <= now) && (x.FromDate.Value.Hour >= EH && x.FromDate.Value.Hour < MH)).ToList();
}
else
{
result = _dbContext.OPS_ManageDowntime.Where(x => (x.FromDate > lastHour && x.FromDate <= now)).ToList();
}
}
else if (_reportDataEntryModels.Weeks == "on")
{
if (_reportDataEntryModels.SelectedShifts == "1")
{
result = _dbContext.OPS_ManageDowntime.Where(x => (x.FromDate > lastWeek && x.FromDate <= now) && (x.FromDate.Value.Hour < EH && x.FromDate.Value.Hour >= MH)).ToList();
}
else if (_reportDataEntryModels.SelectedShifts == "2")
{
result = _dbContext.OPS_ManageDowntime.Where(x => (x.FromDate > lastWeek && x.FromDate <= now) && (x.FromDate.Value.Hour >= EH && x.FromDate.Value.Hour < MH)).ToList();
}
else
{
result = _dbContext.OPS_ManageDowntime.Where(x => (x.FromDate > lastWeek && x.FromDate <= now)).ToList();
}
}
else if (_reportDataEntryModels.Monthly == "on")
{
if (_reportDataEntryModels.SelectedShifts == "1")
{
result = _dbContext.OPS_ManageDowntime.Where(x => (x.FromDate > lastMonth && x.FromDate <= now) && (x.FromDate.Value.Hour < EH && x.FromDate.Value.Hour >= MH)).ToList();
}
else if (_reportDataEntryModels.SelectedShifts == "2")
{
result = _dbContext.OPS_ManageDowntime.Where(x => (x.FromDate > lastMonth && x.FromDate <= now) && (x.FromDate.Value.Hour >= EH && x.FromDate.Value.Hour < MH)).ToList();
}
else
{
result = _dbContext.OPS_ManageDowntime.Where(x => (x.FromDate > lastMonth && x.FromDate <= now)).ToList();
}
}
else if (!string.IsNullOrEmpty(_reportDataEntryModels.From) && !string.IsNullOrEmpty(_reportDataEntryModels.To))
{
if (_reportDataEntryModels.SelectedShifts == "1")
{
result = _dbContext.OPS_ManageDowntime.Where(x => ((x.FromDate.Value.Day >= startDate.Day && x.FromDate.Value.Month >= startDate.Month && x.FromDate.Value.Year >= startDate.Year) &&
(x.FromDate.Value.Hour < EH && x.FromDate.Value.Hour >= MH)) &&
((x.ToDate.Value.Day <= endDate.Day && x.ToDate.Value.Month <= endDate.Month && x.ToDate.Value.Year <= endDate.Year) &&
(x.ToDate.Value.Hour < EH && x.ToDate.Value.Hour >= MH))).ToList();
}
else if (_reportDataEntryModels.SelectedShifts == "2")
{
result = _dbContext.OPS_ManageDowntime.Where(x => ((x.FromDate.Value.Day >= startDate.Day && x.FromDate.Value.Month >= startDate.Month && x.FromDate.Value.Year >= startDate.Year) &&
(x.FromDate.Value.Hour >= EH && x.FromDate.Value.Hour < MH)) &&
((x.ToDate.Value.Day <= endDate.Day && x.ToDate.Value.Month <= endDate.Month && x.ToDate.Value.Year <= endDate.Year) &&
(x.ToDate.Value.Hour >= EH && x.ToDate.Value.Hour < MH))).ToList();
}
else
{
result = _dbContext.OPS_ManageDowntime.Where(x => (x.FromDate.Value.Day >= startDate.Day && x.FromDate.Value.Month >= startDate.Month && x.FromDate.Value.Year >= startDate.Year) &&
(x.ToDate.Value.Day <= endDate.Day && x.ToDate.Value.Month <= endDate.Month && x.ToDate.Value.Year <= endDate.Year)).ToList();
}
}
else
{
result = null;
}
return(result);
}
}
public List <OPS_DataEntry> GetDataEntryReport(ReportDataEntryModels _reportDataEntryModels)
{
DateTime now = DateTime.Now;
var lastHour = now.AddHours(-24);
var lastWeek = now.AddDays(-7);
var lastMonth = now.AddMonths(-1);
var startDate = !string.IsNullOrWhiteSpace(_reportDataEntryModels.From) ? Convert.ToDateTime(DateTime.ParseExact(_reportDataEntryModels.From, "dd/MM/yyyy", CultureInfo.InvariantCulture)
.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture)) : DateTime.Now;
//var startDate = Convert.ToDateTime(_reportDataEntryModels.From);
var endDate = !string.IsNullOrWhiteSpace(_reportDataEntryModels.To) ? Convert.ToDateTime(DateTime.ParseExact(_reportDataEntryModels.To, "dd/MM/yyyy", CultureInfo.InvariantCulture)
.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture)) : DateTime.Now;
//var endDate =Convert.ToDateTime(_reportDataEntryModels.To);
dynamic result;
using (opsdbEntities _dbContext = new opsdbEntities())
{
if (_reportDataEntryModels.SelectedShifts != "0")
{
int id = Convert.ToInt32(_reportDataEntryModels.SelectedShifts);
if (_reportDataEntryModels.SelectedShifts != "0" && _reportDataEntryModels.Hours == "on")
{
result = _dbContext.OPS_DataEntry.Where(x => (x.CreatedDate > lastHour && x.CreatedDate <= now) && x.ManageShifts_Id == id).ToList();
}
else if (_reportDataEntryModels.SelectedShifts != "0" && _reportDataEntryModels.Weeks == "on")
{
result = _dbContext.OPS_DataEntry.Where(x => (x.CreatedDate > lastWeek && x.CreatedDate <= now) && x.ManageShifts_Id == id).ToList();
}
else if (_reportDataEntryModels.SelectedShifts != "0" && _reportDataEntryModels.Monthly == "on")
{
result = _dbContext.OPS_DataEntry.Where(x => (x.CreatedDate > lastMonth && x.CreatedDate <= now) && x.ManageShifts_Id == id).ToList();
}
else if (_reportDataEntryModels.SelectedShifts != "0" && _reportDataEntryModels.From != null && _reportDataEntryModels.To != null)
{
result = _dbContext.OPS_DataEntry.Where(x => (x.CreatedDate.Value.Day >= startDate.Day && x.CreatedDate.Value.Month >= startDate.Month && x.CreatedDate.Value.Year >= startDate.Year) &&
(x.CreatedDate.Value.Day <= endDate.Day && x.CreatedDate.Value.Month <= endDate.Month && x.CreatedDate.Value.Year <= endDate.Year) && x.ManageShifts_Id == id).ToList();
// result = _dbContext.OPS_DataEntry.Where(x => (x.CreatedDate > startDate && x.CreatedDate <= endDate) && x.IsDeleted != true && x.ManageShifts_Id == id).ToList();
}
else
{
result = _dbContext.OPS_DataEntry.Where(x => x.ManageShifts_Id == id).ToList();
}
}
else if (_reportDataEntryModels.Hours == "on")
{
result = _dbContext.OPS_DataEntry.Where(x => (x.CreatedDate > lastHour && x.CreatedDate <= now)).ToList();
}
else if (_reportDataEntryModels.Weeks == "on")
{
result = _dbContext.OPS_DataEntry.Where(x => (x.CreatedDate > lastWeek && x.CreatedDate <= now)).ToList();
}
else if (_reportDataEntryModels.Monthly == "on")
{
result = _dbContext.OPS_DataEntry.Where(x => (x.CreatedDate > lastMonth && x.CreatedDate <= now)).ToList();
}
else if (_reportDataEntryModels.From != null && _reportDataEntryModels.To != null)
{
result = _dbContext.OPS_DataEntry.Where(x => (x.CreatedDate.Value.Day >= startDate.Day && x.CreatedDate.Value.Month >= startDate.Month && x.CreatedDate.Value.Year >= startDate.Year) &&
(x.CreatedDate.Value.Day <= endDate.Day && x.CreatedDate.Value.Month <= endDate.Month && x.CreatedDate.Value.Year <= endDate.Year)).ToList();
//result = _dbContext.OPS_DataEntry.Where(x => (x.CreatedDate >= startDate && x.CreatedDate <= endDate) && x.IsDeleted != true).ToList();
}
else
{
result = null;
}
return(result);
}
}
