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