public IQueryable <LoadViewData> GetLoadsForCarrierMarketplaceAsAdmin(string[] transTypes) { return((from l in _context.Loads join c in _context.Customers on l.CustomerId equals c.CustomerId join OriginLoadStop in _context.LoadStops.Where(ls => ls.StopNbr == 1) on l.LoadId equals OriginLoadStop.LoadId join DestinationLoadStop in _context.LoadStops on new { l.LoadId, StopNbr = (int)l.Stops } equals new { DestinationLoadStop.LoadId, DestinationLoadStop.StopNbr } where transTypes.Contains(l.LatestTransactionTypeId) select new LoadViewData() { LoadId = l.LoadId, CustomerId = l.CustomerId, ReferenceLoadId = l.ReferenceLoadId, ReferenceLoadDisplay = l.ReferenceLoadDisplay, Stops = l.Stops, Miles = l.Miles, LineHaulRate = l.LineHaulRate, FuelRate = l.FuelRate, Commodity = l.Commodity, Weight = l.Weight, IsHazMat = l.IsHazMat, TransactionTypeId = l.LatestTransactionTypeId, DistanceFromOrig = 0, DistanceFromDest = 0, CustomerLoadTypeId = l.CustomerLoadTypeId, //Equipment EquipmentId = l.EquipmentId, EquipmentType = l.Equipment.EquipmentDesc, EquipmentCategoryId = l.Equipment.CategoryId ?? "Unknown", EquipmentCategoryDesc = l.Equipment.CategoryEquipmentDesc, EquipmentTypeDisplay = l.Equipment.CategoryId == null ? l.Equipment.EquipmentDesc : l.Equipment.CategoryEquipmentDesc, OriginLat = (double)OriginLoadStop.Latitude, OriginLng = (double)OriginLoadStop.Longitude, OriginCity = OriginLoadStop.City, OriginState = OriginLoadStop.State, OriginEarlyDtTm = OriginLoadStop.EarlyDtTm, OriginLateDtTm = OriginLoadStop.LateDtTm, DestLat = (double)DestinationLoadStop.Latitude, DestLng = (double)DestinationLoadStop.Longitude, DestCity = DestinationLoadStop.City, DestState = DestinationLoadStop.State, DestEarlyDtTm = DestinationLoadStop.EarlyDtTm, DestLateDtTm = DestinationLoadStop.LateDtTm, IsEstimatedFSC = FscUtilities.IsEstimatedFsc(c, (OriginLoadStop.EarlyDtTm ?? OriginLoadStop.LateDtTm), _dateTimeProvider.Now), // This is needed to make sure the quick filters work w/o loading to memory Scac = null, // Load entire entity to prevent EF from doing a subselect LoadServiceTypes = l.LoadServiceTypes }) .OrderBy(l => l.OriginLateDtTm)); }
protected bool IsEstimatedFsc(Guid customerId, DateTime?originPickupDate) { if (customerId.Equals(default(Guid)) || !originPickupDate.HasValue) { return(false); } if (_custSvc == null) { return(false); // When unit testing, we have no ICustomerService injected, so just always return false } var customer = _custSvc.GetCustomer(customerId); return(FscUtilities.IsEstimatedFsc(customer, originPickupDate.Value, _dateTime.Now)); }
public IQueryable <LoadViewData> GetLoadsForCarrierMarketplace(string[] transTypes, string userPrimaryScac) { return((from l in _context.Loads join cus in _context.Customers on l.CustomerId equals cus.CustomerId join OriginLoadStop in _context.LoadStops.Where(ls => ls.StopNbr == 1) on l.LoadId equals OriginLoadStop.LoadId join DestinationLoadStop in _context.LoadStops on new { l.LoadId, StopNbr = (int)l.Stops } equals new { DestinationLoadStop.LoadId, DestinationLoadStop.StopNbr } join lcs in _context.LoadCarrierScacs on new { l.LoadId, Scac = userPrimaryScac } equals new { lcs.LoadId, lcs.Scac } join cs in _context.CarrierScacs on lcs.Scac equals cs.Scac where transTypes.Contains(l.LatestTransactionTypeId) /* * If the User's PrimaryScac is a Dedicated Scac, then include the load * If the User's PrimaryScac is not dedicated and has no contract rate, then include the load * If the User's PriamryScac is not dedicated and has a contract rate, only include the load * if the contract rate is less-than-or-equal-to the load's LineHaulRate */ && (lcs.ContractRate == null || lcs.ContractRate <= l.LineHaulRate || cs.IsDedicated) select new LoadViewData() { LoadId = l.LoadId, CustomerId = l.CustomerId, ReferenceLoadId = l.ReferenceLoadId, ReferenceLoadDisplay = l.ReferenceLoadDisplay, Stops = l.Stops, Miles = l.Miles, LineHaulRate = cs.IsDedicated ? l.LineHaulRate : lcs.ContractRate ?? l.LineHaulRate, FuelRate = l.FuelRate, Commodity = l.Commodity, Weight = l.Weight, IsHazMat = l.IsHazMat, TransactionTypeId = l.LatestTransactionTypeId, DistanceFromOrig = 0, DistanceFromDest = 0, CustomerLoadTypeId = l.CustomerLoadTypeId, //Equipment EquipmentId = l.EquipmentId, EquipmentType = l.Equipment.EquipmentDesc, EquipmentCategoryId = l.Equipment.CategoryId ?? "Unknown", EquipmentCategoryDesc = l.Equipment.CategoryEquipmentDesc, EquipmentTypeDisplay = l.Equipment.CategoryId == null ? l.Equipment.EquipmentDesc : l.Equipment.CategoryEquipmentDesc, OriginLat = (double)OriginLoadStop.Latitude, OriginLng = (double)OriginLoadStop.Longitude, OriginCity = OriginLoadStop.City, OriginState = OriginLoadStop.State, OriginEarlyDtTm = OriginLoadStop.EarlyDtTm, OriginLateDtTm = OriginLoadStop.LateDtTm, DestLat = (double)DestinationLoadStop.Latitude, DestLng = (double)DestinationLoadStop.Longitude, DestCity = DestinationLoadStop.City, DestState = DestinationLoadStop.State, DestEarlyDtTm = DestinationLoadStop.EarlyDtTm, DestLateDtTm = DestinationLoadStop.LateDtTm, IsEstimatedFSC = FscUtilities.IsEstimatedFsc(cus, (OriginLoadStop.EarlyDtTm ?? OriginLoadStop.LateDtTm), _dateTimeProvider.Now), //This is needed to make sure the quick filters work w / o loading to memory Scac = null, // Load entire entity to prevent EF from doing a subselect LoadServiceTypes = l.LoadServiceTypes }) .OrderBy(l => l.OriginLateDtTm)); }
public IQueryable <LoadViewData> GetLoadsForCarrierWithLoadClaim(string[] transTypes, string[] userAuthorizedScacs) { return((from l in _context.Loads join c in _context.Customers on l.CustomerId equals c.CustomerId join lt in _context.LoadTransactions on l.LoadId equals lt.LoadId join lc in _context.LoadClaims on lt.LoadTransactionId equals lc.LoadTransactionId join claimUser in _context.Users on lc.UserId equals claimUser.UserId into claimUserJoin from claimUser in claimUserJoin.DefaultIfEmpty() join claimScac in _context.CarrierScacs on lc.Scac equals claimScac.Scac into claimScacJoin from claimScac in claimScacJoin.DefaultIfEmpty() join claimCarrier in _context.Carriers on claimScac.CarrierId equals claimCarrier.CarrierId into claimCarrierJoin from claimCarrier in claimCarrierJoin.DefaultIfEmpty() join contractedCarrierScac in _context.CustomerCarrierScacContracts on new { lc.Scac, l.CustomerId } equals new { contractedCarrierScac.Scac, contractedCarrierScac.CustomerId } into contractedCarrierScacJoin from contractedCarrierScac in contractedCarrierScacJoin.DefaultIfEmpty() join OriginLoadStop in _context.LoadStops.Where(ls => ls.StopNbr == 1) on l.LoadId equals OriginLoadStop.LoadId join DestinationLoadStop in _context.LoadStops on new { l.LoadId, StopNbr = (int)l.Stops } equals new { DestinationLoadStop.LoadId, DestinationLoadStop.StopNbr } where transTypes.Contains(l.LatestTransactionTypeId) && ( userAuthorizedScacs.Contains(lc.Scac) && lc.LoadClaimId == ( from lt in _context.LoadTransactions join lc in _context.LoadClaims on lt.LoadTransactionId equals lc.LoadTransactionId where lt.LoadId == l.LoadId orderby lc.CreateDtTm descending select lc).FirstOrDefault().LoadClaimId ) select new LoadViewData() { LoadId = l.LoadId, CustomerId = l.CustomerId, ReferenceLoadId = l.ReferenceLoadId, ReferenceLoadDisplay = l.ReferenceLoadDisplay, Stops = l.Stops, Miles = l.Miles, LineHaulRate = lc != null ? lc.LineHaulRate : l.LineHaulRate, FuelRate = l.FuelRate, Commodity = l.Commodity, Weight = l.Weight, IsHazMat = l.IsHazMat, TransactionTypeId = l.LatestTransactionTypeId, DistanceFromOrig = 0, DistanceFromDest = 0, CustomerLoadTypeId = l.CustomerLoadTypeId, //Equipment EquipmentId = l.EquipmentId, EquipmentType = l.Equipment.EquipmentDesc, EquipmentCategoryId = l.Equipment.CategoryId ?? "Unknown", EquipmentCategoryDesc = l.Equipment.CategoryEquipmentDesc, EquipmentTypeDisplay = l.Equipment.CategoryId == null ? l.Equipment.EquipmentDesc : l.Equipment.CategoryEquipmentDesc, OriginLat = (double)OriginLoadStop.Latitude, OriginLng = (double)OriginLoadStop.Longitude, OriginCity = OriginLoadStop.City, OriginState = OriginLoadStop.State, OriginEarlyDtTm = OriginLoadStop.EarlyDtTm, OriginLateDtTm = OriginLoadStop.LateDtTm, DestLat = (double)DestinationLoadStop.Latitude, DestLng = (double)DestinationLoadStop.Longitude, DestCity = DestinationLoadStop.City, DestState = DestinationLoadStop.State, DestEarlyDtTm = DestinationLoadStop.EarlyDtTm, DestLateDtTm = DestinationLoadStop.LateDtTm, IsEstimatedFSC = FscUtilities.IsEstimatedFsc(c, (OriginLoadStop.EarlyDtTm ?? OriginLoadStop.LateDtTm), _dateTimeProvider.Now), Scac = lc.Scac, BookedUser = claimUser.Username, BookedUserCarrierName = claimCarrier.CarrierName, BillingLoadId = lc.BillingLoadId, BillingLoadDisplay = lc.BillingLoadDisplay, VisibilityPhoneNumber = lc.VisibilityPhoneNumber, VisibilityTruckNumber = lc.VisibilityTruckNumber, VisibilityChgDtTm = lc.VisibilityChgDtTm, MobileExternallyEntered = lc.MobileExternallyEntered, HasContractedCarrierScac = contractedCarrierScac != null, FeeData = new LoadshopFeeData { LoadshopFlatFee = lc.FlatFee, LoadshopPercentFee = lc.PercentFee, LoadshopFeeAdd = lc.FeeAdd, LoadshopFee = lc.LoadshopFee }, // Load entire entity to prevent EF from doing a subselect LoadServiceTypes = l.LoadServiceTypes }) .OrderBy(l => l.OriginLateDtTm)); }
protected PageableQuery <ShippingLoadViewData> GetLoadsBySearchType(ShipperSearchTypeData searchType, int max, DateTime?visibilityPickupWindowDate = null, bool topsToGoCarrier = false, bool p44Carrier = false) { var userPrimaryCustomerId = _context.Users.SingleOrDefault(u => u.IdentUserId == _userContext.UserId)?.PrimaryCustomerId; var onLoadShopTransactionTypes = new[] { "New", "Updated" }; var bookedUserTransActionsTypes = new[] { "Accepted", "Pending" }; var currentDate = _dateTime.Now; var isMarketplace = ShipperSearchTypeData.Posted == searchType; visibilityPickupWindowDate = visibilityPickupWindowDate ?? currentDate; if (userPrimaryCustomerId.HasValue) { //var transactionTypeIds = MapSearchTypeToTransactionList(c); //var shippingLoads = _context.ShippingLoadViews.FromSql($"EXECUTE spGetLoadsByShipperAndTranType @CustomerId = {userPrimaryCustomerId}, @TransactionTypes = {transactionTypeIds}"); var transTypes = _serviceUtilities.MapShipperSearchTypeToTransactionList(searchType); if (isMarketplace) { return((from l in _context.Loads join c in _context.Customers on l.CustomerId equals c.CustomerId join OriginLoadStop in _context.LoadStops.Where(ls => ls.StopNbr == 1) on l.LoadId equals OriginLoadStop.LoadId join DestinationLoadStop in _context.LoadStops on new { l.LoadId, StopNbr = (int)l.Stops } equals new { DestinationLoadStop.LoadId, DestinationLoadStop.StopNbr } where transTypes.Contains(l.LatestTransactionTypeId) && l.CustomerId == userPrimaryCustomerId select new ShippingLoadViewData() { LoadId = l.LoadId, CustomerId = l.CustomerId, ReferenceLoadId = l.ReferenceLoadId, ReferenceLoadDisplay = l.ReferenceLoadDisplay, Stops = l.Stops, Miles = l.Miles, LineHaulRate = l.LineHaulRate, SmartSpotRate = l.SmartSpotRate, FuelRate = l.FuelRate, Commodity = l.Commodity, Weight = l.Weight, IsHazMat = l.IsHazMat, TransactionTypeId = l.LatestTransactionTypeId, DistanceFromOrig = 0, DistanceFromDest = 0, Onloadshop = onLoadShopTransactionTypes.Contains(l.LatestTransactionTypeId), CustomerLoadTypeId = l.CustomerLoadTypeId, //Equipment EquipmentId = l.EquipmentId, EquipmentType = l.Equipment.EquipmentDesc, EquipmentCategoryId = l.Equipment.CategoryId ?? "Unknown", EquipmentCategoryDesc = l.Equipment.CategoryEquipmentDesc, EquipmentTypeDisplay = l.Equipment.CategoryId == null ? l.Equipment.EquipmentDesc : l.Equipment.CategoryEquipmentDesc, OriginLat = (double)OriginLoadStop.Latitude, OriginLng = (double)OriginLoadStop.Longitude, OriginCity = OriginLoadStop.City, OriginState = OriginLoadStop.State, OriginEarlyDtTm = OriginLoadStop.EarlyDtTm, OriginLateDtTm = OriginLoadStop.LateDtTm, DestLat = (double)DestinationLoadStop.Latitude, DestLng = (double)DestinationLoadStop.Longitude, DestCity = DestinationLoadStop.City, DestState = DestinationLoadStop.State, DestEarlyDtTm = DestinationLoadStop.EarlyDtTm, DestLateDtTm = DestinationLoadStop.LateDtTm, IsEstimatedFSC = FscUtilities.IsEstimatedFsc(c, (OriginLoadStop.EarlyDtTm ?? OriginLoadStop.LateDtTm), _dateTime.Now), //Needed so the quick filter works Scac = null, // Load entire entity to prevent EF from doing a subselect LoadServiceTypes = l.LoadServiceTypes }) .OrderByDescending(l => l.OriginLateDtTm) .ToPageableQuery(shippingLoadList => { foreach (var l in shippingLoadList) { l.ShowVisibilityWarning = l.OriginLateDtTm > currentDate && l.OriginLateDtTm <= visibilityPickupWindowDate && l.DestLateDtTm >= currentDate && ((topsToGoCarrier && p44Carrier && l.VisibilityPhoneNumber == null && !l.MobileExternallyEntered && l.VisibilityTruckNumber == null) || (topsToGoCarrier && !p44Carrier && l.VisibilityPhoneNumber == null && !l.MobileExternallyEntered) || (!topsToGoCarrier && p44Carrier && l.VisibilityTruckNumber == null)); } return Task.CompletedTask; }, max)); } else { return((from l in _context.Loads join c in _context.Customers on l.CustomerId equals c.CustomerId join lt in _context.LoadTransactions on l.LoadId equals lt.LoadId join lc in _context.LoadClaims on lt.LoadTransactionId equals lc.LoadTransactionId into lcJoin from lc in lcJoin.DefaultIfEmpty() join claimUser in _context.Users on lc.UserId equals claimUser.UserId into claimUserJoin from claimUser in claimUserJoin.DefaultIfEmpty() join claimScac in _context.CarrierScacs on lc.Scac equals claimScac.Scac into claimScacJoin from claimScac in claimScacJoin.DefaultIfEmpty() join claimCarrier in _context.Carriers on claimScac.CarrierId equals claimCarrier.CarrierId into claimCarrierJoin from claimCarrier in claimCarrierJoin.DefaultIfEmpty() join OriginLoadStop in _context.LoadStops.Where(ls => ls.StopNbr == 1) on l.LoadId equals OriginLoadStop.LoadId join DestinationLoadStop in _context.LoadStops on new { l.LoadId, StopNbr = (int)l.Stops } equals new { DestinationLoadStop.LoadId, DestinationLoadStop.StopNbr } where transTypes.Contains(l.LatestTransactionTypeId) && l.CustomerId == userPrimaryCustomerId && (lc.LoadClaimId == (from lt in _context.LoadTransactions join lc in _context.LoadClaims on lt.LoadTransactionId equals lc.LoadTransactionId where lt.LoadId == l.LoadId orderby lc.CreateDtTm descending select lc).FirstOrDefault().LoadClaimId) select new ShippingLoadViewData() { LoadId = l.LoadId, CustomerId = l.CustomerId, ReferenceLoadId = l.ReferenceLoadId, ReferenceLoadDisplay = l.ReferenceLoadDisplay, Stops = l.Stops, Miles = l.Miles, LineHaulRate = lc != null ? lc.LineHaulRate : l.LineHaulRate, SmartSpotRate = l.SmartSpotRate, FuelRate = l.FuelRate, Commodity = l.Commodity, Weight = l.Weight, IsHazMat = l.IsHazMat, TransactionTypeId = l.LatestTransactionTypeId, DistanceFromOrig = 0, DistanceFromDest = 0, Onloadshop = onLoadShopTransactionTypes.Contains(l.LatestTransactionTypeId), CustomerLoadTypeId = l.CustomerLoadTypeId, //Equipment EquipmentId = l.EquipmentId, EquipmentType = l.Equipment.EquipmentDesc, EquipmentCategoryId = l.Equipment.CategoryId ?? "Unknown", EquipmentCategoryDesc = l.Equipment.CategoryEquipmentDesc, EquipmentTypeDisplay = l.Equipment.CategoryId == null ? l.Equipment.EquipmentDesc : l.Equipment.CategoryEquipmentDesc, OriginLat = (double)OriginLoadStop.Latitude, OriginLng = (double)OriginLoadStop.Longitude, OriginCity = OriginLoadStop.City, OriginState = OriginLoadStop.State, OriginEarlyDtTm = OriginLoadStop.EarlyDtTm, OriginLateDtTm = OriginLoadStop.LateDtTm, DestLat = (double)DestinationLoadStop.Latitude, DestLng = (double)DestinationLoadStop.Longitude, DestCity = DestinationLoadStop.City, DestState = DestinationLoadStop.State, DestEarlyDtTm = DestinationLoadStop.EarlyDtTm, DestLateDtTm = DestinationLoadStop.LateDtTm, IsEstimatedFSC = FscUtilities.IsEstimatedFsc(c, (OriginLoadStop.EarlyDtTm ?? OriginLoadStop.LateDtTm), _dateTime.Now), //Load Claim Scac = lc.Scac, BookedUser = claimUser.Username, BookedUserCarrierName = claimCarrier.CarrierName, BillingLoadId = lc.BillingLoadId, BillingLoadDisplay = lc.BillingLoadDisplay, VisibilityPhoneNumber = lc.VisibilityPhoneNumber, VisibilityTruckNumber = lc.VisibilityTruckNumber, VisibilityChgDtTm = lc.VisibilityChgDtTm, MobileExternallyEntered = lc.MobileExternallyEntered, // Load entire entity to prevent EF from doing a subselect LoadServiceTypes = l.LoadServiceTypes }) .OrderByDescending(l => l.OriginLateDtTm) .ToPageableQuery(shippingLoadList => { foreach (var l in shippingLoadList) { l.ShowVisibilityWarning = l.OriginLateDtTm > currentDate && l.OriginLateDtTm <= visibilityPickupWindowDate && l.DestLateDtTm >= currentDate && ((topsToGoCarrier && p44Carrier && l.VisibilityPhoneNumber == null && !l.MobileExternallyEntered && l.VisibilityTruckNumber == null) || (topsToGoCarrier && !p44Carrier && l.VisibilityPhoneNumber == null && !l.MobileExternallyEntered) || (!topsToGoCarrier && p44Carrier && l.VisibilityTruckNumber == null)); } return Task.CompletedTask; }, max)); } } return(PageableQuery <ShippingLoadViewData> .Empty()); }