public JsonResult Operatordata(int Id)
{
ViewBag.id = Id;
i_facility_tamlEntities db = new i_facility_tamlEntities();
var Data = db.tbloperatordetails.Where(m => m.isDeleted == 0 && m.OPID == Id).Select(m => new { OPID = m.OPID, Dept = m.Dept, OperatorName = m.OperatorName, OperatorID = m.OperatorID });
return(Json(Data, JsonRequestBehavior.AllowGet));
}
public ActionResult Create(IEnumerable <tblshiftdetail> tblp, int ShiftMethod = 0)
{
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();
#region//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
#endregion
//shop name validation
//string shopname = tblp.ShopName.ToString();
//var doesThisShiftDetailsExists = db.tblshiftdetails.Where(m => m.IsDeleted == 0 && m.ShiftDetailsName == ).ToList();
//if (doesThisShopExists.Count == 0)
//{
//check if there's a entry of this shiftMethod in tblshiftdetails
var shiftmethodCheck = db.tblshiftdetails.Where(m => m.IsDeleted == 0 && m.ShiftMethodID == ShiftMethod).ToList();
if (shiftmethodCheck.Count > 0)
{
Session["Error"] = "ShiftDetails for this ShiftMethod Exists.";
ViewBag.ShiftMethod = new SelectList(db.tblshiftmethods.Where(m => m.IsDeleted == 0), "ShiftMethodID", "ShiftMethodName");
return(RedirectToAction("Index"));
}
var shiftmethodiddata = db.tblshiftmethods.Where(m => m.IsDeleted == 0 && m.ShiftMethodID == ShiftMethod).SingleOrDefault();
int noofshifts = shiftmethodiddata.NoOfShifts;
int rowscount = 0;
//to check if names are duplicate
List <string> shiftdetailsnames = new List <string>();
foreach (var shift in tblp)
{
if (shift.ShiftDetailsName != null)
{
shiftdetailsnames.Add(shift.ShiftDetailsName);
}
}
// for current shiftdetails.
if (shiftdetailsnames.Distinct().Count() != shiftdetailsnames.Count())
{
//Console.WriteLine("List contains duplicate values.");
TempData["Error"] = "Shift Names Cannot be Same.";
ViewBag.ShiftMethod = new SelectList(db.tblshiftmethods.Where(m => m.IsDeleted == 0), "ShiftMethodID", "ShiftMethodName");
return(RedirectToAction("Index"));
}
try
{
foreach (var shift in tblp)
{
if (rowscount < noofshifts)
{
// calculate duration
int duration = 0;
string starttimestring = "2016-06-02" + " " + shift.ShiftStartTime;
DateTime starttimedatetime = Convert.ToDateTime(starttimestring);
string endtimestring = null;
TimeSpan tsStart = (System.TimeSpan)shift.ShiftStartTime;
TimeSpan tsEnd = (System.TimeSpan)shift.ShiftEndTime;
int result = TimeSpan.Compare(tsStart, tsEnd);
if (result < 0)
{
endtimestring = "2016-06-02" + " " + shift.ShiftEndTime;
}
else if (result > 0)
{
endtimestring = "2016-06-03" + " " + shift.ShiftEndTime;
shift.NextDay = 1;
}
DateTime endtimedatetime = Convert.ToDateTime(endtimestring);
TimeSpan ts = endtimedatetime.Subtract(starttimedatetime);
duration = Convert.ToInt32(ts.TotalMinutes);
//create new object/row
tblshiftdetail tsd = new tblshiftdetail();
tsd.CreatedBy = UserID;
tsd.CreatedOn = DateTime.Now;
tsd.Duration = duration;
tsd.IsDeleted = 0;
tsd.NextDay = shift.NextDay;
tsd.ShiftMethodID = ShiftMethod;
tsd.ShiftDetailsDesc = shift.ShiftDetailsDesc;
tsd.ShiftDetailsName = shift.ShiftDetailsName;
tsd.ShiftEndTime = shift.ShiftEndTime;
tsd.ShiftStartTime = shift.ShiftStartTime;
db.tblshiftdetails.Add(tsd);
db.SaveChanges();
}
rowscount++;
}
}
catch (Exception e)
{
Session["Error"] = "Shift Name already exists for this ShiftMethod.";
using (i_facility_tamlEntities db1 = new i_facility_tamlEntities())
{
var todeletedata = db1.tblshiftdetails.Where(m => m.IsDeleted == 0 && m.ShiftMethodID == ShiftMethod).ToList();
foreach (var row in todeletedata)
{
row.IsDeleted = 1;
db.Entry(row).State = EntityState.Modified;
db.SaveChanges();
}
}
}
//ViewBag.ShiftMethod = new SelectList(db.tblshiftmethods.Where(m => m.IsDeleted == 0), "ShiftMethodID", "ShiftMethodName");
return(RedirectToAction("Index"));
}
public ActionResult Edit(IEnumerable <tblshiftdetail> tblp, int ShiftMethod = 0)
{
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();
int UserID = Convert.ToInt32(Session["UserID"]);
var shiftmethodiddata = db.tblshiftmethods.Where(m => m.IsDeleted == 0 && m.ShiftMethodID == ShiftMethod).SingleOrDefault();
int noofshifts = shiftmethodiddata.NoOfShifts;
int rowscount = 0;
//insert isedited and other details into old rows and insert the new rows.
var shiftDetailsData = db.tblshiftdetails.Where(m => m.IsDeleted == 0 && m.ShiftMethodID == ShiftMethod).ToList();
//check if shift method is in use or was used and now its being modified.
ShiftDetails sd = new ShiftDetails();
int shiftmethodid = Convert.ToInt32(ShiftMethod);
bool tick = sd.IsThisShiftMethodIsInActionOrEnded(shiftmethodid);
try
{
foreach (var shift in tblp)
{
if (rowscount < noofshifts)
{
using (i_facility_tamlEntities db3 = new i_facility_tamlEntities())
{
// calculate duration
int duration = 0;
string starttimestring = "2016-06-02" + " " + shift.ShiftStartTime;
DateTime starttimedatetime = Convert.ToDateTime(starttimestring);
string endtimestring = null;
TimeSpan tsStart = (System.TimeSpan)shift.ShiftStartTime;
TimeSpan tsEnd = (System.TimeSpan)shift.ShiftEndTime;
int result = TimeSpan.Compare(tsStart, tsEnd);
if (result < 0)
{
endtimestring = "2016-06-02" + " " + shift.ShiftEndTime;
}
else if (result > 0)
{
endtimestring = "2016-06-03" + " " + shift.ShiftEndTime;
shift.NextDay = 1;
}
DateTime endtimedatetime = Convert.ToDateTime(endtimestring);
TimeSpan ts = endtimedatetime.Subtract(starttimedatetime);
duration = Convert.ToInt32(ts.TotalMinutes);
if (tick)
{
//create new object/row
int shiftid = shift.ShiftDetailsID;
int oldcreatedby = 0;
DateTime oldcreatedon = DateTime.Now;
using (i_facility_tamlEntities db1 = new i_facility_tamlEntities())
{
var getShiftId = db1.tblshiftdetails.Where(m => m.IsDeleted == 0 && m.ShiftDetailsID == shiftid).SingleOrDefault();
getShiftId.IsShiftDetailsEdited = 1;
getShiftId.IsDeleted = 1;
getShiftId.ShiftMethodID = ShiftMethod;
getShiftId.ShiftDetailsEditedDate = DateTime.Now;
oldcreatedon = Convert.ToDateTime(getShiftId.CreatedOn);
oldcreatedby = Convert.ToInt32(getShiftId.CreatedBy);
ViewBag.ShiftMethod = new SelectList(db.tblshiftmethods.Where(m => m.IsDeleted == 0), "ShiftMethodID", "ShiftMethodName", shift.ShiftMethodID);
db1.Entry(getShiftId).State = EntityState.Modified;
db1.SaveChanges();
}
tblshiftdetail tsd = new tblshiftdetail();
tsd.Duration = duration;
tsd.IsDeleted = 0;
tsd.CreatedBy = oldcreatedby;
tsd.CreatedOn = oldcreatedon;
tsd.ModifiedBy = UserID;
tsd.ModifiedOn = DateTime.Now;
tsd.IsDeleted = 0;
tsd.NextDay = shift.NextDay;
tsd.ShiftMethodID = ShiftMethod;
tsd.ShiftDetailsDesc = shift.ShiftDetailsName;
tsd.ShiftDetailsName = shift.ShiftDetailsDesc;
tsd.ShiftEndTime = shift.ShiftEndTime;
tsd.ShiftStartTime = shift.ShiftStartTime;
db.tblshiftdetails.Add(tsd);
db.SaveChanges();
}
else
{
//create new object/row
shift.ModifiedBy = UserID;
shift.ModifiedOn = DateTime.Now;
shift.Duration = duration;
shift.IsDeleted = 0;
shift.ShiftMethodID = ShiftMethod;
db3.Entry(shift).State = EntityState.Modified;
db3.SaveChanges();
}
}
}
rowscount++;
}
}
catch (Exception e)
{
ViewBag.ShiftMethod = new SelectList(db.tblshiftmethods.Where(m => m.IsDeleted == 0), "ShiftMethodID", "ShiftMethodName", ShiftMethod);
return(View(tblp));
}
return(RedirectToAction("Index"));
}
//public ActionResult ImportMasterPartsstsw(HttpPostedFileBase file,string UploadType)
public ActionResult ImportMasterPartsstsw(HttpPostedFileBase file, string UploadType)
{
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
//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);
}
excelConnection.Close();
excelConnection1.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();
}
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
ViewBag.Logout = Session["Username"];
ViewBag.roleid = Session["RoleID"];
List <string> TimeUnits = new List <string>();
TimeUnits.Add("Min");
TimeUnits.Add("Hrs");
TimeUnits.Add("Sec");
List <string> WeightUnits = new List <string>();
WeightUnits.Add("Kg");
WeightUnits.Add("g");
string ErrorMsg = null;
if (UploadType == "OverWrite") // Accept only New Codes
{
#region
for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
{
tblmasterparts_st_sw tblmp = new tblmasterparts_st_sw();
String Username = Session["Username"].ToString();
tblmp.CreatedBy = Convert.ToInt32(Session["UserId"]);
tblmp.CreatedOn = DateTime.Now;
tblmp.IsDeleted = 0;
string PartName = null, OpNo = null;
PartName = Convert.ToString(ds.Tables[0].Rows[i][0]);
OpNo = Convert.ToString(ds.Tables[0].Rows[i][1]);
using (i_facility_tamlEntities db1 = new i_facility_tamlEntities())
{
var MasterStdPWTData = db1.tblmasterparts_st_sw.Where(m => m.PartNo == PartName && m.OpNo == OpNo).FirstOrDefault();
if (MasterStdPWTData != null)
{
ErrorMsg = ErrorMsg + PartName + " and " + OpNo + " has details in Database.\n";
continue;
}
else if (string.IsNullOrEmpty(PartName) || string.IsNullOrEmpty(OpNo))
{
ErrorMsg += " PartName or OpNo cannot be empty for " + PartName + " and " + OpNo + " .\n";
continue;
}
else
{
tblmp.PartNo = PartName;
tblmp.OpNo = OpNo;
}
}
try
{
tblmp.StdSetupTime = Convert.ToDecimal(ds.Tables[0].Rows[i][2]);
string setupUnit = Convert.ToString(ds.Tables[0].Rows[i][3]);
if (TimeUnits.Contains(setupUnit))
{
tblmp.StdSetupTimeUnit = setupUnit;
}
else //Default Unit
{
tblmp.StdSetupTimeUnit = "Min";
}
}
catch
{
ErrorMsg = ErrorMsg + "StdSetupTime of Part Number" + PartName + " should be Number.\n";
continue;
}
decimal stdCuttingTime = 0;
try
{
stdCuttingTime = Convert.ToDecimal(ds.Tables[0].Rows[i][4]);
}
catch
{
ErrorMsg = ErrorMsg + "StdCuttingTime of " + PartName + " should be Numbers.\n";
continue;
}
//if (stdCuttingTime == 0)
//{
// ErrorMsg = ErrorMsg + "StdCuttingTime of " + PartName + " cannot be Zero.\n";
// continue;
//}
//else
{
tblmp.StdCuttingTime = stdCuttingTime;
string CuttingTimeUnit = Convert.ToString(ds.Tables[0].Rows[i][5]);
if (TimeUnits.Contains(CuttingTimeUnit))
{
tblmp.StdCuttingTimeUnit = CuttingTimeUnit;
}
else //Default Unit
{
tblmp.StdCuttingTimeUnit = "Min";
}
}
try
{
tblmp.StdChangeoverTime = Convert.ToDecimal(ds.Tables[0].Rows[i][6]);
string ChangeoverUnit = Convert.ToString(ds.Tables[0].Rows[i][7]);
if (TimeUnits.Contains(ChangeoverUnit))
{
tblmp.StdChangeoverTimeUnit = ChangeoverUnit;
}
else //Default Unit
{
tblmp.StdChangeoverTimeUnit = "Min";
}
}
catch
{
ErrorMsg = ErrorMsg + "StdChangeoverTime of Part Number" + PartName + " should be Number.\n";
continue;
}
try
{
tblmp.InputWeight = Convert.ToDecimal(ds.Tables[0].Rows[i][8]);
string InputWeightUnit = Convert.ToString(ds.Tables[0].Rows[i][9]);
if (WeightUnits.Contains(InputWeightUnit))
{
tblmp.InputWeightUnit = InputWeightUnit;
}
else //Default Unit
{
tblmp.InputWeightUnit = "Kg";
}
tblmp.OutputWeight = Convert.ToDecimal(ds.Tables[0].Rows[i][10]);
string OutputWeightUnit = Convert.ToString(ds.Tables[0].Rows[i][11]);
if (WeightUnits.Contains(OutputWeightUnit))
{
tblmp.OutputWeightUnit = OutputWeightUnit;
}
else //Default Unit
{
tblmp.OutputWeightUnit = "Kg";
}
tblmp.MaterialRemovedQty = Convert.ToDecimal(ds.Tables[0].Rows[i][12]);
string MaterialRemovedQtyUnit = Convert.ToString(ds.Tables[0].Rows[i][13]);
if (WeightUnits.Contains(MaterialRemovedQtyUnit))
{
tblmp.MaterialRemovedQtyUnit = MaterialRemovedQtyUnit;
}
else //Default Unit
{
tblmp.MaterialRemovedQtyUnit = "Kg";
}
//Added by Ashok
string SAPCode = Convert.ToString(ds.Tables[0].Rows[i][14]);
if (string.IsNullOrEmpty(SAPCode))
{
ErrorMsg += "SAPCode cannot be null .\n";
continue;
}
else
{
tblmp.SAPCode = SAPCode;
}
}
catch
{
ErrorMsg = ErrorMsg + "I/O Weights & Materials Removed of " + PartName + " should be Numbers.\n";
continue;
}
db.tblmasterparts_st_sw.Add(tblmp);
db.SaveChanges();
}
#endregion
}
else if (UploadType == "New") // Delete Duplicate and Insert New. // if not Duplicate insert that
{
#region
for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
{
tblmasterparts_st_sw tblmp = new tblmasterparts_st_sw();
String Username = Session["Username"].ToString();
tblmp.CreatedBy = Convert.ToInt32(Session["UserId"]);
tblmp.CreatedOn = DateTime.Now;
tblmp.IsDeleted = 0;
string PartName = null, OpNo = null;
PartName = Convert.ToString(ds.Tables[0].Rows[i][0]);
OpNo = Convert.ToString(ds.Tables[0].Rows[i][1]);
using (i_facility_tamlEntities db1 = new i_facility_tamlEntities())
{
//var MasterStdPWTData = db1.tblmasterparts_st_sw.Where(m => m.PartNo == PartName && m.OpNo == OpNo).FirstOrDefault();
//if (MasterStdPWTData != null)
//{
// ErrorMsg += PartName + " and " + OpNo + " has details in Database.\n";
// continue;
//}
//else
if (string.IsNullOrEmpty(PartName) || string.IsNullOrEmpty(OpNo))
{
ErrorMsg += "PartNo and OperationNo cannot be null .\n";
continue;
}
else
{
tblmp.PartNo = PartName;
tblmp.OpNo = OpNo;
}
}
try
{
tblmp.StdSetupTime = Convert.ToDecimal(ds.Tables[0].Rows[i][2]);
string setupUnit = Convert.ToString(ds.Tables[0].Rows[i][3]);
if (TimeUnits.Contains(setupUnit))
{
tblmp.StdSetupTimeUnit = setupUnit;
}
else //Default Unit
{
tblmp.StdSetupTimeUnit = "Min";
}
}
catch
{
ErrorMsg = ErrorMsg + "StdSetupTime of Part Number" + PartName + " should be Number.\n";
continue;
}
decimal stdCuttingTime = 0;
try
{
stdCuttingTime = Convert.ToDecimal(ds.Tables[0].Rows[i][4]);
}
catch
{
ErrorMsg = ErrorMsg + "StdCuttingTime of " + PartName + " should be Numbers.\n";
continue;
}
//if (stdCuttingTime == 0)
//{
// ErrorMsg = ErrorMsg + "StdCuttingTime of " + PartName + " cannot be Zero.\n";
// continue;
//}
//else
{
tblmp.StdCuttingTime = stdCuttingTime;
string CuttingTimeUnit = Convert.ToString(ds.Tables[0].Rows[i][5]);
if (TimeUnits.Contains(CuttingTimeUnit))
{
tblmp.StdCuttingTimeUnit = CuttingTimeUnit;
}
else //Default Unit
{
tblmp.StdCuttingTimeUnit = "Min";
}
}
try
{
tblmp.StdChangeoverTime = Convert.ToDecimal(ds.Tables[0].Rows[i][6]);
string ChangeoverUnit = Convert.ToString(ds.Tables[0].Rows[i][7]);
if (TimeUnits.Contains(ChangeoverUnit))
{
tblmp.StdChangeoverTimeUnit = ChangeoverUnit;
}
else //Default Unit
{
tblmp.StdChangeoverTimeUnit = "Min";
}
}
catch
{
ErrorMsg = ErrorMsg + "StdChangeoverTime of Part Number" + PartName + " should be Number.\n";
continue;
}
try
{
tblmp.InputWeight = Convert.ToDecimal(ds.Tables[0].Rows[i][8]);
string InputWeightUnit = Convert.ToString(ds.Tables[0].Rows[i][9]);
if (WeightUnits.Contains(InputWeightUnit))
{
tblmp.InputWeightUnit = InputWeightUnit;
}
else //Default Unit
{
tblmp.InputWeightUnit = "Kg";
}
tblmp.OutputWeight = Convert.ToDecimal(ds.Tables[0].Rows[i][10]);
string OutputWeightUnit = Convert.ToString(ds.Tables[0].Rows[i][11]);
if (WeightUnits.Contains(OutputWeightUnit))
{
tblmp.OutputWeightUnit = OutputWeightUnit;
}
else //Default Unit
{
tblmp.OutputWeightUnit = "Kg";
}
tblmp.MaterialRemovedQty = Convert.ToDecimal(ds.Tables[0].Rows[i][12]);
string MaterialRemovedQtyUnit = Convert.ToString(ds.Tables[0].Rows[i][13]);
if (WeightUnits.Contains(MaterialRemovedQtyUnit))
{
tblmp.MaterialRemovedQtyUnit = MaterialRemovedQtyUnit;
}
else //Default Unit
{
tblmp.MaterialRemovedQtyUnit = "Kg";
}
//Added by Ashok
string SAPCode = Convert.ToString(ds.Tables[0].Rows[i][14]);
if (string.IsNullOrEmpty(SAPCode))
{
ErrorMsg += "SAPCode cannot be null .\n";
continue;
}
else
{
tblmp.SAPCode = SAPCode;
}
}
catch
{
ErrorMsg = ErrorMsg + "I/O Weights & Materials Removed of " + PartName + " should be Numbers.\n";
continue;
}
//check for dup and delete previous one.
var Dupdata = db.tblmasterparts_st_sw.Where(m => m.PartNo == PartName && m.OpNo == OpNo && m.IsDeleted == 0).FirstOrDefault();
if (Dupdata != null)
{
Dupdata.IsDeleted = 1;
Dupdata.DeletedDate = DateTime.Now;
db.Entry(Dupdata).State = EntityState.Modified;
db.SaveChanges();
}
db.tblmasterparts_st_sw.Add(tblmp);
db.SaveChanges();
}
#endregion
}
else if (UploadType == "Update") // OverWrite Existing Values
{
#region
for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
{
tblmasterparts_st_sw tblmp = new tblmasterparts_st_sw();
String Username = Session["Username"].ToString();
tblmp.CreatedBy = Convert.ToInt32(Session["UserId"]);
tblmp.CreatedOn = DateTime.Now;
tblmp.IsDeleted = 0;
string PartName = null, OpNo = null;
PartName = Convert.ToString(ds.Tables[0].Rows[i][0]);
OpNo = Convert.ToString(ds.Tables[0].Rows[i][1]);
DateTime createdOn = DateTime.Now;
//using (i_facility.Models.i_facility_tamlEntities db1 = new i_facility.Models.i_facility_tamlEntities())
//{
//var MasterStdPWTData = db1.tblmasterparts_st_sw.Where(m => m.PartNo == PartName && m.OpNo == OpNo).FirstOrDefault();
//if (MasterStdPWTData != null)
//{
// ErrorMsg = ErrorMsg + PartName + " and " + OpNo + " has details in Database.\n";
// continue;
//}
//else
if (string.IsNullOrEmpty(PartName) || string.IsNullOrEmpty(OpNo))
{
ErrorMsg = ErrorMsg + PartName + " and " + OpNo + " has details in Database.\n";
continue;
}
else
{
tblmp.PartNo = PartName;
tblmp.OpNo = OpNo;
}
//}
try
{
tblmp.StdSetupTime = Convert.ToDecimal(ds.Tables[0].Rows[i][2]);
string setupUnit = Convert.ToString(ds.Tables[0].Rows[i][3]);
if (TimeUnits.Contains(setupUnit))
{
tblmp.StdSetupTimeUnit = setupUnit;
}
else //Default Unit
{
tblmp.StdSetupTimeUnit = "Min";
}
}
catch
{
ErrorMsg = ErrorMsg + "StdSetupTime of Part Number" + PartName + " should be Number.\n";
continue;
}
decimal stdCuttingTime = 0;
try
{
stdCuttingTime = Convert.ToDecimal(ds.Tables[0].Rows[i][4]);
}
catch
{
ErrorMsg = ErrorMsg + "StdCuttingTime of " + PartName + " should be Numbers.\n";
continue;
}
//if (stdCuttingTime == 0)
//{
// ErrorMsg = ErrorMsg + "StdCuttingTime of " + PartName + " cannot be Zero.\n";
// continue;
//}
//else
{
tblmp.StdCuttingTime = stdCuttingTime;
string CuttingTimeUnit = Convert.ToString(ds.Tables[0].Rows[i][5]);
if (TimeUnits.Contains(CuttingTimeUnit))
{
tblmp.StdCuttingTimeUnit = CuttingTimeUnit;
}
else //Default Unit
{
tblmp.StdCuttingTimeUnit = "Min";
}
}
try
{
tblmp.StdChangeoverTime = Convert.ToDecimal(ds.Tables[0].Rows[i][6]);
string ChangeoverUnit = Convert.ToString(ds.Tables[0].Rows[i][7]);
if (TimeUnits.Contains(ChangeoverUnit))
{
tblmp.StdChangeoverTimeUnit = ChangeoverUnit;
}
else //Default Unit
{
tblmp.StdChangeoverTimeUnit = "Min";
}
}
catch
{
ErrorMsg = ErrorMsg + "StdChangeoverTime of Part Number" + PartName + " should be Number.\n";
continue;
}
try
{
tblmp.InputWeight = Convert.ToDecimal(ds.Tables[0].Rows[i][8]);
string InputWeightUnit = Convert.ToString(ds.Tables[0].Rows[i][9]);
if (WeightUnits.Contains(InputWeightUnit))
{
tblmp.InputWeightUnit = InputWeightUnit;
}
else //Default Unit
{
tblmp.InputWeightUnit = "Kg";
}
tblmp.OutputWeight = Convert.ToDecimal(ds.Tables[0].Rows[i][10]);
string OutputWeightUnit = Convert.ToString(ds.Tables[0].Rows[i][11]);
if (WeightUnits.Contains(OutputWeightUnit))
{
tblmp.OutputWeightUnit = OutputWeightUnit;
}
else //Default Unit
{
tblmp.OutputWeightUnit = "Kg";
}
tblmp.MaterialRemovedQty = Convert.ToDecimal(ds.Tables[0].Rows[i][12]);
string MaterialRemovedQtyUnit = Convert.ToString(ds.Tables[0].Rows[i][13]);
if (WeightUnits.Contains(MaterialRemovedQtyUnit))
{
tblmp.MaterialRemovedQtyUnit = MaterialRemovedQtyUnit;
}
else //Default Unit
{
tblmp.MaterialRemovedQtyUnit = "Kg";
}
//Added by Ashok
string SAPCode = Convert.ToString(ds.Tables[0].Rows[i][14]);
if (string.IsNullOrEmpty(SAPCode))
{
ErrorMsg += "SAPCode cannot be null .\n";
continue;
}
else
{
tblmp.SAPCode = SAPCode;
}
}
catch
{
ErrorMsg = ErrorMsg + "I/O Weights & Materials Removed of " + PartName + " should be Numbers.\n";
continue;
}
var MasterStdPWTData = db.tblmasterparts_st_sw.Where(m => m.PartNo == PartName && m.OpNo == OpNo && m.IsDeleted == 0).FirstOrDefault();
if (MasterStdPWTData == null)
{
db.tblmasterparts_st_sw.Add(tblmp);
db.SaveChanges();
}
else
{
MasterStdPWTData.InputWeight = tblmp.InputWeight;
MasterStdPWTData.InputWeightUnit = tblmp.InputWeightUnit;
MasterStdPWTData.MaterialRemovedQty = tblmp.MaterialRemovedQty;
MasterStdPWTData.MaterialRemovedQtyUnit = tblmp.MaterialRemovedQtyUnit;
MasterStdPWTData.ModifiedBy = Convert.ToInt32(Session["UserId"]);
MasterStdPWTData.ModifiedOn = DateTime.Now;
MasterStdPWTData.OpNo = tblmp.OpNo;
MasterStdPWTData.OutputWeight = tblmp.OutputWeight;
MasterStdPWTData.OutputWeightUnit = tblmp.OutputWeightUnit;
MasterStdPWTData.PartNo = tblmp.PartNo;
MasterStdPWTData.StdChangeoverTime = tblmp.StdChangeoverTime;
MasterStdPWTData.StdChangeoverTimeUnit = tblmp.StdChangeoverTimeUnit;
MasterStdPWTData.StdCuttingTime = tblmp.StdCuttingTime;
MasterStdPWTData.StdCuttingTimeUnit = tblmp.StdCuttingTimeUnit;
MasterStdPWTData.StdSetupTime = tblmp.StdSetupTime;
MasterStdPWTData.StdSetupTimeUnit = tblmp.StdSetupTimeUnit;
MasterStdPWTData.SAPCode = tblmp.SAPCode; //Added by Ashok
db.Entry(MasterStdPWTData).State = EntityState.Modified;
db.SaveChanges();
}
}
#endregion
}
Session["PartNo"] = ErrorMsg;
}
//return RedirectToAction("Index", "MasterParts");
return(View());
}
public ActionResult ImportOperatorData(HttpPostedFileBase file, string UploadType)
{
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
string UserId = Session["UserId"].ToString();
//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);
}
excelConnection.Close();
excelConnection1.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();
}
if ((Session["UserId"] == null) || (Session["UserId"].ToString() == String.Empty))
{
return(RedirectToAction("Login", "Login", null));
}
ViewBag.Logout = Session["Username"].ToString().ToUpper();
ViewBag.roleid = Session["RoleID"];
string text = "";
string ErrorMsg = null;
if (UploadType == "New") // Delete Duplicate and Insert New. // if not Duplicate insert that
{
#region
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tbloperatordetail tblmp = new tbloperatordetail();
String Username = Session["Username"].ToString();
tblmp.CreatedBy = Convert.ToInt32(Session["UserId"]);
tblmp.CreatedOn = DateTime.Now;
tblmp.isDeleted = 0;
string Dept = null, OperatorName = null;
int OperatorID = 0;
Dept = Convert.ToString(ds.Tables[0].Rows[i][0]);
OperatorName = Convert.ToString(ds.Tables[0].Rows[i][1]);
OperatorID = Convert.ToInt32(ds.Tables[0].Rows[i][2]);
bool check = ValidationCheckForInsertion(OperatorID);
if (check == true)
{
using (i_facility_tamlEntities db = new i_facility_tamlEntities())
{
if (string.IsNullOrEmpty(Dept) || string.IsNullOrEmpty(OperatorName) || OperatorID == 0)
{
text = text + htmlerrorMaker(Dept, OperatorName, OperatorID, "Dept, OperatorName or OperatorID cannot be empty/Check the format");
continue;
}
else
{
try
{
tblmp.Dept = Dept.Trim();
tblmp.OperatorName = OperatorName.Trim();
tblmp.OperatorID = OperatorID;
tblmp.CreatedBy = Convert.ToInt32(UserId);
tblmp.CreatedOn = DateTime.Now;
}
catch
{
}
}
}
db.tbloperatordetails.Add(tblmp);
try
{
db.SaveChanges();
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
}
}
throw;
}
}
}
#endregion
}
else if (UploadType == "Update") // Delete Duplicate and Insert New. // if not Duplicate insert that
{
#region
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tbloperatordetail tblmp = new tbloperatordetail();
String Username = Session["Username"].ToString();
tblmp.CreatedBy = Convert.ToInt32(Session["UserId"]);
tblmp.CreatedOn = DateTime.Now;
tblmp.isDeleted = 0;
string Dept = null, OperatorName = null;
int OperatorID = 0;
Dept = Convert.ToString(ds.Tables[0].Rows[i][0]);
OperatorName = Convert.ToString(ds.Tables[0].Rows[i][1]);
OperatorID = Convert.ToInt32(ds.Tables[0].Rows[i][2]);
bool check = ValidationCheckForInsertion(OperatorID);
if (check == true)
{
using (i_facility_tamlEntities db1 = new i_facility_tamlEntities())
{
if (string.IsNullOrEmpty(Dept) || string.IsNullOrEmpty(OperatorName) || OperatorID == 0)
{
text = text + htmlerrorMaker(Dept, OperatorName, OperatorID, "Dept, OperatorName or OperatorID cannot be empty/Check the format");
continue;
}
else
{
try
{
tblmp.Dept = Dept.Trim();
tblmp.OperatorName = OperatorName.Trim();
tblmp.OperatorID = OperatorID;
tblmp.CreatedBy = Convert.ToInt32(UserId);
tblmp.CreatedOn = DateTime.Now;
}
catch
{
}
}
}
db.tbloperatordetails.Add(tblmp);
try
{
db.SaveChanges();
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
}
}
throw;
}
}
else
{
using (i_facility_tamlEntities db1 = new i_facility_tamlEntities())
{
if (string.IsNullOrEmpty(Dept) || string.IsNullOrEmpty(OperatorName) || OperatorID == 0)
{
text = text + htmlerrorMaker(Dept, OperatorName, OperatorID, "Dept, OperatorName or OperatorID cannot be empty/Check the format");
continue;
}
else
{
var OPTDet = db.tbloperatordetails.Where(m => m.isDeleted == 0 && m.OperatorID == OperatorID).FirstOrDefault();
OPTDet.isDeleted = 1;
db.Entry(OPTDet).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
try
{
tblmp.Dept = Dept.Trim();
tblmp.OperatorName = OperatorName.Trim();
tblmp.OperatorID = OperatorID;
tblmp.CreatedBy = Convert.ToInt32(UserId);
tblmp.CreatedOn = DateTime.Now;
}
catch
{
}
}
}
db.tbloperatordetails.Add(tblmp);
try
{
db.SaveChanges();
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
}
}
throw;
}
}
}
#endregion
}
else if (UploadType == "OverWrite") // Delete Duplicate and Insert New. // if not Duplicate insert that
{
#region
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tbloperatordetail tblmp = new tbloperatordetail();
String Username = Session["Username"].ToString();
tblmp.CreatedBy = Convert.ToInt32(Session["UserId"]);
tblmp.CreatedOn = DateTime.Now;
tblmp.isDeleted = 0;
string Dept = null, OperatorName = null;
int OperatorID = 0;
Dept = Convert.ToString(ds.Tables[0].Rows[i][0]);
OperatorName = Convert.ToString(ds.Tables[0].Rows[i][1]);
OperatorID = Convert.ToInt32(ds.Tables[0].Rows[i][2]);
bool check = ValidationCheckForInsertion(OperatorID);
if (check == true)
{
using (i_facility_tamlEntities db1 = new i_facility_tamlEntities())
{
if (string.IsNullOrEmpty(Dept) || string.IsNullOrEmpty(OperatorName) || OperatorID == 0)
{
text = text + htmlerrorMaker(Dept, OperatorName, OperatorID, "Dept, OperatorName or OperatorID cannot be empty/Check the format");
continue;
}
else
{
try
{
tblmp.Dept = Dept.Trim();
tblmp.OperatorName = OperatorName.Trim();
tblmp.OperatorID = OperatorID;
tblmp.CreatedBy = Convert.ToInt32(UserId);
tblmp.CreatedOn = DateTime.Now;
}
catch
{
}
}
}
db.tbloperatordetails.Add(tblmp);
try
{
db.SaveChanges();
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
}
}
throw;
}
}
else
{
var OPTDet = db.tbloperatordetails.Where(m => m.isDeleted == 0 && m.OperatorID == OperatorID).FirstOrDefault();
using (i_facility_tamlEntities db1 = new i_facility_tamlEntities())
{
if (string.IsNullOrEmpty(Dept) || string.IsNullOrEmpty(OperatorName) || OperatorID == 0)
{
text = text + htmlerrorMaker(Dept, OperatorName, OperatorID, "Dept, OperatorName or OperatorID cannot be empty/Check the format");
continue;
}
else
{
try
{
OPTDet.Dept = Dept.Trim();
OPTDet.OperatorName = OperatorName.Trim();
OPTDet.ModifiedBy = Convert.ToInt32(UserId);
OPTDet.ModifiedOn = DateTime.Now;
db.Entry(OPTDet).State = System.Data.Entity.EntityState.Modified;
}
catch
{
}
}
}
//db.tbloperatordetails.Add(OPTDet);
try
{
db.SaveChanges();
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
}
}
throw;
}
}
}
#endregion
}
//TempData["txtShow"] = text;
//// ViewBag.text = text;
//Session["PartNo"] = ErrorMsg;
}
//return RedirectToAction("Index", "MasterParts");
return(RedirectToAction("Index", "OperatorModule"));
}
