public bool IsThisPlanInAction(int id)
{
bool status = false;
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");
}
SRKSDemo.MsqlConnection mc = new SRKSDemo.MsqlConnection();
mc.open();
String sql = "SELECT * FROM " + dbName + ".[tblshiftplanner] WHERE StartDate <='" + CorrectedDate + "' AND EndDate >='" + CorrectedDate + "'AND ShiftPlannerID = " + id + " ORDER BY ShiftPlannerID ASC";
SqlDataAdapter da = new SqlDataAdapter(sql, mc.msqlConnection);
da.Fill(dataHolder);
mc.close();
if (dataHolder.Rows.Count > 0)
{
status = true;
}
return(status);
}
public ActionResult Edit(tblmachineallocation tblmachine)
{
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
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");
}
tblmachine.CorrectedDate = CorrectedDate;
tblmachine.ModifiedBy = Convert.ToInt32(Session["UserId"]);
tblmachine.ModifiedOn = DateTime.Now;
tblmachine.IsDeleted = 0;
if (ModelState.IsValid)
{
db.Entry(tblmachine).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
ViewBag.UserID = new SelectList(db.tblusers.Where(m => m.IsDeleted == 0 && m.PrimaryRole == 3), "UserID", "DisplayName", tblmachine.UserID);
// ViewBag.ShiftID = new SelectList(db.tblshiftdetails_machinewise.Where(m => m.IsDeleted == 0).GroupBy(m => m.ShiftDetailsID), "ShiftDetailsID", "ShiftName");
ViewBag.MachineID = new SelectList(db.tblmachinedetails.Where(m => m.IsDeleted == 0), "MachineID", "MachineDispName", tblmachine.MachineID);
return(View(tblmachine));
}
public bool IsThisShiftMethodIsInActionOrEnded(int id)
{
bool status = true;
DataTable dataHolder = new DataTable();
string CorrectedDate = null;
tbldaytiming StartTime = db.tbldaytimings.Where(m => m.IsDeleted == 0).FirstOrDefault();
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";
SqlDataAdapter da = new SqlDataAdapter(sql, mc.msqlConnection);
da.Fill(dataHolder);
mc.close();
if (dataHolder.Rows.Count == 0)
{
status = false;
}
return(status);
}
public ActionResult DeleteConfirmed(int id)
{
tbldaytiming tbldaytiming = db.tbldaytimings.Find(id);
db.tbldaytimings.Remove(tbldaytiming);
db.SaveChanges();
return(RedirectToAction("Index"));
}
//
// GET: /DayStartAndEndTime/Details/5
public ActionResult Details(int id = 0)
{
tbldaytiming tbldaytiming = db.tbldaytimings.Find(id);
if (tbldaytiming == null)
{
return(HttpNotFound());
}
return(View(tbldaytiming));
}
public ActionResult Edit(tbldaytiming tbldaytiming)
{
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
tbldaytiming.ModifiedBy = 1;
tbldaytiming.ModifiedOn = DateTime.Now;
tbldaytiming.IsDeleted = 0;
if (ModelState.IsValid)
{
db.Entry(tbldaytiming).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(tbldaytiming));
}
public ActionResult Create(tblpriorityalarm tblpriorityalarm)
{
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();
string CreatedON = DateTime.Now.ToString("yyyyMMddHHmmss");
if (ModelState.IsValid)
{
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");
}
//ActiveLog Code
int UserID = Convert.ToInt32(Session["UserId"]);
string CompleteModificationdetail = "New Creation";
Action = "Create";
//ActiveLogStorage Obj = new ActiveLogStorage();
//Obj.SaveActiveLog(Action, Controller, Username, UserID, CompleteModificationdetail);
//End
tblpriorityalarm.CreatedBy = UserID;
tblpriorityalarm.CreatedOn = CreatedON;
tblpriorityalarm.CorrectedDate = CorrectedDate;
tblpriorityalarm.MachineID = 1;
db.tblpriorityalarms.Add(tblpriorityalarm);
db.SaveChanges();
//db.createAlarmsPrority(tblpriorityalarm.AlarmNumber, tblpriorityalarm.AlarmDesc, tblpriorityalarm.AxisNo, tblpriorityalarm.AlarmGroup, tblpriorityalarm.PriorityNumber, 0, CreatedON, 1);
return(RedirectToAction("Index"));
}
ViewBag.MachineID = new SelectList(db.tblmachinedetails.Where(m => m.IsDeleted == 0), "MachineID", "MachineDispName");
return(View(tblpriorityalarm));
}
public ActionResult Create(tbldaytiming tbldaytiming)
{
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
tbldaytiming.CreatedBy = 1;
tbldaytiming.CreatedOn = DateTime.Now;
tbldaytiming.IsDeleted = 0;
//if (ModelState.IsValid)
{
db.tbldaytimings.Add(tbldaytiming);
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(tbldaytiming));
}
//
// GET: /DayStartAndEndTime/Delete/5
public ActionResult Delete(int id = 0)
{
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
tbldaytiming tbldaytiming = db.tbldaytimings.Find(id);
if (tbldaytiming == null)
{
return(HttpNotFound());
}
tbldaytiming.IsDeleted = 1;
tbldaytiming.ModifiedBy = 1;
tbldaytiming.ModifiedOn = System.DateTime.Now;
db.Entry(tbldaytiming).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
//
// GET: /DayStartAndEndTime/Edit/5
public ActionResult Edit(int id = 0)
{
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
ViewBag.Logout = Session["Username"];
ViewBag.roleid = Session["RoleID"];
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
tbldaytiming tbldaytiming = db.tbldaytimings.Find(id);
if (tbldaytiming == null)
{
return(HttpNotFound());
}
return(View(tbldaytiming));
}
public ActionResult Login(tbluser userlogin)
{
ViewBag.Logout = Session["Username"];
if (userlogin.UserName != null && userlogin.Password != null)
{
var usercnt = db.tblusers.Where(m => m.UserName == userlogin.UserName && m.Password == userlogin.Password && m.IsDeleted == 0).Count();
if (usercnt == 0)
{
TempData["username"] = "******";
}
if (usercnt != 0)
{
var log = db.tblusers.Where(m => m.UserName == userlogin.UserName && m.Password == userlogin.Password && m.IsDeleted == 0).Select(m => new { m.UserID, m.PrimaryRole, m.DisplayName, m.MachineID }).Single();
Session["UserID"] = log.UserID;
Session["Username"] = log.DisplayName;
Session["RoleID"] = log.PrimaryRole;
Session["FullName"] = log.DisplayName;
Session["MachineID"] = log.MachineID;
int opid = Convert.ToInt32(Session["UserID"]);
//Getting Shift Value
DateTime Time = DateTime.Now;
//TimeSpan Tm = new TimeSpan(Time.Hour, Time.Minute, Time.Second);
//var ShiftDetails = db.tblshift_mstr.Where(m => m.StartTime <= Tm && m.EndTime >= Tm);
//string Shift = "C";
int ShiftID = 0;
//foreach (var a in ShiftDetails)
//{
// Shift = a.ShiftName;
//}
ViewBag.date = System.DateTime.Now;
//get shift new code only for Operator.
string Shift = null;
if (log.PrimaryRole == 3)
{
ViewBag.shift = "C";
Shift = "C";
}
if (Shift == "A")
{
ShiftID = 1;
}
else if (Shift == "B")
{
ShiftID = 2;
}
else
{
ShiftID = 3;
}
Session["shiftforpopup"] = Shift;
//Checking operator machine is allocated or not
int Machinid = Convert.ToInt32(Session["MachineID"]);
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");
}
//var machineallocation = db.tblmachineallocations.Where(m => m.IsDeleted == 0 && m.CorrectedDate == CorrectedDate && m.UserID == opid && m.ShiftID == ShiftID);
//if (machineallocation.Count() != 0)
//{
// foreach (var a in machineallocation)
// {
// Machinid = Convert.ToInt32(a.MachineID);
// Session["MachineID"] = Machinid;
// }
//}
ViewBag.roleid = log.PrimaryRole;
if (log.PrimaryRole == 1 || log.PrimaryRole == 2)
{
Response.Redirect("~/Dashboard/Index", false);
}
else if (log.PrimaryRole == 3)
{
int MacID = Convert.ToInt32(Session["MachineID"]);
var MacDetails = db.tblmachinedetails.Where(m => m.MachineID == MacID).SingleOrDefault();
//Response.Redirect("~/HMIScree/Index", false);
if (MacDetails.IsNormalWC == 0)
{
// Response.Redirect("~/HMIScree/Index", false);
Response.Redirect("~/HMIScree/Index", false);
}
else if (MacDetails.IsNormalWC == 1)
{
Response.Redirect("~/ManualHMIScreen/Index", false);
}
}
else if (log.PrimaryRole == 4)
{
Response.Redirect("~/MachineStatus/Index", false);
}
else if (log.PrimaryRole == 5)
{
Response.Redirect("~/Dashboard/Index", false);
}
}
}
return(View(userlogin));
}
public ActionResult ImportPriorityAlarm(HttpPostedFileBase file)
{
////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
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 { }
}
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 msg = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
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");
}
string dat = DateTime.Now.ToString("yyyyMMddHHmmss");
string a = ds.Tables[0].Rows[i][0].ToString();
if (string.IsNullOrEmpty(a) == false)
{
//Checking value in db
int AlarmNumber = 0;
try
{
AlarmNumber = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
}
catch
{
msg = msg + " AlarmNumber should be a Number.\n";
continue;
}
//
tblpriorityalarm tblprio = new tblpriorityalarm();
String Username = Session["Username"].ToString();
tblprio.CreatedBy = Convert.ToInt32(Session["UserId"]);
try
{
tblprio.AlarmDesc = ds.Tables[0].Rows[i][1].ToString();
}
catch
{
msg = msg + " Please enter AlarmDescription of AlarmNumber " + AlarmNumber + ".\n";
continue;
}
tblprio.CreatedOn = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
tblprio.IsDeleted = 0;
tblprio.AlarmNumber = AlarmNumber;
try
{
tblprio.AxisNo = Convert.ToInt32(ds.Tables[0].Rows[i][2]);
}
catch
{
msg = msg + " AxisNo of AlarmNumber " + AlarmNumber + " should be a Number.\n";
continue;
}
try
{
tblprio.AlarmGroup = ds.Tables[0].Rows[i][3].ToString();
}
catch
{
msg = msg + " Please enter AlarmGroup of AlarmNumber " + AlarmNumber + " .\n";
continue;
}
try
{
tblprio.PriorityNumber = Convert.ToInt32(ds.Tables[0].Rows[i][4]);
}
catch
{
msg = msg + " Priority Number of AlarmNumber" + AlarmNumber + " should be a Number.\n";
continue;
}
int macid = 0;
try
{
macid = Convert.ToInt32(ds.Tables[0].Rows[i][5]);
}
catch
{
msg = msg + " MachineID of AlarmNumber " + AlarmNumber + " should be a Number.\n";
continue;
}
var machid = db.tblmachinedetails.Where(m => m.MachineID == macid).SingleOrDefault();
if (machid == null)
{
msg = msg + " MachineID of AlarmNumber " + AlarmNumber + " doesnot match in Database.\n";
continue;
}
else
{
tblprio.MachineID = Convert.ToInt32(ds.Tables[0].Rows[i][5]);
}
//ActiveLog Code
int UserID = Convert.ToInt32(Session["UserId"]);
string CompleteModificationdetail = "New Creation";
Action = "Create";
var msgcode = db.tblpriorityalarms.Where(m => m.AlarmNumber == AlarmNumber && m.IsDeleted == 0).SingleOrDefault();
//
if (msgcode == null)
{
db.tblpriorityalarms.Add(tblprio);
db.SaveChanges();
}
else
{
msg = msg + "Alarm Number " + AlarmNumber + " exists in Database.\n";
continue;
}
//MsqlConnection mc1 = new MsqlConnection();
//mc1.open();
//SqlCommand cmd2 = new SqlCommand("INSERT INTO tblpriorityalarms(CreatedOn,CreatedBy," +
// "IsDeleted,isMailSent,CorrectedDate, AlarmNumber, AlarmDesc,AxisNo, AlarmGroup,PriorityNumber,MachineID) VALUES" +
// "('" + dat + "'," + Convert.ToInt32(Session["UserId"]) + ",0,0,'" + CorrectedDate + "'," + Convert.ToInt32(ds.Tables[0].Rows[i][0]) + ",'" +
// "" + 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() + "," + Convert.ToInt32(ds.Tables[0].Rows[i][5]) + ")", mc1.msqlConnection);
//cmd2.ExecuteNonQuery();
//mc1.close();
}
}
Session["AlarmNumber"] = msg;
}
return(RedirectToAction("Index"));
}
