/// <summary> /// Retrieves Current Date and Time from the Server. /// </summary> internal string GetServerDateTime() { dbman = new DBConnectionManager(); string curDateTime = ""; if (dbman.DBConnect().State == ConnectionState.Open) { MySqlCommand cmd = dbman.DBConnect().CreateCommand(); cmd.CommandText = "SELECT NOW() FROM DUAL;"; MySqlDataReader db_reader = cmd.ExecuteReader(); while (db_reader.Read()) { curDateTime = db_reader.GetString("NOW()"); } //close Connection dbman.DBClose(); return(curDateTime); } else { //close Connection dbman.DBClose(); return(null); } }
internal string LogAccount(string logDetails) { string ret = ""; string logID = GenALogID(); string logger = Application.Current.Resources["uid"].ToString(); string[] dt = 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"); MySqlCommand cmd = dbman.DBConnect().CreateCommand(); cmd.CommandText = "INSERT INTO account_logs (log_id, account_id, log_details, log_time, log_date)" + "VALUES(@log_id, @log_creator, @log_details, @log_time, @log_date)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@log_id", logID); cmd.Parameters.AddWithValue("@log_details", logDetails); cmd.Parameters.AddWithValue("@log_date", curDate); cmd.Parameters.AddWithValue("@log_time", curTime); cmd.Parameters.AddWithValue("@log_creator", logger); ret = cmd.ExecuteNonQuery().ToString(); dbman.DBClose(); return(ret); }
internal bool IsArchived(string recordID) { bool ret = false; dbman = new DBConnectionManager(); if (dbman.DBConnect().State == ConnectionState.Open) { MySqlCommand cmd = dbman.DBConnect().CreateCommand(); cmd.CommandText = "SELECT registers.status FROM records, registers WHERE records.record_id = @rid AND records.book_number = registers.book_number LIMIT 1;"; cmd.Parameters.AddWithValue("@rid", recordID); MySqlDataReader db_reader = cmd.ExecuteReader(); while (db_reader.Read()) { if (db_reader.GetString("status") == "Archived") { ret = true; } else { ret = false; } } //close Connection dbman.DBClose(); } else { } return(ret); }
internal string GenerateReceiptNum() { string ret = "000001"; dbman = new DBConnectionManager(); if (dbman.DBConnect().State == ConnectionState.Open) { MySqlCommand cmd = dbman.DBConnect().CreateCommand(); cmd.CommandText = "SELECT DISTINCT * FROM transactions WHERE status = 'Paid' ORDER BY or_number;"; MySqlDataReader db_reader = cmd.ExecuteReader(); while (db_reader.Read()) { string orRecent; if (string.IsNullOrEmpty(db_reader.GetString("or_number"))) { orRecent = "000001"; } else { orRecent = db_reader.GetString("or_number"); } Console.WriteLine(orRecent); ret = String.Format("{0:000000}", (int.Parse(orRecent.TrimStart(new Char[] { '0' })) + 1)); } //close Connection dbman.DBClose(); } else { } return(ret); }
internal int InsertTransaction(string type, string status, string targetID, double fee) { string uid = Application.Current.Resources["uid"].ToString(); string[] dt = 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(); //TODO try { string tID = GenTransactionID(); MySqlCommand cmd = dbman.DBConnect().CreateCommand(); cmd.CommandText = "INSERT INTO transactions(transaction_id, type, status, tran_date, tran_time, completion_date, completion_time, placed_by, completed_by, target_id, fee)" + "VALUES(@transaction_id, @type, @status, @tran_date, @tran_time, @completion_date, @completion_time, @placed_by, @completed_by, @target_id, @fee)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@transaction_id", tID); cmd.Parameters.AddWithValue("@type", type); cmd.Parameters.AddWithValue("@status", status); cmd.Parameters.AddWithValue("@tran_date", curDate); cmd.Parameters.AddWithValue("@tran_time", curTime); cmd.Parameters.AddWithValue("@completion_date", null); cmd.Parameters.AddWithValue("@completion_time", null); cmd.Parameters.AddWithValue("@placed_by", uid); cmd.Parameters.AddWithValue("@completed_by", null); cmd.Parameters.AddWithValue("@target_id", targetID); cmd.Parameters.AddWithValue("@fee", fee); //cmd.Parameters.AddWithValue("@or_number", this.GenerateReceiptNum()); int 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); } }
internal string GenALogID() { dbman = new DBConnectionManager(); if (dbman.DBConnect().State == ConnectionState.Open) { MySqlCommand cmd = dbman.DBConnect().CreateCommand(); cmd.CommandText = "SELECT COUNT(log_id) FROM account_logs;"; MySqlDataReader db_reader = cmd.ExecuteReader(); while (db_reader.Read()) { ret = "LOG-" + (db_reader.GetInt32("COUNT(log_id)") + 1); } //close Connection dbman.DBClose(); } else { } return(ret); }
internal string GenDirectoryID() { dbman = new DBConnectionManager(); if (dbman.DBConnect().State == ConnectionState.Open) { MySqlCommand cmd = dbman.DBConnect().CreateCommand(); cmd.CommandText = "SELECT COUNT(directory_id) FROM burial_directory;"; MySqlDataReader db_reader = cmd.ExecuteReader(); while (db_reader.Read()) { ret = "DIR-" + (db_reader.GetInt32("COUNT(directory_id)") + 1); } //close Connection dbman.DBClose(); } else { } return(ret); }
internal string GenerateReceiptNum2() { dbman = new DBConnectionManager(); if (dbman.DBConnect().State == ConnectionState.Open) { MySqlCommand cmd = dbman.DBConnect().CreateCommand(); cmd.CommandText = "SELECT COUNT(transaction_id) FROM transactions;"; MySqlDataReader db_reader = cmd.ExecuteReader(); while (db_reader.Read()) { ret = String.Format("{0:000000}", (db_reader.GetInt32("COUNT(transaction_id)") + 1)); } //close Connection dbman.DBClose(); } else { } return(ret); }
internal string GetChurchName() { dbman = new DBConnectionManager(); if (dbman.DBConnect().State == ConnectionState.Open) { MySqlCommand cmd = dbman.DBConnect().CreateCommand(); cmd.CommandText = "SELECT key_value FROM settings WHERE key_name = @key_name;"; cmd.Parameters.AddWithValue("@key_name", "Church Name"); MySqlDataReader db_reader = cmd.ExecuteReader(); while (db_reader.Read()) { ret = db_reader.GetString("key_value"); } //close Connection dbman.DBClose(); } else { } return(ret); }
internal string GetUsername(string uid) { dbman = new DBConnectionManager(); 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()) { ret = db_reader.GetString("user_name"); } //close Connection dbman.DBClose(); } else { } return(ret); }
internal string GetAccountType(string uid) { dbman = new DBConnectionManager(); 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 (db_reader.GetInt32("account_type") == 1) { ret = "Administrator"; } else if (db_reader.GetInt32("account_type") == 2) { ret = "Secretary"; } else if (db_reader.GetInt32("account_type") == 3) { ret = "Cashier"; } else if (db_reader.GetInt32("account_type") == 4) { ret = "Registrar"; } else { ret = "Custom"; } } //close Connection dbman.DBClose(); } else { } return(ret); }
internal string GetPrintFee(string type) { dbman = new DBConnectionManager(); if (dbman.DBConnect().State == ConnectionState.Open) { MySqlCommand cmd = dbman.DBConnect().CreateCommand(); cmd.CommandText = "SELECT key_value FROM settings WHERE key_name = @key_name;"; if (type == "Baptismal") { cmd.Parameters.AddWithValue("@key_name", "Print Fee Baptismal"); } else if (type == "Confirmation") { cmd.Parameters.AddWithValue("@key_name", "Print Fee Confirmation"); } else if (type == "Matrimonial") { cmd.Parameters.AddWithValue("@key_name", "Print Fee Matrimonial"); } else if (type == "Burial") { cmd.Parameters.AddWithValue("@key_name", "Print Fee Burial"); } MySqlDataReader db_reader = cmd.ExecuteReader(); while (db_reader.Read()) { ret = db_reader.GetString("key_value"); } //close Connection dbman.DBClose(); } else { } return(ret); }