private string GetQuery(FilterShipmentTransporter filter)
{
var sb = new StringBuilder();
sb.Append(@"SELECT ShipmentTransporter.Id
,ShipmentTransporter.ShipmentId
,ShipmentTransporter.TransporterId
,ShipmentTransporter.Assigned
,ShipmentTransporter.Accepted
,ShipmentTransporter.Declined
,ShipmentTransporter.Selected
,ShipmentTransporter.AssignedDate
,ShipmentTransporter.AcceptedDate
,ShipmentTransporter.DeclinedDate
,ShipmentTransporter.SelectedDate
,ShipmentTransporter.Price
,ShipmentTransporter.LoadingOn
,ShipmentTransporter.DeliveryOn
,ShipmentTransporter.UserIdCreated
,ShipmentTransporter.DateCreated
,ShipmentTransporter.UserIdModified
,ShipmentTransporter.DateModified
,isnull(UserCreatedTable.FirstName,'') + ' '+isnull(UserCreatedTable.LastName,'') as UserCreated
,isnull(UserModifiedTable.FirstName,'') + ' '+isnull(UserModifiedTable.LastName,'') as UserModified
,TransporterRating.Rating
,TransporterRating.AmountReview
,[Address].ContactPerson
,[Address].Phone
,Transporter.Name as TransporterName
,Transporter.IconName
,Shipment.PickUpDate
,Shipment.DeliveryDate
,[Address].Name as AddressName
FROM ShipmentTransporter
left outer join ApplicationUser as UserCreatedTable on UserCreatedTable.Id=ShipmentTransporter.UserIdCreated
left outer join ApplicationUser as UserModifiedTable on UserModifiedTable.Id=ShipmentTransporter.UserIdModified
inner join Transporter on Transporter.Id = ShipmentTransporter.TransporterId
inner join Shipment on Shipment.Id = ShipmentTransporter.ShipmentId
left outer join [Address] on [Address].id=Transporter.AddressId
outer apply (select Round(sum(Rating.Amount)/count(Rating.Id),2) as Rating,count(Rating.Id) as AmountReview from Rating where Rating.TransporterId =Transporter.Id)
as TransporterRating
Where 1=1 ");
if (filter.ShipmentId.HasValue)
{
sb.Append(" and ShipmentTransporter.ShipmentId=" + filter.ShipmentId.Value);
}
if (filter.TransporterId.HasValue)
{
sb.Append(" and ShipmentTransporter.TransporterId=" + filter.TransporterId.Value);
}
if (filter.CustomerId.HasValue)
{
sb.Append(" and Shipment.CustomerId=" + filter.CustomerId.Value);
}
if (!string.IsNullOrEmpty(filter.Predicate))
{
sb.Append(" and " + filter.Predicate);
}
return(sb.ToString());
}
public async Task <List <ShipmentTransporterDto> > GetAll(FilterShipmentTransporter filter)
{
using (IDbConnection cn = new SqlConnection(ConnectionString))
{
cn.Open();
return((await cn.QueryAsync <ShipmentTransporterDto>(GetQuery(filter))).ToList());
}
}
public async Task <IEnumerable <ShipmentTransporterModel> > GetAssignedTransporters(int shipmentId, int customerId, string language)
{
var currentUser = await _authenticationService.GetUser(User.Identity.Name);
if (currentUser.CustomerId != customerId)
{
throw new HttpResponseException(HttpStatusCode.InternalServerError, "Provided customer is not assigned to your account");
}
language.ConvertLocaleStringToServerLanguage();
if (currentUser.CustomerId != customerId)
{
throw new HttpResponseException(HttpStatusCode.InternalServerError, "Provided customer is not assigned to your account");
}
var searchFilter = new FilterShipmentTransporter
{
ShipmentId = shipmentId,
CustomerId = customerId,
};
var shipments = await _shipmentService.GetAssignedTransporters(searchFilter);
return(shipments.OrderByDescending(item => item.Id));
}
