public List <TypeWiseTestReport> GetTypeWiseTestReport(string startDate, string endDate)
{
string query = @"SELECT TT.NAME AS TypeName, COUNT(PT.id) as TotalCount, ISNULL(SUM(DISTINCT TS.FEE) * COUNT(PT.ID), 0) as TotalFee FROM TESTTYPE TT
LEFT JOIN TESTSETUP TS ON TS.type_id = TT.id
LEFT JOIN PATIENTTEST PT ON PT.testSetupID = TS.id
AND PT.created_at BETWEEN '" + startDate + "' AND '" + endDate + "' GROUP BY TT.name";
command.CommandText = query;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <TypeWiseTestReport> testWiseReportList = new List <TypeWiseTestReport>();
while (reader.Read())
{
TypeWiseTestReport typeWiseTestReport = new TypeWiseTestReport();
typeWiseTestReport.TypeName = reader["TypeName"].ToString();
typeWiseTestReport.TotalCount = Convert.ToInt32(reader["TotalCount"].ToString());
typeWiseTestReport.TotalFee = Convert.ToDecimal(reader["TotalFee"].ToString());
testWiseReportList.Add(typeWiseTestReport);
}
reader.Close();
connection.Close();
return(testWiseReportList);
}
public List <TypeWiseTestReport> GetAllTypeWiseReport(string fromDate, string toDate)
{
SqlConnection con = new SqlConnection(connectionString);
string query = @"SELECT Name,SUM(Total) as Total,SUM(Count) as Count FROM TypeWiseTestReport
WHERE InsertDate between '" + fromDate + "' and '" + toDate + "' GROUP BY Name";
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
List <TypeWiseTestReport> typeWiseTestReports = new List <TypeWiseTestReport>();
while (reader.Read())
{
TypeWiseTestReport typeWiseTest = new TypeWiseTestReport();
typeWiseTest.Name = (reader["Name"].ToString());
typeWiseTest.Total = Convert.ToDecimal(reader["Total"].ToString());
typeWiseTest.Count = Convert.ToInt32(reader["Count"].ToString());
typeWiseTestReports.Add(typeWiseTest);
}
reader.Close();
con.Close();
return(typeWiseTestReports);
}
