public void GetLoadDetails(string transactionTypeId)
{
var loads = BuildLoads();
var loadTestId = Guid.NewGuid();
loads.First().LoadId = loadTestId;
loads.First().LatestTransactionTypeId = transactionTypeId;
var loadTransactions = BuildLoadTransactions();
loadTransactions.First().LoadId = loadTestId;
InitDb(loads: loads, loadTransactions: loadTransactions);
InitLoadQueryRepo();
var opts = new GetLoadDetailOptions()
{
LoadId = loadTestId,
TransactionTypes = new List <string>()
{
transactionTypeId
}
};
var result = _repo.GetLoadDetailViews(opts);
result.Should().HaveCount(1);
result.First().LoadId.Should().Be(loadTestId);
}
public virtual List <LoadDetailViewEntity> GetLoadDetailViews(GetLoadDetailOptions options)
{
//var loadIdParam = loadId.HasValue && loadId != Guid.Empty ? loadId.ToString() : null;
//var referenceLoadIdParam = !string.IsNullOrWhiteSpace(referenceLoadId) ? referenceLoadId.Trim() : null;
//var customerIdentIdParam = customerIdentUserId.HasValue && customerIdentUserId != Guid.Empty ? customerIdentUserId.Value.ToString() : null;
//var txTypesParam = transactionTypes != null && transactionTypes.Count > 0 ? string.Join(",", transactionTypes) : null;
//var loads = _context.LoadDetailViews
// .FromSql($"EXEC spGetLoadDetailView @LoadId = {loadIdParam}, @ReferenceLoadId = {referenceLoadIdParam}, @CustomerIdentId = {customerIdentIdParam}, @TransactionTypes = {txTypesParam}")
// .ToList();
var loads = (from l in _context.Loads
join c in _context.Customers on l.CustomerId equals c.CustomerId
join lt in _context.LoadTransactions on l.LatestLoadTransactionId equals lt.LoadTransactionId
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 contractedCarrierScac in _context.CustomerCarrierScacContracts on new { lc.Scac, l.CustomerId } equals new { contractedCarrierScac.Scac, contractedCarrierScac.CustomerId } into contractedCarrierScacJoin
from contractedCarrierScac in contractedCarrierScacJoin.DefaultIfEmpty()
//join lcs in _context.LoadCarrierScacs on new {l.LoadId, Scac = primaryScac } equals new { lcs.LoadId, lcs.Scac } into lcsJoin
//from lcs in lcsJoin.DefaultIfEmpty()
//join cs in _context.CarrierScacs on lcs.Scac equals cs.Scac into csJoin
//from cs in csJoin.DefaultIfEmpty()
//join originLoadStop in _context.LoadStops on new { l.LoadId, StopNbr = 1 } equals new { originLoadStop.LoadId, originLoadStop.StopNbr }
//join destinationLoadStop in _context.LoadStops on new { l.LoadId, StopNbr = (int)l.Stops } equals new { destinationLoadStop.LoadId, destinationLoadStop.StopNbr }
orderby lt.CreateDtTm descending
where
(options.LoadId == null || options.LoadId == l.LoadId) &&
(options.CustomerIdentUserId == null || options.CustomerIdentUserId == c.IdentUserId) &&
(options.ReferenceLoadId == null || options.ReferenceLoadId == l.ReferenceLoadId) &&
(options.TransactionTypes == null || options.TransactionTypes.Contains(l.LatestTransactionTypeId))
select new LoadDetailViewEntity()
{
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,
Cube = l.Cube,
Weight = l.Weight,
IsHazMat = l.IsHazMat,
TransactionTypeId = l.LatestTransactionTypeId,
IsAccepted = l.IsAccepted,
Comments = l.Comments,
CreateDtTm = l.CreateDtTm,
CreateBy = l.CreateBy,
LastChgDtTm = l.LastChgDtTm,
LastChgBy = l.LastChgBy,
ScacsSentWithLoad = l.ScacsSentWithLoad,
ManuallyCreated = l.ManuallyCreated,
TransportationModeId = l.TransportationModeId,
ShipperPickupNumber = l.ShipperPickupNumber,
PlatformPlusLoadId = l.PlatformPlusLoadId,
DATGuardRate = l.DATGuardRate,
MachineLearningRate = l.MachineLearningRate,
UsesAllInRates = l.UsesAllInRates,
//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.NullPropagate(x => x.Scac),
BookedUserId = claimUser.NullPropagate(cu => cu.UserId),
BookedUser = claimUser.NullPropagate(cu => cu.Username),
BookedUserCarrierName = claimCarrier.NullPropagate(cc => cc.CarrierId),
BillingLoadId = lc.NullPropagate(lc => lc.BillingLoadId),
BillingLoadDisplay = lc.NullPropagate(lc => lc.BillingLoadDisplay),
VisibilityPhoneNumber = lc.NullPropagate(lc => lc.VisibilityPhoneNumber),
VisibilityTruckNumber = lc.NullPropagate(lc => lc.VisibilityTruckNumber),
VisibilityChgDtTm = lc.NullPropagate(lc => lc.VisibilityChgDtTm),
MobileExternallyEntered = lc.NullPropagate(lc => lc.MobileExternallyEntered),
ProcessedDtTm = lt.NullPropagate(lt => lt.ProcessedDtTm),
LoadTransactionId = lt.NullPropagate(lt => lt.LoadTransactionId),
TransactionLineHaulRate = lc.NullPropagate(lc => lc.LineHaulRate),
TransactionFuelRate = lc.NullPropagate(lc => lc.FuelRate),
UserId = lc.NullPropagate(lc => lc.UserId),
TransactionLastUpdateTime = lt.NullPropagate(lt => lt.LastChgDtTm),
IsPlatformPlus = lc != null && lc.Scac != null && contractedCarrierScac == null,
FlatFee = lc.NullPropagate(lt => lt.FlatFee),
PercentFee = lc.NullPropagate(lt => lt.PercentFee),
FeeAdd = lc.NullPropagate(lc => lc.FeeAdd),
LoadshopFee = lc.NullPropagate(lc => lc.LoadshopFee),
})
.ToList();
if (options.IncludeContacts || options.IncludeStops || options.IncludeEquipment)
{
var loadIds = loads.Select(x => x.LoadId).Distinct().ToList();
var loadContactGroups = new List <IGrouping <Guid, LoadContactEntity> >();
var loadStopGroups = new List <IGrouping <Guid, LoadStopEntity> >();
var equipmentIds = new List <string>();
var equipments = new List <EquipmentEntity>();
var documentsGroups = new List <IGrouping <Guid, LoadDocumentEntity> >();
var currentStatusGroups = new List <IGrouping <Guid, LoadCurrentStatusEntity> >();
var loadServiceTypes = new List <IGrouping <Guid, LoadServiceTypeEntity> >();
if (options.IncludeContacts)
{
loadContactGroups = _context.LoadContacts.Where(x => loadIds.Contains(x.LoadId)).AsEnumerable().GroupBy(x => x.LoadId).ToList();
}
if (options.IncludeStops)
{
loadStopGroups = _context.LoadStops.Where(x => loadIds.Contains(x.LoadId)).AsEnumerable().GroupBy(x => x.LoadId).ToList();
}
if (options.IncludeEquipment)
{
equipmentIds = loads.Select(x => x.EquipmentId).Distinct().ToList();
equipments = _context.Equipment.Where(x => equipmentIds.Contains(x.EquipmentId)).ToList();
}
if (options.IncludeDocuments)
{
documentsGroups = _context.LoadDocuments.Where(x => loadIds.Contains(x.LoadId)).AsEnumerable().GroupBy(x => x.LoadId).ToList();
}
if (options.IncludeCurrentStatuses)
{
currentStatusGroups = _context.LoadCurrentStatuses.Where(x => loadIds.Contains(x.LoadId)).ToList().GroupBy(x => x.LoadId).ToList();
}
if (options.IncludeServiceTypes)
{
loadServiceTypes = _context.LoadServiceTypes.Include(x => x.ServiceType).Where(x => loadIds.Contains(x.LoadId)).ToList().GroupBy(x => x.LoadId).ToList();
}
foreach (var load in loads)
{
if (options.IncludeContacts)
{
var contacts = loadContactGroups.SingleOrDefault(x => x.Key == load.LoadId)?.ToList();
load.Contacts = contacts;
}
if (options.IncludeStops)
{
var loadStops = loadStopGroups.SingleOrDefault(x => x.Key == load.LoadId)?.ToList();
load.LoadStops = loadStops;
}
if (options.IncludeEquipment)
{
var equipment = equipments.SingleOrDefault(x => x.EquipmentId == load.EquipmentId);
load.Equipment = equipment;
}
if (options.IncludeDocuments)
{
load.LoadDocuments = documentsGroups.FirstOrDefault(x => x.Key == load.LoadId)?.ToList();
}
if (options.IncludeCurrentStatuses)
{
load.LoadCurrentStatuses = currentStatusGroups.FirstOrDefault(x => x.Key == load.LoadId)?.ToList();
}
if (options.IncludeServiceTypes)
{
load.LoadServiceTypes = loadServiceTypes.FirstOrDefault(x => x.Key == load.LoadId)?.ToList();
}
}
}
return(loads);
}
