public List <TypeWiseReport> GetTypeWiseReport(DateTime fromDate, DateTime toDate)
{
SqlConnection connection = new SqlConnection(connectionDB);
string query = "exec spTypeWiseReport '" + fromDate + "','" + toDate + "'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <TypeWiseReport> typeWiseReports = new List <TypeWiseReport>();
while (reader.Read())
{
TypeWiseReport typeReport = new TypeWiseReport();
typeReport.TestTypeName = reader["TypeName"].ToString();
typeReport.TotalNoTest = int.Parse(reader["TotalCount"].ToString());
typeReport.TotalAmount = double.Parse(reader["TotalAmount"].ToString());
typeWiseReports.Add(typeReport);
}
reader.Close();
connection.Close();
return(typeWiseReports);
}
public List <TypeWiseReport> GetTestTypeInfoByDate(double fromDate, double todate)
{
List <TypeWiseReport> typeWiseReportList = new List <TypeWiseReport>();
GetDateConfiguration dateConfiguration = new GetDateConfiguration();
int totalTest = 0;
double totalAmount = 0;
string type = "";
GenarateConnection();
string query = "SELECT * FROM TypeWiseReportView ORDER BY TypeName,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)
{
string newtypename = Reader["TypeName"].ToString();
if (type.Equals(newtypename))
{
totalTest = totalTest + 1;
totalAmount = totalAmount + Convert.ToDouble(Reader["Fee"]);
}
else
{
if (!type.Equals(""))
{
TypeWiseReport typeWiseReport = new TypeWiseReport();
typeWiseReport.TestTypeName = type;
typeWiseReport.TotalAmount = totalAmount;
typeWiseReport.TotalNoOfTest = totalTest;
typeWiseReportList.Add(typeWiseReport);
}
type = Reader["TypeName"].ToString();
totalTest = 1;
totalAmount = Convert.ToDouble(Reader["Fee"]);
}
}
}
TypeWiseReport typeWiseReportForLast = new TypeWiseReport();
typeWiseReportForLast.TestTypeName = type;
typeWiseReportForLast.TotalAmount = totalAmount;
typeWiseReportForLast.TotalNoOfTest = totalTest;
typeWiseReportList.Add(typeWiseReportForLast);
}
Connection.Close();
return(typeWiseReportList);
}
public List <TypeWiseReport> GetAllTestTypeReports(string toDate, string fromDate)
{
List <TypeWiseReport> aTypeWiseReports = new List <TypeWiseReport>();
List <TestTypeRequestView> aTestTypeRequestViews = aTestWiseReportGateway.GetAllTestTypeRequest();
List <TestType> aTestTypes = aTestTypeGateway.GetAllTestTypes();
List <int> testOccurList = new List <int>();
List <int> testMoneyList = new List <int>();
foreach (var testTypeRequestView in aTestTypeRequestViews)
{
if ((string.Compare(testTypeRequestView.DueDate, toDate) <= 0) && (string.Compare(testTypeRequestView.DueDate, fromDate) >= 0))
{
testOccurList.Add(testTypeRequestView.TypeId);
testMoneyList.Add(testTypeRequestView.Fee);
}
}
foreach (var type in aTestTypes)
{
TypeWiseReport aTypeWiseReport = new TypeWiseReport();
aTypeWiseReport.TestTypeName = type.Name;
int totalType = 0;
int totalMoney = 0;
for (int i = 0; i < testOccurList.Count; i++)
{
if (type.Id == testOccurList[i])
{
totalType++;
totalMoney += testMoneyList[i];
}
}
aTypeWiseReport.TotalTestType = totalType;
aTypeWiseReport.TotalAmount = totalMoney;
aTypeWiseReports.Add(aTypeWiseReport);
}
return(aTypeWiseReports);
}
public List <Models.ViewModel.TypeWiseReport> GetAllTypeWiseReport(DateTime fromDate, DateTime toDate)
{
List <TypeWiseReport> aTypeWiseReportList = new List <TypeWiseReport>();
SqlConnection connection = new SqlConnection(connectionString);
string query =
"SELECT TestTypes.TestTypeId, TestTypes.TestTypeName, COUNT(r.TestTypeId) AS TotalTests, ISNULL(SUM(r.Fee), 0) AS TotalAmount " +
"FROM ( SELECT TestTypeId, Fee " +
"FROM vwPatients AS p " +
"WHERE BillDate BETWEEN CONVERT(DATE, '" + fromDate.ToShortDateString() + "', 103) AND CONVERT(DATE, '" + toDate.ToShortDateString() + "', 103)) AS r " +
"RIGHT OUTER JOIN TestTypes ON r.TestTypeId = TestTypes.TestTypeId " +
"GROUP BY TestTypes.TestTypeId, TestTypes.TestTypeName ";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
TypeWiseReport aTypeWiseReport = new TypeWiseReport();
aTypeWiseReport.TestTypeId = Convert.ToInt32(reader["TestTypeId"].ToString());
aTypeWiseReport.TestTypeName = reader["TestTypeName"].ToString();
aTypeWiseReport.TotalTests = Convert.ToInt32(reader["TotalTests"].ToString());
aTypeWiseReport.TotalAmount = Convert.ToDecimal(reader["TotalAmount"].ToString());
aTypeWiseReport.fromDate = Convert.ToDateTime(fromDate.ToShortDateString());
aTypeWiseReport.toDate = Convert.ToDateTime(toDate.ToShortDateString());
aTypeWiseReportList.Add(aTypeWiseReport);
}
reader.Close();
}
connection.Close();
return(aTypeWiseReportList);
}
public List <TypeWiseReport> GetTestTypeList()
{
List <TypeWiseReport> typeWiseReportList = new List <TypeWiseReport>();
GenarateConnection();
using (Connection)
{
Connection.Open();
string query = "select * from Types ORDER BY TypeName;";
Command = new SqlCommand(query, Connection);
Reader = Command.ExecuteReader();
while (Reader.Read())
{
TypeWiseReport typeWiseReport = new TypeWiseReport();
typeWiseReport.TestTypeName = Reader["TypeName"].ToString();
typeWiseReport.TotalAmount = 0;
typeWiseReport.TotalNoOfTest = 0;
typeWiseReportList.Add(typeWiseReport);
}
Connection.Close();
}
return(typeWiseReportList);
}
