public AdvancedSearch(ASCriteria criteria)
{
InitializeComponent();
if (criteria != null)
{
if (criteria.AddedFrom > DateTime.MinValue)
{
textBoxAddedFrom.Text = criteria.AddedFrom.ToString();
}
if (criteria.AddedTo > DateTime.MinValue)
{
textBoxAddedTo.Text = criteria.AddedTo.ToString();
}
textBoxAuthor.Text = criteria.Author;
textBoxDOI.Text = criteria.Doi;
if (criteria.Favorite == 0)
{
radioButtonNo.Checked = true;
}
else if (criteria.Favorite == 1)
{
radioButtonYes.Checked = true;
}
textBoxNotes.Text = criteria.Notes;
textBoxTags.Text = criteria.Tags;
textBoxTitle.Text = criteria.Title;
if (criteria.YearFrom > 0)
{
textBoxYearFrom.Text = criteria.YearFrom.ToString();
}
if (criteria.YearTo > 0)
{
textBoxYearTo.Text = criteria.YearTo.ToString();
}
}
}
private void buttonAdvancedSearch_Click(object sender, EventArgs e)
{
AdvancedSearch ads;
if (this.criteria == null)
{
ads = new AdvancedSearch();
}
else
{
ads = new AdvancedSearch(this.criteria);
}
List<Files> files = null;
if (ads.ShowDialog() == DialogResult.OK)
{
files = dbm.getASFiles(ads.Criteria);
this.criteria = ads.Criteria;
if (treeViewDirs.Nodes.Count == 8)
{
treeViewDirs.Nodes.Add("Search result");
}
treeViewDirs.SelectedNode = null;
treeViewDirs.SelectedNode = treeViewDirs.Nodes[8];
}
}
private void buttonSearch_Click(object sender, EventArgs e)
{
textBoxYearFrom.ForeColor = Color.White;
int yearFrom = 0;
int yearTo = 0;
DateTime addedFrom = DateTime.MinValue;
DateTime addedTo = DateTime.MinValue;
string errorMessage = "";
if ((textBoxYearFrom.Text != "") && !(Int32.TryParse(textBoxYearFrom.Text, out yearFrom)))
{
errorMessage += "Not valid year format. (Year: From:) Please give an integer.\n";
textBoxYearFrom.ForeColor = Color.Red;
}
if ((textBoxYearTo.Text != "") && !(Int32.TryParse(textBoxYearTo.Text, out yearTo)))
{
errorMessage += "Not valid year format. (Year: To:) Please give an integer.\n";
textBoxYearTo.ForeColor = Color.Red;
}
if ((textBoxAddedFrom.Text != "") && !(DateTime.TryParse(textBoxAddedFrom.Text, out addedFrom)))
{
errorMessage += "Not valid date format. (Added: From:) Please give a date.\n";
textBoxAddedFrom.ForeColor = Color.Red;
}
if ((textBoxAddedTo.Text != "") && !(DateTime.TryParse(textBoxAddedTo.Text, out addedTo)))
{
errorMessage += "Not valid date format. (Added: To:) Please give a date.\n";
textBoxAddedTo.ForeColor = Color.Red;
}
if (errorMessage == "")
{
int favorite = 2;
if (radioButtonNo.Checked)
{
favorite = 0;
}
else if (radioButtonYes.Checked)
{
favorite = 1;
}
criteria = new ASCriteria(textBoxTitle.Text, textBoxAuthor.Text, yearFrom, yearTo, textBoxDOI.Text, textBoxTags.Text,
addedFrom, addedTo, textBoxNotes.Text, favorite);
this.DialogResult = System.Windows.Forms.DialogResult.OK;
}
else
{
MessageBox.Show(errorMessage);
}
}
// This finds all files, which have 'Biztosítás' and 'pdf' tags on it.
//Select *
//From files As f
//Where Exists (
// Select 1
// From file_tag As ft
// LEFT JOIN tags t ON t.id = ft.tags_id
// Where t.name In('Biztosítás','pdf') --insert tags here, it can't be empty!
// And ft.files_id = f.Id
// Group By ft.files_id
// Having Count(*) = 2 --tags no.
// ) --insert addicional condition here
//
public List<Files> getASFiles(ASCriteria criteria)
{
List<Files> files = new List<Files>();
string where = "";
string command = @"SELECT f.*, group_concat(t.name, ', ') AS tags_name FROM files f
LEFT JOIN file_tag ft ON ft.'files_id' = f.'id'
LEFT JOIN tags t ON ft.'tags_id' = t.'id'";
if (criteria.Title != "")
{
where += "and f.title LIKE $title ";
}
if (criteria.Author != "")
{
where += "and f.author LIKE $author ";
}
if (criteria.Doi != "")
{
where += "and f.doi LIKE $doi ";
}
if (criteria.Tags != "")
{
where += @"and Exists (
Select 1
From file_tag As ft
LEFT JOIN tags t ON t.id = ft.tags_id
Where t.name In ({name_tags})
And ft.files_id = f.Id
Group By ft.files_id
Having Count(*) = $name_tags_count
)";
}
if (criteria.YearFrom != 0)
{
where += "and f.year >= $yearFrom ";
}
if (criteria.YearTo != 0)
{
where += "and f.year <= $yearTo ";
}
if (criteria.AddedFrom > DateTime.MinValue)
{
where += "and f.added >= $addedFrom ";
}
if (criteria.AddedTo > DateTime.MinValue)
{
where += "and f.added <= $addedTo ";
}
if (criteria.Notes != "")
{
where += "and f.note LIKE $note ";
}
if (criteria.Favorite == 1)
{
where += "and f.favorite = 1 ";
}
else if (criteria.Favorite == 0)
{
where += "and f.favorite = 0 ";
}
if (where != "")
{
where = " WHERE " + where.Remove(0, 4);
command += where;
}
command += @" GROUP BY f.id";
SQLiteCommand sqlc = new SQLiteCommand(command, dbConnection);
if (criteria.Title != "")
{
sqlc.Parameters.AddWithValue("$title", criteria.Title);
}
if (criteria.Author != "")
{
sqlc.Parameters.AddWithValue("$author", criteria.Author);
}
if (criteria.Doi != "")
{
sqlc.Parameters.AddWithValue("$doi", criteria.Doi);
}
if (criteria.Tags != "")
{
string[] tags = criteria.Tags.Split(new string[] { ", ", "," }, StringSplitOptions.RemoveEmptyEntries);
sqlc.AddArrayParameters("name_tags", tags);
sqlc.Parameters.AddWithValue("$name_tags_count", tags.Length);
}
if (criteria.YearFrom != 0)
{
sqlc.Parameters.AddWithValue("$yearFrom", criteria.YearFrom);
}
if (criteria.YearTo != 0)
{
sqlc.Parameters.AddWithValue("$yearTo", criteria.YearTo);
}
if (criteria.AddedFrom > DateTime.MinValue)
{
sqlc.Parameters.AddWithValue("$addedFrom", criteria.AddedFrom);
}
if (criteria.AddedTo > DateTime.MinValue)
{
sqlc.Parameters.AddWithValue("$addedTo", criteria.AddedTo);
}
if (criteria.Notes != "")
{
sqlc.Parameters.AddWithValue("$note", criteria.Notes);
}
SQLiteDataReader sqldr = sqlc.ExecuteReader();
while (sqldr.Read())
{
// Do not use sqldr["id"].toString() because it won't work and kills the program!
files.Add(new Files(
Convert.ToInt32(sqldr["id"]),
Convert.ToString(sqldr["title"]),
Convert.ToString(sqldr["author"]),
Convert.ToString(sqldr["year"]),
Convert.ToString(sqldr["doi"]),
Convert.ToString(sqldr["vdirs_id"]),
Convert.ToBoolean(sqldr["favorite"]),
Convert.ToString(sqldr["type"]),
Convert.ToString(sqldr["tags_name"]),
Convert.ToString(sqldr["note"]),
Convert.ToString(sqldr["location"]),
Convert.ToString(sqldr["added"]),
Convert.ToString(sqldr["rread"])
));
}
return files;
}
