public ActionResult RFIDDetails(tbl_rfidDetails model) { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); if (model.pkid > 0) { _db.Entry(model).State = System.Data.EntityState.Modified; _db.SaveChanges(); ViewBag.alert = "RFID details modified...."; ModelState.Clear(); return(View()); } else { var data = _db.tbl_rfidDetails.Where(x => x.RFIDNUMBER == model.RFIDNUMBER).ToList(); if (data.Count() == 0) { _db.tbl_rfidDetails.Add(model); _db.SaveChanges(); ViewBag.alert = "RFID details saved...."; } else { ViewBag.alert = "RFID Already Exist Please Change RFID Number...."; } ModelState.Clear(); return(View()); } }
public ActionResult operatormapping(int _id = 0) { if (_id == 0) { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); ViewBag.operatorList = new SelectList(_db.tbl_operatorDetails.ToList(), "pkid", "OperatorName"); ViewBag.shift = new SelectList(_db.tbl_operatorshift.ToList(), "pkid", "shiftname"); ViewBag.operatorRFIDLsit = new SelectList(_db.tbl_rfidDetails.ToList(), "pkid", "RFIDNUMBER"); ViewBag.truck = new SelectList(_db.tbl_TruckDetails.ToList(), "pkid", "truckNo"); TimeSpan time1 = TimeSpan.Parse(DateTime.Now.TimeOfDay.ToString()); TimeSpan time2 = TimeSpan.Parse(DateTime.Now.TimeOfDay.ToString()); if (_db.tbl_operatorshift.Where(x => x.shiftstsrtTime <= time1 && x.shiftEndTime >= time2).Count() > 0) { ViewBag.shifId = _db.tbl_operatorshift.Where(x => x.shiftstsrtTime <= time1 && x.shiftEndTime >= time2).FirstOrDefault().pkid; } return(View()); } else { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); ViewBag.operatorList = new SelectList(_db.tbl_operatorDetails.ToList(), "pkid", "OperatorName"); ViewBag.shift = new SelectList(_db.tbl_operatorshift.ToList(), "pkid", "shiftname"); ViewBag.operatorRFIDLsit = new SelectList(_db.tbl_rfidDetails.ToList(), "pkid", "RFIDNUMBER"); ViewBag.truck = new SelectList(_db.tbl_TruckDetails.ToList(), "pkid", "truckNo"); TimeSpan time1 = TimeSpan.Parse(DateTime.Now.TimeOfDay.ToString()); TimeSpan time2 = TimeSpan.Parse(DateTime.Now.TimeOfDay.ToString()); if (_db.tbl_operatorshift.Where(x => x.shiftstsrtTime <= time1 && x.shiftEndTime >= time2).Count() > 0) { ViewBag.shifId = _db.tbl_operatorMapping.Where(x => x.pkid == _id).FirstOrDefault().shift_fkid; } return(View(_db.tbl_operatorMapping.Where(x => x.pkid == _id).FirstOrDefault())); } }
public ActionResult CreateDemoRecord(tbl_weighingcontroller_demo model) { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); model.cdate = DateTime.Now.Date; _db.tbl_weighingcontroller_demo.Add(model); _db.SaveChanges(); ViewBag.alert = "Data Save successfully...."; return(View()); }
public ActionResult DbConfiguration(int _id = 0) { if (_id > 0) { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); return(View(_db.tbl_RemoteConnection.Where(x => x.pkid == _id).FirstOrDefault())); } else { return(View()); } }
public ActionResult DbConfiguration(tbl_RemoteConnection model) { string connectionstring = "Data Source=" + model.serverName + ";Initial Catalog=" + model.dataBaseName + ";User ID=" + model.userName + ";Password="******";"; weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); if (model.pkid > 0) { try { using (SqlConnection con = new SqlConnection(connectionstring)) { con.Open(); _db.Entry(model).State = System.Data.EntityState.Modified; _db.SaveChanges(); con.Close(); } ViewBag.alert = "Connection Modified ...."; ModelState.Clear(); return(View()); } catch (Exception ex) { Commonfunction.LogError(ex, HttpContext.Server.MapPath("~/ErrorLog.txt")); ViewBag.alert = ex.Message; return(View()); } } else { try { using (SqlConnection con = new SqlConnection(connectionstring)) { con.Open(); con.Close(); _db.tbl_RemoteConnection.Add(model); _db.SaveChanges(); ViewBag.alert = "Connection Saved ...."; ModelState.Clear(); return(View()); } } catch (Exception ex) { Commonfunction.LogError(ex, HttpContext.Server.MapPath("~/ErrorLog.txt")); ViewBag.alert = ex.Message; return(View()); } } return(View()); }
public ActionResult operatorDtails(int _id = 0) { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); if (_id > 0) { return(View(_db.tbl_operatorDetails.Where(x => x.pkid == _id).FirstOrDefault())); } else { return(View()); } }
public ActionResult Contractor(int _id = 0) { if (_id > 0) { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); return(View(_db.tbl_contractor.Where(x => x.pkid == _id).FirstOrDefault())); } else { return(View()); } }
public ActionResult shiftList() { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); var search = Request.Form.GetValues("search[value]")[0]; var draw = Request.Form.GetValues("draw").FirstOrDefault(); var start = Request.Form.GetValues("start").FirstOrDefault(); var length = Request.Form.GetValues("length").FirstOrDefault(); //Find Order Column string sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault(); string sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault(); int pageSize = length != null?Convert.ToInt32(length) : 0; int skip = start != null?Convert.ToInt32(start) : 0; int recordsTotal = 0; try { //dc.Configuration.LazyLoadingEnabled = false; // if your table is relational, contain foreign key var v = (from a in _db.tbl_operatorshift.Select(x => new { x.pkid, x.shiftname, x.shiftstsrtTime, x.shiftEndTime }) select a); //SORT if (!string.IsNullOrEmpty(search) && !string.IsNullOrWhiteSpace(search)) { v = (from b in _db.tbl_operatorshift.Where(x => x.shiftname.Contains(search)).Select(x => new { x.pkid, x.shiftname, x.shiftstsrtTime, x.shiftEndTime }) select b); } if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir))) { v = v.OrderBy(sortColumn, sortColumnDir); //v = v.OrderBy(x=>x.orderfkid); } recordsTotal = v.Count(); var data = v.Skip(skip).Take(pageSize).ToList(); List <Shift> newList = new List <Shift>(); foreach (var item in data) { Shift obj = new Shift(); obj.pkid = item.pkid; obj.shiftname = item.shiftname; TimeSpan ts = new TimeSpan(); ts = (TimeSpan)item.shiftstsrtTime; obj.shiftstsrtTime = ts.Hours + ":" + ts.Minutes + ":" + ts.Milliseconds; ts = new TimeSpan(); ts = (TimeSpan)item.shiftEndTime; obj.shiftEndTime = ts.Hours + ":" + ts.Minutes + ":" + ts.Milliseconds; newList.Add(obj); } return(Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = newList }, JsonRequestBehavior.AllowGet)); } catch (Exception e) { Commonfunction.LogError(e, HttpContext.Server.MapPath("~/ErrorLog.txt")); return(Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = "" }, JsonRequestBehavior.AllowGet)); } }
public ActionResult operatorMaapingWithContactor(int _id = 0) { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); ViewBag.contarctor = new SelectList(_db.tbl_contractor.ToList(), "pkid", "contratorName"); ViewBag.operatorlist = new SelectList(_db.tbl_operatorDetails.ToList(), "pkid", "OperatorName"); if (_id > 0) { return(View(_db.tbl_operatorMapiingWithContactor.Where(x => x.pkid == _id).FirstOrDefault())); } else { return(View()); } }
public ActionResult truckMapping(int _id = 0) { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); ViewBag.trucklist = new SelectList(_db.tbl_TruckDetails.ToList(), "pkid", "truckNo"); ViewBag.RFIDList = new SelectList(_db.tbl_rfidDetails.ToList(), "pkid", "RFIDNUMBER"); if (_id > 0) { return(View(_db.tbl_truckMapping.Where(x => x.pkid == _id).FirstOrDefault())); } else { return(View()); } }
public ActionResult operatormapping(tbl_operatorMapping model) { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); ViewBag.operatorList = new SelectList(_db.tbl_operatorDetails.ToList(), "pkid", "OperatorName"); ViewBag.shift = new SelectList(_db.tbl_operatorshift.ToList(), "pkid", "shiftname"); ViewBag.truck = new SelectList(_db.tbl_TruckDetails.ToList(), "pkid", "truckNo"); //ViewBag.shifId = _db.tbl_operatorshift.Where(x => x.shiftstsrtTime <= System.DateTime.Now.TimeOfDay && x.shiftEndTime <= System.DateTime.Now.TimeOfDay).FirstOrDefault().pkid; ViewBag.operatorRFIDLsit = new SelectList(_db.tbl_rfidDetails.ToList(), "pkid", "RFIDNUMBER"); //var truck = _db.tbl_truckMapping.Where(n => n.truckRFID == model.operatorFKID).Count(); // model.operatorFKID = _db.tbl_rfidDetails.Where(x => x.pkid == model.rfiDfkId).FirstOrDefault().RFIDNUMBER; int count = _db.tbl_operatorMapping.Where(x => x.shift_fkid == model.shift_fkid && x.operator_fkId == model.operator_fkId && x.workingdate == model.workingdate).Count(); if (_db.tbl_operatorMapping.Where(x => x.shift_fkid == model.shift_fkid && x.operator_fkId == model.operator_fkId && x.workingdate == model.workingdate && x.rfiDfkId == model.rfiDfkId).Count() == 0) { if (_db.tbl_truckMapping.Where(n => n.truckRFID == model.operatorFKID).Count() == 0) { if (model.pkid > 0) { _db.Entry(model).State = System.Data.EntityState.Modified; _db.SaveChanges(); ModelState.Clear(); ViewBag.alert = "Operator Modified....."; return(View()); } else { _db.tbl_operatorMapping.Add(model); _db.SaveChanges(); ModelState.Clear(); ViewBag.alert = "Operator saved..... "; return(View()); } } else { ViewBag.alert = "Invalid Operator RFID "; return(View()); } } else { ViewBag.alert = "Operator already exist for this shift "; return(View()); } }
public ActionResult truckMapping(tbl_truckMapping model) { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); ViewBag.trucklist = new SelectList(_db.tbl_TruckDetails.ToList(), "pkid", "truckNo"); ViewBag.RFIDList = new SelectList(_db.tbl_rfidDetails.ToList(), "pkid", "RFIDNUMBER"); model.truckNo = _db.tbl_TruckDetails.Where(x => x.pkid == model.truckFKId).FirstOrDefault().truckNo; model.truckRFID = _db.tbl_rfidDetails.Where(x => x.pkid == model.rfif_fkId).FirstOrDefault().RFIDNUMBER; if (model.pkid > 0) { var truckDTL = _db.tbl_truckMapping.Where(x => x.truckRFID == model.truckRFID).ToList(); if (truckDTL.Count() == 0) { _db.Entry(model).State = System.Data.EntityState.Modified; _db.SaveChanges(); ViewBag.alert = "Mapping Modified......"; ModelState.Clear(); return(View()); } else { ViewBag.alert = "RFID Already Mapped With Another Truck......"; return(View()); } } else { var truckDTL = _db.tbl_truckMapping.Where(x => x.truckRFID == model.truckRFID).ToList(); if (truckDTL.Count() == 0) { _db.tbl_truckMapping.Add(model); _db.SaveChanges(); ViewBag.alert = "Mapping saved......"; ModelState.Clear(); return(View()); } else { ViewBag.alert = "RFID Already Mapped With Another Truck....."; return(View()); } } }
public ActionResult operatorMaapingWithContactor(tbl_operatorMapiingWithContactor model) { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); ViewBag.contarctor = new SelectList(_db.tbl_contractor.ToList(), "pkid", "contratorName"); ViewBag.operatorlist = new SelectList(_db.tbl_operatorDetails.ToList(), "pkid", "OperatorName"); if (model.pkid > 0) { if (_db.tbl_operatorMapiingWithContactor.Where(x => x.operatorid == model.operatorid && x.contactorId == model.contactorId).Count() == 0) { model.operatorname = _db.tbl_operatorDetails.Where(x => x.pkid == model.operatorid).FirstOrDefault().OperatorName; model.contactorName = _db.tbl_contractor.Where(x => x.pkid == model.contactorId).FirstOrDefault().contratorName; _db.Entry(model).State = System.Data.EntityState.Modified; _db.SaveChanges(); ViewBag.alert = "Operator mapping modified..."; return(View()); } else { ViewBag.alert = "Operator mapped already exist......"; } } else { if (_db.tbl_operatorMapiingWithContactor.Where(x => x.operatorid == model.operatorid && x.contactorId == model.contactorId).Count() == 0) { model.operatorname = _db.tbl_operatorDetails.Where(x => x.pkid == model.operatorid).FirstOrDefault().OperatorName; model.contactorName = _db.tbl_contractor.Where(x => x.pkid == model.contactorId).FirstOrDefault().contratorName; model.cdate = System.DateTime.Now.Date; _db.tbl_operatorMapiingWithContactor.Add(model); _db.SaveChanges(); } else { ViewBag.alert = "Operator mapped already exist......"; } } return(View()); }
public ActionResult Contractor(tbl_contractor model) { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); if (model.pkid > 0) { _db.Entry(model).State = System.Data.EntityState.Modified; _db.SaveChanges(); ViewBag.alert = "Contractor Modified...."; ModelState.Clear(); return(View()); } else { _db.tbl_contractor.Add(model); _db.SaveChanges(); ViewBag.alert = "Contractor Modified...."; ModelState.Clear(); return(View()); } }
public ActionResult operatorDtails(tbl_operatorDetails model) { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); if (model.pkid > 0) { _db.Entry(model).State = System.Data.EntityState.Modified; _db.SaveChanges(); ViewBag.alert = "Operator details modified.."; ModelState.Clear(); return(View()); } else { _db.tbl_operatorDetails.Add(model); _db.SaveChanges(); ViewBag.alert = "Operator details saved.."; ModelState.Clear(); return(View()); } }
public ActionResult truckMappingList() { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); var search = Request.Form.GetValues("search[value]")[0]; var draw = Request.Form.GetValues("draw").FirstOrDefault(); var start = Request.Form.GetValues("start").FirstOrDefault(); var length = Request.Form.GetValues("length").FirstOrDefault(); //Find Order Column string sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault(); string sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault(); int pageSize = length != null?Convert.ToInt32(length) : 0; int skip = start != null?Convert.ToInt32(start) : 0; int recordsTotal = 0; try { //dc.Configuration.LazyLoadingEnabled = false; // if your table is relational, contain foreign key var v = (from a in _db.tbl_truckMapping.Select(x => new { x.pkid, x.truckNo, x.truckRFID, x.comments }) select a); //SORT if (!string.IsNullOrEmpty(search) && !string.IsNullOrWhiteSpace(search)) { v = (from b in _db.tbl_truckMapping.Where(x => x.truckNo.Contains(search) || x.truckRFID.Contains(search)).Select(x => new { x.pkid, x.truckNo, x.truckRFID, x.comments }) select b); } if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir))) { v = v.OrderBy(sortColumn, sortColumnDir); //v = v.OrderBy(x=>x.orderfkid); } recordsTotal = v.Count(); var data = v.Skip(skip).Take(pageSize).ToList(); return(Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data }, JsonRequestBehavior.AllowGet)); } catch (Exception e) { Commonfunction.LogError(e, HttpContext.Server.MapPath("~/ErrorLog.txt")); return(Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = "" }, JsonRequestBehavior.AllowGet)); } }
public void start() { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); foreach (var server in _db.tbl_RemoteConnection.ToList()) { #region string connectionstring = "Data Source=" + server.serverName + ";Initial Catalog=" + server.dataBaseName + ";User ID=" + server.userName + ";Password="******";"; //string connectionString=_db.tbl_accessConnection.FirstOrDefault().accessconnection; SqlConnection con = new SqlConnection(connectionstring); //OleDbConnection con= new OleDbConnection(connectionstring); SqlCommand cmd; string date1 = ""; #region if (date1 != "") { long idint = 0; try { idint = (long)_db.tbl_storemachineData.Max(x => x.machinePkid); } catch { idint = 0; } if (date1 == "") { cmd = new SqlCommand("select * from tbl_WeighingMachineFill", con); } else { DateTime dt = DateTime.Now.Date; string datestring = dt.ToString("MM-dd-yyyy"); cmd = new SqlCommand("select * from tbl_WeighingMachineFill where CAST(cdateTime as date)='" + datestring + "' ", con); } try { con.Open(); SqlDataAdapter sda = new SqlDataAdapter(); sda.SelectCommand = cmd; DataSet ds = new DataSet(); sda.Fill(ds); con.Close(); using (var ctx = new weighingcontrolSystemEntities()) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { var data = ds.Tables[0].Rows[i][1]; tbl_storemachineData smd = new tbl_storemachineData(); smd.ScaleId = ds.Tables[0].Rows[i][1].ToString().Trim(); smd.truckRFIDIN = ds.Tables[0].Rows[i][2].ToString().Trim(); smd.truckRFIDOUT = ds.Tables[0].Rows[i][3].ToString().Trim(); smd.operatorRFID = ds.Tables[0].Rows[i][4].ToString().Trim(); TimeSpan time1 = TimeSpan.Parse(DateTime.Now.TimeOfDay.ToString()); TimeSpan time2 = TimeSpan.Parse(DateTime.Now.TimeOfDay.ToString()); if (_db.tbl_operatorshift.Where(x => x.shiftstsrtTime <= time1 && x.shiftEndTime >= time2).Count() > 0) { smd.shiftTime = _db.tbl_operatorshift.Where(x => x.shiftstsrtTime <= time1 && x.shiftEndTime >= time2).FirstOrDefault().pkid; } smd.material = ds.Tables[0].Rows[i][5].ToString().Trim(); smd.grossWeight = Convert.ToDecimal(ds.Tables[0].Rows[i][6].ToString().Trim()); smd.dateTimeMachine = Convert.ToDateTime(ds.Tables[0].Rows[i][7].ToString().Trim()); smd.machinePkid = Convert.ToInt32(ds.Tables[0].Rows[i][0].ToString().Trim()); // rfid MappedOrNot if (_db.tbl_truckMapping.Where(x => x.truckRFID == smd.truckRFIDIN || x.truckRFID == smd.truckRFIDOUT).Count() == 1) { // Truck In and unque Record save into database othe wise skip if (smd.truckRFIDIN != "" && _db.tbl_storemachineData.Where(x => x.truckRFIDIN == smd.truckRFIDIN && x.ScaleId == smd.ScaleId && x.dateTimeMachine == smd.dateTimeMachine).Count() == 0) { smd.status = "IN"; smd.netWeight = 0; smd.fairWeight = smd.grossWeight; _db.tbl_storemachineData.Add(smd); _db.SaveChanges(); } else { if (smd.truckRFIDOUT != "" && _db.tbl_storemachineData.Where(x => x.truckRFIDOUT == smd.truckRFIDOUT && x.ScaleId == smd.ScaleId && x.dateTimeMachine == smd.dateTimeMachine).Count() == 0) { if (_db.tbl_storemachineData.Where(x => x.truckRFIDIN == smd.truckRFIDOUT).Count() > 0) { long maxId = _db.tbl_storemachineData.Where(x => x.truckRFIDIN == smd.truckRFIDOUT).Max(x => x.pkid); decimal maxVal = (decimal)_db.tbl_storemachineData.Where(x => x.pkid == maxId).FirstOrDefault().grossWeight; if (maxVal != 0) { smd.status = "OUT"; smd.netWeight = smd.grossWeight - maxVal; smd.fairWeight = maxVal; } } else { smd.status = "OUT"; smd.netWeight = smd.grossWeight; smd.fairWeight = smd.grossWeight; smd.outGrossWeight = smd.grossWeight; smd.outNetWeight = smd.grossWeight; smd.outPkid = smd.machinePkid; smd.netWeight = 0; } if (smd.truckRFIDOUT != "" && smd.truckRFIDOUT != null && smd.status == "OUT") { try { long maxId = _db.tbl_storemachineData.Where(x => x.truckRFIDIN == smd.truckRFIDOUT && x.outPkid == null).Max(x => x.pkid); if (maxId > 0) { var updateData = _db.tbl_storemachineData.Where(x => x.pkid == maxId).FirstOrDefault(); updateData.outPkid = Convert.ToInt32(ds.Tables[0].Rows[i][0].ToString().Trim()); updateData.material = ds.Tables[0].Rows[i][5].ToString().Trim(); updateData.outGrossWeight = Convert.ToDecimal(ds.Tables[0].Rows[i][6].ToString().Trim()); updateData.outTime = Convert.ToDateTime(ds.Tables[0].Rows[i][7].ToString().Trim()); updateData.outLocation = smd.ScaleId; TimeSpan Outtime1 = TimeSpan.Parse(ds.Tables[0].Rows[i][7].ToString().Trim()); if (_db.tbl_operatorshift.Where(x => x.shiftstsrtTime <= Outtime1 && x.shiftEndTime >= Outtime1).Count() > 0) { updateData.outShift = _db.tbl_operatorshift.Where(x => x.shiftstsrtTime <= Outtime1 && x.shiftEndTime >= Outtime1).FirstOrDefault().pkid; } else { updateData.outShift = 0; } _db.Entry(updateData).State = EntityState.Modified; _db.SaveChanges(); } else { _db.tbl_storemachineData.Add(smd); _db.SaveChanges(); } } catch { } } } } } } } } catch (Exception ex) { //itechDll.Commonfunction.LogError(ex, @"~/Errorlog.txt"); Commonfunction.LogError(ex, Path.Combine("~/ErrorLog.txt")); } #endregion } else { long idint = 0; try { idint = (long)_db.tbl_storemachineData.Max(x => x.machinePkid); } catch { idint = 0; } cmd = new SqlCommand("select * from tbl_WeighingMachineFill where pkid>=" + idint + " ", con); try { con.Open(); SqlDataAdapter sda = new SqlDataAdapter(); sda.SelectCommand = cmd; DataSet ds = new DataSet(); sda.Fill(ds); con.Close(); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { var data = ds.Tables[0].Rows[i][1]; tbl_storemachineData smd = new tbl_storemachineData(); smd.ScaleId = ds.Tables[0].Rows[i][1].ToString().Trim(); smd.truckRFIDIN = ds.Tables[0].Rows[i][2].ToString().Trim(); smd.truckRFIDOUT = ds.Tables[0].Rows[i][3].ToString().Trim(); smd.operatorRFID = ds.Tables[0].Rows[i][4].ToString().Trim(); // shift Time TimeSpan time1 = TimeSpan.Parse(DateTime.Now.TimeOfDay.ToString()); TimeSpan time2 = TimeSpan.Parse(DateTime.Now.TimeOfDay.ToString()); if (_db.tbl_operatorshift.Where(x => x.shiftstsrtTime <= time1 && x.shiftEndTime >= time2).Count() > 0) { smd.shiftTime = _db.tbl_operatorshift.Where(x => x.shiftstsrtTime <= time1 && x.shiftEndTime >= time2).FirstOrDefault().pkid; } smd.material = ds.Tables[0].Rows[i][5].ToString().Trim(); smd.grossWeight = Convert.ToDecimal(ds.Tables[0].Rows[i][6].ToString().Trim()); smd.dateTimeMachine = Convert.ToDateTime(ds.Tables[0].Rows[i][7].ToString().Trim()); smd.machinePkid = Convert.ToInt32(ds.Tables[0].Rows[i][0].ToString().Trim()); // rfid MappedOrNot if (_db.tbl_truckMapping.Where(x => x.truckRFID == smd.truckRFIDIN || x.truckRFID == smd.truckRFIDOUT).Count() == 1) { // Truck In and unque Record save into database othe wise skip if (smd.truckRFIDIN != "" && _db.tbl_storemachineData.Where(x => x.truckRFIDIN == smd.truckRFIDIN && x.ScaleId == smd.ScaleId && x.dateTimeMachine == smd.dateTimeMachine).Count() == 0) { smd.status = "IN"; smd.netWeight = 0; smd.fairWeight = smd.grossWeight; _db.tbl_storemachineData.Add(smd); _db.SaveChanges(); } else { if (smd.truckRFIDOUT != "" && _db.tbl_storemachineData.Where(x => x.truckRFIDOUT == smd.truckRFIDOUT && x.ScaleId == smd.ScaleId && x.dateTimeMachine == smd.dateTimeMachine).Count() == 0) { if (_db.tbl_storemachineData.Where(x => x.truckRFIDIN == smd.truckRFIDOUT && x.outPkid == null).Count() > 0) { long maxId = _db.tbl_storemachineData.Where(x => x.truckRFIDIN == smd.truckRFIDOUT).Max(x => x.pkid); decimal maxVal = (decimal)_db.tbl_storemachineData.Where(x => x.pkid == maxId).FirstOrDefault().grossWeight; if (maxVal != 0) { smd.status = "OUT"; smd.netWeight = smd.grossWeight - maxVal; smd.fairWeight = maxVal; } } else { smd.status = "OUT"; smd.netWeight = smd.grossWeight; smd.fairWeight = smd.grossWeight; smd.outGrossWeight = smd.grossWeight; smd.outNetWeight = smd.grossWeight; smd.outPkid = smd.machinePkid; smd.netWeight = 0; } if (smd.truckRFIDOUT != "" && smd.truckRFIDOUT != null && smd.status == "OUT") { try { long maxId = 0; try { maxId = _db.tbl_storemachineData.Where(x => x.truckRFIDIN == smd.truckRFIDOUT).Max(x => x.pkid); } catch { } if (maxId > 0) { var updateData = _db.tbl_storemachineData.Where(x => x.pkid == maxId).FirstOrDefault(); // masterid = Convert.ToInt32(ds.Tables[0].Rows[i][0]); updateData.outPkid = Convert.ToInt32(ds.Tables[0].Rows[i][0].ToString().Trim()); updateData.material = ds.Tables[0].Rows[i][5].ToString().Trim(); //updateData.truckRFIDIN = ds.Tables[0].Rows[i][2].ToString().Trim(); try { updateData.outGrossWeight = Convert.ToDecimal(ds.Tables[0].Rows[i][6].ToString().Trim()); } catch { //cmd.CommandText = "SELECT top 1 TgrossWeight FROM [TBForce].[dbo].[tbl_WeighingMachineFill] WHERE pkid < (" + masterid + ") and RFID1 = " + updateData.truckRFIDIN + "ORDER BY pkid DESC"; //cmd.CommandType = CommandType.Text; //cmd.Connection = con; //con.Open(); //returnValue = cmd.ExecuteScalar(); //con.Close(); //smd.grossWeight = Convert.ToDecimal(returnValue); } updateData.outTime = Convert.ToDateTime(ds.Tables[0].Rows[i][7].ToString().Trim()); updateData.outLocation = smd.ScaleId; TimeSpan Outtime1 = TimeSpan.Parse(ds.Tables[0].Rows[i][7].ToString().Trim()); if (_db.tbl_operatorshift.Where(x => x.shiftstsrtTime <= Outtime1 && x.shiftEndTime >= Outtime1).Count() > 0) { updateData.outShift = _db.tbl_operatorshift.Where(x => x.shiftstsrtTime <= Outtime1 && x.shiftEndTime >= Outtime1).FirstOrDefault().pkid; } else { updateData.outShift = 0; } _db.Entry(updateData).State = EntityState.Modified; _db.SaveChanges(); } else { _db.tbl_storemachineData.Add(smd); _db.SaveChanges(); } } catch { } } } else { } } } else { if (smd.grossWeight > 50) { smd.truckRFIDOUT = "0088"; smd.status = "OUT"; smd.netWeight = smd.grossWeight; smd.fairWeight = smd.grossWeight; smd.outGrossWeight = smd.grossWeight; smd.outNetWeight = smd.grossWeight; smd.outPkid = smd.machinePkid; _db.tbl_storemachineData.Add(smd); _db.SaveChanges(); } else { smd.truckRFIDIN = "0088"; smd.status = "IN"; smd.netWeight = 0; smd.fairWeight = smd.grossWeight; _db.tbl_storemachineData.Add(smd); _db.SaveChanges(); } } } } catch (Exception ex) { //Commonfunction.LogError(ex, Path.Combine("~/ErrorLog.txt")); } } } #endregion }
public ActionResult getTruckWorkingList() { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); var search = Request.Form.GetValues("search[value]")[0]; var draw = Request.Form.GetValues("draw").FirstOrDefault(); var start = Request.Form.GetValues("start").FirstOrDefault(); var length = Request.Form.GetValues("length").FirstOrDefault(); //Find Order Column string sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault(); string sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault(); int pageSize = length != null?Convert.ToInt32(length) : 0; int skip = start != null?Convert.ToInt32(start) : 0; int recordsTotal = 0; //DateTime dtcurr = Convert.ToDateTime("2017-04-27"); try { DateTime dtcurr = DateTime.Now.Date; List <importData> importData = new List <importData>(); var data = (from report in _db.tbl_storemachineData where EntityFunctions.TruncateTime(report.dateTimeMachine) <= dtcurr && report.truckRFIDOUT != "" select new { report.pkid, report.ScaleId, report.truckRFIDIN, report.truckRFIDOUT, report.fairWeight, report.operatorRFID, report.grossWeight, report.netWeight, report.status, report.dateTimeMachine, report.outPkid, report.outTime, report.outGrossWeight, report.outLocation, report.shiftTime }).ToList(); foreach (var item in data.Skip(skip).Take(pageSize)) { importData _obj = new importData(); _obj.pkid = item.pkid; _obj.ScaleId = item.ScaleId; if (item.truckRFIDIN != null && item.truckRFIDIN != "") { _obj.truckNo = (from rfId1 in _db.tbl_rfidDetails where rfId1.RFIDNUMBER == item.truckRFIDIN join truckMap in _db.tbl_truckMapping on rfId1.pkid equals truckMap.rfif_fkId join truckdetails in _db.tbl_TruckDetails on truckMap.truckFKId equals truckdetails.pkid select new { truckdetails.truckNo }).FirstOrDefault().truckNo; } else { _obj.truckNo = (from rfId1 in _db.tbl_rfidDetails where rfId1.RFIDNUMBER == item.truckRFIDOUT join truckMap in _db.tbl_truckMapping on rfId1.pkid equals truckMap.rfif_fkId join truckdetails in _db.tbl_TruckDetails on truckMap.truckFKId equals truckdetails.pkid select new { truckdetails.truckNo }).FirstOrDefault().truckNo; } _obj.rfid = item.operatorRFID; _obj.netweight = item.netWeight.ToString(); _obj.tareWeight = item.fairWeight.ToString(); _obj.grossWeight = item.grossWeight.ToString(); _obj.status = item.status; _obj.outLocation = item.outLocation; _obj.shift = item.shiftTime.ToString(); _obj.pkidOut = (long?)item.outPkid ?? 0; try { _obj.grossWeightOut = (decimal)item.outGrossWeight; _obj.outTime = (DateTime)item.outTime; } catch { } _obj.dateTimeMachine = (DateTime)item.dateTimeMachine; importData.Add(_obj); } List <ExcelExport> lsitExcel = new List <ExcelExport>(); int i = 1; foreach (var item in importData) { ExcelExport _obj = new ExcelExport(); _obj.SRNO = i; _obj.TRUCKNO = item.truckNo; _obj.inLocation = item.ScaleId; try { _obj.CAPACITY = _db.tbl_TruckDetails.Where(x => x.truckNo == item.truckNo).FirstOrDefault().Contact; } catch { _obj.CAPACITY = ""; } _obj.TAREWEIGHT = item.tareWeight; _obj.GROSSWEIGHT = item.grossWeight; if (item.rfid != null && item.rfid != "") { try { long rfidpkid = _db.tbl_rfidDetails.Where(x => x.RFIDNUMBER == item.rfid).FirstOrDefault().pkid; long operatorPkid = (long)_db.tbl_operatorMapping.Where(x => x.rfiDfkId == rfidpkid).FirstOrDefault().operator_fkId; long ContactorId = (long)_db.tbl_operatorMapiingWithContactor.Where(x => x.operatorid == operatorPkid).FirstOrDefault().contactorId; _obj.CONTRACTOR = _db.tbl_contractor.Where(x => x.pkid == ContactorId).FirstOrDefault().contratorName; _obj.OPERATOR = _db.tbl_operatorDetails.Where(x => x.pkid == operatorPkid).FirstOrDefault().OperatorName; } catch { } } _obj.NETWEIGHT = item.netweight; try { _obj.FILL_FACTOR = (Convert.ToDecimal(((Convert.ToDecimal(item.netweight)) / Convert.ToDecimal(_db.tbl_TruckDetails.Where(x => x.truckNo == item.truckNo).FirstOrDefault().Contact))) * 100).ToString("0.00"); } catch { _obj.FILL_FACTOR = "0"; } _obj.TIME = item.dateTimeMachine; _obj.STATUS = item.ScaleId; _obj.outGrossWeight = item.grossWeightOut.ToString(); _obj.outpkid = item.pkidOut; _obj.OutTime = item.outTime; _obj.outLocation = item.outLocation; long shiftId = 0; if (item.outTime != default(DateTime)) { TimeSpan curr = item.outTime.TimeOfDay; var shifdata = _db.tbl_operatorshift.ToList(); foreach (var sht in shifdata) { if ((curr >= sht.shiftstsrtTime) && (curr <= sht.shiftEndTime)) { _obj.shift = sht.shiftname; shiftId = sht.pkid; } } } else { TimeSpan curr = item.dateTimeMachine.TimeOfDay; var shifdata = _db.tbl_operatorshift.ToList(); foreach (var sht in shifdata) { if ((curr >= sht.shiftstsrtTime) && (curr <= sht.shiftEndTime)) { _obj.shift = sht.shiftname; shiftId = sht.pkid; } } } if (_obj.shift != "") { long truckPkid = 36; try { truckPkid = _db.tbl_TruckDetails.Where(x => x.truckNo == _obj.TRUCKNO).FirstOrDefault().pkid; } catch { truckPkid = 36; } DateTime workingdate = _obj.TIME.Date; if (_db.tbl_operatorMapping.Where(x => x.shift_fkid == shiftId && x.rfiDfkId == truckPkid && x.workingdate == workingdate).Count() > 0) { long operatorPkid = (long)_db.tbl_operatorMapping.Where(x => x.shift_fkid == shiftId && x.rfiDfkId == truckPkid && x.workingdate == workingdate).FirstOrDefault().operator_fkId; long ContactorId = (long)_db.tbl_operatorMapiingWithContactor.Where(x => x.operatorid == operatorPkid).FirstOrDefault().contactorId; _obj.CONTRACTOR = _db.tbl_contractor.Where(x => x.pkid == ContactorId).FirstOrDefault().contratorName; _obj.OPERATOR = _db.tbl_operatorDetails.Where(x => x.pkid == operatorPkid).FirstOrDefault().OperatorName; } else { if (_db.tbl_operatorMapping.Where(x => x.rfiDfkId == truckPkid && x.shift_fkid == shiftId).Count() > 0) { try { long maxId = _db.tbl_operatorMapping.Where(x => x.rfiDfkId == truckPkid && x.shift_fkid == shiftId).Max(x => x.pkid); long operatorPkid = (long)_db.tbl_operatorMapping.Where(x => x.pkid == maxId).FirstOrDefault().operator_fkId; long ContactorId = (long)_db.tbl_operatorMapiingWithContactor.Where(x => x.operatorid == operatorPkid).FirstOrDefault().contactorId; _obj.CONTRACTOR = _db.tbl_contractor.Where(x => x.pkid == ContactorId).FirstOrDefault().contratorName; _obj.OPERATOR = _db.tbl_operatorDetails.Where(x => x.pkid == operatorPkid).FirstOrDefault().OperatorName; } catch { _obj.CONTRACTOR = ""; _obj.OPERATOR = ""; } } else { _obj.CONTRACTOR = ""; _obj.OPERATOR = ""; } } } lsitExcel.Add(_obj); i++; } List <dasBordDetaisModal> dasboardModel = new List <dasBordDetaisModal>(); foreach (var dm in lsitExcel.GroupBy(x => new { x.TRUCKNO }).ToList()) { dasBordDetaisModal dmObj = new dasBordDetaisModal(); var firstRow = dm.FirstOrDefault(); dmObj.TruckNo = firstRow.TRUCKNO; if (lsitExcel.Where(x => x.TRUCKNO == firstRow.TRUCKNO && x.shift == "Shift A").Count() > 0) { dmObj.TotalTonShiftA = (decimal)lsitExcel.Where(x => x.TRUCKNO == firstRow.TRUCKNO && x.shift == "Shift A").Sum(x => decimal.Parse(x.NETWEIGHT)); dmObj.noTripShifA = lsitExcel.Where(x => x.TRUCKNO == firstRow.TRUCKNO && x.shift == "Shift A").Count(); dmObj.ToltalFillFactorA = lsitExcel.Where(x => x.TRUCKNO == firstRow.TRUCKNO && x.shift == "Shift A").Sum(x => decimal.Parse(x.FILL_FACTOR)) / dmObj.noTripShifA; } if (lsitExcel.Where(x => x.TRUCKNO == firstRow.TRUCKNO && x.shift == "Shift B").Count() > 0) { dmObj.noTripShifB = lsitExcel.Where(x => x.TRUCKNO == firstRow.TRUCKNO && x.shift == "Shift B").Count(); dmObj.TotalTonShiftB = (decimal)lsitExcel.Where(x => x.TRUCKNO == firstRow.TRUCKNO && x.shift == "Shift B").Sum(x => decimal.Parse(x.NETWEIGHT)); dmObj.ToltalFillFactorB = lsitExcel.Where(x => x.TRUCKNO == firstRow.TRUCKNO && x.shift == "Shift A").Sum(x => decimal.Parse(x.FILL_FACTOR)) / dmObj.noTripShifB; } if (lsitExcel.Where(x => x.TRUCKNO == firstRow.TRUCKNO && x.shift == "Shift C").Count() > 0) { dmObj.TotalTonShiftC = (decimal)lsitExcel.Where(x => x.TRUCKNO == firstRow.TRUCKNO && x.shift == "Shift C").Sum(x => decimal.Parse(x.NETWEIGHT)); dmObj.noTripShifC = lsitExcel.Where(x => x.TRUCKNO == firstRow.TRUCKNO && x.shift == "Shift C").Count(); dmObj.ToltalFillFactorC = lsitExcel.Where(x => x.TRUCKNO == firstRow.TRUCKNO && x.shift == "Shift A").Sum(x => decimal.Parse(x.FILL_FACTOR)) / dmObj.noTripShifC; //dmObj.ToltalFillFactorC = (decimal)lsitExcel.Where(x => x.TRUCKNO == firstRow.TRUCKNO && x.shift == "Shift C").Sum(x => decimal.Parse(x.FILL_FACTOR)); } dmObj.noTripShifFD = dmObj.noTripShifC + dmObj.noTripShifB + dmObj.noTripShifA; dmObj.TotalTonShiftFD = dmObj.TotalTonShiftC + dmObj.TotalTonShiftB + dmObj.TotalTonShiftA; dmObj.ToltalFillFactorFD = dmObj.ToltalFillFactorC + dmObj.ToltalFillFactorB + dmObj.ToltalFillFactorA; dasboardModel.Add(dmObj); } try { //dc.Configuration.LazyLoadingEnabled = false; // if your table is relational, contain foreign key var v = (from a in dasboardModel select a); //SORT if (!string.IsNullOrEmpty(search) && !string.IsNullOrWhiteSpace(search)) { v = (from b in dasboardModel.Where(x => x.TruckNo.Contains(search)) select b); } if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir))) { v = v.OrderBy(sortColumn, sortColumnDir); //v = v.OrderBy(x=>x.orderfkid); } recordsTotal = data.Count(); var dataList = v.ToList(); return(Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = dataList }, JsonRequestBehavior.AllowGet)); } catch (Exception e) { Commonfunction.LogError(e, HttpContext.Server.MapPath("~/ErrorLog.txt")); return(Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = "" }, JsonRequestBehavior.AllowGet)); } } catch (Exception e) { ViewBag.Msg = "Your RFID and Operator Mapping Not Done..."; Commonfunction.LogError(e, HttpContext.Server.MapPath("~/ErrorLog.txt")); return(Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = "" }, JsonRequestBehavior.AllowGet)); } }
public ActionResult operatorshiftList() { weighingcontrolSystemEntities _db = new weighingcontrolSystemEntities(); var search = Request.Form.GetValues("search[value]")[0]; var draw = Request.Form.GetValues("draw").FirstOrDefault(); var start = Request.Form.GetValues("start").FirstOrDefault(); var length = Request.Form.GetValues("length").FirstOrDefault(); //Find Order Column string sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault(); string sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault(); int pageSize = length != null?Convert.ToInt32(length) : 0; int skip = start != null?Convert.ToInt32(start) : 0; int recordsTotal = 0; try { //dc.Configuration.LazyLoadingEnabled = false; // if your table is relational, contain foreign key var v = (from a in _db.tbl_operatorMapping select new { pkid = a.pkid, operatorName = _db.tbl_operatorDetails.Where(x => x.pkid == a.operator_fkId).FirstOrDefault().OperatorName, truckNo = _db.tbl_TruckDetails.Where(x => x.pkid == a.rfiDfkId).FirstOrDefault().truckNo, shiftName = _db.tbl_operatorshift.Where(x => x.pkid == a.shift_fkid).FirstOrDefault().shiftname, date = a.workingdate }); //SORT if (!string.IsNullOrEmpty(search) && !string.IsNullOrWhiteSpace(search)) { v = (from a in _db.tbl_operatorMapping select new { pkid = a.pkid, operatorName = _db.tbl_operatorDetails.Where(x => x.pkid == a.operator_fkId).FirstOrDefault().OperatorName, truckNo = _db.tbl_TruckDetails.Where(x => x.pkid == a.rfiDfkId).FirstOrDefault().truckNo, shiftName = _db.tbl_operatorshift.Where(x => x.pkid == a.shift_fkid).FirstOrDefault().shiftname, date = a.workingdate }).Where(x => x.operatorName.Contains(search) || x.truckNo.Contains(search)); } if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir))) { v = v.OrderBy(sortColumn, sortColumnDir); //v = v.OrderBy(x=>x.orderfkid); } recordsTotal = v.Count(); var data = v.Skip(skip).Take(pageSize).ToList(); List <OperatorList> opelist = new List <OperatorList>(); foreach (var item in data) { OperatorList _obj = new OperatorList(); _obj.pkid = item.pkid; _obj.shiftName = item.shiftName; _obj.truckNo = item.truckNo; _obj.operatorName = item.operatorName; _obj.date = Convert.ToDateTime(item.date).ToShortDateString(); opelist.Add(_obj); } return(Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = opelist }, JsonRequestBehavior.AllowGet)); } catch (Exception e) { Commonfunction.LogError(e, HttpContext.Server.MapPath("~/ErrorLog.txt")); return(Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = "" }, JsonRequestBehavior.AllowGet)); } }