internal List <DateWiseTypeReport> GetDateWiseTestReport(string startDate, string endDate)
{
SqlConnection connection = new SqlConnection(connectionString);
string qurey = @"SELECT TestTypeName, SUM(TestCount) as TestCount,COALESCE(SUM(TotalFee),0) as TotalFee FROM DateWiseTestTypeReport
WHERE RequestDate BETWEEN '" + startDate + "' AND '" + endDate + "' group by TestTypeName";
SqlCommand command = new SqlCommand(qurey, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <DateWiseTypeReport> typeWisReportList = new List <DateWiseTypeReport>();
while (reader.Read())
{
DateWiseTypeReport testReport = new DateWiseTypeReport();
testReport.TestTypeName = reader["TestTypeName"].ToString();
testReport.TotalTest = Convert.ToInt32(reader["TestCount"].ToString());
testReport.TotalFee = Convert.ToInt32(reader["TotalFee"].ToString());
typeWisReportList.Add(testReport);
}
reader.Close();
connection.Close();
return(typeWisReportList);
}
internal List <DateWiseTypeReport> NotGetDateWiseTestreport()
{
SqlConnection connection = new SqlConnection(connectionString);
string query = @"select TestTypeId,TestTypeName from TestType where TestTypeId not in (select tt.TestTypeId from TestType tt,Tests t where tt.TestTypeId=t.TestTypeId)";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <DateWiseTypeReport> typeWiseReportList = new List <DateWiseTypeReport>();
while (reader.Read())
{
DateWiseTypeReport testReport = new DateWiseTypeReport();
testReport.TestTypeName = reader["TestTypeName"].ToString();
testReport.TotalTest = 0;
testReport.TotalFee = 0;
typeWiseReportList.Add(testReport);
}
reader.Close();
connection.Close();
return(typeWiseReportList);
}
internal List <DateWiseTypeReport> GetDateWiseTypeReport(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 TestTypeName, SUM(TestCount) as TestCount,COALESCE(SUM(TotalFee),0) as TotalFee FROM DateWiseTestTypeReport
WHERE RequestDate BETWEEN '" + startDate + "' AND '" + endDate + "' group by TestTypeName";
command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <DateWiseTypeReport> testWiseReportList = new List <DateWiseTypeReport>();
while (reader.Read())
{
DateWiseTypeReport testReport = new DateWiseTypeReport();
testReport.TestTypeName = reader["TestTypeName"].ToString();
testReport.TotalTest = Convert.ToInt32(reader["TestCount"].ToString());
testReport.TotalFee = Convert.ToInt32(reader["TotalFee"].ToString());
testWiseReportList.Add(testReport);
}
reader.Close();
connection.Close();
return(testWiseReportList);
}
