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); }