/// <summary>
/// Inserts the request to the database.
/// </summary>
private int InsertEntry()
{
dbman = new DBConnectionManager();
//TODO
try
{
string recID = pmsutil.GenRecordID();
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText =
"INSERT INTO records(record_id, book_number, page_number, entry_number, record_date, recordholder_fullname, parent1_fullname, parent2_fullname)" +
"VALUES(@record_id, @book_number, @page_number, @entry_number, @record_date, @recordholder_fullname, @parent1_fullname, @parent2_fullname)";
cmd.Prepare();
cmd.Parameters.AddWithValue("@record_id", recID);
cmd.Parameters.AddWithValue("@book_number", bookNum);
cmd.Parameters.AddWithValue("@page_number", pageNum);
cmd.Parameters.AddWithValue("@entry_number", entryNum);
cmd.Parameters.AddWithValue("@record_date", marriageDate);
cmd.Parameters.AddWithValue("@recordholder_fullname", fullName1);
cmd.Parameters.AddWithValue("@parent1_fullname", parent1);
cmd.Parameters.AddWithValue("@parent2_fullname", parent2);
int stat_code = cmd.ExecuteNonQuery();
dbman.DBClose();
//Phase 2
cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText =
"INSERT INTO matrimonial_records(record_id, recordholder2_fullname, parent1_fullname2, parent2_fullname2, status1, status2, age1, age2, place_of_origin1, place_of_origin2, residence1, residence2, witness1, witness2, witness1address, witness2address, stipend, minister, remarks)" +
"VALUES(@record_id, @recordholder2_fullname, @parent1_fullname2, @parent2_fullname2, @status1, @status2, @age1, @age2, @place_of_origin1, @place_of_origin2, @residence1, @residence2, @witness1, @witness2, @witness1address, @witness2address, @stipend, @minister, @remarks)";
cmd.Prepare();
cmd.Parameters.AddWithValue("@record_id", recID);
cmd.Parameters.AddWithValue("@recordholder2_fullname", fullName2);
cmd.Parameters.AddWithValue("@parent1_fullname2", parent3);
cmd.Parameters.AddWithValue("@parent2_fullname2", parent4);
cmd.Parameters.AddWithValue("@status1", status1);
cmd.Parameters.AddWithValue("@status2", status2);
cmd.Parameters.AddWithValue("@age1", age1);
cmd.Parameters.AddWithValue("@age2", age2);
cmd.Parameters.AddWithValue("@place_of_origin1", hometown1);
cmd.Parameters.AddWithValue("@place_of_origin2", hometown2);
cmd.Parameters.AddWithValue("@residence1", residence1);
cmd.Parameters.AddWithValue("@residence2", residence2);
cmd.Parameters.AddWithValue("@witness1", sponsor1);
cmd.Parameters.AddWithValue("@witness2", sponsor2);
cmd.Parameters.AddWithValue("@witness1address", residence3);
cmd.Parameters.AddWithValue("@witness2address", residence4);
cmd.Parameters.AddWithValue("@stipend", stipend);
cmd.Parameters.AddWithValue("@minister", minister);
cmd.Parameters.AddWithValue("@remarks", remarks);
stat_code = cmd.ExecuteNonQuery();
dbman.DBClose();
string tmp = pmsutil.LogRecord(recID, "LOGC-01");
return(stat_code);
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
return(0);
}
}
private void ArchiveItems(object sender, DoWorkEventArgs e)
{
string uid = Application.Current.Resources["uid"].ToString();
string[] dt = pmsutil.GetServerDateTime().Split(null);
cDate = Convert.ToDateTime(dt[0]);
cTime = DateTime.Parse(dt[1] + " " + dt[2]);
curDate = cDate.ToString("yyyy-MM-dd");
curTime = cTime.ToString("HH:mm:ss");
dbman = new DBConnectionManager();
pmsutil = new PMSUtil();
//TODO
try
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText =
"INSERT INTO archives(book_number, date_archived, time_archived, archived_by)" +
"VALUES(@book_number, @date_archived, @time_archived, @archived_by)";
cmd.Parameters.AddWithValue("@book_number", bookNum);
cmd.Parameters.AddWithValue("@date_archived", curDate);
cmd.Parameters.AddWithValue("@time_archived", curTime);
cmd.Parameters.AddWithValue("@archived_by", uid);
cmd.Prepare();
int stat_code = cmd.ExecuteNonQuery();
dbman = new DBConnectionManager();
//TODO
try
{
//Phase 1.2
cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText =
"UPDATE registers SET status = @status WHERE book_number = @book_number;";
cmd.Parameters.AddWithValue("@book_number", bookNum);
cmd.Parameters.AddWithValue("@status", "Archived");
cmd.Prepare();
stat_code = cmd.ExecuteNonQuery();
dbman.DBClose();
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
//return 0;
}
stat_code2 = stat_code;
dbman.DBClose();
Phase2();
//return stat_code;
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
//return 0;
}
}
private void LoginButton_Click(object sender, RoutedEventArgs e)
{
if (CheckInputs() == true)
{
DBConnectionManager dbman = new DBConnectionManager();
LoginSpinner.Visibility = Visibility.Visible;
try
{
if (dbman.DBConnect().State == ConnectionState.Open)
{
StatusLabel.Content = "Invalid credentials. Please try again.";
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
string username = UsernameField.Text;
string password = PasswordField.Password;
cmd.CommandText = "SELECT * FROM accounts WHERE user_name = @username";
cmd.Parameters.AddWithValue("@username", username);
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
if (username == db_reader.GetString("user_name") && SecurePasswordHasher.Verify(password, db_reader.GetString("pass_key")) == true)
{
Application.Current.Resources["uid"] = db_reader.GetString("account_id");
Application.Current.Resources["priv"] = db_reader.GetString("account_type");
dbman.DBClose();
this.Close();
}
}
}
else
{
StatusLabel.Content = "DB Connection Failed!";
}
}
catch (MySqlException ex)
{
StatusLabel.Content = "Error " + ex.Message;
}
LoginSpinner.Visibility = Visibility.Hidden;
dbman.DBClose();
}
else
{
}
}
/// <summary>
/// Inserts the request to the database.
/// </summary>
private int InsertEntry()
{
dbman = new DBConnectionManager();
//TODO
try
{
string recID = pmsutil.GenRecordID();
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText =
"INSERT INTO records(record_id, book_number, page_number, entry_number, record_date, recordholder_fullname, parent1_fullname, parent2_fullname)" +
"VALUES(@record_id, @book_number, @page_number, @entry_number, @record_date, @recordholder_fullname, @parent1_fullname, @parent2_fullname)";
cmd.Prepare();
cmd.Parameters.AddWithValue("@record_id", recID);
cmd.Parameters.AddWithValue("@book_number", bookNum);
cmd.Parameters.AddWithValue("@page_number", pageNum);
cmd.Parameters.AddWithValue("@entry_number", entryNum);
cmd.Parameters.AddWithValue("@record_date", confirmationDate);
cmd.Parameters.AddWithValue("@recordholder_fullname", fullName);
cmd.Parameters.AddWithValue("@parent1_fullname", parent1);
cmd.Parameters.AddWithValue("@parent2_fullname", parent2);
int stat_code = cmd.ExecuteNonQuery();
dbman.DBClose();
//Phase 2
cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText =
"INSERT INTO confirmation_records(record_id, age, parochia, province, place_of_baptism, sponsor, sponsor2, stipend, minister, remarks)" +
"VALUES(@record_id, @age, @parish, @province, @place_of_baptism, @sponsor, @sponsor2, @stipend, @minister, @remarks)";
cmd.Prepare();
cmd.Parameters.AddWithValue("@record_id", recID);
cmd.Parameters.AddWithValue("@age", age);
cmd.Parameters.AddWithValue("@parish", parish);
cmd.Parameters.AddWithValue("@province", province);
cmd.Parameters.AddWithValue("@place_of_baptism", baptismPlace);
cmd.Parameters.AddWithValue("@sponsor", sponsor1);
cmd.Parameters.AddWithValue("@sponsor2", sponsor2);
cmd.Parameters.AddWithValue("@stipend", stipend);
cmd.Parameters.AddWithValue("@minister", minister);
cmd.Parameters.AddWithValue("@remarks", remarks);
stat_code = cmd.ExecuteNonQuery();
dbman.DBClose();
string tmp = pmsutil.LogRecord(recID, "LOGC-01");
return(stat_code);
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
return(0);
}
}
private void SaveButton_Click2(object sender, RoutedEventArgs e)
{
string uid = Application.Current.Resources["uid"].ToString();
string fname = NameTextbox.Text;
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
//TODO
try
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText =
"UPDATE accounts_info SET name = @fname WHERE account_id = @account_id;";
cmd.Prepare();
cmd.Parameters.AddWithValue("@account_id", uid);
cmd.Parameters.AddWithValue("@fname", fname);
int stat_code = cmd.ExecuteNonQuery();
//string tmp = pmsutil.LogRecord(recordID, "LOGC-02");
InfoArea2.Foreground = new SolidColorBrush(Colors.Green);
InfoArea2.Content = "Name successfully changed!";
//close Connection
dbman.DBClose();
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
}
}
else
{
}
}
private void FetchATypes()
{
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT appointment_type, custom FROM appointment_types;";
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
if (db_reader.GetInt32("custom") == 1)
{
MassType.Items.Add(db_reader.GetString("appointment_type"));
}
else
{
EventServiceType.Items.Add(db_reader.GetString("appointment_type"));
}
}
//close Connection
dbman.DBClose();
}
else
{
}
}
private void CreateAppointment_Click(object sender, RoutedEventArgs e)
{
if (CheckInputs() == true)
{
string selTime = SelectedTime.Text;
if (IsAvailable(DateTime.Parse(SelectedDate.Content.ToString()).ToString("yyyy-MM-dd"), DateTime.Parse(selTime).ToString("HH:mm:ss"), GetPriest(AssignedPriest.Text)) == false)
{
MsgNotAvailable(DateTime.Parse(SelectedDate.Content.ToString()).ToString("MMM dd, yyyy"), DateTime.Parse(selTime).ToString("HH:mm tt"), AssignedPriest.Text);
this.Close();
}
else
{
dbman = new DBConnectionManager();
pmsutil = new PMSUtil();
//TODO
try
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText =
"UPDATE appointments SET appointment_date = @appointment_date, appointment_time = @appointment_time, appointment_type = @appointment_type, requested_by = @requested_by, placed_by = @placed_by, remarks = @remarks, status = @status, assigned_priest = @a_priest, venue = @venue WHERE appointment_id = @aid;";
cmd.Parameters.AddWithValue("@aid", _aid);
cmd.Parameters.AddWithValue("@appointment_date", DateTime.Parse(SelectedDate.Content.ToString()).ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@appointment_time", DateTime.Parse(SelectedTime.Text).ToString("HH:mm:ss"));
cmd.Parameters.AddWithValue("@appointment_type", GetATypeID(TypeHolder.Content.ToString()));
cmd.Parameters.AddWithValue("@requested_by", RequestedBy.Text);
cmd.Parameters.AddWithValue("@placed_by", Application.Current.Resources["uid"].ToString());
cmd.Parameters.AddWithValue("@remarks", Remarks.Text);
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@a_priest", GetPriest(AssignedPriest.Text));
cmd.Parameters.AddWithValue("@venue", Venue.Text);
cmd.Prepare();
int stat_code = cmd.ExecuteNonQuery();
dbman.DBClose();
if (stat_code > 0)
{
//Resync Calendar
//PMS.UIComponents.Calendar cal = new UIComponents.Calendar();
//Appointments.app.CalendarHolder.Children.Clear();
//Appointments.app.CalendarHolder.Children.Add(cal);
this.Close();
MsgSuccess();
}
else
{
MsgFail();
}
string tmp = pmsutil.LogScheduling(_aid, "LOGC-02");
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
}
}
}
else
{
}
}
/// <summary>
/// Inserts the request to the database.
/// </summary>
private int UpdateTransaction()
{
string uid = Application.Current.Resources["uid"].ToString();
string[] dt = pmsutil.GetServerDateTime().Split(null);
cDate = Convert.ToDateTime(dt[0]);
cTime = DateTime.Parse(dt[1] + " " + dt[2]);
curDate = cDate.ToString("yyyy-MM-dd");
curTime = cTime.ToString("HH:mm:ss");
dbman = new DBConnectionManager();
try
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText =
"UPDATE transactions SET status = @status, completion_date = @completion_date, completion_time = @completion_time, completed_by = @completed_by WHERE target_id = @aid;";
cmd.Parameters.AddWithValue("@aid", aid);
cmd.Parameters.AddWithValue("@status", "Cancelled");
cmd.Parameters.AddWithValue("@completion_date", cDate);
cmd.Parameters.AddWithValue("@completion_time", cTime);
cmd.Parameters.AddWithValue("@completed_by", uid);
cmd.Prepare();
int stat_code = cmd.ExecuteNonQuery();
dbman.DBClose();
return(stat_code);
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
return(0);
}
}
private string GetAType(string tid)
{
string ret = "";
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT appointment_type FROM appointment_types WHERE type_id = @tid;";
cmd.Parameters.AddWithValue("@tid", tid);
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
ret = db_reader.GetString("appointment_type");
}
//close Connection
dbman.DBClose();
}
else
{
ret = "";
}
return(ret);
}
private void FetchBookEntryNum()
{
int ret = 0;
PageNum.Value = vre.Page.Value;
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT entry_number FROM records WHERE book_number = @bnum AND page_number = @pnum;";
cmd.Parameters.AddWithValue("@bnum", bookNum);
cmd.Parameters.AddWithValue("@pnum", vre.Page.Value);
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
ret = Convert.ToInt32(db_reader.GetString("entry_number"));
}
//close Connection
dbman.DBClose();
}
else
{
ret = 0;
}
EntryNum.Value = ret + 1;
}
private string GetStatus(string aid)
{
string ret = "";
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT status FROM transactions WHERE target_id = @aid LIMIT 1;";
cmd.Parameters.AddWithValue("@aid", aid);
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
ret = db_reader.GetString("status");
}
//close Connection
dbman.DBClose();
}
else
{
ret = "";
}
return(ret);
}
internal int CheckIfArchived(int bookNum)
{
int returnVal = 0;
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT COUNT(book_number) FROM archives WHERE book_number = @book_number;";
cmd.Parameters.AddWithValue("@book_number", bookNum);
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
if (db_reader.GetInt32("COUNT(book_number)") > 0)
{
//Archived
returnVal = 1;
}
else
{
//Not Archived
returnVal = 2;
}
}
//close Connection
dbman.DBClose();
}
return(returnVal);
}
internal string GetRecordName(string rid)
{
string ret = "";
dbman2 = new DBConnectionManager();
if (dbman2.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd2 = dbman2.DBConnect().CreateCommand();
cmd2.CommandText = "SELECT * FROM records WHERE record_id = @record_id LIMIT 1;";
cmd2.Parameters.AddWithValue("@record_id", rid);
cmd2.Prepare();
MySqlDataReader db_reader2 = cmd2.ExecuteReader();
while (db_reader2.Read())
{
ret = db_reader2.GetString("recordholder_fullname");
}
//close Connection
dbman2.DBClose();
}
else
{
ret = "";
}
return(ret);
}
private bool CheckRequirements()
{
bool ret = false;
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM records, registers WHERE registers.book_type = 'Burial' AND registers.book_number = records.book_number AND records.recordholder_fullname = @fname;";
cmd.Parameters.AddWithValue("@fname", FullName.Text);
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
if (db_reader.GetInt32("COUNT(*)") == 0)
{
ret = true;
}
}
//close Connection
dbman.DBClose();
}
else
{
ret = false;
}
return(ret);
}
private void ShowSuggestions6(object sender, System.Windows.Controls.TextChangedEventArgs e)
{
dbman = new DBConnectionManager();
PlaceOfIntermentSuggestionArea.Items.Clear();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT DISTINCT place_of_interment FROM burial_records WHERE " +
"place_of_interment LIKE @query;";
cmd.Parameters.AddWithValue("@query", "%" + PlaceOfInterment.Text + "%");
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
PlaceOfIntermentSuggestionArea.Items.Add(db_reader.GetString("place_of_interment"));
}
//close Connection
dbman.DBClose();
Suggestions6.Visibility = System.Windows.Visibility.Visible;
}
else
{
}
}
private bool IsCustom(string tid)
{
bool ret = false;
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT custom FROM appointment_types WHERE type_id = @tid;";
cmd.Parameters.AddWithValue("@tid", tid);
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
if (db_reader.GetInt32("custom") == 1)
{
ret = false;
}
else
{
ret = true;
}
}
//close Connection
dbman.DBClose();
}
else
{
ret = false;
}
return(ret);
}
/// <summary>
/// Fetches default confirmation stipend value.
/// </summary>
private int FetchBurialStipend()
{
int ret = 0;
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT key_value FROM settings WHERE key_name = 'Matrimonial Stipend';";
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
ret = Convert.ToInt32(db_reader.GetString("key_value"));
}
//close Connection
dbman.DBClose();
}
else
{
ret = 0;
}
return(ret);
}
private void ShowSuggestions4(object sender, System.Windows.Controls.TextChangedEventArgs e)
{
dbman = new DBConnectionManager();
MinisterSuggestionArea.Items.Clear();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT DISTINCT minister FROM confirmation_records WHERE " +
"minister LIKE @query;";
cmd.Parameters.AddWithValue("@query", "%" + Minister.Text + "%");
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
MinisterSuggestionArea.Items.Add(db_reader.GetString("minister"));
}
//close Connection
dbman.DBClose();
Suggestions4.Visibility = System.Windows.Visibility.Visible;
}
else
{
}
}
private string GetPriest(string pid)
{
string ret = "";
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT priest_name FROM residing_priests WHERE priest_id = @pid LIMIT 1;";
cmd.Parameters.AddWithValue("@pid", pid);
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
ret = db_reader.GetString("priest_name");
}
//close Connection
dbman.DBClose();
}
else
{
ret = "";
}
return(ret);
}
private bool IsAvailable(string adate, string atime, string apriest)
{
bool ret = false;
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM appointments WHERE assigned_priest = @apriest AND appointment_date = @adate AND appointment_time = @atime;";
cmd.Parameters.AddWithValue("apriest", apriest);
cmd.Parameters.AddWithValue("adate", adate);
cmd.Parameters.AddWithValue("atime", atime);
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
if (db_reader.GetInt32("COUNT(*)") > 0)
{
ret = false;
}
else
{
ret = true;
}
}
//close Connection
dbman.DBClose();
}
else
{
}
return(ret);
}
private string GetPriestFilters()
{
AppFilter.Items.Add("All");
string ret = "";
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT priest_name FROM residing_priests WHERE priest_name != 'NA' ORDER BY priest_name ASC";
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
AppFilter.Items.Add(db_reader.GetString("priest_name"));
}
//close Connection
dbman.DBClose();
}
else
{
ret = "";
}
return(ret);
}
private int CountAssignedServicesUnfinished(string pid)
{
int ret = 0;
dbman = new DBConnectionManager();
using (conn = new MySqlConnection(dbman.GetConnStr()))
{
conn.Open();
if (conn.State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM appointments WHERE assigned_priest = @pid AND status = 1;";
cmd.Parameters.AddWithValue("@pid", pid);
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
ret = db_reader.GetInt32("COUNT(*)");
}
//close Connection
dbman.DBClose();
}
else
{
ret += 0;
}
}
return(ret);
}
private void ShowSuggestions6(object sender, System.Windows.Controls.TextChangedEventArgs e)
{
dbman = new DBConnectionManager();
Residence4SuggestionArea.Items.Clear();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT DISTINCT witness2address FROM matrimonial_records WHERE " +
"witness2address LIKE @query;";
cmd.Parameters.AddWithValue("@query", "%" + Residence4.Text + "%");
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
Residence4SuggestionArea.Items.Add(db_reader.GetString("witness2address"));
}
//close Connection
dbman.DBClose();
Suggestions6.Visibility = System.Windows.Visibility.Visible;
}
else
{
}
}
private void CreateAppointment_Click(object sender, RoutedEventArgs e)
{
if (CheckInputs() == true)
{
string selTime = SelectedTime.Text;
if (IsAvailable(DateTime.Parse(SelectedDate.Content.ToString()).ToString("yyyy-MM-dd"), DateTime.Parse(selTime).ToString("HH:mm:ss"), GetPriest(AssignedPriest.Text)) == false)
{
MsgNotAvailable(DateTime.Parse(SelectedDate.Content.ToString()).ToString("MMM dd, yyyy"), DateTime.Parse(selTime).ToString("HH:mm tt"), AssignedPriest.Text);
this.Close();
}
else
{
dbman = new DBConnectionManager();
pmsutil = new PMSUtil();
//TODO
try
{
string apmID = pmsutil.GenAppointmentID();
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText =
"INSERT INTO appointments(appointment_id, appointment_date, appointment_time, appointment_type, requested_by, placed_by, remarks, status, assigned_priest, venue)" +
"VALUES(@appointment_id, @appointment_date, @appointment_time, @appointment_type, @requested_by, @placed_by, @remarks, @status, @a_priest, @venue)";
cmd.Parameters.AddWithValue("@appointment_id", apmID);
cmd.Parameters.AddWithValue("@appointment_date", DateTime.Parse(SelectedDate.Content.ToString()).ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@appointment_time", DateTime.Parse(selTime).ToString("HH:mm:ss"));
cmd.Parameters.AddWithValue("@appointment_type", GetATypeID(TypeHolder.Content.ToString()));
cmd.Parameters.AddWithValue("@requested_by", RequestedBy.Text);
cmd.Parameters.AddWithValue("@placed_by", Application.Current.Resources["uid"].ToString());
cmd.Parameters.AddWithValue("@remarks", Remarks.Text);
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@a_priest", GetPriest(AssignedPriest.Text));
cmd.Parameters.AddWithValue("@venue", Venue.Text);
cmd.Prepare();
int stat_code = cmd.ExecuteNonQuery();
dbman.DBClose();
if (stat_code > 0)
{
this.Close();
MsgSuccess();
}
else
{
MsgFail();
}
string tmp = pmsutil.LogScheduling(apmID, "LOGC-01");
pmsutil.InsertTransaction("Liturgical Service - " + TypeHolder.Content.ToString(), "Unpaid", apmID, Convert.ToDouble(Fee.Value));
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
}
}
}
else
{
}
}
private void SaveButton_Click1(object sender, RoutedEventArgs e)
{
dbman = new DBConnectionManager();
string uid = Application.Current.Resources["uid"].ToString();
string old_pass_key = CurrentPassword.Password;
string pass_key = SecurePasswordHasher.Hash(NewPassword1.Password);
if (NewPassword1.Password == NewPassword2.Password)
{
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT * FROM accounts WHERE account_id = @uid LIMIT 1;";
cmd.Parameters.AddWithValue("@uid", uid);
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
if (SecurePasswordHasher.Verify(old_pass_key, db_reader.GetString("pass_key")) == true)
{
//TODO
try
{
MySqlCommand cmd2 = dbman.DBConnect().CreateCommand();
cmd2.CommandText =
"UPDATE accounts SET pass_key = @pass_key WHERE account_id = @account_id;";
cmd2.Prepare();
cmd2.Parameters.AddWithValue("@account_id", uid);
cmd2.Parameters.AddWithValue("@pass_key", pass_key);
int stat_code = cmd2.ExecuteNonQuery();
//string tmp = pmsutil.LogRecord(recordID, "LOGC-02");
InfoArea1.Foreground = new SolidColorBrush(Colors.Green);
InfoArea1.Content = "Password successfully changed!";
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
}
}
else
{
InfoArea1.Foreground = new SolidColorBrush(Colors.Red);
InfoArea1.Content = "Password does not match! Please check your inputs and try again.";
}
}
//close Connection
dbman.DBClose();
}
else
{
}
}
else
{
InfoArea1.Foreground = new SolidColorBrush(Colors.Red);
InfoArea1.Content = "Password does not match! Please check your inputs and try again.";
}
}
public PrintBurialRecordEntryWindow(string targRecord)
{
pmsutil = new PMSUtil();
InitializeComponent();
recordID = targRecord;
GetResidingPriests();
CheckAccess(targRecord);
PrintingFee.Value = Convert.ToDouble(pmsutil.GetPrintFee("Burial"));
Stipend.Value = FetchBurialStipend();
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT * FROM burial_records, records WHERE records.record_id = @record_id AND records.record_id = burial_records.record_id LIMIT 1;";
cmd.Parameters.AddWithValue("@record_id", targRecord);
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
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 = Convert.ToDouble(db_reader.GetString("age"));
FullName.Text = db_reader.GetString("recordholder_fullname");
Age.Value = Convert.ToDouble(db_reader.GetString("age"));
Status.Text = db_reader.GetString("status");
Parent1.Text = db_reader.GetString("parent1_fullname");
Parent2.Text = db_reader.GetString("parent2_fullname");
Residence1.Text = db_reader.GetString("residence");
Residence2.Text = db_reader.GetString("residence2");
Sacrament.Text = db_reader.GetString("sacrament");
CauseOfDeath.Text = db_reader.GetString("cause_of_death");
PlaceOfInterment.Text = db_reader.GetString("place_of_interment");
Stipend.Value = Convert.ToDouble(db_reader.GetString("stipend"));
Minister.Text = db_reader.GetString("minister");
Remarks.Text = db_reader.GetString("remarks");
}
//close Connection
dbman.DBClose();
}
else
{
}
Suggestions1.Visibility = Visibility.Hidden;
Suggestions2.Visibility = Visibility.Hidden;
Suggestions3.Visibility = Visibility.Hidden;
Suggestions4.Visibility = Visibility.Hidden;
Suggestions5.Visibility = Visibility.Hidden;
Suggestions6.Visibility = Visibility.Hidden;
}
public MoveAppointmentWindowPopup(string AptID, DateTime date)
{
string sysFormat = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
_aid = AptID;
InitializeComponent();
SelectedDate.Content = date.ToString(sysFormat);
GetFixedTimeSchedules();
FetchPriests();
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT * FROM appointments WHERE appointment_id = @aid LIMIT 1;";
cmd.Parameters.AddWithValue("@aid", AptID);
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
FetchMassFee(GetAType(db_reader.GetString("appointment_type")));
TypeHolder.Content = GetAType(db_reader.GetString("appointment_type"));
RequestedBy.Text = db_reader.GetString("requested_by");
Venue.Text = db_reader.GetString("venue");
Remarks.Text = db_reader.GetString("remarks");
for (int i = 0; i < SelectedTime.Items.Count; i++)
{
if (SelectedTime.Items[i].ToString() == DateTime.Parse(db_reader.GetString("appointment_time")).ToString("hh:mm tt"))
{
SelectedTime.SelectedIndex = i;
}
}
}
//close Connection
dbman.DBClose();
}
else
{
}
}
private void GetResidingPriests()
{
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT * FROM residing_priests WHERE priest_name != 'NA';";
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
Signatory.Items.Add(db_reader.GetString("priest_name"));
}
//close Connection
dbman.DBClose();
}
else
{
}
}
private string CountEntries(int bookNum)
{
string ret = "0";
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM records WHERE book_number = @book_number;";
cmd.Parameters.AddWithValue("@book_number", bookNum);
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
ret = db_reader.GetString("COUNT(*)");
}
//close Connection
dbman.DBClose();
}
return ret;
}
private void GetFixedTimeSchedules()
{
dbman = new DBConnectionManager();
if (dbman.DBConnect().State == ConnectionState.Open)
{
MySqlCommand cmd = dbman.DBConnect().CreateCommand();
cmd.CommandText = "SELECT * FROM timeslots WHERE status = 'Active';";
cmd.Prepare();
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
SelectedTime.Items.Add(DateTime.Parse(db_reader.GetString("timeslot")).ToString("HH:mm tt"));
}
//close Connection
dbman.DBClose();
}
else
{
}
}
