public List <Filter> FindAllByUser(User user)
{
var connection = DataBase.GetConnection();
string sql = @"
SELECT
filters.id,
filters.name,
filters.text,
feeds.id AS feed_id,
feeds.name AS feed_name,
feeds.url AS feed_url
FROM
filters
LEFT JOIN feed_filters
ON filters.id = feed_filters.filter_id
LEFT JOIN feeds
ON feed_filters.feed_id = feeds.id
WHERE
filters.user_id = :user_id
ORDER BY
filters.name,
filters.id
";
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
command.Parameters.Add(new SQLiteParameter("user_id", user.Id.Value));
using (var reader = command.ExecuteReader())
{
var results = new List <Filter>();
var resultsById = new Dictionary <long, Filter>();
while (reader.Read())
{
var filterId = Convert.ToInt64(reader["id"]);
Filter filter;
if (resultsById.ContainsKey(filterId))
{
filter = resultsById[filterId];
}
else
{
filter = RowReader.ReadFilter(reader, user, "");
results.Add(filter);
resultsById.Add(filterId, filter);
}
if (!reader.IsDBNull(reader.GetOrdinal("feed_id")))
{
filter.Feeds.Add(RowReader.ReadFeed(reader, user, "feed_"));
}
}
return(results);
}
}
}
public List <NewsItem> FindAllByUserAndFilter(User user, Filter filter)
{
var connection = DataBase.GetConnection();
var sql = @"
SELECT
news.id, news.guid, news.title, news.contents, news.datetime,
news.url, news.feed_id, news.favorite, news.unread,
feeds.name AS feed_name, feeds.url AS feed_url
FROM
news
INNER JOIN feeds
ON news.feed_id = feeds.id
WHERE
feeds.user_id = :user_id
AND news.active = 1
AND (
news.title LIKE :filterText
OR news.contents LIKE :filterText
)
";
if (filter.Feeds.Count > 0)
{
sql += " AND news.feed_id IN (" + string.Join(",", filter.Feeds.Select((feed, index) => ":feed_id_" + index)) + ") ";
}
sql += @"
ORDER BY
news.datetime DESC
";
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
command.Parameters.Add(new SQLiteParameter("user_id", user.Id.Value));
// TODO mejorar el filtro para que busque las palabras en cualquier orden
command.Parameters.Add(new SQLiteParameter("filterText", "%" + filter.Text.Replace(' ', '%') + "%"));
for (int i = 0; i < filter.Feeds.Count; ++i)
{
command.Parameters.Add(new SQLiteParameter("feed_id_" + i, filter.Feeds[i].Id));
}
using (var reader = command.ExecuteReader())
{
var results = new List <NewsItem>();
while (reader.Read())
{
results.Add(RowReader.ReadNewsItem(reader, user, ""));
}
return(results);
}
}
}
internal List <NewsItem> FindAllByUserAndFeed(User user, Feed feed)
{
var connection = DataBase.GetConnection();
string sql = @"
SELECT
news.id, news.guid, news.title, news.contents, news.datetime,
news.url, news.feed_id, news.favorite, news.unread,
feeds.name AS feed_name, feeds.url AS feed_url
FROM
news
INNER JOIN feeds
ON news.feed_id = feeds.id
WHERE
feeds.user_id = :user_id
AND news.active = 1
";
if (feed != null)
{
sql += " AND news.feed_id = :feed_id ";
}
sql += @"
ORDER BY
news.datetime DESC
";
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
command.Parameters.Add(new SQLiteParameter("user_id", user.Id.Value));
if (feed != null)
{
command.Parameters.Add(new SQLiteParameter("feed_id", feed.Id.Value));
}
using (var reader = command.ExecuteReader())
{
var results = new List <NewsItem>();
while (reader.Read())
{
results.Add(RowReader.ReadNewsItem(reader, user, ""));
}
return(results);
}
}
}
public List <Feed> FindAllByUser(User user)
{
var connection = DataBase.GetConnection();
string sql = "SELECT id, name, url FROM feeds WHERE user_id = :user_id";
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
command.Parameters.Add(new SQLiteParameter("user_id", user.Id.Value));
using (var reader = command.ExecuteReader())
{
var results = new List <Feed>();
while (reader.Read())
{
results.Add(RowReader.ReadFeed(reader, user, ""));
}
return(results);
}
}
}