public static void UploadStations(string username, UploadFile data, ApplicationDbContext db)
{
using (var transaction = db.Database.BeginTransaction())
{
var fileName = data.Filename;
fileName = StringGenerators.GenerateRandomString(16) + fileName.Replace(" ", "").ToLower();
var filePath = Path.Combine(Directory.GetCurrentDirectory(), "Documents", "TempFiles", fileName);
var base64Data = data.File.Replace("data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,", "");
var binData = Convert.FromBase64String(base64Data);
using (var stream = new MemoryStream(binData))
{
File.WriteAllBytes(filePath, binData);
}
//gembox things
var workbook = ExcelFile.Load(filePath);
var worksheet = workbook.Worksheets.FirstOrDefault(x => x.Name == "Main");
if (worksheet == null)
{
throw new Exception("Invalid Upload File");
}
var uploadData = new List <UploadModel>();
var cnt = 0;
foreach (var row in worksheet.Rows)
{
cnt++;
if (cnt == 1)
{
continue;
}
if (string.IsNullOrEmpty(row.Cells[0].GetFormattedValue()))
{
throw new Exception($"Please enter a valid code for the record on line {cnt}");
}
if (string.IsNullOrEmpty(row.Cells[1].GetFormattedValue()))
{
throw new Exception($"Please enter a name for the record on line {cnt}");
}
if (string.IsNullOrEmpty(row.Cells[2].GetFormattedValue()))
{
throw new Exception($"Please enter the electoral area code for the record on line {cnt}");
}
if (string.IsNullOrEmpty(row.Cells[3].GetFormattedValue()))
{
throw new Exception($"Please enter a constituency code for the record on line {cnt}");
}
if (string.IsNullOrEmpty(row.Cells[4].GetFormattedValue()))
{
throw new Exception($"Please enter a district code for the record on line {cnt}");
}
if (string.IsNullOrEmpty(row.Cells[5].GetFormattedValue()))
{
throw new Exception($"Please enter a region code for the record on line {cnt}");
}
var ud = new UploadModel
{
Code = row.Cells[0].GetFormattedValue(),
Name = row.Cells[1].GetFormattedValue(),
ElectoralAreaCode = row.Cells[2].GetFormattedValue(),
ConstituencyCode = row.Cells[3].GetFormattedValue(),
DistrictCode = row.Cells[4].GetFormattedValue(),
RegionCode = row.Cells[5].GetFormattedValue()
};
uploadData.Add(ud);
}
foreach (var uData in uploadData)
{
var existing = db.Stations.Where(x => (x.Code == uData.Code || uData.Name == x.Name) && x.ElectoralArea.Code == uData.ElectoralAreaCode && x.ElectoralArea.Constituency.Code == uData.ConstituencyCode && x.ElectoralArea.Constituency.District.Code == uData.DistrictCode && x.ElectoralArea.Constituency.District.Region.Code == uData.RegionCode).Include(x => x.ElectoralArea.Constituency.District.Region).FirstOrDefault();
if (existing != null)
{
if (existing.Code == uData.Code)
{
throw new Exception($"There is already an existing record with this Code - {existing.Code}, \nElectoral Area: {existing.ElectoralArea?.Name}, \nConstituency: {existing.ElectoralArea?.Constituency?.Name}, \nDistrict: {existing.ElectoralArea?.Constituency?.District?.Name} and \nRegion: {existing.ElectoralArea?.Constituency?.District?.Region?.Name}");
}
if (existing.Name == uData.Name)
{
throw new Exception($"There is already an existing record with this Name - {existing.Name}, \nElectoral Area: {existing.ElectoralArea?.Name}, \nConstituency: {existing.ElectoralArea?.Constituency?.Name}, \nDistrict: {existing.ElectoralArea?.Constituency?.District?.Name} and \nRegion: {existing.ElectoralArea?.Constituency?.District?.Region?.Name}");
}
}
var region = db.Regions.FirstOrDefault(x => x.Code.ToLower() == uData.RegionCode.ToLower());
if (region == null)
{
throw new Exception($"There is no region with this region code ({uData.RegionCode}). Please check and verify.");
}
var district = db.Districts.FirstOrDefault(x => x.Code.ToLower() == uData.DistrictCode.ToLower() && x.RegionId == region.Id);
if (district == null)
{
throw new Exception($"There is no district with this district code - ({uData.DistrictCode}) and region code - ({uData.RegionCode}). Please check and verify.");
}
var consti = db.Constituencies.FirstOrDefault(x => x.Code.ToLower() == uData.ConstituencyCode.ToLower() && x.DistrictId == district.Id);
if (consti == null)
{
throw new Exception($"There is no constituency with this constituency code - ({uData.ConstituencyCode}), district code - ({uData.DistrictCode}) and region code - ({uData.RegionCode}). Please check and verify.");
}
var ea = db.ElectoralAreas.FirstOrDefault(x => x.Code.ToLower() == uData.ElectoralAreaCode.ToLower() && x.ConstituencyId == consti.Id);
if (ea == null)
{
throw new Exception($"There is no electoral area with this electoral area code - ({uData.ElectoralAreaCode}), constituency code - ({uData.ConstituencyCode}), district code - ({uData.DistrictCode}) and region code - ({uData.RegionCode}). Please check and verify.");
}
// save the data
var newRec = new Station
{
Code = uData.Code,
Name = uData.Name,
ElectoralAreaId = ea.Id,
CreatedAt = DateTime.Now.ToUniversalTime(),
CreatedBy = username,
ModifiedAt = DateTime.Now.ToUniversalTime(),
ModifiedBy = username
};
db.Stations.Add(newRec);
db.SaveChanges();
//db.Entry<Station>(newRec).State = EntityState.Detached;
}
if (File.Exists(fileName))
{
File.Delete(fileName);
}
transaction.Commit();
}
}
public static void UploadRegions(string username, UploadFile data, ApplicationDbContext db)
{
using (var transaction = db.Database.BeginTransaction())
{
var fileName = data.Filename;
fileName = StringGenerators.GenerateRandomString(16) + fileName.Replace(" ", "").ToLower();
var filePath = Path.Combine(Directory.GetCurrentDirectory(), "Documents", "TempFiles", fileName);
var base64Data = data.File.Replace("data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,", "");
var binData = Convert.FromBase64String(base64Data);
using (var stream = new MemoryStream(binData))
{
File.WriteAllBytes(filePath, binData);
}
//gembox things
var workbook = ExcelFile.Load(filePath);
var worksheet = workbook.Worksheets.FirstOrDefault(x => x.Name == "Main");
if (worksheet == null)
{
throw new Exception("Invalid Upload File");
}
var uploadData = new List <UploadModel>();
var cnt = 0;
foreach (var row in worksheet.Rows)
{
cnt++;
if (cnt == 1)
{
continue;
}
if (string.IsNullOrEmpty(row.Cells[0].GetFormattedValue()))
{
throw new Exception($"Please enter a valid code for the record on line {cnt}");
}
if (string.IsNullOrEmpty(row.Cells[1].GetFormattedValue()))
{
throw new Exception($"Please enter a name for the record on line {cnt}");
}
var ud = new UploadModel
{
Code = row.Cells[0].GetFormattedValue(),
Name = row.Cells[1].GetFormattedValue()
};
uploadData.Add(ud);
}
foreach (var uData in uploadData)
{
var existing = db.Regions.Where(x => x.Code == uData.Code || uData.Name == x.Name).FirstOrDefault();
if (existing != null)
{
if (existing.Code == uData.Code)
{
throw new Exception($"There is already an existing record with this Code - {existing.Code}");
}
if (existing.Name == uData.Name)
{
throw new Exception($"There is already an existing record with this Name - {existing.Name}");
}
}
// save the data
var newRec = new Models.Region
{
Code = uData.Code,
Name = uData.Name,
CreatedAt = DateTime.Now.ToUniversalTime(),
CreatedBy = username,
ModifiedAt = DateTime.Now.ToUniversalTime(),
ModifiedBy = username
};
db.Regions.Add(newRec);
db.SaveChanges();
}
if (File.Exists(fileName))
{
File.Delete(fileName);
}
transaction.Commit();
}
}
