internal List <DateWiseTestReport> NotGetDateWiseTestReport()
{
SqlConnection connection = new SqlConnection(connectionString);
string query = @"select TestId,TestName from Tests where TestId not in (select t.TestId from Tests t,PatientTests pt where t.TestId=pt.TestId)";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <DateWiseTestReport> testWiseReportList = new List <DateWiseTestReport>();
while (reader.Read())
{
DateWiseTestReport testReport = new DateWiseTestReport();
testReport.TestName = reader["TestName"].ToString();
testReport.TotalTest = 0;
testReport.TotalFee = 0;
testWiseReportList.Add(testReport);
}
reader.Close();
connection.Close();
return(testWiseReportList);
}
public List <DateWiseTestReport> GetDateWiseTestReport(string startDate, string endDate)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = @"SELECT TestName,SUM(TotalCount) as TotalCount,SUM(Fee) as TotalFee from DateWiseTestReport WHERE InsertDate BETWEEN '" + startDate + "' AND '" + endDate + "' group by TestName";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <DateWiseTestReport> testWiseReportList = new List <DateWiseTestReport>();
while (reader.Read())
{
DateWiseTestReport testReport = new DateWiseTestReport();
testReport.TestName = reader["TestName"].ToString();
testReport.TotalCount = Convert.ToInt32(reader["TotalCount"].ToString());
testReport.TotalFee = Convert.ToDecimal(reader["TotalFee"].ToString());
testWiseReportList.Add(testReport);
}
reader.Close();
connection.Close();
return(testWiseReportList);
}
internal List <DateWiseTestReport> GetDateWiseTestReport(string startDate, string endDate)
{
//string query = "Create View DateWiseTestReport AS SELECT t.TestName, t.TestFee, count(t.Tid) as TestCount,count(t.Tid)*t.TestFee AS TotalFee, pt.RequestDate FROM Test T INNER JOIN PatientTest pt ON t.[Tid] = pt.[TestId] group by t.TestName, t.TestFee,pt.RequestDate";
//command = new SqlCommand(query,connection);
//connection.Open();
//command.ExecuteNonQuery();
//connection.Close();
string query = @"SELECT TestName, SUM(TestCount) as TestCount,SUM(TotalFee) as TotalFee from DateWiseTestReport WHERE RequestDate BETWEEN '" + startDate + "' AND '" + endDate + "' group by TestName";
command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <DateWiseTestReport> testWiseReportList = new List <DateWiseTestReport>();
while (reader.Read())
{
DateWiseTestReport testReport = new DateWiseTestReport();
testReport.TestName = reader["TestName"].ToString();
testReport.TotalTest = Convert.ToInt32(reader["TestCount"].ToString());
testReport.TotalFee = Convert.ToInt32(reader["TotalFee"].ToString());
testWiseReportList.Add(testReport);
}
reader.Close();
connection.Close();
return(testWiseReportList);
}
