public IHttpActionResult Get(string country = null, int?m = null, int?y = null, string lang = null, string order = null, bool?desc = null, int?limit = -1)
{
DataAccess data = new DataAccess();
Finalscore fsc = new Finalscore()
{
id = -1,
namecountry = (country != null?country:"Nan"),
namelanguage = (lang != null?lang:"Nan"),
month = (m != null && y != null?new DateTime((int)y, (int)m + 1, 1):new DateTime(1800, 1, 1)),
score = -1,
order = (order != null?order:"Nan"),
desc = (desc != null?(bool)desc : false)
};
var products = data.GetFinalscore(fsc, limit);
List <FinalscoreModel> result = new List <FinalscoreModel>();
foreach (var item in products)
{
FinalscoreModel finalscore = new FinalscoreModel();
finalscore.id = item.id;
finalscore.namecountry = item.namecountry;
finalscore.namelanguage = item.namelanguage;
finalscore.score = item.score;
finalscore.month = item.month;
result.Add(finalscore);
}
return(Ok(result));
}
public IEnumerable <Finalscore> GetFinalscore(Finalscore sc, int?limit)
{
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
string query = @"
SELECT scores.id
,countries.name as cname
,languages.name as lname
,score
,month
FROM scores
LEFT JOIN countries ON countries.id = scores.id_country
LEFT JOIN languages ON languages.id = scores.id_language";
using (NpgsqlCommand command = connection.CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
bool where = false;
if (sc.namecountry != "Nan")
{
command.CommandText += " WHERE countries.name = @namecountry ";
command.Parameters.Add(new NpgsqlParameter("@namecountry", sc.namecountry));
where = true;
}
if (sc.namelanguage != "Nan")
{
if (where)
{
command.CommandText += "AND";
}
else
{
command.CommandText += " WHERE";
}
command.CommandText += " languages.name = @namelanguage ";
command.Parameters.Add(new NpgsqlParameter("@namelanguage", sc.namelanguage));
}
if (sc.month != new DateTime(1800, 1, 1))
{
if (where)
{
command.CommandText += "AND";
}
else
{
command.CommandText += " WHERE";
}
command.CommandText += " scores.month = @scoremonth ";
command.Parameters.Add(new NpgsqlParameter("@scoremonth", sc.month));
}
if (sc.order != "Nan")
{
command.CommandText += " ORDER BY " + sc.order;
if (sc.desc)
{
command.CommandText += " DESC";
}
else
{
command.CommandText += " ASC";
}
}
if (limit != null && limit > 0)
{
command.CommandText += " LIMIT @limit";
command.Parameters.Add(new NpgsqlParameter("@limit", limit));
}
NpgsqlDataReader reader = command.ExecuteReader();
List <Finalscore> Scores = new List <Finalscore>();
while (reader.Read())
{
Finalscore finalscore = new Finalscore();
finalscore.id = (int)reader["id"];
finalscore.namecountry = reader["cname"] as string;
finalscore.namelanguage = reader["lname"] as string;
finalscore.score = (int)reader["score"];
finalscore.month = DateTime.ParseExact(reader["month"].ToString(), "dd/MM/yyyy hh:mm:ss", CultureInfo.InvariantCulture);
Scores.Add(finalscore);
}
return(Scores);
}
}
}
