internal List <UnpaidBillReport> GetUnPaidBillList(string endDate, string startDate)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = @"SELECT * FROM Patient WHERE DueDate BETWEEN '" + endDate + "' AND '" + startDate + "'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <UnpaidBillReport> unPaidBillList = new List <UnpaidBillReport>();
if (reader.HasRows)
{
while (reader.Read())
{
UnpaidBillReport unpaidBill = new UnpaidBillReport();
unpaidBill.BillNo = reader["Billno"].ToString();
unpaidBill.MobileNo = reader["MobileNo"].ToString();
unpaidBill.PatientName = reader["PatientName"].ToString();
unpaidBill.PaymentStatus = Convert.ToDecimal(reader["PaymentStatus"].ToString());
unpaidBill.TotalAmount = Convert.ToDecimal(reader["TotalAmount"].ToString());
unPaidBillList.Add(unpaidBill);
}
reader.Close();
}
connection.Close();
return(unPaidBillList);
}
public List <UnpaidBillReport> GetUnpaidBillReport(DateTime fromDate, DateTime toDate)
{
SqlConnection connection = new SqlConnection(connectionDB);
string query = "exec spUnpaidBillReport '" + fromDate + "','" + toDate + "'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <UnpaidBillReport> unpaidBillReports = new List <UnpaidBillReport>();
while (reader.Read())
{
UnpaidBillReport unpaidBillReport = new UnpaidBillReport();
unpaidBillReport.BillNo = Convert.ToInt32(reader["BillNo"].ToString());
unpaidBillReport.PatientMobileNo = reader["MobileNo"].ToString();
unpaidBillReport.PatientName = reader["PatientName"].ToString();
unpaidBillReport.UnpaidBillAmount = double.Parse(reader["DueAmount"].ToString());
unpaidBillReports.Add(unpaidBillReport);
}
reader.Close();
connection.Close();
return(unpaidBillReports);
}
public List <Models.ViewModel.UnpaidBillReport> GetAllUnpaidBillReports(DateTime fromDate, DateTime toDate)
{
List <UnpaidBillReport> aUnpaidBillReportList = new List <UnpaidBillReport>();
SqlConnection connection = new SqlConnection(connectionString);
string query =
"SELECT u.PatientId,u.PatientName, u.MobileNo,u.BillNo,u.TotalFee AS TotalAmount, u.PaidAmount,u.DueAmount " +
"FROM vwBillingSummary AS u " +
"WHERE BillDate BETWEEN CONVERT(DATE, '" + fromDate.ToShortDateString() + "', 103) AND CONVERT(DATE, '" + toDate.ToShortDateString() + "', 103)";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
UnpaidBillReport aUnpaidBillReport = new UnpaidBillReport();
aUnpaidBillReport.PatientId = Convert.ToInt32(reader["PatientId"].ToString());
aUnpaidBillReport.PatientName = reader["PatientName"].ToString();
aUnpaidBillReport.MobileNo = reader["MobileNo"].ToString();
aUnpaidBillReport.BillNo = reader["BillNo"].ToString();
aUnpaidBillReport.TotalAmount = Convert.ToDecimal(reader["TotalAmount"].ToString());
aUnpaidBillReport.PaidAmount = Convert.ToDecimal(reader["PaidAmount"].ToString());
aUnpaidBillReport.DueAmount = Convert.ToDecimal(reader["DueAmount"].ToString());
aUnpaidBillReport.fromDate = Convert.ToDateTime(fromDate.ToShortDateString());
aUnpaidBillReport.toDate = Convert.ToDateTime(toDate.ToShortDateString());
aUnpaidBillReportList.Add(aUnpaidBillReport);
}
reader.Close();
}
connection.Close();
return(aUnpaidBillReportList);
}
public List <UnpaidBillReport> GetUnPaidReport(double fromDate, double todate)
{
List <UnpaidBillReport> unpaidBillReportList = new List <UnpaidBillReport>();
GetDateConfiguration dateConfiguration = new GetDateConfiguration();
double billAmount = 0;
string billNumber = "";
string mobileNo = "";
string patientName = "";
GenarateConnection();
string query = "SELECT * FROM TestRequestView WHERE PaymentStatus =@PaymentStatus ORDER BY BillNumber;";
Command = new SqlCommand(query, Connection);
Command.Parameters.Clear();
Command.Parameters.Add("@PaymentStatus", SqlDbType.VarChar);
Command.Parameters["@PaymentStatus"].Value = "0";
Connection.Open();
Reader = Command.ExecuteReader();
if (Reader.HasRows)
{
while (Reader.Read())
{
double printDate =
Convert.ToDouble(dateConfiguration.GetDateInDouble(Reader["PrintDate"].ToString()));
if (fromDate <= printDate & todate >= printDate)
{
if (billNumber.Equals(Reader["BillNumber"].ToString()))
{
billAmount = billAmount + Convert.ToDouble(Reader["Fee"]);
}
else
{
if (!billNumber.Equals(""))
{
UnpaidBillReport unpaidBillReport = new UnpaidBillReport();
unpaidBillReport.BillNumber = billNumber;
unpaidBillReport.BillAmount = billAmount;
unpaidBillReport.PatientName = patientName;
unpaidBillReport.MobileNo = mobileNo;
unpaidBillReportList.Add(unpaidBillReport);
}
billNumber = Reader["BillNumber"].ToString();
mobileNo = Reader["MobileNo"].ToString();
patientName = Reader["Name"].ToString();
billAmount = Convert.ToDouble(Reader["Fee"]);
}
}
}
UnpaidBillReport unpaidBillReports = new UnpaidBillReport();
unpaidBillReports.BillNumber = billNumber;
unpaidBillReports.BillAmount = billAmount;
unpaidBillReports.PatientName = patientName;
unpaidBillReports.MobileNo = mobileNo;
unpaidBillReportList.Add(unpaidBillReports);
}
Connection.Close();
return(unpaidBillReportList);
}