public ActionResult UploadIndex(ExcelRatingsViewModel RatingModel) { RatingsViewModel rating = new RatingsViewModel(); rating = Merge(RatingModel); TempData["SearchedResults"] = rating; var rateSuppliers = rating.RatingRecords.Select(r => new SelectListItem { Text = r.SupplierName + " CID:" + r.CID, Value = r.CID.ToString() }).ToList(); var modifiedlist = selectSuppliers.Select(r => new SelectListItem { Text = r.Text + " CID:" + r.Value, Value = r.Value }).ToList(); ViewBag.RatingSuppliers = rateSuppliers; var NotinListSuppliers = (from fulllist in modifiedlist where !(rateSuppliers.Any(i => i.Value == fulllist.Value)) select fulllist).ToList(); if (NotinListSuppliers != null) { ViewBag.Suppliers = NotinListSuppliers; } else { ViewBag.Suppliers = modifiedlist; } // ViewBag.Suppliers = modifiedlist; return(View("UploadIndex", rating)); }
public ActionResult LoadUploadIndex(int SiteID, int Year, int Month) { ExcelRatingsViewModel ratingsViewModel = new ExcelRatingsViewModel { SiteID = SiteID, isUpload = true }; ratingsViewModel.Month = Month; ratingsViewModel.Year = Year; ratingsViewModel.ShowResult = true; ratingsViewModel.EditMode = false; CreateListViewBags(); return(View("Index", ratingsViewModel)); }
private static List <RatingRecord> ProdRecords(ExcelRatingsViewModel ratingModel, int CheckingDate, Repository Rep) { return((from ratings in Rep.Context.SPFS_SUPPLIER_RATINGS where ratings.SiteID == ratingModel.SiteID && ratings.Rating_period == CheckingDate select new RatingRecord { CID = ratings.CID, SiteID = ratings.SiteID, Inbound_parts = ratings.Inbound_parts, OTR = ratings.OTR, OTD = ratings.OTD, PFR = ratings.PFR }).ToList()); }
//private List<SupplierCacheViewModel> GetSupplierListData() //{ // List<SupplierCacheViewModel> result = new List<SupplierCacheViewModel>(); // using (Repository repository = new Repository()) // { // var MultipleLeftJoin = from sup in repository.Context.SPFS_SUPPLIERS // select new SupplierCacheViewModel // { // CID = sup.CID, // Duns = sup.Duns, //.Replace("\0", "").Trim(), // Name =sup.Name // }; // result = MultipleLeftJoin.ToList(); // } // result.ForEach(z => z.Duns = z.Duns.Replace("\0", "").Trim()); // return result; //} //GET: ExcelUpload public ActionResult Index(int?SiteID, bool isUpload = true) { ExcelRatingsViewModel ratingsViewModel = new ExcelRatingsViewModel { SiteID = SiteID, isUpload = isUpload }; ratingsViewModel.Month = DateTime.Now.Month - 1; ratingsViewModel.Year = DateTime.Now.Year; ratingsViewModel.ShowResult = false; ratingsViewModel.EditMode = true; CreateListViewBags(); return(View(ratingsViewModel)); }
public ActionResult ClearStaging(int SiteID, int Year, int Month) { ExcelRatingsViewModel ratingModel = new ExcelRatingsViewModel() { SiteID = SiteID, Year = Year, Month = Month }; int CheckingDate = Convert.ToInt32("" + ratingModel.Year + ratingModel.Month.ToString().PadLeft(2, '0')); using (Repository Rep = new Repository()) { Rep.Context.SPFS_STAGING_SUPPLIER_RATINGS.RemoveRange(Rep.Context.SPFS_STAGING_SUPPLIER_RATINGS.Where(s => s.SiteID == ratingModel.SiteID && s.Rating_period == CheckingDate)); Rep.Context.SaveChanges(); } CreateListViewBags(); ratingModel.ShowResult = true; ratingModel.EditMode = true; TempData["SearchedResults"] = ratingModel; return(View("Index", ratingModel)); }
private List <RatingRecord> CheckInboundRatings(List <RatingRecord> ratngs, ExcelRatingsViewModel ratingsModel) { List <RatingRecord> ratings = new List <RatingRecord>(); foreach (var item in ratngs) { if (item.Inbound_parts > 0) { SelectSiteGDIS gdis = selectGDIS.Where(g => g.SiteID.Equals(ratingsModel.SiteID)).FirstOrDefault(); //RatingRecord ratingRecord = new RatingRecord(); // ratingRecord.CID = int.TryParse(item.CID,) item.Gdis_org_entity_ID = gdis.Gdis_org_entity_ID; item.Gdis_org_Parent_ID = gdis.Gdis_org_Parent_ID; ratings.Add(item); } } return(ratings); }
private ExcelRatingsViewModel AggregateRecords(ExcelRatingsViewModel RatingModel, List <RatingRecord> Records) { List <RatingRecord> GroupedRecords = Records .GroupBy(r => r.CID)// new { r.CID,r.DUNS }) .Select(g => new RatingRecord { CID = g.Key, DUNS = g.First().DUNS, SupplierName = selectSuppliers.Where(s => s.Value == g.Key.ToString()).First().Text, Gdis_org_entity_ID = g.First().Gdis_org_entity_ID, Inbound_parts = g.Sum(s => s.Inbound_parts), Reject_incident_count = g.Sum(s => s.Reject_incident_count), Reject_parts_count = g.Sum(s => s.Reject_parts_count), OTD = g.Sum(s => s.OTD), OTR = g.Sum(s => s.OTR), PFR = g.Sum(s => s.PFR), ErrorInformation = g.SelectMany((s => s.ErrorInformation != null ? s.ErrorInformation : new List <ErrorDetails>())).ToList(), Temp_Upload_ = g.First().Temp_Upload_ }).ToList(); RatingModel.RatingRecords = GroupedRecords; return(RatingModel); }
public List <RatingRecord> IncidentSpendOrder(ExcelRatingsViewModel RatingModel) { List <RatingRecord> recordsChild = new List <RatingRecord>(); List <RatingRecord> recordsParent = new List <RatingRecord>(); List <RatingRecord> Mergedrecords = new List <RatingRecord>(); List <RatingRecord> Sortedrecords = new List <RatingRecord>(); using (Repository Rep = new Repository()) { recordsChild = (from site in Rep.Context.SPFS_SITES join spend in Rep.Context.SPFS_SPEND_SUPPLIERS on site.SiteID equals spend.SiteID join sup in Rep.Context.SPFS_SUPPLIERS on spend.CID equals sup.CID where spend.SiteID == RatingModel.SiteID select new RatingRecord { CID = spend.CID, SiteID = spend.SiteID, Gdis_org_entity_ID = site.Gdis_org_entity_ID, Gdis_org_Parent_ID = site.Gdis_org_Parent_ID, Reject_incident_count = spend.Reject_incident_count, Reject_parts_count = spend.Reject_parts_count, SupplierName = sup.Name, DUNS = sup.Duns }).ToList(); var parentID = recordsChild.Max(p => p.Gdis_org_Parent_ID); recordsParent = (from spend in Rep.Context.SPFS_SPEND_SUPPLIERS where spend.Gdis_org_Parent_ID == parentID group spend by new { spend.CID, spend.Gdis_org_Parent_ID } into g select new RatingRecord { CID = g.Key.CID, Gdis_org_Parent_ID = g.Key.Gdis_org_Parent_ID, Total_Spend = g.Sum(x => x.Total_Spend) }).ToList(); } Mergedrecords = (from child in recordsChild join parent in recordsParent on new { child.CID, child.Gdis_org_Parent_ID } equals new { parent.CID, parent.Gdis_org_Parent_ID } into merged from m in merged.DefaultIfEmpty() select new RatingRecord { CID = child.CID, DUNS = child.DUNS, SiteID = child.SiteID, Gdis_org_entity_ID = child.Gdis_org_entity_ID, Gdis_org_Parent_ID = child.Gdis_org_Parent_ID, Reject_incident_count = child.Reject_incident_count, Reject_parts_count = child.Reject_parts_count, Total_Spend = m == null ? 0 : m.Total_Spend, SupplierName = child.SupplierName }).ToList(); Mergedrecords.ForEach(z => z.DUNS = z.DUNS.Replace("\0", "").Trim()); Sortedrecords = Mergedrecords.OrderByDescending(x => x.Reject_incident_count).ThenByDescending(x => x.Total_Spend).ToList(); return(Sortedrecords); }
private RatingsViewModel Merge(ExcelRatingsViewModel RatingModel) { List <RatingRecord> Records = (List <RatingRecord>)TempData["RatingRecords"]; //TempData.Keep("RatingRecords"); ExcelRatingsViewModel AggregatedModel = AggregateRecords(RatingModel, Records); RatingsViewModel ConvertedModel = new RatingsViewModel(); List <RatingRecord> ISORecords = IncidentSpendOrder(RatingModel); //List<RatingRecord> HistoryRecords = IncidentSpendOrder(RatingModel); List <RatingRecord> MergedRecords = new List <RatingRecord>(); List <RatingRecord> UnMatchedRecords = new List <RatingRecord>(); var query = from x in ISORecords join y in AggregatedModel.RatingRecords on x.CID equals y.CID select new { x, y }; foreach (var match in query) { match.x.Inbound_parts = match.y.Inbound_parts; match.x.OTD = match.y.OTD; match.x.OTR = match.y.OTR; match.x.PFR = match.y.PFR; match.x.Reject_incident_count = match.y.Reject_incident_count; match.x.Reject_parts_count = match.y.Reject_parts_count; match.x.Temp_Upload_ = match.y.Temp_Upload_; match.x.ErrorInformation = match.y.ErrorInformation; } // MergedRecords = ISORecords; var unmatch = (from agrr in AggregatedModel.RatingRecords where !(ISORecords.Any(i => i.CID == agrr.CID)) select agrr).ToList(); if (unmatch != null) { ISORecords.AddRange(unmatch); } MergedRecords = ISORecords; foreach (RatingRecord rec in MergedRecords) { if (rec.Inbound_parts != 0) { double ppm = (double)rec.Reject_parts_count / rec.Inbound_parts; rec.PPM = Math.Round(ppm * 1000000); double ipm = (double)rec.Reject_incident_count / rec.Inbound_parts; rec.IPM = Math.Round((ipm * 1000000), 2); } else { rec.PPM = 0; rec.IPM = 0.00; } if (rec.OTD != 0) { double pct = (double)rec.OTR / rec.OTD; rec.PCT = Math.Round(pct * 100); } else { rec.PCT = 0; } } ConvertedModel.RatingRecords = MergedRecords; ConvertedModel.isUpload = true; ConvertedModel.Month = RatingModel.Month; ConvertedModel.Year = RatingModel.Year; ConvertedModel.SiteID = RatingModel.SiteID; SelectSiteGDIS gdis = selectGDIS.Where(g => g.SiteID.Equals(RatingModel.SiteID)).FirstOrDefault(); ConvertedModel.SiteName = gdis.Name; //var count = 0; //foreach (var record in GroupedRecords) //{ // if ((record.ErrorInformation != null ? record.ErrorInformation.Count : 0) > 0) // { // count++; // } //} return(ConvertedModel); }
public ActionResult Search(ExcelRatingsViewModel ratingModel) { DateTime date = new DateTime(ratingModel.Year, ratingModel.Month, 01); DateTime current = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 01); if (current.AddMonths(-4) < date) { int CheckingDate = Convert.ToInt32("" + ratingModel.Year + ratingModel.Month.ToString().PadLeft(2, '0')); List <RatingRecord> StagingRecords = new List <RatingRecord>(); List <RatingRecord> CurrentRecords = new List <RatingRecord>(); List <RatingRecord> PreviousMonthRecords = new List <RatingRecord>(); List <RatingRecord> PreviousMonthRecordsStaging = new List <RatingRecord>(); using (Repository Rep = new Repository()) { CurrentRecords = ProdRecords(ratingModel, CheckingDate, Rep); int CurrentCount = CurrentRecords != null ? CurrentRecords.Count : 0; if (CurrentCount <= 0) { StagingRecords = StageRecords(ratingModel, CheckingDate, Rep); if (StagingRecords.Count > 0) { ratingModel.IsStagingRatings = true; ratingModel.IsAlert = true; ViewBag.alertmsg = "Data exists for this rating period that has <strong>Not</strong> been <strong>Submitted</strong> Do you wish to <strong>Upload</strong> again"; //There are existing records submitted for this month //display data from staging CreateListViewBags(); ratingModel.ShowResult = false; ratingModel.EditMode = false; TempData["SearchedResults"] = ratingModel; return(View("Index", ratingModel)); } else { if (current.AddMonths(-1) <= date) { int CheckingDate_Previous = Convert.ToInt32("" + date.Year + (date.Month - 1).ToString().PadLeft(2, '0')); PreviousMonthRecords = ProdRecords(ratingModel, CheckingDate_Previous, Rep); if (PreviousMonthRecords.Count > 0) { //Allow Upload ratingModel.IsPreviousRatings = true; ratingModel.ShowResult = true; ratingModel.EditMode = true; ViewBag.divmsg = "Last month data check was succesfull"; CreateListViewBags(); TempData["SearchedResults"] = ratingModel; return(View("Index", ratingModel)); } else { PreviousMonthRecordsStaging = StageRecords(ratingModel, CheckingDate_Previous, Rep); if (PreviousMonthRecordsStaging.Count > 0) { //you havent submitted last months data. would you like to finish //Yes - Load last months data //No - Continue with current ratings ratingModel.IsAlert = true; ratingModel.IsPreviousStagingRatings = true; ViewBag.alertmsg = "Data exists for last ratings period which has <strong>Not</strong> been <strong>Submitted!</strong>"; CreateListViewBags(); ratingModel.ShowResult = false; ratingModel.EditMode = false; TempData["SearchedResults"] = ratingModel; return(View("Index", ratingModel)); } else { //Allow Upload ratingModel.ShowResult = true; ratingModel.EditMode = true; CreateListViewBags(); TempData["SearchedResults"] = ratingModel; return(View("Index", ratingModel)); } } } else { //Allow Upload ratingModel.ShowResult = true; ratingModel.EditMode = true; ViewBag.divmsg = "This is not current period"; CreateListViewBags(); TempData["SearchedResults"] = ratingModel; return(View("Index", ratingModel)); } } } else { //data exists for you and any changes will overwrite existing data. Press clear to stop editing submittedratings ratingModel.IsAlert = true; ratingModel.IsCurrentRatings = true; ViewBag.alertmsg = "Data exists and has been <strong>Submitted</strong> for this rating period. Do you wish to <strong>Upload</strong> again and any overlay previously <strong>Submitted</strong> ratings?"; TempData["SearchedResults"] = ratingModel; ratingModel.ShowResult = false; ratingModel.EditMode = false; CreateListViewBags(); return(View("Index", ratingModel)); } } } else { ratingModel.ShowResult = false; ratingModel.EditMode = false; TempData["SearchedResults"] = ratingModel; CreateListViewBags(); return(View("Index", ratingModel)); } }
private ExcelRatingsViewModel ProcessExcelDataintoViewModel(ExcelRatingsViewModel ratingModel, DataSet result) { List <RatingRecord> ratings = result.Tables[0].ToList <RatingRecord>(); List <RatingRecord> Inboundratings = CheckInboundRatings(ratings, ratingModel); List <RatingRecord> PrimaryKeyratings = new List <RatingRecord>(); foreach (var item in Inboundratings) { if (!string.IsNullOrEmpty(item.DUNS)) { item.DUNS = item.DUNS.PadLeft(9, '0'); } List <ErrorDetails> ErrorInfo = new List <ErrorDetails>(); bool iRecordfound = false; if (supplierCacheObj.Any(m => m.ERPSupplierID == item.ERP_Supplier_ID && m.Gdis_org_entity_ID.Equals(item.Gdis_org_entity_ID))) //supplierCacheObj.Any(m => m.CID.Equals(item.CID)) { if (supplierCacheObj.Any(m => m.CID.Equals(item.CID))) { iRecordfound = true; if (string.IsNullOrWhiteSpace(item.DUNS)) { item.DUNS = GetDUNSfromCID(item.CID); } } else if (supplierCacheObj.Any(m => m.Duns.Equals(item.DUNS))) { item.CID = GetCIDfromDuns(item.DUNS); if (item.CID == 0) { iRecordfound = false; GetErrors(item, ErrorInfo); } } else { iRecordfound = false; GetErrors(item, ErrorInfo); } } else if (supplierCacheObj.Any(m => m.CID.Equals(item.CID))) { iRecordfound = true; if (string.IsNullOrWhiteSpace(item.DUNS)) { item.DUNS = GetDUNSfromCID(item.CID); } } else if (supplierCacheObj.Any(m => m.Duns.Equals(item.DUNS))) { iRecordfound = true; item.CID = GetCIDfromDuns(item.DUNS); if (item.CID == 0) { iRecordfound = false; GetErrors(item, ErrorInfo); } } else { iRecordfound = false; GetErrors(item, ErrorInfo); } PrimaryKeyratings.Add(item); } //ratingModel.RatingRecords = PrimaryKeyratings; ratingModel.RatingRecords = PrimaryKeyratings.OrderByDescending(o => o.ErrorInformation != null ? o.ErrorInformation.Count : 0).ToList(); TempData["RatingRecords"] = ratingModel.RatingRecords; return(ratingModel); }
public ActionResult Upload(ExcelRatingsViewModel ratingModel) { if (ModelState.IsValid) { if (ratingModel.UploadFile != null && ratingModel.UploadFile.ContentLength > 0) { // ExcelDataReader works with the binary Excel file, so it needs a FileStream // to get started. This is how we avoid dependencies on ACE or Interop: Stream stream = ratingModel.UploadFile.InputStream; DataSet result = null; if (ratingModel.UploadFile.FileName.EndsWith(".xls")) { IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(stream); reader.IsFirstRowAsColumnNames = true; result = reader.AsDataSet(); reader.Close(); } else if (ratingModel.UploadFile.FileName.EndsWith(".xlsx")) { IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream); reader.IsFirstRowAsColumnNames = true; result = reader.AsDataSet(); reader.Close(); } else { ModelState.AddModelError("File", "This file format is not supported"); return(View()); } ratingModel = ProcessExcelDataintoViewModel(ratingModel, result); ViewBag.Suppliers = selectSuppliers; var count = 0; if (ratingModel.RatingRecords.Count > 0) { foreach (var record in ratingModel.RatingRecords) { if ((record.ErrorInformation != null ? record.ErrorInformation.Count : 0) > 1) { count++; } } if (count > 0) { ViewBag.Count = count; //ViewBag.ShowMerge = false; } else { ViewBag.Count = count; // ViewBag.ShowMerge = true; } } else { ModelState.AddModelError("UploadFile", "Please upload Valid File"); CreateListViewBags(); return(View("Index", ratingModel)); } } else { ModelState.AddModelError("UploadFile", "Please Upload Your file"); CreateListViewBags(); return(View("Index", ratingModel)); } } return(View("ExcelReview", ratingModel)); }