static private void CopyToInstance(PerformContext performContext, DbAppContext dbContext, HETSAPI.Import.HETS_Region oldObject, ref Models.Region reg, string systemId)
{
bool isNew = false;
if (reg == null)
{
isNew = true;
reg = new Models.Region();
}
if (dbContext.Regions.Where(x => x.Name.ToUpper() == oldObject.Name.ToUpper()).Count() == 0)
{
isNew = true;
reg.Name = oldObject.Name.Trim();
reg.Id = oldObject.Region_Id; // dbContext.Regions.Max(x => x.Id) + 1;
reg.MinistryRegionID = oldObject.Ministry_Region_Id;
reg.RegionNumber = oldObject.Region_Number;
reg.CreateTimestamp = DateTime.UtcNow;
reg.CreateUserid = systemId;
}
if (isNew)
{
dbContext.Regions.Add(reg); //Adding the city to the database table of HET_CITY
}
try
{
dbContext.SaveChangesForImport();
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR With add or update Region ***");
performContext.WriteLine(e.ToString());
}
}
public void KazakhstanRegionsEditByYear(int Year)
{
Models.Region kz = _context.Region.AsNoTracking().FirstOrDefault(r => r.Year == Year && string.IsNullOrEmpty(r.Coordinates) && r.Code == Startup.Configuration["KazakhstanCode"]);
if (kz != null)
{
kz.Area = _context.Region.AsNoTracking().Where(r => r.Year == Year && !string.IsNullOrEmpty(r.Coordinates) && r.Code != Startup.Configuration["KazakhstanCode"]).Sum(r => r.Area);
kz.Population = _context.Region.AsNoTracking().Where(r => r.Year == Year && !string.IsNullOrEmpty(r.Coordinates) && r.Code != Startup.Configuration["KazakhstanCode"]).Sum(r => r.Population);
_context.Update(kz);
}
_context.SaveChanges();
}
public override Models.IEntity Create(DbDataReader rdr)
{
var r = new Models.Region();
//r.Id = rdr.GetInt32(rdr.GetBytes(rdr.GetOrdinal("regionId")));
r.Id = Convert.ToInt32(rdr["regionId"]); // source could be byte, int, or something else
r.Name = rdr["name"].ToString();
r.RegionType = rdr["regionType"].ToString();
object value = this.GetValueOrNull(rdr, "OrderNumber");
if (value != null)
{
r.OrderNumber = (int)Convert.ToInt32(value);
}
return(r);
}
public List <SelectListItem> LoadRegionByRegionId(int?regionId)
{
List <SelectListItem> ObjList = new List <SelectListItem>()
{
new SelectListItem {
Text = "--Select region--", Value = "0"
},
};
Models.Region data = service.loadRegionsByRegionId(regionId);
if (data != null)
{
ObjList.Add(new SelectListItem
{
Text = data.Name,
Value = data.RegionId.ToString()
});
}
return(ObjList);
}
public void KazakhstanRegionsCreateByYear(int Year)
{
int kzcount = _context.Region.AsNoTracking().Count(r => r.Year == Year && string.IsNullOrEmpty(r.Coordinates) && r.Code == Startup.Configuration["KazakhstanCode"]),
regionscount = _context.Region.AsNoTracking().Count(r => r.Year == Year && !string.IsNullOrEmpty(r.Coordinates) && r.Code != Startup.Configuration["KazakhstanCode"]);
if (kzcount == 0 && regionscount > 0)
{
Models.Region kz = new Models.Region()
{
Area = _context.Region.AsNoTracking().Where(r => r.Year == Year && !string.IsNullOrEmpty(r.Coordinates) && r.Code != Startup.Configuration["KazakhstanCode"]).Sum(r => r.Area),
Code = Startup.Configuration["KazakhstanCode"],
NameEN = _sharedLocalizer.WithCulture(new CultureInfo("en"))["Kazakhstan"],
NameKK = _sharedLocalizer.WithCulture(new CultureInfo("kk"))["Kazakhstan"],
NameRU = _sharedLocalizer.WithCulture(new CultureInfo("ru"))["Kazakhstan"],
Population = _context.Region.AsNoTracking().Where(r => r.Year == Year && !string.IsNullOrEmpty(r.Coordinates) && r.Code != Startup.Configuration["KazakhstanCode"]).Sum(r => r.Population),
Year = Year
};
_context.Add(kz);
}
_context.SaveChanges();
}
public IActionResult Update([FromBody] Models.Region region)
{
_unitOfWork.RegionRepository.Update(region);
_unitOfWork.Complete();
return(new JsonResult(region));
}
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();
}
}
/// <summary>
/// Import Regions
/// </summary>
/// <param name="performContext"></param>
/// <param name="dbContext"></param>
/// <param name="fileLocation"></param>
/// <param name="systemId"></param>
static public void Import(PerformContext performContext, DbAppContext dbContext, string fileLocation, string systemId)
{
string completed = DateTime.Now.ToString("d") + "-" + "Completed";
ImportMap importMap = dbContext.ImportMaps.FirstOrDefault(x => x.OldTable == oldTable && x.OldKey == completed && x.NewKey == sigId);
if (importMap != null)
{
performContext.WriteLine("*** Importing " + xmlFileName + " is complete from the former process ***");
return;
}
try
{
string rootAttr = "ArrayOf" + oldTable;
performContext.WriteLine("Processing " + oldTable);
var progress = performContext.WriteProgressBar();
progress.SetValue(0);
// create serializer and serialize xml file
XmlSerializer ser = new XmlSerializer(typeof(HETS_Region[]), new XmlRootAttribute(rootAttr));
MemoryStream memoryStream = ImportUtility.memoryStreamGenerator(xmlFileName, oldTable, fileLocation, rootAttr);
HETSAPI.Import.HETS_Region[] legacyItems = (HETSAPI.Import.HETS_Region[])ser.Deserialize(memoryStream);
foreach (var item in legacyItems.WithProgress(progress))
{
// see if we have this one already.
Models.Region reg = null;
importMap = dbContext.ImportMaps.FirstOrDefault(x => x.OldTable == oldTable && x.OldKey == item.Region_Id.ToString());
if (dbContext.LocalAreas.Where(x => x.Name.ToUpper() == item.Name.Trim().ToUpper()).Count() > 0)
{
reg = dbContext.Regions.FirstOrDefault(x => x.Name.ToUpper() == item.Name.Trim().ToUpper());
}
if (importMap == null && reg == null) // new entry
{
CopyToInstance(performContext, dbContext, item, ref reg, systemId);
ImportUtility.AddImportMap(dbContext, oldTable, item.Region_Id.ToString(), newTable, reg.Id);
}
else // update
{
if (reg == null) // record was deleted
{
CopyToInstance(performContext, dbContext, item, ref reg, systemId);
// update the import map.
importMap.NewKey = reg.Id;
dbContext.ImportMaps.Update(importMap);
dbContext.SaveChangesForImport();
}
else // ordinary update.
{
CopyToInstance(performContext, dbContext, item, ref reg, systemId);
// touch the import map.
importMap.LastUpdateTimestamp = DateTime.UtcNow;
dbContext.ImportMaps.Update(importMap);
int iResult = dbContext.SaveChangesForImport();
}
}
}
performContext.WriteLine("*** Importing " + xmlFileName + " is Done ***");
ImportUtility.AddImportMap(dbContext, oldTable, completed, newTable, sigId);
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR ***");
performContext.WriteLine(e.ToString());
}
}
