public ActionResult AverageCostByEngineerReport()
{
List <SiteEngineerTotalCost> resultList = new List <SiteEngineerTotalCost>();
List <User> anUserList = db.Users.Where(u => u.UserType == "SiteEngineer").OrderBy(u => u.UserName).ToList();
foreach (var user in anUserList)
{
SiteEngineerTotalCost result = db.Interventions
.Where(i => (i.InterventionState == InterventionState.Completed) && (i.UserID == user.UserID))
.GroupBy(i => i.UserID)
.Select(setc => new SiteEngineerTotalCost
{
UserName = user.UserName,
TotalCost = setc.Average(i => i.CostRequired).ToString(),
TotalLabour = setc.Average(i => i.LabourRequired).ToString(),
DistrictName = setc.FirstOrDefault().User.District.DistrictName
}).FirstOrDefault();
if (result == null)
{
result = new SiteEngineerTotalCost();
result.UserName = user.UserName;
result.TotalCost = "0";
result.TotalLabour = "0";
result.DistrictName = user.District.DistrictName;
}
resultList.Add(result);
}
return(View(resultList));
}
public void Validate_Report_For_Model_SiteEngineerTotalCost_Given_Null_ExpectNoValidationError()
{
var model = new SiteEngineerTotalCost();
var ErrorMeassageList = TestModelHelper.Validate(model);
Assert.AreEqual(0, ErrorMeassageList.Count);
}
public void Validate_Report_For_Model_SiteEngineerTotalCost_Given_Valid_ExpectNoValidationError()
{
var model = new SiteEngineerTotalCost()
{
UserName = "******",
TotalCost = "56",
TotalLabour = "35",
DistrictName = "Rhodes"
};
var ErrorMeassageList = TestModelHelper.Validate(model);
Assert.AreEqual(0, ErrorMeassageList.Count);
}
/// <summary>
/// Get total cost list for an User. Cost list will be calculated depending on the report Type (SUM or AVG)
/// </summary>
/// <param name="reportType"></param>
/// <returns></returns>
public List <SiteEngineerTotalCost> GetTotalCostList(string reportType)
{
List <SiteEngineerTotalCost> aSiteEngineerTotalCostList = new List <SiteEngineerTotalCost>();
connectionString = aDatabaseConfig.Setup("ENETCareDatabase");
using (SqlConnection connection = new SqlConnection())
{
connection.ConnectionString = connectionString;
string query = "";
if (reportType.Equals("Total"))
{
query = "SELECT SUM(LabourRequired), SUM(CostRequired), UserID FROM Intervention WHERE InterventionState=@state GROUP BY UserID";
}
else
{
query = "SELECT AVG(LabourRequired), AVG(CostRequired), UserID FROM Intervention WHERE InterventionState=@state GROUP BY UserID";
}
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add(new SqlParameter("state", "Completed"));
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
SiteEngineerTotalCost aSiteEngineerTotalCost = new SiteEngineerTotalCost();
float SELabourDouble = float.Parse(reader[0].ToString());
double x = Math.Truncate(SELabourDouble * 100) / 100;
aSiteEngineerTotalCost.TotalLabour = x.ToString();
float SECostDouble = float.Parse(reader[1].ToString());
double y = Math.Truncate(SECostDouble * 100) / 100;
aSiteEngineerTotalCost.TotalCost = y.ToString();
aSiteEngineerTotalCost.UserID = Int32.Parse(reader[2].ToString());
aSiteEngineerTotalCost.UserName = aUserGateway.GetUserNameByUserID(Int32.Parse(reader[2].ToString()));
aSiteEngineerTotalCostList.Add(aSiteEngineerTotalCost);
}
}
catch { }
}
return(aSiteEngineerTotalCostList);
}
public List <SiteEngineerTotalCost> PopulateFinalTotalCostList(List <SiteEngineerTotalCost> aSiteEngineerTotalCostList, List <User> anUserList)
{
foreach (var user in anUserList)
{
bool isExist = aSiteEngineerTotalCostList.Exists(x => x.UserID == user.UserID);
if (isExist == false)
{
SiteEngineerTotalCost aSiteEngineerTotalCost = new SiteEngineerTotalCost();
aSiteEngineerTotalCost.UserID = user.UserID;
aSiteEngineerTotalCost.UserName = user.UserName;
aSiteEngineerTotalCost.TotalLabour = "0";
aSiteEngineerTotalCost.TotalCost = "0";
aSiteEngineerTotalCostList.Add(aSiteEngineerTotalCost);
}
}
List <SiteEngineerTotalCost> sortedList = aSiteEngineerTotalCostList.OrderBy(o => o.UserName).ToList();
return(sortedList);
}