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