public static ParcelUpdateExpectedResultsDto AddFromFeatureCollection(RioDbContext _dbContext, FeatureCollection featureCollection, string validParcelNumberRegexPattern, string validParcelNumberAsStringForDisplay, WaterYearDto yearChangesToTakeEffect)
{
var commonColumnMappings = ParcelLayerGDBCommonMappingToParcelStagingColumn.GetCommonMappings(_dbContext);
var wktWriter = new WKTWriter();
//Create a datatable to use for bulk copy
var dt = new DataTable();
dt.Columns.Add("ParcelGeometryText", typeof(string));
dt.Columns.Add("ParcelGeometry4326Text", typeof(string));
dt.Columns.Add("OwnerName", typeof(string));
dt.Columns.Add("ParcelNumber", typeof(string));
dt.Columns.Add("HasConflict", typeof(bool));
foreach (var feature in featureCollection)
{
var parcelNumber = feature.Attributes[commonColumnMappings.ParcelNumber].ToString();
if (!Parcel.IsValidParcelNumber(validParcelNumberRegexPattern, parcelNumber))
{
throw new ValidationException(
$"Parcel number found that does not comply to format {validParcelNumberAsStringForDisplay}. Please ensure that that correct column is selected and all Parcel Numbers follow the specified format and try again.");
}
//if it's an exact duplicate, there was some other info that wasn't relevant for our purposes and we can move along
var geomAs4326Text = wktWriter.Write(feature.Geometry.ProjectTo4326());
var ownerName = feature.Attributes[commonColumnMappings.OwnerName].ToString();
if (dt.AsEnumerable().Any(x =>
x["ParcelGeometry4326Text"].ToString() == geomAs4326Text && x["OwnerName"].ToString() == ownerName &&
x["ParcelNumber"].ToString() == parcelNumber))
{
continue;
}
dt.Rows.Add(
wktWriter.Write(feature.Geometry),
geomAs4326Text,
ownerName,
parcelNumber,
false
);
}
var duplicates = dt.AsEnumerable().GroupBy(x => x[3]).Where(y => y.Count() > 1).ToList();
if (duplicates.Any())
{
duplicates.ForEach(x =>
{
var effectedRows = dt.Select("ParcelNumber='" + x.Key + "'");
var firstEffected = effectedRows.First();
if (effectedRows.Any(y => y["ParcelGeometry4326Text"].ToString() != firstEffected["ParcelGeometry4326Text"].ToString()))
{
throw new ValidationException(
$"Parcel number that has more than one geometry associated with it. Please ensure all Parcels have unique geometries and try uploading again.");
}
foreach (var row in effectedRows)
{
row["HasConflict"] = true;
}
});
}
//Make sure staging table is empty before proceeding
DeleteAll(_dbContext);
var rioDbConnectionString = _dbContext.Database.GetDbConnection().ConnectionString;
using var destinationConnection = new SqlConnection(rioDbConnectionString);
destinationConnection.Open();
using var bulkCopy = new SqlBulkCopy(destinationConnection)
{
DestinationTableName = "dbo.ParcelUpdateStaging", EnableStreaming = true
};
//Specify columns so bulk copy knows exactly what it needs to do
bulkCopy.ColumnMappings.Add("ParcelGeometryText", "ParcelGeometryText");
bulkCopy.ColumnMappings.Add("ParcelGeometry4326Text", "ParcelGeometry4326Text");
bulkCopy.ColumnMappings.Add("OwnerName", "OwnerName");
bulkCopy.ColumnMappings.Add("ParcelNumber", "ParcelNumber");
bulkCopy.ColumnMappings.Add("HasConflict", "HasConflict");
bulkCopy.WriteToServer(dt);
_dbContext.Database.ExecuteSqlRaw("EXECUTE dbo.pUpdateParcelUpdateStagingGeometryFromParcelGeometryText");
return(GetExpectedResultsDto(_dbContext, yearChangesToTakeEffect));
}
public static ParcelUpdateExpectedResultsDto GetExpectedResultsDto(RioDbContext _dbContext, WaterYearDto yearChangesToTakeEffect)
{
var accountsUpdatedView = _dbContext.vParcelLayerUpdateDifferencesInParcelsAssociatedWithAccount.Where(x =>
!x.WaterYearID.HasValue || x.WaterYearID == yearChangesToTakeEffect.WaterYearID);
var accountsUnchanged =
accountsUpdatedView.Count(x =>
x.ExistingParcels.Equals(x.UpdatedParcels));
var accountsToBeInactivated = accountsUpdatedView.Count(x => x.AccountAlreadyExists.HasValue &&
(x.AccountAlreadyExists.Value &&
!string.IsNullOrWhiteSpace(x.ExistingParcels) && string.IsNullOrWhiteSpace(x.UpdatedParcels)) || (!x.AccountAlreadyExists.Value && string.IsNullOrWhiteSpace(x.UpdatedParcels)));
var accountsToBeAdded = accountsUpdatedView.Count(x =>
!x.AccountAlreadyExists.Value);
var parcelsUpdatedView = _dbContext.vParcelLayerUpdateDifferencesInAccountAssociatedWithParcelAndParcelGeometry.Where(x =>
!x.WaterYearID.HasValue || x.WaterYearID == yearChangesToTakeEffect.WaterYearID);
var parcelsUnchanged =
parcelsUpdatedView
.Count(x => x.NewOwnerName.Equals(x.OldOwnerName) && x.NewGeometryText.Equals(x.OldGeometryText) && x.HasConflict.HasValue && !x.HasConflict.Value);
var parcelsWithChangedGeometries = parcelsUpdatedView
.Where(x =>
!string.IsNullOrWhiteSpace(x.OldGeometryText) && !string.IsNullOrWhiteSpace(x.NewGeometryText) && !x.NewGeometryText.Equals(x.OldGeometryText)).Select(x => x.ParcelNumber).Distinct().Count();
var parcelsAssociatedWithNewAccount = parcelsUpdatedView.Count(x =>
x.HasConflict.HasValue && !x.HasConflict.Value && !x.NewOwnerName.Equals(x.OldOwnerName));
var parcelsToBeInactivated = parcelsUpdatedView.Count(x =>
x.WaterYearID.HasValue && !string.IsNullOrWhiteSpace(x.OldOwnerName) && string.IsNullOrWhiteSpace(x.NewOwnerName));
var numParcelsWithConflicts = _dbContext.ParcelUpdateStaging.Where(x => x.HasConflict).Select(x => x.ParcelNumber).Distinct().Count();
var expectedChanges = new ParcelUpdateExpectedResultsDto()
{
NumAccountsUnchanged = accountsUnchanged,
NumAccountsToBeCreated = accountsToBeAdded,
NumAccountsToBeInactivated = accountsToBeInactivated,
NumParcelsUnchanged = parcelsUnchanged,
NumParcelsUpdatedGeometries = parcelsWithChangedGeometries,
NumParcelsAssociatedWithNewAccount = parcelsAssociatedWithNewAccount,
NumParcelsToBeInactivated = parcelsToBeInactivated,
NumParcelsWithConflicts = numParcelsWithConflicts
};
return(expectedChanges);
}
