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; }