public async Task LoadOKPD() { var existingCodes = await _context.OkpdCodes.ToListAsync(); var newCodes = new List <Okpd>(); //var net = new System.Net.WebClient(); var filePath = Path.GetTempPath() + "okpd2.xlsx"; //net.DownloadFile("https://uceb10ae6a8367122c04d5f21eca.dl.dropboxusercontent.com/cd/0/get/BHeWR-csWWDHzlWnwOFkPe0F0tRXalkUscNwdjm5o_TuP4A6uNyXIceTVtCn4VAPOHej6a51p5VRZVia2tNOksAnKyqU_ALsWHv2Fgakyzdl8D0ppNRPMy8N_vS6O9WB8EY/file?_download_id=2408628732885871356774615271356131162785143622949902507230006352&_notify_domain=www.dropbox.com&dl=1", filePath); using var stream = File.Open(filePath, FileMode.Open, FileAccess.Read); using var reader = ExcelReaderFactory.CreateReader(stream); do { while (reader.Read()) { if (!existingCodes.Any(c => c.Code == reader.GetValue(0).ToString())) { var okpdCode = new Okpd { Code = reader.GetValue(0).ToString(), Name = reader.GetValue(1).ToString() }; SetParrent(okpdCode, existingCodes); newCodes.Add(okpdCode); existingCodes.Add(okpdCode); } } } while (reader.NextResult()); await _context.OkpdCodes.AddRangeAsync(newCodes); await _context.SaveChangesAsync(); }
private void SetParrent(Okpd okpdCode, List <Okpd> existingCodes) { existingCodes = existingCodes .OrderBy(o => o.Code).ToList(); if (okpdCode.Code.Length == 2) { return; } if (okpdCode.Code.Length == 4) { okpdCode.Parent = existingCodes .Where(k => k.Code.Length == 2 && k.Code == okpdCode.Code.Substring(0, 2)) .FirstOrDefault(); } if (okpdCode.Code.Length == 5) { okpdCode.Parent = existingCodes .Where(k => k.Code.Length == 4 && k.Code == okpdCode.Code.Substring(0, 4)) .FirstOrDefault(); } if (okpdCode.Code.Length == 7) { okpdCode.Parent = existingCodes .Where(k => k.Code.Length == 5 && k.Code == okpdCode.Code.Substring(0, 5)) .FirstOrDefault(); } if (okpdCode.Code.Length == 8) { okpdCode.Parent = existingCodes .Where(k => k.Code.Length == 7 && k.Code == okpdCode.Code.Substring(0, 7)) .FirstOrDefault(); } if (okpdCode.Code.Length == 12 && okpdCode.Code.EndsWith("0")) { okpdCode.Parent = existingCodes .Where(k => k.Code.Length == 12 && k.Code.Substring(0, 10) == okpdCode.Code.Substring(0, 10) && okpdCode.Code.EndsWith("00")) .FirstOrDefault() ?? existingCodes .Where(k => k.Code.Length == 8 && k.Code == okpdCode.Code.Substring(0, 8)) .FirstOrDefault(); } if (okpdCode.Code.Length == 12 && !okpdCode.Code.EndsWith("0")) { okpdCode.Parent = existingCodes .Where(k => k.Code.Length == 12 && k.Code.Substring(0, 11) == okpdCode.Code.Substring(0, 11) && k.Code.EndsWith("0")) .FirstOrDefault() ?? existingCodes .Where(k => k.Code.Length == 12 && k.Code.Substring(0, 10) == okpdCode.Code.Substring(0, 10) && k.Code.EndsWith("0")) .FirstOrDefault() ?? existingCodes .Where(k => k.Code.Length == 8 && k.Code.Substring(0, 8) == okpdCode.Code.Substring(0, 8)) .FirstOrDefault(); } }