public static IEnumerable <ParcelDto> ListByUserID(RioDbContext dbContext, int userID, int year)
{
var user = dbContext.User.Include(x => x.AccountUser).Single(x => x.UserID == userID);
var accountIDs = user.AccountUser.Select(x => x.AccountID).ToList();
return(ListByAccountIDsAndYear(dbContext, accountIDs, year));
}
//Keep as reference for setting Allocation proportionally across an account and by volume
//public static void BulkSetAllocation(RioDbContext dbContext, List<BulkSetAllocationCSV> records, int waterYear, int parcelAllocationType)
//{
// // delete existing parcel allocations
// var existingParcelAllocations = dbContext.ParcelAllocation.Where(x =>
// x.WaterYear == waterYear && x.ParcelAllocationTypeID == parcelAllocationType);
// if (existingParcelAllocations.Any())
// {
// dbContext.ParcelAllocation.RemoveRange(existingParcelAllocations);
// dbContext.SaveChanges();
// }
// // select parcels owned by accounts from upload and group by accounts to associate allocation volumes with list of parcels
// var accountAllocationVolumes = Parcel.AccountParcelWaterYearOwnershipsByYear(dbContext, waterYear).ToList().GroupBy(x => x.Account.AccountNumber)
// .Where(x => records.Select(y => y.AccountNumber).Contains(x.Key)).Join(records,
// account => account.Key, record => record.AccountNumber,
// (x, y) => new { Parcels = x.Select(z => z.Parcel).ToList(), y.AllocationVolume });
// var parcelAllocations = new List<ParcelAllocation>();
// // apportion the reconciliation volumes to their lists of parcels by area percentage
// foreach (var record in accountAllocationVolumes)
// {
// var parcels = record.Parcels;
// var sum = parcels.Sum(x => x.ParcelAreaInAcres);
// parcelAllocations.AddRange(parcels.Select(x => new ParcelAllocation()
// {
// ParcelID = x.ParcelID,
// AcreFeetAllocated =
// (decimal)(record.AllocationVolume * (x.ParcelAreaInAcres / sum)),
// WaterYear = waterYear,
// ParcelAllocationTypeID = parcelAllocationType
// }));
// }
// dbContext.ParcelAllocation.AddRange(parcelAllocations);
// dbContext.SaveChanges();
//}
public static void BulkSetAllocation(RioDbContext dbContext, List <BulkSetAllocationCSV> records, int waterYear,
int parcelAllocationType)
{
//delete existing parcel allocations
var existingParcelAllocations = dbContext.ParcelAllocation.Where(x =>
x.WaterYear == waterYear && x.ParcelAllocationTypeID == parcelAllocationType);
if (existingParcelAllocations.Any())
{
dbContext.ParcelAllocation.RemoveRange(existingParcelAllocations);
dbContext.SaveChanges();
}
var parcelAllocations = new List <ParcelAllocation>();
foreach (var record in records)
{
var parcel = dbContext.Parcel.First(x => x.ParcelNumber == record.APN);
parcelAllocations.Add(new ParcelAllocation()
{
ParcelID = parcel.ParcelID,
AcreFeetAllocated =
(decimal)(record.AllocationQuantity * parcel.ParcelAreaInAcres),
WaterYear = waterYear,
ParcelAllocationTypeID = parcelAllocationType
});
}
dbContext.ParcelAllocation.AddRange(parcelAllocations);
dbContext.SaveChanges();
}
public static int BulkSetAllocation(RioDbContext dbContext, ParcelAllocationUpsertDto parcelAllocationUpsertDto)
{
// delete existing parcel allocations
var existingParcelAllocations = dbContext.ParcelAllocation.Where(x => x.WaterYear == parcelAllocationUpsertDto.WaterYear && x.ParcelAllocationTypeID == parcelAllocationUpsertDto.ParcelAllocationTypeID);
if (existingParcelAllocations.Any())
{
dbContext.ParcelAllocation.RemoveRange(existingParcelAllocations);
dbContext.SaveChanges();
}
var parcels = dbContext.Parcel.AsNoTracking().OrderBy(x => x.ParcelID).ToList();
foreach (var parcel in parcels)
{
var parcelAllocation = new ParcelAllocation
{
ParcelID = parcel.ParcelID,
WaterYear = parcelAllocationUpsertDto.WaterYear,
ParcelAllocationTypeID = parcelAllocationUpsertDto.ParcelAllocationTypeID,
AcreFeetAllocated = parcelAllocationUpsertDto.AcreFeetAllocated * (decimal)parcel.ParcelAreaInAcres
};
dbContext.ParcelAllocation.Add(parcelAllocation);
}
dbContext.SaveChanges();
return(parcels.Count);
}
public static List <ParcelAllocationDto> Upsert(RioDbContext dbContext, int parcelID, List <ParcelAllocationUpsertDto> parcelAllocationUpsertDtos)
{
// delete existing parcel allocations
var existingParcelAllocations = dbContext.ParcelAllocation.Where(x => x.ParcelID == parcelID);
if (existingParcelAllocations.Any())
{
dbContext.ParcelAllocation.RemoveRange(existingParcelAllocations);
dbContext.SaveChanges();
}
foreach (var parcelAllocationUpsertDto in parcelAllocationUpsertDtos)
{
var parcelAllocation = dbContext.ParcelAllocation
.SingleOrDefault(x => x.ParcelID == parcelID && x.WaterYear == parcelAllocationUpsertDto.WaterYear && x.ParcelAllocationTypeID == parcelAllocationUpsertDto.ParcelAllocationTypeID);
if (parcelAllocation == null)
{
parcelAllocation = new ParcelAllocation
{
ParcelID = parcelID,
WaterYear = parcelAllocationUpsertDto.WaterYear,
ParcelAllocationTypeID = parcelAllocationUpsertDto.ParcelAllocationTypeID
};
dbContext.ParcelAllocation.Add(parcelAllocation);
}
parcelAllocation.AcreFeetAllocated = parcelAllocationUpsertDto.AcreFeetAllocated;
}
dbContext.SaveChanges();
return(ListByParcelID(dbContext, parcelID));
}
public static IEnumerable <WaterTransferRegistrationParcelDto> SaveParcels(RioDbContext dbContext, int waterTransferID, WaterTransferRegistrationDto waterTransferRegistrationDto)
{
// get the registration record
var waterTransferRegistration = dbContext.WaterTransferRegistration.Single(x =>
x.WaterTransferID == waterTransferID && x.WaterTransferTypeID == waterTransferRegistrationDto.WaterTransferTypeID);
// delete existing parcels registered
var existingWaterTransferRegistrationParcels = dbContext.WaterTransferRegistrationParcel.Where(x => x.WaterTransferRegistrationID == waterTransferRegistration.WaterTransferRegistrationID);
if (existingWaterTransferRegistrationParcels.Any())
{
dbContext.WaterTransferRegistrationParcel.RemoveRange(existingWaterTransferRegistrationParcels);
dbContext.SaveChanges();
}
foreach (var waterTransferParcelDto in waterTransferRegistrationDto.WaterTransferRegistrationParcels)
{
var waterTransferRegistrationParcel = new WaterTransferRegistrationParcel
{
WaterTransferRegistrationID = waterTransferRegistration.WaterTransferRegistrationID,
ParcelID = waterTransferParcelDto.ParcelID,
AcreFeetTransferred = waterTransferParcelDto.AcreFeetTransferred
};
dbContext.WaterTransferRegistrationParcel.Add(waterTransferRegistrationParcel);
}
dbContext.SaveChanges();
return(ListByWaterTransferRegistrationID(dbContext, waterTransferRegistration.WaterTransferRegistrationID));
}
public static CustomRichTextDto GetByCustomRichTextTypeID(RioDbContext dbContext, int customRichTextTypeID)
{
var customRichText = dbContext.CustomRichText
.SingleOrDefault(x => x.CustomRichTextTypeID == customRichTextTypeID);
return(customRichText?.AsDto());
}
public static IEnumerable <PostingDetailedDto> ListDetailedByYear(RioDbContext dbContext, int year)
{
var postings = dbContext.vPostingDetailed.Where(x => x.PostingDate.Year == year).OrderByDescending(x => x.PostingDate).ToList()
.Select(posting =>
{
var postingDetailedDto = new PostingDetailedDto()
{
PostingID = posting.PostingID,
PostingDate = posting.PostingDate,
PostingTypeID = posting.PostingTypeID,
PostingTypeDisplayName = posting.PostingTypeDisplayName,
PostingStatusID = posting.PostingStatusID,
PostingStatusDisplayName = posting.PostingStatusDisplayName,
PostedByUserID = posting.PostedByUserID,
PostedByAccountID = posting.PostedByAccountID,
PostedByAccountName = posting.PostedByAccountName,
PostedByFirstName = posting.PostedByFirstName,
PostedByLastName = posting.PostedByLastName,
PostedByEmail = posting.PostedByEmail,
Price = posting.Price,
Quantity = posting.Quantity,
AvailableQuantity = posting.AvailableQuantity,
NumberOfOffers = posting.NumberOfOffers,
};
return(postingDetailedDto);
}).ToList();
return(postings);
}
public static OpenETSyncHistoryDto GetByOpenETSyncHistoryID(RioDbContext dbContext, int openETSyncHistoryID)
{
return(dbContext.OpenETSyncHistory
.Include(x => x.OpenETSyncResultType)
.Include(x => x.WaterYear)
.SingleOrDefault(x => x.OpenETSyncHistoryID == openETSyncHistoryID).AsDto());
}
public static OfferDto CreateNew(RioDbContext dbContext, int postingID, OfferUpsertDto offerUpsertDto)
{
if (!offerUpsertDto.TradeID.HasValue)
{
var trade = Trade.CreateNew(dbContext, postingID, offerUpsertDto.CreateAccountID);
offerUpsertDto.TradeID = trade.TradeID;
}
var offer = new Offer
{
TradeID = offerUpsertDto.TradeID.Value,
OfferNotes = offerUpsertDto.OfferNotes,
CreateAccountID = offerUpsertDto.CreateAccountID,
OfferDate = DateTime.UtcNow,
Price = offerUpsertDto.Price,
Quantity = offerUpsertDto.Quantity,
OfferStatusID = offerUpsertDto.OfferStatusID
};
dbContext.Offer.Add(offer);
dbContext.SaveChanges();
dbContext.Entry(offer).Reload();
return(GetByOfferID(dbContext, offer.OfferID));
}
public static void RemoveAssociatedAccount(RioDbContext dbContext, int userID, int accountID)
{
var currentAccountUser = dbContext.AccountUser.Single(x => x.UserID == userID && x.AccountID == accountID);
dbContext.AccountUser.Remove(currentAccountUser);
dbContext.SaveChanges();
}
public static OfferDto GetMostRecentOfferOfType(RioDbContext dbContext, PostingTypeEnum postingTypeEnum)
{
var offer = dbContext.Offer
.Include(x => x.CreateAccount)
.Include(x => x.OfferStatus)
.Include(x => x.WaterTransfer)
.Include(x => x.Trade)
.ThenInclude(x => x.CreateAccount)
.Include(x => x.Trade)
.ThenInclude(x => x.Posting).ThenInclude(x => x.CreateAccount)
.Include(x => x.Trade)
.ThenInclude(x => x.TradeStatus)
.Include(x => x.Trade)
.ThenInclude(x => x.Posting)
.ThenInclude(x => x.PostingType)
.Include(x => x.Trade)
.ThenInclude(x => x.Posting)
.ThenInclude(x => x.PostingStatus)
.AsNoTracking()
.Where(x => !x.WaterTransfer.Any() && x.OfferStatusID != (int)OfferStatusEnum.Rejected && x.OfferStatusID != (int)OfferStatusEnum.Rescinded &&
(x.Trade.Posting.PostingStatusID == (int)postingTypeEnum &&
x.Trade.Posting.CreateAccountID == x.CreateAccountID) ||
(x.Trade.Posting.PostingStatusID != (int)postingTypeEnum &&
x.Trade.Posting.CreateAccountID != x.CreateAccountID)).OrderByDescending(x => x.OfferDate).FirstOrDefault();
return(offer?.AsDto());
}
public static UserDto UpdateUserEntity(RioDbContext dbContext, int userID, UserUpsertDto userEditDto)
{
if (!userEditDto.RoleID.HasValue)
{
return(null);
}
var user = dbContext.User
.Include(x => x.Role)
.Single(x => x.UserID == userID);
if (user.RoleID != (int)RoleEnum.Admin && userEditDto.RoleID == (int)RoleEnum.Admin)
{
dbContext.AccountUser.RemoveRange(dbContext.AccountUser.Where(x => x.UserID == user.UserID));
}
user.RoleID = userEditDto.RoleID.Value;
user.ReceiveSupportEmails = userEditDto.RoleID.Value == 1 && userEditDto.ReceiveSupportEmails;
user.UpdateDate = DateTime.UtcNow;
dbContext.SaveChanges();
dbContext.Entry(user).Reload();
return(GetByUserID(dbContext, userID));
}
public static UserDto GetByUserGuid(RioDbContext dbContext, Guid userGuid)
{
var user = GetUserImpl(dbContext)
.SingleOrDefault(x => x.UserGuid == userGuid);
return(user?.AsDto());
}
public static IEnumerable <LandownerUsageReportDto> GetByYear(RioDbContext dbContext, int year)
{
var sqlParameter = new SqlParameter("year", year);
var landownerUsageReports = dbContext.LandownerUsageReports.FromSqlRaw($"EXECUTE dbo.pLandownerUsageReport @year", sqlParameter).ToList();
var landownerUsageReportDtos = landownerUsageReports.OrderBy(x => x.AccountNumber).Select(x => new LandownerUsageReportDto()
{
AccountID = x.AccountID,
AccountName = x.AccountName,
AccountNumber = x.AccountNumber,
ProjectWater = x.ProjectWater,
Reconciliation = x.Reconciliation,
NativeYield = x.NativeYield,
StoredWater = x.StoredWater,
AcresManaged = x.AcresManaged,
Allocation = x.Allocation,
Purchased = x.Purchased,
Sold = x.Sold,
TotalSupply = x.TotalSupply,
UsageToDate = x.UsageToDate,
CurrentAvailable = x.CurrentAvailable,
NumberOfPostings = x.NumberOfPostings,
NumberOfTrades = x.NumberOfTrades,
MostRecentTradeNumber = x.MostRecentTradeNumber
});
return(landownerUsageReportDtos);
}
public static List <OpenETSyncHistoryDto> List(RioDbContext dbContext)
{
return(dbContext.OpenETSyncHistory
.Include(x => x.OpenETSyncResultType)
.Include(x => x.WaterYear)
.OrderByDescending(x => x.CreateDate).Select(x => x.AsDto()).ToList());
}
private static IQueryable <WaterTransfer> GetWaterTransfersImpl(RioDbContext dbContext)
{
return(dbContext.WaterTransfer
.Include(x => x.WaterTransferRegistration).ThenInclude(x => x.Account).ThenInclude(x => x.AccountUser).ThenInclude(x => x.User)
.Include(x => x.Offer).ThenInclude(x => x.Trade)
.AsNoTracking());
}
public static void BulkCreateWithListOfNames(RioDbContext dbContext, string rioConfigurationVerificationKeyChars, List <string> accountNamesToCreate)
{
var listOfAccountsToCreate = new List <Account>();
var currentAccountVerificationKeys = GetCurrentAccountVerificationKeys(dbContext);
accountNamesToCreate.ForEach(x =>
{
var accountVerificationKey =
GenerateAndVerifyAccountVerificationKey(rioConfigurationVerificationKeyChars,
currentAccountVerificationKeys);
currentAccountVerificationKeys.Add(accountVerificationKey);
listOfAccountsToCreate.Add(new Account()
{
AccountStatusID = (int)AccountStatusEnum.Active,
AccountName = x,
UpdateDate = DateTime.UtcNow,
CreateDate = DateTime.UtcNow,
AccountVerificationKey = accountVerificationKey
});
});
dbContext.Account.AddRange(listOfAccountsToCreate);
dbContext.SaveChanges();
}
public static List <AccountSimpleDto> ListByUserID(RioDbContext dbContext, int userID)
{
return(dbContext.User.Include(x => x.AccountUser).ThenInclude(x => x.Account)
.Single(x => x.UserID == userID).AccountUser
.OrderBy(x => x.Account.AccountName)
.Select(x => x.Account.AsSimpleDto()).ToList());
}
public static WaterYearDto GetDefaultYearToDisplay(RioDbContext dbContext)
{
var year = DateTime.Now.Year;
return(dbContext.WaterYear.Any(x => x.Year == year) ?
dbContext.WaterYear.Single(x => x.Year == year).AsDto() :
dbContext.WaterYear.OrderByDescending(x => x.Year).First().AsDto());
}
public static void UpdateAccountVerificationKeyLastUsedDateForAccountIDs(RioDbContext dbContext, List <int> accountIDs)
{
var accounts = dbContext.Account.Where(x => accountIDs.Contains(x.AccountID)).ToList();
accounts.ForEach(x => x.AccountVerificationKeyLastUseDate = DateTime.UtcNow);
dbContext.SaveChanges();
}
public static object List(RioDbContext dbContext)
{
var roles = dbContext.AccountStatus
.AsNoTracking()
.Select(x => x.AsDto());
return(roles);
}
public static object GetByAccountStatusID(RioDbContext dbContext, int accountStatusID)
{
var accountStatus = dbContext.AccountStatus
.AsNoTracking()
.FirstOrDefault(x => x.AccountStatusID == accountStatusID);
return(accountStatus?.AsDto());
}
public static void UpdateParcelLayerUpdateDateForID(RioDbContext dbContext, int waterYearId)
{
var waterYear = dbContext.WaterYear.Single(x => x.WaterYearID == waterYearId);
waterYear.ParcelLayerUpdateDate = DateTime.UtcNow;
dbContext.SaveChanges();
}
private static IQueryable <Posting> GetPostingImpl(RioDbContext dbContext)
{
return(dbContext.Posting
.Include(x => x.PostingType)
.Include(x => x.PostingStatus)
.Include(x => x.CreateAccount).ThenInclude(x => x.AccountUser).ThenInclude(x => x.User)
.Include(x => x.CreateAccount.AccountStatus).AsNoTracking());
}
public static List <ParcelSimpleDto> ListParcelsByAccountID(RioDbContext dbContext, int accountId)
{
return(dbContext.AccountReconciliation
.Include(x => x.Parcel)
.Where(x => x.AccountID == accountId)
.Select(x => x.Parcel.AsSimpleDto())
.ToList());
}
public static OfferStatusDto GetByOfferStatusID(RioDbContext dbContext, int offerStatusID)
{
var offerStatus = dbContext.OfferStatus
.AsNoTracking()
.SingleOrDefault(x => x.OfferStatusID == offerStatusID);
return(offerStatus?.AsDto());
}
public static IEnumerable <OfferStatusDto> List(RioDbContext dbContext)
{
var offerStatusDtos = dbContext.OfferStatus
.AsNoTracking()
.Select(x => x.AsDto());
return(offerStatusDtos);
}
public static List <WaterYearDto> ListNonFinalized(RioDbContext dbContext)
{
return(dbContext.WaterYear
.Where(x => x.FinalizeDate == null)
.OrderByDescending(x => x.Year)
.Select(x => x.AsDto())
.ToList());
}
public static void DeleteByParcelID(RioDbContext dbContext, int parcelId)
{
var toRemove = dbContext.AccountReconciliation.Where(x => x.ParcelID == parcelId);
dbContext.AccountReconciliation.RemoveRange(toRemove);
dbContext.SaveChanges();
}
public static bool HasOpenOfferByAccountID(RioDbContext dbContext, PostingDto posting, int createAccountID)
{
return(dbContext.Trade.Any(x =>
x.PostingID == posting.PostingID &&
x.CreateAccountID == createAccountID &&
(x.TradeStatusID == (int)TradeStatusEnum.Accepted ||
x.TradeStatusID == (int)TradeStatusEnum.Countered)));
}
