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