private async void Remarks_Click(object sender, RoutedEventArgs e) { string path = @"\archive.db"; RecordEntryConfirmation record = (RecordEntryConfirmation)EntriesHolder.SelectedItem; if (record == null) { MsgNoItemSelected(); } else if (pmsutil.IsArchived(record.RecordID) == true && pmsutil.CheckArchiveDrive(path) == "dc") { MsgArchiveNotConnected(); } else { var metroWindow = (Application.Current.MainWindow as MetroWindow); await metroWindow.ShowChildWindowAsync(new ViewRemarksWindow(record.RecordID), this.ParentGrid); } }
void Worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) { //SyncChanges(); ArchivalProgBar.IsIndeterminate = false; if (pmsutil.CheckArchiveDrive(path) != "dc") { if (stat_code2 > 0) { MsgSuccess(); } else { MsgFail(); } } else { MsgFail(); } }
internal void CheckArchiveDrive() { DriveInfo[] allDrives = DriveInfo.GetDrives(); pmsutil = new PMSUtil(); if (pmsutil.CheckArchiveDrive(path) != "dc") { this.Dispatcher.Invoke(() => { ArchiveStatus.Content = "Archive Status: Connected"; archiveDrive = pmsutil.CheckArchiveDrive(path); }); } else { this.Dispatcher.Invoke(() => { ArchiveStatus.Content = "Archive Status: Disconnected"; archiveDrive = "init"; }); } Thread.Sleep(5000); // 5sec }
private void SyncMatrimonialEntries(int targBook, int pageNum) { records = new ObservableCollection <RecordEntryMatrimonial>(); dbman = new DBConnectionManager(); using (conn = new MySqlConnection(dbman.GetConnStr())) { conn.Open(); if (conn.State == ConnectionState.Open) { using (MySqlConnection conn2 = new MySqlConnection(dbman.GetConnStr())) { conn2.Open(); MySqlCommand cmd = conn2.CreateCommand(); cmd.CommandText = "SELECT * FROM registers WHERE book_number = @book_number LIMIT 1;"; cmd.Parameters.AddWithValue("@book_number", targBook); cmd.Prepare(); using (MySqlDataReader db_reader = cmd.ExecuteReader()) { while (db_reader.Read()) { if (db_reader.GetString("status") == "Archived") { MsgArchived(); EditRegButton.IsEnabled = false; PrintRegButton.IsEnabled = false; using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = "SELECT * FROM records WHERE records.book_number = @book_number AND records.page_number = @page_number ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@book_number", targBook); cmd2.Parameters.AddWithValue("@page_number", pageNum); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { string archiveDrive = "init"; string path = @"\archive.db"; while (db_reader2.Read()) { pmsutil = new PMSUtil(); if (pmsutil.CheckArchiveDrive(path) != "dc") { archiveDrive = pmsutil.CheckArchiveDrive(path); SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder { FailIfMissing = true, DataSource = archiveDrive }; using (SQLiteConnection connection = new SQLiteConnection(connectionString.ToString())) { // open the connection: connection.Open(); string stm = "SELECT * FROM matrimonial_records WHERE record_id='" + db_reader2.GetString("record_id") + "';"; using (SQLiteCommand cmdx = new SQLiteCommand(stm, connection)) { using (SQLiteDataReader rdr = cmdx.ExecuteReader()) { EditRegButton.IsEnabled = true; PrintRegButton.IsEnabled = true; while (rdr.Read()) { App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryMatrimonial() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), MarriageYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), MarriageDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName1 = db_reader2.GetString("recordholder_fullname"), FullName2 = rdr["recordholder2_fullname"].ToString(), Status1 = rdr["status1"].ToString(), Status2 = rdr["status2"].ToString(), Age1 = Convert.ToInt32(rdr["age1"]), Age2 = Convert.ToInt32(rdr["age2"]), Hometown1 = rdr["place_of_origin1"].ToString(), Hometown2 = rdr["place_of_origin2"].ToString(), Residence1 = rdr["residence1"].ToString(), Residence2 = rdr["residence2"].ToString(), Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Parent3 = rdr["parent1_fullname2"].ToString(), Parent4 = rdr["parent2_fullname2"].ToString(), Witness1 = rdr["witness1"].ToString(), Witness2 = rdr["witness2"].ToString(), W1Residence = rdr["witness1address"].ToString(), W2Residence = rdr["witness2address"].ToString(), Stipend = Convert.ToDouble(rdr["stipend"]), Minister = rdr["minister"].ToString() }); }); } } } } } else { archiveDrive = "init"; App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryMatrimonial() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), MarriageYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), MarriageDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName1 = db_reader2.GetString("recordholder_fullname"), FullName2 = "---", Status1 = "---", Status2 = "---", Age1 = 0, Age2 = 0, Hometown1 = "---", Hometown2 = "---", Residence1 = "---", Residence2 = "---", Parent1 = "---", Parent2 = "---", Parent3 = "---", Parent4 = "---", Witness1 = "---", Witness2 = "---", W1Residence = "---", W2Residence = "---", Stipend = 0, Minister = "---" }); }); } } } } } else { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = "SELECT * FROM records, matrimonial_records WHERE records.book_number = @book_number AND records.page_number = @page_number AND records.record_id = matrimonial_records.record_id ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@book_number", targBook); cmd2.Parameters.AddWithValue("@page_number", pageNum); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { while (db_reader2.Read()) { App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryMatrimonial() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), MarriageYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), MarriageDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName1 = db_reader2.GetString("recordholder_fullname"), FullName2 = db_reader2.GetString("recordholder2_fullname"), Status1 = db_reader2.GetString("status1"), Status2 = db_reader2.GetString("status2"), Age1 = db_reader2.GetInt32("age1"), Age2 = db_reader2.GetInt32("age2"), Hometown1 = db_reader2.GetString("place_of_origin1"), Hometown2 = db_reader2.GetString("place_of_origin2"), Residence1 = db_reader2.GetString("residence1"), Residence2 = db_reader2.GetString("residence2"), Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Parent3 = db_reader2.GetString("parent1_fullname2"), Parent4 = db_reader2.GetString("parent2_fullname2"), Witness1 = db_reader2.GetString("witness1"), Witness2 = db_reader2.GetString("witness2"), W1Residence = db_reader2.GetString("witness1address"), W2Residence = db_reader2.GetString("witness2address"), Stipend = db_reader2.GetFloat("stipend"), Minister = db_reader2.GetString("minister") }); }); } } } } } } } SyncChanges(); System.Threading.Thread.Sleep(1); } else { } //close Connection //conn.Close(); } }
private void SyncBaptismalEntries(string query, string type) { pmsutil = new PMSUtil(); records = new ObservableCollection <RecordEntryBaptismal>(); records_final = new ObservableCollection <RecordEntryBaptismal>(); ComboBoxItem ci = (ComboBoxItem)ItemsPerPage.SelectedItem; int itemsPerPage = Convert.ToInt32(ci.Content); int page = 1; int count = 0; dbman = new DBConnectionManager(); using (conn = new MySqlConnection(dbman.GetConnStr())) { conn.Open(); if (conn.State == ConnectionState.Open) { using (MySqlConnection conn2 = new MySqlConnection(dbman.GetConnStr())) { conn2.Open(); MySqlCommand cmd2 = conn2.CreateCommand(); if (_coverage == 0) { //All cmd2.CommandText = "SELECT DISTINCT * FROM records, registers WHERE registers.book_type = @book_type AND records.book_number = registers.book_number AND " + "(records.recordholder_fullname LIKE @query OR " + "records.parent1_fullname LIKE @query OR " + "records.parent2_fullname LIKE @query) ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@book_type", type); cmd2.Parameters.AddWithValue("@query", "%" + query + "%"); } else if (_coverage == 1) { //Name cmd2.CommandText = "SELECT DISTINCT * FROM records, registers WHERE registers.book_type = @book_type AND records.book_number = registers.book_number AND " + "(records.recordholder_fullname LIKE @query) ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@book_type", type); cmd2.Parameters.AddWithValue("@query", "%" + query + "%"); } else if (_coverage == 2) { DateTime dateValue; if (DateTime.TryParse(query, out dateValue)) { //Date cmd2.CommandText = "SELECT DISTINCT * FROM records, registers WHERE registers.book_type = @book_type AND records.book_number = registers.book_number AND " + "(records.record_date LIKE @query) ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@book_type", type); cmd2.Parameters.AddWithValue("@query", "%" + DateTime.Parse(query).ToString("yyyy-MM-dd") + "%"); } else { MsgNotDate(); //Date cmd2.CommandText = "SELECT DISTINCT * FROM records, registers WHERE registers.book_type = @book_type AND records.book_number = registers.book_number AND " + "(records.record_date LIKE @query) ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@book_type", type); cmd2.Parameters.AddWithValue("@query", "%" + query + "%"); } } else if (_coverage == 3) { //Parents cmd2.CommandText = "SELECT DISTINCT * FROM records, registers WHERE registers.book_type = @book_type AND records.book_number = registers.book_number AND " + "(records.parent1_fullname LIKE @query OR " + "records.parent2_fullname LIKE @query) ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@book_type", type); cmd2.Parameters.AddWithValue("@query", "%" + query + "%"); } cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { string archiveDrive = "init"; while (db_reader2.Read()) { if (db_reader2.GetString("status") == "Archived") { string path = @"\archive.db"; pmsutil = new PMSUtil(); if (pmsutil.CheckArchiveDrive(path) != "dc") { archiveDrive = pmsutil.CheckArchiveDrive(path); SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder { FailIfMissing = true, DataSource = archiveDrive }; using (SQLiteConnection connection = new SQLiteConnection(connectionString.ToString())) { // open the connection: connection.Open(); string stm = "SELECT * FROM baptismal_records WHERE record_id='" + db_reader2.GetString("record_id") + "';"; using (SQLiteCommand cmdx = new SQLiteCommand(stm, connection)) { using (SQLiteDataReader rdr = cmdx.ExecuteReader()) { while (rdr.Read()) { DateTime dateOfBirth = Convert.ToDateTime(rdr["birthday"].ToString()); App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryBaptismal() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), BaptismalYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), BaptismalDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName = db_reader2.GetString("recordholder_fullname"), BirthDate = dateOfBirth.ToString("MMM dd, yyyy"), Legitimacy = rdr["legitimacy"].ToString(), PlaceOfBirth = rdr["place_of_birth"].ToString(), Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Godparent1 = rdr["sponsor1"].ToString(), Godparent2 = rdr["sponsor2"].ToString(), Stipend = Convert.ToDouble(rdr["stipend"]), Minister = rdr["minister"].ToString(), Page = page }); }); } } } } } else { archiveDrive = "init"; App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryBaptismal() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), BaptismalYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), BaptismalDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName = db_reader2.GetString("recordholder_fullname"), BirthDate = "---", Legitimacy = "---", PlaceOfBirth = "---", Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Godparent1 = "---", Godparent2 = "---", Stipend = 0, Minister = "---", Page = page }); }); } } else { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd3 = conn3.CreateCommand(); cmd3.CommandText = "SELECT DISTINCT * FROM baptismal_records WHERE record_id = @rid;"; cmd3.Parameters.AddWithValue("@rid", db_reader2.GetString("record_id")); cmd3.Prepare(); using (MySqlDataReader db_reader3 = cmd3.ExecuteReader()) { while (db_reader3.Read()) { App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryBaptismal() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), BaptismalYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), BaptismalDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName = db_reader2.GetString("recordholder_fullname"), BirthDate = DateTime.Parse(db_reader3.GetString("birthday")).ToString("MMM dd, yyyy"), Legitimacy = db_reader3.GetString("legitimacy"), PlaceOfBirth = db_reader3.GetString("place_of_birth"), Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Godparent1 = db_reader3.GetString("sponsor1"), Godparent2 = db_reader3.GetString("sponsor2"), Stipend = db_reader3.GetFloat("stipend"), Minister = db_reader3.GetString("minister"), Page = page }); }); } } } } count++; if (count == itemsPerPage) { page++; count = 0; } } } } foreach (var cur in records) { if (cur.Page == CurrentPage.Value) { records_final.Add(new RecordEntryBaptismal() { RecordID = cur.RecordID, EntryNumber = cur.EntryNumber, BaptismalYear = cur.BaptismalYear, BaptismalDate = cur.BaptismalDate, FullName = cur.FullName, BirthDate = cur.BirthDate, Legitimacy = cur.Legitimacy, PlaceOfBirth = cur.PlaceOfBirth, Parent1 = cur.Parent1, Parent2 = cur.Parent2, Godparent1 = cur.Godparent1, Godparent2 = cur.Godparent2, Stipend = cur.Stipend, Minister = cur.Minister, Page = cur.Page }); } } SyncChanges(); System.Threading.Thread.Sleep(1); CurrentPage.Maximum = page; } else { } } }
private void SyncMatrimonialEntries(string query, string type) { pmsutil = new PMSUtil(); records = new ObservableCollection <RecordEntryMatrimonial>(); records_final = new ObservableCollection <RecordEntryMatrimonial>(); ComboBoxItem ci = (ComboBoxItem)ItemsPerPage.SelectedItem; int itemsPerPage = Convert.ToInt32(ci.Content); int page = 1; int count = 0; dbman = new DBConnectionManager(); using (conn = new MySqlConnection(dbman.GetConnStr())) { conn.Open(); if (conn.State == ConnectionState.Open) { using (MySqlConnection conn2 = new MySqlConnection(dbman.GetConnStr())) { conn2.Open(); MySqlCommand cmd2 = conn2.CreateCommand(); cmd2.CommandText = "SELECT DISTINCT * FROM records, registers WHERE registers.book_type = @book_type AND records.book_number = registers.book_number AND " + "(records.recordholder_fullname LIKE @query OR " + "records.parent1_fullname LIKE @query OR " + "records.parent2_fullname LIKE @query) ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@book_type", type); cmd2.Parameters.AddWithValue("@query", "%" + query + "%"); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { string archiveDrive = "init"; while (db_reader2.Read()) { if (db_reader2.GetString("status") == "Archived") { string path = @"\archive.db"; pmsutil = new PMSUtil(); if (pmsutil.CheckArchiveDrive(path) != "dc") { archiveDrive = pmsutil.CheckArchiveDrive(path); SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder { FailIfMissing = true, DataSource = archiveDrive }; using (SQLiteConnection connection = new SQLiteConnection(connectionString.ToString())) { // open the connection: connection.Open(); string stm = "SELECT * FROM matrimonial_records WHERE record_id='" + db_reader2.GetString("record_id") + "';"; using (SQLiteCommand cmdx = new SQLiteCommand(stm, connection)) { using (SQLiteDataReader rdr = cmdx.ExecuteReader()) { while (rdr.Read()) { App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryMatrimonial() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), MarriageYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), MarriageDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName1 = db_reader2.GetString("recordholder_fullname"), FullName2 = rdr["recordholder2_fullname"].ToString(), Status1 = rdr["status1"].ToString(), Status2 = rdr["status2"].ToString(), Age1 = Convert.ToInt32(rdr["age1"]), Age2 = Convert.ToInt32(rdr["age2"]), Hometown1 = rdr["place_of_origin1"].ToString(), Hometown2 = rdr["place_of_origin2"].ToString(), Residence1 = rdr["residence1"].ToString(), Residence2 = rdr["residence2"].ToString(), Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Parent3 = rdr["parent1_fullname2"].ToString(), Parent4 = rdr["parent2_fullname2"].ToString(), Witness1 = rdr["witness1"].ToString(), Witness2 = rdr["witness2"].ToString(), W1Residence = rdr["witness1address"].ToString(), W2Residence = rdr["witness2address"].ToString(), Stipend = Convert.ToDouble(rdr["stipend"]), Minister = rdr["minister"].ToString(), Page = page }); }); } } } } } else { archiveDrive = "init"; App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryMatrimonial() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), MarriageYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), MarriageDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName1 = db_reader2.GetString("recordholder_fullname"), FullName2 = "---", Status1 = "---", Status2 = "---", Age1 = 0, Age2 = 0, Hometown1 = "---", Hometown2 = "---", Residence1 = "---", Residence2 = "---", Parent1 = "---", Parent2 = "---", Parent3 = "---", Parent4 = "---", Witness1 = "---", Witness2 = "---", W1Residence = "---", W2Residence = "---", Stipend = 0, Minister = "---", Page = page }); }); } } else { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd3 = conn3.CreateCommand(); cmd3.CommandText = "SELECT DISTINCT * FROM matrimonial_records WHERE record_id = @rid;"; cmd3.Parameters.AddWithValue("@rid", db_reader2.GetString("record_id")); cmd3.Prepare(); using (MySqlDataReader db_reader3 = cmd3.ExecuteReader()) { while (db_reader3.Read()) { App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryMatrimonial() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), MarriageYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), MarriageDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName1 = db_reader2.GetString("recordholder_fullname"), FullName2 = db_reader3.GetString("recordholder2_fullname"), Status1 = db_reader3.GetString("status1"), Status2 = db_reader3.GetString("status2"), Age1 = db_reader3.GetInt32("age1"), Age2 = db_reader3.GetInt32("age2"), Hometown1 = db_reader3.GetString("place_of_origin1"), Hometown2 = db_reader3.GetString("place_of_origin2"), Residence1 = db_reader3.GetString("residence1"), Residence2 = db_reader3.GetString("residence2"), Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Parent3 = db_reader3.GetString("parent1_fullname2"), Parent4 = db_reader3.GetString("parent2_fullname2"), Witness1 = db_reader3.GetString("witness1"), Witness2 = db_reader3.GetString("witness2"), W1Residence = db_reader3.GetString("witness1address"), W2Residence = db_reader3.GetString("witness2address"), Stipend = db_reader3.GetFloat("stipend"), Minister = db_reader3.GetString("minister"), Page = page }); }); } } } } count++; if (count == itemsPerPage) { page++; count = 0; } } } } foreach (var cur in records) { if (cur.Page == CurrentPage.Value) { records_final.Add(new RecordEntryMatrimonial() { RecordID = cur.RecordID, EntryNumber = cur.EntryNumber, MarriageYear = cur.MarriageYear, MarriageDate = cur.MarriageDate, FullName1 = cur.FullName1, FullName2 = cur.FullName2, Status1 = cur.Status1, Status2 = cur.Status2, Age1 = cur.Age1, Age2 = cur.Age2, Hometown1 = cur.Hometown1, Hometown2 = cur.Hometown2, Residence1 = cur.Residence1, Residence2 = cur.Residence2, Parent1 = cur.Parent1, Parent2 = cur.Parent2, Parent3 = cur.Parent3, Parent4 = cur.Parent4, Witness1 = cur.Witness1, Witness2 = cur.Witness2, W1Residence = cur.W1Residence, W2Residence = cur.W2Residence, Stipend = cur.Stipend, Minister = cur.Minister, Page = cur.Page }); } } SyncChanges(); System.Threading.Thread.Sleep(1); CurrentPage.Maximum = page; } else { } } }
private void AddItems(object sender, DoWorkEventArgs e) { records = new ObservableCollection <RecordEntryBurial>(); dbman = new DBConnectionManager(); using (conn = new MySqlConnection(dbman.GetConnStr())) { conn.Open(); if (conn.State == ConnectionState.Open) { using (MySqlConnection conn2 = new MySqlConnection(dbman.GetConnStr())) { conn2.Open(); MySqlCommand cmd = conn2.CreateCommand(); cmd.CommandText = "SELECT * FROM registers WHERE book_number = @book_number LIMIT 1;"; cmd.Parameters.AddWithValue("@book_number", bnum); cmd.Prepare(); using (MySqlDataReader db_reader = cmd.ExecuteReader()) { while (db_reader.Read()) { if (db_reader.GetString("status") == "Archived") { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = "SELECT * FROM records WHERE records.book_number = @book_number AND (records.recordholder_fullname LIKE @query OR records.parent1_fullname LIKE @query OR records.parent2_fullname LIKE @query) GROUP BY records.record_id ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@book_number", bnum); cmd2.Parameters.AddWithValue("@query", "%" + qry + "%"); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { string archiveDrive = "init"; string path = @"\archive.db"; while (db_reader2.Read()) { pmsutil = new PMSUtil(); if (pmsutil.CheckArchiveDrive(path) != "dc") { archiveDrive = pmsutil.CheckArchiveDrive(path); SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder { FailIfMissing = true, DataSource = archiveDrive }; using (SQLiteConnection connection = new SQLiteConnection(connectionString.ToString())) { // open the connection: connection.Open(); string stm = "SELECT * FROM burial_records WHERE record_id='" + db_reader2.GetString("record_id") + "';"; using (SQLiteCommand cmdx = new SQLiteCommand(stm, connection)) { using (SQLiteDataReader rdr = cmdx.ExecuteReader()) { while (rdr.Read()) { DateTime dateOfBurial = Convert.ToDateTime(rdr["burial_date"].ToString()); App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryBurial() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), DeathYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), DeathDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), BurialYear = dateOfBurial.ToString("yyyy"), BurialDate = dateOfBurial.ToString("MMM dd"), FullName = db_reader2.GetString("recordholder_fullname"), Age = Convert.ToInt32(rdr["age"]), Status = rdr["status"].ToString(), Residence1 = rdr["residence"].ToString(), Residence2 = rdr["residence2"].ToString(), Sacrament = rdr["sacrament"].ToString(), CauseOfDeath = rdr["cause_of_death"].ToString(), PlaceOfInterment = rdr["place_of_interment"].ToString(), Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Stipend = Convert.ToDouble(rdr["stipend"]), Minister = rdr["minister"].ToString() }); }); } } } } } else { archiveDrive = "init"; App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryBurial() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), DeathYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), DeathDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), BurialYear = "----", BurialDate = "----", FullName = db_reader2.GetString("recordholder_fullname"), Age = 0, Status = "----", Residence1 = "----", Residence2 = "----", Sacrament = "----", CauseOfDeath = "----", PlaceOfInterment = "----", Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Stipend = 0, Minister = "----" }); }); } } } } } else { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = "SELECT * FROM records, burial_records WHERE records.book_number = @book_number AND records.record_id = burial_records.record_id AND (records.recordholder_fullname LIKE @query OR records.parent1_fullname LIKE @query OR records.parent2_fullname LIKE @query OR burial_records.place_of_interment LIKE @query) GROUP BY records.record_id ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@book_number", bnum); cmd2.Parameters.AddWithValue("@query", "%" + qry + "%"); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { while (db_reader2.Read()) { App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryBurial() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), DeathYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), DeathDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), BurialYear = DateTime.Parse(db_reader2.GetString("burial_date")).ToString("yyyy"), BurialDate = DateTime.Parse(db_reader2.GetString("burial_date")).ToString("MMM dd"), FullName = db_reader2.GetString("recordholder_fullname"), Age = db_reader2.GetInt32("age"), Status = db_reader2.GetString("status"), Residence1 = db_reader2.GetString("residence"), Residence2 = db_reader2.GetString("residence2"), Sacrament = db_reader2.GetString("sacrament"), CauseOfDeath = db_reader2.GetString("cause_of_death"), PlaceOfInterment = db_reader2.GetString("place_of_interment"), Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Stipend = db_reader2.GetFloat("stipend"), Minister = db_reader2.GetString("minister") }); }); } } } } } } } (sender as BackgroundWorker).ReportProgress(0); System.Threading.Thread.Sleep(1); } else { } //close Connection //conn.Close(); } }
/// <summary> /// Creates the AddRequestForm Window and Initializes DB Param. /// </summary> public EditBurialRecordEntryWindow(string targRecord) { pmsutil = new PMSUtil(); InitializeComponent(); recordID = targRecord; Stipend.Value = FetchBurialStipend(); dbman = new DBConnectionManager(); using (conn = new MySqlConnection(dbman.GetConnStr())) { conn.Open(); if (conn.State == ConnectionState.Open) { using (MySqlConnection conn2 = new MySqlConnection(dbman.GetConnStr())) { conn2.Open(); MySqlCommand cmd = conn2.CreateCommand(); cmd.CommandText = "SELECT * FROM records, registers WHERE records.record_id = @rid AND records.book_number = registers.book_number LIMIT 1;"; cmd.Parameters.AddWithValue("@rid", recordID); cmd.Prepare(); using (MySqlDataReader db_reader = cmd.ExecuteReader()) { while (db_reader.Read()) { if (db_reader.GetString("status") == "Archived") { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = "SELECT * FROM records WHERE records.record_id = @rid ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@rid", recordID); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { string archiveDrive = "init"; string path = @"\archive.db"; while (db_reader2.Read()) { if (pmsutil.CheckArchiveDrive(path) != "dc") { archiveDrive = pmsutil.CheckArchiveDrive(path); SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder { FailIfMissing = true, DataSource = archiveDrive }; using (SQLiteConnection connection = new SQLiteConnection(connectionString.ToString())) { // open the connection: connection.Open(); string stm = "SELECT * FROM burial_records WHERE record_id='" + db_reader2.GetString("record_id") + "';"; using (SQLiteCommand cmdx = new SQLiteCommand(stm, connection)) { using (SQLiteDataReader rdr = cmdx.ExecuteReader()) { while (rdr.Read()) { DateTime dateOfBurial = Convert.ToDateTime(rdr["burial_date"].ToString()); EntryNum.Value = Convert.ToDouble(db_reader.GetString("entry_number")); PageNum.Value = Convert.ToDouble(db_reader.GetString("page_number")); DeathDate.Text = db_reader.GetString("record_date"); BurialDate.Text = dateOfBurial.ToString("MMM dd, yyyy"); Age.Value = Convert.ToDouble(rdr["age"]); FullName.Text = db_reader.GetString("recordholder_fullname"); Age.Value = Convert.ToDouble(rdr["age"]); Status.Text = rdr["status"].ToString(); Parent1.Text = db_reader.GetString("parent1_fullname"); Parent2.Text = db_reader.GetString("parent2_fullname"); Residence1.Text = rdr["residence"].ToString(); Residence2.Text = rdr["residence2"].ToString(); Sacrament.Text = rdr["sacrament"].ToString(); CauseOfDeath.Text = rdr["cause_of_death"].ToString(); PlaceOfInterment.Text = rdr["place_of_interment"].ToString(); Stipend.Value = Convert.ToDouble(rdr["stipend"]); Minister.Text = rdr["minister"].ToString(); Remarks.Text = rdr["remarks"].ToString(); } } } } } else { archiveDrive = "init"; EntryNum.Value = Convert.ToDouble(db_reader.GetString("entry_number")); PageNum.Value = Convert.ToDouble(db_reader.GetString("page_number")); DeathDate.Text = db_reader.GetString("record_date"); //BurialDate.Text = db_reader.GetString("burial_date"); Age.Value = 0; FullName.Text = db_reader.GetString("recordholder_fullname"); Age.Value = 0; Status.Text = "----"; Parent1.Text = db_reader.GetString("parent1_fullname"); Parent2.Text = db_reader.GetString("parent2_fullname"); Residence1.Text = "----"; Residence2.Text = "----"; Sacrament.Text = "----"; CauseOfDeath.Text = "----"; PlaceOfInterment.Text = "----"; Stipend.Value = 0; Minister.Text = "----"; Remarks.Text = "----"; } } } } } else { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = "SELECT * FROM records, burial_records WHERE records.record_id = @rid AND records.record_id = burial_records.record_id ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@rid", recordID); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { while (db_reader2.Read()) { EntryNum.Value = Convert.ToDouble(db_reader2.GetString("entry_number")); PageNum.Value = Convert.ToDouble(db_reader2.GetString("page_number")); DeathDate.Text = db_reader2.GetString("record_date"); BurialDate.Text = db_reader2.GetString("burial_date"); Age.Value = Convert.ToDouble(db_reader2.GetString("age")); FullName.Text = db_reader2.GetString("recordholder_fullname"); Age.Value = Convert.ToDouble(db_reader2.GetString("age")); Status.Text = db_reader2.GetString("status"); Parent1.Text = db_reader2.GetString("parent1_fullname"); Parent2.Text = db_reader2.GetString("parent2_fullname"); Residence1.Text = db_reader2.GetString("residence"); Residence2.Text = db_reader2.GetString("residence2"); Sacrament.Text = db_reader2.GetString("sacrament"); CauseOfDeath.Text = db_reader2.GetString("cause_of_death"); PlaceOfInterment.Text = db_reader2.GetString("place_of_interment"); Stipend.Value = Convert.ToDouble(db_reader2.GetString("stipend")); Minister.Text = db_reader2.GetString("minister"); Remarks.Text = db_reader2.GetString("remarks"); } } } } } } } } } Suggestions1.Visibility = Visibility.Hidden; Suggestions2.Visibility = Visibility.Hidden; Suggestions3.Visibility = Visibility.Hidden; Suggestions4.Visibility = Visibility.Hidden; Suggestions5.Visibility = Visibility.Hidden; Suggestions6.Visibility = Visibility.Hidden; }
/// <summary> /// Inserts the request to the database. /// </summary> private int InsertEntry() { int ret = 0; dbman = new DBConnectionManager(); using (conn = new MySqlConnection(dbman.GetConnStr())) { conn.Open(); if (conn.State == ConnectionState.Open) { using (MySqlConnection conn2 = new MySqlConnection(dbman.GetConnStr())) { conn2.Open(); MySqlCommand cmd = conn2.CreateCommand(); cmd.CommandText = "SELECT * FROM records, registers WHERE records.record_id = @rid AND records.book_number = registers.book_number LIMIT 1;"; cmd.Parameters.AddWithValue("@rid", recordID); cmd.Prepare(); using (MySqlDataReader db_reader = cmd.ExecuteReader()) { while (db_reader.Read()) { if (db_reader.GetString("status") == "Archived") { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); try { MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = "UPDATE records SET page_number = @page_number, entry_number = @entry_number, record_date = @record_date, recordholder_fullname = @recordholder_fullname, parent1_fullname = @parent1_fullname, parent2_fullname = @parent2_fullname WHERE record_id = @record_id;"; cmd2.Parameters.AddWithValue("@record_id", recordID); cmd2.Parameters.AddWithValue("@page_number", pageNum); cmd2.Parameters.AddWithValue("@entry_number", entryNum); cmd2.Parameters.AddWithValue("@record_date", deathDate); cmd2.Parameters.AddWithValue("@recordholder_fullname", fullName); cmd2.Parameters.AddWithValue("@parent1_fullname", parent1); cmd2.Parameters.AddWithValue("@parent2_fullname", parent2); cmd2.Prepare(); int stat_code = cmd2.ExecuteNonQuery(); conn3.Close(); conn3.Open(); string path = @"\archive.db"; pmsutil = new PMSUtil(); if (pmsutil.CheckArchiveDrive(path) != "dc") { SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder { FailIfMissing = true, DataSource = pmsutil.CheckArchiveDrive(path) }; //Copy the selected register's record to the archive drive using (SQLiteConnection connection = new SQLiteConnection(connectionString.ToString())) { // open the connection: connection.Open(); SQLiteCommand command = new SQLiteCommand(null, connection) { CommandText = "UPDATE burial_records SET burial_date = @burial_date, age = @age, status = @status, residence = @residence, residence2 = @residence2, sacrament = @sacrament, cause_of_death = @cause_of_death, place_of_interment = @place_of_interment, stipend = @stipend, minister = @minister, remarks = @remarks WHERE record_id = @record_id;" }; command.Parameters.Add(new SQLiteParameter("@record_id", recordID)); command.Parameters.Add(new SQLiteParameter("@record_id", recordID)); command.Parameters.Add(new SQLiteParameter("@burial_date", burialDate)); command.Parameters.Add(new SQLiteParameter("@age", age)); command.Parameters.Add(new SQLiteParameter("@status", status)); command.Parameters.Add(new SQLiteParameter("@residence", residence1)); command.Parameters.Add(new SQLiteParameter("@residence2", residence2)); command.Parameters.Add(new SQLiteParameter("@sacrament", sacrament)); command.Parameters.Add(new SQLiteParameter("@cause_of_death", causeOfDeath)); command.Parameters.Add(new SQLiteParameter("@place_of_interment", intermentPlace)); command.Parameters.Add(new SQLiteParameter("@stipend", stipend)); command.Parameters.Add(new SQLiteParameter("@minister", minister)); command.Parameters.Add(new SQLiteParameter("@remarks", remarks)); // Call Prepare after setting the Commandtext and Parameters. command.Prepare(); command.ExecuteNonQuery(); } } else { } conn3.Close(); string tmp = pmsutil.LogRecord(recordID, "LOGC-02"); } catch (MySqlException ex) { Console.WriteLine("Error: {0}", ex.ToString()); return(0); } } } else { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); try { string recID = pmsutil.GenRecordID(); MySqlCommand cmd2 = dbman.DBConnect().CreateCommand(); cmd2.CommandText = "UPDATE records SET page_number = @page_number, entry_number = @entry_number, record_date = @record_date, recordholder_fullname = @recordholder_fullname, parent1_fullname = @parent1_fullname, parent2_fullname = @parent2_fullname WHERE record_id = @record_id;"; cmd2.Prepare(); cmd2.Parameters.AddWithValue("@record_id", recordID); cmd2.Parameters.AddWithValue("@page_number", pageNum); cmd2.Parameters.AddWithValue("@entry_number", entryNum); cmd2.Parameters.AddWithValue("@record_date", deathDate); cmd2.Parameters.AddWithValue("@recordholder_fullname", fullName); cmd2.Parameters.AddWithValue("@parent1_fullname", parent1); cmd2.Parameters.AddWithValue("@parent2_fullname", parent2); int stat_code = cmd2.ExecuteNonQuery(); conn3.Close(); conn3.Open(); cmd2 = dbman.DBConnect().CreateCommand(); cmd2.CommandText = "UPDATE burial_records SET burial_date = @burial_date, age = @age, status = @status, residence = @residence, residence2 = @residence2, sacrament = @sacrament, cause_of_death = @cause_of_death, place_of_interment = @place_of_interment, stipend = @stipend, minister = @minister, remarks = @remarks WHERE record_id = @record_id;"; cmd2.Prepare(); cmd2.Parameters.AddWithValue("@record_id", recordID); cmd2.Parameters.AddWithValue("@burial_date", burialDate); cmd2.Parameters.AddWithValue("@age", age); cmd2.Parameters.AddWithValue("@status", status); cmd2.Parameters.AddWithValue("@residence", residence1); cmd2.Parameters.AddWithValue("@residence2", residence2); cmd2.Parameters.AddWithValue("@sacrament", sacrament); cmd2.Parameters.AddWithValue("@cause_of_death", causeOfDeath); cmd2.Parameters.AddWithValue("@place_of_interment", intermentPlace); cmd2.Parameters.AddWithValue("@stipend", stipend); cmd2.Parameters.AddWithValue("@minister", minister); cmd2.Parameters.AddWithValue("@remarks", remarks); stat_code = cmd2.ExecuteNonQuery(); conn3.Close(); string tmp = pmsutil.LogRecord(recordID, "LOGC-02"); return(stat_code); } catch (MySqlException ex) { Console.WriteLine("Error: {0}", ex.ToString()); return(0); } } } } } } } return(ret); } }
public ViewRemarksWindow(string recordID) { InitializeComponent(); dbman = new DBConnectionManager(); using (conn = new MySqlConnection(dbman.GetConnStr())) { conn.Open(); if (conn.State == ConnectionState.Open) { using (MySqlConnection conn2 = new MySqlConnection(dbman.GetConnStr())) { conn2.Open(); MySqlCommand cmd = conn2.CreateCommand(); cmd.CommandText = "SELECT * FROM registers, records WHERE records.record_id = @rid AND records.book_number = registers.book_number LIMIT 1;"; cmd.Parameters.AddWithValue("@rid", recordID); cmd.Prepare(); using (MySqlDataReader db_reader = cmd.ExecuteReader()) { while (db_reader.Read()) { if (db_reader.GetString("status") == "Archived") { pmsutil = new PMSUtil(); string archiveDrive = "init"; string path = @"\archive.db"; if (pmsutil.CheckArchiveDrive(path) != "dc") { archiveDrive = pmsutil.CheckArchiveDrive(path); SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder { FailIfMissing = true, DataSource = archiveDrive }; using (SQLiteConnection connection = new SQLiteConnection(connectionString.ToString())) { // open the connection: connection.Open(); string stm = "SELECT * FROM confirmation_records, matrimonial_records, baptismal_records, burial_records WHERE burial_records.record_id ='" + recordID + "' OR baptismal_records.record_id ='" + recordID + "' OR matrimonial_records.record_id ='" + recordID + "' OR confirmation_records.record_id ='" + recordID + "' LIMIT 1;"; using (SQLiteCommand cmdx = new SQLiteCommand(stm, connection)) { using (SQLiteDataReader rdr = cmdx.ExecuteReader()) { while (rdr.Read()) { RemarksContainer.Text = rdr["remarks"].ToString(); } } } } } else { RemarksContainer.Text = "Archive drive not connected."; } } else { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = "SELECT * FROM confirmation_records, matrimonial_records, baptismal_records, burial_records WHERE burial_records.record_id = @record_id OR baptismal_records.record_id = @record_id OR matrimonial_records.record_id = @record_id OR confirmation_records.record_id = @record_id LIMIT 1;"; cmd2.Parameters.AddWithValue("@record_id", recordID); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { while (db_reader2.Read()) { RemarksContainer.Text = db_reader2.GetString("remarks"); } } } } } } } } } //if (dbman.DBConnect().State == ConnectionState.Open) //{ // MySqlCommand cmd = dbman.DBConnect().CreateCommand(); // cmd.CommandText = "SELECT * FROM baptismal_records, matrimonial_records, confirmation_records, burial_records WHERE burial_records.record_id = @record_id OR baptismal_records.record_id = @record_id OR matrimonial_records.record_id = @record_id OR confirmation_records.record_id = @record_id LIMIT 1;"; // cmd.Parameters.AddWithValue("@record_id", recordID); // cmd.Prepare(); // MySqlDataReader db_reader = cmd.ExecuteReader(); // while (db_reader.Read()) // { // RemarksContainer.Text = db_reader.GetString("remarks"); // } // //close Connection // dbman.DBClose(); //} //else //{ //} }
private void SyncEntries() { items = new ObservableCollection <DirEntry>(); items_final = new ObservableCollection <DirEntry>(); ComboBoxItem ci = (ComboBoxItem)ItemsPerPage.SelectedItem; int itemsPerPage = Convert.ToInt32(ci.Content); int page = 1; int count = 0; string name = ""; string bdate = ""; string intermentPlace = ""; string archiveDrive = "init"; string path = @"\archive.db"; dbman = new DBConnectionManager(); using (conn = new MySqlConnection(dbman.GetConnStr())) { conn.Open(); if (conn.State == ConnectionState.Open) { MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT * FROM records, burial_records, burial_directory WHERE burial_records.record_id = records.record_id AND burial_directory.record_id = burial_records.record_id AND (records.recordholder_fullname LIKE @query OR records.parent1_fullname LIKE @query OR records.parent2_fullname LIKE @query OR burial_records.place_of_interment LIKE @query OR burial_records.cause_of_death);"; cmd.Parameters.AddWithValue("@query", "%" + _query + "%"); MySqlDataReader db_reader = cmd.ExecuteReader(); while (db_reader.Read()) { Console.WriteLine("here"); if (IsArchived(db_reader.GetInt32("book_number")) == true) { pmsutil = new PMSUtil(); if (pmsutil.CheckArchiveDrive(path) != "dc") { archiveDrive = pmsutil.CheckArchiveDrive(path); SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder { FailIfMissing = true, DataSource = archiveDrive }; using (SQLiteConnection connection = new SQLiteConnection(connectionString.ToString())) { // open the connection: connection.Open(); string stm = "SELECT * FROM burial_records WHERE record_id='" + db_reader.GetString("record_id") + "';"; using (SQLiteCommand cmdx = new SQLiteCommand(stm, connection)) { using (SQLiteDataReader rdr = cmdx.ExecuteReader()) { while (rdr.Read()) { DateTime dateOfBurial = Convert.ToDateTime(rdr["burial_date"].ToString()); name = db_reader.GetString("recordholder_fullname"); bdate = dateOfBurial.ToString("MMM dd, yyyy"); intermentPlace = rdr["place_of_interment"].ToString(); } } } } } else { name = db_reader.GetString("recordholder_fullname"); bdate = "---"; intermentPlace = "---"; } } else { name = db_reader.GetString("recordholder_fullname"); bdate = DateTime.Parse(db_reader.GetString("burial_date")).ToString("MMM dd, yyyy"); intermentPlace = db_reader.GetString("place_of_interment"); } items.Add(new DirEntry() { DirectoryID = db_reader.GetString("record_id"), RecordID = db_reader.GetString("record_id"), Lot = db_reader.GetString("lot"), Plot = db_reader.GetString("plot"), FName = name, PlaceOfInterment = intermentPlace, BurialDate = bdate, Page = page }); count++; if (count == itemsPerPage) { page++; count = 0; } } } } int temp = 1; foreach (var cur in items) { if (cur.Page == CurrentPage.Value) { items_final.Add(new DirEntry() { No = temp, DirectoryID = cur.DirectoryID, RecordID = cur.RecordID, Lot = cur.Lot, Plot = cur.Plot, FName = cur.FName, PlaceOfInterment = cur.PlaceOfInterment, BurialDate = cur.BurialDate, Page = cur.Page }); temp++; } } DirectoryItemsContainer.Items.Refresh(); DirectoryItemsContainer.ItemsSource = items_final; DirectoryItemsContainer.Items.Refresh(); CurrentPage.Maximum = page; }
public EditConfirmationRecordEntryWindow(string targRecord) { pmsutil = new PMSUtil(); recordID = targRecord; InitializeComponent(); Stipend.Value = FetchConfirmationStipend(); dbman = new DBConnectionManager(); using (conn = new MySqlConnection(dbman.GetConnStr())) { conn.Open(); if (conn.State == ConnectionState.Open) { using (MySqlConnection conn2 = new MySqlConnection(dbman.GetConnStr())) { conn2.Open(); MySqlCommand cmd = conn2.CreateCommand(); cmd.CommandText = "SELECT * FROM records, registers WHERE records.record_id = @rid AND records.book_number = registers.book_number LIMIT 1;"; cmd.Parameters.AddWithValue("@rid", recordID); cmd.Prepare(); using (MySqlDataReader db_reader = cmd.ExecuteReader()) { while (db_reader.Read()) { if (db_reader.GetString("status") == "Archived") { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = "SELECT * FROM records WHERE records.record_id = @rid ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@rid", recordID); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { string archiveDrive = "init"; string path = @"\archive.db"; while (db_reader2.Read()) { if (pmsutil.CheckArchiveDrive(path) != "dc") { archiveDrive = pmsutil.CheckArchiveDrive(path); SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder { FailIfMissing = true, DataSource = archiveDrive }; using (SQLiteConnection connection = new SQLiteConnection(connectionString.ToString())) { // open the connection: connection.Open(); string stm = "SELECT * FROM confirmation_records WHERE record_id='" + db_reader2.GetString("record_id") + "';"; using (SQLiteCommand cmdx = new SQLiteCommand(stm, connection)) { using (SQLiteDataReader rdr = cmdx.ExecuteReader()) { while (rdr.Read()) { EntryNum.Value = db_reader2.GetInt32("entry_number"); PageNum.Value = db_reader2.GetInt32("page_number"); ConfirmationDate.Text = db_reader2.GetString("record_date"); FullName.Text = db_reader2.GetString("recordholder_fullname"); Age.Value = Convert.ToInt32(rdr["age"]); Parish.Text = rdr["parochia"].ToString(); Province.Text = rdr["province"].ToString(); PlaceOfBaptism.Text = rdr["place_of_baptism"].ToString(); Parent1.Text = db_reader2.GetString("parent1_fullname"); Parent2.Text = db_reader2.GetString("parent2_fullname"); Sponsor1.Text = rdr["sponsor"].ToString(); Sponsor2.Text = rdr["sponsor2"].ToString(); Stipend.Value = Convert.ToDouble(rdr["stipend"]); Minister.Text = rdr["minister"].ToString(); Remarks.Text = rdr["remarks"].ToString(); } } } } } else { archiveDrive = "init"; EntryNum.Value = db_reader2.GetInt32("entry_number"); PageNum.Value = db_reader2.GetInt32("page_number"); ConfirmationDate.Text = db_reader2.GetString("record_date"); FullName.Text = db_reader2.GetString("recordholder_fullname"); Age.Value = db_reader2.GetInt32("age"); Parish.Text = db_reader2.GetString("parochia"); Province.Text = db_reader2.GetString("province"); PlaceOfBaptism.Text = db_reader2.GetString("place_of_baptism"); Parent1.Text = db_reader2.GetString("parent1_fullname"); Parent2.Text = db_reader2.GetString("parent2_fullname"); Sponsor1.Text = db_reader2.GetString("sponsor"); Sponsor2.Text = db_reader2.GetString("sponsor2"); Stipend.Value = db_reader2.GetDouble("stipend"); Minister.Text = db_reader2.GetString("minister"); Remarks.Text = db_reader2.GetString("remarks"); } } } } } else { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = "SELECT * FROM records, confirmation_records WHERE records.record_id = @rid AND records.record_id = confirmation_records.record_id ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@rid", recordID); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { while (db_reader2.Read()) { EntryNum.Value = db_reader2.GetInt32("entry_number"); PageNum.Value = db_reader2.GetInt32("page_number"); ConfirmationDate.Text = db_reader2.GetString("record_date"); FullName.Text = db_reader2.GetString("recordholder_fullname"); Age.Value = db_reader2.GetInt32("age"); Parish.Text = db_reader2.GetString("parochia"); Province.Text = db_reader2.GetString("province"); PlaceOfBaptism.Text = db_reader2.GetString("place_of_baptism"); Parent1.Text = db_reader2.GetString("parent1_fullname"); Parent2.Text = db_reader2.GetString("parent2_fullname"); Sponsor1.Text = db_reader2.GetString("sponsor"); Sponsor2.Text = db_reader2.GetString("sponsor2"); Stipend.Value = db_reader2.GetDouble("stipend"); Minister.Text = db_reader2.GetString("minister"); } } } } } } } } } Suggestions1.Visibility = Visibility.Hidden; Suggestions2.Visibility = Visibility.Hidden; Suggestions3.Visibility = Visibility.Hidden; Suggestions4.Visibility = Visibility.Hidden; }
/// <summary> /// Creates the AddRequestForm Window and Initializes DB Param. /// </summary> public PrintBaptismalRecordEntryWindow(string targRecord) { pmsutil = new PMSUtil(); recordID = targRecord; InitializeComponent(); GetResidingPriests(); CheckAccess(targRecord); PrintingFee.Value = Convert.ToDouble(pmsutil.GetPrintFee("Baptismal")); dbman = new DBConnectionManager(); using (conn = new MySqlConnection(dbman.GetConnStr())) { conn.Open(); if (conn.State == ConnectionState.Open) { using (MySqlConnection conn2 = new MySqlConnection(dbman.GetConnStr())) { conn2.Open(); MySqlCommand cmd = conn2.CreateCommand(); cmd.CommandText = "SELECT * FROM records, registers WHERE records.record_id = @rid AND records.book_number = registers.book_number LIMIT 1;"; cmd.Parameters.AddWithValue("@rid", recordID); cmd.Prepare(); using (MySqlDataReader db_reader = cmd.ExecuteReader()) { while (db_reader.Read()) { if (db_reader.GetString("status") == "Archived") { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = "SELECT * FROM records WHERE records.record_id = @rid ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@rid", recordID); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { string archiveDrive = "init"; string path = @"\archive.db"; while (db_reader2.Read()) { if (pmsutil.CheckArchiveDrive(path) != "dc") { archiveDrive = pmsutil.CheckArchiveDrive(path); SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder { FailIfMissing = true, DataSource = archiveDrive }; using (SQLiteConnection connection = new SQLiteConnection(connectionString.ToString())) { // open the connection: connection.Open(); string stm = "SELECT * FROM baptismal_records WHERE record_id='" + db_reader2.GetString("record_id") + "';"; using (SQLiteCommand cmdx = new SQLiteCommand(stm, connection)) { using (SQLiteDataReader rdr = cmdx.ExecuteReader()) { while (rdr.Read()) { App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { bookNum = db_reader.GetInt32("book_number"); EntryNum.Value = Convert.ToDouble(db_reader.GetString("entry_number")); PageNum.Value = Convert.ToDouble(db_reader.GetString("page_number")); BaptismDate.Text = db_reader.GetString("record_date"); Birthdate.Text = rdr["birthday"].ToString(); FullName.Text = db_reader.GetString("recordholder_fullname"); PlaceOfBirth.Text = rdr["place_of_birth"].ToString(); Parent1.Text = db_reader.GetString("parent1_fullname"); Parent2.Text = db_reader.GetString("parent2_fullname"); Sponsor1.Text = rdr["sponsor1"].ToString(); Sponsor2.Text = rdr["sponsor1"].ToString(); Minister.Text = rdr["minister"].ToString(); Remarks.Text = rdr["remarks"].ToString(); }); } } } } } else { } } } } } else { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = "SELECT * FROM records, baptismal_records WHERE records.record_id = @rid AND records.record_id = baptismal_records.record_id ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@rid", recordID); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { while (db_reader2.Read()) { bookNum = db_reader2.GetInt32("book_number"); EntryNum.Value = Convert.ToDouble(db_reader2.GetString("entry_number")); PageNum.Value = Convert.ToDouble(db_reader2.GetString("page_number")); BaptismDate.Text = db_reader2.GetString("record_date"); Birthdate.Text = db_reader2.GetString("birthday"); FullName.Text = db_reader2.GetString("recordholder_fullname"); PlaceOfBirth.Text = db_reader2.GetString("place_of_birth"); Parent1.Text = db_reader2.GetString("parent1_fullname"); Parent2.Text = db_reader2.GetString("parent2_fullname"); Sponsor1.Text = db_reader2.GetString("sponsor1"); Sponsor2.Text = db_reader2.GetString("sponsor2"); Minister.Text = db_reader2.GetString("minister"); Remarks.Text = db_reader2.GetString("remarks"); } } } } } } } } } Suggestions1.Visibility = Visibility.Hidden; Suggestions2.Visibility = Visibility.Hidden; }
private void SyncBaptismalEntries(int targBook, int pageNum) { records = new ObservableCollection <RecordEntryBaptismal>(); dbman = new DBConnectionManager(); using (conn = new MySqlConnection(dbman.GetConnStr())) { conn.Open(); if (conn.State == ConnectionState.Open) { using (MySqlConnection conn2 = new MySqlConnection(dbman.GetConnStr())) { conn2.Open(); MySqlCommand cmd = conn2.CreateCommand(); cmd.CommandText = "SELECT * FROM registers WHERE book_number = @book_number LIMIT 1;"; cmd.Parameters.AddWithValue("@book_number", targBook); cmd.Prepare(); using (MySqlDataReader db_reader = cmd.ExecuteReader()) { while (db_reader.Read()) { if (db_reader.GetString("status") == "Archived") { MsgArchived(); EditRegButton.IsEnabled = false; PrintRegButton.IsEnabled = false; using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = "SELECT * FROM records WHERE records.book_number = @book_number AND records.page_number = @page_number ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@book_number", targBook); cmd2.Parameters.AddWithValue("@page_number", pageNum); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { string archiveDrive = "init"; string path = @"\archive.db"; while (db_reader2.Read()) { pmsutil = new PMSUtil(); if (pmsutil.CheckArchiveDrive(path) != "dc") { archiveDrive = pmsutil.CheckArchiveDrive(path); SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder { FailIfMissing = true, DataSource = archiveDrive }; using (SQLiteConnection connection = new SQLiteConnection(connectionString.ToString())) { // open the connection: connection.Open(); string stm = "SELECT * FROM baptismal_records WHERE record_id='" + db_reader2.GetString("record_id") + "';"; using (SQLiteCommand cmdx = new SQLiteCommand(stm, connection)) { using (SQLiteDataReader rdr = cmdx.ExecuteReader()) { EditRegButton.IsEnabled = true; PrintRegButton.IsEnabled = true; while (rdr.Read()) { DateTime dateOfBirth = Convert.ToDateTime(rdr["birthday"].ToString()); App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryBaptismal() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), BaptismalYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), BaptismalDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName = db_reader2.GetString("recordholder_fullname"), BirthDate = dateOfBirth.ToString("MMM dd, yyyy"), Legitimacy = rdr["legitimacy"].ToString(), PlaceOfBirth = rdr["place_of_birth"].ToString(), Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Godparent1 = rdr["sponsor1"].ToString(), Godparent2 = rdr["sponsor2"].ToString(), Stipend = Convert.ToDouble(rdr["stipend"]), Minister = rdr["minister"].ToString() }); }); } } } } } else { archiveDrive = "init"; App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryBaptismal() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), BaptismalYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), BaptismalDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName = db_reader2.GetString("recordholder_fullname"), BirthDate = "---", Legitimacy = "---", PlaceOfBirth = "---", Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Godparent1 = "---", Godparent2 = "---", Stipend = 0, Minister = "---" }); }); } } } } } else { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = "SELECT * FROM records, baptismal_records WHERE records.book_number = @book_number AND records.page_number = @page_number AND records.record_id = baptismal_records.record_id ORDER BY records.entry_number ASC;"; cmd2.Parameters.AddWithValue("@book_number", targBook); cmd2.Parameters.AddWithValue("@page_number", pageNum); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { while (db_reader2.Read()) { App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryBaptismal() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), BaptismalYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), BaptismalDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName = db_reader2.GetString("recordholder_fullname"), BirthDate = DateTime.Parse(db_reader2.GetString("birthday")).ToString("MMM dd, yyyy"), Legitimacy = db_reader2.GetString("legitimacy"), PlaceOfBirth = db_reader2.GetString("place_of_birth"), Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Godparent1 = db_reader2.GetString("sponsor1"), Godparent2 = db_reader2.GetString("sponsor2"), Stipend = db_reader2.GetFloat("stipend"), Minister = db_reader2.GetString("minister") }); }); } } } } } } } SyncChanges(); System.Threading.Thread.Sleep(1); } else { } //close Connection conn.Close(); } }
private void AddItems(object sender, DoWorkEventArgs e) { records = new ObservableCollection <RecordEntryBaptismal>(); dbman = new DBConnectionManager(); using (conn = new MySqlConnection(dbman.GetConnStr())) { conn.Open(); if (conn.State == ConnectionState.Open) { using (MySqlConnection conn2 = new MySqlConnection(dbman.GetConnStr())) { conn2.Open(); MySqlCommand cmd = conn2.CreateCommand(); cmd.CommandText = "SELECT * FROM registers WHERE book_number = @book_number LIMIT 1;"; cmd.Parameters.AddWithValue("@book_number", bnum); cmd.Prepare(); using (MySqlDataReader db_reader = cmd.ExecuteReader()) { while (db_reader.Read()) { if (db_reader.GetString("status") == "Archived") { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = cmd_tmp; cmd2.Parameters.AddWithValue("@book_number", bnum); cmd2.Parameters.AddWithValue("@query", "%" + qry + "%"); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { string archiveDrive = "init"; string path = @"\archive.db"; while (db_reader2.Read()) { pmsutil = new PMSUtil(); if (pmsutil.CheckArchiveDrive(path) != "dc") { archiveDrive = pmsutil.CheckArchiveDrive(path); SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder { FailIfMissing = true, DataSource = archiveDrive }; using (SQLiteConnection connection = new SQLiteConnection(connectionString.ToString())) { // open the connection: connection.Open(); string stm = "SELECT * FROM confirmation_records WHERE record_id='" + db_reader2.GetString("record_id") + "';"; using (SQLiteCommand cmdx = new SQLiteCommand(stm, connection)) { using (SQLiteDataReader rdr = cmdx.ExecuteReader()) { while (rdr.Read()) { App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryBaptismal() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), BaptismalYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), BaptismalDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName = db_reader2.GetString("recordholder_fullname"), BirthDate = DateTime.Parse(rdr["birthday"].ToString()).ToString("MMM dd, yyyy"), Legitimacy = rdr["legitimacy"].ToString(), PlaceOfBirth = rdr["place_of_birth"].ToString(), Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Godparent1 = rdr["sponsor1"].ToString(), Godparent2 = rdr["sponsor2"].ToString(), Stipend = Convert.ToDouble(rdr["stipend"]), Minister = rdr["minister"].ToString() }); }); } } } } } else { archiveDrive = "init"; App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryBaptismal() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), BaptismalYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), BaptismalDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName = db_reader2.GetString("recordholder_fullname"), BirthDate = "---", Legitimacy = "---", PlaceOfBirth = "---", Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Godparent1 = "---", Godparent2 = "---", Stipend = 0, Minister = "---" }); }); } } } } } else { using (MySqlConnection conn3 = new MySqlConnection(dbman.GetConnStr())) { conn3.Open(); MySqlCommand cmd2 = conn3.CreateCommand(); cmd2.CommandText = cmd_tmp; cmd2.Parameters.AddWithValue("@book_number", bnum); cmd2.Parameters.AddWithValue("@query", "%" + qry + "%"); cmd2.Prepare(); using (MySqlDataReader db_reader2 = cmd2.ExecuteReader()) { while (db_reader2.Read()) { App.Current.Dispatcher.Invoke((Action) delegate // <--- HERE { records.Add(new RecordEntryBaptismal() { RecordID = db_reader2.GetString("record_id"), EntryNumber = db_reader2.GetInt32("entry_number"), BaptismalYear = DateTime.Parse(db_reader2.GetString("record_date")).ToString("yyyy"), BaptismalDate = DateTime.Parse(db_reader2.GetString("record_date")).ToString("MMM dd"), FullName = db_reader2.GetString("recordholder_fullname"), BirthDate = DateTime.Parse(db_reader2.GetString("birthday")).ToString("MMM dd, yyyy"), Legitimacy = db_reader2.GetString("legitimacy"), PlaceOfBirth = db_reader2.GetString("place_of_birth"), Parent1 = db_reader2.GetString("parent1_fullname"), Parent2 = db_reader2.GetString("parent2_fullname"), Godparent1 = db_reader2.GetString("sponsor1"), Godparent2 = db_reader2.GetString("sponsor2"), Stipend = db_reader2.GetFloat("stipend"), Minister = db_reader2.GetString("minister") }); }); } } } } } } } (sender as BackgroundWorker).ReportProgress(0); System.Threading.Thread.Sleep(1); } else { } //close Connection //conn.Close(); } }