public bool IsThisShiftMethodIsInActionOrEnded(int id)
{
bool status = true;
DataTable dataHolder = new DataTable();
string CorrectedDate = null;
tbldaytiming StartTime = db.tbldaytimings.Where(m => m.IsDeleted == 0).SingleOrDefault();
TimeSpan Start = StartTime.StartTime;
if (Start <= DateTime.Now.TimeOfDay)
{
CorrectedDate = DateTime.Now.ToString("yyyy-MM-dd");
}
else
{
CorrectedDate = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
}
MsqlConnection mc = new MsqlConnection();
mc.open();
String sql = "SELECT * FROM tblShiftPlanner WHERE (( StartDate <='" + CorrectedDate + "' AND EndDate >='" + CorrectedDate + "') OR ( EndDate <'" + CorrectedDate + "' )) AND ShiftMethodID = " + id + " ORDER BY ShiftPlannerID ASC";
MySqlDataAdapter da = new MySqlDataAdapter(sql, mc.msqlConnection);
da.Fill(dataHolder);
mc.close();
if (dataHolder.Rows.Count == 0)
{
status = false;
}
return(status);
}
bool DoesThisMachineBelongToThisFactor(string FactorName, int FactorID, int machineID)
{
bool tick = false;
String sql = null;
if (FactorName == "Plant")
{
sql = "SELECT * FROM tblmachinedetails WHERE MachineID = " + machineID + " AND PlantID = " + FactorID;
}
else if (FactorName == "Shop")
{
sql = "SELECT * FROM tblmachinedetails WHERE MachineID = " + machineID + " AND ShopID = " + FactorID;
}
else if (FactorName == "Cell")
{
sql = "SELECT * FROM tblmachinedetails WHERE MachineID = " + machineID + " AND CellID = " + FactorID;
}
else if (FactorName == "Machine")
{
sql = "SELECT * FROM tblmachinedetails WHERE MachineID = " + machineID + " AND MachineID = " + FactorID;
}
MsqlConnection mc = new MsqlConnection();
mc.open();
DataTable dataHolder = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, mc.msqlConnection);
da.Fill(dataHolder);
mc.close();
if (dataHolder.Rows.Count > 0)
{
tick = true;
}
return(tick);
}
public JsonResult FetchMenuStyles()
{
List <DropdownClass> dropdownList = new List <DropdownClass>();
DataTable dt = new DataTable();
try
{
using (MsqlConnection mc = new MsqlConnection())
{
using (SqlCommand cmd = new SqlCommand("GetMenuStyles", mc.msqlConnection))
{
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
mc.open();
cmd.CommandType = CommandType.StoredProcedure;
sda.Fill(dt);
mc.close();
}
}
}
}
catch (Exception ex) { }
for (int i = 0; i < dt.Rows.Count; i++)
{
DropdownClass dropdownClass = new DropdownClass();
dropdownClass.Value = Convert.ToInt32(dt.Rows[i][0]);
dropdownClass.Text = dt.Rows[i][1].ToString();
dropdownList.Add(dropdownClass);
}
return(Json(dropdownList, JsonRequestBehavior.AllowGet));
}
//not using
protected string GetShiftForMachine_Date(int machineId, DateTime datetimevariable)
{
string shift = null;
string datestring = datetimevariable.Date.ToString("yyyy-MM-dd");
string timestring = datetimevariable.Hour + " : " + datetimevariable.Minute + ":" + datetimevariable.Second;
//1. Get shiftMethod from shiftPlanner for that date & machine.
//2. Get Factor and Based on that find if any of them had a plan .
String sql = "SELECT * FROM tblshiftplanner WHERE StartDate <='" + datestring + "' AND EndTime >= '" + datestring + "' ";
MsqlConnection mc = new MsqlConnection();
mc.open();
DataTable dataHolder = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, mc.msqlConnection);
da.Fill(dataHolder);
mc.close();
string factor = null;
int factorID = 0;
for (int i = 0; i < dataHolder.Rows.Count; i++)
{
string ShiftMethodName = null;
int FactorID = 0, ShiftMethodsID = 0;
bool tick = false;
if (!DBNull.Value.Equals(dataHolder.Rows[i][4]))
{
ShiftMethodsID = Convert.ToInt32(dataHolder.Rows[i][3]);
FactorID = Convert.ToInt32(dataHolder.Rows[i][4]);
ShiftMethodName = "Plant";
tick = DoesThisMachineBelongToThisFactor(ShiftMethodName, FactorID, machineId);
}
else if (!DBNull.Value.Equals(dataHolder.Rows[i][5]))
{
ShiftMethodsID = Convert.ToInt32(dataHolder.Rows[i][3]);
FactorID = Convert.ToInt32(dataHolder.Rows[i][5]);
ShiftMethodName = "Shop";
tick = DoesThisMachineBelongToThisFactor(ShiftMethodName, FactorID, machineId);
}
else if (!DBNull.Value.Equals(dataHolder.Rows[i][6]))
{
ShiftMethodsID = Convert.ToInt32(dataHolder.Rows[i][3]);
FactorID = Convert.ToInt32(dataHolder.Rows[i][6]);
ShiftMethodName = "Cell";
tick = DoesThisMachineBelongToThisFactor(ShiftMethodName, FactorID, machineId);
}
else if (!DBNull.Value.Equals(dataHolder.Rows[i][7]))
{
ShiftMethodsID = Convert.ToInt32(dataHolder.Rows[i][3]);
FactorID = Convert.ToInt32(dataHolder.Rows[i][7]);
ShiftMethodName = "Machine";
tick = DoesThisMachineBelongToThisFactor(ShiftMethodName, FactorID, machineId);
}
}
return(shift);
}
public void CalculateUtilization(int PlantID, int ShopID, int CellID, int MachineID, DateTime FromDate, DateTime Enddate)
{
var getMachineList = Serverdb.tblmachinedetails.Where(m => m.IsDeleted == 0).ToList();
if (MachineID != 0)
{
getMachineList = Serverdb.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.MachineID == MachineID).ToList();
}
else if (CellID != 0)
{
getMachineList = Serverdb.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.CellID == CellID).ToList();
}
else if (ShopID != 0)
{
getMachineList = Serverdb.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.ShopID == ShopID).ToList();
}
int dateDifference = Enddate.Subtract(FromDate).Days;
MsqlConnection mc = new MsqlConnection();
for (int i = 0; i <= dateDifference; i++)
{
DateTime QueryDate = FromDate.AddDays(i);
foreach (var Machine in getMachineList)
{
var GetUtilList = Serverdb.tbl_UtilReport.Where(m => m.MachineID == Machine.MachineID && m.CorrectedDate == QueryDate.Date).ToList();
if (GetUtilList.Count == 0)
{
using (SqlCommand cmd = new SqlCommand("[dbo].SP_UtilData", mc.msqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Cdate", QueryDate.Date);
cmd.Parameters.AddWithValue("@MachineID", Machine.MachineID);
mc.open();
cmd.ExecuteNonQuery();
mc.close();
}
//Serverdb.Database.
//Serverdb.SP_UtilData(QueryDate.Date, Machine.MachineID);
}
}
}
}
public string GetCheckboxesForUser(int roleId)
{
List <UserMenus> menus = new List <UserMenus>();
string res = "";
DataTable dt = new DataTable();
try
{
using (MsqlConnection mc = new MsqlConnection())
{
using (SqlCommand cmd = new SqlCommand("GetCheckboxesForUser", mc.msqlConnection))
{
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
mc.open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@roleId", roleId);
sda.Fill(dt);
mc.close();
}
}
}
}
catch (Exception ex) { }
for (int i = 0; i < dt.Rows.Count; i++)
{
UserMenus menu = new UserMenus();
menu.Id = dt.Rows[i]["MenuName"].ToString();
menu.MenuId = Convert.ToInt32(dt.Rows[i]["MenuId"]);
menu.MenuName = dt.Rows[i]["MenuName"].ToString();
string status = Convert.ToString(dt.Rows[i]["MenuStatus"]);
menu.MenuStatus = status;
menus.Add(menu);
}
res = JsonConvert.SerializeObject(menus);
return(res);
}
public string GetDashboardMenus()
{
string res = "";
int roleId = Convert.ToInt32(Session["RoleID"]);
List <DashboardMenu> dashboardMenus = new List <DashboardMenu>();
DataTable dt = new DataTable();
try
{
using (MsqlConnection mc = new MsqlConnection())
{
using (SqlCommand cmd = new SqlCommand("GetDashboardMenus", mc.msqlConnection))
{
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
mc.open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@roleId", roleId);
sda.Fill(dt);
mc.close();
}
}
}
}
catch (Exception ex) { }
for (int i = 0; i < dt.Rows.Count; i++)
{
DashboardMenu dashboardMenu = new DashboardMenu();
dashboardMenu.MenuName = dt.Rows[i]["MenuName"].ToString();
dashboardMenu.MenuURL = dt.Rows[i]["MenuURL"].ToString();
dashboardMenu.ImageURL = dt.Rows[i]["ImageUrl"].ToString();
dashboardMenu.ColourDiv = dt.Rows[i]["ColourDiv"].ToString();
dashboardMenu.Style = dt.Rows[i]["Style"].ToString();
dashboardMenus.Add(dashboardMenu);
}
res = JsonConvert.SerializeObject(dashboardMenus);
return(res);
}
public ActionResult Index(string PlantID = null, string CDate = null, string ShopID = null, string CellID = null, string WorkCenterID = null)
{
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
ViewBag.Logout = Session["Username"];
ViewBag.roleid = Session["RoleID"];
String Username = Session["Username"].ToString();
//1: get list of all 999 which have duration greater than 120 sec
//2: Construct new list in form of a class(UnIdentifiedLossCorrection) and send to view .
string CorrectedDate = DateTime.Now.ToString("yyyy-MM-dd");
if (DateTime.Now.Date.Hour < 6)
{
CorrectedDate = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
}
if (!string.IsNullOrEmpty(CDate))
{
CorrectedDate = CDate;
}
//var noCodeList = db.tbllossofentries.Where(m => m.MessageCodeID == 999 && m.CorrectedDate == CorrectedDate).OrderByDescending(m => m.CorrectedDate).ToList();
//if (!string.IsNullOrEmpty(WorkCenterID))
//{
// noCodeList = db.tbllossofentries.Where(m => m.MessageCodeID == 999 && m.MachineID == WorkCenterID && m.CorrectedDate == CorrectedDate).OrderByDescending(m => m.CorrectedDate).ToList();
//}
int plantId = 0, shopId = 0, cellId = 0, wcId = 0;
string Header = null, query = null;
if (string.IsNullOrEmpty(WorkCenterID))
{
if (string.IsNullOrEmpty(CellID))
{
if (string.IsNullOrEmpty(ShopID))
{
if (string.IsNullOrEmpty(PlantID))
{//donothing
}
else
{
plantId = Convert.ToInt32(PlantID);
string Macs = null;
var SubWCData = db.tblmachinedetails.Where(m => m.PlantID == plantId && m.IsDeleted == 0 && m.IsNormalWC == 0).ToList();
foreach (var subMacs in SubWCData)
{
if (Macs == null)
{
Macs = Convert.ToString(subMacs.MachineID);
}
else
{
Macs += "," + Convert.ToString(subMacs.MachineID);
}
}
query = @" SELECT LossID,MachineID,MessageCodeID,StartDateTime,EndDateTime FROM tbllossofentry Where MachineID IN ( " + Macs + ") and CorrectedDate = '" + CorrectedDate + "' and MessageCodeID = 999 order by MachineID";
}
}
else
{
shopId = Convert.ToInt32(ShopID);
string Macs = null;
var SubWCData = db.tblmachinedetails.Where(m => m.ShopID == shopId && m.IsDeleted == 0 && m.IsNormalWC == 0).ToList();
foreach (var subMacs in SubWCData)
{
if (Macs == null)
{
Macs = Convert.ToString(subMacs.MachineID);
}
else
{
Macs += "," + Convert.ToString(subMacs.MachineID);
}
}
query = @" SELECT LossID,MachineID,MessageCodeID,StartDateTime,EndDateTime FROM tbllossofentry Where MachineID IN ( " + Macs + ") and CorrectedDate = '" + CorrectedDate + "' and MessageCodeID = 999 order by MachineID";
}
}
else
{
cellId = Convert.ToInt32(CellID);
string Macs = null;
var SubWCData = db.tblmachinedetails.Where(m => m.CellID == cellId && m.IsDeleted == 0 && m.IsNormalWC == 0).ToList();
foreach (var subMacs in SubWCData)
{
if (Macs == null)
{
Macs = Convert.ToString(subMacs.MachineID);
}
else
{
Macs += "," + Convert.ToString(subMacs.MachineID);
}
}
query = @" SELECT LossID,MachineID,MessageCodeID,StartDateTime,EndDateTime FROM tbllossofentry Where MachineID IN ( " + Macs + ") and CorrectedDate = '" + CorrectedDate + "' and MessageCodeID = 999 order by MachineID";
}
}
else
{
wcId = Convert.ToInt32(WorkCenterID);
query = @" SELECT LossID,MachineID,MessageCodeID,StartDateTime,EndDateTime FROM tbllossofentry Where MachineID IN ( " + wcId + ") and CorrectedDate = '" + CorrectedDate + "' and MessageCodeID = 999 ";
}
DataTable LossCodesData = new DataTable();
if (!string.IsNullOrEmpty(PlantID))
{
using (MsqlConnection mcLossCodes = new MsqlConnection())
{
mcLossCodes.open();
SqlDataAdapter daLossCodesData = new SqlDataAdapter(query, mcLossCodes.msqlConnection);
daLossCodesData.Fill(LossCodesData);
mcLossCodes.close();
}
}
List <UnIdentifiedLossCorrection> UnIdentified = new List <UnIdentifiedLossCorrection>();
for (int i = 0; i < LossCodesData.Rows.Count; i++)
{
int macID = Convert.ToInt32(LossCodesData.Rows[i][1]);
int LossID = Convert.ToInt32(LossCodesData.Rows[i][0]);
int LossCodeID = Convert.ToInt32(LossCodesData.Rows[i][2]);
string MacName = Convert.ToString(db.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.MachineID == macID).Select(m => m.MachineInvNo).FirstOrDefault());
UnIdentified.Add(new UnIdentifiedLossCorrection {
LossID = LossID, MachineName = MacName, Level1 = LossCodeID, SDateTime = Convert.ToDateTime(LossCodesData.Rows[i][3]), EDateTime = Convert.ToDateTime(LossCodesData.Rows[i][4])
});
}
ViewData["PlantID"] = new SelectList(db.tblplants.Where(m => m.IsDeleted == 0), "PlantID", "PlantName", PlantID);
ViewData["ShopID"] = new SelectList(db.tblshops.Where(m => m.IsDeleted == 0 && m.PlantID == 1), "ShopID", "ShopName", ShopID);
ViewData["CellID"] = new SelectList(db.tblcells.Where(m => m.IsDeleted == 0 && m.PlantID == 999), "CellID", "CellName", CellID);
ViewData["WorkCenterID"] = new SelectList(db.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.PlantID == 999), "MachineID", "MachineInvNo", WorkCenterID);
ViewData["Level1"] = new SelectList(db.tbllossescodes.Where(m => m.IsDeleted == 0 && m.LossCodesLevel == 1 && m.MessageType != "Breakdown" && m.MessageType != "PM"), "LossCodeID", "LossCodeDesc", 999);
ViewData["Level2"] = new SelectList(db.tbllossescodes.Where(m => m.IsDeleted == 0 && m.LossCodesLevel == 2 && m.MessageType == "Nothing"), "LossCodeID", "LossCode"); //Just send an empty List
ViewData["Level3"] = new SelectList(db.tbllossescodes.Where(m => m.IsDeleted == 0 && m.LossCodesLevel == 3 && m.MessageType == "Nothing"), "LossCodeID", "LossCode"); //Just send an empty List
return(View(UnIdentified.ToList()));
}
public ActionResult Index(IList <UnIdentifiedLossCorrection> UnIdentifiedL)
{
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
string MacIDs = null;
int i = 0;
string CorrectedDate = null;
if (UnIdentifiedL != null)
{
foreach (var row in UnIdentifiedL)
{
int LossCodeID = 0, LossID = 0;
if (row.Level3 != 0)
{
LossCodeID = Convert.ToInt32(row.Level3);
LossID = Convert.ToInt32(row.LossID);
}
else if (row.Level2 != 0)
{
LossCodeID = Convert.ToInt32(row.Level2);
LossID = Convert.ToInt32(row.LossID);
}
else if (row.Level1 != 0 && row.Level1 != 999)
{
LossCodeID = Convert.ToInt32(row.Level1);
LossID = Convert.ToInt32(row.LossID);
}
var LossData = db.tbllossofentries.Find(LossID);
if (LossData != null)
{
//Get MacNumber if it doesn't exist in String(MacIDs)
string MacID = Convert.ToString(LossData.MachineID);
if (MacIDs == null)
{
MacIDs += MacID;
}
else
{
MacIDs += "," + MacID;
}
if (i == 0)
{
CorrectedDate = LossData.CorrectedDate;
i++;
}
LossData.MessageCodeID = LossCodeID;
string LossName = Convert.ToString(db.tbllossescodes.Where(m => m.LossCodeID == LossCodeID).Select(m => m.LossCode).FirstOrDefault());
LossData.MessageCode = LossName;
LossData.MessageDesc = LossName;
db.Entry(LossData).State = EntityState.Modified;
db.SaveChanges();
}
}
}
//Delete Data from tblworeport table and tbloeedashboardvariables
string StartDate = Convert.ToString(CorrectedDate);
MsqlConnection mcDeleteRows = new MsqlConnection();
try
{
try
{
DataTable LossCodesData = new DataTable();
string query = @"SELECT Distinct(HMIID) FROM i_facility_taml.dbo.tblworeport WHERE MachineID IN ( " + MacIDs + ") and CorrectedDate = '" + CorrectedDate + "';";
using (MsqlConnection mcLossCodes = new MsqlConnection())
{
mcLossCodes.open();
SqlDataAdapter daLossCodesData = new SqlDataAdapter(query, mcLossCodes.msqlConnection);
daLossCodesData.Fill(LossCodesData);
mcLossCodes.close();
}
mcDeleteRows.open();
SqlCommand cmdDeleteWOData = new SqlCommand("DELETE FROM i_facility_taml.dbo.tblworeport WHERE MachineID IN ( " + MacIDs + ") and CorrectedDate = '" + CorrectedDate + "';", mcDeleteRows.msqlConnection);
cmdDeleteWOData.ExecuteNonQuery();
for (int l = 0; l < LossCodesData.Rows.Count; l++)
{
SqlCommand cmdDeleteWOLosses = new SqlCommand("DELETE FROM i_facility_taml.dbo.tblwolossess WHERE HMIID = '" + LossCodesData.Rows[l][0] + "' ", mcDeleteRows.msqlConnection);
cmdDeleteWOLosses.ExecuteNonQuery();
}
}
catch (Exception e)
{
}
try
{
SqlCommand cmdDeleteOEEData = new SqlCommand("DELETE FROM i_facility_taml.dbo.tbloeedashboardvariables WHERE WCID IN ( " + MacIDs + ") and StartDate = '" + StartDate + "';", mcDeleteRows.msqlConnection);
cmdDeleteOEEData.ExecuteNonQuery();
}
catch (Exception e)
{
}
}
catch (Exception e)
{
}
finally
{
mcDeleteRows.close();
}
//Insert New Data
try
{
CalOEE_WODetails cwod = new CalOEE_WODetails();
cwod.CalculateOEEForYesterday(Convert.ToDateTime(CorrectedDate), Convert.ToDateTime(CorrectedDate));
cwod.CalWODataForYesterday(Convert.ToDateTime(CorrectedDate), Convert.ToDateTime(CorrectedDate));
}
catch (Exception e)
{
}
return(RedirectToAction("Index"));
}
public ActionResult Index()
{
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
Session["colordata"] = null;
ViewBag.Logout = Session["Username"];
ViewBag.roleid = Session["RoleID"];
//calculating Corrected Date
TimeSpan currentHourMint = new TimeSpan(05, 59, 59);
TimeSpan RealCurrntHour = System.DateTime.Now.TimeOfDay;
string CorrectedDate = DateTime.Now.Date.ToString("yyyy-MM-dd");
if (RealCurrntHour < currentHourMint)
{
CorrectedDate = DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd");
}
// getting all machine details and their count.
var macData = db.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.IsNormalWC == 0);
int mc = macData.Count();
ViewBag.macCount = mc;
int[] macid = new int[mc];
int macidlooper = 0;
foreach (var v in macData)
{
macid[macidlooper++] = v.MachineID;
}
Session["macid"] = macid;
ViewBag.macCount = mc;
int[,] maindata = new int[mc, 5];
//int[,] maindata = new int[mc, 6];
// write a raw query to get sum of powerOff, Operating, Idle, BreakDown, PlannedMaintenance.
using (MsqlConnection mc1 = new MsqlConnection())
{
mc1.open();
//SqlCommand cmd1 = new SqlCommand("SELECT MachineID,sum(MachineOffTime) as op,sum(OperatingTime)as o,sum(IdleTime) as it,sum(BreakdownTime)as bt FROM i_facility_tsal.dbo.tblmimics where CorrectedDate='" + CorrectedDate + "'and MachineID IN (select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
//SqlCommand cmd1 = " SELECT MachineID, sum(convert(int, MachineOffTime) )as op,sum(convert(int, OperatingTime)) as o,sum(convert(int, IdleTime)) as it,sum(convert(int, BreakdownTime)) as bt FROM i_facility_tsal.dbo.tblmimics where CorrectedDate = '2019-06-21'and MachineID IN(select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID",
//SqlCommand cmd1 = new SqlCommand("SELECT MachineID,sum(convert (int,MachineOffTime) )as op,sum(convert (int,OperatingTime))as o,sum(convert (int,IdleTime)) as it,sum(convert (int,BreakdownTime))as bt FROM i_facility_tsal.dbo.tblmimics where CorrectedDate='" + CorrectedDate + "' and MachineID IN (select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
SqlCommand cmd1 = new SqlCommand("SELECT MachineID, sum(convert(int, MachineOffTime) )as op,sum(convert(int, OperatingTime)) as o,sum(convert(int, IdleTime)) as it,sum(convert(int, BreakdownTime)) as bt FROM i_facility_tsal.dbo.tblmimics where CorrectedDate = '" + CorrectedDate + "' and MachineID IN(select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
SqlDataReader datareader = cmd1.ExecuteReader();
int maindatalooper1 = 0;
while (datareader.Read())
{
int maindatalooper2 = 0;
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(0);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(1);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(2);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(3);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(4);
maindatalooper1++;
}
mc1.close();
}
Session["colordata"] = maindata;
//var tblMainDT = db.tbllivedailyprodstatus.Include(t => t.tblmachinedetail).Where(m => m.CorrectedDate == CorrectedDate).OrderBy(m => m.StartTime);
//return View(tblMainDT.ToList());
//Get Modes for All Machines for Today
//List<tbllivemodedb> tblModeDT = db.tblmodes.Where(m => m.CorrectedDate == CorrectedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0).OrderBy(m => m.MachineID).ThenBy(m => m.StartTime).ToList();
List <tbllivemodedb> tblModeDT = db.tbllivemodedbs.Where(m => m.CorrectedDate == CorrectedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 1).OrderBy(m => m.MachineID).ThenBy(m => m.StartTime).ToList();
List <tbllivemodedb> tblModeDTCurr = db.tbllivemodedbs.Where(m => m.CorrectedDate == CorrectedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 0).OrderBy(m => m.MachineID).ThenByDescending(m => m.ModeID).ToList();
//Get Latest Mode for each machine and Update the DurationInSec Column
List <tbllivemodedb> CurrentModesOfAllMachines = (from row in tblModeDT
where row.IsCompleted == 0
select row).ToList();
int PrvMachineID = 0;
foreach (var row in tblModeDTCurr)
{
// DateTime startDateTime = Convert.ToDateTime( row.StartTime);
// int DurInSec = Convert.ToInt32( DateTime.Now.Subtract(startDateTime).TotalSeconds );
// //row.DurationInSec = Convert.ToInt32( DateTime.Now.Subtract(startDateTime).TotalSeconds );
// int ModeID = row.ModeID;
//foreach ( var tom in tblModeDT.Where(w => w.ModeID == ModeID)) {
// tom.DurationInSec = DurInSec;
// }
if (PrvMachineID != row.MachineID)
{
DateTime startDateTime = Convert.ToDateTime(row.StartTime);
int DurInSec = Convert.ToInt32(DateTime.Now.Subtract(startDateTime).TotalSeconds);
//row.DurationInSec = Convert.ToInt32( DateTime.Now.Subtract(startDateTime).TotalSeconds );
int ModeID = row.ModeID;
row.DurationInSec = DurInSec;
tblModeDT.Add(row);
//foreach (var tom in tblModeDT.Where(w => w.ModeID == ModeID))
//{
//}
PrvMachineID = row.MachineID;
}
}
List <DBMode> ShowMode = new List <DBMode>();
//Update DurationInSec to Minutes
foreach (var MainRow in tblModeDT.Where(m => m.DurationInSec > 0))
{
DBMode ShowModeItem = new DBMode();
ShowModeItem.ColorCode = MainRow.ColorCode;
ShowModeItem.CorrectedDate = MainRow.CorrectedDate;
ShowModeItem.DurationInSec = MainRow.DurationInSec / 60.00;
ShowModeItem.EndTime = MainRow.EndTime;
ShowModeItem.InsertedBy = MainRow.InsertedBy;
ShowModeItem.InsertedOn = MainRow.InsertedOn;
ShowModeItem.IsCompleted = MainRow.IsCompleted;
ShowModeItem.IsDeleted = MainRow.IsDeleted;
ShowModeItem.MachineID = MainRow.MachineID;
ShowModeItem.Mode = MainRow.Mode;
ShowModeItem.ModeID = MainRow.ModeID;
ShowModeItem.ModifiedBy = MainRow.ModifiedBy;
ShowModeItem.ModifiedOn = MainRow.ModifiedOn;
ShowModeItem.StartTime = MainRow.StartTime;
ShowModeItem.tblmachinedetail = MainRow.tblmachinedetail;
ShowMode.Add(ShowModeItem);
MainRow.DurationInSec = Convert.ToInt32(MainRow.DurationInSec / 60);
}
;
List <string> ShopNames = db.tbllivemodedbs.Where(m => m.CorrectedDate == CorrectedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0).Select(m => m.tblmachinedetail.ShopNo).Distinct().ToList();
ViewBag.DistinctShops = ShopNames;
//return View(tblModeDT);
return(View(ShowMode));
}
public ActionResult Index()
{
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
Session["CellId"] = 1;
Session["colordata"] = null;
ViewBag.Logout = Session["Username"];
ViewBag.roleid = Session["RoleID"];
//calculating Corrected Date
//TimeSpan currentHourMint = new TimeSpan(06, 59, 59);
TimeSpan currentHourMint = new TimeSpan(05, 59, 59);
TimeSpan RealCurrntHour = System.DateTime.Now.TimeOfDay;
string CorrectedDate = DateTime.Now.Date.ToString("yyyy-MM-dd");
DateTime correctedDate = DateTime.Now.Date;
string PrvCorrectedDate = correctedDate.AddDays(-1).Date.ToString("yyyy-MM-dd");
if (RealCurrntHour < currentHourMint)
{
CorrectedDate = DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd");
correctedDate = DateTime.Now.AddDays(-1).Date;
PrvCorrectedDate = correctedDate.AddDays(-1).Date.ToString("yyyy-MM-dd");
}
//correctedDate = Convert.ToDateTime("2019-05-21");
//CorrectedDate = "2019-05-21";
//PrvCorrectedDate = "2019-05-20";
// getting all machine details and their count.
var macData = db.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.IsNormalWC == 0);
int mc = macData.Count();
ViewBag.macCount = mc;
int[] macid = new int[mc];
int macidlooper = 0;
foreach (var v in macData)
{
macid[macidlooper++] = v.MachineID;
}
Session["macid"] = macid;
ViewBag.macCount = mc;
int[,] maindata = new int[mc, 16];
//int[,] maindata = new int[mc, 6];
// write a raw query to get sum of powerOff, Operating, Idle, BreakDown, PlannedMaintenance.
using (MsqlConnection mc1 = new MsqlConnection())
{
mc1.open();
SqlCommand cmd1 = new SqlCommand("SELECT MachineID,sum(OperatingTime)as ot,sum(Delay) as dl,sum(Alarm)as al,sum(MDI) as mdi,sum(JOG) as jog,sum(HND) as hnd,sum(EDIT) as edit,sum(RTN) as rtn,sum(ETC) as etc,sum(PowerOff) as po FROM " + databaseName + ".tblworkmodemimics where CorrectedDate='" + CorrectedDate + "'and MachineID IN (select distinct(MachineID) from " + databaseName + ".tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
SqlDataReader datareader = cmd1.ExecuteReader();
int maindatalooper1 = 0;
while (datareader.Read())
{
int maindatalooper2 = 0;
int OperatingTime = datareader.GetInt32(1);
int Delay = datareader.GetInt32(2);
int Alarm = datareader.GetInt32(3);
int MDI = datareader.GetInt32(4);
int JOG = datareader.GetInt32(5);
int HND = datareader.GetInt32(6);
int EDIT = datareader.GetInt32(7);
int RTN = datareader.GetInt32(8);
int ETC = datareader.GetInt32(9);
int PowerOff = datareader.GetInt32(10);
int TotalTime = OperatingTime + Delay + Alarm + MDI + JOG + HND + EDIT + RTN + ETC + PowerOff;
if (TotalTime == 0)
{
TotalTime = 1;
}
int UtilPer = Convert.ToInt32(Convert.ToDouble(Convert.ToDouble(Convert.ToDouble(OperatingTime) / Convert.ToDouble(TotalTime)) * 100));
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(0);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(1);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(2);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(3);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(4);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(5);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(6);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(7);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(8);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(9);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(10);
maindata[maindatalooper1, maindatalooper2++] = UtilPer;
maindata[maindatalooper1, maindatalooper2++] = TotalTime;
maindatalooper1++;
}
datareader.Close();
SqlCommand Prvcmd1 = new SqlCommand("SELECT MachineID,sum(OperatingTime)as ot,sum(Delay) as dl,sum(Alarm)as al,sum(MDI) as mdi,sum(JOG) as jog,sum(HND) as hnd,sum(EDIT) as edit,sum(RTN) as rtn,sum(ETC) as etc,sum(PowerOff) as po FROM " + databaseName + ".tblworkmodemimics where CorrectedDate='" + PrvCorrectedDate + "'and MachineID IN (select distinct(MachineID) from " + databaseName + ".tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
SqlDataReader Prvdatareader = Prvcmd1.ExecuteReader();
int Prvmaindatalooper1 = 0;
while (Prvdatareader.Read())
{
int OperatingTime = Prvdatareader.GetInt32(1);
int Delay = Prvdatareader.GetInt32(2);
int Alarm = Prvdatareader.GetInt32(3);
int MDI = Prvdatareader.GetInt32(4);
int JOG = Prvdatareader.GetInt32(5);
int HND = Prvdatareader.GetInt32(6);
int EDIT = Prvdatareader.GetInt32(7);
int RTN = Prvdatareader.GetInt32(8);
int ETC = Prvdatareader.GetInt32(9);
int PowerOff = Prvdatareader.GetInt32(10);
int TotalTime = OperatingTime + Delay + Alarm + MDI + JOG + HND + EDIT + RTN + ETC + PowerOff;
if (TotalTime == 0)
{
TotalTime = 1;
}
int UtilPer = Convert.ToInt32(Convert.ToDouble(Convert.ToDouble(Convert.ToDouble(OperatingTime) / Convert.ToDouble(TotalTime)) * 100));
maindata[Prvmaindatalooper1, 13] = UtilPer;
maindata[Prvmaindatalooper1, 14] = OperatingTime;
maindata[Prvmaindatalooper1, 15] = TotalTime;
Prvmaindatalooper1++;
}
Prvdatareader.Close();
mc1.close();
}
Session["colordata"] = maindata;
//Get Modes for All Machines for Today
List <tbllivemode> tblModeDT = db.tbllivemodes.Where(m => m.CorrectedDate == correctedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 1 && m.IsDeleted == 0).OrderBy(m => m.MachineID).ThenBy(m => m.StartTime).ToList();
List <tbllivemode> tblModeDTCurr = db.tbllivemodes.Where(m => m.CorrectedDate == correctedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 0 && m.IsDeleted == 0).OrderBy(m => m.MachineID).ThenByDescending(m => m.ModeID).ToList();
//Get Latest Mode for each machine and Update the DurationInSec Column
List <tbllivemode> CurrentModesOfAllMachines = (from row in tblModeDT
where row.IsCompleted == 0
orderby row.ModeID descending
select row).ToList().OrderByDescending(m => m.ModeID).ToList();
int PrvMachineID = 0;
foreach (var row in tblModeDTCurr)
{
if (PrvMachineID != row.MachineID)
{
DateTime startDateTime = Convert.ToDateTime(row.StartTime);
int DurInSec = Convert.ToInt32(DateTime.Now.Subtract(startDateTime).TotalSeconds);
//row.DurationInSec = Convert.ToInt32( DateTime.Now.Subtract(startDateTime).TotalSeconds );
int ModeID = row.ModeID;
row.DurationInSec = DurInSec;
tblModeDT.Add(row);
//foreach (var tom in tblModeDT.Where(w => w.ModeID == ModeID))
//{
//}
PrvMachineID = row.MachineID;
}
if (row.ModeType == "SETUP")
{
DateTime StartTime = Convert.ToDateTime(row.StartTime);
DateTime EndTime = DateTime.Now;
try
{
EndTime = Convert.ToDateTime(row.LossCodeEnteredTime);
}
catch { }
int DurInSec = Convert.ToInt32(EndTime.Subtract(StartTime).TotalSeconds);
int ModeID = row.ModeID;
row.DurationInSec = DurInSec;
tblModeDT.Add(row);
}
}
//Update DurationInSec to Minutes
foreach (var MainRow in tblModeDT.Where(m => m.DurationInSec > 0))
{
int GetDur = (int)MainRow.DurationInSec / 60;
if (MainRow.ModeType == "SETUP")
{
GetDur = (int)Convert.ToDateTime(MainRow.LossCodeEnteredTime).Subtract(Convert.ToDateTime(MainRow.StartTime)).TotalSeconds / 60;
}
if (GetDur < 1)
{
// if the duration is sec is greatethan the 50 then consider as 1 minutes
int LessThan1min = (int)MainRow.DurationInSec;
if (LessThan1min > 50)
{
GetDur = 1;
}
else
{
GetDur = 0;
}
}
MainRow.DurationInSec = GetDur;
}
;
List <string> ShopNames = db.tbllivemodes.Where(m => m.CorrectedDate == correctedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0).Select(m => m.tblmachinedetail.tblcell.CelldisplayName).Distinct().ToList();
ViewBag.DistinctShops = ShopNames;
return(View(tblModeDT.OrderBy(m => m.MachineID).ThenBy(m => m.StartTime).ToList()));
}
public ActionResult ImportMachineDetails(HttpPostedFileBase file)
{
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
////start logging
//String Username = Session["Username"].ToString();
//int UserID = Convert.ToInt32(Session["UserId"]);
//string CompleteModificationdetail = "Import PriorityAlarm";
//Action = "ImportPriorityAlarm";
//ActiveLogStorage Obj = new ActiveLogStorage();
//Obj.SaveActiveLog(Action, Controller, Username, UserID, CompleteModificationdetail);
////End
//Deleting Excel file
#region
string fileLocation1 = Server.MapPath("~/Content/");
DirectoryInfo di = new DirectoryInfo(fileLocation1);
FileInfo[] files = di.GetFiles("*.xlsx").Where(p => p.Extension == ".xlsx").ToArray();
foreach (FileInfo file1 in files)
{
try
{
file1.Attributes = FileAttributes.Normal;
System.IO.File.Delete(file1.FullName);
}
catch { }
}
#endregion
DataSet ds = new DataSet();
if (Request.Files["file"].ContentLength > 0)
{
string fileExtension = System.IO.Path.GetExtension(Request.Files["file"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["file"].SaveAs(fileLocation);
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//connection String for xls file format.
if (fileExtension == ".xls")
{
//excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
//excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=Excel 8.0;HDR=YES;";
//excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
//"Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"" ;
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
}
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create Connection to Excel work book and add oledb namespace
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return(null);
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
excelConnection1.Close();
excelConnection.Close();
}
if (fileExtension.ToString().ToLower().Equals(".xml"))
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["FileUpload"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["FileUpload"].SaveAs(fileLocation);
XmlTextReader xmlreader = new XmlTextReader(fileLocation);
// DataSet ds = new DataSet();
ds.ReadXml(xmlreader);
xmlreader.Close();
}
string Errors = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string a = ds.Tables[0].Rows[i][0].ToString();
MsqlConnection mc1 = new MsqlConnection();
mc1.open();
string dat = DateTime.Now.ToString();
dat = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
SqlCommand cmd2 = null;
try
{
//cmd2 = new SqlCommand("INSERT INTO tblmachinedetails(InsertedBy,InsertedOn," +
// "IsDeleted, MachineInvNo, IPAddress, ControllerType,MachineModel,MachineMake,ModelType" +
//",MachineDispName,IsParameters,ShopNo,IsPCB) VALUES" +
// "(" + Convert.ToInt32(Session["UserId"]) + ",'" + dat + "',0,'" + ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() + "','" +
// "" + ds.Tables[0].Rows[i][2].ToString() + "','" + ds.Tables[0].Rows[i][3].ToString() + "','" + ds.Tables[0].Rows[i][4].ToString() + "','" +
//"" + ds.Tables[0].Rows[i][5].ToString() + "','" + ds.Tables[0].Rows[i][6].ToString() + "','" + ds.Tables[0].Rows[i][7].ToString() + "'," +
//"" + Convert.ToInt32(ds.Tables[0].Rows[i][8]) + ",'" + ds.Tables[0].Rows[i][9].ToString() + "')", mc1.msqlConnection);
cmd2 = new SqlCommand("INSERT INTO tblmachinedetails (InsertedBy,InsertedOn,IsDeleted,MachineType, MachineInvNo, IPAddress, ControllerType,MachineModel,MachineMake,ModelType,MachineDispName,IsParameters,ShopNo,IsPCB) VALUES( '" + Convert.ToInt32(Session["UserId"]) + "','" + dat + "'," + 0 + "," + 2 + ",'" + ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() + "','" + ds.Tables[0].Rows[i][2].ToString() + "','" + ds.Tables[0].Rows[i][3].ToString() + "','" + ds.Tables[0].Rows[i][4].ToString() + "','" + ds.Tables[0].Rows[i][5].ToString() + "','" + ds.Tables[0].Rows[i][6].ToString() + "','" + ds.Tables[0].Rows[i][7].ToString() + "','" + ds.Tables[0].Rows[i][8].ToString() + "','" + ds.Tables[0].Rows[i][9].ToString() + "')", mc1.msqlConnection);
cmd2.ExecuteNonQuery();
}
catch (Exception e)
{
Errors = e.ToString();
}
mc1.close();
}
Session["MachErrDetails"] = Errors;
}
return(RedirectToAction("Index"));
}
public string GetShiftForMachine(int machineID, DateTime datetimevariable)
{
string shift = null;
string datestring = Convert.ToDateTime(datetimevariable).ToString("yyyy-MM-dd");
string timestring = Convert.ToDateTime(datetimevariable).Hour + " : " + Convert.ToDateTime(datetimevariable).Minute + ":" + Convert.ToDateTime(datetimevariable).Second;
//shift for today or any other day.(Even for future if plan is set).
if (datestring == DateTime.Now.Date.ToString("yyyy-MM-dd"))
{
string datetimevariablestring = Convert.ToDateTime(datetimevariable).ToString("yyyy-MM-dd HH:mm:ss");
String sql = "SELECT * FROM tblshiftdetails_machinewise WHERE MachineID = " + machineID + " AND StartTime <='" + datetimevariablestring + "' AND EndTime >= '" + datetimevariablestring + "' ORDER BY StartTime ASC";
MsqlConnection mc = new MsqlConnection();
mc.open();
DataTable dataHolder = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, mc.msqlConnection);
da.Fill(dataHolder);
mc.close();
if (dataHolder.Rows.Count != 0)
{
shift = dataHolder.Rows[0][2].ToString();
}
}
else
{
//1. Get shiftMethod from shiftPlanner for that date & machine.
//2. Get Factor and Based on that find if any of them had a plan .
String sql = "SELECT * FROM tblshiftplanner WHERE StartDate <='" + datestring + "' AND EndTime >= '" + datestring + "' ";
MsqlConnection mc = new MsqlConnection();
mc.open();
DataTable dataHolder = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, mc.msqlConnection);
da.Fill(dataHolder);
mc.close();
int ShiftMethodsID = 0;
for (int i = 0; i < dataHolder.Rows.Count; i++)
{
string FactorName = null;
int FactorID = 0;
bool tick = false;
if (!DBNull.Value.Equals(dataHolder.Rows[i][4]))
{
ShiftMethodsID = Convert.ToInt32(dataHolder.Rows[i][3]);
FactorID = Convert.ToInt32(dataHolder.Rows[i][4]);
FactorName = "Plant";
tick = DoesThisMachineBelongToThisFactor(FactorName, FactorID, machineID);
}
else if (!DBNull.Value.Equals(dataHolder.Rows[i][5]))
{
ShiftMethodsID = Convert.ToInt32(dataHolder.Rows[i][3]);
FactorID = Convert.ToInt32(dataHolder.Rows[i][5]);
FactorName = "Shop";
tick = DoesThisMachineBelongToThisFactor(FactorName, FactorID, machineID);
}
else if (!DBNull.Value.Equals(dataHolder.Rows[i][6]))
{
ShiftMethodsID = Convert.ToInt32(dataHolder.Rows[i][3]);
FactorID = Convert.ToInt32(dataHolder.Rows[i][6]);
FactorName = "Cell";
tick = DoesThisMachineBelongToThisFactor(FactorName, FactorID, machineID);
}
else if (!DBNull.Value.Equals(dataHolder.Rows[i][7]))
{
ShiftMethodsID = Convert.ToInt32(dataHolder.Rows[i][3]);
FactorID = Convert.ToInt32(dataHolder.Rows[i][7]);
FactorName = "Machine";
tick = DoesThisMachineBelongToThisFactor(FactorName, FactorID, machineID);
}
if (tick)
{
break;
}
}
//now get shift based on shiftmethod and time from tblshiftdetails
String sql1 = "SELECT * FROM tblshiftdetails WHERE ShiftMethodID = " + ShiftMethodsID + "AND ShiftStartTime <='" + timestring + "' AND ShiftEndTime >= '" + timestring + "' ORDER BY ShiftStartTime ASC";
MsqlConnection mc1 = new MsqlConnection();
mc1.open();
DataTable dataHolder1 = new DataTable();
MySqlDataAdapter da1 = new MySqlDataAdapter(sql1, mc1.msqlConnection);
da1.Fill(dataHolder1);
mc1.close();
if (dataHolder1.Rows.Count > 0)
{
shift = dataHolder1.Rows[0][1].ToString();
}
}
return(shift);
}
//public ActionResult Index(string CorrectedDate, string PrvCorrectedDate)
//{
// if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
// {
// return RedirectToAction("Login", "Login", null);
// }
// Session["colordata"] = null;
// ViewBag.Logout = Session["Username"];
// ViewBag.roleid = Session["RoleID"];
// ViewBag.StartDate = CorrectedDate;
// DateTime correcteDate = Convert.ToDateTime(CorrectedDate);
// calculating Corrected Date
// TimeSpan currentHourMint = new TimeSpan(05, 59, 59);
// TimeSpan RealCurrntHour = System.DateTime.Now.TimeOfDay;
// string CorrectedDate = DateTime.Now.Date.ToString("yyyy-MM-dd");
// if (RealCurrntHour < currentHourMint)
// {
// CorrectedDate = DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd");
// }
// getting all machine details and their count.
// var macData = Serverdb.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.IsNormalWC == 0);
// int mc = macData.Count();
// ViewBag.macCount = mc;
// int[] macid = new int[mc];
// int macidlooper = 0;
// foreach (var v in macData)
// {
// macid[macidlooper++] = v.MachineID;
// }
// Session["macid"] = macid;
// ViewBag.macCount = mc;
// int[,] maindata = new int[mc, 5];
// int[,] maindata = new int[mc, 6];
// write a raw query to get sum of powerOff, Operating, Idle, BreakDown, PlannedMaintenance.
// using (MsqlConnection mc1 = new MsqlConnection())
// {
// mc1.open();
// SqlCommand cmd1 = new SqlCommand("SELECT MachineID,sum(MachineOffTime) as op,sum(OperatingTime)as o,sum(IdleTime) as it,sum(BreakdownTime)as bt FROM i_facility_tsal.dbo.tblmimics where CorrectedDate='" + CorrectedDate + "'and MachineID IN (select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
// SqlCommand cmd1 = " SELECT MachineID, sum(convert(int, MachineOffTime) )as op,sum(convert(int, OperatingTime)) as o,sum(convert(int, IdleTime)) as it,sum(convert(int, BreakdownTime)) as bt FROM i_facility_tsal.dbo.tblmimics where CorrectedDate = '2019-06-21'and MachineID IN(select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID",
// SqlCommand cmd1 = new SqlCommand("SELECT MachineID,sum(convert (int,MachineOffTime) )as op,sum(convert (int,OperatingTime))as o,sum(convert (int,IdleTime)) as it,sum(convert (int,BreakdownTime))as bt FROM i_facility_tsal.dbo.tblmimics where CorrectedDate='" + CorrectedDate + "' and MachineID IN (select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
// SqlCommand cmd1 = new SqlCommand("SELECT MachineID, sum(convert(int, MachineOffTime) )as op,sum(convert(int, OperatingTime)) as o,sum(convert(int, IdleTime)) as it,sum(convert(int, BreakdownTime)) as bt FROM [i_facility_WeirMinerals].[dbo].tblmimics where CorrectedDate = '" + CorrectedDate + "' and MachineID IN(select distinct(MachineID) from [i_facility_WeirMinerals].[dbo].tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
// SqlDataReader datareader = cmd1.ExecuteReader();
// int maindatalooper1 = 0;
// while (datareader.Read())
// {
// int maindatalooper2 = 0;
// maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(0);
// maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(1);
// maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(2);
// maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(3);
// maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(4);
// maindatalooper1++;
// }
// mc1.close();
// }
// Session["colordata"] = maindata;
// var tblMainDT = Serverdb.tbllivedailyprodstatus.Include(t => t.tblmachinedetail).Where(m => m.CorrectedDate == CorrectedDate).OrderBy(m => m.StartTime);
// return View(tblMainDT.ToList());
// Get Modes for All Machines for Today
// List < tbllivemodedb > tblModeDT = Serverdb.tblmodes.Where(m => m.CorrectedDate == CorrectedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0).OrderBy(m => m.MachineID).ThenBy(m => m.StartTime).ToList();
// List < tblmode > tblModeDT = Serverdb.tblmodes.Where(m => m.CorrectedDate == correcteDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 1).OrderBy(m => m.MachineID).ThenBy(m => m.StartTime).ToList();
// List<tblmode> tblModeDTCurr = Serverdb.tblmodes.Where(m => m.CorrectedDate == correcteDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 0).OrderBy(m => m.MachineID).ThenByDescending(m => m.ModeID).ToList();
// Get Latest Mode for each machine and Update the DurationInSec Column
// List < tblmode > CurrentModesOfAllMachines = (from row in tblModeDT
// where row.IsCompleted == 0
// select row).ToList();
// int PrvMachineID = 0;
// foreach (var row in tblModeDTCurr)
// {
// DateTime startDateTime = Convert.ToDateTime(row.StartTime);
// int DurInSec = Convert.ToInt32(DateTime.Now.Subtract(startDateTime).TotalSeconds);
// //row.DurationInSec = Convert.ToInt32( DateTime.Now.Subtract(startDateTime).TotalSeconds );
// int ModeID = row.ModeID;
// foreach (var tom in tblModeDT.Where(w => w.ModeID == ModeID))
// {
// tom.DurationInSec = DurInSec;
// }
// if (PrvMachineID != row.MachineID)
// {
// DateTime startDateTime = Convert.ToDateTime(row.StartTime);
// int DurInSec = Convert.ToInt32(DateTime.Now.Subtract(startDateTime).TotalSeconds);
// row.DurationInSec = Convert.ToInt32(DateTime.Now.Subtract(startDateTime).TotalSeconds);
// int ModeID = row.ModeID;
// row.DurationInSec = DurInSec;
// tblModeDT.Add(row);
// foreach (var tom in tblModeDT.Where(w => w.ModeID == ModeID))
// {
// }
// PrvMachineID = row.MachineID;
// }
// }
// List<DBMode> ShowMode = new List<DBMode>();
// Update DurationInSec to Minutes
// foreach (var MainRow in tblModeDT.Where(m => m.DurationInSec > 0))
// {
// DBMode ShowModeItem = new DBMode();
// ShowModeItem.ColorCode = MainRow.ColorCode;
// ShowModeItem.CorrectedDate = Convert.ToString(MainRow.CorrectedDate);
// ShowModeItem.DurationInSec = MainRow.DurationInSec / 60.00;
// ShowModeItem.EndTime = MainRow.EndTime;
// ShowModeItem.InsertedBy = MainRow.InsertedBy;
// ShowModeItem.InsertedOn = MainRow.InsertedOn;
// ShowModeItem.IsCompleted = MainRow.IsCompleted;
// ShowModeItem.IsDeleted = MainRow.IsDeleted;
// ShowModeItem.MachineID = MainRow.MachineID;
// ShowModeItem.Mode = MainRow.MacMode;
// ShowModeItem.ModeID = MainRow.ModeID;
// ShowModeItem.ModifiedBy = MainRow.ModifiedBy;
// ShowModeItem.ModifiedOn = MainRow.ModifiedOn;
// ShowModeItem.StartTime = MainRow.StartTime;
// ShowModeItem.tblmachinedetail = MainRow.tblmachinedetail;
// ShowMode.Add(ShowModeItem);
// MainRow.DurationInSec = Convert.ToInt32(MainRow.DurationInSec / 60);
// };
// List<string> ShopNames = Serverdb.tblmodes.Where(m => m.CorrectedDate == correcteDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0).Select(m => m.tblmachinedetail.ShopNo).Distinct().ToList();
// ViewBag.DistinctShops = ShopNames;
// return View(tblModeDT);
// return View(ShowMode);
//}
public ActionResult Index(string CorrectedDate, string PrvCorrectedDate)
{
//if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
//{
// return RedirectToAction("Login", "Login", null);
//}
Session["CellId"] = 1;
Session["colordata"] = null;
ViewBag.Logout = Session["Username"];
ViewBag.roleid = Session["RoleID"];
DateTime correcteDate = Convert.ToDateTime(CorrectedDate);
// //calculating Corrected Date
// TimeSpan currentHourMint = new TimeSpan(05, 59, 59);
// TimeSpan RealCurrntHour = System.DateTime.Now.TimeOfDay;
// //string CorrectedDate = DateTime.Now.Date.ToString("yyyy-MM-dd");
// //if (RealCurrntHour < currentHourMint)
// //{
// // CorrectedDate = DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd");
// //}
//calculating Corrected Date
TimeSpan currentHourMint = new TimeSpan(05, 59, 59);
TimeSpan RealCurrntHour = System.DateTime.Now.TimeOfDay;
//string CorrectedDate = DateTime.Now.Date.ToString("yyyy-MM-dd");
//DateTime correctedDate = DateTime.Now.Date;
//string PrvCorrectedDate = correctedDate.AddDays(-1).Date.ToString("yyyy-MM-dd");
//if (RealCurrntHour < currentHourMint)
//{
// CorrectedDate = DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd");
// correctedDate = DateTime.Now.AddDays(-1).Date;
// PrvCorrectedDate = correctedDate.AddDays(-1).Date.ToString("yyyy-MM-dd");
//}
// getting all machine details and their count.
var macData = Serverdb.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.IsNormalWC == 0);
int mc = macData.Count();
ViewBag.macCount = mc;
//int[] macid = new int[mc];
//int macidlooper = 0;
//foreach (var v in macData)
//{
// macid[macidlooper++] = v.MachineID;
//}
//Session["macid"] = macid;
//ViewBag.macCount = mc;
//int[,] maindata = new int[mc, 10];
//int[,] maindata = new int[mc, 6];
// write a raw query to get sum of powerOff, Operating, Idle, BreakDown, PlannedMaintenance.
List <Models.MachineStatus> objmachstatUpdate = new List <Models.MachineStatus>();
using (MsqlConnection mc1 = new MsqlConnection())
{
mc1.open();
SqlCommand cmd1 = new SqlCommand("SELECT MachineID,sum(MachineOffTime) as op,sum(OperatingTime)as o,sum(IdleTime) as it,sum(BreakdownTime)as bt FROM " + databaseName + ".tblmimics where CorrectedDate='" + CorrectedDate + "'and MachineID IN (select distinct(MachineID) from " + databaseName + ".tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
SqlDataReader datareader = cmd1.ExecuteReader();
// int maindatalooper1 = 0;
List <Models.MachineStatus> objmachstat = new List <Models.MachineStatus>();
while (datareader.Read())
{
MachineStatus macstatus = new MachineStatus();
//int maindatalooper2 = 0;
int MachineOffTime = datareader.GetInt32(1);
int OpertTime = datareader.GetInt32(2);
int IdleTime = datareader.GetInt32(3);
int BDTime = datareader.GetInt32(4);
int TotalTime = MachineOffTime + OpertTime + IdleTime + BDTime;
if (TotalTime == 0)
{
TotalTime = 1;
}
int UtilPer = Convert.ToInt32(Convert.ToDouble(Convert.ToDouble(Convert.ToDouble(OpertTime) / Convert.ToDouble(TotalTime)) * 100));
macstatus.MachineID = datareader.GetInt32(0);
var cellname = Serverdb.tblmachinedetails.Where(m => m.MachineID == macstatus.MachineID).Select(m => m.tblcell.CelldisplayName).FirstOrDefault();
var machinedet = Serverdb.tblmachinedetails.Where(m => m.MachineID == macstatus.MachineID).FirstOrDefault();
macstatus.CellName = cellname;
macstatus.MachineOffTime = datareader.GetInt32(1);
macstatus.OperatingTime = datareader.GetInt32(2);
macstatus.IdleTime = datareader.GetInt32(3);
macstatus.BreakdownTime = datareader.GetInt32(4);;
macstatus.Utilization = UtilPer;
macstatus.TotalTime = TotalTime;
macstatus.machinedet = machinedet;
//maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(0);
//maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(1);
//maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(2);
//maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(3);
//maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(4);
//maindata[maindatalooper1, maindatalooper2++] = UtilPer;
//maindata[maindatalooper1, maindatalooper2++] = TotalTime;
//maindatalooper1++;
objmachstat.Add(macstatus);
}
datareader.Close();
SqlCommand Prvcmd1 = new SqlCommand("SELECT MachineID,sum(MachineOffTime) as op,sum(OperatingTime)as o,sum(IdleTime) as it,sum(BreakdownTime)as bt FROM " + databaseName + ".tblmimics where CorrectedDate='" + PrvCorrectedDate + "'and MachineID IN (select distinct(MachineID) from " + databaseName + ".tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
SqlDataReader Prvdatareader = Prvcmd1.ExecuteReader();
// int Prvmaindatalooper1 = 0;
while (Prvdatareader.Read())
{
int MachineOffTime = Prvdatareader.GetInt32(1);
int OpertTime = Prvdatareader.GetInt32(2);
int IdleTime = Prvdatareader.GetInt32(3);
int BDTime = Prvdatareader.GetInt32(4);
int TotalTime = MachineOffTime + OpertTime + IdleTime + BDTime;
if (TotalTime == 0)
{
TotalTime = 1;
}
int UtilPer = Convert.ToInt32(Convert.ToDouble(Convert.ToDouble(Convert.ToDouble(OpertTime) / Convert.ToDouble(TotalTime)) * 100));
foreach (var row in objmachstat)
{
if (row.MachineID == Prvdatareader.GetInt32(0))
{
row.PrevOperatingTime = OpertTime;
row.PrevTotalTime = TotalTime;
row.PrevUtilization = UtilPer;
objmachstatUpdate.Add(row);
}
}
//maindata[Prvmaindatalooper1, 7] = UtilPer;
//maindata[Prvmaindatalooper1, 8] = OpertTime;
//maindata[Prvmaindatalooper1, 9] = TotalTime;
//Prvmaindatalooper1++;
}
Prvdatareader.Close();
mc1.close();
}
// Session["colordata"] = maindata;
objmachstatUpdate = objmachstatUpdate.OrderBy(m => m.CellName).ToList();
Session["MachineStauts"] = objmachstatUpdate.OrderBy(m => m.CellName).ToList();
//Get Modes for All Machines for Today
List <tbllivemode> tbllivemodeDT = Serverdb.tbllivemodes.Where(m => m.CorrectedDate == correcteDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 1).OrderBy(m => m.MachineID).ThenBy(m => m.StartTime).ToList();
List <tbllivemode> tbllivemodeDTCurr = Serverdb.tbllivemodes.Where(m => m.CorrectedDate == correcteDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0 && m.IsCompleted == 0).OrderBy(m => m.MachineID).ThenByDescending(m => m.ModeID).ToList();
//Get Latest Mode for each machine and Update the DurationInSec Column
List <tbllivemode> CurrentModesOfAllMachines = (from row in tbllivemodeDT
where row.IsCompleted == 0
orderby row.ModeID descending
select row).ToList().OrderByDescending(m => m.ModeID).ToList();
int PrvMachineID = 0;
foreach (var row in tbllivemodeDTCurr)
{
if (PrvMachineID != row.MachineID)
{
DateTime startDateTime = Convert.ToDateTime(row.StartTime);
int DurInSec = Convert.ToInt32(DateTime.Now.Subtract(startDateTime).TotalSeconds);
//row.DurationInSec = Convert.ToInt32( DateTime.Now.Subtract(startDateTime).TotalSeconds );
int ModeID = row.ModeID;
row.DurationInSec = DurInSec;
tbllivemodeDT.Add(row);
//foreach (var tom in tbllivemodeDT.Where(w => w.ModeID == ModeID))
//{
//}
PrvMachineID = row.MachineID;
}
if (row.ModeType == "SETUP")
{
DateTime StartTime = Convert.ToDateTime(row.StartTime);
DateTime EndTime = DateTime.Now;
try
{
EndTime = Convert.ToDateTime(row.LossCodeEnteredTime);
}
catch { }
int DurInSec = Convert.ToInt32(EndTime.Subtract(StartTime).TotalSeconds);
int ModeID = row.ModeID;
row.DurationInSec = DurInSec;
tbllivemodeDT.Add(row);
}
}
//Update DurationInSec to Minutes
foreach (var MainRow in tbllivemodeDT.Where(m => m.DurationInSec > 0))
{
int GetDur = (int)MainRow.DurationInSec / 60;
if (MainRow.ModeType == "SETUP")
{
GetDur = (int)Convert.ToDateTime(MainRow.LossCodeEnteredTime).Subtract(Convert.ToDateTime(MainRow.StartTime)).TotalSeconds / 60;
}
if (GetDur < 1)
{
GetDur = 0;
}
MainRow.DurationInSec = GetDur;
}
;
List <string> ShopNames = Serverdb.tbllivemodes.Where(m => m.CorrectedDate == correcteDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0).OrderBy(m => m.tblmachinedetail.tblcell.CelldisplayName).Select(m => m.tblmachinedetail.tblcell.CelldisplayName).Distinct().ToList();
// List<int> ShopID = Serverdb.tbllivemodes.Where(m => m.CorrectedDate == correctedDate && m.tblmachinedetail.IsDeleted == 0 && m.tblmachinedetail.IsNormalWC == 0).Select(m => m.tblmachinedetail.tblcell.CellID).Distinct().ToList();
ViewBag.DistinctShops = ShopNames.OrderBy(m => m).ToList();
// ViewBag.DistinctShopID = ShopID;
//List<int> macCountshopwise = new List<int>();
//foreach(int shop in ShopID)
//{
// int MacCount = Serverdb.tblmachinedetails.Where(m => m.tblcell.CellID == shop && m.IsDeleted == 0 && m.IsNormalWC == 0).ToList().Count;
// macCountshopwise.Add(MacCount);
//}
// ViewBag.machinecountbyshop = macCountshopwise;
//ViewBag.machinecountbyshopCount = macCountshopwise.Count;
return(View(tbllivemodeDT.ToList()));
}
public string GetSideMenubar()
{
int roleId = Convert.ToInt32(Session["RoleID"]);
string res = "";
List <SideMenubar> sideMenubars = new List <SideMenubar>();
DataTable dt = new DataTable();
try
{
using (MsqlConnection mc = new MsqlConnection())
{
using (SqlCommand cmd = new SqlCommand("GetSideMenubar", mc.msqlConnection))
{
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
mc.open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@roleId", roleId);
sda.Fill(dt);
mc.close();
}
}
}
}
catch (Exception ex) { }
for (int i = 0; i < dt.Rows.Count; i++)
{
int menuId = Convert.ToInt32(dt.Rows[i]["MenuId"]);
SideMenubar sideMenubar = new SideMenubar();
sideMenubar.MenuName = dt.Rows[i]["MenuName"].ToString();
sideMenubar.RoleId = roleId;
var imageurl = db.sidebar_menus.Where(m => m.MenuName == sideMenubar.MenuName).Select(m => m.ImageURL).FirstOrDefault();
sideMenubar.ImageURL = imageurl;
var values = db.sidebar_menus.Where(m => m.SubMenuName != null && m.MenuId == menuId && m.IsDeleted == 0).OrderBy(m => m.id).Select(m => new { m.SubMenuName, m.SubMenuURL }).ToList();
if (values.Count == 0)
{
//sideMenubar.MenuURL = dt.Rows[i]["MenuURL"].ToString();
var menuurl = db.sidebar_menus.Where(m => m.MenuName == sideMenubar.MenuName).Select(m => m.MenuURL).FirstOrDefault();
sideMenubar.MenuURL = menuurl;
}
List <SubMenus> subMenusList = new List <SubMenus>();
foreach (var item in values)
{
SubMenus subMenus = new SubMenus();
subMenus.SubMenuName = item.SubMenuName;
subMenus.SubMenuURL = item.SubMenuURL;
subMenusList.Add(subMenus);
}
sideMenubar.SubMenus = subMenusList;
sideMenubars.Add(sideMenubar);
}
res = JsonConvert.SerializeObject(sideMenubars);
return(res);
}
public ActionResult ImportMailDetails(HttpPostedFileBase file)
{
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
ViewBag.Logout = Session["Username"];
ViewBag.roleid = Session["RoleID"];
String Username = Session["Username"].ToString();
////start logging
//String Username = Session["Username"].ToString();
//int UserID = Convert.ToInt32(Session["UserId"]);
//string CompleteModificationdetail = "Import PriorityAlarm";
//Action = "ImportPriorityAlarm";
//ActiveLogStorage Obj = new ActiveLogStorage();
//Obj.SaveActiveLog(Action, Controller, Username, UserID, CompleteModificationdetail);
////End
//Deleting Excel file
#region
string fileLocation1 = Server.MapPath("~/Content/");
DirectoryInfo di = new DirectoryInfo(fileLocation1);
FileInfo[] files = di.GetFiles("*.xlsx").Where(p => p.Extension == ".xlsx").ToArray();
foreach (FileInfo file1 in files)
{
try
{
file1.Attributes = FileAttributes.Normal;
System.IO.File.Delete(file1.FullName);
}
catch { }
}
#endregion
DataSet ds = new DataSet();
if (Request.Files["file"].ContentLength > 0)
{
string fileExtension = System.IO.Path.GetExtension(Request.Files["file"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["file"].SaveAs(fileLocation);
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//connection String for xls file format.
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create Connection to Excel work book and add oledb namespace
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return(null);
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
excelConnection1.Close();
excelConnection.Close();
}
if (fileExtension.ToString().ToLower().Equals(".xml"))
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["FileUpload"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["FileUpload"].SaveAs(fileLocation);
XmlTextReader xmlreader = new XmlTextReader(fileLocation);
// DataSet ds = new DataSet();
ds.ReadXml(xmlreader);
xmlreader.Close();
}
string Errors = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Errors = null;
string Name = ds.Tables[0].Rows[i][0].ToString();
string EmailID = ds.Tables[0].Rows[i][1].ToString();
string ContactNo = ds.Tables[0].Rows[i][2].ToString();
if ((!string.IsNullOrEmpty(Name.Trim())) && (!string.IsNullOrEmpty(EmailID.Trim())) && (!string.IsNullOrEmpty(ContactNo.Trim())))
{
var doesNameExists = db.tblmailids.Where(m => m.IsDeleted == 0 && m.Name == Name).ToList();
if (doesNameExists.Count > 0)
{
Errors += " Duplicate Name : " + Name + "\n";
}
else if (Name.Length > 30)
{
Errors += " Name : " + Name + "\n";
}
var doesMailIdExists = db.tblmailids.Where(m => m.EmailID == EmailID).ToList();
if (doesMailIdExists.Count > 0)
{
Errors += " Duplicate Email ID :" + EmailID + "\n";
}
else if ((!EmailID.EndsWith("@tasl.aero")) || EmailID.Length > 50)
{
Errors += " Invalid EmailID : " + EmailID + "\n";
}
var doesCnoExists = db.tblmailids.Where(m => m.IsDeleted == 0 && m.ContactNo == ContactNo).ToList();
if (doesCnoExists.Count > 0)
{
Errors += " Duplicate Contact Number " + ContactNo + "\n";
}
else if ((ContactNo.Contains("[a-zA-Z]+")) || ContactNo.Length > 10)
{
Errors += " No Alphabets are Allowed in ContactNo. : " + ContactNo + "\n";
}
if (Errors == null)
{
MsqlConnection mc1 = new MsqlConnection();
mc1.open();
string dat = DateTime.Now.ToString();
dat = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
SqlCommand cmd2 = null;
try
{
cmd2 = new SqlCommand("INSERT INTO `i_facility_taml.dbo`.`tblmailids`(`Name`,`EmailID`,`ContactNo`,`IsDeleted`,`CreatedOn`,`CreatedBy`) "
+ " VALUES('" + Name + "' , '" + EmailID + "' ,'" + ContactNo + "',0,'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' , '" + Session["UserId"] + "')", mc1.msqlConnection);
cmd2.ExecuteNonQuery();
}
catch (Exception e)
{
Errors = e.ToString();
}
finally
{
mc1.close();
}
}
else
{
Session["Error"] += Errors;
}
}
}
}
return(RedirectToAction("Index"));
}
public ActionResult Index()
{
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
Session["colordata"] = null;
ViewBag.Logout = Session["Username"];
ViewBag.roleid = Session["RoleID"];
//calculating Corrected Date
TimeSpan currentHourMint = new TimeSpan(05, 59, 59);
TimeSpan RealCurrntHour = System.DateTime.Now.TimeOfDay;
string CorrectedDate = DateTime.Now.Date.ToString("yyyy-MM-dd");
if (RealCurrntHour < currentHourMint)
{
CorrectedDate = DateTime.Now.AddDays(-1).Date.ToString("yyyy-MM-dd");
}
// getting all machine details and their count.
var macData = db.tblmachinedetails.Where(m => m.IsDeleted == 0 && m.IsNormalWC == 0);
int mc = macData.Count();
ViewBag.macCount = mc;
int[] macid = new int[mc];
int macidlooper = 0;
foreach (var v in macData)
{
macid[macidlooper++] = v.MachineID;
}
Session["macid"] = macid;
ViewBag.macCount = mc;
int[,] maindata = new int[mc, 5];
//int[,] maindata = new int[mc, 6];
// write a raw query to get sum of powerOff, Operating, Idle, BreakDown, PlannedMaintenance.
using (MsqlConnection mc1 = new MsqlConnection())
{
mc1.open();
SqlCommand cmd1 = new SqlCommand("SELECT MachineID,sum(MachineOffTime) as op,sum(OperatingTime)as o,sum(IdleTime) as it,sum(BreakdownTime)as bt FROM mazakdaq.tblmimics where CorrectedDate='" + CorrectedDate + "'and MachineID IN (select distinct(MachineID) from tblmachinedetails where IsDeleted = 0 and IsNormalWC = 0) group by MachineID", mc1.msqlConnection);
SqlDataReader datareader = cmd1.ExecuteReader();
int maindatalooper1 = 0;
while (datareader.Read())
{
int maindatalooper2 = 0;
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(0);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(1);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(2);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(3);
maindata[maindatalooper1, maindatalooper2++] = datareader.GetInt32(4);
maindatalooper1++;
}
mc1.close();
}
Session["colordata"] = maindata;
// var tblMainDT = db.tbllivedailyprodstatus.Include(t => t.tblmachinedetail).Where(m => m.CorrectedDate == CorrectedDate).OrderBy(m => m.StartTime);
var tblMainDT = db.tbllivedailyprodstatus.Where(m => m.CorrectedDate == CorrectedDate).OrderBy(m => m.StartTime);
return(View(tblMainDT.ToList()));
}
public int GetShiftDuration(int machineID, string shift = null)
{
int totalminutes = 0;
if (shift != null)
{
String sql = "SELECT * FROM tblshiftdetails_machinewise WHERE MachineID = " + machineID + " AND ShiftName = '" + shift + "'";
MsqlConnection mc = new MsqlConnection();
mc.open();
DataTable dataHolder = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, mc.msqlConnection);
da.Fill(dataHolder);
mc.close();
if (dataHolder.Rows.Count != 0)
{
for (int i = 0; i < dataHolder.Rows.Count; i++)
{
TimeSpan tss = (System.TimeSpan)dataHolder.Rows[i][3];
TimeSpan tse = (System.TimeSpan)dataHolder.Rows[i][4];
TimeSpan finalTS1 = new TimeSpan(0, 0, 0, 0);
int sHour = tss.Hours;
int eHour = tse.Hours;
if (eHour < sHour)
{
finalTS1 = tse.Subtract(tss);
}
else
{
finalTS1 = tss.Subtract(tse);
}
totalminutes += Convert.ToInt32(finalTS1.TotalMinutes);
}
}
}
else
{
String sql = "SELECT * FROM tblshiftdetails_machinewise WHERE MachineID = " + machineID;
MsqlConnection mc = new MsqlConnection();
mc.open();
DataTable dataHolder = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, mc.msqlConnection);
da.Fill(dataHolder);
mc.close();
if (dataHolder.Rows.Count != 0)
{
for (int i = 0; i < dataHolder.Rows.Count; i++)
{
TimeSpan tss = (System.TimeSpan)dataHolder.Rows[i][3];
TimeSpan tse = (System.TimeSpan)dataHolder.Rows[i][4];
TimeSpan finalTS1 = new TimeSpan(0, 0, 0, 0);
int sHour = tss.Hours;
int eHour = tse.Hours;
if (eHour < sHour)
{
finalTS1 = tse.Subtract(tss);
}
else
{
finalTS1 = tss.Subtract(tse);
}
totalminutes += Convert.ToInt32(finalTS1.TotalMinutes);
}
}
}
return(totalminutes);
}