public static PGSqlAccessHelper GetInstance()
{
if (instance == null)
{
instance = new PGSqlAccessHelper();
}
return(instance);
}
// Return the count of the existing patients
public int GetPatientCount()
{
NpgsqlConnection connection = null;
int patCount = 0;
try
{
PGSqlAccessHelper pgaccess = PGSqlAccessHelper.GetInstance();
if (!pgaccess.ConnectToDB(out connection))
{
throw new Exception("Failed to connect to the PGSQL DB");
}
Int64 counter = 0; // TODO: This is of no use now. Need to find out how to discard this
string sqlCmd = String.Format(@"call sppatientcount('{0}')", counter);
using (var cmd = new NpgsqlCommand(sqlCmd, connection))
{
//cmd.Parameters.AddWithValue("counter", counter);
using (var reader = cmd.ExecuteReader())
{
reader.Read();
if (reader.HasRows == false)
{
return(0); // Error in reading the patient count
}
else
{
var value = reader.GetInt64(0);
patCount = Int32.Parse(reader[0].ToString());
}
}
}
return(patCount);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
if ((connection != null) && (connection.State != ConnectionState.Open))
{
connection.Close();
}
}
return(patCount);
}
// TODO: Physician name not used and stored anywhere. Create a separate table for this
public bool CreateNewPatient(PatientInfo patInfo,
InsuranceInfo insuranceInfo,
PhysicianDetails physDetails)
{
NpgsqlConnection connection = null;
try
{
PGSqlAccessHelper pgaccess = PGSqlAccessHelper.GetInstance();
if (!pgaccess.ConnectToDB(out connection))
{
throw new Exception("Failed to connect to the PGSQL DB");
}
string sqlCmd =
String.Format(@"CALL spcreatepatient('{0}', '{1}', {2}, '{3}', '{4}', '{5}', '{6}', '{7}')",
patInfo.m_patientId, patInfo.m_patientName, patInfo.m_phoneNum,
patInfo.m_emailId, patInfo.m_address, insuranceInfo.m_insuranceId,
insuranceInfo.m_insurancePlan, physDetails.m_PhysicianId);
using (var cmd = new NpgsqlCommand(sqlCmd, connection))
{
cmd.ExecuteNonQuery();
}
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
if ((connection != null) && (connection.State != ConnectionState.Open))
{
connection.Close();
}
}
return(false);
}
// Delete all the created patients
public bool DeleteAllPatients()
{
NpgsqlConnection connection = null;
try
{
PGSqlAccessHelper pgaccess = PGSqlAccessHelper.GetInstance();
if (!pgaccess.ConnectToDB(out connection))
{
throw new Exception("Failed to connect to the PGSQL DB");
}
string sqlCmd = "call spdeleteallpatients()";
using (var cmd = new NpgsqlCommand(sqlCmd, connection))
{
using (var reader = cmd.ExecuteReader())
{
// Nothing to do here
}
}
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
if ((connection != null) && (connection.State != ConnectionState.Open))
{
connection.Close();
}
}
return(false);
}
// Fetch a specific patient's details
// Using a direct SQL query here
public bool GetPatient(Guid patientId, out PatientInfo patInfo, out InsuranceInfo insuranceInfo,
out PhysicianDetails physDetails)
{
patInfo = null;
insuranceInfo = null;
physDetails = null;
NpgsqlConnection connection = null;
int patCount = 0;
try
{
PGSqlAccessHelper pgaccess = PGSqlAccessHelper.GetInstance();
if (!pgaccess.ConnectToDB(out connection))
{
throw new Exception("Failed to connect to the PGSQL DB");
}
string sqlCmd = "SELECT \"Name\", public.\"PatientDetails\".\"PatientId\", \"PhoneNum\", \"EmailId\", \"Address\", " +
"\"PhysicianId\", \"FirstAdmissionDate\", \"LatestVisitDate\", public.\"InsuranceInfo\".\"InsuranceId\", " +
"\"InsurancePlan\" FROM public.\"PatientDetails\", public.\"PatientInfo\", public.\"InsuranceInfo\" where " +
string.Format("\"PatientDetails\".\"PatientId\" = '{0}' and \"PatientInfo\".\"PatientID\" = '{0}' and\"InsuranceInfo\".\"PatientId\" = '{0}';", patientId);
using (var cmd = new NpgsqlCommand(sqlCmd, connection))
{
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
reader.Read();
if (reader.HasRows == false)
{
return(false); // Matching patient not found
}
else
{
/*
* SELECT "Name", public."PatientDetails"."PatientId", "PhoneNum",
* "EmailId", "Address", "PhysicianId", "FirstAdmissionDate",
* "LatestVisitDate", public."InsuranceInfo"."InsuranceId", "InsurancePlan"
* FROM public."PatientDetails", public."PatientInfo", public."InsuranceInfo"
* where "PatientDetails"."PatientId" = patientid
* and
* "PatientInfo"."PatientID" = patientid
* and
* "InsuranceInfo"."PatientId" = patientid;
*/
var name = reader[0];
patInfo = new PatientInfo(reader.GetString(0), reader.GetInt32(2),
reader.GetString(3), reader.GetString(4), reader.GetGuid(8), reader.GetGuid(1));
insuranceInfo = new InsuranceInfo(patientId, reader.GetString(9));
physDetails = new PhysicianDetails("ABCD", "DEFH"); // TODO: Physician details are not stored. Need to add these
}
}
}
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
if ((connection != null) && (connection.State != ConnectionState.Open))
{
connection.Close();
}
}
return(false);
}
