public Alert GetAlerts(int companyId, int userId)
{
Alert alert = new Alert();
using (var connection = new SqlConnection(PrescienceRxConnectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = @"SELECT COUNT(*) FROM dbo.Workflow WHERE CompanyId = @CompanyId AND WorkerID = @UserId AND StatusID <> 3";
command.Parameters.AddWithValue("@UserId", userId);
command.Parameters.AddWithValue("@CompanyId", companyId);
var reader = command.ExecuteScalar();
if (reader != null)
{
alert.AssignedItems = Convert.ToInt32(reader);
}
command.CommandText = @"SELECT COUNT(*) FROM [dbo].[Customers] c INNER JOIN [dbo].Appointments a ON c.CustomerId = a.CustomerId " +
" INNER JOIN [dbo].[Professional] p on a.ProfessionalId = p.ProfessionalId " +
" WHERE @CompanyID = p.CompanyId AND StartTime > GetDate() ";
command.Parameters.Clear();
command.Parameters.AddWithValue("@CompanyId", companyId);
reader = command.ExecuteScalar();
if (reader != null)
{
alert.NoOfAppointments = Convert.ToInt32(reader);
}
command.CommandText = @"SELECT COUNT(*) FROM dbo.Workflow WHERE CompanyId = @CompanyId AND WorkerID = @UserId AND StatusID <> 3 AND DueDate < GetDate()";
command.Parameters.Clear();
command.Parameters.AddWithValue("@UserId", userId);
command.Parameters.AddWithValue("@CompanyId", companyId);
reader = command.ExecuteScalar();
if (reader != null)
{
alert.DueDateItems = Convert.ToInt32(reader);
}
}
connection.Dispose();
}
return alert;
}
