public List <PatientTestVm> GetPatientTest(string code)
{
Query = "Select t.Name Test FROm PatientTest pt Join Patient p On p.Id = pt.PatientId JOIN Test t ON t.Id = pt.TestId WHERE PatientCode = @code";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Command.Parameters.Clear();
Command.Parameters.Add("code", SqlDbType.VarChar);
Command.Parameters["code"].Value = code;
Reader = Command.ExecuteReader();
List <PatientTestVm> patientTestVms = new List <PatientTestVm>();
if (Reader.Read())
{
PatientTestVm patientTest = new PatientTestVm();
patientTest = new PatientTestVm();
patientTest.TestName = Reader["Test"].ToString();
patientTestVms.Add(patientTest);
}
Reader.Close();
Connection.Close();
return(patientTestVms);
}
public PatientTestVm GetDueInformation(PatientTestVm aPatient)
{
Query = "SELECT b.Unpaid,p.Id,b.Date FROM Patient p JOIN Bill b On p.Id= b.PatientId WHERE p.PatientCode = @patientCode";
Command = new SqlCommand(Query, Connection);
PatientTestVm aPatients = null;
Connection.Open();
Command.Parameters.Clear();
Command.Parameters.Add("patientCode", SqlDbType.VarChar);
Command.Parameters["patientCode"].Value = aPatient.PatientCode;
Reader = Command.ExecuteReader();
bool a = Reader.HasRows;
while (Reader.Read())
{
aPatients = new PatientTestVm();
aPatients.BillDate = Reader["Date"].ToString();
aPatients.UnpaidBill = Convert.ToDouble(Reader["Unpaid"]);
aPatients.Id = (int)Reader["Id"];
}
Reader.Close();
Connection.Close();
return(aPatients);
}
public int SaveIntoBill(PatientTestVm aPatientVm)
{
Query = "Insert into Bill(PatientId,BillNo,Date,TotalBill,Unpaid) Values(@patientId, @billNo, @date, @total,@unpaid)";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Command.Parameters.Clear();
Command.Parameters.Add("patientId", SqlDbType.Int);
Command.Parameters["patientId"].Value = aPatientVm.PatientId;
Command.Parameters.Add("billNo", SqlDbType.VarChar);
Command.Parameters["billNo"].Value = aPatientVm.BillNo;
Command.Parameters.Add("date", SqlDbType.Date);
Command.Parameters["date"].Value = aPatientVm.EntryDate;
Command.Parameters.Add("total", SqlDbType.Decimal);
Command.Parameters["total"].Value = aPatientVm.TotalBill;
Command.Parameters.Add("unpaid", SqlDbType.Decimal);
Command.Parameters["unpaid"].Value = aPatientVm.TotalBill;
int rowAffected = Command.ExecuteNonQuery();
Connection.Close();
return(rowAffected);
}
protected void searchButton_Click(object sender, EventArgs e)
{
PatientTestVm aPatient = new PatientTestVm();
aPatient.PatientCode = patientCodeTextBox.Value;
//aPatient.BillNo = billNoTextBox.Value;
PatientTestVm bPatient = patientTestManager.GetDueInformation(aPatient);
double due = bPatient.UnpaidBill;
if (due == 0)
{
messageLabel2.InnerText = "no dues";
payButton.Enabled = false;
amountTextBox.Value = String.Empty;
dueDateTextBox.Value = String.Empty;
}
else
{
payButton.Enabled = true;
amountTextBox.Value = bPatient.UnpaidBill.ToString();
string date = bPatient.BillDate;
dueDateTextBox.Value = date.Substring(0, 10);
idHiddenField.Value = bPatient.Id.ToString();
}
}
protected void payButton_Click(object sender, EventArgs e)
{
PatientTestVm aPatient = new PatientTestVm();
aPatient.PatientId = Convert.ToInt32(idHiddenField.Value);
aPatient.UnpaidBill = Convert.ToDouble(amountTextBox.Value);
//billNoTextBox.Value = String.Empty;
patientCodeTextBox.Value = String.Empty;
messageLabel.InnerText = patientTestManager.UpdateDueBill(aPatient);
}
public string UpdateDueBill(PatientTestVm aPatient)
{
int rowAffected = aPatientTestGateway.UpdateDueBill(aPatient);
if (rowAffected > 0)
{
return("Bill Updated");
}
else
{
return("Bill Updating failed");
}
}
public int UpdateDueBill(PatientTestVm aPatient)
{
Query = "UPDATE Bill SET Unpaid = '0.00', Paid = @paid WHERE PatientId = @id ";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Command.Parameters.Clear();
Command.Parameters.Add("id", SqlDbType.Int);
Command.Parameters["id"].Value = aPatient.PatientId;
Command.Parameters.Add("paid", SqlDbType.Int);
Command.Parameters["paid"].Value = aPatient.UnpaidBill;
int rowAffected = Command.ExecuteNonQuery();
Connection.Close();
return(rowAffected);
}
protected void saveButton_Click(object sender, EventArgs e)
{
PatientTestVm aPatientVm = new PatientTestVm();
aPatientVm.Code = codeTextBox.Value;
aPatientVm.PatientId = aPatientManager.GetPatientId(aPatientVm);
int id = aPatientVm.PatientId;
aPatientVm.EntryDate = DateTime.Now.ToString("yyyy-MM-dd");
aPatientVm.TotalBill = Convert.ToDouble(totalTextBox.Value);
string aNum = DateTime.Now.ToString("MMdd");
aPatientVm.BillNo = "B" + aPatientVm.Code + aNum;
date = aPatientVm.EntryDate;
billNo = aPatientVm.BillNo;
int rowAffected = aPatientTestManager.SaveIntoBill(aPatientVm);
if (rowAffected > 0)
{
List <PatientTestVm> patientTest = (List <PatientTestVm>)ViewState["name"];
foreach (PatientTestVm patientTestVM in patientTest)
{
patientTestVM.PatientId = id;
patientTestVM.EntryDate = DateTime.Now.ToString("yyyy-MM-dd");
int rowAffected2 = aPatientTestManager.SaveInPatientTest(patientTestVM);
}
messageLabel.InnerText = "Saved successfully..";
}
else
{
messageLabel2.InnerText = "Saving failed";
}
Patient aPatient = aPatientManager.GetPatientForPdf(aPatientVm.PatientId);
Name = aPatient.Name;
contactNo = aPatient.ContactNo;
code = aPatient.Code;
GeneratePdf();
}
public List <PatientTestVm> AddTests()
{
PatientTestVm aPatientVm = new PatientTestVm();
aPatientVm.TestName = selectTestDropDownList.SelectedItem.Text;
aPatientVm.TestId = Convert.ToInt32(selectTestDropDownList.SelectedValue);
aPatientVm.Fee = Convert.ToDouble(feeTextBox.Value);
if (ViewState["name"] == null)
{
List <PatientTestVm> tests = new List <PatientTestVm>();
tests.Add(aPatientVm);
ViewState["name"] = tests;
}
else
{
List <PatientTestVm> tests = (List <PatientTestVm>)ViewState["name"];
tests.Add(aPatientVm);
ViewState["name"] = tests;
}
return((List <PatientTestVm>)ViewState["name"]);
}
//public List<PatientTestVm> GetPatientTest(Patient aPatient)
//{
//}
public int SaveInPatientTest(PatientTestVm patientTestVm)
{
Query = "Insert into PatientTest Values(@patientId, @testId, @date)";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Command.Parameters.Clear();
Command.Parameters.Add("testId", SqlDbType.Int);
Command.Parameters["testId"].Value = patientTestVm.TestId;
Command.Parameters.Add("patientId", SqlDbType.Int);
Command.Parameters["patientId"].Value = patientTestVm.PatientId;
Command.Parameters.Add("date", SqlDbType.Date);
Command.Parameters["date"].Value = patientTestVm.EntryDate;
int rowAffected = Command.ExecuteNonQuery();
Connection.Close();
return(rowAffected);
}
public List <PatientTestVm> GetPatientWithUnpaidBill(string fromDate, string toDate)
{
Query = "Select * from Bill b Join Patient p On p.Id = b.PatientId WHERE b.Date BETWEEN '" + fromDate + "' AND '" +
toDate + "' AND Unpaid > 0 ";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
List <PatientTestVm> patientTest = new List <PatientTestVm>();
while (Reader.Read())
{
PatientTestVm aPatient = new PatientTestVm();
aPatient.PatientName = Reader["Name"].ToString();
aPatient.ContactNo = Reader["ContactNo"].ToString();
aPatient.BillNo = Reader["BillNo"].ToString();
aPatient.UnpaidBill = Convert.ToDouble(Reader["Unpaid"]);
patientTest.Add(aPatient);
}
Reader.Close();
Connection.Close();
return(patientTest);
}
public int GetPatientId(PatientTestVm aPatientVm)
{
Query = "SELECT * FROM Patient WHERE PatientCode = @code";
Command = new SqlCommand(Query, Connection);
Command.Parameters.Clear();
Command.Parameters.Add("code", SqlDbType.VarChar);
Command.Parameters["code"].Value = aPatientVm.Code;
Connection.Open();
Reader = Command.ExecuteReader();
Patient patient = null;
int id = 0;
if (Reader.Read())
{
patient = new Patient();
patient.Id = Convert.ToInt32(Reader["Id"]);
id = patient.Id;
}
Reader.Close();
Connection.Close();
return(id);
}
public PatientTestVm GetDueInformation(PatientTestVm aPatient)
{
return(aPatientTestGateway.GetDueInformation(aPatient));
}
public int SaveIntoBill(PatientTestVm aPatientVm)
{
int rowAffected = aPatientTestGateway.SaveIntoBill(aPatientVm);
return(rowAffected);
}
//public List<PatientTestVm> GetPatientTest(Patient aPatient)
//{
// return aPatientTestGateway.GetPatientTest(aPatient);
//}
public int SaveInPatientTest(PatientTestVm patientTestVm)
{
int rowAffected = aPatientTestGateway.SaveInPatientTest(patientTestVm);
return(rowAffected);
}
public int GetPatientId(PatientTestVm aPatientVm)
{
return(aPatientGateway.GetPatientId(aPatientVm));
}
