public override IActionResult Index() { ViewBag.Roles = JsonConvert.SerializeObject(_roleManager.Roles.ToList() .Select(x => new { label = x.Name, value = x.Name }) .Where(x => x.value != "Contractor") .OrderBy(x => x.value)); //var NetworkNumber = _network.GetAll().Where(x => x.DepartmentId.Equals(Id) && x.IsClosed == false).ToList(); ViewBag.ASP = JsonConvert.SerializeObject(_asp.GetAll().ToList() .Select(x => new { label = x.Name, value = x.Name }) .Where(x => x.value != "Contractor") .OrderBy(x => x.value)); return(base.Index()); }
public async Task <IActionResult> Import(IFormFile file) { int TOTAL_INSERT = 0; int TOTAL_UPDATE = 0; var uploads = System.IO.Path.Combine(_env.WebRootPath, "temp"); file = Request.Form.Files[0]; using (var fileStream = new FileStream(System.IO.Path.Combine(uploads, file.FileName), FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite)) { await file.CopyToAsync(fileStream); try { using (var Stream = new FileStream(System.IO.Path.Combine(uploads, file.FileName), FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite)) { using (ExcelPackage package = new ExcelPackage(Stream)) { ExcelWorksheet worksheet = package.Workbook.Worksheets[1]; int rowCount = worksheet.Dimension.Rows; int ColCount = worksheet.Dimension.Columns; for (int row = 2; row <= rowCount; row++) { if (worksheet.Cells[row, 1].Value != null && worksheet.Cells[row, 2].Value != null) { DateTime dt; var assigmentId = worksheet.Cells[row, 1].Value.ToString(); var shId = worksheet.Cells[row, 2].Value.ToString(); var siteId = "-"; if (worksheet.Cells[row, 3].Value != null) { siteId = worksheet.Cells[row, 3].Value.ToString(); } var siteName = "-"; if (worksheet.Cells[row, 4].Value != null) { siteName = worksheet.Cells[row, 4].Value.ToString(); } var assigmentAcceptDate = DateTime.Parse(worksheet.Cells[row, 5].Text); var prNumber = worksheet.Cells[row, 6].Value.ToString(); var prDate = DateTime.Parse(worksheet.Cells[row, 7].Text); var poNumber = worksheet.Cells[row, 8].Value.ToString(); var poDate = DateTime.Parse(worksheet.Cells[row, 9].Text); var poLineItem = worksheet.Cells[row, 10].Value.ToString(); var shortextPo = worksheet.Cells[row, 11].Value.ToString(); var project = worksheet.Cells[row, 12].Value.ToString(); var valueAssigment = Decimal.Parse(worksheet.Cells[row, 13].Value.ToString()); var top = worksheet.Cells[row, 14].Value.ToString(); var assignmentCreateBy = worksheet.Cells[row, 15].Value.ToString(); var assignmentCreateDate = DateTime.Parse(worksheet.Cells[row, 16].Text); var asp = worksheet.Cells[row, 17].Value.ToString(); var assignmentCancel = Boolean.Parse(worksheet.Cells[row, 18].Value.ToString()); var sow = worksheet.Cells[row, 19].Value.ToString(); ASP aspFind = _asp.GetAll().FirstOrDefault(x => _excel.TruncateString(x.Name) == _excel.TruncateString(asp)); Assignment AssignmentFind = Service .GetAll() .FirstOrDefault(x => _excel.TruncateString(x.AssignmentId.ToString()) == _excel.TruncateString(assigmentId)); if (AssignmentFind == null) { Assignment nt = new Assignment { AssignmentId = assigmentId, ProjectName = project, SiteID = siteId, SiteName = siteName, AssignmentAcceptedDate = assigmentAcceptDate, PRNo = prNumber, PRDateCreated = prDate, PONumber = poNumber, PODate = poDate, LineItemPO = poLineItem, AssignmentReady4Bast = true, ShortTextPO = shortextPo, SHID = shId, ValueAssignment = valueAssigment, TOP = top, AssignmentCreateBy = assignmentCreateBy, AssignmentCreateDate = assignmentCreateDate, AspId = aspFind.Id, AssignmentCancel = assignmentCancel, Sow = sow }; Service.Add(nt); //TempData["Messages"] = "Success add Assignment Id " + assigmentId; Console.WriteLine("Success add Assignment Id " + assigmentId); TOTAL_INSERT++; } else if (AssignmentFind != null && assignmentCancel != AssignmentFind.AssignmentCancel) { Assignment nt = Service.GetById(AssignmentFind.Id); nt.ProjectName = project; nt.SiteID = siteId; nt.SiteName = siteName; nt.AssignmentAcceptedDate = assigmentAcceptDate; nt.PRNo = prNumber; nt.PRDateCreated = prDate; nt.PONumber = poNumber; nt.PODate = poDate; nt.LineItemPO = poLineItem; nt.ShortTextPO = shortextPo; nt.SHID = shId; nt.ValueAssignment = valueAssigment; nt.TOP = top; nt.AssignmentCreateBy = assignmentCreateBy; nt.AssignmentCreateDate = assignmentCreateDate; nt.AspId = AssignmentFind.AspId; nt.AssignmentCancel = assignmentCancel; nt.AssignmentReady4Bast = true; nt.Sow = sow; nt.OtherInfo = shortextPo; Service.Update(nt); //TempData["Messages"] = "Success update Assignment Id " + assigmentId; Console.WriteLine("Success update Assignment Id " + assigmentId); TOTAL_UPDATE++; } } } } } TempData["Messages"] = "Total Inserted = " + TOTAL_INSERT + " , Total Updated = " + TOTAL_UPDATE; } catch (Exception ex) { Console.WriteLine(ex.ToString()); TempData["Messages"] = ex.ToString(); } } System.IO.File.Delete(System.IO.Path.Combine(uploads, file.FileName)); return(RedirectToAction("Index")); }