List <TopListDto> IOffloadService.GetOffloads(QueryParamsForTopList filters)
{
try
{
var parsedFilters = new QueryOffloadsInput
{
Count = ParseCount(filters.count),
pageNo = ParsePageNo(filters.pageNo, filters.count),
PreservationMethod = ParesPreservationMethod(filters.preservationMethod),
FishingGear = ParseFishingGear(filters.fishingGear),
BoatLength = parseBoatLength(filters.boatLength),
LandingTown = ParseLandingTown(filters.landingTown),
LandingState = ParseLandingState(filters.landingState),
Month = ParseMonth(filters.month),
Year = ParseYear(filters.year),
FishName = ParseFishName(filters.fishName),
fromDate = ParseFromDate(ParseMonth(filters.month), ParseYear(filters.year)),
toDate = ParseToDate(ParseMonth(filters.month), ParseYear(filters.year)),
};
return(this._offloadRepo.GetFilteredResults(parsedFilters));
}
catch (Exception e)
{
Console.WriteLine(e);
Console.WriteLine("could not parse input");
return(null);
}
}
public List <TopListDto> GetFilteredResults(QueryOffloadsInput filters)
{
var result = getFilterResultAsync(filters);
result.Wait();
var entity = result.Result;
var dto = new List <TopListDto>();
for (int i = 0; i < entity.Count; i++)
{
dto.Add(new TopListDto()
{
BoatName = entity[i].boatName,
TotalWeight = entity[i].totalWeight,
BoatRegistrationId = entity[i].registrationId,
Town = entity[i].landingTown,
State = entity[i].landingState,
LandingDate = entity[i].landingDate,
BoatRadioSignalId = entity[i].boatRadioSignalId,
BoatNationality = entity[i].boatNationalityId,
BoatFishingGear = entity[i].fishingGear,
BoatLength = entity[i].boatLength,
Avrage = entity[i].avrage,
});
}
return(dto);
}
public List <TopListDto> GetFilteredResults(QueryOffloadsInput filters)
{
var filteredResults = new List <TopListDto>();
var cmd = _connection.CreateCommand();
_connection.Open();
string boatLength = string.Empty;
string fishingGear = string.Empty;
string fishName = string.Empty;
string landingState = string.Empty;
string preservationMethood = string.Empty;
if (filters.BoatLength != null)
{
boatLength = string.Format("AND `Største lengde` BETWEEN {0} AND {1}", filters.BoatLength[0].ToString(), filters.BoatLength[1].ToString());
}
if (filters.FishingGear != null)
{
fishingGear = AddFilter(filters.FishingGear, "`Redskap - gruppe`");
}
if (filters.FishName != null)
{
fishName = AddFilter(filters.FishName, "`Art - hovedgruppe`");
}
if (filters.LandingState != null)
{
landingState = AddFilter(filters.LandingState, "`Landingsfylke`");
}
if (filters.PreservationMethod != null)
{
preservationMethood = AddFilter(filters.PreservationMethod, "Konserveringsmåte");
}
cmd.CommandText = string.Format(
@"SELECT
`Fartøynavn`,
`Redskap`,
`Største lengde`,
SUM(`Rundvekt`),
`Radiokallesignal (seddel)`,
`Registreringsmerke (seddel)`,
`Redskap - gruppe`,
`Art - hovedgruppe`,
`Landingsfylke`,
`Landingsdato`
FROM GetFilteredResults
WHERE `Landingsdato` BETWEEN CAST('{0}' AS DATE) AND CAST('{1}' AS DATE)
{2}
{3}
{4}
{5}
{6}
GROUP BY `Registreringsmerke (seddel)`
ORDER BY SUM(`Rundvekt`) DESC
LIMIT {7}
OFFSET {8};",
filters.fromDate,
filters.toDate,
boatLength,
fishingGear,
fishName,
landingState,
preservationMethood,
filters.Count,
filters.pageNo);
var reader = cmd.ExecuteReader();
using (reader)
{
while (reader.Read())
{
var topListDto = genTopListDto(
boatName: reader.GetString(0),
boatFishingGear: reader.GetString(1),
boatLength: reader.GetDouble(2),
totalWeight: reader.GetDouble(3),
boatRadioSignalId: reader.GetString(4),
boatRegistrationId: reader.GetString(5));
filteredResults.Add(topListDto);
}
}
_connection.Dispose();
return(filteredResults);
}
private async Task <List <TopListEntity> > getFilterResultAsync(QueryOffloadsInput filters)
{
if (filters.Month != null)
{
for (int i = 0; i < filters.Month.Count; i++)
{
Console.WriteLine(filters.Month[i]);
}
}
if (filters.Year != null)
{
for (int i = 0; i < filters.Year.Count; i++)
{
Console.WriteLine(filters.Year[i]);
}
}
using var cmd = _connection.CreateCommand();
cmd.CommandTimeout = 90;
_connection.Open();
cmd.CommandText = @"SELECT
CAST(boat_name AS CHAR(20)) as 'Línubátar nafn' ,
CAST(boat_regestration_id AS CHAR(20)) as 'Línubátar',
SUM(CONVERT(CAST(fish_weight as CHAR(20)), UNSIGNED)) as 'Afl í kg',
CAST(fish_utilization AS CHAR(20)) as 'Fish Utilization',
CAST(document_sales_id AS CHAR (20)) as 'Document sales id',
CAST(buyer_id AS CHAR (20)) as 'Buyer id',
CAST(buyer_nationality AS CHAR (20)) as 'Buyer nationality',
CAST(landing_town_id AS CHAR (20)) as 'Landing town id',
CAST(landing_town AS CHAR (20)) as 'Landing town',
CAST(landing_state_id AS CHAR (20)) as 'Landing state id',
CAST(landing_state AS CHAR (20)) as 'Landing state name',
CAST(boat_id AS CHAR (20)) as 'Boat id',
CAST(boat_radio_signal_id AS CHAR (20)) as 'Boat radio signal id',
CAST(boat_town_id AS CHAR (20)) as 'Boat town id',
CAST(boat_state_id AS CHAR (20)) as 'Boat state id',
CAST(boat_nationality_id AS CHAR (20)) as 'Boat nationality id',
CONVERT(CAST(boat_length AS CHAR (20)), FLOAT) as 'Boat length',
CAST(boat_weight_1969 AS CHAR (20)) as 'Boat weight 1969',
CAST(boat_weight AS CHAR (20)) as 'Boat weight',
CAST(boat_built_year AS CHAR (20)) as 'Boat built year',
CAST(engine_power AS CHAR (20)) as 'Engine power',
CAST(fishing_gear_id AS CHAR (20)) as 'Fishing gear id',
CAST(fishing_gear AS CHAR (20)) as 'Fishing gear',
CAST(Longitude AS CHAR (20)) as 'Longitute',
CAST(Latitude AS CHAR (20)) as 'Latitude',
STR_TO_DATE(CONCAT(SUBSTRING(CAST(landing_date AS CHAR (20)), 1, 2), ',' , CAST(landing_month AS CHAR (20)), ',',SUBSTRING(CAST(landing_date AS CHAR (20)), 7, 10)), '%d,%m,%Y') AS 'Landing date',
CAST(landing_time AS CHAR (20)) as 'Landing time',
CAST(landing_month AS CHAR (20)) as 'Landing month',
CAST(fish_id AS CHAR (20)) as 'Fish id',
CAST(fish_name AS CHAR (20)) AS 'Fish name',
CAST(fish_condition_id AS CHAR (20)) AS 'Fish condition id',
CAST(fish_condition AS CHAR (20)) AS 'Fish condition',
CAST(fish_preservation_method_id AS CHAR (20)) AS 'Fish preservation method id',
CAST(fish_preservation_method AS CHAR (20)) AS 'Fish preservation method',
CAST(packaging_id AS CHAR (20)) AS 'Packaging id',
CAST(packaging AS CHAR (20)) AS 'Packaging',
CAST(fish_quality_id AS CHAR (20)) AS 'Fish quality id',
CAST(fish_quality AS CHAR (20)) AS 'Fish quality',
CAST(fish_utilization AS CHAR (20)) AS 'Fish utilization',
CAST(landing_id AS CHAR (20)) AS 'Landing id',
COUNT(landing_id)/COUNT(DISTINCT landing_month) AS 'Average landings per month',
SUM(CONVERT(CAST(fish_weight as CHAR(20)), UNSIGNED))/COUNT(DISTINCT landing_month) as 'Meðal afl í kg á mánuði'
FROM englishVersion ";
// Ef filtering á við fishinggear, s.s. ef fishing gear filtering er til staðar þá fer það hingað.
bool firstFilter = true;
if (filters.BoatLength != null)
{
firstFilter = false;
string lenStr = string.Format(" WHERE (boat_length BETWEEN {0} AND {1})", filters.BoatLength[0], filters.BoatLength[1]);
cmd.CommandText = cmd.CommandText + lenStr;
}
if (filters.Month != null)
{
cmd.CommandText = cmd.CommandText + AddFilter(filters.Month, firstFilter, "landing_month", false);
firstFilter = false;
}
if (filters.FishingGear != null)
{
cmd.CommandText = cmd.CommandText + AddFilter(filters.FishingGear, firstFilter, "fishing_gear");
firstFilter = false;
}
if (filters.FishName != null)
{
cmd.CommandText = cmd.CommandText + AddFilter(filters.FishName, firstFilter, "fish_name");
firstFilter = false;
}
if (filters.LandingState != null)
{
cmd.CommandText = cmd.CommandText + AddFilter(filters.LandingState, firstFilter, "landing_state");
firstFilter = false;
}
if (filters.Year != null)
{
bool is2016 = false;
for (int i = 0; i < filters.Year.Count; i++)
{
if (filters.Year[i] == 2016)
{
is2016 = true;
}
}
if (!is2016)
{
_connection.Close();
return(new List <TopListEntity>());
}
}
cmd.CommandText = cmd.CommandText + " GROUP BY CAST(boat_regestration_id AS CHAR(20)), CAST(boat_name AS CHAR(20)) ORDER BY SUM(CONVERT(CAST(fish_weight as CHAR(20)), UNSIGNED)) DESC LIMIT " + filters.Count + ";";
// ....where ... fishing_gear = 'Autoline' OR fishing_gear = 'Andreline' OR fishing_gear = 'Juksa/pilk' OR fishing_gear = 'Flyteline'
var res = await this.ReadAllAsync(await cmd.ExecuteReaderAsync());
_connection.Close();
return(res);
}
