public List <PatientVM> GetAllPatients()
{
List <PatientVM> patients = new List <PatientVM>();
PatientVM patient = null;
try
{
string connString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
using (SqlConnection con = new SqlConnection(connString))
{
con.Open();
SqlCommand command = new SqlCommand();
StringBuilder builder = new StringBuilder();
builder.Append("SELECT * FROM dbo.Patient");
command.CommandText = builder.ToString();
command.Connection = con;
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataTable table = new DataTable();
adapter.Fill(table);
var records = table.Select();
foreach (var row in records)
{
patient = new PatientVM();
patient.Id = Convert.ToInt64(row["Id"]);
patient.FirstName = row["FirstName"] as string;
patient.LastName = row["LastName"] as string;
patient.Gender = row["Gender"] as string;
patient.Address = row["Address"] as string;
patient.Phone = row["Phone"] as string;
patient.Email = row["Email"] as string;
patient.PatientID = row["PatientID"] as string;
patient.BirthDate = row["BirthDate"] as string;
patient.Status = row["Status"] as string;
patient.RegistrationDate = row["RegistrationDate"] as string;
patient.MaritalStatus = row["MaritalStatus"] as string;
patients.Add(patient);
}
}
}
}
catch (SqlException oe)
{
ExceptionBag bag = new ExceptionBag();
bag.Message = oe.Message;
bag.Date = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss");
bag.ExecutingOperation = "GetAllPatients";
bag.InnerException = oe.InnerException == null ? string.Empty : oe.InnerException.ToString();
ExceptionLogger.LogToFileAsync(bag);
}
return(patients);
}
public static ActionResult HandleException(FrameworkException frameworkException, IExecutionContext executionCtx, ISessionContext sessionCtx)
{
IExceptionConfig expConfig = ExceptionBag.Get(frameworkException.ErrorId.ToString());
IActionResultBuilder exceptionBuilder = ActionResultBuilderFactory.Create(expConfig.ResponseType, null);
ExceptionCommand exceptionCommand = new ExceptionCommand(expConfig);
ExceptionViewModel exceptionModel = exceptionCommand.Get(executionCtx, sessionCtx);
return(exceptionBuilder.Build(exceptionModel));
}
public List <PaymentVM> GetAllPayments()
{
List <PaymentVM> payments = new List <PaymentVM>();
PaymentVM payment = null;
try
{
string connString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
using (SqlConnection con = new SqlConnection(connString))
{
con.Open();
SqlCommand command = new SqlCommand();
StringBuilder builder = new StringBuilder();
builder.Append("SELECT Payments.Id, Payments.PatientID, Payments.AmountPaid, Patient.FirstName, Patient.LastName,Payments.Date, Payments.PaymentFor, Payments.PaymentMode, Employee.Name PaymentCapturedBy FROM dbo.Payments");
builder.Append(" INNER JOIN Employee ON Payments.CapturedBy = Employee.Id");
builder.Append(" INNER JOIN Patient ON Payments.PatientID = Patient.PatientID");
command.CommandText = builder.ToString();
command.Connection = con;
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataTable table = new DataTable();
adapter.Fill(table);
var records = table.Select();
foreach (var row in records)
{
payment = new PaymentVM();
payment.Id = Convert.ToInt64(row["Id"]);
payment.PatientID = row["PatientID"] as string;
payment.AmountPaid = Convert.ToDecimal(row["AmountPaid"]);
payment.Date = row["Date"] as string;
payment.PaymentCapturedBy = row["PaymentCapturedBy"] as string;
payment.PaymentFor = row["PaymentFor"] as string;
payment.PaymentMode = row["PaymentMode"] as string;
payment.PatientFullName = row["FirstName"] as string + " " + row["LastName"] as string;;
payments.Add(payment);
}
}
}
}
catch (SqlException oe)
{
ExceptionBag bag = new ExceptionBag();
bag.Message = oe.Message;
bag.Date = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss");
bag.ExecutingOperation = "GetAllPayments";
bag.InnerException = oe.InnerException == null ? string.Empty : oe.InnerException.ToString();
ExceptionLogger.LogToFileAsync(bag);
}
return(payments);
}
public bool IsPatientIDExist(string patientId)
{
bool state = false;
try
{
string connString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
using (SqlConnection con = new SqlConnection(connString))
{
con.Open();
SqlCommand command = new SqlCommand();
StringBuilder builder = new StringBuilder();
builder.Append("SELECT PatientID FROM dbo.Patient WHERE PatientID = @patId");
command.CommandText = builder.ToString();
command.Parameters.AddWithValue("@patId", patientId);
command.Connection = con;
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataTable table = new DataTable();
adapter.Fill(table);
var records = table.Select();
foreach (var row in records)
{
var patient = row["PatientID"] as string;
if (patient.Trim() == patientId.Trim())
{
state = true;
}
}
}
}
}
catch (SqlException oe)
{
ExceptionBag bag = new ExceptionBag();
bag.Message = oe.Message;
bag.Date = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss");
bag.ExecutingOperation = "IsPatientIDExist";
bag.InnerException = oe.InnerException == null ? string.Empty : oe.InnerException.ToString();
ExceptionLogger.LogToFileAsync(bag);
}
return(state);
}
public bool SavePatient(Patient model)
{
bool state = false;
try
{
string connString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
using (SqlConnection con = new SqlConnection(connString))
{
con.Open();
SqlCommand command = new SqlCommand();
StringBuilder builder = new StringBuilder();
builder.Append("INSERT INTO dbo.Patient(FirstName, LastName,Gender,Address ,Phone,Email,PatientID,BirthDate,Status ,RegistrationDate,MaritalStatus)");
builder.Append(" VALUES(@First, @LastName, @Gender, @Address , @Phone, @Email, @PatientID, @BirthDate, @Status , @RegistrationDate, @MaritalStatus)");
command.CommandText = builder.ToString();
command.Parameters.AddWithValue("@First", model.FirstName);
command.Parameters.AddWithValue("@LastName", model.LastName);
command.Parameters.AddWithValue("@Gender", model.Gender);
command.Parameters.AddWithValue("@Address", model.Address);
command.Parameters.AddWithValue("@Phone", model.Phone);
command.Parameters.AddWithValue("@Email", model.Email);
command.Parameters.AddWithValue("@PatientID", model.PatientID);
command.Parameters.AddWithValue("@BirthDate", model.BirthDate);
command.Parameters.AddWithValue("@Status", "Active");
command.Parameters.AddWithValue("@RegistrationDate", DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"));
command.Parameters.AddWithValue("@MaritalStatus", model.MaritalStatus);
command.Connection = con;
int result = command.ExecuteNonQuery();
if (result > 0)
{
state = true;
}
}
}
catch (SqlException oe)
{
ExceptionBag bag = new ExceptionBag();
bag.Message = oe.Message;
bag.Date = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss");
bag.ExecutingOperation = "GetAllPatients";
bag.InnerException = oe.InnerException == null ? string.Empty : oe.InnerException.ToString();
ExceptionLogger.LogToFileAsync(bag);
}
return(state);
}
public List <EmployeeVM> GetEmployeeNames()
{
List <EmployeeVM> names = new List <EmployeeVM>();
EmployeeVM emp = null;
try
{
string connString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
using (SqlConnection con = new SqlConnection(connString))
{
con.Open();
SqlCommand command = new SqlCommand();
StringBuilder builder = new StringBuilder();
builder.Append("SELECT Id, Name FROM dbo.Employee");
command.CommandText = builder.ToString();
command.Connection = con;
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataTable table = new DataTable();
adapter.Fill(table);
var records = table.Select();
foreach (var row in records)
{
emp = new EmployeeVM();
emp.Id = Convert.ToInt64(row["Id"]);
emp.Name = row["Name"] as string;
names.Add(emp);
}
}
}
}
catch (SqlException oe)
{
ExceptionBag bag = new ExceptionBag();
bag.Message = oe.Message;
bag.Date = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss");
bag.ExecutingOperation = "GetEmployeeNames";
bag.InnerException = oe.InnerException == null ? string.Empty : oe.InnerException.ToString();
ExceptionLogger.LogToFileAsync(bag);
}
return(names);
}
public bool SavePayment(PaymentVM model)
{
bool state = false;
long empId = FetchEmployeeId(model.PaymentCapturedBy);
try
{
string connString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
using (SqlConnection con = new SqlConnection(connString))
{
con.Open();
SqlCommand command = new SqlCommand();
StringBuilder builder = new StringBuilder();
builder.Append("INSERT INTO dbo.Payments(PatientID, AmountPaid, Date, CapturedBy, PaymentFor, PaymentMode)");
builder.Append(" VALUES(@id, @amt, @date, @capBy , @payFor, @PayMode)");
command.CommandText = builder.ToString();
command.Parameters.AddWithValue("@id", model.PatientID);
command.Parameters.AddWithValue("@amt", model.AmountPaid);
command.Parameters.AddWithValue("@date", DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss.fff tt"));
command.Parameters.AddWithValue("@capBy", empId);
command.Parameters.AddWithValue("@payFor", model.PaymentFor);
command.Parameters.AddWithValue("@PayMode", model.PaymentMode);
command.Connection = con;
int result = command.ExecuteNonQuery();
if (result > 0)
{
state = true;
}
}
}
catch (SqlException oe)
{
ExceptionBag bag = new ExceptionBag();
bag.Message = oe.Message;
bag.Date = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss");
bag.ExecutingOperation = "GetAllPatients";
bag.InnerException = oe.InnerException == null ? string.Empty : oe.InnerException.ToString();
ExceptionLogger.LogToFileAsync(bag);
}
return(state);
}
public long FetchEmployeeId(string employeeName)
{
long empId = 0;
try
{
string connString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
using (SqlConnection con = new SqlConnection(connString))
{
con.Open();
SqlCommand command = new SqlCommand();
StringBuilder builder = new StringBuilder();
builder.Append("SELECT Id FROM dbo.Employee");
builder.Append(" WHERE Name = @name");
command.CommandText = builder.ToString();
command.Parameters.AddWithValue("@name", employeeName);
command.Connection = con;
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataTable table = new DataTable();
adapter.Fill(table);
var records = table.Select();
foreach (var row in records)
{
empId = Convert.ToInt64(row["Id"]);
}
}
}
}
catch (SqlException oe)
{
ExceptionBag bag = new ExceptionBag();
bag.Message = oe.Message;
bag.Date = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss");
bag.ExecutingOperation = "GetAllPayments";
bag.InnerException = oe.InnerException == null ? string.Empty : oe.InnerException.ToString();
ExceptionLogger.LogToFileAsync(bag);
}
return(empId);
}
