/// <summary>
/// Retrieves the quantity and total value for each product type of given statuses at a given distribution centre.
/// </summary>
/// <param name="distributionCentreId">distribution centre id</param>
/// <param name="statuses">package statuses</param>
/// <returns>a list of MedicationTypeViewData</returns>
public List <MedicationTypeViewData> FindDistributionCentreStockByStatus(int distributionCentreId, params PackageStatus[] statuses)
{
List <MedicationTypeViewData> list = new List <MedicationTypeViewData>();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
string query = @"select b.Name, count(*), sum(b.Value)
from MedicationPackage a
join MedicationType b on a.Type = b.Id
where a.StockDC = @id
and a.Status in ({0})
group by b.Name" ;
string[] paramNames = statuses.Select((s, i) => "@status" + i.ToString()).ToArray();
string inClause = string.Join(",", paramNames);
string cmdText = string.Format(query, inClause);
SqlCommand command = new SqlCommand(cmdText, conn);
command.Parameters.Add(new SqlParameter("id", distributionCentreId));
for (int i = 0; i < paramNames.Length; i++)
{
command.Parameters.Add(new SqlParameter(paramNames[i], statuses[i]));
}
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
MedicationTypeViewData row = GetDistributionCentreStockViewDataFromDataReader(reader);
list.Add(row);
}
}
}
return(list);
}
/// <summary>
/// Retrieves the quantity and total value for each product type distributed by a given doctor.
/// </summary>
/// <param name="username">doctor username</param>
/// <returns>a list of MedicationTypeViewData</returns>
public List <MedicationTypeViewData> FindDoctorActivityByUserName(string username)
{
List <MedicationTypeViewData> list = new List <MedicationTypeViewData>();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
string query = @"select b.Name, count(*), sum(b.Value)
from MedicationPackage a
join MedicationType b on a.Type = b.Id
where a.Operator = @username
and a.Status = @status
group by b.Name" ;
SqlCommand command = new SqlCommand(query, conn);
command.Parameters.Add(new SqlParameter("username", username));
command.Parameters.Add(new SqlParameter("status", PackageStatus.Distributed));
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
MedicationTypeViewData row = GetDistributionCentreStockViewDataFromDataReader(reader);
list.Add(row);
}
}
}
return(list);
}
/// <summary>
/// Helper-method to create a medication type view data for a row of the database.
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
MedicationTypeViewData GetDistributionCentreStockViewDataFromDataReader(SqlDataReader reader)
{
var row = new MedicationTypeViewData
{
Type = reader.GetString(0),
Quantity = reader.GetInt32(1),
Value = reader.GetDecimal(2)
};
return(row);
}
