public Intervention UpdateIntervention(int interventionId, User user, string oldStatus, string newStatus)
{
using (context = new CustomDBContext())
{
var intervention = context.Interventions
.Where(i => i.InterventionId == interventionId && i.Client.ClientDistrict.Equals(user.District) &&
i.Status.Equals(oldStatus)) //Added &&
.Select(i => i)
.FirstOrDefault();
if (newStatus.Equals(Status.APPROVED))
{
intervention.ApprovedByUserId = user.UserId;
}
intervention.LastUpdatedByUserId = user.UserId;
intervention.Status = newStatus;
intervention.ModifyDate = DateTime.Now;
try
{
context.SaveChanges();
}
catch
{
throw new FailedToUpdateRecordException();
}
return(intervention);
}
}
public IList <UserViewModel> GetUsersView()
{
context = new CustomDBContext();
context1 = new ApplicationDbContext();
{
context1 = new ApplicationDbContext();
var userList = (from tb1 in context1.Users
from tb2 in tb1.Roles
join tb3 in context1.Roles on tb2.RoleId equals tb3.Id
where tb3.Name == "Site_Engineer" || tb3.Name == "Manager"
select new { tb1.Id, Name = tb3.Name.Replace("_", " "), tb1.UserName }).ToList();
var userDetail = (from tb1 in context.Users
select tb1).ToList();
IList <UserViewModel> userView = (from tb1 in userList
join tb2 in userDetail
on tb1.Id equals tb2.UserId
orderby tb1.UserName
select new UserViewModel()
{
UserId = tb2.UserId,
RoleName = tb1.Name,
UserName = tb1.UserName,
MaximumCost = tb2.MaximumCost,
MaximumHours = tb2.MaximumHours,
District = tb2.District
}).ToList();
return(userView);
}
}
public void AddClient(User user, Client client)
{
using (context = new CustomDBContext())
{
context.Clients.Add(client);
context.SaveChanges();
}
}
public void AddIntervention(Intervention intervention)
{
using (context = new CustomDBContext())
{
context.Interventions.Add(intervention);
context.SaveChanges();
}
}
/// <summary>
/// This method is used for getting all intervention types
/// </summary>
public IList <InterventionType> GetAllInterventionTypes()
{
using (context = new CustomDBContext())
{
var interventionTypes = context.InterventionTypes;
return(interventionTypes.ToList());
}
}
/// <summary>
/// This method is used for getting a client detail in the same district based on the userid and district
/// </summary>
/// <returns>IList</returns>
public IList <Client> GetCurrentClients(string userId, string district)
{
using (context = new CustomDBContext())
{
var clients = context.Clients
.Where(c => c.ClientDistrict.Equals(district) && c.CreatedByUserId.Equals(userId))
.Select(c => c);
return(clients.ToList());
}
}
/// <summary>
/// This method is used for getting intervention with intervention id
/// </summary>
public InterventionType GetIntervention(int interventionTypeId)
{
using (context = new CustomDBContext())
{
var interventionTypes = context.InterventionTypes
.Where(i => i.InterventionTypeId == interventionTypeId)
.Select(i => i);
return(interventionTypes.FirstOrDefault());
}
}
/// <summary>
/// This method is used for getting all clients associated with the district
/// </summary>
/// <returns>IList</returns>
public IList <Client> GetClientsInDistrict(string district)
{
using (context = new CustomDBContext())
{
var clients = context.Clients
.Where(c => c.ClientDistrict.Equals(district))
.Select(c => c);
return(clients.ToList());
}
}
/// <summary>
/// This method is used for getting all clients associated with the user
/// </summary>
/// <returns>IList</returns>
public IList <Client> GetClientsForUser(string userId)
{
using (context = new CustomDBContext())
{
var clients = context.Clients
.Where(c => c.CreatedByUserId.Equals(userId))
.Select(c => c);
return(clients.ToList());
}
}
/// <summary>
/// This method is used for getting current users data
/// </summary>
/// <param name="userID">Id of the current User</param>
/// <returns>user</returns>
public User GetUser(string userID)
{
using (context = new CustomDBContext())
{
var user = context.Users
.Where(u => u.UserId.Equals(userID))
.Select(u => u)
.FirstOrDefault();
return(user);
}
}
/// <summary>
/// This method is used for getting current users district with user id
/// </summary>
/// <returns>district</returns>
public string GetUserDistrict(string userId)
{
using (context = new CustomDBContext())
{
var district = context.Users
.Where(u => u.UserId.Equals(userId))
.Select(u => u.District)
.FirstOrDefault();
return(district);
}
}
/// <summary>
/// This method is used for getting a client detail based on the clientID
/// </summary>
/// <returns>IList</returns>
public Client GetClient(int clientId)
{
using (context = new CustomDBContext())
{
var client = context.Clients
.Where(c => c.ClientId == clientId)
.Select(c => c)
.FirstOrDefault();
return(client);
}
}
/// <summary>
/// This method is used for getting client's assosiated inteventions
/// </summary>
/// <param name="Id">Id of the client</param>
/// <returns>IList</returns>
public IList <Intervention> GetClientsInterventions(int clientId)
{
using (context = new CustomDBContext())
{
IList <Intervention> clientInterventions = context.Interventions
.Select(i => i)
.Where(i => i.ClientId == clientId && i.Status != Status.CANCELLED)
.Include(i => i.InterTypeId)
.ToList();
return(clientInterventions);
}
}
/// <summary>
/// This method is used for getting intervention Type from the Id
/// </summary>
/// <param name="interventionTypeId">Id of an intervention type</param>
/// <returns>InterventionType</returns>
public InterventionType GetInterventionType(int interventionTypeId)
{
using (context = new CustomDBContext())
{
var interventionType = context.InterventionTypes
.Where(i => i.InterventionTypeId.Equals(interventionTypeId))
.Select(i => i)
.FirstOrDefault();
return(interventionType);
}
}
/// <summary>
/// This method is used for getting intervention with intervention id
/// </summary>
/// <returns>IList</returns>
public Intervention GetIntervention(int InterventionId)
{
using (context = new CustomDBContext())
{
var intervention = context.Interventions
.Where(i => i.InterventionId == InterventionId)
.Select(i => i)
.Include(i => i.Client)
.Include(i => i.InterTypeId)
.FirstOrDefault();
return(intervention);
}
}
public IList <Intervention> GetAssociatedIntervention_ForManager(string user_Id)
{
using (context = new CustomDBContext())
{
IList <Intervention> intervention = (context.Interventions
.Where(i => i.ApprovedByUserId.Equals(user_Id) && i.Status == Status.APPROVED)
.Select(i => i))
.Include(i => i.Client)
.Include(i => i.InterTypeId)
.ToList();
return(intervention);
}
}
/// <summary>
/// This method is used for getting interventions with status
/// </summary>
/// <returns>IList</returns>
public IList <Intervention> GetInterventionsByStatus(string status)
{
using (context = new CustomDBContext())
{
IList <Intervention> intervention = (context.Interventions
.Where(i => i.Status.Equals(status))
.Select(i => i))
.Include(i => i.Client)
.Include(i => i.InterTypeId)
.ToList();
return(intervention);
}
}
/// <summary>
/// This method is used for getting inteventions created by the user
/// </summary>
/// <param name="userId">Id of a current User</param>
/// <returns>IList</returns>
public IList <Intervention> GetInterventionsForUser(string userId)
{
using (context = new CustomDBContext())
{
IList <Intervention> interventions = context.Interventions
.Select(i => i)
.Where(i => i.CreatedByUserId.Equals(userId) && i.Status != Status.CANCELLED)
.Include(i => i.InterTypeId)
.Include(i => i.Client)
.ToList();
return(interventions);
}
}
/// <summary>
/// This method is used for getting costs by district view
/// </summary>
public IList <CostsByDistrictModel> CostsByDistrictView()
{
using (context = new CustomDBContext())
{
var districtList = new List <string>();
districtList.Add(Districts.URBAN_INDONESIA);
districtList.Add(Districts.RURAL_INDONESIA);
districtList.Add(Districts.URBAN_PAPUA_NEW_GUINEA);
districtList.Add(Districts.RURAL_PAPUA_NEW_GUINEA);
districtList.Add(Districts.SYDNEY);
districtList.Add(Districts.RURAL_NEW_SOUTH_WALES);
var costsByDistrict = (from tb1 in context.Interventions.Include("Client")
where tb1.Status == Status.COMPLETED
group tb1 by tb1.Client.ClientDistrict into tb2
select new
{
District = tb2.Key,
Hours = tb2.Sum(i => i.InterventionHours),
Costs = tb2.Sum(i => i.InterventionCost)
}).ToList();
IList <CostsByDistrictModel> CostsByDistrictView =
(from tb1 in districtList
join tb2 in costsByDistrict
on tb1 equals tb2.District into temp
from tb3 in temp.DefaultIfEmpty()
select new CostsByDistrictModel()
{
DistrictName = tb1,
Hours = (tb3 == null) ? 0 : tb3.Hours,
Costs = (tb3 == null) ? 0 : tb3.Costs
}).ToList();
IList <CostsByDistrictModel> CostsByDistrictWithTotalView1 = (from tb1 in CostsByDistrictView
select tb1).ToList()
.Union
(from tb2 in CostsByDistrictView
group tb2 by "" into tb2
select new CostsByDistrictModel()
{
DistrictName = "Total",
Hours = tb2.Sum(i => i.Hours),
Costs = tb2.Sum(i => i.Costs)
}).ToList();
return(CostsByDistrictWithTotalView1);
}
}
/// <summary>
/// This method is used for getting monthly costs for district view
/// </summary>
public IList <MonthlyCostsForDistrictModel> MonthlyCostsForDistrictView(string district)
{
var yearList = new List <int> {
2017
};
var monthList = new List <int> {
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
};
var districtList = new List <string>();
districtList.Add(Districts.URBAN_INDONESIA);
districtList.Add(Districts.RURAL_INDONESIA);
districtList.Add(Districts.URBAN_PAPUA_NEW_GUINEA);
districtList.Add(Districts.RURAL_PAPUA_NEW_GUINEA);
districtList.Add(Districts.SYDNEY);
districtList.Add(Districts.RURAL_NEW_SOUTH_WALES);
using (context = new CustomDBContext())
{
var monthlyCosts = (from tb1 in context.Interventions.Include("Client")
where tb1.Status == Status.COMPLETED
group tb1 by new { tb1.Client.ClientDistrict, tb1.CreateDate.Year, tb1.CreateDate.Month } into tb2
select new
{
District = tb2.Key.ClientDistrict,
Year = tb2.Key.Year,
Month = tb2.Key.Month,
Hours = tb2.Sum(i => i.InterventionHours),
Costs = tb2.Sum(i => i.InterventionCost)
}).ToList();
IList <MonthlyCostsForDistrictModel> monthlyCostsForDistrict = (from tb1 in districtList
from tb2 in monthList
from tb22 in yearList
join tb3 in monthlyCosts
on new { district = tb1, month = tb2, year = tb22 } equals new { district = tb3.District, month = tb3.Month, year = tb3.Year } into temp
from tb4 in temp.DefaultIfEmpty()
where tb1 == district
select new MonthlyCostsForDistrictModel()
{
District = tb1,
Year = Convert.ToString(tb22),
Month = new DateTime(2000, Convert.ToInt32(tb2), 1).ToString("MMMM", CultureInfo.GetCultureInfo("en-US")),
MonthlyHours = (tb4 == null) ? 0 : tb4.Hours,
MonthlyCosts = (tb4 == null) ? 0 : tb4.Costs
}).ToList();
return(monthlyCostsForDistrict);
}
}
public void UpdateDistrictForUser(string userId, string district)
{
context = new CustomDBContext();
{
User user = context.Users.Single(u => u.UserId == userId);
user.District = district;
try
{
context.SaveChanges();
}
catch (Exception)
{
throw new FailedToUpdateRecordException();
}
}
}
public Intervention UpdateIntervention(Intervention intervention)
{
using (context = new CustomDBContext())
{
var inter = context.Interventions
.Where(i => i.InterventionId == intervention.InterventionId)
.Select(i => i)
.FirstOrDefault();
inter.ModifyDate = DateTime.Now;
inter.Status = intervention.Status;
inter.LastUpdatedByUserId = intervention.LastUpdatedByUserId;
inter.Comments = intervention.Comments;
inter.Condition = intervention.Condition;
inter.InterventionCost = intervention.InterventionCost;
inter.InterventionHours = intervention.InterventionHours;
context.SaveChanges();
return(inter);
}
}
public IList <AverageCostsByEngineerModel> AverageCostsByEngineerView()
{
context = new CustomDBContext();
context1 = new ApplicationDbContext();
{
var userList = (from tb1 in context1.Users
from tb2 in tb1.Roles
join tb3 in context1.Roles on tb2.RoleId equals tb3.Id
where tb3.Name == "Site_Engineer"
select new { UserId = tb1.Id, RoleName = tb3.Name.Replace("_", " "), UserName = tb1.UserName }).ToList();
var totalCostsByEngineer = (from tb1 in context.Interventions
where tb1.Status == Status.COMPLETED
group tb1 by tb1.CreatedByUserId into tb2
select new
{
UserId = tb2.Key,
AverageHours = tb2.Average(i => i.InterventionHours),
AverageCosts = tb2.Average(i => i.InterventionCost)
}).ToList();
IList <AverageCostsByEngineerModel> AverageCostsByEngineerView =
(from tb1 in userList
join tb2 in totalCostsByEngineer
on tb1.UserId equals tb2.UserId into temp
from tb3 in temp.DefaultIfEmpty()
orderby tb1.UserName
select new AverageCostsByEngineerModel()
{
UserId = tb1.UserId,
RoleName = tb1.RoleName,
UserName = tb1.UserName,
AverageHours = (tb3 == null) ? 0 : tb3.AverageHours,
AverageCosts = (tb3 == null) ? 0 : tb3.AverageCosts
}).ToList();
return(AverageCostsByEngineerView);
}
}
public ProjectDBContext()
{
identityDBContext = new ApplicationDbContext();
customDBContext = new CustomDBContext();
}