public async Task <List <Arrivals> > GetArrivals(ArrivalFilter filter)
{
string filterStr = GetFilter(filter);
DbConnection connection = GetConnection();
string sql = @" SELECT A.Id AS Id,
A.FID AS FID,
A.ArrivalDate AS ArrivalDate,
A.Comments AS Comments,
F.Country AS Country, F.City AS City, F.Address AS Address, F.ZipCode AS ZipCode
FROM Arrivals A
INNER JOIN Facilities F
ON F.Id = A.FID
WHERE " + filterStr;
var result = connection.Query <Arrivals>(sql).ToList();
foreach (var item in result)
{
string sql2 = @"SELECT ASe.Serial AS Serial, ASe.Id AS Id
FROM ArrivalSerials ASe
WHERE ASe.ArrivalID = " + item.Id;
var serials = connection.Query <Serials>(sql2).ToList();
item.Serials = serials;
}
connection.Close();
return(result);
}
private string GetFilter(ArrivalFilter filter)
{
string filterStr = "1=1";
string dateConverted;
if (filter.ArrivalDateFrom != null)
{
dateConverted = filter.ArrivalDateFrom?.ToString("yyyy-MM-ddTHH:mm:ssZ");
filterStr += $" AND A.ArrivalDate >= '{dateConverted}'";
}
if (filter.ArrivalDateTo != null)
{
dateConverted = filter.ArrivalDateTo?.ToString("yyyy-MM-ddTHH:mm:ssZ");
filterStr += $" AND A.ArrivalDate <= '{dateConverted}'";
}
if (filter.Id != 0)
{
filterStr += $" AND A.Id = {filter.Id}";
}
if (!string.IsNullOrEmpty(filter.FID))
{
filterStr += $" AND A.FID like '{filter.FID}%'";
}
return(filterStr);
}
public async Task <int> GetTotalArrivals(ArrivalFilter filter)
{
string filterStr = GetFilter(filter);
DbConnection connection = GetConnection();
string sql = @" SELECT COUNT(*)
FROM Arrivals A
WHERE " + filterStr;
var result = connection.Query <int>(sql).FirstOrDefault();
connection.Close();
return(result);
}