/// <summary>
/// Get the list of costs and labours grouped by district
/// </summary>
/// <returns></returns>
public List <CostByDistrict> GetCostLabourListByDistrict()
{
List <CostByDistrict> aCostByDistrictList = new List <CostByDistrict>();
connectionString = aDatabaseConfig.Setup("ENETCareDatabase");
using (SqlConnection connection = new SqlConnection())
{
connection.ConnectionString = connectionString;
string query = "Select u.DistrictName, SUM(i.LabourRequired) as LabourHour, sum(i.CostRequired) as Cost from (Select u.UserID as UserID, d.DistrictName as DistrictName from [User] as u join [District] as d on u.DistrictID = d.DistrictID) as u join [Intervention] as i on u.UserID = i. UserID where i.InterventionState='Completed' Group By DistrictName";
//"from (Select u.UserID as UserID, d.DistrictName as DistrictName from [User] as u join [District] as d on u.DistrictID = d.DistrictID) as u join [Intervention] as i" +
//"on u.UserID = i. UserID Group By DistrictName";
SqlCommand command = new SqlCommand(query, connection);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
CostByDistrict aCostByDistrict = new CostByDistrict();
aCostByDistrict.DistrictName = reader["DistrictName"].ToString();
aCostByDistrict.LabourHour = float.Parse(reader["LabourHour"].ToString());
aCostByDistrict.Cost = float.Parse(reader["Cost"].ToString());
aCostByDistrictList.Add(aCostByDistrict);
}
}
catch { }
}
return(aCostByDistrictList);
}
public ActionResult MonthlyCostsForDistrict(int?id)
{
List <CostByDistrict> resultList = new List <CostByDistrict>();
int[] monthList = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };
foreach (var month in monthList)
{
CostByDistrict result = db.Interventions.ToList()
.Where(i => i.InterventionState == InterventionState.Completed).Where(i => i.User.DistrictID == id).Where(i => DateTime.Parse(i.InterventionDate, CultureInfo.CurrentCulture).Month == month)
.GroupBy(i => i.InterventionDate)
.Select(set => new CostByDistrict
{
TotalCost = set.Sum(i => i.CostRequired).ToString(),
TotalLabour = set.Sum(i => i.LabourRequired).ToString(),
Date = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(month),
}).FirstOrDefault();
if (result == null)
{
result = new CostByDistrict();
result.Date = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(month);
result.TotalCost = "0";
result.TotalLabour = "0";
}
resultList.Add(result);
}
return(View(resultList));
}
public ActionResult CostByDistrictReport()
{
List <CostByDistrict> resultList = new List <CostByDistrict>();
List <District> aDistrictList = db.Districts.ToList();
foreach (var district in aDistrictList)
{
CostByDistrict result = db.Interventions
.Where(i => i.InterventionState == InterventionState.Completed).Where(i => i.User.DistrictID == district.DistrictID)
.GroupBy(i => i.User.DistrictID)
.Select(set => new CostByDistrict
{
DistrictName = district.DistrictName,
TotalCost = set.Sum(i => i.CostRequired).ToString(),
TotalLabour = set.Sum(i => i.LabourRequired).ToString(),
}).FirstOrDefault();
if (result == null)
{
result = new CostByDistrict();
result.DistrictName = district.DistrictName;
result.TotalCost = "0";
result.TotalLabour = "0";
}
resultList.Add(result);
}
return(View(resultList));
}
public void Validate_Report_For_CostByDistrict_Given_Valid_ExpectNoValidationError()
{
var model = new CostByDistrict()
{
Date = "7/06/2017",
TotalCost = "56",
TotalLabour = "35",
DistrictName = "Rhodes"
};
var ErrorMeassageList = TestModelHelper.Validate(model);
Assert.AreEqual(0, ErrorMeassageList.Count);
}
