public async Task <int> GetAllCount(FilterShipment filter)
{
using (IDbConnection cn = new SqlConnection(ConnectionString))
{
cn.Open();
DynamicParameters d = new DynamicParameters();
{
if (filter.TransporterStatus == ShipmentTransporterStatus.Unassigned)
{
d.Add("@StatusCode", "UAS");
}
if (filter.TransporterStatus == ShipmentTransporterStatus.Assigned)
{
d.Add("@StatusCode", "ASS");
}
if (filter.TransporterStatus == ShipmentTransporterStatus.OpenMarket)
{
d.Add("@StatusCode", "OPEN");
}
if (filter.TransporterStatus == ShipmentTransporterStatus.Completed)
{
d.Add("@StatusCode", "COM");
}
if (!string.IsNullOrEmpty(filter.ShipmentStatus))
{
d.Add("@ShipmentStatus", filter.ShipmentStatus);
}
}
return((await cn.QueryAsync <int>(GetShipmentQueryFilteredCount(filter), d)).Count());
}
}
public async Task <IEnumerable <ShipmentModel> > GetAll(int customerId, int filterType, bool inPending, int startItem, int numberOfRetrievedItems,
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 FilterShipment
{
CustomerId = currentUser.CustomerId.Value,
StartItem = startItem,
Amount = numberOfRetrievedItems,
TransporterStatus = (ShipmentTransporterStatus)filterType,
Pending = inPending,
Declined = !inPending
};
var shipments = await _shipmentService.GetAll(searchFilter);
return(shipments.OrderByDescending(item => item.Id));
}
private string GetShipmentQueryFilteredCount(FilterShipment filter)
{
if (filter.StartItem < 0)
{
filter.StartItem = 0;
}
if (filter.Amount < 0)
{
filter.Amount = 9999;
}
var sb = new StringBuilder();
sb.Append(@"SELECT
[Shipment].[Id]
from Shipment
where 1=1");
if (filter.CustomerId.HasValue)
{
sb.Append(" and Shipment.CustomerId=" + filter.CustomerId.Value);
}
if (!string.IsNullOrEmpty(filter.ShipmentStatus))
{
sb.Append(" and Shipment.ShipmentStatus=@ShipmentStatus");
}
if (filter.TransporterId.HasValue)
{
sb.Append(" and Shipment.TransporterId=" + filter.TransporterId.Value);
}
if (filter.TransporterStatus == ShipmentTransporterStatus.Assigned)
{
sb.Append(" and Shipment.ShipmentStatus=@StatusCode");
if (filter.Declined)
{
sb.Append(@" and exists (select ShipmentTransporter.Id from ShipmentTransporter
where Shipment.Id=ShipmentTransporter.ShipmentId and ShipmentTransporter.Declined=1)");
}
else if (filter.Pending)
{
sb.Append(@" and exists (select ShipmentTransporter.Id from ShipmentTransporter
where Shipment.Id=ShipmentTransporter.ShipmentId and ShipmentTransporter.Declined=0)");
}
}
if (filter.TransporterStatus == ShipmentTransporterStatus.OpenMarket ||
filter.TransporterStatus == ShipmentTransporterStatus.Completed ||
filter.TransporterStatus == ShipmentTransporterStatus.Unassigned)
{
sb.Append(" and Shipment.ShipmentStatus=@StatusCode");
}
return(sb.ToString());
}
public async Task Test_GetShipmentByStatuses_Is_Ok()
{
try
{
FilterShipment f = new FilterShipment {
CustomerId = 1, StartItem = 0,
Amount = 5,
TransporterStatus = ShipmentTransporterStatus.Unassigned
};
List <ShipmentModel> model =
await _shipmentService.GetAll(f);
f.TransporterStatus = ShipmentTransporterStatus.Completed;
model =
await _shipmentService.GetAll(f);
f.TransporterStatus = ShipmentTransporterStatus.OpenMarket;
model =
await _shipmentService.GetAll(f);
f.TransporterStatus = ShipmentTransporterStatus.Unassigned;
model =
await _shipmentService.GetAll(f);
f.TransporterStatus = ShipmentTransporterStatus.Assigned;
model =
await _shipmentService.GetAll(f);
f.Declined = true;
model =
await _shipmentService.GetAll(f);
f.Declined = false;
f.Pending = true;
model =
await _shipmentService.GetAll(f);
}
catch (Exception ex)
{
Assert.IsFalse(true);
}
Assert.IsTrue(true);
}
public async Task <int> GetCount(int customerId, int filterType,
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();
var searchFilter = new FilterShipment
{
CustomerId = currentUser.CustomerId.Value,
StartItem = 0,
Amount = 10000,
TransporterStatus = (ShipmentTransporterStatus)filterType
};
var shipmentsCount = await _shipmentService.GetAllCount(searchFilter);
return(shipmentsCount);
}
private string GetQueryFiltered(FilterShipment filter)
{
if (filter.StartItem < 0)
{
filter.StartItem = 0;
}
if (filter.Amount < 0)
{
filter.Amount = 9999;
}
var sb = new StringBuilder();
sb.Append(@"SELECT RowConstrainedResult.* from (select ROW_NUMBER() OVER
( ORDER BY Shipment.Id ) AS RowNum
,[Shipment].[Id]
,[Shipment].[Reference]
,[Shipment].[PoNumber]
,[Shipment].[PickUpDate]
,[Shipment].[DeliveryDate]
,[Shipment].[CustomerId]
,[Shipment].[SenderAddressId]
,[Shipment].[SenderContactPerson]
,[Shipment].[SenderPhone]
,[Shipment].[SenderRemark]
,[Shipment].[ReceiverAddressId]
,[Shipment].[ReceiverContactPerson]
,[Shipment].[ReceiverPhone]
,[Shipment].[ReceiverRemark]
,isnull([Shipment].[TotalPrice],ShipmentTransporter.Price) as TotalPrice
,[Shipment].[TotalVolume]
,[Shipment].[TotalQuatity]
,[Shipment].[TotalWeight]
,[Shipment].[ShipmentStatus]
,[Shipment].[TransporterId]
,[Shipment].[UserIdCreated]
,[Shipment].[DateCreated]
,[Shipment].[UserIdModified]
,[Shipment].[InvoiceAmount]
,[Shipment].[InvoiceComment]
,[Shipment].[InvoiceDate]
,[Shipment].[PickUpComment]
,[Shipment].[DeliveryComment]
,[Shipment].[DeliveryPod]
,[Shipment].[DateModified]
,isnull(UserCreatedTable.FirstName,'') + ' '+isnull(UserCreatedTable.LastName,'') as UserCreated
,isnull(UserModifiedTable.FirstName,'') + ' '+isnull(UserModifiedTable.LastName,'') as UserModified
,isnull(Transporter.Name,AssignedTransporter.Name) as TransporterName
,FromAddress.Name as AddressFrom
,ToAddress.Name as AddressTo");
if (filter.TransporterStatus == ShipmentTransporterStatus.OpenMarket)
{
sb.Append(@" ,(select count(ShipmentTransporter.Id) As OfferCount from ShipmentTransporter where
ShipmentTransporter.ShipmentId=Shipment.Id) as OfferCount");
}
sb.Append(@" from Shipment
left outer join [ApplicationUser] as UserCreatedTable on UserCreatedTable.Id=Shipment.UserIdCreated
left outer join [ApplicationUser] as UserModifiedTable on UserModifiedTable.Id=Shipment.UserIdModified
left outer join [Transporter] on Transporter.Id=Shipment.TransporterId
left outer join [Address] as FromAddress on FromAddress.Id=Shipment.SenderAddressId
left outer join [Address] as ToAddress on ToAddress.Id=Shipment.ReceiverAddressId
left outer join ShipmentTransporter on ShipmentTransporter.ShipmentId =Shipment.Id and ShipmentTransporter.Assigned=1
left outer join Transporter as AssignedTransporter on AssignedTransporter.Id=ShipmentTransporter.TransporterId ");
sb.Append(@" where 1=1");
if (filter.CustomerId.HasValue)
{
sb.Append(" and Shipment.CustomerId=" + filter.CustomerId.Value);
}
if (!string.IsNullOrEmpty(filter.ShipmentStatus))
{
sb.Append(" and Shipment.ShipmentStatus=@ShipmentStatus");
}
if (filter.TransporterId.HasValue)
{
sb.Append(" and Shipment.TransporterId=" + filter.TransporterId.Value);
}
if (filter.TransporterStatus == ShipmentTransporterStatus.Assigned)
{
sb.Append(" and Shipment.ShipmentStatus=@StatusCode");
if (filter.Declined)
{
sb.Append(@" and exists (select ShipmentTransporter.Id from ShipmentTransporter
where Shipment.Id=ShipmentTransporter.ShipmentId and ShipmentTransporter.Declined=1)");
}
else if (filter.Pending)
{
sb.Append(@" and exists (select ShipmentTransporter.Id from ShipmentTransporter
where Shipment.Id=ShipmentTransporter.ShipmentId and ShipmentTransporter.Declined=0)");
}
}
if (filter.TransporterStatus == ShipmentTransporterStatus.OpenMarket ||
filter.TransporterStatus == ShipmentTransporterStatus.Completed ||
filter.TransporterStatus == ShipmentTransporterStatus.Unassigned)
{
sb.Append(" and Shipment.ShipmentStatus=@StatusCode");
}
sb.Append(@" ) AS RowConstrainedResult
WHERE RowNum between " + filter.StartItem + @" and " + (filter.StartItem + filter.Amount - 1) + @" ORDER BY RowNum ");
return(sb.ToString());
}
