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();
}
}
