public List <TypeNameModel> GetAllTypes()
{
Query = "SELECT * FROM TestType ORDER BY TypeName";
Command = new SqlCommand(Query, Connection);
Connection.Open();
List <TypeNameModel> types = new List <TypeNameModel>();
Reader = Command.ExecuteReader();
while (Reader.Read())
{
TypeNameModel type = new TypeNameModel();
type.TypeName = Reader["TypeName"].ToString();
type.Id = (Int32)Reader["Id"];
types.Add(type);
}
Reader.Close();
Connection.Close();
return(types);
}
protected void saveButton_Click(object sender, EventArgs e)
{
TypeNameModel name = new TypeNameModel();
name.TypeName = typeNameTextBox.Text;
messagelabel.Text = anManager.Save(name);
typeNameTextBox.Text = string.Empty;
PopulateGridview();
}
public List <TypeNameModel> GetTypeWiseReport(string fromDate, string toDate)
{
Query = @"SELECT TypeName,COUNT(TestNameInfo.TypeId) AS TotalTest, COUNT(TestNameInfo.TypeId) *Fee AS TotalAmount
FROM TestType
LEFT JOIN TestNameInfo ON TestType.Id=TestNameInfo.TypeId
LEFT JOIN PatientTest ON TestNameInfo.Id=PatientTest.TestId
Where DueDate Between '" + fromDate + "' AND '" + toDate + "' GROUP BY Typename, Fee UNION ALL " +
"SELECT TypeName,COUNT(TestNameInfo.TypeId)*0 AS TotalTest, COUNT(TestNameInfo.TypeId) *Fee*0 AS TotalAmount " +
"FROM TestType LEFT JOIN TestNameInfo ON TestType.Id=TestNameInfo.TypeId LEFT JOIN PatientTest ON TestNameInfo.Id=PatientTest.TestId " +
"where (PatientTest.DueDate is null OR PatientTest.DueDate NOT BETWEEN '" + fromDate + "' AND '" + toDate + "') " +
"AND TypeName NOT IN (SELECT TypeName FROM TestType LEFT JOIN TestNameInfo ON TestType.Id=TestNameInfo.TypeId " +
"LEFT JOIN PatientTest ON TestNameInfo.Id=PatientTest.TestId WHERE PatientTest.DueDate NOT BETWEEN '" + fromDate + "' AND '" + toDate + "') " +
"GROUP BY Typename, Fee";
Command = new SqlCommand(Query, Connection);
Connection.Open();
List <TypeNameModel> types = new List <TypeNameModel>();
Reader = Command.ExecuteReader();
while (Reader.Read())
{
TypeNameModel test = new TypeNameModel();
test.TypeName = Reader["TypeName"].ToString();
test.NumberOfTests = Convert.ToInt32(Reader["TotalTest"]);
if (Reader["TotalAmount"] == DBNull.Value)
{
test.TotalAmount = 0;
}
else
{
test.TotalAmount = Convert.ToDouble(Reader["TotalAmount"]);
}
types.Add(test);
}
Reader.Close();
Connection.Close();
return(types);
}
public int Save(TypeNameModel type)
{
Query = "INSERT INTO TestType VALUES(@typeName)";
Command = new SqlCommand(Query, Connection);
Command.Parameters.Clear();
Command.Parameters.Add("typeName", SqlDbType.VarChar);
Command.Parameters["typeName"].Value = type.TypeName;
Connection.Open();
int rowAffected = Command.ExecuteNonQuery();
Connection.Close();
return(rowAffected);
}
public string Save(TypeNameModel type)
{
bool existsTypeName = aGateway.IsTestTypeExists(type.TypeName);
if (existsTypeName)
{
return("Type Name exists already");
}
else
{
int rowAffected = aGateway.Save(type);
if (rowAffected > 0)
{
return("Saved Successfully");
}
else
{
return("Saved failed");
}
}
}
