public List <TestWiseReport> GetTestWiseReport(DateTime fromDate, DateTime toDate) { SqlConnection connection = new SqlConnection(connectionDB); string query = "exec spTestWiseReport '" + fromDate + "','" + toDate + "'"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <TestWiseReport> testWiseReport = new List <TestWiseReport>(); while (reader.Read()) { TestWiseReport testReport = new TestWiseReport(); testReport.TestName = reader["TestName"].ToString(); testReport.TotalNoTest = int.Parse(reader["TotalTest"].ToString()); testReport.TotalAmount = double.Parse(reader["TotalAmount"].ToString()); testWiseReport.Add(testReport); } reader.Close(); connection.Close(); return(testWiseReport); }
public List <TestWiseReport> GetTestInfoByDate(double fromDate, double todate) { List <TestWiseReport> testWiseReportList = new List <TestWiseReport>(); GetDateConfiguration dateConfiguration = new GetDateConfiguration(); int totalTest = 0; double totalAmount = 0; string test = ""; GenarateConnection(); string query = "SELECT * FROM TestWiseReportView ORDER BY TestName,PrintDate"; Command = new SqlCommand(query, Connection); Command.Parameters.Clear(); 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 (test.Equals(Reader["TestName"].ToString())) { totalTest = totalTest + 1; totalAmount = totalAmount + Convert.ToDouble(Reader["Fee"]); } else { if (!test.Equals("")) { TestWiseReport testWiseReport = new TestWiseReport(); testWiseReport.TestName = test; testWiseReport.TotalAmount = totalAmount; testWiseReport.TotalTest = totalTest; testWiseReportList.Add(testWiseReport); } test = Reader["TestName"].ToString(); totalTest = 1; totalAmount = Convert.ToDouble(Reader["Fee"]); } } } TestWiseReport testWiseReportForLast = new TestWiseReport(); testWiseReportForLast.TestName = test; testWiseReportForLast.TotalAmount = totalAmount; testWiseReportForLast.TotalTest = totalTest; testWiseReportList.Add(testWiseReportForLast); } Connection.Close(); return(testWiseReportList); }
public List <TestWiseReport> GetAllTestReports(string toDate, string fromDate) { List <TestWiseReport> aTestWiseReports = new List <TestWiseReport>(); List <TestTypeRequestView> aTestTypeRequestViews = aTestWiseReportGateway.GetAllTestTypeRequest(); List <Test> aTests = aTestGateway.GetAllTest(); List <int> testOccurList = new List <int>(); foreach (var testRequestView in aTestTypeRequestViews) { if ((string.Compare(testRequestView.DueDate, toDate) <= 0) && (string.Compare(testRequestView.DueDate, fromDate) >= 0)) { testOccurList.Add(testRequestView.TestId); } } foreach (var test in aTests) { TestWiseReport aTestWiseReport = new TestWiseReport(); aTestWiseReport.TestName = test.Name; int totalTest = 0; for (int i = 0; i < testOccurList.Count; i++) { if (test.Id == testOccurList[i]) { totalTest++; } } aTestWiseReport.TotalTest = totalTest; aTestWiseReport.TotalAmount = totalTest * test.Fee; aTestWiseReports.Add(aTestWiseReport); } return(aTestWiseReports); }
public List <TestWiseReport> GetDateWiseTestReport(string startDate, string endDate) { SqlConnection connection = new SqlConnection(connectionString); //string query = @"SELECT ts.testname, count(pt.id) as TotalCount, sum(ts.fee) as TotalFee FROM patient p, patientTest pt, testSetup ts WHERE // pt.patientID = p.id and // pt.testSetupID = ts.id and // pt.created_at BETWEEN '"+ startDate +"' AND '"+ endDate +"' GROUP BY ts.testname"; string query = @"SELECT ts.testname ,count(pt.id) as TotalCount, count(pt.id) * sum(DISTINCT ts.fee) as TotalFee FROM testSetup ts LEFT JOIN patientTest pt on pt.testSetupID = ts.id and pt.created_at BETWEEN '" + startDate + "' AND '" + endDate + "' GROUP BY ts.testname"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <TestWiseReport> testWiseReportList = new List <TestWiseReport>(); while (reader.Read()) { TestWiseReport testWiseReport = new TestWiseReport(); testWiseReport.TestName = reader["testname"].ToString(); testWiseReport.TotalCount = Convert.ToInt32(reader["TotalCount"].ToString()); testWiseReport.TotalFee = Convert.ToDecimal(reader["TotalFee"].ToString()); testWiseReportList.Add(testWiseReport); } reader.Close(); connection.Close(); return(testWiseReportList); }
public List <Models.ViewModel.TestWiseReport> GetAllTestWiseReport(DateTime fromDate, DateTime toDate) { List <TestWiseReport> aTestWiseReportList = new List <TestWiseReport>(); SqlConnection connection = new SqlConnection(connectionString); string query = "SELECT Tests.TestId, Tests.TestName, COUNT(r.TestId) AS TotalTests, ISNULL(SUM(r.Fee), 0) AS TotalAmount " + "FROM ( SELECT TestId, Fee " + "FROM vwPatients AS p " + "WHERE BillDate BETWEEN CONVERT(DATE, '" + fromDate.ToShortDateString() + "', 103) AND CONVERT(DATE, '" + toDate.ToShortDateString() + "', 103)) AS r " + "RIGHT OUTER JOIN Tests ON r.TestId = Tests.TestId " + "GROUP BY Tests.TestId, Tests.TestName "; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { TestWiseReport aTestWiseReport = new TestWiseReport(); aTestWiseReport.TestId = Convert.ToInt32(reader["TestId"].ToString()); aTestWiseReport.TestName = reader["TestName"].ToString(); aTestWiseReport.TotalTests = Convert.ToInt32(reader["TotalTests"].ToString()); aTestWiseReport.TotalAmount = Convert.ToDecimal(reader["TotalAmount"].ToString()); aTestWiseReport.fromDate = Convert.ToDateTime(fromDate.ToShortDateString()); aTestWiseReport.toDate = Convert.ToDateTime(toDate.ToShortDateString()); aTestWiseReportList.Add(aTestWiseReport); } reader.Close(); } connection.Close(); return(aTestWiseReportList); }
public List <TestWiseReport> GetAllTestDropdown() { List <TestWiseReport> TestList = new List <TestWiseReport>(); GenarateConnection(); using (Connection) { Connection.Open(); string query = "select * from Tests ORDER BY TestName;"; Command = new SqlCommand(query, Connection); Reader = Command.ExecuteReader(); while (Reader.Read()) { TestWiseReport test = new TestWiseReport(); test.TestName = Reader["TestName"].ToString(); test.TotalAmount = 0; test.TotalTest = 0; TestList.Add(test); } Connection.Close(); } return(TestList); }