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