public TestRequestEntry GetSearchedPatientDetailsWithBillNo(string billNo)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM testRequestEntry WHERE billNo=@billno";
TestRequestEntry entry = null;
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@billNo", billNo);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
entry = new TestRequestEntry();
entry.NameOfPatient = reader["nameOfPatient"].ToString();
entry.DateOfBirth = reader["dateOfBirth"].ToString();
entry.MobileNo = reader["mobileNo"].ToString();
entry.BillNo = reader["billNo"].ToString();
entry.TotalFee = Convert.ToDouble(reader["totalFee"]);
entry.Status = reader["paymentStatus"].ToString();
entry.InvoiceDate = reader["invoiceDate"].ToString();
}
reader.Close();
connection.Close();
return(entry);
}
public List <TestRequestEntry> GetAllUnpaidPatient(string fromDate, string toDate)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT DISTINCT * FROM testRequestEntry WHERE invoiceDate BETWEEN @fromDate AND @toDate AND paymentStatus=@status";
List <TestRequestEntry> testRequestEntries = new List <TestRequestEntry>();
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@fromDate", fromDate);
command.Parameters.AddWithValue("@toDate", toDate);
command.Parameters.AddWithValue("@status", "Unpaid");
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TestRequestEntry entry = new TestRequestEntry();
entry.NameOfPatient = reader["nameOfPatient"].ToString();
entry.DateOfBirth = reader["dateOfBirth"].ToString();
entry.MobileNo = reader["mobileNo"].ToString();
entry.BillNo = reader["billNo"].ToString();
entry.TotalFee = Convert.ToDouble(reader["totalFee"]);
entry.InvoiceDate = reader["invoiceDate"].ToString();
entry.Status = reader["paymentStatus"].ToString();
testRequestEntries.Add(entry);
}
reader.Close();
connection.Close();
return(testRequestEntries);
}
public List <TestRequestEntry> GetAllPatient()
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM testRequestEntry";
List <TestRequestEntry> testRequestEntries = new List <TestRequestEntry>();
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TestRequestEntry entry = new TestRequestEntry();
entry.NameOfPatient = reader["nameOfPatient"].ToString();
entry.DateOfBirth = reader["dateOfBirth"].ToString();
entry.MobileNo = reader["mobileNo"].ToString();
entry.BillNo = reader["billNo"].ToString();
entry.TotalFee = Convert.ToDouble(reader["totalFee"]);
entry.InvoiceDate = reader["invoiceDate"].ToString();
entry.Status = reader["paymentStatus"].ToString();
testRequestEntries.Add(entry);
}
reader.Close();
connection.Close();
return(testRequestEntries);
}
public TestRequestEntry GetPatientByMobileNo(string mobileNo)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM Patient WHERE MobileNo = '" + mobileNo + "'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
TestRequestEntry testRequest = null;
if (reader.HasRows)
{
while (reader.Read())
{
testRequest = new TestRequestEntry();
testRequest.PatientName = reader["PatientName"].ToString();
testRequest.MobileNo = reader["MobileNo"].ToString();
testRequest.BillNo = reader["BillNo"].ToString();
testRequest.PatientId = Convert.ToInt32(reader["PatientId"].ToString());
}
reader.Close();
}
connection.Close();
return(testRequest);
}
public int SavePatientTests(List <TestRequestEntry> aEntries, int patientId)
{
TestRequestEntry aTestRequest = new TestRequestEntry();
SqlConnection connection = new SqlConnection(connectionString);
int rowAffected = 0;
foreach (TestRequestEntry aRequest in aEntries)
{
aTestRequest.TestId = aRequest.TestId;
aTestRequest.TestName = aRequest.TestName;
aTestRequest.Fee = aRequest.Fee;
aTestRequest.TotalAmount += aRequest.Fee;
//aTestRequestLists.Add(aTestRequest);
string query = "INSERT INTO PatientTests (PatientId,TestId,Fee)VALUES(" + patientId + "," +
"" + aTestRequest.TestId + "," + aTestRequest.Fee + ")";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
rowAffected = command.ExecuteNonQuery();
connection.Close();
}
return(rowAffected);
}
private void AddToGridView()
{
if (Convert.ToInt32(testFeeTextBox.Text.ToString()) <= 0)
{
msgTestRequestLabel.Text = "Test Fee Must Be Greater Than Zero!";
return;
}
if (Convert.ToInt32(testNameDropDownList.SelectedIndex.ToString()) == 0)
{
msgTestRequestLabel.Text = "Test Name Is Empty!";
return;
}
if (ViewState["TestRequestEntry"] != null)
{
aTestRequestEntryList = (List <TestRequestEntry>)ViewState["TestRequestEntry"];
}
TestRequestEntry aTestRequestEntry = new TestRequestEntry();
aTestRequestEntry.TestId = Convert.ToInt16(testNameDropDownList.SelectedValue.ToString());
aTestRequestEntry.TestName = testNameDropDownList.SelectedItem.Text.ToString();
aTestRequestEntry.Fee = Convert.ToDecimal(testFeeTextBox.Text.ToString());
aTestRequestEntryList.Add(aTestRequestEntry);
ViewState["TestRequestEntry"] = aTestRequestEntryList;
//List<TestRequestEntry> aTestRequestAddToGridView = aTestRequestManager.GetAllRequests(aTestRequestEntryList);
//GridView2.DataSource = aTestRequestAddToGridView;//ViewState["TestRequestEntry"];
GridView2.DataSource = ViewState["TestRequestEntry"];
GridView2.DataBind();
}
private bool FillBillLable(TestRequestEntry geTestRequest)
{
patientNameLable.Text = geTestRequest.PatientName;
billNoLable.Text = geTestRequest.BillNo;
dateLable.Text = DateTime.Now.ToShortDateString();
return(true);
}
protected void searchButton_Click(object sender, EventArgs e)
{
string mobileNo = mobileNoTextBox.Text;
string billNo = billNoTextBox.Text;
TestRequestEntry testRequestEntries = testRequestEntryManager.GetSearchedPatientDetails(mobileNo, billNo);
if (testRequestEntries != null)
{
billNoTextBox.Text = testRequestEntries.BillNo;
mobileNoTextBox.Text = testRequestEntries.MobileNo;
amountTextBox.Text = testRequestEntries.TotalFee.ToString();
dueDateTextBox.Text = testRequestEntries.InvoiceDate;
ViewState.Add("Bill", testRequestEntries.BillNo);
billNoTextBox.ReadOnly = true;
mobileNoTextBox.ReadOnly = true;
resetButton.Visible = true;
messageLabel.Text = String.Empty;
}
else
{
messageLabel.Text = "No Record Found";
billNoTextBox.Text = String.Empty;
mobileNoTextBox.Text = String.Empty;
amountTextBox.Text = String.Empty;
dueDateTextBox.Text = String.Empty;
}
}
public string SavePatientData(TestRequestEntry entry)
{
List <TestRequestEntry> testRequestEntries = testRequestEntryGateway.GetAllPatient();
int checker = 0;
foreach (TestRequestEntry testRequest in testRequestEntries)
{
if (testRequest.MobileNo == entry.MobileNo)
{
checker = 1;
}
}
if (checker > 0)
{
return("Mobile No Already Exists");
}
else
{
if (testRequestEntryGateway.SavePatientData(entry) > 0)
{
return("Patient Saved Successfully");
}
return("Patient Saving Failed");
}
}
public List <TestRequestEntry> GetAllTestEnries(Int64 PatientId)
{
List <TestRequestEntry> aTestRequestEntries = new List <TestRequestEntry>();
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM vwPatientTests WHERE PatientId=" + PatientId + "";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
TestRequestEntry aTests = new TestRequestEntry();
aTests.TestId = Convert.ToInt32(reader["TestId"].ToString());
aTests.TestName = reader["TestName"].ToString();
aTests.Fee = Convert.ToDecimal(reader["Fee"].ToString());
aTests.TotalAmount = aTests.TotalAmount + aTests.Fee;
aTestRequestEntries.Add(aTests);
}
reader.Close();
}
connection.Close();
return(aTestRequestEntries);
}
public TestRequestEntry SearchByBillorMobileNo(string billNo, string mobileNo)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM Patient WHERE BillNo = '" + billNo + "' OR MobileNo = '" + mobileNo + "'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
TestRequestEntry testRequest = null;
if (reader.HasRows)
{
while (reader.Read())
{
testRequest = new TestRequestEntry();
testRequest.PatientId = Convert.ToInt32(reader["PatientId"].ToString());
testRequest.PatientName = reader["PatientName"].ToString();
testRequest.MobileNo = reader["MobileNo"].ToString();
testRequest.TotalAmount = Convert.ToDecimal(reader["TotalAmount"].ToString());
testRequest.DueDate = Convert.ToDateTime(reader["DueDate"].ToString());
testRequest.PaymentStatus = Convert.ToDecimal(reader["PaymentStatus"].ToString());
}
reader.Close();
}
connection.Close();
return(testRequest);
}
public int SaveTestRequest(TestRequestEntry testRequest)
{
if (testRequestGateway.IsMobileNoxists(testRequest.MobileNo))
{
throw new Exception("Mobile number already exists");
}
else
{
return(testRequestGateway.SaveTestRequest(testRequest));
}
}
public string SaveBillInfo(TestRequestEntry entry)
{
int testId = testRequestEntryGateway.GetTestId(entry.TestName);
int patientId = testRequestEntryGateway.GetPatientId(entry.BillNo);
string date = entry.InvoiceDate;
if (testRequestEntryGateway.SaveBillInfo(testId, patientId, date) > 0)
{
return("Info Saved");
}
return("Saving Failed");
}
public int SaveTestRequest(TestRequestEntry testRequest)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "INSERT INTO Patient(PatientName,DOB,MobileNo,TotalAmount,DueDate,PaymentStatus) VALUES ('" + testRequest.PatientName + "','" + testRequest.Dob + "','" + testRequest.MobileNo + "','" + testRequest.TotalAmount + "','" + testRequest.DueDate + "', '" + testRequest.PaymentStatus + "')";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
int rowAffacted = command.ExecuteNonQuery();
connection.Close();
return(rowAffacted);
}
public string UpdatePaymentStatus(double inputAmount, string billNo)
{
TestRequestEntry test = testRequestEntryManager.GetSearchedPatientDetailsWithBill(billNo);
double searchedAmount = test.TotalFee;
if (searchedAmount > 0)
{
double amount = searchedAmount - inputAmount;
if (paymentGateway.UpdatePaymentStatus(amount, billNo) > 0)
{
return("Payment successfull");
}
return("Payment count not be made.");
}
return("Already Paid");
}
protected void searchButton_Click(object sender, EventArgs e)
{
SearchView search = new SearchView();
if (billNoOrMobilenoSearchTextBox.Text == string.Empty)
{
messageLable.Text = "Plese Provide Bill Number or Mobile Number ";
}
string billNo = billNoOrMobilenoSearchTextBox.Text;
string mobileNo = billNoOrMobilenoSearchTextBox.Text;
TestRequestEntry testRequest = paymentManger.SearchByBillorMobile(billNo, mobileNo);
ViewState["testRequest"] = testRequest;
if (ViewState["testRequest"] == null)
{
messageLable.Text = "Not Data Found ! Please try Again";
billNoOrMobilenoSearchTextBox.Text = "";
}
else
{
List <SearchView> searches = paymentManger.GetAllBillInfo(billNo, mobileNo);
billDateLable.Text = testRequest.DueDate.ToShortDateString();
totalFeeLable.Text = testRequest.TotalAmount.ToString();
if (testRequest.PaymentStatus == 0)
{
paidAmountLable.Text = 0.ToString();
}
else
{
paidAmountLable.Text = (testRequest.TotalAmount - testRequest.PaymentStatus).ToString();
}
if (testRequest.PaymentStatus == 0)
{
dueAmountLable.Text = testRequest.TotalAmount.ToString();
}
else
{
dueAmountLable.Text = testRequest.PaymentStatus.ToString();
}
billInfoGridView.DataSource = searches;
billInfoGridView.DataBind();
}
}
public List <TestRequestEntry> GetAllRequests(List <TestRequestEntry> aEntries)
{
List <TestRequestEntry> aTestRequestLists = new List <TestRequestEntry>();
TestRequestEntry aTestRequest = new TestRequestEntry();
foreach (TestRequestEntry aRequest in aEntries)
{
aTestRequest.TestId = aRequest.TestId;
aTestRequest.TestName = aRequest.TestName;
aTestRequest.Fee = aRequest.Fee;
aTestRequest.TotalAmount += aRequest.Fee;
aTestRequestLists.Add(aTestRequest);
}
return(aTestRequestLists);
}
protected void saveButton_Click(object sender, EventArgs e)
{
TestRequestEntry entry = new TestRequestEntry();
entry.NameOfPatient = nameOfThePatientTextBox.Text;
entry.DateOfBirth = dateOfBirthTextBox.Text;
entry.MobileNo = mobileNoTextBox.Text;
entry.TotalFee = Convert.ToDouble(totalTextBox.Text);
entry.BillNo = DateTime.Now.ToString().GetHashCode().ToString("X");
entry.InvoiceDate = DateTime.Now.ToString();
messageLabel.Text = testRequestEntryManager.SavePatientData(entry);
if (messageLabel.Text == "Patient Saved Successfully")
{
foreach (GridViewRow view in testRequestEntryGridView.Rows)
{
entry.TestName = ((Label)view.FindControl("testNameLabel")).Text;
entry.Fee = Convert.ToDouble(((Label)view.FindControl("feeLabel")).Text);
messageLabel2.Text = testRequestEntryManager.SaveBillInfo(entry);
}
if (messageLabel.Text == "Patient Saved Successfully" && messageLabel2.Text == "Info Saved")
{
GeneratePdf(entry.BillNo, entry.InvoiceDate, entry.NameOfPatient, entry.DateOfBirth, entry.MobileNo, entry.TotalFee);
}
}
LoadTests();
nameOfThePatientTextBox.Text = String.Empty;
nameOfThePatientTextBox.Enabled = true;
dateOfBirthTextBox.Text = String.Empty;
dateOfBirthTextBox.Enabled = true;
mobileNoTextBox.Text = String.Empty;
mobileNoTextBox.Enabled = true;
selectTestDropDownList.SelectedIndex = 0;
feeTextBox.Text = String.Empty;
totalTextBox.Text = 0.ToString();
saveButton.Visible = false;
resetButton.Visible = false;
totalLabel.Visible = false;
totalTextBox.Visible = false;
testRequestEntryGridView.DataSource = null;
testRequestEntryGridView.DataBind();
}
public int SavePatientData(TestRequestEntry entry)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "INSERT INTO testRequestEntry VALUES(@name, @dob, @mobile, @total, @billNo, @status,@invoiceDate)";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@name", entry.NameOfPatient);
command.Parameters.AddWithValue("@dob", entry.DateOfBirth);
command.Parameters.AddWithValue("@mobile", entry.MobileNo);
command.Parameters.AddWithValue("@total", entry.TotalFee);
command.Parameters.AddWithValue("@billNO", entry.BillNo);
command.Parameters.AddWithValue("@status", "Unpaid");
command.Parameters.AddWithValue("@invoiceDate", entry.InvoiceDate);
connection.Open();
int rowaffected = command.ExecuteNonQuery();
connection.Close();
return(rowaffected);
}
protected void payButton_Click(object sender, EventArgs e)
{
string billNo = billNoOrMobilenoSearchTextBox.Text;
string mobileNo = billNoOrMobilenoSearchTextBox.Text;
SearchView search = new SearchView();
TestRequestEntry testRequest = paymentManger.SearchByBillorMobile(billNo, mobileNo);
ViewState["testRequest"] = testRequest;
decimal total = 0;
decimal amount = Convert.ToDecimal(amountTextBox.Text);
if (testRequest.PaymentStatus == 0)
{
total = testRequest.TotalAmount - amount;
}
else
{
total = testRequest.PaymentStatus - amount;
}
if (amountTextBox.Text == string.Empty)
{
messageLable.Text = "Please Provide Bill number or Mobile Number";
}
if (paymentManger.UpdatePaymentStatus(testRequest.BillNo, testRequest.MobileNo, total) > 0)
{
messageLable.Text = "Payment Successfull";
billDateLable.Text = testRequest.DueDate.ToShortDateString();
totalFeeLable.Text = testRequest.TotalAmount.ToString();
paidAmountLable.Text = amountTextBox.Text;
dueAmountLable.Text = total.ToString();
}
}
protected void saveButton_Click(object sender, EventArgs e)
{
try
{
List <int> testsIdList = ViewState["testsId"] as List <int>;
if (testsIdList == null)
{
messageLable.Text = "please Select at least one test";
}
else
{
TestRequestEntry testRequest = new TestRequestEntry();
testRequest.PatientName = patientNameTextBox.Text;
testRequest.Dob = dobTextBox.Text;
testRequest.MobileNo = mobileNoTextBox.Text;
testRequest.TotalAmount = (decimal)ViewState["TotalAmount"];
testRequest.DueDate = DateTime.Now;
testRequest.PaymentStatus = 0;
if (testRequestManager.SaveTestRequest(testRequest) > 0)
{
TestRequestEntry getTestRequest = testRequestManager.GetPatientByMobilelNo(testRequest.MobileNo);
int patientId = getTestRequest.PatientId;
string requesteDate = DateTime.Now.ToString();
foreach (int testId in testsIdList)
{
testRequestManager.SavepatientTest(patientId, testId, requesteDate);
}
messageLable.Text = "Saved Successfully";
if (FillBillLable(getTestRequest))
{
messageLable.Text = "Saved Successfully";
}
patientNameTextBox.Text = "";
dobTextBox.Text = "";
mobileNoTextBox.Text = "";
selectTestDropDownList.SelectedIndex = 0;
feeTextBox.Text = "";
GeneratePDF();
}
else
{
messageLable.Text = "Save Failed";
}
}
}
catch (Exception exception)
{
messageLable.Text = exception.Message;
}
patientNameTextBox.Text = "";
dobTextBox.Text = "";
mobileNoTextBox.Text = "";
selectTestDropDownList.SelectedIndex = 0;
feeTextBox.Text = "";
testRequestGridview.DataBind();
}
